ECC Procurement, Indirect Procurement, SQL3

Description
Columns: Ecc Spec Id, Cat Source Type, Cat Source, Cat Po Header Id, Cat Po Line Id, Cat End Date, Cat Document Number, Cat Line Number, Cat Req Template Name, Cat Req Template Line Num ...
Imported from ECC
select * from (select 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
    || '-'
    || plla.line_location_id
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || 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) 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,
    Nvl( Least(apoh.end_date, apol.expiration_date),
         Nvl(apoh.end_date, apol.expiration_date)
    )cat_end_date,
    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,
    ctxh.unit_meas_lookup_code cat_uom,
    (select uom_code from mtl_units_of_measure_tl WHERE unit_of_measure = ctxh.unit_meas_lookup_code and language =  houtl.language) cat_uom_code,
    ctxh.ip_category_id cat_ip_category_id,
    (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,
    DECODE(ctxh.supplier_part_auxid, '##NULL##', TO_CHAR(NULL), ctxh.supplier_part_auxid) cat_supplier_part_auxid,
    avtlp.manufacturer cat_manufacturer,
    av.manufacturer_part_num cat_manufacturer_part_num,
    ctxh.buyer_id cat_buyer_id,
    (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,
    (select count(action_id) from po_buyer_actions where entity_type = 'PA_HEADER' and entity_id = ctxh.po_header_id and status <> 'CLOSED') open_bpa_header_action_count,
    (select count(action_id) from po_buyer_actions where entity_type = 'PA_LINE' and entity_id = ctxh.po_line_id and status <> 'CLOSED') open_bpa_line_action_count,
    apoh.amount_limit agreement_amount_limit,
    NVL(apoh.blanket_total_amount,0) agreed_agreement_amount,
    NVL(apol.committed_amount,0) agreed_line_amount,
    --suggestions
    decode(
    (select count(action_id) from po_buyer_actions where entity_type = 'PA_HEADER' and entity_id = ctxh.po_header_id and status <> 'CLOSED') ,
    0, 'No', 'Yes') has_bpa_header_action_flag,
    decode(
    (select count(action_id) from po_buyer_actions where entity_type = 'PA_LINE' and entity_id = ctxh.po_line_id and status <> 'CLOSED'),
    0, 'No', 'Yes') has_bpa_line_action_flag,
    --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,
    plla.line_location_id std_po_line_location_id,
    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,
    plla.quantity_cancelled std_po_loc_quantity_cancelled,
    plla.unit_meas_lookup_code std_po_loc_uom,
    (select uom_code from mtl_units_of_measure_tl WHERE unit_of_measure = plla.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,
    decode(pla.from_header_id, null, 0,
           gl_currency_api.Convert_amount_sql( pha.currency_code,
                              ( SELECT sob.currency_code
                                  FROM gl_sets_of_books sob,
                                       financials_system_params_all fspa
                                 WHERE sob.set_of_books_id = fspa.set_of_books_id
                                   and fspa.org_id = apoh.org_id
                              ), pha.rate_date, pha.rate_type, decode(pla.matching_basis, 'AMOUNT', pla.amount
                            , pla.quantity * pla.unit_price))) amount_from_source_doc,
	(select min(need_by_date) from po_line_locations_all where po_line_id = pla.po_line_id)  std_po_loc_need_by_date,
    plla.ship_to_location_id std_po_ship_location_id,
    (select loc_tl.location_code
       from hr_locations_all_tl loc_tl
      where plla.ship_to_location_id = loc_tl.location_id
        and loc_tl.language =  houtl.language
    ) std_po_ship_location,
	plla.quantity_received  std_po_loc_received_quantity,
	plla.quantity_rejected  std_po_loc_rejected_quantity,
    pha.approved_date std_po_loc_approved_date,
     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,
    (
       select REPLACE (ppg.group_name,'|',',')
         from per_all_assignments_f paf, pay_people_groups ppg
        where paf.people_group_id = ppg.people_group_id
          and trunc(sysdate) between trunc(effective_start_date)
                                 and trunc(effective_end_date)
          and paf.person_id = pha.agent_id
          and paf.primary_flag = 'Y'
          and rownum = 1
    ) employee_group,
    psgt.char2 cost_center,
    pla.org_id,
    --(select name from hr_all_organization_units_tl where organization_id = pla.org_id and language = userenv('LANG')) org_name,
    houtl.name org_name,
    (select currency_code from gl_sets_of_books where fsp.set_of_books_id = set_of_books_id) functional_currency,
    pha.rate,
    pha.rate_type,
    pha.rate_date,
    decode( ctxh.source_type, 'BLANKET',
                        nvl(
                          (select fnd_message.get_string('FND', 'Yes') from dual where(sysdate-180) >
                                                    (SELECT MAX(poh.approved_date)
                                                    FROM
                                                      po_headers_all poh,
                                                      po_lines_all pol
                                                    WHERE ctxh.po_line_id = pol.from_line_id
                                                    AND poh.po_header_id    = pol.po_header_id
                                                    and poh.type_lookup_code  = 'STANDARD'
                                                    )
                          ), fnd_message.get_string('FND', 'No')),
                       'GLOBAL_BLANKET',nvl(
                          (select fnd_message.get_string('FND', 'Yes') from dual where(sysdate-180) >
                                                    (SELECT MAX(poh.approved_date)
                                                    FROM
                                                      po_headers_all poh,
                                                      po_lines_all pol
                                                    WHERE ctxh.po_line_id = pol.from_line_id
                                                    AND poh.po_header_id    = pol.po_header_id
                                                    and poh.type_lookup_code  = 'STANDARD'
                                                    )
                           ), fnd_message.get_string('FND', 'No')),
          null) item_not_placed_on_po,
    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,
    pha.VENDOR_SITE_ID supplier_site_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 listagg(pda1.project_id,UNISTR('|')) within GROUP(ORDER BY pda1.CREATION_DATE) FROM po_distributions_all pda1 WHERE pda1.po_header_id = pha.po_header_id) project_ids,
    (SELECT Decode(Count(DISTINCT pda2.project_id), 0, 'N', 'Y') FROM po_distributions_all pda2 WHERE pda2.po_header_id = pha.po_header_id) is_proj_ref_on_po,
	(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 ) document_creation_method,
    (CASE
	 WHEN NVL(apoh.authorization_status,'INCOMPLETE') IN ('INCOMPLETE','IN PROCESS','PRE-APPROVED')
		OR EXISTS (SELECT 1
				  FROM po_change_requests
				  WHERE document_header_id = apoh.po_header_id
				  and request_status = 'PENDING')
		OR 	SYSDATE  >= apoh.end_date - 30
		OR PO_CORE_S.get_total(Decode(apoh.type_lookup_code,'BLANKET','GA','GC'), apoh.po_header_id) >=
		   (apoh.amount_limit * 0.9)
		OR apoh.end_date <= SYSDATE
		OR  PO_CORE_S.get_total(Decode(apoh.type_lookup_code,'BLANKET','GA','GC'), apoh.po_header_id) =
		    apoh.amount_limit
		OR exists (select 1 from po_buyer_actions
	             WHERE Trunc(Nvl(completion_date,SYSDATE )) - Trunc(TARGET_DATE)   > 0
                 and status  <> 'CLOSED'  and entity_type = 'PA_HEADER' and entity_id =apoh.po_header_id)
     THEN  'Y'
	 ELSE 'N'
    END )    agreements_to_watch,
    decode( ctxh.source_type, 'BLANKET',
                        nvl(
                          (select 'Y' from dual where(sysdate-180) >
                                                    (SELECT MAX(poh.approved_date)
                                                    FROM
                                                      po_headers_all poh,
                                                      po_lines_all pol
                                                    WHERE ctxh.po_line_id = pol.from_line_id
                                                    AND poh.po_header_id    = pol.po_header_id
                                                    and poh.type_lookup_code  = 'STANDARD'
                                                    )
                          ), 'N'),
                       'GLOBAL_BLANKET',nvl(
                          (select 'Y' from dual where(sysdate-180) >
                                                    (SELECT MAX(poh.approved_date)
                                                    FROM
                                                      po_headers_all poh,
                                                      po_lines_all pol
                                                    WHERE ctxh.po_line_id = pol.from_line_id
                                                    AND poh.po_header_id    = pol.po_header_id
                                                    and poh.type_lookup_code  = 'STANDARD'
                                                    )
                           ), 'N'),
          null)  is_unused_cat_item,
      (SELECT MAX(poh.approved_date)
       FROM   po_headers_all poh,
              po_lines_all pol
       WHERE ctxh.po_line_id = pol.from_line_id
       AND poh.po_header_id    = pol.po_header_id
       and poh.type_lookup_code  = 'STANDARD'
      ) cat_last_used_on,
        houtl.language,
        extract(YEAR from pha.approved_date) spend_year
  from po_lines_all pla,
       po_headers_all pha,
       po_line_locations_all plla,
       icx_cat_items_ctx_hdrs_tlp ctxh,
       icx_cat_attribute_values av,
       icx_cat_attribute_values_tlp avtlp,
       po_headers_all apoh,
       po_lines_all apol,
       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 pla.po_line_id                                               = plla.po_line_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 pla.from_header_id                                           = apol.po_header_id(+)
   and pla.from_line_id                                             = apol.po_line_id(+)
   and apol.po_header_id                                            = apoh.po_header_id(+)
and houtl.language IN ('US')
and (ctxh.po_header_id is null
         or 'Y' = (SELECT global_agreement_flag FROM po_headers_all WHERE po_header_id = ctxh.po_header_id))
   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 >= nvl(fnd_date.Canonical_to_date(fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF')),pha.last_update_date) )
   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"))