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].[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].[paInsFinFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFinFacturas]
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].[paInsTercClieLineas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercClieLineas]
GO

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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
 AS 
 INSERT INTO Trn_FinAmtza (TipDoc,IdPrestamo,IdCia,Item,FecCausac,FecVence,NumDias,VrCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,Causacion,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmFecCausac,@pmFecVence,@pmNumDias,@pmVrCuota,@pmVrCapital,@pmVrInteres,@pmVrSaldo,@pmTasaEfeMes,@pmTasaEfeDia,@pmCausacion
,@pmCdCiaCausac,@pmVrCuoCausac,@pmVrIntCausac,@pmVrSaldoCausac)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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)
 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)
 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)
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
 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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinFacturas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumFactura VARCHAR(20),@pmFecha SMALLDATETIME
,@pmFechaVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrInteres MONEY,@pmDiasLiq DECIMAL(14,4),@pmTasaEfeMes DECIMAL(16,8),@pmTasaEfeDia DECIMAL(16,8),@pmFechaPago SMALLDATETIME,@pmItemPago INT
,@pmFacturado BIT,@pmTipFac VARCHAR(3),@pmFacturaInt INT,@pmFecFactura SMALLDATETIME,@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmVrAbono MONEY
 AS 
 INSERT INTO Trn_FinFacturas (TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago,ItemPago,FecInicio,FecFinal,VrAbono,Facturado,TipFac,FacturaInt,FecFactura)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumFactura,@pmFecha,@pmFechaVence,@pmVrFactura,@pmVrInteres,@pmDiasLiq
,@pmTasaEfeMes,@pmTasaEfeDia,@pmFechaPago,@pmItemPago,@pmFecInicio,@pmFecFinal,@pmVrAbono,@pmFacturado,@pmTipFac,@pmFacturaInt,@pmFecFactura)
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
,@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,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,@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 paInsTercClieLineas @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmIdTasa VARCHAR(4),@pmnPlazo INT,@pmTipoPlazo CHAR(1),@pmCupoCredito MONEY,@pmSaldoCapital MONEY,@pmSaldoInteres MONEY,@pmMaxCupoSmlv DECIMAL(14,4),@pmTipoCausac VARCHAR(10),@pmDiasVence VARCHAR(20)
,@pmFecPreAprob SMALLDATETIME,@pmPlazoAprob INT,@pmEtdoAprob INT,@pmTipoAprob VARCHAR(20),@pmDescAprob VARCHAR(250),@pmGarantias BIT,@pmTipoGarantia VARCHAR(10),@pmDescGarantia VARCHAR(250),@pmVrGarantia MONEY,@pmReqCheques BIT,@pmFecActCupo SMALLDATETIME,@pmEstadoAct INT
,@pmLiqSeguro BIT,@pmCdTasaConv VARCHAR(4),@pmNitConvenio VARCHAR(16),@pmCuotasConv INT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercClieLineas (IdClie,IdAgencia,IdLinea,IdTasa,nPlazo,TipoPlazo,CupoCredito,SaldoCapital,SaldoInteres,MaxCupoSmlv,TipoCausac,DiasVence,LiqSeguro,FecPreAprob,PlazoAprob,EtdoAprob,TipoAprob,DescAprob,Garantias,TipoGarantia,DescGarantia,VrGarantia,ReqCheques,FecActCupo,CdTasaConv,NitConvenio,CuotasConv,EstadoAct,FechaCrea,IdUsuario)
 VALUES (@pmIdClie,@pmIdAgencia,@pmIdLinea,@pmIdTasa,@pmnPlazo,@pmTipoPlazo,@pmCupoCredito,@pmSaldoCapital,@pmSaldoInteres,@pmMaxCupoSmlv,@pmTipoCausac,@pmDiasVence,@pmLiqSeguro,@pmFecPreAprob,@pmPlazoAprob,@pmEtdoAprob,@pmTipoAprob,@pmDescAprob,@pmGarantias,@pmTipoGarantia,@pmDescGarantia,@pmVrGarantia
,@pmReqCheques,@pmFecActCupo,@pmCdTasaConv,@pmNitConvenio,@pmCuotasConv,@pmEstadoAct,@pmFechaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreCartera @pmtmEst CHAR(2),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumCuota INT,@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrTotal MONEY,@pmVrAbonado MONEY,@pmVrSaldo 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),@pmIdVend VARCHAR(16),@pmConcepto VARCHAR(10)
,@pmTipoCuota INT,@pmNumCausacion INT,@pmIdCiaCau CHAR(2),@pmDetalle VARCHAR(250),@pmIdConcepto VARCHAR(4),@pmFecUltPago SMALLDATETIME,@pmFecLiqMora SMALLDATETIME,@pmFechaCrea SMALLDATETIME,@pmCtaForma VARCHAR(30)
,@pmBeneficiario VARCHAR(150),@pmRefCheque VARCHAR(50),@pmNumCheque VARCHAR(20),@pmCdBanco VARCHAR(4)
 AS 
 INSERT INTO tm_PreCartera (tmEst,TipDoc,IdPrestamo,IdCia,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado,VrSaldo,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente,IdAgencia,IdVend,Concepto,TipoCuota,NumCausacion,IdCiaCau,Detalle,IdConcepto,FecUltPago
,FecLiqMora,FechaCrea,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco)
 VALUES (@pmtmEst,@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumCuota,@pmFecha,@pmFechaVence,@pmVrTotal,@pmVrAbonado,@pmVrSaldo,@pmTipoTasa,@pmDTF_EA,@pmTasaNom,@pmSpread_TA,@pmTasaEfe,@pmIdCliente,@pmIdAgencia
,@pmIdVend,@pmConcepto,@pmTipoCuota,@pmNumCausacion,@pmIdCiaCau,@pmDetalle,@pmIdConcepto,@pmFecUltPago,@pmFecLiqMora,@pmFechaCrea,@pmCtaForma,@pmBeneficiario,@pmRefCheque,@pmNumCheque,@pmCdBanco)
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)
 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)
 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)
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)
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
 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)
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,'0'
 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)
 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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreMovi @pmtmEst CHAR(2),@pmtmTipDoc VARCHAR(3),@pmtmNumero INT,@pmtmIdCia CHAR(2),@pmtmItem INT,@pmtmFecha SMALLDATETIME,@pmtmIdCliente VARCHAR(16),@pmtmIdAgencia VARCHAR(16),@pmtmIdVend VARCHAR(16),@pmtmCargos MONEY,@pmtmAbonos MONEY,@pmtmNumCuota INT,@pmtmTimeSys SMALLDATETIME,@pmtmFecVence SMALLDATETIME,@pmtmCdConcepto VARCHAR(4),@pmtmConcepto VARCHAR(10)
,@pmtmDetalle VARCHAR(250),@pmtmReferencia VARCHAR(50),@pmtmTipoTasa VARCHAR(10),@pmtmDTF_EA DECIMAL(16,8),@pmtmTasaNom DECIMAL(16,8),@pmtmPuntos DECIMAL(16,8),@pmtmTasaEfe DECIMAL(16,8),@pmtmTipRef VARCHAR(3),@pmtmDocRef INT,@pmtmIdCiaRef CHAR(2),@pmtmNumForma VARCHAR(20),@pmtmCdBanco VARCHAR(4),@pmtmBeneficiario VARCHAR(150),@pmtmCdForma VARCHAR(4),@pmtmEnEfectivo BIT,@pmtmTotalAbono MONEY
,@pmtmVrAfavor MONEY,@pmtmTipCom VARCHAR(3),@pmtmComprobante INT,@pmtmIdCiaCom CHAR(2),@pmtmAnulado BIT,@pmtmNumDev INT,@pmtmFecDev SMALLDATETIME,@pmtmIdUsuario VARCHAR(11),@pmtmSanClie MONEY,@pmtmSanClieCia MONEY,@pmtmSanAge MONEY,@pmtmSanAgeCia MONEY
 AS 
 INSERT INTO tm_PreMovi (tmEst,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmIdVend,tmCargos,tmAbonos,tmNumCuota,tmTimeSys,tmFecVence,tmCdConcepto,tmConcepto,tmDetalle,tmReferencia,tmTipoTasa,tmDTF_EA,tmTasaNom,tmPuntos,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmNumForma,tmCdBanco,tmBeneficiario,tmCdForma,tmEnEfectivo,tmTotalAbono,tmVrAfavor,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario
,tmSanClie,tmSanClieCia,tmSanAge,tmSanAgeCia)
 VALUES (@pmtmEst,@pmtmTipDoc,@pmtmNumero,@pmtmIdCia,@pmtmItem,@pmtmFecha,@pmtmIdCliente,@pmtmIdAgencia,@pmtmIdVend,@pmtmCargos,@pmtmAbonos,@pmtmNumCuota,@pmtmTimeSys,@pmtmFecVence,@pmtmCdConcepto,@pmtmConcepto,@pmtmDetalle,@pmtmReferencia,@pmtmTipoTasa,@pmtmDTF_EA,@pmtmTasaNom,@pmtmPuntos,@pmtmTasaEfe,@pmtmTipRef,@pmtmDocRef,@pmtmIdCiaRef,@pmtmNumForma,@pmtmCdBanco
,@pmtmBeneficiario,@pmtmCdForma,@pmtmEnEfectivo,@pmtmTotalAbono,@pmtmVrAfavor,@pmtmTipCom,@pmtmComprobante,@pmtmIdCiaCom,@pmtmAnulado,@pmtmNumDev,@pmtmFecDev,@pmtmIdUsuario,@pmtmSanClie,@pmtmSanClieCia,@pmtmSanAge,@pmtmSanAgeCia)

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
 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
 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
