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

--DECLARE @Period VARCHAR(20) = '2024'
--DECLARE @Organization VARCHAR(20) = '00000001'
--DECLARE @EmployeeID VARCHAR(20) = ''

DECLARE @now VARCHAR(20) = FORMAT(GETDATE(), 'yyyyMMdd')
DECLARE @PeiodeEnDt VARCHAR(20) = @Period + '1231'

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_wf TABLE
(
	RegID VARCHAR(20)
	,BizFlowID VARCHAR(250)
	,TaskRunID VARCHAR(250)
	,EmployeeID VARCHAR(20)
)
INSERT INTO @t_wf
SELECT RegID, BizFlowID, TaskIDRun, ExecuteEmployeeID
FROM PCMWFRUNTASK as runtask
INNER JOIN @t_emp as temp
	ON runtask.ExecuteEmployeeID = temp.EmployeeID
WHERE TaskIDRun = 'create'
	AND BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
	AND LEFT(InboundDate,4) = @Period
ORDER BY RegID ASC

SELECT temp.*, runtask.RegID, runtask.BizFlowID, runtask.Sequence, TaskIDRun, runtask.TaskStatus, runtask.ExecuteEmployeeID, pa1.FullName AS ExecuteEmpName
FROM PCMWFRUNTASK as runtask
INNER JOIN @t_wf as twf
	ON runtask.RegID = twf.RegID
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 twf.EmployeeID = temp.EmployeeID
where (TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR WorkFlowStatus = 'F')
	AND runtask.BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
ORDER BY runtask.RegID, runtask.BizFlowID, runtask.Sequence ASC

--SELECT temp.*, runtask.RegID, runtask.BizFlowID, runtask.Sequence, TaskIDRun, runtask.TaskStatus, runtask.ExecuteEmployeeID, pa1.FullName AS ExecuteEmpName
--FROM @t_emp AS temp
--LEFT JOIN @t_wf as twf
--	ON temp.EmployeeID = twf.EmployeeID
--LEFT JOIN PCMWFRUNTASK AS runtask
--	ON runtask.RegID = twf.RegID
--LEFT JOIN PHRPA0001 AS pa1
--	ON runtask.ExecuteEmployeeID = pa1.EmployeeID
--	AND pa1.StartDate <= @now AND pa1.EndDate >= @now
--where (TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR WorkFlowStatus = 'F')
--ORDER BY runtask.RegID, runtask.BizFlowID, runtask.Sequence ASC

--SELECT * FROM PCMWFRUNTASK 
--WHERE LEFT(InboundDate,4) = @Period AND BizFlowID = 'MD_Leave_SJ'
----where TaskIDRun IN ('create ','apv_ds', 'apv_nha', 'Complate')
--order by RegID desc