if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTraConcPago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelTraConcPago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraConcPago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraConcPago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraFletes]
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].[paQryTraConcPago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConcPago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFletesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFletesLta]
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].[paUpTraConcPago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraConcPago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraFletes]
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


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,V.EdoVincula,V.ChipPeajes,V.ChipCombustible,V.FactorRend,V.ComsnCond
--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,V.FecIngreso
,V.FecVigencia,V.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,V.EdoVincula,V.ChipPeajes,V.ChipCombustible,V.FactorRend,V.ComsnCond
--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 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,@pmEdoVincula INT
,@pmChipPeajes BIT,@pmChipCombustible BIT,@pmFactorRend DECIMAL(16,6),@pmComsnCond DECIMAL(14,4)
 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,EdoVincula,ChipPeajes,ChipCombustible,FactorRend,ComsnCond)
 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,@pmEdoVincula,@pmChipPeajes,@pmChipCombustible,@pmFactorRend,@pmComsnCond)
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,EdoVincula,ChipPeajes,ChipCombustible,FactorRend,ComsnCond)
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,EdoVincula,ChipPeajes,ChipCombustible,FactorRend,ComsnCond
 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,@pmEdoVincula INT,@pmChipPeajes BIT,@pmChipCombustible BIT
,@pmFactorRend DECIMAL(16,6),@pmComsnCond DECIMAL(14,4)
 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,EdoVincula=@pmEdoVincula,ChipPeajes=@pmChipPeajes,ChipCombustible=@pmChipCombustible
,FactorRend=@pmFactorRend,ComsnCond=@pmComsnCond
 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,EdoVincula,ChipPeajes,ChipCombustible
,FactorRend,ComsnCond
 FROM Vehiculos
 WHERE IdVehiculo=@pmIdVehiculo
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,V.EdoVincula,V.ChipPeajes,V.ChipCombustible,V.FactorRend,V.ComsnCond
 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraFletes] @pmIdConcepto VARCHAR(4),@pmFechaInicio SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmImporte MONEY,@pmTipoTarif CHAR(1),@pmTipoRuta VARCHAR(10),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmCdTipoVeh VARCHAR(4),@pmTipoAfilVeh VARCHAR(10),@pmId_Vehiculo VARCHAR(10),@pmNitCliente VARCHAR(16),@pmEstado_Mcia VARCHAR(20),@pmEsDeduccion BIT,@pmEsDescuento BIT
,@pmTarifDcto DECIMAL(14,4),@pmFecInicioDcto SMALLDATETIME,@pmFecFinalDcto SMALLDATETIME,@pmObservacion VARCHAR(500),@pmInactivo BIT,@pmIncOtroPago BIT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraFletes (IdConcepto,FechaInicio,FechaFinal,Importe,TipoTarif,TipoRuta,CdRuta,CdCiuOrigen,CdCiuDestino,CdTipoVeh,TipoAfilVeh,Id_Vehiculo,NitCliente,Estado_Mcia,IncOtroPago,EsDeduccion,EsDescuento,TarifDcto,FecInicioDcto,FecFinalDcto,Observacion,Inactivo,FechaCrea,IdUsuario)
 VALUES (@pmIdConcepto,@pmFechaInicio,@pmFechaFinal,@pmImporte,@pmTipoTarif,@pmTipoRuta,@pmCdRuta,@pmCdCiuOrigen,@pmCdCiuDestino,@pmCdTipoVeh,@pmTipoAfilVeh,@pmId_Vehiculo,@pmNitCliente,@pmEstado_Mcia,@pmIncOtroPago,@pmEsDeduccion,@pmEsDescuento,@pmTarifDcto,@pmFecInicioDcto,@pmFecFinalDcto,@pmObservacion,@pmInactivo,@pmFechaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraFletes] @pmId INT,@pmIdConcepto VARCHAR(4),@pmFechaInicio SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmImporte MONEY,@pmTipoTarif CHAR(1),@pmTipoRuta VARCHAR(10),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmCdTipoVeh VARCHAR(4),@pmTipoAfilVeh VARCHAR(10),@pmId_Vehiculo VARCHAR(10),@pmNitCliente VARCHAR(16),@pmEstado_Mcia VARCHAR(20),@pmEsDeduccion BIT,@pmEsDescuento BIT
