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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPagosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinPagosFmt]
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].[paUpFinFactint]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFinFactint]
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 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)
,@pmEdoCausac INT,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinCausacion (TipoProc,NumProc,IdCia,Fecha,TotalCapital,TotalInteres,TotalIntMora,TipoCred,TipCom,Comprobante,Anulado,Observacion,EdoCausac,TimeSys,IdUsuario)
 VALUES (@pmTipoProc,@pmNumProc,@pmIdCia,@pmFecha,@pmTotalCapital,@pmTotalInteres,@pmTotalIntMora,@pmTipoCred,@pmTipCom,@pmComprobante,@pmAnulado
,@pmObservacion,@pmEdoCausac,@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 paInsFinCuotas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumCuota INT,@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrTotal MONEY,@pmVrAbonado MONEY
,@pmTipoTasa VARCHAR(10),@pmDTF_EA DECIMAL(16,8),@pmTasaNom DECIMAL(16,8),@pmSpread_TA DECIMAL(16,8),@pmTasaEfe DECIMAL(16,8),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmTipoCuota INT
,@pmNumCausacion INT,@pmIdCiaCau CHAR(2),@pmDetalle VARCHAR(250),@pmFecUltPago SMALLDATETIME,@pmConcepto VARCHAR(10),@pmFecLiqMora SMALLDATETIME,@pmFechaCrea SMALLDATETIME,@pmCodLinCred VARCHAR(4)
,@pmNumFactura VARCHAR(20),@pmVrInteresFact MONEY,@pmDiasLiquida INT,@pmFecUltCausac SMALLDATETIME,@pmVrAcumCapital MONEY,@pmConvenio INT,@pmNitConvenio VARCHAR(16),@pmNumPreMora INT
,@pmTipoCausac VARCHAR(3),@pmFacturaInt INT,@pmTipFacInt VARCHAR(3),@pmNumFacInt INT,@pmCdCiaFacInt CHAR(2),@pmFechaFacInt SMALLDATETIME
 AS 
 INSERT INTO Trn_FinCuotas (TipDoc,IdPrestamo,IdCia,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente,IdAgencia,Concepto,TipoCuota,TipoCausac,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora
,FechaCrea,CodLinCred,NumFactura,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio,NumPreMora,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumCuota,@pmFecha,@pmFechaVence,@pmVrTotal,@pmVrAbonado,@pmTipoTasa,@pmDTF_EA,@pmTasaNom,@pmSpread_TA,@pmTasaEfe,@pmIdCliente
,@pmIdAgencia,@pmConcepto,@pmTipoCuota,@pmTipoCausac,@pmNumCausacion,@pmIdCiaCau,@pmDetalle,@pmFecUltPago,@pmFecLiqMora,@pmFechaCrea,@pmCodLinCred,@pmNumFactura,@pmVrInteresFact,@pmDiasLiquida,@pmFecUltCausac,@pmVrAcumCapital,@pmConvenio,@pmNitConvenio,@pmNumPreMora
,@pmFacturaInt,@pmTipFacInt,@pmNumFacInt,@pmCdCiaFacInt,@pmFechaFacInt)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFinFactint @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY
,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4)
,@pmIdLocal VARCHAR(8),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoFactInt VARCHAR(10),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinFactint (TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCargos,VrOtrDcto,VrNeto,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,IdLocal,MulPlazos,IdPlazo,TipoFactInt,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFechaVence,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrCargos,@pmVrOtrDcto,@pmVrNeto,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos
,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmIdLocal,@pmMulPlazos,@pmIdPlazo,@pmTipoFactInt,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS 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
,@pmVrRecCaja MONEY
 AS 
 INSERT INTO Trn_FinPagos (TipDoc,IdPrestamo,IdCia,Item,Numero,Fecha,FecPago,VrCuota,EdoForma,CdBanco,CtaCheque,Detalle,TipRec,Recibo,IdCiaRec,VrLiqMora,FecLiqMora,VrRecCaja)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumero,@pmFecha,@pmFecPago,@pmVrCuota,@pmEdoForma,@pmCdBanco,@pmCtaCheque
,@pmDetalle,@pmTipRec,@pmRecibo,@pmIdCiaRec,@pmVrLiqMora,@pmFecLiqMora,@pmVrRecCaja)
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)
,@pmVrAboCapital MONEY,@pmLiqAboCapital INT,@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,VrAboCapital,LiqAboCapital,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,@pmVrAboCapital,@pmLiqAboCapital,@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 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,EdoCausac,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.TipoProc=C.TipoCausac AND 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 ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinCuotas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe
,IdCliente,IdAgencia,TipoCuota,Concepto,TipoCausac,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora,FechaCrea,CodLinCred,NumFactura
,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio,NumPreMora,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt
 FROM Trn_FinCuotas
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasFmt @pmTipDoc VARCHAR(3),@pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2) 
 AS 
 SELECT  IdPrestamo,C.IdCia AS CdCia,Compania,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente,RazonSocial,C.IdAgencia AS  CdAgencia,NomAgencia AS Agencia,TipoCuota,Concepto
,TipoCausac,NumCausacion,IdCiaCau,Detalle,FecUltPago,FecLiqMora,C.FechaCrea AS Fecha_crea,CodLinCred,NumFactura
,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,TipDoc,Convenio,NitConvenio,NumPreMora,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt
 FROM Trn_FinCuotas AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero  
 LEFT JOIN TercCliePrestamo AS CLI ON  C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 WHERE TipDoc=@pmTipDoc AND IdPrestamo BETWEEN @pmIdPrestamoIni AND  @pmIdPrestamoFin AND C.IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasLta @pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT C.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,NomAgencia AS Agencia,C.TipDoc AS CdTipDoc,TipoDoc,C.IdPrestamo AS NumDcmto,C.IdCia AS CdCia,Compania
,Item,VrTotal,VrAbonado,VrTotal-VrAbonado AS ValorSaldo,C.Fecha AS FechaEmision,FechaVence,DATEDIFF(day,FechaVence,@pmFecActual) AS DiasMora
,Concepto,NumCuota,Detalle,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,TipoCuota,C.TipoCausac AS TipoCau,NumCausacion,IdCiaCau,FecUltPago,FecLiqMora,C.FechaCrea AS Fecha_Crea,CodLinCred
,NumFactura,VrInteresFact,DiasLiquida,FecUltCausac,VrAcumCapital,Convenio,NitConvenio,NumPreMora,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt
--Datos del prestamo
,FecPrestamo,VrPrestamo,NPlazos,Causacion,P.NContrato AS NumContrato,CdCiaCon,NActaJunta,NumCredito,ISNULL(P.IdVend,CLI.IdVend) AS CdVend,ISNULL(VN.RazonSocial,VNC.RazonSocial) AS Vendedor,P.IdLinea AS CdLinea,LinCredito
--Datos del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,Comentarios
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinCuotas AS C  INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliePrestamo AS CLI ON  C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Terceros AS VNC ON CLI.IdVend=VNC.IdTercero
 INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc  
 INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 LEFT JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE  (VrTotal-VrAbonado)>@pmSaldo AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinFactint @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCargos
,VrOtrDcto,VrNeto,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend
,TarifaCom,CodTarCom,IdLocal,MulPlazos,IdPlazo,TipoFactInt,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FinFactint
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura 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 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
,TipoFactInt,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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinFactintFmt @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,Fecha,F.IdConcepto AS CdConcepto,C.Concepto AS ConceptoFact,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 CdLocal,CF.Localidad AS NomCiudad,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto
,TipoFactInt,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,F.TipDoc AS TipoDoc
--detalles
,Item,FD.IdConcepto AS CdConc,FD.Concepto AS DescConcepto,TipoReg,Cantidad,VrUnitario,VrTotal,TarifDcto,VrDcto,TarifIva,VrIva,TarifRet,VrRetfte,TarifIca,VrRetica
--Datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia
,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM Trn_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 CF ON F.IdLocal=CF.IdLocal 
 INNER JOIN  TercCliePrestamo AS CLI ON F.IdCliente=CLI.IdClie AND F.IdAgencia=CLI.IdAgencia
 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 Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 INNER JOIN Trn_FinFactDet AS FD ON F.TipDoc=FD.TipDoc  AND F.Factura=FD.Factura  AND F.IdCia=FD.IdCia
 LEFT JOIN TiposCom AS TC ON F.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 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
 WHERE F.TipDoc=@pmTipDoc  AND  F.Factura  BETWEEN  @pmFacturaIni AND @pmFacturaFin  AND F.IdCia=@pmIdCia
 ORDER BY  F.Factura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinFactintLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente 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 CdLocal,Localidad,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto
,TipoFactInt,OrigenAdd,TipCom,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
 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 F.IdLocal=L.IdLocal 
 INNER JOIN  TercCliePrestamo AS CLI ON F.IdCliente=CLI.IdClie AND F.IdAgencia=CLI.IdAgencia
 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
 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,'%')
 ORDER BY F.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 paQryFinPagosFmt @pmTipDoc VARCHAR(3),@pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  Item,Numero,Fecha,FecPago,VrCuota,EdoForma,CdBanco,Banco,CtaCheque,Detalle,TipRec,Recibo,IdCiaRec,VrLiqMora,FecLiqMora,VrRecCaja
 FROM Trn_FinPagos AS P LEFT JOIN Bancos AS B ON P.CdBanco=B.IdBanco
 WHERE TipDoc=@pmTipDoc AND IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE 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,VrAboCapital,LiqAboCapital,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,VrAboCapital,LiqAboCapital
,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,VrAboCapital,LiqAboCapital,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,VrAboCapital,LiqAboCapital,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,VrAboCapital,LiqAboCapital,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 ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFinFactint @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4)
,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocal VARCHAR(8),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT
,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoFactInt VARCHAR(10),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FinFactint SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrNeto=@pmVrNeto,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv
,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,IdLocal=@pmIdLocal,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,TipoFactInt=@pmTipoFactInt ,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE 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),@pmVrAboCapital MONEY,@pmLiqAboCapital INT,@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,VrAboCapital=@pmVrAboCapital,LiqAboCapital=@pmLiqAboCapital,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO