[Home] [Help]
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 pbd.dimension_type = 'R'
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 business_group_id, request_id
into l_bus_grp, l_request_id
from pay_payroll_actions
where payroll_action_id = p_pactid;
update pay_balance_validation bv
set RUN_BALANCE_STATUS = 'I'
where RUN_BALANCE_STATUS <> 'I'
and bv.business_group_id = l_bus_grp
and not exists ( select 'run balance candidate' from pay_balance_dimensions bd,
pay_defined_balances db
where db.defined_balance_id = bv.defined_balance_id
and bd.balance_dimension_id = db.balance_dimension_id
and bd.dimension_type = 'R'
and bd.DIMENSION_LEVEL in ('ASG','GRP'));
select fcp.CONCURRENT_PROGRAM_NAME
into g_program_name
from FND_CONCURRENT_REQUESTS fcr,
FND_CONCURRENT_PROGRAMS fcp
where fcr.REQUEST_ID = l_request_id
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID;
select defined_balance_id,
balance_load_date,
run_balance_status,
case when run_balance_status = 'V' and balance_load_date <= g_start_date then g_start_date else hr_api.g_eot end del_bal_before,
case when run_balance_status = 'V' and balance_load_date <= g_start_date then 'Y' else 'N' end purge_only
bulk collect into g_old_balance_validation_tab
from pay_balance_validation
where business_group_id = l_bus_grp;
select nvl(pbv.run_balance_status, 'I'), nvl(pbv.balance_load_date,hr_api.g_eot)
into l_run_bal_stat, l_old_balance_load_date
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 nvl(pbv.run_balance_status, 'I'), nvl(pbv.balance_load_date,hr_api.g_eot)
into l_run_bal_stat, l_old_balance_load_date
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_payroll_actions set legislative_parameters = legislative_parameters || ' PURGE_ONLY=Y'
where payroll_action_id = p_pactid
and legislative_parameters not like '%PURGE_ONLY%';
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,
p_pact_id generate_pact
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,
g_generate_pact
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
--
if l_rb_row_status in ('I','U','O') 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;
if not(l_delete_bals) then
--
--
hr_utility.set_location(
'pay_run_balance_build.calculate_full_asg_balances',40);
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
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_types_f prt
where paa.assignment_id = p_assid
and paa.assignment_action_id in
( select assignment_action_id from pay_assignment_actions where assignment_id = p_assid
minus select source_action_id from pay_assignment_actions where 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.effective_date >= nvl(p_bal_load_date, ppa.effective_date)
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;
select pba.defined_balance_id
bulk collect
INTO g_tab_def_bal
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
from pay_run_balances prb
where prb.assignment_id = p_asg_id
and prb.defined_balance_id = g_tab_def_bal(a);
delete from pay_run_balances
where defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id
and effective_date < g_old_balance_validation_tab(old_bv).del_bal_before
and assignment_id = p_asg_id;
hr_utility.trace('process_asg_lvl_balances. Delete rbs by load date. p_asg_id:'||p_asg_id||
' cnt:'||sql%rowcount);
if not g_purge_only then --{ not purge only so rb delete not by load date
delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
from pay_run_balances prb
where prb.assignment_id = p_asg_id;
hr_utility.trace('process_asg_lvl_balances. Delete rbs by asg . p_asg_id:'||p_asg_id||
' cnt:'||sql%rowcount);
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;
delete from pay_run_balances where assignment_id = p_asg_id and
effective_date < l_bal_load_date
and defined_balance_id = g_def_bal_id;
hr_utility.trace('pay_run_balance_build.process_asg_lvl_balance delete rb rows before load date:' ||SQL%ROWCOUNT);
lt_tab_getaa.delete;
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;
hr_utility.trace('calculate_full_grp_balances1. Delete by pact . p_pactid:'||p_pactid|| ' cnt:'||sql%rowcount);
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);
hr_utility.trace('calculate_full_grp_balances2. Delete by pact . p_pactid:'||p_pactid|| ' cnt:'||sql%rowcount);
delete from pay_run_balances
where payroll_action_id = p_pactid;
hr_utility.trace('calculate_full_grp_balances3. Delete by pact . p_pactid:'||p_pactid|| ' cnt:'||sql%rowcount);
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);
hr_utility.trace('process_group_lvl_balances1. Delete rbs by rbstatus . p_pactid:'||p_pactid||
' cnt:'||sql%rowcount);
delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */ from pay_run_balances prb
where prb.defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id
and prb.effective_date < g_old_balance_validation_tab(old_bv).del_bal_before
and prb.payroll_action_id = p_pactid;
hr_utility.trace('process_group_lvl_balances. Delete rbs by load date. p_pactid:'||p_pactid||
' cnt:'||l_del_cnt);
delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
from pay_run_balances prb
where prb.payroll_action_id = p_pactid;
hr_utility.trace('process_group_lvl_balances3. Delete rbs by pact . p_pactid:'||p_pactid||
' cnt:'||sql%rowcount);
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')
)
)
);
hr_utility.trace('process_group_lvl_balances4. Delete rbs date . p_pactid:'||p_pactid||
' cnt:'||sql%rowcount);
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,
paa.chunk_number,
ppa.business_group_id
into l_payroll_action_id, l_object_id, l_asg_id, l_chunk_number, 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 pbd.dimension_type = 'R'
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 pbd.dimension_type = 'R'
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 distinct
paf.assignment_id assignment_id,
paf.person_id
from
per_periods_of_service pos,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_population_ranges ppr
where ppr.chunk_number = c_chunk
and ppr.payroll_action_id = ppa.payroll_action_id
and pos.person_id = ppr.person_id
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 = p_payroll_action_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;
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number )
select
pay_assignment_actions_s.nextval assignment_action_id,
asg.assignment_id assignment_id,
p_payroll_action_id payroll_action_id,
'U' action_status,
p_chunk chunk_number,
pay_assignment_actions_s.currval action_sequence,
1 object_version_number
from
( select distinct paf.assignment_id from
per_periods_of_service pos,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_population_ranges ppr
where ppr.chunk_number = p_chunk
and ppr.payroll_action_id = ppa.payroll_action_id
and pos.person_id = ppr.person_id
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 = p_payroll_action_id
and ppa.business_group_id = paf.business_group_id
order by 1 ) asg ;
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number )
select
pay_assignment_actions_s.nextval assignment_action_id,
asg.assignment_id assignment_id,
p_payroll_action_id payroll_action_id,
'U' action_status,
p_chunk chunk_number,
pay_assignment_actions_s.currval action_sequence,
1 object_version_number
from
( select distinct paf.assignment_id from
per_periods_of_service pos,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_population_ranges ppr
where pos.person_id between p_start_person_id and p_end_person_id
and ppr.payroll_action_id = ppa.payroll_action_id
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 = p_payroll_action_id
and ppa.business_group_id = paf.business_group_id
order by 1 ) asg ;
select to_number(parameter_value),
sysdate
into l_timeout_sec,
current_time
from pay_action_parameters
where parameter_name = 'PROCESS_TIMEOUT';
select business_group_id, request_id,
nvl(pay_core_utils.get_parameter('PURGE_ONLY', legislative_parameters), 'N') parm_purge_only
into l_bus_grp, l_request_id, l_parm_purge_only
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select fcp.CONCURRENT_PROGRAM_NAME
into g_program_name
from FND_CONCURRENT_REQUESTS fcr,
FND_CONCURRENT_PROGRAMS fcp
where fcr.REQUEST_ID = l_request_id
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID;
select defined_balance_id,
balance_load_date,
run_balance_status,
case when balance_status is null then g_start_date else hr_api.g_eot end del_bal_before,
case when balance_status is null then 'Y' else 'N' end purge_only
bulk collect into g_old_balance_validation_tab
from ( select bv.defined_balance_id,
bv.balance_load_date, run_balance_status, bv.business_group_id,
(select 'P' from pay_bal_attribute_definitions bad,
pay_balance_attributes ba
where bad.attribute_name = g_att_name
and bad.ATTRIBUTE_ID = ba.ATTRIBUTE_ID
and ba.defined_balance_id = bv.defined_balance_id) balance_status
from pay_balance_validation bv
where bv.business_group_id = l_bus_grp) bv2;
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;