if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsProdProcesos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsProdProcesos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Comisiones_Rec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Comisiones_Rec]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdProcesosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdProcesosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexEnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpProdProcesos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpProdProcesos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsSobDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsSobDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Sobtasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Sobtasas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryClaseTarImp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryClaseTarImp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporteFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporteFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySobDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobGasOxigenLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySobGasOxigenLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobProductosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySobProductosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobretasas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySobretasas_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobretasas_Crr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySobretasas_Crr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobretasasDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySobretasasDet]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryProdProcesosLta] 
AS
SELECT PS.IdProducto AS CodProducto,P.DescripProd,PS.Item,PS.IdTipProc,TipoProceso,PC.Descripcion AS DescProceso,PC.IndOrden
,PS.CostoEst,PS.UltCosto,PS.ProcFinal,PS.CostoMP,PS.CDI_Adic,PS.CodTarifCDI,TF.Tarifa,PS.Id,PC.Inactivo AS ProcInactivo
,P.CodBarras,P.Referencia,P.TipoRef,G.IdLinea AS CdLinea,Linea,S.IdGrupo AS CdGrupo,Grupo,P.IdSubgrupo AS CdSubgpo,Subgrupo
,P.IdMarca,Marca,P.Color,P.Tamano,P.IdUnd,UM.Unidad AS UndadMed,P.Tallaje,P.Seriales,P.Lotes,P.Combo,P.Inactivo AS ProdInactivo,P.DescripLong
FROM ProdProcesos AS PS INNER JOIN TiposProceso AS PC ON PS.IdTipProc=PC.IdTipProc
INNER JOIN ProdMcias AS P ON PS.IdProducto=P.IdProducto
INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo
INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca
INNER JOIN UndMed AS UM ON P.IdUnd=UM.IdUnd LEFT JOIN Tablapor AS TF ON PS.CodTarifCDI=TF.IdTarifa
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexEnt] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario
,(tmEntradas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmImpGlobal
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmUnidades,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmNumLote,tmFecVceLote,tmCodTarDct,tmCodTarRet,tmCodTarIca
,tmTipDoc,tmDocumento,tmIdCia,tmServcios,Tanques,tmNumInicial,tmVrImvCosto,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmTarifaIco,tmVrImpCon
,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd,tmItemCbo,tmMgenCont,tmRec_Costo
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpFormaspago] @pmIdForma VARCHAR(4),@pmFormaPago VARCHAR(50),@pmConsecutivo INT,@pmCuadreCaja BIT
,@pmRefDcmto BIT,@pmRefNit BIT,@pmRefBco BIT,@pmRefFech BIT,@pmRefAtza BIT,@pmRefOtro BIT,@pmVldRangos BIT,@pmVleConsumo BIT
,@pmCodIntegrity VARCHAR(20),@pmCodValera VARCHAR(10),@pmCodIntCom VARCHAR(20),@pmRefProducto BIT,@pmFactTicket BIT,@pmCdMedPago VARCHAR(4),@pmValCtaPago BIT,@pmInactivo BIT
 AS 
 UPDATE Formaspago SET FormaPago=@pmFormaPago,Consecutivo=@pmConsecutivo,CuadreCaja=@pmCuadreCaja,RefDcmto=@pmRefDcmto,RefNit=@pmRefNit
,RefBco=@pmRefBco,RefFech=@pmRefFech,RefAtza=@pmRefAtza,RefOtro=@pmRefOtro,VldRangos=@pmVldRangos,VleConsumo=@pmVleConsumo
,CodIntegrity=@pmCodIntegrity,CodValera=@pmCodValera,CodIntCom=@pmCodIntCom,RefProducto=@pmRefProducto,FactTicket=@pmFactTicket,CdMedPago=@pmCdMedPago,ValCtaPago=@pmValCtaPago,Inactivo=@pmInactivo
 WHERE IdForma=@pmIdForma
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFormaspago] @pmIdForma VARCHAR(4)
 AS 
 IF @pmIdForma IS NULL
  SELECT IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo
,CodIntegrity,CodValera,CodIntCom,RefProducto,FactTicket,CdMedPago,ValCtaPago
  FROM Formaspago
  WHERE Inactivo=0
  ORDER BY Consecutivo
 ELSE
  SELECT IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo
,CodIntegrity,CodValera,CodIntCom,RefProducto,FactTicket,CdMedPago,ValCtaPago,Inactivo
  FROM Formaspago
  WHERE IdForma=@pmIdForma
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsFormaspago] @pmIdForma VARCHAR(4),@pmFormaPago VARCHAR(50),@pmConsecutivo INT,@pmCuadreCaja BIT,@pmRefDcmto BIT
,@pmRefNit BIT,@pmRefBco BIT,@pmRefFech BIT,@pmRefAtza BIT,@pmRefOtro BIT,@pmVldRangos BIT,@pmVleConsumo BIT
,@pmCodIntegrity VARCHAR(20),@pmCodValera VARCHAR(10),@pmCodIntCom VARCHAR(20),@pmRefProducto BIT,@pmFactTicket BIT,@pmCdMedPago VARCHAR(4),@pmValCtaPago BIT,@pmInactivo BIT
 AS 
 INSERT INTO Formaspago (IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo,CodIntegrity,CodValera,Inactivo,CodIntCom,RefProducto,FactTicket,CdMedPago,ValCtaPago)
 VALUES (@pmIdForma,@pmFormaPago,@pmConsecutivo,@pmCuadreCaja,@pmRefDcmto,@pmRefNit,@pmRefBco,@pmRefFech,@pmRefAtza
,@pmRefOtro,@pmVldRangos,@pmVleConsumo,@pmCodIntegrity,@pmCodValera,@pmInactivo,@pmCodIntCom,@pmRefProducto,@pmFactTicket,@pmCdMedPago,@pmValCtaPago)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Comisiones_Rec] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmTarifaCom DECIMAL(14,4)
 AS 
 INSERT INTO tm_Comisiones (tmEst,TipDoc,Numero,IdCia,Item,Fecha,FecVence,IdVend,TarifaCom,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrOtros,VCargos,VrOtrosDctos
,IdConcepto,CdProducto,Cantidad,nClieCon,IdCajero,CdOperario,ComisnOper,IdLocal,CdSzona,Modalidad,TipRef,DocRef,IdCiaRef,Anulado,Observacion,Detalles,Referencia)
SELECT @pmtmEst,TipRec,A.Recibo,A.IdCia,Item,A.Fecha,A.FecPago,A.IdVend,Comision,A.IdCliente,A.IdAgencia
,CASE WHEN SUBSTRING(A.TipDoc,1,2)='FC' AND A.VrBaseCms>0 AND (A.VrAbono-A.VrBaseCms)>=0 THEN A.VrAbono ELSE A.VrBaseCms END
,CASE WHEN SUBSTRING(A.TipDoc,1,2)='FC' AND A.VrBaseCms>0 AND (A.VrAbono-A.VrBaseCms)>=0 THEN A.VrAbono-A.VrBaseCms ELSE 0 END
,VrReteIVA,VrRetencion+VrReteICA,VrPagosMas,VrOtros,VrDescto,IdConcepto,'0',0,'0',IdCajero,'0',0,IdLocal,'0',Modalidad,A.TipDoc,Factura,IdCiaFac,Anulado,Observacion,Detalle,Referencia
 FROM Trn_VencAbonos AS A INNER JOIN Trn_Recibos AS R ON A.TipRec=R.TipDoc AND A.Recibo=R.Recibo AND A.IdCia=R.IdCia
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  A.IdCia LIKE ISNULL(@pmIdCia,'%%') 
 AND A.IdVend LIKE ISNULL(@pmIdVend,'%') AND A.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND A.IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND IdLocal LIKE ISNULL(@pmIdLocEnv,'%') AND A.Comision>ISNULL(@pmTarifaCom,-1)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsProdProcesos] @pmIdProducto VARCHAR(16),@pmItem INT,@pmIdTipProc VARCHAR(4),@pmCostoEst MONEY,@pmUltCosto MONEY,@pmProcFinal BIT
,@pmCostoMP BIT,@pmCDI_Adic BIT,@pmCodTarifCDI VARCHAR(4)
 AS 
 INSERT INTO ProdProcesos (IdProducto,Item,IdTipProc,CostoEst,UltCosto,ProcFinal,CostoMP,CDI_Adic,CodTarifCDI)
 VALUES (@pmIdProducto,@pmItem,@pmIdTipProc,@pmCostoEst,@pmUltCosto,@pmProcFinal,@pmCostoMP,@pmCDI_Adic,@pmCodTarifCDI)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpProdProcesos] @pmId INT,@pmIdProducto VARCHAR(16),@pmItem INT,@pmIdTipProc VARCHAR(4),@pmCostoEst MONEY,@pmUltCosto MONEY,@pmProcFinal BIT
,@pmCostoMP BIT,@pmCDI_Adic BIT,@pmCodTarifCDI VARCHAR(4)
 AS 
 UPDATE ProdProcesos SET IdProducto=@pmIdProducto,Item=@pmItem,IdTipProc=@pmIdTipProc,CostoEst=@pmCostoEst,UltCosto=@pmUltCosto,ProcFinal=@pmProcFinal
,CostoMP=@pmCostoMP,CDI_Adic=@pmCDI_Adic,CodTarifCDI=@pmCodTarifCDI
 WHERE [Id]=@pmId
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSoporteFmt] @pmTipDoc VARCHAR(3),@pmNumDocIni INT,@pmNumDocFin INT,@pmIdCia CHAR(2)
AS 
 SELECT D.TipDoc,TipoDoc,D.NumDoc,D.IdCia AS CdCia,CI.Compania,D.Fecha,D.IdTercero AS NitTercero,T.RazonSocial,D.IdAdquiriente,N.RazonSocial AS Adquiriente
 ,D.Modalidad,D.IdConcepto,Concepto,D.IdMedPago,MedioPago,D.MetPago,D.NumFactura,D.FechaFac,D.FechaVence,D.SubTotal,D.Descuento,D.Retencion,D.ReteIca,D.Bomberil,D.ImpAviTab,D.OtrosCargos,D.OtrosDctos,D.ValorTotal
 ,D.BaseRet,D.BaseBom,D.TarifaRet,D.TarifaIca,D.TarifaBom,D.TarifaAvta,D.CodTarRet,D.CodTarIca,D.CodTarifBom,D.CodTarifAvta,D.DescServicio
,D.CdCCosto,CCosto,D.CdSubCos,SubCosto,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.TipDocRef,D.NumDocRef,D.IdCiaRef,D.CodCueProv,D.NitCueProv,NP.RazonSocial AS NomProveedor,D.Anulado,D.TipDocDev,D.NumDev,D.CdCiaDev,D.FecDev
,D.Observacion,D.IdEstado AS CdEstado,ED.Estado,D.TimeSys AS FechaCrea,D.OrigenAdd,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
--detalles
,DT.Item,DT.IdProducto,P.DescripProd,DT.Descripcion,DT.Cantidad,DT.VrUnitario,DT.ValorOper,DT.VrDcto,DT.VrRetencion,DT.VrRetIca,DT.VrBomberil,DT.VrAviTab,DT.TarifaDct AS PorcDcto
,DT.TarifaRet AS PorcRet,DT.TarifaIca AS PorcICA,DT.TarifaBom AS PorcBom,DT.TarifaAvta AS PorcAvta,DT.CodTarDct AS DetCodDcto,DT.CodTarRet AS DetCodRet
,DT.CodTarIca AS DetCodIca,DT.CodTarifBom AS DetCodBom,DT.CodTarifAvta AS DetCodAvta,DT.ItemCom,DT.CdCuenta,PC.NomCuenta,DT.CdCueCre,DT.PlacaVeh,DT.Veh_Propio,DT.NitCliente,DT.CodAgencia
--Datos resolución
,D.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,FC.EstadoFE,FC.Prefijo AS FEPrefijo,FC.NumFace,FC.CUFE,FC.CUFE_QR,FC.Resolucion AS FENumResol,FC.RangoNum AS FERangoNum,FC.FecVigencia AS FEVigencia,FC.FechaValidacion AS FEFecValida
FROM Trn_DocSoporte AS D INNER JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
INNER JOIN Trn_DocSoporteDet AS DT ON D.TipDoc=DT.TipDoc AND D.NumDoc=DT.NumDoc AND D.IdCia=DT.IdCia
LEFT JOIN ProdMcias AS P ON DT.IdProducto=P.IdProducto
LEFT JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON D.IdMedPago=MP.IdMedPago
LEFT JOIN TiposComRes AS R ON D.CodRes=R.IdRes LEFT JOIN Terceros AS N ON D.IdAdquiriente=N.IdTercero
LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN Puc AS PC ON DT.CdCuenta=PC.IdCuenta
LEFT JOIN Terceros AS NP ON D.NitCueProv=NP.IdTercero
LEFT JOIN Trn_Face AS FC ON D.TipDoc=FC.TipDoc AND D.NumDoc=FC.Documento AND D.IdCia=FC.IdCia
WHERE D.TipDoc=@pmTipDoc AND D.NumDoc BETWEEN @pmNumDocIni AND @pmNumDocFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySobretasas_Crr]  @pmnAnno INT,@pmnMesIni INT=Null,@pmnMesFin INT=Null,@pmIdDec VARCHAR(4)=Null
,@pmNumDeclaraIni INT=Null,@pmNumDeclaraFin INT=Null,@pmIdEnterr VARCHAR(8)=Null,@pmTipoEntidad VARCHAR(10)=Null
,@pmIdBanco VARCHAR(4)=Null
 AS 
 SELECT  IdDec,NumDeclara,nAnno,nMes,TipoEntidad,S.IdEnterr AS CdEnterr,E.TipoId AS EntTipoID
