BUY_ORDER_VALUE Not Populated Even Though There Is Data To Support It

(Doc ID 1988835.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Advanced Planning Command Center - Version and later
Information in this document applies to any platform.


On : version, Load and Summarization ETL

In MSC_SUPPLIES_F, column BUY_ORDER_VALUE is not populated but there are orders and data in the underlying plans which should allow it to be populated.
It appears from the code (msc_supply_pkg), the relevant statement is:

sum(case when (ms.order_type in (1,2,76) or
  (ms.order_type in (5) and msi.purchasing_enabled_flag=1))
  then ms.new_order_quantity * nvl(ms.delivery_price, msi.standard_cost)
  else null end) buy_order_value

IN my plan data, there are no supplies with order_type 1,2, or 76 in MSC_SUPPLIES; however, there are planned orders (ORDER_TYPE = 5) and also, for these items/orders, MSC_APCC_ITEM_D.PURCHASING_ENABLED_FLAG is set to 1. Also, STANDARD_COST is populated in MSC_APCC_ITEM_D.

Based on the data in the base tables, there should be data in the BUY_ORDER_VALUE column.

The issue can be reproduced at will with the following steps:
1. Run the plan and then check the data

The issue has the following business impact:
Due to this issue, data in the dashboards is not correct


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms