1 PACKAGE BODY PAY_GB_RTI_FPS_VALIDATE_13 as
2 /* $Header: pygbrtifpsval13.pkb 120.0.12020000.5 2013/03/20 11:12:15 rajganga noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2012 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_RTI_FPS_VALIDATE_13
10 Purpose:
11 It fetches the archived data for RTI FPS Processes 2013-14, and validates it.
12 This is a UK Specific payroll package.
13
14 History:
15 19-Dec-2012 rajganga 115.0 15890022 Created.
16 09-FEB-2012 ssarap 115.1 16276411 NHS Issues.
17 18-MAR-2013 sampmand 115.2 16505555 Removed final submission parameter check for ECON.
18 ========================================================================================*/
19 --
20 -- Global variables
21 g_package CONSTANT VARCHAR2(50):= 'pay_gb_rti_fps_validations.';
22 g_econ_ni_check varchar2(1);
23
24 ---
25 PROCEDURE populate_run_msg(
26 p_assignment_action_id IN NUMBER
27 ,p_message_text IN varchar2
28 )
29 IS
30 PRAGMA AUTONOMOUS_TRANSACTION;
31 BEGIN
32 hr_utility.set_location(' Entering: populate_run_msg',111);
33
34 INSERT INTO pay_message_lines(line_sequence,
35 payroll_id,
36 message_level,
37 source_id,
38 source_type,
39 line_text)
40 VALUES(
41 pay_message_lines_s.nextval
42 ,100
43 ,'F'
44 ,p_assignment_action_id
45 ,'A'
46 ,substr(p_message_text,1,240)
47 );
48
49 hr_utility.set_location(' Leaving: populate_run_msg',999);
50 COMMIT;
51 EXCEPTION
52 WHEN OTHERS THEN
53 hr_utility.trace('Error occured in populate_run_msg');
54 RAISE;
55 END populate_run_msg;
56
57
58 /*--------------------------------------------------------------------------
59 Name : HEADER_VALIDATIONS
60 Purpose : This procedure validates all Employer Header Details
61 Arguments : Payroll Action ID
62 Notes :
63 --------------------------------------------------------------------------*/
64 PROCEDURE header_validations(p_payroll_action_id IN NUMBER,flag OUT nocopy VARCHAR2)
65 IS
66 l_proc CONSTANT VARCHAR2(70) := g_package || 'header_validations';
67 l_err BOOLEAN;
68 l_exp EXCEPTION;
69
70 cursor csr_payroll_details(pactid NUMBER) is
71 select
72 nvl(UPPER(hoi.org_information11),' ') sender_id,
73 hoi.org_information1 paye_ref,
74 hoi.organization_id orgid,
75 nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
76 nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
77 lpad(nvl(substr(hoi.org_information1,1,3),' '),3,0) tax_office_no,
78 decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
79 nvl(upper(substr(hoi.org_information6,1,13)),' ') acc_ref_no,
80 hoi.org_information7 econ,
81 SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIN_SUB'),1,20) final_submission
82 from pay_payroll_actions pact,
83 hr_organization_information hoi
84 where pact.payroll_action_id=pactid
85 and pact.business_group_id = hoi.organization_id
86 and hoi.org_information_context = 'Tax Details References'
87 and nvl(hoi.org_information10,'UK') = 'UK'
88 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
89 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
90 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
91
92
93 cursor csr_payroll_details_2(paye_ref VARCHAR2,orgid number) is
94 select
95 hoi.org_information2 sautr,
96 hoi.org_information3 cotaxref
97 from hr_organization_information hoi
98 where hoi.organization_id = orgid
99 and hoi.org_information_context = 'Tax Details References Cont'
100 and hoi.org_information1 = paye_ref;
101
102
103 l_payroll_rec csr_payroll_details%rowtype;
104 l_tax_details csr_payroll_details_2%rowtype;
105
106 BEGIN
107
108 hr_utility.set_location('Entering '|| l_proc, 10);
109 l_err := FALSE;
110 flag := 'Y';
111
112 open csr_payroll_details(p_payroll_action_id);
113 fetch csr_payroll_details into l_payroll_rec;
114 close csr_payroll_details;
115
116 open csr_payroll_details_2(l_payroll_rec.paye_ref,l_payroll_rec.orgid);
117 fetch csr_payroll_details_2 into l_tax_details;
118 close csr_payroll_details_2;
119
120 hr_utility.set_location('l_payroll_rec.paye_ref '|| l_payroll_rec.paye_ref, 10);
121 hr_utility.set_location('l_payroll_rec.orgid '|| l_payroll_rec.orgid, 10);
122 hr_utility.set_location('l_tax_details.sautr '|| l_tax_details.sautr, 10);
123 hr_utility.set_location('l_tax_details.cotaxref '|| l_tax_details.cotaxref, 10);
124
125 -- Sender Id
126 if l_payroll_rec.sender_id = ' ' or l_payroll_rec.sender_id is null then
127 hr_utility.set_location('The Sender ID is missing.',10);
128 fnd_file.put_line(fnd_file.output,'The Sender ID is missing.');
129 l_err := true;
130 end if;
131
132 -- HMRC Office Number
133 if l_payroll_rec.tax_office_no = ' ' or l_payroll_rec.tax_office_no is null then
134 hr_utility.set_location('The HMRC Office number is missing.',10);
135 fnd_file.put_line(fnd_file.output,'The HMRC Office number is missing.');
136 l_err := true;
137 elsif validate_input(substr(l_payroll_rec.tax_office_no,1,3),'NUMBER') > 0
138 then
139 hr_utility.set_location('The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.',10);
140 fnd_file.put_line(fnd_file.output,'The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.');
141 l_err := true;
142 end if;
143
144 -- Employer PAYE Ref
145 if l_payroll_rec.tax_ref_no = ' ' or l_payroll_rec.tax_ref_no is null then
146 hr_utility.set_location('The Employer PAYE Reference is missing.',10);
147 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference is missing.');
148 l_err := true;
149 elsif validate_input(l_payroll_rec.tax_ref_no,'FULL_EDI') > 0 then
150 hr_utility.set_location('The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.',10);
151 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.');
152 l_err := true;
153 end if;
154
155 -- Employer Name
156 if l_payroll_rec.employer_name = ' ' or l_payroll_rec.employer_name is null then
157 hr_utility.set_location('The Employer Name is missing.',10);
158 fnd_file.put_line(fnd_file.output,'The Employer Name is missing.');
159 l_err := true;
160 elsif validate_input(l_payroll_rec.employer_name,'EMP_NAME') > 0 then
161 hr_utility.set_location('The Employer Name '||l_payroll_rec.employer_name ||' has invalid characters.',10);
162 fnd_file.put_line(fnd_file.output,'The Employer Name '||l_payroll_rec.employer_name ||' has invalid characters.');
163 l_err := true;
164 end if;
165
166 -- Accounts Office Number
167 if l_payroll_rec.acc_ref_no = ' ' or l_payroll_rec.acc_ref_no is null then
168 hr_utility.set_location('The Account Office Reference Number is missing.',10);
169 fnd_file.put_line(fnd_file.output,'The Account Office Reference Number is missing.');
170 l_err := true;
171 elsif (length(l_payroll_rec.acc_ref_no) > 13
172 OR REGEXP_INSTR(l_payroll_rec.acc_ref_no,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
173
174 hr_utility.set_location ('Incorrect format entered for Accounts Office Ref Num field. Valid format is NNNPANNNNNNNX.',10);
175 fnd_file.put_line(fnd_file.output,'Incorrect format entered for Accounts Office Ref Num field. Valid format is NNNPANNNNNNNX.');
176 l_err := true;
177 END IF;
178
179 -- Econ
180 if l_payroll_rec.econ is not null then
181 IF econ_validate(l_payroll_rec.econ) = 0 THEN
182 hr_utility.set_location('ECON Number '||l_payroll_rec.econ ||' is invalid',10);
183 fnd_file.put_line(fnd_file.output,'ECON Number '||l_payroll_rec.econ ||' is invalid.');
184 l_err := true;
185 END IF;
186 END IF;
187
188 --Self Assessment Unique Tax Ref (SAUTR) and Corporation Tax Ref (COTAXREF) are mutually exclusive, so only one value can be present at a time.
189
190 if l_tax_details.sautr is not null and l_tax_details.cotaxref is not null then
191 hr_utility.set_location('Self Assessment Unique Tax Ref (SAUTR) and Corporation Tax Ref (COTAXREF) are mutually exclusive, hence only one value can be present at a time.',10);
192 fnd_file.put_line(fnd_file.output,'Self Assessment Unique Tax Ref (SAUTR) and Corporation Tax Ref (COTAXREF) are mutually exclusive, hence only one value can be present at a time.');
193 l_err := true;
194 end if;
195
196 if (l_err) then
197 flag := 'N';
198 raise l_exp; -- Raising Exception when error is found
199 end if;
200
201 hr_utility.set_location('Leaving '|| l_proc, 10);
202 EXCEPTION
203 when l_exp then
204 raise_application_error(-20001,'Errors found while archiving data.');
205
206 END header_validations;
207 --
208 --
209 /*
210 Hints For Development :
211 Person - p_tab_rec_data(0)
212 Address - p_tab_rec_data(1)
213 Asg1 - p_tab_rec_data(2)
214 Asg2 - p_tab_rec_data(3)
215 NI - p_tab_rec_data(4) to p_tab_rec_data(7) -- 4 NIs
216
217
218 Declarations :
219 Person - ASPP,Passport
220 Asg - All
221 NI - All
222
223 */
224
225 /*--------------------------------------------------------------------------
226 Name : PERSON_ADDRESS_VALIDATIONS
227 Purpose : This procedure validates all Employee Details
228 Arguments : Assignment Action ID , Effective Date , Record
229 Notes :
230 --------------------------------------------------------------------------*/
231
232 -- Person Address validations
233 PROCEDURE person_address_validations( p_assactid IN NUMBER,
234 p_effective_date IN DATE,
235 p_tab_rec_data IN pay_gb_rti_fps_archive.action_info_table,
236 edi_validation_fail out nocopy varchar2) IS
237
238 l_proc CONSTANT VARCHAR2(70):= g_package||'person_address_validations';
239
240 --Assignment Number
241 l_assignment_number per_assignments_f.assignment_number%TYPE;
242
243 -- Address details
244 l_address_line1 per_addresses.address_line1%TYPE;
245 l_address_line2 per_addresses.address_line2%TYPE;
246 l_address_line3 per_addresses.address_line3%TYPE;
247 l_address_line4 per_addresses.address_line3%TYPE;
248 l_town_or_city per_addresses.town_or_city%TYPE;
249 l_postal_code per_addresses.postal_code%TYPE;
250 l_country per_addresses.country%TYPE;
251
252 -- Person details
253 l_last_name per_people_f.last_name%TYPE;
254 l_first_name per_people_f.first_name%TYPE;
255 l_middle_name per_people_f.middle_names%TYPE;
256 l_national_identifier per_people_f.national_identifier%TYPE;
257 l_title per_people_f.title%TYPE;
258 l_date_of_birth per_people_f.date_of_birth%TYPE;
259 l_sex per_people_f.sex%TYPE;
260 l_passport_no varchar2(50);
261
262 -- Partner Person Details
263 l_partner_last_name per_people_f.last_name%TYPE;
264 l_partner_first_name per_people_f.first_name%TYPE;
265 l_partner_middle_name per_people_f.middle_names%TYPE;
266 l_partner_national_id per_people_f.national_identifier%TYPE;
267
268 -- Assignment Details required for validations
269 l_aspp number;
270 l_start_date date;
271
272 l_effective_date date;
273 l_year1 number;
274 l_year2 number;
275
276 BEGIN
277 hr_utility.set_location('Entering '||l_proc,10);
278 edi_validation_fail := 'N'; -- Initialized validation fail to N
279
280 -- Assignment Number
281 l_assignment_number := p_tab_rec_data(2).act_info13;
282
283 -- Initializing the Address variables
284 l_address_line1 := p_tab_rec_data(1).act_info5;
285 l_address_line2 := p_tab_rec_data(1).act_info6;
286 l_address_line3 := p_tab_rec_data(1).act_info7;
287 l_town_or_city := p_tab_rec_data(1).act_info8;
288 l_country := p_tab_rec_data(1).act_info13;
289 l_postal_code := p_tab_rec_data(1).act_info12;
290
291 -- Initializing the Person variables
292 l_last_name := p_tab_rec_data(0).act_info3;
293 l_first_name := p_tab_rec_data(0).act_info4;
294 l_middle_name := p_tab_rec_data(0).act_info5;
295 l_title := p_tab_rec_data(0).act_info6;
296 l_national_identifier := p_tab_rec_data(0).act_info7;
297 l_date_of_birth := (to_date(substr(p_tab_rec_data(0).act_info8,1,10),'YYYY-MM-DD'));
298 l_sex := p_tab_rec_data(0).act_info9;
299 l_partner_last_name := p_tab_rec_data(0).act_info12;
300 l_partner_first_name := p_tab_rec_data(0).act_info13;
301 l_partner_middle_name := p_tab_rec_data(0).act_info14;
302 l_partner_national_id := p_tab_rec_data(0).act_info15;
303 l_passport_no := p_tab_rec_data(0).act_info16;
304
305 --Assignment Details required for validations
306 l_aspp := p_tab_rec_data(3).act_info22;
307 l_start_date := (to_date(substr(p_tab_rec_data(2).act_info1,1,10),'YYYY-MM-DD'));
308
309 --Local declarations
310 l_year1 := to_number(to_char(sysdate,'YYYY'));
311 l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
312 l_year1 := l_year1- l_year2;
313
314
315 --Address validations
316
317 --Address line 1 is mandatory if Start date is not null or nino is null
318 IF l_start_date is not null or l_national_identifier is null then
319 IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
320 populate_run_msg(p_assactid,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
321 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
322 edi_validation_fail := 'Y';
323 ELSIF validate_input(l_address_line1,'ADDRESS') > 0 THEN
324 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
325 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
326 edi_validation_fail := 'Y';
327 END IF;
328 END IF;
329
330 --Address line 2 is mandatory if Start date is not null
331 IF l_start_date is not null then
332 IF l_address_line2 = ' ' OR l_address_line2 IS NULL THEN
333 populate_run_msg(p_assactid,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
334 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
335 edi_validation_fail := 'Y';
336 ELSIF validate_input(l_address_line2,'ADDRESS') > 0 THEN
337 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
338 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
339 edi_validation_fail := 'Y';
340 END IF;
341 END IF;
342
343 IF l_address_line3 <> ' ' and l_address_line3 IS NOT NULL THEN
344 IF validate_input(l_address_line3,'ADDRESS') > 0 THEN
345 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
346 fnd_file.put_line (fnd_file.LOG,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
347 edi_validation_fail := 'Y';
348 END IF;
349 END IF;
350
351 IF l_town_or_city <> ' ' and l_town_or_city IS NOT NULL THEN
352 IF validate_input(l_town_or_city,'ADDRESS') > 0 THEN
353 populate_run_msg(p_assactid,'The Town Or City ' || l_town_or_city || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
354 fnd_file.put_line (fnd_file.LOG,'The Town Or City ' || l_town_or_city ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
355 edi_validation_fail := 'Y';
356 END IF;
357 END IF;
358
359 IF l_country <> ' ' and l_country IS NOT NULL THEN
360 IF validate_input(l_country,'COUNTRY') > 0 THEN
361 populate_run_msg(p_assactid,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
362 fnd_file.put_line (fnd_file.LOG,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
363 edi_validation_fail := 'Y';
364 END IF;
365 END IF;
366
367 IF l_postal_code <> ' ' and l_postal_code IS NOT NULL THEN
368 IF validate_input(l_postal_code,'POSTALCODE') > 0 THEN
369 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
370 fnd_file.put_line (fnd_file.LOG,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
371 edi_validation_fail := 'Y';
372 END IF;
373 END IF;
374
375
376 -- Person validations
377
378 -- Last name is mandatory field
379 IF l_last_name = ' ' OR l_last_name IS NULL THEN
380 populate_run_msg(p_assactid,'The Last Name of the assignment ' || l_assignment_number || ' is missing.');
381 fnd_file.put_line (fnd_file.LOG,'The Last Name of the assignment '|| l_assignment_number || ' is missing.');
382 edi_validation_fail := 'Y';
383 ELSIF validate_input(l_last_name,'NAME') > 0 THEN
384 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
385 fnd_file.put_line (fnd_file.LOG,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
386 edi_validation_fail := 'Y';
387 END IF;
388
389 -- First name is mandatory field
390 IF l_first_name = ' ' OR l_first_name IS NULL THEN
391 populate_run_msg(p_assactid,'The First Name of the assignment '|| l_assignment_number || ' is missing.');
392 fnd_file.put_line (fnd_file.LOG,'The First Name of the assignment ' || l_assignment_number || ' is missing.');
393 edi_validation_fail := 'Y';
394 ELSIF validate_input(l_first_name,'NAME') > 0 THEN
395 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
396 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_first_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
397 edi_validation_fail := 'Y';
398 END IF;
399
400 -- Middle name = Second First name
401 IF l_middle_name <> ' ' AND l_middle_name IS NOT NULL THEN
402 IF validate_input(l_middle_name,'NAME') > 0 THEN
403 populate_run_msg(p_assactid,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
404 fnd_file.put_line (fnd_file.LOG,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
405 edi_validation_fail := 'Y';
406 END IF;
407 END IF;
408
409
410 IF l_title <> ' ' AND l_title is not null THEN
411 IF validate_input(l_title,'TITLE') > 0 THEN
412 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
413 fnd_file.put_line (fnd_file.LOG,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
414 edi_validation_fail := 'Y';
415 END IF;
416 END IF;
417
418 IF l_national_identifier IS NOT NULL AND
419 hr_gb_utility.ni_validate(l_national_identifier,sysdate) <> 0 THEN
420 populate_run_msg(p_assactid,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
421 fnd_file.put_line (fnd_file.LOG,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
422 edi_validation_fail := 'Y';
423 END IF;
424
425 -- Gender is mandatory field
426 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
427 populate_run_msg(p_assactid,'Gender is not specified for the assignment ' || l_assignment_number || '.');
428 fnd_file.put_line (fnd_file.LOG,'Gender is not specified for the assignment ' || l_assignment_number || '.');
429 edi_validation_fail := 'Y';
430 END IF;
431
432 IF l_passport_no <> ' ' OR l_passport_no IS NOT NULL THEN
433 IF validate_input(l_passport_no,'PASSPORT') > 0 THEN
434 populate_run_msg(p_assactid,'The Passport Number ' || l_passport_no || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
435 fnd_file.put_line (fnd_file.LOG,'The Passport Number ' || l_passport_no || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
436 edi_validation_fail := 'Y';
437 END IF;
438 END IF;
439
440 -- Date of Birth is mandatory field
441 IF l_date_of_birth IS NULL THEN
442 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is missing.');
443 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is missing.');
444 edi_validation_fail := 'Y';
445 END IF;
446
447 IF ((l_date_of_birth > sysdate) OR (l_year1 > 130)) THEN
448 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is invalid');
449 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is invalid');
450 edi_validation_fail := 'Y';
451 END IF;
452
453 -- Partner validations
454
455 -- ASPP rate is mandatory if Partner details are found
456 IF l_partner_last_name <> ' ' OR l_partner_last_name IS NOT NULL THEN
457
458 IF l_aspp IS NULL THEN
459 populate_run_msg(p_assactid, 'Additional Statutory Paternity Pay for assignment ' || l_assignment_number||' is missing, although Partner details are present.');
460 fnd_file.put_line (fnd_file.LOG,'Additional Statutory Paternity Pay for assignment ' || l_assignment_number||' is missing, although Partner details are present.');
461 edi_validation_fail := 'Y';
462 END IF;
463
464 IF validate_input(l_partner_last_name,'NAME') > 0 THEN
465 populate_run_msg(p_assactid,'The Partner Last Name ' || l_partner_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
466 fnd_file.put_line (fnd_file.LOG,'The Partner Last Name ' || l_partner_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
467 edi_validation_fail := 'Y';
468 END IF;
469 END IF;
470
471 -- Partner First name is mandatory field if last name is present
472 IF l_partner_last_name IS NOT NULL AND (l_partner_first_name = ' ' OR l_partner_first_name IS NULL) THEN
473 populate_run_msg(p_assactid,'The Partner First Name of the assignment '|| l_assignment_number || ' is missing.');
474 fnd_file.put_line (fnd_file.LOG,'The Partner First Name of the assignment ' || l_assignment_number || ' is missing.');
475 edi_validation_fail := 'Y';
476 ELSIF validate_input(l_partner_first_name,'NAME') > 0 THEN
477 populate_run_msg(p_assactid,'The Partner First Name ' || l_partner_first_name ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
478 fnd_file.put_line (fnd_file.LOG,'The Partner First Name ' || l_partner_first_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
479 edi_validation_fail := 'Y';
480 END IF;
481
482 -- Middle name = Second First name
483 IF l_partner_middle_name <> ' ' AND l_partner_middle_name IS NOT NULL THEN
484 IF validate_input(l_partner_middle_name,'NAME') > 0 THEN
485 populate_run_msg(p_assactid,'The Partner First Name ' || l_partner_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
486 fnd_file.put_line (fnd_file.LOG,'The Partner First Name ' || l_partner_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
487 edi_validation_fail := 'Y';
488 END IF;
489 END IF;
490
491 IF l_partner_national_id IS NOT NULL AND
492 hr_gb_utility.ni_validate(l_partner_national_id,sysdate) <> 0 THEN
493 populate_run_msg(p_assactid,'The Partner National Insurance number ' || l_partner_national_id || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
494 fnd_file.put_line (fnd_file.LOG,'The Partner National Insurance number ' || l_partner_national_id || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
495 edi_validation_fail := 'Y';
496 END IF;
497
498
499 hr_utility.set_location('Leaving'||l_proc,10);
500 END person_address_validations;
501 --
502 -- Person Address validations
503 PROCEDURE person_addr_validations_aggr( p_assactid IN NUMBER,
504 p_effective_date IN DATE,
505 p_tab_rec_data IN pay_gb_rti_fps_archive.action_info_table,
506 p_tab_rec_det1 IN pay_gb_rti_fps_archive.act_info_rec,
507 p_tab_rec_det2 IN pay_gb_rti_fps_archive.act_info_rec,
508 edi_validation_fail out nocopy varchar2) IS
509
510 l_proc CONSTANT VARCHAR2(70):= g_package||'person_addr_validations_aggr';
511
512 --Assignment Number
513 l_assignment_number per_assignments_f.assignment_number%TYPE;
514
515 -- Address details
516 l_address_line1 per_addresses.address_line1%TYPE;
517 l_address_line2 per_addresses.address_line2%TYPE;
518 l_address_line3 per_addresses.address_line3%TYPE;
519 l_address_line4 per_addresses.address_line3%TYPE;
520 l_town_or_city per_addresses.town_or_city%TYPE;
521 l_postal_code per_addresses.postal_code%TYPE;
522 l_country per_addresses.country%TYPE;
523
524 -- Person details
525 l_last_name per_people_f.last_name%TYPE;
526 l_first_name per_people_f.first_name%TYPE;
527 l_middle_name per_people_f.middle_names%TYPE;
528 l_national_identifier per_people_f.national_identifier%TYPE;
529 l_title per_people_f.title%TYPE;
530 l_date_of_birth per_people_f.date_of_birth%TYPE;
531 l_sex per_people_f.sex%TYPE;
532 l_passport_no varchar2(50);
533
534 -- Partner Person Details
535 l_partner_last_name per_people_f.last_name%TYPE;
536 l_partner_first_name per_people_f.first_name%TYPE;
537 l_partner_middle_name per_people_f.middle_names%TYPE;
538 l_partner_national_id per_people_f.national_identifier%TYPE;
539
540 -- Assignment Details required for validations
541 l_aspp number;
542 l_start_date date;
543
544 l_effective_date date;
545 l_year1 number;
546 l_year2 number;
547
548 BEGIN
549 hr_utility.set_location('Entering '||l_proc,10);
550 edi_validation_fail := 'N'; -- Initialized validation fail to N
551
552 -- Assignment Number
553 --l_assignment_number := p_tab_rec_data(2).act_info13;
554 l_assignment_number := p_tab_rec_det1.act_info13;
555
556 -- Initializing the Address variables
557 l_address_line1 := p_tab_rec_data(1).act_info5;
558 l_address_line2 := p_tab_rec_data(1).act_info6;
559 l_address_line3 := p_tab_rec_data(1).act_info7;
560 l_town_or_city := p_tab_rec_data(1).act_info8;
561 l_country := p_tab_rec_data(1).act_info13;
562 l_postal_code := p_tab_rec_data(1).act_info12;
563
564 -- Initializing the Person variables
565 l_last_name := p_tab_rec_data(0).act_info3;
566 l_first_name := p_tab_rec_data(0).act_info4;
567 l_middle_name := p_tab_rec_data(0).act_info5;
568 l_title := p_tab_rec_data(0).act_info6;
569 l_national_identifier := p_tab_rec_data(0).act_info7;
570 l_date_of_birth := (to_date(substr(p_tab_rec_data(0).act_info8,1,10),'YYYY-MM-DD'));
571 l_sex := p_tab_rec_data(0).act_info9;
572 l_partner_last_name := p_tab_rec_data(0).act_info12;
573 l_partner_first_name := p_tab_rec_data(0).act_info13;
574 l_partner_middle_name := p_tab_rec_data(0).act_info14;
575 l_partner_national_id := p_tab_rec_data(0).act_info15;
576 l_passport_no := p_tab_rec_data(0).act_info16;
577
578 --Assignment Details required for validations
579 --l_aspp := p_tab_rec_data(3).act_info22;
580 l_aspp := p_tab_rec_det2.act_info22;
581 l_start_date := (to_date(substr(p_tab_rec_det1.act_info1,1,10),'YYYY-MM-DD'));
582
583 --Local declarations
584 l_year1 := to_number(to_char(sysdate,'YYYY'));
585 l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
586 l_year1 := l_year1- l_year2;
587
588
589 --Address validations
590
591 --Address line 1 is mandatory if Start date is not null or nino is null
592 IF l_start_date is not null or l_national_identifier is null then
593 IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
594 populate_run_msg(p_assactid,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
595 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
596 edi_validation_fail := 'Y';
597 ELSIF validate_input(l_address_line1,'ADDRESS') > 0 THEN
598 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
599 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
600 edi_validation_fail := 'Y';
601 END IF;
602 END IF;
603
604 --Address line 2 is mandatory if Start date is not null
605 IF l_start_date is not null then
606 IF l_address_line2 = ' ' OR l_address_line2 IS NULL THEN
607 populate_run_msg(p_assactid,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
608 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
609 edi_validation_fail := 'Y';
610 ELSIF validate_input(l_address_line2,'ADDRESS') > 0 THEN
611 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
612 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
613 edi_validation_fail := 'Y';
614 END IF;
615 END IF;
616
617
618 IF l_address_line3 <> ' ' and l_address_line3 IS NOT NULL THEN
619 IF validate_input(l_address_line3,'ADDRESS') > 0 THEN
620 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
621 fnd_file.put_line (fnd_file.LOG,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
622 edi_validation_fail := 'Y';
623 END IF;
624 END IF;
625
626 IF l_town_or_city <> ' ' and l_town_or_city IS NOT NULL THEN
627 IF validate_input(l_town_or_city,'ADDRESS') > 0 THEN
628 populate_run_msg(p_assactid,'The Town Or City ' || l_town_or_city || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
629 fnd_file.put_line (fnd_file.LOG,'The Town Or City ' || l_town_or_city ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
630 edi_validation_fail := 'Y';
631 END IF;
632 END IF;
633
634 IF l_country <> ' ' and l_country IS NOT NULL THEN
635 IF validate_input(l_country,'COUNTRY') > 0 THEN
636 populate_run_msg(p_assactid,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
637 fnd_file.put_line (fnd_file.LOG,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
638 edi_validation_fail := 'Y';
639 END IF;
640 END IF;
641
642 IF l_postal_code <> ' ' and l_postal_code IS NOT NULL THEN
643 IF validate_input(l_postal_code,'POSTALCODE') > 0 THEN
644 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
645 fnd_file.put_line (fnd_file.LOG,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
646 edi_validation_fail := 'Y';
647 END IF;
648 END IF;
649
650
651 -- Person validations
652
653 -- Last name is mandatory field
654 IF l_last_name = ' ' OR l_last_name IS NULL THEN
655 populate_run_msg(p_assactid,'The Last Name of the assignment ' || l_assignment_number || ' is missing.');
656 fnd_file.put_line (fnd_file.LOG,'The Last Name of the assignment '|| l_assignment_number || ' is missing.');
657 edi_validation_fail := 'Y';
658 ELSIF validate_input(l_last_name,'NAME') > 0 THEN
659 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
660 fnd_file.put_line (fnd_file.LOG,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
661 edi_validation_fail := 'Y';
662 END IF;
663
664 -- First name is mandatory field
665 IF l_first_name = ' ' OR l_first_name IS NULL THEN
666 populate_run_msg(p_assactid,'The First Name of the assignment '|| l_assignment_number || ' is missing.');
667 fnd_file.put_line (fnd_file.LOG,'The First Name of the assignment ' || l_assignment_number || ' is missing.');
668 edi_validation_fail := 'Y';
669 ELSIF validate_input(l_first_name,'NAME') > 0 THEN
670 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
671 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_first_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
672 edi_validation_fail := 'Y';
673 END IF;
674
675 -- Middle name = Second First name
676 IF l_middle_name <> ' ' AND l_middle_name IS NOT NULL THEN
677 IF validate_input(l_middle_name,'NAME') > 0 THEN
678 populate_run_msg(p_assactid,'The First Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
679 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
680 edi_validation_fail := 'Y';
681 END IF;
682 END IF;
683
684
685 IF l_title <> ' ' AND l_title is not null THEN
686 IF validate_input(l_title,'TITLE') > 0 THEN
687 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
688 fnd_file.put_line (fnd_file.LOG,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
689 edi_validation_fail := 'Y';
690 END IF;
691 END IF;
692
693 IF l_national_identifier IS NOT NULL AND
694 hr_gb_utility.ni_validate(l_national_identifier,sysdate) <> 0 THEN
695 populate_run_msg(p_assactid,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
696 fnd_file.put_line (fnd_file.LOG,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
697 edi_validation_fail := 'Y';
698 END IF;
699
700 -- Gender is mandatory field
701 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
702 populate_run_msg(p_assactid,'Gender is not specified for the assignment ' || l_assignment_number || '.');
703 fnd_file.put_line (fnd_file.LOG,'Gender is not specified for the assignment ' || l_assignment_number || '.');
704 edi_validation_fail := 'Y';
705 END IF;
706
707 IF l_passport_no <> ' ' OR l_passport_no IS NOT NULL THEN
708 IF validate_input(l_passport_no,'PASSPORT') > 0 THEN
709 populate_run_msg(p_assactid,'The Passport Number ' || l_passport_no || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
710 fnd_file.put_line (fnd_file.LOG,'The Passport Number ' || l_passport_no || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
711 edi_validation_fail := 'Y';
712 END IF;
713 END IF;
714
715 -- Date of Birth is mandatory field
716 IF l_date_of_birth IS NULL THEN
717 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is missing.');
718 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is missing.');
719 edi_validation_fail := 'Y';
720 END IF;
721
722 IF ((l_date_of_birth > sysdate) OR (l_year1 > 130)) THEN
723 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is invalid');
724 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is invalid');
725 edi_validation_fail := 'Y';
726 END IF;
727
728 -- Partner validations
729
730 -- ASPP rate is mandatory if Partner details are found
731 IF l_partner_last_name <> ' ' OR l_partner_last_name IS NOT NULL THEN
732
733 IF l_aspp IS NULL THEN
734 populate_run_msg(p_assactid, 'Additional Statutory Paternity Pay for assignment ' || l_assignment_number||' is missing, although Partner details are present.');
735 fnd_file.put_line (fnd_file.LOG,'Additional Statutory Paternity Pay for assignment ' || l_assignment_number||' is missing, although Partner details are present.');
736 edi_validation_fail := 'Y';
737 END IF;
738
739 IF validate_input(l_partner_last_name,'NAME') > 0 THEN
740 populate_run_msg(p_assactid,'The Partner Last Name ' || l_partner_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
741 fnd_file.put_line (fnd_file.LOG,'The Partner Last Name ' || l_partner_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
742 edi_validation_fail := 'Y';
743 END IF;
744 END IF;
745
746 -- Partner First name is mandatory field if last name is present
747 IF l_partner_last_name IS NOT NULL AND (l_partner_first_name = ' ' OR l_partner_first_name IS NULL) THEN
748 populate_run_msg(p_assactid,'The Partner First Name of the assignment '|| l_assignment_number || ' is missing.');
749 fnd_file.put_line (fnd_file.LOG,'The Partner First Name of the assignment ' || l_assignment_number || ' is missing.');
750 edi_validation_fail := 'Y';
751 ELSIF validate_input(l_partner_first_name,'NAME') > 0 THEN
752 populate_run_msg(p_assactid,'The Partner First Name ' || l_partner_first_name ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
753 fnd_file.put_line (fnd_file.LOG,'The Partner First Name ' || l_partner_first_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
754 edi_validation_fail := 'Y';
755 END IF;
756
757 -- Middle name = Second First name
758 IF l_partner_middle_name <> ' ' AND l_partner_middle_name IS NOT NULL THEN
759 IF validate_input(l_partner_middle_name,'NAME') > 0 THEN
760 populate_run_msg(p_assactid,'The Partner First Name ' || l_partner_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
761 fnd_file.put_line (fnd_file.LOG,'The Partner First Name ' || l_partner_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
762 edi_validation_fail := 'Y';
763 END IF;
764 END IF;
765
766 IF l_partner_national_id IS NOT NULL AND
767 hr_gb_utility.ni_validate(l_partner_national_id,sysdate) <> 0 THEN
768 populate_run_msg(p_assactid,'The Partner National Insurance number ' || l_partner_national_id || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
769 fnd_file.put_line (fnd_file.LOG,'The Partner National Insurance number ' || l_partner_national_id || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
770 edi_validation_fail := 'Y';
771 END IF;
772
773
774 hr_utility.set_location('Leaving'||l_proc,10);
775 END person_addr_validations_aggr;
776 --
777
778
779 --
780
781 /*--------------------------------------------------------------------------
782 Name : ASSIGNMENT_VALIDATIONS
783 Purpose : This procedure validates all Assignment Details
784 Arguments : Assignment Action ID , Effective Date , Assignment Record
785 Notes :
786 --------------------------------------------------------------------------*/
787
788 PROCEDURE assignment_validations(p_assactid IN NUMBER,
789 p_effective_date IN DATE,
790 p_tab_rec_data IN pay_gb_rti_fps_archive.action_info_table,
791 edi_validation_fail out nocopy varchar2) IS
792
793 -- To fetch Effective date
794 CURSOR get_effective_date IS
795 SELECT ppa.effective_date
796 FROM pay_payroll_actions ppa, pay_assignment_actions paa
797 WHERE ppa.payroll_action_id = paa.payroll_action_id
798 AND paa.assignment_action_id = p_assactid;
799
800 --Declarations
801 l_proc CONSTANT VARCHAR2(70):= g_package||'assignment_validations';
802
803 -- Assignment Number
804 l_assignment_number per_assignments_f.assignment_number%TYPE;
805
806 -- Assignment Fields
807 l_start_date date;
808 l_end_date date;
809 l_payment_date date;
810 l_starter_declaration varchar2(100);
811 l_stay_over_six_months varchar2(100);
812 l_stay_less_six_months varchar2(100);
813 l_living_abroad varchar2(100);
814 l_occ_pension_indicator varchar2(100);
815 l_annual_occupational_pension number;
816 l_state_pension_indicator varchar2(100);
817 l_annual_state_pension number;
818 l_payroll_id varchar2(50);
819 l_old_payroll_id varchar2(50);
820 l_pay_frequency varchar2(200);
821 l_payment_week_number number;
822 l_payment_month_number number;
823 l_no_of_earnings number;
824 l_payment_after_leaving varchar(100);
825 l_no_of_hours_worked varchar2(10);
826 l_director_nic_method varchar(100);
827 l_week_director_app number;
828 l_taxable_pay_to_date number;
829 l_total_tax number;
830 l_student_loan_repayment number;
831
832 l_taxable_pay number;
833 l_non_tax_nic_payment number;
834 l_net_pay_deductions number;
835 l_pay_after_statutory number;
836 l_benefits_taxed_payroll number;
837 l_benefits_class1_nic number;
838 l_benefits_n_class1_nic number;
839 l_employee_pension_contri number;
840 l_items_class1_nic number;
841 l_emp_contri_not_paid number;
842 l_student_loan_repay number;
843 l_tax_deducted_refunded number;
844 l_ssp number;
845 l_smp number;
846 l_ospp number;
847 l_sap number;
848 l_aspp number;
849
850 l_tax_code varchar2(15);
851 l_bacs_hash_code varchar2(64);
852
853 -- Person Details needed to validations
854 l_gender varchar2(100);
855
856 -- Local Declarations
857 l_msg_value varchar2(100);
858 l_eff_date date;
859
860
861 BEGIN
862 hr_utility.set_location('Entering: '||l_proc,1);
863
864 edi_validation_fail := 'N'; -- Initialized validation fail to N
865
866 -- Assigning values to the local variables
867 l_assignment_number := p_tab_rec_data(2).act_info13;
868
869 -- Assignment Fields
870 l_start_date := (to_date(substr(p_tab_rec_data(2).act_info1,1,10),'YYYY-MM-DD'));
871 l_end_date := (to_date(substr(p_tab_rec_data(2).act_info2,1,10),'YYYY-MM-DD'));
872 l_payment_date := (to_date(substr(p_tab_rec_data(2).act_info3,1,10),'YYYY-MM-DD'));
873 l_starter_declaration := p_tab_rec_data(2).act_info4;
874 l_stay_over_six_months := p_tab_rec_data(2).act_info6;
875 l_stay_less_six_months := p_tab_rec_data(2).act_info7;
876 l_living_abroad := p_tab_rec_data(2).act_info8;
877 l_occ_pension_indicator := p_tab_rec_data(2).act_info12;
878 l_payroll_id := p_tab_rec_data(2).act_info13;
879 l_old_payroll_id := p_tab_rec_data(2).act_info15;
880 l_pay_frequency := p_tab_rec_data(2).act_info17;
881 l_payment_week_number := p_tab_rec_data(2).act_info18;
882 l_payment_month_number := p_tab_rec_data(2).act_info19;
883 l_no_of_earnings := p_tab_rec_data(2).act_info20;
884 l_payment_after_leaving := p_tab_rec_data(2).act_info22;
885 l_no_of_hours_worked := p_tab_rec_data(2).act_info23;
886 l_director_nic_method := p_tab_rec_data(2).act_info24;
887 l_week_director_app := p_tab_rec_data(2).act_info25;
888 l_annual_occupational_pension := p_tab_rec_data(2).act_info26;
889
890 l_taxable_pay_to_date := p_tab_rec_data(3).act_info1;
891 l_total_tax := p_tab_rec_data(3).act_info2;
892 l_student_loan_repayment := p_tab_rec_data(3).act_info3;
893 l_taxable_pay := p_tab_rec_data(3).act_info6;
894 l_non_tax_nic_payment := p_tab_rec_data(3).act_info7;
895 l_net_pay_deductions := p_tab_rec_data(3).act_info8;
896 l_pay_after_statutory := p_tab_rec_data(3).act_info9;
897 l_benefits_taxed_payroll := p_tab_rec_data(3).act_info10;
898 l_benefits_class1_nic := p_tab_rec_data(3).act_info11;
899 l_benefits_n_class1_nic := p_tab_rec_data(3).act_info12;
900 l_employee_pension_contri := p_tab_rec_data(3).act_info13;
901 l_items_class1_nic := p_tab_rec_data(3).act_info14;
902 l_emp_contri_not_paid := p_tab_rec_data(3).act_info15;
903 l_student_loan_repay := p_tab_rec_data(3).act_info16;
904 l_tax_deducted_refunded := p_tab_rec_data(3).act_info17;
905 l_ssp := p_tab_rec_data(3).act_info18;
906 l_smp := p_tab_rec_data(3).act_info19;
907 l_ospp := p_tab_rec_data(3).act_info20;
908 l_sap := p_tab_rec_data(3).act_info21;
909 l_aspp := p_tab_rec_data(3).act_info22;
910
911 l_tax_code := p_tab_rec_data(3).act_info25;
912 l_bacs_hash_code := p_tab_rec_data(2).act_info28;
913
914 l_gender := p_tab_rec_data(0).act_info9;
915
916
917 -- Assignment Validations
918
919
920 IF l_start_date IS NOT NULL
921 and date_validate(p_assactid,'UK_EMPL_DATE',l_start_date) = 0 THEN
922 populate_run_msg(p_assactid,' The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
923 fnd_file.put_line (fnd_file.LOG,'The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
924 edi_validation_fail := 'Y';
925 END IF;
926
927 IF l_end_date IS NOT NULL
928 and date_validate(p_assactid,'UK_EMPL_DATE_LEAVING',l_end_date) = 0 THEN
929 populate_run_msg(p_assactid,' The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
930 fnd_file.put_line (fnd_file.LOG,'The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
931 edi_validation_fail := 'Y';
932 END IF;
933
934 -- Payment Date is mandatory field
935 IF l_payment_date IS NULL then
936 populate_run_msg(p_assactid, 'The Payment Date is missing for assignment ' || l_assignment_number || '.');
937 fnd_file.put_line (fnd_file.LOG,'The Payment Date is missing for assignment ' || l_assignment_number || '.');
938 edi_validation_fail := 'Y';
939 END IF;
940
941
942 IF l_payment_date IS NOT NULL THEN
943 IF l_payment_date < fnd_date.canonical_to_date('2012/04/06 00:00:00') THEN
944 populate_run_msg(p_assactid, 'The Payment Date ' ||l_payment_date|| ' for assignment ' || l_assignment_number||' is invalid.');
945 fnd_file.put_line (fnd_file.LOG,'The Payment Date ' ||l_payment_date|| ' for assignment ' || l_assignment_number||' is invalid.');
946 edi_validation_fail := 'Y';
947 END IF;
948 END IF;
949
950 -- Starter Declaration is mandatory field
951 IF l_start_date IS NOT NULL and (l_starter_declaration = ' ' OR l_starter_declaration IS NULL )then
952 populate_run_msg(p_assactid, 'The Starter Declaration is missing for assignment ' || l_assignment_number || '.');
953 fnd_file.put_line (fnd_file.LOG,'The Starter Declaration is missing for assignment ' || l_assignment_number || '.');
954 edi_validation_fail := 'Y';
955 END IF;
956
957 IF l_starter_declaration IS NOT NULL THEN
958 IF l_starter_declaration NOT IN ('A' , 'B' , 'C') THEN
959 populate_run_msg(p_assactid, 'The Starter Declaration ' ||l_starter_declaration|| ' for assignment ' || l_assignment_number||' is invalid.');
960 fnd_file.put_line (fnd_file.LOG,'The Starter Declaration ' ||l_starter_declaration|| ' for assignment ' || l_assignment_number||' is invalid.');
961 edi_validation_fail := 'Y';
962 END IF;
963 END IF;
964
965 -- One can be present among the following
966 -- l_stay_over_six_months , l_stay_less_six_months , l_living_abroad
967
968 IF l_stay_over_six_months = 'Y' AND (l_stay_less_six_months = 'Y' OR l_living_abroad = 'Y') THEN
969 populate_run_msg(p_assactid, 'Stay in UK over six months for assignment ' || l_assignment_number||' is invalid.');
970 fnd_file.put_line (fnd_file.LOG,'Stay in UK over six months for assignment ' || l_assignment_number||' is invalid.');
971 edi_validation_fail := 'Y';
972 END IF;
973
974 IF l_stay_less_six_months = 'Y' AND (l_stay_over_six_months = 'Y' OR l_living_abroad = 'Y') THEN
975 populate_run_msg(p_assactid, 'Stay in UK less than six months for assignment ' || l_assignment_number||' is invalid.');
976 fnd_file.put_line (fnd_file.LOG,'Stay in UK less than six months for assignment ' || l_assignment_number||' is invalid.');
977 edi_validation_fail := 'Y';
978 END IF;
979
980 IF l_living_abroad = 'Y' AND (l_stay_less_six_months = 'Y' OR l_stay_over_six_months = 'Y') THEN
981 populate_run_msg(p_assactid, 'Working in/out of UK but living abroad for assignment ' || l_assignment_number||' is invalid.');
982 fnd_file.put_line (fnd_file.LOG,'Working in/out of UK but living abroad for assignment ' || l_assignment_number||' is invalid.');
983 edi_validation_fail := 'Y';
984 END IF;
985
986
987 IF l_occ_pension_indicator = 'Y' AND l_annual_occupational_pension IS NULL THEN
988 populate_run_msg(p_assactid, 'The Annual amount of Occupational Pension for assignment ' || l_assignment_number||' is missing, although Occupational Pension Indicator is present.');
989 fnd_file.put_line (fnd_file.LOG,'The Annual amount of Occupational Pension for assignment ' || l_assignment_number||' is missing, although Occupational Pension Indicator is present.');
990 edi_validation_fail := 'Y';
991 END IF;
992
993
994 IF l_annual_occupational_pension IS NOT NULL THEN
995 IF l_annual_occupational_pension < 0 THEN
996 populate_run_msg(p_assactid, 'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' for assignment ' || l_assignment_number||' must be greater than zero.');
997 fnd_file.put_line (fnd_file.LOG,'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' for assignment ' || l_assignment_number||' must be greater than zero.');
998 edi_validation_fail := 'Y';
999 ELSIF l_annual_occupational_pension > 999999999.99 THEN
1000 populate_run_msg(p_assactid, 'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' exceeds 999999999.99 for assignment ' || l_assignment_number||' must be greater than zero.');
1001 fnd_file.put_line (fnd_file.LOG,'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' exceeds 999999999.99 for assignment ' || l_assignment_number||' must be greater than zero.');
1002 edi_validation_fail := 'Y';
1003 END IF;
1004 END IF;
1005
1006 IF ((l_payroll_id <> ' ' and l_payroll_id IS NOT NULL)
1007 and validate_input(l_payroll_id,'PAYROLLID') > 0) THEN
1008 populate_run_msg(p_assactid,' Payroll ID has invalid characters for the assignment ' || l_assignment_number || '.');
1009 fnd_file.put_line (fnd_file.LOG,'Payroll ID has invalid characters for the assignment ' || l_assignment_number || '.');
1010 edi_validation_fail := 'Y';
1011 END IF;
1012
1013 IF ((l_old_payroll_id <> ' ' and l_old_payroll_id IS NOT NULL)
1014 and validate_input(l_old_payroll_id,'PAYROLLID') > 0) THEN
1015 populate_run_msg(p_assactid,' Old Payroll id has invalid characters for the assignment ' || l_assignment_number || '.');
1016 fnd_file.put_line (fnd_file.LOG,'Old Payroll id has invalid characters for the assignment ' || l_assignment_number || '.');
1017 edi_validation_fail := 'Y';
1018 END IF;
1019
1020
1021 -- Pay Frequency is mandatory field
1022 IF l_pay_frequency IS NULL OR l_pay_frequency = ' ' then
1023 populate_run_msg(p_assactid, 'The Pay frequency is missing for assignment ' || l_assignment_number || '.');
1024 fnd_file.put_line (fnd_file.LOG,'The Pay frequency is missing for assignment ' || l_assignment_number || '.');
1025 edi_validation_fail := 'Y';
1026 END IF;
1027
1028
1029 IF (l_pay_frequency IS NOT NULL OR l_pay_frequency <> ' ') AND
1030 l_pay_frequency NOT IN ('W1','W2','W4','M1','M3','M6','MA','IO','IR') THEN
1031 populate_run_msg(p_assactid, 'The Pay frequency for assignment ' || l_assignment_number|| ' is invalid.' );
1032 fnd_file.put_line (fnd_file.LOG,'The Pay frequency for assignment ' || l_assignment_number|| ' is invalid.');
1033 edi_validation_fail := 'Y';
1034 END IF;
1035
1036 -- Atleast one should be present
1037 IF l_payment_week_number IS NULL AND l_payment_month_number IS NULL THEN
1038 populate_run_msg(p_assactid, 'Either Payment Week Number or Payment Month Number must be present for assignment ' || l_assignment_number || '.');
1039 fnd_file.put_line (fnd_file.LOG,'Either Payment Week Number or Payment Month Number must be present for assignment ' || l_assignment_number || '.');
1040 edi_validation_fail := 'Y';
1041 END IF;
1042
1043 IF l_payment_week_number IS NOT NULL AND
1044 ((l_payment_week_number < 1 OR l_payment_week_number > 54) AND l_payment_week_number <> 56) THEN
1045 populate_run_msg(p_assactid, 'The Payment Week Number for assignment ' || l_assignment_number|| ' is invalid.' );
1046 fnd_file.put_line (fnd_file.LOG,'The Payment Week Number for assignment ' || l_assignment_number|| ' is invalid.');
1047 edi_validation_fail := 'Y';
1048 END IF;
1049
1050 IF l_payment_month_number IS NOT NULL AND
1051 (l_payment_month_number < 1 OR l_payment_month_number > 12) THEN
1052 populate_run_msg(p_assactid, 'The Payment Month Number for assignment ' || l_assignment_number|| ' is invalid.' );
1053 fnd_file.put_line (fnd_file.LOG,'The Payment Month Number for assignment ' || l_assignment_number|| ' is invalid.');
1054 edi_validation_fail := 'Y';
1055 END IF;
1056
1057 -- Number of Earnings periods covered by payment is mandatory field
1058 IF l_no_of_earnings IS NULL THEN
1059 populate_run_msg(p_assactid, 'The Number of Earnings Periods covered by Payment is missing for assignment ' || l_assignment_number || '.');
1060 fnd_file.put_line (fnd_file.LOG,'The Number of Earnings Periods covered by Payment is missing for assignment ' || l_assignment_number || '.');
1061 edi_validation_fail := 'Y';
1062 END IF;
1063
1064 IF l_payment_after_leaving = 'Y' AND
1065 l_end_date = fnd_date.canonical_to_date('4712/12/31') THEN
1066 populate_run_msg(p_assactid, 'The Date of Leaving is missing for assignment ' || l_assignment_number|| ',although Payment after leaving indicator is present.');
1067 fnd_file.put_line (fnd_file.LOG,'The Date of Leaving is missing for assignment ' || l_assignment_number|| ',although Payment after leaving indicator is present.');
1068 edi_validation_fail := 'Y';
1069 END IF;
1070
1071 -- No of normal hours worked is mandatory field
1072
1073 IF l_no_of_hours_worked IS NULL THEN
1074 populate_run_msg(p_assactid, 'The Number of normal hours worked is missing for assignment ' || l_assignment_number || '.');
1075 fnd_file.put_line (fnd_file.LOG,'The Number of normal hours worked is missing for assignment ' || l_assignment_number || '.');
1076 edi_validation_fail := 'Y';
1077 END IF;
1078
1079
1080 IF l_week_director_app IS NOT NULL AND
1081 ((l_week_director_app < 1 OR l_week_director_app > 54) AND l_week_director_app <> 56) THEN
1082 populate_run_msg(p_assactid, 'The Week Number of Director appointment for assignment ' || l_assignment_number|| ' is invalid.' );
1083 fnd_file.put_line (fnd_file.LOG,'The Week Number of Director appointment for assignment ' || l_assignment_number|| ' is invalid.');
1084 edi_validation_fail := 'Y';
1085 END IF;
1086
1087 IF l_director_nic_method IS NOT NULL AND
1088 l_director_nic_method NOT IN ('AN' , 'AL') THEN
1089 populate_run_msg(p_assactid, 'The Director NIC calculation method for assignment ' || l_assignment_number|| ' is invalid.' );
1090 fnd_file.put_line (fnd_file.LOG,'The Director NIC calculation method for assignment ' || l_assignment_number|| ' is invalid.');
1091 edi_validation_fail := 'Y';
1092 END IF;
1093
1094 IF l_week_director_app IS NOT NULL AND
1095 l_director_nic_method IS NULL THEN
1096 populate_run_msg(p_assactid, 'The Director NIC calculation method is missing for assignment ' || l_assignment_number||' although Tax week number is present.');
1097 fnd_file.put_line (fnd_file.LOG,'The Director NIC calculation method is mission for assignment ' || l_assignment_number||' although Tax week number is present.');
1098 edi_validation_fail := 'Y';
1099 END IF;
1100
1101 -- Taxable Pay to Date is mandatory field
1102 IF l_taxable_pay_to_date IS NULL THEN
1103 populate_run_msg(p_assactid, 'The Taxable Pay to Date is missing for assignment ' || l_assignment_number || '.');
1104 fnd_file.put_line (fnd_file.LOG,'The Taxable Pay to Date is missing for assignment ' || l_assignment_number || '.');
1105 edi_validation_fail := 'Y';
1106 END IF;
1107
1108 IF l_taxable_pay_to_date IS NOT NULL AND l_taxable_pay_to_date < 0 THEN
1109 populate_run_msg(p_assactid, 'The Taxable Pay to Date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.' );
1110 fnd_file.put_line (fnd_file.LOG,'The Taxable Pay to Date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1111 edi_validation_fail := 'Y';
1112 END IF;
1113
1114 -- Total Tax to Date is mandatory field
1115 IF l_total_tax IS NULL THEN
1116 populate_run_msg(p_assactid, 'The Total Tax to Date is missing for assignment ' || l_assignment_number || '.');
1117 fnd_file.put_line (fnd_file.LOG,'The Total Tax to Date is missing for assignment ' || l_assignment_number || '.');
1118 edi_validation_fail := 'Y';
1119 END IF;
1120
1121 IF l_total_tax IS NOT NULL AND abs(l_total_tax) > 99999999999 THEN
1122 populate_run_msg(p_assactid, 'The Total Tax to Date exceeds 999999999.99 for assignment ' || l_assignment_number|| '.' );
1123 fnd_file.put_line (fnd_file.LOG,'The Total Tax to Date exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1124 edi_validation_fail := 'Y';
1125 END IF;
1126
1127 IF l_student_loan_repayment IS NOT NULL AND l_student_loan_repayment < 0 THEN
1128 populate_run_msg(p_assactid, 'The Total Student loan repayment recovered to date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.' );
1129 fnd_file.put_line (fnd_file.LOG,'The Total Student loan repayment recovered to date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero..');
1130 edi_validation_fail := 'Y';
1131 ELSIF l_student_loan_repayment IS NOT NULL AND l_student_loan_repayment > 9999900 THEN
1132 populate_run_msg(p_assactid, 'The Total Student loan repayment recovered to date exceeds 99999.00 for assignment ' || l_assignment_number|| '.' );
1133 fnd_file.put_line (fnd_file.LOG,'The Total Student loan repayment recovered to date exceeds 99999.00 for assignment ' || l_assignment_number|| '.');
1134 edi_validation_fail := 'Y';
1135 END IF;
1136
1137 ---- > 0
1138 IF l_taxable_pay IS NOT NULL AND abs(l_taxable_pay) > 99999999999 THEN
1139 populate_run_msg(p_assactid, 'Taxable pay '||l_taxable_pay||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1140 fnd_file.put_line (fnd_file.LOG,'Taxable pay '||l_taxable_pay||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1141 edi_validation_fail := 'Y';
1142 END IF;
1143
1144 IF l_non_tax_nic_payment IS NOT NULL AND abs(l_non_tax_nic_payment) > 99999999999 THEN
1145 populate_run_msg(p_assactid, 'Non Tax or NIC Payment '||l_non_tax_nic_payment||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1146 fnd_file.put_line (fnd_file.LOG,'Non Tax or NIC Payment '||l_non_tax_nic_payment||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1147 edi_validation_fail := 'Y';
1148 END IF;
1149
1150 IF l_net_pay_deductions IS NOT NULL AND abs(l_net_pay_deductions) > 99999999999 THEN
1151 populate_run_msg(p_assactid, 'Deductions from Net pay '||l_net_pay_deductions||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1152 fnd_file.put_line (fnd_file.LOG,'Deductions from Net pay '||l_net_pay_deductions||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1153 edi_validation_fail := 'Y';
1154 END IF;
1155
1156 IF l_pay_after_statutory IS NOT NULL AND abs(l_pay_after_statutory) > 99999999999 THEN
1157 populate_run_msg(p_assactid, 'Pay after Statutory deductions '||l_pay_after_statutory||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1158 fnd_file.put_line (fnd_file.LOG,'Pay after Statutory deductions '||l_pay_after_statutory||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1159 edi_validation_fail := 'Y';
1160 END IF;
1161
1162 IF l_benefits_taxed_payroll IS NOT NULL AND abs(l_benefits_taxed_payroll) > 99999999999 THEN
1163 populate_run_msg(p_assactid, 'Benefits Taxed via Payroll '||l_benefits_taxed_payroll||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1164 fnd_file.put_line (fnd_file.LOG,'Benefits Taxed via Payroll '||l_benefits_taxed_payroll||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1165 edi_validation_fail := 'Y';
1166 END IF;
1167
1168 IF l_benefits_class1_nic IS NOT NULL AND abs(l_benefits_class1_nic) > 99999999999 THEN
1169 populate_run_msg(p_assactid, 'Benefits Subject to Class1 NIC '||l_benefits_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1170 fnd_file.put_line (fnd_file.LOG,'Benefits Subject to Class1 NIC '||l_benefits_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1171 edi_validation_fail := 'Y';
1172 END IF;
1173
1174 IF l_benefits_n_class1_nic IS NOT NULL AND abs(l_benefits_n_class1_nic) > 99999999999 THEN
1175 populate_run_msg(p_assactid, 'Benefits not subject to Class1 NIC '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1176 fnd_file.put_line (fnd_file.LOG,'Benefits not subject to Class1 NIC '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1177 edi_validation_fail := 'Y';
1178 END IF;
1179
1180 IF l_employee_pension_contri IS NOT NULL AND abs(l_employee_pension_contri) > 99999999999 THEN
1181 populate_run_msg(p_assactid, 'Employee Pension contributions '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1182 fnd_file.put_line (fnd_file.LOG,'Employee Pension contributions '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1183 edi_validation_fail := 'Y';
1184 END IF;
1185
1186 IF l_items_class1_nic IS NOT NULL AND abs(l_items_class1_nic) > 99999999999 THEN
1187 populate_run_msg(p_assactid, 'Items subject to Class1 NIC '||l_items_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1188 fnd_file.put_line (fnd_file.LOG,'Items subject to Class1 NIC '||l_items_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1189 edi_validation_fail := 'Y';
1190 END IF;
1191
1192 IF l_emp_contri_not_paid IS NOT NULL AND abs(l_emp_contri_not_paid) > 99999999999 THEN
1193 populate_run_msg(p_assactid, 'Employee contributions not paid '||l_emp_contri_not_paid||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1194 fnd_file.put_line (fnd_file.LOG,'Employee contributions not paid '||l_emp_contri_not_paid||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1195 edi_validation_fail := 'Y';
1196 END IF;
1197
1198 IF l_student_loan_repay IS NOT NULL AND abs(l_student_loan_repay) > 9999900 THEN
1199 populate_run_msg(p_assactid, 'Student Loan Repayment recovered '||l_student_loan_repay||' exceeds 99999.00 for assignment ' || l_assignment_number|| '.');
1200 fnd_file.put_line (fnd_file.LOG,'Student Loan Repayment recovered '||l_student_loan_repay||' exceeds 99999.00 for assignment ' || l_assignment_number|| '.');
1201 edi_validation_fail := 'Y';
1202 END IF;
1203
1204 -- Tax Deducted or Refunded is mandatory field
1205 IF l_tax_deducted_refunded IS NULL THEN
1206 populate_run_msg(p_assactid, 'Tax deducted or refunded is missing for assignment ' || l_assignment_number || '.');
1207 fnd_file.put_line (fnd_file.LOG,'Tax deducted or refunded is missing for assignment ' || l_assignment_number || '.');
1208 edi_validation_fail := 'Y';
1209 END IF;
1210
1211 IF l_tax_deducted_refunded IS NOT NULL AND abs(l_tax_deducted_refunded) > 999999999999 THEN
1212 populate_run_msg(p_assactid, 'Tax Deducted or Refunded '||l_student_loan_repay||' exceeds 9999999999.99 for assignment ' || l_assignment_number|| '.');
1213 fnd_file.put_line (fnd_file.LOG,'Tax Deducted or Refunded '||l_student_loan_repay||' exceeds 9999999999.99 for assignment ' || l_assignment_number|| '.');
1214 edi_validation_fail := 'Y';
1215 END IF;
1216
1217 IF l_ssp IS NOT NULL AND l_ssp < 0 THEN
1218 populate_run_msg(p_assactid, 'Statutory Sick Pay '||l_ssp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1219 fnd_file.put_line (fnd_file.LOG,'Statutory Sick Pay '||l_ssp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1220 edi_validation_fail := 'Y';
1221 ELSIF l_ssp IS NOT NULL AND l_ssp > 999999 THEN
1222 populate_run_msg(p_assactid, 'Statutory Sick Pay '||l_ssp||' exceeds 9999.99 for assignment ' || l_assignment_number|| '.');
1223 fnd_file.put_line (fnd_file.LOG,'Statutory Sick Pay '||l_ssp||' exceeds 9999.99 for assignment ' || l_assignment_number|| '.');
1224 edi_validation_fail := 'Y';
1225 END IF;
1226
1227 IF l_smp IS NOT NULL AND l_smp < 0 THEN
1228 populate_run_msg(p_assactid, 'Statutory Maternity Pay '||l_smp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1229 fnd_file.put_line (fnd_file.LOG,'Statutory Maternity Pay '||l_smp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1230 edi_validation_fail := 'Y';
1231 ELSIF l_smp IS NOT NULL AND l_smp > 99999999 THEN
1232 populate_run_msg(p_assactid, 'Statutory Maternity Pay '||l_smp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1233 fnd_file.put_line (fnd_file.LOG,'Statutory Maternity Pay '||l_smp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1234 edi_validation_fail := 'Y';
1235 END IF;
1236
1237 IF l_smp > 0 AND l_gender <> 'F' THEN
1238 populate_run_msg(p_assactid, 'Gender must be female for assignment ' || l_assignment_number|| ' to claim Statutory Maternity Pay.');
1239 fnd_file.put_line (fnd_file.LOG,'Gender must be female for assignment ' || l_assignment_number|| ' to claim Statutory Maternity Pay.');
1240 edi_validation_fail := 'Y';
1241 END IF;
1242
1243 IF l_ospp IS NOT NULL AND l_ospp < 0 THEN
1244 populate_run_msg(p_assactid, 'Ordinary Statutory Paternity Pay '||l_ospp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1245 fnd_file.put_line (fnd_file.LOG,'Ordinary Statutory Paternity Pay '||l_ospp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1246 edi_validation_fail := 'Y';
1247 ELSIF l_ospp IS NOT NULL AND l_ospp > 99999999 THEN
1248 populate_run_msg(p_assactid, 'Ordinary Statutory Paternity Pay '||l_ospp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1249 fnd_file.put_line (fnd_file.LOG,'Ordinary Statutory Paternity Pay '||l_ospp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1250 edi_validation_fail := 'Y';
1251 END IF;
1252
1253 IF l_sap IS NOT NULL AND l_sap < 0 THEN
1254 populate_run_msg(p_assactid, 'Statutory Adoption Pay '||l_sap||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1255 fnd_file.put_line (fnd_file.LOG,'Statutory Adoption Pay '||l_sap||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1256 edi_validation_fail := 'Y';
1257 ELSIF l_sap IS NOT NULL AND l_sap > 99999999 THEN
1258 populate_run_msg(p_assactid, 'Statutory Adoption Pay '||l_sap||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1259 fnd_file.put_line (fnd_file.LOG,'Statutory Adoption Pay '||l_sap||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1260 edi_validation_fail := 'Y';
1261 END IF;
1262
1263 IF l_aspp IS NOT NULL AND l_aspp < 0 THEN
1264 populate_run_msg(p_assactid, 'Additional Statutory Paternity Pay '||l_aspp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1265 fnd_file.put_line (fnd_file.LOG,'Additional Statutory Paternity Pay '||l_aspp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1266 edi_validation_fail := 'Y';
1267 ELSIF l_aspp IS NOT NULL AND l_aspp > 99999999 THEN
1268 populate_run_msg(p_assactid, 'Additional Statutory Paternity Pay '||l_aspp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1269 fnd_file.put_line (fnd_file.LOG,'Additional Statutory Paternity Pay '||l_aspp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1270 edi_validation_fail := 'Y';
1271 END IF;
1272
1273
1274 -- Tax Code
1275 IF (l_tax_code = ' ' OR l_tax_code IS NULL) then
1276 populate_run_msg(p_assactid, 'The Tax Code is missing for assignment ' || l_assignment_number || '.');
1277 fnd_file.put_line (fnd_file.LOG,'The Tax Code is missing for assignment ' || l_assignment_number || '.');
1278 edi_validation_fail := 'Y';
1279 END IF;
1280
1281
1282 OPEN get_effective_date;
1283 FETCH get_effective_date INTO l_eff_date;
1284 CLOSE get_effective_date;
1285
1286 l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code,l_eff_date); -- Tax code validation
1287
1288 IF (l_tax_code <> ' ' and l_tax_code IS NOT NULL)
1289 AND (l_msg_value <> ' ') THEN
1290 populate_run_msg(p_assactid,'The Tax Code ' || l_tax_code || ' for assignment ' || l_assignment_number||' is invalid.');
1291 fnd_file.put_line (fnd_file.LOG,'The Tax Code ' || l_tax_code || ' for assignment ' || l_assignment_number||' is invalid.');
1292 edi_validation_fail := 'Y';
1293 END IF;
1294
1295 -- Tax code ends
1296
1297 IF l_bacs_hash_code IS NOT NULL AND
1298 regexp_instr(l_bacs_hash_code,'^([[:alnum:]]){64}$') = 0 THEN
1299 populate_run_msg(p_assactid, 'Bacs Hash Code for assignment ' || l_assignment_number|| ' is invalid.' );
1300 fnd_file.put_line (fnd_file.LOG,'Bacs Hash Code for assignment ' || l_assignment_number|| ' is invalid.');
1301 edi_validation_fail := 'Y';
1302 END IF;
1303
1304
1305 hr_utility.set_location('Leaving '||l_proc,1);
1306
1307 END assignment_validations;
1308 --
1309 /*--------------------------------------------------------------------------
1310 Name : ASSIGNMENT_VALIDATIONS_AGGR
1311 Purpose : This procedure validates all Assignment Details for Aggregated employees
1312 Arguments : Assignment Action ID , Effective Date , Assignment Record
1313 Notes :
1314 --------------------------------------------------------------------------*/
1315
1316 PROCEDURE assignment_validations_aggr(p_assactid IN NUMBER,
1317 p_effective_date IN DATE,
1318 p_tab_rec_data IN pay_gb_rti_fps_archive.action_info_table,
1319 p_tab_rec_det1 IN pay_gb_rti_fps_archive.act_info_rec,
1320 p_tab_rec_det2 IN pay_gb_rti_fps_archive.act_info_rec,
1321 edi_validation_fail out nocopy varchar2) IS
1322
1323 -- To fetch Effective date
1324 CURSOR get_effective_date IS
1325 SELECT ppa.effective_date
1326 FROM pay_payroll_actions ppa, pay_assignment_actions paa
1327 WHERE ppa.payroll_action_id = paa.payroll_action_id
1328 AND paa.assignment_action_id = p_assactid;
1329
1330 --Declarations
1331 l_proc CONSTANT VARCHAR2(70):= g_package||'assignment_validations_aggr';
1332
1333 -- Assignment Number
1334 l_assignment_number per_assignments_f.assignment_number%TYPE;
1335
1336 -- Assignment Fields
1337 l_start_date date;
1338 l_end_date date;
1339 l_payment_date date;
1340 l_starter_declaration varchar2(100);
1341 l_stay_over_six_months varchar2(100);
1342 l_stay_less_six_months varchar2(100);
1343 l_living_abroad varchar2(100);
1344 l_occ_pension_indicator varchar2(100);
1345 l_annual_occupational_pension number;
1346 l_state_pension_indicator varchar2(100);
1347 l_annual_state_pension number;
1348 l_payroll_id varchar2(50);
1349 l_old_payroll_id varchar2(50);
1350 l_pay_frequency varchar2(200);
1351 l_payment_week_number number;
1352 l_payment_month_number number;
1353 l_no_of_earnings number;
1354 l_payment_after_leaving varchar(100);
1355 l_no_of_hours_worked varchar2(10);
1356 l_director_nic_method varchar(100);
1357 l_week_director_app number;
1358 l_taxable_pay_to_date number;
1359 l_total_tax number;
1360 l_student_loan_repayment number;
1361
1362 l_taxable_pay number;
1363 l_non_tax_nic_payment number;
1364 l_net_pay_deductions number;
1365 l_pay_after_statutory number;
1366 l_benefits_taxed_payroll number;
1367 l_benefits_class1_nic number;
1368 l_benefits_n_class1_nic number;
1369 l_employee_pension_contri number;
1370 l_items_class1_nic number;
1371 l_emp_contri_not_paid number;
1372 l_student_loan_repay number;
1373 l_tax_deducted_refunded number;
1374 l_ssp number;
1375 l_smp number;
1376 l_ospp number;
1377 l_sap number;
1378 l_aspp number;
1379
1380 l_tax_code varchar2(15);
1381 l_bacs_hash_code varchar2(64);
1382
1383 -- Person Details needed to validations
1384 l_gender varchar2(100);
1385
1386 -- Local Declarations
1387 l_msg_value varchar2(100);
1388 l_eff_date date;
1389
1390
1391 BEGIN
1392 hr_utility.set_location('Entering: '||l_proc,1);
1393
1394 edi_validation_fail := 'N'; -- Initialized validation fail to N
1395
1396 -- Assigning values to the local variables
1397 l_assignment_number := p_tab_rec_det1.act_info13;
1398
1399 -- Assignment Fields
1400 l_start_date := (to_date(substr(p_tab_rec_det1.act_info1,1,10),'YYYY-MM-DD'));
1401 l_end_date := (to_date(substr(p_tab_rec_det1.act_info2,1,10),'YYYY-MM-DD'));
1402 l_payment_date := (to_date(substr(p_tab_rec_det1.act_info3,1,10),'YYYY-MM-DD'));
1403 l_starter_declaration := p_tab_rec_det1.act_info4;
1404 l_stay_over_six_months := p_tab_rec_det1.act_info6;
1405 l_stay_less_six_months := p_tab_rec_det1.act_info7;
1406 l_living_abroad := p_tab_rec_det1.act_info8;
1407 l_occ_pension_indicator := p_tab_rec_det1.act_info12;
1408 l_payroll_id := p_tab_rec_det1.act_info13;
1409 l_old_payroll_id := p_tab_rec_det1.act_info15;
1410 l_pay_frequency := p_tab_rec_det1.act_info17;
1411 l_payment_week_number := p_tab_rec_det1.act_info18;
1412 l_payment_month_number := p_tab_rec_det1.act_info19;
1413 l_no_of_earnings := p_tab_rec_det1.act_info20;
1414 l_payment_after_leaving := p_tab_rec_det1.act_info22;
1415 l_no_of_hours_worked := p_tab_rec_det1.act_info23;
1416 l_director_nic_method := p_tab_rec_det1.act_info24;
1417 l_week_director_app := p_tab_rec_det1.act_info25;
1418 l_annual_occupational_pension := p_tab_rec_det1.act_info26;
1419
1420 l_taxable_pay_to_date := p_tab_rec_det2.act_info1;
1421 l_total_tax := p_tab_rec_det2.act_info2;
1422 l_student_loan_repayment := p_tab_rec_det2.act_info3;
1423 l_taxable_pay := p_tab_rec_det2.act_info6;
1424 l_non_tax_nic_payment := p_tab_rec_det2.act_info7;
1425 l_net_pay_deductions := p_tab_rec_det2.act_info8;
1426 l_pay_after_statutory := p_tab_rec_det2.act_info9;
1427 l_benefits_taxed_payroll := p_tab_rec_det2.act_info10;
1428 l_benefits_class1_nic := p_tab_rec_det2.act_info11;
1429 l_benefits_n_class1_nic := p_tab_rec_det2.act_info12;
1430 l_employee_pension_contri := p_tab_rec_det2.act_info13;
1431 l_items_class1_nic := p_tab_rec_det2.act_info14;
1432 l_emp_contri_not_paid := p_tab_rec_det2.act_info15;
1433 l_student_loan_repay := p_tab_rec_det2.act_info16;
1434
1435 l_tax_deducted_refunded := p_tab_rec_det2.act_info17;
1436
1437 l_ssp := p_tab_rec_det2.act_info18;
1438 l_smp := p_tab_rec_det2.act_info19;
1439 l_ospp := p_tab_rec_det2.act_info20;
1440 l_sap := p_tab_rec_det2.act_info21;
1441 l_aspp := p_tab_rec_det2.act_info22;
1442
1443 l_tax_code := p_tab_rec_det2.act_info25;
1444 l_bacs_hash_code := p_tab_rec_det1.act_info28;
1445
1446 l_gender := p_tab_rec_data(0).act_info9;
1447
1448
1449 -- Assignment Validations
1450
1451
1452 IF l_start_date IS NOT NULL
1453 and date_validate(p_assactid,'UK_EMPL_DATE',l_start_date) = 0 THEN
1454 populate_run_msg(p_assactid,' The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1455 fnd_file.put_line (fnd_file.LOG,'The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1456 edi_validation_fail := 'Y';
1457 END IF;
1458
1459 IF l_end_date IS NOT NULL
1460 and date_validate(p_assactid,'UK_EMPL_DATE_LEAVING',l_end_date) = 0 THEN
1461 populate_run_msg(p_assactid,' The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1462 fnd_file.put_line (fnd_file.LOG,'The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
1463 edi_validation_fail := 'Y';
1464 END IF;
1465
1466 -- Payment Date is mandatory field
1467 IF l_payment_date IS NULL then
1468 populate_run_msg(p_assactid, 'The Payment Date is missing for assignment ' || l_assignment_number || '.');
1469 fnd_file.put_line (fnd_file.LOG,'The Payment Date is missing for assignment ' || l_assignment_number || '.');
1470 edi_validation_fail := 'Y';
1471 END IF;
1472
1473
1474 IF l_payment_date IS NOT NULL THEN
1475 IF l_payment_date < fnd_date.canonical_to_date('2012/04/06 00:00:00') THEN
1476 populate_run_msg(p_assactid, 'The Payment Date ' ||l_payment_date|| ' for assignment ' || l_assignment_number||' is invalid.');
1477 fnd_file.put_line (fnd_file.LOG,'The Payment Date ' ||l_payment_date|| ' for assignment ' || l_assignment_number||' is invalid.');
1478 edi_validation_fail := 'Y';
1479 END IF;
1480 END IF;
1481
1482 -- Starter Declaration is mandatory field
1483 IF l_start_date IS NOT NULL and (l_starter_declaration = ' ' OR l_starter_declaration IS NULL )then
1484 populate_run_msg(p_assactid, 'The Starter Declaration is missing for assignment ' || l_assignment_number || '.');
1485 fnd_file.put_line (fnd_file.LOG,'The Starter Declaration is missing for assignment ' || l_assignment_number || '.');
1486 edi_validation_fail := 'Y';
1487 END IF;
1488
1489 IF l_starter_declaration IS NOT NULL THEN
1490 IF l_starter_declaration NOT IN ('A' , 'B' , 'C') THEN
1491 populate_run_msg(p_assactid, 'The Starter Declaration ' ||l_starter_declaration|| ' for assignment ' || l_assignment_number||' is invalid.');
1492 fnd_file.put_line (fnd_file.LOG,'The Starter Declaration ' ||l_starter_declaration|| ' for assignment ' || l_assignment_number||' is invalid.');
1493 edi_validation_fail := 'Y';
1494 END IF;
1495 END IF;
1496
1497 -- One can be present among the following
1498 -- l_stay_over_six_months , l_stay_less_six_months , l_living_abroad
1499
1500 IF l_stay_over_six_months = 'Y' AND (l_stay_less_six_months = 'Y' OR l_living_abroad = 'Y') THEN
1501 populate_run_msg(p_assactid, 'Stay in UK over six months for assignment ' || l_assignment_number||' is invalid.');
1502 fnd_file.put_line (fnd_file.LOG,'Stay in UK over six months for assignment ' || l_assignment_number||' is invalid.');
1503 edi_validation_fail := 'Y';
1504 END IF;
1505
1506 IF l_stay_less_six_months = 'Y' AND (l_stay_over_six_months = 'Y' OR l_living_abroad = 'Y') THEN
1507 populate_run_msg(p_assactid, 'Stay in UK less than six months for assignment ' || l_assignment_number||' is invalid.');
1508 fnd_file.put_line (fnd_file.LOG,'Stay in UK less than six months for assignment ' || l_assignment_number||' is invalid.');
1509 edi_validation_fail := 'Y';
1510 END IF;
1511
1512 IF l_living_abroad = 'Y' AND (l_stay_less_six_months = 'Y' OR l_stay_over_six_months = 'Y') THEN
1513 populate_run_msg(p_assactid, 'Working in/out of UK but living abroad for assignment ' || l_assignment_number||' is invalid.');
1514 fnd_file.put_line (fnd_file.LOG,'Working in/out of UK but living abroad for assignment ' || l_assignment_number||' is invalid.');
1515 edi_validation_fail := 'Y';
1516 END IF;
1517
1518
1519 IF l_occ_pension_indicator = 'Y' AND l_annual_occupational_pension IS NULL THEN
1520 populate_run_msg(p_assactid, 'The Annual amount of Occupational Pension for assignment ' || l_assignment_number||' is missing, although Occupational Pension Indicator is present.');
1521 fnd_file.put_line (fnd_file.LOG,'The Annual amount of Occupational Pension for assignment ' || l_assignment_number||' is missing, although Occupational Pension Indicator is present.');
1522 edi_validation_fail := 'Y';
1523 END IF;
1524
1525
1526 IF l_annual_occupational_pension IS NOT NULL THEN
1527 IF l_annual_occupational_pension < 0 THEN
1528 populate_run_msg(p_assactid, 'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' for assignment ' || l_assignment_number||' must be greater than zero.');
1529 fnd_file.put_line (fnd_file.LOG,'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' for assignment ' || l_assignment_number||' must be greater than zero.');
1530 edi_validation_fail := 'Y';
1531 ELSIF l_annual_occupational_pension > 999999999.99 THEN
1532 populate_run_msg(p_assactid, 'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' exceeds 999999999.99 for assignment ' || l_assignment_number||' must be greater than zero.');
1533 fnd_file.put_line (fnd_file.LOG,'The Annual amount of Occupational Pension ' ||l_annual_occupational_pension|| ' exceeds 999999999.99 for assignment ' || l_assignment_number||' must be greater than zero.');
1534 edi_validation_fail := 'Y';
1535 END IF;
1536 END IF;
1537
1538 IF ((l_payroll_id <> ' ' and l_payroll_id IS NOT NULL)
1539 and validate_input(l_payroll_id,'PAYROLLID') > 0) THEN
1540 populate_run_msg(p_assactid,' Payroll ID has invalid characters for the assignment ' || l_assignment_number || '.');
1541 fnd_file.put_line (fnd_file.LOG,'Payroll ID has invalid characters for the assignment ' || l_assignment_number || '.');
1542 edi_validation_fail := 'Y';
1543 END IF;
1544
1545 IF ((l_old_payroll_id <> ' ' and l_old_payroll_id IS NOT NULL)
1546 and validate_input(l_old_payroll_id,'PAYROLLID') > 0) THEN
1547 populate_run_msg(p_assactid,' Old Payroll id has invalid characters for the assignment ' || l_assignment_number || '.');
1548 fnd_file.put_line (fnd_file.LOG,'Old Payroll id has invalid characters for the assignment ' || l_assignment_number || '.');
1549 edi_validation_fail := 'Y';
1550 END IF;
1551
1552
1553 -- Pay Frequency is mandatory field
1554 IF l_pay_frequency IS NULL OR l_pay_frequency = ' ' then
1555 populate_run_msg(p_assactid, 'The Pay frequency is missing for assignment ' || l_assignment_number || '.');
1556 fnd_file.put_line (fnd_file.LOG,'The Pay frequency is missing for assignment ' || l_assignment_number || '.');
1557 edi_validation_fail := 'Y';
1558 END IF;
1559
1560
1561 IF (l_pay_frequency IS NOT NULL OR l_pay_frequency <> ' ') AND
1562 l_pay_frequency NOT IN ('W1','W2','W4','M1','M3','M6','MA','IO','IR') THEN
1563 populate_run_msg(p_assactid, 'The Pay frequency for assignment ' || l_assignment_number|| ' is invalid.' );
1564 fnd_file.put_line (fnd_file.LOG,'The Pay frequency for assignment ' || l_assignment_number|| ' is invalid.');
1565 edi_validation_fail := 'Y';
1566 END IF;
1567
1568 -- Atleast one should be present
1569 IF l_payment_week_number IS NULL AND l_payment_month_number IS NULL THEN
1570 populate_run_msg(p_assactid, 'Either Payment Week Number or Payment Month Number must be present for assignment ' || l_assignment_number || '.');
1571 fnd_file.put_line (fnd_file.LOG,'Either Payment Week Number or Payment Month Number must be present for assignment ' || l_assignment_number || '.');
1572 edi_validation_fail := 'Y';
1573 END IF;
1574
1575 IF l_payment_week_number IS NOT NULL AND
1576 ((l_payment_week_number < 1 OR l_payment_week_number > 54) AND l_payment_week_number <> 56) THEN
1577 populate_run_msg(p_assactid, 'The Payment Week Number for assignment ' || l_assignment_number|| ' is invalid.' );
1578 fnd_file.put_line (fnd_file.LOG,'The Payment Week Number for assignment ' || l_assignment_number|| ' is invalid.');
1579 edi_validation_fail := 'Y';
1580 END IF;
1581
1582 IF l_payment_month_number IS NOT NULL AND
1583 (l_payment_month_number < 1 OR l_payment_month_number > 12) THEN
1584 populate_run_msg(p_assactid, 'The Payment Month Number for assignment ' || l_assignment_number|| ' is invalid.' );
1585 fnd_file.put_line (fnd_file.LOG,'The Payment Month Number for assignment ' || l_assignment_number|| ' is invalid.');
1586 edi_validation_fail := 'Y';
1587 END IF;
1588
1589 -- Number of Earnings periods covered by payment is mandatory field
1590 IF l_no_of_earnings IS NULL THEN
1591 populate_run_msg(p_assactid, 'The Number of Earnings Periods covered by Payment is missing for assignment ' || l_assignment_number || '.');
1592 fnd_file.put_line (fnd_file.LOG,'The Number of Earnings Periods covered by Payment is missing for assignment ' || l_assignment_number || '.');
1593 edi_validation_fail := 'Y';
1594 END IF;
1595
1596 IF l_payment_after_leaving = 'Y' AND
1597 l_end_date = fnd_date.canonical_to_date('4712/12/31') THEN
1598 populate_run_msg(p_assactid, 'The Date of Leaving is missing for assignment ' || l_assignment_number|| ',although Payment after leaving indicator is present.');
1599 fnd_file.put_line (fnd_file.LOG,'The Date of Leaving is missing for assignment ' || l_assignment_number|| ',although Payment after leaving indicator is present.');
1600 edi_validation_fail := 'Y';
1601 END IF;
1602
1603 -- No of normal hours worked is mandatory field
1604
1605 IF l_no_of_hours_worked IS NULL THEN
1606 populate_run_msg(p_assactid, 'The Number of normal hours worked is missing for assignment ' || l_assignment_number || '.');
1607 fnd_file.put_line (fnd_file.LOG,'The Number of normal hours worked is missing for assignment ' || l_assignment_number || '.');
1608 edi_validation_fail := 'Y';
1609 END IF;
1610
1611
1612 IF l_week_director_app IS NOT NULL AND
1613 ((l_week_director_app < 1 OR l_week_director_app > 54) AND l_week_director_app <> 56) THEN
1614 populate_run_msg(p_assactid, 'The Week Number of Director appointment for assignment ' || l_assignment_number|| ' is invalid.' );
1615 fnd_file.put_line (fnd_file.LOG,'The Week Number of Director appointment for assignment ' || l_assignment_number|| ' is invalid.');
1616 edi_validation_fail := 'Y';
1617 END IF;
1618
1619 IF l_director_nic_method IS NOT NULL AND
1620 l_director_nic_method NOT IN ('AN' , 'AL') THEN
1621 populate_run_msg(p_assactid, 'The Director NIC calculation method for assignment ' || l_assignment_number|| ' is invalid.' );
1622 fnd_file.put_line (fnd_file.LOG,'The Director NIC calculation method for assignment ' || l_assignment_number|| ' is invalid.');
1623 edi_validation_fail := 'Y';
1624 END IF;
1625
1626 IF l_week_director_app IS NOT NULL AND
1627 l_director_nic_method IS NULL THEN
1628 populate_run_msg(p_assactid, 'The Director NIC calculation method is missing for assignment ' || l_assignment_number||' although Tax week number is present.');
1629 fnd_file.put_line (fnd_file.LOG,'The Director NIC calculation method is mission for assignment ' || l_assignment_number||' although Tax week number is present.');
1630 edi_validation_fail := 'Y';
1631 END IF;
1632
1633 -- Taxable Pay to Date is mandatory field
1634 IF l_taxable_pay_to_date IS NULL THEN
1635 populate_run_msg(p_assactid, 'The Taxable Pay to Date is missing for assignment ' || l_assignment_number || '.');
1636 fnd_file.put_line (fnd_file.LOG,'The Taxable Pay to Date is missing for assignment ' || l_assignment_number || '.');
1637 edi_validation_fail := 'Y';
1638 END IF;
1639
1640 IF l_taxable_pay_to_date IS NOT NULL AND l_taxable_pay_to_date < 0 THEN
1641 populate_run_msg(p_assactid, 'The Taxable Pay to Date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.' );
1642 fnd_file.put_line (fnd_file.LOG,'The Taxable Pay to Date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1643 edi_validation_fail := 'Y';
1644 END IF;
1645
1646 -- Total Tax to Date is mandatory field
1647 IF l_total_tax IS NULL THEN
1648 populate_run_msg(p_assactid, 'The Total Tax to Date is missing for assignment ' || l_assignment_number || '.');
1649 fnd_file.put_line (fnd_file.LOG,'The Total Tax to Date is missing for assignment ' || l_assignment_number || '.');
1650 edi_validation_fail := 'Y';
1651 END IF;
1652
1653 IF l_total_tax IS NOT NULL AND abs(l_total_tax) > 99999999999 THEN
1654 populate_run_msg(p_assactid, 'The Total Tax to Date exceeds 999999999.99 for assignment ' || l_assignment_number|| '.' );
1655 fnd_file.put_line (fnd_file.LOG,'The Total Tax to Date exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1656 edi_validation_fail := 'Y';
1657 END IF;
1658
1659 IF l_student_loan_repayment IS NOT NULL AND l_student_loan_repayment < 0 THEN
1660 populate_run_msg(p_assactid, 'The Total Student loan repayment recovered to date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.' );
1661 fnd_file.put_line (fnd_file.LOG,'The Total Student loan repayment recovered to date for assignment ' || l_assignment_number|| ' must be greater than or equal to zero..');
1662 edi_validation_fail := 'Y';
1663 ELSIF l_student_loan_repayment IS NOT NULL AND l_student_loan_repayment > 9999900 THEN
1664 populate_run_msg(p_assactid, 'The Total Student loan repayment recovered to date exceeds 99999.00 for assignment ' || l_assignment_number|| '.' );
1665 fnd_file.put_line (fnd_file.LOG,'The Total Student loan repayment recovered to date exceeds 99999.00 for assignment ' || l_assignment_number|| '.');
1666 edi_validation_fail := 'Y';
1667 END IF;
1668
1669 ---- > 0
1670 IF l_taxable_pay IS NOT NULL AND abs(l_taxable_pay) > 99999999999 THEN
1671 populate_run_msg(p_assactid, 'Taxable pay '||l_taxable_pay||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1672 fnd_file.put_line (fnd_file.LOG,'Taxable pay '||l_taxable_pay||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1673 edi_validation_fail := 'Y';
1674 END IF;
1675
1676 IF l_non_tax_nic_payment IS NOT NULL AND abs(l_non_tax_nic_payment) > 99999999999 THEN
1677 populate_run_msg(p_assactid, 'Non Tax or NIC Payment '||l_non_tax_nic_payment||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1678 fnd_file.put_line (fnd_file.LOG,'Non Tax or NIC Payment '||l_non_tax_nic_payment||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1679 edi_validation_fail := 'Y';
1680 END IF;
1681
1682 IF l_net_pay_deductions IS NOT NULL AND abs(l_net_pay_deductions) > 99999999999 THEN
1683 populate_run_msg(p_assactid, 'Deductions from Net pay '||l_net_pay_deductions||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1684 fnd_file.put_line (fnd_file.LOG,'Deductions from Net pay '||l_net_pay_deductions||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1685 edi_validation_fail := 'Y';
1686 END IF;
1687
1688 IF l_pay_after_statutory IS NOT NULL AND abs(l_pay_after_statutory) > 99999999999 THEN
1689 populate_run_msg(p_assactid, 'Pay after Statutory deductions '||l_pay_after_statutory||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1690 fnd_file.put_line (fnd_file.LOG,'Pay after Statutory deductions '||l_pay_after_statutory||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1691 edi_validation_fail := 'Y';
1692 END IF;
1693
1694 IF l_benefits_taxed_payroll IS NOT NULL AND abs(l_benefits_taxed_payroll) > 99999999999 THEN
1695 populate_run_msg(p_assactid, 'Benefits Taxed via Payroll '||l_benefits_taxed_payroll||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1696 fnd_file.put_line (fnd_file.LOG,'Benefits Taxed via Payroll '||l_benefits_taxed_payroll||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1697 edi_validation_fail := 'Y';
1698 END IF;
1699
1700 IF l_benefits_class1_nic IS NOT NULL AND abs(l_benefits_class1_nic) > 99999999999 THEN
1701 populate_run_msg(p_assactid, 'Benefits Subject to Class1 NIC '||l_benefits_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1702 fnd_file.put_line (fnd_file.LOG,'Benefits Subject to Class1 NIC '||l_benefits_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1703 edi_validation_fail := 'Y';
1704 END IF;
1705
1706 IF l_benefits_n_class1_nic IS NOT NULL AND abs(l_benefits_n_class1_nic) > 99999999999 THEN
1707 populate_run_msg(p_assactid, 'Benefits not subject to Class1 NIC '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1708 fnd_file.put_line (fnd_file.LOG,'Benefits not subject to Class1 NIC '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1709 edi_validation_fail := 'Y';
1710 END IF;
1711
1712 IF l_employee_pension_contri IS NOT NULL AND abs(l_employee_pension_contri) > 99999999999 THEN
1713 populate_run_msg(p_assactid, 'Employee Pension contributions '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1714 fnd_file.put_line (fnd_file.LOG,'Employee Pension contributions '||l_benefits_n_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1715 edi_validation_fail := 'Y';
1716 END IF;
1717
1718 IF l_items_class1_nic IS NOT NULL AND abs(l_items_class1_nic) > 99999999999 THEN
1719 populate_run_msg(p_assactid, 'Items subject to Class1 NIC '||l_items_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1720 fnd_file.put_line (fnd_file.LOG,'Items subject to Class1 NIC '||l_items_class1_nic||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1721 edi_validation_fail := 'Y';
1722 END IF;
1723
1724 IF l_emp_contri_not_paid IS NOT NULL AND abs(l_emp_contri_not_paid) > 99999999999 THEN
1725 populate_run_msg(p_assactid, 'Employee contributions not paid '||l_emp_contri_not_paid||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1726 fnd_file.put_line (fnd_file.LOG,'Employee contributions not paid '||l_emp_contri_not_paid||' exceeds 999999999.99 for assignment ' || l_assignment_number|| '.');
1727 edi_validation_fail := 'Y';
1728 END IF;
1729
1730 IF l_student_loan_repay IS NOT NULL AND abs(l_student_loan_repay) > 9999900 THEN
1731 populate_run_msg(p_assactid, 'Student Loan Repayment recovered '||l_student_loan_repay||' exceeds 99999.00 for assignment ' || l_assignment_number|| '.');
1732 fnd_file.put_line (fnd_file.LOG,'Student Loan Repayment recovered '||l_student_loan_repay||' exceeds 99999.00 for assignment ' || l_assignment_number|| '.');
1733 edi_validation_fail := 'Y';
1734 END IF;
1735
1736 -- Tax Deducted or Refunded is mandatory field
1737 --Commented the below for testing
1738
1739 IF l_tax_deducted_refunded IS NULL THEN
1740 populate_run_msg(p_assactid, 'Tax deducted or refunded is missing for assignment ' || l_assignment_number || '.');
1741 fnd_file.put_line (fnd_file.LOG,'Tax deducted or refunded is missing for assignment ' || l_assignment_number || '.');
1742 edi_validation_fail := 'Y';
1743 END IF;
1744
1745
1746 IF l_tax_deducted_refunded IS NOT NULL AND abs(l_tax_deducted_refunded) > 999999999999 THEN
1747 populate_run_msg(p_assactid, 'Tax Deducted or Refunded '||l_student_loan_repay||' exceeds 9999999999.99 for assignment ' || l_assignment_number|| '.');
1748 fnd_file.put_line (fnd_file.LOG,'Tax Deducted or Refunded '||l_student_loan_repay||' exceeds 9999999999.99 for assignment ' || l_assignment_number|| '.');
1749 edi_validation_fail := 'Y';
1750 END IF;
1751
1752 IF l_ssp IS NOT NULL AND l_ssp < 0 THEN
1753 populate_run_msg(p_assactid, 'Statutory Sick Pay '||l_ssp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1754 fnd_file.put_line (fnd_file.LOG,'Statutory Sick Pay '||l_ssp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1755 edi_validation_fail := 'Y';
1756 ELSIF l_ssp IS NOT NULL AND l_ssp > 999999 THEN
1757 populate_run_msg(p_assactid, 'Statutory Sick Pay '||l_ssp||' exceeds 9999.99 for assignment ' || l_assignment_number|| '.');
1758 fnd_file.put_line (fnd_file.LOG,'Statutory Sick Pay '||l_ssp||' exceeds 9999.99 for assignment ' || l_assignment_number|| '.');
1759 edi_validation_fail := 'Y';
1760 END IF;
1761
1762 IF l_smp IS NOT NULL AND l_smp < 0 THEN
1763 populate_run_msg(p_assactid, 'Statutory Maternity Pay '||l_smp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1764 fnd_file.put_line (fnd_file.LOG,'Statutory Maternity Pay '||l_smp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1765 edi_validation_fail := 'Y';
1766 ELSIF l_smp IS NOT NULL AND l_smp > 99999999 THEN
1767 populate_run_msg(p_assactid, 'Statutory Maternity Pay '||l_smp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1768 fnd_file.put_line (fnd_file.LOG,'Statutory Maternity Pay '||l_smp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1769 edi_validation_fail := 'Y';
1770 END IF;
1771
1772 IF l_smp > 0 AND l_gender <> 'F' THEN
1773 populate_run_msg(p_assactid, 'Gender must be female for assignment ' || l_assignment_number|| ' to claim Statutory Maternity Pay.');
1774 fnd_file.put_line (fnd_file.LOG,'Gender must be female for assignment ' || l_assignment_number|| ' to claim Statutory Maternity Pay.');
1775 edi_validation_fail := 'Y';
1776 END IF;
1777
1778 IF l_ospp IS NOT NULL AND l_ospp < 0 THEN
1779 populate_run_msg(p_assactid, 'Ordinary Statutory Paternity Pay '||l_ospp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1780 fnd_file.put_line (fnd_file.LOG,'Ordinary Statutory Paternity Pay '||l_ospp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1781 edi_validation_fail := 'Y';
1782 ELSIF l_ospp IS NOT NULL AND l_ospp > 99999999 THEN
1783 populate_run_msg(p_assactid, 'Ordinary Statutory Paternity Pay '||l_ospp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1784 fnd_file.put_line (fnd_file.LOG,'Ordinary Statutory Paternity Pay '||l_ospp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1785 edi_validation_fail := 'Y';
1786 END IF;
1787
1788 IF l_sap IS NOT NULL AND l_sap < 0 THEN
1789 populate_run_msg(p_assactid, 'Statutory Adoption Pay '||l_sap||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1790 fnd_file.put_line (fnd_file.LOG,'Statutory Adoption Pay '||l_sap||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1791 edi_validation_fail := 'Y';
1792 ELSIF l_sap IS NOT NULL AND l_sap > 99999999 THEN
1793 populate_run_msg(p_assactid, 'Statutory Adoption Pay '||l_sap||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1794 fnd_file.put_line (fnd_file.LOG,'Statutory Adoption Pay '||l_sap||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1795 edi_validation_fail := 'Y';
1796 END IF;
1797
1798 IF l_aspp IS NOT NULL AND l_aspp < 0 THEN
1799 populate_run_msg(p_assactid, 'Additional Statutory Paternity Pay '||l_aspp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1800 fnd_file.put_line (fnd_file.LOG,'Additional Statutory Paternity Pay '||l_aspp||' for assignment ' || l_assignment_number|| ' must be greater than or equal to zero.');
1801 edi_validation_fail := 'Y';
1802 ELSIF l_aspp IS NOT NULL AND l_aspp > 99999999 THEN
1803 populate_run_msg(p_assactid, 'Additional Statutory Paternity Pay '||l_aspp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1804 fnd_file.put_line (fnd_file.LOG,'Additional Statutory Paternity Pay '||l_aspp||' exceeds 999999.99 for assignment ' || l_assignment_number|| '.');
1805 edi_validation_fail := 'Y';
1806 END IF;
1807
1808
1809 -- Tax Code
1810 IF (l_tax_code = ' ' OR l_tax_code IS NULL) then
1811 populate_run_msg(p_assactid, 'The Tax Code is missing for assignment ' || l_assignment_number || '.');
1812 fnd_file.put_line (fnd_file.LOG,'The Tax Code is missing for assignment ' || l_assignment_number || '.');
1813 edi_validation_fail := 'Y';
1814 END IF;
1815
1816
1817 OPEN get_effective_date;
1818 FETCH get_effective_date INTO l_eff_date;
1819 CLOSE get_effective_date;
1820
1821 l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code,l_eff_date); -- Tax code validation
1822
1823 IF (l_tax_code <> ' ' and l_tax_code IS NOT NULL)
1824 AND (l_msg_value <> ' ') THEN
1825 populate_run_msg(p_assactid,'The Tax Code ' || l_tax_code || ' for assignment ' || l_assignment_number||' is invalid.');
1826 fnd_file.put_line (fnd_file.LOG,'The Tax Code ' || l_tax_code || ' for assignment ' || l_assignment_number||' is invalid.');
1827 edi_validation_fail := 'Y';
1828 END IF;
1829
1830 -- Tax code ends
1831
1832 IF l_bacs_hash_code IS NOT NULL AND
1833 regexp_instr(l_bacs_hash_code,'^([[:alnum:]]){64}$') = 0 THEN
1834 populate_run_msg(p_assactid, 'Bacs Hash Code for assignment ' || l_assignment_number|| ' is invalid.' );
1835 fnd_file.put_line (fnd_file.LOG,'Bacs Hash Code for assignment ' || l_assignment_number|| ' is invalid.');
1836 edi_validation_fail := 'Y';
1837 END IF;
1838
1839
1840 hr_utility.set_location('Leaving '||l_proc,1);
1841
1842 END assignment_validations_aggr;
1843 --
1844 --
1845 /*--------------------------------------------------------------------------
1846 Name : NI_VALIDATIONS
1847 Purpose : This procedure validates all NI Details
1848 Arguments : Assignment Action ID , Effective Date , NI Record , Record Index
1849 Notes :
1850 --------------------------------------------------------------------------*/
1851 PROCEDURE ni_validations( p_assactid IN NUMBER,
1852 p_effective_date IN DATE,
1853 p_tab_rec_data IN pay_gb_rti_fps_archive.action_info_table,
1854 p_index IN NUMBER,
1855 edi_validation_fail out nocopy varchar2) IS
1856
1857
1858 --Declarations
1859 l_proc CONSTANT VARCHAR2(70):= g_package||'ni_validations';
1860 l_assignment_number per_assignments_f.assignment_number%TYPE;
1861
1862 -- NI Fields
1863 l_ni_category varchar2(2);
1864 l_pay_to_nic number;
1865 l_earnings_lel number;
1866 l_earnings_above_lel number;
1867 l_earnings_above_pt number;
1868 l_earnings_above_uap number;
1869 l_employer_contribution number;
1870 l_employer_contrib_y2d number;
1871 l_employee_contribution number;
1872 l_employee_contrib_y2d number;
1873
1874 -- Employee Person Detail needed for validation
1875 l_gender varchar2(1);
1876
1877
1878 BEGIN
1879 hr_utility.set_location('Entering: '||l_proc,1);
1880
1881 edi_validation_fail := 'N'; -- Initialized validation fail to N
1882
1883 begin
1884 -- Assigning values to the local variables
1885 l_assignment_number := p_tab_rec_data(2).act_info13;
1886
1887 -- Employee Person details
1888 l_gender := p_tab_rec_data(0).act_info9;
1889 exception
1890 when others then
1891 -- fetch the details using asg action id
1892 hr_utility.set_location('Getting from asg_act_id' ,2);
1893 select paaf.assignment_number,papf.sex into l_assignment_number,l_gender from per_all_assignments_f paaf,
1894 pay_assignment_Actions paa ,
1895 per_all_people_f papf where
1896 paa.assignment_action_id = p_assactid
1897 and paa.assignment_id = paaf.assignment_id
1898 and paaf.person_id = papf.person_id
1899 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date
1900 and p_effective_date between papf.effective_start_date and papf.effective_end_date;
1901 hr_utility.set_location('l_assignment_number: ' || l_assignment_number ,2);
1902 end;
1903
1904 -- Assigning values to the local variables
1905 l_ni_category := p_tab_rec_data(p_index).act_info1;
1906 l_pay_to_nic := p_tab_rec_data(p_index).act_info2;
1907 l_earnings_lel := p_tab_rec_data(p_index).act_info3;
1908 l_earnings_above_lel := p_tab_rec_data(p_index).act_info4;
1909 l_earnings_above_pt := p_tab_rec_data(p_index).act_info5;
1910 l_earnings_above_uap := p_tab_rec_data(p_index).act_info6;
1911 l_employer_contribution := p_tab_rec_data(p_index).act_info9;
1912 l_employer_contrib_y2d := p_tab_rec_data(p_index).act_info7;
1913 l_employee_contribution := p_tab_rec_data(p_index).act_info10;
1914 l_employee_contrib_y2d := p_tab_rec_data(p_index).act_info8;
1915
1916
1917 -- NI Validations
1918 IF l_ni_category IS NOT NULL THEN
1919 IF (l_gender = 'M' AND l_ni_category NOT IN ('A','C','D','J','L','N','Q','R','W','X')) OR
1920 (l_gender = 'F' AND l_ni_category NOT IN ('A','B','C','D','E','J','L','N','O','Q','R','T','W','X')) THEN
1921
1922 populate_run_msg(p_assactid,'NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1923 fnd_file.put_line (fnd_file.LOG,'NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1924 edi_validation_fail := 'Y';
1925
1926 END IF;
1927 END IF;
1928
1929 IF l_ni_category IS NOT NULL THEN
1930 IF l_pay_to_nic IS NULL THEN
1931 populate_run_msg(p_assactid,'Pay subject to NICs in this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1932 fnd_file.put_line (fnd_file.LOG,'Pay subject to NICs in this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1933 edi_validation_fail := 'Y';
1934 ELSIF l_ni_category = 'X' AND l_pay_to_nic <> 0 THEN
1935 populate_run_msg(p_assactid,'Pay subject to NICs in this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1936 fnd_file.put_line (fnd_file.LOG,'Pay subject to NICs in this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1937 edi_validation_fail := 'Y';
1938 END IF;
1939 END IF;
1940
1941
1942 IF l_ni_category IS NOT NULL THEN
1943 IF l_earnings_lel IS NULL THEN
1944 populate_run_msg(p_assactid,'Earnings at Lower Earnings Limit Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1945 fnd_file.put_line (fnd_file.LOG,'Earnings at Lower Earnings Limit Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1946 edi_validation_fail := 'Y';
1947 ELSIF l_earnings_lel < 0 THEN
1948 populate_run_msg(p_assactid,'Earnings at Lower Earnings Limit Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1949 fnd_file.put_line (fnd_file.LOG,'Earnings at Lower Earnings Limit Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1950 edi_validation_fail := 'Y';
1951 ELSIF l_ni_category = 'X' AND l_earnings_lel <> 0 THEN
1952 populate_run_msg(p_assactid,'Earnings at Lower Earnings Limit Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1953 fnd_file.put_line (fnd_file.LOG,'Earnings at Lower Earnings Limit Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1954 edi_validation_fail := 'Y';
1955 END IF;
1956 END IF;
1957
1958 IF l_ni_category IS NOT NULL THEN
1959 IF l_earnings_above_lel IS NULL THEN
1960 populate_run_msg(p_assactid,'Earnings above LEL upto PT Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1961 fnd_file.put_line (fnd_file.LOG,'Earnings above LEL upto PT Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1962 edi_validation_fail := 'Y';
1963 ELSIF l_earnings_above_lel < 0 THEN
1964 populate_run_msg(p_assactid,'Earnings above LEL upto PT Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1965 fnd_file.put_line (fnd_file.LOG,'Earnings above LEL upto PT Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1966 edi_validation_fail := 'Y';
1967 ELSIF l_ni_category = 'X' AND l_earnings_above_lel <> 0 THEN
1968 populate_run_msg(p_assactid,'Earnings above LEL upto PT Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1969 fnd_file.put_line (fnd_file.LOG,'Earnings above LEL upto PT Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1970 edi_validation_fail := 'Y';
1971 END IF;
1972 END IF;
1973
1974 IF l_ni_category IS NOT NULL THEN
1975 IF l_earnings_above_pt IS NULL THEN
1976 populate_run_msg(p_assactid,'Earnings above PT upto UAP Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1977 fnd_file.put_line (fnd_file.LOG,'Earnings above PT upto UAP Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1978 edi_validation_fail := 'Y';
1979 ELSIF l_earnings_above_pt < 0 THEN
1980 populate_run_msg(p_assactid,'Earnings above PT upto UAP Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1981 fnd_file.put_line (fnd_file.LOG,'Earnings above PT upto UAP Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1982 edi_validation_fail := 'Y';
1983 ELSIF l_ni_category = 'X' AND l_earnings_above_pt <> 0 THEN
1984 populate_run_msg(p_assactid,'Earnings above PT upto UAP Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1985 fnd_file.put_line (fnd_file.LOG,'Earnings above PT upto UAP Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
1986 edi_validation_fail := 'Y';
1987 END IF;
1988 END IF;
1989
1990 IF l_ni_category IS NOT NULL THEN
1991 IF l_earnings_above_uap IS NULL THEN
1992 populate_run_msg(p_assactid,'Earnings above UAP upto UEL Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1993 fnd_file.put_line (fnd_file.LOG,'Earnings above UAP upto UEL Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is missing.');
1994 edi_validation_fail := 'Y';
1995 ELSIF l_earnings_above_uap < 0 THEN
1996 populate_run_msg(p_assactid,'Earnings above UAP upto UEL Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1997 fnd_file.put_line (fnd_file.LOG,'Earnings above UAP upto UEL Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' is invalid.');
1998 edi_validation_fail := 'Y';
1999 ELSIF l_ni_category = 'X' AND l_earnings_above_uap <> 0 THEN
2000 populate_run_msg(p_assactid,'Earnings above UAP upto UEL Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2001 fnd_file.put_line (fnd_file.LOG,'Earnings above UAP upto UEL Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2002 edi_validation_fail := 'Y';
2003 END IF;
2004 END IF;
2005
2006 --Employer Contribution
2007 IF l_employer_contribution IS NULL THEN
2008 populate_run_msg(p_assactid, 'The Total of Employer contributions in this period is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number);
2009 fnd_file.put_line (fnd_file.LOG,'The Total of Employer contributions in this period is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number);
2010 edi_validation_fail := 'Y';
2011 ELSIF l_ni_category = 'X' AND l_employer_contribution <> 0 THEN
2012 populate_run_msg(p_assactid, 'The Total of Employer contributions in this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2013 fnd_file.put_line (fnd_file.LOG,'The Total of Employer contributions in this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2014 edi_validation_fail := 'Y';
2015 END IF;
2016
2017 IF l_employer_contrib_y2d IS NULL THEN
2018 populate_run_msg(p_assactid, 'The Total of Employer contributions Year to Date is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number);
2019 fnd_file.put_line (fnd_file.LOG,'The Total of Employer contributions Year to Date is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number);
2020 edi_validation_fail := 'Y';
2021 ELSIF l_ni_category = 'X' AND l_employer_contrib_y2d <> 0 THEN
2022 populate_run_msg(p_assactid, 'The Total of Employer contributions Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2023 fnd_file.put_line (fnd_file.LOG,'The Total of Employer contributions Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2024 edi_validation_fail := 'Y';
2025 END IF;
2026
2027 --Employee Contribution
2028 IF l_employee_contribution IS NULL THEN
2029 populate_run_msg(p_assactid, 'The Employee contributions Payable this period is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number || '.');
2030 fnd_file.put_line (fnd_file.LOG,'The Employee contributions Payable this period is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number || '.');
2031 edi_validation_fail := 'Y';
2032 ELSIF l_ni_category IN ('X','C','W') AND l_employee_contribution <> 0 THEN
2033 populate_run_msg(p_assactid, 'The Employee contributions Payable this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2034 fnd_file.put_line (fnd_file.LOG,'The Employee contributions Payable this period for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2035 edi_validation_fail := 'Y';
2036 END IF;
2037
2038 IF l_employee_contrib_y2d IS NULL THEN
2039 populate_run_msg(p_assactid, 'The Employee contributions Payable Year to Date is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number);
2040 fnd_file.put_line (fnd_file.LOG,'The Employee contributions Payable Year to Date is missing for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number);
2041 edi_validation_fail := 'Y';
2042 ELSIF l_ni_category IN ('X','C','W') AND l_employee_contrib_y2d <> 0 THEN
2043 populate_run_msg(p_assactid, 'The Employee contributions Payable Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2044 fnd_file.put_line (fnd_file.LOG,'The Employee contributions Payable Year to Date for NI Category '||l_ni_category|| ' of the assignment ' || l_assignment_number||' must be zero.');
2045 edi_validation_fail := 'Y';
2046 END IF;
2047
2048
2049 -- For Employer - ECON field validation
2050 IF l_ni_category IN ('D','E','L') THEN
2051 g_econ_ni_check := 'Y';
2052 END IF;
2053
2054 END NI_VALIDATIONS;
2055 --
2056 --
2057 /*--------------------------------------------------------------------------
2058 Name : FOOTER_VALIDATIONS
2059 Purpose : This procedure validates all Employer Footer Details
2060 Arguments : ECON , Final Submission
2061 Notes :
2062 --------------------------------------------------------------------------*/
2063 PROCEDURE footer_validations(econ IN VARCHAR2,final_submission IN VARCHAR2,flag OUT nocopy VARCHAR2)
2064 IS
2065
2066 --Declarations
2067 l_proc CONSTANT VARCHAR2(70):= g_package||'footer_validations';
2068 l_err BOOLEAN := FALSE;
2069
2070 BEGIN
2071 hr_utility.set_location('Entering: '||l_proc,1);
2072
2073 -- ECON Validations
2074 /* 16505555 Bug Fix- Validation Rule -110*/
2075 IF g_econ_ni_check = 'Y' THEN
2076 IF econ IS NULL THEN
2077 hr_utility.set_location('ECON is missing.',10);
2078 fnd_file.put_line(fnd_file.output,'ECON is missing.');
2079 l_err := true;
2080 END IF;
2081 END IF;
2082
2083 if (l_err) then
2084 flag := 'Y';
2085 else flag := 'N';
2086 end if;
2087
2088 hr_utility.set_location('Leaving '|| l_proc, 10);
2089 END footer_validations;
2090
2091
2092 -- Returns 1 on success, 0 on failure
2093 function econ_validate(econ varchar2)
2094 return number
2095 is
2096 l_fixed_value number := 37;
2097 l_temp number := 0;
2098 l_sum number := 0 ;
2099 l_reminder number;
2100 l_check_letters varchar2(19) := 'ABCDEFHJKLMNPQRTWXY';
2101
2102 begin
2103 hr_utility.trace('Entering ECON Validate');
2104
2105 -- Format Check ENNNNNNNA
2106 IF regexp_instr(econ,'^E[[:digit:]]{7}[[:alpha:]]$') = 0 THEN
2107 hr_utility.set_location('ECON Validation Fails',10);
2108 return 0;-- Validation fails
2109 END IF;
2110
2111 -- First Char must be E
2112 IF substr(econ,1,1) <> 'E' THEN
2113 hr_utility.set_location('ECON Validation Fails',10);
2114 return 0;-- Validation fails
2115
2116 -- Next 7 should be numeric ranging 3000000 up to 3999999
2117 ELSIF substr(econ,2,7) < 3000000 OR substr(econ,2,7) > 3999999 THEN
2118 hr_utility.set_location('ECON Validation Fails',20);
2119 return 0;-- Validation fails
2120 END IF;
2121
2122 -- Last char must be alpha satifying modulas 19 rule
2123 for i in reverse 2..8 loop
2124 l_temp := substr(econ,(10-i),1) * i;
2125 l_sum := l_sum + l_temp;
2126 end loop;
2127
2128 l_sum := l_sum + l_fixed_value;
2129 l_reminder := mod(l_sum,19);
2130
2131 IF substr(l_check_letters,l_reminder+1,1) <> substr(econ,9,1) THEN
2132 hr_utility.set_location('ECON Validation Fails',30);
2133 return 0; -- Validation Fails
2134 END IF;
2135
2136 return 1; -- Validation passes
2137 hr_utility.trace('Leaving ECON Validate');
2138 END econ_validate;
2139
2140 --
2141 -- Function to validate the format of the fields
2142 function validate_input(p_input_value varchar2,
2143 p_validate_mode varchar2)
2144 return number is
2145 --
2146 l_valid number := 0;
2147 l_invalid_char constant varchar2(1) := '~'; -- required for translate
2148 l_char_chk constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
2149
2150 --
2151 l_emp_set constant varchar2(36) := '-''.';
2152 l_space constant varchar2(10) := ' ';
2153 l_mix_chars constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
2154 l_number_chk constant varchar2(10) := '0123456789';
2155 l_char_set_A constant varchar2(36) := '.,-()/=!""%&*;<>''+:?\[]@$#^{}_';
2156 l_char_set_B constant varchar2(52) := '.,-()/=!""%&*;<>''+:?';
2157 l_char_set_C constant varchar2(52) := '.-''';
2158 l_char_set_D constant varchar2(52) := '-''';
2159 l_translated_value varchar2(200); -- Required to output failing char.
2160 --
2161 BEGIN
2162 --
2163 hr_utility.trace('Entering validate_input');
2164 hr_utility.trace('p_validate_mode='||p_validate_mode);
2165 hr_utility.trace('p_input_value='||p_input_value);
2166 --
2167
2168 if p_validate_mode = 'EMP_NAME' then
2169 if ( substr(p_input_value,1,1) =' ') then
2170 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2171 l_valid := 2;
2172 else
2173 l_translated_value :=
2174 translate(p_input_value,
2175 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_A||l_space,l_invalid_char);
2176
2177 if l_translated_value is not null then
2178 hr_utility.trace('Invalid chars found: '||l_translated_value);
2179 l_valid := 1; -- Not valid
2180 else
2181 l_valid := 0; -- Valid
2182 end if;
2183 end if ;
2184
2185 elsIF p_validate_mode = 'NUMBER' then
2186 --
2187 -- Check for Valid First Char
2188 --
2189 l_translated_value :=
2190 translate(p_input_value,
2191 l_invalid_char||l_number_chk,l_invalid_char);
2192
2193 if l_translated_value is not null then
2194 hr_utility.trace('Invalid chars found: '||l_translated_value);
2195 l_valid := 1; -- Not valid
2196 else
2197 l_valid := 0; -- Valid
2198 end if;
2199
2200 elsIF p_validate_mode = 'FULL_EDI' then
2201 --
2202 -- Check for Valid First Char
2203 --
2204 if ( substr(p_input_value,1,1) =' ') then
2205 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2206 l_valid := 2;
2207 else
2208 l_translated_value :=
2209 translate(p_input_value,
2210 l_invalid_char||l_char_chk||l_number_chk||l_char_set_B||l_space,l_invalid_char);
2211
2212 if l_translated_value is not null then
2213 hr_utility.trace('Invalid chars found: '||l_translated_value);
2214 l_valid := 1; -- Not valid
2215 else
2216 l_valid := 0; -- Valid
2217 end if;
2218 end if;
2219
2220 elsIF p_validate_mode = 'NAME' then
2221 --
2222 -- Check for Valid First Char
2223 --
2224 if not (substr(p_input_value,1,1) between 'A' and 'Z'
2225 or substr(p_input_value,1,1) between 'a' and 'z') then
2226 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2227 l_valid := 2;
2228 else
2229 l_translated_value :=
2230 translate(p_input_value,
2231 l_invalid_char||l_mix_chars||l_char_set_D||l_space,l_invalid_char);
2232
2233 if l_translated_value is not null then
2234 hr_utility.trace('Invalid chars found: '||l_translated_value);
2235 l_valid := 1; -- Not valid
2236 else
2237 l_valid := 0; -- Valid
2238 end if;
2239 end if;
2240
2241 elsIF p_validate_mode = 'TITLE' then
2242 --
2243 -- Check for Valid First Char
2244 --
2245 if not (substr(p_input_value,1,1) between 'A' and 'Z'
2246 or substr(p_input_value,1,1) between 'a' and 'z') then
2247 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2248 l_valid := 2;
2249 else
2250 l_translated_value :=
2251 translate(p_input_value,
2252 l_invalid_char||l_mix_chars||l_char_set_C||l_space,l_invalid_char);
2253
2254 if l_translated_value is not null then
2255 hr_utility.trace('Invalid chars found: '||l_translated_value);
2256 l_valid := 1; -- Not valid
2257 else
2258 l_valid := 0; -- Valid
2259 end if;
2260 end if;
2261
2262 ELSIF p_validate_mode = 'ADDRESS' THEN
2263 --
2264 -- Check for Valid First Char
2265 --
2266 if not (substr(p_input_value,1,1) between 'A' and 'Z'
2267 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
2268 -- First char invalid
2269 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2270 l_valid := 2;
2271 else
2272 l_translated_value :=
2273 translate(p_input_value,
2274 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
2275
2276 if l_translated_value is not null then
2277 hr_utility.trace('Invalid chars found: '||l_translated_value);
2278 l_valid := 1; --Invalid
2279 else
2280 l_valid := 0; -- Valid
2281 end if;
2282 end if;
2283
2284 elsif p_validate_mode = 'COUNTRY' then
2285 if not (substr(p_input_value,1,1) between 'A' and 'Z' or
2286 substr(p_input_value,1,1) between 'a' and 'z' ) then
2287 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2288 l_valid := 2;
2289 else
2290 l_translated_value :=
2291 translate(p_input_value,
2292 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
2293
2294 if l_translated_value is not null then
2295 hr_utility.trace('Invalid chars found: '||l_translated_value);
2296 l_valid := 1; -- Not valid
2297 else
2298 l_valid := 0; -- Valid
2299 end if;
2300 end if ;
2301
2302 -- character set E has alpha,numerals,space
2303 elsif p_validate_mode = 'POSTALCODE' then
2304
2305 if not (substr(p_input_value,1,1) between 'A' and 'Z'
2306 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
2307 -- First char invalid
2308 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2309 l_valid := 2;
2310 else
2311 l_translated_value := translate(p_input_value,
2312 l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
2313 --
2314 if l_translated_value is not null then
2315 hr_utility.trace('Invalid chars found: '||l_translated_value);
2316 l_valid := 1; -- Not valid
2317 else
2318 l_valid := 0; -- Valid
2319 end if;
2320 end if;
2321
2322
2323 -- character set E has alpha,numerals,space
2324 elsif p_validate_mode = 'PASSPORT' then
2325 l_translated_value := translate(p_input_value,
2326 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
2327 --
2328 if l_translated_value is not null then
2329 hr_utility.trace('Invalid chars found: '||l_translated_value);
2330 l_valid := 1; -- Not valid
2331 else
2332 l_valid := 0; -- Valid
2333 end if;
2334
2335
2336 elsif p_validate_mode = 'PAYROLLID' then
2337 if ( substr(p_input_value,1,1) =' ') then
2338 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2339 l_valid := 2;
2340 else
2341 l_translated_value :=
2342 translate(p_input_value,
2343 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_B||l_space,l_invalid_char);
2344
2345 if l_translated_value is not null then
2346 hr_utility.trace('Invalid chars found: '||l_translated_value);
2347 l_valid := 1; -- Not valid
2348 else
2349 l_valid := 0; -- Valid
2350 end if;
2351 end if ;
2352
2353 END IF;
2354 --
2355 hr_utility.trace('Leaving validate_input');
2356 return l_valid;
2357 end validate_input;
2358
2359 -- Function for validating Date fields
2360 FUNCTION date_validate (c_assignment_action_id NUMBER,
2361 p_mode VARCHAR2,
2362 p_validate_date DATE)
2363 RETURN NUMBER
2364 IS
2365
2366
2367 cursor csr_parameter_info is
2368 select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
2369 /*ppa.effective_date*/
2370 sysdate
2371 from pay_payroll_actions ppa
2372 ,pay_assignment_actions paa
2373 where paa.assignment_action_id = c_assignment_action_id
2374 and ppa.payroll_action_id = paa.payroll_action_id;
2375
2376 l_date_valid DATE;
2377 l_return_valid NUMBER;
2378 l_test_submission VARCHAR2(1);
2379 l_tax_date DATE;
2380 l_tax_year VARCHAR2(4);
2381 l_tax_year_start DATE ;
2382 BEGIN
2383 l_return_valid := 1; -- Initializing to one
2384 open csr_parameter_info;
2385 fetch csr_parameter_info into l_test_submission,l_tax_date;
2386 close csr_parameter_info;
2387
2388 hr_utility.trace('date as parameter : '||p_validate_date);
2389
2390 l_tax_year := to_char(l_tax_date,'RRRR');
2391 if (l_tax_date > to_date(l_tax_year||'0405','RRRRMMDD')) THEN
2392 l_tax_date := ADD_MONTHS(to_date(l_tax_year||'0405','RRRRMMDD'),12) ; /*tax year end date*/
2393 else
2394 l_tax_date := to_date(l_tax_year||'0405','RRRRMMDD'); /*tax year end date*/
2395 end if;
2396
2397 l_tax_date := fnd_date.canonical_to_date(to_char(l_tax_date,'RRRRMMDD'));
2398
2399 l_tax_year_start := add_months(l_tax_date,-12)+1 ;
2400
2401 l_date_valid := p_validate_date;
2402
2403 -- Start Date Validation
2404 if (p_mode = 'UK_EMPL_DATE') then
2405 if (l_test_submission = 'N') then -- Live Files
2406 if (l_date_valid > (sysdate+30)) then -- No later than current date + 30 days
2407 l_return_valid := 0;
2408 end if;
2409 else -- Test Files
2410 if (l_date_valid > add_months(l_tax_date,12)) then -- Current Tax year +1
2411 l_return_valid := 0;
2412 end if;
2413 end if;
2414 -- End Date Validation
2415 elsif (p_mode = 'UK_EMPL_DATE_LEAVING') then
2416 if (l_test_submission = 'N') then --Live Files
2417 if (l_date_valid > (sysdate+30)) then -- No later than current date + 30 days
2418 l_return_valid := 0;
2419 elsif(l_date_valid < (add_months(l_tax_date,-24)+1)) then -- Start of previous tax year
2420 l_return_valid := 0;
2421 end if;
2422 else -- Test Files
2423 if (l_date_valid > add_months(l_tax_date,12)) then -- Current Tax year +1
2424 l_return_valid := 0;
2425 end if;
2426 end if;
2427
2428 end if;
2429
2430 RETURN l_return_valid;
2431 END date_validate;
2432
2433 end PAY_GB_RTI_FPS_VALIDATE_13;
[Home] [Help]