Results for “gl_date_fk_key”

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

Overview

FII_PA_REVENUE_FSTG is a table owned by the FII schema within Oracle E-Business Suite, positioned as a staging object for the Project Revenue fact in the Financial Intelligence (FII) product family. In the ETRM 12.1.1 documented schema, the table consists of 75 columns and is classified as VALID. Its stated purpose is to hold project revenue data in a pre-fact, staging form before it is loaded into the corresponding star-schema fact object used by Financial Intelligence analytics and reporting. The presence of currency, date, project, customer, and chart-of-accounts foreign keys, together with generic measure and attribute columns, confirms its role as a denormalized landing area aligned to the dimensional model rather than as a transactional ledger table.

The documented FK metadata identifies a single reference from ROW_ID to CS_SYSTEMS_ALL_B_TEMP, which supports run-time or instance context handling during staging and load processing. Based on the mined foreign-key structure, the heuristic Data Vault classification for this object is standalone; it does not decompose cleanly into hub, link, or satellite roles. This classification should be treated as a modeling suggestion rather than a definitive architectural judgment, since the table is designed for staging and fact pre-load rather than for Data Vault integration.

Key Information Stored

The table combines surrogate key columns, business-reference keys, dimensional foreign keys, and generic measure/attribute slots. The most significant columns are:

• REVENUE_PK — the surrogate primary key for the staged revenue row; the primary identifier within the staging context.
• INSTANCE_FK / INSTANCE_FK_KEY — instance-level reference, typically tied to the FII load/run instance.
• SET_OF_BOOKS_FK / SET_OF_BOOKS_FK_KEY — set of books context, determining the ledger perspective for revenue.
• PROJECT_FK / PROJECT_FK_KEY — the project dimension reference.
• PROJECT_ORG_FK / PROJECT_ORG_FK_KEY — the project organization or operating unit dimension.
• CUSTOMER_FK / CUSTOMER_FK_KEY — customer dimension reference associated with the revenue.
• CURRENCY_GL_FK / CURRENCY_GL_FK_KEY — GL currency dimension reference.
• GL_DATE_FK and PA_DATE_FK — GL accounting date and Project Accounting date dimensions.
• TRANSACTION_DATE_FK — transaction date dimension for the revenue event.
• GL_ACCT1_FK through GL_ACCT10_FK (with corresponding _KEY columns) — up to ten account-segment dimension references supporting flexible chart-of-accounts mapping.
• REVENUE_G and REVENUE_B — the primary revenue measures (generic/gross and base amounts).
• USER_MEASURE1–5 — configurable numeric measure slots for user-defined metrics.
• USER_ATTRIBUTE1–10 and USER_FK1–5 (with _KEY counterparts) — extensibility columns for user-defined attributes and foreign keys.

Because the surrogate key is REVENUE_PK, the business-key candidates are represented by the paired FK and FK_KEY columns, where the _KEY columns typically hold the natural/dimensional identifiers used for joins and the FK columns hold the related surrogate values. Control columns such as OPERATION_CODE, COLLECTION_STATUS, ERROR_CODE, and REQUEST_ID indicate the ETL/integration state of each staged row.

Common Use Cases and Queries

FII_PA_REVENUE_FSTG is most commonly used during Financial Intelligence data loads and diagnostics. Typical patterns include:

• Load validation: Filter on COLLECTION_STATUS or ERROR_CODE to identify rows that failed staging or transformation. For example, SELECT * FROM FII.FII_PA_REVENUE_FSTG WHERE COLLECTION_STATUS = 'ERROR'.
• Project revenue reconciliation: Join PROJECT_FK_KEY and SET_OF_BOOKS_FK_KEY to project and ledger sources to reconcile staged REVENUE_G against source revenue figures.
• Trend and dimension reporting: Aggregate REVENUE_G by GL_DATE_FK_KEY, PROJECT_FK_KEY, or CUSTOMER_FK_KEY for provisional reporting before fact promotion.
• Run monitoring: Group by REQUEST_ID and INSTANCE_FK_KEY to measure throughput and detect incomplete load cycles.

Because the table is a staging object, it is not intended for sustained analytical querying; production reporting should reference the promoted fact table.

Related Objects

The documented FK relationship links ROW_ID to CS_SYSTEMS_ALL_B_TEMP, which serves as the system/instance context source. Other consequential related objects include:

• FII_PA_REVENUE_F (or the corresponding promoted fact object) — the target star-schema fact populated from this staging table.
• CS_SYSTEMS_ALL_B_TEMP — referenced via ROW_ID; provides system registration context.
• FII dimensional tables for project, customer, set of books, currency, date, and GL account, referenced through the *_FK and *_KEY column pairs.
• FII ETL/integration concurrent programs that manage REQUEST_ID, COLLECTION_STATUS, and OPERATION_CODE lifecycle values.

These relationships define staging lineage and should be preserved when diagnosing load behavior or reconciling project revenue between source, staging, and fact layers.