if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenCum]
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].[paQryEmpleadosVac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmpleadosVac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenCum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenCumFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenCumFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenCumLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenCumLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenCumRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenCumRel]
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].[paQryTraOrdenLiqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqDet]
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].[paQryTraOrdenServNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServOpe]
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].[paUpTraOrdenCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenCum]
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 sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncNomVacParcial]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncNomVacParcial]
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.NumReferencia
,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].[paQryTraOrdenLiqDet] @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,CT.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,A.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,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.Referencia2,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,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
,DC.Item,DC.IdConcepto AS DetCodConc,DescConcepto,DC.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito
,DC.IdTercero AS NitTercero,TDT.RazonSocial AS NomTercero,DC.CodAgncia,DC.CdCCosto,CCosto,DC.CdSubCos,SubCosto,pVehiculo
,DC.RubroConc,DC.VrBase,DC.TarifBase,DC.TipFac,DC.Factura,DC.IdCiaFac,ItemFac,DC.FecVence,RefConcep,CD.Concepto AS DetConcep
--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 
 INNER JOIN Trn_TraLiqDetalle AS DC ON L.TipDoc=DC.TipDoc AND L.Liquidacion=DC.Liquidacion AND L.IdCia=DC.IdCia
 INNER JOIN ConcDiversos AS CD ON DC.IdConcepto=CD.IdConcepto 
 LEFT JOIN Puc AS PC ON DC.IdCuenta=PC.IdCuenta LEFT JOIN Terceros AS TDT ON DC.IdTercero=TDT.IdTercero
 LEFT JOIN CentroCosto AS CC ON DC.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON DC.CdSubCos=SC.IdSubCos 
 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].[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.Referencia2,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
,L.TipEgr,L.Egreso,L.IdCiaEgr,L.NumCheque,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
,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,L.CdCCosto,CCosto,L.CdSubCos,SubCosto
 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
 LEFT JOIN CentroCosto AS CC ON L.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON L.CdSubCos=SC.IdSubCos
 --Subconsulta egresos-pagos
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac=@pmTipDoc AND TipDoc=@pmTipDoc AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON L.TipDoc=VC.TipFac AND L.Liquidacion=VC.Documento AND L.IdCia=VC.IdCia
 -- FIN Subconsulta
 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].[paQryTraOrdenCumRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT C.TipDoc AS IdTipDoc,TipoDoc,C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FechaCump,C.IdConcepto AS CdConcepto,Concepto,TipOds,C.NumOrden AS Num_Orden,IdCiaOds
,O.Fecha AS FecOrden,O.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.Modalidad,O.TipoOrden,O.VrAnticipo AS Vr_Anticipo,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,C.TipoAfiVehic,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,DescServicio,C.Cantidad AS CumCantidad,C.VrTotal AS CumValor,C.VrCosto AS CumValTercero,C.RemCliente
,Funcionario,O.NumRemesa,O.CdCiaRem,O.kmtInicial,O.kmtFinal,O.IdDestino,LD.Localidad,C.TipCom,C.Comprobante AS NumComp,C.IdCiaCom,C.Anulado AS CumAnulado,C.NumDev,C.FecDev
,C.NumReferencia,NumAprobCO,CiaAprobCO,FecAprobCO,ObservAprobCO,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,C.OrigenAdd,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaModif
,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,D.IdMercancia,D.Descripcion,D.Cantidad,D.UndMed AS Und_Med,Unidad,D.VrUnitario,D.TarifaPago,D.DocCliente
--Datos del 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
,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_TraOrdenCum AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Conceptos AS CT ON C.IdConcepto=CT.IdConcepto
INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
INNER JOIN Trn_TraOrdenServ AS O ON C.TipOds=O.TipDoc AND C.NumOrden=O.NumOrden  AND C.IdCiaOds=O.IdCia
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 Terceros AS T ON O.IdCliente=T.IdTercero
INNER JOIN Trn_TraOrdenCumDet AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.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 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 Localidades AS LD ON O.IdDestino=LD.IdLocal
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
WHERE C.TipDoc=@pmTipDoc AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServNov] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
 AS 
 SELECT O.TipDoc AS IdTipDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,A.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,O.EstFactura,O.TipFact AS OrdTipFac,O.NumFactura AS OrdFactura,O.CdCiaFact
,kmtInicial,kmtFinal,Cantkms,EstOrden,O.TipLiq AS OrdTipLiq,O.NumLiquida AS OrdNumLiquida,O.CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,O.NumReferencia,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea
--novedades
,C.Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CdConcepto,Concepto,CdCuenta,NomCuenta
,NitTercero,TCN.RazonSocial AS NomTercero,C.FechaNov,C.VrBase,C.TipoTarif,C.RefConc,C.Referencia2,C.Referencia3,C.Fijos,C.IncBaseRet,C.Est_Factura,C.TipFact,C.NumFactura,C.CiaFact,C.VrTotalFact,C.Est_Liquida,C.TipLiq,C.NumLiquida,C.CiaLiq,C.VrTotalLiq
--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 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 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_TraConceptos AS C ON O.TipDoc=C.TipDoc AND O.NumOrden=C.Documento AND O.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto LEFT JOIN Terceros AS TCN ON C.NitTercero=TCN.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 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].[paQryTraOrdenCumFmt] @pmTipDoc VARCHAR(3),@pmCumplidoIni INT,@pmCumplidoFin INT,@pmIdCia CHAR(2)
AS 
 SELECT C.TipDoc AS IdTipDoc,TipoDoc,C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FechaCump,C.IdConcepto AS CdConcepto,Concepto,TipOds,C.NumOrden AS Num_Orden,IdCiaOds
