Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Org Hierarchy
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