ALTER TABLE Trn_FinPrestamo ADD CauAnticipado BIT DEFAULT(0) NOT NULL
GO
UPDATE Trn_FinPrestamo SET CauAnticipado=1 WHERE Causacion='ANTICIPADO'
GO
UPDATE Trn_FinPrestamo SET Causacion='MENSUAL' WHERE Causacion='ANTICIPADO'
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFinPrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFinPrestamo]
GO

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFinPrestamo @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecPrestamo SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrPrestamo MONEY,@pmIdTasa VARCHAR(4),@pmNPlazos INT,@pmCausacion VARCHAR(10),@pmIdMora VARCHAR(4),@pmNContrato INT,@pmCdCiaCon CHAR(2)
,@pmNActaJunta INT,@pmNumCredito VARCHAR(20),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoGarantia VARCHAR(10),@pmVrGarantia MONEY,@pmDescGarantia VARCHAR(250),@pmCxPagar BIT,@pmNumCuotaIni INT,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT
,@pmCuotasMora INT,@pmTipoAprob VARCHAR(20),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmDiaCausac INT,@pmDiaCausac2 INT,@pmDiasVence VARCHAR(50),@pmIdLinea VARCHAR(4),@pmTipoPlazo CHAR(1),@pmTipoCausac INT,@pmCdTasa2 VARCHAR(4),@pmVrSeguro MONEY,@pmVrIntereses MONEY,@pmVrNeto MONEY,@pmVrCuota MONEY
,@pmTipoCredito VARCHAR(10),@pmCalcIntMora BIT,@pmNitEmpConv VARCHAR(16),@pmCuotasConv INT,@pmRefinanciado INT,@pmNumPrestRef INT,@pmCiaPrestRef CHAR(2),@pmVrRefinanc MONEY,@pmDescRefinanc  VARCHAR(150),@pmCdAlmacen VARCHAR(4),@pmCauAnticipado BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinPrestamo (TipDoc,IdPrestamo,IdCia,Fecha,FecPrestamo,FecCorte,DiaCausac,DiaCausac2,DiasVence,IdConcepto,IdCliente,IdAgencia,VrPrestamo,IdLinea,IdTasa,NPlazos,TipoPlazo,Causacion,IdMora,NContrato,CdCiaCon,NActaJunta,NumCredito,IdVend,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora
,TipoAprob,TipoCausac,CdTasa2,VrSeguro,VrIntereses,VrNeto,VrCuota,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmFecha,@pmFecPrestamo,@pmFecCorte,@pmDiaCausac,@pmDiaCausac2,@pmDiasVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrPrestamo,@pmIdLinea,@pmIdTasa,@pmNPlazos,@pmTipoPlazo,@pmCausacion,@pmIdMora,@pmNContrato,@pmCdCiaCon,@pmNActaJunta,@pmNumCredito,@pmIdVend,@pmpVehiculo,@pmCdConductor,@pmTipoGarantia,@pmVrGarantia,@pmDescGarantia
,@pmCxPagar,@pmNumCuotaIni,@pmAcumCapital,@pmAcumIntereses,@pmAcumIntMora,@pmAcumCuotas,@pmPagosCapital,@pmPagosIntereses,@pmPagosIntMora,@pmCuotasPagadas,@pmCuotasMora,@pmTipoAprob,@pmTipoCausac
,@pmCdTasa2,@pmVrSeguro,@pmVrIntereses,@pmVrNeto,@pmVrCuota,@pmTipoCredito,@pmCalcIntMora,@pmNitEmpConv,@pmCuotasConv,@pmRefinanciado,@pmNumPrestRef,@pmCiaPrestRef,@pmVrRefinanc,@pmDescRefinanc,@pmCdAlmacen,@pmCauAnticipado,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFinPrestamo @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecPrestamo SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrPrestamo MONEY,@pmIdTasa VARCHAR(4),@pmNPlazos INT,@pmCausacion VARCHAR(10),@pmIdMora VARCHAR(4)
,@pmNContrato INT,@pmCdCiaCon CHAR(2),@pmNActaJunta INT,@pmNumCredito VARCHAR(20),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoGarantia VARCHAR(10),@pmVrGarantia MONEY,@pmDescGarantia VARCHAR(250),@pmCxPagar BIT,@pmNumCuotaIni INT,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY
,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT,@pmCuotasMora INT,@pmTipoAprob VARCHAR(20),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmDiaCausac INT,@pmDiaCausac2 INT,@pmDiasVence VARCHAR(50),@pmIdLinea VARCHAR(4),@pmTipoPlazo CHAR(1),@pmTipoCausac INT,@pmCdTasa2 VARCHAR(4),@pmVrSeguro MONEY,@pmVrIntereses MONEY,@pmVrNeto MONEY,@pmVrCuota MONEY,@pmTipoCredito VARCHAR(10),@pmCalcIntMora BIT,@pmNitEmpConv VARCHAR(16),@pmCuotasConv INT
,@pmRefinanciado INT,@pmNumPrestRef INT,@pmCiaPrestRef CHAR(2),@pmVrRefinanc MONEY,@pmDescRefinanc  VARCHAR(150),@pmCdAlmacen VARCHAR(4),@pmCauAnticipado BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FinPrestamo SET Fecha=@pmFecha,FecPrestamo=@pmFecPrestamo,FecCorte=@pmFecCorte,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrPrestamo=@pmVrPrestamo,IdTasa=@pmIdTasa,NPlazos=@pmNPlazos,Causacion=@pmCausacion,IdMora=@pmIdMora,NContrato=@pmNContrato,CdCiaCon=@pmCdCiaCon,NActaJunta=@pmNActaJunta,NumCredito=@pmNumCredito,IdVend=@pmIdVend
,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipoGarantia=@pmTipoGarantia,VrGarantia=@pmVrGarantia,DescGarantia=@pmDescGarantia,CxPagar=@pmCxPagar,NumCuotaIni=@pmNumCuotaIni,AcumCapital=@pmAcumCapital,AcumIntereses=@pmAcumIntereses,AcumIntMora=@pmAcumIntMora,AcumCuotas=@pmAcumCuotas,PagosCapital=@pmPagosCapital,PagosIntereses=@pmPagosIntereses,PagosIntMora=@pmPagosIntMora,CuotasPagadas=@pmCuotasPagadas
,CuotasMora=@pmCuotasMora,TipoAprob=@pmTipoAprob,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,CdTasa2=@pmCdTasa2,VrSeguro=@pmVrSeguro,VrIntereses=@pmVrIntereses,VrNeto=@pmVrNeto,VrCuota=@pmVrCuota,FecUpdate=@pmFecUpdate
,DiaCausac=@pmDiaCausac,DiaCausac2=@pmDiaCausac2,DiasVence=@pmDiasVence,IdLinea=@pmIdLinea,TipoPlazo=@pmTipoPlazo,TipoCausac=@pmTipoCausac
,TipoCredito=@pmTipoCredito,CalcIntMora=@pmCalcIntMora,NitEmpConv=@pmNitEmpConv,CuotasConv=@pmCuotasConv
,Refinanciado=@pmRefinanciado,NumPrestRef=@pmNumPrestRef,CiaPrestRef=@pmCiaPrestRef,VrRefinanc=@pmVrRefinanc,DescRefinanc=@pmDescRefinanc,CdAlmacen=@pmCdAlmacen,CauAnticipado=@pmCauAnticipado
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinPrestamo @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Fecha,FecPrestamo,FecCorte,IdConcepto,IdCliente,IdAgencia,VrPrestamo,IdTasa,NPlazos,Causacion,IdMora,NContrato,CdCiaCon,NActaJunta,NumCredito
,IdVend,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora
,CuotasPagadas,CuotasMora,TipoAprob,TipoCausac,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,DiaCausac ,DiaCausac2,DiasVence,IdLinea,TipoPlazo
,CdTasa2,VrSeguro,VrIntereses,VrNeto,VrCuota,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FinPrestamo
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoCr @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdPrestamo,P.IdCia AS CdCia,Compania,Fecha,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia
,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra,NPlazos,Causacion
,P.IdMora AS CdMora,DescMora,TIM.TipoInteres AS TipoIntMora,TasaEA,TasaEM,TIM.DiasGracia AS Dias_Gracia,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor
,pVehiculo,CdConductor,CD.RazonSocial AS NomConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito,TipoPlazo,CdTasa2
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario,TipDoc
--cliente
,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.Fax AS TercFax,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,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 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 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  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS CD ON P.CdConductor=CD.IdTercero  
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE TipDoc=@pmTipDoc  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoFac @pmTipDoc VARCHAR(3), @pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.IdPrestamo AS NumPtmo,P.IdCia AS CdCia,Compania,P.Fecha AS FechaPtmo,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcept,P.IdCliente AS NitCliente
,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,NPlazos,TipoPlazo,P.Causacion AS TipoCausac,P.IdMora AS CdMora,DescMora
,TIM.TipoInteres AS Tipo_IntMora,TasaEM,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito
,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,CdTasa2,VrSeguro,VrIntereses,VrNeto,P.VrCuota AS ValCuota,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos de facturas
,Item,NumFactura,DF.Fecha AS FechaFact,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago,FechaLiq,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura,DiasPlazo
--Datos del cliente
,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.Fax AS TercFax,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,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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
 INNER JOIN Trn_FinFacturas AS DF ON P.TipDoc=DF.TipDoc AND P.IdPrestamo=DF.IdPrestamo AND P.IdCia=DF.IdCia
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE P.TipDoc=@pmTipDoc  AND P.IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin  AND P.IdCia=@pmIdCia
 ORDER BY  P.IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoFmt @pmTipDoc VARCHAR(3), @pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.IdPrestamo AS NumPtmo,P.IdCia AS CdCia,Compania,P.Fecha AS FechaPtmo,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcept,P.IdCliente AS NitCliente
,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,NPlazos,TipoPlazo,P.Causacion AS TipoCausac,P.IdMora AS CdMora,DescMora
,TIM.TipoInteres AS Tipo_IntMora,TasaEM,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito
,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,CdTasa2,VrSeguro,VrIntereses,VrNeto,P.VrCuota AS ValCuota,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos del detalle
,Item,FecCausac,FecVence,NumDias,PC.VrCuota AS TotalCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,PC.Causacion AS NumCausac,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac
--Datos del cliente
,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.Fax AS TercFax,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,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Trn_FinAmtza AS PC ON P.TipDoc=PC.TipDoc AND P.IdPrestamo=PC.IdPrestamo AND P.IdCia=PC.IdCia
 WHERE P.TipDoc=@pmTipDoc  AND P.IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin  AND P.IdCia=@pmIdCia
 ORDER BY  P.IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoLta @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdPrestamo,P.IdCia AS CdCia,Compania,Fecha,FecPrestamo,FecCorte,DiaCausac ,DiaCausac2,DiasVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa
,NPlazos,TipoPlazo,Causacion,IdLinea,IdMora,VrSeguro,VrIntereses,VrNeto,VrCuota,NContrato,CdCiaCon,TipoAprob,TipoCausac,NActaJunta,NumCredito,IdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia
,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora
,CuotasPagadas,CuotasMora,CdTasa2,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 WHERE TipDoc=@pmTipDoc  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinPrestamoRes @pmSaldo DECIMAL(12,2),@pmTipDoc VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT T.RazonSocial AS NomCliente,IdCliente,NomAgencia AS Agencia,P.IdAgencia AS CdAgencia,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania
,NumCredito,P.Fecha AS FecRadica,FecPrestamo,DiaCausac,DiaCausac2,DiasVence,TipoAprob,NActaJunta,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,NPlazos,TipoPlazo,Causacion,P.IdLinea AS CdLinea,LinCredito,P.IdTasa AS CdTasa,DescTasa
,tmVrCuota AS VrCuotaDTF,tmDescCuota AS DescripCuota,tmVrMora AS VrEnMora,AcumCapital,AcumIntereses,VrPrestamo-AcumCapital AS SaldoCapital,tmDiasMora AS PromDiasMora
,CausaMora,GestionClie,AcuerdosClie,TipoGarantia,DescGarantia,VrGarantia,NumCuotaIni,AcumCuotas,CuotasMora
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CdTasa2,TipoCredito,P.CalcIntMora AS CalcMora,NitEmpConv,CuotasConv,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,P.NContrato AS NumContrato,CdCiaCon,P.Observacion AS Observ
,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,Concepto,CauAnticipado,P.IdEstado AS CdEstado,Estado
,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra
--cliente
,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.Fax AS TercFax,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 Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto   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 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  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 
 LEFT JOIN Trn_FinObserv AS PD ON P.TipDoc=PD.TipDoc AND P.IdPrestamo=PD.IdPrestamo AND P.IdCia=PD.IdCia
 LEFT JOIN tm_PreMora AS PM ON P.TipDoc=PM.tmTipDoc AND P.IdPrestamo=PM.tmIdPrestamo AND P.IdCia=PM.tmIdCia
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE P.TipDoc=@pmTipDoc  AND P.Anulado=0   AND (VrPrestamo-AcumCapital)>=@pmSaldo
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND P.IdVend LIKE ISNULL(@pmIdVend,'%') 
 AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,P.IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

