ALTER TABLE Trn_Pagos ADD PrecioPub MONEY DEFAULT(0) NOT NULL
,RefVehiculo VARCHAR(10),RefNumVeh VARCHAR(10)
GO

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].[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].[paQryPagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_EdsFormas_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_EdsFormas_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_EdsFormasDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_EdsFormasDso]
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.PrecioPub,P.RefVehiculo,P.RefNumVeh,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 OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_EdsFormasDso] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmCredito,tmIdForma,FormaPago,tmTipoRango,tmIdRango,tmNumSerie,tmNumForma,tmNForma,tmVrTotal
,tmIdBanco,Banco,tmObservacion,tmIdCuenta,tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,tmpVehiculo,tmnVehiculo
,tmIdVend,V.RazonSocial AS Vendedor,tmCdProducto,tmCantidad,tmReferencia,tmReferncia2,tmFecForma,tmCtaForma,tmBeneficiario
,tmIdUsuario,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantDenm,tmTipDenom,tmCdCta,tmEsCaja,tmCantPuntos
,tmTipAplica,tmNumero,tmCdCCosto,tmCdSubCos,tmVrPrecio,tmVrPrecio*tmCantidad AS VrPublico
,CASE WHEN tmVrPrecio<>0 THEN tmVrTotal-(tmVrPrecio*tmCantidad) ELSE 0 END AS Excedente
,tmVrBase,tmVrPresp
 FROM tm_EdsFormas AS P INNER JOIN Formaspago AS F ON P.tmIdForma=F.IdForma
 INNER JOIN Bancos AS B ON P.tmIdBanco=B.IdBanco
 INNER JOIN Terceros AS T ON P.tmIdCliente=T.IdTercero
 LEFT JOIN Terceros AS V ON P.tmIdVend=V.IdTercero
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_EdsFormas_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_EdsFormas (tmNumero,tmItem,tmCredito,tmTipoRango,tmIdRango,tmIdForma,tmObservacion,tmEsCaja,tmIdBanco,tmNForma,tmFecForma,tmCtaForma,tmBeneficiario,tmNumSerie,tmNumForma
,tmVrTotal,tmIdCliente,tmIdAgencia,tmIdCuenta,tmpVehiculo,tmnVehiculo,tmIdVend,tmCdProducto,tmCantidad,tmReferencia,tmReferncia2,tmCantPuntos,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantDenm
,tmTipDenom,tmCdCta,tmTipAplica,tmIdUsuario,tmCdCCosto,tmCdSubCos,tmVrPrecio,tmVrBase,tmVrPresp)
 SELECT @pmtmNumero,Item,0,CASE WHEN FE_Ticket=1 THEN 'TKT' ELSE '0' END,0,IdForma,Detalle,EsCaja,IdBanco,NumForma,FecForma,CtaForma,Beneficiario,'0',0
,VrPagado,NitCliente,CdAgencia,'0',RefVehiculo,RefNumVeh,'0',ISNULL(CdProducto,'0'),Cant_Gals,Referncia1,Referncia2,0,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,CdCta,'',IdUsuario,'0','0',PrecioPub,PrecioBase,PrecioUnit
 FROM Trn_Pagos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
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)
,@pmPrecioPub MONEY,@pmRefVehiculo VARCHAR(10),@pmRefNumVeh VARCHAR(10),@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,PrecioPub,RefVehiculo,RefNumVeh)
 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,@pmPrecioPub,@pmRefVehiculo,@pmRefNumVeh)
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,PrecioPub,RefVehiculo,RefNumVeh,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


--PRESTAMOS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasRmc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinCuotasRmc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasRac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinCuotasRac]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFinCuotasRmc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)
 AS 
