COPY OF: BOM Bill of Material Structure

select
--  bom_bomrboms_xmlp_pkg.cf_org_nameformula(bbom.organization_id) assembly_organization,
  mif.item_number assembly,
--  bom_bomrboms_xmlp_pkg.cf_item_descformula(mif.item_id, bbom.organization_id) assembly_description,
  xxen_util.meaning(mif.bom_item_type,'BOM_ITEM_TYPE',700) bom_item_type,
  mlu.meaning engineering_bill,
  mif.primary_uom_code assembly_uom,
  bbom.alternate_bom_designator alt_bom_designator,
  bad.description alt_bom_designator_desc,
  rev.revision assembly_revision,
--  fnd_date.date_to_displaydt(:lp_revision_date) assembly_revision_date,
  (select
    listagg(mck.concatenated_segments,', ') within group (order by mck.concatenated_segments)
   from
    mtl_item_categories mic,
    mtl_categories_b_kfv mck
   where
       mic.category_set_id = :p_category_set_id
   and mic.organization_id = bbom.organization_id
   and mic.inventory_item_id = bbom.assembly_item_id
   and mck.category_id = mic.category_id
  ) category,
/*  (select
    substr(rtrim(xmlagg(
      xmlelement(name delem,bdde.element_name ||
                            nvl2(bom_bomrboms_xmlp_pkg.get_ele_desc(mif.bom_item_type, bdde.element_name, mif.item_catalog_group_id),': ' || bom_bomrboms_xmlp_pkg.get_ele_desc(mif.bom_item_type, bdde.element_name, mif.item_catalog_group_id),null)
                ,', ').extract('//text()') order by bdde.element_name).GetClobVal(),', '),1,4000)
   from
    bom_dependent_desc_elements bdde
   where
    bdde.bill_sequence_id=bet.top_bill_sequence_id
  ) descriptive_elements,*/
  xxen_util.meaning(bet.loop_flag,'SYS_YES_NO',700) assembly_has_loop,
  --
  to_char(bet.top_bill_sequence_id ) top_bill_sequence_id,
  mif.item_id assembly_item_id,
  bbom.organization_id assembly_organization_id,
  mif.item_catalog_group_id assembly_item_catalog_group_id
 from
  bom_explosion_temp bet,
  bom_bill_of_materials bbom,
  bom_lists blist,
  mtl_item_flexfields mif,
  mtl_item_revisions_b rev,
  mfg_lookups mlu,
  bom_alternate_designators bad
 where
      bet.group_id = :p_group_id
  and bet.plan_level = 0
  and bet.top_bill_sequence_id = bbom.bill_sequence_id
  and blist.sequence_id = :p_sequence_id1
  and blist.organization_id = bbom.organization_id
  and bbom.assembly_item_id = mif.item_id
  and mif.organization_id = blist.organization_id
  and ( (bbom.alternate_bom_designator is null and bad.organization_id = -1) or
        (bbom.alternate_bom_designator is not null and bbom.alternate_bom_designator = bad.alternate_designator_code and bad.organization_id = blist.organization_id)
      )
  and mlu.lookup_type = 'BOM_NO_YES'
  and mlu.lookup_code = bbom.assembly_type
  and rev.organization_id = blist.organization_id
  and rev.inventory_item_id = bbom.assembly_item_id
  and rev.effectivity_date <= :lp_revision_date
  and not exists
   (select
     'MAX REV DATE'
    from
     mtl_item_revisions_b rev2
    where
     rev2.inventory_item_id = bbom.assembly_item_id
     and rev2.organization_id = bbom.organization_id
     and rev2.effectivity_date > rev.effectivity_date
     and rev2.effectivity_date <= :lp_revision_date)
  and not exists
   (select
     'MAX REV'
    from
     mtl_item_revisions_b rev3
    where
     rev3.inventory_item_id = bbom.assembly_item_id
     and rev3.organization_id = bbom.organization_id
     and rev3.effectivity_date = rev.effectivity_date
     and rev3.revision > rev.revision)
  and (((:p_verify_flag is null) or (:p_verify_flag <> 1)) or ((:p_verify_flag = 1) and (bet.loop_flag = 1)))