Item tables in oracle fusion

Item tables in oracle fusion

In this post , We will be discuss about the Item tables in oracle fusion. Item tables help to store the Inventory Item information in oracle fusion. We will get the Inventory Item Attributes values in item tables in oracle fusion. We have the item revision table , Item categories table which do store the Item related information in oracle fusion. Here below i will share the complete information about Item tables in oracle fusion.

7 Most Important Item tables in oracle fusion

1.EGP_SYSTEM_ITEMS_B
2.EGP_SYSTEM_ITEMS_TL
3.EGP_ITEM_REVISIONS_B 

4.egp_item_cat_assignments

5.egp_category_sets_b

6.egp_system_items_b

7.inv_org_parameters 
8.EGP_CATEGORIES_TL

Inventory Item Query using Item tables in oracle fusion

select ESIB.ITEM_NUMBER,ESIB.INVENTORY_ITEM_STATUS_CODE,ESIB.INVENTORY_ORGANIZATION_ID,
ESIT.DESCRIPTION,ESIB.MASTER_ORG_ID,
EIRB.REVISION,EIRB.REVISION_REASON
from EGP_SYSTEM_ITEMS_B ESIB,
 EGP_SYSTEM_ITEMS_TL ESIT,
EGP_ITEM_REVISIONS_B EIRB
WHERE ESIB.INVENTORY_ITEM_ID=ESIT.INVENTORY_ITEM_ID
AND ESIB.INVENTORY_ITEM_ID=EIRB.INVENTORY_ITEM_ID 

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 ) )