ALTER TABLE tm_MovTanques ADD tmUndsEnt DECIMAL(16,4) DEFAULT(0) NOT NULL,tmUndsDve DECIMAL(16,4) DEFAULT(0) NOT NULL
,tmUndsFac DECIMAL(16,4) DEFAULT(0) NOT NULL,tmUndsDfc DECIMAL(16,4) DEFAULT(0) NOT NULL
,tmUndsSal DECIMAL(16,4) DEFAULT(0) NOT NULL,tmUndsDvs DECIMAL(16,4) DEFAULT(0) NOT NULL
,tmUndsOtr DECIMAL(16,4) DEFAULT(0) NOT NULL,tmUndsDot DECIMAL(16,4) DEFAULT(0) NOT NULL
GO
CREATE TABLE VehRemPolizas ( 
IdRemque VARCHAR(10) NOT NULL,
IdClase VARCHAR(4) NOT NULL,
Numero VARCHAR(30) NOT NULL,
FecEmision SMALLDATETIME NOT NULL,
FecVigencia SMALLDATETIME NOT NULL,
NitCompania VARCHAR(16),
Valor MONEY DEFAULT ((0)) NOT NULL,
Comntarios VARCHAR(250),
DiasAviso INT DEFAULT ((15)) NOT NULL
 CONSTRAINT PK_VehRemPolizas PRIMARY KEY CLUSTERED (IdRemque,IdClase),
 CONSTRAINT CK_VehRemPolizasIdClase CHECK ((len([IdClase])>(0))),
 CONSTRAINT CK_VehRemPolizasIdRemque CHECK ((len([IdRemque])>(0))),
 CONSTRAINT CK_VehRemPolizasNumero CHECK ((len([Numero])>(0))))
GO
ALTER TABLE VehRemPolizas ADD 
 CONSTRAINT FK_VehRemPolizas_ClasePol FOREIGN KEY (IdClase) REFERENCES [dbo].[ClasePol] (IdClase)
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMRMQ','POL','Modificar información de pólizas y vencimientos')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMRMQTRA','POL','Modificar información de pólizas y vencimientos')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_MovTanquesDia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_MovTanquesDia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_MovTanquesRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_MovTanquesRes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehRemPolizasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehRemPolizasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_MovTanques]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_MovTanques]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_MovTanquesDia] @pmtmEst CHAR(2),@pmtmIdProducto VARCHAR(16)=Null
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdTanque,tmFecha,tmSaldoAntDia,tmEntradas,tmDevEnt
,tmPlanillas,tmDevPlanillas,tmNotasCalib,tmFacturas,tmDevFact,tmSalidas,tmDevSalidas,tmAjusteSob,tmAjusteFal
,tmTraslados,tmOtros,tmNuevoSaldo,tmCantFisico,tmDiferencia,tmCantFisico-tmNuevoSaldo AS Sob_Fal,tmCantAgua
--datos del producto
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,DescripAbrv
,PM.IdUnd AS CdUnid,Unidad,Precio1,Precio2,Precio3,Precio4,Precio5,VrCosto,VrCostPmd,ExtciaAct,ExtciaMin,ExtciaMax,FecUltcom,FecUltVta
,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual,TnqGas,TnqUnido,TnqVirtual,NColor
--costos
,tmCostoAntDia,tmCostoEnt,tmCostoDve,tmCostoPla,tmCostoDvp,tmCostoNot,tmCostoFac,tmCostoDfc
,tmCostoSal,tmCostoDvs,tmCostoAjs,tmCostoAjf,tmCostoTra,tmCostoOtr,tmCostoNsal
,tmSaldoAnt,tmCostoAnt,tmSaldoAntCia,tmCostoSanCia,tmSaldoAntTan,tmCostoSanTan,tmSaldoAntPro,tmCostoSanPro
,tmUndsEnt,tmUndsDve,tmUndsFac,tmUndsDfc,tmUndsSal,tmUndsDvs,tmUndsOtr,tmUndsDot
 FROM tm_MovTanques AS M INNER JOIN ProdMcias AS PM ON M.tmIdProducto=PM.IdProducto
 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 UndMed AS UM ON PM.IdUnd=UM.IdUnd
 LEFT JOIN Companias AS CN ON M.tmIdCia=CN.IdCia  LEFT JOIN Tanques AS TQ ON M.tmIdTanque=TQ.IdTanque
 WHERE tmEst=@pmtmEst AND tmIdProducto LIKE ISNULL(@pmtmIdProducto,'%')
 ORDER BY DescripProd,tmIdCia,tmIdTanque,tmFecha
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_MovTanquesRes] @pmtmEst CHAR(2),@pmtmIdProducto VARCHAR(16)=Null
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdTanque,tmSaldoAnt,tmEntradas,tmSalidas,tmSaldoAnt+tmEntradas-tmSalidas AS NuevoSaldo
,tmCostoAnt,tmCostoEnt,tmCostoSal,tmCostoAnt+tmCostoEnt-tmCostoSal AS CostoTotal
,tmSaldoAntCia,tmCostoSanCia,tmSaldoAntTan,tmCostoSanTan,tmSaldoAntPro,tmCostoSanPro,tmUndsEnt AS UnidadesEnt,tmUndsDve AS UnidadesSal
--datos del producto
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,DescripAbrv
,PM.IdUnd AS CdUnid,Unidad,Precio1,Precio2,Precio3,Precio4,Precio5,VrCosto,VrCostPmd,ExtciaAct,ExtciaMin,ExtciaMax,FecUltcom,FecUltVta
,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual,TnqGas,TnqUnido,TnqVirtual,NColor
 FROM tm_MovTanques AS M INNER JOIN ProdMcias AS PM ON M.tmIdProducto=PM.IdProducto
 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 UndMed AS UM ON PM.IdUnd=UM.IdUnd
 LEFT JOIN Companias AS CN ON M.tmIdCia=CN.IdCia  LEFT JOIN Tanques AS TQ ON M.tmIdTanque=TQ.IdTanque
 WHERE tmEst=@pmtmEst AND tmIdProducto LIKE ISNULL(@pmtmIdProducto,'%')
 ORDER BY DescripProd,tmIdCia,tmIdTanque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryVehRemPolizasLta] 
 AS 
