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