ECC Projects, Projects Costing: PSI Summary

select
x.*
from
(
select * from
	(SELECT
        'PSI_PROJ_TASK_DS_SETUP' record_type,
        'PSI_PROJ_TASK_'
        || TO_CHAR(accum.org_id)
        || '-'
        || TO_CHAR(accum.project_id)
        || '-'
        || TO_CHAR(accum.task_id) ecc_spec_id,
        accum.project_id,
        accum.segment1 project_number,
        accum.name project_name,
        accum.org_id,
        accum.operating_unit,
        (
            SELECT
                name
            FROM
                hr_all_organization_units_tl o2
            WHERE
                o2.language = accum.language
                AND o2.organization_id = accum.proj_carry_out_org_id
        ) project_organization,
        accum.projfunc_currency_code currency,
        accum.project_status_name project_status,
        pa_utils.get_lookup_values('PROJECT_SYSTEM_STATUS',accum.project_system_status_code,accum.language) project_status_code,
        accum.project_type project_type,
        pa_utils.get_lookup_values('PROJECT TYPE CLASS',accum.project_type_class_code,accum.language) project_type_class,
        accum.project_type_class_code project_type_class_code,
        pa_project_parties_utils.get_project_manager_name(accum.project_id) project_manager,
        accum.start_date project_start_date,
        accum.completion_date project_completion_date,
        DECODE(accum.task_id,0,NULL,accum.task_id) task_id,
        accum.task_number,
        accum.task_name,
        accum.top_task_id,
        (
            SELECT
                task_name
            FROM
                pa_tasks pt1
            WHERE
                pt1.task_id = accum.top_task_id
        ) top_task_name,
        (
            SELECT
                task_number
            FROM
                pa_tasks pt1
            WHERE
                pt1.task_id = accum.top_task_id
        ) top_task_number,
        accum.parent_task_id,
        (
            SELECT
                task_number
            FROM
                pa_tasks pt2
            WHERE
                pt2.task_id = accum.parent_task_id
        ) parent_task_number,
        (
            SELECT
                task_name
            FROM
                pa_tasks pt2
            WHERE
                pt2.task_id = accum.parent_task_id
        ) parent_task_name,
        accum.wbs_level,
        DECODE(accum.task_id,0,NULL,DECODE(pa_task_utils.check_child_exists(accum.task_id),1,'Y',0,'N')) check_child_exists,
        accum.task_start_date,
        accum.task_completion_date,
        (
            SELECT
                per.full_name
            FROM
                per_all_people_f per
            WHERE
                per.person_id (+) = accum.task_manager_person_id
                AND trunc(SYSDATE) BETWEEN per.effective_start_date (+) AND per.effective_end_date (+)
        ) task_manager,
        (
            SELECT
                name
            FROM
                hr_all_organization_units_tl o3
            WHERE
                o3.language = accum.language
                AND o3.organization_id = accum.task_carry_out_org_id
        ) task_organization,
        accum.billable_flag billable_flag,
        (
            SELECT
                ind_rate_sch_name
            FROM
                pa_ind_rate_schedules
            WHERE
                ind_rate_sch_id = accum.cost_ind_rate_sch_id
        ) burden_schedule,
        DECODE(accum.project_type_class_code,'CONTRACT',pa_utils.get_lookup_values('YES_NO',accum.billable_flag,accum.language) ) is_task_billable,
        DECODE(accum.project_type_class_code,'CAPITAL',pa_utils.get_lookup_values('YES_NO',accum.billable_flag,accum.language) ) is_task_capitalizable,
        nvl(accum.expenditure_budget_tot,0) expenditure_budget_tot,
        nvl(accum.expenditure_budget_itd,0) expenditure_budget_itd,
        nvl(accum.raw_cost,0) raw_cost,
        nvl(accum.burdened_cost,0) burdened_cost,
        accum.labor_hrs labor_hours,
  DECODE(accum.project_type_class_code,'CAPITAL',DECODE(accum.capital_cost_type_code,'R',nvl(accum.billable_raw_cost,0),nvl(accum.billable_brdn_cost,0)) ) capitalizable_cost,
        nvl(accum.revenue_budget_tot,0) revenue_budget_tot,
        nvl(accum.revenue_budget_itd,0) revenue_budget_itd,
		nvl(accum.commitment_amount,0) commitment_amount,
        nvl(accum.billable_raw_cost,0) billable_raw_cost,
        nvl(accum.billable_brdn_cost,0) billable_burden_cost,
        DECODE(accum.capital_cost_type_code,'R',nvl(accum.billable_raw_cost,0),nvl(accum.billable_brdn_cost,0)) billable_cost,
        accum.fin_perc_cmplt,
        accum.est_to_cmplt,
        accum.budget_cost_variance,
        accum.summarization_exception,
		accum.last_update_date,
        accum.language
      FROM
     (
 SELECT
            p.org_id,
            p.project_id,
            p.project_type,
            p.name,
            p.segment1,
            p.pji_source_flag,
            p.project_status_code,
            p.carrying_out_organization_id proj_carry_out_org_id,
            p.start_date,
            p.completion_date,
            p.projfunc_currency_code,
            pah.task_id,
            NULL task_number,
            NULL task_name,
            NULL top_task_id,
            NULL parent_task_id,
            0 wbs_level,
            NULL task_start_date,
            NULL task_completion_date,
            NULL task_manager_person_id,
            NULL task_carry_out_org_id,
            NULL billable_flag,
            NULL cost_ind_rate_sch_id,
			ps.project_status_name,
			ps.project_system_status_code,
			ppta.project_type_class_code,
			ppta.capital_cost_type_code,
            nvl(paa.raw_cost_itd,0) raw_cost,
            nvl(paa.burdened_cost_itd,0) burdened_cost,
            nvl(paa.billable_raw_cost_itd,0) billable_raw_cost,
            nvl(paa.billable_burdened_cost_itd,0) billable_brdn_cost,
            nvl(paa.labor_hours_itd,0) labor_hrs,
            nvl(pabc.base_burdened_cost_tot,0) expenditure_budget_tot,
            nvl(pabc.base_burdened_cost_itd,0) expenditure_budget_itd,
            nvl(pabr.base_revenue_tot,0) revenue_budget_tot,
			nvl(pabr.base_revenue_itd,0) revenue_budget_itd,
            nvl(m.cmt_burdened_cost_itd,0) commitment_amount,
            round(DECODE(pabc.base_burdened_cost_tot,0,0, (paa.burdened_cost_itd / pabc.base_burdened_cost_tot) * 100)) fin_perc_cmplt,
            nvl(pabc.base_burdened_cost_tot,0) - ( nvl(paa.burdened_cost_itd,0) + nvl(m.cmt_burdened_cost_itd,0)) est_to_cmplt,
            nvl(pabc.base_burdened_cost_itd,0) - ( nvl(paa.burdened_cost_itd,0) + nvl(m.cmt_burdened_cost_itd,0)) budget_cost_variance,
            nvl(fnd_message.get_string('PA',pah.sum_exception_code),pah.sum_exception_code) summarization_exception,
			greatest(pah.last_update_date, nvl(paa.last_update_date,pah.last_update_date), nvl(m.last_update_date,pah.last_update_date),
			         nvl(pabc.last_update_date,pah.last_update_date), nvl(pabr.last_update_date,pah.last_update_date)) last_update_date,
			o1.name operating_unit,
			o1.language
        FROM
		    pa_projects_all p,
            pa_project_accum_headers pah,
            pa_project_accum_budgets pabc,
            pa_project_accum_budgets pabr,
            pa_project_accum_actuals paa,
            pa_project_accum_commitments m,
            pa_project_types_all ppta,
            pa_project_statuses ps,
            hr_all_organization_units_tl o1
        WHERE
            p.template_flag = 'N'
            AND pa_project_stus_utils.is_project_closed(p.project_id) = 'N'
            AND p.project_type <> 'AWARD_PROJECT'
            AND p.project_id = pah.project_id (+)
            AND pah.resource_list_id (+) = 0
			AND pah.task_id (+) = 0
            AND pah.project_accum_id = pabc.project_accum_id (+)
            AND pah.project_accum_id = pabr.project_accum_id (+)
            AND pah.project_accum_id = paa.project_accum_id (+)
            AND pah.project_accum_id = m.project_accum_id (+)
            AND pabc.budget_type_code (+) = 'AC'
            AND pabr.budget_type_code (+) = 'AR'
            AND p.project_type = ppta.project_type
            AND p.org_id = ppta.org_id
            AND p.org_id = o1.organization_id
			AND o1.language in ('US')
            AND nvl(ppta.sponsored_flag,'N') = 'N'
            AND p.project_status_code = ps.project_status_code
			AND exists (select /*+ push_subq no_unnest */  1  from pa_expenditure_items_all ei where ei.project_id = p.project_id and ei.last_update_date >= '12-DEC-21')
        UNION
 SELECT
            p.org_id,
            p.project_id,
            p.project_type,
            p.name,
            p.segment1,
            p.pji_source_flag,
            p.project_status_code,
            p.carrying_out_organization_id,
            p.start_date,
            p.completion_date,
            p.projfunc_currency_code,
            t.task_id,
            t.task_number,
            t.task_name,
            t.top_task_id,
            t.parent_task_id,
            t.wbs_level,
            t.start_date task_start_date,
            t.completion_date task_completion_date,
            t.task_manager_person_id,
            t.carrying_out_organization_id task_carry_out_org_id,
            t.billable_flag billable_flag,
            t.cost_ind_rate_sch_id,
			ps.project_status_name,
			ps.project_system_status_code,
			ppta.project_type_class_code,
			ppta.capital_cost_type_code,
            nvl(paa.raw_cost_itd,0) raw_cost,
            nvl(paa.burdened_cost_itd,0) burdened_cost,
            nvl(paa.billable_raw_cost_itd,0) billable_raw_cost,
            nvl(paa.billable_burdened_cost_itd,0) billable_brdn_cost,
            nvl(paa.labor_hours_itd,0) labor_hrs,
            nvl(pabc.base_burdened_cost_tot,0) expenditure_budget_tot,
            nvl(pabc.base_burdened_cost_itd,0) expenditure_budget_itd,
            nvl(pabr.base_revenue_tot,0) revenue_budget_tot,
			nvl(pabr.base_revenue_itd,0) revenue_budget_itd,
            nvl(m.cmt_burdened_cost_itd,0) commitment_amount,
            round(DECODE(pabc.base_burdened_cost_tot,0,0, (paa.burdened_cost_itd / pabc.base_burdened_cost_tot) * 100)) fin_perc_cmplt,
            nvl(pabc.base_burdened_cost_tot,0) - ( nvl(paa.burdened_cost_itd,0) + nvl(m.cmt_burdened_cost_itd,0)) est_to_cmplt,
            nvl(pabc.base_burdened_cost_itd,0) - ( nvl(paa.burdened_cost_itd,0) + nvl(m.cmt_burdened_cost_itd,0)) budget_cost_variance,
            nvl(fnd_message.get_string('PA',pah.sum_exception_code),pah.sum_exception_code) summarization_exception,
			greatest(pah.last_update_date, nvl(paa.last_update_date,pah.last_update_date), nvl(m.last_update_date,pah.last_update_date),
			         nvl(pabc.last_update_date,pah.last_update_date), nvl(pabr.last_update_date,pah.last_update_date)) last_update_date,
			o1.name operating_unit,
			o1.language
        FROM
		    pa_projects_all p,
			pa_tasks t,
            pa_project_accum_headers pah,
            pa_project_accum_budgets pabc,
            pa_project_accum_budgets pabr,
            pa_project_accum_actuals paa,
            pa_project_accum_commitments m,
            pa_project_types_all ppta,
            pa_project_statuses ps,
            hr_all_organization_units_tl o1
        WHERE
            p.template_flag = 'N'
            AND pa_project_stus_utils.is_project_closed(p.project_id) = 'N'
            AND p.project_type <> 'AWARD_PROJECT'
			AND p.project_id = t.project_id
            AND t.project_id = pah.project_id (+)
            AND t.task_id = pah.task_id (+)
            AND pah.resource_list_id (+) = 0
            AND pah.project_accum_id = pabc.project_accum_id (+)
            AND pah.project_accum_id = pabr.project_accum_id (+)
            AND pah.project_accum_id = paa.project_accum_id (+)
            AND pah.project_accum_id = m.project_accum_id (+)
            AND pabc.budget_type_code (+) = 'AC'
            AND pabr.budget_type_code (+) = 'AR'
            AND p.project_type = ppta.project_type
            AND p.org_id = ppta.org_id
            AND p.org_id = o1.organization_id
			AND o1.language in ('US')
            AND nvl(ppta.sponsored_flag,'N') = 'N'
            AND p.project_status_code = ps.project_status_code
			AND exists (select /*+ push_subq no_unnest */  1  from pa_expenditure_items_all ei where ei.project_id = p.project_id and ei.last_update_date >= '12-DEC-21')
			) accum
) PIVOT (
max(OPERATING_UNIT) as OPERATING_UNIT,
max(PROJECT_ORGANIZATION) as PROJECT_ORGANIZATION,
max(PROJECT_STATUS_CODE) as PROJECT_STATUS_CODE,
max(PROJECT_TYPE_CLASS) as PROJECT_TYPE_CLASS,
max(TASK_ORGANIZATION) as TASK_ORGANIZATION,
max(IS_TASK_BILLABLE) as IS_TASK_BILLABLE,
max(IS_TASK_CAPITALIZABLE) as IS_TASK_CAPITALIZABLE
for LANGUAGE in ('US' "US")) 
) x
where
2=2