Inventory tables and Joins in oracle apps r12

Inventory tables and Joins in oracle apps r12

In this post , We will be discuss about the Inventory tables and Joins in oracle apps r12.

 I will share some of the most important inventory tables which helps to store the oracle inventory informations in oracle apps r12. These inventory tables will related to Inventory Items , Inventory Item Categories , Item templates and Item sub inventories. Here below , We will share all these inventory tables and joins in oracle apps r12.

Most Important Inventory tables and Joins in oracle apps r12

1.mtl_category_sets_tl

2.mtl_categories_b_kfv

3.mtl_categories_v

4.mtl_secondary_inventories_fk_v

5.mtl_item_locations_kfv

6. mtl_item_templates

7.mtl_system_items_b

8. mtl_item_sub_defaults

Detail Explanation of Inventory tables and Joins in oracle apps r12

mtl_category_sets_tl

In this table you will get the Item Categories Set Name and its information’s.

mtl_categories_b_kfv

In this table you will the Item Categories Key Flex Values.

mtl_categories_v

In this table you will the Item Category Name.

mtl_secondary_inventories_fk_v

In this table, you will the get the Inventory Sub Inventories Information’s. This is the complete SubInventory Table.

mtl_item_locations_kfv

 In this table , you will get the SubInventory Locators Information’s. This is the Complete Item SubInventory Locator Information table.

 mtl_item_templates

 In this table, you will get the Inventory Item template Information’s. This template assigns to the Items. 

 mtl_system_items_b

 This is the complete Item master table in Oracle Apps.

 mtl_item_sub_defaults

  In this table , you will get the Subinvnetory Information’s which is assigned to the items. Here you will get the details of Item assigned to which sub-inventories

SQL Query Using The Inventory Tables and Joins in oracle apps r12

SELECT msi.segment1, msi.description, msi.primary_uom_code,

msi.cycle_count_enabled_flag, msi.inspection_required_flag,

msi.attribute14 legacy_code, msi.attribute13 legacy_description,

msi.stock_enabled_flag stockable,

msi.mtl_transactions_enabled_flag transactable, msi.lot_control_code,

msi.location_control_code, msi.purchasing_enabled_flag,

msi.must_use_approved_vendor_flag, msi.buyer_id,pap.full_name buyer_name,

msi.list_price_per_unit,

(msi.expense_account) expense_account,

msi.receiving_routing_id, misi.secondary_inventory subinventory,

mil.segment1 loc_seg1,mil.segment2 loc_seg2 ,ood.organization_name,

mcv.segment1 cat_seg1, mcv.segment2 cat_seg2, mcv.segment3 cat_seg3,

mcv.segment4 cat_seg4

FROM apps.mtl_system_items msi,

apps.mtl_secondary_locators msl,

apps.mtl_item_locations mil,

apps.per_all_people_f pap,

apps.mtl_item_sub_inventories misi,

apps.org_organization_definitions ood,

apps.mtl_item_categories mic,

apps.mtl_categories_vl mcv,

apps.mtl_category_sets mcs

WHERE msi.inventory_item_id = msl.inventory_item_id(+)

AND msi.organization_id = msl.organization_id(+)

AND msl.secondary_locator = mil.inventory_location_id(+)

AND msl.organization_id = mil.organization_id(+)

AND pap.person_id(+) = msi.buyer_id

AND msi.inventory_item_id = misi.inventory_item_id(+)

AND msi.organization_id = misi.organization_id(+)

AND ood.organization_id = msi.organization_id

AND mic.inventory_item_id = msi.inventory_item_id

AND mic.organization_id = msi.organization_id

AND mcs.category_set_id = mic.category_set_id

AND mcs.structure_id = mcv.structure_id

AND mcv.category_id = mic.category_id

AND msi.inventory_item_status_code = 'Active'

Click To Know More