Results for “group_mark_id”

2 results




Overview
Explore in AI Chat

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

Overview

The APPS.WIP_JOB_SERIAL_NUMBERS_V view is a Work in Process (WIP) reporting object that consolidates serial number information captured against discrete work orders and their associated operations. It joins serial numbers held in MTL_SERIAL_NUMBERS to work order header and routing detail, then decorates each row with decoded lookup values for serial status and intraoperation step, plus the department code and the standard operation code. In Oracle EBS 12.1.1 and 12.2.2 it is exposed as a read-only view in the APPS schema and is status VALID in ETRM.

Its principal value is that it resolves the encoded foreign keys used in the underlying tables—meaning values, department identifiers, and standard operation identifiers—into human-readable text, thereby allowing reporting tools, OAF pages, and third-party integrations to present work order serial activity without repeatedly reimplementing the decode logic. The view is particularly relevant where the standard operation code underlying a routing step must be surfaced alongside the serial number that moved through that step.

Underlying Base Objects

ETRM documents the following referenced objects: BOM_DEPARTMENTS, BOM_STANDARD_OPERATIONS, MFG_LOOKUPS, MTL_OBJECT_GENEALOGY, MTL_SERIAL_NUMBERS, WIP_ENTITIES, and WIP_OPERATIONS. All are referenced by synonym except MFG_LOOKUPS, which is referenced as a view.

The query is a UNION ALL of two branches. The first branch drives from MTL_SERIAL_NUMBERS to WIP_OPERATIONS on WIP_ENTITY_ID and OPERATION_SEQ_NUM (outer-joined so that serials not yet assigned to an operation are retained), then outer-joins WIP_OPERATIONS to BOM_STANDARD_OPERATIONS on STANDARD_OPERATION_ID and to BOM_DEPARTMENTS on DEPARTMENT_ID. WIP_ENTITIES supplies the work order context. Two lookups are resolved: WIP_SERIAL_STATUS via a DECODE on OPERATION_SEQ_NUM (NULL yields lookup code 1, otherwise 2), and WIP_INTRAOPERATION_STEP via INTRAOPERATION_STEP_TYPE. The second branch captures serials linked through MTL_OBJECT_GENEALOGY to WIP_ENTITIES, assigning the status code 3 and NULL routing attributes.

Key Columns

  • SERIAL_NUMBER — the serial identifier from MTL_SERIAL_NUMBERS.
  • WIP_SERIAL_STATUS — numeric status derived from OPERATION_SEQ_NUM (1 or 2) or the constant 3 for genealogy-based rows.
  • WIP_SERIAL_STATUS_CODE — the decoded WIP_SERIAL_STATUS meaning from MFG_LOOKUPS.
  • CURRENT_STATUS — the serial's current status as recorded in MTL_SERIAL_NUMBERS.
  • MSN_ROWID — ROWID of the underlying serial number row, useful for drill-back.
  • WIP_ENTITY_ID — the work order the serial is associated with.
  • ORGANIZATION_ID — current organization of the serial.
  • PRIMARY_ITEM_ID — inventory item of the serial.
  • DEPARTMENT_CODE — department of the associated routing operation.
  • OPERATION_SEQ_NUM — operation sequence number on the routing.
  • INTRAOPERATION_STEP_CODE / INTRAOPERATION_STEP_TYP — decoded and raw intraoperation step (for example Queue, Run, To Move).
  • STANDARD_OPERATION_CODE — the BOM_STANDARD_OPERATIONS.OPERATION_CODE for the routing step; this is the field commonly retrieved when a user searches for standard_operation_code.

Common Use Cases and Queries

The view is typically used to list serial numbers by work order, to report serials still sitting at a routing operation, and to identify the standard operation code executed on a serial. A representative query follows:

  • Serials with their standard operation code for a given work order: SELECT serial_number, wip_entity_id, operation_seq_num, standard_operation_code, department_code, wip_serial_status_code FROM apps.wip_job_serial_numbers_v WHERE wip_entity_id = :p_wip_entity_id ORDER BY operation_seq_num;
  • Serials at an intraoperation step: SELECT serial_number, operation_seq_num, intraoperation_step_code FROM apps.wip_job_serial_numbers_v WHERE intraoperation_step_code = 'QUEUE';
  • Status summary across a job: SELECT wip_serial_status_code, COUNT(*) FROM apps.wip_job_serial_numbers_v WHERE wip_entity_id = :p_wip_entity_id GROUP BY wip_serial_status_code;
  • Serials linked only via genealogy: SELECT serial_number, wip_entity_id FROM apps.wip_job_serial_numbers_v WHERE wip_serial_status = 3;

Because the routing attributes in the first branch are outer-joined, callers should expect NULL STANDARD_OPERATION_CODE values for serials not yet tied to a specific operation or resolved through the genealogy branch.