Saturday, September 3, 2016

Oracle BOM Query

select
   level lev
   ,LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(assembly_item, '/')||'/'||x.component_item path 
   ,x.*
from (
  select
    mp.organization_code
  , ai.item_number assembly_item
  , c.item_num
  , c.operation_seq_num
  , ci.item_number component_item
  , b.bill_sequence_id
  , b.common_bill_sequence_id
  , b.organization_id
  , nvl(b.common_assembly_item_id,b.assembly_item_id) assembly_item_id
  , c.component_item_id
  , c.component_sequence_id
  , b.alternate_bom_designator
  , to_char(c.effectivity_date, 'DD-MON-YYYY HH24:MI:SS') effectivity_date
  , to_char(c.implementation_date, 'DD-MON-YYYY HH24:MI:SS') implementation_date
  , to_char(c.disable_date, 'DD-MON-YYYY HH24:MI:SS') disable_date
 FROM
    bom_bill_of_materials b
  , bom_inventory_components c
  , mtl_item_flexfields ai
  , mtl_item_flexfields ci
  , mtl_parameters mp

 WHERE
   -- bill/component
   nvl(b.common_bill_sequence_id,b.bill_sequence_id) = c.bill_sequence_id
   -- parent item
   and b.assembly_item_id = ai.inventory_item_id
   and b.organization_id = ai.organization_id
   -- component item
   and c.component_item_id = ci.inventory_item_id
   and b.organization_id = ci.organization_id
   -- org name
   and b.organization_id = mp.organization_id
   and mp.organization_code = '&org'
  ) x
   start with x.assembly_item = '&item' and  nvl(x.alternate_bom_designator,'~NULL~')='~NULL~'
   connect by prior x.component_item_id = x.assembly_item_id
         and nvl(x.alternate_bom_designator,'~NULL~')='~NULL~'
         and level <= &levels

   order siblings by x.operation_seq_num, x.item_num

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect