Results for “resh_reason_code”

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

Overview

OPI_EDW_JOB_DETAIL_FSTG is a fact staging table owned by the OPI (Operations Intelligence) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to serve as the staging area for the Job Detail fact, populated from two source views: OPI_EDW_OPM_JOB_DETAIL_FCV and OPI_EDW_OPI_JOB_DETAILS_FCV. In the Oracle EBS analytics architecture, staging tables such as this one receive extracted operational data, apply light transformation and key resolution, and then feed the corresponding EDW fact tables used by Operations Intelligence dashboards and reports. The table carries 109 columns in the documented 12.1.1 physical schema, reflecting the breadth of job, routing, cost, and quality attributes required to represent discrete and process manufacturing job detail at a granular transaction level.

The metadata classifies this object heuristically as standalone in Data Vault terms, meaning it does not present the characteristic multi-parent hub or link relationship pattern common to integrated model structures. In practice, this suggests modeling it as a satellite-style staging entity anchored on a single business key stream (the job detail identity), with resolved foreign keys attached as descriptive attributes rather than as first-class link structures.

Key Information Stored

The table's primary identifier is JOB_DETAIL_PK, a surrogate key, paired with JOB_DETAIL_PK_KEY as its associated key reference used for EDW-level key resolution. The business-grain identifier is JOB_NO combined with ORGANIZATION-related keys, and OPERATION_CODE identifies the specific routing step being reported.

• ACT_* columns — actual performance measures: ACT_BPR_VAL_B/G, ACT_INP_VAL_B/G, ACT_OUT_QTY, ACT_OUT_VAL_B/G, ACT_SCR_VAL_B/G, ACT_CMPL_DATE, ACT_STRT_DATE, ACT_CNCL_DATE, ACT_JOB_TIME, ACT_MTL_INP_VAL_B/G. The _B and _G suffixes denote base and global currency amounts.
• PLN_* columns — the equivalent planned counterparts: PLN_BPR_VAL_B/G, PLN_OUT_QTY, PLN_CMPL_DATE, PLN_STRT_DATE, PLN_JOB_TIME, and related value columns.
• STD_* columns — standard costs and time: STD_QTY, STD_TIME, STD_VAL_B, STD_VAL_G, plus STND_HRS_EARNED.
• Time element columns — SETUP_TIME, RUN_TIME, QUEUE_TIME, MOVE_TIME, and NO_TIME_RESH, supporting throughput and capacity analysis.
• Quality columns — QC_FAIL_QTY, QC_TEST, REWORK_QTY, SMPL_CNT, and FST_PASS_YLD.
• Job context columns — JOB_STATUS, MFG_MODE, ROUTING, ROUTING_REVISION, BASE_UOM_FK, LOCATOR_FK, PRD_LINE_FK, RES_LOOKUP_FK, STS_LOOKUP_FK, ITEM_FK, TRX_DATE_FK, SOB_CURRENCY_FK, and INSTANCE_FK, each with a corresponding _KEY column for EDW resolution.
• Control and audit columns — COLLECTION_DATE, COLLECTION_STATUS, ERROR_CODE, REQUEST_ID, CREATION_DATE, LAST_UPDATE_DATE, ROW_ID, and fifteen USER_ATTRIBUTE columns plus USER_MEASURE1 through USER_MEASURE5 and USER_FK1 through USER_FK5.

The only documented foreign key is ROW_ID referencing CS_SYSTEMS_ALL_B_TEMP, tying each staged row to a source system definition.

Common Use Cases and Queries

Typical usage centers on job cost variance and yield reporting. Comparing actual to planned values is a frequent pattern:

• Variance analysis: SELECT JOB_NO, OPERATION_CODE, ACT_OUT_QTY, PLN_OUT_QTY, (ACT_OUT_QTY - PLN_OUT_QTY) VARIANCE FROM OPI_EDW_JOB_DETAIL_FSTG;
• Yield and quality review: filter on FST_PASS_YLD, QC_FAIL_QTY, and REWORK_QTY by ROUTING and TRX_DATE_FK.
• Collection monitoring: query COLLECTION_STATUS, ERROR_CODE, and REQUEST_ID to validate staging loads before promoting to the fact layer.
• Cost reporting: aggregate ACT_OUT_VAL_B against STD_VAL_B and ACT_BPR_VAL_B by ITEM_FK, LOCATOR_FK, and SOB_CURRENCY_FK.

Related Objects

• OPI_EDW_OPM_JOB_DETAIL_FCV — primary source view for process manufacturing job detail.
• OPI_EDW_OPI_JOB_DETAILS_FCV — source view for discrete job detail.
• CS_SYSTEMS_ALL_B_TEMP — referenced through ROW_ID.
• EDW Job Detail fact table — downstream target populated from this staging table.
• OPI_EDW_ITEM_FCV / item dimension — joined via ITEM_FK and ITEM_FK_KEY.
• OPI_EDW_ORG / resource dimension — joined via RES_LOOKUP_FK and PRD_LINE_FK.
• OPI_EDW_DATE / TRX_DATE_FK — date dimension linkage.