if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsPagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsPagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Pagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Pagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Pagos_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Pagos_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLineasCredLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryLineasCredLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagos_Bus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos_Bus]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagos_Lta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos_Lta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagosChe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagosChe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagosCns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagosCns]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagosDsd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagosDsd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagosFor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagosFor]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Pagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Pagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_PagosChe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_PagosChe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_PagosCmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_PagosCmp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_PagosDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_PagosDso]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryPagos_Lta] @pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdForma VARCHAR(4)=Null
,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmEsCaja BIT=Null,@pmIdBanco VARCHAR(4)=Null
,@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null,@pmNitCliente VARCHAR(16)=Null,@pmIdUsuario VARCHAR(11)=Null
 AS 
 SELECT TipDoc,TipoDoc,Documento,P.IdCia AS CodCia,Compania,Fecha,Item,P.IdForma AS CdForma,FormaPago,Detalle
,VrPagado,VrCambio,EsCaja,P.IdBanco AS CodBanco,B.Banco AS NomBanco,NumForma,FecForma,CtaForma,Beneficiario
,NitCliente,RazonSocial,CdAgencia,Agencia,CodAgencia,A.Referencia AS RefeAgencia,Referncia1,Referncia2,NumAutoriza,CdLocal,LF.Localidad AS CiudadPlaza,LF.IdDep AS CdDptoPlaza
,DF.Departamento AS DptoPlaza,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BC.Banco AS CtaBanco,VrDenom,Cantidad,TipDenom
,TipRef,DocRef,IdCiaRef,ItemDoc,P.CdProducto,FE_Ticket,Cant_Gals,PrecioBase,PrecioUnit,P.ConsCP,P.CodCueCons,P.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,TipoId,Dv,T.Codigo AS TerCodigo,NomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,L.IdDep AS TercCdDep,D.Departamento AS TercDpto,DocAnulado,NumDev,FecDev
 FROM  Trn_Pagos AS P INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS B ON P.IdBanco=B.IdBanco
 LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero  LEFT JOIN Agencias  AS A ON P.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal  LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep 
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 LEFT JOIN CtasCorrientes AS CTA ON P.CdCta=CTA.IdCta LEFT JOIN  Bancos AS BC ON CTA.IdBanco=BC.IdBanco
 LEFT JOIN (SELECT RC.TipDoc AS DocTipo,RC.Recibo AS DocNumero,RC.IdCia AS DocCia,RC.Anulado AS DocAnulado,NumDev,FecDev FROM Trn_Recibos AS RC 
  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Factura,IdCia,Anulado,NumDev,FecDev FROM Trn_Factcon  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Consignacion,IdCia,Anulado,NumDev,FecDev FROM Trn_Consigna  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Recibo,IdCia,Anulado,NumDev,FecDev FROM Trn_CompCaja  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Planilla,IdCia,Anulado,NumDev,FecDev FROM Trn_EdsCortes  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  ) AS DOC ON P.TipDoc=DOC.DocTipo AND P.Documento=DOC.DocNumero  AND P.IdCia=DOC.DocCia
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdForma  LIKE ISNULL(@pmIdForma,'%') AND P.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND  (EsCaja=ISNULL(@pmEsCaja,0) or EsCaja=ISNULL(@pmEsCaja,1))
 AND Documento BETWEEN ISNULL(@pmDocumentoIni,0) AND ISNULL(@pmDocumentoFin,2147483647)
 AND  NitCliente LIKE ISNULL(@pmNitCliente,'%') AND P.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 ORDER BY TipDoc,P.IdCia,Documento,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Pagos] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdForma,tmDetalle,tmVrPagado,tmVrCambio,tmEsCaja,tmIdBanco,tmNitCliente,tmCdAgencia,tmNumForma
,tmFecForma,tmCtaForma,tmBeneficiario,tmReferncia1,tmReferncia2,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantidad,tmTipDenom
,tmTipRef,tmDocRef,tmIdCiaRef,tmItemDoc,tmCdCta,tmCliente,tmTipDoc,tmDocumento,tmIdCia,tmTipAplica,tmConsCP,tmCodCueCons
 FROM tm_Pagos
 WHERE tmNumero=@pmtmNumero
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Pagos_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_Pagos (tmNumero,tmItem,tmIdForma,tmDetalle,tmVrPagado,tmVrCambio,tmEsCaja,tmIdBanco,tmNitCliente,tmCdAgencia,tmNumForma,tmFecForma,tmCtaForma,tmBeneficiario,tmReferncia1,tmReferncia2
,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantidad,tmTipDenom,tmTipRef,tmDocRef,tmIdCiaRef,tmItemDoc,tmCdCta,tmCliente,tmTipDoc,tmDocumento,tmIdCia,tmConsCP,tmCodCueCons)
 SELECT @pmtmNumero,Item,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma,CtaForma,Beneficiario,Referncia1,Referncia2
,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta,SUBSTRING(RazonSocial,1,150) AS Client,TipDoc,Documento,IdCia,ConsCP,CodCueCons
 FROM Trn_Pagos AS P LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_PagosChe] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmIdForma,tmDetalle,tmVrPagado,tmVrCambio,tmEsCaja,tmIdBanco,tmNitCliente,tmCdAgencia,tmNumForma
,tmFecForma,tmCtaForma,tmBeneficiario,tmReferncia1,tmReferncia2,tmCdLocal,tmNumAutoriza,tmTipAplica,tmIdCia,tmConsCP,tmCodCueCons
 FROM tm_Pagos
 WHERE tmNumero=@pmtmNumero AND (tmIdForma IN ('CHE','BON','CON','TRA') OR tmIdForma LIKE 'CH%')
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Pagos] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdForma VARCHAR(4),@pmtmDetalle VARCHAR(150),@pmtmVrPagado MONEY,@pmtmVrCambio MONEY
,@pmtmEsCaja BIT,@pmtmIdBanco VARCHAR(4),@pmtmNitCliente VARCHAR(16),@pmtmCdAgencia VARCHAR(16),@pmtmNumForma VARCHAR(20),@pmtmFecForma SMALLDATETIME,@pmtmCtaForma VARCHAR(30)
,@pmtmBeneficiario VARCHAR(150),@pmtmReferncia1 VARCHAR(50),@pmtmReferncia2 VARCHAR(50),@pmtmCdLocal VARCHAR(8),@pmtmNumAutoriza VARCHAR(30),@pmtmVrDenom MONEY,@pmtmCantidad INT
,@pmtmTipDenom CHAR(1),@pmtmTipRef VARCHAR(3),@pmtmDocRef INT,@pmtmIdCiaRef CHAR(2),@pmtmItemDoc INT,@pmtmCdCta VARCHAR(4),@pmtmCliente VARCHAR(150)
,@pmtmTipDoc VARCHAR(3),@pmtmDocumento INT,@pmtmIdCia CHAR(2),@pmtmTipAplica VARCHAR(10),@pmtmConsCP BIT,@pmtmCodCueCons VARCHAR(16) 
 AS 
 INSERT INTO tm_Pagos (tmNumero,tmItem,tmIdForma,tmDetalle,tmVrPagado,tmVrCambio,tmEsCaja,tmIdBanco,tmNitCliente,tmCdAgencia,tmNumForma,tmFecForma,tmCtaForma,tmBeneficiario,tmReferncia1,tmReferncia2
,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantidad,tmTipDenom,tmTipRef,tmDocRef,tmIdCiaRef,tmItemDoc,tmCdCta,tmCliente,tmTipDoc,tmDocumento,tmIdCia,tmTipAplica,tmConsCP,tmCodCueCons)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdForma,@pmtmDetalle,@pmtmVrPagado,@pmtmVrCambio,@pmtmEsCaja,@pmtmIdBanco,@pmtmNitCliente,@pmtmCdAgencia,@pmtmNumForma,@pmtmFecForma
,@pmtmCtaForma,@pmtmBeneficiario,@pmtmReferncia1,@pmtmReferncia2,@pmtmCdLocal,@pmtmNumAutoriza,@pmtmVrDenom,@pmtmCantidad,@pmtmTipDenom,@pmtmTipRef,@pmtmDocRef,@pmtmIdCiaRef
,@pmtmItemDoc,@pmtmCdCta,@pmtmCliente,@pmtmTipDoc,@pmtmDocumento,@pmtmIdCia,@pmtmTipAplica,@pmtmConsCP,@pmtmCodCueCons)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_PagosCmp] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmIdForma,FormaPago,tmDetalle,tmNumForma,tmFecForma,tmCtaForma,tmReferncia1
,tmReferncia2,tmCdCta,tmNumAutoriza,tmNitCliente,tmCdAgencia,tmConsCP,tmCodCueCons
,SUM(tmVrPagado) AS STOTPAG,SUM(tmVrCambio) AS STOTCAM
 FROM tm_Pagos AS P INNER JOIN Formaspago AS F ON P.tmIdForma=F.IdForma
 WHERE tmNumero=@pmtmNumero
 GROUP BY tmIdForma,FormaPago,tmDetalle,tmNumForma,tmFecForma,tmCtaForma,tmReferncia1
,tmReferncia2,tmCdCta,tmNumAutoriza,tmNitCliente,tmCdAgencia,tmConsCP,tmCodCueCons
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_PagosDso] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdForma,FormaPago,tmVrPagado,tmNumForma,tmIdBanco,Banco,tmCtaForma,tmBeneficiario,tmFecForma
,tmCdLocal,tmDetalle,tmReferncia1,tmReferncia2,tmNumAutoriza,tmCdCta,tmVrDenom,tmCantidad
,tmVrDenom*tmCantidad AS TotalDenm,tmTipDenom,tmEsCaja,tmIdCia,tmTipRef,tmDocRef,tmIdCiaRef,tmItemDoc,tmConsCP,tmCodCueCons,tmNumero
 FROM tm_Pagos AS P INNER JOIN Formaspago AS F ON P.tmIdForma=F.IdForma
 LEFT JOIN Bancos AS B ON P.tmIdBanco=B.IdBanco
 WHERE tmNumero=@pmtmNumero
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagos_Bus] @pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdForma VARCHAR(4)=Null
,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmEsCaja BIT=Null,@pmIdBanco VARCHAR(4)=Null
,@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null,@pmNitCliente VARCHAR(16)=Null,@pmIdUsuario VARCHAR(11)=Null
 AS 
 SELECT P.TipDoc AS CdTipDoc,TipoDoc,Documento,P.IdCia AS CodCia,Compania,P.Fecha AS FechaRec,Item,P.IdForma AS CdForma,FormaPago,Detalle
,VrPagado,VrCambio,EsCaja,P.IdBanco AS CodBanco,B.Banco AS NomBanco,NumForma,FecForma,CtaForma,Beneficiario
,NitCliente,T.RazonSocial AS NomCliente,CdAgencia,Referncia1,Referncia2,NumAutoriza,P.CdLocal AS CodCiudad,LF.Localidad AS CiudadPlaza,LF.IdDep AS CdDptoPlaza
,DF.Departamento AS DptoPlaza,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BC.Banco AS CtaBanco,VrDenom,Cantidad,TipDenom
,TipRef,DocRef,IdCiaRef,ItemDoc,P.ConsCP,P.CodCueCons,P.IdUsuario AS IdUsuari,Usuario
--datos de recibo de caja
,R.IdVehiculo AS PlacaVeh,NumVeh,R.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,Modalidad,TipCar,Cartulina,IdCiaCar,R.CentInicial AS CentenaIni,R.CentFinal AS CentenaFin
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDV,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,L.IdDep AS TercCdDep,D.Departamento AS TercDpto
 FROM  Trn_Pagos AS P INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS B ON P.IdBanco=B.IdBanco
 LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero 
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal  LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep 
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 LEFT JOIN CtasCorrientes AS CTA ON P.CdCta=CTA.IdCta LEFT JOIN  Bancos AS BC ON CTA.IdBanco=BC.IdBanco
 LEFT JOIN Trn_RecBuses AS R ON P.TipDoc=R.TipDoc AND P.Documento=R.Recibo AND P.IdCia=R.IdCia
 LEFT JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo LEFT JOIN Terceros AS NC ON R.IdConductor =NC.IdTercero
 WHERE  P.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.TipDoc IN ('RC1','OD1','CNS','DVC','RCO','DRC') AND P.TipDoc LIKE ISNULL(@pmTipDoc,'%')
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdForma  LIKE ISNULL(@pmIdForma,'%') AND P.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND  (EsCaja=ISNULL(@pmEsCaja,0) or EsCaja=ISNULL(@pmEsCaja,1))
 AND Documento BETWEEN ISNULL(@pmDocumentoIni,0) AND ISNULL(@pmDocumentoFin,2147483647)
 AND  NitCliente LIKE ISNULL(@pmNitCliente,'%') AND P.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 ORDER BY P.TipDoc,P.IdCia,Documento,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryPagosCns] @pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
,@pmTipDoc VARCHAR(3)=Null,@pmIdForma VARCHAR(4)=Null,@pmNitCliente VARCHAR(16)=Null,@pmCdAgencia VARCHAR(16)=Null
,@pmDocIni INT=Null,@pmDocFin INT=Null
 AS 
 SELECT TipDoc,Documento,IdCia,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,P.IdBanco AS CdBanco,Banco,NumForma,Fecha,FecForma,CtaForma
