Skip to content

Instantly share code, notes, and snippets.

@johnboychan johnboychan/Org Hierarchy
Last active Sep 22, 2018

What would you like to do?
Org Hierarchy #ebs
select haou.date_from,
haou.attribute1 || ' ' ||,
uoepp.uoe_functions.uoe_get_college_org_code(haou.organization_id) L3,
uoepp.uoe_functions.uoe_get_school_org_code(haou.organization_id) L4,
(select listagg(organization_id, ',') within group (order by haou1.organization_id )from hr_all_organization_units haou1
where haou1.attribute1 = haou.attribute1)
from hr_all_organization_units haou
where date_from = '01-AUG-2018 00.00.00'
order by uoepp.uoe_functions.uoe_get_college_org_code(haou.organization_id), uoepp.uoe_functions.uoe_get_school_org_code(haou.organization_id);
select * from hr_all_organization_units where attribute1 = 'P7V'
-- get details of which departtments have been re-opended as it will cause issues with the UOE_FUNCTIONS to get college school code and names
select haou.organization_id
from hr_all_organization_units haou
where 1 = 1
and haou.date_to is null
and haou.attribute1 is not null
and haou.organization_id <> (select max(haou2.organization_id)
from hr_all_organization_units haou2
where haou2.attribute1 = haou.attribute1
order by haou.date_from desc;
select * from hr_all_organization_units where attribute1 = 'D412'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.