ECC Procurement, Indirect Procurement, SQL4

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, SQL4 and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
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
    || '-'
    || review.review_id
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || NULL
    || '-'
    || 'SUGGESTIONS' 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,
    NVL(apoh.blanket_total_amount,0) agreed_agreement_amount,
    NVL(apol.committed_amount,0) agreed_line_amount,
    /*suggestions*/
    review.review_id suggestion_id,
    review.reviewer_id suggester_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) suggester_name,
    review.creation_date suggestion_date,
    review.review_title suggestion_title,
    REPLACE(review.review_comments,'|',',') suggestion_comments,
    nvl(review.status,'O') suggestion_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) suggestion_status_code,
    decode(
    (select count(response_id) from icx_rvw_responses where review_id = review.review_id),
    0, 'No', 'Yes') has_suggestion_responses,
    /*iExpense*/
    /*action items*/
    /*common*/
    PO_PCC_IND_PROC_UTIL_PVT.get_cost_center_for_employee(review.reviewer_id) cost_center,
    review.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,
    review.item_id inventory_item_id,
    (select concatenated_segments
       from mtl_system_items_kfv
      where review.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,
	review.vendor_id supplier_id,
    (select VENDOR_NAME from ap_suppliers aps where aps.VENDOR_ID = review.vendor_id) supplier,
    '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,
       houtl.language,
       'NULL' DOC_AUTHORIZED_USER_IDS
  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_doc_style_headers doc_style,
	   po_session_gt psgt
 where review.review_entity                                         = 'SG'
   and review.org_id                                                = fsp.org_id(+)
   and review.org_id                                                = ctxh.owning_org_id(+)
   and review.po_line_id                                            = ctxh.po_line_id(+)
   and review.req_template_name                                     = ctxh.req_template_name(+)
   and review.req_template_line_num                                 = ctxh.req_template_line_num(+)
   and review.item_id                                               = ctxh.inventory_item_id(+)
   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 ctxh.language                                                = avtlp.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 psgt.index_char1(+) = 'IND_PROC_ITEMS_' || ctxh.po_header_id ||'_'||  ctxh.po_line_id
   AND decode(nvl(ctxh.po_header_id, 0), 0, 'Y', apoh.global_agreement_flag) = 'Y'
   AND houtl.organization_id (+) = review.org_id
   AND doc_style.style_id(+) = apoh.style_id
   AND nvl(doc_style.clm_flag,'N') = 'N'
   and houtl.language IN ('US')
   and avtlp.LANGUAGE(+) IN ('US')
/*Load CutOff Condition*/
   and review.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(document_creation_method) AS document_creation_method ,
        max(suggestion_status_code) AS suggestion_status_code,
        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
Blitz Report™