EGP tables in oracle fusion

Hi friends, we are going to discuss about the EGP tables in oracle fusion. We will share some of the important EGP tables in oracle fusion which helps to store different types of Oracle Inventory information's in oracle fusion. In Oracle fusion , Inventory tables do starts with prefix 'EGP'. We do call the inventory tables as EGP tables. EGP tables helps to stores the Oracle Inventory Master and transactions details in oracle fusion. Using EGP tables , we do develop the inventory sql queries which helps to extract the inventory informations from system. Using EGP tables , we do create the custom BIP reports for inventory in oracle fusion. We will also share some of the important EGP tables sql Queries which helps to extract the different types of Inventory informations in oracle fusion. Please find below the complete detail about EGP tables in oracle fusion.

EGP tables in oracle fusion

6 Top EGP Tables in oracle Fusion

1.EGP_SYSTEM_ITEMS_B

2.EGP_SYSTEM_ITEMS_TL

3.EGP_ITEM_CAT_ASSIGNMENTS

4.EGP_ITEM_REVISIONS_B 

5.EGP_CATEGORY_SETS_B 

6.EGP_CATEGORIES_TL

4 Inventory Item Onhand Tables in Oracle Fusion

1.INV_ONHAND_QUANTITIES_DETAIL

2.INV_UNITS_OF_MEASURE_TL

3.EGP_SYSTEM_ITEMS

4.INV_ORGANIZATION_DEFINITIONS_V

EGP Tables SQL Queries in Oracle Fusion

Here below is some of the important EGP tables sql queries which helps to share the Oracle Fusion inventory module information's in oracle fusion.

EGP SQL 1:- 

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

EGP SQL 2:-

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 

EGP SQL 3:-

select   IODV.ORGANIZATION_NAME " Inventory Organization",

    IOP.ORGANIZATION_CODE " Inventory Org Code" ,

ESI.ITEM_NUMBER "Item Code" ,

  ESI.DESCRIPTION  "Item Description",

  UOMT.UNIT_OF_MEASURE  "Item UOM",

  SUM(IOQD.TRANSACTION_QUANTITY) "Onhand Qty",

  IOQD.SUBINVENTORY_CODE "Item Onhand Subinventory" ,

  IOQD.LOT_NUMBER "Item Onhand Lot" 

from INV_ONHAND_QUANTITIES_DETAIL IOQD ,

  INV_UNITS_OF_MEASURE_TL UOMT ,

  INV_UNITS_OF_MEASURE_B UOMB,

  INV_ORG_PARAMETERS IOP ,

  EGP_SYSTEM_ITEMS ESI ,

  INV_ORGANIZATION_DEFINITIONS_V IODV

where 1 = 1

and IODV.ORGANIZATION_ID    =IOP.ORGANIZATION_ID

and IOQD.INVENTORY_ITEM_ID  = ESI.INVENTORY_ITEM_ID

and IOQD.ORGANIZATION_ID    = ESI.ORGANIZATION_ID

and ESI.ORGANIZATION_ID     = IOP.ORGANIZATION_ID

and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID

and UOMB.UOM_CODE           = IOQD.TRANSACTION_UOM_CODE

and ESI.ITEM_NUMBER=:P_ITEM_CODE

and IOP.ORGANIZATION_ID=89

group by ESI.ITEM_NUMBER ,

  IODV.ORGANIZATION_NAME,

  ESI.DESCRIPTION ,

  IOP.ORGANIZATION_CODE ,

  UOMT.UNIT_OF_MEASURE ,

  IOQD.SUBINVENTORY_CODE ,

  IOQD.LOT_NUMBER

order by ESI.ITEM_NUMBER

EGP tables in oracle fusion

EGP tables in oracle fusion