if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraDevCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraDevCum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehiculos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehiculos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehiculos_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehiculos_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComprobantesEgo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComprobantesEgo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevCum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevCumFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevCumFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosFicha]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosFicha]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosMay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosMay]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraCumplidoAnu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraCumplidoAnu]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpVehiculos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpVehiculos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraRemMciasMuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraRemMciasMuc]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryVehiculosFicha] @pmIdVehiculo VARCHAR(4)
 AS 
 SELECT IdVehiculo,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinea,LineaVeh
,V.IdColor AS CdColor,NomColor,V.IdTipoMot AS CdTipMotor,TipoMotor,V.IdCrceria AS CdCarr,TipoCar,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans
,V.IdCat AS CodCatg,Catpeaje,CdCatv,ClaseMat,Cilind,CapTanq,V.IdCom AS CdTipComb,TipoComb,V.IdLub AS CdLub,TipoLub,V.IdTlla AS CdTipLlantas,TipoLlanta
,IdMarlla,ML.Marca AS MarcaLlantas,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque
,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie, PasjerosSen,NitEmpresa,NE.RazonSocial AS Empresa,IdPropietario,NP.RazonSocial AS Propietario
,IdPoseedor,NT.RazonSocial AS Poseedor,V.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,V.IdPpd AS CdTipProp,TipoProp,VehPropio,Adquisc,NitProv,NPV.RazonSocial AS Proveedor
,FecCompra,VrComcial,VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato, V.IdAdmon AS CdAdmon,TipoAdmon, V.IdNiv AS CdNivel,NivelServicio, V.IdGrupo AS CdGrupo,GrupoProp,CdGrupR,CdTarifa
,TB.Descripcion AS ClaseTarifa, FecIngreso, FecVigencia, FecRetiro
,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,TarjProp,FecTProp,VigTProp,CdLugTp,LT.Localidad AS LugarTarjProp,Ulttramite,RespCivil,FecRCivil,VigRCivil
,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,V.Descripcion AS VehDescripcion,V.Observacion AS Observ,CdCenSer,CentroServ,CdLocal,LU.Localidad AS CiuUbicacion,LU.IdDep AS CodDpto,Departamento
,Ubicacion,FecPriServ,FecUltServ,Regtradora,CentInicial, CentFinal, VrLmtCred, VrSaldoAct, FecUltAcc, TieneAcc, FecPagImp,V.IdEstado AS CdEstado,Estado
,V.Inactivo AS Inactvo,V.IdUsuario AS CdUsuario,Usuario,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil
,DeclaracImp,TipoIngreso,V.IdOrgTra AS CdOrgTra,NomOrgTrans,GPSoperador,GPSUsuario,GPSClave,GPSIdOper,LiqFletePropio
--datos de la licencia
,Licencia,LugarLic,CatLicencia,VigLicencia,CdRutaHab
 FROM Vehiculos  AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposMot AS TM ON V.IdTipoMot=TM.IdTipoMot
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom INNER JOIN TiposLub AS TL ON V.IdLub=TL.IdLub
 INNER JOIN TiposLla AS TLL ON V.IdTlla=TLL.IdTlla INNER JOIN Marcas AS ML ON V.IdMarlla=ML.IdMarca
 INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon
 INNER JOIN TiposNivs AS NV ON V.IdNiv=NV.IdNiv
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NPV ON V.NitProv=NPV.IdTercero
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat =NS.IdTercero LEFT JOIN Localidades AS LT ON V.CdLugTp=LT.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN  TercCndtores AS CT ON V.IdConductor=CT.IdConductor
  LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar LEFT JOIN OrgTransito AS OG ON V.IdOrgTra=OG.IdOrgTra
 WHERE  IdVehiculo=@pmIdVehiculo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehiculosLta] @pmClaseVeh VARCHAR(10)=Null,@pmIdTipoVeh VARCHAR(4)=Null,@pmIdMarca VARCHAR(4)=Null
,@pmIdTipoMot VARCHAR(4)=Null,@pmIdCrceria VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null,@pmConfig VARCHAR(5)=Null,@pmIdCat VARCHAR(4)=Null
,@pmClaseMat VARCHAR(10)=Null,@pmIdCom VARCHAR(4)=Null,@pmIdLub VARCHAR(4)=Null,@pmIdTlla VARCHAR(4)=Null,@pmIdMarlla VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPpd VARCHAR(4)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
,@pmIdAdmon VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
 AS 
 SELECT IdVehiculo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,TC.TipoCar AS TipoCarr,CL.NomColor AS Color,V.Modelo AS ModeloVeh,FecRep,Config,V.IdCat AS CodCatg,CarrCapac
,PesoVacio,NumMotor,SerieChasis,RQ.IdCrceria AS RmqIdCarr,MR.Marca AS MarcaRmq,CR.NomColor AS ColorRmq,NitEmpresa,NE.RazonSocial AS Empresa
,IdPoseedor,NT.RazonSocial AS Poseedor,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CdTarifa,TarjProp,RespCivil,VigRCivil,VrComcial,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,FecPriServ,FecUltServ
,RegNalCarga,FecRegNal,VigRegNal,RevTecMec,FecTecMec,VigTecMec,V.Observacion AS Observ,TipoAfil
,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,V.IdLinea AS CdLinea,LineaVeh,V.IdColor AS CdColor,V.IdTipoMot AS CdTipMotor,TipoMotor
,V.IdCrceria AS CdCarr,VehArtic,NumLlan,NumLlans,Catpeaje,CdCatv,ClaseMat,Cilind,CapTanq,V.IdCom AS CdTipComb,TipoComb,V.IdLub AS CdLub
,TipoLub,V.IdTlla AS CdTipLlantas,TipoLlanta,IdMarlla,ML.Marca AS MarcaLlantas,PesoMax,NumSerie,V.NitProv AS NitProveedor,NPV.RazonSocial AS Proveedor,V.FecCompra AS FechaCompra
,VrAseg,V.VrAvaludo AS VlrAvaludo,V.VidaUtil AS Vida_Util,FecSalida,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdGrupR,FecIngreso
,FecVigencia,FecRetiro,FecTProp,VigTProp,CdLugTp,LT.Localidad AS LugarTarjProp,Ulttramite,FecRCivil,KmInicial,KmActual,Km2Actual,V.Descripcion AS VehDescripcion,V.CdCenSer AS CodCentro,CentroServ
,V.CdLocal AS CodCiuUbic,LU.Localidad AS CiuUbicacion,LU.IdDep AS CodDpto,Departamento,V.Ubicacion,V.PathFoto,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,FecUltAcc,TieneAcc,FecPagImp
,V.IdUsuario AS CdUsuario,Usuario,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion
,FecCertMovil,VigCertMovil,CdRutaHab,RQ.IdMarca AS RmqIdMarca,RQ.IdColor AS RmqIdColor,RC.TipoCar AS RmqTipoCar,GPSoperador,GPSUsuario,GPSClave,GPSIdOper,CantFiltros,LiqFletePropio
--campos libres
,TarjOper,FecTarjOper,VigTarjOper,CertGases,FecCertGas,VigCertGas,DeclaracImp,TipoIngreso,V.IdOrgTra AS CdOrgTra,NomOrgTrans
--Informacion coductor
,V.IdConductor,NC.RazonSocial AS Conductor,NC.IdLugarCed,LCCE.Localidad AS LugCed,CND.TipoSangre,CND.FactorRh,CND.FecNacmto,NC.TelMovil AS CelCnd,NC.Telefono AS TelCnd,NC.e_mail AS Mailcnd,nc.Direccion AS DirCnd
,NC.IdLocal,LCND.Localidad AS LocCnd,CND.Licencia,CND.CatLicencia,CCND.ClaseCuenta AS TipCtaCnd,CND.NumCuenta AS CtaCnd,BCND.Banco AS BanCnd,FECN.Fondo AS FonEps,FPCN.Fondo AS FonPen,FACN.Fondo AS FonArl
--INFORMACION TRAILER Y PROPIETARIO
,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,V.UndCapc AS UndCapacidad,Comptmtos,CapComp,PasjerosPie,PasjerosSen,VR.IdPropietario AS IdPropTra,PT.RazonSocial AS NomPropTra,LPT.Localidad AS LugCedPt
,PT.Direccion AS DirProTra,PT.TelMovil AS CelProTra,PT.Telefono AS TelProTra
--INFORMACION PROPIEATARIO DEL VEHICULO
,V.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,NP.Direccion AS DirProV,NP.TelMovil AS CelularProV,NP.Telefono AS TelProV,V.IdPpd AS CdTipProp,TipoProp,VehPropio,Adquisc
 FROM Vehiculos  AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposMot AS TM ON V.IdTipoMot=TM.IdTipoMot
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom INNER JOIN TiposLub AS TL ON V.IdLub=TL.IdLub
 INNER JOIN TiposLla AS TLL ON V.IdTlla=TLL.IdTlla INNER JOIN Marcas AS ML ON V.IdMarlla=ML.IdMarca
 INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon
 INNER JOIN TiposNivs AS NV ON V.IdNiv=NV.IdNiv
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NPV ON V.NitProv=NPV.IdTercero
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat =NS.IdTercero LEFT JOIN Localidades AS LT ON V.CdLugTp=LT.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN VehRemolq AS RQ ON V.CdRemque=RQ.IdRemque
 LEFT JOIN Marcas AS MR ON RQ.IdMarca=MR.IdMarca LEFT JOIN TiposCol AS CR ON RQ.IdColor=CR.IdColor 
 LEFT JOIN TiposCar AS RC ON RQ.IdCrceria=RC.IdCrceria LEFT JOIN OrgTransito AS OG ON V.IdOrgTra=OG.IdOrgTra
 --CONSULTA PARA INFORMACION DEL CONDUCTOR
 LEFT JOIN TercCndtores AS CND ON V.IdConductor=CND.IdConductor LEFT JOIN Localidades AS LCCE ON NC.IdLugarCed=LCCE.IdLocal
 LEFT JOIN Localidades AS LCND ON NC.IdLocal=LCND.IdLocal LEFT JOIN ClaseCta AS CCND ON CND.IdClase=CCND.IdClase
 LEFT JOIN Bancos AS BCND ON CND.IdBanco=BCND.IdBanco LEFT JOIN Fondos AS FECN ON CND.CdFonEps=FECN.IdFondo
 LEFT JOIN Fondos AS FPCN ON CND.CdFonPen=FPCN.IdFondo LEFT JOIN Fondos AS FACN ON CND.CdFonArp=FACN.IdFondo
 --CONSULTA TRAILER
 LEFT JOIN VehRemolq AS VR ON V.CdRemque=VR.IdRemque LEFT JOIN Terceros AS PT ON VR.IdPropietario=PT.IdTercero
 LEFT JOIN Localidades AS LPT ON PT.IdLugarCed=LPT.IdLocal
 WHERE ClaseVeh LIKE ISNULL(@pmClaseVeh,'%') AND V.IdTipoVeh LIKE ISNULL(@pmIdTipoVeh,'%')
 AND V.IdMarca LIKE ISNULL(@pmIdMarca,'%') AND V.IdTipoMot LIKE ISNULL(@pmIdTipoMot,'%')
 AND V.IdCrceria LIKE ISNULL(@pmIdCrceria,'%')  AND V.Modelo LIKE ISNULL(@pmModelo,'%')
 AND Config LIKE ISNULL(@pmConfig,'%') AND V.IdCat LIKE ISNULL(@pmIdCat ,'%')
 AND ClaseMat  LIKE ISNULL(@pmClaseMat,'%') AND V.IdCom LIKE ISNULL(@pmIdCom,'%')
 AND V.IdLub LIKE ISNULL(@pmIdLub,'%') AND V.IdTlla LIKE ISNULL(@pmIdTlla,'%')
 AND IdMarlla LIKE ISNULL(@pmIdMarlla,'%')  AND V.IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')  AND V.IdConductor LIKE ISNULL(@pmIdConductor,'%') 
 AND V.IdPpd LIKE ISNULL(@pmIdPpd,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND (V.Inactivo=ISNULL(@pmInactivo,0) or V.Inactivo=ISNULL(@pmInactivo,1))
 AND (V.FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND V.FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdVehiculo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.NumDevCum,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,FecDespacho,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
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv
,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro,CU.TipoCumpMT,CU.MotivoSusp,CU.ConsecSusp,CU.MvoRechazo
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,TarifFlete,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario,D.HoraLlegaCargue,D.HoraEntraCargue,D.HoraSaleCargue,D.HoraLlegaDescargue,D.HoraEntraDescargue,D.HoraSaleDescargue
,D.CodCCosto,CCosto,D.CodSubCos,SubCosto
--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,V.IdGrupo AS CdGrupoPro,GrupoProp
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia
 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 CU.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_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 LEFT JOIN Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc  AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia  AND D.ItemRem=RM.Item
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON D.CodCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CodSubCos=SC.IdSubCos
 WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND  CU.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
  ORDER BY CU.IdCia,CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoFmt] @pmTipDoc VARCHAR(3),@pmCumplidoIni INT,@pmCumplidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT CU.TipDoc AS TipCum,TipoDoc,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CU.Observacion AS Observ
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.IdEstado AS CdEstado,Estado,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.NumDevCum
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario,Leyenda
,M.Fecha AS FecManif,FecDespacho,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
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS Observ,TipoComp AS CdTipComp,NumComp AS Comprobante
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,MvoRechazo
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,TarifFlete,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario,TipoCumRemesa,MotivoSuspRem,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue
--Datos del poseedor
,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,CU.CdPlazoPago,PZ.Plazo,PZ.NVmto,PZ.DiasPago
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON CU.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 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 CU.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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta 
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc  AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia  AND D.ItemRem=RM.Item
 LEFT JOIN Plazos AS PZ ON CU.CdPlazoPago=PZ.IdPlazo
 WHERE CU.TipDoc=@pmTipDoc AND CU.Cumplido BETWEEN @pmCumplidoIni AND @pmCumplidoFin AND CU.IdCia=@pmIdCia
 ORDER BY CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FecCumplido,C.Manifiesto AS NumManif,IdCiaMuc,M.Fecha AS FecManif,C.IdVehiculo AS PlacaVeh,Modalidad
,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic
,C.Anulado AS CumAnulado,C.FecDev AS FechAnulado,C.NumDevCum,TipoComp,NumComp,CodConcepto,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,IdLocFletes,LP.Localidad AS LugarPago,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MA.TipoRuta,MvoAnulaCump,ObservAnulado,C.MvoRechazo,NumViajesCum,PesoLiqPago,PesoLiqFact,MA.MucMintrans AS TipoViaje,C.CdPlazoPago
 FROM Trn_TraCumplido AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON C.TipMuc=MA.TipDoc AND C.Manifiesto=MA.Manifiesto AND C.IdCiaMuc=MA.IdCia
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Localidades AS CO ON C.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta LEFT JOIN Localidades AS LP ON M.IdLocFletes=LP.IdLocal 
 WHERE C.TipDoc=@pmTipDoc  AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY C.IdCia,C.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevCumFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia AS CdCia,Compania,D.Fecha,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Cumplido,D.IdCiaDoc,D.FecDoc
,C.TipMuc,C.Manifiesto,C.IdCiaMuc,C.IdVehiculo AS PlacaVeh,C.Modalidad,C.DiasPlazo,C.FecPago,C.Observacion AS CumObserv
,C.TipoCumpMT,C.MotivoSusp,C.ConsecSusp,C.VrAdicCargue,C.VrAdicDescargue,C.VrAdicFlete,C.MotivoVrAdic
,C.VrDctoFlete,C.MotivoVrDcto,C.VrAdicAnticipo,C.FecEntregaDoc,C.NumRadicaMT,C.MvoAnulaCump,C.ObservAnulado,C.NumViajesCum,C.PesoLiqPago,C.PesoLiqFact
,C.CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,C.CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,C.CdRuta,R.Ruta AS DescRuta
,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,M.nRemolque,M.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.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,M.FechaOdp,M.EstOrden,D.TipoAnulacion,D.MvoAnulacion
,D.ModdDev,D.OrigenAdd,D.TipCom,D.Comprobante,D.IdCiaCom,D.Observacion AS Observ,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevCum AS D INNER JOIN Trn_TraCumplido AS C ON D.TipDoc=C.TipDoc AND D.Cumplido=C.Cumplido AND D.IdCiaDoc=C.IdCia
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 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 Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo
 INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 LEFT JOIN Localidades AS CO ON C.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta  
 WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehiculosMay]  @pmIdTipoVeh VARCHAR(4)=Null,@pmIdMarca VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
 AS 
 SELECT IdVehiculo,NumVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinea,LineaVeh
,V.IdColor AS CdColor,NomColor,Modelo,VehArtic,NumMotor,SerieChasis,NumSerie,ClaseMat,CdRemque
,Comptmtos,CapComp,NitEmpresa,NE.RazonSocial AS Empresa,IdPropietario,NP.RazonSocial AS Propietario
,IdConductor,NC.RazonSocial AS Conductor,FecIngreso, FecVigencia, FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,TarjProp,FecTProp,VigTProp,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal
,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,Descripcion,V.Observacion AS Observ
,FecPriServ,FecUltServ,TipoAfil,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,V.IdUsuario AS CdUsuario,Usuario
,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand,V.LiqFletePropio
 FROM Vehiculos  AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero 
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero 
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat =NS.IdTercero 
 WHERE V.IdTipoVeh LIKE ISNULL(@pmIdTipoVeh,'%')  AND V.IdMarca LIKE ISNULL(@pmIdMarca,'%')
 AND Modelo LIKE ISNULL(@pmModelo,'%')   AND IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND  IdConductor LIKE ISNULL(@pmIdConductor,'%')  AND  V.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (V.Inactivo=ISNULL(@pmInactivo,0) or V.Inactivo=ISNULL(@pmInactivo,1))
 AND (FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehiculos] @pmIdVehiculo VARCHAR(10),@pmNumVeh VARCHAR(10),@pmClaseVeh VARCHAR(10),@pmIdTipoVeh VARCHAR(4),@pmIdMarca VARCHAR(4),@pmIdLinea VARCHAR(10),@pmIdColor VARCHAR(4),@pmIdTipoMot VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmFecRep SMALLDATETIME,@pmConfig VARCHAR(5),@pmVehArtic BIT,@pmNumLlan INT,@pmNumLlans INT,@pmIdCat VARCHAR(4),@pmCdCatv VARCHAR(4)
,@pmClaseMat VARCHAR(10),@pmCilind DECIMAL(14,4),@pmCapTanq DECIMAL(14,4),@pmIdCom VARCHAR(4),@pmIdLub VARCHAR(4),@pmIdTlla VARCHAR(4),@pmIdMarlla VARCHAR(4),@pmPesoVacio DECIMAL(14,4),@pmPesoMax DECIMAL(14,4),@pmNumMotor VARCHAR(30),@pmSerieChasis VARCHAR(30),@pmNumSerie VARCHAR(30),@pmCdRemque VARCHAR(10),@pmLongitud DECIMAL(14,4),@pmCarrAlto DECIMAL(14,4),@pmCarrAncho DECIMAL(14,4),@pmCarrLargo DECIMAL(14,4)
,@pmCarrCapac DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmComptmtos INT,@pmCapComp VARCHAR(50),@pmPasjerosPie INT,@pmPasjerosSen INT,@pmNitEmpresa VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmIdPpd VARCHAR(4),@pmAdquisc VARCHAR(10),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVrComcial MONEY,@pmVrAseg MONEY,@pmVrAvaludo MONEY,@pmVidaUtil INT
,@pmFecSalida SMALLDATETIME,@pmNContrato INT,@pmIdAdmon VARCHAR(4),@pmIdNiv VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmCdGrupR VARCHAR(4),@pmCdTarifa VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmNumSoat VARCHAR(30),@pmFecSoat SMALLDATETIME,@pmVigSoat SMALLDATETIME,@pmNitEmpSoat VARCHAR(16),@pmTarjProp VARCHAR(30),@pmFecTProp SMALLDATETIME
,@pmVigTProp SMALLDATETIME,@pmCdLugTp VARCHAR(8),@pmUlttramite VARCHAR(150),@pmRespCivil VARCHAR(30),@pmFecRCivil SMALLDATETIME,@pmVigRCivil SMALLDATETIME,@pmRegNalCarga VARCHAR(30),@pmFecRegNal SMALLDATETIME,@pmVigRegNal SMALLDATETIME,@pmKmInicial INT,@pmKmActual INT,@pmKm2Actual INT,@pmRegtradora BIT,@pmCentInicial INT,@pmCentFinal INT,@pmVrLmtCred MONEY,@pmVrSaldoAct MONEY,@pmDescripcion VARCHAR(100)
,@pmObservacion VARCHAR(250),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmPathFoto VARCHAR(30),@pmFecPriServ SMALLDATETIME,@pmFecUltServ SMALLDATETIME,@pmFecUltAcc SMALLDATETIME,@pmTieneAcc BIT,@pmFecPagImp SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmInactivo BIT
,@pmTipoAfil VARCHAR(10),@pmRevTecMec VARCHAR(30),@pmFecTecMec SMALLDATETIME,@pmVigTecMec SMALLDATETIME,@pmCertGases VARCHAR(30),@pmFecCertGas SMALLDATETIME,@pmVigCertGas SMALLDATETIME,@pmTarjOper VARCHAR(30),@pmFecTarjOper SMALLDATETIME,@pmVigTarjOper SMALLDATETIME,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
,@pmValorCupo MONEY,@pmObligaTProd BIT,@pmGarantiaAcc BIT,@pmDocCompleta BIT,@pmCertMovilizacion VARCHAR(20),@pmFecCertMovil SMALLDATETIME,@pmVigCertMovil SMALLDATETIME,@pmCdRutaHab VARCHAR(4),@pmDeclaracImp VARCHAR(50),@pmTipoIngreso VARCHAR(4),@pmIdOrgTra VARCHAR(8),@pmGPSoperador VARCHAR(250),@pmGPSUsuario VARCHAR(50),@pmGPSClave VARCHAR(50),@pmCantFiltros DECIMAL(14,4),@pmGPSIdOper VARCHAR(16),@pmLiqFletePropio BIT
 AS 
 INSERT INTO Vehiculos (IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg
,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros,GPSIdOper,LiqFletePropio)
 VALUES (@pmIdVehiculo,@pmNumVeh,@pmClaseVeh,@pmIdTipoVeh,@pmIdMarca,@pmIdLinea,@pmIdColor,@pmIdTipoMot,@pmIdCrceria,@pmModelo,@pmFecRep,@pmConfig,@pmVehArtic,@pmNumLlan,@pmNumLlans,@pmIdCat,@pmCdCatv,@pmClaseMat,@pmCilind,@pmCapTanq,@pmIdCom,@pmIdLub,@pmIdTlla,@pmIdMarlla,@pmPesoVacio,@pmPesoMax,@pmNumMotor,@pmSerieChasis,@pmNumSerie,@pmCdRemque,@pmLongitud,@pmCarrAlto,@pmCarrAncho,@pmCarrLargo,@pmCarrCapac
,@pmUndCapc,@pmComptmtos,@pmCapComp,@pmPasjerosPie,@pmPasjerosSen,@pmNitEmpresa,@pmIdPropietario,@pmIdPoseedor,@pmIdConductor,@pmIdPpd,@pmAdquisc,@pmNitProv,@pmFecCompra,@pmVrComcial,@pmVrAseg,@pmVrAvaludo,@pmVidaUtil,@pmFecSalida,@pmNContrato,@pmIdAdmon,@pmIdNiv,@pmIdGrupo,@pmCdGrupR,@pmCdTarifa,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmNumSoat,@pmFecSoat,@pmVigSoat,@pmNitEmpSoat,@pmTarjProp,@pmFecTProp
,@pmVigTProp,@pmCdLugTp,@pmUlttramite,@pmRespCivil,@pmFecRCivil,@pmVigRCivil,@pmRegNalCarga,@pmFecRegNal,@pmVigRegNal,@pmKmInicial,@pmKmActual,@pmKm2Actual,@pmRegtradora,@pmCentInicial,@pmCentFinal,@pmVrLmtCred,@pmVrSaldoAct,@pmDescripcion,@pmObservacion,@pmCdCenSer,@pmCdLocal,@pmUbicacion,@pmPathFoto,@pmFecPriServ,@pmFecUltServ,@pmFecUltAcc,@pmTieneAcc,@pmFecPagImp,@pmIdEstado,@pmInactivo
,@pmTipoAfil,@pmRevTecMec,@pmFecTecMec,@pmVigTecMec,@pmCertGases,@pmFecCertGas,@pmVigCertGas,@pmTarjOper,@pmFecTarjOper,@pmVigTarjOper,@pmFechaAdd,@pmIdUsuario,@pmValorCupo,@pmObligaTProd,@pmGarantiaAcc,@pmDocCompleta,@pmCertMovilizacion,@pmFecCertMovil,@pmVigCertMovil,@pmCdRutaHab,@pmDeclaracImp,@pmTipoIngreso,@pmIdOrgTra,@pmGPSoperador,@pmGPSUsuario,@pmGPSClave,@pmCantFiltros,@pmGPSIdOper,@pmLiqFletePropio)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehiculos_Sel] @pmIdVehiculo VARCHAR(10),@pmNewVehiculo VARCHAR(10)
 AS 
 INSERT INTO Vehiculos (IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg
,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros,GPSIdOper,LiqFletePropio)
SELECT  @pmNewVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg
,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros,GPSIdOper,LiqFletePropio
 FROM Vehiculos WHERE  IdVehiculo=@pmIdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpVehiculos] @pmIdVehiculo VARCHAR(10),@pmNumVeh VARCHAR(10),@pmClaseVeh VARCHAR(10),@pmIdTipoVeh VARCHAR(4),@pmIdMarca VARCHAR(4),@pmIdLinea VARCHAR(10),@pmIdColor VARCHAR(4),@pmIdTipoMot VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmFecRep SMALLDATETIME,@pmConfig VARCHAR(5),@pmVehArtic BIT,@pmNumLlan INT,@pmNumLlans INT,@pmIdCat VARCHAR(4),@pmCdCatv VARCHAR(4)
,@pmClaseMat VARCHAR(10),@pmCilind DECIMAL(14,4),@pmCapTanq DECIMAL(14,4),@pmIdCom VARCHAR(4),@pmIdLub VARCHAR(4),@pmIdTlla VARCHAR(4),@pmIdMarlla VARCHAR(4),@pmPesoVacio DECIMAL(14,4),@pmPesoMax DECIMAL(14,4),@pmNumMotor VARCHAR(30),@pmSerieChasis VARCHAR(30),@pmNumSerie VARCHAR(30),@pmCdRemque VARCHAR(10),@pmLongitud DECIMAL(14,4),@pmCarrAlto DECIMAL(14,4),@pmCarrAncho DECIMAL(14,4),@pmCarrLargo DECIMAL(14,4)
,@pmCarrCapac DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmComptmtos INT,@pmCapComp VARCHAR(50),@pmPasjerosPie INT,@pmPasjerosSen INT,@pmNitEmpresa VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmIdPpd VARCHAR(4),@pmAdquisc VARCHAR(10),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVrComcial MONEY,@pmVrAseg MONEY,@pmVrAvaludo MONEY,@pmVidaUtil INT
,@pmFecSalida SMALLDATETIME,@pmNContrato INT,@pmIdAdmon VARCHAR(4),@pmIdNiv VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmCdGrupR VARCHAR(4),@pmCdTarifa VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmNumSoat VARCHAR(30),@pmFecSoat SMALLDATETIME,@pmVigSoat SMALLDATETIME,@pmNitEmpSoat VARCHAR(16),@pmTarjProp VARCHAR(30),@pmFecTProp SMALLDATETIME
,@pmVigTProp SMALLDATETIME,@pmCdLugTp VARCHAR(8),@pmUlttramite VARCHAR(150),@pmRespCivil VARCHAR(30),@pmFecRCivil SMALLDATETIME,@pmVigRCivil SMALLDATETIME,@pmRegNalCarga VARCHAR(30),@pmFecRegNal SMALLDATETIME,@pmVigRegNal SMALLDATETIME,@pmKmInicial INT,@pmKmActual INT,@pmKm2Actual INT,@pmRegtradora BIT,@pmCentInicial INT,@pmCentFinal INT,@pmVrLmtCred MONEY,@pmVrSaldoAct MONEY,@pmDescripcion VARCHAR(100)
,@pmObservacion VARCHAR(250),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmPathFoto VARCHAR(30),@pmFecPriServ SMALLDATETIME,@pmFecUltServ SMALLDATETIME,@pmFecUltAcc SMALLDATETIME,@pmTieneAcc BIT,@pmFecPagImp SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmInactivo BIT
,@pmTipoAfil VARCHAR(10),@pmRevTecMec VARCHAR(30),@pmFecTecMec SMALLDATETIME,@pmVigTecMec SMALLDATETIME,@pmCertGases VARCHAR(30),@pmFecCertGas SMALLDATETIME,@pmVigCertGas SMALLDATETIME,@pmTarjOper VARCHAR(30),@pmFecTarjOper SMALLDATETIME,@pmVigTarjOper SMALLDATETIME,@pmFechaUpdate SMALLDATETIME
,@pmValorCupo MONEY,@pmObligaTProd BIT,@pmGarantiaAcc BIT,@pmDocCompleta BIT,@pmCertMovilizacion VARCHAR(20),@pmFecCertMovil SMALLDATETIME,@pmVigCertMovil SMALLDATETIME,@pmCdRutaHab VARCHAR(4),@pmDeclaracImp VARCHAR(50),@pmTipoIngreso VARCHAR(4),@pmIdOrgTra VARCHAR(8),@pmGPSoperador VARCHAR(250),@pmGPSUsuario VARCHAR(50),@pmGPSClave VARCHAR(50),@pmCantFiltros DECIMAL(14,4),@pmGPSIdOper VARCHAR(16),@pmLiqFletePropio BIT
 AS 
 UPDATE Vehiculos SET NumVeh=@pmNumVeh,ClaseVeh=@pmClaseVeh,IdTipoVeh=@pmIdTipoVeh,IdMarca=@pmIdMarca,IdLinea=@pmIdLinea,IdColor=@pmIdColor,IdTipoMot=@pmIdTipoMot,IdCrceria=@pmIdCrceria,Modelo=@pmModelo,FecRep=@pmFecRep,Config=@pmConfig,VehArtic=@pmVehArtic,NumLlan=@pmNumLlan,NumLlans=@pmNumLlans,IdCat=@pmIdCat,CdCatv=@pmCdCatv,ClaseMat=@pmClaseMat,Cilind=@pmCilind,CapTanq=@pmCapTanq,IdCom=@pmIdCom,IdLub=@pmIdLub
