Query to get Projects and Tasks Details – Fusion
SELECT TO_CHAR(PPA.PROJECT_ID) PROJECT_ID
,TO_CHAR(PPA.SEGMENT1) PROJECT_NUMBER
,PPA.NAME PROJECT_NAME
,PPA.DESCRIPTION
,PPA.START_DATE
,PPA.COMPLETION_DATE
,PPA.CLOSED_DATE
,PTV.TASK_NUMBER
,TO_CHAR(PTV.TASK_ID) TASK_ID
,TO_CHAR(PPA.CARRYING_OUT_ORGANIZATION_ID) ORGANIZATION_ID
,HRU.NAME ORGANIZATION_NAME
,PPT.PROJECT_TYPE PROJECT_TYPE
,PPA.GROUP_SPACE_TEMPLATE_NAME
,PPA.PM_PROJECT_REFERENCE
,XLE.NAME “LEGAL ENTITY”
,PPA.ACTUAL_START_DATE “PROJECT START DATE”
,PPA.ACTUAL_FINISH_DATE “PROJECT FINISH DATE”
,PPA.PROJECT_STATUS_CODE
,PTV.PM_TASK_REFERENCE “SOURCE TASK REFERENCE”
,PTV.DESCRIPTION “TASK DESCRIPTION”
,PTV1.TASK_NUMBER “PARENT TASK NUMBER”
,PTC.TXN_CTRL_REFERENCE
,PTC.CHARGEABLE_FLAG
,PTC.BILLABLE_FLAG
,PTC.CAPITALIZABLE_FLAG
,PET.EXPENDITURE_TYPE_NAME
,PEC.EXPENDITURE_CATEGORY_NAME
,PCT.CLASS_CODE
,PCC.CLASS_CATEGORY
FROM
PJF_PROJECTS_ALL_VL PPA
,PJF_TASKS_V PTV
,PJF_TASKS_V PTV1
,HR_ORGANIZATION_UNITS HRU
,PJF_PROJECT_TYPES_TL PPT
,XLE_ENTITY_PROFILES XLE
,PJC_TRANSACTION_CONTROLS PTC
,PJF_EXP_TYPES_TL PET
,PJF_EXP_CATEGORIES_TL PEC
,PJF_CLASS_CATEGORIES_TL PCC
,PJF_CLASS_CODES_TL PCT
,PJF_PROJECT_CLASSES PPC
WHERE 1=1
AND PPA.PROJECT_ID=PTV.PROJECT_ID
AND PTV1.PARENT_TASK_ID(+) = PTV.TASK_ID
AND PPA.CARRYING_OUT_ORGANIZATION_ID=HRU.ORGANIZATION_ID(+)
AND PPT.PROJECT_TYPE_ID=PPA.PROJECT_TYPE_ID
AND PPA.SEGMENT1=<Project number>
AND XLE.LEGAL_ENTITY_ID = PPA.LEGAL_ENTITY_ID
AND PPA.PROJECT_ID=PTC.PROJECT_ID(+)
AND PTC.TASK_ID(+) = PTV.TASK_ID
AND PET.EXPENDITURE_TYPE_ID = PTC.EXPENDITURE_TYPE_ID(+)
AND PET.LANGUAGE =’US’
AND NVL(PEC.LANGUAGE,’US’) =’US’
AND PTC.EXPENDITURE_CATEGORY_ID = PEC.EXPENDITURE_CATEGORY_ID(+)
AND PPC.CLASS_CATEGORY_ID = PCC.CLASS_CATEGORY_ID(+)
AND PPC.PROJECT_ID(+) = PPA.PROJECT_ID
AND PPC.CLASS_CODE_ID = PCT.CLASS_CODE_ID(+)
ORDER BY TO_CHAR(PPA.PROJECT_ID);
