ECC Project Manufacturing, Commitments

select
x.*
from
(
select * from ( SELECT  /*+ leading(ppp pp) index(pp PA_PROJECTS_U1) */  'PROJ_CMT-' || pp.project_id || '-' || NVL( TO_CHAR(pt.task_id), 'NO_TASK' ) || '-' || pct.cmt_line_id AS ecc_spec_id,  GREATEST( pp.last_update_date, NVL( pt.last_update_date, pp.last_update_date ) , pct.last_update_date, ptad.last_update_date,  NVL( fca.last_update_date, pp.last_update_date ) , NVL( fcr.last_update_date, pp.last_update_date ) ,  NVL( fcp.last_update_date, pp.last_update_date ) ) AS ecc_last_update_date,  pp.project_id AS project_id, pp.segment1 AS project_number, pp.org_id AS org_id, pt.task_id AS task_id,  pt.task_number AS task_number, pct.cmt_line_id AS cmt_line_id, pct.vendor_name AS supplier_name, pct.cmt_creation_date AS cmt_date, flv1.meaning AS cmt_type, pct.cmt_number AS cmt_number, pct.cmt_line_number AS cmt_line_number, pct.tot_cmt_raw_cost AS cmt_amount,  pct.tot_cmt_burdened_cost AS cmt_burdened_cost, pct.cmt_approved_date AS cmt_approved_date, pct.cmt_promised_date AS cmt_promised_date,  pct.cmt_need_by_date AS cmt_need_by_date, pct.description AS description, pct.expenditure_type AS exp_type, hou.name AS exp_org,  DECODE( pct.cmt_approved_flag, 'y', 'yes', 'n', 'no' ) AS cmt_approved, pct.tot_cmt_quantity AS cmt_quantity, pct.unit_of_measure AS uom, pct.cmt_requestor_name AS cmt_requestor, pct.cmt_buyer_name AS cmt_buyer, flv2.meaning AS cmt_transaction_source, pct.denom_currency_code AS denom_currency_code, pct.denom_raw_cost AS denom_raw_cost,  pct.denom_burdened_cost AS denom_burdened_cost, pct.acct_currency_code AS acct_currency_code, pct.acct_raw_cost AS acct_raw_cost,  pct.acct_burdened_cost AS acct_burdened_cost, pct.acct_rate_date AS acct_rate_date, pct.acct_rate_type AS acct_rate_type,  ROUND( pct.acct_exchange_rate, NVL( fca.extended_precision, NVL( fca.precision, 5 ) ) ) AS acct_exchange_rate,  pct.receipt_currency_code AS receipt_currency_code,  TO_CHAR(pct.receipt_currency_amount, fnd_currency_cache.get_format_mask(pct.receipt_currency_code, 40)) AS receipt_currency_amount,  ROUND( pct.receipt_exchange_rate, NVL( fcr.extended_precision, NVL( fcr.precision, 5 ) ) ) AS receipt_exchange_rate,  pct.project_currency_code AS project_currency_code, pct.project_rate_date AS project_rate_date, pct.project_rate_type AS project_rate_type,  ROUND( pct.project_exchange_rate, NVL( fcp.extended_precision, NVL( fcp.precision, 5 ) ) ) AS project_exchange_rate,  fm.message_text AS cmt_rejection_code, hou.language AS language  FROM pa_projects_all pp, pa_tasks pt, pa_commitment_txns pct, pa_txn_accum_details ptad, hr_all_organization_units_tl hou,  fnd_lookup_values flv1, fnd_lookup_values flv2, fnd_currencies fca, fnd_currencies fcr, fnd_currencies fcp, fnd_new_messages fm  , (SELECT DISTINCT project_id FROM pjm_project_parameters) ppp     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 = ppp.project_id  AND pp.project_id = pct.project_id  AND pt.task_id(+) = pct.task_id  AND ptad.line_type = 'M'  AND ptad.cmt_line_id = pct.cmt_line_id  AND hou.organization_id = pct.organization_id  AND flv1.view_application_id =275  AND flv1.security_group_id =0  AND flv1.lookup_type = 'COMMITMENT LINE TYPE'  AND flv1.lookup_code = pct.line_type  AND flv1.language = hou.language  AND flv2.view_application_id =275  AND flv2.security_group_id =0  AND flv2.lookup_type = 'COMMITMENT TXN SOURCE'  AND flv2.lookup_code = pct.transaction_source  AND flv2.language = hou.language  AND fca.currency_code(+) = pct.acct_currency_code  AND fcr.currency_code(+) = pct.receipt_currency_code  AND fcp.currency_code(+) = pct.project_currency_code  AND fm.application_id(+) =275  AND fm.message_name(+) = pct.cmt_rejection_code  AND (fm.language_code IS NULL  OR fm.language_code = hou.language)  AND hou.language IN ('US')  AND fm.language_code (+) IN ('US') ) PIVOT( MAX(cmt_type) AS cmt_type, MAX(exp_org) AS exp_org,   MAX(cmt_transaction_source) AS cmt_transaction_source,  MAX(cmt_rejection_code) AS cmt_rejection_code FOR LANGUAGE IN ('US' "US"))
) x
where
2=2