if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelEdsUnion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelEdsUnion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuCuentasAux_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuCuentasAux_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasAux_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasAux_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasAux_San]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasAux_San]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEdsUnion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEdsUnion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsUnion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEdsUnion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsUnion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEdsUnion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEdsUnion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEdsUnion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayLiqTotales]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMayLiqTotales]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayLiqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMayLiqDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqDetalleLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqDetalleLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesTot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesTot]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayLiqTotalesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMayLiqTotalesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraManifAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraManifAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraRemAnexo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEdsUnion_Cr] @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT N.TipDoc AS Tip_Doc,TipoDoc,N.NumNota,N.IdCia AS CdCia,Compania,N.Fecha,N.CdTanque AS TanqNuevo,T.IdProducto,T.Descripcion,N.Capacidad,N.Cantidad,N.NivAgua
,N.TipoTrasl,N.NumTraslado,N.Anulado,N.FecDev,N.Observacion AS Observ,N.TimeSys AS Fec_Add,N.FecUpdate AS Fec_Update,N.IdCiaCrea,N.IdUsuario AS CdUsuario,U.Usuario AS NomUsuario,N.OrigenAdd
,D.Item,D.IdTanque AS CdTanque,TQ.IdProducto AS CdProducto,TQ.Descripcion AS TanDescripcion,D.Capacidad AS TanCapacidad,D.NivelAgua AS TanNivelAgua,D.Cantidad AS SaldoActual
 FROM Trn_EdsUnion AS N INNER JOIN Trn_EdsUnionItems 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 Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN Tanques AS TQ ON D.IdTanque=TQ.IdTanque
 LEFT JOIN Tanques AS T ON N.CdTanque=T.IdTanque
 WHERE N.TipDoc=@pmTipDoc AND N.NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuCuentasAux_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuCuentasAux (nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos,TipoAfiVehic)
 SELECT @pmnAnno,@pmnMes,S.IdCia,0,S.IdCuenta,S.IdTercero,S.CodAgncia,S.IdCCosto,S.IdSubCos,S.SubAuxiliar,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0,S.TipoAfiVehic
 FROM AcuCuentasAux AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuCuentasAux AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta
 AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos
 AND AC.SubAuxiliar=S.SubAuxiliar AND AC.TipoAcum=S.TipoAcum AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasAux_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuNiifCuentasAux (nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos,TipoAfiVehic)
 SELECT @pmnAnno,@pmnMes,S.IdCia,0,S.IdCuenta,S.IdTercero,S.CodAgncia,S.IdCCosto,S.IdSubCos,S.SubAuxiliar,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0,S.TipoAfiVehic
 FROM AcuNiifCuentasAux AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuNiifCuentasAux AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes 
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos
 AND AC.SubAuxiliar=S.SubAuxiliar AND AC.TipoAcum=S.TipoAcum AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasAux_San] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT
,@pmTipoAcum VARCHAR(4),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
AS 
 INSERT INTO AcuFisCuentasAux (nAnno,nMes,IdCia,nReg,IdCuenta,IdTercero,CodAgncia,IdCCosto,IdSubCos,SubAuxiliar,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos,TipoAfiVehic)
 SELECT @pmnAnno,@pmnMes,S.IdCia,0,S.IdCuenta,S.IdTercero,S.CodAgncia,S.IdCCosto,S.IdSubCos,S.SubAuxiliar,S.TipoAcum,S.SaldoAnterior+S.TotalDebitos-S.TotalCreditos,0,0,S.TipoAfiVehic
 FROM AcuFisCuentasAux AS S WHERE S.nAnno=@pmAnnoAnt AND S.nMes=@pmMesAnt AND S.TipoAcum=@pmTipoAcum
 AND NOT EXISTS (SELECT * FROM AcuFisCuentasAux AS AC WHERE AC.nAnno=@pmnAnno AND AC.nMes=@pmnMes 
 AND AC.IdCia=S.IdCia AND AC.IdCuenta=S.IdCuenta AND AC.IdTercero=S.IdTercero AND AC.CodAgncia=S.CodAgncia AND AC.IdCCosto=S.IdCCosto AND AC.IdSubCos=S.IdSubCos
 AND AC.SubAuxiliar=S.SubAuxiliar AND AC.TipoAcum=S.TipoAcum AND AC.TipoAfiVehic=S.TipoAfiVehic)
 AND (@pmIdCia IS NULL OR S.IdCia=@pmIdCia) AND (@pmIdCuenta IS NULL OR S.IdCuenta=@pmIdCuenta)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEdsUnion] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumNota,IdCia,Fecha,CdTanque,Capacidad,Cantidad,NivAgua,TipoTrasl,NumTraslado,OrigenAdd,Anulado,FecDev,Observacion,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_EdsUnion
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelEdsUnion] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_EdsUnion WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsEdsUnion] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmCdTanque VARCHAR(4),@pmCapacidad DECIMAL(14,4),@pmCantidad DECIMAL(14,4),@pmNivAgua DECIMAL(14,4)
,@pmTipoTrasl VARCHAR(3),@pmNumTraslado INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_EdsUnion (TipDoc,NumNota,IdCia,Fecha,CdTanque,Capacidad,Cantidad,NivAgua,TipoTrasl,NumTraslado,OrigenAdd,Anulado,FecDev,Observacion,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmCdTanque,@pmCapacidad,@pmCantidad,@pmNivAgua,@pmTipoTrasl,@pmNumTraslado,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpEdsUnion] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmCdTanque VARCHAR(4),@pmCapacidad DECIMAL(14,4),@pmCantidad DECIMAL(14,4),@pmNivAgua DECIMAL(14,4)
,@pmTipoTrasl VARCHAR(3),@pmNumTraslado INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_EdsUnion SET Fecha=@pmFecha,CdTanque=@pmCdTanque,Capacidad=@pmCapacidad,Cantidad=@pmCantidad,NivAgua=@pmNivAgua,TipoTrasl=@pmTipoTrasl,NumTraslado=@pmNumTraslado
,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesLta] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT T.NContrato AS NumContrato,IdCiaCon,T.IdCliente AS NitCliente,TC.RazonSocial AS NombreCliente,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,BaseIvaBonif,VrIvaBonif,VrBonifNeto,VrAbono,T.DiasGracia AS Dias_Gracia,L.Fecha AS FechaLiq,Item,LogLiquida
--datos del contrato
,CN.Fecha AS FechaContrato,FechaInicio,FechaVence,CN.IdTipo AS CdTipo,TipContrato,CN.IdBonif AS CdBonif,TipoBonif
,CN.IdVend AS CdVend,V.RazonSocial AS Vendedor,CN.IdBandera AS CdBandera,TipoBandera
,TipCom,Comprobante,IdCiaCom,L.Observacion AS Observ,TipoNcr,NotaCred,IdCiaNot,VrCuotaFija
--datos del cliente
,TC.TipoId AS TipIdClie,TC.Dv AS DvCliente,TC.NomCial AS NomCialClie,TC.SiglaRaz AS SiglaClie,TC.IdLocal AS CdCiuClie,LT.Localidad AS CiudadCliente
,DP.Departamento AS DptoClie,A.IdLocal AS CdCiuAge,LA.Localidad AS CiudadAgencia,DA.Departamento AS DptoAgencia
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 INNER JOIN Localidades AS LT ON TC.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DP ON LT.IdDep=DP.IdDep 
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS V ON CN.IdVend=V.IdTercero
 LEFT JOIN  MayTipos AS TCN ON CN.IdTipo=TCN.IdTipo
 LEFT JOIN  MayTiposBonif AS TB ON CN.IdBonif=TB.IdBonif
 LEFT JOIN  TiposBan AS B ON CN.IdBandera=B.IdBandera
 WHERE L.NLiquida=@pmNLiquida AND L.IdCia=@pmIdCia 
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMayLiqDetalle] @pmNLiquida INT,@pmIdCia CHAR(2),@pmNContrato INT,@pmIdCiaCon CHAR(2)
,@pmItem INT,@pmIdSubgrupo VARCHAR(8),@pmCombustible BIT,@pmUnidades DECIMAL(14,4),@pmUnidDev DECIMAL(14,4)
,@pmValorTotal MONEY,@pmValorDev MONEY,@pmIntervalo VARCHAR(50),@pmMargen DECIMAL(14,4)
,@pmTarifa DECIMAL(14,4),@pmTipoTarifa CHAR(1),@pmVrLiquida MONEY,@pmLogDetalle VARCHAR(250),@pmSubsidio  DECIMAL(14,4)
,@pmVenGalones DECIMAL(14,4),@pmVenTotal MONEY,@pmCantExcluido DECIMAL(14,4),@pmMgnNeto DECIMAL(14,4),@pmTipoMargen CHAR(1)
,@pmPorcIvaCom DECIMAL(14,4),@pmVrIvaCom DECIMAL(16,4),@pmIvaIncBonif BIT,@pmCdAgenc VARCHAR(16),@pmVrBaseIva DECIMAL(16,4),@pmVrLiquidaNeto MONEY
 AS 
 INSERT INTO Trn_MayLiqDetalle (NLiquida,IdCia,NContrato,IdCiaCon,Item,IdSubgrupo,Combustible,Unidades,UnidDev,ValorTotal
,ValorDev,Intervalo,Margen,Tarifa,TipoTarifa,VrLiquida,LogDetalle,Subsidio,VenGalones,VenTotal,CantExcluido,MgnNeto,TipoMargen
,PorcIvaCom,VrIvaCom,IvaIncBonif,CdAgenc,VrBaseIva,VrLiquidaNeto)
 VALUES (@pmNLiquida,@pmIdCia,@pmNContrato,@pmIdCiaCon,@pmItem,@pmIdSubgrupo,@pmCombustible,@pmUnidades
,@pmUnidDev,@pmValorTotal,@pmValorDev,@pmIntervalo,@pmMargen,@pmTarifa,@pmTipoTarifa,@pmVrLiquida
,@pmLogDetalle,@pmSubsidio,@pmVenGalones,@pmVenTotal,@pmCantExcluido,@pmMgnNeto,@pmTipoMargen,@pmPorcIvaCom,@pmVrIvaCom,@pmIvaIncBonif,@pmCdAgenc,@pmVrBaseIva,@pmVrLiquidaNeto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayLiqDetalleLta] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.NContrato AS NumContrato,IdCiaCon,Item,D.IdSubgrupo AS CdSubgrupo,Subgrupo,Combustible,VenGalones,Unidades,UnidDev
,Unidades-UnidDev AS UnidNeto,VenTotal,ValorTotal,ValorDev,ValorTotal-ValorDev AS VrTotal,Intervalo,TipoTarifa,Tarifa,VrLiquida
,VrBaseIva,PorcIvaCom,VrIvaCom,IvaIncBonif,TipoMargen,Margen,Subsidio,MgnNeto,LogDetalle,CantExcluido,FechaInicio
,IdCliente,RazonSocial,D.CdAgenc AS Id_Agencia,Agencia
 FROM Trn_MayLiqDetalle AS D  INNER JOIN Trn_MayContratos AS CN ON D.NContrato=CN.NContrato AND D.IdCiaCon=CN.IdCia
 INNER JOIN  SubGrupos AS S ON D.IdSubgrupo=S.IdSubgrupo
 INNER JOIN Terceros AS T ON CN.IdCliente=T.IdTercero
 LEFT JOIN Agencias AS A ON D.CdAgenc=A.IdAgencia
 WHERE NLiquida=@pmNLiquida AND D.IdCia=@pmIdCia 
 ORDER BY D.NContrato,IdCiaCon,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMayLiqTotales] @pmNLiquida INT,@pmIdCia CHAR(2),@pmItem INT,@pmNContrato INT,@pmIdCiaCon CHAR(2)
