Results for “mtl_material_txn_allocation_u1”
10 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MTL_MATERIAL_TXN_ALLOCATIONS is an Oracle Inventory (INV) transaction table that records the distribution of material transaction quantities across individual repetitive manufacturing schedules. When a single inventory transaction — such as an issue, receipt, or completion — is applied to more than one repetitive schedule within an organization, Oracle EBS writes one allocation row per affected schedule. The table therefore acts as the bridge that reconciles a single row in MTL_MATERIAL_TRANSACTIONS with the multiple WIP_REPETITIVE_SCHEDULES that consumed or produced those units.
The object resides in the INV schema, is classified as VALID, and is documented as "Unit allocations among repetitive schedules." Under the heuristic Data Vault classification mined from its foreign key structure, the table is best understood as a link entity: its natural role is to resolve a many-to-many relationship between material transactions and repetitive schedules, qualified by organization. It carries no descriptive attributes of its own beyond the allocation quantities and audit columns, which are conventionally modeled as link-level or degenerate attributes rather than a full satellite payload.
Key Information Stored
The documented physical schema contains 15 columns in Oracle EBS 12.2.2. The most significant are the composite business key members and the quantity measures:
- TRANSACTION_ID — Foreign key to MTL_MATERIAL_TRANSACTIONS; identifies the parent material transaction whose quantity is being distributed.
- REPETITIVE_SCHEDULE_ID — Foreign key to WIP_REPETITIVE_SCHEDULES; identifies the specific repetitive schedule receiving the allocated units.
- ORGANIZATION_ID — Foreign key to MTL_PARAMETERS; scopes the allocation to a single inventory organization.
- PRIMARY_QUANTITY — The allocated quantity expressed in the transaction's primary unit of measure.
- TRANSACTION_QUANTITY — The allocated quantity expressed in the transaction's transaction unit of measure.
- TRANSACTION_DATE — The effective date of the allocation, aligned to the parent transaction.
- LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns.
- REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrency and job-context columns identifying the concurrent program that created the allocation.
The surrogate primary key is enforced through MTL_MATERIAL_TXN_ALLOCATION_PK, whose documented unique index, MTL_MATERIAL_TXN_ALLOCATION_U1, covers (TRANSACTION_ID, REPETITIVE_SCHEDULE_ID, ORGANIZATION_ID). Because the unique index columns are drawn entirely from foreign key references, they also constitute the business-key candidate: no additional natural key column exists, and uniqueness is guaranteed only for a given transaction–schedule–organization combination.
Common Use Cases and Queries
Allocation data is primarily consumed for repetitive manufacturing reconciliation, backflush auditing, and transaction-level drill-downs from schedule performance reports. Typical queries join the allocation rows back to the transaction header and to the schedule to reconstruct which repetitive schedules absorbed a given issue or completion.
- Reconciling total allocated quantity against the parent transaction quantity to detect allocation shortfalls.
- Reporting material consumed per repetitive schedule over a date range using TRANSACTION_DATE.
- Auditing backflush and WIP issue transactions by concurrent request using REQUEST_ID and PROGRAM_ID.
A representative pattern:
SELECT a.TRANSACTION_ID, a.REPETITIVE_SCHEDULE_ID, a.ORGANIZATION_ID, a.PRIMARY_QUANTITY, a.TRANSACTION_QUANTITY, a.TRANSACTION_DATE FROM MTL_MATERIAL_TXN_ALLOCATIONS a WHERE a.ORGANIZATION_ID = :org_id AND a.TRANSACTION_DATE BETWEEN :start_date AND :end_date;
Joining to MTL_MATERIAL_TRANSACTIONS on TRANSACTION_ID supplies transaction type and item context, while joining to WIP_REPETITIVE_SCHEDULES on REPETITIVE_SCHEDULE_ID resolves the schedule identity for reporting.
Related Objects
The documented foreign keys identify three direct dependencies, and the primary key relationship anchors the table to the inventory transaction model:
- MTL_MATERIAL_TRANSACTIONS — joined on TRANSACTION_ID; the parent transaction header that owns the allocated quantity.
- WIP_REPETITIVE_SCHEDULES — joined on REPETITIVE_SCHEDULE_ID; the repetitive schedule receiving the allocated units.
- MTL_PARAMETERS — joined on ORGANIZATION_ID; supplies organization-level inventory defaults and validation.
- MTL_TRANSACTION_TYPES and MTL_SYSTEM_ITEMS_B — commonly reached through the parent transaction to classify and describe the allocation.
- WIP_MOVE_TXN_INTERFACE and related WIP interfaces — consume allocation results during repetitive schedule processing.
Because all three foreign keys participate in the unique index, any change to the parent transaction, the target schedule, or the organization invalidates the allocation identity, making referential integrity from these objects the controlling factor for the table's data quality.
-
Unit allocations among repetitive schedules
-
Unit allocations among repetitive schedules
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
eTRM - INV Tables and Views 12.1.1
-
eTRM - INV Tables and Views 12.2.2