,IdTlla=@pmIdTlla,IdMarlla=@pmIdMarlla,PesoVacio=@pmPesoVacio,PesoMax=@pmPesoMax,NumMotor=@pmNumMotor,SerieChasis=@pmSerieChasis,NumSerie=@pmNumSerie,CdRemque=@pmCdRemque,Longitud=@pmLongitud,CarrAlto=@pmCarrAlto,CarrAncho=@pmCarrAncho,CarrLargo=@pmCarrLargo,CarrCapac=@pmCarrCapac,UndCapc=@pmUndCapc,Comptmtos=@pmComptmtos,CapComp=@pmCapComp,PasjerosPie=@pmPasjerosPie,PasjerosSen=@pmPasjerosSen,NitEmpresa=@pmNitEmpresa
,IdPropietario=@pmIdPropietario,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,IdPpd=@pmIdPpd,Adquisc=@pmAdquisc,NitProv=@pmNitProv,FecCompra=@pmFecCompra,VrComcial=@pmVrComcial,VrAseg=@pmVrAseg,VrAvaludo=@pmVrAvaludo,VidaUtil=@pmVidaUtil,FecSalida=@pmFecSalida,NContrato=@pmNContrato,IdAdmon=@pmIdAdmon,IdNiv=@pmIdNiv,IdGrupo=@pmIdGrupo,CdGrupR=@pmCdGrupR,CdTarifa=@pmCdTarifa,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia
,FecRetiro=@pmFecRetiro,NumSoat=@pmNumSoat,FecSoat=@pmFecSoat,VigSoat=@pmVigSoat,NitEmpSoat=@pmNitEmpSoat,TarjProp=@pmTarjProp,FecTProp=@pmFecTProp,VigTProp=@pmVigTProp,CdLugTp=@pmCdLugTp,Ulttramite=@pmUlttramite,RespCivil=@pmRespCivil,FecRCivil=@pmFecRCivil,VigRCivil=@pmVigRCivil,RegNalCarga=@pmRegNalCarga,FecRegNal=@pmFecRegNal,VigRegNal=@pmVigRegNal,KmInicial=@pmKmInicial,KmActual=@pmKmActual,Km2Actual=@pmKm2Actual
,Regtradora=@pmRegtradora,CentInicial=@pmCentInicial,CentFinal=@pmCentFinal,VrLmtCred=@pmVrLmtCred,VrSaldoAct=@pmVrSaldoAct,Descripcion=@pmDescripcion,Observacion=@pmObservacion,CdCenSer=@pmCdCenSer,CdLocal=@pmCdLocal,Ubicacion=@pmUbicacion,PathFoto=@pmPathFoto,FecPriServ=@pmFecPriServ,FecUltServ=@pmFecUltServ,FecUltAcc=@pmFecUltAcc,TieneAcc=@pmTieneAcc,FecPagImp=@pmFecPagImp,IdEstado=@pmIdEstado,Inactivo=@pmInactivo
,TipoAfil=@pmTipoAfil,RevTecMec=@pmRevTecMec,FecTecMec=@pmFecTecMec,VigTecMec=@pmVigTecMec,CertGases=@pmCertGases,FecCertGas=@pmFecCertGas,VigCertGas=@pmVigCertGas,TarjOper=@pmTarjOper,FecTarjOper=@pmFecTarjOper,VigTarjOper=@pmVigTarjOper,FechaUpdate=@pmFechaUpdate
,ValorCupo=@pmValorCupo,ObligaTProd=@pmObligaTProd,GarantiaAcc=@pmGarantiaAcc,DocCompleta=@pmDocCompleta,CertMovilizacion=@pmCertMovilizacion,FecCertMovil=@pmFecCertMovil,VigCertMovil=@pmVigCertMovil,CdRutaHab=@pmCdRutaHab,DeclaracImp=@pmDeclaracImp,TipoIngreso=@pmTipoIngreso,IdOrgTra=@pmIdOrgTra,GPSoperador=@pmGPSoperador,GPSUsuario=@pmGPSUsuario,GPSClave=@pmGPSClave,CantFiltros=@pmCantFiltros,GPSIdOper=@pmGPSIdOper,LiqFletePropio=@pmLiqFletePropio
 WHERE IdVehiculo=@pmIdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehiculos] @pmIdVehiculo VARCHAR(10)
 AS 
