[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.1 2006/10/11 20:43:46 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_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
42 *****************************************************************************/
43
44 gv_package VARCHAR2(100) := 'pay_ca_eoy_rl1_amend_arch';
45 gv_procedure_name VARCHAR2(100);
46
47
48 /*****************************************************************************
49 Name : get_payroll_action_info
50 Purpose : This returns the Payroll Action level
51 information for Provincial YE Amendment Archiver.
52 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
53 p_start_date - Start date of Archiver
54 p_end_date - End date of Archiver
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_pre_org_id out nocopy number
62 ,p_person_id out nocopy number
63 ,p_asg_set out nocopy number
64 ,p_year out nocopy varchar2)
65 IS
66 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
67 select to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
68 'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
69 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
70 'PER_ID',ppa.legislative_parameters)),
71 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
72 'SSN',ppa.legislative_parameters)),
73 to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
74 'ASG_SET',ppa.legislative_parameters)),
75 to_char(effective_date,'YYYY') ,
76 effective_date,
77 start_date,
78 business_group_id
79 from pay_payroll_actions ppa
80 where ppa.payroll_action_id = cp_payroll_action_id;
81
82 ld_end_date DATE;
83 ld_start_date DATE;
84 ln_business_group_id NUMBER;
85 ln_pre_org_id NUMBER := 0;
86 ln_person_id NUMBER := 0;
87 ln_asg_set NUMBER := 0;
88 lv_sin per_all_people_f.national_identifier%TYPE;
89 lv_year VARCHAR2(4);
90
91 BEGIN
92 hr_utility.trace('Entered get_payroll_action_info');
93 open c_payroll_action_info(p_payroll_action_id);
94 fetch c_payroll_action_info into ln_pre_org_id,
95 ln_person_id,
96 lv_sin,
97 ln_asg_set,
98 lv_year,
99 ld_end_date,
100 ld_start_date,
101 ln_business_group_id;
102 close c_payroll_action_info;
103
104 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
105 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
106 hr_utility.trace('ln_pre_org_id = ' || to_char(ln_pre_org_id));
107 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
108 hr_utility.trace('lv_sin = ' || lv_sin);
109 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
110 hr_utility.trace('lv_year = ' || lv_year);
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_pre_org_id := ln_pre_org_id;
116 p_person_id := ln_person_id;
117 p_asg_set := ln_asg_set;
118 p_year := lv_year;
119
120 hr_utility.trace('Leaving get_payroll_action_info');
121
122 EXCEPTION
123 when others then
124 hr_utility.trace('Error in ' || gv_procedure_name ||
125 to_char(sqlcode) || '-' || sqlerrm);
126 raise hr_utility.hr_error;
127
128 END get_payroll_action_info;
129
130
131
132 /******************************************************************
133 Name : eoy_range_cursor
134 Purpose : This returns the select statement that is
135 used to create the range rows for the
136 Provincial YE Amendment Pre-Process.
137 Arguments :
138 Notes : Calls procedure - get_payroll_action_info
139 ******************************************************************/
140 PROCEDURE eoy_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_pre_org_id NUMBER;
149 ln_person_id NUMBER := 0;
150 lv_sin per_all_people_f.national_identifier%TYPE ;
151 ln_asg_set NUMBER := 0;
152 lv_year VARCHAR2(4);
153 lv_sql_string VARCHAR2(32000);
154 ln_eoy_pactid number;
155 lv_record_name varchar2(80);
156 lv_message varchar2(100) := null;
157
158 BEGIN
159 hr_utility.trace('Entered eoy_range_cursor');
160 hr_utility.trace('p_payroll_action_id = ' ||
161 to_char(p_payroll_action_id));
162
163 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
164 ,p_start_date => ld_start_date
165 ,p_end_date => ld_end_date
166 ,p_business_group_id => ln_business_group_id
167 ,p_pre_org_id => ln_pre_org_id
168 ,p_person_id => ln_person_id
169 ,p_asg_set => ln_asg_set
170 ,p_year => lv_year);
171
172 if ln_person_id is not null then
173
174 lv_sql_string :=
175 'select distinct asg.person_id person_id
176 from per_all_assignments_f asg
177 where person_id = ' || to_char(ln_person_id) ||
178 ' and :p_payroll_action_id > 0';
179
180 hr_utility.trace('Range for person_id not null');
181
182 elsif ln_asg_set is not null then
183
184 lv_sql_string :=
185 'select distinct paf.person_id
186 from hr_assignment_set_amendments asgset,
187 per_all_assignments_f paf
188 where assignment_set_id = ' || to_char(ln_asg_set) || '
189 and asgset.assignment_id = paf.assignment_id
190 and asgset.include_or_exclude = ''I''
191 and :payroll_action_id > 0
192 order by paf.person_id';
193
194 hr_utility.trace('Range for asg_set not null');
195
196 else
197
198 lv_record_name := 'Provincial Amendment Pre-Process';
199 lv_message := 'No Employee or Assignment Set specified';
200
201 /* push message into pay_message_lines */
202 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
203 pay_core_utils.push_token('record_name',substr(lv_record_name,1,50));
204 pay_core_utils.push_token('name_or_number','');
205 pay_core_utils.push_token('description',substr(lv_message,1,50));
206
207 lv_sql_string :=
208 'select distinct asg.person_id person_id
209 from per_all_assignments_f asg
210 where person_id = 0
211 and :p_payroll_action_id > 0
212 order by asg.person_id ';
213
214 hr_utility.trace('No person is selected as ln_person_id and ln_asg_set are null');
215
216 end if;
217
218 p_sqlstr := lv_sql_string;
219 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
220
221 hr_utility.trace('Leaving eoy_range_cursor');
222 END eoy_range_cursor;
223
224
225 /************************************************************
226 Name : eoy_action_creation
227 Purpose : This creates the assignment actions for
228 a specific chunk of people to be archived
229 by the Provincial YE Amendment Pre-process.
230 Arguments :
231 Notes : Calls procedure - get_payroll_action_info
232 ************************************************************/
233
234 PROCEDURE eoy_action_creation(
235 p_payroll_action_id in number
236 ,p_start_person_id in number
237 ,p_end_person_id in number
238 ,p_chunk in number)
239
240 IS
241
242 ln_assignment_id NUMBER := 0;
243 ln_pre_org_id NUMBER := 0;
244 ld_effective_date DATE ;
245 ln_asg_action_id NUMBER := 0;
246 ln_primary_assignment_id NUMBER := 0;
247 ln_yepp_aaid NUMBER := 0;
248 ln_payroll_action_id NUMBER := 0;
249 ln_rl1amend_asg_action NUMBER := 0;
250 lv_year VARCHAR2(4);
251
252 ld_end_date DATE;
253 ld_start_date DATE;
254 ln_business_group_id NUMBER;
255 ln_person_id NUMBER := 0 ;
256 ln_person_id_sel NUMBER := 0 ;
257 lv_sin per_all_people_f.national_identifier%TYPE ;
258 ln_asg_set NUMBER := 0 ;
259 ln_prev_asg_action_id NUMBER := 0;
260 ln_prev_assignment_id NUMBER := 0;
261 ld_prev_effective_date DATE ;
262 lv_report_type pay_payroll_actions.report_type%TYPE ;
263 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
264
265
266 lv_serial_number VARCHAR2(30);
267 ln_eoy_pactid number := 0;
268 lv_national_identifier per_all_people_f.national_identifier%type;
269 lv_message varchar2(100):= null;
270 lv_full_name per_all_people_f.full_name%type;
271 lv_name varchar2(100);
272 lv_record_name varchar2(80);
273
274
275 CURSOR c_selected_asg_set(cp_start_person in number
276 ,cp_end_person in number
277 ,cp_asg_set in number) is
278 select distinct paf.person_id
279 from hr_assignment_set_amendments asgset,
280 per_all_assignments_f paf,
281 pay_assignment_actions paa,
282 pay_payroll_actions ppa
283 where asgset.assignment_set_id = cp_asg_set
284 and asgset.include_or_exclude = 'I'
285 and paf.assignment_id = asgset.assignment_id
286 and paf.person_id between cp_start_person
287 and cp_end_person
288 and ppa.report_type = 'RL1'
289 and to_char(ppa.effective_date,'YYYY') = lv_year
290 and ppa.business_group_id+0 = ln_business_group_id
291 and pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
292 ppa.legislative_parameters) = to_char(ln_pre_org_id)
293 and paa.payroll_action_id = ppa.payroll_action_id
294 and paa.action_status = 'C'
295 and paf.person_id = to_number(paa.serial_number);
296
297 PROCEDURE action_creation (p_person_id in NUMBER)
298 IS
299
300 CURSOR get_prev_rl1_amend_dtls (cp_person_id in number
301 ,cp_pre_org_id in number
302 ,cp_effective_date in date) is
303 select ppa.report_type,
304 paa.assignment_id,
305 paa.assignment_action_id
306 from pay_payroll_actions ppa,
307 pay_assignment_actions paa
308 where to_number(paa.serial_number) = cp_person_id
309 and pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
310 ppa.legislative_parameters) = to_char(cp_pre_org_id)
311 and paa.action_status = 'C'
312 and ppa.business_group_id+0 = ln_business_group_id
313 and ppa.payroll_action_id = paa.payroll_action_id
314 and ppa.effective_date = cp_effective_date
315 and ppa.report_type in ('RL1', 'CAEOY_RL1_AMEND_PP')
316 order by paa.assignment_action_id desc;
317
318 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
319 select substr(full_name,1,31), substr(national_identifier,1,11)
320 from per_all_people_f
321 where person_id = cp_person_id
322 ORDER BY effective_end_date desc;
323
324
325 BEGIN
326
327 /* Actions will be created if
328
329 #1 there is a YEPP action not followed by a
330 RL1 Amendment Archiver action
331
332 #2 there is already a Previous RL1 Amendment Archiver
333 action for this person
334 */
335
336 open get_prev_rl1_amend_dtls(p_person_id
337 ,ln_pre_org_id
338 ,ld_end_date);
339 fetch get_prev_rl1_amend_dtls into lv_report_type
340 ,ln_primary_assignment_id
341 ,ln_asg_act_to_lock;
342 hr_utility.trace('lv_report_type ='||lv_report_type);
343 hr_utility.trace('ln_primary_assignment_id ='||
344 to_char(ln_primary_assignment_id));
345 hr_utility.trace('ln_asg_act_to_lock ='||
346 to_char(ln_asg_act_to_lock));
347
348 if get_prev_rl1_amend_dtls%notfound then
349
353
350 hr_utility.trace('get_prev_rl1_amend_dtls Not Found');
351 hr_utility.trace('Warning Message Generated');
352 hr_utility.trace('p_person_id ='||to_char(p_person_id));
354 open get_warning_dtls_for_ee(p_person_id);
355 fetch get_warning_dtls_for_ee into lv_full_name
356 ,lv_national_identifier;
357
358 hr_utility.trace('lv_full_name ='||lv_full_name);
359 hr_utility.trace('lv_national_identifier ='||lv_national_identifier);
360
361 lv_name := lv_full_name || ', SIN '||lv_national_identifier;
362
363 close get_warning_dtls_for_ee;
364
365 lv_record_name := 'RL1 Amendment Pre-Process';
366 lv_message := 'Prov YE Preprocess was not run for this employee';
367
368 /* push message into pay_message_lines */
369 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
370 pay_core_utils.push_token('record_name',substr(lv_record_name,1,50));
371 pay_core_utils.push_token('name_or_number',substr(lv_name,1,50));
372 pay_core_utils.push_token('description',substr(lv_message,1,50));
373
374 end if;
375
376
377 if get_prev_rl1_amend_dtls%found then
378 hr_utility.trace('get_prev_rl1_amend_dtls Found');
379
380 if lv_report_type in ('RL1', 'CAEOY_RL1_AMEND_PP') then
381
382 /* Create an assignment action for this person */
383 select pay_assignment_actions_s.nextval
384 into ln_rl1amend_asg_action
385 from dual;
386 hr_utility.trace('New RL1 Amendment Action = ' ||
387 to_char(ln_rl1amend_asg_action));
388
389 /* Insert into pay_assignment_actions. */
390 -- hr_utility.trace('creating asg. action');
391 hr_nonrun_asact.insact(ln_rl1amend_asg_action
392 ,ln_primary_assignment_id
393 ,p_payroll_action_id
394 ,p_chunk
395 ,null);
396
397 /* Update the serial number column with the person id
398 so that the RL1 Amendment report will not have
399 to do an additional checking against the assignment
400 table */
401
402 -- hr_utility.trace('updating asg. action');
403
404 update pay_assignment_actions aa
405 set aa.serial_number = to_char(p_person_id)
406 where aa.assignment_action_id = ln_rl1amend_asg_action;
407
408 /* Interlock the YEPP or Previous RL1 Amendment Archiver
409 action for current RL1 Amendment Pre-process action */
410
411 hr_utility.trace('Locking Action = ' || to_char(ln_rl1amend_asg_action));
412 hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
413 hr_nonrun_asact.insint(ln_rl1amend_asg_action
414 ,ln_asg_act_to_lock);
415
416 end if; /* report type */
417
418 end if; /* employee found*/
419
420 close get_prev_rl1_amend_dtls;
421 hr_utility.trace('closed get_prev_rl1_amend_dtls');
422 END action_creation;
423
424 BEGIN
425 hr_utility.trace('Entered eoy_action_creation ');
426 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
427 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
428 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
429 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
430
431 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
432 ,p_start_date => ld_start_date
433 ,p_end_date => ld_end_date
434 ,p_business_group_id => ln_business_group_id
435 ,p_pre_org_id => ln_pre_org_id
436 ,p_person_id => ln_person_id
437 ,p_asg_set => ln_asg_set
438 ,p_year => lv_year);
439
440 /* PERSON ID IS NOT NULL */
441
442 if ln_person_id is not null then
443
444 hr_utility.trace('Entered PersonId logic');
445 action_creation(p_start_person_id);
446
447 elsif ln_asg_set is not null then
448
449 hr_utility.trace('Entered Asg Set logic');
450 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
451
452 ln_person_id_sel := 0;
453
454 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
455 hr_utility.trace('End Person ='||to_char(p_end_person_id));
456 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
457 hr_utility.trace('EOY Pactid ='||to_char(ln_eoy_pactid));
458
459 open c_selected_asg_set (p_start_person_id
460 ,p_end_person_id
461 ,ln_asg_set);
462 hr_utility.trace('Opened cusor c_selected_asg_set');
463 loop
464 fetch c_selected_asg_set into ln_person_id_sel ;
465 if c_selected_asg_set%notfound then
466 hr_utility.trace('No Person found for reporting in this chunk');
467 exit;
468 end if;
469
470 hr_utility.trace('ln_person_id after c_selected_asg_set = '||to_char(ln_person_id_sel));
471
472 action_creation(ln_person_id_sel);
473
474 end loop;
475
476 close c_selected_asg_set;
477
478 end if; /* ln_person_id or lv_sin is not null */
479
480 END eoy_action_creation;
481
482 FUNCTION get_parameter(name in varchar2,
483 parameter_list varchar2) return varchar2
484 is
485 start_ptr number;
486 end_ptr number;
487 token_val pay_payroll_actions.legislative_parameters%type;
488 par_value pay_payroll_actions.legislative_parameters%type;
489 begin
490
491 token_val := name||'=';
492
493 start_ptr := instr(parameter_list, token_val) + length(token_val);
494 end_ptr := instr(parameter_list, ' ',start_ptr);
495
496
497 /* if there is no spaces use then length of the string */
498 if end_ptr = 0 then
499 end_ptr := length(parameter_list)+1;
500 end if;
501
502 /* Did we find the token */
503 if instr(parameter_list, token_val) = 0 then
504 par_value := NULL;
505 else
506 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
507 end if;
508
509 return par_value;
510
511 end get_parameter;
512
513 Begin
514 --hr_utility.trace_on(null,'RL1AMENDPP');
515 null;
516
517 end pay_ca_eoy_rl1_amend_arch;