Search Results trx_date_fk




Overview
Explore in AI Chat

Overview

OPI_EDW_OPI_RES_UTIL_FCV is a source view owned by the APPS schema in Oracle E-Business Suite, defined under the OPI (Operations Intelligence) product family. It is documented in ETRM for both 12.1.1 and 12.2.2 as VALID. Its stated purpose is to serve as the source view for extracting job-resource data from the Discrete Manufacturing source tables, feeding the Operations Intelligence / Enterprise Data Warehouse (EDW) star-schema model.

The object is a flat, denormalized "FCV" (fact/collection view) rather than a user-facing inquiry screen. It presents resource utilization records in the surrogate-key form expected by the EDW loaders, and it is consumed downstream by OPI fact loads and by customer-built BI extract programs. In the context of the search term uom_fk, this view is significant because it exposes UOM as a foreign key column rather than a descriptive code, so the raw column value must be joined to a UOM dimension to obtain any human-readable meaning.

Underlying Base Objects

The documented view text defines the view over two base objects:

  • OPI_EDW_RES_UTIL_PUSH_LOG (aliased LOG) — the primary staging/push log holding discrete manufacturing resource utilization data such as resource code, department code, organization code, transaction date, SOB identifier, UOM, actual resource usage and available resource hours. This table supplies nearly every meaningful column in the view.
  • EDW_LOCAL_INSTANCE (aliased INST) — the EDW instance registry, contributing INSTANCE_CODE used to stamp the instance key and to build composite surrogate keys.

ETRM documents no further referenced base objects, and the view performs no joins beyond the implicit cross join of these two sources; the concatenation logic constructs the EDW surrogate identifiers inline. The TRX_DATE_FK value is derived by calling EDW_TIME_PKG.CAL_DAY_FK with the transaction date, SOB identifier and instance code. The view carries Oracle's Proprietary, Confidential Information marking, and none of the columns are sourced from a documented UOM master table — UOM_FK is passed through verbatim from LOG.UOM.

Key Columns

  • RES_UTIL_PK — surrogate key built as RES_UTIL_PK || '-' || INSTANCE_CODE || '-OPI'.
  • LOCATOR_FK — inventory locator surrogate built from organization code, instance code and the literal -PLNT.
  • RES_FK — resource foreign key composed of resource code, department code, organization code and instance code.
  • TRX_DATE_FK — date dimension foreign key produced by EDW_TIME_PKG.CAL_DAY_FK.
  • UOM_FK — the unit-of-measure foreign key. It is mapped directly from LOG.UOM and therefore stores the UOM code or identifier used by the source transaction; it is a dimension pointer, not a descriptive string, and must be resolved against the UOM dimension to render quantities in context.
  • INSTANCE_FK — the EDW instance identifier from EDW_LOCAL_INSTANCE.
  • ACT_RES_USAGE / AVAIL_RES — actual resource usage and available resource capacity for the transaction.
  • DEPARTMENT, TRX_DATE — descriptive department code and the raw transaction date.
  • USER_FK1–5, USER_MEASURE1–5 — placeholders populated with the literal 'NA_EDW' and NULL respectively in the documented definition.
  • USER_ATTRIBUTE1–15 and LAST_UPDATE_DATE — present for EDW load compatibility; the documented view text returns NULL for these.

Common Use Cases and Queries

Typical usage is within OPI extraction and reconciliation work. A frequent requirement is verifying that UOM_FK values resolve correctly against the UOM dimension before fact loading:

  • UOM validation — identifying transactions whose UOM foreign key has no matching dimension row, which indicates a missing or stale UOM member.
  • Resource utilization trending — aggregating ACT_RES_USAGE versus AVAIL_RES by RES_FK, DEPARTMENT and TRX_DATE_FK.
  • Load reconciliation — comparing view row counts and key concatenations against the OPI fact tables.

Example query resolving the UOM foreign key:

SELECT r.RES_UTIL_PK, r.RES_FK, r.UOM_FK, r.DEPARTMENT, r.TRX_DATE, r.ACT_RES_USAGE, r.AVAIL_RES FROM APPS.OPI_EDW_OPI_RES_UTIL_FCV r WHERE r.UOM_FK IS NULL OR NOT EXISTS (SELECT 1 FROM <UOM_DIM> u WHERE u.UOM_FK = r.UOM_FK);

Example aggregation by resource and period:

SELECT r.RES_FK, r.UOM_FK, r.TRX_DATE_FK, SUM(r.ACT_RES_USAGE) ACT_USAGE, SUM(r.AVAIL_RES) AVAIL FROM APPS.OPI_EDW_OPI_RES_UTIL_FCV r GROUP BY r.RES_FK, r.UOM_FK, r.TRX_DATE_FK;

Because the view returns NULL for the user-defined attribute columns and literal 'NA_EDW' for the user foreign keys, queries should not depend on those columns for business logic; the operative measures are ACT_RES_USAGE and AVAIL_RES, qualified by the UOM_FK, RES_FK, LOCATOR_FK and TRX_DATE_FK keys.