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