Query To Get The Project Financial Plan CI Impact Details

SELECT pbv.ci_id,

  NVL(pe.name ,p.name) task_name,

  rlm.alias planning_resource_name,

  (NVL(rac.total_projfunc_burdened_cost,0)+NVL(NULL,0)) total_cost,

  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_txn_raw_cost)             AS raw_cost_txn_cur,

  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_raw_cost_rate_override, to_number(NULL)),5)    AS raw_cost_rate_override,

  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_projfunc_raw_cost)        AS raw_cost_proj_func_cur,

  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_average_burden_cost_rate, to_number(NULL)),5)  AS avg_burd_cost_rate,

  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_burden_cost_rate_override, TO_NUMBER(NULL)),5) AS burd_cost_rate_override,

  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_projfunc_burdened_cost)   AS burd_cost_proj_func_cur,

  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_txn_burdened_cost) AS burd_cost_txn_cur,

  NVL(pe.element_number, p.segment1)

  || '('

  || NVL(pe.name ,p.name)

  || ')' task_name_num,

  NVL(ppe.element_version_id,0) element_version_id

FROM pa_resource_assignments pra,

  pa_proj_elements pe,

  pa_resource_list_members rlm,

  pa_resource_asgn_curr rac,

  pa_budget_versions pbv,

  pa_fin_plan_types_b pfpt,

  pa_proj_fp_options po,

  pa_proj_element_versions ppe,

  pa_control_items ci,

  pa_projects_all p

WHERE pra.resource_assignment_id       = rac.resource_assignment_id

and pra.resource_list_member_id        = rlm.resource_list_member_id

and p.project_id                       = nvl(:p_project_id,p.project_id)

and pra.project_id                     = p.project_id

AND pbv.budget_version_id              = rac.budget_version_id

and pbv.budget_version_id              = pra.budget_version_id

and pbv.project_id                     = pra.project_id

and pbv.ci_id                          = ci.ci_id

AND pbv.ci_id                          = nvl(:p_change_order_id,pbv.ci_id)

AND pbv.fin_plan_type_id               = pfpt.fin_plan_type_id

AND pbv.budget_version_id              = po.fin_plan_version_id

AND po.fin_plan_option_level_code      = 'PLAN_VERSION'

and po.fin_plan_version_id             = pbv.budget_version_id

AND pra.task_id                        = pe.proj_element_id (+)

and ppe.parent_structure_version_id (+)= pa_project_structure_utils.get_fin_struc_ver_id(pe.project_id)

AND pe.proj_element_id                 = ppe.proj_element_id (+)

AND ( rac.total_quantity              IS NOT NULL

OR rac.txn_burden_cost_rate_override  IS NOT NULL

OR rac.total_txn_burdened_cost        IS NOT NULL

OR rac.txn_bill_rate_override         IS NOT NULL

OR rac.total_txn_revenue              IS NOT NULL

or rac.txn_raw_cost_rate_override     is not null

OR rac.total_txn_raw_cost             IS NOT NULL);