Results for “pln_scr_val_g”

21 results




Overview
Explore in AI Chat

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

Overview

OPI_EDW_JOB_DETAIL_F is a fact table in the Operations Intelligence (OPI) module of Oracle E-Business Suite, owned by the OPI schema. It stores detailed transactional information about manufacturing jobs and batches, capturing the outcomes, variances, and operational metrics associated with each job's end products. The table functions as the granular backbone of the OPI data warehouse, which is used for manufacturing analytics, batch costing, and production performance reporting.

The grain of the table is one row per job per end product. As stated in the ETRM documentation, if a batch produces two products — a main product and a co-product — the fact table will hold separate rows for each, ensuring that yield, valuation, and quantity measures are attributed correctly to each output item. This fine-grained design supports both product-level and job-level aggregation.

The metadata's heuristic Data Vault classification identifies this object as standalone, meaning it is modeled as an independent fact rather than a hub, link, or satellite. This reflects its role as a star-schema-style fact table within the OPI EDW, where dimensional context is supplied through foreign keys rather than normalized Data Vault structures.

Key Information Stored

The table contains 89 columns. The most significant are summarized below.

  • JOB_DETAIL_PK — Part of the composite primary key OPI_EDW_JOB_DETAIL_F_U2; identifies the job detail record within the source system lineage.
  • JOB_DETAIL_PK_KEY — The surrogate key and the sole column of unique index OPI_EDW_JOB_DETAIL_F_UKEY. This is the column most often referenced in joins and is the target of the user's search for "job_detail_pk_key". It is also the second component of the composite unique index.
  • JOB_NO — The user-visible job or batch number from the source manufacturing system.
  • JOB_STATUS — Current status of the job (e.g., released, complete, closed), essential for filtering active versus historic work.
  • ITEM_FK_KEY — Foreign key to the item dimension, identifying the end product produced by the job.
  • INSTANCE_FK_KEY — Foreign key tying the record to the originating source instance, relevant in multi-instance OPI deployments.
  • TRX_DATE_FK_KEY — Foreign key to the transaction date dimension, enabling time-series analysis.
  • ACT_OUT_QTY / PLN_OUT_QTY — Actual and planned output quantities, the basis for yield and attainment calculations.
  • ACT_INP_VAL_B / ACT_INP_VAL_G — Actual input value in base and global currency; the principal cost measures.
  • ACT_OUT_VAL_B / ACT_OUT_VAL_G — Actual output valuation, used in cost rollups and variance analysis.
  • FST_PASS_YLD — First-pass yield, a key quality and efficiency metric.
  • RESH_REASON_CODE / NO_TIME_RESH — Reason code and count of time reschedules, supporting schedule compliance reporting.
  • BASE_UOM_FK_KEY — Foreign key to the unit of measure dimension for quantity normalization.
  • USER_MEASURE1 through USER_MEASURE5 and USER_ATTRIBUTE1 through USER_ATTRIBUTE15 — Extensible slots for customer-defined measures and descriptive attributes.

Common Use Cases and Queries

The table is primarily queried through BI Publisher reports, OBIEE/OAC dashboards, and direct SQL against the OPI EDW schema. Typical scenarios include batch yield analysis, planned-versus-actual cost variance reporting, first-pass yield trending, and job status aging.

A representative query joins the fact to a date dimension and an item dimension using the surrogate keys:

  • SELECT jd.JOB_NO, jd.ACT_OUT_QTY, jd.PLN_OUT_QTY, jd.FST_PASS_YLD FROM OPI.OPI_EDW_JOB_DETAIL_F jd WHERE jd.JOB_DETAIL_PK_KEY = :p_key;
  • Aggregation by item: SELECT jd.ITEM_FK_KEY, SUM(ACT_OUT_VAL_B) FROM OPI_EDW_JOB_DETAIL_F jd GROUP BY jd.ITEM_FK_KEY;
  • Time-series analysis joining on TRX_DATE_FK_KEY to the date dimension.

Related Objects

No foreign keys are documented on this table, consistent with its standalone classification; relationships are expressed through surrogate keys resolved in the BI layer or via dimensional views. The most significant related objects are the OPI EDW dimension tables referenced by the FK key columns.