Results for “msc_alternate_resources_v”

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

Overview

APPS.MSC_ALTERNATE_RESOURCES_V is a reporting view within the MSC – Advanced Supply Chain Planning product family. It exposes alternate resource definitions that are attached to routing operations across planning instances, consolidating data from department resource definitions, operation resource sequences, and operation resources into a single queryable structure. Its principal role is to make alternate resource assignments, their usage basis, and their associated capacity limits available for reporting, integration, and diagnostic purposes without requiring consumers to reconstruct the multi-table join manually.

The view is particularly relevant to searches involving MAX_RESOURCE_UNITS, since that column is one of the capacity-related attributes surfaced directly by the view. In planning contexts, MAX_RESOURCE_UNITS expresses the maximum number of resource units that can be consumed or allocated at a given operation for the referenced resource, while RESOURCE_UNITS captures the actual usage quantity recorded on the operation. The view therefore supports analysis of both planned and constraint-bounded resource consumption.

The view is owned by the APPS schema and carries VALID status in the ETRM 12.2.2 metadata, confirming it is available for query in a supported Oracle E-Business Suite 12.1.1 / 12.2.2 environment.

Underlying Base Objects

The view is defined over three documented base objects, each referenced through a SYNONYM in the APPS schema:

• MSC_DEPARTMENT_RESOURCES – provides department-level resource context, including DEPARTMENT_ID, DEPARTMENT_CODE, and RESOURCE_CODE.
• MSC_OPERATION_RESOURCES – the primary source of the operation-level resource rows, supplying ALTERNATE_NUMBER, PRINCIPAL_FLAG, BASIS_TYPE, RESOURCE_USAGE, MAX_RESOURCE_UNITS, RESOURCE_UNITS, and UOM_CODE.
• MSC_OPERATION_RESOURCE_SEQS – supplies the routing and operation sequencing that links a resource to a specific routing operation.

The join is driven by PLAN_ID, SR_INSTANCE_ID, ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, and RESOURCE_SEQ_NUM. The department join is expressed as an outer join (DEPT.DEPARTMENT_ID(+) = MORS.DEPARTMENT_ID), meaning operation resource sequence rows are preserved even where a department resource record does not exist. This outer-join behavior is significant when interpreting results: DEPARTMENT_ID, DEPARTMENT_CODE, and RESOURCE_CODE may be null for operations whose department resource definition is absent.

Key Columns

• PLAN_ID, SR_INSTANCE_ID – identify the plan and source instance to which the resource belongs.
• ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, RESOURCE_SEQ_NUM – locate the resource within a routing and operation sequence.
• DEPARTMENT_ID, DEPARTMENT_CODE, RESOURCE_CODE – department and resource identification codes.
• RESOURCE_ID – the resource identifier used to link to resource master data.
• ALTERNATE_NUMBER – indicates whether the resource is a primary or an alternate assignment.
• PRINCIPAL_FLAG – flags the principal resource for the operation.
• BASIS_TYPE – the basis on which resource usage is calculated.
• RESOURCE_USAGE – the quantity of the resource consumed per unit of the operation.
• MAX_RESOURCE_UNITS – the maximum resource units permitted at the operation; a key constraint attribute in capacity analysis.
• RESOURCE_UNITS – the resource units recorded against the operation.
• UOM_CODE – the unit of measure applicable to the resource quantities.
• Audit columns – LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include auditing alternate resource assignments for a plan, comparing declared maximum capacity against recorded usage, and reconciling planning data against departmental resource setup. The following query lists principal resources with their maximum and actual units:

SELECT plan_id, routing_sequence_id, operation_sequence_id, resource_id, alternate_number, principal_flag, basis_type, resource_usage, max_resource_units, resource_units, uom_code FROM apps.msc_alternate_resources_v WHERE plan_id = :plan_id AND principal_flag = 'Y' ORDER BY routing_sequence_id, operation_sequence_id, resource_seq_num;

To isolate alternates whose recorded usage exceeds the declared maximum, a comparison filter can be applied:

SELECT resource_id, alternate_number, resource_units, max_resource_units FROM apps.msc_alternate_resources_v WHERE resource_units > max_resource_units AND plan_id = :plan_id;

Because department columns are outer-joined, a query listing resources without a matching department record is also useful for data-quality validation:

SELECT plan_id, resource_id, department_code FROM apps.msc_alternate_resources_v WHERE department_code IS NULL;