Subinventory are physical location designated to store the items. And each subinv is connected to only one inventory. Base table to hold the data is inv_secondary_inventories. point to note is that most of the time fusion uses subinventory name and not the id.
Now since you are interested in Subinventory related info, may be following posts can help you:
INV_SECONDARY_INVENTORIES: Subinventory Table structure, Indexes & Constraints
Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory
Fusion Inventory Table – inv_org_parameters
Column Details of inv_org_parameters Table
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Query to fetch the subinventory details is:
This query will return you following columns
1. Subinv Name
2. Inventory Org Name
3. Subinv Description
4. Picking Order
5. Asset Subinv
6. Inventory Type
7. Locator Control
8. Locator Structure
select sub.SECONDARY_INVENTORY_NAME Sub,
iop.organization_code invntory,
sub.DESCRIPTION,
sub.PICKING_ORDER,
Decode(sub.ASSET_INVENTORY,1,’Y’,0,’N’) ASSET_subINVENTORY,
status.STATUS_CODE Material_status,
Decode(sub.DEPRECIABLE_FLAG,1,’Y’,2,’N’) DEPRECIABLE ,
flv.meaning subINVentory_type,
flv1.MEANING Locator_Control,
str_tl.name Locator_structure
from inv_secondary_inventories sub,
inv_material_statuses_tl status,
fnd_lookup_values flv,
fnd_lookup_values flv1,
inv_org_parameters iop,
fnd_kf_str_instances_b str_b,
fnd_kf_str_instances_tl str_tl
where 1=1
AND sub.secondary_inventory_name = :Sub_inv_name
AND sub.status_id = status.status_id
AND flv.lookup_type = ‘INV_SUB_TYPE’
AND flv.lookup_code = sub.subINVentory_type
AND NVL(flv.start_date_active, sysdate -1) <= SYSDATE AND NVL(flv.end_date_active, sysdate +1) >= SYSDATE
AND flv.enabled_flag = ‘Y’
AND flv1.lookup_type = ‘INV_SUB_LOCATOR_CONTROL’
AND flv1.lookup_code = sub.locator_type
AND NVL(flv1.start_date_active, sysdate -1) <= SYSDATE AND NVL(flv1.end_date_active, sysdate +1) >= SYSDATE
AND flv1.enabled_flag = ‘Y’
AND iop.organization_id = sub.organization_id
ANd str_b.structure_instance_number = sub.structure_instance_number
AND str_tl.structure_instance_code = str_b.structure_instance_code
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com