ALTER TABLE Trn_TraGuias ADD NumRemision VARCHAR(20)
GO
ALTER TABLE Trn_TraOrdenGuias ADD Cod_Estado VARCHAR(4)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraGuias]
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.NumRemision,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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmNomRemitente VARCHAR(150),@pmIdDestinatario VARCHAR(16),@pmNomDestinatario VARCHAR(150),@pmDirOrigen VARCHAR(250),@pmIdLocOrigen VARCHAR(8),@pmDirDestino VARCHAR(250),@pmIdLocDestino VARCHAR(8),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmTelRemitente VARCHAR(30),@pmemlRemitente VARCHAR(150),@pmTelDestinatario VARCHAR(30),@pmemlDestinatario VARCHAR(150),@pmIdResponsable VARCHAR(16),@pmNomResponsable VARCHAR(150),@pmVrTotal MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmVrCargos MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmTipRem VARCHAR(3)
,@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFac VARCHAR(3),@pmNumFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmCdConcepto VARCHAR(4),@pmCdForma VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmEstLiquida INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmIdCiaLiq CHAR(2),@pmNumRemision VARCHAR(20),@pmFechaModif SMALLDATETIME
 AS 
 UPDATE Trn_TraGuias SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecVigencia=@pmFecVigencia,Modalidad=@pmModalidad,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdRemitente=@pmIdRemitente,NomRemitente=@pmNomRemitente,IdDestinatario=@pmIdDestinatario,NomDestinatario=@pmNomDestinatario,DirOrigen=@pmDirOrigen,IdLocOrigen=@pmIdLocOrigen,DirDestino=@pmDirDestino,IdLocDestino=@pmIdLocDestino,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,TelRemitente=@pmTelRemitente,emlRemitente=@pmemlRemitente,TelDestinatario=@pmTelDestinatario,emlDestinatario=@pmemlDestinatario,IdResponsable=@pmIdResponsable,NomResponsable=@pmNomResponsable,VrTotal=@pmVrTotal,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,VrCargos=@pmVrCargos,Cantidad=@pmCantidad,PesoTotal=@pmPesoTotal,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem
,EstFactura=@pmEstFactura,TipFac=@pmTipFac,NumFactura=@pmNumFactura,IdCiaFac=@pmIdCiaFac,FechaFac=@pmFechaFac,EstLiquida=@pmEstLiquida,TipLiq=@pmTipLiq,NumLiquida=@pmNumLiquida,IdCiaLiq=@pmIdCiaLiq,NumRemision=@pmNumRemision
,CdConcepto=@pmCdConcepto,CdForma=@pmCdForma,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FechaModif=@pmFechaModif
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmNomRemitente VARCHAR(150),@pmIdDestinatario VARCHAR(16),@pmNomDestinatario VARCHAR(150),@pmDirOrigen VARCHAR(250),@pmIdLocOrigen VARCHAR(8),@pmDirDestino VARCHAR(250),@pmIdLocDestino VARCHAR(8),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmTelRemitente VARCHAR(30),@pmemlRemitente VARCHAR(150),@pmTelDestinatario VARCHAR(30),@pmemlDestinatario VARCHAR(150),@pmIdResponsable VARCHAR(16),@pmNomResponsable VARCHAR(150),@pmVrTotal MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmVrCargos MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmTipRem VARCHAR(3)
,@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFac VARCHAR(3),@pmNumFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmCdConcepto VARCHAR(4),@pmCdForma VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmEstLiquida INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmIdCiaLiq CHAR(2),@pmNumRemision VARCHAR(20),@pmOrigenAdd VARCHAR(10),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraGuias (TipDoc,NumGuia,IdCia,Fecha,FecDespacho,FecVigencia,Modalidad,IdCliente,IdAgencia,IdRemitente,NomRemitente,IdDestinatario,NomDestinatario,DirOrigen,IdLocOrigen,DirDestino,IdLocDestino,NomContacto,TelContacto,emlContacto,TelRemitente,emlRemitente,TelDestinatario,emlDestinatario,IdResponsable,NomResponsable,VrTotal,VrDeclarado,VrSeguro,VrCargos,Cantidad,PesoTotal,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFac,NumFactura,IdCiaFac,FechaFac,EstLiquida,TipLiq,NumLiquida,IdCiaLiq,CdConcepto,CdForma,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,IdUsuario,NumRemision)
 VALUES (@pmTipDoc,@pmNumGuia,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecVigencia,@pmModalidad,@pmIdCliente,@pmIdAgencia,@pmIdRemitente,@pmNomRemitente,@pmIdDestinatario,@pmNomDestinatario,@pmDirOrigen,@pmIdLocOrigen,@pmDirDestino,@pmIdLocDestino,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmTelRemitente,@pmemlRemitente,@pmTelDestinatario,@pmemlDestinatario,@pmIdResponsable,@pmNomResponsable,@pmVrTotal,@pmVrDeclarado,@pmVrSeguro,@pmVrCargos,@pmCantidad,@pmPesoTotal,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFac,@pmNumFactura,@pmIdCiaFac,@pmFechaFac,@pmEstLiquida,@pmTipLiq,@pmNumLiquida,@pmIdCiaLiq,@pmCdConcepto,@pmCdForma,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmFechaCrea,@pmIdCiaCrea,@pmIdUsuario,@pmNumRemision)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumGuia,IdCia,Fecha,FecDespacho,FecVigencia,Modalidad,IdCliente,IdAgencia,IdRemitente,NomRemitente,IdDestinatario,NomDestinatario,DirOrigen,IdLocOrigen,DirDestino,IdLocDestino,NomContacto,TelContacto,emlContacto,TelRemitente,emlRemitente,TelDestinatario,emlDestinatario
,IdResponsable,NomResponsable,VrTotal,VrDeclarado,VrSeguro,VrCargos,Cantidad,PesoTotal,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFac,NumFactura,IdCiaFac,FechaFac,EstLiquida,TipLiq,NumLiquida,IdCiaLiq,CdConcepto,CdForma,TipCom,Comprobante,IdCiaCom,NumRemision
,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,FechaCrea,FechaModif,IdCiaCrea,IdUsuario
 FROM Trn_TraGuias
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasFmt] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT G.TipDoc,TipoDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,G.IdCliente,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.NumRemision,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado
,G.FechaCrea,G.FechaModif,G.IdCiaCrea,G.IdUsuario,Usuario,G.OrigenAdd
--Detalle
,D.Item,D.IdMercancia,D.DescripcionMcia,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmPeso,D.dmsAlto,D.dmsAncho,D.dmsLargo,D.Volumen,D.UndVol,D.IdUnd,UM.Unidad AS UndMedida,D.IdEmp,EP.Empaque--,D.IdNat,D.IdTmcia,D.IdMnjo
,D.VrUnitario,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CantFactura,D.VrFactura,D.CdConcepto AS CdConcFactura,DC.Concepto AS ConcFactura
--datos del tercero
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc
 INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraGuiaMcia AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 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 TiposCom AS TC ON G.TipCom=TC.IdCom
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal
 LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 LEFT JOIN ConcDiversos AS DC ON D.CdConcepto=DC.IdConcepto 
 WHERE G.TipDoc=@pmTipDoc AND G.NumGuia BETWEEN @pmNumGuiaIni AND @pmNumGuiaFin AND G.IdCia=@pmIdCia
GO