,O.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.Modalidad,O.TipoOrden,O.VrAnticipo AS Vr_Anticipo,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,C.TipoAfiVehic,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,DescServicio,C.Cantidad AS CumCantidad,C.VrTotal AS CumValor,C.VrCosto AS CumValTercero,C.RemCliente
,Funcionario,O.NumRemesa,O.CdCiaRem,O.kmtInicial,O.kmtFinal,IdDestino,LD.Localidad,C.TipCom,C.Comprobante AS NumComp,C.IdCiaCom,C.Anulado AS CumAnulado,C.NumDev,C.FecDev,C.Observacion AS Observ
,C.NumReferencia,NumAprobCO,CiaAprobCO,FecAprobCO,ObservAprobCO,C.IdEstado AS CdEstado,Estado,C.OrigenAdd,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaModif,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario,Leyenda
--detalles
,Item,D.IdMercancia,D.Descripcion,D.Cantidad,D.UndMed AS Und_Med,Unidad,D.VrUnitario,D.TarifaPago,D.DocCliente
--Datos del 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
,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_TraOrdenCum AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Conceptos AS CT ON C.IdConcepto=CT.IdConcepto
INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
INNER JOIN Trn_TraOrdenServ AS O ON C.TipOds=O.TipDoc AND C.NumOrden=O.NumOrden  AND C.IdCiaOds=O.IdCia
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 Terceros AS T ON O.IdCliente=T.IdTercero
INNER JOIN Trn_TraOrdenCumDet AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.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 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 Localidades AS LD ON O.IdDestino=LD.IdLocal
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
WHERE C.TipDoc=@pmTipDoc AND C.Cumplido BETWEEN @pmCumplidoIni AND @pmCumplidoFin AND C.IdCia=@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,O.NumReferencia,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].[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.NumDocSop,L.CiaDocSop,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.Referencia2,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
,L.CdCCosto,CCosto,L.CdSubCos,SubCosto
 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 LEFT JOIN CentroCosto AS CC ON L.CdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON L.CdSubCos=SC.IdSubCos
 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].[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,O.NumReferencia,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].[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,O.NumReferencia,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 FUNCTION [dbo].[FuncNomVacParcial] (@pmIdEmpleado VARCHAR(16),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME)
RETURNS INT
 AS  
BEGIN 
 DECLARE @DiasDisf INT
 --Funcion original
 -- SET  @DiasDisf=(SELECT ISNULL(SUM(DiasVac-DiasDom),0)
--  FROM Trn_NomVac
 -- WHERE IdEmpleado=@pmIdEmpleado AND FecIniPmd BETWEEN @pmFechaIni AND @pmFechaFin
 -- AND Anulado=0 AND LiqParcial=1)
 -- *** Funcion modificada por Dpto. Soporte Feb 6/2024 ***
 DECLARE @ColFecInicial SMALLDATETIME
 DECLARE @ColFecFinal SMALLDATETIME
 DECLARE @ColDiasVac INT
 DECLARE @ColDiasDom INT
 SET @DiasDisf=0
 DECLARE CursorVac CURSOR READ_ONLY FOR SELECT FecInicial,FecFinal,DiasVac,DiasDom
 FROM Trn_NomVac
 WHERE IdEmpleado=@pmIdEmpleado AND FecIniPmd<=@pmFechaFin AND FecFinPmd>=@pmFechaIni
 AND Anulado=0 AND LiqParcial=1 
 OPEN CursorVac
 FETCH NEXT FROM CursorVac INTO @ColFecInicial,@ColFecFinal,@ColDiasVac,@ColDiasDom 
  WHILE @@fetch_status = 0
   BEGIN
    IF @ColFecInicial >= @pmFechaIni And @ColFecFinal <= @pmFechaFin 
     SET @DiasDisf=@DiasDisf+(@ColDiasVac-@ColDiasDom)
    ELSE
     BEGIN
      IF @ColFecInicial< @pmFechaIni And @ColFecFinal > @pmFechaFin 
       BEGIN
        SET @DiasDisf=@DiasDisf+(DATEDIFF(DAY,@pmFechaIni,@pmFechaFin)) 
       END
      ELSE
       BEGIN
       IF @ColFecInicial< @pmFechaIni 
        BEGIN 
         SET @ColFecInicial=@pmFechaIni 
        END
       IF @ColFecFinal>@pmFechaFin 
        BEGIN
         SET @ColFecFinal=@pmFechaFin 
        END
       SET @DiasDisf=@DiasDisf+(DATEDIFF(DAY,@ColFecInicial,@ColFecFinal))
       END
     END
    FETCH NEXT FROM CursorVac INTO @ColFecInicial,@ColFecFinal,@ColDiasVac,@ColDiasDom 
   END
 CLOSE CursorVac
 DEALLOCATE CursorVac
 RETURN @DiasDisf
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenCumLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Cumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FechaCump,C.IdConcepto AS CdConcepto,Concepto,TipOds,C.NumOrden AS Num_Orden,IdCiaOds
,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.Modalidad,TipoOrden,O.VrAnticipo AS Vr_Anticipo,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,C.TipoAfiVehic,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,DescServicio,C.Cantidad AS CumCantidad,C.VrTotal AS CumValor,C.VrCosto AS CumValTercero,C.RemCliente
,Funcionario,O.NumRemesa,O.CdCiaRem,O.kmtInicial,O.kmtFinal,C.TipCom,C.Comprobante AS NumComp,C.IdCiaCom,C.Anulado AS CumAnulado,C.NumDev,C.FecDev,C.Observacion AS Observ
,C.NumReferencia,NumAprobCO,CiaAprobCO,FecAprobCO,ObservAprobCO,C.IdEstado AS CdEstado,Estado,C.OrigenAdd,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaModif,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
FROM Trn_TraOrdenCum AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Conceptos AS CT ON C.IdConcepto=CT.IdConcepto
INNER JOIN Trn_TraOrdenServ AS O ON C.TipOds=O.TipDoc AND C.NumOrden=O.NumOrden  AND C.IdCiaOds=O.IdCia
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 Terceros AS T ON O.IdCliente=T.IdTercero
LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia
WHERE C.TipDoc=@pmTipDoc AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.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,O.NumReferencia,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].[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.NumDocSop,L.CiaDocSop
,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,L.Referencia2,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.CdCCosto,L.CdSubCos
,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].[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,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmNumDocSop INT,@pmCiaDocSop CHAR(2),@pmReferencia2 VARCHAR(50)
,@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,CdCCosto,CdSubCos,NumDocSop,CiaDocSop,Referencia2)
 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,@pmCdCCosto,@pmCdSubCos,@pmNumDocSop,@pmCiaDocSop,@pmReferencia2)
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,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmNumDocSop INT,@pmCiaDocSop CHAR(2),@pmReferencia2 VARCHAR(50),@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,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,NumDocSop=@pmNumDocSop,CiaDocSop=@pmCiaDocSop,Referencia2=@pmReferencia2
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
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,CdCCosto,CdSubCos,NumDocSop,CiaDocSop,Referencia2,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].[paInsTraOrdenCum] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmTipoAfiVehic VARCHAR(10),@pmFecDespacho SMALLDATETIME,@pmFecRecibo SMALLDATETIME,@pmFecIngCertif SMALLDATETIME,@pmFecCertificado SMALLDATETIME,@pmDescServicio VARCHAR(100)
,@pmCantidad DECIMAL(14,4),@pmVrTotal MONEY,@pmVrCosto MONEY,@pmRemCliente VARCHAR(30),@pmFuncionario VARCHAR(150),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmNumReferencia VARCHAR(50),@pmNumAprobCO INT,@pmCiaAprobCO CHAR(2),@pmFecAprobCO SMALLDATETIME,@pmObservAprobCO VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenCum (TipDoc,Cumplido,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,TipoAfiVehic,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,DescServicio,Cantidad,VrTotal,VrCosto,RemCliente,Funcionario,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,NumReferencia,NumAprobCO,CiaAprobCO,FecAprobCO,ObservAprobCO)
 VALUES (@pmTipDoc,@pmCumplido,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipOds,@pmNumOrden,@pmIdCiaOds,@pmTipoAfiVehic,@pmFecDespacho,@pmFecRecibo,@pmFecIngCertif,@pmFecCertificado,@pmDescServicio,@pmCantidad,@pmVrTotal,@pmVrCosto,@pmRemCliente,@pmFuncionario,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumReferencia,@pmNumAprobCO,@pmCiaAprobCO,@pmFecAprobCO,@pmObservAprobCO)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraOrdenCum] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmTipoAfiVehic VARCHAR(10),@pmFecDespacho SMALLDATETIME,@pmFecRecibo SMALLDATETIME,@pmFecIngCertif SMALLDATETIME,@pmFecCertificado SMALLDATETIME,@pmDescServicio VARCHAR(100)