,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmUnidades DECIMAL(14,4),@pmVrBonif MONEY,@pmVrAbono MONEY
,@pmVrCuotaFija MONEY,@pmDiasGracia INT,@pmTipoNcr VARCHAR(3),@pmNotaCred INT,@pmIdCiaNot CHAR(2),@pmLogLiquida VARCHAR(250)
,@pmVrIvaBonif DECIMAL(16,4),@pmBaseIvaBonif DECIMAL(16,4),@pmVrBonifNeto MONEY
 AS 
 INSERT INTO Trn_MayLiqTotales (NLiquida,IdCia,Item,NContrato,IdCiaCon,IdCliente,IdAgencia,Unidades,VrBonif,VrAbono
,VrCuotaFija,DiasGracia,TipoNcr,NotaCred,IdCiaNot,LogLiquida,VrIvaBonif,BaseIvaBonif,VrBonifNeto)
 VALUES (@pmNLiquida,@pmIdCia,@pmItem,@pmNContrato,@pmIdCiaCon,@pmIdCliente,@pmIdAgencia,@pmUnidades
,@pmVrBonif,@pmVrAbono,@pmVrCuotaFija,@pmDiasGracia,@pmTipoNcr,@pmNotaCred,@pmIdCiaNot,@pmLogLiquida,@pmVrIvaBonif,@pmBaseIvaBonif,@pmVrBonifNeto)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesTot] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT SUM(Unidades) AS STOTUNI,SUM(VrBonif) AS STOTBON,SUM(VrAbono) AS STOTABO
,SUM(VrCuotaFija) AS STOTFIJ,SUM(VrIvaBonif) AS STOTIVA,SUM(BaseIvaBonif) AS STOTBASE
,SUM(VrBonifNeto) AS STOTNETO
 FROM  Trn_MayLiqTotales
 WHERE NLiquida=@pmNLiquida AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMayLiqTotalesLog] @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,T.NContrato AS NumContrato,IdCiaCon,T.IdCliente AS NitCliente,RazonSocial,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,VrAbono,VrCuotaFija,T.DiasGracia AS Dias_Gracia,NotaCred,IdCiaNot,LogLiquida,L.Fecha AS FechaLiq
,IdTipo,IdBonif,CN.IdVend AS CdVend,TC.IdLocal AS CdCiuClie,A.IdLocal AS CdCiuAge,CdCCBonif,CdSubCCBonif,BaseIvaBonif,VrIvaBonif
,CN.ClaseContrato,VrBonifNeto
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 WHERE L.NLiquida=@pmNLiquida AND L.IdCia=@pmIdCia AND Estado=0
 AND (VrBonif<>0 OR VrCuotaFija>0) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Remesa AS NumRemesa,M.IdCiaRem,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.Anulado,M.FecDev
,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,PuntosRuta,MA.CantViajes,VolumenCarga
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp,CdCondRelev,CRV.RazonSocial AS SegConductor
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
--Condiciones consulta general
 WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipDoc,TipoDoc,R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,IdLocOrigen,CO.Localidad AS CiuOrigen,IdLocDestino,CD.Localidad AS CiuDestino
,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue
,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal,Items,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed
,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,Concepto,CdRutaTarif,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario,Leyenda
,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,RA.CantViajes
,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,CdBodega,Bodega
,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue
--detalle mcias
,Item,D.IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,Cases,Cajas,Palets,NitRemite,Remitente
,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,TarifClie,TarifPago,TarifTabla,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,DA.Agencia AS DetAgencia,Cumplido,IdCiaCump,FechaCump,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp,PesoCont,TipDocInv,NumDocInv,IdCiaDocInv,ItemDocInv
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango,CodigoUN
--Datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraRemesa AS R  INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON R.TipDoc=D.TipDoc  AND R.NumOrden=D.NumOrden AND R.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc  INNER JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal
 INNER JOIN Localidades AS CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia INNER JOIN Mercancias AS MC ON D.IdMercancia=MC.IdMercancia
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango
 LEFT JOIN  Conceptos AS C ON R.CdConcepto=C.IdConcepto LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom
 LEFT JOIN AlmBodegas AS BG ON R.CdBodega=BG.IdBodega
 LEFT JOIN TercCndtores AS CT ON R.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 WHERE R.TipDoc=@pmTipDoc AND R.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND R.IdCia=@pmIdCia
 ORDER BY R.NumOrden
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoFmt] @pmTipDoc VARCHAR(3),@pmCumplidoIni INT,@pmCumplidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT CU.TipDoc AS TipCum,TipoDoc,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CU.Observacion AS Observ
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.IdEstado AS CdEstado,Estado,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario,Leyenda
,M.Fecha AS FecManif,FecDespacho,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS Observ,TipoComp AS CdTipComp,NumComp AS Comprobante
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,TarifFlete,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario,TipoCumRemesa,MotivoSuspRem,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue
--Datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON CU.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta 
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc  AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia  AND D.ItemRem=RM.Item
 WHERE CU.TipDoc=@pmTipDoc AND CU.Cumplido BETWEEN @pmCumplidoIni AND @pmCumplidoFin AND CU.IdCia=@pmIdCia
 ORDER BY CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoFmt] @pmTipDoc VARCHAR(3),@pmManifiestoIni INT,@pmManifiestoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc AS CdTipDoc,TipoDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,CN.Compania AS NomCompania,M.Fecha AS FechaMuc,M.FecDespacho,M.FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,RT.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,M.nRemolque,M.TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,BaseRet,TarifaRet,TarifaIca
