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].[paInsTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenServ]
GO
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].[paQryFacturas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturas_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleRes]
GO
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-- DROP PROCEDURE [dbo].[paQryTraManifiestoRel]
--GO
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 --DROP PROCEDURE [dbo].[paQryTraManifiestoRelDet]
--GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServRelDet]
GO
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaMuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-- DROP PROCEDURE [dbo].[paQryTraRemesaMuc]
--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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenServ]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleRes] @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto  VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null,@pmNPrestamo INT=Null
 AS 
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumCntrato,N.KeyRegistro AS KeyReg,N.IdPeriodo AS NumPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdNom AS CodNmna,TipoNomina,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala AS CodIstala,Instlacion
,N.VrSalario AS SalBasico,N.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,ED.Estado AS Nom_Estado,EsRetiro,NLiqRet,TipoLiq,TimeSys
,N.FecUpdate AS Fec_Update,N.IdUsuario AS IdUsuari,Usuario
--datos del detalle
,Item,D.IdConcepto AS CodConcept,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,ClaseCon,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS Tarfa,DiasCalc,DiasNov,CodFondo,FCO.Fondo AS NomFondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,FC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS VlrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes,SegFinMes,DchDotacion,DchCafeteria
,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj,C.IdGrupo AS CodGrup
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala  INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco 
 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.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar  INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 
 INNER JOIN  Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND  N.KeyRegistro=D.KeyRegistro
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN Fondos AS FCO ON D.CodFondo=FCO.IdFondo
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-2)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (NPrestamo>=ISNULL(@pmNPrestamo,0)  AND NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
--Descuentos en vacaciones
UNION ALL
 SELECT V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.IdEmpleado,D.IdPeriodo,V.FecInicial,V.FecFinal,V.Cantidad,0,V.VrLiquida,V.VrTotDed
,C.IdNom,TipoNomina,C.IdDep,Dependencia,C.IdCCosto,CCosto,C.IdSubCos,SubCosto,C.IdInstala,Instlacion,V.VrSalario,V.pVehiculo,V.TipCom,TipoCom,V.Comprobante,V.IdCiaCom
,V.Observacion,V.IdEstado,ED.Estado,0,V.Numero,C.TipoLiquida,V.TimeSys,V.FecUpdate,V.IdUsuario,Usuario
,D.Item,D.IdConcepto,Concepto,D.Detalle,0,D.Cantidad,0,D.VrDevgado,D.VrDeducido,ClaseCon,'%','NOMINA',D.VrBaseLiq,D.Tarifa,D.Cantidad,0,'0','',D.NPrestamo,D.NCuota,'VAC',D.VrBaseLiq
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,E.IdClase,ClaseCuenta,NumCuenta,E.IdBanco,Banco,IdFonPen,FP.Fondo
,IdFonEps,FS.Fondo,IdFonArp,FR.Fondo,IdCajaCom,FC.Fondo,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo
,C.IdTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo,Cargo,C.IdArea,Area,C.IdClase,ClaseRiesgo,CR.Tarifa,C.VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado,ECO.Estado,C.Inactivo,C.IdJornada,Jornada,TipoTrabj,C.IdGrupo
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN Dependencias AS DPD ON C.IdDep=DPD.IdDep
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco 
 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.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON V.TipCom=TCM.IdCom
 WHERE V.Fecha BETWEEN @pmFecIni AND @pmFecFin AND V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (V.NContrato>=ISNULL(@pmNContrato,0)  AND V.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (D.NPrestamo>=ISNULL(@pmNPrestamo,0)  AND D.NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
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,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,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].[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,@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,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmFacturaIni INT=Null,@pmFacturaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT Factura,IdCia,Fecha,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,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,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio
,IdLocEnv,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans
,EmpTrans,pVehiculo,CdConductor,KmtVehic,CdRuta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,CodAutoRet
,OrigenAdd,TipCom,Comprobante,IdCiaCom,ZonaFrontera,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,Anulado,NumDev,FecDev,F.Observacion AS Observ
,F.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,F.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Facturas F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON F.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario 
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Factura BETWEEN ISNULL(@pmFacturaIni,0) AND ISNULL(@pmFacturaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND F.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Factura
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
,@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)
 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)
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.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,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,ED.Estado,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--cumplido
,EstCumplido,CM.Cumplido AS NumCumplido,CM.IdCia AS CiaCump,CM.Fecha AS FechaCump,CM.DescServicio AS CumpServicio,CM.Observacion AS ObservCump
,CM.IdMercancia AS CdMercanciaCump,CM.Descripcion AS DescripCump,CM.Cantidad AS CantCump,CM.UndMed AS UndMedCump,UMC.Unidad AS UnidadCump,CM.VrUnitario AS TarifCump
,CM.TarifaPago AS TarifPagoCump
--liquidacion
,O.EstOrden,O.TipLiq,O.NumLiquida,O.CdCiaLiq,L.Fecha AS FecLiquida,L.VrTotal AS LiqTotal,L.VrAnticipo AS LiqAnticipo,L.VrIva AS LiqIva,L.VrRetencion AS LiqRetencion
,L.VrReteIca,L.VrReteCREE,L.VrReteIva,L.VrOtros,L.VrDescuento,L.VrNeto,L.Cantidad AS LiqCantidad,L.TarifaPago AS LiqTarifaPago,L.TarifaImp,L.TarifaRet,L.TarifaIca,L.TarifaRiv
,L.Referencia,L.VrGastos
--Factura
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,FactFecha,FactCant,FactTarifClie,FactTotal,FactIva,FactTarifIVA,FactCosto,FactIdCli,TCF.RazonSocial AS FactNomCliente
--Inf vehiculo
,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LC.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,Modelo,NumMotor,SerieChasis,NumSerie,NitEmpresa,NE.RazonSocial AS VehNomEmpresa
,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
FROM Trn_TraOrdenServ AS O INNER JOIN Trn_TraOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal
INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
LEFT JOIN Trn_TraOrdenLiq AS L ON O.TipLiq=L.TipDoc AND O.NumLiquida=L.Liquidacion AND O.CdCiaLiq=L.IdCia
--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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryFacturas_Cr] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,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
,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,F.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga
,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac,F.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans
,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CiaCotiza,FecPedido
,AutzaMora,AutzaCupo,Modalidad,KmtVehic,PesoTotal,UnidTotal,VolTotal,CantFalt,VrCostoRem,VrCostoAfi,VrImpCons,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,F.Observacion AS Observ,ZonaFrontera,VrReteCREE,TarifaRtc,CodTarRtc,Parqueadero,FecCuotaParq,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,KA.NContrato AS KarAgeContrato,KA.Referencia AS KarAgeRefencia,CdLocal,KL.Localidad AS KarCiudad
,CdCCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS CodSubCos,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,K.Cotizacion AS KarNumCotiza,K.IdCiaCot AS KarCiaCotiza,K.Remision AS KarRemision,K.IdCiaRem AS KarCiaRem
,K.Factura AS KarFactura,TipDocDev,NumDocDev,K.IdVend AS KarNitVend,KV.RazonSocial AS KarVendedor,K.Comision AS KarTarifaCom,CdOperario,KO.RazonSocial AS NomOperario,ComisnOper,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase
,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
,CdMngra,NumInicial,NumFinal
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
--campos de memo
,DM.Comentarios AS Comentario,Nota1,Nota2,Nota3
 FROM Trn_Facturas AS F  INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON F.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma
 INNER JOIN CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON F.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN Terceros AS KV ON K.IdVend=KV.IdTercero  LEFT JOIN Terceros AS KO ON K.CdOperario=KO.IdTercero
 LEFT JOIN TiposCom AS TCM ON F.TipCom=TCM.IdCom
 LEFT JOIN Trn_DocMemo AS DM ON F.TipDoc=DM.TipDoc AND F.Factura=DM.Documento AND F.IdCia=DM.IdCia
 WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY F.Factura,Item
GO
--SET ANSI_NULLS OFF
--GO
--SET QUOTED_IDENTIFIER OFF
--GO
--CREATE PROCEDURE [dbo].[paQryTraRemesaMuc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
-- AS 
--SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,FecDespacho,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia
--,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,R.TipoAfiVehic AS TipoAfiVeh,R.Modalidad AS TipoRemesa,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
--,EstCumplido,EstFactura,CdConcepto,Concepto,SerieGuia,NumGuia,R.Observacion AS Observ,R.IdEstado AS CdEdstado
----detalles
--,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS Volmen,UndVol,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Cases,Cajas,Palets
--,NitRemite,Remitente,NitDestntario,Destinatario,DirOrigen,IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
--,DirDestino,IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TarifClie,D.TarifPago AS RemTarifPago
--,D.TarifTabla AS RemTarifTabla,D.VrDeclarado AS ValDeclarado,D.VrSeguro AS ValSeguro,TarifSeguro,UndTarifa,UndTarifPago
--,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Cumplido,IdCiaCump,FechaCump,DetalleCump
--,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
----columnas ocultas
----,NumManif,IdCiaManif,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp
----datos del pedido
--,NumPedido,IdCiaPed,R.FechaPed AS FecPedido,ModalidadPed
----datos de factura
--,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,NumFactura,NumDevFact,FacCantidad,FacValorTotal,FacCostoTotal,FacFaltantes,FacPesoNeto
----datos de manifiesto
--,TipoMuc,ISNULL(MucNumero,0) AS MucNumero,MucCdCia,FechaMuc,FecDespMuc,MucPlacaVeh,MucCdPoseedor,NP.RazonSocial AS NomPoseedor,MucCdRuta,MucTarifTabla,MucTarifPago,MUC.VrFletes AS VrTotFletes,MUC.VrRetencion AS MucVrRetencion
--,MUC.VrReteIca AS MucVrReteIca,MUC.VrDescuento AS MucVrDcto,MUC.VrAnticipo AS MucVrAnticipo,VrAntAdic,MUC.TarifaRet AS MucTarifRet,MUC.TarifaIca AS MucTarifIca
--,NumMintrans,EdoMintrans,MucObserv,MucTipOdp,ISNULL(MucOrdPago,0) AS MucOrdPago,MucCdCiaOdp,TipEgr,Egreso,IdCiaEgr,NumCheque,TotalEgresos
--,dbo.FuncMucListaAntcipos(ISNULL(MucNumero,0),MucCdCia) AS Anticipos
----datos del cliente
--,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
-- FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemMcias AS D ON R.TipDoc=D.TipDoc AND R.NumOrden=D.NumOrden AND R.IdCia=D.IdCia
-- INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
-- INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero 
-- INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
-- INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
-- INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
-- INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
-- INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
-- INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
-- LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Conceptos AS C ON R.CdConcepto=C.IdConcepto 
----==== Consulta de pedidos
-- LEFT JOIN (SELECT P.TipDoc AS TipoPed,P.Pedido AS nPedido,P.IdCia AS CdCiaPed,P.FecDespacho AS FechaPed,P.IdVend AS CdVend,P.Modalidad AS ModalidadPed
--,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,P.Observacion AS PedObserv,D.IdMercancia AS PedCdMcia,D.DescripMcias AS PedDescMcia
--,SUM(D.Cantidad) AS PedCantidad,SUM(D.PesoNeto) AS PedPesoNeto,SUM(D.Volumen) AS PedVolumen,SUM(D.Cases) AS PedCases
--,SUM(D.Cajas) AS PedCajas,SUM(D.Palets) AS PedPalets,MAX(D.TarifClie) AS PedTarifClie,MAX(D.TarifPago) AS PedTarifPago
-- FROM Trn_TraPedido AS P INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
-- WHERE P.TipDoc='PDT' AND P.Anulado=0 GROUP BY P.TipDoc,P.Pedido,P.IdCia,P.FecDespacho,P.IdVend,P.Modalidad,Cotizacion,IdCiaCot
--,NumAprob,IdCiaApr,FecAprob,P.Observacion,D.IdMercancia,D.DescripMcias) AS PD ON R.NumPedido=PD.nPedido AND R.IdCiaPed=PD.CdCiaPed AND D.IdMercancia=PD.PedCdMcia
----==== consulta de facturas-detalles
-- LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*-1 ELSE Cantidad END) AS FacCantidad
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCostoTotal
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades*-1 ELSE Unidades END) AS FacUnidades
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto*-1 ELSE PesoNeto END) AS FacPesoNeto
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen*-1 ELSE Volumen END) AS FacVolumen
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases*-1 ELSE Cases END) AS FacCases
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas*-1 ELSE Cajas END) AS FacCajas
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets*-1 ELSE Palets END) AS FacPalets
--,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes
--,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE Factura END) AS NumFactura
--,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFact
--FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON R.TipDoc=RF.TipRem AND R.NumOrden=RF.Remesa AND R.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem
----==== consulta de manifiestos 
-- LEFT JOIN (SELECT RM.TipRem AS MucTipoRem,RM.Remesa AS MucNumRemesa,RM.IdCiaRem AS MucCdCiaRem,RM.ItemRem AS MucItemRem,RM.TipDoc AS TipoMuc,RM.Manifiesto AS MucNumero,RM.IdCia AS MucCdCia
--,RM.TarifTabla AS MucTarifTabla,RM.TarifPago AS MucTarifPago,RemMintrans,M.Fecha AS FechaMuc,M.FecDespacho AS FecDespMuc,M.IdRuta AS MucCdRuta,M.IdOrigen AS MucCdOrigen,M.IdDestino AS MucCdDestino,M.IdVehiculo AS MucPlacaVeh
--,M.IdConductor AS MucCedCond,M.nRemolque AS MucnRemolque,TipoAfiVehic,M.IdPoseedor AS MucCdPoseedor,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos
--,VrCargos,VrDctos,TarifaFlete,TarifaRet,TarifaIca,NumMintrans,EdoMintrans,M.TipOdp AS MucTipOdp,M.OrdPago AS MucOrdPago,M.IdCiaOdp AS MucCdCiaOdp,M.FechaOdp AS MucFecOdp,M.Observacion AS MucObserv
--FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
--WHERE M.Anulado=0) AS MUC ON R.TipDoc=MUC.MucTipoRem AND R.NumOrden=MUC.MucNumRemesa AND R.IdCia=MUC.MucCdCiaRem AND D.Item=MUC.MucItemRem
----===== Orden de pago
-- LEFT JOIN (SELECT M.TipMuc AS OdpTipMuc,M.Manifiesto AS OdpManif,M.IdCiaMuc AS OdpCdCiaMuc,M.TipDoc AS TipOdp,M.OrdPago AS NumOPago,M.IdCia AS OdpCdCia,OP.Fecha AS FechaOdp
--,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto
--,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,TarifaRet,TarifaIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,OP.Observacion AS OdpObserv
-- FROM Trn_TraOrdenManif AS M INNER JOIN Trn_TraOrdenPago AS OP ON M.TipDoc=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCia=OP.IdCia
-- WHERE OP.Anulado=0) AS ODP ON MUC.MucTipOdp=ODP.TipOdp AND MUC.MucOrdPago=ODP.NumOPago AND MUC.MucCdCiaOdp=ODP.OdpCdCia
-- LEFT JOIN Terceros AS NP ON MUC.MucCdPoseedor=NP.IdTercero 
---- Egresos
-- 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 MUC.MucTipOdp=EGT.TipOrden AND MUC.MucOrdPago=EGT.NumOPago AND MUC.MucCdCiaOdp=EGT.CdCiaOpago
--
-- WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
-- AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Anulado=0
--GO
--SET ANSI_NULLS OFF
--GO
--SET QUOTED_IDENTIFIER OFF
--GO
--CREATE PROCEDURE [dbo].[paQryTraManifiestoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
--,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
-- AS 
-- SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
--,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
--,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
--,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
--,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
--,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,M.FechaOdp,M.EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos
--,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,M.TimeSys,M.FecUpdate,M.IdCiaCrea
--,M.IdUsuario AS CdUsuario,Usuario,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
----detalles
--,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,D.Volumen,UndVol
--,D.Cases,D.Cajas,D.Palets,D.NitRemite,Remitente,DirOrigen,D.IdOrigen AS CodCiuOrigen,LO.Localidad AS CiudadOrigen,NitDestntario,Destinatario,DirDestino,D.IdDestino AS CodCiuDestino,LD.Localidad AS CiudadDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago
--,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,D.Cumplido AS NumCumplidoRem,D.IdCiaCump AS CdCiaCumpRem
--,D.FechaCump AS FecCumRem,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
--,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS DetTipOdp,D.NumeroOdp AS NumOdp,D.IdCiaOdp AS CdCiaOdp,TarifOdp,PesoCont,RemMintrans,EdoRemMin
--,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango
----Datos del vehiculo
--,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,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
--,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
--,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp
-- FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
-- INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
-- INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
-- INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
-- INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
-- INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
-- INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
-- INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
-- INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
-- INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
-- INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
-- INNER JOIN Trn_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND  M.Manifiesto=MR.Manifiesto AND  M.IdCia=MR.IdCia
-- INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
-- INNER JOIN Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
-- INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal   INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal
-- INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
-- INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
-- INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia 
-- 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 M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
-- LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta  LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
-- LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango  LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
-- LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.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 EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
----Condiciones consulta general
-- WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
-- AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
-- AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
-- ORDER BY M.IdCia,M.Manifiesto
--GO
--SET ANSI_NULLS OFF
--GO
--SET QUOTED_IDENTIFIER OFF
--GO
--CREATE PROCEDURE [dbo].[paQryTraManifiestoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
--,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
-- AS 
-- SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
--,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
--,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
--,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
--,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
--,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.Anulado,M.FecDev
--,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
--,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
--,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
----Datos del vehiculo
--,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,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
--,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
--,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp
-- FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND M.IdCia=MA.IdCia
-- INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
-- INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
-- INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
-- INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
-- INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
-- INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
-- INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
-- INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
-- INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
-- INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
-- 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 M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
-- LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
-- LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.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 EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
----Condiciones consulta general
-- WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
-- AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
-- AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
--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.IdDestino AS CdDestino,LD.Localidad AS NomDestino,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob
,kmtInicial,kmtFinal,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
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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.IdDestino AS CdDestino,LD.Localidad AS NomDestino,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,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
 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].[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.IdDestino AS CdDestino,LD.Localidad AS NomDestino,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,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
 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].[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,IdDestino,Localidad,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente
,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,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,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
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].[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)
,@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)
 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)
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),@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
 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,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO