[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
glo_datetrack_ee_tab.delete;
glo_datetrack_ee_hash_tab.delete;
SELECT ptp.start_date start_date,
ptp.end_date end_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND nvl(ppa.date_earned,ppa.effective_date) between ptp.START_DATE and ptp.END_DATE
AND ppa.payroll_id = ptp.payroll_id;
SELECT to_date(rule_mode||'/'||l_year, 'DD/MM/YYYY') start_date
FROM pay_legislation_rules
WHERE legislation_code = p_legislation_code
AND rule_type = 'L' ;
SELECT proration_type, time_definition_id
FROM pay_event_groups
WHERE event_group_id = p_proration_group_id;
SELECT pbg.legislation_code legislation_code,
pbg.business_group_id business_group_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_business_groups_perf pbg
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = pbg.business_group_id ;
SELECT MIN(effective_start_date) esd,
MAX(effective_end_date) eed
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
Lets say the time frame selected by using earlier instructions is
15-JAN-1990 31-OCT-1990
|-----------------------------------------------------------|
Shown below is the life time of the element entry id passed as an input.
|--------------------------------|
13-MAR-1990 15-AUG-1990
We should select the time as 13-MAR-1990 and 15-AUG-1990.
On the parallel lines if the life time of element entry id is
|----------------------------------------------------------|
13-MAR-1990 30-NOV-1990
Then we should select
13-MAR-1990 and 31-OCT-1990 as the time frame.
Similarly if the life time of element entry id is
|----------------------------------------------------------|
01-JAN-1990 30-SEP-1990
Then we should select
15-JAN-1990 and 30-SEP-1990 as the time frame.
**/
--
FOR ces IN c_element_start_end
LOOP
l_esd := ces.esd;
selects the appropriate time periods. This procedure also finds out
the business group id.
***/
time_period(p_assignment_action_id => p_assignment_action_id ,
p_proration_group_id => p_proration_group_id ,
p_element_entry_id => p_element_entry_id ,
p_business_group_id => l_business_group_id ,
p_start_date => l_start_date ,
p_end_date => l_end_date );
SELECT ppa.date_earned
INTO l_date_earned
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id ;
SELECT DISTINCT pee.assignment_id ,
pet.proration_group_id
into p_assignment_id,p_event_group_id
FROM pay_element_entries_f pee,
pay_element_types_f pet
WHERE pee.element_entry_id = p_element_entry_id
AND pee.element_type_id = pet.element_type_id
AND pee.effective_start_date <= l_date_earned
AND pee.effective_end_date >= time_fn(p_assignment_action_id,
pet.proration_group_id ,
p_element_entry_id )
AND l_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date;
SELECT DISTINCT pdt.dated_table_id table_id ,
pdt.table_name table_name ,
nvl(pdt.dyn_trigger_type,'T') dyt_type ,
pdt.start_date_name start_date_name ,
pdt.end_date_name end_date_name ,
pdt.surrogate_key_name surrogate_key_name,
pde.datetracked_event_id datetracked_event_id,
pde.column_name column_name ,
pde.update_type update_type ,
pde.proration_style proration_type,
pdt.owner owner
FROM pay_datetracked_events pde,
pay_dated_tables pdt
WHERE pde.event_group_id = p_proration_group_id
AND pdt.dated_table_id = pde.dated_table_id
order by pdt.dated_table_id,pde.update_type; --ordering vital bug 3598389
p_distinct_tab(l_tab_counter).update_type := cdt.update_type ;
PROCEDURE event_group_table_inserted
(
p_date_counter IN OUT NOCOPY NUMBER,
p_assignment_id IN NUMBER,
p_effective_date IN date,
p_surrogate_key IN NUMBER,
p_business_group_id IN NUMBER,
p_dated_table_id IN NUMBER,
p_start_date_name IN VARCHAR2,
p_end_date_name IN VARCHAR2,
l_proration_type IN VARCHAR2,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output in OUT NOCOPY t_detailed_output_table_type
) AS
insert_row number;
SELECT count(*)
into insert_row
FROM pay_process_events ppe,
pay_event_updates peu
WHERE ppe.assignment_id=p_assignment_id
AND ppe.surrogate_key=p_surrogate_key
AND ppe.business_group_id=p_business_group_id
AND ppe.event_update_id=peu.event_update_id
AND peu.event_type='I'
AND ppe.effective_date=p_effective_date
AND peu.dated_table_id=p_dated_table_id;
SELECT count(*)
INTo upd_end_date
FROM pay_process_events ppe,
pay_event_updates peu
WHERE ppe.assignment_id=p_assignment_id
AND ppe.surrogate_key=p_surrogate_key
AND ppe.business_group_id=p_business_group_id
AND ppe.event_update_id=peu.event_update_id
AND peu.event_type='U'
AND peu.column_name=p_end_date_name
AND ppe.calculation_date+1=p_effective_date
AND peu.dated_table_id=p_dated_table_id;
SELECT count(*)
INTo upd_start_date
FROM pay_process_events ppe,
pay_event_updates peu
WHERE ppe.assignment_id=p_assignment_id
AND ppe.surrogate_key=p_surrogate_key
AND ppe.business_group_id=p_business_group_id
AND ppe.event_update_id=peu.event_update_id
AND peu.event_type='U'
AND peu.column_name=p_start_date_name
AND ppe.calculation_date=p_effective_date
AND peu.dated_table_id=p_dated_table_id;
if (upd_start_date+upd_end_date <> insert_row)
then
t_proration_dates_temp(p_date_counter):= p_effective_date;
t_dynamic_sql.DELETE;
and glo_monitored_events(k).update_type = 'U'
and glo_monitored_events(k).column_name is not null) THEN
p_dynamic_counter := p_dynamic_counter + 1;
p_statement := 'SELECT ' || l_column_string ||
' FROM ' ||
p_table_name ||
' WHERE ' || p_surrogate_key_name || ' = :p_surrogate_key ' ||
' AND :col1 BETWEEN ' ||
p_start_date_name || ' AND ' ||
p_end_date_name;
p_updated_column_name IN VARCHAR2,
p_final_effective_date OUT NOCOPY DATE
) AS
l_dummy NUMBER ;
IF (p_updated_column_name = p_start_date_name) THEN
DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date - 1);
ELSIF (p_updated_column_name = p_end_date_name) THEN
--
DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date);
IF (p_updated_column_name = p_start_date_name) THEN
DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date);
ELSIF (p_updated_column_name = p_end_date_name) THEN
DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':col1', p_effective_date + 1);
SELECT pep.procedure_name
FROM pay_event_procedures pep,
per_business_groups_perf pbg
WHERE pep.dated_table_id = p_table_id
AND UPPER(pep.column_name) = UPPER(p_column_name)
AND nvl(pep.procedure_type, 'E') = 'E'
AND pbg.business_group_id = p_bg_id
AND ( ( pep.business_group_id = pbg.business_group_id
and pep.legislation_code is null)
or ( pep.legislation_code = pbg.legislation_code
and pep.business_group_id is null)
or ( pep.legislation_code is null
and pep.business_group_id is null)
)
ORDER BY NVL(pep.business_group_id, -100) asc,
NVL(pep.legislation_code, ' ') asc;
Order by clause will ensure that the rows that are selected in the following
order i.e. Global, Legislation, and Client specific.
in business_group_id, and legislation_code resp = GLOBAL
XXX in business_group_id, and legislation_code resp = LEGISLATION
XXX in business_group_id, and legislation_code resp = CLIENT specific.
The typical Data in the table will be
Procedure_Name Business Group Id Legislation_code
-------------- ----------------- ----------------
Global
Legislation US
Client 100
We want to sort this in the order of Global, Legislation, and then Client.
The NVLs will generate the output as
Procedure_Name Business Group Id Legislation_code
-------------- ----------------- ----------------
Global -100 ' '
Legislation -100 US
Client 100 ' '
If we order by the abouve output Business Group Id, Legislation_code
We will get the output as
Procedure_Name Business Group Id Legislation_code
-------------- ----------------- ----------------
Global -100 ' '
Legislation -100 US
Client 100 ' '
*/
--
new_idx number;
p_update_type IN VARCHAR2,
p_change_mode IN VARCHAR2,
p_proration_type IN VARCHAR2,
p_datetracked_event IN VARCHAR2,
p_column_name IN VARCHAR2 default 'none',
p_old_val IN VARCHAR2 default null,
p_new_val IN VARCHAR2 default null,
p_change_values IN VARCHAR2 default null,
p_element_entry_id IN NUMBER default null,
p_surrogate_key IN VARCHAR2,
p_dated_table_id IN NUMBER,
p_date_counter IN OUT NOCOPY number,
p_global_env IN OUT NOCOPY t_global_env_rec,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output IN OUT NOCOPY t_detailed_output_table_type
) AS
l_proc VARCHAR2(80) := 'add_found_event';
t_proration_change_type(p_date_counter):= p_update_type;
t_detailed_output(p_date_counter).update_type := p_update_type;
hr_utility.trace(' Event Type :' || p_update_type );
select ''
into l_dummy
from dual
where exists (select ''
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B', 'V')
and (ppa.effective_date >= p_effective_date
or ppa.date_earned >= p_effective_date)
);
p_update_type in varchar2,
p_change_mode in varchar2,
p_change_values in varchar2,
p_surrogate_key IN NUMBER,
p_date_counter IN OUT NOCOPY NUMBER,
p_global_env IN OUT NOCOPY t_global_env_rec,
p_datetracked_id IN NUMBER,
p_column_name IN VARCHAR2,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2,
p_proration_style IN VARCHAR2,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output in OUT NOCOPY t_detailed_output_table_type,
p_run_event_proc in out nocopy boolean,
p_event_proc_res in out nocopy varchar2
)
is
l_output_result VARCHAR2(40) ;
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_proration_type => p_proration_style,
p_datetracked_event => p_datetracked_id,
-- possible future enhancement request
p_column_name => p_column_name,
p_old_val => p_old_value,
p_new_val => p_new_value,
p_element_entry_id => p_element_entry_id,
p_surrogate_key => p_surrogate_key,
p_change_values => p_change_values,
p_dated_table_id => p_table_id,
p_global_env => p_global_env,
p_date_counter => p_date_counter,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
p_update_type => 'U',
p_change_mode => p_change_mode,
p_change_values => null,
p_surrogate_key => p_surrogate_key,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
p_datetracked_id => t_dynamic_sql(l_counter).date_tracked_id,
p_column_name => t_dynamic_sql(l_counter).column_name,
p_old_value => t_dynamic_sql(l_counter).old_value,
p_new_value => t_dynamic_sql(l_counter).new_value,
p_proration_style => t_dynamic_sql(l_counter).proration_style,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output,
p_run_event_proc => run_event_proc,
p_event_proc_res => event_proc_res
);
select ''
into l_dummy
from dual
where exists (select ''
from pay_element_entry_values_f
where element_entry_id = l_ee_id
and element_entry_value_id =
p_surrogate_key
);
p_update_type => 'U',
p_change_mode => p_change_mode,
p_change_values => null,
p_surrogate_key => p_surrogate_key,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
p_datetracked_id => t_dynamic_sql(l_counter).date_tracked_id,
p_column_name => t_dynamic_sql(l_counter).column_name,
p_old_value => t_dynamic_sql(l_counter).old_value,
p_new_value => t_dynamic_sql(l_counter).new_value,
p_proration_style => t_dynamic_sql(l_counter).proration_style,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output,
p_run_event_proc => run_event_proc,
p_event_proc_res => event_proc_res
);
p_updated_column_name IN VARCHAR2,
p_table_id in NUMBER,
p_surrogate_key in NUMBER,
p_change_values in varchar2,
p_effective_date IN DATE,
p_date_counter IN OUT NOCOPY number,
store_correction IN OUT NOCOPY NUMBER,
is_correction IN OUT NOCOPY NUMBER,
l_proration_type IN VARCHAR2,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output in OUT NOCOPY t_detailed_output_table_type
) AS
BEGIN
is_correction:=0;
if (p_start_date_name <> p_updated_column_name AND
p_end_date_name <> p_updated_column_name)
THEN
is_correction:=1;
t_detailed_output(p_date_counter).column_name := p_updated_column_name;
PROCEDURE event_group_table_deleted
(
p_table_name IN VARCHAR2,
p_table_id IN NUMBER,
p_surrogate_key_name IN VARCHAR2,
p_surrogate_key IN NUMBER,
p_end_date_name IN VARCHAR2,
p_effective_date IN DATE,
p_updated_column_name IN VARCHAR2,
p_date_counter IN OUT NOCOPY number,
store_delete IN OUT NOCOPY NUMBER,
is_delete IN OUT NOCOPY NUMBER,
l_proration_type IN VARCHAR2,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output in OUT NOCOPY t_detailed_output_table_type
) AS
l_statement VARCHAR2(1000) ;
is_delete:=0;
IF (p_updated_column_name=p_end_date_name)
THEN
l_statement := 'SELECT 1 FROM dual WHERE EXISTS (select 1 from '
|| p_table_name || ' where ' ||
p_surrogate_key_name ||' = :p_surrogate_key '||
' and ' || p_end_date_name ||' > :col1)';
is_delete:=1;
IF (store_delete = 0)
THEN
t_proration_dates_temp(p_date_counter):= p_effective_date;
PROCEDURE event_group_table_updated
(
p_element_entry_id IN NUMBER DEFAULT NULL ,
p_assignment_action_id IN NUMBER,
p_business_group_id IN NUMBER,
p_assignment_id IN NUMBER,
p_process_mode IN VARCHAR2,
p_change_mode IN VARCHAR2,
p_proration_group_id IN NUMBER,
p_table_id IN NUMBER,
p_table_name IN VARCHAR2,
p_surrogate_key_name IN VARCHAR2,
p_surrogate_key IN NUMBER,
p_start_date_name IN VARCHAR2,
p_end_date_name IN VARCHAR2,
p_effective_date IN DATE,
p_creation_date IN DATE DEFAULT NULL,
p_start_date IN DATE,
p_end_date IN DATE,
p_updated_column_name IN VARCHAR2,
p_date_counter IN OUT NOCOPY number,
p_global_env IN OUT NOCOPY t_global_env_rec,
l_proration_type IN VARCHAR2,
t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output in OUT NOCOPY t_detailed_output_table_type
) AS
l_old_sql_fetch NUMBER ;
p_updated_column_name,
l_final_effective_date);
select procedure_name
into g_tim_def_prc_name
from pay_event_procedures
where time_definition_id = p_time_definition_id
and nvl(procedure_type, 'E') = 'T';
select ptp_td.start_date
from per_time_periods ptp_td,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp_ppa
where ptp_td.time_definition_id = p_time_def
and paa.assignment_action_id = p_asg_act
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = ptp_ppa.payroll_id
and ppa.date_earned between ptp_ppa.start_date
and ptp_ppa.end_date
and ptp_td.start_date > ptp_ppa.start_date
and ptp_td.start_date <= ptp_ppa.end_date;
select min(effective_start_date),
max(effective_end_date)
into l_ee_min_date,
l_ee_max_date
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
p_update_type => null,
p_change_mode => null,
p_proration_type => 'E',
p_datetracked_event => null,
p_surrogate_key => null,
p_dated_table_id => null,
p_date_counter => l_date_counter,
p_global_env => l_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type_temp,
t_proration_type => t_proration_type_temp,
t_detailed_output => t_detailed_output
);
t_proration_dates.delete;
t_proration_dates_temp.delete;
t_proration_change_type.delete;
t_proration_change_type_temp.delete;
t_proration_type_temp.delete;
t_proration_type.delete;
SELECT process_event_id ,
event_update_id ,
change_type ,
assignment_id ,
surrogate_key ,
effective_date
FROM pay_process_events
WHERE assignment_id = p_assignment_id
AND change_type IN ('DATE_PROCESSED',
'DATE_EARNED' ,
'PAYMENT' ,
'GRE' ,
'COST_CENTRE' )
AND status = 'U';
SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
CURSOR c_mixed(p_event_update_id IN NUMBER) IS
SELECT a.dated_table_id table_id ,
a.column_name column_name ,
a.change_type change_type ,
a.event_type event_type ,
b.table_name table_name ,
b.surrogate_key_name surrogate_key_name ,
b.start_date_name start_date_name ,
b.end_date_name end_date_name
FROM pay_event_updates a ,
pay_dated_tables b
WHERE a.dated_table_id = b.dated_table_id;
l_event_update_id pay_event_updates.event_update_id%TYPE ;
l_table_id pay_event_updates.dated_table_id%TYPE ;
l_column_name pay_event_updates.column_name%TYPE ;
l_change_type2 pay_event_updates.change_type%TYPE ;
l_event_type pay_event_updates.event_type%TYPE ;
l_event_update_id := cppe.event_update_id ;
FOR cm IN c_mixed(l_event_update_id)
LOOP
l_table_id := cm.table_id ;
CURSOR c_event_updates(p_event_update_id IN NUMBER ,
p_change_type IN VARCHAR2 ) IS
SELECT a.dated_table_id table_id ,
a.column_name column_name ,
a.event_type event_type
FROM pay_event_updates a
WHERE a.event_update_id = p_event_update_id
AND a.change_type = p_change_type ;
SELECT a.column_name column_name ,
a.event_type event_type
FROM pay_event_updates a,
pay_dated_tables b
WHERE a.dated_table_id = b.dated_table_id
AND a.dated_table_id = p_table_id
AND a.change_type = p_change_type
AND a.column_name NOT IN (p_start_col, p_end_col)
AND a.event_type = 'U'
AND a.column_name IS NOT NULL ;
SELECT change_type ,
event_update_id ,
effective_date
FROM pay_process_events
WHERE process_event_id = p_process_event_id ;
SELECT table_name ,
surrogate_key_name ,
start_date_name ,
end_date_name
FROM pay_dated_tables
WHERE dated_table_id = p_table_id;
l_event_update_id NUMBER ;
l_event_update_id := NULL ;
l_event_update_id := cpes.event_update_id;
FOR ceu IN c_event_updates(l_event_update_id,
l_change_type )
LOOP
l_table_id := ceu.table_id ;
t_dynamic_sql.DELETE;
l_statem := 'select '|| p_qual_definition;
select peqv.from_value,
peqv.to_value,
peqv.valid_event,
peqv.proration_style,
peqv.qualifier_value,
peq.qualifier_definition,
peq.comparison_column,
peq.qualifier_where_clause,
peq.multi_event_sql
from pay_event_value_changes_f peqv,
pay_event_qualifiers_f peq
where peqv.datetracked_event_id = p_datetracked_id
and peqv.valid_event = p_valid_events
and peq.event_qualifier_id = peqv.event_qualifier_id;
select peqv.valid_event,
peqv.proration_style,
peq.assignment_qualification,
peq.entry_qualification
into default_val_event,
default_pro_type,
default_asg_qual,
default_ee_qual
from pay_event_value_changes_f peqv,
pay_event_qualifiers_f peq
where peqv.datetracked_event_id = p_datetracked_event_id
and peqv.default_event = 'Y'
and peq.event_qualifier_id = peqv.event_qualifier_id;
p_update_type IN VARCHAR2,
p_change_mode IN VARCHAR2,
p_process_mode IN VARCHAR2,
p_proration_type IN VARCHAR2,
p_datetracked_event IN VARCHAR2,
p_column_name IN VARCHAR2 default 'none',
p_old_val IN VARCHAR2 default null,
p_new_val IN VARCHAR2 default null,
p_change_values IN VARCHAR2 default null,
p_element_entry_id IN NUMBER default null,
p_surrogate_key IN VARCHAR2,
p_dated_table_id IN NUMBER,
p_table_name IN VARCHAR2,
p_disco IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_assignment_action_id IN NUMBER,
p_business_group_id IN NUMBER,
p_assignment_id IN NUMBER,
p_penserv_mode IN VARCHAR2 default 'N',
p_date_counter IN OUT NOCOPY number,
p_global_env IN OUT NOCOPY t_global_env_rec,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output IN OUT NOCOPY t_detailed_output_table_type
)
is
save_event boolean;
l_update_type varchar2(10);
cursor get_update_type IS
select update_type
from pay_Datetracked_events
where datetracked_event_id = p_datetracked_event;
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_proration_type => p_proration_type,
p_datetracked_event => p_datetracked_event,
p_column_name => p_column_name,
p_change_values => p_change_values,
p_element_entry_id => p_element_entry_id,
p_surrogate_key => p_surrogate_key,
p_dated_table_id => p_dated_table_id,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_proration_type => p_proration_type,
p_datetracked_event => p_datetracked_event,
p_element_entry_id => p_element_entry_id,
p_surrogate_key => p_surrogate_key,
p_dated_table_id => p_dated_table_id,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_change_values => p_change_values,
p_surrogate_key => p_surrogate_key,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
p_datetracked_id => p_datetracked_event,
p_column_name => p_column_name,
p_old_value => p_old_val,
p_new_value => p_new_val,
p_proration_style => p_proration_type,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output,
p_run_event_proc => run_event_proc,
p_event_proc_res => event_proc_res
);
open get_update_type; -- 7190857
fetch get_update_type into l_update_type;
close get_update_type;
select ''
into l_dummy
from dual
where exists (select ''
from pay_element_entry_values_f
where element_entry_id = l_ee_id
and element_entry_value_id = p_surrogate_key
);
-- after all the iterations, switch save_event to TRUE for PURGE update_type.
curr_ptr := glo_datetrack_ee_tab(curr_ptr).next_ptr;
l_update_type = 'P' and
p_penserv_mode = 'N' ) THEN /*Bug 7409433 Added condition p_penserv_mode ='N' */
save_event := TRUE;
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_proration_type => p_proration_type,
p_datetracked_event => p_datetracked_event,
p_column_name => p_column_name,
p_change_values => p_change_values,
p_element_entry_id =>
l_element_entry_id,
p_surrogate_key => p_surrogate_key,
p_dated_table_id => p_dated_table_id,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_proration_type => p_proration_type,
p_datetracked_event => p_datetracked_event,
p_element_entry_id =>
l_element_entry_id,
p_surrogate_key => p_surrogate_key,
p_dated_table_id => p_dated_table_id,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
p_update_type => p_update_type,
p_change_mode => p_change_mode,
p_change_values => p_change_values,
p_surrogate_key => p_surrogate_key,
p_date_counter => p_date_counter,
p_global_env => p_global_env,
p_datetracked_id => p_datetracked_event,
p_column_name => p_column_name,
p_old_value => p_old_val,
p_new_value => p_new_val,
p_proration_style => p_proration_type,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output,
p_run_event_proc => run_event_proc,
p_event_proc_res => event_proc_res
);
select min(effective_date)
into l_effective_date
from pay_process_events
where surrogate_key = p_pro_evt_rec.surrogate_key
and event_update_id = p_pro_evt_rec.event_update_id
and creation_date = p_pro_evt_rec.creation_date;
select count(*)
into l_count
from pay_dated_tables pdt,
pay_event_updates peu,
pay_process_events ppe
where pdt.table_name = p_pro_evt_rec.table_name
and pdt.dated_table_id = peu.dated_table_id
and peu.event_type = 'I'
and peu.change_type = p_pro_evt_rec.change_mode
and peu.event_update_id = ppe.event_update_id
and ppe.surrogate_key = p_pro_evt_rec.surrogate_key
and ppe.creation_date = p_pro_evt_rec.creation_date;
p_update_type => 'DF',
p_change_mode => p_pro_evt_rec.change_mode,
p_process_mode => p_process_mode,
p_proration_type => p_dtevent_rec.proration_type,
p_datetracked_event => p_dtevent_rec.datetracked_event_id,
p_element_entry_id => p_element_entry_id,
p_surrogate_key => p_pro_evt_rec.surrogate_key,
p_dated_table_id => p_dtevent_rec.table_id,
p_table_name => p_dtevent_rec.table_name,
p_disco => p_disco,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_assignment_action_id => p_assignment_action_id,
p_business_group_id => p_business_group_id,
p_assignment_id => p_assignment_id,
p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
if (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
and p_pro_evt_rec.effective_date > p_pro_evt_rec.calculation_date)
then
--
save_disco_details (
p_effective_date => p_pro_evt_rec.calculation_date,
p_creation_date => p_pro_evt_rec.creation_date,
p_update_type => 'DF',
p_change_mode => p_pro_evt_rec.change_mode,
p_process_mode => p_process_mode,
p_proration_type => p_dtevent_rec.proration_type,
p_datetracked_event => p_dtevent_rec.datetracked_event_id,
p_element_entry_id => p_element_entry_id,
p_surrogate_key => p_pro_evt_rec.surrogate_key,
p_dated_table_id => p_dtevent_rec.table_id,
p_table_name => p_dtevent_rec.table_name,
p_disco => p_disco,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_assignment_action_id => p_assignment_action_id,
p_business_group_id => p_business_group_id,
p_assignment_id => p_assignment_id,
p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
Look through PPE for an update or end-date
--
-- all dbt_df now in extra_tests_dbt_df
Driving Query gets candidate rows...
...that may be indicative of one of the six situations.
API-U,API-E,API-DF and , DT-U,DT-E
The first two are indicated by an update to the end-date column with
eff_date = calc_date.
The third is indicated by an update to the end-date column with
eff_date > calc_date
The fourth is definitively indicated by any alteration to the
start-date column.
The last two situations are recorded elsewhere
Further Tests involve...
...differentiating between API-U and API-E by checking the base table
for future dated records. If no future rows exist then must be an E.
(NB. At this point we note that if an E occurs; and then it is undone
and a future row inserted; this test will fail and an API-U will be
Further advanced checking occurs against each update candidate to make
sure we are interested in this type and values of the updates.
---------------------------------------------------------- */
PROCEDURE extra_tests_dbt_u_e
(
p_element_entry_id IN pay_element_entries.element_entry_id%type,
p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type,
p_business_group_id IN per_business_groups.business_group_id%type,
p_assignment_id IN per_all_assignments_f.assignment_id%type,
p_process_mode IN VARCHAR2,
p_event_group_id IN pay_event_groups.event_group_id%type,
p_start_date IN date,
p_end_date IN date,
p_penserv_mode IN VARCHAR2 DEFAULT 'N',
p_date_counter IN OUT NOCOPY number,
p_global_env IN OUT NOCOPY t_global_env_rec,
t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
p_dtevent_rec IN t_distinct_table_rec ,
p_disco IN OUT NOCOPY NUMBER
) IS
l_search varchar2(30) := p_dtevent_rec.update_type;
if (p_pro_evt_rec.updated_column_name = p_dtevent_rec.start_date_name
or p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
) then
--
-- Look for an Update (via Forms => DT-U
--
IF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.start_date_name
and l_search = 'U') THEN
--defo got DT-U
--Allow existing involved code to further test and add to our list
if (g_dbg) then
hr_utility.set_location(l_proc, 25);
event_group_table_updated(p_element_entry_id,
p_assignment_action_id,
p_business_group_id,
p_assignment_id,
p_process_mode,
p_pro_evt_rec.change_mode,
p_event_group_id,
p_dtevent_rec.table_id,
p_dtevent_rec.table_name,
p_dtevent_rec.surrogate_key_name,
p_pro_evt_rec.surrogate_key,
p_dtevent_rec.start_date_name,
p_dtevent_rec.end_date_name,
p_pro_evt_rec.effective_date,
p_pro_evt_rec.creation_date,
p_start_date,
p_end_date,
p_pro_evt_rec.updated_column_name,
p_date_counter,
p_global_env,
p_dtevent_rec.proration_type,
t_dynamic_sql,
t_proration_dates_temp,
t_proration_change_type,
t_proration_type,
t_detailed_output );
'select max('||p_dtevent_rec.end_date_name||')'||
' from '|| p_dtevent_rec.table_name||
' where '|| p_dtevent_rec.surrogate_key_name ||' = :1 '||
' and '|| p_dtevent_rec.end_date_name || '>= :2'||
' group by '|| p_dtevent_rec.surrogate_key_name;
For Datetracked tables, after datetrack update, l_date_dummy is greater
than the effective date. But for PER_ADDRESSES, l_date_dummy will be equal
to effective date even for an UPDATE as it is not datetracked and the new
record will have a different primary key.
*/
if ( p_dtevent_rec.table_name = 'PER_ADDRESSES' and
l_date_dummy = p_pro_evt_rec.effective_date AND
p_penserv_mode <> 'A' ) then -- bug 7211447
--
l_dummy := 2;
'SELECT count(*) FROM '||p_dtevent_rec.table_name||
' WHERE '||p_dtevent_rec.surrogate_key_name ||' = :1 '||
' AND '|| p_dtevent_rec.end_date_name || ' >= :2 ';
IF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
and l_dummy > 1
and l_search = 'U') THEN
--
if (g_dbg) then
hr_utility.set_location(l_proc, 55);
event_group_table_updated(p_element_entry_id,
p_assignment_action_id,
p_business_group_id,
p_assignment_id,
p_process_mode,
p_pro_evt_rec.change_mode,
p_event_group_id,
p_dtevent_rec.table_id,
p_dtevent_rec.table_name,
p_dtevent_rec.surrogate_key_name,
p_pro_evt_rec.surrogate_key,
p_dtevent_rec.start_date_name,
p_dtevent_rec.end_date_name,
p_pro_evt_rec.effective_date,
p_pro_evt_rec.creation_date,
p_start_date,
p_end_date,
p_pro_evt_rec.updated_column_name,
p_date_counter,
p_global_env,
p_dtevent_rec.proration_type,
t_dynamic_sql,
t_proration_dates_temp,
t_proration_change_type,
t_proration_type,
t_detailed_output );
ELSIF (p_pro_evt_rec.updated_column_name = p_dtevent_rec.end_date_name
and l_dummy = 1
and l_search = 'E') THEN
--
if (g_dbg) then
hr_utility.set_location(l_proc, 65);
1) Check it is flagged as a Datetrack delete = End dated
2) Check this end date still applies
---------------------------------------------------------- */
PROCEDURE extra_tests_dyt_pkg_e
(
p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
p_dtevent_rec IN t_distinct_table_rec ,
p_disco IN OUT NOCOPY number
) IS
l_statement varchar2(800);
if (p_pro_evt_rec.event_type = hr_api.g_delete) then
-- >>> PHASE 2: Check e is still relevant
--
hr_utility.set_location(l_proc, 20);
'SELECT count(*) FROM '||p_dtevent_rec.table_name||
' WHERE '||p_dtevent_rec.surrogate_key_name ||' = :1 '||
' AND '|| p_dtevent_rec.end_date_name ||' >= :2';
end if; --If not delete type then dont do anything
1) Check the event update is the right type, a deletion
2) Check this is the latest created ppe event for this
event_update/surrogate_key combination
3) Check this deletion is part of a purge, no rows in base table
also check we havent got this event yet under the guise of another
ppe row, 'cos creation date may be identical for several
---------------------------------------------------------- */
PROCEDURE get_dbt_i_p_cache
(
p_surrogate_key IN pay_process_events.surrogate_key%type ,
p_event_update_id IN pay_process_events.event_update_id%type ,
p_assignment_id IN per_all_assignments.assignment_id%type,
p_change_mode IN pay_event_updates.change_type%type,
p_cache_number IN OUT NOCOPY NUMBER
) IS
CURSOR csr_get_cache_asgid (
cp_base_record_id in number,
cp_event_update_id in number,
cp_mode in varchar2,
cp_assignment_id in number) is
SELECT min(creation_date),
max(creation_date)
FROM PAY_PROCESS_EVENTS
WHERE event_update_id = cp_event_update_id
AND surrogate_key = cp_base_record_id
AND assignment_id is not null
AND assignment_id = cp_assignment_id
AND change_type = nvl(cp_mode,change_type);
cp_event_update_id in number,
cp_mode in varchar2) is
SELECT min(creation_date),
max(creation_date)
FROM PAY_PROCESS_EVENTS
WHERE event_update_id = cp_event_update_id
AND surrogate_key = cp_base_record_id
AND assignment_id is null
AND change_type = nvl(cp_mode,change_type);
l_key := p_event_update_id||'_'
||p_surrogate_key||'_'
||p_change_mode ;
p_event_update_id,
p_change_mode,
p_assignment_id);
p_event_update_id,
p_change_mode);
Look at candidate process event to see if it is an insert
--
Detail Logic:
1) Check this candidate is indicative of an Insert event update
2) Check this candidate is the absolute min creation date for this
event update, surrogate key combination
as clearly later inserts will not be a result of a true insert.
3) No earlier dated row exist in base table, and one today
eg sanity check point 2 and also DO NOT RETURN A TRUE INSERT
IF THE DATA HAS BEEN PURGED (this is designed behaviour) against
the concept of a total audit trail.
---------------------------------------------------------- */
PROCEDURE extra_tests_i
(
p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
p_dtevent_rec IN t_distinct_table_rec ,
p_disco IN OUT NOCOPY number
) IS
l_statement varchar2(800);
p_event_update_id => p_pro_evt_rec.event_update_id,
p_assignment_id => p_pro_evt_rec.assignment_id,
p_change_mode => p_pro_evt_rec.change_mode,
p_cache_number => l_pos
) ;
'SELECT count(*) FROM '||p_dtevent_rec.table_name||
' WHERE ' || p_dtevent_rec.surrogate_key_name || ' = :1 '||
' AND '|| p_dtevent_rec.start_date_name || ' <= :2 ';
Look at candidate process event to see if it is an update
--
Detail Logic:
1) Check the event update is the right type
AND peu.event_type = hr_api.g_update
2) Check the update is one we're interested
---------------------------------------------------------- */
PROCEDURE extra_tests_dyt_pkg_u
(
p_element_entry_id IN pay_element_entries.element_entry_id%type,
p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type,
p_business_group_id IN per_business_groups.business_group_id%type,
p_assignment_id IN per_all_assignments_f.assignment_id%type,
p_process_mode IN VARCHAR2,
p_event_group_id IN pay_event_groups.event_group_id%type,
p_start_date IN date,
p_end_date IN date,
p_date_counter IN OUT NOCOPY number,
p_global_env IN OUT NOCOPY t_global_env_rec,
t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
p_dtevent_rec IN t_distinct_table_rec ,
p_disco IN OUT NOCOPY number
) IS
l_proc VARCHAR2(80) := g_pkg||'.extra_tests_dyt_pkg_u';
if ( p_pro_evt_rec.event_type = hr_api.g_update
or p_pro_evt_rec.event_type = hr_api.g_update_override
or p_pro_evt_rec.event_type = hr_api.g_update_change_insert
) then
event_group_table_updated(p_element_entry_id,
p_assignment_action_id,
p_business_group_id,
p_assignment_id,
p_process_mode,
p_pro_evt_rec.change_mode,
p_event_group_id,
p_dtevent_rec.table_id,
p_dtevent_rec.table_name,
p_dtevent_rec.surrogate_key_name,
p_pro_evt_rec.surrogate_key,
p_dtevent_rec.start_date_name,
p_dtevent_rec.end_date_name,
p_pro_evt_rec.effective_date,
p_pro_evt_rec.creation_date,
p_start_date,
p_end_date,
p_pro_evt_rec.updated_column_name,
p_date_counter,
p_global_env,
p_dtevent_rec.proration_type,
t_dynamic_sql,
t_proration_dates_temp,
t_proration_change_type,
t_proration_type,
t_detailed_output );
1) Check the event update is the right type, a deletion
2) Check this is the latest created ppe event for this
event_update/surrogate_key combination
3) Check this deletion is part of a purge, no rows in base table
also check we havent got this event yet under the guise of another
ppe row, 'cos creation date may be identical for several
---------------------------------------------------------- */
PROCEDURE extra_tests_dbt_p
(
p_pro_evt_rec IN t_mst_process_event_rec ,
p_dtevent_rec IN t_distinct_table_rec ,
p_disco IN OUT NOCOPY number
) IS
l_statement varchar2(800);
p_event_update_id => p_pro_evt_rec.event_update_id,
p_assignment_id => p_pro_evt_rec.assignment_id,
p_change_mode => p_pro_evt_rec.change_mode,
p_cache_number => l_pos
) ;
'SELECT count(*) FROM '||p_dtevent_rec.table_name||
' WHERE '||p_dtevent_rec.surrogate_key_name ||' = :1 ';
As dynamic trigger package, its immediately obvious when a delete future
(FUTURE_CHANGE or DELETE_NEXT_CHANGE has occurred)
But functional requirement to check this is still valid,
eg dont return if a new row has now been introduced after
--
Detail Logic:
Easy to identify 'FUTURE_CHANGE','DELETE_NEXT_CHANGE'
...just want to check all future deletes are still applicable, eg no-one
has reintroduced some information for the surrogate key.
---------------------------------------------------------- */
PROCEDURE extra_tests_dyt_pkg_df
(
p_pro_evt_rec IN t_mst_process_event_rec , --record from master query
p_dtevent_rec IN t_distinct_table_rec ,
p_disco IN OUT NOCOPY number
) IS
l_statement varchar2(800);
p_pro_evt_rec.event_type = hr_api.g_delete_next_change ) THEN
--Check the base table to see if any future dated records exist,
-- Eg we know we have a FUTURE_CHANGE or a DELETE_NEXT_CHANGE
-- but check it still applies, not been overriden
l_statement :=
'SELECT count(*) FROM '||p_dtevent_rec.table_name||
' WHERE ' || p_dtevent_rec.surrogate_key_name || ' = :1 ' ||
' AND '|| p_dtevent_rec.end_date_name || ' >= :2 ';
' , indicative of update_id: '||
p_mst_pe_rec.event_update_id||
' key: '||
p_mst_pe_rec.surrogate_key );
l_search := l_look_for_rec.update_type;
if ( l_previous_look_for_rec.update_type = 'U'
and l_look_for_rec.table_name = l_previous_look_for_rec.table_name) then
l_all_upd_events_recorded := TRUE;
p_mst_pe_rec.event_type = hr_api.g_insert) then
extra_tests_i(p_mst_pe_rec,l_look_for_rec,disco);
if (p_mst_pe_rec.updated_column_name = l_look_for_rec.column_name)
then
disco := G_DISCO_STANDARD;
if (p_mst_pe_rec.updated_column_name = l_look_for_rec.column_name
and p_mst_pe_rec.event_type = hr_api.g_correction) then
disco := G_DISCO_STANDARD;
/* Bug 13784208 : Handling the event DELETE_NEXT_CHANGE */
ELSIF (l_search='D' and p_mst_pe_rec.event_type='DELETE_NEXT_CHANGE') THEN
disco := G_DISCO_STANDARD;
p_update_type => l_search,
p_change_mode => p_mst_pe_rec.change_mode,
p_process_mode => p_process_mode,
p_proration_type => l_look_for_rec.proration_type,
p_datetracked_event => l_look_for_rec.datetracked_event_id,
p_column_name => p_mst_pe_rec.updated_column_name,
p_change_values => p_mst_pe_rec.change_values,
p_element_entry_id => p_element_entry_id,
p_surrogate_key => p_mst_pe_rec.surrogate_key,
p_dated_table_id => l_look_for_rec.table_id,
p_table_name => l_look_for_rec.table_name,
p_disco => disco,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_assignment_action_id => p_assignment_action_id,
p_business_group_id => p_business_group_id,
p_assignment_id => p_assignment_id,
p_penserv_mode => p_penserv_mode, /*Bug 7409433 */
p_date_counter => p_date_counter,
p_global_env => p_global_env,
t_proration_dates_temp => t_proration_dates_temp,
t_proration_change_type => t_proration_change_type,
t_proration_type => t_proration_type,
t_detailed_output => t_detailed_output
);
select /*+ USE_NL(pgr paf)*/
pgr.grade_rule_id
from pay_grade_rules_f pgr,
per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.grade_id = pgr.grade_or_spinal_point_id
union all
select /*+ ORDERED USE_NL(pgr psp psps pspp)*/
pgr.grade_rule_id
from per_spinal_point_placements_f pspp,
per_spinal_point_steps_f psps,
per_spinal_points psp,
pay_grade_rules_f pgr
where psp.spinal_point_id = pgr.grade_or_spinal_point_id
and psp.spinal_point_id = psps.spinal_point_id
and p_assignment_id = pspp.assignment_id
and pspp.step_id = psps.step_id;
select '' chk
from dual
where exists (
select '' chk
from pay_element_entries_f pee
,pqh_criteria_rate_elements pcre
,pqh_rate_matrix_rates_f prmr
where pee.assignment_id=p_assignment_id
and pcre.element_type_id=pee.element_type_id
and pcre.criteria_rate_defn_id=prmr.criteria_rate_defn_id
and prmr.rate_matrix_rate_id=p_surrogate_key)
or exists (
select '' chk
from pay_element_entries_f pee
,pqh_criteria_rate_elements pcre
,pqh_criteria_rate_factors pcrf
,pqh_rate_matrix_rates_f prmr
where pee.assignment_id=p_assignment_id
and pcre.element_type_id=pee.element_type_id
and pcre.criteria_rate_defn_id = pcrf.criteria_rate_defn_id
and pcrf.parent_criteria_rate_defn_id = prmr.criteria_rate_defn_id
and prmr.rate_matrix_rate_id = p_surrogate_key);
SELECT '' chk
FROM dual
WHERE EXISTS
(
SELECT '' chk
FROM pay_input_values_f piv
,pay_element_links_f pelf
,pay_element_entries_f peef
WHERE piv.input_value_id = p_surrogate_key
AND pelf.element_type_id = piv.element_type_id
AND peef.element_link_id = pelf.element_link_id
AND peef.assignment_id = p_assignment_id
);
SELECT '' chk
FROM dual
WHERE EXISTS
(
SELECT '' chk
FROM per_cagr_entitlement_lines_f cagr_lines
,per_cagr_entitlements cagr_ent
,per_all_assignments_f paf
WHERE cagr_lines.cagr_entitlement_line_id = p_surrogate_key
AND cagr_ent.cagr_entitlement_id = cagr_lines.cagr_entitlement_id
AND paf.collective_agreement_id = cagr_ent.collective_agreement_id
AND paf.assignment_id = p_assignment_id
);
g_grade_list.delete();
is select min(ppe.effective_date)
from pay_process_events ppe
where trunc(ppe.creation_date) between p_lapp_date and p_end_date
and ppe.assignment_id = p_assignment_id
and ppe.business_group_id = p_business_group_id
and ppe.effective_date >= ben_ext_thread.g_effective_start_date
and exists (select pde.event_group_id
from pay_datetracked_events pde,
pay_event_updates peu
where pde.event_group_id in (select becv.val_1
from ben_ext_crit_val becv,
ben_ext_crit_typ bect,
ben_ext_dfn bed
where becv.ext_crit_typ_id = bect.ext_crit_typ_id
and bect.ext_crit_prfl_id = bed.ext_crit_prfl_id
and bed.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
and bect.crit_typ_cd = 'CPE')
and ppe.event_update_id = peu.event_update_id
and peu.dated_table_id = pde.dated_table_id);
type t_column_name is table of pay_event_updates.column_name%type
index by binary_integer;
type t_event_type is table of pay_event_updates.event_type%type
index by binary_integer;
type t_event_update_id is table of pay_event_updates.event_update_id%type
index by binary_integer;
l_event_update_id t_event_update_id;
SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PPE) */
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_process_events ppe ,
pay_event_updates peu
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is not null
AND ppe.assignment_id = p_assignment_id
AND ppe.business_group_id = p_business_group_id
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.creation_date BETWEEN cp_cstart_date AND cp_cend_date
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
UNION ALL
SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N3) USE_NL(PDT) */
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_event_updates peu ,
pay_process_events ppe
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is null
AND (ppe.business_group_id = p_business_group_id
or ppe.business_group_id is null)
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.creation_date BETWEEN cp_cstart_date AND cp_cend_date
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
AND pay_interpreter_pkg.valid_group_event_for_asg
(pdt.table_name,
p_assignment_id,
ppe.surrogate_key) = 'Y'
ORDER BY 11, 6, 5, 4;
SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PPE) */
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_process_events ppe ,
pay_event_updates peu
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is not null
AND ppe.assignment_id = p_assignment_id
AND ppe.business_group_id = p_business_group_id
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
or ppe.retroactive_status is null)
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
UNION ALL
SELECT /*+ no_expand ORDERED INDEX(PPE PAY_PROCESS_EVENTS_N5) USE_NL(PDT) */
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_event_updates peu ,
pay_process_events ppe
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is null
AND ppe.business_group_id = p_business_group_id
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
or ppe.retroactive_status is null)
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
AND pay_interpreter_pkg.valid_group_event_for_asg
(pdt.table_name,
p_assignment_id,
ppe.surrogate_key) = 'Y'
ORDER BY 11, 6, 5, 4, 2 ASC;
SELECT
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_process_events ppe ,
pay_event_updates peu
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is not null
AND ppe.assignment_id = p_assignment_id
AND ppe.business_group_id = p_business_group_id
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
or ppe.retroactive_status is null)
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
AND pay_interpreter_pkg.valid_group_event_for_asg
(pdt.table_name,
p_assignment_id,
ppe.surrogate_key) = 'Y'
--ORDER BY 11, 6, 5, 4;
SELECT
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_process_events ppe ,
pay_event_updates peu
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
--Added new condition to capture REPORTS events only
AND ppe.change_type = 'REPORTS'
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is not null
AND ppe.assignment_id = p_assignment_id
AND ppe.business_group_id = p_business_group_id
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
or ppe.retroactive_status is null)
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
AND pay_interpreter_pkg.valid_group_event_for_asg
(pdt.table_name,
p_assignment_id,
ppe.surrogate_key) = 'Y'
--Modified order by to include actual date
ORDER BY 11, 5, 4, 10 desc;
SELECT
peu.column_name updated_column_name ,
peu.event_type event_type ,
peu.event_update_id event_update_id ,
ppe.effective_date effective_date ,
ppe.assignment_id assignment_id ,
ppe.surrogate_key surrogate_key ,
ppe.process_event_id process_event_id,
ppe.description change_values,
ppe.calculation_date calculation_date,
ppe.creation_date creation_date,
ppe.change_type change_mode,
pdt.table_name table_name
FROM
pay_dated_tables pdt ,
pay_process_events ppe ,
pay_event_updates peu
WHERE
peu.event_update_id = ppe.event_update_id + 0
AND peu.dated_table_id = pdt.dated_table_id
--Added new condition to capture REPORTS events only
AND ppe.change_type = 'REPORTS'
AND pdt.dated_table_id IN
( select distinct pde2.dated_table_id table_id
from pay_datetracked_events pde2
where pde2.event_group_id = p_event_group_id
and cp_bulk_processing = 'N'
union all
select distinct pdt2.dated_table_id
from pay_dated_tables pdt2
where cp_bulk_processing = 'Y'
)
AND ppe.assignment_id is not null
AND ppe.assignment_id = p_assignment_id
AND ppe.business_group_id = p_business_group_id
AND ( peu.business_group_id = ppe.business_group_id
or peu.legislation_code = g_leg_code
or ( peu.business_group_id is null
and peu.legislation_code is null) )
AND (ppe.retroactive_status = nvl(p_process, ppe.retroactive_status)
or ppe.retroactive_status is null)
AND ppe.change_type = nvl(p_mode,ppe.change_type)
AND ppe.effective_date BETWEEN cp_estart_date AND cp_eend_date
/* Bug fix 12363861 Start
AND ( ppe.effective_date > cp_last_approved_date or ppe.creation_date > cp_last_approved_date) -- Bug 10092118
*/
AND ( ppe.effective_date > nvl(cp_last_approved_date, hr_api.g_sot) or ppe.creation_date > nvl(cp_last_approved_date, hr_api.g_sot)) -- Bug 10092118
-- Bug fix 12363861 End
AND ( (cp_bulk_processing = 'Y')
or ( cp_bulk_processing = 'N'
AND ( (pdt.table_name <> 'PAY_ELEMENT_ENTRIES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
and ppe.surrogate_key=p_element_entry_id )
)
AND ( ( pdt.table_name <> 'PAY_ELEMENT_ENTRY_VALUES_F' )
or
( pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and exists
( select null
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and element_entry_value_id =
ppe.surrogate_key ) )
)
)
)
AND pay_interpreter_pkg.valid_group_event_for_asg
(pdt.table_name,
p_assignment_id,
ppe.surrogate_key) = 'Y'
--Modified order by to include actual date
ORDER BY 11, 5, 4, 10 desc;
SELECT nvl(decode(name,'PQP GB PenServer Periodic Changes Interface - Allowance History','A',
'PQP GB PenServer Periodic Changes Interface - Bonus History','B',
'N'),'N')
FROM BEN_EXT_DFN
WHERE ext_dfn_id = c_ext_dfn_id
AND name in ('PQP GB PenServer Periodic Changes Interface - Allowance History',
'PQP GB PenServer Periodic Changes Interface - Bonus History')
AND legislation_code ='GB';
SELECT least(run_strt_dt,eff_dt) app_date
FROM ben_ext_rslt
WHERE ext_dfn_id = ben_ext_thread.g_ext_dfn_id
AND business_group_id = p_business_group_id
AND ext_stat_cd = 'A'
ORDER BY app_date DESC;
g_key_date_cache.delete;
g_upd_cache.delete; --bug 3598389
select legislation_code
into g_leg_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
l_column_name.delete;
l_event_type.delete;
l_event_update_id.delete;
l_effective_date.delete;
l_assignment_id.delete;
l_surrogate_key.delete;
l_process_event_id.delete;
l_description.delete;
l_calculation_date.delete;
l_creation_date.delete;
l_change_type.delete;
l_table_name.delete;
l_event_update_id,
l_effective_date,
l_assignment_id,
l_surrogate_key,
l_process_event_id,
l_description,
l_calculation_date,
l_creation_date,
l_change_type,
l_table_name;
l_mst_pe_rec.updated_column_name := l_column_name(i);
l_mst_pe_rec.event_update_id := l_event_update_id(i);
l_column_name.delete;
l_event_type.delete;
l_event_update_id.delete;
l_effective_date.delete;
l_assignment_id.delete;
l_surrogate_key.delete;
l_process_event_id.delete;
l_description.delete;
l_calculation_date.delete;
l_creation_date.delete;
l_change_type.delete;
l_table_name.delete;
l_event_update_id,
l_effective_date,
l_assignment_id,
l_surrogate_key,
l_process_event_id,
l_description,
l_calculation_date,
l_creation_date,
l_change_type,
l_table_name;
l_event_update_id,
l_effective_date,
l_assignment_id,
l_surrogate_key,
l_process_event_id,
l_description,
l_calculation_date,
l_creation_date,
l_change_type,
l_table_name;
l_event_update_id,
l_effective_date,
l_assignment_id,
l_surrogate_key,
l_process_event_id,
l_description,
l_calculation_date,
l_creation_date,
l_change_type,
l_table_name;
l_event_update_id,
l_effective_date,
l_assignment_id,
l_surrogate_key,
l_process_event_id,
l_description,
l_calculation_date,
l_creation_date,
l_change_type,
l_table_name;
l_mst_pe_rec.updated_column_name := l_column_name(i);
l_mst_pe_rec.event_update_id := l_event_update_id(i);
U - Update event
E - End date event
P - Purge event
C - Correction event
DF - Delete Future (Equals both Delete Next and Future Changes)
---------------------------------------------------------- */
PROCEDURE event_group_tables_affected
(
p_element_entry_id IN NUMBER DEFAULT NULL,
p_assignment_action_id IN NUMBER,
p_event_group_id IN NUMBER,
p_assignment_id IN NUMBER,
p_business_group_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_mode IN VARCHAR2,
p_process IN VARCHAR2,
p_process_mode IN VARCHAR2,
p_global_env IN OUT NOCOPY t_global_env_rec,
t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
t_proration_type IN OUT NOCOPY t_proration_type_table_type,
t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
p_penserv_mode IN VARCHAR2 DEFAULT 'N'
) AS
--Misc helper/counters
l_date_counter NUMBER ;
select distinct(et.recalc_event_group_id) recalc_event_group_id
from
pay_element_entries_f ee
,pay_element_links_f el
,pay_element_types_f et
where ee.element_entry_id = nvl(cp_ee_id,-1)
and ee.element_link_id = el.element_link_id
and el.element_type_id = et.element_type_id
and cp_report_date between et.effective_start_date
and et.effective_end_date;
t_dynamic_sql.delete;
t_proration_dates_temp.delete;
t_proration_change_type_temp.delete;
t_proration_type_temp.delete;
select max(business_group_id)
into l_business_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
select max(business_group_id)
into l_business_group_id
from pay_process_events
where assignment_id = p_assignment_id;
SELECT DISTINCT pdt.dated_table_id table_id ,
pdt.table_name table_name ,
pde.column_name column_name ,
pde.update_type update_type
FROM pay_datetracked_events pde,
pay_dated_tables pdt
WHERE pde.event_group_id = p_filter_event_group_id
AND pdt.dated_table_id = pde.dated_table_id
order by pdt.dated_table_id;
hr_utility.trace('Looking for '||reqd_event_rec.update_type||' on '||
reqd_event_rec.table_name||'.'||reqd_event_rec.column_name);
and reqd_event_rec.update_type = p_complete_detail_tab(j).update_type)
then
-- Second, Check that if we care, the column is also the same
if ( reqd_event_rec.update_type not in ('U','C') )
then
--dont care about col - Found a match, add this event to results
p_subset_detail_tab(k) := p_complete_detail_tab(j);
select assignment_action_id
into l_assignment_action_id
from pay_assignment_actions
where assignment_id = p_assignment_id
and rownum = 1;
t_dynamic_sql.delete;
t_detailed_output.delete;
t_proration_dates_temp.delete;
t_proration_change_type_temp.delete;
t_proration_type_temp.delete;