Search Results score_comments

Overview

POA_EDW_CUSTOM_MEASURE_FCV is a valid Oracle E-Business Suite view owned by the APPS schema and shipped with the Purchasing (PO) module. The name follows the Oracle Procurement and Spend Analytics naming convention: the POA_ prefix identifies the Purchasing Analytics product family, EDW denotes the Enterprise Data Warehouse staging layer, and the _FCV suffix indicates a "fact collection view" — a flattened, denormalized projection intended for extraction into the analytics warehouse rather than for transactional use. The view surfaces supplier evaluation scorecard data, exposing the line-level criteria scores recorded against supplier evaluations, along with foreign-key surrogates resolved through the ETRM dimension packages. Its central purpose is to present custom measure scores in a form suitable for loading into the EDW fact tables via the standard ETL process (for example, through the POA_EDW_... concurrent program family). The view text includes a reference to MAX_SCORE as an exposed column, which is directly relevant to the recurring search for that term.

Underlying Base Objects

The documented ETRM metadata lists no formal base objects for this view. However, the embedded view text reveals the underlying sources through aliases: PMS is the supplier evaluation score table (the source of EVALUATION_SCORE_ID, SCORE, WEIGHT, MIN_SCORE, MAX_SCORE, and CRITERIA_CODE); PME is the evaluation master table (supplying CUSTOM_MEASURE_CODE, SUPPLIER_ID, SUPPLIER_SITE_ID, OPER_UNIT_ID, ITEM_ID, and CATEGORY_ID); GPM is an accounting period/date source used for the ACCOUNTING_DATE; ELI supplies INSTANCE_CODE for the ETRM instance context; FSP contributes SET_OF_BOOKS_ID, INVENTORY_ORGANIZATION_ID, and audit columns; and FU provides USER_NAME. Surrogate keys are resolved at query time by calls to EDW_TIME_PKG, EDW_TRD_PARTNER_PKG, EDW_ORGANIZATION_PKG, and EDW_ITEMS_PKG.

Key Columns

• CSTM_MSR_PK — concatenated primary key built from evaluation score ID, accounting date, and instance code.
• SCORE, WEIGHT, MIN_SCORE, MAX_SCORE — the raw evaluation figures; MAX_SCORE defines the upper bound of the scoring range.
• WEIGHTED_SCORE — derived as (SCORE-MIN_SCORE)/(MAX_SCORE-MIN_SCORE)*WEIGHT, normalizing the score against the documented minimum and maximum.
• EVAL_DATE_FK, SUPPLIER_SITE_FK, OPERATING_UNIT_FK, ITEM_FK — EDW dimension surrogate keys resolved via the ETRM packages; a NULL SUPPLIER_SITE_ID falls back to a supplier-level key, and a NULL ITEM_ID pairs with a NULL CATEGORY_ID to yield the literal 'NA_EDW'.
• CUSTOM_MEASURE_FK, CRITERIA_CODE_FK, INSTANCE_FK — composite foreign keys for the measure, criteria, and instance dimensions.
• EVAL_COMMENTS, SCORE_COMMENTS, LAST_UPDATE_DATE, USER_NAME — descriptive and audit attributes.

Common Use Cases and Queries

The view is typically consumed by the analytics load process and by ad-hoc analysis of supplier scorecard performance. A representative query isolating the MAX_SCORE column might read:

SELECT cstm_msr_pk, score, min_score, max_score, weighted_score
FROM   apps.poa_edw_custom_measure_fcv
WHERE  max_score IS NOT NULL;

A second query joins the resolved surrogate keys for dimension-conformed reporting:

SELECT supplier_site_fk, operating_unit_fk, eval_date_fk,
       AVG(weighted_score) avg_weighted_score
FROM   apps.poa_edw_custom_measure_fcv
GROUP  BY supplier_site_fk, operating_unit_fk, eval_date_fk;