ALTER TABLE Trn_TraPresAnt ADD CdTipoCarr VARCHAR(4)
GO
ALTER TABLE Sys_Um ADD CodUMFE VARCHAR(20)
GO
CREATE TABLE TiposRespFis ( 
IdResFis VARCHAR(10) NOT NULL,
NomResFiscal VARCHAR(250) NOT NULL,
Inactivo BIT DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_TiposRespFis PRIMARY KEY CLUSTERED (IdResFis),
 CONSTRAINT CK_TiposRespFisNomResFiscal CHECK ((len([NomResFiscal])>(0))))
GO
CREATE TABLE TercRespFiscal ( 
Id INT IDENTITY ( 1,1 ) NOT NULL,
IdTercero VARCHAR(16) NOT NULL,
IdResFis VARCHAR(10) NOT NULL,
Inactivo BIT DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_TercRespFiscal PRIMARY KEY CLUSTERED (Id))
GO
ALTER TABLE TercRespFiscal ADD 
 CONSTRAINT FK_TercRespFiscal_Terceros FOREIGN KEY (IdTercero) REFERENCES [dbo].[Terceros] (IdTercero),
 CONSTRAINT FK_TercRespFiscal_TiposRespFis FOREIGN KEY (IdResFis) REFERENCES [dbo].[TiposRespFis] (IdResFis)
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMRUTTRA','BLQ','Permitir Bloqueo por Inactividad')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraPresAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraPresAnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPresAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPresAnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPresAntFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPresAntFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPresAntLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPresAntLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraPresAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraPresAnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsUm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsUm]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpUm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpUm]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryUm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryUm]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraPresAntLta] @pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null,@pmTipoAfilVeh VARCHAR(10)=Null
,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 SELECT P.NumPsto AS NumPrespto,Fecha,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TipoRuta,CdRuta,Ruta
,FecInicio,FecFinal,VrGastos,VrComb,VrPeajes,VrAnticipo,CdCat,Catpeaje,CdTipoVeh,TipoVehiculo,TipoAfilVeh,CdTipoMot,TipoMotor
,CdCom,TipoComb,CdRango,DescripRango,ValorFijo,P.Observacion,P.Inactivo AS Inactvo,NitCliente,CL.RazonSocial AS Cliente,CdMercancia,DescripMcia
,CdTipoCarr,TipoCar,Cantgalones,VrExtralegal,FechaCrea,FechaAct,P.IdUsuario AS CdUsuario,Usuario
,D.IdConcepto AS CdConcepto,Concepto,Descripcion,Cantidad,VrUnitario,VrTotal,TipoRubro,CdClase,ClasePeaje,NitTercero,T.RazonSocial,CdTipOper,TipoOperacion
 FROM Trn_TraPresAnt AS P INNER JOIN Trn_TraPresItems AS D ON P.NumPsto=D.NumPsto
 INNER JOIN Localidades AS CO ON  P.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Rutas AS R ON P.CdRuta=R.IdRuta LEFT JOIN TiposVeh AS TV ON P.CdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN PeajesCat AS CP ON P.CdCat=CP.IdCat LEFT JOIN TiposMot AS TM ON P.CdTipoMot=TM.IdTipoMot
 LEFT JOIN TiposFuel AS TF ON P.CdCom=TF.IdCom LEFT JOIN RangosPeso AS RP ON P.CdRango=RP.IdRango
 LEFT JOIN ConcDiversos AS C ON D.IdConcepto=C.IdConcepto LEFT JOIN PeajesClase AS PC ON D.CdClase=PC.IdClase
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN TiposOperac AS TP ON D.CdTipOper=TP.IdTipoOper
 LEFT JOIN Terceros AS CL ON P.NitCliente=CL.IdTercero LEFT JOIN Mercancias AS MC ON P.CdMercancia=MC.IdMercancia
 LEFT JOIN TiposCar AS TCA ON P.CdTipoCarr=TCA.IdCrceria
 WHERE IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND IdDestino LIKE ISNULL(@pmIdDestino,'%') AND TipoAfilVeh LIKE ISNULL(@pmTipoAfilVeh,'%')
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY P.NumPsto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraPresAntFmt] @pmNumPstoIni INT,@pmNumPstoFin INT
 AS 
 SELECT P.NumPsto AS NumPrespto,Fecha,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TipoRuta,CdRuta,Ruta
