[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
23-JUN-2003 kaverma 115.13 3018606 Modified insert_action to call load_data
only if assignment_action_id is not null
19-DEC-2003 kaverma 115.14 3326648 disabled index on ppa.effective date in
cursor c_get_latest_asg
15-JAN-2004 ardsouza 115.15 3361891 Modified 4 cursors to improve performance.
14-MAR-2005 sackumar 115.16 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
07-DEC-2005 sackumar 115.17 4748245 Changed the Range Cursor and Action_creation procedure
to improve the performance.
Also replaced the pay_us_over_limit_pkg.get_parameter
call to pay_us_payroll_utils.get_parameter call.
18-APR-2007 sudedas 115.18 5840569 In case Range Person ID Functionality
is disabled where conditions need to
be added to action_creation procedure.
29-OCT-2007 vaisriva 115.19 5717518 Cursor c_get_latest_asg has been modified to improve
it's performance
--
*/
-------------------- range_cursor ---------------------------------------------
--
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
--
leg_param pay_payroll_actions.legislative_parameters%type;
select ppa.legislative_parameters,
ppa.business_group_id,
pay_us_payroll_utils.get_parameter('GRE',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('ORG',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('LOC',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('AS_OF_DATE',ppa.legislative_parameters)
into leg_param,
l_bg_id,
l_gre_id,
l_org_id,
l_loc_id,
l_as_of_date
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := 'select /*+ ORDERED
index(ppa PAY_PAYROLL_ACTIONS_N5)
index(paa PAY_ASSIGNMENT_ACTIONS_N50)
index(paf per_assignments_pk) */
distinct paf.person_id
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where :payroll_action_id is not null
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status=''C''
and ppa.action_type in (''B'', ''I'', ''R'', ''Q'', ''V'')
and ppa.action_status = ''C''
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between trunc(to_date('''||l_as_of_date||''',''YYYY/MM/DD''), ''Y'')
and to_date('''||l_as_of_date||''',''YYYY/MM/DD'')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paf.business_group_id + 0 = '''||l_bg_id||'''
'|| where_condition ||'
order by paf.person_id';
select
ppa.legislative_parameters,
ppa.business_group_id,
pay_us_payroll_utils.get_parameter('GRE',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('ORG',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('LOC',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('AS_OF_DATE',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('TAX_TYPE',ppa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
procedure insert_action(pactid IN number
,chunk IN number
,p_greid IN number
,p_person_id IN per_all_people_f.person_id%type
,p_assignid IN number
) is
-- Cursor to get the assignment actions
-- Bug 5717518: Cursor c_get_latest_asg has been modified to improve it's performance
cursor c_get_latest_asg(
cp_person_id in number
,cp_tax_unit_id in number
,cp_as_of_date in varchar2
,cp_assignid in number) is -- Bug 5717518
select /*+ ORDERED */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id -- Bug 5717518
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.assignment_id = cp_assignid -- Bug 5717518: New parameter added for performance improvement
and paf.person_id = cp_person_id -- Bug 5717518: Shuffled the Where Clause for performance improvement
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and ppa.effective_date between trunc(to_date(cp_as_of_date,'YYYY/MM/DD'), 'Y') -- Bug 3326648
and to_date(cp_as_of_date,'YYYY/MM/DD')
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED';
end insert_action;
lv_sqlstr := 'select distinct paf.person_id person_id,
paf.assignment_id,
paa.tax_unit_id
from per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
PAY_POPULATION_RANGES ppr
where ppr.payroll_action_id = '|| pactid ||'
and ppr.chunk_number = '|| chunk ||'
and paf.person_id = ppr.person_id
and paf.assignment_type = ''E''
and paa.assignment_id = paf.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paf.payroll_id = ppa.payroll_id
and paf.payroll_id is not null
and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
and paa.action_status = ''C''
and ppa.business_group_id = '||l_bg_id ||'
and paf.business_group_id = ppa.business_group_id
and ppa.effective_date between trunc(to_date('''|| l_as_of_date ||''',''yyyy/mm/dd''), ''Y'')
and to_date('''|| l_as_of_date || ''',''yyyy/mm/dd'')
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
order by 1, 3';
lv_sqlstr := 'select
/*+ ORDERED
index(ppa PAY_PAYROLL_ACTIONS_PK)
index(paa PAY_ASSIGNMENT_ACTIONS_N51)
index(paf PER_ASSIGNMENTS_N12) */
distinct paf.person_id person_id,
paf.assignment_id,
paa.tax_unit_id
from per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paf.person_id between '|| stperson ||' and '|| endperson ||'
and paf.assignment_type = ''E''
and paa.assignment_id = paf.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paf.payroll_id = ppa.payroll_id
and paf.payroll_id is not null
and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
and paa.action_status = ''C''
and ppa.business_group_id = '||l_bg_id ||'
and paf.business_group_id = ppa.business_group_id
and ppa.effective_date between trunc(to_date('''|| l_as_of_date ||''',''yyyy/mm/dd''), ''Y'')
and to_date('''|| l_as_of_date || ''',''yyyy/mm/dd'')
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date '||
lv_where_condition ||
' order by 1, 3';
hr_utility.set_location('Insert action',320);
insert_action(pactid => pactid,
chunk => chunk,
p_greid => ln_greid,
p_person_id => ln_personid,
p_assignid => ln_assgid
);
select
ppa.legislative_parameters,
pay_us_payroll_utils.get_parameter('SORT1',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('SORT2',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('SORT3',ppa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
'SELECT paa.rowid
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f ppf,
hr_organization_units hou,
hr_locations_all hl
WHERE ppa.payroll_action_id = :pactid
AND paa.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ppa.effective_date)
and paf.assignment_type = ''E''
and ppf.person_id = paf.person_id
and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and hou.organization_id = nvl(paf.organization_id,paf.business_group_id)
and hl.location_id = NVL(paf.location_id,hou.location_id)
ORDER BY
decode('''||l_sort1||''',
''Employee_Name'',rpad(ppf.last_name||'' ''||ppf.first_name||'' ''||ppf.middle_names, 63),
''Social_Security_Number'',rpad(ppf.national_identifier, 63),''Organization'', rpad(hou.name, 63),
''Location'',rpad(hl.location_code, 63)),
decode('''||l_sort2||''',
''Employee_Name'',rpad(ppf.last_name||'' ''||ppf.first_name||'' ''||ppf.middle_names, 63),
''Social_Security_Number'',rpad(ppf.national_identifier, 63),''Organization'', rpad(hou.name, 63),
''Location'',rpad(hl.location_code, 63)),
decode('''||l_sort3||''',
''Employee_Name'',rpad(ppf.last_name||'' ''||ppf.first_name||'' ''||ppf.middle_names, 63),
''Social_Security_Number'',rpad(ppf.national_identifier, 63),''Organization'', rpad(hou.name, 63),
''Location'',rpad(hl.location_code, 63))
';