SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[paQryTraManifiestoRen]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
SELECT RM.TipDoc AS TipMuc,RM.Manifiesto AS NumManif,RM.IdCia AS CdCia,Compania,M.Fecha AS MucFecha,M.FecDespacho AS MucFecDespacho,RM.TipRem AS TipRem,RM.Remesa AS NumRemesa,RM.IdCiaRem AS CdCiaRem
,RM.ItemRem AS ItemRemsa,R.Fecha AS RemFecha,R.FecDespacho AS RemFecDespacho,IdMercancia,DescripMcias,D.Cantidad AS RemCantidad,D.PesoNeto AS RemPesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS RemVolumen,UndVol,Cases,Cajas,Palets
,D.TarifClie AS RemTarifClie,UndTarifa,D.VrDeclarado AS RemVrDeclara,D.VrSeguro AS RemVrSeguro,D.TarifSeguro AS RemTarifSeguro,RM.TarifPago AS MucTarifPago,RM.TarifTabla AS MucTarifTabla,UndTarifPago,D.Cumplido AS NumCumplido,D.IdCiaCump AS CdCiaCump
,D.FechaCump AS FecCumplido,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,UndTarCump,PagoCump,UndTarPagoCump,DetalleCump,D.TarifClieFac AS RemTarifFact,D.TarifOdp AS RemTarifOdp,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,NitRemite,Remitente,NitDestntario,Destinatario,D.IdOrigen AS CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.IdDestino AS CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia,R.Modalidad AS TipoRemesa,M.IdVehiculo AS PlacaVeh,M.TipoAfiVehic AS TipoAfiVeh,M.IdConductor AS CedCondutor,CDT.RazonSocial AS NomConductor,M.nRemolque AS NumRemolque
,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,M.VrFletes AS MucVrFletes,M.VrRetencion AS MucVrRetencion,M.VrReteIca AS MucVrReteIca,M.VrAnticipo+VrAntAdic AS MucAnticipos,M.TarifaRet AS MucTarifRet,M.TarifaIca AS MucTarifIca,M.IdRuta AS CdRuta
,M.TipOdp AS CdTipOdp,M.OrdPago AS MucNumOdp,M.IdCiaOdp AS MucCiaOdp,M.FechaOdp AS FecOrdenPago,EstOrden,M.Observacion AS MucObserv
--datos de orden pago
,OP.PesoTotal AS OdpPesoNeto,OP.Unidades AS OdpCantidad,OP.Volumen AS OdpVolumen,OP.TarifaTabla AS OdpTarifTabla,OP.TarifaFlete AS OdpTarifFlete,OP.UnidTarifa AS OdpUndTarifa,ISNULL(OP.VrTotalFletes,0) AS OdpTotalFletes,OP.VrDescuento AS OdpVrDcto
,OP.VrRetencion AS OdpVrRetFte,OP.VrReteIca AS OdpReteIca,OP.VrAnticipos AS OdpAnticipos,OP.VrFaltantes AS OdpFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla
,VrOtrosPagos,OP.VrNeto AS OdpVrNetoPagar,VrRemesas,VrRecCaja,OP.TarifaRet AS OdpTarifRet,OP.TarifaIca AS OdpTarifIca
,OP.IdPoseedor AS OdpNitPoseedor,ODP.Anulado AS OdpAnulado,ODP.Observacion AS OdpObserv,TipEgr,Egreso,IdCiaEgr,NumCheque,OP.Referencia AS OdpReferencia
,R.NumPedido AS nPedido,IdCiaPed,FechaPed,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
,R.EstCumplido AS RemEdoCumplido,R.EstFactura AS RemEdoFactura,SerieGuia,NumGuia,R.Observacion AS RemObserv
--columnas de detalles ocultas,TipFac,Factura,IdCiaFac,FechaFac,TipOdp,NumeroOdp,IdCiaOdp,PesoCont
--datos de factura
,D.TipFac AS CdTipFact,Factura,NumFactura,D.IdCiaFac AS CdCiaFact,FechaFac,FacCantidad,FacUnidades,FacPesoNeto,FacVolumen,FacValorTotal,FacCostoTotal,FacFaltantes
,NumDevFactura,DfaCantidad,DfaUnidades,DfaPesoNeto,DfaVolumen,DfaValorTotal,DfaCostoTotal,DfaFaltantes
,NumNotaDb,NumNotaCr,NotCantidad,NotPesoNeto,NotVolumen,NotValorTotal,NotCostoTotal,Egreso,EGR.FecEgr
--DATOS DE LEGALIZACION DE GASTOS 
,ISNULL(LGA.VrGastos,0) AS VrGastos,V.TIPOAFIL,LGA.Anulado
--NOVDADES DE MANIFIESTO Y DE REMESAS
,NVM.NovDedManif,NVM.NovPagoManif,NVR.CargoRemesa
 FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON RM.TipRem=D.TipDoc AND RM.Remesa=D.NumOrden AND RM.IdCiaRem=D.IdCia AND RM.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON RM.TipRem=R.TipDoc AND RM.Remesa=R.NumOrden AND RM.IdCiaRem=R.IdCia
 INNER JOIN Companias AS CN ON RM.IdCia=CN.IdCia 
 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 Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Trn_TraOrdenPago AS ODP ON M.TipOdp=ODP.TipDoc AND M.OrdPago=ODP.OrdPago AND M.IdCiaOdp=ODP.IdCia
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
--subconsulta de facturas 
LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad ELSE 0 END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad*VrCosto ELSE 0 END) AS FacCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN VrFaltante ELSE 0 END) AS FacFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Unidades ELSE 0 END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN PesoNeto ELSE 0 END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Volumen ELSE 0 END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cases ELSE 0 END) AS FacCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cajas ELSE 0 END) AS FacCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Palets ELSE 0 END) AS FacPalets
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad ELSE 0 END) AS DfaCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfaValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*VrCosto ELSE 0 END) AS DfaCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante ELSE 0 END) AS DfaFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades ELSE 0 END) AS DfaUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto ELSE 0 END) AS DfaPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen ELSE 0 END) AS DfaVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases ELSE 0 END) AS DfaCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas ELSE 0 END) AS DfaCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets ELSE 0 END) AS DfaPalets
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad WHEN 'NCR' THEN Cantidad*-1 ELSE 0 END) AS NotCantidad
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad*VrUnitario WHEN 'NCR' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS NotValorTotal
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad*VrCosto WHEN 'NCR' THEN (Cantidad*VrCosto)*-1 ELSE 0 END) AS NotCostoTotal
,SUM(CASE TipDoc WHEN 'NDB' THEN PesoNeto WHEN 'NCR' THEN PesoNeto*-1 ELSE 0 END) AS NotPesoNeto
,SUM(CASE TipDoc WHEN 'NDB' THEN Volumen WHEN 'NCR' THEN Volumen*-1 ELSE 0 END) AS NotVolumen
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Factura ELSE 0 END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFactura
,MAX(CASE TipDoc WHEN 'NDB' THEN Factura ELSE 0 END) AS NumNotaDb
,MAX(CASE TipDoc WHEN 'NCR' THEN Factura ELSE 0 END) AS NumNotaCr
 FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON RM.TipRem=RF.TipRem AND RM.Remesa=RF.Remesa AND RM.IdCiaRem=RF.IdCiaRem AND RM.ItemRem=RF.ItemRem
