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].[paInsOilDevFacProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilDevFacProd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilFacturasLtd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilFacturasLtd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilDevFcrLtd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilDevFcrLtd]
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].[paQryDevFcrFmtb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrFmtb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilTraslado]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilTraslado]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOilTraslado]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOilTraslado]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilTrasladoProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilTrasladoProd]
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilFacturasLtd] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS  
SELECT F.TipDoc,F.Factura,F.IdCia,Compania,FE.Prefijo,FE.NumFace,F.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,F.Fecha,F.IdPlazo,Plazo,F.FechaVence
,D.Item,D.TipGuia,D.NumGuia,D.IdCiaGuia,D.ItemGuia,D.IdTanque,D.IdProducto,D.Descripcion,P.DescripProd,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.TarifaDct,D.VrDcto,D.TarifaIva,D.VrIva,D.VrBaseRet,D.TarifaRet AS DetTarifRet,D.VrRetFte AS DetVrRetFte
,D.TarifaIca AS DetTarifIca,D.VrRetIca AS DetVrReteIca,D.Temperatura,D.TempLab,D.UnidTemp,D.ValorAPI,D.API_Corregido,D.Densidad,D.CantGuia,D.IndMezcla,D.ItemMezcla,D.FactorMezcla,D.NumTasas,D.TasaBrent,D.TasaWti,D.TRMdolar,D.Puntos
,D.CodSicomPro,D.TipoProd,D.CodBodega,Bodega
,P.Referencia AS ProdReferencia,P.CodBarras AS Cod_Sicom,P.UndMed AS ProdUnd,UMP.Unidad AS ProdUnidad,P.IdSubgrupo,Subgrupo
,F.TarifaRet,F.VrRetencion,F.TarifaIca,F.VrReteICA,F.TarifaRiv,F.VrReteIVA,F.TarifaRtc,F.VrReteCREE,F.VrFletes,F.VrCargos,F.VrOtrDcto,F.VrNeto,F.VrAplicado,F.BaseImp,F.BaseRet,F.BaseIca,F.BaseRiv
,F.TarifaArf,F.VrAutRetFte,F.TarifaAri,F.VrAutRetIca,F.IdAgencia,A.Agencia AS NomAgencia,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,F.TarifaCom,F.IdLocEnv AS IdCiudad,LE.Localidad AS NomCiudad,LE.IdDep,DE.Departamento
,F.IdForma,FormaPago,F.DetallePago,F.MulPlazos,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,F.CargoContac,F.pVehiculo,F.CdConductor,CD.RazonSocial AS Conductor,F.ZonaFrontera,F.TipRem,F.Remision,F.IdCiaRem
,F.IdConcepto AS CdConcepto,Concepto,F.Modalidad,F.IdCCosto,CCosto,F.IdSubCos,SubCosto,F.TipCom,TipoCom,F.Comprobante,F.IdCiaCom,F.Anulado,F.NumDev,F.FecDev,F.Observacion,F.IdEstado,ED.Estado,F.TimeSys AS FechaCrea,F.IdUsuario AS CdUsuario,Usuario,F.OrigenAdd
,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
,T.TipEnte,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen
FROM Trn_Facturas AS F INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 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 Terceros AS T ON F.IdCliente=T.IdTercero 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 Trn_OilFacturaProd AS D ON F.TipDoc=D.TipDoc AND F.Factura=D.Factura AND F.IdCia=D.IdCia
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
INNER JOIN SubGrupos AS SG ON P.IdSubgrupo=SG.IdSubgrupo INNER JOIN Sys_Um AS UMP ON P.UndMed=UMP.UndMed
LEFT JOIN CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos LEFT JOIN Terceros AS CD ON F.CdConductor=CD.IdTercero
LEFT JOIN Localidades AS LE ON F.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
LEFT JOIN Trn_Face AS FE ON F.TipDoc=FE.TipDoc AND F.Factura=FE.Documento AND F.IdCia=FE.IdCia
LEFT JOIN TiposCom AS TC ON F.TipCom=TC.IdCom LEFT JOIN Bodegas AS B ON D.CodBodega=B.IdBodega
WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL)
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.VrBaseRet,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.ItemGuia,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].[paQryOilDevFcrLtd] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS  
SELECT DV.TipDev,DV.Devolucion,DV.IdCia,Compania,DV.Fecha,DV.TipDoc,DV.Factura,DV.IdCiaDoc,DV.FecDoc,DV.IdCliente,T.RazonSocial AS NomCliente
,D.Item,D.IdTanque,D.IdProducto,P.DescripProd,D.TipoProd,D.Descripcion,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.TarifaDct,D.VrDcto,D.TarifaIva,D.VrIva
,D.TarifaRet AS DetTarifRet,D.VrRetFte AS DetVrRetFte,D.VrBaseRet,D.TarifaIca AS DetTarifIca,D.VrRetIca AS DetReteIca
,D.ItemDet,D.TipGuia,D.NumGuia,D.IdCiaGuia,D.ItemGuia,D.CantGuia,D.IndMezcla,D.ItemMezcla,D.FactorMezcla,D.CodSicomPro,D.CodBodega,Bodega
,DV.VrRetencion,DV.VrReteICA,DV.VrReteIVA,DV.VrFletes,DV.VrCargos,DV.VrOtrDcto,DV.VrReteCREE,DV.VrNeto,DV.BaseImp,DV.BaseRet
,DV.Modalidad,DV.IdConcepto,Concepto,DV.IdAgencia,A.Agencia AS NomAgencia,DV.IdVend AS NitVend,VN.RazonSocial AS Vendedor,DV.TarifaCom,DV.TarifaArf,DV.VrAutRetFte,DV.TarifaAri,DV.VrAutRetIca
,DV.Remision,DV.IdCiaRem,DV.ModdDev,DV.TipCom,TipoCom,DV.Comprobante,DV.IdCiaCom,DV.Observacion AS Observ,DV.TimeSys AS FechaCrea,DV.IdCiaCrea,DV.IdUsuario,Usuario,DV.OrigenAdd
,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
,T.TipEnte,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen
FROM Trn_DevFcr AS DV INNER JOIN Companias AS CN ON DV.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON DV.IdConcepto=C.IdConcepto
INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario INNER JOIN Terceros AS VN ON DV.IdVend=VN.IdTercero
INNER JOIN Terceros AS T ON DV.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON DV.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 Trn_OilDevFacProd AS D ON DV.TipDev=D.TipDev AND DV.Devolucion=D.Devolucion AND DV.IdCia=D.IdCia
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
INNER JOIN SubGrupos AS SG ON P.IdSubgrupo=SG.IdSubgrupo INNER JOIN Sys_Um AS UMP ON P.UndMed=UMP.UndMed
LEFT JOIN Agencias AS A ON DV.IdAgencia=A.IdAgencia LEFT JOIN TiposCom AS TC ON DV.TipCom=TC.IdCom
LEFT JOIN Bodegas AS B ON D.CodBodega=B.IdBodega
WHERE DV.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (DV.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilDevFacProd] @pmTipDev VARCHAR(3),@pmDevolucion 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),@pmItemDet INT,@pmTipoProd VARCHAR(10),@pmTipGuia VARCHAR(3),@pmNumGuia INT,@pmIdCiaGuia CHAR(2),@pmCantGuia DECIMAL(16,6),@pmIndMezcla INT,@pmItemMezcla INT,@pmFactorMezcla DECIMAL(16,6),@pmCodTarIva VARCHAR(4),@pmCodTarDct VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodSicomPro VARCHAR(20),@pmCodBodega VARCHAR(4),@pmItemGuia INT,@pmVrBaseRet DECIMAL(16,6)
 AS 
 INSERT INTO Trn_OilDevFacProd (TipDev,Devolucion,IdCia,Item,IdTanque,IdProducto,Descripcion,Cantidad,UndMed,VrUnitario,TarifaDct,VrDcto,TarifaIva,VrIva,TarifaRet,VrRetFte,TarifaIca,VrRetIca,FleteUnd,CostoUnit,ItemDet,TipoProd,TipGuia,NumGuia,IdCiaGuia,CantGuia,IndMezcla,ItemMezcla,FactorMezcla,CodTarIva,CodTarDct,CodTarRet,CodTarIca,CodSicomPro,CodBodega,ItemGuia,VrBaseRet)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmItem,@pmIdTanque,@pmIdProducto,@pmDescripcion,@pmCantidad,@pmUndMed,@pmVrUnitario,@pmTarifaDct,@pmVrDcto,@pmTarifaIva,@pmVrIva,@pmTarifaRet,@pmVrRetFte,@pmTarifaIca,@pmVrRetIca,@pmFleteUnd,@pmCostoUnit,@pmItemDet,@pmTipoProd,@pmTipGuia,@pmNumGuia,@pmIdCiaGuia,@pmCantGuia,@pmIndMezcla,@pmItemMezcla,@pmFactorMezcla,@pmCodTarIva,@pmCodTarDct,@pmCodTarRet,@pmCodTarIca,@pmCodSicomPro,@pmCodBodega,@pmItemGuia,@pmVrBaseRet)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrFmtb] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDev,TipoDoc,D.Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipFact,D.Factura AS NumFactura
,D.IdCiaDoc,D.FecDoc,D.IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,D.VrSubTotal,D.VrDescuento,D.VrImpuesto,D.VrRetencion
,D.VrReteICA,D.VrReteIVA,D.VrFletes,D.VrOtros,D.VrCargos,D.VrOtrDcto,D.VrCostos,D.VrImpCarbono,D.VrIvaIngProd,D.VrNeto,D.Cantidad AS CantTotal,D.BaseImp,D.BaseRet,D.BaseIvaIgp
,D.VrReteCREE,D.TarifaRtc,D.CodTarRtc,D.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,D.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,D.TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,Pedido,IdCiaPed,D.Remision,D.IdCiaRem
,D.FecPedido,D.Modalidad,D.ModdDev,D.PlacaVehic,D.ZonaFrontera,D.TarifaArf,D.VrAutRetFte,D.TarifaAri,D.VrAutRetIca,D.CdMotDev,MotivoDev
,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fec_Add,D.IdCiaCrea,D.IdUsuario,Usuario,D.OrigenAdd
--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,DT.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,ExcIva,Autoret,VrCupo,VrSaldo
--Detalles
,DP.Item,DP.IdTanque,DP.IdProducto,PM.DescripProd,DP.Descripcion,DP.Cantidad,DP.UndMed,UM.Unidad,DP.VrUnitario,DP.TarifaDct,DP.VrDcto,DP.TarifaIva,DP.VrIva,DP.TarifaRet,DP.VrRetFte,DP.VrBaseRet,DP.TarifaIca,DP.VrRetIca,DP.FleteUnd,DP.CostoUnit
,DP.ItemDet,DP.TipoProd,DP.TipGuia,DP.NumGuia,DP.IdCiaGuia,DP.ItemGuia,DP.CantGuia,DP.IndMezcla,DP.ItemMezcla,DP.FactorMezcla,DP.CodTarIva,DP.CodTarDct,DP.CodTarRet,DP.CodTarIca,DP.CodSicomPro,DP.CodBodega,Bodega
,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia,FE.CUFE_QR AS QR,FE.FechaValidacion
 FROM Trn_DevFcr AS D INNER JOIN Trn_OilDevFacProd AS DP ON D.TipDev=DP.TipDev AND D.Devolucion=DP.Devolucion AND D.IdCia=DP.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON D.IdAgencia=A.IdAgencia
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DT ON L.IdDep=DT.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 DP.IdProducto=PM.IdProducto INNER JOIN Sys_Um AS UM ON DP.UndMed=UM.UndMed
 LEFT JOIN Localidades AS LE ON D.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom LEFT JOIN TiposMotdev AS MD ON D.CdMotDev=MD.IdMotDev
 LEFT JOIN Tanques AS TQ ON DP.IdTanque=TQ.IdTanque  LEFT JOIN Bodegas AS B ON DP.CodBodega=B.IdBodega
 LEFT JOIN Trn_Face AS FE ON D.TipDev=FE.TipDoc AND D.Devolucion=FE.Documento AND D.IdCia=FE.IdCia
 WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
 AND DP.IndMezcla<>2
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)
,@pmItemGuia INT,@pmVrBaseRet DECIMAL(16,6)
 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,ItemGuia,VrBaseRet)
 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,@pmItemGuia,@pmVrBaseRet)
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilTraslado] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaInicio SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCantMP DECIMAL(16,6),@pmCantProducido DECIMAL(16,6),@pmCantConsumo DECIMAL(16,6),@pmCantCrudoCom DECIMAL(16,6),@pmTotal_MP DECIMAL(20,6),@pmVU_MP DECIMAL(20,6),@pmCantBrutoMP DECIMAL(16,6),@pmCantNetoMP DECIMAL(16,6),@pmCantFactMP DECIMAL(16,6),@pmTotal_TRA DECIMAL(20,6),@pmVU_TRA DECIMAL(20,6)
,@pmCantNetoTRA DECIMAL(16,6),@pmCantFactTRA DECIMAL(16,6),@pmTotal_TVacio DECIMAL(20,6),@pmVU_TVacio DECIMAL(20,6),@pmCantNetoTv DECIMAL(16,6),@pmCantFactTv DECIMAL(16,6),@pmTotal_Standby DECIMAL(20,6),@pmVU_Standby DECIMAL(20,6),@pmCant_Standby DECIMAL(16,6),@pmTotal_MOD DECIMAL(20,6),@pmTotal_CIF DECIMAL(20,6),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmOrigenAdd VARCHAR(10),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OilTraslado (TipDoc,NumNota,IdCia,Fecha,FechaInicio,FechaFinal,IdConcepto,IdTercero,CantMP,CantProducido,CantConsumo,CantCrudoCom,Total_MP,VU_MP,CantBrutoMP,CantNetoMP,CantFactMP,Total_TRA,VU_TRA,CantNetoTRA,CantFactTRA,Total_TVacio,VU_TVacio,CantNetoTv,CantFactTv,Total_Standby,VU_Standby,Cant_Standby,Total_MOD,Total_CIF,CdCCosto,CdSubCos,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmFechaInicio,@pmFechaFinal,@pmIdConcepto,@pmIdTercero,@pmCantMP,@pmCantProducido,@pmCantConsumo,@pmCantCrudoCom,@pmTotal_MP,@pmVU_MP,@pmCantBrutoMP,@pmCantNetoMP,@pmCantFactMP,@pmTotal_TRA,@pmVU_TRA,@pmCantNetoTRA,@pmCantFactTRA,@pmTotal_TVacio,@pmVU_TVacio,@pmCantNetoTv,@pmCantFactTv,@pmTotal_Standby,@pmVU_Standby,@pmCant_Standby,@pmTotal_MOD,@pmTotal_CIF,@pmCdCCosto,@pmCdSubCos,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmFechaCrea,@pmIdCiaCrea,@pmOrigenAdd,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOilTraslado] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaInicio SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCantMP DECIMAL(16,6),@pmCantProducido DECIMAL(16,6),@pmCantConsumo DECIMAL(16,6),@pmCantCrudoCom DECIMAL(16,6),@pmTotal_MP DECIMAL(20,6),@pmVU_MP DECIMAL(20,6),@pmCantBrutoMP DECIMAL(16,6),@pmCantNetoMP DECIMAL(16,6),@pmCantFactMP DECIMAL(16,6),@pmTotal_TRA DECIMAL(20,6),@pmVU_TRA DECIMAL(20,6)
,@pmCantNetoTRA DECIMAL(16,6),@pmCantFactTRA DECIMAL(16,6),@pmTotal_TVacio DECIMAL(20,6),@pmVU_TVacio DECIMAL(20,6),@pmCantNetoTv DECIMAL(16,6),@pmCantFactTv DECIMAL(16,6),@pmTotal_Standby DECIMAL(20,6),@pmVU_Standby DECIMAL(20,6),@pmCant_Standby DECIMAL(16,6),@pmTotal_MOD DECIMAL(20,6),@pmTotal_CIF DECIMAL(20,6),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4)
 AS 
 UPDATE Trn_OilTraslado SET Fecha=@pmFecha,FechaInicio=@pmFechaInicio,FechaFinal=@pmFechaFinal,IdConcepto=@pmIdConcepto,IdTercero=@pmIdTercero,CantMP=@pmCantMP,CantProducido=@pmCantProducido,CantConsumo=@pmCantConsumo,CantCrudoCom=@pmCantCrudoCom,Total_MP=@pmTotal_MP,VU_MP=@pmVU_MP,CantBrutoMP=@pmCantBrutoMP,CantNetoMP=@pmCantNetoMP,CantFactMP=@pmCantFactMP,Total_TRA=@pmTotal_TRA,VU_TRA=@pmVU_TRA,CantNetoTRA=@pmCantNetoTRA,CantFactTRA=@pmCantFactTRA,Total_TVacio=@pmTotal_TVacio,VU_TVacio=@pmVU_TVacio,CantNetoTv=@pmCantNetoTv,CantFactTv=@pmCantFactTv
,Total_Standby=@pmTotal_Standby,VU_Standby=@pmVU_Standby,Cant_Standby=@pmCant_Standby,Total_MOD=@pmTotal_MOD,Total_CIF=@pmTotal_CIF,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilTrasladoProd] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(500),@pmTipoProd VARCHAR(10),@pmSaldoInicial DECIMAL(16,6),@pmSaldoFinal DECIMAL(16,6),@pmEntradas DECIMAL(16,6),@pmDespachos DECIMAL(16,6),@pmDespMezcla DECIMAL(16,6),@pmSalidas DECIMAL(16,6),@pmAjuSobrante DECIMAL(16,6),@pmAjuFaltante DECIMAL(16,6),@pmCantidad DECIMAL(16,6),@pmUndMed VARCHAR(10),@pmPorcRend DECIMAL(16,6)
,@pmCostoAnt DECIMAL(20,6),@pmVrUnitario DECIMAL(20,6),@pmVrTotal DECIMAL(20,6),@pmSaldoMPAnt DECIMAL(16,6),@pmCantProdAnt DECIMAL(16,6),@pmCostoMPAnt DECIMAL(20,6),@pmCostoMPMes DECIMAL(20,6),@pmCostoUndMP DECIMAL(20,6),@pmCostoUndTra DECIMAL(20,6),@pmCostoTotalTra DECIMAL(20,6),@pmCostoTotalSB DECIMAL(20,6),@pmCostoUndVco DECIMAL(20,6),@pmCostoTotalVco DECIMAL(20,6),@pmCostoTotalMod DECIMAL(20,6),@pmCostoTotalCif DECIMAL(20,6),@pmCodMezcla VARCHAR(16),@pmCdoSubMezcla VARCHAR(8)
,@pmProducido DECIMAL(16,6),@pmCantVehTnsto DECIMAL(16,6),@pmCantBrutoDesp DECIMAL(16,6),@pmCantNetoDesp DECIMAL(16,6),@pmCantTraDesp DECIMAL(16,6)
 AS 
 INSERT INTO Trn_OilTrasladoProd (TipDoc,NumNota,IdCia,Item,IdProducto,Descripcion,TipoProd,SaldoInicial,SaldoFinal,Entradas,Despachos,DespMezcla,Salidas,AjuSobrante,AjuFaltante,Cantidad,UndMed,PorcRend,CostoAnt,VrUnitario,VrTotal,SaldoMPAnt,CantProdAnt,CostoMPAnt,CostoMPMes,CostoUndMP,CostoUndTra,CostoTotalTra,CostoTotalSB,CostoUndVco,CostoTotalVco,CostoTotalMod,CostoTotalCif,CodMezcla,CdoSubMezcla,Producido,CantVehTnsto,CantBrutoDesp,CantNetoDesp,CantTraDesp)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmItem,@pmIdProducto,@pmDescripcion,@pmTipoProd,@pmSaldoInicial,@pmSaldoFinal,@pmEntradas,@pmDespachos,@pmDespMezcla,@pmSalidas,@pmAjuSobrante,@pmAjuFaltante,@pmCantidad,@pmUndMed,@pmPorcRend,@pmCostoAnt,@pmVrUnitario,@pmVrTotal,@pmSaldoMPAnt,@pmCantProdAnt,@pmCostoMPAnt,@pmCostoMPMes,@pmCostoUndMP,@pmCostoUndTra,@pmCostoTotalTra,@pmCostoTotalSB,@pmCostoUndVco,@pmCostoTotalVco,@pmCostoTotalMod
,@pmCostoTotalCif,@pmCodMezcla,@pmCdoSubMezcla,@pmProducido,@pmCantVehTnsto,@pmCantBrutoDesp,@pmCantNetoDesp,@pmCantTraDesp)
GO