BOM QUERY IN ORACLE FUSION

Hi Friends, We are going to discuss about the bom query in oracle fusion. We will share the details sql query which helps to extract the complete inventory bom related information’s in oracle fusion. Using this sql query we will be able to get the complete bom components details including Sub-assemblies and their sub components. The best part of this bom query is this , we can get the complete bom components and if any bom components is itself a BOM or sub assembly itself then it also helps to extract the assembly components details too so by this way this BOM query is really a awesome. In this post , We will also try to share some of important BOM tables too which helps to write the BOM query in oracle fusion. This is one of the important Inventory BOM related query in oracle fusion. Please find below the complete detail about bom query in oracle fusion.

BOM QUERY IN ORACLE FUSION

TOP 4 BOM tables in Oracle Fusion

1.EGP_COMPONENTS_B 

2.EGP_STRUCTURES_B 

3.EGP_SYSTEM_ITEMS_B 

4.EGP_SYSTEM_ITEMS_B

Important BOM SQL query in Oracle Fusion

Here below is the detail sql query which helps to extract the complete bom components details in oracle fusion.

 

SELECT

  CONNECT_BY_ROOT q_bom.assembly_num  root_assembly,

  q_bom.assembly_num,  q_bom.component_num, q_bom.qty,

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

  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_STRUCTURES_B  bs,

          EGP_SYSTEM_ITEMS_B  mb1,

          EGP_SYSTEM_ITEMS_B mb2

   WHERE  bs.PK1_VALUE = mb1.inventory_item_id

   AND    bc.PK1_VALUE = mb2.inventory_item_id

   AND    bc.bill_sequence_id = bs.bill_sequence_id

   AND    mb1.organization_id = mb2.organization_id

   AND    bs.PK2_Value = mb2.organization_id

   AND    mb1.organization_id  = :P_INV_ORG_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 QUERY IN ORACLE FUSION

BOM QUERY IN ORACLE FUSION