Results for “ic_tran_arc”

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

Overview

The GMI.IC_TRAN_ARC table is the inventory transaction archive for Oracle Process Manufacturing (OPM) Inventory, shipped with Oracle E-Business Suite 12.1.1 and 12.2.2. It stores historical inventory transaction records that have been relocated from the active transaction tables, preserving the full audit trail of material movements, adjustments, receipts, issues, and lot activity across warehouses, organizations, and cost organizations. Archiving prevents unbounded growth of live transaction tables while retaining data required for regulatory traceability, cost reconciliation, and historical reporting.

Because the table carries a single-column surrogate primary key (TRANS_ID) and a dense set of foreign key relationships to master and reference tables, its heuristic Data Vault classification is a link. In a dimensional or Data Vault model, IC_TRAN_ARC would be modeled as a transactional link connecting item, lot, warehouse, location, organization, document, reason, grade, and unit-of-measure hubs, with measurable quantities (TRANS_QTY, TRANS_QTY2) as link-level facts rather than satellite attributes.

Key Information Stored

The surrogate primary key is TRANS_ID, enforced by the unique index IC_TRAN_ARC_PK. No additional unique business key is documented, so TRANS_ID is the only guaranteed row identifier. The most significant columns include:

• ITEM_ID — the inventory item transacted; joins to IC_ITEM_MST and IC_ITEM_MST_B.
• LOT_ID — the lot affected; joins to IC_LOTS_MST.
• WHSE_CODE and LOCATION — warehouse and storage location; join respectively to IC_WHSE_MST and IC_LOCT_MST.
• ORGN_CODE and CO_CODE — organization and cost organization; both join to SY_ORGN_MST and SY_ORGN_MST_B.
• TRANS_DATE and CREATION_DATE — the effective transaction date versus the record creation timestamp.
• TRANS_QTY and TRANS_QTY2 — primary and secondary transaction quantities, with TRANS_UM and TRANS_UM2 holding the corresponding units of measure (SY_UOMS_MST).
• DOC_ID, DOC_TYPE, DOC_LINE, LINE_TYPE, LINE_ID, LINE_DETAIL_ID — document and line identity linking the movement to its source document (DOC_TYPE joins SY_DOCS_MST).
• REASON_CODE — the adjustment or movement reason (SY_REAS_CDS / SY_REAS_CDS_B).
• QC_GRADE and LOT_STATUS — quality grade and lot status at transaction time (GMD_GRADES_B, QC_GRAD_MST, IC_LOTS_STS).
• TRANS_STAT — transaction status indicator.
• ARCHIVE_DATE and PURGE_ID — supporting archival and purge lineage.
• GL_POSTED_IND, INVOICED_FLAG, STAGED_IND, LOT_COSTED_IND, INTORDER_POSTED_IND — posting and costing state flags relevant to reconciliation.

Common Use Cases and Queries

Primary use cases include historical inventory reconciliation, audit and compliance traceability, lot genealogy reporting, and cost/invoice posting analysis. A typical query reconciles archived movements for a given item and lot within a date range:

• SELECT TRANS_ID, ITEM_ID, LOT_ID, WHSE_CODE, TRANS_DATE, TRANS_QTY, TRANS_UM FROM GMI.IC_TRAN_ARC WHERE ITEM_ID = :item AND TRANS_DATE BETWEEN :from AND :to;
• Join to IC_ITEM_MST and IC_LOTS_MST to resolve item and lot descriptions for reporting.
• Filter on GL_POSTED_IND = 'N' to identify archived transactions with unresolved general ledger posting.
• Aggregate TRANS_QTY by ORGN_CODE and WHSE_CODE for period-over-period movement analysis.
• Use ARCHIVE_DATE and PURGE_ID to isolate a specific archival batch for audit sampling.

Related Objects

IC_TRAN_ARC is highly referential. The most significant related tables, with their join columns, are:

• IC_ITEM_MST / IC_ITEM_MST_B — on ITEM_ID, for item master attributes.
• IC_LOTS_MST — on ITEM_ID and LOT_ID, for lot master detail.
• IC_WHSE_MST / IC_LOCT_MST — on WHSE_CODE and LOCATION, for warehouse and location context.
• SY_ORGN_MST / SY_ORGN_MST_B — on ORGN_CODE and CO_CODE, for organization and cost organization.
• SY_UOMS_MST — on TRANS_UM and TRANS_UM2, for unit-of-measure values.
• SY_DOCS_MST — on DOC_TYPE, resolving the source document category.
• SY_REAS_CDS / SY_REAS_CDS_B — on REASON_CODE, for reason-code descriptions.
• IC_LOTS_STS — on LOT_STATUS, for lot status meaning.
• GMD_GRADES_B / QC_GRAD_MST — on QC_GRADE, for quality grade reference data.

Together these relationships allow archived transactions to be fully rehydrated for audit and reporting without dependence on the live transaction tables.