[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_EOY_VAL
Source
1 Package body PAY_ZA_EOY_VAL as
2 /* $Header: pyzatyev.pkb 120.56.12020000.11 2012/12/21 09:24:43 abdash ship $ */
3 -- Pacakge Body
4 g_xml_element_count number:=0;
5 g_asg_set_where varchar2(500);
6 g_sort_order_clause varchar2(500);
7
8 type msgtext_tab is table of varchar2(2000) index by binary_integer;
9
10 Function modulus_10_test
11 (p_tax_number in number) return number
12 -- return 1 if correct, else return 0
13 is
14
15 nine_digits number(9);
16 tax_no number(10);
17 n1 number(2);
18 n2 number(1):= substr(p_tax_number,2,1);
19 n3 number(2):= 2 * substr(p_tax_number,3,1);
20 n4 number(1):= substr(p_tax_number,4,1);
21 n5 number(2):= 2 * substr(p_tax_number,5,1);
22 n6 number(1):= substr(p_tax_number,6,1);
23 n7 number(2):= 2 * substr(p_tax_number,7,1);
24 n8 number(1):= substr(p_tax_number,8,1);
25 n9 number(2):= 2* substr(p_tax_number,9,1);
26 n10 number(2):= substr(p_tax_number,10,1);
27 temp number(2);
28 temp1 number(2);
29 res number(2);
30 begin
31 -- Store the 10 digit tax number
32 -- only the first nine chars are used for the test
33
34
35 tax_no:=p_tax_number;
36 If ((length(tax_no) <> 10) or (tax_no is NULL)) then
37 return 0;
38 elsif tax_no between 7000000000 and 7980000000 then
39 -- replace the first digit 7 by 4
40 nine_digits := substr(tax_no,2,10);
41 tax_no := 4||nine_digits;
42 end if;
43
44 -- otherwise do not replace the first digit
45 -- Multiply the first number and thereafter every second number by 2
46 -- if the result is greater than 9, add the individual digits to get
47 -- the final answer
48
49 n1 := 2 * substr(tax_no,1,1);
50 if n1 > 9 then
51 n1 := substr(n1,1,1) + substr(n1,2,1);
52 end if;
53 if n3 > 9 then
54 n3 := substr(n3,1,1) + substr(n3,2,1);
55 end if;
56 if n5 > 9 then
57 n5 := substr(n5,1,1) + substr(n5,2,1);
58 end if;
59 if n7 > 9 then
60 n7 := substr(n7,1,1) + substr(n7,2,1);
61 end if;
62 if n9 > 9 then
63 n9 := substr(n9,1,1) + substr(n9,2,1);
64 end if;
65 temp := n1 + n2 + n3 + n4 + n5 + n6 + n7 + n8 + n9;
66
67 -- deduct the total result from the next full ten to get the last digit
68 if mod(temp,10) > 0 then
69 temp1 := temp - mod(temp,10) + 10;
70 else
71 temp1 := temp;
72 end if;
73 res := temp1 - temp;
74 if res = n10 then
75 return 1;
76 else
77 return 0;
78 end if;
79
80 exception
81
82 when value_error then
83 return 0;
84
85 when others then
86 raise_application_error(-20101,'PAY_ZA_EOY_VAL.MODULUS_10_TEST exception');
87
88
89
90 end modulus_10_test;
91
92 --For validation of SDL and UIF number, p_type is not null.
93 --For PAYE Reference number, this is null
94 Function modulus_10_test
95 (p_tax_number in varchar2
96 ,p_type in varchar2 default null)
97 return number
98 -- return 1 if correct, else return 0
99 is
100
101 nine_digits varchar2(9);
102 tax_no varchar2(10);
103 n1 varchar2(2);
104 n2 varchar2(1);
105 n3 varchar2(2);
106 n4 varchar2(1);
107 n5 varchar2(2);
108 n6 varchar2(1);
109 n7 varchar2(2);
110 n8 varchar2(1);
111 n9 varchar2(2);
112 n10 varchar2(2);
113 temp varchar2(2);
114 temp1 varchar2(2);
115 res varchar2(2);
116 begin
117 -- Store the 10 digit tax number
118 -- only the first nine chars are used for the test
119
120 n2 := substr(p_tax_number,2,1);
121 n3 := 2 * substr(p_tax_number,3,1);
122 n4 := substr(p_tax_number,4,1);
123 n5 := 2 * substr(p_tax_number,5,1);
124 n6 := substr(p_tax_number,6,1);
125 n7 := 2 * substr(p_tax_number,7,1);
126 n8 := substr(p_tax_number,8,1);
127 n9 := 2* substr(p_tax_number,9,1);
128 n10 := substr(p_tax_number,10,1);
129
130 tax_no:=p_tax_number;
131 If ((length(tax_no) <> 10) or (tax_no is NULL)) then
132 return 0;
133 --For UIF and SDL, replace first letter by 4.
134 elsif p_type is not null then
135 -- replace the first letter by 4
136 nine_digits := substr(tax_no,2,10);
137 tax_no := 4||nine_digits;
138 elsif tax_no between 7000000000 and 7980000000 then
139 -- replace the first digit 7 by 4
140 nine_digits := substr(tax_no,2,10);
141 tax_no := 4||nine_digits;
142 end if;
143
144 -- otherwise do not replace the first digit
145 -- Multiply the first number and thereafter every second number by 2
146 -- if the result is greater than 9, add the individual digits to get
147 -- the final answer
148
149 n1 := 2 * substr(tax_no,1,1);
150 if n1 > 9 then
151 n1 := substr(n1,1,1) + substr(n1,2,1);
152 end if;
153 if n3 > 9 then
154 n3 := substr(n3,1,1) + substr(n3,2,1);
155 end if;
156 if n5 > 9 then
157 n5 := substr(n5,1,1) + substr(n5,2,1);
158 end if;
159 if n7 > 9 then
160 n7 := substr(n7,1,1) + substr(n7,2,1);
161 end if;
162 if n9 > 9 then
163 n9 := substr(n9,1,1) + substr(n9,2,1);
164 end if;
165 temp := n1 + n2 + n3 + n4 + n5 + n6 + n7 + n8 + n9;
166
167 -- deduct the total result from the next full ten to get the last digit
168 if mod(temp,10) > 0 then
169 temp1 := temp - mod(temp,10) + 10;
170 else
171 temp1 := temp;
172 end if;
173 res := temp1 - temp;
174 if res = n10 then
175 return 1;
176 else
177 return 0;
178 end if;
179
180 exception
181
182 when value_error then
183 return 0;
184
185 when others then
186 raise_application_error(-20101,'PAY_ZA_EOY_VAL.MODULUS_10_TEST exception');
187
188 end modulus_10_test;
189
190 ---------------------------------------------------------------------------------------
191 --Modulus_13_test for ID Number
192 ---------------------------------------------------------------------------------------
193 Function modulus_13_test
194 (p_id_number in varchar2)
195 return number
196 -- return 1 if correct, else return 0
197 is
198
199 nine_digits varchar2(9);
200 l_national_identifier varchar2(13);
201 check_digit varchar2(1);
202 digit_11_12 varchar2(2);
203 sum_odd_digits varchar2(3);
204 combine_even_digits varchar2(6);
205 combine_even_digits_mul_2 varchar2(8);
206 sum_combine_even_digits_mul_2 varchar2(3);
207 final_sum varchar2(3);
208 final_sum_last_digit varchar2(2);
209 last_digit varchar2(2);
210 temp varchar2(2);
211 temp1 varchar2(2);
212 res varchar2(2);
213 begin
214 -- Store the 13 ID number
215 if length(p_id_number) <> 13 or p_id_number is null then
216 return 0;
217 end if;
218
219 l_national_identifier:=p_id_number;
220 check_digit := substr(l_national_identifier,13,1);
221 -- Fox for bug#12660271. This validation is not required according to spec.
222 /*
223 digit_11_12 := substr(l_national_identifier,11,1) || substr(l_national_identifier,12,1);
224
225 if(to_number(digit_11_12) < 8)
226 then
227
228 l_national_identifier := substr(l_national_identifier, 1, 10) || '0' || '8' || check_digit;
229 end if;
230
231 if(to_number(digit_11_12) > 9 and to_number(digit_11_12) < 14)
232 then
233
234 l_national_identifier := substr(l_national_identifier, 1, 10) || '1' || '8' || check_digit;
235 end if;
236 */
237
238 -- otherwise do not replace
239 -- a)Add all the digits in the odd positions (excluding last digit).
240 -- b)Move the even positions into a field and multiply the number by 2
241 -- c)Add the digits of the result in b
242 -- d)Add the answer in [a] to the answer in [c].
243 -- e)Subtract the last digit of d from 10. The number must tally with the last number in the ID Number.
244 -- If the result is 2 digits, the last digit is used to compare against the last number in the ID Number.
245 -- If the answer differs, the ID number is invalid.
246
247 sum_odd_digits := substr(l_national_identifier,1,1) +
248 substr(l_national_identifier,3,1) +
249 substr(l_national_identifier,5,1) +
250 substr(l_national_identifier,7,1) +
251 substr(l_national_identifier,9,1) +
252 substr(l_national_identifier,11,1);
253
254 combine_even_digits := substr(l_national_identifier,2,1) ||
255 substr(l_national_identifier,4,1) ||
256 substr(l_national_identifier,6,1) ||
257 substr(l_national_identifier,8,1) ||
258 substr(l_national_identifier,10,1) ||
259 substr(l_national_identifier,12,1);
260
261 combine_even_digits_mul_2 := (combine_even_digits) * 2;
262
263 /* Length of combine_even_digits_mul_2 should be atleast 7 */
264 combine_even_digits_mul_2 := lpad(combine_even_digits_mul_2,7,0);
265
266 sum_combine_even_digits_mul_2:= substr(combine_even_digits_mul_2,1,1) +
267 substr(combine_even_digits_mul_2,2,1) +
268 substr(combine_even_digits_mul_2,3,1) +
269 substr(combine_even_digits_mul_2,4,1) +
270 substr(combine_even_digits_mul_2,5,1) +
271 substr(combine_even_digits_mul_2,6,1) +
272 substr(combine_even_digits_mul_2,7,1);
273
274 final_sum := sum_odd_digits + sum_combine_even_digits_mul_2;
275 final_sum_last_digit := mod(final_sum, 10);
276
277 last_digit := to_char(10 - final_sum_last_digit);
278 if(length(last_digit) = 2) then
279 last_digit := substr(last_digit,2,1);
280 end if;
281
282 if(last_digit <> check_digit) then
283 return 0;
284 end if;
285 return 1;
286 exception
287 when others then
288 return 0;
289
290 end modulus_13_test;
291
292 -- Function to check id ID number and Data of birth of a person.
293 --
294 Function check_id_dob(p_id_number in number,p_dob in date) return number
295 is
296 -- return 0 if false, else return 1
297
298 id_six_nos number(6);
299 dob_six_nos varchar2(6);
300 begin
301 if (p_id_number is NULL OR p_dob is NULL) OR (length(p_id_number) <> 13) then
302 return 0;
303 else
304 -- Get the first six characters of the ID number
305 -- Get the data of birth in YYMMDD format and compare
306
307 id_six_nos := substr(p_id_number,1,6);
308
309 dob_six_nos := to_char(p_dob,'YYMMDD');
310 if id_six_nos <> to_number(dob_six_nos) then
311 return 0;
312 else
313 return 1;
314 end if;
315 end if;
316
317 exception
318
319 when others then
320 raise_application_error(-20102,'PAY_ZA_EOY_VAL.CHECK_ID_DOB exception');
321
322
323 end check_id_dob;
324
325 -- Function to check id ID number and Data of birth of a person.
326 -- From Tax Year 2010 onwards
327 Function check_id_dob
328 ( p_id_number in varchar2
329 ,p_dob in date
330 ,p_new_format in varchar2) return number
331 is
332 -- return 0 if false, else return 1
333
334 id_six_nos varchar2(6);
335 dob_six_nos varchar2(6);
336 begin
337
338 id_six_nos := substr(p_id_number,1,6);
339
340 dob_six_nos := to_char(p_dob,'YYMMDD');
341 if id_six_nos <> dob_six_nos then
342 return 0;
343 else
344 return 1;
345 end if;
346 -- end if;
347
348 exception
349
350 when others then
351 raise_application_error(-20102,'PAY_ZA_EOY_VAL.CHECK_ID_DOB exception');
352
353 end check_id_dob;
354
355
356 -- Function to check unique IRP5 number
357 Function check_IRP5_no( p_payroll_id in Number
358 ,p_irp5no in varchar2
359 ,p_tax_year in varchar2) return Number
360 -- return 0 if false, else return 1
361 is
362 v_count number;
363 v_tax_start_date varchar2(20);
364 v_tax_end_date varchar2(20);
365 begin
366 If (p_payroll_id is NULL) or (p_irp5no is NULL) or (p_tax_year is NULL) then
367 return 0;
368 else
369
370 get_tax_start_end_dates(p_payroll_id,p_tax_year,v_tax_start_date,v_tax_end_date);
371 --removed the sub query for selecting the max effective date ,since the date tracked
372 --checks would anyway get the latest payroll details
373
374 select count(*)
375 into v_count
376 from pay_all_payrolls_f pap,
377 hr_soft_coding_keyflex scl
378 where pap.business_group_id = (select pap2.business_group_id from pay_all_payrolls_f pap2 where pap2.payroll_id=p_payroll_id and rownum=1)
379 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
380 and scl.segment8 = p_irp5no
381 and to_date(v_tax_end_date,'DD-MM-YYYY') between pap.effective_start_date and pap.effective_end_date;
382 if v_count > 1 then
383 return 0;
384 else
385 return 1;
386 end if;
387 end if;
388
389 exception
390
391 when others then
392 raise_application_error(-20103,'PAY_ZA_EOY_VAL.CHECK_IRP5_NO exception');
393
394
395 end check_IRP5_no;
396
397 -- Prcoedure to get the Tax Year Start Date and End date
398 Procedure get_tax_start_end_dates
399 (p_payroll_id in number
400 ,p_tax_year in varchar2
401 ,p_tax_start_date out nocopy varchar2
402 ,p_tax_end_date out nocopy varchar2) as
403 begin
404
405 select to_char(min(start_date),'dd-mon-yyyy'),to_char(max(end_date),'dd-mon-yyyy')
406 into p_tax_start_date,p_tax_end_date
407 from per_time_periods
408 where payroll_id = p_payroll_id
409 and prd_information1 = p_tax_year;
410
411 exception
412
413 when others then
414 raise_application_error(-20104,'PAY_ZA_EOY_VAL.GET_TAX_START_END_DATE exception');
415
416
417 end get_tax_start_end_dates;
418
419
420
421 -- Procedure to populate the validation messages form FND_NEW_MESSAGES
422 Procedure populate_messages(c_name OUT NOCOPY VARCHAR2,
423 c_ref_no OUT NOCOPY VARCHAR2,
424 c_ref_no_invalid OUT NOCOPY VARCHAR2,
425 c_person_name OUT NOCOPY VARCHAR2,
426 c_telephone OUT NOCOPY VARCHAR2,
427 c_add_line1 OUT NOCOPY VARCHAR2,
428 c_pcode OUT NOCOPY VARCHAR2,
429 c_pcode1 OUT NOCOPY VARCHAR2,
430 trade_name OUT NOCOPY VARCHAR2,
431 paye_no OUT NOCOPY VARCHAR2,
432 paye_no1 OUT NOCOPY VARCHAR2,
433 address OUT NOCOPY VARCHAR2,
434 pcode OUT NOCOPY VARCHAR2,
435 pcode1 OUT NOCOPY VARCHAR2,
436 payroll_number OUT NOCOPY VARCHAR2,
437 nature_entered OUT NOCOPY VARCHAR2,
438 id_passport OUT NOCOPY VARCHAR2,
439 no_id_passport OUT NOCOPY VARCHAR2,
440 sur_trade_name OUT NOCOPY VARCHAR2,
441 cc_no OUT NOCOPY VARCHAR2,
442 sur_first_name OUT NOCOPY VARCHAR2,
443 M_sur_fname OUT NOCOPY VARCHAR2,
444 M_id_pno_fname OUT NOCOPY VARCHAR2,
445 M_cc_trade_name OUT NOCOPY VARCHAR2,
446 M_lname_fname_cc OUT NOCOPY VARCHAR2,
447 invalid_it_no OUT NOCOPY VARCHAR2,
448 birth_id OUT NOCOPY VARCHAR2,
449 legal_entity OUT NOCOPY VARCHAR2,
450 no_site_paye_split OUT NOCOPY VARCHAR2,
451 neg_bal_not_alwd OUT NOCOPY VARCHAR2,
452 clearance_num OUT NOCOPY VARCHAR2,
453 terminate_emp OUT NOCOPY VARCHAR2,
454 town_city OUT NOCOPY VARCHAR2,
455 employer_name OUT NOCOPY VARCHAR2) as
456 begin
457 -- File header record messages
458 c_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_CREATOR');
459 c_ref_no:=fnd_message.get_string('PAY','PY_ZA_ENTER_CREATOR_REF_NO');
460 c_ref_no_invalid:=fnd_message.get_string('PAY','PY_ZA_INVALID_CREATOR_REF_NO');
461 c_person_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_CONTACT_NAME');
462 c_telephone:=fnd_message.get_string('PAY','PY_ZA_ENTER_CONTACT_PHONE_NO');
463 c_add_line1:=fnd_message.get_string('PAY','PY_ZA_ENTER_ADDRESS_LINE1');
464 c_pcode:=fnd_message.get_string('PAY','PY_ZA_ENTER_POSTAL_CODE');
465 c_pcode1:=fnd_message.get_string('PAY','PY_ZA_INVALID_POSTAL_CODE');
466
467 -- Employer validation messages
468 trade_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_TRADING_NAME');
469 paye_no:=fnd_message.get_string('PAY','PY_ZA_ENTER_TAX_REF_NO');
470 paye_no1:=fnd_message.get_string('PAY','PY_ZA_INVALID_TAX_REF_NO');
471 address:=c_add_line1;
472 pcode:= c_pcode;
473 pcode1:=c_pcode1;
474 --Added for TYE09
475 employer_name :=fnd_message.get_string('PAY','PY_ZA_INVALID_EMPLOYER_NAME');
476 -- Payroll validation message
477 payroll_number:=fnd_message.get_string('PAY','PY_ZA_INVALID_IRP5_NO');
478
479 --Employee validation messages
480 nature_entered:=fnd_message.get_string('PAY','PY_ZA_ENTER_NATURE_PERSON');
481 id_passport:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_AC_ID_PASSNO');
482 no_id_passport:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_B_ID_PASSNO');
483 sur_trade_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_DEFGHK_TRADE');
484 cc_no:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_DEHK_CC_NO');
485
486 sur_first_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_ABC_S_F_NAME');
487 M_sur_fname:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_S_F_NAME');
488 M_id_pno_fname:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_IDPNO_SF_NAM');
489 M_cc_trade_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_CC_NO');
490
491 M_lname_fname_cc:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_FM_NAME_CCNO');
492
493 invalid_it_no:=fnd_message.get_string('PAY','PY_ZA_INVALID_IT_TAX_NO');
494 birth_id:=fnd_message.get_string('PER','HR_ZA_INVALID_NI_DOB');
495 legal_entity:=fnd_message.get_string('PAY','PY_ZA_ENTER_LEGAL_ENTITY');
496
497 no_site_paye_split:=fnd_message.get_string('PAY','PY_ZA_NO_SITE_PAYE_SPLIT');
498 neg_bal_not_alwd :=fnd_message.get_string('PAY','PY_ZA_NEG_BAL_NOT_ALWD');
499 clearance_num:=fnd_message.get_string('PAY','PA_ZA_ENTER_CLEARANCE_NUM');
500 terminate_emp:=fnd_message.get_string('PAY','PA_ZA_TERMINATE_EMP');
501 town_city := fnd_message.get_string('PAY','PAY_ZA_ENTER_TOWN_CITY');
502
503 exception
504
505 when others then
506 raise_application_error(-20105,'PAY_ZA_EOY_VAL.POPULATE_MESSAGES exception');
507
508
509 end populate_messages;
510
511 -- function to convert the decimal separator if it is ',' Eg 123,45 => 123.45
512 function decimal_character_conversion ( amount_char in varchar2) return varchar2 is
513 amount_num number ;
514 amount_ret varchar2(100) ;
515 begin
516 amount_num := to_number(amount_char) ;
517 return amount_char ;
518 exception
519 when others then
520 amount_ret := replace(amount_char,',','.') ;
521 return amount_ret ;
522 end decimal_character_conversion ;
523
524 -- for TYE 2008 write the exceptions to the log file
525 PROCEDURE VALIDATE_TYE_DATA (
526 errbuf out nocopy varchar2,
527 retcode out nocopy number,
528 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
529 p_tax_yr_start_date IN DATE,
530 p_tax_yr_end_date IN DATE
531 )is
532 /* Cursor to select all Income Sars Codes which have negative balances*/
533 g_default_clrno CONSTANT VARCHAR2(11) := '99999999999' ;
534 g_default_dirno CONSTANT VARCHAR2(7) := 'Default' ;
535 g_application_id CONSTANT NUMBER := 801 ;
536
537 CURSOR negative_amt_check_cur(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
538 select irp5.code,
539 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
540 FROM pay_za_irp5_bal_codes irp5,
541 ff_archive_items arc,
542 ff_database_items dbi
543 WHERE dbi.user_entity_id = arc.user_entity_id
544 and irp5.user_name = dbi.user_name
545 AND arc.context1 = p_asgn_action_id
546 and (( irp5.balance_sequence = 1 and
547 (
548 (irp5.code BETWEEN 3601 AND 3607)
549 OR
550 (irp5.code BETWEEN 3609 AND 3613)
551 or
552 (irp5.code BETWEEN 3615 AND 3617 ) -- 3608 and 3614 are LMPSM balance
553 or
554 (irp5.code BETWEEN 3651 AND 3667)
555 OR
556 (irp5.code BETWEEN 3701 AND 3706)
557 OR
558 (irp5.code BETWEEN 3708 AND 3717) -- 3707 and 3718 are LMPSM balances
559 OR
560 (irp5.code BETWEEN 3751 AND 3768)
561 or
562 (irp5.code BETWEEN 3801 and 3810)
563 or
564 (irp5.code BETWEEN 3851 and 3860)
565 or
566 (irp5.code BETWEEN 3813 and 3863)
567 OR -- 3901 to 3907 are LMPSM balances
568 (irp5.code = 3908)
569 OR
570 (irp5.code BETWEEN 3951 and 3957)
571 or
572 (irp5.code BETWEEN 3695 and 3699)
573 OR -- 4001 to 4004, 4006, 4007 are Deduction balances
574 (irp5.code = 4005 )
575 or
576 (irp5.code = 4018)
577 or
578 (irp5.code BETWEEN 4024 and 4025)
579 or
580 (irp5.code BETWEEN 4101 and 4103)
581 or
582 (irp5.code BETWEEN 4472 and 4474)
583 or
584 (irp5.code BETWEEN 4485 and 4487)
585 or
586 (irp5.code = 4493)
587 )
588 )
589 OR
590 ( irp5.code = 4005 AND irp5.balance_sequence = 2)) --Added for Bug 8213478
591 group by irp5.code
592 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) < 0 ;
593
594 /* Cursor to fetch all LumpSum Balance Sars Codes ( For Main Certificate) which have neagtive amounts*/
595 CURSOR fetch_lmpsm_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) is
596 select irp5.code,
597 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
598 FROM pay_za_irp5_bal_codes irp5,
599 ff_archive_items arc,
600 ff_database_items dbi,
601 ff_archive_item_contexts faic2,
602 ff_contexts ffc2
603 WHERE irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3909, 3915, 3920)
604 AND irp5.balance_sequence = 3
605 AND irp5.user_name = dbi.user_name
606 AND dbi.user_entity_id = arc.user_entity_id
607 AND arc.context1 = p_asgn_action_id
608 AND faic2.archive_item_id = arc.archive_item_id
609 AND ffc2.context_id = faic2.context_id
610 AND ffc2.context_name = 'SOURCE_TEXT'
611 AND faic2.CONTEXT = 'To Be Advised'
612 group by irp5.code
613 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0 ;
614
615 /* Cursor to fetch The -ve Lump Sum balances with Directive Number other than Default
616 That is checking the Lump Sum Certificate balances */
617 CURSOR get_lmpsm_crt_bal (p_asg_act_id pay_assignment_actions.assignment_action_id%TYPE) is
621 from pay_za_irp5_bal_codes irp5,
618 select sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) value
619 , faic2.CONTEXT Tax_Directive_Number
620 , irp5.code code
622 ff_archive_items arc,
623 ff_database_items dbi,
624 ff_archive_item_contexts faic2,
625 ff_contexts ffc2
626 where arc.context1 in (select ch.assignment_action_id
627 from pay_assignment_actions main
628 , pay_assignment_actions ch
629 where main.assignment_action_id = p_asg_act_id
630 and ch.payroll_action_id = main.payroll_action_id
631 and ch.assignment_action_id < main.assignment_action_id
632 AND ch.assignment_id = main.assignment_id)
633 and
634 (
635 arc.value is not null
636 or
637 (
638 arc.value is not null
639 and arc.value <> 0
640 )
641 )
642 and dbi.user_entity_id = arc.user_entity_id
643 and irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3909, 3915, 3920)
644 AND irp5.balance_sequence = 3
645 AND dbi.user_name = irp5.user_name
646 AND faic2.archive_item_id = arc.archive_item_id
647 AND ffc2.context_id = faic2.context_id
648 AND ffc2.context_name = 'SOURCE_TEXT'
649 group BY faic2.CONTEXT
650 , irp5.code
651 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) < 0;
652
653
654 CURSOR fetch_deduction_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
655 select irp5.code ,
656 faic2.CONTEXT clearance_num,
657 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
658 FROM pay_za_irp5_bal_codes irp5,
659 ff_archive_items arc,
660 ff_database_items dbi,
661 ff_archive_item_contexts faic2,
662 ff_contexts ffc2
663 WHERE irp5.code IN (4001, 4002, 4003, 4004, 4006, 4007)
664 AND irp5.balance_sequence = 1
665 AND irp5.user_name = dbi.user_name
666 AND dbi.user_entity_id = arc.user_entity_id
667 AND arc.context1 = p_asgn_action_id
668 AND faic2.archive_item_id = arc.archive_item_id
669 AND ffc2.context_id = faic2.context_id
670 AND ffc2.context_name = 'SOURCE_NUMBER'
671 group by irp5.code,
672 faic2.CONTEXT
673 HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0)
674 OR (faic2.CONTEXT = g_default_clrno
675 and sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) <> 0)
676 --added above condition for Bug 7214056
677 UNION /*Added for Bug 8406456 to report negative amount in code 4030. This deduction doesnt have clearance number */
678 select irp5.code ,
679 '11111111111' clearance_num,
680 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
681 FROM pay_za_irp5_bal_codes irp5,
682 ff_archive_items arc,
683 ff_database_items dbi
684 WHERE irp5.code IN (4030)
685 AND irp5.balance_sequence = 1
686 AND irp5.user_name = dbi.user_name
687 AND dbi.user_entity_id = arc.user_entity_id
688 AND arc.context1 = p_asgn_action_id
689 group by irp5.code
690 HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0);
691
692 /*Cursor to fetch Medical Aid Code Values/Lump Sums for Cross Validation*/
693 CURSOR fetch_med_code_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
694 select irp5.code,
695 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
696 FROM pay_za_irp5_bal_codes irp5,
697 ff_archive_items arc,
698 ff_database_items dbi
699 WHERE ((irp5.code IN (3810, 3813, 4005, 4024, 4025, 4474, 4485, 4486, 4493, 4030) --Added 4474 and 4493 for TYE09
700 AND
701 irp5.balance_sequence = 1
702 )
703 OR -- Added for Bug 8213478
704 ( irp5.code = 4005 AND irp5.balance_sequence = 2
705 )
706 )
707 AND irp5.user_name = dbi.user_name
708 AND dbi.user_entity_id = arc.user_entity_id
709 AND arc.context1 = p_asgn_action_id
710 group by irp5.code;
711
712
713 /* Cursor to fetch all assignment_id and max(assignment_action_id) for given payroll_action_id */
714 CURSOR asgn_for_payroll_action_cur IS
715 SELECT assignment_id,
716 max(assignment_action_id) assignment_action_id -- max assignment_action_id relates to Main Certificate
717 FROM pay_assignment_actions
718 WHERE payroll_action_id = p_payroll_action_id
719 GROUP BY assignment_id ;
720
721 /*Cursor to fetch assignment_action_id corresponding to payroll run for given assignment_id and that tax year*/
722 CURSOR payroll_asgn_ac_id_cur(p_asgn_id pay_assignment_actions.assignment_id%type,
723 p_start_date DATE,
724 p_end_date DATE) IS
725 select paa.assignment_action_id
726 from pay_action_contexts pac,
727 pay_assignment_actions paa,
728 pay_payroll_actions ppa,
729 ff_contexts ffc
730 where paa.assignment_id = p_asgn_id
731 and paa.payroll_action_id = ppa.payroll_action_id
732 and ppa.action_type in ('R', 'Q','B')
733 AND pac.assignment_Action_id = paa.assignment_action_id
734 And pac.context_value = g_default_clrno
735 and ffc.context_name = 'SOURCE_NUMBER'
736 and ffc.context_id = pac.context_id
737 and ppa.effective_date >= p_start_date
738 and ppa.effective_date <= p_end_date;
739
740 /*Cursor the fetch all element names which have missing clearance numbers for given assignment_id*/
741 CURSOR elem_names_cur (p_asgn_ac_id pay_assignment_actions.assignment_action_id%type)IS
742 Select element_name
743 FROM pay_assignment_actions paa,
744 pay_payroll_actions ppa,
745 pay_element_types_f pet,
746 pay_input_values_f piv,
747 pay_run_results prr,
748 pay_run_result_values prv
749 Where paa.assignment_action_id = p_asgn_ac_id
750 and prr.assignment_Action_id = paa.assignment_action_id
751 and pet.element_type_id = prr.element_type_id
752 and piv.element_type_id = pet.element_type_id
753 and piv.name = 'Clearance Number'
754 and prv.run_result_id = prr.run_result_id
755 and prv.input_value_id = piv.input_value_id
756 and prv.RESULT_VALUE = g_default_clrno
757 and ppa.payroll_action_id = paa.payroll_action_id
758 and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
759 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date ;
760
761 /*To fetch PKG balance feed for employee not on pension basis */
762 CURSOR fetch_pkg_balances( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
763 select irp5.code,
764 irp5.full_balance_name bal_name,
765 irp5.balance_type_id bal_type_id,
766 trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))) value
767 FROM pay_za_irp5_bal_codes irp5,
768 ff_archive_items arc,
769 ff_database_items dbi,
770 per_assignment_extra_info paei,
771 pay_assignment_actions paa
772 WHERE dbi.user_name in
773 (
774 'A_ANNUAL_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
775 'A_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
776 'A_USE_OF_MOTOR_VEHICLE_PKG_ASG_TAX_YTD',
777 'A_RIGHT_OF_USE_OF_ASSET_PKG_ASG_TAX_YTD',
778 'A_MEALS_REFRESHMENTS_AND_VOUCHERS_PKG_ASG_TAX_YTD',
779 'A_FREE_OR_CHEAP_ACCOMMODATION_PKG_ASG_TAX_YTD',
780 'A_FREE_OR_CHEAP_SERVICES_PKG_ASG_TAX_YTD',
781 'A_LOW_OR_INTEREST_FREE_LOANS_PKG_ASG_TAX_YTD',
782 'A_ANNUAL_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
783 'A_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
784 'A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
785 'A_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
786 'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
787 'A_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
788 'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
789 'A_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
790 'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
791 'A_TAXABLE_INCOME_PKG_ASG_TAX_YTD',
792 'A_TAXABLE_PENSION_PKG_ASG_TAX_YTD',
793 'A_ANNUAL_BONUS_PKG_ASG_TAX_YTD',
794 'A_TAXABLE_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
795 'A_ANNUAL_COMMISSION_PKG_ASG_TAX_YTD',
796 'A_COMMISSION_PKG_ASG_TAX_YTD',
797 'A_ANNUAL_OVERTIME_PKG_ASG_TAX_YTD',
798 'A_OVERTIME_PKG_ASG_TAX_YTD',
799 'A_ANNUITY_FROM_RETIREMENT_FUND_PKG_ASG_TAX_YTD',
800 'A_PURCHASED_ANNUITY_TAXABLE_PKG_ASG_TAX_YTD',
801 'A_ANNUAL_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
802 'A_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
803 'A_ANNUAL_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
804 'A_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
805 'A_ANNUAL_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
806 'A_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
807 'A_TRAVEL_ALLOWANCE_PKG_ASG_TAX_YTD',
808 'A_TAXABLE_REIMBURSIVE_TRAVEL_PKG_ASG_TAX_YTD',
809 'A_TAXABLE_SUBSISTENCE_PKG_ASG_TAX_YTD',
810 'A_ENTERTAINMENT_ALLOWANCE_PKG_ASG_TAX_YTD',
811 'A_PUBLIC_OFFICE_ALLOWANCE_PKG_ASG_TAX_YTD',
812 'A_TOOL_ALLOWANCE_PKG_ASG_TAX_YTD',
813 'A_COMPUTER_ALLOWANCE_PKG_ASG_TAX_YTD',
814 'A_TELEPHONE_ALLOWANCE_PKG_ASG_TAX_YTD',
815 'A_OTHER_TAXABLE_ALLOWANCE_PKG_ASG_TAX_YTD',
816 'A_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_PKG_ASG_TAX_YTD',
817 'A_EE_BROADBASED_SHARE_PLAN_PKG_ASG_TAX_YTD',
818 'A_OTHER_LUMP_SUM_TAXED_AS_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
819 'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
820 'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD',
821 'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD',
822 'A_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD',
823 'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD'
824 )
825 AND irp5.user_name = dbi.user_name
826 AND dbi.user_entity_id = arc.user_entity_id
827 AND paei.assignment_id = paa.assignment_id
828 AND arc.context1 = p_asgn_action_id
829 AND arc.context1 = paa.assignment_action_id
830 AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis: Fixed Percentage of Specific Income
831 AND paei.information_type = 'ZA_SPECIFIC_INFO'
832 AND pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)) <> '0';
833
834 /*Fetch elements feeding to PKG classification */
835 CURSOR fetch_pkg_ele( p_asgn_action_id pay_assignment_actions.assignment_action_id%type, p_bal_typ_id pay_balance_types.balance_type_id%type) IS
836 SELECT ELEM.element_name element_name,
837 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
838 from pay_balance_feeds_f FEED
839 , pay_run_result_values TARGET
840 , pay_run_results RR
841 , per_time_periods PPTP
842 , per_time_periods BPTP
843 , pay_payroll_actions PACT
844 , pay_assignment_actions ASSACT
845 , pay_payroll_actions BACT
846 , pay_assignment_actions BAL_ASSACT
847 , pay_element_types_f ELEM
848 where BAL_ASSACT.assignment_action_id = p_asgn_action_id
849 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
850 and FEED.input_value_id = TARGET.input_value_id
851 and TARGET.run_result_id = RR.run_result_id
852 and RR.assignment_action_id = ASSACT.assignment_action_id
853 + decode(PPTP.year_number, 0, 0, 0)
854 and ASSACT.payroll_action_id = PACT.payroll_action_id
855 and PACT.effective_date between FEED.effective_start_date
856 and FEED.effective_end_date
857 and BPTP.payroll_id = BACT.payroll_id
858 and PPTP.payroll_id = PACT.payroll_id
859 and nvl(BACT.date_earned,BACT.effective_date)
860 between BPTP.start_date and BPTP.end_date
861 and PACT.date_earned between PPTP.start_date and PPTP.end_date
862 and RR.status in ('P','PA')
866 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
863 AND ELEM.element_type_id = RR.element_type_id
864 and PPTP.prd_information1 = BPTP.prd_information1
865 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
867 AND feed.BALANCE_TYPE_ID = p_bal_typ_id
868 GROUP BY ELEM.element_name
869 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
870
871
872 TYPE med_code_tab IS TABLE OF NUMBER Index BY PLS_INTEGER;
873 med_code_t med_code_tab ;
874
875 TYPE lmpsm_code_tab IS TABLE OF number Index BY VARCHAR2(50); --Index by Code||Tax Directive Number
876 retiremnt_fund_lmpsum lmpsm_code_tab;
877
878
879 l_empno per_all_people_f.employee_number%type;
880 l_assgno per_all_assignments_f.assignment_number%type;
881 l_tax_ytd ff_archive_items.value%TYPE ; -- to save Tax paid by employee during tax year
882 l_site ff_archive_items.value%TYPE ;
883 l_paye ff_archive_items.value%TYPE ;
884 l_msgtext varchar2(2000);
885 l_missing_clrno_flag VARCHAR2(1) ;
886 l_count NUMBER :=0;
887 l_count1 NUMBER :=0;
888 l_65date date;
889 l_65flag NUMBER :=0; --Flag indicating whether employee is greater than 65 yr or not
890 l_3902 NUMBER :=0; --Flag indicating whether 3902/3904 is present for the employee
891 l_tax_dir_num varchar2(50):='';
892 a varchar2(50):='';
893
894 --Changes done for Bug No 6749775 and
895 CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
896 SELECT per.employee_number empno, asgn2.assignment_number assgno,
897 add_months(per.date_of_birth,780) dateb--Added for TYE09
898 FROM pay_assignment_actions paa,
899 per_all_assignments_f asgn2,
900 per_all_people_f per,
901 pay_payroll_actions ppa
902 WHERE paa.assignment_action_id = asgn_ac_id
903 AND ppa.payroll_action_id = paa.payroll_action_id
904 AND asgn2.assignment_id = paa.assignment_id
905 AND per.person_id = asgn2.person_id
906 AND asgn2.effective_start_date =
907 ( select max(paf2.effective_start_date)
908 from per_assignments_f paf2
909 where paf2.effective_start_date <= ppa.effective_date
910 and paf2.assignment_id = asgn2.assignment_id
911 )
912 AND per.effective_start_date =
913 ( select max(per2.effective_start_date)
914 from per_all_people_f per2
915 where per2.effective_start_date <= ppa.effective_date
916 and per2.person_id = per.person_id
917 );
918 --End changes for Bug No 6749775
919
920 /* Cursor to check sanity of 3915 and 4115 codes */
921
922 CURSOR chk_rtrmnt_fnd_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
923 SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value,
924 faic2.CONTEXT Tax_Directive_Number
925 FROM ff_archive_items arc,
926 ff_database_items dbi,
927 pay_za_irp5_bal_codes irp5,
928 ff_archive_item_contexts faic2,
929 ff_contexts ffc2
930 WHERE arc.user_entity_id = dbi.user_entity_id
931 and irp5.user_name = dbi.user_name
932 and arc.context1 in (select ch.assignment_action_id
933 from pay_assignment_actions main
934 , pay_assignment_actions ch
935 where main.assignment_action_id = p_asgn_action_id
936 and ch.payroll_action_id = main.payroll_action_id
937 and ch.assignment_action_id <= main.assignment_action_id
938 AND ch.assignment_id = main.assignment_id)
939 and irp5.code in (3915,4115,3920) --Modified for TYS2010 Bug 8406456
940 and faic2.archive_item_id = arc.archive_item_id
941 and ffc2.context_id = faic2.context_id
942 and ffc2.context_name = 'SOURCE_TEXT'
943 GROUP BY irp5.code,faic2.CONTEXT ;
944
945 /*Added for TYS2010 */
946 CURSOR fetch_lmpsm_code_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
947 SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value,
948 faic2.CONTEXT Tax_Directive_Number
949 FROM ff_archive_items arc,
950 ff_database_items dbi,
951 pay_za_irp5_bal_codes irp5,
952 ff_archive_item_contexts faic2,
953 ff_contexts ffc2
954 WHERE arc.user_entity_id = dbi.user_entity_id
955 and irp5.user_name = dbi.user_name
956 and arc.context1 in (select ch.assignment_action_id
957 from pay_assignment_actions main
958 , pay_assignment_actions ch
959 where main.assignment_action_id = p_asgn_action_id
960 and ch.payroll_action_id = main.payroll_action_id
961 and ch.assignment_action_id <= main.assignment_action_id
962 AND ch.assignment_id = main.assignment_id)
963 and irp5.code in (3902,3904,3920)
964 and irp5.balance_sequence = 3
965 and faic2.archive_item_id = arc.archive_item_id
966 and ffc2.context_id = faic2.context_id
967 and ffc2.context_name = 'SOURCE_TEXT'
968 GROUP BY irp5.code,faic2.CONTEXT ;
969
970 begin
971 retcode := 0;
972 -- hr_utility.trace_on(null,'ZATYEVL');
973 FND_FILE.PUT_LINE(FND_FILE.LOG,'In validate_tye_data');
974 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_payroll_action_id :' || p_payroll_action_id);
975 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside validate_tye_data');
976
977 /*Loop through all Assignments for given payroll_action_id*/
978 FOR asgn IN asgn_for_payroll_action_cur
979 LOOP
983 l_3902:=0;
980 l_count:=0;
981 l_count1:=0;
982 l_65flag:=0;
984 /* Fetch Employee_number */
985 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Action Id : ' || asgn.assignment_action_id);
986 -- FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
987 -- FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
988 FOR emp_num IN emp_number_cur(asgn.assignment_action_id)
989 LOOP
990 l_empno := emp_num.empno ;
991 l_assgno:= emp_num.assgno;
992 l_65date:=emp_num.dateb;
993 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
994 END LOOP;
995
996 --Added for TYE09
997 IF l_65date <= p_tax_yr_end_date then
998 l_65flag:=1;
999 END if;
1000 -- Rule 1) If an employee has paid any Tax during the current tax year,
1001 -- they must have a value in the SITE and/or PAYE balance (SITE_ASG_TAX_YTD or PAYE_ASG_TAX_YTD)
1002 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 1) If Employee has paid tax during current tax year, he should have SITE/PAYE split');
1003 select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
1004 into l_tax_ytd
1005 from ff_archive_items arc,
1006 ff_database_items dbi
1007 where dbi.user_name = 'A_TAX_ASG_TAX_YTD'
1008 and arc.user_entity_id = dbi.user_entity_id
1009 and arc.context1 = asgn.assignment_action_id;
1010
1011 IF l_tax_ytd > 0 THEN
1012 select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
1013 into l_site
1014 from ff_archive_items arc,
1015 ff_database_items dbi
1016 where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
1017 and arc.user_entity_id = dbi.user_entity_id
1018 and arc.context1 = asgn.assignment_action_id;
1019
1020 select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
1021 into l_paye
1022 from ff_archive_items arc,
1023 ff_database_items dbi
1024 where dbi.user_name = 'A_PAYE_ASG_TAX_YTD'
1025 and arc.user_entity_id = dbi.user_entity_id
1026 and arc.context1 = asgn.assignment_action_id;
1027
1028 IF l_site = 0 AND l_paye = 0 THEN
1029 l_count:=1;
1030 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1031 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1032 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1033 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1034 fnd_message.set_name('PAY', 'PY_ZA_NO_SITE_PAYE_SPLIT');
1035 fnd_message.set_token('EMPNO',l_empno);
1036 l_msgtext := fnd_message.get('Y');
1037 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Tax Amount : '||l_tax_ytd) ;
1038 FND_FILE.PUT_LINE(FND_FILE.LOG, ' SITE balance : '||l_site) ;
1039 FND_FILE.PUT_LINE(FND_FILE.LOG, ' PAYE balance : '||l_paye) ;
1040 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
1041 END IF ;
1042 END IF ;
1043
1044 -- Rule 2) Check for Income Balances which may not contain negative amounts
1045 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 2) Check for Income Balances which may not contain negative amounts');
1046 FOR neg_amt_check IN negative_amt_check_cur(asgn.assignment_action_id)
1047 LOOP
1048 if l_count <>1 then
1049 l_count:=1;
1050 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1051 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1052 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1053 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1054 end if;
1055 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
1056 fnd_message.set_token('EMPno',l_empno);
1057 fnd_message.set_token('SARScode',neg_amt_check.code);
1058 l_msgtext := fnd_message.get('Y');
1059 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
1060 END LOOP ;
1061
1062 -- Rule 3) Check Lumpsum balances which may not contain negative amounts
1063 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 3) Check Lumpsum balances which may not contain negative amounts');
1064 FOR lmpsum_bal IN fetch_lmpsm_bal_cur(asgn.assignment_action_id)
1065 LOOP
1066 if l_count <>1 then
1067 l_count:=1;
1068 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1069 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1070 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1071 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1072 end if;
1073 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || g_default_dirno);
1074 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
1075 fnd_message.set_token('EMPno',l_empno);
1076 fnd_message.set_token('SARScode',lmpsum_bal.code);
1077 l_msgtext := fnd_message.get('Y');
1078 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
1079 END LOOP ;
1080 ---- Check for the Lump Sum Balances with Directive Number Other than the Defoult directive number
1081
1082 FOR rec_lmpsm_crt_bal IN get_lmpsm_crt_bal(asgn.assignment_action_id)
1083 loop
1084 if l_count <>1 then
1085 l_count:=1;
1086 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1087 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1088 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1089 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1090 end if;
1091 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_lmpsm_crt_bal.Tax_Directive_Number);
1092 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
1093 fnd_message.set_token('EMPno',l_empno);
1094 fnd_message.set_token('SARScode',rec_lmpsm_crt_bal.code);
1095 l_msgtext := fnd_message.get('Y');
1096 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
1097
1098
1099 END loop;
1100 -- End check for Lump Sum Balances with Directive Number Other than the Defoult directive number
1101 -- Rule 4) Check for Deduction Balances
1102 -- a) may not contain negative amounts
1103 -- b) Clearance number must be entered
1104 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 4) Check for Deduction Balances ') ;
1105 l_missing_clrno_flag := 'N' ;
1106 FOR ded_bal IN fetch_deduction_bal_cur(asgn.assignment_action_id)
1107 LOOP
1108 IF ded_bal.value <0 THEN
1109 if l_count <>1 then
1110 l_count:=1;
1111 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1112 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1113 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1114 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1115 end if;
1116 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
1117 fnd_message.set_token('EMPno',l_empno);
1118 fnd_message.set_token('SARScode',ded_bal.code);
1119 l_msgtext := fnd_message.get('Y');
1120 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
1121 END IF ;
1122 IF ded_bal.clearance_num = g_default_clrno THEN
1123 l_missing_clrno_flag := 'Y' ;
1124 END IF ;
1125 END LOOP ;
1126
1127 -- If clearance number is g_default_clrno,
1128 -- find out the element(s) whose run_result_values contain defaults (99999999999) for 'Clearance' Input value
1129 IF l_missing_clrno_flag = 'Y' THEN
1130 /*Loop through all assignment_action_id for payroll run for given assignment*/
1131 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Clearance Number is default ') ;
1132 FOR paa IN payroll_asgn_ac_id_cur(asgn.assignment_id, p_tax_yr_start_date, p_tax_yr_end_date)
1133 LOOP
1134 /*Loop through all element names for which the employee has missing clearance numbers*/
1135 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'For payroll assignment_action id : '||paa.assignment_action_id) ;
1136 FOR elem_names IN elem_names_cur (paa.assignment_action_id)
1137 LOOP
1138 if l_count <>1 then
1139 l_count:=1;
1140 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1141 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1142 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1143 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1144 end if;
1145 fnd_message.set_name('PAY', 'PA_ZA_ENTER_CLEARANCE_NUM');
1146 fnd_message.set_token('EMPno',l_empno);
1147 fnd_message.set_token('ELEMENTname',elem_names.element_name);
1148 l_msgtext := fnd_message.get('Y');
1149 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
1150 END LOOP ;
1151 END LOOP ;
1152 END IF;
1153
1154 /* Loop through all the PKG balances fed through elements for employee not on PKG structure
1155 For Bug 7264311 */
1156 FOR pkg_bal IN fetch_pkg_balances(asgn.assignment_action_id)
1157 LOOP
1158 if l_count <>1 then
1159 l_count:=1;
1160 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1161 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1162 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1163 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1164 end if;
1165
1166 IF l_count1 <>1 then
1167 fnd_message.set_name('PAY', 'PY_ZA_PKG_BAL_NT_ALLOW');
1168 fnd_message.set_token('EMPno',l_empno);
1169 l_msgtext := fnd_message.get('Y');
1170 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
1171 l_count1:=1;
1172 END IF;
1173
1174 FOR pkg_ele IN fetch_pkg_ele(asgn.assignment_action_id, pkg_bal.bal_type_id)
1175 LOOP
1176 fnd_message.set_name('PAY', 'PY_ZA_ELE_FEED_PKG_BAL');
1177 fnd_message.set_token('ELEMENTname',pkg_ele.element_name);
1178 fnd_message.set_token('BALANCEname',pkg_bal.bal_name);
1179 l_msgtext := fnd_message.get('Y');
1180 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
1181 END LOOP;
1182
1183 END LOOP;
1184
1185 -- Rule 5) Cross validation of Medical Aid Codes
1186 -- initialize table of medical aid codes
1187 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 5) Cross validation of Medical Aid Codes');
1188 med_code_t(3810) := 0 ;
1189 med_code_t(3813) := 0 ;
1190 med_code_t(4005) := 0 ;
1191 med_code_t(4024) := 0 ;
1192 med_code_t(4025) := 0 ;
1193 med_code_t(4485) := 0 ;
1194 med_code_t(4486) := 0 ;
1195 --Added for TYE09
1196 med_code_t(4474) := 0 ;
1197 med_code_t(4493) := 0 ;
1198 --Added for TYS09
1199 med_code_t(4030) := 0 ;
1200
1201 FOR med_code_bal IN fetch_med_code_bal_cur(asgn.assignment_action_id)
1202 LOOP
1203 med_code_t(med_code_bal.code) := med_code_bal.value ;
1204 END LOOP ;
1205
1206 -- Added for TYE09
1207 -- Code 3810 must be less than 4474
1208 IF med_code_t(3810) >= med_code_t(4474) AND (med_code_t(3810) <>0 OR med_code_t(4474) <>0) THEN
1209 if l_count <>1 then
1210 l_count:=1;
1211 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1212 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1213 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1214 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1215 end if;
1216 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3810 must be less than Code 4474.') ;
1217 END IF ;
1218
1219
1220 -- 5a) Code 3813 must be equal to the sum of Codes 4024 and 4485
1221 IF med_code_t(3813) <> (med_code_t(4024) + med_code_t(4485)) THEN
1222 if l_count <>1 then
1223 l_count:=1;
1224 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1225 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1226 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1227 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1228 end if;
1229 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3813 must be equal to the sum of Codes 4024 and 4485') ;
1230 END IF ;
1231 -- 5b) Code 4005 must be greater than zero if there is a value in 3810 or 4025
1232 IF (med_code_t(3810) <>0) OR (med_code_t(4025) <>0) THEN
1233 IF med_code_t(4005) <=0 THEN
1234 if l_count <>1 then
1235 l_count:=1;
1236 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1237 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1238 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1239 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1240 end if;
1241 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4005 must be greater than zero if Code 3810 or 4025 has a value') ;
1242 END IF;
1243 END IF ;
1244 -- 5d) Code 4025 may not be greater than 4005
1245 --Modified for TYE09 so that 4025 may not be greater than 4005 except when employee >=65 yr
1246 IF med_code_t(4025) > med_code_t(4005) AND l_65flag=0 THEN
1247 if l_count <>1 then
1248 l_count:=1;
1249 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1250 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1251 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1252 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1253 end if;
1254 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4025 must not be greater than Code 4005 except when the employee is 65 years or older.') ;
1255 END IF ;
1256
1257 --Added for TYE09
1258 --Code 4025 may not be greater than 4486 except when employee >=65 yr
1259 IF med_code_t(4025) > med_code_t(4486) AND l_65flag=0 THEN
1260 if l_count <>1 then
1261 l_count:=1;
1262 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1263 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1265 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1266 end if;
1267 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4025 must not be greater than Code 4486 except when the employee is 65 years or older.') ;
1268 END IF ;
1269
1270
1271 --Added for TYE09
1272 IF med_code_t(3810) <>0 THEN
1273 IF med_code_t(4474) = 0 THEN
1274 if l_count <>1 then
1275 l_count:=1;
1276 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1277 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1278 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1279 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1280 end if;
1281 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4474 must be greater than zero if Code 3810 has a value.') ;
1282 END IF ;
1283 END IF ;
1284
1285
1286 -- Added for TYE09
1287 --Code 4474 and 3810 not allowed when 4493 is present
1288 IF med_code_t(4493) <> 0 THEN
1289 IF med_code_t(4474) <>0 OR med_code_t(3810) <>0 THEN
1290 if l_count <>1 then
1291 l_count:=1;
1292 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1293 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1294 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1295 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1296 end if;
1297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4474 or Code 3810 not allowed if Code 4493 is specified.') ;
1298 END IF ;
1299 END IF ;
1300
1301 -- 5c) Code 4486 must be greater than zero if there is a value in 4025
1302 IF med_code_t(4025) <>0 THEN
1303 IF med_code_t(4486) <= 0 THEN
1304 if l_count <>1 then
1305 l_count:=1;
1306 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1307 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1308 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1309 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1310 end if;
1311 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4486 must be greater than zero if Code 4025 has a value') ;
1312 END IF ;
1313 END IF ;
1314
1315
1316 --Added for TYS2010
1317
1318 FOR rec_lmpsm_bal IN fetch_lmpsm_code_bal_cur(asgn.assignment_action_id)
1319 loop
1320 IF (rec_lmpsm_bal.code = 3920 and length(trunc(pay_za_eoy_val.decimal_character_conversion(rec_lmpsm_bal.value))) > 11) THEN
1321 if l_count <>1 then
1322 l_count:=1;
1323 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1324 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1325 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1326 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1327 end if;
1328 if rec_lmpsm_bal.Tax_Directive_Number <> g_default_dirno then
1329 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_lmpsm_bal.Tax_Directive_Number);
1330 else
1331 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || g_default_dirno);
1332 end if;
1333 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3920 must not exceed 11 digits.') ;
1334 END IF ;
1335
1336 /*Check for l_3902 is placed for if the message is printed once, say for code 3902,
1337 must not display the message again if code 3904 is present */
1338 IF (l_3902 = 0 ) then
1339 IF ((rec_lmpsm_bal.code = 3902 and rec_lmpsm_bal.value <>0) OR (rec_lmpsm_bal.code = 3904 and rec_lmpsm_bal.value <>0))
1340 and to_number(to_char(p_tax_yr_end_date,'YYYY')) > 2009 THEN
1341 l_3902 :=1;
1342 if l_count <>1 then
1343 l_count:=1;
1344 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1345 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1346 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1347 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1348 end if;
1349 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Codes 3902 and 3904 are obsolete from 01-Mar-2009.') ;
1350 --Codes 3902 and 3902 must not be used after tax year 2009
1351 end if;
1352 end if;
1353 END loop;
1354
1355 --Added for TYS2010
1356 IF (length(trunc(pay_za_eoy_val.decimal_character_conversion(med_code_t(4030)))) > 11) THEN
1357 if l_count <>1 then
1358 l_count:=1;
1359 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1360 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1361 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1362 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1363 end if;
1364 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4030 must not exceed 11 digits.') ;
1365 END IF ;
1366
1367
1368
1369 -- Retirement Fund Lumpsum PAYE balance (4115) should not be present if
1370 -- Retirement Fund Lumpsum (3915) or retiremnt_fund_lmpsum(3920) itself is not present
1371 /* retiremnt_fund_lmpsum(3915) := 0 ;
1372 retiremnt_fund_lmpsum(4115) := 0 ;
1373 retiremnt_fund_lmpsum(3920) := 0 ; */
1374 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 6) Cross Validation of codes 3915 and 4115 ') ;
1375 FOR chk_rtrmnt_fnd IN chk_rtrmnt_fnd_cur(asgn.assignment_action_id)
1376 LOOP
1377 retiremnt_fund_lmpsum(chk_rtrmnt_fnd.code||chk_rtrmnt_fnd.Tax_Directive_Number) := chk_rtrmnt_fnd.value ;
1378 end loop;
1379
1380 a:=retiremnt_fund_lmpsum.first();
1381 FOR i in 1..retiremnt_fund_lmpsum.count
1382 loop
1383
1384 hr_utility.set_location('a:'||a,10);
1385 hr_utility.set_location('substr(a,1,4):'||substr(a,1,4),10);
1386 hr_utility.set_location('retiremnt_fund_lmpsum(a):'||retiremnt_fund_lmpsum(a),10);
1387
1388 IF (substr(a,1,4)=4115 and retiremnt_fund_lmpsum(a) <>0 ) THEN
1389 l_tax_dir_num := substr(a,5);
1390 hr_utility.set_location('l_tax_dir_num:'||l_tax_dir_num,10);
1391
1392 if ((retiremnt_fund_lmpsum.exists('3920'||l_tax_dir_num) and retiremnt_fund_lmpsum('3920'||l_tax_dir_num) <> 0)
1393 OR (retiremnt_fund_lmpsum.exists('3915'||l_tax_dir_num) and retiremnt_fund_lmpsum('3915'||l_tax_dir_num) <> 0)) then
1394 null;
1395 else
1396 if l_count <>1 then
1397 l_count:=1;
1398 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1399 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1400 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1401 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1402 end if;
1403 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || l_tax_dir_num);
1404 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4115 must not be present if Codes 3915 or 3920 are not present.');
1405 end if;
1406 END IF ;
1407 a:=retiremnt_fund_lmpsum.next(a);
1408 end loop;
1409 retiremnt_fund_lmpsum.delete();
1410
1411
1412
1413 END LOOP ; -- End of assignment Loop
1414 FND_FILE.PUT_LINE(FND_FILE.LOG,'End of log file');
1415 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1416 -- hr_utility.trace_off;
1417 EXCEPTION
1418 WHEN OTHERS then
1419 errbuf := substr(SQLERRM,1,255);
1420 retcode := sqlcode;
1421 end VALIDATE_TYE_DATA;
1422
1423 ----------------------------------------------------------------------------
1424 --Validate Character Set
1425 ----------------------------------------------------------------------------
1426 function validate_character_set (p_input_value in varchar2
1427 , p_mode in varchar2 ) return boolean as
1428
1429 l_result boolean := true ;
1430 l_translated varchar2(1024);
1431 l_num varchar2(10) := '0123456789' ;
1432
1433 l_invalid_in_alphanum varchar2(100):= '~`!@#$%^&*()_+=|\[]{}<>":;?/.';
1434 l_invalid_in_alpha varchar2(100):= '~`!@#$%^&*()_+=|\[]{}<>":;?/.0123456789';
1435 begin
1436 hr_utility.trace('Validating value : '||p_input_value||' as '||p_mode);
1437 if p_mode = 'NUMERIC' then
1438 l_translated := translate (p_input_value
1439 , '~' || l_num
1440 , '~');
1441 if l_translated is not null then
1442 l_result := false;
1443 end if ;
1444 elsif p_mode = 'ALPHA' then
1445 l_translated := translate (p_input_value
1446 , l_invalid_in_alpha
1447 , lpad('~',length(l_invalid_in_alpha),'~'));
1448 if instr(l_translated,'~') >0 then
1449 l_result := false;
1450 end if ;
1451 elsif p_mode = 'ALPHANUM' then
1452 l_translated := translate (p_input_value
1453 , l_invalid_in_alphanum
1454 , lpad('~',length(l_invalid_in_alphanum),'~'));
1455 if instr(l_translated,'~') >0 then
1456 l_result := false;
1457 end if ;
1458 elsif p_mode = 'FREETEXT' then
1459 null;
1460 -- we will not validate for freetext
1461 end if;
1462
1463 hr_utility.trace('l_translated='||l_translated);
1464 return l_result ;
1465 end validate_character_set;
1466
1467
1468 ----------------------------------------------------------------------------
1469 -- Validate Email ID
1470 ----------------------------------------------------------------------------
1471 function validate_email_id (p_email_id varchar2)
1472 return boolean
1473 is
1474 l_validate_flag boolean := true ;
1475 begin
1476 if instr(p_email_id,'@') <= 0 then
1477 l_validate_flag := false ;
1478 elsif instr(p_email_id,'.') <= 0 then
1479 l_validate_flag := false ;
1480 end if ;
1481 return l_validate_flag;
1482 end validate_email_id ;
1483
1484 ---------------------------------------------------------------------------
1485 -- Validate Address
1486 ---------------------------------------------------------------------------
1487 procedure validate_address (P_STYLE in varchar2
1488 ,P_TAX_YEAR in number
1489 ,P_ADDRESS_TYPE in varchar2 default null
1490 ,P_PRIMARY_FLAG in varchar2 default null
1491 ,P_UNIT_NUMBER in varchar2 default null
1492 ,P_COMPLEX in varchar2 default null
1493 ,P_STREET_NUMBER in varchar2 default null
1494 ,P_STREET_NAME in varchar2 default null
1498 ,P_SAME_AS_RES_ADD in varchar2 default null
1495 ,P_SUBURB_DISTRICT in varchar2 default null
1496 ,P_TOWN_OR_CITY in varchar2 default null
1497 ,P_POSTAL_CODE in varchar2 default null
1499 ,P_ADDRESS_LINE1 in varchar2 default null
1500 ,P_ADDRESS_LINE2 in varchar2 default null
1501 ,P_ADDRESS_LINE3 in varchar2 default null
1502 ,P_NATURE in varchar2 default null
1503 ,P_MSG_TXT in out nocopy msgtext_tab
1504 ,P_WARN_TXT in out nocopy msgtext_tab
1505 ) as
1506 l_location varchar2(50);
1507 l_msg_count number(5):=0;
1508 l_warn_count number(5):=0;
1509 begin
1510
1511 hr_utility.trace('P_STYLE:'||P_STYLE);
1512 hr_utility.trace('P_TAX_YEAR:'||P_TAX_YEAR);
1513 hr_utility.trace('P_ADDRESS_TYPE:'||P_ADDRESS_TYPE);
1514 hr_utility.trace('P_PRIMARY_FLAG:'||P_PRIMARY_FLAG);
1515 hr_utility.trace('P_UNIT_NUMBER:'||P_UNIT_NUMBER);
1516 hr_utility.trace('P_COMPLEX:'||P_COMPLEX);
1517 hr_utility.trace('P_STREET_NUMBER:'||P_STREET_NUMBER);
1518 hr_utility.trace('P_STREET_NAME:'||P_STREET_NAME);
1519 hr_utility.trace('P_SUBURB_DISTRICT:'||P_SUBURB_DISTRICT);
1520 hr_utility.trace('P_TOWN_OR_CITY:'||P_TOWN_OR_CITY);
1521 hr_utility.trace('P_POSTAL_CODE:'||P_POSTAL_CODE);
1522 hr_utility.trace('P_SAME_AS_RES_ADD:'||P_SAME_AS_RES_ADD);
1523 hr_utility.trace('P_ADDRESS_LINE1:'||P_ADDRESS_LINE1);
1524 hr_utility.trace('P_ADDRESS_LINE2:'||P_ADDRESS_LINE2);
1525 hr_utility.trace('P_ADDRESS_LINE3:'||P_ADDRESS_LINE3);
1526 hr_utility.trace('P_NATURE:'||P_NATURE);
1527
1528 --Check which address is passed
1529 if P_STYLE = 'ZA_GRE' then
1530 l_location := 'ZA Tax File Information';
1531 elsif P_STYLE = 'ZA_SARS' and P_ADDRESS_TYPE='ZA_BUS' then
1532 l_location := 'the Business Address';
1533 elsif P_STYLE = 'ZA_SARS' and P_ADDRESS_TYPE='ZA_RES' then
1534 l_location := 'the Residential Address';
1535 else
1536 l_location := 'the Postal Address';
1537 end if;
1538
1539 l_msg_count := P_MSG_TXT.count;
1540 l_warn_count := P_WARN_TXT.count;
1541
1542 if P_STYLE in ('ZA_GRE','ZA_SARS') then
1543 -- Validate Unit Number
1544 hr_utility.set_location('Validating Unit Number',10);
1545 if validate_character_set(P_UNIT_NUMBER,'ALPHANUM') = false then
1546 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1547 fnd_message.set_token('FIELD', 'Unit Number in '||l_location);
1548 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1549 l_msg_count := l_msg_count + 1;
1550 end if;
1551
1552 --Validate Complex
1553 hr_utility.set_location('Validating Complex',20);
1554 if validate_character_set(P_COMPLEX,'FREETEXT') = false then
1555 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1556 fnd_message.set_token('FIELD', 'Complex in '||l_location);
1557 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1558 l_msg_count := l_msg_count + 1;
1559 end if;
1560
1561 --Validate Street Number
1562 hr_utility.set_location('Validating Street Number',25);
1563 if validate_character_set(P_STREET_NUMBER,'ALPHANUM') = false then
1564 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1565 fnd_message.set_token('FIELD', 'Street Number in '||l_location);
1566 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1567 l_msg_count := l_msg_count + 1;
1568 end if;
1569
1570 --For residential, it is mandatory irrespective of nature
1571 --Validate Street or Name of Farm
1572 hr_utility.set_location('Validating Street or Name of Farm',30);
1573 if P_STREET_NAME is null and nvl(P_NATURE,'X') <> 'N' then
1574 fnd_message.set_name('PER', 'HR_ZA_ENTER_STREET_NAME_FARM');
1575 fnd_message.set_token('LOCATION', l_location);
1576 if P_STYLE = 'ZA_GRE' OR (P_STYLE='ZA_SARS' and P_TAX_YEAR <>2010) then
1577 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1578 l_msg_count := l_msg_count + 1;
1579 else
1580 --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
1581 p_warn_txt(l_warn_count):=fnd_message.get('Y');
1582 l_warn_count := l_warn_count + 1;
1583 end if;
1584 elsif validate_character_set(P_STREET_NAME,'FREETEXT') = false then
1585 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1586 fnd_message.set_token('FIELD', 'Street or Name of Farm in '||l_location);
1587 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1588 l_msg_count := l_msg_count + 1;
1589 end if;
1590
1591 --Validate Suburb or District
1592 hr_utility.set_location('Validating Suburb/District',35);
1593 if validate_character_set(P_SUBURB_DISTRICT,'FREETEXT') = false then
1594 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1595 fnd_message.set_token('FIELD', 'Suburb or District in '||l_location);
1596 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1597 l_msg_count := l_msg_count + 1;
1598 end if;
1599
1600 --Validate City or Town
1601 hr_utility.set_location('Validating City/Town',40);
1602 if validate_character_set(P_TOWN_OR_CITY,'FREETEXT') = false then
1603 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1604 fnd_message.set_token('FIELD', 'City or Town in '||l_location);
1605 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1606 l_msg_count := l_msg_count + 1;
1607 end if;
1608
1609 if P_SUBURB_DISTRICT is null and P_TOWN_OR_CITY is null and nvl(P_NATURE,'X') <> 'N' then
1610 fnd_message.set_name('PER', 'HR_ZA_ENTER_DISTRICT_OR_TOWN');
1611 fnd_message.set_token('LOCATION', l_location);
1612 if P_STYLE = 'ZA_GRE' OR (P_STYLE='ZA_SARS' and P_TAX_YEAR <>2010) then
1613 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1614 l_msg_count := l_msg_count + 1;
1615 else
1616 --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
1617 p_warn_txt(l_warn_count):=fnd_message.get('Y');
1618 l_warn_count := l_warn_count + 1;
1619 end if;
1620 end if ;
1621
1622 --Validate Postal Code
1623 hr_utility.set_location('Validating Postal Code',50);
1624 if P_POSTAL_CODE is null and nvl(P_NATURE,'X') <> 'N' then
1625 fnd_message.set_name('PER', 'HR_ZA_NEW_ENTER_POSTAL_CODE');
1626 fnd_message.set_token('LOCATION', l_location);
1627 if P_STYLE = 'ZA_GRE' OR (P_STYLE='ZA_SARS' and P_TAX_YEAR <>2010) then
1628 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1629 l_msg_count := l_msg_count + 1;
1630 else
1631 --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
1632 p_warn_txt(l_warn_count):=fnd_message.get('Y');
1633 l_warn_count := l_warn_count + 1;
1634 end if;
1635 else
1636 if P_STYLE='ZA_GRE' and length(P_POSTAL_CODE) <> 4 then
1637 hr_utility.set_location('Invalid postal code',50);
1638 fnd_message.set_name('PER', 'HR_ZA_INVALID_LENGTH');
1639 fnd_message.set_token('FIELD', 'Postal Code in '||l_location );
1640 fnd_message.set_token('LENGTH', 4);
1641 fnd_message.set_token('UNITS', 'digits');
1642 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1643 l_msg_count := l_msg_count + 1;
1644 end if;
1645 if validate_character_set(P_POSTAL_CODE,'ALPHANUM') = false then
1646 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1647 fnd_message.set_token('FIELD', 'Postal Code in '||l_location);
1648 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1649 l_msg_count := l_msg_count + 1;
1650 end if;
1651 end if;
1652
1653 elsif P_STYLE ='ZA' then
1654 --Validate Address Line1
1655 if P_ADDRESS_LINE1 is null then
1656 fnd_message.set_name('PER', 'HR_ZA_NEW_ENTER_ADDRESS_LINE1');
1657 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1658 l_msg_count := l_msg_count + 1;
1659 elsif validate_character_set(P_ADDRESS_LINE1,'FREETEXT') = false then
1660 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1661 fnd_message.set_token('FIELD', 'Address Line1 in '||l_location);
1662 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1663 l_msg_count := l_msg_count + 1;
1664 end if;
1665
1666 --Validate address line2
1667 if validate_character_set(P_ADDRESS_LINE2,'FREETEXT') = false then
1668 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1669 fnd_message.set_token('FIELD', 'Address Line2 in '||l_location);
1670 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1671 l_msg_count := l_msg_count + 1;
1672 end if;
1673
1674 --Validate address line3
1675 if validate_character_set(P_ADDRESS_LINE3,'FREETEXT') = false then
1676 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1677 fnd_message.set_token('FIELD', 'Address Line3 in '||l_location);
1678 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1679 l_msg_count := l_msg_count + 1;
1680 end if;
1681
1682 --Validate postal code
1683 if P_POSTAL_CODE is null then
1684 fnd_message.set_name('PER', 'HR_ZA_NEW_ENTER_POSTAL_CODE');
1685 fnd_message.set_token('LOCATION', l_location);
1686 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1687 l_msg_count := l_msg_count + 1;
1688 end if;
1689 if validate_character_set(P_POSTAL_CODE,'ALPHANUM') = false then
1690 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1691 fnd_message.set_token('FIELD', 'Postal Code in '||l_location);
1692 p_msg_txt(l_msg_count):=fnd_message.get('Y');
1693 l_msg_count := l_msg_count + 1;
1694 end if;
1695 end if;
1696
1697 end validate_address;
1698
1699 -- -----------------------------------------------------------------------------
1700 -- Get the correct characterset for XML generation
1701 -- -----------------------------------------------------------------------------
1702 --
1703 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
1704 CURSOR csr_get_iana_charset IS
1705 SELECT tag
1706 FROM fnd_lookup_values
1707 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1708 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
1709 INSTR(USERENV('LANGUAGE'), '.') + 1)
1710 AND language = 'US';
1711 --
1712 lv_iana_charset fnd_lookup_values.tag%type;
1713 BEGIN
1714 OPEN csr_get_iana_charset;
1715 FETCH csr_get_iana_charset INTO lv_iana_charset;
1716 CLOSE csr_get_iana_charset;
1717 RETURN (lv_iana_charset);
1718 END get_IANA_charset;
1719
1720 PROCEDURE write_to_clob (p_clob OUT NOCOPY CLOB) IS
1721
1722 -- l_xml_element_template0 VARCHAR2(20) := '<TAG>VALUE</TAG>';
1723 -- l_xml_element_template1 VARCHAR2(30) := '<TAG><![CDATA[VALUE]]></TAG>';
1724 -- l_xml_element_template2 VARCHAR2(10) := '<TAG>';
1725 -- l_xml_element_template3 VARCHAR2(10) := '</TAG>';
1726 l_str1 VARCHAR2(80) ;
1727 l_str2 VARCHAR2(20) := '</EOY> </ROOT>';
1728 l_xml_element VARCHAR2(800);
1729 l_clob CLOB;
1730 --
1731 BEGIN
1732
1733 l_str1 := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?>' ;
1734
1735 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
1736 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
1737 --
1738 dbms_lob.writeappend(l_clob, LENGTH(l_str1), l_str1);
1739 --
1740 IF g_xml_element_table.COUNT > 0 THEN
1741 --
1742 FOR table_counter IN g_xml_element_table.FIRST .. g_xml_element_table.LAST LOOP
1743 --
1744 IF g_xml_element_table(table_counter).tagvalue = '_START_' THEN
1745 l_xml_element := '<' || g_xml_element_table(table_counter).tagname || '>';
1746 ELSIF g_xml_element_table(table_counter).tagvalue = '_END_' THEN
1747 l_xml_element := '</' || g_xml_element_table(table_counter).tagname || '>';
1748 ELSIF g_xml_element_table(table_counter).tagvalue = '_COMMENT_' THEN
1749 l_xml_element := '<!-- ' || g_xml_element_table(table_counter).tagname || ' -->';
1750 ELSE
1751 l_xml_element := '<' || g_xml_element_table(table_counter).tagname ||
1752 '><![CDATA[' || g_xml_element_table(table_counter).tagvalue ||
1753 ']]></' || g_xml_element_table(table_counter).tagname || '>';
1754 END IF;
1755 --
1756 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
1757 --
1758 END LOOP;
1759 --
1760 END IF;
1761
1762 p_clob := l_clob;
1763 --
1764 EXCEPTION
1765 WHEN OTHERS THEN
1766 --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1767 hr_utility.set_location(sqlerrm(sqlcode),110);
1768 --
1769 END write_to_clob;
1770
1771 procedure get_tyev_parameters
1772 (P_LEGAL_ENTITY number,
1773 P_PAYROLL_ID number,
1774 P_TAX_YEAR varchar2,
1775 P_PERIOD_RECON varchar2, -- 9877034 fix
1776 P_CERT_TYPE varchar2,
1777 P_ASG_SET_ID number,
1778 P_PERSON_ID number,
1779 P_TEST_RUN varchar2,
1780 P_TAX_YEAR_END date,
1781 P_SORT_ORDER1 varchar2,
1782 P_SORT_ORDER2 varchar2,
1783 P_SORT_ORDER3 varchar2
1784 -- P_ASG_SET_WHERE varchar2,
1785 -- P_SORT_ORDER_CLAUSE varchar2
1786 )
1787 as
1788 CURSOR get_asg_set_status(c_asg_set_id hr_assignment_sets.assignment_set_id%TYPE) IS
1789 SELECT include_or_exclude
1790 FROM hr_assignment_set_amendments hasa
1791 WHERE hasa.assignment_set_id = c_asg_set_id;
1792
1793 l_ass_set_name varchar2(80);
1794 l_set_flag varchar2(1);
1795 --g_asg_set_where varchar2(500);
1796 l_person_name varchar2(40);
1797 l_test_run varchar2(4);
1798 l_cert_type varchar2(12);
1799 --g_sort_order_clause varchar2(500);
1800 l_sort_order1 varchar2(30);
1801 l_sort_order2 varchar2(30);
1802 l_sort_order3 varchar2(30);
1803 l_legal_entity varchar2(240);
1804 l_payroll_name pay_all_payrolls_f.payroll_name%type;
1805 l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_TYEV_PARAMETERS';
1806 begin
1807 -- Retrieve the Report Parameter Information
1808
1809 hr_utility.set_location('Entering '||l_proc,10);
1810
1811 --Legal Entity Name
1812 select name
1813 into l_legal_entity
1814 from hr_all_organization_units
1815 where organization_id=P_LEGAL_ENTITY;
1816
1817 --Certificate Type
1818 select meaning
1819 into l_cert_type
1820 from hr_lookups
1821 where lookup_type='ZA_TAX_CERTIFICATES' and lookup_code=P_CERT_TYPE;
1822
1826
1823 hr_utility.set_location('l_legal_entity: '||l_legal_entity,10);
1824 hr_utility.set_location('l_cert_type: '||l_cert_type,10);
1825
1827 --Payroll Name
1828 if P_PAYROLL_ID is not null
1829 then
1830 select ppf.payroll_name
1831 into l_payroll_name
1832 from pay_payrolls_f ppf
1833 where ppf.payroll_id=P_PAYROLL_ID
1834 and ppf.effective_start_date =
1835 (select max(effective_start_date)
1836 from pay_all_payrolls_f ppf1
1837 where ppf1.payroll_id=ppf.payroll_id
1838 and P_TAX_YEAR_END between ppf1.effective_start_date and ppf1.effective_end_date);
1839 end if;
1840
1841 hr_utility.set_location('l_payroll_name: '||l_payroll_name,10);
1842
1843 --Assignment Set Info
1844 if P_ASG_SET_ID is not null
1845 then
1846 select assignment_set_name into l_ass_set_name
1847 from hr_assignment_sets
1848 where assignment_set_id=P_ASG_SET_ID;
1849
1850 OPEN get_asg_set_status(P_ASG_SET_ID);
1851 FETCH get_asg_set_status INTO l_set_flag;
1852
1853 IF l_set_flag = 'E' THEN -- if EXCLUDE
1854 g_asg_set_where := ' AND ass.assignment_id NOT IN ( SELECT hasa.assignment_id FROM hr_assignment_set_amendments hasa WHERE hasa.assignment_set_id = ' || P_ASG_SET_ID || ' AND hasa.assignment_id = ass.assignment_id) ';
1855
1856 ELSIF l_set_flag = 'I' THEN -- if INCLUDE
1857 g_asg_set_where := ' AND ass.assignment_id IN ( SELECT hasa.assignment_id FROM hr_assignment_set_amendments hasa WHERE hasa.assignment_set_id = ' || P_ASG_SET_ID || ' AND hasa.assignment_id = ass.assignment_id) ';
1858 ELSE -- Select all assignments assigned to the payroll associated with this assignment set
1859 g_asg_set_where := ' AND 1= 1 ';
1860 END IF;
1861
1862 CLOSE get_asg_set_status;
1863 end if;
1864
1865 if g_asg_set_where is null then
1866 g_asg_set_where := ' AND 1= 1 ';
1867 end if;
1868
1869 hr_utility.set_location('Retrieved Assignment Set Info',10);
1870
1871 --Employee Name Info
1872 if P_PERSON_ID is not null then
1873 select substr(per.full_name,1,40)
1874 into l_person_name
1875 from per_all_people_f per
1876 where person_id=P_PERSON_ID
1877 and per.effective_start_date = ( select max(effective_start_date) from per_all_people_f per1
1878 where per.person_id=per1.person_id
1879 and P_TAX_YEAR_END between per1.effective_start_date and per1.effective_end_date);
1880 end if;
1881
1882 --Test Run Parameters
1883 if P_TEST_RUN is not null then
1884 select meaning
1885 into l_test_run
1886 from fnd_lookups
1887 where lookup_type='YES_NO' and lookup_code=P_TEST_RUN;
1888 else
1889 l_test_run:='N';
1890 end if;
1891
1892 hr_utility.set_location('l_person_name: '||l_person_name,10);
1893 hr_utility.set_location('l_test_run: '||l_test_run,10);
1894
1895
1896 --SORT ORDER parameters
1897 if p_sort_order1 is not null or p_sort_order2 is not null or p_sort_order3 is not null then
1898 g_sort_order_clause:=' order by ';
1899
1900 hr_utility.set_location('Order by clause present',15);
1901 -- Append first sort order
1902 if p_sort_order1 = '3' then
1903 g_sort_order_clause := g_sort_order_clause || ' substr(per.full_name,1,40), ';
1904 l_sort_order1:='Employee Name';
1905 elsif p_sort_order1 = '4' then
1906 g_sort_order_clause := g_sort_order_clause || ' lpad(per.employee_number, 30, ''0''), ';
1907 l_sort_order1:='Employee Number';
1908 elsif p_sort_order1 = '5' then
1909 g_sort_order_clause := g_sort_order_clause || ' lpad(ass.assignment_number, 30, ''0''), ';
1910 l_sort_order1:='Assignment Number';
1911 end if;
1912
1913 -- Append second sort order
1914 if p_sort_order2 = '3' then
1915 g_sort_order_clause := g_sort_order_clause || ' substr(per.full_name,1,40),';
1916 l_sort_order2:='Employee Name';
1917 elsif p_sort_order2 = '4' then
1918 g_sort_order_clause := g_sort_order_clause || ' lpad(per.employee_number, 30, ''0''),';
1919 l_sort_order2:='Employee Number';
1920 elsif p_sort_order2 = '5' then
1921 g_sort_order_clause := g_sort_order_clause || ' lpad(ass.assignment_number, 30, ''0''),';
1922 l_sort_order2:='Assignment Number';
1923 end if;
1924
1925 -- Append third sort order
1926 if p_sort_order3 = '3' then
1927 g_sort_order_clause := g_sort_order_clause || ' substr(per.full_name,1,40),';
1928 l_sort_order3:='Employee Name';
1929 elsif p_sort_order3 = '4' then
1930 g_sort_order_clause := g_sort_order_clause || ' lpad(per.employee_number, 30, ''0''),';
1931 l_sort_order3:='Employee Number';
1932 elsif p_sort_order3 = '5' then
1933 g_sort_order_clause := g_sort_order_clause || ' lpad(ass.assignment_number, 30, ''0''),';
1934 l_sort_order3:='Assignment Number';
1935 end if;
1936
1937 g_sort_order_clause:=g_sort_order_clause || ' ass.assignment_id';
1938
1939 end if;
1940
1941 hr_utility.set_location('Retrieved Sort Order Info',20);
1942
1943 hr_utility.set_location('Populating XML Table',90);
1944
1945 --Build XML for report parameters
1946 g_xml_element_table(g_xml_element_count).tagname := 'LEGAL_ENTITY_NAME';
1947 g_xml_element_table(g_xml_element_count).tagvalue := l_legal_entity;
1948 g_xml_element_count := g_xml_element_count + 1;
1949
1950 g_xml_element_table(g_xml_element_count).tagname := 'TAX_YEAR';
1951 g_xml_element_table(g_xml_element_count).tagvalue := p_tax_year;
1952 g_xml_element_count := g_xml_element_count + 1;
1953
1954 -- 9877034 fix starts
1955 g_xml_element_table(g_xml_element_count).tagname := 'PERIOD_RECON';
1956 g_xml_element_table(g_xml_element_count).tagvalue := P_PERIOD_RECON;
1957 g_xml_element_count := g_xml_element_count + 1;
1958 -- 9877034 fix ends
1959
1960 g_xml_element_table(g_xml_element_count).tagname := 'CERT_TYPE';
1961 g_xml_element_table(g_xml_element_count).tagvalue := l_cert_type;
1962 g_xml_element_count := g_xml_element_count + 1;
1963
1964 g_xml_element_table(g_xml_element_count).tagname := 'PAYROLL_NAME';
1965 g_xml_element_table(g_xml_element_count).tagvalue := l_payroll_name;
1966 g_xml_element_count := g_xml_element_count + 1;
1967
1968 g_xml_element_table(g_xml_element_count).tagname := 'ASSIGN_SET';
1969 g_xml_element_table(g_xml_element_count).tagvalue := l_ass_set_name;
1970 g_xml_element_count := g_xml_element_count + 1;
1971
1972 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NAME';
1973 g_xml_element_table(g_xml_element_count).tagvalue := l_person_name;
1974 g_xml_element_count := g_xml_element_count + 1;
1975
1976 g_xml_element_table(g_xml_element_count).tagname := 'TEST_RUN';
1977 g_xml_element_table(g_xml_element_count).tagvalue := l_test_run;
1978 g_xml_element_count := g_xml_element_count + 1;
1979
1980 g_xml_element_table(g_xml_element_count).tagname := 'SORT1';
1981 g_xml_element_table(g_xml_element_count).tagvalue := l_sort_order1;
1982 g_xml_element_count := g_xml_element_count + 1;
1983
1984 g_xml_element_table(g_xml_element_count).tagname := 'SORT2';
1985 g_xml_element_table(g_xml_element_count).tagvalue := l_sort_order2;
1986 g_xml_element_count := g_xml_element_count + 1;
1987
1988 g_xml_element_table(g_xml_element_count).tagname := 'SORT3';
1989 g_xml_element_table(g_xml_element_count).tagvalue := l_sort_order3;
1990 g_xml_element_count := g_xml_element_count + 1;
1991 --END XML for Report parameters
1992
1993 g_xml_element_table(g_xml_element_count).tagname := 'End Parameter Information';
1994 g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
1995 g_xml_element_count := g_xml_element_count + 1;
1996
1997 hr_utility.set_location('Leaving '||l_proc,20);
1998
1999 end get_tyev_parameters;
2000
2001 procedure get_employer_info
2002 (p_business_group_id number,
2003 p_legal_entity number,
2004 p_tax_year number,
2005 p_er_info OUT nocopy varchar2
2006 )
2007 is
2008 --Retrieve 'ZA Tax Information' (Context ZA_LEGAL_ENTITY) and Location Address details
2009 cursor csr_tax_info is
2010 select hoi.org_information1 er_trade_name, -- Employer Trading or Other Name (Code 2010)
2011 hoi.org_information3 paye_ref_num, -- PAYE Ref Num (Code 2020)
2012 pay_za_eoy_val.modulus_10_test(hoi.org_information3) paye_ref_num_mod,
2013 upper(hoi.org_information12) sdl_ref_num, -- SDL Num (Code 2022)
2014 upper(hoi.org_information6) uif_ref_num, -- UIF Ref Num (Code 2024)
2015 hoi.org_information13 er_trade_class -- Employer Trade Classification (Code 2035)
2016 from hr_organization_information hoi
2017 where hoi.organization_id = p_legal_entity
2018 and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
2019
2020 -- Retrieve 'ZA GRE Tax File Creator Info' (Context ZA_GRE_TAX_FILE_ENTITY) from Legal Entity level
2021 cursor csr_tax_file_creator_inf is
2022 select hoi.org_information1 er_contact_person, -- code 2025
2023 hoi.org_information2 er_contact_number, -- code 2026
2024 hoi.org_information3 er_email_address, -- code 2027
2025 hoi.org_information4 er_unit_num, -- code 2061
2026 hoi.org_information5 er_complex, -- code 2062
2027 hoi.org_information6 er_street_num, -- code 2063
2028 hoi.org_information7 er_street_name_farm, -- code 2063
2029 hoi.org_information8 er_suburb_district, -- code 2063
2030 hoi.org_information9 er_town_city, -- code 2063
2031 hoi.org_information10 er_postal_code -- code 2063
2032 from hr_organization_information hoi
2033 where hoi.organization_id = p_legal_entity
2034 and hoi.org_information_context = 'ZA_GRE_TAX_FILE_ENTITY';
2035
2036 rec_tax_info csr_tax_info%rowtype;
2037 rec_tax_file_creator_inf csr_tax_file_creator_inf%rowtype;
2038 l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_EMPLOYER_INFO';
2039 l_er_info varchar2(1):='N';
2040 l_msgtext varchar2(2000);
2041 --type msgtext_tab is table of varchar2(2000) index by binary_integer;
2042 l_er_msg_tab msgtext_tab;
2043 l_er_warn_tab msgtext_tab;
2044 l_msg_count number(5):=0;
2045 l_er_trade_name varchar2(2);
2046 begin
2047
2048 -- hr_utility.trace_on(null,'ZATYEV');
2049 hr_utility.set_location('Entered '||l_proc,10);
2050
2051 g_xml_element_table(g_xml_element_count).tagname := 'Employer Information';
2052 g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2053 g_xml_element_count := g_xml_element_count + 1;
2054
2055 hr_utility.set_location('Get ZA Tax Information',15);
2056 open csr_tax_info;
2057 fetch csr_tax_info into rec_tax_info;
2058 close csr_tax_info;
2059
2060 hr_utility.set_location('Get ZA GRE Tax File Creator Info',15);
2061 open csr_tax_file_creator_inf;
2062 fetch csr_tax_file_creator_inf into rec_tax_file_creator_inf;
2063 if csr_tax_file_creator_inf%rowcount = 0 then
2064 rec_tax_file_creator_inf.er_contact_person:=null;
2065 rec_tax_file_creator_inf.er_contact_number:=null;
2066 rec_tax_file_creator_inf.er_email_address:=null;
2067 end if;
2068 close csr_tax_file_creator_inf;
2069
2070 l_er_msg_tab.delete;
2071
2072 hr_utility.set_location('Start the Employer level validation',15);
2073
2074 --Validate the Company Trading Name (Code 2010):
2075 hr_utility.set_location('Validate Company Trading Name - Code 2010',15);
2076 if rec_tax_info.er_trade_name is null then
2077 hr_utility.set_location('Company Trading Name is null',15);
2078 fnd_message.set_name('PAY', 'PY_ZA_ENTER_TRADING_NAME');
2079 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2080 l_msg_count := l_msg_count + 1;
2081 l_er_info :='Y';
2082 /* Commented as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2083 elsif length(translate(rec_tax_info.er_trade_name,'~\/*?:><|','~')) <> length(rec_tax_info.er_trade_name)
2084 OR instr(rec_tax_info.er_trade_name,'""')<>0 then
2085 hr_utility.set_location('Company Trading Name is invalid',15);
2086 fnd_message.set_name('PAY', 'PY_ZA_INVALID_EMPLOYER_NAME');
2087 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2088 l_msg_count := l_msg_count + 1;
2089 l_er_info :='Y'; */
2090 elsif validate_character_set(rec_tax_info.er_trade_name,'FREETEXT') = FALSE then
2091 hr_utility.set_location('Company Trading Name is invalid',15);
2092 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2093 fnd_message.set_token('FIELD','Company Trading or Other Name');
2094 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2095 l_msg_count := l_msg_count + 1;
2096 l_er_info :='Y';
2097 end if;
2098
2099 /* Added for ZA Aug reconciliation phase II */
2100 l_er_trade_name := substr(rec_tax_info.er_trade_name,1,1);
2101
2102 if (l_er_trade_name between 'A' and 'Z') or
2103 (l_er_trade_name between 'a' and 'z') or
2104 (l_er_trade_name between '0' and '9') then
2105 null;
2106 else
2107 hr_utility.set_location('Company Trading Name is invalid',15);
2108 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2109 fnd_message.set_token('FIELD','Company Trading or Other Name');
2110 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2111 l_msg_count := l_msg_count + 1;
2112 l_er_info :='Y';
2113 end if;
2114
2115 --Validate Trade classification (Code 2035)
2116 --Updated as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2117 hr_utility.set_location('Validate Company Trade Classification - Code 2035',20);
2118 if rec_tax_info.er_trade_class is null and p_tax_year <> 2010 then
2119 hr_utility.set_location('Company Trade Classification is null',20);
2120 fnd_message.set_name('PAY', 'PY_ZA_ENTER_TRADE_CLASS');
2121 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2122 l_msg_count := l_msg_count + 1;
2123 l_er_info :='Y';
2124 else
2125 hr_utility.set_location('Company Trade Classification is not null',20);
2126 if validate_character_set(rec_tax_info.er_trade_class,'NUMERIC')=FALSE then
2127 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2128 fnd_message.set_token('FIELD', 'Trade Classification in ZA Tax Information');
2129 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2130 l_msg_count := l_msg_count + 1;
2131 l_er_info :='Y';
2132 end if;
2133 if length(rec_tax_info.er_trade_class) > 4 then
2134 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
2135 fnd_message.set_token('FIELD', 'Trade Classification in ZA Tax Information');
2136 fnd_message.set_token('LENGTH', '4');
2137 fnd_message.set_token('UNITS', 'digits');
2138 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2139 l_msg_count := l_msg_count + 1;
2140 l_er_info :='Y';
2141 end if;
2142 end if;
2143
2144 -- Validate the PAYE Reference Number (Code 2020)
2145 hr_utility.set_location('Validating PAYE Ref Number - Code 2020',22);
2146 if rec_tax_info.paye_ref_num is null then
2147 hr_utility.set_location('PAYE Ref Number is null',22);
2148 fnd_message.set_name('PAY', 'PY_ZA_ENTER_TAX_REF_NO');
2149 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2150 l_msg_count := l_msg_count + 1;
2151 l_er_info :='Y';
2152 elsif substr(rec_tax_info.paye_ref_num,1,1) not in ('0','1','2','3','7','9') then
2153 hr_utility.set_location('PAYE Ref Number begins with invalid character',22);
2154 fnd_message.set_name('PAY', 'PY_ZA_INVALID_TAX_REF_NO');
2155 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2156 l_msg_count := l_msg_count + 1;
2157 l_er_info :='Y';
2158 elsif rec_tax_info.paye_ref_num_mod = 0 then
2159 hr_utility.set_location('PAYE Ref Number fails modulus 10 test',22);
2160 fnd_message.set_name('PAY', 'PY_ZA_INVALID_TAX_REF_NO');
2161 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2162 l_msg_count := l_msg_count + 1;
2163 l_er_info :='Y';
2164 end if;
2165
2166 -- Validate the SDL Number (Code 2022)
2167 hr_utility.set_location('Validating SDL Number - Code 2022',25);
2168 if rec_tax_info.sdl_ref_num is not null then
2169 hr_utility.set_location('SDL Number is not null',25);
2170 if substr(rec_tax_info.sdl_ref_num,1,1) <> 'L' OR length(rec_tax_info.sdl_ref_num) <> 10 then
2171 hr_utility.set_location('SDL Number begins with invalid character',25);
2172 fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
2173 fnd_message.set_token('FIELD','SDL Number');
2174 fnd_message.set_token('CHAR1','L');
2175 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2176 l_msg_count := l_msg_count + 1;
2177 l_er_info :='Y';
2178 end if;
2179 if substr(rec_tax_info.sdl_ref_num,2,9) <> substr(rec_tax_info.paye_ref_num,2,9)
2180 AND substr(rec_tax_info.paye_ref_num,1,1) = '7' then
2181 hr_utility.set_location('SDL Number doesnt match with PAYE Number',25);
2182 fnd_message.set_name('PAY', 'PY_ZA_INVALID_LAST_NINE_CHAR');
2183 fnd_message.set_token('FIELD','SDL Number');
2184 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2185 l_msg_count := l_msg_count + 1;
2186 l_er_info :='Y';
2187 end if;
2188 if pay_za_eoy_val.modulus_10_test(rec_tax_info.sdl_ref_num,'S') =0 then
2189 hr_utility.set_location('SDL Number fails modulus 10 test',25);
2190 fnd_message.set_name('PAY', 'PY_ZA_INVALID_SDL_NO');
2191 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2192 l_msg_count := l_msg_count + 1;
2193 l_er_info :='Y';
2194 end if;
2195 end if;
2196
2197 -- Validate the UIF Number (Code 2024)
2198 -- First replacing any char other than U and digits with null as earlier UIF Number segment
2199 -- allowed value greater than 10 digits.
2200 hr_utility.set_location('Validating UIF Number - Code 2024',27);
2201 rec_tax_info.uif_ref_num := translate(rec_tax_info.uif_ref_num,
2202 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
2203 'U0123456789');
2204 if rec_tax_info.uif_ref_num is not null then
2205 hr_utility.set_location('UIF Number not null',27);
2206 if substr(rec_tax_info.uif_ref_num,1,1) <> 'U' OR length(rec_tax_info.uif_ref_num) <> 10 then
2207 hr_utility.set_location('UIF Number begins with invalid character',27);
2208 fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
2209 fnd_message.set_token('FIELD','UIF Number');
2210 fnd_message.set_token('CHAR1','U');
2211 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2212 l_msg_count := l_msg_count + 1;
2213 l_er_info :='Y';
2214 end if;
2215 if substr(rec_tax_info.uif_ref_num,2,9) <> substr(rec_tax_info.paye_ref_num,2,9)
2216 AND substr(rec_tax_info.paye_ref_num,1,1) = '7' then
2217 hr_utility.set_location('UIF Number doesnt match with PAYE Number',27);
2218 fnd_message.set_name('PAY', 'PY_ZA_INVALID_LAST_NINE_CHAR');
2219 fnd_message.set_token('FIELD','UIF Number');
2220 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2221 l_msg_count := l_msg_count + 1;
2222 l_er_info :='Y';
2223 end if;
2224 if pay_za_eoy_val.modulus_10_test(rec_tax_info.uif_ref_num,'U') =0 then
2225 hr_utility.set_location('UIF Number fails modulus 10 test',25);
2226 fnd_message.set_name('PAY', 'PY_ZA_INVALID_UIF_NO');
2227 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2228 l_msg_count := l_msg_count + 1;
2229 l_er_info :='Y';
2230 end if;
2231 end if;
2232
2233 -- Validate GRE Tax File Creator Info
2234 -- Validate Contact Person Name (Code 2025)
2235 hr_utility.set_location('Validating Contact Person Name - Code 2025',30);
2236 if rec_tax_file_creator_inf.er_contact_person is null then
2237 hr_utility.set_location('Contact Person Name is null',30);
2238 fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_NAME');
2239 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2240 l_msg_count := l_msg_count + 1;
2241 l_er_info :='Y';
2242 elsif validate_character_set(rec_tax_file_creator_inf.er_contact_person,'ALPHA') = FALSE then
2243 hr_utility.set_location('Contact Person Name contains invalid characters',30);
2244 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2245 fnd_message.set_token('FIELD','Contact Person in ZA Tax File Information');
2246 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2247 l_msg_count := l_msg_count + 1;
2248 l_er_info :='Y';
2249 end if;
2250
2251 -- Validate Contact Person Number (Code 2026)
2252 hr_utility.set_location('Validating Contact Person Number - Code 2026',32);
2253 if rec_tax_file_creator_inf.er_contact_number is null then
2254 hr_utility.set_location('Contact Person Number is null',32);
2255 hr_utility.set_location('l_msg_count:'||l_msg_count,32);
2256 fnd_message.set_name('PAY', 'PY_ZA_NEW_ENTER_CONTACT_PH_NO');
2257 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2258 l_msg_count := l_msg_count + 1;
2259 l_er_info :='Y';
2260 elsif length(rec_tax_file_creator_inf.er_contact_number) not between 9 and 11 then
2261 hr_utility.set_location('Contact Person Number length not between 9 and 11',32);
2262 fnd_message.set_name('PAY', 'PY_ZA_INVALID_PH_NO');
2263 fnd_message.set_token('FIELD','Contact Number in ZA Tax File Information');
2264 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2265 l_msg_count := l_msg_count + 1;
2266 l_er_info :='Y';
2267
2268 /* Added for ZA Aug reconciliation Phase II */
2269 elsif (instr(rec_tax_file_creator_inf.er_contact_number,'+') > 0 )
2270 or (instr(rec_tax_file_creator_inf.er_contact_number,' ') > 0 ) then
2271 hr_utility.set_location('Contact Person Number contains invalid characters',32);
2272 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2273 fnd_message.set_token('FIELD','Contact Number in ZA Tax File Information');
2274 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2275 l_msg_count := l_msg_count + 1;
2276 l_er_info :='Y';
2277
2278 end if;
2279
2280 -- Validate Employer Email Address (Code 2027)
2281 hr_utility.set_location('Validating Employer Email Address - Code 2027',35);
2282 if rec_tax_file_creator_inf.er_email_address is not null then
2283 hr_utility.set_location('Email Address not null',35);
2284 if validate_email_id(rec_tax_file_creator_inf.er_email_address) = FALSE then
2285 hr_utility.set_location('Email Address doesnt contain @ or .',35);
2286 fnd_message.set_name('PER', 'HR_ZA_INVALID_CONTACT_EMAIL');
2287 fnd_message.set_token('CONTACT','Email Address in ZA Tax File Information');
2288 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2289 l_msg_count := l_msg_count + 1;
2290 l_er_info :='Y';
2291 end if;
2292 if validate_character_set(rec_tax_file_creator_inf.er_email_address,'FREETEXT') = FALSE then
2293 hr_utility.set_location('Email Address contains invalid characters.',35);
2294 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2295 fnd_message.set_token('FIELD','Email Address in ZA Tax File Information');
2296 l_er_msg_tab(l_msg_count) := fnd_message.get('Y');
2297 l_msg_count := l_msg_count + 1;
2298 l_er_info :='Y';
2299 end if;
2300 end if;
2301 -- End Validate GRE Tax File Creator Info general info
2302
2303 hr_utility.set_location('l_msg_count:'||l_msg_count,35);
2304 --Validate employer address
2305 validate_address( P_STYLE => 'ZA_GRE'
2306 ,P_TAX_YEAR => null
2307 ,P_UNIT_NUMBER => rec_tax_file_creator_inf.er_unit_num
2308 ,P_COMPLEX => rec_tax_file_creator_inf.er_complex
2309 ,P_STREET_NUMBER => rec_tax_file_creator_inf.er_street_num
2310 ,P_STREET_NAME => rec_tax_file_creator_inf.er_street_name_farm
2311 ,P_SUBURB_DISTRICT => rec_tax_file_creator_inf.er_suburb_district
2312 ,P_TOWN_OR_CITY => rec_tax_file_creator_inf.er_town_city
2313 ,P_POSTAL_CODE => rec_tax_file_creator_inf.er_postal_code
2314 ,P_MSG_TXT => l_er_msg_tab
2315 ,P_WARN_TXT => l_er_warn_tab
2316 );
2317
2318 hr_utility.set_location('l_msg_count:'||l_msg_count,36);
2319
2320 hr_utility.set_location('Populating XML Table',90);
2321
2322 l_msg_count:=l_er_msg_tab.count;
2323 if l_er_info = 'Y' OR l_msg_count >0 then
2324 g_xml_element_table(g_xml_element_count).tagname := 'ER_INFO';
2325 g_xml_element_table(g_xml_element_count).tagvalue := 'Y';
2326 g_xml_element_count := g_xml_element_count + 1;
2327 l_er_info :='Y';
2328
2329
2330 for i in l_er_msg_tab.first .. l_er_msg_tab.last
2331 loop
2332 g_xml_element_table(g_xml_element_count).tagname := 'ER_ERROR';
2333 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
2334 g_xml_element_count := g_xml_element_count + 1;
2335
2336 g_xml_element_table(g_xml_element_count).tagname := 'ERROR';
2337 g_xml_element_table(g_xml_element_count).tagvalue := l_er_msg_tab(i);
2338 g_xml_element_count := g_xml_element_count + 1;
2339
2340 g_xml_element_table(g_xml_element_count).tagname := 'ER_ERROR';
2341 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
2342 g_xml_element_count := g_xml_element_count + 1;
2343 end loop;
2344 end if;
2345
2346 hr_utility.set_location('l_er_msg_tab.first:'||l_er_msg_tab.first,90);
2347 hr_utility.set_location('l_er_msg_tab.last:'||l_er_msg_tab.last,90);
2348
2349 g_xml_element_table(g_xml_element_count).tagname := 'End Employer Information';
2350 g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
2351 g_xml_element_count := g_xml_element_count + 1;
2352
2353 p_er_info:=l_er_info;
2354
2355 hr_utility.set_location('Leaving '||l_proc,200);
2356
2357 end get_employer_info;
2358
2359 ----------------------------------------------------------------------------
2360 -- Validate phone numbers
2361 ----------------------------------------------------------------------------
2362 procedure validate_phones (p_person_id number
2363 , p_nature varchar2
2364 , p_effective_date date
2365 , p_tax_year number
2366 , p_ee_msg_tab in out nocopy msgtext_tab
2367 , p_ee_warn_tab in out nocopy msgtext_tab
2368 ) is
2369 -- employees returns phone details
2370 cursor csr_phones (p_phone_type varchar2) is
2371 select translate(upper(phone_number),
2372 '0123456789+-. ',
2373 '0123456789') -- remove any character other than digits
2374 from per_phones
2375 where parent_table = 'PER_ALL_PEOPLE_F'
2376 and parent_id = p_person_id
2377 and phone_type = p_phone_type
2378 and p_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY')) ;
2379
2380 l_temp number;
2381 l_home_no varchar2(60);
2382 l_work_no varchar2(60);
2383 l_fax varchar2(60);
2384 l_cell_no varchar2(60);
2385 l_msg_count number(5):=0;
2386 l_warn_count number(5):=0;
2387 l_proc varchar2(250):='PAY_ZA_EOY_VAL.VALIDATE_PHONES';
2388 begin
2389
2390 hr_utility.set_location('Entering '||l_proc,10);
2391 l_msg_count :=p_ee_msg_tab.count;
2392 l_warn_count := p_ee_warn_tab.count;
2393
2394 -- Validate Home Phone Number (Code 3135)
2395
2396 hr_utility.set_location('Validating Home Telephone Number -Code 3135',15);
2397 hr_utility.set_location('Retrieve Home Primary Number',15);
2398 open csr_phones('H1');
2399 fetch csr_phones into l_home_no;
2400 close csr_phones;
2401
2402 if l_home_no is null then
2403 hr_utility.set_location('Retrieve Home Secondary Number',15);
2404 open csr_phones('H2');
2405 fetch csr_phones into l_home_no;
2406 close csr_phones;
2407
2408 if l_home_no is null then
2409 hr_utility.set_location('Retrieve Home Tertiary Number',15);
2410 open csr_phones('H3');
2411 fetch csr_phones into l_home_no;
2412 close csr_phones;
2413 end if;
2414 end if ;
2415
2416 if l_home_no is not null then
2417 if length(l_home_no) not between 9 and 11 then
2418 fnd_message.set_name('PER', 'HR_ZA_INVALID_PH_NO');
2419 fnd_message.set_token('FIELD', 'Home Telephone Number');
2420 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2421 l_msg_count := l_msg_count + 1;
2422 end if;
2423 if validate_character_set(l_home_no,'NUMERIC') = FALSE then
2424 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2425 fnd_message.set_token('FIELD', 'Home Telephone Number');
2426 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2427 l_msg_count := l_msg_count + 1;
2428 end if;
2429 end if;
2430
2431 -- Validate Business Phone Number (Code 3136)
2432 hr_utility.set_location('Validating Business Telephone Number -Code 3136',20);
2433 hr_utility.set_location('Retrieve Work Primary Number',20);
2434 open csr_phones('W1');
2435 fetch csr_phones into l_work_no;
2436 close csr_phones;
2437
2438 if l_work_no is null then
2439 hr_utility.set_location('Retrieve Work Secondary Number',20);
2440 open csr_phones('W2');
2441 fetch csr_phones into l_work_no;
2442 close csr_phones;
2443
2444 if l_work_no is null then
2445 hr_utility.set_location('Retrieve Work Tertiary Number',20);
2446 open csr_phones('W3');
2447 fetch csr_phones into l_work_no;
2448 close csr_phones;
2449 end if;
2450 end if ;
2451
2452 if p_nature <> 'N' and l_work_no is null then
2453 fnd_message.set_name('PER', 'HR_ZA_ENTER_BUS_PH_NO');
2454 if p_tax_year=2010 then
2455 p_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
2456 l_warn_count := l_warn_count + 1;
2457 else
2458 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2459 l_msg_count := l_msg_count + 1;
2460 end if;
2461 elsif l_work_no is not null then
2462 if length(l_work_no) not between 9 and 11 then
2463 fnd_message.set_name('PER', 'HR_ZA_INVALID_PH_NO');
2464 fnd_message.set_token('FIELD', 'Work Telephone Number');
2465 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2466 l_msg_count := l_msg_count + 1;
2467 end if;
2468 if validate_character_set(l_work_no,'NUMERIC') = FALSE then
2469 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2470 fnd_message.set_token('FIELD', 'Work Telephone Number');
2471 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2472 l_msg_count := l_msg_count + 1;
2473 end if;
2474 end if;
2475
2476 -- Validate Fax Number (Code 3137)
2477 hr_utility.set_location('Validating Fax Number -Code 3137',25);
2478 hr_utility.set_location('Retrieve Work Fax Number',25);
2479 open csr_phones('WF');
2480 fetch csr_phones into l_fax;
2481 close csr_phones;
2482
2483 if l_fax is null then
2484 hr_utility.set_location('Retrieve Home Fax Number',25);
2485 open csr_phones('HF');
2486 fetch csr_phones into l_fax;
2487 close csr_phones;
2488 end if;
2489
2490 if l_fax is not null then
2491 if length(l_fax) not between 9 and 11 then
2492 fnd_message.set_name('PER', 'HR_ZA_INVALID_PH_NO');
2493 fnd_message.set_token('FIELD', 'Work Fax/ Home Fax');
2494 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2495 l_msg_count := l_msg_count + 1;
2496 end if;
2497 if validate_character_set(l_fax,'NUMERIC') = FALSE then
2498 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2499 fnd_message.set_token('FIELD', 'Work Fax/ Home Fax');
2500 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2501 l_msg_count := l_msg_count + 1;
2502 end if;
2503 end if;
2504
2505 --Validate cell number (code 3138)
2506 hr_utility.set_location('Validating Cell Number -Code 3138',30);
2507 open csr_phones('M');
2508 fetch csr_phones into l_cell_no;
2509 close csr_phones;
2510
2511 if l_cell_no is not null then
2512 if length(l_cell_no) <> 10 then
2513 /* fnd_message.set_name('PER', 'HR_ZA_INVALID_LENGTH');
2514 fnd_message.set_token('FIELD', 'Mobile Number');
2515 fnd_message.set_token('LENGTH', '10');
2516 fnd_message.set_token('UNITS', 'digits');
2517 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y'); */
2518 p_ee_msg_tab(l_msg_count):='The Mobile Number is invalid. Its length must be between 10 and 11 digits.';
2519 l_msg_count := l_msg_count + 1;
2520 end if;
2521 if validate_character_set(l_cell_no,'NUMERIC') = FALSE then
2522 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2523 fnd_message.set_token('FIELD', 'Mobile Number');
2524 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2525 l_msg_count := l_msg_count + 1;
2526 end if;
2527 end if;
2528
2529 hr_utility.set_location('Leaving '||l_proc,200);
2530
2531 end validate_phones;
2532
2533 -- 11899934
2534
2535 ---------------------------------------------------------------------------
2536 -- Validate Employee_Number
2537 ---------------------------------------------------------------------------
2538
2539 procedure validate_emp_num
2540 (
2541 p_emp_num in varchar2,
2542 P_EE_MSG_TAB IN OUT nocopy msgtext_tab,
2543 P_EE_WARN_TAB IN OUT nocopy msgtext_tab
2544 ) IS
2545
2546 l_msg_count number(5):=0;
2547 l_warn_count number(5):=0;
2548 l_proc varchar2(250) := 'PAY_ZA_EOY_VAL.VALIDATE_EMP_NUM';
2549 --
2550 begin
2551
2552 -- The employee number should be free text and shouldn't be
2553 -- greater than 25 characters.
2554 -- Validate Employee Number (Code 3160)
2555
2556 hr_utility.set_location('Entering '||l_proc,10);
2557 hr_utility.set_location('p_empno'|| p_emp_num, 15);
2558 --
2559 if ((p_emp_num is null)
2560 or (length(p_emp_num) > 25 or per_za_user_hook_pkg.validate_charcter_set(p_emp_num, 'FREETEXT')= FALSE))
2561 then
2562
2563 fnd_message.set_name('PER', 'HR_ZA_INVALID_EMP_NO');
2564 fnd_message.raise_error;
2565 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2566 l_msg_count := l_msg_count + 1;
2567
2568 end if;
2569
2570 hr_utility.set_location('Leaving '||l_proc,20);
2571
2572 end validate_emp_num;
2573
2574 -- 11899934
2575
2576
2577 ---------------------------------------------------------------------------
2578 -- Validate Nature
2579 ---------------------------------------------------------------------------
2580 procedure validate_nature
2581 (P_NATURE varchar2,
2582 P_FNAME varchar2,
2583 P_MNAME varchar2,
2584 P_LNAME varchar2,
2585 P_EMPNO varchar2,
2586 P_TRADING_NAME varchar2,
2587 P_NI varchar2,
2588 P_ID_DOB varchar2,
2589 P_PASSPORT_NO varchar2,
2590 P_COUNTRY_PASSPORT varchar2,
2591 P_IT_NO_VALUE varchar2,
2592 P_IT_NO_VAL number,
2593 P_CERT_TYPE varchar2,
2594 P_TAX_YEAR number,
2595 P_PERIOD_RECON varchar2, -- For Bug 9939519 Aug Submission publish on 14-Jul-2010
2596 P_EE_MSG_TAB IN OUT nocopy msgtext_tab,
2597 P_EE_WARN_TAB IN OUT nocopy msgtext_tab
2598 )
2599 is
2600 l_msg_count number(5):=0;
2601 l_warn_count number(5):=0;
2602 l_proc varchar2(250) := 'PAY_ZA_EOY_VAL.VALIDATE_NATURE';
2603 l_alpha varchar2(52) := '- ,''.';
2604 l_invalid_char varchar2(1) := '~';
2605 begin
2606
2607 hr_utility.set_location('Entering '||l_proc,10);
2608
2609 --Validate Nature of Person (Code 3020)
2610 hr_utility.set_location('Validation Nature of Person -Code 3020',10);
2611 if p_nature is null then
2612 hr_utility.set_location('Nature of Person is null',10);
2613 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NATURE_PERSON');
2614 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2615 l_msg_count := l_msg_count + 1;
2616 elsif p_nature = 'M' then
2617 hr_utility.set_location('Nature of Person is M',10);
2618 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NATURE_PERSON');
2619 fnd_message.set_token('NATURE', 'M');
2620 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2621 l_msg_count := l_msg_count + 1;
2622 elsif p_nature = 'K' then
2623 hr_utility.set_location('Nature of Person is K',10);
2624 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NATURE_PERSON');
2625 fnd_message.set_token('NATURE', 'K');
2626 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2627 l_msg_count := l_msg_count + 1;
2628 end if;
2629
2630 -- Validate Code Employee Surname/Trading Name (Code 3030)
2631 hr_utility.set_location('Validation EE Surname/Trading Name -Code 3030',15);
2632 if p_nature in ('D','E','F','G','H') then
2633 if p_trading_name is null then
2634 hr_utility.set_location('EE Surname is null',15);
2635 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_DEFGH_TRADE');
2636 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2637 l_msg_count := l_msg_count + 1;
2638 elsif validate_character_set(p_trading_name,'FREETEXT')= FALSE then
2639 hr_utility.set_location('EE Surname contains invalid characters',15);
2640 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2641 fnd_message.set_token('FIELD', 'Employee Trading Name');
2642 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2643 l_msg_count := l_msg_count + 1;
2644 /* Commented as per revision 8.0.0 of SARS PAYE Reconiliation 2010
2645 elsif translate(p_trading_name,'~/\*?:><|','~') is not null OR instr(p_trading_name,'""')<>0 then
2646 hr_utility.set_location('EE Surname contains invalid characters',15);
2647 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2648 fnd_message.set_token('FIELD', 'Employee Trading Name');
2649 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2650 l_msg_count := l_msg_count + 1; */
2651 end if;
2652 elsif p_nature in ('A','B','C','N') then
2653 --Validate code 3030
2654 --Checking IF the Surname IS Null
2655 IF p_lname IS NULL then
2656 hr_utility.set_location('EE Surname is null',16);
2657 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ABC_S_F_NAME');
2658 -- fnd_message.set_token('FIELD', 'Employee''s Surname');
2659 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2660 l_msg_count := l_msg_count + 1;
2661 END if;
2662 /* commented as the numeric are allowed in 3030 as it is free text bug 9507670*/
2663 /* uncommented for Aug 2010 reconciliation phase II as numeric are not allowed in 3030 */
2664 if nvl(length(translate(p_lname,'~0123456789','~')),0) <> length(p_lname) then
2665 hr_utility.set_location('EE last name contains invalid characters',15);
2666 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2667 fnd_message.set_token('FIELD', 'Employee''s Surname');
2668 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2669 l_msg_count := l_msg_count + 1;
2670 end if;
2671 end if;
2672
2673 --Validate First Two Names ( Code 3040 )
2674 hr_utility.set_location('Validation First Two Names -Code 3040',20);
2675 if p_nature in ('A','B','C','N') then
2676 if p_fname is null and p_mname is null then
2677 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ABCN_F_NAME');
2678 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2679 l_msg_count := l_msg_count + 1;
2680 /* ucommented as the numeric are not allowed in first two Names bug 9507670*/
2681 elsif nvl(length(translate(p_fname,'~0123456789','~')),0) <> nvl(length(p_fname),0)
2682 OR nvl(length(translate(p_mname,'~0123456789','~')),0) <> nvl(length(p_mname),0) then
2683 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2684 fnd_message.set_token('FIELD', 'First or Middle Name');
2685 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2686 l_msg_count := l_msg_count + 1;
2687 end if;
2688
2689 --First and Middle name contains invalid characters only
2690 --characters like ' ',- etc (Invalid characters for Initials
2691 --Validate Initials
2692 if nvl(length(translate(p_fname,l_invalid_char||l_alpha,l_invalid_char)),0) = 0
2693 AND nvl(length(translate(p_mname,l_invalid_char||l_alpha,l_invalid_char)),0) = 0
2694 AND (p_fname is not null OR p_mname is not null) then
2695 fnd_message.set_name('PAY', 'PY_ZA_INVALID_INITIALS');
2696 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2697 l_msg_count := l_msg_count + 1;
2698 end if;
2699 end if;
2700
2701
2702 --Validate Identity Number (Code 3060)
2703 hr_utility.set_location('Validating Identity Number -Code 3060',22);
2704 if p_nature in ('A','C','N') then
2705 if p_ni is null and p_passport_no is null and p_nature <> 'N' then
2706 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ACN_ID_PASSNO');
2707 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2708 l_msg_count := l_msg_count + 1;
2709 end if;
2710 if nvl(length(p_ni),13)<>13 then
2711 fnd_message.set_name('PER', 'HR_ZA_INVALID_LENGTH');
2712 fnd_message.set_token('FIELD', 'ID Number');
2713 fnd_message.set_token('LENGTH', '13');
2714 fnd_message.set_token('UNITS', 'digits');
2715 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2716 l_msg_count := l_msg_count + 1;
2717 end if;
2718 if validate_character_set(p_ni,'NUMERIC')= FALSE then
2719 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2720 fnd_message.set_token('FIELD', 'ID Number');
2721 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2722 l_msg_count := l_msg_count + 1;
2723 end if;
2724 if p_ni is not null then
2725 if modulus_13_test(p_ni)=0 then
2726 fnd_message.set_name('PER', 'HR_ZA_INVALID_NI');
2727 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2728 l_msg_count := l_msg_count + 1;
2729 end if;
2730 end if;
2731 if p_id_dob = 0 then
2732 fnd_message.set_name('PAY', 'PY_ZA_INVALID_ID_DOB_CORRELAT');
2733 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2734 l_msg_count := l_msg_count + 1;
2735 end if;
2736 end if;
2737
2738 --Validate Passport Number (Code 3070)
2739 hr_utility.set_location('Validating Passport Number -Code 3070',22);
2740 if p_nature not in ('B','D','E','F','G','H') and p_passport_no is not null then
2741 -- bug 12914879
2742 -- if length(p_passport_no)< 7 then
2743 if length(p_passport_no)< 6 then
2744 -- bug 12914879
2745 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
2746 fnd_message.set_token('FIELD', 'Passport Number');
2747 -- bug 12914879
2748 -- fnd_message.set_token('LENGTH', '7');
2749 fnd_message.set_token('LENGTH', '6');
2750 -- bug 12914879
2751 fnd_message.set_token('UNITS', 'characters');
2752 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2753 l_msg_count := l_msg_count + 1;
2754 end if;
2755
2756 -- bug 13006122
2757
2758 if length(p_passport_no)> 13 then
2759
2760 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
2761 fnd_message.set_token('FIELD', 'Passport Number picked');
2762
2763 fnd_message.set_token('LENGTH', '13');
2764
2765 fnd_message.set_token('UNITS', 'characters');
2766 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2767 l_msg_count := l_msg_count + 1;
2768 end if;
2769
2770 -- bug 13006122 ends
2771
2772 if validate_character_set(p_passport_no,'ALPHANUM')= FALSE then
2773 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2774 fnd_message.set_token('FIELD', 'Passport Number');
2775 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2776 l_msg_count := l_msg_count + 1;
2777 end if;
2778 end if;
2779
2780 --Validate Country of Issue ( Code 3075 )
2781 --If Nature of Person is B, then country of Issue is archived as ZNC, hence validation not required.
2782 hr_utility.set_location('Validating Country of Issue -Code 3075',25);
2783 if p_country_passport is null and p_passport_no is not null then -- 9877034 fix removed B and N
2784 fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
2785 --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
2786 if p_tax_year = 2010 then
2787 p_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
2788 l_warn_count := l_warn_count + 1;
2789 else
2790 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2791 l_msg_count := l_msg_count + 1;
2792 end if;
2793 elsif p_country_passport is not null then
2794 if validate_character_set(p_country_passport,'ALPHA')= FALSE then -- 9877034 fix removed B
2795 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2796 fnd_message.set_token('FIELD', 'Country of Passport Issue');
2797 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2798 l_msg_count := l_msg_count + 1;
2799 end if;
2800 if length(p_country_passport)> 3 then -- 9877034 fix removed B
2801 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
2802 fnd_message.set_token('FIELD', 'Country of Passport Issue');
2803 fnd_message.set_token('LENGTH', '3');
2804 fnd_message.set_token('UNITS', 'characters');
2805 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2806 l_msg_count := l_msg_count + 1;
2807 end if;
2808 end if;
2809
2810 --Validate code Income Tax Number (Code 3100)
2811 hr_utility.set_location('Validating Income Tax Number -Code 3100',27);
2812 if p_it_no_value is null and p_cert_type <> '2' and p_nature <> 'F' then
2813 fnd_message.set_name('PAY', 'PY_ZA_ENTER_IT_TAX_NO');
2814 --Made a warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
2815 /* Bug no 9939519 changes specific to Aug 2010 changes
2816 Added Or condition to make it warning for Aug Submission for from Feb2011 its an error.
2817 */
2818 if p_tax_year = 2010
2819 OR
2820 ( p_tax_year = 2011 and P_PERIOD_RECON = '08')
2821 then
2822 p_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
2823 l_warn_count := l_warn_count + 1;
2824 else
2825 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2826 l_msg_count := l_msg_count + 1;
2827 end if;
2828 elsif p_it_no_value is not null and p_nature <> 'F' then
2829 --Check the modulus 10 test
2830 if p_it_no_val = 0 then
2831 fnd_message.set_name('PAY', 'PY_ZA_INVALID_IT_TAX_NO');
2832 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2833 l_msg_count := l_msg_count + 1;
2834 end if;
2835 if p_nature in ('A','B','C','D','N') and substr(p_it_no_value,1,1) not in ('0','1','2','3') then
2836 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NAT_IT_TAX_NO');
2837 fnd_message.set_token('VALID_NUM', '0, 1, 2 or 3');
2838 fnd_message.set_token('VALID_NATURE', 'A, B, C, D, or N');
2839 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2840 l_msg_count := l_msg_count + 1;
2841 elsif p_nature in ('E','G','H') and substr(p_it_no_value,1,1) <> '9' then
2842 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NAT_IT_TAX_NO');
2843 fnd_message.set_token('VALID_NUM', '9');
2844 fnd_message.set_token('VALID_NATURE', 'E, G, or H');
2845 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2846 l_msg_count := l_msg_count + 1;
2847 end if;
2848 if validate_character_set(p_it_no_value,'NUMERIC')= FALSE then
2849 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2850 fnd_message.set_token('FIELD', 'Income Tax Number');
2851 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2852 l_msg_count := l_msg_count + 1;
2853 end if;
2854 end if;
2855
2856 -- bug 11899934
2857 -- Commenting out the Validation for Employee Number as the validation is already done.
2858 /*
2859 --Validate Employee Number (Code 3160)
2860 if validate_character_set(P_EMPNO,'ALPHANUM')= FALSE then
2861 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2862 fnd_message.set_token('FIELD', 'Employee Number');
2863 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2864 l_msg_count := l_msg_count + 1;
2865 end if;
2866
2867 */
2868
2869 -- bug 11899934
2870
2871 hr_utility.set_location('Leaving '||l_proc,200);
2872
2873 end validate_nature;
2874
2875 --------------------------------------------------------------------------
2876 --Validate contact details
2877 --------------------------------------------------------------------------
2878 procedure validate_contact_details(P_PERSON_ID number,
2879 P_NATURE varchar2,
2880 P_EMAIL varchar2,
2881 P_TAX_YEAR_END date,
2882 P_TAX_YEAR number,
2883 P_EE_MSG_TAB IN OUT nocopy msgtext_tab,
2884 P_EE_WARN_TAB IN OUT nocopy msgtext_tab)
2885 is
2886 l_msg_count number(5):=0;
2887 l_proc varchar2(250):='PAY_ZA_EOY_VAL.VALIDATE_CONTACT_DETAILS';
2888 begin
2889 l_msg_count := p_ee_msg_tab.count;
2890
2891 hr_utility.set_location('Entering '||l_proc,10);
2892
2893 -- Validate email id (Code 3125)
2894 hr_utility.set_location('Validating Email Address -Code 3125',10);
2895 if p_email is not null then
2896 if validate_email_id(p_email) = FALSE then
2897 fnd_message.set_name('PER', 'HR_ZA_INVALID_CONTACT_EMAIL');
2898 fnd_message.set_token('CONTACT', 'Employee');
2899 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2900 l_msg_count := l_msg_count + 1;
2901 end if;
2902 if validate_character_set(p_email,'FREETEXT') = FALSE then
2903 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
2904 fnd_message.set_token('FIELD', 'Employee Email Address');
2905 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2906 l_msg_count := l_msg_count + 1;
2907 end if;
2908 if length(p_email) > 70 then
2909 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
2910 fnd_message.set_token('FIELD', 'Employee Email Address');
2911 fnd_message.set_token('LENGTH', '70');
2912 fnd_message.set_token('UNITS', 'characters');
2913 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
2914 l_msg_count := l_msg_count + 1;
2915 end if;
2916 end if;
2917
2918 --validate the phones i.e. codes 3135 to 3138
2919 hr_utility.set_location('Validating Phone Numbers',15);
2920 validate_phones(P_PERSON_ID, P_NATURE, P_TAX_YEAR_END, P_TAX_YEAR, P_EE_MSG_TAB, P_EE_WARN_TAB);
2921
2922 hr_utility.set_location('Leaving '||l_proc,200);
2923 end validate_contact_details;
2924
2925 ---------------------------------------------------------------------------
2926 --Validate Bank Details
2927 ---------------------------------------------------------------------------
2928 procedure validate_bank_details(P_PERSON_ID number,
2929 P_ASG_ID number,
2930 P_NATURE varchar2,
2931 P_PAYMENT_TYPE varchar2,
2932 P_PAY_METHOD_ID number,
2933 P_EFFECTIVE_DATE date,
2934 P_TAX_YEAR number,
2935 P_EE_MSG_TAB IN OUT nocopy msgtext_tab,
2936 P_EE_WARN_TAB IN OUT nocopy msgtext_tab )
2937 is
2938 l_msg_count number(5):=0;
2939 l_warn_count number(5):=0;
2940 l_count number :=0;
2941
2942 -- At Assignment Extra Info, Payment Type contains values
2943 -- 0 (Cash Payment)
2944 -- 1 (Internal Account Payment)
2945 -- 7 (Foreign Bank Account Payment)
2946 -- If it is 1 (Internal Account Payment),
2947 -- then account details needs to be validated
2948 cursor csr_asg_eit_acc
2949 is
2950 select pea.external_account_id ext_acc_id,
2951 pea.segment3 sars_acc_no,
2952 -- pea.segment2 account_type,
2953 -- pea.segment1 branch_code,
2954 pea.segment4 acc_holder_name,
2955 pea.segment6 acc_holder_reln,
2956 p_payment_type account_type
2957 from pay_external_accounts pea,
2958 pay_personal_payment_methods_f ppm
2959 where ppm.assignment_id = P_ASG_ID
2960 and ppm.personal_payment_method_id = P_PAY_METHOD_ID
2961 and ppm.external_account_id = pea.external_account_id
2962 and p_effective_date between ppm.effective_start_date and ppm.effective_end_date;
2963
2964 -- At bank detail DDF, account type contains values
2965 -- Y (Internal Account Payment)
2966 -- 0 (Cash Payment)
2967 -- 7 (Foreign Bank Account Payment)
2968 -- If it is 1 (Internal Account Payment),
2969 -- then account details needs to be validated
2970 cursor csr_bank_ddf_info
2971 is
2972 select pea.external_account_id ext_acc_id,
2973 pea.segment3 sars_acc_no,
2974 pea.segment4 acc_holder_name,
2975 pea.segment6 acc_holder_reln,
2976 ppm.ppm_information1 account_type
2977 from pay_personal_payment_methods_f ppm,
2978 pay_external_accounts pea
2979 where ppm.assignment_id = P_ASG_ID
2980 and ppm.external_account_id = pea.external_account_id(+)
2981 and ppm.ppm_information_category in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
2982 and ppm.ppm_information1 in ('Y','0','7')
2983 and p_effective_date between ppm.effective_start_date and ppm.effective_end_date;
2984
2985 rec_asg_bnk_det csr_asg_eit_acc%rowtype;
2986 l_external_account_id number;
2987 l_proc varchar2(250):= 'PAY_ZA_OEY_VAL.VALIDATE_BANK_DETAILS';
2988 begin
2989
2990 hr_utility.set_location('Entering '||l_proc,10);
2991 l_msg_count := P_EE_MSG_TAB.count;
2992 l_warn_count := P_EE_WARN_TAB.count;
2993
2994 select count(*)
2995 into l_count
2996 from pay_personal_payment_methods_f
2997 where assignment_id = P_ASG_ID
2998 and PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
2999 and ppm_information1 in ('Y','0','7')
3000 and p_effective_date between effective_start_date and effective_end_date;
3001
3002 --Only one account can be set to SARS reporting
3003 if l_count > 1 then
3004 fnd_message.set_name('PAY', 'PY_ZA_INV_PERS_PAYM_DDF');
3005 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3006 l_msg_count := l_msg_count + 1;
3007 elsif l_count = 1 then
3008 open csr_bank_ddf_info;
3009 fetch csr_bank_ddf_info into rec_asg_bnk_det;
3010 close csr_bank_ddf_info;
3011 else --Bank Detail DDF not set. Hence retrieve from Assignment EIT
3012 if P_PAY_METHOD_ID is null and P_PAYMENT_TYPE = 1 then
3013 fnd_message.set_name('PAY', 'PY_ZA_ENTER_REP_ACC_NO');
3014 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3015 l_msg_count := l_msg_count + 1;
3016 elsif P_PAYMENT_TYPE = 1 then
3017 open csr_asg_eit_acc;
3018 fetch csr_asg_eit_acc into rec_asg_bnk_det;
3019 if csr_asg_eit_acc%notfound then
3020 --Raise an error stating the personal payment method doesnt exists
3021 fnd_message.set_name('PAY', 'PY_ZA_DEL_PAY_METHOD');
3022 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3023 l_msg_count := l_msg_count + 1;
3024 end if;
3025 close csr_asg_eit_acc;
3026 elsif P_PAYMENT_TYPE is null then
3027 --Raise an error stating the account type is not configured
3028 fnd_message.set_name('PAY', 'PY_ZA_ENTER_ACC_TYPE');
3029 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3030 l_msg_count := l_msg_count + 1;
3031 end if;
3032 end if;
3033
3034 --Validate the information for account holder's name and relationship
3035 if rec_asg_bnk_det.account_type in ('Y','1') then
3036 --Added as per revision 8.0.0 of SARS PAYE Reconiliation 2010
3037 if rec_asg_bnk_det.acc_holder_name is null then
3038 fnd_message.set_name('PAY', 'PY_ZA_ENTER_ACC_HOLDER_NAME');
3039 if p_tax_year=2010 then
3040 p_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
3041 l_warn_count := l_warn_count + 1;
3042 else
3043 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3044 l_msg_count := l_msg_count + 1;
3045 end if;
3046 end if;
3047
3048 if validate_character_set(rec_asg_bnk_det.acc_holder_name,'FREETEXT') = FALSE then
3049 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
3050 fnd_message.set_token('FIELD', 'Account Holder''s Name');
3051 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3052 l_msg_count := l_msg_count + 1;
3053 end if;
3054 if rec_asg_bnk_det.acc_holder_reln is null then
3055 fnd_message.set_name('PAY', 'PY_ZA_ENTER_ACC_HOLDER_REL');
3056 --Made warning as per revision 8.0.0 of SARS PAYE Reconiliation 2010 for only Tax Year 2010
3057 if p_tax_year=2010 then
3058 p_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
3059 l_warn_count := l_warn_count + 1;
3060 else
3061 p_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3062 l_msg_count := l_msg_count + 1;
3063 end if;
3064 end if;
3065 end if;
3066
3067
3068 hr_utility.set_location('Leaving '||l_proc,100);
3069
3070 end validate_bank_details;
3071 ---------------------------------------------------------------------------
3072 --Get Employee Information
3073 ---------------------------------------------------------------------------
3074 procedure get_employee_info
3075 (P_BUSINESS_GROUP_ID number,
3076 P_LEGAL_ENTITY number,
3077 P_PAYROLL_ID number,
3078 P_ASG_SET_ID number,
3079 P_PERSON_ID number,
3080 P_TAX_YEAR number,
3081 P_TAX_YEAR_START date,
3082 P_TAX_YEAR_END date,
3083 P_PERIOD_RECON varchar2, -- added Parameter for validation 9939519
3084 P_CERT_TYPE varchar2,
3085 P_EE_NDF_INFO OUT nocopy varchar2
3086 )
3087 is
3088 --For ITREG certificate I dont think Warning section is required.
3089 type t_csr_employee is ref cursor;
3090 csr_employee t_csr_employee;
3091
3092
3093 cursor csr_sars_address(p_person_id number, l_effective_date date
3094 , p_address_style varchar2, p_address_type varchar2)
3095 is
3096 select address_line1 ee_unit_num
3097 , address_line2 ee_complex
3098 , address_line3 ee_street_num
3099 , region_1 ee_street_name
3100 , region_2 ee_suburb_district
3101 , town_or_city ee_town_city
3102 , postal_code ee_postal_code
3103 from per_addresses
3104 where person_id = p_person_id
3105 and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
3106 and style = p_address_style
3107 and address_type = p_address_type;
3108
3109 cursor csr_sars_loc_address(p_location_id number)
3110 is
3111 select lei_information1 ee_unit_num
3112 , lei_information2 ee_complex
3113 , lei_information3 ee_street_num
3114 , lei_information4 ee_street_name
3115 , lei_information5 ee_suburb_district
3116 , lei_information6 ee_town_city
3117 , lei_information7 ee_postal_code
3118 from hr_location_extra_info
3119 where location_id = p_location_id
3120 and information_type ='ZA_SARS_ADDRESS';
3121
3122 cursor csr_postal_address(p_person_id number, l_effective_date date)
3123 is
3124 select nvl(region_2,'N') ee_indicator -- Postal Address same as residential address flag
3125 , decode(region_2,'Y',null,address_line1) ee_add_line1 -- if flag = Y, then don't populate remaining postal address fields
3126 , decode(region_2,'Y',null,address_line2) ee_add_line2
3127 , decode(region_2,'Y',null,address_line3) ee_add_line3
3128 , decode(region_2,'Y',null,postal_code) ee_postal_code
3129 from per_addresses
3130 where person_id = p_person_id
3131 and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
3132 and style = 'ZA'
3133 and primary_flag = 'Y';
3134
3135 l_proc varchar2(250) :='PAY_ZA_EOY_VAL.get_employee_info';
3136 rec_employee csr_employee%type;
3137 type missing_LE_rec is record
3138 ( emp_no per_all_people_f.employee_number%type,
3139 emp_name varchar2(600),
3140 asg_no per_all_assignments_f.assignment_number%type
3141 );
3142 type missing_LE_table is table of missing_LE_rec index by binary_integer;
3143 l_miss_LE_tab missing_LE_table;
3144 l_ee_msg_tab msgtext_tab;
3145 l_ee_warn_tab msgtext_tab;
3146 l_msg_count number(5):=0;
3147 l_warn_count number(5):=0;
3148 l_tax_year_end date;
3149 l_effective_date date;
3150 l_ee_info varchar2(1):='N';
3151 l_warn_info varchar2(1):='N';
3152 l_miss_LE_warn_info varchar2(1):='N';
3153 rec_sars_bus_address csr_sars_address%rowtype;
3154 rec_sars_res_address csr_sars_address%rowtype;
3155 rec_postal_address csr_postal_address%rowtype;
3156 l_emp_no per_all_people_f.employee_number%type;
3157 l_person_id per_all_people_f.person_id%type;
3158 l_assignment_id per_all_assignments_f.assignment_id%type;
3159 l_emp_name varchar2(600);
3160 l_asg_no per_all_assignments_f.assignment_number%type;
3161 l_lname per_all_people_f.last_name%type;
3162 l_fname per_all_people_f.first_name%type;
3163 l_mname per_all_people_f.middle_names%type;
3164 l_ni per_all_people_f.national_identifier%type;
3165 l_location_id per_all_assignments_f.location_id%type;
3166 l_position_id per_all_assignments_f.position_id%type;
3167 l_organization_id per_all_assignments_f.organization_id%type;
3168 l_pos_location_id per_all_positions.location_id%type;
3169 l_org_location_id hr_all_organization_units.location_id%type;
3170 l_passport_no varchar2(150);
3171 l_country_passport varchar2(150);
3172 l_id_dob number(1);
3173 l_it_no_val number(1);
3174 l_it_no_value varchar2(150);
3175 l_email per_all_people_f.email_address%type;
3176 l_emp_trading_name varchar2(150);
3177 l_emp_cc_no varchar2(150);
3178 l_nature varchar2(3);
3179 l_legal_entity varchar2(150);
3180 l_payment_type varchar2(150);
3181 l_personal_pay_meth_id varchar2(150);
3182 l_organization_id1 varchar2(150);
3183 l_miss_LE_count number(5):=0;
3184 l_ee_missing_LE_msg varchar2(90);
3185 l_period_recon_last_date date;
3186
3187 l_sql varchar2(4000);
3188 begin
3189 -- hr_utility.trace_on(null,'ZATYEV');
3190 hr_utility.set_location('Entering '||l_proc,10);
3191
3192 hr_utility.set_location('P_BUSINESS_GROUP_ID :'||P_BUSINESS_GROUP_ID,10);
3193 hr_utility.set_location('P_LEGAL_ENTITY :'||P_LEGAL_ENTITY,10);
3194 hr_utility.set_location('P_PAYROLL_ID :'||P_PAYROLL_ID,10);
3195 hr_utility.set_location('P_ASG_SET_ID :'||P_ASG_SET_ID,10);
3196 hr_utility.set_location('P_PERSON_ID :'||P_PERSON_ID,10);
3197 hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR ,10);
3198 hr_utility.set_location('P_TAX_YEAR_START:'||to_char(P_TAX_YEAR_START,'dd-mon-yyyy hh24:mi:ss'),10);
3199 hr_utility.set_location('P_TAX_YEAR_END :'||to_char(P_TAX_YEAR_END,'dd-mon-yyyy hh24:mi:ss'),10);
3200 hr_utility.set_location('P_CERT_TYPE :'||P_CERT_TYPE,10);
3201 hr_utility.set_location('P_TAX_YEAR :'||P_TAX_YEAR,10);
3202 hr_utility.trace('g_asg_set_where:'|| g_asg_set_where);
3203 hr_utility.trace('g_sort_order_clause:'|| g_sort_order_clause);
3204 l_miss_LE_tab.delete;
3205
3206 g_xml_element_table(g_xml_element_count).tagname := 'Employee Information';
3207 g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
3208 g_xml_element_count := g_xml_element_count + 1;
3209
3210 l_sql :='select ass.assignment_id ,
3211 ass.person_id ,
3212 per.employee_number ,
3213 per.last_name || '' ,'' || initcap(per.title) || '' '' || per.first_name ,
3214 ass.assignment_number ,
3215 per.last_name ,
3216 per.first_name ,
3217 per.middle_names ,
3218 per.national_identifier ,
3219 per.per_information2 ,
3220 per.per_information10 ,
3221 pay_za_eoy_val.check_id_dob(per.national_identifier,per.date_of_birth,''Y'') ,
3222 per.per_information1 ,
3223 pay_za_eoy_val.modulus_10_test(per.per_information1) ,
3224 per.email_address ,
3225 aei.aei_information2 ,
3226 aei.aei_information3 ,
3227 hr_general.decode_lookup(''ZA_PER_NATURES'',aei.aei_information4) ,
3228 aei.aei_information7 ,
3229 aei.aei_information13 ,
3230 aei.aei_information14 ,
3231 nvl(aei.aei_information7,''-1''),
3232 ass.location_id,
3233 ass.position_id,
3234 ass.organization_id,
3235 (select pap.location_id
3236 from per_all_positions pap
3237 where pap.position_id = ass.position_id) pos_location_id,
3238 (select haou.location_id
3239 from hr_all_organization_units haou
3240 where haou.organization_id=ass.organization_id) org_location_id
3241 from
3242 per_assignments_f ass,
3243 per_all_people_f per,
3244 per_assignment_extra_info aei
3245 where
3246 ass.payroll_id = nvl(:1,ass.payroll_id)
3247 and ass.business_group_id=:2
3248 and aei.assignment_id(+)=ass.assignment_id
3249 and (aei.information_type(+)=''ZA_SPECIFIC_INFO'' )
3250 and nvl(aei.aei_information7,:3) = :4
3251 and ( :5 = ''1''
3252 OR (:6 =''2'' and aei.aei_information4 in (''01'',''02'',''03'',''11''))
3253 )
3254 and exists (select 1
3255 from pay_payroll_actions ppa,
3256 pay_assignment_actions paa,
3257 per_time_periods ptp
3258 where
3259 ppa.payroll_id=ass.payroll_id
3260 and paa.assignment_id=ass.assignment_id
3261 and ptp.payroll_id = ppa.payroll_id
3262 and ptp.prd_information1 = :7
3263 and ptp.end_date <= decode(:8,''02'', ptp.end_date, :9)
3264 and paa.payroll_action_id=ppa.payroll_action_id
3265 and ptp.time_period_id = ppa.time_period_id
3266 and ppa.action_type in (''R'', ''Q'', ''V'', ''B'', ''I'')
3267 and paa.action_status in (''C'',''S'') --10376999
3268 )
3269 and (:10 between ass.effective_start_date and ass.effective_end_date
3270 OR
3271 (ass.effective_end_date <=:11
3272 and ass.effective_end_date = ( select max(ass1.effective_end_date)
3273 from per_assignments_f ass1
3274 where ass.assignment_id = ass1.assignment_id
3275 )))
3276 and per.person_id = ass.person_id
3277 and :12 between per.effective_start_date and per.effective_end_date
3278 and per.person_id=nvl(:13,per.person_id)
3279 and per.per_information_category=''ZA'''||g_asg_set_where||g_sort_order_clause;
3280
3281
3282 select last_day(decode(P_PERIOD_RECON, '02', to_date(P_TAX_YEAR||'-02-01','yyyy-mm-dd'), '08', to_date(P_TAX_YEAR-1 ||'-08-01','yyyy-mm-dd')))
3283 into l_period_recon_last_date
3284 from dual;
3285
3286 open csr_employee for l_sql using p_payroll_id, p_business_group_id,p_legal_entity, p_legal_entity,
3287 p_cert_type,p_cert_type,p_tax_year,p_period_recon,l_period_recon_last_date,p_tax_year_end,p_tax_year_end,p_tax_year_end,
3288 p_person_id;
3289 loop
3290 fetch csr_employee into l_assignment_id,l_person_id,l_emp_no,l_emp_name,l_asg_no
3291 ,l_lname,l_fname,l_mname,l_ni,l_passport_no,l_country_passport,l_id_dob
3292 ,l_it_no_value,l_it_no_val,l_email,l_emp_trading_name,l_emp_cc_no,l_nature,l_legal_entity
3293 ,l_payment_type,l_personal_pay_meth_id,l_organization_id1,l_location_id,l_position_id,l_organization_id,l_pos_location_id,l_org_location_id;
3294 exit when csr_employee%notfound;
3295 /*
3296 for rec_employee in csr_employee(p_cert_type)
3297 loop */
3298 hr_utility.set_location('Employee Number:'||l_emp_no, 10);
3299 hr_utility.set_location('Employee Name:'||l_emp_name,10);
3300 hr_utility.set_location('Legal Entity:'||l_legal_entity,10 );
3301
3302 l_ee_msg_tab.delete;
3303 l_ee_warn_tab.delete;
3304
3305 --Fetch the least of assignment's max effective end date and tax year end date
3306 select least(max(paaf.effective_end_date),p_tax_year_end)
3307 into l_effective_date
3308 from per_all_assignments_f paaf
3309 where paaf.effective_start_date <= p_tax_year_end
3310 and paaf.assignment_id = l_assignment_id;
3311
3312 hr_utility.set_location('l_effective_date:'||to_char(l_effective_date,'dd-mon-yyyy'),10);
3313
3314 --Legal entity not provided. Hence populate the warnings table
3315 if l_legal_entity is null then
3316 hr_utility.set_location('Legal Entity not provided.',20);
3317
3318 --create a warnings table for missing legal entities and populate it.
3319 -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_LEGAL_ENTITY');
3320 -- l_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
3321 -- l_warn_count := l_warn_count + 1;
3322 l_miss_LE_tab(l_miss_LE_count).emp_no:=l_emp_no;
3323 l_miss_LE_tab(l_miss_LE_count).asg_no:=l_asg_no;
3324 l_miss_LE_tab(l_miss_LE_count).emp_name:=l_emp_name;
3325 l_miss_LE_count := l_miss_LE_count + 1;
3326 else
3327
3328 -- 11899934
3329 hr_utility.set_location('Validating Employee Number',19);
3330 -- Validate Employee Number
3331
3332 validate_emp_num
3333 ( p_emp_num => l_emp_no
3334 ,P_EE_MSG_TAB => l_ee_msg_tab
3335 ,P_EE_WARN_TAB => l_ee_warn_tab
3336 );
3337 l_msg_count := l_ee_msg_tab.count;
3338 -- 11899934
3339
3340 hr_utility.set_location('Validating Nature',20);
3344 ,P_FNAME => l_fname
3341 -- Validate nature of person
3342
3343 validate_nature( P_NATURE => l_nature
3345 ,P_MNAME => l_mname
3346 ,P_LNAME => l_lname
3347 ,P_EMPNO => l_emp_no
3348 ,P_TRADING_NAME => l_emp_trading_name
3349 ,P_NI => l_ni
3350 ,P_ID_DOB => l_id_dob
3351 ,P_PASSPORT_NO => l_passport_no
3352 ,P_COUNTRY_PASSPORT => l_country_passport
3353 ,P_IT_NO_VALUE => l_it_no_value
3354 ,P_IT_NO_VAL => l_it_no_val
3355 ,P_CERT_TYPE => p_cert_type
3356 ,P_TAX_YEAR => p_tax_year
3357 ,P_PERIOD_RECON => p_period_recon -- For Bug 9939519 Aug Submission publish on 14-Jul-2010
3358 ,P_EE_MSG_TAB => l_ee_msg_tab
3359 ,P_EE_WARN_TAB => l_ee_warn_tab
3360 );
3361 l_msg_count := l_ee_msg_tab.count;
3362
3363 --Validate contact details
3364 hr_utility.set_location('Validating Contact details',20);
3365 validate_contact_details( P_PERSON_ID => l_person_id
3366 ,P_NATURE => l_nature
3367 ,P_EMAIL => l_email
3368 ,P_TAX_YEAR_END=> l_effective_date
3369 ,P_TAX_YEAR => p_tax_year
3370 ,P_EE_MSG_TAB => l_ee_msg_tab
3371 ,P_EE_WARN_TAB => l_ee_warn_tab
3372 );
3373
3374
3375 --Retrieve employee's business address
3376 hr_utility.set_location('Retrieve Business address',20);
3377 /*open csr_sars_address(l_person_id, l_effective_date,
3378 'ZA_SARS', 'ZA_BUS');
3379 fetch csr_sars_address into rec_sars_bus_address;
3380 if csr_sars_address%notfound then
3381 --Business address not specified at employee level
3382 --Hence check the address at Extra Location Information for assignment's location id.
3383 open csr_sars_loc_address(l_location_id);
3384 fetch csr_sars_loc_address into rec_sars_bus_address;
3385 if csr_sars_loc_address%notfound then
3386 rec_sars_bus_address.ee_unit_num:=null;
3387 rec_sars_bus_address.ee_complex:=null;
3388 rec_sars_bus_address.ee_street_num:=null;
3389 rec_sars_bus_address.ee_street_name:=null;
3390 rec_sars_bus_address.ee_suburb_district:=null;
3391 rec_sars_bus_address.ee_town_city:=null;
3392 rec_sars_bus_address.ee_postal_code:=null;
3393 end if;
3394 close csr_sars_loc_address;
3395 end if;
3396 close csr_sars_address;*/
3397 IF (l_location_id is not null) THEN
3398 open csr_sars_loc_address(l_location_id);
3399 fetch csr_sars_loc_address into rec_sars_bus_address;
3400 if csr_sars_loc_address%notfound then
3401 rec_sars_bus_address.ee_unit_num:=null;
3402 rec_sars_bus_address.ee_complex:=null;
3403 rec_sars_bus_address.ee_street_num:=null;
3404 rec_sars_bus_address.ee_street_name:=null;
3405 rec_sars_bus_address.ee_suburb_district:=null;
3406 rec_sars_bus_address.ee_town_city:=null;
3407 rec_sars_bus_address.ee_postal_code:=null;
3408 fnd_message.set_name('PER', 'HR_ZA_ENTER_BUS_ADDRESS');
3409 fnd_message.set_token('LOCATION', 'Location');
3410 l_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3411 l_msg_count := l_msg_count + 1;
3412 end if;
3413 close csr_sars_loc_address;
3414 ELSIF (l_pos_location_id is not null) THEN
3415 open csr_sars_loc_address(l_pos_location_id);
3416 fetch csr_sars_loc_address into rec_sars_bus_address;
3417 if csr_sars_loc_address%notfound then
3418 rec_sars_bus_address.ee_unit_num:=null;
3419 rec_sars_bus_address.ee_complex:=null;
3420 rec_sars_bus_address.ee_street_num:=null;
3421 rec_sars_bus_address.ee_street_name:=null;
3422 rec_sars_bus_address.ee_suburb_district:=null;
3423 rec_sars_bus_address.ee_town_city:=null;
3424 rec_sars_bus_address.ee_postal_code:=null;
3425 fnd_message.set_name('PER', 'HR_ZA_ENTER_BUS_ADDRESS');
3426 fnd_message.set_token('LOCATION', 'Position''s Location');
3427 l_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3428 l_msg_count := l_msg_count + 1;
3429 end if;
3430 close csr_sars_loc_address;
3431 ELSIF (l_org_location_id is not null) THEN
3432 open csr_sars_loc_address(l_org_location_id);
3433 fetch csr_sars_loc_address into rec_sars_bus_address;
3434 if csr_sars_loc_address%notfound then
3435 rec_sars_bus_address.ee_unit_num:=null;
3436 rec_sars_bus_address.ee_complex:=null;
3437 rec_sars_bus_address.ee_street_num:=null;
3441 rec_sars_bus_address.ee_postal_code:=null;
3438 rec_sars_bus_address.ee_street_name:=null;
3439 rec_sars_bus_address.ee_suburb_district:=null;
3440 rec_sars_bus_address.ee_town_city:=null;
3442 fnd_message.set_name('PER', 'HR_ZA_ENTER_BUS_ADDRESS');
3443 fnd_message.set_token('LOCATION', 'Organization''s Location');
3444 l_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3445 l_msg_count := l_msg_count + 1;
3446 end if;
3447 close csr_sars_loc_address;
3448 ELSE
3449 fnd_message.set_name('PER', 'HR_ZA_ENTER_BUS_ADDRESS');
3450 fnd_message.set_token('LOCATION', 'Location or Position''s Location or Organization''s Location');
3451 l_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3452 l_msg_count := l_msg_count + 1;
3453 END IF;
3454
3455 validate_address( P_STYLE => 'ZA_SARS'
3456 ,P_TAX_YEAR => p_tax_year
3457 ,P_ADDRESS_TYPE => 'ZA_BUS'
3458 ,P_UNIT_NUMBER => rec_sars_bus_address.ee_unit_num
3459 ,P_COMPLEX => rec_sars_bus_address.ee_complex
3460 ,P_STREET_NUMBER => rec_sars_bus_address.ee_street_num
3461 ,P_STREET_NAME => rec_sars_bus_address.ee_street_name
3462 ,P_SUBURB_DISTRICT => rec_sars_bus_address.ee_suburb_district
3463 ,P_TOWN_OR_CITY => rec_sars_bus_address.ee_town_city
3464 ,P_POSTAL_CODE => rec_sars_bus_address.ee_postal_code
3465 ,P_NATURE => l_nature
3466 ,P_MSG_TXT => l_ee_msg_tab
3467 ,P_WARN_TXT => l_ee_warn_tab
3468 );
3469
3470 --Retrieve employee's residential address
3471 hr_utility.set_location('Retrieve Residential address',20);
3472 open csr_sars_address(l_person_id, l_effective_date,
3473 'ZA_SARS', 'ZA_RES');
3474 fetch csr_sars_address into rec_sars_res_address;
3475 if csr_sars_address%notfound then
3476 rec_sars_res_address.ee_unit_num:=null;
3477 rec_sars_res_address.ee_complex:=null;
3478 rec_sars_res_address.ee_street_num:=null;
3479 rec_sars_res_address.ee_street_name:=null;
3480 rec_sars_res_address.ee_suburb_district:=null;
3481 rec_sars_res_address.ee_town_city:=null;
3482 rec_sars_res_address.ee_postal_code:=null;
3483 end if;
3484 close csr_sars_address;
3485 --No nature 'N' check for residential address, hence nature not passed.
3486 validate_address( P_STYLE => 'ZA_SARS'
3487 ,P_TAX_YEAR => p_tax_year
3488 ,P_ADDRESS_TYPE => 'ZA_RES'
3489 ,P_UNIT_NUMBER => rec_sars_res_address.ee_unit_num
3490 ,P_COMPLEX => rec_sars_res_address.ee_complex
3491 ,P_STREET_NUMBER => rec_sars_res_address.ee_street_num
3492 ,P_STREET_NAME => rec_sars_res_address.ee_street_name
3493 ,P_SUBURB_DISTRICT => rec_sars_res_address.ee_suburb_district
3494 ,P_TOWN_OR_CITY => rec_sars_res_address.ee_town_city
3495 ,P_POSTAL_CODE => rec_sars_res_address.ee_postal_code
3496 ,P_MSG_TXT => l_ee_msg_tab
3497 ,P_WARN_TXT => l_ee_warn_tab
3498 );
3499
3500 hr_utility.set_location('Retrieve Postal address',20);
3501 --Retrieve employee's postal address
3502 open csr_postal_address(l_person_id, l_effective_date);
3503 fetch csr_postal_address into rec_postal_address;
3504 if csr_postal_address%notfound then
3505 l_msg_count :=l_ee_msg_tab.count;
3506 l_warn_count:=l_ee_warn_tab.count;
3507 fnd_message.set_name('PER', 'HR_ZA_PRIM_ADD_STYLE');
3508 if p_tax_year = 2010 then
3509 l_ee_warn_tab(l_warn_count):=fnd_message.get('Y');
3510 l_warn_count := l_warn_count + 1;
3511 else
3512 l_ee_msg_tab(l_msg_count):=fnd_message.get('Y');
3513 l_msg_count := l_msg_count + 1;
3514 end if;
3515 elsif rec_postal_address.ee_indicator = 'N' then
3516 validate_address( P_STYLE => 'ZA'
3517 ,P_TAX_YEAR => p_tax_year
3518 ,P_ADDRESS_LINE1 => rec_postal_address.ee_add_line1
3519 ,P_ADDRESS_LINE2 => rec_postal_address.ee_add_line2
3520 ,P_ADDRESS_LINE3 => rec_postal_address.ee_add_line3
3521 ,P_POSTAL_CODE => rec_postal_address.ee_postal_code
3522 ,P_MSG_TXT => l_ee_msg_tab
3523 ,P_WARN_TXT => l_ee_warn_tab
3524 );
3525 end if;
3526 close csr_postal_address;
3527
3528 --Validate bank details
3529 hr_utility.set_location('Validating bank details',20);
3530 validate_bank_details(l_person_id,l_assignment_id,l_nature,
3531 l_payment_type,l_personal_pay_meth_id,
3535
3532 l_effective_date, p_tax_year,l_ee_msg_tab,l_ee_warn_tab);
3533
3534 end if;
3536 -- Populate XML
3537 -- If any employee errors present then show the section
3538 hr_utility.set_location('Populate XML Error Table',50);
3539
3540 if l_ee_msg_tab.count > 0 and l_ee_info = 'N' then
3541 hr_utility.set_location('Employee level errors exists',20);
3542 l_ee_info := 'Y';
3543 g_xml_element_table(g_xml_element_count).tagname := 'EE_INFO';
3544 g_xml_element_table(g_xml_element_count).tagvalue := l_ee_info;
3545 g_xml_element_count := g_xml_element_count + 1;
3546 end if;
3547
3548 if l_ee_msg_tab.count > 0 then
3549 g_xml_element_table(g_xml_element_count).tagname := 'EMP';
3550 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3551 g_xml_element_count := g_xml_element_count + 1;
3552
3553 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NO';
3554 g_xml_element_table(g_xml_element_count).tagvalue := l_emp_no;
3555 g_xml_element_count := g_xml_element_count + 1;
3556
3557 g_xml_element_table(g_xml_element_count).tagname := 'ASG_NO';
3558 g_xml_element_table(g_xml_element_count).tagvalue := l_asg_no;
3559 g_xml_element_count := g_xml_element_count + 1;
3560
3561 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NAME';
3562 g_xml_element_table(g_xml_element_count).tagvalue := l_emp_name;
3563 g_xml_element_count := g_xml_element_count + 1;
3564
3565
3566 hr_utility.trace('l_ee_msg_tab.first:'||l_ee_msg_tab.first);
3567 hr_utility.trace('l_ee_msg_tab.last:'||l_ee_msg_tab.last);
3568
3569 for i in l_ee_msg_tab.first .. l_ee_msg_tab.last
3570 loop
3571 g_xml_element_table(g_xml_element_count).tagname := 'EE_ERROR';
3572 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3573 g_xml_element_count := g_xml_element_count + 1;
3574
3575 g_xml_element_table(g_xml_element_count).tagname := 'ERROR';
3576 g_xml_element_table(g_xml_element_count).tagvalue := l_ee_msg_tab(i);
3577 g_xml_element_count := g_xml_element_count + 1;
3578
3579 g_xml_element_table(g_xml_element_count).tagname := 'EE_ERROR';
3580 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3581 g_xml_element_count := g_xml_element_count + 1;
3582
3583 end loop;
3584
3585 g_xml_element_table(g_xml_element_count).tagname := 'EMP';
3586 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3587 g_xml_element_count := g_xml_element_count + 1;
3588 end if;
3589
3590 -- Populate warnings
3591 hr_utility.set_location('Populate XML Warnings Table',70);
3592 if l_ee_warn_tab.count > 0 and l_warn_info = 'N' then
3593 l_warn_info := 'Y';
3594 g_xml_element_table(g_xml_element_count).tagname := 'WARN_INFO';
3595 g_xml_element_table(g_xml_element_count).tagvalue := l_warn_info;
3596 g_xml_element_count := g_xml_element_count + 1;
3597 end if;
3598
3599 if l_ee_warn_tab.count > 0 then
3600 g_xml_element_table(g_xml_element_count).tagname := 'WARN_EMP';
3601 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3602 g_xml_element_count := g_xml_element_count + 1;
3603
3604 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NO';
3605 g_xml_element_table(g_xml_element_count).tagvalue := l_emp_no;
3606 g_xml_element_count := g_xml_element_count + 1;
3607
3608 g_xml_element_table(g_xml_element_count).tagname := 'ASG_NO';
3609 g_xml_element_table(g_xml_element_count).tagvalue := l_asg_no;
3610 g_xml_element_count := g_xml_element_count + 1;
3611
3612 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NAME';
3613 g_xml_element_table(g_xml_element_count).tagvalue := l_emp_name;
3614 g_xml_element_count := g_xml_element_count + 1;
3615
3616 for i in l_ee_warn_tab.first .. l_ee_warn_tab.last
3617 loop
3618 g_xml_element_table(g_xml_element_count).tagname := 'EE_WARN';
3619 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3620 g_xml_element_count := g_xml_element_count + 1;
3621
3622 g_xml_element_table(g_xml_element_count).tagname := 'WARN';
3623 g_xml_element_table(g_xml_element_count).tagvalue := l_ee_warn_tab(i);
3624 g_xml_element_count := g_xml_element_count + 1;
3625
3626 g_xml_element_table(g_xml_element_count).tagname := 'EE_WARN';
3627 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3628 g_xml_element_count := g_xml_element_count + 1;
3629
3630 end loop;
3631
3632 g_xml_element_table(g_xml_element_count).tagname := 'WARN_EMP';
3633 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3634 g_xml_element_count := g_xml_element_count + 1;
3635 end if;
3636
3637 end loop;
3638
3639 --Report the missing Legal Entities warning
3640 if l_miss_LE_tab.count > 0 then
3641 l_miss_LE_warn_info := 'Y';
3642 g_xml_element_table(g_xml_element_count).tagname := 'WARN_LE_INFO';
3646
3643 g_xml_element_table(g_xml_element_count).tagvalue := l_warn_info;
3644 g_xml_element_count := g_xml_element_count + 1;
3645 end if;
3647 if l_miss_LE_tab.count > 0 then
3648 fnd_message.set_name('PAY', 'PY_ZA_ENTER_LEGAL_ENTITY');
3649 l_ee_missing_LE_msg:=fnd_message.get('Y');
3650
3651 for i in l_miss_LE_tab.first .. l_miss_LE_tab.last
3652 loop
3653 g_xml_element_table(g_xml_element_count).tagname := 'WARN_LE_EMP';
3654 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3655 g_xml_element_count := g_xml_element_count + 1;
3656
3657 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NO';
3658 g_xml_element_table(g_xml_element_count).tagvalue := l_miss_LE_tab(i).emp_no;
3659 g_xml_element_count := g_xml_element_count + 1;
3660
3661 g_xml_element_table(g_xml_element_count).tagname := 'ASG_NO';
3662 g_xml_element_table(g_xml_element_count).tagvalue := l_miss_LE_tab(i).asg_no;
3663 g_xml_element_count := g_xml_element_count + 1;
3664
3665 g_xml_element_table(g_xml_element_count).tagname := 'EMP_NAME';
3666 g_xml_element_table(g_xml_element_count).tagvalue := l_miss_LE_tab(i).emp_name;
3667 g_xml_element_count := g_xml_element_count + 1;
3668 /*
3669 g_xml_element_table(g_xml_element_count).tagname := 'EE_WARN';
3670 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3671 g_xml_element_count := g_xml_element_count + 1;
3672
3673 g_xml_element_table(g_xml_element_count).tagname := 'WARN';
3674 g_xml_element_table(g_xml_element_count).tagvalue := l_ee_missing_LE_msg;
3675 g_xml_element_count := g_xml_element_count + 1;
3676
3677 g_xml_element_table(g_xml_element_count).tagname := 'EE_WARN';
3678 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3679 g_xml_element_count := g_xml_element_count + 1;
3680 */
3681
3682 g_xml_element_table(g_xml_element_count).tagname := 'WARN_LE_EMP';
3683 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3684 g_xml_element_count := g_xml_element_count + 1;
3685
3686 end loop;
3687
3688 end if;
3689
3690 if csr_employee%ISOPEN then
3691 close csr_employee;
3692 end if;
3693
3694 g_xml_element_table(g_xml_element_count).tagname := 'End Employee Information';
3695 g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
3696 g_xml_element_count := g_xml_element_count + 1;
3697
3698 if l_warn_info='N' and l_ee_info='N' and l_miss_LE_warn_info='N' then
3699 P_EE_NDF_INFO:='N';
3700 elsif l_ee_info='N' then
3701 P_EE_NDF_INFO:='Y';
3702 end if;
3703
3704 hr_utility.set_location('l_warn_info:'||l_warn_info,100);
3705 hr_utility.set_location('l_ee_info:'||l_ee_info,100);
3706 hr_utility.set_location('P_EE_NDF_INFO:'||P_EE_NDF_INFO,100);
3707 hr_utility.set_location('Leaving '||l_proc,200);
3708 end get_employee_info;
3709
3710 procedure get_tyev_xml(
3711 P_PROCESS_NAME IN varchar2,
3712 P_BUSINESS_GROUP_ID IN number,
3713 P_ACTN_PARAMTR_GRP_ID IN number,
3714 P_LEGAL_ENTITY IN number,
3715 P_LEGAL_ENTITY_HIDDEN IN varchar2,
3716 P_TAX_YEAR IN varchar2,
3717 P_TAX_YEAR_H IN varchar2,
3718 P_PERIOD_RECON IN varchar2, -- 9877034 fix
3719 P_PERIOD_RECON_H IN varchar2, -- 9877034 fix
3720 P_CERT_TYPE IN varchar2,
3721 P_CERT_TYPE_H IN varchar2,
3722 P_PAYROLL_ID IN number,
3723 P_PAYROLL_ID_H IN varchar2,
3724 P_START_DATE IN varchar2,
3725 P_END_DATE IN varchar2,
3726 P_ASG_SET_ID IN number,
3727 P_ASG_SET_ID_H IN varchar2,
3728 P_PERSON_ID IN number,
3729 P_PERSON_ID_H IN varchar2,
3730 P_TEST_RUN IN varchar2,
3731 P_SORT_ORDER1 IN varchar2,
3732 P_SORT_ORDER2 IN varchar2,
3733 P_SORT_ORDER3 IN varchar2,
3734 P_MONTHLY_RUN IN varchar2,
3735 p_template_name IN varchar2,
3736 P_COMBINE_CERT IN varchar2,
3737 P_COMBINE_CERT_H IN varchar2,
3738 p_xml out nocopy CLOB
3739 )
3740 is
3741
3742 l_proc varchar2(250):='PAY_ZA_EOY_VAL.GET_TYEV_XML';
3743 --g_xml_element_count number(5):=0;
3744 p_clob clob;
3745 l_tax_year number(4);
3746 l_tax_year_start_v varchar2(20);
3747 l_tax_year_end_v varchar2(20);
3748 l_tax_year_start date;
3749 l_tax_year_end date;
3750 --g_asg_set_where varchar2(500);
3751 --g_sort_order_clause varchar2(500);
3752 l_req_id number;
3753 --Variables to indicate no data found (NDF) in the template
3754 l_er_ndf_info varchar2(1);
3755 l_ee_ndf_info varchar2(1);
3756 l_archiver_flag number(1):=0;
3757 l_period_recon varchar2(2); -- 9877034 fix
3758
3759 /*
3760
3761 cursor csr_payrolls (l_tax_year number)
3762 IS
3763 select distinct payroll_id
3767 from per_time_periods ptp2
3764 from pay_all_payrolls_f papf
3765 where business_group_id = P_BUSINESS_GROUP_ID
3766 and exists ( select ''
3768 where ptp2.prd_information1=l_tax_year
3769 and ptp2.payroll_id = papf.payroll_id
3770 );
3771 */
3772
3773 begin
3774 -- hr_utility.trace_on(null,'ZATYEV');
3775 hr_utility.set_location('Entering '||l_proc,10);
3776
3777 g_xml_element_table.DELETE;
3778 g_xml_element_count:=0;
3779 ---
3780 -- Start XML
3781 ---
3782 g_xml_element_table(g_xml_element_count).tagname := 'ZATYE2010';
3783 g_xml_element_table(g_xml_element_count).tagvalue := '_START_';
3784 g_xml_element_count := g_xml_element_count + 1;
3785
3786
3787 g_xml_element_table(g_xml_element_count).tagname := 'Parameter Information';
3788 g_xml_element_table(g_xml_element_count).tagvalue := '_COMMENT_';
3789 g_xml_element_count := g_xml_element_count + 1;
3790
3791 -- Tax Year Information
3792 l_tax_year :=rtrim(substr(P_TAX_YEAR_H,10,4));
3793 l_period_recon := rtrim(substr(P_PERIOD_RECON_H,16,2)); -- 9877034 fix
3794
3795
3796
3797 l_tax_year_start:=fnd_date.canonical_to_date(substr(P_START_DATE,12,10));
3798 l_tax_year_end :=fnd_date.canonical_to_date(substr(P_END_DATE,10,10));
3799
3800
3801 hr_utility.set_location('Before retrieving parameter info',10);
3802
3803 -- Retrieve parameter details
3804 get_tyev_parameters(P_LEGAL_ENTITY => P_LEGAL_ENTITY
3805 ,P_PAYROLL_ID => P_PAYROLL_ID
3806 ,P_TAX_YEAR => l_TAX_YEAR
3807 ,P_PERIOD_RECON => l_period_recon -- 9877034 fix
3808 ,P_CERT_TYPE => P_CERT_TYPE
3809 ,P_ASG_SET_ID => P_ASG_SET_ID
3810 ,P_PERSON_ID => P_PERSON_ID
3811 ,P_TEST_RUN => P_TEST_RUN
3812 ,P_TAX_YEAR_END => l_tax_year_end
3813 ,P_SORT_ORDER1 => P_SORT_ORDER1
3814 ,P_SORT_ORDER2 => P_SORT_ORDER2
3815 ,P_SORT_ORDER3 => P_SORT_ORDER3
3816 );
3817
3818 hr_utility.set_location('Before retrieving Employer info',10);
3819 -- Retrieve Employer specific errors/warnings
3820 get_employer_info(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
3821 ,P_LEGAL_ENTITY => P_LEGAL_ENTITY
3822 ,P_TAX_YEAR => l_TAX_YEAR
3823 ,P_ER_INFO => l_er_ndf_info
3824 );
3825
3826 hr_utility.set_location('Before retrieving Employee info',10);
3827 -- Retrieve Employee specific errors/warnings
3828 get_employee_info(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
3829 ,P_LEGAL_ENTITY => P_LEGAL_ENTITY
3830 ,P_PAYROLL_ID => P_PAYROLL_ID
3831 ,P_ASG_SET_ID => P_ASG_SET_ID
3832 ,P_PERSON_ID => P_PERSON_ID
3833 ,P_TAX_YEAR => l_tax_year
3834 ,P_PERIOD_RECON => l_period_recon -- Aug changes Published on 14-Jul-2010 Bug no 9939519
3835 ,P_TAX_YEAR_START => l_tax_year_start
3836 ,P_TAX_YEAR_END => l_tax_year_end
3837 ,P_CERT_TYPE => P_CERT_TYPE
3838 ,P_EE_NDF_INFO => l_ee_ndf_info
3839 );
3840
3841 if l_ee_ndf_info='N' and l_er_ndf_info='N' then
3842 g_xml_element_table(g_xml_element_count).tagname := 'NDF';
3843 g_xml_element_table(g_xml_element_count).tagvalue := 'Y';
3844 g_xml_element_count := g_xml_element_count + 1;
3845 end if;
3846
3847 g_xml_element_table(g_xml_element_count).tagname := 'ZATYE2010';
3848 g_xml_element_table(g_xml_element_count).tagvalue := '_END_';
3849 g_xml_element_count := g_xml_element_count + 1;
3850
3851 /*
3852 dbms_lob.createtemporary(p_clob, FALSE, DBMS_LOB.CALL);
3853 dbms_lob.open(p_clob, DBMS_LOB.LOB_READWRITE);
3854 --
3855 dbms_lob.writeappend(p_clob, 10, '<AB>1</AB>');
3856 p_xml:=p_clob;
3857 */
3858 write_to_clob(p_xml);
3859
3860 hr_utility.set_location('l_ee_ndf_info:'||l_ee_ndf_info,40);
3861 hr_utility.set_location('l_er_ndf_info:'||l_er_ndf_info,40);
3862 hr_utility.set_location('P_MONTHLY_RUN:'||P_MONTHLY_RUN,40);
3863 hr_utility.set_location('P_TEST_RUN:'||P_TEST_RUN,40);
3864
3865
3866 if (P_TEST_RUN='Y' OR (l_ee_ndf_info is not null and l_er_ndf_info<>'Y'))
3867 then
3868 --No errors encountered for Non Test Run Annual report
3869 if P_TEST_RUN='N' then
3870 if P_MONTHLY_RUN='N' then
3871 FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax Year End data has been validated and appears correct.');
3872 else
3873 FND_FILE.PUT_LINE(FND_FILE.LOG,'No errors identified when validating tax year end data.');
3874 end if;
3875 end if;
3876
3877 if P_MONTHLY_RUN='N' then
3878 l_req_id := fnd_request.submit_request( 'PAY',
3879 'PYZAIRPA2010',
3880 'Tax Certificate Preprocess',NULL,NULL,
3881 'ARCHIVE','ZA_TYE','Tax Certificate Preprocess',
3882 '','',
3883 'ARCHIVE',P_BUSINESS_GROUP_ID,'','',P_ACTN_PARAMTR_GRP_ID,-- action_param_group
3884 P_LEGAL_ENTITY, P_LEGAL_ENTITY_HIDDEN,
3885 P_TAX_YEAR, P_TAX_YEAR_H,
3886 P_PERIOD_RECON,P_PERIOD_RECON_H,
3887 P_CERT_TYPE, P_CERT_TYPE_H,
3888 P_PAYROLL_ID,P_PAYROLL_ID_H,
3889 P_START_DATE,P_END_DATE,
3890 P_ASG_SET_ID,P_ASG_SET_ID_H,
3891 P_PERSON_ID,P_PERSON_ID_H,
3892 P_COMBINE_CERT,P_COMBINE_CERT_H,
3893 chr(0),
3894 '','','',
3895 '','','','','','','','','','',
3896 '','','','','','','','','','',
3897 '','','','','','','','','','',
3898 '','','','','','','','','','',
3899 '','','','','','','','','','',
3900 '','','','','','','','','','',
3901 '','','','','','','','');
3902 end if;
3903 else
3904 if P_TEST_RUN ='N' then
3905 --errors found and archiver was not fired
3906 if P_MONTHLY_RUN='N' then
3907 FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax Year End data errors exist. Refer to Tax Year End Data Validation Report output and correct the data.');
3908 else
3909 FND_FILE.PUT_LINE(FND_FILE.LOG,'Errors identified when validating tax year end data. Refer to the Tax Year End Data Validation Report output for further details.');
3910 end if;
3911 end if;
3912 end if;
3913
3914
3915 end get_tyev_xml;
3916
3917
3918 PROCEDURE VALIDATE_TYE_DATA_EOY2010 (
3919 errbuf out nocopy varchar2,
3920 retcode out nocopy number,
3921 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
3922 p_tax_yr_start_date IN DATE,
3923 p_tax_yr_end_date IN DATE,
3924 p_tax_year IN number,
3925 p_period_recon IN varchar2
3926 )
3927 is
3928
3929 -- Fetch the assignment actions processed in the Tax Certificate Preprocess.
3930 CURSOR asgn_for_payroll_action_cur IS
3931 SELECT assignment_id, assignment_action_id
3932 FROM pay_assignment_actions
3933 WHERE payroll_action_id = p_payroll_action_id
3934 ORDER BY assignment_id ;
3935
3936 --Fetch the assignment details
3937 CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
3938 SELECT per.employee_number empno, asgn2.assignment_number assgno
3939 FROM pay_assignment_actions paa,
3940 per_all_assignments_f asgn2,
3941 per_all_people_f per
3942 WHERE paa.assignment_action_id = asgn_ac_id
3943 AND asgn2.assignment_id = paa.assignment_id
3944 AND per.person_id = asgn2.person_id
3945 AND asgn2.effective_start_date =
3946 ( select max(paf2.effective_start_date)
3947 from per_assignments_f paf2
3948 where paf2.effective_start_date <= ( select max(ptp.end_date)
3949 from per_time_periods ptp
3950 where ptp.prd_information1 = p_tax_year
3951 and ptp.payroll_id = asgn2.payroll_id)
3952 and paf2.assignment_id = asgn2.assignment_id
3953 )
3954 AND asgn2.effective_start_date between per.effective_start_date and per.effective_end_date;
3955
3956
3957 -- Retrieve the Tax details for main certificate
3958 CURSOR tax_info ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
3959 ,cert_num varchar2) IS
3960 select nvl(pai.action_information3,0) SITE,
3961 nvl(pai.action_information11,0) PAYE,
3962 nvl(pai.action_information10,0) TAX
3963 from pay_action_information pai
3964 where pai.action_context_id = asgn_ac_id
3965 and pai.action_context_type = 'AAP'
3966 and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
3967 and pai.action_information30 = cert_num;
3968
3969 -- Retrieve the number of income codes for main certificate
3970 -- Lump sum codes are less than 13, hence the number of income codes in
3971 -- lumpsum certificate cannot exceed 13.
3972 CURSOR csr_num_inc_codes ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
3973 ,cert_num varchar2) IS
3974 select count(1)
3975 from pay_action_information pai
3976 where pai.action_context_id = asgn_ac_id
3977 and pai.action_context_type = 'AAP'
3978 and ( pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
3979 OR
3980 pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
3981 )
3982 and pai.action_information30 = cert_num
3983 and pai.action_information3 is null
3984 and pai.action_information2 <> '3907'
3985 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
3986
3987 CURSOR csr_num_3907 ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
3988 ,cert_num varchar2) IS
3989 select 1
3990 from pay_action_information pai
3991 where pai.action_context_id = asgn_ac_id
3992 and pai.action_context_type = 'AAP'
3993 and ( pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
3994 OR
3995 pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
3996 )
3997 and pai.action_information30 = cert_num
3998 and pai.action_information3 is null
3999 and pai.action_information2 = '3907'
4000 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
4001
4002
4003 -- Retrieve the number of deduction codes. These exist in main certificate only
4004 CURSOR csr_num_ded_codes ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE
4005 ,cert_num varchar2) IS
4006 select count(1)
4007 from pay_action_information pai
4008 where pai.action_context_id = asgn_ac_id
4009 and pai.action_context_type = 'AAP'
4010 and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4011 and pai.action_information30 = cert_num
4012 and pai.action_information3 is null
4013 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
4014
4015
4016 -- Retrieve main certificate number
4017 CURSOR csr_cert_num( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
4018 select pai.action_information30 temp_cert_num
4019 ,pai2.action_information2 cert_type --IRP5/IT3A/ITREG
4020 from pay_action_information pai
4021 ,pay_action_information pai2
4022 where pai.action_context_id = asgn_ac_id
4023 and pai.action_context_type = 'AAP'
4024 and pai.action_information_category = 'ZATYE_EMPLOYEE_CONTACT_INFO'
4025 and pai2.action_information_category ='ZATYE_EMPLOYEE_INFO'
4026 and pai2.action_context_id=pai.action_context_id
4027 and pai2.action_context_type=pai.action_context_type
4028 and pai.action_information26='MAIN'
4029 and pai2.action_information30=pai.action_information30;
4030
4031
4032 --Retrieve negative normal incomes (not lump sums). These are included in main certificate only
4033 CURSOR csr_neg_income(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4034 ,p_cert_num varchar2 ) IS
4035 select pai.action_information2 code,
4036 trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value --code_group_value
4037 FROM pay_action_information pai
4038 where pai.action_context_id = p_asgn_action_id
4039 and pai.action_context_type = 'AAP'
4040 and pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
4041 and pai.action_information30 = p_cert_num
4042 and pai.action_information3 is null --code included in
4043 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4044 order by pai.action_information2;
4045
4046 --Retrieve negative lump sums. These are included in main certificate.
4047 CURSOR csr_neg_lmpsm(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4048 ,p_cert_num varchar2 ) IS
4049 select pai.action_information2 code,
4053 nvl(pai.action_information8,0) value1,
4050 'To Be Advised' to_be_adv,
4051 nvl(pai.action_information4,0) to_be_adv_val, --To Be Advised value
4052 pai.action_information7 direct1,
4054 pai.action_information9 direct2,
4055 nvl(pai.action_information10,0) value2,
4056 pai.action_information11 direct3,
4057 nvl(pai.action_information12,0) value3
4058 FROM pay_action_information pai
4059 where pai.action_context_id = p_asgn_action_id
4060 and pai.action_context_type = 'AAP'
4061 and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4062 and pai.action_information30 = p_cert_num
4063 and pai.action_information3 is null --code included in
4064 and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4065 )
4066 order by pai.action_information2;
4067
4068 --Retrieve negative lump sums. These are included in lump sum certificate.
4069 --p_cert_num is main certificate number
4070 CURSOR csr_neg_lmpsm2(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4071 ,p_cert_num varchar2 ) IS
4072 select pai.action_information2 code,
4073 pai.action_information5 value,
4074 pai2.action_information18 direct1
4075 FROM pay_action_information pai,
4076 pay_action_information pai2
4077 where pai.action_context_id = p_asgn_action_id
4078 and pai.action_context_type = 'AAP'
4079 and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4080 and pai2.action_information_category ='ZATYE_EMPLOYEE_INFO'
4081 and pai2.action_context_id = pai.action_context_id
4082 and pai2.action_context_type = pai.action_context_type
4083 and pai.action_information30 = pai2.action_information30
4084 and pai.action_information30 <> p_cert_num
4085 and pai.action_information3 is null --code included in
4086 and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4087 )
4088 order by pai.action_information2;
4089
4090
4091 --Retrieve negative deductions. These are included in main certificate only
4092 CURSOR csr_neg_deduct(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE
4093 ,p_cert_num varchar2 ) IS
4094 select pai.action_information2 code,
4095 trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value --code_group_value
4096 FROM pay_action_information pai
4097 where pai.action_context_id = p_asgn_action_id
4098 and pai.action_context_type = 'AAP'
4099 and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4100 and pai.action_information30 = p_cert_num
4101 and pai.action_information3 is null --code included in
4102 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4103 order by pai.action_information2;
4104
4105 -- Retrieve the PAYE Reference number of the employer
4106 CURSOR csr_paye_ref_num IS
4107 SELECT pai.action_information2 PAYE_REF_NUM
4108 FROM pay_action_information pai
4109 WHERE pai.action_context_id = p_payroll_action_id
4110 AND pai.action_context_type = 'PA'
4111 AND pai.action_information_category = 'ZATYE_EMPLOYER_INFO';
4112
4113 -- Retrieve the value for Tax and Tax on Retirement Fund
4114 CURSOR tax_codes ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
4115 select '1'
4116 from pay_action_information pai
4117 where pai.action_context_id = asgn_ac_id
4118 and pai.action_context_type = 'AAP'
4119 and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4120 and ( trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information10,0)))) <> 0
4121 OR
4122 trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4123 )
4124 ;
4125
4126
4127 --Check whether the employee on specific income has PKG balances
4128 CURSOR fetch_pkg_balances( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4129 , p_cert_num varchar2 ) IS
4130 select '1' flag
4131 FROM pay_action_information pai,
4132 per_assignment_extra_info paei
4133 where pai.action_context_id = p_asgn_action_id
4134 and paei.assignment_id = pai.assignment_id
4135 AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis:1 is Fixed Percentage of Specific Income
4136 AND paei.information_type = 'ZA_SPECIFIC_INFO'
4137 and pai.action_information_category = 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS'
4138 and pai.action_information30 = p_cert_num
4139 AND pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)) <> '0'; --Gross PKG
4140
4141 --Retrieve the elements feeding PKG balances.
4142 CURSOR fetch_pkg_ele( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
4143 SELECT ELEM.element_name element_name,
4144 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
4145 from pay_balance_feeds_f FEED
4146 , pay_run_result_values TARGET
4147 , pay_run_results RR
4148 , per_time_periods PPTP
4149 , per_time_periods BPTP
4150 , pay_payroll_actions PACT
4151 , pay_assignment_actions ASSACT
4152 , pay_payroll_actions BACT
4153 , pay_assignment_actions BAL_ASSACT
4154 , pay_element_types_f ELEM
4155 , pay_balance_types PBT
4156 where BAL_ASSACT.assignment_action_id = p_asgn_action_id
4157 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
4158 and FEED.input_value_id = TARGET.input_value_id
4159 and TARGET.run_result_id = RR.run_result_id
4160 and RR.assignment_action_id = ASSACT.assignment_action_id
4161 + decode(PPTP.year_number, 0, 0, 0)
4162 and ASSACT.payroll_action_id = PACT.payroll_action_id
4163 and PACT.effective_date between FEED.effective_start_date
4164 and FEED.effective_end_date
4165 and BPTP.payroll_id = BACT.payroll_id
4166 and PPTP.payroll_id = PACT.payroll_id
4167 and nvl(BACT.date_earned,BACT.effective_date)
4168 between BPTP.start_date and BPTP.end_date
4169 and PACT.date_earned between PPTP.start_date and PPTP.end_date
4170 and RR.status in ('P','PA')
4171 AND ELEM.element_type_id = RR.element_type_id
4172 and PPTP.prd_information1 = BPTP.prd_information1
4173 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
4174 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
4175 AND feed.BALANCE_TYPE_ID = PBT.balance_type_id
4176 AND PBT.balance_name in ('Taxable Package Components',
4177 'Annual Taxable Package Components'
4178 )
4179 GROUP BY ELEM.element_name
4180 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
4181
4182
4183 -- Retrieve the value of code 4115
4184 CURSOR chk_tax_ret_fund_ls ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
4185 select pai.action_information5,
4186 pai.action_information30 temp_cert_num,
4187 pai2.action_information18 direct1 --Directive1
4188 FROM pay_action_information pai,
4189 pay_action_information pai2
4190 where pai.action_context_id = p_asgn_action_id
4191 and pai.action_context_type = 'AAP'
4192 and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4193 and pai2.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4194 and pai.action_context_id = pai2.action_context_id
4195 and pai.action_context_type = pai2.action_context_type
4196 and pai.action_information30 = pai2.action_information30
4197 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4198 order by pai.action_information30;
4199
4200 --Retrieve the value of codes 3901,3915,3920, 3921 for certificate which has some value in code 4115 (added 3901 for bug 11899934)
4201 /*CURSOR chk_ret_fund_ls ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4202 ,p_cert_num varchar2) IS
4203 select pai.action_information2 code, --either 3901, 3920, 3921, 3915 (added 3901 for bug 11899934)
4204 pai.action_information30 temp_cert_num
4205 FROM pay_action_information pai
4206 where pai.action_context_id = p_asgn_action_id
4207 and pai.action_context_type = 'AAP'
4208 and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4209 and pai.action_information30 = p_cert_num
4210 and pai.action_information3 is null --code included in
4211 and to_number(pai.action_information2) in (3901,3915,3920,3921) -- (added 3901 for bug 11899934)
4212 and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
4213 order by pai.action_information2;*/
4214
4215
4216 -- Cursor for cross validation - Income codes - Main certificate only
4217 CURSOR cross_val_inc_codes ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4218 ,p_cert_num varchar2) IS
4219 select to_number(pai.action_information2) code --income code
4220 ,trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value--income value
4221 ,pai.action_information30 temp_cert_num
4222 from pay_action_information pai
4223 where pai.action_context_id = p_asgn_action_id
4224 and pai.action_context_type='AAP'
4225 and pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
4226 and pai.action_information3 is null
4227 and to_number(pai.action_information2) in (3810,3813,3860,3863)
4228 and pai.action_information30=p_cert_num
4229 order by pai.action_information30;
4230
4231 -- Cursor for cross validation - Deduction codes --Main Certificate only
4232 CURSOR cross_val_ded_codes ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type
4233 ,p_cert_num varchar2) IS
4234 select to_number(pai.action_information2) code --deduction code
4235 ,trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value--deduction value
4236 ,pai.action_information30 temp_cert_num
4237 from pay_action_information pai
4238 where pai.action_context_id = p_asgn_action_id
4239 and pai.action_context_type='AAP'
4240 and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4241 and pai.action_information3 is null
4242 and to_number(pai.action_information2) in (4005,4024,4474,4493)
4243 and pai.action_information30=p_cert_num
4244 order by pai.action_information30;
4245
4246 --Retrieve negative values for context ZATYE_EMPLOYEE_TAX_AND_REASONS
4247 CURSOR chk_neg_tax ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4248 IS
4249 select pai.action_information30 temp_cert_num,
4250 pai2.action_information18 direct1, --Directive1
4251 nvl(pai.action_information3,0) site,
4252 nvl(pai.action_information4,0) paye,
4253 nvl(pai.action_information5,0) tax_ret,
4254 nvl(pai.action_information6,0) uif,
4255 nvl(pai.action_information7,0) sdl,
4256 nvl(pai.action_information8,0) total
4257 FROM pay_action_information pai,
4258 pay_action_information pai2
4259 where pai.action_context_id = p_asgn_action_id
4260 and pai.action_context_type = 'AAP'
4261 and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4262 -- Fix for bug#14128085
4263 --and pai.action_information3 is null --code included in
4264 and pai2.action_information2 in ('IRP5','IT3(a)')
4265 and pai2.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4266 and pai2.action_context_id = pai.action_context_id
4267 and pai.action_context_type = pai2.action_context_type
4268 and pai.action_information30 = pai2.action_information30
4269 and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
4270 OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
4271 OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
4272 OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
4273 OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
4274 OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
4275 )
4276 order by pai.action_information2;
4277
4278 -- Added for bug#10287216
4279
4280
4281 CURSOR csr_fetch_rfi_nrfi( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4282 IS
4283 SELECT nvl(ACTION_INFORMATION2, '0') Non_Taxable_Income,
4284 nvl(ACTION_INFORMATION3, '0') Gross_Retire_Fund_Income,
4285 nvl(ACTION_INFORMATION4, '0') Gross_Non_Retire_Fund_In
4286 FROM pay_action_information
4287 WHERE action_context_id = p_asgn_action_id
4288 AND action_information_category = 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS' ;
4289
4290 -- Added for bug#10287216
4291
4292 -- 2013 TAX YEAR CHANGES.
4293
4294
4295 CURSOR csr_3922_rep_err( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4296 IS
4297 SELECT pai2.action_information14
4298 FROM pay_action_information pai
4299 , pay_action_information pai2
4300 WHERE pai.action_context_id = p_asgn_action_id
4301 AND pai.action_context_type = 'AAP'
4302 AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4303 AND pai.action_information2 in ('IRP5','IT3(a)')
4304 AND pai2.action_context_id = pai.action_context_id
4305 AND pai2.action_context_type = pai.action_context_type
4306 AND pai2.action_information30 = pai.action_information30
4307 AND pai2.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4308 AND pai2.action_information14 is not null;
4309
4310 -- 2013 TAX YEAR CHANGES.
4311
4312 rec_info tax_info%rowtype;
4313 --rec_ret_fund_ls chk_ret_fund_ls%rowtype;
4314 l_msgtext varchar2(2000);
4315 l_empno per_all_people_f.employee_number%type;
4316 l_assgno per_all_assignments_f.assignment_number%type;
4317 l_count number(1):=0;
4318 l_main_cert_num varchar2(30);
4319 l_cert_type varchar2(10);
4320 l_code number(4);
4321 l_index varchar2(50);
4322 l_cert_num varchar2(30);
4323 l_num_ded_codes number(2);
4324 l_num_inc_codes number(2);
4325 l_num_inc_temp number(2);
4326 l_paye_ref_num number(10);
4327 l_tax_code_ind varchar2(1);
4328 l_run_ass_act_id number;
4329 l_run_action_seq number;
4330 l_directive1 varchar2(100);
4331 l_ass_act_id pay_assignment_actions.assignment_action_id%type;
4332 l_leg_param pay_payroll_actions.legislative_parameters%type;
4333 l_rec_count varchar2(100);
4334 l_age varchar2(100);
4335 l_4116_val number;
4336
4337 l_3922_rep_err varchar2(1); -- 2013 TAX YEAR CHANGES.
4338
4339 type cross_val_tab is table of number index by binary_integer;
4340 --Index in above table is temp certificate num + code
4341 cross_val_t cross_val_tab;
4342
4343 -- Code 4116 is Applicable only if nature of person in A/B/C/N
4344 CURSOR csr_4116_nature_of_person( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4345 IS
4346 SELECT 'X'
4347 FROM pay_action_information pai
4348 , pay_action_information pai2
4349 WHERE pai.action_context_id = p_asgn_action_id
4353 AND pai.action_information3 not in ('A','B','C','N')
4350 AND pai.action_context_type = 'AAP'
4351 AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4352 AND pai.action_information2 in ('IRP5','IT3(a)')
4354 AND pai2.action_context_id = pai.action_context_id
4355 AND pai2.action_context_type = pai.action_context_type
4356 AND pai2.action_information30 = pai.action_information30
4357 AND pai2.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4358 AND pai2.action_information13 is not null;
4359
4360 -- Code 4116 is Mandatory if any value specified for Medical Scheme Contributions (4005) and age is below 65
4361 -- Code 4116 is Must not be there if any value specified for Medical Scheme Contributions (4005) and age is above 65
4362 CURSOR csr_4116_4005_age( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4363 IS
4364 SELECT decode (sign (add_months (to_date (pai.action_information11, 'YYYYMMDD'), 780) - to_date (pai.action_information4 || '-03-01', 'yyyy-mm-dd')-1), 1
4365 , 'B', 'A') age
4366 , pai3.action_information13
4367 FROM pay_action_information pai
4368 , pay_action_information pai2
4369 , pay_action_information pai3
4370 WHERE pai.action_context_id = p_asgn_action_id
4371 AND pai.action_context_type = 'AAP'
4372 AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4373 AND pai.action_information2 in ('IRP5','IT3(a)')
4374 AND pai2.action_context_id = pai.action_context_id
4375 AND pai2.action_context_type = pai.action_context_type
4376 AND pai2.action_information30 = pai.action_information30
4377 AND pai2.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
4378 AND pai2.action_information2 = '4005'
4379 AND trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai2.action_information5,0)))) <> 0
4380 AND pai3.action_context_id = pai.action_context_id
4381 AND pai3.action_context_type = pai.action_context_type
4382 AND pai3.action_information30 = pai.action_information30
4383 AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS';
4384
4385 -- Code 4115 is only mandatory if there is a value for 3901,3915, 3920, 3921 or 3922 AND 4150 does not have a value
4386 CURSOR csr_4115_is_mandatory( p_asgn_action_id pay_assignment_actions.assignment_action_id%type)
4387 IS
4388 SELECT 'X'
4389 FROM pay_action_information pai
4390 , pay_action_information pai2
4391 , pay_action_information pai3
4392 WHERE pai.action_context_id = p_asgn_action_id
4393 AND pai.action_context_type = 'AAP'
4394 AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
4395 AND pai.action_information2 in ('IRP5','IT3(a)')
4396 AND pai2.action_context_id = pai.action_context_id
4397 AND pai2.action_context_type = pai.action_context_type
4398 AND pai2.action_information30 = pai.action_information30
4399 AND pai2.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
4400 AND pai2.action_information2 in ('3901','3915','3920','3921','3922')
4401 AND trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai2.action_information5,0)))) <> 0
4402 AND pai3.action_context_id = pai2.action_context_id
4403 AND pai3.action_context_type = pai2.action_context_type
4404 AND pai3.action_information30 = pai2.action_information30
4405 AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
4406 AND pai3.action_information9 is null
4407 AND pai3.action_information5 is null;
4408
4409 begin
4410 retcode := 0;
4411 -- hr_utility.trace_on(null,'ZATYEVL');
4412 FND_FILE.PUT_LINE(FND_FILE.LOG,'In validate_tye_data');
4413 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_payroll_action_id :' || p_payroll_action_id);
4414 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside validate_tye_data');
4415
4416 --For ITREG no validations required as codes are not archived.
4417 select legislative_parameters
4418 into l_leg_param
4419 from pay_payroll_actions
4420 where payroll_action_id = p_payroll_action_id;
4421
4422 --For ITREG certificate dont do any validations
4423 if pay_za_irp5_archive_pkg.get_parameter('CERT_TYPE', l_leg_param)=2 then
4424 hr_utility.set_location('ITREG Certificate',10);
4425 return;
4426 end if;
4427
4428 /*Loop through all Assignments for given payroll_action_id*/
4429 FOR rec_asgn IN asgn_for_payroll_action_cur
4430 LOOP
4431 l_count:=0;
4432 l_num_inc_codes:=0;
4433 l_ass_act_id:=rec_asgn.assignment_action_id;
4434
4435 --Fetch employee number and assignment number
4436 FOR emp_num IN emp_number_cur(l_ass_act_id)
4437 LOOP
4438 l_empno := emp_num.empno ;
4439 l_assgno:= emp_num.assgno;
4440 END LOOP;
4441 hr_utility.set_location('Processing Employee:'||l_empno,15);
4442
4443 --Fetch the temporary number generated for main certificate
4444 hr_utility.set_location('Fetching Main certificate Number',15);
4445 open csr_cert_num(l_ass_act_id);
4446 fetch csr_cert_num into l_main_cert_num,l_cert_type;
4447 close csr_cert_num;
4448
4449 hr_utility.set_location('l_main_cert_num:'||l_main_cert_num,15);
4450 hr_utility.set_location('l_cert_type: '||l_cert_type,15);
4451
4452 -- Check whether the SITE PAYE split exists
4453 hr_utility.set_location('Fetching Tax Details',15);
4454
4455
4456 open tax_info(l_ass_act_id,l_main_cert_num);
4457 fetch tax_info into rec_info;
4458 close tax_info;
4459 hr_utility.set_location('Fetched Tax Details',15);
4460
4461 /* The check should be for TYE or if employee is terminated
4462 For normal employee in Mid TYE this check should not be performed*/
4463 if p_period_recon = '02' and rec_info.tax <>0 and (rec_info.site=0 and rec_info.paye=0) then
4464 l_count:=1;
4465 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4466 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4467 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4468 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4469 fnd_message.set_name('PAY', 'PY_ZA_NO_SITE_PAYE_SPLIT');
4470 fnd_message.set_token('EMPNO',l_empno);
4471 l_msgtext := fnd_message.get('Y');
4472 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Tax Amount : '||rec_info.tax) ;
4473 FND_FILE.PUT_LINE(FND_FILE.LOG, ' SITE balance : '||rec_info.site) ;
4474 FND_FILE.PUT_LINE(FND_FILE.LOG, ' PAYE balance : '||rec_info.paye) ;
4475 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4476 end if;
4477
4478
4479 --Validate negative income in the main certificate
4480 hr_utility.set_location('Validating negative income for main certificate',20);
4481 for rec_neg_income in csr_neg_income(l_ass_act_id, l_main_cert_num)
4482 loop
4483 if l_count <>1 then
4484 l_count:=1;
4485 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4486 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4487 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4488 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4489 end if;
4490 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4491 fnd_message.set_token('EMPno',l_empno);
4492 fnd_message.set_token('SARScode',rec_neg_income.code);
4493 l_msgtext := fnd_message.get('Y');
4494 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4495 end loop;
4496
4497 --Validate negative lump sums in the main certificate
4498 hr_utility.set_location('Validating negative income for lump sum',20);
4499 for rec_neg_lmpsm in csr_neg_lmpsm(l_ass_act_id, l_main_cert_num)
4500 loop
4501 if l_count <>1 then
4502 l_count:=1;
4503 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4504 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4505 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4506 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4507 end if;
4508
4509 -- To Be Advised directive has negative value
4510 if rec_neg_lmpsm.to_be_adv_val < 0 then
4511 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_neg_lmpsm.to_be_adv);
4512 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4513 fnd_message.set_token('EMPno',l_empno);
4514 fnd_message.set_token('SARScode',rec_neg_lmpsm.code);
4515 l_msgtext := fnd_message.get('Y');
4516 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4517 end if;
4518
4519 --Directive 1 has negative value
4520 if rec_neg_lmpsm.value1 < 0 then
4521 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_neg_lmpsm.direct1);
4522 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4523 fnd_message.set_token('EMPno',l_empno);
4524 fnd_message.set_token('SARScode',rec_neg_lmpsm.code);
4525 l_msgtext := fnd_message.get('Y');
4526 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4527 end if;
4528
4529 --Directive 2 has negative value
4530 if rec_neg_lmpsm.value2 < 0 then
4531 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_neg_lmpsm.direct2);
4532 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4533 fnd_message.set_token('EMPno',l_empno);
4534 fnd_message.set_token('SARScode',rec_neg_lmpsm.code);
4535 l_msgtext := fnd_message.get('Y');
4536 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4537 end if;
4538
4539 --Directive 3 has negative value
4540 if rec_neg_lmpsm.value3 < 0 then
4541 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_neg_lmpsm.direct3);
4542 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4543 fnd_message.set_token('EMPno',l_empno);
4544 fnd_message.set_token('SARScode',rec_neg_lmpsm.code);
4545 l_msgtext := fnd_message.get('Y');
4546 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4547 end if;
4548 end loop;
4549
4550 --Validate negative lump sums in the lump sum certificate
4551 hr_utility.set_location('Validating negative income for lump sum',20);
4552 for rec_neg_lmpsm in csr_neg_lmpsm2(l_ass_act_id, l_main_cert_num)
4553 loop
4554 if l_count <>1 then
4555 l_count:=1;
4556 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4557 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4558 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4559 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4560 end if;
4561
4562 -- To Be Advised directive has negative value
4563 if rec_neg_lmpsm.value < 0 then
4564 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_neg_lmpsm.direct1);
4565 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4566 fnd_message.set_token('EMPno',l_empno);
4567 fnd_message.set_token('SARScode',rec_neg_lmpsm.code);
4568 l_msgtext := fnd_message.get('Y');
4569 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4570 end if;
4571 end loop;
4572
4573 for rec_neg_tax in chk_neg_tax(l_ass_act_id)
4574 loop
4575 if l_count <>1 then
4576 l_count:=1;
4577 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4578 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4579 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4580 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4581 end if;
4582 if rec_neg_tax.temp_cert_num = l_main_cert_num then
4583 l_directive1:='Certificate: Main Certificate';
4584 else
4585 l_directive1:='Tax Directive Number '||rec_neg_tax.direct1;
4586 end if;
4587
4588 --Site has negative value
4589 if rec_neg_tax.site < 0 then
4590 FND_FILE.PUT_LINE(FND_FILE.LOG, l_directive1);
4591 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4592 fnd_message.set_token('EMPno',l_empno);
4593 fnd_message.set_token('SARScode','4101');
4594 l_msgtext := fnd_message.get('Y');
4595 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4596 end if;
4597
4598 --Paye has negative value
4599 if rec_neg_tax.paye < 0 then
4600 FND_FILE.PUT_LINE(FND_FILE.LOG, l_directive1);
4601 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4602 fnd_message.set_token('EMPno',l_empno);
4603 fnd_message.set_token('SARScode','4102');
4604 l_msgtext := fnd_message.get('Y');
4605 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4606 end if;
4607
4608 --Tax on Retirement Fund has negative value
4609 if rec_neg_tax.tax_ret < 0 then
4610 FND_FILE.PUT_LINE(FND_FILE.LOG, l_directive1);
4611 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4612 fnd_message.set_token('EMPno',l_empno);
4613 fnd_message.set_token('SARScode','4115');
4614 l_msgtext := fnd_message.get('Y');
4615 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4616 end if;
4617
4618 --UIF has negative value
4619 if rec_neg_tax.uif < 0 then
4620 FND_FILE.PUT_LINE(FND_FILE.LOG, l_directive1);
4621 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4622 fnd_message.set_token('EMPno',l_empno);
4623 fnd_message.set_token('SARScode','4141');
4624 l_msgtext := fnd_message.get('Y');
4625 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4626 end if;
4627
4628 --SDL has negative value
4629 if rec_neg_tax.sdl < 0 then
4630 FND_FILE.PUT_LINE(FND_FILE.LOG, l_directive1);
4631 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4632 fnd_message.set_token('EMPno',l_empno);
4633 fnd_message.set_token('SARScode','4142');
4634 l_msgtext := fnd_message.get('Y');
4635 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4636 end if;
4637
4638 --total has negative value
4639 if rec_neg_tax.total < 0 then
4640 FND_FILE.PUT_LINE(FND_FILE.LOG, l_directive1);
4641 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4642 fnd_message.set_token('EMPno',l_empno);
4643 fnd_message.set_token('SARScode','4149');
4644 l_msgtext := fnd_message.get('Y');
4645 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4646 end if;
4647
4648 end loop;
4649
4650 --Validate negative deductions in the main certificate
4651 hr_utility.set_location('Validating negative deductions in main certificate',20);
4652 for rec_neg_deduct in csr_neg_deduct(l_ass_act_id, l_main_cert_num)
4653 loop
4654 if l_count <>1 then
4655 l_count:=1;
4656 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4657 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4658 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4659 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4660 end if;
4661 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
4662 fnd_message.set_token('EMPno',l_empno);
4663 fnd_message.set_token('SARScode',rec_neg_deduct.code);
4664 l_msgtext := fnd_message.get('Y');
4665 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
4666 end loop;
4667
4668 -- Added for bug#10287216
4669
4670 for rec_fetch_rfi_nrfi in csr_fetch_rfi_nrfi(l_ass_act_id)
4671
4672 loop
4673
4674 -- Validate Non-Taxable Income is not negative.
4675 hr_utility.set_location('Validating Non-Taxable Income',20);
4676
4677 if rec_fetch_rfi_nrfi.Non_Taxable_Income < 0 then
4678
4679 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Non-Taxable Income is negative for ');
4680 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4681 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4682 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Non-Taxable Income : ' || rec_fetch_rfi_nrfi.Non_Taxable_Income);
4683
4684 end if;
4685
4686 hr_utility.set_location('Validated Non-Taxable Income',20);
4687
4688
4689 -- Validate Gross Retirement Funding Income is not negative
4690 hr_utility.set_location('Validating Gross Retirement Funding Income',20);
4691
4692
4693 if rec_fetch_rfi_nrfi.Gross_Retire_Fund_Income < 0 then
4694
4695 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gross Retirement Funding Income is negative for ');
4696 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4697 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4698 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gross Retirement Funding Income : ' || rec_fetch_rfi_nrfi.Gross_Retire_Fund_Income);
4699
4700 end if;
4701
4702
4703 hr_utility.set_location('Validated Gross Retirement Funding Income',20);
4704
4705
4706 -- Validate Gross Non-Retirement Funding Income is not negative
4707 hr_utility.set_location('Validating Gross Non-Retirement Funding Income ',20);
4708
4709
4710 if rec_fetch_rfi_nrfi.Gross_Non_Retire_Fund_In < 0 then
4711
4712 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gross Non-Retirement Funding Income is negative for :');
4713 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4714 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4715 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gross Non-Retirement Funding Income : ' || rec_fetch_rfi_nrfi.Gross_Non_Retire_Fund_In);
4716
4717 end if ;
4718
4719 hr_utility.set_location('Validated Gross Non-Retirement Funding Income ',20);
4720
4721 end loop ;
4722
4723 -- Added for bug#10287216
4724
4725 --Employee not on package structure but has PKG balances
4726 hr_utility.set_location('Check whether Emp on PKG Structure',25);
4730 l_count:=1;
4727 for rec_pkg_balances in fetch_pkg_balances(l_ass_act_id, l_main_cert_num)
4728 loop
4729 if l_count <>1 then
4731 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4732 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4733 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4734 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4735 end if;
4736
4737 fnd_message.set_name('PAY', 'PY_ZA_PKG_BAL_NT_ALLOW');
4738 fnd_message.set_token('EMPno',l_empno);
4739 l_msgtext := fnd_message.get('Y');
4740 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4741
4742 select max(paa.action_sequence)
4743 into l_run_action_seq
4744 from pay_assignment_actions paa,
4745 pay_payroll_actions ppa,
4746 per_time_periods ptp
4747 where paa.assignment_id = rec_asgn.assignment_id
4748 and paa.action_status IN ('C','S') -- 10376999
4749 and paa.payroll_action_id = ppa.payroll_action_id
4750 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
4751 and ppa.action_status = 'C'
4752 and ppa.time_period_id = ptp.time_period_id
4753 and ptp.prd_information1 = p_tax_year;
4754
4755 select assignment_action_id
4756 into l_run_ass_act_id
4757 from pay_assignment_actions
4758 where assignment_id = rec_asgn.assignment_id
4759 and action_sequence = l_run_action_seq;
4760
4761
4762 hr_utility.set_location('Employee not on Package structure but has PKG balances',25);
4763 FOR pkg_ele IN fetch_pkg_ele(l_run_ass_act_id)
4764 LOOP
4765 fnd_message.set_name('PAY', 'PY_ZA_NEW_ELE_FEED_PKG_BAL');
4766 fnd_message.set_token('ELEMENTname',pkg_ele.element_name);
4767 l_msgtext := fnd_message.get('Y');
4768 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4769 END LOOP;
4770 end loop; --end loop for fetch_pkg_balances
4771
4772 -- Code 4115 must be present only if code 3901, 3915, 3920, 3921 are present. (added 3901 for bug 11899934)
4773 hr_utility.set_location('Tax on Retirement Fund check',30);
4774 /*for rec_tax_ret_fund_ls in chk_tax_ret_fund_ls(l_ass_act_id)
4775 loop
4776 open chk_ret_fund_ls(l_ass_act_id, rec_tax_ret_fund_ls.temp_cert_num);
4777 fetch chk_ret_fund_ls into rec_ret_fund_ls;
4778 if chk_ret_fund_ls%notfound then
4779 if l_count <>1 then
4780 l_count:=1;
4781 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4782 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4783 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4784 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4785 end if;
4786 --If main certificate has code 4115 but 3901,3915,3920,3921 not present (added 3901 for bug 11899934)
4787 if rec_tax_ret_fund_ls.temp_cert_num = l_main_cert_num then
4788 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Certificate ' || 'Main Certificate');
4789 else
4790 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_tax_ret_fund_ls.direct1);
4791 end if;
4792 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4115 must not be present if Codes 3901, 3915, 3920, and 3921 are not present.'); -- (added 3901 for bug 11899934)
4793 end if;
4794 close chk_ret_fund_ls;
4795 end loop;*/
4796 open csr_4115_is_mandatory(l_ass_act_id);
4797 fetch csr_4115_is_mandatory into l_rec_count;
4798 if csr_4115_is_mandatory%found then
4799 if l_count <>1 then
4800 l_count:=1;
4801 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4802 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4803 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4804 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4805 end if;
4806 l_msgtext :='Code 4115 is mandatory if there is a value for 3901, 3915, 3920, 3921 and 3922 AND 4150 does not have a value';
4807 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4808 end if;
4809 close csr_4115_is_mandatory;
4810 hr_utility.set_location('End Tax on Retirement Fund check',35);
4811
4812
4813 --Delete the cross validation table
4814 cross_val_t.delete;
4815 cross_val_t(3810):=0;
4816 cross_val_t(3860):=0;
4817 cross_val_t(3813):=0;
4818 cross_val_t(3863):=0;
4819 cross_val_t(4005):=0;
4820 cross_val_t(4024):=0;
4821 cross_val_t(4474):=0;
4822 cross_val_t(4493):=0;
4823
4824 hr_utility.set_location('Before populating cross validation table',40);
4825 --Populate the cross validation table
4826 FOR rec_cv_inc_codes IN cross_val_inc_codes(l_ass_act_id , l_main_cert_num)
4827 LOOP
4828 cross_val_t(rec_cv_inc_codes.code)
4829 := rec_cv_inc_codes.value;
4830 END LOOP;
4831
4832 hr_utility.set_location('Intermediate cross validation table',40);
4833
4834 FOR rec_cv_ded_codes IN cross_val_ded_codes(l_ass_act_id, l_main_cert_num)
4835 LOOP
4839
4836 cross_val_t(rec_cv_ded_codes.code)
4837 := rec_cv_ded_codes.value;
4838 END LOOP;
4840 hr_utility.set_location('After cross validation table',40);
4841
4842 -- Code 3810 must be less than code 4474
4843 /* Bug 9939519 added below condition as per the new BRS version 2.0 published on 14-Jul-2010 */
4844
4845 /*
4846 The value for Code 3810 / 3860 must be less than the value for code 4474,
4847 if the year of assessment is equal to 2007 < 2011.
4848 The value of code 3810 / 3860 must be equal to the value for code 4474,
4849 if the year of assessment is >2010.
4850 */
4851
4852 if p_tax_year < 2011 Then
4853 if (((nvl(cross_val_t(3810),0) + nvl(cross_val_t(3860),0)) >= cross_val_t(4474)) AND
4854 ((nvl(cross_val_t(3810),0) + nvl(cross_val_t(3860),0)) <> 0 OR cross_val_t(4474)<>0)) then
4855 if l_count <>1 then
4856 l_count:=1;
4857 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4858 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4859 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4860 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4861 end if;
4862 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3810 / 3860 must be less than Code 4474.');
4863 end if;
4864 else
4865 if (nvl(cross_val_t(3810),0) + nvl(cross_val_t(3860),0)) <> nvl(cross_val_t(4474),0) then
4866 if l_count <>1 then
4867 l_count:=1;
4868 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4869 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4870 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4871 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4872 end if;
4873 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3810 / 3860 must be equal to the Code 4474.');
4874 end if;
4875 end if;
4876
4877 hr_utility.set_location('check whether 3813 greater than 4024',45);
4878 --Code 3813/3863 must be greater than or equal to the value of code 4024
4879 if ((case when cross_val_t(3813)=0 then cross_val_t(3863) else cross_val_t(3813) end) < cross_val_t(4024)) then
4880 if l_count <>1 then
4881 l_count:=1;
4882 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4883 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4884 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4885 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4886 end if;
4887 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Codes 3813 and 3863 must not be less than Code 4024.');
4888 end if;
4889
4890 --Code 3810/3860 and 4474 is not allowed if code 4493 is specified
4891 hr_utility.set_location('check whether 3810 OR 4474 present if 4493 specified',45);
4892 if cross_val_t(4493) <> 0 then
4893 if cross_val_t(3810) <>0 OR cross_val_t(3860) <>0 OR cross_val_t(4474) <>0 then
4894 if l_count <>1 then
4895 l_count:=1;
4896 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4897 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4898 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4899 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4900 end if;
4901 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Codes 3810, 3860, and 4474 not allowed if Code 4493 is specified.');
4902 end if;
4903 end if;
4904
4905 /* --Already handled in message Code 3813/3863 must be greater than or equal to the value of code 4024
4906 --Code 4024 cannot be greater than the sum of the values for codes 3813 and 3863
4907 hr_utility.set_location('Check whether 4024 greater than 3813/3863',45);
4908 if cross_val_t(4024) > cross_val_t(3813) OR cross_val_t(4024) > cross_val_t(3863) then
4909 if l_count <>1 then
4910 l_count:=1;
4911 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4912 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4913 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4914 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4915 end if;
4916 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4024 must be less than Codes 3813 and 3863.');
4917 end if;
4918 */
4919
4920 --Code 4474 is mandatory if an amount is specified for code 3810/3860
4921 hr_utility.set_location('4474 mandatory for 3810',45);
4922 if (cross_val_t(3810) <> 0 OR cross_val_t(3860)<>0) AND cross_val_t(4474)=0 then
4923 if l_count <>1 then
4924 l_count:=1;
4925 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4926 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4927 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4928 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4929 end if;
4930 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4474 must be greater than zero if Code 3810 or Code 3860 has a value.');
4931 end if;
4932
4933 --Code 4005 is mandatory if an amount is specified for code 3810/3860
4934 hr_utility.set_location('4005 mandatory for 3810',45);
4935 if (cross_val_t(3810) <> 0 OR cross_val_t(3860)<>0) AND cross_val_t(4005)=0 then
4939 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4936 if l_count <>1 then
4937 l_count:=1;
4938 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4940 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4941 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4942 end if;
4943 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4005 must be greater than zero if Code 3810 or Code 3860 has a value.');
4944 end if;
4945
4946
4947 -- Check that number of deduction codes must not exceed 7.
4948 hr_utility.set_location('Count deduction codes',45);
4949 open csr_num_ded_codes(l_ass_act_id , l_main_cert_num);
4950 fetch csr_num_ded_codes into l_num_ded_codes;
4951 close csr_num_ded_codes;
4952
4953 if l_num_ded_codes > 7 then
4954 if l_count <>1 then
4955 l_count:=1;
4956 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4957 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4958 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4959 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4960 end if;
4961 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NUM_CODES');
4962 fnd_message.set_token('EMPNO',l_empno);
4963 fnd_message.set_token('COUNT','7');
4964 fnd_message.set_token('TYPE','deductions and/or contribution');
4965 l_msgtext := fnd_message.get('Y');
4966 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4967 end if;
4968
4969 -- Check that number of income codes must not exceed 13.
4970 hr_utility.set_location('Count income codes',50);
4971 open csr_num_inc_codes(l_ass_act_id , l_main_cert_num);
4972 fetch csr_num_inc_codes into l_num_inc_temp;
4973 close csr_num_inc_codes;
4974
4975 open csr_num_3907(l_ass_act_id , l_main_cert_num);
4976 fetch csr_num_3907 into l_num_inc_codes;
4977 close csr_num_3907;
4978
4979 l_num_inc_codes:=nvl(l_num_inc_codes,0) + nvl(l_num_inc_temp,0);
4980
4981 if l_num_inc_codes > 13 then
4982 if l_count <>1 then
4983 l_count:=1;
4984 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4985 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
4986 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
4987 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
4988 end if;
4989 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NUM_CODES');
4990 fnd_message.set_token('EMPNO',l_empno);
4991 fnd_message.set_token('COUNT','13');
4992 fnd_message.set_token('TYPE','income');
4993 l_msgtext := fnd_message.get('Y');
4994 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
4995 end if;
4996
4997 --If the employer is not register for PAYE (Paye Reference number doesnot start with 7)
4998 --but any employee has code 4101,4102,4115 then it is invalid
4999 hr_utility.set_location('Check PAYE Ref Num',60);
5000 open csr_paye_ref_num;
5001 fetch csr_paye_ref_num into l_paye_ref_num;
5002 if csr_paye_ref_num%notfound then
5003 l_paye_ref_num:=7;
5004 end if;
5005 close csr_paye_ref_num;
5006
5007 hr_utility.set_location('Retrieved PAYE Ref Num',60);
5008 if substr(l_paye_ref_num,1,1) <> 7 then
5009 open tax_codes(l_ass_act_id);
5010 fetch tax_codes into l_tax_code_ind;
5011 close tax_codes;
5012
5013 if l_tax_code_ind is not null then
5014 if l_count <>1 then
5015 l_count:=1;
5016 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5017 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5018 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
5019 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
5020 end if;
5021 fnd_message.set_name('PAY', 'PY_ZA_INVALID_TAX_PAYENUM_COM');
5022 fnd_message.set_token('EMPNO',l_empno);
5023 l_msgtext := fnd_message.get('Y');
5024 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
5025 end if;
5026 end if;
5027
5028 -- Aug 2012 Changes
5029 open csr_4116_nature_of_person(l_ass_act_id);
5030 fetch csr_4116_nature_of_person into l_rec_count;
5031 if csr_4116_nature_of_person%found then
5032 if l_count <>1 then
5033 l_count:=1;
5034 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5035 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5036 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
5037 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
5038 end if;
5039 l_msgtext :='Code 4116 may only have a value if Nature of Person is A, B, C or N';
5040 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
5041 end if;
5042 close csr_4116_nature_of_person;
5043
5044 open csr_4116_4005_age(l_ass_act_id);
5045 fetch csr_4116_4005_age into l_age,l_4116_val;
5046 if csr_4116_4005_age%found then
5047 if (l_age = 'B' and l_4116_val is null) THEN
5048 if l_count <>1 then
5049 l_count:=1;
5050 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5051 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5052 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
5053 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
5054 end if;
5055 l_msgtext :='Code 4116 must have a value if any value is specified for Medical Scheme Contributions (4005) and age is below 65';
5056 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
5057 elsif ( l_age = 'A' and l_4116_val is not null ) THEN
5058 if l_count <>1 then
5059 l_count:=1;
5060 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5061 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5062 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
5063 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
5064 end if;
5065 l_msgtext :='Code 4116 must not have a value if any value is specified for Medical Scheme Contributions (4005) and age is 65 or above';
5066 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
5067 end if;
5068
5069 end if;
5070 close csr_4116_4005_age;
5071
5072 open csr_3922_rep_err(l_ass_act_id);
5073 fetch csr_3922_rep_err into l_3922_rep_err;
5074 if l_3922_rep_err = 'Y' then
5075 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5076 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5077 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
5078 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
5079 l_msgtext :='Value for code 3922 is greater than R300 000 therefore amount reflected under code 3922 should not be reported under code 3696';
5080 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
5081 elsif l_3922_rep_err = 'N' then
5082 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5083 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5084 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
5085 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
5086 l_msgtext :='Value for code 3922 is less than or equal to R300 000 therefore amount reflected under code 3922 should be reported under code 3696';
5087 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
5088 end if;
5089
5090 close csr_3922_rep_err;
5091
5092 END LOOP; --end of assignment loop
5093
5094 FND_FILE.PUT_LINE(FND_FILE.LOG,'End of log file');
5095 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
5096 -- hr_utility.trace_off;
5097 EXCEPTION
5098 WHEN OTHERS then
5099 errbuf := substr(SQLERRM,1,255);
5100 retcode := sqlcode;
5101
5102 end VALIDATE_TYE_DATA_EOY2010;
5103
5104 end PAY_ZA_EOY_VAL;