1 PACKAGE BODY pay_ca_eoy_t4a_amend_arch AS
2 /* $Header: pycat4aca.pkb 120.1 2006/10/11 20:40:13 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_arch
21
22 Description : This procedure is used by T4A Amendment Pre-Process
23 to archive data for T4A Amendment Paper Report.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 25-AUG-2004 SSouresr 115.0 Created.
30 05-MAR-2005 SSouresr 115.1 Removed extra p_payroll_action_id
31 from the range cursor
32 11-OCT-2006 meshah 115.2 5528944 Added order by clause in the range
33 cursor queries.
34 *****************************************************************************/
35
36 gv_package VARCHAR2(100) := 'pay_ca_eoy_t4a_amend_arch';
37 gv_procedure_name VARCHAR2(100);
38
39
40 /*****************************************************************************
41 Name : get_payroll_action_info
42 Purpose : This returns the Payroll Action level
43 information for Federal YE Amendment Archiver.
44 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
45 p_start_date - Start date of Archiver
46 p_end_date - End date of Archiver
47 p_business_group_id - Business Group ID
48 ******************************************************************************/
49 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
50 ,p_end_date out nocopy date
51 ,p_start_date out nocopy date
52 ,p_business_group_id out nocopy number
53 ,p_tax_unit_id out nocopy number
54 ,p_person_id out nocopy number
55 ,p_asg_set out nocopy number
56 ,p_year out nocopy varchar2)
57 IS
58 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
59 select to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
60 'TRANSFER_GRE',ppa.legislative_parameters)),
61 to_number(pay_ca_eoy_t4a_amend_arch.get_parameter(
62 'PER_ID',ppa.legislative_parameters)),
63 to_number(pay_ca_eoy_t4a_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_tax_unit_id NUMBER := 0;
76 ln_person_id NUMBER := 0;
77 ln_asg_set NUMBER := 0;
78 lv_year VARCHAR2(4);
79
80 BEGIN
81 hr_utility.trace('Entered get_payroll_action_info');
82
83 open c_payroll_action_info(p_payroll_action_id);
84 fetch c_payroll_action_info into ln_tax_unit_id,
85 ln_person_id,
86 ln_asg_set,
87 lv_year,
88 ld_end_date,
89 ld_start_date,
90 ln_business_group_id;
91 close c_payroll_action_info;
92
93 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
94 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
95 hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
96 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
97 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
98 hr_utility.trace('lv_year = ' || lv_year);
99
100 p_end_date := ld_end_date;
101 p_start_date := ld_start_date;
102 p_business_group_id := ln_business_group_id;
103 p_tax_unit_id := ln_tax_unit_id;
104 p_person_id := ln_person_id;
105 p_asg_set := ln_asg_set;
106 p_year := lv_year;
107
108 hr_utility.trace('Leaving get_payroll_action_info');
109
110 EXCEPTION
111 when others then
112 hr_utility.trace('Error in ' || gv_procedure_name ||
113 to_char(sqlcode) || '-' || sqlerrm);
114 raise hr_utility.hr_error;
115
116 END get_payroll_action_info;
117
118
119
120 /******************************************************************
121 Name : eoy_range_cursor
122 Purpose : This returns the select statement that is
123 used to create the range rows for the
124 Federal YE Amendment Pre-Process.
125 Arguments :
126 Notes : Calls procedure - get_payroll_action_info
127 ******************************************************************/
128 PROCEDURE eoy_range_cursor(
129 p_payroll_action_id in number
130 ,p_sqlstr out nocopy varchar2)
131 IS
132
133 ld_end_date DATE;
134 ld_start_date DATE;
135 ln_business_group_id NUMBER;
136 ln_tax_unit_id NUMBER;
137 ln_person_id NUMBER := 0;
138 ln_asg_set NUMBER := 0;
139 lv_year VARCHAR2(4);
140 lv_sql_string VARCHAR2(32000);
141 lv_record_name varchar2(80);
142 lv_message varchar2(100) := null;
143
144 BEGIN
145 hr_utility.trace('Entered eoy_range_cursor');
146 hr_utility.trace('p_payroll_action_id = ' ||
147 to_char(p_payroll_action_id));
148
149 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
150 ,p_start_date => ld_start_date
151 ,p_end_date => ld_end_date
152 ,p_business_group_id => ln_business_group_id
153 ,p_tax_unit_id => ln_tax_unit_id
154 ,p_person_id => ln_person_id
155 ,p_asg_set => ln_asg_set
156 ,p_year => lv_year);
157
158 if ln_person_id is not null then
159
160 lv_sql_string :=
161 'select distinct asg.person_id person_id
162 from per_all_assignments_f asg
163 where person_id = ' || to_char(ln_person_id) ||
164 ' and :p_payroll_action_id > 0';
165
166 hr_utility.trace('Range for person_id not null');
167
168 elsif ln_asg_set is not null then
169
170 lv_sql_string :=
171 'select distinct paf.person_id
172 from hr_assignment_set_amendments asgset,
173 per_all_assignments_f paf
174 where assignment_set_id = ' || to_char(ln_asg_set) || '
175 and asgset.assignment_id = paf.assignment_id
176 and asgset.include_or_exclude = ''I''
177 and :payroll_action_id > 0
178 order by paf.person_id ';
179
180 hr_utility.trace('Range for asg_set not null');
181
182 else
183
184 lv_record_name := 'Federal Amendment Pre-Process';
185 lv_message := 'No Employee or Assignment Set specified';
186
187 /* push message into pay_message_lines */
188 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
189 pay_core_utils.push_token('record_name',substr(lv_record_name,1,50));
190 pay_core_utils.push_token('name_or_number','');
191 pay_core_utils.push_token('description',substr(lv_message,1,50));
192
193 lv_sql_string :=
194 'select distinct asg.person_id person_id
195 from per_all_assignments_f asg
196 where person_id = 0
197 and :p_payroll_action_id > 0
198 order by asg.person_id ';
199
200 hr_utility.trace('No person selected as ln_person_id and ln_asg_set are null');
201
202 end if;
203
204 p_sqlstr := lv_sql_string;
205 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
206
207 hr_utility.trace('Leaving eoy_range_cursor');
208
209 END eoy_range_cursor;
210
211
212 /************************************************************
213 Name : eoy_action_creation
214 Purpose : This creates the assignment actions for
215 a specific chunk of people to be archived
216 by the Federal YE Amendment Pre-process.
217 Arguments :
218 Notes : Calls procedure - get_payroll_action_info
219 ************************************************************/
220
221 PROCEDURE eoy_action_creation(
222 p_payroll_action_id in number
223 ,p_start_person_id in number
224 ,p_end_person_id in number
225 ,p_chunk in number)
226
227 IS
228
229 ln_assignment_id NUMBER := 0;
230 ln_tax_unit_id NUMBER := 0;
231 ld_effective_date DATE;
232 ln_asg_action_id NUMBER := 0;
233 ln_primary_assignment_id NUMBER := 0;
234 ln_yepp_aaid NUMBER := 0;
235 ln_payroll_action_id NUMBER := 0;
236 ln_t4a_amend_asg_action NUMBER := 0;
237 lv_year VARCHAR2(4);
238
239 ld_end_date DATE;
240 ld_start_date DATE;
241 ln_business_group_id NUMBER;
242 ln_person_id NUMBER := 0;
243 ln_person_id_sel NUMBER := 0;
244 ln_asg_set NUMBER := 0;
245 ln_prev_asg_action_id NUMBER := 0;
246 ln_prev_assignment_id NUMBER := 0;
247 ln_prev_tax_unit_id NUMBER := 0;
248 ld_prev_effective_date DATE;
249 lv_report_type pay_payroll_actions.report_type%TYPE;
250 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
251
252
253 lv_serial_number VARCHAR2(30);
254 lv_national_identifier per_all_people_f.national_identifier%type;
255 lv_message varchar2(100):= null;
256 lv_full_name per_all_people_f.full_name%type;
257 lv_name varchar2(100);
258 lv_record_name varchar2(80);
259
260
261 CURSOR c_selected_asg_set(cp_start_person in number
262 ,cp_end_person in number
263 ,cp_asg_set in number) is
264 select distinct paf.person_id
265 from hr_assignment_set_amendments asgset,
266 per_all_assignments_f paf,
267 pay_assignment_actions paa,
268 pay_payroll_actions ppa
269 where asgset.assignment_set_id = cp_asg_set
270 and asgset.include_or_exclude = 'I'
271 and paf.assignment_id = asgset.assignment_id
272 and paf.person_id between cp_start_person
273 and cp_end_person
274 and ppa.report_type = 'T4A'
275 and to_char(ppa.effective_date,'YYYY') = lv_year
276 and ppa.business_group_id+0 = ln_business_group_id
277 and to_number(pay_ca_eoy_t4a_amend_arch.get_parameter('TRANSFER_GRE',
278 ppa.legislative_parameters)) = ln_tax_unit_id
279 and paa.payroll_action_id = ppa.payroll_action_id
280 and paa.action_status = 'C'
281 and paf.person_id = to_number(paa.serial_number);
282
283
284 PROCEDURE action_creation (p_person_id in NUMBER)
285 IS
286
287 CURSOR get_prev_t4a_amend_dtls(cp_person_id in number
288 ,cp_tax_unit_id in number
289 ,cp_effective_date in date) is
290 select ppa.report_type,
291 paa.assignment_id,
292 paa.assignment_action_id
293 from pay_payroll_actions ppa,
294 pay_assignment_actions paa
295 where paa.tax_unit_id = cp_tax_unit_id
296 and paa.action_status = 'C'
297 and ppa.business_group_id+0 = ln_business_group_id
298 and ppa.payroll_action_id = paa.payroll_action_id
299 and ppa.effective_date = cp_effective_date
300 and ppa.report_type in ('T4A', 'CAEOY_T4A_AMEND_PP')
301 and to_number(pay_ca_eoy_t4a_amend_arch.get_parameter('TRANSFER_GRE',
302 ppa.legislative_parameters)) = cp_tax_unit_id
303 and to_number(paa.serial_number) = cp_person_id
304 order by paa.assignment_action_id desc;
305
306 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
307 select substr(full_name,1,31), substr(national_identifier,1,11)
308 from per_all_people_f
309 where person_id = cp_person_id
310 ORDER BY effective_end_date desc;
311
312 BEGIN
313
314 /* Actions will be created if
315
319 #2 there is already a Previous T4A Amendment Archiver
316 #1 there is a YEPP action not followed by a
317 T4A Amendment Archiver action
318
320 action for this person
321 */
322
323 open get_prev_t4a_amend_dtls(p_person_id
324 ,ln_tax_unit_id
325 ,ld_end_date);
326
327 fetch get_prev_t4a_amend_dtls into lv_report_type
328 ,ln_primary_assignment_id
329 ,ln_asg_act_to_lock;
330
331 hr_utility.trace('lv_report_type ='||lv_report_type);
332 hr_utility.trace('ln_primary_assignment_id ='||
333 to_char(ln_primary_assignment_id));
334 hr_utility.trace('ln_asg_act_to_lock ='||
335 to_char(ln_asg_act_to_lock));
336
337 if get_prev_t4a_amend_dtls%notfound then
338
339 hr_utility.trace('get_prev_t4a_amend_dtls Not Found');
340 hr_utility.trace('Warning Message Generated');
341 hr_utility.trace('p_person_id ='||to_char(p_person_id));
342
343 open get_warning_dtls_for_ee(p_person_id);
344 fetch get_warning_dtls_for_ee into lv_full_name
345 ,lv_national_identifier;
346
347 hr_utility.trace('lv_full_name ='||lv_full_name);
348 hr_utility.trace('lv_national_identifier ='||lv_national_identifier);
349
350 lv_name := lv_full_name || ', SIN '||lv_national_identifier;
351
352 close get_warning_dtls_for_ee;
353
354 lv_record_name := 'T4A Amendment Pre-Process';
355 lv_message := 'Federal YE Preprocess was not run for this employee';
356
357 /* push message into pay_message_lines */
358 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
359 pay_core_utils.push_token('record_name',substr(lv_record_name,1,50));
360 pay_core_utils.push_token('name_or_number',substr(lv_name,1,50));
361 pay_core_utils.push_token('description',substr(lv_message,1,50));
362
363 end if;
364
365
366 if get_prev_t4a_amend_dtls%found then
367 hr_utility.trace('get_prev_t4a_amend_dtls Found');
368
369 if lv_report_type in ('T4A', 'CAEOY_T4A_AMEND_PP') then
370
371 /* Create an assignment action for this person */
372 select pay_assignment_actions_s.nextval
373 into ln_t4a_amend_asg_action
374 from dual;
375
376 hr_utility.trace('New T4A Amendment Action = ' ||
377 to_char(ln_t4a_amend_asg_action));
378
379 /* Insert into pay_assignment_actions. */
380 hr_utility.trace('creating assignment action');
381 hr_nonrun_asact.insact(ln_t4a_amend_asg_action
382 ,ln_primary_assignment_id
383 ,p_payroll_action_id
384 ,p_chunk
385 ,ln_tax_unit_id);
386
387 /* Update the serial number column with the person id
388 so that the T4A Amendment report will not have
389 to do an additional checking against the assignment
390 table */
391
392 hr_utility.trace('updating assignment 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_t4a_amend_asg_action;
397
398 /* Interlock the YEPP or Previous T4A Amendment Archiver
399 action for current T4A Amendment Pre-process action */
400
401 hr_utility.trace('Locking Action = ' || to_char(ln_t4a_amend_asg_action));
402 hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
403 hr_nonrun_asact.insint(ln_t4a_amend_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_t4a_amend_dtls;
411 hr_utility.trace('closed get_prev_t4a_amend_dtls');
412 END action_creation;
413
414 BEGIN
415 hr_utility.trace('Entered eoy_action_creation ');
416 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
417 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
418 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
419 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
420
421 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
422 ,p_start_date => ld_start_date
423 ,p_end_date => ld_end_date
424 ,p_business_group_id => ln_business_group_id
425 ,p_tax_unit_id => ln_tax_unit_id
426 ,p_person_id => ln_person_id
427 ,p_asg_set => ln_asg_set
428 ,p_year => lv_year);
429
430 /* PERSON ID IS NOT NULL */
431
432 if ln_person_id is not null then
433
434 hr_utility.trace('Entered Person Id logic');
435 action_creation(p_start_person_id);
436
437 elsif ln_asg_set is not null then
438
439 hr_utility.trace('Entered Asg Set logic');
440 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
441
442 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
443 hr_utility.trace('End Person ='||to_char(p_end_person_id));
444 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
445
446 open c_selected_asg_set(p_start_person_id
447 ,p_end_person_id
448 ,ln_asg_set);
449
450 hr_utility.trace('Opened cusor c_selected_asg_set');
451
452 loop
453
454 fetch c_selected_asg_set into ln_person_id_sel;
455
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 --hr_utility.trace_on(null,'T4AAMENDPP');
506 null;
507
508
509
510 end pay_ca_eoy_t4a_amend_arch;
[Home] [Help]