if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomBasesConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomBasesConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomConcConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomConcConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomEscalafones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomEscalafones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomEstadosConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomEstadosConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomGruposConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomGruposConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomNovConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNomZonasConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelNomZonasConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomBasesConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomBasesConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomConcConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomConcConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomConCuentas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomConCuentas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomEscalafones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomEscalafones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomEstadosConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomEstadosConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomGruposConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomGruposConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomNovConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomNovConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomNovConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomZonasConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomZonasConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ContratoDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoLab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ContratoLab]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmpleadosCnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmpleadosCnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomBasesConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomBasesConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConcConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConcConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConcConvLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConcConvLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConCuentas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConCuentas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConCuentasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConCuentasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConvencionesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConvencionesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomEscalafones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomEscalafones]
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].[paQryNomEstadosConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomEstadosConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomGruposConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomGruposConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovConv]
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].[paQryNomNovedadPen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovedadPen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomZonasConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomZonasConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomBasesConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomBasesConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomConcConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomConcConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomConCuentas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomConCuentas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomEscalafones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomEscalafones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomEstadosConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomEstadosConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomGruposConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomGruposConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomNovConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomZonasConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomZonasConv]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_ContratoLab] @pmNContrato INT,@pmInactivo BIT=Null
 AS 
 SELECT  C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo
,C.IdJornada AS IdJrnada,J.Jornada,J.TurnoRotativo,Rangos,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdClase AS CodCla,ClaseRiesgo,Tarifa,TipoTrabj,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdNom AS CodNom
,TipoNomina,Asistencia,NoDevenga,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,FspFinmes,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac
,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,DsoSabado,TipoPrendDot,TipoMedSent,C.TipoTraspaso,C.FechaTraspaso,C.ContratoRef,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,C.Inactivo AS Inactvo,IdCot,C.IdGrupo AS GrupAR,VrUpc,SubTipoCot,FecFinPrueba,TN.NomDiario
,C.IdConvCT,C.CodGrupoCT,C.CodBaseCT,C.CodEscfon
--datos del empleado
,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom,CC.Fondo AS CajaComp
,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltIng,FecUltRet,FecVincula,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,DecRenta
,E.IdProf AS CodProf,Profesion,Observacion,E.IdEstado AS Emp_IdEstdo,ET.Estado AS Emp_Estdo,E.Inactivo AS Emp_Inactivo
,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update,C.IdUsuario AS IdUsuari,Usuario,LiqEspSalud,DctoSalud,PagoSalud
 FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia  INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado
 WHERE  C.NContrato=@pmNContrato  AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_ContratoDso] @pmIdEmpleado VARCHAR(16)=Null,@pmIndefinido BIT=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdSubCos VARCHAR(16)=Null,@pmIdNom VARCHAR(4)=Null,@pmTipoLiquida CHAR(1)=Null
,@pmSalMinimo BIT=Null,@pmSalIntegral BIT=Null,@pmFPnomina VARCHAR(10)=Null,@pmModoRet INT=Null,@pmIdClase VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdEstado VARCHAR(4)=Null
,@pmNContrato INT=Null,@pmFecIngresoIni SMALLDATETIME=Null,@pmFecIngresoFin SMALLDATETIME=Null,@pmATFinMes BIT=Null,@pmSegFinMes BIT=Null,@pmDchDotacion BIT=Null
--parametros de empleados
,@pmTipoSangre CHAR(2)=Null,@pmFactorRh CHAR(1)=Null,@pmSexo CHAR(1)=Null,@pmIdEstCivil VARCHAR(4)=Null,@pmClaseLib INT=Null, @pmIdProf VARCHAR(4)=Null,@pmTallaCam VARCHAR(5)=Null
,@pmTallaPan VARCHAR(5)=Null,@pmTallaZap VARCHAR(5)=Null,@pmIdFonPen VARCHAR(8)=Null,@pmIdFonEps VARCHAR(8)=Null,@pmIdFonArp VARCHAR(8)=Null,@pmIdFonCes VARCHAR(8)=Null,@pmIdCajaCom VARCHAR(8)=Null
,@pmIdBanco VARCHAR(4)=Null,@pmEmp_IdEstado VARCHAR(4)=Null,@pmEmp_Inactivo BIT=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null
 AS 
 SELECT  C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo
,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania
,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,FspFinmes,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,DsoSabado,FecFinPrueba,TipoPrendDot,TipoMedSent,BenefPacto,C.TipoTraspaso,C.FechaTraspaso,C.ContratoRef,C.IdMedPago,MedioPago,C.IdActEco,AC.IdSector,AC.CodAdicional,AC.DescActividad
,C.IdConvCT,C.CodGrupoCT,C.CodBaseCT,C.CodEscfon,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,IdCot,C.IdGrupo AS GrupAR,VrUpc
--empleados
,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,VvdaPropia,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion
,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja
,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes
,IdCajaCom,CC.Fondo AS CajaComp,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,FotoEmp,FotoLic,FotoFirma,Observacion,E.IdEstado AS Emp_IdEstado
,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,TipoId,Codigo,ET.NColor AS EmpEstcolor,E.FechaAdd AS EmpFecAdd,E.FechaUpdate AS EmpFecupd,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario,SubTipoCot
 FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado 
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario
 LEFT JOIN NomMediosPago AS MP ON C.IdMedPago=MP.IdMedPago LEFT JOIN NomActividades AS AC ON C.IdActEco=AC.IdActEco
 WHERE C.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND C.IdCargo LIKE ISNULL(@pmIdCargo,'%')
 AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND C.IdSubCos LIKE ISNULL(@pmIdSubCos,'%')  AND C.IdNom LIKE ISNULL(@pmIdNom,'%') 
 AND C.TipoLiquida LIKE ISNULL(@pmTipoLiquida,'%') AND FPnomina LIKE ISNULL(@pmFPnomina,'%')  AND C.IdClase LIKE ISNULL(@pmIdClase,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (C.NContrato>=ISNULL(@pmNContrato,0)  AND C.NContrato<=ISNULL(@pmNContrato,2147483647)) 
 AND (ModoRet>=ISNULL(@pmModoRet,0)  AND ModoRet<=ISNULL(@pmModoRet,2147483647)) 
 AND (SalMinimo=ISNULL(@pmSalMinimo,0) or SalMinimo=ISNULL(@pmSalMinimo,1))
 AND (Indefinido=ISNULL(@pmIndefinido,0) or Indefinido=ISNULL(@pmIndefinido,1))
 AND (SalIntegral=ISNULL(@pmSalIntegral,0) or SalIntegral=ISNULL(@pmSalIntegral,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (ATFinMes=ISNULL(@pmATFinMes,0) or ATFinMes=ISNULL(@pmATFinMes,1))
 AND (SegFinMes=ISNULL(@pmSegFinMes,0) or SegFinMes=ISNULL(@pmSegFinMes,1))
 AND (DchDotacion=ISNULL(@pmDchDotacion,0) or DchDotacion=ISNULL(@pmDchDotacion,1))
 AND (FecIngreso>=ISNULL(@pmFecIngresoIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngresoFin,CAST('20781230' AS SMALLDATETIME)))
 AND TipoSangre LIKE ISNULL(@pmTipoSangre,'%%') AND FactorRh LIKE ISNULL(@pmFactorRh,'%') AND Sexo LIKE ISNULL(@pmSexo,'%')
 AND IdEstCivil LIKE ISNULL(@pmIdEstCivil,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%') AND TallaCam LIKE ISNULL(@pmTallaCam,'%%')
 AND TallaPan LIKE ISNULL(@pmTallaPan,'%%') AND TallaZap LIKE ISNULL(@pmTallaZap,'%%') AND IdFonPen LIKE ISNULL(@pmIdFonPen,'%')
 AND IdFonCes LIKE ISNULL(@pmIdFonCes,'%') AND IdFonArp LIKE ISNULL(@pmIdFonArp,'%') AND IdFonEps LIKE ISNULL(@pmIdFonEps,'%')
 AND IdCajaCom LIKE ISNULL(@pmIdCajaCom ,'%') AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND E.IdEstado LIKE ISNULL(@pmEmp_IdEstado,'%')
 AND (ClaseLib>=ISNULL(@pmClaseLib,0)  AND ClaseLib<=ISNULL(@pmClaseLib,2147483647)) 
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1))
 AND (FecNacmto>=ISNULL(@pmFecNacmtoIni,CAST('19100101' AS SMALLDATETIME)) AND FecNacmto<=ISNULL(@pmFecNacmtoFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Apellidos,Nombres,C.NContrato
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmpleadosCnt] @pmIdNom VARCHAR(4),@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT E.IdEmpleado AS IdEmpledo,Apellidos,Nombres,E.TipoId,E.Dv,IdLugarCed,LC.Localidad AS LugarCed,E.IdProf AS CodProf,Profesion,TallaCam,TallaPan,TallaZap
,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom
,CC.Fondo AS CajaComp,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,CdLocCue,E.NContrato AS NumContrato,FecUltIng,FecUltRet,FecVincula,NCAnterior,FecIngAnt,FecRetAnt
,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,FecUltAum,FecUltCes
,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,FecUltParc,EsCondtor,pVehiculo,DecRenta,E.IdEstado AS IdEst_Emp,ET.Estado AS Estad_Emp,E.Inactivo AS Inctivo,Codigo,E.IdUsuario  AS IdUsuar
--datos del contrato
,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdJornada AS IdJrnada,J.Jornada,J.TurnoRotativo,Rangos,C.IdArea AS CodArea,Area,C.IdDep AS CodDep
,Dependencia,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac
,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,TipoTrabj,IdCot,SubTipoCot,IdGrupo,VrUpc,Reclmto,Notas,C.IdEstado AS IdEst_Con,EC.Estado AS Estad_Con,C.Inactivo AS Inactvo,CdFonAvp,FspFinmes,FecFinPrueba
,LiqEspSalud,DctoSalud,PagoSalud,TN.NomDiario,C.TipoTraspaso,C.FechaTraspaso,C.ContratoRef,C.IdConvCT,C.CodGrupoCT,C.CodBaseCT,C.CodEscfon
 FROM Empleados AS E INNER JOIN Emp_Contrato AS C ON E.NContrato=C.NContrato
 INNER JOIN  Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia  INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado
 WHERE E.Inactivo=0 AND  C.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER  BY E.IdEmpleado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomNovConv] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmCodBase VARCHAR(4),@pmCodZona VARCHAR(4),@pmTipoNom VARCHAR(10),@pmCantidad DECIMAL(14,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmVrSalario MONEY,@pmOrigenReg INT,@pmAnulado BIT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomNovConv (IdPeriodo,IdEmpleado,NContrato,FecInicial,FecFinal,IdConv,IdGrupo,CodEscfon,CodBase,CodZona,TipoNom,Cantidad,VrDevengado,VrDeducido,VrSalario,OrigenReg,Anulado,FechaCrea,IdUsuario)
 VALUES (@pmIdPeriodo,@pmIdEmpleado,@pmNContrato,@pmFecInicial,@pmFecFinal,@pmIdConv,@pmIdGrupo,@pmCodEscfon,@pmCodBase,@pmCodZona,@pmTipoNom,@pmCantidad,@pmVrDevengado,@pmVrDeducido,@pmVrSalario,@pmOrigenReg,@pmAnulado,@pmFechaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovConv] @pmId BIGINT
 AS 
 SELECT [Id],IdPeriodo,IdEmpleado,NContrato,FecInicial,FecFinal,IdConv,IdGrupo,CodEscfon,CodBase,CodZona,TipoNom,Cantidad,VrDevengado,VrDeducido,VrSalario,OrigenReg,Anulado,FechaCrea,IdUsuario
 FROM Trn_NomNovConv
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomNovConv] @pmId BIGINT,@pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmCodBase VARCHAR(4),@pmCodZona VARCHAR(4),@pmTipoNom VARCHAR(10),@pmCantidad DECIMAL(14,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmVrSalario MONEY,@pmOrigenReg INT,@pmAnulado BIT
 AS 
 UPDATE Trn_NomNovConv SET IdPeriodo=@pmIdPeriodo,IdEmpleado=@pmIdEmpleado,NContrato=@pmNContrato,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,IdConv=@pmIdConv,IdGrupo=@pmIdGrupo,CodEscfon=@pmCodEscfon,CodBase=@pmCodBase,CodZona=@pmCodZona,TipoNom=@pmTipoNom,Cantidad=@pmCantidad,VrDevengado=@pmVrDevengado,VrDeducido=@pmVrDeducido,VrSalario=@pmVrSalario,OrigenReg=@pmOrigenReg,Anulado=@pmAnulado
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomNovConv] @pmId BIGINT
 AS 
 BEGIN
  DECLARE @TrnNombre VARCHAR(20)
  SET @TrnNombre = 'TrnNomNovConv'
  BEGIN TRANSACTION @TrnNombre
  DELETE FROM Trn_NomNovConc WHERE IdNovedad=@pmId
  DELETE FROM Trn_NomNovEst WHERE IdNovedad=@pmId
  DELETE FROM Trn_NomNovConv WHERE [Id]=@pmId
  COMMIT TRANSACTION @TrnNombre
 END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovConvLta] @pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME
 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,N.VrDevengado,N.VrDeducido,N.VrSalario,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 
 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.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].[paQryNomConvenciones] @pmId INT
 AS 
 SELECT Id,Fecha,Proyecto,FecInicio,FecFinal,IdLocal,NitTercero,Observacion,FechaCrea,Inactivo
 FROM NomConvenciones
 WHERE [Id]=@pmId
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,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
 WHERE E.Inactivo=0
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomConvenciones] @pmFecha SMALLDATETIME,@pmProyecto VARCHAR(150),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdLocal VARCHAR(8),@pmNitTercero VARCHAR(16),@pmObservacion VARCHAR(500),@pmFechaCrea SMALLDATETIME,@pmInactivo BIT
 AS 
 INSERT INTO NomConvenciones (Fecha,Proyecto,FecInicio,FecFinal,IdLocal,NitTercero,Observacion,FechaCrea,Inactivo)
 VALUES (@pmFecha,@pmProyecto,@pmFecInicio,@pmFecFinal,@pmIdLocal,@pmNitTercero,@pmObservacion,@pmFechaCrea,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomConvenciones] @pmId INT
 AS 
 DELETE FROM NomConvenciones WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConvencionesLta]
 AS 
 SELECT C.Id,C.Proyecto,C.Fecha,C.FecInicio,C.FecFinal,C.IdLocal AS CodCiudad,L.Localidad AS NomCiudad,L.IdDep,D.Departamento
,C.NitTercero,RazonSocial,C.Observacion,C.FechaCrea,C.Inactivo
 FROM NomConvenciones AS C 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].[paUpNomConvenciones] @pmId INT,@pmFecha SMALLDATETIME,@pmProyecto VARCHAR(150),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdLocal VARCHAR(8),@pmNitTercero VARCHAR(16),@pmObservacion VARCHAR(500),@pmFechaCrea SMALLDATETIME,@pmInactivo BIT
 AS 
 UPDATE NomConvenciones SET Fecha=@pmFecha,Proyecto=@pmProyecto,FecInicio=@pmFecInicio,FecFinal=@pmFecFinal,IdLocal=@pmIdLocal,NitTercero=@pmNitTercero,Observacion=@pmObservacion,Inactivo=@pmInactivo
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConcConvLta] @pmPeriodoIni INT,@pmPeriodoFin INT,@pmIdConvIni INT,@pmIdConvFin INT
 AS 
 SELECT C.Id,C.IdPeriodo,C.FecInicio,C.FecFinal,C.IdConv,CV.Proyecto,C.IdGrupo,G.GrupoConv,C.CodEscfon,E.Escalafon,E.IdCargo,CG.Cargo
,C.CodZona,ZonaConv,C.TipoNom,C.IdConcepto,CN.Concepto,CN.ClaseCon,C.Descripcion,C.Unidad,C.Importe,C.Observaciones
,C.IdUsuario,Usuario,C.FechaCrea,C.Inactivo
 FROM Trn_NomConcConv AS C INNER JOIN NomConvenciones AS CV ON C.IdConv=CV.Id INNER JOIN NomGruposConv AS G ON C.IdGrupo=G.IdGrupo
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN NomConceptos AS CN ON C.IdConcepto=CN.IdConcepto
 LEFT JOIN NomEscalafones AS E ON C.CodEscfon=E.IdEscfon LEFT JOIN Cargos AS CG ON E.IdCargo=CG.IdCargo
 LEFT JOIN NomZonasConv AS Z ON C.CodZona=Z.IdZona
 WHERE C.IdPeriodo BETWEEN ISNULL(@pmPeriodoIni,0) AND ISNULL(@pmPeriodoFin,2147483647)
 AND C.IdConv BETWEEN ISNULL(@pmIdConvIni,0) AND ISNULL(@pmIdConvFin,2147483647)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConCuentasLta] @pmIdConcepto VARCHAR(4)=Null,@pmCodNom VARCHAR(4)=Null,@pmCodDep VARCHAR(4)=Null
,@pmInactivo BIT=Null
 AS 
 SELECT NC.IdConcepto AS CodConcept,Concepto,Item,NClase,Tipo_Nom,NC.IdCuenta AS CodCuenta,NomCuenta,TipoMov
,CodNom,TipoNomina,CodDep,Dependencia,CodFon,Fondo,ClaseCon,Unidad,Tarifa,Pdtermnado,NitTerc
 FROM NomConCuentas AS NC INNER JOIN NomConceptos AS C ON NC.IdConcepto=C.IdConcepto
 INNER JOIN Puc AS P ON NC.IdCuenta=P.IdCuenta 
 LEFT JOIN TiposNom AS TN ON NC.CodNom=TN.IdNom
 LEFT JOIN Dependencias AS D ON NC.CodDep=D.IdDep
 LEFT JOIN Fondos AS F ON NC.CodFon=F.IdFondo
 WHERE NC.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND CodNom LIKE ISNULL(@pmCodNom,'%')
 AND CodDep LIKE ISNULL(@pmCodDep,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY NC.IdConcepto,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNovedadPen] @pmIdEmpleado VARCHAR(16),@pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME
,@pmIdConcepto VARCHAR(4)=Null,@pmModalidad VARCHAR(10)=Null,@pmRemnrado BIT=Null,@pmCantDias INT=0
 AS 
 SELECT N.IdEmpleado,N.Numero,N.IdConcepto,N.FecInicio,N.FecFinal,N.CantDias,N.DiaInicio,N.Modalidad,N.IdNov,N.CodInstla
,CodClsRie,N.NumAutza,CodOldFon,CodNewFon,N.VrCotVolAfi,N.VrCotVolApo,N.VrNoRetenido,N.TipoIncap
,Remnrado,FechaDcto,VrDescuento,N.TipoBaseLiq,N.Observacion,N.IdUsuario,N.Inactivo
,C.Concepto,C.ClaseCon,C.Tarifa AS ConcTarif,C.BaseTrfa AS ConcBase,C.DiasEmp,C.TarifaEmp
 FROM Trn_NomNovedad AS N INNER JOIN NomConceptos AS C ON N.IdConcepto=C.IdConcepto
 WHERE N.IdEmpleado=@pmIdEmpleado AND N.Inactivo=0 AND N.CantDias>@pmCantDias AND N.FecFinal>=@pmFecIni AND N.FecInicio<=@pmFecFin
 AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND N.Modalidad LIKE ISNULL(@pmModalidad,'%') 
 AND (Remnrado=ISNULL(@pmRemnrado,0) or Remnrado=ISNULL(@pmRemnrado,1))
 ORDER BY N.Numero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomNovConc] @pmIdNovedad BIGINT,@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDescripcion VARCHAR(500),@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrTotal MONEY,@pmVrBaseLiq MONEY,@pmTarifa DECIMAL(14,4),@pmUnidad VARCHAR(10),@pmClaseCon VARCHAR(10),@pmNitTercero VARCHAR(16),@pmReferencia VARCHAR(50),@pmHoraInicial DATETIME,@pmHoraFinal DATETIME
,@pmTipoReg INT,@pmNumRegVf INT,@pmCantDias INT,@pmCantNov INT
 AS 
 INSERT INTO Trn_NomNovConc (IdNovedad,Item,IdConcepto,Descripcion,Cantidad,VrUnitario,VrTotal,VrBaseLiq,Tarifa,Unidad,ClaseCon,NitTercero,Referencia,HoraInicial,HoraFinal,TipoReg,NumRegVf,CantDias,CantNov)
 VALUES (@pmIdNovedad,@pmItem,@pmIdConcepto,@pmDescripcion,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmVrBaseLiq,@pmTarifa,@pmUnidad,@pmClaseCon,@pmNitTercero,@pmReferencia,@pmHoraInicial,@pmHoraFinal,@pmTipoReg,@pmNumRegVf,@pmCantDias,@pmCantNov)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomConcConv] @pmId INT
 AS 
 BEGIN
  DECLARE @TrnNombre VARCHAR(20)
  SET @TrnNombre = 'TrnNomConcConv'
  BEGIN TRANSACTION @TrnNombre
  DELETE FROM Trn_NomConcCond WHERE IdConc=@pmId
  DELETE FROM Trn_NomConcConv WHERE [Id]=@pmId
  COMMIT TRANSACTION  @TrnNombre
 END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomConcConv] @pmId INT,@pmIdPeriodo INT,@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmCodZona VARCHAR(4),@pmTipoNom VARCHAR(10),@pmIdConcepto VARCHAR(4),@pmDescripcion VARCHAR(500),@pmUnidad VARCHAR(10),@pmImporte MONEY,@pmObservaciones VARCHAR(1000),@pmInactivo BIT,@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME
 AS 
 UPDATE Trn_NomConcConv SET IdPeriodo=@pmIdPeriodo,FecInicio=@pmFecInicio,FecFinal=@pmFecFinal,IdConv=@pmIdConv,IdGrupo=@pmIdGrupo,CodEscfon=@pmCodEscfon,CodZona=@pmCodZona,TipoNom=@pmTipoNom,IdConcepto=@pmIdConcepto,Descripcion=@pmDescripcion,Unidad=@pmUnidad,Importe=@pmImporte,Observaciones=@pmObservaciones,Inactivo=@pmInactivo
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConcConv] @pmId INT
 AS 
 SELECT [Id],IdPeriodo,FecInicio,FecFinal,IdConv,IdGrupo,CodEscfon,CodZona,TipoNom,IdConcepto,Descripcion,Unidad,Importe,Observaciones,IdUsuario,FechaCrea,Inactivo
 FROM Trn_NomConcConv
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomConcConv] @pmIdPeriodo INT,@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmCodZona VARCHAR(4),@pmTipoNom VARCHAR(10),@pmIdConcepto VARCHAR(4),@pmDescripcion VARCHAR(500),@pmUnidad VARCHAR(10),@pmImporte MONEY,@pmObservaciones VARCHAR(1000),@pmInactivo BIT,@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME
 AS 
 INSERT INTO Trn_NomConcConv (IdPeriodo,FecInicio,FecFinal,IdConv,IdGrupo,CodEscfon,CodZona,TipoNom,IdConcepto,Descripcion,Unidad,Importe,Observaciones,IdUsuario,FechaCrea,Inactivo)
 VALUES (@pmIdPeriodo,@pmFecInicio,@pmFecFinal,@pmIdConv,@pmIdGrupo,@pmCodEscfon,@pmCodZona,@pmTipoNom,@pmIdConcepto,@pmDescripcion,@pmUnidad,@pmImporte,@pmObservaciones,@pmIdUsuario,@pmFechaCrea,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomBasesConv] @pmIdBase VARCHAR(4)
 AS 
 DELETE FROM NomBasesConv
 WHERE IdBase=@pmIdBase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomBasesConv] @pmIdBase VARCHAR(4)
 AS 
 IF @pmIdBase IS NULL 
  BEGIN
   SELECT IdBase,NombreBase,Inactivo
   FROM NomBasesConv
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdBase,NombreBase,Inactivo
   FROM NomBasesConv
   WHERE IdBase=@pmIdBase 
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomBasesConv] @pmIdBase VARCHAR(4),@pmNombreBase VARCHAR(150),@pmInactivo BIT
 AS 
 INSERT INTO NomBasesConv (IdBase,NombreBase,Inactivo)
 VALUES (@pmIdBase,@pmNombreBase,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomBasesConv] @pmIdBase VARCHAR(4),@pmNombreBase VARCHAR(150),@pmInactivo BIT
 AS 
 UPDATE NomBasesConv SET NombreBase=@pmNombreBase,Inactivo=@pmInactivo
 WHERE IdBase=@pmIdBase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomEscalafones] @pmIdEscfon VARCHAR(4)
 AS 
 SELECT IdEscfon,Escalafon,IdConv,IdGrupo,IdCargo,Inactivo
 FROM NomEscalafones
 WHERE IdEscfon=@pmIdEscfon
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomEscalafones] @pmIdEscfon VARCHAR(4),@pmEscalafon VARCHAR(150),@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmIdCargo VARCHAR(4),@pmInactivo BIT
 AS 
 INSERT INTO NomEscalafones (IdEscfon,Escalafon,IdConv,IdGrupo,IdCargo,Inactivo)
 VALUES (@pmIdEscfon,@pmEscalafon,@pmIdConv,@pmIdGrupo,@pmIdCargo,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomEscalafones] @pmIdEscfon VARCHAR(4),@pmEscalafon VARCHAR(150),@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmIdCargo VARCHAR(4),@pmInactivo BIT
 AS 
 UPDATE NomEscalafones SET Escalafon=@pmEscalafon,IdConv=@pmIdConv,IdGrupo=@pmIdGrupo,IdCargo=@pmIdCargo,Inactivo=@pmInactivo
 WHERE IdEscfon=@pmIdEscfon
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomEscalafones] @pmIdEscfon VARCHAR(4)
 AS 
 DELETE FROM NomEscalafones WHERE IdEscfon=@pmIdEscfon
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomEstadosConv] @pmIdEstado VARCHAR(4)
 AS 
 IF @pmIdEstado IS NULL
  BEGIN
   SELECT IdEstado,EstadoConv,NColor,Inactivo
   FROM NomEstadosConv
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdEstado,EstadoConv,NColor,Inactivo
   FROM NomEstadosConv
   WHERE IdEstado=@pmIdEstado  
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomEstadosConv] @pmIdEstado VARCHAR(4)
 AS 
 DELETE FROM NomEstadosConv WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomEstadosConv] @pmIdEstado VARCHAR(4),@pmEstadoConv VARCHAR(50),@pmNColor INT,@pmInactivo BIT
 AS 
 INSERT INTO NomEstadosConv (IdEstado,EstadoConv,NColor,Inactivo)
 VALUES (@pmIdEstado,@pmEstadoConv,@pmNColor,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomEstadosConv] @pmIdEstado VARCHAR(4),@pmEstadoConv VARCHAR(50),@pmNColor INT,@pmInactivo BIT
 AS 
 UPDATE NomEstadosConv SET EstadoConv=@pmEstadoConv,NColor=@pmNColor,Inactivo=@pmInactivo
 WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_Contrato] @pmNContrato INT
 AS 
 SELECT NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdSubCos,IdCia,IdNom
,IdClase,TipoTrabj,IdCot,IdGrupo,VrUpc,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,SubTipoCot,DsoSabado,FspFinmes,FecFinPrueba
,TipoPrendDot,TipoMedSent,BenefPacto,TipoTraspaso,FechaTraspaso,ContratoRef,IdMedPago,IdActEco,IdConvCT,CodGrupoCT,CodBaseCT,CodEscfon,FecAdd,FecUpdate,IdUsuario
 FROM Emp_Contrato
 WHERE NContrato=@pmNContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpEmp_Contrato] @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4),@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT
,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2),@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4),@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmDsoSabado BIT
,@pmFspFinmes BIT,@pmFecFinPrueba SMALLDATETIME,@pmTipoPrendDot VARCHAR(10),@pmTipoMedSent VARCHAR(4),@pmBenefPacto VARCHAR(100),@pmTipoTraspaso INT,@pmFechaTraspaso SMALLDATETIME,@pmContratoRef INT,@pmIdMedPago VARCHAR(4),@pmIdActEco VARCHAR(10),@pmIdConvCT INT,@pmCodGrupoCT VARCHAR(4),@pmCodBaseCT VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Emp_Contrato SET IdEmpleado=@pmIdEmpleado,IdTipCon=@pmIdTipCon,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,Indefinido=@pmIndefinido,IdCargo=@pmIdCargo,IdJornada=@pmIdJornada,IdDep=@pmIdDep,IdArea=@pmIdArea,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdCia=@pmIdCia,IdNom=@pmIdNom,TipoLiquida=@pmTipoLiquida,ModPromdio=@pmModPromdio
,VrSalario=@pmVrSalario,VrAuxTrans=@pmVrAuxTrans,SalMinimo=@pmSalMinimo,SalIntegral=@pmSalIntegral,FPnomina=@pmFPnomina,ModoRet=@pmModoRet,FecAfiSeg=@pmFecAfiSeg,ATFinMes=@pmATFinMes,SegFinMes=@pmSegFinMes,DenyPEN=@pmDenyPEN,DenyEPS=@pmDenyEPS,DenyARP=@pmDenyARP,DenyCaj=@pmDenyCaj,DenyCes=@pmDenyCes,DenyPri=@pmDenyPri,DenyVac=@pmDenyVac,DenyHex=@pmDenyHex
,BasePrest=@pmBasePrest,BaseSeg=@pmBaseSeg,DchDotacion=@pmDchDotacion,DchCafeteria=@pmDchCafeteria,Reclmto=@pmReclmto,Notas=@pmNotas,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,IdSubCos=@pmIdSubCos,IdClase=@pmIdClase,TipoTrabj=@pmTipoTrabj ,IdCot=@pmIdCot,VrUpc=@pmVrUpc,IdGrupo=@pmIdGrupo,DenyDcto=@pmDenyDcto,SubTipoCot =@pmSubTipoCot
,DsoSabado=@pmDsoSabado,FspFinmes=@pmFspFinmes,FecFinPrueba=@pmFecFinPrueba,TipoPrendDot=@pmTipoPrendDot,TipoMedSent=@pmTipoMedSent,BenefPacto=@pmBenefPacto,TipoTraspaso=@pmTipoTraspaso,FechaTraspaso=@pmFechaTraspaso,ContratoRef=@pmContratoRef,IdMedPago=@pmIdMedPago,IdActEco=@pmIdActEco,IdConvCT=@pmIdConvCT,CodGrupoCT=@pmCodGrupoCT,CodBaseCT=@pmCodBaseCT,CodEscfon=@pmCodEscfon
 WHERE NContrato=@pmNContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsEmp_Contrato] @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4)
