ECC Project Manufacturing, Procurement Activities, SQL1

select
x.*
from
(
select * from ( SELECT /* PO */    'PROJ_PO' || '-' || dist.project_id || '-' || NVL( TO_CHAR(dist.task_id), 'NO_TASK' ) || '-' ||  poh.po_header_id || '-' || pol.po_line_id || '-' || poll.line_location_id || '-' || dist.po_distribution_id AS ecc_spec_id, 'PROJ_PO' AS record_type,  t.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')) ) 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 po_header_id, poh.segment1 AS po_number,  TO_CHAR(poh.po_header_id) AS ACTIVITY_ID,  t.type_name AS po_document_type, poh.revision_num AS po_revision_number, poh.revised_date AS po_revised_date,  poh.creation_date AS po_creation_date,  postatus_lkup.meaning AS po_status,  poh.vendor_id AS supplier_id, pv.vendor_name AS supplier_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, hrl2.location_code AS ship_to_location_name,  shipvia_lkup.freight_code_tl AS ship_via,  freight_lkup.meaning AS freight_term,  fob_lkup.meaning AS fob,  poh.agent_id AS po_agent_id, peo.full_name AS po_agent_name, poh.bill_to_location_id AS bill_to_location_id,  hrl3.location_code AS bill_to_location_name, poh.terms_id AS terms_id, term.name AS payment_term,  poh.currency_code AS currency, pol.po_line_id AS po_line_id, pol.line_num AS po_line_number,  plt.line_type AS po_line_type, hou2.name AS po_purchasing_org, SUBSTR( msi.segment1, 1, 40 ) AS item_number,  pol.item_revision AS item_revision,  NVL( msitl.description, pol.item_description ) AS item_description,  lineuom.unit_of_measure_tl AS po_line_uom,  pol.quantity AS po_line_quantity, pol.unit_price AS po_line_price, poll.line_location_id AS po_shipment_id,  poll.shipment_num AS po_shipment_number, ood.organization_code AS po_shipment_org,  hrl.location_code AS po_shipment_location,  shipmentuom.unit_of_measure_tl AS po_shipment_uom,  poll.quantity AS po_shipment_quantity, poll.quantity_received AS po_shipment_quantity_received,  poll.quantity_accepted AS po_shipment_quantity_accepted, poll.quantity_rejected AS po_shipment_quantity_rejected,  poll.quantity_billed AS po_shipment_quantity_billed, poll.quantity_cancelled AS po_shipment_quantity_cancelled,  poll.promised_date AS po_shipment_promised_date, poll.need_by_date AS po_shipment_need_by_date,  dist.po_distribution_id AS po_distribution_id, dist.distribution_num AS po_distribution_number,  dist.quantity_ordered AS po_distribution_quantity, dist.end_item_unit_number AS end_item_unit_number,  DECODE( pol.matching_basis, 'QUANTITY', dist.quantity_ordered * poll.price_override, 'AMOUNT', dist.amount_ordered ) AS po_dist_amount,  dist.destination_organization_id AS po_dist_dest_organization_id, hou3.name AS po_dist_dest_org_name  FROM pa_projects_all pp, pa_tasks pt, po_headers_all poh, po_lines_all pol, po_line_locations_all poll,  po_distributions_all dist,  po_line_types_tl plt,  hr_all_organization_units_tl hou2,  hr_all_organization_units_tl hou3, org_organization_definitions ood, hr_locations_all_tl hrl,  mtl_system_items_b msi,  mtl_system_items_tl msitl,  po_document_types_all_tl t,  fnd_lookup_values postatus_lkup,  po_vendors pv, po_vendor_sites_all pvs,  hr_locations_all_tl hrl2,  fnd_lookup_values freight_lkup,  fnd_lookup_values fob_lkup,  hr_locations_all_tl hrl3,  ap_terms_tl term,  org_freight_tl shipvia_lkup,  per_all_people_f peo,  mtl_units_of_measure_tl lineuom,  mtl_units_of_measure_tl shipmentuom  ,(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.type_lookup_code = 'STANDARD'  AND poh.approved_flag IN ('Y')  AND pol.po_line_id = dist.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 plt.line_type_id(+) = pol.line_type_id  AND hou2.organization_id(+) = poh.org_id  AND hou3.organization_id(+) = dist.destination_organization_id  AND ood.organization_id(+) = poll.ship_to_organization_id  AND hrl.location_id(+) = poll.ship_to_location_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 t.document_type_code IN ('PO', 'PA')  AND t.document_subtype = 'STANDARD'  AND t.org_id = poh.org_id  AND postatus_lkup.lookup_type = 'AUTHORIZATION STATUS'  AND postatus_lkup.lookup_code = nvl(poh.authorization_status, 'INCOMPLETE')  AND postatus_lkup.view_application_id = 201  AND postatus_lkup.security_group_id = 0  AND dist.project_id IS NOT NULL  AND pv.vendor_id = poh.vendor_id  AND pvs.vendor_id = poh.vendor_id  AND pvs.vendor_site_id = poh.vendor_site_id  AND hrl2.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 shipvia_lkup.organization_id( + ) = poh.org_id  AND shipvia_lkup.freight_code( + ) = poh.ship_via_lookup_code  AND peo.person_id = poh.agent_id   AND (trunc(SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date)  AND hrl3.location_id = poh.bill_to_location_id  AND term.term_id = poh.terms_id  AND lineuom.unit_of_measure( + ) = pol.unit_meas_lookup_code  AND shipmentuom.unit_of_measure( + ) = pol.unit_meas_lookup_code  AND t.language = term.language  AND term.language = hrl2.language  AND hrl2.language = hrl3.language  AND hrl3.language = NVL( hrl.language, hrl3.language )  AND term.language = NVL( hou3.language, term.language )  AND hrl2.language = NVL( hou2.language, hrl2.language )  AND t.language = nvl(msitl.language, t.language)  AND t.language = postatus_lkup.language  AND t.language = nvl(freight_lkup.language, t.language)  AND t.language = nvl(fob_lkup.language, t.language)  AND t.language = nvl(shipvia_lkup.language, t.language)  AND t.language = nvl(lineuom.language, t.language)  AND t.language = nvl(shipmentuom.language, t.language)  AND t.language = nvl(plt.language, t.language)  AND t.language IN ('US')  AND hrl.language (+) IN ('US')  AND hou3.language (+) IN ('US')  AND hou2.language (+) IN ('US')  AND msitl.language (+) IN ('US')  AND postatus_lkup.language IN ('US')  AND freight_lkup.language( + ) IN ('US')  AND fob_lkup.language( + ) IN ('US')  AND shipvia_lkup.language( + ) IN ('US')  AND lineuom.language( + ) IN ('US')  AND shipmentuom.language( + ) IN ('US')  AND plt.language( + ) IN ('US') )  PIVOT(MAX(po_purchasing_org) AS po_purchasing_org, MAX(po_dist_dest_org_name) AS po_dist_dest_org_name, MAX(po_shipment_location) AS po_shipment_location, MAX(po_document_type) AS po_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 (freight_term) AS freight_term,  MAX (fob) AS fob,  MAX (ship_via) AS ship_via,  MAX (po_line_uom) AS po_line_uom,  MAX (po_shipment_uom) AS po_shipment_uom,  MAX (po_line_type) AS po_line_type  FOR LANGUAGE IN ('US' "US"))
) x
where
2=2