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