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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM