DBA Data[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;