LEFT JOIN(select OE.TipOdp ,OE.OrdPago,OE.IdCiaOdp,MAX(E.Fecha)AS FecEgr from Trn_TraEgrOrden  OE inner join Trn_Comprobantes E
ON OE.TipCom =E.TipCom AND OE.NumEgreso =E.Comprobante  AND OE.IdCia=E.IdCia 
group by OE.TipOdp ,OE.OrdPago,OE.IdCiaOdp) AS EGR ON ODP.TipDoc =EGR.TipOdp AND ODP.OrdPago=EGR.OrdPago AND ODP.IdCia =EGR.IdCiaOdp 
LEFT JOIN Trn_TraLiqViajes AS LGA ON M.TipOdp=LGA.TipDoc AND M.OrdPago=LGA.Liquidacion AND M.IdCiaOdp=LGA.IdCia
LEFT JOIN (select TipDoc,Documento,IdCia,SUM(CASE WHEN TipoConc='DEDUCCION' THEN Cantidad*VrUnitario ELSE 0 END) AS NovDedManif
,SUM(CASE WHEN TipoConc='PAGO' THEN Cantidad*VrUnitario ELSE 0 END) AS NovPagoManif from Trn_TraConceptos --WHERE TipDoc='MUC' 
group by TipDoc,Documento,IdCia) AS NVM ON NVM.TipDoc=RM.TipDoc  AND NVM.Documento=RM.Manifiesto AND NVM.IdCia=RM.IdCia
LEFT JOIN (select TipDoc,Documento,IdCia,SUM(CASE WHEN TipoConc='CARGO' THEN Cantidad*VrUnitario ELSE 0 END) AS CargoRemesa from Trn_TraConceptos --WHERE TipDoc='RMT' 
 group by TipDoc,Documento,IdCia) AS NVR ON NVR.TipDoc=RM.TipRem  AND NVR.Documento=RM.Remesa AND NVR.IdCia=RM.IdCiaRem
 WHERE  RM.TipDoc='MUC' AND M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.Anulado=0 AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND  M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 --WHERE  RM.TipDoc='MUC' and RM.Manifiesto=17