INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLIPRE','PREINF','GEN',6,'Intereses por Facturar','FRMLIPRE','S',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLEXP','PREINF','GEN',7,'Extracto de  Préstamos','FRMLEXP','S',0,'')
GO
INSERT INTO Sys_Report  (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LIF','01','Intereses por facturar - Compra de cartera','CrPreInt.rpt',1,1,1,0,1,'paQryFinFacturasInt','','Intereses por facturar')
INSERT INTO Sys_Report  (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LIF','05','Intereses por facturar - Préstamos','CrPreIntCor.rpt',1,1,1,0,1,'paQryFinCuotasInt','','Intereses por facturar')
INSERT INTO Sys_Report  (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('EXP','01','Predeterminado','CrPreExto.rpt',1,1,1,0,1,'paQryFinPrestamoExto','','Extracto de préstamos')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinFacturasInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinFacturasInt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinCuotasInt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamoExto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinPrestamoExto]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasExto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinCuotasExto]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliePrestamo_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliePrestamo_Sel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuPrestamos_Uni]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAcuPrestamos_Uni]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinFacturasInt  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT P.TipDoc AS TipPrest,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania,Item,NumFactura,F.Fecha AS FecCausac,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago
,FechaLiq,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura
,T.RazonSocial AS NomCliente,IdCliente,NomAgencia AS Agencia,P.IdAgencia AS CdAgencia,NumCredito,P.Fecha AS FecRadica,FecPrestamo,FecCorte
,VrPrestamo,VrSeguro,VrCuota,NPlazos,TipoPlazo,Causacion,P.IdLinea AS CdLinea,LinCredito,TipoCredito,TipoCausac,P.IdTasa AS CdTasa,DescTasa
,AcumCapital,AcumIntereses,VrPrestamo-AcumCapital AS SaldoCapital,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas
,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,Concepto,P.Observacion AS Observ
,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.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
 FROM  Trn_FinPrestamo AS P INNER JOIN Trn_FinFacturas AS F ON P.TipDoc=F.TipDoc AND P.IdPrestamo=F.IdPrestamo AND P.IdCia=F.IdCia
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto
 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 LineasCred AS LC ON P.IdLinea=LC.IdLinea  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 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 Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE F.FechaLiq BETWEEN @pmFechaIni AND @pmFechaFin AND P.TipoCredito='FACTURAS'  AND P.Anulado=0
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasInt @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT F.TipDoc AS TipoPrest,F.IdPrestamo AS NumPrest,F.IdCia AS CdCia,Compania,Item,NumCuota,F.Fecha AS FecCausac,FechaVence,VrTotal,VrAbonado
,TipoTasa,F.DTF_EA AS TasasDTFea,TasaNom,F.Spread_TA AS TasaPuntos,TasaEfe,F.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS  CdAgencia,NomAgencia AS Agencia
,TipoCuota,F.Concepto AS ConcepCuota,F.TipoCausac AS TipoDocCau,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora,NumFactura,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio
,NumPreMora,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt
--datos del prestamo
,NumCredito,P.Fecha AS FecRadica,FecPrestamo,FecCorte,VrPrestamo,VrSeguro,VrCuota,NPlazos,TipoPlazo,Causacion,P.IdLinea AS CdLinea,LinCredito,TipoCredito,P.TipoCausac AS Tipo_Causac,P.IdTasa AS CdTasa,DescTasa
,AcumCapital,AcumIntereses,VrPrestamo-AcumCapital AS SaldoCapital,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas
,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,P.Anulado AS PreAnulado,P.Observacion AS Observ
,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.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
 FROM Trn_FinCuotas AS F INNER JOIN Companias AS CI ON F.IdCia=CI.IdCia  INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero 
 INNER JOIN TercCliePrestamo AS CLI ON  F.IdCliente=CLI.IdClie AND F.IdAgencia=CLI.IdAgencia  
 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 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_FinPrestamo AS P ON F.TipDoc=P.TipDoc AND F.IdPrestamo=P.IdPrestamo AND  F.IdCia=P.IdCia
 LEFT JOIN Terceros AS VN ON P.IdVend=VN.IdTercero  LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea 
 LEFT JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa LEFT JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  F.Concepto IN ('INTERESES','INTMORA')
 AND  F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoExto @pmFecCorte SMALLDATETIME, @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT P.TipDoc AS TipPrest,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania,FecPrestamo,P.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia
,VrPrestamo,P.VrSeguro AS VrCuoSeguro,VrCuota,NPlazos,Causacion,NumCuotaIni,P.IdLinea AS CdLinea,LinCredito,TipoCredito,TipoCausac,P.IdTasa AS CdTasa,DescTasa,P.CalcIntMora AS CalcIMora
,NumCredito,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,TipoGarantia,VrGarantia,DescGarantia,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,P.IdEstado AS CdEstado,Estado,P.Observacion AS Observ
,TI.TipoInteres AS TipoIntPre,TasaEfectiva,TipoPeriodo,CausaInteres,TI.DTF_EA AS DTFEA,TI.Spread_TA AS Puntos,OpcCompra
--acumulados 
,AcumCuotas,CuotasPagadas,CuotasMora,AcumCapital,PagosCapital,AcumIntereses,PagosIntereses,AcumIntMora,PagosIntMora
--acum Causaciones
,ISNULL((SELECT SUM(VrTotal) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='CAPITAL'),0) AS TotCapitalCau
,ISNULL((SELECT SUM(VrTotal) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='INTERESES'),0) AS TotInteresCau
,ISNULL((SELECT COUNT(NumCuota) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto=(CASE WHEN P.TipoCredito='PRESTAMO' AND P.TipoCausac=2 THEN 'INTERESES' ELSE 'CAPITAL' END)),0) AS CantCuotasCau
,ISNULL((SELECT SUM(VrTotal) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc='ND2' AND Trn_FinCuotas.NumPreMora=P.IdPrestamo AND Trn_FinCuotas.IdCiaCau=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte  AND Trn_FinCuotas.Concepto='INTMORA'),0) AS TotInteresMora
--INT MORA DEL PERIODO
,ISNULL((SELECT SUM(VrTotal) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc='ND2' AND Trn_FinCuotas.NumPreMora=P.IdPrestamo AND Trn_FinCuotas.IdCiaCau=P.IdCia
   AND Trn_FinCuotas.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Trn_FinCuotas.Concepto='INTMORA'),0) AS IntMoraPeriodo
,ISNULL((SELECT SUM(VrTotal) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='SEGURO'),0) AS TotSeguroCau
--acum pagos
,ISNULL((SELECT SUM(Trn_FinAbonos.TotalAbono) FROM Trn_FinAbonos INNER JOIN Trn_FinCuotas ON Trn_FinAbonos.TipDoc=Trn_FinCuotas.TipDoc AND Trn_FinAbonos.IdPrestamo=Trn_FinCuotas.IdPrestamo
  AND Trn_FinAbonos.IdCiaPre=Trn_FinCuotas.IdCia AND Trn_FinAbonos.ItemPre=Trn_FinCuotas.Item
  WHERE Trn_FinAbonos.TipDoc=P.TipDoc AND Trn_FinAbonos.IdPrestamo=P.IdPrestamo AND Trn_FinAbonos.IdCiaPre=P.IdCia AND Trn_FinAbonos.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='CAPITAL'),0) AS TotPagosCapital
,ISNULL((SELECT SUM(Trn_FinAbonos.TotalAbono) FROM Trn_FinAbonos INNER JOIN Trn_FinCuotas ON Trn_FinAbonos.TipDoc=Trn_FinCuotas.TipDoc AND Trn_FinAbonos.IdPrestamo=Trn_FinCuotas.IdPrestamo
  AND Trn_FinAbonos.IdCiaPre=Trn_FinCuotas.IdCia AND Trn_FinAbonos.ItemPre=Trn_FinCuotas.Item
  WHERE Trn_FinAbonos.TipDoc=P.TipDoc AND Trn_FinAbonos.IdPrestamo=P.IdPrestamo AND Trn_FinAbonos.IdCiaPre=P.IdCia AND Trn_FinAbonos.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='INTERESES'),0) AS TotPagosInteres
,ISNULL((SELECT SUM(Trn_FinAbonos.TotalAbono) FROM Trn_FinAbonos INNER JOIN Trn_FinCuotas ON Trn_FinAbonos.TipDoc=Trn_FinCuotas.TipDoc AND Trn_FinAbonos.IdPrestamo=Trn_FinCuotas.IdPrestamo
  AND Trn_FinAbonos.IdCiaPre=Trn_FinCuotas.IdCia AND Trn_FinAbonos.ItemPre=Trn_FinCuotas.Item
  WHERE Trn_FinCuotas.TipDoc='ND2' AND Trn_FinCuotas.NumPreMora=P.IdPrestamo AND Trn_FinCuotas.IdCiaCau=P.IdCia AND Trn_FinCuotas.Concepto='INTMORA' AND Trn_FinAbonos.Fecha<=@pmFecCorte),0) AS TotPagosIMora
,ISNULL((SELECT SUM(Trn_FinAbonos.TotalAbono) FROM Trn_FinAbonos INNER JOIN Trn_FinCuotas ON Trn_FinAbonos.TipDoc=Trn_FinCuotas.TipDoc AND Trn_FinAbonos.IdPrestamo=Trn_FinCuotas.IdPrestamo
  AND Trn_FinAbonos.IdCiaPre=Trn_FinCuotas.IdCia AND Trn_FinAbonos.ItemPre=Trn_FinCuotas.Item
  WHERE Trn_FinAbonos.TipDoc=P.TipDoc AND Trn_FinAbonos.IdPrestamo=P.IdPrestamo AND Trn_FinAbonos.IdCiaPre=P.IdCia AND Trn_FinAbonos.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='SEGURO'),0) AS TotPagosSeguro
