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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFinCuotas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFinCuotas]
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].[paInstm_PreIntMora]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreIntMora]
GO

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinCuotasLta]
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].[paQryFinPrestamoAcum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinPrestamoAcum]
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].[paQryFinPrestamoRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinPrestamoRes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_PreIntMoraLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_PreIntMoraLta]
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].[paUpFinPrestamoMora]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFinPrestamoMora]
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinCauMora @pmTipoProc VARCHAR(3),@pmNumProc INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCiaPre CHAR(2),@pmItemPre INT,@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmFecVence SMALLDATETIME,@pmVrTotal MONEY,@pmVrAbonado MONEY,@pmFecUltLiq SMALLDATETIME,@pmFecIniLiq SMALLDATETIME,@pmFecFinLiq SMALLDATETIME
,@pmDiasLiquida INT,@pmDiasGracia INT,@pmBaseLiquida MONEY,@pmTasaEA DECIMAL(16,8),@pmTasaEM DECIMAL(16,8),@pmTasaDia DECIMAL(16,8),@pmVrLiquida MONEY,@pmTarifaIva DECIMAL(14,4),@pmVrIvaInt MONEY,@pmNumNotaDb INT,@pmNumComp INT
,@pmCdLinCred VARCHAR(4),@pmNCuotaLiq INT
 AS 
 INSERT INTO Trn_FinCauMora (TipoProc,NumProc,IdCia,Item,TipDoc,IdPrestamo,IdCiaPre,ItemPre,IdCliente,IdAgencia,IdVend,FecVence,VrTotal,VrAbonado,FecUltLiq,FecIniLiq,FecFinLiq,DiasLiquida,DiasGracia,BaseLiquida,TasaEA,TasaEM,TasaDia,VrLiquida,TarifaIva,VrIvaInt,NumNotaDb,NumComp,CdLinCred,NCuotaLiq)
 VALUES (@pmTipoProc,@pmNumProc,@pmIdCia,@pmItem,@pmTipDoc,@pmIdPrestamo,@pmIdCiaPre,@pmItemPre,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmFecVence,@pmVrTotal,@pmVrAbonado,@pmFecUltLiq,@pmFecIniLiq,@pmFecFinLiq
,@pmDiasLiquida,@pmDiasGracia,@pmBaseLiquida,@pmTasaEA,@pmTasaEM,@pmTasaDia,@pmVrLiquida,@pmTarifaIva,@pmVrIvaInt,@pmNumNotaDb,@pmNumComp,@pmCdLinCred,@pmNCuotaLiq)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinCuotas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumCuota INT,@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrTotal MONEY,@pmVrAbonado MONEY
,@pmTipoTasa VARCHAR(10),@pmDTF_EA DECIMAL(16,8),@pmTasaNom DECIMAL(16,8),@pmSpread_TA DECIMAL(16,8),@pmTasaEfe DECIMAL(16,8),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmTipoCuota INT
,@pmNumCausacion INT,@pmIdCiaCau CHAR(2),@pmDetalle VARCHAR(250),@pmFecUltPago SMALLDATETIME,@pmConcepto VARCHAR(10),@pmFecLiqMora SMALLDATETIME,@pmFechaCrea SMALLDATETIME,@pmCodLinCred VARCHAR(4)
,@pmNumFactura VARCHAR(20),@pmVrInteresFact MONEY,@pmDiasLiquida INT,@pmFecUltCausac SMALLDATETIME,@pmVrAcumCapital MONEY,@pmConvenio INT,@pmNitConvenio VARCHAR(16),@pmNumPreMora INT
 AS 
 INSERT INTO Trn_FinCuotas (TipDoc,IdPrestamo,IdCia,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente,IdAgencia,Concepto,TipoCuota,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora
,FechaCrea,CodLinCred,NumFactura,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio,NumPreMora)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumCuota,@pmFecha,@pmFechaVence,@pmVrTotal,@pmVrAbonado,@pmTipoTasa,@pmDTF_EA,@pmTasaNom,@pmSpread_TA,@pmTasaEfe,@pmIdCliente
,@pmIdAgencia,@pmConcepto,@pmTipoCuota,@pmNumCausacion,@pmIdCiaCau,@pmDetalle,@pmFecUltPago,@pmFecLiqMora,@pmFechaCrea,@pmCodLinCred,@pmNumFactura,@pmVrInteresFact,@pmDiasLiquida,@pmFecUltCausac,@pmVrAcumCapital,@pmConvenio,@pmNitConvenio,@pmNumPreMora)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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)
,@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,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,@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 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
 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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  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)
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
 FROM Trn_FinAmtza
 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  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)
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
 FROM Trn_FinCuotas
 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 OFF 
