Results for “journal_date”

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

Overview

XTR_GAIN_LOSS_DNM is a Treasury (XTR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores denormalized gain and loss information for accounting and reporting purposes. Rather than recomputing foreign exchange, revaluation, or deal-level gain/loss figures at query time, Treasury writes a flattened, accounting-ready representation of these amounts into this table so that downstream journal generation, subledger transfer, and reconciliation processes can consume them directly. The table resides in the XTR schema and is classified as VALID in the ETRM object catalog, indicating it is an active, supported data object rather than a deprecated or legacy artifact.

From a heuristic Data Vault modeling perspective, the foreign-key topology mined from the schema suggests this object behaves as a link entity. Its two documented foreign keys — BATCH_ID into XTR_BATCHES and COMPANY_CODE into XTR_PARTY_INFO — connect a processing batch on one side to a legal entity or party on the other, with the denormalized gain/loss measures carried as link-level descriptive payload. This classification is a modeling suggestion rather than an Oracle-documented designation; in physical EBS terms the object is simply a denormalized accounting staging table.

Key Information Stored

The table contains 17 documented columns. The most significant include:

• GAIN_LOSS_DNM_ID — the surrogate primary key, enforced by the XTR_GAIN_LOSS_DNM_PK constraint. This is the unique row identifier and the column most frequently searched, so it is the correct join key when linking to child or dependent records.
• BATCH_ID — foreign key to XTR_BATCHES; identifies the processing run that produced the denormalized accounting rows, enabling batch-level reconciliation and reruns.
• COMPANY_CODE — foreign key to XTR_PARTY_INFO; identifies the company, legal entity, or party associated with the accounting entry.
• DEAL_NUMBER and TRANSACTION_NUMBER — business-key candidates that tie the row back to the originating Treasury deal and transaction, allowing traceability to source instruments.
• AMOUNT and AMOUNT_TYPE — the denormalized gain/loss value and a classification of what the amount represents (for example, realized versus unrealized, or a specific accounting bucket).
• CURRENCY — currency in which the amount is denominated, essential for FX gain/loss interpretation.
• DATE_TYPE and JOURNAL_DATE — control how the amount is dated and into which accounting period it will be journalized.
• ACTION — the processing or accounting action associated with the row.
• REVAL_EFF_FLAG — a revaluation effectiveness indicator, flagging rows relevant to revaluation logic.
• CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — standard EBS WHO-column audit trail, useful for debugging which process or user generated a given row.

Common Use Cases and Queries

Typical scenarios include reconstructing the gain/loss detail for a Treasury batch, verifying that accounting amounts match deal-level expectations, and feeding reconciliation or reporting extracts during period close. A common lookup retrieves a single row by its surrogate key:

SELECT gain_loss_dnm_id, deal_number, transaction_number, amount, amount_type, currency, journal_date FROM xtr.xtr_gain_loss_dnm WHERE gain_loss_dnm_id = :p_id;

Batch-level analysis aggregates amounts by batch and company:

SELECT batch_id, company_code, currency, SUM(amount) FROM xtr.xtr_gain_loss_dnm WHERE batch_id = :p_batch GROUP BY batch_id, company_code, currency;

Revaluation-focused queries filter on REVAL_EFF_FLAG, while period-close reporting typically filters on JOURNAL_DATE. Joining back to XTR_BATCHES and XTR_PARTY_INFO enriches the extract with batch and entity descriptions.

Related Objects

• XTR_BATCHES — referenced via BATCH_ID; parent of the processing run that generated each row.
• XTR_PARTY_INFO — referenced via COMPANY_CODE; supplies company/party attributes.
• XTR_GAIN_LOSS_DNM_PK — the primary key constraint on GAIN_LOSS_DNM_ID that other tables may reference.
• Treasury deal and transaction tables — the logical parents of DEAL_NUMBER and TRANSACTION_NUMBER, used for source traceability.
• Subledger accounting / journal import objects — downstream consumers of the denormalized amounts and JOURNAL_DATE.