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].[paInsNomAutoliq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsNomAutoliq]
GO

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Vac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_Nomina_Vac]
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_ContratoAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryEmp_ContratoAnt]
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_ContratoEca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryEmp_ContratoEca]
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].[paQryNomAutoliq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNomAutoliq]
GO

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

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

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

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

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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NominaVac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_NominaVac]
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].[paUpNomAutoliq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpNomAutoliq]
GO

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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),@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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNomAutoliq @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmFecha SMALLDATETIME,@pmPerAutoliq VARCHAR(6),@pmPerAutoSal VARCHAR(6),@pmFechaPago SMALLDATETIME,@pmIdAportnte VARCHAR(16),@pmPresentacion CHAR(1),@pmCodCia CHAR(2),@pmCodFondo VARCHAR(8),@pmCodArp VARCHAR(8),@pmCodOper VARCHAR(10),@pmEsCorr BIT,@pmPerCorr VARCHAR(6)
,@pmNumCorr VARCHAR(20),@pmTotalEmp INT,@pmVrNomina MONEY,@pmVrPension MONEY,@pmVrSalud MONEY,@pmVrRiesgos MONEY,@pmVrCajas MONEY,@pmVrSena MONEY,@pmVrIcbf MONEY,@pmVrEsap MONEY,@pmVrMinEduc MONEY,@pmTotalAfipen INT,@pmTotalAfiSal INT,@pmTotalAfiRie INT,@pmTotalAfiCaj INT,@pmTotalAfiSen INT,@pmTotalAfiIcb INT,@pmTotalAfiEsa INT,@pmTotalAfiEduc INT,@pmTotAdmPen INT
,@pmTotAdmSal INT,@pmTotAdmRie INT,@pmTotAdmCom INT,@pmObservacion VARCHAR(250),@pmLogAdmon VARCHAR(250),@pmRedoMiles BIT,@pmAnulado BIT,@pmIdEstado VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmTipoPlanilla VARCHAR(3)
 AS 
 INSERT INTO Trn_NomAutoliq (TipoLiq,Numero,Fecha,PerAutoliq,PerAutoSal,FechaPago,IdAportnte,Presentacion,CodCia,CodFondo,CodArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrIcbf,VrEsap,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,Observacion,LogAdmon,RedoMiles,Anulado,IdEstado,TimeSys,IdUsuario,TipoPlanilla)
 VALUES (@pmTipoLiq,@pmNumero,@pmFecha,@pmPerAutoliq,@pmPerAutoSal,@pmFechaPago,@pmIdAportnte,@pmPresentacion,@pmCodCia,@pmCodFondo,@pmCodArp,@pmCodOper,@pmEsCorr,@pmPerCorr,@pmNumCorr,@pmTotalEmp,@pmVrNomina,@pmVrPension,@pmVrSalud,@pmVrRiesgos,@pmVrCajas,@pmVrSena,@pmVrIcbf,@pmVrEsap,@pmVrMinEduc,@pmTotalAfipen,@pmTotalAfiSal,@pmTotalAfiRie,@pmTotalAfiCaj,@pmTotalAfiSen,@pmTotalAfiIcb
,@pmTotalAfiEsa,@pmTotalAfiEduc,@pmTotAdmPen,@pmTotAdmSal,@pmTotAdmRie,@pmTotAdmCom,@pmObservacion,@pmLogAdmon,@pmRedoMiles,@pmAnulado,@pmIdEstado,@pmTimeSys,@pmIdUsuario,@pmTipoPlanilla)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNomFijos @pmIdEmpleado VARCHAR(16),@pmnAnno INT,@pmnMes INT,@pmTarifa DECIMAL(10,4),@pmVrBase MONEY,@pmVrSalarios MONEY,@pmVrPrima MONEY,@pmVrVacacnes MONEY,@pmVrSIntExcto MONEY
,@pmVrOtrosIng MONEY,@pmVrIngresos MONEY,@pmVrPensiones MONEY,@pmVrDedcido MONEY,@pmVrExcento MONEY,@pmVrCertEdu MONEY,@pmVrCertVda MONEY,@pmVrCertAFC MONEY,@pmVrCertfcado MONEY,@pmPeriodos DECIMAL(10,4),@pmVrTotalBase MONEY
,@pmOrignPorc VARCHAR(10),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmVrSalud MONEY
 AS 
 INSERT INTO Trn_NomFijos (IdEmpleado,nAnno,nMes,Tarifa,VrBase,VrSalarios,VrPrima,VrVacacnes,VrSIntExcto,VrOtrosIng,VrIngresos,VrPensiones,VrDedcido,VrExcento,VrCertEdu,VrCertVda,VrCertAFC,VrCertfcado,Periodos,VrTotalBase,OrignPorc,FecAdd,IdUsuario,VrSalud)
 VALUES (@pmIdEmpleado,@pmnAnno,@pmnMes,@pmTarifa,@pmVrBase,@pmVrSalarios,@pmVrPrima,@pmVrVacacnes,@pmVrSIntExcto,@pmVrOtrosIng,@pmVrIngresos,@pmVrPensiones,@pmVrDedcido,@pmVrExcento,@pmVrCertEdu,@pmVrCertVda,@pmVrCertAFC
,@pmVrCertfcado,@pmPeriodos,@pmVrTotalBase,@pmOrignPorc,@pmFecAdd,@pmIdUsuario,@pmVrSalud)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNomVacDet @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad INT,@pmVrDevgado MONEY,@pmVrDeducido MONEY
,@pmTarifa DECIMAL(10,4),@pmVrBaseLiq MONEY,@pmVrCertfcado MONEY,@pmVrPension MONEY,@pmNPrestamo INT,@pmNCuota INT,@pmIdPeriodo VARCHAR(8),@pmVrSalud MONEY
 AS 
 INSERT INTO Trn_NomVacDet (IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud)
 VALUES (@pmIdEmpleado,@pmNumero,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrDevgado,@pmVrDeducido,@pmTarifa,@pmVrBaseLiq,@pmVrCertfcado,@pmVrPension,@pmNPrestamo,@pmNCuota,@pmIdPeriodo,@pmVrSalud)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNomVacDet_Sel @pmIdEmpleado VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_NomVacDet (IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud)
 SELECT @pmNewCedula,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud
 FROM  Trn_NomVacDet 
 WHERE IdEmpleado=@pmIdEmpleado
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Nomina @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4),@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY
,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4),@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME
,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8),@pmtmFecUltLiq SMALLDATETIME,@pmtmFecIniPmd SMALLDATETIME,@pmtmFecFinPmd SMALLDATETIME,@pmtmLogPmdio VARCHAR(600),@pmtmVrPension MONEY,@pmtmVrSalud MONEY
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud)
 VALUES (@pmtmNumero,@pmtmIdEmpleado,@pmtmItem,@pmtmIdConcepto,@pmtmClaseCon,@pmtmDetalle,@pmtmCantDevg,@pmtmCantDed,@pmtmVrUnitario,@pmtmVrTotDevg,@pmtmVrTotDed,@pmtmUnidad,@pmtmClaseLiq,@pmtmVrOrigen,@pmtmTarifa
,@pmtmDiasCalc,@pmtmDiasNov,@pmtmCodFondo,@pmtmNPrestamo,@pmtmNCuota,@pmtmOrigCargue,@pmtmVrBaseLiq,@pmtmNContto,@pmtmFecIni,@pmtmFecFin,@pmtmIdPeriodo,@pmtmFecUltLiq,@pmtmFecIniPmd,@pmtmFecFinPmd,@pmtmLogPmdio,@pmtmVrPension,@pmtmVrSalud)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Nomina_Liq  @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmFecUltLiq
,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud)
 SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle
,'CntDev'= CASE  WHEN VrDeducido<=0 THEN Cantidad ELSE 0 END
,'CntDed'= CASE  WHEN VrDeducido>0 THEN Cantidad ELSE 0  END
,VrUnitario,VrDevngado,VrDeducido,Unidad,ClaseLiq,VrBase,D.Tarifa,DiasBase,D.DiasNoRem,CodFondo
,NPrestamo,NCuota,'LIQUDACION',VrBaseLiq,NContrato,FecDesde,FecHasta,IdPeriodo,FecUltLiq,FecIniPmd,FecFinPmd,LogPmdio,0,0
 FROM Trn_NomRetDet AS D INNER JOIN Trn_NomRet AS R ON D.IdEmpleado=R.IdEmpleado AND D.Numero=R.Numero
 INNER JOIN  NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE D.IdEmpleado=@pmIdEmpleado AND D.Numero=@pmNumero  AND  ClaseLiq NOT IN  ('APORTE','PROVISION')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Nomina_Sel @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud)
 SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad,ClaseLiq,VrOrigen,D.Tarifa,DiasCalc
,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,NContrato,FecInicial,FecFinal,D.IdPeriodo,0,0
 FROM Trn_NomDetalle  AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdPeriodo=@pmIdPeriodo AND D.KeyRegistro=@pmKeyRegistro 
 AND  ClaseLiq  IN  ('NOMINA','NOVEDAD')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Nomina_Vac  @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud)
 SELECT @pmtmNumero,Item,IdEmpleado,D.IdConcepto,ClaseCon,Detalle,0,Cantidad,0,VrDevgado,VrDeducido,Unidad,'NOMINA',VrBaseLiq,D.Tarifa,0,0,'',NPrestamo,NCuota,'VAC',VrCertfcado
,0,getdate(),getdate(),IdPeriodo,VrPension,VrSalud
 FROM Trn_NomVacDet  AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,FecAdd,FecUpdate,IdUsuario
 FROM Emp_Contrato
 WHERE NContrato=@pmNContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryEmp_ContratoAnt @pmIdEmpleado VARCHAR(16),@pmNContratoExc INT
 AS 
 SELECT NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdSubCos,IdCia,IdNom
,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,FecAdd,FecUpdate,IdUsuario
 FROM Emp_Contrato
 WHERE IdEmpleado=@pmIdEmpleado AND NContrato<>@pmNContratoExc
 ORDER BY FecIngreso,NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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 CHAR(2)=Null
,@pmTallaPan CHAR(2)=Null,@pmTallaZap CHAR(2)=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,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,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
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryEmp_ContratoEca @pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdProf VARCHAR(4)=Null,@pmSexo CHAR(1)=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null
AS 
 SELECT C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,TipoId,Codigo,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,ET.NColor AS EmpEstcolor,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario
--datos del contrato
,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,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,DchDotacion,DchCafeteria,Reclmto,Notas,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.IdUsuario AS IdUsuari,U.Usuario AS Con_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
 WHERE   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.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND Sexo LIKE ISNULL(@pmSexo,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,Jornada,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,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac
,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,C.Inactivo AS Inactvo,IdCot,C.IdGrupo AS GrupAR,VrUpc,SubTipoCot
--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
,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
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomAutoliq @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20)
 AS 
 SELECT TipoLiq,Numero,Fecha,PerAutoliq,PerAutoSal,FechaPago,IdAportnte,Presentacion,CodCia,CodFondo,CodArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrIcbf,VrEsap,VrMinEduc,TotalAfipen,TotalAfiSal
,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,Observacion,LogAdmon,RedoMiles,Anulado,IdEstado,TimeSys,FecUpdate,IdUsuario,TipoPlanilla
 FROM Trn_NomAutoliq
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomAutoliq_Cr @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmPerAutoliq VARCHAR(6)=Null
AS
 SELECT  A.TipoLiq AS TipoPla,A.Numero AS NumPla,Fecha,PerAutoliq,PerAutoSal,FechaPago,A.IdAportnte AS NitAportnte,NA.RazonSocial AS NomAportante,Presentacion,A.CodCia AS CodSucur,Compania,CodSucsal,DireccSuc,TelefSuc,CI.IdLocal AS CodCiuSuc
,LC.Localidad AS CiudadSuc,LC.IdDep AS CodDepSuc,CodFondo,F.Fondo AS NomFondo,CodArp,FRP.Fondo AS FondoArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,A.VrPension AS Total_Pen,A.VrSalud AS Total_Sal,A.VrRiesgos AS Total_Rie,A.VrCajas AS Total_Caj
,A.VrSena AS Total_Sen,A.VrIcbf AS Total_ICB,A.VrEsap AS Total_ESA,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,A.Observacion AS Observ,LogAdmon,RedoMiles
,Anulado,A.IdEstado AS IdEstdo,Estado,TimeSys,A.FecUpdate AS Fec_Update,A.IdUsuario AS IdUsuari,Usuario
--datos del aportante
,NA.TipoId AS Apo_TipID,DvNit,IdRepLegal,RLG.RazonSocial AS NomRepLegal,RLG.TipoId AS RLegTID,RLG.Dv AS RLegDV,NA.Direccion AS Apo_dirccion,NA.IdLocal AS Apo_CodCiu,LA.Localidad AS Apo_NomCiu,LA.IdDep AS Apo_CodDep,Departamento,NA.Telefono AS Apo_tel,NA.Fax AS Apo_Fax,NA.e_mail AS Apo_email
,ClaseApo,TipoEco,TipoPersna,CIIUDane,FecIniCndato,TipAccCndato,FecFinCndato,CodOperInfo,TipoApo
--datos del detalle
,D.Item AS DetItem,D.IdEmpleado AS IdEmpldo,D.NContrato AS NumContrato,Apellido1,Apellido2,Nombre1,Nombre2,D.IdFonPen AS IdFdoPen,FP.Fondo AS FondoPen,TFP.TipoId AS Pen_TipID,FP.IdTercero AS Pen_Nit,TFP.Dv AS Pen_DV,TFP.RazonSocial AS Pen_Nombre
,IdFonSal,FS.Fondo AS FondoSal,TFS.TipoId AS Sal_TipID,FS.IdTercero AS Sal_Nit,TFS.Dv AS Sal_Dv,TFS.RazonSocial AS Sal_Nombre,IdFonRie,FR.Fondo AS FondoRie,TFR.TipoId AS Rie_TipID,FR.IdTercero AS Rie_nit,TFR.Dv AS Rie_Dv,TFR.RazonSocial AS Rie_nombre
,IdCajCom,FC.Fondo AS FondoCc,TFC.TipoId AS CC_TipId,FC.IdTercero AS CC_Nit,TFC.Dv AS CC_DV,TFC.RazonSocial AS cc_Nombre,SalBasico,VrIBCPen,DiasPen,TarifPen,D.VrPension AS VlrPension,VrApoFsp,VrApoFsu,VrApoVolAfi,VrApoVolApt,VrNoReten,VrIBCSal,DiasSal,TarifSal,D.VrSalud AS VlrSalud,D.VrUpc AS VlrUpc
,VrIncap,VrLicencia,NumAutInc,NumAutLic,VrIBCRie,DiasRie,TarifRie,D.VrRiesgos AS VlrRiesgos,VrIncIrp,NumAutIrp,D.IdInstala AS CodInstla,I.Instlacion AS CentroTrabaj,I.CodCiu AS CiuInstala
,VrIBCRef,VrIBCCajas,DiasCom,TarifCom,D.VrCajas AS VlrCajas,TarifSen,D.VrSena AS VlrSena,TarifIcb,D.VrICBF AS Vlricbf,TarifEsap,D.VrESAP AS VlrESAP,TarifEdu,VrMinEdu,TarifFsp,TarifFsu
--datos de novedades
,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp,NovVct,NovIrp,CodNewPen,FNP.Fondo AS NewFonPen,CodNewSal,FNS.Fondo AS NewFonEPS,CodNewIns,NI.Instlacion AS NewCentroTrabj,CodNewRie,CRN.Tarifa AS New_TarRie,CodNewGru
,NGR.TarfaTrab AS NewTarfTrab,NGR.TarfaEmp AS NewTarfEmp,DiasNov
--datos del empleado
,Apellidos,Nombres,E.Codigo AS EmpCodigo,E.TipoId AS Emp_TipID,E.Dv AS Emp_Dv,E.IdLugarCed AS Emp_IdLugrCed,L.Localidad AS Lugar_Ced,L.IdDep AS Emp_CodDep,E.Direccion AS Dirccion,E.Telefono AS Emp_Telfono,E.TelMovil AS Emp_TelMovil,E.e_mail AS Emp_Email,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,NumCuenta
,E.IdBanco AS CodBanco,EsCondtor,pVehiculo,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,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.IdDep AS CodDepe,Dependencia,C.IdCCosto AS CodCcto,CCosto,C.IdSubCos AS CodScost,SubCosto,C.IdNom AS CodTipNom,TipoNomina,C.IdClase AS CodClaRie,CR.ClaseRiesgo AS ClasRiesgo
,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,C.VrUpc AS Con_VrUpc,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg,C.IdCot AS CodCotz,Cotizante,C.IdGrupo AS CodGrup,GAR.TarfaTrab AS Con_TrfaTrab,GAR.TarfaEmp AS TrfaEmp,TipoTrabj
,ATFinMes,SegFinMes,C.IdEstado AS Con_IdEstdo,C.Inactivo AS Con_inactivo,IdJornada,DenyPEN,DenyEPS,DenyARP,DenyCaj,D.CdFonAvp AS CodFondoAvp,E.CdFonAvp AS Emp_FondoAVP,TipoPlanilla,SubTipoCot
 FROM Trn_NomAutoliq AS A INNER JOIN Trn_NomAlqDet AS D ON A.TipoLiq=D.TipoLiq AND A.Numero=D.Numero 
 INNER JOIN  NomAportante AS NA ON A.IdAportnte=NA.IdAportnte  INNER JOIN Terceros AS RLG ON NA.IdRepLegal=RLG.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario INNER JOIN Localidades AS LA ON NA.IdLocal=LA.IdLocal
 INNER JOIN Departamentos AS DE ON LA.IdDep=DE.IdDep
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Fondos AS FP ON D.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON D.IdFonSal=FS.IdFondo INNER JOIN Fondos AS FR ON D.IdFonRie=FR.IdFondo
 INNER JOIN Fondos  AS FC ON D.IdCajCom=FC.IdFondo INNER JOIN Terceros AS TFP ON FP.IdTercero=TFP.IdTercero
 INNER JOIN Terceros AS TFS ON FS.IdTercero=TFS.IdTercero  INNER JOIN Terceros AS TFR ON FR.IdTercero=TFR.IdTercero
 INNER JOIN Terceros AS TFC ON FC.IdTercero=TFC.IdTercero  INNER JOIN Instalaciones AS I ON D.IdInstala=I.IdInstala
 INNER JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato  INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep 
 INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom  INNER JOIN TiposCot AS TCZ ON C.IdCot=TCZ.IdCot
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf  INNER JOIN EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 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 
 LEFT JOIN GruposAR AS GAR ON C.IdGrupo=GAR.IdGrupo
 LEFT JOIN Trn_NomAlqNov AS DN ON D.TipoLiq=DN.TipoLiq AND D.Numero=DN.Numero AND D.Item=DN.Item
 LEFT JOIN Fondos AS F ON A.CodFondo=F.IdFondo LEFT JOIN Fondos AS FRP ON A.CodArp=FRP.IdFondo
 LEFT JOIN Companias AS CI ON A.CodCia=CI.IdCia LEFT JOIN Localidades AS LC ON CI.IdLocal=LC.IdLocal
 LEFT JOIN Fondos AS FNP ON DN.CodNewPen=FNP.IdFondo  LEFT JOIN Fondos AS FNS ON DN.CodNewSal=FNS.IdFondo
 LEFT JOIN Instalaciones AS NI ON DN.CodNewIns=NI.IdInstala LEFT JOIN ClaseRie AS CRN ON DN.CodNewRie=CRN.IdClase
 LEFT JOIN GruposAR AS NGR ON DN.CodNewGru=NGR.IdGrupo
 WHERE A.TipoLiq=@pmTipoLiq AND A.Numero LIKE ISNULL(@pmNumero,'%') AND PerAutoliq LIKE ISNULL(@pmPerAutoliq,'%')
 ORDER BY A.Numero,Apellidos,Nombres
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomAutoliqDso @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipoLiq VARCHAR(3)=Null,@pmNumero VARCHAR(20)=Null
,@pmPerAutoliq VARCHAR(6)=Null,@pmCodFondo VARCHAR(8)=Null,@pmEsCorr BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmPresentacion CHAR(1)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipoLiq,Numero,Fecha,PerAutoliq,PerAutoSal,FechaPago,A.IdAportnte AS NitAportnte,RazonSocial,Presentacion,CodCia,CodFondo,F.Fondo AS NomFondo,CodArp,FR.Fondo AS FondoArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,VrPension,VrSalud,VrRiesgos
,VrCajas,VrSena,VrIcbf,VrEsap,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,Observacion
,LogAdmon,RedoMiles,Anulado,A.IdEstado AS IdEstdo,Estado,TipoPlanilla,TimeSys,A.FecUpdate AS Fec_Updte,A.IdUsuario AS IdUsuari,Usuario
 FROM Trn_NomAutoliq AS A INNER JOIN NomAportante AS NA ON A.IdAportnte=NA.IdAportnte
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 LEFT JOIN Fondos AS F ON A.CodFondo=F.IdFondo LEFT JOIN Fondos AS FR ON A.CodArp=FR.IdFondo
 WHERE TipoLiq LIKE ISNULL(@pmTipoLiq,'%') AND Numero LIKE ISNULL(@pmNumero,'%') AND PerAutoliq LIKE ISNULL(@pmPerAutoliq,'%')
 AND CodFondo LIKE ISNULL(@pmCodFondo,'%') AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND Presentacion LIKE ISNULL(@pmPresentacion,'%')
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (EsCorr=ISNULL(@pmEsCorr,0) or EsCorr=ISNULL(@pmEsCorr,1))
 ORDER BY TipoLiq,Numero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomFijos @pmIdEmpleado VARCHAR(16),@pmnAnno INT,@pmnMes INT
 AS 
 SELECT IdEmpleado,nAnno,nMes,Tarifa,VrBase,VrSalarios,VrPrima,VrVacacnes,VrSIntExcto,VrOtrosIng,VrIngresos,VrPensiones,VrSalud,VrDedcido,VrExcento,VrCertEdu,VrCertVda
,VrCertAFC,VrCertfcado,VrTotalBase,Periodos,OrignPorc,FecAdd,FecUpdate,IdUsuario
 FROM Trn_NomFijos
 WHERE IdEmpleado=@pmIdEmpleado AND nAnno=@pmnAnno AND nMes=@pmnMes
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomFijosLta @pmIdEmpleado VARCHAR(16)=Null,@pmnAnno INT=Null,@pmnMes INT=Null
 AS 
 SELECT F.IdEmpleado AS IdEmpledo,Apellidos,Nombres,nAnno,nMes,Tarifa,VrBase,VrSalarios,VrPrima,VrVacacnes,VrSIntExcto,VrOtrosIng,VrIngresos,VrPensiones,VrSalud,VrDedcido,VrExcento
,VrCertEdu,VrCertVda,VrCertAFC,VrCertfcado,VrTotalBase,Periodos,OrignPorc,Codigo,NContrato,FecUltIng,F.IdUsuario AS IdUsuari,Usuario,FecAdd,FecUpdate
 FROM Trn_NomFijos AS F INNER JOIN Empleados AS E ON F.IdEmpleado=E.IdEmpleado
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 WHERE F.IdEmpleado like ISNULL(@pmIdEmpleado,'%')
 AND (nAnno>=ISNULL(@pmnAnno,0)  AND nAnno<=ISNULL(@pmnAnno,2147483647))
 AND (nMes>=ISNULL(@pmnMes,0)  AND nMes<=ISNULL(@pmnMes,2147483647))
 ORDER BY Apellidos,Nombres,nAnno,nMes
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVac_Cr @pmIdEmpleado VARCHAR(16),@pmNumero INT
 AS 
 SELECT V.IdEmpleado AS IdEmpldo,Apellidos,Nombres,V.Numero AS NumReg,Fecha,V.NContrato AS NumContrato,FecInicial,FecFinal,V.Cantidad AS DiasCal,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,DiasVac
,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,V.VrSalario AS Vac_VrBasico,V.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,V.Observacion AS Observ,LogPmdio,Anulado,V.IdEstado AS Vac_IdEstdo,ED.Estado AS Vac_Estado
,TimeSys,V.FecUpdate AS FecUpd,V.IdUsuario AS IdUsuari,Usuario
--datos del detalle de liquidacion
,Item,D.IdConcepto AS CodConcepto,Concepto,ClaseCon,Detalle,D.Cantidad AS Cant,Unidad,VrDevgado,VrDeducido,D.Tarifa AS Tarfa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo
--datos del 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.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdCCosto AS CodCcto,CCosto
,C.IdSubCos AS CodSubcen,SubCosto,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,Asistencia,NoDevenga,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS SalBasico,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
,NumVeh,TipoAdmon,VehPropio,VrSalud
 FROM Trn_NomVac  AS V INNER JOIN  Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario  INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado
 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 Dependencias AS DP ON C.IdDep=DP.IdDep
 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 TiposNom AS TN ON C.IdNom=TN.IdNom 
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 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
 LEFT JOIN  Trn_NomVacDet  AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero  LEFT JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 LEFT JOIN TiposCom AS TM ON V.TipCom=TM.IdCom
 LEFT JOIN NomVehiculos AS VH ON V.pVehiculo=VH.IdVehiculo
 WHERE  V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (V.Numero>=ISNULL(@pmNumero,0)  AND V.Numero<=ISNULL(@pmNumero,2147483647))
ORDER BY   V.Numero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVacDet @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmItem INT
 AS 
 SELECT IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension
,VrSalud,NPrestamo,NCuota,IdPeriodo
 FROM Trn_NomVacDet
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVacDetCon @pmIdEmpleado VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmMes VARCHAR(6)
,@pmIdPeriodo VARCHAR(8)=Null,@pmNContrato INT=Null
 AS 
 SELECT SUM(VD.Cantidad) AS SCANT,SUM(VrDevgado) AS SDEVNG,SUM(VrDeducido) AS STOTDED,SUM(VrBaseLiq) AS STOTBAS
,SUM(VrCertfcado) AS STOTCER,SUM(VrPension) AS STOTPEN,SUM(VrSalud) AS STOTSALUD
 FROM Trn_NomVacDet AS VD INNER JOIN Trn_NomVac AS V ON VD.IdEmpleado=V.IdEmpleado AND VD.Numero=V.Numero
 WHERE VD.IdEmpleado=@pmIdEmpleado AND  IdConcepto=@pmIdConcepto AND Anulado=0 AND SUBSTRING(IdPeriodo,1,6)=@pmMes
 AND  IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') 
 AND (NContrato>=ISNULL(@pmNContrato,0)  AND NContrato<=ISNULL(@pmNContrato,2147483647))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVacDet_Cr @pmIdEmpleado VARCHAR(16)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmNumero INT=Null
,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null
,@pmIdCCosto VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmNContrato INT=Null,@pmFecVacIni SMALLDATETIME=Null
,@pmFecVacFin SMALLDATETIME=Null,@pmIdConcepto VARCHAR(4)=Null
 AS 
 SELECT Q.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Q.Numero AS NumLiquida,Fecha,Q.NContrato AS NumContrato,FecInicial,FecFinal,Q.Cantidad AS CantDias,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem
,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico,Q.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,Q.Observacion AS Observ,LogPmdio,Anulado
,Q.IdEstado AS IdEstdo,ED.Estado AS Liq_estado,TimeSys,Q.FecUpdate AS FecUpd,Q.IdUsuario AS IdUsuari,Usuario
--detalle de liq vac
,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,ClaseCon,D.Cantidad AS Cant,VrDevgado,VrDeducido,D.Tarifa AS Tarfa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo
--datos del 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,E.pVehiculo AS Emp_Vehiculo,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,FecUltCes,FecUltPri,FecUltVac,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdJornada AS CodJnada,Jornada
,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,C.IdCCosto AS CodCcto,CCosto,C.IdSubCos AS CodSubcen,SubCosto,Asistencia,NoDevenga,TipoTrabj
,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS SalBasico,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModPromdio,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,DenyCes,DenyPri,DenyVac,BasePrest
,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,VrSalud
 FROM Trn_NomVac AS Q INNER JOIN Trn_NomVacDet AS D ON Q.IdEmpleado=D.IdEmpleado AND Q.Numero=D.Numero
 INNER JOIN adm_Usuarios AS U ON Q.IdUsuario=U.IdUsuario  INNER JOIN EstadoDoc AS ED ON Q.IdEstado=ED.IdEstado
 INNER JOIN  Empleados AS E ON Q.IdEmpleado=E.IdEmpleado  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  Emp_Contrato AS C ON Q.NContrato=C.NContrato  INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon  
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 
 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 Dependencias AS DP ON C.IdDep=DP.IdDep INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 LEFT JOIN TiposCom AS TM ON Q.TipCom=TM.IdCom 
 WHERE  Q.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%')
 AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND Q.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND (Q.Numero>=ISNULL(@pmNumero,0)  AND Q.Numero<=ISNULL(@pmNumero,2147483647))
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 AND (Q.NContrato >=ISNULL(@pmNContrato ,0)  AND Q.NContrato <=ISNULL(@pmNContrato,2147483647))
 AND  (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (FecInicial>=ISNULL(@pmFecVacIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicial<=ISNULL(@pmFecVacFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Apellidos,Nombres,Q.Numero,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVacDetRes @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdNom VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmInactivo BIT=Null
,@pmEmp_Inactivo BIT=Null,@pmFecVacIni SMALLDATETIME=Null,@pmFecVacFin SMALLDATETIME=Null
 AS 
 SELECT Q.IdEmpleado AS IdEmpldo,Q.Numero AS NumLiquida,Q.NContrato AS NumContrato,Fecha,FecInicial,FecFinal,Q.Cantidad AS CantDias
,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico
,Q.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,Q.Observacion AS Observ
--detalle de liq vac
,Item,IdConcepto,Detalle,D.Cantidad AS Cant,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud
--datos del contrato
,IdArea,IdDep,IdInstala,IdNom,TipoLiquida,IdCCosto,IdSubCos,C.VrSalario AS SalBasico,VrAuxTrans
 FROM Trn_NomVac AS Q  INNER JOIN  Empleados AS E ON Q.IdEmpleado=E.IdEmpleado
 INNER JOIN  Emp_Contrato AS C ON Q.NContrato=C.NContrato
 LEFT JOIN Trn_NomVacDet AS D ON Q.IdEmpleado=D.IdEmpleado AND Q.Numero=D.Numero
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Anulado=0
 AND Q.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND IdDep LIKE ISNULL(@pmIdDep,'%') AND IdArea LIKE ISNULL(@pmIdArea,'%')
 AND IdNom LIKE ISNULL(@pmIdNom,'%')
 AND IdInstala LIKE ISNULL(@pmIdInstala,'%') AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND (Q.NContrato >=ISNULL(@pmNContrato ,0)  AND Q.NContrato <=ISNULL(@pmNContrato,2147483647))
 AND (FecInicial>=ISNULL(@pmFecVacIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicial<=ISNULL(@pmFecVacFin,CAST('20781230' AS SMALLDATETIME)))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 ORDER BY IdEmpldo,Q.Numero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVacIng @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdNom VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdEmpleado VARCHAR(16)=Null
,@pmNContrato INT=Null,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null
AS 
 SELECT Q.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Q.Numero AS NumLiquida,Fecha,Q.NContrato AS NumContrato,FecInicial,FecFinal,Q.Cantidad AS CantDias,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem
,DiasVac,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico,Q.pVehiculo AS PlacaVeh
,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,ClaseCon,CN.Tarifa AS TarifDft,D.Cantidad AS Cant,VrDevgado,VrDeducido,D.Tarifa AS Tarfa,VrBaseLiq,VrCertfcado,VrPension,VrSalud,NPrestamo
,IdTipCon,IdArea,IdDep,IdInstala,IdNom,TipoLiquida,IdCCosto,IdSubCos,C.VrSalario AS SalBasico,VrAuxTrans,E.Inactivo AS Emp_Inactvo,C.Inactivo AS Con_inactivo,IdFonPen,IdFonEps
 FROM Trn_NomVac AS Q INNER JOIN Empleados AS E ON Q.IdEmpleado=E.IdEmpleado
 INNER JOIN  Emp_Contrato AS C ON Q.NContrato=C.NContrato
 LEFT JOIN  Trn_NomVacDet AS D ON Q.IdEmpleado=D.IdEmpleado AND Q.Numero=D.Numero
 LEFT JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto  
 WHERE  FecInicial  BETWEEN  @pmFecIni AND @pmFecFin
 AND IdNom LIKE ISNULL(@pmIdNom,'%')  AND IdArea LIKE ISNULL(@pmIdArea,'%')
 AND IdDep LIKE ISNULL(@pmIdDep,'%') AND IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%')   AND IdTipCon LIKE ISNULL(@pmIdTipCon,'%')
 AND Q.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') 
 AND (Q.NContrato>=ISNULL(@pmNContrato,0)  AND Q.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 ORDER BY Q.IdEmpleado,Q.Numero,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_Nomina @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT
 AS 
 SELECT tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq
,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
ORDER BY tmIdEmpleado,tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_NominaVac @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16)
 AS 
 SELECT tmItem,tmIdConcepto,tmDetalle,tmClaseCon,tmCantDed,tmUnidad,tmVrUnitario,tmVrTotDed,tmVrOrigen,tmTarifa
,tmNPrestamo,tmNCuota,tmDiasCalc,tmVrPension AS VrApoPension,tmVrBaseLiq AS VrCertf,tmIdEmpleado,tmIdPeriodo
,tmVrSalud
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero  AND tmClaseLiq IN ('NOMINA','NOVEDAD')
 AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
ORDER BY tmIdEmpleado,tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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),@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
 WHERE NContrato=@pmNContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpNomAutoliq @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmFecha SMALLDATETIME,@pmPerAutoliq VARCHAR(6),@pmPerAutoSal VARCHAR(6),@pmFechaPago SMALLDATETIME,@pmIdAportnte VARCHAR(16),@pmPresentacion CHAR(1),@pmCodCia CHAR(2),@pmCodFondo VARCHAR(8),@pmCodArp VARCHAR(8),@pmCodOper VARCHAR(10),@pmEsCorr BIT,@pmPerCorr VARCHAR(6),@pmNumCorr VARCHAR(20),@pmTotalEmp INT,@pmVrNomina MONEY,@pmVrPension MONEY,@pmVrSalud MONEY,@pmVrRiesgos MONEY,@pmVrCajas MONEY
,@pmVrSena MONEY,@pmVrIcbf MONEY,@pmVrEsap MONEY,@pmVrMinEduc MONEY,@pmTotalAfipen INT,@pmTotalAfiSal INT,@pmTotalAfiRie INT,@pmTotalAfiCaj INT,@pmTotalAfiSen INT,@pmTotalAfiIcb INT,@pmTotalAfiEsa INT,@pmTotalAfiEduc INT,@pmTotAdmPen INT,@pmTotAdmSal INT,@pmTotAdmRie INT,@pmTotAdmCom INT,@pmObservacion VARCHAR(250),@pmLogAdmon VARCHAR(250),@pmRedoMiles BIT,@pmAnulado BIT,@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME,@pmTipoPlanilla VARCHAR(3)
 AS 
 UPDATE Trn_NomAutoliq SET Fecha=@pmFecha,PerAutoliq=@pmPerAutoliq,PerAutoSal=@pmPerAutoSal,FechaPago=@pmFechaPago,IdAportnte=@pmIdAportnte,Presentacion=@pmPresentacion,CodCia=@pmCodCia,CodFondo=@pmCodFondo,CodArp=@pmCodArp,CodOper=@pmCodOper,EsCorr=@pmEsCorr,PerCorr=@pmPerCorr,NumCorr=@pmNumCorr,TotalEmp=@pmTotalEmp,VrNomina=@pmVrNomina,VrPension=@pmVrPension,VrSalud=@pmVrSalud,VrRiesgos=@pmVrRiesgos,VrCajas=@pmVrCajas,VrSena=@pmVrSena,VrIcbf=@pmVrIcbf,VrEsap=@pmVrEsap,VrMinEduc=@pmVrMinEduc
,TotalAfipen=@pmTotalAfipen,TotalAfiSal=@pmTotalAfiSal,TotalAfiRie=@pmTotalAfiRie,TotalAfiCaj=@pmTotalAfiCaj,TotalAfiSen=@pmTotalAfiSen,TotalAfiIcb=@pmTotalAfiIcb,TotalAfiEsa=@pmTotalAfiEsa,TotalAfiEduc=@pmTotalAfiEduc,TotAdmPen=@pmTotAdmPen,TotAdmSal=@pmTotAdmSal,TotAdmRie=@pmTotAdmRie,TotAdmCom=@pmTotAdmCom,Observacion=@pmObservacion,LogAdmon=@pmLogAdmon,RedoMiles=@pmRedoMiles,Anulado=@pmAnulado,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipoPlanilla=@pmTipoPlanilla
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpNomFijos @pmIdEmpleado VARCHAR(16),@pmnAnno INT,@pmnMes INT,@pmTarifa DECIMAL(10,4),@pmVrBase MONEY,@pmVrSalarios MONEY,@pmVrPrima MONEY,@pmVrVacacnes MONEY,@pmVrSIntExcto MONEY,@pmVrOtrosIng MONEY,@pmVrIngresos MONEY
,@pmVrPensiones MONEY,@pmVrDedcido MONEY,@pmVrExcento MONEY,@pmVrCertEdu MONEY,@pmVrCertVda MONEY,@pmVrCertAFC MONEY,@pmVrCertfcado MONEY,@pmPeriodos DECIMAL(10,4),@pmVrTotalBase MONEY,@pmOrignPorc VARCHAR(10),@pmFecUpdate SMALLDATETIME
,@pmVrSalud MONEY 
 AS 
 UPDATE Trn_NomFijos SET Tarifa=@pmTarifa,VrBase=@pmVrBase,VrSalarios=@pmVrSalarios,VrPrima=@pmVrPrima,VrVacacnes=@pmVrVacacnes,VrSIntExcto=@pmVrSIntExcto,VrOtrosIng=@pmVrOtrosIng,VrIngresos=@pmVrIngresos,VrPensiones=@pmVrPensiones,VrDedcido=@pmVrDedcido
,VrExcento=@pmVrExcento,VrCertEdu=@pmVrCertEdu,VrCertVda=@pmVrCertVda,VrCertAFC=@pmVrCertAFC,VrCertfcado=@pmVrCertfcado,Periodos=@pmPeriodos,VrTotalBase=@pmVrTotalBase,OrignPorc=@pmOrignPorc,FecUpdate=@pmFecUpdate
,VrSalud=@pmVrSalud
 WHERE IdEmpleado=@pmIdEmpleado AND nAnno=@pmnAnno AND nMes=@pmnMes
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpNomVacDet @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad INT,@pmVrDevgado MONEY,@pmVrDeducido MONEY
,@pmTarifa DECIMAL(10,4),@pmVrBaseLiq MONEY,@pmVrCertfcado MONEY,@pmVrPension MONEY,@pmNPrestamo INT,@pmNCuota INT,@pmIdPeriodo VARCHAR(8),@pmVrSalud MONEY
 AS 
 UPDATE Trn_NomVacDet SET IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,Cantidad=@pmCantidad,VrDevgado=@pmVrDevgado,VrDeducido=@pmVrDeducido,Tarifa=@pmTarifa,VrBaseLiq=@pmVrBaseLiq,VrCertfcado=@pmVrCertfcado
,VrPension=@pmVrPension,VrSalud=@pmVrSalud,NPrestamo=@pmNPrestamo,NCuota=@pmNCuota,IdPeriodo=@pmIdPeriodo
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero AND Item=@pmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUptm_Nomina @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4)
,@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4)
,@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8)
,@pmtmVrPension MONEY,@pmtmVrSalud MONEY
 AS 
 UPDATE tm_Nomina SET tmIdConcepto=@pmtmIdConcepto,tmClaseCon=@pmtmClaseCon,tmDetalle=@pmtmDetalle,tmCantDevg=@pmtmCantDevg,tmCantDed=@pmtmCantDed,tmVrUnitario=@pmtmVrUnitario,tmVrTotDevg=@pmtmVrTotDevg,tmVrTotDed=@pmtmVrTotDed
,tmUnidad=@pmtmUnidad,tmClaseLiq=@pmtmClaseLiq,tmVrOrigen=@pmtmVrOrigen,tmTarifa=@pmtmTarifa,tmDiasCalc=@pmtmDiasCalc,tmDiasNov=@pmtmDiasNov,tmCodFondo=@pmtmCodFondo,tmNPrestamo=@pmtmNPrestamo,tmNCuota=@pmtmNCuota,tmOrigCargue=@pmtmOrigCargue
,tmVrBaseLiq=@pmtmVrBaseLiq,tmNContto=@pmtmNContto,tmFecIni=@pmtmFecIni,tmFecFin=@pmtmFecFin,tmIdPeriodo=@pmtmIdPeriodo,tmVrPension=@pmtmVrPension,tmVrSalud=@pmtmVrSalud
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado AND tmItem=@pmtmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

