Search Results outermost_license_plate_number




Overview
Explore in AI Chat

Overview

APPS.WMS_LICENSE_PLATE_NUMBERS_V is a reporting and integration view over Oracle Warehouse Management license plate (LPN) data. Its distinguishing purpose is to denormalize the raw license plate records held in WMS_LICENSE_PLATE_NUMBERS and resolve the numerical cost group identifier into a human-readable cost group name by joining to CST_COST_GROUPS. The view is presented as a UNION ALL of two branches: the first returns top-level (outermost) LPNs whose PARENT_LPN_ID is null, and the second returns nested child LPNs, resolving their parent license plate numbers and outermost LPN identifiers through self-joins back to the license plate table. This structure makes the view suitable for reporting on both standalone containers and containers that are physically packed inside other containers, without requiring the caller to write recursive or multi-pass logic. The user search term "cost_group_name" maps directly to the view column of that name, which is one of the principal reasons the view is queried rather than the base table.

Underlying Base Objects

The documented referenced base objects are WMS_LICENSE_PLATE_NUMBERS (accessed via synonym) and CST_COST_GROUPS (accessed via synonym). The first branch of the UNION ALL selects from WMS_LICENSE_PLATE_NUMBERS aliased as WLN, filtered by wln.parent_lpn_id is null, and performs an outer join to CST_COST_GROUPS aliased as CG on wln.cost_group_id = cg.cost_group_id(+). Because the join is outer, license plates without a matching cost group still appear, with COST_GROUP_NAME returned as null. The second branch selects from WMS_LICENSE_PLATE_NUMBERS aliased as WLP and self-joins to additional instances of the same table (WLP1, WLP2) to derive PARENT_LICENSE_PLATE_NUMBER and OUTERMOST_LICENSE_PLATE_NUMBER. Each branch also exposes the ROWID as ROW_ID, although this is a composite across both branches and should not be treated as a stable surrogate key. The view therefore carries no additional storage; it is a pure definition layered over these two base objects.

Key Columns

Common Use Cases and Queries

Typical uses include LPN inventory reporting, container hierarchy analysis, and cost group classification of packed material. A cost-group-oriented query follows:

SELECT lpn_id, license_plate_number, organization_id,
       cost_group_id, cost_group_name
FROM   apps.wms_license_plate_numbers_v
WHERE  cost_group_name = 'DEFAULT';

Because the view already filters and unions parent and child rows, callers reporting on nesting can query directly without recursion:

SELECT license_plate_number, parent_license_plate_number,
       outermost_license_plate_number, inventory_item_id
FROM   apps.wms_license_plate_numbers_v
WHERE  parent_license_plate_number IS NOT NULL
ORDER  BY outermost_license_plate_number;

Where no cost group is assigned, COST_GROUP_NAME is null by virtue of the outer join, so queries filtering on a specific name will naturally exclude unassigned plates unless an explicit null check is added.