[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
condition where the insert of
payroll action inserts no rows.
Caused ora-08002 error, currval
not defined. Note changes
elsewhere (pydynsql.pkb) should
prevent this situation occurring
but this is a safety measure.
RThirlby 02-MAY-2002 115.8 2348875 Altered != to <> for gscc
standards.
D. Saxby 25-APR-2002 115.7 2341428 Altered pypurgbv to detect
condition where the insert of
payroll action inserts no rows.
Caused ora-08002 error, currval
not defined. Note changes
elsewhere (pydynsql.pkb) should
prevent this situation occurring
but this is a safety measure.
D. Saxby 16-JAN-2002 115.6 2179667 Ensure that elements can be
correctly created in NLS envs.
D. Saxby 18-DEC-2001 115.5 GSCC standards fix.
D. Saxby 14-NOV-2001 115.4 Added procedure pypurgbv.
D. Saxby 06-AUG-2001 115.2 Added procedure pypurcif.
D. Saxby 15-DEC-2000 115.1 Amended order by statements.
D. Saxby 12-DEC-2000 115.0 Initial Version
========================================================================
*/
--
-- Global Types
--
type t_asgact_rec is record
(assignment_action_id number
,action_status pay_assignment_actions.action_status%type
,action_sequence number
-- Payroll action information
,payroll_action_id number
,action_type pay_payroll_actions.action_type%type
,business_group_id number
,action_population_status pay_payroll_actions.action_population_status%type
,ppa_action_status pay_payroll_actions.action_status%type
,effective_date date
,date_earned date
-- Assignment information
,assignment_id number
,payroll_id number
,time_period_id number
,rollup_date date -- The rollup date for terminated assignment
);
select
act.action_status
,act.action_sequence
-- Payroll action information
,act.payroll_action_id
,pac.action_type
,pac.business_group_id
,pac.action_population_status
,pac.action_status ppa_action_status
,pac.effective_date
,pac.date_earned
-- Assignment information
,act.assignment_id
,act.end_date rollup_date
from
pay_payroll_actions pac
,pay_assignment_actions act
where
act.assignment_action_id = p_assignment_action_id
and pac.payroll_action_id = act.payroll_action_id
;
select
-- Assignment information
asg.payroll_id
,ptp.time_period_id
from
per_all_assignments_f asg
,per_time_periods ptp
where
asg.assignment_id = p_assignment_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and ptp.payroll_id = asg.payroll_id
and p_effective_date between
ptp.start_date and ptp.end_date;
select
ppa.action_type
,ppa.business_group_id
,pbg.legislation_code
,ppa.effective_date
,ppa.balance_set_id
from
pay_payroll_actions ppa
,per_business_groups_perf pbg
where
ppa.payroll_action_id = p_purge_action_id
and pbg.business_group_id = ppa.business_group_id
;
select
distinct pdb.balance_type_id
from
pay_balance_set_members pbsm
,pay_defined_balances pdb
where
pbsm.balance_set_id = p_bal_set_id
and pdb.defined_balance_id = pbsm.defined_balance_id
order by pdb.balance_type_id
;
select count(assignment_action_id)
into g_purge_action_rec.asg_count
from pay_assignment_actions
where payroll_action_id = p_purge_action_id
and action_status <> 'C';
g_bal_exists.delete;
select
/*+ ordered
use_nl (piv prrv) */
count(1)
from
pay_balance_feeds_f pbf
,pay_input_values_f piv
,pay_run_result_values prrv
where
pbf.balance_type_id = p_baltypid
and pbf.effective_start_date <= p_purge_date
and piv.input_value_id = pbf.input_value_id
and pbf.effective_start_date between piv.effective_start_date
and piv.effective_end_date
and nvl(piv.business_group_id, p_bg_id) = p_bg_id
and nvl(piv.legislation_code, p_leg_code) = p_leg_code
and prrv.input_value_id = piv.input_value_id
and rownum < p_limit+2
;
select
/*+ ordered
index (prr PAY_RUN_RESULTS_N1)
index (purge_paa PAY_ASSIGNMENT_ACTIONS_N51)
use_nl (piv prr paa ppa purge_paa prrv) */
1
from
pay_balance_feeds_f pbf
,pay_input_values_f piv
,pay_run_results prr
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_assignment_actions purge_paa
,pay_run_result_values prrv
where
pbf.balance_type_id = p_baltypid
and pbf.effective_start_date <= p_purge_date
and piv.input_value_id = pbf.input_value_id
and pbf.effective_start_date between piv.effective_start_date
and piv.effective_end_date
and nvl(piv.business_group_id, p_bg_id) = p_bg_id
and nvl(piv.legislation_code, p_leg_code) = p_leg_code
and prr.element_type_id = piv.element_type_id
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id+0 = p_bg_id
and ppa.effective_date <= p_purge_date
and ppa.effective_date between pbf.effective_start_date
and pbf.effective_end_date
and purge_paa.payroll_action_id = p_purge_pactid
and purge_paa.assignment_id = paa.assignment_id
-- not including completed purge assignment actions.
and purge_paa.action_status <> 'C'
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = piv.input_value_id
and prrv.result_value is not null
;
select
ppa.effective_date
,paa.assignment_id
,paa.end_date
from
pay_assignment_actions paa
,pay_payroll_actions ppa
where
paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
;
select
1
from
per_all_assignments_f asg
,per_time_periods ptp
where
asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and ptp.payroll_id = asg.payroll_id
and p_effective_date between ptp.start_date
and ptp.end_date;
select
asg.effective_end_date end_date
from
per_all_assignments_f asg
,per_time_periods ptp
where
asg.assignment_id = p_assignment_id
and asg.effective_end_date <= p_effective_date
and ptp.payroll_id = asg.payroll_id
and asg.effective_end_date between ptp.start_date
and ptp.end_date
UNION ALL
--
-- The time period ends before the asg end date.
-- Effective Date
-- Asg |------------------> |
-- Prd |----->|----->
--
select
ptp.end_date end_date
from
per_all_assignments_f asg
,per_time_periods ptp
where
asg.assignment_id = p_assignment_id
and asg.effective_start_date <= p_effective_date
and ptp.payroll_id = asg.payroll_id
and ptp.end_date <= p_effective_date
and ptp.end_date between asg.effective_start_date
and asg.effective_end_date
order by 1 desc;
update pay_assignment_actions
set end_date = null
where assignment_action_id = p_assignment_action_id;
update pay_assignment_actions
set end_date = l_rollup_date
where assignment_action_id = p_assignment_action_id;
select count(*)
into l_pat_count
from pay_purge_action_types pat;
select distinct
ffc.context_id,
ffc.context_name,
rep.jurisdiction_code
from pay_us_asg_reporting rep,
ff_contexts ffc
where rep.assignment_id = p_assignment_id
and ffc.context_name = 'JURISDICTION_CODE'
and rep.jurisdiction_code is not null
union all
--
-- Check the assignment action to restrict the contexts
-- with the purge date. Bug 5089841.
--
select distinct
ffc.context_id,
ffc.context_name,
to_char(paa.tax_unit_id)
from ff_contexts ffc,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ffc.context_name = 'TAX_UNIT_ID'
and paa.assignment_id = p_assignment_id
and paa.tax_unit_id is not null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date <= p_purge_date
order by 1, 3;
select ffc.context_id,
ffc.context_name,
to_char(act.tax_unit_id) context_value
from ff_contexts ffc,
pay_assignment_actions act,
pay_payroll_actions pac
where act.assignment_id = p_assignment_id
and pac.payroll_action_id = act.payroll_action_id
and pac.effective_date <= p_purge_date
and act.tax_unit_id is not null
and ffc.context_name = 'TAX_UNIT_ID'
union
select ctx.context_id,
ffc.context_name,
ctx.context_value
from pay_action_contexts ctx,
ff_contexts ffc,
pay_assignment_actions act,
pay_payroll_actions pac
where ctx.assignment_id = p_assignment_id
and ffc.context_id = ctx.context_id
and ffc.context_name in ('JURISDICTION_CODE',
'ORIGINAL_ENTRY_ID',
'ELEMENT_ENTRY_ID',
'SOURCE_ID',
'SOURCE_TEXT')
and act.assignment_action_id = ctx.assignment_action_id
and pac.payroll_action_id = act.payroll_action_id
and pac.effective_date <= p_purge_date
order by 1, 3;
p_select_type in varchar2
) is
begin
-- Open the appropriate cursor, depending on
-- the string passed in.
if(p_select_type = 'ASG_REPORTING') then
open_asg_rep_cur(p_ctx_cursor, p_assignment_id, p_purge_date);
elsif(p_select_type = 'ACT_CONTEXTS') then
open_act_ctx_cur(p_ctx_cursor, p_assignment_id, p_purge_date);
select /*+ INDEX(act PAY_ASSIGNMENT_ACTIONS_N51) */
distinct
act.rowid
,act.assignment_action_id
,act.action_sequence
from pay_assignment_actions act,
pay_balance_batch_lines bbl
where bbl.batch_id = p_batch_id
and act.payroll_action_id = bbl.payroll_action_id
and act.assignment_id = bbl.assignment_id
order by act.action_sequence desc;
l_update_count number := 0;
update pay_assignment_actions act
set act.action_sequence = l_action_sequence
where act.rowid = c1rec.rowid;
update pay_run_balances prb
set prb.action_sequence = l_action_sequence
where prb.assignment_action_id = c1rec.assignment_action_id;
l_update_count := l_update_count + 1;
ff_utils.assert((l_update_count > 0), 'pypu2uacs:2');
select
pbd.period_type
from
pay_defined_balances pdb
,pay_balance_dimensions pbd
where
pdb.defined_balance_id = p_defined_balance_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
;
* Before we call the get_value function, we insert
* a temporary assignment and payroll action.
* This is done to ensure that values of time_period_id
* and payroll_id are set on the payroll action associated
* with the assignment action whoes value is passed to
* the get_value call.
* In addition, the assignment action must have the same
* action_sequence value as that for the current purge
* action.
* As these are somewhat unique requirements, decided
* that would be better to do this here, rather than
* further complicate the balance code.
* We'd love to just update the purge payroll action
* to set the time_period_id, but expect this to be
* a major locking issue.
*/
savepoint get_value;
select pay_payroll_actions_s.nextval
into l_new_payactid
from dual;
insert into pay_payroll_actions (
payroll_action_id,
action_type,
business_group_id,
payroll_id,
action_population_status,
action_status,
effective_date,
date_earned,
time_period_id,
object_version_number)
values(l_new_payactid,
l_asgact_rec.action_type,
l_asgact_rec.business_group_id,
l_asgact_rec.payroll_id,
l_asgact_rec.action_population_status,
l_asgact_rec.ppa_action_status,
nvl(l_asgact_rec.rollup_date, l_asgact_rec.effective_date),
nvl(l_asgact_rec.rollup_date, l_asgact_rec.date_earned),
l_asgact_rec.time_period_id,
1);
select pay_assignment_actions_s.nextval
into l_new_assactid
from dual;
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
action_sequence,
object_version_number)
values(l_new_assactid,
l_asgact_rec.assignment_id,
l_new_payactid,
l_asgact_rec.action_status,
l_asgact_rec.action_sequence,
1);
select rub.defined_balance_id,
rub.jurisdiction_code,
rub.original_entry_id,
rub.tax_unit_id,
rub.value
from pay_purge_rollup_balances rub
where rub.assignment_action_id = p_assignment_action_id;
select upper(replace(pbt.balance_name, ' ', '_')) ||
pbd.database_item_suffix
into l_balance_name
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.defined_balance_id = c1rec.defined_balance_id
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id;
select pbt.balance_type_id,
pbt.balance_name,
pbt.balance_uom,
pbt.currency_code,
nvl(pbt.jurisdiction_level, 0) jurisdiction_level
from pay_balance_set_members bsm,
pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where bsm.balance_set_id = p_balance_set_id
and pdb.defined_balance_id = bsm.defined_balance_id
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pay_balance_upload.dim_is_supported
(p_legislation_code, pbd.dimension_name) = 'Y'
union /* do not return duplicates */
select pbt.balance_type_id,
pbt.balance_name,
pbt.balance_uom,
pbt.currency_code,
nvl(pbt.jurisdiction_level, 0) jurisdiction_level
from ff_fdi_usages_f fdu,
ff_formulas_f fff,
ff_database_items fdi,
ff_user_entities fue,
pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where ((fff.business_group_id is null and fff.legislation_code is null)
or (fff.business_group_id is null
and fff.legislation_code = p_legislation_code)
or (fff.legislation_code is null
and fff.business_group_id = p_business_group_id))
and fdu.formula_id = fff.formula_id
and fdu.usage = 'D'
and fdi.user_name = fdu.item_name
and fue.user_entity_id = fdi.user_entity_id
and ((fue.business_group_id is null and fue.legislation_code is null)
or (fue.business_group_id is null
and fue.legislation_code = p_legislation_code)
or (fue.legislation_code is null
and fue.business_group_id = p_business_group_id))
and fue.creator_type = 'B'
and pdb.defined_balance_id = fue.creator_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_level = 'ASG'
and pbd.period_type = 'LIFETIME'
and pay_balance_upload.dim_is_supported
(p_legislation_code, pbd.dimension_name) = 'Y'
and pbt.balance_type_id = pdb.balance_type_id
order by 1; -- balance_type_id.
select pay_element_types_s.nextval,
hrl.meaning
into l_et_id,
l_ptr
from hr_lookups hrl
where hrl.lookup_type = 'TERMINATION_RULE'
and hrl.lookup_code = 'F';
update pay_element_types_f pet
set pet.element_information1 = 'B'
where pet.element_type_id = l_et_id;
select pet.element_name,
pet.element_type_id,
pet.business_group_id
into l_et_name,
l_et_id,
l_bg_id
from pay_element_links_f pel,
pay_element_types_f pet
where pel.element_link_id = p_el_id
and pel.effective_start_date = c_sot
and pel.effective_end_date = c_eot
and pet.element_type_id = pel.element_type_id
and pet.effective_start_date = c_sot
and pet.effective_end_date = c_eot;
p_insert_type => 'INSERT_INPUT_VALUE',
p_element_link_id => p_el_id,
p_input_value_id => l_iv_id,
p_input_value_name => p_iv_name,
p_costable_type => NULL,
p_validation_start_date => c_sot,
p_validation_end_date => c_eot,
p_default_value => NULL,
p_max_value => NULL,
p_min_value => NULL,
p_warning_or_error_flag => NULL,
p_hot_default_flag => NULL,
p_legislation_code => NULL,
p_pay_value_name => NULL,
p_element_type_id => l_et_id);
select pbg.name
into l_bg_name
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select 1
into l_dummy
from pay_balance_feeds_f pbf,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_classifications pec
where pbf.balance_type_id = c1rec.balance_type_id
and piv.input_value_id = pbf.input_value_id
and pet.element_type_id = piv.element_type_id
and pec.classification_id = pet.classification_id
and pec.balance_initialization_flag = 'Y'
and pbf.effective_start_date = c_sot
and pbf.effective_end_date = c_eot
and piv.effective_start_date = c_sot
and piv.effective_end_date = c_eot
and piv.effective_start_date = c_sot
and piv.effective_end_date = c_eot;
l_et_tab.delete(l_el_id);