,@pmTarifDcto DECIMAL(14,4),@pmFecInicioDcto SMALLDATETIME,@pmFecFinalDcto SMALLDATETIME,@pmObservacion VARCHAR(500),@pmInactivo BIT,@pmIncOtroPago BIT,@pmFechaAct SMALLDATETIME
 AS 
 UPDATE Trn_TraFletes SET IdConcepto=@pmIdConcepto,FechaInicio=@pmFechaInicio,FechaFinal=@pmFechaFinal,Importe=@pmImporte,TipoTarif=@pmTipoTarif,TipoRuta=@pmTipoRuta,CdRuta=@pmCdRuta,CdCiuOrigen=@pmCdCiuOrigen,CdCiuDestino=@pmCdCiuDestino,CdTipoVeh=@pmCdTipoVeh,TipoAfilVeh=@pmTipoAfilVeh,Id_Vehiculo=@pmId_Vehiculo,NitCliente=@pmNitCliente,Estado_Mcia=@pmEstado_Mcia,EsDeduccion=@pmEsDeduccion,EsDescuento=@pmEsDescuento,TarifDcto=@pmTarifDcto,FecInicioDcto=@pmFecInicioDcto,FecFinalDcto=@pmFecFinalDcto
,Observacion=@pmObservacion,Inactivo=@pmInactivo,FechaAct=@pmFechaAct,IncOtroPago=@pmIncOtroPago
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraFletes] @pmId INT
 AS 
 SELECT [Id],IdConcepto,FechaInicio,FechaFinal,Importe,TipoTarif,TipoRuta,CdRuta,CdCiuOrigen,CdCiuDestino,CdTipoVeh,TipoAfilVeh,Id_Vehiculo,NitCliente,Estado_Mcia,IncOtroPago,EsDeduccion,EsDescuento,TarifDcto,FecInicioDcto,FecFinalDcto,Observacion,Inactivo,FechaCrea,FechaAct,IdUsuario
 FROM Trn_TraFletes
 WHERE [Id]=@pmId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraFletesLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT T.Id,T.IdConcepto,C.Concepto,T.FechaInicio,T.FechaFinal,T.Importe,T.TipoTarif,T.TipoRuta,T.CdRuta,R.Ruta,R.Distkmt
,T.CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOri,DPO.Departamento AS DptoOrigen
,T.CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,T.CdTipoVeh,TipoVehiculo,T.TipoAfilVeh,T.Id_Vehiculo,T.NitCliente,NC.RazonSocial AS NomCliente,T.Estado_Mcia,T.IncOtroPago,T.EsDeduccion,T.EsDescuento,T.TarifDcto,T.FecInicioDcto,T.FecFinalDcto
,T.Observacion,T.Inactivo,T.FechaCrea,T.FechaAct,T.IdUsuario,Usuario
 FROM Trn_TraFletes AS T INNER JOIN TraConcPago AS C ON T.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 LEFT JOIN Rutas AS R ON T.CdRuta=R.IdRuta
 LEFT JOIN Localidades AS LO ON T.CdCiuOrigen=LO.IdLocal LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS LD ON T.CdCiuDestino=LD.IdLocal LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN Terceros AS NC ON T.NitCliente=NC.IdTercero LEFT JOIN TiposVeh AS TV ON T.CdTipoVeh=TV.IdTipoVeh
 WHERE T.FechaInicio BETWEEN @pmFechaIni AND @pmFechaFin
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraConcPago] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmInactivo BIT
 AS 
 UPDATE TraConcPago SET Concepto=@pmConcepto,Inactivo=@pmInactivo
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraConcPago] @pmIdConcepto VARCHAR(4)
 AS 
 IF @pmIdConcepto IS NULL
  BEGIN
   SELECT IdConcepto,Concepto
   FROM TraConcPago
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdConcepto,Concepto,Inactivo
   FROM TraConcPago
   WHERE IdConcepto=@pmIdConcepto
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTraConcPago] @pmIdConcepto VARCHAR(4)
 AS 
 DELETE FROM TraConcPago WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraConcPago] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmInactivo BIT
 AS 
 INSERT INTO TraConcPago (IdConcepto,Concepto,Inactivo)
 VALUES (@pmIdConcepto,@pmConcepto,@pmInactivo)
