[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_SOE
Source
1 package body PAY_ZA_SOE as
2 /* $Header: pyzasoe.pkb 120.20.12020000.7 2012/12/07 13:55:09 sgmaram ship $ */
3 --
4 g_debug boolean := hr_utility.debug_enabled;
5 g_max_action number;
6 g_min_action number;
7
8 --
9 --
10 /* ---------------------------------------------------------------------
11 Function : Get_Tax_Status
12
13 This funtion is used in the function Personal_Information to get the
14 Tax status for given assignment id and date earned.
15 ------------------------------------------------------------------------ */
16 function Get_Tax_Status(p_assignment_id in number, p_date_earned in date) return varchar2 is
17 --
18 l_tax_status varchar2(3);
19 l_tax_status_name varchar2(60);
20 --
21 begin
22 --
23 if g_debug then
24 hr_utility.set_location('Entering pay_za_soe.Get_Tax_Status', 10);
25 end if;
26 --
27
28 pay_balance_pkg.set_context('ASSIGNMENT_ID', to_char(p_assignment_id));
29 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(p_date_earned));
30
31 l_tax_status := pay_balance_pkg.run_db_item('ZA_TAX_TAX_STATUS_ENTRY_VALUE', null, 'ZA');
32
33 begin
34 select meaning
35 into l_tax_status_name
36 from fnd_common_lookups
37 where lookup_type = 'ZA_TAX_STATUS'
38 and lookup_code = l_tax_status;
39
40 exception
41 when no_data_found then
42 l_tax_status_name := '';
43 end;
44
45 --
46 if g_debug then
47 hr_utility.set_location('Leaving pay_za_soe.Get_Tax_Status', 20);
48 end if;
49 --
50 return l_tax_status_name;
51 --
52 end Get_Tax_Status;
53 --
54 --
55 /* ---------------------------------------------------------------------
56 Function : getElements
57
58 This function returns a query which fetch all elements with runresult
59 value != 0 for given Assignment Action Id and Element Set Name
60 ------------------------------------------------------------------------ */
61 function getElements(p_assignment_action_id number, p_element_set_name varchar2) return long is
62 --
63 l_sql long;
64 --
65 begin
66 --
67 --
68 if g_debug then
69 hr_utility.set_location('Entering pay_soe_glb.getElements', 10);
70 end if;
71 --
72
73 l_sql := 'Select nvl(ettl.reporting_name,et.element_type_id) COL01
74 ,nvl(ettl.reporting_name,ettl.element_name) COL02
75 ,to_char(sum(fnd_number.canonical_to_number(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
76 ,decode(count(*),1,''1'',''2'') COL17 -- destination indicator
77 ,decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
78 From pay_assignment_actions aa
79 ,pay_run_results rr
80 ,pay_run_result_values rrv
81 ,pay_input_values_f iv
82 ,pay_input_values_f_tl ivtl
83 ,pay_element_types_f et
84 ,pay_element_types_f_tl ettl
85 ,pay_element_set_members esm
86 ,pay_element_sets es
87 Where aa.assignment_action_id :action_clause
88 and aa.assignment_action_id = rr.assignment_action_id
89 and rr.status in (''P'',''PA'')
90 and rr.run_result_id = rrv.run_result_id
91 and rr.element_type_id = et.element_type_id
92 and :effective_date between et.effective_start_date and et.effective_end_date
93 and et.element_type_id = ettl.element_type_id
94 and rrv.input_value_id = iv.input_value_id
95 and iv.name = ''Pay Value''
96 and :effective_date between iv.effective_start_date and iv.effective_end_date
97 and iv.input_value_id = ivtl.input_value_id
98 and ettl.language = userenv(''LANG'')
99 and ivtl.language = userenv(''LANG'')
100 and et.element_type_id = esm.element_type_id
101 and esm.element_set_id = es.element_set_id
102 and ( es.BUSINESS_GROUP_ID is null
103 or es.BUSINESS_GROUP_ID = :business_group_id )
104 and ( es.LEGISLATION_CODE is null
105 or es.LEGISLATION_CODE = '':legislation_code'' )
106 and es.element_set_name = '''|| p_element_set_name ||'''
107 group by nvl(ettl.reporting_name,ettl.element_name)
108 ,ettl.reporting_name
109 ,nvl(ettl.reporting_name,et.element_type_id)
110 having nvl(sum(fnd_number.canonical_to_number(rrv.result_value)),0) != 0
111 order by nvl(ettl.reporting_name,ettl.element_name)';
112 --
113 if g_debug then
114 hr_utility.set_location('Leaving pay_soe_glb.getElements', 20);
115 end if;
116 --
117 return l_sql;
118 --
119 end getElements;
120 --
121 --
122 /* ---------------------------------------------------------------------
123 Function : Personal_Information
124
125 This return returs SQL query which will be executed inoreder to fetch
126 data for Personal Information Region of ZA SOE.
127 ------------------------------------------------------------------------ */
128 function Personal_Information(p_assignment_action_id in number) return long is
129 --
130 l_sql long;
131 l_date_earned varchar2(15);
132 l_run_assignment_action_id number;
133 l_assignment_id number;
134 l_payroll_action_id number;
135 l_assignment_action_id number;
136 --
137 begin
138 --
139 if g_debug then
140 hr_utility.set_location('Entering pay_za_soe.Personal_Information', 10);
141 end if;
142 --
143 l_assignment_action_id := p_assignment_action_id;
144 --5507715
145 PAY_ZA_PAYROLL_ACTION_PKG.formula_inputs_hc (
146 p_assignment_action_id => l_assignment_action_id
147 , p_run_assignment_action_id => l_run_assignment_action_id
148 , p_assignment_id => l_assignment_id
149 , p_payroll_action_id => l_payroll_action_id
150 , p_date_earned => l_date_earned
151 );
152
153 l_sql :=
154 '
155 Select org.name as COL01 -- Organisation Name
156 ,org2.name as COL02 -- Legal Entity Name
157 ,job.name as COL03
158 ,loc.location_code as COL04
159 ,grd.name as COL05
160 ,pos.name as COL06
161 ,peo.national_identifier as COL07
162 ,employee_number as COL08
163 ,hl.meaning as COL09 -- Nationality
164 ,asg.assignment_number as COL10
165 ,fnd_date.date_to_displaydate(pps.date_start) as COL11
166 ,fnd_date.date_to_displaydate(pps.actual_termination_date) as COL12
167 ,to_char(fnd_number.canonical_to_number(nvl(ppb1.salary,''0'')),fnd_currency.get_format_mask(:g_currency_code,40)) as COL13
168 ,peo.per_information1 as COL14 -- Tax Reference Number
169 ,pay_za_soe.get_tax_status(asg.assignment_id, :effective_date) as COL15
170 ,ptp.period_num as COL16
171 From per_all_people_f peo
172 ,per_all_assignments_f asg
173 ,per_jobs_vl job
174 ,pay_assignment_actions paa
175 ,per_assignment_extra_info pae
176 ,per_periods_of_service pps
177 ,pay_payroll_actions ppa
178 ,per_time_periods ptp
179 ,per_time_period_types ptt
180 ,hr_all_organization_units_vl org
181 ,hr_all_organization_units_vl org2
182 ,hr_locations loc
183 ,per_grades_vl grd
184 ,per_all_positions pos
185 ,pay_payrolls_f pay
186 ,pay_people_groups pg
187 ,hr_lookups hl
188 ,(select ppb2.pay_basis_id
189 ,ppb2.business_group_id
190 ,ee.assignment_id
191 ,eev.screen_entry_value as salary
192 from per_pay_bases ppb2
193 ,pay_element_entries_f ee
194 ,pay_element_entry_values_f eev
195 where ppb2.input_value_id = eev.input_value_id
196 and ee.element_entry_id = eev.element_entry_id
197 and :effective_date between ee.effective_start_date and ee.effective_end_date
198 and :effective_date between eev.effective_start_date and eev.effective_end_date
199 ) ppb1
200 Where asg.assignment_id = :assignment_id and
201 :effective_date between asg.effective_start_date and asg.effective_end_date and
202 asg.person_id = peo.person_id and
203 :effective_date between peo.effective_start_date and peo.effective_end_date and
204 asg.job_id = job.job_id(+) and
205 asg.pay_basis_id = ppb1.pay_basis_id(+) and
206 asg.assignment_id = ppb1.assignment_id(+) and
207 asg.business_group_id = ppb1.business_group_id(+) and
208 paa.assignment_action_id = ''' || p_assignment_action_id || ''' and
209 ppa.payroll_action_id = paa.payroll_action_id and
210 ptp.payroll_id = ppa.payroll_id AND '
211 || 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')' || ' between ptp.start_date and ptp.end_date and
212 ptp.period_type = ptt.period_type and
213 pps.period_of_service_id = asg.period_of_service_id and
214 asg.organization_id = org.organization_id and
215 :effective_date between org.date_from and nvl(org.date_to, :effective_date) and
216 pae.assignment_id(+) = asg.assignment_id and
217 pae.aei_information_category(+) = ''ZA_SPECIFIC_INFO'' and
218 org2.organization_id(+) = pae.aei_information7 and
219 org2.date_from(+) <= :effective_date and
220 nvl(org2.date_to(+), :effective_date) >= :effective_date and
221 asg.location_id = loc.location_id(+) and
222 asg.grade_id = grd.grade_id(+) and
223 asg.people_group_id = pg.people_group_id(+) and
224 asg.position_id = pos.position_id(+) and
225 asg.payroll_id = pay.payroll_id(+) and
226 :effective_date between pay.effective_start_date(+) and pay.effective_end_date(+) and
227 hl.application_id (+) = 800 and
228 hl.lookup_type (+) = ''NATIONALITY'' and
229 hl.lookup_code (+) = peo.nationality';
230 --
231 if g_debug then
232 hr_utility.set_location('Leaving pay_za_soe.Personal_Information', 20);
233 end if;
234 --
235 return l_sql;
236 --
237 end Personal_Information;
238 --
239 --
240 /* ---------------------------------------------------------------------
241 Function : Payroll_Processing_Information
242
243 This return returs SQL query which will be executed inoreder to fetch
244 data for Payroll Processing Information Region of ZA On line SOE.
245 ------------------------------------------------------------------------ */
246 function Payroll_Processing_Information(p_assignment_action_id in number) return long is
247 --
248 l_sql long;
249 l_date_earned varchar2(15);
250 l_run_assignment_action_id number;
251 l_assignment_id number;
252 l_payroll_action_id number;
253 l_assignment_action_id number;
254 --
255 begin
256 --
257 if g_debug then
258 hr_utility.set_location('Entering pay_za_soe.Payroll_Processing_Information', 10);
259 end if;
260 --
261 l_assignment_action_id := p_assignment_action_id;
262 --5507715
263 PAY_ZA_PAYROLL_ACTION_PKG.formula_inputs_hc (
264 p_assignment_action_id => l_assignment_action_id
265 , p_run_assignment_action_id => l_run_assignment_action_id
266 , p_assignment_id => l_assignment_id
267 , p_payroll_action_id => l_payroll_action_id
268 , p_date_earned => l_date_earned
269 );
270 --
271 l_sql :=
272 'Select ptp.period_name as COL01 -- Period Name
273 ,fnd_date.date_to_displaydate(ppa.effective_date) as COL02 -- Pay Date
274 ,ptp.period_type as COL03 -- Period Type
275 ,fnd_date.date_to_displaydate(ptp.start_date) as COL04 -- Period Start Date
276 ,fnd_date.date_to_displaydate(ptp.end_date) as COL05 -- Period End Date
277 From per_time_periods ptp
278 ,pay_payroll_actions ppa
279 ,pay_assignment_actions paa
280 ,per_time_period_types ptt
281 Where paa.assignment_action_id = ''' || p_assignment_action_id || ''' and
282 paa.payroll_action_id = ppa.payroll_action_id and
283 ptp.payroll_id = ppa.payroll_id AND '
284 || 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')' || ' between ptp.start_date and ptp.end_date and
285 ptp.period_type = ptt.period_type';
286 --
287 if g_debug then
288 hr_utility.set_location('Leaving pay_za_soe.Payroll_Processing_Information', 20);
289 end if;
290 --
291 return l_sql;
292 --
293 end Payroll_Processing_Information;
294 --
295 --
296 /* ---------------------------------------------------------------------
297 Function : Elements1
298
299 This function returns a query which fetches all elements, which fells
300 under element set attached to element1 segment of SOE Information flexfeild,
301 with runresult value != 0 for given Assignment Action Id
302 ------------------------------------------------------------------------ */
303 function Elements1(p_assignment_action_id in number) return long is
304 --
305 l_sql long;
306 --
307 begin
308 --
309 if g_debug then
310 hr_utility.set_location('Entering pay_za_soe.Elements1', 10);
311 end if;
312 --
313
314 l_sql := getElements(p_assignment_action_id, pay_soe_util.getConfig('ELEMENTS1'));
315
316 --
317 if g_debug then
318 hr_utility.set_location('Leaving pay_za_soe.Elements1', 20);
319 end if;
320 --
321 return l_sql;
322 --
323 end Elements1;
324 --
325 --
326 /* ---------------------------------------------------------------------
327 Function : Elements2
328
329 This function returns a query which fetches all elements, which fells
330 under element set attached to element2 segment of SOE Information flexfeild,
331 with runresult value != 0 for given Assignment Action Id
332 ------------------------------------------------------------------------ */
333 function Elements2(p_assignment_action_id in number) return long is
334 --
335 l_sql long;
336 --
337 begin
338 --
339 if g_debug then
340 hr_utility.set_location('Entering pay_za_soe.Elements2', 10);
341 end if;
342 --
343
344 l_sql := getElements(p_assignment_action_id, pay_soe_util.getConfig('ELEMENTS2'));
345
346 --
347 if g_debug then
348 hr_utility.set_location('Leaving pay_za_soe.Elements2', 20);
349 end if;
350 --
351 return l_sql;
352 --
353 end Elements2;
354 --
355 --
356 /* ---------------------------------------------------------------------
357 Function : Elements3
358
359 This function returns a query which fetches all elements, which fells
360 under element set attached to element3 segment of SOE Information flexfeild,
361 with runresult value != 0 for given Assignment Action Id
362 ------------------------------------------------------------------------ */
363 function Elements3(p_assignment_action_id in number) return long is
364 --
365 l_sql long;
366 --
367 begin
368 --
369 if g_debug then
370 hr_utility.set_location('Entering pay_za_soe.Elements3', 10);
371 end if;
372 --
373
374 l_sql := getElements(p_assignment_action_id, pay_soe_util.getConfig('ELEMENTS3'));
375
376 --
377 if g_debug then
378 hr_utility.set_location('Leaving pay_za_soe.Elements3', 20);
379 end if;
380 --
381 return l_sql;
382 --
383 end Elements3;
384 --
385 --
386 /* ---------------------------------------------------------------------
387 Function : Elements4
388
389 This function returns a query which fetches all elements, which fells
390 under element set attached to element4 segment of SOE Information flexfeild,
391 with runresult value != 0 for given Assignment Action Id
392 ------------------------------------------------------------------------ */
393 function Elements4(p_assignment_action_id in number) return long is
394 --
395 l_sql long;
396 --
397 begin
398 --
399 if g_debug then
400 hr_utility.set_location('Entering pay_za_soe.Elements4', 10);
401 end if;
402 --
403
404 l_sql := getElements(p_assignment_action_id, pay_soe_util.getConfig('ELEMENTS4'));
405
406 --
407 if g_debug then
408 hr_utility.set_location('Leaving pay_za_soe.Elements4', 20);
409 end if;
410 --
411 return l_sql;
412 --
413 end Elements4;
414 --
415 --
416 /* ---------------------------------------------------------------------
417 Function : Elements5
418
419 This function returns a query which fetches all elements, which fells
420 under element set attached to element5 segment of SOE Information flexfeild,
421 with runresult value != 0 for given Assignment Action Id
422 ------------------------------------------------------------------------ */
423 function Elements5(p_assignment_action_id in number) return long is
424 --
425 l_sql long;
426 --
427 begin
428 --
429 if g_debug then
430 hr_utility.set_location('Entering pay_za_soe.Elements5', 10);
431 end if;
432 --
433
434 l_sql := getElements(p_assignment_action_id, pay_soe_util.getConfig('ELEMENTS5'));
435
436 --
437 if g_debug then
438 hr_utility.set_location('Leaving pay_za_soe.Elements5', 20);
439 end if;
440 --
441 return l_sql;
442 --
443 end Elements5;
444 --
445 --
446 /* ---------------------------------------------------------------------
447 Function : Elements6
448
449 This function returns a query which fetches all elements, which fells
450 under element set attached to element6 segment of SOE Information flexfeild,
451 with runresult value != 0 for given Assignment Action Id
452 ------------------------------------------------------------------------ */
453 function Elements6(p_assignment_action_id in number) return long is
454 --
455 l_sql long;
456 --
457 begin
458 --
459 if g_debug then
460 hr_utility.set_location('Entering pay_za_soe.Elements6', 10);
461 end if;
462 --
463
464 l_sql := getElements(p_assignment_action_id, pay_soe_util.getConfig('ELEMENTS6'));
465
466 --
467 if g_debug then
468 hr_utility.set_location('Leaving pay_za_soe.Elements6', 20);
469 end if;
470 --
471 return l_sql;
472 --
473 end Elements6;
474 --
475 --
476 /* ---------------------------------------------------------------------
477 Function : Balance_Details
478
479 This function is used to update balance details of Assignment Action Id.
480 There are 125 balances listed to include into balance region. Apart from
481 this wser can update 'SOE Detail Information' DFF to include those
482 balances to appear on Balances region of Online ZA SOE.
483 ------------------------------------------------------------------------ */
484 function Balance_Details(p_assignment_action_id in number) return long is
485 --
486 l_sql long;
487 --
488 name_count number;
489 dim_id number;
490 balance_name varchar2(80);
491 display_name varchar2(80);
492 balance_suffix pay_balance_dimensions.dimension_name%TYPE;
493 balance_val varchar2(80);
494 bal_found_flag varchar2(80);
495 l_date_earned varchar2(15);
496 l_run_assignment_action_id number;
497 l_assignment_id number;
498 l_payroll_action_id number;
499 l_assignment_action_id number;
500 --
501 type balance_rec is record
502 (
503 balance_name varchar2(80),
504 display_name varchar2(80),
505 balance_suffix pay_balance_dimensions.dimension_name%TYPE
506 );
507 --
508 type user_balance_table_type is table of balance_rec index by binary_integer;
509 --
510 l_max_user_balance_index number := 0;
511 l_tab_index number := 0;
512 l_user_balance_table user_balance_table_type;
513 --
514 cursor user_balances is
515 Select pbt.balance_name as balance_name
516 ,nvl(org.org_information7,pbt.balance_name) as display_name
517 ,pbd.dimension_name as balance_suffix
518 From pay_balance_types pbt
519 ,pay_balance_dimensions pbd
520 ,pay_assignment_actions paa
521 ,pay_payroll_actions ppa
522 ,per_all_assignments_f asg
523 ,hr_organization_information org
524 Where paa.assignment_action_id = p_assignment_action_id and
525 paa.assignment_id = asg.assignment_id and
526 paa.payroll_action_id = ppa.payroll_action_id and
527 ppa.effective_date between asg.effective_start_date and asg.effective_end_date and
528 asg.organization_id = org.organization_id and
529 org.org_information_context = 'Business Group:SOE Detail' and
530 org.org_information1 = 'BALANCE' and
531 pbt.balance_type_id = org.org_information4 and
532 pbd.balance_dimension_id = org.org_information5;
533 --
534 begin
535 --
536 if g_debug then
537 hr_utility.set_location('Entering pay_za_soe.Balance_Details', 10);
538 end if;
539 --
540
541 --
542 pay_soe_util.clear;
543 --
544 l_assignment_action_id := p_assignment_action_id;
545
546 --5507715
547 PAY_ZA_PAYROLL_ACTION_PKG.formula_inputs_hc (
548 p_assignment_action_id => l_assignment_action_id
549 , p_run_assignment_action_id => l_run_assignment_action_id
550 , p_assignment_id => l_assignment_id
551 , p_payroll_action_id => l_payroll_action_id
552 , p_date_earned => l_date_earned
553 );
554
555 -- Update user balance details
556 for user_balances_rec in user_balances
557 loop
558 --
559 balance_name := user_balances_rec.balance_name;
560 display_name := user_balances_rec.display_name;
561 balance_suffix := user_balances_rec.balance_suffix;
562
563 -- Update user balances info the local table
564 l_max_user_balance_index := l_max_user_balance_index + 1;
565 l_user_balance_table(l_max_user_balance_index).balance_name := balance_name;
566 l_user_balance_table(l_max_user_balance_index).display_name := display_name;
567 l_user_balance_table(l_max_user_balance_index).balance_suffix := balance_suffix;
568
569 -- This package is called to get the balance id which is needed in the get value package
570 dim_id := pay_za_payroll_action_pkg.defined_balance_id(balance_name,balance_suffix);
571
572 -- Use the get_value package to get value of balance
573 balance_val := pay_balance_pkg.get_value(dim_id, l_run_assignment_action_id, false);
574
575 if nvl(balance_val, 0) <> 0 then
576 balance_val := to_char(fnd_number.canonical_to_number(balance_val),fnd_currency.get_format_mask('ZAR',40));
577 pay_soe_util.setValue('01', balance_name, true, false);
578 pay_soe_util.setValue('02', display_name, false, false);
579 pay_soe_util.setValue('16', balance_val, false, true);
580 end if;
581 --
582 end loop;
583 --
584 --
585
586 -- Update standard balance details
587 name_count := 1;
588 loop
589 -- if name_count > 197 then
590 -- Added 2 new balances for 2008 Sars code
591 -- if name_count > 215 then
592 -- if name_count > 216 then
593 -- if name_count > 220 then
594 -- if name_count > 223 then -- DIRECTOR REMUNERATION ENHANCEMENT
595 -- if name_count > 224 then -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
596 -- if name_count > 224 then -- Medical Tax Credit Lumpsum Changes
597 -- if name_count > 226 then -- New secondary element classification - annual share options exercised
598 if name_count > 229 then -- 2013 TAX YEAR CHANGES.
599 exit;
600 end if;
601
602 -- These balance names are hardcoded and should not be changed
603 -- bug 3011568 added entries for the two new balances
604 -- for 4346920 Balance feed enhancement Added PKG balances
605 -- And Package Taxable Balances RFI and NRFI
606 -- bug 6444483 added entries for the two new balances for 2008 Sars code
607 -- bug 6867418 added entries for the six more new balances for 2008 Sars code
608 -- bug 8406456 added entries for the three new balances for TYS 2009 Sars code
609 IF name_count < 124 then
610 select decode
611 (
612 name_count,
613 1 , 'Taxable Income RFI',
614 2 , 'Taxable Income NRFI',
615 3 , 'Taxable Income PKG',
616 4 , 'Non Taxable Income',
617 5 , 'Taxable Pension RFI',
618 6 , 'Taxable Pension NRFI',
619 7 , 'Taxable Pension PKG',
620 8 , 'Non Taxable Pension',
621 9 , 'Taxable Annual Payment RFI',
622 10 , 'Taxable Annual Payment NRFI',
623 11 , 'Taxable Annual Payment PKG',
624 12 , 'Annual Bonus RFI',
625 13 , 'Annual Bonus NRFI',
626 14 , 'Annual Bonus PKG',
627 15 , 'Commission RFI',
628 16 , 'Commission NRFI',
629 17 , 'Commission PKG',
630 18 , 'Overtime RFI',
631 19 , 'Overtime NRFI',
632 20 , 'Overtime PKG',
633 21 , 'Taxable Arbitration Award RFI',
634 22 , 'Taxable Arbitration Award NRFI',
635 23 , 'Non Taxable Arbitration Award',
636 24 , 'Annuity from Retirement Fund RFI',
637 25 , 'Annuity from Retirement Fund NRFI',
638 26 , 'Annuity from Retirement Fund PKG',
639 27 , 'Purchased Annuity Taxable RFI',
640 28 , 'Purchased Annuity Taxable NRFI',
641 29 , 'Purchased Annuity Taxable PKG',
642 30 , 'Purchased Annuity Non Taxable',
643 31 , 'Travel Allowance RFI',
644 32 , 'Travel Allowance NRFI',
645 33 , 'Travel Allowance PKG',
646 34 , 'Taxable Reimbursive Travel RFI',
647 35 , 'Taxable Reimbursive Travel NRFI',
648 36 , 'Taxable Reimbursive Travel PKG',
649 37 , 'Non Taxable Reimbursive Travel',
650 38 , 'Taxable Subsistence RFI',
651 39 , 'Taxable Subsistence NRFI',
652 40 , 'Taxable Subsistence PKG',
653 41 , 'Non Taxable Subsistence',
654 42 , 'Entertainment Allowance RFI',
655 43 , 'Entertainment Allowance NRFI',
656 44 , 'Entertainment Allowance PKG',
657 45 , 'Share Options Exercised RFI',
658 46 , 'Share Options Exercised NRFI',
659 47 , 'Public Office Allowance RFI',
660 48 , 'Public Office Allowance NRFI',
661 49 , 'Public Office Allowance PKG',
662 50 , 'Uniform Allowance',
663 51 , 'Tool Allowance RFI',
664 52 , 'Tool Allowance NRFI',
665 53 , 'Tool Allowance PKG',
666 54 , 'Computer Allowance RFI',
667 55 , 'Computer Allowance NRFI',
668 56 , 'Computer Allowance PKG',
669 57 , 'Telephone Allowance RFI',
670 58 , 'Telephone Allowance NRFI',
671 59 , 'Telephone Allowance PKG',
672 60 , 'Other Taxable Allowance RFI',
673 61 , 'Other Taxable Allowance NRFI',
674 62 , 'Other Taxable Allowance PKG',
675 63 , 'Other Non Taxable Allowance',
676 64 , 'Asset Purchased at Reduced Value RFI',
677 65 , 'Asset Purchased at Reduced Value NRFI',
678 66 , 'Asset Purchased at Reduced Value PKG',
679 67 , 'Use of Motor Vehicle RFI',
680 68 , 'Use of Motor Vehicle NRFI',
681 69 , 'Use of Motor Vehicle PKG',
682 70 , 'Right of Use of Asset RFI',
683 71 , 'Right of Use of Asset NRFI',
684 72 , 'Right of Use of Asset PKG',
685 73 , 'Meals Refreshments and Vouchers RFI',
686 74 , 'Meals Refreshments and Vouchers NRFI',
687 75 , 'Meals Refreshments and Vouchers PKG',
688 76 , 'Free or Cheap Accommodation RFI',
689 77 , 'Free or Cheap Accommodation NRFI',
690 78 , 'Free or Cheap Accommodation PKG',
691 79 , 'Free or Cheap Services RFI',
692 80 , 'Free or Cheap Services NRFI',
693 81 , 'Free or Cheap Services PKG',
694 82 , 'Low or Interest Free Loans RFI',
695 83 , 'Low or Interest Free Loans NRFI',
696 84 , 'Low or Interest Free Loans PKG',
697 85 , 'Payment of Employee Debt RFI',
698 86 , 'Payment of Employee Debt NRFI',
699 87 , 'Payment of Employee Debt PKG',
700 88 , 'Bursaries and Scholarships RFI',
701 89 , 'Bursaries and Scholarships NRFI',
702 90 , 'Bursaries and Scholarships PKG',
703 91 , 'Medical Aid Paid on Behalf of Employee RFI',
704 92 , 'Medical Aid Paid on Behalf of Employee NRFI',
705 93 , 'Medical Aid Paid on Behalf of Employee PKG',
706 94 , 'Retirement or Retrenchment Gratuities',
707 95 , 'Resignation Pension and RAF Lump Sums',
708 96 , 'Retirement Pension and RAF Lump Sums',
709 97 , 'Resignation Provident Lump Sums',
710 98 , 'Retirement Provident Lump Sums',
711 99 , 'Special Remuneration',
712 100, 'Other Lump Sums',
713 101, 'Current Pension Fund',
714 102, 'Arrear Pension Fund',
715 103, 'Current Provident Fund',
716 104, 'Arrear Provident Fund',
717 105, 'Medical Aid Contribution',
718 106, 'Current Retirement Annuity',
719 107, 'Arrear Retirement Annuity',
720 108, 'Tax on Lump Sums',
721 109, 'Tax',
722 110, 'UIF Employee Contribution',
723 111, 'Voluntary Tax',
724 112, 'Bonus Provision',
725 113, 'SITE',
726 114, 'PAYE',
727 115, 'Annual Pension Fund',
728 116, 'Annual Commission RFI',
729 117, 'Annual Commission NRFI',
730 118, 'Annual Commission PKG',
731 119, 'Annual Provident Fund',
732 120, 'Restraint of Trade RFI',
733 121, 'Restraint of Trade NRFI',
734 122, 'Restraint of Trade PKG',
735 123, 'Annual Restraint of Trade RFI'
736 )
737 into balance_name
738 from dual;
739 else
740 select decode
741 (
742 name_count,
743 124, 'Annual Restraint of Trade NRFI',
744 125, 'Annual Restraint of Trade PKG',
745 126, 'Annual Asset Purchased at Reduced Value RFI',
746 127, 'Annual Asset Purchased at Reduced Value NRFI',
747 128, 'Annual Asset Purchased at Reduced Value PKG',
748 129, 'Annual Retirement Annuity',
749 130, 'Annual Arrear Pension Fund',
750 131, 'Annual Arrear Retirement Annuity',
751 132, 'Other Retirement Lump Sums',
752 133, 'Directors Deemed Remuneration',
753 134, 'Annual Bursaries and Scholarships RFI',
754 135, 'Annual Bursaries and Scholarships NRFI',
755 136, 'Annual Bursaries and Scholarships PKG',
756 137, 'Labour Broker Payments RFI',
757 138, 'Labour Broker Payments NRFI',
758 139, 'Labour Broker Payments PKG',
759 140, 'Annual Labour Broker Payments RFI',
760 141, 'Annual Labour Broker Payments NRFI',
761 142, 'Annual Labour Broker Payments PKG',
762 143, 'Independent Contractor Payments RFI',
763 144, 'Independent Contractor Payments NRFI',
764 145, 'Independent Contractor Payments PKG',
765 146, 'Annual Independent Contractor Payments RFI',
766 147, 'Annual Independent Contractor Payments NRFI',
767 148, 'Annual Independent Contractor Payments PKG',
768 149, 'Annual Payment of Employee Debt RFI',
769 150, 'Annual Payment of Employee Debt NRFI',
770 151, 'Annual Payment of Employee Debt PKG',
771 152, 'Annual Taxable Package Components RFI',
772 153, 'Taxable Package Components RFI',
773 154, 'Annual Taxable Package Components NRFI',
774 155, 'Taxable Package Components NRFI',
775 156, 'Taxable Subsistence Allowance Foreign Travel RFI',
776 157, 'Taxable Subsistence Allowance Foreign Travel NRFI',
777 158, 'Taxable Subsistence Allowance Foreign Travel PKG',
778 159, 'Non Taxable Subsistence Allowance Foreign Travel',
779 160, 'Executive Equity Shares RFI',
780 161, 'Executive Equity Shares NRFI',
781 162, 'EE Income Protection Policy Contributions',
782 163, 'Annual EE Income Protection Policy Contributions',
783 164, 'EE Broadbased Share Plan NRFI',
784 165, 'EE Broadbased Share Plan RFI',
785 166, 'EE Broadbased Share Plan PKG',
786 167, 'Other Lump Sum Taxed as Annual Payment NRFI',
787 168, 'Other Lump Sum Taxed as Annual Payment RFI',
788 169, 'Other Lump Sum Taxed as Annual Payment PKG',
789 -- Begin: New Balances for TYS 06-07
790 170, 'Med Costs Pd by ER IRO EE_Family RFI',
791 171, 'Med Costs Pd by ER IRO EE_Family NRFI',
792 172, 'Med Costs Pd by ER IRO EE_Family PKG',
793 173, 'Annual Med Costs Pd by ER IRO EE_Family RFI',
794 174, 'Annual Med Costs Pd by ER IRO EE_Family NRFI',
795 175, 'Annual Med Costs Pd by ER IRO EE_Family PKG',
796 176, 'Annual Med Costs Pd by ER IRO Other RFI',
797 177, 'Annual Med Costs Pd by ER IRO Other NRFI',
798 178, 'Annual Med Costs Pd by ER IRO Other PKG',
799 179, 'Med Costs Pd by ER IRO Other RFI',
800 180, 'Med Costs Pd by ER IRO Other NRFI',
801 181, 'Med Costs Pd by ER IRO Other PKG',
802 182, 'Medical Contributions Abatement',
803 183, 'Annual Medical Contributions Abatement',
804 184, 'Medical Fund Capping Amount',
805 185, 'Med Costs Dmd Pd by EE EE_Family RFI',
806 186, 'Med Costs Dmd Pd by EE EE_Family NRFI',
807 187, 'Med Costs Dmd Pd by EE EE_Family PKG',
808 188, 'Annual Med Costs Dmd Pd by EE EE_Family RFI',
809 189, 'Annual Med Costs Dmd Pd by EE EE_Family NRFI',
810 190, 'Annual Med Costs Dmd Pd by EE EE_Family PKG',
811 191, 'Med Costs Dmd Pd by EE Other RFI',
812 192, 'Med Costs Dmd Pd by EE Other NRFI',
813 193, 'Med Costs Dmd Pd by EE Other PKG',
814 194, 'Annual Med Costs Dmd Pd by EE Other RFI',
815 195, 'Annual Med Costs Dmd Pd by EE Other NRFI',
816 196, 'Annual Med Costs Dmd Pd by EE Other PKG',
817 197, 'Non Taxable Med Costs Pd by ER',
818 -- End: New Balances for TYS 06-07
819 -- Start for 2008 SARS codes
820 198, 'Employers Retirement Annuity Fund Contributions',
821 199, 'Employers Premium paid on Loss of Income Policies',
822 200, 'Medical Contr Pd by ER for Retired EE',
823 201, 'Surplus Apportionment',
824 202, 'Unclaimed Benefits',
825 203, 'Retire Pen RAF Prov Fund Ben on Ret or Death RFI',
826 204, 'Retire Pen RAF Prov Fund Ben on Ret or Death NRFI',
827 205, 'Tax on Retirement Fund Lump Sums',
828 --Added for Bug 7634596
829 206, 'Pension Employer Contribution',
830 207, 'Provident Employer Contribution',
831 208, 'Medical Aid Employer Contribution',
832 --Added for Bug 8406456-Mar2009 Sars codes
833 209, 'Retire Pen RAF and Prov Fund Lump Sum withdrawal benefits',
834 210, 'Donations made by EE and paid by ER',
835 211, 'Annual Donations made by EE and paid by ER',
836 --End for Bug 8406456
837 --Added for new balances for EE Debt for net to gross
838 212, 'Annual Payment of Employee Debt NRFI NTG',
839 213, 'Annual Payment of Employee Debt RFI NTG',
840 214, 'PAYE Employer Contribution for Tax Free Earnings',
841 215, 'Living Annuity and Surplus Apportionments Lump Sums',
842 -- Severance Benefit Payments Enhancement
843 216, 'Severance Benefit Payments',
844 -- TYE 2012 Changes
845 217, 'Non Taxable Bursaries and Scholarships',
846 218, 'Medical Aid Tax Credit',
847 219, 'Annual Medical Aid Tax Credit',
848 220, 'Total Medical Aid Tax Credit Used',
849 -- DIRECTOR REMUNERATION ENHANCEMENT
850 221, 'Director Remuneration NRFI',
851 222, 'Director Remuneration PKG',
852 223, 'Director Remuneration RFI',
853 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
854 224 ,'RFI Override SARS Reporting',
855 -- 225, 'Medical Tax Credit Used on Lump Sums'
856 -- NEW SECONDARY ELEMENT CLASSIFICATION - ANNUAL SHARE OPTIONS EXERCISED
857 225, 'Annual Share Options Exercised NRFI',
858 226, 'Annual Share Options Exercised RFI',
859 227,'Comp iro Death during Emp NRFI',
860 228,'Comp iro Death Non Taxable',
861 229,'Comp iro Death during Emp RFI'
862 )
863 into balance_name
864 from dual;
865 END if;
866
867 balance_suffix := '_ASG_TAX_YTD';
868 if balance_name = 'Directors Deemed Remuneration' then
869 balance_suffix := '_ASG_ITD';
870 end if;
871
872 -- Check whether this balace already exists in user balances
873 bal_found_flag := 'false';
874 for l_tab_index in 1..l_max_user_balance_index loop
875 if balance_name = l_user_balance_table(l_tab_index).balance_name and
876 balance_suffix = l_user_balance_table(l_tab_index).balance_suffix then
877 bal_found_flag := 'true';
878 end if;
879 end loop;
880
881 -- Update standard balance details if it is not already updated in user balance details
882 if(bal_found_flag = 'false') then
883 -- This package is called to get the balance id which is needed in the get value package
884 dim_id := pay_za_payroll_action_pkg.defined_balance_id(balance_name,balance_suffix);
885
886 -- Use the get_value package to get value of balance
887 balance_val := pay_balance_pkg.get_value(dim_id, l_run_assignment_action_id, false);
888
889 if nvl(balance_val, 0) <> 0 then
890 balance_val := to_char(fnd_number.canonical_to_number(balance_val),fnd_currency.get_format_mask('ZAR',40));
891 pay_soe_util.setValue('01', balance_name, true, false);
892 pay_soe_util.setValue('02', balance_name, false, false);
893 pay_soe_util.setValue('16', balance_val, false, true);
894 end if;
895 end if;
896 name_count := name_count + 1;
897
898 end loop;
899 l_sql := pay_soe_util.genCursor || ' order by 2';
900 pay_soe_util.clear;
901 --
902 --
903 if g_debug then
904 hr_utility.set_location('Leaving pay_za_soe.Balance_Details', 20);
905 end if;
906 --
907 return l_sql;
908 --
909 end Balance_Details;
910 --
911 --
912 /* ---------------------------------------------------------------------
913 Function : Payment_Method_Details
914
915 Text
916 ------------------------------------------------------------------------ */
917 function Payment_Method_Details(p_assignment_action_id in number) return long is
918 --
919 l_sql long;
920 l_date_earned varchar2(15);
921 l_run_assignment_action_id number;
922 l_assignment_id number;
923 l_payroll_action_id number;
924 l_assignment_action_id number;
925 --
926 begin
927
928 if g_debug then
929 hr_utility.set_location('Entering pay_za_soe.Payment_Method_Details', 10);
930 end if;
931 l_assignment_action_id := p_assignment_action_id;
932 --5507715
933 PAY_ZA_PAYROLL_ACTION_PKG.formula_inputs_hc(
934 p_assignment_action_id => l_assignment_action_id
935 , p_run_assignment_action_id => l_run_assignment_action_id
936 , p_assignment_id => l_assignment_id
937 , p_payroll_action_id => l_payroll_action_id
938 , p_date_earned => l_date_earned
939 );
940 --
941 l_sql :=
942 'Select substr(popmf.org_payment_method_name, 1, 30) as COL01 -- Payment Method
943 ,ppt.payment_type_name as COL02 -- Payment Type
944 ,cdv.bank_name as COL03 -- Bank
945 ,pea.segment1 as COL04 -- Branch Code
946 ,pea.segment3 as COL05 -- Account No
947 ,to_char(ppp.value, fnd_currency.get_format_mask(:g_currency_code,40)) as COL16 -- Payment Amount -- Bug 4392560
948 ,fnd_date.date_to_displaydate( '
949 || 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')'
950 ||') as COL06 -- Payment_date
951 From pay_pre_payments ppp
952 ,pay_personal_payment_methods_f pppmf
953 ,pay_org_payment_methods_f popmf
954 ,pay_external_accounts pea
955 ,pay_za_branch_cdv_details cdv
956 ,pay_assignment_actions paa
957 ,pay_payroll_actions ppa
958 ,pay_payment_types_tl ppt
959 ,pay_action_interlocks pai
960 Where (pai.locked_action_id = :assignment_action_id or pai.locking_action_id = :assignment_action_id) and
961 paa.assignment_action_id = pai.locking_action_id and
962 paa.payroll_action_id = ppa.payroll_action_id and
963 ppa.action_type in (''P'' , ''U'') and
964 ppp.assignment_action_id = paa.assignment_action_id and
965 pppmf.personal_payment_method_id (+) = ppp.personal_payment_method_id and
966 ppa.effective_date between nvl(pppmf.effective_start_date, ppa.effective_date) and
967 nvl(pppmf.effective_end_date, ppa.effective_date) and
968 ppa.effective_date between popmf.effective_start_date and
969 popmf.effective_end_date and
970 popmf.org_payment_method_id = ppp.org_payment_method_id and
971 pea.external_account_id (+) = pppmf.external_account_id and
972 cdv.branch_code (+) = pea.segment1 and
973 popmf.payment_type_id = ppt.payment_type_id(+) and
974 ppt.language(+) = userenv(''LANG'')';
975 --
976 if g_debug then
977 hr_utility.set_location('Leaving pay_za_soe.Payment_Method_Details', 20);
978 end if;
979 --
980 return l_sql;
981 --
982 end Payment_Method_Details;
983 --
984 --
985 end PAY_ZA_SOE;