ALTER TABLE AcuPrestamosLin ADD Prestamos MONEY DEFAULT(0) NOT NULL
,DevPrestamos MONEY DEFAULT(0) NOT NULL
,Recibos MONEY DEFAULT(0) NOT NULL
,DevRecibos MONEY DEFAULT(0) NOT NULL
,NotasDeb MONEY DEFAULT(0) NOT NULL
,NotasCre MONEY DEFAULT(0) NOT NULL
,Facturas MONEY DEFAULT(0) NOT NULL
,DevFacturas MONEY DEFAULT(0) NOT NULL
,OtrosAbonos MONEY DEFAULT(0) NOT NULL
,DevAportes MONEY DEFAULT(0) NOT NULL
,SaAportes MONEY DEFAULT(0) NOT NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuPrestamosLin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuPrestamosLin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuPrestamosLinCau]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuPrestamosLinCau]
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
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].[paQryFinAbonosRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinAbonosRes]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAcuPrestamosLin]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAcuPrestamosLin]
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 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.FecInicio>=@pmFechaIni AND A.FecFinal<=@pmFechaFin)
OR (A.FecInicio<@pmFechaIni AND A.FecFinal>@pmFechaFin)
OR (A.FecInicio BETWEEN @pmFechaIni AND @pmFechaFin)
OR (A.FecFinal BETWEEN @pmFechaIni AND @pmFechaFin))
AND (A.IdCiaCrea=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFinAbonosRes] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)
 AS 
 SELECT A.TipRec,TR.TipoDoc AS TipoDocRec,A.Recibo,A.IdCia,CI.Compania,A.Item AS ItemRec,A.Fecha AS FechaRec,A.FecPago
,A.TipDoc,TD.TipoDoc,A.IdPrestamo AS NumDoc,A.IdCiaPre,CD.Compania AS NomCiaDoc,A.ItemPre,A.TotalAbono,A.Detalle AS DetallePago
,A.IdCliente,T.RazonSocial AS NomCliente,A.IdAgencia,CL.NomAgencia,A.IdVend,VN.RazonSocial AS Vendedor,A.VrBaseCms,A.Comision,A.TipoAplica
,C.FechaVence,C.NumCuota,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
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia  INNER JOIN Companias AS CD ON A.IdCiaPre=CD.IdCia
 INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc INNER JOIN Sys_TiposDoc AS TR ON A.TipRec=TR.IdDoc
 INNER JOIN Terceros AS T ON A.IdCliente=T.IdTercero
 LEFT JOIN TercCliePrestamo AS CL ON A.IdCliente=CL.IdClie AND A.IdAgencia=CL.IdAgencia
 LEFT JOIN Terceros AS VN ON A.IdVend=VN.IdTercero 
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCiaPre=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAcuPrestamosLinCau] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
AS 
INSERT INTO AcuPrestamosLin (nAnno,nMes,IdCia,IdCliente,IdAgencia,IdLinea,Concepto,SaldoAnt,TotalCargos,TotalAbonos
,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas,OtrosAbonos,DevAportes,SaAportes)
SELECT YEAR(Fecha),MONTH(Fecha),IdCia,IdCliente,IdAgencia,ISNULL(CodLinCred,'0001'),Concepto,0,SUM(VrTotal),0
,SUM(CASE WHEN TipDoc IN ('PR1','CVN','APO') THEN VrTotal ELSE 0 END),0,0,0
,SUM(CASE WHEN TipDoc='ND2' THEN VrTotal ELSE 0 END),0
,SUM(CASE WHEN TipDoc='FIF' THEN VrTotal ELSE 0 END),0,0,0,0
FROM Trn_FinCuotas
WHERE (TipDoc IN ('ND2','FIF','CVN','APO') OR (TipDoc='PR1' AND Concepto<>'CAPITAL'))
AND YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes 
AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
GROUP BY YEAR(Fecha),MONTH(Fecha),IdCia,IdCliente,IdAgencia,CodLinCred,Concepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAcuPrestamosLin] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmConcepto VARCHAR(10),@pmSaldoAnt MONEY,@pmTotalCargos MONEY,@pmTotalAbonos MONEY
,@pmPrestamos MONEY,@pmDevPrestamos MONEY,@pmRecibos MONEY,@pmDevRecibos MONEY,@pmNotasDeb MONEY,@pmNotasCre MONEY,@pmFacturas MONEY
,@pmDevFacturas MONEY,@pmOtrosAbonos MONEY,@pmDevAportes MONEY,@pmSaAportes MONEY
 AS 
 INSERT INTO AcuPrestamosLin (nAnno,nMes,IdCia,IdCliente,IdAgencia,IdLinea,Concepto,SaldoAnt,TotalCargos,TotalAbonos
,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas,OtrosAbonos,DevAportes,SaAportes)
 VALUES (@pmnAnno,@pmnMes,@pmIdCia,@pmIdCliente,@pmIdAgencia,@pmIdLinea,@pmConcepto,@pmSaldoAnt,@pmTotalCargos,@pmTotalAbonos
,@pmPrestamos,@pmDevPrestamos,@pmRecibos,@pmDevRecibos,@pmNotasDeb,@pmNotasCre,@pmFacturas,@pmDevFacturas,@pmOtrosAbonos,@pmDevAportes,@pmSaAportes)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAcuPrestamosLin] (@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(SaldoAnt+TotalCargos-TotalAbonos),0) FROM AcuPrestamosLin
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCliente=@pmIdCliente AND (IdAgencia=@pmIdAgencia OR @pmIdAgencia IS NULL)
 AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
 AND (IdLinea=@pmIdLinea OR @pmIdLinea IS NULL)
 AND (Concepto=@pmConcepto OR @pmConcepto IS NULL)
 )
 RETURN @vrSaldoAnt
END
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)-(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,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.FecInicio>=@pmFechaIni AND A.FecFinal<=@pmFechaFin)
OR (A.FecInicio<@pmFechaIni AND A.FecFinal>@pmFechaFin)
OR (A.FecInicio BETWEEN @pmFechaIni AND @pmFechaFin)
OR (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