The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update boolean;
l_update := FALSE;
l_update := TRUE;
l_update := TRUE;
if (l_update = TRUE) then
begin
--
select balance_validation_id
into l_bal_valid_id
from pay_balance_validation
where defined_balance_id = p_defined_balace_id
and business_group_id = p_business_group_id;
update pay_balance_validation
set run_balance_status = p_status,
balance_load_date = g_start_date
where balance_validation_id = l_bal_valid_id;
insert into pay_balance_validation
(balance_validation_id,
defined_balance_id,
business_group_id,
run_balance_status,
balance_load_date)
values ( pay_balance_validation_s.nextval,
p_defined_balace_id,
p_business_group_id,
p_status,
g_start_date);
select pdb.defined_balance_id,
ppa.business_group_id,
nvl(pay_core_utils.get_parameter('BAL_LVL',
ppa.legislative_parameters),
'BOTH') balance_level,
pbd.dimension_level
from pay_payroll_actions ppa,
per_business_groups pbg,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where ppa.payroll_action_id = p_pact_id
and ppa.business_group_id = pbg.business_group_id
and ((pdb.business_group_id = pbg.business_group_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = pbg.legislation_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
)
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and ((pbd.dimension_level =
nvl(pay_core_utils.get_parameter('BAL_LVL',
ppa.legislative_parameters),
'BOTH'))
or
(nvl(pay_core_utils.get_parameter('BAL_LVL',
ppa.legislative_parameters),
'BOTH')
= 'BOTH')
);
select nvl(pbv.run_balance_status, 'I')
into l_run_bal_stat
from pay_defined_balances pdb,
pay_balance_validation pbv
where pdb.defined_balance_id = dbrec.defined_balance_id
and pbv.defined_balance_id (+) = pdb.defined_balance_id
and pbv.business_group_id (+) = dbrec.business_group_id;
update pay_balance_validation
set balance_load_date = g_start_date
where dbrec.defined_balance_id = defined_balance_id
and dbrec.business_group_id = business_group_id;
select business_group_id
into l_bus_grp
from pay_payroll_actions
where payroll_action_id = p_pactid;
select nvl(pbv.run_balance_status, 'I')
into l_run_bal_stat
from pay_defined_balances pdb,
pay_balance_validation pbv
where pdb.defined_balance_id = g_def_bal_id
and pbv.defined_balance_id (+) = pdb.defined_balance_id
and pbv.business_group_id (+) = l_bus_grp;
update pay_balance_validation
set balance_load_date = g_start_date
where defined_balance_id = g_def_bal_id
and business_group_id = l_bus_grp;
select
pay_core_utils.get_parameter('DEF_BAL_ID',
pa1.legislative_parameters),
nvl(pay_core_utils.get_parameter('BAL_LVL',
pa1.legislative_parameters),
'BOTH'),
nvl(pay_core_utils.get_parameter('PROC_MODE',
pa1.legislative_parameters),
'ALL'),
to_date(pay_core_utils.get_parameter('BAL_START_DATE',
pa1.legislative_parameters),
'YYYY/MM/DD'),
nvl(pay_core_utils.get_parameter('LOAD_TYPE',
pa1.legislative_parameters),
'ALL'),
pbg.legislation_code,
pbg.business_group_id,
'GEN_BAL_'||p_pact_id
into
g_def_bal_id,
g_bal_lvl,
g_proc_mode,
g_start_date,
g_load_type,
g_leg_code,
g_bus_grp,
g_att_name
from pay_payroll_actions pa1,
per_business_groups pbg
where pa1.payroll_action_id = p_pact_id
and pa1.business_group_id = pbg.business_group_id;
select rule_mode
into g_save_run_bals
from pay_legislation_rules
where legislation_code = g_leg_code
and rule_type = 'SAVE_RUN_BAL';
select rule_mode
into g_save_asg_run_bals
from pay_legislation_rules
where legislation_code = g_leg_code
and rule_type = 'SAVE_ASG_RUN_BAL';
select nvl(pbd.dimension_level, 'ASG')
into g_bal_lvl
from pay_defined_balances pdb,
pay_balance_dimensions pbd
where pdb.defined_balance_id = g_def_bal_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
l_delete_bals boolean;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
from pay_run_balances prb
where prb.defined_balance_id = g_def_bal_id
and prb.assignment_action_id = p_asg_act_id;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
null;
l_delete_bals boolean;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
--
hr_utility.set_location(
'pay_run_balance_build.calculate_full_asg_balances',30);
delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
from pay_run_balances prb
where prb.defined_balance_id = g_def_bal_id
and prb.assignment_action_id = p_asg_act_id;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
hr_utility.set_location(
'pay_run_balance_build.calculate_full_asg_balances',60);
select /*+ ORDERED USE_NL(ppa prt)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
INDEX(prt PAY_RUN_TYPES_PK) */
paa.assignment_action_id,
ppa.effective_date,
nvl(prt.run_method, 'N') run_method,
ppa.business_group_id,
pbg.legislation_code
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_types_f prt,
per_business_groups_perf pbg
where paa.assignment_id = p_assid
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and ppa.business_group_id = pbg.business_group_id
and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
and nvl(prt.effective_end_date, ppa.effective_date)
order by 2;
delete /*+ USE_NL(prb) INDEX(prb PAY_RUN_BALANCES_N1) */
from pay_run_balances prb
where prb.assignment_id = p_asg_id
and prb.defined_balance_id in
(select pba.defined_balance_id
from pay_balance_attributes pba
, pay_bal_attribute_definitions bad
where pba.attribute_id = bad.attribute_id
and bad.attribute_name = g_att_name);
delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
from pay_run_balances prb
where prb.assignment_id = p_asg_id;
delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
from pay_run_balances prb
where prb.assignment_id = p_asg_id
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = prb.defined_balance_id
and pbv.business_group_id = p_bus_grp
and prb.effective_date < greatest(nvl(pbv.balance_load_date,
to_date('0001/01/01 00:00:00',
'YYYY/MM/DD HH24:MI:SS')
),
nvl(g_start_date,
to_date('0001/01/01 00:00:00',
'YYYY/MM/DD HH24:MI:SS')
)
)
);
select balance_load_date,
run_balance_status
into l_bal_load_date,
l_run_bal_status
from pay_balance_validation
where defined_balance_id = g_def_bal_id
and business_group_id = p_bus_grp;
l_delete_bals boolean;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
delete from pay_run_balances
where defined_balance_id = g_def_bal_id
and payroll_action_id = p_pactid;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
if (g_proc_mode = 'INVALID') then
--
delete from pay_run_balances prb
where prb.payroll_action_id = p_pactid
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id =
prb.defined_balance_id
and pbv.run_balance_status = 'P'
and pbv.business_group_id = p_bus_grp);
delete from pay_run_balances
where payroll_action_id = p_pactid;
delete
from pay_run_balances prb
where prb.payroll_action_id = p_pactid
and g_start_date is not null
and g_start_date > p_eff_date;
l_delete_bals boolean;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
delete from pay_run_balances
where defined_balance_id = g_def_bal_id
and payroll_action_id = p_pactid;
l_delete_bals := FALSE;
l_delete_bals := TRUE;
if (l_delete_bals) then
--
if (g_proc_mode = 'INVALID') then
--
delete from pay_run_balances prb
where prb.payroll_action_id = p_pactid
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = prb.defined_balance_id
and pbv.run_balance_status = 'P'
and pbv.business_group_id = p_bus_grp);
delete from pay_run_balances
where payroll_action_id = p_pactid;
select ppa.effective_date,
ppa.business_group_id,
pbg.legislation_code
into l_grp_eff_date,
l_bus_grp,
l_leg_code
from pay_payroll_actions ppa,
per_business_groups pbg
where ppa.payroll_action_id = p_pactid
and ppa.business_group_id = pbg.business_group_id;
delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
from pay_run_balances prb
where prb.payroll_action_id = p_pactid
and exists (select /*+ INDEX(pbv PAY_BALANCE_VALIDATION_UK1) */ ''
from pay_balance_validation pbv
where pbv.defined_balance_id = prb.defined_balance_id
and pbv.run_balance_status = 'P'
and pbv.business_group_id = l_bus_grp);
delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
from pay_run_balances prb
where prb.payroll_action_id = p_pactid;
delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
from pay_run_balances prb
where prb.payroll_action_id = p_pactid
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = prb.defined_balance_id
and pbv.business_group_id = l_bus_grp
and prb.effective_date < greatest(nvl(pbv.balance_load_date,
to_date('0001/01/01 00:00:00',
'YYYY/MM/DD HH24:MI:SS')
),
nvl(g_start_date,
to_date('0001/01/01 00:00:00',
'YYYY/MM/DD HH24:MI:SS')
)
)
);
select balance_load_date,
run_balance_status
into l_bal_load_date,
l_run_bal_status
from pay_balance_validation
where defined_balance_id = g_def_bal_id
and business_group_id = l_bus_grp;
select paa.assignment_action_id,
ppa.effective_date,
nvl(prt.run_method, 'N') run_method,
ppa.business_group_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_types_f prt,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_assactid
and paa_arch.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
and nvl(prt.effective_end_date, ppa.effective_date)
order by 2;
l_delete_bals boolean;
select paa.payroll_action_id,
paa.object_id,
paa.assignment_id,
ppa.business_group_id
into l_payroll_action_id, l_object_id, l_asg_id, l_bus_grp
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_assactid
and ppa.payroll_action_id = paa.payroll_action_id;
select sysdate
into current_date
from sys.dual;
select
pdb.defined_balance_id
from
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbd.dimension_level = 'GRP'
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pbt.balance_type_id
and ((pdb.business_group_id = p_bus_grp_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = p_leg_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
)
and ( p_bal_list <> 'INVALID'
or ( p_bal_list = 'INVALID'
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = pdb.defined_balance_id
and pbv.business_group_id = p_bus_grp_id
and pbv.run_balance_status = 'P')
)
)
and ( p_def_bal is null
or p_def_bal = pdb.defined_balance_id
);
select
pdb.defined_balance_id
from
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbd.dimension_level = 'ASG'
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pbt.balance_type_id
and ( p_bal_list <> 'INVALID'
or ( p_bal_list = 'INVALID'
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = pdb.defined_balance_id
and pbv.business_group_id = p_bus_grp_id
and pbv.run_balance_status = 'P')
)
)
and ( p_def_bal is null
or p_def_bal = pdb.defined_balance_id
)
and ((pdb.business_group_id = p_bus_grp_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = p_leg_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
);
select pay_bal_attribute_definition_s.nextval
into l_attribute_id
from dual;
insert into pay_bal_attribute_definitions
(attribute_id,
attribute_name,
alterable,
business_group_id)
values (l_attribute_id,
l_attribute_name,
'N',
g_bus_grp);
insert into pay_balance_attributes
(balance_attribute_id,
attribute_id,
defined_balance_id
)
values
(pay_balance_attributes_s.nextval,
l_attribute_id,
grprec.defined_balance_id
);
insert into pay_balance_attributes
(balance_attribute_id,
attribute_id,
defined_balance_id
)
values
(pay_balance_attributes_s.nextval,
l_attribute_id,
asgrec.defined_balance_id
);
Purpose : This returns the select statement that is used to created the
range rows for the Tax Filing (FLS) Archiver.
Arguments :
Notes :
*/
PROCEDURE action_range_cursor( p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
--
lv_sql_string VARCHAR2(32000);
'select 1
from dual
where 1 = 0
and 1 = :payroll_action_id';
'select ppa_r.payroll_action_id
from pay_payroll_actions ppa,
pay_payroll_actions ppa_r
where ppa.payroll_action_id = :payroll_action_id
and ppa_r.action_type in (''R'',''Q'',''B'', ''I'',''V'')
and ppa.business_group_id = ppa_r.business_group_id
order by ppa_r.payroll_action_id';
'select distinct asg.person_id
from
per_periods_of_service pos,
per_assignments_f asg,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and pos.person_id = asg.person_id
and pos.period_of_service_id = asg.period_of_service_id
and pos.business_group_id = ppa.business_group_id
and asg.business_group_id = ppa.business_group_id
order by asg.person_id';
select ppa.payroll_action_id
from pay_payroll_actions ppa,
pay_payroll_actions ppa_arc
where ppa.payroll_action_id between stpactid and enpactid
and ppa_arc.payroll_action_id = pact_id
and ppa.business_group_id = ppa_arc.business_group_id
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
order by 1;
select distinct
paf.assignment_id assignment_id,
paf.person_id
from
per_periods_of_service pos,
per_all_assignments_f paf,
pay_payroll_actions ppa
where pos.person_id between stperson and endperson
and pos.person_id = paf.person_id
and pos.period_of_service_id = paf.period_of_service_id
and pos.business_group_id = ppa.business_group_id
and ppa.payroll_action_id = pact_id
and ppa.business_group_id = paf.business_group_id
order by 1, 2;
select pay_assignment_actions_s.nextval
into ln_lockingactid
from dual;
select pay_assignment_actions_s.nextval
into ln_lockingactid
from dual;
select to_number(parameter_value),
sysdate
into l_timeout_sec,
current_time
from pay_action_parameters
where parameter_name = 'PROCESS_TIMEOUT';
select ppa.payroll_action_id ,
ppa.action_sequence,
ppa.effective_date,
ppa.business_group_id
from
pay_payroll_actions ppa_arch,
pay_payroll_actions ppa
where ppa_arch.payroll_action_id = p_pact_id
and ppa_arch.business_group_id = ppa.business_group_id
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
order by 2;
l_delete_bals boolean;
select count(*)
into act_com
from pay_assignment_actions
where payroll_action_id = pactid
and action_status <> 'C';
select pay_core_utils.get_parameter('REMOVE_ACT',
pa1.legislative_parameters)
into remove_act
from pay_payroll_actions pa1
where pa1.payroll_action_id = pactid;
delete from pay_balance_attributes
where attribute_id in (select attribute_id
from pay_bal_attribute_definitions
where attribute_name = g_att_name
);
delete from pay_bal_attribute_definitions
where attribute_name = g_att_name;