,NitEntidad,E.Dv AS EntDv,NomEntidad,S.IdClase AS CdClase,ClaseCuenta,S.NumCuenta AS NroCuenta,S.IdBanco AS IdBanc,Banco,CodEntidad,TitularCuenta
--valores
,tmAcpmCant,tmAcpmBase,tmAcpmValor,tmAcpmTarif,tmAcpmPrecio,tmAcpiCant,tmAcpiBase,tmAcpiValor,tmAcpiTarif,tmAcpiPrecio,tmGcorCant,tmGcorBase
,tmGcorValor,tmGcorTarif,tmGcorPrecio,tmGextCant,tmGextBase,tmGextValor,tmGextTarif,tmGextPrecio,tmGimpCant,tmGimpBase,tmGimpValor,tmGimpTarif
,tmGimpPrecio,tmGcoxCant,tmGcoxBase,tmGcoxValor,tmGcoxTarif,tmGcoxPrecio,tmGeoxCant,tmGeoxBase,tmGeoxValor,tmGeoxTarif,tmGeoxPrecio
,tmAczfCant,tmAczfBase,tmAczfValor,tmAczfTarif,tmAczfPrecio,tmGnzfCant,tmGnzfBase,tmGnzfValor,tmGnzfTarif,tmGnzfPrecio,tmGOzfCant,tmGOzfBase,tmGOzfValor,tmGOzfTarif,tmGOzfPrecio
,VrSobretasa,VrSanciones,VrCompensa,VrIntMora,VrTotalPagar,VrFondoSubs,VrRecaudoDep,VrEfectivo,VrOtros,VrTransf,VrPagado,TipEgreso,NumEgreso,CiaEgreso
,FormaPago,S.CdLocCue AS IdLocCue,L.Localidad AS LugarCuenta,L.IdDep AS CdDepLugar,DP.Departamento AS DptoLugarCuenta,NumCheque,CodBanco
,CodCta,NumeroCta,DirEntidad,E.Telefono AS EntTelefono,E.Fax AS EntFax,E.SitioWeb AS EntSitioWeb,EmailEnt,E.CedRepLegal AS CedRepres,NomRepres,CargoRepres
,RazonSocial,LT.Localidad AS NomCiudad,LT.IdDep AS CdDep,DT.Departamento AS NomDpto
,Estado,Fecha,NitDeclara,DvDeclara,CedDeclara,Declarante
 FROM Trn_Sobretasas AS S  INNER JOIN tm_Sobtasas AS T ON S.IdDec=T.tmIdDec AND S.NumDeclara=T.tmNumDeclara
 INNER JOIN SobEntidades AS E ON S.IdEnterr=E.IdEnterr
 INNER JOIN Bancos AS B ON S.IdBanco=B.IdBanco
 INNER JOIN ClaseCta AS CT ON S.IdClase=CT.IdClase
 LEFT JOIN Localidades AS L ON S.CdLocCue=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN Localidades AS LT ON S.IdEnterr=LT.IdLocal LEFT JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep
 LEFT JOIN Terceros AS TE ON E.NitEntidad=TE.IdTercero
 LEFT JOIN CtasCorrientes AS CTA ON S.CodCta=CTA.IdCta
 WHERE nAnno=@pmnAnno AND Estado<=1 AND IdDec LIKE ISNULL(@pmIdDec ,'%')
 AND nMes BETWEEN ISNULL(@pmnMesIni,0)  AND ISNULL(@pmnMesFin,20)
 AND NumDeclara BETWEEN  ISNULL(@pmNumDeclaraIni,0) AND ISNULL(@pmNumDeclaraFin,2147483647)
 AND S.IdEnterr LIKE ISNULL(@pmIdEnterr,'%') AND TipoEntidad  LIKE ISNULL(@pmTipoEntidad ,'%')
 AND S.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 ORDER BY IdDec,NomEntidad,NumDeclara
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySobretasasDet] @pmnAnno INT,@pmnMesIni INT=Null,@pmnMesFin INT=Null
,@pmIdDec VARCHAR(4)=Null,@pmIdEnterr VARCHAR(8)=Null,@pmTipoEntidad VARCHAR(10)=Null,@pmIdBanco VARCHAR(4)=Null
,@pmNumDeclaraIni INT=Null,@pmNumDeclaraFin INT=Null
 AS 
 SELECT S.IdDec AS CdDec,S.NumDeclara AS NroDeclara,nAnno,nMes,S.IdEnterr AS CdEnterr,FormaPago,NomEntidad,Localidad,IdDep
,TipoEntidad,VrSobretasa,VrSanciones,VrCompensa,VrIntMora,VrTotalPagar
,VrEfectivo,VrOtros,VrTransf,VrPagado,Estado,CodBanco,CodCta
,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase,PorcAch
 FROM  Trn_Sobretasas AS S INNER JOIN  Trn_SobDetalle AS D ON S.IdDec=D.IdDec AND S.NumDeclara=D.NumDeclara
 INNER JOIN SobEntidades AS E ON S.IdEnterr=E.IdEnterr
 LEFT JOIN Localidades AS L ON E.IdEnterr=L.IdLocal
 WHERE nAnno=@pmnAnno AND Estado<=1 --2 Y 3 no se incluyen
 AND (nMes>=ISNULL(@pmnMesIni,0)  AND nMes<=ISNULL(@pmnMesFin,2147483647))
 AND S.IdDec LIKE ISNULL(@pmIdDec,'%') AND S.IdEnterr LIKE ISNULL(@pmIdEnterr,'%')
 AND TipoEntidad LIKE ISNULL(@pmTipoEntidad,'%') AND S.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (S.NumDeclara>=ISNULL(@pmNumDeclaraIni,0)  AND S.NumDeclara<=ISNULL(@pmNumDeclaraFin,2147483647))
 ORDER BY S.IdDec,S.NumDeclara,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySobretasas_Cr]  @pmIdDec VARCHAR(4),@pmNumDeclaraIni INT,@pmNumDeclaraFin INT
,@pmnAnnoIni INT=Null,@pmnAnnoFin INT=Null,@pmnMesIni INT=Null,@pmnMesFin INT=Null
,@pmIdEnterr VARCHAR(8)=Null,@pmTipoEntidad VARCHAR(10)=Null,@pmIdBanco VARCHAR(4)=Null,@pmEstado INT=Null
 AS 
 SELECT S.IdDec AS CdDec,Declaracion,S.NumDeclara AS NumeroDec,Fecha,nAnno,nMes,NitDeclara,DvDeclara,CedDeclara,Declarante
,VrSobretasa,VrSanciones,VrCompensa,VrIntMora,VrTotalPagar,VrFondoSubs,VrRecaudoDep,VrEfectivo,VrOtros,VrTransf,VrPagado,TipEgreso,NumEgreso,CiaEgreso
--detalles
,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase,PorcAch
--entidad
,TipoEntidad,S.IdEnterr AS CdEnterr,NomEntidad,E.TipoId AS EntTipoID,NitEntidad,E.Dv AS EntDv,RazonSocial,DirEntidad,E.Telefono AS EntTelefono,E.Fax AS EntFax
,E.SitioWeb AS EntSitioWeb,EmailEnt,E.CedRepLegal AS CedRepres,NomRepres,CargoRepres,FormaPago
,S.IdClase AS CdClase,ClaseCuenta,S.NumCuenta AS NroCuenta,S.IdBanco AS IdBanc,Banco,CodEntidad
,S.CdLocCue AS IdLocCue,L.Localidad AS LugarCuenta,L.IdDep AS CdDepLugar,DP.Departamento AS DptoLugarCuenta,TitularCuenta,NumCheque,CodBanco,CodCta,NumeroCta
,S.TipoFirma AS TipFirma,S.NitRevisor AS IdRevisor,S.NomRevisor AS NombRevisor,S.TpRevisor AS TarjProf,Estado,Adhesivo,FechaPago
,EsCorr,NumCorr,FecCorr,S.Observacion AS Observ,S.IdUsuario AS IdUsuari,Usuario,TimeSys
--información del tipo de declaracion
,TipoDec,TipoIdDec,TD.Direccion AS DirDeclarante,TD.IdLocal AS CdCiuDec,LT.Localidad AS CiuDeclarante
,LT.IdDep AS CdDepDec,DT.Departamento AS DptoDeclarante,TD.Telefono AS TelefDeclara,TD.CedRepLegal AS CedRepDeclara,TD.RepLegal AS RepLegalDeclara
 FROM Trn_Sobretasas AS S INNER JOIN Trn_SobDetalle AS D ON S.IdDec=D.IdDec AND S.NumDeclara=D.NumDeclara
 INNER JOIN SobEntidades AS E ON S.IdEnterr=E.IdEnterr INNER JOIN SobTiposDec AS TD ON S.IdDec=TD.IdDec
 INNER JOIN Bancos AS B ON S.IdBanco=B.IdBanco
 INNER JOIN  ClaseCta AS CT ON S.IdClase=CT.IdClase
 INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LT ON TD.IdLocal=LT.IdLocal INNER JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep
 LEFT JOIN Localidades AS L ON S.CdLocCue=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN Terceros AS T ON E.NitEntidad=T.IdTercero LEFT JOIN CtasCorrientes AS CTA ON S.CodCta=CTA.IdCta
 WHERE S.IdDec LIKE ISNULL(@pmIdDec ,'%')
 AND S.NumDeclara BETWEEN  ISNULL(@pmNumDeclaraIni,0) AND ISNULL(@pmNumDeclaraFin,2147483647)
 AND nMes BETWEEN ISNULL(@pmnMesIni,0)  AND ISNULL(@pmnMesFin,20)
 AND nAnno BETWEEN ISNULL(@pmnAnnoIni,0) AND ISNULL(@pmnAnnoFin,2147483647)
 AND S.IdEnterr LIKE ISNULL(@pmIdEnterr,'%') AND TipoEntidad  LIKE ISNULL(@pmTipoEntidad ,'%')
 AND S.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND (Estado>=ISNULL(@pmEstado,-1)  AND Estado<=ISNULL(@pmEstado,2147483647)) 
 ORDER BY S.NumDeclara,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Sobtasas] @pmtmIdDec VARCHAR(4),@pmtmNumDeclara INT,@pmtmAcpmCant DECIMAL(14,4)
