Oracle Ebs 多阶BOM展开

SELECT CONNECT_BY_ROOT(PARENT_ITEM_ID) TOP_ITEM_ID,
       CONNECT_BY_ROOT(PARENT_ITEM) TOP_ITEM,
       CHILD_ITEM,
       LEVEL
  FROM (SELECT MSI1.INVENTORY_ITEM_ID PARENT_ITEM_ID,
               MSI1.SEGMENT1          PARENT_ITEM,
               MSI2.SEGMENT1          CHILD_ITEM,
               BBOM.ASSEMBLY_TYPE
          FROM BOM_BILL_OF_MATERIALS        BBOM,
               BOM_INVENTORY_COMPONENTS     BIC,
               MTL_SYSTEM_ITEMS             MSI1,
               ORG_ORGANIZATION_DEFINITIONS ORG1,
               MTL_SYSTEM_ITEMS             MSI2,
               FND_LOOKUP_VALUES_VL         FLVV
         WHERE BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
           AND BBOM.ASSEMBLY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
           AND BBOM.ORGANIZATION_ID = MSI1.ORGANIZATION_ID
           AND BBOM.ORGANIZATION_ID = ORG1.ORGANIZATION_ID
           AND BIC.COMPONENT_ITEM_ID = MSI2.INVENTORY_ITEM_ID
           AND BIC.PK2_VALUE = MSI2.ORGANIZATION_ID
           AND BIC.WIP_SUPPLY_TYPE = FLVV.LOOKUP_CODE(+)
           AND NVL(BIC.DISABLE_DATE, SYSDATE + 1) > SYSDATE
           AND BIC.EFFECTIVITY_DATE <= SYSDATE
           AND FLVV.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
           AND ORG1.ORGANIZATION_ID = P_ORGANIZATION_ID
           AND BBOM.ALTERNATE_BOM_DESIGNATOR IS NULL
           AND BIC.IMPLEMENTATION_DATE IS NOT NULL
           AND MSI1.ITEM_TYPE NOT IN ('AOC', 'ATOA', 'ATOB'))
 WHERE CONNECT_BY_ROOT(ASSEMBLY_TYPE) = 1
   AND CONNECT_BY_ROOT(PARENT_ITEM) = 'item_num'
CONNECT BY NOCYCLE PRIOR CHILD_ITEM = PARENT_ITEM
 START WITH PARENT_ITEM = 'item_num'