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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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  ClaseLiq IN ('NOMINA','NOVEDAD')  AND VrBaseLiq>0 AND IdConcepto NOT IN ('BAS','AXT','LIC','IGE','IRP','HHD','HHN','DSR','VAC')
  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') 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')
   AND (VrBaseLiq>0 OR IdConcepto='DNR' OR  IdConcepto='SAN')
   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') 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')
   AND IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO