if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTarifasFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifasServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTarifasServ]
GO
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].[paInsTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifasFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasFletesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifasFletesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifasServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifasServLta]
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].[paQryTraDevLgoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTarifasFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifasServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTarifasServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposEnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposEnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposEnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTiposEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelTiposEnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTercCliente_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercClienteLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercClienteLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAgencias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAgencias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgencias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgenciasCod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgenciasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoDetFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoDetFac]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServFac] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
AS
SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,T.TipoId AS ClieTipoId,O.IdCliente,T.Dv AS ClieDv,T.RazonSocial AS NomCliente
,O.IdVehiculo AS PlacaVeh,NumVeh,O.TipoAfiVehic,O.Modalidad,TipoOrden,O.VrTotal AS ValorTotal,O.VrCosto AS TotalTercero,O.Cantidad AS CantTotal,O.VrAnticipo
,D.Item,D.IdMercancia AS CdMercancia,D.Descripcion,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.TarifaPago,D.DocCliente
,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,O.CdRuta,Ruta,O.IdOrigen AS CdOrigen,LO.Localidad AS NomOrigen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino,O.IdAgencia AS CdAgencia,Agencia
,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,NomEstacion,TipoResiduo,O.RemCliente,NomRecibido,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,O.NumRemesa,O.CdCiaRem,kmtInicial,kmtFinal,Cantkms,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,ED.Estado,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--cumplido
,EstCumplido,CM.Cumplido AS NumCumplido,CM.IdCia AS CiaCump,CM.Fecha AS FechaCump,CM.DescServicio AS CumpServicio,CM.Observacion AS ObservCump
,CM.IdMercancia AS CdMercanciaCump,CM.Descripcion AS DescripCump,CM.Cantidad AS CantCump,CM.UndMed AS UndMedCump,UMC.Unidad AS UnidadCump,CM.VrUnitario AS TarifCump
,CM.TarifaPago AS TarifPagoCump
--liquidacion
,O.EstOrden,O.TipLiq,O.NumLiquida,O.CdCiaLiq,L.Fecha AS FecLiquida,L.VrTotal AS LiqTotal,L.VrAnticipo AS LiqAnticipo,L.VrIva AS LiqIva,L.VrRetencion AS LiqRetencion
,L.VrReteIca,L.VrReteCREE,L.VrReteIva,L.VrOtros,L.VrDescuento,L.VrNeto,L.Cantidad AS LiqCantidad,L.TarifaPago AS LiqTarifaPago,L.TarifaImp,L.TarifaRet,L.TarifaIca,L.TarifaRiv
,L.Referencia,L.VrGastos
--Factura
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,FactFecha,FactCant,FactTarifClie,FactTotal,FactIva,FactTarifIVA,FactCosto,FactIdCli,TCF.RazonSocial AS FactNomCliente
--Inf vehiculo
,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LC.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,Modelo,NumMotor,SerieChasis,NumSerie,NitEmpresa,NE.RazonSocial AS VehNomEmpresa
,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
FROM Trn_TraOrdenServ AS O INNER JOIN Trn_TraOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal
INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
LEFT JOIN Trn_TraOrdenLiq AS L ON O.TipLiq=L.TipDoc AND O.NumLiquida=L.Liquidacion AND O.CdCiaLiq=L.IdCia
LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
--Subconsulta cumplido
LEFT JOIN (SELECT C.Cumplido,C.IdCia,C.Fecha,C.TipOds,C.NumOrden,C.IdCiaOds,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,C.DescServicio
,C.RemCliente,C.Funcionario,C.Observacion,D.IdMercancia,D.Descripcion,D.Item,D.Cantidad,D.UndMed,D.VrUnitario,D.TarifaPago,D.DocCliente
FROM Trn_TraOrdenCum AS C INNER JOIN Trn_TraOrdenCumDet AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.IdCia=D.IdCia
WHERE C.Anulado=0) AS CM ON O.TipDoc=CM.TipOds AND O.NumOrden=CM.NumOrden AND O.IdCia=CM.IdCiaOds AND D.Item=CM.Item
LEFT JOIN Sys_Um AS UMC ON CM.UndMed=UMC.UndMed
--Subconsulta factura
LEFT JOIN (SELECT F.TipDoc,F.Factura,F.IdCia,D.TipRem,D.Remesa,D.IdCiaRem,F.IdCliente AS FactIdCli,F.Fecha AS FactFecha,SUM(D.Cantidad) AS FactCant,SUM(D.VrUnitario*D.Cantidad) AS FactTotal
,SUM(D.VrCosto*D.Cantidad) AS FactCosto,SUM(D.VrImpuesto) AS FactIva,MAX(D.TarifaIva) AS FactTarifIVA,MAX(D.VrUnitario) AS FactTarifClie,MAX(D.VrCosto) AS FactTarifTerc
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
WHERE F.Anulado=0 GROUP BY F.TipDoc,F.Factura,F.IdCia,D.TipRem,D.Remesa,D.IdCiaRem,F.IdCliente,F.Fecha) AS FS ON O.TipFact=FS.TipDoc AND O.NumFactura=FS.Factura AND O.CdCiaFact=FS.IdCia
AND O.TipDoc=FS.TipRem AND O.NumOrden=FS.Remesa AND O.IdCia=FS.IdCiaRem
LEFT JOIN Terceros AS TCF ON FS.FactIdCli=TCF.IdTercero
WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario,Leyenda
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND L.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,TipOds,L.NumOrden,IdCiaOds,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,L.VrConcCargos,L.VrConcDed,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.Referencia,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo  LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
 AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,Cantkms,EstOrden,TipLiq,NumLiquida,CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea,OrigenAdd,O.IdUsuario AS CdUsuario,Usuario
--Cliente
,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.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.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.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.Modalidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.Cantidad AS CantOrden,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrConcCargos,VrConcDed,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,L.VrConcCargos,L.VrConcDed,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.Referencia,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo  LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
 AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,Cantkms,EstOrden,TipLiq,NumLiquida,CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea,OrigenAdd,O.IdUsuario AS CdUsuario,Usuario
--Cliente
,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.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.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.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
--detalles
,Item,D.IdMercancia AS CdMercancia,DescripMcia,D.Descripcion,D.Cantidad,D.UndMed,Unidad,D.VrUnitario,D.TarifaPago
,CantCump,TarifaCump,PagoCump,DocCliente
 FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed 
 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Mercancias AS MC ON D.IdMercancia=MC.IdMercancia
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen AS CdOrigen,LO.Localidad AS NomOrigen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino
,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum,EstFactura,TipFact,NumFactura,CdCiaFact
,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,kmtInicial,kmtFinal,Cantkms,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea
,OrigenAdd,O.IdUsuario AS CdUsuario,Usuario
--Detalles
,Item,D.IdMercancia AS CdMercancia,DescripMcia,D.Descripcion,D.Cantidad,D.UndMed,Unidad,D.VrUnitario,D.TarifaPago
,CantCump,TarifaCump,PagoCump,DocCliente
--Cliente
,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
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.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.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Mercancias AS MC ON D.IdMercancia=MC.IdMercancia
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,TipOds,L.NumOrden,IdCiaOds,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,L.VrConcCargos,L.VrConcDed,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.Referencia,CdPlazo,FechaVence,UndMed
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdDestino,Localidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem
,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrConcCargos,VrConcDed,VrNeto,L.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT NumOrden,O.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecRecibo,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdVehiculo AS PlacaVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,VrTotal,VrCosto,Cantidad,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino,LD.Localidad AS Destino
,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum
,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,OrigenAdd,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,kmtInicial,kmtFinal,Cantkms,O.IdCiaCrea AS CdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero
INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasFletesLta] @pmTipoTarifa VARCHAR(10)=Null,@pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null
,@pmNitCliente VARCHAR(16)=Null,@pmInactivo BIT=Null,@pmCdRuta VARCHAR(4)=Null
 AS 
 SELECT Numero,IdOrigen,O.Localidad AS CiuOrigen,IdDestino,D.Localidad AS CiuDestino,CdRuta,TarifClie,TarifPago,TarifTabla,IdMneda,T.UndMed AS Und_Med,Unidad
,NitCliente,RazonSocial,CdNat,Natlzaprod,CdTipoVeh,TipoVehiculo,CdRango,DescripRango,T.Inactivo AS Inactvo,UndTarifClie,UndTarifPago,CdMercancia,TipoAfiVehic
,TarifAfiliado,TarifTercero,TarifPropio,TipoOper,CdGrupo,GrupoProp,MargenMin,FechaCrea,T.IdUsuario AS CdUsuario,Usuario
,O.IdDep AS CdDep,DPO.Departamento AS DptoOrigen,D.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,TipoTarifa
 FROM TarifasFletes AS T INNER JOIN Localidades AS O ON T.IdOrigen=O.IdLocal INNER JOIN Localidades AS D ON T.IdDestino=D.IdLocal
 INNER JOIN Departamentos AS DPO ON O.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON D.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS TC ON T.NitCliente=TC.IdTercero LEFT JOIN Sys_Um AS UM ON T.UndMed=UM.UndMed 
 LEFT JOIN TiposNat AS N ON T.CdNat=N.IdNat LEFT JOIN TiposVeh AS TV ON T.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN RangosPeso AS R ON T.CdRango=R.IdRango LEFT JOIN GruposPro AS G ON T.CdGrupo=G.IdGrupo
 WHERE TipoTarifa LIKE ISNULL(@pmTipoTarifa,'%') AND  IdOrigen LIKE ISNULL(@pmIdOrigen,'%')
 AND IdDestino LIKE ISNULL(@pmIdDestino,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 AND CdRuta LIKE ISNULL(@pmCdRuta,'%')
 AND  (T.Inactivo=ISNULL(@pmInactivo,0) or T.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY O.Localidad,D.Localidad,Numero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTarifasFletes] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmNumero INT,@pmTipoTarifa VARCHAR(10),@pmTarifClie MONEY,@pmTarifPago MONEY,@pmTarifTabla MONEY,@pmIdMneda VARCHAR(5)
,@pmUndMed VARCHAR(10),@pmNitCliente VARCHAR(16),@pmCdNat VARCHAR(4),@pmCdTipoVeh VARCHAR(4),@pmCdRango VARCHAR(4)
,@pmUndTarifClie VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmCdMercancia VARCHAR(16),@pmTipoAfiVehic VARCHAR(10)
,@pmTarifAfiliado DECIMAL(16,6),@pmTarifTercero DECIMAL(16,6),@pmTarifPropio DECIMAL(16,6),@pmTipoOper VARCHAR(10),@pmCdGrupo VARCHAR(4),@pmMargenMin DECIMAL(14,4)
 AS 
 UPDATE TarifasFletes SET TipoTarifa=@pmTipoTarifa,TarifClie=@pmTarifClie,TarifPago=@pmTarifPago,TarifTabla=@pmTarifTabla,IdMneda=@pmIdMneda,UndMed=@pmUndMed,NitCliente=@pmNitCliente
,CdNat=@pmCdNat,CdTipoVeh=@pmCdTipoVeh,CdRango=@pmCdRango,Inactivo=@pmInactivo,UndTarifClie=@pmUndTarifClie,UndTarifPago=@pmUndTarifPago,CdRuta=@pmCdRuta
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,CdMercancia=@pmCdMercancia,TipoAfiVehic=@pmTipoAfiVehic
,TarifAfiliado=@pmTarifAfiliado,TarifTercero=@pmTarifTercero,TarifPropio=@pmTarifPropio,TipoOper=@pmTipoOper,CdGrupo=@pmCdGrupo,MargenMin=@pmMargenMin
 WHERE  Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTarifasFletes] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmNumero INT,@pmTipoTarifa VARCHAR(10),@pmTarifClie MONEY,@pmTarifPago MONEY
,@pmTarifTabla MONEY,@pmIdMneda VARCHAR(5),@pmUndMed VARCHAR(10),@pmNitCliente VARCHAR(16),@pmCdNat VARCHAR(4),@pmCdTipoVeh VARCHAR(4)
,@pmCdRango VARCHAR(4),@pmUndTarifClie VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmCdMercancia VARCHAR(16)
,@pmTipoAfiVehic VARCHAR(10),@pmTarifAfiliado DECIMAL(16,6),@pmTarifTercero DECIMAL(16,6),@pmTarifPropio DECIMAL(16,6),@pmTipoOper VARCHAR(10)
,@pmCdGrupo VARCHAR(4),@pmMargenMin DECIMAL(14,4),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TarifasFletes (Numero,IdOrigen,IdDestino,CdRuta,TipoTarifa,TarifClie,TarifPago,TarifTabla,IdMneda,UndMed,NitCliente,CdNat,CdTipoVeh,CdRango
,UndTarifClie,UndTarifPago,TipoAfiVehic,TarifAfiliado,TarifTercero,TarifPropio,Inactivo,CdMercancia,FechaCrea,IdUsuario,TipoOper,CdGrupo,MargenMin)
 VALUES (@pmNumero,@pmIdOrigen,@pmIdDestino,@pmCdRuta,@pmTipoTarifa,@pmTarifClie,@pmTarifPago,@pmTarifTabla,@pmIdMneda,@pmUndMed,@pmNitCliente
,@pmCdNat,@pmCdTipoVeh,@pmCdRango,@pmUndTarifClie,@pmUndTarifPago,@pmTipoAfiVehic,@pmTarifAfiliado,@pmTarifTercero,@pmTarifPropio,@pmInactivo,@pmCdMercancia,@pmFechaCrea,@pmIdUsuario,@pmTipoOper,@pmCdGrupo,@pmMargenMin)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasFletes] @pmNumero INT
 AS 
 SELECT Numero,IdOrigen,IdDestino,CdRuta,TipoTarifa,TarifClie,TarifPago,TarifTabla,IdMneda,UndMed
,NitCliente,CdNat,CdTipoVeh,CdRango,UndTarifClie,UndTarifPago
,Inactivo,CdMercancia,TipoAfiVehic,TarifAfiliado,TarifTercero,TarifPropio,TipoOper,CdGrupo,MargenMin,FechaCrea,IdUsuario
 FROM TarifasFletes
 WHERE  Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasServLta] 
 AS 
SELECT Numero,TipoServ,IdCliente,RazonSocial,T.IdMercancia AS CodMcia,DescripMcia,T.IdEstacion AS IdCampo,E.EstServicio AS DescCampo
,T.UndMed,Unidad,TipoAfiVehic,CdTipoVeh,TarifaClie,TarifaPago,MargenMin,T.Inactivo
,CdRuta,CdCiuOrigen,O.Localidad AS CiuOrigen,CdCiuDestino,D.Localidad AS CiuDestino,T.FechaCrea,T.IdUsuario AS CdUsuario,Usuario
FROM TarifasServ AS T INNER JOIN Terceros AS C ON T.IdCliente=C.IdTercero INNER JOIN EdsServicio AS E ON T.IdEstacion=E.IdEstacion 
INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario INNER JOIN Sys_Um AS UM ON T.UndMed=UM.UndMed
LEFT JOIN Mercancias AS M ON T.IdMercancia=M.IdMercancia LEFT JOIN Localidades AS O ON T.CdCiuOrigen=O.IdLocal
LEFT JOIN Localidades AS D ON T.CdCiuDestino=D.IdLocal
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTarifasServ] @pmTipoServ INT,@pmIdCliente VARCHAR(16),@pmIdMercancia VARCHAR(16),@pmIdEstacion VARCHAR(4),@pmUndMed VARCHAR(10)
,@pmTipoAfiVehic VARCHAR(10),@pmCdTipoVeh VARCHAR(4),@pmTarifaClie DECIMAL(16,4),@pmTarifaPago DECIMAL(16,4),@pmInactivo BIT
,@pmMargenMin DECIMAL(14,4),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TarifasServ (TipoServ,IdCliente,IdMercancia,IdEstacion,UndMed,TipoAfiVehic,CdTipoVeh,TarifaClie,TarifaPago,Inactivo,FechaCrea,IdUsuario,MargenMin,CdRuta,CdCiuOrigen,CdCiuDestino)
 VALUES (@pmTipoServ,@pmIdCliente,@pmIdMercancia,@pmIdEstacion,@pmUndMed,@pmTipoAfiVehic,@pmCdTipoVeh,@pmTarifaClie,@pmTarifaPago,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmMargenMin,@pmCdRuta,@pmCdCiuOrigen,@pmCdCiuDestino)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTarifasServ] @pmNumero INT,@pmTipoServ INT,@pmIdCliente VARCHAR(16),@pmIdMercancia VARCHAR(16),@pmIdEstacion VARCHAR(4),@pmUndMed VARCHAR(10),@pmTipoAfiVehic VARCHAR(10)
,@pmCdTipoVeh VARCHAR(4),@pmTarifaClie DECIMAL(16,4),@pmTarifaPago DECIMAL(16,4),@pmInactivo BIT,@pmMargenMin DECIMAL(14,4),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8)
 AS 
 UPDATE TarifasServ SET TipoServ=@pmTipoServ,IdCliente=@pmIdCliente,IdMercancia=@pmIdMercancia,IdEstacion=@pmIdEstacion,UndMed=@pmUndMed,TipoAfiVehic=@pmTipoAfiVehic
,CdTipoVeh=@pmCdTipoVeh,TarifaClie=@pmTarifaClie,TarifaPago=@pmTarifaPago,Inactivo=@pmInactivo,MargenMin=@pmMargenMin,CdRuta=@pmCdRuta,CdCiuOrigen=@pmCdCiuOrigen,CdCiuDestino=@pmCdCiuDestino
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasServ] @pmNumero INT
 AS 
 SELECT Numero,TipoServ,IdCliente,IdMercancia,IdEstacion,UndMed,TipoAfiVehic
,CdTipoVeh,TarifaClie,TarifaPago,Inactivo,MargenMin,CdRuta,CdCiuOrigen,CdCiuDestino,FechaCrea,IdUsuario
 FROM TarifasServ
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,IdVehiculo,IdPoseedor,IdConductor,TipoAfiVehic,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenLiq
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmTipoAfiVehic VARCHAR(10),@pmVrTotal MONEY,@pmVrCosto MONEY
,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmTarifaPago MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmTarifaImp DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4)
,@pmTarifaIca DECIMAL(16,6),@pmTarifaRiv DECIMAL(14,4),@pmTarifaRtc DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmReferencia VARCHAR(50),@pmCxPagar BIT,@pmCdPlazo VARCHAR(4),@pmFechaVence SMALLDATETIME,@pmUndMed VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmVrConcCargos MONEY,@pmVrConcDed MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenLiq (TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,IdVehiculo,IdPoseedor,IdConductor,TipoAfiVehic,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv
,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipOds,@pmNumOrden,@pmIdCiaOds,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmTipoAfiVehic,@pmVrTotal,@pmVrCosto,@pmVrAnticipo,@pmVrGastos,@pmVrIva,@pmVrRetencion,@pmVrReteIca,@pmVrReteCREE,@pmVrReteIva,@pmVrOtros,@pmVrDescuento,@pmVrNeto,@pmCantidad,@pmTarifaPago,@pmBaseImp
,@pmBaseRet,@pmBaseIca,@pmTarifaImp,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmTarifaRtc,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmCodTarRtc,@pmReferencia,@pmCxPagar,@pmCdPlazo,@pmFechaVence,@pmUndMed,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmNumCheque,@pmVrConcCargos,@pmVrConcDed)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmTipoAfiVehic VARCHAR(10),@pmVrTotal MONEY,@pmVrCosto MONEY
,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmTarifaPago MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmTarifaImp DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4)
,@pmTarifaIca DECIMAL(16,6),@pmTarifaRiv DECIMAL(14,4),@pmTarifaRtc DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmReferencia VARCHAR(50),@pmCxPagar BIT,@pmCdPlazo VARCHAR(4),@pmFechaVence SMALLDATETIME,@pmUndMed VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmVrConcCargos MONEY,@pmVrConcDed MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenLiq SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipOds=@pmTipOds,NumOrden=@pmNumOrden,IdCiaOds=@pmIdCiaOds,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,TipoAfiVehic=@pmTipoAfiVehic,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto,VrAnticipo=@pmVrAnticipo,VrGastos=@pmVrGastos,VrIva=@pmVrIva,VrRetencion=@pmVrRetencion
,VrReteIca=@pmVrReteIca,VrReteCREE=@pmVrReteCREE,VrReteIva=@pmVrReteIva,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,TarifaPago=@pmTarifaPago,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,TarifaImp=@pmTarifaImp,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,TarifaRtc=@pmTarifaRtc
,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,CodTarRtc=@pmCodTarRtc,Referencia=@pmReferencia,CxPagar=@pmCxPagar,CdPlazo=@pmCdPlazo,FechaVence=@pmFechaVence,UndMed=@pmUndMed,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipEgr=@pmTipEgr,Egreso=@pmEgreso,IdCiaEgr=@pmIdCiaEgr,NumCheque=@pmNumCheque,VrConcCargos=@pmVrConcCargos,VrConcDed=@pmVrConcDed
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
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)
 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
 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].[paUpTraOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecRecibo SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmVrTotal MONEY,@pmVrCosto MONEY,@pmCantidad DECIMAL(14,4),@pmVrAnticipo MONEY,@pmIdDestino VARCHAR(8),@pmTipoOrden VARCHAR(10),@pmIdEstacion VARCHAR(4),@pmNomEstacion VARCHAR(50),@pmTipoResiduo VARCHAR(100),@pmRemCliente VARCHAR(30),@pmNomRecibido VARCHAR(150),@pmFecIngCertif SMALLDATETIME,@pmFecCertificado SMALLDATETIME,@pmNumRemesa INT,@pmCdCiaRem CHAR(2)
,@pmEstCumplido INT,@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmkmtInicial INT,@pmkmtFinal INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmEstOrden INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmCdCiaLiq CHAR(2),@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmUsuAprob VARCHAR(11),@pmObservAprob VARCHAR(250)
,@pmCdRuta VARCHAR(4),@pmIdOrigen VARCHAR(8),@pmCantkms INT,@pmNumCump INT,@pmCdCiaCum CHAR(2),@pmCdTarIva VARCHAR(4),@pmCdTarRiv VARCHAR(4),@pmCdTarRet VARCHAR(4),@pmCdTarIca VARCHAR(4),@pmCdTarRtc VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenServ SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecRecibo=@pmFecRecibo,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,nRemolque=@pmnRemolque,TipoAfiVehic=@pmTipoAfiVehic,Modalidad=@pmModalidad,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto,Cantidad=@pmCantidad,VrAnticipo=@pmVrAnticipo
,IdDestino=@pmIdDestino,TipoOrden=@pmTipoOrden,IdEstacion=@pmIdEstacion,NomEstacion=@pmNomEstacion,TipoResiduo=@pmTipoResiduo,RemCliente=@pmRemCliente,NomRecibido=@pmNomRecibido,FecIngCertif=@pmFecIngCertif,FecCertificado=@pmFecCertificado,NumRemesa=@pmNumRemesa,CdCiaRem=@pmCdCiaRem,EstCumplido=@pmEstCumplido,EstFactura=@pmEstFactura,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CdCiaFact=@pmCdCiaFact
,kmtInicial=@pmkmtInicial,kmtFinal=@pmkmtFinal,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,EstOrden=@pmEstOrden,TipLiq=@pmTipLiq,NumLiquida=@pmNumLiquida,CdCiaLiq=@pmCdCiaLiq,NumAprob=@pmNumAprob,IdCiaApr=@pmIdCiaApr,FecAprob=@pmFecAprob,UsuAprob=@pmUsuAprob,ObservAprob=@pmObservAprob
,CdRuta=@pmCdRuta,IdOrigen=@pmIdOrigen,Cantkms=@pmCantkms,NumCump=@pmNumCump,CdCiaCum=@pmCdCiaCum,CdTarIva=@pmCdTarIva,CdTarRiv=@pmCdTarRiv,CdTarRet=@pmCdTarRet,CdTarIca=@pmCdTarIca,CdTarRtc=@pmCdTarRtc
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,FecRecibo,IdCliente,IdAgencia,IdVehiculo,IdPoseedor,IdConductor,nRemolque,TipoAfiVehic,Modalidad,VrTotal,VrCosto,Cantidad,VrAnticipo,IdDestino,TipoOrden,IdEstacion,NomEstacion,TipoResiduo,RemCliente,NomRecibido
,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact,kmtInicial,kmtFinal,EstOrden,TipLiq,NumLiquida,CdCiaLiq,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob
,CdRuta,IdOrigen,Cantkms,NumCump,CdCiaCum,CdTarIva,CdTarRiv,CdTarRet,CdTarIca,CdTarRtc,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecRecibo SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmVrTotal MONEY,@pmVrCosto MONEY,@pmCantidad DECIMAL(14,4),@pmVrAnticipo MONEY,@pmIdDestino VARCHAR(8),@pmTipoOrden VARCHAR(10),@pmIdEstacion VARCHAR(4),@pmNomEstacion VARCHAR(50),@pmTipoResiduo VARCHAR(100),@pmRemCliente VARCHAR(30),@pmNomRecibido VARCHAR(150),@pmFecIngCertif SMALLDATETIME,@pmFecCertificado SMALLDATETIME,@pmNumRemesa INT,@pmCdCiaRem CHAR(2)
,@pmEstCumplido INT,@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmkmtInicial INT,@pmkmtFinal INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmEstOrden INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmCdCiaLiq CHAR(2),@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmUsuAprob VARCHAR(11),@pmObservAprob VARCHAR(250)
,@pmCdRuta VARCHAR(4),@pmIdOrigen VARCHAR(8),@pmCantkms INT,@pmNumCump INT,@pmCdCiaCum CHAR(2),@pmCdTarIva VARCHAR(4),@pmCdTarRiv VARCHAR(4),@pmCdTarRet VARCHAR(4),@pmCdTarIca VARCHAR(4),@pmCdTarRtc VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenServ (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,FecRecibo,IdCliente,IdAgencia,IdVehiculo,IdPoseedor,IdConductor,nRemolque,TipoAfiVehic,Modalidad,VrTotal,VrCosto,Cantidad,VrAnticipo,IdDestino,TipoOrden,IdEstacion,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,EstOrden,TipLiq,NumLiquida,CdCiaLiq,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,CdRuta,IdOrigen,Cantkms,NumCump,CdCiaCum,CdTarIva,CdTarRiv,CdTarRet,CdTarIca,CdTarRtc)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecRecibo,@pmIdCliente,@pmIdAgencia,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmnRemolque,@pmTipoAfiVehic,@pmModalidad,@pmVrTotal,@pmVrCosto,@pmCantidad,@pmVrAnticipo,@pmIdDestino,@pmTipoOrden,@pmIdEstacion,@pmNomEstacion,@pmTipoResiduo,@pmRemCliente,@pmNomRecibido,@pmFecIngCertif,@pmFecCertificado,@pmNumRemesa
,@pmCdCiaRem,@pmEstCumplido,@pmEstFactura,@pmTipFact,@pmNumFactura,@pmCdCiaFact,@pmkmtInicial,@pmkmtFinal,@pmEstOrden,@pmTipLiq,@pmNumLiquida,@pmCdCiaLiq,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumAprob,@pmIdCiaApr,@pmFecAprob,@pmUsuAprob,@pmObservAprob,@pmCdRuta,@pmIdOrigen,@pmCantkms,@pmNumCump,@pmCdCiaCum,@pmCdTarIva,@pmCdTarRiv,@pmCdTarRet,@pmCdTarIca,@pmCdTarRtc)
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)
 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)
 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)
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
 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,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
ALTER PROCEDURE [dbo].[paInsTiposCon] @pmIdTipCon VARCHAR(4),@pmTipoContrato VARCHAR(100)
,@pmDuracion INT,@pmVincLabUgpp VARCHAR(250),@pmInactivo BIT
 AS 
 INSERT INTO TiposCon (IdTipCon,TipoContrato,Duracion,Inactivo,VincLabUgpp)
 VALUES (@pmIdTipCon,@pmTipoContrato,@pmDuracion,@pmInactivo,@pmVincLabUgpp)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[paUpTiposCon] @pmIdTipCon VARCHAR(4),@pmTipoContrato VARCHAR(100)
,@pmDuracion INT,@pmVincLabUgpp VARCHAR(250),@pmInactivo BIT
 AS 
 UPDATE TiposCon SET TipoContrato=@pmTipoContrato,Duracion=@pmDuracion,Inactivo=@pmInactivo
,VincLabUgpp=@pmVincLabUgpp
 WHERE IdTipCon=@pmIdTipCon
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[paQryTiposCon] @pmIdTipCon VARCHAR(4)
 AS 
 IF @pmIdTipCon IS NULL
  SELECT IdTipCon,TipoContrato,Duracion,VincLabUgpp
  FROM TiposCon
  WHERE Inactivo=0
  ORDER BY IdTipCon
 ELSE
  SELECT IdTipCon,TipoContrato,Duracion,VincLabUgpp,Inactivo
  FROM TiposCon
  WHERE IdTipCon=@pmIdTipCon
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercClienteLta] @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdRegimen VARCHAR(4)=Null,@pmTipoId CHAR(1)=Null
,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLocEnv VARCHAR(8)=Null,@pmIdForma VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmIdZona VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdPlazo VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdRuta VARCHAR(4)=Null
,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmInactivoTer BIT=Null
 AS 
 SELECT IdClie,T.RazonSocial AS RaznSocial,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS Dirccion,T.IdLocal AS CdCiudad,L.Localidad AS Ciudad,L.IdDep AS CdDpto
,D.Departamento AS Dpto,T.Telefono AS Tlefono,T.Fax AS NumFax,T.TelMovil AS TelCeluar,T.SitioWeb AS WebSite,T.e_mail AS Email,DirEnv,IdLocEnv,LE.Localidad AS CiuEnvio
,DE.Departamento AS Dptoenvio,DiasEntga,C.IdGrupo AS CdGrupo,GrupoClie,C.IdEstrato AS CdEstrato,Estrato,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona
,C.IdRuta AS CdRuta,Ruta,C.IdPlazo AS CdPlazo,Plazo,NVmto,PlazosImp,CdPlazoComb,C.IdForma AS CdFrma,FormaPago,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen
,Regimen,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,NitFact,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco,B.Direccion AS Dirbanco
,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,Contrato,NContrato,CiaContMay,FecIngreso,FecVigencia
,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,T.Observacion AS Observ,Cmntario1,Cmntario2,Cmntario3,CdBandera,PrendGarant,FecUpCupo,TipoCliente,CodClieSicom,CdDiaEnt,C.IdEstado AS CdEst_clie,EC.Estado AS EstadoClie,C.Inactivo AS Clie_inactivo
,T.IdEstado AS CdEst_terc,ET.Estado AS Estad_terc,T.Inactivo AS Terc_inactivo,T.Codigo AS CodTerc,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula
,T.EsVendedor AS EsVend,T.EsConductor AS EsCond,T.EsPropietario AS EsProp,T.EsProveedor AS EsProvee,T.EsEmpleado AS EsEmpldo,T.EsOperario AS EsOper,T.EsAccnista AS EsAcc
,T.EsCiaAseg AS EsAseg,CdTipBloq,C.FechaAdd AS FecAdd,C.FechaUpdate AS FecUpd,C.IdUsuario AS IdUsuari,Usuario,T.IniStgNom AS PosNomb,Restric_Cia,CupoGalones,FecPlazoDoc,EdoRadicaDoc
,DescEdoDoc,ComIndustrial,NumLista,Termicas,CodRetCom,CodCCosto,CodSubCosto,T.FechaNac,T.ImagenDoc1,T.ImagenDoc2,T.ImagenDoc3
 FROM TercCliente  AS C INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero  INNER JOIN Terceros AS TV ON C.IdVend=TV.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Estratos AS ETC ON C.IdEstrato=ETC.IdEstrato
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 INNER JOIN Formaspago AS F ON C.IdForma=F.IdForma
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase
 INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario   INNER JOIN Localidades AS LE ON C.IdLocEnv=LE.IdLocal
 INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  SectoresEco AS S ON T.IdSector=S.IdSector  INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%')  AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%')
 AND T.TipoId LIKE ISNULL(@pmTipoId,'%') AND T.IdProf LIKE ISNULL(@pmIdProf,'%') AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND C.IdForma LIKE ISNULL(@pmIdForma,'%') AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND SZ.IdZona LIKE ISNULL(@pmIdZona,'%') AND C.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdPlazo LIKE ISNULL(@pmIdPlazo,'%') AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgenciasLta] @pmIdClie VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdZona VARCHAR(4)=Null,@pmIdSzona VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdSector VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 SELECT A.IdClie AS NitCliente,RazonSocial,IdAgencia,CodAgencia,Agencia,DirAgncia,A.IdLocal AS CdCiuAgncia,L.Localidad AS CiudadAgncia
,L.IdDep AS CdDptoAgncia,D.Departamento AS DptoAgncia,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,A.FecUpCupo AS FechaUpCupo,Referencia
,CodSicom,A.NContrato AS NroCont,CiaCont,A.FecIngreso AS Fec_Ingreso,NomCial,SiglaRaz,SZA.IdZona AS Age_CdZona,ZA.Zona AS Age_Zona,A.IdSzona AS Age_IdSubzona,SZA.Subzona AS Age_Subzona
,A.IdPlazo AS CdPlazo,Plazo,NVmto,A.CdPlazoComb AS CdPlazoComb,A.IdForma AS CdForma,FormaPago,A.IdVend AS NitVend,A.CdCms AS CodCms,A.CdDct AS CodDct,IntMora,A.DiasGracia AS Dias_gracia,FactFletes,A.FactSold AS FacturaSold
,A.CupoGalones AS CupoGals,A.CodRuta,Ruta,CodDiaEnt
,TipoId,Dv,Direccion,T.IdLocal AS CdCiuClie,LT.Localidad AS CiuClie,LT.IdDep AS CdDptoClie,DT.Departamento AS DptoClie
,NitRepLeg,NomRepLeg,NitContac,NomContac,C.IdGrupo AS CdGrupo,GrupoClie,SZ.IdZona AS CdZona,Z.Zona AS CliZona,C.IdSzona AS CdSubzona,SZ.Subzona AS CliSubZona
,A.CdBandera AS CodBandera,TipoBandera,C.IdEstrato AS CdEstrato,Estrato,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRgmen,Regimen,TipEnte,VrCupo,VrSaldo,Telefono,Fax
,TelMovil,e_mail,C.IdEstado AS CdEstado,Estado,C.Inactivo AS Clie_inactivo,A.IdEstado AS Age_Estado,A.Inactivo AS Age_inactivo,IniStgNom,A.FechaAdd AS FecAdd
,TipoZona,Comentarios,TipoFactFletes,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari,Usuario
--datos nuevos
,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,CdCCBonif,CdSubCCBonif
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 INNER JOIN TercCliente AS C ON A.IdClie=C.IdClie INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON A.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona INNER JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN Estratos AS EC ON C.IdEstrato=EC.IdEstrato
 INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN RegimenDian AS R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS E ON C.IdEstado=E.IdEstado INNER JOIN Plazos AS PZ ON A.IdPlazo=PZ.IdPlazo
 INNER JOIN Formaspago AS FP ON A.IdForma=FP.IdForma
 LEFT JOIN TiposBan AS TB ON A.CdBandera=TB.IdBandera LEFT JOIN Rutas AS RT ON A.CodRuta=RT.IdRuta
 WHERE  A.IdClie LIKE ISNULL(@pmIdClie,'%') AND A.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo ,'%') AND SZA.IdZona LIKE ISNULL(@pmIdZona,'%') AND A.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%') AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND T.IdSector LIKE ISNULL(@pmIdSector,'%')
 AND (A.Inactivo=ISNULL(@pmInactivo,0) or  A.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY RazonSocial,Agencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgenciasCod] @pmIdClie VARCHAR(16),@pmCodAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,CodSicom,Referencia,Comentarios,CupoGalones
,TipoZona,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,TipoFactFletes,CdCCBonif,CdSubCCBonif,CodRuta,CodDiaEnt
 FROM Agencias
 WHERE IdClie=@pmIdClie AND CodAgencia LIKE ISNULL(@pmCodAgencia,'%')
 ORDER BY IdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgencias] @pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,CodSicom,Referencia,Comentarios,CupoGalones
,IdEstado,Inactivo,TipoZona,FechaAdd,FechaUpdate,IdUsuario,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,TipoFactFletes,CdCCBonif,CdSubCCBonif,CodRuta,CodDiaEnt
 FROM Agencias
 WHERE IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAgencias] @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16)
,@pmNomCont VARCHAR(150),@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4)
,@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmCodSicom VARCHAR(20),@pmCdTipAgenc VARCHAR(4),@pmCdSubAgenc VARCHAR(4),@pmCdGrupAgenc VARCHAR(4), @pmCdSubGrupAgenc VARCHAR(4), @pmCdFntePago VARCHAR(4), @pmPlazoSobtasa VARCHAR(20)
,@pmDocFinanc VARCHAR(3), @pmVolContMes DECIMAL(14,4), @pmVolContTotal DECIMAL(14,4), @pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4), @pmFecPriVenta SMALLDATETIME, @pmInvCtbObra MONEY, @pmInvCtbCapital MONEY, @pmInvCtbEfectivo MONEY, @pmInvCtbImagen MONEY, @pmInvCtbMntmto MONEY
,@pmFecDesemb SMALLDATETIME, @pmInvContObra MONEY, @pmInvContCapital MONEY, @pmInvContEfectivo MONEY, @pmInvContImagen MONEY, @pmCrtlAprovInv INT, @pmCrtlAprovImg INT,@pmFecVigCond SMALLDATETIME,@pmTasaIntArranq DECIMAL(14,4),@pmAgencOperando BIT
,@pmTipoFactFletes INT,@pmCdCCBonif VARCHAR(16),@pmCdSubCCBonif VARCHAR(16),@pmCodRuta VARCHAR(4),@pmCodDiaEnt VARCHAR(4),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Agencias (IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,CodSicom,FecIngreso,Referencia,Comentarios
,CupoGalones,TipoZona,IdEstado,Inactivo,FechaAdd,IdUsuario,CdTipAgenc, CdSubAgenc, CdGrupAgenc, CdSubGrupAgenc, CdFntePago, PlazoSobtasa, DocFinanc, VolContMes, VolContTotal, VolViabMes,VolViabTotal, FecPriVenta, InvCtbObra, InvCtbCapital, InvCtbEfectivo, InvCtbImagen, InvCtbMntmto, FecDesemb, InvContObra, InvContCapital
,InvContEfectivo, InvContImagen, CrtlAprovInv, CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,TipoFactFletes,CdCCBonif,CdSubCCBonif,CodRuta,CodDiaEnt)
 VALUES (@pmIdAgencia,@pmCodAgencia,@pmIdClie,@pmAgencia,@pmDirAgncia,@pmIdLocal,@pmTelAgncia,@pmFaxAgncia,@pmNitCont,@pmNomCont,@pmemlCont,@pmCargoCont,@pmIdSzona,@pmIdPlazo,@pmCdPlazoComb,@pmIdForma,@pmCdBandera,@pmIdVend
,@pmCdCms,@pmCdDct,@pmIntMora,@pmDiasGracia,@pmFactFletes,@pmFactSold,@pmVrCupoCre,@pmVrSaldoAct,@pmFecUpCupo,@pmNContrato,@pmCiaCont,@pmCodSicom,@pmFecIngreso,@pmReferencia,@pmComentarios,@pmCupoGalones,@pmTipoZona,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario
,@pmCdTipAgenc, @pmCdSubAgenc,@pmCdGrupAgenc,@pmCdSubGrupAgenc,@pmCdFntePago,@pmPlazoSobtasa,@pmDocFinanc,@pmVolContMes,@pmVolContTotal,@pmVolViabMes,@pmVolViabTotal,@pmFecPriVenta
,@pmInvCtbObra, @pmInvCtbCapital,@pmInvCtbEfectivo,@pmInvCtbImagen,@pmInvCtbMntmto,@pmFecDesemb,@pmInvContObra,@pmInvContCapital,@pmInvContEfectivo,@pmInvContImagen,@pmCrtlAprovInv,@pmCrtlAprovImg,@pmFecVigCond,@pmTasaIntArranq,@pmAgencOperando,@pmTipoFactFletes,@pmCdCCBonif,@pmCdSubCCBonif,@pmCodRuta,@pmCodDiaEnt)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpAgencias] @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16),@pmNomCont VARCHAR(150)
,@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT
,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmCodSicom VARCHAR(20)
,@pmCdTipAgenc VARCHAR(4),@pmCdSubAgenc VARCHAR(4),@pmCdGrupAgenc VARCHAR(4), @pmCdSubGrupAgenc VARCHAR(4), @pmCdFntePago VARCHAR(4), @pmPlazoSobtasa VARCHAR(20)
,@pmDocFinanc VARCHAR(3), @pmVolContMes DECIMAL(14,4), @pmVolContTotal DECIMAL(14,4), @pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4), @pmFecPriVenta SMALLDATETIME, @pmInvCtbObra MONEY, @pmInvCtbCapital MONEY, @pmInvCtbEfectivo MONEY, @pmInvCtbImagen MONEY, @pmInvCtbMntmto MONEY
,@pmFecDesemb SMALLDATETIME, @pmInvContObra MONEY, @pmInvContCapital MONEY, @pmInvContEfectivo MONEY, @pmInvContImagen MONEY, @pmCrtlAprovInv INT, @pmCrtlAprovImg INT,@pmFecVigCond SMALLDATETIME,@pmTasaIntArranq DECIMAL(14,4),@pmAgencOperando BIT,@pmTipoFactFletes INT,@pmCdCCBonif VARCHAR(16),@pmCdSubCCBonif VARCHAR(16),@pmCodRuta VARCHAR(4),@pmCodDiaEnt VARCHAR(4),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Agencias SET CodAgencia=@pmCodAgencia,IdClie=@pmIdClie,Agencia=@pmAgencia,DirAgncia=@pmDirAgncia,IdLocal=@pmIdLocal,TelAgncia=@pmTelAgncia,FaxAgncia=@pmFaxAgncia,NitCont=@pmNitCont,NomCont=@pmNomCont,emlCont=@pmemlCont,CargoCont=@pmCargoCont,IdSzona=@pmIdSzona
,IdPlazo=@pmIdPlazo,CdPlazoComb=@pmCdPlazoComb,IdForma=@pmIdForma,CdBandera=@pmCdBandera,IdVend=@pmIdVend,CdCms=@pmCdCms,CdDct=@pmCdDct,IntMora=@pmIntMora,DiasGracia=@pmDiasGracia,FactFletes=@pmFactFletes,FactSold=@pmFactSold,VrCupoCre=@pmVrCupoCre,VrSaldoAct=@pmVrSaldoAct
,FecUpCupo=@pmFecUpCupo,FecIngreso=@pmFecIngreso,Referencia=@pmReferencia,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,NContrato=@pmNContrato,CiaCont=@pmCiaCont,CupoGalones=@pmCupoGalones,FechaUpdate=@pmFechaUpdate,TipoZona=@pmTipoZona,CodSicom=@pmCodSicom
,CdTipAgenc=@pmCdTipAgenc,CdSubAgenc=@pmCdSubAgenc,CdGrupAgenc=@pmCdGrupAgenc,CdSubGrupAgenc=@pmCdSubGrupAgenc,CdFntePago=@pmCdFntePago,PlazoSobtasa=@pmPlazoSobtasa,DocFinanc=@pmDocFinanc,VolContMes=@pmVolContMes,VolContTotal=@pmVolContTotal,VolViabMes=@pmVolViabMes,VolViabTotal=@pmVolViabTotal
,FecPriVenta=@pmFecPriVenta,InvCtbObra=@pmInvCtbObra,InvCtbCapital=@pmInvCtbCapital,InvCtbEfectivo=@pmInvCtbEfectivo,InvCtbImagen=@pmInvCtbImagen,InvCtbMntmto=@pmInvCtbMntmto,FecDesemb=@pmFecDesemb,InvContObra=@pmInvContObra,InvContCapital=@pmInvContCapital,InvContEfectivo=@pmInvContEfectivo
,InvContImagen=@pmInvContImagen,CrtlAprovInv=@pmCrtlAprovInv,CrtlAprovImg=@pmCrtlAprovImg,FecVigCond=@pmFecVigCond,TasaIntArranq=@pmTasaIntArranq,AgencOperando=@pmAgencOperando,TipoFactFletes=@pmTipoFactFletes,CdCCBonif=@pmCdCCBonif,CdSubCCBonif=@pmCdSubCCBonif,CodRuta=@pmCodRuta,CodDiaEnt=@pmCodDiaEnt
 WHERE IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTercCliente] @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250)
,@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4),@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30)
,@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4),@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT
,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2),@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150)
,@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4),@pmCdPlazoComb VARCHAR(4),@pmCodClieSicom VARCHAR(20),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFecPlazoDoc SMALLDATETIME,@pmEdoRadicaDoc INT,@pmCdTipBloq VARCHAR(4)
,@pmDescEdoDoc VARCHAR(250),@pmComIndustrial INT,@pmNumLista CHAR(1),@pmTermicas BIT,@pmCodRetCom VARCHAR(4),@pmCodCCosto VARCHAR(16),@pmCodSubCosto VARCHAR(16),@pmCdDiaEnt VARCHAR(4),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario
,CupoGalones,Restric_Cia,CdPlazoComb,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,Termicas,CodRetCom,CodCCosto,CodSubCosto,CdDiaEnt)
 VALUES (@pmIdClie,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmDirEnv,@pmIdLocEnv,@pmDiasEntga,@pmIdSzona,@pmIdGrupo,@pmIdPlazo,@pmIdForma,@pmIdEstrato,@pmIdVend,@pmNitFact,@pmIdRuta,@pmIdClase,@pmNumCuenta
,@pmIdBanco,@pmCdMney,@pmCdDct,@pmCdRet,@pmCdRiv,@pmCdCms,@pmExcIva,@pmTrfIntMora,@pmDiasGracia,@pmLiqFletes,@pmFactSold,@pmAutoret,@pmIncRet,@pmIncRiv,@pmIncIca,@pmFactTipo,@pmVrCupo,@pmVrSaldo,@pmUidClie,@pmPwdClie,@pmCodClieSicom,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro
,@pmMatMerc,@pmFecMat,@pmPathFoto,@pmPathFirma,@pmCmntario1,@pmCmntario2,@pmPlazosImp,@pmIdEstado,@pmTipoCliente,@pmInactivo,@pmCdBandera,@pmContrato,@pmNContrato,@pmCiaContMay,@pmCmntario3,@pmPrendGarant,@pmFecUpCupo,@pmFechaAdd,@pmIdUsuario,@pmCupoGalones,@pmRestric_Cia
,@pmCdPlazoComb,@pmFecPlazoDoc,@pmEdoRadicaDoc,@pmCdTipBloq,@pmDescEdoDoc,@pmComIndustrial,@pmNumLista,@pmTermicas,@pmCodRetCom,@pmCodCCosto,@pmCodSubCosto,@pmCdDiaEnt)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTercCliente] @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4)
,@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4)
,@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY
,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2)
,@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150),@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4)
,@pmCdPlazoComb VARCHAR(4),@pmCodClieSicom VARCHAR(20),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFecPlazoDoc SMALLDATETIME,@pmEdoRadicaDoc INT,@pmCdTipBloq VARCHAR(4),@pmDescEdoDoc VARCHAR(250),@pmComIndustrial INT
,@pmNumLista CHAR(1),@pmTermicas BIT,@pmCodRetCom VARCHAR(4),@pmCodCCosto VARCHAR(16),@pmCodSubCosto VARCHAR(16),@pmCdDiaEnt VARCHAR(4),@pmFechaUpdate SMALLDATETIME 
 AS 
 UPDATE TercCliente SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,DirEnv=@pmDirEnv,IdLocEnv=@pmIdLocEnv
,DiasEntga=@pmDiasEntga,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdPlazo=@pmIdPlazo,IdForma=@pmIdForma,IdEstrato=@pmIdEstrato,IdVend=@pmIdVend,NitFact=@pmNitFact,IdRuta=@pmIdRuta,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco
,CdMney=@pmCdMney,CdDct=@pmCdDct,CdRet=@pmCdRet,CdRiv=@pmCdRiv,CdCms=@pmCdCms,ExcIva=@pmExcIva,TrfIntMora=@pmTrfIntMora,DiasGracia=@pmDiasGracia,LiqFletes=@pmLiqFletes,FactSold=@pmFactSold,Autoret=@pmAutoret,IncRet=@pmIncRet,IncRiv=@pmIncRiv
,IncIca=@pmIncIca,FactTipo=@pmFactTipo,VrCupo=@pmVrCupo,VrSaldo=@pmVrSaldo,UidClie=@pmUidClie,PwdClie=@pmPwdClie,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,MatMerc=@pmMatMerc,FecMat=@pmFecMat,PathFoto=@pmPathFoto
,PathFirma=@pmPathFirma,Cmntario1=@pmCmntario1,Cmntario2=@pmCmntario2,PlazosImp=@pmPlazosImp,CdBandera=@pmCdBandera,Contrato=@pmContrato,NContrato=@pmNContrato,CiaContMay=@pmCiaContMay,Cmntario3=@pmCmntario3,PrendGarant=@pmPrendGarant,FecUpCupo=@pmFecUpCupo
,CdPlazoComb=@pmCdPlazoComb,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,TipoCliente=@pmTipoCliente,FechaUpdate=@pmFechaUpdate,Restric_Cia=@pmRestric_Cia,CupoGalones=@pmCupoGalones,CodClieSicom=@pmCodClieSicom,FecPlazoDoc=@pmFecPlazoDoc,EdoRadicaDoc=@pmEdoRadicaDoc
,CdTipBloq=@pmCdTipBloq,DescEdoDoc=@pmDescEdoDoc,ComIndustrial=@pmComIndustrial,NumLista=@pmNumLista,Termicas=@pmTermicas,CodRetCom=@pmCodRetCom,CodCCosto=@pmCodCCosto,CodSubCosto=@pmCodSubCosto,CdDiaEnt=@pmCdDiaEnt
 WHERE IdClie=@pmIdClie
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercCliente] @pmIdClie VARCHAR(16)
 AS 
 SELECT IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga
,IdSzona,IdGrupo,IdPlazo,CdPlazoComb,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv
,CdCms,PlazosImp,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie
,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,CdBandera,CodClieSicom,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo
,TipoCliente,CupoGalones,Restric_Cia,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,IdEstado,Inactivo,Termicas,CodRetCom,CodCCosto,CodSubCosto,CdDiaEnt
,FechaAdd,FechaUpdate,IdUsuario
 FROM TercCliente
 WHERE IdClie=@pmIdClie
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTercCliente_Sel] @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliente WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario
,CupoGalones,Restric_Cia,CdPlazoComb,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,Termicas,CodRetCom,CodCCosto,CodSubCosto,CdDiaEnt)
  SELECT @pmNewIdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb
,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,NumLista,Termicas,CodRetCom,CodCCosto,CodSubCosto,CdDiaEnt
  FROM  TercCliente 
  WHERE IdClie=@pmIdClie
GO

CREATE PROCEDURE paInsTiposEnt @pmIdEnt VARCHAR(4),@pmDiaEntrega VARCHAR(50),@pmInactivo BIT
 AS 
 INSERT INTO TiposEnt (IdEnt,DiaEntrega,Inactivo)
 VALUES (@pmIdEnt,@pmDiaEntrega,@pmInactivo)
GO
CREATE PROCEDURE paUpTiposEnt @pmIdEnt VARCHAR(4),@pmDiaEntrega VARCHAR(50),@pmInactivo BIT
 AS 
 UPDATE TiposEnt SET DiaEntrega=@pmDiaEntrega,Inactivo=@pmInactivo
 WHERE IdEnt=@pmIdEnt
GO
CREATE PROCEDURE paQryTiposEnt @pmIdEnt VARCHAR(4)
 AS 
 IF @pmIdEnt IS NULL
  SELECT IdEnt,DiaEntrega
  FROM TiposEnt
  WHERE Inactivo=0
 ELSE
  SELECT IdEnt,DiaEntrega,Inactivo
  FROM TiposEnt WHERE IdEnt=@pmIdEnt
GO
CREATE PROCEDURE paDelTiposEnt @pmIdEnt VARCHAR(4)
 AS 
 DELETE FROM TiposEnt WHERE IdEnt=@pmIdEnt
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedidoDetFac] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT O.TipDoc,O.Pedido,O.IdCia AS CdCia,Compania,O.Fecha,O.FechaVence,O.IdConcepto AS CdConcepto,Concepto,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,CodAgencia,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,O.TarifaCom
,O.VrFletes,O.VrOtros,O.VrCargos,O.VrOtrDcto,O.VrNeto,O.DirEnvio,O.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LugarEnvio,O.DiasEntraga,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,O.CargoContac
,O.IdForma AS CdForma,FormaPago,O.DetallePago,O.MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,O.NitEmpTrans,O.EmpTrans,O.pVehiculo AS PlacaVeh,O.CdConductor,TC.RazonSocial AS Conductor,O.CdRuta,Ruta,RefPedido,O.TipFac,O.Factura,O.IdCiaFac,O.FechaFact,O.TipRem,O.Remision,O.IdCiaRem,O.FechaRem
,O.NumCotizac,CdCiaCotizac,O.NumAutoriza,O.Modalidad,O.Vigencia,O.NumAprob,O.IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.ZonaFrontera,TipoTrans,O.TipoOrden,TipoModifica,O.TimeSys AS FechaCrea,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--detalles
,D.Item,D.IdProducto AS CdProducto,DescripProd,PM.TipoRef,D.IdBodega AS CdBodega,Bodega,D.CdTanque,D.Salidas AS CantOrden,D.IdUnd AS CdUnd,UM.Unidad,D.VrUnitario AS CostoUnd,D.VrPrecio AS VlrUnitario,D.TarifaIva,D.VrIvaSal,D.TarifaDct,D.VrDctoSal
,D.TarifaRet,D.VrReteSal,D.TarifaIca,D.VrIcaSal,D.ListaPrec,D.VrBruto,D.VrBase,D.Unidades,D.Descripcion AS DetDescripcion,D.Referencia,D.Referencia2,D.Servcios,D.NoVentas,D.EsCombo,D.EsProdBase
,D.TipOrd AS DetTipoFac,D.NumOrden AS DetNumFac,D.IdCiaOrd AS DetIdCiaFac,D.galsneto AS CantTotFact,D.CantObseq,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
--datos de item facturado
,DF.TipDoc AS FacTipo,DF.Documento AS FacNumero,DF.IdCia AS FacIdCia,DF.FecFact,DF.Salidas AS FacCantidad,DF.VrUnitario AS FacCostoUnd,DF.VrPrecio AS FacVrUnitario,DF.TarifaIva AS FacTarifIva,DF.VrIvaSal AS FacVrIva
,DF.TarifaDct AS FacTarifDcto,DF.VrDctoSal AS FacVrDcto,DF.TarifaRet AS FacTarifRet,DF.TarifaIca AS FacTarifIca,DF.ListaPrec AS FacLista,DF.VrBruto AS FacVrBruto,DF.VrBase AS FacVrBase,D.Unidades AS FacUnidades,DF.Servcios AS FacServicio
,DF.EsCombo AS FacEsCombo,DF.EsProdBase AS FacProdBase,DF.galsbruto AS FacCantTotPed,DF.VrRetencion AS FacRetencion,DF.VrReteICA AS FacReteICA,DF.VrReteIVA AS FacReteIva,DF.VrFletes AS FacFletes,DF.VrOtros AS FacVrOtros,DF.VrCargos AS FacVrCargos,DF.VrOtrDcto AS FacOtrDcto
,DF.VrNeto AS FacVrNeto,DF.BaseRet,DF.VrReteCREE AS FacReteCREE
--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
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo,CdDiaEnt,DiaEntrega,CLI.IdEstrato AS CdEstrato,Estrato
 FROM Trn_Opedido AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON O.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 L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN Trn_Kardex AS D ON O.TipDoc=D.TipDoc AND O.Pedido=D.Documento AND O.IdCia=D.IdCia
 INNER JOIN ProdMcias AS PM ON D.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON D.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 LEFT JOIN Localidades AS LE ON O.IdLocEnv=LE.IdLocal LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta LEFT JOIN TiposEnt AS EG ON CLI.CdDiaEnt=EG.IdEnt LEFT JOIN Estratos AS ETT ON CLI.IdEstrato=ETT.IdEstrato
 --facturas>
 LEFT JOIN (SELECT D.TipDoc,D.Documento,D.IdCia,D.Item,D.Fecha,D.IdProducto,D.IdBodega,Salidas,VrUnitario,VrPrecio,D.TarifaIva,D.VrIvaSal,D.TarifaDct,D.VrDctoSal,D.TarifaRet,VrReteSal
,D.TarifaIca,D.VrIcaSal,ListaPrec,D.VrBruto,VrBase,D.Unidades,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.Remision,D.IdCiaRem,ItemCombo,Servcios,EsCombo,EsProdBase,galsbruto
,F.Fecha AS FecFact,F.IdConcepto AS CdConcFact,F.IdCliente,F.IdAgencia,F.VrRetencion,F.VrReteICA,F.VrReteIVA,VrFletes,F.VrOtros,VrCargos,VrOtrDcto,VrNeto,F.BaseRet,F.TarifaRet AS TarifRetFact,F.TarifaIca AS TarifIcaFact,F.TarifaRiv AS TarifRivFact
,F.TarifaRtc,F.VrReteCREE,F.Observacion FROM Trn_Kardex AS D INNER JOIN Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Documento=F.Factura AND D.IdCia=F.IdCia 
 WHERE D.TipDoc IN ('FCR','FC1','FC2','FC3','FC4','FC5') AND F.Anulado=0) AS DF ON D.TipDoc=DF.TipOrd AND D.Documento=DF.NumOrden AND D.IdCia=DF.IdCiaOrd AND D.Item=DF.ItemCombo AND D.IdProducto=DF.IdProducto
--<Facturas
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND (@pmIdCia IS NULL OR O.IdCia=@pmIdCia) AND (@pmIdCliente IS NULL OR O.IdCliente=@pmIdCliente)
GO