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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAcuPrestamos @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmSaldoAnt MONEY
,@pmPrestamos MONEY,@pmDevPrestamos MONEY,@pmRecibos MONEY,@pmDevRecibos MONEY,@pmNotasDeb MONEY,@pmNotasCre MONEY
,@pmFacturas MONEY,@pmDevFacturas MONEY
 AS 
 INSERT INTO AcuPrestamos (nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas)
 VALUES (@pmnAnno,@pmnMes,@pmIdCia,@pmIdCliente,@pmIdAgencia,@pmSaldoAnt,@pmPrestamos,@pmDevPrestamos,@pmRecibos,@pmDevRecibos,@pmNotasDeb,@pmNotasCre,@pmFacturas,@pmDevFacturas)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAcuPrestamosCau  @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 INSERT INTO AcuPrestamos (nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas)
 SELECT YEAR(Fecha),MONTH(Fecha),IdCia,IdCliente,IdAgencia,0,SUM(CASE TipDoc WHEN 'PR1' THEN VrTotal ELSE 0 END),0,0,0
 ,SUM(CASE TipDoc WHEN 'ND2' THEN VrTotal ELSE 0 END),0,SUM(CASE TipDoc WHEN 'FIF' THEN VrTotal ELSE 0 END),0
 FROM Trn_FinCuotas
 WHERE TipDoc IN ('PR1','ND2','FIF')  AND YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 GROUP BY YEAR(Fecha),MONTH(Fecha),IdCia,IdCliente,IdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinCausacion @pmTipoProc VARCHAR(3),@pmNumProc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTotalCapital MONEY
,@pmTotalInteres MONEY,@pmTotalIntMora MONEY,@pmTipoCred VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmAnulado BIT,@pmObservacion VARCHAR(250)
,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinCausacion (TipoProc,NumProc,IdCia,Fecha,TotalCapital,TotalInteres,TotalIntMora,TipoCred,TipCom,Comprobante,Anulado,Observacion,TimeSys,IdUsuario)
 VALUES (@pmTipoProc,@pmNumProc,@pmIdCia,@pmFecha,@pmTotalCapital,@pmTotalInteres,@pmTotalIntMora,@pmTipoCred,@pmTipCom,@pmComprobante,@pmAnulado
,@pmObservacion,@pmTimeSys,@pmIdUsuario)
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,@pmCdCiaFac CHAR(2),@pmFechaLiq SMALLDATETIME
 AS 
 INSERT INTO Trn_FinFacturas (TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago,ItemPago,FechaLiq,FecInicio,FecFinal,VrAbono,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumFactura,@pmFecha,@pmFechaVence,@pmVrFactura,@pmVrInteres,@pmDiasLiq
,@pmTasaEfeMes,@pmTasaEfeDia,@pmFechaPago,@pmItemPago,@pmFechaLiq,@pmFecInicio,@pmFecFinal,@pmVrAbono,@pmFacturado,@pmTipFac,@pmFacturaInt,@pmCdCiaFac,@pmFecFactura)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinPagos @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumero VARCHAR(20),@pmFecha SMALLDATETIME,@pmFecPago SMALLDATETIME,@pmVrCuota MONEY
,@pmEdoForma INT,@pmCdBanco VARCHAR(4),@pmCtaCheque VARCHAR(30),@pmDetalle VARCHAR(150),@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmVrLiqMora MONEY,@pmFecLiqMora SMALLDATETIME
 AS 
 INSERT INTO Trn_FinPagos (TipDoc,IdPrestamo,IdCia,Item,Numero,Fecha,FecPago,VrCuota,EdoForma,CdBanco,CtaCheque,Detalle,TipRec,Recibo,IdCiaRec,VrLiqMora,FecLiqMora)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumero,@pmFecha,@pmFecPago,@pmVrCuota,@pmEdoForma,@pmCdBanco,@pmCtaCheque
