ECC Procurement, Indirect Procurement, SQL1

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, SQL1 and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select * from (select  /*+ leading(ctxh) index(ctxh ICX_CAT_ITEMS_CTX_HDRS_TLP_N99) index(apol PO_LINES_U1) */  ctxh.source_type
    || '-'
    || ctxh.org_id
    || '-'
    || ctxh.inventory_item_id
    || '-'
    || ctxh.po_line_id
    || '-'
    || ctxh.req_template_name
    || '-'
    || ctxh.req_template_line_num
    || '-'
    || review.review_id
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || 'CATALOG_ITEMS' 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 = apol.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,
    review.review_id,
    review.reviewer_id,
    (select full_name from per_all_people_f
      where person_id = review.reviewer_id
        and trunc(sysdate) BETWEEN effective_start_date
                               and effective_end_date) reviewer_name,
    review.creation_date review_date,
    review.review_title,
    REPLACE(review.review_comments,'|',',') review_comments,
    review.rating review_rating,
    decode(review.review_entity, 'I', rating, null) review_item_rating,
    decode(review.review_entity, 'S', rating, null) review_supplier_rating,
    review.review_entity,
    (select meaning from fnd_lookup_values
      where lookup_type = 'ICX_REVIEW_ENTITY_TYPE' and lookup_code = review.review_entity AND language= houtl.language) review_for,
    (select VENDOR_NAME from ap_suppliers aps where aps.VENDOR_ID = review.vendor_id) review_supplier,
	review.vendor_id review_supplier_id,
    nvl(review.status,'O') review_status,
    (select meaning from fnd_lookup_values
      where lookup_type = 'ICX_REVIEW_STATUS_TYPE' and lookup_code = nvl(review.status,'O')
        AND language= houtl.language AND view_application_id = 3) review_status_code,
	(select meaning from fnd_lookup_values
      where lookup_type = 'PO_REVIEW_PURCHASED_FROM_TYPES' and lookup_code = review.purchased_from AND language= houtl.language) review_purchased_from_code,
    (
      select listagg((select full_name from per_all_people_f
                       where person_id = resp.responder_id
                         and trunc(sysdate) BETWEEN effective_start_date
                                                and effective_end_date)
             || ' : '
             || response_text, ';')
             within group (order by review_id)
        from icx_rvw_responses resp
       where resp.review_id = review.review_id
        GROUP BY review_id
    ) review_responses,
	(select count(usefull_flag) from icx_review_usefull_dtls where review_id = review.review_id and usefull_flag = 'Y') review_useful_count,
	REPLACE (psgt.char1,' ',UNISTR('|')) review_keywords,
    (select count(response_id) from icx_rvw_responses where review_id = review.review_id) review_response_count,
    (select count(usefull_flag) from icx_review_usefull_dtls where review_id = review.review_id and usefull_flag = 'Y')
    || '/'
    ||
    (select count(usefull_flag) from icx_review_usefull_dtls where review_id = review.review_id and usefull_flag = 'N') usefull_useless,
    NVL(apoh.blanket_total_amount,0) agreed_agreement_amount,
    NVL(apol.committed_amount,0) agreed_line_amount,
    /*suggestions*/
    decode(
    (select count(response_id) from icx_rvw_responses where review_id = review.review_id),
    0, 'No', 'Yes') has_review_responses,
    /*iExpense*/
    /*standard po*/
    /*action items*/
    /*common*/
    psgt.char2 cost_center,
    ctxh.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,
    ctxh.inventory_item_id inventory_item_id,
    (select concatenated_segments
       from mtl_system_items_kfv
      where ctxh.inventory_item_id = inventory_item_id and fsp.inventory_organization_id = organization_id) item_number,
    ctxh.po_category_id,
    (select concatenated_segments from mtl_categories_kfv where ctxh.po_category_id = category_id) purchasing_category,
    ctxh.line_type_id,
    (SELECT line_type FROM po_line_types_tl where line_type_id = ctxh.line_type_id and language = houtl.language) line_type,
    ctxh.currency_code transaction_currency,
	DECODE(ctxh.supplier_id, -2, to_number(NULL), ctxh.supplier_id) supplier_id,
    ctxh.supplier supplier,
    (select vendor_site_code FROM AP_SUPPLIER_SITES_ALL where ctxh.supplier_site_id = vendor_site_id) supplier_site,
  'N' 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 = apoh.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,
   psgt.date1 performance_date,
   psgt.num2 ontime_delivery_rating,
   psgt.num3 quality_rating,
   psgt.num4 price_comp_rating,
   houtl.LANGUAGE ,
    psgt.char3 DOC_AUTHORIZED_USER_IDS,
    psgt.num5 purchased_agreement_amount,
    psgt.num6 purchased_line_amount
  from icx_cat_items_ctx_hdrs_tlp ctxh,  icx_rvw_reviews review,
       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_session_gt psgt
 where ctxh.owning_org_id  = fsp.org_id 
   and ctxh.language         = houtl.LANGUAGE(+)
   and ctxh.owning_org_id    = review.org_id(+)
   and ctxh.po_line_id       = review.po_line_id(+)
   and ctxh.req_template_name= review.req_template_name(+)
   and ctxh.req_template_line_num = review.req_template_line_num(+)
   and ctxh.inventory_item_id = review.item_id(+)
   and review.review_entity(+) <> 'Q'
   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 ctxh.po_header_id = apol.po_header_id(+)
   and ctxh.po_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 (+) = ctxh.org_id
  AND psgt.index_char1(+) = 'IND_PROC_ITEMS_' || ctxh.po_header_id ||'_'||  ctxh.po_line_id
  /*AND psgt.num7(+) = review.review_id */
   /*Load CutOff Condition*/
   and ctxh.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(review_for) as review_for,
        max(review_status_code) as review_status_code,
        max(review_purchased_from_code) as review_purchased_from_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(document_creation_method) AS document_creation_method,
        max(CAT_IP_CATEGORY_NAME) AS   CAT_IP_CATEGORY_NAME
 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