Results for “msc_bor_requirements_pk”

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

Overview

MSC_BOR_REQUIREMENTS is a table within the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) product module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the set of resources required in an organization for an item, functioning as the planning-side representation of bill-of-resources (BOR) requirements used during material and capacity planning runs. Each row associates an assembly item, a resource, and a department within a specific plan, organization, and source instance context, allowing ASCP to explode resource demands across the planning horizon.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure suggests this object behaves as a link table. It resolves and connects multiple business entities — assemblies, resources, departments, and bills of resources — within the context of a given plan, rather than representing an isolated hub entity. The composite primary key MSC_BOR_REQUIREMENTS_PK (PLAN_ID, TRANSACTION_ID, SR_INSTANCE_ID) reinforces this role, tying each requirement row to a specific plan execution and source instance transaction.

Key Information Stored

The table documents 45 columns in the 12.2.2 physical schema. The most significant columns for functional and reporting purposes include:

• PLAN_ID, TRANSACTION_ID, SR_INSTANCE_ID — the composite primary key, identifying the plan, the transaction within that plan, and the source instance respectively.
• BILL_OF_RESOURCES — foreign key to MSC_BILL_OF_RESOURCES, linking the requirement to its parent BOR definition.
• ASSEMBLY_ITEM_ID, SOURCE_ITEM_ID — the assembly item for which resources are required; ASSEMBLY_ITEM_ID references MSC_SYSTEM_ITEMS.
• RESOURCE_ID, DEPARTMENT_ID, ORGANIZATION_ID — identify the resource, department, and organization context; these join to MSC_DEPARTMENT_RESOURCES and BOM_DEPARTMENTS.
• RESOURCE_UNITS, RESOURCE_DEPARTMENT_HOURS, BASIS, ASSEMBLY_USAGE — quantitative measures describing how much of a resource is consumed and on what basis.
• OPERATION_SEQUENCE_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM, SETBACK_DAYS — routing and scheduling attributes that position the resource requirement in the operation sequence and time horizon.
• ORIGINATION_TYPE, REFRESH_NUMBER — indicate the origin of the record and the planning refresh cycle.
• Standard audit columns (LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID, and the ATTRIBUTE1–15 flexfields) support traceability and extensibility.

The business-key candidate MSC_BOR_REQUIREMENTS_U1 (PLAN_ID, SR_INSTANCE_ID, TRANSACTION_ID) provides an alternative unique identification beyond the surrogate primary key.

Common Use Cases and Queries

Typical reporting scenarios include capacity analysis by department and resource, assembly-level resource rollups, and reconciliation of planned resource consumption against source BOR definitions. A representative query joining requirements to resources and items:

• SELECT r.PLAN_ID, r.ORGANIZATION_ID, r.ASSEMBLY_ITEM_ID, r.RESOURCE_ID, r.DEPARTMENT_ID, r.RESOURCE_UNITS, r.RESOURCE_DEPARTMENT_HOURS FROM MSC.MSC_BOR_REQUIREMENTS r WHERE r.PLAN_ID = :plan_id AND r.ORGANIZATION_ID = :org_id;
• Join to MSC_DEPARTMENT_RESOURCES on (PLAN_ID, ORGANIZATION_ID, RESOURCE_ID, DEPARTMENT_ID, SR_INSTANCE_ID) to obtain resource master detail.
• Join to MSC_SYSTEM_ITEMS on (ASSEMBLY_ITEM_ID, PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID) to resolve item descriptions.
• Join to BOM_DEPARTMENTS on DEPARTMENT_ID for department naming in capacity reports.

Related Objects

The documented foreign-key relationships identify the following significant related objects:

• MSC_BILL_OF_RESOURCES — referenced via BILL_OF_RESOURCES; the parent BOR definition.
• MSC_SYSTEM_ITEMS — referenced via ASSEMBLY_ITEM_ID; the planned assembly.
• MSC_DEPARTMENT_RESOURCES — referenced via PLAN_ID, ORGANIZATION_ID, RESOURCE_ID, SR_INSTANCE_ID, DEPARTMENT_ID; the resource master.
• BOM_DEPARTMENTS — referenced via DEPARTMENT_ID; department definition.

These relationships make MSC_BOR_REQUIREMENTS a central intersection point for ASCP capacity and resource planning reporting.