ALTER TABLE tm_PreCuotas ADD tmPMInicio SMALLDATETIME,tmPMFinal SMALLDATETIME,tmPMDias INT DEFAULT(0) NOT NULL,tmPMValorInt MONEY DEFAULT(0) NOT NULL
GO
ALTER TABLE Trn_FinAmtza ADD FecInicioPM SMALLDATETIME,FecFinalPM SMALLDATETIME,DiasInteresPM INT DEFAULT(0) NOT NULL,VrInteresPM MONEY DEFAULT(0) NOT NULL
GO
ALTER TABLE Trn_FinPrestamo ADD VrInteresPM MONEY DEFAULT(0) NOT NULL,CantDiasPM INT DEFAULT(0) NOT NULL,NumNotaIni INT DEFAULT(0) NOT NULL,NumNotaFin INT DEFAULT(0) NOT NULL,FechaFinPM SMALLDATETIME
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('IPM','LIQUIDAR INTERESES DE PERIODOS MUERTOS INICIALMENTE','BOOLEAN','0',5,'PRESTAMOS')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFinAmtza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsFinAmtza]
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].[paInstm_PreCuotas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreCuotas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreCuotas_Aza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreCuotas_Aza]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreCuotas_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreCuotas_Sel]
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].[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].[paQrytm_PreCuotasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_PreCuotasLta]
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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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.VrInteresPM,P.CantDiasPM,P.NumNotaIni,P.NumNotaFin,P.FechaFinPM
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos del detalle
,PC.Item,FecCausac,FecVence,NumDias,PC.VrCuota AS TotalCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,PC.Causacion AS NumCausac,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac,VrCustodia,VrProrroga,TasaCustodia
--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,TPS.Tarifa AS TarSeg
,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios,CLI.IncRetFte AS CalcularReteFte 
 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 Trn_FinPrestSeguro PS ON P.TipDoc =PS.TipDoc AND P.IdPrestamo=PS.IdPrestamo AND P.IdCia =PS.IdCia 
LEFT JOIN Tablapor TPS ON PS.CdTarSeguro = TPS.IdTarifa 
 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 ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,VrInteresPM,CantDiasPM,NumNotaIni,NumNotaFin,FechaFinPM
,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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,@pmCdCtaPago VARCHAR(4)
,@pmVrInteresPM MONEY,@pmCantDiasPM INT,@pmNumNotaIni INT,@pmNumNotaFin INT,@pmFechaFinPM SMALLDATETIME,@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,CdCtaPago,VrInteresPM,CantDiasPM,NumNotaIni,NumNotaFin,FechaFinPM)
 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,@pmCdCtaPago,@pmVrInteresPM,@pmCantDiasPM,@pmNumNotaIni,@pmNumNotaFin,@pmFechaFinPM)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,@pmCdCtaPago VARCHAR(4),@pmVrInteresPM MONEY,@pmCantDiasPM INT,@pmNumNotaIni INT,@pmNumNotaFin INT,@pmFechaFinPM SMALLDATETIME,@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,CdCtaPago=@pmCdCtaPago,VrInteresPM=@pmVrInteresPM,CantDiasPM=@pmCantDiasPM,NumNotaIni=@pmNumNotaIni,NumNotaFin=@pmNumNotaFin,FechaFinPM=@pmFechaFinPM
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,CdCtaPago,VrInteresPM,CantDiasPM,NumNotaIni,NumNotaFin,FechaFinPM
 FROM Trn_FinPrestamo
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[paQrytm_PreCuotasLta] @pmtmNumero VARCHAR(5)
AS
SELECT  tmItem, tmTipDoc, tmPrestamo, tmIdCiaPre, tmItemPre, tmCuota, tmConcepto, tmFecCausa, tmFecVence, tmVrTotal
,tmTipoTasa, tmDTF_EA,tmTasaNom, tmSpread, tmTasaEfe, tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,NomAgencia AS Agencia
,tmIdVend,V.RazonSocial AS NomVendedor,tmEstado, tmDetalle,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr, tmTotAbono,tmVrSaldo
,tmUltCausac,tmVrAcumCap,tmNitConv,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod,tmTarifIva,tmVrIva,tmTarifRet,tmVrRet
,tmPMInicio,tmPMFinal,tmPMDias,tmPMValorInt
--datos del prestamo
,FecPrestamo,VrPrestamo,NPlazos,Causacion,P.NContrato AS NumContrato,CdCiaCon,NumCuotaIni,P.IdEstado AS CdEstado
 FROM   tm_PreCuotas AS PC  INNER JOIN Terceros AS T ON PC.tmIdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON PC.tmIdVend=V.IdTercero
 INNER JOIN Trn_FinPrestamo AS P ON PC.tmTipDoc=P.TipDoc AND PC.tmPrestamo=P.IdPrestamo AND PC.tmIdCiaPre=P.IdCia
 LEFT JOIN TercCliePrestamo AS A ON  PC.tmIdCliente=A.IdClie AND PC.tmIdAgencia=A.IdAgencia
 WHERE tmNumero=@pmtmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,CdCtaPago,CauAnticipado,P.VrInteresPM,P.CantDiasPM,P.NumNotaIni,P.NumNotaFin,P.FechaFinPM
