if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemFact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemFact]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemFact_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemFact_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraFacRemesas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraFacRemesas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrRelTraDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrRelTraDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasRelTraDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasRelTraDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFact]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFactLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFactLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFacRemesasItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFacRemesasItem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaMuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaMuc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaOdp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaOdp]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaOdp] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
AS 
SELECT R.TipDoc,R.NumOrden,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,D.Item,D.IdMercancia AS CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmdPeso,D.Volumen,R.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,D.NitRemite,D.Remitente,D.DirOrigen,D.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.NitDestntario,D.Destinatario,D.DirDestino,D.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,D.TarifClie,D.TarifPago,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.UndTarifPago,Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida
,R.IdAgencia AS CdAgencia,A.Agencia,R.IdVehiculo,R.IdConductor,CDT.RazonSocial AS NomConductor,R.nRemolque,R.TipoAfiVehic,R.Modalidad,NumPedido,IdCiaPed,R.TipDcm,R.NumDocmto,R.IdCiaDcm,R.EstCumplido,D.Cumplido AS NumCumplido,D.IdCiaCump,R.EstFactura
--datos de factura
,RF.TipoFact,RF.NumFactura,RF.IdCiaFact,F.Fecha AS FacFecha,RF.FacCantidad,RF.FacValorTotal,F.IdCliente AS FacIdCliente,NCL.RazonSocial AS FacNomCliente,F.IdAgencia AS FacIdAgencia,AF.Agencia AS FacAgencia,F.IdVend AS FacIdVend,VN.RazonSocial AS Vendedor
--Manifiesto y ordenes
,MC.TipDoc AS MucTipo,MC.Manifiesto,MC.IdCia AS MucIdCia,MC.Fecha AS MucFecha,MC.IdRuta,Ruta,MC.IdPoseedor,NP.RazonSocial AS NomPoseedor,MC.Cumplido AS MucCumplido,MC.IdCiaCump AS MucIdCiaCump,MC.EstCumplido AS MucEstCumplido,MC.EstOrden AS MucEstOrden
,OP.OdpTipo,OP.OrdPago,OP.OdpIdCia,OP.OdpVrFlete,OP.OdpDescuento,OP.OdpRetencion,OP.OdpReteIca,OP.OdpVrCREE,OP.OdpAnticipos,OP.OdpFaltantes,OP.OdpSeguros,OP.OdpFondos,OP.OdpAportes,OP.OdpOtrosDcto,OP.OdpImpuestos
,OP.OdpEstampillas,OP.OdpOtrosPagos,OP.OdpVrNeto,OP.OdpTarifaUnd,OP.OdpPesoTotal,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,EG.TotEgresos
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 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 Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
--Remeasa/Facturas
LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem
,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
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN '0' ELSE TipDoc END) AS TipoFact
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN '00' ELSE IdCia END) AS IdCiaFact
,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 (CantPago*VrCosto)*-1 ELSE CantPago*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 VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE TarifaIva END) AS FacTarifIva
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE TarifaRet END) AS FacTarifRet
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE VrRetencion END) AS FacRetencion
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE TarifaIca END) AS FacTarifIca
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE VrReteIca END) AS FacReteIca
FROM Trn_TraFacRemesas WHERE TipoReg=0 GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON D.TipDoc=RF.TipRem AND D.NumOrden=RF.Remesa AND D.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem
LEFT JOIN (SELECT TipDoc,Factura,IdCia,Fecha,IdCliente,IdAgencia,IdVend,IdPlazo,BaseImp,BaseRet FROM Trn_Facturas WHERE Modalidad='TRANSPORTE' AND Anulado=0) AS F ON RF.TipoFact=F.TipDoc
AND RF.NumFactura=F.Factura AND RF.IdCiaFact=F.IdCia
--remesa/manifiesto
LEFT JOIN (SELECT MR.TipRem,MR.Remesa,MR.IdCiaRem,MR.ItemRem,MR.TipDoc,MR.Manifiesto,MR.IdCia,MR.TarifPago,MR.RemMintrans,M.Fecha,M.IdRuta,M.IdVehiculo,M.nRemolque,M.TipoAfiVehic,M.IdPoseedor,M.IdConductor
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrAnticipo,M.VrAntAdic,M.VrCargos,M.VrDctos,M.VrFleteNeto,M.PesoTotal,M.TarifaRet,M.TarifaIca,M.Cumplido,M.IdCiaCump,M.FechaCump,M.EstCumplido,M.EstOrden
FROM Trn_TraManifRem AS MR INNER JOIN Trn_TraManifiesto AS M ON MR.TipDoc=M.TipDoc AND MR.Manifiesto=M.Manifiesto AND MR.IdCia=M.IdCia WHERE M.Anulado=0) AS MC ON D.TipDoc=MC.TipRem AND D.NumOrden=MC.Remesa AND D.IdCia=MC.IdCiaRem AND D.Item=MC.ItemRem
--Remesa/Manif/ordnenes
LEFT JOIN (SELECT OM.TipMuc,OM.Manifiesto,OM.IdCiaMuc,OM.TipDoc AS OdpTipo,OM.OrdPago,OM.IdCia AS OdpIdCia,OM.VrTotalFletes AS OdpVrFlete,OM.VrDescuento AS OdpDescuento,OM.VrRetencion AS OdpRetencion,OM.VrReteIca AS OdpReteIca
,OM.VrReteCREE AS OdpVrCREE,OM.VrAnticipos AS OdpAnticipos,OM.VrFaltantes AS OdpFaltantes,OM.VrSeguros AS OdpSeguros,OM.VrFondos AS OdpFondos,OM.VrAportes AS OdpAportes,OM.VrOtrosDctos AS OdpOtrosDcto,OM.VrImpuestos AS OdpImpuestos,OM.VrEstampilla AS OdpEstampillas
,OM.VrOtrosPagos AS OdpOtrosPagos,OM.VrNeto AS OdpVrNeto,OM.TarifaFlete AS OdpTarifaUnd,OM.PesoTotal AS OdpPesoTotal,OM.Unidades AS OdpCantidad,OM.Volumen AS OdpVolumen,OM.TipEgr,OM.Egreso,OM.IdCiaEgr
FROM Trn_TraOrdenManif AS OM INNER JOIN Trn_TraOrdenPago AS O ON OM.TipDoc=O.TipDoc AND OM.OrdPago=O.OrdPago AND OM.IdCia=O.IdCia WHERE O.Anulado=0) AS OP ON MC.TipDoc=OP.TipMuc AND MC.Manifiesto=OP.Manifiesto AND MC.IdCia=OP.IdCiaMuc
--Egresos
LEFT JOIN (SELECT EO.TipOdp,EO.OrdPago,EO.IdCiaOdp,SUM(VrAbonado) AS TotEgresos 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 OP.OdpTipo=EG.TipOdp AND OP.OrdPago=EG.OrdPago AND OP.OdpIdCia=EG.IdCiaOdp
LEFT JOIN Terceros AS NCL ON F.IdCliente=NCL.IdTercero LEFT JOIN Agencias AS AF ON F.IdAgencia=AF.IdAgencia
LEFT JOIN Terceros AS VN ON F.IdVend=VN.IdTercero LEFT JOIN Terceros AS NP ON MC.IdPoseedor=NP.IdTercero
LEFT JOIN Rutas AS RT ON MC.IdRuta=RT.IdRuta
WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (@pmIdCia IS NULL OR R.IdCia=@pmIdCia)
AND (@pmIdCliente IS NULL OR R.IdCliente=@pmIdCliente) AND R.Anulado=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFacturasRelTraDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT F.TipDoc AS TipoFact,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha,FE.Prefijo,FE.NumFace,F.IdConcepto AS CdConcepto,C.Concepto AS ConcDescrip,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia
,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,F.VrDescuento AS VrDcto,F.VrImpuesto AS VrIva,F.VrRetencion AS VrRetFte,F.VrReteICA AS VrRetIca,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal
,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,BaseImp,BaseRet,BaseIca,BaseRiv,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi
,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio
,DE.Departamento AS DptoEnvio,DiasEntraga,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac
,F.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,CdConductor,TC.RazonSocial AS Conductor
,F.CdRuta,Ruta,TipPed,Pedido,IdCiaPed,AutzaMora,AutzaCupo,VrReteCREE,TarifaRtc,CodTarRtc,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom
,F.Anulado AS FacAnulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,TimeSys,F.FecUpdate AS Fec_Update,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,TipoReg,FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,FecRemesa,Descripcion,D.Cantidad AS Cant,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,Volumen,UndVol,Cases,Cajas,Palets,CantPago
,D.TarifaIva AS DetTarifIva,D.VrImpuesto AS DetVrIva,TarifaDct,D.VrDescuento AS DetVrDcto,D.TarifaRet AS DetTarifRet,D.VrRetencion AS DetVrRetFte,D.TarifaIca AS DetTarifIca,D.VrReteIca AS DetVrRetIca,VrFaltante,D.Remision AS DetNumRemsion,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,DescripMcia,CdConcepto,CF.Concepto AS DetConcepto
,CdCCosto,CCosto,D.CdSubCos AS CdSubCentro,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,CdAgencia,D.pVehiculo AS PlacaVeh,TipoAfiVehic,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.Anulado AS DetAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_Facturas AS F  INNER JOIN  Trn_TraFacRemesas AS D ON F.TipDoc=D.TipDoc  AND F.Factura=D.Factura  AND F.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON F.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON F.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 Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 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
 LEFT JOIN  Localidades AS LE ON F.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Mercancias AS MC ON D.CdMercancia=MC.IdMercancia LEFT JOIN  ConcDiversos AS CF ON D.CdConcepto=CF.IdConcepto
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Trn_Face AS FE ON F.TipDoc=FE.TipDoc AND F.Factura=FE.Documento AND F.IdCia=FE.IdCia
 WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND F.TipDoc LIKE ISNULL(@pmTipDoc,'%')
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND F.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY F.IdCia,F.Factura
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasFmt] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,F.IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,F.VrDescuento AS VrDcto,F.VrImpuesto AS VrIva,F.VrRetencion AS VrRetFte,F.VrReteICA AS VrRetIca,F.VrReteIVA,F.VrFletes
,F.VrOtros,F.VrCargos,F.VrOtrDcto,F.VrCostos,F.VrSobretasa,F.VrImpGlobal,F.VrFaltantes,F.VrAnticipos,F.VrNeto,VrAplicado,F.Cantidad AS CantTotal,F.PesoTotal,F.UnidTotal,F.VolTotal,CantFalt,F.VrCostoRem,F.VrCostoAfi,F.BaseImp,F.BaseRet,F.BaseIca,F.BaseRiv
,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,DiasEntraga,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac
,F.IdForma AS CdForma,F.DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.TipPed,F.Pedido,F.IdCiaPed,F.TipRem AS TipRemision,F.Remision AS NumRemision,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CiaCotiza,F.FecPedido
,AutzaMora,AutzaCupo,F.Modalidad,KmtVehic,F.TipCom,TipoCom,F.Comprobante,F.IdCiaCom,F.Anulado AS FacAnulado,F.NumDev,F.FecDev,F.Observacion AS Observ,VrReteCREE,TarifaRtc,CodTarRtc,F.IdEstado AS CdEstado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,Leyenda
--detales
,D.Item,D.TipoReg,D.FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.FecRemesa,D.Descripcion,D.Cantidad AS Cant,D.VrUnitario,D.VrCosto,D.UndTarifa,D.UndCosto,D.Unidades,D.PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen,D.UndVol,D.Cases,D.Cajas,D.Palets,D.CantPago
,D.TarifaIva AS DetTarifIva,D.VrImpuesto AS DetVrIva,TarifaDct,D.VrDescuento AS DetVrDcto,D.TarifaRet AS DetTarifRet,D.VrRetencion AS DetVrRetFte,D.TarifaIca AS DetTarifIca,D.VrReteIca AS DetVrRetIca,VrFaltante,D.Remision AS DetNumRemsion,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CdMercancia,DescripMcia
,D.CdConcepto,CF.Concepto AS DetConcepto,D.CdCCosto,CCosto,D.CdSubCos AS CdSubCentro,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,D.CdAgencia,D.pVehiculo AS PlacaVeh,D.TipoAfiVehic,D.IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,D.IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.Anulado AS DetAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,R.CdRutaTarif,RT.Ruta
,RM.SedeRem,SDR.NomSede AS NomSedeRem,RM.SedeDest,SDD.NomSede AS NomSedeDest
--Información del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,DM.Comentarios
,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia
 FROM Trn_Facturas AS F INNER JOIN  Trn_TraFacRemesas AS D ON F.TipDoc=D.TipDoc  AND F.Factura=D.Factura AND F.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc
 INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  
 INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliente AS CLI ON F.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 Agencias AS A ON F.IdAgencia=A.IdAgencia 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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
 LEFT JOIN  Localidades AS LE ON F.IdLocEnv=LE.IdLocal   LEFT JOIN TiposCom AS TCM ON F.TipCom=TCM.IdCom  
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Mercancias AS MC ON D.CdMercancia=MC.IdMercancia LEFT JOIN  ConcDiversos AS CF ON D.CdConcepto=CF.IdConcepto
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Trn_TraRemesa AS R ON D.TipRem=R.TipDoc AND D.Remesa=R.NumOrden AND D.IdCiaRem=R.IdCia
 LEFT JOIN Rutas AS RT ON R.CdRutaTarif=RT.IdRuta 
 LEFT JOIN Trn_DocMemo AS DM ON F.TipDoc=DM.TipDoc AND F.Factura=DM.Documento AND F.IdCia=DM.IdCia
 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
 LEFT JOIN TercSedes AS SDR ON RM.NitRemite=SDR.IdTercero AND RM.SedeRem=SDR.IdSede LEFT JOIN TercSedes AS SDD ON RM.NitDestntario=SDD.IdTercero AND RM.SedeDest=SDD.IdSede
 LEFT JOIN Trn_Face AS FE ON F.TipDoc=FE.TipDoc AND F.Factura=FE.Documento AND F.IdCia=FE.IdCia
 WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia
 ORDER BY F.Factura
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaFac]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT FR.TipDoc AS CdTipDoc,TipoDoc,Factura,FR.IdCia AS CdCia,CN.Compania AS NomCiaFact,FechaFact,Item,TipoReg,TipRem,Remesa,IdCiaRem,CR.Compania AS NomCiaRem,ItemRem,FecRemesa,FR.Descripcion AS DescripRem
,FR.Cantidad AS CantFact,VrUnitario,UndTarifa,VrCosto,UndCosto,CantPago,TarifaDct,VrDescuento,TarifaIva,VrImpuesto,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,CantidadFalt,UnidadFalt,Unidades,PesoNeto,FR.UndMed AS CdUmPeso,Unidad
,Volumen,UndVol,Cases,Cajas,Palets,FR.CdConcepto AS CdConcept,CDV.Concepto AS DescConcept,CdMercancia,DescripMcia,CdAgencia,Agencia,CdCCosto,CCosto,FR.CdSubCos AS CdSubCentro,SubCosto,FR.TipoAfiVehic AS TipoAfiVeh,pVehiculo
,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,FR.Anulado AS FacAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,NumPedRem,CiaPedRem
--datos de remesa
,Modalidad,IdRemitente,NomRemite,IdDestinatario,NomDestino,R.IdConductor AS RemCedCond,CDT.RazonSocial AS RemConductor,nRemolque,NumManif,IdCiaManif,R.Observacion AS RemObserv,RA.TipoRuta,RA.TipoMintrans
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo
--datos de factura encabezados
,FacCdConc,CF.Concepto AS FacConcepto,T.TipoId AS TercTipo,FacNitClie,T.Dv AS TercDv,T.RazonSocial AS NombreClie,FacNitVend,VN.RazonSocial AS Vendedor,FacCdCiudad,LF.Localidad AS FacCiudad,LF.IdDep AS FacCdDpto,DF.Departamento AS FacDpto,FacVrNeto
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraFacRemesas AS FR INNER JOIN Sys_TiposDoc AS TD ON FR.TipDoc=TD.IdDoc INNER JOIN Companias AS CN ON FR.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON FR.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON FR.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 --SUBQUERY FACTURAS
 INNER JOIN (SELECT F.TipDoc AS FacTipo,F.Factura AS FacNumero,F.IdCia AS FacCdCia,F.IdConcepto AS FacCdConc,F.IdCliente AS FacNitClie,F.IdAgencia AS FacIdAgenc
 ,F.IdVend AS FacNitVend,F.IdLocEnv AS FacCdCiudad,F.VrCargos AS FacVrCargos,F.VrOtrDcto AS FacVrOtrDctos,F.VrNeto AS FacVrNeto
 FROM Trn_Facturas AS F WHERE F.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND  F.Modalidad='TRANSPORTE'  AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,D.IdConcepto,D.IdCliente,D.IdAgencia,D.IdVend,D.IdLocEnv,D.VrCargos,D.VrOtrDcto,D.VrNeto
 FROM Trn_DevFcr AS D WHERE D.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND D.Modalidad='TRANSPORTE' AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 UNION ALL
 SELECT N.TipDoc,N.NumNota,N.IdCia,N.IdConcepto,N.IdCliente,N.IdAgencia,N.IdVend,N.IdLocal,N.VrOtros,0,N.VrNeto
 FROM Trn_Notas AS N WHERE N.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND N.Modalidad='REMESAS' AND  N.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%')  ) AS F ON FR.TipDoc=F.FacTipo AND FR.Factura=F.FacNumero AND FR.IdCia=F.FacCdCia
 INNER JOIN Terceros AS T ON F.FacNitClie=T.IdTercero INNER JOIN TercCliente AS CLI ON F.FacNitClie=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 Terceros AS VN ON F.FacNitVend=VN.IdTercero INNER JOIN Conceptos AS CF ON F.FacCdConc=CF.IdConcepto
 LEFT JOIN Localidades AS LF ON F.FacCdCiudad=LF.IdLocal LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep
 LEFT JOIN Companias AS CR ON FR.IdCiaRem=CR.IdCia  LEFT JOIN Sys_Um AS UMP ON FR.UndMed=UMP.UndMed
 LEFT JOIN ConcDiversos AS CDV ON FR.CdConcepto=CDV.IdConcepto  LEFT JOIN Agencias  AS A ON FR.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON FR.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON FR.CdSubCos=SC.IdSubCos
 LEFT JOIN Trn_TraRemesa AS R ON FR.TipRem=R.TipDoc AND FR.Remesa=R.NumOrden AND FR.IdCiaRem=R.IdCia
 LEFT JOIN Trn_TraRemAnexo AS RA ON FR.TipRem=RA.TipDoc AND FR.Remesa=RA.NumOrden AND FR.IdCiaRem=RA.IdCia
 LEFT JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero
 LEFT JOIN Vehiculos AS V ON FR.pVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Mercancias AS MC ON FR.CdMercancia=MC.IdMercancia
 WHERE FR.FechaFact BETWEEN @pmFechaIni AND @pmFechaFin AND FR.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.FacNitClie LIKE ISNULL(@pmIdCliente,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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,OP.VrTotalFletes 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,EGR.FecEgr
,ISNULL(NM.Cargos,0) AS MucNovCargos,ISNULL(NM.Deducciones,0) AS MucNovDeducc,ISNULL(NR.Cargos,0) AS RemNovCargos,ISNULL(NR.Deducciones,0) AS RemNovDeducc
 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 CantPago*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 CantPago*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 
--Novedades
LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE WHEN (TipoConc='PAGO' OR TipoConc='GASTO') THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE 0 END) AS Cargos
,SUM(CASE TipoConc WHEN 'DEDUCCION' THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE  0 END) AS Deducciones
FROM Trn_TraConceptos WHERE TipDoc='MUC' GROUP BY TipDoc,Documento,IdCia) AS NM ON M.TipDoc=NM.TipDoc AND M.Manifiesto=NM.Documento AND M.IdCia=NM.IdCia
LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE TipoConc WHEN 'CARGO' THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE 0 END) AS Cargos
,SUM(CASE TipoConc WHEN 'DEDUCCION' THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE  0 END) AS Deducciones
FROM Trn_TraConceptos WHERE TipDoc='RMT' GROUP BY TipDoc,Documento,IdCia) AS NR ON RM.TipRem=NR.TipDoc AND RM.Remesa=NR.Documento AND RM.IdCiaRem=NR.IdCia
--main query condition
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,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,DF.IdCia AS CdCia,Compania,Fecha,DF.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,DF.TipDoc AS TipoFac,DF.Factura AS NumFactura,IdCiaDoc,FecDoc
,IdCliente,T.RazonSocial AS NomCliente,DF.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,DF.VrDescuento AS VrDcto,DF.VrImpuesto AS VrIva,DF.VrRetencion AS VrRetFte,DF.VrReteICA AS VrRetIca,VrReteIVA
,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,DF.Cantidad AS CantTotal,BaseImp,BaseRet,VrReteCREE,TarifaRtc,CodTarRtc
,DF.IdVend AS CdVend,VN.RazonSocial AS Vendedor,TarifaCom,DF.IdLocEnv AS CdLocEnv,LE.Localidad AS CiudadEnvio,Pedido,IdCiaPed,Cotizacion,IdCiaCot
,FecPedido,Modalidad,ModdDev,TipCom,TipoCom,Comprobante,IdCiaCom,DF.Observacion AS Observ,TimeSys,IdCiaCrea,DF.IdUsuario AS CdUsuario,Usuario,Leyenda
--detalles
,Item,TipoReg,FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,Descripcion,D.Cantidad AS Cant,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,Volumen,UndVol,Cases,Cajas,Palets,CantPago
,D.TarifaIva AS DetTarifIva,D.VrImpuesto AS DetVrIva,TarifaDct,D.VrDescuento AS DetVrDcto,D.TarifaRet AS DetTarifRet,D.VrRetencion AS DetVrRetFte,D.TarifaIca AS DetTarifIca,D.VrReteIca AS DetVrRetIca,VrFaltante,D.Remision AS DetNumRemsion,DocCliente,Referencia1,Referencia2,Referencia3
,CdMercancia,DescripMcia,CdConcepto,CF.Concepto AS DetConcepto,CdCCosto,CCosto,D.CdSubCos AS CdSubCosto,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,CdAgencia,D.pVehiculo AS PlacaVeh,TipoAfiVehic,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.Anulado AS DetAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,NumPedRem,CiaPedRem
--Información del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_DevFcr AS DF INNER JOIN  Trn_TraFacRemesas AS D ON DF.TipDev=D.TipDoc  AND DF.Devolucion=D.Factura  AND DF.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON DF.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON DF.TipDev=TD.IdDoc
 INNER JOIN  Conceptos AS C ON DF.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON DF.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON DF.IdVend=VN.IdTercero
 INNER JOIN Terceros AS T ON DF.IdCliente=T.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  
 INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliente AS CLI ON DF.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 Agencias  AS A ON DF.IdAgencia=A.IdAgencia 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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
 LEFT JOIN  Localidades AS LE ON DF.IdLocEnv=LE.IdLocal   LEFT JOIN TiposCom AS TCM ON DF.TipCom=TCM.IdCom  
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Mercancias AS MC ON D.CdMercancia=MC.IdMercancia LEFT JOIN  ConcDiversos AS CF ON D.CdConcepto=CF.IdConcepto
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 WHERE DF.TipDev=@pmTipDev AND DF.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND DF.IdCia=@pmIdCia
 ORDER BY  DF.Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrRelTraDet] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT TipDev,Devolucion,DF.IdCia AS CdCia,Compania,Fecha,DF.IdConcepto AS CdConcepto,C.Concepto AS ConcDesc,DF.TipDoc AS TipoFac,DF.Factura AS NumFactura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente
,DF.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,DF.VrDescuento AS VrDcto,DF.VrImpuesto AS VrIva,DF.VrRetencion AS VrRetFte,DF.VrReteICA AS VrRetIca,VrReteIVA,VrFletes,VrOtros,VrCargos
,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,DF.Cantidad AS CantTotal,CantPuntos,BaseImp,BaseRet
,DF.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,DF.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,Pedido,IdCiaPed,Cotizacion,IdCiaCot,FecPedido,VrReteCREE,TarifaRtc,CodTarRtc,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,DF.Observacion AS Observ,DF.IdEstado AS CdEstado
,TimeSys,IdCiaCrea,DF.IdUsuario AS IdUsuari,Usuario
--detalles
,Item,TipoReg,FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,FecRemesa,Descripcion,D.Cantidad AS Cant,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,Volumen,UndVol,Cases,Cajas,Palets,CantPago
,D.TarifaIva AS DetTarifIva,D.VrImpuesto AS DetVrIva,TarifaDct,D.VrDescuento AS DetVrDcto,D.TarifaRet AS DetTarifRet,D.VrRetencion AS DetVrRetFte,D.TarifaIca AS DetTarifIca,D.VrReteIca AS DetVrRetIca,VrFaltante,D.Remision AS DetNumRemsion,DocCliente,Referencia1,Referencia2,Referencia3
,CdMercancia,DescripMcia,CdConcepto,CF.Concepto AS DetConcepto,CdCCosto,CCosto,D.CdSubCos AS CdSubCosto,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,CdAgencia,D.pVehiculo AS PlacaVeh,TipoAfiVehic,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.Anulado AS DetAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,NumPedRem,CiaPedRem
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_DevFcr AS DF  INNER JOIN  Trn_TraFacRemesas AS D ON DF.TipDev=D.TipDoc  AND DF.Devolucion=D.Factura  AND DF.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON DF.IdCia=CN.IdCia   INNER JOIN Terceros AS T ON DF.IdCliente=T.IdTercero 
 INNER JOIN  Conceptos AS C ON DF.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON DF.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON DF.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON DF.IdAgencia=A.IdAgencia 
 INNER JOIN TercCliente AS CLI ON DF.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 Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 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
 LEFT JOIN  Localidades AS LE ON DF.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Mercancias AS MC ON D.CdMercancia=MC.IdMercancia LEFT JOIN  ConcDiversos AS CF ON D.CdConcepto=CF.IdConcepto
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 WHERE  DF.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  DF.TipDev LIKE ISNULL(@pmTipDev,'%')
 AND DF.IdCia LIKE ISNULL(@pmIdCia,'%%') AND DF.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND DF.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND DF.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY DF.IdCia,DF.Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraFacRemesasItem] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Factura,IdCia,Item,TipoReg,FechaFact,TipRem,Remesa,IdCiaRem,ItemRem,FecRemesa,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets
,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero
,CdAgencia,pVehiculo,TipoAfiVehic,IdOrigen,IdDestino,Anulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,CantPago
 FROM Trn_TraFacRemesas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraFacRemesas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipoReg INT,@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemesa INT,@pmIdCiaRem CHAR(2),@pmItemRem INT,@pmFecRemesa SMALLDATETIME,@pmDescripcion VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrCosto MONEY,@pmUndTarifa VARCHAR(10),@pmUndCosto VARCHAR(10),@pmUnidades DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmVolumen DECIMAL(14,4),@pmUndVol VARCHAR(10)
,@pmCases INT,@pmCajas INT,@pmPalets INT,@pmTarifaIva DECIMAL(14,4),@pmVrImpuesto MONEY,@pmTarifaDct DECIMAL(14,4),@pmVrDescuento MONEY,@pmTarifaRet DECIMAL(14,4),@pmVrRetencion MONEY,@pmTarifaIca DECIMAL(14,4),@pmVrReteIca MONEY,@pmVrFaltante MONEY,@pmRemision DECIMAL(18,2),@pmDocCliente VARCHAR(30),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmCdMercancia VARCHAR(16),@pmCdConcepto VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16)
,@pmNitTercero VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmAnulado INT,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME,@pmCantidadFalt DECIMAL(14,4),@pmUnidadFalt VARCHAR(10),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarDct VARCHAR(4),@pmNumPedRem INT,@pmCiaPedRem CHAR(2),@pmVrDeclMcia MONEY,@pmTarifaSeg DECIMAL(14,4),@pmVrSeguroRem MONEY,@pmNitAsegurad VARCHAR(16),@pmCantPago DECIMAL(14,4)
 AS 
 INSERT INTO Trn_TraFacRemesas (TipDoc,Factura,IdCia,Item,TipoReg,FechaFact,TipRem,Remesa,IdCiaRem,ItemRem,FecRemesa,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero,CdAgencia,pVehiculo,TipoAfiVehic,IdOrigen,IdDestino
,Anulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,CantPago)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmTipoReg,@pmFechaFact,@pmTipRem,@pmRemesa,@pmIdCiaRem,@pmItemRem,@pmFecRemesa,@pmDescripcion,@pmCantidad,@pmVrUnitario,@pmVrCosto,@pmUndTarifa,@pmUndCosto,@pmUnidades,@pmPesoNeto,@pmUndMed,@pmVolumen,@pmUndVol,@pmCases,@pmCajas,@pmPalets,@pmTarifaIva,@pmVrImpuesto,@pmTarifaDct,@pmVrDescuento,@pmTarifaRet,@pmVrRetencion,@pmTarifaIca,@pmVrReteIca,@pmVrFaltante,@pmRemision,@pmDocCliente,@pmReferencia1,@pmReferencia2,@pmReferencia3
,@pmCdMercancia,@pmCdConcepto,@pmCdCCosto,@pmCdSubCos,@pmNitTercero,@pmCdAgencia,@pmpVehiculo,@pmTipoAfiVehic,@pmIdOrigen,@pmIdDestino,@pmAnulado,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmFecDocRef,@pmCantidadFalt,@pmUnidadFalt,@pmCodTarRet,@pmCodTarIca,@pmCodTarDct,@pmNumPedRem,@pmCiaPedRem,@pmVrDeclMcia,@pmTarifaSeg,@pmVrSeguroRem,@pmNitAsegurad,@pmCantPago)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemFact_Sel] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraRemFact (tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets
,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia
,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido
,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago)
 SELECT @pmtmNumero,Item,TipoReg,TipRem,Remesa,IdCiaRem,ItemRem,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets
,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero,CdAgencia
,pVehiculo,TipoAfiVehic,'0','','',IdOrigen,'0','','',IdDestino,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,0,FecRemesa,0,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,0,TipDocRef,NumDocRef,IdCiaRef,CantPago
 FROM Trn_TraFacRemesas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemFact] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipoReg INT,@pmtmTipRem VARCHAR(3),@pmtmRemesa INT,@pmtmIdCiaRem CHAR(2),@pmtmItemRem INT,@pmtmDescripcion VARCHAR(250),@pmtmCantidad DECIMAL(14,4),@pmtmVrUnitario MONEY,@pmtmVrCosto MONEY,@pmtmUndTarifa VARCHAR(10),@pmtmUndCosto VARCHAR(10),@pmtmUnidades DECIMAL(14,4),@pmtmPesoNeto DECIMAL(14,4),@pmtmUndMed VARCHAR(10),@pmtmVolumen DECIMAL(14,4)
,@pmtmUndVol VARCHAR(10),@pmtmCases INT,@pmtmCajas INT,@pmtmPalets INT,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifaDct DECIMAL(14,4),@pmtmVrDscto MONEY,@pmtmTarifaRet DECIMAL(14,4),@pmtmVrRetFte MONEY,@pmtmTarifaIca DECIMAL(14,4),@pmtmVrReteIca MONEY,@pmtmVrFaltante MONEY,@pmtmRemision DECIMAL(18,2),@pmtmDocCliente VARCHAR(30),@pmtmReferencia1 VARCHAR(50),@pmtmReferencia2 VARCHAR(50),@pmtmReferencia3 VARCHAR(50)
,@pmtmCdMercancia VARCHAR(16),@pmtmCdConc VARCHAR(4),@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmNitTercero VARCHAR(16),@pmtmCdAgencia VARCHAR(16),@pmtmpVehiculo VARCHAR(10),@pmtmTipoVehic VARCHAR(10),@pmtmNitRemite VARCHAR(16),@pmtmRemitente VARCHAR(250),@pmtmDirOrigen VARCHAR(250),@pmtmIdOrigen VARCHAR(8),@pmtmNitDestntario VARCHAR(16),@pmtmDestinatario VARCHAR(250),@pmtmDirDestino VARCHAR(250)
,@pmtmIdDestino VARCHAR(8),@pmtmCantFalt DECIMAL(14,4),@pmtmUnidadFalt VARCHAR(10),@pmtmCdTarRet VARCHAR(4),@pmtmCdTarIca VARCHAR(4),@pmtmCdTarDct VARCHAR(4),@pmtmPedRem INT,@pmtmCiaPed CHAR(2),@pmtmEtdoFact INT,@pmtmFecRemesa SMALLDATETIME,@pmtmIntCumplido INT,@pmtmVrDeclarado MONEY,@pmtmTarifSeguro DECIMAL(14,4),@pmtmVrSeguro MONEY,@pmtmNitAsegurad VARCHAR(16),@pmtmCostoCump MONEY,@pmtmTipoOrd VARCHAR(3),@pmtmNumOrden INT,@pmtmIdCiaOrd CHAR(2),@pmtmCantPago DECIMAL(14,4)
 AS 
 INSERT INTO tm_TraRemFact (tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia
,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipoReg,@pmtmTipRem,@pmtmRemesa,@pmtmIdCiaRem,@pmtmItemRem,@pmtmDescripcion,@pmtmCantidad,@pmtmVrUnitario,@pmtmVrCosto,@pmtmUndTarifa,@pmtmUndCosto,@pmtmUnidades,@pmtmPesoNeto,@pmtmUndMed,@pmtmVolumen,@pmtmUndVol,@pmtmCases,@pmtmCajas,@pmtmPalets,@pmtmTarifaIva,@pmtmVrIva,@pmtmTarifaDct,@pmtmVrDscto,@pmtmTarifaRet,@pmtmVrRetFte,@pmtmTarifaIca,@pmtmVrReteIca,@pmtmVrFaltante,@pmtmRemision
,@pmtmDocCliente,@pmtmReferencia1,@pmtmReferencia2,@pmtmReferencia3,@pmtmCdMercancia,@pmtmCdConc,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmNitTercero,@pmtmCdAgencia,@pmtmpVehiculo,@pmtmTipoVehic,@pmtmNitRemite,@pmtmRemitente,@pmtmDirOrigen,@pmtmIdOrigen,@pmtmNitDestntario,@pmtmDestinatario,@pmtmDirDestino,@pmtmIdDestino,@pmtmCantFalt,@pmtmUnidadFalt,@pmtmCdTarRet,@pmtmCdTarIca,@pmtmCdTarDct,@pmtmPedRem,@pmtmCiaPed,@pmtmEtdoFact,@pmtmFecRemesa,@pmtmIntCumplido
,@pmtmVrDeclarado,@pmtmTarifSeguro,@pmtmVrSeguro,@pmtmNitAsegurad,@pmtmCostoCump,@pmtmTipoOrd,@pmtmNumOrden,@pmtmIdCiaOrd,@pmtmCantPago)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFact] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen
,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago
 FROM tm_TraRemFact
 WHERE tmNumero=@pmtmNumero AND tmItem=@pmtmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFactLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen
,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCantPago,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd
 FROM tm_TraRemFact
 WHERE tmNumero=@pmtmNumero
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE 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,RA.TipOrdMud,RA.NumInvent,RA.IdCiaInv AS CdCiaInv,R.nRemolque AS RemRemolque
--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 (CantPago*VrCosto)*-1 ELSE CantPago*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
GO