[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO HRI_MAP_WRKFC_ORGMGR_CT (
--
-- Organization Manager Id
--
orgmgr_id
--
-- Effective dates
--
,effective_start_date
,effective_end_date
--
-- Dimensions
--
,organization_id
,job_id
,position_id
,grade_id
--
-- Net changes on effective date for all subordinates
--
,total_headcount
--
-- Net changes on effective date for direct reports only
--
,dr_headcount
--
-- WHO Columns
--
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
SELECT
dlt.supervisor_person_id supervisor_person_id
,dlt.effective_start_date effective_start_date
,nvl(dlt.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')) effective_end_date
,dlt.organization_id organization_id
,dlt.job_id job_id
,dlt.position_id position_id
,dlt.grade_id grade_id
,SUM(dlt.headcount_value * dlt.metric_adjust_multiplier)
headcount_adjust
,SUM(dlt.headcount_value * dlt.metric_adjust_multiplier * dlt.direct_ind)
dr_headcount_adjust
--
-- WHO Columns
--
,SYSDATE
,l_user_id
,l_user_id
,l_user_id
,SYSDATE
FROM (SELECT
orgmgr.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,GREATEST(evts.effective_change_date,
orgmgr.effective_start_date) effective_start_date
,LEAST(evts.effective_change_end_date,
orgmgr.effective_end_date ) effective_end_date
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,evts.headcount headcount_value
,evts.fte fte_value
,DECODE(orgmgr.sub_relative_level, 0, 1, 0) direct_ind
,1 metric_adjust_multiplier
,orgmgr.sup_level supervisor_level
,sub_assignment_id sub_assignment_id
FROM
hri_mb_asgn_events_ct evts
,hri_cs_suph_orgmgr_ct orgmgr
WHERE orgmgr.sub_person_id = evts.supervisor_id
AND (evts.effective_change_date BETWEEN orgmgr.effective_start_date AND orgmgr.effective_end_date
OR orgmgr.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 orgmgr.sup_person_id = p_person_id
UNION ALL
SELECT
orgmgr.sup_person_id supervisor_person_id
,evts.supervisor_id direct_supervisor_person_id
,LEAST(evts.effective_change_end_date, orgmgr.effective_end_date) + 1
effective_start_date
,null effective_end_date
,evts.person_id person_id
,evts.assignment_id assignment_id
,evts.job_id job_id
,evts.organization_id organization_id
,evts.position_id position_id
,evts.grade_id grade_id
,evts.headcount headcount_value
,evts.fte fte_value
,DECODE(orgmgr.sub_relative_level, 0, 1, 0) direct_ind
,-1 metric_adjust_multiplier
,orgmgr.sup_level supervisor_level
,sub_assignment_id sub_assignment_id
FROM
hri_mb_asgn_events_ct evts
,hri_cs_suph_orgmgr_ct orgmgr
WHERE orgmgr.sub_person_id = evts.supervisor_id
AND (orgmgr.effective_end_date BETWEEN evts.effective_change_date AND evts.effective_change_end_date
OR evts.effective_change_end_date BETWEEN orgmgr.effective_start_date AND orgmgr.effective_end_date)
AND LEAST(orgmgr.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 orgmgr.sup_person_id = p_person_id ) dlt
GROUP BY
dlt.supervisor_person_id
,dlt.effective_start_date
,dlt.effective_end_date
,dlt.organization_id
,dlt.job_id
,dlt.position_id
,dlt.grade_id;
dbg(SQL%ROWCOUNT||' records inserted into HRI_MAP_WRKFC_ORGMGR_CT');
SELECT DISTINCT sup_person_id
FROM hri_cs_suph_orgmgr_ct
WHERE sup_person_id BETWEEN p_start_object_id and p_end_object_id;
'SELECT /*+ parallel (ASG_EVT, default, default) */
DISTINCT person_id object_id
FROM hri_mb_asgn_events_ct asg_evt
ORDER BY person_id';
dbg('Full Refresh selected - Creating indexes');
dbg('Full Refresh selected - gathering stats');
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 sup_person_id object_id
FROM hri_cs_suph_orgmgr_ct
ORDER BY sup_person_id)
)
GROUP BY mthd_range_id;