[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update serial numner with whether it is
master or child (sep check) action.
20-MAR-2002 tclewis 115.10 Added code to the action creation to
handle multi-assignment processing.
13-JUN-2002 tclewis 115.14 Modified the actions_creation cursro
non multiple assigmnet payroll register
to set a temporary flab l_action_insert
:= 'Y' as to not insert an extra record
when the payment cursor returns no data.
13-JUN-2002 tclewis 115.15 fixed a bug where we are not exiting the
c_payments loop correctly.
07-AUG-2002 rmonge 115.16 Increase size of action_type to varchar2(30)
21-oct-2002 tclewis 115.17 removed the "for Update... " in the action_creation
code. Changed the "for update" clause
in the sort_cursor to paa.assignment_id from
paf.assignment_id
19-DEC-2002 tclewis 115.18 added nocopy.
27-DEC-2002 meshah 115.19 fixed gscc warning.
17-SEP-2003 ardsouza 115.20 modified sort_action procedure to sort based on
date paid of 'P','U'& 'V' process(Bug 2641972).
26-jan-2004 djoshi 115.22 modified action_creation for bug 3385676
We will insert multiple rows for when
pre-payment is locking multiple rows.
27-jan-2004 djoshi 115.23 Corrected missing exit statement
29-jan-2004 djoshi 115.24 the action creation cursor has been
changed to make sure we have
missing assignment actions
also Created
05-feb-2004 ssmukher 115.25 Bug 3372747: 11.5.10 Performance Changes
09-Feb-2004 ssmukher 115.26 Bug 3372747 - Corrected dec for
leg_param.
16-Feb-2004 djoshi 115.27 Bug 3423464. Regular Not showing up
15-Mar-2005 schauhan 115.37 Added Logic for showing Balance Adjustments on report.
Bug 4074976.
09-May-2006 ppanda 115.38 Bug # 5204333 Fixed
lv_max_run_flag which was used in action_creation
procedure was not re-initialized after processing
the Actions for Balance Adjustments.
This variable is initialized with default value N
after processing actions in the loop
20-Sep-2006 sjawid 115.39 Bug 5366862 fixed
i.added date effective join to c_payroll_def.
ii.changed the c_payroll_def Open statement to
use the EFFECTIVE_DATE from the
PRE_PAYMENTS PAYROLL ACTION,
not the effective date from the
payroll register payroll action.
28-jun-2007 vmkulkar 115.40 Created a new cursor c_actions_1
Bug 5502369
*/
--------------------------- range_cursor ---------------------------------
PROCEDURE range_cursor (pactid in number,
sqlstr out nocopy varchar2) is
leg_param pay_payroll_actions.legislative_parameters%type;
select legislative_parameters
into leg_param
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select ppa.legislative_parameters,
pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('L_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('P_ID', ppa.legislative_parameters),
ppa.start_date,
ppa.effective_date,
ppa.business_group_id
into leg_param,
l_consolidation_set_id,
l_payroll_id,
l_organization_id,
l_location_id,
l_person_id,
l_leg_start_date,
l_leg_end_date,
l_business_group_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
l_payroll_text := 'and pa1.payroll_id in (select payroll_id from pay_payrolls_f)';
'select distinct asg.person_id
from pay_payroll_actions ppa,
pay_payroll_actions pa1,
pay_assignment_actions act,
per_assignments_f asg,
pay_payrolls_f ppf
where ppa.payroll_action_id = :payroll_action_id
'||l_consolidation_set_text||'
'||l_payroll_text||'
and pa1.effective_date between ppa.start_date
and ppa.effective_date
and pa1.effective_date between asg.effective_start_date
and asg.effective_end_date
and pa1.action_type in (''P'',''U'',''V'')
and pa1.payroll_action_id = act.payroll_action_id
and asg.assignment_id = act.assignment_id
and act.action_status = ''C''
and asg.organization_id = nvl('''||l_organization_id||''',
asg.organization_id)
and asg.location_id = nvl('''||l_location_id||''',
asg.location_id)
and asg.person_id = nvl('''||l_person_id||''',
asg.person_id)
and asg.business_group_id +0 = ppa.business_group_id
and asg.payroll_id = ppf.payroll_id
and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.payroll_id >=0
order by asg.person_id';
select pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id,
pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id,
pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
pay_payrg_pkg.get_parameter('L_ID',ppa.legislative_parameters) location_id,
pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
pay_payrg_pkg.get_parameter('P_ID',ppa.legislative_parameters) person_id,
pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
pay_payrg_pkg.get_parameter('PASID',ppa.legislative_parameters) assignment_set_id,
ppa.start_date start_date,
ppa.effective_date effective_date
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select /*+ ORDERED */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa.action_type,
ppa.effective_date,
act.source_action_id,
nvl(ppa.start_date,ppa.effective_date)
from pay_payrolls_f ppf, -- Bug 3372747
pay_payroll_actions ppa, /* pre-payments and reversals
payroll action id */
pay_assignment_actions act,
per_assignments_f paf
where (c_payroll_id is NULL
or ppa.payroll_id = c_payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date >= c_start_date
and nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
-- decode (ppa.action_type,'P', add_months(c_effective_date,12),
-- 'U', add_months(c_effective_date,12),
-- 'V', c_effective_date)
-- c_effective_date
and ppa.action_type in ('P','U','V')
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id +0 = c_business_group_id
and paf.assignment_id = act.assignment_id
and (c_tax_unit_id is NULL
or act.tax_unit_id = c_tax_unit_id)
and (c_organization_id is NULL
or paf.organization_id = c_organization_id)
and (c_location_id is NULL
or paf.location_id = c_location_id)
and (c_person_id is NULL
or paf.person_id = c_person_id)
and paf.person_id between c_stperson and c_endperson
and paf.business_group_id +0 = c_business_group_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.payroll_id = ppf.payroll_id -- Bug 3372747
and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.payroll_id >= 0
order by act.assignment_id;
select /*+ ORDERED */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa.action_type,
ppa.effective_date,
act.source_action_id,
nvl(ppa.start_date,ppa.effective_date)
from
HR_ASSIGNMENT_SET_AMENDMENTS HASA ,
PER_ASSIGNMENTS_F PAF ,
PAY_ASSIGNMENT_ACTIONS ACT ,
PAY_PAYROLL_ACTIONS PPA ,
PAY_PAYROLLS_F PPF
where (c_payroll_id is NULL
or ppa.payroll_id = c_payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date >= c_start_date
and nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
-- decode (ppa.action_type,'P', add_months(c_effective_date,12),
-- 'U', add_months(c_effective_date,12),
-- 'V', c_effective_date)
-- c_effective_date
and ppa.action_type in ('P','U','V')
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id +0 = c_business_group_id
and paf.assignment_id = act.assignment_id
and (c_tax_unit_id is NULL
or act.tax_unit_id = c_tax_unit_id)
and (c_organization_id is NULL
or paf.organization_id = c_organization_id)
and (c_location_id is NULL
or paf.location_id = c_location_id)
and (c_person_id is NULL
or paf.person_id = c_person_id)
and hasa.assignment_set_id = c_assignment_set_id
and hasa.assignment_id = paf.assignment_id
and paf.person_id between c_stperson and c_endperson
and paf.business_group_id +0 = c_business_group_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.payroll_id = ppf.payroll_id -- Bug 3372747
and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.payroll_id >= 0
order by act.assignment_id;
select distinct ppp.source_action_id
from pay_pre_payments ppp
where ppp.assignment_action_id = cp_pre_pymt_action_id
order by ppp.source_action_id;
select distinct nvl(ppp.source_action_id,-999)
from pay_payment_information_v ppp
where ppp.assignment_action_id = cp_pre_pymt_action_id
and ppp.action_status = 'C'
order by 1;
select ppa.effective_date,
ppa.action_type
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_pre_pymt_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B');
select assignment_action_id, ppa.action_type
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_pre_pymt_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ((paa.run_type_id is null and paa.source_action_id is null) or
(paa.run_type_id is not null and paa.source_action_id is not null
and paa.run_type_id in
(select prt.run_type_id
from pay_run_types_f prt
where prt.shortname <> 'SEPCHECK'
and prt.legislation_code = 'US'
and ppa.effective_date between prt.effective_start_date
and prt.effective_end_date)
)
)
order by assignment_action_id desc;
select assignment_action_id, ppa.action_type
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_pre_pymt_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ((paa.run_type_id is null and paa.source_action_id is null) or
(paa.run_type_id is not null and paa.source_action_id is not null
and paa.run_type_id in
(select prt.run_type_id
from pay_run_types_f prt
where prt.shortname <> 'SEPCHECK'
and prt.legislation_code = 'US'
and ppa.effective_date between prt.effective_start_date
and prt.effective_end_date)
)
)
and ppa.action_type = 'B'
order by assignment_action_id desc;
select multi_assignments_flag
from pay_payrolls_f ppf,
per_assignments_f paf
where paf.payroll_id = ppf.payroll_id
and cp_effective_date between paf.effective_start_date
and paf.effective_end_date
and cp_effective_date between ppf.effective_start_date --bug5366862
and ppf.effective_end_date
and paf.assignment_id = cp_assignment_id;
select max(paa2.assignment_action_id)
from pay_assignment_actions paa2, -- assignment_actions for slave payroll runs.
pay_assignment_actions paa1, -- assignment_action for master payroll run
pay_run_Types_f prt,
pay_payroll_actions ppa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pre_pymt_action_id
and pai.locked_action_id = paa1.assignment_action_id
and paa1.source_action_id is null -- master assignment_action
and paa1.assignment_action_id = paa2.source_action_id
and paa1.payroll_action_id = paa2.payroll_action_id
and paa2.run_type_id = prt.run_type_id
and prt.shortname <> 'SEPCHECK'
and prt.legislation_code = 'US'
and paa2.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between prt.effective_start_date
and prt.effective_end_date;
select distinct max(paa2.assignment_action_id)
from pay_assignment_actions paa2,
-- assignment_actions for slave payroll runs.
pay_assignment_actions paa1,
-- assignment_action for master payroll run
pay_run_Types_f prt,
pay_payroll_actions ppa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pre_pymt_action_id
and pai.locked_action_id = paa1.assignment_action_id
and paa1.source_action_id is null -- master assignment_action
and paa1.assignment_action_id = paa2.source_action_id
and paa1.payroll_action_id = paa2.payroll_action_id
and paa2.run_type_id = prt.run_type_id
and prt.shortname <> 'SEPCHECK'
and prt.legislation_code = 'US'
and paa2.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between prt.effective_start_date
and prt.effective_end_date
group by paa1.assignment_action_id;
select 'Y'
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_pre_pymt_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and action_type = 'V';
l_action_insert varchar2(1);
select legislative_parameters
into l_leg_param
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
l_action_insert := 'N';
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
runactid);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
l_asg_act_id);
serialno := nvl(run_action_type,'R') || -- Serial number updated for MAX BA only.
lv_sep_check ||
lv_multi_asg_flag ||
to_char(l_asg_act_id);
-- update pay_assignment_actions serial_number with runactid.
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
l_asg_act_id);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
lockedactid);
if l_action_insert = 'N' then
-- We need to make sure that the pre_pay assignment
-- action is not locking a void action as the void)
-- is handled else where
l_void_action := 'N';
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
- insert multiple rows in pay_us_rpt_totals
fetch c_payroll_run into ln_master_action_id,lv_run_action_type;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
runactid);
else -- l_action_insert = 'N'
exit;
end if; -- l_action_insert = 'N'
** we need to insert one action for each of the rows that we
** return from the cursor (i.e. one for each assignment/pre-payment source).
**************************************************************/
if (ln_prev_source_action_id is null or
ln_source_action_id <> ln_prev_source_action_id or
ln_source_action_id = -999) then
-- insert the action record.
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
l_action_insert := 'Y';
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
runactid);
if lv_run_action_type in ('R','Q') then -- makes sure that run is inserted just once.
lv_max_run_flag := 'Y';
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
runactid);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
lockedactid);
select pay_payrg_pkg.get_parameter('P_S1', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('P_S2', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('P_S3', ppa.legislative_parameters)
into l_sort_1,
l_sort_2,
l_sort_3
from pay_payroll_actions ppa
where ppa.payroll_action_id = payactid;
'select paa.rowid
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payactid
and paa.payroll_action_id = ppa.payroll_action_id
order by
(decode('''||l_sort_1||''',
null, null,
pay_payrg_pkg.sort_option (
'''||l_sort_1||''',
paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id))),
(decode('''||l_sort_2||''',
null, null,
pay_payrg_pkg.sort_option (
'''||l_sort_2||''',
paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id))),
(decode('''||l_sort_3||''',
null, null,
pay_payrg_pkg.sort_option (
'''||l_sort_3||''',
paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id))),
(select hou.name
from hr_all_organization_units hou, /* Assignment Org */
per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and ppa.effective_date between
paf.effective_start_date and paf.effective_end_date
and hou.organization_id = paf.organization_id
and rownum = 1),
(select distinct ppf.full_name
from per_all_people_f ppf,
per_all_assignments_f paf
where paf.assignment_id = paa.assignment_id
and ppf.person_id = paf.person_id
and ppa.effective_date between
paf.effective_start_date and paf.effective_end_date
and ppa.effective_date between
ppf.effective_start_date and ppf.effective_end_date
),
(select ppa2.effective_date
from pay_payroll_actions ppa2,
pay_assignment_actions paa2
where paa2.assignment_action_id = to_number(substr(paa.serial_number,4))
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in (''R'', ''Q'', ''V'', ''B'')
)
for update of paa.assignment_id';
select hou1.name
into return_val
from hr_all_organization_units hou1 /* Tax Unit */
where hou1.organization_id = c_tax_unit_id
and rownum = 1;
select decode(c_option_name,
'Organization',hou.name,
'Location',loc.location_code,
null)
into return_val
from hr_all_organization_units hou, /* Assignment Org */
hr_locations_all loc,
per_assignments_f paf
where paf.assignment_id = c_assignment_id
and c_effective_date between
paf.effective_start_date and paf.effective_end_date
and hou.organization_id = paf.organization_id
and loc.location_id = paf.location_id
and rownum = 1;