Search Results sob_currency_fk_key

Overview

OPI.OPI_EDW_JOB_RSRC_F is the Job Resource Fact table within the Oracle E-Business Suite Operational Intelligence (OPI) enterprise data warehouse. It stores detailed information regarding resources consumed in jobs and batches, capturing usage, value, and count measures for each resource alongside the quantities produced by those resources and their associated timing information. The table records both actual and planned values for every measure, allowing comparative analysis of scheduled versus realized resource consumption. In addition, it carries descriptive attributes such as the department code and job number to support dimensional slicing.

The object resides in the APPS_TS_SUMMARY tablespace with a PCTFREE of 10, consistent with a summary-layer fact table that is bulk-loaded and queried analytically. It is owned by the OPI schema and is designated as FND Design Data under the same name. The table carries 63 documented columns in the 12.1.1 physical schema.

From a modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is standalone, suggesting this object functions as an independent fact rather than a hub, link, or satellite. Its structure — a composite primary key over JOB_RSRC_PK and JOB_RSRC_PK_KEY, surrounded by a dense set of bitmap-indexed dimension foreign keys — is characteristic of a star-schema fact table intended for direct dimensional reporting rather than normalized integration modeling.

Key Information Stored

The ten to fifteen most significant columns fall into three groups: identity, dimensional references, and measures.

• JOB_RSRC_PK_KEY and JOB_RSRC_PK — the composite primary key, enforced by the unique index OPI_EDW_JOB_RSRC_F_U2. A second unique index, OPI_EDW_JOB_RSRC_F_UKEY, covers JOB_RSRC_PK_KEY alone, marking it as the strongest business-key candidate.
• RSRC_FK_KEY — surrogate reference to the resource dimension; the central subject of the fact.
• OPRN_FK_KEY and OPERATION_SEQ_NO — the operation dimension reference and the sequence number of the job step.
• ITEM_FK_KEY, LOCATOR_FK_KEY, INSTANCE_FK_KEY, ACTIVITY_FK_KEY — item, locator, instance, and activity dimension surrogates.
• QTY_UOM_FK_KEY — the unit of measure for produced quantity; the bitmap index OPI_EDW_JOB_RSRC_F_N3 is built on this column, which is the term the user searched for. USAGE_UOM_FK_KEY covers the parallel unit of measure for resource usage (index N2).
• TRX_DATE_FK_KEY and SOB_CURRENCY_FK_KEY — the date and set-of-books currency dimension references, both bitmap-indexed (N10 and N1 respectively).
• ACT_RSRC_QTY, ACT_RSRC_COUNT, ACT_RSRC_USAGE, together with PLN_RSRC_QTY, PLN_RSRC_COUNT, and PLN_RSRC_USAGE — the paired actual and planned quantity, count, and usage measures.
• ACT_RSRC_VAL_B/_G and PLN_RSRC_VAL_B/_G — actual and planned resource value in base and global currency, supported by the parallel usage-value columns.
• ACT_STRT_DATE, ACT_CMPL_DATE, PLN_STRT_DATE, PLN_CMPL_DATE — actual and planned start and completion dates for the job step.
• DEPARTMENT and JOB_NO — descriptive departmental and job identifiers; EXTD_RSRC_COST and STND_RSRC_USAGE provide extended cost and standard usage reference values.
• USER_FK1_KEY through USER_FK5_KEY, USER_MEASURE1 through USER_MEASURE5, and USER_ATTRIBUTE1 through USER_ATTRIBUTE15 — extension slots for customer-specific dimensions, measures, and attributes.

Common Use Cases and Queries

The primary reporting use case is resource consumption analysis: comparing planned versus actual usage, count, and value for resources applied against discrete jobs and batches. A typical aggregate joins the fact to the resource, item, and date dimensions to report usage variance by department and resource over a period.

A representative query pattern groups the fact by resource, unit of measure, and transaction date:

• SELECT rsrc_fk_key, usage_uom_fk_key, trx_date_fk_key, SUM(act_rsrc_usage), SUM(pln_rsrc_usage) FROM opi.opi_edw_job_rsrc_f WHERE trx_date_fk_key BETWEEN :lo AND :hi GROUP BY rsrc_fk_key, usage_uom_fk_key, trx_date_fk_key;
• Variance reporting: SELECT department, job_no, SUM(act_rsrc_val_g - pln_rsrc_val_g) FROM opi.opi_edw_job_rsrc_f GROUP BY department, job_no;
• Unit-of-measure reconciliation: filter or group on qty_uom_fk_key and usage_uom_fk_key to verify that produced quantities and consumed usage are reported in consistent units.
• Step-level timing analysis comparing ACT_STRT_DATE and ACT_CMPL_DATE against PLN_STRT_DATE and PLN_CMPL_DATE for schedule adherence reporting.

Because the dimensional keys are bitmap-indexed, the table is optimized for multi-predicate ad hoc filtering — for example, restricting by item, locator, and date simultaneously before aggregating measures. The presence of both actual and planned columns means the same table serves as-of reporting and variance analysis without a second fact source.

Related Objects

The foreign-key and index structure identifies the most significant related objects as the conformed dimensions referenced by the surrogate keys:

• The resource dimension, joined on RSRC_FK_KEY, providing resource identity and description.
• The operation dimension, joined on OPRN_FK_KEY, supplying operation and routing context.
• The item dimension, joined on ITEM_FK_KEY, for produced-item reporting.
• The locator dimension, joined on LOCATOR_FK_KEY.
• The unit-of-measure dimension, joined on QTY_UOM_FK_KEY and USAGE_UOM_FK_KEY.
• The date dimension, joined on TRX_DATE_FK_KEY.
• The currency/set-of-books dimension, joined on SOB_CURRENCY_FK_KEY.
• The activity dimension, joined on ACTIVITY_FK_KEY.
• The instance dimension, joined on INSTANCE_FK_KEY.
• The sourcing job and batch facts or staging tables that populate this fact via the ETL load program, which generates the surrogate primary key JOB_RSRC_PK_KEY.

In the 12.1.1 and 12.2.2 schemas the object retains the same owner, name, and status of VALID. Analysts should treat the unique indexes as the authoritative business-key definition and resolve all reporting joins through the FK_KEY surrogate columns rather than the descriptive DEPARMENT or JOB_NO attributes.