Use the below query to fetch list of Projects & key data needed by Business Analysts
SELECT
(SELECT name FROM HR_ORGANIZATION_UNITS_F_TL
WHERE organization_id = ppp.ORG_ID AND LANGUAGE = ‘US’) Business_Unit_Name
,(SELECT name FROM pjf_projects_all_vl p1
WHERE p1.project_id = ppp.created_from_project_id) Project_Template
,(SELECT project_type FROM pjf_project_types_vl
WHERE project_type_id = ppp.project_type_id) Project_Type
,SEGMENT1 Project_Number ,NAME Project_Name ,DESCRIPTION Project_Description
,(SELECT name FROM HR_ORGANIZATION_UNITS_F_TL
WHERE organization_id = ppp.CARRYING_OUT_ORGANIZATION_ID AND LANGUAGE = ‘US’) Project_Owning_Org
,(SELECT Project_Status_Name FROM PJF_PROJECT_STATUSES_TL
WHERE PROJECT_STATUS_CODE = ppp.PROJECT_STATUS_CODE AND LANGUAGE = ‘US’) Project_Status
,(SELECT pcc.class_code FROM PJF_CLASS_CATEGORIES_TL pcct,PJF_CLASS_CODES_TL pcc,pjf_project_classes ppc
WHERE ppp.project_id = ppc.project_id AND pcc.class_code_id = ppc.class_code_id
AND pcc.LANGUAGE = ‘US’ AND pcct.LANGUAGE = ‘US’ AND pcct.class_category_id = ppc.class_category_id
AND pcct.class_Category = ‘Project Classification_1’) Project_Classification_1
— repeat this for as many classifications you may have
,TO_CHAR(START_DATE, ‘DD-MM-YYYY’) Project_Start_Date
,TO_CHAR(COMPLETION_DATE, ‘DD-MM-YYYY’) Project_End_Date
,PROJECT_CURRENCY_CODE Project_Curr
,ALLOW_CROSS_CHARGE_FLAG Project_Cross_Charge_Flag
FROM pjf_projects_all_vl ppp
WHERE ppp.template_flag = ‘N’;
PS: Subquery is just my personal choice & comfort area. You can denormalize as you please.