BANK KALTENG

??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 = '')