1 PACKAGE BODY Pay_Za_Tax_Year_Start_Pkg AS
2 /* $Header: pyzatysp.pkb 115.2 2002/11/28 15:16:22 jlouw noship $ */
3 /*
4 REM +=====================================================================+
5 REM | Copyright (c) 1997 Oracle Corporation South Africa Ltd |
6 REM | Cape Town, Western Cape, South Africa |
7 REM | All rights reserved. |
8 REM +=====================================================================+
9 REM
10 REM Package File Name : pyzatysp.pkb
11 REM Description : This package declares a procedure to process the
12 REM start of the tax year. I.e. resetting the tax directive
13 REM numbers, tax directive value and tax status.
14 REM
15 REM Change List:
16 REM ------------
17 REM
18 REM Name Date Version Bug Text
19 REM -------------- ----------- ------- -------- ----------------------
20 REM K de Klerk 19-APR-1999 110.0 Initial Version
21 REM E. ShungKing 23-OCT-1999 110.1 Change to tax year start
22 REM A.STANDER 12-MAR-2000 110.2 Change not to update people that
23 REM have been updated manualy
24 REM L. Kloppers 23-SEP-2002 115.1 2224332 Modify to cater for new
25 REM Tax Statuses 'N' and 'P'
26 REM ========================================================================
27 */
28
29
30 /* This procedure sets the Tax Certificate number
31 (input value on the ZA_Tax element) to 'N', clears the
32 Tax Directive Number and Tax Directive Value (also input
33 values on the ZA_Tax element) and set the input value
34 Tax Status on the ZA_Tax element to 'A' - Normal where the Tax Status is
35 'C' or 'D', and set it to 'M' - Private Director where the Tax Status is
36 'N' or 'P'*/
37
38 PROCEDURE reset_all_ind
39 (
40 p_errmsg OUT NOCOPY VARCHAR2,
41 p_errcode OUT NOCOPY NUMBER,
42 p_payroll NUMBER,
43 p_tax_year VARCHAR2)
44 AS
45 l_tax_year NUMBER;
46 l_tax_year_end DATE;
47 l_tax_year_start DATE;
48 l_error_message VARCHAR2(100);
49 l_one_record c_entry_details%ROWTYPE;
50 l_new_value VARCHAR2(60);
51 l_element_type_id NUMBER;
52 tax_status_id NUMBER;
53 tax_directive_number_id NUMBER;
54 tax_directive_value_id NUMBER;
55
56
57
58
59 BEGIN
60
61
62 -- Get the tax year end and new start date --
63 l_tax_year_start := Pay_Za_Update_Pkg.get_tax_year_end(p_payroll, p_tax_year);
64
65 l_tax_year_end := l_tax_year_start - 1;
66
67 -- Get the tax year that the process must run for --
68 l_tax_year := TO_NUMBER(SUBSTR(p_tax_year,-4));
69
70 -- Get the element_type_id for ZA_tax --
71 SELECT element_type_id
72 INTO l_element_type_id
73 FROM pay_element_types_f
74 WHERE element_name = 'ZA_Tax'
75 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
76
77
78 -- Get the input_value_id's for Tax Status ,Tax Directive Number, Tax Directive Value
79 SELECT input_value_id
80 INTO tax_status_id
81 FROM pay_input_values_f
82 WHERE element_type_id = l_element_type_id
83 AND name = 'Tax Status'
84 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
85
86 SELECT input_value_id
87 INTO tax_directive_number_id
88 FROM pay_input_values_f
89 WHERE element_type_id = l_element_type_id
90 AND name = 'Tax Directive Number'
91 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
92
93 SELECT input_value_id
94 INTO tax_directive_value_id
95 FROM pay_input_values_f
96 WHERE element_type_id = l_element_type_id
97 AND name = 'Tax Directive Value'
98 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
99
100
101 -- Check if the payroll is updatable --
102 IF Pay_Za_Update_Pkg.payroll_updateble(p_payroll, l_tax_year) THEN
103
104 FOR v_assignments IN c_assignments(p_payroll, l_tax_year_end)
105 LOOP
106
107 OPEN c_entry_details(l_element_type_id,tax_status_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
108 FETCH c_entry_details INTO l_one_record;
109
110 IF l_one_record.screen_entry_value IN ('C','D') THEN
111
112 IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
113 l_new_value := 'Normal';
114
115 Pay_Za_Update_Pkg.update_this_record
116 (
117 l_one_record,
118 l_new_value,
119 'UPDATE_CHANGE_INSERT'
120 );
121
122 END IF;
123 END IF;
124
125 IF l_one_record.screen_entry_value IN ('N','P') THEN
126
127 IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
128 l_new_value := 'Private Director';
129
130 Pay_Za_Update_Pkg.update_this_record
131 (
132 l_one_record,
133 l_new_value,
134 'UPDATE_CHANGE_INSERT'
135 );
136
137 END IF;
138 END IF;
139
140 CLOSE c_entry_details;
141
142 OPEN c_entry_details(l_element_type_id,tax_directive_number_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
143 FETCH c_entry_details INTO l_one_record;
144
145 IF l_one_record.screen_entry_value IS NOT NULL THEN
146
147 IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
148 l_new_value := ' ';
149
150 Pay_Za_Update_Pkg.update_this_record
151 (
152 l_one_record,
153 l_new_value,
154 'UPDATE_CHANGE_INSERT'
155 );
156
157 END IF;
158 END IF;
159 CLOSE c_entry_details;
160
161 OPEN c_entry_details(l_element_type_id,tax_directive_value_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
162 FETCH c_entry_details INTO l_one_record;
163
164 IF l_one_record.screen_entry_value IS NOT NULL THEN
165
166 IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
167 -- l_new_value := ' ';
168 l_new_value := null;
169
170 Pay_Za_Update_Pkg.update_this_record
171 (
172 l_one_record,
173 l_new_value,
174 'UPDATE_CHANGE_INSERT'
175 );
176
177 END IF;
178 END IF;
179 CLOSE c_entry_details;
180 END LOOP;
181
182 -- Update the TYS table --
183 Pay_Za_Update_Pkg.update_tysp_table
184 (
185 p_payroll,
186 l_tax_year
187 );
188 END IF;
189
190 END reset_all_ind;
191
192 /* This procedure does a rollback on the tax year end process */
193
194 PROCEDURE rollback_all_ind
195 (
196 p_errmsg OUT NOCOPY VARCHAR2,
197 p_errcode OUT NOCOPY NUMBER,
198 p_payroll NUMBER,
199 p_tax_year VARCHAR2
200 )
201 AS
202
203 l_tax_year NUMBER;
204 l_tax_year_end DATE;
205 l_tax_year_start DATE;
206 l_error_message VARCHAR2(100);
207 l_one_record c_entry_details%ROWTYPE;
208 l_new_value VARCHAR2(60);
209 l_element_type_id NUMBER;
210 tax_status_id NUMBER;
211 tax_directive_number_id NUMBER;
212 tax_directive_value_id NUMBER;
213
214 BEGIN
215
216 -- Get the tax year end date --
217 l_tax_year_start := Pay_Za_Update_Pkg.get_tax_year_end(p_payroll, p_tax_year);
218 l_tax_year_end := l_tax_year_start -1;
219
220 -- Get the tax year that the process must run for --
221 l_tax_year := TO_NUMBER(SUBSTR(p_tax_year,-4));
222
223 -- Get the element_type_id for ZA_tax --
224 SELECT element_type_id
225 INTO l_element_type_id
226 FROM pay_element_types_f
227 WHERE element_name = 'ZA_Tax'
228 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
229
230 -- Get the input_value_id's for Tax Status ,Tax Directive Number, Tax Directive Value
231 SELECT input_value_id
232 INTO tax_status_id
233 FROM pay_input_values_f
234 WHERE element_type_id = l_element_type_id
235 AND name = 'Tax Status'
236 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
237
238 SELECT input_value_id
239 INTO tax_directive_number_id
240 FROM pay_input_values_f
241 WHERE element_type_id = l_element_type_id
242 AND name = 'Tax Directive Number'
243 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
244
245 SELECT input_value_id
246 INTO tax_directive_value_id
247 FROM pay_input_values_f
248 WHERE element_type_id = l_element_type_id
249 AND name = 'Tax Directive Value'
250 AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
251
252 -- Check if the payroll is updatable --
253 IF Pay_Za_Update_Pkg.payroll_rollbackable(p_payroll, l_tax_year) THEN
254
255 FOR v_assignments IN c_assignments(p_payroll, l_tax_year_end)
256 LOOP
257
258 -- Check and update tax_status if tax_status in ('C','D','N','P') --
259 OPEN c_entry_details(l_element_type_id,tax_status_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
260 FETCH c_entry_details INTO l_one_record;
261
262 Pay_Za_Update_Pkg.rollback_this_record(l_one_record);
263 CLOSE c_entry_details;
264
265 OPEN c_entry_details(l_element_type_id,tax_directive_number_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
266 FETCH c_entry_details INTO l_one_record;
267
268 Pay_Za_Update_Pkg.rollback_this_record(l_one_record);
269 CLOSE c_entry_details;
270
271 OPEN c_entry_details(l_element_type_id,tax_directive_value_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
272 FETCH c_entry_details INTO l_one_record;
273
274 Pay_Za_Update_Pkg.rollback_this_record(l_one_record);
275 CLOSE c_entry_details;
276 END LOOP;
277
278 -- Update the TYS table --
279 Pay_Za_Update_Pkg.delete_tysp_table
280 (
281 p_payroll,
282 l_tax_year
283 );
284 END IF;
285
286 END rollback_all_ind;
287 END Pay_Za_Tax_Year_Start_Pkg;
[Home] [Help]