1 package body py_za_tax_certificates as
2 /* $Header: pyzatcer.pkb 120.12.12010000.3 2008/08/06 08:46:26 ubhat ship $ */
3 /*
4 -- +======================================================================+
5 -- | Copyright (c) 1998 Oracle Corporation South Africa Ltd |
6 -- | Cape Town, Western Cape, South Africa |
7 -- | All rights reserved. |
8 -- +======================================================================+
9 -- SQL Script File Name : pyzatcer.pkb
10 -- Description : This sql script seeds the py_za_tax_certificates
11 -- package for the ZA localisation. This package
12 -- is used in the Tax Certificate reports.
13 --
14 -- Change List:
15 -- ------------
16 --
17 -- Name Date Version Bug Text
18 -- -------------- ----------- ------- ------- -----------------------------
19 -- F.D. Loubser 08-May-2000 110.0 Initial version
20 -- J.N. Louw 24-Aug-2000 115.0 Updated for ZAPatch11i.01
21 -- F.D. Loubser 11-Sep-2000 115.1 Updated for CBO
22 -- G.J. Fraser 09-Jan-2001 110.11 Changed Balance Names
23 -- L. KLoppers 06-Sep-2002 115.2 2266025 Added A_OTHER_RETIREMENT_LUMP_SUMS_ASG_RETRO_TAX_YTD
24 -- and A_OTHER_RETIREMENT_LUMP_SUMS_ASG_RETRO_TAX_PTD
25 -- L. Kloppers 12-Sep-2002 115.2 2224332 Added Function get_sars_code to return correct SARS Code
26 -- in case of a Director or Foreign Income
27 -- N. Venugopal 11-Aug-2003 115.4 3069004 Added A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_NRFI_ASG_TAX_YTD,
28 -- A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_RFI_ASG_TAX_YTD
29 -- Nageswara 01-Feb-2004 115.5 3396163 Added
30 -- A_LABOUR_BROKER_PAYMENTS_NRFI_ASG_TAX_YTD,
31 -- A_ANNUAL_LABOUR_BROKER_PAYMENTS_NRFI_ASG_TAX_YTD,
32 -- A_INDEPENDENT_CONTRACTOR_PAYMENTS_RFI_ASG_TAX_YTD,
33 -- A_INDEPENDENT_CONTRACTOR_PAYMENTS_NRFI_ASG_TAX_YTD,
34 -- A_ANNUAL_INDEPENDENT_CONTRACTOR_PAYMENTS_RFI_ASG_TAX_YTD,
35 -- A_ANNUAL_INDEPENDENT_CONTRACTOR_PAYMENTS_NRFI_ASG_TAX_YTD
36 -- Nageswara 13-Feb-2004 115.6 3396163 A_LABOUR_BROKER_PAYMENTS_RFI_ASG_TAX_YTD,
37 -- A_ANNUAL_LABOUR_BROKER_PAYMENTS_RFI_ASG_TAX_YTD,
38 -- Commented 'serverout on size'
39 -- Nageswara 20-Oct-2004 115.7 3931277 Added
40 -- A_ANNUAL_PAYMENT_OF_EMPLOYEE_DEBT_RFI_ASG_TAX_YTD
41 -- A_ANNUAL_PAYMENT_OF_EMPLOYEE_DEBT_NRFI_ASG_TAX_YTD
42 -- Local variable initialization moved to body section instead
43 -- in Declarative section. - GSCC standard
44 -- Nageswara 31-Dec-2004 115.8 4083627 Added
45 -- A_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_RFI_ASG_TAX_YTD
46 -- A_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_NRFI_ASG_TAX_YTD
47 -- A_NON_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_ASG_TAX_YTD
48 -- A_EXECUTIVE_EQUITY_SHARES_RFI_ASG_TAX_YTD
49 -- A_EXECUTIVE_EQUITY_SHARES_NRFI_ASG_TAX_YTD
50 -- R. V. Pahune 25-Aug-2005 115.10 4346920 For Tax Directive Number Source Type in 'I', 'E'
51 -- as After Migration the tax directive Number is fed inderectly
52 -- by formulae result. and pay_run_results.status <> 'B'
53 -- R.V. Pahune 27-Sep-2005 115.13 4346920 changes the query for lump_sum_indicator.
54 -- A. Mahanty 27-Jan-2006 115.14 4346920 Removed
55 -- A_NON_TAXABLE_ARBITRATION_AWARD_ASG_LMPSM_TAX_YTD
56 -- A. Mahanty 18-May-2006 115.15 5231652 The unused procedure get_tax_data
57 removed
58 REM R Pahune 13-Apr-2007 115.16 Duplicate certificates are produce in case of only
59 REM Lump Sum payment made in tax year.
60 REM R Pahune 24-Apr-2007 115.17 No IT3A in case of Lump Sum Cert.
61 REM R Pahune 22-Jun-2007 115.19 Zero Balances reported in Elec Tax File and
62 REM added missing Med balances
63 REM R Pahune 26-Jun-2007 115.20 return 'A' if l_sum < 0 cond added
64 REM R Babla 05-Mar-2008 115.22 6867418 Changes for sars TYE2008 to include the lump sum
65 REM income sources and tax on the new lump sum
66 REM P Arusia 19-Mar-2008 115.23 6867418 Modified ipr5_indicator function to add canonical_to_number
67 -- ========================================================================
68 */
69 ---------------------------------------------------------------------------
70 -- This function is used to populate the temporary table for the IRP5 and
71 -- IT3A reports
72 -- It returns the sequence number of the temporary values
73 ---------------------------------------------------------------------------
74 function populate_temporary_table
75 (
76 p_irp5_indicator in varchar2,
77 p_payroll_action_id in varchar2,
78 p_employee in number
79 ) return number is
80
81 cursor c_main is
82 select per.employee_number,
83 paa.assignment_action_id ASS_ACTION_ID,
84 ppa.effective_date CF_EFF_DATE,
85 per.national_identifier,
86 per.per_information1,
87 per.per_information2,
88 per.last_name,
89 per.first_name,
90 ass.assignment_number,
91 per.first_name || ' ' ||
92 substr(per.middle_names, 1, (replace(instr(per.middle_names, ',', 1), 0, 250) - 1)) FIRST_NAMES,
93 to_char(per.date_of_birth, 'YYYYMMDD') DATE_OF_BIRTH,
94 substr(per.first_name, 1, 1) || nvl(substr(per.middle_names, 1, 1), '')
95 || substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 1), 0, 250) + 1), 1)
96 || substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 2), 0, 250) + 1), 1)
97 || substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 3), 0, 250) + 1), 1)
98 || substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 4), 0, 250) + 1), 1) INITIALS,
99 per.middle_names,
100 adr.address_line1,
101 adr.address_line2,
102 adr.address_line3,
103 adr.town_or_city,
104 adr.postal_code,
105 ass.assignment_id,
106 ass.location_id,
107 ass.payroll_id,
108 ass.effective_start_date,
109 ass.effective_end_date,
110 aei.aei_information4,
111 aei.aei_information7,
112 aei.aei_information6,
113 aei.aei_information3,
114 nvl(aei.aei_information8, nvl(scl.segment10, '1')) AEI_INFORMATION8,
115 decode(aei.aei_information2, null, per.last_name, aei.aei_information2) AEI_INFORMATION2,
116 hrl.location_code,
117 hrl.address_line_1,
118 hrl.address_line_2,
119 hrl.address_line_3,
120 hrl.town_or_city CITY,
121 hrl.postal_code POSTCODE,
122 org.name ORG_NAME,
123 hoi.organization_id,
124 hoi.org_information1,
125 hoi.org_information3,
126 hoi.org_information4,
127 nvl(fcl.meaning, 'A') NATURE,
128 paa.serial_number
129 from hr_all_organization_units org,
130 per_all_people_f per,
131 per_addresses adr,
132 per_all_assignments_f ass,
133 per_assignment_extra_info aei,
134 hr_soft_coding_keyflex scl,
135 pay_all_payrolls_f ppf,
136 hr_locations hrl,
137 hr_organization_information hoi,
138 hr_all_organization_units hou,
139 fnd_common_lookups fcl,
140 pay_assignment_actions paa,
141 pay_payroll_actions ppa
142 where ppa.payroll_action_id = substr(P_PAYROLL_ACTION_ID, 28)
143 and ppa.action_type = 'X'
144 and ppa.action_status = 'C'
145 and ppa.report_type = 'ZA_IRP5'
146 and paa.payroll_action_id = ppa.payroll_action_id
147 and nvl(substr(paa.serial_number, 1, 1), '1') in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '&')
148 and ass.assignment_id = paa.assignment_id
149 and (ass.assignment_id = P_EMPLOYEE or P_EMPLOYEE is null)
150 and ass.effective_start_date =
151 (
152 select max(paf2.effective_start_date)
153 from per_assignments_f paf2
154 where paf2.effective_start_date <= ppa.effective_date
155 and paf2.assignment_id = paa.assignment_id
156 )
157 and per.person_id = ass.person_id
158 and per.effective_start_date =
159 (
160 select max(per2.effective_start_date)
161 from per_people_f per2
162 where per2.effective_start_date <= ppa.effective_date
163 and per2.person_id = ass.person_id
164 )
165 and ppf.payroll_id = ass.payroll_id
166 and ppf.effective_start_date =
167 (
168 select max(ppf2.effective_start_date)
169 from pay_all_payrolls_f ppf2
170 where ppf2.effective_start_date <= ppa.effective_date
171 and ppf2.payroll_id = ass.payroll_id
172 )
173 and scl.soft_coding_keyflex_id (+) = ppf.soft_coding_keyflex_id
174 and per.person_id = adr.person_id (+)
175 and adr.style (+) = 'ZA'
176 and adr.primary_flag (+) = 'Y'
177 and org.organization_id (+) = ass.organization_id
178 and ass.assignment_id = aei.assignment_id (+)
179 and aei.aei_information7 = hou.organization_id
180 and hou.organization_id = hoi.organization_id
181 and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
182 and hrl.location_id (+) = hou.location_id
183 and hrl.style (+) = 'ZA'
184 and fcl.lookup_type (+) = 'ZA_PER_NATURES'
185 and fcl.lookup_code (+) = aei.aei_information4
186 and fcl.application_id (+) = 800;
187
188 l_irp5_indicator varchar2(1);
189 l_irp5_id number(15);
190
191 begin
192 /*
193 -- Get a unique sequence number to access the temporary table
194 select pay_za_irp5_temp_s.nextval
195 into l_irp5_id
196 from dual;
197
198 -- Populate the temporary table
199 for v_main in c_main
200 loop
201
202 if irp5_indicator(v_main.ass_action_id) = p_irp5_indicator then
203
204 insert into pay_za_irp5_temp
205 values
206 (
207 l_irp5_id,
208 v_main.employee_number,
209 v_main.ass_action_id,
210 v_main.cf_eff_date,
211 v_main.national_identifier,
212 v_main.per_information1,
213 v_main.per_information2,
214 v_main.last_name,
215 v_main.first_name,
216 v_main.assignment_number,
217 v_main.first_names,
218 v_main.date_of_birth,
219 v_main.initials,
220 v_main.middle_names,
221 v_main.address_line1,
222 v_main.address_line2,
223 v_main.address_line3,
224 v_main.town_or_city,
225 v_main.postal_code,
226 v_main.assignment_id,
227 v_main.location_id,
228 v_main.payroll_id,
229 v_main.effective_start_date,
230 v_main.effective_end_date,
231 v_main.aei_information4,
232 v_main.aei_information7,
233 v_main.aei_information6,
234 v_main.aei_information3,
235 v_main.aei_information8,
236 v_main.aei_information2,
237 v_main.location_code,
238 v_main.address_line_1,
239 v_main.address_line_2,
240 v_main.address_line_3,
241 v_main.city,
242 v_main.postcode,
243 v_main.org_name,
244 v_main.organization_id,
245 v_main.org_information1,
246 v_main.org_information3,
247 v_main.org_information4,
248 v_main.nature,
249 v_main.serial_number
250 );
251
252 end if;
253
254 end loop c_main;
255 */
256 return l_irp5_id;
257
258 end populate_temporary_table;
259
260 ---------------------------------------------------------------------------
261 -- This function is used to indicate whether the Certificate is an IRP5 or
262 -- an IT3A
263 ---------------------------------------------------------------------------
264 function irp5_indicator
265 (
266 p_assignment_action_id in number
267 ) return varchar2 is
268
269 l_lump_sum_indicator varchar2(1);
270 l_site number(15, 3);
271 l_paye number(15, 3);
272 l_voluntary_tax number(15, 3);
273 l_total number(15, 3);
274 l_sum number(15);
275 l_lmpsm_sum number(15);
276
277 begin
278
279 -- Local variable initialization - GSCC standards
280 l_lump_sum_indicator := 'N';
281 l_site := 0;
282 l_paye := 0;
283 l_voluntary_tax := 0;
284 l_total := 0;
285 l_sum := 0;
286
287 -- Check whether this is a Lump Sum or a Main Certificate
288
289 Select decode(count(*), 0 ,'Y', 'N')
290 into l_lump_sum_indicator
291 From pay_payroll_actions ppa_arch,
292 pay_assignment_actions paa_arch
293 where paa_arch.assignment_action_id = p_assignment_action_id
294 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
295 and paa_arch.assignment_action_id =
296 (
297 select max(paa.assignment_action_id)
298 from pay_assignment_actions paa
299 where paa.payroll_action_id = ppa_arch.payroll_action_id
300 and paa.assignment_id = paa_arch.assignment_id
301 ) ;
302 -- If this is the Main Certificate
303 if l_lump_sum_indicator = 'N' then
304
305 -- Get the SITE value
306 begin
307
308 select fnd_number.canonical_to_number(arc.value)
309 into l_site
310 from ff_archive_items arc,
311 ff_database_items dbi
312 where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
313 and arc.user_entity_id = dbi.user_entity_id
314 and arc.context1 = p_assignment_action_id;
315
316 exception
317 when no_data_found then
318 l_site := 0;
319
320 end;
321
322 -- Get the PAYE value
323 begin
324
325 select fnd_number.canonical_to_number(arc.value)
326 into l_paye
327 from ff_archive_items arc,
328 ff_database_items dbi
329 where dbi.user_name = 'A_PAYE_ASG_TAX_YTD'
330 and arc.user_entity_id = dbi.user_entity_id
331 and arc.context1 = p_assignment_action_id;
332
333 exception
334 when no_data_found then
335 l_paye := 0;
336
337 end;
338
339 -- Get the Voluntary Tax value
340 begin
341
342 select arc.value
343 into l_voluntary_tax
344 from ff_archive_items arc,
345 ff_database_items dbi
346 where dbi.user_name = 'A_VOLUNTARY_TAX_ASG_TAX_YTD'
347 and arc.user_entity_id = dbi.user_entity_id
348 and arc.context1 = p_assignment_action_id;
349
350 exception
351 when no_data_found then
352 l_voluntary_tax := 0;
353
354 end;
355
356 -- Calculate the Total Tax paid
357 l_total := l_paye + l_voluntary_tax + l_site;
358
359 else
360
361 -- Get the Lump Sum Tax value
362 begin
363
364 select sum(arc.value)
365 into l_total
366 from ff_archive_items arc,
367 ff_database_items dbi
368 where dbi.user_name IN ('A_TAX_ON_LUMP_SUMS_ASG_LMPSM_TAX_YTD',
369 'A_TAX_ON_RETIREMENT_FUND_LUMP_SUMS_ASG_LMPSM_TAX_YTD')
370 and arc.user_entity_id = dbi.user_entity_id
371 and arc.context1 = p_assignment_action_id;
372
373 exception
374 when no_data_found then
375 l_total := 0;
376
377 end;
378
379 end if;
380
381 -- Check whether the assignment had zeroes for all his income balances
382 -- bug 3069004 , Added A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_NRFI_ASG_TAX_YTD, A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_RFI_ASG_TAX_YTD
383 if l_lump_sum_indicator = 'N' then
384
385 begin
386
387 -- Check the Main Certificate Income sources
388 -- Added for the balance feed enhancement
389 select sum(trunc(to_number(arc.value))) value
390 into l_sum
391 from -- pay_za_irp5_bal_codes irp5,
392 ff_archive_items arc,
393 ff_database_items dbi
394 where arc.context1 = p_assignment_action_id
395 and
396 (
397 arc.value is not null
398 or
399 (
400 arc.value is not null
401 and to_number(arc.value) <> 0
402 )
403 )
404 and dbi.user_entity_id = arc.user_entity_id
405 -- and irp5.user_name = dbi.user_name
406 and dbi.user_name in
407 (
408 'A_GROSS_REMUNERATION_ASG_TAX_YTD',
409 'A_GROSS_NON_TAXABLE_INCOME_ASG_TAX_YTD',
410 'A_ARREAR_PROVIDENT_FUND_ASG_TAX_YTD',
411 'A_ARREAR_RETIREMENT_ANNUITY_ASG_TAX_YTD',
412 'A_CURRENT_PROVIDENT_FUND_ASG_TAX_YTD',
413 'A_CURRENT_RETIREMENT_ANNUITY_ASG_TAX_YTD',
414 'A_MEDICAL_AID_CONTRIBUTION_ASG_TAX_YTD'
415 );
416 exception
417 when no_data_found then
418 l_sum := 0;
419 end;
420
421 -- End adding for balance feed enhancement
422 -- Added to get if only lump sum payment is made avoid duplicate certificate
423 begin
424
425 -- Check the Lump Sum Certificate Income Sources
426 select sum(trunc(to_number(arc.value))) value
427 into l_lmpsm_sum
428 from -- pay_za_irp5_bal_codes irp5,
429 ff_archive_items arc,
430 ff_database_items dbi
431 where arc.context1 in (select ch.assignment_action_id
432 from pay_assignment_actions main
433 , pay_assignment_actions ch
434 where main.assignment_action_id = p_assignment_action_id
435 and ch.payroll_action_id = main.payroll_action_id
436 and ch.assignment_action_id < main.assignment_action_id
437 AND ch.assignment_id = main.assignment_id)
438 and
439 (
440 arc.value is not null
441 or
442 (
443 arc.value is not null
444 and arc.value <> 0
445 )
446 )
447 and dbi.user_entity_id = arc.user_entity_id
448 -- and irp5.user_name = dbi.user_name
449 and dbi.user_name in
450 (
451 'A_EXECUTIVE_EQUITY_SHARES_NRFI_ASG_LMPSM_TAX_YTD'
452 ,'A_EXECUTIVE_EQUITY_SHARES_RFI_ASG_LMPSM_TAX_YTD'
453 ,'A_OTHER_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
454 ,'A_OTHER_RETIREMENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
455 ,'A_RESIGNATION_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
456 ,'A_RESIGNATION_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
457 ,'A_RETIREMENT_OR_RETRENCHMENT_GRATUITIES_ASG_LMPSM_TAX_YTD'
458 ,'A_RETIREMENT_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
459 ,'A_RETIREMENT_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
460 ,'A_SHARE_OPTIONS_EXERCISED_NRFI_ASG_LMPSM_TAX_YTD'
461 ,'A_SHARE_OPTIONS_EXERCISED_RFI_ASG_LMPSM_TAX_YTD'
462 ,'A_SPECIAL_REMUNERATION_ASG_LMPSM_TAX_YTD'
463 ,'A_TAXABLE_ARBITRATION_AWARD_NRFI_ASG_LMPSM_TAX_YTD'
464 ,'A_TAXABLE_ARBITRATION_AWARD_RFI_ASG_LMPSM_TAX_YTD'
465 ,'A_SURPLUS_APPORTIONMENT_ASG_LMPSM_TAX_YTD'
466 ,'A_UNCLAIMED_BENEFITS_ASG_LMPSM_TAX_YTD'
467 ,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_RFI_ASG_LMPSM_TAX_YTD'
468 ,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_NRFI_ASG_LMPSM_TAX_YTD'
469 );
470 -- Added for the balance feed enhancement
471
472 -- End adding for balance feed enhancement
473 exception
474 when no_data_found then
475 l_lmpsm_sum := 0;
476
477 end;
478 if l_lmpsm_sum is null then
479 l_lmpsm_sum := 0;
480 end if;
481 l_sum := l_sum - l_lmpsm_sum;
482 -- End if only Lump Sum payment is made
483
484 -- added l_sum < 0 to avoid certificates for zero balances .
485 -- Check the Main Certificate Deductions
486 if ((l_sum = 0) or (l_sum is null)) then
487
488 begin
489
490 select sum(trunc(to_number(arc.value))) value
491 into l_sum
492 from ff_archive_items arc,
493 ff_database_items dbi
494 where arc.context1 = p_assignment_action_id
495 and dbi.user_name IN (
496 'A_ANNUAL_PENSION_FUND_ASG_CLRNO_TAX_YTD'
497 ,'A_CURRENT_PENSION_FUND_ASG_CLRNO_TAX_YTD'
498 ,'A_ANNUAL_ARREAR_PENSION_FUND_ASG_CLRNO_TAX_YTD'
499 ,'A_ARREAR_PENSION_FUND_ASG_CLRNO_TAX_YTD'
500 ,'A_ANNUAL_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
501 ,'A_CURRENT_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
502 ,'A_ARREAR_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
503 ,'A_ANNUAL_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
504 ,'A_CURRENT_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
505 ,'A_ANNUAL_ARREAR_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
506 ,'A_ARREAR_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
507 ,'A_ANNUAL_EE_INCOME_PROTECTION_POLICY_CONTRIBUTIONS_ASG_TAX_YTD'
508 ,'A_EE_INCOME_PROTECTION_POLICY_CONTRIBUTIONS_ASG_TAX_YTD'
509 ,'A_MEDICAL_AID_CONTRIBUTION_ASG_TAX_YTD'
510 ,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_RFI_ASG_TAX_YTD' -- added on 22-May-2007
511 ,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_NRFI_ASG_TAX_YTD'
512 ,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD'
513 ,'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_RFI_ASG_TAX_YTD'
514 ,'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_NRFI_ASG_TAX_YTD'
515 ,'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD'
516 ,'A_MEDICAL_CONTRIBUTIONS_ABATEMENT_ASG_TAX_YTD'
517 ,'A_ANNUAL_MEDICAL_CONTRIBUTIONS_ABATEMENT_ASG_TAX_YTD'
518 )
519 and dbi.user_entity_id = arc.user_entity_id;
520
521 exception
522 when no_data_found then
523 l_sum := 0;
524
525 end;
526
527 end if;
528
529 else
530
531 begin
532
533 -- Check the Lump Sum Certificate Income Sources
534 select sum(trunc(to_number(arc.value))) value
535 into l_sum
536 from -- pay_za_irp5_bal_codes irp5,
537 ff_archive_items arc,
538 ff_database_items dbi
539 where arc.context1 = p_assignment_action_id
540 and
541 (
542 arc.value is not null
543 or
544 (
545 arc.value is not null
546 and arc.value <> 0
547 )
548 )
549 and dbi.user_entity_id = arc.user_entity_id
550 -- and irp5.user_name = dbi.user_name
551 and dbi.user_name in
552 (
553 'A_EXECUTIVE_EQUITY_SHARES_NRFI_ASG_LMPSM_TAX_YTD'
554 ,'A_EXECUTIVE_EQUITY_SHARES_RFI_ASG_LMPSM_TAX_YTD'
555 ,'A_OTHER_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
556 ,'A_OTHER_RETIREMENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
557 ,'A_RESIGNATION_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
558 ,'A_RESIGNATION_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
559 ,'A_RETIREMENT_OR_RETRENCHMENT_GRATUITIES_ASG_LMPSM_TAX_YTD'
560 ,'A_RETIREMENT_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
561 ,'A_RETIREMENT_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
562 ,'A_SHARE_OPTIONS_EXERCISED_NRFI_ASG_LMPSM_TAX_YTD'
563 ,'A_SHARE_OPTIONS_EXERCISED_RFI_ASG_LMPSM_TAX_YTD'
564 ,'A_SPECIAL_REMUNERATION_ASG_LMPSM_TAX_YTD'
565 ,'A_TAXABLE_ARBITRATION_AWARD_NRFI_ASG_LMPSM_TAX_YTD'
566 ,'A_TAXABLE_ARBITRATION_AWARD_RFI_ASG_LMPSM_TAX_YTD'
567 ,'A_SURPLUS_APPORTIONMENT_ASG_LMPSM_TAX_YTD'
568 ,'A_UNCLAIMED_BENEFITS_ASG_LMPSM_TAX_YTD'
569 ,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_RFI_ASG_LMPSM_TAX_YTD'
570 ,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_NRFI_ASG_LMPSM_TAX_YTD'
571
572 );
573 -- Added for the balance feed enhancement
574
575 -- End adding for balance feed enhancement
576 exception
577 when no_data_found then
578 l_sum := 0;
579
580 end;
581
582 end if;
583
584 if ((l_sum <= 0) or (l_sum is null)) then
585
586 -- If the assignment had zero for all his balances then don't include him
587 return 'A';
588
589 else -- Check for IRP5/IT3A
590
591 if l_total > 0 then
592
593 return 'Y';
594
595 else
596
597 return 'N';
598
599 end if;
600
601 end if;
602
603 end irp5_indicator;
604
605 ---------------------------------------------------------------------------
606 -- This function is used to retrieve the Tax Status, Tax Directive Number
607 -- and Tax Directive Value Input Values from the ZA_Tax element
608 ---------------------------------------------------------------------------
609 --Bug 5231652
610 -- Removed, as this procedure was not referenced anymore
611 /*
612 procedure get_tax_data
613 (
614 assignment_id in number,
615 assignment_action_id in number,
616 date_earned in date,
617 p_tax_status in out nocopy varchar2,
618 p_directive_number in out nocopy varchar2,
619 p_directive_value in out nocopy number,
620 p_lump_sum_indicator in varchar2
621 ) is
622 */
623 --
624 /*-----------------------------------------------------------------------------
625 Name : get_sars_code
626 Purpose : Returns the correct SARS Code for Directors and/or Foreign Income
627 Arguments : SARS Code
628 Tax Status
629 Nature of Person
630 Notes : This function is used to establish the SARS Code that needs to
631 be printed on Tax Year End Reports and the Electronic Tax Year
632 End File. It works on the principle that where the Nature of Person
633 is 'C' (Director of Private Company or Member of Close Corporation)
634 OR where Tax Status is 'M', 'N', 'P' or 'Q' (Private Director,
635 Private Director with Directive Amount, Private Director with
636 Directive Percentage or Private Director Zero Tax), the SARS Code
637 3601 must be changed to 3615, and additionally,
638 where Nature of Person is 'M' (Foreign Employment Income),
639 50 must be added to the SARS Code.
640 -----------------------------------------------------------------------------*/
641
642 function get_sars_code
643 (
644 p_sars_code in varchar2,
645 p_tax_status in varchar2,
646 p_nature in varchar2
647 ) return varchar2 is
648
649 l_tax_status fnd_lookup_values.lookup_code%type;
650 l_nature fnd_lookup_values.meaning%type;
651 l_sars_code varchar2(256);
652
653 begin
654
655 -- Local variable initialization - GSCC standards
656 l_sars_code := 0;
657
658 if ((p_nature = 'C' or (p_tax_status in ('M', 'N', 'P', 'Q'))) and p_sars_code = '3601')
659 then
660 l_sars_code := '3615';
661 else
662 l_sars_code := p_sars_code;
663 end if;
664
665 if (p_nature = 'M' and to_number(l_sars_code) >= 3601 and to_number(l_sars_code) <= 3907
666 and to_number(l_sars_code) not in (3695, 3696, 3697, 3698, 3699))
667 then
668 l_sars_code := to_char(to_number(l_sars_code) + 50);
669 end if;
670
671 return l_sars_code;
672
673 end get_sars_code;
674
675
676 end py_za_tax_certificates;
[Home] [Help]