INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('HEM','CANTIDAD DE HORAS EXTRAS MAXIMO PERMITIDO','INTEGER','-1',4,'NOMINA')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleBas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleBas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetallePmd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetallePmd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NominaBas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NominaBas]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleBas] @pmMes VARCHAR(6),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16)
,@pmIdConcepto VARCHAR(4),@pmIdPeriodo VARCHAR(8),@pmTipBas VARCHAR(3),@pmClaseLiq VARCHAR(10),@pmPerMax INT
 AS 
 IF @pmTipBas='SD' --SALARIO BASICO
 SELECT IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(CantDed) AS SCANDED,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED
 ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV,SUM(VrBaseLiq) AS SBASELIQ,SUM(VrBasExceso) AS STOTBEXC
 ,SUM(CASE WHEN IdConcepto='IGE' AND VrBasExceso>0 THEN VrBasExceso ELSE VrBaseLiq END) AS SBASEIGE 
 FROM Trn_NomDetalle 
 WHERE KeyRegistro=@pmKeyRegistro  AND IdEmpleado=@pmIdEmpleado AND SUBSTRING(IdPeriodo,1,6)=@pmMes
 AND IdPeriodo<>@pmIdPeriodo  AND CAST(SUBSTRING(IdPeriodo,7,2) AS INT)<@pmPerMax
 AND (IdConcepto NOT IN ('BAS','HHD','HHN','LIC','IGE','IRP','DSR','VAC','DCP','DCS','FSP','FSU','RET') OR (IdConcepto IN ('BAS','HHD','HHN') AND OrigCargue='CONVENCION'))
 AND IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 GROUP BY IdConcepto

 ELSE --TOTAL DEVENGADO

 SELECT IdConcepto, SUM(CantDevg) AS SCANDEV,SUM(CantDed) AS SCANDED,SUM(VrTotDevg) AS STOTDEV,SUM(VrTotDed) AS STOTDED
 ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV,SUM(VrBaseLiq) AS SBASELIQ,SUM(VrBasExceso) AS STOTBEXC
 ,SUM(CASE WHEN IdConcepto='IGE' AND VrBasExceso>0 THEN VrBasExceso ELSE VrBaseLiq END) AS SBASEIGE
 FROM Trn_NomDetalle 
 WHERE KeyRegistro=@pmKeyRegistro  AND IdEmpleado=@pmIdEmpleado AND SUBSTRING(IdPeriodo,1,6)=@pmMes
 AND IdPeriodo<>@pmIdPeriodo  AND CAST(SUBSTRING(IdPeriodo,7,2) AS INT)<@pmPerMax
 AND IdConcepto NOT IN ('DCP','DCS','FSP','FSU','RET')
 AND ClaseLiq IN ('NOMINA','NOVEDAD') AND IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 GROUP BY IdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetallePmd] @pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmModoPmd CHAR(1),@pmFecIni SMALLDATETIME
,@pmFecFin SMALLDATETIME,@pmIdConcepto VARCHAR(4)=Null
 AS 
 IF @pmModoPmd='F' --SALARIOS FIJO
  SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes,IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV,SUM(VrBaseLiq) AS SBASELIQ
  FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
  WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
  AND (IdConcepto NOT IN ('BAS','HHD','HHN','AXT','LIC','IGE','IRP','DSR','VAC') OR (IdConcepto IN ('BAS','HHD','HHN','AXT') AND OrigCargue='CONVENCION'))
  AND ClaseLiq IN ('NOMINA','NOVEDAD') AND VrBaseLiq>0 
  GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
 ELSE 
  BEGIN
   IF @pmModoPmd='B' --SALARIO BASICO
    SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes,IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
   ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV
  ,SUM(CASE  WHEN IdConcepto IN ('DNR','SAN','STC') THEN CantDed*VrUnitario WHEN IdConcepto IN ('IGE','IRP') THEN (N.VrSalario/30)*CantDevg  ELSE VrBaseLiq END) AS SBASELIQ
    FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
    WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
   AND  ClaseLiq IN ('NOMINA','NOVEDAD') AND IdConcepto IN ('BAS','AXT','LIC','IGE','IRP','HHD','HHN','DSR','VAC','DNR','SAN','STC')
   AND (VrBaseLiq>0 OR IdConcepto='DNR' OR IdConcepto='SAN' OR IdConcepto='STC')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  ELSE --SALARIO VARIABLE
   SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes, IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
  ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV
 ,SUM(CASE  WHEN IdConcepto IN ('DNR','SAN','STC') THEN CantDed*VrUnitario WHEN IdConcepto IN ('IGE','IRP') THEN (N.VrSalario/30)*CantDevg  ELSE VrBaseLiq END) AS SBASELIQ
   FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
   WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
   AND  ClaseLiq IN ('NOMINA','NOVEDAD') AND (VrBaseLiq>0 OR IdConcepto='DNR' OR IdConcepto='SAN' OR IdConcepto='STC')
   AND IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NominaBas] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmTipBas VARCHAR(3)
,@pmtmClaseLiq VARCHAR(10)
 AS 
 IF @pmTipBas ='SD' --SALARIO BASICO+EXTRAS
  SELECT tmIdConcepto,SUM(tmCantDevg) AS SCANDEV,SUM(tmVrTotDevg) AS STOTDEV,SUM(tmCantDed) AS SCANDED,SUM(tmVrTotDed) AS STOTDED,SUM(tmVrBaseLiq) AS SBASLIQ
  ,SUM(tmBasExceso) AS STOTBEXC,SUM(CASE WHEN tmIdConcepto='IGE' AND tmBasExceso>0 THEN tmBasExceso ELSE tmVrBaseLiq END) AS SBASEIGE 
  FROM tm_Nomina 
  WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado 
  AND (tmIdConcepto NOT IN ('BAS','HHD','HHN','LIC','IGE','IRP','DSR','VAC','DCP','DCS','FSP','FSU','RET') OR (tmIdConcepto IN ('BAS','HHD','HHN') AND tmOrigCargue='CONVENCION'))
  AND tmClaseLiq LIKE ISNULL(@pmtmClaseLiq,'%')
  GROUP BY tmIdConcepto
 ELSE --TOTAL DEVENGADO
  SELECT tmIdConcepto,SUM(tmCantDevg) AS SCANDEV ,SUM(tmVrTotDevg) AS STOTDEV,SUM(tmCantDed) AS SCANDED,SUM(tmVrTotDed) AS STOTDED,SUM(tmVrBaseLiq) AS SBASLIQ
  ,SUM(tmBasExceso) AS STOTBEXC,SUM(CASE WHEN tmIdConcepto='IGE' AND tmBasExceso>0 THEN tmBasExceso ELSE tmVrBaseLiq END) AS SBASEIGE 
  FROM tm_Nomina 
  WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado AND tmClaseLiq IN ('NOMINA','NOVEDAD')
  AND tmIdConcepto NOT IN ('DCP','DCS','FSP','FSU','RET')
  GROUP BY tmIdConcepto
GO