Search Results msc_bis_targets_u2

Overview

MSC.MSC_BIS_TARGETS is a transactional configuration table in the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema, MSC. It stores the target values that drive Business Intelligence System (BIS) reports and performance-measurement dashboards. Each row defines a target, threshold ranges, and notification responsibilities for a fully qualified business dimension combination — organization, time, business plan, and up to five additional user-defined dimensions. This allows planners to compare actual supply chain performance against defined goals and to trigger alerts when actuals fall into low, medium, or high range bands.

The table is owned by the MSC schema, holds 34 documented columns, resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and carries a status of VALID in both 12.1.1 and 12.2.2. Mined from its foreign key structure, the heuristic Data Vault classification is link: the table connects dimension members, business plans, and target-level reference data, and its business keys therefore behave like link-hash candidates rather than pure reference or hub data. This classification is offered as a modeling suggestion, not a normative statement of the physical design.

Key Information Stored

The surrogate primary key is MSC_BIS_TARGETS_PK, defined on (TARGET_ID, SR_INSTANCE_ID). Two unique indexes act as business-key candidates:

• MSC_BIS_TARGETS_U1 (SR_INSTANCE_ID, TARGET_ID) — enforces uniqueness of a target within a source application instance.
• MSC_BIS_TARGETS_U2 (TARGET_LEVEL_ID, SR_INSTANCE_ID, ORG_LEVEL_VALUE_ID, BUSINESS_PLAN_ID, TIME_LEVEL_VALUE_ID, DIM1_LEVEL_VALUE_ID through DIM5_LEVEL_VALUE_ID) — the full dimensional grain. This is the index users encounter when troubleshooting duplicate-target errors, since it constrains a target to one row per level, instance, org, plan, time, and dimension combination.

The most significant columns include TARGET_ID (target identifier), SR_INSTANCE_ID (source application instance), TARGET_LEVEL_ID (target level reference), and BUSINESS_PLAN_ID (business plan reference). The dimension qualifiers ORG_LEVEL_VALUE_ID, TIME_LEVEL_VALUE_ID, and DIM1–DIM5_LEVEL_VALUE_ID are VARCHAR2(80) values that scope the target to a specific slice of the planning cube. TARGET holds the numeric goal, while RANGE1_LOW/HIGH, RANGE2_LOW/HIGH, and RANGE3_LOW/HIGH define three threshold bands used for traffic-light style scoring. NOTIFY_RESP1_ID through NOTIFY_RESP3_ID, with matching _SHORT_NAME columns, identify the responsibility owners notified when a range is breached. Audit and concurrency columns include LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REFRESH_NUMBER, which supports concurrent-program-driven refreshes.

Common Use Cases and Queries

Typical usage centres on target-versus-actual reporting. Planners retrieve the target and range bands for a given dimensional slice, join to the business plan, and compare against measured values. A representative pattern:

• SELECT t.target_id, t.target, t.range1_low, t.range1_high, t.range2_low, t.range2_high, t.range3_low, t.range3_high FROM msc.msc_bis_targets t WHERE t.sr_instance_id = :instance_id AND t.business_plan_id = :plan_id AND t.org_level_value_id = :org_id;
• Filtering by TARGET_LEVEL_ID to pull all targets at a level: SELECT * FROM msc.msc_bis_targets WHERE target_level_id = :level_id;
• Locating duplicate business keys that violate MSC_BIS_TARGETS_U2: SELECT target_level_id, org_level_value_id, business_plan_id, time_level_value_id, COUNT(*) FROM msc.msc_bis_targets GROUP BY target_level_id, org_level_value_id, business_plan_id, time_level_value_id HAVING COUNT(*) > 1;
• Identifying notification owners: SELECT target_id, notify_resp1_short_name, notify_resp2_short_name, notify_resp3_short_name FROM msc.msc_bis_targets;

Because the table is BI-oriented, reports usually aggregate across the dimension columns and compare TARGET to a computed metric, classifying the result into one of the three ranges. REFRESH_NUMBER and the program columns allow administrators to track which concurrent request last repopulated a target set.

Related Objects

The documented foreign keys anchor the table to its reference dimension data, and the unique indexes imply additional join paths:

• MSC.MSC_BIS_TARGET_LEVELS — joined on TARGET_LEVEL_ID; supplies the level definition that a target belongs to.
• MSC.MSC_BIS_BUSINESS_PLANS — joined on SR_INSTANCE_ID (and BUSINESS_PLAN_ID); identifies the plan context for the target.
• MSC.MSC_BIS_TARGETS_U2 — the unique index that supports dimensional lookups across ORG_LEVEL_VALUE_ID, TIME_LEVEL_VALUE_ID, and DIM1–DIM5.
• MSC.MSC_BIS_TARGETS_U1 — the unique index supporting target/instance resolution.
• MSC.MSC_BIS_TARGETS_PK — the primary key used by dependent child tables or referential queries.

In practice, these targets are consumed by the BIS reporting layer and by ASCP dashboards that read target definitions for scorecard rendering and exception notification. Administrators should treat the dimensional columns as the effective business key and validate loads against MSC_BIS_TARGETS_U2 to avoid duplicate-target conflicts during data refresh.