[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT /*+ APPEND */
INTO HRI_CL_WKR_SUP_STATUS_CT
(person_id
,effective_start_date
,effective_end_date
,supervisor_flag
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
SELECT chgs.person_id,
chgs.effective_date effective_start_date,
least(nvl((LEAD(chgs.effective_date, 1)
OVER (PARTITION BY chgs.person_id
ORDER BY chgs.effective_date)) - 1,
chgs.termination_date),
chgs.termination_date) effective_end_date,
decode(chgs.leaf_indicator,1,'N','Y'),
l_current_time,
l_user_id,
l_user_id,
l_user_id,
l_current_time
FROM --
-- Calculate supervisory status (leaf node) status for every person on any particular date
-- Use an analytic function to get previous leaf node status
--
(SELECT /*+ USE_HASH(asg ast leaf_date) */
leaf_date.event_supervisor_id person_id,
leaf_date.effective_date effective_date,
NVL(pos.actual_termination_date, l_end_of_time) termination_date,
--
-- If there is no asg status reporting to a person then he is not a
-- supervisor
--
DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
--
-- The leaf_indicator_prev column returns a person's supervisory status
-- on a previous effective date. However when a person has been re-hired
-- and if the records are not contiguous. Then two records should be
-- created even if his supervisory status is unchanged.
-- We don't want his records for duration he was not there
-- with the organization
--
CASE WHEN leaf_date.effective_date - 1 =
NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date), l_end_of_time)
THEN
--
-- 4099447 When the person is rehired the next day, then two records
-- two different records should be created. Return the leaf_indicator_prev
-- as null
--
NULL
WHEN leaf_date.effective_date - 1 BETWEEN
LAG(leaf_date.effective_date ,1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
THEN
--
-- records are contiguous. Return the prev_leaf_indicator status
--
LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
ELSE
--
-- previous records and current record is not contiguous
-- return null. So that the present record does not get
-- filtered out.
--
null
END leaf_indicator_prev,
NVL(pos.actual_termination_date, l_end_of_time)
FROM (--
-- Using a inline view as oracle doesn't like outer joins with in clause..
--
SELECT supervisor_id,
effective_start_date,
effective_end_date,
assignment_status_type_id
FROM per_all_assignments_f
WHERE assignment_type in ('E','C')
AND primary_flag = 'Y'
) asg,
per_assignment_status_types ast,
(select person_id,
date_start,
actual_termination_date
from per_periods_of_service
UNION ALL
select person_id,
date_start,
actual_termination_date
from per_periods_of_placement
)pos,
(--
-- This gets all supervisors whose subordinates have had events that
-- can affects his supervisory status
--
SELECT CASE WHEN WORKER_TERM_IND = 1 THEN
--
-- For the termination records get the person's
-- previous supervisor_id, as the supervisor_id
-- is set to -1
--
evt.supervisor_prv_id
ELSE
evt.supervisor_id
END event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt
WHERE (
(--
-- get only those asg records which have had a
-- change in supervisor
--
supervisor_change_ind = 1
--
-- or change in the primary assignment
--
OR primary_flag <> primary_flag_prv
--
-- or if the event record is a retrospective
-- record
--
OR asg_rtrspctv_strt_event_ind = 1
)
AND evt.supervisor_id <> -1
)
AND PRIMARY_FLAG = 'Y'
UNION
--
-- The Previous query will only get the assignment events records
-- for Transfer In. But Transfer Out's also affect a person's
-- supervisory status. Get all the transfer out events
--
SELECT evt.supervisor_prv_id event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt
WHERE (
(
(supervisor_change_ind = 1
OR worker_term_ind = 1
)
AND primary_flag = 'Y'
)
OR
(evt.primary_flag_prv='Y'
AND evt.primary_flag='N'
)
)
AND evt.supervisor_prv_id <> -1
UNION
--
-- Gets all active person's
--
SELECT pos.person_id,
GREATEST(p_collect_from,pos.date_start)
FROM per_periods_of_service pos
WHERE (p_collect_from BETWEEN pos.date_start AND NVL(pos.actual_termination_date, hr_general.end_of_time)
OR p_collect_from <= pos.date_start)
--
-- Gets all active contingent workers
--
UNION
SELECT pop.person_id,
GREATEST(p_collect_from,pop.date_start)
FROM per_periods_of_placement pop
WHERE (p_collect_from BETWEEN pop.date_start AND NVL(pop.actual_termination_date, hr_general.end_of_time)
OR p_collect_from <= pop.date_start)
)leaf_date
WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
AND leaf_date.event_supervisor_id = pos.person_id
AND leaf_date.effective_date BETWEEN pos.date_start
AND NVL(pos.actual_termination_date, l_end_of_time)
AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
AND ast.per_system_status (+) <> 'TERM_ASSIGN'
AND leaf_date.effective_date BETWEEN asg.effective_start_date (+) AND asg.effective_end_date (+)
GROUP BY leaf_date.event_supervisor_id, leaf_date.effective_date, pos.actual_termination_date
)chgs
WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
INSERT /*+ APPEND */
INTO hri_cl_wkr_sup_status_ct
(person_id
,effective_start_date
,effective_end_date
,supervisor_flag
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date
)
SELECT chgs.person_id,
chgs.effective_date effective_start_date,
least(nvl((LEAD(chgs.effective_date, 1)
OVER (PARTITION BY chgs.person_id
ORDER BY chgs.effective_date)) - 1,
chgs.termination_date),
chgs.termination_date) effective_end_date,
decode(chgs.leaf_indicator,1,'N','Y'),
l_current_time,
l_user_id,
l_user_id,
l_user_id,
l_current_time
FROM --
-- Calculate supervisory status (leaf node) status for every person on any particular date
-- Use an analytic function to get previous leaf node status
--
(SELECT /*+ USE_HASH(asg ast leaf_date) */
leaf_date.event_supervisor_id person_id,
leaf_date.effective_date effective_date,
NVL(pos.actual_termination_date, l_end_of_time) termination_date,
--
-- If there is no asg status reporting to a person then he is not a
-- supervisor
--
DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
--
-- The leaf_indicator_prev column returns a person's supervisory status
-- on a previous effective date. However when a person has been re-hired
-- and if the records are not contiguous. Then two records should be
-- created even if his supervisory status is unchanged.
-- We don't want his records for duration he was not there
-- with the organization
--
CASE WHEN leaf_date.effective_date - 1 =
NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date), l_end_of_time)
THEN
--
-- 4099447 When the person is rehired the next day, then two records
-- two different records should be created. Return the leaf_indicator_prev
-- as null
--
NULL
WHEN leaf_date.effective_date - 1 BETWEEN
LAG(leaf_date.effective_date ,1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
THEN
--
-- records are contiguous. Return the prev_leaf_indicator status
--
LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
ELSE
--
-- previous records and current record is not contiguous
-- return null. So that the present record does not get
-- filtered out.
--
null
END leaf_indicator_prev,
NVL(pos.actual_termination_date, l_end_of_time)
FROM (--
-- Using a inline view as oracle doesn't like outer joins with in clause..
--
SELECT supervisor_id,
effective_start_date,
effective_end_date,
assignment_status_type_id
FROM per_all_assignments_f
WHERE assignment_type in ('E','C')
AND primary_flag = 'Y'
) asg,
per_assignment_status_types ast,
--
-- CWK Change
--
(select pos.person_id,
pos.date_start,
pos.actual_termination_date
from per_periods_of_service pos,
hri_eq_sprvsr_hstry_chgs eq
where eq.person_id=pos.person_id
UNION ALL
select pop.person_id,
pop.date_start,
pop.actual_termination_date
from per_periods_of_placement pop,
hri_eq_sprvsr_hstry_chgs eq
WHERE eq.person_id=pop.person_id
)pos,
(--
-- This gets all supervisors whose subordinates have had events that
-- can affects his supervisory status
--
SELECT CASE WHEN WORKER_TERM_IND = 1 THEN
--
-- For the termination records get the person's
-- previous supervisor_id, as the supervisor_id
-- is set to -1
--
evt.supervisor_prv_id
ELSE
evt.supervisor_id
END event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt,
hri_eq_sprvsr_hstry_chgs eq
WHERE (
(--
-- get only those asg records which have had a
-- change in supervisor
--
supervisor_change_ind = 1
--
-- or change in the primary assignment
--
OR primary_flag <> primary_flag_prv
--
-- or if the event record is a retrospective
-- record
--
OR asg_rtrspctv_strt_event_ind = 1
)
AND evt.supervisor_id <> -1
)
AND PRIMARY_FLAG = 'Y'
AND eq.person_id=evt.supervisor_id
UNION
--
-- The Previous query will only get the assignment events records
-- for Transfer In. But Transfer Out's also affect a person's
-- supervisory status. Get all the transfer out events
--
SELECT evt.supervisor_prv_id event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt,
hri_eq_sprvsr_hstry_chgs eq
WHERE (
(
(supervisor_change_ind = 1
OR worker_term_ind = 1
)
AND primary_flag = 'Y'
)
OR
(evt.primary_flag_prv='Y'
AND evt.primary_flag='N'
)
)
AND evt.supervisor_prv_id <> -1
AND eq.person_id = evt.supervisor_prv_id
UNION
--
-- Gets all active person's
--
SELECT pos.person_id,
GREATEST(hri_bpl_parameter.get_bis_global_start_date
,pos.date_start)
FROM per_periods_of_service pos,
hri_eq_sprvsr_hstry_chgs eq
WHERE eq.person_id=pos.person_id
UNION
--
-- Gets all active placements
--
SELECT pop.person_id,
GREATEST(hri_bpl_parameter.get_bis_global_start_date
,pop.date_start)
FROM per_periods_of_placement pop,
hri_eq_sprvsr_hstry_chgs eq
WHERE eq.person_id=pop.person_id
) leaf_date
WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
AND leaf_date.event_supervisor_id = pos.person_id
AND leaf_date.effective_date BETWEEN pos.date_start
AND NVL(pos.actual_termination_date, l_end_of_time)
AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
AND ast.per_system_status (+) <> 'TERM_ASSIGN'
AND leaf_date.effective_date BETWEEN asg.effective_start_date (+) AND asg.effective_end_date (+)
GROUP BY leaf_date.event_supervisor_id, leaf_date.effective_date, pos.actual_termination_date
)chgs
WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
INSERT /*+ APPEND */
INTO HRI_CL_WKR_SUP_STATUS_ASG_CT
(person_id
,effective_start_date
,effective_end_date
,supervisor_flag
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
SELECT chgs.person_id,
chgs.effective_date effective_start_date,
least(nvl((LEAD(chgs.effective_date, 1)
OVER (PARTITION BY chgs.person_id
ORDER BY chgs.effective_date)) - 1,
chgs.termination_date),
chgs.termination_date) effective_end_date,
decode(chgs.leaf_indicator,1,'N','Y'),
l_current_time,
l_user_id,
l_user_id,
l_user_id,
l_current_time
FROM --
-- Calculate supervisory status (leaf node) status for every person on any particular date
-- Use an analytic function to get previous leaf node status
--
(SELECT /*+ USE_HASH(asg ast leaf_date) */
leaf_date.event_supervisor_id person_id,
leaf_date.effective_date effective_date,
NVL(pos.actual_termination_date, l_end_of_time) termination_date,
--
-- If there is no asg status reporting to a person then he is not a
-- supervisor
--
DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
--
-- The leaf_indicator_prev column returns a person's supervisory status
-- on a previous effective date. However when a person has been re-hired
-- and if the records are not contiguous. Then two records should be
-- created even if his supervisory status is unchanged.
-- We don't want his records for duration he was not there
-- with the organization
--
CASE WHEN leaf_date.effective_date - 1 =
NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date), l_end_of_time)
THEN
--
-- 4099447 When the person is rehired the next day, then two records
-- two different records should be created. Return the leaf_indicator_prev
-- as null
--
NULL
WHEN leaf_date.effective_date - 1 BETWEEN
LAG(leaf_date.effective_date ,1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
THEN
--
-- records are contiguous. Return the prev_leaf_indicator status
--
LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
ELSE
--
-- previous records and current record is not contiguous
-- return null. So that the present record does not get
-- filtered out.
--
null
END leaf_indicator_prev,
NVL(pos.actual_termination_date, l_end_of_time)
FROM (--
-- Using a inline view as oracle doesn't like outer joins with in clause..
--
SELECT supervisor_id,
effective_start_date,
effective_end_date,
assignment_status_type_id
FROM per_all_assignments_f
WHERE assignment_type in ('E','C')
) asg,
per_assignment_status_types ast,
(select person_id,
date_start,
actual_termination_date
from per_periods_of_service
UNION ALL
select person_id,
date_start,
actual_termination_date
from per_periods_of_placement
)pos,
(--
-- This gets all supervisors whose subordinates have had events that
-- can affects his supervisory status
--
SELECT evt.supervisor_id event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt
WHERE (worker_hire_ind = 1
OR post_hire_asgn_start_ind = 1
OR supervisor_change_ind = 1
OR asg_rtrspctv_strt_event_ind = 1)
AND evt.supervisor_id <> -1
UNION
--
-- The Previous query will only get the assignment events records
-- for Transfer In. But Transfer Out's also affect a person's
-- supervisory status. Get all the transfer out events
--
SELECT evt.supervisor_prv_id event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt
WHERE (supervisor_change_ind = 1
OR worker_term_ind = 1
OR pre_sprtn_asgn_end_ind = 1)
AND evt.supervisor_prv_id <> -1
UNION
--
-- Gets all active employees
--
SELECT pos.person_id, GREATEST(p_collect_from,pos.date_start)
FROM per_periods_of_service pos
WHERE (p_collect_from BETWEEN pos.date_start
AND NVL(pos.actual_termination_date, hr_general.end_of_time)
OR p_collect_from <= pos.date_start)
--
-- Gets all active contingent workers
--
UNION
SELECT pop.person_id, GREATEST(p_collect_from,pop.date_start)
FROM per_periods_of_placement pop
WHERE (p_collect_from BETWEEN pop.date_start
AND NVL(pop.actual_termination_date, hr_general.end_of_time)
OR p_collect_from <= pop.date_start)
)leaf_date
WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
AND leaf_date.event_supervisor_id = pos.person_id
AND leaf_date.effective_date BETWEEN pos.date_start
AND NVL(pos.actual_termination_date, l_end_of_time)
AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
AND ast.per_system_status (+) <> 'TERM_ASSIGN'
AND leaf_date.effective_date BETWEEN asg.effective_start_date (+)
AND asg.effective_end_date (+)
GROUP BY
leaf_date.event_supervisor_id
,leaf_date.effective_date
,pos.actual_termination_date
) chgs
WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
INSERT /*+ APPEND */
INTO hri_cl_wkr_sup_status_asg_ct
(person_id
,effective_start_date
,effective_end_date
,supervisor_flag
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date
)
SELECT chgs.person_id,
chgs.effective_date effective_start_date,
least(nvl((LEAD(chgs.effective_date, 1)
OVER (PARTITION BY chgs.person_id
ORDER BY chgs.effective_date)) - 1,
chgs.termination_date),
chgs.termination_date) effective_end_date,
decode(chgs.leaf_indicator,1,'N','Y'),
l_current_time,
l_user_id,
l_user_id,
l_user_id,
l_current_time
FROM --
-- Calculate supervisory status (leaf node) status for every person on any particular date
-- Use an analytic function to get previous leaf node status
--
(SELECT /*+ USE_HASH(asg ast leaf_date) */
leaf_date.event_supervisor_id person_id,
leaf_date.effective_date effective_date,
NVL(pos.actual_termination_date, l_end_of_time) termination_date,
--
-- If there is no asg status reporting to a person then he is not a
-- supervisor
--
DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
--
-- The leaf_indicator_prev column returns a person's supervisory status
-- on a previous effective date. However when a person has been re-hired
-- and if the records are not contiguous. Then two records should be
-- created even if his supervisory status is unchanged.
-- We don't want his records for duration he was not there
-- with the organization
--
CASE WHEN leaf_date.effective_date - 1 =
NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date), l_end_of_time)
THEN
--
-- 4099447 When the person is rehired the next day, then two records
-- two different records should be created. Return the leaf_indicator_prev
-- as null
--
NULL
WHEN leaf_date.effective_date - 1 BETWEEN
LAG(leaf_date.effective_date ,1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
THEN
--
-- records are contiguous. Return the prev_leaf_indicator status
--
LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
OVER (PARTITION BY leaf_date.event_supervisor_id
ORDER BY leaf_date.effective_date)
ELSE
--
-- previous records and current record is not contiguous
-- return null. So that the present record does not get
-- filtered out.
--
null
END leaf_indicator_prev,
NVL(pos.actual_termination_date, l_end_of_time)
FROM (--
-- Using a inline view as oracle doesn't like outer joins with in clause..
--
SELECT supervisor_id,
effective_start_date,
effective_end_date,
assignment_status_type_id
FROM per_all_assignments_f
WHERE assignment_type in ('E','C')
) asg,
per_assignment_status_types ast,
--
-- CWK Change
--
(select pos.person_id,
pos.date_start,
pos.actual_termination_date
from per_periods_of_service pos,
hri_eq_sprvsr_hstry_chgs eq
where eq.person_id=pos.person_id
UNION ALL
select pop.person_id,
pop.date_start,
pop.actual_termination_date
from per_periods_of_placement pop,
hri_eq_sprvsr_hstry_chgs eq
WHERE eq.person_id=pop.person_id
)pos,
(--
-- This gets all supervisors whose subordinates have had events that
-- can affects his supervisory status
--
SELECT evt.supervisor_id event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt,
hri_eq_sprvsr_hstry_chgs eq
WHERE (worker_hire_ind = 1
OR post_hire_asgn_start_ind = 1
OR supervisor_change_ind = 1
OR asg_rtrspctv_strt_event_ind = 1)
AND evt.supervisor_id <> -1
AND eq.person_id=evt.supervisor_id
UNION
--
-- The Previous query will only get the assignment events records
-- for Transfer In. But Transfer Out's also affect a person's
-- supervisory status. Get all the transfer out events
--
SELECT evt.supervisor_prv_id event_supervisor_id,
evt.effective_change_date effective_date
FROM hri_mb_asgn_events_ct evt,
hri_eq_sprvsr_hstry_chgs eq
WHERE (supervisor_change_ind = 1
OR worker_term_ind = 1
OR pre_sprtn_asgn_end_ind = 1)
AND evt.supervisor_prv_id <> -1
AND eq.person_id = evt.supervisor_prv_id
UNION
--
-- Gets all active person's
--
SELECT pos.person_id,
GREATEST(hri_bpl_parameter.get_bis_global_start_date
,pos.date_start)
FROM per_periods_of_service pos,
hri_eq_sprvsr_hstry_chgs eq
WHERE eq.person_id=pos.person_id
UNION
--
-- Gets all active placements
--
SELECT pop.person_id,
GREATEST(hri_bpl_parameter.get_bis_global_start_date
,pop.date_start)
FROM per_periods_of_placement pop,
hri_eq_sprvsr_hstry_chgs eq
WHERE eq.person_id=pop.person_id
) leaf_date
WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
AND leaf_date.event_supervisor_id = pos.person_id
AND leaf_date.effective_date BETWEEN pos.date_start
AND NVL(pos.actual_termination_date, l_end_of_time)
AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
AND ast.per_system_status (+) <> 'TERM_ASSIGN'
AND leaf_date.effective_date BETWEEN asg.effective_start_date (+)
AND asg.effective_end_date (+)
GROUP BY
leaf_date.event_supervisor_id
,leaf_date.effective_date
,pos.actual_termination_date
) chgs
WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
PROCEDURE update_event_queue IS
--
BEGIN
--
dbg('Inside update_event_queue');
UPDATE hri_eq_sprvsr_hstry_chgs eq
SET person_id = (SELECT person_id
FROM per_all_assignments_f asg
WHERE eq.assignment_id=asg.assignment_id
AND rownum=1
);
dbg('Exiting update_event_queue');
g_msg_sub_group := NVL(g_msg_sub_group, 'UPDATE_EVENT_QUEUE');
END update_event_queue;
dbg('Calling update_event_queue');
update_event_queue;
INSERT /*+ APPEND */ INTO hri_eq_sprvsr_hstry_chgs
(person_id
,erlst_evnt_effective_date
,source_code)
SELECT DISTINCT
evt.supervisor_id
,eq.erlst_evnt_effective_date
,'DERIVED'
FROM
hri_eq_sprvsr_hstry_chgs eq
,hri_mb_asgn_events_ct evt
WHERE eq.assignment_id = evt.assignment_id
AND evt.effective_change_end_date >= eq.erlst_evnt_effective_date
AND eq.source_code IS NULL
AND NOT EXISTS
(SELECT null
FROM hri_eq_sprvsr_hstry_chgs eq2
WHERE eq2.person_id = evt.supervisor_id);
INSERT /*+ APPEND */ INTO hri_eq_sprvsr_hstry_chgs
(person_id
,erlst_evnt_effective_date
,source_code)
SELECT DISTINCT
asg.supervisor_id
,eq.erlst_evnt_effective_date
,'DERIVED'
FROM
hri_eq_sprvsr_hstry_chgs eq
,per_all_assignments_f asg
WHERE eq.assignment_id = asg.assignment_id
AND asg.effective_start_date >= eq.erlst_evnt_effective_date
AND eq.source_code IS NULL
AND NOT EXISTS
(SELECT null
FROM hri_eq_sprvsr_hstry_chgs eq2
WHERE eq2.person_id = asg.supervisor_id);
DELETE FROM hri_eq_sprvsr_hstry_chgs eq
WHERE eq.source_code IS NULL
AND eq.person_id IN
(SELECT
pps.person_id
FROM
hri_eq_sprvsr_hstry_chgs eq2
,per_periods_of_service pps
WHERE eq2.person_id = pps.person_id
AND pps.date_start <> eq2.erlst_evnt_effective_date
AND pps.actual_termination_date IS NULL);
DELETE FROM hri_eq_sprvsr_hstry_chgs eq
WHERE eq.source_code IS NULL
AND eq.person_id IN
(SELECT
ppp.person_id
FROM
hri_eq_sprvsr_hstry_chgs eq2
,per_periods_of_placement ppp
WHERE eq2.person_id = ppp.person_id
AND ppp.date_start <> eq2.erlst_evnt_effective_date
AND ppp.actual_termination_date IS NULL);
PROCEDURE delete_old_supervisor_status IS
--
BEGIN
--
dbg('Inside delete_old_supervisor_status');
DELETE HRI_CL_WKR_SUP_STATUS_CT
WHERE person_id in (SELECT person_id
FROM hri_eq_sprvsr_hstry_chgs
);
dbg('Exiting delete_old_supervisor_status');
dbg('An error occured while deleteing old supervisor status records.');
g_msg_sub_group := NVL(g_msg_sub_group, 'DELETE_OLD_SUPERVISOR_STATUS');
END delete_old_supervisor_status;
PROCEDURE delete_asg_supervisor_status IS
--
BEGIN
--
dbg('Inside delete_asg_supervisor_status');
DELETE HRI_CL_WKR_SUP_STATUS_ASG_CT
WHERE person_id in (SELECT person_id
FROM hri_eq_sprvsr_hstry_chgs
);
dbg('Exiting delete_asg_supervisor_status');
dbg('An error occured while deleteing asg supervisor status records.');
g_msg_sub_group := NVL(g_msg_sub_group, 'DELETE_OLD_SUPERVISOR_STATUS');
END delete_asg_supervisor_status;
PROCEDURE incremental_update( p_start_date IN DATE,
p_end_date IN DATE) IS
--
l_effective_start_date DATE;
dbg('Inside incremental_update');
dbg('Starting incremental Update ...');
dbg('Calling delete_old_supervisor_status...');
delete_old_supervisor_status;
dbg('Calling delete_asg_supervisor_status...');
delete_asg_supervisor_status;
dbg('Exiting incremental_update');
g_msg_sub_group := NVL(g_msg_sub_group, 'INCREMENTAL_UPDATE');
END incremental_update;
PROCEDURE incremental_update( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_debugging IN VARCHAR2 DEFAULT 'N') IS
--
l_start_date DATE ;
incremental_update( p_start_date => l_start_date,
p_end_date => l_end_date);
g_msg_sub_group := NVL(g_msg_sub_group, 'INCREMENTAL_UPDATE');
dbg('Calling incremental update of supervisor status history');
hri_opl_sup_status_hst.incremental_update(p_start_date => l_start_date
,p_end_date => l_end_date);