This PA project classes report query retrieves comprehensive project data, including organization details, project attributes, classification codes, and funding flags. It ensures rule compliance and clean structure by joining multiple tables to present project type, status, and classification percentages.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query to retrieve project classification and project type details, ensuring clean structure and rule compliance.
-- Organization Information
HAOUTL1.NAME AS CARRYING_OUT_ORGANIZATION,
HAOUTL.NAME AS ORGANIZATION_UNIT_NAME,
PPAV.NAME AS PROJECT_NAME,
PPAV.SEGMENT1 AS PROJECT_NUMBER,
PPTV.PROJECT_TYPE,
PPAV.PROJECT_STATUS_CODE,
PPAV.START_DATE AS PROJECT_START_DATE,
PPAV.COMPLETION_DATE AS PROJECT_COMPLETION_DATE,
PPAV.CLOSED_DATE AS PROJECT_CLOSED_DATE,
PPAV.DESCRIPTION AS PROJECT_DESCRIPTION,
PPAV.PUBLIC_SECTOR_FLAG,
NVL(PPAV.SCHEDULED_DURATION, 0) AS RETENTION_PERCENTAGE,
PCCV.DESCRIPTION AS CLASS_CATEGORY_DESCRIPTION,
PCCV2.DESCRIPTION AS CLASS_CODE_DESCRIPTION,
PPC.CODE_PERCENTAGE AS CLASSIFICATION_PERCENTAGE,
PCCV.PICK_ONE_CODE_ONLY_FLAG AS ONE_CODE_ONLY_FLAG,
PPSV.PROJECT_STATUS_NAME AS PROJECT_STATUS,
PPAV.BUDGETARY_CONTROL_FLAG
FROM
PJF_PROJECT_CLASSES PPC,
PJF_PROJECTS_ALL_VL PPAV,
PJF_PROJECT_TYPES_VL PPTV,
PJF_PROJECT_STATUSES_VL PPSV,
PJF_CLASS_CATEGORIES_VL PCCV,
PJF_CLASS_CODES_VL PCCV2,
PJF_CLASS_CODES_B PCCB,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL1,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL
WHERE
1=1
AND PCCV.CLASS_CATEGORY_ID = PPC.CLASS_CATEGORY_ID
AND PCCV2.CLASS_CODE_ID = PPC.CLASS_CODE_ID
AND PCCB.CLASS_CODE_ID = PPC.CLASS_CODE_ID
AND PPAV.PROJECT_ID = PPC.PROJECT_ID
AND PPAV.PROJECT_TYPE_ID = PPTV.PROJECT_TYPE_ID
AND PPSV.PROJECT_STATUS_CODE = PPAV.PROJECT_STATUS_CODE
AND HAOUTL1.ORGANIZATION_ID = PPAV.CARRYING_OUT_ORGANIZATION_ID
AND HAOUTL1.LANGUAGE = USERENV('LANG')
AND PPAV.ORG_ID = HAOUTL.ORGANIZATION_ID
AND HAOUTL.LANGUAGE = USERENV('LANG')
AND NVL(PPAV.TEMPLATE_FLAG, 'N') = 'N';