if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomGruposConvLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomGruposConvLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomEscalafonesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomEscalafonesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConcCondLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConcCondLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovConvLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovConvLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovEstLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovEstLta]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovConvLta] @pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmNumNovIni INT=Null,@pmNumNovFin INT=Null
 AS 
 SELECT N.Id,N.IdPeriodo,N.IdEmpleado,E.Apellidos,E.Nombres,N.NContrato,N.FecInicial,N.FecFinal,N.IdConv,CV.Proyecto,N.IdGrupo,G.GrupoConv,N.CodEscfon,EC.Escalafon
,EC.IdCargo,CG.Cargo,N.CodBase,B.NombreBase,N.CodZona,Z.ZonaConv,N.TipoNom,N.Cantidad AS CantTotal,N.VrDevengado,N.VrDeducido,N.VrSalario
--items del detalle 
,D.Item,D.IdConcepto,CN.Concepto,D.Descripcion,D.Cantidad,D.VrUnitario,D.VrTotal,D.VrBaseLiq,D.Tarifa,D.Unidad,D.ClaseCon,D.NitTercero,T.RazonSocial
,D.Referencia,D.HoraInicial,D.HoraFinal,D.TipoReg,D.NumRegVf,D.CantDias,D.CantNov,CN.BaseTrfa
,N.OrigenReg,N.Anulado,N.FechaCrea,N.IdUsuario,Usuario
 FROM Trn_NomNovConv AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado 
 INNER JOIN NomConvenciones AS CV ON N.IdConv=CV.Id INNER JOIN NomGruposConv AS G ON N.IdGrupo=G.IdGrupo
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario 
 INNER JOIN Trn_NomNovConc AS D ON N.Id=D.IdNovedad INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero
 LEFT JOIN NomEscalafones AS EC ON N.CodEscfon=EC.IdEscfon LEFT JOIN Cargos AS CG ON EC.IdCargo=CG.IdCargo
 LEFT JOIN NomZonasConv AS Z ON N.CodZona=Z.IdZona LEFT JOIN NomBasesConv AS B ON N.CodBase=B.IdBase
 WHERE N.Id BETWEEN ISNULL(@pmNumNovIni,0) AND ISNULL(@pmNumNovFin,2147483647)
 AND ((N.FecInicial BETWEEN @pmFecInicio AND @pmFecFinal) OR (N.FecFinal BETWEEN @pmFecInicio AND @pmFecFinal)
 OR (N.FecInicial>=@pmFecInicio AND N.FecFinal<=@pmFecFinal))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomGruposConvLta] 
 AS 
 SELECT G.IdGrupo,G.GrupoConv,G.IdConv,C.Proyecto,C.Fecha,C.FecInicio,C.FecFinal
 ,C.IdLocal AS CodCiudad,L.Localidad AS NomCiudad,L.IdDep,D.Departamento
,C.NitTercero,RazonSocial,G.Inactivo
 FROM NomGruposConv AS G INNER JOIN NomConvenciones AS C ON G.IdConv=C.Id
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomEscalafonesLta]
 AS 
 SELECT E.IdEscfon,E.Escalafon,E.IdConv,C.Proyecto,E.IdGrupo,G.GrupoConv,E.IdCargo,CG.Cargo
 ,C.Fecha,C.FecInicio,C.FecFinal,C.IdLocal AS CodCiudad,L.Localidad AS NomCiudad,L.IdDep,D.Departamento
 ,C.NitTercero,RazonSocial,E.Inactivo
 FROM NomEscalafones AS E INNER JOIN Cargos AS CG ON E.IdCargo=CG.IdCargo
 INNER JOIN NomConvenciones AS C ON E.IdConv=C.Id INNER JOIN NomGruposConv AS G ON E.IdGrupo=G.IdGrupo
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConcCondLta] @pmPeriodoIni INT,@pmPeriodoFin INT
 AS 
 SELECT CN.Id,CN.IdConc,CN.Item,CN.CodCond,EstadoConv AS DescCond,NColor
 FROM Trn_NomConcCond AS CN INNER JOIN Trn_NomConcConv AS CF ON CN.IdConc=CF.Id
 LEFT JOIN NomEstadosConv AS E ON CN.CodCond=E.IdEstado
 WHERE TipoEnte='ESTADOS' AND CF.IdPeriodo BETWEEN ISNULL(@pmPeriodoIni,0) AND ISNULL(@pmPeriodoFin,2147483647)
 UNION ALL
 SELECT CN.Id,CN.IdConc,CN.Item,CN.CodCond,NombreBase,0
 FROM Trn_NomConcCond AS CN INNER JOIN Trn_NomConcConv AS CF ON CN.IdConc=CF.Id
 LEFT JOIN NomBasesConv AS B ON CN.CodCond=B.IdBase
 WHERE TipoEnte='BASES' AND CF.IdPeriodo BETWEEN ISNULL(@pmPeriodoIni,0) AND ISNULL(@pmPeriodoFin,2147483647)
 UNION ALL 
 SELECT CN.Id,CN.IdConc,CN.Item,CN.CodCond,C.Concepto,0
 FROM Trn_NomConcCond AS CN INNER JOIN Trn_NomConcConv AS CF ON CN.IdConc=CF.Id
 LEFT JOIN NomConceptos AS C ON CN.CodCond=C.IdConcepto
 WHERE TipoEnte='NOVEDADES' AND CF.IdPeriodo BETWEEN ISNULL(@pmPeriodoIni,0) AND ISNULL(@pmPeriodoFin,2147483647)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovEstLta] @pmNumNovIni INT=Null,@pmNumNovFin INT=Null
AS 
 SELECT NE.Id,NE.IdNovedad,NE.IdEstado,E.EstadoConv,E.NColor,NE.FechaDia
 FROM Trn_NomNovEst AS NE INNER JOIN NomEstadosConv AS E ON NE.IdEstado=E.IdEstado
 WHERE NE.IdNovedad BETWEEN ISNULL(@pmNumNovIni,0) AND ISNULL(@pmNumNovFin,2147483647)
GO