Oracle EBS: 展BOM之SQL (II)

    SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
           LEVEL component_level,
           c.component_item_id,
           a.organization_id,
           c.item_num assm_item_num,
           c.operation_seq_num assm_operation_seq_num,
           c.effectivity_date assm_effectivity_date,
           c.disable_date assm_disable_date,
           c.component_quantity assm_component_quantity,
           (SELECT MAX (revision)
              FROM mtl_item_revisions
             WHERE     organization_id = 318
                   AND effectivity_date <= SYSDATE
                   AND inventory_item_id = c.component_item_id)
              assm_item_rev
      FROM (SELECT *
              FROM apps.bom_inventory_components
             WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                       AND TRUNC (NVL (disable_date, SYSDATE + 1))) c,
           (SELECT *
              FROM apps.bom_bill_of_materials
             WHERE alternate_bom_designator IS NULL
               AND organization_id = 318) a
     WHERE 1 = 1 AND a.bill_sequence_id = c.bill_sequence_id
START WITH a.assembly_item_id = 5435108   --P_ITEM_ID
CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;


Ref:
BOM Bill of Materials explosion query in BOM Oracle Apps
https://sivakandigatla.blogspot.com/2015/08/bom-bill-of-materials-explosion-query.html