In this session, we are going to see how to fetch Oracle Cloud HCM Department changes using SQL
SQL Text:
with people_tbl_vw as (
select papf.person_id
,papf.person_number
,ppnf.display_name
from per_all_people_f papf
,per_person_names_f ppnf
where 1 = 1
and ppnf.person_id = papf.person_id
and ppnf.name_type = 'GLOBAL'
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between ppnf.effective_start_date and ppnf.effective_end_date
and (coalesce(null,:p_pers_num) is null or papf.person_id in (:p_pers_num))
)
,dep_chng_tbl_vw as (
select papf.person_number
,papf.display_name
,bus.name business_unit
,paam.effective_start_date
,to_char(paam.effective_start_date,'dd/Mon/yyyy','nls_date_language=english') effective_date
,dep.name new_value
,dep_prev.name old_value
from people_tbl_vw papf
,per_all_assignments_m paam
,per_all_assignments_m paam_prev
,hr_organization_v dep
,hr_organization_v dep_prev
,hr_organization_v bus
where 1 = 1
and paam.person_id = papf.person_id
and paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
and paam_prev.assignment_type = 'E'
and paam_prev.effective_latest_change = 'Y'
and paam_prev.assignment_id = paam.assignment_id
and paam_prev.effective_end_date + 1 = paam.effective_start_date
and paam_prev.organization_id <> paam.organization_id
and dep_prev.name <> dep.name
and dep.organization_id = paam.organization_id
and dep.classification_code = 'DEPARTMENT'
and dep.status = 'A'
and dep_prev.organization_id = paam_prev.organization_id
and dep_prev.classification_code = 'DEPARTMENT'
and dep_prev.status = 'A'
and bus.organization_id = paam.business_unit_id
and bus.classification_code = 'FUN_BUSINESS_UNIT'
and bus.status = 'A'
and paam.effective_start_date between dep.effective_start_date and dep.effective_end_date
and paam_prev.effective_start_date between dep_prev.effective_start_date and dep_prev.effective_end_date
and (coalesce(null,:p_bu) is null or paam.business_unit_id in (:p_bu))
)
select *
from dep_chng_tbl_vw
where 1 = 1
and effective_start_date between :p_st_dt and :p_ed_dt
order by person_number,to_number(to_char(effective_start_date,'yyyymmdd'))
Comments
Post a Comment