Results for “msd_sr_backlog_v”

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

Overview

MSD_SR_BACKLOG_V is a source view owned by the APPS schema within the MSD – Demand Planning product family in Oracle E-Business Suite. Its documented purpose is to serve as the Source View for Booking Data, applied to an Oracle Applications instance from which Backlog information is to be retrieved. In the ETRM collection architecture, views of this type act as the extraction interface between the transactional order management tables and the Demand Planning staging and collection routines. Rather than reading the raw OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL structures directly, the collection programs query a curated, conformed projection that normalizes keys, dates, currencies, units of measure, and hierarchies into a shape acceptable to the planning engine.

The view is listed with a status of VALID in the 12.2.2 metadata, and it is included in the documented set of referenced base objects for the APPS schema. This confirms that it remains a supported, active collection object across the 12.1.1 and 12.2.2 releases, even though its description references the historical 11i origin of the extract definition.

Underlying Base Objects

The documented referenced base objects for this view are: FND_GLOBAL (PACKAGE), HZ_CUST_ACCOUNTS (SYNONYM), HZ_CUST_SITE_USES_ALL (SYNONYM), MSD_APP_INSTANCE_ORGS (SYNONYM), MSD_SETUP_PARAMETERS (SYNONYM), MSD_SR_UTIL (PACKAGE), MTL_SYSTEM_ITEMS (SYNONYM), OE_ORDER_HEADERS_ALL (SYNONYM), OE_ORDER_LINES_ALL (SYNONYM), OE_TRANSACTION_TYPES_TL (SYNONYM), RA_SALESREPS_ALL (SYNONYM), and SO_LOOKUPS (SYNONYM).

The view joins order lines (OE_ORDER_LINES_ALL) to order headers (OE_ORDER_HEADERS_ALL) on HEADER_ID, then extends outward to the ship-from organization through MSD_APP_INSTANCE_ORGS, the item master through MTL_SYSTEM_ITEMS, the customer and site-use records through RA_CUSTOMERS, RA_SITE_USES_ALL, and HZ_CUST_ACCOUNTS / HZ_CUST_SITE_USES_ALL, the sales representative through RA_SALESREPS_ALL, and the transaction type through OE_TRANSACTION_TYPES_TL. SO_LOOKUPS supplies lookup-code translation for the line status or source category, while MSD_SETUP_PARAMETERS drives conditional filtering on MSD_PLANNING_PERCENTAGE and MSD_CUSTOMER_ATTRIBUTE. FND_GLOBAL and MSD_SR_UTIL provide the session context and a suite of helper functions used throughout the projection.

Key Columns

• SHIP_FROM_ORG_ID — the organization shipping the order line; nulls are replaced by the MSD_SR_UTIL.GET_NULL_PK sentinel value.
• INVENTORY_ITEM_ID — the inventory item on the order line, likewise defaulted to the null-PK sentinel.
• CUSTOMER_ID and SITE_USE_ID — customer and ship-to site identifiers.
• SALESREP_ID — the sales representative associated with the line or order.
• BOOKED_DATE, REQUEST_DATE, SCHEDULE_SHIP_DATE — truncated to the day, with fallback logic (SCHEDULE_SHIP_DATE falls back to REQUEST_DATE, both default to 01-JAN-1000 when null).
• Extended amounts — UOM_CONV applied to ORDER_QUANTITY_UOM and INVENTORY_ITEM_ID, multiplied by ORDERED_QUANTITY, unit selling price (or list price, or zero), and CONVERT_GLOBAL_AMT using the transactional currency and booked date.
• Extended quantity — UOM-converted ordered quantity.
• VISIBLE_DEMAND_FLAG — defaults to 'N' when null; controls whether the demand is visible to planning.
• ON_HOLD — computed by MSD_SR_UTIL.ON_HOLD from HEADER_ID and LINE_ID, flagging holds for demand exclusion.
• Parent item columns — derived by DECODE on the MSD_PLANNING_PERCENTAGE parameter and MSD_SR_UTIL.FIND_PARENT_ITEM, using LINK_TO_LINE_ID and the ATO_LINE_ID relationship. These are the columns most closely associated with configuration/ATO hierarchy resolution, which is why searches for sr_parent_item_pk typically surface this view.
• OTC.NAME — the transaction type name from OE_TRANSACTION_TYPES_TL.

Common Use Cases and Queries

The primary use case is populating the Demand Planning backlog / booking staging tables. Collection administrators inspect the view to reconcile row counts against OE_ORDER_LINES_ALL, and developers query it to validate UOM and currency conversion behavior before a collection run.

A representative query for backlog by item and organization:

• SELECT INVENTORY_ITEM_ID, SHIP_FROM_ORG_ID, SUM(ORDERED_QUANTITY), SUM(EXTENDED_AMOUNT) FROM APPS.MSD_SR_BACKLOG_V GROUP BY INVENTORY_ITEM_ID, SHIP_FROM_ORG_ID;
• SELECT * FROM APPS.MSD_SR_BACKLOG_V WHERE ON_HOLD = 'Y';
• SELECT * FROM APPS.MSD_SR_BACKLOG_V WHERE VISIBLE_DEMAND_FLAG = 'Y' AND SCHEDULE_SHIP_DATE >= SYSDATE;

Because the view embeds calls to MSD_SR_UTIL functions, filtering on indexed base-table columns through a join to OE_ORDER_LINES_ALL is generally more efficient than scanning the view alone.