SELECT P.IdRemque,P.IdClase AS CdClase,ClasePoliza,P.Numero,P.FecEmision,P.FecVigencia AS FecVence,P.NitCompania,RazonSocial
,P.Valor,P.DiasAviso,P.Comntarios 
FROM VehRemPolizas AS P INNER JOIN ClasePol AS CL ON P.IdClase=CL.IdClase
INNER JOIN VehRemolq AS R ON P.IdRemque=R.IdRemque
LEFT JOIN Terceros AS T ON P.NitCompania=T.IdTercero
WHERE R.Inactivo=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_MovTanques] @pmtmEst CHAR(2),@pmtmIdProducto VARCHAR(16),@pmtmIdTanque VARCHAR(4),@pmtmItem INT,@pmtmIdCia CHAR(2),@pmtmFecha SMALLDATETIME
,@pmtmSaldoAnt DECIMAL(14,4),@pmtmCostoAnt MONEY,@pmtmEntradas DECIMAL(14,4),@pmtmCostoEnt MONEY,@pmtmDevEnt DECIMAL(14,4),@pmtmCostoDve MONEY,@pmtmSalidas DECIMAL(14,4)
,@pmtmCostoSal MONEY,@pmtmDevSalidas DECIMAL(14,4),@pmtmCostoDvs MONEY,@pmtmFacturas DECIMAL(14,4),@pmtmCostoFac MONEY,@pmtmDevFact DECIMAL(14,4),@pmtmCostoDfc MONEY
,@pmtmAjusteSob DECIMAL(14,4),@pmtmCostoAjs MONEY,@pmtmAjusteFal DECIMAL(14,4),@pmtmCostoAjf MONEY,@pmtmPlanillas DECIMAL(14,4),@pmtmCostoPla MONEY,@pmtmDevPlanillas DECIMAL(14,4)
,@pmtmCostoDvp MONEY,@pmtmNotasCalib DECIMAL(14,4),@pmtmCostoNot MONEY,@pmtmTraslados DECIMAL(14,4),@pmtmCostoTra MONEY,@pmtmOtros DECIMAL(14,4),@pmtmCostoOtr MONEY
,@pmtmNuevoSaldo DECIMAL(14,4),@pmtmCostoNsal MONEY,@pmtmCantFisico DECIMAL(14,4),@pmtmCantAgua DECIMAL(14,4),@pmtmDiferencia DECIMAL(14,4),@pmtmSaldoAntCia DECIMAL(14,4)
,@pmtmCostoSanCia MONEY,@pmtmSaldoAntTan DECIMAL(14,4),@pmtmCostoSanTan MONEY,@pmtmSaldoAntPro DECIMAL(14,4),@pmtmCostoSanPro MONEY,@pmtmSaldoAntDia DECIMAL(14,4),@pmtmCostoAntDia MONEY
,@pmtmUndsEnt DECIMAL(16,4),@pmtmUndsDve DECIMAL(16,4),@pmtmUndsFac DECIMAL(16,4),@pmtmUndsDfc DECIMAL(16,4),@pmtmUndsSal DECIMAL(16,4),@pmtmUndsDvs DECIMAL(16,4),@pmtmUndsOtr DECIMAL(16,4),@pmtmUndsDot DECIMAL(16,4)
 AS 
 INSERT INTO tm_MovTanques (tmEst,tmIdProducto,tmIdTanque,tmItem,tmIdCia,tmFecha,tmSaldoAnt,tmCostoAnt,tmEntradas,tmCostoEnt,tmDevEnt,tmCostoDve,tmSalidas,tmCostoSal,tmDevSalidas,tmCostoDvs
,tmFacturas,tmCostoFac,tmDevFact,tmCostoDfc,tmAjusteSob,tmCostoAjs,tmAjusteFal,tmCostoAjf,tmPlanillas,tmCostoPla,tmDevPlanillas,tmCostoDvp,tmNotasCalib,tmCostoNot,tmTraslados,tmCostoTra,tmOtros
,tmCostoOtr,tmNuevoSaldo,tmCostoNsal,tmCantFisico,tmCantAgua,tmDiferencia,tmSaldoAntCia,tmCostoSanCia,tmSaldoAntTan,tmCostoSanTan,tmSaldoAntPro,tmCostoSanPro,tmSaldoAntDia,tmCostoAntDia
,tmUndsEnt,tmUndsDve,tmUndsFac,tmUndsDfc,tmUndsSal,tmUndsDvs,tmUndsOtr,tmUndsDot)
 VALUES (@pmtmEst,@pmtmIdProducto,@pmtmIdTanque,@pmtmItem,@pmtmIdCia,@pmtmFecha,@pmtmSaldoAnt,@pmtmCostoAnt,@pmtmEntradas,@pmtmCostoEnt,@pmtmDevEnt,@pmtmCostoDve
,@pmtmSalidas,@pmtmCostoSal,@pmtmDevSalidas,@pmtmCostoDvs,@pmtmFacturas,@pmtmCostoFac,@pmtmDevFact,@pmtmCostoDfc,@pmtmAjusteSob,@pmtmCostoAjs,@pmtmAjusteFal
,@pmtmCostoAjf,@pmtmPlanillas,@pmtmCostoPla,@pmtmDevPlanillas,@pmtmCostoDvp,@pmtmNotasCalib,@pmtmCostoNot,@pmtmTraslados,@pmtmCostoTra,@pmtmOtros,@pmtmCostoOtr
,@pmtmNuevoSaldo,@pmtmCostoNsal,@pmtmCantFisico,@pmtmCantAgua,@pmtmDiferencia,@pmtmSaldoAntCia,@pmtmCostoSanCia,@pmtmSaldoAntTan,@pmtmCostoSanTan,@pmtmSaldoAntPro,@pmtmCostoSanPro,@pmtmSaldoAntDia,@pmtmCostoAntDia
,@pmtmUndsEnt,@pmtmUndsDve,@pmtmUndsFac,@pmtmUndsDfc,@pmtmUndsSal,@pmtmUndsDvs,@pmtmUndsOtr,@pmtmUndsDot)
GO