if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilMedicionesFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilMedicionesFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilCompraFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilCompraFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilDevCompraFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilDevCompraFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilCostosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilCostosFmt]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilCompraFmt] @pmTipDoc VARCHAR(3),@pmNumCompraIni INT,@pmNumCompraFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT C.TipDoc,TipoDoc,C.NumCompra,C.IdCia,Compania,C.Fecha,C.IdProveedor,T.RazonSocial,C.NumFactura,C.FechaFac,C.FechaVence,C.Modalidad,C.SubTotal,C.Descuento,C.Impuesto,C.Retencion,C.ReteIca
,C.ReteIva,C.IvaIngProd,C.OtrosCargos,C.OtrosDctos,C.Fletes,C.VrNeto,C.Cantidad AS CantTotal,C.BaseImp,C.BaseRet,C.BaseIvaIgp,C.TarifaIva,C.TarifaRet,C.TarifaIca,C.TarifaRiv
,C.MulPlazos,C.IdPlazo,Plazo,NVmto,DiasPago,C.CxPagar,C.IdConcepto,Concepto,C.CdCCosto,CC.CCosto AS CentCosto,C.CdSubCos,SC.SubCosto AS SubcCosto,C.FechaInicio,C.FechaFinal,C.EstRegCosto,C.NumRegCosto,C.CiaRegCosto,C.TipCom,C.Comprobante,C.IdCiaCom
,C.CodTarIva,C.CodTarRet,C.CodTarIca,C.CodTarRiv,C.Anulado,C.FecDev,C.NumDev,C.Observacion,C.IdEstado,ED.Estado,C.FechaCrea,C.IdCiaCrea,C.OrigenAdd,C.IdUsuario,Usuario
,D.Item,D.IdProducto,P.DescripProd,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.CostoUnit,D.VrTotal,D.VrDcto,D.VrIva,D.TarifaIva AS TarifIva,D.TarifaDct,D.CodTarIva AS CodTarifIva,D.CodTarDct,D.Descripcion,D.CdMoneda,Mneda
,D.CantBruto,D.CantNeto,D.CantEntrada,D.UMCargue,D.UMEntrada
,P.IdSubgrupo,P.TipoRef,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea
,T.TipoId,T.Dv,T.Codigo AS CodTercero,T.NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,T.Telefono,Fax,T.e_mail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte
 FROM Trn_OilCompra AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Conceptos AS CN ON C.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 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_OilCompraProd AS D ON C.TipDoc=D.TipDoc AND C.NumCompra=D.NumCompra AND C.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 S ON P.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea LEFT JOIN adm_monedas AS MN ON D.CdMoneda=MN.IdMneda
 LEFT JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo LEFT JOIN TercProvee AS TP ON C.IdProveedor=TP.IdProv
 LEFT JOIN CentroCosto AS CC ON C.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON C.CdSubCos=SC.IdSubCos
 WHERE C.TipDoc=@pmTipDoc AND C.NumCompra BETWEEN @pmNumCompraIni AND @pmNumCompraFin AND C.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilDevCompraFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
SELECT DV.TipDev,TipoDoc,DV.Devolucion,DV.IdCia,Compania,DV.Fecha,DV.IdConcepto,Concepto,DV.TipDoc,DV.NumCompra,DV.IdCiaDoc,DV.FecDoc,DV.IdProveedor,T.RazonSocial
,DV.NumFactura,C.FechaFac,C.FechaVence,DV.CxPagar,C.IdPlazo,Plazo,NVmto,DiasPago,DV.SubTotal,DV.Descuento,DV.Impuesto,DV.Retencion,DV.ReteIca,DV.ReteIva,DV.IvaIngProd,DV.OtrosCargos,DV.OtrosDctos,DV.Fletes,DV.VrNeto
,DV.Cantidad AS CantTotal,DV.BaseImp,DV.BaseRet,DV.BaseIvaIgp,DV.TarifaIva,DV.TarifaRet,DV.TarifaIca,DV.TarifaRiv,DV.CodTarIva,DV.CodTarRet,DV.CodTarIca,DV.CodTarRiv
,DV.CdCCosto,CC.CCosto,DV.CdSubCos,SC.SubCosto,DV.Modalidad,C.FechaInicio,C.FechaFinal,DV.TipCom,DV.Comprobante,DV.IdCiaCom,DV.ModdDev,DV.Observacion,DV.FechaCrea,DV.IdCiaCrea,DV.OrigenAdd,DV.IdUsuario,Usuario
,D.Item,D.IdProducto,P.DescripProd,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.CostoUnit,D.VrTotal,D.VrDcto,D.VrIva,D.TarifaIva AS TarifIva,D.TarifaDct,D.CodTarIva AS CodTarifIva,D.CodTarDct,D.Descripcion,D.CdMoneda,Mneda,D.CantBruto,D.CantNeto,D.CantEntrada,D.UMCargue,D.UMEntrada
,P.IdSubgrupo,P.TipoRef,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea
,T.TipoId,T.Dv,T.Codigo AS CodTercero,T.NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,T.Telefono,Fax,T.e_mail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte
FROM Trn_OilDevCompra AS DV INNER JOIN Trn_OilCompra AS C ON DV.TipDoc=C.TipDoc AND DV.NumCompra=C.NumCompra AND DV.IdCiaDoc=C.IdCia
INNER JOIN Trn_OilDevComProd AS D ON DV.TipDev=D.TipDev AND DV.Devolucion=D.Devolucion AND DV.IdCia=D.IdCia
INNER JOIN Companias AS CI ON DV.IdCia=CI.IdCia INNER JOIN Conceptos AS CN ON DV.IdConcepto=CN.IdConcepto
INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON DV.TipDev=TD.IdDoc 
INNER JOIN Terceros AS T ON DV.IdProveedor=T.IdTercero 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
LEFT JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo LEFT JOIN TercProvee AS TP ON DV.IdProveedor=TP.IdProv
LEFT JOIN CentroCosto AS CC ON DV.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON DV.CdSubCos=SC.IdSubCos
LEFT JOIN adm_monedas AS MN ON D.CdMoneda=MN.IdMneda
WHERE DV.TipDev=@pmTipDev AND DV.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND DV.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilCostosFmt] @pmTipProc VARCHAR(3),@pmNumProcesoIni INT,@pmNumProcesoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT PC.TipProc,TipoDoc,PC.NumProceso,PC.IdCia,CI.Compania,PC.Fecha,PC.FechaInicio,PC.FechaFinal,PC.IdConcepto,Concepto,PC.NitProveedor AS NitTercero,N.RazonSocial,PC.CodCiaFac,CC.Compania AS CiaCompras
,PC.Cantidad AS CantTotal,PC.SubTotal,PC.VrTransporte,PC.VrTraVacio,PC.TipCom,PC.Comprobante,PC.IdCiaCom,PC.Anulado,PC.FecDev,PC.NumDev,PC.Observacion,PC.FechaCrea,PC.IdCiaCrea,PC.OrigenAdd,PC.IdUsuario,Usuario
--detalles
,D.Item,D.TipCom AS DetTipCom,D.NumCompra,D.IdCiaCom AS CiaCom,D.ItemCom,D.IdProducto,P.DescripProd,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.VrTotal,D.CostoUnit,D.CantNeto,D.UMCargue,UMC.Unidad AS UM_Cargue
,C.Fecha AS FecCompra,C.IdProveedor,T.RazonSocial AS NomProveedor,C.NumFactura,C.FechaFac,C.FechaVence,C.Modalidad,C.FechaInicio AS FecIniPdo,C.FechaFinal AS FecFinPdo,C.Observacion AS ObservCompra
FROM Trn_OilCostos AS PC INNER JOIN Trn_OilCostosProd AS D ON PC.TipProc=D.TipProc AND PC.NumProceso=D.NumProceso AND PC.IdCia=D.IdCia
INNER JOIN Companias AS CI ON PC.IdCia=CI.IdCia INNER JOIN Conceptos AS CN ON PC.IdConcepto=CN.IdConcepto
INNER JOIN adm_Usuarios AS U ON PC.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON PC.TipProc=TD.IdDoc
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
INNER JOIN Trn_OilCompra AS C ON D.TipCom=C.TipDoc AND D.NumCompra=C.NumCompra AND D.IdCiaCom=C.IdCia
INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero
LEFT JOIN Terceros AS N ON PC.NitProveedor=N.IdTercero LEFT JOIN Companias AS CC ON PC.CodCiaFac=CC.IdCia
LEFT JOIN Sys_Um AS UMC ON D.UMCargue=UMC.UndMed
WHERE PC.TipProc=@pmTipProc AND PC.NumProceso BETWEEN @pmNumProcesoIni AND @pmNumProcesoFin AND PC.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilMedicionesFmt] @pmTipDoc VARCHAR(3),@pmNumInventIni INT,@pmNumInventFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc,TipoDoc,M.NumInvent,M.IdCia,Compania,M.Fecha,M.FecProceso,M.IdTurno,Turno,M.IdOperario,M.NomOperario,M.Modalidad,M.TipDocAju,M.AjuInvSob,M.AjuInvFal,M.IdCiaAin
 ,M.Anulado,M.NumDev,M.FecDev,M.Observacion,M.IdEstado,ED.Estado,M.FechaCrea,M.IdCiaCrea,M.OrigenAdd,M.IdUsuario,Usuario
--Detalles
,D.Item,D.IdTanque,D.IdProducto,P.DescripProd,D.NivelVol,D.Cantidad,D.UndMed,UM.Unidad,D.TipoMed,D.AlturaTq,D.UndAforoC,D.UndAforoF,D.Temperatura,D.UnidTemp
,D.ValorAPI,D.API_Corregido,D.Densidad,D.CantSaldo,D.CantAjuste,D.VrUnitario,TQ.CapTanq,TQ.NivAgua,TQ.CapNeta,TQ.TipoProd,TQ.Descripcion AS TanqDescrip
FROM Trn_OilMediciones AS M INNER JOIN Trn_OilMedicionesTq AS D ON M.TipDoc=D.TipDoc AND M.NumInvent=D.NumInvent AND M.IdCia=D.IdCia
INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Turnos AS TN ON M.IdTurno=TN.IdTurno
INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario 
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
WHERE M.TipDoc=@pmTipDoc AND M.NumInvent BETWEEN @pmNumInventIni AND @pmNumInventFin AND M.IdCia=@pmIdCia
GO