[E-Bussiness Suite] Query All Resource PSI - Project Costing

Deskripsi 
     Untuk mendapatkan semua item resource per-project dari PSI (Project Costing) beserta nilai dari commitment , actual , cost budget , Actual + Commitment berdasarkan task_number level 3.

Howto
SELECT distinct PPAH.project_id
      ,PPAH.task_id
      ,PT.WBS_LEVEL
      ,PT.top_task_id
      --,PRLM.parent_member_id
      ,PTK.parent_member_id
      ,PT.task_name
      ,PT.TASK_NUMBER
      ,PRLM.alias
      ,PRLM.expenditure_type as Resource_Name
      ,NVL(PPAB.BASE_RAW_COST_ITD,0) as ITD_Cost_Budget
      ,NVL(PPAA.RAW_COST_ITD,0) as ITD_Act_Cost
      ,NVL(PPAC.CMT_RAW_COST_ITD,0) as Commitment_ITD 
      ,(NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0)) as Total_Act_Com
      ,PPAB.BASE_RAW_COST_TOT 
      ,PPAB.ORIG_BURDENED_COST_TOT 
      ,(NVL(PPAB.BASE_RAW_COST_TOT,0) - NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0) ) as estimate_to_complete
      
FROM pa_project_accum_headers PPAH
    ,pa_resource_list_members PRLM
    ,pa_project_accum_actuals PPAA
    ,PA_PROJECT_ACCUM_BUDGETS PPAB
    ,PA_PROJECT_ACCUM_COMMITMENTS PPAC
    ,pa_tasks PT
    ,(select distinct prlm.parent_member_id, pt.task_name
      from pa_resource_list_members PRLM
          ,pa_project_accum_headers PPAH
          ,pa_tasks PT
    
     where PT.project_id = :Project_ID
     and wbs_level = '2'
     and PT.PROJECT_ID = PPAH.PROJECT_ID
     AND PT.TASK_ID = PPAH.TASK_ID  
     and PPAH.resource_id=PRLM.resource_id
     order by task_name) PTK
    
where PT.project_id = :Project_ID
and wbs_level = '3'
and PT.PROJECT_ID = PPAH.PROJECT_ID
AND PT.TASK_ID = PPAH.TASK_ID  
and PPAH.resource_id=PRLM.resource_id
and PPAH.PROJECT_ACCUM_ID=PPAA.PROJECT_ACCUM_ID(+)
and PPAH.PROJECT_ACCUM_ID=PPAB.PROJECT_ACCUM_ID(+)
and PPAH.PROJECT_ACCUM_ID=PPAC.PROJECT_ACCUM_ID(+)
and PRLM.parent_member_id = ptk.parent_member_id(+)
--to see minus budget you add this line below .....
--and (NVL(PPAB.BASE_RAW_COST_TOT,0) - NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0) ) < 0
order by pt.task_number 
;