ECC Project Manufacturing, Projects

select
x.*
from
(
select * from ( SELECT  /*+ leading(pppa) index(pp PA_PROJECTS_U1) */    'PROJ-' || pp.project_id   AS ecc_spec_id,	 GREATEST(     NVL(pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),   NVL(pps.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),   NVL(ppsc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),   NVL(ppah.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),    NVL(ppaa.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),    NVL(ppab.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),    NVL(ppac.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')),    NVL(NVL(ppm.last_update_date, pp.last_update_date), to_date('01-01-1990', 'DD-MM-YYYY'))) AS ecc_last_update_date,         pp.project_id                   AS project_id,         pp.segment1                     AS project_number,         pp.name                         AS project_name,         pp.description                  AS project_description,         pp.start_date                   AS project_start_date,         pp.completion_date              AS project_completion_date,         pp.project_status_code          AS project_status_code,         pps.project_status_name         AS project_status,         pp.project_type                 AS project_type,         hou1.name                       AS project_organization,         ppm.full_name                   AS project_manager,         pp.org_id                       AS org_id,         hou2.name                       AS operating_unit,         DECODE(ppsc.enabled_flag, 'Y', ppaa.revenue_ptd, NULL) AS proj_revenue_ptd,         DECODE(ppsc.enabled_flag, 'Y', ppaa.burdened_cost_ptd, NULL) AS proj_burdened_cost_ptd,         DECODE(ppsc.enabled_flag, 'Y', ppaa.revenue_ptd - ppaa.burdened_cost_ptd, NULL) AS proj_margin_ptd,  	     DECODE(ppsc.enabled_flag, 'Y', DECODE(ppaa.revenue_ptd, 0, 0,(ppaa.revenue_ptd - ppaa.burdened_cost_ptd) / ppaa.revenue_ptd * 100), NULL) AS proj_margin_ptd_percentage,         	DECODE(ppsc.enabled_flag, 'Y', ppaa.revenue_ytd, NULL) AS proj_revenue_ytd,   		 	DECODE(ppsc.enabled_flag, 'Y', ppaa.burdened_cost_ytd, NULL) AS proj_burdened_cost_ytd,       		 DECODE(ppsc.enabled_flag, 'Y', ppaa.revenue_ytd - ppaa.burdened_cost_ytd, NULL) AS proj_margin_ytd,         DECODE(ppsc.enabled_flag, 'Y', DECODE(ppaa.revenue_ytd, 0, 0, (ppaa.revenue_ytd - ppaa.burdened_cost_ytd) / ppaa.revenue_ytd * 100), NULL) AS proj_margin_ytd_percentage,         DECODE(ppsc.enabled_flag, 'Y', ppaa.revenue_itd, NULL) AS proj_revenue_itd,         DECODE(ppsc.enabled_flag, 'Y', ppaa.burdened_cost_itd, NULL) AS proj_burdened_cost_itd, DECODE(ppsc.enabled_flag, 'Y', ppaa.revenue_itd, - ppaa.burdened_cost_itd, NULL) AS proj_margin_itd,         DECODE(ppsc.enabled_flag, 'Y', DECODE(ppaa.revenue_itd, 0, 0,(ppaa.revenue_itd - ppaa.burdened_cost_itd) / ppaa.revenue_itd * 100), NULL) AS proj_margin_itd_percentage,         DECODE(ppsc.enabled_flag, 'Y', ppab.base_revenue_tot, NULL) AS proj_total_revenue_budget,         DECODE(ppsc.enabled_flag, 'Y', DECODE(SIGN(ppab.base_revenue_tot - ppaa.revenue_itd), -1, NULL, ppab.base_revenue_tot - ppaa.revenue_itd), NULL) AS proj_backlog,         DECODE(ppsc.enabled_flag, 'Y', DECODE(ppab.base_revenue_tot, 0, NULL, DECODE(SIGN(ppab.base_revenue_tot - ppaa.revenue_itd), -1, NULL,  					(ppab.base_revenue_tot - ppaa.revenue_itd) / ppab.base_revenue_tot * 100)), NULL) AS proj_backlog_percentage,         DECODE(ppsc.enabled_flag, 'Y', ppac.cmt_burdened_cost_ptd, NULL) AS proj_commitment_cost, 		 DECODE(ppsc.enabled_flag, 'Y', ppaa.burdened_cost_itd + NVL(ppac.cmt_burdened_cost_ptd, 0), NULL) AS proj_total_cost,         pp.project_currency_code        AS project_currency_code,         hou1.language                   AS language  FROM pa_projects_all pp,                          pa_project_statuses pps,                     pa_project_status_controls ppsc,             pa_project_accum_headers ppah,               pa_project_accum_actuals ppaa,               pa_project_accum_budgets ppab,               pa_project_accum_commitments ppac,           hr_all_organization_units_tl hou1,           hr_all_organization_units_tl hou2,      	   (SELECT DISTINCT project_id FROM pjm_project_parameters) pppa,       (           SELECT ppp.project_id,                 pap.full_name,                 GREATEST(pap.last_update_date, ppp.last_update_date) last_update_date         FROM per_all_people_f pap,              pa_project_players ppp         WHERE ppp.project_role_type = 'PROJECT MANAGER'           AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active AND NVL(ppp.end_date_active, SYSDATE + 1)           AND pap.person_id = ppp.person_id           AND TRUNC(SYSDATE) BETWEEN pap.effective_start_date AND pap.effective_end_date       ) ppm      WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date)   AND pp.project_type <> 'AWARD_PROJECT'    AND pp.template_flag = 'N'    AND pp.project_id = pppa.project_id     AND pps.project_status_code = pp.project_status_code    AND ppsc.project_status_code = pp.project_status_code    AND ppsc.action_code = 'STATUS_REPORTING'    AND ppah.project_id = pp.project_id    AND ppah.resource_list_member_id = 0    AND ppah.task_id = 0    AND ppaa.project_accum_id = ppah.project_accum_id    AND ppab.project_accum_id = ppah.project_accum_id    AND ppab.budget_type_code = 'AR'    AND ppac.project_accum_id = ppah.project_accum_id    AND hou1.organization_id = pp.carrying_out_organization_id    AND hou2.organization_id = pp.org_id    AND hou2.language = hou1.language    AND ppm.project_id (+) = pp.project_id  AND hou1.language IN ('US') )  PIVOT( MAX(project_organization) AS project_organization,  MAX(operating_unit) AS operating_unit FOR LANGUAGE IN ('US' "US"))
) x
where
2=2