/****** Object: StoredProcedure [dbo].[paQryTraRemesaMuc] Script Date: 06/14/2017 12:10:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[paQryTraRemesaMuc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null AS SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,FecDespacho,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia ,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,R.TipoAfiVehic AS TipoAfiVeh,R.Modalidad AS TipoRemesa,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue ,EstCumplido,EstFactura,CdConcepto,Concepto,SerieGuia,NumGuia,R.Observacion AS Observ,R.IdEstado AS CdEdstado,RA.TipoRuta,RA.TipoMintrans --detalles ,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS Volmen,UndVol,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Cases,Cajas,Palets ,NitRemite,Remitente,NitDestntario,Destinatario,DirOrigen,IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen ,DirDestino,IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TarifClie,D.TarifPago AS RemTarifPago ,D.TarifTabla AS RemTarifTabla,D.VrDeclarado AS ValDeclarado,D.VrSeguro AS ValSeguro,TarifSeguro,UndTarifa,UndTarifPago ,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Cumplido,IdCiaCump,FechaCump,DetalleCump ,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump --columnas ocultas --,NumManif,IdCiaManif,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp --datos del pedido ,NumPedido,IdCiaPed,R.FechaPed AS FecPedido,ModalidadPed --datos de factura ,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,NumFactura,NumDevFact,FacCantidad,FacValorTotal,FacCostoTotal,FacFaltantes,FacPesoNeto --datos de manifiesto ,TipoMuc,ISNULL(MucNumero,0) AS MucNumero,MucCdCia,FechaMuc,FecDespMuc,MucPlacaVeh,MucCdPoseedor,NP.RazonSocial AS NomPoseedor,MucCdRuta,MucTarifTabla,MucTarifPago,MUC.VrFletes AS VrTotFletes,MUC.VrRetencion AS MucVrRetencion ,MUC.VrReteIca AS MucVrReteIca,MUC.VrDescuento AS MucVrDcto,MUC.VrAnticipo AS MucVrAnticipo,VrAntAdic,MUC.TarifaRet AS MucTarifRet,MUC.TarifaIca AS MucTarifIca ,NumMintrans,EdoMintrans,MucObserv,MucTipOdp,ISNULL(MucOrdPago,0) AS MucOrdPago,MucCdCiaOdp,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,TotalEgresos ,dbo.FuncMucListaAntcipos(ISNULL(MucNumero,0),MucCdCia) AS Anticipos --datos del cliente ,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail ,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie FROM Trn_TraRemesa AS R 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 Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.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 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 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Conceptos AS C ON R.CdConcepto=C.IdConcepto LEFT JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia --==== Consulta de pedidos LEFT JOIN (SELECT P.TipDoc AS TipoPed,P.Pedido AS nPedido,P.IdCia AS CdCiaPed,P.FecDespacho AS FechaPed,P.IdVend AS CdVend,P.Modalidad AS ModalidadPed ,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,P.Observacion AS PedObserv,D.IdMercancia AS PedCdMcia,D.DescripMcias AS PedDescMcia ,SUM(D.Cantidad) AS PedCantidad,SUM(D.PesoNeto) AS PedPesoNeto,SUM(D.Volumen) AS PedVolumen,SUM(D.Cases) AS PedCases ,SUM(D.Cajas) AS PedCajas,SUM(D.Palets) AS PedPalets,MAX(D.TarifClie) AS PedTarifClie,MAX(D.TarifPago) AS PedTarifPago FROM Trn_TraPedido AS P INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia WHERE P.TipDoc='PDT' AND P.Anulado=0 GROUP BY P.TipDoc,P.Pedido,P.IdCia,P.FecDespacho,P.IdVend,P.Modalidad,Cotizacion,IdCiaCot ,NumAprob,IdCiaApr,FecAprob,P.Observacion,D.IdMercancia,D.DescripMcias) AS PD ON R.NumPedido=PD.nPedido AND R.IdCiaPed=PD.CdCiaPed AND D.IdMercancia=PD.PedCdMcia --==== consulta de facturas-detalles LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*-1 ELSE Cantidad END) AS FacCantidad ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCostoTotal ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades*-1 ELSE Unidades END) AS FacUnidades ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto*-1 ELSE PesoNeto END) AS FacPesoNeto ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen*-1 ELSE Volumen END) AS FacVolumen ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases*-1 ELSE Cases END) AS FacCases ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas*-1 ELSE Cajas END) AS FacCajas ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets*-1 ELSE Palets END) AS FacPalets ,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes ,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE Factura END) AS NumFactura ,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFact FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON R.TipDoc=RF.TipRem AND R.NumOrden=RF.Remesa AND R.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem --==== consulta de manifiestos LEFT JOIN (SELECT RM.TipRem AS MucTipoRem,RM.Remesa AS MucNumRemesa,RM.IdCiaRem AS MucCdCiaRem,RM.ItemRem AS MucItemRem,RM.TipDoc AS TipoMuc,RM.Manifiesto AS MucNumero,RM.IdCia AS MucCdCia ,RM.TarifTabla AS MucTarifTabla,RM.TarifPago AS MucTarifPago,RemMintrans,M.Fecha AS FechaMuc,M.FecDespacho AS FecDespMuc,M.IdRuta AS MucCdRuta,M.IdOrigen AS MucCdOrigen,M.IdDestino AS MucCdDestino,M.IdVehiculo AS MucPlacaVeh ,M.IdConductor AS MucCedCond,M.nRemolque AS MucnRemolque,TipoAfiVehic,M.IdPoseedor AS MucCdPoseedor,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos ,VrCargos,VrDctos,TarifaFlete,TarifaRet,TarifaIca,NumMintrans,EdoMintrans,M.TipOdp AS MucTipOdp,M.OrdPago AS MucOrdPago,M.IdCiaOdp AS MucCdCiaOdp,M.FechaOdp AS MucFecOdp,M.Observacion AS MucObserv 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 WHERE M.Anulado=0) AS MUC ON R.TipDoc=MUC.MucTipoRem AND R.NumOrden=MUC.MucNumRemesa AND R.IdCia=MUC.MucCdCiaRem AND D.Item=MUC.MucItemRem --===== Orden de pago LEFT JOIN (SELECT M.TipMuc AS OdpTipMuc,M.Manifiesto AS OdpManif,M.IdCiaMuc AS OdpCdCiaMuc,M.TipDoc AS TipOdp,M.OrdPago AS NumOPago,M.IdCia AS OdpCdCia,OP.Fecha AS FechaOdp ,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto ,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,TarifaRet,TarifaIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,OP.Observacion AS OdpObserv FROM Trn_TraOrdenManif AS M INNER JOIN Trn_TraOrdenPago AS OP ON M.TipDoc=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCia=OP.IdCia WHERE OP.Anulado=0) AS ODP ON MUC.MucTipOdp=ODP.TipOdp AND MUC.MucOrdPago=ODP.NumOPago AND MUC.MucCdCiaOdp=ODP.OdpCdCia LEFT JOIN Terceros AS NP ON MUC.MucCdPoseedor=NP.IdTercero -- Egresos 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 EGT ON MUC.MucTipOdp=EGT.TipOrden AND MUC.MucOrdPago=EGT.NumOPago AND MUC.MucCdCiaOdp=EGT.CdCiaOpago WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Anulado=0