[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_TYE_ARCHIVE_PKG
Source
1 PACKAGE BODY PAY_ZA_TYE_ARCHIVE_PKG as
2 /* $Header: pyzatyea.pkb 120.32.12020000.25 2012/12/21 14:41:53 abdash ship $ */
3
4 g_package varchar2(30) := 'pay_za_tye_archive_pkg.' ;
5 sql_range varchar2(4000);
6
7 /*
8 ZA Tax Year End Data Archive Structure
9 --------------------------------------
10 Action Context Type : PA
11 Action_Info_Categiry: ZATYE_EMPLOYER_INFO
12 Action_Information1 : Code 2010 (Trading name)
13 Action_Information2 : Code 2020 (PAYE Ref Number)
14 Action_Information3 : Code 2022 (SDL Ref Num)
15 Action_Information4 : Code 2024 (UIF Ref Num)
16 Action_Information5 : Code 2025 (Employer Contact Person)
17 Action_Information6 : Code 2026 (Employer Contact Number)
18 Action_Information7 : Code 2027 (Employer e-mail Address)
19 Action_Information8 : Code 2028 (Payroll Software)
20 Action_Information9 : Code 2030 (Transaction Year)
21 Action_Information10: Code 2031 (Period of Reconciliation)
22 Action_Information11: Code 2035 (Employer Trade Classification)
23 Action_Information12: Code 2061 (Er Address : Unit Num)
24 Action_Information13: Code 2062 (Er Address : Complex)
25 Action_Information14: Code 2063 (Er Address : Street Num)
26 Action_Information15: Code 2064 (Er Address : Street / Name of Farm)
27 Action_Information16: Code 2065 (Er Address : Suburb/District)
28 Action_Information17: Code 2066 (Er Address : City/Town)
29 Action_Information18: Code 2080 (Er Address: Postal Code)
30
31 Action Context Type : AAP
32 Action_Info_Categiry: ZATYE_EMPLOYEE_INFO
33 Action_Information1 : Code 3010 (Certificate Num)
34 Action_Information2 : Code 3015 (Type of Certificate)
35 Action_Information3 : Code 3020 (Nature of Person)
39 Action_Information7 : Code 3050 (Initials)
36 Action_Information4 : Code 3025 (Year of Assessment)
37 Action_Information5 : Code 3030 (Surname or Trading Name)
38 Action_Information6 : Code 3040 (First 2 Names)
40 Action_Information8 : Code 3060 (Identity Number)
41 Action_Information9 : Code 3070 (Passport Number)
42 Action_Information10: Code 3075 (Country of Issue)
43 Action_Information11: Code 3080 (Date of Birth)
44 Action_Information12: Code 3100 (Income Tax Ref Num)
45 Action_Information13: Code 3160 (Employee Number)
46 Action_Information14: Code 3170 (Date Employed From)
47 Action_Information15: Code 3180 (Date Employed To)
48 Action_Information16: Code 3200 (Pay prds in Yr of Assessment)
49 Action_Information17: Code 3210 (Pay Periods Worked)
50 Action_Information18: Code 3230_1 (Directive Number - 1)
51 Action_Information19: Code 3230_2 (Directive Number - 2)
52 Action_Information20: Code 3230_3 (Directive Number - 3)
53 Action_Information21: Code 3240 (Bank Account Type)
54 Action_Information22: Code 3241 (Bank Account Number)
55 Action_Information23: Code 3242 (Bank Branch Number)
56 Action_Information24: Code 3243 (Bank Name)
57 Action_Information25: Code 3244 (Bank Branch Name)
58 Action_Information26: Code 3245 (A/c Holder Name)
59 Action_Information27: Code 3246 (A/C Holder Relationship)
60 Action_Information28: Old/Manual Certificate (O/M/OM)
61 Action_Information29: Manual Certificate Number
62 Action_Information30: Temporary Certificate Number
63
64
65 Action Context Type : AAP
66 Action_Info_Categiry: ZATYE_EMPLOYEE_CONTACT_INFO
67 Action_Information1 : Code 3010 (Certificate Num)
68 Action_Information2 : Code 3125 (Contact E-mail)
69 Action_Information3 : Code 3135 (Home Telephone Num)
70 Action_Information4 : Code 3136 (Bus Telephone Num)
71 Action_Information5 : Code 3137 (Fax Num)
72 Action_Information6 : Code 3138 (Cell Num)
73 Action_Information7 : Code 3144 (Addr Bus: Unit Num)
74 Action_Information8 : Code 3145 (Addr Bus: Complex)
75 Action_Information9 : Code 3146 (Addr Bus : Street Num)
76 Action_Information10: Code 3147 (Addr Bus: Street/Name of Farm)
77 Action_Information11: Code 3148 (Addr Bus: Suburb/District)
78 Action_Information12: Code 3149 (Addr Bus: City/Town)
79 Action_Information13: Code 3150 (Addr Bus: Postal Code)
80 Action_Information14: Code 3211 (Addr Res: Unit Number)
81 Action_Information15: Code 3212 (Addr Res: Complex)
82 Action_Information16: Code 3213 (Addr Res: Street Number)
83 Action_Information17: Code 3214 (Addr Res: Street/Name of Farm)
84 Action_Information18: Code 3215 (Addr Res: Suburb/District)
85 Action_Information19: Code 3216 (Addr Res: City/Town)
86 Action_Information20: Code 3217 (Addr Res: Postal Code)
87 Action_Information21: Code 3218 (Postal Addr Same as Res)
88 Action_Information22: Code 3221 (Addr Pos: Line 1)
89 Action_Information23: Code 3222 (Addr Pos: Line2)
90 Action_Information24: Code 3223 (Addr Pos: Line3)
91 Action_Information25: Code 3229 (Addr Pos: Postal Code)
92 Action_Information26: Main Cert(MAIN)/Lumpsum Cert(LMPSM)
93 Action_Information27: Location Code
94 Action_Information30: Temporary Certificate Number
95
96
97 Action Context Type : AAP
98 Action_Info_Categiry: ZATYE_EMPLOYEE_INCOME
99 Action_Information1 : Code 3010 (Certificate Num)
100 Action_Information2 : Code Name
101 Action_Information3 : Code Included In
102 Action_Information4 : Code Value
103 Action_Information5 : Code Group Value
104 Action_Information30: Temporary Certificate Number
105
106 Action Context Type : AAP
107 Action_Info_Categiry: ZATYE_EMPLOYEE_LUMPSUMS
108 Action_Information1 : Code 3010 (Certificate Num)
109 Action_Information2 : Code Name
110 Action_Information3 : Code Included In
111 Action_Information4 : Normal Ceritificate Code Value
112 Action_Information5 : Code Group Value
113 Action_Information6 : Normal Certificate Directive
114 Action_Information7 : Directive 1
115 Action_Information8 : Directive 1 Value
116 Action_Information9 : Directive 2
117 Action_Information10: Directive 2 Value
118 Action_Information11: Directive 3
119 Action_Information12: Directive 3 Value
120 Action_Information30: Temporary Certificate Number
121
122 Action Context Type : AAP
123 Action_Info_Categiry: ZATYE_FINAL_EE_INCOME_1
124 Action_Information1 : Code 3010 (Certificate Num)
125 Action_Information2 : Code Name
126 Action_Information3 : Code Value
127 Action_Information4 : Code Name
128 Action_Information5 : Code Value
129 Action_Information6 : Code Name
130 Action_Information7 : Code Value
131 Action_Information8 : Code Name
132 Action_Information9 : Code Value
133 Action_Information10 : Code Name
134 Action_Information11 : Code Value
135 Action_Information12 : Code Name
136 Action_Information13 : Code Value
137 Action_Information14 : Code Name
138 Action_Information15 : Code Value
139 Action_Information16 : Code Name
140 Action_Information17 : Code Value
141 Action_Information18 : Code Name
142 Action_Information19 : Code Value
143 Action_Information20 : Code Name
144 Action_Information21 : Code Value
145 Action_Information22 : Code Name
146 Action_Information23 : Code Value
147 Action_Information24 : Code Name
148 Action_Information25 : Code Value
149 Action_Information26 : Code Name
150 Action_Information27 : Code Value
151 Action_Information30: Temporary Certificate Number
152
153 Action Context Type : AAP
154 Action_Info_Categiry: ZATYE_FINAL_EE_INCOME_2
155 Action_Information1 : Code 3010 (Certificate Num)
156 Action_Information2 : Code Name
157 Action_Information3 : Code Value
158 Action_Information4 : Code Name
159 Action_Information5 : Code Value
163 Action_Information9 : Code Value
160 Action_Information6 : Code Name
161 Action_Information7 : Code Value
162 Action_Information8 : Code Name
164 Action_Information10 : Code Name
165 Action_Information11 : Code Value
166 Action_Information12 : Code Name
167 Action_Information13 : Code Value
168 Action_Information14 : Code Name
169 Action_Information15 : Code Value
170 Action_Information16 : Code Name
171 Action_Information17 : Code Value
172 Action_Information18 : Code Name
173 Action_Information19 : Code Value
174 Action_Information20 : Code Name
175 Action_Information21 : Code Value
176 Action_Information22 : Code Name
177 Action_Information23 : Code Value
178 Action_Information24 : Code Name
179 Action_Information25 : Code Value
180 Action_Information26 : Code Name
181 Action_Information27 : Code Value
182 Action_Information30: Temporary Certificate Number
183
184 Action Context Type : AAP
185 Action_Info_Categiry: ZATYE_EMPLOYEE_GROSS_REMUNERATIONS
186 Action_Information1 : Code 3010 (Certificate Num)
187 Action_Information2 : Code 3696 (Non-Taxable Income)
188 Action_Information3 : Code 3697 (Gross Retirement Funding Income)
189 Action_Information4 : Code 3698 (Gross Non-Retire't Funding Inc)
190 Action_Information5 : Gross PKG (for use in exception log)
191 Action_Information30: Temporary Certificate Number
192
193 Action Context Type : AAP
194 Action_Info_Categiry: ZATYE_EMPLOYEE_DEDUCTIONS
195 Action_Information1 : Code 3010 (Certificate Num)
196 Action_Information2 : Code Name
197 Action_Information3 : Code Included In
198 Action_Information4 : Code Value
199 Action_Information5 : Code Group Value
200 Action_Information30: Temporary Certificate Number
201
202 Action Context Type : AAP
203 Action_Info_Categiry: ZATYE_FINAL_EE_DEDUCTIONS
204 Action_Information1 : Code 3010 (Certificate Num)
205 Action_Information2 : Code Name
206 Action_Information3 : Code Value
207 Action_Information4 : Code Name
208 Action_Information5 : Code Value
209 Action_Information6 : Code Name
210 Action_Information7 : Code Value
211 Action_Information8 : Code Name
212 Action_Information9 : Code Value
213 Action_Information10 : Code Name
214 Action_Information11 : Code Value
215 Action_Information12 : Code Name
216 Action_Information13 : Code Value
217 Action_Information14 : Code Name
218 Action_Information15 : Code Value
219 Action_Information16 : Code Name
220 Action_Information17 : Code Value
221 Action_Information18 : Code Name
222 Action_Information19 : Code Value
223 Action_Information20 : Code Name
224 Action_Information21 : Code Value
225 Action_Information22 : Code Name
226 Action_Information23 : Code Value
227 Action_Information24 : Code Name
228 Action_Information25 : Code Value
229 Action_Information26 : Code Name
230 Action_Information27 : Code Value
231 Action_Information30: Temporary Certificate Number
232
233 Action Context Type : AAP
234 Action_Info_Categiry: ZATYE_EMPLOYEE_TAX_AND_REASONS
235 Action_Information1 : Code 3010 (Certificate Num)
236 Action_Information2 : Code 4497(Total Deductions/ Contributions)
237 Action_Information3 : Code 4101 (SITE)
238 Action_Information4 : Code 4102 (PAYE+Voluntary Tax + Tax On Lumpsum)
239 Action_Information5 : Code 4115 (PAYE on retire't lumpsum benefits)
240 Action_Information6 : Code 4141 (Ee + Er UIF Contributions)
241 Action_Information7 : Code 4142 (Er SDL Contributions)
242 Action_Information8 : Code 4149 (Total Tax, SDL, UIF)
243 Action_Information9 : Code 4150 (Reason Code for IT3(a))
244 Action_Information10: Tax (for use in Exception log)
245 Action_Information11: PAYE(for use in Exception log)
246 Action_Information13: Code 4116 (Med Tax Credit) --TYE 2012
247 Action_Information30: Temporary Certificate Number
248 Action_Information12: Terminated flag if Tax year > 2010 and period of reconciliation is 08
249 -- TYE 2013 Feb
250 Action_Information14:Flag to show if balance "Comp iro Death Non Taxable" has a value even when value for code 3922 is above R300 000
251 or Comp iro Death RFI/NRFI have value when value for code 3922 is less than R300 000
252 */
253
254
255 -----------------
256 -- Declare PL/SQL tables
257 -----------------
258
259 ----
260 -- This table stores distinct code_names and a broad classification of them
261 -- (INCOME, LUMPSUM, DEDUCTION, SITE, PAYE, PAYE_RET_LMPSM).
262 -- Attribute lumpsum = Y/N
263 -- Index will be code_name
264 ----
265 type code_list is record
266 ( code_type varchar2(100),
267 lumpsum varchar2(1)
268 );
269
270 ----
271 -- This table stores the details for every code.
272 -- Index will be a running sequence
273 -- This table may contain multiple rows for a code
274 -- Each row will contain the code_name, balance feeding this code,
275 -- and a subtype
276 -- (NON_TAXABLE, RFI, NRFI, PKG -- for income sources
277 -- RFI_LUMPSUM, LUMPSUM -- for Lumpsum sources
278 -- DEDUCTION -- for deduction sources
279 -- SITE, PAYE, PAYE_RET_LMPSM)
280 ----
281 type code_balances is record
282 ( code number,
283 defined_balance_id number,
284 full_balance_name varchar2(100),
285 balance_type_id number,
286 sub_type varchar2(100)
287 );
288
289
290 ----
291 -- This table stores values for all codes per assignment
292 -- This will be initialised and used as a local variable in the archive_code section
293 -- For that particular assignment, this table will contain the values of various codes
297 , included_in varchar2(100)
294 ----
295 type asg_code_rec is record
296 ( value number
298 , group_value number
299 );
300
301 ----
302 -- This table stores the directive numbers for an assignment
303 -- This will be initialised and used as a local variable in the archive_code section
304 -- For that particular assignemnt, this table will contain all the directive numbers
305 -- including 'To Be Advised' of present.
306 ----
307 type dir_num_rec is record
308 ( certificate_type varchar2(4),
309 certificate_merged_with_main varchar2(1)
310 );
311
312 ---
313 -- This table will store the final code values to be reported for this assignment
314 -- Index will the SARS codes
315 -- This is used so as to traverse from first to last of its index (code)
316 -- thereby producing the output as sorted by code
317 ---
318 type final_archive_rec is record (
319 value number,
320 code_type varchar2(20)
321 );
322
323 ----
324 -- This table will be used as a transit variable to pass information column values to
325 -- archive api
326 ----
327 type act_info_rec is record
328 ( assignment_id number(20)
329 ,person_id number(20)
330 ,effective_date date
331 ,action_info_category varchar2(50)
332 ,act_info1 varchar2(300)
333 ,act_info2 varchar2(300)
334 ,act_info3 varchar2(300)
335 ,act_info4 varchar2(300)
336 ,act_info5 varchar2(300)
337 ,act_info6 varchar2(300)
338 ,act_info7 varchar2(300)
339 ,act_info8 varchar2(300)
340 ,act_info9 varchar2(300)
341 ,act_info10 varchar2(300)
342 ,act_info11 varchar2(300)
343 ,act_info12 varchar2(300)
344 ,act_info13 varchar2(300)
345 ,act_info14 varchar2(300)
346 ,act_info15 varchar2(300)
347 ,act_info16 varchar2(300)
348 ,act_info17 varchar2(300)
349 ,act_info18 varchar2(300)
350 ,act_info19 varchar2(300)
351 ,act_info20 varchar2(300)
352 ,act_info21 varchar2(300)
353 ,act_info22 varchar2(300)
354 ,act_info23 varchar2(300)
355 ,act_info24 varchar2(300)
356 ,act_info25 varchar2(300)
357 ,act_info26 varchar2(300)
358 ,act_info27 varchar2(300)
359 ,act_info28 varchar2(300)
360 ,act_info29 varchar2(300)
361 ,act_info30 varchar2(300)
362 );
363
364 type lump_dir_set_rec is record
365 ( dir_1 varchar2(100)
366 , dir_2 varchar2(100)
367 , dir_3 varchar2(100)
368 );
369
370 type code_list_table is table of code_list index by binary_integer;
371 type code_balances_table is table of code_balances index by binary_integer;
372 type code_table is table of asg_code_rec index by varchar2(100);
373 type dir_num_table is table of dir_num_rec index by varchar2(100);
374 type action_info_table is table of act_info_rec index by binary_integer;
375 type final_archive_table is table of final_archive_rec index by binary_integer;
376
377 type lump_dir_set_tab is table of lump_dir_set_rec index by binary_integer;
378
379 -------------------
380 -- Forward declaration of functions and procedures
381 -------------------
382 procedure set_code_tables;
383 function names(name varchar2) return varchar2 ;
384 function initials(name varchar2) return varchar2 ;
385 procedure get_phones (p_person_id in number
386 , p_effective_date in date
387 , p_home_phone out nocopy varchar2
388 , p_work_phone out nocopy varchar2
389 , p_fax out nocopy varchar2
390 , p_cell_number out nocopy varchar2
391 );
392 procedure combine_certificates(
393 p_main_cert_type in varchar2
394 , p_main_cert_dir_num in varchar2
395 , t_code in code_table
396 , t_dir_num in out nocopy dir_num_table
397 , t_lump_dir_set out nocopy lump_dir_set_tab
398 , p_directive_1 out nocopy varchar2
399 , p_directive_2 out nocopy varchar2
400 , p_directive_3 out nocopy varchar2
401 );
402 procedure insert_archive_row(
403 p_assactid in number
404 , p_tab_rec_data in action_info_table
405 ) ;
406 procedure fetch_balances (
407 p_assignment_action_id in number
408 , t_dir_num in dir_num_table
409 , p_period_recon in varchar2 -- 9901425 fix
410 , t_code out nocopy code_table
411 );
412 function get_balance_value (
413 p_bal_name in varchar2
414 , p_dim_name in varchar2
418 p_run_assact_id in number
415 , p_asg_act_id in number )
416 return number ;
417 procedure populate_irp5_indicators(
419 , t_code in code_table
420 , p_main_cert_type out nocopy varchar2
421 , t_dir_num in out nocopy dir_num_table
422 ) ;
423 procedure consolidate_codes(
424 t_dir_num in out nocopy dir_num_table
425 , t_code in out nocopy code_table
426 , p_pre_merge in boolean
427 );
428 function it3a_reason_code(
429 p_run_assact_id in number
430 , p_nature in varchar2
431 , p_tax_status in varchar2
432 , p_normal_directive_value in varchar2
433 , p_gross_total in number
434 , p_gross_non_txble_income in number
435 , p_lmpsm_cert in varchar2
436 , p_tax_on_lmpsm in number
437 , p_independent_contractor in varchar2
438 , p_foreign_income in varchar2
439 , p_labour_broker in VARCHAR2
440 , l_4116 in number)
441 return varchar2 ;
442 procedure copy_record (
443 from_rec in act_info_rec
444 , to_rec in out nocopy act_info_rec
445 ) ;
446 function final_code (p_tax_year in varchar2 -- Bug 13367825
447 , p_code_complete in varchar2
448 , p_nature in varchar2
449 , p_tax_status in varchar2
450 , p_foreign_income in varchar2
451 ) return varchar2 ;
452 function get_def_bal_id (
453 p_bal_type_id in number
454 , p_dim_name in varchar2) return number ;
455
456 procedure fetch_person_data (p_assactid in number
457 , p_effective_date in date
458 , p_itreg_batch in varchar2
459 , p_tax_status in varchar2
460 , p_employee_info_rec out nocopy act_info_rec
461 , p_employee_contact_info_rec out nocopy act_info_rec
462 , p_assignment_id out nocopy number
463 , p_person_id out nocopy number
464 , p_foreign_income out nocopy varchar2
465 , pactid out nocopy number
466 , p_nature out nocopy varchar2
467 , p_independent_contractor out nocopy varchar2
468 , p_labour_broker out nocopy varchar2
469 , p_lumpsum_date out nocopy date);
470
471 -----------------------------
472 -- Global variables
473 -----------------------------
474 g_code_list code_list_table ;
475 g_code_bal code_balances_table;
476 g_defined_balance_lst_normal pay_balance_pkg.t_balance_value_tab; -- used for batch balance retrieval
477 g_defined_balance_lst_lmpsm pay_balance_pkg.t_balance_value_tab; -- used for batch balance retrieval
478
479 g_tax_year varchar2(4);
480
481 /*--------------------------------------------------------------------------
482 Name : range_cursor
483 Purpose : 1) Archives Employer level information
484 2) This returns the select statement that is used to create the
485 range rows.
486 Arguments :
487 Notes : The range cursor determines which people should be processed.
488 The normal practice is to include everyone, and then limit
489 the list during the assignment action creation.
490 --------------------------------------------------------------------------*/
491 procedure range_cursor (pactid in number,
492 sqlstr out nocopy varchar2) as
493
494 -- Get 'ZA Tax Information' (Context ZA_LEGAL_ENTITY)
495 cursor csr_tax_info (p_legal_entity_org hr_all_organization_units.organization_id%type)is
496 select substr(hoi.org_information1, 1, 90) er_trade_name, -- Employer Trading or Other Name (Code 2010)
497 hoi.org_information3 paye_ref_num, -- PAYE Ref Num (Code 2020)
498 upper(hoi.org_information6) uif_ref_num, -- UIF Ref Num (Code 2024)
499 upper(hoi.org_information12) sdl_ref_num, -- UIF Ref Num (Code 2024)
500 hoi.org_information13 er_trade_class -- Employer Trade Classification (Code 2035)
501 from hr_organization_information hoi
502 where hoi.organization_id = p_legal_entity_org
503 and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
504
505 -- Get 'ZA Tax File Information' (Context ZA_GRE_TAX_FILE_ENTITY)
506 cursor csr_tax_file_creater_inf(p_legal_entity_org number) is
507 select (substr(hoi.org_information1, 1, 30)) er_contact_person, -- code 2025
508 hoi.org_information2 er_contact_number, -- code 2026
509 hoi.org_information3 er_email_address, -- code 2027
510 substr(hoi.org_information4,1,5) unit_number, -- Address : Unit Number (Code 2061)
514 substr(hoi.org_information8,1,34) suburb_district, -- Address : Suburb/District (Code 2065)
511 substr(hoi.org_information5,1,25) complex, -- Address : Complex (Code 2062)
512 substr(hoi.org_information6,1,5) street_number, -- Address : Street Number (Code 2063)
513 substr(hoi.org_information7,1,25) street_farm, -- Address : Street/Name of Farm (Code 2064)
515 substr(hoi.org_information9,1,23) town_city, -- Address : Town/Cuty (Code 2066)
516 substr(hoi.org_information10,1,4) postal_code -- Address : Postal Code (Code 2080)
517 from hr_organization_information hoi
518 where hoi.organization_id = p_legal_entity_org
519 and hoi.org_information_context = 'ZA_GRE_TAX_FILE_ENTITY';
520
521 l_proc varchar2(100) := g_package||'range_cursor';
522 l_legal_entity_org number;
523 l_action_info_id number;
524 l_ovn number;
525 l_tax_year varchar2(4);
526 l_cert_type_param varchar2(1);
527 l_itreg_batch varchar2(1);
528 rec_tax_info csr_tax_info%rowtype;
529 rec_tax_file_creater_inf csr_tax_file_creater_inf%rowtype;
530 leg_param pay_payroll_actions.legislative_parameters%type;
531 l_period_recon varchar2(30); -- 9877034 fix
532
533 l_asg_set_id number;
534 l_person_id number;
535
536 begin
537 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
538 hr_utility.set_location('Entering '||l_proc,10);
539
540 -- Archive Legal Entity Level (Employer) Information
541
542 -- Retrieve legislative parameters from the archiver payroll action
543 select legislative_parameters
544 into leg_param
545 from pay_payroll_actions
546 where payroll_action_id = pactid;
547
548 l_legal_entity_org := get_parameter('LEGAL_ENTITY', leg_param);
549 l_tax_year := get_parameter('TAX_YEAR', leg_param);
550 l_cert_type_param := get_parameter('CERT_TYPE', leg_param);
551 l_period_recon := NVL(get_parameter('PERIOD_RECON',leg_param),'02'); -- 9877034 fix
552 fnd_file.put_line (fnd_file.LOG, '$$$ PERIOD_RECON '||l_period_recon);
553
554 l_asg_set_id := get_parameter('ASG_SET_ID', leg_param);
555 l_person_id := get_parameter('PERSON_ID', leg_param);
556
557 if l_cert_type_param = 1 then
558 -- This is a normal (IRP5/IT3A) archive process
559 l_itreg_batch := 'N';
560 hr_utility.set_location(l_proc,15);
561 else
562 -- This is an ITREG batch process
563 l_itreg_batch := 'Y';
564 hr_utility.set_location(l_proc,16);
565 end if;
566
567 hr_utility.set_location(l_proc,20);
568
569 -- Fetch Legal Entity level information
570 open csr_tax_info(l_legal_entity_org);
571 fetch csr_tax_info into rec_tax_info;
572 close csr_tax_info ;
573
574 open csr_tax_file_creater_inf(l_legal_entity_org);
575 fetch csr_tax_file_creater_inf into rec_tax_file_creater_inf;
576 close csr_tax_file_creater_inf;
577
578 hr_utility.set_location(l_proc,30);
579
580 -- remove special characters from UIF Ref Num
581 rec_tax_info.uif_ref_num := translate(rec_tax_info.uif_ref_num,
582 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
583 'U0123456789');
584
585 -- archive data
586 hr_utility.set_location(l_proc,40);
587 pay_action_information_api.create_action_information
588 (
589 p_action_information_id => l_action_info_id,
590 p_action_context_id => pactid,
591 p_action_context_type => 'PA',
592 p_object_version_number => l_ovn,
593 p_effective_date => sysdate,
594 p_action_information_category => 'ZATYE_EMPLOYER_INFO',
595 p_action_information1 => rec_tax_info.er_trade_name,
596 p_action_information2 => rec_tax_info.paye_ref_num,
597 p_action_information3 => rec_tax_info.sdl_ref_num,
598 p_action_information4 => rec_tax_info.uif_ref_num,
599 p_action_information5 => rec_tax_file_creater_inf.er_contact_person,
600 p_action_information6 => rec_tax_file_creater_inf.er_contact_number,
601 p_action_information7 => rec_tax_file_creater_inf.er_email_address,
602 p_action_information8 => 'PAG1016', -- Bug 14341749
603 p_action_information9 => case when l_itreg_batch='N' then l_tax_year else null end,
604 --p_action_information10 => case when l_itreg_batch='N' then (l_tax_year || l_period_recon ) else null end, -- 9877034 fix
605 p_action_information10 => case when (l_itreg_batch='N' and l_period_recon='02') then ( l_tax_year || l_period_recon )
606 when (l_itreg_batch='N' and l_period_recon='08') then ( l_tax_year-1 || l_period_recon )
607 else null
608 end,
609 p_action_information11 => rec_tax_info.er_trade_class,
610 p_action_information12 => rec_tax_file_creater_inf.unit_number,
611 p_action_information13 => rec_tax_file_creater_inf.complex,
612 p_action_information14 => rec_tax_file_creater_inf.street_number,
613 p_action_information15 => rec_tax_file_creater_inf.street_farm,
614 p_action_information16 => rec_tax_file_creater_inf.suburb_district,
615 p_action_information17 => rec_tax_file_creater_inf.town_city,
616 p_action_information18 => rec_tax_file_creater_inf.postal_code
617 );
618
619 hr_utility.set_location(l_proc,50);
620
621 sql_range :=
622 'SELECT distinct paf.person_id
623 FROM per_assignments_f paf,
624 pay_payrolls_f PPY,
625 pay_payroll_actions PPA,
626 per_assignment_extra_info AEI
627 WHERE PPA.payroll_action_id = :payroll_action_id
628 AND paf.business_group_id = PPA.business_group_id
629 AND paf.assignment_type = ''E''
630 AND PPY.payroll_id = paf.payroll_id
631 AND AEI.assignment_id = paf.assignment_id
632 AND AEI.aei_information_category = ''ZA_SPECIFIC_INFO''
633 AND AEI.aei_information7 = to_char('||l_legal_entity_org||')';
634
635 if (l_person_id is not null) then
636 sql_range := sql_range ||' AND paf.person_id = '||l_person_id;
637 end if;
638
639 if (l_asg_set_id is not null) then
640 sql_range := sql_range ||
641 ' AND exists (select 1
642 from hr_assignment_sets has1
643 where has1.assignment_set_id = '||l_asg_set_id||'
644 and has1.business_group_id = paf.business_group_id
645 and nvl(has1.payroll_id,paf.payroll_id) = paf.payroll_id
646 and (not exists (select 1
647 from hr_assignment_set_amendments hasa1
648 where hasa1.assignment_set_id = has1.assignment_set_id)
649 or exists (select 1
650 from hr_assignment_set_amendments hasa2
651 where hasa2.assignment_set_id = has1.assignment_set_id
652 and hasa2.assignment_id = paf.assignment_id
653 and nvl(hasa2.include_or_exclude,''I'') = ''I'')
654 or (not exists (select 1
655 from hr_assignment_set_amendments hasa3
656 where hasa3.assignment_set_id = has1.assignment_set_id
657 and hasa3.assignment_id = paf.assignment_id
658 and nvl(hasa3.include_or_exclude,''I'') = ''E'')
659 and not exists (select 1
660 from hr_assignment_set_amendments hasa4
661 where hasa4.assignment_set_id = has1.assignment_set_id
662 and nvl(hasa4.include_or_exclude,''I'') = ''I'')
663 )
664 )
665 )';
666 end if;
667
668 sql_range := sql_range|| ' ORDER BY paf.person_id';
669
670 fnd_file.put_line(fnd_file.log,'sql_range:' ||sql_range);
671
672 sqlstr := sql_range;
673 hr_utility.set_location('Leaving '||l_proc,999);
674 end ;
675
676
677
678
679 /*--------------------------------------------------------------------------
680 Name : archinit
681 Purpose : This procedure can be used to perform an initialisation
682 section
683 Arguments :
684 Notes : Call set_code_tables to initialize global pl/sql tables
685 --------------------------------------------------------------------------*/
686 procedure archinit(p_payroll_action_id in number) as
687
688 l_tax_year varchar2(10); -- Bug 13367825
689
690 begin
691 set_code_tables;
692 -- Bug 13367825 starts
693
694 select get_parameter('TAX_YEAR', legislative_parameters)
695 into l_tax_year
696 from pay_payroll_actions
697 where payroll_action_id = p_payroll_action_id;
698
699 if l_tax_year < 2013 then
700 if g_code_list.exists(3615) then
701 g_code_list.delete(3615);
702 end if;
703 end if;
704 -- Bug 13367825 ends
705
706 end ;
707
708
709
710
711 /*--------------------------------------------------------------------------
712 Name : action_creation
713 Purpose : This creates the assignment actions for a specific chunk.
714 Arguments :
715 Notes :
719 endperson in number,
716 --------------------------------------------------------------------------*/
717 procedure action_creation(pactid in number,
718 stperson in number,
720 chunk in number) as
721 -- This cursor returns all assignments for which processing took place
722 -- in the Tax Year.
723 -- Note: This cursor does not date effectively join to per_assignments_f.
724 -- Duplicate assignments are, however, removed in the cursor loop.
725 /*
726 "The cursor looks for assignments that are assigned AT TAX YEAR END to
727 "specific legal entity that was given in the TYE Archiver SRS -
728 "BUT, this means it will find the All Assignments it was on during
729 "the Tax year (and for which processing took place), whenever the TYE Archiver SRS
730 "is run for each of those legal entities.
731 */
732
733 cursor get_asg( p_legal_entity hr_all_organization_units.organization_id%TYPE
734 , p_payroll_id pay_all_payrolls_f.payroll_id%TYPE
735 , p_tax_year varchar2
736 , p_itreg_batch varchar2
737 , p_period_recon varchar2
738 , p_period_recon_last_date date) is
739 SELECT /*+ INDEX(asg PER_ASSIGNMENTS_F_N12) */
740 /* we used the above hint to always ensure that the use the person_id
741 index on per_assignments_f, otherwise, it is feasible the CBO may decide to
742 choose the N7 (payroll_id) index due to it being a bind */
743 asg.person_id person_id
744 , asg.assignment_id assignment_id
745 FROM
746 per_all_assignments_f asg
747 , pay_payroll_actions ppa_arch
748 , per_assignment_extra_info aei
749 WHERE
750 ppa_arch.payroll_action_id = pactid
751 AND asg.business_group_id + 0 = ppa_arch.business_group_id
752 AND asg.person_id BETWEEN stperson AND endperson
753 AND aei.assignment_id = asg.assignment_id
754 AND aei.aei_information7 = to_char(p_legal_entity)
755 AND asg.payroll_id is not null
756 AND asg.payroll_id = nvl(p_payroll_id,asg.payroll_id)
757 AND
758 ( ppa_arch.effective_date BETWEEN asg.effective_start_date
759 AND asg.effective_end_date
760 OR
761 ( asg.effective_end_date <= ppa_arch.effective_date
762 AND asg.effective_end_date =
763 ( SELECT MAX(asg2.effective_end_date)
764 FROM per_all_assignments_f asg2
765 WHERE asg2.assignment_id = asg.assignment_id
766 )
767 )
768 )
769 -- for ITREG batch, pick up only persons with Nature A/B/C/N
770 AND (p_itreg_batch = 'N'
771 or
772 (p_itreg_batch = 'Y' and aei.aei_information4 in ('01','02','03','11'))
773 )
774 AND EXISTS (SELECT /*+ ORDERED */
775 /* the ordered hint will force the paa table to be joined to first */
776 NULL
777 FROM pay_assignment_actions paa
778 , pay_payroll_actions ppa
779 , per_time_periods ptp
780 WHERE paa.assignment_id = asg.assignment_id
781 AND paa.payroll_action_id = ppa.payroll_action_id
782 AND ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
783 AND ptp.time_period_id = ppa.time_period_id
784 AND ptp.prd_information1 = p_tax_year
785 AND ptp.end_date <= decode(p_period_recon, '02', ptp.end_date, p_period_recon_last_date)
786 AND paa.action_status IN ('C','S') --10376999
787 AND ppa.action_status = 'C'
788 )
789 order by 1, 2;
790
791 asg_set_id number;
792 person_id number;
793 l_payroll_id number;
794 asg_include boolean;
795 lockingactid number;
796 v_incl_sw char;
797 prev_asg_id number := 0;
798 l_legal_entity_org number;
799 l_tax_year varchar2(4);
800 l_ppa_payroll_id number;
801 l_proc varchar2(100) := g_package||'action_creation';
802 leg_param pay_payroll_actions.legislative_parameters%type;
803 l_cert_type varchar2(1);
804 l_itreg_batch varchar2(1);
805 l_period_recon varchar2(30);
806 l_period_recon_last_date date;
807 BEGIN
808
809 -- hr_utility.trace_on(null,'ZATRC');
810 hr_utility.set_location('Entering '||l_proc, 10);
811 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
812
813 -- Get the legislative parameters from the archiver payroll action
814 select legislative_parameters,payroll_id
815 into leg_param,l_ppa_payroll_id
816 from pay_payroll_actions
817 where payroll_action_id = pactid;
818
819 asg_set_id := get_parameter('ASG_SET_ID', leg_param);
820 person_id := get_parameter('PERSON_ID', leg_param);
821 l_legal_entity_org := get_parameter('LEGAL_ENTITY', leg_param);
822 l_payroll_id := get_parameter('PAYROLL_ID', leg_param);
823 l_tax_year := get_parameter('TAX_YEAR', leg_param);
824 l_cert_type := get_parameter('CERT_TYPE', leg_param);
825
826 l_period_recon := NVL(get_parameter('PERIOD_RECON',leg_param),'02');
827
828 select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
829 into l_period_recon_last_date
830 from dual;
831
832 hr_utility.set_location(l_proc,10);
833
834 if l_cert_type = '1' then
838 end if;
835 l_itreg_batch := 'N';
836 else
837 l_itreg_batch := 'Y';
839
840 -- Update the Payroll Action with the Payroll ID
841 --
842 IF l_ppa_payroll_id IS NULL and l_payroll_id is not null THEN
843 update pay_payroll_actions
844 set payroll_id = l_payroll_id
845 where payroll_action_id = pactid;
846 END IF;
847
848 if asg_set_id is not null then
849 begin
850 select distinct include_or_exclude
851 into v_incl_sw
852 from hr_assignment_set_amendments
853 where assignment_set_id = asg_set_id;
854 exception
855 when no_data_found then
856 v_incl_sw := 'I';
857 end;
858 end if;
859
860 hr_utility.set_location(l_proc,20);
861
862 for asgrec in get_asg(l_legal_entity_org,l_payroll_id,l_tax_year,l_itreg_batch,l_period_recon,l_period_recon_last_date) loop
863 hr_utility.set_location('Assignment_id : ' || to_char(asgrec.assignment_id), 20);
864 asg_include := TRUE;
865
866 -- Remove duplicate assignments
867 if prev_asg_id <> asgrec.assignment_id then -- revisit -- check if required ?
868
869 prev_asg_id := asgrec.assignment_id;
870
871 if asg_set_id is not null then
872
873 declare
874 inc_flag varchar2(5);
875 begin
876 select include_or_exclude
877 into inc_flag
878 from hr_assignment_set_amendments
879 where assignment_set_id = asg_set_id
880 and assignment_id = asgrec.assignment_id;
881
882 if inc_flag = 'E' then
883 asg_include := FALSE;
884 end if;
885 exception
886 when no_data_found then
887 if v_incl_sw = 'I' then
888 asg_include := FALSE;
889 else
890 asg_include := TRUE;
891 end if;
892 end ;
893
894 end if;
895
896 if person_id is not null then
897 if person_id <> asgrec.person_id then
898 asg_include := FALSE;
899 end if;
900 end if;
901
902 /* Earlier we were creating separate assignment actions for every directive number
903 attached to the assignment.
904 But now we are creating only one assignment action per assignment
905 */
906 if asg_include = TRUE then
907 -- Create one assignment action for every assignment
908 hr_utility.set_location('Archiving for assignment_id '||asgrec.assignment_id, 50);
909 select pay_assignment_actions_s.nextval
910 into lockingactid
911 from dual;
912
913 -- Insert assignment into pay_assignment_actions
914 hr_nonrun_asact.insact
915 (
916 lockingactid,
917 asgrec.assignment_id,
918 pactid,
919 chunk,
920 null
921 );
922 end if;
923
924 end if;
925 end loop;
926
927 hr_utility.set_location('Leaving '||l_proc, 999);
928 -- hr_utility.trace_off;
929 end action_creation;
930
931 -------------------------------------------------------------------
932 -- return the no of main certificate income codes . this is required
933 -- as we can have maximum of 13 income codes in a certificate
934 -------------------------------------------------------------------
935
936 function get_main_inc_code_cnt(t_code in code_table,
937 t_inc_code in out nocopy code_table ) return number
938 as
939 l_count number := 0;
940 l_key varchar2(100);
941 l_proc varchar2(100) := g_package||'get_main_inc_code_cnt';
942 begin
943 hr_utility.set_location('Entering '||l_proc, 10);
944
945 l_key := t_code.first;
946 while l_key is not null
947 loop
948 IF (length(l_key) = 4 AND
949 l_key not in ('3604','3607','3609','3612','3705','3706','3709','3710','3711','3712','3716','3803','3804','3807') ) THEN
950 IF (g_code_list.exists(l_key)) THEN
951 IF (g_code_list(l_key).code_type ='INCOME' AND
952 t_code(l_key).group_value <> 0) THEN
953 t_inc_code(l_key).value := 0;
954 END IF;
955 END IF;
956 END IF;
957 l_key := t_code.next(l_key);
958 end loop;
959
960 l_count := t_inc_code.count();
961 hr_utility.set_location('Main Inc Code Count:'||l_count, 20);
962 hr_utility.set_location('Leaving '||l_proc, 100);
963 return l_count;
964
965 end get_main_inc_code_cnt;
966
967 -------------------------------------------------------------------
968 -- given the lump sum dir this will return the no of income codes
969 -- for that lump sum dir
970 -------------------------------------------------------------------
971 function get_lump_inc_code_cnt(t_code in code_table,
972 dir_num in varchar2,
973 t_inc_code in out nocopy code_table) return number
974 as
975 l_count number := 0;
976 l_key varchar2(100);
977 l_proc varchar2(100) := g_package||'get_lump_inc_code_cnt';
978 begin
979 hr_utility.set_location('Entering '||l_proc, 10);
980
981 l_key := t_code.first;
982 while l_key is not null
983 loop
984
985 IF ( (length(l_key) > 4) AND
986 (substr(l_key,1,4) not in ('4102','4115','3697','3698','4116','4142')) AND
987 (l_key = substr(l_key,1,4)||'-'||dir_num ) AND
988 (nvl(t_code(l_key).group_value,0) <> 0)
989 ) THEN
990 t_inc_code(substr(l_key,1,4)).value := 0;
991 END IF;
992 l_key := t_code.next(l_key);
993 end loop;
994
995 l_count := t_inc_code.count();
996 hr_utility.set_location('Lumpsum Dir Inc Count:'||dir_num||':'||l_count, 20);
997 hr_utility.set_location('Leaving '||l_proc, 100);
998 return l_count;
999
1000 end get_lump_inc_code_cnt;
1001
1002
1003 /*--------------------------------------------------------------------------
1004 Name : archive_data
1005 Purpose : This sets up the contexts needed for the live (non-archive)
1006 database items
1007 Arguments :
1008 Notes : Every possible context for a specific assignment action has to
1009 be added to the PL/SQL table
1010
1011 PL/SQL Tables used -
1012 1) t_code - contains values of all codes for this assignment
1013 - index by code(for normal codes) or code-dirnum (for lumpsums)
1014 - Attributes : a) Value
1015 2) t_dir_num - all directive numbers for this assignment,
1016 - index by directive_number
1017 - Attributes : a) certificate_type - IRP5/IT3A/ITREG
1018 b) clubbed with main certificate flag - Y (for dirnums which are clubbed with main certificate)
1019 - N (for dirnums which have separate certificates)
1020
1021 Code flow -
1022 1) Set PL/SQL tables, Fetch ZA_Tax Element Details for last payroll_run for this assignment
1023 2) Fetch employee's basic data
1024 3) Populate t_dir_num with all directive numbers for this assignment in this tax year including ('To Be Advised')
1025 4) Fetch_balances() - Populate t_code with values for income/lumpsum/deduction/gross codes for this assignment
1026 5) Populate_irp5_indicators() - Identify type(IRP5/IT3A/ITREG) of main certificate and lumpsum certificates
1027 6) Combine_certificates() - Identify which lumpsum certificates can be merged with main certificate
1028 7) Consolidate_codes() - Consolidate codes for
1029 a) Codes which are to be incorporated into other codes as per SARS
1030 b) All codes of lumpsum certificates are to be merged with main certificate
1031 c) For codes 3907, 3697, and 3698
1032 Merge t_code(code-To Be Advised) into t_code(code) to avoid duplicate reporting
1033 8) Populate employee's Main Certificate information into l_archive_tab
1034 9) Populate employee's Lumpsum Certificate information into l_archive_tab
1035 10) Call archive API to archive data from l_archive_tab
1036
1037 --------------------------------------------------------------------------*/
1038 procedure archive_data (p_assactid in number,
1039 p_effective_date in date) as
1040
1041 cursor curdirnum (p_ass_id in number, p_tax_year varchar2, p_period_recon_last_date date, p_period_recon varchar2) is
1042 select distinct prrv.result_value directive_number
1043 from pay_payroll_actions ppa
1044 , per_time_periods ptp
1045 , pay_assignment_actions paa
1046 , pay_run_results prr
1047 , pay_run_result_values prrv
1048 , pay_element_types_f peef
1049 , pay_input_values_f piv
1050 WHERE ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1051 and ppa.action_status = 'C'
1052 and ppa.time_period_id = ptp.time_period_id
1053 and ptp.prd_information1 = p_tax_year
1054 and ptp.end_date <= decode(p_period_recon, '02',ptp.end_date , p_period_recon_last_date) -- 9877034 fix
1055 and paa.payroll_action_id = ppa.payroll_action_id
1056 and paa.action_status = 'C'
1057 and paa.assignment_id = p_ass_id
1058 and prr.assignment_action_id = paa.assignment_action_id
1059 and prrv.run_result_id = prr.run_result_id
1060 and peef.element_type_id = prr.element_type_id
1061 and piv.input_value_id = prrv.input_value_id
1062 and piv.name = 'Tax Directive Number'
1063 and peef.element_name <> 'ZA_Tax'
1064 and ppa.effective_date between peef.effective_start_date and peef.effective_end_date
1065 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
1066
1067 cursor csr_tax_status (p_run_assact_id number, p_input_value_name varchar2)is
1068 select max(peevf.screen_entry_value)
1069 from pay_element_entry_values_f peevf,
1070 pay_element_entries_f peef,
1074 pay_payroll_actions ppa,
1071 pay_link_input_values_f plivf,
1072 pay_input_values_f pivf,
1073 pay_element_types_f petf,
1075 pay_assignment_actions paa
1076 where paa.assignment_action_id = p_run_assact_id
1077 and ppa.payroll_action_id = paa.payroll_action_id
1078 and petf.element_name = 'ZA_Tax'
1079 and petf.legislation_code = 'ZA'
1080 and petf.business_group_id is null
1081 and ppa.effective_date between petf.effective_start_date
1082 and petf.effective_end_date
1083 and pivf.element_type_id = petf.element_type_id
1084 and pivf.name = p_input_value_name
1085 and ppa.effective_date between pivf.effective_start_date
1086 and pivf.effective_end_date
1087 and plivf.input_value_id = pivf.input_value_id
1088 and ppa.effective_date between plivf.effective_start_date
1089 and plivf.effective_end_date
1090 and peef.element_link_id = plivf.element_link_id
1091 and peef.assignment_id = paa.assignment_id
1092 and peevf.element_entry_id = peef.element_entry_id
1093 and peef.effective_start_date =
1094 (
1095 select max(peef2.effective_start_date)
1096 from pay_element_entries_f peef2
1097 where peef2.effective_start_date <= ppa.effective_date
1098 and peef2.element_link_id = plivf.element_link_id
1099 and peef2.assignment_id = paa.assignment_id
1100 )
1101 and peevf.input_value_id = pivf.input_value_id
1102 and peevf.effective_start_date = peef.effective_start_date
1103 and peevf.effective_end_date = peef.effective_end_date;
1104
1105 -- 2013 TAX YEAR CHANGES.
1106
1107 cursor csr_get_glb_value is
1108 select global_value
1109 from ff_globals_f
1110 where legislation_code = 'ZA'
1111 and global_name = 'ZA_COMP_DEATH_EMP_AMT'
1112 and p_effective_date between effective_start_date and effective_end_date;
1113
1114 l_global_value ff_globals_f.global_value%type;
1115
1116 l_3922_non_taxable number := 0;
1117 l_3922_rfi number := 0;
1118 l_3922_nrfi number := 0;
1119 l_3922 number := 0;
1120
1121 l_3922_rep_err varchar2(1) := 'X';
1122
1123 -- 2013 TAX YEAR CHANGES.
1124
1125 l_proc varchar2(100) := g_package||'archive_data';
1126 l_tax_year varchar2(4);
1127 l_run_action_seq number;
1128 l_run_assact_id number;
1129 l_assignment_id number;
1130 l_pact_id number;
1131 l_main_cert_type varchar2(4);
1132 l_main_cert_dir_num varchar2(100);
1133 l_directive_1 varchar2(100);
1134 l_directive_2 varchar2(100);
1135 l_directive_3 varchar2(100);
1136 l_rec_count number := 0;
1137 l_code_final varchar2(2);
1138 l_code_complete varchar2(100);
1139 l_tax_status varchar2(100);
1140 l_foreign_income varchar2(1);
1141 l_person_id number;
1142 l_dir_num varchar2(100);
1143 l_cert_count number:=1 ; -- to generate temporary certificate numbers
1144 l_main_cert_dir_val varchar2(100);
1145 l_nature varchar2(2);
1146 l_independent_contractor varchar2(1);
1147 l_labour_broker varchar2(1);
1148 l_reason_for_IT3A varchar2(2);
1149 l_leg_param pay_payroll_actions.legislative_parameters%type;
1150 t_final_arch final_archive_table;
1151 t_code code_table; -- values of all codes for this assignment, index by code / code-dirnum
1152 t_dir_num dir_num_table; -- all directive numbers for this assignment,
1153 -- index by directive_number
1154 -- if combine_cert= 'Y' then set of 3 dir per cert else single dir per cert
1155 t_lump_dir_set lump_dir_set_tab;
1156
1157 l_combine_cert varchar2(30):='N';
1158
1159 l_temp_cert_num varchar2(30);
1160 l_row_count number := 0;
1161 l_code varchar2(4);
1162 l_archive_tab action_info_table;
1163 l_inc1_rec number;
1164 l_inc2_rec number;
1165 l_ded_rec number;
1166 l_inc_count number := 2; -- start from action_information2
1167 l_ded_count number := 2; -- start from action_information2
1168 l_rec number;
1169 l_field number;
1170 l_cert_type_param varchar2(1);
1171 l_itreg_batch varchar2(1);
1172 l_lumpsum_effective_date date;
1173 l_code_arch varchar2(4);
1174
1175 l_4101 varchar2(100);
1176 l_4102 varchar2(100);
1177 l_4103 varchar2(100);
1178 l_4115 varchar2(100);
1179 l_4141 varchar2(100);
1180 l_4142 varchar2(100);
1181 l_4149 varchar2(100);
1182 l_4150 varchar2(100);
1183 l_period_recon varchar2(30); -- 9877034 fix
1184 l_asg_end_date date; -- 9877034 fix
1185 l_terminated_bef_sep varchar2(1); -- 9877034 fix
1186 l_period_recon_last_date date; -- 9877034 fix
1187 l_4116 varchar2(100);
1188 l_65Years_age varchar2(100);
1189
1190
1191
1192 l_lump_sum_inc_cnt number;
1193 l_lump_dir_cnt number;
1194 l_first_flag boolean;
1195
1196 l_lump_dir_set number;
1197 l_lump_value number;
1198 l_lump_grp_value number;
1199 l_lump_3697_val number;
1200 l_lump_3698_val number;
1201 l_lump_3696_val number;
1202
1203 l_lump_3915_value number;
1204 l_lump_3920_value number;
1205 l_lump_3921_value number;
1206 l_lump_3922_value number;
1207
1208 l_dir_set_count number;
1209
1210 l_valid_dir_flag boolean default false;
1211 l_4005 varchar2(100);
1212
1213 begin
1214 null;
1215 -- hr_utility.trace_on(null,'ZATRC');
1216 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
1217 hr_utility.set_location('Entering '||l_proc,1);
1218 hr_utility.set_location('p_assactid : '||p_assactid,1);
1219 hr_utility.set_location('p_effective_date : '||p_effective_date,1);
1220 ------------------------------------------------------------------------
1221 -- 1. Set PL/SQL Tables
1222 -- Fetch ZA_Tax Element Details for last payroll_run for this assignment
1223 ------------------------------------------------------------------------
1224
1225 -- set pl/sql tables is not already set
1226 if g_code_list.count = 0 then
1227 set_code_tables;
1228 end if ;
1229
1230 select ppa.legislative_parameters
1231 , paa.assignment_id
1232 into l_leg_param
1233 , l_assignment_id
1234 from pay_payroll_actions ppa
1235 , pay_assignment_actions paa
1236 where paa.assignment_action_id = p_assactid
1237 and ppa.payroll_action_id = paa.payroll_action_id;
1238
1239 select max(paaf.person_id)
1240 into l_person_id
1241 from per_all_assignments_f paaf
1242 where paaf.assignment_id = l_assignment_id;
1243
1244 l_combine_cert := get_parameter('P_COMBINE_CERT', l_leg_param);
1245 hr_utility.set_location('P_COMBINE_CERT:'||l_combine_cert,1);
1246
1247 l_tax_year := get_parameter('TAX_YEAR', l_leg_param);
1248 -- Bug 13367825
1249 if l_tax_year < 2013 then
1250 if g_code_list.exists(3615) then
1251 g_code_list.delete(3615);
1252 end if;
1253 end if;
1254 -- Bug 13367825
1255 l_cert_type_param := get_parameter('CERT_TYPE', l_leg_param);
1256 g_tax_year := l_tax_year;
1257
1258 -- 9877034 starts
1259 l_period_recon := NVL(get_parameter('PERIOD_RECON',l_leg_param),'02');
1260
1261 select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
1262 into l_period_recon_last_date
1263 from dual;
1264
1265 if l_period_recon = '08' then
1266
1267 --Check if the assignment is terminated or ended
1268 SELECT max (paaf.effective_start_date)
1269 INTO l_asg_end_date
1270 FROM per_assignment_status_types past
1271 ,per_all_assignments_f paaf
1272 WHERE paaf.assignment_id = l_assignment_id
1273 AND paaf.assignment_status_type_id = past.assignment_status_type_id
1274 AND past.per_system_status IN ('TERM_ASSIGN','END');
1275
1276 IF (l_asg_end_date is null) THEN
1277 -- Find the Actual Termination date of the employee
1278
1279 -- fix for bug 12992314 starts
1280
1281 -- SELECT max (actual_termination_date)
1282 SELECT max (nvl(actual_termination_date,TO_DATE('31/12/4712','DD/MM/YYYY')))
1283
1284 -- fix for bug 12992314 ends
1285 INTO l_asg_end_date
1286 FROM per_periods_of_service
1287 WHERE person_id = l_person_id
1288
1289 -- fix for bug 12992314 starts
1290
1291 AND DATE_START <= last_day(to_date('01-02-'||l_tax_year,'DD-MM-YYYY'));
1292 -- AND actual_termination_date IS NOT NULL;
1293
1294 -- fix for bug 12992314 ends
1295
1296 END IF;
1297
1298 if l_asg_end_date <= l_period_recon_last_date then
1299 l_terminated_bef_sep := 'Y';
1300 end if;
1301
1302 end if;
1303 -- 9877034 ends
1304
1305 hr_utility.trace('Certificate type param: '|| l_cert_type_param);
1306
1307 if l_cert_type_param = 1 then
1308 -- This is a normal (IRP5/IT3A) archive process
1309 l_itreg_batch := 'N';
1310 else
1311 -- This is an ITREG batch process
1312 l_itreg_batch := 'Y';
1313 end if;
1314
1315 -- Fetch assignment_action_id for last payroll run for that assignment
1316 -- in this tax year
1317 select max(paa.action_sequence)
1318 into l_run_action_seq
1319 from pay_assignment_actions paa,
1320 pay_payroll_actions ppa,
1321 per_time_periods ptp
1322 where paa.assignment_id = l_assignment_id
1323 and paa.action_status IN ('C','S') --10376999
1324 and paa.payroll_action_id = ppa.payroll_action_id
1325 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
1326 and ppa.action_status = 'C'
1327 and ppa.time_period_id = ptp.time_period_id
1328 and ptp.prd_information1 = l_tax_year
1332 into l_run_assact_id
1329 and ptp.end_date <= decode(l_period_recon, '02', ptp.end_date, l_period_recon_last_date); -- 9877034 fix
1330
1331 select assignment_action_id
1333 from pay_assignment_actions
1334 where assignment_id = l_assignment_id
1335 and action_sequence = l_run_action_seq;
1336
1337 hr_utility.trace('Last payroll run assignment_action_id : '|| l_run_assact_id);
1338
1339 hr_utility.set_location(l_proc,10);
1340 open csr_tax_status (l_run_assact_id, 'Tax Status');
1341 fetch csr_tax_status into l_tax_status;
1342 close csr_tax_status;
1343
1344 open csr_tax_status (l_run_assact_id, 'Tax Directive Number');
1345 fetch csr_tax_status into l_main_cert_dir_num;
1346 close csr_tax_status;
1347
1348 open csr_tax_status (l_run_assact_id, 'Tax Directive Value');
1349 fetch csr_tax_status into l_main_cert_dir_val;
1350 close csr_tax_status;
1351
1352 l_tax_status := nvl(l_tax_status,'A');
1353
1354 hr_utility.set_location(l_proc,20);
1355
1356
1357 ------------------------------------------------------------------------
1358 -- 2. Fetch employee's basic data
1359 ------------------------------------------------------------------------
1360 fetch_person_data( p_assactid
1361 , p_effective_date
1362 , l_itreg_batch
1363 , l_tax_status
1364 -- Out parameters
1365 , l_archive_tab(0)
1366 , l_archive_tab(1)
1367 , l_assignment_id
1368 , l_person_id
1369 , l_foreign_income
1370 , l_pact_id
1371 , l_nature
1372 , l_independent_contractor
1373 , l_labour_broker
1374 , l_lumpsum_effective_date);
1375
1376 hr_utility.set_location(l_proc,30);
1377
1378 -- Check person is below/above the 65 Years of age
1379 select decode (sign (add_months (to_date (l_archive_tab(0).act_info11, 'YYYYMMDD'), 780)
1380 - to_date (l_archive_tab(0).act_info4 || '-03-01', 'yyyy-mm-dd')-1), 1
1381 , 'B', 'A')
1382 into l_65Years_age
1383 from dual;
1384 hr_utility.set_location('l_65Years_age:'||l_65Years_age,30);
1385
1386 ------------------------------------------------------------------------
1387 -- 3. Populate t_dir_num with all directive numbers for this assignment in this tax year
1388 ------------------------------------------------------------------------
1389 if l_itreg_batch = 'N' then
1390 for dirnum in curdirnum(l_assignment_id, l_tax_year, l_period_recon_last_date, l_period_recon) -- 9877034 fix
1391 loop
1392 hr_utility.trace('t_dir_num('||dirnum.directive_number||')');
1393 t_dir_num(dirnum.directive_number).certificate_type := null;
1394 t_dir_num(dirnum.directive_number).certificate_merged_with_main := null;
1395 end loop;
1396 end if;
1397
1398 hr_utility.set_location(l_proc,40);
1399
1400 ------------------------------------------------------------------------
1401 -- 4. Populate t_code with values for income/lumpsum/deduction/gross codes for this assignment
1402 ------------------------------------------------------------------------
1403 if l_itreg_batch = 'N' then
1404 fetch_balances ( l_run_assact_id
1405 , t_dir_num
1406 , l_period_recon
1407 , t_code);
1408 end if;
1409
1410 hr_utility.set_location(l_proc,50);
1411
1412 ------------------------------------------------------------------------
1413 -- 5. Identify type(IRP5/IT3A/ITREG) of main certificate and lumpsum certificates
1414 ------------------------------------------------------------------------
1415 if l_itreg_batch = 'N' then
1416 populate_irp5_indicators(l_run_assact_id
1417 , t_code
1418 , l_main_cert_type
1419 , t_dir_num);
1420 end if;
1421
1422 hr_utility.set_location(l_proc,60);
1423
1424 ------------------------------------------------------------------------
1425 -- 6. Identify which lumpsum certificates can be merged with main certificate
1426 ------------------------------------------------------------------------
1427 -- Bug 9499475 - Removing the functionality of combining lumpsum certificates
1428 -- with the main certificate.
1429 -- This is done because we are introducing the functionality of Aug's certificate
1430 -- number to be re-used in Feb, if the Aug's certificate number is not reused
1431 -- then for SARS, Feb's certificate would mean an additional information, else
1432 -- it would mean replacement of Aug's information with Feb's information
1433 --
1434 -- If we keep allowing for certificate combination, then the lumpsum cert
1435 -- combining with main cert might be different in Aug and Feb, but generated
1436 -- under same certificate number. Then should we reuse the Aug's cert num, OR
1437 -- generate a new cert num. Both would be wrong.
1438 --
1439 -- Hence the functionality of certificate number combination has been removed
1440 --
1441 if l_itreg_batch = 'N' then
1442 if t_dir_num.exists('To Be Advised') then
1443 t_dir_num('To Be Advised').certificate_merged_with_main := 'Y';
1444 end if;
1445 -- Just Merge Main certificate income codes
1446 -- This is required as we need to merge the code before counting the main certificate income code count
1447 consolidate_codes(t_dir_num
1448 , t_code
1449 , true);
1450
1451 if (l_combine_cert ='Y' ) then
1452 combine_certificates( l_main_cert_type
1453 , l_main_cert_dir_num
1454 , t_code
1455 , t_dir_num
1456 , t_lump_dir_set
1457 , l_directive_1
1458 , l_directive_2
1459 , l_directive_3);
1460 else
1461 -- Populate t_lump_dir_set with single lumpsun dir per record (i.e per certificate)
1462 l_dir_set_count :=1;
1463 l_dir_num := t_dir_num.first;
1464 while l_dir_num is not null
1465 loop
1466 if l_dir_num <> 'To Be Advised' then
1467 t_lump_dir_set(l_dir_set_count).dir_1:=l_dir_num;
1468 l_dir_set_count := l_dir_set_count +1;
1469 end if;
1470 l_dir_num := t_dir_num.next(l_dir_num);
1471 end loop;
1472 end if;
1473 end if;
1474
1475 hr_utility.set_location(l_proc,70);
1476
1477 ------------------------------------------------------------------------
1478 -- 7. Consolidate codes for
1479 -- 1) Codes which are to be incorporated into other codes as per SARS
1480 -- 2) All codes of lumpsum certificates are to be merged with main certificate
1481 -- 3) For codes 3907, 3697, 3698, and 4102
1482 -- Merge t_code(code-To Be Advised) into t_code(code) to avoid duplicate reporting
1483 ------------------------------------------------------------------------
1484 if l_itreg_batch = 'N' then
1485 consolidate_codes(t_dir_num
1486 , t_code
1487 , false);
1488 end if;
1489
1490 hr_utility.set_location(l_proc,80);
1491
1492 ------------------------------------------------------------------------
1493 -- 8. Populate employee's Main Certificate information into archive_tab
1494 ------------------------------------------------------------------------
1495 l_temp_cert_num := lpad(p_assactid,25,'0')||'-'||lpad(l_cert_count,4,'0');
1496
1497 -- Update employee's basic information
1498 if l_itreg_batch = 'N' then
1499 if l_main_cert_type = 'IRP5' then
1500 l_archive_tab(0).act_info2 := 'IRP5'; -- Main Certificate Type (IRP5/IT3A/ITREG/A)
1501 elsif l_main_cert_type = 'IT3A' then
1502 l_archive_tab(0).act_info2 := 'IT3(a)'; -- Main Certificate Type (IRP5/IT3A/ITREG/A)
1503 elsif l_main_cert_type = 'A' then
1504 l_archive_tab(0).act_info2 := 'A';
1505 end if;
1506 else
1507 l_archive_tab(0).act_info2 := 'ITREG';
1508 end if;
1509
1510 l_archive_tab(0).act_info18 := l_directive_1; -- Directive1
1511 l_archive_tab(0).act_info19 := l_directive_2; -- Directive2
1512 l_archive_tab(0).act_info20 := l_directive_3; -- Directive3
1513 l_archive_tab(0).act_info30 := l_temp_cert_num; -- Temporary certificate Number
1514
1515 l_archive_tab(1).act_info26 := 'MAIN'; -- employee's main certificate
1516 l_archive_tab(1).act_info30 := l_temp_cert_num; -- Temporary certificate Number
1517 l_rec_count := 1;
1518
1519 hr_utility.set_location(l_proc,90);
1520
1521 -- Archive Income/Deduction codes data only if this is not an itreg batch
1522 if l_itreg_batch = 'N' then
1523 -- Employee's main certificate income/deduction information - all codes
1524 hr_utility.set_location(l_proc,100);
1525 l_code_complete := t_code.first;
1526 loop
1527 l_code := substr(l_code_complete,1,4);
1528
1529 hr_utility.set_location('Code : '|| l_code_complete||' Value : '||trunc(t_code(l_code_complete).group_value),110);
1530
1531 if length(l_code_complete)>5 and l_code not in ('4102','4115','3696','3697','3698','4116')then
1532 -- For 3907, t_code(3907-dirnum) will be archived under Lumpsums
1533 -- Lumpsum code
1534 l_dir_num := substr(l_code_complete,6);
1535 if l_dir_num = 'To Be Advised' then
1536 -- Archive only To Be Advised record for lumpsums
1537 -- separate directive_num values will be archived in same record
1538
1539 hr_utility.set_location(l_proc,120);
1540
1541 l_rec_count := l_rec_count + 1;
1542 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1543 l_archive_tab(l_rec_count).person_id := l_person_id;
1544 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_LUMPSUMS';
1545 l_archive_tab(l_rec_count).act_info2 := final_code(l_tax_year, l_code_complete,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1546 l_archive_tab(l_rec_count).act_info3 := final_code(l_tax_year, t_code(l_code_complete).included_in,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1547 l_archive_tab(l_rec_count).act_info4 := t_code(l_code_complete).value;
1548 l_archive_tab(l_rec_count).act_info5 := trunc(t_code(l_code_complete).group_value);
1549 l_archive_tab(l_rec_count).act_info6 := l_main_cert_dir_num; -- from ZA_Tax element
1550 if l_directive_1 is not null then
1551 l_archive_tab(l_rec_count).act_info7 := l_directive_1;
1552 if l_directive_1 = l_main_cert_dir_num then
1553 l_archive_tab(l_rec_count).act_info8 := null;
1554 else
1555 l_archive_tab(l_rec_count).act_info8 := t_code(l_code||'-'||l_directive_1).group_value;
1556 end if;
1557 end if;
1558 if l_directive_2 is not null then
1559 l_archive_tab(l_rec_count).act_info9 := l_directive_2;
1560 l_archive_tab(l_rec_count).act_info10 := t_code(l_code||'-'||l_directive_2).group_value;
1561 end if;
1562
1563 if l_directive_3 is not null then
1564 l_archive_tab(l_rec_count).act_info11 := l_directive_3;
1565 l_archive_tab(l_rec_count).act_info12 := t_code(l_code||'-'||l_directive_3).group_value;
1566 end if;
1567 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1568 end if;
1569 else
1570 -- Not Lumpsum
1571
1572 l_code := substr(l_code_complete,1,4);
1573 if g_code_list.exists(l_code) then
1574 if g_code_list(l_code).code_type = 'INCOME' then
1575
1576 hr_utility.set_location(l_proc,130);
1577
1578 -- Income Code
1579 -- For 3907, t_code(3907) will be archived as Normal Income
1580 l_rec_count := l_rec_count + 1;
1581 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1582 l_archive_tab(l_rec_count).person_id := l_person_id;
1583 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_INCOME';
1584 l_archive_tab(l_rec_count).act_info2 := final_code(l_tax_year, l_code,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1585 l_archive_tab(l_rec_count).act_info3 := final_code(l_tax_year, t_code(l_code).included_in,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1586 l_archive_tab(l_rec_count).act_info4 := t_code(l_code).value;
1587 l_archive_tab(l_rec_count).act_info5 := trunc(t_code(l_code).group_value);
1588 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1589 elsif g_code_list(l_code).code_type = 'DEDUCTION' then
1590 -- Deduction code
1591
1592 hr_utility.set_location(l_proc,140);
1593
1594 l_rec_count := l_rec_count + 1;
1595 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1596 l_archive_tab(l_rec_count).person_id := l_person_id;
1597 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_DEDUCTIONS';
1598 l_archive_tab(l_rec_count).act_info2 := l_code_complete;
1599 l_archive_tab(l_rec_count).act_info3 := t_code(l_code_complete).included_in;
1600 l_archive_tab(l_rec_count).act_info4 := t_code(l_code_complete).value;
1601 l_archive_tab(l_rec_count).act_info5 := trunc(t_code(l_code_complete).group_value);
1602 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1603 if (l_code_complete ='4005') then
1604 l_4005 := l_archive_tab(l_rec_count).act_info5;
1605 end if;
1606 else
1607 --ignore this code
1608 hr_utility.set_location(l_proc,150);
1609
1610 end if;
1611 else
1612 -- Can be a Gross code, ignore this code
1613 null;
1614 end if;
1615 end if;
1616
1617 l_code_complete := t_code.next(l_code_complete);
1618 exit when l_code_complete is null;
1619 end loop;
1620
1621 hr_utility.set_location(l_proc,160);
1622
1623 -- Employee's main certificate income/deduction information - final record
1624 t_final_arch.delete;
1625
1626 l_rec_count := l_rec_count + 1;
1627 l_inc1_rec := l_rec_count;
1628 l_archive_tab(l_inc1_rec).assignment_id := l_assignment_id;
1629 l_archive_tab(l_inc1_rec).person_id := l_person_id;
1630 l_archive_tab(l_inc1_rec).action_info_category := 'ZATYE_FINAL_EE_INCOME_1';
1631 l_archive_tab(l_inc1_rec).act_info30 := l_temp_cert_num;
1632
1633 l_rec_count := l_rec_count + 1;
1634 l_inc2_rec := l_rec_count;
1635 l_archive_tab(l_inc2_rec).assignment_id := l_assignment_id;
1636 l_archive_tab(l_inc2_rec).person_id := l_person_id;
1637 l_archive_tab(l_inc2_rec).action_info_category := 'ZATYE_FINAL_EE_INCOME_2';
1638 l_archive_tab(l_inc2_rec).act_info30 := l_temp_cert_num;
1639
1640 l_rec_count := l_rec_count + 1;
1641 l_ded_rec := l_rec_count;
1642 l_archive_tab(l_ded_rec).assignment_id := l_assignment_id;
1643 l_archive_tab(l_ded_rec).person_id := l_person_id;
1644 l_archive_tab(l_ded_rec).action_info_category := 'ZATYE_FINAL_EE_DEDUCTIONS';
1645 l_archive_tab(l_ded_rec).act_info30 := l_temp_cert_num;
1646
1647 l_code_complete := t_code.first;
1648 l_code := substr(l_code_complete,1,4);
1649 l_dir_num := substr(l_code_complete,6);
1650 loop
1651 -- Only those codes which have not already been included
1652 if g_code_list.exists(l_code)
1653 and
1654 t_code(l_code_complete).included_in is null
1655 and
1656 t_code(l_code_complete).group_value <> 0 then
1657 -- Normal Income or 'To Be Advised' Lumpsum
1658 -- (For all lumpsum certificates, which are merged
1659 -- with main cert, their amounts have already been
1660 -- added to 'To Be Advised' group_value)
1661
1662 -- Both t_code(3907) and t_code(3907-To Be Advised) can exist
1663 -- Hence ignore t_code(3907-To Be Advised)
1664 -- and report the combined value (already combined in consolidate_codes) ONCE with t_code(3907)
1665 if ((length(l_code_complete)>5 and l_dir_num = 'To Be Advised' and l_code not in ('4102','4115','3696','3697','3698','4116')) -- Lumpsum
1666 or
1667 (g_code_list(l_code).code_type = 'INCOME' and length(l_code_complete) = 4) -- Income
1668 )
1669 and
1670 l_code_complete <> '3907-To Be Advised' then -- ignore 3907-To Be Advised
1671 l_code_arch := final_code(l_tax_year, l_code_complete,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1672 t_final_arch(l_code_arch).value := trunc(t_code(l_code_complete).group_value);
1673 t_final_arch(l_code_arch).code_type := 'INCOME';
1674 elsif g_code_list(l_code).code_type = 'DEDUCTION' then
1675 -- ZATYE_FINAL_EE_DEDUCTIONS
1676 l_code_arch := l_code;
1677 t_final_arch(l_code_arch).value := trunc(t_code(l_code).group_value);
1678 t_final_arch(l_code_arch).code_type := 'DEDUCTION';
1679 else
1680 null ;
1681 -- ignore this code
1682 end if;
1683 end if;
1684
1685 -- fetch next code
1686 l_code_complete := t_code.next(l_code_complete);
1687 l_code := substr(l_code_complete,1,4);
1688 l_dir_num := substr(l_code_complete,6);
1689 exit when l_code is null;
1690 end loop;
1691
1692 hr_utility.set_location(l_proc,170);
1693
1694 l_code := t_final_arch.first;
1695 while l_code is not null
1696 loop
1697 -- identify the record and field to be updated
1698 if t_final_arch(l_code).code_type = 'INCOME' then
1699 if l_inc_count <=26 then
1700 -- ZATYE_FINAL_EE_INCOME_1
1701 l_rec := l_inc1_rec;
1702 l_field := l_inc_count;
1703 l_inc_count := l_inc_count + 2 ;
1704 else
1705 -- ZATYE_FINAL_EE_INCOME_2
1706 l_rec := l_inc2_rec;
1707 l_field := mod(l_inc_count,28)+2;
1708 l_inc_count := l_inc_count + 2 ;
1709 end if;
1710 else
1711 l_rec := l_ded_rec;
1712 l_field := l_ded_count;
1713 l_ded_count := l_ded_count + 2 ;
1714 end if;
1715
1716 if l_field = 2 then
1717 l_archive_tab(l_rec).act_info2 := l_code;
1718 l_archive_tab(l_rec).act_info3 := t_final_arch(l_code).value;
1719 elsif l_field = 4 then
1720 l_archive_tab(l_rec).act_info4 := l_code;
1721 l_archive_tab(l_rec).act_info5 := t_final_arch(l_code).value;
1722 elsif l_field = 6 then
1723 l_archive_tab(l_rec).act_info6 := l_code;
1724 l_archive_tab(l_rec).act_info7 := t_final_arch(l_code).value;
1725 elsif l_field = 8 then
1726 l_archive_tab(l_rec).act_info8 := l_code;
1727 l_archive_tab(l_rec).act_info9 := t_final_arch(l_code).value;
1728 elsif l_field = 10 then
1729 l_archive_tab(l_rec).act_info10 := l_code;
1730 l_archive_tab(l_rec).act_info11 := t_final_arch(l_code).value;
1734 elsif l_field = 14 then
1731 elsif l_field = 12 then
1732 l_archive_tab(l_rec).act_info12 := l_code;
1733 l_archive_tab(l_rec).act_info13 := t_final_arch(l_code).value;
1735 l_archive_tab(l_rec).act_info14 := l_code;
1736 l_archive_tab(l_rec).act_info15 := t_final_arch(l_code).value;
1737 elsif l_field = 16 then
1738 l_archive_tab(l_rec).act_info16 := l_code;
1739 l_archive_tab(l_rec).act_info17 := t_final_arch(l_code).value;
1740 elsif l_field = 18 then
1741 l_archive_tab(l_rec).act_info18 := l_code;
1742 l_archive_tab(l_rec).act_info19 := t_final_arch(l_code).value;
1743 elsif l_field = 20 then
1744 l_archive_tab(l_rec).act_info20 := l_code;
1745 l_archive_tab(l_rec).act_info21 := t_final_arch(l_code).value;
1746 elsif l_field = 22 then
1747 l_archive_tab(l_rec).act_info22 := l_code;
1748 l_archive_tab(l_rec).act_info23 := t_final_arch(l_code).value;
1749 elsif l_field = 24 then
1750 l_archive_tab(l_rec).act_info24 := l_code;
1751 l_archive_tab(l_rec).act_info25 := t_final_arch(l_code).value;
1752 elsif l_field = 26 then
1753 l_archive_tab(l_rec).act_info26 := l_code;
1754 l_archive_tab(l_rec).act_info27 := t_final_arch(l_code).value;
1755 end if;
1756
1757 l_code := t_final_arch.next(l_code);
1758 end loop;
1759
1760 hr_utility.set_location(l_proc,180);
1761
1762 -- Employee's main certificate Gross Remunerations
1763 l_rec_count := l_rec_count + 1;
1764 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1765 l_archive_tab(l_rec_count).person_id := l_person_id;
1766 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS';
1767 if trunc(t_code(3696).group_value) > 0 then
1768 -- if no non-taxable income exists, then this value must be nil
1769 l_archive_tab(l_rec_count).act_info2 := trunc(t_code(3696).group_value);
1770 end if;
1771 if l_inc_count > 2 then
1772 -- if there is no income code, then 3697, 3698 must be nil
1773 l_archive_tab(l_rec_count).act_info3 := trunc(t_code(3697).group_value);
1774 l_archive_tab(l_rec_count).act_info4 := trunc(t_code(3698).group_value);
1775 end if;
1776 l_archive_tab(l_rec_count).act_info5 := trunc(t_code(9999).group_value);-- Gross PKG , only used in excpetion log calculations
1777 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1778
1779 hr_utility.set_location(l_proc,190);
1780
1781 -- Employee's main certificate Tax and Reasons
1782 l_rec_count := l_rec_count + 1;
1783 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1784 l_archive_tab(l_rec_count).person_id := l_person_id;
1785 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_TAX_AND_REASONS';
1786 if l_ded_count > 2 then
1787 -- if no deduction code, then this value must be nil
1788 l_archive_tab(l_rec_count).act_info2 := trunc(t_code(4497).group_value);
1789 end if;
1790
1791 hr_utility.set_location(l_proc,191);
1792
1793 l_4101 := trim(to_char(t_code(4101).group_value,'99999999990D99'));
1794 if t_dir_num.exists('To Be Advised') then -- Lumpsum amounts exist
1795 l_4102 := trim(to_char(t_code(4102).group_value + t_code(4102||'-To Be Advised').group_value,'99999999990D99'));
1796 l_4115 := trim(to_char(t_code(4115||'-To Be Advised').group_value,'99999999990D99'));
1797 if (t_code('3915-To Be Advised').group_value = 0 and
1798 t_code('3920-To Be Advised').group_value = 0 and
1799 t_code('3921-To Be Advised').group_value = 0 and
1800 t_code('3922-To Be Advised').group_value = 0 and
1801 l_4115 = '0.00') then
1802 l_4115 := null;
1803 end if;
1804 else -- No Lumpsum amounts
1805 l_4102 := trim(to_char(t_code(4102).group_value,'99999999990D99'));
1806 l_4115 := null;
1807 end if;
1808
1809 hr_utility.set_location(l_proc,192);
1810 l_4141 := trim(to_char(t_code(4141).group_value,'99999999990D99')); -- UIF
1811 l_4142 := trim(to_char(nvl(t_code(4142).group_value,0),'99999999990D99')); -- Bug 14376752
1812 l_4149 := trim(to_char((to_number(l_4101) +
1813 to_number(l_4102) +
1814 nvl(to_number(l_4115),0) +
1815 to_number(l_4141) +
1816 to_number(nvl(l_4142,0)))
1817 ,'99999999990D99'));
1818
1819 hr_utility.set_location(l_proc,193);
1820
1821 -- TYE 2012 Changes
1822 IF (t_code.exists(4116) and l_tax_year > 2012 ) THEN
1823 l_4116 :=trim(to_char(t_code(4116).group_value,'99999999990D99'));
1824 IF l_4116 = '0.00' and ((l_nature not in ('A','B','C','N') OR l_65Years_age ='A')
1825 OR (nvl(l_4005,'0') = '0' )) THEN
1826 l_4116 :=null;
1827 END IF;
1828 ELSE
1829 l_4116 :=null;
1830 END IF;
1831
1832 if l_main_cert_type = 'IT3A' then
1833 if l_4101 = '0.00' then l_4101 := null; end if;
1834 if l_4102 = '0.00' then l_4102 := null; end if;
1835 if l_4115 = '0.00' then l_4115 := null; end if;
1836 l_4150 := it3a_reason_code( l_run_assact_id, l_nature, l_tax_status, l_main_cert_dir_val
1837 , t_code(3697).group_value + t_code(3698).group_value
1838 , t_code(3696).group_value
1839 , 'N', null, l_independent_contractor, l_foreign_income, l_labour_broker,l_4116 ) ;
1840 end if;
1841
1842 -- Bug#14578341
1843 if l_4101 = '0.00' then
1844 l_4101 := null;
1845 end if;
1846 if l_4102 = '0.00' then
1847 l_4102 := null;
1848 end if;
1849
1850 -- 2013 TAX YEAR CHANGES.
1851
1852 hr_utility.set_location('l_3922_rep_err' || l_3922_rep_err,193.1);
1853
1854 l_3922_non_taxable := nvl(get_balance_value ('Comp iro Death Non Taxable','_ASG_TAX_YTD',l_run_assact_id),0);
1855 l_3922_rfi := nvl(get_balance_value ('Comp iro Death during Emp RFI','_ASG_TAX_YTD',l_run_assact_id),0);
1856 l_3922_nrfi := nvl(get_balance_value ('Comp iro Death during Emp NRFI','_ASG_TAX_YTD',l_run_assact_id),0);
1857
1858 l_3922 := l_3922_non_taxable + l_3922_rfi + l_3922_nrfi;
1859 if l_3922 > 0 then
1860 OPEN csr_get_glb_value;
1861 FETCH csr_get_glb_value INTO l_global_value;
1862 CLOSE csr_get_glb_value;
1863
1864 if l_3922 > fnd_number.canonical_to_number(l_global_value) then
1865
1866 if l_3922_non_taxable <> 0 then
1867 l_3922_rep_err := 'Y';
1868 hr_utility.set_location('l_3922_rep_err' || l_3922_rep_err,193.3);
1869 end if;
1870 else
1871
1872 if (l_3922_rfi + l_3922_nrfi) <> 0 then
1873 l_3922_rep_err := 'N';
1874 hr_utility.set_location('l_3922_rep_err' || l_3922_rep_err,193.4);
1875 end if;
1876
1877 end if;
1878 end if;
1879 -- 2013 TAX YEAR CHANGES.
1880
1881
1882
1883 hr_utility.set_location(l_proc,195);
1884
1885 l_archive_tab(l_rec_count).act_info3 := l_4101;
1886 l_archive_tab(l_rec_count).act_info4 := l_4102;
1887 l_archive_tab(l_rec_count).act_info5 := l_4115;
1888 l_archive_tab(l_rec_count).act_info6 := l_4141;
1889 l_archive_tab(l_rec_count).act_info7 := l_4142;
1890 l_archive_tab(l_rec_count).act_info8 := l_4149;
1891 l_archive_tab(l_rec_count).act_info9 := l_4150;
1892 l_archive_tab(l_rec_count).act_info10:= t_code(9997).group_value; -- Tax_ASG_TAX_YTD - for use in exception log
1893 l_archive_tab(l_rec_count).act_info11:= t_code(9998).group_value; -- PAYE_ASG_TAX_YTD - for use in exception log
1894 l_archive_tab(l_rec_count).act_info13:= l_4116;
1895 l_archive_tab(l_rec_count).act_info14:= l_3922_rep_err; -- 2013 TAX YEAR CHANGES.
1896 l_archive_tab(l_rec_count).act_info30:= l_temp_cert_num;
1897
1898 -- 9877034 starts
1899 if l_tax_year > 2010 then
1900 l_archive_tab(l_rec_count).act_info12:= l_terminated_bef_sep;
1901 end if;
1902 -- 9877034 ends
1903
1904 hr_utility.set_location(l_proc,200);
1905
1906
1907 ------------------------------------------------------------------------
1908 -- 9. Populate employee's Lumpsum Certificate information into archive_tab
1909 ------------------------------------------------------------------------
1910 l_lump_dir_set := t_lump_dir_set.first;
1911 if l_lump_dir_set is not null then
1912 loop
1913
1914 hr_utility.set_location('Archiving for directive_number set '||l_lump_dir_set,210);
1915 l_valid_dir_flag := false;
1916
1917 l_cert_count := l_cert_count + 1; -- increase certificate number count
1918 l_temp_cert_num := lpad(p_assactid,25,'0')||'-'||lpad(l_cert_count,4,'0'); -- Temporary certificate number
1919
1920 -- Employee information record
1921 l_rec_count := l_rec_count + 1; -- increase archive record count
1922 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num; -- Temporary certificate Number
1923 copy_record(l_archive_tab(0),l_archive_tab(l_rec_count));
1924 if t_dir_num(t_lump_dir_set(l_lump_dir_set).dir_1).certificate_type = 'IRP5' then -- Main Certificate Type (IRP5/IT3A/ITREG/A)
1925 l_archive_tab(l_rec_count).act_info2 := 'IRP5';
1926 elsif t_dir_num(t_lump_dir_set(l_lump_dir_set).dir_1).certificate_type = 'IT3A' then
1927 l_archive_tab(l_rec_count).act_info2 := 'IT3(a)';
1928 elsif t_dir_num(t_lump_dir_set(l_lump_dir_set).dir_1).certificate_type = 'A' then
1929 l_archive_tab(l_rec_count).act_info2 := 'A';
1930 end if;
1931
1932 --l_archive_tab(l_rec_count).act_info14 := trim(to_char(l_lumpsum_effective_date,'YYYYMMDD')); -- Date Employed From
1933 --l_archive_tab(l_rec_count).act_info15 := trim(to_char(l_lumpsum_effective_date,'YYYYMMDD')); -- Date Employed To
1934 l_archive_tab(l_rec_count).act_info16 := '1.0000'; -- Total Pay Periods in tax Year
1935 l_archive_tab(l_rec_count).act_info17 := '1.0000'; -- Pay Periods Worked
1936 l_archive_tab(l_rec_count).act_info18 := t_lump_dir_set(l_lump_dir_set).dir_1; -- Directive1
1937 l_archive_tab(l_rec_count).act_info19 := t_lump_dir_set(l_lump_dir_set).dir_2; -- Directive2
1938 l_archive_tab(l_rec_count).act_info20 := t_lump_dir_set(l_lump_dir_set).dir_3; -- Directive3
1939 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num; -- Temporary certificate Number
1940
1941 -- Employee contact information record
1942 l_rec_count := l_rec_count + 1;
1943 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1944 copy_record(l_archive_tab(1),l_archive_tab(l_rec_count));
1945 l_archive_tab(l_rec_count).act_info26 := 'LMPSM'; -- employee's lumpsum certificate
1946 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1947
1948 hr_utility.set_location(l_proc,220);
1949
1950 -- Employee's lumpsum information records
1951 l_code := g_code_list.first;
1952 loop
1953 if g_code_list(l_code).lumpsum = 'Y' and l_code not in('4102','4115','4116')then
1954 l_rec_count := l_rec_count + 1;
1955 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1956 l_archive_tab(l_rec_count).person_id := l_person_id;
1957 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_LUMPSUMS';
1958 l_archive_tab(l_rec_count).act_info2 := final_code(l_tax_year, l_code,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1959 l_archive_tab(l_rec_count).act_info3 := final_code(l_tax_year, t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_1).included_in,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
1960
1961 l_lump_value := t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_1).value;
1962 l_lump_grp_value := t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
1963
1964 if ( t_code.exists(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_2) ) then
1965 l_lump_value := l_lump_value + t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_2).value;
1966 l_lump_grp_value := l_lump_grp_value + t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
1967 end if;
1968 if (t_code.exists(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
1969 l_lump_value := l_lump_value + t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_3).value;
1970 l_lump_grp_value := l_lump_grp_value + t_code(l_code||'-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
1971 end if;
1972
1973 l_archive_tab(l_rec_count).act_info4 := l_lump_value;
1974 l_archive_tab(l_rec_count).act_info5 := l_lump_grp_value;
1975 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1976 end if;
1977 l_code := g_code_list.next(l_code);
1978 exit when l_code is null;
1979 end loop;
1980
1981 hr_utility.set_location(l_proc,230);
1982
1983 -- Employee's lumpsum certificate income information - final record
1984 t_final_arch.delete;
1985 l_rec_count := l_rec_count + 1;
1986 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
1987 l_archive_tab(l_rec_count).person_id := l_person_id;
1988 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_FINAL_EE_INCOME_1';
1989 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
1990
1991 FOR i in 1..3 LOOP
1992
1993 if ( i = 1 ) then
1994 l_dir_num := t_lump_dir_set(l_lump_dir_set).dir_1;
1995 elsif( i = 2 ) then
1996 l_dir_num := t_lump_dir_set(l_lump_dir_set).dir_2;
1997 else
1998 l_dir_num := t_lump_dir_set(l_lump_dir_set).dir_3;
1999 end if;
2000
2001 if (l_dir_num is not null) THEN
2002 if (l_dir_num <> 'To Be Advised' and not(l_valid_dir_flag) ) then
2003 l_valid_dir_flag := true;
2004 end if;
2005 l_code := g_code_list.first;
2006 loop
2007 if g_code_list(l_code).lumpsum = 'Y' and l_code not in('4102','4115','4116') then
2008 if t_code(l_code||'-'||l_dir_num).included_in is null
2009 and t_code(l_code||'-'||l_dir_num).group_value <>0 then
2010 l_code_complete := l_code||'-'||l_dir_num;
2011 l_code_arch := final_code(l_tax_year, l_code_complete,l_nature,l_tax_status,l_foreign_income); -- Bug 13367825
2012 hr_utility.set_location('l_code_arch:'||l_code_arch,230);
2013 if (t_final_arch.exists(l_code_arch)) then
2014 t_final_arch(l_code_arch).value := t_final_arch(l_code_arch).value + trunc(t_code(l_code_complete).group_value);
2015 else
2016 t_final_arch(l_code_arch).value := trunc(t_code(l_code_complete).group_value);
2017 end if;
2018 t_final_arch(l_code_arch).code_type := 'INCOME';
2019 end if;
2020 end if;
2021 l_code := g_code_list.next(l_code);
2022 exit when l_code is null;
2023 end loop;
2024 end if;
2025 END LOOP; -- For Loop
2026
2027 l_inc_count := 2;
2028 l_code := t_final_arch.first;
2029 while l_code is not null
2030 loop
2031 if l_inc_count = 2 then
2032 l_archive_tab(l_rec_count).act_info2 := l_code;
2033 l_archive_tab(l_rec_count).act_info3 := t_final_arch(l_code).value;
2034 elsif l_inc_count = 4 then
2035 l_archive_tab(l_rec_count).act_info4 := l_code;
2036 l_archive_tab(l_rec_count).act_info5 := t_final_arch(l_code).value;
2037 elsif l_inc_count = 6 then
2038 l_archive_tab(l_rec_count).act_info6 := l_code;
2039 l_archive_tab(l_rec_count).act_info7 := t_final_arch(l_code).value;
2040 elsif l_inc_count = 8 then
2041 l_archive_tab(l_rec_count).act_info8 := l_code;
2042 l_archive_tab(l_rec_count).act_info9 := t_final_arch(l_code).value;
2043 elsif l_inc_count = 10 then
2044 l_archive_tab(l_rec_count).act_info10 := l_code;
2045 l_archive_tab(l_rec_count).act_info11 := t_final_arch(l_code).value;
2046 elsif l_inc_count = 12 then
2047 l_archive_tab(l_rec_count).act_info12 := l_code;
2048 l_archive_tab(l_rec_count).act_info13 := t_final_arch(l_code).value;
2049 elsif l_inc_count = 14 then
2050 l_archive_tab(l_rec_count).act_info14 := l_code;
2051 l_archive_tab(l_rec_count).act_info15 := t_final_arch(l_code).value;
2052 elsif l_inc_count = 16 then
2053 l_archive_tab(l_rec_count).act_info16 := l_code;
2054 l_archive_tab(l_rec_count).act_info17 := t_final_arch(l_code).value;
2055 elsif l_inc_count = 18 then
2056 l_archive_tab(l_rec_count).act_info18 := l_code;
2057 l_archive_tab(l_rec_count).act_info19 := t_final_arch(l_code).value;
2058 elsif l_inc_count = 20 then
2059 l_archive_tab(l_rec_count).act_info20 := l_code;
2060 l_archive_tab(l_rec_count).act_info21 := t_final_arch(l_code).value;
2061 elsif l_inc_count = 22 then
2062 l_archive_tab(l_rec_count).act_info22 := l_code;
2063 l_archive_tab(l_rec_count).act_info23 := t_final_arch(l_code).value;
2064 elsif l_inc_count = 24 then
2065 l_archive_tab(l_rec_count).act_info24 := l_code;
2066 l_archive_tab(l_rec_count).act_info25 := t_final_arch(l_code).value;
2067 elsif l_inc_count = 26 then
2068 l_archive_tab(l_rec_count).act_info26 := l_code;
2069 l_archive_tab(l_rec_count).act_info27 := t_final_arch(l_code).value;
2070 end if;
2071 l_inc_count := l_inc_count+ 2;
2072
2073 l_code := t_final_arch.next(l_code);
2074 end loop;
2075
2076 hr_utility.set_location(l_proc,240);
2077
2078 -- Employee's Decuction Final Record for Code 4005
2079 if (l_valid_dir_flag) then
2080 l_rec_count := l_rec_count + 1;
2081 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
2082 l_archive_tab(l_rec_count).person_id := l_person_id;
2083 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_FINAL_EE_DEDUCTIONS';
2084 l_archive_tab(l_rec_count).act_info2 := '4005';
2085 l_archive_tab(l_rec_count).act_info3 := null;
2086 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
2087 end if;
2088
2089 -- Employee's lumpsum gross remuneration
2090 l_rec_count := l_rec_count + 1;
2091 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
2092 l_archive_tab(l_rec_count).person_id := l_person_id;
2093 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS';
2094 -- l_archive_tab(l_rec_count).act_info2 := null; -- Non-Taxable Income
2095 if l_inc_count > 2 then
2096 -- 2013 TAX YEAR CHANGES.
2097 l_lump_3696_val := t_code(3696||'-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2098 if (t_code.exists(3696||'-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2099 l_lump_3696_val := l_lump_3696_val + t_code(3696||'-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2100 end if;
2101 if (t_code.exists(3696||'-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2102 l_lump_3696_val := l_lump_3696_val + t_code(3696||'-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2103 end if;
2104 -- 2013 TAX YEAR CHANGES.
2105 l_lump_3697_val := t_code(3697||'-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2106 if (t_code.exists(3697||'-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2107 l_lump_3697_val := l_lump_3697_val + t_code(3697||'-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2108 end if;
2109 if (t_code.exists(3697||'-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2110 l_lump_3697_val := l_lump_3697_val + t_code(3697||'-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2111 end if;
2112 l_lump_3698_val := t_code(3698||'-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2113 if (t_code.exists(3698||'-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2114 l_lump_3698_val := l_lump_3698_val + t_code(3698||'-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2115 end if;
2116 if (t_code.exists(3698||'-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2117 l_lump_3698_val := l_lump_3698_val + t_code(3698||'-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2118 end if;
2119
2120 l_archive_tab(l_rec_count).act_info2 := l_lump_3696_val;
2121 l_archive_tab(l_rec_count).act_info3 := l_lump_3697_val;
2122 l_archive_tab(l_rec_count).act_info4 := l_lump_3698_val;
2123 end if;
2124 l_archive_tab(l_rec_count).act_info30 := l_temp_cert_num;
2125
2126 hr_utility.set_location(l_proc,242);
2127
2128 -- Employee's lumpsum tax and reasons
2129 l_rec_count := l_rec_count + 1;
2130 l_archive_tab(l_rec_count).assignment_id := l_assignment_id;
2131 l_archive_tab(l_rec_count).person_id := l_person_id;
2132 l_archive_tab(l_rec_count).action_info_category := 'ZATYE_EMPLOYEE_TAX_AND_REASONS';
2133
2134 l_4101 := '0.00'; -- SITE
2135 l_4102 := t_code('4102-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2136 if (t_code.exists('4102-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2137 l_4102 := l_4102 + t_code('4102-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2138 end if;
2139 if (t_code.exists('4102-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2140 l_4102 := l_4102 + t_code('4102-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2141 end if;
2142 l_4102 := trim(to_char(l_4102,'99999999990D99'));
2143 l_4115 := t_code('4115-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2144 if (t_code.exists('4115-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2145 l_4115 := l_4115 + t_code('4115-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2146 end if;
2147 if (t_code.exists('4115-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2148 l_4115 := l_4115 + t_code('4115-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2149 end if;
2150 l_4115 := trim(to_char(l_4115,'99999999990D99'));
2151
2152 l_4141 := '0.00';
2153 l_4142 := '0.00';
2154
2155 -- Bug 14376752
2156 l_4142 := t_code('4142-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2157 if (t_code.exists('4142-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2158 l_4142 := l_4142 + t_code('4142-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2159 end if;
2160 if (t_code.exists('4142-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2161 l_4142 := l_4142 + t_code('4142-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2162 end if;
2163 l_4142 := trim(to_char(nvl(to_number(l_4142),0),'99999999990D99'));
2164 -- Bug 14376752
2165
2166 l_4149 := trim(to_char(to_number(l_4102) + nvl(to_number(l_4115),0) + nvl(to_number(l_4142),0),'99999999990D99'));
2167 l_4150 := null;
2168
2169 hr_utility.set_location(l_proc,243);
2170 -- TYE 2012 Changes
2171 IF (t_code.exists(4116) and l_tax_year > 2012 ) THEN
2172 l_4116 := t_code('4116-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2173 if (t_code.exists('4116-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2174 l_4116 := l_4116 + t_code('4116-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2175 end if;
2176 if (t_code.exists('4116-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2177 l_4116 := l_4116 + t_code('4116-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2178 end if;
2179 l_4116 := trim(to_char(l_4116,'99999999990D99'));
2180
2181 IF l_4116 = '0.00' and (l_nature not in ('A','B','C','N') OR l_65Years_age ='A') THEN
2182 l_4116 :=null;
2183 END IF;
2184 ELSE
2185 l_4116 :=null;
2186 END IF;
2187
2188 -- For Lumpsum if value 4116 is '0.00' then do not print
2189 IF (l_4116 ='0.00') then
2190 l_4116 := null;
2191 END IF;
2192
2193 hr_utility.set_location(l_proc,244);
2194
2195 -- if there is no value in 3915, 3920, 3921 and 3922, then code 4115 must not be specified
2196 -- if there is still a value in 4115, then it should be reported as error in exception log
2197 l_lump_3915_value := t_code('3915-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2198 if (t_code.exists('3915-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2199 l_lump_3915_value :=l_lump_3915_value + t_code('3915-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2200 end if;
2201 if (t_code.exists('3915-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2202 l_lump_3915_value :=l_lump_3915_value + t_code('3915-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2203 end if;
2204
2205 l_lump_3920_value := t_code('3920-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2206 if (t_code.exists('3920-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2207 l_lump_3920_value :=l_lump_3920_value + t_code('3920-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2208 end if;
2209 if (t_code.exists('3920-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2210 l_lump_3920_value :=l_lump_3920_value + t_code('3920-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2211 end if;
2212
2213 l_lump_3921_value := t_code('3921-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2214 if (t_code.exists('3921-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2215 l_lump_3921_value :=l_lump_3921_value + t_code('3921-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2216 end if;
2217 if (t_code.exists('3921-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2218 l_lump_3921_value :=l_lump_3921_value + t_code('3921-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2219 end if;
2220 -- 2013 TAX YEAR CHANGES.
2221 l_lump_3922_value := t_code('3922-'||t_lump_dir_set(l_lump_dir_set).dir_1).group_value;
2222 if (t_code.exists('3922-'||t_lump_dir_set(l_lump_dir_set).dir_2)) then
2223 l_lump_3922_value :=l_lump_3922_value + t_code('3922-'||t_lump_dir_set(l_lump_dir_set).dir_2).group_value;
2224 end if;
2225 if (t_code.exists('3922-'||t_lump_dir_set(l_lump_dir_set).dir_3)) then
2226 l_lump_3922_value :=l_lump_3922_value + t_code('3922-'||t_lump_dir_set(l_lump_dir_set).dir_3).group_value;
2227 end if;
2228 -- 2013 TAX YEAR CHANGES.
2229 if ( l_lump_3915_value = 0 and
2230 l_lump_3920_value = 0 and
2231 l_lump_3921_value = 0 and
2232 l_lump_3922_value = 0 and -- 2013 TAX YEAR CHANGES.
2233 l_4115 = '0.00') then
2234 l_4115 := null;
2235 end if;
2236
2237 hr_utility.set_location(l_proc,245);
2238
2239 if t_dir_num(t_lump_dir_set(l_lump_dir_set).dir_1).certificate_type = 'IT3A' then
2240 if l_4101 = '0.00' then l_4101 := null; end if;
2241 if l_4102 = '0.00' then l_4102 := null; end if;
2242 if l_4115 = '0.00' then l_4115 := null; end if;
2243 l_4150 := it3a_reason_code( l_run_assact_id, l_nature, l_tax_status, l_main_cert_dir_val
2244 , l_lump_3697_val + l_lump_3698_val + l_lump_3696_val
2245 , 0, 'Y'
2246 , l_4102
2247 , l_independent_contractor, l_foreign_income, l_labour_broker,l_4116 ) ;
2248 end if;
2249
2250 -- Bug#14578341
2251 if l_4101 = '0.00' then
2252 l_4101 := null;
2253 end if;
2254 if l_4102 = '0.00' then
2255 l_4102 := null;
2256 end if;
2257
2258 l_archive_tab(l_rec_count).act_info2 := null; -- Total Deductions
2259 l_archive_tab(l_rec_count).act_info3 := l_4101;
2260 l_archive_tab(l_rec_count).act_info4 := l_4102;
2261 l_archive_tab(l_rec_count).act_info5 := l_4115;
2262 l_archive_tab(l_rec_count).act_info6 := l_4141;
2263 l_archive_tab(l_rec_count).act_info7 := l_4142;
2264 l_archive_tab(l_rec_count).act_info8 := l_4149;
2265 l_archive_tab(l_rec_count).act_info9 := l_4150;
2266 l_archive_tab(l_rec_count).act_info10:= null;
2267 l_archive_tab(l_rec_count).act_info11:= null;
2268 l_archive_tab(l_rec_count).act_info13:= l_4116;
2269
2270 l_archive_tab(l_rec_count).act_info30:= l_temp_cert_num;
2271
2272 --end if;
2273
2274 hr_utility.set_location(l_proc,250);
2275 l_lump_dir_set := t_lump_dir_set.next(l_lump_dir_set);
2276 exit when l_lump_dir_set is null;
2277 end loop;
2278 end if;
2279 end if; -- end of "if l_itreg_batch = 'N'"
2280
2281 hr_utility.set_location(l_proc,230);
2282
2283 ------------------------------------------------------------------------
2284 -- 10. Call archive API to archive data from l_archive_tab
2285 ------------------------------------------------------------------------
2286
2287 insert_archive_row(p_assactid, l_archive_tab);
2288
2289 hr_utility.set_location('Leaving '||l_proc,999);
2290
2291 end archive_data;
2292
2293
2294
2295 ---------------------------------------------------------------------------
2296 -- Procedure deinit_code
2297 ---------------------------------------------------------------------------
2298 procedure archdinit(pactid in number) as
2299
2300 cursor csr_employee_info_rec is
2301 select pai.action_information30
2302 , pai.action_context_id
2303 , pai.action_context_type
2304 from pay_payroll_actions ppa
2305 , pay_assignment_actions paa
2306 , pay_action_information pai
2307 where ppa.payroll_action_id = pactid
2308 and ppa.action_status = 'C'
2309 and paa.payroll_action_id = ppa.payroll_action_id
2310 and paa.action_status = 'C'
2311 and pai.action_context_id = paa.assignment_action_id
2312 and pai.action_context_type = 'AAP'
2313 and pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
2314 and action_information1 is null
2315 order by pai.action_information30 ;
2316
2317
2318 l_req_id NUMBER ;
2319 l_start_date DATE;
2320 l_end_date DATE;
2321 leg_param pay_payroll_actions.legislative_parameters%type;
2322 l_legal_entity_org number;
2323 l_tax_year varchar2(4);
2324 l_proc varchar2(100) := g_package||'deinit_code';
2325 l_cert_type varchar2(1);
2326 l_itreg_batch varchar2(1);
2327 l_itreg_cert_num varchar2(30) := lpad('0',30,'0');
2328 l_period_recon varchar2(30); -- 9877034 fix
2329 begin
2330 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
2331
2332 hr_utility.set_location('Entering '|| l_proc,10);
2333
2334 select legislative_parameters
2335 into leg_param
2336 from pay_payroll_actions
2337 where payroll_action_id = pactid;
2338
2339 l_legal_entity_org := get_parameter('LEGAL_ENTITY', leg_param);
2340 l_tax_year := get_parameter('TAX_YEAR', leg_param);
2341 l_cert_type := get_parameter('CERT_TYPE', leg_param);
2342
2343 if l_cert_type = '1' then
2344 l_itreg_batch := 'N';
2345 else
2346 l_itreg_batch := 'Y';
2347 end if;
2348
2349 -- commented as certificate number should not be generated if it is an itreg . Bug 10049956
2350
2351 /*
2352 if l_itreg_batch = 'Y' then
2353 for emprec in csr_employee_info_rec
2354 loop
2355 l_itreg_cert_num := lpad(l_itreg_cert_num + 1,30,'0');
2356
2357 update pay_action_information
2358 set action_information1 = l_itreg_cert_num
2359 where action_context_type = emprec.action_context_type
2360 and action_context_id = emprec.action_context_id
2361 and action_information30 = emprec.action_information30 ;
2362
2363 end loop;
2364 end if;
2365
2366 */
2367
2368 -- Fork Exception Log Concurrent Program
2369 l_start_date := to_date(get_parameter('START_DATE', leg_param),'YYYY/MM/DD hh24:mi:ss');
2370 l_end_date := to_date(get_parameter('END_DATE', leg_param),'YYYY/MM/DD hh24:mi:ss');
2371 l_period_recon := NVL(get_parameter('PERIOD_RECON', leg_param),'02'); -- 9877034 fix
2372
2373 l_req_id := fnd_request.submit_request( 'PAY', -- application
2374 'PYZATYVL2010', -- program
2375 'Create Tax Year End exception log', -- description
2376 NULL, -- start_time
2377 NULL, -- sub_request
2378 pactid,l_start_date,l_end_date,l_tax_year,l_period_recon,chr(0),-- Start of Parameters or Arguments
2379 '','','','','',
2380 '','','','','','','','','','',
2381 '','','','','','','','','','',
2382 '','','','','','','','','','',
2383 '','','','','','','','','','',
2384 '','','','','','','','','','',
2385 '','','','','','','','','','',
2386 '','','','','','','','','','',
2387 '','','','','','','','','','',
2388 '','','','','','','','','');
2389
2390 IF (l_req_id = 0) THEN
2391 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to Create Tax Certificate Exception Log');
2392 END IF;
2393
2394 hr_utility.set_location('Leaving '|| l_proc,999);
2395
2396 end archdinit;
2397
2398
2399 /*--------------------------------------------------------------------------
2400 Name : set_code_tables
2401 Purpose : This procedure can be used to set the global pl/sql tables
2402 Arguments :
2403 Notes : This will initialize the global tables -
2404 1) g_code_list - indexed by unique codes, contains code type
2405 Contain single row per code
2406 2) g_code_bal - indexed by running sequence, contains details
2407 of codes and balances feeding that code.
2408 Can contain multiple rows per code
2409 3) g_defined_balance_lst_lmpsm - defined balance list for lumpsum
2410 balances - to be used in batch balance retrieval
2411 4) g_defined_balance_lst_normal - defined balance list for normal
2412 balances - to be used in batch balance retrieval
2413 --------------------------------------------------------------------------*/
2414 procedure set_code_tables is
2415
2416 -- INCOME SOURCES
2417 -- normal codes
2418 -- Normal : 3601-3607,3609-3613,3615-3617
2419 -- Allowances : 3701-3706,3708-3717
2420 -- Fringe : 3801-3810,3813
2421 -- Other Lumpsum : 3907 (only Other Lumpsum taxed as annual payment - balance seq 1) ,3908
2422 --
2423 -- Lumpsum codes
2424 -- 3608,3614,3707,3718,3901,3906,3907(only balance sequence 3),3909,3915,3920,3921,3922 -- 2013 TAX YEAR CHANGES.
2425 --
2426 -- DEDUCTIONS/ CONTRIBUTIONS
2427 -- 4001-4007,4018,4024,4026,4030,4474,4493
2428
2429 -- initialize code table
2430 cursor csr_code_details is
2431 select code,
2432 decode(code,
2433 '3601','INCOME','3602','INCOME','3603','INCOME','3604','INCOME',
2434 '3605','INCOME','3606','INCOME','3607','INCOME','3609','INCOME',
2435 '3610','INCOME','3611','INCOME','3612','INCOME','3613','INCOME',
2436 '3615','INCOME','3616','INCOME','3617','INCOME',
2437 '3701','INCOME','3702','INCOME','3703','INCOME','3704','INCOME',
2438 '3705','INCOME','3706','INCOME','3708','INCOME','3709','INCOME',
2439 '3710','INCOME','3711','INCOME','3712','INCOME','3713','INCOME',
2440 '3714','INCOME','3715','INCOME','3716','INCOME','3717','INCOME',
2441 '3801','INCOME','3802','INCOME','3803','INCOME','3804','INCOME',
2442 '3805','INCOME','3806','INCOME','3807','INCOME','3808','INCOME',
2443 '3809','INCOME','3810','INCOME','3813','INCOME','3815','INCOME',
2444 '3907','INCOME','3908','INCOME',
2445 '3608','LUMPSUM','3614','LUMPSUM','3707','LUMPSUM','3718','LUMPSUM',
2446 '3901','LUMPSUM','3906','LUMPSUM','3907','LUMPSUM','3909','LUMPSUM',
2447 '3915','LUMPSUM','3920','LUMPSUM','3921','LUMPSUM', '3922', 'LUMPSUM',
2448 '4001','DEDUCTION','4002','DEDUCTION','4003','DEDUCTION','4004','DEDUCTION',
2449 '4005','DEDUCTION','4006','DEDUCTION','4007','DEDUCTION','4018','DEDUCTION',
2450 '4024','DEDUCTION','4026','DEDUCTION','4030','DEDUCTION','4474','DEDUCTION',
2451 '4493','DEDUCTION',
2452 '4101','SITE','4102','PAYE','4115','PAYE_RET_LMPSM','4116','MED_TAX_CR') code_type,
2453 sub_type(code,user_name,balance_sequence) code_sub_type,
2454 full_balance_name,
2455 balance_type_id,
2456 balance_sequence
2457 from pay_za_irp5_bal_codes
2458 where ( code in (3601,3602,3603,3604,3605,3606,3607,3609,3610,3611,3612,3613,3615,3616,3617,
2459 3701,3702,3703,3704,3705,3706,3708,3709,3710,3711,3712,3713,3714,3715,3716,3717,
2460 3801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3813,3908,
2461 4001,4002,4003,4004,4005,4006,4007,4018,4024,4026,4030,4474,4493,
2462 4101,3815)
2463 and balance_sequence = 1
2464 )
2465 or
2466 (code = 4005 and balance_sequence = 2)
2467 or
2468 (code = 3907 and balance_sequence = 1 and full_balance_name <> 'Other Lump Sums')
2469 or
2470 (code = 4102 and balance_sequence = 1 and full_balance_name <> 'Tax on Lump Sums')
2471 or
2472 (code = 4116 and balance_sequence = 1 and full_balance_name <> 'Medical Tax Credit Used on Lump Sums')
2473 or
2474 ( code in (3608,3614,3707,3718,3901,3906,3907,3909,3915,3920,3921,3922,4102,4115,4116)
2475 and balance_sequence = 3
2476 )
2477 order by code asc, balance_sequence desc;
2478
2479 l_prev_code varchar2(4) := '0000';
2480 l_count number := 1;
2481 l_lmpsm_count number := 1;
2482 l_normal_count number := 1;
2483 l_def_bal_id number;
2484 l_proc varchar2(100) := g_package||'set_code_tables';
2485 begin
2486 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
2487 hr_utility.set_location('Entering '|| l_proc,10);
2488
2489 for code_rec in csr_code_details
2490 loop
2491 -- Add next distinct code to g_code_list
2492 if l_prev_code <> code_rec.code then
2493 if code_rec.balance_sequence = 3 then
2494 g_code_list(code_rec.code).lumpsum := 'Y'; -- for 3907 lumpsum=Y', but code_type = 'INCOME'
2495 g_code_list(code_rec.code).code_type := code_rec.code_type;
2496 else
2497 g_code_list(code_rec.code).lumpsum := 'N';
2498 g_code_list(code_rec.code).code_type := code_rec.code_type;
2499 end if;
2500 l_prev_code := code_rec.code;
2501
2502 end if;
2503
2504 -- Add code details to g_code_bal and
2505 -- populate def_bal_list for lmpsm and normal used for batch balance retreival
2506 if ( code_rec.balance_sequence = 3) then
2507 l_def_bal_id := get_def_bal_id(code_rec.balance_type_id, '_ASG_LMPSM_TAX_YTD');
2508 g_code_bal(l_count).code := code_rec.code;
2509 g_code_bal(l_count).defined_balance_id := l_def_bal_id;
2510 g_code_bal(l_count).full_balance_name := code_rec.full_balance_name;
2511 g_code_bal(l_count).sub_type := code_rec.code_sub_type;
2512 g_code_bal(l_count).balance_type_id := code_rec.balance_type_id;
2513 g_defined_balance_lst_lmpsm(l_lmpsm_count).defined_balance_id := l_def_bal_id;
2514 l_lmpsm_count := l_lmpsm_count + 1;
2515 l_count := l_count + 1;
2516 else
2517 l_def_bal_id := get_def_bal_id(code_rec.balance_type_id, '_ASG_TAX_YTD');
2518 g_code_bal(l_count).code := code_rec.code;
2519 g_code_bal(l_count).defined_balance_id := l_def_bal_id;
2520 g_code_bal(l_count).full_balance_name := code_rec.full_balance_name;
2521 g_code_bal(l_count).sub_type := code_rec.code_sub_type;
2522 g_code_bal(l_count).balance_type_id := code_rec.balance_type_id;
2523 -- balances for code 4005,seq 2, have already been included with code 3810
2524 if not (code_rec.code = '4005' and code_rec.balance_sequence = 2) then
2525 g_defined_balance_lst_normal(l_normal_count).defined_balance_id := l_def_bal_id;
2526 l_normal_count := l_normal_count + 1;
2527 end if;
2528 l_count := l_count + 1;
2529 end if;
2530 end loop;
2531
2532 hr_utility.set_location('Leaving '|| l_proc,999);
2533 end set_code_tables;
2534
2535
2536 ----------------------------------------------------------------------------
2537 --- This function returns the subtype of code
2538 -- (NON_TAXABLE, RFI, NRFI, PKG -- for income sources
2539 -- RFI_LUMPSUM, LUMPSUM -- for Lumpsum sources
2540 -- DEDUCTION -- for deduction sources
2541 -- SITE, PAYE, PAYE_RET_LMPSM)
2542 -----------------------------------------------------------------------------
2543 function sub_type(p_code number, user_name varchar2, p_balance_sequence number) return varchar2 is
2544 begin
2545 if p_code in (4115) then
2546 return 'PAYE_RET_LMPSM';
2547 elsif p_code in (4102) then
2548 return 'PAYE';
2549 elsif p_code in (4116) then
2550 return 'MED_CRDT';
2551 elsif p_code in (4101) then
2552 return 'SITE';
2553 -- Lumpsums
2554 elsif substr(user_name,-22,22) = '_RFI_ASG_LMPSM_TAX_YTD' then
2555 return 'RFI_LUMPSUM';
2556 -- Non-taxable for code 3922
2557 -- 2013 TAX YEAR CHANGES.
2558 elsif p_code in (3922) and substr(user_name,18,11) = 'NON_TAXABLE' and p_balance_sequence = 3 then
2559 hr_utility.set_location('No_taxable_once',500);
2560 return 'NON_TAXABLE_LUMPSUM';
2561 -- 2013 TAX YEAR CHANGES.
2562 elsif p_balance_sequence = 3 then -- this will take 3907 - Other Lump sums
2563 return 'LUMPSUM';
2564 -- Deductions
2565 elsif p_code in (4001,4002,4003,4004,4005,4006,4007,4018,4024,4026,4030,4474,4493) then
2566 return 'DEDUCTION';
2567 -- Non Taxable Income
2568 elsif p_code in (3602,3604,3609,3612,3703,3714,3705,3709,3716,3908,3815) then
2569 return 'NON_TAXABLE';
2570 -- Income sources
2571 elsif substr(user_name,-16,16) = '_RFI_ASG_TAX_YTD' or substr(user_name,-20,20)='_RFI_NTG_ASG_TAX_YTD' then
2572 return 'RFI';
2573 elsif substr(user_name,-17,17) = '_NRFI_ASG_TAX_YTD' or substr(user_name,-21,21)='_NRFI_NTG_ASG_TAX_YTD' then
2574 return 'NRFI';
2575 elsif substr(user_name,-16,16) = '_PKG_ASG_TAX_YTD' OR substr(user_name,-20,20) = '_PKG_NTG_ASG_TAX_YTD' then
2576 return 'PKG';
2577 else
2578 return null;
2579 end if;
2580 end sub_type;
2581
2582
2583
2584
2585 -------------------------------------------------------------------------
2586 --- Function to fetch employee's basic data
2587 -------------------------------------------------------------------------
2588 procedure fetch_person_data (p_assactid in number
2589 , p_effective_date in date
2590 , p_itreg_batch in varchar2
2591 , p_tax_status in varchar2
2592 , p_employee_info_rec out nocopy act_info_rec
2593 , p_employee_contact_info_rec out nocopy act_info_rec
2594 , p_assignment_id out nocopy number
2595 , p_person_id out nocopy number
2596 , p_foreign_income out nocopy varchar2
2597 , pactid out nocopy number
2598 , p_nature out nocopy varchar2
2599 , p_independent_contractor out nocopy varchar2
2600 , p_labour_broker out nocopy varchar2
2601 , p_lumpsum_date out nocopy date) is
2602 cursor csr_asg_info is
2603 select aei.assignment_id
2604 , substr(aei.AEI_INFORMATION2,1,120) trade_name
2605 , hr_general.decode_lookup('ZA_PER_NATURES',aei.AEI_INFORMATION4) nature
2606 , paa.payroll_action_id
2607 , aei.aei_information6 independent_contractor
2608 , aei.aei_information10 labour_broker
2609 , aei.aei_information15 foreign_income
2610 , aei.aei_information13 payment_type
2611 , aei.aei_information14 personal_pay_method_id
2612 from per_assignment_extra_info aei
2613 , pay_assignment_actions paa
2614 where paa.assignment_action_id = p_assactid
2615 and aei.assignment_id = paa.assignment_id
2616 and aei.aei_information_category = 'ZA_SPECIFIC_INFO';
2617
2618
2619 cursor csr_person_info(p_assignment_id number, l_effective_date date) is
2620 select ppf.person_id
2621 , substr(ltrim(rtrim(ppf.last_name)),1,120) last_name
2622 , ppf.first_name || ',' || ppf.middle_names first_two_names
2623 , ppf.national_identifier id_number
2624 , ppf.per_information2 passport_number
2625 , ppf.per_information10 country_of_passport_issue
2626 , to_char(ppf.date_of_birth,'YYYYMMDD') date_of_birth
2627 , ppf.per_information1 income_tax_ref_num
2628 , ppf.employee_number
2629 , ppf.email_address
2630 --, a.location_id
2631 , nvl(a.location_id, nvl(pap.location_id, haou.location_id)) location_id
2632 from per_all_people_f ppf
2633 , per_all_assignments_f a
2634 , per_all_positions pap
2635 , hr_all_organization_units haou
2636 where a.assignment_id = p_assignment_id
2637 and ppf.person_id = a.person_id
2638 and l_effective_date between a.effective_start_date and a.effective_end_date
2639 and l_effective_date between ppf.effective_start_date and ppf.effective_end_date
2640 and pap.position_id (+) = a.position_id
2641 and haou.organization_id = a.organization_id ;
2642
2643
2644 -- Cursor to fetch Business/Residential address
2645 cursor csr_sars_address(p_person_id number, l_effective_date date
2646 , p_address_style varchar2, p_address_type varchar2) is
2647 select address_line1
2648 , address_line2
2649 , address_line3
2650 , region_1
2651 , region_2
2652 , town_or_city
2653 , postal_code
2654 from per_addresses
2655 where person_id = p_person_id
2656 and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
2657 and style = p_address_style
2658 and address_type = p_address_type;
2659
2660 cursor csr_sars_loc_address(p_location_id number)
2661 is
2662 select lei_information1 ee_unit_num
2663 , lei_information2 ee_complex
2664 , lei_information3 ee_street_num
2665 , lei_information4 ee_street_name
2666 , lei_information5 ee_suburb_district
2667 , lei_information6 ee_town_city
2668 , lei_information7 ee_postal_code
2669 from hr_location_extra_info
2670 where location_id = p_location_id
2671 and information_type ='ZA_SARS_ADDRESS';
2672
2673 cursor csr_location_code(p_location_id number)
2674 is
2675 select location_code
2676 from hr_locations
2677 where location_id = p_location_id;
2678
2679 -- Cursor to fetch Postal address
2680 cursor csr_postal_address(p_person_id number, l_effective_date date) is
2681 select decode(region_2,'Y','X',null) -- Postal Address same as residential address flag
2682 , decode(region_2,'Y',null,address_line1) -- if flag = Y, then don't populate remaining postal address fields
2683 , decode(region_2,'Y',null,address_line2)
2684 , decode(region_2,'Y',null,address_line3)
2685 , decode(region_2,'Y',null,postal_code)
2686 from per_addresses
2687 where person_id = p_person_id
2688 and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
2689 and style = 'ZA'
2690 and primary_flag = 'Y';
2691
2692 --Added for ER 9387986
2693 cursor csr_asg_bank_ddf (p_assignment_id number, l_effective_date date) is
2694 select personal_payment_method_id personal_pay_method_id
2695 , ppm_information1 account_type
2696 from pay_personal_payment_methods_f
2697 where assignment_id = p_assignment_id
2698 and PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
2699 and ppm_information1 in ('Y','0','7')
2700 and l_effective_date between effective_start_date and effective_end_date;
2701
2702
2703 cursor asg_account_details( p_personal_pay_method_id number, l_effective_date date) is
2704 select pea.segment2 account_type -- account_type
2705 , pea.segment3 account_number -- account number
2706 , pea.segment1 branch_code -- bank branch code
2707 , trim(translate(trim(bnk.bank_name),
2708 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- ,''0123456789~%^&*<>{}[]"\/?@&$!#+=;:().',
2709 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- ,''')) bank_name
2710 , trim(translate(trim(bnk.branch_name),
2711 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- ,''0123456789~%^&*<>{}[]"\/?@&$!#+=;:().',
2712 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- ,''')) bank_branch_name
2713 , substr(pea.segment4,1,50) ac_holder_name -- account holder name
2714 , pea.segment6 ac_holder_relation-- account holder relationship
2715 from PAY_PERSONAL_PAYMENT_METHODS_F PPM
2716 , pay_external_accounts pea
2717 , pay_za_branch_cdv_details bnk
2718 where PPM.PERSONAL_PAYMENT_METHOD_ID = p_personal_pay_method_id
2719 and PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
2720 and bnk.branch_code = pea.segment1
2721 and l_effective_date between PPM.effective_start_date and PPM.effective_end_date ;
2722
2723 l_assignment_id number;
2724 l_person_id number;
2725 asg_rec csr_asg_info%rowtype;
2726 person_rec csr_person_info%rowtype;
2727 asg_bnk_ddf_rec csr_asg_bank_ddf%rowtype;
2728 l_surname_or_trade_name varchar2(120);
2729 l_first_two_names varchar2(90);
2730 l_initials varchar2(5);
2731 l_effective_date date;
2732 acc asg_account_details%rowtype;
2733 l_run_assact_id number;
2734 l_proc varchar2(100) := g_package||'fetch_person_data';
2735 l_tax_year varchar2(4);
2736 l_leg_param varchar2(1000);
2737 l_max_act_seq number;
2738 l_payroll_id number;
2739 l_tax_year_start_date date;
2740 l_tax_year_end_date date;
2741 l_days_in_tax_year number;
2742 l_total_tax_periods number;
2743 l_asg_hire_date date;
2744 l_asg_term_date date;
2745
2746 l_late_payment varchar2(1);
2747 l_date_employed_from date;
2748 l_date_employed_to date;
2749 l_total_pay_periods varchar2(8);
2750 l_pay_periods_worked varchar2(8);
2751 l_itreg_cert_num varchar2(30);
2752
2753 l_period_recon varchar2(30); -- 9877034 fix
2754 l_period_recon_last_date date; -- 9877034 fix
2755
2756 l_location_code hr_locations.location_code%type;
2757
2758 begin
2759 hr_utility.set_location('Entering '||l_proc,10);
2760 select ppa.legislative_parameters
2761 , paa.assignment_id
2762 into l_leg_param
2763 , l_assignment_id
2764 from pay_payroll_actions ppa
2765 , pay_assignment_actions paa
2766 where paa.assignment_action_id = p_assactid
2767 and ppa.payroll_action_id = paa.payroll_action_id ;
2768
2769 l_tax_year := get_parameter('TAX_YEAR', l_leg_param);
2770
2771 -- 9877034 starts
2772 l_period_recon := NVL(get_parameter('PERIOD_RECON', l_leg_param),'02');
2773
2774 select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
2775 into l_period_recon_last_date
2776 from dual;
2777
2778 fnd_file.put_line (fnd_file.LOG, '$$$ l_period_recon_last_date '||l_period_recon_last_date);
2779 -- 9877034 ends
2780
2781 -- fetch person and assignment details
2782 open csr_asg_info;
2783 fetch csr_asg_info into asg_rec;
2784 close csr_asg_info;
2785
2786 l_assignment_id := asg_rec.assignment_id;
2787
2788 -- get l_effective_date to fetch person/phones/bank etc data
2789 select least(p_effective_date,max(effective_end_date))
2790 into l_effective_date
2791 from per_all_assignments_f
2792 where assignment_id = l_assignment_id;
2793
2794 hr_utility.set_location(l_proc,20);
2795
2796 open csr_person_info(l_assignment_id,l_effective_date);
2797 fetch csr_person_info into person_rec;
2798 close csr_person_info;
2799
2800 l_person_id := person_rec.person_id;
2801
2802 -- payroll_id for which payroll process for this assignment
2803 -- was run in this tax year
2804 select max(paa.action_sequence)
2805 into l_max_act_seq
2806 from pay_assignment_actions paa
2807 , pay_payroll_actions ppa
2808 , per_time_periods ptp
2809 where paa.assignment_id = l_assignment_id
2810 and paa.payroll_action_id = ppa.payroll_action_id
2811 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
2812 and ptp.time_period_id = ppa.time_period_id
2813 and ptp.prd_information1 = l_tax_year
2814 and ptp.end_date <= decode(l_period_recon, '02',ptp.end_date, l_period_recon_last_date) -- 9877034 fix
2815 and paa.action_status IN ('C','S')
2816 and ppa.action_status = 'C';
2817
2818 select ppa.payroll_id, paa.assignment_action_id, ptp.start_date
2819 into l_payroll_id, l_run_assact_id, p_lumpsum_date
2820 from pay_payroll_actions ppa
2821 , pay_assignment_actions paa
2822 , per_time_periods ptp
2823 where paa.assignment_id = l_assignment_id
2824 and paa.action_sequence = l_max_act_seq
2825 and ppa.payroll_action_id = paa.payroll_action_id
2826 and ptp.time_period_id = ppa.time_period_id ;
2827
2828 hr_utility.trace('Payroll ID :' || l_payroll_id);
2829
2830 -- 9877034 starts
2831 if l_period_recon = '02' then
2832 select min(start_date), max(end_date)
2833 into l_tax_year_start_date, l_tax_year_end_date
2834 from per_time_periods
2835 where payroll_id = l_payroll_id
2836 and prd_information1 = l_tax_year;
2837
2838 select count(start_date)
2839 into l_total_tax_periods
2840 from per_time_periods
2841 where payroll_id = l_payroll_id
2842 and prd_information1 = l_tax_year;
2843 else
2844 select min(start_date), max(end_date)
2845 into l_tax_year_start_date, l_tax_year_end_date
2846 from per_time_periods
2847 where payroll_id = l_payroll_id
2848 and prd_information1 = l_tax_year
2849 and end_date <= l_period_recon_last_date;
2850
2851 select count(start_date)
2852 into l_total_tax_periods
2853 from per_time_periods
2854 where payroll_id = l_payroll_id
2855 and prd_information1 = l_tax_year
2856 and end_date <= l_period_recon_last_date;
2857 end if;
2858 fnd_file.put_line (fnd_file.LOG, '$$$ l_tax_year_end_date '||l_tax_year_end_date);
2859 fnd_file.put_line (fnd_file.LOG, '$$$ l_tax_year_start_date '||l_tax_year_start_date);
2860 -- 9877034 ends
2861
2862 l_days_in_tax_year := l_tax_year_end_date - l_tax_year_start_date + 1 ;
2863
2864
2865 select nvl(min(paaf.effective_start_date), fnd_date.canonical_to_date('1001/01/01 00:00:00'))
2866 , nvl(max(paaf.effective_end_date), fnd_date.canonical_to_date('4712/12/31 00:00:00'))
2867 into l_asg_hire_date
2868 , l_asg_term_date
2869 from per_assignment_status_types past,
2870 per_all_assignments_f paaf
2871 where paaf.assignment_id = l_assignment_id
2872 and paaf.effective_start_date <= l_tax_year_end_date
2873 and paaf.assignment_status_type_id = past.assignment_status_type_id
2874 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
2875
2876 hr_utility.trace('Assignment_id : '||l_assignment_id);
2877 hr_utility.trace('Assignment Hire Date :' || l_asg_hire_date);
2878 hr_utility.trace('Assignment Termination Date :' || l_asg_term_date);
2879
2880 if l_asg_term_date < l_tax_year_start_date then
2881 l_late_payment := 'Y';
2882 else
2883 l_late_payment := 'N';
2884 end if;
2885
2886 if l_tax_year_start_date < l_asg_hire_date then
2887 l_date_employed_from := l_asg_hire_date;
2888 else
2889 if l_late_payment = 'Y' then
2890 l_date_employed_from := l_asg_term_date;
2891 else
2892 l_date_employed_from := l_tax_year_start_date;
2893 end if;
2894 end if;
2895
2896 if l_tax_year_end_date < l_asg_term_date then
2897 l_date_employed_to := l_tax_year_end_date;
2898 else
2899 if l_late_payment = 'Y' then
2900 l_date_employed_to := l_date_employed_from;
2901 else
2902 l_date_employed_to := l_asg_term_date;
2903 end if;
2904 end if;
2905
2906 if p_tax_status = 'G' then
2907 l_total_pay_periods := trim(to_char(trunc(l_days_in_tax_year,4),'990D9999')) ;
2908 if l_late_payment = 'Y' then
2909 l_pay_periods_worked := '1.0000';
2910 else
2911 l_pay_periods_worked := trim(to_char(trunc( nvl(get_balance_value('Total Seasonal Workers Days Worked','_ASG_TAX_YTD',l_run_assact_id),0), 4),'990D9999'));
2912 end if;
2913 else
2914 l_total_pay_periods := trim(to_char(trunc(l_total_tax_periods,4),'990D9999')) ;
2915 if l_late_payment = 'Y' then
2916 l_pay_periods_worked := '1.0000';
2917 else
2918 select trim(to_char(trunc( (l_date_employed_to - l_date_employed_from + 1)/
2919 (( l_tax_year_end_date - l_tax_year_start_date +1)/l_total_tax_periods)
2920 ,4),'990D9999'))
2921 into l_pay_periods_worked
2922 from dual;
2923 end if;
2924 end if;
2925
2926 hr_utility.set_location(l_proc,30);
2927
2928 if asg_rec.nature in ('A','B','C','N') then
2929 l_surname_or_trade_name := person_rec.last_name;
2930 else
2931 l_surname_or_trade_name := asg_rec.trade_name;
2932 end if;
2933
2934 if asg_rec.nature in ('D','E','F','G','H') then
2935 l_first_two_names := null;
2936 l_initials := null;
2937 else
2938 l_first_two_names := substr(names(person_rec.first_two_names),1,90);
2939 l_initials := initials(person_rec.first_two_names);
2940 end if;
2941
2942 if asg_rec.nature in ('B','D','E','F','G','H') then
2943 person_rec.id_number := null;
2944 /* person_rec.passport_number := null;
2945 person_rec.country_of_passport_issue := null; */ -- 9877034 fix
2946 end if ;
2947
2948 /* if asg_rec.nature = 'B' then
2949 person_rec.country_of_passport_issue := 'ZNC';
2950 end if ; */ -- 9877034 fix
2951
2952 if asg_rec.nature in ('D','E','F','G','H') then
2953 person_rec.date_of_birth := null;
2954 end if ;
2955
2956 if asg_rec.nature = 'F' then
2957 person_rec.income_tax_ref_num := null;
2958 end if;
2959
2960 hr_utility.set_location(l_proc,40);
2961
2962 -- Bank Account Information
2963 -- At bank detail DDF, account type contains values
2964 -- Y (Internal Account Payment)
2965 -- 0 (Cash Payment)
2966 -- 7 (Foreign Bank Account Payment)
2967
2968 -- At Assignment Extra Info, Payment Type contains values
2969 -- 0 (Cash Payment)
2970 -- 1 (Internal Account Payment)
2971 -- 7 (Foreign Bank Account Payment)
2972
2973 -- If it is 1 (Internal Account Payment),
2974 -- then account details need to be fetched and reported
2975 -- else Employee Bank Account Type will be set to 0/7 and rest of the details reported as null
2976
2977 open csr_asg_bank_ddf(l_assignment_id,l_effective_date);
2978 fetch csr_asg_bank_ddf into asg_bnk_ddf_rec;
2979 if csr_asg_bank_ddf%found then
2980 if asg_bnk_ddf_rec.account_type = 'Y' then
2981 open asg_account_details(asg_bnk_ddf_rec.personal_pay_method_id, l_effective_date);
2982 fetch asg_account_details into acc;
2983 close asg_account_details;
2984 else
2985 acc.account_type := asg_bnk_ddf_rec.account_type;
2986 end if;
2987 --Bank Detail DDF not set, hence retrieve from Assignment EIT
2988 elsif asg_rec.payment_type = 1 then
2989 open asg_account_details(asg_rec.personal_pay_method_id, l_effective_date);
2990 fetch asg_account_details into acc;
2991 close asg_account_details;
2992 else
2993 acc.account_type := asg_rec.payment_type;
2994 end if;
2995 close csr_asg_bank_ddf;
2996
2997 -- commented for itreg as certification number should not be generated.Bug 10049956
2998 -- certificate number for ITREG batch
2999 /*
3000 if p_itreg_batch = 'Y' then
3001 if person_rec.id_number is not null then
3002 l_itreg_cert_num := lpad(person_rec.id_number,30,'0');
3003 elsif person_rec.passport_number is not null then
3004 l_itreg_cert_num := lpad(person_rec.passport_number,30,'0');
3005 end if;
3006 end if;
3007 */
3008
3009 hr_utility.set_location(l_proc,50);
3010
3011 p_employee_info_rec.assignment_id := l_assignment_id;
3012 p_employee_info_rec.person_id := l_person_id;
3013 p_employee_info_rec.action_info_category := 'ZATYE_EMPLOYEE_INFO';
3014 p_employee_info_rec.act_info1 := null; --case when p_itreg_batch='Y' then l_itreg_cert_num else null end; -- Certificate Number -- will be generated in IRP5/IT3A process -- modified for bug 10049956
3015 p_employee_info_rec.act_info2 := null; -- Certificate Type -- This will be populated in archive code
3016 p_employee_info_rec.act_info3 := asg_rec.nature;
3017 p_employee_info_rec.act_info4 := case when p_itreg_batch='N' then l_tax_year else null end; -- Year of Assessment
3018 p_employee_info_rec.act_info5 := l_surname_or_trade_name;
3019 p_employee_info_rec.act_info6 := l_first_two_names;
3020 p_employee_info_rec.act_info7 := l_initials;
3021 p_employee_info_rec.act_info8 := person_rec.id_number;
3022 p_employee_info_rec.act_info9 := person_rec.passport_number;
3023 p_employee_info_rec.act_info10 := person_rec.country_of_passport_issue;
3024 p_employee_info_rec.act_info11 := person_rec.date_of_birth;
3025 p_employee_info_rec.act_info12 := person_rec.income_tax_ref_num;
3026 p_employee_info_rec.act_info13 := person_rec.employee_number;
3027 p_employee_info_rec.act_info14 := case when p_itreg_batch='N' then to_char(l_date_employed_from,'YYYYMMDD') else null end; -- Date Employed From
3028 p_employee_info_rec.act_info15 := case when p_itreg_batch='N' then to_char(l_date_employed_to,'YYYYMMDD') else null end; -- Date Employed To
3029 p_employee_info_rec.act_info16 := case when p_itreg_batch='N' then l_total_pay_periods else null end; -- Pay Periods in Year of Assessment
3030 p_employee_info_rec.act_info17 := case when p_itreg_batch='N' then l_pay_periods_worked else null end; -- Pay periods worked
3031 p_employee_info_rec.act_info18 := null; -- Directive1 -- This will be populated in archive code
3032 p_employee_info_rec.act_info19 := null; -- Directive2 -- This will be populated in archive code
3036 p_employee_info_rec.act_info23 := acc.branch_code;
3033 p_employee_info_rec.act_info20 := null; -- Directive3 -- This will be populated in archive code
3034 p_employee_info_rec.act_info21 := acc.account_type;
3035 p_employee_info_rec.act_info22 := acc.account_number;
3037 p_employee_info_rec.act_info24 := acc.bank_name;
3038 p_employee_info_rec.act_info25 := acc.bank_branch_name;
3039 p_employee_info_rec.act_info26 := acc.ac_holder_name;
3040 p_employee_info_rec.act_info27 := acc.ac_holder_relation;
3041 p_employee_info_rec.act_info28 := null; -- Old/Manual Certificate
3042 p_employee_info_rec.act_info29 := null; -- Manual Certificate Number
3043 p_employee_info_rec.act_info30 := null; -- Temporary Certificate Number -- This will be populated in deinit code
3044
3045 hr_utility.set_location(l_proc,60);
3046
3047 -- EMPLOYEE_CONTACT_INFORMATION
3048 p_employee_contact_info_rec.assignment_id := l_assignment_id;
3049 p_employee_contact_info_rec.person_id := l_person_id;
3050 p_employee_contact_info_rec.action_info_category := 'ZATYE_EMPLOYEE_CONTACT_INFO';
3051 p_employee_contact_info_rec.act_info1 := null; -- case when p_itreg_batch='Y' then l_itreg_cert_num else null end; -- Certificate Number -- will be generated in IRP5/IT3A process -- no certificate number for ITREG bug 10049956
3052 p_employee_contact_info_rec.act_info2 := person_rec.email_address ;
3053
3054 hr_utility.set_location(l_proc,70);
3055
3056 -- Populate Phone numbers
3057 get_phones (l_person_id
3058 , l_effective_date
3059 , p_employee_contact_info_rec.act_info3
3060 , p_employee_contact_info_rec.act_info4
3061 , p_employee_contact_info_rec.act_info5
3062 , p_employee_contact_info_rec.act_info6) ;
3063
3064 hr_utility.set_location(l_proc,80);
3065
3066 -- Populate Address Information
3067
3068 -- hr_utility.trace_on(null,'ZATRC');
3069 hr_utility.trace('l_effective_date = '||to_char(l_effective_date));
3070 -- Business Address
3071 -- Fetch peron's address with address_style 'South Africa(SARS)' and address_type 'ZA-Business'
3072 /*open csr_sars_address(l_person_id, l_effective_date, 'ZA_SARS', 'ZA_BUS');
3073 fetch csr_sars_address into p_employee_contact_info_rec.act_info7
3074 , p_employee_contact_info_rec.act_info8
3075 , p_employee_contact_info_rec.act_info9
3076 , p_employee_contact_info_rec.act_info10
3077 , p_employee_contact_info_rec.act_info11
3078 , p_employee_contact_info_rec.act_info12
3079 , p_employee_contact_info_rec.act_info13;
3080 if csr_sars_address%notfound then
3081 open csr_sars_loc_address(person_rec.location_id);
3082 fetch csr_sars_loc_address into p_employee_contact_info_rec.act_info7
3083 , p_employee_contact_info_rec.act_info8
3084 , p_employee_contact_info_rec.act_info9
3085 , p_employee_contact_info_rec.act_info10
3086 , p_employee_contact_info_rec.act_info11
3087 , p_employee_contact_info_rec.act_info12
3088 , p_employee_contact_info_rec.act_info13;
3089 close csr_sars_loc_address;
3090 end if;
3091 close csr_sars_address;*/
3092 -- Fetch peron's address from assignment
3093 open csr_sars_loc_address(person_rec.location_id);
3094 fetch csr_sars_loc_address into p_employee_contact_info_rec.act_info7
3095 , p_employee_contact_info_rec.act_info8
3096 , p_employee_contact_info_rec.act_info9
3097 , p_employee_contact_info_rec.act_info10
3098 , p_employee_contact_info_rec.act_info11
3099 , p_employee_contact_info_rec.act_info12
3100 , p_employee_contact_info_rec.act_info13;
3101 close csr_sars_loc_address;
3102
3103 -- Residential Address
3104 -- Fetch peron's address with address_style 'South Africa(SARS)' and address_type 'ZA-Residential'
3105 open csr_sars_address(l_person_id, l_effective_date, 'ZA_SARS', 'ZA_RES');
3106 fetch csr_sars_address into p_employee_contact_info_rec.act_info14
3107 , p_employee_contact_info_rec.act_info15
3108 , p_employee_contact_info_rec.act_info16
3109 , p_employee_contact_info_rec.act_info17
3110 , p_employee_contact_info_rec.act_info18
3111 , p_employee_contact_info_rec.act_info19
3112 , p_employee_contact_info_rec.act_info20;
3113 close csr_sars_address;
3114 hr_utility.set_location(l_proc,90);
3115
3116 -- Postal Address
3117 -- Fetch peron's Primary address with address_style 'South Africa'
3118 open csr_postal_address(l_person_id, l_effective_date);
3119 fetch csr_postal_address into p_employee_contact_info_rec.act_info21
3120 , p_employee_contact_info_rec.act_info22
3121 , p_employee_contact_info_rec.act_info23
3122 , p_employee_contact_info_rec.act_info24
3123 , p_employee_contact_info_rec.act_info25;
3124 close csr_postal_address;
3125
3126 hr_utility.set_location(l_proc,100);
3127
3128 p_employee_contact_info_rec.act_info26 := null; -- Main/Lumpsum certificate -- this will be populated in archive code
3129
3130 open csr_location_code(person_rec.location_id);
3131 fetch csr_location_code into l_location_code;
3132 close csr_location_code;
3133
3137
3134 p_employee_contact_info_rec.act_info27 := l_location_code; -- Location Code
3135
3136 hr_utility.set_location(l_proc,110);
3138 -- Set Output parameters
3139 p_assignment_id := l_assignment_id;
3140 p_person_id := l_person_id;
3141 p_foreign_income := asg_rec.foreign_income;
3142 pactid := asg_rec.payroll_action_id;
3143 p_nature := asg_rec.nature;
3144 p_independent_contractor := asg_rec.independent_contractor;
3145 p_labour_broker := asg_rec.labour_broker;
3146
3147 hr_utility.set_location('Leaving '||l_proc,999);
3148 end fetch_person_data;
3149
3150
3151
3152 -----------------------------------------------------------------------------------------
3153 -- Function to populate t_code with values for
3154 -- income/lumpsum/deduction/gross codes for this assignment
3155 -- Input parameter - 1) Assignment action id of last payroll run
3156 -- 2) t_dir_num (List of directive numbers for this assignment)
3157 -- Output parameter - t_code (PL/SQL table populated with values for various code for this assignment)
3158 -----------------------------------------------------------------------------------------
3159 procedure fetch_balances ( p_assignment_action_id in number
3160 , t_dir_num in dir_num_table
3161 , p_period_recon in varchar2
3162 , t_code out nocopy code_table
3163 ) is
3164 l_def_bal_count number := 1;
3165 l_code varchar2(100);
3166 l_prev_code varchar2(100);
3167 l_total number := 0;
3168 l_dir_num varchar2(100);
3169 l_def_bal_id number;
3170 l_gross_rfi number := 0;
3171 l_gross_nrfi number := 0;
3172 l_gross_txble_income number := 0;
3173 l_gross_non_txble_income number := 0;
3174 l_gross_pkg number := 0;
3175 l_gross_deduction number := 0;
3176 l_gross_non_txble_lmpsm number := 0;
3177 l_gross_rfi_lmpsm number := 0;
3178 l_gross_income_lmpsm number := 0;
3179 l_value number := 0;
3180 l_tax number := 0;
3181 l_paye number := 0;
3182 l_taxbale_pkg_cmp number := 0;
3183 l_annual_taxbale_pkg_cmp number := 0;
3184 l_proc varchar2(100) := g_package||'fetch_balances';
3185 l_context_lst pay_balance_pkg.t_context_tab; -- used for batch balance retrieval
3186 l_output_table pay_balance_pkg.t_detailed_bal_out_tab; -- output of batch balance retrieval
3187 l_site number := 0; -- 9877034 fix
3188 l_period_recon varchar2(30); -- 9877034 fix
3189 l_leg_param varchar2(1000); -- 9877034 fix
3190
3191 -- Bug 13717246
3192 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
3193
3194 l_rfi_override number := 0;
3195
3196 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
3197
3198 -- Bug 14376752
3199 l_dir_num_sdl varchar2(100);
3200 -- Bug 14376752
3201 begin
3202 hr_utility.set_location('Entering '||l_proc,10);
3203
3204
3205
3206 l_period_recon := p_period_recon ; -- 9901425 fix
3207
3208 ----------------------------------------------------------------------------------------
3209 -- Fetch Normal income/deduction codes
3210 ----------------------------------------------------------------------------------------
3211 -- set context list
3212 l_context_lst(1).tax_unit_id :=null;
3213 l_context_lst(1).jurisdiction_code :=null;
3214 l_context_lst(1).source_id :=null;
3215 l_context_lst(1).source_text :=null;
3216 l_context_lst(1).source_number :=null;
3217 l_context_lst(1).source_text2 :=null;
3218 l_context_lst(1).time_def_id :=null;
3219 l_context_lst(1).balance_date :=null;
3220 l_context_lst(1).local_unit_id :=null;
3221 l_context_lst(1).source_number2 :=null;
3222 l_context_lst(1).organization_id :=null;
3223
3224 -- Fetch values for Normal Income codes using batch balance retrieval
3225 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id
3226 , p_defined_balance_lst => g_defined_balance_lst_normal
3227 , p_context_lst => l_context_lst
3228 , p_output_table => l_output_table
3229 );
3230
3231 hr_utility.set_location(l_proc,20);
3232
3233 -- Output table l_output_table gives balance values per defined balance id
3234 -- Loop through output_table to separate out values for each code
3235 for j in l_output_table.first .. l_output_table.last
3236 loop
3237 -- g_code_bal contains code details for each defined_balance_id
3238 l_def_bal_id := l_output_table(j).defined_balance_id;
3239
3240 for i in g_code_bal.first .. g_code_bal.last loop
3241 if g_code_bal(i).defined_balance_id = l_def_bal_id then
3242 l_code := g_code_bal(i).code;
3243 l_value := nvl(l_output_table(j).balance_value,0);
3244
3245 -- fix for bug 12981111 starts
3246 /*
3247 if l_code not in (4101,4102,4115,) then
3248 l_value := trunc(l_value);
3249 end if;
3250 */
3251 -- fix for bug 12981111 ends
3252
3253 hr_utility.trace('CODE : '||l_code||' VALUE : '||l_value);
3254
3255 -- Populate code and value in t_code table using index "code"
3256 if t_code.exists(l_code) then
3260 t_code(l_code).value := l_value;
3257 t_code(l_code).value := t_code(l_code).value + l_value;
3258 t_code(l_code).group_value := t_code(l_code).value;
3259 else
3261 t_code(l_code).group_value := t_code(l_code).value;
3262 end if;
3263
3264 -- fix for bug 12981111 starts
3265 end if;
3266 end loop;
3267
3268 for i in g_code_bal.first .. g_code_bal.last loop
3269 if g_code_bal(i).defined_balance_id = l_def_bal_id then
3270 l_code := g_code_bal(i).code;
3271 l_value := nvl(l_output_table(j).balance_value,0);
3272 -- Bug 13367825
3273 -- added to avoid 3615 being added to RFI/NRFI/PKG and Gross income for Tax year less than 2013
3274 if g_tax_year < 2013 then
3275 if l_code = 3615 then
3276 l_value := 0;
3277 end if;
3278 end if;
3279 -- Bug 13367825
3280 if l_code not in (4101,4102,4115,4116) then
3281 l_value := trunc(l_value);
3282 end if;
3283
3284 -- fix for bug 12981111 ends
3285
3286 -- Add amount to gross variables depending upon code subtypes
3287 if g_code_bal(i).sub_type in ('RFI') then
3288 l_gross_rfi := l_gross_rfi + l_value;
3289 end if;
3290 if g_code_bal(i).sub_type in ('PKG') then
3291 l_gross_pkg := l_gross_pkg + l_value;
3292 end if;
3293 if g_code_bal(i).sub_type in ('RFI','NRFI','PKG') then
3294 l_gross_txble_income := l_gross_txble_income + l_value;
3295 end if;
3296 if g_code_bal(i).sub_type in ('NON_TAXABLE') then
3297 l_gross_non_txble_income := l_gross_non_txble_income + l_value;
3298 end if;
3299 if g_code_bal(i).sub_type in ('DEDUCTION') then
3300 l_gross_deduction := l_gross_deduction + l_value;
3301 end if;
3302 if g_code_bal(i).full_balance_name = 'PAYE' then
3303 l_paye := l_value;
3304 end if ;
3305 /* Added Code for Aug submission 9877034 */
3306 if l_period_recon = '08' and l_code = '4101' then
3307 l_site := l_value;
3308 end if;
3309 /* End of adding code for Aug submission 9877034 */
3310
3311 end if;
3312 end loop;
3313 end loop;
3314
3315 l_tax := nvl(get_balance_value ('Tax','_ASG_TAX_YTD',p_assignment_action_id),0); --9901425 fix
3316
3317 -- Bug 13717246
3318 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
3319
3320 l_rfi_override := nvl(trunc(get_balance_value ('RFI Override SARS Reporting', '_ASG_TAX_YTD', p_assignment_action_id)), 0);
3321
3322 if l_rfi_override <> 0 then
3323 l_gross_rfi := l_rfi_override;
3324 end if;
3325
3326 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
3327
3328 /* Added Code for Aug submission 9877034 fix */
3329 /* RPahune for incorporating the change to incllude Tax for Aug Payroll if SITE PAYE split is not done.*/
3330
3331 if l_period_recon = '08' and l_paye = 0 and l_site = 0 then
3332 -- Populate code and value in t_code table using index "code"
3333 if t_code.exists(4102) then
3334 t_code(4102).value := t_code(4102).value + l_tax;
3335 t_code(4102).group_value := t_code(4102).value;
3336 else
3337 t_code(4102).value := l_tax;
3338 t_code(4102).group_value := t_code(l_code).value;
3339 end if;
3340 end if;
3341 /* Logic if it is Aug Run and there is SITE and PAYE are Zero
3342 Add Tax_ASG_TAX_YTD in code 4102 */
3343 /* End of added Code for Aug submission by RPahune on 07-Jul-2010 */
3344 -- set value for gross deduction
3345 t_code(4497).value := l_gross_deduction;
3346 t_code(4497).group_value := l_gross_deduction;
3347
3348 hr_utility.set_location(l_proc,30);
3349
3350 ----------------------------------------------------------------------------------------
3351 --- Fetch Lumpsum codes for all directive numbers (including 'To Be Advised')
3352 ----------------------------------------------------------------------------------------
3353 hr_utility.trace('Going for lumpsums .. ');
3354 l_dir_num := t_dir_num.first;
3355
3356 if l_dir_num is not null then
3357 loop
3358 hr_utility.trace('Directive Number : '||l_dir_num);
3359
3360 -- reset pl/sql tables, total and count variables
3361 l_gross_non_txble_lmpsm := 0;
3362 l_gross_rfi_lmpsm := 0;
3363 l_gross_income_lmpsm := 0;
3364 l_context_lst.delete;
3365 l_output_table.delete;
3366
3367 -- set context list
3368 l_context_lst(1).tax_unit_id :=null;
3369 l_context_lst(1).jurisdiction_code :=null;
3370 l_context_lst(1).source_id :=null;
3371 l_context_lst(1).source_text :=l_dir_num; -- directive number
3372 l_context_lst(1).source_number :=null;
3373 l_context_lst(1).source_text2 :=null;
3374 l_context_lst(1).time_def_id :=null;
3375 l_context_lst(1).balance_date :=null;
3376 l_context_lst(1).local_unit_id :=null;
3377 l_context_lst(1).source_number2 :=null;
3378 l_context_lst(1).organization_id :=null;
3379
3380 -- Fetch values for Lumpsum codes using batch balance retrieval
3381 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id
3382 ,p_defined_balance_lst => g_defined_balance_lst_lmpsm
3383 ,p_context_lst => l_context_lst
3384 ,p_output_table => l_output_table
3385 );
3386
3387 hr_utility.set_location(l_proc,40);
3388
3389 -- Output table l_output_table gives balance values per defined balance id
3390 -- Loop through output_table to separate out values for each code
3391 for j in l_output_table.first .. l_output_table.last
3392 loop
3393 -- g_code_bal contains code details for each defined_balance_id
3394 l_def_bal_id := l_output_table(j).defined_balance_id;
3395
3396 for i in g_code_bal.first .. g_code_bal.last loop
3397 if g_code_bal(i).defined_balance_id = l_def_bal_id then
3398 l_code := g_code_bal(i).code;
3399 l_value := nvl(l_output_table(j).balance_value,0);
3400 if l_code not in (4101,4102,4115,4116) then
3401 l_value := trunc(l_value);
3402 end if;
3403 hr_utility.trace('CODE : '||l_code||' VALUE : '||l_value);
3404
3405 -- Populate code and value in t_code table using index "code-dirnum"
3406 if t_code.exists(l_code||'-'||l_dir_num) then
3407 t_code(l_code||'-'||l_dir_num).value := t_code(l_code||'-'||l_dir_num).value + l_value;
3408 t_code(l_code||'-'||l_dir_num).group_value := t_code(l_code||'-'||l_dir_num).value;
3409 else
3410 t_code(l_code||'-'||l_dir_num).value := l_value;
3411 t_code(l_code||'-'||l_dir_num).group_value := t_code(l_code||'-'||l_dir_num).value;
3412 end if;
3413
3414 -- Add amount to gross variables depending upon code subtypes
3415 -- 2013 TAX YEAR CHANGES.
3416
3417 if g_code_bal(i).sub_type in ('NON_TAXABLE_LUMPSUM') then
3418 l_gross_non_txble_lmpsm := l_gross_non_txble_lmpsm + l_value;
3419 end if;
3420
3421 -- 2013 TAX YEAR CHANGES.
3422
3423 if g_code_bal(i).sub_type in ('RFI_LUMPSUM') then
3424 l_gross_rfi_lmpsm := l_gross_rfi_lmpsm + l_value;
3425 end if;
3426
3427 if g_code_bal(i).sub_type in ('RFI_LUMPSUM','LUMPSUM') then
3428 l_gross_income_lmpsm := l_gross_income_lmpsm + l_value;
3429 end if;
3430 end if;
3431 end loop;
3432 end loop;
3433
3434 hr_utility.set_location(l_proc,50);
3435 -- 2013 TAX YEAR CHANGES.
3436 t_code(3696||'-'||l_dir_num).value := l_gross_non_txble_lmpsm ;
3437 t_code(3696||'-'||l_dir_num).group_value := l_gross_non_txble_lmpsm ;
3438 -- 2013 TAX YEAR CHANGES.
3439 t_code(3697||'-'||l_dir_num).value := l_gross_rfi_lmpsm ;
3440 t_code(3697||'-'||l_dir_num).group_value := l_gross_rfi_lmpsm ;
3441 t_code(3698||'-'||l_dir_num).value := l_gross_income_lmpsm - l_gross_rfi_lmpsm;
3442 t_code(3698||'-'||l_dir_num).group_value := l_gross_income_lmpsm - l_gross_rfi_lmpsm;
3443
3444 -- Go for next directive number
3448 end if;
3445 l_dir_num := t_dir_num.next(l_dir_num);
3446 exit when l_dir_num is null;
3447 end loop;
3449
3450 hr_utility.set_location(l_proc,60);
3451 hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3452
3453 -- add 'Taxable Package Components RFI' and 'Annual Taxable Package Components RFI' to gross_rfi
3454
3455 -- Need to do the below calc only if users have not over ride the RFI
3456
3457 IF l_rfi_override = 0 then
3458
3459 l_taxbale_pkg_cmp := trunc(nvl(get_balance_value ('Taxable Package Components RFI','_ASG_TAX_YTD',p_assignment_action_id),0));
3460 l_annual_taxbale_pkg_cmp := trunc(nvl(get_balance_value ('Annual Taxable Package Components RFI','_ASG_TAX_YTD',p_assignment_action_id),0));
3461
3462 /* There might be cases like
3463 l_gross_rfi = 200
3464 l_taxbale_pkg_cmp = 50
3465 l_annual_taxbale_pkg_cmp = 0
3466 l_gross_txble_income = 249
3467
3468 if we simple calcuate l_gross_nrfi = l_gross_txble_income - (l_gross_rfi + l_taxbale_pkg_cmp)
3469 then we will have -ve values in code 3698
3470
3471 Hence we have to take care that if after summing up Taxable Package Comp RFI and Ann. Txble Pckg Comp to l_groo_rfi
3472 the value exceeds l_gross_txble_income, then explicitly set l_gross_nrfi = 0 and take the l_gross_txble_income value
3473 value for l_gross_rfi
3474
3475 This case will only come up when 100% of PKG goes into RFI, which should be a rare scenario
3476 */
3477 if (l_gross_rfi + l_taxbale_pkg_cmp + l_annual_taxbale_pkg_cmp) > l_gross_txble_income then
3478 l_gross_rfi := l_gross_txble_income;
3479 l_gross_nrfi := 0;
3480 else
3481 l_gross_rfi := l_gross_rfi + l_taxbale_pkg_cmp + l_annual_taxbale_pkg_cmp ;
3482 l_gross_nrfi := l_gross_txble_income - l_gross_rfi;
3483 end if;
3484
3485
3486
3487 hr_utility.set_location ('l_taxbale_pkg_cmp = '|| l_taxbale_pkg_cmp,60);
3488 hr_utility.set_location ('l_annual_taxbale_pkg_cmp = '|| l_annual_taxbale_pkg_cmp,60);
3489 hr_utility.set_location ('l_gross_rfi = '|| l_gross_rfi,60);
3490 hr_utility.set_location ('l_gross_txble_income = '|| l_gross_txble_income,60);
3491 hr_utility.set_location ('l_gross_nrfi = '|| l_gross_nrfi,60);
3492
3493 else
3494 l_gross_nrfi := l_gross_txble_income - l_gross_rfi;
3495 end if;
3496 -- Need to do the above calc only if users have not over ride the RFI
3497
3498 t_code(3696).value := l_gross_non_txble_income;
3499 t_code(3696).group_value := l_gross_non_txble_income;
3500 t_code(3697).value := l_gross_rfi;
3501 t_code(3697).group_value := l_gross_rfi;
3502 t_code(3698).value := l_gross_nrfi;
3503 t_code(3698).group_value := l_gross_nrfi;
3504 t_code(9997).value := l_tax; -- Archive Tax_ASG_TAX_YTD - for in exception log calcualtions
3505 t_code(9997).group_value := l_tax;
3506 t_code(9998).value := l_paye; -- Archive PAYE_ASG_TAX_YTD- for in exception log calcualtions
3507 t_code(9998).group_value := l_paye;
3508 t_code(9999).value := l_gross_pkg; -- Archive Gross PKG - for in exception log calcualtions
3509 t_code(9999).group_value := l_gross_pkg;
3510
3511 -- UIF Conributions
3512 hr_utility.set_location(l_proc,70);
3513 t_code(4141).value := nvl(get_balance_value ('UIF Employee Contribution', '_ASG_TAX_YTD', p_assignment_action_id),0)
3514 + nvl(get_balance_value ('UIF Employer Contribution', '_ASG_TAX_YTD', p_assignment_action_id),0);
3515 t_code(4141).group_value := t_code(4141).value;
3516
3517 -- SDL Conributions
3518 -- Bug 14376752
3519
3520 t_code(4142).value := nvl(get_balance_value ('Skills Levy NE', '_ASG_TAX_YTD', p_assignment_action_id),0);
3521 t_code(4142).group_value := t_code(4142).value;
3522
3523 hr_utility.trace('Going for lumpsums for SDL .. ');
3524 l_dir_num_sdl := t_dir_num.first;
3525
3526 if l_dir_num_sdl is not null then
3527 loop
3528 hr_utility.trace('Directive Number : '||l_dir_num_sdl);
3529
3530 -- reset pl/sql tables, total and count variables
3531 l_context_lst.delete;
3532
3533 -- set context list
3534 pay_balance_pkg.set_context('SOURCE_TEXT',l_dir_num_sdl);
3535
3536 -- fetch SDL for the dir num
3537 t_code(4142||'-'||l_dir_num_sdl).value := nvl(get_balance_value('Skills Levy LS', '_ASG_LMPSM_TAX_YTD' ,p_assignment_action_id),0);
3538 t_code(4142||'-'||l_dir_num_sdl).group_value := t_code(4142||'-'||l_dir_num_sdl).value;
3539
3540
3541 -- Go for next directive number
3542 l_dir_num_sdl := t_dir_num.next(l_dir_num_sdl);
3543 exit when l_dir_num_sdl is null;
3544 end loop;
3545 end if;
3546
3547 -- Bug 14376752
3548
3549 hr_utility.set_location('Leaving '||l_proc,999);
3550
3551 end fetch_balances;
3552
3553
3554 ---------------------------------------------------------------------------
3555 -- This function is used to identify certificate types (IRP5/ IT3A/ ITREG)
3556 -- Output -
3557 -- 1) p_main_cert_type (type of main certificate)
3558 -- 2) t_dir_num : populates the field 'certificate_type' for each directive number
3559 ---------------------------------------------------------------------------
3560 procedure populate_irp5_indicators
3561 (
3562 p_run_assact_id in number
3563 , t_code in code_table
3564 , p_main_cert_type out nocopy varchar2
3565 , t_dir_num in out nocopy dir_num_table
3566 ) is
3567
3568 l_dir_num varchar2(100);
3569 l_site number(15, 3);
3570 l_paye_plus_vol_tax number(15, 3);
3571 l_total_tax number;
3572 l_lmpsm_sum number;
3573 l_total_income number := 0;
3574 l_all_lumpsum_income number := 0;
3575 l_lumpsum_income number := 0;
3576 l_main_cert_income number := 0;
3577 l_deductions number := 0;
3578 l_proc varchar2(100) := g_package||'populate_irp5_indicators';
3579 l_4115_ToBeAdvised number(15, 3) := 0;
3580 begin
3581 hr_utility.set_location('Entering '||l_proc,10);
3582 -----------------------------------------
3583 -- Checking type of Lumpsum Certificates
3584 -----------------------------------------
3585 l_dir_num := t_dir_num.first;
3586 if l_dir_num is not null then
3587 loop
3588 hr_utility.set_location('Directive Num '||l_dir_num,20);
3589 if l_dir_num <> 'To Be Advised' then
3590 l_total_tax := t_code('4102-'||l_dir_num).group_value
3591 + t_code('4115-'||l_dir_num).group_value;
3592 l_lumpsum_income := t_code('3696-'||l_dir_num).group_value
3593 + t_code('3697-'||l_dir_num).group_value
3594 + t_code('3698-'||l_dir_num).group_value;
3595
3596 hr_utility.set_location('Total Tax '||l_total_tax,20);
3597 hr_utility.set_location('l_lumpsum_income '||l_lumpsum_income,20);
3598
3599 if l_lumpsum_income<=0 then
3600 -- If the assignment had zero for all his balances then don't include him
3601 t_dir_num(l_dir_num).certificate_type := 'A';
3602 else -- Check for IRP5/IT3A
3603 if l_total_tax > 0 then
3604 t_dir_num(l_dir_num).certificate_type := 'IRP5';
3605 else
3606 t_dir_num(l_dir_num).certificate_type := 'IT3A';
3607 end if;
3608 end if;
3609
3610 l_all_lumpsum_income := l_all_lumpsum_income + l_lumpsum_income;
3611 end if;
3612 hr_utility.set_location('Cert type for '||l_dir_num||' is ' || t_dir_num(l_dir_num).certificate_type,10);
3613 l_dir_num := t_dir_num.next(l_dir_num);
3614 exit when l_dir_num is null;
3615 end loop;
3616 end if;
3617
3618 -------------------------------------
3619 -- Checking type of main certificate
3620 -------------------------------------
3621 hr_utility.set_location(l_proc,30);
3622 l_site := t_code('4101').group_value;
3623
3624 if t_code.exists('4102-To Be Advised') then
3625 l_paye_plus_vol_tax := t_code('4102').group_value + t_code('4102-To Be Advised').group_value;
3626 l_4115_ToBeAdvised := t_code('4115-To Be Advised').group_value;
3627 else
3628 l_paye_plus_vol_tax := t_code('4102').group_value;
3629 end if;
3630
3631 -- Total Tax paid on main certificate
3632 l_total_tax := l_paye_plus_vol_tax + l_site + l_4115_ToBeAdvised;
3633
3634 hr_utility.set_location('l_site '||l_site,20);
3635 hr_utility.set_location('l_paye_plus_vol_tax '||l_paye_plus_vol_tax,20);
3636 hr_utility.set_location('l_4115_ToBeAdvised '||l_4115_ToBeAdvised,20);
3637 hr_utility.set_location('l_total_tax '||l_total_tax,20);
3638 hr_utility.set_location('l_main_cert_income '||l_main_cert_income,20);
3639 hr_utility.set_location('l_deductions '||l_deductions,20);
3640
3641 -- Total Main certificate income
3642 if t_code.exists('3697-To Be Advised') then
3643 l_main_cert_income := t_code(3696).group_value
3644 + t_code(3697).group_value
3645 + t_code(3698).group_value
3646 + t_code(3696||'-To Be Advised').group_value
3647 + t_code(3697||'-To Be Advised').group_value
3648 + t_code(3698||'-To Be Advised').group_value;
3649 else
3650 l_main_cert_income := t_code(3696).group_value
3651 + t_code(3697).group_value
3652 + t_code(3698).group_value;
3653 end if;
3654
3655 -- Total Main certificate deductions
3656 l_deductions := t_code(4497).group_value;
3657
3658 hr_utility.set_location(l_proc,40);
3659 if l_main_cert_income<=0 and l_deductions<=0 then
3660 -- If the assignment had zero for all his balances
3661 -- then don't include him
3662 p_main_cert_type := 'A';
3663 else -- Check for IRP5/IT3A
3664 if l_total_tax > 0 then
3665 p_main_cert_type := 'IRP5';
3666 else
3667 p_main_cert_type := 'IT3A';
3668 end if;
3669 end if;
3670 hr_utility.set_location('Cert type for Main Certificate is ' || p_main_cert_type,50);
3671 hr_utility.set_location('Leaving '||l_proc,999);
3672 end populate_irp5_indicators;
3673
3674
3675 -----------------------------------------------------------------------------------------
3676 -- Procudure to identify which lumpsum certificates can be merged with main certificate
3677 -- Inputs -
3678 -- 1) p_main_cert_type (Main certificate type
3679 -- 2) p_main cert_dir_num ( Main certificate directive Number - given in ZA_TAX element)
3680 -- Outputs -
3681 -- 1) p_directive_1 (Directive Number of first merged lumpsum certificate)
3682 -- 2) p_directive_2 (Directive Number of second merged lumpsum certificate)
3683 -- 3) p_directive_3 (Directive Number of third merged lumpsum certificate)
3684 -- In/Out -
3685 -- 1) t_dir_num (Directive Number table
3686 -- for directive numbers which are merged with main certificate
3687 -- set attribute certificate_merged_with_main = 'Y'
3688 --
3689 -- Note - If there is a main certificate number given in ZA_TAX element
3690 -- Then this will be the first merged directive number
3691 -- and we can have only 2 further lumpsum certificates merged
3692 -----------------------------------------------------------------------------------------
3693 procedure combine_certificates(p_main_cert_type in varchar2
3694 , p_main_cert_dir_num in varchar2
3695 , t_code in code_table
3696 , t_dir_num in out nocopy dir_num_table
3697 , t_lump_dir_set out nocopy lump_dir_set_tab
3698 , p_directive_1 out nocopy varchar2
3699 , p_directive_2 out nocopy varchar2
3700 , p_directive_3 out nocopy varchar2) is
3701 l_combined_cert_count number := 0;
3702 l_dir_num varchar2(100);
3703 l_proc varchar2(100) := g_package || 'combine_certificates';
3704
3705 l_main_inc_code_count number;
3706 l_lump_inc_code_count number;
3707 l_dir_merge_count number;
3708 l_dir_count number;
3709 l_dir_set_count number;
3710 l_dir_set_rec lump_dir_set_rec;
3711
3712 t_inc_code code_table;
3713
3714 begin
3715 hr_utility.set_location('Entering '|| l_proc,10);
3716
3717 l_main_inc_code_count := get_main_inc_code_cnt(t_code,t_inc_code);
3718
3719 if t_dir_num.exists('To Be Advised') then
3720 t_dir_num('To Be Advised').certificate_merged_with_main := 'Y';
3721 end if;
3722 -- TODO need to chek if this is required or not
3723 l_lump_inc_code_count := get_lump_inc_code_cnt(t_code,p_main_cert_dir_num,t_inc_code);
3724 IF ((l_lump_inc_code_count) <=13) THEN
3725 if p_main_cert_dir_num is not null then
3726 hr_utility.set_location('Merging Main Dir with Main:'|| p_main_cert_dir_num,10);
3727 l_combined_cert_count:= 1;
3728 p_directive_1 := p_main_cert_dir_num;
3729 end if;
3730 END IF;
3731
3732 -- Lumpsum certificates will get merged in main certificate
3733 -- in order of their directive number names
3734 -- as we are looping through t_dir_num from first to last
3735 l_dir_num := t_dir_num.first;
3736 if l_dir_num is not null then
3737 loop
3738 if l_dir_num <> 'To Be Advised' then
3739 if t_dir_num(l_dir_num).certificate_type = p_main_cert_type then
3740
3741 l_lump_inc_code_count := get_lump_inc_code_cnt(t_code,l_dir_num,t_inc_code);
3742 IF ((l_lump_inc_code_count) <=13) THEN
3743 hr_utility.set_location('Merging Dir with Main:'|| l_dir_num,10);
3744 l_combined_cert_count := l_combined_cert_count + 1;
3745 t_dir_num(l_dir_num).certificate_merged_with_main := 'Y';
3746
3747 if l_combined_cert_count = 1 then
3748 p_directive_1 := l_dir_num;
3749 elsif l_combined_cert_count = 2 then
3750 p_directive_2 := l_dir_num;
3751 elsif l_combined_cert_count = 3 then
3752 p_directive_3 := l_dir_num;
3753 end if;
3754
3755 exit when l_combined_cert_count = 3;
3756 END IF;
3757 end if;
3758 end if;
3759
3760 l_dir_num := t_dir_num.next(l_dir_num);
3761 exit when l_dir_num is null;
3762 end loop;
3763 end if;
3764
3765 hr_utility.set_location('Directive Number 1 : '|| p_directive_1,20);
3766 hr_utility.set_location('Directive Number 2 : '|| p_directive_2,20);
3767 hr_utility.set_location('Directive Number 3 : '|| p_directive_3,20);
3768
3769 -----------------------------------------------------------------------
3770 -- Group the remaining Lumpsum Dir as set of 3 for IRP5,IT3A Seperately
3771 -----------------------------------------------------------------------
3772 l_dir_count :=0;
3773 l_dir_set_count :=1;
3774
3775 l_lump_inc_code_count :=0;
3776 t_inc_code.delete;
3777
3778
3779 FOR i in 1..2
3780 LOOP
3781 l_dir_num := t_dir_num.first;
3782 while l_dir_num is not null
3783 loop
3784 IF ((i = 1 AND t_dir_num(l_dir_num).certificate_type='IRP5') OR (i = 2 AND t_dir_num(l_dir_num).certificate_type='IT3A')) THEN
3785 hr_utility.set_location(l_dir_num||':'||t_dir_num(l_dir_num).certificate_type||':'||t_dir_num(l_dir_num).certificate_merged_with_main,60);
3786 IF t_dir_num(l_dir_num).certificate_merged_with_main is null THEN
3787 l_dir_count := l_dir_count + 1;
3788 l_lump_inc_code_count := get_lump_inc_code_cnt(t_code,l_dir_num,t_inc_code);
3789 IF (l_dir_count > 3 OR l_lump_inc_code_count > 13) THEN
3790 l_dir_set_count := l_dir_set_count + 1;
3791 l_dir_count :=1;
3792 END IF;
3793 IF (l_dir_count = 1) THEN
3794 t_lump_dir_set(l_dir_set_count).dir_1 := l_dir_num;
3795 ELSIF (l_dir_count = 2) THEN
3796 t_lump_dir_set(l_dir_set_count).dir_2 := l_dir_num;
3797 ELSIF (l_dir_count = 3) THEN
3798 t_lump_dir_set(l_dir_set_count).dir_3 := l_dir_num;
3799 END IF;
3800 END IF;
3801 END IF;
3802 l_dir_num := t_dir_num.next(l_dir_num);
3803 end loop; -- End While
3804 END LOOP; -- End For
3805
3806
3807 hr_utility.set_location('Leaving '|| l_proc,999);
3808 end combine_certificates;
3809
3810
3811
3812
3813 -----------------------------------------------------------------------------------------
3814 -- Procudure to merge code values of -
3815 -- 1) Codes whose values have been directed by SARS to be merged with other codes
3816 -- a) Codes 3603, 3607, 3610 to be merged into 3601.
3817 -- b) Codes 3604, 3609, 3612 to be merged into 3602.
3818 -- c) Codes 3706, 3710, 3711, 3712 to be merged into 3713.
3819 -- d) Codes 3705, 3709, 3716 to be merged into 3714.
3820 -- e) Codes 3803, 3804, 3805, 3806, 3807, 3808, 3809 to be merged into 3801.
3821 -- f) Codes 4004 to be merged into 4003.
3822 -- 2) All codes of lumpsum certificates which have been identified to be merged
3823 -- with main certificate
3824 -- 3) For codes 3907, 3696, 3697, and 3698
3825 -- both t_code(code) ad well as t_code(code-To Be Advised) might exist
3826 -- If so, then merge t_code(code-To Be Advised) into t_code(code)
3827 -- to avoid them from being reported twice
3828 --
3829 -- Inputs - t_dir_num (Directive Number table)
3830 -- In/Out - t_code (Code Values table for that assignment)
3831 --
3832 -- Note - When a code B is merged with code A -
3833 -- 1) t_code(B).group_value is added to t_code(A).group_value
3834 -- 2) t_code(B).included_in = A
3835 --
3836 -----------------------------------------------------------------------------------------
3837 procedure consolidate_codes(t_dir_num in out nocopy dir_num_table
3838 , t_code in out nocopy code_table
3839 , p_pre_merge in boolean ) as
3840
3841 l_dir_num varchar2(100);
3842 l_proc varchar2(100) := g_package||'consolidate_codes';
3843 l_code varchar2(4);
3844 l_code_complete varchar2(100);
3845 l_code_temp varchar2(4);
3846
3847 l_value number := 0;
3848 l_prev_tcode number := 0;
3849 l_gross_txble_income number := 0;
3850 l_gross_non_txble_income number := 0;
3851 l_gross_rfi number := 0;
3852 l_gross_nrfi number := 0;
3853
3854
3855 -- procedure to merge code_B into code_A
3856 procedure merge (code_B varchar2, code_A varchar2) is
3857 begin
3858 if t_code.exists(code_A) then
3859 t_code(code_A).group_value := t_code(code_A).group_value + t_code(code_B).group_value;
3860 else
3861 if substr(code_A,6) = 'To Be Advised' then
3862 -- Create new directive number 'To Be Advised'
3863
3864 t_dir_num('To Be Advised').certificate_type := null;
3865 t_dir_num('To Be Advised').certificate_merged_with_main := 'Y';
3866 l_code_temp := g_code_list.first;
3867 loop
3868 if g_code_list(l_code_temp).lumpsum = 'Y' then
3869 t_code(l_code_temp||'-To Be Advised').value := 0;
3870 t_code(l_code_temp||'-To Be Advised').group_value := 0;
3871 end if;
3872 l_code_temp := g_code_list.next(l_code_temp);
3873 exit when l_code_temp is null;
3874 end loop;
3875 -- 2013 TAX YEAR CHANGES.
3876 t_code('3696-To Be Advised').value := 0;
3877 t_code('3696-To Be Advised').group_value := 0;
3878 -- 2013 TAX YEAR CHANGES.
3879 t_code('3697-To Be Advised').value := 0;
3880 t_code('3697-To Be Advised').group_value := 0;
3881
3882 t_code('3698-To Be Advised').value := 0;
3883 t_code('3698-To Be Advised').group_value := 0;
3884
3885 -- Bug 14376752
3886 t_code('4142-To Be Advised').value := 0;
3887 t_code('4142-To Be Advised').group_value := 0;
3888 -- Bug 14376752
3889 -- Add code_B value to code_A
3890 t_code(code_A).value := 0;
3891 t_code(code_A).group_value := t_code(code_B).group_value;
3892
3893 end if;
3894 end if;
3895 t_code(code_B).included_in := code_A;
3896 end merge;
3897
3898 begin
3899 hr_utility.set_location('Entering '||l_proc,10);
3900
3901 ------------------------------------------------------------------------------
3902 -- 1) Codes whose values have been directed by SARS to be merged with other codes
3903 ------------------------------------------------------------------------------
3904
3905 -- TYE 2012 Changes
3906 -- Effective 01-Mar-2012 Report the following codes seperately
3907 -- 3603,3610,3805,3806,3808,3809
3908 IF (p_pre_merge) THEN
3909 IF (g_tax_year < 2013 ) THEN
3910 merge(3603,3601);
3911 merge(3610,3601);
3912 merge(3805,3801);
3913 merge(3806,3801);
3914 merge(3808,3801);
3915 merge(3809,3801);
3916 END IF;
3917 merge(3607,3601);
3918 merge(3604,3602);
3919 merge(3609,3602);
3920 merge(3612,3602);
3921 merge(3706,3713);
3922 merge(3710,3713);
3923 merge(3711,3713);
3924 merge(3712,3713);
3925 merge(3705,3714);
3926 merge(3709,3714);
3927 merge(3716,3714);
3928 merge(3803,3801);
3929 merge(3804,3801);
3930 merge(3807,3801);
3931 merge(4004,4003);
3932
3933 ELSE -- pre merge
3934
3935 hr_utility.set_location(l_proc,20);
3936 for i in g_code_bal.first .. g_code_bal.last loop
3937
3938 l_code := g_code_bal(i).code;
3939 if (t_code.exists(l_code) and l_prev_tcode <> l_code ) then
3940 l_value := t_code(l_code).group_value;
3941
3942 if l_code not in (4101,4102,4115,4116) then
3943 t_code(l_code).value := trunc(t_code(l_code).value);
3944 t_code(l_code).group_value := trunc(t_code(l_code).group_value);
3945 end if;
3946
3947 if (g_code_bal(i).sub_type in ('RFI','NRFI','PKG') and t_code(l_code).included_in is null) then
3948 l_gross_txble_income := l_gross_txble_income + t_code(l_code).group_value;
3949 end if;
3950 hr_utility.set_location('CODE : '||l_code||' GROUP VALUE : '||t_code(l_code).group_value,12);
3951 end if;
3952 l_prev_tcode := l_code;
3953 end loop;
3954
3955 hr_utility.set_location('l_gross_txble_income: '|| l_gross_txble_income,99);
3956
3957 if (t_code.exists(3697)) then
3958 l_gross_rfi := t_code(3697).group_value;
3959 end if;
3960
3961 if (t_code.exists(3698)) then
3962 t_code(3698).value := l_gross_txble_income - l_gross_rfi ;
3963 t_code(3698).group_value := l_gross_txble_income - l_gross_rfi ;
3964 end if;
3965
3966 hr_utility.set_location(l_proc,21);
3967
3968 ---------------------------------------------------------------------------------------
3969 -- 2) Codes of lumpsum certificates which have been identified to be merged with main cert
3970 ---------------------------------------------------------------------------------------
3971
3972 -- Loop through all directive numbers
3973 l_dir_num := t_dir_num.first;
3974 if l_dir_num is not null then
3975 loop
3976 -- if directive number has been identified to be merged with main certificate
3977 if t_dir_num(l_dir_num).certificate_merged_with_main = 'Y' and l_dir_num <> 'To Be Advised' then
3978 -- loop through all codes for this assignment
3979 l_code := g_code_list.first;
3980 loop
3981 -- if the code is a lumpsum code
3982 if g_code_list(l_code).lumpsum = 'Y' then
3983 -- merge t_code(code-dirnum) into t_code(code-To Be Advised)
3984 merge(l_code||'-'||l_dir_num , l_code||'-To Be Advised');
3985 end if;
3986 l_code := g_code_list.next(l_code);
3987 exit when l_code is null;
3988 end loop;
3989 -- merge 3696/3697/3698-dirnum into 3696/3697/3698-To Be Advised
3990 merge(3696||'-'||l_dir_num , 3696||'-To Be Advised');
3991 merge(3697||'-'||l_dir_num , 3697||'-To Be Advised');
3992 merge(3698||'-'||l_dir_num , 3698||'-To Be Advised');
3993 merge(4142||'-'||l_dir_num , 4142||'-To Be Advised'); -- Bug 14376752
3994 end if;
3995
3996 l_dir_num := t_dir_num.next(l_dir_num);
3997 exit when l_dir_num is null;
3998
3999 end loop;
4000 end if;
4001
4002 hr_utility.set_location(l_proc,30);
4003
4004 ---------------------------------------------------------------------------------------
4005 -- 3) For codes 3907, 3696,3697, and 3698
4006 -- Merge t_code(code-To Be Advised) into t_code(code)
4007 ---------------------------------------------------------------------------------------
4008 if t_code.exists('3907-To Be Advised') then
4009 merge('3907-To Be Advised',3907);
4010 merge('3696-To Be Advised',3696);
4011 merge('3697-To Be Advised',3697);
4012 merge('3698-To Be Advised',3698);
4013 merge('4142-To Be Advised',4142); -- Bug 14376752
4014 end if;
4015 END IF;
4016
4017
4018 hr_utility.set_location('Leaving '||l_proc,999);
4019 end consolidate_codes;
4020
4021
4022
4023
4024 -------------------------------------------
4025 ---Function to provide IT3A Reason code
4026 -------------------------------------------
4027 function it3a_reason_code( p_run_assact_id number
4028 , p_nature varchar2
4029 , p_tax_status varchar2
4030 , p_normal_directive_value varchar2
4031 , p_gross_total number
4032 , p_gross_non_txble_income number
4033 , p_lmpsm_cert varchar2
4034 , p_tax_on_lmpsm number
4035 , p_independent_contractor varchar2
4036 , p_foreign_income varchar2
4037 , p_labour_broker VARCHAR2
4038 , l_4116 number) return varchar2 is
4039 l_tax_threshold_ind number ;
4040 l_reason_code varchar2(2) := '&&';
4041 l_normal_directive_value number;
4042 l_proc varchar2(100) := g_package || 'it3a_reason_code';
4043 begin
4044 hr_utility.set_location('Entering '||l_proc,10);
4045 begin
4046 l_tax_threshold_ind := nvl(get_balance_value('Tax Threshold Ind','_ASG_TAX_YTD',p_run_assact_id),0);
4047 exception when others then
4048 l_tax_threshold_ind := 0;
4049 end ;
4050
4051 begin
4052 l_normal_directive_value := to_number(p_normal_directive_value);
4053 exception when others then
4054 l_normal_directive_value := 0;
4055 end ;
4056
4057 if nvl(l_4116,0) > 0 then
4058 l_reason_code := '08';
4059 end if;
4060
4061 if p_gross_total = 0 and p_gross_non_txble_income >0 then
4062 l_reason_code := '04';
4063 end if;
4064
4065 if p_tax_status = 'H' then
4066 l_reason_code := '04';
4067 end if;
4068
4069 if (p_tax_status = 'C' or p_tax_status = 'D') and l_normal_directive_value = 0 then
4070 l_reason_code := '04';
4071 end if;
4072
4073 if l_reason_code = '&&' and l_tax_threshold_ind > 0 then
4074 l_reason_code := '02';
4075 end if;
4076
4077 if p_independent_contractor = 'Y' then
4078 l_reason_code := '03';
4079 end if;
4080
4081 if p_foreign_income = 'Y' then
4082 l_reason_code := '05';
4083 end if;
4084
4085 if p_tax_status = 'Q' then
4086 l_reason_code := '06';
4087 end if;
4088
4089 if l_reason_code = '06' and l_tax_threshold_ind > 0 then
4090 l_reason_code := '02';
4091 end if;
4092
4093 if l_reason_code = '&&' then
4094 l_reason_code := '02';
4095 end if;
4096
4097 if p_labour_broker = 'Y' then
4098 l_reason_code := '07';
4099 end if;
4100
4101 if p_lmpsm_cert = 'Y' then
4102 if p_tax_on_lmpsm <= 0 then
4103 l_reason_code := '04';
4104 end if;
4105 end if;
4106
4107 hr_utility.set_location('IT3A Reason Code : '||l_reason_code,20);
4108
4109 hr_utility.set_location('Leaving '||l_proc,999);
4110 return l_reason_code;
4111 end it3a_reason_code;
4112
4113
4114
4115
4116 -----------------------------------------------------
4117 -- function to return final code to be archived
4118 -----------------------------------------------------
4119 function final_code (p_tax_year in varchar2 -- Bug 13367825
4120 , p_code_complete in varchar2
4121 , p_nature in varchar2
4122 , p_tax_status in varchar2
4123 , p_foreign_income in varchar2
4124 ) return varchar2 is
4125 l_code varchar2(4);
4126 begin
4127 l_code := to_number(substr(p_code_complete,1,4));
4128
4129 -- For Director of private company/ Member of close corporation
4130 -- the income under 3601 needs to be reported under 3615
4131 -- Bug 13367825
4132 -- for Tax Director Remuneration Changes
4133
4134 if(p_tax_year <= 2012 ) then
4135 if (p_nature = 'C' and l_code = '3601') then
4136 l_code := '3615';
4137 end if;
4138 end if;
4139 -- Bug 13367825
4140 -- Check for foreign income code
4141 if (p_foreign_income = 'Y' and to_number(l_code) >= 3601 and to_number(l_code) <= 3907
4142 and to_number(l_code) not in (3614,3908,3909,3915,3920,3921,3922
4143 ,3696, 3697, 3698))
4144 then
4145 l_code := to_char(l_code + 50);
4146 end if;
4147
4148 return l_code;
4149 end final_code;
4150
4151
4152
4153
4154 ------------------------------------------------------------------------------
4155 -- Procedure to fetch and return Home Phone, Work Phone, Fax and Cell Number
4156 -- of the person
4157 ------------------------------------------------------------------------------
4158 procedure get_phones (p_person_id number
4159 , p_effective_date date
4160 , p_home_phone out nocopy varchar2
4161 , p_work_phone out nocopy varchar2
4162 , p_fax out nocopy varchar2
4163 , p_cell_number out nocopy varchar2 ) is
4164 cursor csr_phones (p_phone_type varchar2) is
4165 select translate(upper(phone_number),
4166 '0123456789+-. ',
4167 '0123456789') -- remove any character other than digits
4168 from per_phones
4169 where parent_table = 'PER_ALL_PEOPLE_F'
4170 and parent_id = p_person_id
4171 and phone_type = p_phone_type
4172 and p_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY')) ;
4173 l_temp number;
4174 begin
4175 -- Home Phone
4176 open csr_phones('H1');
4177 fetch csr_phones into p_home_phone;
4178 close csr_phones;
4179
4180 if p_home_phone is null then
4181 open csr_phones('H2');
4182 fetch csr_phones into p_home_phone;
4183 close csr_phones;
4184
4185 if p_home_phone is null then
4186 open csr_phones('H3');
4187 fetch csr_phones into p_home_phone;
4188 close csr_phones;
4189 end if;
4190 end if ;
4191
4192 --
4193 -- Business Phone
4194 --
4195 open csr_phones('W1');
4196 fetch csr_phones into p_work_phone;
4197 close csr_phones;
4198
4199 if p_work_phone is null then
4200 open csr_phones('W2');
4201 fetch csr_phones into p_work_phone;
4202 close csr_phones;
4203
4204 if p_work_phone is null then
4205 open csr_phones('W3');
4206 fetch csr_phones into p_work_phone;
4207 close csr_phones;
4208 end if;
4209 end if ;
4210
4211 --
4212 -- Fax
4213 --
4214 open csr_phones('WF');
4215 fetch csr_phones into p_fax;
4216 close csr_phones;
4217
4218 if p_fax is null then
4219 open csr_phones('HF');
4220 fetch csr_phones into p_fax;
4221 close csr_phones;
4222 end if;
4223
4224 --
4225 -- Mobile
4226 --
4227 open csr_phones('M');
4228 fetch csr_phones into p_cell_number;
4229 close csr_phones;
4230
4231 end get_phones;
4232
4233
4234
4235
4236
4237 -------------------------------------------------------------------------
4238 -- Procedure to create a copy of an archive record
4239 -------------------------------------------------------------------------
4240
4241 procedure copy_record(from_rec in act_info_rec
4242 ,to_rec in out nocopy act_info_rec) is
4243 begin
4244 to_rec.assignment_id := from_rec.assignment_id ;
4245 to_rec.person_id := from_rec.person_id ;
4246 to_rec.action_info_category := from_rec.action_info_category;
4247 to_rec.act_info1 := from_rec.act_info1 ;
4248 to_rec.act_info2 := from_rec.act_info2 ;
4249 to_rec.act_info3 := from_rec.act_info3 ;
4250 to_rec.act_info4 := from_rec.act_info4 ;
4251 to_rec.act_info5 := from_rec.act_info5 ;
4252 to_rec.act_info6 := from_rec.act_info6 ;
4253 to_rec.act_info7 := from_rec.act_info7 ;
4254 to_rec.act_info8 := from_rec.act_info8 ;
4255 to_rec.act_info9 := from_rec.act_info9 ;
4256 to_rec.act_info10 := from_rec.act_info10 ;
4257 to_rec.act_info11 := from_rec.act_info11 ;
4258 to_rec.act_info12 := from_rec.act_info12 ;
4259 to_rec.act_info13 := from_rec.act_info13 ;
4260 to_rec.act_info14 := from_rec.act_info14 ;
4261 to_rec.act_info15 := from_rec.act_info15 ;
4262 to_rec.act_info16 := from_rec.act_info16 ;
4263 to_rec.act_info17 := from_rec.act_info17 ;
4264 to_rec.act_info18 := from_rec.act_info18 ;
4265 to_rec.act_info19 := from_rec.act_info19 ;
4266 to_rec.act_info20 := from_rec.act_info20 ;
4267 to_rec.act_info21 := from_rec.act_info21 ;
4268 to_rec.act_info22 := from_rec.act_info22 ;
4269 to_rec.act_info23 := from_rec.act_info23 ;
4270 to_rec.act_info24 := from_rec.act_info24 ;
4271 to_rec.act_info25 := from_rec.act_info25 ;
4272 to_rec.act_info26 := from_rec.act_info26 ;
4273 to_rec.act_info27 := from_rec.act_info27 ;
4274 to_rec.act_info28 := from_rec.act_info28 ;
4275 to_rec.act_info29 := from_rec.act_info29 ;
4276 to_rec.act_info30 := from_rec.act_info30 ;
4277 end copy_record;
4278
4279
4280
4281
4282
4283 -------------------------------------------------------------------------
4284 --- This function returns defined_balance_id for a balance and dimenesion
4285 -------------------------------------------------------------------------
4286 function get_def_bal_id (p_bal_type_id number,
4287 p_dim_name varchar2) return number is
4288 cursor c_get_def_bal_id is
4289 select pdb.defined_balance_id
4290 from pay_balance_dimensions pbd
4291 , pay_defined_balances pdb
4292 where pbd.dimension_name = p_dim_name
4293 and pbd.legislation_code = 'ZA'
4294 and pdb.balance_type_id = p_bal_type_id
4295 and pdb.balance_dimension_id = pbd.balance_dimension_id;
4296
4297 l_def_bal_id number;
4298 begin
4299 open c_get_def_bal_id;
4300 fetch c_get_def_bal_id into l_def_bal_id ;
4301 close c_get_def_bal_id ;
4302
4303 return l_def_bal_id;
4304 end get_def_bal_id;
4305
4306
4307
4308
4309 -----------------------------------------------------------------
4310 -- Function to get balance Value
4311 ------------------------------------------------------------------
4312 function get_balance_value (p_bal_name varchar2,
4313 p_dim_name varchar2,
4314 p_asg_act_id number)
4315 return number is
4316 cursor csr_bal_id (p_bal_name varchar2)is
4317 select balance_type_id
4318 from pay_balance_types
4319 where balance_name = p_bal_name
4320 and legislation_code = 'ZA';
4321
4322 cursor c_get_bal_value( p_def_bal_id in number) is
4323 select pay_balance_pkg.get_value(p_def_bal_id, --p_def_bal_id
4324 p_asg_act_id, --assignment_action_id
4325 null,
4326 null,
4327 null,
4328 null,
4329 null,
4330 null,
4331 null,
4332 'TRUE')
4333 from dual;
4334
4335 l_def_bal_id number;
4336 l_bal_val number;
4337 l_bal_id number;
4338 begin
4339 open csr_bal_id(p_bal_name);
4340 fetch csr_bal_id into l_bal_id;
4341 close csr_bal_id;
4342
4343 l_def_bal_id := get_def_bal_id (l_bal_id, p_dim_name );
4344
4345 open c_get_bal_value(l_def_bal_id);
4346 fetch c_get_bal_value into l_bal_val;
4347 close c_get_bal_value;
4348
4349 return fnd_number.canonical_to_number(l_bal_val);
4350 end get_balance_value;
4351
4352
4353
4354
4355 /*--------------------------------------------------------------------------
4356 Name : get_parameter
4357 Purpose : Returns a legislative parameter
4358 Arguments :
4359 Notes : The legislative parameter field must be of the form:
4360 PARAMETER_NAME=PARAMETER_VALUE. No spaces is allowed in either
4361 the PARAMETER_NAME or the PARAMETER_VALUE.
4362 --------------------------------------------------------------------------*/
4363 function get_parameter
4364 (
4365 name in varchar2,
4366 parameter_list varchar2
4367 ) return varchar2 is
4368
4369 start_ptr number;
4370 end_ptr number;
4371 token_val pay_payroll_actions.legislative_parameters%type;
4372 par_value pay_payroll_actions.legislative_parameters%type;
4373
4374 begin
4375
4376 token_val := name || '=';
4377
4378 start_ptr := instr(parameter_list, token_val) + length(token_val);
4379 end_ptr := instr(parameter_list, ' ', start_ptr);
4380
4381 /* if there is no spaces, then use the length of the string */
4382 if end_ptr = 0 then
4383 end_ptr := length(parameter_list) + 1;
4384 end if;
4385
4386 /* Did we find the token */
4387 if instr(parameter_list, token_val) = 0 then
4388 par_value := NULL;
4389 else
4390 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
4391 end if;
4392
4393 return par_value;
4394
4395 end get_parameter;
4396
4397
4398
4399
4400 ----------------------------------------------------------------------------
4401 --- Function to return first two names
4402 ----------------------------------------------------------------------------
4403 function names(name varchar2) return varchar2 is
4404
4405 l_pos number;
4406 l_pos2 number;
4407 l_name varchar2(255);
4408 l_answer varchar2(255);
4409
4410 begin
4411
4412 -- Remove any unnecessary spaces
4413 l_name := ltrim(rtrim(name));
4414
4415 -- Get the first name
4416 l_pos := instr(l_name, ',', 1, 1);
4417 l_answer := rtrim(substr(l_name, 1, l_pos - 1));
4418
4419 -- Append the second name
4420 l_pos2 := instr(l_name, ',', l_pos + 1, 1);
4421 if l_pos2 = 0 then
4422
4423 -- Concatenate the rest of the string
4424 l_answer := l_answer || ' ' || ltrim(rtrim( substr(l_name, l_pos + 1) ));
4425
4426 else
4427
4428 -- Concatenate the name up to the comma
4429 l_answer := l_answer || ' ' || ltrim(rtrim( substr(l_name, l_pos + 1, l_pos2 - l_pos - 1) ));
4430
4431 end if;
4432
4433 l_answer := ltrim(rtrim(l_answer));
4434
4435 return l_answer;
4436
4437 end names;
4438
4439
4440
4441
4442 --------------------------------------------------------------------------------------------
4443 -- This function is used to return the initials of the employee
4444 -- Note: initials('Francois, Daniel, van der Merwe') would return 'FDV'
4445 -- Note: A maximum of five characters is returned
4446 --------------------------------------------------------------------------------------------
4447 function initials(name varchar2) return varchar2 is
4448
4449 l_initials varchar2(255);
4450 l_pos number;
4451 l_name varchar2(255);
4452 l_trc_initial varchar2(4);
4453
4454 begin
4455
4456 -- Get the first initial
4457 l_name := rtrim(ltrim(name));
4458 -- replace all apostrophe with null
4459 l_name := translate(l_name,'@''','@');
4460 -- remove all numeric digits for ER 9369854
4461 l_name := trim(translate(l_name,' 0123456789',' '));
4462 -- replace all hyphen and spaces with comma
4463 l_name := translate(l_name,'- ',',,');
4464
4465 if length(l_name) > 0 then
4466 l_name := ','||l_name;
4467 -- l_initials := substr(l_name, 1, 1);
4468
4469 end if;
4470
4471 -- Check for a comma
4472 if l_initials = ',' then
4473
4474 l_initials := '';
4475
4476 end if;
4477
4478 l_pos := instr(l_name, ',', 1, 1);
4479 while l_pos <> 0 loop
4480
4481 -- Move the Position indicator to the character after the comma
4482 l_pos := l_pos + 1;
4483
4484 -- Move forward until you find something that is not a space
4485 while substr(l_name, l_pos, 1) = ',' loop
4486
4487 l_pos := l_pos + 1;
4488
4489 end loop;
4490
4491 -- Append the initial
4492 l_trc_initial := substr(l_name, l_pos, 1);
4493
4494 --Initial must contain only a to z OR A to Z
4495 while (l_trc_initial not between 'a' and 'z') and (l_trc_initial not between 'A' and 'Z')
4496 loop
4497 l_pos := l_pos + 1;
4498 l_trc_initial := substr(l_name, l_pos, 1);
4499 end loop;
4500
4501 l_initials := l_initials || l_trc_initial;
4502 -- Find the next initial
4503 l_pos := instr(l_name, ',', l_pos, 1);
4504
4505 end loop;
4506
4507 -- Format the result and limit it to 5 characters
4508 l_initials := substr(l_initials, 1, 5);
4509
4510 return l_initials;
4511
4512 end initials;
4513
4514
4515
4516
4517 ----------------------------------------------------------------------
4518 -- Procedure to call archive API to to archive the data
4519 -- present in the PL/SQL table
4520 ----------------------------------------------------------------------
4521 procedure insert_archive_row(p_assactid in number,
4522 p_tab_rec_data in action_info_table) is
4523 l_proc constant varchar2(50):= g_package||'insert_archive_row';
4524 l_ovn number;
4525 l_action_id number;
4526 begin
4527 hr_utility.set_location('Entering: '||l_proc,1);
4528 if p_tab_rec_data.count > 0 then
4529 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
4530
4531 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
4532 hr_utility.trace('action_context_id = '|| p_assactid);
4533 hr_utility.trace('p_tab_rec_data(i).action_info_category = '|| p_tab_rec_data(i).action_info_category);
4534 if p_tab_rec_data(i).action_info_category is not null then
4535 pay_action_information_api.create_action_information(
4536 p_action_information_id => l_action_id,
4537 p_object_version_number => l_ovn,
4538 p_action_information_category => p_tab_rec_data(i).action_info_category,
4539 p_action_context_id => p_assactid,
4540 p_action_context_type => 'AAP',
4541 p_assignment_id => p_tab_rec_data(i).assignment_id,
4542 p_effective_date => sysdate,
4543 p_action_information1 => p_tab_rec_data(i).act_info1,
4544 p_action_information2 => p_tab_rec_data(i).act_info2,
4545 p_action_information3 => p_tab_rec_data(i).act_info3,
4546 p_action_information4 => p_tab_rec_data(i).act_info4,
4547 p_action_information5 => p_tab_rec_data(i).act_info5,
4548 p_action_information6 => p_tab_rec_data(i).act_info6,
4549 p_action_information7 => p_tab_rec_data(i).act_info7,
4550 p_action_information8 => p_tab_rec_data(i).act_info8,
4551 p_action_information9 => p_tab_rec_data(i).act_info9,
4552 p_action_information10 => p_tab_rec_data(i).act_info10,
4553 p_action_information11 => p_tab_rec_data(i).act_info11,
4554 p_action_information12 => p_tab_rec_data(i).act_info12,
4555 p_action_information13 => p_tab_rec_data(i).act_info13,
4556 p_action_information14 => p_tab_rec_data(i).act_info14,
4557 p_action_information15 => p_tab_rec_data(i).act_info15,
4558 p_action_information16 => p_tab_rec_data(i).act_info16,
4559 p_action_information17 => p_tab_rec_data(i).act_info17,
4560 p_action_information18 => p_tab_rec_data(i).act_info18,
4561 p_action_information19 => p_tab_rec_data(i).act_info19,
4562 p_action_information20 => p_tab_rec_data(i).act_info20,
4563 p_action_information21 => p_tab_rec_data(i).act_info21,
4564 p_action_information22 => p_tab_rec_data(i).act_info22,
4565 p_action_information23 => p_tab_rec_data(i).act_info23,
4566 p_action_information24 => p_tab_rec_data(i).act_info24,
4567 p_action_information25 => p_tab_rec_data(i).act_info25,
4568 p_action_information26 => p_tab_rec_data(i).act_info26,
4569 p_action_information27 => p_tab_rec_data(i).act_info27,
4570 p_action_information28 => p_tab_rec_data(i).act_info28,
4571 p_action_information29 => p_tab_rec_data(i).act_info29,
4572 p_action_information30 => p_tab_rec_data(i).act_info30
4573 );
4574 end if;
4575 end loop;
4576 end if;
4577 hr_utility.set_location('Leaving: '||l_proc,999);
4578 end insert_archive_row;
4579
4580
4581 end PAY_ZA_TYE_ARCHIVE_PKG;