BI Report, Business Intelligence Report,, Enterprise structure, Oracle Fusion

Subinventory Query in Oracle Fusion

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

Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. Try them only when nothing else works.. kind of SOS...
https://www.amazon.in/gp/product/B093CC1CLD?ie=UTF8&tag=oraclebytes-21&camp=3638&linkCode=xm2&creativeASIN=B093CC1CLD

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

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?