Search Results stnd_hrs_earned

Overview

APPS.OPI_EDW_JOB_DETAIL_V is an Oracle E-Business Suite operational data store view that exposes completed production job detail in a denormalized, star-schema-friendly form. It is a reporting and extract layer built for the Oracle Process Manufacturing (OPM) and Enterprise Data Warehouse (EDW) analytics stack, presenting one row per closed or otherwise completed production job batch. The view consolidates batch-level outcome measures—material input and output valuations, scrap valuation, output quantities, and job duration—against conformed dimension keys for company, organization, operating unit, plant, calendar, product line, item revision, inventory locator, and unit of measure. Because it is defined over the EDW materialized dimension and fact tables rather than directly over the transactional OPM tables, it is intended for downstream ETL, data mart loading, and BI queries rather than for transactional forms or concurrent program processing. In the context of EBS 12.1.1 / 12.2.2, it sits at the boundary between OPM production execution data and the warehouse objects that feed cost, yield, and efficiency reporting.

Underlying Base Objects

The view joins a single OPM job detail fact table to five EDW dimension tables. Documented base objects referenced in the view text are:

• OPI_EDW_JOB_DETAIL_F (aliased jd) — the job-level fact table supplying material input value, output value, scrap value, output quantity, standard quantity, standard hours earned, job time, and the completion date attributes.
• EDW_MTL_INVENTORY_LOC_M (inv) — inventory locator dimension supplying company, organization, operating unit, and plant keys.
• EDW_TIME_M (time) — calendar dimension supplying year, quarter, and period keys.
• EDW_OPI_PRDL_M (pl) — product line dimension.
• edw_items_m (it) — item dimension supplying category, item revision, item number, and item organization keys.
• EDW_MTL_UOM_M (uom) — unit of measure dimension.

Joins are performed entirely on surrogate foreign keys: LOCATOR_FK_KEY to the inventory locator key, TRX_DATE_FK_KEY to the calendar day key, PRD_LINE_FK_KEY to the product line key, ITEM_FK_KEY to the item revision key, and BASE_UOM_FK_KEY to the UOM key. A restricting predicate limits the result set to jobs whose JOB_STATUS is one of Closed Batch, Certified, Complete, or Closed, so in-process and cancelled batches are excluded.

Key Columns

• Dimension surrogate keys — ALL_INV_ID, PCMP_ID, PORG_ID, OPERATING_UNIT_ID, PLANT_ID, ALL_TIME_ID, CAL_YR_ID, CAL_QTR_ID, CAL_PERIOD_ID, ALL_PRDL_ID, PRDL_ID, ALL_ITEMS_ID, ITEM_CAT_ID, ITEM_ID, ITEM_NUMBER_ID, ITEM_ORG_ID, and UOM_ID provide the conformed keys used for dimensional aggregation.
• Valuation measures — ACT_MTL_INP_VAL_G, PLN_MTL_INP_VAL_G, ACT_OUT_VAL_G, PLN_OUT_VAL_G, ACT_SCR_VAL_G, and PLN_SCR_VAL_G expose actual versus planned material input, output, and scrap values in the global reporting currency.
• Quantity and time measures — ACT_OUT_QTY, PLN_OUT_QTY, STD_QTY, ACT_JOB_TIME, STND_HRS_EARNED, and STD_TIME support yield, variance, and efficiency analysis.
• LATE_JOB — a derived flag computed as CASE WHEN ACT_CMPL_DATE > PLN_CMPL_DATE THEN 1 ELSE 0 END. This is the column supporting the user's search term pln_cmpl_date (planned completion date), and it is the only column in this view that references the planned completion date; the underlying fact column itself is not projected.
• JOB — a constant literal (1) used as a counting or rollup helper.

Common Use Cases and Queries

Typical usage includes late-batch performance reporting, actual-versus-planned yield and cost variance analysis, and period-end extraction into a manufacturing data mart.

Identifying late completed jobs by plant and period:

SELECT plant_id, cal_period_id, COUNT(*) late_jobs
FROM   apps.opi_edw_job_detail_v
WHERE  late_job = 1
GROUP  BY plant_id, cal_period_id;

Measuring planned versus actual material input variance:

SELECT prdl_id, item_id,
       SUM(act_mtl_inp_val_g - pln_mtl_inp_val_g) mtl_variance
FROM   apps.opi_edw_job_detail_v
WHERE  cal_yr_id = :year
GROUP  BY prdl_id, item_id;

Comparing output yield and scrap against standard:

SELECT item_number_id, uom_id,
       SUM(act_out_qty) actual_out_qty, SUM(std_qty) std_qty,
       SUM(act_scr_val_g) scrap_value
FROM   apps.opi_edw_job_detail_v
GROUP  BY item_number_id, uom_id;

Because the view restricts to the four completed job statuses and joins only on surrogate keys, queries should filter on the dimension key columns or join the corresponding EDW dimension views to obtain descriptive attribute values.