1 package body pay_us_over_limit_pkg as
2 /* $Header: pyusoltm.pkb 120.3.12010000.1 2008/07/27 23:54:29 appldev ship $ */
3 /*
4 Copyright (c) Oracle Corporation 2001. All rights reserved
5 --
6 Name :This package defines the cursors needed for OLT to run Multi-Threaded
7 --
8 Change List
9 -----------
10 Date Name Vers Bug No Description
11 ----------- ---------- ----- ------- -----------------------------------
12 01-DEC-2001 irgonzal 115.0 2045352 Created.
13 08-DEC-2001 irgonzal 115.1 Simplied the sort_action cursor
14 since the sorting is performed in
15 the second stage of the process.
16 Removed reference to pactid and
17 per_people_f in Action_Creation
18 cursor.
19 11-DEC-2001 irgonzal 115.2 Corrected typo in sort_action
20 procedure and removed reference
21 to per_people_f table in sort_
22 action procedure.
23 20-DEC-2001 meshah 115.3 2157065 changed hr_locations to
24 hr_locations_all in c_actions
25 cursor.
26 04-FEB-2001 meshah 115.4 2166701 Changed the action creation cursor.
27 Also changed the names of the
28 dummy parameters from
29 legislative_parameters.
30 05-FEB-2001 meshah 115.5 Added checkfile entry to the file.
31 19-MAR-2001 meshah 115.6 2262842 Added business_group_id checking
32 in the range and actio_creation
33 cursor.
34 2261018 Changed the date checking on the
35 per_assignments_f table in the
36 action_creation cursor.
37 06-AUG-2002 rmonge 115.7 2447123 Changed action_type to varchar2(30)
38 12-SEP-2002 irgonzal 115.8 2453584 Split action creation cursor in
39 several cursors to avoid reading
40 same objects twice.
41 13-NOV-2002 irgonzal 115.9 2453584 Changed action creation cursors:
42 modified condition that checks
43 ppa.effective_date.
44 18-MAY-2003 vgunasek 115.10 2938556 report rewrite including support for
45 new balance reporting architecture (run
46 balances) and multi threading.
47 06-JUN-2003 vgunasek 115.11 2938556 Changed sort action code
48 18-JUN-2003 kaverma 115.12 3015312 Modified action_creation code and broke
49 cursor all for performance improvement.
50 23-JUN-2003 kaverma 115.13 3018606 Modified insert_action to call load_data
51 only if assignment_action_id is not null
52 19-DEC-2003 kaverma 115.14 3326648 disabled index on ppa.effective date in
53 cursor c_get_latest_asg
54 15-JAN-2004 ardsouza 115.15 3361891 Modified 4 cursors to improve performance.
55 14-MAR-2005 sackumar 115.16 4222032 Change in the Range Cursor removing redundant
56 use of bind Variable (:payroll_action_id)
57 07-DEC-2005 sackumar 115.17 4748245 Changed the Range Cursor and Action_creation procedure
58 to improve the performance.
59 Also replaced the pay_us_over_limit_pkg.get_parameter
60 call to pay_us_payroll_utils.get_parameter call.
61 18-APR-2007 sudedas 115.18 5840569 In case Range Person ID Functionality
62 is disabled where conditions need to
63 be added to action_creation procedure.
64 29-OCT-2007 vaisriva 115.19 5717518 Cursor c_get_latest_asg has been modified to improve
65 it's performance
66 --
67 */
68 -------------------- range_cursor ---------------------------------------------
69 --
70 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
71 --
72 leg_param pay_payroll_actions.legislative_parameters%type;
73 l_gre_id number;
74 l_loc_id number;
75 l_org_id number;
76 l_as_of_date varchar2(240);
77 l_bg_id pay_payroll_actions.business_group_id%type;
78 where_condition varchar(5000);
79
80 --
81 --
82 begin
83 -- hr_utility.trace_on(null,'oracle');
84 hr_utility.set_location('IN range_cursor',200);
85
86 begin
87 select ppa.legislative_parameters,
88 ppa.business_group_id,
89 pay_us_payroll_utils.get_parameter('GRE',ppa.legislative_parameters),
90 pay_us_payroll_utils.get_parameter('ORG',ppa.legislative_parameters),
91 pay_us_payroll_utils.get_parameter('LOC',ppa.legislative_parameters),
92 pay_us_payroll_utils.get_parameter('AS_OF_DATE',ppa.legislative_parameters)
93 into leg_param,
94 l_bg_id,
95 l_gre_id,
96 l_org_id,
97 l_loc_id,
98 l_as_of_date
99 from pay_payroll_actions ppa
100 where ppa.payroll_action_id = pactid;
101 exception
102 when others then
103 hr_utility.trace('Legislative parameters not found for pactid '||to_char(pactid));
104 --raise;
105 end;
106
107 where_condition := '';
108 if l_gre_id is not null then
109 where_condition :=where_condition||' and paa.tax_unit_id ='||l_gre_id;
110 end if;
111 if l_org_id is not null then
112 where_condition :=where_condition||' and paf.organization_id ='||l_org_id;
113 end if;
114 if l_loc_id is not null then
115 where_condition :=where_condition||' and paf.location_id ='||l_loc_id;
116 end if;
117
118 hr_utility.trace('Range where condition='||where_condition);
119 hr_utility.trace('l_as_of_date='||l_as_of_date);
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,
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''
134 and ppa.action_type in (''B'', ''I'', ''R'', ''Q'', ''V'')
135 and ppa.action_status = ''C''
136 and paf.assignment_id = paa.assignment_id
137 and ppa.effective_date between trunc(to_date('''||l_as_of_date||''',''YYYY/MM/DD''), ''Y'')
138 and to_date('''||l_as_of_date||''',''YYYY/MM/DD'')
139 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
140 and paf.business_group_id + 0 = '''||l_bg_id||'''
141 '|| where_condition ||'
142 order by paf.person_id';
143
144 hr_utility.set_location('OUT range_cursor',250);
145 end range_cursor;
146 --
147 --------------------------- action_creation ---------------------------------
148 --
149 procedure action_creation(pactid in number,
150 stperson in number,
151 endperson in number,
152 chunk in number) is
153
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);
163
164 l_per_id per_assignments_f.person_id%type;
165 l_ssn per_people_f.national_identifier%type;
166 l_state_code pay_us_states.state_code%type;
167 l_state_abbrev pay_us_states.state_abbrev%type;
168
169
170 lockingactid number;
171 lockedactid number;
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);
181 sac_temp number;
182 lv_where_condition varchar2(5000) ;
183 --
184 cursor c_parameters ( pactid number) is
185 select
186 ppa.legislative_parameters,
187 ppa.business_group_id,
188 pay_us_payroll_utils.get_parameter('GRE',ppa.legislative_parameters),
189 pay_us_payroll_utils.get_parameter('ORG',ppa.legislative_parameters),
190 pay_us_payroll_utils.get_parameter('LOC',ppa.legislative_parameters),
191 pay_us_payroll_utils.get_parameter('AS_OF_DATE',ppa.legislative_parameters),
192 pay_us_payroll_utils.get_parameter('TAX_TYPE',ppa.legislative_parameters)
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
202 -- the payroll actions which have been submitted in the year for
203 -- which the user is running the report.
204 -- So this condition was modified:
205 -- " ...and ppa.effective_date <= to_date(l_as_of_date,'YYYY/MM/DD') ..."
206 --
207 -- All the four cursors are splitted for performance improvement. (Bug: 3015312)
208 -- Bug 3361891 - All the four cursors are modified to improve performance.
209 -- Bug 4748245 - All the four cursors are removed and introduce a ref cursor
210
211 TYPE overlimit IS REF CURSOR;
212 c_seq_act overlimit;
213 lv_sqlstr varchar2(5000);
214 lv_org_condition varchar2(200);
215 lv_loc_condition varchar2(200);
216 lv_gre_condition varchar2(200);
217
218 ln_greid number;
219 ln_personid number;
220 ln_assgid number;
221
222 -- Bug 4748245
223 --
224 -- #2453584
225 -- Bug# 3015312 - Added cursor to improve the performance of the action_code
226 -- and modified the procedure
227
228 procedure insert_action(pactid IN number
229 ,chunk IN number
230 ,p_greid IN number
231 ,p_person_id IN per_all_people_f.person_id%type
232 ,p_assignid IN number
233 ) is
234
235 -- Cursor to get the assignment actions
236 -- Bug 5717518: Cursor c_get_latest_asg has been modified to improve it's performance
237 cursor c_get_latest_asg(
238 cp_person_id in number
239 ,cp_tax_unit_id in number
240 ,cp_as_of_date in varchar2
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
250 and paa.assignment_id = paf.assignment_id
251 and paa.tax_unit_id = cp_tax_unit_id
252 and paa.payroll_action_id = ppa.payroll_action_id
253 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
254 and paa.source_action_id is null)
255 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
256 and paa.source_action_id is not null )
257 or (ppa.action_type = 'V' and ppa.run_type_id is null
258 and paa.run_type_id is not null
259 and paa.source_action_id is null))
260 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
261 and ppa.effective_date between trunc(to_date(cp_as_of_date,'YYYY/MM/DD'), 'Y') -- Bug 3326648
262 and to_date(cp_as_of_date,'YYYY/MM/DD')
263 and ppa.action_type = pac.action_type
264 and pac.classification_name = 'SEQUENCED';
265 -- End of Bug 5717518
266
267 l_max_asg_action_id number;
268
269 begin
270 if ((l_prev_person_id = p_person_id) AND
271 (l_prev_tu_id = p_greid)) then
272 null;
273 else
274 l_prev_person_id := p_person_id;
275 l_prev_tu_id := p_greid;
276
277 num := 0;
278 num := num + 1;
279 --
280 -- Added the call as part of the bug # 2938556
281 -- moved insertion of assignment action to this package.
282 -- Bug 5717518: Cursor c_get_latest_asg has been modified to improve it's performance
283 open c_get_latest_asg(p_person_id,p_greid,l_as_of_date,p_assignid);
284 fetch c_get_latest_asg into l_max_asg_action_id;
285 close c_get_latest_asg;
286 if l_max_asg_action_id is not null then --Bug3018606
287 pay_us_over_limit_tax_rpt_pkg.load_data
288 (pactid,
289 chunk,
290 p_assignid,
291 l_max_asg_action_id,--p_lockedactid (Bug3015312 )
292 p_greid
293 );
294 end if;
295 end if;
296 hr_utility.trace(' Actions found = '||to_char(num));
297 end insert_action;
298 --
299 --
300 --------------- Main Action Creation --------------------------------
301 begin
302 --hr_utility.trace_on(null,'oracle');
303
304 hr_utility.set_location('IN action_creation',300);
305 --
306 open c_parameters(pactid);
307 fetch c_parameters into leg_param,
308 l_bg_id,
309 l_gre_id,
310 l_org_id,
311 l_loc_id,
312 l_as_of_date,
313 l_tax_type;
314
315 if c_parameters%notfound then
316 hr_utility.trace('Legislative parameters not found for pactid '||pactid);
317 close c_parameters;
318 --raise;
319 end if;
320 close c_parameters;
321
322 hr_utility.set_location('action creation after prm',301);
323 hr_utility.trace('Parmeters: ');
324 hr_utility.trace(' gre_id : '||to_char(l_gre_id));
325 hr_utility.trace(' bg_id : '||to_char(l_bg_id));
326 hr_utility.trace(' as of date: '||l_as_of_date);
327 hr_utility.trace(' pactid : '||to_char(pactid));
328 hr_utility.trace(' chunk : '||to_char(chunk));
329 hr_utility.trace(' loc_id : '||to_char(l_loc_id)); -- l_loc_id
330 hr_utility.trace(' org_id : '||to_char(l_org_id)); -- l_org_id
331 hr_utility.trace(' stperson : '|| to_char(stperson));
332 hr_utility.trace(' endperson : '|| to_char(endperson));
333
334 hr_utility.set_location('action creation before ref cursor',302);
335 --
336 if pay_ac_utility.range_person_on(
337 P_REPORT_TYPE => 'OLT',
338 P_REPORT_FORMAT => 'DEFAULT',
339 P_REPORT_QUALIFIER => 'DEFAULT',
340 P_REPORT_CATEGORY => 'REPORT'
341 ) then
342
343 hr_utility.set_location('action creation before opening ref cursor',303);
344 hr_utility.trace('Range Person id is ON');
345
346
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 ||'
356 and paf.person_id = ppr.person_id
357 and paf.assignment_type = ''E''
358 and paa.assignment_id = paf.assignment_id
359 and ppa.payroll_action_id = paa.payroll_action_id
360 and paf.payroll_id = ppa.payroll_id
361 and paf.payroll_id is not null
362 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
363 and paa.action_status = ''C''
364 and ppa.business_group_id = '||l_bg_id ||'
365 and paf.business_group_id = ppa.business_group_id
366 and ppa.effective_date between trunc(to_date('''|| l_as_of_date ||''',''yyyy/mm/dd''), ''Y'')
367 and to_date('''|| l_as_of_date || ''',''yyyy/mm/dd'')
368 and ppa.effective_date between paf.effective_start_date
369 and paf.effective_end_date
370 order by 1, 3';
371 else
372
373 hr_utility.set_location('action creation before opening ref cursor',304);
374 hr_utility.trace('Range Person id is Off');
375
376 -- 5840569
377 lv_where_condition := '' ;
378 if l_gre_id is not null then
379 lv_where_condition := lv_where_condition||' and paa.tax_unit_id ='||l_gre_id ;
380 end if;
381 if l_org_id is not null then
382 lv_where_condition := lv_where_condition||' and paf.organization_id ='||l_org_id ;
383 end if;
384 if l_loc_id is not null then
385 lv_where_condition := lv_where_condition||' and paf.location_id ='||l_loc_id ;
386 end if;
387 hr_utility.trace('lv_where_condition :'||lv_where_condition) ;
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
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
403 and ppa.payroll_action_id = paa.payroll_action_id
404 and paf.payroll_id = ppa.payroll_id
405 and paf.payroll_id is not null
406 and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
407 and paa.action_status = ''C''
408 and ppa.business_group_id = '||l_bg_id ||'
409 and paf.business_group_id = ppa.business_group_id
410 and ppa.effective_date between trunc(to_date('''|| l_as_of_date ||''',''yyyy/mm/dd''), ''Y'')
411 and to_date('''|| l_as_of_date || ''',''yyyy/mm/dd'')
412 and ppa.effective_date between paf.effective_start_date
413 and paf.effective_end_date '||
414 lv_where_condition ||
415 ' order by 1, 3';
416 end if;
417
418 hr_utility.set_location('action creation before opening ref cursor',305);
419 open c_seq_act for lv_sqlstr;
420 loop
421 hr_utility.set_location('in ref cursor loop',310);
422 fetch c_seq_act into ln_personid,ln_assgid,ln_greid;
423 if c_seq_act%notfound then
424 hr_utility.set_location('exiting from ref cursor loop',320);
425 exit;
426 end if;
427 hr_utility.set_location('Insert action',320);
428 hr_utility.trace('ln_personid='||ln_personid);
429 hr_utility.trace('ln_assgid'||ln_assgid);
430 hr_utility.trace('ln_greid='||ln_greid);
431
432 insert_action(pactid => pactid,
433 chunk => chunk,
434 p_greid => ln_greid,
435 p_person_id => ln_personid,
436 p_assignid => ln_assgid
437 );
438 end loop;
439 close c_seq_act;
440
441 hr_utility.set_location('OUT action_creation',350);
442 end action_creation;
443 --
444 --
445 ------------------------------ sort_action ------------------------------------
446 --
447 procedure sort_action
448 (
449 pactid in varchar2, /* payroll action id */
450 sqlstr in out nocopy varchar2, /* string holding the sql statement */
451 len out nocopy number /* length of the sql string */
452 ) is
453 --
454 leg_param pay_payroll_actions.legislative_parameters%type;
455 l_sort1 varchar2(60);
456 l_sort2 varchar2(60);
457 l_sort3 varchar2(60);
458
459 cursor c_parameters ( pactid number) is
460 select
461 ppa.legislative_parameters,
462 pay_us_payroll_utils.get_parameter('SORT1',ppa.legislative_parameters),
463 pay_us_payroll_utils.get_parameter('SORT2',ppa.legislative_parameters),
464 pay_us_payroll_utils.get_parameter('SORT3',ppa.legislative_parameters)
465 from pay_payroll_actions ppa
466 where ppa.payroll_action_id = pactid;
467 --
468 begin
469 hr_utility.set_location('IN sort_action',400);
470 open c_parameters(pactid);
471 fetch c_parameters into leg_param,
472 l_sort1,
473 l_sort2,
474 l_sort3;
475 if c_parameters%notfound then
476 hr_utility.trace('Legislative parameters not found for pactid '||pactid);
477 close c_parameters;
478 -- raise;
479 end if;
480 close c_parameters;
481 --
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
491 WHERE ppa.payroll_action_id = :pactid
492 AND paa.payroll_action_id = ppa.payroll_action_id
493 and paf.assignment_id = paa.assignment_id
494 and paf.effective_start_date =
495 (select max(paf2.effective_start_date)
496 from per_all_assignments_f paf2
497 where paf2.assignment_id = paf.assignment_id
498 and paf2.effective_start_date <= ppa.effective_date)
499 and paf.assignment_type = ''E''
500 and ppf.person_id = paf.person_id
501 and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
502 and hou.organization_id = nvl(paf.organization_id,paf.business_group_id)
503 and hl.location_id = NVL(paf.location_id,hou.location_id)
504 ORDER BY
505 decode('''||l_sort1||''',
506 ''Employee_Name'',rpad(ppf.last_name||'' ''||ppf.first_name||'' ''||ppf.middle_names, 63),
507 ''Social_Security_Number'',rpad(ppf.national_identifier, 63),''Organization'', rpad(hou.name, 63),
508 ''Location'',rpad(hl.location_code, 63)),
509 decode('''||l_sort2||''',
510 ''Employee_Name'',rpad(ppf.last_name||'' ''||ppf.first_name||'' ''||ppf.middle_names, 63),
511 ''Social_Security_Number'',rpad(ppf.national_identifier, 63),''Organization'', rpad(hou.name, 63),
512 ''Location'',rpad(hl.location_code, 63)),
513 decode('''||l_sort3||''',
514 ''Employee_Name'',rpad(ppf.last_name||'' ''||ppf.first_name||'' ''||ppf.middle_names, 63),
515 ''Social_Security_Number'',rpad(ppf.national_identifier, 63),''Organization'', rpad(hou.name, 63),
516 ''Location'',rpad(hl.location_code, 63))
517 ';
518 len := length(sqlstr); -- return the length of the string.
519
520 hr_utility.trace('Sort sql string length = '||to_char(len));
521 hr_utility.set_location('OUT sort_action',450);
522 -- hr_utility.trace_off;
523
524 end sort_action;
525
526 --
527 ------------------------------ get_parameter ----------------------------------
528 --
529 function get_parameter(name in varchar2,
530 parameter_list varchar2) return varchar2
531 is
532 start_ptr number;
533 end_ptr number;
534 token_val pay_payroll_actions.legislative_parameters%type;
535 par_value pay_payroll_actions.legislative_parameters%type;
536 begin
537 --
538 token_val := name||'=';
539 --
540 start_ptr := instr(parameter_list, token_val) + length(token_val);
541 end_ptr := instr(parameter_list, ' ',start_ptr);
542 --
543 /* if there is no spaces use then length of the string */
544 if end_ptr = 0 then
545 end_ptr := length(parameter_list)+1;
546 end if;
547 --
548 /* Did we find the token */
549 if instr(parameter_list, token_val) = 0 then
550 par_value := NULL;
551 else
552 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
553 end if;
554 --
555 return par_value;
556 --
557 end get_parameter;
558 --
559 end pay_us_over_limit_pkg;
[Home] [Help]