,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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsFinAmtza] @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecCausac SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmNumDias INT,@pmVrCuota MONEY,@pmVrCapital MONEY
,@pmVrInteres MONEY,@pmVrSaldo MONEY,@pmTasaEfeMes DECIMAL(16,8),@pmTasaEfeDia DECIMAL(16,8),@pmCausacion INT,@pmCdCiaCausac CHAR(2),@pmVrCuoCausac MONEY,@pmVrIntCausac MONEY,@pmVrSaldoCausac MONEY,@pmVrSeguroCuo MONEY
,@pmVrCustodia MONEY,@pmVrProrroga MONEY,@pmTasaCustodia DECIMAL(16,8),@pmFecInicioPM SMALLDATETIME,@pmFecFinalPM SMALLDATETIME,@pmDiasInteresPM INT,@pmVrInteresPM MONEY
 AS 
 INSERT INTO Trn_FinAmtza (TipDoc,IdPrestamo,IdCia,Item,FecCausac,FecVence,NumDias,VrCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,Causacion,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac,VrSeguroCuo,VrCustodia,VrProrroga,TasaCustodia
,FecInicioPM,FecFinalPM,DiasInteresPM,VrInteresPM)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmFecCausac,@pmFecVence,@pmNumDias,@pmVrCuota,@pmVrCapital,@pmVrInteres,@pmVrSaldo,@pmTasaEfeMes,@pmTasaEfeDia,@pmCausacion
,@pmCdCiaCausac,@pmVrCuoCausac,@pmVrIntCausac,@pmVrSaldoCausac,@pmVrSeguroCuo,@pmVrCustodia,@pmVrProrroga,@pmTasaCustodia,@pmFecInicioPM,@pmFecFinalPM,@pmDiasInteresPM,@pmVrInteresPM)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[paInstm_PreCuotas_Aza] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 INSERT INTO tm_PreCuotas (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCiaPre,tmItemPre,tmCuota,tmConcepto,tmFecCausa,tmFecVence,tmVrTotal,tmTipoTasa
,tmDTF_EA,tmTasaNom,tmSpread,tmTasaEfe,tmIdCliente,tmIdAgencia,tmIdVend,tmEstado,tmDetalle,tmTotAbono,tmLinCred,tmFactura
,tmDiasLiq,tmVrIntCorr,tmVrSaldo,tmUltCausac,tmVrAcumCap,tmNitConv,tmConvnio,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod,tmTarifIva,tmVrIva,tmTarifRet,tmVrRet,tmPMInicio,tmPMFinal,tmPMDias,tmPMValorInt)
SELECT @pmtmNumero,Item,TipDoc,IdPrestamo,IdCia,Item,Item,'CAPITAL',FecCausac,FecVence,VrCuota,'VARIABLE',TasaEfeMes,TasaEfeDia,0,TasaEfeMes
,'0','0','0',0,'',0,'0','',NumDias,VrInteres,VrSaldo,Null,VrCapital,'0',0,VrSeguroCuo,VrProrroga,VrCustodia,TasaCustodia,0,0,0,0,FecInicioPM,FecFinalPM,DiasInteresPM,VrInteresPM
 FROM Trn_FinAmtza
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[paInstm_PreCuotas_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 INSERT INTO tm_PreCuotas (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCiaPre,tmItemPre,tmCuota,tmConcepto,tmFecCausa,tmFecVence,tmVrTotal,tmTipoTasa
,tmDTF_EA,tmTasaNom,tmSpread,tmTasaEfe,tmIdCliente,tmIdAgencia,tmIdVend,tmEstado,tmDetalle,tmTotAbono,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr
,tmVrSaldo,tmUltCausac,tmVrAcumCap,tmNitConv,tmConvnio,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod,tmTarifIva,tmVrIva,tmTarifRet,tmVrRet,tmPMInicio,tmPMFinal,tmPMDias,tmPMValorInt)
SELECT @pmtmNumero,Item,TipDoc,IdPrestamo,IdCia,Item,NumCuota,Concepto,Fecha,FechaVence,VrTotal,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente
,IdAgencia,'0',1,Detalle,VrAbonado,CodLinCred,NumFactura,DiasLiquida,VrInteresFact,0,FecUltCausac,VrAcumCapital,NitConvenio,Convenio,0,0,0,0
,TarifIvaInt,VrIvaInt,TarifRetInt,VrRetInt,Null,Null,0,0
 FROM Trn_FinCuotas
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_PreCuotas] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipDoc VARCHAR(3),@pmtmPrestamo INT,@pmtmIdCiaPre CHAR(2),@pmtmItemPre INT,@pmtmCuota INT,@pmtmConcepto VARCHAR(10)
,@pmtmFecCausa SMALLDATETIME,@pmtmFecVence SMALLDATETIME,@pmtmVrTotal MONEY,@pmtmTipoTasa VARCHAR(10),@pmtmDTF_EA DECIMAL(16,8),@pmtmTasaNom DECIMAL(16,8),@pmtmSpread DECIMAL(16,8)
,@pmtmTasaEfe DECIMAL(16,8),@pmtmIdCliente VARCHAR(16),@pmtmIdAgencia VARCHAR(16),@pmtmIdVend VARCHAR(16),@pmtmEstado INT,@pmtmDetalle VARCHAR(250),@pmtmTotAbono MONEY,@pmtmLinCred VARCHAR(4)
,@pmtmFactura VARCHAR(20),@pmtmDiasLiq INT,@pmtmVrIntCorr MONEY,@pmtmVrSaldo MONEY,@pmtmUltCausac SMALLDATETIME,@pmtmVrAcumCap MONEY,@pmtmNitConv VARCHAR(16),@pmtmConvnio INT,@pmtmVrSeguro MONEY
,@pmtmVrProrroga MONEY,@pmtmVrCustodia MONEY,@pmtmTasaCustod DECIMAL(16,8),@pmtmTarifIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifRet DECIMAL(14,4),@pmtmVrRet MONEY,@pmtmPMInicio SMALLDATETIME,@pmtmPMFinal SMALLDATETIME,@pmtmPMDias INT,@pmtmPMValorInt MONEY
 AS 
 INSERT INTO tm_PreCuotas (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCiaPre,tmItemPre,tmCuota,tmConcepto,tmFecCausa,tmFecVence,tmVrTotal,tmTipoTasa,tmDTF_EA,tmTasaNom,tmSpread,tmTasaEfe,tmIdCliente
,tmIdAgencia,tmIdVend,tmEstado,tmDetalle,tmTotAbono,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr,tmVrSaldo,tmUltCausac,tmVrAcumCap,tmNitConv,tmConvnio,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod
,tmTarifIva,tmVrIva,tmTarifRet,tmVrRet,tmPMInicio,tmPMFinal,tmPMDias,tmPMValorInt)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipDoc,@pmtmPrestamo,@pmtmIdCiaPre,@pmtmItemPre,@pmtmCuota,@pmtmConcepto,@pmtmFecCausa,@pmtmFecVence,@pmtmVrTotal,@pmtmTipoTasa,@pmtmDTF_EA,@pmtmTasaNom
,@pmtmSpread,@pmtmTasaEfe,@pmtmIdCliente,@pmtmIdAgencia,@pmtmIdVend,@pmtmEstado,@pmtmDetalle,@pmtmTotAbono,@pmtmLinCred,@pmtmFactura,@pmtmDiasLiq,@pmtmVrIntCorr,@pmtmVrSaldo,@pmtmUltCausac
,@pmtmVrAcumCap,@pmtmNitConv,@pmtmConvnio,@pmtmVrSeguro,@pmtmVrProrroga,@pmtmVrCustodia,@pmtmTasaCustod,@pmtmTarifIva,@pmtmVrIva,@pmtmTarifRet,@pmtmVrRet,@pmtmPMInicio,@pmtmPMFinal,@pmtmPMDias,@pmtmPMValorInt)
GO
