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