[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fc.context_name,
pbcv.value
from pay_balance_context_values pbcv,
ff_contexts fc
where pbcv.latest_balance_id = p_latest_bal_id
and pbcv.context_id = fc.context_id;
select distinct assignment_id
from per_all_assignments_f
where person_id = p_person_id;
l_def_bal_id.delete;
l_asg_act_id.delete;
l_e_asg_act_id.delete;
l_p_asg_act_id.delete;
l_asg_id.delete;
l_per_id.delete;
l_value.delete;
l_e_value.delete;
l_p_value.delete;
l_lat_bal_id.delete;
l_cont_value.delete;
l_cont_name.delete;
l_def_bal_id_ins.delete;
l_asg_act_id_ins.delete;
l_e_asg_act_id_ins.delete;
l_p_asg_act_id_ins.delete;
l_asg_id_ins.delete;
l_per_id_ins.delete;
l_value_ins.delete;
l_e_value_ins.delete;
l_p_value_ins.delete;
l_lat_bal_id_ins.delete;
l_tu_tab.delete;
l_jc_tab.delete;
l_oei_tab.delete;
l_si_tab.delete;
l_st_tab.delete;
l_st2_tab.delete;
l_sn_tab.delete;
l_tg_tab.delete;
l_pay_id_tab.delete;
select /*+ ORDERED USE_NL(pplb pbcv fc) */
pplb.defined_balance_id,
pplb.assignment_action_id,
pplb.person_id,
pplb.value,
pplb.latest_balance_id,
pplb.expired_assignment_action_id,
pplb.expired_value,
pplb.prev_balance_value,
pplb.prev_assignment_action_id,
pbcv.value,
fc.context_name
bulk collect into
l_def_bal_id,
l_asg_act_id,
l_per_id,
l_value,
l_lat_bal_id,
l_e_asg_act_id,
l_e_value,
l_p_value,
l_p_asg_act_id,
l_cont_value,
l_cont_name
from pay_person_latest_balances pplb,
pay_balance_context_values pbcv,
ff_contexts fc
where pplb.person_id = p_person_id
and pplb.latest_balance_id = pbcv.latest_balance_id (+)
and nvl(pbcv.context_id, -1) = fc.context_id (+)
order by pplb.latest_balance_id;
insert into pay_latest_balances
(latest_balance_id,
defined_balance_id,
assignment_action_id,
value,
person_id,
expired_assignment_action_id,
expired_value,
prev_assignment_action_id,
prev_balance_value,
tax_unit_id,
jurisdiction_code,
original_entry_id,
source_id,
source_text,
source_text2,
source_number,
tax_group,
payroll_id)
values (
l_lat_bal_id_ins(i),
l_def_bal_id_ins(i),
l_asg_act_id_ins(i),
l_value_ins(i),
l_per_id_ins(i),
l_e_asg_act_id_ins(i),
l_e_value_ins(i),
l_p_asg_act_id_ins(i),
l_p_value_ins(i),
l_tu_tab(i),
l_jc_tab(i),
l_oei_tab(i),
l_si_tab(i),
l_st_tab(i),
l_st2_tab(i),
l_sn_tab(i),
l_tg_tab(i),
l_pay_id_tab(i));
l_def_bal_id.delete;
l_asg_act_id.delete;
l_e_asg_act_id.delete;
l_p_asg_act_id.delete;
l_asg_id.delete;
l_per_id.delete;
l_value.delete;
l_e_value.delete;
l_p_value.delete;
l_lat_bal_id.delete;
l_cont_value.delete;
l_cont_name.delete;
l_def_bal_id_ins.delete;
l_asg_act_id_ins.delete;
l_e_asg_act_id_ins.delete;
l_p_asg_act_id_ins.delete;
l_asg_id_ins.delete;
l_per_id_ins.delete;
l_value_ins.delete;
l_e_value_ins.delete;
l_p_value_ins.delete;
l_lat_bal_id_ins.delete;
l_tu_tab.delete;
l_jc_tab.delete;
l_oei_tab.delete;
l_si_tab.delete;
l_st_tab.delete;
l_st2_tab.delete;
l_sn_tab.delete;
l_tg_tab.delete;
l_pay_id_tab.delete;
select /*+ ORDERED USE_NL(palb pbcv fc) */
palb.defined_balance_id,
palb.assignment_action_id,
palb.assignment_id,
palb.value,
palb.latest_balance_id,
palb.expired_assignment_action_id,
palb.expired_value,
palb.prev_balance_value,
palb.prev_assignment_action_id,
pbcv.value,
fc.context_name
bulk collect into
l_def_bal_id,
l_asg_act_id,
l_asg_id,
l_value,
l_lat_bal_id,
l_e_asg_act_id,
l_e_value,
l_p_value,
l_p_asg_act_id,
l_cont_value,
l_cont_name
from pay_assignment_latest_balances palb,
pay_balance_context_values pbcv,
ff_contexts fc
where palb.assignment_id = asgrec.assignment_id
and palb.latest_balance_id = pbcv.latest_balance_id (+)
and nvl(pbcv.context_id, -1) = fc.context_id (+)
order by palb.latest_balance_id;
insert into pay_latest_balances
(latest_balance_id,
defined_balance_id,
assignment_action_id,
value,
person_id,
assignment_id,
expired_assignment_action_id,
expired_value,
prev_assignment_action_id,
prev_balance_value,
tax_unit_id,
jurisdiction_code,
original_entry_id,
source_id,
source_text,
source_text2,
source_number,
tax_group,
payroll_id)
values (
l_lat_bal_id_ins(i),
l_def_bal_id_ins(i),
l_asg_act_id_ins(i),
l_value_ins(i),
l_per_id_ins(i),
l_asg_id_ins(i),
l_e_asg_act_id_ins(i),
l_e_value_ins(i),
l_p_asg_act_id_ins(i),
l_p_value_ins(i),
l_tu_tab(i),
l_jc_tab(i),
l_oei_tab(i),
l_si_tab(i),
l_st_tab(i),
l_st2_tab(i),
l_sn_tab(i),
l_tg_tab(i),
l_pay_id_tab(i));
select pee.element_entry_id,
pee.element_type_id,
pee.creator_type,
pee.source_id,
pee.source_asg_action_id,
pee.source_run_type
from pay_element_entries_f pee
where pee.creator_type in ('RR', 'EE')
and pee.assignment_id = p_asg_id;
select ''
into l_dummy
from pay_entry_process_details
where element_entry_id = eerec.element_entry_id;
select prr.source_id,
prr.element_type_id,
pay_core_utils.get_process_path(prr.assignment_action_id),
prr.assignment_action_id,
paa.source_action_id
into l_src_entry_id,
l_src_et_id,
l_ppath,
l_loc_aa_id,
l_loc_src_aa_id
from pay_run_results prr,
pay_assignment_actions paa
where prr.run_result_id = l_run_result_id
and prr.assignment_action_id = paa.assignment_action_id;
select assignment_action_id,
source_action_id
into l_loc_aa_id,
l_loc_src_aa_id
from pay_assignment_actions
where assignment_action_id = l_loc_src_aa_id;
update pay_element_entries_f
set source_asg_action_id = l_src_asg_act_id
where element_entry_id = eerec.element_entry_id;
select distinct pet.element_type_id
into l_src_et_id
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.element_entry_id = l_src_entry_id
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = pet.element_type_id;
select count(distinct(element_type_id))
into l_retro_et_cnt
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id;
select distinct(element_type_id)
into l_src_et_id
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id;
select distinct(element_type_id)
into l_src_et_id
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id
and rownum = 1;
select count(distinct(element_type_id))
into l_retro_et_cnt
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id;
select distinct element_type_id
into l_src_et_id
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id
and rownum = 1;
select pay_core_utils.get_process_path(assignment_action_id)
into l_ppath
from pay_assignment_actions
where source_action_id = l_src_asg_act_id
and run_type_id = eerec.source_run_type
and rownum = 1;
update pay_entry_process_details
set run_result_id = l_run_result_id,
source_entry_id = l_src_entry_id,
process_path = l_ppath,
source_asg_action_id = l_src_asg_act_id,
source_element_type_id = l_src_et_id
where element_entry_id = eerec.element_entry_id;
select prr.source_id,
prr.element_type_id,
pay_core_utils.get_process_path(prr.assignment_action_id),
prr.assignment_action_id,
paa.source_action_id
into l_src_entry_id,
l_src_et_id,
l_ppath,
l_loc_aa_id,
l_loc_src_aa_id
from pay_run_results prr,
pay_assignment_actions paa
where prr.run_result_id = l_run_result_id
and prr.assignment_action_id = paa.assignment_action_id;
select assignment_action_id,
source_action_id
into l_loc_aa_id,
l_loc_src_aa_id
from pay_assignment_actions
where assignment_action_id = l_loc_src_aa_id;
update pay_element_entries_f
set source_asg_action_id = l_src_asg_act_id
where element_entry_id = eerec.element_entry_id;
select distinct pet.element_type_id
into l_src_et_id
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.element_entry_id = l_src_entry_id
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = pet.element_type_id;
select count(distinct(element_type_id))
into l_retro_et_cnt
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id;
select distinct(element_type_id)
into l_src_et_id
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id;
select distinct(element_type_id)
into l_src_et_id
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id
and rownum = 1;
select count(distinct(element_type_id))
into l_retro_et_cnt
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id;
select distinct element_type_id
into l_src_et_id
from pay_element_types_f
where retro_summ_ele_id = eerec.element_type_id
and rownum = 1;
select pay_core_utils.get_process_path(assignment_action_id)
into l_ppath
from pay_assignment_actions
where source_action_id = l_src_asg_act_id
and run_type_id = eerec.source_run_type
and rownum = 1;
insert into pay_entry_process_details
(element_entry_id,
run_result_id,
source_entry_id,
process_path,
source_asg_action_id,
source_element_type_id
)
values
(eerec.element_entry_id,
l_run_result_id,
l_src_entry_id,
l_ppath,
l_src_asg_act_id,
l_src_et_id);
select ''
into l_dummy
from dual
where exists (select ''
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_object_id
and paa.payroll_action_id = ppa.payroll_action_id
and action_type = 'L');
select count(*)
into l_count
from pay_payroll_actions
where action_type = 'L';
select 'TRUE'
from pay_quickpay_inclusions
where rownum = 1;
SELECT /*+ ORDERED
USE_NL (ASGT_ACTION, EE, ET)
INDEX(
EE PAY_ELEMENT_ENTRIES_F_N50,
ET PAY_ELEMENT_TYPES_F_PK)
*/
DISTINCT ASGT_ACTION.assignment_action_id, EE.element_entry_id
FROM pay_assignment_actions ASGT_ACTION,
pay_payroll_actions PAY_ACTION,
pay_element_entries_f EE,
pay_element_types_f ET
WHERE ASGT_ACTION.assignment_id = p_asg_id
AND ASGT_ACTION.payroll_action_id = PAY_ACTION.payroll_action_id
AND PAY_ACTION.action_type = 'Q'
AND ASGT_ACTION.source_action_id is null
AND ASGT_ACTION.assignment_id = EE.assignment_id
AND EE.effective_start_date <= PAY_ACTION.date_earned
AND EE.effective_end_date >= DECODE (
ET.proration_group_id,
null, PAY_ACTION.date_earned,
pay_interpreter_pkg.prorate_start_date (
ASGT_ACTION.assignment_action_id, ET.proration_group_id
)
)
AND ET.element_type_id = EE.element_type_id
AND PAY_ACTION.date_earned BETWEEN ET.effective_start_date
AND ET.effective_end_date
--
-- Create exclusions for all entries that do not exist in
-- PAY_QUICKPAY_INCLUSIONS...
--
AND NOT EXISTS (
SELECT 'x'
FROM pay_quickpay_inclusions qi
WHERE qi.assignment_action_id = ASGT_ACTION.assignment_action_id
AND qi.element_entry_id = EE.element_entry_id
)
--
-- The QuickPay process will be modified to always ignore entries whose
-- element type has a process_in_run_flag of 'N', therefore these can also be
-- ignored...
--
AND ET.process_in_run_flag = 'Y'
--
-- The QuickPay process will be modified to always ignore balance adjustments,
-- replacement adjustments and additive adjustments, therefore these can also
-- be ignored...
--
AND EE.entry_type NOT IN ('B', 'A', 'R')
--
-- The QuickPay process will be modified to ignore nonrecurring entries that
-- have already been processed, therefore we only want to create exclusions for
-- nonrecurring entries that have not been processed...
--
AND ( ( ( (ET.processing_type = 'N'
)
--
-- Recurring additional or override entries are handled as if they
-- were non-recurring.
--
OR ( ET.processing_type = 'R'
AND EE.entry_type <> 'E'
)
)
AND (NOT EXISTS (SELECT null
FROM pay_run_results pr1
WHERE pr1.source_id = EE.element_entry_id
AND pr1.source_type = 'E'
AND pr1.status <> 'U'
)
OR EXISTS (SELECT null
FROM pay_run_results pr1
WHERE pr1.source_id = EE.element_entry_id
AND pr1.source_type = 'E'
AND pr1.status = 'U'
)
)
)
--
-- Exclude other recurring entries.
-- i.e. Those which are not additional or overrides entries.
--
OR ( ET.processing_type = 'R'
AND EE.entry_type = 'E'
)
);
insert into pay_quickpay_exclusions (
assignment_action_id,
element_entry_id,
last_update_date,
last_updated_by,
created_by,
creation_date
)
values (
asgt_action_ids(i),
element_entry_ids(i),
SYSDATE,
1,
1,
SYSDATE
);
select ''
into l_dummy
from dual
where exists (select ''
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_object_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = 'Q');
select ''
into l_dummy
from dual
where exists (select ''
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_object_id
and paa.payroll_action_id = ppa.payroll_action_id
and action_type in ('R', 'Q', 'B', 'V', 'I'));
Purpose : This procedure is used to delete any null value run
result values for an assignment
NB Other than the Jurisidiction code result value
Arguments :
Notes :
*/
--
procedure upg_sparse_matrix_rrvs (p_assignment_id in number)
is
--
type t_asg_act_id is table of pay_assignment_actions.assignment_action_id%type
index by binary_integer;
select /*+ ORDERED use_nl(aa pa)*/
aa.assignment_action_id, pa.effective_date
from pay_assignment_actions aa,
pay_payroll_actions pa
where aa.assignment_id = p_assignment_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.action_type in ('R', 'Q', 'B', 'V', 'I');
select pbg.legislation_code, pbg.business_group_id
into l_leg_code, l_bus_grp
from per_all_assignments_f asg,
per_business_groups_perf pbg
where asg.assignment_id = p_assignment_id
and pbg.business_group_id = asg.business_group_id
and rownum = 1;
delete from pay_run_result_values rrv
where result_value is null
and run_result_id in
(select rr.run_result_id
from pay_run_results rr
where rr.assignment_action_id = asgt_action_ids(i))
and not exists
(select 1
from pay_input_values_f iv
where iv.input_value_id = rrv.input_value_id
and run_dates(i) between iv.effective_start_date
and iv.effective_end_date
and iv.name = g_jur_name);
delete from pay_run_result_values rrv
where result_value is null
and run_result_id in
(select rr.run_result_id
from pay_run_results rr
where rr.assignment_action_id = asgt_action_ids(i));
select ''
into l_dummy
from dual
where exists (select ''
from pay_latest_balances plb
where plb.person_id = p_object_id
);
select object_group_id
from pay_object_groups
where source_id = p_person_id
and source_type = 'PPF';
update pay_latest_balances
set process_group_id = l_process_group_id
where person_id = p_person_id
and assignment_id is null
and process_group_id is null
and defined_balance_id in (
select db.defined_balance_id from
pay_balance_dimensions d,
pay_defined_balances db
where d.balance_dimension_id = db.balance_dimension_id
and d.dimension_type = 'O'
and d.dimension_level = 'PG');
delete from pay_latest_balances
where assignment_id is null
and person_id = p_person_id
and process_group_id is null
and defined_balance_id in (
select db.defined_balance_id from pay_balance_dimensions d, pay_defined_balances db
where d.balance_dimension_id = db.balance_dimension_id
and d.dimension_type = 'O'
and d.dimension_level = 'PG');
select ''
into l_dummy
from dual
where exists (select ''
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_object_id
and paa.payroll_action_id = ppa.payroll_action_id
and action_type in ('R', 'Q', 'B', 'I'));
select pee.element_entry_id,
nvl(pee.source_start_date, ptp.start_date) start_date,
nvl(pee.source_end_date, ptp.end_date) end_date,
decode(nvl(p_std_time_def_id, -1),
-1, null,
decode(nvl(pet.time_definition_type, 'N'),
'S', pet.time_definition_id,
'G', p_std_time_def_id,
null)
) time_definition_id
from pay_element_entries_f pee,
pay_entry_process_details pepd,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_element_types_f pet
where pee.creator_type in ('RR', 'EE')
and pee.assignment_id = p_asg_id
and pee.element_entry_id = pepd.element_entry_id
and pepd.source_asg_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and pet.element_type_id = pepd.source_element_type_id
and ppa.date_earned between pet.effective_start_date
and pet.effective_end_date
and ppa.date_earned between ptp.start_date
and ptp.end_date;
SELECT
prr.run_result_id,
decode(nvl(pet.time_definition_type, 'N'),
'S', pet.time_definition_id,
'G', p_std_time_def_id,
null) time_definition_id,
ppa.action_type
from
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f pet,
pay_run_results prr
where paa.assignment_id = p_asg_id
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B', 'V', 'I')
and prr.element_type_id = pet.element_type_id
and prr.time_definition_id is null
and ppa.date_earned between pet.effective_start_date
and pet.effective_end_date
and pet.time_definition_type in ('G', 'S')
order by decode(action_type,
'V', 2,
1);
select distinct pbg.business_group_id,
pbg.legislation_code
into l_bus_grpid,
l_leg_code
from per_business_groups_perf pbg,
per_all_assignments_f paf
where paf.assignment_id = p_asg_id
and paf.business_group_id = pbg.business_group_id;
select ptd.time_definition_id
into l_std_time_def_id
from pay_time_definitions ptd
where ptd.short_name = l_leg_code||'_STANDARD';
update pay_run_results
set time_definition_id = l_td_tab(i)
where run_result_id = l_rr_tab(i);
update pay_run_results prr
set prr.start_date = (select pee.date_earned
from pay_element_entries_f pee
where pee.element_entry_id = prr.source_id
),
prr.end_date = (select pee.date_earned
from pay_element_entries_f pee
where pee.element_entry_id = prr.source_id
)
where prr.run_result_id = l_rr_tab(i)
and exists (select ''
from pay_element_entries_f pee
where pee.element_entry_id = prr.source_id
and pee.date_earned is not null)
and prr.start_date is null
and l_action(i) <> 'V';
update pay_run_results prr
set prr.start_date = (select prr.start_date
from pay_run_results prr2
where prr2.run_result_id = prr.source_id
),
prr.end_date = (select prr.end_date
from pay_run_results prr2
where prr2.run_result_id = prr.source_id
)
where prr.run_result_id = l_rr_tab(i)
and exists (select ''
from pay_run_results prr2
where prr2.run_result_id = prr.source_id
and prr2.start_date is not null)
and prr.start_date is null
and l_action(i) = 'V';
update pay_element_entries_f
set source_start_date = l_std_tab(i),
source_end_date = l_end_tab(i)
where element_entry_id = l_ee_tab(i);
update pay_entry_process_details
set time_definition_id = l_td_tab(i)
where element_entry_id = l_ee_tab(i);
update pay_run_results
set start_date = l_std_tab(i),
end_date = l_end_tab(i),
time_definition_id = l_td_tab(i)
where source_id = l_ee_tab(i)
and source_type in ('E', 'I');
update pay_run_results prr
set prr.start_date = l_std_tab(i),
prr.end_date = l_end_tab(i),
prr.time_definition_id = l_td_tab(i)
where prr.source_id in (select prr1.run_result_id
from pay_run_results prr1
where prr1.source_id = l_ee_tab(i)
and prr1.source_type in ('E', 'I')
)
and prr.source_type in ('R', 'V');
select ''
into l_dummy
from dual
where exists (select ''
from per_all_assignments_f asg
where asg.assignment_id = p_object_id
and asg.people_group_id is not null
and asg.assignment_type in ('A','O'));
select asg.assignment_type,
asg.effective_start_date,
asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id = p_asg_id
order by asg.effective_start_date;
select alu.assignment_link_usage_id,
alu.effective_start_date,
alu.effective_end_date
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_asg_id
and alu.effective_start_date <= p_asg_effective_end_date
and alu.effective_end_date >= p_asg_effective_start_date;
l_alu_update_table t_alu_table_rec;
l_alu_delete_table t_alu_table_rec;
l_update_counter number := 1;
l_delete_counter number := 1;
delete pay_assignment_link_usages_f alu
where not exists (
select null
from per_all_assignments_f asg
where asg.assignment_id = alu.assignment_id
and asg.assignment_type not in ('A','O')
)
and alu.assignment_id = p_assignment_id;
l_alu_update_table.alu_id(l_update_counter) :=
r_alu.assignment_link_usage_id;
l_alu_update_table.effective_start_date(l_update_counter) :=
r_next_asg.effective_start_date;
l_update_counter := l_update_counter + 1;
l_alu_delete_table.alu_id(l_delete_counter) :=
r_alu.assignment_link_usage_id;
l_alu_delete_table.effective_start_date(l_delete_counter) :=
r_alu.effective_start_date;
l_delete_counter := l_delete_counter + 1;
if l_alu_update_table.alu_id.count > 0 then
--
-- Do bulk update of ALU start dates
--
forall i in 1 .. l_alu_update_table.alu_id.count
update pay_assignment_link_usages_f
set effective_start_date = l_alu_update_table.effective_start_date(i)
where assignment_link_usage_id = l_alu_update_table.alu_id(i);
if l_alu_delete_table.alu_id.count > 0 then
--
-- Do bulk delete of ALUs that only belong to applicant assignments
--
forall i in 1 .. l_alu_delete_table.alu_id.count
delete pay_assignment_link_usages_f
where assignment_link_usage_id = l_alu_delete_table.alu_id(i)
and effective_start_date = l_alu_delete_table.effective_start_date(i);
l_update_counter := 1;
l_delete_counter := 1;
l_alu_update_table.alu_id.delete;
l_alu_update_table.effective_start_date.delete;
l_alu_delete_table.alu_id.delete;
l_alu_delete_table.effective_start_date.delete;
select ''
into l_dummy
from dual
where exists (select ''
from per_all_assignments_f asg
where asg.assignment_id = p_object_id
and asg.assignment_type = 'E'
and asg.people_group_id is not null);