,ISNULL((SELECT COUNT(*) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.VrTotal<=(Trn_FinCuotas.VrAbonado+0.5) AND Trn_FinCuotas.Concepto=(CASE WHEN P.TipoCredito='PRESTAMO' AND P.TipoCausac=2 THEN 'INTERESES' ELSE 'CAPITAL' END)),0) AS CantCuotasPagos
,ISNULL((SELECT COUNT(*) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
   AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.FechaVence<@pmFecCorte AND Trn_FinCuotas.VrTotal>Trn_FinCuotas.VrAbonado
  AND Trn_FinCuotas.Concepto=(CASE WHEN P.TipoCredito='PRESTAMO' AND P.TipoCausac=2 THEN 'INTERESES' ELSE 'CAPITAL' END)),0) AS CantCuotasMora
,ISNULL((SELECT SUM(Trn_FinCuotas.VrTotal-Trn_FinCuotas.VrAbonado) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=P.TipDoc AND Trn_FinCuotas.IdPrestamo=P.IdPrestamo AND Trn_FinCuotas.IdCia=P.IdCia
  AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.FechaVence<@pmFecCorte AND Trn_FinCuotas.VrTotal>Trn_FinCuotas.VrAbonado),0) AS TotCuotasMora
--datos de cuota del periodo
,NumCuota,CT.Fecha AS FecCausac,CT.FechaVence AS FecVence,NumDias,TasaEfe,TasaDia,CT.DTF_EA AS TasaDTF,VrCapital,CT.VrIntereses AS VrInteresCorr,CT.VrSeguro AS Vr_Seguro,VrTotalCuota,VrPagado
--datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,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.e_mail AS TercEmail,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
 FROM Trn_FinPrestamo AS P INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia 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 TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero  INNER JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 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
  --Cuotas del periodo
 LEFT JOIN (SELECT TipDoc,IdPrestamo,IdCia,NumCuota,Fecha,FechaVence,DTF_EA,TasaEfe,TasaNom AS TasaDia,SUM(CASE Concepto WHEN 'CAPITAL' THEN VrTotal ELSE 0 END) AS VrCapital
 ,SUM(CASE Concepto WHEN 'INTERESES' THEN VrTotal ELSE 0 END) AS VrIntereses,SUM(CASE Concepto WHEN 'SEGURO' THEN VrTotal ELSE 0 END) AS VrSeguro
 ,SUM(VrTotal) AS VrTotalCuota,SUM(VrAbonado) AS VrPagado,SUM(CASE Concepto WHEN 'INTERESES' THEN DiasLiquida ELSE 0 END) AS NumDias FROM Trn_FinCuotas WHERE Trn_FinCuotas.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 GROUP BY TipDoc,IdPrestamo,IdCia,NumCuota,Fecha,FechaVence,DTF_EA,TasaEfe,TasaNom) AS CT ON P.TipDoc=CT.TipDoc AND P.IdPrestamo=CT.IdPrestamo AND P.IdCia=CT.IdCia
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE FecPrestamo<=@pmFechaFin AND P.Anulado=0
 ORDER BY P.TipDoc,P.IdPrestamo,P.IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasExto @pmFechaIni SMALLDATETIME,@pmFecCorte SMALLDATETIME
 AS 
 SELECT TipDoc,IdPrestamo,C.IdCia AS CdCia,Compania,Item,NumCuota,Fecha,FechaVence,DATEDIFF(day,FechaVence,@pmFecCorte) AS DiasMora,VrTotal,VrAbonado
,IdCliente,RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,NomAgencia AS Agencia,Concepto,Detalle,TipoCuota,NumFactura,NumCausacion,IdCiaCau,FecUltCausac
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,DiasLiquida,VrAcumCapital,Convenio,NitConvenio,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt
,FecUltPago,FecLiqMora
 FROM Trn_FinCuotas AS C  INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN TercCliePrestamo AS CLI ON  C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 WHERE Fecha<@pmFechaIni AND VrTotal>VrAbonado
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCliePrestamo_Sel @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliePrestamo WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliePrestamo (IdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
 ,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc)
 SELECT @pmNewIdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
 ,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc
 FROM TercCliePrestamo WHERE IdClie=@pmIdClie

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAcuPrestamos_Uni @pmIdCliente VARCHAR(16),@pmIdClienteDos VARCHAR(16),@pmNewCliente VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdCliente FROM AcuPrestamos WHERE IdCliente=@pmIdCliente OR IdCliente=@pmIdClienteDos)
  INSERT INTO AcuPrestamos (nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas)
 SELECT nAnno,nMes,IdCia,@pmNewCliente,IdAgencia,SUM(SaldoAnt),SUM(Prestamos),SUM(DevPrestamos),SUM(Recibos),SUM(DevRecibos),SUM(NotasDeb)
,SUM(NotasCre),SUM(Facturas),SUM(DevFacturas)
 FROM AcuPrestamos WHERE IdCliente=@pmIdCliente OR IdCliente=@pmIdClienteDos
  GROUP BY  nAnno,nMes,IdCia,IdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO