[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_MOVDED_EDI
Source
1 PACKAGE BODY PAY_GB_MOVDED_EDI as
2 /* $Header: pygbmedi.pkb 120.19.12010000.2 2008/08/06 07:17:29 ubhat ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name
9 PAY_GB_MOVDED_EDI
10 Purpose
11 Package to control archiver process in the creation of assignment actions
12 and the creation of EDI Message files uing the magtape process for EDI
13 Message Types : P45(3), P46, P46PENNOT
14 This is a UK Specific payroll package.
15 Notes
16
17 History
18 10-OCT-2000 S.Robinson 115.0 Date Created.
19 06-NOV-2002 BTHAMMIN 115.1 Bug 2657976
20 A new procedure get_job_segment
21 is added.
22 09-DEC-2002 BTHAMMIN 115.2 Check for enabled and displayed
23 segments.
24 23-DEC-2002 NSUGAVAN 115.3 To be R8.0 compliant, commented out
25 function get_job_segment as it has been
26 moved to a different file(pygbjseg.pkh)
27 24-DEC-2002 AMILLS 115.4 Bug 2677022. Performance fix for three
28 c_state cursors.
29 23-DEC-2002 NSUGAVAN 115.5 Removed commented code.
30 21-MAY-2003 ASENGAR 115.6 Changed the cursor c_state of the
31 procedure p46_action_creation for picking
32 assignments on basis on P46_SEND_EDI_FLAG
33 and P46_SEND_EDI for Bug 1843915.
34 27-AUG-2003 SSEKHAR 115.7 Bug 1843915: Changed the cursor c_state of
35 p45_3_action_creation to pick assignments
36 on basis of P45_3_SEND_EDI_FLAG and
37 P45_3_SEND_EDI. Also the cursor c_state of
38 p46_pennot_action_creation has been changed
39 to pick up assignments based on
40 P46_PENNOT_SEND_EDI_FLAG and
41 P46_PENNOT_SEND_EDI
42 07-OCT-2004 ALIKHAR 115.8 Bug 3891351: Changed the cursors c_state of
43 p45_3_action_creation, p46_action_creation
44 and p46_pennot_action_creation to join
45 assignment table with period of service
46 table based on period_of_service_id column.
47 16-JUN-2006 KTHAMPAN 115.9 Code change for EDI Rollback.
48 23-JUN-2006 KTHAMPAN 115.10 Not to create asg action if no rows is found
49 on the per_assignment_extra_info table
50 (internal_action_creation)
51 29-JUN-2006 KTHAMPAN 115.11 Fix gscc error.
52 16-AUG-2006 TUKUMAR 115.12 Bug 5469122 : changed csr_student_loan in
53 function fetch_p45_3_rec
54 21-NOV-2006 TUKUMAR 115.13 Bug 5660011 : changed function fetch_tax_rec
55 31-Oct-2007 ABHGANGU 115.14 6345375 UK EOY Changes..
56 Added
57 fetch_45_46_pennot_rec
58 ,p46_5_pennot_action_creation
59 ,p45pt_3_action_creation
60 ,date_validate
61 Modified
62 archive_code
63 1-NOV-07 PARUSIA 115.15 Added check in range_cursor to log
64 error in Logfile for P45PT3 if Test Submission
65 is Yes and Test_ID is not provided. It also
66 raises an unhandled exception
67 1-NOV-07 PARUSIA 115.16 Added the same check of Test_ID for
68 P46_Pennot too.
69 19-Nov-2007 ABHGANGU 115.17 6345375 Added fetch_p46_5_rec,p46_5_action_creation
70 Changed internal_action_creation for P46 EDI
71 19-Nov-2007 PARUSIA 115.19 6345375 Changed fetch_p45_3_rec
72 to archive continue_student_loan_deductions
73 12-Dec-2007 PARUSIA 115.20 6643668 In fetch_tax_rec, commented the code
74 which was getting tax data from
75 run result values, prior to element element
76 values. Tax data should be picked only
77 from element entry values only irrespective
78 of run results. Also commented the check
79 in csr_paye_details of link's max effective
80 date.
81 27-Dec-2007 rlingama 115.23 6710197 Added function get_territory_short_name for
82 fetching country name for the country code
83 30-Jan-2007 parusia 115.24 6770200 Changed check_action to check if the employee
84 has already been processed by the old process
85 also. Like for P45PT3, check for P45(3) (old
86 process) also.
87 05-Feb-2007 apmishra 115.24 6652235 P46 Car Edi enhancement.
88 8-feb-2008 parusia 115.26 6804206 Corrected the l_tax_year_start conversion line.
89 8-feb-2008 parusia 115.27 6804206 Corrected the l_tax_year_start conversion line.
90 30-Apr-2008 rlingama 115.28 6994632 P45(3) minor enhancements for UK EOY Changes APR08
91 ==============================================================================*/
92 --
93 --
94 TYPE act_info_rec IS RECORD
95 ( assignment_id number(20)
96 ,person_id number(20)
97 ,effective_date date
98 ,action_info_category varchar2(50)
99 ,act_info1 varchar2(300)
100 ,act_info2 varchar2(300)
101 ,act_info3 varchar2(300)
102 ,act_info4 varchar2(300)
103 ,act_info5 varchar2(300)
104 ,act_info6 varchar2(300)
105 ,act_info7 varchar2(300)
106 ,act_info8 varchar2(300)
107 ,act_info9 varchar2(300)
108 ,act_info10 varchar2(300)
109 ,act_info11 varchar2(300)
110 ,act_info12 varchar2(300)
111 ,act_info13 varchar2(300)
112 ,act_info14 varchar2(300)
113 ,act_info15 varchar2(300)
114 ,act_info16 varchar2(300)
115 ,act_info17 varchar2(300)
116 ,act_info18 varchar2(300)
117 ,act_info19 varchar2(300)
118 ,act_info20 varchar2(300)
119 ,act_info21 varchar2(300)
120 ,act_info22 varchar2(300)
121 ,act_info23 varchar2(300)
122 ,act_info24 varchar2(300)
123 ,act_info25 varchar2(300)
124 ,act_info26 varchar2(300)
125 ,act_info27 varchar2(300)
126 ,act_info28 varchar2(300)
127 ,act_info29 varchar2(300)
128 ,act_info30 varchar2(300)
129 );
130
131 TYPE action_info_table IS TABLE OF
132 act_info_rec INDEX BY BINARY_INTEGER;
133
134 TYPE g_tax_rec IS RECORD(
135 tax_code VARCHAR2(20),
136 tax_basis VARCHAR2(20),
137 prev_paid VARCHAR2(20),
138 prev_tax VARCHAR2(20));
139
140 g_package CONSTANT VARCHAR2(20):= 'pay_gb_movded_edi.';
141 --
142 --
143 /*------------- PRIVATE PROCEDURE -----------------*/
144 --
145 --
146 FUNCTION validate_data(p_value in varchar2,
147 p_name in varchar2,
148 p_mode in varchar2) return boolean IS
149 l_proc CONSTANT VARCHAR2(50):= g_package||'validate_data';
150 BEGIN
151 hr_utility.set_location('Entering: '||l_proc,1);
152 if pay_gb_eoy_magtape.validate_input(UPPER(p_value),p_mode) > 0 then
153 hr_utility.set_location('Name/Value : ' || p_name || '/' || p_value ,10);
154 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
155 pay_core_utils.push_token('INPUT_NAME', p_name);
156 pay_core_utils.push_token('INPUT_VALUE', p_value);
157 return false;
158 end if;
159 hr_utility.set_location('Leaving: '||l_proc,999);
160 return true;
161 END validate_data;
162 --
163 --
164 FUNCTION check_action(p_mode varchar2,
165 p_assignment_id number) RETURN boolean
166 IS
167 l_proc CONSTANT VARCHAR2(50):= g_package||'check_action';
168 l_action number;
169 l_ret boolean;
170
171 cursor csr_check_action(l_mode1 varchar2, l_mode2 varchar2) is
172 select 1
173 from pay_payroll_actions pay,
174 pay_assignment_actions paa
175 where (pay.report_type like l_mode1
176 or
177 pay.report_type like l_mode2) -- Bug 6770200.
178 and pay.action_status ='C'
179 and pay.report_qualifier = 'GB'
180 and pay.report_category = 'EDI'
181 and pay.payroll_action_id = paa.payroll_action_id
182 and paa.action_status = 'C'
183 and paa.assignment_id = p_assignment_id;
184
185 l_mode1 varchar2(15) ;
186 l_mode2 varchar2 (15) ;
187 BEGIN
188 l_ret := true;
189 hr_utility.set_location('Entering: '||l_proc,1);
190
191 -- Bug 6770200
192 -- Additional modes added. Like while checking for P45PT3,
193 -- also check if the employee has been picked by a P45PT3(new) or
194 -- P45(3) - pre 06-Apr-08 process. Similar checks done for P46 and Pennot
195 -- also.
196 if p_mode = 'P45PT_3' or p_mode = 'P45_3'then
197 l_mode1 := 'P45_3' ;
198 l_mode2 := 'P45PT_3' ;
199 elsif p_mode = 'P45PT1' or p_mode = 'P45' then
200 l_mode1 := 'P45' ;
201 l_mode2 := 'P45PT1' ;
202 elsif p_mode = 'P46_5' or p_mode = 'P46' then
203 l_mode1 := 'P46' ;
204 l_mode2 := 'P46_5';
205 elsif p_mode = 'P46_PENNOT' or p_mode = 'P46_5_PENNOT' then
206 l_mode1 := 'P46_PENNOT';
207 l_mode2 := 'P46_5_PENNOT' ;
208 else
209 l_mode1 := p_mode ;
210 l_mode2 := p_mode ;
211 end if ;
212
213 open csr_check_action(l_mode1, l_mode2);
214 fetch csr_check_action into l_action;
215 if csr_check_action%FOUND then
216 hr_utility.set_location('Assignment action complete',5);
217 l_ret := false;
218 end if;
219 close csr_check_action;
220 hr_utility.set_location('Leaving: '||l_proc,999);
221 return l_ret;
222 END;
223 --
224 --
225 PROCEDURE reset_flag(p_type varchar2,
226 p_assact number)
227 IS
228 l_proc CONSTANT VARCHAR2(50):= g_package||'reset_flag';
229 l_ovn number;
230
231 cursor csr_aei_details is
232 select aei.assignment_extra_info_id,
233 aei.object_version_number,
234 aei.aei_information1
235 from pay_assignment_actions paa,
236 per_assignment_extra_info aei
237 where paa.assignment_action_id = p_assact
238 and aei.assignment_id = paa.assignment_id
239 and aei.information_type = p_type;
240
241 l_aei_rec csr_aei_details%rowtype;
242 BEGIN
243 open csr_aei_details;
244 fetch csr_aei_details into l_aei_rec;
245 close csr_aei_details;
246
247 if l_aei_rec.aei_information1 = 'N' then
248 hr_assignment_extra_info_api.update_assignment_extra_info
249 (p_validate => false,
250 p_object_version_number => l_ovn,
251 p_assignment_extra_info_id => l_aei_rec.assignment_extra_info_id,
252 p_aei_information_category => p_type,
253 p_aei_information1 => 'Y');
254 end if;
255 END;
256 --
257 --
258 PROCEDURE internal_action_creation(pactid in number,
259 stperson in number,
260 endperson in number,
261 chunk in number,
262 p_info_type in varchar2,
263 p_rep_type in varchar2)
264 IS
265 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
266 l_payroll_id number;
267 l_business_group_id number;
268 l_ass_act_id number;
269 l_assignment_id number;
270 l_effective_date date;
271 l_arch boolean;
272 l_send_flag varchar2(2);
273 l_static_flag varchar2(2);
274 l_tax_ref varchar2(20);
275 /**** for p46_5 ***/
276 l_def_send_flag varchar2(2);
277 l_def_static_flag varchar2(2);
278 l_reason varchar2(2);
279 l_p45_not_run boolean;
280 l_p46_5_def number; /**** l_p46_5_def : 0 -> no default to be run; 1 -> default for P46_5 to be run; 2 -> default has been run,now the normal P46_5 *****/
281 l_locked_action_id number;
282 l_exist number;
283 cursor csr_parameter_info is
284 select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
285 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
286 effective_date,
287 business_group_id
288 from pay_payroll_actions
289 where payroll_action_id = pactid;
290
291 cursor csr_asg is
292 select asg.assignment_id
293 from per_all_people_f pap,
294 per_assignments_f asg,
295 per_periods_of_service serv,
296 pay_all_payrolls_f pay,
297 hr_soft_coding_keyflex sck
298 where pap.person_id between stperson and endperson
299 and pap.current_employee_flag = 'Y'
300 and pap.person_id = asg.person_id
301 and asg.business_group_id = l_business_group_id
302 and asg.payroll_id = pay.payroll_id
303 and asg.period_of_service_id = serv.period_of_service_id
304 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
305 and upper(l_tax_ref) = upper(sck.segment1)
306 and (l_payroll_id IS NULL
307 or
308 l_payroll_id = pay.payroll_id)
309 and serv.date_start <= l_effective_date
310 and l_effective_date between asg.effective_start_date and asg.effective_end_date
311 and l_effective_date between pap.effective_start_date and pap.effective_end_date
312 and l_effective_date between pay.effective_start_date and pay.effective_end_date;
313
314 cursor csr_aei_flag(p_assignment_id number) is
315 select aei_information1,
316 decode(p_info_type,'GB_P45_3', aei_information8
317 ,'GB_P46PENNOT', aei_information4
318 ,'GB_P46', aei_information3)
319 from per_assignment_extra_info
320 where assignment_id = p_assignment_id
321 and information_type = p_info_type;
322
323 cursor csr_p46_5_default(p_assignment_id number) is /*** open this cursor only for rep_type=P46_5 ***/
324 select aei_information5,
325 aei_information6
326 from per_assignment_extra_info
327 where assignment_id = p_assignment_id
328 and information_type = p_info_type;
329
330 cursor csr_p46_5_def_det(p_assignment_id number,default_archive varchar2)
331 is
332 select 1
333 from pay_action_information pa
334 ,pay_payroll_actions ppa
335 ,pay_assignment_actions paa
336 where pa.action_information_category in 'GB P46_5 EDI'
337 and pa.action_context_type = 'AAP'
338 and pa.action_information4 = default_archive
339 and pa.assignment_id = p_assignment_id
340 and paa.assignment_action_id = pa.action_context_id
341 and ppa.payroll_action_id = paa.payroll_action_id
342 and ppa.action_status = 'C';
343
344 cursor csr_p46_5_def_assact(p_assignment_id number) is
345 select act.assignment_action_id
346 from pay_payroll_actions pact,
347 pay_assignment_actions act
348 where pact.report_type = 'P46_5'
349 and pact.action_status ='C'
350 and pact.report_qualifier = 'GB'
351 and pact.report_category = 'EDI'
352 and pact.payroll_action_id = act.payroll_action_id
353 and act.action_status = 'C'
354 and act.assignment_id = p_assignment_id;
355
356 BEGIN
357 hr_utility.set_location('Entering: '||l_proc,1);
358 open csr_parameter_info;
359 fetch csr_parameter_info into l_payroll_id,
360 l_tax_ref,
361 l_effective_date,
362 l_business_group_id;
363 close csr_parameter_info;
364
365 hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
366 for asg_rec in csr_asg loop
367 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
368 l_arch := false;
369 l_p46_5_def := 0;
370 open csr_aei_flag(asg_rec.assignment_id);
371 fetch csr_aei_flag into l_send_flag, l_static_flag;
372
373 -- only create asg action if rows is found
374 if csr_aei_flag%FOUND then
375 hr_utility.set_location('\n l_send_flag = ' || l_send_flag || ' l_static_flag = ' || l_static_flag,20);
376 if p_rep_type = 'P46_5' then
377 l_p45_not_run := check_action('P45%3', asg_rec.assignment_id);
378 if l_p45_not_run then
379 open csr_p46_5_default(asg_rec.assignment_id);
380 fetch csr_p46_5_default into l_def_send_flag,l_def_static_flag;
381 close csr_p46_5_default;
382 /*** checking if the default is to be run *****/
383 if l_def_send_flag = 'Y' then
384 l_arch := true;
385 l_p46_5_def := 1;
386 else
387 if l_def_send_flag = 'N' and l_def_static_flag = 'Y' then
388 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
389 if l_arch then
390 l_p46_5_def := 1; /** if def not archived then archive ***/
391 else
392 l_p46_5_def := 0;
393 end if;
394 end if;
395 end if;
396 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def)||l_def_send_flag||l_def_static_flag||l_send_flag||l_static_flag||asg_rec.assignment_id);
397 /**** checking if the default has been run or not enabled to run ****/
398 if l_p46_5_def = 0 and nvl(l_def_send_flag,'N') = 'N' /*and nvl(l_def_static_flag,'Y') = 'Y'*/ then
399 if l_send_flag = 'Y' then
400 l_arch := true;
401 l_p46_5_def := 2; /**** diff b/n normal run for other report types and P46_5 normal run ***/
402 else
403 if l_send_flag = 'N' and l_static_flag = 'Y' then /** checking if P46 normal is rolled back ***/
404 fnd_file.put_line(fnd_file.LOG,'11111');
405 open csr_p46_5_def_det(asg_rec.assignment_id,'N');
406 fetch csr_p46_5_def_det into l_exist;
407 if csr_p46_5_def_det%notfound then
408 -- Bug 6770200
409 l_arch := check_action('P46_5', asg_rec.assignment_id);
410 end if;
411 close csr_p46_5_def_det;
412 if l_arch then
413 l_p46_5_def := 2;
414 end if;
415 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def));
416 end if;
417 end if;
418 end if;
419 /*else
420 l_reason := 'X'; */
421 end if;
422 else
423 if l_send_flag = 'Y' then
424 l_arch := true;
425 else
426 if l_send_flag = 'N' and l_static_flag = 'Y' then
427 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
428 end if;
429 end if;
430 end if;
431 if l_arch then
432 hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
433 select pay_assignment_actions_s.nextval
434 into l_ass_act_id
435 from dual;
436 --
437 -- insert into pay_assignment_actions.
438 hr_nonrun_asact.insact(l_ass_act_id,
439 asg_rec.assignment_id,
440 pactid,
441 chunk,
442 null);
443
444 if l_p46_5_def = 2 then
445 open csr_p46_5_def_assact(asg_rec.assignment_id);
446 fetch csr_p46_5_def_assact into l_locked_action_id;
447 if csr_p46_5_def_assact%NOTFOUND then /*** condition happens only when default is not run prior to normal P46 ****/
448 l_locked_action_id := -1;
449 end if;
450 close csr_p46_5_def_assact;
451 fnd_file.put_line(fnd_file.LOG,to_char(l_locked_action_id));
452 if l_locked_action_id > 0 then
453 delete pay_action_interlocks where locked_action_id = l_locked_action_id;
454 hr_nonrun_asact.insint(l_ass_act_id,l_locked_action_id);
455 end if;
456 end if;
457
458 end if;
459 end if;
460 close csr_aei_flag;
461
462 end loop;
463
464 hr_utility.set_location('Leaving: '||l_proc,999);
465 END internal_action_creation;
466 --
467 --
468 --
469 --
470 /*** EOY 07-08 ****/
471 FUNCTION fetch_45_46_pennot_rec(p_effective_date IN DATE,
472 p_tax_rec IN g_tax_rec,
473 p_person_rec IN act_info_rec,
474 p_info_type IN VARCHAR2,
475 p_assact_id IN NUMBER,
476 p_45_46_pennot_rec OUT nocopy act_info_rec) return boolean IS
477 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_45_46_pennot_rec';
478 l_assignment_id number;
479 l_ovn number;
480 l_arch boolean;
481
482
483 cursor csr_45_46_pennot_details is
484 select aei.assignment_extra_info_id,
485 aei.aei_information1 send_edi,
486 aei.aei_information2 annual_pension,
487 aei.aei_information3 date_pension_start,
488 aei.aei_information4 static_flag,
489 aei.aei_information5 prev_emp_paye_ref,
490 aei.aei_information6 date_left_prev_emp,
491 aei.aei_information7 prev_tax_code,
492 aei.aei_information8 prev_tax_basis,
493 aei.aei_information9 prev_last_pay_period_type,
494 aei.aei_information10 prev_last_pay_period,
495 aei.aei_information11 recently_bereaved,
496 aei.object_version_number
497 from per_assignment_extra_info aei
498 where aei.assignment_id = p_person_rec.assignment_id
499 and aei.information_type = p_info_type;
500
501
502
503 l_45_46_pennot_rec csr_45_46_pennot_details%rowtype;
504 BEGIN
505 hr_utility.set_location('Entering: '||l_proc,1);
506 l_arch := true;
507
508 fnd_file.put_line(fnd_file.LOG,'Entering: '||l_proc);
509 open csr_45_46_pennot_details;
510 fetch csr_45_46_pennot_details into l_45_46_pennot_rec;
511 close csr_45_46_pennot_details;
512
513
514 if length(ltrim(p_tax_rec.tax_code,'S')) > 6 then
515 --l_arch := false;
516 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
517 pay_core_utils.push_token('ITEM_NAME', 'Tax Code');
518 pay_core_utils.push_token('MAX_VALUE', '6 characters');
519 hr_utility.set_location('Tax Code error',20);
520 fnd_file.put_line(fnd_file.LOG,'l_arch3: ');
521
522 end if;
523 if length(ltrim(l_45_46_pennot_rec.prev_tax_code,'S')) > 6 then
524 --l_arch := false;
525 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
526 pay_core_utils.push_token('ITEM_NAME', 'Previous Tax Code');
527 pay_core_utils.push_token('MAX_VALUE', '6 characters');
528 hr_utility.set_location('Prev Tax Code',40);
529 fnd_file.put_line(fnd_file.LOG,'l_arch4: ');
530 end if;
531
532 if not validate_data(substr(ltrim(substr(l_45_46_pennot_rec.prev_emp_paye_ref,4,8),'/'),1,7),'Previous Tax Reference','FULL_EDI') then
533 --l_arch := false;
534 hr_utility.set_location('Previous Tax Reference error',50);
535 fnd_file.put_line(fnd_file.LOG,'l_arch5: ');
536 end if;
537
538 if not validate_data(substr(l_45_46_pennot_rec.prev_emp_paye_ref,1,3),'Previous Tax District','FULL_EDI') then
539 --l_arch := false;
540 hr_utility.set_location('Previous Tax District error',60);
541 fnd_file.put_line(fnd_file.LOG,'l_arch6: ');
542 end if;
543
544 if not validate_data(p_tax_rec.prev_paid,'Previous Pay','FULL_EDI') then
545 -- l_arch := false;
546 hr_utility.set_location('Prev Pay Valiation',70);
547 fnd_file.put_line(fnd_file.LOG,'l_arch7: ');
548 end if;
549
550 if not validate_data(p_tax_rec.prev_tax,'Previous Tax','FULL_EDI') then
551 --l_arch := false;
552 hr_utility.set_location('Prev Tax Validation',80);
553 fnd_file.put_line(fnd_file.LOG,'l_arch8: ');
554 end if;
555
556 if not validate_data(l_45_46_pennot_rec.prev_last_pay_period,'Previous Last Payment Period','FULL_EDI') then
557 --l_arch := false;
558 hr_utility.set_location('Previous period error',90);
559 fnd_file.put_line(fnd_file.LOG,'l_arch9: ');
560 end if;
561
562 l_ovn := l_45_46_pennot_rec.object_version_number;
563 if l_arch then
564 hr_utility.set_location('Clear Flag',20);
565 hr_assignment_extra_info_api.update_assignment_extra_info
566 (p_validate => false,
567 p_object_version_number => l_ovn,
568 p_assignment_extra_info_id => l_45_46_pennot_rec.assignment_extra_info_id,
569 p_aei_information_category => 'GB_P46PENNOT',
570 p_aei_information1 => 'N');
571 end if;
572
573 if p_info_type = 'GB_P46PENNOT' then
574 p_45_46_pennot_rec.action_info_category := 'GB P46 PENNOT EDI';
575 end if;
576
577 p_45_46_pennot_rec.assignment_id := p_person_rec.assignment_id;
578 p_45_46_pennot_rec.effective_date := p_effective_date;
579 p_45_46_pennot_rec.act_info1 := l_ovn;
580 p_45_46_pennot_rec.act_info2 := trim(l_45_46_pennot_rec.annual_pension);
581 p_45_46_pennot_rec.act_info3 := l_45_46_pennot_rec.date_pension_start;
582 p_45_46_pennot_rec.act_info4 := l_45_46_pennot_rec.prev_emp_paye_ref;
583 p_45_46_pennot_rec.act_info5 := l_45_46_pennot_rec.date_left_prev_emp;
584 p_45_46_pennot_rec.act_info6 := l_45_46_pennot_rec.prev_tax_code;
585 p_45_46_pennot_rec.act_info7 := l_45_46_pennot_rec.prev_tax_basis;
586 p_45_46_pennot_rec.act_info8 := l_45_46_pennot_rec.prev_last_pay_period_type;
587 p_45_46_pennot_rec.act_info9 := l_45_46_pennot_rec.prev_last_pay_period;
588 p_45_46_pennot_rec.act_info10 := l_45_46_pennot_rec.recently_bereaved;
589 p_45_46_pennot_rec.act_info11 := p_tax_rec.prev_paid;
590 p_45_46_pennot_rec.act_info12 := p_tax_rec.prev_tax;
591
592 hr_utility.set_location('Leaving: '||l_proc,999);
593 return l_arch;
594
595
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 fnd_file.put_line(fnd_file.LOG,'2435*****');
600 return false;
601 END fetch_45_46_pennot_rec;
602 --
603
604 PROCEDURE fetch_tax_rec(p_assactid IN NUMBER,
605 p_effective_date IN DATE,
606 p_tax_rec OUT nocopy g_tax_rec) IS
607
608 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_tax_rec';
609 l_paye_id number;
610 l_paye_details_id number;
611 l_paye_rr_id number;
612 l_paye_details_rr_id number;
613 l_assignment_id number;
614 l_element_id number;
615 l_asg_start date;
616 l_asg_end date;
617
618 cursor csr_element_id(p_name varchar2) is
619 select element_type_id
620 from pay_element_types_f
621 where element_name = p_name
622 and legislation_code = 'GB';
623
624 cursor csr_assignment_details is
625 select /*+ ORDERED */
626 asg.assignment_id,
627 asg.effective_start_date,
628 asg.effective_end_date
629 from pay_assignment_actions paa,
630 per_assignments_f asg
631 where paa.assignment_action_id = p_assactid
632 and paa.assignment_id = asg.assignment_id
633 and p_effective_date between asg.effective_start_date and asg.effective_end_date;
634
635 -- Bug 6643668
636 -- Tax data should be picked only from element entry values.
637 -- Earlier code was checking run results first, then if values not found
638 -- there, then it was going for element entry values.
639 /*
640 cursor csr_max_run_result(p_element_id number) is
641 select /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
642 pact PAY_PAYROLL_ACTIONS_PK,
643 r2 PAY_RUN_RESULTS_N50)
644 USE_NL(assact2, pact, r2) */
645 /* to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type|| r2.run_result_id),17))
646 from pay_assignment_actions assact2,
647 pay_payroll_actions pact,
648 pay_run_results r2
649 where assact2.assignment_id = l_assignment_id
650 and r2.element_type_id+0 = p_element_id
651 and r2.assignment_action_id = assact2.assignment_action_id
652 and r2.status IN ('P', 'PA')
653 and pact.payroll_action_id = assact2.payroll_action_id
654 and pact.action_type IN ( 'Q','R','B','I')
655 and assact2.action_status = 'C'
656 and pact.effective_date between l_asg_start and l_asg_end
657 and not exists(
658 select '1'
659 from pay_action_interlocks pai,
660 pay_assignment_actions assact3,
661 pay_payroll_actions pact3
662 where pai.locked_action_id = assact2.assignment_action_id
663 and pai.locking_action_id = assact3.assignment_action_id
664 and pact3.payroll_action_id = assact3.payroll_action_id
665 and pact3.action_type = 'V'
666 and assact3.action_status = 'C');
667
668 cursor csr_run_result(l_run_result_id number,l_element_type_id number) is
669 select max(decode(name,'Tax Code',result_value,NULL)) tax_code,
670 max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
671 to_number(max(decode(name,'Pay Previous',
672 fnd_number.canonical_to_number(result_value),NULL))) pay_previous,
673 to_number(max(decode(name,'Tax Previous',
674 fnd_number.canonical_to_number(result_value),NULL))) tax_previous
675 from pay_input_values_f v,
676 pay_run_result_values rrv
677 where rrv.run_result_id = l_run_result_id
678 and v.input_value_id = rrv.input_value_id
679 and v.element_type_id = l_element_type_id;
680 */
681
682 cursor csr_paye_details is
683 select max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
684 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
685 max(decode(iv.name,'Pay Previous',screen_entry_value)) pay_previous,
686 max(decode(iv.name,'Tax Previous',screen_entry_value)) tax_previous
687 from pay_element_entries_f e,
688 pay_element_entry_values_f v,
689 pay_input_values_f iv,
690 pay_element_links_f link
691 where e.assignment_id = l_assignment_id
692 and link.element_type_id = l_paye_details_id
693 and e.element_link_id = link.element_link_id
694 and e.element_entry_id = v.element_entry_id
695 and iv.input_value_id = v.input_value_id
696 and p_effective_date between e.effective_start_date and e.effective_end_date -- 5660011
697 and p_effective_date between v.effective_start_date and v.effective_end_date
698 and p_effective_date between link.effective_start_date and link.effective_end_date
699 and e.effective_end_date between link.effective_start_date and link.effective_end_date
700 and e.effective_end_date between iv.effective_start_date and iv.effective_end_date
701 and e.effective_end_date between v.effective_start_date and v.effective_end_date ;
702 -- Bug 6643668 - this check is not reuqired
703 /*and e.effective_end_date = (select max(e1.effective_end_date)
704 from pay_element_entries_f e1,
705 pay_element_links_f link1
706 where link1.element_type_id = l_paye_details_id
707 and e1.assignment_id = l_assignment_id
708 and e1.element_link_id = link1.element_link_id);
709 */
710
711 BEGIN
712 hr_utility.set_location('Entering: '||l_proc,1);
713
714
715
716 open csr_element_id('PAYE');
717 fetch csr_element_id into l_paye_id;
718 close csr_element_id;
719
720 open csr_element_id('PAYE Details');
721 fetch csr_element_id into l_paye_details_id;
722 close csr_element_id;
723
724 open csr_assignment_details;
725 fetch csr_assignment_details into l_assignment_id,
726 l_asg_start,
727 l_asg_end;
728 close csr_assignment_details;
729
730 -- Bug 6643668
731 -- Tax data should be picked only from element entry values.
732 -- Earlier code was checking run results first, then if values not found
733 -- there, then it was going for element entry values.
734 /*
735 open csr_max_run_result(l_paye_id);
736 fetch csr_max_run_result into l_paye_rr_id;
737 close csr_max_run_result;
738
739 open csr_max_run_result(l_paye_details_id);
740 fetch csr_max_run_result into l_paye_details_rr_id;
741 close csr_max_run_result;
742
743 open csr_run_result(l_paye_rr_id, l_paye_id);
744 fetch csr_run_result into p_tax_rec.tax_code,
745 p_tax_rec.tax_basis,
746 p_tax_rec.prev_paid,
747 p_tax_rec.prev_tax;
748 close csr_run_result;
749 -- if Tax code is not found, fetch from the latest PAYE Details run results
750
751 -- Bug 5660011
752 if ( p_tax_rec.prev_tax is null and p_tax_rec.prev_paid is null ) or
753 ( p_tax_rec.prev_tax = 0 and p_tax_rec.prev_paid = 0 ) then
754 */
755 open csr_paye_details;
756 fetch csr_paye_details into p_tax_rec.tax_code,
757 p_tax_rec.tax_basis,
758 p_tax_rec.prev_paid,
759 p_tax_rec.prev_tax;
760 close csr_paye_details;
761 /* -- Bug 6643668 continued
762 end if;
763
764 if p_tax_rec.tax_code is null then
765 open csr_run_result(l_paye_details_rr_id, l_paye_details_id);
766 fetch csr_run_result into p_tax_rec.tax_code,
767 p_tax_rec.tax_basis,
768 p_tax_rec.prev_paid,
769 p_tax_rec.prev_tax;
770 close csr_run_result;
771
772 -- 3. Still not found, fetch the value from the PAYE
773 if p_tax_rec.tax_code is null then
774 hr_utility.trace('Fetching run result 3');
775 open csr_paye_details;
776 fetch csr_paye_details into p_tax_rec.tax_code,
777 p_tax_rec.tax_basis,
778 p_tax_rec.prev_paid,
779 p_tax_rec.prev_tax;
780 close csr_paye_details;
781 end if;
782 end if;
783 */
784 hr_utility.set_location('Leaving: '||l_proc,999);
785 END fetch_tax_rec;
786 --
787 --
788 FUNCTION fetch_address_rec(p_person_id IN NUMBER,
789 p_assignment_id IN NUMBER,
790 p_effective_date IN DATE,
791 p_addr_rec OUT nocopy act_info_rec) return boolean IS
792 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_address_rec';
793 l_arch boolean;
794 l_temp varchar2(200);
795 cursor csr_address is
796 select upper(substr(addr.address_line1,1,35)) addr1,
797 upper(substr(addr.address_line2,1,35)) addr2,
798 upper(substr(addr.address_line3,1,35)) addr3,
799 upper(hr_general.decode_lookup('GB_COUNTY', substr(addr.region_1,1,35))) county,
800 addr.postal_code post_code,
801 upper(addr.town_or_city) town_or_city,
802 upper(addr.country) country
803 from per_addresses addr
804 where addr.person_id(+) = p_person_id
805 and ( addr.primary_flag = 'Y'
806 or addr.primary_flag is null)
807 and p_effective_date between nvl(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
808 and nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
809 l_addr_rec csr_address%rowtype;
810 BEGIN
811 hr_utility.set_location('Entering: '||l_proc,1);
812 l_arch := true;
813
814 open csr_address;
815 fetch csr_address into l_addr_rec;
816 close csr_address;
817
818 l_temp := l_addr_rec.addr1 || ' ' || l_addr_rec.addr2 ||
819 l_addr_rec.addr3 || ' ' || l_addr_rec.town_or_city ||
820 l_addr_rec.county;
821
822 if l_addr_rec.addr1 is null then
823 pay_core_utils.push_message(800, 'HR_78088_MISSING_DATA_ERR', 'F');
824 pay_core_utils.push_token('TOKEN', 'Address');
825 l_arch := false;
826 hr_utility.set_location('Address missing',10);
827 end if;
828
829 if not validate_data(l_temp,'Address','EDI_SURNAME') then
830 l_arch := false;
831 hr_utility.set_location('Address Validation',20);
832 end if;
833
834 if not validate_data(l_addr_rec.post_code,'Post Code','FULL_EDI') then
835 l_arch := false;
836 hr_utility.set_location('Post Code error',20);
837 end if;
838
839 p_addr_rec.assignment_id := p_assignment_id;
840 p_addr_rec.effective_date := p_effective_date;
841 p_addr_rec.action_info_category := 'ADDRESS DETAILS';
842 p_addr_rec.act_info5 := l_addr_rec.addr1;
843 p_addr_rec.act_info6 := l_addr_rec.addr2;
844 p_addr_rec.act_info7 := l_addr_rec.addr3;
845 p_addr_rec.act_info8 := l_addr_rec.town_or_city;
846 p_addr_rec.act_info9 := l_addr_rec.county;
847 p_addr_rec.act_info12 := l_addr_rec.post_code;
848 p_addr_rec.act_info13 := l_addr_rec.country;
849
850 hr_utility.set_location('Leaving: '||l_proc,999);
851 return l_arch;
852 END fetch_address_rec;
853 --
854 --
855 --
856 --
857 -- Function to fetch country name for the given country code
858 --
859
860 FUNCTION get_territory_short_name(prm_name in varchar2)
861 return varchar2 is
862 --
863 -- Cursor to fetch country name for the country code
864 --
865 Cursor csr_territory_short_name (p_code varchar2) is
866 select territory_short_name
867 from fnd_territories_vl
868 where territory_code = p_code;
869
870 l_code varchar2(200);
871 BEGIN
872 open csr_territory_short_name(prm_name);
873 fetch csr_territory_short_name into l_code;
874 close csr_territory_short_name;
875
876 return l_code;
877
878 END get_territory_short_name;
879 --
880 --
881 --
882 FUNCTION fetch_person_rec(p_assactid IN NUMBER,
883 p_effective_date IN DATE,
884 p_tax_rec IN g_tax_rec,
885 p_person_rec OUT nocopy act_info_rec) return boolean IS
886
887 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
888 l_job varchar2(70);
889 l_person_id number;
890 l_arch boolean;
891 l_temp varchar2(30);
892
893 cursor csr_person_details is
894 select /*+ ORDERED */
895 pap.person_id,
896 paa.assignment_id,
897 pap.title,
898 pap.first_name,
899 pap.last_name,
900 pap.middle_names,
901 paa.ASSIGNMENT_NUMBER,
902 pap.national_identifier,
903 pap.sex,
904 fnd_date.date_to_canonical(pap.date_of_birth) date_of_birth,
905 fnd_date.date_to_canonical(decode(pap.current_employee_flag, 'Y', serv.date_start, null)) hire_date
906 from pay_assignment_actions act,
907 per_assignments_f paa,
908 per_people_f pap,
909 per_periods_of_service serv
910 where act.assignment_action_id = p_assactid
911 and act.assignment_id = paa.assignment_id
912 and paa.person_id = pap.person_id
913 and paa.period_of_service_id = serv.period_of_service_id
914 and serv.date_start <= p_effective_date
915 and p_effective_date between paa.effective_start_date and paa.effective_end_date
916 and p_effective_date between pap.effective_start_date and pap.effective_end_date;
917
918 cursor csr_job is
919 select pay_get_job_segment_pkg.get_job_segment(paa.business_group_id,job.job_definition_id,act.payroll_action_id) job
920 from pay_assignment_actions act,
921 per_assignments_f paa,
922 per_jobs job
923 where act.assignment_action_id = p_assactid
924 and act.assignment_id = paa.assignment_id
925 and paa.job_id = job.job_id(+)
926 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
927
928 l_person_rec csr_person_details%rowtype;
929 BEGIN
930 hr_utility.set_location('Entering: '||l_proc,1);
931 l_arch := true;
932
933 open csr_person_details;
934 fetch csr_person_details into l_person_rec;
935 close csr_person_details;
936
937 open csr_job;
938 fetch csr_job into l_job;
939 close csr_job;
940
941 if not validate_data(l_person_rec.first_name,'First Name','EDI_SURNAME') then
942 l_arch := false;
943 hr_utility.set_location('First Name error',10);
944 end if;
945
946 if not validate_data(l_person_rec.last_name,'Last Name','EDI_SURNAME') then
947 l_arch := false;
948 hr_utility.set_location('Last Name error',20);
949 end if;
950
951 if not validate_data(l_person_rec.assignment_number,'Assignment Number','FULL_EDI') then
952 l_arch := false;
953 hr_utility.set_location('Assignment Number error',30);
954 end if;
955
956 if not validate_data(l_person_rec.sex,'Sex','FULL_EDI') then
957 l_arch := false;
958 hr_utility.set_location('Sex error',40);
959 end if;
960
961 if not validate_data(l_job,'Job Title','FULL_EDI') then
962 l_arch := false;
963 hr_utility.set_location('Job Title error',50);
964 end if;
965
966 if l_person_rec.national_identifier is not null and
967 hr_gb_utility.ni_validate(l_person_rec.national_identifier,sysdate) <> 0 then
968 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
969 pay_core_utils.push_token('INPUT_NAME', 'NI Number');
970 pay_core_utils.push_token('INPUT_VALUE', l_person_rec.national_identifier);
971 l_arch := false;
972 hr_utility.set_location('NI error',60);
973 end if;
974
975 /** -- NO Tax code validation yet as it is different between P45(3),P46 and P46P --**
976 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,l_person_rec.assignment_id);
977 if l_temp <> ' ' then
978 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
979 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
980 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
981 l_arch := false;
982 hr_utility.set_location('Tax Code error',30);
983 end if;
984 */
985 p_person_rec.person_id := l_person_rec.person_id;
986 p_person_rec.assignment_id := l_person_rec.assignment_id;
987 p_person_rec.effective_date := p_effective_date;
988 p_person_rec.action_info_category := 'GB EMPLOYEE DETAILS';
989 p_person_rec.act_info6 := l_person_rec.first_name;
990 p_person_rec.act_info7 := l_person_rec.middle_names;
991 p_person_rec.act_info8 := l_person_rec.last_name;
992 p_person_rec.act_info11 := l_person_rec.assignment_number;
993 p_person_rec.act_info12 := l_person_rec.national_identifier;
994 p_person_rec.act_info14 := l_person_rec.title;
995 p_person_rec.act_info15 := l_person_rec.date_of_birth;
996 p_person_rec.act_info16 := l_person_rec.hire_date;
997 p_person_rec.act_info17 := l_person_rec.sex;
998 p_person_rec.act_info18 := l_job;
999 p_person_rec.act_info21 := p_tax_rec.tax_code;
1000 p_person_rec.act_info22 := p_tax_rec.tax_basis;
1001 hr_utility.set_location('Leaving: '||l_proc,999);
1002 return l_arch;
1003 END fetch_person_rec;
1004 --
1005 --
1006 FUNCTION fetch_p45_3_rec(p_effective_date IN DATE,
1007 p_tax_rec IN g_tax_rec,
1008 p_person_rec IN act_info_rec,
1009 p_p45_3_rec OUT nocopy act_info_rec) return boolean IS
1010 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p45_3_rec';
1011 l_assignment_id number;
1012 l_ovn number;
1013 l_arch boolean;
1014 l_temp varchar2(30);
1015
1016 cursor csr_p45_3_details is
1017 select aei.assignment_extra_info_id,
1018 aei.aei_information1 send_edi,
1019 aei.aei_information2 prev_tax_district,
1020 aei.aei_information3 date_left,
1021 aei.aei_information4 prev_tax_code,
1022 aei.aei_information5 prev_tax_basis,
1023 aei.aei_information6 prev_period_type,
1024 aei.aei_information7 prev_period,
1025 aei.aei_information8 static_flag,
1026 /*changes for P45PT_3 start*/
1027 aei.aei_information9 prev_tax_paid_notified,
1028 aei.aei_information10 not_paid_between_start_and5apr,
1029 aei.aei_information11 continue_sl_deductions,
1030 /*changes for P45PT_3 start*/
1031 --Bug 6994632 fetching Prev Tax Pay Notified value
1032 aei.aei_information12 prev_tax_pay_notified,
1033 aei.object_version_number
1034 from per_assignment_extra_info aei
1035 where aei.assignment_id = p_person_rec.assignment_id
1036 and aei.information_type = 'GB_P45_3';
1037
1038 cursor csr_student_loan is
1039 select nvl(min(decode(inv.name, 'Start Date', eev.screen_entry_value, 'X')),'X') s_date,
1040 nvl(min(decode(inv.name, 'End Date', eev.screen_entry_value, null)),'4712/12/31 00:00:00') e_date,
1041 fnd_date.date_to_canonical(min(decode(inv.name, 'End Date', eev.effective_end_date, fnd_date.canonical_to_date('4712/12/31 00:00:00')))) eff_date
1042 from pay_element_types_f elt,
1043 pay_element_entries_f ele,
1044 pay_input_values_f inv,
1045 pay_element_entry_values_f eev
1046 where elt.element_name = 'Student Loan'
1047 and ele.element_type_id = elt.element_type_id
1048 and ele.assignment_id = p_person_rec.assignment_id
1049 and inv.element_type_id = elt.element_type_id
1050 and eev.input_value_id + 0 = inv.input_value_id
1051 and eev.element_entry_id = ele.element_entry_id -- Bug 5469122
1052 and p_effective_date between elt.effective_start_date and elt.effective_end_date
1053 and p_effective_date between ele.effective_start_date and ele.effective_end_date
1054 and p_effective_date between inv.effective_start_date and inv.effective_end_date
1055 and p_effective_date between eev.effective_start_date and eev.effective_end_date;
1056
1057 l_p45_3_rec csr_p45_3_details%rowtype;
1058 l_student_rec csr_student_loan%rowtype;
1059 BEGIN
1060 hr_utility.set_location('Entering: '||l_proc,1);
1061 l_arch := true;
1062
1063 open csr_p45_3_details;
1064 fetch csr_p45_3_details into l_p45_3_rec;
1065 close csr_p45_3_details;
1066
1067 open csr_student_loan;
1068 fetch csr_student_loan into l_student_rec;
1069 close csr_student_loan;
1070
1071 if l_p45_3_rec.date_left is null then
1072 pay_core_utils.push_message(800, 'HR_78088_MISSING_DATA_ERR', 'F');
1073 pay_core_utils.push_token('TOKEN', 'Date Left Previous Employer');
1074 l_arch := false;
1075 hr_utility.set_location('Date Left null',30);
1076 end if;
1077
1078 if to_number(p_tax_rec.prev_paid) > 999999.99 then
1079 l_arch := false;
1080 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1081 pay_core_utils.push_token('ITEM_NAME', 'Previous Pay');
1082 pay_core_utils.push_token('MAX_VALUE', '999999.99');
1083 hr_utility.set_location('Prev Paid > 999999.99',10);
1084 end if;
1085
1086 if to_number(p_tax_rec.prev_tax) > 999999.99 then
1087 l_arch := false;
1088 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1089 pay_core_utils.push_token('ITEM_NAME', 'Previous Tax');
1090 pay_core_utils.push_token('MAX_VALUE', '999999.99');
1091 hr_utility.set_location('Prev Tax > 999999.99',20);
1092 end if;
1093
1094 /** -- Validate using the orignal, will use new one when requirement comes out --
1095 l_temp := hr_gb_utility.tax_code_validate(l_p45_3_rec.prev_tax_code,sysdate,p_person_rec.assignment_id);
1096 if l_temp <> ' ' then
1097 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1098 pay_core_utils.push_token('INPUT_NAME', 'Previous Tax Code');
1099 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1100 l_arch := false;
1101 hr_utility.set_location('Tax Code error',30);
1102 end if;
1103 */
1104 if length(ltrim(p_tax_rec.tax_code,'S')) > 6 then
1105 l_arch := false;
1106 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1107 pay_core_utils.push_token('ITEM_NAME', 'Tax Code');
1108 pay_core_utils.push_token('MAX_VALUE', '6 characters');
1109 hr_utility.set_location('Tax Code error',30);
1110 end if;
1111
1112 if length(ltrim(l_p45_3_rec.prev_tax_code,'S')) > 6 then
1113 l_arch := false;
1114 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1115 pay_core_utils.push_token('ITEM_NAME', 'Previous Tax Code');
1116 pay_core_utils.push_token('MAX_VALUE', '6 characters');
1117 hr_utility.set_location('Prev Tax Code',40);
1118 end if;
1119
1120 if not validate_data(substr(ltrim(substr(l_p45_3_rec.prev_tax_district,4,8),'/'),1,7),'Previous Tax Reference','FULL_EDI') then
1121 l_arch := false;
1122 hr_utility.set_location('Previous Tax Reference error',50);
1123 end if;
1124
1125 if not validate_data(substr(l_p45_3_rec.prev_tax_district,1,3),'Previous Tax District','FULL_EDI') then
1126 l_arch := false;
1127 hr_utility.set_location('Previous Tax District error',60);
1128 end if;
1129
1130 if not validate_data(p_tax_rec.prev_paid,'Previous Pay','FULL_EDI') then
1131 l_arch := false;
1132 hr_utility.set_location('Prev Pay Valiation',70);
1133 end if;
1134
1135 if not validate_data(p_tax_rec.prev_tax,'Previous Tax','FULL_EDI') then
1136 l_arch := false;
1137 hr_utility.set_location('Prev Tax Validation',80);
1138 end if;
1139
1140 if not validate_data(l_p45_3_rec.prev_period,'Previous Last Payment Period','FULL_EDI') then
1141 l_arch := false;
1142 hr_utility.set_location('Previous period error',90);
1143 end if;
1144
1145 l_ovn := l_p45_3_rec.object_version_number;
1146 if l_arch then
1147 hr_utility.set_location('Clear Flag',100);
1148 hr_assignment_extra_info_api.update_assignment_extra_info
1149 (p_validate => false,
1150 p_object_version_number => l_ovn,
1151 p_assignment_extra_info_id => l_p45_3_rec.assignment_extra_info_id,
1152 p_aei_information_category => 'GB_P45_3',
1153 p_aei_information1 => 'N');
1154 end if;
1155
1156 p_p45_3_rec.assignment_id := p_person_rec.assignment_id;
1157 p_p45_3_rec.effective_date := p_effective_date;
1158 p_p45_3_rec.action_info_category := 'GB P45(3) EDI';
1159 p_p45_3_rec.act_info1 := l_ovn;
1160 p_p45_3_rec.act_info2 := l_p45_3_rec.prev_tax_district;
1161 p_p45_3_rec.act_info3 := l_p45_3_rec.date_left;
1162 p_p45_3_rec.act_info4 := l_p45_3_rec.prev_tax_code;
1163 p_p45_3_rec.act_info5 := l_p45_3_rec.prev_tax_basis;
1164 p_p45_3_rec.act_info6 := l_p45_3_rec.prev_period_type;
1165 p_p45_3_rec.act_info7 := l_p45_3_rec.prev_period;
1166 p_p45_3_rec.act_info8 := p_tax_rec.prev_paid;
1167 p_p45_3_rec.act_info9 := p_tax_rec.prev_tax;
1168 p_p45_3_rec.act_info10:= l_student_rec.s_date;
1169 p_p45_3_rec.act_info11:= l_student_rec.e_date;
1170 p_p45_3_rec.act_info12:= l_student_rec.eff_date;
1171 /*changes for P45PT_3 start*/
1172 p_p45_3_rec.act_info13:= l_p45_3_rec.prev_tax_paid_notified;
1173 p_p45_3_rec.act_info14:= l_p45_3_rec.not_paid_between_start_and5apr;
1174 p_p45_3_rec.act_info15:= l_p45_3_rec.continue_sl_deductions;
1175 /*changes for P45PT_3 end*/
1176 -- Bug 6994632 passing Prev Tax Pay Notified value to archive function
1177 p_p45_3_rec.act_info16:= l_p45_3_rec.prev_tax_pay_notified;
1178
1179 hr_utility.set_location('Leaving: '||l_proc,999);
1180 return l_arch;
1181 END fetch_p45_3_rec;
1182 --
1183 --
1184 FUNCTION fetch_p46_rec(p_effective_date IN DATE,
1185 p_tax_rec IN g_tax_rec,
1186 p_person_rec IN act_info_rec,
1187 p_p46_rec OUT nocopy act_info_rec) return boolean IS
1188 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46_rec';
1189 l_assignment_id number;
1190 l_ovn number;
1191 l_arch boolean;
1192 l_temp varchar2(50);
1193
1194 cursor csr_p46_details is
1195 select aei.assignment_extra_info_id,
1196 aei.aei_information1 send_edi,
1197 aei.aei_information2 p46_statement,
1198 aei.aei_information3 static_flag,
1199 aei.aei_information4 student_loan,
1200 aei.object_version_number
1201 from per_assignment_extra_info aei
1202 where aei.assignment_id = p_person_rec.assignment_id
1203 and aei.information_type = 'GB_P46';
1204
1205 l_p46_rec csr_p46_details%rowtype;
1206 BEGIN
1207 hr_utility.set_location('Entering: '||l_proc,1);
1208 l_arch := true;
1209
1210 open csr_p46_details;
1211 fetch csr_p46_details into l_p46_rec;
1212 close csr_p46_details;
1213
1214 if not validate_data(l_p46_rec.p46_statement,'P46 Statement','FULL_EDI') then
1215 l_arch := false;
1216 hr_utility.set_location('P46 Statement validation',10);
1217 end if;
1218
1219 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,p_person_rec.assignment_id);
1220 if l_temp <> ' ' then
1221 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1222 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
1223 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1224 l_arch := false;
1225 hr_utility.set_location('Tax Code error',20);
1226 end if;
1227
1228 l_ovn := l_p46_rec.object_version_number;
1229 if l_arch then
1230 hr_utility.set_location('Clear Flag',30);
1231 hr_assignment_extra_info_api.update_assignment_extra_info
1232 (p_validate => false,
1233 p_object_version_number => l_ovn,
1234 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
1235 p_aei_information_category => 'GB_P46',
1236 p_aei_information1 => 'N');
1237 end if;
1238
1239 p_p46_rec.assignment_id := p_person_rec.assignment_id;
1240 p_p46_rec.effective_date := p_effective_date;
1241 p_p46_rec.action_info_category := 'GB P46 EDI';
1242 p_p46_rec.act_info1 := l_ovn;
1243 p_p46_rec.act_info2 := l_p46_rec.p46_statement;
1244 p_p46_rec.act_info3 := l_p46_rec.student_loan;
1245
1246 hr_utility.set_location('Leaving: '||l_proc,999);
1247 return l_arch;
1248 END fetch_p46_rec;
1249 --
1250 FUNCTION fetch_p46_5_rec(p_effective_date IN DATE,
1251 p_tax_rec IN g_tax_rec,
1252 p_person_rec IN act_info_rec,
1253 p_p46_rec OUT nocopy act_info_rec) return boolean IS
1254 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46_rec';
1255 l_assignment_id number;
1256 l_ovn number;
1257 l_arch boolean;
1258 l_temp varchar2(50);
1259 l_def_archive varchar2(2);
1260 l_exist number;
1261
1262 cursor csr_p46_details is
1263 select aei.assignment_extra_info_id,
1264 aei.aei_information1 send_edi,
1265 aei.aei_information2 p46_statement,
1266 aei.aei_information3 static_flag,
1267 aei.aei_information4 student_loan,
1268 aei.aei_information5 default_send_edi,
1269 aei.aei_information6 default_static_edi,
1270 aei.object_version_number
1271 from per_assignment_extra_info aei
1272 where aei.assignment_id = p_person_rec.assignment_id
1273 and aei.information_type = 'GB_P46';
1274
1275 cursor csr_p46_5_def_det
1276 is
1277 select 1
1278 from pay_action_information pa
1279 ,pay_payroll_actions ppa
1280 ,pay_assignment_actions paa
1281 where pa.action_information_category = 'GB P46_5 EDI'
1282 and pa.action_context_type = 'AAP'
1283 and pa.action_information4 = 'Y'
1284 and pa.assignment_id = p_person_rec.assignment_id
1285 and paa.assignment_action_id = pa.action_context_id
1286 and ppa.payroll_action_id = paa.payroll_action_id
1287 and ppa.action_status = 'C';
1288
1289
1290 l_p46_rec csr_p46_details%rowtype;
1291 BEGIN
1292 hr_utility.set_location('Entering: '||l_proc,1);
1293 fnd_file.put_line(fnd_file.LOG,'Entering: '||l_proc);
1294 l_arch := true;
1295
1296 open csr_p46_details;
1297 fetch csr_p46_details into l_p46_rec;
1298 close csr_p46_details;
1299 l_def_archive := 'N';
1300
1301 if l_p46_rec.default_send_edi = 'Y' then
1302 l_def_archive := 'Y';
1303 else
1304 if l_p46_rec.default_send_edi = 'N' and l_p46_rec.default_static_edi = 'Y' then
1305 open csr_p46_5_def_det;
1306 fetch csr_p46_5_def_det into l_exist;
1307 if csr_p46_5_def_det%found then
1308 l_def_archive := 'N';
1309 else
1310 l_def_archive := 'Y';
1311 end if;
1312 close csr_p46_5_def_det;
1313 /*else
1314 l_def_archive := 'N';*/
1315 end if;
1316 end if;
1317
1318 /* if not validate_data(l_p46_rec.p46_statement,'P46 Statement','FULL_EDI') then
1319 l_arch := false;
1320 hr_utility.set_location('P46 Statement validation',10);
1321 end if;
1322
1323 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,p_person_rec.assignment_id);
1324 if l_temp <> ' ' then
1325 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1326 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
1327 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1328 l_arch := false;
1329 hr_utility.set_location('Tax Code error',20);
1330 end if;*/
1331
1332 l_ovn := l_p46_rec.object_version_number;
1333 if l_arch then
1334 hr_utility.set_location('Clear Flag',30);
1335 if l_def_archive = 'N' then
1336 hr_assignment_extra_info_api.update_assignment_extra_info
1337 (p_validate => false,
1338 p_object_version_number => l_ovn,
1339 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
1340 p_aei_information_category => 'GB_P46',
1341 p_aei_information1 => 'N');
1342 else
1343 hr_assignment_extra_info_api.update_assignment_extra_info
1344 (p_validate => false,
1345 p_object_version_number => l_ovn,
1346 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
1347 p_aei_information_category => 'GB_P46',
1348 p_aei_information5 => 'N');
1349
1350 end if;
1351 end if;
1352
1353 p_p46_rec.assignment_id := p_person_rec.assignment_id;
1354 p_p46_rec.effective_date := p_effective_date;
1355 p_p46_rec.action_info_category := 'GB P46_5 EDI';
1356 p_p46_rec.act_info1 := l_ovn;
1357 p_p46_rec.act_info2 := l_p46_rec.p46_statement;
1358 p_p46_rec.act_info3 := l_p46_rec.student_loan;
1359 p_p46_rec.act_info4 := l_def_archive;
1360 hr_utility.set_location('Leaving: '||l_proc,999);
1361 fnd_file.put_line(fnd_file.LOG,'Leaving: '||l_proc);
1362 return l_arch;
1363 END fetch_p46_5_rec;
1364 --
1365 FUNCTION fetch_p46p_rec(p_effective_date IN DATE,
1366 p_tax_rec IN g_tax_rec,
1367 p_person_rec IN act_info_rec,
1368 p_p46p_rec OUT nocopy act_info_rec) return boolean IS
1369 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46p_rec';
1370 l_assignment_id number;
1371 l_ovn number;
1372 l_arch boolean;
1373
1374 cursor csr_p46p_details is
1375 select aei.assignment_extra_info_id,
1376 aei.aei_information1 send_edi,
1377 aei.aei_information2 annual_pension,
1378 aei.aei_information3 date_pension_start,
1379 aei.aei_information4 static_flag,
1380 aei.object_version_number
1381 from per_assignment_extra_info aei
1382 where aei.assignment_id = p_person_rec.assignment_id
1383 and aei.information_type = 'GB_P46PENNOT';
1384
1385 l_p46p_rec csr_p46p_details%rowtype;
1386 BEGIN
1387 hr_utility.set_location('Entering: '||l_proc,1);
1388 l_arch := true;
1389
1390 open csr_p46p_details;
1391 fetch csr_p46p_details into l_p46p_rec;
1392 close csr_p46p_details;
1393
1394 if not validate_data(l_p46p_rec.annual_pension,'Annual Pension','FULL_EDI') then
1395 l_arch := false;
1396 hr_utility.set_location('Annaul Pension',10);
1397 end if;
1398
1399 if length(ltrim(p_tax_rec.tax_code,'S')) > 6 then
1400 l_arch := false;
1401 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1402 pay_core_utils.push_token('ITEM_NAME', 'Tax Code');
1403 pay_core_utils.push_token('MAX_VALUE', '6 characters');
1404 hr_utility.set_location('Tax Code error',20);
1405 end if;
1406
1407 l_ovn := l_p46p_rec.object_version_number;
1408 if l_arch then
1409 hr_utility.set_location('Clear Flag',20);
1410 hr_assignment_extra_info_api.update_assignment_extra_info
1411 (p_validate => false,
1412 p_object_version_number => l_ovn,
1413 p_assignment_extra_info_id => l_p46p_rec.assignment_extra_info_id,
1414 p_aei_information_category => 'GB_P46PENNOT',
1415 p_aei_information1 => 'N');
1416 end if;
1417
1418 p_p46p_rec.assignment_id := p_person_rec.assignment_id;
1419 p_p46p_rec.effective_date := p_effective_date;
1420 p_p46p_rec.action_info_category := 'GB P46 Pension EDI';
1421 p_p46p_rec.act_info1 := l_ovn;
1422 p_p46p_rec.act_info2 := l_p46p_rec.annual_pension;
1423 p_p46p_rec.act_info3 := l_p46p_rec.date_pension_start;
1424
1425 hr_utility.set_location('Leaving: '||l_proc,999);
1426 return l_arch;
1427 END fetch_p46p_rec;
1428 --
1429 --
1430 PROCEDURE insert_archive_row(p_assactid IN NUMBER,
1431 p_effective_date IN DATE,
1432 p_tab_rec_data IN action_info_table) IS
1433 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
1434 l_ovn number;
1435 l_action_id number;
1436 BEGIN
1437 hr_utility.set_location('Entering: '||l_proc,1);
1438 if p_tab_rec_data.count > 0 then
1439 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
1440 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
1441 hr_utility.trace('action_context_id = '|| p_assactid);
1442 if p_tab_rec_data(i).action_info_category is not null then
1443 pay_action_information_api.create_action_information(
1444 p_action_information_id => l_action_id,
1445 p_object_version_number => l_ovn,
1446 p_action_information_category => p_tab_rec_data(i).action_info_category,
1447 p_action_context_id => p_assactid,
1448 p_action_context_type => 'AAP',
1449 p_assignment_id => p_tab_rec_data(i).assignment_id,
1450 p_effective_date => p_effective_date,
1451 p_action_information1 => p_tab_rec_data(i).act_info1,
1452 p_action_information2 => p_tab_rec_data(i).act_info2,
1453 p_action_information3 => p_tab_rec_data(i).act_info3,
1454 p_action_information4 => p_tab_rec_data(i).act_info4,
1455 p_action_information5 => p_tab_rec_data(i).act_info5,
1456 p_action_information6 => p_tab_rec_data(i).act_info6,
1457 p_action_information7 => p_tab_rec_data(i).act_info7,
1458 p_action_information8 => p_tab_rec_data(i).act_info8,
1459 p_action_information9 => p_tab_rec_data(i).act_info9,
1460 p_action_information10 => p_tab_rec_data(i).act_info10,
1461 p_action_information11 => p_tab_rec_data(i).act_info11,
1462 p_action_information12 => p_tab_rec_data(i).act_info12,
1463 p_action_information13 => p_tab_rec_data(i).act_info13,
1464 p_action_information14 => p_tab_rec_data(i).act_info14,
1465 p_action_information15 => p_tab_rec_data(i).act_info15,
1466 p_action_information16 => p_tab_rec_data(i).act_info16,
1467 p_action_information17 => p_tab_rec_data(i).act_info17,
1468 p_action_information18 => p_tab_rec_data(i).act_info18,
1469 p_action_information19 => p_tab_rec_data(i).act_info19,
1470 p_action_information20 => p_tab_rec_data(i).act_info20,
1471 p_action_information21 => p_tab_rec_data(i).act_info21,
1472 p_action_information22 => p_tab_rec_data(i).act_info22,
1473 p_action_information23 => p_tab_rec_data(i).act_info23,
1474 p_action_information24 => p_tab_rec_data(i).act_info24,
1475 p_action_information25 => p_tab_rec_data(i).act_info25,
1476 p_action_information26 => p_tab_rec_data(i).act_info26,
1477 p_action_information27 => p_tab_rec_data(i).act_info27,
1478 p_action_information28 => p_tab_rec_data(i).act_info28,
1479 p_action_information29 => p_tab_rec_data(i).act_info29,
1480 p_action_information30 => p_tab_rec_data(i).act_info30
1481 );
1482 end if;
1483 end loop;
1484 end if;
1485 hr_utility.set_location('Leaving: '||l_proc,999);
1486 END insert_archive_row;
1487 --
1488 --
1489 /*------------ PUBLIC PROCEDURE --------------*/
1490 --
1491 --
1492 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
1493 IS
1494 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
1495 l_sender_id VARCHAR2(30);
1496 l_tax_ref VARCHAR2(30);
1497 l_tax_dist VARCHAR2(30);
1498 l_employer_addr VARCHAR2(255);
1499 l_employer_name VARCHAR2(150);
1500 l_err BOOLEAN;
1501 l_exp EXCEPTION;
1502
1503 cursor csr_sender_id is
1504 select upper(hoi.org_information11),
1505 upper(hoi.org_information1),
1506 upper(hoi.org_information2),
1507 upper(hoi.org_information3),
1508 upper(hoi.org_information4)
1509 from pay_payroll_actions pact,
1510 hr_organization_information hoi
1511 where pact.payroll_action_id = p_payroll_action_id
1512 and pact.business_group_id = hoi.organization_id
1513 and hoi.org_information_context = 'Tax Details References'
1514 and (hoi.org_information10 is null
1515 OR
1516 hoi.org_information10 = 'UK')
1517 and upper(hoi.org_information1) =
1518 upper(substr(pact.legislative_parameters,
1519 instr(pact.legislative_parameters,'TAX_REF=') + 8,
1520 instr(pact.legislative_parameters||' ',' ',
1521 instr(pact.legislative_parameters,'TAX_REF=')+8)
1522 - instr(pact.legislative_parameters,'TAX_REF=') - 8));
1523 BEGIN
1524 hr_utility.set_location('Entering '|| l_proc, 10);
1525 l_err := FALSE;
1526
1527 open csr_sender_id;
1528 fetch csr_sender_id into l_sender_id, l_tax_ref, l_tax_dist, l_employer_name, l_employer_addr;
1529 close csr_sender_id;
1530
1531 if l_sender_id is null then
1532 pay_core_utils.push_message(800, 'HR_78087_EDI_SENDER_ID_MISSING', 'F');
1533 pay_core_utils.push_token('TAX_REF', l_tax_ref);
1534 l_err := true;
1535 else
1536 if (not validate_data(l_sender_id,'Sender ID','FULL_EDI')) then
1537 l_err := true;
1538 end if;
1539 end if;
1540 if pay_gb_eoy_magtape.validate_input(substr(l_tax_ref,1,3),'NUMBER') > 0
1541 OR
1542 pay_gb_eoy_magtape.validate_input(l_tax_ref,'FULL_EDI') > 0 then
1543 pay_core_utils.push_message(800, 'HR_GB_78049_INV_EMP_PAYE_REF', 'F');
1544 l_err := true;
1545 end if;
1546
1547 if (not validate_data(l_tax_dist,'IR Office Name ','FULL_EDI')) then
1548 l_err := true;
1549 end if;
1550
1551 if (not validate_data(l_employer_name,'Employers Name','FULL_EDI')) then
1552 l_err := true;
1553 end if;
1554
1555 if (not validate_data(l_employer_addr,'Employers Address','FULL_EDI')) then
1556 l_err := true;
1557 end if;
1558
1559 if (l_err) then
1560 raise l_exp;
1561 end if;
1562 hr_utility.set_location('Leaving '|| l_proc, 10);
1563 EXCEPTION
1564 when others then
1565 hr_utility.raise_error;
1566 END archinit;
1567 --
1568 --
1569 PROCEDURE range_cursor (pactid IN NUMBER,
1570 sqlstr OUT NOCOPY VARCHAR2)
1571 IS
1572 /* Changes for P45PT3 start*/
1573 cursor csr_parameter_info IS
1574 SELECT
1575 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
1576 'TEST'),1,1) test_indicator,
1577 trim(substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
1578 'TEST_ID'),1,8)) test_id,
1579 report_type
1580 FROM pay_payroll_actions
1581 WHERE payroll_action_id = pactid;
1582
1583
1584 l_test_indicator varchar2(1);
1585 l_test_id varchar2(8);
1586 l_report_type varchar2(15);
1587 test_indicator_error EXCEPTION;
1588 /* Changes for P45PT3 end*/
1589 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
1590 BEGIN
1591 hr_utility.set_location('Entering: '||l_proc,1);
1592
1593 /* Changes for P45PT3 start*/
1594 OPEN csr_parameter_info;
1595 fetch csr_parameter_info into l_test_indicator,l_test_id,l_report_type;
1596 CLOSE csr_parameter_info ;
1597
1598 IF l_report_type = 'P45PT_3' or l_report_type='P46_5_PENNOT' or l_report_type='P46_5' THEN
1599 IF (l_test_indicator = 'Y' AND l_test_id IS NULL) THEN
1600 fnd_file.put_line (fnd_file.LOG,'Enter the Test ID as EDI Test Indicator is Yes.');
1601 RAISE test_indicator_error;
1602 END IF;
1603 END IF;
1604 /* Changes for P45PT3 end*/
1605
1606 sqlstr := 'select distinct person_id '||
1607 'from per_people_f ppf, '||
1608 'pay_payroll_actions ppa '||
1609 'where ppa.payroll_action_id = :payroll_action_id '||
1610 'and ppa.business_group_id = ppf.business_group_id '||
1611 'order by ppf.person_id';
1612 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
1613 hr_utility.set_location(' Leaving: '||l_proc,100);
1614 /* Changes for P45PT3 start*/
1615 EXCEPTION
1616 WHEN test_indicator_error THEN
1617 RAISE; -- reraise the error
1618 /* Changes for P45PT3 end*/
1619 END range_cursor;
1620 --
1621 --
1622 PROCEDURE p45_3_action_creation (pactid in number,
1623 stperson in number,
1624 endperson in number,
1625 chunk in number) IS
1626 BEGIN
1627 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P45_3', 'P45_3');
1628 END p45_3_action_creation;
1629 --
1630 --
1631 /*changes for P45PT_3 start*/
1632 PROCEDURE p45pt_3_action_creation (pactid in number,
1633 stperson in number,
1634 endperson in number,
1635 chunk in number) IS
1636 BEGIN
1637 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P45_3', 'P45PT_3');
1638 END p45pt_3_action_creation;
1639 /*changes for P45PT_3 end*/
1640 --
1641 --
1642 PROCEDURE p46_action_creation (pactid in number,
1643 stperson in number,
1644 endperson in number,
1645 chunk in number) IS
1646 BEGIN
1647 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46', 'P46');
1648 END p46_action_creation;
1649 --
1650 /*** Changes for P46 EOY *****/
1651 PROCEDURE p46_5_action_creation (pactid in number,
1652 stperson in number,
1653 endperson in number,
1654 chunk in number) IS
1655 BEGIN
1656 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46', 'P46_5');
1657 END p46_5_action_creation;
1658 /*** End ***/
1659 --
1660 PROCEDURE p46_pennot_action_creation (pactid in number,
1661 stperson in number,
1662 endperson in number,
1663 chunk in number) IS
1664 BEGIN
1665 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46PENNOT', 'P46_PENNOT');
1666 END p46_pennot_action_creation;
1667 --
1668
1669 /**UK EOY07-08 P46 PENNOT --- Corresponds to CP PENNOT EDI Process **/
1670 PROCEDURE p46_5_pennot_action_creation (pactid in number,
1671 stperson in number,
1672 endperson in number,
1673 chunk in number) IS
1674 BEGIN
1675 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46PENNOT', 'P46_5_PENNOT');
1676 END p46_5_pennot_action_creation;
1677
1678 --
1679 --
1680 --
1681 PROCEDURE archive_code(p_assactid IN NUMBER,
1682 p_effective_date IN DATE) IS
1683 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
1684 error_found EXCEPTION;
1685 l_archive_tab action_info_table;
1686 l_tax_rec g_tax_rec;
1687 l_archive_person boolean;
1688 l_archive_addr boolean;
1689 l_archive_data boolean;
1690 l_archive_type VARCHAR2(20);
1691
1692 cursor csr_archive_type is
1693 select report_type
1694 from pay_assignment_actions paa,
1695 pay_payroll_actions ppa
1696 where paa.assignment_action_id = p_assactid
1697 and paa.payroll_action_id = ppa.payroll_action_id;
1698
1699 BEGIN
1700 hr_utility.trace('\n xxxx Test Indicator='||pay_magtape_generic.get_parameter_value('TEST'));
1701 --hr_utility.trace_on(null,'TKP');
1702 fnd_file.put_line(fnd_file.LOG,'Entering: '||l_proc);
1703 --hr_utility.trace('Tushar effective date is '|| to_char(p_effective_date,'DD-MON-YYYY') );
1704 --hr_utility.set_location('Entering: '||l_proc,1);
1705 open csr_archive_type;
1706 fetch csr_archive_type into l_archive_type;
1707 close csr_archive_type;
1708
1709 fetch_tax_rec(p_assactid,p_effective_date,l_tax_rec);
1710
1711 hr_utility.set_location('Fetching person details ',10);
1712 l_archive_person := fetch_person_rec(p_assactid, p_effective_date, l_tax_rec, l_archive_tab(0));
1713
1714 hr_utility.set_location('Fetching address details ',20);
1715 l_archive_addr := fetch_address_rec(l_archive_tab(0).person_id,
1716 l_archive_tab(0).assignment_id,
1717 p_effective_date,
1718 l_archive_tab(1));
1719
1720 hr_utility.set_location('Fetching P45(3) details ',30);
1721 if l_archive_type = 'P45_3' then
1722 l_archive_data := fetch_p45_3_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
1723 /*changes for P45PT_3 start*/
1724 elsif l_archive_type = 'P45PT_3' then
1725 l_archive_data := fetch_p45_3_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
1726 /*changes for P45PT_3 end*/
1727 elsif l_archive_type = 'P46' then
1728 l_archive_data := fetch_p46_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
1729 elsif l_archive_type = 'P46_5' then
1730 l_archive_data := fetch_p46_5_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
1731 elsif l_archive_type = 'P46_PENNOT' then
1732 l_archive_data := fetch_p46p_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
1733 /**** EOY 07-08 ****/
1734 elsif l_archive_type = 'P46_5_PENNOT' then
1735 l_archive_data := fetch_45_46_pennot_rec(p_effective_date,l_tax_rec, l_archive_tab(0),'GB_P46PENNOT',p_assactid,l_archive_tab(2));
1736 end if;
1737
1738
1739 if l_archive_person and l_archive_addr and l_archive_data then
1740 insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
1741 else
1742 fnd_file.put_line(fnd_file.LOG,'Archiving');
1743 raise error_found;
1744 end if;
1745 hr_utility.set_location('Leaving: '||l_proc,999);
1746 EXCEPTION
1747 when error_found then
1748 if l_archive_type = 'P45_3' then
1749 reset_flag('GB_P45_3',p_assactid);
1750 /* changes for P45PT_3 start */
1751 elsif l_archive_type = 'P45PT_3' then
1752 reset_flag('GB_P45_3',p_assactid);
1753 /* changes for P45PT_3 end */
1754 elsif l_archive_type = 'P46' then
1755 reset_flag('GB_P46',p_assactid);
1756 elsif l_archive_type = 'P46_PENNOT' then
1757 reset_flag('GB_P46PENNOT',p_assactid);
1758 elsif l_archive_type = 'P46_5_PENNOT' then
1759 reset_flag('GB_P46PENNOT',p_assactid);
1760 end if;
1761 hr_utility.raise_error;
1762 END archive_code;
1763 --
1764 --
1765 PROCEDURE deinitialization_code(pactid IN NUMBER)
1766 IS
1767 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
1768 l_counter number;
1769
1770 procedure write_header is
1771 l_token varchar2(255);
1772 l_addr1 varchar2(255);
1773 l_addr2 varchar2(255);
1774 l_addr3 varchar2(255);
1775 l_addr4 varchar2(255);
1776 l_form varchar2(40);
1777 l_tax_ref varchar2(20);
1778 l_urgent varchar2(2);
1779 l_test varchar2(2);
1780 l_temp number;
1781
1782 cursor csr_leg_param is
1783 select legislative_parameters para,
1784 fnd_number.number_to_canonical(request_id) control_id,
1785 report_type,
1786 business_group_id
1787 from pay_payroll_actions
1788 where payroll_action_id = pactid;
1789
1790 cursor csr_header_det(p_bus_id number,
1791 p_tax_ref varchar2) is
1792 select nvl(hoi.org_information11,' ') sender_id,
1793 nvl(upper(hoi.org_information2),' ') hrmc_office,
1794 nvl(upper(hoi.org_information4),' ') er_addr,
1795 nvl(upper(hoi.org_information3),' ') er_name
1796 from hr_organization_information hoi
1797 where hoi.organization_id = p_bus_id
1798 and hoi.org_information_context = 'Tax Details References'
1799 and nvl(hoi.org_information10,'UK') = 'UK'
1800 and upper(hoi.org_information1) = upper(p_tax_ref);
1801
1802 l_param csr_leg_param%rowtype;
1803 l_det csr_header_det%rowtype;
1804 begin
1805 open csr_leg_param;
1806 fetch csr_leg_param into l_param;
1807 close csr_leg_param;
1808
1809 l_token := 'TAX_REF';
1810 l_temp := instr(l_param.para,l_token);
1811 l_tax_ref := substr(l_param.para, l_temp + length(l_token) + 1,
1812 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
1813 l_token := 'URGENT';
1814 l_temp := instr(l_param.para,l_token);
1815 l_urgent := substr(l_param.para, l_temp + length(l_token) + 1,
1816 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
1817 l_token := 'TEST';
1818 l_temp := instr(l_param.para,l_token);
1819 l_test := substr(l_param.para, l_temp + length(l_token) + 1,
1820 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
1821
1822 open csr_header_det(l_param.business_group_id, l_tax_ref);
1823 fetch csr_header_det into l_det;
1824 close csr_header_det;
1825
1826 l_addr1 := l_det.er_addr;
1827 if length(l_addr1) > 35 then
1828 l_temp := instr(l_addr1, ',', 34 - length(l_addr1));
1829 if l_temp = 0 then
1830 l_temp := 35;
1831 end if;
1832 l_addr2 := ltrim(substr(l_addr1, 1 + l_temp),' ,');
1833 l_addr1 := substr(l_addr1,1,l_temp);
1834 end if;
1835 if length(l_addr2) > 35 then
1836 l_temp := instr(l_addr2, ',', 34 - length(l_addr2));
1837 if l_temp = 0 then
1838 l_temp := 35;
1839 end if;
1840 l_addr3 := ltrim(substr(l_addr2, 1 + l_temp),' ,');
1841 l_addr2 := substr(l_addr2,1,l_temp);
1842 end if;
1843 if length(l_addr3) > 35 then
1844 l_temp := instr(l_addr3, ',', 34 - length(l_addr3));
1845 if l_temp = 0 then
1846 l_temp := 35;
1847 end if;
1848 l_addr3 := ltrim(substr(l_addr3, 1 + l_temp),' ,');
1849 l_addr4 := substr(l_addr3,1,l_temp);
1850 end if;
1851
1852
1853 if l_param.report_type = 'P45_3' then
1854 l_form := 'P45(3) ( MOVDED 3.0 )';
1855 /* changes for P45PT_3 start */
1856 elsif l_param.report_type = 'P45PT_3' then
1857 l_form := 'P45(3) ( MOVDED 5.0 )';
1858 /* changes for P45PT_3 end */
1859 elsif l_param.report_type = 'P46' then
1860 l_form := 'P46 ( P46 4.0 )';
1861 elsif l_param.report_type = 'P46_PENNOT' then
1862 l_form := 'P46 Pension Notification ( MOVDED 3.0 )';
1863 elsif l_param.report_type = 'P46_5_PENNOT' then
1864 l_form := 'P46 Pension Notification ( MOVDED 5.0 )';
1865 elsif l_param.report_type = 'P46_5' then
1866 l_form := 'P46 ( MOVDED 5.0 )';
1867 end if;
1868
1869 fnd_file.put_line(fnd_file.output,'\nEDI Transmission Report:');
1870 fnd_file.put_line(fnd_file.output,' ');
1871 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form );
1872 fnd_file.put_line(fnd_file.output,rpad('Sender : ',32) || l_det.sender_id);
1873 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
1874 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
1875 fnd_file.put_line(fnd_file.output,rpad('Test Transmission : ',32) || l_test);
1876 fnd_file.put_line(fnd_file.output,rpad('Urgent : ',32) || l_urgent);
1877 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
1878 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
1879 fnd_file.put_line(fnd_file.output,rpad('HRMC Office : ',32) || l_det.hrmc_office);
1880 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
1881 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
1882 if length(l_addr2) > 0 then
1883 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
1884 end if;
1885 if length(l_addr3) > 0 then
1886 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
1887 end if;
1888 if length(l_addr4) > 0 then
1889 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
1890 end if;
1891 end write_header;
1892
1893 procedure write_sub_header(p_type varchar2) is
1894 begin
1895 fnd_file.put_line(fnd_file.output,null);
1896 if p_type = 'E' then
1897 fnd_file.put_line(fnd_file.output,'The following assignments have completed with error');
1898 else
1899 fnd_file.put_line(fnd_file.output,'The following assignments have completed successfully');
1900 end if;
1901 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
1902 rpad('NI Number',11) ||
1903 rpad('Employee Name', 50));
1904 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
1905 rpad('-',10,'-') || ' ' ||
1906 rpad('-',50,'-'));
1907 end write_sub_header;
1908
1909 procedure write_body(p_type varchar2) is
1910 l_count number;
1911 l_temp varchar2(255);
1912 cursor csr_asg is
1913 select /*+ ORDERED */
1914 peo.first_name f_name ,
1915 peo.middle_names m_name,
1916 peo.last_name l_name,
1917 peo.title title,
1918 paf.assignment_number emp_no,
1919 peo.national_identifier ni_no
1920 from pay_payroll_actions pay,
1921 pay_assignment_actions paa,
1922 per_all_assignments_f paf,
1923 per_all_people_f peo
1924 where pay.payroll_action_id = pactid
1925 and paa.payroll_action_id = pay.payroll_action_id
1926 and paa.action_status = p_type
1927 and paf.assignment_id = paa.assignment_id
1928 and peo.person_id = paf.person_id
1929 and pay.effective_date between paf.effective_start_date and paf.effective_end_date
1930 and pay.effective_date between peo.effective_start_date and peo.effective_end_date;
1931 begin
1932 l_count := 0;
1933 for asg_rec in csr_asg loop
1934 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' ||
1935 asg_rec.f_name || ' ' || asg_rec.m_name;
1936 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
1937 rpad(asg_rec.ni_no ,10) || ' ' ||
1938 rpad(l_temp,50));
1939 l_count := l_count + 1;
1940 end loop;
1941 fnd_file.put_line(fnd_file.output,null);
1942 if p_type = 'E' then
1943 fnd_file.put_line(fnd_file.output,'Total Number of assignments completed with error : ' || l_count);
1944 else
1945 fnd_file.put_line(fnd_file.output,'Total Number of assignments completed successfully :' || l_count);
1946 end if;
1947 l_counter := l_counter + l_count;
1948 end write_body;
1949
1950 procedure write_footer is
1951 begin
1952 fnd_file.put_line(fnd_file.output,null);
1953 fnd_file.put_line(fnd_file.output,'Total Number Of Records : ' || l_counter);
1954 end write_footer;
1955 BEGIN
1956 hr_utility.set_location('Entering: '||l_proc,1);
1957 l_counter := 0;
1958 write_header;
1959 write_sub_header('C');
1960 write_body('C');
1961 write_sub_header('E');
1962 write_body('E');
1963 write_footer;
1964 hr_utility.set_location('Leaving: '||l_proc,999);
1965 END deinitialization_code;
1966 --
1967 --
1968 FUNCTION date_validate (c_assignment_action_id NUMBER,
1969 p_mode VARCHAR2,
1970 p_validate_date DATE)
1971 RETURN NUMBER
1972 IS
1973
1974
1975 cursor csr_parameter_info is
1976 select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST_ID'),
1977 pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
1978 /*ppa.effective_date*/
1979 sysdate
1980 from pay_payroll_actions ppa
1981 ,pay_assignment_actions paa
1982 where paa.assignment_action_id = c_assignment_action_id
1983 and ppa.payroll_action_id = paa.payroll_action_id;
1984
1985
1986 l_date_valid DATE;
1987 l_return_valid NUMBER;
1988 l_test_id VARCHAR2(8);
1989 l_test_submission VARCHAR2(1);
1990 l_tax_date DATE;
1991 l_tax_year VARCHAR2(4);
1992 l_tax_year_start DATE ;
1993 BEGIN
1994 l_return_valid := 1;
1995 open csr_parameter_info;
1996 fetch csr_parameter_info into l_test_id,l_test_submission,l_tax_date;
1997 close csr_parameter_info;
1998
1999
2000 l_tax_year := to_char(l_tax_date,'RRRR');
2001 if (l_tax_date > to_date(l_tax_year||'0405','RRRRMMDD')) THEN
2002 l_tax_date := ADD_MONTHS(to_date(l_tax_year||'0405','RRRRMMDD'),12) ; /*tax year end date*/
2003 else
2004 l_tax_date := to_date(l_tax_year||'0405','RRRRMMDD'); /*tax year end date*/
2005 end if;
2006
2007 l_tax_date := fnd_date.canonical_to_date(to_char(l_tax_date,'RRRRMMDD'));
2008
2009 l_tax_year_start := add_months(l_tax_date,-12)+1 ; -- 6804206
2010
2011 l_date_valid := p_validate_date;
2012
2013 if (p_mode = 'LEFT_DATE') then
2014 if (l_date_valid < add_months(l_tax_date,-72)+1) then /*vrn : 36*/
2015 l_return_valid := 0;
2016 else
2017 if (l_test_submission = 'N') then
2018 if (l_date_valid > l_tax_date) then
2019 l_return_valid := 0;
2020 end if;
2021 else
2022 if (l_date_valid > add_months(l_tax_date,12)) then
2023 l_return_valid := 0;
2024 end if;
2025 end if;
2026 end if;
2027 elsif (p_mode = 'PENSION_DATE') then
2028 if (l_test_submission = 'N') then
2029 if (l_date_valid > l_tax_date) then
2030 l_return_valid := 0;
2031 end if;
2032 else
2033 if l_date_valid > add_months(l_tax_date,12) then
2034 l_return_valid := 0;
2035 end if;
2036 end if;
2037 elsif (p_mode = 'HIRE_DATE') then
2038 if (l_test_submission = 'N') then
2039 if (l_date_valid > l_tax_date) then
2040 l_return_valid := 0;
2041 end if;
2042 else
2043 if (l_date_valid > add_months(l_tax_date,12)) then
2044 l_return_valid := 0;
2045 end if;
2046 end if;
2047 --
2048 elsif (p_mode = 'DOB') then
2049 if (l_date_valid > sysdate) then
2050 l_return_valid := 0;
2051 end if;
2052
2053 elsif (p_mode = 'SOY_CHECK') then
2054 if l_date_valid <= l_tax_year_start then
2055 l_return_valid := 0;
2056 end if;
2057 --
2058 end if;
2059
2060 return l_return_valid;
2061 END date_validate;
2062
2063 end PAY_GB_MOVDED_EDI;