,M.IdLocFletes,CF.Localidad AS CiuLugarFletes,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,M.EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.OrigenAdd,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom
,M.TimeSys,M.FecUpdate,M.IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario,Leyenda,kmsTotal,VrFleteTon,MA.TipoRuta AS MucTipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,MA.IdMneda,MA.VrTasa,MA.NomRemite AS NomRmtente,MA.NomDestino AS NomDestnatario
,MA.LugarFletes AS Lugar_Fletes,NumAnticipo,NumCheque,MA.TipoMintrans AS TipoMucMintrans,MucMintrans,ContIntegral,VolumenCarga,MA.NumPoliza AS NumPolizaMuc,DescFirma,CodEmpresa,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,MA.MvoAnulacion,MvoSuspension,PuntosRuta,MA.CantViajes
--detalles
,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol
,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,D.IdOrigen AS RemCodOrigen,LO.Localidad AS RemCiudadOrigen,LO.IdDep AS RemCdepOrigen,DOR.Departamento AS RemDptoOrigen,NitDestntario,Destinatario,DirDestino
,D.IdDestino AS RemCodDestino,LD.Localidad AS RemCiudadDestino,LD.IdDep AS RemCdepDestino,DDN.Departamento AS RemDptoDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro
,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,DA.Agencia AS DetAgencia
,D.Cumplido AS RemCumplido,D.IdCiaCump AS RemCiaCump,D.FechaCump AS RemFechaCum,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS RemTipoOdp,D.NumeroOdp AS RemNumOdp,D.IdCiaOdp AS RemCiaOdp,TarifOdp,PesoCont,MR.RemMintrans AS RemMucMintrans,EdoRemMin
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango,CodBodDtno,TipTraslado,CodigoUN
,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,TiempoCargue,TiempoDesc,FecLlegCargue,FecSalidaCargue,FecLlegdesc,FecSalidadesc
,IdCliente,CL.RazonSocial AS RemNomCliente,R.IdClieFact AS RemIdPropMcia,PM.RazonSocial AS RemPropMcia
--Datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,V.Modelo,V.Config
,V.PesoVacio,V.PesoMax,V.NumMotor,V.SerieChasis,V.NumSerie,V.CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,RQ.PesoKg AS PesoVacioRq
,CT.CertJudicial,CT.FecJudicial,CT.VigJudicial,CT.Licencia,CT.CatLicencia,CT.IdLugar AS CdLugar,EL.LugarLic,CT.FecLicencia,CT.VigLicencia,CDT.Direccion AS CdtDireccion,CDT.Telefono AS CdtTelefono,CDT.e_mail AS CdtEmail
--Datos adicionales de conductor 
,CDT.TelMovil AS CdtMovil,LC.Localidad AS CdtCiudad, DC.Departamento AS CdtDpto
--Datos Conductor relevo
,CdCondRelev,CRV.RazonSocial AS SegConductor,CTR.CertJudicial  AS CertJudicialRelev,CTR.FecJudicial AS FecJudicialRelev,CTR.VigJudicial AS VigJudicialRelev,CTR.Licencia AS LicenciaRelev,CTR.CatLicencia AS CatLicenciaRelev
,CTR.IdLugar AS CdLugarRelev,ELR.LugarLic AS LugarLicRelev,CTR.FecLicencia AS FecLicenciaRelev,CTR.VigLicencia AS VigLicenciaRelev,CRV.Direccion AS CdtDireccionRelev,CRV.Telefono AS CdtTelefonoRelev,CRV.e_mail AS CdtEmailRelev
,CRV.TelMovil AS CdtMovilRelev,LCR.Localidad AS CdtCiudadRelev, DCR.Departamento AS CdtDptoRelev
 FROM Trn_TraManifiesto AS M INNER JOIN  Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Trn_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND  M.Manifiesto=MR.Manifiesto AND  M.IdCia=MR.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON MR.TipRem=R.TipDoc AND MR.Remesa=R.NumOrden AND MR.IdCiaRem=R.IdCia 
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS RT ON M.IdRuta=RT.IdRuta  INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 -- Se agregan tablas para informacion adicional del conductor
 INNER JOIN Localidades AS LC ON CDT.IdLocal =LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep =DC.IdDep
 ------
 INNER JOIN Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DOR ON LO.IdDep=DOR.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DDN ON LD.IdDep=DDN.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia 
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS COL ON V.IdColor=COL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Terceros AS CL ON R.IdCliente=CL.IdTercero LEFT JOIN Terceros AS PM ON R.IdClieFact=PM.IdTercero
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia  LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango    LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta
 LEFT JOIN Polizas AS PS ON MA.NumPoliza=PS.NumPoliza
 LEFT JOIN Trn_TraRemAnexo AS RA ON MR.TipRem=RA.TipDoc AND MR.Remesa=RA.NumOrden AND MR.IdCiaRem=RA.IdCia
 LEFT JOIN VehRemolq AS RQ ON M.nRemolque=RQ.IdRemque
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN Localidades AS LCR ON CRV.IdLocal=LCR.IdLocal LEFT JOIN Departamentos AS DCR ON LCR.IdDep=DCR.IdDep
 LEFT JOIN TercCndtores AS CTR ON M.CdCondRelev=CTR.IdConductor LEFT JOIN ExpLicencias AS ELR ON CTR.IdLugar=ELR.IdLugar
 WHERE M.TipDoc=@pmTipDoc AND M.Manifiesto BETWEEN @pmManifiestoIni AND @pmManifiestoFin AND M.IdCia=@pmIdCia
 ORDER BY M.Manifiesto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,IdOrigen,LO.Localidad AS CiudadOrigen,IdDestino,LD.Localidad AS CiudadDestino,IdRuta,IdRutaFle,M.IdVehiculo AS PlacaVeh,NumVeh
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,Cantidad,PesoTotal,VolumenCarga,BaseRet,TarifaRet,TarifaIca,CodTarRet
,CodTarIca,IdLocFletes,LugarFletes,FechaPago,PagoCargue,PagoDescargue,NomRemite,NomDestino,NumMintrans,EdoMintrans,Remesa,IdCiaRem,Cumplido,IdCiaCump,FechaCump,EstCumplido
,TipOdp,OrdPago,IdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,NumAnticipo,NumCheque,TipoMintrans,TipoRuta,MucMintrans,PuntosRuta,CdCondRelev,A.CantViajes
,OrigenAdd,Anulado,FecDev,TipCom,Comprobante,IdCiaCom,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS A ON M.TipDoc=A.TipDoc AND M.Manifiesto=A.Manifiesto AND M.IdCia=A.IdCia
 INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN Companias AS CI ON M.IdCia=CI.IdCia
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero  INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 WHERE M.TipDoc=@pmTipDoc AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdRuta LIKE ISNULL(@pmIdRuta,'%')  AND IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND IdDestino LIKE ISNULL(@pmIdDestino,'%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY M.IdCia,M.Manifiesto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,Anulado,FecDev,Observacion,IdEstado
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CdRuta,CdOrigen,CdDestino,TipoComp,NumComp,CodConcepto
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue
,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum
 FROM Trn_TraCumplido
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FecCumplido,C.Manifiesto AS NumManif,IdCiaMuc,M.Fecha AS FecManif,C.IdVehiculo AS PlacaVeh,Modalidad
,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic
,C.Anulado AS CumAnulado,C.FecDev AS FechAnulado,TipoComp,NumComp,CodConcepto,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,IdLocFletes,LP.Localidad AS LugarPago,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MA.TipoRuta,MvoAnulaCump,ObservAnulado,NumViajesCum,MA.MucMintrans AS TipoViaje
 FROM Trn_TraCumplido AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON C.TipMuc=MA.TipDoc AND C.Manifiesto=MA.Manifiesto AND C.IdCiaMuc=MA.IdCia
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Localidades AS CO ON C.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta LEFT JOIN Localidades AS LP ON M.IdLocFletes=LP.IdLocal 
 WHERE C.TipDoc=@pmTipDoc  AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY C.IdCia,C.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraCumplido SET Fecha=@pmFecha,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,IdVehiculo=@pmIdVehiculo,Modalidad=@pmModalidad,DiasPlazo=@pmDiasPlazo,FecPago=@pmFecPago,TipoMargen=@pmTipoMargen
,MargenFalt=@pmMargenFalt,UndCalcFalt=@pmUndCalcFalt,TarifFaltPago=@pmTarifFaltPago,TarifFaltCobro=@pmTarifFaltCobro,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,NRadicaDoc=@pmNRadicaDoc,IdCiaRadic=@pmIdCiaRadic,CdCiaOfic=@pmCdCiaOfic,FecRadic=@pmFecRadic,CdRuta=@pmCdRuta,CdOrigen=@pmCdOrigen,CdDestino=@pmCdDestino,TipoComp=@pmTipoComp,NumComp=@pmNumComp,CodConcepto=@pmCodConcepto,FecUpdate=@pmFecUpdate
,TipoCumpMT=@pmTipoCumpMT,MotivoSusp=@pmMotivoSusp,ConsecSusp=@pmConsecSusp,VrAdicCargue=@pmVrAdicCargue,VrAdicDescargue=@pmVrAdicDescargue,VrAdicFlete=@pmVrAdicFlete,MotivoVrAdic=@pmMotivoVrAdic,VrDctoFlete=@pmVrDctoFlete
,MotivoVrDcto=@pmMotivoVrDcto,VrAdicAnticipo=@pmVrAdicAnticipo,FecEntregaDoc=@pmFecEntregaDoc,MvoAnulaCump=@pmMvoAnulaCump,ObservAnulado=@pmObservAnulado,NumViajesCum=@pmNumViajesCum
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraCumplido (TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CdRuta,CdOrigen,CdDestino
,Anulado,FecDev,TipoComp,NumComp,CodConcepto,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum)
 VALUES (@pmTipDoc,@pmCumplido,@pmIdCia,@pmFecha,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmModalidad,@pmDiasPlazo,@pmFecPago,@pmTipoMargen,@pmMargenFalt,@pmUndCalcFalt,@pmTarifFaltPago,@pmTarifFaltCobro,@pmNRadicaDoc,@pmIdCiaRadic,@pmCdCiaOfic,@pmFecRadic
,@pmCdRuta,@pmCdOrigen,@pmCdDestino,@pmAnulado,@pmFecDev,@pmTipoComp,@pmNumComp,@pmCodConcepto,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoCumpMT,@pmMotivoSusp,@pmConsecSusp,@pmVrAdicCargue,@pmVrAdicDescargue,@pmVrAdicFlete
,@pmMotivoVrAdic,@pmVrDctoFlete,@pmMotivoVrDcto,@pmVrAdicAnticipo,@pmFecEntregaDoc,0,@pmMvoAnulaCump,@pmObservAnulado,@pmNumViajesCum)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null,@pmIdVehiculo VARCHAR(10)=Null
 AS 
 SELECT R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Fecha,HoraLlegada,FecDespacho,FecEntrega,IdCliente,NomCliente,R.IdAgencia AS CdAgencia,Agencia,IdClieFact,CF.RazonSocial AS ClienteFact,IdRemitente,NomRemite,IdLocOrigen,LO.Localidad AS CiudadOrigen,LugarCargue
,NomContacto,TelContacto,emlContacto,IdDestinatario,NomDestino,IdLocDestino,LD.Localidad AS CiudadDestino,LugarDescargue,ContacDestino,TelContacDest,emlContacDest,IdLocFletes,LF.Localidad AS LugarFletes
,IdVehiculo,nRemolque,TipoAfiVehic,IdConductor,NC.RazonSocial AS Conductor,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,VrDeclarado,VrSeguro,Cantidad,PesoTotal,Items,IdMneda,VrTasa,RA.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura
,CdConcepto,CdRutaTarif,CdBodega,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,VigenciaOdc,RA.CantViajes,PactoCargue,HorasPactoCargue,MinPactoCargue
,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraRemesa AS R  INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 INNER JOIN Localidades AS LO ON R.IdLocOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON R.IdLocDestino=LD.IdLocal
 INNER JOIN Localidades AS LF ON R.IdLocFletes=LF.IdLocal  INNER JOIN Terceros AS NC ON R.IdConductor=NC.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero LEFT JOIN Terceros AS CF ON R.IdClieFact=CF.IdTercero
 WHERE R.TipDoc=@pmTipDoc AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 ORDER BY R.IdCia,R.NumOrden
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,IdVend,IdTarifCom,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino
,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv
,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,MvoAnulacion,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TipoDevRemesa,CodCiuTransb,MvoTransbordo,CantViajes
 FROM Trn_TraRemAnexo
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmIdVend VARCHAR(16),@pmIdTarifCom VARCHAR(4),@pmNomCliente VARCHAR(150),@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarCargue VARCHAR(150),@pmLugarDescargue VARCHAR(150)
,@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContacDestino VARCHAR(150),@pmTelContacDest VARCHAR(30),@pmemlContacDest VARCHAR(100),@pmPolizaSeguro VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME
,@pmManifMintrans DECIMAL(14,2),@pmRemMintrans DECIMAL(14,2),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTomadorPoliza CHAR(1),@pmTipoRuta VARCHAR(10),@pmTipoMintrans VARCHAR(10),@pmNitEmpTransp VARCHAR(16),@pmNumFactEmp VARCHAR(20)
,@pmMvoAnulacion VARCHAR(3),@pmPactoCargue BIT,@pmHorasPactoCargue INT,@pmMinPactoCargue INT,@pmHoraCitaCargue SMALLDATETIME,@pmHoraLlegaCargue SMALLDATETIME,@pmHoraEntraCargue SMALLDATETIME,@pmHoraSaleCargue SMALLDATETIME
,@pmPactoDescargue BIT,@pmHorasPactoDescargue INT,@pmMinPactoDescargue INT,@pmHoraCitaDescargue SMALLDATETIME,@pmHoraLlegaDescargue SMALLDATETIME,@pmHoraEntraDescargue SMALLDATETIME,@pmHoraSaleDescargue SMALLDATETIME
,@pmTipoDevRemesa VARCHAR(3),@pmCodCiuTransb VARCHAR(8),@pmMvoTransbordo VARCHAR(3),@pmCantViajes INT
 AS 
 INSERT INTO Trn_TraRemAnexo (TipDoc,NumOrden,IdCia,IdVend,IdTarifCom,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans
,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,MvoAnulacion,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TipoDevRemesa,CodCiuTransb,MvoTransbordo,CantViajes)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmIdVend,@pmIdTarifCom,@pmNomCliente,@pmNomRemite,@pmNomDestino,@pmLugarCargue,@pmLugarDescargue,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmContacDestino,@pmTelContacDest,@pmemlContacDest,@pmTomadorPoliza,@pmPolizaSeguro,@pmNitCiaPoliza,@pmNomCiaPoliza
,@pmFecVencePol,@pmManifMintrans,@pmRemMintrans,@pmTipSal,@pmNumSalida,@pmIdCiaSal,@pmNumInvent,@pmIdCiaInv,@pmTipoRuta,@pmTipoMintrans,@pmNitEmpTransp,@pmNumFactEmp,@pmMvoAnulacion,@pmPactoCargue,@pmHorasPactoCargue,@pmMinPactoCargue,@pmHoraCitaCargue,@pmHoraLlegaCargue,@pmHoraEntraCargue
,@pmHoraSaleCargue,@pmPactoDescargue,@pmHorasPactoDescargue,@pmMinPactoDescargue,@pmHoraCitaDescargue,@pmHoraLlegaDescargue,@pmHoraEntraDescargue,@pmHoraSaleDescargue,@pmTipoDevRemesa,@pmCodCiuTransb,@pmMvoTransbordo,@pmCantViajes)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmIdVend VARCHAR(16),@pmIdTarifCom VARCHAR(4),@pmNomCliente VARCHAR(150),@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarCargue VARCHAR(150),@pmLugarDescargue VARCHAR(150),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30)
,@pmemlContacto VARCHAR(100),@pmContacDestino VARCHAR(150),@pmTelContacDest VARCHAR(30),@pmemlContacDest VARCHAR(100),@pmPolizaSeguro VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME,@pmManifMintrans DECIMAL(14,2),@pmRemMintrans DECIMAL(14,2),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2)
,@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTomadorPoliza CHAR(1),@pmTipoRuta VARCHAR(10),@pmTipoMintrans VARCHAR(10),@pmNitEmpTransp VARCHAR(16),@pmNumFactEmp VARCHAR(20)
,@pmMvoAnulacion VARCHAR(3),@pmPactoCargue BIT,@pmHorasPactoCargue INT,@pmMinPactoCargue INT,@pmHoraCitaCargue SMALLDATETIME,@pmHoraLlegaCargue SMALLDATETIME,@pmHoraEntraCargue SMALLDATETIME,@pmHoraSaleCargue SMALLDATETIME
,@pmPactoDescargue BIT,@pmHorasPactoDescargue INT,@pmMinPactoDescargue INT,@pmHoraCitaDescargue SMALLDATETIME,@pmHoraLlegaDescargue SMALLDATETIME,@pmHoraEntraDescargue SMALLDATETIME,@pmHoraSaleDescargue SMALLDATETIME,@pmTipoDevRemesa VARCHAR(3),@pmCodCiuTransb VARCHAR(8),@pmMvoTransbordo VARCHAR(3),@pmCantViajes INT
 AS 
 UPDATE Trn_TraRemAnexo SET IdVend=@pmIdVend,IdTarifCom=@pmIdTarifCom,NomCliente=@pmNomCliente,NomRemite=@pmNomRemite,NomDestino=@pmNomDestino,LugarCargue=@pmLugarCargue,LugarDescargue=@pmLugarDescargue,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,ContacDestino=@pmContacDestino,TelContacDest=@pmTelContacDest
,emlContacDest=@pmemlContacDest,PolizaSeguro=@pmPolizaSeguro,NitCiaPoliza=@pmNitCiaPoliza,NomCiaPoliza=@pmNomCiaPoliza,FecVencePol=@pmFecVencePol,ManifMintrans=@pmManifMintrans,RemMintrans=@pmRemMintrans,TipSal=@pmTipSal,NumSalida=@pmNumSalida,IdCiaSal=@pmIdCiaSal,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TomadorPoliza=@pmTomadorPoliza
,TipoRuta=@pmTipoRuta,TipoMintrans=@pmTipoMintrans,NitEmpTransp=@pmNitEmpTransp,NumFactEmp=@pmNumFactEmp,MvoAnulacion=@pmMvoAnulacion,PactoCargue=@pmPactoCargue,HorasPactoCargue=@pmHorasPactoCargue,MinPactoCargue=@pmMinPactoCargue
,HoraCitaCargue=@pmHoraCitaCargue,HoraLlegaCargue=@pmHoraLlegaCargue,HoraEntraCargue=@pmHoraEntraCargue,HoraSaleCargue=@pmHoraSaleCargue,PactoDescargue=@pmPactoDescargue,HorasPactoDescargue=@pmHorasPactoDescargue,MinPactoDescargue=@pmMinPactoDescargue
,HoraCitaDescargue=@pmHoraCitaDescargue,HoraLlegaDescargue=@pmHoraLlegaDescargue,HoraEntraDescargue=@pmHoraEntraDescargue,HoraSaleDescargue=@pmHoraSaleDescargue,TipoDevRemesa=@pmTipoDevRemesa,CodCiuTransb=@pmCodCiuTransb,MvoTransbordo=@pmMvoTransbordo,CantViajes=@pmCantViajes
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraManifAnexo] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmkmsTotal DECIMAL(14,4),@pmVrFleteTon MONEY,@pmTipoRuta VARCHAR(10)
,@pmCdLocTrao VARCHAR(8),@pmCdLocTrad VARCHAR(8),@pmKmsTraOri DECIMAL(14,4),@pmKmsTraDes DECIMAL(14,4),@pmIdMneda VARCHAR(5),@pmVrTasa DECIMAL(14,4)
,@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarFletes VARCHAR(100),@pmNumAnticipo INT,@pmNumCheque VARCHAR(20),@pmTipoMintrans VARCHAR(10)
,@pmMucMintrans INT,@pmContIntegral INT,@pmVolumenCarga DECIMAL(14,4),@pmNumPoliza VARCHAR(30),@pmCdBodDtno VARCHAR(4),@pmTipoTrslado INT,@pmMvoAnulacion VARCHAR(5),@pmMvoSuspension VARCHAR(5)
,@pmWsSeguro BIT,@pmNumRadSeguro DECIMAL(18,2),@pmPuntosRuta DECIMAL(14,4),@pmCantViajes INT
 AS 
 INSERT INTO Trn_TraManifAnexo (TipDoc,Manifiesto,IdCia,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino
,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga,NumPoliza,CdBodDtno,TipoTrslado,MvoAnulacion,MvoSuspension,WsSeguro,NumRadSeguro,PuntosRuta,CantViajes)
 VALUES (@pmTipDoc,@pmManifiesto,@pmIdCia,@pmkmsTotal,@pmVrFleteTon,@pmTipoRuta,@pmCdLocTrao,@pmCdLocTrad,@pmKmsTraOri,@pmKmsTraDes,@pmIdMneda,@pmVrTasa
,@pmNomRemite,@pmNomDestino,@pmLugarFletes,@pmNumAnticipo,@pmNumCheque,@pmTipoMintrans,@pmMucMintrans,@pmContIntegral,@pmVolumenCarga,@pmNumPoliza,@pmCdBodDtno,@pmTipoTrslado,@pmMvoAnulacion,@pmMvoSuspension,@pmWsSeguro,@pmNumRadSeguro,@pmPuntosRuta,@pmCantViajes)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifAnexo] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Manifiesto,IdCia,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa
,NomRemite,NomDestino,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
,NumPoliza,CdBodDtno,TipoTrslado,MvoAnulacion,MvoSuspension,WsSeguro,NumRadSeguro,PuntosRuta,CantViajes
 FROM Trn_TraManifAnexo
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND IdCia=@pmIdCia
GO