SELECT IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla
,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa
,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia
,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual
,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,FechaUpdate,IdUsuario
,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros,GPSIdOper,LiqFletePropio
 FROM Vehiculos
 WHERE IdVehiculo=@pmIdVehiculo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,Anulado,FecDev,Observacion,IdEstado
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CdRuta,CdOrigen,CdDestino,TipoComp,NumComp,CodConcepto
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue
,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,CdPlazoPago,NumDevCum,MvoRechazo
 FROM Trn_TraCumplido
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmPesoLiqPago INT,@pmPesoLiqFact INT,@pmCdPlazoPago VARCHAR(4),@pmNumDevCum INT,@pmMvoRechazo INT
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraCumplido (TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CdRuta,CdOrigen,CdDestino
,Anulado,FecDev,TipoComp,NumComp,CodConcepto,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,CdPlazoPago,NumDevCum,MvoRechazo)
 VALUES (@pmTipDoc,@pmCumplido,@pmIdCia,@pmFecha,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmModalidad,@pmDiasPlazo,@pmFecPago,@pmTipoMargen,@pmMargenFalt,@pmUndCalcFalt,@pmTarifFaltPago,@pmTarifFaltCobro,@pmNRadicaDoc,@pmIdCiaRadic,@pmCdCiaOfic,@pmFecRadic
,@pmCdRuta,@pmCdOrigen,@pmCdDestino,@pmAnulado,@pmFecDev,@pmTipoComp,@pmNumComp,@pmCodConcepto,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoCumpMT,@pmMotivoSusp,@pmConsecSusp,@pmVrAdicCargue,@pmVrAdicDescargue,@pmVrAdicFlete
,@pmMotivoVrAdic,@pmVrDctoFlete,@pmMotivoVrDcto,@pmVrAdicAnticipo,@pmFecEntregaDoc,0,@pmMvoAnulaCump,@pmObservAnulado,@pmNumViajesCum,@pmPesoLiqPago,@pmPesoLiqFact,@pmCdPlazoPago,@pmNumDevCum,@pmMvoRechazo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmPesoLiqPago INT,@pmPesoLiqFact INT,@pmCdPlazoPago VARCHAR(4)
,@pmNumDevCum INT,@pmMvoRechazo INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraCumplido SET Fecha=@pmFecha,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,IdVehiculo=@pmIdVehiculo,Modalidad=@pmModalidad,DiasPlazo=@pmDiasPlazo,FecPago=@pmFecPago,TipoMargen=@pmTipoMargen
,MargenFalt=@pmMargenFalt,UndCalcFalt=@pmUndCalcFalt,TarifFaltPago=@pmTarifFaltPago,TarifFaltCobro=@pmTarifFaltCobro,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,NRadicaDoc=@pmNRadicaDoc,IdCiaRadic=@pmIdCiaRadic,CdCiaOfic=@pmCdCiaOfic,FecRadic=@pmFecRadic,CdRuta=@pmCdRuta,CdOrigen=@pmCdOrigen,CdDestino=@pmCdDestino,TipoComp=@pmTipoComp,NumComp=@pmNumComp,CodConcepto=@pmCodConcepto,FecUpdate=@pmFecUpdate
,TipoCumpMT=@pmTipoCumpMT,MotivoSusp=@pmMotivoSusp,ConsecSusp=@pmConsecSusp,VrAdicCargue=@pmVrAdicCargue,VrAdicDescargue=@pmVrAdicDescargue,VrAdicFlete=@pmVrAdicFlete,MotivoVrAdic=@pmMotivoVrAdic,VrDctoFlete=@pmVrDctoFlete
,MotivoVrDcto=@pmMotivoVrDcto,VrAdicAnticipo=@pmVrAdicAnticipo,FecEntregaDoc=@pmFecEntregaDoc,MvoAnulaCump=@pmMvoAnulaCump,ObservAnulado=@pmObservAnulado,NumViajesCum=@pmNumViajesCum,PesoLiqPago=@pmPesoLiqPago,PesoLiqFact=@pmPesoLiqFact,CdPlazoPago=@pmCdPlazoPago
,NumDevCum=@pmNumDevCum,MvoRechazo=@pmMvoRechazo
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpTraCumplidoAnu] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumDevCum INT,@pmMvoRechazo INT
 AS 
 UPDATE Trn_TraCumplido SET Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,MvoAnulaCump=@pmMvoAnulaCump,ObservAnulado=@pmObservAnulado,NumDevCum=@pmNumDevCum,MvoRechazo=@pmMvoRechazo
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComprobantesEgo] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null
 AS 
 SELECT C.TipCom AS CdTipEgr,TipoCom,C.Comprobante AS NumEgreso,C.IdCia AS CdCiaEgr,Compania,C.Fecha AS FecEgreso,T.TipoId AS TercTipo,C.IdTercero AS NitTercero,T.Dv AS TercDv
,T.RazonSocial AS NomTercero,C.VrTotal,C.IdCta AS CdCtaCte,NumeroCta,CTA.IdBanco AS CdBanco,Banco,C.EnEfectivo,C.NumCheque AS NoCheque,C.FecCheque
,C.TipDoc,C.Documento,C.IdCiaDoc,C.Anulado,C.NumDev,C.FecDev,C.pVehiculo,C.VehPropio AS VehEsPropio,C.CedCondtor,CD.RazonSocial AS NomConductor,C.Beneficiario,C.Anticipo,C.Observacion AS Observ
,OP.TipDoc AS TipOdp,OP.OrdPago,OP.IdCia AS CiaOdp,OP.TipMuc,OP.Manifiesto,OP.IdCiaMuc
 FROM Trn_Comprobantes AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 INNER JOIN TiposCom AS TC ON C.TipCom=TC.IdCom
 LEFT JOIN CtasCorrientes AS CTA ON C.IdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN Terceros AS CD ON C.CedCondtor=CD.IdTercero 
 LEFT JOIN (SELECT TipDoc,OrdPago,IdCia,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo FROM Trn_TraOrdenManif
 UNION ALL SELECT TipDoc,Liquidacion,IdCia,TipOds,NumOrden,IdCiaOds,IdVehiculo FROM Trn_TraOrdenLiq) AS OP ON C.TipDoc=OP.TipDoc AND C.Documento=OP.OrdPago AND C.IdCiaDoc=OP.IdCia
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.EsEgreso=1 
 AND (C.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (C.IdTercero=@pmIdTercero OR @pmIdTercero IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevCum] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Cumplido,IdCiaDoc,FecDoc,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,TipoAnulacion,MvoAnulacion
,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_TraDevCum
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraDevCum] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME
,@pmModdDev VARCHAR(10),@pmOrigenAdd VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmTipoAnulacion INT,@pmMvoAnulacion VARCHAR(5),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraDevCum (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Cumplido,IdCiaDoc,FecDoc,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,TimeSys,IdCiaCrea,IdUsuario,TipoAnulacion,MvoAnulacion)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmCumplido,@pmIdCiaDoc,@pmFecDoc,@pmModdDev,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoAnulacion,@pmMvoAnulacion)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.NumDevCum,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,FecDespacho,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
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv
,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro,CU.TipoCumpMT,CU.MotivoSusp,CU.ConsecSusp,CU.MvoRechazo
,dbo.FuncTraCumplidoCobro(CU.TipDoc,CU.Cumplido,CU.IdCia) AS VrTotalClie,dbo.FuncTraCumplidoPago(CU.TipDoc,CU.Cumplido,CU.IdCia) AS VrTotalPago
--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,V.IdGrupo AS CdGrupoPro,GrupoProp
,M.Remesa,M.IdCiaRem,RMT.Fecha AS Fecremesa,RMT.Comprobante AS Cmpremesa,RMT.TipCom AS TipComRmt
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia
 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 CU.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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN (SELECT NumOrden,IdCia,Fecha,TipCom,Comprobante,IdCiaCom FROM Trn_TraRemesa WHERE TipDoc='RMT') AS RMT ON M.Remesa=RMT.NumOrden AND M.IdCiaRem=RMT.IdCia 
 WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND  CU.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
  ORDER BY CU.IdCia,CU.Cumplido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpTraRemMciasMuc] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdMercancia VARCHAR(16),@pmDescripMcias VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmdmsAlto DECIMAL(14,4),@pmdmsAncho DECIMAL(14,4),@pmdmsLargo DECIMAL(14,4),@pmVolumen DECIMAL(14,4)
