BANK KALTENG

??USE [MinovaES_BankKalteng_Dev]

GO

/****** Object:  StoredProcedure [dbo].[PRPTCMMERITINCREASE]    Script Date: 13/07/2022 10.16.07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PRPTCMMERITINCREASE]

(

	@Version VARCHAR(8) = '0011'

	, @CompanyID VARCHAR(8) = '1000'

	, @Year VARCHAR(8)  = '2022'

	, @CostCenter VARCHAR(8) = ''

	, @Organization VARCHAR(8) = ''

	, @EmployeeIDFrom VARCHAR(8) = ''

	, @EmployeeIDTo VARCHAR(8) = ''

	, @MenuID VARCHAR(10) = 'PA01'

	, @UserID VARCHAR(10) = 'minovais'

)

AS

--DECLARE @Version VARCHAR(8) = '0011'

--DECLARE @CompanyID VARCHAR(8) = '1000'

--DECLARE @Year VARCHAR(8)  = '2022'

--DECLARE @CostCenter VARCHAR(8) = ''

--DECLARE @Organization VARCHAR(8) = ''

--DECLARE @EmployeeIDFrom VARCHAR(8) = ''

--DECLARE @EmployeeIDTo VARCHAR(8) = ''

--DECLARE @MenuID VARCHAR(10) = 'PA01'

--DECLARE @UserID VARCHAR(10) = 'minovais'

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.PHRCMTRFIXEDCAL 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 = @Version

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