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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelFinFactint @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 DELETE 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 ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelFinDevFac @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_FinDevFac
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion 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 paInsFinConcFactura @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmTipoConc VARCHAR(10),@pmGravado BIT,@pmCodTarIva VARCHAR(4)
,@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmCdCueIva VARCHAR(16),@pmCdCueRet VARCHAR(16),@pmCdCueIca VARCHAR(16),@pmInactivo BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO FinConcFactura (IdConcepto,Concepto,TipoConc,Gravado,CodTarIva,CodTarRet,CodTarIca,IdCuenta,CdCueIva,CdCueRet,CdCueIca,Inactivo,FechaAdd,IdUsuario)
 VALUES (@pmIdConcepto,@pmConcepto,@pmTipoConc,@pmGravado,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmIdCuenta
,@pmCdCueIva,@pmCdCueRet,@pmCdCueIca,@pmInactivo,@pmFechaAdd,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFinDevFac @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY
,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmIdVend VARCHAR(16),@pmIdLocal VARCHAR(8),@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinDevFac (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCargos,VrOtrDcto,VrNeto,IdVend,IdLocal,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmFactura,@pmIdCiaDoc,@pmFecDoc,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrCargos,@pmVrOtrDcto,@pmVrNeto,@pmIdVend,@pmIdLocal
,@pmModdDev,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@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 paInsFinFactDet @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmTipoReg VARCHAR(10),@pmCantidad DECIMAL(14,4)
,@pmVrUnitario MONEY,@pmVrTotal MONEY,@pmTarifIva DECIMAL(14,4),@pmVrIva MONEY,@pmTarifRet DECIMAL(14,4),@pmVrRetfte MONEY,@pmTarifIca DECIMAL(14,4),@pmVrRetica MONEY,@pmnPrestamo INT,@pmCdCiaPrest CHAR(2)
,@pmFecIniLiq SMALLDATETIME,@pmFecFinLiq SMALLDATETIME,@pmCantDias INT,@pmCdTarifIva VARCHAR(4),@pmCdTarifRet VARCHAR(4),@pmCdTarifIca VARCHAR(4),@pmTarifDcto DECIMAL(14,4),@pmVrDcto MONEY,@pmCdTarifDct VARCHAR(4)
 AS 
 INSERT INTO Trn_FinFactDet (TipDoc,Factura,IdCia,Item,IdConcepto,Concepto,TipoReg,Cantidad,VrUnitario,VrTotal,TarifIva,VrIva,TarifRet,VrRetfte,TarifIca,VrRetica,TarifDcto,VrDcto,nPrestamo,CdCiaPrest,FecIniLiq,FecFinLiq,CantDias,CdTarifIva,CdTarifRet,CdTarifIca,CdTarifDct)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmIdConcepto,@pmConcepto,@pmTipoReg,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmTarifIva,@pmVrIva,@pmTarifRet,@pmVrRetfte,@pmTarifIca,@pmVrRetica,@pmTarifDcto,@pmVrDcto,@pmnPrestamo
,@pmCdCiaPrest,@pmFecIniLiq,@pmFecFinLiq,@pmCantDias,@pmCdTarifIva,@pmCdTarifRet,@pmCdTarifIca,@pmCdTarifDct)
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),@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,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,@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 paInsFinFacturas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumFactura VARCHAR(20),@pmFecha SMALLDATETIME
,@pmFechaVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrInteres MONEY,@pmDiasLiq DECIMAL(14,4),@pmTasaEfeMes DECIMAL(16,8),@pmTasaEfeDia DECIMAL(16,8),@pmFechaPago SMALLDATETIME,@pmItemPago INT
,@pmFacturado BIT,@pmTipFac VARCHAR(3),@pmFacturaInt INT,@pmFecFactura SMALLDATETIME,@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmVrAbono MONEY,@pmCdCiaFac CHAR(2)
 AS 
 INSERT INTO Trn_FinFacturas (TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago,ItemPago,FecInicio,FecFinal,VrAbono,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumFactura,@pmFecha,@pmFechaVence,@pmVrFactura,@pmVrInteres,@pmDiasLiq
,@pmTasaEfeMes,@pmTasaEfeDia,@pmFechaPago,@pmItemPago,@pmFecInicio,@pmFecFinal,@pmVrAbono,@pmFacturado,@pmTipFac,@pmFacturaInt,@pmCdCiaFac,@pmFecFactura)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreFactura @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdConc VARCHAR(4),@pmtmConcepto VARCHAR(250),@pmtmTipoConc VARCHAR(10),@pmtmCantidad DECIMAL(14,4),@pmtmVrUnitario MONEY
,@pmtmVrTotal MONEY,@pmtmTarifIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifRet DECIMAL(14,4),@pmtmVrRetfte MONEY,@pmtmTarifIca DECIMAL(14,4),@pmtmVrRetica MONEY,@pmtmnPrestamo INT,@pmtmCdCiaPrest CHAR(2)
,@pmtmFecIniLiq SMALLDATETIME,@pmtmFecFinLiq SMALLDATETIME,@pmtmCantDias INT,@pmtmCdTarifIva VARCHAR(4),@pmtmCdTarifRet VARCHAR(4),@pmtmCdTarifIca VARCHAR(4),@pmtmTarifDct DECIMAL(14,4),@pmtmVrDcto MONEY
,@pmtmCdTarifDct VARCHAR(4)
 AS 
 INSERT INTO tm_PreFactura (tmNumero,tmItem,tmIdConc,tmConcepto,tmTipoConc,tmCantidad,tmVrUnitario,tmVrTotal,tmTarifIva,tmVrIva,tmTarifRet,tmVrRetfte,tmTarifIca,tmVrRetica,tmTarifDct,tmVrDcto,tmnPrestamo,tmCdCiaPrest,tmFecIniLiq,tmFecFinLiq
,tmCantDias,tmCdTarifIva,tmCdTarifRet,tmCdTarifIca,tmCdTarifDct )
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdConc,@pmtmConcepto,@pmtmTipoConc,@pmtmCantidad,@pmtmVrUnitario,@pmtmVrTotal,@pmtmTarifIva,@pmtmVrIva,@pmtmTarifRet,@pmtmVrRetfte,@pmtmTarifIca,@pmtmVrRetica
,@pmtmTarifDct,@pmtmVrDcto,@pmtmnPrestamo,@pmtmCdCiaPrest,@pmtmFecIniLiq,@pmtmFecFinLiq,@pmtmCantDias,@pmtmCdTarifIva,@pmtmCdTarifRet,@pmtmCdTarifIca,@pmtmCdTarifDct)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_PreFactura_Sel @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_PreFactura (tmNumero,tmItem,tmIdConc,tmConcepto,tmTipoConc,tmCantidad,tmVrUnitario,tmVrTotal,tmTarifIva,tmVrIva,tmTarifRet,tmVrRetfte,tmTarifIca,tmVrRetica,tmTarifDct,tmVrDcto,tmnPrestamo,tmCdCiaPrest,tmFecIniLiq,tmFecFinLiq
,tmCantDias,tmCdTarifIva,tmCdTarifRet,tmCdTarifIca,tmCdTarifDct)
 SELECT @pmtmNumero,Item,IdConcepto,Concepto,TipoReg,Cantidad,VrUnitario,VrTotal,TarifIva,VrIva,TarifRet,VrRetfte,TarifIca,VrRetica,TarifDcto,VrDcto,nPrestamo,CdCiaPrest,FecIniLiq,FecFinLiq,CantDias,CdTarifIva,CdTarifRet,CdTarifIca,CdTarifDct
 FROM Trn_FinFactDet
 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 paQryFinConcFacturaLta @pmTipoConc VARCHAR(10)=Null
 AS 
 SELECT IdConcepto,Concepto,TipoConc,Gravado,CodTarIva,TIV.Tarifa AS TarifIva,CodTarRet,TR.Tarifa AS TarifRet,CodTarIca,TIC.Tarifa AS TarifIca
,C.IdCuenta AS CdCuenta,NomCuenta,CdCueIva,CdCueRet,CdCueIca,C.Inactivo AS Inactvo,C.FechaAdd AS FechaCrea,C.FechaUpdate AS FechaAct,C.IdUsuario AS CdUsuario,Usuario
 FROM FinConcFactura AS C INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 LEFT JOIN Puc AS P ON C.IdCuenta=P.IdCuenta
 LEFT JOIN Tablapor AS TIV ON C.CodTarIva=TIV.IdTarifa  LEFT JOIN Tablapor AS TR ON C.CodTarRet=TR.IdTarifa
 LEFT JOIN Tablapor AS TIC ON C.CodTarIca=TIC.IdTarifa
 WHERE TipoConc LIKE ISNULL(@pmTipoConc,'%')
 ORDER BY Concepto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinDevFac @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto
,VrRetencion,VrReteICA,VrReteIVA,VrCargos,VrOtrDcto,VrNeto,IdVend,IdLocal,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FinDevFac
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion 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 paQryFinDevFacDoc @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,ModdDev
 FROM Trn_FinDevFac
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura  AND IdCiaDoc=@pmIdCiaDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinDevFacLta @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS CdAgencia
,NomAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCargos,VrOtrDcto,VrNeto,D.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,D.IdLocal AS CdLocal
,Localidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,TipDev
 FROM  Trn_FinDevFac  AS D  INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON D.IdVend=VN.IdTercero
 INNER JOIN Localidades AS L ON D.IdLocal=L.IdLocal 
 INNER JOIN  TercCliePrestamo AS CLI ON D.IdCliente=CLI.IdClie AND D.IdAgencia=CLI.IdAgencia
 WHERE  D.TipDev=@pmTipDev  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 ORDER BY D.IdCia,Factura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinDevRecFmt @pmTipDev VARCHAR(3),@pmDevolucionIni INT, @pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipRec,Recibo,IdCiaDoc,FecDoc,FecPago
,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS CdAgencia,NomAgencia AS NomAgencia,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 DevRefrencia,D.NumAutoriza AS NumAutza,ModdDev,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS CdUsuario,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,P.NumAutoriza AS PagAutoriza,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,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,Comentarios
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM  Trn_FinDevRec AS D  INNER JOIN Trn_Pagos AS P ON D.TipDev=P.TipDoc AND D.Devolucion=P.Documento AND D.IdCia=P.IdCia
 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 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 DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN  TercCliePrestamo AS CLI ON D.IdCliente=CLI.IdClie AND D.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona  INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 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 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 TipDev=@pmTipDev  AND Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
 ORDER BY Devolucion
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,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 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
,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
,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 ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinFacturas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago
,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura
 FROM Trn_FinFacturas
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER 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,NomAgencia AS 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,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_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 TercCliePrestamo AS CLI ON N.IdCliente=CLI.IdClie AND N.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN TiposCom AS TC ON N.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 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 paQryFinNotasFmt @pmTipDoc VARCHAR(3),@pmNumNotaIni INT, @pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  NumNota,N.IdCia AS CdCia,Compania,N.Fecha AS FechaNota,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,N.IdAgencia AS CdAgencia,NomAgencia AS NomAgencia
,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,CC.CCosto AS NotCenCosto,N.IdSubCos AS CdSubCost,SC.SubCosto AS NotSubCentro,N.IdVend AS CdVend,V.RazonSocial AS Vendedor
,TarifaCom,CodTarCom,N.Referencia AS NotReferencia,pVehiculo,CdConductor,TipDcm,N.Documento AS NumDcmto,IdCiaDcm,FecDcm,CdForma,N.NumCheque AS NroCheque,CdBanco,BN.Banco AS NomBanco,MulPlazos
,N.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,Modalidad,N.TipCom AS TipComp,TipoCom,N.Comprobante AS NumComp,IdCiaCom,N.Observacion AS Observ,Anulado,N.IdEstado AS CdEstado,Estado
,OrigenAdd,N.TimeSys AS FechaCrea,N.FecUpdate AS FechaAct,N.IdCiaCrea  AS CdCiaCrea,N.IdUsuario AS CdUsuario,Usuario
--detalle del comprobante
,Item,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,D.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,IdVehiculo,D.IdCCosto AS DetCodCentCost,DC.CCosto AS DetCentCost
,D.IdSubCos AS DetCodSubCentro,DS.SubCosto  AS DetSubCentro,VrBase,TarifaBase,D.TipDoc AS DetTipoDoc,D.Documento AS DetNumDoc,IdCiaDoc,TipFac,Factura,IdCiaFac,ItemFac
,FecVence,D.NumCheque AS DetNumCheque,TipoAplica,NitOtros,Referncia
--Datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,CLI.Comentarios AS ClieComentarios
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinNotas AS N INNER JOIN Terceros AS T ON N.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  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  TercCliePrestamo AS CLI ON N.IdCliente=CLI.IdClie AND N.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona  INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 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  CLI.IdLocalAge=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  Trn_ComDetalle AS D ON N.TipCom=D.TipCom AND N.Comprobante=D.Comprobante AND N.IdCiaCom=D.IdCia
 LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta LEFT JOIN Terceros AS DT ON D.IdTercero=DT.IdTercero
 LEFT JOIN CentroCosto AS DC ON D.IdCCosto=DC.IdCCosto  LEFT JOIN SubCentros AS DS ON D.IdSubCos=DS.IdSubCos
 WHERE N.TipDoc=@pmTipDoc  AND NumNota BETWEEN  @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
 ORDER BY NumNota
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,pVehiculo,CdConductor,CD.RazonSocial AS Conductor,TipDcm,Documento,IdCiaDcm
,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario,TipDoc
--Datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,Comentarios
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinRecibos AS R  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN  TercCliePrestamo AS CLI ON R.IdCliente=CLI.IdClie AND R.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona   INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  
 INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom  
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Bancos AS B ON R.CdBanco=B.IdBanco  LEFT JOIN Terceros AS CD ON R.CdConductor=CD.IdTercero
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND R.IdVend LIKE ISNULL(@pmIdVend,'%')  AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 ORDER BY R.IdCia,Recibo

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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
,TipDcm,R.Documento AS NumDcmto,IdCiaDcm,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
--datos de forma de pago
,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,EsCaja,P.IdBanco AS PagCdBanco,BF.Banco AS PagBanco
,P.NumForma AS PagNumForma,FecForma,CtaForma,Beneficiario,Referncia1,P.Referncia2 AS PagReferencia2
,CdLocal,LF.Localidad AS CiudadPlaza,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef
--Datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,Comentarios
--agencias
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinRecibos AS R  INNER JOIN Trn_Pagos AS P ON R.TipDoc=P.TipDoc AND R.Recibo=P.Documento AND R.IdCia=P.IdCia
 INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN  Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario   INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS BF ON P.IdBanco=BF.IdBanco
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN  TercCliePrestamo AS CLI ON R.IdCliente=CLI.IdClie AND R.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom  LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal 
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE R.TipDoc=@pmTipDoc  AND Recibo BETWEEN @pmReciboIni AND @pmReciboFin AND R.IdCia=@pmIdCia
 ORDER BY  R.Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryRecChequesPre @pmTipDoc VARCHAR(3),@pmReciboIni INT,@pmReciboFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT C.TipDoc AS Tip_Doc,TipoDoc,C.Recibo AS NumRecibo,C.IdCia AS CdCia,Compania,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS Cd_Agencia
,NomAgencia,VrTotal,Cantidad,OrigenAdd,PlacaVehic,C.Anulado AS EstaAnulado,NumDev,C.FecDev AS FechDev
,C.Observacion AS Observ,C.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,TimeSys,FecUpdate,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,Leyenda
--INFORMACION DEL CHEQUE
,TipForma,Numero,H.IdBanco AS CodBanco,B.Banco AS NomBanco,H.Fecha AS FechaCheq,FecPago,VrForma,CtaForma,Beneficiario,Referncia1,Referncia2,Detalle
,NitCliente,HC.RazonSocial AS CheNombCliente,H.CdAgencia AS CheIdAgencia,CdVend,V.RazonSocial AS NomVendedor,H.IdLocal AS CodCiudad,LP.Localidad AS CiudadPlaza
,H.TipDoc AS TipDocAplica,Documento,IdCiaDoc,FecDoc,TipCon,NumConsig,IdCiaCon,FecConsig,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco
,BCT.Banco AS CtaBanco,H.Anulado AS CheAnulado,H.FecDev AS CheFecAnulado,H.IdEstado AS CheCodEstado,HE.Estado AS CheEstado
--Información 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,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CliCodBanco,BC.Banco AS CliBanco,EsExento,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero
, DirAgencia,IdLocalAge,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_RecCheques AS C  INNER JOIN Trn_Cheques AS H ON C.TipDoc=H.TipRec  AND C.Recibo=H.Recibo AND C.IdCia=H.IdCia
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero 
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 INNER JOIN Bancos AS B ON H.IdBanco=B.IdBanco INNER JOIN Localidades AS LP ON H.IdLocal=LP.IdLocal
 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 Bancos AS BC ON CLI.IdBanco=BC.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
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS HC ON H.NitCliente=HC.IdTercero LEFT JOIN Terceros AS V ON H.CdVend=V.IdTercero
 LEFT JOIN CtasCorrientes AS CTA ON H.CdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON  CTA.IdBanco=BCT.IdBanco
 LEFT JOIN EstadoDoc AS HE ON H.IdEstado=HE.IdEstado LEFT JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 WHERE C.TipDoc=@pmTipDoc AND C.Recibo BETWEEN @pmReciboIni AND @pmReciboFin AND C.IdCia=@pmIdCia
 ORDER BY C.Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFinConcFactura @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmTipoConc VARCHAR(10),@pmGravado BIT,@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4)
,@pmIdCuenta VARCHAR(16),@pmCdCueIva VARCHAR(16),@pmCdCueRet VARCHAR(16),@pmCdCueIca VARCHAR(16),@pmInactivo BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE FinConcFactura SET Concepto=@pmConcepto,TipoConc=@pmTipoConc,Gravado=@pmGravado,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,IdCuenta=@pmIdCuenta
,CdCueIva=@pmCdCueIva,CdCueRet=@pmCdCueRet,CdCueIca=@pmCdCueIca,Inactivo=@pmInactivo,FechaUpdate=@pmFechaUpdate
 WHERE IdConcepto=@pmIdConcepto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFinDevFac @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmIdVend VARCHAR(16),@pmIdLocal VARCHAR(8),@pmModdDev VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FinDevFac SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Factura=@pmFactura,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto
,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrNeto=@pmVrNeto,IdVend=@pmIdVend,IdLocal=@pmIdLocal,ModdDev=@pmModdDev,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion 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 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),@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,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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paUpFinFactintDev @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
,@pmAnulado BIT,@pmNumDev INT,@pmFecDev  SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
 AS 
 UPDATE Trn_FinFactint SET  Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev
,Observacion=@pmObservacion,IdEstado=@pmIdEstado
WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

