Results for “msc_exception_thresholds_v”

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

Overview

MSC_EXCEPTION_THRESHOLDS_V is a reporting view owned by the APPS schema within the MSC (Advanced Supply Chain Planning) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to present a decoded, human-readable catalogue of the planning exception types recognized by Advanced Supply Chain Planning, together with the metadata that drives how each exception behaves in the planning workbench. Rather than exposing raw lookup codes, the view joins multiple MFG_LOOKUPS rows to derive descriptive group names, exception names, and a series of boolean-style flag columns that indicate whether a given exception participates in threshold, horizon, or period comparisons.

The view returns one row per MSC exception type, effectively delivering the complete exception registry used by the planning engine and the exception management UI. Because it resolves numeric lookup codes into meaningful text and governs which columns are applicable for each exception, it is the reference object of choice whenever a report, concurrent program, or integration must interpret exception output without hard-coding lookup meanings. It is a read-only, deterministic view that carries no application logic beyond the DECODE expressions in its definition.

In Oracle EBS reporting and integration terms, the view functions as a semantic layer over configuration lookup data. External systems consuming planning exception extracts, custom BI Publisher reports, and OBIEE repositories commonly reference it to label exception results and to determine which threshold prompts should be exposed to users.

Underlying Base Objects

The ETRM 12.2.2 metadata documents two referenced base objects: MFG_LOOKUPS, which is itself a view, and MSC_USER_EXCEPTIONS, registered as a synonym. The view definition is self-joined three times over MFG_LOOKUPS, aliased MFGLOOKUP1, MFGLOOKUP2, and MFGLOOKUP3.

• MFGLOOKUP1 supplies the exception group lookup type MSC_X_EXCEPTION_GROUP, returning EXCEPTION_GROUP_NAME.
• MFGLOOKUP2 supplies the exception type lookup type MSC_X_EXCEPTION_TYPE, providing the exception name and the numeric exception type code that drives all DECODE-based flags.
• MFGLOOKUP3 supplies the threshold translation lookup type MSC_X_EXCEP_THRESHOLDS_TRANS, providing THRESHOLD_MEANING where a threshold is applicable.

The join between the group and the exception type is established by mapping each MSC_X_EXCEPTION_TYPE lookup code to its parent MSC_X_EXCEPTION_GROUP lookup code through a large DECODE expression, which enumerates the association for every documented exception code (1 through 48). MSC_USER_EXCEPTIONS provides the user-facing exception definition context in which these lookups are interpreted.

Key Columns

• EXCEPTION_GROUP_NAME — the descriptive name of the exception group, derived from MSC_X_EXCEPTION_GROUP.
• EXCEPTION_NAME — the descriptive name of the individual exception type, derived from MSC_X_EXCEPTION_TYPE.
• EXCEPTION_TYPE — the numeric lookup code that uniquely identifies the exception.
• THRESHOLD_FLAG — Y or N indicating whether the exception supports a numeric threshold. Codes 9, 10, 29, 30, 33, and 34 return N; all others return Y.
• THRESHOLD_MEANING — the translated threshold description from MSC_X_EXCEP_THRESHOLDS_TRANS, returned as NULL for the six codes that do not support thresholds.
• HORIZON_FLAG — Y when the exception is evaluated against a planning horizon (codes 1, 2, 5, 6, 7, 8, 13, and 19 to 28); otherwise N.
• PERIOD_FLAG — Y for period-oriented exceptions (codes 35 through 48).
• CUSTOMER_SUPPLIER_FLAG — C for customer-facing, S for supplier-facing, and B for both, based on the exception type code.

Common Use Cases and Queries

Typical usage includes driving exception selection lists in custom planning reports, validating which exceptions accept user thresholds, and populating BI Publisher layouts with consistent group and exception labels.

List all exceptions that accept a threshold, grouped by exception group:

• SELECT exception_group_name, exception_name, threshold_meaning FROM msc_exception_thresholds_v WHERE threshold_flag = 'Y' ORDER BY exception_group_name, exception_name;

Identify supplier-facing exceptions evaluated against a horizon:

• SELECT exception_name, exception_type FROM msc_exception_thresholds_v WHERE customer_supplier_flag IN ('S','B') AND horizon_flag = 'Y';

Retrieve the complete lookup mapping for validation or migration purposes:

• SELECT exception_type, exception_name, exception_group_name FROM msc_exception_thresholds_v ORDER BY exception_type;

Because the view draws solely from lookup configuration, it returns data without dependence on plan run results, making it safe for use in reference queries and metadata-driven integrations.