Results for “msd_sr_loc_cus_v”

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

Overview

The MSD_SR_LOC_CUS_V view is a source (interface) view owned by the APPS schema within the MSD – Demand Planning module of Oracle E-Business Suite. It is classified as a VALID database object in both Oracle EBS 12.1.1 and 12.2.2, and its documented purpose is to associate the Location level to the Customer level in the Geography dimension used by Demand Planning. In the dimensional model, this view supplies the source rows that map a ship-to location (Site Use) to its owning customer, so that location-level and customer-level hierarchies can be reconciled within the geography dimension.

This source view was originally designed to be applied to an Oracle Applications 11i instance from which Location-to-Customer association information is collected, and it persists unchanged into the EBS 12.x releases. Because it is a source view rather than a transactional table, it is consumed by the Demand Planning collection and dimension-build programs, and by reporting that requires a flattened Location-to-Customer mapping.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects:

• MSD_SETUP_PARAMETERS (synonym) — supplies the MSD_CUSTOMER_ATTRIBUTE parameter that drives the attribute filter logic in the view text.
• MSD_SR_UTIL (package) — provides GET_NULL_DESC and GET_NULL_PK for the synthetic "unknown" row.
• DUAL — used to generate the default null-key row via UNION.
• HZ/RA party and site objects — HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTIES, and the legacy RA_CUSTOMERS, RA_SITE_USES_ALL, RA_ADDRESSES_ALL tables — provide the customer and ship-to location records.
• Additional capability objects — FND_LOOKUP_VALUES_VL, HR_ALL_ORGANIZATION_UNITS_TL, HR_ORGANIZATION_INFORMATION, MTL_PARAMETERS, and PO_LOCATION_ASSOCIATIONS_ALL — support lookups, operating-unit, and location-association logic.

In the documented view text, the primary joins are RA_SITE_USES_ALL (ship-to site uses) to RA_ADDRESSES_ALL, and RA_ADDRESSES_ALL to RA_CUSTOMERS, ensuring only active SHIP_TO associations are surfaced.

Key Columns

• LEVEL_VALUE_PK — the primary key of the child level; sourced from PRS.SITE_USE_ID (the ship-to Site Use ID). This is the exact column referenced by the user search term "level_value_pk".
• LEVEL_VALUE — the descriptive child value, concatenated as customer name : customer number : location.
• PARENT_VALUE_PK — the parent customer key, sourced from RAC.CUSTOMER_ID.
• PARENT_VALUE — the parent description, customer name : customer number, with MSD_SR_UTIL.GET_NULL_DESC substituted when null.
• ATTRIBUTE1 .. ATTRIBUTE5 — currently returned as NULL; the attribute-based filtering is applied through the DECODE on the MSD_CUSTOMER_ATTRIBUTE parameter rather than exposed as these columns.

Common Use Cases and Queries

Typical scenarios include validating the Location-to-Customer hierarchy before a Demand Planning collection run, and reporting on which ship-to sites roll up to which customers.

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

Because the view UNIONs a synthetic "unknown" row via MSD_SR_UTIL.GET_NULL_PK, users reconciling counts should account for that default row. Filtering by customer is common:

SELECT level_value_pk, level_value
FROM   apps.msd_sr_loc_cus_v
WHERE  parent_value_pk = :customer_id;

Note that the view text references legacy RA_ tables while the 12.2.2 metadata lists HZ synonyms; on R12 installations the HZ objects are the authoritative trading-community sources, and the underlying synonym resolution maps RA_ to HZ_ equivalents. When the MSD_CUSTOMER_ATTRIBUTE setup parameter selects a customer attribute, the view restricts the returned rows to customers whose designated attribute value matches the configured criterion.