if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNERecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNERecord]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNERecordDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNERecordDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomina_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomina_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNERecordLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNERecordLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina_Cpn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina_Cpn]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomina]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomina_Cr]  @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)=Null,@pmIdEmpleado VARCHAR(16)=Null,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
 AS 
 SELECT  N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContrato,N.IdPeriodo AS IdPerdo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdDep AS CodDep,Dependencia,N.IdInstala AS CodInst,Instlacion,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcen,SubCosto,N.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,TipoLiq,N.VrSalario AS VrSalBasico,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS Nom_IdEstdo,EN.Estado AS Nom_Estado,EsRetiro,NLiqRet
,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_QR,N.KeyRegistro AS KeyRegstro,N.NE_ComAju,N.NE_CntAju,N.NE_Ajustes,N.TimeSys,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario
--datos detalle de nomina
,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS TarifaBase,DiasCalc,DiasNov,CodFondo,FD.Fondo AS Det_Fondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,ClaseCon
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes,SegFinMes,DchDotacion,DchCafeteria
,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj
,NumVeh,TipoAdmon,VehPropio,VrATransMes
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado 
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco  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 CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado  LEFT JOIN Fondos AS FD ON D.CodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON N.pVehiculo=VH.IdVehiculo
 WHERE N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')  AND N.KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%') AND N.IdNom like ISNULL(@pmIdNom,'%')
 AND  N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-1)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 ORDER BY N.KeyRegistro,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomina_Cpn]  @pmIdPeriodo VARCHAR(8),@pmMes VARCHAR(6),@pmKeyRegistro VARCHAR(30)=Null,@pmIdEmpleado VARCHAR(16)=Null
,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
 AS 
 SELECT  N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContrato,N.IdPeriodo AS IdPerdo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdDep AS CodDep,Dependencia,N.IdInstala AS CodInst,Instlacion,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcen,SubCosto,N.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,TipoLiq,N.VrSalario AS VrSalBasico,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS Nom_IdEstdo,EN.Estado AS Nom_Estado,EsRetiro,NLiqRet
,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_QR,N.KeyRegistro AS KeyRegstro,N.NE_ComAju,N.NE_CntAju,N.NE_Ajustes,N.TimeSys,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario
--datos detalle de nomina
,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS TarifaBase,DiasCalc,DiasNov,CodFondo,FD.Fondo AS Det_Fondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,ClaseCon
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes,SegFinMes,DchDotacion,DchCafeteria
,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj
,NumVeh,TipoAdmon,VehPropio,VrATransMes
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado 
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco  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 CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado  LEFT JOIN Fondos AS FD ON D.CodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON N.pVehiculo=VH.IdVehiculo
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND ClaseLiq IN ('NOMINA','NOVEDAD','LIQUDACION')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')  AND N.KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%') 
 AND N.IdNom like ISNULL(@pmIdNom,'%') AND  N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-1)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 ORDER BY N.KeyRegistro,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNERecordDet] @pmTipoReg VARCHAR(3),@pmNumRegistro INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(250),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY,@pmVrTotDed MONEY,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY
,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16),@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTipoIncLab VARCHAR(20)
 AS 
 INSERT INTO Trn_NERecordDet (TipoReg,NumRegistro,IdCia,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab)
 VALUES (@pmTipoReg,@pmNumRegistro,@pmIdCia,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantDevg,@pmCantDed,@pmVrUnitario,@pmVrTotDevg,@pmVrTotDed,@pmUnidad,@pmClaseLiq,@pmVrOrigen,@pmTarifa,@pmDiasCalc,@pmDiasNov,@pmCodFondo,@pmNPrestamo,@pmNCuota,@pmOrigCargue,@pmVrBaseLiq,@pmVrBasExceso,@pmNitTercero,@pmHoraInicial,@pmHoraFinal,@pmTipoIncLab)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNERecordLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
AS 
SELECT N.TipoReg,N.NumRegistro,N.IdCia,N.Fecha,N.IdEmpleado,E.Apellidos,E.Nombres,N.NContrato,N.IdPeriodo,N.FecInicial,N.FecFinal
,N.Cantidad,N.CantNoRem,N.VrDevengado,N.VrDeducido,N.IdDep,Dependencia,N.IdInstala,IST.Instlacion,N.IdCCosto,CCosto,N.IdSubCos,SubCosto
,N.IdNom,TipoNomina,N.TipoLiq,N.VrSalario,N.VrATransMes,N.EsRetiro,N.NLiqRet,N.pVehiculo,N.FecIniPmd,N.FecFinPmd,N.TipCom,N.Comprobante,N.IdCiaCom
,N.CdTipoCot,N.SubTipo_Cot,N.Sal_Integral,N.TipoIdent,N.NotasAportante,N.Observacion,N.IdEstado,EN.Estado AS Nom_Estado,N.FechaCrea,N.IdUsuario,Usuario
,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_CntAju,N.NE_Ajustes,N.IdRegNom
--,N.DiasVac,N.NPeriodos,N.CantRem,N.VrRemndo,N.EsParcial,N.NumLiquida,N.NumSemLiq
,D.Item,D.IdConcepto,CN.Concepto,CN.ClaseCon,D.Detalle,D.CantDevg,D.VrUnitario,D.VrTotDevg,D.CantDed,D.VrTotDed,D.Unidad,D.ClaseLiq,D.VrOrigen,D.Tarifa,D.DiasCalc,D.DiasNov
,D.CodFondo,F.Fondo,D.NPrestamo,D.NCuota,D.OrigCargue,D.VrBaseLiq,D.VrBasExceso,D.NitTercero,T.RazonSocial,D.HoraInicial,D.HoraFinal,D.TipoIncLab
--datos del contrato
,E.Codigo,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,C.IdCargo,Cargo,C.IdTipCon,TipoContrato,C.FecIngreso,C.FecRetiro,C.Indefinido
,C.FPnomina,C.ATFinMes,C.SegFinMes,C.IdEstado AS Con_IdEstado,ECO.Estado AS Con_Estado,C.Inactivo AS Con_Inactivo
FROM Trn_NERecord AS N INNER JOIN Trn_NERecordDet AS D ON N.TipoReg=D.TipoReg AND N.NumRegistro=D.NumRegistro AND N.IdCia=D.IdCia
INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom 
INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep INNER JOIN Instalaciones AS IST ON N.IdInstala=IST.IdInstala
INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado
INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado
INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
LEFT JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN Fondos AS F ON D.CodFondo=F.IdFondo
WHERE N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (N.IdPeriodo=@pmIdPeriodo OR @pmIdPeriodo IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNominaDso] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdNom VARCHAR(4)=Null
 AS 
 SELECT KeyRegistro,N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,IdPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,N.NContrato AS NumContrato,N.IdNom AS CodNom,TipoNomina
,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala
,N.VrSalario,N.pVehiculo AS PlacaVeh,N.TipCom,N.Comprobante,N.IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,Estado,N.EsRetiro,N.NLiqRet,N.TipoLiq
,N.VrATransMes,N.CdTipoCot,N.SubTipo_Cot,N.Sal_Integral,N.TipoIdent,N.NotasAportante,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_QR,N.NE_ComAju,N.NE_CntAju,N.NE_Ajustes
,N.IdUsuario AS IdUsuari,Usuario,N.TimeSys,N.FecUpdate AS FecUpdte
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN Dependencias AS D ON N.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 WHERE IdPeriodo=@pmIdPeriodo AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 ORDER BY  N.IdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomina_Sel]  @pmIdEmpleado  VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_Nomina (IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,VrDevengado,VrDeducido,NContrato,IdDep
,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes)
 SELECT  IdPeriodo,@pmNewCedula +CAST(NContrato AS VARCHAR(10)) +CASE EsRetiro WHEN 1 THEN '_1' ELSE '_0'  END,@pmNewCedula,FecInicial,FecFinal,Cantidad,VrDevengado
,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem
,TimeSys,IdUsuario,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes
 FROM Trn_Nomina 
 WHERE IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmNContrato INT,@pmIdDep VARCHAR(4)
,@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmEsRetiro BIT,@pmNLiqRet INT
,@pmTipoLiq CHAR(1),@pmCantNoRem DECIMAL(10,4),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmCantModifica INT,@pmNE_Estado INT,@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_FecEnvio DATETIME
,@pmNE_CUNE VARCHAR(500),@pmNE_ComAju BIT,@pmNE_CntAju INT,@pmNE_Ajustes VARCHAR(500),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmFecUpdate SMALLDATETIME
 AS 
 INSERT INTO Trn_Nomina (IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,VrDevengado,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,FecUpdate,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes)
 VALUES (@pmIdPeriodo,@pmKeyRegistro,@pmIdEmpleado,@pmFecInicial,@pmFecFinal,@pmCantidad,@pmVrDevengado,@pmVrDeducido,@pmNContrato,@pmIdDep,@pmIdInstala,@pmIdCCosto,@pmIdSubCos,@pmIdNom,@pmVrSalario,@pmVrATransMes,@pmpVehiculo,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado
,@pmEsRetiro,@pmNLiqRet,@pmTipoLiq,@pmCantNoRem,@pmTimeSys,@pmFecUpdate,@pmIdUsuario,@pmCdTipoCot,@pmSubTipo_Cot,@pmSal_Integral,@pmTipoIdent,@pmNotasAportante,@pmCantModifica,@pmNE_Estado,@pmNE_Prefijo,@pmNE_Numero,@pmNE_FecEnvio,@pmNE_CUNE,Null,@pmNE_ComAju,@pmNE_CntAju,@pmNE_Ajustes)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmNContrato INT,@pmIdDep VARCHAR(4)
,@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmEsRetiro BIT,@pmNLiqRet INT,@pmTipoLiq CHAR(1),@pmCantNoRem DECIMAL(10,4),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmCantModifica INT,@pmNE_Estado INT,@pmNE_Prefijo VARCHAR(20)
,@pmNE_Numero INT,@pmNE_FecEnvio DATETIME,@pmNE_CUNE VARCHAR(500),@pmNE_ComAju BIT,@pmNE_CntAju INT,@pmNE_Ajustes VARCHAR(500),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Nomina SET IdEmpleado=@pmIdEmpleado,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,Cantidad=@pmCantidad,VrDevengado=@pmVrDevengado,VrDeducido=@pmVrDeducido,NContrato=@pmNContrato,IdDep=@pmIdDep,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdNom=@pmIdNom
,VrSalario=@pmVrSalario,VrATransMes=@pmVrATransMes,pVehiculo=@pmpVehiculo,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EsRetiro=@pmEsRetiro,FecUpdate=@pmFecUpdate,NLiqRet=@pmNLiqRet,TipoLiq=@pmTipoLiq,CantNoRem=@pmCantNoRem
,CdTipoCot=@pmCdTipoCot,SubTipo_Cot=@pmSubTipo_Cot,Sal_Integral=@pmSal_Integral,TipoIdent=@pmTipoIdent,NotasAportante=@pmNotasAportante,CantModifica=@pmCantModifica,NE_Estado=@pmNE_Estado,NE_Prefijo=@pmNE_Prefijo,NE_Numero=@pmNE_Numero,NE_FecEnvio=@pmNE_FecEnvio,NE_CUNE=@pmNE_CUNE
,NE_ComAju=@pmNE_ComAju,NE_CntAju=@pmNE_CntAju,NE_Ajustes=@pmNE_Ajustes
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)
 AS 
 SELECT IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom
,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,TimeSys,FecUpdate,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes
 FROM Trn_Nomina
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%')
 ORDER BY KeyRegistro
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNERecord] @pmTipoReg VARCHAR(3),@pmNumRegistro INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdRegNom VARCHAR(30),@pmNumLiquida INT,@pmNumSemLiq INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmCantNoRem DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmIdDep VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmFecIniPmd SMALLDATETIME,@pmFecFinPmd SMALLDATETIME
,@pmDiasVac INT,@pmNPeriodos INT,@pmCantRem INT,@pmVrRemndo MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmEsRetiro BIT,@pmNLiqRet INT,@pmTipoLiq CHAR(1),@pmEsParcial BIT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmCantModifica INT,@pmNE_Estado INT,@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_FecEnvio SMALLDATETIME,@pmNE_CUNE VARCHAR(500),@pmNE_CntAju INT,@pmNE_Ajustes VARCHAR(500)
 AS 
 INSERT INTO Trn_NERecord (TipoReg,NumRegistro,IdCia,Fecha,IdPeriodo,IdEmpleado,NContrato,IdRegNom,NumLiquida,NumSemLiq,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,FecIniPmd,FecFinPmd,DiasVac,NPeriodos,CantRem,VrRemndo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,EsParcial,FechaCrea,IdUsuario,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_CntAju,NE_Ajustes)
 VALUES (@pmTipoReg,@pmNumRegistro,@pmIdCia,@pmFecha,@pmIdPeriodo,@pmIdEmpleado,@pmNContrato,@pmIdRegNom,@pmNumLiquida,@pmNumSemLiq,@pmFecInicial,@pmFecFinal,@pmCantidad,@pmCantNoRem,@pmVrDevengado,@pmVrDeducido,@pmIdDep,@pmIdInstala,@pmIdCCosto,@pmIdSubCos,@pmIdNom,@pmVrSalario,@pmVrATransMes,@pmpVehiculo,@pmFecIniPmd,@pmFecFinPmd,@pmDiasVac,@pmNPeriodos,@pmCantRem,@pmVrRemndo,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmEsRetiro,@pmNLiqRet,@pmTipoLiq,@pmEsParcial,@pmFechaCrea,@pmIdUsuario,@pmCdTipoCot,@pmSubTipo_Cot,@pmSal_Integral,@pmTipoIdent,@pmNotasAportante,@pmCantModifica,@pmNE_Estado,@pmNE_Prefijo,@pmNE_Numero,@pmNE_FecEnvio,@pmNE_CUNE,@pmNE_CntAju,@pmNE_Ajustes)
GO