INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLRAC','PREINF','GEN',12,'Resumen Consolidado de Cartera','FRMLRAC','S',0,'')
GO
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('RAC','01','Predeterminado(Hz)','CrRac.rpt',1,2,1,0,1,'paQryFinCuotasRac','','Consolidado de Cartera')
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].[paQryFinCuotasRac] @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