Search Results criteria_code_fk

Overview

APPS.POA_EDW_CUSTOM_MEASURE_FCV is a view owned by the APPS schema within the Oracle E-Business Suite Procurement application. The suffix FCV indicates that it is a "Flattened Collection View," a class of objects introduced in the Procurement and Oracle Purchasing Analytics family to present denormalized, warehouse-ready row sets for the Enterprise Data Warehouse (EDW) and Oracle Business Intelligence (OBIEE) reporting layers. The object is registered under FND Design Data as PO.POA_EDW_CUSTOM_MEASURE_FCV and is delivered in a VALID state in both EBS 12.1.1 and 12.2.2.

The view exposes supplier scorecard and custom-measure evaluation data — the scoring records produced by Oracle Procurement's supplier performance and custom measure functionality. Its role is to surface that transactional measure data in a form suitable for extract, transform, and load (ETL) into a reporting store, and for direct query by downstream analytics. Oracle explicitly marks the object "Oracle Internal Use Only" and does not support direct application data access except through standard Oracle Applications programs, so it should be treated as a read-only integration surface rather than an application-programming interface.

Underlying Base Objects

The ETRM metadata does not document explicit referenced base objects for this view. Based on the naming convention and column set, the view is defined over the supplier scorecard custom-measure base tables in the POA (Procurement Analytics) schema family, including the custom measure definition and the instance/evaluation fact tables that store individual measure scores per supplier, item, operating unit, and evaluation date. The view joins these tables and flattens them into a single denormalized row per measure evaluation, which is characteristic of the FCV pattern. Because the metadata lists no documented base tables, dependency should be confirmed at the site level by querying ALL_DEPENDENCIES and USER_DEPENDENCIES for the object before building custom extracts.

Key Columns

• SEQ_ID, VIEW_ID, EVALUATION_ID, CSTM_MSR_PK — surrogate identifiers for the row, view, evaluation instance, and the custom measure primary key.
• INSTANCE_FK, CUSTOM_MEASURE_FK, CRITERIA_CODE_FK — foreign keys linking the row to the scorecard instance, the measure definition, and the evaluation criterion.
• EVAL_DATE_FK — the evaluation date foreign key. Stored as VARCHAR2(4000), it carries the date dimension key that anchors each measurement to its evaluation period; this is the column most commonly used for time-based filtering and trend reporting.
• SUPPLIER_SITE_FK, OPERATING_UNIT_FK, ITEM_FK — VARCHAR2(4000) dimension keys for supplier site, operating unit, and item, likewise denormalized for warehouse use.
• DUNS_FK, UNSPSC_FK, SIC_CODE_FK — external classification keys for supplier and commodity standardization.
• SCORE, WEIGHT, WEIGHTED_SCORE, MIN_SCORE, MAX_SCORE — numeric measure values, including the computed weighted score and the defined scale bounds.
• LAST_UPDATE_DATE, USER_NAME — audit columns; LAST_UPDATE_DATE is the standard Who column indicating the last row update.
• EVAL_COMMENTS, SCORE_COMMENTS — free-text evaluator input.
• USER_ATTRIBUTE1–15, USER_FK1–5, USER_MEASURE1–5 — configurable extension attributes, foreign keys, and numeric measures.

Common Use Cases and Queries

Typical usage includes supplier performance dashboards, weighted-score trending by evaluation period, and ETL staging for OBIEE. Because the view exposes EVAL_DATE_FK as a character-formatted key, joins to a date dimension or conversions using TO_DATE are typical.

SELECT EVALUATION_ID,
       INSTANCE_FK,
       CUSTOM_MEASURE_FK,
       EVAL_DATE_FK,
       SUPPLIER_SITE_FK,
       SCORE,
       WEIGHTED_SCORE
FROM   APPS.POA_EDW_CUSTOM_MEASURE_FCV
WHERE  EVAL_DATE_FK = :p_eval_date
ORDER  BY CUSTOM_MEASURE_FK;

A second common pattern aggregates weighted scores per supplier for a date range:

SELECT SUPPLIER_SITE_FK,
       SUM(WEIGHTED_SCORE) AS total_weighted_score,
       AVG(SCORE)          AS avg_score
FROM   APPS.POA_EDW_CUSTOM_MEASURE_FCV
WHERE  EVAL_DATE_FK BETWEEN :from_date AND :to_date
GROUP  BY SUPPLIER_SITE_FK;

Since EVAL_DATE_FK is a VARCHAR2 key rather than a native DATE, range predicates depend on the key's lexical ordering; the appropriate date-string format (for example 'YYYY-MM-DD') must be supplied for correct results.