if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEmp_Estudios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEmp_Estudios]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEmp_Parientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEmp_Parientes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomCapacitaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomCapacitaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComprobantesCaj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComprobantesCaj]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_Estudios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_Estudios]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_EstudiosDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_EstudiosDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_Parientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_Parientes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ParientesDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ParientesDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoOrdenRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoOrdenRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEmp_Estudios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEmp_Estudios]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEmp_Parientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEmp_Parientes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomCapacitaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomCapacitaciones]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paQryMttoOrdenRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null
AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,Fecha,FechaOrden,FechaVence,O.IdVehiculo,NumVeh,kmVehiculo,nRemolque,kmRemolque,O.IdTipoOdt AS CdTipoOdt,TipoOrden,O.IdCenSer AS CdCenSer,CentroServ
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdOperario AS NitOperario,T.RazonSocial AS NomOperario,IdSupervisor,NSU.RazonSocial AS Supervisor,O.Modalidad
,VrServicios,VrRepuestos,VrManoObra,VrOtros,FechaCierre,NitCenSer,O.CdEstadoVeh,EV.Estado AS EstadoVeh,EV.NColor AS EdvNColor,EV.OutDemand,O.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,ED.NColor AS EdNumColor
,O.Anulado,NumDev,FecDev,O.Observacion AS Observ,O.TimeSys AS FechaCrea,O.FecUpdate AS FecActualiza,O.IdCiaCrea AS CdCIaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,Leyenda
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion,KmInicial,KmActual,Km2Actual,V.Ubicacion
,V.IdEstado AS VehCdEstado,EVH.Estado AS VehEstado,EVH.NColor AS VehEstColor,EVH.OutDemand AS VehNoDisponible,V.Inactivo AS VehInactivo
,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_MttoOrden AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MttoTiposOdt AS TOT ON O.IdTipoOdt=TOT.IdTipoOdt INNER JOIN CentrosServ AS CS ON O.IdCenSer=CS.IdCenSer
 INNER JOIN Terceros AS T ON O.IdOperario=T.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NSU ON O.IdSupervisor=NSU.IdTercero
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 LEFT JOIN EstadoVeh AS EV ON O.CdEstadoVeh=EV.IdEstado 
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero
 LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN EstadoVeh AS EVH ON V.IdEstado=EVH.IdEstado
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paQryEmp_Parientes] @pmIdEmpleado VARCHAR(16),@pmItem INT
 AS 
 SELECT IdEmpleado,Item,Parntsco,Nombre,FecNacmto,UltGrado,Dirccion,Telefno,TipoRef,NitRef,EmailRef,CargoRef
 FROM Emp_Parientes
 WHERE IdEmpleado=@pmIdEmpleado  AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647)) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paInsEmp_Parientes] @pmIdEmpleado VARCHAR(16),@pmItem INT,@pmParntsco VARCHAR(10),@pmNombre VARCHAR(50)
,@pmFecNacmto SMALLDATETIME,@pmUltGrado VARCHAR(20),@pmDirccion VARCHAR(100),@pmTelefno VARCHAR(20),@pmTipoRef VARCHAR(10),@pmNitRef VARCHAR(16)
,@pmEmailRef VARCHAR(100),@pmCargoRef VARCHAR(50)
 AS 
 INSERT INTO Emp_Parientes (IdEmpleado,Item,Parntsco,Nombre,FecNacmto,UltGrado,Dirccion,Telefno,TipoRef,NitRef,EmailRef,CargoRef)
 VALUES (@pmIdEmpleado,@pmItem,@pmParntsco,@pmNombre,@pmFecNacmto,@pmUltGrado,@pmDirccion,@pmTelefno,@pmTipoRef,@pmNitRef,@pmEmailRef,@pmCargoRef)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paUpEmp_Parientes] @pmIdEmpleado VARCHAR(16),@pmItem INT,@pmParntsco VARCHAR(10),@pmNombre VARCHAR(50)
,@pmFecNacmto SMALLDATETIME,@pmUltGrado VARCHAR(20),@pmDirccion VARCHAR(100),@pmTelefno VARCHAR(20),@pmTipoRef VARCHAR(10),@pmNitRef VARCHAR(16)
,@pmEmailRef VARCHAR(100),@pmCargoRef VARCHAR(50)
 AS 
 UPDATE Emp_Parientes SET Parntsco=@pmParntsco,Nombre=@pmNombre,FecNacmto=@pmFecNacmto,UltGrado=@pmUltGrado,Dirccion=@pmDirccion,Telefno=@pmTelefno
,TipoRef=@pmTipoRef,NitRef=@pmNitRef,EmailRef=@pmEmailRef,CargoRef=@pmCargoRef
 WHERE IdEmpleado=@pmIdEmpleado AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paQryEmp_ParientesDso] @pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT P.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Item,Parntsco,Nombre,P.FecNacmto AS FechaNaci,UltGrado,Dirccion,Telefno
,TipoRef,NitRef,EmailRef,CargoRef
 FROM Emp_Parientes AS P INNER JOIN Empleados AS E ON P.IdEmpleado=E.IdEmpleado
 WHERE P.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER BY P.IdEmpleado,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paInsNomCapacitaciones] @pmIdClase VARCHAR(4),@pmItem INT,@pmVigencia INT,@pmCdEmpleado VARCHAR(16),@pmCdProf VARCHAR(4)
,@pmCdCargo VARCHAR(4),@pmCdArea VARCHAR(4),@pmCdDep VARCHAR(4),@pmCumplimto VARCHAR(20),@pmInactivo BIT
 AS 
 INSERT INTO NomCapacitaciones (IdClase,Item,Vigencia,CdEmpleado,CdProf,CdCargo,CdArea,CdDep,Cumplimto,Inactivo)
 VALUES (@pmIdClase,@pmItem,@pmVigencia,@pmCdEmpleado,@pmCdProf,@pmCdCargo,@pmCdArea,@pmCdDep,@pmCumplimto,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paUpNomCapacitaciones] @pmIdClase VARCHAR(4),@pmItem INT,@pmVigencia INT,@pmCdEmpleado VARCHAR(16),@pmCdProf VARCHAR(4)
,@pmCdCargo VARCHAR(4),@pmCdArea VARCHAR(4),@pmCdDep VARCHAR(4),@pmCumplimto VARCHAR(20),@pmInactivo BIT
 AS 
 UPDATE NomCapacitaciones SET Vigencia=@pmVigencia,CdEmpleado=@pmCdEmpleado,CdProf=@pmCdProf,CdCargo=@pmCdCargo
,CdArea=@pmCdArea,CdDep=@pmCdDep,Cumplimto=@pmCumplimto,Inactivo=@pmInactivo
 WHERE IdClase=@pmIdClase AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paQryEmp_EstudiosDso] @pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT ER.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Item,Estblecmto,Titulo,Fecha,FecVence,ER.IdClase,NomClase,Habilitado
 FROM Emp_Estudios AS ER INNER JOIN Empleados AS E ON ER.IdEmpleado=E.IdEmpleado
 LEFT JOIN ClaseCapac AS C ON ER.IdClase=C.IdClase
 WHERE ER.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER BY ER.IdEmpleado,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paQryComprobantesCaj] @pmFecha SMALLDATETIME,@pmIdCia CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 SELECT  TipCom,IdCia,IdUsuario,SUM(VrTotal) AS STOTAL
 FROM Trn_Comprobantes
 WHERE Fecha=@pmFecha AND EsEgreso=1 AND EnEfectivo=1 AND TipDoc<>'DVA' AND TipDoc<>'DAO'
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND  IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 GROUP BY TipCom,IdCia,IdUsuario 
 UNION ALL
 SELECT 'DEG',IdCia,IdUsuario,SUM(VrTotal) AS STOTAL
 FROM Trn_Comprobantes
 WHERE Fecha=@pmFecha AND EsEgreso=1 AND EnEfectivo=1 AND TipDoc IN ('DVA','DAO') AND Integrado=1
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 GROUP BY IdCia,IdUsuario 
 ORDER BY IdCia,IdUsuario
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paUpEmp_Estudios] @pmIdEmpleado VARCHAR(16),@pmItem INT,@pmEstblecmto VARCHAR(50)
,@pmTitulo VARCHAR(50),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdClase VARCHAR(4),@pmHabilitado BIT
 AS 
 UPDATE Emp_Estudios SET Estblecmto=@pmEstblecmto,Titulo=@pmTitulo,Fecha=@pmFecha,FecVence=@pmFecVence
 ,IdClase=@pmIdClase,Habilitado=@pmHabilitado
 WHERE IdEmpleado=@pmIdEmpleado AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paQryEmp_Estudios] @pmIdEmpleado VARCHAR(16),@pmItem INT
 AS 
 SELECT IdEmpleado,Item,Estblecmto,Titulo,Fecha,FecVence,IdClase,Habilitado
 FROM Emp_Estudios
 WHERE IdEmpleado=@pmIdEmpleado 
 AND (Item>=ISNULL(@pmItem,0) AND Item<=ISNULL(@pmItem,2147483647)) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [paInsEmp_Estudios] @pmIdEmpleado VARCHAR(16),@pmItem INT,@pmEstblecmto VARCHAR(50)
,@pmTitulo VARCHAR(50),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdClase VARCHAR(4),@pmHabilitado BIT
 AS 
 INSERT INTO Emp_Estudios (IdEmpleado,Item,Estblecmto,Titulo,Fecha,FecVence,IdClase,Habilitado)
 VALUES (@pmIdEmpleado,@pmItem,@pmEstblecmto,@pmTitulo,@pmFecha,@pmFecVence,@pmIdClase,@pmHabilitado)
GO