[Home] [Help]
120: hr_utility.trace('l_bg_id='||l_bg_id);
121:
122: sqlstr := 'select /*+ ORDERED
123: index(ppa PAY_PAYROLL_ACTIONS_N5)
124: index(paa PAY_ASSIGNMENT_ACTIONS_N50)
125: index(paf per_assignments_pk) */
126: distinct paf.person_id
127: from
128: pay_payroll_actions ppa,
125: index(paf per_assignments_pk) */
126: distinct paf.person_id
127: from
128: pay_payroll_actions ppa,
129: pay_assignment_actions paa,
130: per_assignments_f paf
131: where :payroll_action_id is not null
132: and paa.payroll_action_id = ppa.payroll_action_id
133: and paa.action_status=''C''
154: leg_param pay_payroll_actions.legislative_parameters%type;
155: action_type varchar2(30);
156: l_as_of_date varchar2(240);
157: -- l_date_prm varchar2(50);
158: l_gre_id pay_assignment_actions.tax_unit_id%type;
159: l_org_id per_assignments_f.organization_id%type;
160: l_loc_id per_assignments_f.location_id%type;
161: l_bg_id per_assignments_f.business_group_id%type;
162: l_tax_type varchar2(100);
172: assignid number;
173: greid number;
174: num number;
175:
176: l_aaid pay_assignment_actions.assignment_action_id%TYPE;
177: l_tu_id pay_assignment_actions.tax_unit_id%TYPE;
178: l_person_id per_people_f.person_id%TYPE;
179:
180: p_over_limit_flag varchar2(1);
173: greid number;
174: num number;
175:
176: l_aaid pay_assignment_actions.assignment_action_id%TYPE;
177: l_tu_id pay_assignment_actions.tax_unit_id%TYPE;
178: l_person_id per_people_f.person_id%TYPE;
179:
180: p_over_limit_flag varchar2(1);
181: sac_temp number;
193: from pay_payroll_actions ppa
194: where ppa.payroll_action_id = pactid;
195:
196: l_prev_person_id per_people_f.person_id%type;
197: l_prev_tu_id pay_assignment_actions.tax_unit_id%type;
198:
199: --
200: -- #2453584: split cursors
201: -- The report is only getting the YTD values so we only need to get
241: ,cp_assignid in number) is -- Bug 5717518
242: select /*+ ORDERED */
243: to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id -- Bug 5717518
244: from per_all_assignments_f paf,
245: pay_assignment_actions paa,
246: pay_payroll_actions ppa,
247: pay_action_classifications pac
248: where paf.assignment_id = cp_assignid -- Bug 5717518: New parameter added for performance improvement
249: and paf.person_id = cp_person_id -- Bug 5717518: Shuffled the Where Clause for performance improvement
347: lv_sqlstr := 'select distinct paf.person_id person_id,
348: paf.assignment_id,
349: paa.tax_unit_id
350: from per_assignments_f paf,
351: pay_assignment_actions paa,
352: pay_payroll_actions ppa,
353: PAY_POPULATION_RANGES ppr
354: where ppr.payroll_action_id = '|| pactid ||'
355: and ppr.chunk_number = '|| chunk ||'
388:
389: lv_sqlstr := 'select
390: /*+ ORDERED
391: index(ppa PAY_PAYROLL_ACTIONS_PK)
392: index(paa PAY_ASSIGNMENT_ACTIONS_N51)
393: index(paf PER_ASSIGNMENTS_N12) */
394: distinct paf.person_id person_id,
395: paf.assignment_id,
396: paa.tax_unit_id
394: distinct paf.person_id person_id,
395: paf.assignment_id,
396: paa.tax_unit_id
397: from per_assignments_f paf,
398: pay_assignment_actions paa,
399: pay_payroll_actions ppa
400: where paf.person_id between '|| stperson ||' and '|| endperson ||'
401: and paf.assignment_type = ''E''
402: and paa.assignment_id = paf.assignment_id
482:
483: sqlstr :=
484: 'SELECT paa.rowid
485: FROM pay_payroll_actions ppa,
486: pay_assignment_actions paa,
487: per_all_assignments_f paf,
488: per_all_people_f ppf,
489: hr_organization_units hou,
490: hr_locations_all hl