Search Results txn_src

Overview

OPI_EDW_OPI_JOB_DETAIL_FCV is an Oracle E-Business Suite source view owned by the APPS schema and validated in both 12.1.1 and 12.2.2. It belongs to the OPI (Operations Intelligence) product family and serves as the extract view that feeds the Job Detail fact table within the Oracle E-Business Suite data warehouse. Its purpose is to consolidate work-in-process job information — quantities, costs, and currency-converted values — into a denormalized, flat structure suitable for dimensional reporting and ETL consumption.

The view is a "FCV," or fact collection view, meaning it is designed to be consumed by the EDW/BI layer rather than by transactional forms. It joins job data from the OPI job tables to item and organization master data, and it applies UOM conversion and currency conversion through centralized EDW utility functions. Because it references cost_element_value attributes such as ACT_MTL_INP_VAL (actual material input value) and ACT_OUT_VAL (actual output value), it is commonly surfaced when users search for cost element information tied to jobs.

Underlying Base Objects

The documented metadata for the view lists no separately registered base objects in ETRM 12.2.2, and the view text relies on inline subquery aliases rather than explicit schema-qualified table references. Based on the SQL excerpt, the principal sources are the job master records (aliased JOBS, keyed by JOB_ID), the item/organization combination represented by ITEM_ORG, the instance identification (INST.INSTANCE_CODE), and material/item attributes from the primary item view (MSI.PRIMARY_UOM_CODE). The view also calls EDW_UTIL.GET_UOM_CONV_RATE and EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT, which are EDW package functions used to normalize quantities and amounts.

Because the view text is truncated in the documentation, a definitive column-by-column mapping to base tables cannot be fully reconstructed from the metadata alone; the composition is best treated as the standard OPI job fact extraction pattern built over the WIP/discrete job and item master structures.

Key Columns

• JOB_DETAIL_PK — Surrogate key built as ORG_ID || '-' || JOB_ID || '-' || ITEM_ORG || '-' || INSTANCE_CODE || '-OPI', uniquely identifying each job detail row.
• ACT_OUT_QTY / PLN_OUT_QTY — Actual and planned output quantities, multiplied by the primary UOM conversion rate.
• ACT_OUT_VAL_B / ACT_OUT_VAL_G — Actual output value in base and global (converted) currency; the global value is produced by EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT.
• ACT_INP_VAL_B / ACT_INP_VAL_G — Actual input value in base and global currency.
• ACT_MTL_INP_VAL_B / ACT_MTL_INP_VAL_G — Actual material input value in base and global currency, a core material cost element.
• PLN_MTL_INP_VAL_B / PLN_MTL_INP_VAL_G — Planned material input value, derived conditionally using DECODE(ENTITY_TYPE, 4, ACT_MTL_INP_VAL, PLN_MTL_INP_VAL).
• ACT_SCR_VAL_B / ACT_SCR_VAL_G — Actual scrap value in base and global currency.

Common Use Cases and Queries

Typical uses include job-level cost analysis, material yield tracking, and currency-normalized reporting in the OPI warehouse. The following illustrates a standard extract:

SELECT JOB_DETAIL_PK,
       ACT_OUT_QTY,
       ACT_OUT_VAL_B,
       ACT_OUT_VAL_G,
       ACT_MTL_INP_VAL_B,
       ACT_MTL_INP_VAL_G,
       PLN_MTL_INP_VAL_B
FROM   APPS.OPI_EDW_OPI_JOB_DETAIL_FCV
WHERE  ACT_MTL_INP_VAL_B IS NOT NULL;

Cost element reporting compares actual versus planned material input value, while scrap analysis aggregates ACT_SCR_VAL_B by organization. Because global columns depend on ACT_CMPL_DATE and CURRENCY_CODE, queries filtering incomplete jobs should account for NULL conversion results.