1 PACKAGE BODY Pay_Za_Update_Pkg AS
2 /* $Header: pyzaupdt.pkb 120.2 2008/03/18 10:16:50 parusi noship $ */
3
4 /* This function checks whether a payroll is updatuble for TYSP. */
5
6 FUNCTION payroll_updateble
7 (
8 p_payroll NUMBER,
9 p_tax_year NUMBER
10 )
11 RETURN BOOLEAN
12 AS
13
14 l_result NUMBER;
15
16 BEGIN
17
18 SELECT
19 COUNT(tysp_id) INTO l_result
20 FROM
21 pay_za_tys_processes
22 WHERE
23 payroll_id = p_payroll
24 AND tax_year = p_tax_year;
25
26 IF l_result > 0 THEN
27 RETURN (FALSE);
28 ELSE
29 RETURN (TRUE);
30 END IF;
31
32 END payroll_updateble;
33
34 /* This function returns the current tax year for a specific payroll. */
35
36 /*
37 Function get_tax_year
38 (
39 p_payroll number
40 )
41 Return varchar2
42 As
43
44 l_tax_year varchar2(4);
45
46 Begin
47
48 Select
49 ptp.prd_information1 into l_tax_year
50 From
51 per_time_periods ptp
52 Where
53 ptp.payroll_id = p_payroll
54 And (sysdate - 365) between ptp.start_date and ptp.end_date;
55
56 Return l_tax_year;
57
58 End get_tax_year;
59 */
60
61 /* This function returns the tax year end date */
62
63 FUNCTION get_tax_year_end
64 (
65 p_payroll NUMBER,
66 p_tax_year VARCHAR2
67 )
68 RETURN DATE
69 AS
70
71 l_tax_year_end DATE;
72 l_year VARCHAR2(4);
73
74 BEGIN
75
76 l_year := TO_CHAR(TO_NUMBER( SUBSTR(p_tax_year,-4) ) - 1);
77
78 SELECT MAX(ptp.end_date) + 1 INTO l_tax_year_end
79 FROM
80 per_time_periods ptp
81 WHERE
82 ptp.payroll_id = p_payroll
83 AND ptp.prd_information1 = SUBSTR(p_tax_year,-4);
84
85 RETURN l_tax_year_end;
86
87 END get_tax_year_end;
88
89 /* This function does validation on one entry
90 If the validation_mode is 'NEXT_DAY_CHANGE', the function will check
91 that their is not a date effective entry on the following day of the
92 effective_date */
93
94 FUNCTION entry_valid
95 (
96 p_record Pay_Za_Tax_Year_Start_Pkg.c_entry_details%ROWTYPE,
97 p_validation_mode VARCHAR2
98 )
99 RETURN BOOLEAN
100 AS
101
102 l_result VARCHAR2(60);
103 l_count NUMBER;
104 l_current_eff_start_date DATE ;
105 l_current_eff_end_date DATE;
106
107 BEGIN
108 IF p_validation_mode = 'NEXT_DAY_CHANGE' THEN
109 BEGIN
110 SELECT
111 peev.screen_entry_value INTO l_result
112 FROM
113 pay_element_entries_f pee,
114 pay_element_entry_values_f peev,
115 pay_input_values_f piv,
116 pay_element_types_f pet,
117 pay_element_links_f pel,
118 per_assignments_f pa
119 WHERE
120 pee.element_entry_id = peev.element_entry_id
121 AND piv.input_value_id = peev.input_value_id
122 AND piv.name = p_record.name
123 AND pee.element_entry_id = p_record.element_entry_id
124 AND piv.input_value_id = p_record.input_value_id
125 AND piv.input_value_id = peev.input_value_id
126 AND pet.element_name = 'ZA_Tax'
127 AND pel.element_type_id = pet.element_type_id
128 AND pee.element_link_id = pel.element_link_id
129 AND pa.assignment_id = pee.assignment_id
130 AND (p_record.p_effective_date + 1) BETWEEN peev.effective_start_date AND peev.effective_end_date
131 AND (p_record.p_effective_date + 1)BETWEEN pee.effective_start_date AND pee.effective_end_date
132 AND (p_record.p_effective_date + 1)BETWEEN piv.effective_start_date AND piv.effective_end_date
133 AND (p_record.p_effective_date + 1)BETWEEN pet.effective_start_date AND pet.effective_end_date
134 AND (p_record.p_effective_date + 1)BETWEEN pel.effective_start_date AND pel.effective_end_date
135 AND (p_record.p_effective_date + 1)BETWEEN pa.effective_start_date AND pa.effective_end_date;
136
137 IF NVL(l_result,'x') = NVL(p_record.screen_entry_value,'x') THEN
138 RETURN (FALSE);
139 ELSE
140 RETURN (TRUE);
141 END IF;
142
143 EXCEPTION when no_data_found then
144 RETURN (TRUE);
145 END;
146
147 ELSIF p_validation_mode = 'FUTURE_CHANGE' THEN
148 SELECT
149 COUNT(piv.input_value_id) INTO l_count
150 FROM
151 pay_input_values_f piv,
152 pay_element_types_f pet,
153 pay_element_links_f pel,
154 pay_element_entries_f pee,
155 pay_element_entry_values_f peev
156 WHERE
157 pee.element_entry_id = peev.element_entry_id
158 AND pee.element_entry_id = p_record.element_entry_id
159 AND piv.input_value_id = peev.input_value_id
160 AND pet.element_type_id = piv.element_type_id
161 AND pet.element_name = 'ZA_Tax'
162 AND pel.element_type_id = pet.element_type_id
163 AND pee.element_link_id = pel.element_link_id
164 AND (p_record.p_effective_date + 2) < peev.effective_start_date
165 AND (p_record.p_effective_date + 2) < pee.effective_start_date
166 AND (p_record.p_effective_date + 1) BETWEEN piv.effective_start_date AND piv.effective_end_date
167 AND (p_record.p_effective_date + 1) BETWEEN pet.effective_start_date AND pet.effective_end_date
168 AND (p_record.p_effective_date + 1) BETWEEN pel.effective_start_date AND pel.effective_end_date;
169
170 IF l_count > 0 THEN
171 RETURN (TRUE);
172 ELSE
173 RETURN (FALSE);
174 END IF;
175
176 ELSIF p_validation_mode = 'ALREADY_NEW' THEN
177 SELECT
178 peev.screen_entry_value INTO l_result
179 FROM
180 pay_element_entries_f pee,
181 pay_element_entry_values_f peev,
182 pay_input_values_f piv,
183 pay_element_types_f pet,
184 pay_element_links_f pel,
185 per_assignments_f pa
186 WHERE
187 pee.element_entry_id = peev.element_entry_id
188 AND piv.input_value_id = peev.input_value_id
189 AND piv.name = p_record.name
190 AND pee.element_entry_id = p_record.element_entry_id
191 AND piv.input_value_id = p_record.input_value_id
192 AND piv.input_value_id = peev.input_value_id
193 AND pet.element_name = 'ZA_Tax'
194 AND pel.element_type_id = pet.element_type_id
195 AND pee.element_link_id = pel.element_link_id
196 AND pa.assignment_id = pee.assignment_id
197 AND (p_record.p_effective_date - 1) BETWEEN peev.effective_start_date AND peev.effective_end_date
198 AND (p_record.p_effective_date - 1) BETWEEN pee.effective_start_date AND pee.effective_end_date
199 AND (p_record.p_effective_date - 1) BETWEEN piv.effective_start_date AND piv.effective_end_date
200 AND (p_record.p_effective_date - 1) BETWEEN pet.effective_start_date AND pet.effective_end_date
201 AND (p_record.p_effective_date - 1) BETWEEN pel.effective_start_date AND pel.effective_end_date
202 AND (p_record.p_effective_date - 1) BETWEEN pa.effective_start_date AND pa.effective_end_date;
203
204 IF p_record.screen_entry_value IS NULL OR
205 NVL(l_result,'x') = NVL(p_record.screen_entry_value,'x') THEN
206 RETURN (FALSE);
207 ELSE
208 RETURN (TRUE);
209 END IF;
210 ELSIF p_validation_mode = 'DELETE_NEXT_CHANGE' THEN
211 /* Bug 5956650
212 to check if there exists an element_entry row (which we intend to rollback)
213 after the current row*/
214 select ee.effective_start_date,
215 ee.effective_end_date
216 into l_current_eff_start_date,
217 l_current_eff_end_date
218 from pay_element_entries_f ee,
219 pay_element_links_f el,
220 pay_element_types_f et,
221 pay_element_classifications ec
222 where ee.element_entry_id = p_record.element_entry_id
223 and el.element_link_id = ee.element_link_id
224 and et.element_type_id = el.element_type_id
225 and ec.classification_id = et.classification_id
226 and p_record.p_effective_date between ee.effective_start_date
227 and ee.effective_end_date
228 and p_record.p_effective_date between el.effective_start_date
229 and el.effective_end_date
230 and p_record.p_effective_date between et.effective_start_date
231 and et.effective_end_date;
232
233 select count(ee.effective_end_date)
234 into l_count
235 from pay_element_entries_f ee
236 where ee.element_entry_id = p_record.element_entry_id
237 and ee.effective_start_date > l_current_eff_end_date;
238
239 IF l_count > 0 THEN
240 RETURN TRUE;
241 ELSE
242 RETURN FALSE;
243 END IF;
244 END IF;
245
246 END entry_valid;
247
248 /* This procedure updates the record that is passed in as a parameer. */
249
250 PROCEDURE update_this_record
251 (
252 p_one_record Pay_Za_Tax_Year_Start_Pkg.c_entry_details%ROWTYPE,
253 p_new_value VARCHAR2,
254 p_update_mode VARCHAR2
255 )
256 AS
257 BEGIN
258 hr_utility.set_location('Update_this_record ',10);
259 hr_entry_api.update_element_entry
260 (
261 p_dt_update_mode => p_update_mode,
262 p_session_date => p_one_record.p_effective_date + 1,
263 p_element_entry_id => p_one_record.element_entry_id,
264 p_input_value_id1 => p_one_record.input_value_id,
265 p_entry_value1 => p_new_value
266 );
267
268 END update_this_record;
269
270 /* This procedure updates the PAY_ZA_TYS_PROCESSES table to ensure that
271 the process will not be run for the same payroll and the same tax year.*/
272
273 PROCEDURE update_tysp_table
274 (
275 p_payroll NUMBER,
276 p_tax_year NUMBER
277 )
278 AS
279 BEGIN
280 INSERT INTO pay_za_tys_processes (
281 TYSP_ID
282 , PAYROLL_ID
283 , CONSOLIDATION_SET_ID
284 , TAX_YEAR
285 )
286 VALUES
287 (
288 pay_za_tys_processes_s.NEXTVAL,
289 p_payroll,
290 0,
291 p_tax_year
292 );
293
294 END update_tysp_table;
295
296 /* This procedure deletes the PAY_ZA_TYS_PROCESSES table to indicate that the
297 TYSP process was rolled back for this specific payroll. In other words if
298 the user wants to run the process for this payroll, he will be allowed to. */
299
300 PROCEDURE delete_tysp_table
301 (
302 p_payroll NUMBER,
303 p_tax_year NUMBER
304 )
305 AS
306 BEGIN
307
308 DELETE FROM pay_za_tys_processes
309 WHERE payroll_id = p_payroll
310 AND tax_year = p_tax_year;
311
312 END delete_tysp_table;
313
314 /* This function determines if the payroll can be rolled back */
315
316 FUNCTION payroll_rollbackable
317 (
318 p_payroll NUMBER,
319 p_tax_year NUMBER
320 )
321 RETURN BOOLEAN
322 AS
323 l_result NUMBER;
324
325 BEGIN
326 SELECT
327 COUNT(tysp_id) INTO l_result
328 FROM
329 pay_za_tys_processes
330 WHERE
331 payroll_id = p_payroll
332 AND tax_year = p_tax_year;
333
334 IF l_result > 0 THEN
335 RETURN (TRUE);
336 ELSE
337 RETURN (FALSE);
338 END IF;
339
340 END payroll_rollbackable;
341
342 /* This function returns the original value of a record */
343
344 FUNCTION get_original_value
345 (
346 p_record Pay_Za_Tax_Year_Start_Pkg.c_entry_details%ROWTYPE
347 )
348 RETURN VARCHAR2
349 AS
350
351 l_result VARCHAR2(60);
352
353 BEGIN
354 SELECT
355 peev.screen_entry_value INTO l_result
356 FROM
357 pay_element_entries_f pee,
358 pay_element_entry_values_f peev,
359 pay_input_values_f piv,
360 pay_element_types_f pet,
361 pay_element_links_f pel,
362 per_assignments_f pa
363 WHERE
364 pee.element_entry_id = peev.element_entry_id
365 AND piv.input_value_id = peev.input_value_id
366 AND piv.name = p_record.name
367 AND pee.element_entry_id = p_record.element_entry_id
368 AND piv.input_value_id = p_record.input_value_id
369 AND piv.input_value_id = peev.input_value_id
370 AND pet.element_name = 'ZA_Tax'
371 AND pel.element_type_id = pet.element_type_id
372 AND pee.element_link_id = pel.element_link_id
373 AND pa.assignment_id = pee.assignment_id
374 AND (p_record.p_effective_date - 1) BETWEEN peev.effective_start_date AND peev.effective_end_date
375 AND (p_record.p_effective_date - 1) BETWEEN pee.effective_start_date AND pee.effective_end_date
376 AND (p_record.p_effective_date - 1) BETWEEN piv.effective_start_date AND piv.effective_end_date
377 AND (p_record.p_effective_date - 1) BETWEEN pet.effective_start_date AND pet.effective_end_date
378 AND (p_record.p_effective_date - 1) BETWEEN pel.effective_start_date AND pel.effective_end_date
379 AND (p_record.p_effective_date - 1) BETWEEN pa.effective_start_date AND pa.effective_end_date;
380
381 RETURN (l_result);
382
383 END get_original_value;
384
385 /* This procedure rolls back one record, depending on its state */
386
387 PROCEDURE rollback_this_record
388 (
389 p_record Pay_Za_Tax_Year_Start_Pkg.c_entry_details%ROWTYPE
390 )
391 AS
392
393 l_original_value VARCHAR2(60);
394
395 BEGIN
396 hr_utility.set_location('Pay_Za_Tax_Year_Start_Pkg.rollback_this_record ',10);
397 IF NOT entry_valid(p_record,'ALREADY_NEW') THEN
398 IF entry_valid(p_record,'FUTURE_CHANGE') THEN
399 hr_utility.set_location('Future Change ',10);
400 l_original_value := get_original_value(p_record);
401 update_this_record
402 (
403 p_record,
404 l_original_value,
405 'CORRECTION'
406 );
407 ELSE
408 /*Bug 5956650
409 before calling hr_entry_valid.delete_element_entry check
410 if there exists another element_entry row (Next change which we intend to delete)
411 after the current row
412 */
413 IF entry_valid(p_record,'DELETE_NEXT_CHANGE') THEN
414 hr_utility.set_location('Delete Next Change ',20);
415 hr_entry_api.delete_element_entry
416 (
417 p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
418 p_session_date => p_record.p_effective_date - 1,
419 p_element_entry_id => p_record.element_entry_id
420 );
421 END IF;
422 END IF;
423 END IF;
424
425 END rollback_this_record;
426
427 END Pay_Za_Update_Pkg;
[Home] [Help]