Search Results rsrc_fk




Overview
Explore in AI Chat

Overview

OPI_EDW_OPI_JOB_RSRC_FCV is a public view owned by the APPS schema in Oracle E-Business Suite, registered under FND Design Data as OPI.OPI_EDW_OPI_JOB_RSRC_FCV. It is classified as a flat/conformed view (the "FCV" suffix) that serves the Oracle Process Manufacturing (OPM) reporting and data warehouse integration layer. Its stated purpose is to act as the source view for extracting Job Resource information from OPI (Oracle Process Industries) sources, making it a standard staging point for ETL processes and for custom operational reporting against job and resource transactions.

The view aggregates both planned and actual resource measures alongside their monetary valuations, expressed in warehouse currency and in the set of books currency. Because it is a public view, it may be queried directly by custom reports, concurrent programs, and BI Publisher data models without modifying the underlying transactional tables. In the 12.1.1 and 12.2.2 release streams the view retains a VALID status, and it does not carry the _VL or _V security predicate convention, so it does not enforce row-level operating unit security on its own.

Underlying Base Objects

The ETRM metadata for this object documents no referenced base objects, which is common for conformed warehouse views whose definitions are shipped by the OPI.OPI design data rather than being introspectable through the standard dependency report. Functionally, the view draws from the OPM job and job resource tables — primarily the junction of GME_BATCH_HEADER and the OPM resource usage / job resource detail tables — together with OPI_EDW source keys such as VIEW_ID and SEQ_ID that identify the ETL slice and sequence. The JOB_RSRC_PK column carries the VARCHAR2(239) composite primary key of the underlying job resource record, and JOB_NO and OPERATION_SEQ_NO link each resource row back to its job and routing operation. Because dependency information is not published, the view should be treated as a black-box extraction surface: consumers should reference it by name rather than assuming column-to-table lineage.

Key Columns

  • VIEW_ID / SEQ_ID / JOB_RSRC_PK — ETL view identifier, sequence surrogate key, and the composite primary key for the job resource record.
  • ACT_RSRC_COUNT / PLN_RSRC_COUNT — Actual and planned resource counts consumed at the batch step.
  • ACT_RSRC_QTY / PLN_RSRC_QTY — Actual and planned resource quantities.
  • STND_RSRC_USAGE — The key column returned when searching on "stnd_rsrc_usage." Documented as (planned resource usage / planned quantity produced) × actual quantity produced, i.e., the standard resource consumption normalized against actual production. Commonly used to derive resource variances.
  • ACT_RSRC_USAGE / PLN_RSRC_USAGE — Actual and planned resource usage values.
  • ACT_RSRC_VAL_B / ACT_RSRC_VAL_G, PLN_RSRC_VAL_B / PLN_RSRC_VAL_G, and *_USAGE_VAL_B / *_USAGE_VAL_G — Actual, planned, and usage valuations in set of books currency (B) and warehouse currency (G), supporting dual-currency and cost analysis.
  • EXTD_RSRC_COST — Extended resource cost for the line.
  • JOB_NO, OPERATION_SEQ_NO, DEPARTMENT — Job identifier, operation sequence, and owning department for grouping and drill-down.
  • ACT_STRT_DATE / ACT_CMPL_DATE / PLN_STRT_DATE / PLN_CMPL_DATE — Actual and planned start and completion dates for schedule versus execution analysis.

Common Use Cases and Queries

Typical scenarios include resource variance reporting (standard versus actual usage), job cost roll-ups by department or operation, and ETL extracts into a data warehouse or custom reporting schema. A representative query isolating standard resource usage is:

SELECT job_no, operation_seq_no, department, pln_rsrc_qty, act_rsrc_qty, stnd_rsrc_usage, act_rsrc_usage, (act_rsrc_usage - stnd_rsrc_usage) AS usage_variance FROM apps.opi_edw_opi_job_rsrc_fcv WHERE job_no = :p_job ORDER BY operation_seq_no;

For cost and currency analysis, filter on the warehouse and set of books value columns:

SELECT department, SUM(pln_rsrc_usage_val_g) pln_val, SUM(act_rsrc_usage_val_g) act_val, SUM(extd_rsrc_cost) extd_cost FROM apps.opi_edw_opi_job_rsrc_fcv GROUP BY department;

Because the view exposes no operating unit or security predicate, callers in a multi-org environment should join to the owning job or batch table to apply appropriate org-level filtering, and should account for the ETL key columns when deduplicating warehouse refreshes.