if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilDevCostos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilDevCostos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilCostosFmv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilCostosFmv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilDevCostos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilDevCostos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilDevCostosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilDevCostosFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilDevCostosFmv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilDevCostosFmv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOilDevCostos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOilDevCostos]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilDevCostosFmv] @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.NumProceso,DV.IdCiaDoc,DV.FecDoc,DV.Cantidad AS CantTotal,DV.SubTotal
,DV.TipCom,TipoCom,DV.Comprobante,DV.IdCiaCom,DV.ModdDev,DV.Observacion,DV.FechaCrea,DV.IdCiaCrea,DV.OrigenAdd,DV.IdUsuario,Usuario
--detalles
,D.Item,D.IdProducto,P.DescripProd,P.TipoProdCo,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.VrTotal,D.CostoUnit
,DC.TipCom AS TipoFact,DC.NumCompra AS NumFactura,DC.IdCiaCom AS CiaFact,DC.ItemCom AS ItemFact
,F.Fecha AS FecFact,F.IdCliente,T.RazonSocial AS NomCliente,F.FechaVence,F.Observacion AS ObservFactura
FROM Trn_OilDevCostos AS DV INNER JOIN Trn_OilDevCostosProd AS D ON DV.TipDev=D.TipDev AND DV.Devolucion=D.Devolucion AND DV.IdCia=D.IdCia
INNER JOIN Trn_OilCostosProd AS DC ON DV.TipDoc=DC.TipProc AND DV.NumProceso=DC.NumProceso AND DV.IdCiaDoc=DC.IdCia AND D.Item=DC.Item
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 ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
LEFT JOIN Trn_Facturas AS F ON DC.TipCom=F.TipDoc AND DC.NumCompra=F.Factura AND DC.IdCiaCom=F.IdCia
LEFT JOIN Terceros AS T ON F.IdCliente=T.IdTercero
LEFT JOIN TiposCom AS TC ON DV.TipCom=TC.IdCom
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].[paQryOilCostosFmv] @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 TipFact,D.NumCompra AS NumFactura,D.IdCiaCom AS CiaFact,D.ItemCom AS ItemFact,D.IdProducto,P.DescripProd,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.VrTotal
,F.Fecha AS FecFactura,F.IdCliente,T.RazonSocial AS NomCliente,F.FechaVence,F.Observacion AS ObservFactura
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
LEFT JOIN Trn_Facturas AS F ON D.TipCom=F.TipDoc AND D.NumCompra=F.Factura AND D.IdCiaCom=F.IdCia
LEFT JOIN Terceros AS T ON F.IdCliente=T.IdTercero
LEFT JOIN Terceros AS N ON PC.NitProveedor=N.IdTercero LEFT JOIN Companias AS CC ON PC.CodCiaFac=CC.IdCia
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].[paQryOilDevCostosFmt] @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.NumProceso,DV.IdCiaDoc,DV.FecDoc,DV.Cantidad AS CantTotal,DV.SubTotal,DV.VrTransporte,DV.VrTraVacio
,DV.TipCom,TipoCom,DV.Comprobante,DV.IdCiaCom,DV.ModdDev,DV.Observacion,DV.FechaCrea,DV.IdCiaCrea,DV.OrigenAdd,DV.IdUsuario,Usuario
--detalles
,D.Item,D.IdProducto,P.DescripProd,P.TipoProdCo,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.VrTotal,D.CostoUnit
,DC.TipCom AS DetTipCom,DC.NumCompra,DC.IdCiaCom AS CiaCom,DC.ItemCom,DC.CantNeto,DC.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_OilDevCostos AS DV INNER JOIN Trn_OilDevCostosProd AS D ON DV.TipDev=D.TipDev AND DV.Devolucion=D.Devolucion AND DV.IdCia=D.IdCia
INNER JOIN Trn_OilCostosProd AS DC ON DV.TipDoc=DC.TipProc AND DV.NumProceso=DC.NumProceso AND DV.IdCiaDoc=DC.IdCia AND D.Item=DC.Item
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 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 DC.TipCom=C.TipDoc AND DC.NumCompra=C.NumCompra AND DC.IdCiaCom=C.IdCia
INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero LEFT JOIN Sys_Um AS UMC ON DC.UMCargue=UMC.UndMed
LEFT JOIN TiposCom AS TC ON DV.TipCom=TC.IdCom
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].[paQryOilDevCostos] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumProceso,IdCiaDoc,FecDoc,Cantidad,SubTotal,VrTransporte,VrTraVacio,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario
 FROM Trn_OilDevCostos
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilDevCostos] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmCantidad DECIMAL(16,6),@pmSubTotal DECIMAL(16,6),@pmVrTransporte DECIMAL(16,6),@pmVrTraVacio DECIMAL(16,6),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(2000),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmOrigenAdd VARCHAR(10),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OilDevCostos (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumProceso,IdCiaDoc,FecDoc,Cantidad,SubTotal,VrTransporte,VrTraVacio,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmNumProceso,@pmIdCiaDoc,@pmFecDoc,@pmCantidad,@pmSubTotal,@pmVrTransporte,@pmVrTraVacio,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmModdDev,@pmObservacion,@pmFechaCrea,@pmIdCiaCrea,@pmOrigenAdd,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOilDevCostos] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmCantidad DECIMAL(16,6),@pmSubTotal DECIMAL(16,6),@pmVrTransporte DECIMAL(16,6),@pmVrTraVacio DECIMAL(16,6),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(2000)
 AS 
 UPDATE Trn_OilDevCostos SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,NumProceso=@pmNumProceso,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,Cantidad=@pmCantidad,SubTotal=@pmSubTotal,VrTransporte=@pmVrTransporte,VrTraVacio=@pmVrTraVacio,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,ModdDev=@pmModdDev,Observacion=@pmObservacion
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO