WITH go_up_in_chain
AS ( SELECT CASE WHEN CONNECT_BY_ISLEAF = 1 THEN msib1.inventory_item_id END AS from_item_id
            , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN msib1.segment1 END        AS from_item             
            , mri.related_item_id                                            AS to_item_id
            , msib2.segment1                                                 AS to_item
            , LEVEL                                                          AS priority
            , 'UP'                                                           AS direction
         FROM apps.mtl_related_items mri
            INNER JOIN apps.mtl_system_items_b msib1 ON mri.inventory_item_id = msib1.inventory_item_id
                                              AND mri.organization_id   = msib1.organization_id
            INNER JOIN apps.mtl_system_items_b msib2 ON mri.related_item_id   = msib2.inventory_item_id         
                                              AND mri.organization_id   = msib2.organization_id
            INNER JOIN apps.fnd_lookup_values flv    ON mri.relationship_type_id = TO_NUMBER(TO_CHAR(FLV.lookup_code))
                                                    AND flv.lookup_type   = 'MTL_RELATIONSHIP_TYPES'
                                                     AND flv.meaning LIKE 'MNAO%'
        WHERE msib1.organization_id = 85
          AND msib2.organization_id = 85                   
           AND SYSDATE BETWEEN NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1)                     
           AND CONNECT_BY_ISCYCLE = 0                                                                         
        START WITH msib2.segment1 = '057825130B'
          AND mri.attr_char1 IN ('AN')
           AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id
                                                                            , inventory_item_id 
                                                                        FROM mtl_related_items )
        CONNECT BY NOCYCLE PRIOR mri.inventory_item_id = mri.related_item_id 
          AND mri.attr_char1 IN ('AN')  
          AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id
                                                                            , inventory_item_id 
                                                                        FROM mtl_related_items )       
), go_down_in_chain 
AS ( SELECT CONNECT_BY_ROOT (msib1.inventory_item_id)        AS from_item_id
            , CONNECT_BY_ROOT (msib1.segment1)               AS from_item             
            , mri.related_item_id                            AS to_item_id
            , msib2.segment1                                 AS to_item
            , LEVEL                                          AS priority
            , 'DOWN'                                         AS direction
         FROM apps.mtl_related_items mri
            INNER JOIN apps.mtl_system_items_b msib1 ON mri.inventory_item_id = msib1.inventory_item_id
                                              AND mri.organization_id   = msib1.organization_id
            INNER JOIN apps.mtl_system_items_b msib2 ON mri.related_item_id   = msib2.inventory_item_id         
                                              AND mri.organization_id   = msib2.organization_id
            INNER JOIN apps.fnd_lookup_values flv    ON TO_NUMBER(TO_CHAR(FLV.lookup_code)) = mri.relationship_type_id
                                                    AND flv.lookup_type   = 'MTL_RELATIONSHIP_TYPES'
                                                    AND flv.meaning LIKE 'MNAO%'
        WHERE msib1.organization_id = 85
          AND msib2.organization_id = 85                   
           AND SYSDATE BETWEEN NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1)                     
           AND CONNECT_BY_ISCYCLE = 0                                                                         
        START WITH msib1.segment1 = '057825130B'                    
           AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id
                                                                            , inventory_item_id 
                                                                        FROM mtl_related_items )
        CONNECT BY NOCYCLE PRIOR flv.meaning != 'MNAO MKTG KITS'
          AND mri.inventory_item_id = PRIOR mri.related_item_id 
          AND PRIOR mri.attr_char1 IN ('A')          
          AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id
                                                                            , inventory_item_id 
                                                                        FROM mtl_related_items )       
), tree AS (
  SELECT * FROM go_down_in_chain
  UNION ALL
  SELECT * FROM go_up_in_chain
)
  SELECT  /*+ gather_plan_statistics */ FIRST_VALUE ( FROM_item ) OVER ( ORDER  BY CASE WHEN direction = 'UP' THEN 1 ELSE 2 END, 
                                                    CASE WHEN direction = 'UP' THEN - priority ELSE priority  END ) from_item
         , t.to_item                                                                                                to_item
  FROM   tree t
  ORDER BY CASE WHEN direction = 'UP' THEN 1 ELSE 2 END
          , CASE WHEN direction = 'UP' THEN - priority ELSE priority END;