,@pmDetalle,@pmTipRec,@pmRecibo,@pmIdCiaRec,@pmVrLiqMora,@pmFecLiqMora)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFinRecibos @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFecPago SMALLDATETIME,@pmVrRecibido MONEY,@pmVrSubTotal MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrOtros MONEY,@pmVrOtrDcto MONEY
,@pmVrPagosMas MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmVrEfectivo MONEY,@pmVrAnticipo MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4)
,@pmCodTarCom VARCHAR(4),@pmEnEfectivo BIT,@pmCdForma VARCHAR(4),@pmNumForma VARCHAR(20),@pmDetallePago VARCHAR(250),@pmCdBanco VARCHAR(4),@pmCdCta VARCHAR(4),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoCredito VARCHAR(10),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDcm CHAR(2),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinRecibos (TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrEfectivo,VrAnticipo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdVend,TarifaCom,CodTarCom,EnEfectivo,CdForma,NumForma,DetallePago,CdBanco,CdCta,Referencia,pVehiculo,CdConductor
,TipoCredito,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmRecibo,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFecPago,@pmVrRecibido,@pmVrSubTotal,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrOtros,@pmVrOtrDcto,@pmVrPagosMas,@pmVrNeto,@pmVrAplicado,@pmVrEfectivo,@pmVrAnticipo,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarRet,@pmCodTarIca
,@pmCodTarRiv,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmEnEfectivo,@pmCdForma,@pmNumForma,@pmDetallePago,@pmCdBanco,@pmCdCta,@pmReferencia,@pmpVehiculo,@pmCdConductor,@pmTipoCredito,@pmTipDcm,@pmDocumento,@pmIdCiaDcm,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreCartera_Fac @pmtmEst CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmIdVend VARCHAR(16)=Null
 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)
 SELECT @pmtmEst,C.TipDoc,C.IdPrestamo,C.IdCia,Item,NumCuota,C.Fecha,C.FechaVence,VrTotal,VrAbonado,VrTotal-VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe
,C.IdCliente,C.IdAgencia,IdVend,Concepto,TipoCuota,NumCausacion,IdCiaCau,Detalle,IdConcepto,FecUltPago,FecLiqMora,FechaCrea,'EFE','','FACTURAS','0','0'
 FROM Trn_FinCuotas  AS C INNER JOIN  Trn_FinFactint  AS F ON C.TipDoc=F.TipDoc  AND C.IdPrestamo=F.Factura AND C.IdCia=F.IdCia
 WHERE C.TipDoc='FIF' AND VrTotal>VrAbonado AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')   AND F.IdVend LIKE ISNULL(@pmIdVend,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paInstm_PreMovi_Dfa  @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 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)
 SELECT @pmtmEst,TipDev,Devolucion,IdCia,0,Fecha,IdCliente,IdAgencia,IdVend,0,VrNeto,0,TimeSys,Fecha,IdConcepto,'',Observacion,'','0',0,0,0,0,TipDoc,Factura,IdCiaDoc
,'','0','','',0,0,0,TipCom,Comprobante,IdCiaCom,0,0,Fecha,IdUsuario,0,0,0,0
 FROM Trn_FinDevFac
  WHERE Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paInstm_PreMovi_Fac  @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 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)
 SELECT @pmtmEst,C.TipDoc,C.IdPrestamo,C.IdCia,Item,C.Fecha,C.IdCliente,C.IdAgencia,IdVend,VrTotal,0,NumCuota,TimeSys,C.FechaVence,IdConcepto,Concepto,Detalle,'FACTURAS'
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,'CAU',NumCausacion,IdCiaCau,'0','0','0','0',0,VrAbonado,0,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,IdUsuario,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinFactint  AS F ON C.TipDoc=F.TipDoc  AND C.IdPrestamo=F.Factura  AND C.IdCia=F.IdCia
 WHERE C.TipDoc='FIF' AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAcuPrestamos @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)
,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
 AS 
 SELECT nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos
,NotasDeb,NotasCre,Facturas,DevFacturas
 FROM AcuPrestamos
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCia=@pmIdCia 
AND IdCliente=@pmIdCliente AND IdAgencia=@pmIdAgencia

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, 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCausacionFmt @pmTipoProc VARCHAR(3),@pmNumProcIni INT,@pmNumProcFin INT,@pmIdCia CHAR(2) 
 AS 
 SELECT  NumProc,FC.IdCia AS CdCia,Compania,FC.Fecha AS FecCausac,FC.TipCom AS TipComp,FC.Comprobante AS NumComp,FC.Anulado AS Anuldo
 ,FC.Observacion AS CauObserv,C.TipDoc AS Tip_Doc,C.IdPrestamo AS NumPstmo,Item,NumCuota,FechaVence
,VrTotal,VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,C.IdCliente AS NitCliente,RazonSocial,C.IdAgencia AS  CdAgencia,NomAgencia AS Agencia
,TipoCuota,Concepto,Detalle,FecUltPago,FecLiqMora,C.FechaCrea AS CuoFecCrea,FC.TimeSys AS CauFechaCrea,FC.IdUsuario AS CdUsuario,Usuario
--datos del prestamo
,FecPrestamo,FecCorte,NPlazos,Causacion,VrPrestamo,NumCredito,P.Observacion AS Observ,TipoCred
 FROM  Trn_FinCausacion AS FC INNER JOIN Trn_FinCuotas AS C ON FC.NumProc=C.NumCausacion AND FC.IdCia=C.IdCiaCau
 INNER JOIN Companias AS CI ON FC.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON FC.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero   
 INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 LEFT JOIN TercCliePrestamo AS CLI ON  C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 WHERE TipoProc=@pmTipoProc AND  NumProc BETWEEN @pmNumProcIni AND  @pmNumProcFin AND FC.IdCia=@pmIdCia
 AND C.TipDoc='PR1'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinDevFacCr @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS CdAgencia
,NomAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCargos,VrOtrDcto,VrNeto,D.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,D.IdLocal AS CdLocal
,LD.Localidad AS CiudadFact,ModdDev,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,TipDev
--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,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,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM  Trn_FinDevFac  AS D  INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado  INNER JOIN Localidades AS LD ON D.IdLocal=LD.IdLocal 
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON D.IdVend=VN.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 D.IdCliente=CLI.IdClie AND D.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 TiposCom AS TC ON D.TipCom=TC.IdCom
 WHERE  D.TipDev=@pmTipDev  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY D.IdCia,Factura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinDevPreCr @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT  Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipDocPre,D.IdPrestamo AS NumPrestamo,IdCiaDoc,FecDoc,FecPrestamo,FecCorte
,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS CdAgencia,NomAgencia AS Agencia,D.VrPrestamo AS ValorDev,D.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,NumCuotaIni,AcumCapital,AcumCuotas
,NPlazos,TipoPlazo,Causacion,NActaJunta,NumCredito,P.NContrato AS NumContrato,CdCiaCon,D.CxPagar AS Cx_pagar,ModdDev,D.TipCom AS Tip_Comp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp
,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito,TipoCredito
,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.OrigenAdd AS Origen_Crea,D.TimeSys AS FechaCrea,D.FecUpdate AS FechaAct,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
--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,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,EsExento,FecIngreso,VrSalBasico,DirAgencia
,CLI.IdLocalAge  AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinDevPre AS D INNER JOIN Trn_FinPrestamo AS P ON D.TipDoc=P.TipDoc AND  D.IdPrestamo=P.IdPrestamo  AND D.IdCiaDoc=P.IdCia
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia  INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto  
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON D.IdVend=VN.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 D.IdCliente=CLI.IdClie AND D.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 TiposCom AS TC ON D.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 LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE TipDev=@pmTipDev   AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY  D.IdCia,Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinDevRecCr @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null
 AS 
 SELECT  Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Recibo,IdCiaDoc,FecDoc,FecPago,IdCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrAnticipo,VrEfectivo,BaseRet,BaseIca,BaseRiv
,D.IdVend AS CdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,EnEfectivo,D.Referencia AS Refrencia,NumAutoriza,ModdDev,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ
,D.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--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,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
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM Trn_FinDevRec AS D  INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 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 D.IdCliente=CLI.IdClie AND D.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 TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE TipDev=@pmTipDev  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 ORDER BY D.IdCia,Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinFactintCr @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT  Factura,F.IdCia AS CdCia,Compania,Fecha,F.IdConcepto AS CdConcepto,Concepto,F.IdCliente AS CdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS CdAgencia,NomAgencia,FechaVence,VrSubTotal,VrDescuento
,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCargos,VrOtrDcto,VrNeto,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv
,F.IdCCosto AS CdCCosto,CCosto,F.IdSubCos AS CdSubCos,SubCosto,F.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,TarifaCom,CodTarCom,F.IdLocal AS CdLocalFact,FL.Localidad AS CiudadFact,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto
,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,TimeSys,F.FecUpdate AS FechaAct,IdCiaCrea,F.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_FinFactint AS F  INNER JOIN Companias AS CI ON F.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON F.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 Localidades AS FL ON F.IdLocal=FL.IdLocal 
 INNER JOIN  TercCliePrestamo AS CLI ON F.IdCliente=CLI.IdClie AND F.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 CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos
 LEFT JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo LEFT JOIN TiposCom AS TC ON F.TipCom=TC.IdCom
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND F.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY F.IdCia,Factura

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
,FechaLiq,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,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 paQryFinRecibos @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrEfectivo
,VrAnticipo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdVend,TarifaCom,CodTarCom,EnEfectivo,CdForma,NumForma,DetallePago,CdBanco
,CdCta,Referencia,pVehiculo,CdConductor,TipoCredito,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FinRecibos
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo 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 paQryFinRecibosCr @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null 
 AS 
 SELECT  Recibo,R.IdCia AS CdCia,Compania,Fecha,R.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,NomAgencia AS Agencia,FecPago
,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrEfectivo,VrAnticipo,BaseRet,BaseIca,BaseRiv
,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,R.IdVend AS CdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,EnEfectivo,CdForma,NumForma
,DetallePago,CdBanco,Banco,CdCta,R.Referencia AS Refrencia,TipoCredito,pVehiculo,CdConductor,CD.RazonSocial AS Conductor,TipDcm,Documento,IdCiaDcm
,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario,TipDoc
--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,Comentarios
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinRecibos AS R  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 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 R.IdCliente=CLI.IdClie AND R.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 TiposCom AS TCM ON R.TipCom=TCM.IdCom  
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Bancos AS B ON R.CdBanco=B.IdBanco  LEFT JOIN Terceros AS CD ON R.CdConductor=CD.IdTercero
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND R.IdVend LIKE ISNULL(@pmIdVend,'%')  AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 ORDER BY R.IdCia,Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinRecibosDoc  @pmDocumento INT,@pmIdCiaDcm CHAR(2),@pmTipDcm VARCHAR(3)=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrEfectivo
,VrAnticipo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdVend,TarifaCom,CodTarCom,EnEfectivo,CdForma,NumForma,DetallePago,CdBanco
,CdCta,Referencia,pVehiculo,CdConductor,TipoCredito,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FinRecibos
 WHERE Documento=@pmDocumento AND IdCiaDcm=@pmIdCiaDcm AND TipDcm LIKE ISNULL(@pmTipDcm,'%')
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 ORDER BY IdCia,Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinRecibosFmt @pmTipDoc VARCHAR(3),@pmReciboIni INT, @pmReciboFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  Recibo,R.IdCia AS CdCia,Compania,R.Fecha AS FecRecibo,R.IdConcepto AS CdConcepto,Concepto,R.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,NomAgencia AS NomAgencia
,FecPago,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrEfectivo,VrAnticipo
,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,R.IdVend AS CdVend,V.RazonSocial AS Vendedor
,TarifaCom,CodTarCom,EnEfectivo,CdForma,R.NumForma AS RecNumForma,DetallePago,CdBanco,BR.Banco AS RecBanco,R.CdCta AS RecCodCta,R.Referencia AS RecReferncia,pVehiculo,CdConductor
,TipoCredito,TipDcm,R.Documento AS NumDcmto,IdCiaDcm,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
--datos de forma de pago
,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,EsCaja,P.IdBanco AS PagCdBanco,BF.Banco AS PagBanco
,P.NumForma AS PagNumForma,FecForma,CtaForma,Beneficiario,Referncia1,P.Referncia2 AS PagReferencia2
,CdLocal,LF.Localidad AS CiudadPlaza,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef
--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,Comentarios
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinRecibos AS R  INNER JOIN Trn_Pagos AS P ON R.TipDoc=P.TipDoc AND R.Recibo=P.Documento AND R.IdCia=P.IdCia
 INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario   INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS BF ON P.IdBanco=BF.IdBanco
 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 R.IdCliente=CLI.IdClie AND R.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 TiposCom AS TCM ON R.TipCom=TCM.IdCom  LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal 
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE R.TipDoc=@pmTipDoc  AND Recibo BETWEEN @pmReciboIni AND @pmReciboFin AND R.IdCia=@pmIdCia
 ORDER BY  R.Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinRecibosLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT  Recibo,R.IdCia AS CdCia,Compania,Fecha,R.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrRetencion,VrReteICA,VrReteIVA
