ECC Project Manufacturing, Sales Orders

select
x.*
from
(
select * from ( SELECT  /*+ no_expand */    'PROJ_SO' || '-' || ool.project_id || '-' || NVL( TO_CHAR(ool.task_id), 'NO_TASK' ) || '-' ||  ooh.header_id || '-' || ool.line_id AS ecc_spec_id, 'PROJ_SO' AS record_type,  otype.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( ool.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ooh.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( otype.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( oterm.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( mif.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ship_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( ship_loc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ship_party_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( ship_acct_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bill_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( bill_loc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bill_party_site.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( bill_acct_site.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,  ool.line_id AS so_line_id, ooh.header_id AS so_header_id, TO_CHAR(ooh.order_number) AS so_number,  oe_flex_util.get_concat_value(ool.line_number, ool.shipment_number, ool.option_number, ool.component_number, ool.service_number) AS so_line_number,  ooh.cust_po_number AS cust_po_number, ooh.order_category_code AS so_category, otype.name AS so_type,  mif.segment1 AS item_number, msitl.description AS item_description, ool.end_item_unit_number AS end_item_unit_number,  lookup1.meaning AS so_freight_terms, oterm.name AS so_payment_terms,  hca.cust_account_id               AS so_customer_id,  hca.account_number                AS so_customer_number,  hp.party_name                     AS so_customer_name,  ooh.ordered_date AS so_order_date,  ooh.request_date AS so_request_date, DECODE( ool.ordered_quantity, 0, 'Yes', 'No' ) AS so_cancelled_flag,  DECODE( ship_loc.city, NULL , NULL , ship_loc.city || ', ' ) || DECODE( ship_loc.postal_code, NULL , NULL , ship_loc.postal_code || ', ' ) || DECODE( ship_loc.country, NULL , NULL , ship_loc.country ) AS ship_to_location,  oe_org_addresses.ship_to_org_id AS ship_to_location_id, NVL( ool.shipping_method_code, ooh.shipping_method_code ) AS ship_method,  lookup2.meaning AS ship_priority, DECODE( ooh.partial_shipments_allowed, 'Y', 'Yes', 'N', 'No' ) AS partial_ship,  oe_org_addresses.bill_to_org_id AS bill_to_location_id,  DECODE( bill_loc.city, NULL , NULL , bill_loc.city || ', ' ) || DECODE( bill_loc.postal_code, NULL , NULL , bill_loc.postal_code || ', ' ) || DECODE( bill_loc.country, NULL , NULL , bill_loc.country ) AS bill_to_location,  DECODE( ool.line_category_code, 'RETURN', (-1) * ( NVL( ool.ordered_quantity, 0 ) - NVL( ool.cancelled_quantity, 0 ) ) , (NVL( ool.ordered_quantity, 0 ) - NVL( ool.cancelled_quantity, 0 ) )) * NVL( ool.unit_selling_price, 0 ) AS so_line_amount,  ooh.transactional_curr_code AS currency_code,  DECODE( NVL( ool.tax_exempt_flag, ooh.tax_exempt_flag ) ,  'S', 'Standard', 's', 'Standard', 'E', 'Exempt', 'e', 'Exempt', 'R', 'Required', 'r', 'Required',  NVL( ool.tax_exempt_flag, ooh.tax_exempt_flag ) ) AS so_tax_exempt,  hou.name AS warehouse  FROM pa_projects_all pp, pa_tasks pt, oe_order_lines_all ool, oe_order_headers_all ooh, oe_transaction_types_tl otype, ra_terms_tl oterm,  mtl_system_items_b mif, mtl_system_items_tl msitl, oe_lookups lookup1, oe_lookups lookup2, hz_cust_site_uses_all ship_site, hz_locations ship_loc,  hz_party_sites ship_party_site, hz_cust_acct_sites_all ship_acct_site, hz_cust_site_uses_all bill_site, hz_locations bill_loc,  hz_party_sites bill_party_site, hz_cust_acct_sites_all bill_acct_site, hr_organization_units hou, ( SELECT ool.line_id, NVL( ool.ship_to_org_id, ooh.ship_to_org_id ) AS ship_to_org_id,  NVL( ool.invoice_to_org_id, ooh.invoice_to_org_id ) AS bill_to_org_id  FROM oe_order_lines_all ool, oe_order_headers_all ooh       ,pjm_project_parameters  p31  WHERE ooh.header_id = ool.header_id  AND ool.project_id = p31.project_id ) oe_org_addresses,  hz_parties hp,  hz_cust_accounts hca  ,pjm_project_parameters p3     WHERE pp.start_date >= NVL(to_date('','RRRR/MM/DD HH24:MI:SS'), pp.start_date) AND pp.project_type != 'AWARD_PROJECT'  AND pp.template_flag = 'N'  AND pp.project_id = p3.project_id  AND pp.project_id = ool.project_id  AND pt.task_id(+) = ool.task_id  AND ooh.header_id = ool.header_id  AND oterm.term_id(+) = ooh.payment_term_id  AND otype.transaction_type_id = ooh.order_type_id  AND hca.cust_account_id (+) = ooh.sold_to_org_id  AND hca.party_id = hp.party_id(+)  AND mif.inventory_item_id = ool.inventory_item_id  AND mif.organization_id = ool.ship_from_org_id  AND msitl.inventory_item_id = mif.inventory_item_id  AND msitl.organization_id = mif.organization_id  AND msitl.language = otype.language  AND lookup1.lookup_code(+) = ooh.freight_terms_code  AND lookup1.lookup_type(+) = 'FREIGHT_TERMS'  AND lookup2.lookup_code(+) = ooh.shipment_priority_code  AND lookup2.lookup_type(+) = 'SHIPMENT_PRIORITY'  AND oe_org_addresses.line_id = ool.line_id  AND ship_site.site_use_id(+) = oe_org_addresses.ship_to_org_id  AND ship_site.cust_acct_site_id = ship_acct_site.cust_acct_site_id(+)  AND ship_acct_site.party_site_id = ship_party_site.party_site_id(+)  AND ship_party_site.location_id = ship_loc.location_id(+)  AND NVL( ship_site.org_id, -99 ) = NVL( ship_acct_site.org_id, -99 )  AND bill_site.site_use_id(+) = oe_org_addresses.bill_to_org_id  AND bill_site.cust_acct_site_id = bill_acct_site.cust_acct_site_id(+)  AND bill_acct_site.party_site_id = bill_party_site.party_site_id(+)  AND bill_party_site.location_id = bill_loc.location_id(+)  AND NVL( bill_site.org_id, -99 ) = NVL( bill_acct_site.org_id, -99 )  AND hou.organization_id(+) = ool.ship_from_org_id  AND otype.language = NVL( oterm.language, otype.language )  AND OTYPE.LANGUAGE in ('US')  AND oterm.language (+) IN ('US')  AND msitl.language (+) IN ('US') )  PIVOT(MAX(so_payment_terms) AS so_payment_terms, MAX(so_type) AS so_type  , MAX(item_description) AS item_description FOR LANGUAGE IN ('US' "US"))
) x
where
2=2