[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_T4A_AMEND_ARCH
Source
1 PACKAGE BODY pay_ca_eoy_t4a_amend_arch AS
2 /* $Header: pycat4aca.pkb 120.3 2012/01/19 08:47:37 rpahune ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ca_eoy_t4a_amend_arch
21
22 Description : This procedure is used by T4A Amendment Pre-Process
23 to archive data for T4A Amendment Paper Report.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 25-AUG-2004 SSouresr 115.0 Created.
30 05-MAR-2005 SSouresr 115.1 Removed extra p_payroll_action_id
31 from the range cursor
32 11-OCT-2006 meshah 115.2 5528944 Added order by clause in the range
33 cursor queries.
34 29-Aug-2011 sneelapa 115.113 10399514 Introduced new CURSOR
35 c_selected_asg_set_range it will
36 be called in place of
37 c_selected_asg_set CURSOR,
38 if RANGE_PERSON_ID is enabled.
39 *****************************************************************************/
40
41 gv_package VARCHAR2(100) := 'pay_ca_eoy_t4a_amend_arch';
42 gv_procedure_name VARCHAR2(100);
43
44
45 /*****************************************************************************
46 Name : get_payroll_action_info
47 Purpose : This returns the Payroll Action level
48 information for Federal YE Amendment Archiver.
49 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
50 p_start_date - Start date of Archiver
51 p_end_date - End date of Archiver
52 p_business_group_id - Business Group ID
53 ******************************************************************************/
54 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
55 ,p_end_date out nocopy date
56 ,p_start_date out nocopy date
57 ,p_business_group_id out nocopy number
58 ,p_tax_unit_id out nocopy number
59 ,p_person_id out nocopy number
60 ,p_asg_set out nocopy number
61 ,p_year out nocopy varchar2)
62 IS
63 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
64 select to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
65 'TRANSFER_GRE',ppa.legislative_parameters)),
66 to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
67 'PER_ID',ppa.legislative_parameters)),
68 to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
69 'ASG_SET',ppa.legislative_parameters)),
70 to_char(effective_date,'YYYY') ,
71 effective_date,
72 start_date,
73 business_group_id
74 from pay_payroll_actions ppa
75 where ppa.payroll_action_id = cp_payroll_action_id;
76
77 ld_end_date DATE;
78 ld_start_date DATE;
79 ln_business_group_id NUMBER;
80 ln_tax_unit_id NUMBER := 0;
81 ln_person_id NUMBER := 0;
82 ln_asg_set NUMBER := 0;
83 lv_year VARCHAR2(4);
84
85 BEGIN
86 hr_utility.trace('Entered get_payroll_action_info');
87
88 open c_payroll_action_info(p_payroll_action_id);
89 fetch c_payroll_action_info into ln_tax_unit_id,
90 ln_person_id,
91 ln_asg_set,
92 lv_year,
93 ld_end_date,
94 ld_start_date,
95 ln_business_group_id;
96 close c_payroll_action_info;
97
98 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
99 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
100 hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
101 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
102 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
103 hr_utility.trace('lv_year = ' || lv_year);
104
105 p_end_date := ld_end_date;
106 p_start_date := ld_start_date;
107 p_business_group_id := ln_business_group_id;
108 p_tax_unit_id := ln_tax_unit_id;
109 p_person_id := ln_person_id;
110 p_asg_set := ln_asg_set;
111 p_year := lv_year;
112
113 hr_utility.trace('Leaving get_payroll_action_info');
114
115 EXCEPTION
116 when others then
117 hr_utility.trace('Error in ' || gv_procedure_name ||
118 to_char(sqlcode) || '-' || sqlerrm);
119 raise hr_utility.hr_error;
120
121 END get_payroll_action_info;
122
123 /*****************************************************************************
124 Name : get_payroll_action_info
125 Purpose : This returns the Payroll Action level
126 information for Federal YE Amendment Archiver.
127 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
128 p_start_date - Start date of Archiver
129 p_end_date - End date of Archiver
130 p_business_group_id - Business Group ID
131 ******************************************************************************/
132 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
133 ,p_end_date out nocopy date
134 ,p_start_date out nocopy date
135 ,p_business_group_id out nocopy number
136 ,p_tax_unit_id out nocopy number
137 ,p_person_id out nocopy number
138 ,p_asg_set out nocopy number
139 ,p_year out nocopy varchar2
140 -- Added for bug 10399514
141 ,p_report_type out nocopy varchar2
142 ,p_state out nocopy varchar2
143 ,p_report_cat out nocopy varchar2
144 -- Added for bug 10399514
145 )
146 IS
147 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
148 select to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
149 'TRANSFER_GRE',ppa.legislative_parameters)),
150 to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
151 'PER_ID',ppa.legislative_parameters)),
152 to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
153 'ASG_SET',ppa.legislative_parameters)),
154 to_char(effective_date,'YYYY') ,
155 effective_date,
156 start_date,
157 business_group_id
158 -- Added for bug 10399514
159 ,report_type
160 ,report_qualifier
161 ,report_category
162 -- Added for bug 10399514
163 from pay_payroll_actions ppa
164 where ppa.payroll_action_id = cp_payroll_action_id;
165
166 ld_end_date DATE;
167 ld_start_date DATE;
168 ln_business_group_id NUMBER;
169 ln_tax_unit_id NUMBER := 0;
170 ln_person_id NUMBER := 0;
171 ln_asg_set NUMBER := 0;
172 lv_year VARCHAR2(4);
173 -- Variables declared for bug 10399514
174 lv_report_type pay_payroll_actions.report_type%type;
175 lv_report_qualifier pay_payroll_actions.report_qualifier%type;
176 lv_report_cat pay_payroll_actions.report_category%type;
177 -- Variables declared for bug 10399514
178
179 BEGIN
180 hr_utility.trace('Entered get_payroll_action_info');
181
182 open c_payroll_action_info(p_payroll_action_id);
183 fetch c_payroll_action_info into ln_tax_unit_id,
184 ln_person_id,
185 ln_asg_set,
186 lv_year,
187 ld_end_date,
188 ld_start_date,
189 ln_business_group_id,
190 -- Added for bug 10399514
191 lv_report_type,
192 lv_report_qualifier,
193 lv_report_cat;
194 -- Added for bug 10399514
195 close c_payroll_action_info;
196
197 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
198 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
199 hr_utility.trace('ln_tax_unit_id = ' || to_char(ln_tax_unit_id));
200 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
201 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
202 hr_utility.trace('lv_report_type = ' || lv_report_type);
203 hr_utility.trace('lv_report_qualifier = '|| lv_report_qualifier);
204 hr_utility.trace('lv_report_cat = ' || lv_report_cat);
205
206 p_end_date := ld_end_date;
207 p_start_date := ld_start_date;
208 p_business_group_id := ln_business_group_id;
209 p_tax_unit_id := ln_tax_unit_id;
210 p_person_id := ln_person_id;
211 p_asg_set := ln_asg_set;
212 p_year := lv_year;
213 -- Added for bug 10399514
214 p_report_type := lv_report_type;
215 p_state := lv_report_qualifier;
216 p_report_cat := lv_report_cat;
217 -- Added for bug 10399514
218
219 hr_utility.trace('Leaving get_payroll_action_info');
220
221 EXCEPTION
222 when others then
223 hr_utility.trace('Error in ' || gv_procedure_name ||
224 to_char(sqlcode) || '-' || sqlerrm);
225 raise hr_utility.hr_error;
226
227 END get_payroll_action_info;
228
229
230
231 /******************************************************************
232 Name : eoy_range_cursor
233 Purpose : This returns the select statement that is
234 used to create the range rows for the
235 Federal YE Amendment Pre-Process.
236 Arguments :
237 Notes : Calls procedure - get_payroll_action_info
238 ******************************************************************/
239 PROCEDURE eoy_range_cursor(
240 p_payroll_action_id in number
241 ,p_sqlstr out nocopy varchar2)
242 IS
243
244 ld_end_date DATE;
245 ld_start_date DATE;
246 ln_business_group_id NUMBER;
247 ln_tax_unit_id NUMBER;
248 ln_person_id NUMBER := 0;
249 ln_asg_set NUMBER := 0;
250 lv_year VARCHAR2(4);
251 lv_sql_string VARCHAR2(32000);
252 lv_record_name varchar2(80);
253 lv_message varchar2(100) := null;
254
255 BEGIN
256 hr_utility.trace('Entered eoy_range_cursor');
257 hr_utility.trace('p_payroll_action_id = ' ||
258 to_char(p_payroll_action_id));
259
260 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
261 ,p_start_date => ld_start_date
262 ,p_end_date => ld_end_date
263 ,p_business_group_id => ln_business_group_id
264 ,p_tax_unit_id => ln_tax_unit_id
265 ,p_person_id => ln_person_id
266 ,p_asg_set => ln_asg_set
267 ,p_year => lv_year
268 );
269 if ln_person_id is not null then
270
271 lv_sql_string :=
272 'select distinct asg.person_id person_id
273 from per_all_assignments_f asg
274 where person_id = ' || to_char(ln_person_id) ||
275 ' and :p_payroll_action_id > 0';
276
277 hr_utility.trace('Range for person_id not null');
278
279 elsif ln_asg_set is not null then
280
281 lv_sql_string :=
282 'select distinct paf.person_id
283 from hr_assignment_set_amendments asgset,
284 per_all_assignments_f paf
285 where assignment_set_id = ' || to_char(ln_asg_set) || '
286 and asgset.assignment_id = paf.assignment_id
287 and asgset.include_or_exclude = ''I''
288 and :payroll_action_id > 0
289 order by paf.person_id ';
290
291 hr_utility.trace('Range for asg_set not null');
292
293 else
294
295 lv_record_name := 'Federal Amendment Pre-Process';
296 lv_message := 'No Employee or Assignment Set specified';
297
298 /* push message into pay_message_lines */
299 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
300 pay_core_utils.push_token('record_name',substr(lv_record_name,1,50));
301 pay_core_utils.push_token('name_or_number','');
302 pay_core_utils.push_token('description',substr(lv_message,1,50));
303
304 lv_sql_string :=
305 'select distinct asg.person_id person_id
306 from per_all_assignments_f asg
307 where person_id = 0
308 and :p_payroll_action_id > 0
309 order by asg.person_id ';
310
311 hr_utility.trace('No person selected as ln_person_id and ln_asg_set are null');
312
313 end if;
314
315 p_sqlstr := lv_sql_string;
316 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
317
318 hr_utility.trace('Leaving eoy_range_cursor');
319
320 END eoy_range_cursor;
321
322
323 /************************************************************
324 Name : eoy_action_creation
325 Purpose : This creates the assignment actions for
326 a specific chunk of people to be archived
327 by the Federal YE Amendment Pre-process.
328 Arguments :
329 Notes : Calls procedure - get_payroll_action_info
330 ************************************************************/
331
332 PROCEDURE eoy_action_creation(
333 p_payroll_action_id in number
334 ,p_start_person_id in number
335 ,p_end_person_id in number
336 ,p_chunk in number)
337
338 IS
339
340 ln_assignment_id NUMBER := 0;
341 ln_tax_unit_id NUMBER := 0;
342 ld_effective_date DATE;
343 ln_asg_action_id NUMBER := 0;
344 ln_primary_assignment_id NUMBER := 0;
345 ln_yepp_aaid NUMBER := 0;
346 ln_payroll_action_id NUMBER := 0;
347 ln_t4a_amend_asg_action NUMBER := 0;
348 lv_year VARCHAR2(4);
349
350 ld_end_date DATE;
351 ld_start_date DATE;
352 ln_business_group_id NUMBER;
353 ln_person_id NUMBER := 0;
354 ln_person_id_sel NUMBER := 0;
355 ln_asg_set NUMBER := 0;
356 ln_prev_asg_action_id NUMBER := 0;
357 ln_prev_assignment_id NUMBER := 0;
358 ln_prev_tax_unit_id NUMBER := 0;
359 ld_prev_effective_date DATE;
360 lv_report_type pay_payroll_actions.report_type%TYPE;
361 -- Variables declared for bug 10399514
362 l_person_on boolean ;
363 l_report_cat pay_payroll_actions.report_category%type;
364 l_state pay_payroll_actions.report_qualifier%type;
365 l_report_format pay_report_format_mappings_f.report_format%type;
366 -- Variables declared for bug 10399514
367 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
368
369
370 lv_serial_number VARCHAR2(30);
371 lv_national_identifier per_all_people_f.national_identifier%type;
372 lv_message varchar2(100):= null;
373 lv_full_name per_all_people_f.full_name%type;
374 lv_name varchar2(100);
375 lv_record_name varchar2(80);
376
377
378 CURSOR c_selected_asg_set(cp_start_person in number
379 ,cp_end_person in number
380 ,cp_asg_set in number) is
381 select distinct paf.person_id
382 from hr_assignment_set_amendments asgset,
383 per_all_assignments_f paf,
384 pay_assignment_actions paa,
385 pay_payroll_actions ppa
386 where asgset.assignment_set_id = cp_asg_set
387 and asgset.include_or_exclude = 'I'
388 and paf.assignment_id = asgset.assignment_id
389 and paf.person_id between cp_start_person
390 and cp_end_person
391 and ppa.report_type = 'T4A'
392 and to_char(ppa.effective_date,'YYYY') = lv_year
393 and ppa.business_group_id+0 = ln_business_group_id
394 and to_number(pay_ca_eoy_t4a_amend_arch.get_parameter('TRANSFER_GRE',
395 ppa.legislative_parameters)) = ln_tax_unit_id
396 and paa.payroll_action_id = ppa.payroll_action_id
397 and paa.action_status = 'C'
398 and paf.person_id = to_number(paa.serial_number);
399
400 -- Added for Bug# 10399514
401 -- Used when RANGE_PERSON_ID functionality is available
402
403 CURSOR c_selected_asg_set_range(cp_start_person in number
404 ,cp_end_person in number
405 ,cp_asg_set in number) is
406 select distinct paf.person_id
407 from hr_assignment_set_amendments asgset,
408 per_all_assignments_f paf,
409 pay_assignment_actions paa,
410 pay_payroll_actions ppa,
411 pay_population_ranges ppr
412 where asgset.assignment_set_id = cp_asg_set
413 and asgset.include_or_exclude = 'I'
414 and paf.assignment_id = asgset.assignment_id
415 --and paf.person_id between cp_start_person
416 -- and cp_end_person
417 and ppr.payroll_action_id = p_payroll_action_id
418 and ppr.chunk_number = p_chunk
419 and ppr.person_id = paf.person_id
420 and ppa.report_type = 'T4A'
421 and to_char(ppa.effective_date,'YYYY') = lv_year
422 and ppa.business_group_id+0 = ln_business_group_id
423 and to_number(pay_ca_eoy_t4a_amend_arch.get_parameter('TRANSFER_GRE',
424 ppa.legislative_parameters)) = ln_tax_unit_id
425 and paa.payroll_action_id = ppa.payroll_action_id
426 and paa.action_status = 'C'
427 and paf.person_id = to_number(paa.serial_number);
428
429
430 PROCEDURE action_creation (p_person_id in NUMBER)
431 IS
432
433 CURSOR get_prev_t4a_amend_dtls(cp_person_id in number
434 ,cp_tax_unit_id in number
435 ,cp_effective_date in date) is
436 select ppa.report_type,
437 paa.assignment_id,
438 paa.assignment_action_id
439 from pay_payroll_actions ppa,
440 pay_assignment_actions paa
441 where paa.tax_unit_id = cp_tax_unit_id
442 and paa.action_status = 'C'
443 and ppa.business_group_id+0 = ln_business_group_id
444 and ppa.payroll_action_id = paa.payroll_action_id
445 and ppa.effective_date = cp_effective_date
446 and ppa.report_type in ('T4A', 'CAEOY_T4A_AMEND_PP')
447 and to_number(pay_ca_eoy_t4a_amend_arch.get_parameter('TRANSFER_GRE',
448 ppa.legislative_parameters)) = cp_tax_unit_id
449 and to_number(paa.serial_number) = cp_person_id
450 order by paa.assignment_action_id desc;
451
452 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
453 select substr(full_name,1,31), substr(national_identifier,1,11)
454 from per_all_people_f
455 where person_id = cp_person_id
456 ORDER BY effective_end_date desc;
457
458 BEGIN
459
460 /* Actions will be created if
461
462 #1 there is a YEPP action not followed by a
463 T4A Amendment Archiver action
464
465 #2 there is already a Previous T4A Amendment Archiver
466 action for this person
467 */
468
469 open get_prev_t4a_amend_dtls(p_person_id
470 ,ln_tax_unit_id
471 ,ld_end_date);
472
473 fetch get_prev_t4a_amend_dtls into lv_report_type
474 ,ln_primary_assignment_id
475 ,ln_asg_act_to_lock;
476
477 hr_utility.trace('lv_report_type ='||lv_report_type);
478 hr_utility.trace('ln_primary_assignment_id ='||
479 to_char(ln_primary_assignment_id));
480 hr_utility.trace('ln_asg_act_to_lock ='||
481 to_char(ln_asg_act_to_lock));
482
483 if get_prev_t4a_amend_dtls%notfound then
484
485 hr_utility.trace('get_prev_t4a_amend_dtls Not Found');
486 hr_utility.trace('Warning Message Generated');
487 hr_utility.trace('p_person_id ='||to_char(p_person_id));
488
489 open get_warning_dtls_for_ee(p_person_id);
490 fetch get_warning_dtls_for_ee into lv_full_name
491 ,lv_national_identifier;
492
493 hr_utility.trace('lv_full_name ='||lv_full_name);
494 hr_utility.trace('lv_national_identifier ='||lv_national_identifier);
495
496 lv_name := lv_full_name || ', SIN '||lv_national_identifier;
497
498 close get_warning_dtls_for_ee;
499
500 lv_record_name := 'T4A Amendment Pre-Process';
501 lv_message := 'Federal YE Preprocess was not run for this employee';
502
503 /* push message into pay_message_lines */
504 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
505 pay_core_utils.push_token('record_name',substr(lv_record_name,1,50));
506 pay_core_utils.push_token('name_or_number',substr(lv_name,1,50));
507 pay_core_utils.push_token('description',substr(lv_message,1,50));
508
509 end if;
510
511
512 if get_prev_t4a_amend_dtls%found then
513 hr_utility.trace('get_prev_t4a_amend_dtls Found');
514
515 if lv_report_type in ('T4A', 'CAEOY_T4A_AMEND_PP') then
516
517 /* Create an assignment action for this person */
518 select pay_assignment_actions_s.nextval
519 into ln_t4a_amend_asg_action
520 from dual;
521
522 hr_utility.trace('New T4A Amendment Action = ' ||
523 to_char(ln_t4a_amend_asg_action));
524
525 /* Insert into pay_assignment_actions. */
526 hr_utility.trace('creating assignment action');
527 hr_nonrun_asact.insact(ln_t4a_amend_asg_action
528 ,ln_primary_assignment_id
529 ,p_payroll_action_id
530 ,p_chunk
531 ,ln_tax_unit_id);
532
533 /* Update the serial number column with the person id
534 so that the T4A Amendment report will not have
535 to do an additional checking against the assignment
536 table */
537
538 hr_utility.trace('updating assignment action');
539
540 update pay_assignment_actions aa
541 set aa.serial_number = to_char(p_person_id)
542 where aa.assignment_action_id = ln_t4a_amend_asg_action;
543
544 /* Interlock the YEPP or Previous T4A Amendment Archiver
545 action for current T4A Amendment Pre-process action */
546
547 hr_utility.trace('Locking Action = ' || to_char(ln_t4a_amend_asg_action));
548 hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
549 hr_nonrun_asact.insint(ln_t4a_amend_asg_action
550 ,ln_asg_act_to_lock);
551
552 end if; /* report type */
553
554 end if; /* employee found*/
555
556 close get_prev_t4a_amend_dtls;
557 hr_utility.trace('closed get_prev_t4a_amend_dtls');
558 END action_creation;
559
560 BEGIN
561 hr_utility.trace('Entered eoy_action_creation ');
562 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
563 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
564 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
565 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
566
567 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
568 ,p_start_date => ld_start_date
569 ,p_end_date => ld_end_date
570 ,p_business_group_id => ln_business_group_id
571 ,p_tax_unit_id => ln_tax_unit_id
572 ,p_person_id => ln_person_id
573 ,p_asg_set => ln_asg_set
574 ,p_year => lv_year
575 -- Added for bug 10399514
576 ,p_report_type => lv_report_type
577 ,p_state => l_state
578 ,p_report_cat => l_report_cat);
579 -- Added for bug 10399514
580
581 /* PERSON ID IS NOT NULL */
582
583 if ln_person_id is not null then
584
585 hr_utility.trace('Entered Person Id logic');
586 action_creation(p_start_person_id);
587
588 elsif ln_asg_set is not null then
589
590 hr_utility.trace('Entered Asg Set logic');
591 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
592
593 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
594 hr_utility.trace('End Person ='||to_char(p_end_person_id));
595 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
596
597 -- Code modification for bug 10399514 start here
598 l_person_on := FALSE ;
599
600 Begin
601 select report_format
602 into l_report_format
603 from pay_report_format_mappings_f
604 where report_type = lv_report_type
605 and report_qualifier = l_state
606 and report_category = l_report_cat ;
607 Exception
608 When Others Then
609 l_report_format := Null ;
610 End ;
611
612 l_person_on := pay_ac_utility.range_person_on( p_report_type => lv_report_type,
613 p_report_format => l_report_format,
614 p_report_qualifier => l_state,
615 p_report_category => l_report_cat) ;
616
617
618 if l_person_on then
619 hr_utility.trace('opening c_selected_asg_set_range CURSOR');
620 OPEN c_selected_asg_set_range (p_start_person_id
621 ,p_end_person_id
622 ,ln_asg_set);
623 else
624 hr_utility.trace('opening c_selected_asg_set CURSOR');
625 OPEN c_selected_asg_set (p_start_person_id
626 ,p_end_person_id
627 ,ln_asg_set);
628 end if ;
629
630 -- Code modification for bug 10399514 ends here
631
632
633 loop
634
635 -- Code modification for bug 10399514 starts here
636 if l_person_on then
637 hr_utility.trace('fetching from c_selected_asg_set_range CURSOR');
638 fetch c_selected_asg_set_range
639 into ln_person_id_sel;
640 exit when c_selected_asg_set_range%NOTFOUND;
641 else
642 hr_utility.trace('fetching from c_selected_asg_set CURSOR');
643 fetch c_selected_asg_set
644 into ln_person_id_sel;
645 exit when c_selected_asg_set%NOTFOUND;
646 end if ;
647 -- Code modification for bug 10399514 ends here
648
649 hr_utility.trace('ln_person_id after c_selected_asg_set = '||to_char(ln_person_id_sel));
650
651 action_creation(ln_person_id_sel);
652
653 end loop;
654
655 -- Code modification for bug 10399514 starts here
656 if l_person_on then
657 hr_utility.trace('closing c_selected_asg_set_range CURSOR');
658 close c_selected_asg_set_range;
659 else
660 hr_utility.trace('closing c_selected_asg_set CURSOR');
661 close c_selected_asg_set;
662 end if ;
663 -- Code modification for bug 10399514 ends here
664 end if; /* ln_person_id or lv_sin is not null */
665
666 END eoy_action_creation;
667
668 FUNCTION get_parameter(name in varchar2,
669 parameter_list varchar2) return varchar2
670 is
671 start_ptr number;
672 end_ptr number;
673 token_val pay_payroll_actions.legislative_parameters%type;
674 par_value pay_payroll_actions.legislative_parameters%type;
675 begin
676
677 token_val := name||'=';
678
679 start_ptr := instr(parameter_list, token_val) + length(token_val);
680 end_ptr := instr(parameter_list, ' ',start_ptr);
681
682
683 /* if there is no spaces use then length of the string */
684 if end_ptr = 0 then
685 end_ptr := length(parameter_list)+1;
686 end if;
687
688 /* Did we find the token */
689 if instr(parameter_list, token_val) = 0 then
690 par_value := NULL;
691 else
692 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
693 end if;
694
695 return par_value;
696
697 end get_parameter;
698
699 Begin
700 --hr_utility.trace_on(null,'T4AAMENDPP');
701 null;
702
703
704
705 end pay_ca_eoy_t4a_amend_arch;