ECC Procurement, Indirect Procurement, SQL1

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
	  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,
    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 and rownum < 2) 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 and rownum <2) 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 and rownum<2) 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 (po_pcc_ind_proc_util_pvt.remove_stop_words(review.review_title||' '||review.review_comments),' ',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
				 and rownum < 2) 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
   /*Load CutOff Condition*/
   and ctxh.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(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
Download
 
Related Blitz Reports
ECC Procurement, Indirect Procurement, SQL4 ECC Procurement, Indirect Procurement, SQL3 ECC Sourcing, Negotiations, SQL1 ECC Sourcing, Negotiations, SQL2 ECC Procurement, Requisitions ECC Project Procurement, Item Summary GW Items CAC Inventory and Intransit Value (Period-End) CAC ICP PII Inventory and Intransit Value (Period-End)
Blitz Report™

Blitz Report™ provides multiple benefits: