BOM Tables in Oracle Fusion

Hi friends, We are going to discuss about the BOM Tables in Oracle Fusion. We will share some of the important BOM tables which helps to store the BOM & BOM components details and the BOM routing details in oracle fusion. Using this BOM tables we do write the sql queries which helps to extract the Inventory BOM related data in oracle fusion and helps to develop the Custom BIP BOM reports in oracle fusion. In Oracle Fusion, BOM tables structure have been changed as compared to Oracle apps r12 BOM tables. We will also share some of the important BOM Related SQL queries which helps to get the BOM related data in oracle fusion. Please find below the complete detail about BOM Tables in Oracle Fusion.

BOM Tables in Oracle Fusion

Top 9 BOM Tables in Oracle Fusion.

4 BOM Tables:-

1.EGP_COMPONENTS_B 

2.EGP_STRUCTURES_B 

3.EGP_SYSTEM_ITEMS_B 

4. EGP_SYSTEM_ITEMS_B

5 BOM Routing Tables in Oracle Fusion:

5.WIS_WORK_DEFINITIONS_V
6.WIS_WD_OPERATIONS_V
7.WIS_WD_OPERATION_RESOURCES_V
8.INV_ORGANIZATION_DEFINITIONS_V
10.WIS_WD_OPERATION_MATERIALS_V

Detail SQL Query to Extract BOM Details in Oracle fusion using BOM Tables

Here below is the bom query which helps to share the bom and its components details in oracle fusion. Using this sql query , we can develop the Custom BOM BIP reports in oracle fusion..

SELECT

  CONNECT_BY_ROOT q_bom.assembly_num  root_assembly,

  q_bom.assembly_num,  q_bom.component_num,

  SUBSTR(SYS_CONNECT_BY_PATH(q_bom.assembly_Num, ' <-- '),5) assembly_path,

q_bom.qty,

  LEVEL  bom_level,  CONNECT_BY_ISCYCLE is_cycle

FROM

  (SELECT  mb1.item_number assembly_num, mb2.item_number  component_num, bc.component_quantity qty

   FROM  EGP_COMPONENTS_B  bc,

          EGP_SYSTEM_ITEMS_B  mb1,

          EGP_SYSTEM_ITEMS_B mb2,

         EGP_STRUCTURES_B  bs

   WHERE  bs.PK1_VALUE = mb1.inventory_item_id

   AND    bc.PK1_VALUE = mb2.inventory_item_id

   AND    bs.PK2_Value = mb2.organization_id

   AND    mb1.organization_id  = :P_INV_ORG_ID

   AND    bc.bill_sequence_id = bs.bill_sequence_id

   AND    mb1.organization_id = mb2.organization_id

  ) q_bom

START WITH  q_bom.assembly_Num = :P_BOM_ITEM

CONNECT BY NOCYCLE PRIOR q_bom.component_num = q_bom.assembly_num

ORDER SIBLINGS BY q_bom.assembly_Num;

BOM Tables in Oracle Fusion

BOM Tables in Oracle Fusion