GO

CREATE PROCEDURE paInstm_PreIntMora @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipDoc VARCHAR(3),@pmtmPrestamo INT,@pmtmIdCia CHAR(2),@pmtmItemPre INT,@pmtmIdCliente VARCHAR(16),@pmtmIdAgencia VARCHAR(16),@pmtmIdVend VARCHAR(16)
,@pmtmFecVence SMALLDATETIME,@pmtmVrTotal MONEY,@pmtmVrAbonado MONEY,@pmtmFecUltLiq SMALLDATETIME,@pmtmFecIniLiq SMALLDATETIME,@pmtmFecFinLiq SMALLDATETIME,@pmtmDiasLiq INT,@pmtmDiasGracia INT,@pmtmBaseLiq MONEY
,@pmtmTasaEA DECIMAL(16,8),@pmtmTasaEM DECIMAL(16,8),@pmtmTasaDia DECIMAL(16,8),@pmtmVrLiquida MONEY,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmEstado INT,@pmtmDetalle VARCHAR(250),@pmtmLinCred VARCHAR(4),@pmtmNCuota INT
 AS 
 INSERT INTO tm_PreIntMora (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCia,tmItemPre,tmIdCliente,tmIdAgencia,tmIdVend,tmFecVence,tmVrTotal,tmVrAbonado,tmFecUltLiq,tmFecIniLiq,tmFecFinLiq,tmDiasLiq,tmDiasGracia,tmBaseLiq,tmTasaEA,tmTasaEM,tmTasaDia
,tmVrLiquida,tmTarifaIva,tmVrIva,tmEstado,tmDetalle,tmLinCred,tmNCuota)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipDoc,@pmtmPrestamo,@pmtmIdCia,@pmtmItemPre,@pmtmIdCliente,@pmtmIdAgencia,@pmtmIdVend,@pmtmFecVence,@pmtmVrTotal,@pmtmVrAbonado,@pmtmFecUltLiq,@pmtmFecIniLiq,@pmtmFecFinLiq
,@pmtmDiasLiq,@pmtmDiasGracia,@pmtmBaseLiq,@pmtmTasaEA,@pmtmTasaEM,@pmtmTasaDia,@pmtmVrLiquida,@pmtmTarifaIva,@pmtmVrIva,@pmtmEstado,@pmtmDetalle,@pmtmLinCred,@pmtmNCuota)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreIntMora_Sel @pmtmNumero VARCHAR(5),@pmTipoProc VARCHAR(3),@pmNumProc INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_PreIntMora (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCia,tmItemPre,tmIdCliente,tmIdAgencia,tmIdVend,tmFecVence,tmVrTotal,tmVrAbonado
,tmFecUltLiq,tmFecIniLiq,tmFecFinLiq,tmDiasLiq,tmDiasGracia,tmBaseLiq,tmTasaEA,tmTasaEM,tmTasaDia,tmVrLiquida,tmTarifaIva,tmVrIva,tmEstado,tmDetalle,tmLinCred,tmNCuota)
 SELECT @pmtmNumero,Item,TipDoc,IdPrestamo,IdCiaPre,ItemPre,IdCliente,IdAgencia,IdVend,FecVence,VrTotal,VrAbonado,FecUltLiq,FecIniLiq,FecFinLiq,DiasLiquida
,DiasGracia,BaseLiquida,TasaEA,TasaEM,TasaDia,VrLiquida,TarifaIva,VrIvaInt,1,'',CdLinCred,NCuotaLiq
 FROM Trn_FinCauMora 
 WHERE TipoProc=@pmTipoProc AND  NumProc=@pmNumProc AND  IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreVence @pmtmEst CHAR(2),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmNumCuota INT,@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME
,@pmNumDias INT,@pmVrCuota MONEY,@pmVrCapital MONEY,@pmVrInteres MONEY,@pmVrIntMora MONEY,@pmVrAbonoCap MONEY,@pmVrAbonoInt MONEY,@pmVrSaldo MONEY
,@pmTasaEfeMes DECIMAL(16,8),@pmTasaEfeDia DECIMAL(16,8),@pmTipoCuota VARCHAR(10)
 AS 
 INSERT INTO tm_PreVence (tmEst,TipDoc,IdPrestamo,IdCia,NumCuota,Fecha,FechaVence,NumDias,VrCuota,VrCapital,VrInteres,VrIntMora,VrAbonoCap,VrAbonoInt,VrSaldo,TasaEfeMes,TasaEfeDia,TipoCuota)
 VALUES (@pmtmEst,@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmNumCuota,@pmFecha,@pmFechaVence,@pmNumDias,@pmVrCuota,@pmVrCapital,@pmVrInteres,@pmVrIntMora
,@pmVrAbonoCap,@pmVrAbonoInt,@pmVrSaldo,@pmTasaEfeMes,@pmTasaEfeDia,@pmTipoCuota)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreVenceAmza @pmtmEst CHAR(2),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_PreVence (tmEst,TipDoc,IdPrestamo,IdCia,NumCuota,Fecha,FechaVence,NumDias,VrCuota,VrCapital,VrInteres,VrIntMora
,VrAbonoCap,VrAbonoInt,VrSaldo,TasaEfeMes,TasaEfeDia,TipoCuota)
 SELECT @pmtmEst,TipDoc,IdPrestamo,IdCia,Item,FecCausac,FecVence,NumDias,VrCuota,VrCapital,VrInteres,0
,0,0,VrSaldo,TasaEfeMes,TasaEfeDia,'PORCAUSAR'
 FROM Trn_FinAmtza 
 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  paQryFinCauMoraFmt @pmTipoProc VARCHAR(3),@pmNumProcIni INT,@pmNumProcFin INT,@pmIdCia CHAR(2)
AS
 SELECT  CM.NumProc AS NumCausac,CM.IdCia AS CdCia,Compania,Fecha,Item,TipDoc, IdPrestamo, IdCiaPre, ItemPre,NCuotaLiq, IdCliente,T.RazonSocial AS NomCliente,CM.IdAgencia AS CdAgencia,NomAgencia AS Agencia
,CM.IdVend AS CdVend,V.RazonSocial AS NomVendedor,FecVence,VrTotal, VrAbonado, FecUltLiq,FecIniLiq, FecFinLiq, DiasLiquida, CM.DiasGracia AS Dias_Gracia
, BaseLiquida, TasaEA, TasaEM, TasaDia, VrLiquida, TarifaIva, VrIvaInt, NumNotaDb, NumComp,C.Observacion AS Observ,CdLinCred,C.Anulado AS Anuldo,C.IdUsuario AS CdUsuario,Usuario,TipoCred
 FROM  Trn_FinCauMora  AS CM INNER JOIN Trn_FinCausacion AS C ON CM.TipoProc=C.TipoProc AND CM.NumProc=C.NumProc  AND CM.IdCia=C.IdCia
 INNER JOIN Companias AS CI ON CM.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON  C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS T ON CM.IdCliente=T.IdTercero   INNER JOIN Terceros AS V ON CM.IdVend=V.IdTercero
 LEFT JOIN TercCliePrestamo AS CLI ON CM.IdCliente=CLI.IdClie AND CM.IdAgencia=CLI.IdAgencia
 WHERE CM.TipoProc=@pmTipoProc  AND  CM.NumProc BETWEEN @pmNumProcIni AND @pmNumProcFin AND CM.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 paQryFinCuotas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe
,IdCliente,IdAgencia,TipoCuota,Concepto,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora,FechaCrea,CodLinCred,NumFactura
,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio,NumPreMora
 FROM Trn_FinCuotas
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasFmt @pmTipDoc VARCHAR(3),@pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2) 
 AS 
 SELECT  IdPrestamo,C.IdCia AS CdCia,Compania,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente,RazonSocial,C.IdAgencia AS  CdAgencia,NomAgencia AS Agencia,TipoCuota,Concepto
,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora,C.FechaCrea AS Fecha_crea,CodLinCred,NumFactura
,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,TipDoc,Convenio,NitConvenio,NumPreMora
 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  
 LEFT JOIN TercCliePrestamo AS CLI ON  C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 WHERE TipDoc=@pmTipDoc AND IdPrestamo BETWEEN @pmIdPrestamoIni AND  @pmIdPrestamoFin AND C.IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasLta @pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT C.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,NomAgencia AS Agencia,C.TipDoc AS CdTipDoc,TipoDoc,C.IdPrestamo AS NumDcmto,C.IdCia AS CdCia,Compania
,Item,VrTotal,VrAbonado,VrTotal-VrAbonado AS ValorSaldo,C.Fecha AS FechaEmision,FechaVence,DATEDIFF(day,FechaVence,@pmFecActual) AS DiasMora
,Concepto,NumCuota,Detalle,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,TipoCuota,NumCausacion,IdCiaCau,FecUltPago,FecLiqMora,C.FechaCrea AS Fecha_Crea,CodLinCred
,NumFactura,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio,NumPreMora
--Datos del prestamo
,FecPrestamo,VrPrestamo,NPlazos,Causacion,P.NContrato AS NumContrato,CdCiaCon,NActaJunta,NumCredito,ISNULL(P.IdVend,CLI.IdVend) AS CdVend,ISNULL(VN.RazonSocial,VNC.RazonSocial) AS Vendedor,P.IdLinea AS CdLinea,LinCredito
--Datos del tercero
,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,DP.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,Comentarios
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 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 Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.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  C.IdCliente=CLI.IdClie AND C.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 Terceros AS VNC ON CLI.IdVend=VNC.IdTercero
 INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc  
 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_FinPrestamo AS P ON C.TipDoc=P.TipDoc AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 LEFT JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE  (VrTotal-VrAbonado)>@pmSaldo AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
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,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 paQryFinPrestamoAcum  @pmIdCia CHAR(2)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmTipoCredito VARCHAR(10)=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,FecCorte,DiaCausac,DiaCausac2,P.DiasVence AS DiasVce,P.TipoAprob AS TipAprob,NActaJunta,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,NPlazos,P.TipoPlazo AS TipPlazo,Causacion
,P.IdLinea AS CdLinea,LinCredito,P.IdTasa AS CdTasa,DescTasa,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,NumCuotaIni
,TipoCredito,P.TipoCausac AS Tipo_Causac,P.CalcIntMora AS CalcMora,NitEmpConv,P.CuotasConv AS NCuoConv,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,P.NContrato AS NumContrato,CdCiaCon
,P.TipoGarantia AS TipoGarant,P.DescGarantia AS Desc_Garant,P.VrGarantia AS ValGarant,P.Observacion AS Observ,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,Concepto,P.IdEstado AS CdEstado,Estado
,Anulado,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,CupoCredito,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 INNER JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN TercClieLineas AS LCR ON P.IdCliente=LCR.IdClie AND P.IdAgencia=LCR.IdAgencia AND P.IdLinea=LCR.IdLinea
 WHERE  P.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND P.TipoCredito LIKE ISNULL(@pmTipoCredito,'%')  
 ORDER BY P.IdCia,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 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,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 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,Causacion,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,VrCuota,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
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos del detalle
,Item,NumCuota,PC.Fecha AS FechaCausa,FechaVence,VrTotal,VrAbonado,TipoTasa,PC.DTF_EA AS TasaDTF,TasaNom,PC.Spread_TA AS Puntos,TasaEfe,TipoCuota,PC.Concepto AS ConceptoCuota
,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora,PC.FechaCrea AS Fec_Crea
--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_FinCuotas 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,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,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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQrytm_PreIntMoraLta @pmtmNumero VARCHAR(5)
AS
 SELECT      tmItem, tmTipDoc, tmPrestamo, tmIdCia, tmItemPre,tmNCuota, tmIdCliente,T.RazonSocial AS NomCliente, tmIdAgencia,NomAgencia AS Agencia,tmIdVend,V.RazonSocial AS NomVendedor
, tmFecVence, tmVrTotal, tmVrAbonado, tmFecUltLiq,tmFecIniLiq, tmFecFinLiq, tmDiasLiq, tmDiasGracia, tmBaseLiq
,tmTasaEA, tmTasaEM, tmTasaDia, tmVrLiquida, tmTarifaIva, tmVrIva, tmEstado,tmDetalle,tmLinCred
 FROM  tm_PreIntMora AS IM INNER JOIN Terceros AS T ON IM.tmIdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON IM.tmIdVend=V.IdTercero
 LEFT JOIN TercCliePrestamo AS A ON  IM.tmIdCliente=A.IdClie AND IM.tmIdAgencia=A.IdAgencia
  WHERE tmNumero=@pmtmNumero
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),@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
 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 paUpFinPrestamoMora  @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 UPDATE Trn_FinPrestamo SET Trn_FinPrestamo.AcumIntMora=(SELECT ISNULL(SUM(Trn_FinCuotas.VrTotal),0)
 FROM Trn_FinCuotas  WHERE Trn_FinCuotas.TipDoc='ND2' AND  Trn_FinCuotas.NumPreMora=Trn_FinPrestamo.IdPrestamo
 AND Trn_FinCuotas.IdCiaCau=Trn_FinPrestamo.IdCia AND Concepto='INTMORA')
 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 paUpFinPrestamoTot @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY
,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT,@pmCuotasMora INT
 AS 
 UPDATE Trn_FinPrestamo SET  AcumCapital=@pmAcumCapital,AcumIntereses=@pmAcumIntereses,AcumIntMora=@pmAcumIntMora
,AcumCuotas=@pmAcumCuotas,PagosCapital=@pmPagosCapital,PagosIntereses=@pmPagosIntereses,PagosIntMora=@pmPagosIntMora
,CuotasPagadas=@pmCuotasPagadas,CuotasMora=@pmCuotasMora
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

