W_PRODUCT_D.PROD_CAT1_WID Column is Populated with 0s for Non Inventory Items (Doc ID 2171427.1)

Last updated on NOVEMBER 30, 2016

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.10.1 and later
Information in this document applies to any platform.

Symptoms

Problem pertains to the OOTB reports. We have not done a single customization yet to the OOTB code.


All the categories from MTL_CATEGORIES_B got populated in W_PRODUCT_D. But correspondingly all the categories didn't populate in W_PROD_CAT_DH table.

Issue 1:

For purchasing lines which don't have any item details but consist of categories (in bi apps we are treating them as non inventory items)
were being grouped into Not Applicable as highlighted in the screenshot. The derivation of PRODUCT_WID in the fact table W_PURCH_SCHEDULE_LINE_F is based on inventory item id present in PO Lines table.
But for the above mentioned scenario, we don't have inventory item ids, just we have the category ids.

Issue 2:

There are categories for which no item was assigned. In bi apps we are populating these categories as non inventory items in W_PRODUCT_D.
The extraction query for non inventory items uses only MTL_CATEGORIES_B and there is no dependency on MTL_ITEM_CATEGORIES
But in case of W_PROD_CAT_DH the extraction query mainly uses MTL_ITEM_CATEGORIES alone with MTL_CATEGORIES_B (Equi join),
So W_PROD_CAT_DH consist of categories to which any of the item being assigned. So, for all the non inventory items in W_PRODUCT_D,
it was not able to resolve the PROD_CAT1_WID column as there is no record corresponding to non inventory item (for that category) in W_PROD_CAT_DH.



Cause

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