Results for “xtr_journals”

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

Overview

XTR_JOURNALS is a Treasury (XTR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores detailed journal entry information generated by Treasury transactions. It sits at the intersection of Treasury processing and the General Ledger, holding the accounting distributions that result from deals, settlements, and related treasury activity before or as they are transferred to Oracle General Ledger. The table is owned by the XTR schema and is documented as VALID in the ETRM repository.

The table carries a heuristic Data Vault classification of link. This reflects its structure: XTR_JOURNALS relates business events (deals, transactions, settlements) to accounting entities (code combinations and sets of books) and to a Treasury batch. In Data Vault modeling terms, it functions as a connecting artifact between hubs rather than as a standalone hub or a descriptive satellite, because its foreign keys bind it to GL_CODE_COMBINATIONS, GL_SETS_OF_BOOKS_11I, and XTR_BATCHES.

Key Information Stored

The documented physical schema contains 34 columns. The most operationally significant columns include:

• JOURNAL_DATE, ORIG_JOURNAL_DATE, ALT_JOURNAL_DATE, and DATE_TYPE — the accounting date, its original value, an alternate date, and the classification of which date is in effect.
• CODE_COMBINATION_ID — the accounting flexfield combination applied to the journal line; foreign key to GL_CODE_COMBINATIONS.
• SET_OF_BOOKS_ID — the ledger (set of books) receiving the entry; foreign key to GL_SETS_OF_BOOKS_11I.
• BATCH_ID — the Treasury batch that produced the journal; foreign key to XTR_BATCHES.
• DEBIT_AMOUNT, CREDIT_AMOUNT, ACCOUNTED_DR, and ACCOUNTED_CR — entered and accounted debit/credit values, permitting both transactional-currency and ledger-currency reporting.
• CURRENCY, AMOUNT_TYPE, and COMPANY_CODE — the currency of the entry, the type of amount represented, and the company/entity identifier.
• DEAL_NUMBER, DEAL_TYPE, DEAL_SUBTYPE, TRANSACTION_NUMBER, SETTLEMENT_NUMBER, PORTFOLIO_CODE, and PRODUCT_TYPE — the Treasury business context of each journal line.
• JNL_REVERSAL_IND — indicates whether the entry is a reversal.
• SUSPENSE_GL, CANCELLED_IN_GL, TRANSFER_TO_EXTERNAL_GL, and ACTION_CODE — status and control flags governing GL transfer and suspense handling.
• GL_SL_LINK_ID — the link to the corresponding General Ledger subledger entry, used to trace or reconcile post-transfer.

The documented unique index XTR_JOURNALS_U1 is a composite business key spanning JOURNAL_DATE, COMPANY_CODE, CURRENCY, DEAL_NUMBER, TRANSACTION_NUMBER, SETTLEMENT_NUMBER, AMOUNT_TYPE, CODE_COMBINATION_ID, DEBIT_AMOUNT, CREDIT_AMOUNT, and JNL_REVERSAL_IND. This composite identifies a distinct accounting line and effectively serves as the natural key; no single surrogate primary-key column is documented in the supplied metadata.

Common Use Cases and Queries

Typical uses include reconciling Treasury-generated accounting entries to General Ledger, investigating suspense balances, and reporting on deal-related journal activity by portfolio or company.

A reconciliation query joining journals back to their originating batch and code combination:

• SELECT j.BATCH_ID, j.DEAL_NUMBER, j.JOURNAL_DATE, j.CURRENCY, j.DEBIT_AMOUNT, j.CREDIT_AMOUNT, j.TRANSFER_TO_EXTERNAL_GL FROM XTR_JOURNALS j WHERE j.SET_OF_BOOKS_ID = :ledger_id AND j.SUSPENSE_GL IS NOT NULL ORDER BY j.JOURNAL_DATE;

Identifying entries not yet transferred, or traced via GL_SL_LINK_ID after transfer, supports subledger-to-GL tie-out. Reporting by CURRENCY, COMPANY_CODE, PORTFOLIO_CODE, or AMOUNT_TYPE is common for treasury accounting summaries, while JNL_REVERSAL_IND and CANCELLED_IN_GL are used to filter for corrections and reversals.

Related Objects

The documented foreign keys establish the primary relationships:

• GL_CODE_COMBINATIONS — joined on XTR_JOURNALS.CODE_COMBINATION_ID; supplies the accounting flexfield detail.
• GL_SETS_OF_BOOKS_11I — joined on XTR_JOURNALS.SET_OF_BOOKS_ID; identifies the ledger the entry belongs to.
• XTR_BATCHES — joined on XTR_JOURNALS.BATCH_ID; identifies the Treasury batch that generated the journal.

In practice, GL subledger interfaces and the General Ledger transfer process consume these rows through the GL_SL_LINK_ID linkage, and Treasury reporting views in the XTR schema present journal detail to end users. Reconciliation and accounting reporting should always constrain by SET_OF_BOOKS_ID and JOURNAL_DATE to make effective use of the composite business key.