[Home] [Help]
254: wevt.asg_assgnmnt_fk
255: ,eq.erlst_evnt_effective_date
256: ,'ASG_MGR_' || eq.source_code
257: FROM
258: hri_eq_sprvsr_hrchy_chgs eq
259: ,hri_mb_wrkfc_evt_ct wevt
260: WHERE wevt.time_day_evt_end_fk >= eq.erlst_evnt_effective_date
261: AND wevt.per_person_mgr_fk = eq.person_id;
262:
268: wevt.asg_assgnmnt_fk
269: ,eq.erlst_evnt_effective_date
270: ,'ASG_MGR_' || eq.source_code
271: FROM
272: hri_eq_sprvsr_hrchy_chgs eq
273: ,hri_mb_wrkfc_evt_ct wevt
274: WHERE wevt.time_day_evt_end_fk >= eq.erlst_evnt_effective_date
275: AND wevt.per_person_mgr_fk = eq.person_id;
276:
298: SELECT
299: person_id
300: ,erlst_evnt_effective_date
301: ,source_code
302: FROM hri_eq_sprvsr_hrchy_chgs;
303:
304: -- commit
305: COMMIT;
306:
334: MERGE INTO hri_eq_asg_sup_wrfc delta_eq
335: USING (SELECT assignment_id,
336: erlst_evnt_effective_date,
337: 'SUPERVISOR' source_type
338: FROM hri_eq_sprvsr_hrchy_chgs) sup_eq
339: ON ( delta_eq.source_type = 'SUPERVISOR'
340: AND sup_eq.assignment_id = delta_eq.source_id)
341: WHEN MATCHED THEN
342: UPDATE SET delta_eq.erlst_evnt_effective_date =
360: MERGE INTO hri_eq_sup_absnc absnc_eq
361: USING (SELECT person_id,
362: erlst_evnt_effective_date,
363: 'SUPERVISOR' source_type
364: FROM hri_eq_sprvsr_hrchy_chgs) sup_eq
365: ON ( absnc_eq.source_type = 'SUPERVISOR'
366: AND sup_eq.person_id = absnc_eq.source_id)
367: WHEN MATCHED THEN
368: UPDATE SET absnc_eq.erlst_evnt_effective_date =
840:
841: END insert_trn_row;
842:
843: -- ----------------------------------------------------------------------------
844: -- This procedure populates the person_id column in hri_eq_sprvsr_hrchy_chgs
845: -- by using the value of assignment_id
846: -- ----------------------------------------------------------------------------
847: PROCEDURE update_event_queue IS
848:
858: -- not be correct in the hiearchy.
859: -- Removed the condition (AND supervisor_id is not null) from the inner query
860:
861: -- Delete records that are not primary employee assignment change events
862: DELETE /*+ PARALLEL(eq, default,default)*/ hri_eq_sprvsr_hrchy_chgs eq
863: WHERE assignment_id NOT IN
864: (SELECT assignment_id
865: FROM per_all_assignments_f asg
866: WHERE eq.assignment_id = asg.assignment_id
873: -- Commit
874: commit;
875:
876: -- Set person ids on event queue
877: UPDATE hri_eq_sprvsr_hrchy_chgs eq
878: SET person_id =
879: (SELECT person_id
880: FROM per_all_assignments_f asg
881: WHERE eq.assignment_id = asg.assignment_id
906: -- Delete chain updates after the date of refresh
907: DELETE FROM hri_cs_suph sph
908: WHERE sph.rowid IN
909: (SELECT sph2.rowid
910: FROM hri_eq_sprvsr_hrchy_chgs evt,
911: hri_cs_suph sph2
912: WHERE evt.person_id = sph2.sub_person_id
913: AND evt.person_id BETWEEN p_start_person_id
914: AND p_end_person_id
919: -- Delete lookup chain updates after the date of refresh
920: DELETE FROM hri_cs_mngrsc_ct chn
921: WHERE chn.rowid IN
922: (SELECT chn2.rowid
923: FROM hri_eq_sprvsr_hrchy_chgs evt,
924: hri_cs_mngrsc_ct chn2
925: WHERE evt.person_id = chn2.mgrs_person_fk
926: AND evt.person_id BETWEEN p_start_person_id
927: AND p_end_person_id
933: -- for latest chains of supervisor in event queue
934: UPDATE hri_cs_suph sph
935: SET effective_end_date =
936: (SELECT (evt.erlst_evnt_effective_date - 1)
937: FROM hri_eq_sprvsr_hrchy_chgs evt
938: WHERE evt.person_id = sph.sub_person_id
939: AND evt.erlst_evnt_effective_date BETWEEN sph.effective_start_date
940: AND sph.effective_end_date)
941: ,last_update_date = sysdate
945: (SELECT
946: sph2.sub_person_id,
947: sph2.sup_person_id,
948: sph2.effective_start_date
949: FROM hri_eq_sprvsr_hrchy_chgs evt,
950: hri_cs_suph sph2
951: WHERE evt.person_id = sph2.sub_person_id
952: AND evt.person_id BETWEEN p_start_person_id
953: AND p_end_person_id
960: -- for latest lookup chains of supervisor in event queue
961: UPDATE hri_cs_mngrsc_ct chn
962: SET chn.mgrs_date_end =
963: (SELECT (evt.erlst_evnt_effective_date - 1)
964: FROM hri_eq_sprvsr_hrchy_chgs evt
965: WHERE evt.person_id = chn.mgrs_person_fk
966: AND evt.erlst_evnt_effective_date BETWEEN chn.mgrs_date_start
967: AND chn.mgrs_date_end)
968: ,last_update_date = sysdate
968: ,last_update_date = sysdate
969: WHERE chn.mgrs_mngrsc_pk IN
970: (SELECT
971: chn2.mgrs_mngrsc_pk
972: FROM hri_eq_sprvsr_hrchy_chgs evt,
973: hri_cs_mngrsc_ct chn2
974: WHERE evt.person_id = chn2.mgrs_person_fk
975: AND evt.person_id BETWEEN p_start_person_id
976: AND p_end_person_id
989:
990: END delete_and_end_date_suph_recs;
991:
992: -- ----------------------------------------------------------------------------
993: -- Removes later duplicate events for a person in hri_eq_sprvsr_hrchy_chgs
994: -- leaving only the earliest recorded event held in the table
995: -- ----------------------------------------------------------------------------
996: PROCEDURE remove_duplicates IS
997:
997:
998: BEGIN
999:
1000: -- Delete duplicate events from queue
1001: DELETE FROM hri_eq_sprvsr_hrchy_chgs evt
1002: WHERE EXISTS
1003: (SELECT 'x'
1004: FROM hri_eq_sprvsr_hrchy_chgs evt2
1005: WHERE evt2.person_id = evt.person_id
1000: -- Delete duplicate events from queue
1001: DELETE FROM hri_eq_sprvsr_hrchy_chgs evt
1002: WHERE EXISTS
1003: (SELECT 'x'
1004: FROM hri_eq_sprvsr_hrchy_chgs evt2
1005: WHERE evt2.person_id = evt.person_id
1006: AND ((evt.erlst_evnt_effective_date = evt2.erlst_evnt_effective_date
1007: AND evt.rowid < evt2.rowid)
1008: OR
1026: -- ----------------------------------------------------------------------------
1027: -- For every change for a supervisor there is a knock on effect on his
1028: -- subordinates i.e. the supervisor hierarchy for the subordinates changes.
1029: -- This procedure finds the subordinates for a supervisor that has an event
1030: -- and inserts them into 'hri_eq_sprvsr_hrchy_chgs'.
1031: -- ----------------------------------------------------------------------------
1032: PROCEDURE find_subordinates IS
1033:
1034: BEGIN
1033:
1034: BEGIN
1035:
1036: -- Insert subordinate records into event queue
1037: INSERT /*+ append */ INTO hri_eq_sprvsr_hrchy_chgs
1038: (person_id
1039: ,assignment_id
1040: ,erlst_evnt_effective_date
1041: ,source_code)
1044: ,sph.sub_assignment_id
1045: ,GREATEST(evt.erlst_evnt_effective_date,sph.effective_start_date)
1046: ,'DERIVED'
1047: FROM
1048: hri_eq_sprvsr_hrchy_chgs evt
1049: ,hri_cs_suph sph
1050: WHERE sph.sup_person_id = evt.person_id
1051: AND sph.sub_relative_level > 0
1052: AND sph.effective_end_date >= evt.erlst_evnt_effective_date;
1983: SELECT DISTINCT
1984: eq.person_id
1985: ,eq.erlst_evnt_effective_date change_date
1986: FROM
1987: hri_eq_sprvsr_hrchy_chgs eq
1988: WHERE eq.person_id BETWEEN p_start_object_id and p_end_object_id;
1989:
1990: BEGIN
1991:
2146:
2147: -- Set the SQL statement for the entire range
2148: p_sqlstr :=
2149: 'SELECT person_id object_id
2150: FROM hri_eq_sprvsr_hrchy_chgs
2151: ORDER BY person_id';
2152:
2153: END IF;
2154:
2194: p_table_owner => l_schema);
2195: END IF;
2196:
2197: -- As the supervisor hierarchy has been rebuilt, purge the events queue
2198: hri_opl_event_capture.purge_queue('HRI_EQ_SPRVSR_HRCHY_CHGS');
2199:
2200: IF (p_mthd_action_id > -1) THEN
2201:
2202: -- Log process end
2222: SELECT
2223: person_id
2224: ,erlst_evnt_effective_date start_date
2225: FROM
2226: hri_eq_sprvsr_hrchy_chgs;
2227:
2228: BEGIN
2229:
2230: -- Set globals