Results for “msd_sr_cat_all_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSD_SR_CAT_ALL_V is a source view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the MSD — Demand Planning product. Its documented purpose is to associate the Product Category level to the Top level within the Product Dimension hierarchy used by Demand Planning and related supply chain planning engines. In the ETRM metadata model, this view functions as a staging or source construct that materializes category-to-top-level associations consumed during the collection and setup of planning dimensional data. It is therefore relevant both to implementations sourcing data from an existing Oracle Applications instance and to the internal dimensional loading logic that maps transaction-level categories into planning hierarchies.

The view is recorded with VALID status, indicating that it compiles successfully against its referenced objects in a properly configured 12.1.1 or 12.2.2 instance. Its content is derived from a Category set defined through a system parameter, making it configuration-sensitive rather than static.

Underlying Base Objects

Per the documented view metadata, MSD_SR_CAT_ALL_V is defined over the following referenced base objects: MSD_APP_INSTANCE_ORGS, MSD_SETUP_PARAMETERS, MSD_SR_UTIL (a package), MTL_CATEGORIES_B_KFV, MTL_CATEGORIES_TL, MTL_ITEM_CATEGORIES, and DUAL.

• MSD_SETUP_PARAMETERS — supplies the parameter MSD_CATEGORY_SET_NAME, whose value identifies the active category set. This is the governing filter for the view.
• MTL_ITEM_CATEGORIES — the transactional assignment of items to categories, providing the category-to-item linkage.
• MTL_CATEGORIES_B_KFV and MTL_CATEGORIES_TL — the category key flexfield view and the translated category name table, supplying category identifiers and concatenated segment descriptions.
• MSD_SR_UTIL — a PL/SQL package supplying the helper functions GET_ALL_PRD_PK, GET_ALL_PRD_DESC, GET_NULL_PK, and GET_NULL_DESC, which produce surrogate keys and descriptions for the “all products” and null members.
• MSD_APP_INSTANCE_ORGS and DUAL — supporting objects referenced in the broader definition; DUAL is used to generate the synthetic union row.

The view text joins MSD_SETUP_PARAMETERS to MTL_ITEM_CATEGORIES_V filtered on the configured category set, then unions a synthetic row that represents the “All Products” pseudo-member at the top of the dimension.

Key Columns

• LEVEL_VALUE_PK — the surrogate primary key of the category level member.
• LEVEL_VALUE — the concatenated category segment string (CATEGORY_CONCAT_SEGS), i.e., the human-readable category path.
• PARENT_VALUE_PK — the surrogate key of the parent, which in this view resolves to the Top level (“all products”) member.
• PARENT_VALUE — the description of the parent top-level member.
• ATTRIBUTE1 through ATTRIBUTE5 — reserved attribute columns; the documented view text populates these as NULL, leaving extension slots available for downstream dimensional enrichment.

Because PARENT_VALUE_PK is populated by the MSD_SR_UTIL helper, the view effectively emits one row per configured category and one additional row representing the Top level itself.

Common Use Cases and Queries

The view is typically queried to validate that category-set configuration has correctly resolved into planning dimension members, or to diagnose missing category-to-top-level associations during collection. A representative query is:

SELECT level_value_pk, level_value, parent_value_pk, parent_value FROM apps.msd_sr_cat_all_v ORDER BY level_value;

To confirm the active category set driving the view, query the parameter directly:

SELECT parameter_name, parameter_value FROM apps.msd_setup_parameters WHERE parameter_name = 'MSD_CATEGORY_SET_NAME';

Analysts frequently join the view to item-category assignments to identify categories present in inventory but absent from the planning dimension. Because the view relies on MSD_SR_UTIL and on the configured parameter, any troubleshooting should confirm both the package is valid and the parameter is maintained before concluding that data is missing.