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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paDelAcuPrestamos @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM AcuPrestamos
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND 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 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
 AS 
 INSERT INTO AcuPrestamos (nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre)
 VALUES (@pmnAnno,@pmnMes,@pmIdCia,@pmIdCliente,@pmIdAgencia,@pmSaldoAnt,@pmPrestamos,@pmDevPrestamos,@pmRecibos,@pmDevRecibos,@pmNotasDeb,@pmNotasCre)
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)
 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
 FROM Trn_FinCuotas
 WHERE TipDoc IN ('PR1','ND2')  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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Caja_Od2 @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Caja (tmEst,tmItem,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrOtros,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrNeto,VrEfectivo
,VrPagosOtr,VrCreditos,VrGastComb,VrGastOtros,Cantidad,CantPuntos,PuntosAcum,EnEfectivo,CdForma,NumForma,CdBanco,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2
,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,2,TipDev,Devolucion,D.IdCia,D.Fecha,D.IdConcepto,D.IdCliente,D.IdAgencia,D.VrSubTotal,0,D.VrReteIVA,D.VrRetencion+D.VrReteICA,D.VrPagosMas,D.VrOtros,D.VrOtrDcto,0,0,D.VrNeto,D.VrEfectivo
,0,0,0,0,0,0,0,D.EnEfectivo,CdForma,NumForma,CdBanco,SUBSTRING(DetallePago,1,150),CdCta,D.FecPago,D.IdVend,D.TarifaCom,'0',D.IdUsuario,'0',D.Referencia,''
,'RC2',R.pVehiculo,R.TipDcm,R.Documento,R.IdCiaDcm,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Recibo,FecDoc,D.Observacion,D.TimeSys
 FROM Trn_FinDevRec AS D INNER JOIN Trn_FinRecibos AS R ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
 WHERE  D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdUsuario LIKE ISNULL(@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_Caja_Rc2 @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Caja (tmEst,tmItem,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrOtros,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrNeto,VrEfectivo
,VrPagosOtr,VrCreditos,VrGastComb,VrGastOtros,Cantidad,CantPuntos,PuntosAcum,EnEfectivo,CdForma,NumForma,CdBanco,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2
,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,2,TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,VrSubTotal,0,VrReteIVA,VrRetencion+VrReteICA,VrPagosMas,VrOtros,VrOtrDcto,0,0,VrNeto,VrEfectivo
,0,0,0,0,0,0,0,EnEfectivo,CdForma,NumForma,CdBanco,SUBSTRING(DetallePago,1,150),CdCta,FecPago,IdVend,TarifaCom,'0',IdUsuario,'0',Referencia,''
,'RC2',pVehiculo,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys
 FROM Trn_FinRecibos
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdUsuario LIKE ISNULL(@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_Diario_NotPre @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Diario (tmEst,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrSobretasa,VrRetencion,VrReteIca,VrReteIva,VrOtros
,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,VrCreditos,VrValesGastos,CantPuntos,CdProducto,Cantidad,EnEfectivo,CdForma,NumForma,CdBanco
,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2,Beneficiario,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,VrSubTotal,0,VrImpuesto,0,VrRetencion,VrReteICA,VrReteIVA,VrSancion
,0,0,0,0,0,VrNeto,VrOtros AS VlrEfectivo,0,0,0,0,'0',0,0,CdForma,NumCheque,CdBanco
,'','0',FecDcm,IdVend,TarifaCom,'0',IdUsuario,'0',Referencia,'','',Modalidad,pVehiculo,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,0,0,Null,Observacion,TimeSys
 FROM Trn_FinNotas
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdUsuario LIKE ISNULL(@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_Diario_Od2 @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Diario (tmEst,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrSobretasa,VrRetencion,VrReteIca,VrReteIva,VrOtros
,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,VrCreditos,VrValesGastos,CantPuntos,CdProducto,Cantidad,EnEfectivo,CdForma,NumForma,CdBanco
,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2,Beneficiario,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,TipDev,Devolucion,D.IdCia,D.Fecha,D.IdConcepto,D.IdCliente,D.IdAgencia,D.VrSubTotal,0,0,0,D.VrRetencion,D.VrReteICA,D.VrReteIVA,D.VrPagosMas
,D.VrOtros,D.VrOtrDcto,0,0,0,D.VrNeto,D.VrEfectivo,0,0,0,0,'0',0,D.EnEfectivo,CdForma,NumForma,CdBanco
,SUBSTRING(DetallePago,1,150),CdCta,D.FecPago,D.IdVend,D.TarifaCom,'0',D.IdUsuario,'0',D.Referencia,'','','RC2',R.pVehiculo,R.TipDcm,R.Documento,R.IdCiaDcm,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Recibo,FecDoc,D.Observacion,D.TimeSys
 FROM Trn_FinDevRec AS D INNER JOIN Trn_FinRecibos AS R ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
 WHERE  D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdUsuario LIKE ISNULL(@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_Diario_Od2Det @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Diario (tmEst,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrSobretasa,VrRetencion,VrReteIca,VrReteIva,VrOtros
,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,VrCreditos,VrValesGastos,CantPuntos,CdProducto,Cantidad,EnEfectivo,CdForma,NumForma,CdBanco
,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2,Beneficiario,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,TipDev,Devolucion,R.IdCia,R.Fecha,IdConcepto,IdCliente,IdAgencia,VrPagado,VrCambio,0,0,VrRetencion,VrReteICA,VrReteIVA,VrPagosMas
,VrOtros,VrOtrDcto,0,0,0,VrPagado-VrCambio,VrEfectivo,0,0,0,0,'0',0,EnEfectivo,IdForma,P.NumForma,IdBanco
,Detalle,CASE P.CdCta WHEN NULL THEN '0'  WHEN '' THEN '0' ELSE P.CdCta END,FecForma,IdVend,TarifaCom,'0',R.IdUsuario,CdLocal,Referncia1,Referncia2,Beneficiario,'RC2','0',TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,0,Recibo,FecDoc,Observacion,TimeSys
 FROM Trn_FinDevRec AS R INNER JOIN Trn_Pagos AS P ON R.TipDev=P.TipDoc AND R.Devolucion=P.Documento AND R.IdCia=P.IdCia
 WHERE  R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdUsuario LIKE ISNULL(@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_Diario_Rc2 @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Diario (tmEst,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrSobretasa,VrRetencion,VrReteIca,VrReteIva,VrOtros
,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,VrCreditos,VrValesGastos,CantPuntos,CdProducto,Cantidad,EnEfectivo,CdForma,NumForma,CdBanco
,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2,Beneficiario,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,VrSubTotal,0,0,0,VrRetencion,VrReteICA,VrReteIVA,VrPagosMas
,VrOtros,VrOtrDcto,0,0,0,VrNeto,VrEfectivo,0,0,0,0,'0',0,EnEfectivo,CdForma,NumForma,CdBanco
,SUBSTRING(DetallePago,1,150),CdCta,FecPago,IdVend,TarifaCom,'0',IdUsuario,'0',Referencia,'','','RC2',pVehiculo,TipDcm,Documento,IdCiaDcm,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys
 FROM Trn_FinRecibos
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdUsuario LIKE ISNULL(@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_Diario_Rc2Det @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Diario (tmEst,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrSobretasa,VrRetencion,VrReteIca,VrReteIva,VrOtros
,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrCostos,VrNeto,VrEfectivo,VrPagosOtr,VrCreditos,VrValesGastos,CantPuntos,CdProducto,Cantidad,EnEfectivo,CdForma,NumForma,CdBanco
,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2,Beneficiario,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,R.TipDoc,Recibo,R.IdCia,R.Fecha,IdConcepto,IdCliente,IdAgencia,VrPagado,VrCambio,0,0,VrRetencion,VrReteICA,VrReteIVA,VrPagosMas
,VrOtros,VrOtrDcto,0,0,0,VrPagado-VrCambio,VrEfectivo,0,0,0,0,'0',0,EnEfectivo,IdForma,P.NumForma,IdBanco
,Detalle,CASE P.CdCta WHEN NULL THEN '0'  WHEN '' THEN '0' ELSE P.CdCta END,FecForma,IdVend,TarifaCom,'0',R.IdUsuario,CdLocal,Referncia1,Referncia2,Beneficiario,'RC2',pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys
 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
 WHERE  R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdUsuario LIKE ISNULL(@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 @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(14,4),@pmTasaNom DECIMAL(14,4),@pmSpread_TA DECIMAL(14,4),@pmTasaEfe DECIMAL(14,4),@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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreCartera_Ndb @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,CdForma,'',Referencia,NumCheque,CdBanco
 FROM Trn_FinCuotas  AS C INNER JOIN Trn_FinNotas AS N ON C.TipDoc=N.TipDoc  AND C.IdPrestamo=N.NumNota AND C.IdCia=N.IdCia
 WHERE C.TipDoc='ND2' AND VrTotal>VrAbonado AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')   AND N.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_PreCartera_Pre @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,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,'0','','','','0'
 FROM Trn_FinCuotas  AS C  INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 WHERE C.TipDoc='PR1' AND VrTotal>VrAbonado AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')   AND P.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_PreCartera_Saf @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,'SAF',MAX(Recibo),IdCia,0,0,getdate(),getdate(),SUM(VrAFavor-VrOtrosDb),SUM(VrAbono),SUM((VrAFavor-VrOtrosDb)-VrAbono),'0',0,0,0,0
,IdCliente,IdAgencia,IdVend,'SAF',0,0,'00','SALDO A FAVOR','0',NULL,NULL,getdate(),'0','','','','0'
 FROM Trn_FinAfavor
 WHERE  (VrAFavor-VrOtrosDb)>VrAbono AND  IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')   AND IdVend LIKE ISNULL(@pmIdVend,'%')
 GROUP BY IdCia,IdCliente,IdAgencia,IdVend
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(14,4),@pmtmTasaNom DECIMAL(14,4),@pmtmPuntos DECIMAL(14,4),@pmtmTasaEfe DECIMAL(14,4),@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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paInstm_PreMovi_Abo  @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,'INI',Recibo,A.IdCia,A.Item,A.Fecha,A.IdCliente,A.IdAgencia,IdVend,0,TotalAbono,NumCuota,TimeSys,FecPago,'0',Concepto,A.Detalle,'ABONO SALDO INICIAL'
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,A.TipDoc,A.IdPrestamo,A.IdCiaPre,'0','0','','0',0,VrAbonado,0,'0',0,'00',0,0,Null,'ADMIN',0,0,0,0
 FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND  A.IdPrestamo=C.IdPrestamo AND  A.IdCiaPre=C.IdCia AND  A.ItemPre=C.Item
 WHERE A.TipRec='PR1' AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND A.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND A.IdCia LIKE ISNULL(@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_PreMovi_Ndb  @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,TipDoc,NumNota,IdCia,0,Fecha,IdCliente,IdAgencia,IdVend,CASE TipDoc WHEN 'ND2' THEN VrNeto ELSE 0 END,CASE TipDoc WHEN 'NC2' THEN VrNeto ELSE 0 END,0,TimeSys,FechaVence,IdConcepto
,Modalidad,Observacion,Referencia,'0',0,0,0,0,TipDcm,Documento,IdCiaDcm,NumCheque,CdBanco,'',CdForma,0,0,VrAnticipo,TipCom,Comprobante,IdCiaCom,Anulado,0,Null,IdUsuario,0,0,0,0
 FROM Trn_FinNotas
 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_Odb  @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,VrNeto,0,0,TimeSys,FecDoc,IdConcepto,ModdDev,Observacion,Referencia,'0',0,0,0,0,TipDoc,Recibo,IdCiaDoc,'0','0','','0',EnEfectivo,0,VrAnticipo
,TipCom,Comprobante,IdCiaCom,0,0,Null,IdUsuario,0,0,0,0
 FROM Trn_FinDevRec
 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_Pre  @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,FechaVence,IdConcepto,Concepto,Detalle,Causacion
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,'CAU',NumCausacion,IdCiaCau,NumCredito,'0',TipoGarantia,'0',0,VrAbonado,0,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,IdUsuario,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo  AND C.IdCia=P.IdCia
 WHERE C.TipDoc='PR1' 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paInstm_PreMovi_Rec  @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,TipDoc,Recibo,IdCia,0,Fecha,IdCliente,IdAgencia,IdVend,0,VrNeto,0,TimeSys,FecPago,IdConcepto,pVehiculo,Observacion,Referencia,'0',0,0,0,0,TipDcm,Documento,IdCiaDcm
,NumForma,CdBanco,'',CdForma,EnEfectivo,0,VrAnticipo,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,IdUsuario,0,0,0,0
 FROM Trn_FinRecibos
  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 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
 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 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,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,FechaCrea
--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
--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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON C.IdCliente=CLI.IdClie 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  
 LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=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
 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 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,Agencia,D.VrPrestamo AS ValorDev,D.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,NumCuotaIni,AcumCapital,AcumCuotas
,NPlazos,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
,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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON D.IdCliente=CLI.IdClie 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
 LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom  LEFT JOIN Agencias AS A ON D.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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,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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON D.IdCliente=CLI.IdClie 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
 LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom    LEFT JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia  
 LEFT JOIN Localidades AS LA ON A.IdLocal=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 paQryFinNotasCr @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT  NumNota,N.IdCia AS CdCia,Compania,Fecha,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,N.IdAgencia AS CdAgencia,Agencia
,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrSancion,VrOtros,VrNeto,VrAplicado,VrAnticipo,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv
,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,N.IdCCosto AS CdcCosto,CCosto,N.IdSubCos AS CdSubCost,SubCosto,N.IdVend AS CdVend,V.RazonSocial AS Vendedor
,TarifaCom,CodTarCom,N.Referencia AS Refrencia,pVehiculo,CdConductor,CD.RazonSocial AS Conductor,TipDcm,Documento,IdCiaDcm,FecDcm,CdForma,NumCheque
,CdBanco,Banco,MulPlazos,N.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,Modalidad,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,Anulado
,N.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,N.FecUpdate AS FechaAct,IdCiaCrea,N.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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_FinNotas AS N INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON N.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON N.IdCia=CI.IdCia INNER JOIN Terceros AS V ON N.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario 
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado  INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 INNER JOIN  Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo
 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 TercCliente AS CLI ON N.IdCliente=CLI.IdClie 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
 LEFT JOIN Agencias  AS A ON N.IdAgencia=A.IdAgencia LEFT JOIN TiposCom AS TC ON N.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Bancos AS BN ON N.CdBanco=BN.IdBanco LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS CD ON N.CdConductor=CD.IdTercero
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
  AND N.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND N.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY N.IdCia,NumNota

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,Agencia
,VrPrestamo,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
,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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON P.IdCliente=CLI.IdClie 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
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS CD ON P.CdConductor=CD.IdTercero  
 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 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,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,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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON R.IdCliente=CLI.IdClie 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
 LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom  LEFT JOIN Agencias  AS A ON R.IdAgencia=A.IdAgencia 
 LEFT JOIN Localidades AS LA ON A.IdLocal=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 paQrytm_PreCartera @pmtmEst CHAR(2),@pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME
 AS 
 SELECT T.RazonSocial AS NomCliente,C.IdCliente AS NitCliente,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,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
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON C.IdCliente=CLI.IdClie
 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
 LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia
 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 A.IdLocal=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_PreMovi @pmtmEst CHAR(2)
 AS 
 SELECT tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,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
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 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 TercCliente AS CLI ON  M.tmIdCliente=CLI.IdClie
 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
 LEFT JOIN Agencias AS A ON M.tmIdAgencia=A.IdAgencia
 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 A.IdLocal=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
 AS 
 UPDATE AcuPrestamos SET SaldoAnt=@pmSaldoAnt,Prestamos=@pmPrestamos,DevPrestamos=@pmDevPrestamos,Recibos=@pmRecibos,DevRecibos=@pmDevRecibos
,NotasDeb=@pmNotasDeb,NotasCre=@pmNotasCre
 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 paUpAcuPrestamosSan @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
,@pmSaldoAnt MONEY
 AS 
 UPDATE AcuPrestamos SET SaldoAnt=@pmSaldoAnt
 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