if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilDespClieProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsOilDespClieProd] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilFacturaProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsOilFacturaProd] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsProdPrecCrudo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsProdPrecCrudo] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasFmtb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFacturasFmtb] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilDespClieFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryOilDespClieFmt] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdPrecCrudoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryProdPrecCrudoLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpProdPrecCrudo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpProdPrecCrudo] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryFacturasFmtb] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2) AS SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente ,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes ,VrOtros,VrCargos,VrOtrDcto,VrImpCarbono,F.VrIvaIngProd,F.VrReteCREE,F.VrAutRetIca,F.VrAutRetFte,F.VrNeto,F.VrAplicado,F.Cantidad AS CantTotal,F.BaseImp,F.BaseRet,F.BaseIca,F.BaseRiv,F.BaseIvaIgp ,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.TarifaRtc,F.TarifaArf,F.TarifaAri,F.IdCCosto,CC.CCosto AS CentCosto,F.IdSubCos,SC.SubCosto AS SubcCosto ,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,F.TarifaCom,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,F.DiasEntraga ,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,F.CargoContac,F.IdForma AS CdForma,FormaPago,F.DetallePago ,F.MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,F.TipRem,F.Remision,F.IdCiaRem,F.FecPedido ,F.AutzaMora,F.AutzaCupo,F.Modalidad,F.TipCom,TCM.TipoCom,F.Comprobante,F.IdCiaCom,F.Anulado,F.NumDev,F.FecDev,F.Observacion,F.ZonaFrontera,TipoFE,FechaFE,F.IdEstado AS CdEstado,Estado ,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,F.OrigenAdd,TD.Leyenda AS TdcDescripcion --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,DP.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 ,NitRepLeg,NomRepLeg,ExcIva,Autoret,VrCupo,VrSaldo,A.CodSicom --Detalles ,D.Item,D.IdTanque,D.IdProducto AS CodProducto,D.Descripcion,PM.DescripProd,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.TarifaDct,D.VrDcto,D.TarifaIva,D.VrIva,D.TarifaRet,D.VrRetFte,D.TarifaIca,D.VrRetIca,D.FleteUnd,D.CostoUnit ,D.Temperatura,D.TempLab,D.UnidTemp,D.ValorAPI,D.API_Corregido,D.Densidad,D.TipoProd,D.TipGuia,D.NumGuia,D.IdCiaGuia,D.CantGuia,D.IndMezcla,D.ItemMezcla,D.FactorMezcla,D.NumTasas,D.TasaBrent,D.TasaWti,D.TRMdolar,D.Puntos ,D.CodTarIva,D.CodTarDct,D.CodTarRet,D.CodTarIca,D.CdMoneda,D.CodSicomPro,D.CodBodega,Bodega,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo ,DM.Comentarios AS Comentario,Nota1,Nota2,Nota3,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia,FE.CUFE_QR AS QR,FE.FechaValidacion FROM Trn_Facturas AS F INNER JOIN Trn_OilFacturaProd AS D ON F.TipDoc=D.TipDoc AND F.Factura=D.Factura AND F.IdCia=D.IdCia INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto INNER JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector INNER JOIN ProdMcias AS PM ON D.IdProducto=PM.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo LEFT JOIN Bodegas AS B ON D.CodBodega=B.IdBodega LEFT JOIN Tanques AS TQ ON D.IdTanque=TQ.IdTanque LEFT JOIN Localidades AS LE ON F.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON F.TipCom=TCM.IdCom LEFT JOIN Trn_DocMemo AS DM ON F.TipDoc=DM.TipDoc AND F.Factura=DM.Documento AND F.IdCia=DM.IdCia LEFT JOIN Trn_Face AS FE ON F.TipDoc=FE.TipDoc AND F.Factura=FE.Documento AND F.IdCia=FE.IdCia WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia AND D.IndMezcla<>2 GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paUpProdPrecCrudo] @pmTipoTarif VARCHAR(3),@pmNumero INT,@pmIdCia CHAR(2),@pmIdProducto VARCHAR(16),@pmIdTercero VARCHAR(16),@pmDuracion VARCHAR(10),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME ,@pmTarifa DECIMAL(16,6),@pmIdMoneda VARCHAR(5),@pmUndMed VARCHAR(10),@pmCodCiudad VARCHAR(8),@pmInactivo BIT AS UPDATE ProdPrecCrudo SET IdCia=@pmIdCia,IdProducto=@pmIdProducto,IdTercero=@pmIdTercero,Duracion=@pmDuracion,FecInicio=@pmFecInicio,FecFinal=@pmFecFinal,Tarifa=@pmTarifa,IdMoneda=@pmIdMoneda ,UndMed=@pmUndMed,CodCiudad=@pmCodCiudad,Inactivo=@pmInactivo WHERE TipoTarif=@pmTipoTarif AND Numero=@pmNumero GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsProdPrecCrudo] @pmTipoTarif VARCHAR(3),@pmNumero INT,@pmIdCia CHAR(2),@pmIdProducto VARCHAR(16),@pmIdTercero VARCHAR(16),@pmDuracion VARCHAR(10),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmTarifa DECIMAL(16,6) ,@pmIdMoneda VARCHAR(5),@pmUndMed VARCHAR(10),@pmCodCiudad VARCHAR(8),@pmInactivo BIT AS INSERT INTO ProdPrecCrudo (TipoTarif,Numero,IdCia,IdProducto,IdTercero,Duracion,FecInicio,FecFinal,Tarifa,IdMoneda,UndMed,Inactivo,CodCiudad) VALUES (@pmTipoTarif,@pmNumero,@pmIdCia,@pmIdProducto,@pmIdTercero,@pmDuracion,@pmFecInicio,@pmFecFinal,@pmTarifa,@pmIdMoneda,@pmUndMed,@pmInactivo,@pmCodCiudad) GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryProdPrecCrudoLta] @pmTipoTarif VARCHAR(3) AS SELECT T.TipoTarif,T.Numero,T.IdCia,Compania,T.IdProducto,P.DescripProd,T.IdTercero,N.RazonSocial,T.CodCiudad,Localidad ,T.Duracion,T.FecInicio,T.FecFinal,T.Tarifa,T.IdMoneda,M.Mneda AS Moneda,T.UndMed,UM.Unidad,T.Inactivo FROM ProdPrecCrudo AS T INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto INNER JOIN adm_monedas AS M ON T.IdMoneda=M.IdMneda INNER JOIN Companias AS C ON T.IdCia=C.IdCia INNER JOIN Terceros AS N ON T.IdTercero=N.IdTercero LEFT JOIN Sys_Um AS UM ON T.UndMed=UM.UndMed LEFT JOIN Localidades AS L ON T.CodCiudad=L.IdLocal WHERE TipoTarif=@pmTipoTarif GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryOilDespClieFmt] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2) AS SELECT G.TipDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecCargue,G.FecDespacho,G.FecDescargue,G.HorasVig,G.IdCliente,T.RazonSocial AS NomCliente,G.IdAgencia,A.Agencia ,G.IdVehiculo,G.IdConductor,CD.RazonSocial AS NomConductor,G.NitEmpTrans,G.NomEmpTrans,G.Remolque,G.IdEstacion AS IdCampo,CM.EstServicio AS Campo ,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CdDptoOrig,DT.Departamento AS DptoOrigen ,G.IdLocDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CdDptoDest,DD.Departamento AS DptoDestino ,G.LugarOrigen,G.DirDestino,G.NomContacto,G.TelContacto,G.emlContacto,G.Modalidad,G.Referencia,G.NumRemision,G.DetSellos,G.CodSicom,G.ZonaFrontera ,G.Cantidad AS CantTotal,G.SubTotal,G.VrIva AS TotalIVA,G.VrTransporte,G.TipOrden,G.NumOrden,G.IdCiaOrden,G.EstFactura,G.TipFac,G.Factura,G.IdCiaFac,G.FechaFact ,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado,G.FechaCrea,G.IdCiaCrea,G.OrigenAdd,G.IdUsuario,Usuario,TD.TipoDoc --detalles ,D.Item,D.IdTanque,D.TipoProd,D.IdProducto,D.Descripcion,D.CantBruto,D.Cantidad,D.UndMed,UM.Unidad,D.Temperatura,D.TempLab,D.UnidTemp,D.ValorAPI,D.API_Corregido,D.Densidad,D.ValorVCW ,D.FactorTemp,D.VrUnitario,D.FleteUnd,D.TarifaIva,D.VrIva,D.CodTarIva,D.CodSicomPro,P.DescripProd,TQ.CapTanq,TQ.NivAgua,TQ.CapNeta,TQ.Descripcion AS TanqDescrip,TQ.Altura ,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercCiudad,L.IdDep AS TercIdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.TelMovil AS TercCelular,T.e_mail AS TercEmail FROM Trn_OilDespClie AS G INNER JOIN Trn_OilDespClieProd AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc INNER JOIN EstadoDoc AS ED ON G.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero INNER JOIN Terceros AS CD ON G.IdConductor=CD.IdTercero INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DT ON LO.IdDep=DT.IdDep INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed INNER JOIN Tanques AS TQ ON D.IdTanque=TQ.IdTanque LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN EdsServicio AS CM ON G.IdEstacion=CM.IdEstacion WHERE G.TipDoc=@pmTipDoc AND G.NumGuia BETWEEN @pmNumGuiaIni AND @pmNumGuiaFin AND G.IdCia=@pmIdCia GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsOilFacturaProd] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdTanque VARCHAR(4),@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(500),@pmCantidad DECIMAL(16,6),@pmUndMed VARCHAR(10),@pmVrUnitario DECIMAL(16,6),@pmTarifaDct DECIMAL(16,6),@pmVrDcto DECIMAL(16,6),@pmTarifaIva DECIMAL(16,6),@pmVrIva DECIMAL(16,6) ,@pmTarifaRet DECIMAL(16,6),@pmVrRetFte DECIMAL(16,6),@pmTarifaIca DECIMAL(16,6),@pmVrRetIca DECIMAL(16,6),@pmFleteUnd DECIMAL(16,6),@pmCostoUnit DECIMAL(16,6),@pmTemperatura DECIMAL(16,6),@pmTempLab DECIMAL(16,6),@pmUnidTemp CHAR(1),@pmValorAPI DECIMAL(16,6),@pmAPI_Corregido DECIMAL(16,6),@pmDensidad DECIMAL(16,6),@pmTipoProd VARCHAR(10) ,@pmTipGuia VARCHAR(3),@pmNumGuia INT,@pmIdCiaGuia CHAR(2),@pmCantGuia DECIMAL(16,6),@pmIndMezcla INT,@pmItemMezcla INT,@pmFactorMezcla DECIMAL(16,6),@pmNumTasas INT,@pmTasaBrent DECIMAL(16,6),@pmTasaWti DECIMAL(16,6),@pmTRMdolar DECIMAL(16,6),@pmPuntos DECIMAL(16,6),@pmCodTarIva VARCHAR(4),@pmCodTarDct VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCdMoneda VARCHAR(5),@pmCodSicomPro VARCHAR(20),@pmCodBodega VARCHAR(4) AS INSERT INTO Trn_OilFacturaProd (TipDoc,Factura,IdCia,Item,IdTanque,IdProducto,Descripcion,Cantidad,UndMed,VrUnitario,TarifaDct,VrDcto,TarifaIva,VrIva,TarifaRet,VrRetFte,TarifaIca,VrRetIca,FleteUnd,CostoUnit,Temperatura,TempLab,UnidTemp,ValorAPI,API_Corregido,Densidad,TipoProd,TipGuia,NumGuia,IdCiaGuia,CantGuia,IndMezcla,ItemMezcla,FactorMezcla,NumTasas,TasaBrent,TasaWti,TRMdolar,Puntos,CodTarIva,CodTarDct,CodTarRet,CodTarIca,CdMoneda,CodSicomPro,CodBodega) VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmIdTanque,@pmIdProducto,@pmDescripcion,@pmCantidad,@pmUndMed,@pmVrUnitario,@pmTarifaDct,@pmVrDcto,@pmTarifaIva,@pmVrIva,@pmTarifaRet,@pmVrRetFte,@pmTarifaIca,@pmVrRetIca,@pmFleteUnd,@pmCostoUnit,@pmTemperatura,@pmTempLab,@pmUnidTemp,@pmValorAPI,@pmAPI_Corregido,@pmDensidad,@pmTipoProd,@pmTipGuia,@pmNumGuia,@pmIdCiaGuia,@pmCantGuia,@pmIndMezcla,@pmItemMezcla,@pmFactorMezcla ,@pmNumTasas,@pmTasaBrent,@pmTasaWti,@pmTRMdolar,@pmPuntos,@pmCodTarIva,@pmCodTarDct,@pmCodTarRet,@pmCodTarIca,@pmCdMoneda,@pmCodSicomPro,@pmCodBodega) GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsOilDespClieProd] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdTanque VARCHAR(4),@pmTipoProd VARCHAR(10),@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(1000),@pmCantidad DECIMAL(16,6),@pmUndMed VARCHAR(10),@pmTemperatura DECIMAL(16,6),@pmTempLab DECIMAL(16,6),@pmUnidTemp CHAR(1),@pmValorAPI DECIMAL(16,6),@pmAPI_Corregido DECIMAL(16,6) ,@pmDensidad DECIMAL(16,6),@pmValorVCW DECIMAL(16,6),@pmFactorTemp DECIMAL(16,6),@pmVrUnitario DECIMAL(16,6),@pmFleteUnd DECIMAL(16,6),@pmTarifaIva DECIMAL(16,6),@pmVrIva DECIMAL(16,6),@pmCodTarIva VARCHAR(4),@pmCodSicomPro VARCHAR(20),@pmIndMezcla INT,@pmItemMezcla INT,@pmFactorMezcla DECIMAL(16,6),@pmCantBruto DECIMAL(16,6) AS INSERT INTO Trn_OilDespClieProd (TipDoc,NumGuia,IdCia,Item,IdTanque,TipoProd,IdProducto,Descripcion,Cantidad,UndMed,Temperatura,TempLab,UnidTemp,ValorAPI,API_Corregido,Densidad,ValorVCW,FactorTemp,VrUnitario,FleteUnd,TarifaIva,VrIva,CodTarIva,CodSicomPro,IndMezcla,ItemMezcla,FactorMezcla,CantBruto) VALUES (@pmTipDoc,@pmNumGuia,@pmIdCia,@pmItem,@pmIdTanque,@pmTipoProd,@pmIdProducto,@pmDescripcion,@pmCantidad,@pmUndMed,@pmTemperatura,@pmTempLab,@pmUnidTemp,@pmValorAPI,@pmAPI_Corregido,@pmDensidad,@pmValorVCW,@pmFactorTemp,@pmVrUnitario,@pmFleteUnd,@pmTarifaIva,@pmVrIva,@pmCodTarIva,@pmCodSicomPro,@pmIndMezcla,@pmItemMezcla,@pmFactorMezcla,@pmCantBruto) GO