[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM dba_mview_logs
WHERE master = 'HRI_MAP_SUP_WRKFC_ASG'
AND log_owner = p_schema;
PROCEDURE update_job_changes(p_start_object_id IN NUMBER
,p_end_object_id IN NUMBER )
IS
BEGIN
--
dbg('Inside update_job_changes');
UPDATE hri_map_sup_wrkfc_asg asg_dlt
SET (asg_dlt.job_fmly_code , asg_dlt.job_fnctn_code) =
(SELECT jobh.job_fmly_code ,
jobh.job_fnctn_code
FROM hri_cs_jobh_ct jobh
WHERE jobh.job_id = asg_dlt.job_id)
WHERE (assignment_id, evts_effective_end_date) in
(SELECT asgn.assignment_id,
asgn.effective_change_end_date
FROM hri_mb_asgn_events_ct asgn,
hri_eq_asg_sup_wrfc eq
WHERE asgn.job_id = eq.source_id
AND eq.source_type = 'JOB'
AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
dbg(sql%rowcount || ' records update due to job changes');
END update_job_changes;
PROCEDURE update_prmry_job_role_changes(p_start_object_id IN NUMBER
,p_end_object_id IN NUMBER )
IS
BEGIN
--
dbg('Inside update_prmry_job_role_changes');
UPDATE hri_map_sup_wrkfc_asg asg_dlt
SET asg_dlt.primary_job_role_code =
(SELECT jbrl.job_role_code
FROM hri_cs_job_job_role_ct jbrl
WHERE jbrl.job_id = asg_dlt.job_id
AND jbrl.primary_role_for_job_flag = 'Y')
WHERE (assignment_id, evts_effective_end_date) IN
(SELECT asgn.assignment_id,
asgn.effective_change_end_date
FROM hri_mb_asgn_events_ct asgn,
hri_eq_asg_sup_wrfc eq
WHERE asgn.job_id = eq.source_id
AND eq.source_type = 'PRIMARY_JOB_ROLE'
AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
dbg(sql%rowcount || ' records updated due to primary job role changes');
END update_prmry_job_role_changes;
PROCEDURE update_location_changes(p_start_object_id IN NUMBER
,p_end_object_id IN NUMBER )
IS
BEGIN
--
dbg('Inside update_location_changes');
UPDATE hri_map_sup_wrkfc_asg asg_dlt
SET (asg_dlt.geo_area_code,asg_dlt.geo_country_code,asg_dlt.geo_region_code,asg_dlt.geo_city_cid) =
(SELECT geoh.area_code,
geoh.country_code,
geoh.region_code,
geoh.city_cid
FROM hri_cs_geo_lochr_ct geoh
WHERE geoh.location_id = asg_dlt.location_id)
WHERE (assignment_id, evts_effective_end_date) IN
(SELECT asgn.assignment_id,
asgn.effective_change_end_date
FROM hri_mb_asgn_events_ct asgn,
hri_eq_asg_sup_wrfc eq
WHERE asgn.location_id = eq.source_id
AND eq.source_type = 'LOCATION'
AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
dbg(sql%rowcount || ' records updated due to location changes');
END update_location_changes;
PROCEDURE update_person_type_changes(p_start_object_id IN NUMBER
,p_end_object_id IN NUMBER )
IS
BEGIN
null;
dbg('Inside update_person_type_changes');
UPDATE hri_map_sup_wrkfc_asg asg_dlt
SET (asg_dlt.wkth_wktyp_sk_fk,asg_dlt.wkth_lvl1_sk_fk,asg_dlt.wkth_lvl2_sk_fk,asg_dlt.wkth_wktyp_code) =
(SELECT prsn.wkth_wktyp_sk_fk,
prsn.wkth_lvl1_sk_fk,
prsn.wkth_lvl2_sk_fk,
prsn.wkth_wktyp_code
FROM hri_cs_prsntyp_ct prsn,
hri_mb_asgn_events_ct asgn
WHERE prsn.prsntyp_sk_pk = asgn.prsntyp_sk_fk
AND asgn.assignment_id = asg_dlt.assignment_id
AND ROWNUM < 2
)
WHERE (assignment_id, evts_effective_end_date) IN
(SELECT asgn.assignment_id,
asgn.effective_change_end_date
FROM hri_mb_asgn_events_ct asgn,
hri_eq_asg_sup_wrfc eq
WHERE asgn.prsntyp_sk_fk = eq.source_id
AND eq.source_type = 'PERSON_TYPE'
AND eq.source_id BETWEEN p_start_object_id AND p_end_object_id);
dbg(sql%rowcount || ' records updated due to person type changes');
END update_person_type_changes;
DELETE hri_map_sup_wrkfc_asg asg_sph
WHERE asg_sph.assignment_id in
(select source_id
from hri_eq_asg_sup_wrfc evt
where evt.source_id between p_start_object_id and p_end_object_id
and evt.source_id = asg_sph.assignment_id
AND evt.source_type = 'ASG_EVENT')
AND asg_sph.evts_effective_end_date >=
(select evt.erlst_evnt_effective_date - 1
from hri_eq_asg_sup_wrfc evt
where evt.source_id = asg_sph.assignment_id
AND evt.source_type = 'ASG_EVENT');
dbg(sql%rowcount || ' records deleted due to assignment event changes');
INSERT INTO HRI_MAP_SUP_WRKFC_ASG (
--
-- Supervisor id's
--
supervisor_person_id
,direct_supervisor_person_id
--
-- Effective Dates
--
,effective_date
--
-- 3986188 a end date column is required which should contain the least end date
-- from events or supervisor hiearchy tables
--
,effective_end_date
,evts_effective_end_date
,suph_effective_end_date
--
-- Period of work start date
--
,pow_start_date
--
-- 4234485, Period of work start date in Julian days.
--
,pow_value_days_julian
,pow_extn_days_julian
--
-- Unique key generated for the events fact
--
,event_id
--
-- Assignment related FK id's
--
,person_id
,assignment_id
,location_id
,job_id
,organization_id
,position_id
,grade_id
--
-- Workforce related FK id's
--
,wkth_wktyp_sk_fk
,wkth_lvl1_sk_fk
,wkth_lvl2_sk_fk
--
-- Length of work related FK id
--
,pow_band_sk_fk
--
-- Job codes
--
,job_fmly_code
,job_fnctn_code
--
-- Priamry job role code
--
,primary_job_role_code
--
--
-- Location codes
--
,geo_area_code
,geo_country_code
,geo_region_code
,geo_city_cid
--
-- Termination reason and category
--
,leaving_reason_code
,separation_category
--
-- Performance band
--
,perf_band
--
-- Workforce type code
--
,wkth_wktyp_code
--
-- Salary currency and value
--
,anl_slry_currency
,anl_slry_value
--
-- Headcount and FTE value
--
,headcount_value
,fte_value
--
-- Indicators
--
,worker_hire_ind
,post_hire_asgn_start_ind
,worker_term_ind
,term_voluntary_ind
,term_involuntary_ind
,pre_sprtn_asgn_end_ind
,transfer_in_ind
,transfer_out_ind
--
,direct_ind
,primary_flag_ind
,primary_asg_with_hdc_ind
--
-- Indicators to decide summarization requirements
--
,summarization_rqd_ind
,summarization_rqd_chng_ind
--
-- Indicates gain and loss events
--
,metric_adjust_multiplier
--
-- Relative supervisor level
--
,supervisor_level
--
-- Admin columns
--
,admin_row_type
,admin_evts_rowid
,admin_suph_rowid
,admin_jobh_rowid
,admin_geoh_rowid
--
-- WHO Columns
--
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date
--
-- Incremental changes
--
,sub_assignment_id)
SELECT /*+ ORDERED */
suph.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,GREATEST(evts.effective_change_date,
suph.effective_start_date) effective_date
--
-- 3986188 a end date column is required which should contain the least end date
-- from events or supervisor hiearchy tables
--
,LEAST(evts.effective_change_end_date,
suph.effective_end_date ) effective_end_date
,evts.effective_change_end_date evts_effective_end_date
,suph.effective_end_date suph_effective_end_date
,evts.pow_start_date_adj pow_start_date
,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
pow_value_days_julian
,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
pow_extn_days_julian
,evts.event_id event_id
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.location_id location_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
,evts.pow_band_sk_fk pow_band_sk_fk
,jobh.job_fmly_code job_fmly_code
,jobh.job_fnctn_code job_fnctn_code
--
-- Assign job role only for primary job roles
--
,CASE
WHEN rolj.primary_role_for_job_flag = 'Y' THEN
rolj.job_role_code
ELSE
'NA_EDW'
END primary_job_role_code
--
,geoh.area_code geo_area_code
,geoh.country_code geo_country_code
,geoh.region_code geo_region_code
,geoh.city_cid geo_city_cid
,evts.leaving_reason_code leaving_reason_code
,'NA_EDW' separation_category
,evts.perf_band perf_band
,prsn.wkth_wktyp_code wkth_wktyp_code
,evts.anl_slry_currency anl_slry_currency
--
-- Set salary, headcount and fte to 0 when summarization is not
-- required
--
,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
,evts.headcount * evts.summarization_rqd_ind headcount_value
,evts.fte * evts.summarization_rqd_ind fte_value
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 0
ELSE evts.worker_hire_ind
END worker_hire_ind
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 0
ELSE evts.post_hire_asgn_start_ind
END post_hire_asgn_start_ind
,0 worker_term_ind
,0 term_voluntary_ind
,0 term_involuntary_ind
,0 pre_sprtn_asgn_end_ind
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 1
WHEN evts.effective_change_date > suph.effective_start_date
THEN evts.supervisor_change_ind
ELSE 1 - (evts.worker_hire_ind + evts.post_hire_asgn_start_ind)
END transfer_in_ind
,0 transfer_out_ind
,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
--
-- 4013742
-- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
-- when summarization is not required
--
,CASE WHEN evts.primary_flag = 'Y'
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_flag_ind
,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_asg_with_hdc_ind
,evts.summarization_rqd_ind summarization_rqd_ind
,CASE
--
-- Only set for assignment change events
--
WHEN evts.effective_change_date >= suph.effective_start_date THEN
evts.summarization_rqd_chng_ind
--
-- For supervisor change events, set as 0
--
ELSE
0
END summarization_rqd_chng_ind
,1 metric_adjust_multiplier
,suph.sup_level supervisor_level
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 'GAIN SUP EVENT ONLY'
WHEN evts.effective_change_date > suph.effective_start_date
THEN 'GAIN ASG EVENT ONLY'
ELSE 'GAIN ASG SUP EVENT'
END admin_row_type
,evts.rowid admin_evts_rowid
,suph.rowid admin_suph_rowid
,jobh.rowid admin_jobh_rowid
,geoh.rowid admin_geoh_rowid
--
-- WHO Columns
--
, SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
--
-- Incremental Changes
--
,sub_assignment_id sub_assignment_id
FROM
hri_eq_asg_sup_wrfc eq
,hri_mb_asgn_events_ct evts
,hri_cs_jobh_ct jobh
,hri_cs_geo_lochr_ct geoh
,hri_cs_prsntyp_ct prsn
,hri_cs_job_job_role_ct rolj
,hri_cs_suph suph
WHERE suph.sub_person_id = evts.supervisor_id
AND suph.sup_invalid_flag_code = 'N'
AND (evts.effective_change_date BETWEEN suph.effective_start_date AND suph.effective_end_date
OR suph.effective_start_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date)
AND evts.pre_sprtn_asgn_end_ind = 0
AND evts.worker_term_ind = 0
AND geoh.location_id = evts.location_id
AND jobh.job_id = evts.job_id
AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
AND evts.job_id = rolj.job_id
AND eq.source_id between p_start_object_id and p_end_object_id
AND eq.source_type = 'ASG_EVENT'
AND eq.source_id = evts.assignment_id
AND eq.erlst_evnt_effective_date -1 <= evts.effective_change_end_date
UNION ALL
SELECT /*+ ORDERED */
suph.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,LEAST(evts.effective_change_end_date, suph.effective_end_date) + 1
effective_date
--
-- 3986188 a end date column is required which should contain the least end date
-- from events or supervisor hiearchy tables
--
,null effective_end_date
,evts.effective_change_end_date evts_effective_end_date
,suph.effective_end_date suph_effective_end_date
,evts.pow_start_date_adj pow_start_date
,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
pow_value_days_julian
,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
pow_extn_days_julian
,evts.event_id event_id
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.location_id location_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
,evts.pow_band_sk_fk pow_band_sk_fk
,jobh.job_fmly_code job_fmly_code
,jobh.job_fnctn_code job_fnctn_code
,CASE
WHEN rolj.primary_role_for_job_flag = 'Y' THEN
rolj.job_role_code
ELSE
'NA_EDW'
END primary_job_role_code
--
,geoh.area_code geo_area_code
,geoh.country_code geo_country_code
,geoh.region_code geo_region_code
,geoh.city_cid geo_city_cid
,evts.leaving_reason_code leaving_reason_code
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 'NA_EDW'
ELSE evts.separation_category_nxt
END separation_category
,evts.perf_band perf_band
,prsn.wkth_wktyp_code wkth_wktyp_code
,evts.anl_slry_currency anl_slry_currency
,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
,evts.headcount * evts.summarization_rqd_ind headcount_value
,evts.fte * evts.summarization_rqd_ind fte_value
,0 worker_hire_ind
,0 post_hire_asgn_start_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.worker_term_nxt_ind
END worker_term_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.term_voluntary_nxt_ind
END term_voluntary_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.term_involuntary_nxt_ind
END term_involuntary_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.pre_sprtn_asgn_end_nxt_ind
END pre_sprtn_asgn_end_ind
,0 transfer_in_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 1
WHEN suph.effective_end_date > evts.effective_change_end_date
THEN evts.supervisor_change_nxt_ind
ELSE 1 - (evts.worker_term_nxt_ind + evts.pre_sprtn_asgn_end_nxt_ind)
END transfer_out_ind
,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
--
-- 4013742
-- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
-- when summarization is not required
--
,CASE WHEN evts.primary_flag = 'Y'
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_flag_ind
,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_asg_with_hdc_ind
,evts.summarization_rqd_ind summarization_rqd_ind
,CASE
WHEN suph.effective_end_date >= evts.effective_change_end_date THEN
evts.summarization_rqd_chng_nxt_ind
ELSE
0
END summarization_rqd_chng_ind
,-1 metric_adjust_multiplier
,suph.sup_level supervisor_level
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 'LOSS SUP EVENT ONLY'
WHEN suph.effective_end_date > evts.effective_change_end_date
THEN 'LOSS ASG EVENT ONLY'
ELSE 'LOSS ASG SUP EVENT'
END admin_row_type
,evts.rowid admin_evts_rowid
,suph.rowid admin_suph_rowid
,jobh.rowid admin_jobh_rowid
,geoh.rowid admin_geoh_rowid
--
-- WHO Columns
--
,SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
--
-- Incremental Changes
--
,sub_assignment_id sub_assignment_id
FROM
hri_eq_asg_sup_wrfc eq
,hri_mb_asgn_events_ct evts
,hri_cs_jobh_ct jobh
,hri_cs_geo_lochr_ct geoh
,hri_cs_prsntyp_ct prsn
,hri_cs_job_job_role_ct rolj
,hri_cs_suph suph
WHERE suph.sub_person_id = evts.supervisor_id
AND suph.sup_invalid_flag_code = 'N'
AND (suph.effective_end_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date
OR evts.effective_change_end_date BETWEEN suph.effective_start_date AND suph.effective_end_date)
AND LEAST(suph.effective_end_date, evts.effective_change_end_date) < to_date('31-12-4712','DD-MM-YYYY')
AND evts.pre_sprtn_asgn_end_ind = 0
AND evts.worker_term_ind = 0
AND geoh.location_id = evts.location_id
AND jobh.job_id = evts.job_id
AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
AND evts.job_id = rolj.job_id
AND eq.source_id between p_start_object_id and p_end_object_id
AND eq.source_id = evts.assignment_id
AND eq.source_type = 'ASG_EVENT'
AND eq.erlst_evnt_effective_date -1 <= evts.effective_change_end_date;
dbg(SQL%ROWCOUNT||' records inserted for asg events changes');
DELETE hri_map_sup_wrkfc_asg asg_sph
WHERE asg_sph.sub_assignment_id in
(SELECT evt.source_id
FROM hri_eq_asg_sup_wrfc evt
WHERE evt.source_id between p_start_object_id and p_end_object_id
AND evt.source_id = asg_sph.sub_assignment_id
AND evt.source_type = 'SUPERVISOR')
AND asg_sph.suph_effective_end_date >=
(SELECT evt.erlst_evnt_effective_date - 1
FROM hri_eq_asg_sup_wrfc evt
WHERE evt.source_id = asg_sph.sub_assignment_id
AND evt.source_type = 'SUPERVISOR');
dbg(sql%rowcount || ' records deleted due to sup eq');
INSERT INTO HRI_MAP_SUP_WRKFC_ASG (
--
-- Supervisor id's
--
supervisor_person_id
,direct_supervisor_person_id
--
-- Effective Dates
--
,effective_date
--
-- 3986188 a end date column is required which should contain the least end date
-- from events or supervisor hiearchy tables
--
,effective_end_date
,evts_effective_end_date
,suph_effective_end_date
--
-- Period of work start date
--
,pow_start_date
--
-- 4234485, Period of work start date in Julian days.
--
,pow_value_days_julian
,pow_extn_days_julian
--
-- Unique key generated for the events fact
--
,event_id
--
-- Assignment related FK id's
--
,person_id
,assignment_id
,location_id
,job_id
,organization_id
,position_id
,grade_id
--
-- Workforce related FK id's
--
,wkth_wktyp_sk_fk
,wkth_lvl1_sk_fk
,wkth_lvl2_sk_fk
--
-- Length of work related FK id
--
,pow_band_sk_fk
--
-- Job codes
--
,job_fmly_code
,job_fnctn_code
--
-- Priamry job role code
--
,primary_job_role_code
--
--
-- Location codes
--
,geo_area_code
,geo_country_code
,geo_region_code
,geo_city_cid
--
-- Termination reason and category
--
,leaving_reason_code
,separation_category
--
-- Performance band
--
,perf_band
--
-- Workforce type code
--
,wkth_wktyp_code
--
-- Salary currency and value
--
,anl_slry_currency
,anl_slry_value
--
-- Headcount and FTE value
--
,headcount_value
,fte_value
--
-- Indicators
--
,worker_hire_ind
,post_hire_asgn_start_ind
,worker_term_ind
,term_voluntary_ind
,term_involuntary_ind
,pre_sprtn_asgn_end_ind
,transfer_in_ind
,transfer_out_ind
--
,direct_ind
,primary_flag_ind
,primary_asg_with_hdc_ind
--
-- Indicators to decide summarization requirements
--
,summarization_rqd_ind
,summarization_rqd_chng_ind
--
-- Indicates gain and loss events
--
,metric_adjust_multiplier
--
-- Relative supervisor level
--
,supervisor_level
--
-- Admin columns
--
,admin_row_type
,admin_evts_rowid
,admin_suph_rowid
,admin_jobh_rowid
,admin_geoh_rowid
--
-- WHO Columns
--
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date
--
-- Incremental changes
--
,sub_assignment_id)
SELECT
suph.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,GREATEST(evts.effective_change_date,
suph.effective_start_date) effective_date
--
-- 3986188 a end date column is required which should contain the least end date
-- from events or supervisor hiearchy tables
--
,LEAST(evts.effective_change_end_date,
suph.effective_end_date ) effective_end_date
,evts.effective_change_end_date evts_effective_end_date
,suph.effective_end_date suph_effective_end_date
,evts.pow_start_date_adj pow_start_date
,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
pow_value_days_julian
,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
pow_extn_days_julian
,evts.event_id event_id
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.location_id location_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
,evts.pow_band_sk_fk pow_band_sk_fk
,jobh.job_fmly_code job_fmly_code
,jobh.job_fnctn_code job_fnctn_code
--
-- Assign job role only for primary job roles
--
,CASE
WHEN rolj.primary_role_for_job_flag = 'Y' THEN
rolj.job_role_code
ELSE
'NA_EDW'
END primary_job_role_code
--
,geoh.area_code geo_area_code
,geoh.country_code geo_country_code
,geoh.region_code geo_region_code
,geoh.city_cid geo_city_cid
,evts.leaving_reason_code leaving_reason_code
,'NA_EDW' separation_category
,evts.perf_band perf_band
,prsn.wkth_wktyp_code wkth_wktyp_code
,evts.anl_slry_currency anl_slry_currency
--
-- Set salary, headcount and fte to 0 when summarization is not
-- required
--
,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
,evts.headcount * evts.summarization_rqd_ind headcount_value
,evts.fte * evts.summarization_rqd_ind fte_value
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 0
ELSE evts.worker_hire_ind
END worker_hire_ind
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 0
ELSE evts.post_hire_asgn_start_ind
END post_hire_asgn_start_ind
,0 worker_term_ind
,0 term_voluntary_ind
,0 term_involuntary_ind
,0 pre_sprtn_asgn_end_ind
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 1
WHEN evts.effective_change_date > suph.effective_start_date
THEN evts.supervisor_change_ind
ELSE 1 - (evts.worker_hire_ind + evts.post_hire_asgn_start_ind)
END transfer_in_ind
,0 transfer_out_ind
,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
--
-- 4013742
-- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
-- when summarization is not required
--
,CASE WHEN evts.primary_flag = 'Y'
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_flag_ind
,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_asg_with_hdc_ind
,evts.summarization_rqd_ind summarization_rqd_ind
,CASE
--
-- Only set for assignment change events
--
WHEN evts.effective_change_date >= suph.effective_start_date THEN
evts.summarization_rqd_chng_ind
--
-- For supervisor change events, set as 0
--
ELSE
0
END summarization_rqd_chng_ind
,1 metric_adjust_multiplier
,suph.sup_level supervisor_level
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 'GAIN SUP EVENT ONLY'
WHEN evts.effective_change_date > suph.effective_start_date
THEN 'GAIN ASG EVENT ONLY'
ELSE 'GAIN ASG SUP EVENT'
END admin_row_type
,evts.rowid admin_evts_rowid
,suph.rowid admin_suph_rowid
,jobh.rowid admin_jobh_rowid
,geoh.rowid admin_geoh_rowid
--
-- WHO Columns
--
, SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
--
-- Incremental Changes
--
,sub_assignment_id sub_assignment_id
FROM
hri_mb_asgn_events_ct evts
,hri_cs_jobh_ct jobh
,hri_cs_geo_lochr_ct geoh
,hri_cs_suph suph
,hri_cs_prsntyp_ct prsn
,hri_cs_job_job_role_ct rolj
,hri_eq_asg_sup_wrfc eq
WHERE suph.sub_person_id = evts.supervisor_id
AND suph.sup_invalid_flag_code = 'N'
AND (evts.effective_change_date BETWEEN suph.effective_start_date AND suph.effective_end_date
OR suph.effective_start_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date)
AND evts.pre_sprtn_asgn_end_ind = 0
AND evts.worker_term_ind = 0
AND geoh.location_id = evts.location_id
AND jobh.job_id = evts.job_id
AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
AND evts.job_id = rolj.job_id
AND eq.source_id between p_start_object_id and p_end_object_id
AND eq.source_type = 'SUPERVISOR'
AND eq.source_id = suph.sub_assignment_id
AND eq.erlst_evnt_effective_date - 1 <= suph.effective_end_date
UNION ALL
SELECT
suph.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,LEAST(evts.effective_change_end_date, suph.effective_end_date) + 1
effective_date
--
-- 3986188 a end date column is required which should contain the least end date
-- from events or supervisor hiearchy tables
--
,null effective_end_date
,evts.effective_change_end_date evts_effective_end_date
,suph.effective_end_date suph_effective_end_date
,evts.pow_start_date_adj pow_start_date
,to_char(evts.pow_start_date_adj,'J') * evts.summarization_rqd_ind
pow_value_days_julian
,nvl(to_char(evts.pow_extn_strt_dt,'J') * evts.summarization_rqd_ind,0)
pow_extn_days_julian
,evts.event_id event_id
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.location_id location_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
,evts.pow_band_sk_fk pow_band_sk_fk
,jobh.job_fmly_code job_fmly_code
,jobh.job_fnctn_code job_fnctn_code
,CASE
WHEN rolj.primary_role_for_job_flag = 'Y' THEN
rolj.job_role_code
ELSE
'NA_EDW'
END primary_job_role_code
--
,geoh.area_code geo_area_code
,geoh.country_code geo_country_code
,geoh.region_code geo_region_code
,geoh.city_cid geo_city_cid
,evts.leaving_reason_code leaving_reason_code
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 'NA_EDW'
ELSE evts.separation_category_nxt
END separation_category
,evts.perf_band perf_band
,prsn.wkth_wktyp_code wkth_wktyp_code
,evts.anl_slry_currency anl_slry_currency
,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
,evts.headcount * evts.summarization_rqd_ind headcount_value
,evts.fte * evts.summarization_rqd_ind fte_value
,0 worker_hire_ind
,0 post_hire_asgn_start_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.worker_term_nxt_ind
END worker_term_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.term_voluntary_nxt_ind
END term_voluntary_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.term_involuntary_nxt_ind
END term_involuntary_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.pre_sprtn_asgn_end_nxt_ind
END pre_sprtn_asgn_end_ind
,0 transfer_in_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 1
WHEN suph.effective_end_date > evts.effective_change_end_date
THEN evts.supervisor_change_nxt_ind
ELSE 1 - (evts.worker_term_nxt_ind + evts.pre_sprtn_asgn_end_nxt_ind)
END transfer_out_ind
,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
--
-- 4013742
-- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
-- when summarization is not required
--
,CASE WHEN evts.primary_flag = 'Y'
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_flag_ind
,CASE WHEN evts.primary_flag = 'Y' and evts.headcount > 0
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_asg_with_hdc_ind
,evts.summarization_rqd_ind summarization_rqd_ind
,CASE
WHEN suph.effective_end_date >= evts.effective_change_end_date THEN
evts.summarization_rqd_chng_nxt_ind
ELSE
0
END summarization_rqd_chng_ind
,-1 metric_adjust_multiplier
,suph.sup_level supervisor_level
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 'LOSS SUP EVENT ONLY'
WHEN suph.effective_end_date > evts.effective_change_end_date
THEN 'LOSS ASG EVENT ONLY'
ELSE 'LOSS ASG SUP EVENT'
END admin_row_type
,evts.rowid admin_evts_rowid
,suph.rowid admin_suph_rowid
,jobh.rowid admin_jobh_rowid
,geoh.rowid admin_geoh_rowid
--
-- WHO Columns
--
,SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
--
-- Incremental Changes
--
,sub_assignment_id sub_assignment_id
FROM
hri_mb_asgn_events_ct evts
,hri_cs_jobh_ct jobh
,hri_cs_geo_lochr_ct geoh
,hri_cs_suph suph
,hri_cs_prsntyp_ct prsn
,hri_cs_job_job_role_ct rolj
,hri_eq_asg_sup_wrfc eq
WHERE suph.sub_person_id = evts.supervisor_id
AND suph.sup_invalid_flag_code = 'N'
AND (suph.effective_end_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date
OR evts.effective_change_end_date BETWEEN suph.effective_start_date AND suph.effective_end_date)
AND LEAST(suph.effective_end_date, evts.effective_change_end_date) < to_date('31-12-4712','DD-MM-YYYY')
AND evts.pre_sprtn_asgn_end_ind = 0
AND evts.worker_term_ind = 0
AND geoh.location_id = evts.location_id
AND jobh.job_id = evts.job_id
AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
AND evts.job_id = rolj.job_id
AND eq.source_id between p_start_object_id and p_end_object_id
AND eq.source_type = 'SUPERVISOR'
AND eq.source_id = suph.sub_assignment_id
AND eq.erlst_evnt_effective_date -1 <= suph.effective_end_date;
dbg(SQL%ROWCOUNT||' records inserted into HRI_MAP_SUP_WRKFC_ASG due to sup eq');
hri_opl_multi_thread.update_parameters
(p_mthd_action_id => p_mthd_action_id,
p_full_refresh => g_full_refresh,
p_global_start_date => l_dbi_collection_start_date);
update_job_changes(p_start_object_id => p_start_object_id
,p_end_object_id => p_end_object_id);
update_prmry_job_role_changes(p_start_object_id => p_start_object_id
,p_end_object_id => p_end_object_id);
update_location_changes(p_start_object_id => p_start_object_id
,p_end_object_id => p_end_object_id);
update_person_type_changes(p_start_object_id => p_start_object_id
,p_end_object_id => p_end_object_id);
'INSERT ' || l_hint || 'INTO ' || l_table_name || ' ' || l_partition_clause || '
(supervisor_person_id
,direct_supervisor_person_id
,effective_date
,effective_end_date
,evts_effective_end_date
,suph_effective_end_date
,pow_start_date
,pow_value_days_julian
,pow_extn_days_julian
,event_id
,person_id
,assignment_id
,location_id
,job_id
,organization_id
,position_id
,grade_id
,wkth_wktyp_sk_fk
,wkth_lvl1_sk_fk
,wkth_lvl2_sk_fk
,pow_band_sk_fk
,job_fmly_code
,job_fnctn_code
,primary_job_role_code
,geo_area_code
,geo_country_code
,geo_region_code
,geo_city_cid
,leaving_reason_code
,separation_category
,perf_band
,wkth_wktyp_code
,anl_slry_currency
,anl_slry_value
,headcount_value
,fte_value
,worker_hire_ind
,post_hire_asgn_start_ind
,worker_term_ind
,term_voluntary_ind
,term_involuntary_ind
,pre_sprtn_asgn_end_ind
,transfer_in_ind
,transfer_out_ind
,direct_ind
,primary_flag_ind
,primary_asg_with_hdc_ind
,summarization_rqd_ind
,summarization_rqd_chng_ind
,metric_adjust_multiplier
,supervisor_level
,admin_row_type
,admin_evts_rowid
,admin_suph_rowid
,admin_jobh_rowid
,admin_geoh_rowid
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date
,sub_assignment_id' ||
l_partition_column || ')
SELECT /*+ ORDERED */
suph.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,GREATEST(evts.effective_change_date,
suph.effective_start_date) effective_date
,LEAST(evts.effective_change_end_date,
suph.effective_end_date ) effective_end_date
,evts.effective_change_end_date evts_effective_end_date
,suph.effective_end_date suph_effective_end_date
,evts.pow_start_date_adj pow_start_date
,to_char(evts.pow_start_date_adj,''J'') * evts.summarization_rqd_ind
pow_value_days_julian
,nvl(to_char(evts.pow_extn_strt_dt,''J'') * evts.summarization_rqd_ind,0)
pow_extn_days_julian
,evts.event_id event_id
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.location_id location_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
,evts.pow_band_sk_fk pow_band_sk_fk
,jobh.job_fmly_code job_fmly_code
,jobh.job_fnctn_code job_fnctn_code
,CASE WHEN rolj.primary_role_for_job_flag = ''Y''
THEN rolj.job_role_code
ELSE ''NA_EDW''
END primary_job_role_code
,geoh.area_code geo_area_code
,geoh.country_code geo_country_code
,geoh.region_code geo_region_code
,geoh.city_cid geo_city_cid
,evts.leaving_reason_code leaving_reason_code
,''NA_EDW'' separation_category
,evts.perf_band perf_band
,prsn.wkth_wktyp_code wkth_wktyp_code
,evts.anl_slry_currency anl_slry_currency
,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
,evts.headcount * evts.summarization_rqd_ind headcount_value
,evts.fte * evts.summarization_rqd_ind fte_value
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 0
ELSE evts.worker_hire_ind
END worker_hire_ind
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 0
ELSE evts.post_hire_asgn_start_ind
END post_hire_asgn_start_ind
,0 worker_term_ind
,0 term_voluntary_ind
,0 term_involuntary_ind
,0 pre_sprtn_asgn_end_ind
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN 1
WHEN evts.effective_change_date > suph.effective_start_date
THEN evts.supervisor_change_ind
ELSE 1 - (evts.worker_hire_ind + evts.post_hire_asgn_start_ind)
END transfer_in_ind
,0 transfer_out_ind
,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
,CASE WHEN evts.primary_flag = ''Y''
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_flag_ind
,CASE WHEN evts.primary_flag = ''Y'' and evts.headcount > 0
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_asg_with_hdc_ind
,evts.summarization_rqd_ind summarization_rqd_ind
,CASE WHEN evts.effective_change_date >= suph.effective_start_date
THEN evts.summarization_rqd_chng_ind
ELSE 0
END summarization_rqd_chng_ind
,1 metric_adjust_multiplier
,suph.sup_level supervisor_level
,CASE WHEN evts.effective_change_date < suph.effective_start_date
THEN ''GAIN SUP EVENT ONLY''
WHEN evts.effective_change_date > suph.effective_start_date
THEN ''GAIN ASG EVENT ONLY''
ELSE ''GAIN ASG SUP EVENT''
END admin_row_type
,evts.rowid admin_evts_rowid
,suph.rowid admin_suph_rowid
,jobh.rowid admin_jobh_rowid
,geoh.rowid admin_geoh_rowid
,:l_current_time
,' || l_user_id || '
,' || l_user_id || '
,' || l_user_id || '
,:l_current_time
,sub_assignment_id sub_assignment_id' ||
l_part_col_value || '
FROM
hri_mb_asgn_events_ct evts
,hri_cs_jobh_ct jobh
,hri_cs_geo_lochr_ct geoh
,hri_cs_prsntyp_ct prsn
,hri_cs_job_job_role_ct rolj
,hri_cs_suph suph
WHERE suph.sub_person_id = evts.supervisor_id
AND suph.sup_invalid_flag_code = ''N''
AND (evts.effective_change_date BETWEEN suph.effective_start_date
AND suph.effective_end_date
OR suph.effective_start_date BETWEEN evts.effective_change_date
AND evts.effective_change_end_date)
AND evts.pre_sprtn_asgn_end_ind = 0
AND evts.worker_term_ind = 0
AND geoh.location_id = evts.location_id
AND jobh.job_id = evts.job_id
AND evts.assignment_id between :start_object_id and :end_object_id
AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
AND evts.job_id = rolj.job_id
UNION ALL
SELECT /*+ ORDERED */
suph.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,LEAST(evts.effective_change_end_date, suph.effective_end_date) + 1
effective_date
,null effective_end_date
,evts.effective_change_end_date evts_effective_end_date
,suph.effective_end_date suph_effective_end_date
,evts.pow_start_date_adj pow_start_date
,to_char(evts.pow_start_date_adj,''J'') * evts.summarization_rqd_ind
pow_value_days_julian
,nvl(to_char(evts.pow_extn_strt_dt,''J'') * evts.summarization_rqd_ind,0)
pow_extn_days_julian
,evts.event_id event_id
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.location_id location_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,prsn.wkth_wktyp_sk_fk wkth_wktyp_sk_fk
,prsn.wkth_lvl1_sk_fk wkth_lvl1_sk_fk
,prsn.wkth_lvl2_sk_fk wkth_lvl2_sk_fk
,evts.pow_band_sk_fk pow_band_sk_fk
,jobh.job_fmly_code job_fmly_code
,jobh.job_fnctn_code job_fnctn_code
,CASE WHEN rolj.primary_role_for_job_flag = ''Y''
THEN rolj.job_role_code
ELSE ''NA_EDW''
END primary_job_role_code
,geoh.area_code geo_area_code
,geoh.country_code geo_country_code
,geoh.region_code geo_region_code
,geoh.city_cid geo_city_cid
,evts.leaving_reason_code leaving_reason_code
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN ''NA_EDW''
ELSE evts.separation_category_nxt
END separation_category
,evts.perf_band perf_band
,prsn.wkth_wktyp_code wkth_wktyp_code
,evts.anl_slry_currency anl_slry_currency
,evts.anl_slry * evts.summarization_rqd_ind anl_slry_value
,evts.headcount * evts.summarization_rqd_ind headcount_value
,evts.fte * evts.summarization_rqd_ind fte_value
,0 worker_hire_ind
,0 post_hire_asgn_start_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.worker_term_nxt_ind
END worker_term_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.term_voluntary_nxt_ind
END term_voluntary_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.term_involuntary_nxt_ind
END term_involuntary_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 0
ELSE evts.pre_sprtn_asgn_end_nxt_ind
END pre_sprtn_asgn_end_ind
,0 transfer_in_ind
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN 1
WHEN suph.effective_end_date > evts.effective_change_end_date
THEN evts.supervisor_change_nxt_ind
ELSE 1 - (evts.worker_term_nxt_ind + evts.pre_sprtn_asgn_end_nxt_ind)
END transfer_out_ind
,DECODE(suph.sub_relative_level, 0, 1, 0) direct_ind
--
-- 4013742
-- Set primary_flag_ind and primary_asg_with_hdc_ind to 0
-- when summarization is not required
--
,CASE WHEN evts.primary_flag = ''Y''
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_flag_ind
,CASE WHEN evts.primary_flag = ''Y'' and evts.headcount > 0
THEN 1 * evts.summarization_rqd_ind ELSE 0 END
primary_asg_with_hdc_ind
,evts.summarization_rqd_ind summarization_rqd_ind
,CASE
WHEN suph.effective_end_date >= evts.effective_change_end_date THEN
evts.summarization_rqd_chng_nxt_ind
ELSE
0
END summarization_rqd_chng_ind
,-1 metric_adjust_multiplier
,suph.sup_level supervisor_level
,CASE WHEN suph.effective_end_date < evts.effective_change_end_date
THEN ''LOSS SUP EVENT ONLY''
WHEN suph.effective_end_date > evts.effective_change_end_date
THEN ''LOSS ASG EVENT ONLY''
ELSE ''LOSS ASG SUP EVENT''
END admin_row_type
,evts.rowid admin_evts_rowid
,suph.rowid admin_suph_rowid
,jobh.rowid admin_jobh_rowid
,geoh.rowid admin_geoh_rowid
,:l_current_time
,' || l_user_id || '
,' || l_user_id || '
,' || l_user_id || '
,:l_current_time
,sub_assignment_id sub_assignment_id' ||
l_part_col_value || '
FROM
hri_mb_asgn_events_ct evts
,hri_cs_jobh_ct jobh
,hri_cs_geo_lochr_ct geoh
,hri_cs_prsntyp_ct prsn
,hri_cs_job_job_role_ct rolj
,hri_cs_suph suph
WHERE suph.sub_person_id = evts.supervisor_id
AND suph.sup_invalid_flag_code = ''N''
AND (suph.effective_end_date BETWEEN evts.effective_change_date
AND evts.effective_change_end_date
OR evts.effective_change_end_date BETWEEN suph.effective_start_date
AND suph.effective_end_date)
AND evts.pre_sprtn_asgn_end_ind = 0
AND evts.worker_term_ind = 0
AND geoh.location_id = evts.location_id
AND jobh.job_id = evts.job_id
AND evts.assignment_id between :start_object_id and :end_object_id
AND LEAST(suph.effective_end_date,
evts.effective_change_end_date) < :end_of_time
AND evts.prsntyp_sk_fk = prsn.prsntyp_sk_pk
AND evts.job_id = rolj.job_id';
dbg(SQL%ROWCOUNT||' records inserted into ' || l_table_name);
'SELECT DISTINCT
assignment_id object_id
FROM hri_mb_asgn_events_ct
ORDER BY assignment_id';
'SELECT /*+ parallel (EQ, default, default) */ DISTINCT source_id object_id
FROM hri_eq_asg_sup_wrfc eq
ORDER BY object_id';
dbg('Full Refresh selected - Creating indexes');
DELETE /*+ INDEX(dlt hri_map_sup_wrkfc_asg_n4) */
FROM hri_map_sup_wrkfc_asg dlt
WHERE assignment_id IN
(SELECT source_id
FROM hri_eq_asg_sup_wrfc evt
WHERE evt.source_type = 'ASG_EVENT')
AND EXISTS
(SELECT /*+ INDEX(dlt2 hri_map_sup_wrkfc_asg_n4) */
NULL
FROM
hri_map_sup_wrkfc_asg dlt2
WHERE dlt2.assignment_id = dlt.assignment_id
AND dlt2.evts_effective_end_date = dlt.evts_effective_end_date
AND dlt2.supervisor_person_id = dlt.supervisor_person_id
AND dlt2.effective_date = dlt.effective_date
AND dlt2.metric_adjust_multiplier = dlt.metric_adjust_multiplier
AND dlt2.ROWID > dlt.ROWID);
SELECT mthd_range_id,
min(object_id) start_object_id,
max(object_id) end_object_id
FROM (SELECT hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
,object_id
FROM (SELECT DISTINCT assignment_id object_id
FROM hri_mb_asgn_events_ct
ORDER BY assignment_id)
)
GROUP BY mthd_range_id;