if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelOilTraslado]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelOilTraslado]
GO
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].[paInsOilTraslado]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilTraslado]
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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilTraslado]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilTraslado]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilTrasladoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilTrasladoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilTrasladoRubrosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilTrasladoRubrosFmt]
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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilTrasladoRubrosFmt] @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
AS 
SELECT R.TipDoc,R.NumNota,R.IdCia,R.Item,R.IdProducto,P.DescripProd,R.TipoRubro,R.CantBruto,R.CantNeto,R.Cantidad,R.VrUnitario,R.VrTotal
,R.UndMed,U.Unidad,R.CodCueCP,P.IdSubgrupo
FROM Trn_OilTrasladoRubros AS R INNER JOIN ProdMcias AS P ON R.IdProducto=P.IdProducto INNER JOIN Sys_Um AS U ON R.UndMed=U.UndMed
WHERE R.TipDoc=@pmTipDoc AND R.NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND R.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilTrasladoFmt] @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT N.TipDoc,TipoDoc,N.NumNota,N.IdCia,Compania,N.Fecha,N.FechaInicio,N.FechaFinal,N.IdTercero,T.RazonSocial,N.CantMP,N.CantProducido,N.CantConsumo,N.CantCrudoCom
,N.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
,N.IdConcepto,Concepto,N.CdCCosto,CCosto,N.CdSubCos,SubCosto,N.TipCom,N.Comprobante,N.IdCiaCom,N.Anulado,N.NumDev,N.FecDev,N.Observacion,N.IdEstado,ED.Estado,N.FechaCrea,N.IdCiaCrea,N.OrigenAdd,N.IdUsuario,Usuario
--Detalles
,D.Item,D.IdProducto,D.Descripcion,D.TipoProd,D.SaldoInicial,D.SaldoFinal,D.Entradas,D.Despachos,D.DespMezcla,D.Salidas,D.AjuSobrante,D.AjuFaltante,D.Cantidad,D.UndMed,UM.Unidad
,D.PorcRend,D.CostoAnt,D.VrUnitario,D.VrTotal,D.SaldoMPAnt,D.CantProdAnt,D.CostoMPAnt,D.CostoMPMes,D.CostoUndMP,D.CostoUndTra,D.CostoTotalTra,D.CostoTotalSB,D.CostoUndVco,D.CostoTotalVco
,D.CostoTotalMod,D.CostoTotalCif,P.DescripProd,P.IdSubgrupo,D.CodMezcla,D.CdoSubMezcla
FROM Trn_OilTraslado AS N INNER JOIN Trn_OilTrasladoProd AS D ON N.TipDoc=D.TipDoc AND N.NumNota=D.NumNota AND N.IdCia=D.IdCia
INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero
INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN Conceptos AS CT ON N.IdConcepto=CT.IdConcepto
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
LEFT JOIN CentroCosto AS CC ON N.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON N.CdSubCos=SC.IdSubCos
WHERE N.TipDoc=@pmTipDoc AND N.NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
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(16,6),@pmVU_MP DECIMAL(16,6),@pmCantBrutoMP DECIMAL(16,6),@pmCantNetoMP DECIMAL(16,6),@pmCantFactMP DECIMAL(16,6),@pmTotal_TRA DECIMAL(16,6),@pmVU_TRA DECIMAL(16,6),@pmCantNetoTRA DECIMAL(16,6),@pmCantFactTRA DECIMAL(16,6),@pmTotal_TVacio DECIMAL(16,6),@pmVU_TVacio DECIMAL(16,6),@pmCantNetoTv DECIMAL(16,6),@pmCantFactTv DECIMAL(16,6),@pmTotal_Standby DECIMAL(16,6),@pmVU_Standby DECIMAL(16,6),@pmCant_Standby DECIMAL(16,6),@pmTotal_MOD DECIMAL(16,6),@pmTotal_CIF DECIMAL(16,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(16,6),@pmVU_MP DECIMAL(16,6),@pmCantBrutoMP DECIMAL(16,6),@pmCantNetoMP DECIMAL(16,6),@pmCantFactMP DECIMAL(16,6),@pmTotal_TRA DECIMAL(16,6),@pmVU_TRA DECIMAL(16,6),@pmCantNetoTRA DECIMAL(16,6),@pmCantFactTRA DECIMAL(16,6),@pmTotal_TVacio DECIMAL(16,6),@pmVU_TVacio DECIMAL(16,6),@pmCantNetoTv DECIMAL(16,6),@pmCantFactTv DECIMAL(16,6),@pmTotal_Standby DECIMAL(16,6),@pmVU_Standby DECIMAL(16,6),@pmCant_Standby DECIMAL(16,6),@pmTotal_MOD DECIMAL(16,6),@pmTotal_CIF DECIMAL(16,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].[paQryOilTraslado] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT 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
 FROM Trn_OilTraslado
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelOilTraslado] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_OilTraslado
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
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)
 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)
 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)
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(16,6),@pmVrUnitario DECIMAL(16,6),@pmVrTotal DECIMAL(16,6),@pmSaldoMPAnt DECIMAL(16,6),@pmCantProdAnt DECIMAL(16,6),@pmCostoMPAnt DECIMAL(16,6),@pmCostoMPMes DECIMAL(16,6),@pmCostoUndMP DECIMAL(16,6),@pmCostoUndTra DECIMAL(16,6),@pmCostoTotalTra DECIMAL(16,6),@pmCostoTotalSB DECIMAL(16,6),@pmCostoUndVco DECIMAL(16,6),@pmCostoTotalVco DECIMAL(16,6),@pmCostoTotalMod DECIMAL(16,6),@pmCostoTotalCif DECIMAL(16,6),@pmCodMezcla VARCHAR(16),@pmCdoSubMezcla VARCHAR(8)
 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)
 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)
GO