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,  ordercat_lkup.meaning 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,  freight_lkup.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,  ool.request_date AS so_request_date,  ool.promise_date AS so_promise_date,  ool.schedule_ship_date AS so_schedule_ship_date,  cancelflag_lkup.meaning  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,  shipmethod_lkup.meaning  AS ship_method,    shippriority_lkup.meaning  AS ship_priority,  partship_lkup.meaning  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,  taxexempt_lkup.meaning 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,  fnd_lookup_values freight_lkup,        fnd_lookup_values shippriority_lkup,   fnd_lookup_values taxexempt_lkup,      fnd_lookup_values ordercat_lkup,       fnd_lookup_values shipmethod_lkup,     fnd_lookup_values cancelflag_lkup,     fnd_lookup_values partship_lkup,       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,         NVL(ool.tax_exempt_flag, ooh.tax_exempt_flag) AS so_tax_exempt,         NVL(ool.shipping_method_code, ooh.shipping_method_code) AS ship_method  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  AND ool.ship_from_org_id = p31.organization_id  ) oe_org_addresses,  hz_parties hp,  hz_cust_accounts hca  ,( 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_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 freight_lkup.lookup_code(+) = ooh.freight_terms_code  AND freight_lkup.lookup_type(+) = 'FREIGHT_TERMS'  and freight_lkup.view_application_id(+) = 660  AND freight_lkup.security_group_id(+) = 0  AND shippriority_lkup.lookup_code(+) = ooh.shipment_priority_code  and shippriority_lkup.lookup_type(+) = 'SHIPMENT_PRIORITY'  and shippriority_lkup.view_application_id(+) = 660  AND shippriority_lkup.security_group_id(+) = 0  AND taxexempt_lkup.lookup_type(+) = 'ZX_EXEMPTION_CONTROL'  AND taxexempt_lkup.lookup_code(+) <> 'O'  AND taxexempt_lkup.lookup_code(+) = oe_org_addresses.so_tax_exempt  AND taxexempt_lkup.view_application_id(+) = 0  AND taxexempt_lkup.security_group_id(+) = 0  AND ordercat_lkup.lookup_code = ooh.order_category_code  AND ordercat_lkup.lookup_code IN ('ORDER','RETURN','MIXED')  AND ordercat_lkup.lookup_type = 'ORDER_CATEGORY'  AND ordercat_lkup.view_application_id(+) = 660  AND ordercat_lkup.security_group_id = 0  AND shipmethod_lkup.lookup_type(+) = 'SHIP_METHOD'  AND shipmethod_lkup.lookup_code(+) = oe_org_addresses.ship_method  AND shipmethod_lkup.view_application_id(+) = 3  AND shipmethod_lkup.security_group_id(+) = 0  AND cancelflag_lkup.lookup_type = 'YES_NO'  AND cancelflag_lkup.lookup_code = ool.cancelled_flag  and cancelflag_lkup.view_application_id(+) = 660  AND cancelflag_lkup.security_group_id = 0  AND partship_lkup.lookup_type(+) = 'YES_NO'  AND partship_lkup.lookup_code(+) = ooh.partial_shipments_allowed  AND partship_lkup.view_application_id(+) = 660  AND partship_lkup.security_group_id(+) = 0  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 = NVL(freight_lkup.language, otype.language)  AND otype.language = NVL(shippriority_lkup.language, otype.language)  AND otype.language = NVL(taxexempt_lkup.language, otype.language)  AND otype.language = ordercat_lkup.language  AND otype.language = NVL(shipmethod_lkup.language, otype.language)  AND otype.language = cancelflag_lkup.language  AND otype.language = NVL(partship_lkup.language, otype.language)  AND OTYPE.LANGUAGE in ('US')  AND oterm.language (+) IN ('US')  AND msitl.language (+) IN ('US')  AND freight_lkup.language(+)      IN ('US')  AND shippriority_lkup.language(+) IN ('US')  AND taxexempt_lkup.language(+)    IN ('US')  AND ordercat_lkup.language        IN ('US')  AND shipmethod_lkup.language(+)   IN ('US')  AND cancelflag_lkup.language      IN ('US')  AND partship_lkup.language(+)     IN ('US') )  PIVOT(MAX(so_payment_terms) AS so_payment_terms,  MAX(so_type) AS so_type  , MAX(item_description) AS item_description  ,MAX(so_freight_terms) AS so_freight_terms  ,MAX(ship_priority) as ship_priority  ,MAX(so_tax_exempt) AS so_tax_exempt  ,MAX(so_category) AS so_category  ,MAX(ship_method) AS ship_method  ,MAX(so_cancelled_flag) AS so_cancelled_flag  ,MAX(partial_ship) AS partial_ship FOR LANGUAGE IN ('US' "US"))
) x
where
2=2