--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 OFF 
GO

CREATE PROCEDURE paQryFinFacturas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago
,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,FecFactura
 FROM Trn_FinFacturas
 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 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,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,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
,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,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
,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 ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTercClieLineas @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4)
 AS 
 SELECT IdClie,IdAgencia,IdLinea,IdTasa,nPlazo,TipoPlazo,CupoCredito,SaldoCapital,SaldoInteres,MaxCupoSmlv,TipoCausac,DiasVence,LiqSeguro,FecPreAprob
,PlazoAprob,EtdoAprob,TipoAprob,DescAprob,Garantias,TipoGarantia,DescGarantia,VrGarantia,ReqCheques,FecActCupo
,CdTasaConv,NitConvenio,CuotasConv,EstadoAct,FechaCrea,FechaAct,IdUsuario
 FROM TercClieLineas
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia AND IdLinea=@pmIdLinea
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercClieLineasLta @pmIdClie VARCHAR(16)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdTipoTerc  VARCHAR(4)=Null
 AS 
 SELECT LC.IdClie AS NitCliente,RazonSocial,LC.IdAgencia AS CdAgencia,NomAgencia,LC.IdLinea AS CdLinea,LinCredito,LC.IdTasa AS CdTasa,DescTasa,TasaEfectiva,nPlazo,TipoPlazo,CupoCredito
,SaldoCapital,SaldoInteres,MaxCupoSmlv,TipoCausac,DiasVence,FecPreAprob,PlazoAprob,EtdoAprob,TipoAprob,DescAprob,Garantias,TipoGarantia,DescGarantia,VrGarantia
,ReqCheques,FecActCupo,EstadoAct,LiqSeguro,CdTasaConv,NitConvenio,CuotasConv,LC.FechaCrea AS FecCrea,LC.FechaAct AS FecActualiza,LC.IdUsuario AS CdUsuario,Usuario
--datos del cliente
,TipoId,Dv,C.IdTipoTerc AS CdTipoTerc,TipoTercero,C.IdEstado AS CdEstado,Estado,FecIngreso,VrSalBasico,MaxDcto,Comentarios
 FROM TercClieLineas AS LC  INNER JOIN LineasCred AS L ON LC.IdLinea=L.IdLinea
 INNER JOIN  TercCliePrestamo AS C ON LC.IdClie=C.IdClie AND LC.IdAgencia=C.IdAgencia  INNER JOIN Terceros AS T ON LC.IdClie=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON LC.IdUsuario=U.IdUsuario
 INNER JOIN TiposTerc AS TC ON C.IdTipoTerc=TC.IdTipoTerc  INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado
 LEFT JOIN TasasInteres AS TI ON LC.IdTasa=TI.IdTasa
 WHERE C.Inactivo=0 AND LC.IdClie LIKE ISNULL(@pmIdClie,'%') AND LC.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND  C.IdTipoTerc  LIKE ISNULL(@pmIdTipoTerc ,'%')
 ORDER BY RazonSocial,LC.IdLinea
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  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,Agencia
,tmIdVend,V.RazonSocial AS NomVendedor,tmEstado, tmDetalle,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr, tmTotAbono,tmVrSaldo
,tmUltCausac,tmVrAcumCap,tmNitConv
--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 Agencias  AS A ON PC.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,@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
 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 paUpTercClieLineas @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmIdTasa VARCHAR(4),@pmnPlazo INT,@pmTipoPlazo CHAR(1),@pmCupoCredito MONEY,@pmSaldoCapital MONEY,@pmSaldoInteres MONEY,@pmMaxCupoSmlv DECIMAL(14,4),@pmTipoCausac VARCHAR(10),@pmDiasVence VARCHAR(20)
