ECC Project Manufacturing, Procurement Activities, SQL3

select
x.*
from
(
select * from ( SELECT  /* BR */    'PROJ_BR' || '-' || dist.project_id || '-' || NVL( TO_CHAR(dist.task_id), 'NO_TASK' ) || '-' ||  por.po_header_id || '-' || por.po_release_id || '-' || pol.line_num || '-' || poll.shipment_num || '-' || dist.distribution_num AS ecc_spec_id, 'PROJ_BR' AS record_type,  b.language AS language,  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( pol.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( poll.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( dist.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( por.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, por.po_header_id AS po_header_id, poh.segment1 AS po_number, t.type_name AS br_document_type, por.po_release_id AS br_release_id, por.release_num AS br_release_number,  TO_CHAR(por.po_header_id)|| ',' ||TO_CHAR(por.po_release_id)  AS ACTIVITY_ID,  pol.line_num AS po_line_number,  poll.shipment_num AS po_shipment_number, dist.distribution_num AS po_distribution_number, por.revision_num AS po_revision_number, por.revised_date AS po_revised_date, por.release_date AS br_release_date, por.printed_date AS br_printed_date, po_status_lkup.meaning AS po_status,  brclosestatus_lkup.meaning AS br_closure_status,  poh.vendor_id AS supplier_id,  pv.vendor_name AS supplier_name, poh.vendor_contact_id AS supplier_contact_id,  DECODE(pvc.last_name||', '||pvc.first_name, ', ', NULL, pvc.last_name||', '||pvc.first_name) AS supplier_contact_name,  poh.vendor_site_id AS supplier_site_id, pvs.vendor_site_code AS supplier_site_name, poh.ship_to_location_id AS ship_to_location_id,  hrl.location_code AS ship_to_location_name,  shipvia_lkup.freight_code_tl AS ship_via,  freight_lkup.meaning AS freight_terms,  fob_lkup.meaning AS fob,  por.agent_id AS po_agent_id, prf.full_name AS po_agent_name,  poh.bill_to_location_id AS bill_to_location_id, hrl2.location_code AS bill_to_location_name,  poh.terms_id AS terms_id, b.name AS payment_term, poh.currency_code AS currency,  DECODE( pol.matching_basis, 'QUANTITY', dist.quantity_ordered * poll.price_override, 'AMOUNT', dist.amount_ordered ) AS po_dist_amount,  SUBSTR( msi.segment1, 1, 40 ) AS item_number,  NVL(msitl.description, pol.item_description) AS item_description,  dist.end_item_unit_number AS end_item_unit_number  FROM pa_projects_all pp, pa_tasks pt, po_releases_all por, po_headers_all poh, po_lines_all pol,  po_line_locations_all poll, po_distributions_all dist, po_document_types_all_tl t,  fnd_lookup_values  po_status_lkup,  fnd_lookup_values  brclosestatus_lkup,  po_vendors pv,  po_vendor_contacts pvc,  po_vendor_sites_all pvs, hr_locations_all_tl hrl,  fnd_lookup_values freight_lkup,  fnd_lookup_values fob_lkup,  hr_locations_all_tl hrl2, per_all_people_f prf, ap_terms_tl b, mtl_system_items_b msi,  mtl_system_items_tl msitl,  org_freight_tl shipvia_lkup  ,(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 = dist.project_id  AND pt.task_id(+) = dist.task_id  AND poh.po_header_id = por.po_header_id  AND poh.type_lookup_code = 'BLANKET'  AND por.approved_flag IN ('Y', 'R')  AND dist.po_header_id = poh.po_header_id  AND dist.po_release_id = por.po_release_id  AND dist.po_line_id = pol.po_line_id  AND poh.po_header_id = pol.po_header_id  AND dist.line_location_id = poll.line_location_id  AND pol.po_line_id = poll.po_line_id  AND dist.org_id = pol.org_id  AND pol.item_id IS NOT NULL  AND t.document_type_code(+) = 'RELEASE'  AND t.document_subtype(+) = por.release_type  AND t.org_id(+) = por.org_id  AND po_status_lkup.lookup_type = 'AUTHORIZATION STATUS'  AND po_status_lkup.lookup_code = NVL( por.authorization_status, 'INCOMPLETE' )  AND po_status_lkup.view_application_id = 201    AND po_status_lkup.security_group_id = 0        AND brclosestatus_lkup.lookup_type = 'DOCUMENT STATE'  AND brclosestatus_lkup.lookup_code = nvl(por.closed_code, 'OPEN')  AND brclosestatus_lkup.view_application_id = 201  AND brclosestatus_lkup.security_group_id = 0  AND pv.vendor_id(+) = poh.vendor_id  AND pvc.vendor_contact_id(+) = poh.vendor_contact_id  AND pvc.vendor_site_id(+) = poh.vendor_site_id  AND pvs.vendor_id(+) = poh.vendor_id  AND pvs.vendor_site_id(+) = poh.vendor_site_id  AND hrl.location_id(+) = poh.ship_to_location_id  AND freight_lkup.lookup_type(+) = 'FREIGHT TERMS'  AND freight_lkup.lookup_code(+) = poh.freight_terms_lookup_code  AND freight_lkup.view_application_id(+) = 201  AND freight_lkup.security_group_id(+) = 0  AND fob_lkup.lookup_type(+) = 'FOB'  AND fob_lkup.lookup_code(+) = poh.fob_lookup_code  AND fob_lkup.view_application_id(+) = 201  AND fob_lkup.security_group_id(+) = 0  AND prf.person_id(+) = por.agent_id  AND trunc(SYSDATE) BETWEEN prf.effective_start_date AND prf.effective_end_date  AND hrl2.location_id(+) = poh.bill_to_location_id  AND b.term_id = poh.terms_id  AND msi.inventory_item_id(+) = pol.item_id  AND NVL( msi.organization_id, dist.destination_organization_id ) = dist.destination_organization_id  AND msitl.inventory_item_id (+) = msi.inventory_item_id  AND msitl.organization_id (+) = msi.organization_id  AND dist.project_id IS NOT NULL  AND shipvia_lkup.organization_id(+) = poh.org_id  AND shipvia_lkup.freight_code(+) = poh.ship_via_lookup_code  AND b.language = NVL( hrl.language, b.language )  AND b.language = NVL( hrl2.language, b.language )  AND b.language = NVL( t.language, b.language )  AND b.language = nvl(msitl.language, b.language)  AND b.language = po_status_lkup.language   AND b.language = brclosestatus_lkup.language  AND b.language = nvl(shipvia_lkup.language, b.language)  AND b.language = nvl(freight_lkup.language, b.language)  AND b.language = nvl(fob_lkup.language, b.language)  AND b.language IN ('US')  AND po_status_lkup.language IN ('US')  AND brclosestatus_lkup.language IN ('US')  AND shipvia_lkup.language(+) IN ('US')  AND freight_lkup.language(+) IN ('US')  AND fob_lkup.language(+) IN ('US') )  PIVOT(MAX(br_document_type) AS br_document_type, MAX(ship_to_location_name) AS ship_to_location_name, MAX(bill_to_location_name) AS bill_to_location_name, MAX(payment_term) AS payment_term, MAX(item_description) AS item_description, MAX (po_status) AS po_status, MAX (br_closure_status) AS br_closure_status, MAX (ship_via)          AS ship_via ,MAX (freight_terms)     AS freight_terms, MAX (fob)               AS fob FOR LANGUAGE IN ('US' "US"))
) x
where
2=2