Search Results sts_lookup_fk_key

Overview

OPI.OPI_EDW_JOB_DETAIL_F is a fact table in the Oracle Process Manufacturing (OPM) operational data store / enterprise data warehouse schema, owned by the OPI schema. It stores detailed production information about jobs and batches executed across the manufacturing environment. The grain of the table is one row per job per end product, meaning that a single batch producing a main product and a co-product generates two distinct fact rows. This design supports multi-output yield and cost analysis, which is essential in process industries where co-products and by-products are common.

The table resides in the APPS_TS_SUMMARY tablespace, consistent with its role as a pre-aggregated analytical object rather than a high-transaction operational table. It is documented in ETRM 12.1.1 with 89 columns and remains valid in 12.2.2. The heuristic Data Vault classification mined from its foreign key structure is standalone, suggesting that within a Data Vault model this object would most naturally be treated as a standalone entity or a flattened fact rather than a formally linked hub-and-satellite construct. Modelers integrating this table into a warehouse should note that many of its foreign keys already behave like dimension references.

Key Information Stored

The table's primary key is enforced by the unique index OPI_EDW_JOB_DETAIL_F_U2 over (JOB_DETAIL_PK, JOB_DETAIL_PK_KEY), while a second unique index OPI_EDW_JOB_DETAIL_F_UKEY covers JOB_DETAIL_PK_KEY alone, making it the primary business-key candidate. The JOB_NO column carries the human-readable job identifier, and JOB_STATUS, MFG_MODE, ROUTING, and ROUTING_VERSION describe the manufacturing context.

Quantitative measures dominate the fact table. ACT_OUT_QTY, PLN_OUT_QTY, and STD_QTY capture actual, planned, and standard output quantities. Corresponding value columns include ACT_OUT_VAL_B, ACT_OUT_VAL_G, PLN_OUT_VAL_B, PLN_OUT_VAL_G, STD_VAL_B, and STD_VAL_G, where the _B suffix denotes Set of Books currency and _G denotes warehouse currency. Material input values are held in ACT_MTL_INP_VAL_B/_G and PLN_MTL_INP_VAL_B/_G, while by-product and scrap values appear in ACT_BPR_VAL_B/_G, ACT_SCR_VAL_B/_G, and their planned counterparts. Yield and quality indicators include FST_PASS_YLD, QC_TEST, QC_FAIL_QTY, REWORK_QTY, NO_ADJ, and NO_TIME_RESH. Time-based measures such as ACT_JOB_TIME, PLN_JOB_TIME, MOVE_TIME, QUEUE_TIME, RUN_TIME, SETUP_TIME, STD_TIME, and STND_HRS_EARNED support efficiency analysis. Dates include ACT_CMPL_DATE, ACT_CNCL_DATE, ACT_STRT_DATE, and PLN_STRT_DATE.

The dimensional foreign keys — PRD_LINE_FK_KEY, ITEM_FK_KEY, LOCATOR_FK_KEY, INSTANCE_FK_KEY, BASE_UOM_FK_KEY, SOB_CURRENCY_FK_KEY, RES_LOOKUP_FK_KEY, STS_LOOKUP_FK_KEY, and TRX_DATE_FK_KEY — are each indexed with bitmap indexes (N1 through N9), which is characteristic of a star-schema fact table optimized for ad hoc analytical queries. USER_FK1_KEY through USER_FK5_KEY and USER_MEASURE1 through USER_MEASURE5 provide extension slots.

Common Use Cases and Queries

The most frequent use case is production variance reporting — comparing planned versus actual output quantity and value by item, production line, or period. A typical query joins the fact to its dimensions through the bitmap-indexed keys:

• Yield analysis: SELECT ITEM_FK_KEY, SUM(ACT_OUT_QTY), SUM(PLN_OUT_QTY), AVG(FST_PASS_YLD) FROM OPI_EDW_JOB_DETAIL_F WHERE TRX_DATE_FK_KEY = :period GROUP BY ITEM_FK_KEY;
• Production line performance: aggregate ACT_JOB_TIME, RUN_TIME, and MOVE_TIME filtered by PRD_LINE_FK_KEY to identify bottlenecks.
• By-product and scrap cost reporting: sum ACT_BPR_VAL_B and ACT_SCR_VAL_B grouped by SOB_CURRENCY_FK_KEY for financial reconciliation.
• Rework and quality trending: count rows where REWORK_QTY > 0 or QC_FAIL_QTY > 0, segmented by RES_LOOKUP_FK_KEY (resource) and STS_LOOKUP_FK_KEY (status).

Because PRD_LINE_FK_KEY is bitmap indexed, queries filtering or grouping by production line perform efficiently without additional tuning. Users should be aware that the _B columns are denominated in the ledger (Set of Books) currency while _G columns are in warehouse currency, so currency conversion is implicit in the column suffix and should not be double-applied.

Related Objects

The documented unique indexes OPI_EDW_JOB_DETAIL_F_U2 and OPI_EDW_JOB_DETAIL_F_UKEY constrain the primary and business keys of this table and are the objects consulted first when validating row uniqueness. The bitmap indexes OP I_EDW_JOB_DETAIL_F_N1 through N9 reference the foreign keys SOB_CURRENCY_FK_KEY, BASE_UOM_FK_KEY, INSTANCE_FK_KEY, ITEM_FK_KEY, LOCATOR_FK_KEY, PRD_LINE_FK_KEY, RES_LOOKUP_FK_KEY, STS_LOOKUP_FK_KEY, and TRX_DATE_FK_KEY respectively, each of which corresponds to a conformed dimension table in the OPI EDW schema (item, locator, resource, status, currency, UOM, instance, production line, and time dimensions). The fact table is typically joined downstream to these dimensions on the matching _FK_KEY columns rather than to the base OPM transaction tables, which are loaded into the EDW through the OPI staging and mapping layer. No outbound foreign keys are documented, consistent with its standalone classification; consumers should therefore treat this object as a leaf fact and resolve dimensional context through the key columns above.