,@pmFecPreAprob SMALLDATETIME,@pmPlazoAprob INT,@pmEtdoAprob INT,@pmTipoAprob VARCHAR(20),@pmDescAprob VARCHAR(250),@pmGarantias BIT,@pmTipoGarantia VARCHAR(10),@pmDescGarantia VARCHAR(250),@pmVrGarantia MONEY,@pmReqCheques BIT,@pmFecActCupo SMALLDATETIME,@pmEstadoAct INT,@pmLiqSeguro BIT
,@pmCdTasaConv VARCHAR(4),@pmNitConvenio VARCHAR(16),@pmCuotasConv INT,@pmFechaAct SMALLDATETIME
 AS 
 UPDATE TercClieLineas SET IdTasa=@pmIdTasa,nPlazo=@pmnPlazo,TipoPlazo=@pmTipoPlazo,CupoCredito=@pmCupoCredito,SaldoCapital=@pmSaldoCapital,SaldoInteres=@pmSaldoInteres,MaxCupoSmlv=@pmMaxCupoSmlv,TipoCausac=@pmTipoCausac,DiasVence=@pmDiasVence,FecPreAprob=@pmFecPreAprob,PlazoAprob=@pmPlazoAprob,EtdoAprob=@pmEtdoAprob
,TipoAprob=@pmTipoAprob,DescAprob=@pmDescAprob,Garantias=@pmGarantias,TipoGarantia=@pmTipoGarantia,DescGarantia=@pmDescGarantia,VrGarantia=@pmVrGarantia,ReqCheques=@pmReqCheques,FecActCupo=@pmFecActCupo,EstadoAct=@pmEstadoAct,LiqSeguro=@pmLiqSeguro,CdTasaConv=@pmCdTasaConv,NitConvenio=@pmNitConvenio,CuotasConv=@pmCuotasConv,FechaAct=@pmFechaAct
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia AND IdLinea=@pmIdLinea
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--Jul 28/2010

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsRegistro @pmIdCia CHAR(2),@pmIdEmpresa VARCHAR(16),@pmNitEmpresa VARCHAR(16),@pmDvNit CHAR(1),@pmRazonSocial VARCHAR(250)
,@pmDescripcion VARCHAR(250),@pmDireccion VARCHAR(250),@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmIdLocal VARCHAR(8),@pmIdPais VARCHAR(8)
,@pme_mail VARCHAR(100),@pmArchivoLogo VARCHAR(30),@pmNumeroSerial VARCHAR(80),@pmRegimen VARCHAR(50),@pmLicencia VARCHAR(20),@pmVigenciaLic SMALLDATETIME
,@pmPersoneria VARCHAR(40),@pmLeyenda VARCHAR(250),@pmFechaRegistro SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmCurrtyDate SMALLDATETIME
,@pmTipoId CHAR(1),@pmNitRepLegal VARCHAR(16),@pmRepLegal VARCHAR(150),@pmTipoPersna CHAR(1),@pmCIIUDane VARCHAR(8),@pmCurDate1 SMALLDATETIME,@pmFechaPrest SMALLDATETIME
 AS 
 INSERT INTO adm_Registro (IdCia,IdEmpresa,NitEmpresa,DvNit,RazonSocial,Descripcion,Direccion,Telefono,Fax,IdLocal,IdPais,e_mail,ArchivoLogo,NumeroSerial,Regimen,Licencia,VigenciaLic
,Personeria,Leyenda,FechaRegistro,FechaVence,CurrtyDate,TipoId,NitRepLegal,RepLegal,TipoPersna,CIIUDane,CurDate1,FechaPrest)
 VALUES (@pmIdCia,@pmIdEmpresa,@pmNitEmpresa,@pmDvNit,@pmRazonSocial,@pmDescripcion,@pmDireccion,@pmTelefono,@pmFax,@pmIdLocal,@pmIdPais,@pme_mail,@pmArchivoLogo
,@pmNumeroSerial,@pmRegimen,@pmLicencia,@pmVigenciaLic,@pmPersoneria,@pmLeyenda,@pmFechaRegistro,@pmFechaVence,@pmCurrtyDate,@pmTipoId,@pmNitRepLegal,@pmRepLegal,@pmTipoPersna,@pmCIIUDane,@pmCurDate1,@pmFechaPrest)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryRegistro @pmIdCia CHAR(2)=Null
 AS 
 SELECT IdCia,IdEmpresa,NitEmpresa,DvNit,RazonSocial,Descripcion,Direccion,Telefono,Fax,R.IdLocal AS CodCiu
,R.IdPais AS IdPai,e_mail,ArchivoLogo,NumeroSerial,Regimen,Licencia,VigenciaLic,Personeria,Leyenda,FechaRegistro,FechaVence,CurrtyDate
,CodISO,NombrePais,P.IdMneda AS CodMnda,Mneda,Simbolo,MneLocal,VrCambio,Localidad,TipoId,NitRepLegal
,RepLegal,TipoPersna,CIIUDane,CurDate1,FechaPrest
 FROM adm_Registro AS R INNER JOIN adm_Paises AS P ON R.IdPais=P.IdPais 
 INNER JOIN adm_monedas AS M ON P.IdMneda=M.IdMneda
 INNER JOIN Localidades AS  L ON R.IdLocal=L.IdLocal
 WHERE IdCia  LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpRegistro @pmIdCia CHAR(2),@pmIdEmpresa VARCHAR(16),@pmNitEmpresa VARCHAR(16),@pmDvNit CHAR(1),@pmRazonSocial VARCHAR(250),@pmDescripcion VARCHAR(250)
,@pmDireccion VARCHAR(250),@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmIdLocal VARCHAR(8),@pmIdPais VARCHAR(8),@pme_mail VARCHAR(100),@pmArchivoLogo VARCHAR(30),@pmNumeroSerial VARCHAR(80)
,@pmRegimen VARCHAR(50),@pmLicencia VARCHAR(20),@pmVigenciaLic SMALLDATETIME,@pmPersoneria VARCHAR(40),@pmLeyenda VARCHAR(250),@pmFechaRegistro SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmCurrtyDate SMALLDATETIME
,@pmTipoId CHAR(1),@pmNitRepLegal VARCHAR(16),@pmRepLegal VARCHAR(150),@pmTipoPersna CHAR(1),@pmCIIUDane VARCHAR(8),@pmCurDate1 SMALLDATETIME,@pmFechaPrest SMALLDATETIME
 AS 
 UPDATE adm_Registro SET IdEmpresa=@pmIdEmpresa,NitEmpresa=@pmNitEmpresa,DvNit=@pmDvNit,RazonSocial=@pmRazonSocial,Descripcion=@pmDescripcion,Direccion=@pmDireccion,Telefono=@pmTelefono
,Fax=@pmFax,IdLocal=@pmIdLocal,IdPais=@pmIdPais,e_mail=@pme_mail,ArchivoLogo=@pmArchivoLogo,NumeroSerial=@pmNumeroSerial,Regimen=@pmRegimen,Licencia=@pmLicencia,VigenciaLic=@pmVigenciaLic
,Personeria=@pmPersoneria,Leyenda=@pmLeyenda,FechaRegistro=@pmFechaRegistro,FechaVence=@pmFechaVence,CurrtyDate=@pmCurrtyDate
,TipoId=@pmTipoId,NitRepLegal=@pmNitRepLegal,RepLegal=@pmRepLegal,TipoPersna=@pmTipoPersna ,CIIUDane=@pmCIIUDane,CurDate1=@pmCurDate1,FechaPrest=@pmFechaPrest
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


