SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paQryTraManifInt_Cr] @pmTipDoc VARCHAR(3),@pmManifiestoIni INT,@pmManifiestoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc,TipoDoc,M.Manifiesto,M.IdCia AS CdCia,Compania,M.Fecha,M.FecEmision,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,M.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,M.IdDestino,LD.Localidad AS DescDestino
,M.IdVehiculo AS PlacaVeh,M.nRemolque,M.IdConductor AS CdConductor,NC.RazonSocial AS CondTitula,M.IdCondRelev,NCA.RazonSocial AS CondAuxiliar,NatCarga,M.CdAduana,AF.NomAduana AS AduanaFront,AF.IdPais AS CdPaisAdu,PAF.NombrePais AS PaisAduFront
,M.CdAduanaDest,AD.NomAduana AS AduanaDest,AD.IdPais AS CdPaisAduDest,PAD.NombrePais AS PaisAduDestino,M.IdMoneda AS CdMoneda,MN.Simbolo,MN.Mneda AS Moneda,M.ValorTotal,M.PesoTotal,M.NumMuc,M.CdCiaMuc
,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.OrigenAdd,M.TimeSys AS FechaCrea,M.FecUpdate,M.IdCiaCrea AS CdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
--detalles
,D.Item,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.PesoBruto,D.Volumen,D.ValorMcia,D.IdMoneda AS DetIdMoneda,MD.Mneda AS DetMoneda,MD.Simbolo AS DetMonSimb,D.IdUnd AS CdUnd,UM.Unidad
,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.UndMed,UP.Unidad AS DesUndPeso,D.UndVol,UV.Unidad AS DesUndVol,D.Bultos
--datos del vehículo
,V.IdMarca AS CdMarca,MV.Marca,V.Modelo,V.SerieChasis,V.NumSerie,V.IdPoseedor AS CdPoseedor,T.RazonSocial AS Poseedor
,V.NumSoat,VigSoat,TarjProp,VigTProp,RevTecMec,VigTecMec,V.TarjOper,VigTarjOper,V.Descripcion AS VehDescripcion,V.CertMovilizacion,VigCertMovil
,RQ.IdMarca AS CdMarcaRmq,MR.Marca AS MarcaRmq,RQ.Modelo AS ModeloRmq,RQ.NumSerial AS NumSerieRmq
,NC.IdLugarCed AS CondIdLugar,LC.Localidad AS CondLugarCed,DLC.IdPais AS CdPaisLugar,PLC.NombrePais AS CondPaisLugar
,CDT.TipoSangre,CDT.FactorRh,CDT.Pasporte,CDT.VigPaspte,CDT.Licencia AS NumLicencia,CDT.CatLicencia,CDT.VigLicencia,CDT.NumTarjeta,CDT.VigTarjeta,CDT.PasSeguridad,CDT.VigPasSeg
,NCA.IdLugarCed AS AuxIdLugar,LCA.Localidad AS AuxLugarCed,DLA.IdPais AS AuxCdPais,PLA.NombrePais AS AuxPaisLugar
,CDA.TipoSangre AS AuxTipoSangre,CDA.FactorRh AS AuxFactorRh,CDA.Pasporte AS AuxPasaporte,CDA.VigPaspte AS AuxVigPaspte,CDA.Licencia AS AuxNumLicencia
,CDA.CatLicencia AS AuxCategLic,CDA.VigLicencia AS AuxVigLicencia,CDA.NumTarjeta AS AuxNumTarjeta,CDA.VigTarjeta AS AuxVigTarjeta,CDA.PasSeguridad AS AuxPasSeguridad,CDA.VigPasSeg AS AuxVigPasSeg
-- se agregan Documentos del Remitente agregados en la carta de porte internacional 
,CP.DocRemitente
 FROM Trn_TraManifInt AS M INNER JOIN Companias AS C ON M.IdCia=C.IdCia INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo
 INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON M.IdEstado=E.IdEstado INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero INNER JOIN TercCndtores AS CDT ON M.IdConductor=CDT.IdConductor 
 INNER JOIN Terceros AS T ON V.IdPoseedor=T.IdTercero  INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
 INNER JOIN Trn_TraManifIntMcia AS D ON M.TipDoc=D.TipDoc AND M.Manifiesto=D.Manifiesto AND M.IdCia=D.IdCia
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN adm_monedas AS MD ON D.IdMoneda=MD.IdMneda
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat 
 LEFT JOIN VehRemolq AS RQ ON M.nRemolque=RQ.IdRemque LEFT JOIN Marcas AS MR ON RQ.IdMarca=MR.IdMarca
 LEFT JOIN Sys_Um AS UP ON D.UndMed=UP.UndMed LEFT JOIN Sys_Um AS UV ON D.UndVol=UV.UndMed LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 LEFT JOIN Terceros AS NCA ON M.IdCondRelev=NCA.IdTercero LEFT JOIN TercCndtores AS CDA ON M.IdCondRelev=CDA.IdConductor
 LEFT JOIN Aduanas AS AF ON M.CdAduana=AF.IdAduana LEFT JOIN Aduanas AS AD ON M.CdAduanaDest=AD.IdAduana
 LEFT JOIN adm_Paises AS PAF ON AF.IdPais=PAF.IdPais LEFT JOIN adm_Paises AS PAD ON AD.IdPais=PAD.IdPais
 LEFT JOIN adm_monedas AS MN ON M.IdMoneda=MN.IdMneda 
 LEFT JOIN Localidades AS LC ON NC.IdLugarCed=LC.IdLocal LEFT JOIN Departamentos AS DLC ON LC.IdDep=DLC.IdDep LEFT JOIN adm_Paises AS PLC ON DLC.IdPais=PLC.IdPais
 LEFT JOIN Localidades AS LCA ON NCA.IdLugarCed=LCA.IdLocal LEFT JOIN Departamentos AS DLA ON LCA.IdDep=DLA.IdDep LEFT JOIN adm_Paises AS PLA ON DLA.IdPais=PLA.IdPais
 -- se agregan Documentos del Remitente agregados en la carta de porte internacional 
 LEFT JOIN Trn_TraPorteInt AS CP ON D.TipOrd=CP.TipDoc AND D.NumOrden=CP.NumOrden AND D.IdCiaOrd=CP.IdCia
 WHERE M.TipDoc=@pmTipDoc AND M.Manifiesto BETWEEN @pmManifiestoIni AND @pmManifiestoFin AND M.IdCia=@pmIdCia