Search Results fv_be_trx_dtls




Overview
Explore in AI Chat

Overview

FV_BE_TRX_DTLS is the base transaction detail table within the Federal Financials (FV) product of Oracle E-Business Suite, holding transaction-level records for all budget execution activity. It is owned by the FV schema and is documented in ETRM for releases 12.1.1 and 12.2.2. The table serves as the underlying data source for the detail block of the Define Appropriations and Define Fund Distributions forms, meaning that appropriation and fund distribution maintenance screens in the Federal Financials module read and write directly against this table.

From a dimensional modeling perspective, the mined structure suggests a standalone Data Vault classification. Because the table carries its own surrogate primary key (TRANSACTION_ID) and no foreign key relationships were detected in the documented FK structure, it behaves neither as a pure hub nor as a dependent satellite in a strict Data Vault sense. In practice it is a wide, denormalized transaction fact table containing both descriptive attributes and the full budgeting segment string, which is characteristic of a reporting-oriented fact object rather than a normalized hub-link-satellite construct. The classification should therefore be treated as a modeling suggestion rather than a prescriptive design.

Key Information Stored

The table contains 76 documented columns in the 12.2.2 physical schema. The most operationally significant columns are:

PUBLIC_LAW_CODE, MAIN_ACCOUNT, DEPT_ID, ADVANCE_TYPE, TRANSFER_DESCRIPTION, SOURCE, GROUP_ID, and EVENT_ID provide further federal-specific context.

Common Use Cases and Queries

Typical usage centers on reporting budget execution activity by appropriation, fund distribution, ledger, and period. A common query aggregates amounts by document and status:

  • Extract all posted budget execution transactions for a given ledger and fiscal quarter: SELECT TRANSACTION_ID, DOC_ID, GL_DATE, AMOUNT, INCREASE_DECREASE_FLAG FROM FV.FV_BE_TRX_DTLS WHERE SET_OF_BOOKS_ID = :p_sob AND QUARTER_NUM = :p_qtr AND TRANSACTION_STATUS = 'POSTED';
  • Reconcile transactions that have not yet transferred to the general ledger: SELECT DOC_ID, COUNT(*), SUM(AMOUNT) FROM FV.FV_BE_TRX_DTLS WHERE GL_TRANSFER_FLAG = 'N' GROUP BY DOC_ID;
  • Report by accounting flexfield segment, joining the SEGMENTn columns to the appropriate value set descriptions.
  • Audit approvals and corrections using APPROVED_BY_USER_ID, APPROVAL_DATE, and CORRECTED_FLAG.
  • Trace posting activity via POSTING_PROCESS_ID to correlate with GL journal import runs.

Because the table underlies the Define Appropriations and Define Fund Distributions forms, any form-level query or personalization referencing those screens ultimately reads this table.

Related Objects

The documented FK structure classifies the table as standalone, so no enforced foreign key constraints were mined. The following objects are the most significant functional counterparts commonly joined or referenced in Federal Financials reporting:

  • FV_BE_TRX_HEADERS — the transaction header table keyed by the parent document, joined on DOC_ID.
  • FV_BE_TRX_TYPES and related transaction type setup tables, joined on TRANSACTION_TYPE_ID.
  • GL_SETS_OF_BOOKS — joined on SET_OF_BOOKS_ID to resolve ledger names.
  • GL_JE_BATCHES / GL_JE_HEADERS — the general ledger destination rows created once POSTING_PROCESS_ID and GL_TRANSFER_FLAG are set.
  • FND_USER — joined on APPROVED_BY_USER_ID, CREATED_BY, and LAST_UPDATED_BY for audit resolution.
  • FND_FLEX_VALUES and FND_FLEX_VALUES_TL — used to translate the SEGMENT1–SEGMENT30 values into descriptive account names.
  • FV_BE_TRX_DTLS is itself exposed through the Define Appropriations and Define Fund Distributions form blocks, which act as the primary user-facing interface over the detail records.

Given the absence of documented FK constraints, all joins with these objects should be performed explicitly on the logical key columns (DOC_ID, TRANSACTION_TYPE_ID, SET_OF_BOOKS_ID, and user ID columns) rather than relying on database-enforced referential integrity.