,Beneficiario,Referncia1,Referncia2,NumAutoriza,NitCliente,RazonSocial,CdAgencia,CdLocal,Localidad,CdCta
,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,EsCaja,ConsCP,CodCueCons,P.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Pagos AS P INNER JOIN Formaspago AS F ON P.IdForma=F.IdForma
 INNER JOIN  Bancos AS B ON P.IdBanco=B.IdBanco  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Localidades AS L ON P.CdLocal=L.IdLocal
 LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero
 WHERE TipDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO','RC1','RC2' )
 AND ((TipRef='CNS' AND DocRef<=0) OR (TipRef='EGO' AND DocRef<=0) OR (TipRef<>'CNS' AND TipRef<>'EGO')  )
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND P.IdForma LIKE ISNULL(@pmIdForma,'%')
 AND NitCliente LIKE ISNULL(@pmNitCliente,'%') AND CdAgencia LIKE ISNULL(@pmCdAgencia,'%')
 AND Documento BETWEEN ISNULL(@pmDocIni,0) AND ISNULL(@pmDocFin,2147483647)
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY IdCia,TipDoc,Documento
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagosDsd] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,EsCaja,P.IdBanco AS CodBanco,Banco,NumForma,FecForma
,CtaForma,Beneficiario,Referncia1,Referncia2,NumAutoriza,NitCliente,CdAgencia,CdLocal,TipDenom,VrDenom,Cantidad
,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta,ConsCP,CodCueCons,P.IdUsuario AS IdUsuari,Usuario,TipDoc,Documento,IdCia,Fecha
 FROM Trn_Pagos AS P INNER JOIN Formaspago AS F ON P.IdForma=F.IdForma
 INNER JOIN  Bancos AS B ON P.IdBanco=B.IdBanco
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagos_Cr] @pmTipDoc VARCHAR(3),@pmDocumentoIni INT,@pmDocumentoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,TipoDoc,Documento,P.IdCia AS CodCia,Compania,Fecha,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,EsCaja
,P.IdBanco AS CodBanco,B.Banco AS NomBanco,NitCliente,RazonSocial,CdAgencia,Agencia,CodAgencia,A.Referencia AS RefeAgencia,NumForma
,FecForma,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,LF.Localidad AS CiudadPlaza,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,CdProducto,FE_Ticket
,Cant_Gals,PrecioBase,PrecioUnit,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BC.Banco AS CtaBanco,P.ConsCP,P.CodCueCons,P.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,TipoId,Dv,T.Codigo AS TerCodigo,NomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento,Telefono,e_mail
 FROM  Trn_Pagos AS P INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS B ON P.IdBanco=B.IdBanco
 LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero  LEFT JOIN Agencias  AS A ON P.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal 
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 LEFT JOIN CtasCorrientes AS CTA ON P.CdCta=CTA.IdCta LEFT JOIN  Bancos AS BC ON CTA.IdBanco=BC.IdBanco
 WHERE P.TipDoc=@pmTipDoc AND Documento BETWEEN @pmDocumentoIni AND @pmDocumentoFin AND P.IdCia=@pmIdCia
 ORDER BY Documento,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagosChe] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,Fecha,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma
,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta,ConsCP,CodCueCons
 FROM Trn_Pagos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 AND (IdForma IN ('CHE','BON','CON','TRA') OR IdForma LIKE 'CH%')
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsPagos] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmIdForma VARCHAR(4),@pmDetalle VARCHAR(150),@pmVrPagado MONEY,@pmVrCambio MONEY
,@pmEsCaja BIT,@pmIdBanco VARCHAR(4),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmNumForma VARCHAR(20),@pmFecForma SMALLDATETIME,@pmCtaForma VARCHAR(30),@pmBeneficiario VARCHAR(150),@pmReferncia1 VARCHAR(50)
,@pmReferncia2 VARCHAR(50),@pmCdLocal VARCHAR(8),@pmNumAutoriza VARCHAR(30),@pmVrDenom MONEY,@pmCantidad INT,@pmTipDenom CHAR(1),@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmItemDoc INT
,@pmCdCta VARCHAR(4),@pmCdProducto VARCHAR(16),@pmFE_Ticket BIT,@pmCant_Gals DECIMAL(14,4),@pmPrecioBase MONEY,@pmPrecioUnit MONEY,@pmConsCP BIT,@pmCodCueCons VARCHAR(16),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Pagos (TipDoc,Documento,IdCia,Item,Fecha,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta,IdUsuario
,CdProducto,FE_Ticket,Cant_Gals,PrecioBase,PrecioUnit,ConsCP,CodCueCons)
 VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmItem,@pmFecha,@pmIdForma,@pmDetalle,@pmVrPagado,@pmVrCambio,@pmEsCaja,@pmIdBanco,@pmNitCliente,@pmCdAgencia,@pmNumForma,@pmFecForma,@pmCtaForma,@pmBeneficiario,@pmReferncia1,@pmReferncia2
,@pmCdLocal,@pmNumAutoriza,@pmVrDenom,@pmCantidad,@pmTipDenom,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmItemDoc,@pmCdCta,@pmIdUsuario,@pmCdProducto,@pmFE_Ticket,@pmCant_Gals,@pmPrecioBase,@pmPrecioUnit,@pmConsCP,@pmCodCueCons)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagos] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Documento,IdCia,Item,Fecha,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma
,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta
,CdProducto,FE_Ticket,Cant_Gals,PrecioBase,PrecioUnit,ConsCP,CodCueCons,IdUsuario
 FROM Trn_Pagos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryLineasCredLta]
 AS 
 SELECT  IdLinea,LinCredito,CdTasaDef,TipoInteres,TasaEfectiva,CdTarifIva
