INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCGTC','TRAINF','TRA',29,'Guías de Transporte','FRMLCGTC','SSSSSSSSSSSSS',0,'')
GO
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('GTC','01','Consecutivo de Guías','LtGtc.rpt',1,2,1,0,1,'paQryTraGuiasLta','','Guías de Transporte')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT G.TipDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,T.TipoId,G.IdCliente,T.Dv,T.RazonSocial AS Nom_Cliente,G.IdAgencia,A.Agencia AS NomAgencia
,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.TelRemitente,G.emlRemitente,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.TelDestinatario,G.emlDestinatario
,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,DP.Departamento AS DptoOrigen,G.IdLocDestino,LD.Localidad AS CiudadDestino,DD.Departamento AS DptoDestino,G.NomContacto,G.TelContacto,G.emlContacto
,G.IdResponsable,G.NomResponsable,G.VrTotal,G.VrDeclarado AS VrTotDeclarado,G.VrSeguro AS VrTotSeguro,G.VrCargos,G.Cantidad AS Cant_Total,G.PesoTotal,G.TipRem,G.NumRemesa,G.IdCiaRem,G.EstFactura,G.TipFac,G.NumFactura,G.IdCiaFac,G.FechaFac
,G.EstLiquida,G.TipLiq,G.NumLiquida,G.IdCiaLiq,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TC.TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado,G.FechaCrea,G.IdUsuario,Usuario
--datos de factura
,GF.FacCantidad,GF.FacValorTotal,GF.FacVrSeguro,GF.FacVrCargos,GF.FacUnidades,GF.FacPesoNeto,GF.FacVolumen
,GM.NumManif,GM.CiaManif,MC.IdVehiculo,MC.TipoAfiVehic,MC.IdConductor,NC.RazonSocial AS NomConductor,MC.IdPoseedor,NP.RazonSocial AS NomPoseedor,MC.EstOrden,MC.TipOdp,MC.OrdPago,MC.IdCiaOdp
,LG.LiqVrGuias,LG.LiqVrSeguro,LG.LiqVrCargos,LG.LiqCantidad,LG.LiqPesoTotal
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero
 INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DP ON LO.IdDep=DP.IdDep
 INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 LEFT JOIN Conceptos AS C ON G.CdConcepto=C.IdConcepto LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN TiposCom AS TC ON G.TipCom=TC.IdCom
--subconsulta facturacion
 LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,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 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 (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacVrSeguro
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN CostoAdicTbdo*-1 ELSE CostoAdicTbdo END) AS FacVrCargos
,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 WHERE TipRem='GTC' GROUP BY TipRem,Remesa,IdCiaRem) AS GF ON G.TipDoc=GF.TipRem AND G.NumGuia=GF.Remesa AND G.IdCia=GF.IdCiaRem
--subconsulta No. manifiesto
LEFT JOIN (SELECT RG.TipDoc,RG.NumGuia,RG.IdCiaGuia,MAX(MR.Manifiesto) AS NumManif,MAX(MR.IdCia) AS CiaManif
 FROM Trn_TraRemGuias AS RG INNER JOIN Trn_TraManifRem AS MR ON RG.TipRem=MR.TipRem AND RG.Remesa=MR.Remesa AND RG.IdCia=MR.IdCiaRem
 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 GROUP BY RG.TipDoc,RG.NumGuia,RG.IdCiaGuia) AS GM ON G.TipDoc=GM.TipDoc AND G.NumGuia=GM.NumGuia AND G.IdCia=GM.IdCiaGuia
--subconsulta manifiestos
LEFT JOIN (SELECT M.TipDoc,M.Manifiesto,M.IdCia,M.Fecha,M.IdOrigen,M.IdDestino,M.IdVehiculo,M.TipoAfiVehic,M.IdConductor,M.IdPropietario,M.IdPoseedor,M.EstCumplido,M.Cumplido,M.IdCiaCump,M.EstOrden,M.TipOdp,M.OrdPago,M.IdCiaOdp,M.Observacion
,O.Fecha AS FecOPago,O.TipCom,O.Comprobante,O.IdCiaCom,O.Anulado AS OdpAnulado,OM.VrTotalFletes,OM.VrRetencion,OM.VrReteIca,OM.VrAnticipos,OM.VrFaltantes,OM.VrSeguros,OM.VrFondos,OM.VrAportes,OM.VrOtrosDctos,OM.VrImpuestos,OM.VrOtrosPagos
,OM.VrNeto,OM.TarifaTabla,OM.TarifaFlete,OM.VrRemesas,OM.VrRecCaja,OM.VrSeguroRc
FROM Trn_TraManifiesto AS M LEFT JOIN Trn_TraOrdenPago AS O ON M.TipOdp=O.TipDoc AND M.OrdPago=O.OrdPago AND M.IdCiaOdp=O.IdCia
LEFT JOIN Trn_TraOrdenManif AS OM ON M.TipOdp=OM.TipDoc AND M.OrdPago=OM.OrdPago AND M.IdCiaOdp=OM.IdCia WHERE M.Anulado=0) AS MC ON GM.NumManif=MC.Manifiesto AND GM.CiaManif=MC.IdCia
--subconsulta Liq. contraentrega
LEFT JOIN (SELECT TipGui,NumGuia,IdCiaGuia,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN VrLiquidado WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN VrLiquidado*-1 ELSE 0 END) AS LiqVrGuias
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN VrSeguro WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN VrSeguro*-1 ELSE 0 END) AS LiqVrSeguro
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN VrCargos WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN VrCargos*-1 ELSE 0 END) AS LiqVrCargos
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN Cantidad WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN Cantidad*-1 ELSE 0 END) AS LiqCantidad
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN PesoTotal WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN PesoTotal*-1 ELSE 0 END) AS LiqPesoTotal
FROM Trn_TraOrdenGuias GROUP BY TipGui,NumGuia,IdCiaGuia) AS LG ON G.TipDoc=LG.TipGui AND G.NumGuia=LG.NumGuia AND G.IdCia=LG.IdCiaGuia
LEFT JOIN Terceros AS NP ON MC.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS NC ON MC.IdConductor=NC.IdTercero
WHERE G.TipDoc=@pmTipDoc AND G.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (G.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO