Skip to content

Instantly share code, notes, and snippets.

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

Embed
What would you like to do?
Org Hierarchy #ebs
select haou.date_from,
haou.attribute1 || ' ' || haou.name,
haou.organization_id,
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
,haou.date_from
,haou.date_to
,haou.attribute1
,haou.name
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.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.