ECC Project Procurement, Item Summary

Description
Categories: Enterprise Command Center
Columns: Record Type, Ecc Spec Id, Record Identifier, Item Id, Category Id, Item Description, Inventory Item Status Code, List Price, Market Price, Inventory Planning Code ...
Imported from Enterprise Command Center
Dataset Key: po-item-summary
Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL
Security Procedure:
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)