,TIV.Tarifa AS TarifIva,CdTarifRet,TR.Tarifa AS TarifRet,PuntosTI
 FROM LineasCred AS L LEFT JOIN Tablapor AS TIV ON L.CdTarifIva=TIV.IdTarifa
 LEFT JOIN Tablapor AS TR ON L.CdTarifRet=TR.IdTarifa
 LEFT JOIN TasasInteres AS TA ON L.CdTasaDef=TA.IdTasa
 WHERE L.Inactivo=0
 ORDER BY LinCredito
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagosFor] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
,@pmIdForma VARCHAR(4),@pmNumForma VARCHAR(20),@pmIdBanco VARCHAR(4)
 AS 
 SELECT Item,Fecha,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma
,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta,CdProducto
,FE_Ticket,IdUsuario,ConsCP,CodCueCons
 FROM Trn_Pagos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 AND IdForma=@pmIdForma  AND NumForma=@pmNumForma AND IdBanco=@pmIdBanco
GO
--MARZO 14
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinPrestamoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinPrestamoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamoCr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinPrestamoCr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinPrestamo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFinPrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpFinPrestamo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFinPrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsFinPrestamo]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFinPrestamoCr] @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdPrestamo,P.IdCia AS CdCia,Compania,Fecha,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia
,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra,NPlazos,Causacion
,P.IdMora AS CdMora,DescMora,TIM.TipoInteres AS TipoIntMora,TasaEA,TasaEM,TIM.DiasGracia AS Dias_Gracia,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor
,pVehiculo,CdConductor,CD.RazonSocial AS NomConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito,TipoPlazo,CdTasa2
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,VrInteresPM,CantDiasPM,NumNotaIni,NumNotaFin,FechaFinPM,IndDebAuto
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario,TipDoc
--cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado  INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS CD ON P.CdConductor=CD.IdTercero  
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE TipDoc=@pmTipDoc  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,IdPrestamo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFinPrestamoFmt] @pmTipDoc VARCHAR(3), @pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.IdPrestamo AS NumPtmo,P.IdCia AS CdCia,Compania,P.Fecha AS FechaPtmo,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcept,P.IdCliente AS NitCliente
,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,NPlazos,TipoPlazo,P.Causacion AS TipoCausac,P.IdMora AS CdMora,DescMora
,TIM.TipoInteres AS Tipo_IntMora,TasaEM,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito
,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,CdTasa2,VrSeguro,VrIntereses,VrNeto,P.VrCuota AS ValCuota,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,P.VrInteresPM,P.CantDiasPM,P.NumNotaIni,P.NumNotaFin,P.FechaFinPM,P.IndDebAuto
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos del detalle
,PC.Item,FecCausac,FecVence,NumDias,PC.VrCuota AS TotalCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,PC.Causacion AS NumCausac,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac,VrCustodia,VrProrroga,TasaCustodia
--Datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,TPS.Tarifa AS TarSeg
,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios,CLI.IncRetFte AS CalcularReteFte 
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT join Trn_FinPrestSeguro PS ON P.TipDoc =PS.TipDoc AND P.IdPrestamo=PS.IdPrestamo AND P.IdCia =PS.IdCia 
LEFT JOIN Tablapor TPS ON PS.CdTarSeguro = TPS.IdTarifa 
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Trn_FinAmtza AS PC ON P.TipDoc=PC.TipDoc AND P.IdPrestamo=PC.IdPrestamo AND P.IdCia=PC.IdCia
 WHERE P.TipDoc=@pmTipDoc  AND P.IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin  AND P.IdCia=@pmIdCia
 ORDER BY  P.IdPrestamo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFinPrestamoLta] @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdPrestamo,P.IdCia AS CdCia,Compania,Fecha,FecPrestamo,FecCorte,DiaCausac ,DiaCausac2,DiasVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa
,NPlazos,TipoPlazo,Causacion,IdLinea,IdMora,VrSeguro,VrIntereses,VrNeto,VrCuota,NContrato,CdCiaCon,TipoAprob,TipoCausac,NActaJunta,NumCredito,IdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia
,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora
,CuotasPagadas,CuotasMora,CdTasa2,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CdCtaPago,CauAnticipado,P.VrInteresPM,P.CantDiasPM,P.NumNotaIni,P.NumNotaFin,P.FechaFinPM
,P.IndDebAuto,P.TipCom,P.Comprobante,P.IdCiaCom,P.Anulado,P.NumDev,P.FecDev,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 WHERE TipDoc=@pmTipDoc  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,IdPrestamo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsFinPrestamo] @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecPrestamo SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrPrestamo MONEY,@pmIdTasa VARCHAR(4),@pmNPlazos INT,@pmCausacion VARCHAR(10),@pmIdMora VARCHAR(4),@pmNContrato INT,@pmCdCiaCon CHAR(2)
,@pmNActaJunta INT,@pmNumCredito VARCHAR(20),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoGarantia VARCHAR(10),@pmVrGarantia MONEY,@pmDescGarantia VARCHAR(250),@pmCxPagar BIT,@pmNumCuotaIni INT,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT
,@pmCuotasMora INT,@pmTipoAprob VARCHAR(20),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmDiaCausac INT,@pmDiaCausac2 INT,@pmDiasVence VARCHAR(50),@pmIdLinea VARCHAR(4),@pmTipoPlazo CHAR(1),@pmTipoCausac INT,@pmCdTasa2 VARCHAR(4),@pmVrSeguro MONEY
,@pmVrIntereses MONEY,@pmVrNeto MONEY,@pmVrCuota MONEY,@pmTipoCredito VARCHAR(10),@pmCalcIntMora BIT,@pmNitEmpConv VARCHAR(16),@pmCuotasConv INT,@pmRefinanciado INT,@pmNumPrestRef INT,@pmCiaPrestRef CHAR(2),@pmVrRefinanc MONEY,@pmDescRefinanc  VARCHAR(150),@pmCdAlmacen VARCHAR(4),@pmCauAnticipado BIT,@pmCdCtaPago VARCHAR(4)
,@pmVrInteresPM MONEY,@pmCantDiasPM INT,@pmNumNotaIni INT,@pmNumNotaFin INT,@pmFechaFinPM SMALLDATETIME,@pmIndDebAuto INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinPrestamo (TipDoc,IdPrestamo,IdCia,Fecha,FecPrestamo,FecCorte,DiaCausac,DiaCausac2,DiasVence,IdConcepto,IdCliente,IdAgencia,VrPrestamo,IdLinea,IdTasa,NPlazos,TipoPlazo,Causacion,IdMora,NContrato,CdCiaCon,NActaJunta,NumCredito,IdVend,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora
,TipoAprob,TipoCausac,CdTasa2,VrSeguro,VrIntereses,VrNeto,VrCuota,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,CdCtaPago,VrInteresPM,CantDiasPM,NumNotaIni,NumNotaFin,FechaFinPM,IndDebAuto)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmFecha,@pmFecPrestamo,@pmFecCorte,@pmDiaCausac,@pmDiaCausac2,@pmDiasVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrPrestamo,@pmIdLinea,@pmIdTasa,@pmNPlazos,@pmTipoPlazo,@pmCausacion,@pmIdMora,@pmNContrato,@pmCdCiaCon,@pmNActaJunta,@pmNumCredito,@pmIdVend,@pmpVehiculo,@pmCdConductor,@pmTipoGarantia,@pmVrGarantia,@pmDescGarantia
,@pmCxPagar,@pmNumCuotaIni,@pmAcumCapital,@pmAcumIntereses,@pmAcumIntMora,@pmAcumCuotas,@pmPagosCapital,@pmPagosIntereses,@pmPagosIntMora,@pmCuotasPagadas,@pmCuotasMora,@pmTipoAprob,@pmTipoCausac
,@pmCdTasa2,@pmVrSeguro,@pmVrIntereses,@pmVrNeto,@pmVrCuota,@pmTipoCredito,@pmCalcIntMora,@pmNitEmpConv,@pmCuotasConv,@pmRefinanciado,@pmNumPrestRef,@pmCiaPrestRef,@pmVrRefinanc,@pmDescRefinanc,@pmCdAlmacen,@pmCauAnticipado,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCdCtaPago,@pmVrInteresPM,@pmCantDiasPM,@pmNumNotaIni,@pmNumNotaFin,@pmFechaFinPM,@pmIndDebAuto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFinPrestamo] @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Fecha,FecPrestamo,FecCorte,IdConcepto,IdCliente,IdAgencia,VrPrestamo,IdTasa,NPlazos,Causacion,IdMora,NContrato,CdCiaCon,NActaJunta,NumCredito
,IdVend,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora
,CuotasPagadas,CuotasMora,TipoAprob,TipoCausac,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,DiaCausac ,DiaCausac2,DiasVence,IdLinea,TipoPlazo
,CdTasa2,VrSeguro,VrIntereses,VrNeto,VrCuota,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado
,TimeSys,FecUpdate,IdCiaCrea,IdUsuario,CdCtaPago,VrInteresPM,CantDiasPM,NumNotaIni,NumNotaFin,FechaFinPM,IndDebAuto
 FROM Trn_FinPrestamo
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpFinPrestamo] @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecPrestamo SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrPrestamo MONEY,@pmIdTasa VARCHAR(4),@pmNPlazos INT,@pmCausacion VARCHAR(10),@pmIdMora VARCHAR(4)
,@pmNContrato INT,@pmCdCiaCon CHAR(2),@pmNActaJunta INT,@pmNumCredito VARCHAR(20),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoGarantia VARCHAR(10),@pmVrGarantia MONEY,@pmDescGarantia VARCHAR(250),@pmCxPagar BIT,@pmNumCuotaIni INT,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY
,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT,@pmCuotasMora INT,@pmTipoAprob VARCHAR(20),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmDiaCausac INT,@pmDiaCausac2 INT,@pmDiasVence VARCHAR(50),@pmIdLinea VARCHAR(4),@pmTipoPlazo CHAR(1),@pmTipoCausac INT,@pmCdTasa2 VARCHAR(4),@pmVrSeguro MONEY,@pmVrIntereses MONEY,@pmVrNeto MONEY,@pmVrCuota MONEY,@pmTipoCredito VARCHAR(10),@pmCalcIntMora BIT,@pmNitEmpConv VARCHAR(16),@pmCuotasConv INT
,@pmRefinanciado INT,@pmNumPrestRef INT,@pmCiaPrestRef CHAR(2),@pmVrRefinanc MONEY,@pmDescRefinanc  VARCHAR(150),@pmCdAlmacen VARCHAR(4),@pmCauAnticipado BIT,@pmCdCtaPago VARCHAR(4),@pmVrInteresPM MONEY,@pmCantDiasPM INT,@pmNumNotaIni INT,@pmNumNotaFin INT,@pmFechaFinPM SMALLDATETIME,@pmIndDebAuto INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FinPrestamo SET Fecha=@pmFecha,FecPrestamo=@pmFecPrestamo,FecCorte=@pmFecCorte,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrPrestamo=@pmVrPrestamo,IdTasa=@pmIdTasa,NPlazos=@pmNPlazos,Causacion=@pmCausacion,IdMora=@pmIdMora,NContrato=@pmNContrato,CdCiaCon=@pmCdCiaCon,NActaJunta=@pmNActaJunta,NumCredito=@pmNumCredito,IdVend=@pmIdVend
,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipoGarantia=@pmTipoGarantia,VrGarantia=@pmVrGarantia,DescGarantia=@pmDescGarantia,CxPagar=@pmCxPagar,NumCuotaIni=@pmNumCuotaIni,AcumCapital=@pmAcumCapital,AcumIntereses=@pmAcumIntereses,AcumIntMora=@pmAcumIntMora,AcumCuotas=@pmAcumCuotas,PagosCapital=@pmPagosCapital,PagosIntereses=@pmPagosIntereses,PagosIntMora=@pmPagosIntMora,CuotasPagadas=@pmCuotasPagadas
,CuotasMora=@pmCuotasMora,TipoAprob=@pmTipoAprob,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,CdTasa2=@pmCdTasa2,VrSeguro=@pmVrSeguro,VrIntereses=@pmVrIntereses,VrNeto=@pmVrNeto,VrCuota=@pmVrCuota,FecUpdate=@pmFecUpdate
,DiaCausac=@pmDiaCausac,DiaCausac2=@pmDiaCausac2,DiasVence=@pmDiasVence,IdLinea=@pmIdLinea,TipoPlazo=@pmTipoPlazo,TipoCausac=@pmTipoCausac
,TipoCredito=@pmTipoCredito,CalcIntMora=@pmCalcIntMora,NitEmpConv=@pmNitEmpConv,CuotasConv=@pmCuotasConv
,Refinanciado=@pmRefinanciado,NumPrestRef=@pmNumPrestRef,CiaPrestRef=@pmCiaPrestRef,VrRefinanc=@pmVrRefinanc,DescRefinanc=@pmDescRefinanc,CdAlmacen=@pmCdAlmacen,CauAnticipado=@pmCauAnticipado,CdCtaPago=@pmCdCtaPago,VrInteresPM=@pmVrInteresPM,CantDiasPM=@pmCantDiasPM,NumNotaIni=@pmNumNotaIni,NumNotaFin=@pmNumNotaFin,FechaFinPM=@pmFechaFinPM,IndDebAuto=@pmIndDebAuto
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
--mar 23
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaBanlv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaBanlv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaBanl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaBanl]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaBanNv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaBanNv]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaBanlv] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
--Nomina + liq prima+intereses+vac
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,E.Direccion AS EmpDireccion,TelMovil,e_mail,Telefono,N.NContrato AS NumContrato,TipoLiq,Cargo,'NOM' AS TipProc,'NOMI' AS CodConcepto
,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED,SUM(VrOrigen) AS SVALBAS,SUM(VrTotDevg-VrTotDed) AS SNETLIQ
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CTA ON E.IdClase=CTA.IdClase
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE SUBSTRING(N.IdPeriodo,1,6)=@pmMes  AND FPnomina='TRANS' AND ClaseLiq IN ('NOMINA','NOVEDAD')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY  N.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,N.NContrato,TipoLiq,Cargo
UNION ALL
SELECT D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
,SUM(VrLiquida),SUM(VrDeducido),SUM(VrParcial),SUM(VrLiquida-VrDeducido) 
 FROM Trn_NomLiquida AS L INNER JOIN Trn_NomLiqDet AS D ON L.TipoLiq=D.TipoLiq AND L.Numero=D.Numero
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
 INNER JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND ((L.TipoLiq='CES' AND Semstre=0) OR L.TipoLiq='PRI') 
 AND L.Anulado=0 AND FPnomina='TRANS'
 AND D.IdNom LIKE ISNULL(@pmIdNom,'%') AND D.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
GROUP BY D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
--vacaciones
UNION ALL
SELECT V.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,V.NContrato,C.TipoLiquida,Cargo,'VAC','VA'
,VrLiquida,VrTotDed,VrRemndo,(VrLiquida+VrRemndo)-VrTotDed
 FROM Trn_NomVac AS V INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado  
 INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE V.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND FPnomina='TRANS' 
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
ORDER BY Apellidos,Nombres,N.IdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaBanl] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
--Nomina + liq prima+intereses
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,E.Direccion AS EmpDireccion,TelMovil,e_mail,Telefono,N.NContrato AS NumContrato,TipoLiq,Cargo,'NOM' AS TipProc,'NOMI' AS CodConcepto
,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED,SUM(VrOrigen) AS SVALBAS,SUM(VrTotDevg-VrTotDed) AS SNETLIQ
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CTA ON E.IdClase=CTA.IdClase
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE SUBSTRING(N.IdPeriodo,1,6)=@pmMes  AND FPnomina='TRANS' AND ClaseLiq IN ('NOMINA','NOVEDAD')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY  N.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,N.NContrato,TipoLiq,Cargo
UNION ALL
SELECT D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
,SUM(VrLiquida),SUM(VrDeducido),SUM(VrParcial),SUM(VrLiquida-VrDeducido) 
 FROM Trn_NomLiquida AS L INNER JOIN Trn_NomLiqDet AS D ON L.TipoLiq=D.TipoLiq AND L.Numero=D.Numero
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
 INNER JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND ((L.TipoLiq='CES' AND Semstre=0) OR L.TipoLiq='PRI') 
 AND L.Anulado=0 AND FPnomina='TRANS'
 AND D.IdNom LIKE ISNULL(@pmIdNom,'%') AND D.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
GROUP BY D.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,D.NContrato,C.TipoLiquida,Cargo,L.TipoLiq,D.IdConcepto
ORDER BY Apellidos,Nombres,N.IdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaBanNv] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
--Nomina +vacaciones
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase AS CdClase,ClaseCuenta,NumCuenta
,E.IdBanco AS CdBanco,Banco,CodEntidad,CdLocCue,E.Direccion AS EmpDireccion,TelMovil,e_mail,Telefono,N.NContrato AS NumContrato,TipoLiq,Cargo,'NOM' AS TipProc,'NOMI' AS CodConcepto
,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED,SUM(VrOrigen) AS SVALBAS,SUM(VrTotDevg-VrTotDed) AS SNETLIQ
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CTA ON E.IdClase=CTA.IdClase
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE SUBSTRING(N.IdPeriodo,1,6)=@pmMes  AND FPnomina='TRANS' AND ClaseLiq IN ('NOMINA','NOVEDAD')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY  N.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,N.NContrato,TipoLiq,Cargo
--vacaciones
UNION ALL
SELECT V.IdEmpleado,Apellidos,Nombres,TipoId,Codigo,IdLocal,E.IdClase,ClaseCuenta,NumCuenta
,E.IdBanco,Banco,CodEntidad,CdLocCue,E.Direccion,TelMovil,e_mail,Telefono,V.NContrato,C.TipoLiquida,Cargo,'VAC','VA'
,VrLiquida,VrTotDed,VrRemndo,(VrLiquida+VrRemndo)-VrTotDed
 FROM Trn_NomVac AS V INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado  
 INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 WHERE V.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND FPnomina='TRANS' 
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
ORDER BY Apellidos,Nombres,N.IdEmpleado
GO