Results for “pjm_unit_numbers_pk”

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

Overview

PJM_UNIT_NUMBERS is a Project Manufacturing (PJM) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores "End Item Model/Unit Numbers." It is the master registry for unit numbers assigned to configured or model end items manufactured under project manufacturing and contract manufacturing business flows. Each row represents a distinct unit number — typically a serialized or tracked end item instance — identified by the primary key unit_number, and anchored to the inventory master organization and the specific end item defined in the item master.

The table plays a central role in the PJM "end item unit number" mechanism, where finished goods built against a project are identified by a unit number rather than solely by a serial number. Downstream Work in Process (WIP) and serialization processes resolve against this table to associate discrete jobs, flow schedules, and serial records with the correct end item unit. From a modeling perspective, the heuristic Data Vault classification for PJM_UNIT_NUMBERS is hub-leaning: the primary key unit_number is a stable business key, and the table is heavily referenced by foreign keys from transactional tables, which is characteristic of a hub rather than a satellite or link.

Key Information Stored

The documented physical schema for 12.2.2 contains 30 columns. The most significant include:

• UNIT_NUMBER — Primary key (PJM_UNIT_NUMBERS_PK) and the unique business identifier (PJM_UNIT_NUMBERS_U1). The end item unit number itself.
• END_ITEM_ID — Foreign key to MTL_SYSTEM_ITEMS_B; identifies the finished end item / model defined in the item master.
• MASTER_ORGANIZATION_ID — Foreign key to MTL_PARAMETERS; the inventory master organization in which the end item is defined.
• PREFIX — Optional prefix used when generating or formatting unit numbers.
• COMMENTS — Free-text notes on the unit number record.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield (DFF) columns used to capture customer-specific attributes on unit numbers.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard "Who" audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program request context columns, indicating that unit number rows are frequently created or updated by concurrent processes.

The surrogate primary key unit_number is a single-column PK, and the unique index PJM_UNIT_NUMBERS_U1 confirms it as the business-key candidate. The combination of unit_number with master_organization_id and end_item_id supplies the natural business context for the record.

Common Use Cases and Queries

Typical scenarios include determining which end item a unit number belongs to, listing all unit numbers for a project's end item, and reconciling unit numbers against serial numbers and discrete jobs.

• Resolve a unit number to its end item and master organization:

  SELECT pu.unit_number, ms.segment1 AS end_item,
         mp.organization_code AS master_org
  FROM   pjm_unit_numbers pu,
         mtl_system_items_b ms,
         mtl_parameters mp
  WHERE  pu.end_item_id = ms.inventory_item_id
  AND    pu.master_organization_id = ms.organization_id
  AND    pu.master_organization_id = mp.organization_id
  AND    pu.unit_number = :unit_number;
  

• List unit numbers for a given end item: filter on end_item_id and master_organization_id.
• Trace where a unit number is used by joining to MTL_SERIAL_NUMBERS, WIP_DISCRETE_JOBS, WIP_FLOW_SCHEDULES, or WIP_JOB_SCHEDULE_INTERFACE on the end_item_unit_number column.
• Audit changes over time by joining to PJM_UNIT_SERIAL_HISTORY on old_unit_number or new_unit_number.

These queries commonly support manufacturing reporting, project deliverables tracking, and reconciliations between PJM unit numbers and WIP/serialization transactions.

Related Objects

The table is tightly coupled to inventory, serialization, and work-in-process objects through the documented foreign keys:

• MTL_PARAMETERS — joined on PJM_UNIT_NUMBERS.MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID (master organization lookup).
• MTL_SYSTEM_ITEMS_B — joined on END_ITEM_ID and MASTER_ORGANIZATION_ID (end item definition).
• MTL_SERIAL_NUMBERS — references PJM_UNIT_NUMBERS via END_ITEM_UNIT_NUMBER.
• PJM_UNIT_SERIAL_HISTORY — references PJM_UNIT_NUMBERS twice, via OLD_UNIT_NUMBER and NEW_UNIT_NUMBER, tracking renumbering history.
• WIP_DISCRETE_JOBS — references PJM_UNIT_NUMBERS via END_ITEM_UNIT_NUMBER (discrete job association).
• WIP_FLOW_SCHEDULES — references PJM_UNIT_NUMBERS via END_ITEM_UNIT_NUMBER (repetitive/flow manufacturing).
• WIP_JOB_SCHEDULE_INTERFACE — references PJM_UNIT_NUMBERS via END_ITEM_UNIT_NUMBER (job import interface).

Together these relationships establish PJM_UNIT_NUMBERS as the central reference point that ties project manufacturing end items to their serialization and shop-floor execution records.