1,391 total views
In this post,we will try to see how to create a flattened tree query from the hierarchy trees available (org tree/position tree).
First we will fetch the Org and Parent Org from the tree node tables and then depending upon the number of levels that the customer is using, we will have to left outer join the same temporary table that many number of times and select the org and parent org from those tables.
WITH ORG_TREE AS ( SELECT /*+ materialize */ DISTINCT * FROM ( SELECT ( SELECT p.name FROM hr_all_organization_units_f_vl p WHERE p.organization_id = ot.parent_organization_id AND rownum = 1 ) parent_org_name ,( SELECT c.name FROM hr_all_organization_units_f_vl c WHERE c.organization_id = ot.organization_id AND rownum = 1 ) org_name ,( SELECT c.attribute_number1 FROM hr_all_organization_units_f_vl c WHERE c.organization_id = ot.organization_id AND rownum = 1 ) org_legacy ,ot.tree_structure_code ,ot.parent_organization_id parent_org_id ,ot.organization_id org_id ,LEVEL levelcount FROM per_org_tree_node_v ot WHERE ot.tree_structure_code = 'PER_ORG_TREE_STRUCTURE' AND ot.tree_code = 'LPER_ORG_TREE' START WITH ot.parent_organization_id IS NULL CONNECT BY PRIOR ot.organization_id = ot.parent_organization_id ) ORDER BY levelcount ASC ) ,flattened_tree AS ( SELECT /*+ materialize */ lev01.ORG_NAME LVL1_ORG ,lev02.ORG_NAME LVL2_ORG ,lev03.ORG_NAME LVL3_ORG ,lev04.ORG_NAME LVL4_ORG ,lev05.ORG_NAME LVL5_ORG ,lev06.ORG_NAME LVL6_ORG ,lev07.ORG_NAME LVL7_ORG FROM ORG_TREE lev01 LEFT OUTER JOIN ORG_TREE lev02 ON lev01.org_id = lev02.parent_org_id LEFT OUTER JOIN ORG_TREE lev03 ON lev02.org_id = lev03.parent_org_id LEFT OUTER JOIN ORG_TREE lev04 ON lev03.org_id = lev04.parent_org_id LEFT OUTER JOIN ORG_TREE lev05 ON lev04.org_id = lev05.parent_org_id LEFT OUTER JOIN ORG_TREE lev06 ON lev05.org_id = lev06.parent_org_id LEFT OUTER JOIN ORG_TREE lev07 ON lev06.org_id = lev07.parent_org_id WHERE lev01.PARENT_ORG_NAME IS NULL ) SELECT * FROM flattened_tree
If you have any questions, please feel free to reach out to me by posting in comments section.
If you are interested in learning Fusion Technical tools go through this post
If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020