,@pmUndVol VARCHAR(10),@pmIdUnd VARCHAR(4),@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmCdRango VARCHAR(4),@pmCases INT,@pmCajas INT,@pmPalets INT,@pmNitRemite VARCHAR(16),@pmRemitente VARCHAR(250),@pmDirOrigen VARCHAR(250),@pmIdOrigen VARCHAR(8),@pmNitDestntario VARCHAR(16),@pmDestinatario VARCHAR(250)
,@pmDirDestino VARCHAR(250),@pmIdDestino VARCHAR(8),@pmTarifPago MONEY,@pmTarifTabla MONEY,@pmRemision DECIMAL(18,2),@pmDocCliente VARCHAR(30),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmSedeRem VARCHAR(10),@pmSedeDest VARCHAR(10)
 AS 
 UPDATE Trn_TraRemMcias SET IdMercancia=@pmIdMercancia,DescripMcias=@pmDescripMcias,Cantidad=@pmCantidad,PesoNeto=@pmPesoNeto,UndMed=@pmUndMed,dmsAlto=@pmdmsAlto,dmsAncho=@pmdmsAncho,dmsLargo=@pmdmsLargo,Volumen=@pmVolumen,UndVol=@pmUndVol,IdUnd=@pmIdUnd,IdEmp=@pmIdEmp,IdNat=@pmIdNat,IdTmcia=@pmIdTmcia,IdMnjo=@pmIdMnjo,CdRango=@pmCdRango
,Cases=@pmCases,Cajas=@pmCajas,Palets=@pmPalets,NitRemite=@pmNitRemite,Remitente=@pmRemitente,DirOrigen=@pmDirOrigen,IdOrigen=ISNULL(@pmIdOrigen,IdOrigen),NitDestntario=@pmNitDestntario,Destinatario=@pmDestinatario,DirDestino=@pmDirDestino,IdDestino=ISNULL(@pmIdDestino,IdDestino),TarifPago=ISNULL(@pmTarifPago,TarifPago),TarifTabla=ISNULL(@pmTarifTabla,TarifTabla)
,Remision=@pmRemision,DocCliente=@pmDocCliente,Referencia1=@pmReferencia1,Referencia2=@pmReferencia2,Referencia3=@pmReferencia3,SedeRem=@pmSedeRem,SedeDest=@pmSedeDest
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia AND Item=@pmItem
GO

--marzo 10
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCorrResiduos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsCorrResiduos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDesagregaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDesagregaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMercancias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsResiduosPeligrosos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsResiduosPeligrosos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCorrResiduos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCorrResiduos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDesagregaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDesagregaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDesagregacionesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDesagregacionesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMercancias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMercanciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMercanciasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryResiduosPeligrosos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryResiduosPeligrosos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryResiduosPeligrososLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryResiduosPeligrososLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryUndMedDse]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryUndMedDse]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCorrResiduos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpCorrResiduos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDesagregaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDesagregaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMercancias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpResiduosPeligrosos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpResiduosPeligrosos]
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryResiduosPeligrososLta] @pmInactivo BIT=Null
 AS 
 SELECT R.CodigoUN,R.Designacion,R.Clase,R.PeligroSec,R.GrupoEmb
,R.IdCoRes,C.CodigoCR,C.GrupoRP,C.CorrResiduo,R.IdCRdes,D.CodigoDR,D.Desagregacion,R.Inactivo
 FROM ResiduosPeligrosos AS R LEFT JOIN CorrResiduos AS C ON R.IdCoRes=C.IdCorr
 LEFT JOIN Desagregaciones AS D ON R.IdCRdes=D.IdDrp
 WHERE (R.Inactivo=@pmInactivo OR @pmInactivo IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMercanciasLta] @pmIdGrupo VARCHAR(10)=Null,@pmIdNat VARCHAR(4)=Null,@pmIdTmcia VARCHAR(4)=Null
,@pmInactivo BIT=Null
 AS 
 SELECT IdMercancia,DescripMcia,M.IdGrupo AS CdGrupo,GrupoMcia,M.UndMed AS Und_Med,UT.Unidad AS UM_PesoTra,M.IdUnd AS CdUndPre,UM.Unidad,UM.IdEmp AS CdEmp