SELECT C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia,CL.NomAgencia,C.TipDoc,TD.TipoDoc,C.IdPrestamo,C.IdCia,CI.Compania,C.Item,C.Fecha,C.FechaVence
,C.Concepto,C.Detalle,C.VrTotal,C.VrAbonado AS TotalAbonado,C.NumCuota
,A.TipRec,TR.TipoDoc AS TipoRecibo,A.Recibo,A.IdCia AS CdCiaRec,CR.Compania AS NomCiaRecibo,A.Fecha AS FecRecibo,A.FecPago,A.TotalAbono AS VrAbono,A.Detalle AS DetalleAbono
--Datos tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.Telefono,T.TelMovil,T.e_mail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,CL.IdGrupo AS CdGrupoCli,GrupoClie,CL.IdTipoTerc AS CdTipoTerc,TipoTercero
FROM Trn_FinCuotas AS C LEFT JOIN Trn_FinAbonos AS A ON C.TipDoc=A.TipDoc AND C.IdPrestamo=A.IdPrestamo AND C.IdCia=A.IdCiaPre AND C.Item=A.ItemPre
INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
INNER JOIN Terceros AS T ON C.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
LEFT JOIN TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
LEFT JOIN Sys_TiposDoc AS TR ON A.TipRec=TR.IdDoc LEFT JOIN Companias AS CR ON A.IdCia=CR.IdCia
WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (C.IdCia=@pmIdCia OR @pmIdCia IS NULL) 
UNION ALL
SELECT A.IdCliente,T.RazonSocial,A.IdAgencia,CL.NomAgencia,'SIA','SALDO INICIAL DE APORTES',A.Id,A.IdCiaCrea,CI.Compania,A.Item,A.FecInicio,A.FecFinal
,'APORTES',A.Descripcion,A.ValorTotal,A.ValorTotal,A.CantCuotas
,'SIA','ABONOS SALDOS INICIALES',A.Id,A.IdCiaCrea,CI.Compania,A.FecInicio,A.FecFinal,A.ValorTotal,'SALDOS INCINIALES DE APORTES'
--Datos del tercero
,T.TipoId,T.Dv,T.Direccion,T.IdLocal,L.Localidad,L.IdDep,D.Departamento,T.Telefono,T.TelMovil,T.e_mail,T.IdSector,SectorEco,T.IdRegimen,Regimen,CL.IdGrupo,GrupoClie,CL.IdTipoTerc,TipoTercero
FROM Trn_FinCiAportes AS A INNER JOIN Terceros AS T ON A.IdCliente=T.IdTercero INNER JOIN Companias AS CI ON A.IdCiaCrea=CI.IdCia
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
LEFT JOIN TercCliePrestamo AS CL ON A.IdCliente=CL.IdClie AND A.IdAgencia=CL.IdAgencia
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
WHERE A.FecFinal BETWEEN @pmFechaIni AND @pmFechaFin
AND (A.IdCiaCrea=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFinCuotasRac] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmAnnoAnt INT,@pmMesAnt INT,@pmIdCia CHAR(2)
 AS 
SELECT C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia,CL.NomAgencia,C.TipoReg,C.TipDoc,TD.TipoDoc,C.IdPrestamo,C.IdCia,CI.Compania,C.Item,C.NumCuota,C.Fecha,C.FechaVence
,C.Concepto,C.Detalle,C.CodLinCred,LC.LinCredito,C.VrTotal,C.VrAbono,C.TotalAbonado,C.VrOtros
,C.TipDocRef,C.NumDocRef,C.IdCiaRef,C.ItemDocRef,C.IdVend,VN.RazonSocial AS Vendedor,C.TipoAplica,C.FechaCrea
,CASE WHEN C.Concepto='APORTES' THEN [dbo].[FuncAcuPrestamosLinA](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,Null,C.Concepto) ELSE [dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,Null,C.Concepto) END AS SaldoAnt
,CASE WHEN C.Concepto='APORTES' THEN [dbo].[FuncAcuPrestamosLinA](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,Null,C.Concepto) ELSE [dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,Null,C.Concepto) END AS SaldoAntCia
,[dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,Null,Null) AS SaldoAntClie
,[dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,Null,Null) AS SaldoAntClieCia
--datos de terceros
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.Telefono,T.TelMovil,T.e_mail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,CL.IdGrupo AS CdGrupoCli,GrupoClie,CL.IdTipoTerc AS CdTipoTerc,TipoTercero
--SUBCONSULTA DE TABLAS UNIDAS
FROM (SELECT C.IdCliente,C.IdAgencia,1 AS TipoReg,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,NumCuota,C.Fecha,C.FechaVence,C.Concepto,C.Detalle,C.CodLinCred
,C.VrTotal,C.VrAbonado*0 AS VrAbono,C.VrAbonado AS TotalAbonado,C.VrAcumCapital*0 AS VrOtros,C.TipoCausac AS TipDocRef,C.NumCausacion AS NumDocRef,C.IdCiaCau AS IdCiaRef,C.NumCuota AS ItemDocRef
,CL.IdVend,C.TipoCuota AS TipoAplica,C.FechaCrea
FROM Trn_FinCuotas AS C LEFT JOIN TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (C.IdCia=@pmIdCia OR @pmIdCia IS NULL) 
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipRec,A.Recibo,A.IdCia,A.Item,C.NumCuota,A.Fecha,A.FecPago,C.Concepto,A.Detalle,C.CodLinCred
,0,A.TotalAbono,C.VrAbonado,0,A.TipDoc,A.IdPrestamo,A.IdCiaPre,A.ItemPre,A.IdVend,A.TipoAplica,A.TimeSys
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)  
AND A.TipRec<>'RC2' AND A.TipRec<>'OD2'
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipRec,A.Recibo,A.IdCia,A.Item,C.NumCuota,A.Fecha,A.FecPago,C.Concepto,A.Detalle,C.CodLinCred
,0,A.TotalAbono,C.VrAbonado,CASE WHEN CHARINDEX('{APORTES}',R.Observacion)>0 THEN R.VrPagosMas ELSE 0 END 
,A.TipDoc,A.IdPrestamo,A.IdCiaPre,A.ItemPre,A.IdVend,A.TipoAplica,A.TimeSys
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
INNER JOIN Trn_FinRecibos AS R ON A.TipRec=R.TipDoc AND A.Recibo=R.Recibo AND A.IdCia=R.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND A.TipRec='RC2'
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipRec,A.Recibo,A.IdCia,A.Item,C.NumCuota,A.Fecha,A.FecPago,C.Concepto,A.Detalle,C.CodLinCred
,0,A.TotalAbono,C.VrAbonado,CASE WHEN CHARINDEX('{APORTES}',R.Observacion)>0 THEN D.VrPagosMas*-1 ELSE 0 END 
,A.TipDoc,A.IdPrestamo,A.IdCiaPre,A.ItemPre,A.IdVend,A.TipoAplica,A.TimeSys
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
INNER JOIN Trn_FinDevRec AS D ON A.TipRec=D.TipDev AND A.Recibo=D.Devolucion AND A.IdCia=D.IdCia
INNER JOIN Trn_FinRecibos AS R ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND A.TipRec='OD2'
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipDoc,A.Recibo,A.IdCia,0,0,A.Fecha,R.FecPago
,A.Concepto,'SALDOS A FAVOR',A.CodLinCred,0,(A.VrAFavor-A.VrOtrosDb)-A.VrAbono,A.VrAbono,0
,R.TipDcm,R.Documento,R.IdCiaDcm,0,A.IdVend,1,R.TimeSys
FROM Trn_FinAfavor AS A INNER JOIN Trn_FinRecibos AS R ON A.TipDoc=R.TipDoc AND A.Recibo=R.Recibo AND A.IdCia=R.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (A.VrAFavor-A.VrOtrosDb)>A.VrAbono
UNION ALL
SELECT A.IdCliente,A.IdAgencia,0,'SIA',A.Id,A.IdCiaCrea,A.Item,A.CantCuotas,A.FecInicio,A.FecFinal,'APORTES',A.Descripcion,'0'
,A.ValorTotal,A.ValorTotal,A.ValorTotal,0,'SIA',0,'00',0,CL.IdVend,0,A.FechaCrea
FROM Trn_FinCiAportes AS A LEFT JOIN TercCliePrestamo AS CL ON A.IdCliente=CL.IdClie AND A.IdAgencia=CL.IdAgencia 
WHERE A.FecFinal BETWEEN @pmFechaIni AND @pmFechaFin
AND (A.IdCiaCrea=@pmIdCia OR @pmIdCia IS NULL) 
) AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Terceros AS T ON C.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 TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc LEFT JOIN LineasCred AS LC ON C.CodLinCred=LC.IdLinea
LEFT JOIN Terceros AS VN ON C.IdVend=VN.IdTercero
--FIN SUBCONSULTA 
-- UNION CON SALDOS ANTERIORES -NO REGISTRADO
UNION ALL

SELECT S.IdCliente,T.RazonSocial,S.IdAgencia,CL.NomAgencia,-1,'SA','SALDO ANTERIOR',0,S.IdCia,CI.Compania,0,0,@pmFechaIni,@pmFechaIni
,S.Concepto,'SALDO ANTERIOR',S.IdLinea,LC.LinCredito,0,0,0,0,'SA',0,'00',0,CL.IdVend,VN.RazonSocial,-1,@pmFechaIni
,CASE WHEN S.Concepto='APORTES' THEN (S.SaAportes+S.Recibos+S.OtrosAbonos)-(S.DevRecibos+S.DevAportes) ELSE S.SaldoAnt+S.TotalCargos-S.TotalAbonos END
,0,0,0
--datos de terceros
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.Telefono,T.TelMovil,T.e_mail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,CL.IdGrupo AS CdGrupoCli,GrupoClie,CL.IdTipoTerc AS CdTipoTerc,TipoTercero
FROM AcuPrestamosLin AS S INNER JOIN Companias AS CI ON S.IdCia=CI.IdCia INNER JOIN Terceros AS T ON S.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
LEFT JOIN TercCliePrestamo AS CL ON S.IdCliente=CL.IdClie AND S.IdAgencia=CL.IdAgencia
LEFT JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo LEFT JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
LEFT JOIN LineasCred AS LC ON S.IdLinea=LC.IdLinea LEFT JOIN Terceros AS VN ON CL.IdVend=VN.IdTercero
WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND (S.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND NOT EXISTS (SELECT * FROM (SELECT IdCliente,IdAgencia,TipDoc,IdPrestamo,IdCia,Concepto,CodLinCred FROM Trn_FinCuotas
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (IdCia=@pmIdCia OR @pmIdCia IS NULL) 
 UNION ALL
 SELECT A.IdCliente,A.IdAgencia,A.TipRec,A.Recibo,A.IdCia,C.Concepto,C.CodLinCred
 FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)  
 UNION ALL
 SELECT A.IdCliente,A.IdAgencia,A.TipDoc,A.Recibo,A.IdCia,A.Concepto,A.CodLinCred
 FROM Trn_FinAfavor AS A 
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (A.VrAFavor-A.VrOtrosDb)>A.VrAbono) AS RC 
 WHERE RC.IdCliente=S.IdCliente AND RC.IdAgencia=S.IdAgencia AND RC.IdCia=S.IdCia AND RC.Concepto=S.Concepto AND RC.CodLinCred=S.IdLinea)
 AND (CASE WHEN S.Concepto='APORTES' THEN (S.SaAportes+S.Recibos+S.OtrosAbonos)-(S.DevRecibos+S.DevAportes) ELSE S.SaldoAnt+S.TotalCargos-S.TotalAbonos END)<>0
GO