[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO hri_cl_per_snrmgr_ct
(id
,value
,start_date
,end_date
,snapshot_start_date
,snapshot_end_date)
SELECT
hsal.supervisor_person_id
,hsal.supervisor_person_id
,MIN(effective_start_date)
,MAX(effective_end_date)
,MIN(effective_start_date)
,MAX(effective_end_date)
FROM
hri_mdp_sup_wcnt_sup_mv hsal
WHERE hsal.total_headcount > l_senior_mgr_threshold
GROUP BY
hsal.supervisor_person_id;
INSERT INTO hri_cl_per_snap_prds_ct
(id
,value
,snapshot_start_date
,snapshot_end_date
,senior_manager_flag)
SELECT
id
,value
,MIN(snapshot_start_date) snapshot_start_date
,MAX(snapshot_end_date) snapshot_end_date
,DECODE(SUM(senior_manager_ind), 1, 'Y', 'N')
senior_manager_flag
FROM
(SELECT
tab.person_id id
,to_char(tab.person_id) VALUE
,GREATEST(MIN(sub_mgr.effective_start_date),
tab.snapshot_start_date) snapshot_start_date
/* Snapshot period should continue for the length of the longest period */
/* beyond the subordinate having manager status so that snapshots */
/* are available - bug 4300189 */
,LEAST(MAX(sub_mgr.effective_end_date) + g_max_period_length,
tab.snapshot_end_date) snapshot_end_date
,0 senior_manager_ind
FROM
(SELECT /*+ ORDERED USE_NL(sub) */
sub.person_id
,GREATEST(MIN(snrmgr.snapshot_start_date),
MIN(sub.effective_change_date))
snapshot_start_date
,LEAST(MAX(snrmgr.snapshot_end_date),
MAX(sub.effective_change_end_date))
snapshot_end_date
FROM
hri_cl_per_snrmgr_ct snrmgr
,hri_mb_asgn_events_ct sub
WHERE sub.supervisor_id = snrmgr.id
/* Non-terminated primary subordinates */
AND sub.worker_term_ind = 0
AND sub.primary_flag = 'Y'
/* Slicing date join */
AND (sub.effective_change_date BETWEEN snrmgr.snapshot_start_date
AND snrmgr.snapshot_end_date
OR snrmgr.snapshot_start_date BETWEEN sub.effective_change_date
AND sub.effective_change_end_date)
GROUP BY
sub.person_id
) tab
,hri_cl_wkr_sup_status_ct sub_mgr
WHERE tab.person_id = sub_mgr.person_id
/* Who are supervisors during the snapshot period or in the year preceding */
AND sub_mgr.supervisor_flag = 'Y'
/* Slicing Date Join including rolling year (365 days) preceding */
AND (tab.snapshot_start_date - g_max_period_length
BETWEEN sub_mgr.effective_start_date
AND sub_mgr.effective_end_date
OR sub_mgr.effective_start_date
BETWEEN tab.snapshot_start_date - g_max_period_length
AND tab.snapshot_end_date)
GROUP BY
tab.person_id
,tab.snapshot_start_date
,tab.snapshot_end_date
UNION ALL
SELECT
id
,VALUE
,snapshot_start_date
,snapshot_end_date
,1
FROM hri_cl_per_snrmgr_ct
)
GROUP BY
id
,value;