USE [MinovaES_BankKalteng_Dev]
GO
/****** Object:  StoredProcedure [dbo].[PRPTCMBONUS]    Script Date: 13/07/2022 10.13.06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PRPTCMBONUS]
(
  @CompanyID varchar(8),
  @Scenario varchar(8),
  @Year varchar(8),
  @CostCenter varchar(8),
  @Organization varchar(8),
  @EmployeeIDFrom varchar(8),
  @EmployeeIDTo varchar(8),
  @MenuID varchar(8),
  @UserID varchar(8)
)
AS

--DECLARE @Scenario VARCHAR(8)
--DECLARE @CompanyID VARCHAR(8)
--DECLARE @Year VARCHAR(8)
--DECLARE @CostCenter VARCHAR(8)
--DECLARE @Organization VARCHAR(8)
--DECLARE @EmployeeIDFrom VARCHAR(8)
--DECLARE @EmployeeIDTo VARCHAR(8)
--DECLARE @MenuID varchar(8) = 'CMR01'
--DECLARE @UserID varchar(8) = 'tri'
--SET @Scenario = '0012'
--SET @Year = '2023'
--SET @CompanyID = '1000'
--SET @CostCenter = ''
--SET @Organization = ''
--SET @EmployeeIDFrom = ''
--SET @EmployeeIDTo = ''

DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

DECLARE @TableResult TABLE ( EmployeeID VARCHAR(20) )
INSERT  INTO @TableResult
        EXEC dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, -- varchar(max)
            @UserID = @UserID

SELECT DISTINCT fixcal.* 
				,pa1.FullName
				,pos.ObjectDescription AS PosDesc
				,job.ObjectDescription AS JobDesc
				,org.ObjectDescription AS OrgDesc
				,cc.ObjectDescription AS CCDesc
				,cu300.WTDefinitionDesc AS WTDesc
				,dbo.fn_formatdatetime(fixcal.PayrollDate, 'dd\mm\yyyy') AS BaseDate
FROM dbo.PHRCMTRVARIABLECAL AS fixcal
INNER JOIN @TableResult AS tableotorisasi
	ON fixcal.EmployeeID = tableotorisasi.EmployeeID
LEFT JOIN dbo.PHRPA0001 AS pa1
	ON fixcal.EmployeeID = pa1.EmployeeID
	AND pa1.StartDate <= @now AND pa1.EndDate >= @now
LEFT JOIN dbo.PHRPA0002 AS pa2
	ON fixcal.EmployeeID = pa2.EmployeeID
	AND pa2.StartDate <= @now AND pa2.EndDate >= @now
LEFT JOIN dbo.PHROM0001 AS pos
	ON pos.ObjectClass = 'P'
	AND pos.ObjectID = pa2.Position 
	AND pos.StartDate <= @now AND pos.EndDate >= @now
LEFT JOIN dbo.PHROM0001 AS job
	ON job.ObjectClass = 'J'
	AND job.ObjectID = pa2.Job 
	AND job.StartDate <= @now AND job.EndDate >= @now
LEFT JOIN dbo.PHROM0001 AS org
	ON org.ObjectClass = 'O'
	AND org.ObjectID = pa2.Organization 
	AND org.StartDate <= @now AND org.EndDate >= @now
LEFT JOIN dbo.PHROM0001 AS cc
	ON cc.ObjectClass = 'CC'
	AND cc.ObjectID = pa2.CostCenter 
	AND cc.StartDate <= @now AND cc.EndDate >= @now
LEFT JOIN dbo.PHRPYCU0300 AS cu300
	ON cu300.WageTypeDefinition = fixcal.WageTypeTarget
	AND cu300.StartDate <= @now AND cu300.EndDate >= @now
WHERE fixcal.Scenario = @Scenario
	AND LEFT(fixcal.StartDate,4) = @Year
	AND (fixcal.CompanyID = @CompanyID OR @CompanyID = '')
	AND (fixcal.Organization = @Organization OR @Organization = '')
	AND (fixcal.CostCenter = @CostCenter OR @CostCenter = '')
	AND (fixcal.EmployeeID >= @EmployeeIDFrom OR @EmployeeIDFrom = '')
	AND (fixcal.EmployeeID <= @EmployeeIDTo OR @EmployeeIDTo = '')