[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO hri_cs_asgn_suph_events_ct
(assignment_id
,effective_start_date
,effective_end_date
,person_id
,supervisor_person_id
,supervisor_assignment_id
,business_group_id
,assignment_type
,primary_flag
,assignment_status_type_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT
chg.assignment_id
,chg.effective_start_date
-- Day before date of next supervisor change or if no further changes
-- then the latest end date for the assignment
,NVL(LEAD(chg.effective_start_date, 1) OVER
(PARTITION BY chg.assignment_id
ORDER BY chg.effective_start_date) - 1
,chg.latest_end_date)
effective_end_date
,chg.person_id
,chg.supervisor_id supervisor_person_id
,TO_NUMBER(NULL) supervisor_assignment_id
,chg.business_group_id
,chg.assignment_type
,chg.primary_flag
,chg.assignment_status_type_id
,g_sysdate
,g_user
,g_user
,g_user
,g_sysdate
FROM
(SELECT
prv.assignment_id
,prv.effective_start_date
-- Latest end date for an active assignment
,LAST_VALUE(prv.effective_end_date) OVER
(PARTITION BY prv.assignment_id
ORDER BY prv.effective_start_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
latest_end_date
,prv.person_id
,prv.supervisor_id
,prv.business_group_id
,prv.assignment_type
,prv.primary_flag
,prv.assignment_status_type_id
-- Previous supervisor value - set first row so that it is always
-- picked up as a change
,NVL(LAG(prv.supervisor_id, 1) OVER
(PARTITION BY prv.assignment_id
ORDER BY prv.effective_start_date)
,-999)
supervisor_prv_id
FROM
(SELECT
asg.assignment_id
,GREATEST(asg.effective_start_date, eq.erlst_evnt_effective_date)
effective_start_date
,asg.effective_end_date
,asg.person_id
,NVL(asg.supervisor_id, -1) supervisor_id
,asg.primary_flag
,asg.assignment_status_type_id
,asg.assignment_type
,asg.business_group_id
FROM
hri_eq_sprvsr_hrchy_chgs eq
,per_all_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_type IN ('E', 'C')
AND asg.primary_flag = 'Y'
AND eq.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
AND asg.effective_end_date >= eq.erlst_evnt_effective_date
AND eq.assignment_id = asg.assignment_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
) prv
) chg
-- Filter out date-tracked records where no supervisor change has occurred
WHERE chg.supervisor_id <> chg.supervisor_prv_id;
INSERT INTO hri_cs_asgn_suph_events_ct
(assignment_id
,effective_start_date
,effective_end_date
,person_id
,supervisor_person_id
,supervisor_assignment_id
,business_group_id
,assignment_type
,primary_flag
,assignment_status_type_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT
chg.assignment_id
,chg.effective_start_date
-- Day before date of next supervisor change or if no further changes
-- then the latest end date for the assignment
,NVL(LEAD(chg.effective_start_date, 1) OVER
(PARTITION BY chg.assignment_id
ORDER BY chg.effective_start_date) - 1
,chg.latest_end_date)
effective_end_date
,chg.person_id
,chg.supervisor_id supervisor_person_id
,TO_NUMBER(NULL) supervisor_assignment_id
,chg.business_group_id
,chg.assignment_type
,chg.primary_flag
,chg.assignment_status_type_id
,g_sysdate
,g_user
,g_user
,g_user
,g_sysdate
FROM
(SELECT
prv.assignment_id
,prv.effective_start_date
-- Latest end date for an active assignment
,LAST_VALUE(prv.effective_end_date) OVER
(PARTITION BY prv.assignment_id
ORDER BY prv.effective_start_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
latest_end_date
,prv.person_id
,prv.supervisor_id
,prv.business_group_id
,prv.assignment_type
,prv.primary_flag
,prv.assignment_status_type_id
-- Previous supervisor value - set first row so that it is always
-- picked up as a change
,NVL(LAG(prv.supervisor_id, 1) OVER
(PARTITION BY prv.assignment_id
ORDER BY prv.effective_start_date)
,-999)
supervisor_prv_id
FROM
(SELECT
asg.assignment_id
,GREATEST(asg.effective_start_date, g_refresh_start_date)
effective_start_date
,asg.effective_end_date
,asg.person_id
,NVL(asg.supervisor_id, -1) supervisor_id
,asg.primary_flag
,asg.assignment_status_type_id
,asg.assignment_type
,asg.business_group_id
FROM
per_all_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_type IN ('E', 'C')
AND asg.primary_flag = 'Y'
AND asg.assignment_id BETWEEN p_start_asg_id AND p_end_asg_id
AND asg.effective_end_date >= g_refresh_start_date
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
) prv
) chg
-- Filter out date-tracked records where no supervisor change has occurred
WHERE chg.supervisor_id <> chg.supervisor_prv_id;
INSERT /*+ APPEND */ INTO hri_cs_asgn_suph_events_ct
(assignment_id
,effective_start_date
,effective_end_date
,person_id
,supervisor_person_id
,supervisor_assignment_id
,business_group_id
,assignment_type
,primary_flag
,assignment_status_type_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT
asg.assignment_id
,GREATEST(NVL(pos.date_start, ppp.date_start),
TRUNC(SYSDATE))
effective_start_date
,NVL(pos.actual_termination_date,
NVL(ppp.actual_termination_date, g_end_of_time))
effective_end_date
,asg.person_id
,NVL(asg.supervisor_id, -1) supervisor_person_id
,to_number(null) supervisor_assignment_id
,asg.business_group_id
,asg.assignment_type
,asg.primary_flag
,asg.assignment_status_type_id
,g_sysdate
,g_user
,g_user
,g_user
,g_sysdate
FROM
per_all_assignments_f asg
,per_assignment_status_types ast
,per_periods_of_service pos
,per_periods_of_placement ppp
WHERE asg.assignment_type IN ('E', 'C')
AND asg.primary_flag = 'Y'
AND asg.effective_end_date >= g_refresh_start_date
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND trunc(SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.period_of_service_id = pos.period_of_service_id (+)
AND asg.person_id = ppp.person_id (+)
AND asg.period_of_placement_date_start = ppp.date_start (+);
'SELECT /*+ PARALLEL(asg, DEFAULT, DEFAULT) */
DISTINCT assignment_id object_id
FROM per_all_assignments_f asg
WHERE assignment_type IN (''E'', ''C'')
AND primary_flag = ''Y''
AND effective_end_date >= to_date(''' ||
to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
''',''DD-MM-YYYY'')
ORDER BY assignment_id';
DELETE FROM hri_cs_asgn_suph_events_ct ase
WHERE ase.rowid IN
(SELECT ase2.rowid
FROM
hri_cs_asgn_suph_events_ct ase2
,hri_eq_sprvsr_hrchy_chgs eq
WHERE eq.assignment_id = ase2.assignment_id
AND ase2.effective_start_date >= eq.erlst_evnt_effective_date);
UPDATE hri_cs_asgn_suph_events_ct ase
SET effective_end_date =
(SELECT (evt.erlst_evnt_effective_date - 1)
FROM hri_eq_sprvsr_hrchy_chgs evt
WHERE evt.assignment_id = ase.assignment_id)
WHERE ase.rowid IN
(SELECT ase2.rowid
FROM
hri_cs_asgn_suph_events_ct ase2
,hri_eq_sprvsr_hrchy_chgs eq
WHERE eq.assignment_id = ase2.assignment_id
AND ase2.effective_end_date >= eq.erlst_evnt_effective_date);
'SELECT /*+ PARALLEL(eq, DEFAULT, DEFAULT) */
assignment_id object_id
FROM hri_eq_sprvsr_hrchy_chgs eq
ORDER BY assignment_id';
SELECT max(assignment_id) INTO l_end_asg_id
FROM per_all_assignments_f;