Results for “wip_op_res_instance_usage_v”

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

Overview

The APPS.WIP_OP_RES_INSTANCE_USAGE_V view is a Work in Process (WIP) reporting and integration object that exposes usage data for operation resources at the resource-instance level. It is defined in the APPS schema and is valid in Oracle EBS 12.1.1 and 12.2.2. The view joins WIP operation resource definitions with BOM resource and department setup, and optionally with WIP operation resource usage records, so that both planned and actual resource consumption can be reported in a single row per resource instance. Its role in EBS reporting is to provide a flattened, query-friendly projection of resource usage that spans manufacturing execution and setup data, which is particularly useful in shop floor dashboards, resource utilization extracts, and integration interfaces that need to reconcile scheduled resource assignments against actual usage.

Underlying Base Objects

The view is defined over seven documented base objects, all accessed through synonyms in the APPS schema: BOM_DEPARTMENTS, BOM_DEPARTMENT_RESOURCES, BOM_RESOURCES, WIP_OPERATIONS, WIP_OPERATION_RESOURCES, WIP_OPERATION_RESOURCE_USAGE, and WIP_OP_RESOURCE_INSTANCES. WIP_OP_RESOURCE_INSTANCES provides the driving resource-instance rows, while WIP_OPERATION_RESOURCES supplies the scheduled resource definition (restricted by SCHEDULED_FLAG IN (1, 3, 4)). WIP_OPERATION_RESOURCE_USAGE is outer-joined on WIP entity, operation sequence, resource sequence, organization, instance ID, and serial number, so instances without usage records still return rows. BOM_RESOURCES and BOM_DEPARTMENT_RESOURCES supply resource code and department-resource linkage, and BOM_DEPARTMENTS supplies the department code. WIP_OPERATIONS ties the operation to its department and organization, with the join on NVL(REPETITIVE_SCHEDULE_ID, -1) ensuring repetitive and discrete jobs do not cross-match.

Key Columns

The view exposes WIP_ENTITY_ID, REPETITIVE_SCHEDULE_ID, ORGANIZATION_ID, OPERATION_SEQ_NUM, and RESOURCE_SEQ_NUM as the primary job and operation context. RESOURCE_ID and RESOURCE_CODE identify the resource, while DEPARTMENT_ID and DEPARTMENT_CODE identify the owning department. SHARE_FROM_DEPT_ID indicates the department from which the resource is shared, which is significant for cross-department resource charging. START_DATE and COMPLETION_DATE are derived using DECODE against the usage record, falling back to the operation resource instance dates when usage is null, giving effective usage dates. ASSIGNED_UNITS reflects the assigned resource units, SCHEDULED_FLAG distinguishes scheduled resource states, and INSTANCE_ID and SERIAL_NUMBER identify the specific resource instance. A constant literal is returned for one of the select positions, and REPETITIVE_SCHEDULE_ID is returned as a TO_NUMBER(NULL) placeholder.

Common Use Cases and Queries

Typical scenarios include reporting actual resource usage per job, analyzing shared resources across departments, and extracting resource instance detail for integration. The SHARE_FROM_DEPT_ID column is commonly used to reconcile shared-resource costs. A representative query:

• SELECT wip_entity_id, organization_id, operation_seq_num, resource_seq_num, resource_code, department_code, share_from_dept_id, start_date, completion_date, assigned_units FROM apps.wip_op_res_instance_usage_v WHERE wip_entity_id = :p_wip_entity_id ORDER BY operation_seq_num, resource_seq_num;
• SELECT department_code, share_from_dept_id, resource_code, SUM(assigned_units) FROM apps.wip_op_res_instance_usage_v WHERE organization_id = :p_org_id GROUP BY department_code, share_from_dept_id, resource_code;
• SELECT COUNT(*) FROM apps.wip_op_res_instance_usage_v WHERE scheduled_flag = 1 AND instance_id IS NOT NULL;

Because SCHEDULED_FLAG is constrained to values 1, 3, and 4, callers should filter accordingly when validating results against source tables.