[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_direct_insert CONSTANT BOOLEAN := FALSE;
g_insert_cache t_effdt_cache_table;
SELECT sup_person_id,
sub_relative_level,
0 row_number
FROM hri_cs_suph_v
WHERE sub_person_id = cp_sup_id
AND sub_relative_level > 0
AND cp_eff_dt BETWEEN effective_start_date AND effective_end_date
ORDER BY sub_relative_level ASC;
g_start_full_msg := 'Begin summary refresh full update run...';
g_start_delta_msg := 'Begin summary refresh delta update run...';
SELECT asg.supervisor_id supervisor_id,
MIN(pos.actual_termination_date-1) effective_start_date,
MAX(pos.actual_termination_date+1) effective_end_date
FROM per_all_assignments_f asg
, per_periods_of_service pos
WHERE asg.supervisor_id IS NOT NULL
AND asg.period_of_service_id = pos.period_of_service_id
AND pos.actual_termination_date <= TRUNC(SYSDATE)
AND asg.effective_end_date = pos.actual_termination_date
AND pos.actual_termination_date BETWEEN cp_start AND cp_end
GROUP BY asg.supervisor_id;
msg('Deleted all rows');
CURSOR get_deletes_todo(
cp_id IN NUMBER,
cp_st IN DATE,
cp_en IN DATE
) IS
SELECT summary_context_id supervisor_id,
effective_date,
sum_information4 voluntary_char,
sum_information5 involuntary_char
FROM hr_ptl_summary_data
WHERE effective_date BETWEEN cp_st AND cp_en
AND summary_context_id = cp_id
AND sum_information_category = information_category;
FOR del_rec IN get_deletes_todo(
p_supv_tab(i).supervisor_id,
p_supv_tab(i).effective_start_date,
p_supv_tab(i).effective_end_date
) LOOP
l_loop := l_loop + 1;
'Processing deletes for '||
p_supv_tab(i).supervisor_id||' between '||
p_supv_tab(i).effective_start_date||' and '||
p_supv_tab(i).effective_end_date
);
UPDATE hr_ptl_summary_data
SET sum_information4 = TO_NUMBER(sum_information4) - TO_NUMBER(del_rec.voluntary_char),
sum_information5 = TO_NUMBER(sum_information5) - TO_NUMBER(del_rec.involuntary_char)
WHERE sum_information2 = TO_CHAR(sup_rec.sup_person_id)
AND sum_information3 = TO_CHAR(l_sub_id)
AND sum_information1 = fnd_date.date_to_canonical(del_rec.effective_date)
AND sum_information_category = information_category;
dbg('No rows returned by get_deletes_todo for '||
p_supv_tab(i).supervisor_id||' '||
fnd_date.date_to_canonical(p_supv_tab(i).effective_start_date)||' '||
fnd_date.date_to_canonical(p_supv_tab(i).effective_end_date)||
' (data not previously record in summary, not an error)'
);
DELETE
FROM hr_ptl_summary_data
WHERE effective_date BETWEEN
p_supv_tab(i).effective_start_date AND
p_supv_tab(i).effective_end_date
AND summary_context_id = p_supv_tab(i).supervisor_id
AND sum_information_category = information_category;
' deleted '||SQL%ROWCOUNT||' rows'
);
PROCEDURE do_insert(
p_eff_dt IN DATE,
p_supv_id IN NUMBER,
p_sub_supv_id IN NUMBER,
p_vol_sep IN NUMBER,
p_invol_sep IN NUMBER
) IS
BEGIN
dbg(
'Inserting new row for '||p_supv_id||'/'||p_sub_supv_id||'/'||p_eff_dt||
' data '||p_vol_sep||'/'||p_invol_sep
);
INSERT INTO hr_ptl_summary_data (
summary_data_id,
summary_context_type,
summary_context_id,
effective_date,
created_by,
creation_date,
object_version_number,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
sum_information_category,
sum_information1,
sum_information2,
sum_information3,
sum_information4,
sum_information5
) VALUES (
hr_ptl_summary_data_s.NEXTVAL,
context_type,
p_supv_id,
p_eff_dt,
g_who_id,
g_who_date,
g_who_version,
g_who_id,
g_who_date,
g_who_login,
g_who_request,
g_who_application,
g_who_program,
g_who_date,
information_category,
fnd_date.date_to_canonical(p_eff_dt),
TO_CHAR(p_supv_id),
TO_CHAR(p_sub_supv_id),
TO_CHAR(p_vol_sep),
TO_CHAR(p_invol_sep)
);
END do_insert;
PROCEDURE insert_cache_data(
p_supv_id IN NUMBER,
p_sub_supv_id IN NUMBER,
p_eff_dt IN DATE,
p_vol_sep IN NUMBER,
p_invol_sep IN NUMBER
) IS
--
-- Convert the date to a number to enable use to use it
-- as a hash key into a PL/SQL table
l_dt_num NUMBER := TO_NUMBER(TO_CHAR(p_eff_dt,'YYYYMMDD'));
IF NOT g_insert_cache.EXISTS(l_dt_num) THEN
g_insert_cache(l_dt_num).effective_date := p_eff_dt;
IF NOT g_insert_cache(l_dt_num).supervisor_rows.EXISTS(p_supv_id) THEN
g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).supervisor_id := p_supv_id;
IF NOT g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows.EXISTS(p_sub_supv_id) THEN
g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).subordinate_id := p_sub_supv_id;
g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).voluntary_seps := 0;
g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).involuntary_seps := 0;
l_vol_sep := g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).voluntary_seps;
l_invol_sep := g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).involuntary_seps;
g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).voluntary_seps := l_vol_sep + p_vol_sep;
g_insert_cache(l_dt_num).supervisor_rows(p_supv_id).subordinate_rows(p_sub_supv_id).involuntary_seps := l_invol_sep + p_invol_sep;
END insert_cache_data;
PROCEDURE flush_insert_cache IS
--
l_effdt NUMBER;
IF NOT g_direct_insert THEN
--
-- Loop over all the effective dates for which we cached data
l_effdt := g_insert_cache.FIRST;
l_supv := g_insert_cache(l_effdt).supervisor_rows.FIRST;
l_subor := g_insert_cache(l_effdt).supervisor_rows(l_supv).subordinate_rows.FIRST;
do_insert(
g_insert_cache(l_effdt).effective_date,
g_insert_cache(l_effdt).supervisor_rows(l_supv).supervisor_id,
g_insert_cache(l_effdt).supervisor_rows(l_supv).subordinate_rows(l_subor).subordinate_id,
g_insert_cache(l_effdt).supervisor_rows(l_supv).subordinate_rows(l_subor).voluntary_seps,
g_insert_cache(l_effdt).supervisor_rows(l_supv).subordinate_rows(l_subor).involuntary_seps
);
l_subor := g_insert_cache(l_effdt).supervisor_rows(l_supv).subordinate_rows.NEXT(l_subor);
l_supv := g_insert_cache(l_effdt).supervisor_rows.NEXT(l_supv);
l_effdt := g_insert_cache.NEXT(l_effdt);
g_insert_cache.DELETE;
END flush_insert_cache;
PROCEDURE insert_summary_data(
p_supv_id IN NUMBER,
p_sub_supv_id IN NUMBER,
p_eff_dt IN DATE,
p_vol_sep IN NUMBER,
p_invol_sep IN NUMBER
) IS
--
-- Cursor to see if we've already inserted a row
-- for this date/supervisor/subordinate
CURSOR chk_exists(
cp_eff_dt IN VARCHAR2,
cp_sup_id IN VARCHAR2,
cp_sub_id IN VARCHAR2
) IS
-- Get rowid for fast updates and the current separation figures
SELECT rowid,
sum_information4,
sum_information5
FROM hr_ptl_summary_data
-- Make sure we're looking at the right rows
WHERE sum_information_category = information_category
-- Use the text versions of the foreign keys to hit the index
AND sum_information1 = cp_eff_dt
AND sum_information2 = cp_sup_id
AND sum_information3 = cp_sub_id;
UPDATE hr_ptl_summary_data
SET sum_information4 = TO_CHAR(TO_NUMBER(l_vol) + p_vol_sep),
sum_information5 = TO_CHAR(TO_NUMBER(l_invol) + p_invol_sep)
WHERE rowid = l_rid;
do_insert(p_eff_dt,p_supv_id,p_sub_supv_id,p_vol_sep,p_invol_sep);
END insert_summary_data;
PROCEDURE proxy_insert_data(
p_supv_id IN NUMBER,
p_sub_supv_id IN NUMBER,
p_eff_dt IN DATE,
p_vol_sep IN NUMBER,
p_invol_sep IN NUMBER
) IS
BEGIN
--
-- Insert directly into the table, a performance hit, since it first
-- has to select back from the table to see if the row's already there
IF g_direct_insert THEN
insert_summary_data(
p_supv_id,
p_sub_supv_id,
p_eff_dt,
p_vol_sep,
p_invol_sep
);
insert_cache_data(
p_supv_id,
p_sub_supv_id,
p_eff_dt,
p_vol_sep,
p_invol_sep
);
END proxy_insert_data;
SELECT
pos.actual_termination_date + 1 effective_date,
SUM(DECODE(scr.separation_category_code,
involuntary_code, 0, -- Involuntary is zero
DECODE(g_global_wmt,
'FTE', wmv.fte,
'HEAD', wmv.head,
0))) voluntary_separations,
SUM(DECODE(scr.separation_category_code,
involuntary_code, DECODE(g_global_wmt,
'FTE', wmv.fte,
'HEAD', wmv.head,
0),
0)) involuntary_separations
FROM
per_all_assignments_f asg
, per_periods_of_service pos
, per_assignment_status_types ast
, hri_cs_sepcr_v scr
, hri_mb_wmv wmv
WHERE asg.assignment_id = wmv.assignment_id
AND asg.supervisor_id = cp_supv_id
AND asg.period_of_service_id = pos.period_of_service_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND NVL(pos.leaving_reason,'NA_EDW') = scr.separation_reason_code
AND pos.actual_termination_date <= TRUNC(SYSDATE)
AND asg.effective_end_date = pos.actual_termination_date
AND pos.actual_termination_date BETWEEN cp_st_dt
AND cp_en_dt
AND pos.actual_termination_date BETWEEN wmv.effective_start_date
AND wmv.effective_end_date
GROUP BY
pos.actual_termination_date + 1;
proxy_insert_data(
rec.sup_person_id,
direct_report_id,
l_supv_rec.effective_date,
0,
0
);
proxy_insert_data(
rec.sup_person_id,
l_prev_supv,
l_supv_rec.effective_date,
l_supv_rec.voluntary_separations,
l_supv_rec.involuntary_separations
);
proxy_insert_data(
p_supv_tab(i).supervisor_id,
direct_report_id,
l_supv_rec.effective_date,
l_supv_rec.voluntary_separations,
l_supv_rec.involuntary_separations
);
msg('Inserted '||l_dir_tot||' direct report rows');
msg('Inserted '||l_rup_tot||' rollup rows');
flush_insert_cache;