--TAXSUR YA SE ACTUALIZO 4.0.14
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTercCndReferencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelTercCndReferencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCndReferencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCndReferencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCndtores]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCndtores]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCndtores_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCndtores_Sel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsVehRemolq]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCndReferencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCndReferencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCndtores]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCndtores]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCndtoresLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCndtoresLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVehRemolq]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehRemolqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVehRemolqLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCndReferencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTercCndReferencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCndtores]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTercCndtores]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpVehRemolq]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelTercCndReferencias @pmIdConductor VARCHAR(16),@pmItem INT
 AS 
 DELETE FROM TercCndReferencias
 WHERE IdConductor=@pmIdConductor  
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTercCndReferencias @pmIdConductor VARCHAR(16),@pmItem INT,@pmTipoRef VARCHAR(10),@pmEmpresaRef VARCHAR(150),@pmNitRef VARCHAR(16),@pmDireccionRef VARCHAR(150)
,@pmTelefonoRef VARCHAR(30),@pmContactoRef VARCHAR(150),@pmEmailRef VARCHAR(100),@pmCargoRef VARCHAR(50),@pmCuentaBanco VARCHAR(30),@pmFechaInicial SMALLDATETIME
,@pmFechaFinal SMALLDATETIME,@pmVerificado VARCHAR(150),@pmObservRef VARCHAR(250)
 AS 
 INSERT INTO TercCndReferencias (IdConductor,Item,TipoRef,EmpresaRef,NitRef,DireccionRef,TelefonoRef,ContactoRef,EmailRef,CargoRef,CuentaBanco,FechaInicial,FechaFinal,Verificado,ObservRef)
 VALUES (@pmIdConductor,@pmItem,@pmTipoRef,@pmEmpresaRef,@pmNitRef,@pmDireccionRef,@pmTelefonoRef,@pmContactoRef,@pmEmailRef,@pmCargoRef,@pmCuentaBanco,@pmFechaInicial,@pmFechaFinal,@pmVerificado,@pmObservRef)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCndtores @pmIdConductor VARCHAR(16),@pmIdGrupo VARCHAR(4),@pmIdModd VARCHAR(4),@pmTipoSangre CHAR(2),@pmFactorRh CHAR(1),@pmLibMilitar VARCHAR(25),@pmClaseLib INT,@pmFechaLib SMALLDATETIME,@pmDistMil VARCHAR(10)
,@pmCertJudicial VARCHAR(25),@pmFecJudicial SMALLDATETIME,@pmVigJudicial SMALLDATETIME,@pmLicencia VARCHAR(25),@pmIdLugar VARCHAR(8),@pmCatLicencia INT,@pmFecLicencia SMALLDATETIME,@pmVigLicencia SMALLDATETIME
,@pmFecNacmto SMALLDATETIME,@pmLugarNacmto VARCHAR(30),@pmSexo CHAR(1),@pmIdEstCivil VARCHAR(4),@pmNHijos INT,@pmPasporte VARCHAR(25),@pmVigPaspte SMALLDATETIME
,@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmCdLocCue VARCHAR(8),@pmCdFonPen VARCHAR(8),@pmCdFonEps VARCHAR(8),@pmCdFonArp VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmPathFoto VARCHAR(30)
,@pmPathFirma VARCHAR(30),@pmPathLic VARCHAR(30),@pmCmntarios VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNumTarjeta VARCHAR(30),@pmEdicTarjeta INT,@pmFecTarjeta SMALLDATETIME
,@pmVigTarjeta SMALLDATETIME,@pmPasSeguridad VARCHAR(30),@pmFecPasSeg SMALLDATETIME,@pmVigPasSeg SMALLDATETIME,@pmNitEmpTrans VARCHAR(16),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
,@pmNum_Contrato INT,@pmContrato_Activo BIT,@pmCdTipCon VARCHAR(4),@pmFec_Ingreso SMALLDATETIME,@pmFec_Retiro SMALLDATETIME,@pmFec_Vigencia SMALLDATETIME,@pmFec_IngresoAnt SMALLDATETIME,@pmFec_UltimoAcc SMALLDATETIME
,@pmPasadoJudicial VARCHAR(250),@pmRestriccion VARCHAR(50),@pmFecPagoPen SMALLDATETIME,@pmFecPagoEps SMALLDATETIME,@pmFecPagoArp SMALLDATETIME
 AS 
 INSERT INTO TercCndtores (IdConductor,IdGrupo,IdModd,TipoSangre,FactorRh,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps,CdFonArp,pVehiculo
,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,Pasporte,VigPaspte,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,FechaAdd,IdUsuario,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso
 ,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion,FecPagoPen,FecPagoEps,FecPagoArp)
 VALUES (@pmIdConductor,@pmIdGrupo,@pmIdModd,@pmTipoSangre,@pmFactorRh,@pmLibMilitar,@pmClaseLib,@pmFechaLib,@pmDistMil,@pmCertJudicial,@pmFecJudicial,@pmVigJudicial,@pmLicencia,@pmIdLugar,@pmCatLicencia,@pmFecLicencia,@pmVigLicencia,@pmIdClase
,@pmNumCuenta,@pmIdBanco,@pmCdLocCue,@pmCdFonPen,@pmCdFonEps,@pmCdFonArp,@pmpVehiculo,@pmFecNacmto,@pmLugarNacmto,@pmSexo,@pmIdEstCivil,@pmNHijos,@pmPasporte,@pmVigPaspte,@pmPathFoto,@pmPathFirma,@pmPathLic,@pmCmntarios
,@pmIdEstado,@pmInactivo,@pmNumTarjeta,@pmEdicTarjeta,@pmFecTarjeta,@pmVigTarjeta,@pmPasSeguridad,@pmFecPasSeg,@pmVigPasSeg,@pmNitEmpTrans,@pmFechaAdd,@pmIdUsuario,@pmNum_Contrato,@pmContrato_Activo,@pmCdTipCon,@pmFec_Ingreso
 ,@pmFec_Retiro,@pmFec_Vigencia,@pmFec_IngresoAnt,@pmFec_UltimoAcc,@pmPasadoJudicial,@pmRestriccion,@pmFecPagoPen,@pmFecPagoEps,@pmFecPagoArp)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCndtores_Sel @pmIdConductor VARCHAR(16),@pmNewConductor VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdConductor FROM  TercCndtores WHERE IdConductor=@pmIdConductor)
  INSERT INTO TercCndtores (IdConductor,IdGrupo,IdModd,TipoSangre,FactorRh,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps,CdFonArp,pVehiculo
 ,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,Pasporte,VigPaspte,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,FechaAdd,IdUsuario
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion,FecPagoPen,FecPagoEps,FecPagoArp)
  SELECT @pmNewConductor,IdGrupo,IdModd,TipoSangre,FactorRh,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps,CdFonArp,pVehiculo
 ,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,Pasporte,VigPaspte,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,FechaAdd,IdUsuario
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion,FecPagoPen,FecPagoEps,FecPagoArp
  FROM TercCndtores 
  WHERE IdConductor=@pmIdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsVehRemolq @pmIdRemque VARCHAR(10),@pmIdMarca VARCHAR(4),@pmIdColor VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmNEjes INT,@pmnLlantas INT,@pmAltura DECIMAL(14,4)
,@pmAncho DECIMAL(14,4),@pmLargo DECIMAL(14,4),@pmVlumen DECIMAL(14,4),@pmPesoKg DECIMAL(14,4),@pmCapneta DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmNumComp INT,@pmCapcComp VARCHAR(50),@pmNumSerial VARCHAR(30)
,@pmIdPropietario VARCHAR(16),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVidaUtil INT,@pmVrComercial MONEY,@pmVrAvaludo MONEY,@pmVrAsegurado MONEY,@pmKilmetraje INT,@pmEquipoAdic VARCHAR(50)
,@pmDescripcion VARCHAR(100),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTarjetaProp VARCHAR(30),@pmFecTarjProp  SMALLDATETIME
,@pmVigTarjProp SMALLDATETIME,@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO VehRemolq (IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje
,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion,Observacion,IdEstado,Inactivo,FecAdd,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp)
 VALUES (@pmIdRemque,@pmIdMarca,@pmIdColor,@pmIdCrceria,@pmModelo,@pmNEjes,@pmnLlantas,@pmAltura,@pmAncho,@pmLargo,@pmVlumen,@pmPesoKg,@pmCapneta,@pmUndCapc,@pmNumComp,@pmCapcComp,@pmNumSerial,@pmIdPropietario
,@pmNitProv,@pmFecCompra,@pmVidaUtil,@pmVrComercial,@pmVrAvaludo,@pmVrAsegurado,@pmKilmetraje,@pmEquipoAdic,@pmDescripcion,@pmCdCenSer,@pmCdLocal,@pmUbicacion,@pmObservacion,@pmIdEstado,@pmInactivo,@pmFecAdd,@pmIdUsuario,@pmTarjetaProp,@pmFecTarjProp,@pmVigTarjProp)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTercCndReferencias @pmIdConductor VARCHAR(16),@pmItem INT
 AS 
 SELECT Item,TipoRef,EmpresaRef,NitRef,DireccionRef,TelefonoRef,ContactoRef,EmailRef,CargoRef
,CuentaBanco,FechaInicial,FechaFinal,Verificado,ObservRef,IdConductor
 FROM TercCndReferencias
 WHERE IdConductor=@pmIdConductor 
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercCndtores @pmIdConductor VARCHAR(16)
 AS 
 SELECT IdConductor,IdGrupo,IdModd,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial
,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps
,CdFonArp,pVehiculo,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans
,FechaAdd,FechaUpdate,IdUsuario,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion
,FecPagoPen,FecPagoEps,FecPagoArp
 FROM TercCndtores
 WHERE IdConductor=@pmIdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercCndtoresLta @pmIdLocal VARCHAR(8)=Null,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdBanco VARCHAR(4)=Null,@pmIdLugar VARCHAR(8)=Null,@pmCatLicencia INT=Null,@pmIdModd VARCHAR(4)=Null,@pmEsEmpleado BIT=Null
,@pmEsPropietario BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmInactivoTer BIT=Null
 AS 
 SELECT IdConductor,RazonSocial,T.Codigo AS CodCondtor,TipoId,Dv,FecExpCed,IdLugarCed,LC.Localidad AS LugarCedula,T.Direccion AS Dirccion,T.IdLocal AS CodCiudad
