Results for “fv_invoice_statuses_all”

42 results




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

Overview

FV_INVOICE_STATUSES_ALL is a Federal Financials (FV) module table in Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It stores invoice status information consumed specifically by the Report on Receivables Due From Public and the Schedule 9 federal reporting process. The table operates within the FV schema and is classified as VALID in the ETRM repository. Its scope is narrow by design: rather than functioning as a general-purpose transactional ledger, it records the status context attached to customer transactions so that statutory receivables reporting can resolve the correct reporting treatment.

The documented physical schema lists 29 columns. The primary key constraint SYS_C0026133 is defined over the composite of CUSTOMER_TRX_ID and STATUS, confirming that a single transaction may carry multiple status rows over time or across reporting contexts. Under the heuristic Data Vault classification mined from the foreign key structure, the table is treated as standalone. In modeling terms, this suggests the object is best represented as a satellite-like structure keyed by a business composite, with no documented parent hub or link dependency asserted through the ETRM relationship data. This classification is a suggestion derived from the observed key structure rather than a declared Data Vault design.

Key Information Stored

Among the 29 documented columns, the following are the most significant for functional and reporting purposes:

  • CUSTOMER_TRX_ID — the transaction identifier linking the status record to the underlying receivable. It participates in the composite primary key.
  • STATUS — the invoice status value. Together with CUSTOMER_TRX_ID it forms the composite primary key SYS_C0026133 and is the primary business-key candidate.
  • INVOICE_STATUS_ID — a surrogate identifier for the status record, distinct from the composite business key.
  • SET_OF_BOOKS_ID — the ledger context in which the status applies, enabling multi-ledger reporting separation.
  • ORG_ID — the operating unit context, supporting multi-org data access and reporting filters.
  • START_DATE and END_DATE — the effective date range for which the status is active, supporting temporal reporting as of a given Schedule 9 period.
  • COMMENTS — free-text annotation for the status record.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns describing record provenance.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS Flexfield descriptive columns, available for federal-specific extensions.

The composite primary key distinguishes the business key (CUSTOMER_TRX_ID, STATUS) from the surrogate INVOICE_STATUS_ID. No additional unique indexes are documented beyond SYS_C0026133.

Common Use Cases and Queries

The principal use case is the generation of the Report on Receivables Due From Public and Schedule 9. Reporting logic joins this table to receivable transactions to determine the correct status classification for each reported line. A representative query pattern filters by operating unit and ledger:

  • Retrieve all current statuses for a transaction: SELECT status, start_date, end_date FROM fv_invoice_statuses_all WHERE customer_trx_id = :trx_id ORDER BY start_date DESC;
  • Period reporting: restrict rows where START_DATE is on or before the period end and END_DATE is null or after the period end.
  • Multi-org reporting: constrain by ORG_ID and SET_OF_BOOKS_ID to align with the reporting entity.
  • Audit review: inspect CREATED_BY, CREATION_DATE, LAST_UPDATED_BY and LAST_UPDATE_DATE to trace status maintenance.

Because the table is keyed by transaction and status, care is required to select the effective row for a given reporting date rather than returning all historical rows.

Related Objects

The ETRM metadata documents the table as standalone in the mined relationship structure, so related objects are inferred from the CUSTOMER_TRX_ID and STATUS semantics rather than from declared foreign keys:

  • RA_CUSTOMER_TRX_ALL — the core receivables transaction table, joined on CUSTOMER_TRX_ID.
  • RA_CUSTOMER_TRX_LINES_ALL — transaction lines linked through CUSTOMER_TRX_ID for line-level reporting.
  • AR_PAYMENT_SCHEDULES_ALL — payment schedule and outstanding balance data, joined on CUSTOMER_TRX_ID.
  • FV_INVOICE_STATUSES — where a secured or non-_ALL variant exists in the FV schema, sharing the same column structure.
  • FV Report on Receivables / Schedule 9 concurrent programs — the reporting processes that consume this table directly.
  • GL_SETS_OF_BOOKS — joined on SET_OF_BOOKS_ID to resolve ledger names for reporting.
  • HR_OPERATING_UNITS — joined on ORG_ID to resolve operating unit context.

These relationships are functional and reporting-oriented; implementers should confirm the exact join semantics against the deployed 12.1.1 or 12.2.2 instance before relying on them in production SQL.