Results for “avl_rsrc”

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

Overview

OPI_EDW_JOB_RSRC_FSTG is a staging (interface) table owned by the OPI schema within the Oracle Operations Intelligence (OPI) product family. It serves as the landing area for data extracted from Discrete Manufacturing and Process Manufacturing source systems before that data is transformed and loaded into the Job Resource Fact in the Oracle E-Business Suite data warehouse. In Oracle EBS 12.1.1 and 12.2.2, this table is a central component of the extract-transform-load pipeline that consolidates shop floor execution data for analytical reporting in the Operations Intelligence dashboards and the Enterprise Data Warehouse.

The table is documented in the ETRM metadata as VALID with 84 columns. Its heuristic Data Vault classification, mined from the foreign key structure, is standalone. This classification is offered as a modeling suggestion rather than a prescriptive definition. Practically, this means the table functions as a denormalized staging entity that receives raw feed data before the warehouse assigns final dimensional and fact surrogate keys. It is not a traditional Data Vault hub, link, or satellite, but rather a transient structure that supports controlled, auditable loading from manufacturing source systems.

Key Information Stored

Although the table contains 84 columns, most operations center on a manageable set of core attributes. The table carries a primary key column, JOB_RSRC_PK, which serves as the surrogate identifier for each staging record, along with a companion JOB_RSRC_PK_KEY used for linking to warehouse fact keys. Business-key candidates are represented through the composite of JOB_NO, OPERATION_SEQ_NO, OPERATION_CODE, RESOURCE reference columns, and TRX_DATE-related keys, which together describe a unique job-resource transaction in the source system.

• JOB_RSRC_PK / JOB_RSRC_PK_KEY — surrogate primary key and warehouse linkage key for the staging row.
• JOB_NO — the manufacturing job or batch identifier from Discrete or Process Manufacturing.
• OPERATION_SEQ_NO and OPERATION_CODE — the routing operation and sequence associated with resource usage.
• RSRC_FK / RSRC_FK_KEY — foreign key reference to the resource master used on the job.
• ITEM_FK / ITEM_FK_KEY — reference to the item being produced by the job.
• ACT_STRT_DATE and ACT_CMPL_DATE — actual start and completion timestamps for the operation.
• PLN_STRT_DATE and PLN_CMPL_DATE — planned start and completion dates used for schedule variance analysis.
• ACT_RSRC_QTY, PLN_RSRC_QTY, ACT_RSRC_COUNT, PLN_RSRC_COUNT — actual and planned resource quantities and counts.
• ACT_RSRC_USAGE, PLN_RSRC_USAGE, STND_RSRC_USAGE, AVL_RSRC — usage, standard usage, and available resource metrics.
• ACT_RSRC_VAL_B / _G and PLN_RSRC_VAL_B / _G — actual and planned resource values in base and global currency.
• EXTD_RSRC_COST — extended resource cost for the transaction.
• SOB_CURRENCY_FK — set of books currency context for monetary measures.
• COLLECTION_DATE and COLLECTION_STATUS — operational fields tracking the ETL collection cycle.
• ERROR_CODE, REQUEST_ID, CREATION_DATE, LAST_UPDATE_DATE — load diagnostics and audit columns.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE15, USER_MEASURE1 through USER_MEASURE5, USER_FK1 through USER_FK5 — configurable extension slots for customer-specific reporting.

Common Use Cases and Queries

The primary use case for OPI_EDW_JOB_RSRC_FSTG is analytical reporting on manufacturing job resource consumption — tracking whether actual resource usage, cost, and timing diverged from plan. A common pattern compares planned and actual resource values by job and resource, filtering only successfully collected rows:

• Resource variance analysis — SELECT JOB_NO, OPERATION_SEQ_NO, ACT_RSRC_VAL_B - PLN_RSRC_VAL_B AS variance FROM OPI_EDW_JOB_RSRC_FSTG WHERE COLLECTION_STATUS = 'SUCCESS';
• Schedule adherence — compare ACT_STRT_DATE and ACT_CMPL_DATE against PLN_STRT_DATE and PLN_CMPL_DATE to identify late operations.
• Cost trending — aggregate EXTD_RSRC_COST and ACT_RSRC_VAL_G by TRX_DATE_FK or SOB_CURRENCY_FK for period cost reporting.
• ETL monitoring — group by REQUEST_ID and COLLECTION_STATUS to detect failed or incomplete loads, and inspect ERROR_CODE for rejected rows.
• Resource utilization — sum ACT_RSRC_USAGE against AVL_RSRC to derive utilization ratios by resource.

Related Objects

The documented foreign key relationship references CS_SYSTEMS_ALL_B_TEMP from OPI_EDW_JOB_RSRC_FSTG.ROW_ID. Additional related objects from the OPI and manufacturing schemas include:

• OPI_EDW_JOB_RSRC_F — the target Job Resource Fact populated from this staging table.
• CS_SYSTEMS_ALL_B_TEMP — source system context, joined via ROW_ID.
• WIP_DISCRETE_JOBS and WIP_OPERATIONS — Discrete Manufacturing sources feeding JOB_NO and operation data.
• GME_BATCH_HEADER and GME_BATCH_STEPS — Process Manufacturing sources for batch job resource feeds.
• BOM_RESOURCES and WIP_REQUIREMENT_OPERATIONS — resource master and requirement sources referenced by RSRC_FK.
• OPI_DBI_* fact and dimension views — reporting layer objects that consume the loaded Job Resource Fact.
• FND_REQUEST / FND_CONCURRENT_REQUESTS — concurrent program execution records matched through REQUEST_ID.

Collectively these objects form the lineage chain from shop-floor transactional systems through staging into the Operations Intelligence warehouse, supporting governed manufacturing analytics across Discrete and Process Manufacturing.