ECC Project Procurement, Item Summary

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