ECC Procurement, Indirect Procurement, SQL3

select
x.*
from
(
select * from (select  /*+ leading(pha) index(pha PO_HEADERS_N9) */  ctxh.source_type
    || '-'
    || ctxh.org_id
    || '-'
    || ctxh.inventory_item_id
    || '-'
    || ctxh.po_line_id
    || '-'
    || ctxh.req_template_name
    || '-'
    || ctxh.req_template_line_num
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || pla.po_header_id
    || '-'
    || pla.po_line_id
    || '-'
    || NULL
    || '-'
    || 'STANDARD_PO' ecc_spec_id,
    /*catalog items*/
    ctxh.source_type cat_source_type,
    (select meaning from fnd_lookup_values
      where lookup_type = 'ICX_CAT_SOURCE_TYPE' and lookup_code = ctxh.source_type and language = houtl.language
	  and rownum<2) cat_source_type_code,
    DECODE(ctxh.source_type,'TEMPLATE',DECODE(least(LENGTH(ctxh.req_template_name) , 20),20,SUBSTR(ctxh.req_template_name,1,17)
          ||'...',ctxh.req_template_name),'INTERNAL_TEMPLATE',DECODE(least(LENGTH(ctxh.req_template_name) , 20),20,SUBSTR(ctxh.req_template_name,1,17)
          ||'...',ctxh.req_template_name),'QUOTATION',ICX_CAT_UTIL_PVT.get_message('ICX_CAT_QUOTATION_SOURCE','NUMBER',ctxh.document_number) ,
          'BLANKET',ICX_CAT_UTIL_PVT.get_message('ICX_CAT_BLANKET_SOURCE','NUMBER',ctxh.document_number),'GLOBAL_BLANKET',
          ICX_CAT_UTIL_PVT.get_message('ICX_CAT_BLANKET_SOURCE','NUMBER',ctxh.document_number), NULL) cat_source,
    nvl(avtlp.description, ctxh.description) cat_item_description,
    avtlp.long_description cat_long_description,
    ctxh.po_header_id cat_po_header_id,
    DECODE(ctxh.merged_source_type, 'REQ_TEMPLATE', NVL(ctxh.req_template_po_line_id, -2), ctxh.po_line_id) cat_po_line_id,
    ctxh.document_number cat_document_number,
    ctxh.line_num cat_line_number,
    ctxh.req_template_name cat_req_template_name,
    ctxh.req_template_line_num cat_req_template_line_num,
    (select unit_of_measure_tl from mtl_units_of_measure_tl WHERE unit_of_measure = ctxh.unit_meas_lookup_code and language =  houtl.language) cat_uom_code,
    (SELECT ip_category_name FROM icx_cat_items_ctx_hdrs_tlp WHERE po_line_id = pla.from_line_id AND org_id = houtl.organization_id AND LANGUAGE = houtl.LANGUAGE) cat_ip_category_name,
    ctxh.unit_price cat_unit_price,
    ctxh.currency_code cat_currency,
    DECODE(ctxh.supplier_part_num, '##NULL##', TO_CHAR(NULL), ctxh.supplier_part_num) cat_supplier_part_num,
    avtlp.manufacturer cat_manufacturer,
    av.manufacturer_part_num cat_manufacturer_part_num,
    (select full_name from per_all_people_f
      where person_id = ctxh.buyer_id
        and trunc(sysdate) BETWEEN effective_start_date
                               and effective_end_date) cat_buyer_name,
    av.lead_time cat_lead_time,
    av.unspsc cat_unspsc,
    ctxh.hazard_class cat_hazard_class,
    (select VENDOR_NAME from ap_suppliers aps where aps.VENDOR_ID = pha.VENDOR_ID)  review_supplier,
    /*suggestions*/
    /*iExpense*/
    /*standard po*/
    pha.po_header_id std_po_header_id,
    pla.po_line_id std_po_line_id,
    pha.segment1 std_po_document_number,
    pla.line_num std_po_line_number,
    REPLACE(pla.item_description,'|',',') std_po_line_description,
	replace(psgt.char1, ' ', UNISTR('|')) std_po_line_desc_for_tag,
    pla.from_header_id std_po_from_header_id,
    pla.from_line_id std_po_from_line_id,
    decode(nvl(pla.from_header_id, pla.contract_id), null, 'Yes', 'No') std_po_line_off_contract,
    pha.agent_id std_po_buyer_id,
    (select full_name from per_all_people_f
                       where person_id = pha.agent_id
                         and trunc(sysdate) BETWEEN effective_start_date
                                                and effective_end_date)
    std_po_buyer_name,
    pla.quantity std_po_loc_quantity,
    (select unit_of_measure_tl from mtl_units_of_measure_tl WHERE unit_of_measure = pla.unit_meas_lookup_code and language =  houtl.language) std_po_loc_uom_code,
    pla.unit_price std_po_loc_unit_price,
    psgt.num1 std_po_loc_amount,
    decode(nvl(pla.from_header_id, pla.contract_id), null,
           psgt.num1, 0) std_po_off_contract_loc_amount,
    (select min(need_by_date) from po_line_locations_all where po_line_id = pla.po_line_id)  std_po_loc_need_by_date,
    decode((select count(*) from po_line_locations_all pll where pll.po_line_id = pla.po_line_id),
            1, (select pll.ship_to_location_id from po_line_locations_all pll where pll.po_line_id = pla.po_line_id),
                -999) std_po_ship_location_id,
    decode((select count(*) from po_line_locations_all pll where pll.po_line_id = pla.po_line_id),
           1,
            (select loc_tl.location_code
            from hr_locations_all_tl loc_tl, po_line_locations_all pll
            where pll.ship_to_location_id = loc_tl.location_id
            and pll.po_line_id = pla.po_line_id
            and loc_tl.language =  houtl.language
            ),
            po_pcc_orders_util_pvt.get_fnd_message('PO_MULTIPLE_ATTR','201',houtl.language)
    ) std_po_ship_location,
     psgt.num1 spend_amount,
    decode(nvl(pla.from_header_id, pla.contract_id), null, 'Off-Contract', 'Contract') spend_type,
    /*action items*/
    /*common*/
    pha.approved_date spend_date,
    psgt.char2 cost_center,
    pla.org_id,
    houtl.name org_name,
    (select currency_code from gl_sets_of_books where fsp.set_of_books_id = set_of_books_id) functional_currency,
    pla.item_id inventory_item_id,
    (select concatenated_segments
       from mtl_system_items_kfv
      where pla.item_id = inventory_item_id and fsp.inventory_organization_id = organization_id) item_number,
    pla.category_id po_category_id,
    (select concatenated_segments from mtl_categories_kfv where pla.category_id = category_id) purchasing_category,
    pla.line_type_id,
    (SELECT line_type FROM po_line_types_tl where line_type_id = pla.line_type_id and language =  houtl.language) line_type,
    pha.currency_code transaction_currency,
	pha.VENDOR_ID supplier_id,
    (select VENDOR_NAME from ap_suppliers aps where aps.VENDOR_ID = pha.VENDOR_ID) supplier,
    (select vendor_site_code FROM AP_SUPPLIER_SITES_ALL where pha.VENDOR_SITE_ID = vendor_site_id) supplier_site,
    psgt.char3 DOC_AUTHORIZED_USER_IDS,
	(SELECT lkp.meaning
          FROM   fnd_lookup_values lkp
          WHERE  lkp.lookup_type = 'PO_DOCUMENT_CREATION_METHOD'
                 AND lkp.LANGUAGE =  houtl.language
                 AND lkp.lookup_code = pha.document_creation_method
				 and rownum<2) document_creation_method,
    'N'    agreements_to_watch,
    houtl.language,
    extract(YEAR from pha.approved_date) spend_year
  from po_lines_all pla,  po_headers_all pha,
       icx_cat_items_ctx_hdrs_tlp ctxh,
       icx_cat_attribute_values av,
       icx_cat_attribute_values_tlp avtlp,
       financials_system_params_all fsp,
       hr_all_organization_units_tl houtl,
	   po_doc_style_headers doc_style ,
     po_session_gt psgt
 where pha.po_header_id = pla.po_header_id 
   and pha.type_lookup_code                                         = 'STANDARD'
   and pla.from_header_id                                           = ctxh.po_header_id(+)
   and pla.from_line_id                                             = ctxh.po_line_id(+)
   and pla.org_id                                                   = fsp.org_id(+)
   and pla.org_id                                                   = ctxh.org_id(+)
   and ctxh.req_template_name(+)                                    = '-2'
   and ctxh.req_template_line_num(+)                                = -2
   and Nvl(ctxh.LANGUAGE,houtl.LANGUAGE)                            = houtl.LANGUAGE
   and ctxh.inventory_item_id                                       = av.inventory_item_id(+)
   and ctxh.owning_org_id                                           = av.org_id(+)
   and ctxh.po_line_id                                              = av.po_line_id(+)
   and ctxh.req_template_name                                       = av.req_template_name(+)
   and ctxh.req_template_line_num                                   = av.req_template_line_num(+)
   and ctxh.inventory_item_id                                       = avtlp.inventory_item_id(+)
   and ctxh.owning_org_id                                           = avtlp.org_id(+)
   and ctxh.po_line_id                                              = avtlp.po_line_id(+)
   and ctxh.req_template_name                                       = avtlp.req_template_name(+)
   and ctxh.req_template_line_num                                   = avtlp.req_template_line_num(+)
   AND Nvl(avtlp.LANGUAGE,houtl.LANGUAGE)                           = houtl.language
   and houtl.language IN ('US')
   and avtlp.LANGUAGE(+) IN ('US')
   AND houtl.organization_id (+) = pha.org_id
   AND doc_style.style_id(+) = pha.style_id
   AND nvl(doc_style.clm_flag,'N') = 'N'
   AND psgt.index_char1(+) = 'IND_PROC_PO_' || pla.po_header_id ||'_'||  pla.po_line_id
 
   /*Load CutOff Condition*/
   and pha.last_update_date >= to_date('2021/12/12', 'YYYY/MM/DD'))
   PIVOT (max(org_name) as Org_name,
        max(cat_source_type_code) as cat_source_type_code,
        max(cat_uom_code) as cat_uom_code,
        max(line_type) as line_type,
        max(cat_manufacturer) AS cat_manufacturer ,
        max(cat_item_description) AS cat_item_description,
        max(cat_long_description) AS cat_long_description,
        max(std_po_loc_uom_code) AS std_po_loc_uom_code,
        max(document_creation_method) AS document_creation_method,
        max(CAT_IP_CATEGORY_NAME) AS   CAT_IP_CATEGORY_NAME,
        max(STD_PO_SHIP_LOCATION) AS   STD_PO_SHIP_LOCATION
        for LANGUAGE in ('US' "US"))
) x
where
2=2