
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
