INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCV','OBQ','Permitir Incluir Unidades Obsequiadas')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCR','OBQ','Permitir Incluir Unidades Obsequiadas')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCC','OBQ','Permitir Incluir Unidades Obsequiadas')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCO','OBQ','Permitir Incluir Unidades Obsequiadas')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDFCO2','OBQ','Permitir Incluir Unidades Obsequiadas')
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('FOS','NO FACTURAR ORDENES DE SERVICIO SIN LIQUIDACION','BOOLEAN','0',5,'TRANS')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsRangosFor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEdsRangosFor]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryInvDetalleFalTq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryInvDetalleFalTq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryInvDetalleSobTq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryInvDetalleSobTq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryInvDetalleTan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryInvDetalleTan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryInvDetalleTco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryInvDetalleTco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexDfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexDfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexDvf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexDvf]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexFco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexFcr]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryInvDetalleSobTq] @pmTipDoc VARCHAR(3),@pmnInventario INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.IdProducto AS CdProducto,DescripProd,D.CdTanque,D.Cantidad,D.CantExist
,CASE T.TnqUnido WHEN 1 THEN 0 ELSE (D.Cantidad-D.CantExist) END AS CantDif
,D.UndAforo,D.UndAgua,D.CantAgua
 FROM Trn_InvDetalle AS D INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
 LEFT JOIN Tanques AS T ON D.CdTanque=T.IdTanque
 WHERE D.TipDoc=@pmTipDoc AND D.nInventario=@pmnInventario AND D.IdCia=@pmIdCia 
 AND (D.Cantidad-D.CantExist)>0 AND T.TnqUnido=0
 ORDER BY DescripProd,D.CdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryInvDetalleTan] @pmTipDoc VARCHAR(3),@pmnInventario INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,D.IdProducto AS CdProducto,DescripProd,D.IdBodega AS CdBodega,CdTanque,UndAforo,UndAgua,D.Cantidad,CantAgua,CantExist
,CASE T.TnqUnido WHEN 1 THEN 0 ELSE (Cantidad-CantExist) END AS CantDif,VrUnitario
,CASE T.TnqVirtual WHEN 1 THEN 0 ELSE (Cantidad*VrUnitario) END AS CostoTotal
,D.Descripcion,D.Unidades,NoConteo,D.IdUsuario AS IdUsuari,Usuario,D.IdEstacion AS CdEstacion
,D.FechaAdd AS Fec_Add
 FROM Trn_InvDetalle AS D  INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Tanques AS T ON D.CdTanque=T.IdTanque
 WHERE D.TipDoc=@pmTipDoc AND D.nInventario=@pmnInventario AND D.IdCia=@pmIdCia 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryInvDetalleTco] @pmTipDoc VARCHAR(3),@pmnInventario INT,@pmIdCia CHAR(2)
,@pmIdUsuario VARCHAR(11)=Null
 AS 
 SELECT COUNT(CASE T.TnqVirtual WHEN 1 THEN 0 ELSE D.Item END) AS SITEMS
,SUM(CASE T.TnqVirtual WHEN 1 THEN 0 ELSE D.Cantidad END) AS SCANT
,SUM(CASE T.TnqVirtual WHEN 1 THEN 0 ELSE (D.VrUnitario*D.Cantidad) END) AS SCOSTOT
,SUM(CASE T.TnqUnido WHEN 1 THEN 0 ELSE D.CantExist END) AS SCANEXI
,SUM(CASE T.TnqUnido WHEN 1 THEN 0 ELSE (D.VrUnitario*D.CantExist) END) AS SEXITOT
,SUM(CASE T.TnqVirtual WHEN 1 THEN 0 ELSE D.CantAgua END) AS SCANTAGUA
,SUM(CASE T.TnqVirtual WHEN 1 THEN 0 ELSE D.Unidades END) AS STOTUNI
 FROM Trn_InvDetalle AS D INNER JOIN Tanques AS T ON D.CdTanque=T.IdTanque
 WHERE D.TipDoc=@pmTipDoc AND D.nInventario=@pmnInventario AND D.IdCia=@pmIdCia 
 AND D.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryInvDetalleFalTq] @pmTipDoc VARCHAR(3),@pmnInventario INT,@pmIdCia CHAR(2)
 AS 
SELECT D.IdProducto AS CdProducto,DescripProd,D.CdTanque,D.Cantidad,D.CantExist
,CASE T.TnqUnido WHEN 1 THEN 0 ELSE (D.Cantidad-D.CantExist) END AS CantDif
,D.UndAforo,D.UndAgua,D.CantAgua
 FROM Trn_InvDetalle AS D INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
 LEFT JOIN Tanques AS T ON D.CdTanque=T.IdTanque
 WHERE D.TipDoc=@pmTipDoc AND D.nInventario=@pmnInventario AND D.IdCia=@pmIdCia 
 AND (D.Cantidad-D.CantExist)<0 AND T.TnqUnido=0
 ORDER BY DescripProd,D.CdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexFcr] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto
,(tmVrPrecio*tmSalidas)-tmVrDcto AS SubTotal,tmTarifaIva,tmVrIva,tmTarifaIco,tmVrImpCon
,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva AS ValorTotal,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva+tmVrImpCon AS ValorNeto
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca
,tmVrUnitario,tmVrUnitario*tmSalidas AS CostoTotal,tmVrBruto,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion
,tmIdVend,tmComision,tmCdOperario,tmComisnOper,tmNumLote,tmFechLote,tmFecVceLote,tmCdCCosto,tmCdSubCos,tmComptmntos
,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb
,tmServcios,Tanques,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmTipDoc,tmDocumento,tmIdCia
,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmCdAgencia,tmRec_Costo,tmMgenCont,tmIvaComb,tmImpCarb,tmCantObseq,tmIvaObseq
FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexDfo] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto
,(tmVrPrecio*tmEntradas)-tmVrDcto AS SubTotal,tmTarifaIva,tmVrIva,((tmVrPrecio*tmEntradas)-tmVrDcto)+tmVrIva AS ValorTotal
,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmTarifaIco,tmVrImpCon,tmVrUnitario,tmVrUnitario*tmEntradas AS CostoTotal,tmVrBruto,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion
,tmIdVend,tmComision,tmCdCCosto,tmCdSubCos,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet
,tmCodTarCom,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmServcios,Tanques,tmCdTanque,tmEsCombo,tmEsProdBase,tmItemCbo,tmCantObseq,tmIvaObseq
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexDvf] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto
,(tmVrPrecio*tmEntradas)-tmVrDcto AS SubTotal,tmTarifaIva,tmVrIva,((tmVrPrecio*tmEntradas)-tmVrDcto)+tmVrIva AS ValorTotal
,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmTarifaIco,tmVrImpCon
,tmVrUnitario,tmVrUnitario*tmEntradas AS CostoTotal,tmVrBruto,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion
,tmIdVend,tmComision,tmCdCCosto,tmCdSubCos,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet
,tmCodTarCom,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb
,tmServcios,Tanques,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmTipDoc,tmDocumento,tmIdCia
,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmCdAgencia,tmCantObseq,tmIvaObseq
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexFco] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmSalidas,tmVrPrecio,tmListaPrec,tmTarifaDct,tmVrDcto,tmTarifaIva,tmTarifaIco
,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva+tmVrImpCon AS ValorTotal,((tmVrPrecio*tmSalidas)-tmVrDcto)+tmVrIva AS ValorNeto
,tmVrIva,tmVrImpCon,tmUnidades,tmIdUnd,Unidad,tmIdBodega,Bodega,tmReferencia,tmDescripcion,tmVrBruto,tmVrUnitario,tmVrUnitario*tmSalidas AS CostoTotal
,tmIdVend,tmComision,tmCodTarDct,tmCodTarIva,tmCodTarCom,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmServcios,Tanques,tmCdTanque
,tmEsCombo,tmEsProdBase,tmItemCbo,tmTipDoc,tmDocumento,tmIdCia,tmCdOperario,tmComisnOper,tmCodTarCmc,tmpVehiculo,tmRec_Costo,tmCantObseq,tmIvaObseq
FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY  tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEdsRangosFor] @pmTipoRango VARCHAR(3),@pmNumSerie VARCHAR(5)
,@pmNumForma INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipoRango,IdRango,IdForma,Fecha,FecVence,IdCliente,IdAgencia,pVehiculo,nVehiculo,IdCuenta,NumSerie,NumInicial,NumFinal
,NumTarjeta,VrCupo,VrConsumos,TipoConsumo,ReqPlaca,ReqProd,ReqGalones,VldAgencia,VldPlaca
,Modalidad,Anulado,Observacion,IdEstado,NumAuto,NumActual,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_EdsRangos
 WHERE TipoRango=@pmTipoRango AND NumSerie=@pmNumSerie
 AND @pmNumForma BETWEEN NumInicial AND NumFinal AND Anulado=0 AND IdCiaCrea=@pmIdCia
 ORDER BY IdRango
GO