Search Results fv_be_trx_hdrs_u2

Overview

The FV.FV_BE_TRX_HDRS table stores document header information for all budget execution transactions within the Oracle E-Business Suite Public Sector / Federal Financials (formerly ETRM) module. It functions as the base table for the header block of the Define Appropriations and Define Fund Distributions forms, and it is the primary repository for appropriation, fund distribution, and related budget execution documents. The object resides in the FV schema, carries the FND Design Data identifier FV.FV_BE_TRX_HDRS, and is confirmed VALID in the ETRM 12.2.2 schema, with the same structure available in 12.1.1.

In Data Vault terms, the mined classification suggests a satellite-leaning object: the table's core identity is the surrogate key DOC_ID, while descriptive attributes such as document status, totals, transaction dates, and document numbers are mutable over time. Modelling this as a satellite attached to a transaction hub is a reasonable heuristic, given the presence of REVISION_NUM and INTERNAL_REVISION_NUM, which track document version evolution rather than introducing new business entities. Physical storage uses the APPS_TS_TX_DATA tablespace with PCT Free 10, while indexes sit in APPS_TS_TX_IDX.

Key Information Stored

The table documents 70 columns. The most significant are summarized below.

• DOC_ID (NUMBER(15), mandatory) — the surrogate primary key (FV_BE_TRX_HDRS_PK1) and unique index FV_BE_TRX_HDRS_U2. This is the join key to child lines and distributions.
• DOC_NUMBER (VARCHAR2(30)) — the human-readable document number.
• BUDGET_LEVEL_ID (NUMBER(15)) — foreign key to FV_BUDGET_LEVELS, indicating the appropriations/fund-distribution level.
• SET_OF_BOOKS_ID and SOURCE — together with DOC_NUMBER and BUDGET_LEVEL_ID, form the business-key candidate FV_BE_TRX_HDRS_U1.
• DOC_STATUS (VARCHAR2(25)) — workflow status of the document.
• DOC_TOTAL, DISTRIBUTION_AMOUNT — monetary amounts for the header and its distribution.
• TRANSACTION_DATE — the accounting date of the transaction.
• REVISION_NUM, INTERNAL_REVISION_NUM — public and internal revision tracking.
• BUDGETING_SEGMENTS (VARCHAR2(2000)) and SEGMENT1–SEGMENT30 — the accounting flexfield concatenation and individual key flexfield segments.
• PARENT_DOC_ID, APPROVAL_ID, APPROVED_BY_USER_ID, BU_GROUP_ID — lineage, approval, and budgeting-user-group references.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns, plus a full ATTRIBUTE1–ATTRIBUTE15 DFF set.

Common Use Cases and Queries

Typical reporting queries resolve the latest revision of a document, or roll up distributions against budgets. A common pattern retrieves current appropriation headers for a set of books:

SELECT h.doc_id, h.doc_number, h.doc_status, h.doc_total, h.transaction_date
  FROM fv.fv_be_trx_hdrs h
 WHERE h.set_of_books_id = :sob_id
   AND h.budget_level_id = :level_id
   AND h.source = :source
   AND h.transaction_date BETWEEN :dt_from AND :dt_to
 ORDER BY h.transaction_date DESC;

Joining to the budget level for descriptive labels is common:

SELECT h.doc_number, b.budget_level_name, h.doc_total
  FROM fv.fv_be_trx_hdrs h, fv.fv_budget_levels b
 WHERE h.budget_level_id = b.budget_level_id
   AND h.doc_status = 'APPROVED';

Because DOC_ID is the surrogate key, sub-queries against line-level distributions can retrieve detail:

SELECT l.line_number, l.amount
  FROM fv.fv_be_trx_lines l
 WHERE l.doc_id = :doc_id;

Auditing scenarios examine revision history by scanning REVISION_NUM and OLD_DOC_NUMBER, while month-end reconciliation compares DOC_TOTAL against summed distribution lines grouped by DOC_NUMBER and SET_OF_BOOKS_ID.

Related Objects

The following objects are the most significant dependencies and references for this table:

• FV_BUDGET_LEVELS — joined on BUDGET_LEVEL_ID; defines the appropriations and fund-distribution level.
• FV_BE_TRX_LINES — child lines keyed to DOC_ID, providing the distribution detail.
• FV_BUDGET_USER_HDR — referenced by BU_GROUP_ID, identifying the budgeting user group.
• JTF_UM_APPROVALS_B — referenced by APPROVAL_ID, providing approval workflow context.
• FV_BE_TRX_HDRS self-join on PARENT_DOC_ID — resolves document lineage for revisions and hierarchy.
• GL_SETS_OF_BOOKS — joined on SET_OF_BOOKS_ID for accounting ledger context.
• HR_ALL_ORGANIZATION_UNITS / FND_USER — joined on APPROVED_BY_USER_ID for approver identity.

Downstream views and the Define Appropriations / Define Fund Distributions forms consume this table directly, with DOC_ID, BUDGET_LEVEL_ID, DOC_NUMBER, SET_OF_BOOKS_ID, and SOURCE forming the core access paths via FV_BE_TRX_HDRS_U1 and FV_BE_TRX_HDRS_U2.