,FecInicio,FecFinal,VrGastos,VrComb,VrPeajes,VrAnticipo,CdCat,Catpeaje,CdTipoVeh,TipoVehiculo,TipoAfilVeh,CdTipoMot,TipoMotor
,CdCom,TipoComb,CdRango,DescripRango,ValorFijo,P.Observacion,P.Inactivo AS Inactvo,NitCliente,CL.RazonSocial AS Cliente,CdMercancia,DescripMcia
,CdTipoCarr,TipoCar,P.TipoServicio,Cantgalones,VrExtralegal,FechaCrea,FechaAct,P.IdUsuario AS CdUsuario,Usuario
,D.IdConcepto AS CdConcepto,Concepto,Descripcion,Cantidad,VrUnitario,VrTotal,TipoRubro,CdClase,ClasePeaje,NitTercero,T.RazonSocial,CdTipOper,TipoOperacion
 FROM Trn_TraPresAnt AS P INNER JOIN Trn_TraPresItems AS D ON P.NumPsto=D.NumPsto
 INNER JOIN Localidades AS CO ON  P.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Rutas AS R ON P.CdRuta=R.IdRuta LEFT JOIN TiposVeh AS TV ON P.CdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN PeajesCat AS CP ON P.CdCat=CP.IdCat LEFT JOIN TiposMot AS TM ON P.CdTipoMot=TM.IdTipoMot
 LEFT JOIN TiposFuel AS TF ON P.CdCom=TF.IdCom LEFT JOIN RangosPeso AS RP ON P.CdRango=RP.IdRango
 LEFT JOIN ConcDiversos AS C ON D.IdConcepto=C.IdConcepto LEFT JOIN PeajesClase AS PC ON D.CdClase=PC.IdClase
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN TiposOperac AS TP ON D.CdTipOper=TP.IdTipoOper
 LEFT JOIN Terceros AS CL ON P.NitCliente=CL.IdTercero LEFT JOIN Mercancias AS MC ON P.CdMercancia=MC.IdMercancia
 LEFT JOIN TiposCar AS TCA ON P.CdTipoCarr=TCA.IdCrceria
 WHERE P.NumPsto BETWEEN @pmNumPstoIni AND @pmNumPstoFin
 ORDER BY P.NumPsto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPresAnt] @pmNumPsto INT
 AS 
 SELECT NumPsto,Fecha,IdOrigen,IdDestino,TipoRuta,CdRuta,FecInicio,FecFinal,VrGastos,VrComb,VrPeajes,VrAnticipo,CdCat,CdTipoVeh
,TipoAfilVeh,CdTipoMot,CdCom,CdRango,ValorFijo,Observacion,Inactivo,NitCliente,CdMercancia,TipoServicio,Cantgalones,VrExtralegal,CdTipoCarr
,FechaCrea,FechaAct,IdUsuario
 FROM Trn_TraPresAnt
 WHERE NumPsto=@pmNumPsto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraPresAnt] @pmNumPsto INT,@pmFecha SMALLDATETIME,@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmVrGastos MONEY,@pmVrComb MONEY,@pmVrPeajes MONEY,@pmVrAnticipo MONEY,@pmCdCat VARCHAR(4)
,@pmCdTipoVeh VARCHAR(4),@pmTipoAfilVeh VARCHAR(10),@pmCdTipoMot VARCHAR(4),@pmCdCom VARCHAR(4),@pmCdRango VARCHAR(4),@pmValorFijo BIT,@pmObservacion VARCHAR(250),@pmInactivo BIT,@pmTipoRuta VARCHAR(10),@pmCdRuta  VARCHAR(4)
,@pmNitCliente VARCHAR(16),@pmCdMercancia VARCHAR(16),@pmTipoServicio INT,@pmCantgalones DECIMAL(14,4),@pmVrExtralegal MONEY,@pmCdTipoCarr VARCHAR(4),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraPresAnt (NumPsto,Fecha,IdOrigen,IdDestino,TipoRuta,CdRuta,FecInicio,FecFinal,VrGastos,VrComb,VrPeajes,VrAnticipo,CdCat,CdTipoVeh,TipoAfilVeh,CdTipoMot,CdCom,CdRango,ValorFijo,Observacion,Inactivo,FechaCrea,IdUsuario,NitCliente,CdMercancia,TipoServicio,Cantgalones,VrExtralegal,CdTipoCarr)
 VALUES (@pmNumPsto,@pmFecha,@pmIdOrigen,@pmIdDestino,@pmTipoRuta,@pmCdRuta,@pmFecInicio,@pmFecFinal,@pmVrGastos,@pmVrComb,@pmVrPeajes,@pmVrAnticipo,@pmCdCat,@pmCdTipoVeh,@pmTipoAfilVeh,@pmCdTipoMot,@pmCdCom
,@pmCdRango,@pmValorFijo,@pmObservacion,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmNitCliente,@pmCdMercancia,@pmTipoServicio,@pmCantgalones,@pmVrExtralegal,@pmCdTipoCarr)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraPresAnt] @pmNumPsto INT,@pmFecha SMALLDATETIME,@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME
,@pmVrGastos MONEY,@pmVrComb MONEY,@pmVrPeajes MONEY,@pmVrAnticipo MONEY,@pmCdCat VARCHAR(4),@pmCdTipoVeh VARCHAR(4),@pmTipoAfilVeh VARCHAR(10),@pmCdTipoMot VARCHAR(4)
,@pmCdCom VARCHAR(4),@pmCdRango VARCHAR(4),@pmValorFijo BIT,@pmObservacion VARCHAR(250),@pmInactivo BIT,@pmTipoRuta VARCHAR(10),@pmCdRuta  VARCHAR(4),@pmNitCliente VARCHAR(16)
,@pmCdMercancia VARCHAR(16),@pmTipoServicio INT,@pmCantgalones DECIMAL(14,4),@pmVrExtralegal MONEY,@pmCdTipoCarr VARCHAR(4),@pmFechaAct SMALLDATETIME 
 AS 
 UPDATE Trn_TraPresAnt SET Fecha=@pmFecha,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,FecInicio=@pmFecInicio,FecFinal=@pmFecFinal,VrGastos=@pmVrGastos,VrComb=@pmVrComb,VrPeajes=@pmVrPeajes
,VrAnticipo=@pmVrAnticipo,CdCat=@pmCdCat,CdTipoVeh=@pmCdTipoVeh,TipoAfilVeh=@pmTipoAfilVeh,CdTipoMot=@pmCdTipoMot,CdCom=@pmCdCom,CdRango=@pmCdRango,ValorFijo=@pmValorFijo
,Observacion=@pmObservacion,Inactivo=@pmInactivo,TipoRuta=@pmTipoRuta,CdRuta=@pmCdRuta,FechaAct=@pmFechaAct,NitCliente=@pmNitCliente,CdMercancia=@pmCdMercancia,TipoServicio=@pmTipoServicio
,Cantgalones=@pmCantgalones,VrExtralegal=@pmVrExtralegal,CdTipoCarr=@pmCdTipoCarr
 WHERE NumPsto=@pmNumPsto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsUm] @pmUndMed VARCHAR(10),@pmUnidad VARCHAR(50),@pmCarga BIT,@pmCodUMFE VARCHAR(20)
 AS 
 INSERT INTO Sys_Um (UndMed,Unidad,Carga,CodUMFE)
 VALUES (@pmUndMed,@pmUnidad,@pmCarga,@pmCodUMFE)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON 
GO
CREATE PROCEDURE [dbo].[paUpUm] @pmUndMed VARCHAR(10),@pmUnidad VARCHAR(50),@pmCarga BIT,@pmCodUMFE VARCHAR(20)
 AS 
 UPDATE Sys_Um SET Unidad=@pmUnidad,Carga=@pmCarga,CodUMFE=@pmCodUMFE
 WHERE UndMed=@pmUndMed
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryUm] @pmUndMed VARCHAR(10)
 AS 
 SELECT UndMed,Unidad,Carga,CodUMFE
 FROM Sys_Um
 WHERE UndMed LIKE ISNULL(@pmUndMed,'%')
 ORDER BY UndMed
GO