Query to get subinventory details in Oracle Fusion

Query to get subinventory details in Oracle Fusion

Hi friends, we are going to discuss about the sql query to extract the subinventory details in oracle fusion. We will share the complete sql query using this we can extract the oracle inventory subinventory information's in oracle fusion. In oracle fusion , Subinventory tables are totally changed as compared to Oracle Apps. In oracle apps , subinventory tables do start with the MTL prefix but in Oracle fusion subinventory tables started with the INV prefix. Subinventory meaning in oracle fusion application is the store which helps to keep the item stock. Physically we have stores in our organization and these stores are equivalent to subinventory in oracle fusion application which helps to maintain our item stock. Using sub inventories we can keep track of our item stock and find out the current on hand stock for the inventory items in oracle fusion application. In this post , we will also share some of the most important tables too which helps to store the subinventory details in oracle fusion. We can use this below query to develop the oracle fusion inventory subinventory customer BIP reports . Please find below the complete detail about Query to get subinventory details in Oracle Fusion.

Query to get subinventory details in Oracle Fusion

2 Important Tables to get subinventory details in Oracle Fusion

Here below is the list of the subinventory tables which holds the subinventory data in oracle fusion.

1.inv_secondary_inventories

A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory. . Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. 

2.INV_SECONDARY_LOCATORS

Detail SQL Query to get subinventory details in Oracle Fusion

Here below is the detail sql query which helps to extract the complete subinventory information's in oracle fusion.

Here below is the complete sql query.

select sub.SECONDARY_INVENTORY_NAME "Subinventory Name",

sub.DESCRIPTION "Subinventory Description",

iop.organization_code "Invnetory Organization",

Decode(sub.ASSET_INVENTORY,1,’Y’,0,’N’) "Asset Subinventory",

Decode(sub.DEPRECIABLE_FLAG,1,’Y’,2,’N’) DEPRECIABLE ,

status.STATUS_CODE Material_status,

flv.meaning "Subinventory Type",

str_tl.name "Locator Name"

from inv_org_parameters iop,

fnd_kf_str_instances_b str_b,

fnd_kf_str_instances_tl str_tl,

inv_secondary_inventories sub,

inv_material_statuses_tl status,

fnd_lookup_values flv,

fnd_lookup_values flv1

where sub.secondary_inventory_name = :p_subinv_name

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

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 NVL(flv1.start_date_active, sysdate -1) <= SYSDATE AND NVL(flv1.end_date_active, sysdate +1) >= SYSDATE

AND NVL(flv.enabled_flag,'Y') = 'Y'

AND flv1.lookup_type = 'INV_SUB_LOCATOR_CONTROL'

AND flv1.lookup_code = sub.locator_type

Query to get subinventory details in Oracle Fusion