,L.Localidad AS Ciudad,L.IdDep AS CodDpto,Departamento,Telefono,Fax,TelMovil,e_mail,T.IdProf AS CdProf,Profesion,C.IdGrupo AS CodGrupo,GrupoClie,C.IdModd AS CdModd,Modalidad,TipoSangre,FactorRh
,FecNacmto,LugarNacmto,Sexo,IdEstCivil,EstCivil,NHijos,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,C.IdLugar AS CodLugarLic,LugarLic,CatLicencia,FecLicencia,VigLicencia,C.IdClase AS CdClaseCta
,Pasporte,VigPaspte,ClaseCuenta,NumCuenta,C.IdBanco AS CodBanco,Banco,CdLocCue,LCT.Localidad AS LugarCuenta,CdFonPen,FP.Fondo AS FondoPen,CdFonEps,FS.Fondo AS Eps,CdFonArp
,FR.Fondo AS FondoRiesgo,pVehiculo,PathFoto,PathFirma,PathLic,Observacion,Cmntarios,EsCliente,EsVendedor,EsPropietario,EsEmpleado,EsOperario,IniStgNom
,C.IdEstado AS CdEstado,EC.Estado AS EstadoCond,C.Inactivo AS Inactiv,T.IdEstado AS IdEstdTerc,ET.Estado AS EstadoTerc,T.Inactivo AS Ter_Inactivo
,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,C.IdUsuario AS IdUsuari,Usuario,EC.NColor AS EstdNColor,C.FechaAdd AS Fec_Add,C.FechaUpdate AS Fec_Upd
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion,FecPagoPen,FecPagoEps,FecPagoArp
 FROM TercCndtores AS C INNER JOIN Terceros AS T ON C.IdConductor=T.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN ExpLicencias AS LL ON C.IdLugar=LL.IdLugar
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase  INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN TiposMod AS M ON C.IdModd=M.IdModd INNER JOIN EstadoCiv AS ECV ON C.IdEstCivil=ECV.IdEstado
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf  INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN  Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 LEFT JOIN  Localidades AS LCT ON  C.CdLocCue=LCT.IdLocal 
 LEFT JOIN Fondos AS FP ON C.CdFonPen=FP.IdFondo LEFT JOIN Fondos AS FS ON C.CdFonEps=FS.IdFondo
 LEFT JOIN Fondos AS FR ON C.CdFonArp=FR.IdFondo
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND T.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')   AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND C.IdLugar LIKE ISNULL(@pmIdLugar,'%') AND C.IdModd LIKE ISNULL(@pmIdModd,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (CatLicencia>=ISNULL(@pmCatLicencia,0)  AND CatLicencia<=ISNULL(@pmCatLicencia,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (EsEmpleado=ISNULL(@pmEsEmpleado,0) or EsEmpleado=ISNULL(@pmEsEmpleado,1))
 AND (EsPropietario=ISNULL(@pmEsPropietario,0) or EsPropietario=ISNULL(@pmEsPropietario,1))
 ORDER BY T.RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVehRemolq @pmIdRemque VARCHAR(10)
 AS 
 SELECT IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp
,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion
,Observacion,IdEstado,Inactivo,FecAdd,FecUpdate,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp
 FROM VehRemolq
 WHERE IdRemque=@pmIdRemque
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVehRemolqLta @pmIdMarca VARCHAR(4)=Null,@pmIdCrceria VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
 AS 
 SELECT IdRemque,R.IdMarca AS CdMarca,Marca,R.IdColor AS CodColor,NomColor,R.IdCrceria AS CodCarr,TipoCar,Modelo,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen
,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NP.RazonSocial AS Propietario,NitProv,NPV.RazonSocial AS Proveedor,FecCompra,VidaUtil
,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje,EquipoAdic,Descripcion,CdCenSer,CentroServ,CdLocal,LU.Localidad AS CiuUbicacion
,LU.IdDep AS CodDpto,Departamento,Ubicacion,R.Observacion AS Observ,R.IdEstado AS CodEstado,Estado,R.Inactivo AS Inactiv,R.IdUsuario AS IdUsuari,Usuario
,FecAdd,FecUpdate,EV.NColor AS NumColor,OutDemand,TarjetaProp,FecTarjProp,VigTarjProp
 FROM VehRemolq AS R INNER JOIN Marcas AS M ON R.IdMarca=M.IdMarca  INNER JOIN TiposCol AS CL ON R.IdColor=CL.IdColor
 INNER JOIN TiposCar AS TC ON R.IdCrceria=TC.IdCrceria  INNER JOIN Terceros AS NP ON  R.IdPropietario=NP.IdTercero
 INNER JOIN EstadoVeh AS EV ON R.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS NPV ON R.NitProv=NPV.IdTercero
 LEFT JOIN CentrosServ AS CS ON R.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON R.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 WHERE R.IdMarca LIKE ISNULL(@pmIdMarca,'%')  AND R.IdCrceria LIKE ISNULL(@pmIdCrceria,'%')
 AND Modelo LIKE ISNULL(@pmModelo,'%')  AND IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (R.Inactivo=ISNULL(@pmInactivo,0) or R.Inactivo=ISNULL(@pmInactivo,1))
 AND (FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdRemque
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTercCndReferencias @pmIdConductor VARCHAR(16),@pmItem INT,@pmTipoRef VARCHAR(10),@pmEmpresaRef VARCHAR(150),@pmNitRef VARCHAR(16),@pmDireccionRef VARCHAR(150),@pmTelefonoRef VARCHAR(30),@pmContactoRef VARCHAR(150)
,@pmEmailRef VARCHAR(100),@pmCargoRef VARCHAR(50),@pmCuentaBanco VARCHAR(30),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmVerificado VARCHAR(150),@pmObservRef VARCHAR(250)
 AS 
 UPDATE TercCndReferencias SET TipoRef=@pmTipoRef,EmpresaRef=@pmEmpresaRef,NitRef=@pmNitRef,DireccionRef=@pmDireccionRef,TelefonoRef=@pmTelefonoRef,ContactoRef=@pmContactoRef,EmailRef=@pmEmailRef,CargoRef=@pmCargoRef,CuentaBanco=@pmCuentaBanco
,FechaInicial=@pmFechaInicial,FechaFinal=@pmFechaFinal,Verificado=@pmVerificado,ObservRef=@pmObservRef
 WHERE IdConductor=@pmIdConductor AND Item=@pmItem

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTercCndtores @pmIdConductor VARCHAR(16),@pmIdGrupo VARCHAR(4),@pmIdModd VARCHAR(4),@pmTipoSangre CHAR(2),@pmFactorRh CHAR(1),@pmLibMilitar VARCHAR(25),@pmClaseLib INT,@pmFechaLib SMALLDATETIME,@pmDistMil VARCHAR(10),@pmCertJudicial VARCHAR(25)
,@pmFecJudicial SMALLDATETIME,@pmVigJudicial SMALLDATETIME,@pmLicencia VARCHAR(25),@pmIdLugar VARCHAR(8),@pmCatLicencia INT,@pmFecLicencia SMALLDATETIME,@pmVigLicencia SMALLDATETIME
,@pmFecNacmto SMALLDATETIME,@pmLugarNacmto VARCHAR(30),@pmSexo CHAR(1),@pmIdEstCivil VARCHAR(4),@pmNHijos INT,@pmPasporte VARCHAR(25),@pmVigPaspte SMALLDATETIME,@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4)
,@pmCdLocCue VARCHAR(8),@pmCdFonPen VARCHAR(8),@pmCdFonEps VARCHAR(8),@pmCdFonArp VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmPathLic VARCHAR(30),@pmCmntarios VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNumTarjeta VARCHAR(30),@pmEdicTarjeta INT,@pmFecTarjeta SMALLDATETIME,@pmVigTarjeta SMALLDATETIME,@pmPasSeguridad VARCHAR(30),@pmFecPasSeg SMALLDATETIME,@pmVigPasSeg SMALLDATETIME,@pmNitEmpTrans VARCHAR(16),@pmFechaUpdate SMALLDATETIME
,@pmNum_Contrato INT,@pmContrato_Activo BIT,@pmCdTipCon VARCHAR(4),@pmFec_Ingreso SMALLDATETIME,@pmFec_Retiro SMALLDATETIME,@pmFec_Vigencia SMALLDATETIME,@pmFec_IngresoAnt SMALLDATETIME,@pmFec_UltimoAcc SMALLDATETIME
,@pmPasadoJudicial VARCHAR(250),@pmRestriccion VARCHAR(50),@pmFecPagoPen SMALLDATETIME,@pmFecPagoEps SMALLDATETIME,@pmFecPagoArp SMALLDATETIME
 AS 
 UPDATE TercCndtores SET IdGrupo=@pmIdGrupo,IdModd=@pmIdModd,TipoSangre=@pmTipoSangre,FactorRh=@pmFactorRh,LibMilitar=@pmLibMilitar,ClaseLib=@pmClaseLib,FechaLib=@pmFechaLib,DistMil=@pmDistMil,CertJudicial=@pmCertJudicial,FecJudicial=@pmFecJudicial,VigJudicial=@pmVigJudicial
,Licencia=@pmLicencia,IdLugar=@pmIdLugar,CatLicencia=@pmCatLicencia,FecLicencia=@pmFecLicencia,VigLicencia=@pmVigLicencia,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco,CdLocCue=@pmCdLocCue,CdFonPen=@pmCdFonPen,CdFonEps=@pmCdFonEps
,CdFonArp=@pmCdFonArp,FecNacmto=@pmFecNacmto,LugarNacmto=@pmLugarNacmto,Sexo=@pmSexo,IdEstCivil=@pmIdEstCivil,NHijos=@pmNHijos,Pasporte=@pmPasporte,VigPaspte=@pmVigPaspte,pVehiculo=@pmpVehiculo,PathFoto=@pmPathFoto,PathFirma=@pmPathFirma
,PathLic=@pmPathLic,Cmntarios=@pmCmntarios,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,NumTarjeta=@pmNumTarjeta,EdicTarjeta=@pmEdicTarjeta,FecTarjeta=@pmFecTarjeta,VigTarjeta=@pmVigTarjeta,PasSeguridad=@pmPasSeguridad,FecPasSeg=@pmFecPasSeg,VigPasSeg=@pmVigPasSeg
,NitEmpTrans=@pmNitEmpTrans,FechaUpdate=@pmFechaUpdate,Num_Contrato=@pmNum_Contrato,Contrato_Activo=@pmContrato_Activo,CdTipCon=@pmCdTipCon,Fec_Ingreso=@pmFec_Ingreso,Fec_Retiro=@pmFec_Retiro,Fec_Vigencia=@pmFec_Vigencia,Fec_IngresoAnt=@pmFec_IngresoAnt
,Fec_UltimoAcc=@pmFec_UltimoAcc,PasadoJudicial=@pmPasadoJudicial,Restriccion=@pmRestriccion,FecPagoPen=@pmFecPagoPen,FecPagoEps=@pmFecPagoEps,FecPagoArp=@pmFecPagoArp
 WHERE IdConductor=@pmIdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpVehRemolq @pmIdRemque VARCHAR(10),@pmIdMarca VARCHAR(4),@pmIdColor VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmNEjes INT,@pmnLlantas INT,@pmAltura DECIMAL(14,4),@pmAncho DECIMAL(14,4),@pmLargo DECIMAL(14,4)
,@pmVlumen DECIMAL(14,4),@pmPesoKg DECIMAL(14,4),@pmCapneta DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmNumComp INT,@pmCapcComp VARCHAR(50),@pmNumSerial VARCHAR(30),@pmIdPropietario VARCHAR(16),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME
,@pmVidaUtil INT,@pmVrComercial MONEY,@pmVrAvaludo MONEY,@pmVrAsegurado MONEY,@pmKilmetraje INT,@pmEquipoAdic VARCHAR(50),@pmDescripcion VARCHAR(100),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTarjetaProp VARCHAR(30),@pmFecTarjProp  SMALLDATETIME,@pmVigTarjProp SMALLDATETIME,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE VehRemolq SET IdMarca=@pmIdMarca,IdColor=@pmIdColor,IdCrceria=@pmIdCrceria,Modelo=@pmModelo,NEjes=@pmNEjes,nLlantas=@pmnLlantas,Altura=@pmAltura,Ancho=@pmAncho,Largo=@pmLargo,Vlumen=@pmVlumen,PesoKg=@pmPesoKg,Capneta=@pmCapneta,UndCapc=@pmUndCapc
,NumComp=@pmNumComp,CapcComp=@pmCapcComp,NumSerial=@pmNumSerial,IdPropietario=@pmIdPropietario,NitProv=@pmNitProv,FecCompra=@pmFecCompra,VidaUtil=@pmVidaUtil,VrComercial=@pmVrComercial,VrAvaludo=@pmVrAvaludo,VrAsegurado=@pmVrAsegurado,Kilmetraje=@pmKilmetraje
,EquipoAdic=@pmEquipoAdic,Descripcion=@pmDescripcion,CdCenSer=@pmCdCenSer,CdLocal=@pmCdLocal,Ubicacion=@pmUbicacion,Observacion=@pmObservacion,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,TarjetaProp=@pmTarjetaProp,FecTarjProp=@pmFecTarjProp,VigTarjProp=@pmVigTarjProp
 WHERE IdRemque=@pmIdRemque
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO