Search Results gl_date_fk




Overview
Explore in AI Chat

Overview

APPS.FII_PA_REVENUE_F_FCV is a foreign-key (FK) collection view within the Oracle E-Business Suite 12.1.1 and 12.2.2 ETRM (Enterprise Transaction Repository Model) layer, designed primarily to support the Oracle Business Intelligence Applications (OBIA/BI-Apps) and Enterprise Data Warehouse (EDW) extracts. The suffix "_FCV" denotes a Foreign-Key Collection View, indicating that the view's purpose is to expose a normalized set of surrogate key columns (foreign keys) that link transactional revenue facts from Oracle Projects to conformed dimensions such as date, project, customer, currency, and accounting flexfield dimensions. Rather than presenting business-level attributed rows, the view emits key-bearing records that ETL mappings use to assemble star-schema fact tables. In this role it functions as a staging or interface object between the EBS transactional schema (specifically Projects revenue data) and the downstream data warehouse, ensuring consistent key generation across source and target systems.

Underlying Base Objects

The documented ETRM metadata for this view lists no explicit referenced base objects. Nevertheless, the view text reveals that it is constructed over a source revenue table (aliased conceptually as PA revenue data) and, via the GL_SEG column, an accounting flexfield segment source. The view performs heavy string manipulation (substrb, rtrim, decode) across GL_SEG to split a concatenated accounting flexfield into ten discrete GL account key columns. When GL_SEG equals the sentinel value 'NA_EDW', the decode expressions short-circuit and return 'NA_EDW' directly, preserving the EDW convention for not-available dimension members. Otherwise segments are trimmed and concatenated with a trailing segment fragment (substrb(GL_SEG,251)), indicating a 250-character concatenated GL key with a trailing qualifier. Because the FK metadata documents no base objects, integrators should treat the view as a fixed interface contract and avoid assuming the precise source table names without inspecting the underlying PA revenue staging object in the target instance.

Key Columns

  • SEQ_ID / VIEW_TYPE / REVENUE_PK / REVENUE_G / REVENUE_B: Row identity and grain columns; REVENUE_PK is the primary key of the revenue fact, with G and B variants typically denoting grouping and base attributes used during ETL reconciliation.
  • INSTANCE_FK: Identifies the source EBS instance, essential in multi-instance or consolidated warehouse deployments.
  • SET_OF_BOOKS_FK / SET_OF_BOOKS_ID: Links the revenue row to the ledger or set of books dimension.
  • PROJECT_FK / PROJECT_ORG_FK / CUSTOMER_FK: Conformed dimension keys for project, project organization, and customer.
  • CURRENCY_GL_FK / GLOBAL_CURRENCY_RATE: Currency dimension key and the associated global (corporate) currency rate used for translation of amounts in reporting currency.
  • GL_DATE_FK / PA_DATE_FK / TRANSACTION_DATE_FK: Date dimension foreign keys. TRANSACTION_DATE_FK is the key most relevant to the user's search term; it points to the transaction date of the revenue event and is used to align revenue facts to the time dimension. GL_DATE_FK and PA_DATE_FK provide alternative date roles (accounting date and Projects date) for role-playing date dimension joins.
  • GL_ACCT1_FK … GL_ACCT10_FK: Ten accounting flexfield segment keys derived from GL_SEG, each eligible to join to the corresponding accounting flexfield dimension.
  • LAST_UPDATE_DATE: Delta-extraction control column for incremental ETL.
  • USER_MEASURE1 / USER_MEASURE2: Placeholder numeric measures returned as NULL, reserved for extensibility.

Common Use Cases and Queries

The principal use case is populating or validating warehouse revenue facts and their foreign-key relationships, particularly date-role resolution. A representative query joining the transaction date role is:

  • SELECT REVENUE_PK, TRANSACTION_DATE_FK, PROJECT_FK, CUSTOMER_FK, SET_OF_BOOKS_FK, GLOBAL_CURRENCY_RATE FROM APPS.FII_PA_REVENUE_F_FCV WHERE LAST_UPDATE_DATE >= :last_extract; — incremental extraction filtered on the delta column.
  • SELECT TRANSACTION_DATE_FK, COUNT(*) FROM APPS.FII_PA_REVENUE_F_FCV GROUP BY TRANSACTION_DATE_FK; — distribution/coverage check verifying that every revenue row resolves to a valid time dimension member.
  • SELECT * FROM APPS.FII_PA_REVENUE_F_FCV WHERE TRANSACTION_DATE_FK = 'NA_EDW' OR SET_OF_BOOKS_FK = 'NA_EDW'; — data-quality audit identifying unresolved foreign keys before dimension joins.
  • Joining GL_ACCT1_FK through GL_ACCT10_FK to the accounting flexfield dimension to reconstitute account combinations for revenue reporting.

Because the view emits only keys, downstream queries must join to the appropriate conformed dimensions (date, project, customer, currency, GL account) to produce business-meaningful output. Databases at 12.1.1 and 12.2.2 share this interface, and the presence of TRANSACTION_DATE_FK, GL_DATE_FK, and PA_DATE_FK confirms the multiple-date-role pattern typical of OBIA financial and Projects fact sources.