ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN EEE75CEB4AE96AD8E05362FB09051F8BY select x.* from ( SELECT * FROM ( select 'ITEM_SUMMARY' RECORD_TYPE, ITEM_DESCRIPTION || ' - ' || ITEM_ID ECC_SPEC_ID, ITEM_DESCRIPTION || ' - ' || ITEM_ID RECORD_IDENTIFIER, ITEM_ID, CATEGORY_ID, ITEM_DESCRIPTION, INVENTORY_ITEM_STATUS_CODE, LIST_PRICE, MARKET_PRICE, INVENTORY_PLANNING_CODE, UOM.unit_of_measure PRIMARY_UOM, CATEGORY_ITEM, ITEM_NUMBER, CURRENCY_CODE, ORG_ID, (SELECT meaning FROM fnd_lookup_values flv WHERE flv.lookup_type = 'MTL_MATERIAL_PLANNING' AND flv.lookup_code = summary.inventory_planning_code AND flv.LANGUAGE = lang.language_code ) inventory_planning_method, PRIMARY_UOM_CODE, LEAD_TIME, UNIT_PRICE, UNIT_PRICE_MIN, UNIT_PRICE_AVG, TOTAL_SPEND, OFF_CONTRACT_SPEND, CONTRACT_SPEND, QUANTITY_AVAILABLE, QUANTITY_OVERDUE, RECEIPT_PENDING, REQS_IN_POOL_QTY, OPEN_PO, OPEN_REQ, ACTIVE_AGR, ACTIVE_NEG, LANG.LANGUAGE_CODE LANGUAGE FROM (select item_id, category_id, item_description, inventory_item_status_code, MIN(list_price) LIST_PRICE, MIN(market_price) MARKET_PRICE, inventory_planning_code , primary_uom_code, AVG(LEAD_TIME) LEAD_TIME, max(CATEGORY_ITEM) CATEGORY_ITEM, max(ITEM_NUMBER) ITEM_NUMBER, max(CURRENCY_CODE) CURRENCY_CODE, max(ORG_ID) ORG_ID, max(unit_price) UNIT_PRICE, min(unit_price) UNIT_PRICE_MIN, avg(unit_price) UNIT_PRICE_AVG, sum(total_spend) TOTAL_SPEND, sum(off_contract_spend) OFF_CONTRACT_SPEND, sum(contract_spend) CONTRACT_SPEND, max(quantity_available) QUANTITY_AVAILABLE, sum(quantity_overdue) QUANTITY_OVERDUE, sum(receipt_pending) RECEIPT_PENDING, sum(reqs_in_pool_qty) REQS_IN_POOL_QTY, count(distinct open_po) open_po, count(distinct open_req) open_req, count(distinct active_agr) active_agr, count(distinct active_neg) active_neg from (SELECT pol.item_id, pol.category_id, pol.item_description, cat.concatenated_segments category_item, item_kfv.concatenated_segments item_number, gsb.currency_code, fsp.ORG_ID, itm.inventory_item_status_code, itm.list_price_per_unit list_price, itm.market_price, itm.inventory_planning_code, itm.primary_uom_code, NVL(pll.quantity_cancelled, 0) quantity_cancelled, NVL(pll.quantity_rejected, 0) quantity_rejected, NVL(pll.quantity_received, 0) quantity_received, DECODE(pll.lead_time_unit, 'Week', ( NVL(pll.lead_time, apav.lead_time) * 7 ), ( NVL(pll.lead_time, apav.lead_time) )) lead_time, DECODE(NVL(pll.closed_code, 'OPEN'), 'OPEN', (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)-nvl(pll.quantity_received,0)), 0) receipt_pending, decode(nvl(pll.closed_code, 'OPEN'), 'OPEN', 'PO:'||poh.po_header_id, null) open_po, null open_req, null active_agr, null active_neg, (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN NVL(pol.unit_price, 0) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, NVL(pol.unit_price, 0), 0 )) END ) unit_price, NVL(pll.quantity, 0) quantity, (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) , 0 )) END ) total_spend, DECODE(poh.type_lookup_code, 'STANDARD', DECODE( NVL(agh.type_lookup_code, 'STANDARD'), 'BLANKET', 0,'CONTRACT', 0, (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) , 0 )) END ) )) off_contract_spend, DECODE(NVL(agh.type_lookup_code, 'STANDARD'), 'BLANKET', (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) , 0 )) END ) , 'CONTRACT', (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, decode(pll.matching_basis,'QUANTITY', (nvl(pol.unit_price,1)* (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0))), ( NVL(pll.amount,0) - NVL(pll.amount_cancelled, 0) )) , 0 )) END ) , 0) contract_spend, NVL(pll.quantity_accepted, 0) quantity_accepted, NVL(pll.quantity_billed, 0) quantity_billed, ( CASE WHEN ( pll.need_by_date IS NOT NULL AND pll.need_by_date < SYSDATE ) THEN NVL(pod.quantity_ordered, 0) - NVL(pll.quantity_received, 0) ELSE NULL END ) quantity_overdue, 'PO' type_line, pod.po_distribution_id, pll.line_location_id, poh.po_header_id, pol.po_line_id, prj.project_id, (select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail detail where detail.inventory_item_id = pol.item_id and detail.organization_id = fsp.inventory_organization_id) quantity_available, 0 reqs_in_pool_qty FROM po_line_locations_all pll, po_lines_all pol, po_headers_all poh, po_attribute_values apav, mtl_system_items_kfv item_kfv, mtl_categories_kfv cat, po_headers_all agh, po_distributions_all pod, mtl_system_items_b itm, pa_projects_all prj, financials_system_params_all fsp, gl_sets_of_books gsb WHERE pol.matching_basis IN ('QUANTITY','AMOUNT') AND pol.po_header_id = poh.po_header_id AND pll.po_line_id = pol.po_line_id AND pll.po_header_id = poh.po_header_id AND apav.po_line_id(+) = pol.po_line_id AND fsp.ORG_ID = poh.org_id AND gsb.set_of_books_id = fsp.set_of_books_id AND pol.item_id = itm.inventory_item_id (+) AND fsp.inventory_organization_id = nvl(itm.organization_id, fsp.inventory_organization_id) AND itm.inventory_item_id = item_kfv.inventory_item_id (+) AND itm.organization_id = item_kfv.organization_id (+) AND pol.category_id = cat.category_id(+) AND agh.po_header_id(+) = NVL(pol.from_header_id,pol.contract_id) AND agh.type_lookup_code (+) IN ('BLANKET','CONTRACT') AND agh.global_agreement_flag(+) = 'Y' AND pod.line_location_id = pll.line_location_id AND pod.po_header_id = poh.po_header_id AND poh.type_lookup_code = 'STANDARD' AND pod.project_id = prj.project_id (+) AND poh.authorization_status != 'INCOMPLETE' AND poh.creation_date >= fnd_date.Canonical_to_date( fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF')) UNION ALL SELECT prl.item_id, prl.category_id, prl.item_description, cat.concatenated_segments category_item, item_kfv.concatenated_segments item_number, gsb.currency_code, fsp.ORG_ID, itm.inventory_item_status_code, itm.list_price_per_unit list_price, itm.market_price, itm.inventory_planning_code, itm.primary_uom_code, NVL(prl.quantity_cancelled, 0) quantity_cancelled, 0 quantity_rejected, NVL(prl.quantity_received, 0) quantity_received, NULL lead_time, 0 receipt_pending, null open_po, DECODE(NVL(prl.closed_code, DECODE(prl.reqs_in_pool_flag,'Y','OPEN','CLOSED')), 'OPEN', 'REQ:'||prh.requisition_header_id, null) open_req, null active_agr, null active_neg, NVL(prl.unit_price, 0) unit_price, NVL(prl.quantity, 0) quantity, DECODE(prl.order_type_lookup_code, 'FIXED PRICE', prl.amount, 'RATE', prl.amount, decode(prl.cancel_flag, 'Y', 0, decode(prl.matching_basis, 'AMOUNT', PRL.AMOUNT, PRL.UNIT_PRICE * (PRL.QUANTITY - NVL(PRL.QUANTITY_CANCELLED, 0))))) total_spend, 0 off_contract_spend, 0 contract_spend, 0 quantity_accepted, 0 quantity_billed, 0 quantity_overdue, 'REQ' type_line, prd.distribution_id, NULL, prl.requisition_line_id, prh.requisition_header_id, prj.project_id , (select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail detail where detail.inventory_item_id = prl.item_id and detail.organization_id = fsp.inventory_organization_id) quantity_available, decode(prl.reqs_in_pool_flag, 'Y',(nvl(prl.quantity,0)-nvl(prl.quantity_delivered,0)) ,0) reqs_in_pool_qty FROM po_req_distributions_all prd, po_requisition_lines_all prl, po_requisition_headers_all prh, mtl_system_items_kfv item_kfv, mtl_categories_kfv cat, mtl_system_items_b itm, pa_projects_all prj, gl_sets_of_books gsb, financials_system_params_all fsp WHERE prd.requisition_line_id = prl.requisition_line_id AND prl.requisition_header_id = prh.requisition_header_id AND fsp.org_id = prh.org_id AND prl.item_id = itm.inventory_item_id (+) AND fsp.inventory_organization_id = nvl(itm.organization_id, fsp.inventory_organization_id) AND itm.inventory_item_id = item_kfv.inventory_item_id (+) AND itm.organization_id = item_kfv.organization_id (+) AND prl.category_id = cat.category_id(+) AND prl.matching_basis IN ('QUANTITY','AMOUNT') AND prd.project_id = prj.project_id (+) AND gsb.set_of_books_id = prd.set_of_books_id AND prh.creation_date >= fnd_date.Canonical_to_date( fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF')) UNION ALL SELECT pol.item_id, pol.category_id, pol.item_description, cat.concatenated_segments category_item, item_kfv.concatenated_segments item_number, gsb.currency_code, fsp.ORG_ID, itm.inventory_item_status_code, itm.list_price_per_unit list_price, itm.market_price, itm.inventory_planning_code, itm.primary_uom_code, 0 quantity_cancelled, 0 quantity_rejected, pol.quantity_committed quantity_received, apav.lead_time lead_time, 0 receipt_pending, null open_po, null open_req, decode(nvl(pol.closed_code, 'OPEN'), 'OPEN', 'AGR:'||poh.po_header_id, null) active_agr, null active_neg, (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN pol.unit_price ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, pol.unit_price,0)) END ) unit_price, pol.quantity, (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN DECODE(NVL(pol.quantity, 0), 0, NVL(pol.unit_price,pol.amount), ( NVL(pol.quantity, 1) * pol.unit_price )) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, DECODE(NVL(pol.quantity, 0), 0, NVL(pol.unit_price,pol.amount), ( NVL(pol.quantity, 1) * pol.unit_price )), 0 )) END ) total_spend, 0 off_contract_spend, (CASE WHEN (poh.currency_code is null or gsb.currency_code = poh.currency_code) THEN DECODE(NVL(pol.quantity, 0), 0, pol.unit_price, ( NVL(pol.quantity, 1) * pol.unit_price )) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( poh.currency_code, gsb.currency_code, poh.rate_date, poh.rate_type, poh.rate, DECODE(NVL(pol.quantity, 0), 0, pol.unit_price, ( NVL(pol.quantity, 1) * pol.unit_price )), 0 )) END ) contract_spend, 0 quantity_accepted, 0 quantity_billed , 0 quantity_overdue, 'AGR' type_line, NULL po_distribution_id, NULL line_location_id, poh.po_header_id, pol.po_line_id, ppa.project_id, (select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail detail where detail.inventory_item_id = pol.item_id and detail.organization_id = fsp.inventory_organization_id) quantity_available, 0 reqs_in_pool_qty FROM po_lines_all pol, po_headers_all poh, pa_projects_all ppa, po_attribute_values apav, mtl_system_items_kfv item_kfv, mtl_categories_kfv cat, financials_system_params_all fsp, mtl_system_items_b itm, gl_sets_of_books gsb WHERE pol.matching_basis IN ('QUANTITY','AMOUNT') AND pol.po_header_id(+) = poh.po_header_id AND poh.type_lookup_code IN ('BLANKET','CONTRACT') AND poh.global_agreement_flag = 'Y' AND fsp.ORG_ID = poh.org_id AND apav.po_line_id(+) = pol.po_line_id AND gsb.set_of_books_id = fsp.set_of_books_id AND pol.item_id = itm.inventory_item_id (+) AND fsp.inventory_organization_id = nvl(itm.organization_id, fsp.inventory_organization_id) AND itm.inventory_item_id = item_kfv.inventory_item_id (+) AND itm.organization_id = item_kfv.organization_id (+) AND pol.category_id = cat.category_id(+) AND pol.project_id = ppa.project_id(+) AND poh.authorization_status != 'INCOMPLETE' AND poh.creation_date >= fnd_date.Canonical_to_date( fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF')) UNION ALL SELECT itm.item_id, itm.category_id, itm.item_description, itm.category_name category_item, item_kfv.concatenated_segments item_number, gsb.currency_code, fsp.ORG_ID, itmB.inventory_item_status_code, itmB.list_price_per_unit list_price, itmB.market_price, itmB.inventory_planning_code, itmB.primary_uom_code, 0 quantity_cancelled, 0 quantity_rejected, 0 quantity_received, NULL lead_time, 0 receipt_pending, null open_po, null open_req, null active_agr, DECODE(NVL(ah.auction_status, 'ACTIVE'), 'ACTIVE', 'NEG:'||ah.auction_header_id, 'COMMIT_ACTIVE','NEG:'||ah.auction_header_id , NULL) active_neg, (CASE WHEN (pbi.unit_price IS NULL) THEN NULL WHEN (ah.currency_code IS NULL OR gsb.currency_code = ah.currency_code) THEN pbi.unit_price ELSE (GL_CURRENCY_API.convert_closest_amount_sql( ah.currency_code, gsb.currency_code, ah.rate_date, ah.rate_type, ah.rate, pbi.unit_price, 0)) END) unit_price, itm.quantity, (CASE WHEN (pbi.unit_price IS NULL OR pbi.quantity IS NULL) THEN 0 WHEN (ah.currency_code IS NULL OR gsb.currency_code = ah.currency_code) THEN (pbi.unit_price*pbi.quantity) ELSE (GL_CURRENCY_API.convert_closest_amount_sql( ah.currency_code, gsb.currency_code, ah.rate_date, ah.rate_type, ah.rate, (pbi.unit_price*pbi.quantity), 0)) END) total_spend, 0 off_contract_spend, 0 contract_spend, 0 quantity_accepted, 0 quantity_billed, 0 quantity_overdue, 'NEG' type_line, NULL po_distribution_id, NULL line_location_id, ah.auction_header_id po_header_id, NULL po_line_id, proj.project_id, (select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail detail where detail.inventory_item_id = itm.item_id and detail.organization_id = fsp.inventory_organization_id) quantity_available, 0 reqs_in_pool_qty FROM pon_auction_item_prices_all itm, pon_auction_headers_all ah, pon_bid_item_prices pbi, pa_projects_all proj, mtl_system_items_kfv item_kfv, financials_system_params_all fsp, mtl_system_items_b itmB, gl_sets_of_books gsb WHERE itm.order_type_lookup_code IN ('QUANTITY','AMOUNT') AND ah.auction_status NOT IN ( 'DRAFT', 'DELETED', 'CANCELLED' ) AND pbi.award_status (+) <> 'REJECTED' AND pbi.line_number (+) = itm.line_number AND pbi.auction_header_id (+) = itm.auction_header_id AND itm.auction_header_id = ah.auction_header_id AND fsp.org_id = ah.org_id AND gsb.set_of_books_id = fsp.set_of_books_id AND itm.item_id = itmB.inventory_item_id (+) AND fsp.inventory_organization_id = nvl(itmB.organization_id, fsp.inventory_organization_id) AND itmB.inventory_item_id = item_kfv.inventory_item_id (+) AND itmB.organization_id = item_kfv.organization_id (+) AND itm.project_id = proj.project_id(+) AND ah.creation_date >= fnd_date.Canonical_to_date( fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF' )) ) group by item_id, category_id, item_description ,inventory_item_status_code ,inventory_planning_code ,primary_uom_code ) summary, mtl_units_of_measure_tl uom, fnd_languages lang WHERE lang.installed_flag in ('I', 'B') AND summary.primary_uom_code = uom.uom_code(+) AND nvl(uom.language, lang.language_code) = lang.language_code and LANG.LANGUAGE_CODE in ('US') ) PIVOT ( MAX(INVENTORY_PLANNING_METHOD) AS INVENTORY_PLANNING_METHOD , MAX(PRIMARY_UOM) AS PRIMARY_UOM for LANGUAGE in ('US' "US") ) ) x where 2=2 AR ECC شراء المشروع, ملخص الصنف Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues D ECC Projektbeschaffung, Artikelübersicht Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues E ECC Project Procurement, Resumen de Artículo Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues F ECC Project Procurement, Synthèse articles Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues I ECC Acquisti progetto, Sintetico articolo Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues JA ECC プロジェクト調達, 品目要約 Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues KO ECC 프로젝트 조달, 품목 요약 Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues RU ECC Закупки для проекта, Сводка позиций Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues S ECC Projektupphandling, Artikelöversikt Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues TR ECC Proje Tedariği, Kalem Özeti Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues US ECC Project Procurement, Item Summary Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues ZHS ECC Project Procurement, 产品汇总 Imported from Enterprise Command Center Dataset Key: po-item-summary Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues Enterprise Command Center