,@pmCantidad DECIMAL(14,4),@pmVrTotal MONEY,@pmVrCosto MONEY,@pmRemCliente VARCHAR(30),@pmFuncionario VARCHAR(150),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmNumReferencia VARCHAR(50),@pmNumAprobCO INT,@pmCiaAprobCO CHAR(2),@pmFecAprobCO SMALLDATETIME,@pmObservAprobCO VARCHAR(250),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenCum SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipOds=@pmTipOds,NumOrden=@pmNumOrden,IdCiaOds=@pmIdCiaOds,TipoAfiVehic=@pmTipoAfiVehic,FecDespacho=@pmFecDespacho,FecRecibo=@pmFecRecibo,FecIngCertif=@pmFecIngCertif,FecCertificado=@pmFecCertificado,DescServicio=@pmDescServicio,Cantidad=@pmCantidad,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto
,RemCliente=@pmRemCliente,Funcionario=@pmFuncionario,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,NumReferencia=@pmNumReferencia,NumAprobCO=@pmNumAprobCO,CiaAprobCO=@pmCiaAprobCO,FecAprobCO=@pmFecAprobCO,ObservAprobCO=@pmObservAprobCO
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenCum] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Cumplido,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,TipoAfiVehic,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,DescServicio,Cantidad,VrTotal,VrCosto,RemCliente,Funcionario,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado
,NumReferencia,NumAprobCO,CiaAprobCO,FecAprobCO,ObservAprobCO,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenCum
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido 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),@pmNumReferencia VARCHAR(50),@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,NumReferencia=@pmNumReferencia
 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,NumReferencia,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),@pmNumReferencia VARCHAR(50),@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,NumReferencia)
 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,@pmNumReferencia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServOpe] @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.NumReferencia,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,dbo.FuncOsaListaAntcipos(O.NumOrden,O.IdCia) AS Anticipos,L.TipEgr,L.Egreso,L.IdCiaEgr,EG.Fecha AS FecEgreso,EG.NumCheque,EGT.TotalEgresos
--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
LEFT JOIN Trn_Comprobantes AS EG ON L.TipEgr=EG.TipCom AND L.Egreso=EG.Comprobante AND L.IdCiaEgr=EG.IdCia
LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EGT ON O.TipLiq=EGT.TipOrden AND O.NumLiquida=EGT.NumOPago AND O.CdCiaLiq=EGT.CdCiaOpago
WHERE 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].[paQryEmpleadosVac] @pmDias INT,@pmFecVacIni SMALLDATETIME,@pmFecVacFin SMALLDATETIME,@pmIdProf VARCHAR(4)=Null
,@pmEmp_IdEstado VARCHAR(4)=Null,@pmEmp_Inactivo BIT=Null,@pmEsCondtor BIT=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdEstado VARCHAR(4)=Null
,@pmFecIngresoIni SMALLDATETIME=Null,@pmFecIngresoFin SMALLDATETIME=Null,@pmSalIntegral BIT=Null
 AS 
 SELECT E.IdEmpleado AS IdEmplado,Apellidos,Nombres,Codigo,TipoId,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,FecNacmto,LugarNacmto,TipoSangre
,FactorRh,Sexo,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia
,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase
,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes
,IdCajaCom,CC.Fondo AS CajaComp,E.NContrato AS NCntrato,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,FecUltParc,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,FotoEmp,FotoLic
,FotoFirma,Observacion,E.IdEstado AS Emp_IdEstado,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,ET.NColor AS EmpEstcolor,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario
--Datos del contrato
,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto
,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina
,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyCes,DenyPri,DenyVac,DchDotacion,DchCafeteria,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,VrUpc
,IdCot,C.IdGrupo AS GrupAR
--Dias parciales disfrutados SP Original
--,dbo.FuncNomVacParcial(E.IdEmpleado,ISNULL(FecUltVac,FecIngreso),@pmFecVacFin) AS DiasDisf
--,dbo.FuncNomVacParcRem(E.IdEmpleado,ISNULL(FecUltVac,FecIngreso),@pmFecVacFin) AS DiasRem
--Dias parciales disfrutados Func modificadas por Dpto. Soporte Feb 6/2024
,dbo.FuncNomVacParcial(E.IdEmpleado,DATEADD(day,1,ISNULL(FecUltVac,FecIngreso)),@pmFecVacFin) AS DiasDisf
,dbo.FuncNomVacParcRem(E.IdEmpleado,DATEADD(day,0,ISNULL(FecUltVac,FecIngreso)),@pmFecVacFin) AS DiasRem
 FROM Empleados AS E INNER JOIN Emp_Contrato AS C ON E.NContrato=C.NContrato
 INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon  INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo 
 INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep 
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala 
 INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom 
 INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado  INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario 
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 WHERE DATEADD(day,@pmDias,ISNULL(FecUltVac,FecIngreso)) BETWEEN @pmFecVacIni AND @pmFecVacFin AND E.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND E.IdEstado LIKE ISNULL(@pmEmp_IdEstado,'%') AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND C.IdCargo LIKE ISNULL(@pmIdCargo,'%')
 AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (SalIntegral=ISNULL(@pmSalIntegral,0) or SalIntegral=ISNULL(@pmSalIntegral,1))
 AND (FecIngreso>=ISNULL(@pmFecIngresoIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngresoFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Apellidos,Nombres
GO
--marzo 1/24


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsFacturas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpFacturas]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes
,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv
,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,DiasEntraga
,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta
,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,KmtVehic,CodAutoRet,OrigenAdd,TipCom,Comprobante,IdCiaCom
,ZonaFrontera,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,VrImpCons,VrReteCREE,VrImpCarbono,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,BaseIvaObsq,VrIvaObsequio,VrImpuBA,VrImpuCUP
,TipoFE,FechaFE,BaseIvaIgp,VrIvaIngProd,CodTarArf,TarifaArf,VrAutRetFte,CodTarAri,TarifaAri,VrAutRetIca,ImpAviTab,TarifaAvta,CodTarifAvta,VrBomberil,TarifaBom,CodTarifBom,VrAutRetIat,VrAutRetBom,TipoOperTra
,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Facturas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsFacturas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY
,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmPuntosAcum DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4)
,@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5)
,@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmTipCot VARCHAR(3)
,@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmModalidad VARCHAR(10),@pmKmtVehic INT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmZonaFrontera INT,@pmPesoTotal DECIMAL(14,4),@pmUnidTotal DECIMAL(14,4),@pmVolTotal DECIMAL(14,4),@pmCantFalt DECIMAL(14,4),@pmVrCostoRem MONEY,@pmVrCostoAfi MONEY,@pmCodAutoRet VARCHAR(4),@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmParqueadero BIT,@pmFecCuotaParq SMALLDATETIME
,@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmTipoFE VARCHAR(3),@pmFechaFE SMALLDATETIME,@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmCodTarArf VARCHAR(4),@pmTarifaArf DECIMAL(14,4),@pmVrAutRetFte MONEY,@pmCodTarAri VARCHAR(4),@pmTarifaAri DECIMAL(14,4),@pmVrAutRetIca MONEY,@pmVrImpuBA MONEY,@pmVrImpuCUP MONEY
,@pmImpAviTab MONEY,@pmTarifaAvta DECIMAL(14,4),@pmCodTarifAvta VARCHAR(4),@pmVrBomberil MONEY,@pmTarifaBom DECIMAL(14,4),@pmCodTarifBom VARCHAR(4),@pmVrAutRetIat MONEY,@pmVrAutRetBom MONEY,@pmTipoOperTra VARCHAR(10),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Facturas (TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem
,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,KmtVehic,ZonaFrontera,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,CodAutoRet,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,TipoFE,FechaFE,BaseIvaIgp,VrIvaIngProd,CodTarArf,TarifaArf,VrAutRetFte,CodTarAri,TarifaAri,VrAutRetIca,VrImpuBA,VrImpuCUP,ImpAviTab,TarifaAvta,CodTarifAvta,VrBomberil,TarifaBom,CodTarifBom,VrAutRetIat,VrAutRetBom,TipoOperTra)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFechaVence,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes,@pmVrAnticipos,@pmVrNeto,@pmVrAplicado,@pmCantidad,@pmCantPuntos,@pmPuntosAcum,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio
,@pmIdLocEnv,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmTipCot,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmAutzaMora,@pmAutzaCupo,@pmModalidad,@pmKmtVehic,@pmZonaFrontera,@pmPesoTotal,@pmUnidTotal,@pmVolTotal,@pmCantFalt,@pmVrCostoRem,@pmVrCostoAfi,@pmCodAutoRet,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCons,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc
,@pmParqueadero,@pmFecCuotaParq,@pmBaseIvaObsq,@pmVrIvaObsequio,@pmVrImpCarbono,@pmTipoFE,@pmFechaFE,@pmBaseIvaIgp,@pmVrIvaIngProd,@pmCodTarArf,@pmTarifaArf,@pmVrAutRetFte,@pmCodTarAri,@pmTarifaAri,@pmVrAutRetIca,@pmVrImpuBA,@pmVrImpuCUP,@pmImpAviTab,@pmTarifaAvta,@pmCodTarifAvta,@pmVrBomberil,@pmTarifaBom,@pmCodTarifBom,@pmVrAutRetIat,@pmVrAutRetBom,@pmTipoOperTra)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpFacturas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY
,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmPuntosAcum DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4)
,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmpVehiculo VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmTipCot VARCHAR(3),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmModalidad VARCHAR(10),@pmKmtVehic INT
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmPesoTotal DECIMAL(14,4),@pmUnidTotal DECIMAL(14,4),@pmVolTotal DECIMAL(14,4),@pmCantFalt DECIMAL(14,4),@pmVrCostoRem MONEY,@pmVrCostoAfi MONEY,@pmCodAutoRet VARCHAR(4)
,@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmParqueadero BIT,@pmFecCuotaParq SMALLDATETIME,@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmTipoFE VARCHAR(3),@pmFechaFE SMALLDATETIME,@pmBaseIvaIgp MONEY,@pmVrIvaIngProd MONEY,@pmCodTarArf VARCHAR(4),@pmTarifaArf DECIMAL(14,4),@pmVrAutRetFte MONEY
,@pmCodTarAri VARCHAR(4),@pmTarifaAri DECIMAL(14,4),@pmVrAutRetIca MONEY,@pmVrImpuBA MONEY,@pmVrImpuCUP MONEY,@pmImpAviTab MONEY,@pmTarifaAvta DECIMAL(14,4),@pmCodTarifAvta VARCHAR(4),@pmVrBomberil MONEY,@pmTarifaBom DECIMAL(14,4),@pmCodTarifBom VARCHAR(4),@pmVrAutRetIat MONEY,@pmVrAutRetBom MONEY,@pmTipoOperTra VARCHAR(10),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Facturas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos
,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarIva=@pmCodTarIva
,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocEnv=@pmIdLocEnv,DiasEntraga=@pmDiasEntraga,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargoContac=@pmCargoContac
,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,CdMney=@pmCdMney,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,CdRuta=@pmCdRuta,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,TipRem=@pmTipRem,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,TipCot=@pmTipCot,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,PuntosAcum=@pmPuntosAcum,KmtVehic=@pmKmtVehic,ZonaFrontera=@pmZonaFrontera,Parqueadero=@pmParqueadero,FecCuotaParq=@pmFecCuotaParq
,PesoTotal=@pmPesoTotal,UnidTotal=@pmUnidTotal,VolTotal=@pmVolTotal,CantFalt=@pmCantFalt,VrCostoRem=@pmVrCostoRem,VrCostoAfi=@pmVrCostoAfi,CodAutoRet=@pmCodAutoRet,VrImpCons=@pmVrImpCons,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,BaseIvaObsq=@pmBaseIvaObsq,VrIvaObsequio=@pmVrIvaObsequio,VrImpCarbono=@pmVrImpCarbono,TipoFE=@pmTipoFE,FechaFE=@pmFechaFE,BaseIvaIgp=@pmBaseIvaIgp,VrIvaIngProd=@pmVrIvaIngProd
,CodTarArf=@pmCodTarArf,TarifaArf=@pmTarifaArf,VrAutRetFte=@pmVrAutRetFte,CodTarAri=@pmCodTarAri,TarifaAri=@pmTarifaAri,VrAutRetIca=@pmVrAutRetIca,VrImpuBA=@pmVrImpuBA,VrImpuCUP=@pmVrImpuCUP,ImpAviTab=@pmImpAviTab,TarifaAvta=@pmTarifaAvta,CodTarifAvta=@pmCodTarifAvta,VrBomberil=@pmVrBomberil,TarifaBom=@pmTarifaBom,CodTarifBom=@pmCodTarifBom,VrAutRetIat=@pmVrAutRetIat,VrAutRetBom=@pmVrAutRetBom,TipoOperTra=@pmTipoOperTra,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO