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