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].[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','LIC','IGE','IRP','HHD','HHN','DSR','VAC','DCP','DCS','FSP','FSU','RET') 
 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].[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','LIC','IGE','IRP','HHD','HHN','DSR','VAC','DCP','DCS','FSP','FSU','RET')
  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