,VrOtros,VrOtrDcto,VrPagosMas,VrNeto,VrAplicado,VrEfectivo,VrAnticipo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdVend,V.RazonSocial AS Vendedor
,TarifaCom,CodTarCom,EnEfectivo,CdForma,NumForma,DetallePago,CdBanco
,CdCta,Referencia,pVehiculo,CdConductor,TipoCredito,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
 FROM Trn_FinRecibos AS R  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') 
 ORDER BY R.IdCia,Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_PagosDso @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdForma,FormaPago,tmVrPagado,tmNumForma,tmIdBanco,Banco,tmCtaForma,tmBeneficiario,tmFecForma
,tmCdLocal,tmDetalle,tmReferncia1,tmReferncia2,tmNumAutoriza,tmCdCta,tmVrDenom,tmCantidad
,tmVrDenom*tmCantidad AS TotalDenm,tmTipDenom,tmEsCaja,tmIdCia,tmTipRef,tmDocRef,tmIdCiaRef,tmItemDoc,tmNumero
 FROM tm_Pagos AS P INNER JOIN Formaspago AS F ON P.tmIdForma=F.IdForma
 LEFT JOIN Bancos AS B ON P.tmIdBanco=B.IdBanco
 WHERE tmNumero=@pmtmNumero
 ORDER BY  tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_PreCartera @pmtmEst CHAR(2),@pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME
 AS 
 SELECT T.RazonSocial AS NomCliente,C.IdCliente AS NitCliente,NomAgencia AS Agencia,C.IdAgencia AS CdAgencia,C.TipDoc AS CdTipoDoc,TipoDoc,IdPrestamo,C.IdCia AS CdCia,Compania,Item
,VrTotal,VrAbonado,CASE C.TipDoc WHEN 'SAF' THEN C.VrSaldo*-1  ELSE  C.VrSaldo END AS ValorSaldo,Fecha,FechaVence,DATEDIFF(day,FechaVence,@pmFecActual) AS DiasMora
,C.Concepto AS ConceptoCuota,NumCuota,Detalle,C.IdConcepto AS CdConcepto,CO.Concepto AS NomConcepto,C.IdVend AS CdVend,VN.RazonSocial AS Vendedor,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,TipoCuota
,NumCausacion,IdCiaCau,FecUltPago,FecLiqMora,C.FechaCrea,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco
--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,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.IdVend AS CdVendClie,VNC.RazonSocial AS NomVendClie
,DirAgencia,CLI.IdLocalAge  AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,Comentarios
 FROM tm_PreCartera  AS C INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero  INNER JOIN  Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS VN ON C.IdVend=VN.IdTercero
 INNER JOIN TercCliePrestamo AS CLI ON C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 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 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  TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN Conceptos AS CO ON C.IdConcepto=CO.IdConcepto  LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE tmEst=@pmtmEst AND C.VrSaldo>@pmSaldo
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,NomAgencia AS 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 TercCliePrestamo AS A ON  PC.tmIdCliente=A.IdClie AND PC.tmIdAgencia=A.IdAgencia
 WHERE tmNumero=@pmtmNumero
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, 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_PreMovi @pmtmEst CHAR(2)
 AS 
 SELECT tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,NomAgencia AS Agencia,tmIdCia,Compania,tmTipDoc,TipoDoc,tmNumero,tmItem,tmFecha,tmCargos,tmAbonos,tmConcepto,tmDetalle,tmReferencia
 ,tmNumCuota,tmNumForma,tmTipRef,tmDocRef,tmIdCiaRef,tmCdConcepto,Concepto,tmIdVend,VN.RazonSocial AS Vendedor,tmSanAge,tmSanAgeCia,tmSanClie,tmSanClieCia
,tmTimeSys,tmFecVence,tmTipoTasa,tmDTF_EA,tmTasaNom,tmPuntos,tmTasaEfe,tmCdBanco,Banco,tmBeneficiario,tmCdForma
,tmEnEfectivo,tmTotalAbono,tmVrAfavor,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario,Usuario
--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,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
--agencias
,DirAgencia,CLI.IdLocalAge  AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,Comentarios
 FROM tm_PreMovi  AS  M  INNER JOIN Terceros AS T ON M.tmIdCliente=T.IdTercero  INNER JOIN  Companias AS CI ON M.tmIdCia=CI.IdCia
 INNER JOIN Terceros AS VN ON M.tmIdVend=VN.IdTercero INNER JOIN  TercCliePrestamo AS CLI ON M.tmIdCliente=CLI.IdClie AND M.tmIdAgencia=CLI.IdAgencia
 INNER JOIN adm_Usuarios AS U ON M.tmIdUsuario=U.IdUsuario
 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 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 Conceptos AS C ON M.tmCdConcepto=C.IdConcepto  LEFT JOIN Sys_TiposDoc AS TD ON M.tmTipDoc=TD.IdDoc
 LEFT JOIN Bancos AS B ON M.tmCdBanco =B.IdBanco 
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE tmEst=@pmtmEst
 ORDER BY T.RazonSocial,tmIdAgencia,tmTimeSys
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpAcuPrestamos @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
,@pmSaldoAnt MONEY,@pmPrestamos MONEY,@pmDevPrestamos MONEY,@pmRecibos MONEY,@pmDevRecibos MONEY,@pmNotasDeb MONEY,@pmNotasCre MONEY
,@pmFacturas MONEY,@pmDevFacturas MONEY
 AS 
 UPDATE AcuPrestamos SET SaldoAnt=@pmSaldoAnt,Prestamos=@pmPrestamos,DevPrestamos=@pmDevPrestamos,Recibos=@pmRecibos,DevRecibos=@pmDevRecibos
,NotasDeb=@pmNotasDeb,NotasCre=@pmNotasCre,Facturas=@pmFacturas,DevFacturas=@pmDevFacturas
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCia=@pmIdCia AND IdCliente=@pmIdCliente AND IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFinRecibos @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFecPago SMALLDATETIME,@pmVrRecibido MONEY,@pmVrSubTotal MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrOtros MONEY,@pmVrOtrDcto MONEY,@pmVrPagosMas MONEY,@pmVrNeto MONEY
,@pmVrAplicado MONEY,@pmVrEfectivo MONEY,@pmVrAnticipo MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmEnEfectivo BIT,@pmCdForma VARCHAR(4)
,@pmNumForma VARCHAR(20),@pmDetallePago VARCHAR(250),@pmCdBanco VARCHAR(4),@pmCdCta VARCHAR(4),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoCredito VARCHAR(10),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDcm CHAR(2),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FinRecibos SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FecPago=@pmFecPago,VrRecibido=@pmVrRecibido,VrSubTotal=@pmVrSubTotal,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrOtros=@pmVrOtros,VrOtrDcto=@pmVrOtrDcto,VrPagosMas=@pmVrPagosMas,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,VrEfectivo=@pmVrEfectivo,VrAnticipo=@pmVrAnticipo
,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,EnEfectivo=@pmEnEfectivo,CdForma=@pmCdForma,NumForma=@pmNumForma,DetallePago=@pmDetallePago,CdBanco=@pmCdBanco,CdCta=@pmCdCta
,Referencia=@pmReferencia,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipoCredito=@pmTipoCredito,TipDcm=@pmTipDcm,Documento=@pmDocumento,IdCiaDcm=@pmIdCiaDcm,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO