ECC Project Manufacturing, Procurement Activities, SQL5

select
x.*
from
(
select * from ( SELECT /* RFQ */    'PROJ_RFQ' || '-' ||  line.project_id || '-' ||   NVL( TO_CHAR(line.task_id), 'NO_TASK' ) || '-' ||  poh.po_header_id || '-' ||  line.po_line_id || '-' ||   NVL(TO_CHAR(rfqv.vendor_id), 'NO_SUPP' ) || '-' ||  NVL(TO_CHAR(rfqv.vendor_site_id), 'NO_SITE' ) AS ecc_spec_id,  'PROJ_RFQ' AS record_type,  GREATEST( NVL( pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( pt.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( poh.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( line.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( rfqv.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.org_id AS org_id, pt.task_id AS task_id, pt.task_number AS task_number,  poh.po_header_id AS rfq_id, poh.segment1 AS rfq_number,  TO_CHAR(poh.po_header_id) AS ACTIVITY_ID,  rfqstatus_lkup.meaning AS rfq_status,  rfqstatus_lkup.language as language,  poh.agent_id AS po_agent_id,  prf.full_name AS po_agent_name, rfqv.vendor_id AS supplier_id, pov.vendor_name AS supplier_name,  rfqv.vendor_site_id AS supplier_site_id, pvs.vendor_site_code AS supplier_site_name, rfqv.vendor_contact_id AS supplier_contact_id,  DECODE( rfqv.vendor_contact_id, NULL , NULL , pvc.last_name || ' , ' || pvc.first_name ) AS supplier_contact_name,  poh.reply_date AS rfq_reply_date, rfq_close_date AS rfq_close_date  FROM pa_projects_all pp, pa_tasks pt,  fnd_lookup_values rfqstatus_lkup,  po_headers_all poh, po_lines_all line,  po_vendors pov, po_rfq_vendors rfqv,  po_vendor_sites_all pvs, (   SELECT c.vendor_contact_id vendor_contact_id, SUBSTR( hp.person_first_name, 1, 15 ) first_name,          SUBSTR( hp.person_middle_name, 1, 15 ) middle_name, SUBSTR( hp.person_last_name, 1, 15 ) last_name,          NVL( hp.person_pre_name_adjunct, hp.salutation ) prefix, SUBSTR( hp.person_title, 30 ) title    FROM ap_supplier_contacts c, hz_parties hp    WHERE c.per_party_id = hp.party_id  ) pvc,  per_all_people_f prf  ,(select distinct project_id from pjm_project_parameters ) p3     WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date) and pp.project_id = p3.project_id  AND pp.project_type <> 'AWARD_PROJECT'  AND pp.template_flag = 'N'  AND pp.project_id = line.project_id  AND pt.task_id(+) = line.task_id  AND rfqstatus_lkup.lookup_type = 'RFQ/QUOTE STATUS'  AND rfqstatus_lkup.lookup_code = nvl(poh.status_lookup_code, 'I')  AND rfqstatus_lkup.view_application_id = 201  AND rfqstatus_lkup.security_group_id = 0  AND poh.type_lookup_code = 'RFQ'  AND line.po_header_id = poh.po_header_id  AND rfqv.po_header_id(+) = poh.po_header_id  AND pov.vendor_id(+) = rfqv.vendor_id  AND pvs.vendor_site_id(+) = rfqv.vendor_site_id  AND pvc.vendor_contact_id(+) = rfqv.vendor_contact_id  AND prf.person_id(+) = poh.agent_id  AND trunc(SYSDATE) BETWEEN prf.effective_start_date AND prf.effective_end_date  AND rfqstatus_lkup.language IN ('US') )  PIVOT( MAX (rfq_status) AS rfq_status FOR LANGUAGE IN ('US' "US")) 
) x
where
2=2