if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelOilEntrada]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelOilEntrada]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelOilGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelOilGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilEntrada]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilEntrada]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilEntradaProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilEntradaProd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOilGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOilGuia]
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].[paInsTiposTasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposTasas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilEntrada]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilEntrada]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposTasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposTasas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOilEntrada]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOilEntrada]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOilGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOilGuia]
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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposTasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposTasas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilGuiaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilGuiaFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOilEntradaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOilEntradaFmt]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAuxInvNivelMax]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAuxInvNivelMax]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAuxInvNivelMin]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAuxInvNivelMin]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAuxInvSaldoAct]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAuxInvSaldoAct]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AuxInv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AuxInv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AuxInvUbic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AuxInvUbic]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AuxInvVen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AuxInvVen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMudOrdenServ]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilGuia] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumGuia,IdCia,Fecha,FecDespacho,IdProveedor,IdVehiculo,IdConductor,IdEstacion,IdProducto,CantBruto,CantNeto,UndMed,Temperatura,UnidTemp,ValorAPI,ValorVCW,CantDescargue,APIDescargue,TempDescargue,FecDescargue,NitEmpTrans,NomEmpTrans,NumRemision,Remolque,TipoEnt,NumEntrada,IdCiaEnt,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario
 FROM Trn_OilGuia
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelOilGuia] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_OilGuia WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOilGuia] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdProveedor VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdEstacion VARCHAR(4),@pmIdProducto VARCHAR(16),@pmCantBruto DECIMAL(16,6),@pmCantNeto DECIMAL(16,6),@pmUndMed VARCHAR(10),@pmTemperatura DECIMAL(16,6),@pmUnidTemp CHAR(1),@pmValorAPI DECIMAL(16,6),@pmValorVCW DECIMAL(16,6),@pmCantDescargue DECIMAL(16,6),@pmAPIDescargue DECIMAL(16,6),@pmTempDescargue DECIMAL(16,6),@pmFecDescargue SMALLDATETIME
,@pmNitEmpTrans VARCHAR(16),@pmNomEmpTrans VARCHAR(150),@pmNumRemision VARCHAR(20),@pmRemolque VARCHAR(10),@pmTipoEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCiaEnt CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4)
 AS 
 UPDATE Trn_OilGuia SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,IdProveedor=@pmIdProveedor,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,IdEstacion=@pmIdEstacion,IdProducto=@pmIdProducto,CantBruto=@pmCantBruto,CantNeto=@pmCantNeto,UndMed=@pmUndMed,Temperatura=@pmTemperatura,UnidTemp=@pmUnidTemp,ValorAPI=@pmValorAPI,ValorVCW=@pmValorVCW,CantDescargue=@pmCantDescargue,APIDescargue=@pmAPIDescargue,TempDescargue=@pmTempDescargue,FecDescargue=@pmFecDescargue,NitEmpTrans=@pmNitEmpTrans,NomEmpTrans=@pmNomEmpTrans,NumRemision=@pmNumRemision,Remolque=@pmRemolque
,TipoEnt=@pmTipoEnt,NumEntrada=@pmNumEntrada,IdCiaEnt=@pmIdCiaEnt,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilGuia] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdProveedor VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdEstacion VARCHAR(4),@pmIdProducto VARCHAR(16),@pmCantBruto DECIMAL(16,6),@pmCantNeto DECIMAL(16,6),@pmUndMed VARCHAR(10),@pmTemperatura DECIMAL(16,6),@pmUnidTemp CHAR(1),@pmValorAPI DECIMAL(16,6),@pmValorVCW DECIMAL(16,6),@pmCantDescargue DECIMAL(16,6),@pmAPIDescargue DECIMAL(16,6),@pmTempDescargue DECIMAL(16,6),@pmFecDescargue SMALLDATETIME
,@pmNitEmpTrans VARCHAR(16),@pmNomEmpTrans VARCHAR(150),@pmNumRemision VARCHAR(20),@pmRemolque VARCHAR(10),@pmTipoEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCiaEnt CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmOrigenAdd VARCHAR(10),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OilGuia (TipDoc,NumGuia,IdCia,Fecha,FecDespacho,IdProveedor,IdVehiculo,IdConductor,IdEstacion,IdProducto,CantBruto,CantNeto,UndMed,Temperatura,UnidTemp,ValorAPI,ValorVCW,CantDescargue,APIDescargue,TempDescargue,FecDescargue,NitEmpTrans,NomEmpTrans,NumRemision,Remolque,TipoEnt,NumEntrada,IdCiaEnt,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario)
 VALUES (@pmTipDoc,@pmNumGuia,@pmIdCia,@pmFecha,@pmFecDespacho,@pmIdProveedor,@pmIdVehiculo,@pmIdConductor,@pmIdEstacion,@pmIdProducto,@pmCantBruto,@pmCantNeto,@pmUndMed,@pmTemperatura,@pmUnidTemp,@pmValorAPI,@pmValorVCW,@pmCantDescargue,@pmAPIDescargue,@pmTempDescargue,@pmFecDescargue,@pmNitEmpTrans,@pmNomEmpTrans,@pmNumRemision,@pmRemolque,@pmTipoEnt,@pmNumEntrada,@pmIdCiaEnt,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmFechaCrea,@pmIdCiaCrea,@pmOrigenAdd,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposTasas] @pmIdTasa VARCHAR(4)
 AS 
 SELECT IdTasa,TipoTasa,Simbolo,Unidad,Inactivo
 FROM TiposTasas
 WHERE IdTasa=@pmIdTasa
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposTasas] @pmIdTasa VARCHAR(4),@pmTipoTasa VARCHAR(50),@pmSimbolo VARCHAR(10),@pmUnidad VARCHAR(50),@pmInactivo BIT
 AS 
 INSERT INTO TiposTasas (IdTasa,TipoTasa,Simbolo,Unidad,Inactivo)
 VALUES (@pmIdTasa,@pmTipoTasa,@pmSimbolo,@pmUnidad,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposTasas] @pmIdTasa VARCHAR(4),@pmTipoTasa VARCHAR(50),@pmSimbolo VARCHAR(10),@pmUnidad VARCHAR(50),@pmInactivo BIT
 AS 
 UPDATE TiposTasas SET TipoTasa=@pmTipoTasa,Simbolo=@pmSimbolo,Unidad=@pmUnidad,Inactivo=@pmInactivo
 WHERE IdTasa=@pmIdTasa
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),@pmInactivo BIT
 AS 
 INSERT INTO ProdPrecCrudo (TipoTarif,Numero,IdCia,IdProducto,IdTercero,Duracion,FecInicio,FecFinal,Tarifa,IdMoneda,Inactivo)
 VALUES (@pmTipoTarif,@pmNumero,@pmIdCia,@pmIdProducto,@pmIdTercero,@pmDuracion,@pmFecInicio,@pmFecFinal,@pmTarifa,@pmIdMoneda,@pmInactivo)
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),@pmInactivo BIT
 AS 
 UPDATE ProdPrecCrudo SET IdCia=@pmIdCia,IdProducto=@pmIdProducto,IdTercero=@pmIdTercero,Duracion=@pmDuracion,FecInicio=@pmFecInicio,FecFinal=@pmFecFinal,Tarifa=@pmTarifa,IdMoneda=@pmIdMoneda,Inactivo=@pmInactivo
 WHERE TipoTarif=@pmTipoTarif AND Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelOilEntrada] @pmTipEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_OilEntrada
 WHERE TipEnt=@pmTipEnt AND NumEntrada=@pmNumEntrada AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilEntrada] @pmTipEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmNumGuia INT,@pmIdCiaGuia CHAR(2),@pmFecDespacho SMALLDATETIME,@pmFecDescargue SMALLDATETIME,@pmIdProveedor VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmNomEmpTrans VARCHAR(150),@pmRemolque VARCHAR(10),@pmValorTotal DECIMAL(16,6),@pmCtaPagar BIT,@pmTipFactCxp VARCHAR(3),@pmNumFactura INT,@pmIdCiaCxp CHAR(2),@pmEstContable INT,@pmCdConcepto VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmOrigenAdd VARCHAR(10),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OilEntrada (TipEnt,NumEntrada,IdCia,Fecha,NumGuia,IdCiaGuia,FecDespacho,FecDescargue,IdProveedor,IdVehiculo,IdConductor,NitEmpTrans,NomEmpTrans,Remolque,ValorTotal,CtaPagar,TipFactCxp,NumFactura,IdCiaCxp,EstContable,CdConcepto,TipCom,Comprobante,IdCiaCom,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario)
 VALUES (@pmTipEnt,@pmNumEntrada,@pmIdCia,@pmFecha,@pmNumGuia,@pmIdCiaGuia,@pmFecDespacho,@pmFecDescargue,@pmIdProveedor,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmNomEmpTrans,@pmRemolque,@pmValorTotal,@pmCtaPagar,@pmTipFactCxp,@pmNumFactura,@pmIdCiaCxp,@pmEstContable,@pmCdConcepto,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmFechaCrea,@pmIdCiaCrea,@pmOrigenAdd,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOilEntrada] @pmTipEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmNumGuia INT,@pmIdCiaGuia CHAR(2),@pmFecDespacho SMALLDATETIME,@pmFecDescargue SMALLDATETIME,@pmIdProveedor VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmNomEmpTrans VARCHAR(150),@pmRemolque VARCHAR(10),@pmValorTotal DECIMAL(16,6),@pmCtaPagar BIT,@pmTipFactCxp VARCHAR(3),@pmNumFactura INT,@pmIdCiaCxp CHAR(2),@pmEstContable INT,@pmCdConcepto VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4)
 AS 
 UPDATE Trn_OilEntrada SET Fecha=@pmFecha,NumGuia=@pmNumGuia,IdCiaGuia=@pmIdCiaGuia,FecDespacho=@pmFecDespacho,FecDescargue=@pmFecDescargue,IdProveedor=@pmIdProveedor,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,NitEmpTrans=@pmNitEmpTrans,NomEmpTrans=@pmNomEmpTrans,Remolque=@pmRemolque,ValorTotal=@pmValorTotal,CtaPagar=@pmCtaPagar,TipFactCxp=@pmTipFactCxp,NumFactura=@pmNumFactura,IdCiaCxp=@pmIdCiaCxp,EstContable=@pmEstContable,CdConcepto=@pmCdConcepto,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion
,IdEstado=@pmIdEstado
 WHERE TipEnt=@pmTipEnt AND NumEntrada=@pmNumEntrada AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilEntrada] @pmTipEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipEnt,NumEntrada,IdCia,Fecha,NumGuia,IdCiaGuia,FecDespacho,FecDescargue,IdProveedor,IdVehiculo,IdConductor,NitEmpTrans,NomEmpTrans,Remolque,ValorTotal,CtaPagar,TipFactCxp,NumFactura,IdCiaCxp,EstContable,CdConcepto,TipCom,Comprobante,IdCiaCom,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,OrigenAdd,IdUsuario
 FROM Trn_OilEntrada
 WHERE TipEnt=@pmTipEnt AND NumEntrada=@pmNumEntrada AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOilEntradaProd] @pmTipEnt VARCHAR(3),@pmNumEntrada INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdTanque VARCHAR(4),@pmIdProducto VARCHAR(16),@pmCantidad DECIMAL(16,6),@pmUndMed VARCHAR(10),@pmTemperatura DECIMAL(16,6),@pmUnidTemp CHAR(1),@pmValorAPI DECIMAL(16,6),@pmValorVCW DECIMAL(16,6),@pmAPI_Corregido DECIMAL(16,6),@pmDensidad DECIMAL(16,6),@pmCantCargue DECIMAL(16,6)
,@pmTempCargue DECIMAL(16,6),@pmAPI_Cargue DECIMAL(16,6),@pmVCW_Cargue DECIMAL(16,6),@pmPesoNeto DECIMAL(16,6),@pmVrUnitario DECIMAL(16,6)
 AS 
 INSERT INTO Trn_OilEntradaProd (TipEnt,NumEntrada,IdCia,Item,IdTanque,IdProducto,Cantidad,UndMed,Temperatura,UnidTemp,ValorAPI,ValorVCW,API_Corregido,Densidad,CantCargue,TempCargue,API_Cargue,VCW_Cargue,PesoNeto,VrUnitario)
 VALUES (@pmTipEnt,@pmNumEntrada,@pmIdCia,@pmItem,@pmIdTanque,@pmIdProducto,@pmCantidad,@pmUndMed,@pmTemperatura,@pmUnidTemp,@pmValorAPI,@pmValorVCW,@pmAPI_Corregido,@pmDensidad,@pmCantCargue,@pmTempCargue,@pmAPI_Cargue,@pmVCW_Cargue,@pmPesoNeto,@pmVrUnitario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOilGuiaFmt] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT G.TipDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.IdProveedor,T.RazonSocial AS NomProveedor,G.IdVehiculo,G.IdConductor,CD.RazonSocial AS NomConductor,G.IdEstacion AS IdCampo,CM.EstServicio AS Campo
,G.IdProducto,DescripProd,G.CantBruto,G.CantNeto,G.UndMed,UM.Unidad,G.Temperatura,G.UnidTemp,G.ValorAPI,G.ValorVCW,G.CantDescargue,G.APIDescargue,G.TempDescargue,G.FecDescargue
,G.NitEmpTrans,G.NomEmpTrans,G.NumRemision,G.Remolque,G.TipoEnt,G.NumEntrada,G.IdCiaEnt,G.Anulado,G.FecDev
,G.Observacion,G.IdEstado,Estado,G.FechaCrea,G.IdCiaCrea,G.OrigenAdd,G.IdUsuario,Usuario,TipoDoc
--datos proveedor
,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercCiudad,L.IdDep AS TercIdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.TelMovil AS TercCelular,T.e_mail AS TercEmail
 FROM Trn_OilGuia AS G INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc INNER JOIN EstadoDoc AS ED ON G.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Terceros AS T ON G.IdProveedor=T.IdTercero
 INNER JOIN ProdMcias AS P ON G.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON G.UndMed=UM.UndMed
 INNER JOIN Terceros AS CD ON G.IdConductor=CD.IdTercero INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 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].[paQryOilEntradaFmt] @pmTipEnt VARCHAR(3),@pmNumEntradaIni INT,@pmNumEntradaFin INT,@pmIdCia CHAR(2)
AS 
 SELECT E.TipEnt,E.NumEntrada,E.IdCia,Compania,E.Fecha,E.NumGuia,E.IdCiaGuia,E.FecDespacho,E.FecDescargue,E.IdProveedor,T.RazonSocial AS NomProveedor,E.IdVehiculo,E.IdConductor,CD.RazonSocial AS NomConductor
,D.IdTanque,D.IdProducto,D.Cantidad,D.UndMed,D.Temperatura,D.UnidTemp,D.ValorAPI,D.ValorVCW,D.API_Corregido,D.Densidad
,D.CantCargue,D.TempCargue,D.API_Cargue,D.VCW_Cargue,D.PesoNeto,D.VrUnitario
,E.NitEmpTrans,E.NomEmpTrans,E.Remolque,E.ValorTotal,E.CtaPagar,E.TipFactCxp,E.NumFactura,E.IdCiaCxp,E.EstContable,E.CdConcepto,Concepto
,E.TipCom,E.Comprobante,E.IdCiaCom,E.Anulado,E.FecDev,E.Observacion,E.IdEstado,Estado,E.FechaCrea,E.IdCiaCrea,E.OrigenAdd,E.IdUsuario,Usuario,TipoDoc
--Datos del proveedor
,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_OilEntrada AS E INNER JOIN Trn_OilEntradaProd AS D ON E.TipEnt=D.TipEnt AND E.NumEntrada=D.NumEntrada AND E.IdCia=D.IdCia
 INNER JOIN Sys_TiposDoc AS TD ON E.TipEnt=TD.IdDoc INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Terceros AS T ON E.IdProveedor=T.IdTercero
 INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed
 INNER JOIN Terceros AS CD ON E.IdConductor=CD.IdTercero INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 LEFT JOIN Conceptos AS CT ON E.CdConcepto=CT.IdConcepto
 WHERE E.TipEnt=@pmTipEnt AND E.NumEntrada BETWEEN @pmNumEntradaIni AND @pmNumEntradaFin AND E.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro
,O.IdVend,VN.RazonSocial AS NomVendedor,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipCausac,O.Causacion,O.CdCiaCau,O.EstCumplido,O.FechaCump,O.TipoRuta,O.Volumen,EstadoBod,O.CdBodega,B.Bodega AS NomBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.Referencias,O.IdEstado AS CdEstado,Estado
--Detalles de orden
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.NumDocFac,D.IndFactItem,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
--datos de facturas
,FC.FacNumero,FC.FacFecha,ISNULL(FC.FacValor,0) AS FacVrTotal,ISNULL(FC.FacCosto,0) AS FacVrCosto,ISNULL(FC.FacImpuesto,0) AS FacVrIva,ISNULL(FC.FacSeguro,0) AS FacVrSeguro,FacBaseIngreso,DfcBaseIngreso,FacReajuste,DfcReajuste
--datos del tercero
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,V.NumVeh,V.TipoAfil,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,V.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,V.IdConductor AS CedConductor,CDT.RazonSocial AS Conductor,V.NumSoat,V.VigSoat
,O.NumAutCupo,O.NumAutMora,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN AlmBodegas AS B ON O.CdBodega=B.IdBodega
 LEFT JOIN Vehiculos AS V ON D.PlacaVehic=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CLR ON V.IdColor=CLR.IdColor
 LEFT JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS CDT ON V.IdConductor=CDT.IdTercero
 LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 --Facturas item por item
 LEFT JOIN (SELECT TipRem AS FacTipOrd,Remesa AS FacNumOrden,IdCiaRem AS FacIdCiaOrd,ItemRem AS FacItemOrd,MAX(FecRemesa) AS FacFecha
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValor
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCosto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrImpuesto*-1 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacSeguro
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN VrDeclMcia ELSE 0 END) AS FacDeclarado
,MAX(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN FR.Factura ELSE 0 END) AS FacNumero
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS DfcBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacReajuste
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfcReajuste
 FROM Trn_TraFacRemesas AS FR LEFT JOIN ConcDiversos AS C ON FR.CdConcepto=C.IdConcepto
 WHERE FR.TipRem='OSM' GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS FC ON D.TipDoc=FC.FacTipOrd AND D.NumOrden=FC.FacNumOrden AND D.IdCia=FC.FacIdCiaOrd AND D.Item=FC.FacItemOrd
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod
,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Anulado,O.FecDev,O.Observacion AS Observ,O.Referencias,O.IdEstado AS CdEstado,Estado,O.NumAutCupo,O.NumAutMora
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ 
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 WHERE O.TipDoc=@pmTipDoc AND O.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod
,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Anulado,O.FecDev,O.Observacion AS Observ,O.Referencias,O.IdEstado AS CdEstado,Estado,O.NumAutCupo,O.NumAutMora
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.NumDocFac,D.IndFactItem,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact
,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,EstCumplido,FechaCump,TipoRuta,Volumen,EstadoBod,CdBodega,FechaIngBod,FechaRetBod,NitRemitente,Remitente,NitDestinatario,Destinatario,NumAutCupo,NumAutMora,Referencias,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_MudOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmEstadoBod INT,@pmCdBodega VARCHAR(4),@pmFechaIngBod SMALLDATETIME,@pmFechaRetBod SMALLDATETIME,@pmNitRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmNitDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150)
,@pmNumAutCupo INT,@pmNumAutMora INT,@pmReferencias VARCHAR(1000),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudOrdenServ (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv
,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,EstCumplido,FechaCump,TipoRuta,Volumen,EstadoBod,CdBodega,FechaIngBod,FechaRetBod,NitRemitente,Remitente,NitDestinatario,Destinatario,NumAutCupo,NumAutMora,Referencias)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmIdTipoServ,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmDirCargue,@pmTipoInmRem,@pmPisoCargue,@pmTelsRemite,@pmCelRemite,@pmDirDescargue,@pmTipoInmDest,@pmPisoDescargue,@pmTelsDest,@pmCelDest,@pmIdOrigen,@pmIdDestino,@pmVrServicio,@pmVrEmpaque,@pmVrBodega,@pmVrOtros,@pmVrDescuento,@pmVrImpuesto,@pmVrNeto,@pmVrDeclarado,@pmVrSeguro
,@pmServicios,@pmFormaPago,@pmReferencia,@pmTipInv,@pmNumInvent,@pmIdCiaInv,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFact,@pmNumFactura,@pmCdCiaFact,@pmTipCausac,@pmCausacion,@pmCdCiaCau,@pmNomContacto,@pmTelsContacto,@pmemlContacto,@pmContactoDest,@pmemlContDest,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmEstCumplido,@pmFechaCump
,@pmTipoRuta,@pmVolumen,@pmEstadoBod,@pmCdBodega,@pmFechaIngBod,@pmFechaRetBod,@pmNitRemitente,@pmRemitente,@pmNitDestinatario,@pmDestinatario,@pmNumAutCupo,@pmNumAutMora,@pmReferencias)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmEstadoBod INT,@pmCdBodega VARCHAR(4),@pmFechaIngBod SMALLDATETIME,@pmFechaRetBod SMALLDATETIME,@pmNitRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmNitDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150)
,@pmNumAutCupo INT,@pmNumAutMora INT,@pmReferencias VARCHAR(1000),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_MudOrdenServ SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,IdTipoServ=@pmIdTipoServ,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVend=@pmIdVend,DirCargue=@pmDirCargue,TipoInmRem=@pmTipoInmRem,PisoCargue=@pmPisoCargue,TelsRemite=@pmTelsRemite,CelRemite=@pmCelRemite,DirDescargue=@pmDirDescargue,TipoInmDest=@pmTipoInmDest,PisoDescargue=@pmPisoDescargue,TelsDest=@pmTelsDest,CelDest=@pmCelDest
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,VrServicio=@pmVrServicio,VrEmpaque=@pmVrEmpaque,VrBodega=@pmVrBodega,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrNeto=@pmVrNeto,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,Servicios=@pmServicios,FormaPago=@pmFormaPago,Referencia=@pmReferencia,TipInv=@pmTipInv,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem
,EstFactura=@pmEstFactura,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CdCiaFact=@pmCdCiaFact,TipCausac=@pmTipCausac,Causacion=@pmCausacion,CdCiaCau=@pmCdCiaCau,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,emlContacto=@pmemlContacto,ContactoDest=@pmContactoDest,emlContDest=@pmemlContDest,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EstCumplido=@pmEstCumplido
,FechaCump=@pmFechaCump,TipoRuta=@pmTipoRuta,Volumen=@pmVolumen,EstadoBod=@pmEstadoBod,CdBodega=@pmCdBodega,FechaIngBod=@pmFechaIngBod,FechaRetBod=@pmFechaRetBod,NitRemitente=@pmNitRemitente,Remitente=@pmRemitente,NitDestinatario=@pmNitDestinatario,Destinatario=@pmDestinatario,NumAutCupo=@pmNumAutCupo,NumAutMora=@pmNumAutMora,Referencias=@pmReferencias,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAuxInvSaldoAct] (@pmIdProducto VARCHAR(16),@pmIdCia CHAR(2),@pmIdBodega VARCHAR(4))
RETURNS DECIMAL(14,4)
 AS  
 BEGIN
  DECLARE @SaldoAct DECIMAL(14,4)
  IF (@pmIdCia='00' OR @pmIdCia='0')
   BEGIN
    IF @pmIdBodega='0'
     BEGIN
      SET @SaldoAct=(SELECT SUM(SaldoActual) FROM ProdSaldos WHERE IdProducto=@pmIdProducto)
     END
    ELSE
     BEGIN
      SET @SaldoAct=(SELECT SUM(SaldoActual) FROM ProdSaldos WHERE IdProducto=@pmIdProducto AND IdBodega=@pmIdBodega)
     END
   END
  ELSE
   BEGIN
    IF @pmIdBodega='0'
     BEGIN
      SET @SaldoAct=(SELECT SUM(SaldoActual) FROM ProdSaldos WHERE IdProducto=@pmIdProducto AND IdCia=@pmIdCia)
     END
    ELSE
     BEGIN
      SET @SaldoAct=(SELECT SUM(SaldoActual) FROM ProdSaldos WHERE IdProducto=@pmIdProducto AND IdBodega=@pmIdBodega AND IdCia=@pmIdCia)
     END
   END
  RETURN @SaldoAct
 END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAuxInvNivelMax] (@pmIdProducto VARCHAR(16),@pmIdCia CHAR(2))
RETURNS DECIMAL(14,4)
 AS  
 BEGIN
  DECLARE @NivelMax DECIMAL(14,4)
  IF (@pmIdCia='00' OR @pmIdCia='0')
   BEGIN
    SET @NivelMax=(SELECT ExtciaMax FROM ProdMcias WHERE IdProducto=@pmIdProducto)
   END
  ELSE
   BEGIN 
    SET @NivelMax=(SELECT NivelMax FROM ProdNiveles WHERE IdProducto=@pmIdProducto AND IdCia=@pmIdCia)
   END
  RETURN @NivelMax
 END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncAuxInvNivelMin] (@pmIdProducto VARCHAR(16),@pmIdCia CHAR(2))
RETURNS DECIMAL(14,4)
 AS  
 BEGIN
  DECLARE @NivelMin DECIMAL(14,4)
  IF (@pmIdCia='00' OR @pmIdCia='0')
   BEGIN
    SET @NivelMin=(SELECT ExtciaMin FROM ProdMcias WHERE IdProducto=@pmIdProducto)
   END
  ELSE
   BEGIN 
    SET @NivelMin=(SELECT NivelMin FROM ProdNiveles WHERE IdProducto=@pmIdProducto AND IdCia=@pmIdCia)
   END
  RETURN @NivelMin
 END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AuxInvUbic] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdBodega,Bodega,tmIdUbic,Ubicacion
,tmSaldoAnt,tmCostoAnt,tmEntradas,tmCostoEnt,tmSalidas,tmCostoSal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmSaldoAnt+tmEntradas-tmSalidas ELSE tmSalidas-tmEntradas  END AS NuevoSaldo
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoAnt+tmCostoEnt-tmCostoSal ELSE 0  END AS CostoTotal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm ELSE 0  END AS CostoPromActual
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm*(tmSaldoAnt+tmEntradas-tmSalidas) ELSE 0  END AS  CostoTotalPromedio
,TipoRef,PM.IdUnd AS CdUnid,UM.Unidad AS UnidadPres,PM.UndMed AS CdUndMed,SU.Unidad AS UnidadMed
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,Tamano,Color,Referencia,VrCosto AS ProdUltCosto,VrCostPmd AS ProdCostoProm,IdTarIva,Tarifa,Simbolo,IvaInc
,Precio1,Precio2,Precio3,Precio4,Precio5,IdProv,PV.RazonSocial AS NomProveedor,DescripLong,DescripAbrv
,dbo.FuncAuxInvNivelMin(tmIdProducto,tmIdCia) AS ExtciaMin,dbo.FuncAuxInvNivelMax(tmIdProducto,tmIdCia) AS ExtciaMax
,dbo.FuncAuxInvSaldoAct(tmIdProducto,tmIdCia,tmIdBodega) AS ExtciaAct --,ExtciaMin,ExtciaMax,ExtciaAct
,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo,Pasillo,Nivel,Profdad,Notas
 FROM tm_AuxInvUbic AS A INNER JOIN ProdMcias AS PM ON A.tmIdProducto=PM.IdProducto
 INNER JOIN UndMed AS UM ON PM.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed
 INNER JOIN Terceros AS PV ON PM.IdProv=PV.IdTercero
 LEFT JOIN Companias AS CN ON A.tmIdCia=CN.IdCia LEFT JOIN Bodegas AS B ON A.tmIdBodega=B.IdBodega
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa LEFT JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 LEFT JOIN BodUbic AS U ON A.tmIdUbic=U.IdUbic
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AuxInvVen] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdBodega,Bodega,tmNumLote,tmSaldoAnt,tmCostoAnt
,tmEntradas,tmCostoEnt,tmSalidas,tmCostoSal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmSaldoAnt+tmEntradas-tmSalidas ELSE tmSalidas-tmEntradas  END AS NuevoSaldo
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoAnt+tmCostoEnt-tmCostoSal ELSE 0  END AS CostoTotal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm ELSE 0  END AS CostoPromActual
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm*(tmSaldoAnt+tmEntradas-tmSalidas) ELSE 0  END AS  CostoTotalPromedio,tmProdCia
--información de ventas
,tmVtaCant,tmVtaCantDev,tmVtaCant-tmVtaCantDev AS VtaCantNeto,tmVtaTotal,tmVtaTotalDev,(tmVtaTotal-tmVtaTotalDev)+(tmVtaGlobal-tmVtaGlobalDev) AS VtaNeto
,tmVtaCosto,tmVtaCostoDev,tmVtaCosto-tmVtaCostoDev AS VtaCostoNeto,tmVtaDcto,tmVtaDctoDev,tmVtaDcto-tmVtaDctoDev AS VtaDctoNeto
,((tmVtaTotal-tmVtaTotalDev)+(tmVtaGlobal-tmVtaGlobalDev))-(tmVtaCosto-tmVtaCostoDev)  AS VtaUtilidad,tmVtaIva,tmVtaIvaDev,tmVtaRet,tmVtaRetDev
,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev
,TipoRef,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,tmCdTanque,PM.IdUnd AS CdUnid,UM.Unidad AS UnidadPres,PM.UndMed AS CdUndMed,SU.Unidad AS UnidadMed
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,Tamano,Color,Referencia,VrCosto AS ProdUltCosto,VrCostPmd AS ProdCostoProm,IdTarIva,Tarifa,Simbolo,IvaInc
,tmPrecioVta1 AS Precio1,tmPrecioVta2 AS Precio2,tmPrecioVta3 AS Precio3,tmPrecioVta4 AS Precio4,tmPrecioVta5 AS Precio5
,IdProv,PV.RazonSocial AS NomProveedor,DescripLong,DescripAbrv
,dbo.FuncAuxInvNivelMin(tmIdProducto,tmIdCia) AS ExtciaMin,dbo.FuncAuxInvNivelMax(tmIdProducto,tmIdCia) AS ExtciaMax
,dbo.FuncAuxInvSaldoAct(tmIdProducto,tmIdCia,tmIdBodega) AS ExtciaAct --,ExtciaMin,ExtciaMax,ExtciaAct
,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo
 FROM tm_AuxInv  AS A INNER JOIN ProdMcias  AS PM ON A.tmIdProducto=PM.IdProducto 
 INNER JOIN UndMed AS UM ON PM.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed   INNER JOIN Terceros AS PV ON PM.IdProv=PV.IdTercero
 LEFT JOIN Companias AS CN ON A.tmIdCia=CN.IdCia  LEFT JOIN Bodegas AS B ON A.tmIdBodega=B.IdBodega
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa
 WHERE tmEst=@pmtmEst 
 ORDER BY DescripProd,tmIdCia,tmIdBodega
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AuxInv] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdBodega,Bodega,tmNumLote,tmSaldoAnt,tmCostoAnt
,tmEntradas,tmCostoEnt,tmSalidas,tmCostoSal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmSaldoAnt+tmEntradas-tmSalidas ELSE tmSalidas-tmEntradas  END AS NuevoSaldo
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoAnt+tmCostoEnt-tmCostoSal ELSE 0  END AS CostoTotal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm ELSE 0  END AS CostoPromActual
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm*(tmSaldoAnt+tmEntradas-tmSalidas) ELSE 0  END AS  CostoTotalPromedio,tmProdCia
,TipoRef,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,tmCdTanque,PM.IdUnd AS CdUnid,UM.Unidad AS UnidadPres,PM.UndMed AS CdUndMed,SU.Unidad AS UnidadMed
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,Tamano,Color,Referencia,VrCosto AS ProdUltCosto,VrCostPmd AS ProdCostoProm,IdTarIva,Tarifa,Simbolo,IvaInc
,tmPrecioVta1 AS Precio1,tmPrecioVta2 AS Precio2,tmPrecioVta3 AS Precio3,tmPrecioVta4 AS Precio4,tmPrecioVta5 AS Precio5,IdProv,PV.RazonSocial AS NomProveedor,DescripLong,DescripAbrv
,dbo.FuncAuxInvNivelMin(tmIdProducto,tmIdCia) AS ExtciaMin,dbo.FuncAuxInvNivelMax(tmIdProducto,tmIdCia) AS ExtciaMax
,dbo.FuncAuxInvSaldoAct(tmIdProducto,tmIdCia,tmIdBodega) AS ExtciaAct
,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo 
 FROM tm_AuxInv AS A INNER JOIN ProdMcias AS PM ON A.tmIdProducto=PM.IdProducto 
 INNER JOIN UndMed AS UM ON PM.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed INNER JOIN Terceros AS PV ON PM.IdProv=PV.IdTercero
 LEFT JOIN Companias AS CN ON A.tmIdCia=CN.IdCia LEFT JOIN Bodegas AS B ON A.tmIdBodega=B.IdBodega
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa
 WHERE tmEst=@pmtmEst
 ORDER BY DescripProd,tmIdCia,tmIdBodega
GO
--feb 23
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCtasCorrientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsCtasCorrientes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTanques]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTanques]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCtasCorrientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCtasCorrientes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCtasCorrientesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCtasCorrientesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCtasCorrientesNum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCtasCorrientesNum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCtasCorrientesPuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCtasCorrientesPuc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTanques]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTanques]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTanquesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTanquesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCtasCorrientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpCtasCorrientes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTanques]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTanques]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTanques] @pmIdTanque VARCHAR(4),@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(50),@pmCapTanq DECIMAL(14,4),@pmNivAgua DECIMAL(14,4),@pmCapNeta DECIMAL(14,4)
,@pmSaldoActual DECIMAL(14,4),@pmTnqGas BIT,@pmTnqUnido BIT,@pmTnqVirtual BIT,@pmCdNueTan VARCHAR(4),@pmNColor INT,@pmFechaAdd SMALLDATETIME,@pmInactivo BIT,@pmIdCiaDef CHAR(2)
,@pmCodigoApi VARCHAR(10),@pmCdTerminal VARCHAR(4),@pmTipoProd VARCHAR(10),@pmAltura DECIMAL(16,6)
 AS 
 INSERT INTO Tanques (IdTanque,IdProducto,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual,TnqGas,TnqUnido,TnqVirtual,CdNueTan,NColor,FechaAdd,Inactivo,IdCiaDef,CodigoApi,CdTerminal,TipoProd,Altura)
 VALUES (@pmIdTanque,@pmIdProducto,@pmDescripcion,@pmCapTanq,@pmNivAgua,@pmCapNeta,@pmSaldoActual,@pmTnqGas,@pmTnqUnido,@pmTnqVirtual,@pmCdNueTan,@pmNColor
,@pmFechaAdd,@pmInactivo,@pmIdCiaDef,@pmCodigoApi,@pmCdTerminal,@pmTipoProd,@pmAltura)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTanques] @pmIdTanque VARCHAR(4),@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(50),@pmCapTanq DECIMAL(14,4),@pmNivAgua DECIMAL(14,4),@pmCapNeta DECIMAL(14,4)
,@pmSaldoActual DECIMAL(14,4),@pmTnqGas BIT,@pmTnqUnido BIT,@pmTnqVirtual BIT,@pmCdNueTan VARCHAR(4),@pmNColor INT
,@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT,@pmIdCiaDef CHAR(2),@pmCodigoApi VARCHAR(10),@pmCdTerminal VARCHAR(4),@pmTipoProd VARCHAR(10),@pmAltura DECIMAL(16,6)
 AS 
 UPDATE Tanques SET IdProducto=@pmIdProducto,Descripcion=@pmDescripcion,CapTanq=@pmCapTanq,NivAgua=@pmNivAgua,CapNeta=@pmCapNeta,SaldoActual=@pmSaldoActual
,TnqGas=@pmTnqGas,TnqUnido=@pmTnqUnido,TnqVirtual=@pmTnqVirtual,CdNueTan=@pmCdNueTan,NColor=@pmNColor,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo
,IdCiaDef =@pmIdCiaDef,CodigoApi=@pmCodigoApi,CdTerminal=@pmCdTerminal,TipoProd=@pmTipoProd,Altura=@pmAltura
 WHERE IdTanque=@pmIdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTanquesLta] @pmIdProducto VARCHAR(16)=Null
 AS 
 SELECT IdTanque,T.IdProducto AS CdProd,DescripProd,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual
,TnqGas,TnqUnido,TnqVirtual,CdNueTan,NColor,TipoProd,Altura,T.FechaAdd AS Fec_Add,T.FechaUpdate AS Fec_Upd,IdCiaDef,CdTerminal
 FROM Tanques AS T INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto
 WHERE T.Inactivo=0 AND  T.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 ORDER BY DescripProd,IdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTanques] @pmIdTanque VARCHAR(4)
 AS 
 SELECT IdTanque,IdProducto,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual
 ,TnqGas,TnqUnido,TnqVirtual,CdNueTan,NColor,FechaAdd,FechaUpdate,Inactivo,IdCiaDef,CodigoApi,CdTerminal,TipoProd,Altura
 FROM Tanques
 WHERE IdTanque=@pmIdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryCtasCorrientesLta] @pmIdBanco VARCHAR(4)=Null,@pmIdCuenta VARCHAR(16)=Null
,@pmInactivo BIT=Null,@pmIdCiaDef CHAR(2)=Null
 AS 
 SELECT IdCta,NumeroCta,C.IdBanco AS CodBco,Banco,C.IdCuenta AS IdCuent,NomCuenta,ConsCheques,RangoCons,ConsActual,Formato,TipoPapel,Orientacion
,VistaPrevia,VerSetup,NumCopias,TipoProp,IdCiaDef,C.Inactivo AS Inactvo
 FROM CtasCorrientes AS C INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta
 WHERE C.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND  (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND IdCiaDef LIKE ISNULL(@pmIdCiaDef,'%%')
ORDER BY  IdCta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryCtasCorrientesPuc] @pmIdCuenta VARCHAR(16)
AS
 SELECT IdCta,NumeroCta,C.IdBanco AS CodBanco,Banco,IdCuenta,ConsCheques,RangoCons,ConsActual,Formato
 ,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,C.Inactivo AS Inactiv,IdCiaDef,TipoProp
  FROM   CtasCorrientes AS C INNER JOIN Bancos ON C.IdBanco=Bancos.IdBanco
  WHERE  IdCuenta=@pmIdCuenta
  ORDER BY IdCta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryCtasCorrientesNum] @pmNumeroCta VARCHAR(20),@pmOper VARCHAR(4)

AS
 IF  @pmOper ='LIKE'
 SELECT IdCta,NumeroCta,C.IdBanco AS CodBanco,Banco,IdCuenta,ConsCheques,RangoCons,ConsActual,Formato
 ,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,C.Inactivo AS Inactiv,IdCiaDef,TipoProp
  FROM   CtasCorrientes AS C INNER JOIN Bancos ON C.IdBanco=Bancos.IdBanco
  WHERE  NumeroCta LIKE @pmNumeroCta
  ORDER BY IdCta
 ELSE
  SELECT IdCta,NumeroCta,C.IdBanco AS CodBanco,Banco,IdCuenta,ConsCheques,RangoCons,ConsActual,Formato
 ,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,C.Inactivo AS Inactiv,IdCiaDef,TipoProp
  FROM   CtasCorrientes AS C INNER JOIN Bancos ON C.IdBanco=Bancos.IdBanco
   WHERE  NumeroCta=@pmNumeroCta
  ORDER BY IdCta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsCtasCorrientes] @pmIdCta VARCHAR(4),@pmNumeroCta VARCHAR(20),@pmIdBanco VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmConsCheques BIT,@pmRangoCons VARCHAR(20)
,@pmConsActual INT,@pmFormato VARCHAR(20),@pmTipoPapel INT,@pmOrientacion INT,@pmVistaPrevia BIT,@pmVerSetup BIT,@pmNumCopias INT,@pmInactivo BIT,@pmIdCiaDef CHAR(2),@pmTipoProp VARCHAR(10)
 AS 
 INSERT INTO CtasCorrientes (IdCta,NumeroCta,IdBanco,IdCuenta,ConsCheques,RangoCons,ConsActual,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,IdCiaDef,Inactivo,TipoProp)
 VALUES (@pmIdCta,@pmNumeroCta,@pmIdBanco,@pmIdCuenta,@pmConsCheques,@pmRangoCons,@pmConsActual,@pmFormato,@pmTipoPapel,@pmOrientacion
,@pmVistaPrevia,@pmVerSetup,@pmNumCopias,@pmIdCiaDef,@pmInactivo,@pmTipoProp)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpCtasCorrientes] @pmIdCta VARCHAR(4),@pmNumeroCta VARCHAR(20),@pmIdBanco VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmConsCheques BIT,@pmRangoCons VARCHAR(20)
,@pmConsActual INT,@pmFormato VARCHAR(20),@pmTipoPapel INT,@pmOrientacion INT,@pmVistaPrevia BIT,@pmVerSetup BIT,@pmNumCopias INT,@pmInactivo BIT,@pmIdCiaDef CHAR(2),@pmTipoProp VARCHAR(10)
 AS 
 UPDATE CtasCorrientes SET NumeroCta=@pmNumeroCta,IdBanco=@pmIdBanco,IdCuenta=@pmIdCuenta,ConsCheques=@pmConsCheques,RangoCons=@pmRangoCons,ConsActual=@pmConsActual,Formato=@pmFormato
,TipoPapel=@pmTipoPapel,Orientacion=@pmOrientacion,VistaPrevia=@pmVistaPrevia,VerSetup=@pmVerSetup,NumCopias=@pmNumCopias,Inactivo=@pmInactivo,IdCiaDef=@pmIdCiaDef,TipoProp=@pmTipoProp
 WHERE IdCta=@pmIdCta
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryCtasCorrientes] @pmIdCta VARCHAR(4)
 AS 
 SELECT IdCta,NumeroCta,IdBanco,IdCuenta,ConsCheques,RangoCons,ConsActual,Formato,TipoPapel,Orientacion
,VistaPrevia,VerSetup,NumCopias,IdCiaDef,TipoProp,Inactivo
 FROM CtasCorrientes
 WHERE IdCta=@pmIdCta
GO