GO
--ene 22


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGatProrratVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsGatProrratVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAportesRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAportesRes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAportesRpt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAportesRpt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAportesRtl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAportesRtl]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatProrrateosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatProrrateosFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatProrrateosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatProrrateosLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsGatProrratVeh] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdAsociado VARCHAR(16),@pmUndVehiculo CHAR(1),@pmIdVehiculo VARCHAR(10),@pmTarifaLiq DECIMAL(16,6)
,@pmVrAportes MONEY,@pmImporte MONEY,@pmVrRetiros MONEY,@pmVrAvaluo MONEY,@pmTipoBaseLiq CHAR(1),@pmCodTarifa VARCHAR(4),@pmObservacion VARCHAR(500),@pmFecLiquida SMALLDATETIME,@pmFecIngreso SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmEdoVincula INT
,@pmNum_CasoSin INT,@pmCia_CasoSin CHAR(2),@pmVrAprobActa MONEY,@pmEstado_Reg INT
 AS 
 INSERT INTO Trn_GatProrratVeh (TipDoc,NumProceso,IdCia,Item,IdAsociado,UndVehiculo,IdVehiculo,TarifaLiq,VrAportes,Importe,VrRetiros,VrAvaluo,TipoBaseLiq,CodTarifa,Observacion,FecLiquida,FecIngreso,FecRetiro,EdoVincula,Num_CasoSin,Cia_CasoSin,VrAprobActa,Estado_Reg)
 VALUES (@pmTipDoc,@pmNumProceso,@pmIdCia,@pmItem,@pmIdAsociado,@pmUndVehiculo,@pmIdVehiculo,@pmTarifaLiq,@pmVrAportes,@pmImporte,@pmVrRetiros,@pmVrAvaluo,@pmTipoBaseLiq,@pmCodTarifa,@pmObservacion,@pmFecLiquida,@pmFecIngreso,@pmFecRetiro,@pmEdoVincula,@pmNum_CasoSin,@pmCia_CasoSin,@pmVrAprobActa,@pmEstado_Reg)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAportesRpt] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT T.RazonSocial AS NomAsociado,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.nRemolque,A.FecEmision AS Fecha,A.TipDoc,TD.TipoDoc,A.NumRegistro,A.IdCia,CI.Compania,A.Item,A.Concepto,A.Importe,A.VrDevolucion,A.Descripcion,A.FecVence
,A.TipoBaseLiq,A.VrAvaluo,A.VrComercial,A.VrAsegurado,A.TarifaApo,A.Cantidad,A.CantDiasAnt,A.VrDiasAnt,A.Referencia,A.TipDocRef,A.NumDocRef,A.TipCom,A.Comprobante,A.FecLiqApo,A.FechaCrea
,V.NumVeh,V.IdTipoVeh,TV.TipoVehiculo,V.IdMarca,Marca,V.Modelo,V.IdCrceria,TipoCar,V.TipoAfil,V.FecIngreso,V.FecRetiro,V.EdoVincula,V.Inactivo
,T.TipoId,T.Dv,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercNomCiudad,L.IdDep,DP.Departamento,T.Telefono,T.TelMovil,T.e_mail,T.IdLugarCed,LC.Localidad AS LugarExpCed,T.FecExpCed,T.Asociado AS EsAsociado,T.Inactivo AS TercInactivo
,T.CdGrupoTerc,GC.GrupoClie
FROM Trn_GatAportes AS A INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH
UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
LEFT JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
WHERE A.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatProrrateosLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT P.TipDoc,P.NumProceso,P.IdCia,Compania,P.Fecha,P.IdConcepto,CN.Concepto,P.Modalidad,P.TipDocActa,P.NumDocActa,P.IdCiaActa,P.FecSuceso,P.IdAsociado,T.RazonSocial AS NomAsociado,P.UndVehiculo,P.IdVehiculo
,P.nRemolque,P.VrAprobado,P.VrAvaluo,P.TipoAfiVeh,P.Referencia,P.TipCom,TipoCom,P.Comprobante,P.IdCiaCom,P.Anulado,P.NumDev,P.FechaDev,P.Observacion,P.IdUsuario,Usuario,P.FechaCrea,P.IdCiaCrea,P.ModuloCrea,TD.TipoDoc
,D.Item,D.IdAsociado AS DetNitAsociado,NA.RazonSocial AS DetNomAsociado,D.UndVehiculo AS DetUndVehic,D.IdVehiculo AS DetIdVehiculo,D.VrAvaluo AS DetVrAvaluo,D.TipoBaseLiq,D.TarifaLiq,D.VrAportes,D.Importe,D.VrRetiros,D.CodTarifa
,D.Observacion AS DetObservacion,D.FecLiquida,D.FecIngreso AS DetFecIngreso,D.FecRetiro AS DetFecRetiro,D.EdoVincula AS DetEdoVincula,D.Num_CasoSin,D.Cia_CasoSin,D.VrAprobActa,D.Estado_Reg
,A.NumActa,A.FechaActa AS ActFecha,A.ValorTotal AS ActVrAprobado,A.Observacion AS ActObservacion
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatProrrateos AS P INNER JOIN Trn_GatProrratVeh AS D ON P.TipDoc=D.TipDoc AND P.NumProceso=D.NumProceso AND P.IdCia=D.IdCia
INNER JOIN Trn_JurActas AS A ON P.TipDocActa=A.TipDoc AND P.NumDocActa=A.NumProceso AND P.IdCiaActa=A.IdCia
INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
INNER JOIN Conceptos AS CN ON P.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON P.IdAsociado=T.IdTercero
INNER JOIN Terceros AS NA ON D.IdAsociado=NA.IdTercero
LEFT JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo LEFT JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
LEFT JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON P.TipCom=TCO.IdCom
WHERE P.TipDoc=@pmTipDoc AND P.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
 AND (@pmIdCia IS NULL OR P.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatProrrateosFmt] @pmTipDoc VARCHAR(3),@pmNumProcesoIni INT,@pmNumProcesoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.TipDoc,P.NumProceso,P.IdCia,Compania,P.Fecha,P.IdConcepto,CN.Concepto,P.Modalidad,P.TipDocActa,P.NumDocActa,P.IdCiaActa,P.FecSuceso,P.IdAsociado,T.RazonSocial AS NomAsociado,P.UndVehiculo,P.IdVehiculo
,P.nRemolque,P.VrAprobado,P.VrAvaluo,P.TipoAfiVeh,P.Referencia,P.TipCom,TipoCom,P.Comprobante,P.IdCiaCom,P.Anulado,P.NumDev,P.FechaDev,P.Observacion,P.IdUsuario,Usuario,P.FechaCrea,P.IdCiaCrea,P.ModuloCrea,TD.TipoDoc
,D.Item,D.IdAsociado AS DetNitAsociado,NA.RazonSocial AS DetNomAsociado,D.UndVehiculo AS DetUndVehic,D.IdVehiculo AS DetIdVehiculo,D.VrAvaluo AS DetVrAvaluo,D.TipoBaseLiq,D.TarifaLiq,D.VrAportes,D.Importe,D.VrRetiros,D.CodTarifa
,D.Observacion AS DetObservacion,D.FecLiquida,D.FecIngreso AS DetFecIngreso,D.FecRetiro AS DetFecRetiro,D.EdoVincula AS DetEdoVincula,D.Num_CasoSin,D.Cia_CasoSin,D.VrAprobActa,D.Estado_Reg
,A.NumActa,A.FechaActa AS ActFecha,A.ValorTotal AS ActVrAprobado,A.Observacion AS ActObservacion
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatProrrateos AS P INNER JOIN Trn_GatProrratVeh AS D ON P.TipDoc=D.TipDoc AND P.NumProceso=D.NumProceso AND P.IdCia=D.IdCia
INNER JOIN Trn_JurActas AS A ON P.TipDocActa=A.TipDoc AND P.NumDocActa=A.NumProceso AND P.IdCiaActa=A.IdCia
INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
INNER JOIN Conceptos AS CN ON P.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON P.IdAsociado=T.IdTercero
INNER JOIN Terceros AS NA ON D.IdAsociado=NA.IdTercero
LEFT JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo LEFT JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
LEFT JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON P.TipCom=TCO.IdCom
WHERE P.TipDoc=@pmTipDoc AND P.NumProceso BETWEEN @pmNumProcesoIni AND @pmNumProcesoFin AND P.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAportesRtl] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT T.RazonSocial AS NomAsociado,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),A.IdCia,A.IdAsociado,A.IdVehiculo),0) AS SaldoAnterior,SA.VrAportes,SA.VrVincula
,ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0) AS TotalAportes,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0) AS TotalRetAportes,SA.VrRetGarantia
,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),A.IdCia,A.IdAsociado,A.IdVehiculo),0)+(ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0)-ISNULL(SA.VrGarantia,0))-(ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0)-ISNULL(SA.VrRetGarantia,0)) AS SaldoAportes,SUM(A.Importe) AS Total
,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),Null,A.IdAsociado,A.IdVehiculo),0) AS SaldoAntVeh
FROM Trn_GatAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH
UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
LEFT JOIN (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE (CASE WHEN (Concepto='TRASLADO' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) END) AS VrAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrGarantia
,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE (CASE WHEN (Concepto='TRASLADO' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) END) AS VrRetAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetGarantia 
FROM Trn_GatAportes WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia) AS SA ON A.IdAsociado=SA.IdAsociado AND A.UndVehiculo=SA.UndVehiculo AND A.IdVehiculo=SA.IdVehiculo AND A.IdCia=SA.IdCia
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
WHERE A.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,SA.VrAportes,SA.VrVincula,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,SA.VrRetGarantia,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
--saldos no movimiento
UNION ALL 
SELECT T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0,0,0,0,0,0,0,0,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0
,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
,0
FROM AcuAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH
UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
WHERE A.nAnno=YEAR(@pmFechaIni) AND A.nMes=MONTH(@pmFechaIni) AND A.SaldoAnt<>0 AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND NOT EXISTS (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,SUM(Importe) AS Total FROM Trn_GatAportes WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND Trn_GatAportes.IdCia=A.IdCia AND Trn_GatAportes.IdAsociado=A.IdAsociado AND Trn_GatAportes.UndVehiculo=A.UndVehiculo AND Trn_GatAportes.IdVehiculo=A.IdVehiculo AND YEAR(Trn_GatAportes.FecEmision)=A.nAnno AND MONTH(Trn_GatAportes.FecEmision)=A.nMes
GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia)
GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAportesRes] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT T.RazonSocial AS NomAsociado,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,YEAR(A.FecEmision) AS YearNum,MONTH(A.FecEmision) AS MonthNum
,ISNULL(dbo.FuncAcuAportesSan(YEAR(A.FecEmision),MONTH(A.FecEmision),A.IdCia,A.IdAsociado,A.IdVehiculo),0) AS SaldoAnterior,SA.VrAportes,SA.VrVincula,ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0) AS TotalAportes,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0) AS TotalRetAportes,SA.VrRetGarantia
,ISNULL(dbo.FuncAcuAportesSan(YEAR(A.FecEmision),MONTH(A.FecEmision),A.IdCia,A.IdAsociado,A.IdVehiculo),0)+(ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0)-ISNULL(SA.VrGarantia,0))-(ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0)-ISNULL(SA.VrRetGarantia,0)) AS SaldoAportes,SUM(A.Importe) AS Total
,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),Null,A.IdAsociado,A.IdVehiculo),0) AS SaldoAntVeh
 FROM Trn_GatAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
 INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH 
 UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
 LEFT JOIN (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision) AS YearNum,MONTH(FecEmision) AS MonthNum,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE (CASE WHEN (Concepto='TRASLADO' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) END) AS VrAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrGarantia
,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE (CASE WHEN (Concepto='TRASLADO' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) END) AS VrRetAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetGarantia
 FROM Trn_GatAportes WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision),MONTH(FecEmision)) AS SA ON A.IdAsociado=SA.IdAsociado AND A.UndVehiculo=SA.UndVehiculo AND A.IdVehiculo=SA.IdVehiculo AND A.IdCia=SA.IdCia AND YEAR(A.FecEmision)=SA.YearNum AND MONTH(A.FecEmision)=SA.MonthNum 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
 WHERE A.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
 GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,YEAR(A.FecEmision),MONTH(A.FecEmision),SA.VrAportes,SA.VrVincula,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,SA.VrRetGarantia,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
 --Saldos anteriores  sin movimientos
 UNION ALL SELECT T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,A.nAnno,A.nMes,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0,0,0,0,0,0,0,0,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0
 ,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie,0
 FROM AcuAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
 INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH 
 UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
 WHERE A.nAnno=YEAR(@pmFechaIni) AND A.nMes=MONTH(@pmFechaIni) AND A.SaldoAnt<>0 AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
 AND NOT EXISTS (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision) AS YearNum,MONTH(FecEmision) AS MonthNum,SUM(Importe) AS Total FROM Trn_GatAportes
 WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND Trn_GatAportes.IdCia=A.IdCia AND Trn_GatAportes.IdAsociado=A.IdAsociado AND Trn_GatAportes.UndVehiculo=A.UndVehiculo AND Trn_GatAportes.IdVehiculo=A.IdVehiculo AND YEAR(Trn_GatAportes.FecEmision)=A.nAnno AND MONTH(Trn_GatAportes.FecEmision)=A.nMes 
 GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision),MONTH(FecEmision))
 GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,A.nAnno,A.nMes,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
GO
--ene 29
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevAdicionalLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevAdicionalLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevAjuProrLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevAjuProrLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevAjustesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevAjustesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevLiquidaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevLiquidaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevProrrFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevProrrFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevRetirosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevRetirosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevTrasladoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevTrasladoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevVincLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevVincLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevVincLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.TipDoc,D.NumDocVinc,D.IdCiaDoc,D.FecDoc,V.FechaIngreso,D.IdAsociado,T.RazonSocial AS NomAsociado,D.UndVehiculo,D.IdVehiculo,D.nRemolque
,D.VrIngresos,D.VrTraslados,V.TarifaVinc,V.VrVinculacion,V.VrAvaluo,V.VrComercial,V.VrAsegurado,V.TipoBaseLiq,V.TipoAfiVeh,V.Referencia,V.CdTarifVinc,V.FecUltLiqApo
,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
--detalle aportes
,DA.Item,DA.FecVence,DA.Concepto AS ApoConcepto,DA.Importe,DA.VrDevolucion AS VrDeduccion,DA.Cantidad,DA.TarifaApo,DA.Descripcion AS ApoDescripcion,DA.Referencia AS ApoReferencia
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatDevVinc AS D INNER JOIN Trn_GatVincula AS V ON D.TipDoc=V.TipDoc AND D.NumDocVinc=V.NumRegistro AND D.IdCiaDoc=V.IdCia
INNER JOIN Terceros AS T ON D.IdAsociado=T.IdTercero INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
INNER JOIN Trn_GatAportes AS DA ON D.TipDev=DA.TipDoc AND D.Devolucion=DA.NumRegistro AND D.IdCia=DA.IdCia
INNER JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevRetirosLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.TipDoc,D.NumRetiro,D.IdCiaDoc,D.FecDoc,R.FechaRetiro,D.TipoRetiro,D.Modalidad,D.IdAsociado,T.RazonSocial AS NomAsociado,D.UndVehiculo,D.IdVehiculo,D.nRemolque
,D.VrDevolucion,R.VrRetiro,R.VrAportes,R.VrVinculacion,R.VrSaldoVinc,R.VrGarantia,R.VrSaldoApo,R.VrTotalApo,R.VrAvaluo,R.TipoBaseLiq,R.TipoAfiVeh,R.Referencia,R.FechaCorte,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev
,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
,DA.Item,DA.UndVehiculo AS DetUndVehic,DA.IdVehiculo AS DetVehiculo,DA.VrRetiro AS DetVrRetio,DA.VrAportes AS DetVrAportes,DA.VrVinculacion AS DetVrVincula,DA.VrSaldoVinc AS DetVrSaldoVinc
,DA.VrGarantia AS DetVrGarantia,DA.VrSaldoApo AS DetVrSaldoApo,DA.VrTotalApo AS DetVrTotalApo,DA.VrAvaluo AS DetVrAvaluo,DA.TipoBaseLiq AS DetTipoBaseLiq
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
 FROM Trn_GatDevRetiros AS D INNER JOIN Trn_GatRetiros AS R ON D.TipDoc=R.TipDoc AND D.NumRetiro=R.NumRegistro AND D.IdCiaDoc=R.IdCia
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON D.IdAsociado=T.IdTercero
INNER JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
LEFT JOIN Trn_GatRetirosDet AS DA ON D.TipDoc=DA.TipDoc AND D.NumRetiro=DA.NumRegistro AND D.IdCiaDoc=DA.IdCia
WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevTrasladoFmt] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.Modalidad,D.TipDoc,D.NumTraslado,D.IdCiaDoc,D.FecDoc
,D.IdAsociado,NA.RazonSocial AS NomAsociado,D.UndVehiculo,D.IdVehiculo,D.nRemolque,D.IdAsociadoDtn,ND.RazonSocial AS NomAsociadoDtn,D.UndVehicDtn,D.IdVehiculoDtn,D.VrDevolucion,T.VrTraslado,T.VrAportes,T.VrVinculacion,T.VrSaldoVinc,T.VrGarantia
,T.VrSaldoApo,T.VrTotalApo,T.VrAvaluo,T.TipoBaseLiq,T.TipoAfiVeh,T.Referencia,T.FechaCorte,T.VrAbonos,T.TipDocRef,T.NumDocRef,T.CiaDocRef,T.TarifaGar,T.UndTarifGar
,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
,DA.Item,DA.FecEmision,DA.FecVence,DA.Concepto AS ApoConcepto,DA.IdAsociado AS ApoIdAsociado,A.RazonSocial AS ApoNomAsociado,DA.UndVehiculo AS ApoUndVehic,DA.IdVehiculo AS ApoIdVehiculo,DA.nRemolque AS ApoRemolque,DA.Importe,DA.VrComercial,DA.VrAsegurado,DA.Cantidad,DA.TarifaApo,DA.CodTarifApo
,DA.CantDiasAnt,DA.VrDiasAnt,DA.VrDevolucion AS VrDeduccion,DA.Descripcion AS ApoDescripcion,DA.Referencia AS ApoReferencia,DA.TipDocRef AS ApoDocRef,DA.NumDocRef AS ApoNumDocRef,DA.FecLiqApo
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatDevTraslado AS D INNER JOIN Trn_GatTraslados AS T ON D.TipDoc=T.TipDoc AND D.NumTraslado=T.NumTraslado AND D.IdCiaDoc=T.IdCia
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS NA ON D.IdAsociado=NA.IdTercero
INNER JOIN Trn_GatAportes AS DA ON D.TipDev=DA.TipDoc AND D.Devolucion=DA.NumRegistro AND D.IdCia=DA.IdCia
INNER JOIN Terceros AS A ON DA.IdAsociado=A.IdTercero
INNER JOIN QryVehiculosGat AS QV ON DA.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom LEFT JOIN Terceros AS ND ON D.IdAsociadoDtn=ND.IdTercero
WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevAdicionalLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.TipDoc,D.NumRegistro,D.IdCiaDoc,D.FecDoc,D.IdAsociado,T.RazonSocial AS NomAsociado
,D.UndVehiculo,D.IdVehiculo,D.nRemolque,D.VrDevolucion,A.VrSaldo,A.VrAvaluo,A.TipoBaseLiq,A.TipoAfiVeh,A.Referencia,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
--recibos
,DR.Item,DR.TipRec,DR.Recibo,DR.IdCiaRec,DR.ItemRec,DR.VrAbono,DR.Descripcion,R.Fecha AS FecRecibo,R.TipoConc,R.Descripcion AS RecDescripcion,R.VrIngreso,R.VrDevolucion AS RecVrDev,R.VrAbonos AS TotalAbonos,R.TipDocRef,R.NumDocRef,R.CiaDocRef,R.CodCuenta,R.ItemDet,R.EstadoReg
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatDevAdicional AS D INNER JOIN Trn_GatAdicionales AS A ON D.TipDoc=A.TipDoc AND D.NumRegistro=A.NumRegistro AND D.IdCiaDoc=A.IdCia
INNER JOIN Trn_GatAdicionalRec AS DR ON D.TipDoc=DR.TipDoc AND D.NumRegistro=DR.NumRegistro AND D.IdCiaDoc=DR.IdCia
INNER JOIN Terceros AS T ON D.IdAsociado=T.IdTercero INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
INNER JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
LEFT JOIN Trn_GatIngresos AS R ON DR.TipRec=R.TipDoc AND DR.Recibo=R.Recibo AND DR.IdCiaRec=R.IdCia AND DR.ItemRec=R.Item
WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevAjuProrLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.Modalidad,D.TipDoc,D.NumAjuste,D.IdCiaDoc,D.FecDoc,D.VrDevolucion
,A.TipPror,A.NumProceso,A.IdCiaPror,A.VrAjustes,A.Referencia,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
,DA.Item,DA.FecVence,DA.IdAsociado,T.RazonSocial AS NomAsociado,DA.UndVehiculo,DA.IdVehiculo,DA.nRemolque,DA.Concepto AS ApoConcepto,DA.Importe,DA.VrAvaluo,DA.VrComercial,DA.VrAsegurado,DA.TipoBaseLiq,DA.VrDevolucion AS VrDeduccion,DA.Cantidad
,DA.TarifaApo,DA.CodTarifApo,DA.CantDiasAnt,DA.VrDiasAnt,DA.Descripcion AS ApoDescripcion,DA.Referencia AS ApoReferencia,DA.TipDocRef AS ApoTipRef,DA.NumDocRef AS ApoNumRef,DA.TipoAfiVeh,DA.FecLiqApo
,P.Modalidad AS ProrModalidad,P.TipDocActa,P.NumDocActa,P.IdCiaActa,AJ.NumActa,AJ.FechaActa,P.FecSuceso,P.VrAprobado,P.Observacion AS ProrObservacion,AJ.Observacion AS ActaObservacion
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
 FROM Trn_GatDevAjuPror AS D INNER JOIN Trn_GatAjustesPror AS A ON D.TipDoc=A.TipDoc AND D.NumAjuste=A.NumAjuste AND D.IdCiaDoc=A.IdCia
 INNER JOIN Trn_GatAportes AS DA ON D.TipDev=DA.TipDoc AND D.Devolucion=DA.NumRegistro AND D.IdCia=DA.IdCia
 INNER JOIN Trn_GatProrrateos AS P ON A.TipPror=P.TipDoc AND A.NumProceso=P.NumProceso AND A.IdCiaPror=P.IdCia
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia 
 INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN Terceros AS T ON DA.IdAsociado=T.IdTercero
 INNER JOIN QryVehiculosGat AS QV ON DA.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
 INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
 LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
 LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
 LEFT JOIN Trn_JurActas AS AJ ON P.TipDocActa=AJ.TipDoc AND P.NumDocActa=AJ.NumProceso AND P.IdCiaActa=AJ.IdCia
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
 AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevAjustesLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
AS 
SELECT D.TipDev,D.Devolucion,D.IdCia,D.Fecha,D.IdConcepto,CN.Concepto,D.TipDoc,D.NumAjuste,D.IdCiaDoc,D.FecDoc,D.Modalidad,D.VrDevolucion,A.VrAjustes,A.FechaVence,A.Referencia,A.TipDocRef,A.NumDocRef,A.CiaDocRef,A.TipCau,A.Causacion,A.IdCiaCau
,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
,DA.Item,DA.FecVence,DA.IdAsociado,T.RazonSocial AS NomAsociado,DA.UndVehiculo,DA.IdVehiculo,DA.nRemolque,DA.Concepto AS ApoConcepto,DA.Importe,DA.VrAvaluo,DA.VrComercial,DA.VrAsegurado,DA.TipoBaseLiq,DA.VrDevolucion AS VrDeduccion,DA.Cantidad
,DA.TarifaApo,DA.CodTarifApo,DA.CantDiasAnt,DA.VrDiasAnt,DA.Descripcion AS ApoDescripcion,DA.Referencia AS ApoReferencia,DA.TipDocRef AS ApoTipRef,DA.NumDocRef AS ApoNumRef,DA.TipoAfiVeh,DA.FecLiqApo
,L.FechaLiquida,L.VrAportes AS TotalLiquida,L.Anulado AS LiqAnulado
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
 FROM Trn_GatDevAjustes AS D INNER JOIN Trn_GatAjustes AS A ON D.TipDoc=A.TipDoc AND D.NumAjuste=A.NumAjuste AND D.IdCiaDoc=A.IdCia
 INNER JOIN Trn_GatAportes AS DA ON D.TipDev=DA.TipDoc AND D.Devolucion=DA.NumRegistro AND D.IdCia=DA.IdCia
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia 
 INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN Terceros AS T ON DA.IdAsociado=T.IdTercero
 INNER JOIN QryVehiculosGat AS QV ON DA.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
 INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
 LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
 LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
 LEFT JOIN Trn_GatLiquida AS L ON A.TipDocRef=L.TipDoc AND A.NumDocRef=L.NumLiquida AND A.CiaDocRef=L.IdCia
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
 AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevLiquidaLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.TipDoc,D.NumLiquida,D.IdCiaDoc,D.FecDoc,L.FechaLiquida,L.FechaVence,D.Modalidad,L.Periodos,L.TarifaLiq,L.VrAportes,D.VrDevolucion,D.VrBaseLiq
,L.Referencia,L.TipCau,L.Causacion,L.IdCiaCau,L.CdTarifLiq,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
,DA.Item,DA.FecVence,DA.IdAsociado,T.RazonSocial AS NomAsociado,DA.UndVehiculo,DA.IdVehiculo,DA.nRemolque,DA.Concepto AS ApoConcepto,DA.Importe,DA.VrAvaluo,DA.VrComercial,DA.VrAsegurado,DA.TipoBaseLiq,DA.VrDevolucion AS VrDeduccion,DA.Cantidad
,DA.TarifaApo,DA.CodTarifApo,DA.CantDiasAnt,DA.VrDiasAnt,DA.Descripcion AS ApoDescripcion,DA.Referencia AS ApoReferencia,DA.TipDocRef,DA.NumDocRef,DA.TipoAfiVeh,DA.FecLiqApo
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
 FROM Trn_GatDevLiquida AS D INNER JOIN Trn_GatLiquida AS L ON D.TipDoc=L.TipDoc AND D.NumLiquida=L.NumLiquida AND D.IdCiaDoc=L.IdCia
 INNER JOIN Trn_GatAportes AS DA ON D.TipDev=DA.TipDoc AND D.Devolucion=DA.NumRegistro AND D.IdCia=DA.IdCia
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia 
 INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN Terceros AS T ON DA.IdAsociado=T.IdTercero
 INNER JOIN QryVehiculosGat AS QV ON DA.IdVehiculo=QV.IdVehiculo INNER JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
 INNER JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria INNER JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
 LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
 LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
 AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevProrrFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,CN.Concepto,D.TipDoc,D.NumProceso,D.IdCiaDoc,D.FecDoc,D.Modalidad
,D.IdAsociado,T.RazonSocial AS NomAsociado,D.UndVehiculo,D.IdVehiculo,D.nRemolque,D.VrDevolucion,P.VrAprobado,P.VrAvaluo
,P.TipDocActa,P.NumDocActa,P.IdCiaActa,P.FecSuceso,P.TipoAfiVeh,P.Referencia,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom
,D.ModdDev,D.Observacion,D.IdUsuario,Usuario,D.FechaCrea,D.IdCiaCrea,D.ModuloCrea,TD.TipoDoc
,DA.Item,DA.IdAsociado AS DetNitAsociado,NA.RazonSocial AS DetNomAsociado,DA.UndVehiculo AS DetUndVehic,DA.IdVehiculo AS DetIdVehiculo,DA.VrAvaluo AS DetVrAvaluo,DA.TipoBaseLiq,DA.TarifaLiq,DA.VrAportes,DA.Importe,DA.VrRetiros,DA.CodTarifa
,DA.Observacion AS DetObservacion,DA.FecLiquida,DA.FecIngreso AS DetFecIngreso,DA.FecRetiro AS DetFecRetiro,DA.EdoVincula AS DetEdoVincula,DA.Num_CasoSin,DA.Cia_CasoSin,DA.VrAprobActa,DA.Estado_Reg
,A.NumActa,A.FechaActa AS ActFecha,A.ValorTotal AS ActVrAprobado,A.Observacion AS ActObservacion
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatDevProrr AS D INNER JOIN Trn_GatProrrateos AS P ON D.TipDoc=P.TipDoc AND D.NumProceso=P.NumProceso AND D.IdCiaDoc=P.IdCia
INNER JOIN Trn_GatProrratVeh AS DA ON D.TipDoc=DA.TipDoc AND D.NumProceso=DA.NumProceso AND D.IdCiaDoc=DA.IdCia
INNER JOIN Trn_JurActas AS A ON P.TipDocActa=A.TipDoc AND P.NumDocActa=A.NumProceso AND P.IdCiaActa=A.IdCia
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON D.IdAsociado=T.IdTercero
INNER JOIN Terceros AS NA ON DA.IdAsociado=NA.IdTercero
LEFT JOIN QryVehiculosGat AS QV ON DA.IdVehiculo=QV.IdVehiculo LEFT JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
LEFT JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON D.TipCom=TCO.IdCom
WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
GO