if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTiposOperarios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelTiposOperarios]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposNom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposNom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposOperarios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposOperarios]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoLab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ContratoLab]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmpleadosCnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmpleadosCnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaApo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaApo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposNom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposNom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposOperarios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposOperarios]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposNom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposNom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposOperarios]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposOperarios]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaApo] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmCodFondo VARCHAR(8)=Null,@pmIdCia CHAR(2)=Null
AS 
 SELECT N.KeyRegistro AS KeyReg,N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContto,FecInicial,FecFinal,N.IdDep AS CodDep,N.IdInstala AS CodInstala,N.IdCCosto AS CodCcto
,N.IdSubCos AS CodSubcc,N.IdNom AS CodNom,N.pVehiculo AS PlacaVeh,N.VrSalario AS Salbasico,EsRetiro,NLiqRet,IdFonPen,IdFonEps,IdFonArp,IdFonCes,IdCajaCom,FecIngreso,FecRetiro,IdArea,C.IdClase AS CodClaRie
,CR.Tarifa AS TarRie,TipoTrabj,VrAuxTrans,VrUpc,SalIntegral,SalMinimo,C.Inactivo AS Con_Inctivo,D.IdConcepto AS CodConcepto,Concepto,ClaseCon,CN.Tarifa AS TarifDft,ClaseLiq,CantDevg,CantDed,VrTotDevg,VrTotDed,VrOrigen,D.Tarifa AS Tarfa
,DiasCalc,DiasNov,DiasCalc-DiasNov AS DiasNeto,CodFondo,IdCot,SubTipoCot,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyDcto,LiqEspSalud,DctoSalud,PagoSalud
 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 ClaseRie AS CR ON C.IdClase=CR.IdClase  INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND NLiqRet>=0 AND D.IdConcepto IN ('PEN','RIE','SAL','FSP','FSU','SEN','ICB','COM','DCP','DCS','AVP','AVA')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND CodFondo LIKE ISNULL(@pmCodFondo,'%')
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 UNION ALL
 SELECT D.IdPeriodo,V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.FecInicial,V.FecFinal,C.IdDep,C.IdInstala,C.IdCCosto,C.IdSubCos,C.IdNom,V.pVehiculo
,V.VrSalario,0,0,IdFonPen,E.IdFonEps,IdFonArp,IdFonCes,IdCajaCom,C.FecIngreso,C.FecRetiro,C.IdArea,C.IdClase,0,C.TipoTrabj,C.VrAuxTrans,C.VrUpc
,C.SalIntegral,C.SalMinimo,C.Inactivo,D.IdConcepto,Concepto,ClaseCon,CN.Tarifa,'NOMINA',0,D.Cantidad,D.VrDevgado,D.VrDeducido,D.VrBaseLiq,D.Tarifa
,D.Cantidad,0,D.Cantidad,E.IdFonEps,C.IdCot,C.SubTipoCot,C.DenyPEN,C.DenyEPS,C.DenyARP,C.DenyCaj,C.DenyDcto,LiqEspSalud,DctoSalud,PagoSalud
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE SUBSTRING(D.IdPeriodo,1,6)=@pmMes AND D.IdConcepto IN ('DCP','DCS','FSP','FSU') AND V.Anulado=0
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 ORDER BY  N.IdEmpleado,N.NContrato,D.IdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_ContratoLab] @pmNContrato INT,@pmInactivo BIT=Null
 AS 
 SELECT  C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo
,C.IdJornada AS IdJrnada,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,FspFinmes,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac
,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,DsoSabado,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,DecRenta
,E.IdProf AS CodProf,Profesion,Observacion,E.IdEstado AS Emp_IdEstdo,ET.Estado AS Emp_Estdo,E.Inactivo AS Emp_Inactivo
,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update,C.IdUsuario AS IdUsuari,Usuario,LiqEspSalud,DctoSalud,PagoSalud
 FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia  INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado
 WHERE  C.NContrato=@pmNContrato  AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmpleadosCnt] @pmIdNom VARCHAR(4),@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT E.IdEmpleado AS IdEmpledo,Apellidos,Nombres,IdLugarCed,LC.Localidad AS LugarCed,E.IdProf AS CodProf,Profesion,TallaCam,TallaPan,TallaZap
,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom
,CC.Fondo AS CajaComp,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,CdLocCue,E.NContrato AS NumContrato,FecUltIng,FecUltRet,FecVincula,NCAnterior,FecIngAnt,FecRetAnt
,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,FecUltAum,FecUltCes
,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,FecUltParc,EsCondtor,pVehiculo,DecRenta,E.IdEstado AS IdEst_Emp,ET.Estado AS Estad_Emp,E.Inactivo AS Inctivo,Codigo,E.IdUsuario  AS IdUsuar
--datos del contrato
,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdJornada AS IdJrnada,Jornada,Rangos,C.IdArea AS CodArea,Area,C.IdDep AS CodDep
,Dependencia,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac
,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,TipoTrabj,IdCot,IdGrupo,VrUpc,Reclmto,Notas,C.IdEstado AS IdEst_Con,EC.Estado AS Estad_Con,C.Inactivo AS Inactvo,CdFonAvp,FspFinmes
,LiqEspSalud,DctoSalud,PagoSalud
 FROM Empleados AS E INNER JOIN Emp_Contrato AS C ON E.NContrato=C.NContrato
 INNER JOIN  Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia  INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado
 WHERE E.Inactivo=0 AND  C.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER  BY E.IdEmpleado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposOperarios] @pmIdOper VARCHAR(4),@pmTipoOperario VARCHAR(50),@pmTarifaCom DECIMAL(14,4),@pmInactivo BIT
 AS 
 INSERT INTO TiposOperarios (IdOper,TipoOperario,TarifaCom,Inactivo)
 VALUES (@pmIdOper,@pmTipoOperario,@pmTarifaCom,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposOperarios] @pmIdOper VARCHAR(4),@pmTipoOperario VARCHAR(50),@pmTarifaCom DECIMAL(14,4),@pmInactivo BIT
 AS 
 UPDATE TiposOperarios SET TipoOperario=@pmTipoOperario,TarifaCom=@pmTarifaCom,Inactivo=@pmInactivo
 WHERE IdOper=@pmIdOper
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposOperarios] @pmIdOper VARCHAR(4)
 AS 
 IF @pmIdOper IS NULL
  BEGIN
  SELECT IdOper,TipoOperario,TarifaCom FROM TiposOperarios
  WHERE Inactivo=0 ORDER BY TipoOperario
  END
 ELSE
  BEGIN
   SELECT IdOper,TipoOperario,TarifaCom,Inactivo FROM TiposOperarios
   WHERE IdOper=@pmIdOper
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTiposOperarios] @pmIdOper VARCHAR(4)
 AS 
 DELETE FROM TiposOperarios WHERE IdOper=@pmIdOper
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpTiposNom] @pmIdNom VARCHAR(4),@pmTipoNomina VARCHAR(50),@pmAsistencia BIT,@pmNoDevenga BIT
,@pmTipoLiquida CHAR(1),@pmFechaActual SMALLDATETIME,@pmInactivo BIT,@pmCdCiaInt CHAR(2)
,@pmLiqEspSalud BIT,@pmDctoSalud DECIMAL(14,4),@pmPagoSalud DECIMAL(14,4)
 AS 
 UPDATE TiposNom SET TipoNomina=@pmTipoNomina,Asistencia=@pmAsistencia,NoDevenga=@pmNoDevenga,TipoLiquida=@pmTipoLiquida
,FechaActual=@pmFechaActual,Inactivo=@pmInactivo,CdCiaInt=@pmCdCiaInt,LiqEspSalud=@pmLiqEspSalud,DctoSalud=@pmDctoSalud,PagoSalud=@pmPagoSalud
 WHERE IdNom=@pmIdNom
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposNom] @pmIdNom VARCHAR(4)
 AS 
 IF @pmIdNom IS NULL
  SELECT IdNom,TipoNomina,Asistencia,NoDevenga,TipoLiquida,FechaActual,CdCiaInt,LiqEspSalud,DctoSalud,PagoSalud
  FROM TiposNom WHERE Inactivo=0
  ORDER BY IdNom
ELSE
  SELECT IdNom,TipoNomina,Asistencia,NoDevenga,TipoLiquida,FechaActual,CdCiaInt,LiqEspSalud,DctoSalud,PagoSalud,Inactivo
  FROM TiposNom
  WHERE IdNom=@pmIdNom
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTiposNom] @pmIdNom VARCHAR(4),@pmTipoNomina VARCHAR(50),@pmAsistencia BIT,@pmNoDevenga BIT
,@pmTipoLiquida CHAR(1),@pmFechaActual SMALLDATETIME,@pmInactivo BIT,@pmCdCiaInt CHAR(2),@pmLiqEspSalud BIT,@pmDctoSalud DECIMAL(14,4),@pmPagoSalud DECIMAL(14,4)
 AS 
 INSERT INTO TiposNom (IdNom,TipoNomina,Asistencia,NoDevenga,TipoLiquida,FechaActual,Inactivo,CdCiaInt,LiqEspSalud,DctoSalud,PagoSalud)
 VALUES (@pmIdNom,@pmTipoNomina,@pmAsistencia,@pmNoDevenga,@pmTipoLiquida,@pmFechaActual,@pmInactivo,@pmCdCiaInt,@pmLiqEspSalud,@pmDctoSalud,@pmPagoSalud)
GO