[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_TAX_REG
Source
1 PACKAGE BODY py_za_tax_reg AS
2 /* $Header: pyzatreg.pkb 120.1 2006/06/13 10:14:45 amahanty noship $ */
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 A. Mahanty 13/06/2006 115.22 5330452 Modified the cursor
12 csr_processed_assignments. The query was
13 modified to pick up the correct action_sequence.
14 (choosing max payroll_action_id may give incorrect
15 balance values in some cases)
16 Secure views were used for Performance enhancement.
17 A. Mahanty 14/04/2005 115.21 3491357 BRA Enh. Balance Value retrieval
18 modified.
19 J.N. Louw 23/06/2004 115.20 3694450 Modified assignment_nature
20 to reference fnd_lookup_values
21 instead of hr_lookups
22 R. Pahune 09/02/2004 115.19 3400581 Modified the cursor
23 csr_processed_assignments.
24 N. Venugopal 09/01/2004 115.18 3221746 removed set serverout on for gscc compliance.
25 N. Venugopal 07/01/2004 115.17 3221746 Code changes for performace improvement.
26 N. Venugopal 11/08/2003 115.16 3069004 Modified cursor csr_irp5_balances.
27 L. Kloppers 23/12/2002 115.15 2720082 Modified the cursors:
28 csr_processed_assignments to
29 select assignments only if they are on the
30 chosen payroll in the specified payroll period
31 for which the Tax Register is being run, and
32 csr_irp5_balances to
33 select lump sum balances for an assignment, even
34 where they were paid in earlier payrolls that
35 the assignment was on.
36 A.Sengar 10/12/2002 115.14 2665394 Modified the cursor
37 csr_processed_assignments to
38 improve the performance of the
39 select statement.
40 L. Kloppers 23/09/2002 115.11 2224332 Added Procedure assignment_nature
41 Modified Procedure pre_process to call
42 py_za_tax_certificates.get_sars_code
43 for correct saving of balance codes for
44 Foreign- and Directors Income
45 Removed DEFAULT NULL for two parameters in
46 public procedure pre_process as per gscc
47 J.N. Louw 29/05/2002 115.9 1858619 Fixing QA raised issues
48 2377480 Legal Entity fetch per
49 assignment and not per
50 organization
51 J.N. Louw 28/02/2002 115.8 Added
52 hr_utility calls
53 Removed
54 record creation for
55 assignment with no
56 balance values
57 J.N. Louw 04/02/2002 115.7 Added
58 include_assignment
59 J.N. Louw 25/01/2002 115.5 1756600 Register was updated to
60 1756617 accommodate bug changes
61 1858619 and merge of both
62 2117507 current and terminated
63 2132644 assignments reports
64 L. Kloppers 01-Mar-2001 115.4 Changed
65 per_assignment_status_types_tl
66 back to
67 per_assignment_status_types
68 and use PER_SYSTEM_STATUS
69 i.s.o. USER_STATUS
70 L. Kloppers 23-Feb-2001 115.3 Changed
71 per_assignment_status_types
72 to
73 per_assignment_status_types_tl
74 L. Kloppers 06-Feb-2001 115.2 Changed "end_date"
75 to "ptp.end_date"
76 L. Kloppers 31-Jan-2001 115.1 Changed attribute1
77 to prd_information1
78 A vd Berg 22-Jan-2001 110.11 Amended Version Number
79 G. Fraser 10-Nov-2000 110.8 Changed Termination
80 Assignment Cursor
81 G. Fraser 24-May-2000 110.3-7 Speed improvements
82 L.J.Kloppers 23-Feb-2000 110.2 Added p_tax_register_id
83 IN OUT NOCOPY parameter
84 L.J.Kloppers 13-Feb-2000 110.1 Added p_total_employees
85 and p_total_assignments
86 IN OUT NOCOPY parameters
87 L.J.Kloppers 12-Feb-2000 110.0 Initial Version
88 */
89
90 -------------------------------------------------------------------------------
91 -- PACKAGE BODY --
92 -------------------------------------------------------------------------------
93
94 ------------------
95 -- Package Globals
96 ------------------
97 g_tax_register_id pay_za_tax_registers.tax_register_id%TYPE;
98 g_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
99 g_start_period_id per_time_periods.time_period_id%TYPE;
100 g_end_period_id per_time_periods.time_period_id%TYPE;
101 g_period_num per_time_periods.period_num%TYPE;
102 g_period_start_date per_time_periods.start_date%TYPE;
103 g_period_end_date per_time_periods.end_date%TYPE;
104 g_payroll_name pay_all_payrolls_f.payroll_name%TYPE;
105 g_include_asg VARCHAR2(1);
106 g_retrieve_ptd BOOLEAN;
107 g_retrieve_mtd BOOLEAN;
108 g_retrieve_ytd BOOLEAN;
109 --
110 -------------------------------------------------------------------------------
111 -- zeroval
112 -------------------------------------------------------------------------------
113 PROCEDURE zvl (
114 p_val IN OUT NOCOPY NUMBER
115 )
116 AS
117 -------------------------------------------------------------------------------
118 BEGIN -- MAIN --
119 -------------------------------------------------------------------------------
120 hr_utility.set_location('py_za_tax_reg.zvl',1);
121
122 IF p_val IS NOT NULL THEN
123 IF p_val = 0 THEN
124 p_val := NULL;
125 END IF;
126 END IF;
127
128 hr_utility.set_location('py_za_tax_reg.zvl',2);
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 hr_utility.set_location('py_za_tax_reg.zvl',3);
133 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
134 hr_utility.raise_error;
135 -------------------------------------------------------------------------------
136 END zvl;
137
138 -------------------------------------------------------------------------------
139 -- valid_record
140 -------------------------------------------------------------------------------
141 FUNCTION valid_record (
142 p_ptd_bal IN NUMBER DEFAULT NULL
143 , p_mtd_bal IN NUMBER DEFAULT NULL
144 , p_ytd_bal IN NUMBER DEFAULT NULL
145 )
146 RETURN BOOLEAN
147 AS
148 ------------
149 -- Variables
150 ------------
151 l_check_val VARCHAR2(1) := 'X';
152 l_ret_val BOOLEAN DEFAULT FALSE;
153 ------------
154 -------------------------------------------------------------------------------
155 BEGIN -- MAIN --
156 -------------------------------------------------------------------------------
157 hr_utility.set_location('py_za_tax_reg.valid_record',1);
158
159 IF nvl(
160 to_char(
161 nvl(
162 nvl( p_ptd_bal
163 , p_mtd_bal
164 )
165 , p_ytd_bal
166 )
167 )
168 , l_check_val
169 ) <> l_check_val
170 THEN
171 hr_utility.set_location('py_za_tax_reg.valid_record',2);
172 l_ret_val := TRUE;
173 END IF;
174
175 hr_utility.set_location('py_za_tax_reg.valid_record',3);
176 RETURN l_ret_val;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 hr_utility.set_location('py_za_tax_reg.valid_record',4);
181 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
182 hr_utility.raise_error;
183 -------------------------------------------------------------------------------
184 END valid_record;
185
186 -------------------------------------------------------------------------------
187 -- balance_id
188 -------------------------------------------------------------------------------
189 FUNCTION balance_id (
190 p_balance_name IN pay_balance_types.balance_name%TYPE
191 )
192 RETURN pay_balance_types.balance_type_id%TYPE
193 AS
194 ---------
195 -- Cursor
196 ---------
197 CURSOR csr_balance_id (
198 p_balance_name IN pay_balance_types.balance_name%TYPE
199 )
200 IS
201 SELECT
202 pbt.balance_type_id
203 FROM
204 pay_balance_types pbt
205 WHERE
206 pbt.balance_name = p_balance_name
207 AND pbt.business_group_id IS NULL
208 AND pbt.legislation_code = 'ZA';
209
210 ------------
211 -- Variables
212 ------------
213 l_retval pay_balance_types.balance_type_id%TYPE;
214
215 -------------------------------------------------------------------------------
216 BEGIN -- MAIN --
217 -------------------------------------------------------------------------------
218 hr_utility.set_location('py_za_tax_reg.balance_id',1);
219
220 OPEN csr_balance_id(p_balance_name);
221 FETCH csr_balance_id INTO l_retval;
222 CLOSE csr_balance_id;
223
224 hr_utility.set_location('py_za_tax_reg.balance_id',2);
225 RETURN l_retval;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 hr_utility.set_location('py_za_tax_reg.balance_id',3);
230 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
231 hr_utility.raise_error;
232 -------------------------------------------------------------------------------
233 END balance_id;
234
235 -------------------------------------------------------------------------------
236 -- ptd_value
237 -------------------------------------------------------------------------------
238 FUNCTION ptd_value (
239 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
240 , p_action_period_id IN per_time_periods.time_period_id%TYPE
241 , p_balance_type_id IN pay_balance_types.balance_type_id%TYPE
242 , p_balance_name IN pay_za_irp5_bal_codes.full_balance_name%TYPE
243 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
244 )
245 RETURN NUMBER AS
246 ------------
247 -- Variables
248 ------------
249 l_ptd_value NUMBER;
250 --
251 -------------------------------------------------------------------------------
252 BEGIN -- MAIN --
253 -------------------------------------------------------------------------------
254 hr_utility.set_location('py_za_tax_reg.ptd_value',1);
255 -- Check if the PTD value must be retrieved
256 --
257 IF g_retrieve_ptd THEN
258 hr_utility.set_location('py_za_tax_reg.ptd_value',2);
259 -- PTD value of Site and Paye Amount not necessary
260 --
261 IF UPPER(p_balance_name) NOT IN ('SITE','PAYE') THEN
262 hr_utility.set_location('py_za_tax_reg.ptd_value',3);
263 -- Is the assignment's action in the current period
264 --
265 IF g_end_period_id = p_action_period_id THEN
266 hr_utility.set_location('py_za_tax_reg.ptd_value',4);
267 -- Retrieve the value
268 --3491357
269 /*l_ptd_value := py_za_bal.calc_asg_tax_ptd_action (
270 p_asg_action_id
271 , p_balance_type_id
272 , p_effective_date
273 );*/
274 l_ptd_value := py_za_bal.get_balance_value_action (
275 p_asg_action_id
276 , p_balance_type_id
277 , '_ASG_TAX_PTD'
278 );
279 END IF;
280 END IF;
281 END IF;
282 hr_utility.set_location('py_za_tax_reg.ptd_value',5);
283 zvl(l_ptd_value);
284 hr_utility.set_location('py_za_tax_reg.ptd_value',6);
285 -- Return
286 RETURN l_ptd_value;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 hr_utility.set_location('py_za_tax_reg.ptd_value',7);
291 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
292 hr_utility.raise_error;
293 -------------------------------------------------------------------------------
294 END ptd_value;
295
296 -------------------------------------------------------------------------------
297 -- mtd_value
298 -------------------------------------------------------------------------------
299 FUNCTION mtd_value (
300 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
301 , p_balance_type_id IN pay_balance_types.balance_type_id%TYPE
302 , p_balance_name IN pay_za_irp5_bal_codes.full_balance_name%TYPE
303 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
304 )
305 RETURN NUMBER AS
306 ------------
307 -- Variables
308 ------------
309 l_mtd_value NUMBER;
310 --
311 -------------------------------------------------------------------------------
312 BEGIN -- MAIN --
313 -------------------------------------------------------------------------------
314 hr_utility.set_location('py_za_tax_reg.mtd_value',1);
315 -- Check if the MTD value must be retrieved
316 --
317 IF g_retrieve_mtd THEN
318 hr_utility.set_location('py_za_tax_reg.mtd_value',2);
319 -- PTD value of Site and Paye Amount not necessary
320 --
321 IF UPPER(p_balance_name) NOT IN ('SITE','PAYE') THEN
322 hr_utility.set_location('py_za_tax_reg.mtd_value',3);
323 -- Is the effective date of the action in the current period
324 --
325 IF p_effective_date between g_period_start_date
326 and g_period_end_date
327 THEN
328 hr_utility.set_location('py_za_tax_reg.mtd_value',4);
329 -- Retrieve the value
330 --3491357
331 /*l_mtd_value := py_za_bal.calc_asg_tax_mtd_action (
332 p_asg_action_id
333 , p_balance_type_id
334 , p_effective_date
335 );*/
336 l_mtd_value := py_za_bal.get_balance_value_action (
337 p_asg_action_id
338 , p_balance_type_id
339 , '_ASG_TAX_MTD'
340 );
341 END IF;
342 END IF;
343 END IF;
344 hr_utility.set_location('py_za_tax_reg.mtd_value',5);
345 zvl(l_mtd_value);
346 hr_utility.set_location('py_za_tax_reg.mtd_value',6);
347 -- Return
348 RETURN l_mtd_value;
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 hr_utility.set_location('py_za_tax_reg.mtd_value',7);
353 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
354 hr_utility.raise_error;
355 -------------------------------------------------------------------------------
356 END mtd_value;
357
358 -------------------------------------------------------------------------------
359 -- ytd_value
360 -------------------------------------------------------------------------------
361 FUNCTION ytd_value (
362 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
363 , p_balance_type_id IN pay_balance_types.balance_type_id%TYPE
364 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
365 )
366 RETURN NUMBER AS
367 ------------
368 -- Variables
369 ------------
370 l_ytd_value NUMBER;
371 --
372 -------------------------------------------------------------------------------
373 BEGIN -- MAIN --
374 -------------------------------------------------------------------------------
375 hr_utility.set_location('py_za_tax_reg.ytd_value',1);
376 -- Check if the YTD value must be retrieved
377 --
378 IF g_retrieve_ytd THEN
379 hr_utility.set_location('py_za_tax_reg.ytd_value',2);
380 -- Retrieve the value
381 --3491357
382 /*l_ytd_value := py_za_bal.calc_asg_tax_ytd_action (
383 p_asg_action_id
384 , p_balance_type_id
385 , p_effective_date
386 );*/
387 l_ytd_value := py_za_bal.get_balance_value_action (
388 p_asg_action_id
389 , p_balance_type_id
390 , '_ASG_TAX_YTD'
391 );
392 END IF;
393 hr_utility.set_location('py_za_tax_reg.ytd_value',3);
394 zvl(l_ytd_value);
395 hr_utility.set_location('py_za_tax_reg.ytd_value',4);
396 -- Return
397 RETURN l_ytd_value;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 hr_utility.set_location('py_za_tax_reg.ytd_value',5);
402 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
403 hr_utility.raise_error;
404 -------------------------------------------------------------------------------
405 END ytd_value;
406
407 -------------------------------------------------------------------------------
408 -- run_result_value
409 -------------------------------------------------------------------------------
410 FUNCTION run_result_value (
411 p_element_name IN pay_element_types_f.element_name%TYPE
412 , p_value_name IN pay_input_values_f.name%TYPE
413 , p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
414 , p_run_result_id IN OUT NOCOPY pay_run_results.run_result_id%TYPE
415 )
416 RETURN pay_run_result_values.result_value%TYPE
417 AS
418 ---------
419 -- Cursor
420 ---------
421 CURSOR csr_result_value
422 IS
423 SELECT
424 prrv.result_value
425 , prrv.run_result_id
426 FROM
427 pay_element_types_f pet
428 , pay_input_values_f piv
429 , pay_run_results prr
430 , pay_run_result_values prrv
431 WHERE
432 pet.element_name = p_element_name
433 AND pet.legislation_code = 'ZA'
434 AND pet.element_type_id = piv.element_type_id
435 AND piv.name = p_value_name
436 AND piv.input_value_id = prrv.input_value_id
437 AND prr.element_type_id = pet.element_type_id
438 AND prr.run_result_id = prrv.run_result_id
439 AND prr.assignment_action_id =
440 (
441 SELECT
442 MAX(paa2.assignment_action_id)
443 FROM
444 pay_run_results prr2
445 , pay_assignment_actions paa2
446 , pay_payroll_actions ppa2
447 WHERE
448 prr2.element_type_id = pet.element_type_id
449 AND prr2.run_result_id = nvl(p_run_result_id, prr2.run_result_id)
450 AND prr2.assignment_action_id = paa2.assignment_action_id
451 AND paa2.assignment_id = p_assignment_id
452 AND paa2.payroll_action_id = ppa2.payroll_action_id
453 AND ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
454 AND ppa2.time_period_id BETWEEN g_start_period_id
455 AND g_end_period_id
456 );
457
458 ------------
459 -- Variables
460 ------------
461 l_result_value csr_result_value%ROWTYPE;
462 -------------------------------------------------------------------------------
463 BEGIN -- MAIN --
464 -------------------------------------------------------------------------------
465 hr_utility.set_location('py_za_tax_reg.run_result_value',1);
466 OPEN csr_result_value;
467 FETCH csr_result_value INTO l_result_value;
468 CLOSE csr_result_value;
469 --
470 hr_utility.set_location('py_za_tax_reg.run_result_value',2);
471 p_run_result_id := l_result_value.run_result_id;
472 RETURN l_result_value.result_value;
473 --
474 EXCEPTION
475 WHEN OTHERS THEN
476 hr_utility.set_location('py_za_tax_reg.run_result_value',3);
477 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
478 hr_utility.raise_error;
479 -------------------------------------------------------------------------------
480 END run_result_value;
481
482 -------------------------------------------------------------------------------
483 -- run_result_value
484 -- Overloaded version of the function where the run_result_id us known
485 -------------------------------------------------------------------------------
486 FUNCTION run_result_value (
487 p_value_name IN pay_input_values_f.name%TYPE
488 , p_run_result_id IN pay_run_results.run_result_id%TYPE
489 )
490 RETURN pay_run_result_values.result_value%TYPE
491 AS
492 ---------
493 -- Cursor
494 ---------
495 CURSOR csr_result_value
496 IS
497 SELECT
498 prrv.result_value
499 FROM
500 pay_run_results prr
501 , pay_input_values_f piv
502 , pay_run_result_values prrv
503 WHERE
504 prr.run_result_id = p_run_result_id
505 AND prr.element_type_id = piv.element_type_id
506 AND piv.name = p_value_name
507 AND piv.input_value_id = prrv.input_value_id
508 AND prr.run_result_id = prrv.run_result_id;
509
510 ------------
511 -- Variables
512 ------------
513 l_result_value csr_result_value%ROWTYPE;
514 -------------------------------------------------------------------------------
515 BEGIN -- MAIN --
516 -------------------------------------------------------------------------------
517 hr_utility.set_location('py_za_tax_reg.run_result_value',4);
518 OPEN csr_result_value;
519 FETCH csr_result_value INTO l_result_value;
520 CLOSE csr_result_value;
521 --
522 hr_utility.set_location('py_za_tax_reg.run_result_value',5);
523 RETURN l_result_value.result_value;
524 --
525 EXCEPTION
526 WHEN OTHERS THEN
527 hr_utility.set_location('py_za_tax_reg.run_result_value',6);
528 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
529 hr_utility.raise_error;
530 -------------------------------------------------------------------------------
531 END run_result_value;
532
533 -------------------------------------------------------------------------------
534 -- decode_lookup_code
535 -------------------------------------------------------------------------------
536 FUNCTION decode_lookup_code (
537 p_lookup_type IN hr_lookups.lookup_type%TYPE
538 , p_lookup_code IN hr_lookups.lookup_code%TYPE
539 , p_application_id IN hr_lookups.application_id%TYPE
540 )
541 RETURN hr_lookups.meaning%TYPE AS
542 ---------
543 -- Cursor
544 ---------
545 CURSOR csr_lookup_meaning
546 IS
547 SELECT hl.meaning
548 FROM hr_lookups hl
549 WHERE hl.lookup_type = p_lookup_type
550 AND hl.lookup_code = p_lookup_code
551 AND hl.application_id = p_application_id;
552 --
553 ------------
554 -- Variables
555 ------------
556 l_meaning hr_lookups.meaning%TYPE;
557 --
558 -------------------------------------------------------------------------------
559 BEGIN -- MAIN --
560 -------------------------------------------------------------------------------
561 hr_utility.set_location('py_za_tax_reg.decode_lookup_code',1);
562 OPEN csr_lookup_meaning;
563 FETCH csr_lookup_meaning INTO l_meaning;
564 CLOSE csr_lookup_meaning;
565
566 hr_utility.set_location('py_za_tax_reg.decode_lookup_code',2);
567 RETURN l_meaning;
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 hr_utility.set_location('py_za_tax_reg.decode_lookup_code',3);
572 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
573 hr_utility.raise_error;
574 -------------------------------------------------------------------------------
575 END decode_lookup_code;
576
577 -------------------------------------------------------------------------------
578 -- assignment_tax_status_directive
579 -------------------------------------------------------------------------------
580 PROCEDURE assignment_tax_sta_dir (
581 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
582 , p_asg_tax_status OUT NOCOPY hr_lookups.meaning%TYPE
583 , p_asg_dir_value OUT NOCOPY pay_run_result_values.result_value%TYPE
584 , p_asg_tax_status_code OUT NOCOPY hr_lookups.lookup_code%TYPE
585 )
586 AS
587 ------------
588 -- Variables
589 ------------
590 l_tax_status hr_lookups.meaning%TYPE;
591 l_dir_value pay_run_result_values.result_value%TYPE;
592 l_run_result_id pay_run_results.run_result_id%TYPE;
593 l_asg_tax_status_code hr_lookups.lookup_code%TYPE;
594
595 -------------------------------------------------------------------------------
596 BEGIN -- MAIN --
597 -------------------------------------------------------------------------------
598 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',1);
599 --
600 l_tax_status := run_result_value (
601 p_element_name => 'ZA_Tax'
602 , p_value_name => 'Tax Status'
603 , p_assignment_id => p_assignment_id
604 , p_run_result_id => l_run_result_id
605 );
606 --
607 l_asg_tax_status_code := l_tax_status;
608 --
609 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',2);
610 --
611 l_tax_status := decode_lookup_code (
612 p_lookup_type => 'ZA_TAX_STATUS'
613 , p_lookup_code => l_tax_status
614 , p_application_id => 800
615 );
616 --
617 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',3);
618 --
619 IF l_run_result_id IS NOT NULL THEN
620 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',4);
621 -- Find the directive value for the same result id
622 l_dir_value := run_result_value (
623 p_value_name => 'Tax Directive Value'
624 , p_run_result_id => l_run_result_id
625 );
626 END IF;
627 --
628 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',5);
629 --
630 p_asg_tax_status := l_tax_status;
631 p_asg_dir_value := l_dir_value;
632 p_asg_tax_status_code := l_asg_tax_status_code;
633
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 hr_utility.set_location('py_za_tax_reg.assignment_tax_sta_dir',6);
638 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
639 hr_utility.raise_error;
640 -------------------------------------------------------------------------------
641 END assignment_tax_sta_dir;
642
643 -------------------------------------------------------------------------------
644 -- assignment_nature
645 -------------------------------------------------------------------------------
646 PROCEDURE assignment_nature (
647 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
648 , p_effective_date IN DATE
649 , p_asg_nature OUT NOCOPY hr_lookups.meaning%TYPE
650 )
651 AS
652 ------------
653 -- Variables
654 ------------
655
656 -----------------------------------------------------------------
657 -- Cursor csr_asg_nature
658 -----------------------------------------------------------------
659 CURSOR csr_asg_nature (
660 c_assignment_id IN per_all_assignments_f.assignment_id%TYPE
661 , c_effective_date IN DATE
662 )
663 IS
664 SELECT
665 nvl(fcl.meaning, 'A') nature
666 FROM
667 per_all_assignments_f ass
668 , per_assignment_extra_info aei
669 , fnd_lookup_values fcl
670 WHERE ass.assignment_id = c_assignment_id
671 AND ass.effective_start_date =
672 (
673 SELECT max(paf2.effective_start_date)
674 FROM per_all_assignments_f paf2
675 WHERE paf2.assignment_id = ass.assignment_id
676 AND paf2.effective_start_date <= c_effective_date
677 )
678 AND ass.assignment_id = aei.assignment_id(+)
679 AND aei.aei_information_category = 'ZA_SPECIFIC_INFO'
680 AND fcl.lookup_type(+) = 'ZA_PER_NATURES'
681 AND fcl.lookup_code(+) = aei.aei_information4
682 AND fcl.language(+) = 'US';
683
684
685 l_nature hr_lookups.meaning%TYPE;
686
687 -------------------------------------------------------------------------------
688 BEGIN -- MAIN --
689 -------------------------------------------------------------------------------
690 hr_utility.set_location('py_za_tax_reg.assignment_nature',1);
691 --
692 FOR v_asg_nature IN csr_asg_nature
693 ( c_assignment_id => p_assignment_id
694 , c_effective_date => p_effective_date
695 )
696 LOOP
697
698 l_nature := v_asg_nature.nature;
699
700 END LOOP csr_asg_nature;
701
702 IF l_nature IS NULL THEN
703
704 l_nature := 'A';
705
706 END IF;
707 --
708 hr_utility.set_location('py_za_tax_reg.assignment_nature',2);
709 --
710 p_asg_nature := l_nature;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 hr_utility.set_location('py_za_tax_reg.assignment_nature',3);
715 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
716 hr_utility.raise_error;
717 -------------------------------------------------------------------------------
718 END assignment_nature;
719
720 -------------------------------------------------------------------------------
721 -- assignment_dys_worked
722 -------------------------------------------------------------------------------
723 FUNCTION assignment_dys_worked (
724 p_asg_tax_status IN hr_lookups.meaning%TYPE
725 , p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
726 , p_effective_date IN pay_payroll_actions.effective_date%TYPE
727 )
728 RETURN NUMBER
729 AS
730 ------------
731 -- Variables
732 ------------
733 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
734 l_balance_value NUMBER;
735 -------------------------------------------------------------------------------
736 BEGIN -- MAIN --
737 -------------------------------------------------------------------------------
738 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',1);
739 IF p_asg_tax_status = 'Seasonal Worker' THEN
740 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',2);
741 --
742 l_bal_type_id :=
743 balance_id (
744 p_balance_name => 'Total Seasonal Workers Days Worked'
745 );
746 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',3);
747 l_balance_value :=
748 ytd_value (
749 p_asg_action_id => p_asg_action_id
750 , p_balance_type_id => l_bal_type_id
751 , p_effective_date => p_effective_date
752 );
753 END IF;
754
755 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',4);
756 zvl(l_balance_value);
757 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',5);
758 -- Return
759 RETURN l_balance_value;
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 hr_utility.set_location('py_za_tax_reg.assignment_dys_worked',6);
764 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
765 hr_utility.raise_error;
766 -------------------------------------------------------------------------------
767 END assignment_dys_worked;
768
769 -------------------------------------------------------------------------------
770 -- assignment_start_date
771 -------------------------------------------------------------------------------
772 FUNCTION assignment_start_date (
773 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
774 )
775 RETURN DATE AS
776 ---------
777 -- Cursor
778 ---------
779 CURSOR csr_assignment_start_date
780 IS
781 SELECT MIN(per.effective_start_date)
782 FROM per_all_assignments_f per
783 , per_assignment_status_types past
784 WHERE per.assignment_id = p_assignment_id
785 AND per.assignment_status_type_id = past.assignment_status_type_id
786 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
787
788 ------------
789 -- Variables
790 ------------
791 /*<variabel_name> <datatype> DEFAULT <default_value>*/
792 l_date per_all_assignments_f.effective_start_date%TYPE;
793 -------------------------------------------------------------------------------
794 BEGIN -- MAIN --
795 -------------------------------------------------------------------------------
796 hr_utility.set_location('py_za_tax_reg.assignment_start_date',1);
797 OPEN csr_assignment_start_date;
798 FETCH csr_assignment_start_date INTO l_date;
799 CLOSE csr_assignment_start_date;
800
801 hr_utility.set_location('py_za_tax_reg.assignment_start_date',2);
802 RETURN l_date;
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 hr_utility.set_location('py_za_tax_reg.assignment_start_date',3);
807 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
808 hr_utility.raise_error;
809 -------------------------------------------------------------------------------
810 END assignment_start_date;
811
812 -------------------------------------------------------------------------------
813 -- assignment_end_date
814 -------------------------------------------------------------------------------
815 FUNCTION assignment_end_date (
816 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
817 )
818 RETURN DATE AS
819 ---------
820 -- Cursor
821 ---------
822 CURSOR csr_assignment_end_date
823 IS
824 SELECT MAX(per.effective_end_date)
825 FROM per_all_assignments_f per
826 , per_assignment_status_types past
827 WHERE per.assignment_id = p_assignment_id
828 AND per.assignment_status_type_id = past.assignment_status_type_id
829 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
830
831 ------------
832 -- Variables
833 ------------
834 /*<variabel_name> <datatype> DEFAULT <default_value>*/
835 l_date per_all_assignments_f.effective_start_date%TYPE;
836 -------------------------------------------------------------------------------
837 BEGIN -- MAIN --
838 -------------------------------------------------------------------------------
839 hr_utility.set_location('py_za_tax_reg.assignment_end_date',1);
840
841 OPEN csr_assignment_end_date;
842 FETCH csr_assignment_end_date INTO l_date;
843 CLOSE csr_assignment_end_date;
844
845 hr_utility.set_location('py_za_tax_reg.assignment_end_date',2);
846 RETURN l_date;
847
848 EXCEPTION
849 WHEN OTHERS THEN
850 hr_utility.set_location('py_za_tax_reg.assignment_end_date',3);
851 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
852 hr_utility.raise_error;
853 -------------------------------------------------------------------------------
854 END assignment_end_date;
855
856 -------------------------------------------------------------------------------
857 -- include_assignment
858 -------------------------------------------------------------------------------
859 FUNCTION include_assignment (
860 p_asg_id IN per_all_assignments_f.assignment_id%TYPE
861 , p_asg_start_date OUT NOCOPY per_all_assignments_f.effective_start_date%TYPE
862 , p_asg_end_date OUT NOCOPY per_all_assignments_f.effective_end_date%TYPE
863 )
864 RETURN BOOLEAN AS
865 ------------
866 -- Variables
867 ------------
868 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
869 l_include BOOLEAN;
870 --
871 -------------------------------------------------------------------------------
872 BEGIN -- MAIN --
873 -------------------------------------------------------------------------------
874 hr_utility.set_location('py_za_tax_reg.include_assignment',1);
875 --
876 p_asg_end_date := assignment_end_date (p_assignment_id => p_asg_id);
877 -- Include ALL Assignments
878 --
879 IF g_include_asg = 'A' THEN
880 hr_utility.set_location('py_za_tax_reg.include_assignment',2);
881 --
882 l_include := TRUE;
883 -- Include Terminated Assignments ONLY
884 --
885 ELSIF g_include_asg = 'T' THEN
886 hr_utility.set_location('py_za_tax_reg.include_assignment',3);
887 --
888 IF p_asg_end_date < g_period_end_date THEN
889 hr_utility.set_location('py_za_tax_reg.include_assignment',4);
890 l_include := TRUE;
891 ELSE
892 hr_utility.set_location('py_za_tax_reg.include_assignment',5);
893 l_include := FALSE;
894 END IF;
895 -- Include Current Assignments ONLY
896 --
897 ELSIF g_include_asg = 'C' THEN
898 hr_utility.set_location('py_za_tax_reg.include_assignment',6);
899 --
900 IF p_asg_end_date >= g_period_end_date THEN
901 hr_utility.set_location('py_za_tax_reg.include_assignment',7);
902 l_include := TRUE;
903 ELSE
904 hr_utility.set_location('py_za_tax_reg.include_assignment',8);
905 l_include := FALSE;
906 END IF;
907 END IF;
908
909
910 -- Set the end date of the assignment to null if
911 -- it's on or after the period end date
912 -- this will indicate a non terminated assignment
913 --
914 IF p_asg_end_date >= g_period_end_date THEN
915 hr_utility.set_location('py_za_tax_reg.include_assignment',9);
916 p_asg_end_date := NULL;
917 END IF;
918
919 IF l_include THEN
920 hr_utility.set_location('py_za_tax_reg.include_assignment',10);
921 p_asg_start_date := assignment_start_date (p_assignment_id => p_asg_id);
922 END IF;
923
924 hr_utility.set_location('py_za_tax_reg.include_assignment',11);
925 RETURN l_include;
926
927 EXCEPTION
928 WHEN OTHERS THEN
929 hr_utility.set_location('py_za_tax_reg.include_assignment',12);
930 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
931 hr_utility.raise_error;
932 -------------------------------------------------------------------------------
933 END include_assignment;
934
935 -------------------------------------------------------------------------------
936 -- include_assignment
937 -- This function is the overloaded version of include_assignment
938 -- It is called from the value set PY_SRS_ZA_TX_RGSTR_ASG
939 -------------------------------------------------------------------------------
940 FUNCTION include_assignment (
941 p_asg_id IN per_all_assignments_f.assignment_id%TYPE
942 , p_period_end_date IN per_time_periods.end_date%TYPE
943 , p_include_flag IN VARCHAR2
944 )
945 RETURN VARCHAR2 AS
946 ------------
947 -- Variables
948 ------------
949 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
950 l_include VARCHAR2(1);
951 --
952 -------------------------------------------------------------------------------
953 BEGIN -- MAIN --
954 -------------------------------------------------------------------------------
955 l_asg_end_date := assignment_end_date (p_assignment_id => p_asg_id);
956 -- Include ALL Assignments
957 --
958 IF p_include_flag = 'A' THEN
959 l_include := 'Y';
960 -- Include Terminated Assignments ONLY
961 --
962 ELSIF p_include_flag = 'T' THEN
963 IF l_asg_end_date < p_period_end_date THEN
964 l_include := 'Y';
965 ELSE
966 l_include := 'N';
967 END IF;
968 -- Include Current Assignments ONLY
969 --
970 ELSIF p_include_flag = 'C' THEN
971 IF l_asg_end_date >= p_period_end_date THEN
972 l_include := 'Y';
973 ELSE
974 l_include := 'N';
975 END IF;
976 END IF;
977
978 hr_utility.set_location('py_za_tax_reg.include_assignment',1);
979 RETURN l_include;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 hr_utility.set_location('py_za_tax_reg.include_assignment',2);
984 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
985 hr_utility.raise_error;
986 -------------------------------------------------------------------------------
987 END include_assignment;
988
989 -------------------------------------------------------------------------------
990 -- total_employees
991 -------------------------------------------------------------------------------
992 FUNCTION total_employees RETURN NUMBER AS
993 ---------
994 -- Cursor
995 ---------
996 CURSOR csr_total_employees
997 IS
998 SELECT
999 count(max(tr.person_id))
1000 FROM
1001 pay_za_tax_registers tr
1002 GROUP BY
1003 tr.person_id;
1004
1005 ------------
1006 -- Variables
1007 ------------
1008 l_tot_employees NUMBER;
1009
1010 -------------------------------------------------------------------------------
1011 BEGIN -- MAIN --
1012 -------------------------------------------------------------------------------
1013 hr_utility.set_location('py_za_tax_reg.total_employees',1);
1014
1015 OPEN csr_total_employees;
1016 FETCH csr_total_employees INTO l_tot_employees;
1017 CLOSE csr_total_employees;
1018
1019 hr_utility.set_location('py_za_tax_reg.total_employees',2);
1020 RETURN l_tot_employees;
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 hr_utility.set_location('py_za_tax_reg.total_employees',3);
1025 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1026 hr_utility.raise_error;
1027 -------------------------------------------------------------------------------
1028 END total_employees;
1029
1030 -------------------------------------------------------------------------------
1031 -- total_assignments
1032 -------------------------------------------------------------------------------
1033 FUNCTION total_assignments RETURN NUMBER AS
1034 ---------
1035 -- Cursor
1036 ---------
1037 CURSOR csr_total_assignments
1038 IS
1039 SELECT
1040 count(max(tr.assignment_id))
1041 FROM
1042 pay_za_tax_registers tr
1043 GROUP BY
1044 tr.assignment_id;
1045
1046 ------------
1047 -- Variables
1048 ------------
1049 l_tot_assignments NUMBER;
1050
1051 -------------------------------------------------------------------------------
1052 BEGIN -- MAIN --
1053 -------------------------------------------------------------------------------
1054 hr_utility.set_location('py_za_tax_reg.total_assignments',1);
1055
1056 OPEN csr_total_assignments;
1057 FETCH csr_total_assignments INTO l_tot_assignments;
1058 CLOSE csr_total_assignments;
1059
1060 hr_utility.set_location('py_za_tax_reg.total_assignments',2);
1061 RETURN l_tot_assignments;
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 hr_utility.set_location('py_za_tax_reg.total_assignments',3);
1066 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1067 hr_utility.raise_error;
1068 -------------------------------------------------------------------------------
1069 END total_assignments;
1070
1071
1072 -------------------------------------------------------------------------------
1073 -- set_period_details
1074 -------------------------------------------------------------------------------
1075 PROCEDURE set_period_details AS
1076 ---------
1077 -- Cursor
1078 ---------
1079 CURSOR csr_min_time_period
1080 IS
1081 SELECT
1082 MIN(ptp.time_period_id) min_time_period
1083 FROM
1084 per_time_periods ptp
1085 WHERE
1086 ptp.payroll_id = g_payroll_id
1087 AND ptp.prd_information1 =
1088 (
1089 SELECT ptp2.prd_information1
1090 FROM per_time_periods ptp2
1091 WHERE ptp2.payroll_id = g_payroll_id
1092 AND ptp2.time_period_id = g_end_period_id
1093 );
1094 ---------
1095 -- Cursor
1096 ---------
1097 CURSOR csr_period_details
1098 IS
1099 SELECT ptp.period_num
1100 , ptp.start_date
1101 , ptp.end_date
1102 FROM per_time_periods ptp
1103 WHERE ptp.time_period_id = g_end_period_id;
1104 ------------
1105 -- Variables
1106 ------------
1107 l_min_period_id per_time_periods.time_period_id%TYPE;
1108 l_period_info csr_period_details%ROWTYPE;
1109 --
1110 -------------------------------------------------------------------------------
1111 BEGIN -- MAIN --
1112 -------------------------------------------------------------------------------
1113 hr_utility.set_location('py_za_tax_reg.set_period_details',1);
1114 IF g_start_period_id IS NULL THEN
1115 hr_utility.set_location('py_za_tax_reg.set_period_details',2);
1116
1117 OPEN csr_min_time_period;
1118 FETCH csr_min_time_period INTO l_min_period_id;
1119 CLOSE csr_min_time_period;
1120
1121 g_start_period_id := l_min_period_id;
1122 END IF;
1123 --
1124 hr_utility.set_location('py_za_tax_reg.set_period_details',3);
1125 --
1126 OPEN csr_period_details;
1127 FETCH csr_period_details INTO l_period_info;
1128 CLOSE csr_period_details;
1129 --
1130 hr_utility.set_location('py_za_tax_reg.set_period_details',4);
1131 --
1132 g_period_num := l_period_info.period_num;
1133 g_period_start_date := l_period_info.start_date;
1134 g_period_end_date := l_period_info.end_date;
1135 --
1136 hr_utility.set_location('py_za_tax_reg.set_period_details',5);
1137
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 hr_utility.set_location('py_za_tax_reg.set_period_details',6);
1141 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1142 hr_utility.raise_error;
1143 -------------------------------------------------------------------------------
1144 END set_period_details;
1145
1146
1147 -------------------------------------------------------------------------------
1148 -- set_payroll_details
1149 -------------------------------------------------------------------------------
1150 PROCEDURE set_payroll_details AS
1151 ---------
1152 -- Cursor
1153 ---------
1154 -- 3221746 removed fnd_sessions table
1155 CURSOR csr_payroll_name
1156 IS
1157 SELECT
1158 pap.payroll_name
1159 FROM
1160 pay_all_payrolls_f pap
1161 WHERE
1162 pap.payroll_id = g_payroll_id
1163 AND g_period_end_date BETWEEN pap.effective_start_date
1164 AND pap.effective_end_date;
1165
1166 ------------
1167 -- Variables
1168 ------------
1169 --
1170 -------------------------------------------------------------------------------
1171 BEGIN -- MAIN --
1172 -------------------------------------------------------------------------------
1173 hr_utility.set_location('py_za_tax_reg.set_payroll_details',1);
1174
1175 OPEN csr_payroll_name;
1176 FETCH csr_payroll_name INTO g_payroll_name;
1177 CLOSE csr_payroll_name;
1178
1179 hr_utility.set_location('py_za_tax_reg.set_payroll_details',2);
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 hr_utility.set_location('py_za_tax_reg.set_payroll_details',3);
1184 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1185 hr_utility.raise_error;
1186 -------------------------------------------------------------------------------
1187 END set_payroll_details;
1188
1189 -------------------------------------------------------------------------------
1190 -- set_globals
1191 -------------------------------------------------------------------------------
1192 PROCEDURE set_globals (
1193 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
1194 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1195 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1196 , p_include IN VARCHAR2
1197 , p_retrieve_ptd IN VARCHAR2
1198 , p_retrieve_mtd IN VARCHAR2
1199 , p_retrieve_ytd IN VARCHAR2
1200 )
1201 AS
1202 ------------
1203 -- Variables
1204 ------------
1205 --
1206 -------------------------------------------------------------------------------
1207 BEGIN -- MAIN --
1208 -------------------------------------------------------------------------------
1209 hr_utility.set_location('py_za_tax_reg.set_globals',1);
1210 --
1211 SELECT
1212 pay_za_tax_registers_s.nextval
1213 INTO
1214 g_tax_register_id
1215 FROM
1216 dual;
1217 --
1218 hr_utility.set_location('py_za_tax_reg.set_globals',2);
1219 --
1220 g_payroll_id := p_payroll_id;
1221 g_start_period_id := p_start_period_id;
1222 g_end_period_id := p_end_period_id;
1223 g_include_asg := p_include;
1224 --
1225 hr_utility.set_location('py_za_tax_reg.set_globals',3);
1226 --
1227 IF p_retrieve_ptd = 'Y' THEN
1228 hr_utility.set_location('py_za_tax_reg.set_globals',4);
1229 g_retrieve_ptd := TRUE;
1230 END IF;
1231 IF p_retrieve_mtd = 'Y' THEN
1232 hr_utility.set_location('py_za_tax_reg.set_globals',5);
1233 g_retrieve_mtd := TRUE;
1234 END IF;
1235 IF p_retrieve_ytd = 'Y' THEN
1236 hr_utility.set_location('py_za_tax_reg.set_globals',6);
1237 g_retrieve_ytd := TRUE;
1238 END IF;
1239 --
1240 hr_utility.set_location('py_za_tax_reg.set_globals',7);
1241 --
1242 set_period_details;
1243 set_payroll_details;
1244 --set_company_details;
1245 --
1246 hr_utility.set_location('py_za_tax_reg.set_globals',8);
1247
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 hr_utility.set_location('py_za_tax_reg.set_globals',9);
1251 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1252 hr_utility.raise_error;
1253 -------------------------------------------------------------------------------
1254 END set_globals;
1255
1256 -------------------------------------------------------------------------------
1257 -- ins_register
1258 -------------------------------------------------------------------------------
1259 PROCEDURE ins_register (
1260 -- <parameter_name> <IN OUT> <datatype> <default>
1261 p_full_name IN pay_za_tax_registers.full_name%TYPE
1262 , p_employee_number IN pay_za_tax_registers.employee_number%TYPE
1263 , p_person_id IN pay_za_tax_registers.person_id%TYPE
1264 , p_date_of_birth IN pay_za_tax_registers.date_of_birth%TYPE
1265 , p_age IN pay_za_tax_registers.age%TYPE
1266 , p_tax_reference_no IN pay_za_tax_registers.tax_reference_no%TYPE
1267 , p_cmpy_tax_reference_no IN pay_za_tax_registers.cmpy_tax_reference_no%TYPE
1268 , p_tax_status IN pay_za_tax_registers.tax_status%TYPE
1269 , p_tax_directive_value IN pay_za_tax_registers.tax_directive_value%TYPE
1270 , p_days_worked IN pay_za_tax_registers.days_worked%TYPE
1271 , p_assignment_id IN pay_za_tax_registers.assignment_id%TYPE
1272 , p_assignment_action_id IN pay_za_tax_registers.assignment_action_id%TYPE
1273 , p_assignment_number IN pay_za_tax_registers.assignment_number%TYPE
1274 , p_assignment_start_date IN pay_za_tax_registers.assignment_start_date%TYPE
1275 , p_assignment_end_date IN pay_za_tax_registers.assignment_end_date%TYPE
1276 , p_bal_name IN pay_za_tax_registers.bal_name%TYPE DEFAULT NULL
1277 , p_bal_code IN pay_za_tax_registers.bal_code%TYPE DEFAULT NULL
1278 , p_tot_ptd IN pay_za_tax_registers.tot_ptd%TYPE DEFAULT NULL
1279 , p_tot_mtd IN pay_za_tax_registers.tot_mtd%TYPE DEFAULT NULL
1280 , p_tot_ytd IN pay_za_tax_registers.tot_ytd%TYPE DEFAULT NULL
1281 )
1282 AS
1283 ------------
1284 -- Variables
1285 ------------
1286 --
1287 -------------------------------------------------------------------------------
1288 BEGIN -- MAIN --
1289 -------------------------------------------------------------------------------
1290 hr_utility.set_location('py_za_tax_reg.ins_register',1);
1291 --
1292 INSERT INTO pay_za_tax_registers (
1293 tax_register_id
1294 , full_name
1295 , employee_number
1296 , person_id
1297 , date_of_birth
1298 , age
1299 , tax_reference_no
1300 , cmpy_tax_reference_no
1301 , tax_status
1302 , tax_directive_value
1303 , days_worked
1304 , assignment_id
1305 , assignment_action_id
1306 , assignment_number
1307 , assignment_start_date
1308 , assignment_end_date
1309 , bal_name
1310 , bal_code
1311 , tot_ptd
1312 , tot_mtd
1313 , tot_ytd
1314 )
1315 VALUES (
1316 g_tax_register_id
1317 , p_full_name
1318 , p_employee_number
1319 , p_person_id
1320 , p_date_of_birth
1321 , p_age
1322 , p_tax_reference_no
1323 , p_cmpy_tax_reference_no
1324 , p_tax_status
1325 , p_tax_directive_value
1326 , p_days_worked
1327 , p_assignment_id
1328 , p_assignment_action_id
1329 , p_assignment_number
1330 , p_assignment_start_date
1331 , p_assignment_end_date
1332 , p_bal_name
1333 , p_bal_code
1334 , p_tot_ptd
1335 , p_tot_mtd
1336 , p_tot_ytd
1337 );
1338 --
1339 hr_utility.set_location('py_za_tax_reg.ins_register',2);
1340 --
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 hr_utility.set_location('py_za_tax_reg.ins_register',3);
1344 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1345 hr_utility.raise_error;
1346 -------------------------------------------------------------------------------
1347 END ins_register;
1348
1349
1350 -------------------------------------------------------------------------------
1351 -- clear_register
1352 -------------------------------------------------------------------------------
1353 PROCEDURE clear_register (
1354 p_id IN pay_za_tax_registers.tax_register_id%TYPE
1355 )
1356 AS
1357 -------------------------------------------------------------------------------
1358 BEGIN -- MAIN --
1359 -------------------------------------------------------------------------------
1360 hr_utility.set_location('py_za_tax_reg.clear_register',1);
1361 --
1362 DELETE
1363 FROM
1364 pay_za_tax_registers ztr
1365 WHERE
1366 ztr.tax_register_id = p_id;
1367
1368 hr_utility.set_location('py_za_tax_reg.clear_register',2);
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 hr_utility.set_location('py_za_tax_reg.clear_register',3);
1373 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1374 hr_utility.raise_error;
1375 -------------------------------------------------------------------------------
1376 END clear_register;
1377
1378 -------------------------------------------------------------------------------
1379 -- Procedure pre_process
1380 --
1381 -- The Pre Process procedure called by the ZA Tax Register Report
1382 -- It populates the pay_za_tax_registers table with
1383 -- processed assignment balance value information
1384 -------------------------------------------------------------------------------
1385 PROCEDURE pre_process (
1386 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
1387 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1388 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1389 , p_include IN VARCHAR2
1390 , p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1391 , p_retrieve_ptd IN VARCHAR2
1392 , p_retrieve_mtd IN VARCHAR2
1393 , p_retrieve_ytd IN VARCHAR2
1394 , p_tax_register_id OUT NOCOPY pay_za_tax_registers.tax_register_id%TYPE
1395 , p_payroll_name OUT NOCOPY pay_all_payrolls_f.payroll_name%TYPE
1396 , p_period_num OUT NOCOPY per_time_periods.period_num%TYPE
1397 , p_period_start_date OUT NOCOPY per_time_periods.start_date%TYPE
1398 , p_period_end_date OUT NOCOPY per_time_periods.end_date%TYPE
1399 , p_tot_employees OUT NOCOPY NUMBER
1400 , p_tot_assignments OUT NOCOPY NUMBER
1401 )
1402 AS
1403 -----------------------------------------------------------------
1404 -- Cursor csr_processed_assignments
1405 --
1406 -- Selects processed assignments and corresponding person details
1407 -- for a specific payroll within two time periods
1408 -- returning the maximum assignment action
1409 -----------------------------------------------------------------
1410 -- Bug 5330452
1411 CURSOR csr_processed_assignments (
1412 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE
1413 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1414 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1415 , p_asg_id IN per_all_assignments_f.assignment_id%TYPE DEFAULT NULL
1416 )
1417 IS
1418 SELECT
1419 paa.assignment_action_id
1420 , paa.assignment_id
1421 , ppa.time_period_id
1422 , ppa.effective_date
1423 , asg.assignment_number
1424 , pap.person_id
1425 , pap.full_name
1426 , pap.date_of_birth
1427 , pap.employee_number
1428 , pap.per_information1 tax_reference_number
1429 , trunc(months_between(g_period_end_date, pap.date_of_birth)/12) age
1430 , oit.org_information3 cmpy_tax_reference_number
1431 FROM
1432 pay_assignment_actions paa
1433 , pay_payroll_actions ppa
1434 , hr_organization_information oit
1435 , per_assignment_extra_info aei
1436 , per_assignments_f asg
1437 , per_people_f pap
1438 , (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id) ptp
1439 WHERE
1440 ppa.payroll_id = p_payroll_id
1441 AND ppa.time_period_id >= p_start_period_id
1442 AND ppa.time_period_id <= p_end_period_id
1443 AND ppa.payroll_action_id = paa.payroll_action_id
1444 AND paa.assignment_id = nvl(p_asg_id, paa.assignment_id)
1445 AND paa.rowid =
1446 (select rowid from pay_assignment_actions paa2 where
1447 paa2.assignment_id=paa.assignment_id
1448 and paa2.action_sequence=
1449 (select MAX(paa3.action_sequence) from pay_assignment_actions paa3,
1450 pay_payroll_actions ppa2
1451 where paa3.assignment_id = paa.assignment_id
1452 and paa3.payroll_action_id = ppa2.payroll_action_id
1453 and ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
1454 and ppa2.time_period_id <= p_end_period_id
1455 and ppa2.payroll_id = p_payroll_id
1456 )
1457 )
1458 AND paa.assignment_id = asg.assignment_id
1459 AND (
1460 (
1461 asg.effective_start_date <= ptp.end_date
1462 AND asg.effective_end_date >= ptp.end_date
1463 )
1464 OR
1465 (
1466 asg.effective_end_date <= ptp.end_date
1467 AND asg.effective_end_date = (select max(asg2.effective_end_date)
1468 from per_assignments_f asg2
1469 where asg2.assignment_id = asg.assignment_id)
1470 )
1471 )
1472 AND asg.payroll_id = p_payroll_id
1473 AND asg.assignment_id = aei.assignment_id(+)
1474 AND aei.aei_information_category(+) = 'ZA_SPECIFIC_INFO'
1475 AND aei.aei_information7 = oit.organization_id(+)
1476 AND oit.org_information_context(+) = 'ZA_LEGAL_ENTITY'
1477 AND asg.person_id = pap.person_id
1478 -- important, must be app eff date to get correct data
1479 AND asg.payroll_id = ppa.payroll_id
1480 AND g_period_end_date BETWEEN pap.effective_start_date
1481 AND pap.effective_end_date;
1482 -----------------------------------------------------------
1483 -- Cursor csr_irp5_balances
1484 --
1485 -- select those balances that have been fed by any
1486 -- assignment action of the assignment within the specified
1487 -- time periods, the tax year
1488 -----------------------------------------------------------
1489 CURSOR csr_irp5_balances (
1490 p_asg_action_id IN pay_assignment_actions.assignment_action_id%TYPE
1491 , p_asg_id IN pay_assignment_actions.assignment_id%TYPE
1492 , p_start_period_id IN per_time_periods.time_period_id%TYPE
1493 , p_end_period_id IN per_time_periods.time_period_id%TYPE
1494 )
1495 IS
1496 SELECT DISTINCT
1497 pbc.full_balance_name bal_name
1498 , pbc.code bal_code
1499 , pbc.balance_type_id bal_id
1500 FROM pay_za_irp5_bal_codes pbc
1501 , pay_run_result_values prrv
1502 , pay_run_results prr
1503 , pay_balance_feeds_f feed
1504 , pay_payroll_actions ppa
1505 , pay_assignment_actions paa
1506 WHERE prrv.input_value_id = feed.input_value_id
1507 AND prr.run_result_id = prrv.run_result_id
1508 AND paa.assignment_action_id <= p_asg_action_id
1509 AND prr.assignment_action_id = paa.assignment_action_id
1510 AND paa.assignment_id = p_asg_id
1511 AND ppa.payroll_action_id = paa.payroll_action_id
1512 AND ppa.action_type IN ('R', 'I', 'B', 'Q', 'V')
1513 AND ppa.effective_date >= (select start_date from per_time_periods ptp where ptp.time_period_id = p_start_period_id)
1514 AND ppa.effective_date <= (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id)
1515 AND pbc.balance_type_id = feed.balance_type_id
1516 AND pbc.balance_sequence = 1;
1517 ------------
1518 -- Variables
1519 ------------
1520 l_asg_start_date per_all_assignments_f.effective_start_date%TYPE;
1521 l_asg_end_date per_all_assignments_f.effective_end_date%TYPE;
1522 l_asg_tax_status pay_run_result_values.result_value%TYPE;
1523 l_asg_dir_value pay_run_result_values.result_value%TYPE;
1524 l_asg_dys_worked NUMBER;
1525 l_ptd_bal NUMBER;
1526 l_mtd_bal NUMBER;
1527 l_ytd_bal NUMBER;
1528 l_asg_tax_status_code hr_lookups.lookup_code%TYPE;
1529 l_nature hr_lookups.meaning%TYPE;
1530 l_bal_code pay_za_irp5_bal_codes.code%TYPE;
1531
1532 -------------------------------------------------------------------------------
1533 BEGIN -- Pre Process - MAIN --
1534 -------------------------------------------------------------------------------
1535 hr_utility.set_location('py_za_tax_reg.pre_process',1);
1536 --
1537 set_globals (
1538 p_payroll_id => p_payroll_id
1539 , p_start_period_id => p_start_period_id
1540 , p_end_period_id => p_end_period_id
1541 , p_include => p_include
1542 , p_retrieve_ptd => p_retrieve_ptd
1543 , p_retrieve_mtd => p_retrieve_mtd
1544 , p_retrieve_ytd => p_retrieve_ytd
1545 );
1546 --
1547 hr_utility.set_location('py_za_tax_reg.pre_process',2);
1548 ------------------------
1549 <<Processed_Assignments>>
1550 ------------------------
1551 FOR v_assignments IN csr_processed_assignments
1552 ( p_payroll_id => g_payroll_id
1553 , p_start_period_id => g_start_period_id
1554 , p_end_period_id => g_end_period_id
1555 , p_asg_id => p_assignment_id
1556 )
1557 LOOP
1558 hr_utility.set_location('py_za_tax_reg.pre_process',3);
1559 --
1560 IF include_assignment (
1561 p_asg_id => v_assignments.assignment_id
1562 , p_asg_start_date => l_asg_start_date
1563 , p_asg_end_date => l_asg_end_date
1564 )
1565 THEN
1566 hr_utility.set_location('py_za_tax_reg.pre_process',4);
1567 -- get assignment's tax status and directive value
1568 assignment_tax_sta_dir (
1569 p_assignment_id => v_assignments.assignment_id
1570 , p_asg_tax_status => l_asg_tax_status
1571 , p_asg_dir_value => l_asg_dir_value
1572 , p_asg_tax_status_code => l_asg_tax_status_code
1573 );
1574 --
1575 -- get assignment's nature of person
1576 assignment_nature (
1577 p_assignment_id => v_assignments.assignment_id
1578 , p_effective_date => v_assignments.effective_date
1579 , p_asg_nature => l_nature
1580 );
1581 --
1582 hr_utility.set_location('py_za_tax_reg.pre_process',6);
1583 -- get assignment's seasonal days worked
1584 l_asg_dys_worked :=
1585 assignment_dys_worked (
1586 p_asg_tax_status => l_asg_tax_status
1587 , p_asg_action_id => v_assignments.assignment_action_id
1588 , p_effective_date => v_assignments.effective_date
1589 );
1590 --
1591 hr_utility.set_location('py_za_tax_reg.pre_process',7);
1592 -----------------
1593 <<Balance_Values>>
1594 -----------------
1595 FOR v_bal IN csr_irp5_balances (
1596 p_asg_action_id => v_assignments.assignment_action_id
1597 , p_asg_id => v_assignments.assignment_id
1598 , p_start_period_id => g_start_period_id
1599 , p_end_period_id => g_end_period_id
1600 )
1601 LOOP
1602 hr_utility.set_location('py_za_tax_reg.pre_process',8);
1603 --
1604 --get the correct SARS Code for directors and foreign income
1605 l_bal_code := py_za_tax_certificates.get_sars_code(
1606 p_sars_code => v_bal.bal_code
1607 , p_tax_status => l_asg_tax_status_code
1608 , p_nature => l_nature
1609 );
1610 --
1611 l_ptd_bal :=
1612 ptd_value (
1613 p_asg_action_id => v_assignments.assignment_action_id
1614 , p_action_period_id => v_assignments.time_period_id
1615 , p_balance_type_id => v_bal.bal_id
1616 , p_balance_name => v_bal.bal_name
1617 , p_effective_date => v_assignments.effective_date
1618 );
1619 --
1620 hr_utility.set_location('py_za_tax_reg.pre_process',9);
1621 --
1622 l_mtd_bal :=
1623 mtd_value (
1624 p_asg_action_id => v_assignments.assignment_action_id
1625 , p_balance_type_id => v_bal.bal_id
1626 , p_balance_name => v_bal.bal_name
1627 , p_effective_date => v_assignments.effective_date
1628 );
1629 --
1630 hr_utility.set_location('py_za_tax_reg.pre_process',10);
1631 --
1632 l_ytd_bal :=
1633 ytd_value (
1634 p_asg_action_id => v_assignments.assignment_action_id
1635 , p_balance_type_id => v_bal.bal_id
1636 , p_effective_date => v_assignments.effective_date
1637 );
1638 --
1639 hr_utility.set_location('py_za_tax_reg.pre_process',11);
1640 --
1641 IF valid_record (
1642 p_ptd_bal => l_ptd_bal
1643 , p_mtd_bal => l_mtd_bal
1644 , p_ytd_bal => l_ytd_bal
1645 )
1646 THEN
1647 hr_utility.set_location('py_za_tax_reg.pre_process',12);
1648 -- Create the register record
1649 --
1650 ins_register (
1651 p_full_name => v_assignments.full_name
1652 , p_employee_number => v_assignments.employee_number
1653 , p_person_id => v_assignments.person_id
1654 , p_date_of_birth => v_assignments.date_of_birth
1655 , p_age => v_assignments.age
1656 , p_tax_reference_no => v_assignments.tax_reference_number
1657 , p_cmpy_tax_reference_no => v_assignments.cmpy_tax_reference_number
1658 , p_tax_status => l_asg_tax_status
1659 , p_tax_directive_value => l_asg_dir_value
1660 , p_days_worked => l_asg_dys_worked
1661 , p_assignment_id => v_assignments.assignment_id
1662 , p_assignment_action_id => v_assignments.assignment_action_id
1663 , p_assignment_number => v_assignments.assignment_number
1664 , p_assignment_start_date => l_asg_start_date
1665 , p_assignment_end_date => l_asg_end_date
1666 , p_bal_name => v_bal.bal_name
1667 , p_bal_code => l_bal_code
1668 , p_tot_ptd => l_ptd_bal
1669 , p_tot_mtd => l_mtd_bal
1670 , p_tot_ytd => l_ytd_bal
1671 );
1672 END IF; -- Valid Record
1673 END LOOP Balance_Values;
1674 END IF; -- Include Assignment
1675 END LOOP Processed_Assignments;
1676 --
1677 hr_utility.set_location('py_za_tax_reg.pre_process',13);
1678 ---------------------
1679 -- Set out Parameters
1680 ---------------------
1681 p_tax_register_id := g_tax_register_id;
1682 p_payroll_name := g_payroll_name;
1683 p_period_num := g_period_num;
1684 p_period_start_date := g_period_start_date;
1685 p_period_end_date := g_period_end_date;
1686 p_tot_employees := total_employees;
1687 p_tot_assignments := total_assignments;
1688 EXCEPTION
1689 WHEN OTHERS THEN
1690 hr_utility.set_location('py_za_tax_reg.pre_process',14);
1691 hr_utility.set_message(801,'Sql Err Code: '||TO_CHAR(SQLCODE));
1692 hr_utility.raise_error;
1693 -------------------------------------------------------------------------------
1694 END pre_process;-- END --
1695 -------------------------------------------------------------------------------
1696 -------------------------------------------------------------------------------
1697 END py_za_tax_reg;-- END OF PACKAGE --