Search Results ozf_inventory_tmp_t

Overview

OZF_INVENTORY_TMP_T is a transient staging table owned by the OZF schema within the Oracle Trade Management module (OZF) of Oracle E-Business Suite 12.1.1 and 12.2.2. Its name suffix ("_TMP_T") indicates its function as a temporary or intermediate staging structure used during Trade Management processing cycles—typically during accrual, inventory reconciliation, or deal/settlement workflows where customer account inventory positions must be assembled before being validated and posted to permanent tables.

The object is registered as VALID in the ETRM 12.2.2 physical schema with 13 documented columns. It carries WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), confirming it is an audited, DML-populated staging table rather than a global temporary table. Data is loaded, consumed, and purged or truncated by concurrent Trade Management programs.

Data Vault classification derived heuristically from the foreign key structure is standalone. This is a modeling suggestion only: the table has a single documented outbound reference to HZ_CUST_ACCOUNTS and no incoming dependencies, so it does not participate in a hub/link/satellite topology. It is best treated as a transient staging entity rather than as a durable integration point in a Data Vault model.

Key Information Stored

Although 13 columns are documented, the following are the substantive business and control attributes:

• PARTY_ID — the trading partner party identifier, used to associate the staged inventory row with a specific customer or partner entity in the TCA model.
• CUST_ACCOUNT_ID — the customer account identifier; this is the only documented foreign key column, referencing HZ_CUST_ACCOUNTS.
• INVENTORY_ITEM_ID — the inventory item identifier for which the inventory position is being staged.
• PRIMARY_UOM_CODE — the unit of measure under which PRIMARY_QUANTITY is expressed, ensuring correct interpretation of quantities across items with differing UOMs.
• PRIMARY_QUANTITY — the inventory quantity in the primary unit of measure; the core measured business fact in the row.
• TRANSACTION_DATE — the effective date of the underlying inventory transaction, used for period assignment and dating of the staged record.
• SOURCE_CODE — identifies the originating subsystem or process that populated the row (for example the feeder program or transaction source).
• TRANSFER_TYPE — classifies the nature of the inventory movement being staged, distinguishing transfer behavior applied during downstream processing.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns used to trace which user or concurrent program inserted and last modified each row.

The documented metadata does not identify a declared surrogate primary key or a unique business-key index. In practice, uniqueness is enforced contextually by the loading program through the combination of PARTY_ID, CUST_ACCOUNT_ID, INVENTORY_ITEM_ID, and TRANSACTION_DATE, which are the strongest business-key candidates in the absence of a documented surrogate key.

Common Use Cases and Queries

Because this is a staging table, its primary use case is intermediate processing rather than ad hoc reporting. Typical scenarios include reconciling customer inventory positions prior to accrual calculation, validating that quantities loaded from an external or upstream source map correctly to items and UOMs, and diagnosing incomplete or rejected Trade Management batch runs.

• Inspecting staged rows for a given account before posting: SELECT PARTY_ID, INVENTORY_ITEM_ID, PRIMARY_UOM_CODE, PRIMARY_QUANTITY, TRANSACTION_DATE FROM OZF.OZF_INVENTORY_TMP_T WHERE CUST_ACCOUNT_ID = :p_account_id ORDER BY TRANSACTION_DATE;
• Auditing load provenance by source: SELECT SOURCE_CODE, TRANSFER_TYPE, COUNT(*) FROM OZF.OZF_INVENTORY_TMP_T GROUP BY SOURCE_CODE, TRANSFER_TYPE;
• Identifying stale or orphaned staging data: SELECT * FROM OZF.OZF_INVENTORY_TMP_T WHERE CREATION_DATE < SYSDATE - 7;
• Tracing a problematic batch back to its loader: SELECT CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, COUNT(*) FROM OZF.OZF_INVENTORY_TMP_T GROUP BY CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN;

Reports should generally be directed at the permanent downstream tables; queries against this object are diagnostic in nature.

Related Objects

• HZ_CUST_ACCOUNTS — referenced directly by OZF_INVENTORY_TMP_T.CUST_ACCOUNT_ID; the canonical customer account definition in the TCA model.
• HZ_PARTIES — the parent of HZ_CUST_ACCOUNTS and the source of the PARTY_ID value carried in this table.
• MTL_SYSTEM_ITEMS_B — the inventory item definition corresponding to INVENTORY_ITEM_ID and the UOM conventions applied via PRIMARY_UOM_CODE.
• OZF_ACCRUAL_* / OZF settlement and accrual tables — downstream consumers that typically receive validated and aggregated rows from this staging structure, subject to the Trade Management release in use.
• MTL_TRANSACTIONS / MTL_MATERIAL_TRANSACTIONS — the source of inventory movement facts that feed staged quantity and transaction-date values where inventory data is sourced from Oracle Inventory.

No PL/SQL package, view, or trigger dependencies are recorded in the supplied metadata; any such relationships should be confirmed directly against ALL_DEPENDENCIES and the Trade Management concurrent program definitions for the specific 12.1.1 or 12.2.2 instance.