Search Results gl_arcvive_references_pk

Overview

GL_ARCHIVE_REFERENCES is a General Ledger archive table in Oracle E-Business Suite (12.1.1 and 12.2.2) that preserves historical rows of GL_IMPORT_REFERENCES after the corresponding journal data has been purged or archived from the active ledger tables. Its role is to retain the linkage between posted General Ledger journal lines and the originating subledger documents, ensuring that audit trails and reconciliation references remain queryable long after the source transactions have been removed from operational tables. As an archive companion to GL_IMPORT_REFERENCES, it is typically populated during GL archive and purge routines rather than through direct user or application entry.

From a heuristic Data Vault modeling perspective, the FK structure indicates that GL_ARCHIVE_REFERENCES behaves as a link object. It resolves many-to-many style associations between journal lines (GL_JE_LINES) and subledger document sequence identifiers, carrying descriptive sequence values rather than standing as an independent hub of business entities or a pure descriptive satellite.

Key Information Stored

The documented metadata identifies the following significant columns:

• JE_HEADER_ID — The foreign key to the journal header/line context; part of the composite primary key GL_ARCVIVE_REFERENCES_PK and also a component of the FK to GL_JE_LINES.
• JE_LINE_NUM — The journal line number; pairs with JE_HEADER_ID in the composite primary key and in the GL_JE_LINES foreign key, uniquely locating the archived journal line.
• JE_BATCH_ID — The batch identifier; foreign key to GL_JE_BATCHES, allowing archived references to be grouped by their originating journal batch.
• SUBLEDGER_DOC_SEQUENCE_ID — Component of the unique key GL_ARCHIVE_REFERENCES_UK1; identifies the subledger document sequence.
• SUBLEDGER_DOC_SEQUENCE_VALUE — The sequence value paired with SUBLEDGER_DOC_SEQUENCE_ID in GL_ARCHIVE_REFERENCES_UK1, representing the subledger document reference preserved for audit.

Two candidate keys are documented. GL_ARCVIVE_REFERENCES_PK (JE_HEADER_ID, JE_LINE_NUM) serves as the surrogate row locator for the archived journal line, while GL_ARCHIVE_REFERENCES_UK1 (SUBLEDGER_DOC_SEQUENCE_ID, SUBLEDGER_DOC_SEQUENCE_VALUE) is the business-key candidate that uniquely identifies the retained subledger document reference.

Common Use Cases and Queries

Primary use cases include audit reconstruction, reconciliation of subledger documents to journals after purge, and reporting on historical import references no longer present in GL_IMPORT_REFERENCES. A join between the archive and active tables is common when tracing a document across the retention boundary:

• Retrieving archived references by journal line: SELECT je_header_id, je_line_num, subledger_doc_sequence_id, subledger_doc_sequence_value FROM gl_archive_references WHERE je_header_id = :header_id;
• Grouping archived references by batch: join JE_BATCH_ID to GL_JE_BATCHES for batch-level audit reporting.
• Reconciling a subledger document: filter on SUBLEDGER_DOC_SEQUENCE_ID and SUBLEDGER_DOC_SEQUENCE_VALUE using the UK1 columns.
• Comparison reporting: union archived rows with GL_IMPORT_REFERENCES to detect gaps between active and archived retention windows.

Related Objects

The following objects are most significant, based on the documented relationships:

• GL_IMPORT_REFERENCES — The source table for which this is the archive counterpart.
• GL_JE_LINES — Referenced via JE_HEADER_ID and JE_LINE_NUM, providing the journal line context.
• GL_JE_BATCHES — Referenced via JE_BATCH_ID, supporting batch-level grouping.
• GL_JE_HEADERS — Parent context for the referenced journal header.
• GL_ARCHIVE_REFERENCES — Related through the two documented unique keys (GL_ARCVIVE_REFERENCES_PK and GL_ARCHIVE_REFERENCES_UK1), which govern row integrity.

Note: the metadata states the table is "Not implemented in this database," so availability depends on the environment and any GL archive/purge configuration in place.