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