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