/****** Object:  StoredProcedure [dbo].[paQryTraDespachosLta]    Script Date: 06/14/2017 12:08:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paQryTraDespachosLta] @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 D.TipDoc AS CdTipDoc,D.Manifiesto AS NumManif,D.IdCia AS CdCia,Compania,D.Fecha AS FecMuc,FecCargue,D.FecDespacho,D.IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,D.IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.IdRuta AS CdRuta,R.Ruta AS DescRuta,D.IdVehiculo AS PlacaVeh,M.nRemolque AS MucRemolque,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,T.TipoId AS PosTipoId,D.IdPoseedor AS NitPoseedor,T.Dv AS PosDv,T.RazonSocial AS NomPoseedor,NitRemite,Remitente,NitDestntario,Destinatario,DescMcia,D.PesoTotal,NumOrden,IdCiaOrd,D.Remesa,D.IdCiaRem
,D.Estado AS EdoDespacho,D.Anulado AS DesAnulado,D.FecDev,D.Observacion AS Observ,D.TimeSys,D.FecUpdate,D.IdUsuario AS CdUsuario,Usuario
--datos del vehíclo
,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,TPR.VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
--datos del MAnifiesto
,M.VrFletes AS ManVrFletes,M.VrRetencion AS ManVrRetencion,M.VrReteIca As ManVrRteIca,ISNULL(AC.VlrAnticipo,0)-ISNULL(DV.VrDevAnt,0) AS ManVrAnticipo,M.VrNeto AS ManVrNeto,M.VrPagos AS ManVrPagos,M.VrCargos AS ManVrCargos
,MO.NumCheque AS ManNumChe,AC.NumAnt As ManNumAnt
,CP.TipCom AS TipEgr,CP.Comprobante AS NumEgr,CP.IdCia AS IdCiaEgr,CP.Fecha AS FecCheEgr
,M.TipOdp AS TipManOdp,M.OrdPago As ManNumOdp,M.IdCiaOdp AS ManCiaOdp,M.FechaOdp AS FecMucOdp,ISNULL(EGO.ABONO,0) As ManVrAbono,MA.NomDestino AS MaNomDest,MA.NomRemite AS ManNomRemi
 FROM Trn_TraDespachos AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto M ON D.TipDoc=M.TipDoc AND D.Manifiesto=M.Manifiesto AND D.IdCia=M.IdCia
 INNER JOIN Trn_TraManifAnexo MA ON D.TipDoc=MA.TipDoc AND D.Manifiesto=MA.Manifiesto AND D.IdCia=MA.IdCia 
 INNER JOIN Localidades AS CO ON  D.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON D.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON D.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero 
 INNER JOIN Vehiculos AS V ON D.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 TercCndtores AS CT ON D.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Trn_TraOrdenManif AS MO ON M.TipOdp=MO.TipDoc AND M.OrdPago=MO.OrdPago AND M.IdCiaOdp=MO.IdCia
 LEFT JOIN Trn_Comprobantes AS CP ON MO.TipEgr=CP.TipCom AND MO.Egreso=CP.Comprobante AND MO.IdCiaEGR=CP.IdCia
 LEFT JOIN (SELECT TipMuc,Manifiesto,IdCiaMuc,MAX(Anticipo) AS NumAnt,SUM(VrAnticipo) AS VlrAnticipo
 FROM Trn_TraAnticipos GROUP BY TipMuc,Manifiesto,IdCiaMuc) AS AC ON M.TipDoc=AC.TipMuc AND M.Manifiesto=AC.Manifiesto AND M.IdCia=AC.IdCiaMuc 
 LEFT JOIN (SELECT TipMuc,Manifiesto,IdCiaMuc,SUM(AB.VrAbono)AS VrDevAnt FROM Trn_TraAnticipos AS A LEFT JOIN Trn_TraAntAbonos AS AB 
 ON A.TipDoc=AB.TipAnt AND A.Anticipo=AB.Anticipo AND A.IdCia=AB.IdCiaAnt WHERE AB.TipDoc ='DVA' GROUP BY TipMuc,Manifiesto,IdCiaMuc)
 AS DV ON M.TipDoc=DV.TipMuc AND M.Manifiesto=DV.Manifiesto AND M.IdCia=DV.IdCiaMuc 
 LEFT JOIN (SELECT TipOdp,OrdPago,IdCiaOdp,SUM(VrAbonado) AS ABONO FROM Trn_TraEgrOrden GROUP BY TipOdp,OrdPago,IdCiaOdp) AS EGO ON M.TipOdp=EGO.TipOdp AND M.OrdPago=EGO.OrdPago AND M.IdCiaOdp=EGO.IdCiaOdp 
 WHERE D.TipDoc=@pmTipDoc AND  D.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')