Results for “ic_stat_rpt”

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

Overview

IC_STAT_RPT is a GMI (Process Manufacturing Inventory) table that stores Intrastat reporting data within Oracle E-Business Suite 12.1.1 and 12.2.2. Intrastat is the statistical reporting system used by European Union member states to collect data on the trade of goods between member countries. In the EBS context, IC_STAT_RPT captures the transactional detail required to produce these mandatory statistical declarations, linking inventory movements to the item, lot, country of origin, and country of destination information mandated by customs and statistical authorities.

The table resides in the GMI schema, which is the process manufacturing inventory schema, and carries a VALID status in the data dictionary. Its documented physical schema in ETRM 12.2.2 consists of 13 columns, with foreign key relationships extending to item master, lot master, and inventory transaction tables. Based on the foreign key structure, the heuristic Data Vault classification for this object is a link table. This modeling suggestion reflects the table's role in resolving relationships between items, lots, and inventory transactions rather than acting as a standalone hub or descriptive satellite. This classification is heuristic and should be validated against the actual business usage patterns during any data vault implementation.

Key Information Stored

IC_STAT_RPT holds thirteen documented columns that together capture the Intrastat reporting record. The surrogate primary key is SEQ_NO, a system-generated sequence number that uniquely identifies each row. The core business keys are TRANS_ID, ITEM_ID, and LOT_ID, which together establish the linkage to a specific inventory transaction, item, and lot. TRANS_CNT provides additional transaction-level detail.

• SEQ_NO — Surrogate primary key uniquely identifying each Intrastat reporting row.
• TRANS_ID — Foreign key referencing the inventory transaction in IC_TRAN_CMP and IC_TRAN_PND.
• ITEM_ID — Foreign key to the item master (IC_ITEM_MST and IC_ITEM_MST_B).
• LOT_ID — Foreign key to the lot master (IC_LOTS_MST), associating the reporting line to a specific lot.
• ISTAT_QTY — The Intrastat-reported quantity for the transaction line, typically the statistical unit value required by customs.
• CTRY_ORIGIN — Country of origin for the goods, a mandatory Intrastat data element.
• CTRY_DEST — Country of destination for the goods, the counterpart Intrastat field.
• TRANS_CNT — Transaction counter or count associated with the reporting record.
• CREATION_DATE, CREATED_BY — Audit columns recording when and by whom the row was created.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit trail columns tracking the most recent modification.

The combination of TRANS_ID, ITEM_ID, and LOT_ID serves as the practical business-key candidate, though no unique index is explicitly documented in the provided metadata.

Common Use Cases and Queries

IC_STAT_RPT is predominantly used for generating Intrastat returns. Reporting users query this table to aggregate movements by country of origin, country of destination, and reporting period. A typical query pattern joins to IC_ITEM_MST and IC_LOTS_MST to enrich the statistical data with item descriptions and lot attributes.

One common pattern aggregates reported quantities by country pair:

• SELECT CTRY_ORIGIN, CTRY_DEST, SUM(ISTAT_QTY) FROM GMI.IC_STAT_RPT GROUP BY CTRY_ORIGIN, CTRY_DEST;

Another frequent use case links the statistical record back to the originating inventory transaction to validate completeness of the Intrastat extract:

• SELECT r.SEQ_NO, r.ITEM_ID, t.TRANS_ID FROM GMI.IC_STAT_RPT r, GMI.IC_TRAN_CMP t WHERE r.TRANS_ID = t.TRANS_ID;

Reconciliation queries compare ISTAT_QTY against the underlying transaction quantity to detect discrepancies prior to submission. Auditors also use the audit columns to trace when a given Intrastat line was generated or last amended.

Related Objects

The following objects are related to IC_STAT_RPT through documented foreign key relationships and shared business context:

• IC_ITEM_MST — Item master; joined on ITEM_ID.
• IC_ITEM_MST_B — Item master child/base table; joined on ITEM_ID.
• IC_LOTS_MST — Lot master; joined on ITEM_ID and LOT_ID.
• IC_TRAN_CMP — Completed inventory transactions; joined on TRANS_ID.
• IC_TRAN_PND — Pending inventory transactions; joined on TRANS_ID.

These relationships confirm IC_STAT_RPT's role as a link table between inventory movement transactions and the item/lot dimensions, and they provide the join paths needed for both Intrastat reporting and transactional reconciliation.