ECC Procurement, Indirect Procurement, SQL3

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Indirect Procurement for Procurement Command Center

Dataset Key: po-pcc-ind-proc
Query Procedure: PO_PCC_IND_PROC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesInd
Run ECC Procurement, Indirect Procurement, SQL3 and other Oracle EBS reports with Blitz Report™ on our demo environment
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
    || '-'
    || plla.line_location_id
    || '-'
    || '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,
    (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,
    NVL(apoh.blanket_total_amount,0) agreed_agreement_amount,
    NVL(apol.committed_amount,0) agreed_line_amount,
    /*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,
    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,
    (select unit_of_measure_tl 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,
	(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,
     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,
    decode( ctxh.source_type, 'BLANKET',
                        nvl(
                          (select fnd_message.get_string('FND', 'Yes') from dual where(sysdate-180) >
                                                    psgt.date2), fnd_message.get_string('FND', 'No')),
                       'GLOBAL_BLANKET',nvl(
                          (select fnd_message.get_string('FND', 'Yes') from dual where(sysdate-180) >
                                                    psgt.date2), 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,
    (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 ) 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 nvl(psgt.num10, 0) >=(apoh.amount_limit * 0.9)
		OR apoh.end_date <= SYSDATE
		OR  nvl(psgt.num10, 0) = apoh.amount_limit
     THEN  'Y'
	 ELSE 'N'
    END )    agreements_to_watch,
    decode( ctxh.source_type, 'BLANKET',
                        nvl(
                          (select 'Y' from dual where(sysdate-180) >
                                                    psgt.date2), 'N'),
                       'GLOBAL_BLANKET',nvl(
                          (select 'Y' from dual where(sysdate-180) >
                                                    psgt.date2), 'N'),
          null)  is_unused_cat_item,
      psgt.date2 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 avtlp.LANGUAGE(+) IN ('US')
   AND decode(nvl(ctxh.po_header_id, 0), 0, 'Y', apoh.global_agreement_flag) = 'Y'
   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('2020/04/17', '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
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV
Blitz Report™