1 Package body PAY_ZA_EOY_VAL as
2 /* $Header: pyzatyev.pkb 120.9.12010000.9 2008/09/11 08:35:29 rbabla ship $ */
3 -- Pacakge Body
4 Function modulus_10_test
5 (p_tax_number in number) return number
6 -- return 1 if correct, else return 0
7 is
8
9 nine_digits number(9);
10 tax_no number(10);
11 n1 number(2);
12 n2 number(1):= substr(p_tax_number,2,1);
13 n3 number(2):= 2 * substr(p_tax_number,3,1);
14 n4 number(1):= substr(p_tax_number,4,1);
15 n5 number(2):= 2 * substr(p_tax_number,5,1);
16 n6 number(1):= substr(p_tax_number,6,1);
17 n7 number(2):= 2 * substr(p_tax_number,7,1);
18 n8 number(1):= substr(p_tax_number,8,1);
19 n9 number(2):= 2* substr(p_tax_number,9,1);
20 n10 number(2):= substr(p_tax_number,10,1);
21 temp number(2);
22 temp1 number(2);
23 res number(2);
24 begin
25 -- Store the 10 digit tax number
26 -- only the first nine chars are used for the test
27
28
29 tax_no:=p_tax_number;
30 If ((length(tax_no) <> 10) or (tax_no is NULL)) then
31 return 0;
32 elsif tax_no between 7000000000 and 7980000000 then
33 -- replace the first digit 7 by 4
34 nine_digits := substr(tax_no,2,10);
35 tax_no := 4||nine_digits;
36 end if;
37
38 -- otherwise do not replace the first digit
39 -- Multiply the first number and thereafter every second number by 2
40 -- if the result is greater than 9, add the individual digits to get
41 -- the final answer
42
43 n1 := 2 * substr(tax_no,1,1);
44 if n1 > 9 then
45 n1 := substr(n1,1,1) + substr(n1,2,1);
46 end if;
47 if n3 > 9 then
48 n3 := substr(n3,1,1) + substr(n3,2,1);
49 end if;
50 if n5 > 9 then
51 n5 := substr(n5,1,1) + substr(n5,2,1);
52 end if;
53 if n7 > 9 then
54 n7 := substr(n7,1,1) + substr(n7,2,1);
55 end if;
56 if n9 > 9 then
57 n9 := substr(n9,1,1) + substr(n9,2,1);
58 end if;
59 temp := n1 + n2 + n3 + n4 + n5 + n6 + n7 + n8 + n9;
60
61 -- deduct the total result from the next full ten to get the last digit
62 if mod(temp,10) > 0 then
63 temp1 := temp - mod(temp,10) + 10;
64 else
65 temp1 := temp;
66 end if;
67 res := temp1 - temp;
68 if res = n10 then
69 return 1;
70 else
71 return 0;
72 end if;
73
74 exception
75
76 when value_error then
77 return 0;
78
79 when others then
80 raise_application_error(-20101,'PAY_ZA_EOY_VAL.MODULUS_10_TEST exception');
81
82
83
84 end modulus_10_test;
85
86 Function modulus_10_test
87 (p_tax_number in varchar2) return number
88 -- return 1 if correct, else return 0
89 is
90
91 nine_digits varchar2(9);
92 tax_no varchar2(10);
93 n1 varchar2(2);
94 n2 varchar2(1):= substr(p_tax_number,2,1);
95 n3 varchar2(2):= 2 * substr(p_tax_number,3,1);
96 n4 varchar2(1):= substr(p_tax_number,4,1);
97 n5 varchar2(2):= 2 * substr(p_tax_number,5,1);
98 n6 varchar2(1):= substr(p_tax_number,6,1);
99 n7 varchar2(2):= 2 * substr(p_tax_number,7,1);
100 n8 varchar2(1):= substr(p_tax_number,8,1);
101 n9 varchar2(2):= 2* substr(p_tax_number,9,1);
102 n10 varchar2(2):= substr(p_tax_number,10,1);
103 temp varchar2(2);
104 temp1 varchar2(2);
105 res varchar2(2);
106 begin
107 -- Store the 10 digit tax number
108 -- only the first nine chars are used for the test
109
110
111 tax_no:=p_tax_number;
112 If ((length(tax_no) <> 10) or (tax_no is NULL)) then
113 return 0;
114 elsif tax_no between 7000000000 and 7980000000 then
115 -- replace the first digit 7 by 4
116 nine_digits := substr(tax_no,2,10);
117 tax_no := 4||nine_digits;
118 end if;
119
120 -- otherwise do not replace the first digit
121 -- Multiply the first number and thereafter every second number by 2
122 -- if the result is greater than 9, add the individual digits to get
123 -- the final answer
124
125 n1 := 2 * substr(tax_no,1,1);
126 if n1 > 9 then
127 n1 := substr(n1,1,1) + substr(n1,2,1);
128 end if;
129 if n3 > 9 then
130 n3 := substr(n3,1,1) + substr(n3,2,1);
131 end if;
132 if n5 > 9 then
133 n5 := substr(n5,1,1) + substr(n5,2,1);
134 end if;
135 if n7 > 9 then
136 n7 := substr(n7,1,1) + substr(n7,2,1);
137 end if;
138 if n9 > 9 then
139 n9 := substr(n9,1,1) + substr(n9,2,1);
140 end if;
141 temp := n1 + n2 + n3 + n4 + n5 + n6 + n7 + n8 + n9;
142
143 -- deduct the total result from the next full ten to get the last digit
144 if mod(temp,10) > 0 then
145 temp1 := temp - mod(temp,10) + 10;
146 else
147 temp1 := temp;
148 end if;
149 res := temp1 - temp;
150 if res = n10 then
151 return 1;
152 else
153 return 0;
154 end if;
155
156 exception
157
158 when value_error then
159 return 0;
160
161 when others then
162 raise_application_error(-20101,'PAY_ZA_EOY_VAL.MODULUS_10_TEST exception');
163
164
165
166 end modulus_10_test;
167
168
169 -- Function to check id ID number and Data of birth of a person.
170 --
171 Function check_id_dob(p_id_number in number,p_dob in date) return number
172 is
173 -- return 0 if false, else return 1
174
175 id_six_nos number(6);
176 dob_six_nos varchar2(6);
177 begin
178 if (p_id_number is NULL OR p_dob is NULL) OR (length(p_id_number) <> 13) then
179 return 0;
180 else
181 -- Get the first six characters of the ID number
182 -- Get the data of birth in YYMMDD format and compare
183
184 id_six_nos := substr(p_id_number,1,6);
185
186 dob_six_nos := to_char(p_dob,'YYMMDD');
187 if id_six_nos <> to_number(dob_six_nos) then
188 return 0;
189 else
190 return 1;
191 end if;
192 end if;
193
194 exception
195
196 when others then
197 raise_application_error(-20102,'PAY_ZA_EOY_VAL.CHECK_ID_DOB exception');
198
199
200 end check_id_dob;
201
202 -- Function to check unique IRP5 number
203 Function check_IRP5_no( p_payroll_id in Number
204 ,p_irp5no in varchar2
205 ,p_tax_year in varchar2) return Number
206 -- return 0 if false, else return 1
207 is
208 v_count number;
209 v_tax_start_date varchar2(20);
210 v_tax_end_date varchar2(20);
211 begin
212 If (p_payroll_id is NULL) or (p_irp5no is NULL) or (p_tax_year is NULL) then
213 return 0;
214 else
215
216 get_tax_start_end_dates(p_payroll_id,p_tax_year,v_tax_start_date,v_tax_end_date);
217 --removed the sub query for selecting the max effective date ,since the date tracked
218 --checks would anyway get the latest payroll details
219
220 select count(*)
221 into v_count
222 from pay_all_payrolls_f pap,
223 hr_soft_coding_keyflex scl
224 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)
225 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
226 and scl.segment8 = p_irp5no
227 and to_date(v_tax_end_date,'DD-MM-YYYY') between pap.effective_start_date and pap.effective_end_date;
228 if v_count > 1 then
229 return 0;
230 else
231 return 1;
232 end if;
233 end if;
234
235 exception
236
237 when others then
238 raise_application_error(-20103,'PAY_ZA_EOY_VAL.CHECK_IRP5_NO exception');
239
240
241 end check_IRP5_no;
242
243 -- Prcoedure to get the Tax Year Start Date and End date
244 Procedure get_tax_start_end_dates
245 (p_payroll_id in number
246 ,p_tax_year in varchar2
247 ,p_tax_start_date out nocopy varchar2
248 ,p_tax_end_date out nocopy varchar2) as
249 begin
250
251 select to_char(min(start_date),'dd-mon-yyyy'),to_char(max(end_date),'dd-mon-yyyy')
252 into p_tax_start_date,p_tax_end_date
253 from per_time_periods
254 where payroll_id = p_payroll_id
255 and prd_information1 = p_tax_year;
256
257 exception
258
259 when others then
260 raise_application_error(-20104,'PAY_ZA_EOY_VAL.GET_TAX_START_END_DATE exception');
261
262
263 end get_tax_start_end_dates;
264
265
266
267 -- Procedure to populate the validation messages form FND_NEW_MESSAGES
268 Procedure populate_messages(c_name OUT NOCOPY VARCHAR2,
269 c_ref_no OUT NOCOPY VARCHAR2,
270 c_ref_no_invalid OUT NOCOPY VARCHAR2,
271 c_person_name OUT NOCOPY VARCHAR2,
272 c_telephone OUT NOCOPY VARCHAR2,
273 c_add_line1 OUT NOCOPY VARCHAR2,
274 c_pcode OUT NOCOPY VARCHAR2,
275 c_pcode1 OUT NOCOPY VARCHAR2,
276 trade_name OUT NOCOPY VARCHAR2,
277 paye_no OUT NOCOPY VARCHAR2,
278 paye_no1 OUT NOCOPY VARCHAR2,
279 address OUT NOCOPY VARCHAR2,
280 pcode OUT NOCOPY VARCHAR2,
281 pcode1 OUT NOCOPY VARCHAR2,
282 payroll_number OUT NOCOPY VARCHAR2,
283 nature_entered OUT NOCOPY VARCHAR2,
284 id_passport OUT NOCOPY VARCHAR2,
285 no_id_passport OUT NOCOPY VARCHAR2,
286 sur_trade_name OUT NOCOPY VARCHAR2,
287 cc_no OUT NOCOPY VARCHAR2,
288 sur_first_name OUT NOCOPY VARCHAR2,
289 M_sur_fname OUT NOCOPY VARCHAR2,
290 M_id_pno_fname OUT NOCOPY VARCHAR2,
291 M_cc_trade_name OUT NOCOPY VARCHAR2,
292 M_lname_fname_cc OUT NOCOPY VARCHAR2,
293 invalid_it_no OUT NOCOPY VARCHAR2,
294 birth_id OUT NOCOPY VARCHAR2,
295 legal_entity OUT NOCOPY VARCHAR2,
296 no_site_paye_split OUT NOCOPY VARCHAR2,
297 neg_bal_not_alwd OUT NOCOPY VARCHAR2,
298 clearance_num OUT NOCOPY VARCHAR2,
299 terminate_emp OUT NOCOPY VARCHAR2,
300 town_city OUT NOCOPY VARCHAR2) as
301 begin
302 -- File header record messages
303 c_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_CREATOR');
304 c_ref_no:=fnd_message.get_string('PAY','PY_ZA_ENTER_CREATOR_REF_NO');
305 c_ref_no_invalid:=fnd_message.get_string('PAY','PY_ZA_INVALID_CREATOR_REF_NO');
306 c_person_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_CONTACT_NAME');
307 c_telephone:=fnd_message.get_string('PAY','PY_ZA_ENTER_CONTACT_PHONE_NO');
308 c_add_line1:=fnd_message.get_string('PAY','PY_ZA_ENTER_ADDRESS_LINE1');
309 c_pcode:=fnd_message.get_string('PAY','PY_ZA_ENTER_POSTAL_CODE');
310 c_pcode1:=fnd_message.get_string('PAY','PY_ZA_INVALID_POSTAL_CODE');
311
312 -- Employer validation messages
313 trade_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_TRADING_NAME');
314 paye_no:=fnd_message.get_string('PAY','PY_ZA_ENTER_TAX_REF_NO');
315 paye_no1:=fnd_message.get_string('PAY','PY_ZA_INVALID_TAX_REF_NO');
316 address:=c_add_line1;
317 pcode:= c_pcode;
318 pcode1:=c_pcode1;
319
320 -- Payroll validation message
321 payroll_number:=fnd_message.get_string('PAY','PY_ZA_INVALID_IRP5_NO');
322
323 --Employee validation messages
324 nature_entered:=fnd_message.get_string('PAY','PY_ZA_ENTER_NATURE_PERSON');
325 id_passport:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_AC_ID_PASSNO');
326 no_id_passport:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_B_ID_PASSNO');
327 sur_trade_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_DEFGHK_TRADE');
328 cc_no:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_DEHK_CC_NO');
329
330 sur_first_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_ABC_S_F_NAME');
331 M_sur_fname:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_S_F_NAME');
332 M_id_pno_fname:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_IDPNO_SF_NAM');
333 M_cc_trade_name:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_CC_NO');
334
335 M_lname_fname_cc:=fnd_message.get_string('PAY','PY_ZA_ENTER_NAT_M_FM_NAME_CCNO');
336
337 invalid_it_no:=fnd_message.get_string('PAY','PY_ZA_INVALID_IT_TAX_NO');
338 birth_id:=fnd_message.get_string('PER','HR_ZA_INVALID_NI_DOB');
339 legal_entity:=fnd_message.get_string('PAY','PY_ZA_ENTER_LEGAL_ENTITY');
340
341 no_site_paye_split:=fnd_message.get_string('PAY','PY_ZA_NO_SITE_PAYE_SPLIT');
342 neg_bal_not_alwd :=fnd_message.get_string('PAY','PY_ZA_NEG_BAL_NOT_ALWD');
343 clearance_num:=fnd_message.get_string('PAY','PA_ZA_ENTER_CLEARANCE_NUM');
344 terminate_emp:=fnd_message.get_string('PAY','PA_ZA_TERMINATE_EMP');
345 town_city := fnd_message.get_string('PAY','PAY_ZA_ENTER_TOWN_CITY');
346
347 exception
348
349 when others then
350 raise_application_error(-20105,'PAY_ZA_EOY_VAL.POPULATE_MESSAGES exception');
351
352
353 end populate_messages;
354
355 -- function to convert the decimal separator if it is ',' Eg 123,45 => 123.45
356 function decimal_character_conversion ( amount_char in varchar2) return varchar2 is
357 amount_num number ;
358 amount_ret varchar2(100) ;
359 begin
360 amount_num := to_number(amount_char) ;
361 return amount_char ;
362 exception
363 when others then
364 amount_ret := replace(amount_char,',','.') ;
365 return amount_ret ;
366 end decimal_character_conversion ;
367
368 -- for TYE 2008 write the exceptions to the log file
369 PROCEDURE VALIDATE_TYE_DATA (
370 errbuf out nocopy varchar2,
371 retcode out nocopy number,
372 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
373 p_tax_yr_start_date IN DATE,
374 p_tax_yr_end_date IN DATE
375 )is
376 /* Cursor to select all Income Sars Codes which have negative balances*/
377 g_default_clrno CONSTANT VARCHAR2(11) := '99999999999' ;
378 g_default_dirno CONSTANT VARCHAR2(7) := 'Default' ;
379 g_application_id CONSTANT NUMBER := 801 ;
380
381 CURSOR negative_amt_check_cur(p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
382 select irp5.code,
386 ff_database_items dbi
383 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
384 FROM pay_za_irp5_bal_codes irp5,
385 ff_archive_items arc,
387 WHERE dbi.user_entity_id = arc.user_entity_id
388 and irp5.user_name = dbi.user_name
389 AND arc.context1 = p_asgn_action_id
390 and irp5.balance_sequence = 1
391 and (
392 (irp5.code BETWEEN 3601 AND 3607)
393 OR
394 (irp5.code BETWEEN 3609 AND 3613)
395 or
396 (irp5.code BETWEEN 3615 AND 3617 ) -- 3608 and 3614 are LMPSM balance
397 or
398 (irp5.code BETWEEN 3651 AND 3667)
399 OR
400 (irp5.code BETWEEN 3701 AND 3706)
401 OR
402 (irp5.code BETWEEN 3708 AND 3717) -- 3707 and 3718 are LMPSM balances
403 OR
404 (irp5.code BETWEEN 3751 AND 3768)
405 or
406 (irp5.code BETWEEN 3801 and 3810)
407 or
408 (irp5.code BETWEEN 3851 and 3860)
409 or
410 (irp5.code BETWEEN 3813 and 3863)
411 OR -- 3901 to 3907 are LMPSM balances
412 (irp5.code BETWEEN 3951 and 3957)
413 or
414 (irp5.code BETWEEN 3695 and 3699)
415 OR -- 4001 to 4004, 4006, 4007 are Deduction balances
416 (irp5.code = 4005 )
417 or
418 (irp5.code = 4018)
419 or
420 (irp5.code BETWEEN 4024 and 4025)
421 or
422 (irp5.code BETWEEN 4101 and 4103)
423 or
424 (irp5.code BETWEEN 4472 and 4474)
425 or
426 (irp5.code BETWEEN 4485 and 4487)
427 or
428 (irp5.code = 4493)
429 )
430 group by irp5.code
431 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) < 0 ;
432
433 /* Cursor to fetch all LumpSum Balance Sars Codes ( For Main Certificate) which have neagtive amounts*/
434 CURSOR fetch_lmpsm_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) is
435 select irp5.code,
436 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
437 FROM pay_za_irp5_bal_codes irp5,
438 ff_archive_items arc,
439 ff_database_items dbi,
440 ff_archive_item_contexts faic2,
441 ff_contexts ffc2
442 WHERE irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3908, 3909, 3915)
443 AND irp5.balance_sequence = 3
444 AND irp5.user_name = dbi.user_name
445 AND dbi.user_entity_id = arc.user_entity_id
446 AND arc.context1 = p_asgn_action_id
450 AND faic2.CONTEXT = 'To Be Advised'
447 AND faic2.archive_item_id = arc.archive_item_id
448 AND ffc2.context_id = faic2.context_id
449 AND ffc2.context_name = 'SOURCE_TEXT'
451 group by irp5.code
452 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0 ;
453
454 /* Cursor to fetch The -ve Lump Sum balances with Directive Number other than Default
455 That is checking the Lump Sum Certificate balances */
456 CURSOR get_lmpsm_crt_bal (p_asg_act_id pay_assignment_actions.assignment_action_id%TYPE) is
457 select sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) value
458 , faic2.CONTEXT Tax_Directive_Number
459 , irp5.code code
460 from pay_za_irp5_bal_codes irp5,
461 ff_archive_items arc,
462 ff_database_items dbi,
463 ff_archive_item_contexts faic2,
464 ff_contexts ffc2
465 where arc.context1 in (select ch.assignment_action_id
466 from pay_assignment_actions main
467 , pay_assignment_actions ch
468 where main.assignment_action_id = p_asg_act_id
469 and ch.payroll_action_id = main.payroll_action_id
470 and ch.assignment_action_id < main.assignment_action_id
471 AND ch.assignment_id = main.assignment_id)
472 and
473 (
474 arc.value is not null
475 or
476 (
477 arc.value is not null
478 and arc.value <> 0
479 )
480 )
481 and dbi.user_entity_id = arc.user_entity_id
482 and irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3908, 3909, 3915)
483 AND irp5.balance_sequence = 3
484 AND dbi.user_name = irp5.user_name
485 AND faic2.archive_item_id = arc.archive_item_id
486 AND ffc2.context_id = faic2.context_id
487 AND ffc2.context_name = 'SOURCE_TEXT'
488 group BY faic2.CONTEXT
489 , irp5.code
490 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) < 0;
491
492
493 /*Cursor to fetch all Deduction Balances which have either negative amounts or missing clearance numbers*/
494 CURSOR fetch_deduction_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
495 select irp5.code ,
496 faic2.CONTEXT clearance_num,
497 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
498 FROM pay_za_irp5_bal_codes irp5,
499 ff_archive_items arc,
500 ff_database_items dbi,
501 ff_archive_item_contexts faic2,
502 ff_contexts ffc2
503 WHERE irp5.code IN (4001, 4002, 4003, 4004, 4006, 4007)
504 AND irp5.balance_sequence = 1
505 AND irp5.user_name = dbi.user_name
506 AND dbi.user_entity_id = arc.user_entity_id
507 AND arc.context1 = p_asgn_action_id
508 AND faic2.archive_item_id = arc.archive_item_id
509 AND ffc2.context_id = faic2.context_id
510 AND ffc2.context_name = 'SOURCE_NUMBER'
511 group by irp5.code,
512 faic2.CONTEXT
513 HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0)
514 OR (faic2.CONTEXT = g_default_clrno
515 and sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) <> 0);
516 --added above condition for Bug 7214056
517
518 /*Cursor to fetch Medical Aid Code Values for Cross Validation*/
519 CURSOR fetch_med_code_bal_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
520 select irp5.code,
521 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
522 FROM pay_za_irp5_bal_codes irp5,
523 ff_archive_items arc,
524 ff_database_items dbi
525 WHERE irp5.code IN (3810, 3813, 4005, 4024, 4025, 4485, 4486)
526 AND irp5.balance_sequence = 1
527 AND irp5.user_name = dbi.user_name
528 AND dbi.user_entity_id = arc.user_entity_id
529 AND arc.context1 = p_asgn_action_id
530 group by irp5.code;
531
532 /* Cursor to fetch all assignment_id and max(assignment_action_id) for given payroll_action_id */
533 CURSOR asgn_for_payroll_action_cur IS
534 SELECT assignment_id,
535 max(assignment_action_id) assignment_action_id -- max assignment_action_id relates to Main Certificate
536 FROM pay_assignment_actions
537 WHERE payroll_action_id = p_payroll_action_id
538 GROUP BY assignment_id ;
539
540 /*Cursor to fetch assignment_action_id corresponding to payroll run for given assignment_id and that tax year*/
541 CURSOR payroll_asgn_ac_id_cur(p_asgn_id pay_assignment_actions.assignment_id%type,
542 p_start_date DATE,
543 p_end_date DATE) IS
544 select paa.assignment_action_id
545 from pay_action_contexts pac,
546 pay_assignment_actions paa,
547 pay_payroll_actions ppa,
548 ff_contexts ffc
549 where paa.assignment_id = p_asgn_id
553 And pac.context_value = g_default_clrno
550 and paa.payroll_action_id = ppa.payroll_action_id
551 and ppa.action_type in ('R', 'Q','B')
552 AND pac.assignment_Action_id = paa.assignment_action_id
554 and ffc.context_name = 'SOURCE_NUMBER'
555 and ffc.context_id = pac.context_id
556 and ppa.effective_date >= p_start_date
557 and ppa.effective_date <= p_end_date;
558
559 /*Cursor the fetch all element names which have missing clearance numbers for given assignment_id*/
560 CURSOR elem_names_cur (p_asgn_ac_id pay_assignment_actions.assignment_action_id%type)IS
561 Select element_name
562 FROM pay_assignment_actions paa,
563 pay_payroll_actions ppa,
564 pay_element_types_f pet,
565 pay_input_values_f piv,
566 pay_run_results prr,
567 pay_run_result_values prv
568 Where paa.assignment_action_id = p_asgn_ac_id
569 and prr.assignment_Action_id = paa.assignment_action_id
570 and pet.element_type_id = prr.element_type_id
571 and piv.element_type_id = pet.element_type_id
572 and piv.name = 'Clearance Number'
573 and prv.run_result_id = prr.run_result_id
574 and prv.input_value_id = piv.input_value_id
575 and prv.RESULT_VALUE = g_default_clrno
576 and ppa.payroll_action_id = paa.payroll_action_id
577 and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
578 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date ;
579
580 /*To fetch PKG balance feed for employee not on pension basis */
581 CURSOR fetch_pkg_balances( p_asgn_action_id pay_assignment_actions.assignment_action_id%type) IS
582 select irp5.code,
583 irp5.full_balance_name bal_name,
584 irp5.balance_type_id bal_type_id,
585 trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))) value
586 FROM pay_za_irp5_bal_codes irp5,
587 ff_archive_items arc,
588 ff_database_items dbi,
589 per_assignment_extra_info paei,
590 pay_assignment_actions paa
591 WHERE dbi.user_name in
592 (
593 'A_ANNUAL_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
594 'A_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
595 'A_USE_OF_MOTOR_VEHICLE_PKG_ASG_TAX_YTD',
596 'A_RIGHT_OF_USE_OF_ASSET_PKG_ASG_TAX_YTD',
597 'A_MEALS_REFRESHMENTS_AND_VOUCHERS_PKG_ASG_TAX_YTD',
598 'A_FREE_OR_CHEAP_ACCOMMODATION_PKG_ASG_TAX_YTD',
599 'A_FREE_OR_CHEAP_SERVICES_PKG_ASG_TAX_YTD',
600 'A_LOW_OR_INTEREST_FREE_LOANS_PKG_ASG_TAX_YTD',
601 'A_ANNUAL_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
602 'A_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
603 'A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
604 'A_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
605 'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
606 'A_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
607 'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
608 'A_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
609 'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
610 'A_TAXABLE_INCOME_PKG_ASG_TAX_YTD',
611 'A_TAXABLE_PENSION_PKG_ASG_TAX_YTD',
612 'A_ANNUAL_BONUS_PKG_ASG_TAX_YTD',
613 'A_TAXABLE_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
614 'A_ANNUAL_COMMISSION_PKG_ASG_TAX_YTD',
615 'A_COMMISSION_PKG_ASG_TAX_YTD',
616 'A_ANNUAL_OVERTIME_PKG_ASG_TAX_YTD',
617 'A_OVERTIME_PKG_ASG_TAX_YTD',
618 'A_ANNUITY_FROM_RETIREMENT_FUND_PKG_ASG_TAX_YTD',
619 'A_PURCHASED_ANNUITY_TAXABLE_PKG_ASG_TAX_YTD',
620 'A_ANNUAL_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
621 'A_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
622 'A_ANNUAL_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
623 'A_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
624 'A_ANNUAL_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
625 'A_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
626 'A_TRAVEL_ALLOWANCE_PKG_ASG_TAX_YTD',
627 'A_TAXABLE_REIMBURSIVE_TRAVEL_PKG_ASG_TAX_YTD',
628 'A_TAXABLE_SUBSISTENCE_PKG_ASG_TAX_YTD',
629 'A_ENTERTAINMENT_ALLOWANCE_PKG_ASG_TAX_YTD',
630 'A_PUBLIC_OFFICE_ALLOWANCE_PKG_ASG_TAX_YTD',
631 'A_TOOL_ALLOWANCE_PKG_ASG_TAX_YTD',
632 'A_COMPUTER_ALLOWANCE_PKG_ASG_TAX_YTD',
633 'A_TELEPHONE_ALLOWANCE_PKG_ASG_TAX_YTD',
634 'A_OTHER_TAXABLE_ALLOWANCE_PKG_ASG_TAX_YTD',
635 'A_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_PKG_ASG_TAX_YTD',
636 'A_EE_BROADBASED_SHARE_PLAN_PKG_ASG_TAX_YTD',
637 'A_OTHER_LUMP_SUM_TAXED_AS_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
638 'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
639 'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD',
640 'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD',
644 AND irp5.balance_sequence = 1
641 'A_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD',
642 'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD'
643 )
645 AND irp5.user_name = dbi.user_name
646 AND dbi.user_entity_id = arc.user_entity_id
647 AND paei.assignment_id = paa.assignment_id
648 AND arc.context1 = p_asgn_action_id
649 AND arc.context1 = paa.assignment_action_id
650 AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis: Fixed Percentage of Specific Income
651 AND paei.information_type = 'ZA_SPECIFIC_INFO'
652 AND pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)) <> '0';
653
654 /*Fetch elements feeding to PKG classification */
655 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
656 SELECT ELEM.element_name element_name,
657 sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
658 from pay_balance_feeds_f FEED
659 , pay_run_result_values TARGET
660 , pay_run_results RR
661 , per_time_periods PPTP
662 , per_time_periods BPTP
663 , pay_payroll_actions PACT
664 , pay_assignment_actions ASSACT
665 , pay_payroll_actions BACT
666 , pay_assignment_actions BAL_ASSACT
667 , pay_element_types_f ELEM
668 where BAL_ASSACT.assignment_action_id = p_asgn_action_id
669 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
670 and FEED.input_value_id = TARGET.input_value_id
671 and TARGET.run_result_id = RR.run_result_id
672 and RR.assignment_action_id = ASSACT.assignment_action_id
673 + decode(PPTP.year_number, 0, 0, 0)
674 and ASSACT.payroll_action_id = PACT.payroll_action_id
675 and PACT.effective_date between FEED.effective_start_date
676 and FEED.effective_end_date
677 and BPTP.payroll_id = BACT.payroll_id
678 and PPTP.payroll_id = PACT.payroll_id
679 and nvl(BACT.date_earned,BACT.effective_date)
680 between BPTP.start_date and BPTP.end_date
681 and PACT.date_earned between PPTP.start_date and PPTP.end_date
682 and RR.status in ('P','PA')
683 AND ELEM.element_type_id = RR.element_type_id
684 and PPTP.prd_information1 = BPTP.prd_information1
685 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
686 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
687 AND feed.BALANCE_TYPE_ID = p_bal_typ_id
688 GROUP BY ELEM.element_name
689 HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
690
691
692 TYPE med_code_tab IS TABLE OF NUMBER Index BY PLS_INTEGER;
693 med_code_t med_code_tab ;
694 retiremnt_fund_lmpsum med_code_tab;
695
696 l_empno per_all_people_f.employee_number%type;
697 l_assgno per_all_assignments_f.assignment_number%type;
698 l_tax_ytd ff_archive_items.value%TYPE ; -- to save Tax paid by employee during tax year
699 l_site ff_archive_items.value%TYPE ;
700 l_paye ff_archive_items.value%TYPE ;
701 l_msgtext varchar2(2000);
702 l_missing_clrno_flag VARCHAR2(1) ;
703 l_count NUMBER :=0;
704 l_count1 NUMBER :=0;
705
706
707 --Changes done for Bug No 6749775
708 CURSOR emp_number_cur ( asgn_ac_id pay_assignment_actions.assignment_action_id%TYPE) IS
709 SELECT per.employee_number empno, asgn2.assignment_number assgno
710 FROM pay_assignment_actions paa,
711 per_all_assignments_f asgn2,
712 per_all_people_f per,
713 pay_payroll_actions ppa
714 WHERE paa.assignment_action_id = asgn_ac_id
718 AND asgn2.effective_start_date =
715 AND ppa.payroll_action_id = paa.payroll_action_id
716 AND asgn2.assignment_id = paa.assignment_id
717 AND per.person_id = asgn2.person_id
719 ( select max(paf2.effective_start_date)
720 from per_assignments_f paf2
721 where paf2.effective_start_date <= ppa.effective_date
722 and paf2.assignment_id = asgn2.assignment_id
723 )
724 AND per.effective_start_date =
725 ( select max(per2.effective_start_date)
726 from per_all_people_f per2
727 where per2.effective_start_date <= ppa.effective_date
728 and per2.person_id = per.person_id
729 );
730 --End changes for Bug No 6749775
731
732 /* Cursor to check sanity of 3915 and 4115 codes */
733 CURSOR chk_rtrmnt_fnd_cur ( p_asgn_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
734 SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
735 FROM ff_archive_items arc,
736 ff_database_items dbi,
737 pay_za_irp5_bal_codes irp5
738 WHERE arc.user_entity_id = dbi.user_entity_id
739 and irp5.user_name = dbi.user_name
740 and arc.context1 in (p_asgn_action_id)
741 and code in (3915,4115)
742 GROUP BY irp5.code ;
743
744 begin
745 retcode := 0;
746 -- hr_utility.trace_on(null,'ZATYEVL');
747 FND_FILE.PUT_LINE(FND_FILE.LOG,'In validate_tye_data');
748 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_payroll_action_id :' || p_payroll_action_id);
749 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside validate_tye_data');
750
751 /*Loop through all Assignments for given payroll_action_id*/
752 FOR asgn IN asgn_for_payroll_action_cur
753 LOOP
754 l_count:=0;
755 l_count1:=0;
756 /* Fetch Employee_number */
757 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Action Id : ' || asgn.assignment_action_id);
758 -- FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
759 -- FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
760 FOR emp_num IN emp_number_cur(asgn.assignment_action_id)
761 LOOP
762 l_empno := emp_num.empno ;
763 l_assgno:= emp_num.assgno;
764 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
765 END LOOP;
766
767 -- Rule 1) If an employee has paid any Tax during the current tax year,
768 -- they must have a value in the SITE and/or PAYE balance (SITE_ASG_TAX_YTD or PAYE_ASG_TAX_YTD)
769 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 1) If Employee has paid tax during current tax year, he should have SITE/PAYE split');
770 select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
771 into l_tax_ytd
772 from ff_archive_items arc,
773 ff_database_items dbi
774 where dbi.user_name = 'A_TAX_ASG_TAX_YTD'
775 and arc.user_entity_id = dbi.user_entity_id
776 and arc.context1 = asgn.assignment_action_id;
777
778 IF l_tax_ytd > 0 THEN
779 select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
780 into l_site
781 from ff_archive_items arc,
782 ff_database_items dbi
783 where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
784 and arc.user_entity_id = dbi.user_entity_id
785 and arc.context1 = asgn.assignment_action_id;
786
787 select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
788 into l_paye
789 from ff_archive_items arc,
790 ff_database_items dbi
791 where dbi.user_name = 'A_PAYE_ASG_TAX_YTD'
792 and arc.user_entity_id = dbi.user_entity_id
793 and arc.context1 = asgn.assignment_action_id;
794
795 IF l_site = 0 AND l_paye = 0 THEN
796 l_count:=1;
797 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
798 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
799 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
800 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
801 fnd_message.set_name('PAY', 'PY_ZA_NO_SITE_PAYE_SPLIT');
802 fnd_message.set_token('EMPNO',l_empno);
803 l_msgtext := fnd_message.get('Y');
804 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Tax Amount : '||l_tax_ytd) ;
805 FND_FILE.PUT_LINE(FND_FILE.LOG, ' SITE balance : '||l_site) ;
806 FND_FILE.PUT_LINE(FND_FILE.LOG, ' PAYE balance : '||l_paye) ;
807 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
808 END IF ;
809 END IF ;
810
811 -- Rule 2) Check for Income Balances which may not contain negative amounts
812 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 2) Check for Income Balances which may not contain negative amounts');
813 FOR neg_amt_check IN negative_amt_check_cur(asgn.assignment_action_id)
814 LOOP
815 if l_count <>1 then
816 l_count:=1;
817 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
818 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
819 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
820 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
824 fnd_message.set_token('SARScode',neg_amt_check.code);
821 end if;
822 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
823 fnd_message.set_token('EMPno',l_empno);
825 l_msgtext := fnd_message.get('Y');
826 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
827 END LOOP ;
828
829 -- Rule 3) Check Lumpsum balances which may not contain negative amounts
830 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 3) Check Lumpsum balances which may not contain negative amounts');
831 FOR lmpsum_bal IN fetch_lmpsm_bal_cur(asgn.assignment_action_id)
832 LOOP
833 if l_count <>1 then
834 l_count:=1;
835 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
836 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
837 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
838 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
839 end if;
840 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || g_default_dirno);
841 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
842 fnd_message.set_token('EMPno',l_empno);
843 fnd_message.set_token('SARScode',lmpsum_bal.code);
844 l_msgtext := fnd_message.get('Y');
845 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
846 END LOOP ;
847 ---- Check for the Lump Sum Balances with Directive Number Other than the Defoult directive number
848
849 FOR rec_lmpsm_crt_bal IN get_lmpsm_crt_bal(asgn.assignment_action_id)
850 loop
851 if l_count <>1 then
852 l_count:=1;
853 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
854 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
855 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
856 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
857 end if;
858 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Tax Directive Number ' || rec_lmpsm_crt_bal.Tax_Directive_Number);
859 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
860 fnd_message.set_token('EMPno',l_empno);
861 fnd_message.set_token('SARScode',rec_lmpsm_crt_bal.code);
862 l_msgtext := fnd_message.get('Y');
863 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
864
865
866 END loop;
867 -- End check for Lump Sum Balances with Directive Number Other than the Defoult directive number
868 -- Rule 4) Check for Deduction Balances
869 -- a) may not contain negative amounts
870 -- b) Clearance number must be entered
871 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 4) Check for Deduction Balances ') ;
872 l_missing_clrno_flag := 'N' ;
873 FOR ded_bal IN fetch_deduction_bal_cur(asgn.assignment_action_id)
874 LOOP
875 IF ded_bal.value <0 THEN
876 if l_count <>1 then
877 l_count:=1;
878 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
879 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
880 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
881 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
882 end if;
883 fnd_message.set_name('PAY', 'PY_ZA_NEG_BAL_NOT_ALWD');
884 fnd_message.set_token('EMPno',l_empno);
885 fnd_message.set_token('SARScode',ded_bal.code);
886 l_msgtext := fnd_message.get('Y');
887 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
888 END IF ;
889 IF ded_bal.clearance_num = g_default_clrno THEN
890 l_missing_clrno_flag := 'Y' ;
891 END IF ;
892 END LOOP ;
893
894 -- If clearance number is g_default_clrno,
895 -- find out the element(s) whose run_result_values contain defaults (99999999999) for 'Clearance' Input value
896 IF l_missing_clrno_flag = 'Y' THEN
897 /*Loop through all assignment_action_id for payroll run for given assignment*/
898 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Clearance Number is default ') ;
899 FOR paa IN payroll_asgn_ac_id_cur(asgn.assignment_id, p_tax_yr_start_date, p_tax_yr_end_date)
900 LOOP
901 /*Loop through all element names for which the employee has missing clearance numbers*/
902 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'For payroll assignment_action id : '||paa.assignment_action_id) ;
903 FOR elem_names IN elem_names_cur (paa.assignment_action_id)
904 LOOP
905 if l_count <>1 then
906 l_count:=1;
907 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
908 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
909 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
910 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
911 end if;
912 fnd_message.set_name('PAY', 'PA_ZA_ENTER_CLEARANCE_NUM');
913 fnd_message.set_token('EMPno',l_empno);
914 fnd_message.set_token('ELEMENTname',elem_names.element_name);
915 l_msgtext := fnd_message.get('Y');
916 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msgtext);
917 END LOOP ;
918 END LOOP ;
919 END IF;
920
921 /* Loop through all the PKG balances fed through elements for employee not on PKG structure
922 For Bug 7264311 */
923 FOR pkg_bal IN fetch_pkg_balances(asgn.assignment_action_id)
924 LOOP
925 if l_count <>1 then
926 l_count:=1;
927 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
928 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
929 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
930 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
931 end if;
932
933 IF l_count1 <>1 then
934 fnd_message.set_name('PAY', 'PY_ZA_PKG_BAL_NT_ALLOW');
935 fnd_message.set_token('EMPno',l_empno);
936 l_msgtext := fnd_message.get('Y');
937 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
938 l_count1:=1;
939 END IF;
940
941 FOR pkg_ele IN fetch_pkg_ele(asgn.assignment_action_id, pkg_bal.bal_type_id)
942 LOOP
943 fnd_message.set_name('PAY', 'PY_ZA_ELE_FEED_PKG_BAL');
944 fnd_message.set_token('ELEMENTname',pkg_ele.element_name);
945 fnd_message.set_token('BALANCEname',pkg_bal.bal_name);
946 l_msgtext := fnd_message.get('Y');
947 FND_FILE.PUT_LINE (FND_FILE.LOG,l_msgtext);
948 END LOOP;
949
950 END LOOP;
951
952 -- Rule 5) Cross validation of Medical Aid Codes
953 -- initialize table of medical aid codes
954 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 5) Cross validation of Medical Aid Codes');
955 med_code_t(3810) := 0 ;
956 med_code_t(3813) := 0 ;
957 med_code_t(4005) := 0 ;
958 med_code_t(4024) := 0 ;
959 med_code_t(4025) := 0 ;
960 med_code_t(4485) := 0 ;
961 med_code_t(4486) := 0 ;
962 FOR med_code_bal IN fetch_med_code_bal_cur(asgn.assignment_action_id)
963 LOOP
964 med_code_t(med_code_bal.code) := med_code_bal.value ;
965 END LOOP ;
966
967 -- 5a) Code 3813 must be equal to the sum of Codes 4024 and 4485
968 IF med_code_t(3813) <> (med_code_t(4024) + med_code_t(4485)) THEN
969 if l_count <>1 then
970 l_count:=1;
971 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
972 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
973 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
974 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
975 end if;
976 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 3813 must be equal to the sum of Codes 4024 and 4485') ;
977 END IF ;
978 -- 5b) Code 4005 must be greater than zero if there is a value in 3810 or 4025
979 IF (med_code_t(3810) <>0) OR (med_code_t(4025) <>0) THEN
980 IF med_code_t(4005) <=0 THEN
981 if l_count <>1 then
982 l_count:=1;
983 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
984 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
985 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
986 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
987 end if;
988 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4005 must be greater than zero if Code 3810 or 4025 has a value') ;
989 END IF;
990 END IF ;
991 -- 5c) Code 4486 must be greater than zero if there is a value in 4025
992 IF med_code_t(4025) <>0 THEN
993 IF med_code_t(4486) <= 0 THEN
994 if l_count <>1 then
995 l_count:=1;
996 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
997 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
998 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
999 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1000 end if;
1001 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4486 must be greater than zero if Code 4025 has a value') ;
1002 END IF ;
1003 END IF ;
1004 -- 5d) Code 4025 may not be greater than 4005
1005 IF med_code_t(4025) > med_code_t(4005) THEN
1006 if l_count <>1 then
1007 l_count:=1;
1008 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1009 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1010 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Employee Number : ' || l_empno);
1011 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Assignment Number : ' || l_assgno);
1012 end if;
1013 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4025 may not be greater than Code 4005') ;
1014 END IF ;
1015
1016 -- Retirement Fund Lumpsum PAYE balance (4115) should not be present if
1017 -- Retirement Fund Lumpsum (3915) itself is not present
1018 retiremnt_fund_lmpsum(3915) := 0 ;
1019 retiremnt_fund_lmpsum(4115) := 0 ;
1020 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rule 6) Cross Validation of codes 3915 and 4115 ') ;
1021 FOR chk_rtrmnt_fnd IN chk_rtrmnt_fnd_cur(asgn.assignment_action_id)
1022 LOOP
1023 retiremnt_fund_lmpsum(chk_rtrmnt_fnd.code) := chk_rtrmnt_fnd.value ;
1024 END LOOP;
1025
1026 IF retiremnt_fund_lmpsum(4115)<> 0 and retiremnt_fund_lmpsum(3915) = 0 THEN
1027 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Code 4115 must not be present if Code 3915 is not present') ;
1028 END IF ;
1029
1030 END LOOP ; -- End of assignment Loop
1031 FND_FILE.PUT_LINE(FND_FILE.LOG,'End of log file');
1032 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1033 -- hr_utility.trace_off;
1034 EXCEPTION
1035 WHEN OTHERS then
1036 errbuf := substr(SQLERRM,1,255);
1037 retcode := sqlcode;
1038 end VALIDATE_TYE_DATA;
1039
1040 end PAY_ZA_EOY_VAL;
[Home] [Help]