,@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT
,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2)
,@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4)
,@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmDsoSabado BIT,@pmFspFinmes BIT,@pmFecFinPrueba SMALLDATETIME,@pmTipoPrendDot VARCHAR(10),@pmTipoMedSent VARCHAR(4),@pmBenefPacto VARCHAR(100),@pmTipoTraspaso INT,@pmFechaTraspaso SMALLDATETIME,@pmContratoRef INT,@pmIdMedPago VARCHAR(4),@pmIdActEco VARCHAR(10)
,@pmIdConvCT INT,@pmCodGrupoCT VARCHAR(4),@pmCodBaseCT VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Emp_Contrato (NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdCia,IdNom,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS
,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,IdSubCos,IdClase,TipoTrabj,FecAdd,IdUsuario,IdCot,VrUpc,IdGrupo,DenyDcto,SubTipoCot,DsoSabado,FspFinmes,FecFinPrueba,TipoPrendDot,TipoMedSent,BenefPacto,TipoTraspaso,FechaTraspaso,ContratoRef,IdMedPago,IdActEco,IdConvCT,CodGrupoCT,CodBaseCT,CodEscfon)
 VALUES (@pmNContrato,@pmIdEmpleado,@pmIdTipCon,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmIndefinido,@pmIdCargo,@pmIdJornada,@pmIdDep,@pmIdArea,@pmIdInstala,@pmIdCCosto,@pmIdCia,@pmIdNom,@pmTipoLiquida,@pmModPromdio,@pmVrSalario,@pmVrAuxTrans,@pmSalMinimo
,@pmSalIntegral,@pmFPnomina,@pmModoRet,@pmFecAfiSeg,@pmATFinMes,@pmSegFinMes,@pmDenyPEN,@pmDenyEPS,@pmDenyARP,@pmDenyCaj,@pmDenyCes,@pmDenyPri,@pmDenyVac,@pmDenyHex,@pmBasePrest,@pmBaseSeg,@pmDchDotacion,@pmDchCafeteria,@pmReclmto,@pmNotas
,@pmIdEstado,@pmInactivo,@pmIdSubCos,@pmIdClase,@pmTipoTrabj,@pmFecAdd,@pmIdUsuario,@pmIdCot,@pmVrUpc,@pmIdGrupo,@pmDenyDcto,@pmSubTipoCot,@pmDsoSabado,@pmFspFinmes,@pmFecFinPrueba,@pmTipoPrendDot,@pmTipoMedSent,@pmBenefPacto,@pmTipoTraspaso,@pmFechaTraspaso,@pmContratoRef,@pmIdMedPago,@pmIdActEco,@pmIdConvCT,@pmCodGrupoCT,@pmCodBaseCT,@pmCodEscfon)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConCuentas] @pmIdConcepto VARCHAR(4),@pmItem INT
 AS 
 SELECT IdConcepto,Item,NClase,IdCuenta,TipoMov,CodNom,CodDep,CodFon,NitTerc,Tipo_Nom
 FROM NomConCuentas
 WHERE IdConcepto=@pmIdConcepto 
 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 [dbo].[paInsNomConCuentas] @pmIdConcepto VARCHAR(4),@pmItem INT,@pmNClase INT,@pmIdCuenta VARCHAR(16)
,@pmTipoMov CHAR(1),@pmCodNom VARCHAR(4),@pmCodDep VARCHAR(4),@pmCodFon VARCHAR(8),@pmNitTerc CHAR(1),@pmTipo_Nom VARCHAR(10)
 AS 
 INSERT INTO NomConCuentas (IdConcepto,Item,NClase,IdCuenta,TipoMov,CodNom,CodDep,CodFon,NitTerc,Tipo_Nom)
 VALUES (@pmIdConcepto,@pmItem,@pmNClase,@pmIdCuenta,@pmTipoMov,@pmCodNom,@pmCodDep,@pmCodFon,@pmNitTerc,@pmTipo_Nom)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomConCuentas] @pmIdConcepto VARCHAR(4),@pmItem INT,@pmNClase INT,@pmIdCuenta VARCHAR(16)
,@pmTipoMov CHAR(1),@pmCodNom VARCHAR(4),@pmCodDep VARCHAR(4),@pmCodFon VARCHAR(8),@pmNitTerc CHAR(1),@pmTipo_Nom VARCHAR(10)
 AS 
 UPDATE NomConCuentas SET NClase=@pmNClase,IdCuenta=@pmIdCuenta,TipoMov=@pmTipoMov,CodNom=@pmCodNom
,CodDep=@pmCodDep,CodFon=@pmCodFon,NitTerc=@pmNitTerc,Tipo_Nom=@pmTipo_Nom
 WHERE IdConcepto=@pmIdConcepto AND  Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomGruposConv] @pmIdGrupo VARCHAR(4),@pmGrupoConv VARCHAR(150),@pmIdConv INT,@pmInactivo BIT
 AS 
 INSERT INTO NomGruposConv (IdGrupo,GrupoConv,IdConv,Inactivo)
 VALUES (@pmIdGrupo,@pmGrupoConv,@pmIdConv,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomGruposConv] @pmIdGrupo VARCHAR(4),@pmGrupoConv VARCHAR(150),@pmIdConv INT,@pmInactivo BIT
 AS 
 UPDATE NomGruposConv SET GrupoConv=@pmGrupoConv,IdConv=@pmIdConv,Inactivo=@pmInactivo
 WHERE IdGrupo=@pmIdGrupo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomGruposConv] @pmIdGrupo VARCHAR(4)
 AS 
 SELECT IdGrupo,GrupoConv,IdConv,Inactivo
 FROM NomGruposConv
 WHERE IdGrupo=@pmIdGrupo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomGruposConv] @pmIdGrupo VARCHAR(4)
 AS 
 DELETE FROM NomGruposConv WHERE IdGrupo=@pmIdGrupo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomZonasConv] @pmIdZona VARCHAR(4),@pmZonaConv VARCHAR(150),@pmInactivo BIT
 AS 
 UPDATE NomZonasConv SET ZonaConv=@pmZonaConv,Inactivo=@pmInactivo
 WHERE IdZona=@pmIdZona
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomZonasConv] @pmIdZona VARCHAR(4)
 AS 
 IF @pmIdZona IS NULL
  BEGIN
   SELECT IdZona,ZonaConv,Inactivo
   FROM NomZonasConv
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdZona,ZonaConv,Inactivo
   FROM NomZonasConv
   WHERE IdZona=@pmIdZona
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNomZonasConv] @pmIdZona VARCHAR(4)
 AS 
 DELETE FROM NomZonasConv
 WHERE IdZona=@pmIdZona
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomZonasConv] @pmIdZona VARCHAR(4),@pmZonaConv VARCHAR(150),@pmInactivo BIT
 AS 
 INSERT INTO NomZonasConv (IdZona,ZonaConv,Inactivo)
 VALUES (@pmIdZona,@pmZonaConv,@pmInactivo)
GO
--nov 13/2024
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFactMvo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFactMvo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFactMvo] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,Compania,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,CT.CCosto,tmCdSubCos,SC.SubCosto,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen
,tmIdOrigen,CO.Localidad AS CiudadOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,CD.Localidad AS CiudadDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,tmCantFalt,tmUnidadFalt
,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd
,tmCantPago,tmVrAutoIca,tmRemTransb,tmCostoAdic,tmNitMucTbdo,tmTipoNov,tmItemOrd,tmTarifIat,tmVrAviTab,tmCdTarifIat,tmTarifBom,tmVrSobBom,tmCdTarifBom,tmVrAutoIat,tmVrAutoBom
,tmEstado,tmObserv,tmTotFletes,tmTotOtros,tmTotCargos,tmTotDctos,tmTotCostoAfi,tmTotCostoRem,tmReteCree,tmTarifaCree,tmCdTarCree,tmReteIva,tmTarifaRiv,tmCdTarRiv,tmTarifaAri,tmCdTarAri,tmBaseIva,tmBaseRet,tmBaseIca,tmBaseRiv,tmVrNeto
,R.Fecha,R.FecDespacho,R.IdCliente,TC.RazonSocial,R.IdAgencia,A.Agencia AS NomAgencia,R.nRemolque,R.Modalidad,R.NumPedido,R.IdCiaPed,R.FechaPed,R.TipDcm,R.NumDocmto,R.IdCiaDcm,R.NumManif,R.IdCiaManif,R.EstCumplido,R.EstFactura,R.Observacion AS Observ
,R.CdRutaTarif,RT.Ruta AS DescRuta,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
 FROM tm_TraRemFact AS T INNER JOIN Companias AS CN ON T.tmIdCiaRem=CN.IdCia
 INNER JOIN Localidades AS CO ON T.tmIdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON T.tmIdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Trn_TraRemesa AS R ON T.tmTipRem=R.TipDoc AND T.tmRemesa=R.NumOrden AND T.tmIdCiaRem=R.IdCia
 INNER JOIN Terceros AS TC ON R.IdCliente=TC.IdTercero INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero   
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Rutas AS RT ON R.CdRutaTarif=RT.IdRuta
 LEFT JOIN CentroCosto AS CT ON T.tmCdCCosto=CT.IdCCosto LEFT JOIN SubCentros AS SC ON T.tmCdSubCos=SC.IdSubCos
 WHERE tmNumero=@pmtmNumero
GO
--dic 3
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
--dic 5
ALTER TABLE Trn_NomAjustesCom ADD Orig_Cargue VARCHAR(10)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovEst_Cp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovEst_Cp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConCuentasCla]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConCuentasCla]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovEst_Cp] @pmIdPeriodo VARCHAR(8),@pmMes VARCHAR(6),@pmIdEmpleado VARCHAR(16)=Null
AS 
SELECT ET.IdNovedad,N.IdPeriodo,N.IdEmpleado,E.Apellidos,E.Nombres,N.NContrato,N.IdConv,CV.Proyecto,ET.IdEstado,EC.EstadoConv,EC.NColor,ET.FechaDia
FROM Trn_NomNovEst AS ET INNER JOIN  Trn_NomNovConv AS N ON ET.IdNovedad=N.Id
INNER JOIN NomEstadosConv AS EC ON ET.IdEstado=EC.IdEstado
INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado 
INNER JOIN NomConvenciones AS CV ON N.IdConv=CV.Id
WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND (N.IdPeriodo=@pmIdPeriodo OR @pmIdPeriodo IS NULL)
AND (N.IdEmpleado=@pmIdEmpleado OR @pmIdEmpleado IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConCuentasCla] @pmIdConcepto VARCHAR(4),@pmNClase INT
,@pmCodNom VARCHAR(4),@pmCodDep VARCHAR(4),@pmTipo_Nom VARCHAR(10)=Null
 AS 
 SELECT IdConcepto,Item,NClase,IdCuenta,TipoMov,CodNom,CodDep,CodFon,NitTerc,Tipo_Nom
 FROM NomConCuentas
 WHERE IdConcepto=@pmIdConcepto AND NClase=@pmNClase
 AND (CodNom=@pmCodNom OR CodNom IS NULL OR LEN(CodNom)=0 )
 AND (CodDep=@pmCodDep OR CodDep IS NULL OR LEN(CodDep)=0)
 AND (ISNULL(Tipo_Nom,'')=@pmTipo_Nom OR @pmTipo_Nom IS NULL OR LEN(ISNULL(Tipo_Nom,''))=0)
 ORDER BY Item
GO