ALTER PROCEDURE [dbo].[CRPTBKTELWU]
(
	@Period VARCHAR(20)
	,@Organization VARCHAR(20)
	,@EmployeeID VARCHAR(20)
	,@AppraisalType VARCHAR(20)
)
AS

--DECLARE @Period VARCHAR(20) = '2025'
--DECLARE @Organization VARCHAR(20) = '00000001'
--DECLARE @EmployeeID VARCHAR(20) = ''--'00000110'--'00000043'
--DECLARE @AppraisalType VARCHAR(20) = 'G'

DECLARE @now VARCHAR(20) = FORMAT(GETDATE(), 'yyyyMMdd')
DECLARE @PeriodeEnDt 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)
)
INSERT INTO @t_org
select org_id, org_name, org_id_04, org_name_04 from dbo.GetOrgInOrgWide(@Organization, @now)

DECLARE @t_emp 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)
)

INSERT INTO @t_emp
SELECT pa2.EmployeeID, pa1.FullName, torg.diV_id, torg.div_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 = @EmployeeID OR @EmployeeID = '')

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)
	,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)
	,PICSeq DECIMAL(18,0)
	,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, hispic.Sequence, hispic.ExecuteEmpID AS ExecuteEmployeeID, pic.FullName AS ExecuteEmpName
FROM PHRPA0047 AS pa47
INNER JOIN @t_emp AS temp
	ON pa47.EmployeeID = temp.EmployeeID
LEFT JOIN PCMWFHISPIC AS hispic
	ON hispic.RegID = pa47.RegID
LEFT JOIN dbo.PHRPA0001 AS pic
	ON hispic.ExecuteEmpID = pic.EmployeeID
	AND pic.StartDate <= @now AND pic.EndDate >= @now
WHERE (pa47.EmployeeID = @EmployeeID OR @EmployeeID = '')
	AND (pa47.AppraisalType = @AppraisalType OR @AppraisalType = '')
	AND LEFT(pa47.StartDate,4) = @Period

DECLARE @t_histask TABLE
(
	RegID DECIMAL(18,0)
	,[Sequence] DECIMAL(18,0)
	,TaskIDRun VARCHAR(100)
	,WFStatus VARCHAR(100)
	,TaskStatus VARCHAR(100)
	,ExecutedEmployeeID VARCHAR(100)
)

INSERT INTO @t_histask
SELECT HisTask.RegID, HisTask.Sequence, HisTask.TaskID, HisTask.WorkFlowStatus, HisTask.TaskStatus, HisTask.ExecuteEmployeeID
FROM dbo.PCMWFHISTASK AS HisTask
INNER JOIN @t_pa47 AS pa47
	ON HisTask.RegID = pa47.RegID

DECLARE @t_wf TABLE
(
	RegID VARCHAR(20)
	,BizFlowID VARCHAR(250)
	,TaskRunID VARCHAR(250)
	,EmployeeID VARCHAR(20)
)

INSERT INTO @t_wf
SELECT DISTINCT MAX(runtask.RegID), BizFlowID, TaskIDRun, ExecuteEmployeeID
--SELECT RegID, BizFlowID, TaskIDRun, ExecuteEmployeeID
FROM PCMWFRUNTASK AS runtask
INNER JOIN @t_emp AS temp
	ON runtask.ExecuteEmployeeID = temp.EmployeeID
INNER JOIN PCMWFRUNBIZDATA AS bizdata
	ON runtask.RegID = bizdata.RegID
WHERE TaskIDRun = 'create'
	AND BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
	AND LEFT(InboundDate,4) = @Period
	AND (runtask.BizFlowID = @BizFlowID OR @BizFlowID = '')
	AND (runtask.RegID NOT IN ( SELECT RegID FROM @t_pa47))
	AND bizdata.FieldID = 'StartDate_47' AND LEFT(bizdata.Value,4) = @Period
GROUP BY BizFlowID, TaskIDRun, ExecuteEmployeeID
--ORDER BY RegID ASC

DECLARE @TRunTask TABLE
(
	[RegID] [BIGINT] NOT NULL,
	[Sequence] [INT] NOT NULL,
	[BizFlowID] [VARCHAR](50) NOT NULL,
	[TaskID] [VARCHAR](50) NOT NULL,
	[DepthLevel] [BIGINT] NOT NULL,
	[BizFlowIDRun] [VARCHAR](50) NULL,
	[TaskIDRun] [VARCHAR](50) NULL,
	[InboundDate] [VARCHAR](14) NULL,
	[ReadDate] [VARCHAR](14) NULL,
	[ExecuteDate] [VARCHAR](14) NULL,
	[ExecuteUser] [VARCHAR](150) NULL,
	[ExecuteEmployeeID] [VARCHAR](8) NULL,
	[WorkFlowStatus] [VARCHAR](3) NULL,
	[TaskStatus] [VARCHAR](4) NULL,
	[ConditionResult] [VARCHAR](500) NULL,
	[TaskFrom] [VARCHAR](50) NULL,
	[TaskTo] [VARCHAR](50) NULL,
	[FlowType] [VARCHAR](3) NULL,
	[MailSent] [VARCHAR](1) NULL
	,EmployeeID VARCHAR(8)
)

INSERT INTO @TRunTask
SELECT DISTINCT runtask.*, twf.EmployeeID
FROM PCMWFRUNTASK AS runtask
INNER JOIN @t_wf AS twf
	ON runtask.RegID = twf.RegID
WHERE (TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR WorkFlowStatus = 'F')
	AND runtask.BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
	AND (runtask.BizFlowID = @BizFlowID OR @BizFlowID = '')

INSERT INTO @t_result
SELECT DISTINCT temp.*, runtask.RegID, runtask.BizFlowID, runtask.Sequence, TaskIDRun, runtask.TaskStatus, runtask.ExecuteEmployeeID, pa1.FullName AS ExecuteEmpName 
FROM @TRunTask AS runtask
LEFT JOIN PHRPA0001 AS pa1
	ON runtask.ExecuteEmployeeID = pa1.EmployeeID
	AND pa1.StartDate <= @now AND pa1.EndDate >= @now
LEFT JOIN @t_emp AS temp
	ON runtask.EmployeeID = temp.EmployeeID
INNER JOIN ( SELECT RegID, BizFlowID, MAX(Sequence) AS MaxSeq FROM @TRunTask GROUP BY RegID, BizFlowID ) AS TMaxSeq
	ON TMaxSeq.BizFlowID = runtask.BizFlowID AND TMaxSeq.RegID = runtask.RegID
WHERE TMaxSeq.MaxSeq = runtask.Sequence
ORDER BY runtask.RegID, runtask.BizFlowID ASC

INSERT INTO @t_result
SELECT DISTINCT EmployeeID ,EmployeeName ,DivID ,DivDesc ,Organization ,OrgDesc ,Position ,PosDesc ,Job ,JobDesc ,pa47.RegID ,BizFlowID ,pa47.[Sequence] ,histask.TaskIDRun ,pa47.TaskStatus ,ExecuteEmployeeID ,ExecuteEmpName
FROM @t_pa47 AS pa47
INNER JOIN (SELECT RegID, MAX(PICSeq) AS MaxPICSeq FROM @t_pa47 GROUP BY RegID) AS MaxPIC
	ON pa47.RegID = MaxPIC.RegID
INNER JOIN 	( SELECT a.* FROM @t_histask AS a
			INNER JOIN (SELECT RegID, MAX(Sequence) AS MaxSeq FROM @t_histask GROUP BY RegID) AS b
				ON a.RegID = b.RegID 
			WHERE a.Sequence = b.MaxSeq ) AS histask
	ON histask.RegID = pa47.RegID
WHERE LEFT(pa47.StartDate,4) = @Period AND MaxPIC.MaxPICSeq = pa47.PICSeq

SELECT * FROM @t_result