[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_RL2_AMEND_REG
Source
1 PACKAGE BODY pay_ca_eoy_rl2_amend_reg AS
2 /* $Header: pycarl2cr.pkb 120.1 2006/10/17 00:10:01 meshah noship $ */
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_rl2_amend_reg
21
22 Description : This Package is used by RL2 Amendment Register
23 and RL2 Amendment Paper Reports.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 04-FEB-2006 SSouresr 115.0 Created.
30 16-OCT-2006 meshah 115.1 5528944 Added order by to the range
31 cursor queries.
32 *****************************************************************************/
33
34 gv_package VARCHAR2(100) := 'pay_ca_eoy_rl2_amend_reg';
35 gv_procedure_name VARCHAR2(100);
36
37 /*****************************************************************************
38 Name : get_payroll_action_info
39 Purpose : This returns the Payroll Action level
40 information for RL2 Amendment Paper.
41 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
42 p_start_date - Start date of RL2 Amendment Paper
43 p_end_date - End date of RL2 Amendment Paper
44 p_business_group_id - Business Group ID
45 *****************************************************************************/
46 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
47 ,p_end_date out nocopy date
48 ,p_start_date out nocopy date
49 ,p_business_group_id out nocopy number
50 ,p_pre_org_id out nocopy number
51 ,p_person_id out nocopy number
52 ,p_asg_set out nocopy number
53 ,p_print out nocopy varchar2
54 ,p_report_type out nocopy varchar2
55 )
56 IS
57 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
58 select to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
59 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
60 to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
61 'PER_ID',ppa.legislative_parameters)),
62 to_number(pay_ca_eoy_rl1_amend_reg.get_parameter(
63 'ASG_SET_ID',ppa.legislative_parameters)),
64 pay_ca_eoy_rl1_amend_reg.get_parameter(
65 'MODE',ppa.legislative_parameters),
66 effective_date,
67 start_date,
68 business_group_id,
69 report_type
70 from pay_payroll_actions ppa
71 where ppa.payroll_action_id = cp_payroll_action_id;
72
73 ld_end_date DATE;
74 ld_start_date DATE;
75 ln_business_group_id NUMBER;
76 ln_pre_org_id NUMBER := 0;
77 ln_person_id NUMBER := 0;
78 ln_asg_set NUMBER := 0;
79 lv_print varchar2(10);
80 lv_report_type varchar2(50);
81
82 BEGIN
83 hr_utility.trace('Entered get_payroll_action_info');
84 open c_payroll_action_info(p_payroll_action_id);
85 fetch c_payroll_action_info into ln_pre_org_id,
86 ln_person_id,
87 ln_asg_set,
88 lv_print,
89 ld_end_date,
90 ld_start_date,
91 ln_business_group_id,
92 lv_report_type;
93 close c_payroll_action_info;
94
95 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
96 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
97 hr_utility.trace('ln_pre_org_id = ' || to_char(ln_pre_org_id));
98 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
99 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
100
101 p_end_date := ld_end_date;
102 p_start_date := ld_start_date;
103 p_business_group_id := ln_business_group_id;
104 p_pre_org_id := ln_pre_org_id;
105 p_person_id := ln_person_id;
106 p_asg_set := ln_asg_set;
107 p_print := lv_print;
108 p_report_type := lv_report_type;
109
110 hr_utility.trace('Leaving get_payroll_action_info');
111
112 EXCEPTION
113 when others then
114 hr_utility.trace('Error in ' || gv_procedure_name ||
115 to_char(sqlcode) || '-' || sqlerrm);
116 raise hr_utility.hr_error;
117
118 END get_payroll_action_info;
119
120
121 /******************************************************************
122 Name : range_cursor
123 Purpose : This returns the select statement that is
124 used to created the range rows for the
125 RL2 Amendment Paper.
126 Arguments :
127 Notes : Calls procedure - get_payroll_action_info
128 ******************************************************************/
129 PROCEDURE range_cursor(
130 p_payroll_action_id in number
131 ,p_sqlstr out nocopy varchar2)
132 IS
133
134 ld_end_date DATE;
135 ld_start_date DATE;
136 ln_business_group_id NUMBER;
137 ln_pre_org_id NUMBER;
138 ln_person_id NUMBER := 0;
139 ln_asg_set NUMBER := 0;
140 lv_sql_string VARCHAR2(32000);
141 lv_print varchar2(10):=null;
142 ln_year number;
143 lv_report_type varchar2(50);
144
145 BEGIN
146 hr_utility.trace('Entered range_cursor');
147 hr_utility.trace('p_payroll_action_id = ' ||
148 to_char(p_payroll_action_id));
149
150 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
151 ,p_start_date => ld_start_date
152 ,p_end_date => ld_end_date
153 ,p_business_group_id => ln_business_group_id
154 ,p_pre_org_id => ln_pre_org_id
155 ,p_person_id => ln_person_id
156 ,p_asg_set => ln_asg_set
157 ,p_print => lv_print
158 ,p_report_type => lv_report_type);
159
160 ln_year := to_number(to_char(ld_end_date,'YYYY'));
161
162 if ln_person_id is not null then
163
164 lv_sql_string :=
165 'select distinct asg.person_id person_id
166 from per_assignments_f asg
167 where person_id = ' || ln_person_id ||
168 ' and :payroll_action_id > 0';
169
170 hr_utility.trace('Range for person_id not null');
171
172 elsif ln_asg_set is not null then
173
174 lv_sql_string :=
175 'select distinct paf.person_id
176 from hr_assignment_set_amendments asgset,
177 per_assignments_f paf
178 where assignment_set_id = ' || ln_asg_set || '
179 and asgset.assignment_id = paf.assignment_id
180 and asgset.include_or_exclude = ''I''
181 and :payroll_action_id > 0
182 order by paf.person_id ';
183
184 hr_utility.trace('Range for asg_set not null');
185
186 elsif ln_pre_org_id is not NULL then
187
188 lv_sql_string :=
189 'select distinct paf.person_id
190 from pay_payroll_actions ppa_arch,
191 pay_assignment_actions paa_arch,
192 per_assignments_f paf,
193 pay_payroll_actions ppa
194 where paa_arch.assignment_id = paf.assignment_id
195 and ppa.payroll_action_id = :payroll_action_id
196 and ppa_arch.business_group_id = ppa.business_group_id
197 and ppa_arch.effective_date = ppa.effective_date
198 and ppa_arch.report_type = ''CAEOY_RL2_AMEND_PP''
199 and pycadar_pkg.get_parameter(''PRE_ORGANIZATION_ID'',
200 ppa_arch.legislative_parameters) = '|| ln_pre_org_id ||'
201 and paa_arch.action_status = ''C''
202 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
203 order by paf.person_id ';
204
205 else
206
207 lv_sql_string :=
208 'select distinct paf.person_id
209 from pay_payroll_actions ppa_arch,
210 pay_assignment_actions paa_arch,
211 per_assignments_f paf,
212 pay_payroll_actions ppa
213 where paa_arch.assignment_id = paf.assignment_id
214 and ppa.payroll_action_id = :payroll_action_id
215 and ppa_arch.business_group_id = ppa.business_group_id
216 and ppa_arch.effective_date = ppa.effective_date
217 and ppa_arch.report_type = ''CAEOY_RL2_AMEND_PP''
218 and paa_arch.action_status = ''C''
219 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
220 order by paf.person_id ';
221
222 end if;
223
224 p_sqlstr := lv_sql_string;
225 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
226
227 END range_cursor;
228
229
230 /************************************************************
231 Name : action_creation
232 Purpose : This creates the assignment actions for
233 a specific chunk of people to be archived
234 by the RL2 Amendment Report process.
235 Arguments :
236 Notes : Calls procedure - get_payroll_action_info
237 ************************************************************/
238
239 PROCEDURE action_creation(
240 p_payroll_action_id in number
241 ,p_start_person_id in number
242 ,p_end_person_id in number
243 ,p_chunk in number)
244
245 IS
246 ln_assignment_id NUMBER := 0;
247 ln_pre_org_id NUMBER := 0;
248 ln_pre_parameter NUMBER;
249 ln_pre_id NUMBER;
250 ld_effective_date DATE;
251 ln_asg_action_id NUMBER := 0;
252 ln_primary_assignment_id NUMBER := 0;
253 ln_payroll_action_id NUMBER := 0;
254 ln_rl2_amend_reg_asg_action NUMBER := 0;
255 lv_year VARCHAR2(4);
256
257 ld_end_date DATE;
258 ld_start_date DATE;
259 ln_business_group_id NUMBER;
260 ln_person_id NUMBER := 0 ;
261 ln_set_person_id NUMBER := 0 ;
262 ln_asg_set NUMBER := 0 ;
263 lv_print varchar2(10);
264
265 lv_report_type pay_payroll_actions.report_type%TYPE ;
266 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
267
268 lv_serial_number VARCHAR2(30);
269 lv_employee_number per_people_f.employee_number%type;
270 lv_message varchar2(100):= null;
271 lv_full_name per_people_f.full_name%type;
272 lv_record_name varchar2(100);
273 ln_serial_number pay_assignment_actions.serial_number%TYPE;
274 lv_ppr_report_type varchar2(50);
275
276 CURSOR c_selected_asg_set(cp_start_person in number
277 ,cp_end_person in number
278 ,cp_asg_set in number
279 ,cp_effective_date in date) is
280 select distinct paf.person_id
281 from hr_assignment_set_amendments asgset,
282 per_assignments_f paf,
283 pay_payroll_actions ppa_arch,
284 pay_assignment_actions paa_arch
285 where asgset.assignment_set_id = cp_asg_set
286 and asgset.include_or_exclude = 'I'
287 and paf.assignment_id = asgset.assignment_id
288 and paf.person_id between cp_start_person
289 and cp_end_person
290 and ppa_arch.business_group_id = ln_business_group_id
291 and ppa_arch.report_type = 'CAEOY_RL2_AMEND_PP'
292 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
293 and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
294 ppa_arch.legislative_parameters) =
295 nvl(ln_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
296 ppa_arch.legislative_parameters))
297 and paa_arch.action_status = 'C'
298 and ppa_arch.effective_date = cp_effective_date
299 and paf.person_id = to_number(paa_arch.serial_number);
300
301 /* Cursor c_all_pres to select RL2 Amendment PRE based on Business Group
302 and effective date */
303 CURSOR c_all_pres(cp_bg_id number,
304 cp_eff_date date) IS
305 select hou.organization_id
306 from hr_organization_information hoi,
307 hr_all_organization_units hou
308 where hou.business_group_id = cp_bg_id
309 AND hou.organization_id = hoi.organization_id
310 AND hou.date_from <= cp_eff_date
311 AND nvl(hou.date_to,
312 fnd_date.canonical_to_date('4712/12/31 00:00:00')) >= cp_eff_date
313 AND hoi.org_information_context = 'Prov Reporting Est'
314 AND hoi.org_information4 = 'P02'
315 AND exists ( SELECT 1
316 FROM pay_payroll_actions ppa ,
317 pay_assignment_actions paa
318 WHERE ppa.report_type = 'CAEOY_RL2_AMEND_PP'
319 AND ppa.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
320 AND ppa.business_group_id = cp_bg_id
321 AND ppa.effective_date = cp_eff_date
322 AND paa.payroll_action_id = ppa.payroll_action_id
323 AND pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
324 ppa.legislative_parameters) = to_char(hou.organization_id));
325
326 cursor c_all_asg(cp_bg_id number,
327 cp_pre_org_id number,
328 cp_eff_date date,
329 cp_start_person number,
330 cp_end_person number) is
331 select distinct paa_arch.serial_number
332 from pay_payroll_actions ppa_arch,
333 pay_assignment_actions paa_arch
334 where ppa_arch.business_group_id = cp_bg_id
335 and ppa_arch.report_type = 'CAEOY_RL2_AMEND_PP'
336 AND ppa_arch.report_qualifier = 'CAEOY_RL2_AMEND_PPQ'
337 and ppa_arch.effective_date = cp_eff_date
338 and ppa_arch.action_status = 'C'
339 and paa_arch.payroll_action_id = ppa_arch.payroll_action_id
340 and paa_arch.action_status = 'C'
341 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
342 ppa_arch.legislative_parameters) = to_char(cp_pre_org_id)
343 and to_number(paa_arch.serial_number) between
344 cp_start_person and cp_end_person;
345
346
347
348 PROCEDURE action_creation (p_person_id in NUMBER)
349 IS
350
351 CURSOR get_latest_rl2_amend_dtls (cp_person_id in number
352 ,cp_pre_org_id in number
353 ,cp_effective_date in date
354 ,cp_business_group_id in number) is
355 select ppa.report_type,
356 paa.assignment_id,
357 paa.assignment_action_id,
358 pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
359 ppa.legislative_parameters)
360 from pay_payroll_actions ppa,
361 pay_assignment_actions paa
362 where paa.serial_number = to_char(cp_person_id)
363 and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
364 ppa.legislative_parameters) =
365 nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
366 ppa.legislative_parameters))
367 and paa.action_status = 'C'
368 and ppa.payroll_action_id = paa.payroll_action_id
369 and ppa.effective_date = cp_effective_date
370 and ppa.business_group_id = cp_business_group_id
371 and ppa.report_type = 'CAEOY_RL2_AMEND_PP'
372 and not exists (select 1
373 from pay_action_interlocks
374 where locked_action_id = paa.assignment_action_id)
375 and exists (select 1
376 from per_assignments_f paf
377 where paf.assignment_id = paa.assignment_id
378 and paf.effective_start_date <= cp_effective_date
379 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
380 order by paa.assignment_action_id desc;
381
382 CURSOR get_all_rl2_amend_dtls (cp_person_id in number
383 ,cp_pre_org_id in number
384 ,cp_effective_date in date
385 ,cp_business_group_id in number) is
386 select ppa.report_type,
387 paa.assignment_id,
388 paa.assignment_action_id,
389 pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
393 where paa.serial_number = to_char(cp_person_id)
390 ppa.legislative_parameters)
391 from pay_payroll_actions ppa,
392 pay_assignment_actions paa
394 and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
395 ppa.legislative_parameters) =
396 nvl(cp_pre_org_id,pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
397 ppa.legislative_parameters))
398 and paa.action_status = 'C'
399 and ppa.business_group_id = cp_business_group_id
400 and ppa.payroll_action_id = paa.payroll_action_id
401 and ppa.effective_date = cp_effective_date
402 and ppa.report_type = 'CAEOY_RL2_AMEND_PP'
403 and exists (select 1
404 from per_assignments_f paf
405 where paf.assignment_id = paa.assignment_id
406 and paf.effective_start_date <= cp_effective_date
407 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
408 order by paa.assignment_action_id desc;
409
410 CURSOR get_yepp_pact_id(cp_bg_id number,
411 cp_pre number,
412 cp_year date) IS
413 select payroll_action_id
414 from pay_payroll_actions
415 where business_group_id = cp_bg_id
416 and report_type = 'RL2'
417 and report_qualifier = 'CAEOYRL2'
418 and action_type = 'X'
419 and action_status = 'C'
420 and effective_date = cp_year
421 and pay_ca_eoy_rl1_amend_reg.get_parameter('PRE_ORGANIZATION_ID',
422 legislative_parameters) = to_char(cp_pre);
423
424 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
425 select substr(full_name,1,48), employee_number
426 from per_people_f
427 where person_id = cp_person_id
428 order by effective_end_date desc;
429
430 CURSOR c_get_prov_amend_flag(cp_asg_act_id number) IS
431 select action_information2
432 from pay_action_information
433 where action_context_id = cp_asg_act_id
434 and action_information_category = 'CAEOY RL2 EMPLOYEE INFO2'
435 and action_context_type = 'AAP'
436 and jurisdiction_code = 'QC';
437
438 lv_gross_earn_value varchar2(30);
439 lv_jurisdiction varchar2(10);
440 lv_prov_amend_flag varchar2(5);
441 ln_yepp_pact_id number;
442 ln_pre_id_null number;
443 ln_iteration number := 0;
444
445 BEGIN
446
447 -- Check mode here
448 if lv_print = 'RECENT' then
449 open get_latest_rl2_amend_dtls(p_person_id
450 ,ln_pre_org_id
451 ,ld_end_date
452 ,ln_business_group_id);
453
454 loop
455
456 fetch get_latest_rl2_amend_dtls into lv_report_type,
457 ln_primary_assignment_id,
458 ln_asg_act_to_lock,
459 ln_pre_id_null;
460
461 if get_latest_rl2_amend_dtls%notfound then
462
463 if ln_iteration = 0 then
464
465 open get_warning_dtls_for_ee(p_person_id);
466 fetch get_warning_dtls_for_ee into lv_full_name,
467 lv_employee_number;
468 close get_warning_dtls_for_ee;
469
470 hr_utility.trace('get_latest_rl2_amend_dtls not found');
471 hr_utility.trace('p_person_id :'||to_char(p_person_id));
472
473 if lv_ppr_report_type = 'PYRL2PRAMEND' then
474 lv_record_name := 'RL2 Amend Paper Report';
475 else
476 lv_record_name := 'RL2 Amend Register Report';
477 end if;
478 lv_message := 'RL2 Amend Preprocess was not run for this employee';
479
480 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
481 pay_core_utils.push_token('record_name',lv_record_name);
482 pay_core_utils.push_token('name_or_number',lv_full_name);
483 pay_core_utils.push_token('description',lv_message);
484
485 end if;
486 exit;
487
488 end if;
489
490 ln_iteration := ln_iteration + 1;
491
492 if get_latest_rl2_amend_dtls%found then
493
494 begin
495
496 open c_get_prov_amend_flag(ln_asg_act_to_lock);
497
498 lv_prov_amend_flag := 'N';
499 fetch c_get_prov_amend_flag into lv_prov_amend_flag;
500
501 hr_utility.trace('lv_prov_amend_flag : '||lv_prov_amend_flag);
502
503 if c_get_prov_amend_flag%FOUND then
504
505 if lv_prov_amend_flag = 'Y' then
506
507 if ln_pre_parameter is NULL then
508 ln_pre_org_id := ln_pre_id_null;
509 end if;
510
511 open get_yepp_pact_id(ln_business_group_id,
512 ln_pre_org_id,
513 ld_end_date);
517 /* Create an assignment action for this person */
514 fetch get_yepp_pact_id into ln_yepp_pact_id;
515 close get_yepp_pact_id;
516
518 hr_utility.trace('get_latest_rl2_amend_dtls found ');
519 hr_utility.trace('Report Type: '||lv_report_type);
520
521 select pay_assignment_actions_s.nextval
522 into ln_rl2_amend_reg_asg_action
523 from dual;
524
525 hr_utility.trace('New RL2 Amend Action = ' ||
526 to_char(ln_rl2_amend_reg_asg_action));
527
528 /* Insert into pay_assignment_actions. */
529 hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
530 ln_primary_assignment_id,
531 p_payroll_action_id,
532 p_chunk,
533 ln_pre_org_id);
534
535 if ln_pre_parameter is NULL then
536 ln_pre_org_id := '';
537 end if;
538
539 /***********************************************************
540 ** Update the serial number column with Province_code QC,
541 ** Archiver assignment_action and Archiver Payroll_action_id
542 ** so that we need not refer back in the reports. This
543 ** logic works for both RL2 Amendment Register and RL2 Register
544 ** reports.
545 ***********************************************************/
546 ln_serial_number := 'QC'||
547 lpad(to_char(ln_asg_act_to_lock),14,0)||
548 lpad(to_char(ln_yepp_pact_id),14,0);
549
550 update pay_assignment_actions aa
551 set aa.serial_number = ln_serial_number
552 where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
553
554 hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
555 hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
556 hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
557
558 hr_utility.trace('Locking Action'||ln_rl2_amend_reg_asg_action);
559 hr_utility.trace('ln_serial_number :' || ln_serial_number);
560
561 end if; -- lv_prov_amend_flag = 'Y'
562
563 end if; -- c_get_prov_amend_flag%FOUND
564
565 close c_get_prov_amend_flag;
566
567 end;
568
569 end if; /* get_latest_rl2_amend_dtls found*/
570
571 end loop; /* get_latest_rl2_amend_dtls loop */
572 close get_latest_rl2_amend_dtls;
573
574 -- check Mode here
575 elsif lv_print = 'HISTORICAL' then
576
577 open get_all_rl2_amend_dtls(p_person_id
578 ,ln_pre_org_id
579 ,ld_end_date
580 ,ln_business_group_id);
581 lv_report_type := null;
582 ln_primary_assignment_id := 0;
583 ln_asg_act_to_lock := 0;
584 ln_pre_id_null := 0;
585
586 hr_utility.trace('lv_print :'||lv_print);
587
588 loop
589
590 fetch get_all_rl2_amend_dtls into lv_report_type
591 ,ln_primary_assignment_id
592 ,ln_asg_act_to_lock
593 ,ln_pre_id_null;
594
595 if get_all_rl2_amend_dtls%notfound then
596 hr_utility.trace('get_all_rl2_amend_dtls not found ');
597 exit;
598 end if;
599
600 open c_get_prov_amend_flag(ln_asg_act_to_lock);
601
602 lv_prov_amend_flag := 'N';
603 fetch c_get_prov_amend_flag into lv_prov_amend_flag;
604
605 hr_utility.trace('lv_prov_amend_flag : '||lv_prov_amend_flag);
606
607 if c_get_prov_amend_flag%FOUND then
608
609 if lv_prov_amend_flag = 'Y' then
610
611 if ln_pre_parameter is NULL then
612 ln_pre_org_id := ln_pre_id_null;
613 end if;
614
615 open get_yepp_pact_id(ln_business_group_id,
616 ln_pre_org_id,
617 ld_end_date);
618 fetch get_yepp_pact_id into ln_yepp_pact_id;
619 close get_yepp_pact_id;
620
621 hr_utility.trace('get_all_rl2_amend_dtls found ');
622 hr_utility.trace('Report Type: '||lv_report_type);
623
624 /* Create an assignment action for this person */
625
626 select pay_assignment_actions_s.nextval
627 into ln_rl2_amend_reg_asg_action
628 from dual;
629
630 hr_utility.trace('New RL2 Amend Action = ' || ln_rl2_amend_reg_asg_action);
631
632 /* Insert into pay_assignment_actions. */
636 p_chunk,
633 hr_nonrun_asact.insact(ln_rl2_amend_reg_asg_action,
634 ln_primary_assignment_id,
635 p_payroll_action_id,
637 ln_pre_org_id);
638
639 if ln_pre_parameter is NULL then
640 ln_pre_org_id := '';
641 end if;
642
643 /***********************************************************
644 ** Update the serial number column with Province_code QC,
645 ** Archiver assignment_action and Archiver Payroll_action_id
646 ** so that we need not refer back in the reports. This
647 ** logic works for both RL2 Amendment Register and RL2 Register
648 ** reports.
649 ***********************************************************/
650 ln_serial_number := 'QC'||
651 lpad(to_char(ln_asg_act_to_lock),14,0)||
652 lpad(to_char(ln_yepp_pact_id),14,0);
653
654 hr_utility.trace('Province: '||substr(ln_serial_number,1,2));
655 hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,3,14));
656 hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,17,14));
657
658 update pay_assignment_actions aa
659 set aa.serial_number = ln_serial_number
660 where aa.assignment_action_id = ln_rl2_amend_reg_asg_action;
661
662 end if; -- lv_amend_flag = 'Y' condition
663
664 end if; -- c_get_prov_amend_flag%found condition
665 close c_get_prov_amend_flag;
666
667 end loop; -- loop for get_all_rl2_amend_dtls
668 close get_all_rl2_amend_dtls;
669
670 end if; /* lv_print check i.e, mode */
671
672 END action_creation;
673
674 BEGIN
675 hr_utility.trace('Entered action_creation ');
676 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
677 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
678 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
679 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
680
681 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
682 ,p_start_date => ld_start_date
683 ,p_end_date => ld_end_date
684 ,p_business_group_id => ln_business_group_id
685 ,p_pre_org_id => ln_pre_parameter
686 ,p_person_id => ln_person_id
687 ,p_asg_set => ln_asg_set
688 ,p_print => lv_print
689 ,p_report_type => lv_ppr_report_type);
690
691 hr_utility.trace('lv_ppr_report_type: '||lv_ppr_report_type);
692
693 ln_pre_org_id := ln_pre_parameter;
694
695 /* PERSON ID IS NOT NULL */
696 if ln_person_id is not null then
697 action_creation(p_start_person_id);
698
699 elsif ln_asg_set is not null then
700
701 hr_utility.trace('Entered Asg Set logic');
702 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
703 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
704 hr_utility.trace('End Person ='||to_char(p_end_person_id));
705
706 open c_selected_asg_set (p_start_person_id
707 ,p_end_person_id
708 ,ln_asg_set
709 ,ld_end_date);
710 hr_utility.trace('Opened cusor c_selected_asg_set');
711 loop
712 fetch c_selected_asg_set into ln_set_person_id;
713 if c_selected_asg_set%notfound then
714 hr_utility.trace('c_selected_asg_set not found ');
715 hr_utility.trace('No Person found for reporting in this chunk');
716 exit;
717 end if;
718
719 action_creation(ln_set_person_id);
720
721 end loop;
722 close c_selected_asg_set;
723
724 elsif ln_pre_org_id is not null then
725
726 hr_utility.trace('Entered PRE not null logic');
727 hr_utility.trace('PRE Organization Id ='||to_char(ln_pre_org_id));
728 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
729 hr_utility.trace('End Person ='||to_char(p_end_person_id));
730
731 open c_all_asg (ln_business_group_id,
732 ln_pre_org_id,
733 ld_end_date,
734 p_start_person_id,
735 p_end_person_id);
736 hr_utility.trace('Opened cusor c_all_asg');
737
738 loop
739 fetch c_all_asg into ln_set_person_id;
740 if c_all_asg%notfound then
741 hr_utility.trace('c_all_asg not found ');
742 hr_utility.trace('No Person found for reporting in this chunk');
743 exit;
744 end if;
745
746 action_creation(ln_set_person_id);
747
748 end loop;
752
749 close c_all_asg;
750
751 else
753 hr_utility.trace('Entered All PRE logic');
754
755 open c_all_pres(ln_business_group_id,
756 ld_end_date);
757
758 loop -- c_all_pres
759
760 fetch c_all_pres into ln_pre_id;
761
762 if c_all_pres%NOTFOUND then
763 hr_utility.trace('c_all_pres NOT FOUND');
764 exit;
765 end if;
766
767 hr_utility.trace('PRE ='||to_char(ln_pre_id));
768 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
769 hr_utility.trace('End Person ='||to_char(p_end_person_id));
770
771 open c_all_asg (ln_business_group_id,
772 ln_pre_id,
773 ld_end_date,
774 p_start_person_id,
775 p_end_person_id);
776 hr_utility.trace('Opened cursor c_all_asg');
777
778 loop -- c_all_asg
779 fetch c_all_asg into ln_set_person_id;
780 if c_all_asg%notfound then
781 hr_utility.trace('c_all_asg not found ');
782 hr_utility.trace('No Person found for reporting in this chunk');
783 exit;
784 end if;
785
786 ln_pre_org_id := ln_pre_id;
787 action_creation(ln_set_person_id);
788
789 end loop; -- c_all_asg
790 close c_all_asg;
791
792 end loop; -- c_all_pres
793 close c_all_pres;
794
795 end if; /* ln_person_id */
796
797 END action_creation;
798
799
800 ---------------------------------- sort_action ------------------------------
801
802 PROCEDURE sort_action
803 (
804 payactid in varchar2,
805 sqlstr in out nocopy varchar2,
806 len out nocopy number
807 ) is
808
809 begin
810
811 hr_utility.trace('Beginning of the sort_action cursor');
812
813 sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
814 from hr_all_organization_units hou1,
815 hr_all_organization_units hou,
816 hr_locations_all loc,
817 per_all_people_f ppf,
818 per_all_assignments_f paf,
819 pay_assignment_actions paa1,
820 pay_payroll_actions ppa1
821 where ppa1.payroll_action_id = :pactid
822 and paa1.payroll_action_id = ppa1.payroll_action_id
823 and paa1.assignment_id = paf.assignment_id
824 and paf.business_group_id = ppa1.business_group_id
825 and ppa1.effective_date >= paf.effective_start_date
826 and hou.organization_id = paa1.tax_unit_id
827 and loc.location_id = paf.location_id
828 and hou1.organization_id = paf.organization_id
829 and ppf.person_id = paf.person_id
830 and ppa1.effective_date between
831 ppf.effective_start_date and ppf.effective_end_date
832 and paf.effective_end_date = (
833 select max(paaf2.effective_end_date)
834 from per_all_assignments_f paaf2
835 where paaf2.assignment_id = paf.assignment_id
836 and paaf2.effective_start_date <= ppa1.effective_date)
837 order by
838 decode(pay_ca_rl2_reg.get_parameter(''P_S1'',ppa1.legislative_parameters),
839 ''RL1_PRE'',hou.name,
840 ''RL1_ORG'',hou1.name,
841 ''RL1_LOC'',loc.location_code,null)
842 ,decode(pay_ca_rl2_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
843 ''RL1_PRE'',hou.name,
844 ''RL1_ORG'',hou1.name,
845 ''RL1_LOC'',loc.location_code,null)
846 ,decode(pay_ca_rl2_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
847 ''RL1_PRE'',hou.name,
848 ''RL1_ORG'',hou1.name,
849 ''RL1_LOC'',loc.location_code,null)
850 ,ppf.last_name,ppf.first_name';
851
852
853 len := length(sqlstr); -- return the length of the string.
854 hr_utility.trace('End of the sort_Action cursor');
855
856 end sort_action;
857
858
859 FUNCTION get_parameter(name in varchar2,
860 parameter_list varchar2) return varchar2
861 IS
862 start_ptr number;
863 end_ptr number;
864 token_val pay_payroll_actions.legislative_parameters%type;
865 par_value pay_payroll_actions.legislative_parameters%type;
866
867 BEGIN
868
869 token_val := name||'=';
870
871 start_ptr := instr(parameter_list, token_val) + length(token_val);
872 end_ptr := instr(parameter_list, ' ',start_ptr);
873
874
875 /* if there is no spaces use then length of the string */
876 if end_ptr = 0 then
877 end_ptr := length(parameter_list)+1;
878 end if;
879
880 /* Did we find the token */
881 if instr(parameter_list, token_val) = 0 then
882 par_value := NULL;
883 else
884 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
885 end if;
886
887 return par_value;
888
889 END get_parameter;
890 Begin
891 /* hr_utility.trace_on(null,'RL2AMEND_REG'); */
892 null;
893
894
895 end pay_ca_eoy_rl2_amend_reg;