Results for “edw_local_system_parameters”

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

Overview

FIIBV_PROJ_CATEG7_LCV is a Financial Intelligence (FII) base view owned by the APPS schema in Oracle E-Business Suite. It forms part of the FII Business Intelligence/Enterprise Data Warehouse (EDW) layer, specifically supplying the Project dimension's "Category 7" attribute. In Oracle Projects terminology, Category 7 is one of the configurable class categories (CLASS_CATEG7) held in the PA_CLASS_CATEGORIES table, and is commonly mapped to a descriptive classification such as project type, service line, or another user-defined grouping used for analytical reporting. The view surfaces that category as a denormalized dimension key, enabling FII fact tables and ETL processes to join project-level facts to a single, standardized Category 7 label.

Underlying Base Objects

The view is defined over three documented base objects:

• PA_CLASS_CATEGORIES (alias CL) — the Projects class category setup table, providing CLASS_CATEGORY and LAST_UPDATE_DATE.
• EDW_LOCAL_SYSTEM_PARAMETERS (alias CT) — the EDW parameter table, whose CLASS_CATEG7 column establishes which class category is treated as Category 7.
• EDW_LOCAL_INSTANCE (alias ELI) — the EDW local instance table, supplying INSTANCE_CODE for multi-instance identification.

The view joins PA_CLASS_CATEGORIES to EDW_LOCAL_SYSTEM_PARAMETERS on CLASS_CATEGORY = CT.CLASS_CATEG7, and cross-references EDW_LOCAL_INSTANCE to append an instance code. The result is a single-row-per-category mapping defined by the EDW instance configuration rather than by project data itself, making this view a configuration-driven dimension source.

Key Columns

• PROJ_CATEG7_PK — the derived primary/surrogate key, built as CLASS_CATEGORY concatenated with the instance code (CLASS_CATEGORY || '-' || ELI.INSTANCE_CODE).
• NAME — returns CLASS_CATEGORY; represents the Category 7 name.
• PROJECT_CATEGORY7 — returns CLASS_CATEGORY, the category value used as the project dimension attribute.
• ALL_FK — a constant 'ALL' value providing a fixed foreign key for an "ALL" member used in aggregate/rollup reporting.
• INSTANCE — ELI.INSTANCE_CODE, identifying the source EDW instance.
• LAST_UPDATE_DATE — CL.LAST_UPDATE_DATE, used for incremental ETL change detection.
• _DF:PA:PA_CLASS_CATEGORIES_DESC_FLEX:CL — a descriptive flexfield utility column referencing the PA_CLASS_CATEGORIES descriptive flexfield, and "_DF:PCT:_EDW" cites the descriptor for the EDW FII context.

Common Use Cases and Queries

Typical uses involve building or validating the Project Category 7 dimension for Financial Intelligence dashboards, reconciling EDW dimension values with PA_CLASS_CATEGORIES setup, and verifying which category is currently configured as CLASS_CATEG7.

Sample query listing Category 7 members:

• SELECT PROJ_CATEG7_PK, NAME, PROJECT_CATEGORY7, INSTANCE, LAST_UPDATE_DATE FROM APPS.FIIBV_PROJ_CATEG7_LCV;
• Incremental load filter: SELECT * FROM APPS.FIIBV_PROJ_CATEG7_LCV WHERE LAST_UPDATE_DATE >= :last_run_date;
• Instance-specific lookup: SELECT PROJECT_CATEGORY7 FROM APPS.FIIBV_PROJ_CATEG7_LCV WHERE INSTANCE = :instance_code;

Because the view derives from EDW parameter configuration, queries return only the category designated by EDW_LOCAL_SYSTEM_PARAMETERS.CLASS_CATEG7 for the active instance, so it should be treated as a configured lookup rather than a full enumeration of all class categories. Joins to project facts are typically on PROJ_CATEG7_PK or PROJECT_CATEGORY7, or through ALL_FK for the aggregate member.