[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_SOE
Source
1 package body PAY_ZA_SOE as
2 /* $Header: pyzasoe.pkb 120.9.12010000.2 2008/08/06 08:45:30 ubhat 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(50);
491 display_name varchar2(50);
492 balance_suffix varchar2(13);
493 balance_val varchar2(16);
494 bal_found_flag varchar2(50);
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(50),
504 display_name varchar2(50),
505 balance_suffix varchar2(13)
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 > 205 then
592 exit;
593 end if;
594
595 -- These balance names are hardcoded and should not be changed
596 -- bug 3011568 added entries for the two new balances
597 -- for 4346920 Balance feed enhancement Added PKG balances
598 -- And Package Taxable Balances RFI and NRFI
599 -- bug 6444483 added entries for the two new balances for 2008 Sars code
600 -- bug 6867418 added entries for the six more new balances for 2008 Sars code
601 IF name_count < 124 then
602 select decode
603 (
604 name_count,
605 1 , 'Taxable Income RFI',
606 2 , 'Taxable Income NRFI',
607 3 , 'Taxable Income PKG',
608 4 , 'Non Taxable Income',
609 5 , 'Taxable Pension RFI',
610 6 , 'Taxable Pension NRFI',
611 7 , 'Taxable Pension PKG',
612 8 , 'Non Taxable Pension',
613 9 , 'Taxable Annual Payment RFI',
614 10 , 'Taxable Annual Payment NRFI',
615 11 , 'Taxable Annual Payment PKG',
616 12 , 'Annual Bonus RFI',
617 13 , 'Annual Bonus NRFI',
618 14 , 'Annual Bonus PKG',
619 15 , 'Commission RFI',
620 16 , 'Commission NRFI',
621 17 , 'Commission PKG',
622 18 , 'Overtime RFI',
623 19 , 'Overtime NRFI',
624 20 , 'Overtime PKG',
625 21 , 'Taxable Arbitration Award RFI',
626 22 , 'Taxable Arbitration Award NRFI',
627 23 , 'Non Taxable Arbitration Award',
628 24 , 'Annuity from Retirement Fund RFI',
629 25 , 'Annuity from Retirement Fund NRFI',
630 26 , 'Annuity from Retirement Fund PKG',
631 27 , 'Purchased Annuity Taxable RFI',
632 28 , 'Purchased Annuity Taxable NRFI',
633 29 , 'Purchased Annuity Taxable PKG',
634 30 , 'Purchased Annuity Non Taxable',
635 31 , 'Travel Allowance RFI',
636 32 , 'Travel Allowance NRFI',
637 33 , 'Travel Allowance PKG',
638 34 , 'Taxable Reimbursive Travel RFI',
639 35 , 'Taxable Reimbursive Travel NRFI',
640 36 , 'Taxable Reimbursive Travel PKG',
641 37 , 'Non Taxable Reimbursive Travel',
642 38 , 'Taxable Subsistence RFI',
643 39 , 'Taxable Subsistence NRFI',
644 40 , 'Taxable Subsistence PKG',
645 41 , 'Non Taxable Subsistence',
646 42 , 'Entertainment Allowance RFI',
647 43 , 'Entertainment Allowance NRFI',
648 44 , 'Entertainment Allowance PKG',
649 45 , 'Share Options Exercised RFI',
650 46 , 'Share Options Exercised NRFI',
651 47 , 'Public Office Allowance RFI',
652 48 , 'Public Office Allowance NRFI',
653 49 , 'Public Office Allowance PKG',
654 50 , 'Uniform Allowance',
655 51 , 'Tool Allowance RFI',
656 52 , 'Tool Allowance NRFI',
657 53 , 'Tool Allowance PKG',
658 54 , 'Computer Allowance RFI',
659 55 , 'Computer Allowance NRFI',
660 56 , 'Computer Allowance PKG',
661 57 , 'Telephone Allowance RFI',
662 58 , 'Telephone Allowance NRFI',
663 59 , 'Telephone Allowance PKG',
664 60 , 'Other Taxable Allowance RFI',
665 61 , 'Other Taxable Allowance NRFI',
666 62 , 'Other Taxable Allowance PKG',
667 63 , 'Other Non Taxable Allowance',
668 64 , 'Asset Purchased at Reduced Value RFI',
669 65 , 'Asset Purchased at Reduced Value NRFI',
670 66 , 'Asset Purchased at Reduced Value PKG',
671 67 , 'Use of Motor Vehicle RFI',
672 68 , 'Use of Motor Vehicle NRFI',
673 69 , 'Use of Motor Vehicle PKG',
674 70 , 'Right of Use of Asset RFI',
675 71 , 'Right of Use of Asset NRFI',
676 72 , 'Right of Use of Asset PKG',
677 73 , 'Meals Refreshments and Vouchers RFI',
678 74 , 'Meals Refreshments and Vouchers NRFI',
679 75 , 'Meals Refreshments and Vouchers PKG',
680 76 , 'Free or Cheap Accommodation RFI',
681 77 , 'Free or Cheap Accommodation NRFI',
682 78 , 'Free or Cheap Accommodation PKG',
683 79 , 'Free or Cheap Services RFI',
684 80 , 'Free or Cheap Services NRFI',
685 81 , 'Free or Cheap Services PKG',
686 82 , 'Low or Interest Free Loans RFI',
687 83 , 'Low or Interest Free Loans NRFI',
688 84 , 'Low or Interest Free Loans PKG',
689 85 , 'Payment of Employee Debt RFI',
690 86 , 'Payment of Employee Debt NRFI',
691 87 , 'Payment of Employee Debt PKG',
692 88 , 'Bursaries and Scholarships RFI',
693 89 , 'Bursaries and Scholarships NRFI',
694 90 , 'Bursaries and Scholarships PKG',
695 91 , 'Medical Aid Paid on Behalf of Employee RFI',
696 92 , 'Medical Aid Paid on Behalf of Employee NRFI',
697 93 , 'Medical Aid Paid on Behalf of Employee PKG',
698 94 , 'Retirement or Retrenchment Gratuities',
699 95 , 'Resignation Pension and RAF Lump Sums',
700 96 , 'Retirement Pension and RAF Lump Sums',
701 97 , 'Resignation Provident Lump Sums',
702 98 , 'Retirement Provident Lump Sums',
703 99 , 'Special Remuneration',
704 100, 'Other Lump Sums',
705 101, 'Current Pension Fund',
706 102, 'Arrear Pension Fund',
707 103, 'Current Provident Fund',
708 104, 'Arrear Provident Fund',
709 105, 'Medical Aid Contribution',
710 106, 'Current Retirement Annuity',
711 107, 'Arrear Retirement Annuity',
712 108, 'Tax on Lump Sums',
713 109, 'Tax',
714 110, 'UIF Employee Contribution',
715 111, 'Voluntary Tax',
716 112, 'Bonus Provision',
717 113, 'SITE',
718 114, 'PAYE',
719 115, 'Annual Pension Fund',
720 116, 'Annual Commission RFI',
721 117, 'Annual Commission NRFI',
722 118, 'Annual Commission PKG',
723 119, 'Annual Provident Fund',
724 120, 'Restraint of Trade RFI',
725 121, 'Restraint of Trade NRFI',
726 122, 'Restraint of Trade PKG',
727 123, 'Annual Restraint of Trade RFI'
728 )
729 into balance_name
730 from dual;
731 else
732 select decode
733 (
734 name_count,
735 124, 'Annual Restraint of Trade NRFI',
736 125, 'Annual Restraint of Trade PKG',
737 126, 'Annual Asset Purchased at Reduced Value RFI',
738 127, 'Annual Asset Purchased at Reduced Value NRFI',
739 128, 'Annual Asset Purchased at Reduced Value PKG',
740 129, 'Annual Retirement Annuity',
741 130, 'Annual Arrear Pension Fund',
742 131, 'Annual Arrear Retirement Annuity',
743 132, 'Other Retirement Lump Sums',
744 133, 'Directors Deemed Remuneration',
745 134, 'Annual Bursaries and Scholarships RFI',
746 135, 'Annual Bursaries and Scholarships NRFI',
747 136, 'Annual Bursaries and Scholarships PKG',
748 137, 'Labour Broker Payments RFI',
749 138, 'Labour Broker Payments NRFI',
750 139, 'Labour Broker Payments PKG',
751 140, 'Annual Labour Broker Payments RFI',
752 141, 'Annual Labour Broker Payments NRFI',
753 142, 'Annual Labour Broker Payments PKG',
754 143, 'Independent Contractor Payments RFI',
755 144, 'Independent Contractor Payments NRFI',
756 145, 'Independent Contractor Payments PKG',
757 146, 'Annual Independent Contractor Payments RFI',
758 147, 'Annual Independent Contractor Payments NRFI',
759 148, 'Annual Independent Contractor Payments PKG',
760 149, 'Annual Payment of Employee Debt RFI',
761 150, 'Annual Payment of Employee Debt NRFI',
762 151, 'Annual Payment of Employee Debt PKG',
763 152, 'Annual Taxable Package Components RFI',
764 153, 'Taxable Package Components RFI',
765 154, 'Annual Taxable Package Components NRFI',
766 155, 'Taxable Package Components NRFI',
767 156, 'Taxable Subsistence Allowance Foreign Travel RFI',
768 157, 'Taxable Subsistence Allowance Foreign Travel NRFI',
769 158, 'Taxable Subsistence Allowance Foreign Travel PKG',
770 159, 'Non Taxable Subsistence Allowance Foreign Travel',
771 160, 'Executive Equity Shares RFI',
772 161, 'Executive Equity Shares NRFI',
773 162, 'EE Income Protection Policy Contributions',
774 163, 'Annual EE Income Protection Policy Contributions',
775 164, 'EE Broadbased Share Plan NRFI',
776 165, 'EE Broadbased Share Plan RFI',
777 166, 'EE Broadbased Share Plan PKG',
778 167, 'Other Lump Sum Taxed as Annual Payment NRFI',
779 168, 'Other Lump Sum Taxed as Annual Payment RFI',
780 169, 'Other Lump Sum Taxed as Annual Payment PKG',
781 -- Begin: New Balances for TYS 06-07
782 170, 'Med Costs Pd by ER IRO EE_Family RFI',
783 171, 'Med Costs Pd by ER IRO EE_Family NRFI',
784 172, 'Med Costs Pd by ER IRO EE_Family PKG',
785 173, 'Annual Med Costs Pd by ER IRO EE_Family RFI',
786 174, 'Annual Med Costs Pd by ER IRO EE_Family NRFI',
787 175, 'Annual Med Costs Pd by ER IRO EE_Family PKG',
788 176, 'Annual Med Costs Pd by ER IRO Other RFI',
789 177, 'Annual Med Costs Pd by ER IRO Other NRFI',
790 178, 'Annual Med Costs Pd by ER IRO Other PKG',
791 179, 'Med Costs Pd by ER IRO Other RFI',
792 180, 'Med Costs Pd by ER IRO Other NRFI',
793 181, 'Med Costs Pd by ER IRO Other PKG',
794 182, 'Medical Contributions Abatement',
795 183, 'Annual Medical Contributions Abatement',
796 184, 'Medical Fund Capping Amount',
797 185, 'Med Costs Dmd Pd by EE EE_Family RFI',
798 186, 'Med Costs Dmd Pd by EE EE_Family NRFI',
799 187, 'Med Costs Dmd Pd by EE EE_Family PKG',
800 188, 'Annual Med Costs Dmd Pd by EE EE_Family RFI',
801 189, 'Annual Med Costs Dmd Pd by EE EE_Family NRFI',
802 190, 'Annual Med Costs Dmd Pd by EE EE_Family PKG',
803 191, 'Med Costs Dmd Pd by EE Other RFI',
804 192, 'Med Costs Dmd Pd by EE Other NRFI',
805 193, 'Med Costs Dmd Pd by EE Other PKG',
806 194, 'Annual Med Costs Dmd Pd by EE Other RFI',
807 195, 'Annual Med Costs Dmd Pd by EE Other NRFI',
808 196, 'Annual Med Costs Dmd Pd by EE Other PKG',
809 197, 'Non Taxable Med Costs Pd by ER',
810 -- End: New Balances for TYS 06-07
811 -- Start for 2008 SARS codes
812 198, 'Employers Retirement Annuity Fund Contributions',
813 199, 'Employers Premium paid on Loss of Income Policies',
814 200, 'Medical Contr Pd by ER for Retired EE',
815 201, 'Surplus Apportionment',
816 202, 'Unclaimed Benefits',
817 203, 'Retire Pen RAF Prov Fund Ben on Ret or Death RFI',
818 204, 'Retire Pen RAF Prov Fund Ben on Ret or Death NRFI',
819 205, 'Tax on Retirement Fund Lump Sums'
820 )
821 into balance_name
822 from dual;
823 END if;
824
825 balance_suffix := '_ASG_TAX_YTD';
826 if balance_name = 'Directors Deemed Remuneration' then
827 balance_suffix := '_ASG_ITD';
828 end if;
829
830 -- Check whether this balace already exists in user balances
831 bal_found_flag := 'false';
832 for l_tab_index in 1..l_max_user_balance_index loop
833 if balance_name = l_user_balance_table(l_tab_index).balance_name and
834 balance_suffix = l_user_balance_table(l_tab_index).balance_suffix then
835 bal_found_flag := 'true';
836 end if;
837 end loop;
838
839 -- Update standard balance details if it is not already updated in user balance details
840 if(bal_found_flag = 'false') then
841 -- This package is called to get the balance id which is needed in the get value package
842 dim_id := pay_za_payroll_action_pkg.defined_balance_id(balance_name,balance_suffix);
843
844 -- Use the get_value package to get value of balance
845 balance_val := pay_balance_pkg.get_value(dim_id, l_run_assignment_action_id, false);
846
847 if nvl(balance_val, 0) <> 0 then
848 balance_val := to_char(fnd_number.canonical_to_number(balance_val),fnd_currency.get_format_mask('ZAR',40));
849 pay_soe_util.setValue('01', balance_name, true, false);
850 pay_soe_util.setValue('02', balance_name, false, false);
851 pay_soe_util.setValue('16', balance_val, false, true);
852 end if;
853 end if;
854 name_count := name_count + 1;
855
856 end loop;
857 l_sql := pay_soe_util.genCursor || ' order by 2';
858 pay_soe_util.clear;
859 --
860 --
861 if g_debug then
862 hr_utility.set_location('Leaving pay_za_soe.Balance_Details', 20);
863 end if;
864 --
865 return l_sql;
866 --
867 end Balance_Details;
868 --
869 --
870 /* ---------------------------------------------------------------------
871 Function : Payment_Method_Details
872
873 Text
874 ------------------------------------------------------------------------ */
875 function Payment_Method_Details(p_assignment_action_id in number) return long is
876 --
877 l_sql long;
878 l_date_earned varchar2(15);
879 l_run_assignment_action_id number;
880 l_assignment_id number;
881 l_payroll_action_id number;
882 l_assignment_action_id number;
883 --
884 begin
885
886 if g_debug then
887 hr_utility.set_location('Entering pay_za_soe.Payment_Method_Details', 10);
888 end if;
889 l_assignment_action_id := p_assignment_action_id;
890 --5507715
891 PAY_ZA_PAYROLL_ACTION_PKG.formula_inputs_hc(
892 p_assignment_action_id => l_assignment_action_id
893 , p_run_assignment_action_id => l_run_assignment_action_id
894 , p_assignment_id => l_assignment_id
895 , p_payroll_action_id => l_payroll_action_id
896 , p_date_earned => l_date_earned
897 );
898 --
899 l_sql :=
900 'Select substr(popmf.org_payment_method_name, 1, 30) as COL01 -- Payment Method
901 ,ppt.payment_type_name as COL02 -- Payment Type
902 ,cdv.bank_name as COL03 -- Bank
903 ,pea.segment1 as COL04 -- Branch Code
904 ,pea.segment3 as COL05 -- Account No
905 ,to_char(ppp.value, fnd_currency.get_format_mask(:g_currency_code,40)) as COL16 -- Payment Amount -- Bug 4392560
906 ,fnd_date.date_to_displaydate( '
907 || 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')'
908 ||') as COL06 -- Payment_date
909 From pay_pre_payments ppp
910 ,pay_personal_payment_methods_f pppmf
911 ,pay_org_payment_methods_f popmf
912 ,pay_external_accounts pea
913 ,pay_za_branch_cdv_details cdv
914 ,pay_assignment_actions paa
915 ,pay_payroll_actions ppa
916 ,pay_payment_types_tl ppt
917 ,pay_action_interlocks pai
918 Where (pai.locked_action_id = :assignment_action_id or pai.locking_action_id = :assignment_action_id) and
919 paa.assignment_action_id = pai.locking_action_id and
920 paa.payroll_action_id = ppa.payroll_action_id and
921 ppa.action_type in (''P'' , ''U'') and
922 ppp.assignment_action_id = paa.assignment_action_id and
923 pppmf.personal_payment_method_id (+) = ppp.personal_payment_method_id and
924 ppa.effective_date between nvl(pppmf.effective_start_date, ppa.effective_date) and
925 nvl(pppmf.effective_end_date, ppa.effective_date) and
926 ppa.effective_date between popmf.effective_start_date and
927 popmf.effective_end_date and
928 popmf.org_payment_method_id = ppp.org_payment_method_id and
929 pea.external_account_id (+) = pppmf.external_account_id and
930 cdv.branch_code (+) = pea.segment1 and
931 popmf.payment_type_id = ppt.payment_type_id(+) and
932 ppt.language(+) = userenv(''LANG'')';
933 --
934 if g_debug then
935 hr_utility.set_location('Leaving pay_za_soe.Payment_Method_Details', 20);
936 end if;
937 --
938 return l_sql;
939 --
940 end Payment_Method_Details;
941 --
942 --
943 end PAY_ZA_SOE;