,@pmtmAcpmBase MONEY,@pmtmAcpmValor MONEY,@pmtmAcpmTarif DECIMAL(14,4),@pmtmAcpmPrecio MONEY
,@pmtmAcpiCant DECIMAL(14,4),@pmtmAcpiBase MONEY,@pmtmAcpiValor MONEY,@pmtmAcpiTarif DECIMAL(14,4)
,@pmtmAcpiPrecio MONEY,@pmtmGcorCant DECIMAL(14,4),@pmtmGcorBase MONEY,@pmtmGcorValor MONEY
,@pmtmGcorTarif DECIMAL(14,4),@pmtmGcorPrecio MONEY,@pmtmGextCant DECIMAL(14,4),@pmtmGextBase MONEY
,@pmtmGextValor MONEY,@pmtmGextTarif DECIMAL(14,4),@pmtmGextPrecio MONEY,@pmtmGimpCant DECIMAL(14,4)
,@pmtmGimpBase MONEY,@pmtmGimpValor MONEY,@pmtmGimpTarif DECIMAL(14,4),@pmtmGimpPrecio MONEY
,@pmtmGcoxCant DECIMAL(14,4),@pmtmGcoxBase MONEY,@pmtmGcoxValor MONEY,@pmtmGcoxTarif DECIMAL(14,4)
,@pmtmGcoxPrecio MONEY,@pmtmGeoxCant DECIMAL(14,4),@pmtmGeoxBase MONEY,@pmtmGeoxValor MONEY,@pmtmGeoxTarif DECIMAL(14,4),@pmtmGeoxPrecio MONEY
,@pmtmAczfCant DECIMAL(14,4),@pmtmAczfBase MONEY,@pmtmAczfValor MONEY,@pmtmAczfTarif DECIMAL(14,4) ,@pmtmAczfPrecio MONEY
,@pmtmGnzfCant DECIMAL(14,4),@pmtmGnzfBase MONEY,@pmtmGnzfValor MONEY,@pmtmGnzfTarif DECIMAL(14,4),@pmtmGnzfPrecio MONEY
,@pmtmGOzfCant DECIMAL(14,4),@pmtmGOzfBase MONEY,@pmtmGOzfValor MONEY,@pmtmGOzfTarif DECIMAL(14,4),@pmtmGOzfPrecio MONEY
 AS 
 INSERT INTO tm_Sobtasas (tmIdDec,tmNumDeclara,tmAcpmCant,tmAcpmBase,tmAcpmValor,tmAcpmTarif,tmAcpmPrecio,tmAcpiCant,tmAcpiBase,tmAcpiValor
,tmAcpiTarif,tmAcpiPrecio,tmGcorCant,tmGcorBase,tmGcorValor,tmGcorTarif,tmGcorPrecio,tmGextCant,tmGextBase,tmGextValor,tmGextTarif,tmGextPrecio
,tmGimpCant,tmGimpBase,tmGimpValor,tmGimpTarif,tmGimpPrecio,tmGcoxCant,tmGcoxBase,tmGcoxValor,tmGcoxTarif,tmGcoxPrecio,tmGeoxCant
,tmGeoxBase,tmGeoxValor,tmGeoxTarif,tmGeoxPrecio,tmAczfCant,tmAczfBase,tmAczfValor,tmAczfTarif,tmAczfPrecio,tmGnzfCant
,tmGnzfBase,tmGnzfValor,tmGnzfTarif,tmGnzfPrecio,tmGOzfCant,tmGOzfBase,tmGOzfValor,tmGOzfTarif,tmGOzfPrecio)
 VALUES (@pmtmIdDec,@pmtmNumDeclara,@pmtmAcpmCant,@pmtmAcpmBase,@pmtmAcpmValor,@pmtmAcpmTarif,@pmtmAcpmPrecio,@pmtmAcpiCant
,@pmtmAcpiBase,@pmtmAcpiValor,@pmtmAcpiTarif,@pmtmAcpiPrecio,@pmtmGcorCant,@pmtmGcorBase,@pmtmGcorValor,@pmtmGcorTarif
,@pmtmGcorPrecio,@pmtmGextCant,@pmtmGextBase,@pmtmGextValor,@pmtmGextTarif,@pmtmGextPrecio,@pmtmGimpCant,@pmtmGimpBase
,@pmtmGimpValor,@pmtmGimpTarif,@pmtmGimpPrecio,@pmtmGcoxCant,@pmtmGcoxBase,@pmtmGcoxValor,@pmtmGcoxTarif,@pmtmGcoxPrecio
,@pmtmGeoxCant,@pmtmGeoxBase,@pmtmGeoxValor,@pmtmGeoxTarif,@pmtmGeoxPrecio,@pmtmAczfCant,@pmtmAczfBase,@pmtmAczfValor,@pmtmAczfTarif 
,@pmtmAczfPrecio,@pmtmGnzfCant,@pmtmGnzfBase,@pmtmGnzfValor,@pmtmGnzfTarif,@pmtmGnzfPrecio,@pmtmGOzfCant,@pmtmGOzfBase,@pmtmGOzfValor,@pmtmGOzfTarif,@pmtmGOzfPrecio)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsSobDetalle] @pmIdDec VARCHAR(4),@pmNumDeclara INT,@pmItem INT,@pmConcepto VARCHAR(250)
,@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrBase MONEY,@pmTarifa DECIMAL(14,4),@pmVrLiquida MONEY
,@pmVrPrecio MONEY,@pmClaseProd VARCHAR(20),@pmTarifGalon DECIMAL(14,4),@pmPorcBase DECIMAL(14,4),@pmPorcAch DECIMAL(14,4) 
 AS 
 INSERT INTO Trn_SobDetalle (IdDec,NumDeclara,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase,PorcAch)
 VALUES (@pmIdDec,@pmNumDeclara,@pmItem,@pmConcepto,@pmCantidad,@pmVrUnitario,@pmVrBase,@pmTarifa
,@pmVrLiquida,@pmVrPrecio,@pmClaseProd,@pmTarifGalon,@pmPorcBase,@pmPorcAch)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrySobDetalle] @pmIdDec VARCHAR(4),@pmNumDeclara INT,@pmItem INT
 AS 
 SELECT IdDec,NumDeclara,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase,PorcAch
 FROM Trn_SobDetalle
 WHERE IdDec=@pmIdDec AND NumDeclara=@pmNumDeclara
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY  Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryClaseTarImp]
 AS 
  SELECT IdClase,NomClase
  FROM ClaseTar
  WHERE Inactivo=0 AND IdClase IN ('DEP','GLO','ICA','IVA','MUN','NAC','RET','RIV','SOB','SOL','MGM','PRE','TSN','TSD','TSM','SUB','MAR','COS','IVI','IGP','MGC','MGN','CSE','INC','BIM','BIC','ICO','BIP','IVP','BPC','DTR','TST','PRD','PRM','PRT','PRN')
  ORDER BY NomClase
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySobProductosLta] 
 AS 
 SELECT S.IdClase,S.IdProducto AS CdProducto,P.DescripProd,S.ClaseProd AS CodClase,C.ClaseProd
 FROM SobProductos AS S INNER JOIN ProdMcias AS P ON S.IdProducto=P.IdProducto
 INNER JOIN ClasesProd AS C ON S.ClaseProd=C.IdClaseProd
 ORDER BY S.IdClase
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySobGasOxigenLta] @pmIdDec VARCHAR(4),@pmNumDeclaraIni INT,@pmNumDeclaraFin INT
AS 
SELECT D.IdDec,D.NumDeclara,D.Item,D.ItemDet,D.ClaseProd,D.IdProducto,P.DescripProd,D.Cantidad,D.Tarifa,D.PorcBase
,D.PorcAch,D.BaseGrav,D.VrSobretasa,D.PrecioRef
FROM Trn_SobGasOxigen AS D INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
WHERE D.IdDec=@pmIdDec AND D.NumDeclara BETWEEN ISNULL(@pmNumDeclaraIni,0) AND ISNULL(@pmNumDeclaraFin,2147483647)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionDet] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Fecha,IdRespons,IdCCosto,IdSubCos,IdDep,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad
,DirEntrega,IdLocEnt,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev,Observacion,IdEstado
,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,D.Cantidad AS CantArt,IdUnd,VrUnitario,D.Referencia
,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.Cant_Orden,D.CantSalida,D.CantDevSal,D.CodCCtoDet,D.CodSCtoDet,D.PlacaVehDet
,D.TipSal,D.NSalida,D.IdCiaSal,D.FechaSal
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion
 AND R.IdCia=D.IdCia
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion=@pmRequisicion AND R.IdCia=@pmIdCia
 ORDER BY Item
GO
--may 2/2022

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuPrestamosLin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuPrestamosLin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuPrestamosLinCau]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuPrestamosLinCau]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasRac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinCuotasRac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasRmc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinCuotasRmc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinAbonosRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinAbonosRes]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAcuPrestamosLin]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAcuPrestamosLin]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAcuPrestamosLinA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAcuPrestamosLinA]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFinCuotasRmc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)
 AS 
SELECT C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia,CL.NomAgencia,C.TipDoc,TD.TipoDoc,C.IdPrestamo,C.IdCia,CI.Compania,C.Item,C.Fecha,C.FechaVence
,C.Concepto,C.Detalle,C.VrTotal,C.VrAbonado AS TotalAbonado,C.NumCuota
,A.TipRec,TR.TipoDoc AS TipoRecibo,A.Recibo,A.IdCia AS CdCiaRec,CR.Compania AS NomCiaRecibo,A.Fecha AS FecRecibo,A.FecPago,A.TotalAbono AS VrAbono,A.Detalle AS DetalleAbono
--Datos tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.Telefono,T.TelMovil,T.e_mail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,CL.IdGrupo AS CdGrupoCli,GrupoClie,CL.IdTipoTerc AS CdTipoTerc,TipoTercero
FROM Trn_FinCuotas AS C LEFT JOIN Trn_FinAbonos AS A ON C.TipDoc=A.TipDoc AND C.IdPrestamo=A.IdPrestamo AND C.IdCia=A.IdCiaPre AND C.Item=A.ItemPre
INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc 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 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 TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
LEFT JOIN Sys_TiposDoc AS TR ON A.TipRec=TR.IdDoc LEFT JOIN Companias AS CR ON A.IdCia=CR.IdCia
WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (C.IdCia=@pmIdCia OR @pmIdCia IS NULL) 
UNION ALL
SELECT A.IdCliente,T.RazonSocial,A.IdAgencia,CL.NomAgencia,'SIA','SALDO INICIAL DE APORTES',A.Id,A.IdCiaCrea,CI.Compania,A.Item,A.FecInicio,A.FecFinal
,'APORTES',A.Descripcion,A.ValorTotal,A.ValorTotal,A.CantCuotas
,'SIA','ABONOS SALDOS INICIALES',A.Id,A.IdCiaCrea,CI.Compania,A.FecInicio,A.FecFinal,A.ValorTotal,'SALDOS INCINIALES DE APORTES'
--Datos del tercero
,T.TipoId,T.Dv,T.Direccion,T.IdLocal,L.Localidad,L.IdDep,D.Departamento,T.Telefono,T.TelMovil,T.e_mail,T.IdSector,SectorEco,T.IdRegimen,Regimen,CL.IdGrupo,GrupoClie,CL.IdTipoTerc,TipoTercero
FROM Trn_FinCiAportes AS A INNER JOIN Terceros AS T ON A.IdCliente=T.IdTercero INNER JOIN Companias AS CI ON A.IdCiaCrea=CI.IdCia
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 TercCliePrestamo AS CL ON A.IdCliente=CL.IdClie AND A.IdAgencia=CL.IdAgencia
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
WHERE A.FecFinal BETWEEN @pmFechaIni AND @pmFechaFin
AND (A.IdCiaCrea=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFinAbonosRes] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)
 AS 
 SELECT A.TipRec,TR.TipoDoc AS TipoDocRec,A.Recibo,A.IdCia,CI.Compania,A.Item AS ItemRec,A.Fecha AS FechaRec,A.FecPago
,A.TipDoc,TD.TipoDoc,A.IdPrestamo AS NumDoc,A.IdCiaPre,CD.Compania AS NomCiaDoc,A.ItemPre,A.TotalAbono,A.Detalle AS DetallePago
,A.IdCliente,T.RazonSocial AS NomCliente,A.IdAgencia,CL.NomAgencia,A.IdVend,VN.RazonSocial AS Vendedor,A.VrBaseCms,A.Comision,A.TipoAplica
,C.FechaVence,C.NumCuota,C.Concepto,C.CodLinCred
 FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia  INNER JOIN Companias AS CD ON A.IdCiaPre=CD.IdCia
 INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc INNER JOIN Sys_TiposDoc AS TR ON A.TipRec=TR.IdDoc
 INNER JOIN Terceros AS T ON A.IdCliente=T.IdTercero
 LEFT JOIN TercCliePrestamo AS CL ON A.IdCliente=CL.IdClie AND A.IdAgencia=CL.IdAgencia
 LEFT JOIN Terceros AS VN ON A.IdVend=VN.IdTercero 
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCiaPre=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAcuPrestamosLinCau] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
AS 
INSERT INTO AcuPrestamosLin (nAnno,nMes,IdCia,IdCliente,IdAgencia,IdLinea,Concepto,SaldoAnt,TotalCargos,TotalAbonos
,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas,OtrosAbonos,DevAportes,SaAportes)
SELECT YEAR(Fecha),MONTH(Fecha),IdCia,IdCliente,IdAgencia,ISNULL(CodLinCred,'0001'),Concepto,0,SUM(VrTotal),0
,SUM(CASE WHEN TipDoc IN ('PR1','CVN','APO') THEN VrTotal ELSE 0 END),0,0,0
,SUM(CASE WHEN TipDoc='ND2' THEN VrTotal ELSE 0 END),0
,SUM(CASE WHEN TipDoc='FIF' THEN VrTotal ELSE 0 END),0,0,0,0
FROM Trn_FinCuotas
WHERE (TipDoc IN ('ND2','FIF','CVN','APO') OR (TipDoc='PR1' AND Concepto<>'CAPITAL'))
AND YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes 
AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
GROUP BY YEAR(Fecha),MONTH(Fecha),IdCia,IdCliente,IdAgencia,CodLinCred,Concepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAcuPrestamosLin] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmConcepto VARCHAR(10),@pmSaldoAnt MONEY,@pmTotalCargos MONEY,@pmTotalAbonos MONEY
,@pmPrestamos MONEY,@pmDevPrestamos MONEY,@pmRecibos MONEY,@pmDevRecibos MONEY,@pmNotasDeb MONEY,@pmNotasCre MONEY,@pmFacturas MONEY
,@pmDevFacturas MONEY,@pmOtrosAbonos MONEY,@pmDevAportes MONEY,@pmSaAportes MONEY
 AS 
 INSERT INTO AcuPrestamosLin (nAnno,nMes,IdCia,IdCliente,IdAgencia,IdLinea,Concepto,SaldoAnt,TotalCargos,TotalAbonos
,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas,OtrosAbonos,DevAportes,SaAportes)
 VALUES (@pmnAnno,@pmnMes,@pmIdCia,@pmIdCliente,@pmIdAgencia,@pmIdLinea,@pmConcepto,@pmSaldoAnt,@pmTotalCargos,@pmTotalAbonos
,@pmPrestamos,@pmDevPrestamos,@pmRecibos,@pmDevRecibos,@pmNotasDeb,@pmNotasCre,@pmFacturas,@pmDevFacturas,@pmOtrosAbonos,@pmDevAportes,@pmSaAportes)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAcuPrestamosLin] (@pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmConcepto VARCHAR(10))
RETURNS MONEY
 AS  
BEGIN 
 DECLARE @vrSaldoAnt MONEY
 SET  @vrSaldoAnt=(SELECT ISNULL(SUM(SaldoAnt+TotalCargos-TotalAbonos),0) FROM AcuPrestamosLin
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCliente=@pmIdCliente AND (IdAgencia=@pmIdAgencia OR @pmIdAgencia IS NULL)
 AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
 AND (IdLinea=@pmIdLinea OR @pmIdLinea IS NULL)
 AND (Concepto=@pmConcepto OR @pmConcepto IS NULL)
 )
 RETURN @vrSaldoAnt
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAcuPrestamosLinA] (@pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmConcepto VARCHAR(10))
RETURNS MONEY
 AS  
BEGIN 
 DECLARE @vrSaldoAnt MONEY
 SET  @vrSaldoAnt=(SELECT ISNULL(SUM((SaAportes+Recibos+OtrosAbonos)-(DevRecibos+DevAportes)),0) FROM AcuPrestamosLin
 WHERE nAnno=@pmnAnno AND nMes=@pmnMes AND IdCliente=@pmIdCliente
 AND Concepto=@pmConcepto
 AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
 AND (IdAgencia=@pmIdAgencia OR @pmIdAgencia IS NULL)
 AND (IdLinea=@pmIdLinea OR @pmIdLinea IS NULL)
 )
 RETURN @vrSaldoAnt
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[paQryFinCuotasRac] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmAnnoAnt INT,@pmMesAnt INT,@pmIdCia CHAR(2)
 AS 
SELECT C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia,CL.NomAgencia,C.TipoReg,C.TipDoc,TD.TipoDoc,C.IdPrestamo,C.IdCia,CI.Compania,C.Item,C.NumCuota,C.Fecha,C.FechaVence
,C.Concepto,C.Detalle,C.CodLinCred,LC.LinCredito,C.VrTotal,C.VrAbono,C.TotalAbonado,C.VrOtros
,C.TipDocRef,C.NumDocRef,C.IdCiaRef,C.ItemDocRef,C.IdVend,VN.RazonSocial AS Vendedor,C.TipoAplica,C.FechaCrea
,CASE WHEN C.Concepto='APORTES' THEN [dbo].[FuncAcuPrestamosLinA](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,Null,C.Concepto) ELSE [dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,Null,C.Concepto) END AS SaldoAnt
,CASE WHEN C.Concepto='APORTES' THEN [dbo].[FuncAcuPrestamosLinA](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,Null,C.Concepto) ELSE [dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,Null,C.Concepto) END AS SaldoAntCia
,[dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,Null,C.IdCliente,C.IdAgencia,Null,Null) AS SaldoAntClie
,[dbo].[FuncAcuPrestamosLin](@pmAnnoAnt,@pmMesAnt,C.IdCia,C.IdCliente,C.IdAgencia,Null,Null) AS SaldoAntClieCia
--datos de terceros
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.Telefono,T.TelMovil,T.e_mail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,CL.IdGrupo AS CdGrupoCli,GrupoClie,CL.IdTipoTerc AS CdTipoTerc,TipoTercero
--SUBCONSULTA DE TABLAS UNIDAS
FROM (SELECT C.IdCliente,C.IdAgencia,1 AS TipoReg,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,NumCuota,C.Fecha,C.FechaVence,C.Concepto,C.Detalle,C.CodLinCred
,C.VrTotal,C.VrAbonado*0 AS VrAbono,C.VrAbonado AS TotalAbonado,C.VrAcumCapital*0 AS VrOtros,C.TipoCausac AS TipDocRef,C.NumCausacion AS NumDocRef,C.IdCiaCau AS IdCiaRef,C.NumCuota AS ItemDocRef
,CL.IdVend,C.TipoCuota AS TipoAplica,C.FechaCrea
FROM Trn_FinCuotas AS C LEFT JOIN TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (C.IdCia=@pmIdCia OR @pmIdCia IS NULL) 
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipRec,A.Recibo,A.IdCia,A.Item,C.NumCuota,A.Fecha,A.FecPago,C.Concepto,A.Detalle,C.CodLinCred
,0,A.TotalAbono,C.VrAbonado,0,A.TipDoc,A.IdPrestamo,A.IdCiaPre,A.ItemPre,A.IdVend,A.TipoAplica,A.TimeSys
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)  
AND A.TipRec<>'RC2' AND A.TipRec<>'OD2'
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipRec,A.Recibo,A.IdCia,A.Item,C.NumCuota,A.Fecha,A.FecPago,C.Concepto,A.Detalle,C.CodLinCred
,0,A.TotalAbono,C.VrAbonado,CASE WHEN CHARINDEX('{APORTES}',R.Observacion)>0 THEN R.VrPagosMas ELSE 0 END 
,A.TipDoc,A.IdPrestamo,A.IdCiaPre,A.ItemPre,A.IdVend,A.TipoAplica,A.TimeSys
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
INNER JOIN Trn_FinRecibos AS R ON A.TipRec=R.TipDoc AND A.Recibo=R.Recibo AND A.IdCia=R.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND A.TipRec='RC2'
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipRec,A.Recibo,A.IdCia,A.Item,C.NumCuota,A.Fecha,A.FecPago,C.Concepto,A.Detalle,C.CodLinCred
,0,A.TotalAbono,C.VrAbonado,CASE WHEN CHARINDEX('{APORTES}',R.Observacion)>0 THEN D.VrPagosMas*-1 ELSE 0 END 
,A.TipDoc,A.IdPrestamo,A.IdCiaPre,A.ItemPre,A.IdVend,A.TipoAplica,A.TimeSys
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
INNER JOIN Trn_FinDevRec AS D ON A.TipRec=D.TipDev AND A.Recibo=D.Devolucion AND A.IdCia=D.IdCia
INNER JOIN Trn_FinRecibos AS R ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND A.TipRec='OD2'
UNION ALL
SELECT A.IdCliente,A.IdAgencia,2,A.TipDoc,A.Recibo,A.IdCia,0,0,A.Fecha,R.FecPago
,A.Concepto,'SALDOS A FAVOR',A.CodLinCred,0,(A.VrAFavor-A.VrOtrosDb)-A.VrAbono,A.VrAbono,0
,R.TipDcm,R.Documento,R.IdCiaDcm,0,A.IdVend,1,R.TimeSys
FROM Trn_FinAfavor AS A INNER JOIN Trn_FinRecibos AS R ON A.TipDoc=R.TipDoc AND A.Recibo=R.Recibo AND A.IdCia=R.IdCia
WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (A.VrAFavor-A.VrOtrosDb)>A.VrAbono
UNION ALL
SELECT A.IdCliente,A.IdAgencia,0,'SIA',A.Id,A.IdCiaCrea,A.Item,A.CantCuotas,A.FecInicio,A.FecFinal,'APORTES',A.Descripcion,'0'
,A.ValorTotal,A.ValorTotal,A.ValorTotal,0,'SIA',0,'00',0,CL.IdVend,0,A.FechaCrea
FROM Trn_FinCiAportes AS A LEFT JOIN TercCliePrestamo AS CL ON A.IdCliente=CL.IdClie AND A.IdAgencia=CL.IdAgencia 
WHERE A.FecFinal BETWEEN @pmFechaIni AND @pmFechaFin
AND (A.IdCiaCrea=@pmIdCia OR @pmIdCia IS NULL) 
) 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 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 CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
INNER JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc LEFT JOIN LineasCred AS LC ON C.CodLinCred=LC.IdLinea
LEFT JOIN Terceros AS VN ON C.IdVend=VN.IdTercero
--FIN SUBCONSULTA 
-- UNION CON SALDOS ANTERIORES -NO REGISTRADO
UNION ALL

SELECT S.IdCliente,T.RazonSocial,S.IdAgencia,CL.NomAgencia,-1,'SA','SALDO ANTERIOR',0,S.IdCia,CI.Compania,0,0,@pmFechaIni,@pmFechaIni
,S.Concepto,'SALDO ANTERIOR',S.IdLinea,LC.LinCredito,0,0,0,0,'SA',0,'00',0,CL.IdVend,VN.RazonSocial,-1,@pmFechaIni
,CASE WHEN S.Concepto='APORTES' THEN (S.SaAportes+S.Recibos+S.OtrosAbonos)-(S.DevRecibos+S.DevAportes) ELSE S.SaldoAnt+S.TotalCargos-S.TotalAbonos END
,0,0,0
--datos de terceros
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto
,T.Telefono,T.TelMovil,T.e_mail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,CL.IdGrupo AS CdGrupoCli,GrupoClie,CL.IdTipoTerc AS CdTipoTerc,TipoTercero
FROM AcuPrestamosLin AS S INNER JOIN Companias AS CI ON S.IdCia=CI.IdCia INNER JOIN Terceros AS T ON S.IdCliente=T.IdTercero 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
LEFT JOIN TercCliePrestamo AS CL ON S.IdCliente=CL.IdClie AND S.IdAgencia=CL.IdAgencia
LEFT JOIN GruposCli AS GC ON CL.IdGrupo=GC.IdGrupo LEFT JOIN TiposTerc AS TTC ON CL.IdTipoTerc=TTC.IdTipoTerc
LEFT JOIN LineasCred AS LC ON S.IdLinea=LC.IdLinea LEFT JOIN Terceros AS VN ON CL.IdVend=VN.IdTercero
WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND (S.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND NOT EXISTS (SELECT * FROM (SELECT IdCliente,IdAgencia,TipDoc,IdPrestamo,IdCia,Concepto,CodLinCred FROM Trn_FinCuotas
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (IdCia=@pmIdCia OR @pmIdCia IS NULL) 
 UNION ALL
 SELECT A.IdCliente,A.IdAgencia,A.TipRec,A.Recibo,A.IdCia,C.Concepto,C.CodLinCred
 FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)  
 UNION ALL
 SELECT A.IdCliente,A.IdAgencia,A.TipDoc,A.Recibo,A.IdCia,A.Concepto,A.CodLinCred
 FROM Trn_FinAfavor AS A 
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (A.VrAFavor-A.VrOtrosDb)>A.VrAbono) AS RC 
 WHERE RC.IdCliente=S.IdCliente AND RC.IdAgencia=S.IdAgencia AND RC.IdCia=S.IdCia AND RC.Concepto=S.Concepto AND RC.CodLinCred=S.IdLinea)
 AND (CASE WHEN S.Concepto='APORTES' THEN (S.SaAportes+S.Recibos+S.OtrosAbonos)-(S.DevRecibos+S.DevAportes) ELSE S.SaldoAnt+S.TotalCargos-S.TotalAbonos END)<>0


--mayo 10/2022

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsPagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsPagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagos_Lta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos_Lta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPagos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPagos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_EdsFormas_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_EdsFormas_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_EdsFormasDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_EdsFormasDso]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryPagos_Lta] @pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdForma VARCHAR(4)=Null
,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmEsCaja BIT=Null,@pmIdBanco VARCHAR(4)=Null
,@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null,@pmNitCliente VARCHAR(16)=Null,@pmIdUsuario VARCHAR(11)=Null
 AS 
 SELECT TipDoc,TipoDoc,Documento,P.IdCia AS CodCia,Compania,Fecha,Item,P.IdForma AS CdForma,FormaPago,Detalle
,VrPagado,VrCambio,EsCaja,P.IdBanco AS CodBanco,B.Banco AS NomBanco,NumForma,FecForma,CtaForma,Beneficiario
,NitCliente,RazonSocial,CdAgencia,Agencia,CodAgencia,A.Referencia AS RefeAgencia,Referncia1,Referncia2,NumAutoriza,CdLocal,LF.Localidad AS CiudadPlaza,LF.IdDep AS CdDptoPlaza
,DF.Departamento AS DptoPlaza,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BC.Banco AS CtaBanco,VrDenom,Cantidad,TipDenom
,TipRef,DocRef,IdCiaRef,ItemDoc,P.CdProducto,FE_Ticket,Cant_Gals,PrecioBase,PrecioUnit,P.ConsCP,P.CodCueCons,P.PrecioPub,P.RefVehiculo,P.RefNumVeh,P.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,TipoId,Dv,T.Codigo AS TerCodigo,NomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,L.IdDep AS TercCdDep,D.Departamento AS TercDpto,DocAnulado,NumDev,FecDev
 FROM  Trn_Pagos AS P INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS B ON P.IdBanco=B.IdBanco
 LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero  LEFT JOIN Agencias  AS A ON P.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal  LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep 
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 LEFT JOIN CtasCorrientes AS CTA ON P.CdCta=CTA.IdCta LEFT JOIN  Bancos AS BC ON CTA.IdBanco=BC.IdBanco
 LEFT JOIN (SELECT RC.TipDoc AS DocTipo,RC.Recibo AS DocNumero,RC.IdCia AS DocCia,RC.Anulado AS DocAnulado,NumDev,FecDev FROM Trn_Recibos AS RC 
  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Factura,IdCia,Anulado,NumDev,FecDev FROM Trn_Factcon  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Consignacion,IdCia,Anulado,NumDev,FecDev FROM Trn_Consigna  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Recibo,IdCia,Anulado,NumDev,FecDev FROM Trn_CompCaja  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  UNION SELECT TipDoc,Planilla,IdCia,Anulado,NumDev,FecDev FROM Trn_EdsCortes  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  ) AS DOC ON P.TipDoc=DOC.DocTipo AND P.Documento=DOC.DocNumero  AND P.IdCia=DOC.DocCia
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdForma  LIKE ISNULL(@pmIdForma,'%') AND P.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND  (EsCaja=ISNULL(@pmEsCaja,0) or EsCaja=ISNULL(@pmEsCaja,1))
 AND Documento BETWEEN ISNULL(@pmDocumentoIni,0) AND ISNULL(@pmDocumentoFin,2147483647)
 AND  NitCliente LIKE ISNULL(@pmNitCliente,'%') AND P.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 ORDER BY TipDoc,P.IdCia,Documento,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_EdsFormasDso] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmCredito,tmIdForma,FormaPago,tmTipoRango,tmIdRango,tmNumSerie,tmNumForma,tmNForma,tmVrTotal
,tmIdBanco,Banco,tmObservacion,tmIdCuenta,tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,tmpVehiculo,tmnVehiculo
,tmIdVend,V.RazonSocial AS Vendedor,tmCdProducto,tmCantidad,tmReferencia,tmReferncia2,tmFecForma,tmCtaForma,tmBeneficiario
,tmIdUsuario,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantDenm,tmTipDenom,tmCdCta,tmEsCaja,tmCantPuntos
,tmTipAplica,tmNumero,tmCdCCosto,tmCdSubCos,tmVrPrecio,tmVrPrecio*tmCantidad AS VrPublico
,CASE WHEN tmVrPrecio<>0 THEN tmVrTotal-(tmVrPrecio*tmCantidad) ELSE 0 END AS Excedente
,tmVrBase,tmVrPresp
 FROM tm_EdsFormas AS P INNER JOIN Formaspago AS F ON P.tmIdForma=F.IdForma
 INNER JOIN Bancos AS B ON P.tmIdBanco=B.IdBanco
 INNER JOIN Terceros AS T ON P.tmIdCliente=T.IdTercero
 LEFT JOIN Terceros AS V ON P.tmIdVend=V.IdTercero
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_EdsFormas_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_EdsFormas (tmNumero,tmItem,tmCredito,tmTipoRango,tmIdRango,tmIdForma,tmObservacion,tmEsCaja,tmIdBanco,tmNForma,tmFecForma,tmCtaForma,tmBeneficiario,tmNumSerie,tmNumForma
,tmVrTotal,tmIdCliente,tmIdAgencia,tmIdCuenta,tmpVehiculo,tmnVehiculo,tmIdVend,tmCdProducto,tmCantidad,tmReferencia,tmReferncia2,tmCantPuntos,tmCdLocal,tmNumAutoriza,tmVrDenom,tmCantDenm
,tmTipDenom,tmCdCta,tmTipAplica,tmIdUsuario,tmCdCCosto,tmCdSubCos,tmVrPrecio,tmVrBase,tmVrPresp)
 SELECT @pmtmNumero,Item,0,CASE WHEN FE_Ticket=1 THEN 'TKT' ELSE '0' END,0,IdForma,Detalle,EsCaja,IdBanco,NumForma,FecForma,CtaForma,Beneficiario,'0',0
,VrPagado,NitCliente,CdAgencia,'0',RefVehiculo,RefNumVeh,'0',ISNULL(CdProducto,'0'),Cant_Gals,Referncia1,Referncia2,0,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,CdCta,'',IdUsuario,'0','0',PrecioPub,PrecioBase,PrecioUnit
 FROM Trn_Pagos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsPagos] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmIdForma VARCHAR(4),@pmDetalle VARCHAR(150),@pmVrPagado MONEY,@pmVrCambio MONEY
,@pmEsCaja BIT,@pmIdBanco VARCHAR(4),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmNumForma VARCHAR(20),@pmFecForma SMALLDATETIME,@pmCtaForma VARCHAR(30),@pmBeneficiario VARCHAR(150),@pmReferncia1 VARCHAR(50)
,@pmReferncia2 VARCHAR(50),@pmCdLocal VARCHAR(8),@pmNumAutoriza VARCHAR(30),@pmVrDenom MONEY,@pmCantidad INT,@pmTipDenom CHAR(1),@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmItemDoc INT
,@pmCdCta VARCHAR(4),@pmCdProducto VARCHAR(16),@pmFE_Ticket BIT,@pmCant_Gals DECIMAL(14,4),@pmPrecioBase MONEY,@pmPrecioUnit MONEY,@pmConsCP BIT,@pmCodCueCons VARCHAR(16)
,@pmPrecioPub MONEY,@pmRefVehiculo VARCHAR(10),@pmRefNumVeh VARCHAR(10),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Pagos (TipDoc,Documento,IdCia,Item,Fecha,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta,IdUsuario
,CdProducto,FE_Ticket,Cant_Gals,PrecioBase,PrecioUnit,ConsCP,CodCueCons,PrecioPub,RefVehiculo,RefNumVeh)
 VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmItem,@pmFecha,@pmIdForma,@pmDetalle,@pmVrPagado,@pmVrCambio,@pmEsCaja,@pmIdBanco,@pmNitCliente,@pmCdAgencia,@pmNumForma,@pmFecForma,@pmCtaForma,@pmBeneficiario,@pmReferncia1,@pmReferncia2
,@pmCdLocal,@pmNumAutoriza,@pmVrDenom,@pmCantidad,@pmTipDenom,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmItemDoc,@pmCdCta,@pmIdUsuario,@pmCdProducto,@pmFE_Ticket,@pmCant_Gals,@pmPrecioBase,@pmPrecioUnit,@pmConsCP,@pmCodCueCons,@pmPrecioPub,@pmRefVehiculo,@pmRefNumVeh)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPagos] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Documento,IdCia,Item,Fecha,IdForma,Detalle,VrPagado,VrCambio,EsCaja,IdBanco,NitCliente,CdAgencia,NumForma,FecForma
,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,CdCta
,CdProducto,FE_Ticket,Cant_Gals,PrecioBase,PrecioUnit,ConsCP,CodCueCons,PrecioPub,RefVehiculo,RefNumVeh,IdUsuario
 FROM Trn_Pagos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO