Inventory Tables in Oracle Fusion

Hi friends, we are going to discuss about Inventory tables in Oracle Fusion. We will share some of the important Inventory tables which helps to store most useful inventory informations in oracle fusion. In this post , We will also share the inventory sql queries too which helps to extract the inventory informations in oracle fusion. As a Oracle Fusion consultant , we should have the knowledge about Inventory tables in oracle fusion and also which tables helps to store which part of informations in oracle fusion. In Oracle Fusion , Inventory tables are totally changed as compared to Oracle apps r12. In Oracle Fusion Inventory items tables starts with 'EGP%'. Please find below the complete detail about Most Important Inventory tables in Oracle Fusion.

Most Important Inventory tables in Oracle Fusion

Top 9 Inventory Tables in Oracle Fusion

1.EGP_SYSTEM_ITEMS

2.EGP_CATEGORY_SETS_VL

3.EGP_CATEGORIES_VL

4.EGP_ITEM_CATEGORIES

5.INV_ORG_PARAMETERS

6.EGP_ITEM_REVISIONS_B

7.EGP_ITEM_CAT_ASSIGNMENTS

8.INV_ONHAND_QUANTITIES_DETAIL

9.INV_RESERVATIONS

2 Important Inventory SQL Queries in Oracle Fusion

Here below is some of the useful inventory queries which helps to extract the inventory informations in oracle fusion. You can use these sql queries to develop the custom bip reports in oracle fusion.

Inventory Query 1:-

Select ESIB.ITEM_NUMBER, ESIT.DESCRIPTION, ESIB.PRIMARY_UOM_CODE, IR.RESERVATION_QUANTITY from

EGP_SYSTEM_ITEMS_B ESIB,

EGP_SYSTEM_ITEMS_TL ESIT,

INV_RESERVATIONS IR

where ESIB.INVENTORY_ITEM_ID=ESIT.INVENTORY_ITEM_ID

and ESIB.ORGANIZATION_ID=ESIT.ORGANIZATION_ID

and ESIT.LANGUAGE=USERENV.('LANG')

and IR.INVENTORY_ITEM_ID=ESIB.INVENTORY_ITEM_ID

and IR.ORGANIZATION_ID=ESIB.ORGANIZATION_ID

Inventory Query 2:-

SELECT ITEMS.ITEM_NUMBER,

ITEMCATS.category_id,

ITEMCATS.sequence_number,

ITEMCATS.alt_item_cat_code,

ECT.DESCRIPTION,

ECT.CATEGORY_NAME

FROM egp_item_cat_assignments ITEMCATS,

egp_category_sets_b CATSETS,

egp_system_items_b ITEMS,

inv_org_parameters ORG,
EGP_CATEGORIES_TL ECT

WHERE CATSETS.category_set_id = ITEMCATS.category_set_id

AND ITEMS.inventory_item_id = ITEMCATS.inventory_item_id

AND ORG.organization_id = ITEMS.organization_id

AND ITEMCATS.category_id=ECT.category_id

AND ( ( CATSETS.control_level = 1

AND ITEMCATS.organization_id = ORG.master_organization_id )

OR ( CATSETS.control_level = 2

AND ITEMCATS.organization_id = ORG.organization_id ) )