,M.IdNat AS CdNat,Natlzaprod,M.IdMnjo AS CdMnjo,ManejoMcia,M.IdTmcia AS CdTmcia,TipoMcia,Contenedor,M.IdProducto AS CdProducto,DescripProd,CodigoMcia
,M.IdEmp AS CdEmp,Empaque,EstadoMcia,M.UmCapac,M.UM_Prod,UP.Unidad AS DescUMprod,M.CodigoUN,UN.Designacion,UN.Clase AS ClaseUN,UN.PeligroSec,UN.GrupoEmb
,UN.IdCoRes,CR.GrupoRP,CR.CodigoCR,CR.CorrResiduo,UN.IdCRdes,DG.CodigoDR,DG.Desagregacion
,M.IdEstado AS CdEstado,Estado,M.Inactivo AS Inactvo,M.FechaAdd AS FechaCrea,M.FechaUpdate AS FechaAct,M.IdUsuario AS CdUsuario,Usuario
 FROM Mercancias AS M INNER JOIN GruposMcia AS G ON M.IdGrupo=G.IdGrupo INNER JOIN Sys_Um AS UT ON M.UndMed=UT.UndMed
 INNER JOIN UndMed AS UM ON M.IdUnd=UM.IdUnd INNER JOIN TiposNat AS N ON M.IdNat=N.IdNat
 INNER JOIN TiposMnjo AS MM ON M.IdMnjo=MM.IdMnjo INNER JOIN TiposMcia AS TM ON M.IdTmcia=TM.IdTmcia
 INNER JOIN EstadoPro AS EP ON M.IdEstado=EP.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario 
 LEFT JOIN ProdMcias AS P ON M.IdProducto=P.IdProducto LEFT JOIN Empaques AS E ON M.IdEmp=E.IdEmp
 LEFT JOIN Sys_Um AS UP ON M.UM_Prod=UP.UndMed
 LEFT JOIN ResiduosPeligrosos AS UN ON M.CodigoUN=UN.CodigoUN LEFT JOIN CorrResiduos AS CR ON UN.IdCoRes=CR.IdCorr
 LEFT JOIN Desagregaciones AS DG ON UN.IdCRdes=DG.IdDrp
 WHERE M.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND M.IdNat LIKE ISNULL(@pmIdNat,'%') AND M.IdTmcia LIKE ISNULL(@pmIdTmcia,'%')
  AND (M.Inactivo=ISNULL(@pmInactivo,0) or  M.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY DescripMcia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryCorrResiduos] @pmIdCorr VARCHAR(8)
 AS 
 IF @pmIdCorr IS NULL
  BEGIN
   SELECT IdCorr,GrupoRP,CodigoCR,CorrResiduo,Inactivo
   FROM CorrResiduos
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdCorr,GrupoRP,CodigoCR,CorrResiduo,Inactivo
   FROM CorrResiduos
   WHERE IdCorr=@pmIdCorr
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsCorrResiduos] @pmIdCorr VARCHAR(8),@pmGrupoRP VARCHAR(4),@pmCodigoCR VARCHAR(10),@pmCorrResiduo VARCHAR(500),@pmInactivo BIT
 AS 
 INSERT INTO CorrResiduos (IdCorr,GrupoRP,CodigoCR,CorrResiduo,Inactivo)
 VALUES (@pmIdCorr,@pmGrupoRP,@pmCodigoCR,@pmCorrResiduo,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpCorrResiduos] @pmIdCorr VARCHAR(8),@pmGrupoRP VARCHAR(4),@pmCodigoCR VARCHAR(10),@pmCorrResiduo VARCHAR(500),@pmInactivo BIT
 AS 
 UPDATE CorrResiduos SET GrupoRP=@pmGrupoRP,CodigoCR=@pmCodigoCR,CorrResiduo=@pmCorrResiduo,Inactivo=@pmInactivo
 WHERE IdCorr=@pmIdCorr
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDesagregacionesLta] @pmInactivo BIT=Null
 AS 
 SELECT D.IdDrp,D.CodigoDR,D.Desagregacion,D.CodigoCR AS IdCorrRes,C.GrupoRP,C.CorrResiduo,C.CodigoCR,D.Inactivo
 FROM Desagregaciones AS D INNER JOIN CorrResiduos AS C ON D.CodigoCR=C.IdCorr
 WHERE (D.Inactivo=@pmInactivo OR @pmInactivo IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsResiduosPeligrosos] @pmCodigoUN VARCHAR(30),@pmDesignacion VARCHAR(500),@pmClase VARCHAR(10),@pmPeligroSec VARCHAR(10),@pmGrupoEmb VARCHAR(20),@pmIdCoRes VARCHAR(8),@pmIdCRdes VARCHAR(8),@pmInactivo BIT
 AS 
 INSERT INTO ResiduosPeligrosos (CodigoUN,Designacion,Clase,PeligroSec,GrupoEmb,IdCoRes,IdCRdes,Inactivo)
 VALUES (@pmCodigoUN,@pmDesignacion,@pmClase,@pmPeligroSec,@pmGrupoEmb,@pmIdCoRes,@pmIdCRdes,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpResiduosPeligrosos] @pmCodigoUN VARCHAR(30),@pmDesignacion VARCHAR(500),@pmClase VARCHAR(10),@pmPeligroSec VARCHAR(10),@pmGrupoEmb VARCHAR(20),@pmIdCoRes VARCHAR(8),@pmIdCRdes VARCHAR(8),@pmInactivo BIT
 AS 
 UPDATE ResiduosPeligrosos SET Designacion=@pmDesignacion,Clase=@pmClase,PeligroSec=@pmPeligroSec,GrupoEmb=@pmGrupoEmb,IdCoRes=@pmIdCoRes,IdCRdes=@pmIdCRdes,Inactivo=@pmInactivo
 WHERE CodigoUN=@pmCodigoUN
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryResiduosPeligrosos] @pmCodigoUN VARCHAR(30)
 AS 
 SELECT CodigoUN,Designacion,Clase,PeligroSec,GrupoEmb,IdCoRes,IdCRdes,Inactivo
 FROM ResiduosPeligrosos
 WHERE CodigoUN=@pmCodigoUN
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDesagregaciones] @pmIdDrp VARCHAR(8),@pmCodigoDR VARCHAR(10),@pmDesagregacion VARCHAR(500),@pmCodigoCR VARCHAR(8),@pmInactivo BIT
 AS 
 INSERT INTO Desagregaciones (IdDrp,CodigoDR,Desagregacion,CodigoCR,Inactivo)
 VALUES (@pmIdDrp,@pmCodigoDR,@pmDesagregacion,@pmCodigoCR,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDesagregaciones] @pmIdDrp VARCHAR(8),@pmCodigoDR VARCHAR(10),@pmDesagregacion VARCHAR(500),@pmCodigoCR VARCHAR(8),@pmInactivo BIT
 AS 
 UPDATE Desagregaciones SET CodigoDR=@pmCodigoDR,Desagregacion=@pmDesagregacion,CodigoCR=@pmCodigoCR,Inactivo=@pmInactivo
 WHERE IdDrp=@pmIdDrp
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDesagregaciones] @pmIdDrp VARCHAR(8)
 AS 
 SELECT IdDrp,CodigoDR,Desagregacion,CodigoCR,Inactivo
 FROM Desagregaciones
 WHERE IdDrp=@pmIdDrp
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryUndMedDse] @pmClaseEmp VARCHAR(10)=Null
 AS 
 IF @pmClaseEmp IS NULL
  BEGIN
   SELECT U.IdUnd,U.Unidad,E.Empaque+ '-'+U.Unidad+' ('+ U.ClaseEmp+')' AS DsUnd
   FROM UndMed AS U LEFT JOIN Empaques AS E ON U.IdEmp=E.IdEmp 
   WHERE U.Inactivo=0
   ORDER BY E.Empaque
  END
 ELSE
  BEGIN 
   IF @pmClaseEmp='?'
    BEGIN
     SELECT U.IdUnd,U.Unidad,E.Empaque+ '-'+U.Unidad+' ('+ U.ClaseEmp+')' AS DsUnd
     FROM UndMed AS U LEFT JOIN Empaques AS E ON U.IdEmp=E.IdEmp 
     WHERE U.ClaseEmp IN ('EMBALAJE','PRIMARIO') AND U.Inactivo=0
     ORDER BY E.Empaque
    END
   ELSE
    BEGIN
     SELECT U.IdUnd,U.Unidad,E.Empaque+ '-'+U.Unidad+' ('+ U.ClaseEmp+')' AS DsUnd
     FROM UndMed AS U LEFT JOIN Empaques AS E ON U.IdEmp=E.IdEmp 
     WHERE U.ClaseEmp=@pmClaseEmp AND U.Inactivo=0
     ORDER BY E.Empaque
    END
   END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMercancias] @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10)
,@pmIdUnd VARCHAR(4),@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT
,@pmIdProducto VARCHAR(16),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmUmCapac VARCHAR(10),@pmCodigoUN VARCHAR(30),@pmUM_Prod VARCHAR(10),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Mercancias (IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto,UmCapac,IdEstado,Inactivo,FechaAdd,IdUsuario,CodigoUN,UM_Prod)
 VALUES (@pmIdMercancia,@pmDescripMcia,@pmCodigoMcia,@pmIdGrupo,@pmUndMed,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmEstadoMcia,@pmContenedor,@pmIdProducto
,@pmUmCapac,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmCodigoUN,@pmUM_Prod)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMercancias] @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT,@pmIdProducto VARCHAR(16)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmUmCapac VARCHAR(10),@pmCodigoUN VARCHAR(30),@pmUM_Prod VARCHAR(10),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Mercancias SET DescripMcia=@pmDescripMcia,CodigoMcia=@pmCodigoMcia,IdGrupo=@pmIdGrupo,UndMed=@pmUndMed,IdUnd=@pmIdUnd
,IdNat=@pmIdNat,IdMnjo=@pmIdMnjo,IdTmcia=@pmIdTmcia,Contenedor=@pmContenedor,IdProducto=@pmIdProducto,IdEstado=@pmIdEstado,Inactivo=@pmInactivo
,IdEmp=@pmIdEmp,EstadoMcia=@pmEstadoMcia,UmCapac=@pmUmCapac,CodigoUN=@pmCodigoUN,UM_Prod=@pmUM_Prod,FechaUpdate=@pmFechaUpdate
 WHERE IdMercancia=@pmIdMercancia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMercancias] @pmIdMercancia VARCHAR(16)
 AS 
 SELECT IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto
,IdEstado,Inactivo,UmCapac,CodigoUN,UM_Prod,FechaAdd,FechaUpdate,IdUsuario
 FROM Mercancias
 WHERE IdMercancia=@pmIdMercancia
GO