Results for “gl_extract_history”

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

Overview

GL_EXTRACT_HISTORY is a General Ledger reference table in the GL schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It records successful full or incremental extract requests whose source data originates from the GL_BALANCES_DELTA table, populated by concurrent programs such as Analyzer-Extract Incremental Balances. The table therefore functions as a processing ledger for balance-extraction activity, capturing the dimension combinations and run identifiers associated with each completed extract so that downstream consumers (for example, internal reporting, consolidation, or external data warehouse loads) can reconcile what was extracted, for which ledger, and for which accounting period.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from its foreign-key structure is link. This is a modeling suggestion: the table primarily resolves relationships among ledgers, budget versions, encumbrance types, and summary templates, keyed by extraction context rather than describing a single business entity. The physical table contains 14 documented columns and is owned by the GL schema; its status is recorded as VALID.

Key Information Stored

The primary key, GL_EXTRACT_HISTORY_PK, is a composite business key rather than a system-generated surrogate. A parallel unique index, GL_EXTRACT_HISTORY_U1, covers the identical column set, confirming the business-key candidate:

• LEDGER_ID — identifies the ledger (set of books) from which balances were extracted; joins to GL_SETS_OF_BOOKS_11I.
• EXTRACT_PROGRAM_CODE — the extract program that produced the history row (full versus incremental variants).
• PERIOD_NAME — the accounting period for which the extract was performed.
• ACTUAL_FLAG — distinguishes actual, budget, or encumbrance balance types.
• EXTRACT_LEVEL_CODE — the level of detail or aggregation at which extraction occurred.
• BUDGET_VERSION_ID — budget version context; joins to GL_BUDGET_VERSIONS.
• ENCUMBRANCE_TYPE_ID — encumbrance type context; joins to GL_ENCUMBRANCE_TYPES.
• TEMPLATE_ID — summary template used; joins to GL_SUMMARY_TEMPLATES.
• MAX_DELTA_RUN_ID — the high-water mark of the delta run processed, enabling incremental extraction to resume from the correct point.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created and last modified the row and when.

Common Use Cases and Queries

Typical usage centers on auditing extraction completeness and driving incremental loads. A common query identifies the most recent extract for a ledger and period:

SELECT ledger_id, period_name, extract_program_code,
       MAX_DELTA_RUN_ID, last_update_date
FROM   gl_extract_history
WHERE  ledger_id = :ledger_id
AND    period_name = :period_name
ORDER BY last_update_date DESC;

Reporting scenarios include verifying that all required ledger/period/budget-version combinations have been extracted before a downstream load, reconciling the delta watermark against GL_BALANCES_DELTA, and producing an audit trail of who ran extracts and when. The table also supports exception reporting to detect periods lacking a successful full extract before an incremental run.

Related Objects

• GL_BALANCES_DELTA — the delta balances table that supplies the extract data recorded here.
• GL_SETS_OF_BOOKS_11I — joined via GL_EXTRACT_HISTORY.LEDGER_ID.
• GL_BUDGET_VERSIONS — joined via GL_EXTRACT_HISTORY.BUDGET_VERSION_ID.
• GL_ENCUMBRANCE_TYPES — joined via GL_EXTRACT_HISTORY.ENCUMBRANCE_TYPE_ID.
• GL_SUMMARY_TEMPLATES — joined via GL_EXTRACT_HISTORY.TEMPLATE_ID.

These relationships are enforced through the documented foreign keys, and the composite unique index GL_EXTRACT_HISTORY_U1 governs row uniqueness across the extraction dimensions.