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
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 || '-' || 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 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, 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, /*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, 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 = pla.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, decode((select count(*) from po_line_locations_all pll where pll.po_line_id = pla.po_line_id), 1, (select pll.ship_to_location_id from po_line_locations_all pll where pll.po_line_id = pla.po_line_id), -999) std_po_ship_location_id, decode((select count(*) from po_line_locations_all pll where pll.po_line_id = pla.po_line_id), 1, (select loc_tl.location_code from hr_locations_all_tl loc_tl, po_line_locations_all pll where pll.ship_to_location_id = loc_tl.location_id and pll.po_line_id = pla.po_line_id and loc_tl.language = houtl.language ), po_pcc_orders_util_pvt.get_fnd_message('PO_MULTIPLE_ATTR','201',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, 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 and rownum<2) document_creation_method, 'N' agreements_to_watch, houtl.language, extract(YEAR from pha.approved_date) spend_year from po_lines_all pla, po_headers_all pha, icx_cat_items_ctx_hdrs_tlp ctxh, icx_cat_attribute_values av, icx_cat_attribute_values_tlp avtlp, 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 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 houtl.language IN ('US') and avtlp.LANGUAGE(+) IN ('US') 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('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(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 |
|
LOV |