Query to Get Purchase Category hierarchy In oracle Fusion

 SELECT (SELECT category_name

                 FROM   por_browse_categories_tl pbct

                 WHERE  pbct.category_id = picpl.level1_cat_id) Level_1_Name,

                (SELECT category_name

                 FROM   por_browse_categories_tl pbct

                 WHERE  pbct.category_id = picpl.level2_cat_id) Level_2_Name,

                (SELECT category_name

                 FROM   por_browse_categories_tl pbct

                 WHERE  pbct.category_id = picpl.level3_cat_id) Level_3_Name

FROM   por_item_cat_parent_levels picpl

WHERE  picpl.type = 'SOURCING'

ORDER  BY level_1_name,

          level_2_name,

          level_3_name