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