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>;
