[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* 1) Update event queue (incremental only) */
/* 2) Empty out existing table (all or queued supervisors) */
/* 3) End date chains for queued supervisors (incremental only) */
/* 4) Remove indexes (full refresh only) */
/* 5) Disable WHO trigger */
/* */
/* Main collection (multi-thread by person) */
/* ======================================== */
/* 1) Set first date to sample management chain as later of person hire */
/* or refresh from date */
/* 2) Loop through sample dates: */
/* i) Insert links in chain when there is a change in supervisor, */
/* assignment, levels or orphan status. */
/* ii) Retain the next sample date as the earliest date any link in */
/* the sampled chain has the next supervisor change event */
/* 3) Exit the loop when either: */
/* i) No data is found - person has been terminated on the previous date */
/* ii) Sample date hits end of time - no further changes */
/* 4) Ensure PL/SQL table of rows to insert is fully updated and execute */
/* the bulk insert */
/* */
/* Post-process (single-thread) */
/* ============================ */
/* 1) Recreate indexes (full refresh only) */
/* 2) Enable WHO trigger */
/* */
/* Data Structures */
/* =============== */
/* A chain cache table stores information about each link in the chain. It */
/* is indexed by link level. It should be well maintained (i.e. links are */
/* removed when no longer required). The person being processed will always */
/* be the last link in the chain. */
/* */
/* Error handling */
/* ============== */
/* */
/* Orphans (no exception raised) */
/* ----------------------------- */
/* If the management chain is sampled and it is found that the top manager */
/* has a supervisor assigned then the chain is said to be orphaned. */
/* */
/* If the supervisor of the top manager has been terminated, it is possible */
/* they may be re-hired. This should be taken into account when deciding */
/* which date to next sample the hierarchy. */
/* */
/* Loops (exception explicitly trapped) */
/* ------------------------------------ */
/* When a loop is encountered the person being processed is deemed an orphan */
/* (since no management chain can be found for them). */
/* */
/* They are then treated as an orphan but the chain resampled at regular */
/* intervals up to system date in case the data is fixed at a later date. */
/* */
/* Other errors (not trapped) */
/* -------------------------- */
/* Other errors are not handled. */
/******************************************************************************/
-- Information to be held for each link in a chain
TYPE g_link_record_type IS RECORD
(chain_id NUMBER
,person_id per_all_assignments_f.person_id%TYPE
,assignment_id per_all_assignments_f.assignment_id%TYPE
,business_group_id per_all_assignments_f.business_group_id%TYPE
,asg_status_type_id per_all_assignments_f.assignment_status_type_id%TYPE
,start_date DATE
,relative_level PLS_INTEGER
,orphan_flag VARCHAR2(30));
SELECT full_name
FROM per_people_x
WHERE person_id = p_person_id;
INSERT INTO hri_eq_wrkfc_evt
(assignment_id
,erlst_evnt_effective_date
,source_code)
SELECT /*+ ORDERED */
wevt.asg_assgnmnt_fk
,eq.erlst_evnt_effective_date
,'ASG_MGR_' || eq.source_code
FROM
hri_eq_sprvsr_hrchy_chgs eq
,hri_mb_wrkfc_evt_ct wevt
WHERE wevt.time_day_evt_end_fk >= eq.erlst_evnt_effective_date
AND wevt.per_person_mgr_fk = eq.person_id;
INSERT INTO hri_eq_wrkfc_mnth
(assignment_id
,erlst_evnt_effective_date
,source_code)
SELECT /*+ ORDERED */
wevt.asg_assgnmnt_fk
,eq.erlst_evnt_effective_date
,'ASG_MGR_' || eq.source_code
FROM
hri_eq_sprvsr_hrchy_chgs eq
,hri_mb_wrkfc_evt_ct wevt
WHERE wevt.time_day_evt_end_fk >= eq.erlst_evnt_effective_date
AND wevt.per_person_mgr_fk = eq.person_id;
INSERT INTO hri_eq_wrkfc_evt_mgrh
(sup_person_id
,erlst_evnt_effective_date
,source_code)
SELECT
person_id
,erlst_evnt_effective_date
,source_code
FROM hri_eq_sprvsr_hrchy_chgs;
USING (SELECT assignment_id,
erlst_evnt_effective_date,
'SUPERVISOR' source_type
FROM hri_eq_sprvsr_hrchy_chgs) sup_eq
ON ( delta_eq.source_type = 'SUPERVISOR'
AND sup_eq.assignment_id = delta_eq.source_id)
WHEN MATCHED THEN
UPDATE SET delta_eq.erlst_evnt_effective_date =
least(delta_eq.erlst_evnt_effective_date,sup_eq.erlst_evnt_effective_date)
WHEN NOT MATCHED THEN
INSERT (delta_eq.source_type,
delta_eq.source_id,
delta_eq.erlst_evnt_effective_date
)
VALUES (sup_eq.source_type,
sup_eq.assignment_id,
sup_eq.erlst_evnt_effective_date);
USING (SELECT person_id,
erlst_evnt_effective_date,
'SUPERVISOR' source_type
FROM hri_eq_sprvsr_hrchy_chgs) sup_eq
ON ( absnc_eq.source_type = 'SUPERVISOR'
AND sup_eq.person_id = absnc_eq.source_id)
WHEN MATCHED THEN
UPDATE SET absnc_eq.erlst_evnt_effective_date =
LEAST(absnc_eq.erlst_evnt_effective_date,
sup_eq.erlst_evnt_effective_date)
WHEN NOT MATCHED THEN
INSERT (absnc_eq.source_type,
absnc_eq.source_id,
absnc_eq.erlst_evnt_effective_date
)
VALUES (sup_eq.source_type,
sup_eq.person_id,
sup_eq.erlst_evnt_effective_date);
PROCEDURE recover_insert_rows IS
-- variables needed for populating the WHO columns
l_user_id NUMBER;
INSERT INTO hri_cs_suph
(sup_person_id
,sup_assignment_id
,sup_level
,sup_business_group_id
,sup_assignment_status_type_id
,sup_invalid_flag_code
,sup_sub1_mgr_person_fk
,sup_sub2_mgr_person_fk
,sup_sub3_mgr_person_fk
,sup_sub4_mgr_person_fk
,sub_person_id
,sub_assignment_id
,sub_level
,sub_relative_level
,sub_business_group_id
,sub_invalid_flag_code
,sub_primary_asg_flag_code
,orphan_flag_code
,sub_mngrsc_fk
,effective_start_date
,effective_end_date
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_suph_sup_psn_id(i)
,g_suph_sup_asg_id(i)
,g_suph_sup_level(i)
,g_suph_sup_bgr_id(i)
,g_suph_sup_ast_id(i)
,'N'
,g_suph_sup_sub1_psn_id(i)
,g_suph_sup_sub2_psn_id(i)
,g_suph_sup_sub3_psn_id(i)
,g_suph_sup_sub4_psn_id(i)
,g_suph_sub_psn_id(i)
,g_suph_sub_asg_id(i)
,g_suph_sub_level(i)
,g_suph_sub_rlt_lvl(i)
,g_suph_sub_bgr_id(i)
,'N'
,'Y'
,g_suph_orphan_flg(i)
,g_suph_sub_chain_id(i)
,g_suph_start_date(i)
,g_suph_end_date(i)
,g_current_time
,l_user_id
,l_user_id
,l_user_id
,g_current_time);
INSERT INTO hri_cs_mngrsc_ct
(mgrs_mngrsc_pk
,mgrs_person_fk
,mgrs_assignment_fk
,mgrs_date_start
,mgrs_date_end
,mgrs_level
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_chn_chain_id(i)
,g_chn_psn_id(i)
,g_chn_asg_id(i)
,g_chn_start_date(i)
,g_chn_end_date(i)
,g_chn_psn_lvl(i)
,g_current_time
,l_user_id
,l_user_id
,l_user_id
,g_current_time);
INSERT INTO hri_mdp_mgrh_transfers_ct
(mgr_sup_person_fk
,per_person_fk
,asg_assgnmnt_fk
,per_person_trn_fk
,time_day_evt_fk
,ptyp_wrktyp_fk
,transfer_in_ind
,transfer_out_ind
,direct_ind
,direct_record_ind
,sec_asg_ind
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_trn_sup_psn_id(i)
,g_trn_psn_id(i)
,g_trn_asg_id(i)
,g_trn_ref_id(i)
,g_trn_date(i)
,g_trn_wty_fk(i)
,g_trn_in_ind(i)
,g_trn_out_ind(i)
,g_trn_dir_ind(i)
,g_trn_dir_rec(i)
,g_trn_sec_asg_ind(i)
,g_current_time
,l_user_id
,l_user_id
,l_user_id
,g_current_time);
END recover_insert_rows;
PROCEDURE bulk_insert_rows IS
l_user_id NUMBER;
INSERT INTO hri_cs_suph
(sup_person_id
,sup_assignment_id
,sup_level
,sup_business_group_id
,sup_assignment_status_type_id
,sup_invalid_flag_code
,sup_sub1_mgr_person_fk
,sup_sub2_mgr_person_fk
,sup_sub3_mgr_person_fk
,sup_sub4_mgr_person_fk
,sub_person_id
,sub_assignment_id
,sub_level
,sub_relative_level
,sub_business_group_id
,sub_invalid_flag_code
,sub_primary_asg_flag_code
,orphan_flag_code
,sub_mngrsc_fk
,effective_start_date
,effective_end_date
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_suph_sup_psn_id(i)
,g_suph_sup_asg_id(i)
,g_suph_sup_level(i)
,g_suph_sup_bgr_id(i)
,g_suph_sup_ast_id(i)
,'N'
,g_suph_sup_sub1_psn_id(i)
,g_suph_sup_sub2_psn_id(i)
,g_suph_sup_sub3_psn_id(i)
,g_suph_sup_sub4_psn_id(i)
,g_suph_sub_psn_id(i)
,g_suph_sub_asg_id(i)
,g_suph_sub_level(i)
,g_suph_sub_rlt_lvl(i)
,g_suph_sub_bgr_id(i)
,'N'
,'Y'
,g_suph_orphan_flg(i)
,g_suph_sub_chain_id(i)
,g_suph_start_date(i)
,g_suph_end_date(i)
,g_current_time
,l_user_id
,l_user_id
,l_user_id
,g_current_time);
INSERT INTO hri_cs_mngrsc_ct
(mgrs_mngrsc_pk
,mgrs_person_fk
,mgrs_assignment_fk
,mgrs_date_start
,mgrs_date_end
,mgrs_level
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_chn_chain_id(i)
,g_chn_psn_id(i)
,g_chn_asg_id(i)
,g_chn_start_date(i)
,g_chn_end_date(i)
,g_chn_psn_lvl(i)
,g_current_time
,l_user_id
,l_user_id
,l_user_id
,g_current_time);
INSERT INTO hri_mdp_mgrh_transfers_ct
(mgr_sup_person_fk
,per_person_fk
,asg_assgnmnt_fk
,per_person_trn_fk
,time_day_evt_fk
,ptyp_wrktyp_fk
,transfer_in_ind
,transfer_out_ind
,direct_ind
,direct_record_ind
,sec_asg_ind
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
(g_trn_sup_psn_id(i)
,g_trn_psn_id(i)
,g_trn_asg_id(i)
,g_trn_ref_id(i)
,g_trn_date(i)
,g_trn_wty_fk(i)
,g_trn_in_ind(i)
,g_trn_out_ind(i)
,g_trn_dir_ind(i)
,g_trn_dir_rec(i)
,g_trn_sec_asg_ind(i)
,g_current_time
,l_user_id
,l_user_id
,l_user_id
,g_current_time);
recover_insert_rows;
END bulk_insert_rows;
PROCEDURE insert_row(p_supv_person_id IN NUMBER
,p_supv_assignment_id IN NUMBER
,p_supv_level IN NUMBER
,p_supv_business_group_id IN NUMBER
,p_supv_asg_status_type_id IN NUMBER
,p_supv_sub1_psn_id IN VARCHAR2
,p_supv_sub2_psn_id IN VARCHAR2
,p_supv_sub3_psn_id IN VARCHAR2
,p_supv_sub4_psn_id IN VARCHAR2
,p_sub_person_id IN NUMBER
,p_sub_assignment_id IN NUMBER
,p_sub_level IN NUMBER
,p_sub_relative_level IN NUMBER
,p_sub_business_group_id IN NUMBER
,p_effective_start_date IN DATE
,p_effective_end_date IN DATE
,p_orphan_flag IN VARCHAR2
,p_chain_id IN VARCHAR2) IS
BEGIN
-- increment the index
g_suph_row_count := g_suph_row_count + 1;
END insert_row;
PROCEDURE insert_chn_row(p_person_id IN NUMBER
,p_assignment_id IN NUMBER
,p_start_date IN DATE
,p_end_date IN DATE
,p_chain_id IN NUMBER
,p_person_level IN NUMBER) IS
l_user_id NUMBER;
END insert_chn_row;
PROCEDURE insert_trn_row(p_sup_person_id IN NUMBER
,p_trn_person_id IN NUMBER
,p_trn_assignment_id IN NUMBER
,p_ref_person_id IN NUMBER
,p_transfer_date IN DATE
,p_trn_wrktyp_fk IN VARCHAR2
,p_transfer_in_ind IN NUMBER
,p_transfer_out_ind IN NUMBER
,p_direct_ind IN NUMBER
,p_direct_rec IN NUMBER
,p_sec_asg_ind IN NUMBER) IS
BEGIN
-- Add row
g_trn_row_count := g_trn_row_count + 1;
END insert_trn_row;
PROCEDURE update_event_queue IS
BEGIN
-- 3667099 The events queue may contain records for events that have taken place
-- to assignment records which do not affect the supervisor hierarchy, for
-- example secondary assingments and non employee assingments.
-- Delete event queue records that are related to secondary assingments
-- ,non employee assignments and assignments that do not have any supervisor
-- 4186087 If a person is made a top supervisor or if a new top supervisor is
-- added the event should not be deleted, otherwise the person's record may
-- not be correct in the hiearchy.
-- Removed the condition (AND supervisor_id is not null) from the inner query
-- Delete records that are not primary employee assignment change events
DELETE /*+ PARALLEL(eq, default,default)*/ hri_eq_sprvsr_hrchy_chgs eq
WHERE assignment_id NOT IN
(SELECT assignment_id
FROM per_all_assignments_f asg
WHERE eq.assignment_id = asg.assignment_id
AND primary_flag = 'Y'
AND assignment_type IN ('E','C')
AND asg.effective_end_date >= eq.erlst_evnt_effective_date);
debug(sql%rowcount || ' records deleted from supervior events queue.');
UPDATE hri_eq_sprvsr_hrchy_chgs eq
SET person_id =
(SELECT person_id
FROM per_all_assignments_f asg
WHERE eq.assignment_id = asg.assignment_id
AND rownum = 1);
g_msg_sub_group := NVL(g_msg_sub_group, 'UPDATE_EVENT_QUEUE');
END update_event_queue;
PROCEDURE delete_and_end_date_suph_recs
(p_start_person_id IN NUMBER,
p_end_person_id IN NUMBER) IS
BEGIN
-- Delete chain updates after the date of refresh
DELETE FROM hri_cs_suph sph
WHERE sph.rowid IN
(SELECT sph2.rowid
FROM hri_eq_sprvsr_hrchy_chgs evt,
hri_cs_suph sph2
WHERE evt.person_id = sph2.sub_person_id
AND evt.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND evt.erlst_evnt_effective_date <= sph2.effective_start_date);
debug(sql%rowcount || ' supervisor hierarchy records deleted.');
DELETE FROM hri_cs_mngrsc_ct chn
WHERE chn.rowid IN
(SELECT chn2.rowid
FROM hri_eq_sprvsr_hrchy_chgs evt,
hri_cs_mngrsc_ct chn2
WHERE evt.person_id = chn2.mgrs_person_fk
AND evt.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND evt.erlst_evnt_effective_date <= chn2.mgrs_date_start);
debug(sql%rowcount || ' supervisor chain lookup records deleted.');
UPDATE hri_cs_suph sph
SET effective_end_date =
(SELECT (evt.erlst_evnt_effective_date - 1)
FROM hri_eq_sprvsr_hrchy_chgs evt
WHERE evt.person_id = sph.sub_person_id
AND evt.erlst_evnt_effective_date BETWEEN sph.effective_start_date
AND sph.effective_end_date)
,last_update_date = sysdate
WHERE (sph.sub_person_id,
sph.sup_person_id,
sph.effective_start_date) IN
(SELECT
sph2.sub_person_id,
sph2.sup_person_id,
sph2.effective_start_date
FROM hri_eq_sprvsr_hrchy_chgs evt,
hri_cs_suph sph2
WHERE evt.person_id = sph2.sub_person_id
AND evt.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND evt.erlst_evnt_effective_date BETWEEN sph2.effective_start_date
AND sph2.effective_end_date);
UPDATE hri_cs_mngrsc_ct chn
SET chn.mgrs_date_end =
(SELECT (evt.erlst_evnt_effective_date - 1)
FROM hri_eq_sprvsr_hrchy_chgs evt
WHERE evt.person_id = chn.mgrs_person_fk
AND evt.erlst_evnt_effective_date BETWEEN chn.mgrs_date_start
AND chn.mgrs_date_end)
,last_update_date = sysdate
WHERE chn.mgrs_mngrsc_pk IN
(SELECT
chn2.mgrs_mngrsc_pk
FROM hri_eq_sprvsr_hrchy_chgs evt,
hri_cs_mngrsc_ct chn2
WHERE evt.person_id = chn2.mgrs_person_fk
AND evt.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND evt.erlst_evnt_effective_date BETWEEN chn.mgrs_date_start
AND chn.mgrs_date_end);
END delete_and_end_date_suph_recs;
DELETE FROM hri_eq_sprvsr_hrchy_chgs evt
WHERE EXISTS
(SELECT 'x'
FROM hri_eq_sprvsr_hrchy_chgs evt2
WHERE evt2.person_id = evt.person_id
AND ((evt.erlst_evnt_effective_date = evt2.erlst_evnt_effective_date
AND evt.rowid < evt2.rowid)
OR
evt.erlst_evnt_effective_date > evt2.erlst_evnt_effective_date));
debug(sql%rowcount || ' duplicate records deleted.');
INSERT /*+ append */ INTO hri_eq_sprvsr_hrchy_chgs
(person_id
,assignment_id
,erlst_evnt_effective_date
,source_code)
SELECT
sph.sub_person_id
,sph.sub_assignment_id
,GREATEST(evt.erlst_evnt_effective_date,sph.effective_start_date)
,'DERIVED'
FROM
hri_eq_sprvsr_hrchy_chgs evt
,hri_cs_suph sph
WHERE sph.sup_person_id = evt.person_id
AND sph.sub_relative_level > 0
AND sph.effective_end_date >= evt.erlst_evnt_effective_date;
debug(sql%rowcount || ' subordinate records inserted.');
SELECT hri_cs_mngrsc_ct_s.nextval
INTO l_chain_id
FROM dual;
SELECT
sec_pre.person_id
,sec_pre.assignment_id
,CASE WHEN sec_pre.assignment_type = 'E'
THEN 'EMP'
ELSE 'CWK'
END ptyp_wrktyp_fk
FROM
per_all_assignments_f sec_pre
,per_all_assignments_f sec_post
WHERE sec_pre.supervisor_id = p_trn_psn_id
AND sec_post.assignment_id = sec_pre.assignment_id
AND sec_post.supervisor_id = sec_pre.supervisor_id
AND sec_post.primary_flag = 'N'
AND sec_pre.assignment_type IN ('E','C')
AND p_trn_date - 1 BETWEEN sec_pre.effective_start_date AND sec_pre.effective_end_date
AND p_trn_date BETWEEN sec_post.effective_start_date AND sec_post.effective_end_date;
insert_trn_row
(p_sup_person_id => l_idx
,p_trn_person_id => p_trn_psn_id
,p_trn_assignment_id => p_trn_asg_id
,p_ref_person_id => -1
,p_transfer_date => p_trn_date
,p_trn_wrktyp_fk => l_wrktyp_fk
,p_transfer_in_ind => l_transfer_in_ind
,p_transfer_out_ind => l_transfer_out_ind
,p_direct_ind => l_direct_ind
,p_direct_rec => 0
,p_sec_asg_ind => 0);
insert_trn_row
(p_sup_person_id => l_idx
,p_trn_person_id => l_sec_psn_tab(i)
,p_trn_assignment_id => l_sec_asg_tab(i)
,p_ref_person_id => p_trn_psn_id
,p_transfer_date => p_trn_date
,p_trn_wrktyp_fk => l_sec_wrktyp_tab(i)
,p_transfer_in_ind => l_transfer_in_ind
,p_transfer_out_ind => l_transfer_out_ind
,p_direct_ind => 0
,p_direct_rec => 0
,p_sec_asg_ind => 1);
insert_trn_row
(p_sup_person_id => l_idx
,p_trn_person_id => p_trn_psn_id
,p_trn_assignment_id => p_trn_asg_id
,p_ref_person_id => -1
,p_transfer_date => p_trn_date
,p_trn_wrktyp_fk => l_wrktyp_fk
,p_transfer_in_ind => 0
,p_transfer_out_ind => 1
,p_direct_ind => 1
,p_direct_rec => 1
,p_sec_asg_ind => 0);
insert_trn_row
(p_sup_person_id => l_idx
,p_trn_person_id => p_trn_psn_id
,p_trn_assignment_id => p_trn_asg_id
,p_ref_person_id => -1
,p_transfer_date => p_trn_date
,p_trn_wrktyp_fk => l_wrktyp_fk
,p_transfer_in_ind => 1
,p_transfer_out_ind => 0
,p_direct_ind => 1
,p_direct_rec => 1
,p_sec_asg_ind => 0);
insert_row
(p_supv_person_id => p_new_psn_tab(i)
,p_supv_assignment_id => p_new_asg_tab(i)
,p_supv_level => l_sup_level
,p_supv_business_group_id => p_new_bgr_tab(i)
,p_supv_asg_status_type_id => p_new_ast_tab(i)
,p_supv_sub1_psn_id => l_sup_sub1_psn_id
,p_supv_sub2_psn_id => l_sup_sub2_psn_id
,p_supv_sub3_psn_id => l_sup_sub3_psn_id
,p_supv_sub4_psn_id => l_sup_sub4_psn_id
,p_sub_person_id => p_new_psn_tab(1)
,p_sub_assignment_id => p_new_asg_tab(1)
,p_sub_level => l_sub_level
,p_sub_relative_level => l_sub_level - l_sup_level
,p_sub_business_group_id => p_new_bgr_tab(1)
,p_effective_start_date => p_loop_date
,p_effective_end_date => l_chain_end_date
,p_orphan_flag => p_orphan_flag
,p_chain_id => l_chain_id);
p_chain_table.DELETE(i);
insert_chn_row
(p_person_id => p_new_psn_tab(1)
,p_assignment_id => p_new_asg_tab(1)
,p_start_date => p_loop_date
,p_end_date => l_chain_end_date
,p_chain_id => l_chain_id
,p_person_level => l_sub_level);
SELECT
hier.person_id
,hier.assignment_id
,hier.business_group_id
,hier.supervisor_person_id
,hier.assignment_status_type_id
,hier.effective_end_date
,hier.assignment_type
FROM
(SELECT
ase.person_id
,ase.assignment_id
,ase.business_group_id
,ase.supervisor_person_id
,ase.assignment_status_type_id
,ase.effective_end_date
,ase.assignment_type
FROM
hri_cs_asgn_suph_events_ct ase
WHERE ase.primary_flag = 'Y'
AND v_effective_date BETWEEN ase.effective_start_date
AND ase.effective_end_date
) hier
START WITH hier.person_id = p_person_id
CONNECT BY hier.person_id = PRIOR hier.supervisor_person_id;
SELECT
ase.person_id
,ase.assignment_id
,ase.business_group_id
,ase.supervisor_person_id
,ase.assignment_status_type_id
,CASE WHEN v_effective_date >= ADD_MONTHS(TRUNC(SYSDATE), -1)
THEN ase.effective_end_date
ELSE LEAST(ase.effective_end_date, ADD_MONTHS(v_effective_date, 1))
END
,ase.assignment_type
FROM
hri_cs_asgn_suph_events_ct ase
WHERE ase.person_id = p_person_id
AND ase.primary_flag = 'Y'
AND v_effective_date BETWEEN ase.effective_start_date
AND ase.effective_end_date;
SELECT MIN(effective_start_date)
INTO l_loop_date
FROM hri_cs_asgn_suph_events_ct
WHERE person_id = p_person_id
AND primary_flag = 'Y';
SELECT
GREATEST(pos.date_start,
p_refresh_from_date) start_date
,LEAST(NVL(pos.actual_termination_date, g_end_of_time),
g_end_of_time) end_date
FROM
per_periods_of_service pos
WHERE pos.person_id = p_person_id
AND (p_refresh_from_date BETWEEN pos.date_start
AND NVL(pos.actual_termination_date, g_end_of_time)
OR pos.date_start > p_refresh_from_date)
UNION ALL
SELECT
GREATEST(pop.date_start,
p_refresh_from_date) start_date
,LEAST(NVL(pop.actual_termination_date, g_end_of_time),
g_end_of_time) end_date
FROM
per_periods_of_placement pop
WHERE pop.person_id = p_person_id
AND (p_refresh_from_date BETWEEN pop.date_start
AND NVL(pop.actual_termination_date, g_end_of_time)
OR pop.date_start > p_refresh_from_date);
SELECT DISTINCT
ase.person_id
FROM
hri_cs_asgn_suph_events_ct ase
WHERE ase.person_id BETWEEN p_start_object_id and p_end_object_id
AND ase.effective_end_date >= g_refresh_start_date;
SELECT DISTINCT
eq.person_id
,eq.erlst_evnt_effective_date change_date
FROM
hri_eq_sprvsr_hrchy_chgs eq
WHERE eq.person_id BETWEEN p_start_object_id and p_end_object_id;
delete_and_end_date_suph_recs
(p_start_person_id => p_start_object_id,
p_end_person_id => p_end_object_id);
hri_opl_wrkfc_trnsfr_events.delete_transfers_mgrh
(p_start_object_id => p_start_object_id,
p_end_object_id => p_end_object_id);
bulk_insert_rows;
insert_chn_row
(p_person_id => -1
,p_assignment_id => -1
,p_start_date => hr_general.start_of_time
,p_end_date => g_end_of_time
,p_chain_id => -1
,p_person_level => to_number(null));
bulk_insert_rows;
'SELECT DISTINCT person_id object_id
FROM hri_cs_asgn_suph_events_ct
ORDER BY person_id';
update_event_queue;
'SELECT person_id object_id
FROM hri_eq_sprvsr_hrchy_chgs
ORDER BY person_id';
SELECT
person_id
,erlst_evnt_effective_date start_date
FROM
hri_eq_sprvsr_hrchy_chgs;
SELECT DISTINCT person_id
FROM hri_cs_asgn_suph_events_ct
WHERE primary_flag = 'Y';
bulk_insert_rows;
bulk_insert_rows;
DELETE FROM hri_cs_suph
WHERE sub_person_id = p_person_id;
bulk_insert_rows;