Results for “unspsc_fk_key”

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

Overview

POA_EDW_CSTM_MSR_F is a fact table owned by the POA schema within the Oracle E-Business Suite Purchasing Intelligence module (Purchasing Intelligence / POA). It is documented in ETRM as the "Custom Measure fact table" and is classified as VALID. The table stores user-defined scoring measures captured during supplier evaluations, extending the standard evaluation framework delivered by Oracle Advanced Procurement and the Oracle Procurement Data Warehouse.

From a dimensional modeling perspective, the mined relationship data classifies this object as standalone under the heuristic Data Vault taxonomy. Its only documented foreign key, EVALUATION_ID, points to AMW_EVALUATIONS_B; there are no dependent child tables identified. Because the table combines descriptors, scores, and comments under a single unique key, an analyst modeling this source may reasonably treat it as a satellite attached to the evaluation hub (AMW_EVALUATIONS_B) rather than a true hub or link, since its grain is one row per custom measure per evaluation rather than a shared business concept across domains.

Key Information Stored

The physical definition documented for ETRM 12.1.1 contains 48 columns. The table's surrogate primary key is CSTM_MSR_PK, and the unique index POA_EDW_CSTM_MSR_F_U1 (CSTM_MSR_PK) is documented as the sole business-key candidate on the table. Note that both CSTM_MSR_PK and CSTM_MSR_PK_KEY are present, the latter being the warehouse-surrogate variant typically used in ETL joins.

The most significant columns fall into three groups:

• Evaluation linkage: EVALUATION_ID, which joins back to AMW_EVALUATIONS_B and defines the parent evaluation to which each custom measure row belongs.
• Dimensional keys: CUSTOM_MEASURE_FK_KEY, CRITERIA_CODE_FK_KEY, SUPPLIER_SITE_FK_KEY, DUNS_FK_KEY, OPERATING_UNIT_FK_KEY, ITEM_FK_KEY, SIC_CODE_FK_KEY, UNSPSC_FK_KEY, EVAL_DATE_FK_KEY, INSTANCE_FK_KEY, and the five generic USER_FK1_KEY through USER_FK5_KEY slots. These resolve to the corresponding warehouse dimensions (supplier, item, commodity, organization, date, etc.).
• Scores, weighting, and free-form values: SCORE, MIN_SCORE, MAX_SCORE, WEIGHT, WEIGHTED_SCORE, plus USER_MEASURE1 through USER_MEASURE5 for user-defined numeric measures, and USER_ATTRIBUTE1 through USER_ATTRIBUTE15 for descriptive flex-style text. USER_NAME, SCORE_COMMENTS, and EVAL_COMMENTS provide attribution and narrative, while LAST_UPDATE_DATE and CREATION_DATE support incremental loads.

Common Use Cases and Queries

Purchasing Intelligence reporting typically traverses this table to enrich standard supplier scorecards with custom measures. A canonical pattern joins the fact to the evaluation hub:

SELECT f.EVALUATION_ID,
       f.SCORE,
       f.WEIGHT,
       f.WEIGHTED_SCORE,
       f.USER_MEASURE1,
       f.SCORE_COMMENTS
  FROM poa.poa_edw_cstm_msr_f f,
       amw.amw_evaluations_b e
 WHERE f.EVALUATION_ID = e.EVALUATION_ID
   AND f.CREATION_DATE >= :p_start_date;

Suppliers are analyzed by aggregating WEIGHTED_SCORE across DUNS_FK_KEY or SUPPLIER_SITE_FK_KEY, while commodity trends use SIC_CODE_FK_KEY or UNSPSC_FK_KEY. DBA routines routinely use CSTM_MSR_PK and the unique index for row-level reconciliation, and ETL processes key off LAST_UPDATE_DATE for delta extraction.

Related Objects

• AMW_EVALUATIONS_B — the parent evaluation table; joined on EVALUATION_ID. Documented FK source.
• AMW_EVALUATIONS_TL — translated names for evaluations, joined on EVALUATION_ID for multi-language reporting.
• POA_EDW_* dimension tables — the surrogate targets for the *_FK_KEY columns (supplier, commodity, item, organization, and date dimensions).
• POA_EDW_STD_MSR_F — the standard-measure counterpart fact; useful for union reporting across standard and custom measures.
• POA_EDW_*_MSR measure metadata tables — supply the CUSTOM_MEASURE_FK_KEY to measure-name resolution.
• FND_USER — referenced indirectly through USER_NAME for attribution of scoring activity.

Because the object resides in the POA warehouse schema rather than the transactional AMW schema, all access should be read-only and scheduled to avoid concurrent ETL refresh windows.