ALTER PROCEDURE [dbo].[paQryTraLiquidaFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT LQ.TipDoc AS TipLiq,TipoDoc,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,CN.Compania AS NomCompania,LQ.Fecha AS FechaLiq,LQ.IdConcepto AS CdConcepto,Concepto
,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,VrTotal,LQ.CxPagar AS CPagar,LQ.TipCom AS CdTipCom,TipoCom,LQ.Comprobante AS NumComp,LQ.IdCiaCom AS CdCiaComp
,LQ.Anulado AS Anuldo,LQ.NumDev AS NDevolucion,LQ.FecDev AS FechDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida
,LQ.TimeSys AS FechaAdd,LQ.FecUpdate AS FechaAct,LQ.IdCiaCrea AS CdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos de ordenes de pago
,LP.TipOdp AS TipOrden,LP.OrdPago AS NumOrden,LP.IdCiaOdp AS CdCiaOdp,COP.Compania AS NomCiaOrden,OP.Fecha AS FechaOdp,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif
,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros
,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto,VrAbonado,TotAbonado,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total
,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,NumCheque,O.Referencia AS Referncia,EdoLiqCausac
,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,M.Fecha AS FecManif,VrApos,VrFam,VrFsc,VrFgi,VrTarApos ,VrTarFam ,VrTarFGi,VrTarFsc,VrBaseApos ,VrBaseFam,VrBaseFgi,VrBaseFsc,TPV.NumCuenta AS NumCtaProveedor,CTA.ClaseCuenta
,TPV.NitContac,TPV.NomContac,BA.Banco 
 FROM Trn_TraLiquida AS LQ INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON LQ.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON LQ.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario 
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Trn_TraLiquidaOdp AS LP ON LQ.TipDoc=LP.TipDoc AND LQ.Liquidacion=LP.Liquidacion AND LQ.IdCia=LP.IdCia
 INNER JOIN Trn_TraOrdenPago AS OP ON LP.TipOdp=OP.TipDoc AND LP.OrdPago=OP.OrdPago AND LP.IdCiaOdp=OP.IdCia
 INNER JOIN Trn_TraOrdenManif AS O ON LP.TipOdp=O.TipDoc AND LP.OrdPago=O.OrdPago AND LP.IdCiaOdp=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Vehiculos AS V ON O.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
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero
 INNER JOIN Companias AS COP ON LP.IdCiaOdp=COP.IdCia
 INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN (SELECT TipDoc,Documento,IdCiaDoc,SUM(VrAbonado) AS TotAbonado FROM Trn_ComFactura WHERE TipFac='ODP' AND TipDoc='ODP' GROUP BY TipDoc,Documento,IdCiaDoc)
 AS ABO ON LP.TipOdp=ABO.TipDoc AND LP.OrdPago=ABO.Documento AND LP.IdCiaOdp=ABO.IdCiaDoc
 --fondos-conceptos
 LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE WHEN Concepto='FLE' AND Factura='FGI' OR Concepto='FLE' AND CodConc='FGI' THEN Valor ELSE 0 END ) AS VrFgi
 ,MAX(CASE WHEN Concepto='FLE' AND Factura='FGI' OR Concepto='FLE' AND CodConc='FGI' THEN Vrtarifa ELSE 0 END ) as VrTarFGi
 ,MAX(CASE WHEN Concepto='FLE' AND Factura='FGI' OR Concepto='FLE' AND CodConc='FGI' THEN VrBase ELSE 0 END ) as VrBaseFgi,SUM(CASE WHEN Concepto='FLE' AND Factura='FAM' OR Concepto='FLE' AND CodConc='FAM' THEN Valor ELSE 0 END ) AS VrFam
 ,MAX(CASE WHEN Concepto='FLE' AND Factura='FAM' OR Concepto='FLE' AND CodConc='FAM' THEN Vrtarifa ELSE 0 END ) as VrTarFam
 ,MAX(CASE WHEN Concepto='FLE' AND Factura='FAM' OR Concepto='FLE' AND CodConc='FAM' THEN VrBase ELSE 0 END ) as VrBaseFam,SUM(CASE WHEN Concepto='FLE' AND Factura='FSC' OR Concepto='FLE' AND CodConc='FSC' THEN Valor ELSE 0 END ) AS VrFsc
 ,MAX(CASE WHEN Concepto='FLE' AND Factura='FSC' OR Concepto='FLE' AND CodConc='FSC' THEN Vrtarifa ELSE 0 END ) as VrTarFsc,MAX(CASE WHEN Concepto='FLE' AND Factura='FSC'OR Concepto='FLE' AND CodConc='FSC' THEN VrBase ELSE 0 END ) as VrBaseFsc
 ,SUM(CASE WHEN Concepto='OTR' AND Factura='APOS' OR Concepto='OTR' AND CodConc='APOS' THEN Valor ELSE 0 END) AS VrApos,MAX(CASE WHEN Concepto='OTR' AND Factura='APOS' OR Concepto='OTR' AND CodConc='APOS' THEN Vrtarifa ELSE 0 END ) as VrTarApos
 ,MAX(CASE WHEN Concepto='OTR' AND Factura='APOS' OR Concepto='OTR' AND CodConc='APOS' THEN VrBase ELSE 0 END ) as VrBaseApos
 FROM Trn_DetCuentas WHERE TipDoc='ODP' GROUP BY TipDoc,Documento,IdCia) AS FCN ON LP.TipOdp=FCN.TipDoc AND LP.OrdPago=FCN.Documento AND LP.IdCiaOdp=FCN.IdCia
 LEFT JOIN TercProvee TPV ON LQ.IdPoseedor=TPV.IdProv  LEFT JOIN ClaseCta CTA ON TPV.IdClase=CTA.IdClase 
 LEFT JOIN Bancos BA ON TPV.IdBanco =BA.IdBanco 
 WHERE LQ.TipDoc=@pmTipDoc AND LQ.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND LQ.IdCia=@pmIdCia