Oracle RMS Pack Items

What Are Pack Items in Oracle RMS?#

In Oracle RMS, a pack item is an item that contains one or more component items at defined quantities. The pack has its own item number in ITEM_MASTER, its own stock on hand in ITEM_LOC_SOH, and can appear on purchase orders and transfers just like any regular item. What makes it different is the PACKSKU table — a component breakdown that says what is inside the pack and in what quantity.

Packs exist in retail for three main commercial reasons. First, suppliers often ship in pre-configured multi-unit cases that cannot be broken before receipt — a supplier may only sell a product in cartons of twelve. Second, retailers create bundled selling units — a gift set containing three related products sold together at a single price. Third, buyers consolidate replenishment orders into mixed-item vendor packs to take advantage of minimum order quantities or freight efficiency.

Oracle RMS models all three scenarios with the same underlying mechanism — a pack item record plus component rows in PACKSKU — but with different configurations of SIMPLE_PACK_IND, ORDERABLE_IND, and SELLABLE_IND that determine how the pack behaves operationally.

The Pack Flags — PACK_IND and SIMPLE_PACK_IND#

Two columns on ITEM_MASTER together determine whether an item is a pack and what kind of pack it is:

These two flags in combination produce three meaningful states:

Simple Packs — Same Component, Multiple Units#

A simple pack is the most common pack type in Oracle RMS. It represents a pre-defined multi-unit grouping of a single sellable item — typically a supplier-defined carton or consumer multi-pack.

Complex Packs — Mixed Components#

A complex pack contains two or more different component items at specified quantities. Each component has its own ITEM_MASTER row and its own SOH tracking. The pack itself also has its own SOH. These two layers of inventory exist simultaneously and independently.

Buyer Packs — Vendor Pre-Packs#

A buyer pack (sometimes called a vendor pack or pre-pack) is a specific sub-type of pack used in fashion and apparel retail. It is a supplier-defined assortment of the same style item across multiple sizes or colours — shipped as a single unit at a single cost.

A classic buyer pack example: a buyer orders a pre-pack of a polo shirt that contains 2 units of size S, 3 units of size M, 3 units of size L, and 2 units of size XL — all in the same colour. The supplier ships this as one "pack" of 10 units. The buyer's PO is raised against the pack item, not the individual SKUs.

In RMS terms, a buyer pack is structurally a complex pack (PACK_IND = 'Y', SIMPLE_PACK_IND = 'N') where all the component items are child SKUs of the same parent style. What distinguishes it commercially is:

The PACKSKU Table — Component Structure#

The PACKSKU table is the structural backbone of every pack item. It defines what is inside a pack and in what quantity. Without at least one row here for a pack item, the pack has no defined contents — it is an empty shell that will fail receiving validation.

ORDERABLE_IND and SELLABLE_IND#

These two indicators on ITEM_MASTER control how a pack participates in the supply chain. They are not pack-specific — they exist on all item types — but their combination has particularly important meaning for packs.

Pack SOH vs Component SOH#

This is the concept that causes the most inventory calculation errors when developers are new to RMS pack items. The pack item and its component items each maintain independent, separate stock on hand positions in ITEM_LOC_SOH. They do not automatically stay in sync.

Pack Breakdown — When Packs Are Opened#

Pack breakdown is the transaction that converts pack stock into component stock. It reduces the pack's SOH by the number of packs being broken and increases each component item's SOH by the corresponding quantity from PACKSKU.

In Oracle RMS, pack breakdown can occur in three contexts:

Key Tables — Quick Reference#

Practical SQL Examples#

1. List all components of a pack with quantities and component details

-- Full component breakdown for a pack item
-- Use V_PACKSKU_QTY view for description joins (preferred over direct PACKSKU)
SELECT ps.pack_no,
im_pack.item_desc                  pack_desc,
im_pack.simple_pack_ind,
ps.item                            component_item,
im_comp.item_desc                  component_desc,
ps.quantity,
ps.uom,
im_comp.unit_of_measure            component_uom,
im_comp.status                     component_status,
-- Implied total units in one pack
ps.quantity                        units_per_pack
FROM   packsku       ps
JOIN   item_master   im_pack ON im_pack.item = ps.pack_no
JOIN   item_master   im_comp ON im_comp.item = ps.item
WHERE  ps.pack_no = :v_pack_item
ORDER BY ps.item;

2. All packs that contain a specific component item

-- Find every pack that uses a given item as a component
-- Useful when discontinuing an item — must also handle all packs containing it
SELECT ps.pack_no,
im_pack.item_desc                  pack_desc,
im_pack.pack_ind,
im_pack.simple_pack_ind,
im_pack.orderable_ind,
im_pack.sellable_ind,
im_pack.status                     pack_status,
ps.quantity                        qty_in_pack
FROM   packsku     ps
JOIN   item_master im_pack ON im_pack.item = ps.pack_no
WHERE  ps.item = :v_component_item
ORDER BY im_pack.status, im_pack.item_desc;

3. Pack SOH vs component SOH — theoretical availability comparison

-- Compare pack SOH vs loose component SOH at a location
-- Shows theoretical total availability (packs × qty + loose units)
SELECT ps.item                             component_item,
im_comp.item_desc                   component_desc,
-- Loose component SOH at this location
NVL(soh_comp.stock_on_hand, 0)      loose_component_soh,
-- Pack SOH and implied component units locked in packs
ps.pack_no,
NVL(soh_pack.stock_on_hand, 0)      pack_soh,
ps.quantity                         qty_per_pack,
NVL(soh_pack.stock_on_hand, 0)

ps.quantity                     units_locked_in_packs,
-- Total theoretical (do NOT use for operational decisions without breakdown)
NVL(soh_comp.stock_on_hand, 0)

NVL(soh_pack.stock_on_hand, 0)

ps.quantity                     theoretical_total
FROM   packsku         ps
JOIN   item_master     im_comp  ON im_comp.item = ps.item
-- Component SOH at the target location
LEFT JOIN item_loc_soh soh_comp ON  soh_comp.item     = ps.item
AND soh_comp.loc      = :v_loc
AND soh_comp.loc_type = :v_loc_type
-- Pack SOH at the target location
LEFT JOIN item_loc_soh soh_pack ON  soh_pack.item     = ps.pack_no
AND soh_pack.loc      = :v_loc
AND soh_pack.loc_type = :v_loc_type
WHERE  ps.pack_no = :v_pack_item
ORDER BY ps.item;

4. Packs with missing PACKSKU rows — data integrity check

-- Pack items in ITEM_MASTER that have no component rows in PACKSKU
-- These are empty shells that will fail receiving and breakdown validation
SELECT im.item,
im.item_desc,
im.pack_ind,
im.simple_pack_ind,
im.orderable_ind,
im.sellable_ind,
im.status,
im.dept,
dp.dept_name
FROM   item_master im
JOIN   deps        dp ON dp.dept = im.dept
WHERE  im.pack_ind = 'Y'
AND  NOT EXISTS (
SELECT 1
FROM   packsku ps
WHERE  ps.pack_no = im.item
)
ORDER BY im.status, im.dept, im.item_desc;

5. PL/SQL — validate pack structure before activation

-- Validate a pack item has a complete and consistent PACKSKU structure
-- Returns an error message string, or 'OK' if all checks pass
FUNCTION validate_pack_structure (
p_pack_item IN item_master.item%TYPE
) RETURN VARCHAR2 IS
l_pack_ind        item_master.pack_ind%TYPE;
l_simple_pack_ind item_master.simple_pack_ind%TYPE;
l_component_count PLS_INTEGER;
l_zero_qty_count  PLS_INTEGER;
l_invalid_comp    PLS_INTEGER;
BEGIN
-- 1. Confirm item is flagged as a pack
SELECT pack_ind, simple_pack_ind
INTO   l_pack_ind, l_simple_pack_ind
FROM   item_master
WHERE  item = p_pack_item;
IF l_pack_ind != 'Y' THEN
RETURN 'NOT_A_PACK: PACK_IND is not Y for item ' || p_pack_item;
END IF;
-- 2. Must have at least one PACKSKU row
SELECT COUNT(1) INTO l_component_count
FROM   packsku WHERE pack_no = p_pack_item;
IF l_component_count = 0 THEN
RETURN 'NO_COMPONENTS: PACKSKU has no rows for pack ' || p_pack_item;
END IF;
-- 3. Simple pack must have exactly one component row
IF l_simple_pack_ind = 'Y' AND l_component_count > 1 THEN
RETURN 'SIMPLE_PACK_MULTI_COMP: Simple pack has '
|| l_component_count || ' components — must be 1';
END IF;
-- 4. No zero or negative quantities
SELECT COUNT(1) INTO l_zero_qty_count
FROM   packsku
WHERE  pack_no  = p_pack_item
AND   0>=quantity;
IF l_zero_qty_count > 0 THEN
RETURN 'ZERO_QTY: ' || l_zero_qty_count
|| ' component(s) have zero or negative quantity';
END IF;
-- 5. All component items must be Active and not themselves packs
SELECT COUNT(1) INTO l_invalid_comp
FROM   packsku     ps
JOIN   item_master im ON im.item = ps.item
WHERE  ps.pack_no  = p_pack_item
AND  (im.status != 'A' OR im.pack_ind = 'Y');
IF l_invalid_comp > 0 THEN
RETURN 'INVALID_COMPONENTS: ' || l_invalid_comp
|| ' component(s) are inactive or are themselves packs';
END IF;
RETURN 'OK';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'ITEM_NOT_FOUND: ' || p_pack_item || ' not in ITEM_MASTER';
WHEN OTHERS THEN
RETURN 'ERROR: ' || SQLERRM;
END validate_pack_structure;

Common Gotchas for Developers#

• !

  Summing pack SOH and component SOH to get total units. Pack SOH and component SOH are independent positions. A pack of 6 bottles with 10 units of pack SOH represents 60 bottles locked in pack format — not 10. Adding pack SOH directly to the component's loose SOH without multiplying by the pack quantity produces an understated total. Multiplying by quantity and adding to loose SOH gives a theoretical total — but it is only operationally available after a breakdown transaction.

• !

  Creating a pack item without any PACKSKU rows. A PACK_IND = 'Y' item with no rows in PACKSKU is an empty shell. It can be ranged and may even be activated without error depending on the RMS version. But when a purchase order receipt tries to process a breakdown, or when a replenishment calculation tries to evaluate the pack cost vs component cost, it will find no component structure and either throw an error or produce a zero result silently.

• !

  Using a pack item as a component of another pack. Oracle RMS does not support nested packs — a pack item inside another pack. Components referenced in PACKSKU must be regular items (PACK_IND = 'N'). Attempting to reference a pack as a component will either fail at the application validation layer or produce receiving and breakdown logic that cannot resolve the nested structure.

• !

  Ranging the pack but not ranging the components (or vice versa).

  For breakdown to work at a location, both the pack and its component items must have ITEM_LOC records at that location. A pack ranged to Store 100 whose component items are not ranged to Store 100 cannot have a breakdown processed at that store — the system has no ITEM_LOC_SOH record to credit the component inventory to. Always range pack and components together.

• !

  Setting QUANTITY = 0 on a PACKSKU row. A zero-quantity component is structurally invalid. The row exists in PACKSKU so the pack appears to have a component, but any calculation that uses the quantity — breakdown, cost calculation, theoretical availability — produces a zero result for that component. This is usually a data migration error. Always validate PACKSKU.QUANTITY 0 before activating a pack item.

• !

  Discontinuing a component item without checking which packs use it. When an item is being set to STATUS = 'D' or STATUS = 'I', any pack that contains it as a component is affected. If the component becomes unavailable, the pack cannot be properly received or broken down. Always run the "all packs containing a component" query (SQL example 2 above) before inactivating or deleting any item that might be a pack component.

• !

  Querying V_PACKSKU_QTY and expecting it to exist in all RMS versions. The view V_PACKSKU_QTY is available in most standard RMS installations but is a database view, not a base table. In some customised environments or older versions, this view may have been dropped or altered. If you get an ORA-00942 error referencing this view, fall back to querying PACKSKU directly with explicit joins to ITEM_MASTER and ITEM_DESC.

Key Takeaways#