BANK KALTENG

??ALTER PROCEDURE [dbo].[CRPTBKTWUL]

(

	@Period VARCHAR(20)

	,@Organization VARCHAR(20)

)

AS

--DECLARE @Period VARCHAR(20) = '2025'

--DECLARE @Organization VARCHAR(20) = '00000002'

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)

	,div_id VARCHAR(20)

	,div_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, 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 

DECLARE @t_wf TABLE

(

	RegID VARCHAR(20)

	,BizFlowID VARCHAR(250)

	,TaskRunID VARCHAR(250)

	,EmployeeID VARCHAR(20)

	,WorkFlowStatus VARCHAR(20)

)

INSERT INTO @t_wf

SELECT RegID, BizFlowID, TaskIDRun, ExecuteEmployeeID, WorkFlowStatus

FROM PCMWFRUNTASK as runtask

INNER JOIN @t_emp as temp

	ON runtask.ExecuteEmployeeID = temp.EmployeeID

WHERE (TaskIDRun = 'create') 

	--AND BizFlowID = 'MD_Leave_SJ'

	AND LEFT(InboundDate,4) = @Period

order by EmployeeID, RegID, Sequence ASC

DECLARE @t_wf_detail TABLE

(

	RegID VARCHAR(20)

	,BizFlowID VARCHAR(250)

	,TaskRunID VARCHAR(250)

	,TaskStatus VARCHAR(20)

	,ExecuteEmployeeID VARCHAR(20)

	,WorkFlowStatus VARCHAR(20)

	,EmployeeID VARCHAR(20)

)

INSERT INTO @t_wf_detail

SELECT runtask.RegID, runtask.BizFlowID, runtask.TaskIDRun, runtask.TaskStatus, runtask.ExecuteEmployeeID, runtask.WorkFlowStatus, twf.EmployeeID

FROM PCMWFRUNTASK as runtask

INNER JOIN @t_wf as twf

	ON runtask.RegID = twf.RegID

where (runtask.TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR runtask.WorkFlowStatus = 'F')

order by RegID ASC

DECLARE @t_emp_detail TABLE

(

	EmployeeID VARCHAR(20)

	,EmployeeName VARCHAR(500)

	,div_id VARCHAR(20)

	,div_name VARCHAR(500)

	,Organization VARCHAR(20)

	,OrgDesc VARCHAR(500)

	,Position VARCHAR(20)

	,PosDesc VARCHAR(500)

	,Job VARCHAR(20)

	,JobDesc VARCHAR(500)

	,TotCreate DECIMAL(22,0)

	,TotFinish DECIMAL(22,0)

	,EmpWFStatus VARCHAR(20)

	,TotApvDs DECIMAL(22,0)

	,TotApvNha DECIMAL(22,0)

)

INSERT INTO @t_emp_detail

SELECT temp.*, tcreate.TotCreate, tfinish.TotFinish, 

		CASE WHEN tcreate.TotCreate = tfinish.TotFinish THEN 'F' ELSE 'N' END AS EmpWFStatus, ISNULL(TotApvDs,0) AS TotApvDs, ISNULL(TotApvNha,0) AS TotApvNha

FROM @t_emp AS temp

LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotCreate FROM @t_wf WHERE TaskRunID = 'create' group by EmployeeID ) AS tcreate

	ON temp.EmployeeID = tcreate.EmployeeID

LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotFinish FROM @t_wf_detail WHERE WorkFlowStatus = 'F' group by EmployeeID ) AS tfinish

	ON temp.EmployeeID = tfinish.EmployeeID

LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotApvDs FROM @t_wf_detail WHERE TaskRunID = 'apv_ds' group by EmployeeID ) AS tapv_ds