[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_GB_RTI_EAS
Source
1 PACKAGE BODY PAY_GB_RTI_EAS as
2 /* $Header: pygbrtieas.pkb 120.1.12020000.14 2013/03/25 11:00:50 rajganga noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_RTI_EAS
10 Purpose:
11 It fetches the live data and archives it for RTI Processes.
12 This is a UK Specific payroll package.
13
14 History:
15 30-Nov-2012 sampmand 115.0 14759083 Created for EAS 2013/14.
16 03-Dec-2012 sampmand 115.2 14759083 added aggregated assignment processing logic.
17 and modified cursor to include terminated employees.
18 06-Dec-2012 sampmand 115.3 14759083 Modified to remove ambiguous column definition.
19 06-Dec-2012 sampmand 115.4 14759083 Modified paye aggregation logic to remove globals.
20 12-Dec-2012 sampmand 115.5 15989066 Modified to correct QA logged bugs.
21 17-Dec-2012 sampmand 115.6 15989066 Modified to fix GSCC errors.
22 19-Dec-2012 sampmand 115.7 15989066 Modified to include validation for effective date.
23 26-Dec-2012 sampmand 115.8 15989066 Modified to remove display of end date for active assignments
24 in warnings section.
25 09-Jan-2013 sampmand 115.13 15989066 Modified the range cursor to
26 to use per_all_people_f instead of per_people_f
27 22-Mar-2013 rajganga 115.14 16469408 Modified for address fetching and validations.
28 25-Mar-2013 rajganga 115.14 16469408 Modified for address fetching and validations.
29 ==========================================================================*/
30
31 g_package CONSTANT VARCHAR2(20):= 'pay_gb_rti_eas.';
32 g_paye_details_id number;
33 g_validation_check VARCHAR2(1) := 'Y';
34 --g_person_id number;
35 --g_part_no number:=1;
36
37 -- Change the code numbers eas
38 /*
39 EAS Data Archive Structure
40 --------------------------------------
41 Action Context Type : PA
42 Action_Info_Category: RTI PAYROLL INFO
43
44 Action_Information4 : Employer Name
45 Action_Information6 : Hmrc Office Number
46 Action_Information7 : Employer Paye Reference
47 Action_Information8: Payroll Product Version
48 Action_Information9: Number of Parts
49 Action_Information10: Unique Part Identifier
50 Action_Information11: Employer AO Reference
51 Action_Information12: Sender ID
52 Action_Information13: Test Indicator
53 Action_Information14: Request ID
54
55
56
57 Action Context Type : AAP
58 Action_Info_Category: GB RTI EMPLOYEE DETAILS
59
60 Action_Information3 : Last Name
61 Action_Information4 : First Name
62 Action_Information5 : Middle Name
63 Action_Information6 : Title
64 Action_Information7 : Ni Number
65 Action_Information8 : Date Of Birth
66 Action_Information9 : Gender
67 Action_Information10 : Aggregate PAYE flag
68 Action_Information11 : Multiple Asg flag
69 Action_Information17 : Employee Number
70 Action_Information18 : Current Employee Flag
71 Action_Information19 : Actual Termination Date
72
73 Action Context Type : AAP
74 Action_Info_Category: ADDRESS DETAILS
75
76 Action_Information5 : Address Line1
77 Action_Information6 : Address Line2
78 Action_Information7 : Address Line3
79 Action_Information8 : Address Line4 : City/Town
80 Action_Information12: Postal Code
81 Action_Information13: Country
82
83
84 Action Context Type : AAP
85 Action_Info_Category: GB RTI ASG DETAILS
86
87 Action_Information3 : Payroll Id
88 Action_Information4 : Irregular Employment Indicator
89 Action_Information5 : Start Date
90 Action_Information6 : End Date
91 Action_Information7: Tax Code
92 Action_Information8: Tax Basis
93 Action_Information10: Expat Indicator
94 Action_Information11: Occupational Pension Indicator
95 Action_Information12: Pay Non Indiv Indicator
96
97 */
98
99
100 -- This table will be used as a transit variable to pass information column values to
101 -- archive api
102 TYPE act_info_rec IS RECORD
103 ( assignment_id number(20)
104 ,person_id number(20)
105 ,effective_date date
106 ,action_info_category varchar2(50)
107 ,act_info1 varchar2(300)
108 ,act_info2 varchar2(300)
109 ,act_info3 varchar2(300)
110 ,act_info4 varchar2(300)
111 ,act_info5 varchar2(300)
112 ,act_info6 varchar2(300)
113 ,act_info7 varchar2(300)
114 ,act_info8 varchar2(300)
115 ,act_info9 varchar2(300)
116 ,act_info10 varchar2(300)
117 ,act_info11 varchar2(300)
118 ,act_info12 varchar2(300)
119 ,act_info13 varchar2(300)
120 ,act_info14 varchar2(300)
121 ,act_info15 varchar2(300)
122 ,act_info16 varchar2(300)
123 ,act_info17 varchar2(300)
124 ,act_info18 varchar2(300)
125 ,act_info19 varchar2(300)
126 ,act_info20 varchar2(300)
127 ,act_info21 varchar2(300)
128 ,act_info22 varchar2(300)
129 ,act_info23 varchar2(300)
130 ,act_info24 varchar2(300)
131 ,act_info25 varchar2(300)
132 ,act_info26 varchar2(300)
133 ,act_info27 varchar2(300)
134 ,act_info28 varchar2(300)
135 ,act_info29 varchar2(300)
136 ,act_info30 varchar2(300)
137 );
138
139 TYPE action_info_table IS TABLE OF act_info_rec INDEX BY BINARY_INTEGER;
140 ---
141 --
142 --
143 -- Function to fetch address details (CONTEXT - ADDRESS DETAILS)
144 FUNCTION fetch_address_rec(p_person_id IN NUMBER,
145 p_assignment_id IN NUMBER,
146 p_effective_date IN DATE,
147 p_addr_rec OUT nocopy act_info_rec) return boolean IS
148
149
150 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_address_rec';
151 l_arch boolean;
152 --Address Details
153 l_address_line1 per_addresses.address_line1%TYPE;
154 l_address_line2 per_addresses.address_line2%TYPE;
155 l_address_line3 per_addresses.address_line3%TYPE;
156 l_address_line4 per_addresses.address_line3%TYPE;
157
158 cursor csr_address is
159 select upper(substr(trim(addr.address_line1),1,35)) addr1,
160 upper(substr(trim(addr.address_line2),1,35)) addr2,
161 upper(substr(trim(addr.address_line3),1,35)) addr3,
162 substr(addr.postal_code,1,10) post_code,
163 upper(substr(trim(addr.town_or_city),1,35)) addr4,
164 upper(substr(trim(addr.country),1,35)) country
165 from per_addresses addr
166 where addr.person_id = p_person_id
167 and ( addr.primary_flag = 'Y'
168 or addr.primary_flag is null)
169 and p_effective_date between nvl(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
170 and nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
171
172
173 l_addr_rec csr_address%rowtype;
174
175 BEGIN
176 hr_utility.set_location('Entering : '||l_proc,111);
177 l_arch := true;
178
179 open csr_address;
180 fetch csr_address into l_addr_rec;
181 close csr_address;
182
183 l_address_line1 := l_addr_rec.addr1;
184 l_address_line2 := l_addr_rec.addr2;
185 l_address_line3 := l_addr_rec.addr3;
186 l_address_line4 := l_addr_rec.addr4;
187
188 -- Swapping from Address line4 to line2
189 if l_address_line3 = ' '
190 then
191 l_address_line3 := l_address_line4;
192 l_address_line4 := ' ';
193 end if;
194
195 if l_address_line2 = ' '
196 then
197 l_address_line2 := l_address_line3;
198 l_address_line3 := l_address_line4;
199 l_address_line4 := ' ';
200 end if;
201
202 if LENGTH(TRIM(l_address_line4)) > 0
203 then
204 l_address_line4 := l_address_line4;
205 else
206 l_address_line4 := ' ';
207 end if;
208
209 if LENGTH(TRIM(l_address_line3)) > 0
210 then
211 l_address_line3 := l_address_line3;
212 else
213 l_address_line3 := l_address_line4;
214 l_address_line4 := ' ';
215 end if;
216
217 if LENGTH(TRIM(l_address_line2)) > 0
218 then
219 l_address_line2 := l_address_line2;
220 else
221 l_address_line2 := l_address_line3;
222 l_address_line3 := l_address_line4;
223 l_address_line4 := ' ';
224 end if;
225
226 -- For Foreign Country, postal code will not be reported , where as Country will be reported
227 -- For GB , postal code will be reported
228 IF l_addr_rec.country = 'GB' THEN
229 if l_addr_rec.post_code <> ' ' or l_addr_rec.post_code is not null then
230 l_addr_rec.country := ' ';
231 end if;
232 else
233 l_addr_rec.post_code:= ' ';
234 end if;
235
236 --Trace Messages
237 hr_utility.trace('Address Line 1 : '||l_address_line1);
238 hr_utility.trace('Address Line 2 : '||l_address_line2);
239 hr_utility.trace('Address Line 3 : '||l_address_line3);
240 hr_utility.trace('Address Line 4 : '||l_address_line4);
241
242 p_addr_rec.assignment_id := p_assignment_id;
243 p_addr_rec.action_info_category := 'ADDRESS DETAILS';
244 p_addr_rec.act_info5 := l_address_line1;
245 p_addr_rec.act_info6 := l_address_line2;
246 p_addr_rec.act_info7 := l_address_line3;
247 p_addr_rec.act_info8 := l_address_line4;
248 p_addr_rec.act_info12 := l_addr_rec.post_code;
249 p_addr_rec.act_info13 := l_addr_rec.country;
250
251 hr_utility.set_location('Leaving: '||l_proc,999);
252 return l_arch;
253
254 END fetch_address_rec;
255 --
256 -- Function to fetch person details (CONTEXT - GB RTI EMPLOYEE DETAILS)
257 FUNCTION fetch_person_rec(p_assactid IN NUMBER,
258 p_effective_date IN DATE,
259 p_person_rec OUT nocopy act_info_rec) return boolean IS
260
261 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
262 l_arch boolean;
263 l_employee_flag varchar2(10);
264 l_start_date date;
265 l_end_date date;
266
267 cursor c_get_employee_flag(asg_id number) is
268 select distinct pap.effective_start_date , pap.effective_end_date , pap.current_employee_flag current_employee_flag
269 from per_all_assignments_f paa,
270 per_all_people_f pap,
271 per_periods_of_service serv
272 where paa.assignment_id = asg_id
273 and pap.person_id = paa.person_id
274 and paa.period_of_service_id = serv.period_of_service_id and
275 (serv.actual_termination_date is not null and serv.actual_termination_date+1 between pap.effective_start_date and pap.effective_end_date);
276
277
278 cursor csr_person_details_active IS
279 SELECT
280 pap.person_id p_person_id,
281 paa.assignment_id,
282 paa.assignment_number emp_no,
283 substr(trim(pap.last_name), 1,35) last_name,
284 substr(trim(pap.first_name), 1,least(decode(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
285 substr(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
286 pap.title title,
287 substr(pap.national_identifier,1,9) national_identifier,
288 pap.date_of_birth date_of_birth,
289 substr(pap.sex,1,1) sex ,
290 decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
291 decode(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
292 current_employee_flag,
293 pap.effective_start_date effective_start_date,
294 serv.actual_termination_date actual_termination_date
295 FROM pay_assignment_actions act,
296 per_all_assignments_f paa,
297 per_all_people_f pap,
298 per_periods_of_service serv,
299 per_assignment_status_types past
300 where act.assignment_action_id = p_assactid
301 and act.assignment_id = paa.assignment_id
302 and paa.assignment_status_type_id = past.assignment_status_type_id
303 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
304 and paa.person_id = pap.person_id
305 and paa.period_of_service_id = serv.period_of_service_id
306 and p_effective_date between pap.effective_start_date and pap.effective_end_date
307 and p_effective_date between paa.effective_start_date and paa.effective_end_date
308 order by pap.effective_start_date desc;
309
310
311 cursor csr_person_details_terminated IS
312 SELECT
313 pap.person_id p_person_id,
314 paa.assignment_id,
315 paa.assignment_number emp_no,
316 substr(trim(pap.last_name), 1,35) last_name,
317 substr(trim(pap.first_name), 1,least(decode(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
318 substr(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
319 pap.title title,
320 substr(pap.national_identifier,1,9) national_identifier,
321 pap.date_of_birth date_of_birth,
322 substr(pap.sex,1,1) sex ,
323 decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
324 decode(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
325 current_employee_flag,
326 pap.effective_start_date effective_start_date,
327 serv.actual_termination_date actual_termination_date
328 FROM pay_assignment_actions act,
329 per_all_assignments_f paa,
330 per_all_people_f pap,
331 per_periods_of_service serv,
332 per_assignment_status_types past
333 where act.assignment_action_id = p_assactid
334 and act.assignment_id = paa.assignment_id
335 and paa.assignment_status_type_id = past.assignment_status_type_id
336 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
337 and paa.person_id = pap.person_id
338 and paa.period_of_service_id = serv.period_of_service_id
339 order by pap.effective_start_date desc;
340
341 l_person_rec csr_person_details_active%rowtype;
342 BEGIN
343
344 hr_utility.set_location('Entering: '||l_proc,1);
345 l_arch := true;
346
347 open csr_person_details_active;
348 fetch csr_person_details_active into l_person_rec;
349
350 if csr_person_details_active%notfound then
351 open csr_person_details_terminated;
352 fetch csr_person_details_terminated into l_person_rec;
353 if csr_person_details_terminated%notfound then
354 l_arch := false;
355 end if;
356 if l_person_rec.actual_termination_date is null then
357 l_person_rec.current_employee_flag := 'Y';
358 end if;
359 close csr_person_details_terminated;
360 end if;
361 close csr_person_details_active;
362
363 hr_utility.trace('Termination date ' ||l_person_rec.actual_termination_date);
364 hr_utility.trace('Start date ' ||l_person_rec.effective_start_date);
365 hr_utility.trace('Current employee flag' ||l_person_rec.current_employee_flag);
366
367 p_person_rec.person_id := l_person_rec.p_person_id;
368 p_person_rec.assignment_id := l_person_rec.assignment_id;
369 p_person_rec.action_info_category := 'GB RTI EMPLOYEE DETAILS';
370 p_person_rec.act_info3 := l_person_rec.last_name;
371 p_person_rec.act_info4 := l_person_rec.first_name;
372 p_person_rec.act_info5 := l_person_rec.middle_name;
373 p_person_rec.act_info6 := l_person_rec.title;
374 p_person_rec.act_info7 := l_person_rec.national_identifier;
375 p_person_rec.act_info8 := to_char(l_person_rec.date_of_birth,'YYYY-MM-DD');
376 p_person_rec.act_info9 := l_person_rec.sex;
377 p_person_rec.act_info10 := l_person_rec.agg_paye_flag;
378 p_person_rec.act_info11 := l_person_rec.multiple_asg_flag;
379 p_person_rec.act_info17 := l_person_rec.emp_no;
380 p_person_rec.act_info18 := l_person_rec.current_employee_flag;
381 p_person_rec.act_info19 := to_char(l_person_rec.actual_termination_date,'YYYY-MM-DD');
382
383 hr_utility.set_location('Leaving: '||l_proc,999);
384 return l_arch;
385 END fetch_person_rec;
386 --
387
388 /* EAS 2013 Changes */
389 /*
390 Returns expat_indicator value for the person from the source configured in Configuration
391 values page for this BG.
392 */
393 FUNCTION get_expat_indicator(
394 p_assignment_id NUMBER,p_business_group_id number,p_effective_start_date date)
395 RETURN VARCHAR2
396 IS
397 l_config_values PQP_UTILITIES.t_config_values;
398 l_source_name VARCHAR2(100);
399 l_context_name VARCHAR2(100);
400 l_column_name VARCHAR2(100);
401 l_function_name VARCHAR2(100);
402 l_sqlstr VARCHAR2(1000);
403 l_expat_indicator VARCHAR2(20) := NULL;
404 l_package VARCHAR2(100);
405 l_effective_date date;
406
407 /* EAS 2013 Changes */
408 cursor csr_get_extra_asg_details is
409 SELECT AEI_INFORMATION5
410 FROM PER_ASSIGNMENT_EXTRA_INFO
411 WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
412 AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
413 AND assignment_id = p_assignment_id;
414
415 begin
416
417 -- Source 1
418
419 -- First check in the assignment form
420 -- Assignment EIT Context='RTI Information'
421
422 open csr_get_extra_asg_details;
423 fetch csr_get_extra_asg_details into l_expat_indicator;
424 close csr_get_extra_asg_details;
425
426 -- Source 2
427 -- Configuration values for expat_indicator
428 if l_expat_indicator is null then
429 hr_utility.trace('in config ');
430
431 -- Use below API to get the config values for the given context in the business group
432 PQP_UTILITIES.get_config_type_values( p_configuration_type => 'PAY_GB_FPS_EXPAT_INDICATOR' ,
433 p_business_group_id => p_business_group_id ,
434 p_legislation_code => 'GB' ,
435 p_tab_config_values => l_config_values );
436
437 IF l_config_values.COUNT > 0 THEN
438 l_source_name := l_config_values(l_config_values.FIRST).pcv_information1;
439 l_context_name := l_config_values(l_config_values.FIRST).pcv_information4;
440 l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
441 l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
442
443 hr_utility.trace('l_source_name for expat_indicator: ' || l_source_name);
444 hr_utility.trace('l_context_name for expat_indicator: ' || l_context_name);
445 hr_utility.trace('l_column_name for expat_indicator: ' || l_column_name);
446 hr_utility.trace('l_function_name for expat_indicator: ' || l_function_name);
447
448 /**
449
450 - Assignment ==> Extra details
451 - Assignment ==> DFF
452 - Formula Function
453 **/
454 IF l_source_name = 'Assignment Developer DF' THEN
455 l_sqlstr := 'select ' || l_column_name ||
456 ' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
457 begin
458
459 hr_utility.trace('string '||l_sqlstr);
460 EXECUTE immediate l_sqlstr INTO l_expat_indicator USING p_assignment_id,l_context_name;
461
462 exception
463 WHEN OTHERS THEN
464 hr_utility.trace('Exception in expat_indicator.Assignment Developer DFF.');
465 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
466 return null;
467 end;
468
469 elsif l_source_name = 'PER_ASSIGNMENTS' THEN
470
471 l_sqlstr := 'select ' || l_column_name ||
472 ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
473 ' and '||p_effective_start_date||' between effective_start_date and effective_end_date' ;
474 begin
475 EXECUTE immediate l_sqlstr INTO l_expat_indicator USING p_assignment_id,l_context_name;
476
477 exception
478 WHEN OTHERS THEN
479 hr_utility.trace('Exception in expat_indicator.PER_ASSIGNMENTS.');
480 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
481 return null;
482 end;
483
484 elsif l_source_name = 'Function' THEN
485
486 begin
487 SELECT definition
488 INTO l_package
489 FROM ff_functions
490 WHERE name = l_function_name
491 and business_group_id = p_business_group_id;
492
493
494 hr_utility.trace('l_package'||l_package);
495
496 l_sqlstr :=
497 'SELECT '
498 || l_package
499 || ' (:p_assignment_id,:p_eff_date) '
500 || 'FROM DUAL';
501 EXECUTE IMMEDIATE l_sqlstr into l_expat_indicator USING p_assignment_id,p_effective_start_date;
502
503 hr_utility.trace('Function value l_expat_indicator '||l_expat_indicator);
504
505 exception
506 WHEN OTHERS THEN
507 hr_utility.trace('Exception in expat_indicator.Function.');
508 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
509 return null;
510 end;
511
512 END IF;
513
514 END IF;
515 END IF;--source 2 end
516
517 hr_utility.trace(' l_expat_indicator : ' || l_expat_indicator);
518 hr_utility.trace('Leaving get_expat_indicator');
519
520 return l_expat_indicator;
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 hr_utility.trace('Exception in get_expat_indicator end of function block.');
525 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
526 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
527 return null;
528 END get_expat_indicator;
529
530
531 /* EAS 2013 Changes */
532 /*
533 Returns occ_pension_indicator value for the person from the source configured in Configuration
534 values page for this BG.
535 */
536 FUNCTION get_occ_pension_indicator(
537 p_assignment_id NUMBER,p_business_group_id number,p_effective_start_date date)
538 RETURN VARCHAR2
539 IS
540 l_config_values PQP_UTILITIES.t_config_values;
541 l_source_name VARCHAR2(100);
542 l_context_name VARCHAR2(100);
543 l_column_name VARCHAR2(100);
544 l_function_name VARCHAR2(100);
545 l_sqlstr VARCHAR2(1000);
546 l_occ_pension_indicator VARCHAR2(20) := NULL;
547 l_package VARCHAR2(100);
548 l_effective_date date;
549
550 /* EAS 2013 Changes */
551 cursor csr_get_extra_asg_details is
552 SELECT AEI_INFORMATION6
553 FROM PER_ASSIGNMENT_EXTRA_INFO
554 WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
555 AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
556 AND assignment_id = p_assignment_id;
557
558 begin
559
560 -- Source 1
561
562 -- First check in the assignment form
563 -- Assignment EIT Context='RTI Information'
564
565 open csr_get_extra_asg_details;
566 fetch csr_get_extra_asg_details into l_occ_pension_indicator;
567 close csr_get_extra_asg_details;
568
569 -- Source 2
570 -- Configuration values for occ_pension_indicator
571 if l_occ_pension_indicator is null then
572 hr_utility.trace('in config ');
573
574 -- Use below API to get the config values for the given context in the business group
575 PQP_UTILITIES.get_config_type_values( p_configuration_type => 'PAY_GB_FPS_OCC_PENSION_IND' ,
576 p_business_group_id => p_business_group_id ,
577 p_legislation_code => 'GB' ,
578 p_tab_config_values => l_config_values );
579
580 IF l_config_values.COUNT > 0 THEN
581 l_source_name := l_config_values(l_config_values.FIRST).pcv_information1;
582 l_context_name := l_config_values(l_config_values.FIRST).pcv_information4;
583 l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
584 l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
585
586 hr_utility.trace('l_source_name for occ_pension_indicator: ' || l_source_name);
587 hr_utility.trace('l_context_name for occ_pension_indicator: ' || l_context_name);
588 hr_utility.trace('l_column_name for occ_pension_indicator: ' || l_column_name);
589 hr_utility.trace('l_function_name for occ_pension_indicator: ' || l_function_name);
590
591 /**
592
593 - Assignment ==> Extra details
594 - Assignment ==> DFF
595 - Formula Function
596 **/
597 IF l_source_name = 'Assignment Developer DF' THEN
598 l_sqlstr := 'select ' || l_column_name ||
599 ' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
600 begin
601
602 hr_utility.trace('string '||l_sqlstr);
603 EXECUTE immediate l_sqlstr INTO l_occ_pension_indicator USING p_assignment_id,l_context_name;
604
605 exception
606 WHEN OTHERS THEN
607 hr_utility.trace('Exception in occ_pension_indicator.Assignment Developer DFF.');
608 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
609 return null;
610 end;
611
612 elsif l_source_name = 'PER_ASSIGNMENTS' THEN
613
614 l_sqlstr := 'select ' || l_column_name ||
615 ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
616 ' and '||p_effective_start_date||' between effective_start_date and effective_end_date' ;
617 begin
618 EXECUTE immediate l_sqlstr INTO l_occ_pension_indicator USING p_assignment_id,l_context_name;
619
620 exception
621 WHEN OTHERS THEN
622 hr_utility.trace('Exception in occ_pension_indicator.PER_ASSIGNMENTS.');
623 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
624 return null;
625 end;
626
627 elsif l_source_name = 'Function' THEN
628
629 begin
630 SELECT definition
631 INTO l_package
632 FROM ff_functions
633 WHERE name = l_function_name
634 and business_group_id = p_business_group_id;
635
636
637 hr_utility.trace('l_package'||l_package);
638
639 l_sqlstr :=
640 'SELECT '
641 || l_package
642 || ' (:p_assignment_id,:p_eff_date) '
643 || 'FROM DUAL';
644 EXECUTE IMMEDIATE l_sqlstr into l_occ_pension_indicator USING p_assignment_id,p_effective_start_date;
645
646 hr_utility.trace('Function value l_occ_pension_indicator '||l_occ_pension_indicator);
647
648 exception
649 WHEN OTHERS THEN
650 hr_utility.trace('Exception in occ_pension_indicator.Function.');
651 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
652 return null;
653 end;
654
655 END IF;
656
657 END IF;
658 END IF;--source 2 end
659
660 hr_utility.trace(' l_occ_pension_indicator : ' || l_occ_pension_indicator);
661 hr_utility.trace('Leaving get_occ_pension_indicator');
662
663 return l_occ_pension_indicator;
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 hr_utility.trace('Exception in get_occ_pension_indicator end of function block.');
668 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
669 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
670 return null;
671 END get_occ_pension_indicator;
672
673 /* EAS 2013 Changes */
674 /*
675 Returns pay_non_indiv_ind value for the person from the source configured in Configuration
676 values page for this BG.
677 */
678 FUNCTION get_pay_non_indiv_ind(
679 p_assignment_id NUMBER,p_business_group_id number,p_effective_start_date date)
680 RETURN VARCHAR2
681 IS
682 l_config_values PQP_UTILITIES.t_config_values;
683 l_source_name VARCHAR2(100);
684 l_context_name VARCHAR2(100);
685 l_column_name VARCHAR2(100);
686 l_function_name VARCHAR2(100);
687 l_sqlstr VARCHAR2(1000);
688 l_pay_non_indiv_ind VARCHAR2(20) := NULL;
689 l_package VARCHAR2(100);
690 l_effective_date date;
691
692 /* EAS 2013 Changes */
693 cursor csr_get_extra_asg_details is
694 SELECT AEI_INFORMATION7
695 FROM PER_ASSIGNMENT_EXTRA_INFO
696 WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
697 AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
698 AND assignment_id = p_assignment_id;
699
700 begin
701
702 -- Source 1
703
704 -- First check in the assignment form
705 -- Assignment EIT Context='RTI Information'
706
707 open csr_get_extra_asg_details;
708 fetch csr_get_extra_asg_details into l_pay_non_indiv_ind;
709 close csr_get_extra_asg_details;
710
711 -- Source 2
712 -- Configuration values for pay_non_indiv_ind
713 if l_pay_non_indiv_ind is null then
714 hr_utility.trace('in config ');
715
716 -- Use below API to get the config values for the given context in the business group
717 PQP_UTILITIES.get_config_type_values( p_configuration_type => 'PAY_GB_FPS_PAY_NON_INDIV_IND' ,
718 p_business_group_id => p_business_group_id ,
719 p_legislation_code => 'GB' ,
720 p_tab_config_values => l_config_values );
721
722 IF l_config_values.COUNT > 0 THEN
723 l_source_name := l_config_values(l_config_values.FIRST).pcv_information1;
724 l_context_name := l_config_values(l_config_values.FIRST).pcv_information4;
725 l_column_name := l_config_values(l_config_values.FIRST).pcv_information5;
726 l_function_name := l_config_values(l_config_values.FIRST).pcv_information6;
727
728 hr_utility.trace('l_source_name for pay_non_indiv_ind: ' || l_source_name);
729 hr_utility.trace('l_context_name for pay_non_indiv_ind: ' || l_context_name);
730 hr_utility.trace('l_column_name for pay_non_indiv_ind: ' || l_column_name);
731 hr_utility.trace('l_function_name for pay_non_indiv_ind: ' || l_function_name);
732
733 /**
734
735 - Assignment ==> Extra details
736 - Assignment ==> DFF
737 - Formula Function
738 **/
739 IF l_source_name = 'Assignment Developer DF' THEN
740 l_sqlstr := 'select ' || l_column_name ||
741 ' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
742 begin
743
744 hr_utility.trace('string '||l_sqlstr);
745 EXECUTE immediate l_sqlstr INTO l_pay_non_indiv_ind USING p_assignment_id,l_context_name;
746
747 exception
748 WHEN OTHERS THEN
749 hr_utility.trace('Exception in pay_non_indiv_ind.Assignment Developer DFF.');
750 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
751 return null;
752 end;
753
754 elsif l_source_name = 'PER_ASSIGNMENTS' THEN
755
756 l_sqlstr := 'select ' || l_column_name ||
757 ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
758 ' and '||p_effective_start_date||' between effective_start_date and effective_end_date' ;
759
760 begin
761 EXECUTE immediate l_sqlstr INTO l_pay_non_indiv_ind USING p_assignment_id,l_context_name;
762
763 exception
764 WHEN OTHERS THEN
765 hr_utility.trace('Exception in pay_non_indiv_ind.PER_ASSIGNMENTS.');
766 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
767 return null;
768 end;
769
770 elsif l_source_name = 'Function' THEN
771
772 begin
773 SELECT definition
774 INTO l_package
775 FROM ff_functions
776 WHERE name = l_function_name
777 and business_group_id = p_business_group_id;
778
779
780 hr_utility.trace('l_package'||l_package);
781
782 l_sqlstr :=
783 'SELECT '
784 || l_package
785 || ' (:p_assignment_id,:p_eff_date) '
786 || 'FROM DUAL';
787 EXECUTE IMMEDIATE l_sqlstr into l_pay_non_indiv_ind USING p_assignment_id,p_effective_start_date;
788
789 hr_utility.trace('Function value l_pay_non_indiv_ind '||l_pay_non_indiv_ind);
790
791 exception
792 WHEN OTHERS THEN
793 hr_utility.trace('Exception in pay_non_indiv_ind.Function.');
794 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
795 return null;
796 end;
797
798 END IF;
799
800 END IF;
801 END IF;--source 2 end
802
803 hr_utility.trace(' l_pay_non_indiv_ind : ' || l_pay_non_indiv_ind);
804 hr_utility.trace('Leaving get_pay_non_indiv_ind');
805
806 return l_pay_non_indiv_ind;
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 hr_utility.trace('Exception in get_pay_non_indiv_ind end of function block.');
811 hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
812 fnd_file.put_line(fnd_file.LOG,'Exception:' || SQLCODE || ' - ' || SQLERRM);
813 return null;
814 END get_pay_non_indiv_ind;
815
816 -- Function to fetch Assignment details (CONTEXT - GB_RTI_ASG_DETAILS)
817 FUNCTION fetch_eas_asg_rec(p_effective_date IN DATE,
818 p_assactid IN NUMBER,
819 p_person_rec IN act_info_rec,
820 p_eas_rec OUT nocopy act_info_rec) return boolean IS
821
822 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_eas_asg_rec';
823 l_arch boolean;
824 l_irregular_emp_ind varchar2(1) := 'N';
825 l_expat_indicator varchar2(1);
826 l_occ_pension_indicator varchar2(1);
827 l_pay_non_indiv_ind varchar2(1);
828 l_tax_screen_value varchar2(60);
829 l_taxbasis_screen_value varchar2(60);
830 l_active_flag varchar2(1) := 'N';
831 l_effective_date date;
832 l_asg_no varchar2(100);
833 l_tax_ref varchar2(100);
834 l_asg_id number;
835 l_prev_asg_id number;
836 l_eff_date date;
837 l_eff_start_date date;
838 l_eff_end_date date;
839 l_final_eff_start_date date;
840 l_final_eff_end_date date;
841 l_tax_year_start date;
842 l_paye_id number;
843 l_tax_code_ivid number;
844 l_tax_basis_ivid number;
845 l_effective_start_date date;
846
847 l_business_group_id number;
848
849
850
851 cursor csr_get_asg_no is
852 select assignment_number,
853 paaf.effective_start_date
854 from per_all_assignments_f paaf
855 where assignment_id = p_person_rec.assignment_id
856 and paaf.effective_start_date =
857 (select max(paaf1.effective_start_date)
858 from per_all_assignments_f paaf1 where
859 paaf1.assignment_id = paaf.assignment_id
860 and paaf1.assignment_type = 'E'
861 and paaf1.effective_start_date <= l_eff_date
862 );
863
864 cursor csr_tax_code_basis is
865 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
866 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis
867 FROM pay_element_entries_f e,
868 pay_element_entry_values_f v,
869 pay_input_values_f iv,
870 pay_element_links_f link
871 WHERE e.assignment_id = p_person_rec.assignment_id
872 AND link.element_type_id = g_paye_details_id
873 AND e.element_link_id = link.element_link_id
874 AND e.element_entry_id = v.element_entry_id
875 AND iv.input_value_id = v.input_value_id
876 AND iv.legislation_code = 'GB'
877 AND l_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
878 AND l_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date
879 AND l_effective_date BETWEEN v.effective_start_date AND v.effective_end_date
880 AND l_effective_date between e.effective_start_date and e.effective_end_date;
881
882 cursor get_agg_start_end_dates is
883 select
884 pay_gb_eoy_archive.get_agg_active_start(p_person_rec.assignment_id,l_tax_ref,l_effective_date) start_date,
885 pay_gb_eoy_archive.get_agg_active_end(p_person_rec.assignment_id,l_tax_ref,l_effective_date) end_date
886 from dual;
887
888 cursor csr_get_irregular_details is
889 SELECT AEI_INFORMATION3
890 FROM PER_ASSIGNMENT_EXTRA_INFO
891 WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
892 AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
893 AND assignment_id = p_person_rec.assignment_id;
894
895
896
897
898 cursor csr_parameter_info is
899 select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
900 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
901 business_group_id
902 from pay_payroll_actions pact,
903 pay_assignment_actions paa
904 where paa.assignment_action_id = p_assactid
905 and pact.payroll_action_id = paa.payroll_action_id;
906
907 cursor csr_check_cont_emp(p_person_id number,p_tax_ref varchar2) is
908 select min(paaf.EFFECTIVE_START_DATE) , max(paaf1.EFFECTIVE_END_DATE) , max(paaf.assignment_id) l_prev_asg_id
909 from
910 per_all_people_f peo,
911 per_all_people_f peo1,
912 per_all_assignments_f paaf,
913 per_all_assignments_f paaf1,
914 per_assignment_status_types past,
915 per_assignment_status_types past1,
916 pay_all_payrolls_f papf ,
917 pay_all_payrolls_f papf1,
918 hr_soft_coding_keyflex flex
919 where
920 peo.person_id = p_person_id
921 and paaf.person_id = peo.person_id
922 and paaf.assignment_status_type_id = past.assignment_status_type_id
923 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
924 and papf.payroll_id = paaf.payroll_id
925 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
926 and upper(p_tax_ref) = upper(flex.segment1)
927 and peo1.person_id = p_person_id
928 and paaf1.person_id = peo1.person_id
929 and paaf1.assignment_id = p_person_rec.assignment_id
930 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
931 and past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
932 and paaf.assignment_type = 'E'
933 and paaf1.assignment_type = 'E'
934 and papf1.payroll_id = paaf1.payroll_id
935 and flex.SOFT_CODING_KEYFLEX_ID = papf1.SOFT_CODING_KEYFLEX_ID
936 and upper(p_tax_ref) = upper(flex.segment1)
937 and paaf.EFFECTIVE_END_DATE = paaf1.EFFECTIVE_START_DATE - 1
938 and paaf.assignment_id <> paaf1.assignment_id
939 and exists
940 (select '1' from per_all_assignments_f paafs,
941 per_assignment_status_types pasts
942 where paafs.assignment_id = paaf.assignment_id
943 and paafs.effective_start_date = paaf1.effective_start_date
944 and paafs.assignment_status_type_id = pasts.assignment_status_type_id
945 and pasts.per_system_status in ('TERM_ASSIGN')
946 ) ;
947
948
949 cursor csr_get_start_end_dates(p_eff_date date,p_tax_ref varchar2) is
950 select paaf.effective_start_date,paaf.effective_end_date
951 from
952 per_all_assignments_f paaf,
953 pay_all_payrolls_f papf ,
954 hr_soft_coding_keyflex flex,
955 per_assignment_status_types past
956 where
957 paaf.assignment_id = p_person_rec.assignment_id
958 and papf.payroll_id = paaf.payroll_id
959 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
960 and upper(p_tax_ref) = upper(flex.segment1)
961 and paaf.assignment_status_type_id = past.assignment_status_type_id
962 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
963 and paaf.effective_start_date =
964 (select max(paaf1.effective_start_date) from per_all_assignments_f paaf1,per_assignment_status_types past1
965 where paaf1.assignment_id = paaf.assignment_id
966 and paaf1.assignment_type = 'E'
967 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
968 and past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
969 and paaf1.effective_start_date <= p_eff_date
970 )
971 and p_eff_date between papf.effective_start_date and papf.effective_end_date;
972
973
974 BEGIN
975 hr_utility.set_location('Entering: '||l_proc,1);
976 l_arch := true;
977
978 SELECT distinct element_type_id
979 into g_paye_details_id
980 FROM pay_element_types_f
981 WHERE element_name = 'PAYE Details';
982
983 open csr_parameter_info;
984 fetch csr_parameter_info into l_tax_ref,l_eff_date,l_business_group_id;
985 close csr_parameter_info;
986
987
988 open csr_check_cont_emp(p_person_rec.person_id,l_tax_ref);
989 fetch csr_check_cont_emp into l_final_eff_start_date,l_final_eff_end_date,l_prev_asg_id;
990 if l_final_eff_start_date is not null then
991 close csr_check_cont_emp;
992 l_final_eff_start_date := get_eff_start_date(l_prev_asg_id,l_final_eff_start_date,l_tax_ref);
993 l_final_eff_end_date := get_eff_end_date(p_person_rec.assignment_id,l_final_eff_end_date,l_tax_ref);
994 else
995 close csr_check_cont_emp;
996 open csr_get_start_end_dates(l_eff_date,l_tax_ref);
997 fetch csr_get_start_end_dates into l_eff_start_date,l_eff_end_date;
998 if csr_get_start_end_dates%found then
999 l_final_eff_start_date := get_eff_start_date(p_person_rec.assignment_id,l_eff_start_date,l_tax_ref);
1000 l_final_eff_end_date := get_eff_end_date(p_person_rec.assignment_id,l_eff_end_date,l_tax_ref);
1001 end if;
1002 close csr_get_start_end_dates;
1003 end if;
1004
1005
1006 l_effective_date := p_effective_date;
1007
1008
1009 -- To fetch Terminated Employee Details
1010 if p_person_rec.act_info18 is null or p_person_rec.act_info18 <> 'Y' then
1011 l_effective_date := least(l_eff_date,(to_date(substr(p_person_rec.act_info19,1,10),'YYYY-MM-DD')));
1012 hr_utility.trace('Effective date : '||l_eff_date||' Termination Date : '||p_person_rec.act_info19);
1013 hr_utility.trace('l_effective_date : '||l_effective_date);
1014 else
1015 l_effective_date := least (p_effective_date, l_final_eff_end_date);
1016 hr_utility.trace('l_effective_date : '||l_effective_date);
1017 end if;
1018
1019 open csr_get_asg_no;
1020 fetch csr_get_asg_no into l_asg_no,l_effective_start_date;
1021 close csr_get_asg_no;
1022
1023 open csr_tax_code_basis;
1024 fetch csr_tax_code_basis into l_tax_screen_value,l_taxbasis_screen_value;
1025 close csr_tax_code_basis;
1026
1027 -- To get Agg Start and End date
1028 if p_person_rec.act_info10 = 'Y' then
1029 open get_agg_start_end_dates;
1030 fetch get_agg_start_end_dates into l_final_eff_start_date, l_final_eff_end_date;
1031 close get_agg_start_end_dates;
1032 end if;
1033
1034 -- To fetch Terminated Employee Details
1035 if p_person_rec.act_info18 is null or p_person_rec.act_info18 <> 'Y' then
1036 l_final_eff_end_date := l_effective_date;
1037 end if;
1038
1039
1040 -- Trace Messages
1041 hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
1042 hr_utility.set_location('Asg no : '||l_asg_no,20);
1043 hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
1044 hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
1045 hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
1046 hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
1047
1048 -- If tax basis is non cumulative('N') then making it as 'Y' else ' '
1049 if l_taxbasis_screen_value = 'N' then
1050 l_taxbasis_screen_value := 'Y';
1051 else
1052 l_taxbasis_screen_value := ' ';
1053 end if;
1054
1055 -- To Fetch Irregular Employment Indicator from EIT
1056 open csr_get_irregular_details;
1057 fetch csr_get_irregular_details into l_irregular_emp_ind;
1058 close csr_get_irregular_details;
1059
1060 /* EAS 2013 Changes */
1061 -- To Fetch Expat_Indicator,Occ_Pension_Indicator and Pay_Non_Indiv_Ind
1062 l_expat_indicator := get_expat_indicator(p_person_rec.assignment_id,l_business_group_id,l_effective_start_date);
1063
1064 l_occ_pension_indicator := get_occ_pension_indicator(p_person_rec.assignment_id,l_business_group_id,l_effective_start_date);
1065
1066 l_pay_non_indiv_ind := get_pay_non_indiv_ind(p_person_rec.assignment_id,l_business_group_id,l_effective_start_date);
1067
1068 -- if end date is 31 Dec 4712 make it as null
1069 if l_final_eff_end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') then
1070 l_final_eff_end_date := null;
1071 End if;
1072
1073 --if Termination Date is in future make it as null
1074 if l_final_eff_end_date > l_eff_date then
1075 l_final_eff_end_date := null;
1076 end if;
1077
1078 p_eas_rec.assignment_id := p_person_rec.assignment_id;
1079 p_eas_rec.action_info_category := 'GB RTI ASG DETAILS';
1080 p_eas_rec.act_info3 := l_asg_no;
1081 p_eas_rec.act_info4 := l_irregular_emp_ind;
1082 p_eas_rec.act_info5 := to_char(l_final_eff_start_date,'YYYY-MM-DD');
1083 p_eas_rec.act_info6 := to_char(l_final_eff_end_date,'YYYY-MM-DD');
1084 p_eas_rec.act_info7 := l_tax_screen_value;
1085 p_eas_rec.act_info8 := l_taxbasis_screen_value;
1086 p_eas_rec.act_info10 := l_expat_indicator;
1087 p_eas_rec.act_info11 := l_occ_pension_indicator;
1088 p_eas_rec.act_info12 := l_pay_non_indiv_ind;
1089
1090
1091 hr_utility.set_location('Leaving: '||l_proc,999);
1092 return l_arch;
1093 END fetch_eas_asg_rec;
1094 --
1095 --
1096
1097
1098
1099 PROCEDURE populate_run_msg(
1100 p_assignment_action_id IN NUMBER
1101 ,p_message_text IN varchar2
1102 )
1103 IS
1104 PRAGMA AUTONOMOUS_TRANSACTION;
1105 BEGIN
1106 /* EAS 2013 Changes */
1107 if (instr(p_message_text,'is aggregated & therefore not included on the EDI file') = 0) then
1108
1109 INSERT INTO pay_message_lines(line_sequence,
1110 payroll_id,
1111 message_level,
1112 source_id,
1113 source_type,
1114 line_text)
1115 VALUES(
1116 pay_message_lines_s.nextval
1117 ,100
1118 ,'F'
1119 ,p_assignment_action_id
1120 ,'A'
1121 ,substr(p_message_text,1,240)
1122 );
1123 else
1124 INSERT INTO pay_message_lines(line_sequence,
1125 payroll_id,
1126 message_level,
1127 source_id,
1128 source_type,
1129 line_text)
1130 VALUES(
1131 pay_message_lines_s.nextval
1132 ,200
1133 ,'W'
1134 ,p_assignment_action_id
1135 ,'A'
1136 ,substr(p_message_text,1,240)
1137 );
1138 end if;
1139
1140 COMMIT;
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 hr_utility.trace('Error occured in populate_run_msg');
1144 RAISE;
1145 END populate_run_msg;
1146
1147 -- Function to validate assignment details for EAS report
1148 PROCEDURE eas_asg_etext_validations(p_assactid IN NUMBER,
1149 p_effective_date IN DATE,
1150 p_tab_rec_data IN action_info_table,
1151 edi_validation_fail out nocopy varchar2) IS
1152
1153 CURSOR get_effective_date IS
1154 SELECT ppa.effective_date
1155 FROM pay_payroll_actions ppa, pay_assignment_actions paa
1156 WHERE ppa.payroll_action_id = paa.payroll_action_id
1157 AND paa.assignment_action_id = p_assactid;
1158
1159 l_proc CONSTANT VARCHAR2(50):= g_package||'eas_asg_etext_validations';
1160 l_ovn number;
1161 l_action_id number;
1162
1163 l_assignment_number per_assignments_f.assignment_number%TYPE;
1164 l_payroll_id varchar2(100);
1165 l_process_type_in_use varchar2(100);
1166 l_tax_code_in_use varchar2(100);
1167 l_tax_basis_in_use varchar2(100);
1168 l_msg_value varchar2(100);
1169 l_eff_date date;
1170 l_eas_start_asg_date date;
1171 l_eas_end_asg_date date;
1172 l_expat_indicator varchar2(100);
1173 l_occ_pension_indicator varchar2(100);
1174 l_pay_non_indiv_ind varchar2(100);
1175
1176 BEGIN
1177 hr_utility.set_location('Entering: '||l_proc,1);
1178
1179 edi_validation_fail := 'N'; -- Initialized validation fail to N
1180 l_assignment_number := p_tab_rec_data(0).act_info17;
1181 l_payroll_id := p_tab_rec_data(2).act_info3;
1182 l_process_type_in_use := p_tab_rec_data(2).act_info4;
1183 l_tax_code_in_use := p_tab_rec_data(2).act_info7;
1184 l_tax_basis_in_use := p_tab_rec_data(2).act_info8;
1185 l_eas_start_asg_date := (to_date(substr(p_tab_rec_data(2).act_info5,1,10),'YYYY-MM-DD'));
1186 l_eas_end_asg_date := (to_date(substr(p_tab_rec_data(2).act_info6,1,10),'YYYY-MM-DD'));
1187 /* EAS 2013 Changes */
1188 l_expat_indicator := p_tab_rec_data(2).act_info10;
1189 l_occ_pension_indicator := p_tab_rec_data(2).act_info11;
1190 l_pay_non_indiv_ind := p_tab_rec_data(2).act_info12;
1191
1192 -- Trace Messages
1193 hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
1194 hr_utility.set_location('Process type : '||l_process_type_in_use,20);
1195 hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
1196 hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
1197 hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
1198 hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
1199 hr_utility.set_location('Expat Indicator : '||l_expat_indicator,70);
1200 hr_utility.set_location('occ pension Indicator : '||l_occ_pension_indicator,80);
1201 hr_utility.set_location('pay non indiv ind : '||l_pay_non_indiv_ind,90);
1202
1203
1204 IF ((l_payroll_id <> ' ' and l_payroll_id IS NOT NULL)
1205 and validate_input(l_payroll_id,'PAYROLLID') > 0) THEN
1206 populate_run_msg(p_assactid,'Payroll ID has invalid characters for the assignment ' || l_assignment_number||'.');
1207 fnd_file.put_line (fnd_file.LOG,'Payroll ID has invalid characters for the assignment ' || l_assignment_number||'.');
1208 edi_validation_fail := 'Y';
1209 END IF;
1210
1211 IF l_eas_start_asg_date IS NOT NULL
1212 and date_validate(p_assactid,'UK_EMPL_DATE',l_eas_start_asg_date) = 0 THEN
1213 populate_run_msg(p_assactid,'The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1214 fnd_file.put_line (fnd_file.LOG,'The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1215 edi_validation_fail := 'Y';
1216 END IF;
1217
1218
1219 IF l_eas_end_asg_date IS NOT NULL
1220 and date_validate(p_assactid,'UK_EMPL_DATE_LEAVING',l_eas_end_asg_date) = 0 THEN
1221 populate_run_msg(p_assactid,' The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1222 fnd_file.put_line (fnd_file.LOG,'The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1223 edi_validation_fail := 'Y';
1224 END IF;
1225
1226 OPEN get_effective_date;
1227 FETCH get_effective_date
1228 INTO l_eff_date;
1229 CLOSE get_effective_date;
1230
1231
1232 l_eff_date := trunc (l_eff_date);
1233 hr_utility.trace('l_eff_date = '|| l_eff_date);
1234
1235 l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code_in_use,l_eff_date); -- Tax code validation
1236
1237 /* EAS 2013 Changes */
1238 /*
1239 IF ((l_expat_indicator <> ' ' and l_expat_indicator IS NOT NULL)
1240 and l_expat_indicator <> 'Y') then
1241 populate_run_msg(p_assactid,'The Expat indicator for the assignment ' || l_assignment_number || ' is invalid. Verify and submit a valid Expat indicator.');
1242 fnd_file.put_line (fnd_file.LOG,'The Expat indicator for the assignment ' || l_assignment_number || ' is invalid. Verify and submit a valid Expat indicator.');
1243 edi_validation_fail := 'Y';
1244 END IF;
1245
1246 IF ((l_occ_pension_indicator <> ' ' and l_occ_pension_indicator IS NOT NULL)
1247 and l_occ_pension_indicator <> 'Y') then
1248 populate_run_msg(p_assactid,'The Occupational Pension indicator for the assignment ' || l_assignment_number || ' is invalid. Verify and submit a valid Occupational Pension indicator.');
1249 fnd_file.put_line (fnd_file.LOG,'The Occupational Pension indicator for the assignment ' || l_assignment_number || ' is invalid. Verify and submit a valid Occupational Pension indicator.');
1250 edi_validation_fail := 'Y';
1251 END IF;
1252
1253 IF ((l_pay_non_indiv_ind <> ' ' and l_pay_non_indiv_ind IS NOT NULL)
1254 and l_pay_non_indiv_ind <> 'Y') then
1255 populate_run_msg(p_assactid,'The Payment to a Non-Individual indicator for the assignment ' || l_assignment_number || ' is invalid. Verify and submit a valid Payment to a Non-Individual indicator.');
1256 fnd_file.put_line (fnd_file.LOG,'The Payment to a Non-Individual indicator for the assignment ' || l_assignment_number || ' is invalid. Verify and submit a valid Payment to a Non-Individual indicator.');
1257 edi_validation_fail := 'Y';
1258 END IF; */
1259
1260 --Tax code
1261
1262 IF (l_tax_code_in_use = ' ' OR l_tax_code_in_use IS NULL) then
1263 populate_run_msg(p_assactid, 'The Tax Code is missing for assignment ' || l_assignment_number||'.');
1264 fnd_file.put_line (fnd_file.LOG,'The Tax Code is missing for assignment ' || l_assignment_number||'.');
1265 edi_validation_fail := 'Y';
1266 END IF;
1267
1268
1269 IF (l_tax_code_in_use <> ' ' and l_tax_code_in_use IS NOT NULL)
1270 AND (l_msg_value <> ' ') THEN
1271 populate_run_msg(p_assactid,'The Tax Code ' || l_tax_code_in_use || ' for assignment ' || l_assignment_number||' is invalid.');
1272 fnd_file.put_line (fnd_file.LOG,'The Tax Code ' || l_tax_code_in_use || ' for assignment ' || l_assignment_number||' is invalid.');
1273 edi_validation_fail := 'Y';
1274 END IF;
1275
1276 -- Tax basis validations
1277
1278 --Tax basis cannot be present if tax code is absent
1279 IF (l_tax_basis_in_use <>' ' and l_tax_basis_in_use IS NOT NULL) AND (l_tax_code_in_use = ' ' OR l_tax_code_in_use IS NULL)THEN
1280 populate_run_msg(p_assactid, 'The Tax Code is not present although, Tax Basis is present for assignment ' || l_assignment_number||'.');
1281 fnd_file.put_line (fnd_file.LOG,'The Tax Code is not present although, Tax Basis is present for assignment ' || l_assignment_number||'.');
1282 edi_validation_fail := 'Y';
1283 END IF;
1284
1285 IF (l_tax_basis_in_use <>' ' AND l_tax_basis_in_use IS NOT NULL) AND (l_tax_basis_in_use <> 'Y' )THEN
1286 populate_run_msg(p_assactid, 'Tax Basis ' ||l_tax_basis_in_use ||' for assignment ' || l_assignment_number||' is invalid.');
1287 fnd_file.put_line (fnd_file.LOG,'Tax Basis ' ||l_tax_basis_in_use ||' for assignment ' || l_assignment_number||' is invalid.');
1288 edi_validation_fail := 'Y';
1289 END IF;
1290
1291 hr_utility.set_location('Leaving: '||l_proc,111);
1292
1293 END eas_asg_etext_validations;
1294 --
1295 -- Person Address validations
1296 PROCEDURE person_addr_validations(p_assactid IN NUMBER,
1297 p_effective_date IN DATE,
1298 p_tab_rec_data IN action_info_table,
1299 p_archive_type IN varchar2 default null,
1300 edi_validation_fail out nocopy varchar2) IS
1301
1302 l_proc CONSTANT VARCHAR2(50):= g_package||'person_addr_validations';
1303 l_ovn number;
1304 l_action_id number;
1305
1306
1307 l_assignment_number per_assignments_f.assignment_number%TYPE;
1308
1309 --address details
1310 l_address_line1 per_addresses.address_line1%TYPE;
1311 l_address_line2 per_addresses.address_line2%TYPE;
1312 l_address_line3 per_addresses.address_line3%TYPE;
1313 l_address_line4 per_addresses.address_line3%TYPE;
1314 l_town_or_city per_addresses.town_or_city%TYPE;
1315 l_postal_code per_addresses.postal_code%TYPE;
1316 l_country per_addresses.country%TYPE; --R
1317
1318
1319 --person details
1320 l_last_name per_people_f.last_name%TYPE;
1321 l_first_name per_people_f.first_name%TYPE;
1322 l_middle_name per_people_f.middle_names%TYPE;
1323 l_national_identifier per_people_f.national_identifier%TYPE;
1324 l_title per_people_f.title%TYPE;
1325 l_date_of_birth per_people_f.date_of_birth%TYPE;
1326 l_sex per_people_f.sex%TYPE;
1327
1328
1329 l_count_char_errors number;
1330 l_count_missing_val number;
1331
1332 l_effective_date date;
1333 l_start_date date;
1334 l_end_date date;
1335
1336 l_year1 number;
1337 l_year2 number;
1338
1339 l_pay_non_indiv_ind varchar2(1);
1340
1341
1342
1343
1344
1345
1346 BEGIN
1347 hr_utility.set_location('Entering '||l_proc,10);
1348
1349 l_count_char_errors := 0;
1350 l_count_missing_val := 0;
1351
1352 l_address_line1 := p_tab_rec_data(1).act_info5;
1353 l_address_line2 := p_tab_rec_data(1).act_info6;
1354 l_address_line3 := p_tab_rec_data(1).act_info7;
1355 l_town_or_city := p_tab_rec_data(1).act_info8;
1356 l_country := p_tab_rec_data(1).act_info13;
1357 l_postal_code := p_tab_rec_data(1).act_info12;
1358
1359 l_last_name := p_tab_rec_data(0).act_info3;
1360 l_first_name := p_tab_rec_data(0).act_info4;
1361 l_middle_name := p_tab_rec_data(0).act_info5;
1362 l_national_identifier := p_tab_rec_data(0).act_info7;
1363 l_title := p_tab_rec_data(0).act_info6;
1364 l_date_of_birth := (to_date(substr(p_tab_rec_data(0).act_info8,1,10),'YYYY-MM-DD'));
1365 l_sex := p_tab_rec_data(0).act_info9;
1366
1367 l_assignment_number := p_tab_rec_data(0).act_info17;
1368
1369 l_pay_non_indiv_ind := p_tab_rec_data(2).act_info12;
1370
1371
1372 l_year1 := to_number(to_char(p_effective_date,'YYYY'));
1373 l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
1374 l_year1 := l_year1- l_year2;
1375
1376 --address validation
1377
1378 --Address line 1 and 2 are mandatory as Start date is not null
1379 /* EAS 2013 changes */
1380 IF ((l_address_line1 = ' ' OR l_address_line1 IS NULL) and (l_national_identifier = ' ' OR l_national_identifier IS NULL)) THEN
1381 populate_run_msg(p_assactid,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
1382 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
1383 edi_validation_fail := 'Y';
1384 l_count_missing_val := l_count_missing_val + 1;
1385 END IF;
1386
1387 IF l_address_line1 <> ' ' and l_address_line1 IS NOT NULL THEN
1388 IF validate_input(l_address_line1,'ADDRESS') > 0 THEN
1389 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1390 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1391 edi_validation_fail := 'Y';
1392 l_count_char_errors := l_count_char_errors + 1;
1393 end if;
1394 END IF;
1395
1396 IF ((l_address_line2 = ' ' OR l_address_line2 IS NULL) and (l_national_identifier = ' ' OR l_national_identifier IS NULL)) THEN
1397 populate_run_msg(p_assactid,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing. Enter either Address Line 2 or Address Line 3 or Town for the assignment.');
1398 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing. Enter either Address Line 2 or Address Line 3 or Town for the assignment.');
1399 edi_validation_fail := 'Y';
1400 l_count_missing_val := l_count_missing_val + 1;
1401 end if;
1402
1403 IF l_address_line2 <> ' ' and l_address_line2 IS NOT NULL THEN
1404 IF validate_input(l_address_line2,'ADDRESS') > 0 THEN
1405 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
1406 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
1407 edi_validation_fail := 'Y';
1408 l_count_char_errors := l_count_char_errors + 1;
1409 end if;
1410 end if;
1411
1412 IF l_address_line3 <> ' ' and l_address_line3 IS NOT NULL THEN
1413 IF validate_input(l_address_line3,'ADDRESS') > 0 THEN
1414 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1415 fnd_file.put_line (fnd_file.LOG,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1416 edi_validation_fail := 'Y';
1417 l_count_char_errors := l_count_char_errors + 1;
1418 END IF;
1419 END IF;
1420
1421 IF l_town_or_city <> ' ' and l_town_or_city IS NOT NULL THEN
1422 IF validate_input(l_town_or_city,'ADDRESS') > 0 THEN
1423 populate_run_msg(p_assactid,'The Town or City ' || l_town_or_city || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
1424 fnd_file.put_line (fnd_file.LOG,'The Town or City ' || l_town_or_city ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
1425 edi_validation_fail := 'Y';
1426 l_count_char_errors := l_count_char_errors + 1;
1427 END IF;
1428 END IF;
1429
1430 IF l_country <> ' ' and l_country IS NOT NULL THEN
1431 IF validate_input(l_country,'COUNTRY') > 0 THEN
1432 populate_run_msg(p_assactid,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1433 fnd_file.put_line (fnd_file.LOG,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1434 edi_validation_fail := 'Y';
1435 l_count_char_errors := l_count_char_errors + 1;
1436 END IF;
1437 END IF;
1438
1439 IF l_postal_code <> ' ' and l_postal_code IS NOT NULL THEN
1440 IF validate_input(l_postal_code,'POSTALCODE') > 0 THEN
1441 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1442 fnd_file.put_line (fnd_file.LOG,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1443 edi_validation_fail := 'Y';
1444 l_count_char_errors := l_count_char_errors + 1;
1445 END IF;
1446 END IF;
1447
1448 --person validation
1449
1450 IF l_last_name = ' ' OR l_last_name is null THEN
1451 populate_run_msg(p_assactid,'The Last Name of the assignment ' || l_assignment_number || ' is missing.');
1452 fnd_file.put_line (fnd_file.LOG,'The Last Name of the assignment '|| l_assignment_number || ' is missing.');
1453 edi_validation_fail := 'Y';
1454 l_count_missing_val := l_count_missing_val + 1;
1455
1456 elsif validate_input(l_last_name,'NAME') > 0 then
1457 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1458 fnd_file.put_line (fnd_file.LOG,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1459 edi_validation_fail := 'Y';
1460 l_count_char_errors := l_count_char_errors + 1;
1461 END IF;
1462
1463 IF l_first_name = ' ' OR l_first_name is null THEN
1464 populate_run_msg(p_assactid,'The First Name of the assignment '|| l_assignment_number || ' is missing.');
1465 fnd_file.put_line (fnd_file.LOG,'The First Name of the assignment ' || l_assignment_number || ' is missing.');
1466 edi_validation_fail := 'Y';
1467 l_count_missing_val := l_count_missing_val + 1;
1468
1469 ELSIF validate_input(l_first_name,'NAME') > 0 THEN
1470 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
1471 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_first_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1472 edi_validation_fail := 'Y';
1473 l_count_char_errors := l_count_char_errors + 1;
1474 END IF;
1475
1476 IF l_middle_name <> ' ' AND l_middle_name is not null THEN
1477 IF validate_input(l_middle_name,'NAME') > 0 THEN
1478 populate_run_msg(p_assactid,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1479 fnd_file.put_line (fnd_file.LOG,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1480 edi_validation_fail := 'Y';
1481 l_count_char_errors := l_count_char_errors + 1;
1482 END IF;
1483 END IF;
1484
1485
1486 IF l_title <> ' ' AND l_title is not null THEN
1487 IF validate_input(l_title,'TITLE') > 0 THEN
1488 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1489 fnd_file.put_line (fnd_file.LOG,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1490 edi_validation_fail := 'Y';
1491 l_count_char_errors := l_count_char_errors + 1;
1492 END IF;
1493 END IF;
1494
1495 IF l_national_identifier is not null AND
1496 hr_gb_utility.ni_validate(l_national_identifier,p_effective_date) <> 0 THEN
1497 populate_run_msg(p_assactid,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1498 fnd_file.put_line (fnd_file.LOG,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1499 edi_validation_fail := 'Y';
1500 END IF;
1501
1502 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
1503 populate_run_msg(p_assactid,'Gender is not specified for the assignment ' || l_assignment_number||'.');
1504 fnd_file.put_line (fnd_file.LOG,'Gender is not specified for the assignment ' || l_assignment_number||'.');
1505 edi_validation_fail := 'Y';
1506 END IF;
1507
1508 /* EAS 2013 changes */
1509 IF ((l_date_of_birth IS NULL) and (l_pay_non_indiv_ind = ' ' or l_pay_non_indiv_ind IS NULL)) THEN
1510 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is missing. Enter a date of birth for the assignment.');
1511 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment ' || l_assignment_number || ' is missing. Enter a date of birth for the assignment.');
1512 edi_validation_fail := 'Y';
1513 END IF;
1514
1515 /*
1516 IF ((l_date_of_birth IS not NULL) and (l_pay_non_indiv_ind <> ' ' or l_pay_non_indiv_ind IS not NULL)) THEN
1517 populate_run_msg(p_assactid,'Date of Birth present for a non_individual. Please remove and resubmit');
1518 fnd_file.put_line (fnd_file.LOG,'Date of Birth present for a non_individual. Please remove and resubmit');
1519 edi_validation_fail := 'Y';
1520 END IF;*/
1521
1522 IF ((l_date_of_birth > p_effective_date) OR (l_year1 > 130)) THEN
1523 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is invalid.');
1524 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is invalid.');
1525 edi_validation_fail := 'Y';
1526 END IF;
1527
1528 hr_utility.set_location('Leaving'||l_proc,10);
1529 END person_addr_validations;
1530 --
1531
1532 -- Person Address validations
1533 PROCEDURE person_addr_validations_nino(p_assactid IN NUMBER,
1534 p_effective_date IN DATE,
1535 p_tab_rec_data IN action_info_table,
1536 p_archive_type IN varchar2 default null,
1537 edi_validation_fail out nocopy varchar2) IS
1538
1539 l_proc CONSTANT VARCHAR2(50):= g_package||'person_addr_validations';
1540 l_ovn number;
1541 l_action_id number;
1542 l_employee_number per_all_people_f.employee_number%TYPE;
1543
1544
1545 l_assignment_number per_assignments_f.assignment_number%TYPE;
1546
1547 --address details
1548 l_address_line1 per_addresses.address_line1%TYPE;
1549 l_address_line2 per_addresses.address_line2%TYPE;
1550 l_address_line3 per_addresses.address_line3%TYPE;
1551 l_address_line4 per_addresses.address_line3%TYPE;
1552 l_town_or_city per_addresses.town_or_city%TYPE;
1553 l_postal_code per_addresses.postal_code%TYPE;
1554 l_country per_addresses.country%TYPE; --R
1555
1556
1557 --person details
1558 l_last_name per_people_f.last_name%TYPE;
1559 l_first_name per_people_f.first_name%TYPE;
1560 l_middle_name per_people_f.middle_names%TYPE;
1561 l_national_identifier per_people_f.national_identifier%TYPE;
1562 l_title per_people_f.title%TYPE;
1563 l_date_of_birth per_people_f.date_of_birth%TYPE;
1564 l_sex per_people_f.sex%TYPE;
1565
1566
1567 l_count_char_errors number;
1568 l_count_missing_val number;
1569
1570 l_effective_date date;
1571 l_start_date date;
1572 l_end_date date;
1573
1574 l_year1 number;
1575 l_year2 number;
1576
1577 BEGIN
1578 hr_utility.set_location('Entering '||l_proc,10);
1579
1580 l_count_char_errors := 0;
1581 l_count_missing_val := 0;
1582
1583 l_address_line1 := p_tab_rec_data(1).act_info5;
1584 l_address_line2 := p_tab_rec_data(1).act_info6;
1585 l_address_line3 := p_tab_rec_data(1).act_info7;
1586 l_town_or_city := p_tab_rec_data(1).act_info8;
1587 l_country := p_tab_rec_data(1).act_info13;
1588 l_postal_code := p_tab_rec_data(1).act_info12;
1589
1590 l_last_name := p_tab_rec_data(0).act_info3;
1591 l_first_name := p_tab_rec_data(0).act_info4;
1592 l_middle_name := p_tab_rec_data(0).act_info5;
1593 l_national_identifier := p_tab_rec_data(0).act_info7;
1594 l_title := p_tab_rec_data(0).act_info6;
1595 l_date_of_birth := (to_date(substr(p_tab_rec_data(0).act_info8,1,10),'YYYY-MM-DD'));
1596 l_sex := p_tab_rec_data(0).act_info9;
1597
1598 l_assignment_number := p_tab_rec_data(0).assignment_id;
1599 l_year1 := to_number(to_char(sysdate,'YYYY'));
1600 l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
1601 l_year1 := l_year1- l_year2;
1602
1603 --address validation
1604 SELECT DISTINCT employee_number into l_employee_number from per_all_people_f where EMPLOYEE_NUMBER IS NOT NULL AND person_id=p_tab_rec_data(0).person_id;
1605
1606
1607 IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
1608 populate_run_msg(p_assactid,'The Address Line 1 of the employee ' || l_employee_number || ' is missing.');
1609 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 of the employee ' || l_employee_number || ' is missing.');
1610 edi_validation_fail := 'Y';
1611 l_count_missing_val := l_count_missing_val + 1;
1612
1613 elsIF validate_input(l_address_line1,'ADDRESS') > 0 THEN
1614 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1615 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 ' || l_address_line1 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1616 edi_validation_fail := 'Y';
1617 l_count_char_errors := l_count_char_errors + 1;
1618 end if;
1619
1620 IF l_address_line2 = ' ' OR l_address_line2 IS NULL THEN
1621 populate_run_msg(p_assactid,'The Address Line 2 of the employee ' || l_employee_number || ' is missing.');
1622 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 of the employee ' || l_employee_number || ' is missing.');
1623 edi_validation_fail := 'Y';
1624 l_count_missing_val := l_count_missing_val + 1;
1625
1626 elsIF validate_input(l_address_line2,'ADDRESS') > 0 THEN
1627 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the employee '|| l_employee_number || ' has invalid characters.');
1628 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 ' || l_address_line2 || ' of the employee '|| l_employee_number || ' has invalid characters.');
1629 edi_validation_fail := 'Y';
1630 l_count_char_errors := l_count_char_errors + 1;
1631 end if;
1632
1633 IF l_address_line3 <> ' ' and l_address_line3 IS NOT NULL THEN
1634 IF validate_input(l_address_line3,'ADDRESS') > 0 THEN
1635 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1636 fnd_file.put_line (fnd_file.LOG,'The Address Line 3 ' || l_address_line3 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1637 edi_validation_fail := 'Y';
1638 l_count_char_errors := l_count_char_errors + 1;
1639 END IF;
1640 END IF;
1641
1642 IF l_town_or_city <> ' ' and l_town_or_city IS NOT NULL THEN
1643 IF validate_input(l_town_or_city,'ADDRESS') > 0 THEN
1644 populate_run_msg(p_assactid,'The Town or City ' || l_town_or_city || ' of the employee '|| l_employee_number || ' has invalid characters.');
1645 fnd_file.put_line (fnd_file.LOG,'The Town or City ' || l_town_or_city ||' of the employee ' || l_employee_number || ' has invalid characters.');
1646 edi_validation_fail := 'Y';
1647 l_count_char_errors := l_count_char_errors + 1;
1648 END IF;
1649 END IF;
1650
1651 IF l_country <> ' ' and l_country IS NOT NULL THEN
1652 IF validate_input(l_country,'COUNTRY') > 0 THEN
1653 populate_run_msg(p_assactid,'The Country ' || l_country || ' of the employee ' || l_employee_number || ' has invalid characters.');
1654 fnd_file.put_line (fnd_file.LOG,'The Country ' || l_country || ' of the employee ' || l_employee_number || ' has invalid characters.');
1655 edi_validation_fail := 'Y';
1656 l_count_char_errors := l_count_char_errors + 1;
1657 END IF;
1658 END IF;
1659
1660 IF l_postal_code <> ' ' and l_postal_code IS NOT NULL THEN
1661 IF validate_input(l_postal_code,'POSTALCODE') > 0 THEN
1662 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the employee ' || l_employee_number || ' has invalid characters.');
1663 fnd_file.put_line (fnd_file.LOG,'The Postal Code ' || l_postal_code || ' of the employee ' || l_employee_number || ' has invalid characters.');
1664 edi_validation_fail := 'Y';
1665 l_count_char_errors := l_count_char_errors + 1;
1666 END IF;
1667 END IF;
1668
1669 --person validation
1670
1671 IF l_last_name = ' ' OR l_last_name is null THEN
1672 populate_run_msg(p_assactid,'The Last Name of the employee ' || l_employee_number || ' is missing.');
1673 fnd_file.put_line (fnd_file.LOG,'The Last Name of the employee '|| l_employee_number || ' is missing.');
1674 edi_validation_fail := 'Y';
1675 l_count_missing_val := l_count_missing_val + 1;
1676
1677 elsif validate_input(l_last_name,'NAME') > 0 then
1678 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1679 fnd_file.put_line (fnd_file.LOG,'The Last Name ' || l_last_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1680 edi_validation_fail := 'Y';
1681 l_count_char_errors := l_count_char_errors + 1;
1682 END IF;
1683
1684 IF l_first_name = ' ' OR l_first_name is null THEN
1685 populate_run_msg(p_assactid,'The First Name of the employee '|| l_employee_number || ' is missing.');
1686 fnd_file.put_line (fnd_file.LOG,'The First Name of the employee ' || l_employee_number || ' is missing.');
1687 edi_validation_fail := 'Y';
1688 l_count_missing_val := l_count_missing_val + 1;
1689
1690 ELSIF validate_input(l_first_name,'NAME') > 0 THEN
1691 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the employee ' || l_employee_number || ' has invalid characters.');
1692 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_first_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1693 edi_validation_fail := 'Y';
1694 l_count_char_errors := l_count_char_errors + 1;
1695 END IF;
1696
1697 IF l_middle_name <> ' ' AND l_middle_name is not null THEN
1698 IF validate_input(l_middle_name,'NAME') > 0 THEN
1699 populate_run_msg(p_assactid,'The First Name ' || l_middle_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1700 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_middle_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1701 edi_validation_fail := 'Y';
1702 l_count_char_errors := l_count_char_errors + 1;
1703 END IF;
1704 END IF;
1705
1706
1707 IF l_title <> ' ' AND l_title is not null THEN
1708 IF validate_input(l_title,'TITLE') > 0 THEN
1709 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the employee ' || l_employee_number || ' has invalid characters.');
1710 fnd_file.put_line (fnd_file.LOG,'The Title ' || l_title || ' of the employee ' || l_employee_number || ' has invalid characters.');
1711 edi_validation_fail := 'Y';
1712 l_count_char_errors := l_count_char_errors + 1;
1713 END IF;
1714 END IF;
1715
1716 IF l_national_identifier is not null AND
1717 hr_gb_utility.ni_validate(l_national_identifier,sysdate) <> 0 THEN
1718 populate_run_msg(p_assactid,'The National Insurance number ' || l_national_identifier || ' of the employee ' || l_employee_number || ' has invalid characters.');
1719 fnd_file.put_line (fnd_file.LOG,'The National Insurance number ' || l_national_identifier || ' of the employee ' || l_employee_number || ' has invalid characters.');
1720 edi_validation_fail := 'Y';
1721 END IF;
1722
1723 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
1724 populate_run_msg(p_assactid,'Gender is not specified for the employee ' || l_employee_number||'.');
1725 fnd_file.put_line (fnd_file.LOG,'Gender is not specified for the employee ' || l_employee_number||'.');
1726 edi_validation_fail := 'Y';
1727 END IF;
1728
1729 IF l_date_of_birth IS NULL THEN
1730 populate_run_msg(p_assactid,'The Date of Birth of the employee ' || l_employee_number || ' is missing.');
1731 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the employee '|| l_employee_number || ' is missing.');
1732 edi_validation_fail := 'Y';
1733 END IF;
1734
1735 IF ((l_date_of_birth > sysdate) OR (l_year1 > 130)) THEN
1736 populate_run_msg(p_assactid,'The Date of Birth of the employee ' || l_employee_number || ' is invalid.');
1737 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the employee '|| l_employee_number || ' is invalid.');
1738 edi_validation_fail := 'Y';
1739 END IF;
1740
1741 hr_utility.set_location('Leaving'||l_proc,10);
1742 END person_addr_validations_nino;
1743 --
1744
1745 --
1746 -- Function to validate the format of the fields
1747 function validate_input(p_input_value varchar2,
1748 p_validate_mode varchar2)
1749 return number is
1750 --
1751 l_valid number := 0;
1752 l_invalid_char constant varchar2(1) := '~'; -- required for translate
1753 l_char_chk constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
1754
1755 --EAS
1756 l_emp_set constant varchar2(36) := '-''.';
1757 l_space constant varchar2(10) := ' ';
1758 l_mix_chars constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
1759 l_number_chk constant varchar2(10) := '0123456789';
1760 l_char_set_A constant varchar2(36) := '.,-()/=!""%&*;<>''+:?\[]@$#^{}_';
1761 l_char_set_B constant varchar2(52) := '.,-()/=!""%&*;<>''+:?';
1762 l_char_set_C constant varchar2(52) := '.-''';
1763 l_char_set_D constant varchar2(52) := '-''';
1764 l_translated_value varchar2(200); -- Required to output failing char.
1765 --
1766 BEGIN
1767 --
1768 hr_utility.trace('Entering validate_input');
1769 hr_utility.trace('p_validate_mode='||p_validate_mode);
1770 hr_utility.trace('p_input_value='||p_input_value);
1771 --
1772
1773 if p_validate_mode = 'EMP_NAME' then
1774 if ( substr(p_input_value,1,1) =' ') then
1775 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1776 l_valid := 2;
1777 else
1778 l_translated_value :=
1779 translate(p_input_value,
1780 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_A||l_space,l_invalid_char);
1781
1782 if l_translated_value is not null then
1783 hr_utility.trace('Invalid chars found: '||l_translated_value);
1784 l_valid := 1; -- Not valid
1785 else
1786 l_valid := 0; -- Valid
1787 end if;
1788 end if ;
1789
1790 elsIF p_validate_mode = 'NUMBER' then
1791 --
1792 -- Check for Valid First Char
1793 --
1794 l_translated_value :=
1795 translate(p_input_value,
1796 l_invalid_char||l_number_chk,l_invalid_char);
1797
1798 if l_translated_value is not null then
1799 hr_utility.trace('Invalid chars found: '||l_translated_value);
1800 l_valid := 1; -- Not valid
1801 else
1802 l_valid := 0; -- Valid
1803 end if;
1804
1805 elsIF p_validate_mode = 'FULL_EDI' then
1806 --
1807 -- Check for Valid First Char
1808 --
1809 if ( substr(p_input_value,1,1) =' ') then
1810 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1811 l_valid := 2;
1812 else
1813 l_translated_value :=
1814 translate(p_input_value,
1815 l_invalid_char||l_char_chk||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1816
1817 if l_translated_value is not null then
1818 hr_utility.trace('Invalid chars found: '||l_translated_value);
1819 l_valid := 1; -- Not valid
1820 else
1821 l_valid := 0; -- Valid
1822 end if;
1823 end if;
1824
1825 elsIF p_validate_mode = 'NAME' then
1826 --
1827 -- Check for Valid First Char
1828 --
1829 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1830 or substr(p_input_value,1,1) between 'a' and 'z') then
1831 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1832 l_valid := 2;
1833 else
1834 l_translated_value :=
1835 translate(p_input_value,
1836 l_invalid_char||l_mix_chars||l_char_set_D||l_space,l_invalid_char);
1837
1838 if l_translated_value is not null then
1839 hr_utility.trace('Invalid chars found: '||l_translated_value);
1840 l_valid := 1; -- Not valid
1841 else
1842 l_valid := 0; -- Valid
1843 end if;
1844 end if;
1845
1846 elsIF p_validate_mode = 'TITLE' then
1847 --
1848 -- Check for Valid First Char
1849 --
1850 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1851 or substr(p_input_value,1,1) between 'a' and 'z') then
1852 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1853 l_valid := 2;
1854 else
1855 l_translated_value :=
1856 translate(p_input_value,
1857 l_invalid_char||l_mix_chars||l_char_set_C||l_space,l_invalid_char);
1858
1859 if l_translated_value is not null then
1860 hr_utility.trace('Invalid chars found: '||l_translated_value);
1861 l_valid := 1; -- Not valid
1862 else
1863 l_valid := 0; -- Valid
1864 end if;
1865 end if;
1866
1867 ELSIF p_validate_mode = 'ADDRESS' THEN
1868 --
1869 -- Check for Valid First Char
1870 --
1871 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1872 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1873 -- First char invalid
1874 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1875 l_valid := 2;
1876 else
1877 l_translated_value :=
1878 translate(p_input_value,
1879 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
1880
1881 if l_translated_value is not null then
1882 hr_utility.trace('Invalid chars found: '||l_translated_value);
1883 l_valid := 1; --Invalid
1884 else
1885 l_valid := 0; -- Valid
1886 end if;
1887 end if;
1888
1889 elsif p_validate_mode = 'COUNTRY' then
1890 if not (substr(p_input_value,1,1) between 'A' and 'Z' or
1891 substr(p_input_value,1,1) between 'a' and 'z' ) then
1892 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1893 l_valid := 2;
1894 else
1895 l_translated_value :=
1896 translate(p_input_value,
1897 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
1898
1899 if l_translated_value is not null then
1900 hr_utility.trace('Invalid chars found: '||l_translated_value);
1901 l_valid := 1; -- Not valid
1902 else
1903 l_valid := 0; -- Valid
1904 end if;
1905 end if ;
1906
1907 -- character set E has alpha,numerals,space
1908 elsif p_validate_mode = 'POSTALCODE' then
1909
1910 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1911 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1912 -- First char invalid
1913 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1914 l_valid := 2;
1915 else
1916 l_translated_value := translate(p_input_value,
1917 l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1918 --
1919 if l_translated_value is not null then
1920 hr_utility.trace('Invalid chars found: '||l_translated_value);
1921 l_valid := 1; -- Not valid
1922 else
1923 l_valid := 0; -- Valid
1924 end if;
1925 end if;
1926
1927 /* EOY 2013 changes */
1928 -- character set E has alpha,numerals,space
1929 elsif p_validate_mode = 'UNIQUE_PART_ID' then
1930
1931 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1932 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1933 -- First char invalid
1934 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1935 l_valid := 2;
1936 else
1937 l_translated_value := translate(p_input_value,
1938 l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1939 --
1940 if l_translated_value is not null then
1941 hr_utility.trace('Invalid chars found: '||l_translated_value);
1942 l_valid := 1; -- Not valid
1943 else
1944 l_valid := 0; -- Valid
1945 end if;
1946 end if;
1947
1948 elsif p_validate_mode = 'PAYROLLID' then
1949 if ( substr(p_input_value,1,1) =' ') then
1950 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1951 l_valid := 2;
1952 else
1953 l_translated_value :=
1954 translate(p_input_value,
1955 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1956
1957 if l_translated_value is not null then
1958 hr_utility.trace('Invalid chars found: '||l_translated_value);
1959 l_valid := 1; -- Not valid
1960 else
1961 l_valid := 0; -- Valid
1962 end if;
1963 end if ;
1964
1965 END IF;
1966 --
1967 hr_utility.trace('Leaving validate_input');
1968 return l_valid;
1969 end validate_input;
1970
1971 -- Function to set New Starter RTI Sent flag
1972 procedure set_new_starter_rti_sent(p_asg_id number)
1973
1974 is
1975
1976
1977
1978 cursor csr_asg_extra_info is
1979 select ASSIGNMENT_EXTRA_INFO_ID, AEI_INFORMATION8,object_version_number
1980 FROM per_assignment_extra_info
1981 WHERE assignment_id = p_asg_id
1982 AND information_type = 'GB_RTI_ASG_DETAILS';
1983
1984 l_asg_extra_info_id number;
1985 l_starter_flag varchar2(1);
1986 l_object_version_number number;
1987
1988 begin
1989 hr_utility.trace('Entering set_new_starter_rti_sent');
1990
1991 open csr_asg_extra_info;
1992 fetch csr_asg_extra_info into l_asg_extra_info_id,l_starter_flag,l_object_version_number;
1993 close csr_asg_extra_info;
1994
1995 if l_asg_extra_info_id is null then
1996 -- Create the extra info and set the RTI Sent flag to Yes
1997 hr_assignment_extra_info_api.create_assignment_extra_info
1998 (p_validate => false,
1999 p_assignment_id => p_asg_id,
2000 p_information_type => 'GB_RTI_ASG_DETAILS',
2001 p_aei_information_category => 'GB_RTI_ASG_DETAILS',
2002 p_aei_information8 => 'Y',
2003 p_aei_information9 => 'N',
2004 p_aei_information19 => 'N',
2005 p_object_version_number => l_object_version_number,
2006 p_assignment_extra_info_id => l_asg_extra_info_id
2007 );
2008
2009 hr_utility.trace('New Assignment extra info id : '||l_asg_extra_info_id);
2010
2011
2012 elsif l_starter_flag <> 'Y' then
2013 -- Update the flag to Yes
2014 hr_assignment_extra_info_api.update_assignment_extra_info
2015 (p_validate => false,
2016 p_object_version_number => l_object_version_number,
2017 p_assignment_extra_info_id => l_asg_extra_info_id,
2018 p_aei_information_category => 'GB_RTI_ASG_DETAILS',
2019 p_aei_information8 => 'Y'
2020 );
2021
2022 hr_utility.trace('Existing Assignment extra info id : '||l_asg_extra_info_id);
2023
2024 end if;
2025
2026 hr_utility.trace('Leaving set_new_starter_rti_sent');
2027
2028 end set_new_starter_rti_sent;
2029
2030 -- Procedure to insert data to the archive table pay_action_information
2031 PROCEDURE insert_archive_row(p_assactid IN NUMBER,
2032 p_effective_date IN DATE,
2033 p_tab_rec_data IN action_info_table) IS
2034 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
2035 l_ovn number;
2036 l_action_id number;
2037 BEGIN
2038 hr_utility.set_location('Entering: '||l_proc,1);
2039 if p_tab_rec_data.count > 0 then
2040 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
2041 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
2042 hr_utility.trace('action_context_id = '|| p_assactid);
2043 if p_tab_rec_data(i).action_info_category is not null then
2044 pay_action_information_api.create_action_information(
2045 p_action_information_id => l_action_id,
2046 p_object_version_number => l_ovn,
2047 p_action_information_category => p_tab_rec_data(i).action_info_category,
2048 p_action_context_id => p_assactid,
2049 p_action_context_type => 'AAP',
2050 p_assignment_id => p_tab_rec_data(i).assignment_id,
2051 p_effective_date => p_effective_date,
2052 p_action_information1 => p_tab_rec_data(i).act_info1,
2053 p_action_information2 => p_tab_rec_data(i).act_info2,
2054 p_action_information3 => p_tab_rec_data(i).act_info3,
2055 p_action_information4 => p_tab_rec_data(i).act_info4,
2056 p_action_information5 => p_tab_rec_data(i).act_info5,
2057 p_action_information6 => p_tab_rec_data(i).act_info6,
2058 p_action_information7 => p_tab_rec_data(i).act_info7,
2059 p_action_information8 => p_tab_rec_data(i).act_info8,
2060 p_action_information9 => p_tab_rec_data(i).act_info9,
2061 p_action_information10 => p_tab_rec_data(i).act_info10,
2062 p_action_information11 => p_tab_rec_data(i).act_info11,
2063 p_action_information12 => p_tab_rec_data(i).act_info12,
2064 p_action_information13 => p_tab_rec_data(i).act_info13,
2065 p_action_information14 => p_tab_rec_data(i).act_info14,
2066 p_action_information15 => p_tab_rec_data(i).act_info15,
2067 p_action_information16 => p_tab_rec_data(i).act_info16,
2068 p_action_information17 => p_tab_rec_data(i).act_info17,
2069 p_action_information18 => p_tab_rec_data(i).act_info18,
2070 p_action_information19 => p_tab_rec_data(i).act_info19,
2071 p_action_information20 => p_tab_rec_data(i).act_info20,
2072 p_action_information21 => p_tab_rec_data(i).act_info21,
2073 p_action_information22 => p_tab_rec_data(i).act_info22,
2074 p_action_information23 => p_tab_rec_data(i).act_info23,
2075 p_action_information24 => p_tab_rec_data(i).act_info24,
2076 p_action_information25 => p_tab_rec_data(i).act_info25,
2077 p_action_information26 => p_tab_rec_data(i).act_info26,
2078 p_action_information27 => p_tab_rec_data(i).act_info27,
2079 p_action_information28 => p_tab_rec_data(i).act_info28,
2080 p_action_information29 => p_tab_rec_data(i).act_info29,
2081 p_action_information30 => p_tab_rec_data(i).act_info30
2082 );
2083 end if;
2084 end loop;
2085 end if;
2086 hr_utility.set_location('Leaving: '||l_proc,999);
2087 END insert_archive_row;
2088
2089 function check_resubmission(pactid number,tax_ref varchar2,permit varchar2,bg_id number)
2090 return varchar2 is
2091 l_exists varchar2(1):='N';
2092 cursor get_prev_flag is
2093 select 'Y' from dual where exists(select *
2094 from pay_payroll_actions prev
2095 where prev.payroll_action_id <> pactid
2096 and prev.business_group_id =bg_id
2097 and prev.ACTION_STATUS='C'
2098 and prev.REPORT_TYPE='RTI_EAS_REP_13'
2099 and substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'TAX_REF'),1,20) = substr(tax_ref,1,20)
2100 and substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'PERMIT'),1,12)=substr(permit,1,12)
2101 and substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'NO_OF_PARTS'),1,2)=
2102 nvl('',substr(pay_gb_eoy_archive.get_parameter(prev.legislative_parameters,'NO_OF_PARTS'),1,2))
2103 );
2104 begin
2105 open get_prev_flag;
2106 fetch get_prev_flag into l_exists;
2107 close get_prev_flag;
2108
2109 return l_exists;
2110 end;
2111 --
2112 --
2113 /*------------ PUBLIC PROCEDURES --------------*/
2114
2115 /*--------------------------------------------------------------------------
2116 Name : range_cursor
2117 Purpose : This returns the select statement that is used to create the
2118 range rows.
2119 Arguments :
2120 Notes : The range cursor determines which people should be processed.
2121 The normal practice is to include everyone, and then limit
2122 the list during the assignment action creation.
2123 --------------------------------------------------------------------------*/
2124 PROCEDURE range_cursor (pactid IN NUMBER,
2125 sqlstr OUT NOCOPY VARCHAR2)
2126 IS
2127
2128
2129
2130 l_report_type varchar2(15);
2131 --NINO New CP addition
2132 l_payroll_id varchar2(25);
2133
2134 -- l_employee_number will have the Employee ID parameter from the NINO Program
2135 l_employee_number varchar2(25);
2136 -- l_assignment_set will have the Assignment Set parameter from the NINO Program
2137 l_assignment_set varchar2(25);
2138
2139 l_paye_ref hr_organization_information.org_information1%TYPE;
2140 l_err BOOLEAN;
2141 l_exp EXCEPTION;
2142
2143 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
2144 /* EAS 2013 changes */
2145 cursor csr_payroll_details(pactid NUMBER) is
2146 select
2147 fnd_number.number_to_canonical(pact.request_id) request_id,
2148 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
2149 substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
2150 substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'PERMIT'),1,12) unique_part_id,
2151 report_type,
2152 business_group_id
2153 from pay_payroll_actions pact,
2154 hr_organization_information hoi
2155 where pact.payroll_action_id=pactid
2156 and pact.business_group_id = hoi.organization_id
2157 and hoi.org_information_context = 'Tax Details References'
2158 and nvl(hoi.org_information10,'UK') = 'UK'
2159 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
2160 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
2161 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
2162
2163 cursor csr_get_paye_ref(pactid number) is
2164 select pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF')
2165 from pay_payroll_actions pact
2166 where payroll_action_id = pactid;
2167
2168 l_payroll_rec csr_payroll_details%rowtype;
2169 BEGIN
2170 hr_utility.set_location('Entering: '||l_proc,1);
2171
2172 l_err := FALSE;
2173
2174 open csr_payroll_details(pactid);
2175 fetch csr_payroll_details into l_payroll_rec;
2176 close csr_payroll_details;
2177
2178 open csr_get_paye_ref(pactid);
2179 fetch csr_get_paye_ref into l_paye_ref;
2180 close csr_get_paye_ref;
2181
2182 l_report_type := l_payroll_rec.report_type;
2183
2184
2185 IF l_report_type = 'RTI_EAS_REP_13' THEN
2186 hr_utility.set_location('Calling header validations', 10);
2187
2188 /* EAS 2013 Changes*/
2189
2190 if l_payroll_rec.effective_date < fnd_date.canonical_to_date('2013/04/06 00:00:00') then
2191 hr_utility.set_location('Effective Date must be later than 06-April-2013',10);
2192 fnd_file.put_line(fnd_file.output,'Effective Date must be later than 06-April-2013.');
2193 l_err := true;
2194 end if;
2195 if l_payroll_rec.no_of_parts = ' ' or l_payroll_rec.no_of_parts is null then
2196 hr_utility.set_location('Enter the Number of Parts for Submission. This is a mandatory field.',10);
2197 fnd_file.put_line(fnd_file.output,'Enter the Number of Parts for Submission. This is a mandatory field.');
2198 l_err := true;
2199 elsif substr(l_payroll_rec.no_of_parts,1,2) = '00' or substr(l_payroll_rec.no_of_parts,1,2) ='0'
2200 then
2201 hr_utility.set_location('Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.',10);
2202 fnd_file.put_line(fnd_file.output,'Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.');
2203 l_err := true;
2204 elsif validate_input(substr(l_payroll_rec.no_of_parts,1,2),'NUMBER') > 0
2205 then
2206 hr_utility.set_location('Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.',10);
2207 fnd_file.put_line(fnd_file.output,'Number of Parts '||l_payroll_rec.no_of_parts||' must be numeric and in the range of 01 to 99 only.');
2208 l_err := true;
2209 end if;
2210
2211 if ((l_payroll_rec.unique_part_id = ' ' or l_payroll_rec.unique_part_id is null) and (l_payroll_rec.no_of_parts > 1)) then
2212 hr_utility.set_location('The value in the Number of Parts field is greater than 01. The Unique Part Identifier field is now mandatory. Enter a value for the Unique Part Identifier.',10);
2213 fnd_file.put_line(fnd_file.output,'The value in the Number of Parts field is greater than 01. The Unique Part Identifier field is now mandatory. Enter a value for the Unique Part Identifier.');
2214 l_err := true;
2215 elsif (check_resubmission(pactid,l_paye_ref,l_payroll_rec.unique_part_id,l_payroll_rec.business_group_id) = 'Y')
2216 then
2217 hr_utility.set_location('The Unique Part Identifier '||l_payroll_rec.unique_part_id|| ' has been previously used. Change the Unique Part Identifier and resubmit.',10);
2218 fnd_file.put_line(fnd_file.output,'The Unique Part Identifier '||l_payroll_rec.unique_part_id|| ' has been previously used. Change the Unique Part Identifier and resubmit.');
2219 l_err := true;
2220
2221
2222 elsif validate_input(substr(l_payroll_rec.unique_part_id,1,12),'UNIQUE_PART_ID') > 0
2223 then
2224 hr_utility.set_location('The Unique Part Identifier '||l_payroll_rec.unique_part_id||' has invalid characters. Verify and submit a valid Unique Part Identifier.',10);
2225 fnd_file.put_line(fnd_file.output,'The Unique Part Identifier '||l_payroll_rec.unique_part_id||' has invalid characters. Verify and submit a valid Unique Part Identifier.');
2226 l_err := true;
2227 end if;
2228 END IF;
2229
2230 if (l_err) then
2231 raise l_exp;
2232 end if;
2233
2234 IF l_report_type = 'RTI_EAS_REP_13' THEN
2235
2236 sqlstr := 'select distinct person_id '||
2237 'from per_all_people_f ppf, '||
2238 'pay_payroll_actions ppa '||
2239 'where ppa.payroll_action_id = :payroll_action_id '||
2240 'and ppa.business_group_id = ppf.business_group_id '||
2241 'order by ppf.person_id';
2242 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
2243 hr_utility.set_location(' Leaving: '||l_proc,100);
2244 END IF;
2245
2246
2247
2248 IF l_report_type = 'RTI_NINO_REP' THEN
2249 -- Getting the Payroll ID
2250 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2251 p_payroll_action_id => pactid,
2252 p_token_name => 'PAYROLL',
2253 p_token_value => l_payroll_id);
2254
2255 -- Getting the Assignment Set
2256 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2257 p_payroll_action_id => pactid,
2258 p_token_name => 'ASSET',
2259 p_token_value => l_assignment_set);
2260
2261 -- Getting the Employee Numnber (Emp ID)
2262 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2263 p_payroll_action_id => pactid,
2264 p_token_name => 'EMPID',
2265 p_token_value => l_employee_number);
2266
2267 hr_utility.trace(' Payroll : '||l_payroll_id);
2268 hr_utility.trace(' Assignment Set : '||l_assignment_set);
2269 hr_utility.trace(' Employee ID : '||l_employee_number);
2270
2271 IF (l_payroll_id is null) THEN
2272 sqlstr := 'select distinct person_id '||
2273 'from per_all_people_f ppf, '||
2274 'pay_payroll_actions ppa '||
2275 'where ppa.payroll_action_id = :payroll_action_id '||
2276 'and ppa.business_group_id = ppf.business_group_id '||
2277 'order by ppf.person_id';
2278
2279 END IF;
2280
2281 IF (l_payroll_id is not null) THEN
2282
2283 IF ((l_employee_number is null) and (l_assignment_set is null)) THEN
2284 sqlstr := 'select distinct PERSON_ID '||
2285 'from per_all_assignments_f paaf, '||
2286 'pay_payroll_actions ppa '||
2287 'where ppa.payroll_action_id = :payroll_action_id '||
2288 'and ppa.business_group_id = paaf.business_group_id '||
2289 ' and paaf.PAYROLL_ID = ' ||''''||l_payroll_id||''''||
2290 ' order by person_id';
2291 END IF;
2292
2293 IF (l_employee_number is not null ) THEN
2294 sqlstr := 'select distinct ppf.person_id '||
2295 'from per_all_people_f ppf, '||
2296 'per_all_assignments_f paaf, '||
2297 'pay_payroll_actions ppa '||
2298 'where ppa.payroll_action_id = :payroll_action_id '||
2299 ' and paaf.person_id=ppf.person_id '||
2300 'and ppa.business_group_id = ppf.business_group_id '||
2301 ' and ppf.EMPLOYEE_NUMBER = ' ||''''||l_employee_number||''''||
2302 ' and paaf.PAYROLL_ID = ' ||''''||l_payroll_id||''''||
2303 'order by ppf.person_id';
2304 END IF;
2305
2306 IF (l_assignment_set is not null)THEN
2307
2308 sqlstr := 'select distinct person_id '||
2309 'from per_all_people_f ppf, '||
2310 'pay_payroll_actions ppa '||
2311 'where ppa.payroll_action_id = :payroll_action_id '||
2312 'and ppa.business_group_id = ppf.business_group_id '||
2313 'order by ppf.person_id';
2314 END IF;
2315
2316
2317
2318 END IF;
2319 END IF;
2320 hr_utility.set_location('Leaving '|| l_proc, 10);
2321 EXCEPTION
2322 when l_exp then
2323 g_validation_check := 'N';
2324 raise_application_error(-20001,'Errors found while archiving data.');
2325 END range_cursor;
2326 --
2327 --
2328 /*--------------------------------------------------------------------------
2329 Name : action_creation
2330 Purpose : This creates the assignment actions for a specific chunk.
2331 Arguments :
2332 Notes :
2333 --------------------------------------------------------------------------*/
2334
2335 PROCEDURE rti_eas_action_creation (pactid in number,
2336 stperson in number,
2337 endperson in number,
2338 chunk in number) IS
2339 BEGIN
2340 internal_action_creation_eas(pactid, stperson, endperson, chunk,'GB_RTI_EAS', 'RTI_EAS_REP_13');
2341 END rti_eas_action_creation;
2342
2343 --
2344 PROCEDURE rti_nino_action_creation (pactid in number,
2345 stperson in number,
2346 endperson in number,
2347 chunk in number) IS
2348 BEGIN
2349 internal_action_creation_nino(pactid, stperson, endperson, chunk,'GB_RTI_NINO', 'RTI_NINO_REP');
2350 END rti_nino_action_creation;
2351
2352
2353 PROCEDURE internal_action_creation_eas(pactid in number,
2354 stperson in number,
2355 endperson in number,
2356 chunk in number,
2357 p_info_type in varchar2,
2358 p_rep_type in varchar2)
2359 IS
2360 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
2361 l_payroll_id number;
2362 l_tax_ref varchar2(20);
2363 l_business_group_id number;
2364 l_effective_date date;
2365 l_tax_year_start_date DATE;
2366 l_no_of_parts number;
2367 l_unique_part_id VARCHAR2(12);
2368 l_ass_act_id number;
2369 l_assignment_id number;
2370
2371
2372 lockingactid number;
2373 l_locked_action_id number;
2374 l_exist number;
2375 /* EAS 2013 changes */
2376 cursor csr_parameter_info is
2377 select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
2378 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
2379 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
2380 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PERMIT'),1,12) unique_part_id,
2381 business_group_id
2382 from pay_payroll_actions pact
2383 where payroll_action_id = pactid;
2384
2385 -- Cursor to fetch Active Assignments
2386 cursor csr_asg_active is
2387 select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
2388 pap.person_id person_id,past.per_system_status status
2389 from per_all_people_f pap,
2390 per_all_assignments_f asg,
2391 per_assignment_status_types past,
2392 per_periods_of_service serv,
2393 pay_all_payrolls_f pay,
2394 hr_soft_coding_keyflex sck,
2395 hr_organization_information org
2396 where pap.person_id between stperson and endperson
2397 and pap.current_employee_flag = 'Y'
2398 and pap.person_id = asg.person_id
2399 and asg.business_group_id = l_business_group_id
2400 and asg.assignment_status_type_id = past.assignment_status_type_id
2401 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
2402 and asg.payroll_id = pay.payroll_id
2403 and asg.period_of_service_id = serv.period_of_service_id
2404 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2405 and upper(nvl(l_tax_ref, sck.segment1)) =
2406 upper(sck.segment1)
2407 and org.organization_id = l_business_group_id
2408 and org.org_information_context =
2409 'Tax Details References'||decode(sck.segment1,'','','')
2410 and org.org_information1 = sck.segment1
2411 and nvl(org.org_information10,'UK') = 'UK'
2412 and nvl(l_unique_part_id,substr(sck.segment10,1,12)) = substr(sck.segment10,1,12)
2413 --and l_effective_date between asg.effective_start_date and asg.effective_end_date
2414 and asg.effective_start_date =
2415 ( select max(asg2.effective_start_date)
2416 from per_all_assignments_f asg2
2417 where asg2.assignment_id = asg.assignment_id
2418 and asg2.assignment_type = 'E'
2419 and asg2.effective_start_date <= l_effective_date
2420 )
2421 and l_effective_date between pap.effective_start_date and pap.effective_end_date
2422 and l_effective_date between pay.effective_start_date and pay.effective_end_date
2423 order by person_id,assignment_id;
2424
2425 --Cursor to fetch Terminated Employees
2426 cursor csr_asg_terminated is
2427 select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,
2428 pap.person_id person_id, serv.actual_termination_date actual_termination_date
2429 from per_all_people_f pap,
2430 per_all_assignments_f asg,
2431 per_periods_of_service serv,
2432 pay_all_payrolls_f pay,
2433 hr_soft_coding_keyflex sck,
2434 hr_organization_information org
2435 where pap.person_id between stperson and endperson
2436 and serv.actual_termination_date is not null
2437 and pap.person_id = asg.person_id
2438 and asg.business_group_id = l_business_group_id
2439 and asg.payroll_id = pay.payroll_id
2440 and asg.period_of_service_id = serv.period_of_service_id
2441 and asg.assignment_type = 'E'
2442 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2443 and upper(nvl(l_tax_ref, sck.segment1)) =
2444 upper(sck.segment1)
2445 and org.organization_id = l_business_group_id
2446 and org.org_information_context =
2447 'Tax Details References'||decode(sck.segment1,'','','')
2448 and org.org_information1 = sck.segment1
2449 and nvl(org.org_information10,'UK') = 'UK'
2450 and nvl(l_unique_part_id,substr(sck.segment10,1,12)) = substr(sck.segment10,1,12)
2451 and pap.effective_start_date =
2452 ( select max(pap2.effective_start_date) from
2453 per_all_people_f pap2
2454 where pap2.person_id = pap.person_id
2455 and pap2.effective_start_date <= l_effective_date
2456 )
2457 and asg.effective_start_date =
2458 ( select max(asg2.effective_start_date)
2459 from per_all_assignments_f asg2
2460 where asg2.assignment_id = asg.assignment_id
2461 and asg2.assignment_type = 'E'
2462 and asg2.effective_start_date <= l_effective_date
2463 )
2464 and asg.effective_end_date >= l_tax_year_start_date
2465 and l_effective_date between pay.effective_start_date and pay.effective_end_date
2466 and serv.actual_termination_date >= l_tax_year_start_date
2467 and serv.actual_termination_date < l_effective_date
2468 order by person_id,assignment_id;
2469
2470 -- Cursor to fetch Agg Flag for a Person
2471 cursor csr_get_agg_flag (p_person_id number) is
2472 select per_information10 per_agg_flag from per_all_people_f
2473 where PERSON_ID = p_person_id
2474 and l_effective_date between effective_start_date and effective_end_date;
2475
2476 cursor csr_find_next_asg(p_person_id number,p_assignment_id number) is
2477 select paaf.assignment_id
2478 from per_all_people_f peo,
2479 per_all_assignments_f paaf,
2480 per_all_assignments_f paaf1,
2481 per_assignment_status_types past,
2482 per_assignment_status_types past1,
2483 pay_all_payrolls_f papf ,
2484 hr_soft_coding_keyflex flex
2485 where
2486 peo.person_id = p_person_id
2487 and paaf.person_id = peo.person_id
2488 and paaf.assignment_id <> p_assignment_id
2489 and paaf1.assignment_id = p_assignment_id
2490 and paaf.assignment_status_type_id = past.assignment_status_type_id
2491 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
2492 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
2493 and past1.per_system_status in ('TERM_ASSIGN')
2494 and papf.payroll_id = paaf.payroll_id
2495 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
2496 and upper(l_tax_ref) = upper(flex.segment1)
2497 and paaf.effective_start_date = paaf1.effective_start_date;
2498
2499 cursor csr_get_terminated_date(p_asg_id number) is
2500 select max(effective_end_date)
2501 from per_all_assignments_f paaf ,
2502 per_assignment_status_types past
2503 where assignment_id = p_asg_id
2504 --and paaf.assignment_status_type_id in ( 1,2)
2505 and paaf.assignment_status_type_id = past.assignment_status_type_id
2506 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
2507 and paaf.effective_start_date <= l_effective_date;
2508
2509
2510 l_next_asg number;
2511 l_flag varchar2(1) :='Y';
2512 l_prev_asg number:=0;
2513 l_status number;
2514 l_per_agg_flag varchar2(1);
2515 l_asg_end_date date;
2516 --l_person_id number :=0;
2517 --l_first_asg varchar2(1) :='Y';
2518
2519 BEGIN
2520
2521 hr_utility.set_location('Entering: '||l_proc,1);
2522
2523 open csr_parameter_info;
2524 fetch csr_parameter_info into l_tax_ref,
2525 l_effective_date,
2526 l_no_of_parts,
2527 l_unique_part_id,
2528 l_business_group_id;
2529 close csr_parameter_info;
2530 hr_utility.set_location('Process effective date'||l_effective_date,20);
2531 l_tax_year_start_date := fetch_soy(l_effective_date);
2532
2533 hr_utility.set_location('Active Assignments',15);
2534 -- To fetch Active Assignments
2535 for asg_rec in csr_asg_active loop
2536
2537 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2538 l_flag:= 'Y';
2539 -- l_first_asg := 'Y';
2540 /* EAS 2013 changes */
2541 -- If Aggregate flag is set then ignore consecutive assignments
2542 --if asg_rec.per_agg_flag = 'Y' then
2543 --hr_utility.trace('g_person_id : '||g_person_id);
2544 --hr_utility.trace('asg_rec.person_id : '||asg_rec.person_id);
2545 --if (g_person_id <> asg_rec.person_id) then
2546 -- l_first_asg := 'Y';
2547
2548 --else
2549 --l_first_asg :='N';
2550 --end if;
2551 -- end if;
2552 --hr_utility.set_location('l_first_asg :' || l_first_asg,15);
2553
2554 /*
2555 if asg_rec.asg_primary_flag <> 'Y' then
2556 hr_utility.trace(' Aggregation Found. This is not primary assignment.');
2557 l_flag:= 'N';
2558 end if;
2559 */
2560 -- P45 check.If issued do not pick the assignment
2561 /*
2562 if asg_rec.status = 'TERM_ASSIGN' and pay_p45_pkg.return_p45_issued_flag(asg_rec.assignment_id) = 'Y' then
2563 fnd_file.put_line (fnd_file.LOG,'Terminated Asg '||asg_rec.assignment_id||' found.P45 issued.');
2564 l_flag:= 'N';
2565 end if;*/
2566
2567 -- If Terminated before start of the Tax Year. Ignore the assignment
2568 open csr_get_terminated_date(asg_rec.assignment_id);
2569 fetch csr_get_terminated_date into l_asg_end_date;
2570 close csr_get_terminated_date;
2571
2572 hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
2573
2574 if l_asg_end_date < l_tax_year_start_date then
2575 hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
2576 l_flag:= 'N';
2577 end if;
2578
2579 -- To check whether continous employment exist for this assignment
2580 if asg_rec.status = 'TERM_ASSIGN' and l_flag = 'Y' then
2581 open csr_find_next_asg(asg_rec.person_id,asg_rec.assignment_id);
2582 fetch csr_find_next_asg into l_next_asg;
2583 if csr_find_next_asg%found then
2584 hr_utility.trace('Continous Employment.Ignore this assignment.');
2585 l_flag:= 'N';
2586 end if;
2587 close csr_find_next_asg;
2588 end if;
2589
2590 if (l_flag = 'Y') then
2591
2592 -- Create one assignment action for every assignment
2593 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2594 select pay_assignment_actions_s.nextval
2595 into lockingactid
2596 from dual;
2597
2598 -- Insert assignment into pay_assignment_actions
2599 hr_nonrun_asact.insact
2600 (
2601 lockingactid,
2602 asg_rec.assignment_id,
2603 pactid,
2604 chunk,
2605 null
2606 );
2607 end if;
2608 --g_person_id := asg_rec.person_id;
2609 --hr_utility.trace('g_person_id : '||g_person_id);
2610 end loop;
2611
2612 -- To fetch Terminated assignments
2613 hr_utility.set_location('Terminated Assignments',15);
2614 for asg_rec in csr_asg_terminated loop
2615 l_flag := 'Y';
2616 /* Check the cached value of person id .
2617 If the assignment has been archived already, skip the assignment again
2618 when appers in date tracked records.
2619 */
2620 if l_prev_asg <> asg_rec.assignment_id then
2621 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2622
2623 -- If Aggregate flag is set then ignore non primary assignments
2624
2625 open csr_get_agg_flag(asg_rec.person_id);
2626 fetch csr_get_agg_flag into l_per_agg_flag;
2627 close csr_get_agg_flag;
2628 hr_utility.set_location('Aggregation Flag:'||l_per_agg_flag,1515);
2629 /* EAS 2013 changes */
2630 /*if l_per_agg_flag = 'Y' then
2631 if asg_rec.asg_primary_flag <> 'Y' then
2632 hr_utility.trace(' Aggregation Found. This is not primary assignment.');
2633 l_flag:= 'N';
2634 end if;
2635 end if;*/
2636
2637 -- If Terminated before start of the Tax Year. Ignore the assignment
2638 open csr_get_terminated_date(asg_rec.assignment_id);
2639 fetch csr_get_terminated_date into l_asg_end_date;
2640 close csr_get_terminated_date;
2641
2642 hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
2643
2644 if l_asg_end_date < l_tax_year_start_date then
2645 hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
2646 l_flag:= 'N';
2647 end if;
2648
2649 if l_flag = 'Y' then
2650
2651 -- Create one assignment action for every assignment
2652 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2653 select pay_assignment_actions_s.nextval
2654 into lockingactid
2655 from dual;
2656
2657 -- Insert assignment into pay_assignment_actions
2658 hr_nonrun_asact.insact
2659 (
2660 lockingactid,
2661 asg_rec.assignment_id,
2662 pactid,
2663 chunk,
2664 null
2665 );
2666 -- Cache the archived person id
2667 l_prev_asg := asg_rec.assignment_id;
2668 end if;
2669 end if; -- cache check end
2670 end loop;
2671
2672 hr_utility.set_location('Leaving: '||l_proc,999);
2673 END internal_action_creation_eas;
2674
2675 --
2676 --
2677
2678 PROCEDURE internal_action_creation_nino (pactid in number,
2679 stperson in number,
2680 endperson in number,
2681 chunk in number,
2682 p_info_type in varchar2,
2683 p_rep_type in varchar2)
2684 IS
2685 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
2686 l_payroll_id number;
2687 l_tax_ref varchar2(20);
2688 l_business_group_id number;
2689 l_effective_date date;
2690 l_emp_num varchar2(20);
2691 l_ass_act_id number;
2692 l_assignment_id number;
2693 l_arch boolean;
2694 l_assignment_set number;
2695 l_formula_id number;
2696 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
2697 lockingactid number;
2698 l_locked_action_id number;
2699 l_exist number;
2700 l_exclude_flag char := 'I';
2701 cursor csr_parameter_info is
2702 select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL')) payroll_id,
2703 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
2704 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EMPID'),1,20) emp_num,
2705 effective_date,
2706 business_group_id
2707 from pay_payroll_actions
2708 where payroll_action_id = pactid;
2709
2710 cursor csr_asg is
2711 select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
2712 pap.person_id person_id,asg.effective_start_date effective_start_date
2713 from per_all_people_f pap,
2714 per_all_assignments_f asg,
2715 per_periods_of_service serv,
2716 pay_all_payrolls_f pay,
2717 hr_soft_coding_keyflex sck,
2718 per_people_extra_info ppei
2719 where pap.person_id between stperson and endperson
2720 and pap.person_id =ppei.person_id (+)
2721 and nvl(ppei.pei_information_category,'RTI_NINO') = 'RTI_NINO'
2722 and nvl(ppei.pei_information1,'No') = 'No'
2723 and ppei.pei_information5 is null
2724 --and pap.current_employee_flag = 'Y'
2725 --and nvl(pap.current_employee_flag,'N') = decode(l_emp_num,null,'Y', nvl(pap.current_employee_flag,'N'))
2726 and asg.primary_flag = 'Y'
2727 and nvl(pap.current_employee_flag,'N') = decode(l_emp_num, null,decode(l_assignment_set,null,'Y',nvl(pap.current_employee_flag,'N')),nvl(pap.current_employee_flag,'N'))
2728 and pap.person_id = asg.person_id
2729 and asg.business_group_id = l_business_group_id
2730 and asg.payroll_id = pay.payroll_id
2731 and asg.period_of_service_id = serv.period_of_service_id
2732 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2733 and upper(l_tax_ref) = upper(sck.segment1)
2734 and (l_payroll_id IS NULL
2735 or
2736 l_payroll_id = pay.payroll_id)
2737 and not exists (select 1
2738 from hr_assignment_set_amendments hasa
2739 where hasa.assignment_set_id = l_assignment_set
2740 and hasa.assignment_id = asg.assignment_id
2741 and hasa.include_or_exclude = 'E')
2742 and serv.date_start <= l_effective_date
2743 and l_effective_date between asg.effective_start_date and asg.effective_end_date
2744 and l_effective_date between pap.effective_start_date and pap.effective_end_date
2745 and l_effective_date between pay.effective_start_date and pay.effective_end_date
2746 order by person_id,effective_start_date;
2747
2748 cursor csr_exc_inc is
2749 -- Assuming Only one type of include_or_exclude will be there for an assignment set
2750 select nvl(include_or_exclude,'I')
2751 from hr_assignment_set_amendments
2752 where assignment_set_id = l_assignment_set;
2753
2754 l_prev_person number:=0;
2755 l_flag varchar2(1) :='Y';
2756 BEGIN
2757 hr_utility.set_location('Entering: '||l_proc,1);
2758 open csr_parameter_info;
2759 fetch csr_parameter_info into l_payroll_id,
2760 l_tax_ref,
2761 l_emp_num,
2762 l_effective_date,
2763 l_business_group_id;
2764 close csr_parameter_info;
2765
2766 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2767 p_payroll_action_id => pactid,
2768 p_token_name => 'ASSET',
2769 p_token_value => l_assignment_set);
2770 hr_utility.set_location('Assignment Set Id '||l_assignment_set,11);
2771
2772 hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
2773 for asg_rec in csr_asg loop
2774 l_flag := 'Y';
2775 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2776
2777
2778
2779 open csr_exc_inc ;
2780 fetch csr_exc_inc into l_exclude_flag;
2781 IF csr_exc_inc%notfound then
2782 l_exclude_flag :='I';
2783 end if;
2784 close csr_exc_inc;
2785 --Check for Assignment set
2786 If l_assignment_set is not null and l_exclude_flag ='I' then
2787 pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => l_assignment_set
2788 ,p_formula_id => l_formula_id
2789 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2790 );
2791 hr_utility.set_location('Assignment Set -FormulaID :' || l_formula_id,11);
2792 hr_utility.set_location('Assignment Set -Amendments Count :' || l_tab_asg_set_amnds.count,11);
2793
2794 If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
2795 hr_utility.set_location('Assignment Set -FormulaID :' || asg_rec.assignment_id,15);
2796 l_flag := 'N'; -- Assignment Action not to be created
2797 end if;
2798
2799
2800
2801 l_flag := pqp_budget_maintenance.chk_is_asg_in_asg_set(p_assignment_id => asg_rec.assignment_id
2802 ,p_formula_id => l_formula_id
2803 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2804 ,p_effective_date => l_effective_date
2805 );
2806 hr_utility.set_location('Assignment Id - l_flag :' || l_flag,11);
2807 End if;
2808
2809
2810 if l_flag = 'Y' then
2811
2812 -- Create one assignment action for every assignment
2813 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2814 select pay_assignment_actions_s.nextval
2815 into lockingactid
2816 from dual;
2817
2818 -- Insert assignment into pay_assignment_actions
2819 hr_nonrun_asact.insact
2820 (
2821 lockingactid,
2822 asg_rec.assignment_id,
2823 pactid,
2824 chunk,
2825 null
2826 );
2827
2828 end if;
2829 --l_prev_person := asg_rec.person_id;
2830 end loop;
2831
2832 hr_utility.set_location('Leaving: '||l_proc,999);
2833 END internal_action_creation_nino;
2834 --
2835
2836 --
2837
2838 /*--------------------------------------------------------------------------
2839 Name : archinit
2840 Purpose : This procedure can be used to perform an initialisation
2841 section and validation of Employer details
2842 Arguments :
2843 Notes :
2844 --------------------------------------------------------------------------*/
2845 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
2846 IS
2847 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
2848
2849 l_sender_id hr_organization_information.org_information11%TYPE;
2850 l_tax_ref hr_organization_information.org_information1%TYPE;
2851 l_tax_dist hr_organization_information.org_information2%TYPE;
2852 l_paye_ref hr_organization_information.org_information1%TYPE;
2853 l_employer_addr VARCHAR2(255);
2854 l_employer_name VARCHAR2(150);
2855 l_err BOOLEAN;
2856 l_exp EXCEPTION;
2857 l_rep_typ varchar2(100);
2858 l_exists varchar2(1):='N';
2859
2860 /* EAS 2013 changes */
2861 cursor csr_payroll_details(pactid NUMBER) is
2862 select
2863 nvl(UPPER(hoi.org_information11),' ') sender_id,
2864 decode(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
2865 fnd_number.number_to_canonical(pact.request_id) request_id,
2866 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
2867 nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
2868 nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
2869 lpad(nvl(substr(hoi.org_information1,1,3),' '),3,0) tax_office_no,
2870 decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
2871 nvl(upper(substr(hoi.org_information6,1,13)),' ') acc_ref_no,
2872 substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
2873 substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'PERMIT'),1,12) unique_part_id,
2874 report_type,
2875 business_group_id
2876 from pay_payroll_actions pact,
2877 hr_organization_information hoi
2878 where pact.payroll_action_id=pactid
2879 and pact.business_group_id = hoi.organization_id
2880 and hoi.org_information_context = 'Tax Details References'
2881 and nvl(hoi.org_information10,'UK') = 'UK'
2882 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
2883 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
2884 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
2885
2886 cursor csr_get_paye_ref(pactid number) is
2887 select pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF')
2888 from pay_payroll_actions pact
2889 where payroll_action_id = pactid;
2890
2891
2892 l_payroll_rec csr_payroll_details%rowtype;
2893
2894 BEGIN
2895 hr_utility.set_location('Entering '|| l_proc, 10);
2896 l_err := FALSE;
2897
2898 open csr_payroll_details(p_payroll_action_id);
2899 fetch csr_payroll_details into l_payroll_rec;
2900 close csr_payroll_details;
2901
2902 open csr_get_paye_ref(p_payroll_action_id);
2903 fetch csr_get_paye_ref into l_paye_ref;
2904 close csr_get_paye_ref;
2905
2906 l_rep_typ := l_payroll_rec.report_type;
2907
2908 hr_utility.set_location('l_sender_id '|| l_sender_id, 10);
2909
2910 IF l_rep_typ in ('RTI_EAS_REP_13','RTI_NINO_REP')
2911 THEN
2912 hr_utility.set_location('Calling header validations', 10);
2913
2914 if l_payroll_rec.sender_id = ' ' or l_payroll_rec.sender_id is null then
2915 hr_utility.set_location('The Sender Id is missing.',10);
2916 fnd_file.put_line(fnd_file.output,'The Sender Id is missing.');
2917 l_err := true;
2918 end if;
2919
2920 if l_payroll_rec.tax_office_no = ' ' or l_payroll_rec.tax_office_no is null then
2921 hr_utility.set_location('The HMRC Office Number is missing.',10);
2922 fnd_file.put_line(fnd_file.output,'The HMRC Office Number is missing.');
2923 l_err := true;
2924 elsif validate_input(substr(l_payroll_rec.tax_office_no,1,3),'NUMBER') > 0
2925 then
2926 hr_utility.set_location('The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.',10);
2927 fnd_file.put_line(fnd_file.output,'The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.');
2928 l_err := true;
2929 end if;
2930
2931 if l_payroll_rec.tax_ref_no = ' ' or l_payroll_rec.tax_ref_no is null then
2932 hr_utility.set_location('The Employer PAYE Reference is missing.',10);
2933 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference is missing.');
2934 l_err := true;
2935 elsif validate_input(l_payroll_rec.tax_ref_no,'FULL_EDI') > 0 then
2936 hr_utility.set_location('The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.',10);
2937 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.');
2938 l_err := true;
2939 end if;
2940
2941 if l_payroll_rec.employer_name = ' ' or l_payroll_rec.employer_name is null then
2942 hr_utility.set_location('The Employer Name is missing.',10);
2943 fnd_file.put_line(fnd_file.output,'The Employer Name is missing.');
2944 l_err := true;
2945 elsif validate_input(l_payroll_rec.employer_name,'EMP_NAME') > 0 then
2946 hr_utility.set_location('The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.',10);
2947 fnd_file.put_line(fnd_file.output,'The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.');
2948 l_err := true;
2949 end if;
2950
2951 if l_payroll_rec.acc_ref_no = ' ' or l_payroll_rec.acc_ref_no is null then
2952 hr_utility.set_location('The Employer Accounts Office Ref is missing.',10);
2953 fnd_file.put_line(fnd_file.output,'The Employer Accounts Office Ref is missing.');
2954 l_err := true;
2955
2956 elsif (length(l_payroll_rec.acc_ref_no) > 13
2957 OR REGEXP_INSTR(l_payroll_rec.acc_ref_no,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
2958
2959 hr_utility.set_location ('Incorrect format entered for Employer Accounts Office Ref field. Valid format is NNNPANNNNNNNX.',10);
2960 fnd_file.put_line(fnd_file.output,'Incorrect format entered for Employer Accounts Office Ref field. Valid format is NNNPANNNNNNNX.');
2961 l_err := true;
2962 END IF;
2963
2964 END IF;
2965
2966 if (l_err) then
2967 raise l_exp;
2968 end if;
2969
2970 hr_utility.set_location('Leaving '|| l_proc, 10);
2971 EXCEPTION
2972 when l_exp then
2973 g_validation_check := 'N';
2974 raise_application_error(-20001,'Errors found while archiving data.');
2975
2976 END archinit;
2977 --
2978 --
2979 --
2980
2981 /*--------------------------------------------------------------------------
2982 Name : archive_data
2983 Purpose : This sets up the contexts needed for the live (non-archive)
2984 database items
2985 Arguments :
2986 Notes : Every possible context for a specific assignment action has to
2987 be added to the PL/SQL table
2988 --------------------------------------------------------------------------*/
2989 --
2990 --
2991 PROCEDURE archive_code(p_assactid IN NUMBER,
2992 p_effective_date IN DATE) IS
2993
2994 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
2995 error_found EXCEPTION;
2996 warning_found EXCEPTION;
2997 l_archive_tab action_info_table;
2998 l_archive_person boolean;
2999 l_archive_addr boolean;
3000 l_archive_type VARCHAR2(20);
3001 l_archive_asg boolean;
3002 l_primary_flag varchar2(1);
3003 l_assignment_number per_all_assignments_f.assignment_number%type;
3004 l_assignment_id per_all_assignments_f.assignment_id%type;
3005 l_eas_etext_asg_flag varchar2(1);
3006 l_per_addr_val_flag varchar2(1);
3007 l_eas_val_err boolean := False;
3008 l_personaddr_val_err boolean := False;
3009 l_err_log number;
3010 l_eff_temp_date varchar2(25);
3011 l_eff_date Date;
3012 l_ter_eff_date date;
3013 l_business_group_id number;
3014 l_tax_ref varchar2(100);
3015 l_first_asg_processed varchar2(1);
3016 l_effective_date date;
3017 l_pact_id number;
3018 l_warning varchar2(1);
3019 l_error varchar2(1) :='N';
3020 c_pact_id number;
3021 l_prev_expat varchar2(1);
3022 l_prev_occ_pension varchar2(1);
3023 l_prev_pay_non_indiv varchar2(1);
3024 l_expat varchar2(1);
3025 l_occ_pension varchar2(1);
3026 l_pay_non_indiv varchar2(1);
3027 min_asg number;
3028
3029 cursor csr_archive_type is
3030 select report_type,paa.payroll_action_id,BUSINESS_GROUP_ID,
3031 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
3032 from pay_assignment_actions paa,
3033 pay_payroll_actions ppa
3034 where paa.assignment_action_id = p_assactid
3035 and paa.payroll_action_id = ppa.payroll_action_id;
3036
3037 cursor get_already_processed_asg(p_pact_id number,p_person_id number,p_business_group_id number,p_effective_date date)
3038 is
3039 select min(paa.assignment_action_id)
3040 from pay_assignment_actions paa
3041 where
3042 paa.payroll_action_id = p_pact_id
3043 and paa.action_status = 'C'
3044 and paa.assignment_id in(
3045 select assignment_id
3046 from per_all_assignments_f paaf
3047 where paaf.person_id = p_person_id
3048 and paaf.business_group_id = p_business_group_id
3049 and paaf.effective_start_date =
3050 (select max(paaf1.effective_start_date)
3051 from per_all_assignments_f paaf1 where
3052 paaf1.assignment_id = paaf.assignment_id
3053 and paaf1.assignment_type = 'E'
3054 and paaf1.effective_start_date <= p_effective_date));
3055
3056
3057 cursor processed_asg(p_business_group_id number,p_assignment_id number,p_tax_ref varchar2,p_effective_date date)
3058 is
3059 select distinct ppa.payroll_action_id
3060 from pay_payroll_actions ppa,
3061 pay_assignment_actions paa,
3062 per_all_assignments_f asg1,
3063 per_all_assignments_f asg2
3064 where asg1.assignment_id = p_assignment_id
3065 and asg1.person_id = asg2.person_id
3066 and asg1.assignment_id <> asg2.assignment_id
3067 and asg1.business_group_id = p_business_group_id
3068 and asg2.business_group_id = asg1.business_group_id
3069 and paa.assignment_id = asg2.assignment_id
3070 and paa.ACTION_STATUS='C'
3071 and paa.payroll_action_id = ppa.payroll_action_id
3072 and ppa.business_group_id = asg1.business_group_id
3073 and ppa.REPORT_TYPE='RTI_EAS_REP_13'
3074 and substr(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'TAX_REF'),1,20) = substr(p_tax_ref,1,20)
3075 and asg1.effective_start_date =
3076 (select max(paaf1.effective_start_date)
3077 from per_all_assignments_f paaf1 where
3078 paaf1.assignment_id = asg1.assignment_id
3079 and paaf1.assignment_type = 'E'
3080 and paaf1.effective_start_date <= p_effective_date)
3081 and asg2.effective_start_date =
3082 (select max(paaf2.effective_start_date)
3083 from per_all_assignments_f paaf2 where
3084 paaf2.assignment_id = asg2.assignment_id
3085 and paaf2.assignment_type = 'E'
3086 and paaf2.effective_start_date <= p_effective_date);
3087
3088
3089 cursor fetch_all_paye_agg_asg(p_business_group_id number,p_tax_ref varchar2,p_person_id number,p_effective_date date)
3090 is
3091 select asg.assignment_id
3092 from per_all_assignments_f asg,
3093 pay_payrolls_f pay,
3094 hr_soft_coding_keyflex sck
3095 where asg.person_id = p_person_id
3096 and asg.business_group_id = p_business_group_id
3097 and pay.payroll_id=asg.payroll_id
3098 and pay.SOFT_CODING_KEYFLEX_ID=sck.SOFT_CODING_KEYFLEX_ID
3099 and sck.SEGMENT1=p_tax_ref
3100 and p_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE
3101 and asg.effective_start_date =
3102 (select max(paaf1.effective_start_date)
3103 from per_all_assignments_f paaf1 where
3104 paaf1.assignment_id = asg.assignment_id
3105 and paaf1.assignment_type = 'E'
3106 and paaf1.effective_start_date <= p_effective_date);
3107
3108 BEGIN
3109 hr_utility.set_location('Entering: '||l_proc,1);
3110
3111 open csr_archive_type;
3112 fetch csr_archive_type into l_archive_type,l_pact_id,l_business_group_id,l_tax_ref;
3113 close csr_archive_type;
3114
3115
3116 -- For EAS
3117 IF l_archive_type = 'RTI_EAS_REP_13' THEN
3118 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
3119 p_payroll_action_id => l_pact_id,
3120 p_token_name => 'START',
3121 p_token_value => l_eff_temp_date);
3122 l_eff_date := fnd_date.canonical_to_date(l_eff_temp_date);
3123
3124 END IF;
3125 l_effective_date := p_effective_date; -- For NINO
3126
3127
3128 -- For EAS
3129 IF l_archive_type = 'RTI_EAS_REP_13'
3130 THEN l_effective_date := l_eff_date;
3131 END IF;
3132 hr_utility.set_location('Archive Code l_effective_date Value:'||l_effective_date,909);
3133 l_archive_person := fetch_person_rec(p_assactid, l_effective_date,l_archive_tab(0));
3134
3135 l_archive_addr := fetch_address_rec(l_archive_tab(0).person_id,
3136 l_archive_tab(0).assignment_id,
3137 l_effective_date,
3138 l_archive_tab(1));
3139
3140 -- Fetching element details
3141
3142 hr_utility.trace('employee flag '||l_archive_tab(0).act_info18);
3143
3144 --Fetching assignments details for EAS
3145 IF l_archive_type = 'RTI_EAS_REP_13'
3146 THEN
3147 hr_utility.set_location('Fetching Assignment details ',30);
3148 l_archive_asg := fetch_eas_asg_rec(l_eff_date,p_assactid,l_archive_tab(0),l_archive_tab(2));
3149 END IF;
3150
3151 -- Validating fetched Person,Address,Assignment details for EAS
3152 IF l_archive_type = 'RTI_EAS_REP_13'
3153 THEN
3154 /* EAS 2013 Changes */
3155 /* Checking whether multiple PAYE aggregated assignments have same value for all the 3 new indicators*/
3156 if (l_archive_tab(0).act_info11 = 'Y' and l_archive_tab(0).act_info10 = 'Y') then
3157 open fetch_all_paye_agg_asg(l_business_group_id,l_tax_ref,l_archive_tab(0).person_id,l_eff_date);
3158 fetch fetch_all_paye_agg_asg into l_assignment_id;
3159 l_prev_expat := get_expat_indicator(l_assignment_id,l_business_group_id,l_eff_date);
3160 l_prev_occ_pension := get_occ_pension_indicator(l_assignment_id,l_business_group_id,l_eff_date);
3161 l_prev_pay_non_indiv := get_pay_non_indiv_ind(l_assignment_id,l_business_group_id,l_eff_date);
3162 loop
3163 fetch fetch_all_paye_agg_asg into l_assignment_id;
3164 exit when fetch_all_paye_agg_asg%notfound;
3165 l_expat := get_expat_indicator(l_assignment_id,l_business_group_id,l_eff_date);
3166 l_occ_pension := get_occ_pension_indicator(l_assignment_id,l_business_group_id,l_eff_date);
3167 l_pay_non_indiv := get_pay_non_indiv_ind(l_assignment_id,l_business_group_id,l_eff_date);
3168 if(((l_prev_expat <> '' or l_prev_expat is not null)and (l_expat = '' or l_expat is null))
3169 or ((l_prev_expat = '' or l_prev_expat is null) and (l_expat <> '' or l_expat is not null))
3170 or ((l_prev_expat <> '' or l_prev_expat is not null)and (l_expat <> '' or l_expat is not null)and (l_prev_expat <> l_expat))) then
3171 populate_run_msg(p_assactid,'There are aggregated assignments for an ExPat but indicators are not set across all assignments');
3172 fnd_file.put_line (fnd_file.LOG,'There are aggregated assignments for an ExPat but indicators are not set across all assignments');
3173 l_error :='Y';
3174 end if;
3175 if(((l_prev_occ_pension <> '' or l_prev_occ_pension is not null) and (l_occ_pension = '' or l_occ_pension is null))
3176 or ((l_prev_occ_pension = '' or l_prev_occ_pension is null) and (l_occ_pension <> '' or l_occ_pension is not null))
3177 or ((l_prev_occ_pension <> '' or l_prev_occ_pension is not null)and (l_occ_pension <> '' or l_occ_pension is not null)and (l_prev_occ_pension <> l_occ_pension)))then
3178 populate_run_msg(p_assactid,'There are aggregated assignments for the pensioner but indicators are not set across all assignments.');
3179 fnd_file.put_line (fnd_file.LOG,'There are aggregated assignments for the pensioner but indicators are not set across all assignments.');
3180 l_error :='Y';
3181 end if;
3182 if(((l_prev_pay_non_indiv <> '' or l_prev_pay_non_indiv is not null)and (l_pay_non_indiv = '' or l_pay_non_indiv is null))
3183 or ((l_prev_pay_non_indiv = '' or l_prev_pay_non_indiv is null) and (l_pay_non_indiv <> '' or l_pay_non_indiv is not null))
3184 or ((l_prev_pay_non_indiv <> '' or l_prev_pay_non_indiv is not null)and (l_pay_non_indiv <> '' or l_pay_non_indiv is not null)and (l_prev_pay_non_indiv <> l_pay_non_indiv))) then
3185 populate_run_msg(p_assactid,'There are aggregated assignments for payment to a non-individual but indicators are not set across all assignments');
3186 fnd_file.put_line (fnd_file.LOG,'There are aggregated assignments for payment to a non-individual but indicators are not set across all assignments.');
3187 l_error :='Y';
3188 end if;
3189 if l_error = 'Y' then
3190 close fetch_all_paye_agg_asg;
3191 raise error_found;
3192 end if;
3193 l_prev_expat := l_expat;
3194 l_prev_occ_pension := l_occ_pension;
3195 l_prev_pay_non_indiv := l_pay_non_indiv;
3196 end loop;
3197 close fetch_all_paye_agg_asg;
3198 end if;
3199 l_warning :='N';
3200 /* EAS 2013 Changes */
3201 /* Checking whether multiple assignments are PAYE aggregated*/
3202 if (l_archive_tab(0).act_info11 = 'Y' and l_archive_tab(0).act_info10 = 'Y') then
3203 open get_already_processed_asg(l_pact_id,l_archive_tab(0).person_id,l_business_group_id,l_effective_date);
3204 fetch get_already_processed_asg into min_asg;
3205 close get_already_processed_asg;
3206 if p_assactid <> min_asg then
3207 populate_run_msg(p_assactid,'Assignment '|| l_archive_tab(2).act_info3|| ' is aggregated & therefore not included on the EDI file as it has already been reported');
3208 l_warning :='Y';
3209 else
3210 open processed_asg(l_business_group_id,l_archive_tab(0).assignment_id,l_tax_ref,l_effective_date);
3211 loop
3212 fetch processed_asg into c_pact_id;
3213 exit when processed_asg%notfound;
3214 hr_utility.trace('payroll_action_id fetched by cursor: '||c_pact_id);
3215 end loop;
3216 if processed_asg%rowcount = 0 then
3217 null;
3218 elsif processed_asg%rowcount = 1 then
3219 if c_pact_id <> l_pact_id then
3220 populate_run_msg(p_assactid,'Assignment '|| l_archive_tab(2).act_info3|| ' is aggregated & therefore not included on the EDI file as it has already been reported on a previous EAS');
3221 l_warning :='Y';
3222 end if;
3223 else
3224 populate_run_msg(p_assactid,'Assignment '|| l_archive_tab(2).act_info3|| ' is aggregated & therefore not included on the EDI file as it has already been reported on a previous EAS');
3225 l_warning :='Y';
3226 end if;
3227 close processed_asg;
3228 end if;
3229 --g_person_id := l_archive_tab(0).person_id;
3230 if l_warning = 'Y' then
3231 raise warning_found;
3232 end if;
3233 end if;
3234
3235 hr_utility.set_location('Calling Person Address validations', 10);
3236 person_addr_validations(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
3237 hr_utility.trace('person address' ||l_per_addr_val_flag);
3238 IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3239 l_personaddr_val_err := TRUE;
3240 END IF;
3241
3242 hr_utility.set_location('Calling EAS validations', 10);
3243 eas_asg_etext_validations(p_assactid, p_effective_date, l_archive_tab, l_eas_etext_asg_flag);
3244 IF l_eas_etext_asg_flag = 'Y' THEN -- If validation fails
3245 l_eas_val_err := TRUE;
3246 END IF;
3247
3248 END IF;
3249 -- Validating fetched Person,Address details for NINO
3250 IF l_archive_type = 'RTI_NINO_REP'
3251 THEN
3252 hr_utility.set_location('Calling Person Address validations', 10);
3253 person_addr_validations_nino(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
3254 hr_utility.trace('person address' ||l_per_addr_val_flag);
3255 IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
3256 l_personaddr_val_err := TRUE;
3257 END IF;
3258 END IF;
3259
3260 if l_archive_person and l_archive_addr then
3261
3262 IF l_archive_type = 'RTI_EAS_REP_13'
3263 THEN
3264 IF l_personaddr_val_err OR l_eas_val_err -- If validation fails , that record will not be archived.
3265 THEN
3266 hr_utility.set_location('Validation failed, raise error.',999);
3267 raise error_found;
3268 ELSE
3269 hr_utility.set_location('Validation successful, archive data.',999);
3270 /* EAS 2013 changes*/
3271 /* set RTI sent flag for all aggregated assignments under the same PAYE reference*/
3272 set_new_starter_rti_sent(l_archive_tab(0).assignment_id);
3273
3274 if (l_archive_tab(0).act_info11 = 'Y' and l_archive_tab(0).act_info10 = 'Y') then
3275 open fetch_all_paye_agg_asg(l_business_group_id,l_tax_ref,l_archive_tab(0).person_id,l_eff_date);
3276 loop
3277 fetch fetch_all_paye_agg_asg into l_assignment_id;
3278 exit when fetch_all_paye_agg_asg%notfound;
3279 set_new_starter_rti_sent(l_assignment_id);
3280 end loop;
3281 close fetch_all_paye_agg_asg;
3282 end if;
3283 insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
3284 --cache last archived person_id
3285 --g_person_id := l_archive_tab(0).person_id;
3286 END IF;
3287 END IF;
3288
3289 IF l_archive_type = 'RTI_NINO_REP'
3290 THEN
3291 IF l_personaddr_val_err -- If validation fails , that record will not be archived.
3292 THEN
3293 hr_utility.set_location('Validation failed, raise error.',999);
3294 raise error_found;
3295 ELSE
3296 hr_utility.set_location('Validation successful, archive data.',999);
3297 insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
3298 END IF;
3299 END IF;
3300
3301 else
3302 raise error_found;
3303 end if;
3304
3305 hr_utility.set_location('Leaving: '||l_proc,999);
3306
3307 EXCEPTION
3308
3309 when warning_found then
3310 hr_utility.trace('Warnings found');
3311
3312 when error_found then
3313 IF l_archive_type in ('RTI_EAS_REP_13','RTI_NINO_REP')
3314 THEN
3315 raise_application_error(-20001,'Errors found while archiving data.');
3316 ELSE
3317 hr_utility.raise_error;
3318 END IF;
3319
3320 END archive_code;
3321 --
3322 --
3323 PROCEDURE deinitialization_code(pactid IN NUMBER)
3324 IS
3325 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
3326 l_counter number;
3327
3328 Cursor csr_is_etext_report IS
3329 Select report_type
3330 From pay_payroll_actions pact
3331 Where pact.payroll_action_id = pactid;
3332
3333 l_is_etext_report varchar2(50);
3334 l_request_id fnd_concurrent_requests.request_id%TYPE;
3335 xml_layout boolean;
3336
3337 -- Cursor to fetch Employer details
3338 /* EAS 2013 Changes */
3339 cursor csr_payroll_details(pactid NUMBER) is
3340 select
3341 nvl(UPPER(hoi.org_information11),' ') sender_id,
3342 decode(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
3343 fnd_number.number_to_canonical(pact.request_id) request_id,
3344 nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
3345 nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
3346 substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'NO_OF_PARTS'),1,2) no_of_parts,
3347 substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'PERMIT'),1,12) unique_part_id,
3348 lpad(substr(hoi.ORG_INFORMATION1,0,instr(hoi.ORG_INFORMATION1,'/')-1),3,0) tax_office_no,
3349 decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
3350 (lpad(substr(hoi.ORG_INFORMATION6,0,instr(hoi.ORG_INFORMATION6,'P')-1),3,0)
3351 || 'P'
3352 || substr(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+1,1)
3353 || lpad(substr(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+2,length(hoi.ORG_INFORMATION6)-3-(instr(hoi.ORG_INFORMATION6,'P')-1)),7,0)
3354 || substr(hoi.ORG_INFORMATION6,length(hoi.ORG_INFORMATION6),1)
3355 )
3356 as acc_ref_no,
3357
3358 pact.business_group_id bus_grp_id,
3359 pact.action_parameter_group_id act_param_grp_id
3360 from pay_payroll_actions pact,
3361 hr_organization_information hoi
3362 where pact.payroll_action_id=pactid
3363 and pact.business_group_id = hoi.organization_id
3364 and hoi.org_information_context = 'Tax Details References'
3365 and nvl(hoi.org_information10,'UK') = 'UK'
3366 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
3367 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
3368 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
3369
3370 l_payroll_rec csr_payroll_details%rowtype;
3371 l_acc_ref_no hr_organization_information.org_information6%type;
3372 l_action_info_id NUMBER(15);
3373 l_ovn NUMBER;
3374 l_exp exception;
3375 ---------------------
3376 ---------------------
3377 procedure write_header(report_type varchar2) is
3378 l_token varchar2(255);
3379 l_addr1 varchar2(255);
3380 l_addr2 varchar2(255);
3381 l_addr3 varchar2(255);
3382 l_addr4 varchar2(255);
3383 l_form varchar2(40);
3384 l_tax_ref varchar2(20);
3385 l_urgent varchar2(2);
3386 l_test varchar2(2);
3387 l_temp number;
3388 l_form_name varchar2(100);
3389 l_no_of_parts number;
3390 l_unique_part_id VARCHAR2(12);
3391
3392 cursor csr_leg_param is
3393 select legislative_parameters para,
3394 fnd_number.number_to_canonical(request_id) control_id,
3395 report_type,
3396 business_group_id
3397 from pay_payroll_actions pact
3398 where payroll_action_id = pactid;
3399
3400 cursor csr_date is
3401 select to_char(to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD'),'DD-MON-RRRR') effective_date
3402 from pay_payroll_actions pact
3403 where payroll_action_id = pactid;
3404
3405
3406 cursor csr_header_det(p_bus_id number,
3407 p_tax_ref varchar2) is
3408 select nvl(hoi.org_information11,' ') sender_id,
3409 nvl(upper(hoi.org_information2),' ') hrmc_office,
3410 nvl(upper(hoi.org_information4),' ') er_addr,
3411 nvl(upper(hoi.org_information3),' ') er_name
3412 from hr_organization_information hoi
3413 where hoi.organization_id = p_bus_id
3414 and hoi.org_information_context = 'Tax Details References'
3415 and nvl(hoi.org_information10,'UK') = 'UK'
3416 and upper(hoi.org_information1) = upper(p_tax_ref);
3417
3418 l_param csr_leg_param%rowtype;
3419 l_det csr_header_det%rowtype;
3420 l_eff_date date;
3421 begin
3422
3423 open csr_leg_param;
3424 fetch csr_leg_param into l_param;
3425 close csr_leg_param;
3426
3427 if l_param.report_type = 'RTI_EAS_REP_13' then
3428 open csr_date;
3429 fetch csr_date into l_eff_date;
3430 close csr_date;
3431 end if;
3432
3433 l_token := 'TAX_REF';
3434 l_temp := instr(l_param.para,l_token);
3435 l_tax_ref := substr(l_param.para, l_temp + length(l_token) + 1,
3436 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3437
3438 l_token := 'TEST';
3439 l_temp := instr(l_param.para,l_token);
3440 l_test := substr(l_param.para, l_temp + length(l_token) + 1,
3441 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3442 /* EAS 2013 Changes */
3443 l_token := 'NO_OF_PARTS';
3444 l_temp := instr(l_param.para,l_token);
3445 l_no_of_parts := substr(l_param.para, l_temp + length(l_token) + 1,
3446 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3447
3448
3449 l_token := 'PERMIT';
3450 l_temp := instr(l_param.para,l_token);
3451 l_unique_part_id := substr(l_param.para, l_temp + length(l_token) + 1,
3452 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3453
3454 open csr_header_det(l_param.business_group_id, l_tax_ref);
3455 fetch csr_header_det into l_det;
3456 close csr_header_det;
3457
3458 l_addr1 := l_det.er_addr;
3459 if length(l_addr1) > 35 then
3460 l_temp := instr(l_addr1, ',', 34 - length(l_addr1));
3461 if l_temp = 0 then
3462 l_temp := 35;
3463 end if;
3464 l_addr2 := ltrim(substr(l_addr1, 1 + l_temp),' ,');
3465 l_addr1 := substr(l_addr1,1,l_temp);
3466 end if;
3467 if length(l_addr2) > 35 then
3468 l_temp := instr(l_addr2, ',', 34 - length(l_addr2));
3469 if l_temp = 0 then
3470 l_temp := 35;
3471 end if;
3472 l_addr3 := ltrim(substr(l_addr2, 1 + l_temp),' ,');
3473 l_addr2 := substr(l_addr2,1,l_temp);
3474 end if;
3475 if length(l_addr3) > 35 then
3476 l_temp := instr(l_addr3, ',', 34 - length(l_addr3));
3477 if l_temp = 0 then
3478 l_temp := 35;
3479 end if;
3480 l_addr3 := ltrim(substr(l_addr3, 1 + l_temp),' ,');
3481 l_addr4 := substr(l_addr3,1,l_temp);
3482 end if;
3483
3484
3485 if l_param.report_type = 'RTI_EAS_REP_13' then
3486 l_form := 'RTI_EAS_REP_13';
3487 l_form_name :='Employer Alignment Submission';
3488 elsif l_param.report_type = 'RTI_NINO_REP' then
3489 l_form := 'RTI_NINO_REP';
3490 l_form_name :='NINO Verification Request Process';
3491 end if;
3492
3493 /* EAS 2013 Changes */
3494 fnd_file.put_line(fnd_file.output,' ');
3495 fnd_file.put_line(fnd_file.output,'EDI Transmission Report:');
3496 fnd_file.put_line(fnd_file.output,' ');
3497 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form_name);
3498 fnd_file.put_line(fnd_file.output,rpad('Sender : ',32) || l_det.sender_id);
3499 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
3500 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
3501 fnd_file.put_line(fnd_file.output,rpad('Test Transmission : ',32) || l_test);
3502 fnd_file.put_line(fnd_file.output,rpad('Number of Parts : ',32) || l_no_of_parts);
3503 --fnd_file.put_line(fnd_file.output,rpad('Part : ',32) || g_part_no);
3504 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
3505 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
3506 fnd_file.put_line(fnd_file.output,rpad('HRMC Office : ',32) || l_det.hrmc_office);
3507 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
3508 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
3509 fnd_file.put_line(fnd_file.output,rpad('Unique Part Identifier : ',32) || l_unique_part_id);
3510
3511 if length(l_addr2) > 0 then
3512 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
3513 end if;
3514 if length(l_addr3) > 0 then
3515 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
3516 end if;
3517 if length(l_addr4) > 0 then
3518 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
3519 end if;
3520 if report_type = 'RTI_EAS_REP_13' then
3521 fnd_file.put_line(fnd_file.output,rpad('Effective Date : ',32) || l_eff_date);
3522 end if;
3523
3524 end write_header;
3525
3526 procedure write_sub_header(p_type varchar2,report_type varchar2) is
3527 begin
3528 fnd_file.put_line(fnd_file.output,null);
3529 if p_type = 'E' then
3530 fnd_file.put_line(fnd_file.output,'The following employments have completed with error');
3531 /* EAS 2013 Changes */
3532 elsif p_type = 'W' then
3533 fnd_file.put_line(fnd_file.output,'The following employments have completed with warnings');
3534 else
3535 fnd_file.put_line(fnd_file.output,'The following employments have completed successfully');
3536 end if;
3537
3538 if report_type = 'RTI_EAS_REP_13' then
3539
3540 if p_type = 'C' then --Completed records
3541 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
3542 rpad('NI Number',11) ||
3543 rpad('Employee Name', 51) ||
3544 rpad('Start Date',16) ||
3545 rpad('End Date',16));
3546 else-- errored records
3547 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
3548 rpad('NI Number',11) ||
3549 rpad('Employee Name', 51) ||
3550 rpad('Start Date',16) ||
3551 rpad('End Date',16) ||
3552 rpad('Error Messages',30));
3553 end if;
3554
3555
3556 if p_type = 'C' then -- Completed records
3557 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
3558 rpad('-',10,'-') || ' ' ||
3559 rpad('-',50,'-') || ' ' ||
3560 rpad('-',15,'-') || ' ' ||
3561 rpad('-',15,'-') );
3562
3563 else -- errored records
3564 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
3565 rpad('-',10,'-') || ' ' ||
3566 rpad('-',50,'-') || ' ' ||
3567 rpad('-',15,'-') || ' ' ||
3568 rpad('-',15,'-') || ' ' ||
3569 rpad('-',30,'-'));
3570 end if;
3571 end if;
3572
3573 if report_type = 'RTI_NINO_REP' then
3574
3575 if p_type <> 'E' then -- completed records
3576 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
3577 rpad('NI Number',11) ||
3578 rpad('Employee Name', 52));
3579 else
3580 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
3581 rpad('NI Number',11) ||
3582 rpad('Employee Name', 51) ||
3583 rpad('Error Messages',30));
3584 end if;
3585
3586
3587 if p_type <> 'E' then
3588 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
3589 rpad('-',10,'-') || ' ' ||
3590 rpad('-',52,'-') );
3591
3592 else
3593 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
3594 rpad('-',10,'-') || ' ' ||
3595 rpad('-',50,'-') || ' ' ||
3596 rpad('-',30,'-'));
3597 end if;
3598 end if;
3599
3600 end write_sub_header;
3601
3602 procedure write_body(p_type varchar2,pactid IN NUMBER,report_type varchar2) is
3603
3604 l_count number;
3605 l_count_warns number;
3606 i number;
3607 l_temp varchar2(255);
3608 l_emp_count number;
3609 l_number varchar2(30);
3610 l_flag varchar2(1):='Y';
3611 l_start_date date;
3612 l_end_date date;
3613 l_nino_eff_date date;
3614 l_eff_date date;
3615
3616 cursor csr_payroll_details(pactid NUMBER) is
3617 select
3618 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date
3619 from pay_payroll_actions pact,
3620 hr_organization_information hoi
3621 where pact.payroll_action_id=pactid
3622 and pact.business_group_id = hoi.organization_id
3623 and hoi.org_information_context = 'Tax Details References'
3624 and nvl(hoi.org_information10,'UK') = 'UK'
3625 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
3626 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
3627 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
3628
3629 -- Cursor to fetch Completed records
3630 cursor get_asg_action_id is
3631 select distinct paa.assignment_action_id asg_action_id, paa.assignment_id
3632 , paaf.assignment_number
3633 from pay_payroll_actions ppa,
3634 pay_assignment_actions paa
3635 , per_all_assignments_f paaf
3636 where ppa.payroll_action_id = pactid -- pact_id
3637 and paa.payroll_action_id = ppa.payroll_action_id
3638 and paa.action_status = 'C'
3639 and paaf.assignment_id = paa.assignment_id
3640 and paaf.effective_start_date =
3641 (
3642 SELECT MAX(paaf2.effective_start_date)
3643 FROM per_all_assignments_f paaf2
3644 WHERE paaf2.assignment_id = paaf.assignment_id
3645 )
3646 ORDER BY
3647 paaf.assignment_number;
3648 -- Cursor to fetch Errored Records
3649 cursor get_asg_action_id_error is
3650 select distinct paa.assignment_action_id asg_action_id, paa.assignment_id
3651 , paaf.assignment_number
3652 from pay_payroll_actions ppa,
3653 pay_assignment_actions paa
3654 , per_all_assignments_f paaf
3655 where ppa.payroll_action_id = pactid -- pact_id
3656 and paa.payroll_action_id = ppa.payroll_action_id
3657 and paa.action_status = 'E'
3658 and paaf.assignment_id = paa.assignment_id
3659 and paaf.effective_start_date =
3660 (
3661 SELECT MAX(paaf2.effective_start_date)
3662 FROM per_all_assignments_f paaf2
3663 WHERE paaf2.assignment_id = paaf.assignment_id
3664 )
3665 ORDER BY
3666 paaf.assignment_number;
3667
3668 --Cursor to get effective date for NINO
3669 cursor csr_parameter_date is
3670 select effective_date
3671 from pay_payroll_actions
3672 where payroll_action_id = pactid;
3673
3674
3675 cursor get_person_details_archive(c_assignment_id number) is
3676 select pai_emp.action_information3 l_name,
3677 pai_emp.action_information4 f_name,
3678 pai_emp.action_information5 m_name,
3679 pai_emp.action_information6 title,
3680 pai_emp.action_information17 emp_no,
3681 nvl(pai_emp.action_information7,' ')ni_no,
3682 pai_emp.action_information17 emp_no1,
3683 to_char(to_date(substr(pai_asg.action_information5,1,10),'YYYY-MM-DD'),'DD-MON-RRRR') start_date,
3684 to_char(to_date(substr(pai_asg.action_information6,1,10),'YYYY-MM-DD'),'DD-MON-RRRR') end_date,
3685 pai_emp.action_information17 emp_no2
3686 from pay_action_information pai_emp,
3687 pay_action_information pai_asg,
3688 pay_assignment_actions paa
3689 where paa.payroll_action_id = pactid and paa.ACTION_STATUS = 'C' and paa.assignment_id = c_assignment_id
3690 and pai_emp.action_context_id = paa.assignment_action_id
3691 and pai_emp.action_information_category = 'GB RTI EMPLOYEE DETAILS'
3692 and pai_emp.action_context_type = 'AAP'
3693 and pai_asg.action_context_id = paa.assignment_action_id
3694 and pai_asg.action_information_category = 'GB RTI ASG DETAILS'
3695 and pai_asg.action_context_type = 'AAP';
3696
3697 cursor get_person_details(c_assignment_id number,p_eff_date date) is
3698 select distinct pap.first_name f_name ,
3699 pap.middle_names m_name,
3700 pap.last_name l_name,
3701 pap.title title,
3702 paa.assignment_number emp_no,
3703 nvl(pap.national_identifier,' ')ni_no,
3704 nvl(pap.employee_number,' ') employee_number,
3705 to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
3706 paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
3707 pap.person_id
3708 from per_all_assignments_f paa,
3709 per_assignment_status_types past,
3710 per_all_people_f pap
3711 ,pay_payroll_actions ppa
3712 where paa.person_id = pap.person_id
3713 and paa.assignment_id = c_assignment_id
3714 and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
3715 -- and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Added for Bug#13626488
3716 AND ppa.payroll_action_id = pactid
3717 AND p_eff_date BETWEEN pap.effective_start_date
3718 AND pap.effective_end_date
3719 AND paa.assignment_type = 'E'
3720 AND paa.effective_start_date =
3721 (
3722 SELECT MAX(paa2.effective_start_date)
3723 FROM per_all_assignments_f paa2
3724 WHERE paa2.assignment_id = paa.assignment_id
3725 AND paa2.effective_start_date <= p_eff_date
3726 )
3727 order by end_date desc;
3728
3729 cursor get_person_details_nino(c_assignment_id number) is
3730 select distinct pap.first_name f_name ,
3731 pap.middle_names m_name,
3732 pap.last_name l_name,
3733 pap.title title,
3734 paa.assignment_number emp_no,
3735 nvl(substr(pap.national_identifier,1,9),' ')ni_no,
3736 nvl(pap.employee_number,' ') employee_number,
3737 to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
3738 paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
3739 pap.person_id
3740 from per_all_assignments_f paa,
3741 per_assignment_status_types past,
3742 per_all_people_f pap
3743 where paa.person_id = pap.person_id
3744 and paa.assignment_id = c_assignment_id
3745 and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
3746 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
3747 and l_nino_eff_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
3748 order by end_date desc;
3749 -- Cursor to fetch error Messages from pay_message lines table
3750 cursor csr_err_msg(asg_action_id number) is
3751 select distinct line_text from pay_message_lines where source_id = asg_action_id and payroll_id = 100;
3752 /* EAS 2013 changes */
3753 -- Cursor to fetch warning Messages from pay_message lines table
3754 cursor csr_war_msg(asg_action_id number) is
3755 select distinct line_text from pay_message_lines where source_id = asg_action_id and payroll_id = 200;
3756
3757 err_msg csr_err_msg%rowtype;
3758 war_msg csr_war_msg%rowtype;
3759
3760 et_asg_rec get_person_details%rowtype;
3761 asg_rec get_person_details%rowtype;
3762 asg_rec1 get_person_details_archive%rowtype;
3763
3764 l_et_temp varchar2(255);
3765
3766 begin
3767
3768 l_count := 0;
3769 l_count_warns :=0;
3770 if report_type = 'RTI_NINO_REP' then
3771 open csr_parameter_date;
3772 fetch csr_parameter_date into l_nino_eff_date;
3773 close csr_parameter_date;
3774
3775 hr_utility.set_location('l_nino_eff_date '||l_nino_eff_date,009);
3776 end if;
3777 --Completed Records
3778 IF p_type = 'ET' THEN
3779 FOR action_id IN get_asg_action_id LOOP
3780
3781
3782
3783 if report_type = 'RTI_NINO_REP' then
3784 open get_person_details_nino(action_id.assignment_id);
3785 fetch get_person_details_nino into et_asg_rec;
3786 close get_person_details_nino;
3787 elsif report_type = 'RTI_EAS_REP_13' then
3788 open get_person_details_archive(action_id.assignment_id);
3789 fetch get_person_details_archive into asg_rec1;
3790 close get_person_details_archive;
3791 end if;
3792
3793
3794 OPEN csr_war_msg(action_id.asg_action_id);
3795 FETCH csr_war_msg INTO war_msg;
3796
3797 IF csr_war_msg%FOUND THEN
3798 l_flag :='N';
3799 else
3800 l_flag :='Y';
3801 end if;
3802 CLOSE csr_war_msg;
3803
3804 if (report_type = 'RTI_EAS_REP_13' and l_flag = 'Y') then
3805 l_et_temp := asg_rec1.l_name || ', '|| asg_rec1.title || ' ' ||
3806 asg_rec1.f_name || ' ' || asg_rec1.m_name;
3807 elsif report_type = 'RTI_NINO_REP' then
3808 l_et_temp := et_asg_rec.l_name || ', '|| et_asg_rec.title || ' ' ||
3809 et_asg_rec.f_name || ' ' || et_asg_rec.m_name;
3810 end if;
3811
3812
3813 if (report_type = 'RTI_EAS_REP_13' and l_flag = 'Y') then
3814
3815
3816 fnd_file.put_line(fnd_file.output,rpad(asg_rec1.emp_no, 18) || ' ' ||
3817 rpad(asg_rec1.ni_no ,10) || ' ' ||
3818 rpad(l_et_temp,50) || ' ' ||
3819 rpad(asg_rec1.start_date,15) || ' ' ||
3820 rpad(asg_rec1.end_date,15) );
3821
3822 elsif report_type = 'RTI_NINO_REP' then
3823 fnd_file.put_line(fnd_file.output,rpad(et_asg_rec.employee_number, 18) || ' ' ||
3824 rpad(et_asg_rec.ni_no ,10) || ' ' ||
3825 rpad(l_et_temp,50));
3826 end if;
3827
3828 l_count := l_count + 1;
3829 END LOOP;
3830 END IF;
3831
3832 open csr_payroll_details(pactid);
3833 fetch csr_payroll_details into l_eff_date;
3834 close csr_payroll_details;
3835
3836 -- Errored Records
3837 IF p_type = 'E' THEN null;
3838 FOR action_id IN get_asg_action_id_error LOOP
3839
3840 -- Fetching details
3841
3842
3843 if report_type = 'RTI_NINO_REP' then
3844 open get_person_details_nino(action_id.assignment_id);
3845 fetch get_person_details_nino into asg_rec;
3846 close get_person_details_nino;
3847 elsif report_type = 'RTI_EAS_REP_13' then
3848 open get_person_details(action_id.assignment_id,l_eff_date);
3849 fetch get_person_details into asg_rec;
3850 close get_person_details;
3851 end if;
3852
3853 open csr_err_msg(action_id.asg_action_id);
3854 fetch csr_err_msg into err_msg;
3855
3856 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' ||
3857 asg_rec.f_name || ' ' || asg_rec.m_name;
3858
3859 if report_type = 'RTI_EAS_REP_13' then
3860 if asg_rec.end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') then
3861 -- if asg_rec.end_date = '31-DEC-4712' then
3862 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3863 rpad(asg_rec.ni_no ,10) || ' ' ||
3864 rpad(l_temp,50) || ' ' ||
3865 rpad(asg_rec.start_date,15) || ' ' ||
3866 rpad(' ' ,15) || ' ' ||
3867 rpad(err_msg.line_text,160) ); -- error message
3868 else
3869 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3870 rpad(asg_rec.ni_no ,10) || ' ' ||
3871 rpad(l_temp,50) || ' ' ||
3872 rpad(asg_rec.start_date,15) || ' ' ||
3873 rpad(asg_rec.end_date,15) || ' ' ||
3874 rpad(err_msg.line_text,160) ); -- error message
3875
3876 end if;
3877 elsif report_type = 'RTI_NINO_REP' then
3878 fnd_file.put_line(fnd_file.output,rpad(asg_rec.employee_number, 18) || ' ' ||
3879 rpad(asg_rec.ni_no ,10) || ' ' ||
3880 rpad(l_temp,50) || ' ' ||
3881 rpad(err_msg.line_text,100) ); -- error message
3882 end if;
3883 -- To print remaining error messgaes if any
3884 loop
3885 fetch csr_err_msg into err_msg;
3886 exit when csr_err_msg%notfound;
3887 if report_type = 'RTI_EAS_REP_13' then
3888 fnd_file.put_line(fnd_file.output,rpad(' ',113,' ')||rpad(err_msg.line_text,160));
3889 elsif report_type = 'RTI_NINO_REP' then
3890 fnd_file.put_line(fnd_file.output,rpad(' ',81,' ')||rpad(err_msg.line_text,100));
3891 end if;
3892 end loop;
3893 close csr_err_msg;
3894
3895 l_count := l_count + 1;
3896 END LOOP;
3897 END IF;
3898
3899 fnd_file.put_line(fnd_file.output,null);
3900 /* EAS 2013 Changes */
3901 -- Warning Records
3902 IF p_type = 'W' THEN
3903
3904 FOR action_id IN get_asg_action_id
3905 LOOP
3906
3907 -- Fetching details
3908 open get_person_details(action_id.assignment_id,l_eff_date);
3909 fetch get_person_details into asg_rec;
3910 close get_person_details;
3911
3912 -- fnd_file.put_line(fnd_file.LOG,'action_id.asg_action_id: '||action_id.asg_action_id);
3913
3914 OPEN csr_war_msg(action_id.asg_action_id);
3915 FETCH csr_war_msg INTO war_msg;
3916
3917 -- fnd_file.put_line(fnd_file.LOG,'action_id.asg_action_id: '||action_id.asg_action_id);
3918
3919 IF csr_war_msg%NOTFOUND THEN
3920 NULL;
3921 ELSE
3922 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' || asg_rec.f_name || ' ' || asg_rec.m_name;
3923 if asg_rec.end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') then
3924
3925 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3926 rpad(asg_rec.ni_no ,10) || ' ' ||
3927 rpad(l_temp,50) || ' ' ||
3928 rpad(asg_rec.start_date,15) || ' ' ||
3929 rpad(' ' ,15) || ' ' ||
3930 rpad(war_msg.line_text,150) ); -- error message
3931 else
3932 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3933 rpad(asg_rec.ni_no ,10) || ' ' ||
3934 rpad(l_temp,50) || ' ' ||
3935 rpad(asg_rec.start_date,15) || ' ' ||
3936 rpad(asg_rec.end_date,15) || ' ' ||
3937 rpad(war_msg.line_text,150) ); -- error message
3938 END IF;
3939 l_count_warns := l_count_warns + 1;
3940 END IF;
3941 CLOSE csr_war_msg;
3942
3943 END LOOP;
3944
3945 END IF;
3946 fnd_file.put_line(fnd_file.output,NULL);
3947
3948 IF p_type = 'E' THEN
3949 fnd_file.put_line(fnd_file.output,'Total Number of employments completed with error : ' || l_count);
3950 ELSIF p_type = 'W' THEN
3951 fnd_file.put_line(fnd_file.output,'Total Number of employments completed with warning : ' || l_count_warns);
3952 ELSE
3953 fnd_file.put_line(fnd_file.output,'Total Number of employments completed successfully :' || l_count);
3954 END IF;
3955 l_counter := l_counter + l_count;
3956 END write_body;
3957
3958 procedure write_footer is
3959
3960 cursor get_person_count is
3961 select count(distinct(paaf.person_id))
3962 from pay_payroll_actions ppa,
3963 pay_assignment_actions paa,
3964 per_all_assignments_f paaf
3965 where ppa.payroll_action_id = pactid -- pact_id
3966 and paa.payroll_action_id = ppa.payroll_action_id
3967 and paa.action_status = 'C'
3968 and paaf.assignment_id = paa.assignment_id;
3969
3970 l_person_count number;
3971 begin
3972 open get_person_count;
3973 fetch get_person_count into l_person_count;
3974 close get_person_count;
3975
3976 fnd_file.put_line(fnd_file.output,null);
3977 fnd_file.put_line(fnd_file.output,'Total Number Of employments : ' || l_counter);
3978 fnd_file.put_line(fnd_file.output,null);
3979 fnd_file.put_line(fnd_file.output,'Total Number of employees successfully processed : ' || l_person_count);
3980 end write_footer;
3981
3982 ---------------------
3983 ---------------------
3984 BEGIN
3985 hr_utility.trace('Entering deinit: '||l_proc);
3986
3987 -- To avoid re-archiving while Retry
3988 delete from pay_action_information pai
3989 where pai.action_context_id = pactid
3990 and pai.action_context_type = 'PA'
3991 and pai.action_information_category in ('RTI PAYROLL INFO');
3992
3993
3994 open csr_payroll_details(pactid);
3995 fetch csr_payroll_details into l_payroll_rec;
3996 close csr_payroll_details;
3997
3998 /* EAS 2013 Changes */
3999
4000 --g_part_no=g_part_no + 1;
4001 pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
4002
4003 hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
4004 hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
4005 hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
4006 hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
4007 hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
4008
4009
4010 hr_utility.set_location('Archiving RTI PAYROLL INFO',60);
4011
4012 pay_action_information_api.create_action_information (
4013 p_action_information_id => l_action_info_id
4014 , p_action_context_id => pactid
4015 , p_action_context_type => 'PA'
4016 , p_object_version_number => l_ovn
4017 -- , p_effective_date => l_payroll_rec.effective_date
4018 , p_source_id => NULL
4019 , p_source_text => NULL
4020 , p_action_information_category => 'RTI PAYROLL INFO'
4021 , p_action_information1 => pactid
4022 , p_action_information3 => NULL
4023 , p_action_information4 => l_payroll_rec.employer_name
4024 , p_action_information6 => l_payroll_rec.tax_office_no
4025 , p_action_information7 => l_payroll_rec.tax_ref_no
4026 , p_action_information8 => l_payroll_rec.payroll_ver
4027 , p_action_information9 => l_payroll_rec.no_of_parts
4028 , p_action_information10 => l_payroll_rec.unique_part_id
4029 , p_action_information11 => l_payroll_rec.acc_ref_no
4030 , p_action_information12 => l_payroll_rec.sender_id
4031 , p_action_information13 => l_payroll_rec.test_indicator
4032 , p_action_information14 => to_char(l_payroll_rec.request_id));
4033
4034 OPEN csr_is_etext_report;
4035 FETCH csr_is_etext_report INTO l_is_etext_report;
4036 CLOSE csr_is_etext_report;
4037
4038 l_counter := 0;
4039 write_header(l_is_etext_report);
4040 write_sub_header('C',l_is_etext_report);
4041
4042 IF l_is_etext_report IN ('RTI_EAS_REP_13', 'RTI_NINO_REP')
4043 THEN
4044 write_body('ET',pactid,l_is_etext_report);
4045 ELSE
4046 write_body('C',pactid,l_is_etext_report);
4047 END IF;
4048
4049 write_sub_header('E',l_is_etext_report);
4050 write_body('E',pactid,l_is_etext_report);
4051 /* EAS 2013 Changes */
4052 write_sub_header('W',l_is_etext_report);
4053 write_body('W',pactid,l_is_etext_report);
4054 write_footer;
4055
4056 -- For Spawning the output Concurrent Program
4057 IF (l_is_etext_report='RTI_EAS_REP_13') THEN
4058
4059 hr_utility.set_location('Spawning RTI Employer Alignment Submission Output process',1);
4060
4061 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTIEASOP13','en','US','ETEXT');
4062 IF (xml_layout = true and g_validation_check = 'Y')
4063 THEN
4064
4065 l_request_id := fnd_request.submit_request
4066 (application => 'PAY'
4067 ,program => 'PYGBRTIEASOP13'
4068 ,argument1 => pactid
4069 );
4070
4071 Commit;
4072 --check for process submit error
4073 IF l_request_id = 0
4074 THEN
4075 hr_utility.set_location('Error spawning new process',1);
4076 END IF;
4077 END IF;
4078 END IF;
4079 IF (l_is_etext_report='RTI_NINO_REP') THEN
4080
4081 hr_utility.set_location('Spawning RTI NINO verification Output process',1);
4082
4083 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTININOOP','en','US','ETEXT');
4084
4085 IF xml_layout = true
4086 THEN
4087
4088 l_request_id := fnd_request.submit_request
4089 (application => 'PAY'
4090 ,program => 'PYGBRTININOOP'
4091 ,argument1 => pactid
4092 );
4093
4094 /*l_request_id := fnd_request.submit_request
4095 (application => 'PAY'
4096 ,program => 'PYGBRTININOOP'
4097 ,argument1 => 'ARCHIVE' --Process Name
4098 ,argument2 => 'RTI_NINO_OUTPUT'
4099 ,argument3 => 'GB'
4100 ,argument4 => '0001/01/01 00:00:00'
4101 ,argument5 => fnd_date.date_to_canonical(sysdate)
4102 ,argument6 => 'XML'
4103
4104 ,argument7 => l_payroll_rec.bus_grp_id
4105 ,argument8 => null --Magnetic File Name
4106 ,argument9 => null --Report File Name
4107 ,argument10 => l_payroll_rec.act_param_grp_id --Action Parameter Group
4108 ,argument11 => 'ARCHIVE_REQUEST_ID='||pactid
4109
4110 ,argument12 => 'REP_GROUP=RTI_NINO_OUTPUT'
4111 ,argument13 => 'REP_CAT=RTI_NINO_OUTPUT');
4112
4113 */
4114 Commit;
4115
4116 --check for process submit error
4117 IF l_request_id = 0
4118 THEN
4119 hr_utility.set_location('Error spawning new process',1);
4120 END IF;
4121 END IF;
4122
4123
4124 END IF;
4125
4126 hr_utility.set_location('Leaving: '||l_proc,999);
4127
4128 END deinitialization_code;
4129 --
4130 -- Function for validating Date fields
4131 FUNCTION date_validate (c_assignment_action_id NUMBER,
4132 p_mode VARCHAR2,
4133 p_validate_date DATE)
4134 RETURN NUMBER
4135 IS
4136
4137
4138 cursor csr_parameter_info is
4139 select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
4140 NVL(fnd_date.canonical_to_date(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'START')),sysdate)
4141 from pay_payroll_actions ppa
4142 ,pay_assignment_actions paa
4143 where paa.assignment_action_id = c_assignment_action_id
4144 and ppa.payroll_action_id = paa.payroll_action_id;
4145
4146 l_date_valid DATE;
4147 l_return_valid NUMBER;
4148 l_test_submission VARCHAR2(1);
4149 l_tax_date DATE;
4150 l_tax_year VARCHAR2(4);
4151 l_tax_year_start DATE ;
4152 l_effective_date DATE;
4153 BEGIN
4154 l_return_valid := 1;
4155 open csr_parameter_info;
4156 fetch csr_parameter_info into l_test_submission,l_tax_date;
4157 close csr_parameter_info;
4158
4159 l_effective_date:= l_tax_date;
4160
4161 l_tax_year := to_char(l_tax_date,'RRRR');
4162 if (l_tax_date > to_date(l_tax_year||'0405','RRRRMMDD')) THEN
4163 l_tax_date := ADD_MONTHS(to_date(l_tax_year||'0405','RRRRMMDD'),12) ; /*tax year end date*/
4164 else
4165 l_tax_date := to_date(l_tax_year||'0405','RRRRMMDD'); /*tax year end date*/
4166 end if;
4167
4168 l_tax_date := fnd_date.canonical_to_date(to_char(l_tax_date,'RRRRMMDD'));
4169
4170 l_tax_year_start := add_months(l_tax_date,-12)+1 ;
4171
4172 l_date_valid := p_validate_date;
4173
4174 hr_utility.set_location('date_validate.l_effective_date '||l_effective_date,1);
4175 hr_utility.set_location('date_validate.l_tax_date'||l_tax_date,1);
4176
4177 -- Start Date Validation
4178 if (p_mode = 'UK_EMPL_DATE') then
4179 if (l_test_submission = 'N') then
4180 if (l_date_valid > (l_effective_date+30)) then
4181 l_return_valid := 0;
4182 end if;
4183 else
4184 if (l_date_valid > add_months(l_tax_date,12)) then
4185 l_return_valid := 0;
4186 end if;
4187 end if;
4188 -- End Date Validation
4189 /* EAS 2013 Changes */
4190 elsif (p_mode = 'UK_EMPL_DATE_LEAVING') then
4191 if (l_test_submission = 'N') then
4192 if (l_date_valid > (l_effective_date+30)) then
4193 l_return_valid := 0;
4194 end if;
4195 if(l_date_valid < add_months(l_tax_year_start,-72)) then
4196 l_return_valid := 0;
4197 end if;
4198 else
4199 if (l_date_valid > add_months(l_tax_date,12)) then
4200 l_return_valid := 0;
4201 end if;
4202 if(l_date_valid < add_months(l_tax_year_start,-72)) then
4203 l_return_valid := 0;
4204 end if;
4205 end if;
4206
4207 end if;
4208
4209 return l_return_valid;
4210 END date_validate;
4211
4212 function get_eff_start_date (asg_id number,eff_date date,l_tax_ref varchar2)
4213 return date is
4214 l_eff_date date;
4215 cursor csr_get_start_date is
4216 select paaf.effective_start_date from
4217 per_all_assignments_f paaf,
4218 pay_all_payrolls_f papf ,
4219 hr_soft_coding_keyflex flex,
4220 per_assignment_status_types past
4221 where paaf.assignment_id = asg_id and paaf.effective_end_date = eff_date - 1
4222 and papf.payroll_id = paaf.payroll_id
4223 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
4224 and upper(l_tax_ref) = upper(flex.segment1)
4225 and paaf.assignment_status_type_id = past.assignment_status_type_id
4226 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
4227 begin
4228 open csr_get_start_date;
4229 fetch csr_get_start_date into l_eff_date;
4230 if csr_get_start_date%notfound then
4231 close csr_get_start_date;
4232 return eff_date;
4233 else
4234 close csr_get_start_date;
4235 l_eff_date := get_eff_start_date(asg_id,l_eff_date,l_tax_ref);
4236 return l_eff_date;
4237 end if;
4238 end;
4239
4240 function get_eff_end_date (asg_id number,eff_date date,l_tax_ref varchar2)
4241 return date is
4242 l_eff_date date;
4243 cursor csr_get_end_date is
4244 select paaf.effective_end_date
4245 from
4246 per_all_assignments_f paaf,
4247 pay_all_payrolls_f papf ,
4248 hr_soft_coding_keyflex flex,
4249 per_assignment_status_types past
4250 where paaf.assignment_id = asg_id and paaf.effective_start_date = eff_date + 1
4251 and papf.payroll_id = paaf.payroll_id
4252 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
4253 and upper(l_tax_ref) = upper(flex.segment1)
4254 and paaf.assignment_status_type_id = past.assignment_status_type_id
4255 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
4256
4257 begin
4258 open csr_get_end_date;
4259 fetch csr_get_end_date into l_eff_date;
4260 if csr_get_end_date%notfound then
4261 close csr_get_end_date;
4262 return eff_date;
4263 else
4264 close csr_get_end_date;
4265 l_eff_date := get_eff_end_date(asg_id,l_eff_date,l_tax_ref);
4266 return l_eff_date;
4267 end if;
4268 end;
4269
4270 --Procedure to fetch Tax year Start date using effective date parameter of concurrent Program.
4271 FUNCTION fetch_soy(l_effective_date date) return date
4272 IS
4273 l_tax_year_start_date DATE;
4274 BEGIN
4275 If l_effective_date >= to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) Then
4276 l_tax_year_start_date := to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
4277 Else
4278 l_tax_year_start_date := to_date('06-04-'||to_char(to_number(substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
4279 End If;
4280 return l_tax_year_start_date;
4281 END fetch_soy;
4282
4283 end PAY_GB_RTI_EAS;