Search Results customer_fk

Overview

FII_PA_REVENUE_F_FCV is a database view owned by the APPS schema in Oracle E-Business Suite, classified under the FII (Financial Intelligence) product family. In the ETRM (E-Business Suite Technical Reference Manual) documentation it is described as the "EDW Project Revenue fact source view." This designation identifies the object as a source-side component of the Enterprise Data Warehouse (EDW) extraction layer that feeds the Oracle Financial Intelligence and Business Intelligence (BI) reporting stack, particularly Oracle Business Intelligence Applications (OBIA) and its Projects analytics modules.

Rather than storing data itself, the view presents a denormalized, analytics-ready projection of project revenue transactions originating in Oracle Projects (PA). It exposes surrogate keys, foreign keys to conformed dimensions, and generalized GL accounting flexfield references in a structure intended for consumption by ETL (Extract, Transform, Load) processes. The naming convention reflects this purpose: the "_F_" segment denotes a fact-style view (fact source), "_FCV" indicates a "fact collection view" used by the Financial Intelligence Collection (FIC) or DAC-driven extraction programs, and the "PA_REVENUE" portion identifies the subject area as project revenue.

The view is marked with a status of VALID and resides in the APPS schema, meaning it is compiled against the APPS synonym layer and is accessible to any EBS responsibility with appropriate grants. It exists in both Oracle EBS 12.1.1 and 12.2.2, with the FII/EDW integration being substantially the same across both releases; differences are largely confined to underlying table definitions rather than the view's own structure.

Underlying Base Objects

The ETRM metadata for this object documents no explicit referenced base objects, which is a common characteristic of FII fact source views. In practice, FII_PA_REVENUE_F_FCV is defined over the interface and staging tables maintained by the Financial Intelligence product, principally FII_PA_REVENUE_F (the persistent fact staging table) and its associated dimension-lookup structures. The view applies decoding and reformatting logic to these tables so that downstream extraction receives a consistent, EDW-conformant shape.

The view selects from one primary source and derives a full set of GL accounting flexfield foreign keys (GL_ACCT1_FK through at least GL_ACCT9_FK) by parsing a packed GL_SEG column. It does not itself join to PA_PROJECTS, GL_CODE_COMBINATIONS, or other operational tables; those joins occur upstream when the fact table is populated. Consequently, the view is best understood as a transformation and publication layer between the FII fact table and the EDW collection process.

Key Columns

• SEQ_ID — Sequential identifier used during extraction ordering.
• VIEW_TYPE — Discriminator marking the row's origin or extraction category.
• REVENUE_PK / REVENUE_G / REVENUE_B — Primary key, grouping, and business key columns identifying the revenue fact record.
• INSTANCE_FK — Foreign key to the EBS instance dimension, supporting multi-instance warehouse loads.
• SET_OF_BOOKS_FK — Foreign key to the ledger/set of books dimension.
• PROJECT_FK / PROJECT_ORG_FK — Foreign keys to the project and project organization dimensions.
• CUSTOMER_FK — Foreign key to the customer dimension.
• CURRENCY_GL_FK — Foreign key to the global currency dimension.
• GL_DATE_FK / PA_DATE_FK / TRANSACTION_DATE_FK — Foreign keys to the GL accounting date, PA date, and transaction date dimensions.
• GL_ACCT1_FK through GL_ACCT9_FK — Nine decoded accounting flexfield segment foreign keys. Each is derived by taking a 25-character slice of the packed GL_SEG string, trimming trailing blanks, and concatenating a common suffix from offset 251. A value of 'NA_EDW' is preserved as a literal sentinel, indicating a segment not applicable to the EDW model.

Common Use Cases and Queries

The primary use case is ETL extraction of project revenue facts into the EDW. Typical queries select the surrogate and foreign keys along with the decoded accounting segments for a bounded date range, driven by the DAC execution plan.

Example — retrieve project revenue facts for a specific set of books and accounting period:

SELECT seq_id, revenue_pk, set_of_books_fk, project_fk,
       customer_fk, gl_date_fk,
       gl_acct1_fk, gl_acct2_fk, gl_acct3_fk
FROM   apps.fii_pa_revenue_f_fcv
WHERE  set_of_books_fk = :p_sob
AND    gl_date_fk BETWEEN :p_start AND :p_end;

Example — count facts by project to validate load completeness:

SELECT project_fk, COUNT(*) fact_count
FROM   apps.fii_pa_revenue_f_fcv
GROUP  BY project_fk
ORDER  BY fact_count DESC;

Example — inspect decoded accounting segments, filtering out the 'NA_EDW' sentinel:

SELECT revenue_pk, gl_acct1_fk, gl_acct2_fk
FROM   apps.fii_pa_revenue_f_fcv
WHERE  gl_acct1_fk <> 'NA_EDW';

Because the view is a read-only projection, it is safe to query directly; any tuning should focus on the underlying fact table's indexes and the DAC extraction parameters rather than on the view definition itself.