Link Tables Between Workflow Table (FA_FUSION_SOAINFRA.WFTASK) and Projects Table (PJF_PROJECTS_ALL_B)

 1. PROJECT STATUS NOTIFICATION APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ProjectStatusNotificationApproval'
and identificationkey = (select project_id from pjf_projects_all_b where segment1 = <Project Number>);

2. FORECAST APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ApprovePlanVersion'
and identificationkey = (select plan_version_id from PJO_PLAN_VERSIONS_B where project_id = (select project_id from pjf_projects_all_b where segment1 = <Project Number>));

 

3. CONTRACT APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ContractsApproval' and taskid in (select task_id from OKC_K_HEADERS_ALL_B where contract_number = <Contract Number>); 

 

To join the contracts table with the projects table, you need the following SQL:

SELECT OKH.CONTRACT_NUMBER
,OKHT.COGNOMEN AWARD_NAME
,PPAB.SEGMENT1 PROJ_NUM
FROM fusion.OKC_K_HEADERS_ALL_B OKH
, fusion.OKC_K_HEADERS_TL OKHT
, fusion.GMS_AWARD_HEADERS_B GAH
, fusion.GMS_AWARD_PROJECTS GAP
, fusion.PJF_PROJECTS_ALL_B PPAB
WHERE GAH.ID = OKH.ID
AND OKH.ID = OKHT.ID
AND OKH.ID = GAP.AWARD_ID
AND GAP.PROJECT_ID = PPAB.PROJECT_ID
AND OKH.CONTRACT_NUMBER = <Contract Number>;