if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasRac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinCuotasRac]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAcuPrestamosLinA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAcuPrestamosLinA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAcuPrestamosLinA] (@pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmConcepto VARCHAR(10))
RETURNS MONEY
 AS  
BEGIN 
 DECLARE @vrSaldoAnt MONEY
 SET  @vrSaldoAnt=(SELECT ISNULL(SUM((SaAportes+Recibos+OtrosAbonos+NotasCre)-(DevRecibos+DevAportes)),0) FROM AcuPrestamosLin
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCliente=@pmIdCliente
 AND Concepto=@pmConcepto
 AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
 AND (IdAgencia=@pmIdAgencia OR @pmIdAgencia IS NULL)
 AND (IdLinea=@pmIdLinea OR @pmIdLinea IS NULL)
 )
 RETURN @vrSaldoAnt
END
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,C.CodLinCred,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,C.CodLinCred,C.Concepto) END AS SaldoAntCia
,CASE WHEN C.Concepto='APORTES' THEN [dbo].[FuncAcuPrestamosLinA](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,C.CodLinCred,C.Concepto) ELSE [dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,C.CodLinCred,Null) END AS SaldoAntLinea
,CASE WHEN C.Concepto='APORTES' THEN [dbo].[FuncAcuPrestamosLinA](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,C.CodLinCred,C.Concepto) ELSE [dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,C.CodLinCred,Null) END AS SaldoAntLinCia
,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,Null) END AS SaldoAntClie
,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,Null) END 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 R.VrPagosMas>0 THEN ISNULL(DC.VrOtrosApo,0) 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
LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(Valor) AS VrOtrosApo FROM Trn_DetCuentas WHERE TipDoc='RC2' AND Concepto='OTR'
AND CodConc='_APO' GROUP BY TipDoc,Documento,IdCia) AS DC ON A.TipRec=DC.TipDoc AND A.Recibo=DC.Documento AND A.IdCia=DC.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 D.VrPagosMas>0 THEN ISNULL(DC.VrOtrosApo,0)*-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
LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(Valor) AS VrOtrosApo FROM Trn_DetCuentas WHERE TipDoc='RC2' AND Concepto='OTR'
AND CodConc='_APO' GROUP BY TipDoc,Documento,IdCia) AS DC ON D.TipDoc=DC.TipDoc AND D.Recibo=DC.Documento AND D.IdCiaDoc=DC.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 LEFT JOIN (SELECT TipDoc,Recibo,IdCia,Fecha,FecPago,TipDcm,Documento,IdCiaDcm,TimeSys FROM Trn_FinRecibos
WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin
UNION ALL
SELECT TipDoc,NumNota,IdCia,Fecha,Fecha,TipDcm,Documento,IdCiaDcm,TimeSys FROM Trn_FinNotas WHERE TipDoc='NC2'
AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin) 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,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