The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pre_update pa_history_type;
if lower(p_operation) = 'insert' then
-- Check if this record was previously updated/created within this session
-- so it should already be in the rg. then don't add row since it is already in rg.
l_row_touched := row_already_touched(p_row_id);
elsif lower(p_operation) = 'update' then
-- Check if this record was previously updated/created within this session
-- so it should be in the rg. then don't update pre. and pre-update.
l_row_touched := row_already_touched(p_row_id);
-- since this is an update, save the pre values for this row
add_row_pre_update_record_rg(p_old_record_data, indx);
else /* delete */
hr_utility.set_location(l_proc, 35);
Procedure add_row_pre_update_record_rg ( p_pre_update_rg in ghr_pa_history%rowtype,
p_ind in binary_integer) is
l_proc varchar2(30) := 'add_row_pre_update_record_rg';
-- when doing the assignment with p_pre_update directly.
l_hist_data := p_pre_update_rg;
g_pre_update(p_ind) := l_hist_data;
End add_row_pre_update_record_rg ;
for l_ind in 1 .. g_pre_update.count LOOP
null;
g_pre_update.delete;
g_operation_info.delete;
PROCEDURE post_update_process IS
indx binary_integer:=1;
l_proc varchar2(30) := 'post_update_process';
select 'X' from ghr_nature_of_actions
where code in ('300','301','302','303','304','312','317',
'330','350','351','353','355','356','357','385')
and nature_of_action_id = l_session_var.noa_id;
select
assignment_id ,
person_id
from per_all_assignments_f
where position_id = c_position_id
and assignment_type <> 'B';
select
person_id
from per_all_assignments_f
where assignment_id = c_assignment_id
and assignment_type <> 'B';
select
assignment_id
from pay_element_entries_f
where element_entry_id = c_element_entry_id;
-- g_operation_info rg). For every row, the corresponding history information will be inserted into
-- ghr_pa_history.
WHILE indx <= g_operation_info.COUNT
LOOP
hr_utility.set_location(l_proc || 'in post process' || to_char(indx) || g_operation_info(indx).operation, 10);
-- if operation is insert then call fetch_history_info passing l_session_var.date_effective - 1
-- otherwise use l_session_var.dete_effective.
if g_operation_info(indx).operation = 'insert' then
l_pre_effective_end_date := l_session_var.date_effective - 1;
elsif g_operation_info(indx).operation = 'update' then
l_pre_effective_end_date := l_session_var.date_effective;
-- if this is a datetrack table, DML operation was an update, and it is not the correction SF52 then
-- we may use Pre-update from global session
if (lower(g_operation_info(indx).table_name) in (lower(ghr_history_api.g_eleevl_table),
lower(ghr_history_api.g_asgn_table),lower(ghr_history_api.g_peop_table),
lower(ghr_history_api.g_posn_table),
lower(ghr_history_api.g_eleent_table)) AND l_session_var.noa_id_correct is NULL AND
lower(g_operation_info(indx).operation) = 'update') then
hr_utility.set_location(l_proc || 'using pre_update', 9158);
l_pre_record := g_pre_update(indx);
IF g_operation_info(indx).operation = 'insert' THEN
IF l_return_status IS NULL THEN
hr_utility.set_location(l_proc || 'Insert: history found.', 15);
-- inserting with pre-values
l_pre_values_flag := 'Y';
hr_utility.set_location(l_proc || 'Insert: history NOT found.', 20);
-- inserting with no pre-values
l_dml_operation := ghr_history_api.g_ins_operation;
ELSIF g_operation_info(indx).operation = 'update' THEN
-- set pre-record rg
hr_utility.set_location(l_proc || 'Update.', 25);
hr_utility.set_location(l_proc || 'Update: history found.', 15);
hr_utility.set_location(l_proc || 'Update: history NOT found.', 20);
hr_utility.set_location(l_proc || 'Fetch history info for post-update.', 30);
-- get post_update using rowid.
-- Need only pass rowid, other parms should be changed to be nullable.
fetch_history_info(
p_table_name => g_operation_info(indx).table_name,
p_hist_data => l_post_data,
p_row_id => g_operation_info(indx).row_id,
p_result_code => l_return_status);
-- row id then no need of inserting into PA History.
l_rec_avl := TRUE;
-- set post-update-rg
init_record_data(l_session_var, l_post_data, indx);
hr_utility.set_location(l_proc || 'Insert in pa history: effective_date = .' || to_char(l_post_data.effective_date), 40);
hr_utility.set_location(l_proc || 'Insert in pa history: Process_date = .' || to_char(l_post_data.Process_date), 45);
hr_utility.set_location(l_proc || 'Insert in pa history: table_name = .' || l_post_data.table_name, 50);
hr_utility.set_location(l_proc || 'Insert in pa history: pa_request_id = .' || to_char(l_post_data.pa_request_id), 60);
hr_utility.set_location(l_proc || 'Insert in pa history: nature_of_action_id = .' || to_char(l_post_data.nature_of_action_id), 65);
hr_utility.set_location(l_proc || 'Insert in pa history: dml_operation = .' || l_post_data.dml_operation, 75);
-- we need to update the row with the most recent value.
-- retrieve the currently effective eleevl row from history and re-apply it to the
-- core table if necessary.
-- get the row from ghr_pa_history for this element_entry_value given the effective date
-- of this action.
ghr_history_fetch.get_date_eff_eleevl(
p_element_entry_value_id => to_number(l_post_data.information1) ,
p_date_effective => l_post_data.effective_date ,
p_element_entry_data => l_eleevl_data ,
p_result_code => l_return_status ,
p_pa_history_id => l_pa_history_id);
ghr_corr_canc_sf52.update_eleentval( p_hist_pre => l_eleevl_hist_data);
-- need not be cascaded or updated with
-- most recent values because they will only ever be inserted by non-correction sf52's. They
-- will never be updated by non-correction sf52's. Correction's that update these tables
-- are already handled correctly.
null;
END post_update_process;
-- trigger to be fired. This must be set only before post_update_process and
-- not in the form so that validation trigger gets fired for the first time
-- but not at the time of cascacade.
ghr_api.g_api_dml := TRUE;
Post_update_process;
select trunc(effective_date)
from fnd_sessions
where session_id = (select userenv('sessionid')
from dual);