BANK KALTENG

??--USE [MinovaES_BankKalteng_BaseServerProd]

--GO

--/****** Object:  StoredProcedure [dbo].[CRPTBKTWUL]    Script Date: 17/07/2025 11.17.23 ******/

--SET ANSI_NULLS ON

--GO

--SET QUOTED_IDENTIFIER ON

--GO

--ALTER PROCEDURE [dbo].[CRPTBKTWUL]

--(

--	@Period VARCHAR(20)

--	,@Organization VARCHAR(20)

--	,@AppraisalType VARCHAR(20)

--)

--AS

DECLARE @Period VARCHAR(20) = '2025'

DECLARE @Organization VARCHAR(20) = '00000001'

DECLARE @AppraisalType VARCHAR(20) = 'F'

DECLARE @now VARCHAR(20) = FORMAT(getdate(), 'yyyyMMdd')

DECLARE @PeiodeEnDt VARCHAR(20) = @Period + '1231'

DECLARE @BizFlowID VARCHAR(200) = ''

IF(@AppraisalType = 'F')

BEGIN 

	SET @BizFlowID = 'MD_PMS_Final'

END

ELSE IF(@AppraisalType = 'G')

BEGIN 

	SET @BizFlowID = 'MD_PMS_Goal'

END

ELSE IF(@AppraisalType = 'P')

BEGIN 

	SET @BizFlowID = 'MD_PMS_Review'

END

IF(@Organization = '')

BEGIN SET @Organization = '00000001' END

DECLARE @t_org TABLE(

	org_id VARCHAR(20)

	,org_name VARCHAR(500)

	,div_id VARCHAR(20)

	,div_name VARCHAR(500)

	,cab_id VARCHAR(20)

	,cab_name VARCHAR(500)

)

INSERT INTO @t_org

select org_id, org_name, org_id_04, org_name_04, org_id_12, org_name_12 from dbo.CBKT_GetOrgInOrgWide(@Organization, @now)

DECLARE @t_emp TABLE

(

	EmployeeID VARCHAR(20)

	,EmployeeName VARCHAR(500)

	,div_id VARCHAR(20)

	,div_name VARCHAR(500)

	,cab_id VARCHAR(20)

	,cab_name VARCHAR(500)

	,Organization VARCHAR(20)

	,OrgDesc VARCHAR(500)

	,Position VARCHAR(20)

	,PosDesc VARCHAR(500)

	,Job VARCHAR(20)

	,JobDesc VARCHAR(500)

)

INSERT INTO @t_emp

SELECT pa2.EmployeeID, pa1.FullName, torg.div_id, torg.div_name, torg.cab_id, torg.cab_name, pa2.Organization, torg.org_name, pa2.Position, pos.ObjectDescription, pa2.Job, job.ObjectDescription

FROM PHRPA0002 as pa2

INNER JOIN @t_org AS torg

	ON pa2.Organization = torg.org_id

LEFT JOIN PHRPA0001 AS pa1

	ON pa2.EmployeeID = pa1.EmployeeID

	AND pa1.StartDate <= @now AND pa1.EndDate >= @now

LEFT JOIN PHROM0001 AS pos

	ON pa2.Position = pos.ObjectID

	AND pos.ObjectClass = 'P'

	AND pos.StartDate <= @now AND pos.EndDate >= @now

LEFT JOIN PHROM0001 AS job

	ON pa2.Job = job.ObjectID

	AND job.ObjectClass = 'J'

	AND job.StartDate <= @now AND job.EndDate >= @now

WHERE pa2.StartDate <= @now AND pa2.EndDate >= @now 

	--AND pa2.EmployeeID IN ( '00000043','00000110','00000855')

DECLARE @t_result TABLE

(

	EmployeeID VARCHAR(20)

	,EmployeeName VARCHAR(500)

	,DivID VARCHAR(20)

	,DivDesc VARCHAR(500)

	,Organization VARCHAR(20)

	,OrgDesc VARCHAR(500)

	,Position VARCHAR(20)

	,PosDesc VARCHAR(500)

	,Job VARCHAR(20)

	,JobDesc VARCHAR(500)

	,RegID DECIMAL(18,0)

	,BizFlowID VARCHAR(100)

	,[Sequence] DECIMAL(18,0)

	,TaskIDRun VARCHAR(100)

	,TaskStatus VARCHAR(50)

	,ExecuteEmployeeID VARCHAR(50)

	,ExecuteEmpName VARCHAR(50)

)

DECLARE @t_pa47 TABLE

(

	EmployeeID VARCHAR(20)

	,EmployeeName VARCHAR(500)

	,DivID VARCHAR(20)

	,DivDesc VARCHAR(500)

	,CabID VARCHAR(20)

	,CabDesc VARCHAR(500)

	,Organization VARCHAR(20)

	,OrgDesc VARCHAR(500)

	,Position VARCHAR(20)

	,PosDesc VARCHAR(500)

	,Job VARCHAR(20)

	,JobDesc VARCHAR(500)

	,StartDate VARCHAR(20)

	,RegID DECIMAL(18,0)

	,BizFlowID VARCHAR(100)

	,[Sequence] DECIMAL(18,0)

	,TaskIDRun VARCHAR(100)

	,TaskStatus VARCHAR(50)

	,ExecuteEmployeeID VARCHAR(50)

	,ExecuteEmpName VARCHAR(50)

)

INSERT INTO @t_pa47

SELECT DISTINCT temp.*, pa47.StartDate, pa47.RegID, '' AS BizFlowID, 1 AS Sequence, '' AS TaskIDRun, 'F' AS TaskStatus, '' AS ExecuteEmployeeID, '' AS ExecuteEmpName

FROM PHRPA0047 AS pa47

INNER JOIN @t_emp AS temp

	ON pa47.EmployeeID = temp.EmployeeID

WHERE LEFT(StartDate,4) = @Period

	AND (pa47.AppraisalType = @AppraisalType OR @AppraisalType = '')