[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_TAX_REG
Source
1 PACKAGE BODY PY_ZA_TAX_REG AS
2 /* $Header: pyzatreg.pkb 120.14.12020000.12 2013/01/31 15:20:57 abdash ship $ */
3 /* Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA */
4 /* All rights reserved.
5 /*
6 Change List:
7 ------------
8
9 Name Date Version Bug Text
10 -------------- ----------- ------- ------- -----------------------
11 ABDASH 31/01/2013 115.47 16174886 ADDING OF LEGAL ENTITY PARAMETER TO TAX REGISTER REPORT(XML)
12 ABDASH 17/12/2012 115.46 15970629 2013 TAX YEAR CHANGES.
13 NCHINNAM 01/10/2012 115.45 14578463 Fix for bug#14578463
14 MKUPPUCH 02/05/2012 115.44 13924112 Populating the Tax Code 3696
15 ABDASH 15/02/2012 115.43 13717246 ZA:RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
16 ABDASH 02/02/2012 115.42 13367825 DIRECTOR REMUNERATION ENHANCEMENT
17 NCHINNAM 30/12/2011 115.39 13490546 TYE 2012 Changes
18 ABDASH 08/12/2011 115.38 13444804 New Tax Register Report(Using BI Publisher) Enhancement
19 NCHINNAM 01/12/2011 115.37 13444804 New Tax Register Report(Using BI Publisher) Enhancement
20 NCHINNAM 07/01/2011 115.36 10254490 Fix for Issue
21 NCHINNAM 02/08/2010 115/35 9955013 ptd value of the code 4102 should be tax less site plus tax on lumpsum
22 NCHINNAM 30/07/2010 115/34 9955013 ptd value of the code 4102 should be tax less site
23 nchinnam 05/07/2010 115/33 9876955 Code 4102 Should be sum of PAYE+Tax on Lump Sums+Voluntary Tax
24 B Keshary 30/06/2010 115/32 9780615 To display the values if there is not Tax i.e no
25 value in 4103
26 B Keshary 10/06/2010 115/30 9780615 To display the 4103 value in 4102 code even when
27 the SITE/PAYE splilt is not done
28 P Arusia 03/05/2010 115/29 9588376 Bypassed the balances 'Taxable Package Components RFI',
29 'Taxable Package Components NRFI',
30 'Annual Taxable Package Components RFI',
31 'Annual Taxable Package Components NRFI'
32 as these are already considered in 'Total RFI/NRFIable
33 (Annual) Income' while calculating 3697 and 3698
34 R Babla 02/04/2010 115.28 9539950 Added nvl to l_pdt_bal, mtd and ytd when code exist
35 in t_code_val
36 R Babla 30/03/2010 115.27 9402834 Populating codes 4141,4142, 4149 and removing 4103
37 P Arusia 25/02/2010 115.26 9369937 Resetting the user table t_code_val
38 for each assignment
39 P Arusia 02/12/2009 115.25 9117260 Changes for Tax Year 2010
40 R Babla 23/02/2009 115.24 8274764 Modified cursor csr_irp5_balances
41 to add a join on the pay_assignment_actions.action_sequence
42 and cursor csr_processed_assignments to select
43 the action_sequence
44 R Babla 30/01/2009 115.23 8213478 Modifed the cursor csr_irp5_balances
45 to include the code 4005 with
46 balance_sequence 2
47 A. Mahanty 13/06/2006 115.22 5330452 Modified the cursor
48 csr_processed_assignments. The query was
49 modified to pick up the correct action_sequence.
50 (choosing max payroll_action_id may give incorrect
51 balance values in some cases)
52 Secure views were used for Performance enhancement.
53 A. Mahanty 14/04/2005 115.21 3491357 BRA Enh. Balance Value retrieval
54 modified.
55 J.N. Louw 23/06/2004 115.20 3694450 Modified assignment_nature
56 to reference fnd_lookup_values
57 instead of hr_lookups
58 R. Pahune 09/02/2004 115.19 3400581 Modified the cursor
59 csr_processed_assignments.
60 N. Venugopal 09/01/2004 115.18 3221746 removed set serverout on for gscc compliance.
61 N. Venugopal 07/01/2004 115.17 3221746 Code changes for performace improvement.
62 N. Venugopal 11/08/2003 115.16 3069004 Modified cursor csr_irp5_balances.
63 L. Kloppers 23/12/2002 115.15 2720082 Modified the cursors:
64 csr_processed_assignments to
65 select assignments only if they are on the
66 chosen payroll in the specified payroll period
67 for which the Tax Register is being run, and
68 csr_irp5_balances to
69 select lump sum balances for an assignment, even
70 where they were paid in earlier payrolls that
71 the assignment was on.
72 A.Sengar 10/12/2002 115.14 2665394 Modified the cursor
73 csr_processed_assignments to
74 improve the performance of the
75 select statement.
76 L. Kloppers 23/09/2002 115.11 2224332 Added Procedure assignment_nature
77 Modified Procedure pre_process to call
78 py_za_tax_certificates.get_sars_code
79 for correct saving of balance codes for
80 Foreign- and Directors Income
81 Removed DEFAULT NULL for two parameters in
82 public procedure pre_process as per gscc
83 J.N. Louw 29/05/2002 115.9 1858619 Fixing QA raised issues
84 2377480 Legal Entity fetch per
85 assignment and not per
86 organization
87 J.N. Louw 28/02/2002 115.8 Added
88 hr_utility calls
89 Removed
90 record creation for
91 assignment with no
92 balance values
93 J.N. Louw 04/02/2002 115.7 Added
94 include_assignment
95 J.N. Louw 25/01/2002 115.5 1756600 Register was updated to
96 1756617 accommodate bug changes
97 1858619 and merge of both
98 2117507 current and terminated
99 2132644 assignments reports
100 L. Kloppers 01-Mar-2001 115.4 Changed
101 per_assignment_status_types_tl
102 back to
103 per_assignment_status_types
104 and use PER_SYSTEM_STATUS
105 i.s.o. USER_STATUS
106 L. Kloppers 23-Feb-2001 115.3 Changed
107 per_assignment_status_types
108 to
109 per_assignment_status_types_tl
110 L. Kloppers 06-Feb-2001 115.2 Changed "end_date"
111 to "ptp.end_date"
112 L. Kloppers 31-Jan-2001 115.1 Changed attribute1
113 to prd_information1
114 A vd Berg 22-Jan-2001 110.11 Amended Version Number
115 G. Fraser 10-Nov-2000 110.8 Changed Termination
116 Assignment Cursor
117 G. Fraser 24-May-2000 110.3-7 Speed improvements
118 L.J.Kloppers 23-Feb-2000 110.2 Added p_tax_register_id
119 IN OUT NOCOPY parameter
120 L.J.Kloppers 13-Feb-2000 110.1 Added p_total_employees
121 and p_total_assignments
122 IN OUT NOCOPY parameters
123 L.J.Kloppers 12-Feb-2000 110.0 Initial Version
124 */
125
126 -------------------------------------------------------------------------------
127 -- PACKAGE BODY --
128 -------------------------------------------------------------------------------
129
130 ------------------
131 -- Package Globals
132 ------------------
133 type code_desc is record (
134 bal_name varchar2(100)
135 );
136
137 type code_value_rec is record (
138 bal_name varchar2(400),
139 included_in number,
140 ptd_val number,
141 mtd_val number,
142 ytd_val number,
143 ptd_group_val number,
144 mtd_group_val number,
145 ytd_group_val number
146 );
147 type code_value_table is table of code_value_rec index by binary_integer;
148 type code_desc_table is table of code_desc index by binary_integer;
149
150 g_code code_desc_table;
151 g_tax_register_id pay_za_tax_registers.tax_register_id%TYPE;
152 g_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
153 g_start_period_id per_time_periods.time_period_id%TYPE;
154 g_end_period_id per_time_periods.time_period_id%TYPE;
155 g_period_num per_time_periods.period_num%TYPE;
156 g_period_start_date per_time_periods.start_date%TYPE;
157 g_period_end_date per_time_periods.end_date%TYPE;
158 g_payroll_name pay_all_payrolls_f.payroll_name%TYPE;
159 g_include_asg VARCHAR2(1);
160 g_retrieve_ptd BOOLEAN;
161 g_retrieve_mtd BOOLEAN;
162 g_retrieve_ytd BOOLEAN;
163 g_tax_year VARCHAR2(10); -- Bug 13367825
164 --
165 -------------------------------------------------------------------------------
166 -- zeroval
167 -------------------------------------------------------------------------------
168 PROCEDURE zvl (
169 p_val IN OUT NOCOPY NUMBER
170 )
171 AS
172 -------------------------------------------------------------------------------
173 BEGIN -- MAIN --
174 -------------------------------------------------------------------------------
175 hr_utility.set_location('py_za_tax_reg.zvl',1);
176
177 IF p_val IS NOT NULL THEN
178 IF p_val = 0 THEN
179 p_val := NULL;
180 END IF;
181 END IF;
182
183 hr_utility.set_location('py_za_tax_reg.zvl',2);
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 hr_utility.set_location('py_za_tax_reg.zvl',3);
188 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
189 hr_utility.raise_error;
190 -------------------------------------------------------------------------------
191 END zvl;
192
193 -------------------------------------------------------------------------------
194 -- valid_record
195 -------------------------------------------------------------------------------
196 FUNCTION valid_record (
197 p_ptd_bal IN NUMBER DEFAULT NULL
198 , p_mtd_bal IN NUMBER DEFAULT NULL
199 , p_ytd_bal IN NUMBER DEFAULT NULL
200 )
201 RETURN BOOLEAN
202 AS
203 ------------
204 -- Variables
205 ------------
206 l_check_val VARCHAR2(1) := 'X';
207 l_ret_val BOOLEAN DEFAULT FALSE;
208 ------------
209 -------------------------------------------------------------------------------
210 BEGIN -- MAIN --
211 -------------------------------------------------------------------------------
212 hr_utility.set_location('py_za_tax_reg.valid_record',1);
213
214 IF nvl(
215 to_char(
216 nvl(
217 nvl( p_ptd_bal
218 , p_mtd_bal
219 )
220 , p_ytd_bal
221 )
222 )
223 , l_check_val
224 ) <> l_check_val
225 THEN
226 hr_utility.set_location('py_za_tax_reg.valid_record',2);
227 l_ret_val := TRUE;
228 END IF;
229
230 hr_utility.set_location('py_za_tax_reg.valid_record',3);
231 RETURN l_ret_val;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 hr_utility.set_location('py_za_tax_reg.valid_record',4);
236 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
237 hr_utility.raise_error;
238 -------------------------------------------------------------------------------
239 END valid_record;
240
241 -------------------------------------------------------------------------------
242 -- balance_id
243 -------------------------------------------------------------------------------
244 FUNCTION balance_id (
245 p_balance_name IN pay_balance_types.balance_name%TYPE
246 )
247 RETURN pay_balance_types.balance_type_id%TYPE
248 AS
249 ---------
250 -- Cursor
251 ---------
252 CURSOR csr_balance_id (
253 p_balance_name IN pay_balance_types.balance_name%TYPE
254 )
255 IS
256 SELECT
257 pbt.balance_type_id
258 FROM
259 pay_balance_types pbt
260 WHERE
261 pbt.balance_name = p_balance_name
262 AND pbt.business_group_id IS NULL
263 AND pbt.legislation_code = 'ZA';
264
265 ------------
266 -- Variables
267 ------------
268 l_retval pay_balance_types.balance_type_id%TYPE;
269
270 -------------------------------------------------------------------------------
271 BEGIN -- MAIN --
272 -------------------------------------------------------------------------------
273 hr_utility.set_location('py_za_tax_reg.balance_id',1);
274
275 OPEN csr_balance_id(p_balance_name);
276 FETCH csr_balance_id INTO l_retval;
277 CLOSE csr_balance_id;
278
279 hr_utility.set_location('py_za_tax_reg.balance_id',2);
280 RETURN l_retval;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 hr_utility.set_location('py_za_tax_reg.balance_id',3);
285 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
286 hr_utility.raise_error;
287 -------------------------------------------------------------------------------
288 END balance_id;
289
290 -------------------------------------------------------------------------------
291 -- ptd_value
292 -------------------------------------------------------------------------------
293 FUNCTION ptd_value (
294 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
295 , p_action_period_id IN per_time_periods.time_period_id%TYPE
296 , p_balance_type_id IN pay_balance_types.balance_type_id%TYPE
297 , p_balance_name IN pay_za_irp5_bal_codes.full_balance_name%TYPE
298 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
299 )
300 RETURN NUMBER AS
301 ------------
302 -- Variables
303 ------------
304 l_ptd_value NUMBER;
305 --
306 -------------------------------------------------------------------------------
307 BEGIN -- MAIN --
308 -------------------------------------------------------------------------------
309 hr_utility.set_location('py_za_tax_reg.ptd_value',1);
310 -- Check if the PTD value must be retrieved
311 --
312 IF g_retrieve_ptd THEN
313 hr_utility.set_location('py_za_tax_reg.ptd_value',2);
314 -- PTD value of Site and Paye Amount not necessary
315 --
316 /* comment for bug 9780615 */
317 -- IF UPPER(p_balance_name) NOT IN ('SITE','PAYE') THEN
318 -- hr_utility.set_location('py_za_tax_reg.ptd_value',3);
319 -- Is the assignment's action in the current period
320 --
321 IF g_end_period_id = p_action_period_id THEN
322 hr_utility.set_location('py_za_tax_reg.ptd_value',4);
323 -- Retrieve the value
324 --3491357
325 /*l_ptd_value := py_za_bal.calc_asg_tax_ptd_action (
326 p_asg_action_id
327 , p_balance_type_id
328 , p_effective_date
329 );*/
330 l_ptd_value := py_za_bal.get_balance_value_action (
331 p_asg_action_id
332 , p_balance_type_id
333 , '_ASG_TAX_PTD'
334 );
335 END IF;
336 -- END IF;
337 END IF;
338 hr_utility.set_location('py_za_tax_reg.ptd_value',5);
339 zvl(l_ptd_value);
340 hr_utility.set_location('py_za_tax_reg.ptd_value',6);
341 -- Return
342 RETURN l_ptd_value;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 hr_utility.set_location('py_za_tax_reg.ptd_value',7);
347 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
348 hr_utility.raise_error;
349 -------------------------------------------------------------------------------
350 END ptd_value;
351
352 -------------------------------------------------------------------------------
353 -- mtd_value
354 -------------------------------------------------------------------------------
355 FUNCTION mtd_value (
356 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
357 , p_balance_type_id IN pay_balance_types.balance_type_id%TYPE
358 , p_balance_name IN pay_za_irp5_bal_codes.full_balance_name%TYPE
359 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
360 )
361 RETURN NUMBER AS
362 ------------
363 -- Variables
364 ------------
365 l_mtd_value NUMBER;
366 --
367 -------------------------------------------------------------------------------
368 BEGIN -- MAIN --
369 -------------------------------------------------------------------------------
370 hr_utility.set_location('py_za_tax_reg.mtd_value',1);
371 -- Check if the MTD value must be retrieved
372 --
373 IF g_retrieve_mtd THEN
374 hr_utility.set_location('py_za_tax_reg.mtd_value',2);
375 -- PTD value of Site and Paye Amount not necessary
376 --
377 /* comment for bug 9780615 */
378 -- IF UPPER(p_balance_name) NOT IN ('SITE','PAYE') THEN
379 -- hr_utility.set_location('py_za_tax_reg.mtd_value',3);
380 -- Is the effective date of the action in the current period
381 --
382 IF p_effective_date between g_period_start_date
383 and g_period_end_date
384 THEN
385 hr_utility.set_location('py_za_tax_reg.mtd_value',4);
386 -- Retrieve the value
387 --3491357
388 /*l_mtd_value := py_za_bal.calc_asg_tax_mtd_action (
389 p_asg_action_id
390 , p_balance_type_id
391 , p_effective_date
392 );*/
393 l_mtd_value := py_za_bal.get_balance_value_action (
394 p_asg_action_id
395 , p_balance_type_id
396 , '_ASG_TAX_MTD'
397 );
398 END IF;
399 -- END IF;
400 END IF;
401 hr_utility.set_location('py_za_tax_reg.mtd_value',5);
402 zvl(l_mtd_value);
403 hr_utility.set_location('py_za_tax_reg.mtd_value',6);
404 -- Return
405 RETURN l_mtd_value;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 hr_utility.set_location('py_za_tax_reg.mtd_value',7);
410 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
411 hr_utility.raise_error;
412 -------------------------------------------------------------------------------
413 END mtd_value;
414
415 -------------------------------------------------------------------------------
416 -- ytd_value
417 -------------------------------------------------------------------------------
418 FUNCTION ytd_value (
419 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
420 , p_balance_type_id IN pay_balance_types.balance_type_id%TYPE
421 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
422 )
423 RETURN NUMBER AS
424 ------------
425 -- Variables
426 ------------
427 l_ytd_value NUMBER;
428 --
429 -------------------------------------------------------------------------------
430 BEGIN -- MAIN --
431 -------------------------------------------------------------------------------
432 hr_utility.set_location('py_za_tax_reg.ytd_value',1);
433 -- Check if the YTD value must be retrieved
434 --
435 IF g_retrieve_ytd THEN
436 hr_utility.set_location('py_za_tax_reg.ytd_value',2);
437 -- Retrieve the value
438 --3491357
439 /*l_ytd_value := py_za_bal.calc_asg_tax_ytd_action (
440 p_asg_action_id
441 , p_balance_type_id
442 , p_effective_date
443 );*/
444 l_ytd_value := py_za_bal.get_balance_value_action (
445 p_asg_action_id
446 , p_balance_type_id
447 , '_ASG_TAX_YTD'
448 );
449 END IF;
450 hr_utility.set_location('py_za_tax_reg.ytd_value',3);
451 zvl(l_ytd_value);
452 hr_utility.set_location('py_za_tax_reg.ytd_value',4);
453 -- Return
454 RETURN l_ytd_value;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 hr_utility.set_location('py_za_tax_reg.ytd_value',5);
459 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
460 hr_utility.raise_error;
461 -------------------------------------------------------------------------------
462 END ytd_value;
463
464 -------------------------------------------------------------------------------
465 -- run_result_value
466 -------------------------------------------------------------------------------
467 FUNCTION run_result_value (
468 p_element_name IN pay_element_types_f.element_name%TYPE
469 , p_value_name IN pay_input_values_f.name%TYPE
470 , p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
471 , p_run_result_id IN OUT NOCOPY pay_run_results.run_result_id%TYPE
472 )
473 RETURN pay_run_result_values.result_value%TYPE
474 AS
475 ---------
476 -- Cursor
477 ---------
478 CURSOR csr_result_value
479 IS
480 SELECT
481 prrv.result_value
482 , prrv.run_result_id
483 FROM
484 pay_element_types_f pet
485 , pay_input_values_f piv
486 , pay_run_results prr
487 , pay_run_result_values prrv
488 WHERE
489 pet.element_name = p_element_name
490 AND pet.legislation_code = 'ZA'
491 AND pet.element_type_id = piv.element_type_id
492 AND piv.name = p_value_name
493 AND piv.input_value_id = prrv.input_value_id
494 AND prr.element_type_id = pet.element_type_id
495 AND prr.run_result_id = prrv.run_result_id
496 AND prr.assignment_action_id =
497 (
498 SELECT
499 MAX(paa2.assignment_action_id)
500 FROM
501 pay_run_results prr2
502 , pay_assignment_actions paa2
503 , pay_payroll_actions ppa2
504 WHERE
505 prr2.element_type_id = pet.element_type_id
506 AND prr2.run_result_id = nvl(p_run_result_id, prr2.run_result_id)
507 AND prr2.assignment_action_id = paa2.assignment_action_id
508 AND paa2.assignment_id = p_assignment_id
509 AND paa2.payroll_action_id = ppa2.payroll_action_id
510 AND ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
511 AND ppa2.time_period_id BETWEEN g_start_period_id
512 AND g_end_period_id
513 );
514
515 ------------
516 -- Variables
517 ------------
518 l_result_value csr_result_value%ROWTYPE;
519 -------------------------------------------------------------------------------
520 BEGIN -- MAIN --
521 -------------------------------------------------------------------------------
522 hr_utility.set_location('py_za_tax_reg.run_result_value',1);
523 OPEN csr_result_value;
524 FETCH csr_result_value INTO l_result_value;
525 CLOSE csr_result_value;
526 --
527 hr_utility.set_location('py_za_tax_reg.run_result_value',2);
528 p_run_result_id := l_result_value.run_result_id;
529 RETURN l_result_value.result_value;
530 --
531 EXCEPTION
532 WHEN OTHERS THEN
533 hr_utility.set_location('py_za_tax_reg.run_result_value',3);
534 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
535 hr_utility.raise_error;
536 -------------------------------------------------------------------------------
537 END run_result_value;
538
539 -------------------------------------------------------------------------------
540 -- run_result_value
541 -- Overloaded version of the function where the run_result_id us known
542 -------------------------------------------------------------------------------
543 FUNCTION run_result_value (
544 p_value_name IN pay_input_values_f.name%TYPE
545 , p_run_result_id IN pay_run_results.run_result_id%TYPE
546 )
547 RETURN pay_run_result_values.result_value%TYPE
548 AS
549 ---------
550 -- Cursor
551 ---------
552 CURSOR csr_result_value
553 IS
554 SELECT
555 prrv.result_value
556 FROM
557 pay_run_results prr
558 , pay_input_values_f piv
559 , pay_run_result_values prrv
560 WHERE
561 prr.run_result_id = p_run_result_id
562 AND prr.element_type_id = piv.element_type_id
563 AND piv.name = p_value_name
564 AND piv.input_value_id = prrv.input_value_id
565 AND prr.run_result_id = prrv.run_result_id;
566
567 ------------
568 -- Variables
569 ------------
570 l_result_value csr_result_value%ROWTYPE;
571 -------------------------------------------------------------------------------
572 BEGIN -- MAIN --
573 -------------------------------------------------------------------------------
574 hr_utility.set_location('py_za_tax_reg.run_result_value',4);
575 OPEN csr_result_value;
576 FETCH csr_result_value INTO l_result_value;
577 CLOSE csr_result_value;
578 --
579 hr_utility.set_location('py_za_tax_reg.run_result_value',5);
580 RETURN l_result_value.result_value;
581 --
582 EXCEPTION
583 WHEN OTHERS THEN
584 hr_utility.set_location('py_za_tax_reg.run_result_value',6);
585 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
586 hr_utility.raise_error;
587 -------------------------------------------------------------------------------
588 END run_result_value;
589
590 -------------------------------------------------------------------------------
591 -- decode_lookup_code
592 -------------------------------------------------------------------------------
593 FUNCTION decode_lookup_code (
594 p_lookup_type IN hr_lookups.lookup_type%TYPE
595 , p_lookup_code IN hr_lookups.lookup_code%TYPE
596 , p_application_id IN hr_lookups.application_id%TYPE
597 )
598 RETURN hr_lookups.meaning%TYPE AS
599 ---------
600 -- Cursor
601 ---------
602 CURSOR csr_lookup_meaning
603 IS
604 SELECT hl.meaning
605 FROM hr_lookups hl
606 WHERE hl.lookup_type = p_lookup_type
607 AND hl.lookup_code = p_lookup_code
608 AND hl.application_id = p_application_id;
609 --
610 ------------
611 -- Variables
612 ------------
613 l_meaning hr_lookups.meaning%TYPE;
614 --
615 -------------------------------------------------------------------------------
616 BEGIN -- MAIN --
617 -------------------------------------------------------------------------------
618 hr_utility.set_location('py_za_tax_reg.decode_lookup_code',1);
619 OPEN csr_lookup_meaning;
620 FETCH csr_lookup_meaning INTO l_meaning;
621 CLOSE csr_lookup_meaning;
622
623 hr_utility.set_location('py_za_tax_reg.decode_lookup_code',2);
624 RETURN l_meaning;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 hr_utility.set_location('py_za_tax_reg.decode_lookup_code',3);
629 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
630 hr_utility.raise_error;
631 -------------------------------------------------------------------------------
632 END decode_lookup_code;
633
634 -------------------------------------------------------------------------------
635 -- assignment_tax_status_directive
636 -------------------------------------------------------------------------------
637 PROCEDURE assignment_tax_sta_dir (
638 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
639 , p_asg_tax_status OUT NOCOPY hr_lookups.meaning%TYPE
640 , p_asg_dir_value OUT NOCOPY pay_run_result_values.result_value%TYPE
641 , p_asg_tax_status_code OUT NOCOPY hr_lookups.lookup_code%TYPE
642 )
643 AS
644 ------------
645 -- Variables
646 ------------
647 l_tax_status hr_lookups.meaning%TYPE;
648 l_dir_value pay_run_result_values.result_value%TYPE;
649 l_run_result_id pay_run_results.run_result_id%TYPE;
650 l_asg_tax_status_code hr_lookups.lookup_code%TYPE;
651
652 -------------------------------------------------------------------------------
653 BEGIN -- MAIN --
654 -------------------------------------------------------------------------------
655 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',1);
656 --
657 l_tax_status := run_result_value (
658 p_element_name => 'ZA_Tax'
659 , p_value_name => 'Tax Status'
660 , p_assignment_id => p_assignment_id
661 , p_run_result_id => l_run_result_id
662 );
663 --
664 l_asg_tax_status_code := l_tax_status;
665 --
666 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',2);
667 --
668 l_tax_status := decode_lookup_code (
669 p_lookup_type => 'ZA_TAX_STATUS'
670 , p_lookup_code => l_tax_status
671 , p_application_id => 800
672 );
673 --
674 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',3);
675 --
676 IF l_run_result_id IS NOT NULL THEN
677 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',4);
678 -- Find the directive value for the same result id
679 l_dir_value := run_result_value (
680 p_value_name => 'Tax Directive Value'
681 , p_run_result_id => l_run_result_id
682 );
683 END IF;
684 --
685 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',5);
686 --
687 p_asg_tax_status := l_tax_status;
688 p_asg_dir_value := l_dir_value;
689 p_asg_tax_status_code := l_asg_tax_status_code;
690
691
692 EXCEPTION
693 WHEN OTHERS THEN
694 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',6);
695 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
696 hr_utility.raise_error;
697 -------------------------------------------------------------------------------
698 END assignment_tax_sta_dir;
699
700 -------------------------------------------------------------------------------
701 -- assignment_nature
702 -------------------------------------------------------------------------------
703 PROCEDURE assignment_nature (
704 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
705 , p_effective_date IN DATE
706 , p_asg_nature OUT NOCOPY hr_lookups.meaning%TYPE
707 )
708 AS
709 ------------
710 -- Variables
711 ------------
712
713 -----------------------------------------------------------------
714 -- Cursor csr_asg_nature
715 -----------------------------------------------------------------
716 CURSOR csr_asg_nature (
717 c_assignment_id IN per_all_assignments_f.assignment_id%TYPE
718 , c_effective_date IN DATE
719 )
720 IS
721 SELECT
722 nvl(fcl.meaning, 'A') nature
723 FROM
724 per_all_assignments_f ass
725 , per_assignment_extra_info aei
726 , fnd_lookup_values fcl
727 WHERE ass.assignment_id = c_assignment_id
728 AND ass.effective_start_date =
729 (
730 SELECT max(paf2.effective_start_date)
731 FROM per_all_assignments_f paf2
732 WHERE paf2.assignment_id = ass.assignment_id
733 AND paf2.effective_start_date <= c_effective_date
734 )
735 AND ass.assignment_id = aei.assignment_id(+)
736 AND aei.aei_information_category = 'ZA_SPECIFIC_INFO'
737 AND fcl.lookup_type(+) = 'ZA_PER_NATURES'
738 AND fcl.lookup_code(+) = aei.aei_information4
739 AND fcl.language(+) = 'US';
740
741
742 l_nature hr_lookups.meaning%TYPE;
743
744 -------------------------------------------------------------------------------
745 BEGIN -- MAIN --
746 -------------------------------------------------------------------------------
747 hr_utility.set_location('py_za_tax_reg.assignment_nature',1);
748 --
749 FOR v_asg_nature IN csr_asg_nature
750 ( c_assignment_id => p_assignment_id
751 , c_effective_date => p_effective_date
752 )
753 LOOP
754
755 l_nature := v_asg_nature.nature;
756
757 END LOOP csr_asg_nature;
758
759 IF l_nature IS NULL THEN
760
761 l_nature := 'A';
762
763 END IF;
764 --
765 hr_utility.set_location('py_za_tax_reg.assignment_nature',2);
766 --
767 p_asg_nature := l_nature;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 hr_utility.set_location('py_za_tax_reg.assignment_nature',3);
772 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
773 hr_utility.raise_error;
774 -------------------------------------------------------------------------------
775 END assignment_nature;
776
777 -------------------------------------------------------------------------------
778 -- assignment_dys_worked
779 -------------------------------------------------------------------------------
780 FUNCTION assignment_dys_worked (
781 p_asg_tax_status IN hr_lookups.meaning%TYPE
782 , p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
783 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
784 )
785 RETURN NUMBER
786 AS
787 ------------
788 -- Variables
789 ------------
790 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
791 l_balance_value NUMBER;
792 -------------------------------------------------------------------------------
793 BEGIN -- MAIN --
794 -------------------------------------------------------------------------------
795 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',1);
796 IF p_asg_tax_status = 'Seasonal Worker' THEN
797 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',2);
798 --
799 l_bal_type_id :=
800 balance_id (
801 p_balance_name => 'Total Seasonal Workers Days Worked'
802 );
803 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',3);
804 l_balance_value :=
805 ytd_value (
806 p_asg_action_id => p_asg_action_id
807 , p_balance_type_id => l_bal_type_id
808 , p_effective_date => p_effective_date
809 );
810 END IF;
811
812 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',4);
813 zvl(l_balance_value);
814 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',5);
815 -- Return
816 RETURN l_balance_value;
817
818 EXCEPTION
819 WHEN OTHERS THEN
820 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',6);
821 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
822 hr_utility.raise_error;
823 -------------------------------------------------------------------------------
824 END assignment_dys_worked;
825
826 -------------------------------------------------------------------------------
827 -- assignment_start_date
828 -------------------------------------------------------------------------------
829 FUNCTION assignment_start_date (
830 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
831 )
832 RETURN DATE AS
833 ---------
834 -- Cursor
835 ---------
836 CURSOR csr_assignment_start_date
837 IS
838 SELECT MIN(per.effective_start_date)
839 FROM per_all_assignments_f per
840 , per_assignment_status_types past
841 WHERE per.assignment_id = p_assignment_id
842 AND per.assignment_status_type_id = past.assignment_status_type_id
843 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
844
845 ------------
846 -- Variables
847 ------------
848 /*<variabel_name> <datatype> DEFAULT <default_value>*/
849 l_date per_all_assignments_f.effective_start_date%TYPE;
850 -------------------------------------------------------------------------------
851 BEGIN -- MAIN --
852 -------------------------------------------------------------------------------
853 hr_utility.set_location('py_za_tax_reg.assignment_start_date',1);
854 OPEN csr_assignment_start_date;
855 FETCH csr_assignment_start_date INTO l_date;
856 CLOSE csr_assignment_start_date;
857
858 hr_utility.set_location('py_za_tax_reg.assignment_start_date',2);
859 RETURN l_date;
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 hr_utility.set_location('py_za_tax_reg.assignment_start_date',3);
864 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
865 hr_utility.raise_error;
866 -------------------------------------------------------------------------------
867 END assignment_start_date;
868
869 -------------------------------------------------------------------------------
870 -- assignment_end_date
871 -------------------------------------------------------------------------------
872 FUNCTION assignment_end_date (
873 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
874 )
875 RETURN DATE AS
876 ---------
877 -- Cursor
878 ---------
879 CURSOR csr_assignment_end_date
880 IS
881 SELECT MAX(per.effective_end_date)
882 FROM per_all_assignments_f per
883 , per_assignment_status_types past
884 WHERE per.assignment_id = p_assignment_id
885 AND per.assignment_status_type_id = past.assignment_status_type_id
886 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
887
888 ------------
889 -- Variables
890 ------------
891 /*<variabel_name> <datatype> DEFAULT <default_value>*/
892 l_date per_all_assignments_f.effective_start_date%TYPE;
893 -------------------------------------------------------------------------------
894 BEGIN -- MAIN --
895 -------------------------------------------------------------------------------
896 hr_utility.set_location('py_za_tax_reg.assignment_end_date',1);
897
898 OPEN csr_assignment_end_date;
899 FETCH csr_assignment_end_date INTO l_date;
900 CLOSE csr_assignment_end_date;
901
902 hr_utility.set_location('py_za_tax_reg.assignment_end_date',2);
903 RETURN l_date;
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 hr_utility.set_location('py_za_tax_reg.assignment_end_date',3);
908 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
909 hr_utility.raise_error;
910 -------------------------------------------------------------------------------
911 END assignment_end_date;
912
913 -------------------------------------------------------------------------------
914 -- include_assignment
915 -------------------------------------------------------------------------------
916 FUNCTION include_assignment (
917 p_asg_id IN per_all_assignments_f.assignment_id%TYPE
918 , p_asg_start_date OUT NOCOPY per_all_assignments_f.effective_start_date%TYPE
919 , p_asg_end_date OUT NOCOPY per_all_assignments_f.effective_end_date%TYPE
920 )
921 RETURN BOOLEAN AS
922 ------------
923 -- Variables
924 ------------
925 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
926 l_include BOOLEAN;
927 --
928 -------------------------------------------------------------------------------
929 BEGIN -- MAIN --
930 -------------------------------------------------------------------------------
931 hr_utility.set_location('py_za_tax_reg.include_assignment',1);
932 --
933 p_asg_end_date := assignment_end_date (p_assignment_id => p_asg_id);
934 -- Include ALL Assignments
935 --
936 IF g_include_asg = 'A' THEN
937 hr_utility.set_location('py_za_tax_reg.include_assignment',2);
938 --
939 l_include := TRUE;
940 -- Include Terminated Assignments ONLY
941 --
942 ELSIF g_include_asg = 'T' THEN
943 hr_utility.set_location('py_za_tax_reg.include_assignment',3);
944 --
945 IF p_asg_end_date < g_period_end_date THEN
946 hr_utility.set_location('py_za_tax_reg.include_assignment',4);
947 l_include := TRUE;
948 ELSE
949 hr_utility.set_location('py_za_tax_reg.include_assignment',5);
950 l_include := FALSE;
951 END IF;
952 -- Include Current Assignments ONLY
953 --
954 ELSIF g_include_asg = 'C' THEN
955 hr_utility.set_location('py_za_tax_reg.include_assignment',6);
956 --
957 IF p_asg_end_date >= g_period_end_date THEN
958 hr_utility.set_location('py_za_tax_reg.include_assignment',7);
959 l_include := TRUE;
960 ELSE
961 hr_utility.set_location('py_za_tax_reg.include_assignment',8);
962 l_include := FALSE;
963 END IF;
964 END IF;
965
966
967 -- Set the end date of the assignment to null if
968 -- it's on or after the period end date
969 -- this will indicate a non terminated assignment
970 --
971 IF p_asg_end_date >= g_period_end_date THEN
972 hr_utility.set_location('py_za_tax_reg.include_assignment',9);
973 p_asg_end_date := NULL;
974 END IF;
975
976 IF l_include THEN
977 hr_utility.set_location('py_za_tax_reg.include_assignment',10);
978 p_asg_start_date := assignment_start_date (p_assignment_id => p_asg_id);
979 END IF;
980
981 hr_utility.set_location('py_za_tax_reg.include_assignment',11);
982 RETURN l_include;
983
984 EXCEPTION
985 WHEN OTHERS THEN
986 hr_utility.set_location('py_za_tax_reg.include_assignment',12);
987 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
988 hr_utility.raise_error;
989 -------------------------------------------------------------------------------
990 END include_assignment;
991
992 -------------------------------------------------------------------------------
993 -- include_assignment
994 -- This function is the overloaded version of include_assignment
995 -- It is called from the value set PY_SRS_ZA_TX_RGSTR_ASG
996 -------------------------------------------------------------------------------
997 FUNCTION include_assignment (
998 p_asg_id IN per_all_assignments_f.assignment_id%TYPE
999 , p_period_end_date IN per_time_periods.end_date%TYPE
1000 , p_include_flag IN VARCHAR2
1001 )
1002 RETURN VARCHAR2 AS
1003 ------------
1004 -- Variables
1005 ------------
1006 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
1007 l_include VARCHAR2(1);
1008 --
1009 -------------------------------------------------------------------------------
1010 BEGIN -- MAIN --
1011 -------------------------------------------------------------------------------
1012 l_asg_end_date := assignment_end_date (p_assignment_id => p_asg_id);
1013 -- Include ALL Assignments
1014 --
1015 IF p_include_flag = 'A' THEN
1016 l_include := 'Y';
1017 -- Include Terminated Assignments ONLY
1018 --
1019 ELSIF p_include_flag = 'T' THEN
1020 IF l_asg_end_date < p_period_end_date THEN
1021 l_include := 'Y';
1022 ELSE
1023 l_include := 'N';
1024 END IF;
1025 -- Include Current Assignments ONLY
1026 --
1027 ELSIF p_include_flag = 'C' THEN
1028 IF l_asg_end_date >= p_period_end_date THEN
1029 l_include := 'Y';
1030 ELSE
1031 l_include := 'N';
1032 END IF;
1033 END IF;
1034
1035 hr_utility.set_location('py_za_tax_reg.include_assignment',1);
1036 RETURN l_include;
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 hr_utility.set_location('py_za_tax_reg.include_assignment',2);
1041 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1042 hr_utility.raise_error;
1043 -------------------------------------------------------------------------------
1044 END include_assignment;
1045
1046 -------------------------------------------------------------------------------
1047 -- total_employees
1048 -------------------------------------------------------------------------------
1049 FUNCTION total_employees RETURN NUMBER AS
1050 ---------
1051 -- Cursor
1052 ---------
1053 CURSOR csr_total_employees
1054 IS
1055 SELECT
1056 count(max(tr.person_id))
1057 FROM
1058 pay_za_tax_registers tr
1059 GROUP BY
1060 tr.person_id;
1061
1062 ------------
1063 -- Variables
1064 ------------
1065 l_tot_employees NUMBER;
1066
1067 -------------------------------------------------------------------------------
1068 BEGIN -- MAIN --
1069 -------------------------------------------------------------------------------
1070 hr_utility.set_location('py_za_tax_reg.total_employees',1);
1071
1072 OPEN csr_total_employees;
1073 FETCH csr_total_employees INTO l_tot_employees;
1074 CLOSE csr_total_employees;
1075
1076 hr_utility.set_location('py_za_tax_reg.total_employees',2);
1077 RETURN l_tot_employees;
1078
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 hr_utility.set_location('py_za_tax_reg.total_employees',3);
1082 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1083 hr_utility.raise_error;
1084 -------------------------------------------------------------------------------
1085 END total_employees;
1086
1087 -------------------------------------------------------------------------------
1088 -- total_assignments
1089 -------------------------------------------------------------------------------
1090 FUNCTION total_assignments RETURN NUMBER AS
1091 ---------
1092 -- Cursor
1093 ---------
1094 CURSOR csr_total_assignments
1095 IS
1096 SELECT
1097 count(max(tr.assignment_id))
1098 FROM
1099 pay_za_tax_registers tr
1100 GROUP BY
1101 tr.assignment_id;
1102
1103 ------------
1104 -- Variables
1105 ------------
1106 l_tot_assignments NUMBER;
1107
1108 -------------------------------------------------------------------------------
1109 BEGIN -- MAIN --
1110 -------------------------------------------------------------------------------
1111 hr_utility.set_location('py_za_tax_reg.total_assignments',1);
1112
1113 OPEN csr_total_assignments;
1114 FETCH csr_total_assignments INTO l_tot_assignments;
1115 CLOSE csr_total_assignments;
1116
1117 hr_utility.set_location('py_za_tax_reg.total_assignments',2);
1118 RETURN l_tot_assignments;
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 hr_utility.set_location('py_za_tax_reg.total_assignments',3);
1123 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1124 hr_utility.raise_error;
1125 -------------------------------------------------------------------------------
1126 END total_assignments;
1127
1128
1129 -------------------------------------------------------------------------------
1130 -- set_period_details
1131 -------------------------------------------------------------------------------
1132 PROCEDURE set_period_details AS
1133 ---------
1134 -- Cursor
1135 ---------
1136 CURSOR csr_min_time_period
1137 IS
1138 SELECT
1139 MIN(ptp.time_period_id) min_time_period
1140 FROM
1141 per_time_periods ptp
1142 WHERE
1143 ptp.payroll_id = g_payroll_id
1144 AND ptp.prd_information1 =
1145 (
1146 SELECT ptp2.prd_information1
1147 FROM per_time_periods ptp2
1148 WHERE ptp2.payroll_id = g_payroll_id
1149 AND ptp2.time_period_id = g_end_period_id
1150 );
1151 ---------
1152 -- Cursor
1153 ---------
1154 CURSOR csr_period_details
1155 IS
1156 SELECT ptp.period_num
1157 , ptp.start_date
1158 , ptp.end_date
1159 FROM per_time_periods ptp
1160 WHERE ptp.time_period_id = g_end_period_id;
1161 ------------
1162 -- Variables
1163 ------------
1164 l_min_period_id per_time_periods.time_period_id%TYPE;
1165 l_period_info csr_period_details%ROWTYPE;
1166 --
1167 -------------------------------------------------------------------------------
1168 BEGIN -- MAIN --
1169 -------------------------------------------------------------------------------
1170 hr_utility.set_location('py_za_tax_reg.set_period_details',1);
1171 IF g_start_period_id IS NULL THEN
1172 hr_utility.set_location('py_za_tax_reg.set_period_details',2);
1173
1174 OPEN csr_min_time_period;
1175 FETCH csr_min_time_period INTO l_min_period_id;
1176 CLOSE csr_min_time_period;
1177
1178 g_start_period_id := l_min_period_id;
1179 END IF;
1180 --
1181 hr_utility.set_location('py_za_tax_reg.set_period_details',3);
1182 --
1183 OPEN csr_period_details;
1184 FETCH csr_period_details INTO l_period_info;
1185 CLOSE csr_period_details;
1186 --
1187 hr_utility.set_location('py_za_tax_reg.set_period_details',4);
1188 --
1189 g_period_num := l_period_info.period_num;
1190 g_period_start_date := l_period_info.start_date;
1191 g_period_end_date := l_period_info.end_date;
1192 --
1193 hr_utility.set_location('py_za_tax_reg.set_period_details',5);
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 hr_utility.set_location('py_za_tax_reg.set_period_details',6);
1198 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1199 hr_utility.raise_error;
1200 -------------------------------------------------------------------------------
1201 END set_period_details;
1202
1203
1204 -------------------------------------------------------------------------------
1205 -- set_payroll_details
1206 -------------------------------------------------------------------------------
1207 PROCEDURE set_payroll_details AS
1208 ---------
1209 -- Cursor
1210 ---------
1211 -- 3221746 removed fnd_sessions table
1212 CURSOR csr_payroll_name
1213 IS
1214 SELECT
1215 pap.payroll_name
1216 FROM
1217 pay_all_payrolls_f pap
1218 WHERE
1219 pap.payroll_id = g_payroll_id
1220 AND g_period_end_date BETWEEN pap.effective_start_date
1221 AND pap.effective_end_date;
1222
1223 ------------
1224 -- Variables
1225 ------------
1226 --
1227 -------------------------------------------------------------------------------
1228 BEGIN -- MAIN --
1229 -------------------------------------------------------------------------------
1230 hr_utility.set_location('py_za_tax_reg.set_payroll_details',1);
1231
1232 OPEN csr_payroll_name;
1233 FETCH csr_payroll_name INTO g_payroll_name;
1234 CLOSE csr_payroll_name;
1235
1236 hr_utility.set_location('py_za_tax_reg.set_payroll_details',2);
1237
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240 hr_utility.set_location('py_za_tax_reg.set_payroll_details',3);
1241 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1242 hr_utility.raise_error;
1243 -------------------------------------------------------------------------------
1244 END set_payroll_details;
1245
1246 -------------------------------------------------------------------------------
1247 -- set_globals
1248 -------------------------------------------------------------------------------
1249 PROCEDURE set_globals (
1250 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
1251 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1252 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1253 , p_include IN VARCHAR2
1254 , p_retrieve_ptd IN VARCHAR2
1255 , p_retrieve_mtd IN VARCHAR2
1256 , p_retrieve_ytd IN VARCHAR2
1257 )
1258 AS
1259
1260 ---------
1261 -- Cursor
1262 ---------
1263
1264 -- Bug 13367825
1265 -- DIRECTOR REMUNERATION ENHANCEMENT
1266 -- cursor to find the Tax Year
1267
1268 CURSOR csr_tax_year
1269 IS
1270 select PRD_INFORMATION1
1271 from per_time_periods
1272 where PRD_INFORMATION_CATEGORY = 'ZA'
1273 and TIME_PERIOD_ID = p_end_period_id ;
1274
1275 ------------
1276 -- Variables
1277 ------------
1278 l_tax_year VARCHAR2(10); -- Bug 13367825
1279 -------------------------------------------------------------------------------
1280 BEGIN -- MAIN --
1281 -------------------------------------------------------------------------------
1282 hr_utility.set_location('py_za_tax_reg.set_globals',1);
1283 --
1284 -- Bug 13367825
1285 OPEN csr_tax_year;
1286 FETCH csr_tax_year INTO l_tax_year;
1287 CLOSE csr_tax_year;
1288 -- Bug 13367825
1289
1290 SELECT
1291 pay_za_tax_registers_s.nextval
1292 INTO
1293 g_tax_register_id
1294 FROM
1295 dual;
1296 --
1297 hr_utility.set_location('py_za_tax_reg.set_globals',2);
1298 --
1299 g_payroll_id := p_payroll_id;
1300 g_start_period_id := p_start_period_id;
1301 g_end_period_id := p_end_period_id;
1302 g_include_asg := p_include;
1303 g_tax_year := l_tax_year ; -- Bug 13367825
1304 --
1305 hr_utility.set_location('py_za_tax_reg.set_globals',3);
1306 --
1307 IF p_retrieve_ptd = 'Y' THEN
1308 hr_utility.set_location('py_za_tax_reg.set_globals',4);
1309 g_retrieve_ptd := TRUE;
1310 END IF;
1311 IF p_retrieve_mtd = 'Y' THEN
1312 hr_utility.set_location('py_za_tax_reg.set_globals',5);
1313 g_retrieve_mtd := TRUE;
1314 END IF;
1315 IF p_retrieve_ytd = 'Y' THEN
1316 hr_utility.set_location('py_za_tax_reg.set_globals',6);
1317 g_retrieve_ytd := TRUE;
1318 END IF;
1319 --
1320 hr_utility.set_location('py_za_tax_reg.set_globals',7);
1321 --
1322 set_period_details;
1323 set_payroll_details;
1324 --set_company_details;
1325 --
1326 hr_utility.set_location('py_za_tax_reg.set_globals',8);
1327
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 hr_utility.set_location('py_za_tax_reg.set_globals',9);
1331 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1332 hr_utility.raise_error;
1333 -------------------------------------------------------------------------------
1334 END set_globals;
1335
1336 -------------------------------------------------------------------------------
1337 -- ins_register
1338 -------------------------------------------------------------------------------
1339 PROCEDURE ins_register (
1340 -- <parameter_name> <IN OUT> <datatype> <default>
1341 p_full_name IN pay_za_tax_registers.full_name%TYPE
1342 , p_employee_number IN pay_za_tax_registers.employee_number%TYPE
1343 , p_person_id IN pay_za_tax_registers.person_id%TYPE
1344 , p_date_of_birth IN pay_za_tax_registers.date_of_birth%TYPE
1345 , p_age IN pay_za_tax_registers.age%TYPE
1346 , p_tax_reference_no IN pay_za_tax_registers.tax_reference_no%TYPE
1347 , p_cmpy_tax_reference_no IN pay_za_tax_registers.cmpy_tax_reference_no%TYPE
1348 , p_tax_status IN pay_za_tax_registers.tax_status%TYPE
1349 , p_tax_directive_value IN pay_za_tax_registers.tax_directive_value%TYPE
1350 , p_days_worked IN pay_za_tax_registers.days_worked%TYPE
1351 , p_assignment_id IN pay_za_tax_registers.assignment_id%TYPE
1352 , p_assignment_action_id IN pay_za_tax_registers.assignment_action_id%TYPE
1353 , p_assignment_number IN pay_za_tax_registers.assignment_number%TYPE
1354 , p_assignment_start_date IN pay_za_tax_registers.assignment_start_date%TYPE
1355 , p_assignment_end_date IN pay_za_tax_registers.assignment_end_date%TYPE
1356 , p_bal_name IN pay_za_tax_registers.bal_name%TYPE DEFAULT NULL
1357 , p_bal_code IN pay_za_tax_registers.bal_code%TYPE DEFAULT NULL
1358 , p_tot_ptd IN pay_za_tax_registers.tot_ptd%TYPE DEFAULT NULL
1359 , p_tot_mtd IN pay_za_tax_registers.tot_mtd%TYPE DEFAULT NULL
1360 , p_tot_ytd IN pay_za_tax_registers.tot_ytd%TYPE DEFAULT NULL
1361 )
1362 AS
1363 ------------
1364 -- Variables
1365 ------------
1366 --
1367 -------------------------------------------------------------------------------
1368 BEGIN -- MAIN --
1369 -------------------------------------------------------------------------------
1370 hr_utility.set_location('py_za_tax_reg.ins_register',1);
1371 --
1372 INSERT INTO pay_za_tax_registers (
1373 tax_register_id
1374 , full_name
1375 , employee_number
1376 , person_id
1377 , date_of_birth
1378 , age
1379 , tax_reference_no
1380 , cmpy_tax_reference_no
1381 , tax_status
1382 , tax_directive_value
1383 , days_worked
1384 , assignment_id
1385 , assignment_action_id
1386 , assignment_number
1387 , assignment_start_date
1388 , assignment_end_date
1389 , bal_name
1390 , bal_code
1391 , tot_ptd
1392 , tot_mtd
1393 , tot_ytd
1394 )
1395 VALUES (
1396 g_tax_register_id
1397 , p_full_name
1398 , p_employee_number
1399 , p_person_id
1400 , p_date_of_birth
1401 , p_age
1402 , p_tax_reference_no
1403 , p_cmpy_tax_reference_no
1404 , p_tax_status
1405 , p_tax_directive_value
1406 , p_days_worked
1407 , p_assignment_id
1408 , p_assignment_action_id
1409 , p_assignment_number
1410 , p_assignment_start_date
1411 , p_assignment_end_date
1412 , p_bal_name
1413 , p_bal_code
1414 , p_tot_ptd
1415 , p_tot_mtd
1416 , p_tot_ytd
1417 );
1418 --
1419 hr_utility.set_location('py_za_tax_reg.ins_register',2);
1420 --
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 hr_utility.set_location('py_za_tax_reg.ins_register',3);
1424 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1425 hr_utility.raise_error;
1426 -------------------------------------------------------------------------------
1427 END ins_register;
1428
1429
1430 -------------------------------------------------------------------------------
1431 -- clear_register
1432 -------------------------------------------------------------------------------
1433 PROCEDURE clear_register (
1434 p_id IN pay_za_tax_registers.tax_register_id%TYPE
1435 )
1436 AS
1437 -------------------------------------------------------------------------------
1438 BEGIN -- MAIN --
1439 -------------------------------------------------------------------------------
1440 hr_utility.set_location('py_za_tax_reg.clear_register',1);
1441 --
1442 DELETE
1443 FROM
1444 pay_za_tax_registers ztr
1445 WHERE
1446 ztr.tax_register_id = p_id;
1447
1448 hr_utility.set_location('py_za_tax_reg.clear_register',2);
1449
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452 hr_utility.set_location('py_za_tax_reg.clear_register',3);
1453 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1454 hr_utility.raise_error;
1455 -------------------------------------------------------------------------------
1456 END clear_register;
1457
1458 -------------------------------------------------------------------------------
1459 -- Procedure pre_process
1460 --
1461 -- The Pre Process procedure called by the ZA Tax Register Report
1462 -- It populates the pay_za_tax_registers table with
1463 -- processed assignment balance value information
1464 -------------------------------------------------------------------------------
1465 PROCEDURE pre_process (
1466 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
1467 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1468 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1469 , p_include IN VARCHAR2
1470 , p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1471 , p_retrieve_ptd IN VARCHAR2
1472 , p_retrieve_mtd IN VARCHAR2
1473 , p_retrieve_ytd IN VARCHAR2
1474 , p_tax_register_id OUT NOCOPY pay_za_tax_registers.tax_register_id%TYPE
1475 , p_payroll_name OUT NOCOPY pay_all_payrolls_f.payroll_name%TYPE
1476 , p_period_num OUT NOCOPY per_time_periods.period_num%TYPE
1477 , p_period_start_date OUT NOCOPY per_time_periods.start_date%TYPE
1478 , p_period_end_date OUT NOCOPY per_time_periods.end_date%TYPE
1479 , p_tot_employees OUT NOCOPY NUMBER
1480 , p_tot_assignments OUT NOCOPY NUMBER
1481 , p_assactid IN pay_assignment_actions.assignment_action_id%TYPE DEFAULT NULL -- bug 13444804
1482 )
1483 AS
1484 -----------------------------------------------------------------
1485 -- Cursor csr_processed_assignments
1486 --
1487 -- Selects processed assignments and corresponding person details
1488 -- for a specific payroll within two time periods
1489 -- returning the maximum assignment action
1490 -----------------------------------------------------------------
1491 -- Bug 5330452
1492 CURSOR csr_processed_assignments (
1493 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
1494 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1495 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1496 , p_asg_id IN per_all_assignments_f.assignment_id%TYPE DEFAULT NULL
1497 )
1498 IS
1499 SELECT
1500 paa.assignment_action_id
1501 , paa.assignment_id
1502 , paa.action_sequence
1503 , ppa.time_period_id
1504 , ppa.effective_date
1505 , asg.assignment_number
1506 , pap.person_id
1507 , pap.full_name
1508 , pap.date_of_birth
1509 , pap.employee_number
1510 , pap.per_information1 tax_reference_number
1511 , trunc(months_between(g_period_end_date, pap.date_of_birth)/12) age
1512 , oit.org_information3 cmpy_tax_reference_number
1513 FROM
1514 pay_assignment_actions paa
1515 , pay_payroll_actions ppa
1516 , hr_organization_information oit
1517 , per_assignment_extra_info aei
1518 , per_assignments_f asg
1519 , per_people_f pap
1520 , (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id) ptp
1521 WHERE
1522 ppa.payroll_id = p_payroll_id
1523 AND ppa.time_period_id >= p_start_period_id
1524 AND ppa.time_period_id <= p_end_period_id
1525 AND ppa.payroll_action_id = paa.payroll_action_id
1526 AND paa.assignment_id = nvl(p_asg_id, paa.assignment_id)
1527 AND paa.rowid =
1528 (select rowid from pay_assignment_actions paa2 where
1529 paa2.assignment_id=paa.assignment_id
1530 and paa2.action_sequence=
1531 (select MAX(paa3.action_sequence) from pay_assignment_actions paa3,
1532 pay_payroll_actions ppa2
1533 where paa3.assignment_id = paa.assignment_id
1534 and paa3.payroll_action_id = ppa2.payroll_action_id
1535 and ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
1536 and ppa2.time_period_id <= p_end_period_id
1537 and ppa2.payroll_id = p_payroll_id
1538 )
1539 )
1540 AND paa.assignment_id = asg.assignment_id
1541 AND (
1542 (
1543 asg.effective_start_date <= ptp.end_date
1544 AND asg.effective_end_date >= ptp.end_date
1545 )
1546 OR
1547 (
1548 asg.effective_end_date <= ptp.end_date
1549 AND asg.effective_end_date = (select max(asg2.effective_end_date)
1550 from per_assignments_f asg2
1551 where asg2.assignment_id = asg.assignment_id)
1552 )
1553 )
1554 AND asg.payroll_id = p_payroll_id
1555 AND asg.assignment_id = aei.assignment_id(+)
1556 AND aei.aei_information_category(+) = 'ZA_SPECIFIC_INFO'
1557 AND aei.aei_information7 = oit.organization_id(+)
1558 AND oit.org_information_context(+) = 'ZA_LEGAL_ENTITY'
1559 AND asg.person_id = pap.person_id
1560 -- important, must be app eff date to get correct data
1561 AND asg.payroll_id = ppa.payroll_id
1562 AND g_period_end_date BETWEEN pap.effective_start_date
1563 AND pap.effective_end_date;
1564 -----------------------------------------------------------
1565 -- Cursor csr_irp5_balances
1566 --
1567 -- select those balances that have been fed by any
1568 -- assignment action of the assignment within the specified
1569 -- time periods, the tax year
1570 -----------------------------------------------------------
1571 CURSOR csr_irp5_balances (
1572 -- p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
1573 p_action_seq IN pay_assignment_actions.action_sequence%TYPE
1574 , p_asg_id IN pay_assignment_actions.assignment_id%TYPE
1575 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1576 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1577 )
1578 IS
1579 SELECT DISTINCT
1580 pbc.full_balance_name bal_name
1581 , pbc.code bal_code
1582 , pbc.balance_type_id bal_id
1583 FROM pay_za_irp5_bal_codes pbc
1584 , pay_run_result_values prrv
1585 , pay_run_results prr
1586 , pay_balance_feeds_f feed
1587 , pay_payroll_actions ppa
1588 , pay_assignment_actions paa
1589 WHERE prrv.input_value_id = feed.input_value_id
1590 AND prr.run_result_id = prrv.run_result_id
1591 -- AND paa.assignment_action_id <= p_asg_action_id
1592 AND paa.action_sequence < = p_action_seq
1593 AND prr.assignment_action_id = paa.assignment_action_id
1594 AND paa.assignment_id = p_asg_id
1595 AND ppa.payroll_action_id = paa.payroll_action_id
1596 AND ppa.action_type IN ('R', 'I', 'B', 'Q', 'V')
1597 AND ppa.effective_date >= (select start_date from per_time_periods ptp where ptp.time_period_id = p_start_period_id)
1598 AND ppa.effective_date <= (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id)
1599 AND pbc.balance_type_id = feed.balance_type_id
1600 AND (pbc.balance_sequence = 1
1601 or (pbc.code=4005 and pbc.balance_sequence=2)
1602 ) ;
1603 ------------
1604 -- Variables
1605 ------------
1606 l_asg_start_date per_all_assignments_f.effective_start_date%TYPE;
1607 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
1608 l_asg_tax_status pay_run_result_values.result_value%TYPE;
1609 l_asg_dir_value pay_run_result_values.result_value%TYPE;
1610 l_asg_dys_worked NUMBER;
1611 l_ptd_bal NUMBER;
1612 l_mtd_bal NUMBER;
1613 l_ytd_bal NUMBER;
1614 l_asg_tax_status_code hr_lookups.lookup_code%TYPE;
1615 l_nature hr_lookups.meaning%TYPE;
1616 l_bal_code pay_za_irp5_bal_codes.code%TYPE;
1617
1618 l_ovn number; -- bug 13444804
1619 l_action_id number; -- bug 13444804
1620
1621 -------------------------------------------------------------------------------
1622 BEGIN -- Pre Process - MAIN --
1623 -------------------------------------------------------------------------------
1624 -- hr_utility.trace_on(null,'ZATAXREG');
1625 hr_utility.set_location('py_za_tax_reg.pre_process',1);
1626 --
1627 IF (p_assactid IS NULL ) THEN
1628 set_globals (
1629 p_payroll_id => p_payroll_id
1630 , p_start_period_id => p_start_period_id
1631 , p_end_period_id => p_end_period_id
1632 , p_include => p_include
1633 , p_retrieve_ptd => p_retrieve_ptd
1634 , p_retrieve_mtd => p_retrieve_mtd
1635 , p_retrieve_ytd => p_retrieve_ytd
1636 );
1637 END IF;
1638 --
1639 hr_utility.set_location('py_za_tax_reg.pre_process',2);
1640 ------------------------
1641 <<Processed_Assignments>>
1642 ------------------------
1643 FOR v_assignments IN csr_processed_assignments
1644 ( p_payroll_id => g_payroll_id
1645 , p_start_period_id => g_start_period_id
1646 , p_end_period_id => g_end_period_id
1647 , p_asg_id => p_assignment_id
1648 )
1649 LOOP
1650 hr_utility.set_location('py_za_tax_reg.pre_process',3);
1651 --
1652 IF include_assignment (
1653 p_asg_id => v_assignments.assignment_id
1654 , p_asg_start_date => l_asg_start_date
1655 , p_asg_end_date => l_asg_end_date
1656 )
1657 THEN
1658 hr_utility.set_location('py_za_tax_reg.pre_process',4);
1659 -- get assignment's tax status and directive value
1660 assignment_tax_sta_dir (
1661 p_assignment_id => v_assignments.assignment_id
1662 , p_asg_tax_status => l_asg_tax_status
1663 , p_asg_dir_value => l_asg_dir_value
1664 , p_asg_tax_status_code => l_asg_tax_status_code
1665 );
1666 --
1667 -- get assignment's nature of person
1668 assignment_nature (
1669 p_assignment_id => v_assignments.assignment_id
1670 , p_effective_date => v_assignments.effective_date
1671 , p_asg_nature => l_nature
1672 );
1673 --
1674 hr_utility.set_location('py_za_tax_reg.pre_process',6);
1675 -- get assignment's seasonal days worked
1676 l_asg_dys_worked :=
1677 assignment_dys_worked (
1678 p_asg_tax_status => l_asg_tax_status
1679 , p_asg_action_id => v_assignments.assignment_action_id
1680 , p_effective_date => v_assignments.effective_date
1681 );
1682 --
1683 hr_utility.set_location('py_za_tax_reg.pre_process',7);
1684 -----------------
1685 <<Balance_Values>>
1686 -----------------
1687 FOR v_bal IN csr_irp5_balances (
1688 -- p_asg_action_id => v_assignments.assignment_action_id
1689 p_action_seq => v_assignments.action_sequence
1690 , p_asg_id => v_assignments.assignment_id
1691 , p_start_period_id => g_start_period_id
1692 , p_end_period_id => g_end_period_id
1693 )
1694 LOOP
1695 hr_utility.set_location('py_za_tax_reg.pre_process',8);
1696 --
1697 --get the correct SARS Code for directors and foreign income
1698 l_bal_code := py_za_tax_certificates.get_sars_code(
1699 p_sars_code => v_bal.bal_code
1700 , p_tax_status => l_asg_tax_status_code
1701 , p_nature => l_nature
1702 );
1703 --
1704 l_ptd_bal :=
1705 ptd_value (
1706 p_asg_action_id => v_assignments.assignment_action_id
1707 , p_action_period_id => v_assignments.time_period_id
1708 , p_balance_type_id => v_bal.bal_id
1709 , p_balance_name => v_bal.bal_name
1710 , p_effective_date => v_assignments.effective_date
1711 );
1712 --
1713 hr_utility.set_location('py_za_tax_reg.pre_process',9);
1714 --
1715 l_mtd_bal :=
1716 mtd_value (
1717 p_asg_action_id => v_assignments.assignment_action_id
1718 , p_balance_type_id => v_bal.bal_id
1719 , p_balance_name => v_bal.bal_name
1720 , p_effective_date => v_assignments.effective_date
1721 );
1722 --
1723 hr_utility.set_location('py_za_tax_reg.pre_process',10);
1724 --
1725 l_ytd_bal :=
1726 ytd_value (
1727 p_asg_action_id => v_assignments.assignment_action_id
1728 , p_balance_type_id => v_bal.bal_id
1729 , p_effective_date => v_assignments.effective_date
1730 );
1731 --
1732 hr_utility.set_location('py_za_tax_reg.pre_process',11);
1733 --
1734 IF valid_record (
1735 p_ptd_bal => l_ptd_bal
1736 , p_mtd_bal => l_mtd_bal
1737 , p_ytd_bal => l_ytd_bal
1738 )
1739 THEN
1740 hr_utility.set_location('py_za_tax_reg.pre_process',12);
1741 -- Create the register record
1742 --
1743 if (p_assactid IS NULL ) THEN
1744 ins_register (
1745 p_full_name => v_assignments.full_name
1746 , p_employee_number => v_assignments.employee_number
1747 , p_person_id => v_assignments.person_id
1748 , p_date_of_birth => v_assignments.date_of_birth
1749 , p_age => v_assignments.age
1750 , p_tax_reference_no => v_assignments.tax_reference_number
1751 , p_cmpy_tax_reference_no => v_assignments.cmpy_tax_reference_number
1752 , p_tax_status => l_asg_tax_status
1753 , p_tax_directive_value => l_asg_dir_value
1754 , p_days_worked => l_asg_dys_worked
1755 , p_assignment_id => v_assignments.assignment_id
1756 , p_assignment_action_id => v_assignments.assignment_action_id
1757 , p_assignment_number => v_assignments.assignment_number
1758 , p_assignment_start_date => l_asg_start_date
1759 , p_assignment_end_date => l_asg_end_date
1760 , p_bal_name => v_bal.bal_name
1761 , p_bal_code => l_bal_code
1762 , p_tot_ptd => l_ptd_bal
1763 , p_tot_mtd => l_mtd_bal
1764 , p_tot_ytd => l_ytd_bal
1765 );
1766 ELSE
1767 pay_action_information_api.create_action_information(
1768 p_action_information_id => l_action_id,
1769 p_object_version_number => l_ovn,
1770 p_action_information_category => 'ZA_TAX_REG_REP',
1771 p_action_context_id => p_assactid,
1772 p_action_context_type => 'AAP',
1773 p_assignment_id => v_assignments.assignment_id,
1774 p_effective_date => null,
1775 p_action_information1 => v_assignments.full_name,
1776 p_action_information2 => v_assignments.employee_number,
1777 p_action_information3 => v_assignments.person_id,
1778 p_action_information4 => v_assignments.date_of_birth,
1779 p_action_information5 => v_assignments.age,
1780 p_action_information6 => v_assignments.tax_reference_number,
1781 p_action_information7 => v_assignments.cmpy_tax_reference_number,
1782 p_action_information8 => l_asg_tax_status,
1783 p_action_information9 => l_asg_dir_value,
1784 p_action_information10 => l_asg_dys_worked,
1785 p_action_information11 => v_assignments.assignment_id,
1786 p_action_information12 => v_assignments.assignment_action_id,
1787 p_action_information13 => v_assignments.assignment_number,
1788 p_action_information14 => l_asg_start_date,
1789 p_action_information15 => l_asg_end_date,
1790 p_action_information16 => v_bal.bal_name,
1791 p_action_information17 => l_bal_code,
1792 p_action_information18 => l_ptd_bal,
1793 p_action_information19 => l_mtd_bal,
1794 p_action_information20 => l_ytd_bal,
1795 p_action_information21 => p_payroll_id,
1796 p_action_information22 => p_end_period_id
1797 );
1798 END IF;
1799 END IF; -- Valid Record
1800 END LOOP Balance_Values;
1801 END IF; -- Include Assignment
1802 END LOOP Processed_Assignments;
1803 --
1804 hr_utility.set_location('py_za_tax_reg.pre_process',13);
1805 ---------------------
1806 -- Set out Parameters
1807 ---------------------
1808 p_tax_register_id := g_tax_register_id;
1809 p_payroll_name := g_payroll_name;
1810 p_period_num := g_period_num;
1811 p_period_start_date := g_period_start_date;
1812 p_period_end_date := g_period_end_date;
1813 p_tot_employees := total_employees;
1814 p_tot_assignments := total_assignments;
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 hr_utility.set_location('py_za_tax_reg.pre_process',14);
1818 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1819 hr_utility.raise_error;
1820 -------------------------------------------------------------------------------
1821 END pre_process;-- END --
1822 -------------------------------------------------------------------------------
1823
1824
1825 -------------------------------------------------------------------------------
1826 -- valid_record used from tax year 2010 onwards
1827 -------------------------------------------------------------------------------
1828 FUNCTION valid_record_01032009 (
1829 p_ptd_bal IN NUMBER DEFAULT NULL
1830 , p_mtd_bal IN NUMBER DEFAULT NULL
1831 , p_ytd_bal IN NUMBER DEFAULT NULL
1832 , p_code IN NUMBER
1833 , p_desc OUT NOCOPY VARCHAR2
1834 )
1835 RETURN BOOLEAN
1836 AS
1837 ------------
1838 -- Variables
1839 ------------
1840 l_check_val VARCHAR2(1) := 'X';
1841 l_ret_val BOOLEAN DEFAULT FALSE;
1842 l_code varchar2(4);
1843 ------------
1844 -------------------------------------------------------------------------------
1845 BEGIN -- MAIN --
1846 -------------------------------------------------------------------------------
1847 l_code := p_code;
1848 hr_utility.set_location('py_za_tax_reg.valid_record_01032009 code:'||p_code,1);
1849
1850 IF nvl(
1851 to_char(
1852 nvl(
1853 nvl( p_ptd_bal
1854 , p_mtd_bal
1855 )
1856 , p_ytd_bal
1857 )
1858 )
1859 , l_check_val
1860 ) <> l_check_val
1861 THEN
1862 hr_utility.set_location('py_za_tax_reg.valid_record_01032009',2);
1863 l_ret_val := TRUE;
1864
1865 if l_code = 3665 then l_code := 3651;
1866 -- Bug 13367825
1867 -- elsif l_code = 3615 then l_code := 3601;
1868 elsif (g_tax_year <= 2012 ) then
1869 if l_code = 3615 then
1870 l_code := 3601;
1871 end if;
1872 -- Bug 13367825
1873 end if;
1874
1875 -- g_code contains list of all codes which are valid and their descriptions
1876 if g_code.exists(l_code) then
1877 p_desc := g_code(l_code).bal_name;
1878 hr_utility.set_location('py_za_tax_reg.valid_record_01032009',2.1);
1879 elsif g_code.exists(l_code-50) then
1880 p_desc := g_code(l_code-50).bal_name;
1881 hr_utility.set_location('py_za_tax_reg.valid_record_01032009',2.2);
1882 else
1883 l_ret_val := FALSE;
1884 hr_utility.set_location('py_za_tax_reg.valid_record_01032009',2.3);
1885 end if;
1886 END IF;
1887
1888 hr_utility.set_location('py_za_tax_reg.valid_record_01032009',3);
1889 RETURN l_ret_val;
1890
1891 EXCEPTION
1892 WHEN OTHERS THEN
1893 hr_utility.set_location('py_za_tax_reg.valid_record_01032009',4);
1894 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1895 hr_utility.raise_error;
1896 -------------------------------------------------------------------------------
1897 END valid_record_01032009;
1898
1899
1900 -------------------------------------------------------------------------------
1901 -- assignment_nature to be used from tax year 2010 onwards
1902 -------------------------------------------------------------------------------
1903 PROCEDURE assignment_nature_01032009 (
1904 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1905 , p_effective_date IN DATE
1906 , p_asg_nature OUT NOCOPY hr_lookups.meaning%TYPE
1907 , p_foreign_income OUT NOCOPY varchar2
1908 )
1909 AS
1910 ------------
1911 -- Variables
1912 ------------
1913
1914 -----------------------------------------------------------------
1915 -- Cursor csr_asg_nature
1916 -----------------------------------------------------------------
1917 CURSOR csr_asg_nature (
1918 c_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1919 , c_effective_date IN DATE
1920 )
1921 IS
1922 SELECT
1923 nvl(fcl.meaning, 'A') nature,
1924 aei.aei_information15 foreign_income
1925 FROM
1926 per_all_assignments_f ass
1927 , per_assignment_extra_info aei
1928 , fnd_lookup_values fcl
1929 WHERE ass.assignment_id = c_assignment_id
1930 AND ass.effective_start_date =
1931 (
1932 SELECT max(paf2.effective_start_date)
1933 FROM per_all_assignments_f paf2
1934 WHERE paf2.assignment_id = ass.assignment_id
1935 AND paf2.effective_start_date <= c_effective_date
1936 )
1937 AND ass.assignment_id = aei.assignment_id(+)
1938 AND aei.aei_information_category = 'ZA_SPECIFIC_INFO'
1939 AND fcl.lookup_type(+) = 'ZA_PER_NATURES'
1940 AND fcl.lookup_code(+) = aei.aei_information4
1941 AND fcl.language(+) = 'US';
1942
1943
1944 l_nature hr_lookups.meaning%TYPE;
1945
1946 -------------------------------------------------------------------------------
1947 BEGIN -- MAIN --
1948 -------------------------------------------------------------------------------
1949 hr_utility.set_location('py_za_tax_reg.assignment_nature_01032009',1);
1950 --
1951 FOR v_asg_nature IN csr_asg_nature
1952 ( c_assignment_id => p_assignment_id
1953 , c_effective_date => p_effective_date
1954 )
1955 LOOP
1956
1957 l_nature := v_asg_nature.nature;
1958 p_foreign_income := v_asg_nature.foreign_income;
1959
1960 END LOOP csr_asg_nature;
1961
1962 IF l_nature IS NULL THEN
1963
1964 l_nature := 'A';
1965
1966 END IF;
1967 --
1968 hr_utility.set_location('py_za_tax_reg.assignment_nature_01032009',2);
1969 --
1970 p_asg_nature := l_nature;
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 hr_utility.set_location('py_za_tax_reg.assignment_nature_01032009',3);
1975 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1976 hr_utility.raise_error;
1977 -------------------------------------------------------------------------------
1978 END assignment_nature_01032009;
1979
1980
1981 -------------------------------------------------------------------------------
1982 -- get_sars_code
1983 -------------------------------------------------------------------------------
1984 function get_sars_code
1985 (
1986 p_sars_code in varchar2,
1987 p_foreign_income in varchar2,
1988 p_nature in varchar2
1989 ) return varchar2 is
1990
1991 l_sars_code varchar2(256);
1992
1993 -------------------------------------------------------------------------------
1994 BEGIN -- Pre Process - MAIN --
1995 -------------------------------------------------------------------------------
1996 -- Local variable initialization - GSCC standards
1997 l_sars_code := 0;
1998
1999 -- Bug 13367825
2000 if ((p_nature = 'C') and (p_sars_code = '3601') and (g_tax_year <= 2012 ))
2001 -- Bug 13367825
2002 then
2003 l_sars_code := '3615';
2004 else
2005 l_sars_code := p_sars_code;
2006 end if;
2007
2008 if (p_foreign_income = 'Y' and to_number(l_sars_code) >= 3601 and to_number(l_sars_code) <= 3907
2009 and to_number(l_sars_code) not in (3614,3908,3909,3915,3920,3921,3922
2010 ,3696, 3697, 3698))
2011 then
2012 l_sars_code := to_char(to_number(l_sars_code) + 50);
2013 end if;
2014
2015 return l_sars_code;
2016 -------------------------------------------------------------------------------
2017 END get_sars_code;
2018
2019
2020
2021
2022
2023 -------------------------------------------------------------------------------
2024 -- fetch_code_desc
2025 -------------------------------------------------------------------------------
2026 PROCEDURE fetch_code_desc as
2027 cursor csr_code_desc is
2028 select lookup_code code,
2029 description code_desc
2030 from hr_lookups
2031 where application_id = 800
2032 and lookup_type = 'ZA_SARS_CODE_DESCRIPTIONS';
2033 -------------------------------------------------------------------------------
2034 BEGIN --
2035 -------------------------------------------------------------------------------
2036 for rec in csr_code_desc loop
2037 g_code(rec.code).bal_name := rec.code_desc;
2038 end loop;
2039 g_code(4103).bal_name := 'Tax';
2040 -------------------------------------------------------------------------------
2041 END fetch_code_desc;
2042
2043
2044
2045 -------------------------------------------------------------------------------
2046 -- merge
2047 -------------------------------------------------------------------------------
2048 PROCEDURE merge (
2049 t_code_val IN OUT NOCOPY code_value_table
2050 , from_code IN number
2051 , to_code IN number
2052 ) as
2053 function get_bal_name (l_code number) return varchar2 is
2054 cursor csr_bal_name(l_code number) is
2055 select balance_name
2056 from pay_za_irp5_bal_codes
2057 where code = l_code;
2058 l_bal_name varchar2(100);
2059 begin
2060 if l_code = '4003' then
2061 l_bal_name := 'Current and Arrear Provident Fund';
2062 end if;
2063 open csr_bal_name(l_code);
2064 fetch csr_bal_name into l_bal_name;
2065 close csr_bal_name;
2066
2067 return l_bal_name;
2068 end get_bal_name;
2069 -------------------------------------------------------------------------------
2070 BEGIN -- Pre Process - MAIN --
2071 -------------------------------------------------------------------------------
2072 hr_utility.set_location('Entering merge',1);
2073 hr_utility.set_location('from_code:'||from_code,1);
2074 hr_utility.set_location('to_code:'||to_code,1);
2075 if t_code_val.exists(from_code) then
2076 hr_utility.set_location('From Code exists',2);
2077 t_code_val(from_code).included_in := to_code;
2078 if not t_code_val.exists(to_code) then
2079 hr_utility.set_location('To Code doesnt exists',2);
2080 t_code_val(to_code).bal_name := get_bal_name(to_code);
2081 IF g_retrieve_ptd THEN
2082 t_code_val(to_code).ptd_val := 0;
2083 t_code_val(to_code).ptd_group_val := 0;
2084 END IF;
2085 IF g_retrieve_mtd THEN
2086 t_code_val(to_code).mtd_val := 0;
2087 t_code_val(to_code).mtd_group_val := 0;
2088 END IF;
2089 IF g_retrieve_ytd THEN
2090 t_code_val(to_code).ytd_val := 0;
2091 t_code_val(to_code).ytd_group_val := 0;
2092 END IF;
2093 end if;
2094
2095 IF g_retrieve_ptd THEN
2096 t_code_val(to_code).ptd_group_val := nvl(t_code_val( to_code).ptd_group_val,0) +
2097 nvl(t_code_val(from_code).ptd_group_val,0) ;
2098 END IF;
2099 IF g_retrieve_mtd THEN
2100 t_code_val(to_code).mtd_group_val := nvl(t_code_val( to_code).mtd_group_val,0) +
2101 nvl(t_code_val(from_code).mtd_group_val,0) ;
2102 END IF;
2103 IF g_retrieve_ytd then
2104 t_code_val(to_code).ytd_group_val := nvl(t_code_val( to_code).ytd_group_val,0) +
2105 nvl(t_code_val(from_code).ytd_group_val,0) ;
2106 END IF;
2107 hr_utility.set_location('After merging',5);
2108 hr_utility.set_location('t_code_val(to_code).ptd_group_val:'||t_code_val(to_code).ptd_group_val,10);
2109 hr_utility.set_location('t_code_val(to_code).mtd_group_val:'||t_code_val(to_code).mtd_group_val,10);
2110 hr_utility.set_location('t_code_val(to_code).ytd_group_val:'||t_code_val(to_code).ytd_group_val,10);
2111 end if;
2112 hr_utility.set_location('Exiting merge',50);
2113 -------------------------------------------------------------------------------
2114 END merge;
2115
2116
2117 --------------------------------------------------------------------------------
2118 --populate_4149
2119 --------------------------------------------------------------------------------
2120 PROCEDURE populate_4149( t_code_val IN OUT NOCOPY code_value_table
2121 ,p_4149_PTD IN NUMBER
2122 ,p_4149_MTD IN NUMBER
2123 ,p_4149_YTD IN NUMBER)
2124 IS
2125 BEGIN
2126 if not t_code_val.exists(4149) then
2127 IF g_retrieve_ptd THEN
2128 t_code_val(4149).ptd_val := 0;
2129 t_code_val(4149).ptd_group_val := 0;
2130 END IF;
2131 IF g_retrieve_mtd THEN
2132 t_code_val(4149).mtd_val := 0;
2133 t_code_val(4149).mtd_group_val := 0;
2134 END IF;
2135 IF g_retrieve_ytd THEN
2136 t_code_val(4149).ytd_val := 0;
2137 t_code_val(4149).ytd_group_val := 0;
2138 END IF;
2139 end if;
2140
2141 --Populate 4103 value in 4149
2142 merge(t_code_val,4103,4149);
2143 IF g_retrieve_ptd THEN
2144 t_code_val(4149).ptd_group_val := t_code_val(4149).ptd_group_val +
2145 p_4149_PTD;
2146 END IF;
2147 IF g_retrieve_mtd THEN
2148 t_code_val(4149).mtd_group_val := t_code_val(4149).mtd_group_val +
2149 p_4149_MTD;
2150 END IF;
2151 IF g_retrieve_ytd then
2152 t_code_val(4149).ytd_group_val := t_code_val(4149).ytd_group_val +
2153 p_4149_YTD;
2154 END IF;
2155
2156
2157 END;
2158 -----------------------------------------------------------------------------------------
2159
2160
2161 -------------------------------------------------------------------------------
2162 -- Procedure pre_process to be used from tax year 2010 onwards
2163 --
2164 -- The Pre Process procedure called by the ZA Tax Register Report
2165 -- It populates the pay_za_tax_registers table with
2166 -- processed assignment balance value information
2167 -------------------------------------------------------------------------------
2168 PROCEDURE pre_process_01032009 (
2169 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
2170 , p_start_period_id IN per_time_periods.time_period_id%TYPE
2171 , p_end_period_id IN per_time_periods.time_period_id%TYPE
2172 , p_include IN VARCHAR2
2173 , p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2174 , p_retrieve_ptd IN VARCHAR2
2175 , p_retrieve_mtd IN VARCHAR2
2176 , p_retrieve_ytd IN VARCHAR2
2177 , p_tax_register_id OUT NOCOPY pay_za_tax_registers.tax_register_id%TYPE
2178 , p_payroll_name OUT NOCOPY pay_all_payrolls_f.payroll_name%TYPE
2179 , p_period_num OUT NOCOPY per_time_periods.period_num%TYPE
2180 , p_period_start_date OUT NOCOPY per_time_periods.start_date%TYPE
2181 , p_period_end_date OUT NOCOPY per_time_periods.end_date%TYPE
2182 , p_tot_employees OUT NOCOPY NUMBER
2183 , p_tot_assignments OUT NOCOPY NUMBER
2184 , p_assactid IN pay_assignment_actions.assignment_action_id%TYPE DEFAULT NULL -- bug 13444804
2185 )
2186 AS
2187 -----------------------------------------------------------------
2188 -- Cursor csr_processed_assignments
2189 --
2190 -- Selects processed assignments and corresponding person details
2191 -- for a specific payroll within two time periods
2192 -- returning the maximum assignment action
2193 -----------------------------------------------------------------
2194 -- Bug 5330452
2195 CURSOR csr_processed_assignments (
2196 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
2197 , p_start_period_id IN per_time_periods.time_period_id%TYPE
2198 , p_end_period_id IN per_time_periods.time_period_id%TYPE
2199 , p_asg_id IN per_all_assignments_f.assignment_id%TYPE DEFAULT NULL
2200 )
2201 IS
2202 SELECT
2203 paa.assignment_action_id
2204 , paa.assignment_id
2205 , paa.action_sequence
2206 , ppa.time_period_id
2207 , ppa.effective_date
2208 , asg.assignment_number
2209 , pap.person_id
2210 , pap.full_name
2211 , pap.date_of_birth
2212 , pap.employee_number
2213 , pap.per_information1 tax_reference_number
2214 , trunc(months_between(g_period_end_date, pap.date_of_birth)/12) age
2215 , oit.org_information3 cmpy_tax_reference_number
2216 FROM
2217 pay_assignment_actions paa
2218 , pay_payroll_actions ppa
2219 , hr_organization_information oit
2220 , per_assignment_extra_info aei
2221 , per_assignments_f asg
2222 , per_people_f pap
2223 , (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id) ptp
2224 WHERE
2225 ppa.payroll_id = p_payroll_id
2226 AND ppa.time_period_id >= p_start_period_id
2227 AND ppa.time_period_id <= p_end_period_id
2228 AND ppa.payroll_action_id = paa.payroll_action_id
2229 AND paa.assignment_id = nvl(p_asg_id, paa.assignment_id)
2230 AND paa.rowid =
2231 (select rowid from pay_assignment_actions paa2 where
2232 paa2.assignment_id=paa.assignment_id
2233 and paa2.action_sequence=
2234 (select MAX(paa3.action_sequence) from pay_assignment_actions paa3,
2235 pay_payroll_actions ppa2
2236 where paa3.assignment_id = paa.assignment_id
2237 and paa3.payroll_action_id = ppa2.payroll_action_id
2238 and ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
2239 and ppa2.time_period_id >= p_start_period_id
2240 and ppa2.time_period_id <= p_end_period_id
2241 and ppa2.payroll_id = p_payroll_id
2242 and paa3.ACTION_STATUS in ( 'C', 'S')
2243 )
2244 )
2245 AND paa.assignment_id = asg.assignment_id
2246 AND (
2247 (
2248 asg.effective_start_date <= ptp.end_date
2249 AND asg.effective_end_date >= ptp.end_date
2250 )
2251 OR
2252 (
2253 asg.effective_end_date <= ptp.end_date
2254 AND asg.effective_end_date = (select max(asg2.effective_end_date)
2255 from per_assignments_f asg2
2256 where asg2.assignment_id = asg.assignment_id)
2257 )
2258 )
2259 AND asg.payroll_id = p_payroll_id
2260 AND asg.assignment_id = aei.assignment_id(+)
2261 AND aei.aei_information_category(+) = 'ZA_SPECIFIC_INFO'
2262 AND aei.aei_information7 = oit.organization_id(+)
2263 AND oit.org_information_context(+) = 'ZA_LEGAL_ENTITY'
2264 AND asg.person_id = pap.person_id
2265 -- important, must be app eff date to get correct data
2266 AND asg.payroll_id = ppa.payroll_id
2267 AND g_period_end_date BETWEEN pap.effective_start_date
2268 AND pap.effective_end_date;
2269 -----------------------------------------------------------
2270 -- Cursor csr_irp5_balances
2271 --
2272 -- select those balances that have been fed by any
2273 -- assignment action of the assignment within the specified
2274 -- time periods, the tax year
2275 -----------------------------------------------------------
2276 CURSOR csr_irp5_balances (
2277 -- p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
2278 p_action_seq IN pay_assignment_actions.action_sequence%TYPE
2279 , p_asg_id IN pay_assignment_actions.assignment_id%TYPE
2280 , p_start_period_id IN per_time_periods.time_period_id%TYPE
2281 , p_end_period_id IN per_time_periods.time_period_id%TYPE
2282 )
2283 IS
2284 SELECT DISTINCT
2285 pbc.balance_name bal_name
2286 , pbc.code bal_code
2287 , pbc.balance_type_id bal_id
2288 FROM pay_za_irp5_bal_codes pbc
2289 , pay_run_result_values prrv
2290 , pay_run_results prr
2291 , pay_balance_feeds_f feed
2292 , pay_payroll_actions ppa
2293 , pay_assignment_actions paa
2294 WHERE prrv.input_value_id = feed.input_value_id
2295 AND prr.run_result_id = prrv.run_result_id
2296 -- AND paa.assignment_action_id <= p_asg_action_id
2297 AND paa.action_sequence < = p_action_seq
2298 AND prr.assignment_action_id = paa.assignment_action_id
2299 AND paa.assignment_id = p_asg_id
2300 AND ppa.payroll_action_id = paa.payroll_action_id
2301 AND ppa.action_type IN ('R', 'I', 'B', 'Q', 'V')
2302 AND ppa.effective_date >= (select start_date from per_time_periods ptp where ptp.time_period_id = p_start_period_id)
2303 AND ppa.effective_date <= (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id)
2304 AND pbc.balance_type_id = feed.balance_type_id
2305 AND (pbc.balance_sequence = 1
2306 or (pbc.code=4005 and pbc.balance_sequence=2)
2307 )
2308 AND pbc.full_balance_name not in ('Taxable Package Components RFI',
2309 'Taxable Package Components NRFI',
2310 'Annual Taxable Package Components RFI',
2311 'Annual Taxable Package Components NRFI')
2312 UNION
2313 SELECT DISTINCT
2314 pbt.balance_name bal_name
2315 , decode(pbt.balance_name,'Skills Levy',4142,4141) bal_code
2316 , pbt.balance_type_id bal_id
2317 FROM pay_balance_types pbt
2318 , pay_run_result_values prrv
2319 , pay_run_results prr
2320 , pay_balance_feeds_f feed
2321 , pay_payroll_actions ppa
2322 , pay_assignment_actions paa
2323 WHERE prrv.input_value_id = feed.input_value_id
2324 AND prr.run_result_id = prrv.run_result_id
2325 AND paa.action_sequence < = p_action_seq
2326 AND prr.assignment_action_id = paa.assignment_action_id
2327 AND paa.assignment_id = p_asg_id
2328 AND ppa.payroll_action_id = paa.payroll_action_id
2329 AND ppa.action_type IN ('R', 'I', 'B', 'Q', 'V')
2330 AND ppa.effective_date >= (select start_date from per_time_periods ptp where ptp.time_period_id = p_start_period_id)
2331 AND ppa.effective_date <= (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id)
2332 AND pbt.balance_type_id = feed.balance_type_id
2333 AND pbt.balance_name in ('Skills Levy','UIF Employee Contribution','UIF Employer Contribution')
2334 AND pbt.legislation_code='ZA';
2335 /*
2336 SELECT DISTINCT
2337 pbt.balance_name bal_name
2338 , decode(pbt.balance_name,'Skills Levy',4142,4141) bal_code
2339 , pbt.balance_type_id bal_id
2340 FROM pay_balance_types pbt
2341 WHERE pbt.balance_name in ('Skills Levy','UIF Employee Contribution','UIF Employer Contribution')
2342 AND pbt.legislation_code='ZA'; */
2343
2344
2345 -- Bug 13717246
2346 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
2347 -----------------------------------------------------------
2348 -- Cursor csr_balance_type_id
2349 --
2350 -- fetches the balance_type_id for
2351 -- the balance 'RFI Override SARS Reporting'
2352 -----------------------------------------------------------
2353
2354 Cursor csr_balance_type_id is
2355 select balance_type_id
2356 from pay_balance_types
2357 where balance_name = 'RFI Override SARS Reporting'
2358 and legislation_code = 'ZA' ;
2359
2360 -----------------------------------------------------------
2361 -- Cursor csr_balance_type_id_codes
2362 --
2363 -- fetches the balance_type_id and full_balance_name
2364 -- for codes 3915,3608,3707 and 3718 from
2365 -- pay_za_irp5_bal_codes table where full balance name
2366 -- contains RFI
2367 -----------------------------------------------------------
2368
2369 Cursor csr_balance_details_codes is
2370 select code bal_code, balance_type_id bal_id, full_balance_name bal_name
2371 from pay_za_irp5_bal_codes
2372 where user_name like '%_ASG_LMPSM_TAX_YTD'
2373 and full_balance_name like '% RFI' ;
2374
2375
2376 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
2377
2378
2379 ------------
2380 -- Variables
2381 ------------
2382 l_asg_start_date per_all_assignments_f.effective_start_date%TYPE;
2383 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
2384 l_asg_tax_status pay_run_result_values.result_value%TYPE;
2385 l_asg_dir_value pay_run_result_values.result_value%TYPE;
2386 l_asg_dys_worked NUMBER;
2387 l_ptd_bal NUMBER;
2388 l_mtd_bal NUMBER;
2389 l_ytd_bal NUMBER;
2390 l_asg_tax_status_code hr_lookups.lookup_code%TYPE;
2391 l_nature hr_lookups.meaning%TYPE;
2392 l_bal_code pay_za_irp5_bal_codes.code%TYPE;
2393 t_code_val code_value_table;
2394 l_code pay_za_irp5_bal_codes.code%TYPE;
2395 l_asg_foreign_income varchar2(1);
2396 l_4149_ptd NUMBER;
2397 l_4149_mtd NUMBER;
2398 l_4149_ytd NUMBER;
2399 l_4102_ptd NUMBER;
2400 l_4102_mtd NUMBER;
2401 l_4102_ytd NUMBER;
2402
2403 l_ovn number; -- bug 13444804
2404 l_action_id number; -- bug 13444804
2405
2406 -- Bug#13924112
2407 -- Adding Tax Code 3696 To TAX REGISTER REPORT
2408 l_3696_ptd NUMBER;
2409 l_3696_mtd NUMBER;
2410 l_3696_ytd NUMBER;
2411 -- End Bug#13924112
2412 -- Bug 13717246
2413 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
2414
2415 l_balance_type_id_rfi_override pay_balance_types.balance_type_id%TYPE;
2416 l_ptd_bal_rfi_override NUMBER;
2417 l_mtd_bal_rfi_override NUMBER;
2418 l_ytd_bal_rfi_override NUMBER;
2419 l_ptd_bal_rfi_code NUMBER;
2420 l_mtd_bal_rfi_code NUMBER;
2421 l_ytd_bal_rfi_code NUMBER;
2422
2423 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
2424
2425
2426 -------------------------------------------------------------------------------
2427 BEGIN -- Pre Process - MAIN --
2428 -------------------------------------------------------------------------------
2429 -- hr_utility.trace_on(null,'ZATAXREG');
2430 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',1);
2431 --
2432 if (p_assactid IS NULL ) THEN
2433 set_globals (
2434 p_payroll_id => p_payroll_id
2435 , p_start_period_id => p_start_period_id
2436 , p_end_period_id => p_end_period_id
2437 , p_include => p_include
2438 , p_retrieve_ptd => p_retrieve_ptd
2439 , p_retrieve_mtd => p_retrieve_mtd
2440 , p_retrieve_ytd => p_retrieve_ytd
2441 );
2442 --
2443 -- fetch code descriptions
2444 fetch_code_desc;
2445 --
2446 END IF;
2447 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',2);
2448 ------------------------
2449 <<Processed_Assignments>>
2450 ------------------------
2451 FOR v_assignments IN csr_processed_assignments
2452 ( p_payroll_id => g_payroll_id
2453 , p_start_period_id => g_start_period_id
2454 , p_end_period_id => g_end_period_id
2455 , p_asg_id => p_assignment_id
2456 )
2457 LOOP
2458 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',3);
2459 hr_utility.set_location('Assignment ID:'||v_assignments.assignment_id,3);
2460 hr_utility.set_location('Employee Num :'||v_assignments.employee_number,3);
2461 --
2462 IF include_assignment (
2463 p_asg_id => v_assignments.assignment_id
2464 , p_asg_start_date => l_asg_start_date
2465 , p_asg_end_date => l_asg_end_date
2466 )
2467 THEN
2468 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',4);
2469 -- get assignment's tax status and directive value
2470 assignment_tax_sta_dir (
2471 p_assignment_id => v_assignments.assignment_id
2472 , p_asg_tax_status => l_asg_tax_status
2473 , p_asg_dir_value => l_asg_dir_value
2474 , p_asg_tax_status_code => l_asg_tax_status_code
2475 );
2476 --
2477 -- get assignment's nature of person
2478 assignment_nature_01032009 (
2479 p_assignment_id => v_assignments.assignment_id
2480 , p_effective_date => v_assignments.effective_date
2481 , p_asg_nature => l_nature
2482 , p_foreign_income => l_asg_foreign_income
2483 );
2484 --
2485 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',6);
2486 -- get assignment's seasonal days worked
2487 l_asg_dys_worked :=
2488 assignment_dys_worked (
2489 p_asg_tax_status => l_asg_tax_status
2490 , p_asg_action_id => v_assignments.assignment_action_id
2491 , p_effective_date => v_assignments.effective_date
2492 );
2493 --
2494 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',7);
2495
2496 -----------------
2497 <<Balance_Values>>
2498 -----------------
2499 t_code_val.delete;
2500 l_4149_ptd :=0;
2501 l_4149_mtd :=0;
2502 l_4149_ytd :=0;
2503 l_4102_ptd :=0;
2504 l_4102_mtd :=0;
2505 l_4102_ytd :=0;
2506
2507 -- Bug#13924112
2508 -- Adding Tax Code 3696 To TAX REGISTER REPORT
2509 l_3696_ptd :=0;
2510 l_3696_mtd :=0;
2511 l_3696_ytd :=0;
2512 -- End Bug#13924112
2513 FOR v_bal IN csr_irp5_balances (
2514 -- p_asg_action_id => v_assignments.assignment_action_id
2515 p_action_seq => v_assignments.action_sequence
2516 , p_asg_id => v_assignments.assignment_id
2517 , p_start_period_id => g_start_period_id
2518 , p_end_period_id => g_end_period_id
2519 )
2520 LOOP
2521 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',8);
2522 --
2523 hr_utility.set_location('Balance Type ID:'||v_bal.bal_id,8);
2524 hr_utility.set_location('Balance Name :'||v_bal.bal_name,8);
2525 hr_utility.set_location('v_assignments.action_sequence :'||v_assignments.action_sequence,8);
2526 hr_utility.set_location('g_start_period_id :'||g_start_period_id,8);
2527 hr_utility.set_location('g_end_period_id :'||g_end_period_id,8);
2528 hr_utility.set_location('v_assignments.assignment_id :'||v_assignments.assignment_id,8);
2529 hr_utility.set_location('v_assignments.effective_date :'||v_assignments.effective_date,8);
2530
2531 hr_utility.set_location('v_assignments.time_period_id :'||v_assignments.time_period_id,8);
2532 hr_utility.set_location('v_assignments.assignment_action_id :'||v_assignments.assignment_action_id,8);
2533
2534
2535 l_ptd_bal :=
2536 ptd_value (
2537 p_asg_action_id => v_assignments.assignment_action_id
2538 , p_action_period_id => v_assignments.time_period_id
2539 , p_balance_type_id => v_bal.bal_id
2540 , p_balance_name => v_bal.bal_name
2541 , p_effective_date => v_assignments.effective_date
2542 );
2543 --
2544 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',9);
2545 --
2546 l_mtd_bal :=
2547 mtd_value (
2548 p_asg_action_id => v_assignments.assignment_action_id
2549 , p_balance_type_id => v_bal.bal_id
2550 , p_balance_name => v_bal.bal_name
2551 , p_effective_date => v_assignments.effective_date
2552 );
2553 --
2554 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',10);
2555 --
2556 l_ytd_bal :=
2557 ytd_value (
2558 p_asg_action_id => v_assignments.assignment_action_id
2559 , p_balance_type_id => v_bal.bal_id
2560 , p_effective_date => v_assignments.effective_date
2561 );
2562 --
2563 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',11);
2564 --
2565 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',12);
2566 hr_utility.set_location('code :'||v_bal.bal_code,12);
2567 hr_utility.set_location('PTD:'||l_ptd_bal||' MTD:'||l_mtd_bal||' YTD:'||l_ytd_bal,12);
2568 t_code_val(v_bal.bal_code).bal_name := v_bal.bal_name;
2569
2570 --Retrieve the value to be populated in code 4149
2571 --Value of Tax i.e. 4103 will be merged in code 4149 through populate_4149
2572 if v_bal.bal_name in ('Tax on Lump Sums','Voluntary Tax')
2573 OR v_bal.bal_code in (4115,4141,4142) then
2574 l_4149_ptd:=l_4149_ptd + nvl(l_ptd_bal,0);
2575 l_4149_mtd:=l_4149_mtd + nvl(l_mtd_bal,0);
2576 l_4149_ytd:=l_4149_ytd + nvl(l_ytd_bal,0);
2577 hr_utility.set_location('l_4149_ptd:'||l_4149_ptd,12);
2578 hr_utility.set_location('l_4149_mtd:'||l_4149_mtd,12);
2579 hr_utility.set_location('l_4149_ytd:'||l_4149_ytd,12);
2580 end if;
2581
2582 /* Added for bug 9780615 */
2583
2584 if v_bal.bal_name in ('PAYE') then
2585 l_4102_ptd:= nvl(l_ptd_bal,0);
2586 l_4102_mtd:= nvl(l_mtd_bal,0);
2587 l_4102_ytd:= nvl(l_ytd_bal,0);
2588 hr_utility.set_location('l_4102_ptd:'||l_4102_ptd,12);
2589 hr_utility.set_location('l_4102_mtd:'||l_4102_mtd,12);
2590 hr_utility.set_location('l_4102_ytd:'||l_4102_ytd,12);
2591 end if;
2592
2593 -- Bug#13924112
2594 -- Adding Tax Code 3696 To TAX REGISTER REPORT
2595 if v_bal.bal_code in (3602,3652,3703,3753,3714,3764,3908) or (v_bal.bal_code in (3922) and v_bal.bal_name in ('Comp iro Death Non Taxable')) then
2596 l_3696_ptd:=l_3696_ptd + nvl(l_ptd_bal,0);
2597 l_3696_mtd:=l_3696_mtd + nvl(l_mtd_bal,0);
2598 l_3696_ytd:=l_3696_ytd + nvl(l_ytd_bal,0);
2599 hr_utility.set_location('l_3696_ptd:'||l_3696_ptd,12);
2600 hr_utility.set_location('l_3696_mtd:'||l_3696_mtd,12);
2601 hr_utility.set_location('l_3696_ytd:'||l_3696_ytd,12);
2602 end if;
2603 -- End Bug#13924112
2604 if t_code_val.exists(v_bal.bal_code) then
2605 hr_utility.set_location('Code'||v_bal.bal_code||' exists',12);
2606 t_code_val(v_bal.bal_code).ptd_val := nvl(t_code_val(v_bal.bal_code).ptd_val,0) + nvl(l_ptd_bal,0);
2607 t_code_val(v_bal.bal_code).mtd_val := nvl(t_code_val(v_bal.bal_code).mtd_val,0) + nvl(l_mtd_bal,0);
2608 t_code_val(v_bal.bal_code).ytd_val := nvl(t_code_val(v_bal.bal_code).ytd_val,0) + nvl(l_ytd_bal,0);
2609 else
2610 hr_utility.set_location('Code' ||v_bal.bal_code||' does not exists',12);
2611 t_code_val(v_bal.bal_code).ptd_val := l_ptd_bal;
2612 t_code_val(v_bal.bal_code).mtd_val := l_mtd_bal;
2613 t_code_val(v_bal.bal_code).ytd_val := l_ytd_bal;
2614 end if;
2615 t_code_val(v_bal.bal_code).ptd_group_val := t_code_val(v_bal.bal_code).ptd_val;
2616 t_code_val(v_bal.bal_code).mtd_group_val := t_code_val(v_bal.bal_code).mtd_val;
2617 t_code_val(v_bal.bal_code).ytd_group_val := t_code_val(v_bal.bal_code).ytd_val;
2618 hr_utility.set_location('t_code_val(v_bal.bal_code).ptd_val:'||t_code_val(v_bal.bal_code).ptd_val,12);
2619 hr_utility.set_location('t_code_val(v_bal.bal_code).mtd_val:'||t_code_val(v_bal.bal_code).mtd_val,12);
2620 hr_utility.set_location('t_code_val(v_bal.bal_code).ytd_val:'||t_code_val(v_bal.bal_code).ytd_val,12);
2621 hr_utility.set_location('t_code_val(v_bal.bal_code).ptd_group_val:'||t_code_val(v_bal.bal_code).ptd_group_val,12);
2622 hr_utility.set_location('t_code_val(v_bal.bal_code).mtd_group_val:'||t_code_val(v_bal.bal_code).mtd_group_val,12);
2623 hr_utility.set_location('t_code_val(v_bal.bal_code).ytd_group_val:'||t_code_val(v_bal.bal_code).ytd_group_val,12);
2624
2625 END LOOP Balance_Values;
2626
2627 -- Merge codes
2628 --
2629 IF (g_period_end_date < to_date('01-03-2012','DD-MM-YYYY')) THEN
2630 merge(t_code_val,3603,3601);
2631 merge(t_code_val,3610,3601);
2632 merge(t_code_val,3805,3801);
2633 merge(t_code_val,3806,3801);
2634 merge(t_code_val,3808,3801);
2635 merge(t_code_val,3809,3801);
2636 END IF;
2637 merge(t_code_val,3607,3601);
2638 merge(t_code_val,3604,3602);
2639 merge(t_code_val,3609,3602);
2640 merge(t_code_val,3612,3602);
2641 merge(t_code_val,3706,3713);
2642 merge(t_code_val,3710,3713);
2643 merge(t_code_val,3711,3713);
2644 merge(t_code_val,3712,3713);
2645 merge(t_code_val,3705,3714);
2646 merge(t_code_val,3709,3714);
2647 merge(t_code_val,3716,3714);
2648 merge(t_code_val,3803,3801);
2649 merge(t_code_val,3804,3801);
2650 merge(t_code_val,3807,3801);
2651 merge(t_code_val,4004,4003);
2652
2653 --Populate Code 4149 (i.e. Total Tax + SDL + UIF)
2654 populate_4149(t_code_val,l_4149_ptd,l_4149_mtd,l_4149_ytd);
2655
2656
2657
2658 /* added for bug 9780615 */
2659 /*
2660 if (t_code_val.exists(4103) ) then
2661 if (t_code_val(4103).ptd_group_val > 0
2662 and t_code_val(4101).ptd_group_val = 0
2663 and l_4102_ptd = 0)
2664 then
2665 -- Fix for bug#9876955
2666 -- t_code_val(4102).ptd_group_val := nvl(l_4102_ptd,0) + nvl(t_code_val(4103).ptd_group_val,0);
2667 t_code_val(4102).ptd_group_val := t_code_val(4102).ptd_group_val + nvl(l_4102_ptd,0) + nvl(t_code_val(4103).ptd_group_val,0);
2668 end if;
2669
2670 if (t_code_val(4103).mtd_group_val > 0
2671 and t_code_val(4101).mtd_group_val = 0
2672 and l_4102_mtd = 0)
2673 then
2674 -- Fix for bug#9876955
2675 -- t_code_val(4102).mtd_group_val := nvl(l_4102_mtd,0) + nvl(t_code_val(4103).mtd_group_val,0);
2676 t_code_val(4102).mtd_group_val := t_code_val(4102).mtd_group_val + nvl(l_4102_mtd,0) + nvl(t_code_val(4103).mtd_group_val,0);
2677 end if;
2678
2679 if (t_code_val(4103).ytd_group_val > 0
2680 and t_code_val(4101).ytd_group_val = 0
2681 and l_4102_ytd = 0)
2682 then
2683 -- Fix for bug#9876955
2684 -- t_code_val(4102).ytd_group_val := nvl(l_4102_ytd,0) + nvl(t_code_val(4103).ytd_group_val,0);
2685 t_code_val(4102).ytd_group_val := t_code_val(4102).ytd_group_val + nvl(l_4102_ytd,0) + nvl(t_code_val(4103).ytd_group_val,0);
2686 end if;
2687 end if;
2688 */
2689
2690 --Fix for bug#9955013
2691 /* if (t_code_val.exists(4103) ) then
2692 if (t_code_val(4103).ptd_group_val > 0 ) then
2693 t_code_val(4102).ptd_group_val := t_code_val(4102).ptd_group_val - l_4102_ptd + (t_code_val(4103).ptd_group_val - nvl(t_code_val(4101).ptd_group_val,0));
2694 end if;
2695 if (t_code_val(4103).mtd_group_val > 0 ) then
2696 t_code_val(4102).mtd_group_val := t_code_val(4102).mtd_group_val - l_4102_mtd + t_code_val(4103).mtd_group_val - nvl(t_code_val(4101).mtd_group_val,0);
2697 end if;
2698 if (t_code_val(4103).ytd_group_val > 0 ) then
2699 t_code_val(4102).ytd_group_val := t_code_val(4102).ytd_group_val - l_4102_ytd + t_code_val(4103).ytd_group_val - nvl(t_code_val(4101).ytd_group_val,0);
2700 end if;
2701 end if; */
2702
2703 -- Fix for bug#10129722
2704
2705 if (t_code_val.exists(4103) ) then
2706 t_code_val(4102).ptd_group_val := t_code_val(4102).ptd_group_val - l_4102_ptd + (nvl(t_code_val(4103).ptd_group_val,0) - nvl(t_code_val(4101).ptd_group_val,0));
2707 t_code_val(4102).mtd_group_val := t_code_val(4102).mtd_group_val - l_4102_mtd + (nvl(t_code_val(4103).mtd_group_val,0) - nvl(t_code_val(4101).mtd_group_val,0));
2708 t_code_val(4102).ytd_group_val := t_code_val(4102).ytd_group_val - l_4102_ytd + (nvl(t_code_val(4103).ytd_group_val,0) - nvl(t_code_val(4101).ytd_group_val,0));
2709 end if;
2710
2711
2712 -- Bug#13924112
2713 -- Adding Tax Code 3696 To TAX REGISTER REPORT
2714 t_code_val(3696).ptd_group_val := l_3696_ptd;
2715 t_code_val(3696).mtd_group_val := l_3696_mtd;
2716 t_code_val(3696).ytd_group_val := l_3696_ytd;
2717 t_code_val(3696).bal_name := 'Gross Non-Taxable Income';
2718 -- End Bug#13924112
2719 -- Bug 13717246
2720 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
2721
2722 open csr_balance_type_id;
2723 fetch csr_balance_type_id into l_balance_type_id_rfi_override;
2724 close csr_balance_type_id;
2725
2726 l_ptd_bal_rfi_override := nvl(
2727 ptd_value (
2728 p_asg_action_id => v_assignments.assignment_action_id
2729 , p_action_period_id => v_assignments.time_period_id
2730 , p_balance_type_id => l_balance_type_id_rfi_override
2731 , p_balance_name => 'RFI Override SARS Reporting'
2732 , p_effective_date => v_assignments.effective_date
2733 ),0);
2734
2735 l_mtd_bal_rfi_override := nvl(
2736 mtd_value (
2737 p_asg_action_id => v_assignments.assignment_action_id
2738 , p_balance_type_id => l_balance_type_id_rfi_override
2739 , p_balance_name => 'RFI Override SARS Reporting'
2740 , p_effective_date => v_assignments.effective_date
2741 ),0);
2742
2743 l_ytd_bal_rfi_override := nvl(
2744 ytd_value (
2745 p_asg_action_id => v_assignments.assignment_action_id
2746 , p_balance_type_id => l_balance_type_id_rfi_override
2747 , p_effective_date => v_assignments.effective_date
2748 ),0);
2749
2750 if not t_code_val.exists(3697) then
2751 if l_ytd_bal_rfi_override <> 0 then
2752 t_code_val(3697).ptd_group_val := l_ptd_bal_rfi_override;
2753 t_code_val(3697).mtd_group_val := l_mtd_bal_rfi_override;
2754 t_code_val(3697).ytd_group_val := l_ytd_bal_rfi_override;
2755 end if;
2756 end if;
2757
2758 if t_code_val.exists(3698) then
2759 if l_ytd_bal_rfi_override <> 0 then
2760 t_code_val(3698).ptd_group_val := nvl(t_code_val(3697).ptd_group_val,0) + nvl(t_code_val(3698).ptd_group_val,0) - l_ptd_bal_rfi_override;
2761 t_code_val(3698).mtd_group_val := nvl(t_code_val(3697).mtd_group_val,0) + nvl(t_code_val(3698).mtd_group_val,0) - l_mtd_bal_rfi_override;
2762 t_code_val(3698).ytd_group_val := nvl(t_code_val(3697).ytd_group_val,0) + nvl(t_code_val(3698).ytd_group_val,0) - l_ytd_bal_rfi_override;
2763 end if;
2764 else
2765 if l_ytd_bal_rfi_override <> 0 then
2766 t_code_val(3698).ptd_group_val := t_code_val(3697).ptd_group_val - l_ptd_bal_rfi_override;
2767 t_code_val(3698).mtd_group_val := t_code_val(3697).mtd_group_val - l_mtd_bal_rfi_override;
2768 t_code_val(3698).ytd_group_val := t_code_val(3697).ytd_group_val - l_ytd_bal_rfi_override;
2769 end if;
2770 end if;
2771
2772 if t_code_val.exists(3697) then
2773 if l_ytd_bal_rfi_override <> 0 then
2774
2775 l_ptd_bal_rfi_code := 0;
2776 l_mtd_bal_rfi_code := 0;
2777 l_ytd_bal_rfi_code := 0;
2778
2779 FOR v_bal_codes IN csr_balance_details_codes loop
2780
2781 l_ptd_bal_rfi_code := l_ptd_bal_rfi_code + nvl(
2782 ptd_value (
2783 p_asg_action_id => v_assignments.assignment_action_id
2784 , p_action_period_id => v_assignments.time_period_id
2785 , p_balance_type_id => v_bal_codes.bal_id
2786 , p_balance_name => v_bal_codes.bal_name
2787 , p_effective_date => v_assignments.effective_date
2788 ),0);
2789
2790 l_mtd_bal_rfi_code := l_mtd_bal_rfi_code + nvl(
2791 mtd_value (
2792 p_asg_action_id => v_assignments.assignment_action_id
2793 , p_balance_type_id => v_bal_codes.bal_id
2794 , p_balance_name => v_bal_codes.bal_name
2795 , p_effective_date => v_assignments.effective_date
2796 ),0);
2797
2798 l_ytd_bal_rfi_code := l_ytd_bal_rfi_code + nvl(
2799 ytd_value (
2800 p_asg_action_id => v_assignments.assignment_action_id
2801 , p_balance_type_id => v_bal_codes.bal_id
2802 , p_effective_date => v_assignments.effective_date
2803 ),0);
2804
2805 end loop Balance_Details;
2806
2807 t_code_val(3697).ptd_group_val := l_ptd_bal_rfi_override + l_ptd_bal_rfi_code;
2808 t_code_val(3697).mtd_group_val := l_mtd_bal_rfi_override + l_mtd_bal_rfi_code;
2809 t_code_val(3697).ytd_group_val := l_ytd_bal_rfi_override + l_ytd_bal_rfi_code;
2810
2811 end if;
2812
2813 end if;
2814
2815 -- RFI OVERRIDE FUNCTIONALITY FOR SARS REPORTING
2816
2817 --Create the register records
2818 --
2819 hr_utility.set_location('After Merging codes',12.1);
2820 l_code := t_code_val.first;
2821 while TRIM(l_code) is not null
2822 loop
2823 zvl(t_code_val(l_code).ptd_group_val);
2824 zvl(t_code_val(l_code).mtd_group_val);
2825 zvl(t_code_val(l_code).ytd_group_val);
2826 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',12.2);
2827 hr_utility.set_location('Code :'||l_code||' PTD:'||t_code_val(l_code).ptd_group_val||' MTD:'||t_code_val(l_code).mtd_group_val||' YTD:'||t_code_val(l_code).ytd_group_val,12.2);
2828 IF valid_record_01032009 (
2829 p_ptd_bal => t_code_val(l_code).ptd_group_val
2830 , p_mtd_bal => t_code_val(l_code).mtd_group_val
2831 , p_ytd_bal => t_code_val(l_code).ytd_group_val
2832 , p_code => l_code
2833 , p_desc => t_code_val(l_code).bal_name
2834 )
2835 THEN
2836 --
2837 --get the correct SARS Code for directors and foreign income
2838 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',12.3);
2839 l_bal_code := get_sars_code(
2840 p_sars_code => l_code
2841 , p_foreign_income => l_asg_foreign_income
2842 , p_nature => l_nature
2843 );
2844 if t_code_val(l_code).included_in is null then
2845 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',12.4);
2846 if (p_assactid IS NULL ) THEN
2847 ins_register (
2848 p_full_name => v_assignments.full_name
2849 , p_employee_number => v_assignments.employee_number
2850 , p_person_id => v_assignments.person_id
2851 , p_date_of_birth => v_assignments.date_of_birth
2852 , p_age => v_assignments.age
2853 , p_tax_reference_no => v_assignments.tax_reference_number
2854 , p_cmpy_tax_reference_no => v_assignments.cmpy_tax_reference_number
2855 , p_tax_status => l_asg_tax_status
2856 , p_tax_directive_value => l_asg_dir_value
2857 , p_days_worked => l_asg_dys_worked
2858 , p_assignment_id => v_assignments.assignment_id
2859 , p_assignment_action_id => v_assignments.assignment_action_id
2860 , p_assignment_number => v_assignments.assignment_number
2861 , p_assignment_start_date => l_asg_start_date
2862 , p_assignment_end_date => l_asg_end_date
2863 , p_bal_name => t_code_val(l_code).bal_name
2864 , p_bal_code => l_bal_code
2865 , p_tot_ptd => t_code_val(l_code).ptd_group_val
2866 , p_tot_mtd => t_code_val(l_code).mtd_group_val
2867 , p_tot_ytd => t_code_val(l_code).ytd_group_val
2868 );
2869 ELSE
2870 pay_action_information_api.create_action_information(
2871 p_action_information_id => l_action_id,
2872 p_object_version_number => l_ovn,
2873 p_action_information_category => 'ZA_TAX_REG_REP',
2874 p_action_context_id => p_assactid,
2875 p_action_context_type => 'AAP',
2876 p_assignment_id => v_assignments.assignment_id,
2877 p_effective_date => null,
2878 p_action_information1 => v_assignments.full_name,
2879 p_action_information2 => v_assignments.employee_number,
2880 p_action_information3 => v_assignments.person_id,
2881 p_action_information4 => fnd_date.date_to_displaydate(v_assignments.date_of_birth),
2882 p_action_information5 => v_assignments.age,
2883 p_action_information6 => v_assignments.tax_reference_number,
2884 p_action_information7 => v_assignments.cmpy_tax_reference_number,
2885 p_action_information8 => l_asg_tax_status,
2886 p_action_information9 => l_asg_dir_value,
2887 p_action_information10 => l_asg_dys_worked,
2888 p_action_information11 => v_assignments.assignment_id,
2889 p_action_information12 => v_assignments.assignment_action_id,
2890 p_action_information13 => v_assignments.assignment_number,
2891 p_action_information14 => fnd_date.date_to_displaydate(l_asg_start_date),
2892 p_action_information15 => fnd_date.date_to_displaydate(l_asg_end_date),
2893 p_action_information16 => t_code_val(l_code).bal_name,
2894 p_action_information17 => l_bal_code,
2895 p_action_information18 => t_code_val(l_code).ptd_group_val,
2896 p_action_information19 => t_code_val(l_code).mtd_group_val,
2897 p_action_information20 => t_code_val(l_code).ytd_group_val,
2898 p_action_information21 => p_payroll_id,
2899 p_action_information22 => p_end_period_id
2900 );
2901 END IF;
2902 end if;
2903 END IF; -- valid record
2904 if l_code = t_code_val.last THEN
2905 l_code := NULL;
2906 else
2907 l_code := t_code_val.next(l_code);
2908 end if;
2909 end loop;
2910 END IF; -- Include Assignment
2911 END LOOP Processed_Assignments;
2912 --
2913 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',13);
2914
2915
2916
2917 ---------------------
2918 -- Set out Parameters
2919 ---------------------
2920 p_tax_register_id := g_tax_register_id;
2921 p_payroll_name := g_payroll_name;
2922 p_period_num := g_period_num;
2923 p_period_start_date := g_period_start_date;
2924 p_period_end_date := g_period_end_date;
2925 p_tot_employees := total_employees;
2926 p_tot_assignments := total_assignments;
2927 EXCEPTION
2928 WHEN OTHERS THEN
2929 hr_utility.set_location('py_za_tax_reg.pre_process_01032009',14);
2930 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
2931 hr_utility.raise_error;
2932 -------------------------------------------------------------------------------
2933 END pre_process_01032009;-- END --
2934 -------------------------------------------------------------------------------
2935
2936 -- bug 13444804
2937
2938 PROCEDURE archive_set_globals (
2939 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
2940 , p_start_period_id IN per_time_periods.time_period_id%TYPE
2941 , p_end_period_id IN per_time_periods.time_period_id%TYPE
2942 , p_include IN VARCHAR2
2943 , p_retrieve_ptd IN VARCHAR2
2944 , p_retrieve_mtd IN VARCHAR2
2945 , p_retrieve_ytd IN VARCHAR2
2946 , p_tax_year IN VARCHAR2
2947 )
2948 AS
2949
2950 BEGIN
2951 -- Set Globals
2952 set_globals (
2953 p_payroll_id => p_payroll_id
2954 , p_start_period_id => p_start_period_id
2955 , p_end_period_id => p_end_period_id
2956 , p_include => p_include
2957 , p_retrieve_ptd => p_retrieve_ptd
2958 , p_retrieve_mtd => p_retrieve_mtd
2959 , p_retrieve_ytd => p_retrieve_ytd
2960 );
2961
2962 IF ( p_tax_year > '2009') THEN
2963 -- fetch code descriptions
2964 fetch_code_desc;
2965 END IF;
2966
2967 END archive_set_globals;
2968
2969 -- bug 13444804
2970
2971 -------------------------------------------------------------------------------
2972 END py_za_tax_reg;-- END OF PACKAGE --