Script to count LPNs by context in Oracle Apps 11i

Need to count how many LPNs are in each context for a given organization in Oracle E-Business Suite 11i? Here’s a quick script:

select 
  count(*), 
  lpn_context,
  decode(lpn_context,
    '1', 'Resides in Inventory',
    '2', 'Resides in WIP',
    '3', 'Resides in Receiving',
    '4', 'Issued out of Stores',
    '5', 'Defined but not used',
    '6', 'Resides in Intransit',
    '7', 'Resides in Vendor',
    '8', 'Packing Context',
    '9', 'Loaded to Dock',
    '10', 'Prepack for WIP',
    '11', 'Picked') as context
from 
  wms_license_plate_numbers
where
  organization_id = &org_id
group by lpn_context 
order by lpn_context

 

Reference

What Are The Different Status Of A LPN And What Does Each Context Mean ? [ID 437902.1]