[Home] [Help]
PACKAGE BODY: APPS.HR_AU_TAX_API
Source
1 PACKAGE BODY hr_au_tax_api AS
2 /* $Header: hrauwrtx.pkb 120.5.12010000.2 2008/08/06 08:33:40 ubhat ship $ */
3 /*
4 +===========================================================================
5 | Copyright (c) 1999 Oracle Corporation Ltd
6 | All rights reserved.
7 +===========================================================================
8 |SQL Script File Name : HR AU WR TX . PKB
9 | Name : hr_au_person_tax_api
10 | Description : Person Tax API Wrapper for AU
11 |
12 | Name Date Version Bug Text
13 | -------------- ---------- ------- ----- ----
14 | sgoggin 11-JUN-1999 110.0 Created for AU
15 | sclarke 28-APR-2000 115.1 1262179
16 | sparker 30-JUN-2000 115.2 StatUpd Changed "PAYE INFORMATION"->"TAX INFORMATION"
17 | abajpai 30-SEP-2000 115.6 Added SFSS
18 | abajpai 11-OCT-2000 115.7 SFSS Changes Rolledback
19 | rayyadev 30-OCT-2000 115.9 considered the case of SFSS before 31-JUL-2000
20 | rsinghal 27-FEB-2001 115.10 changes for Medicare levy surcharge
21 apunekar 30-APR-2001 115.11 Removed the DEFAULT null criteria for
22 p_rebate_amount,p_dependent_children,
23 p_tax_variation_amount in
24 maintain_PAYE_tax_info.
25 apunekar 24-APR-2001 115.12 Reintroduced the Default null criteria.
26 srussell 28-FEB-2002 115.17 2240759 Add new parameter
27 p_override_user_ent_chk in calls to
28 py_element_entry_api.
29 shoskatt 30-MAY-2002 115.18 Bug 2145933 - Changed the package to handle
30 Senior Australian.
31 kaverma 26-NOV-2002 115.19 Bug 2601218 - Changed maintain_paye_tax_info and
32 maintain_super_info procedures.
33 kaverma 04-DEC-2002 115.20 Added nocopy for parameters
34 vgsriniv 17-DEC-2003 115.21 Bug:3318756. Modified cursor get_passed_tax_field_values
35 to use fnd_date.chardate_to_date instead of to_date
36 srrajago 07-JUN-2004 115.22 Bug: 3648796 - Performance Fix to remove FTS.Assigned the correct element name to the variable
37 g_paye_element(as in seed).In the cursors csr_paye_element,csr_paye_tax_element(in the procedures
38 maintain_paye_tax_info and maintain_SUPER_info),removed UPPER function on element_name.Value
39 assigned to the element_name is also corrected (as in seed).Removed GSCC warnings(File.Sql.35).
40 avenkatk 16-SEP-2004 115.23 Bug 3875404 -Procedure - maintain_SUPER_info - Added a new local variable for binding IN parameter.Done to avoid errors
41 while using NOCOPY params in the Core element entry API.
42 sclarke 20-NOV-2004 115.24 4035174 Added input for SFSS in procedure get_paye_input_ids
43 JLin 12-JAN-2005 115.25 4108099 Changed the mode to 'CORRECTION' when it calls update_element_entry
44 if effective_start_date is same as the session date in maintain_PAYE_tax_info
45 abhkumar 30-MAR-2005 115.26 4244787 Changed the mode from UPDATE_CHANGE_INSERT to UPDATE if there dosen't exists any future rows
46 for Tax Information
47 abhkumar 08-SEP-2005 115.27 4598178 Changed related to tax variation on bonus enhancement.
48 sclarke 25-FEB-2006 115.28 4704141 Added new procedure to create workbench tax info.
49 sclarke 10-MAR-2006 115.30 element link for CRP tax information
50 abhargav 14-Mar-2007 115.31 Renamed parameter p_hecs_sfss_flag to p_help_sfss_flag in procedure maintain_PAYE_tax_info().
51 vaisriva 26-May-2008 115.32 7042960 2008 Statutory Updates - FTA Claim Changes
52
53
54 |NOTES
55 +==========================================================================================
56 */
57 type paye_number_table is table of number not null index by binary_integer;
58 g_package constant varchar2(33) := 'hr_au_tax_api.';
59 g_paye_element constant varchar2(60) := 'Tax Information'; -- Bug: 3648796
60 g_paye_input1 constant varchar2(60) := 'AUSTRALIAN RESIDENT';
61 g_paye_input2 constant varchar2(60) := 'TAX FREE THRESHOLD';
62 g_paye_input3 constant varchar2(60) := 'REBATE AMOUNT';
63 g_paye_input4 constant varchar2(60) := 'FTA CLAIM';
64 g_paye_input5 constant varchar2(60) := 'SAVINGS REBATE';
65 g_paye_input6 constant varchar2(60) := 'HECS';
66 g_paye_input7 constant varchar2(60) := 'DATE DECLARATION SIGNED';
67 g_paye_input8 constant varchar2(60) := 'MEDICARE LEVY VARIATION';
68 g_paye_input9 constant varchar2(60) := 'SPOUSE';
69 g_paye_input10 constant varchar2(60) := 'DEPENDENT CHILDREN';
70 g_paye_input11 constant varchar2(60) := 'TAX VARIATION TYPE';
71 g_paye_input12 constant varchar2(60) := 'TAX VARIATION AMOUNT';
72 g_paye_input13 constant varchar2(60) := 'TAX SCALE';
73 g_paye_input14 constant varchar2(60) := 'TAX FILE NUMBER';
74 g_paye_input15 constant varchar2(60) := 'SFSS';
75 g_legislation_code constant varchar2(2) := 'AU';
76
77 g_debug boolean := hr_utility.debug_enabled;
78
79
80 PROCEDURE maintain_PAYE_tax_info
81 (p_validate IN BOOLEAN DEFAULT FALSE
82 ,p_assignment_id IN NUMBER
83 ,p_effective_start_date IN OUT nocopy DATE
84 ,p_effective_end_date IN OUT nocopy DATE
85 ,p_session_date IN DATE
86 ,p_mode IN VARCHAR2
87 ,p_business_group_id IN NUMBER
88 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
89 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
90 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
91 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
92 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
93 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
94 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
95 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
96 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
97 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
98 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
99 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
100 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
101 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
102 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
103 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
104 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
105 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
106 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
107 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
108 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
109 ,p_entry_information_category IN VARCHAR2 DEFAULT NULL
110 ,p_entry_information1 IN VARCHAR2 DEFAULT NULL
111 ,p_australian_resident_flag IN VARCHAR2
112 ,p_tax_free_threshold_flag IN VARCHAR2
113 ,p_rebate_amount IN NUMBER DEFAULT NULL
114 ,p_fta_claim_flag IN VARCHAR2
115 ,p_savings_rebate_flag IN VARCHAR2
116 ,p_help_sfss_flag IN VARCHAR2 /* Bug# 5258625 */
117 ,p_declaration_signed_date IN VARCHAR2
118 ,p_medicare_levy_variation_code IN VARCHAR2
119 ,p_spouse_mls_flag IN VARCHAR2
120 ,p_dependent_children IN VARCHAR2 DEFAULT NULL
121 ,p_tax_variation_type IN VARCHAR2
122 ,p_tax_variation_amount IN NUMBER DEFAULT NULL
123 ,p_tax_file_number IN VARCHAR2
124 ,p_update_warning OUT nocopy BOOLEAN
125 ) IS
126
127
128 type number_table is table of number not null index by binary_integer;
129 type varchar2_table is table of varchar2(60) index by binary_integer;
130
131 l_inp_value_id_table number_table;
132 l_scr_value_table varchar2_table;
133
134 l_dummy NUMBER := NULL;
135 l_element_type_id NUMBER :=0;
136 l_element_link_id NUMBER :=0;
137 l_element_entry_id NUMBER :=0;
138 l_object_version_number NUMBER;
139
140 l_entry_information_category pay_element_entries_f.entry_information_category%type;
141 l_entry_information1 pay_element_entries_f.entry_information1%type;
142
143 l_session_date DATE;
144 l_mode VARCHAR2(100);
145
146 CURSOR csr_paye_tax_element IS
147 SELECT pet.element_type_id
148 FROM pay_element_types_f pet
149 WHERE pet.element_name = 'Tax Information' -- Bug No: 3648796
150 AND l_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
151 AND legislation_code = 'AU';
152
153
154 CURSOR csr_paye_tax_input_values(p_element_type_id pay_input_values_f.element_type_id%TYPE) IS
155 SELECT piv.input_value_id
156 ,piv.name
157 FROM pay_input_values_f piv
158 WHERE piv.element_type_id = p_element_type_id
159 AND l_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
160
161
162 CURSOR csr_ele_entry (p_element_link NUMBER, p_inp_val NUMBER)IS
163 SELECT pee.element_entry_id,
164 object_version_number
165 FROM pay_element_entries_f pee,
166 pay_element_entry_values_f pev
167 WHERE pee.assignment_id = p_assignment_id
168 AND l_session_date BETWEEN pee.effective_start_date AND pee.effective_end_date
169 AND pee.element_link_id = p_element_link
170 AND pev.element_entry_id = pee.element_entry_id
171 AND l_session_date BETWEEN pev.effective_start_date AND pev.effective_end_date
172 AND pev.input_value_id = p_inp_val;
173
174
175 /* Bug# 4244787 - Cursor added to check whether future element entry exists for Tax Information when the mode
176 is UPDATE_CHANGE_INSERT*/
177 CURSOR csr_fut_ele_entry(c_element_entry_id NUMBER, c_effective_date DATE) is
178 SELECT pee.effective_start_date, pee.effective_end_date
179 FROM pay_element_entries_f pee
180 WHERE pee.element_entry_id = c_element_entry_id
181 AND pee.effective_start_date > c_effective_date
182 ORDER BY pee.effective_start_date;
183
184 cursor csr_leave_loading_flag
185 (p_assignment_id number
186 ,p_effective_date date
187 ) is
188 select scl.segment2
189 from per_all_assignments_f asg
190 , hr_soft_coding_keyflex scl
191 where scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
192 and asg.effective_start_date <= p_effective_date
193 and asg.effective_end_date >= p_effective_date
194 and asg.assignment_id = p_assignment_id;
195
196 /* Bug No : 2601218 - Cursor to get the database tax detail field values */
197 CURSOR get_prev_database_tax_fields(p_element_entry_id NUMBER,
198 p_au_res_input_value_id NUMBER,
199 p_tax_free_input_value_id NUMBER,
200 p_fta_input_value_id NUMBER,
201 p_savings_reb_input_value_id NUMBER,
202 p_hecs_sfss_input_value_id NUMBER,
203 p_dec_date_input_value_id NUMBER,
204 p_spouse_input_value_id NUMBER,
205 p_tfn_input_value_id NUMBER,
206 p_effective_start_date DATE) IS
207 SELECT decode(eev0.SCREEN_ENTRY_VALUE,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null) ,
208 eev1.SCREEN_ENTRY_VALUE ,
209 DECODE(
210 eev2.SCREEN_ENTRY_VALUE,
211 'N', 'N',
212 'Y', 'Y',
213 'NF','N',
214 'NP','N',
215 'NC','N',
216 'YF','Y',
217 'YP','Y',
218 'YC','Y',
219 'N'
220 ),
221 DECODE(
222 eev2.SCREEN_ENTRY_VALUE,
223 'Y', 'X',
224 'N', 'X',
225 'NF','F',
226 'NP','P',
227 'NC','C',
228 'YF','F',
229 'YP','P',
230 'YC','C',
231 'X'),
232 decode(eev4.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N',Null) ,
233 decode(eev4.SCREEN_ENTRY_VALUE,'YY','Y','NY','Y','N') ,
234 eev5.SCREEN_ENTRY_VALUE ,
235 decode(decode(eev6.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(eev3.SCREEN_ENTRY_VALUE,'Y','Y','N')) ,
236 eev7.SCREEN_ENTRY_VALUE ,
237 pee.entry_information1
238 FROM pay_element_entries_f pee ,
239 pay_element_entry_values_f eev0 ,
240 pay_element_entry_values_f eev1 ,
241 pay_element_entry_values_f eev2 ,
242 pay_element_entry_values_f eev3 ,
243 pay_element_entry_values_f eev4 ,
244 pay_element_entry_values_f eev5 ,
245 pay_element_entry_values_f eev6 ,
246 pay_element_entry_values_f eev7 ,
247 hr_lookups hrl0 ,
248 hr_lookups hrl1 ,
249 hr_lookups hrl2 ,
250 hr_lookups hrl3 ,
251 hr_lookups hrl4 ,
252 hr_lookups hrl5
253 WHERE pee.element_entry_id = p_element_entry_id
254 AND eev0.INPUT_VALUE_ID = p_au_res_input_value_id
255 AND eev0.ELEMENT_ENTRY_ID = pee.element_entry_id
256 AND hrl0.lookup_type (+) = 'AU_AUST_RES_SENR_AUS'
257 AND hrl0.lookup_code (+) = eev0.SCREEN_ENTRY_VALUE
258 AND hrl0.enabled_flag (+)= 'Y'
259 AND eev1.INPUT_VALUE_ID = p_tax_free_input_value_id
260 AND eev1.ELEMENT_ENTRY_ID = pee.element_entry_id
261 AND hrl1.lookup_type (+) = 'YES_NO'
262 AND hrl1.lookup_code (+) = eev1.SCREEN_ENTRY_VALUE
263 AND hrl1.enabled_flag (+)= 'Y'
264 AND eev2.INPUT_VALUE_ID = p_fta_input_value_id
265 AND eev2.ELEMENT_ENTRY_ID = pee.element_entry_id
266 AND hrl2.lookup_type (+) = 'HR_AU_FTA_PAYMENT_BASIS'
267 AND hrl2.lookup_code (+) = eev2.SCREEN_ENTRY_VALUE
268 AND hrl2.enabled_flag (+) = 'Y'
269 AND eev3.INPUT_VALUE_ID = p_savings_reb_input_value_id
270 AND eev3.ELEMENT_ENTRY_ID = pee.element_entry_id
271 AND hrl3.lookup_type(+) = 'YES_NO'
272 AND hrl3.lookup_code(+) = eev3.SCREEN_ENTRY_VALUE
273 AND hrl3.enabled_flag (+) = 'Y'
274 AND eev4.INPUT_VALUE_ID = p_hecs_sfss_input_value_id
275 AND eev4.ELEMENT_ENTRY_ID = pee.element_entry_id
276 AND hrl4.lookup_type(+) = 'AU_HECS_SFSS'
277 AND hrl4.lookup_code (+) = eev4.SCREEN_ENTRY_VALUE
278 AND hrl4.enabled_flag (+) = 'Y'
279 AND eev5.INPUT_VALUE_ID = p_dec_date_input_value_id
280 AND eev5.ELEMENT_ENTRY_ID = pee.element_entry_id
281 AND eev6.INPUT_VALUE_ID = p_spouse_input_value_id
282 AND eev6.ELEMENT_ENTRY_ID = pee.element_entry_id
283 AND hrl5.lookup_type (+) = 'AU_SPOUSE_MLS'
284 AND hrl5.lookup_code (+) = eev6.SCREEN_ENTRY_VALUE
285 AND hrl5.enabled_flag (+) = 'Y'
286 AND eev7.INPUT_VALUE_ID = p_tfn_input_value_id
287 AND eev7.ELEMENT_ENTRY_ID= pee.element_entry_id
288 AND p_effective_start_date between eev0.effective_start_date and eev0.effective_end_date
289 AND p_effective_start_date between eev1.effective_start_date and eev1.effective_end_date
290 AND p_effective_start_date between eev2.effective_start_date and eev2.effective_end_date
291 AND p_effective_start_date between eev3.effective_start_date and eev3.effective_end_date
292 AND p_effective_start_date between eev4.effective_start_date and eev4.effective_end_date
293 AND p_effective_start_date between eev5.effective_start_date and eev5.effective_end_date
294 AND p_effective_start_date between eev6.effective_start_date and eev6.effective_end_date
295 AND p_effective_start_date between eev7.effective_start_date and eev7.effective_end_date
296 AND p_effective_start_date between pee.effective_start_date and pee.effective_end_date;
297
298 /* Bug 7042960: 2008 Statutory Updates - FTA Claim Changes */
299 CURSOR get_fta_claim_flag(p_fta_claim_flag VARCHAR2) IS
300 SELECT DECODE(
301 p_fta_claim_flag,
302 'N', 'N',
303 'Y', 'N',
304 'NF','NF',
305 'NP','NP',
306 'NC','NC',
307 'YF','NF',
308 'YP','NP',
309 'YC','NC',
310 'N'
311 )
312 FROM DUAL
313 WHERE rownum=1;
314
315 /* Bug : 2601218 - Cursor to get the tax detail field value from parameters passed */
316 /* Bug : 3318756 - Replaced to_date with fnd_date.chardate_to_date in the select
317 statement for p_declaration_signed_date parameter */
318 CURSOR get_passed_tax_field_values( p_australian_resident_flag VARCHAR2
319 ,p_tax_free_threshold_flag VARCHAR2
320 ,p_fta_claim_flag VARCHAR2
321 ,p_savings_rebate_flag VARCHAR2
322 ,p_hecs_sfss_flag VARCHAR2
323 ,p_declaration_signed_date VARCHAR2
324 ,p_spouse_mls_flag VARCHAR2
325 ,p_tax_file_number VARCHAR2) IS
326 SELECT decode(p_australian_resident_flag,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null) ,
327 p_tax_free_threshold_flag,
328 DECODE(
329 p_fta_claim_flag,
330 'N', 'N',
331 'Y', 'Y',
332 'NF','N',
333 'NP','N',
334 'NC','N',
335 'YF','Y',
336 'YP','Y',
337 'YC','Y',
338 'N'
339 ),
340 DECODE(
341 p_fta_claim_flag,
342 'Y', 'X',
343 'N', 'X',
344 'NF','F',
345 'NP','P',
346 'NC','C',
347 'YF','F',
348 'YP','P',
349 'YC','C',
350 'X'),
351 decode(p_hecs_sfss_flag,'Y','Y','N','N','YY','Y','NY','N',Null) ,
352 decode(p_hecs_sfss_flag,'YY','Y','NY','Y','N') ,
353 fnd_date.date_to_canonical(fnd_date.chardate_to_date(p_declaration_signed_date)) ,
354 decode(decode(p_spouse_mls_flag,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(p_savings_rebate_flag,'Y','Y','N')) ,
355 p_tax_file_number
356 FROM DUAL
357 WHERE rownum=1;
358
359
360 -- Tax scale temp variable
361 L_TAX_SCALE INTEGER := 2;
362 L_lev_lod_flg VARCHAR2(3);
363 l_upd_tax_scale varchar2(2);
364
365 l_curr_australian_res_flag VARCHAR2(1);
366 l_curr_tax_free_threshold_flag VARCHAR2(1);
367 l_curr_fta_claim_flag VARCHAR2(1);
368 l_curr_basis_of_payment VARCHAR2(1);
369 l_curr_hecs_flag VARCHAR2(1);
370 l_curr_sfss_flag VARCHAR2(1);
371 l_curr_declaration_signed_date VARCHAR2(19);
372 l_curr_rebate_flag VARCHAR2(1);
373 l_curr_tax_file_number VARCHAR2(11);
374
375 l_fta_claim_flag VARCHAR2(2); /* Bug 7042960 */
376
377 l_prev_australian_res_flag VARCHAR2(1);
378 l_prev_tax_free_threshold_flag VARCHAR2(1);
379 l_prev_fta_claim_flag VARCHAR2(1);
380 l_prev_basis_of_payment VARCHAR2(1);
381 l_prev_hecs_flag VARCHAR2(1);
382 l_prev_sfss_flag VARCHAR2(1);
383 l_prev_declaration_signed_date VARCHAR2(19);
384 l_prev_rebate_flag VARCHAR2(1);
385 l_prev_tax_file_number VARCHAR2(11);
386 l_prev_entry_information1 VARCHAR2(19);
387 l_prev_record_exists VARCHAR2(1); -- Bug No: 3648796
388 l_start_date DATE; /*Bug# 4244787*/
389 l_end_date DATE; /*Bug# 4244787*/
390
391 l_calling_source VARCHAR2(10);
392
393 BEGIN
394
395 l_prev_record_exists := 'Y'; -- Bug No: 3648796
396 l_mode := p_mode; -- Bug 4108099
397
398 l_session_date := TRUNC(p_session_date);
399 if l_session_date = TRUNC(p_effective_start_date) -- Bug 4108099
400 and l_mode = 'UPDATE' then
401 l_mode := 'CORRECTION';
402 end if;
403
404 -- Get the leave loading flags.
405 OPEN csr_leave_loading_flag (p_assignment_id, l_session_date);
406 FETCH csr_leave_loading_flag INTO l_lev_lod_flg;
407 CLOSE csr_leave_loading_flag;
408
409 hr_utility.trace('after leave loading');
410
411 --SFSS Value cannot exist before 31-JUL-2000
412 -- Bug#5258625
413 if ((l_session_date < to_date('31/07/2000','DD/MM/YYYY')) and (substr(P_help_sfss_flag,2,1) is not null)) then
414 hr_utility.set_message(801,'HR_AU_SFSS_NOT_VALID');
415 hr_utility.raise_error;
416 end If;
417
418 -- MLS is not valid before 01-JUL-2000
419 if ((l_session_date < to_date('01/07/2000','DD/MM/YYYY')) and (substr(p_spouse_mls_flag,2,1) =
420 'Y'))
421 then
422 hr_utility.set_message(801,'HR_AU_MLS_NOT_VALID');
423 hr_utility.raise_error;
424 end If;
425
426
427 -- Derive the tax scale
428 L_tax_Scale := hr_au_tax_api.tax_scale
429 (
430 p_tax_file_number => p_tax_file_number
431 ,p_australian_resident_flag => p_australian_resident_flag
432 ,p_tax_free_threshold_flag => p_tax_free_threshold_flag
433 ,p_lev_lod_flg => l_lev_lod_flg
434 ,p_medicare_levy_variation_code => p_medicare_levy_variation_code
435 ,p_tax_variation_type => substr(p_tax_variation_type,1,1) /*Bug 4598178*/
436 );
437 hr_utility.trace('Tax Scale : '||to_char(L_tax_scale));
438 --
439 -- Get the element type id for the Tax element
440 hr_utility.trace('element_type_id');
441 --
442 OPEN csr_paye_tax_element;
443 fetch csr_paye_tax_element into l_element_type_id;
444 IF (csr_paye_tax_element%NOTFOUND)
445 THEN
446 CLOSE csr_paye_tax_element;
447 hr_utility.set_message(801,'HR_AU_NZ_ELE_TYP_NOT_FND');
448 hr_utility.raise_error;
449 END IF;
450 CLOSE csr_paye_tax_element;
451
452 --
453 -- Get the Input Value Id for each Tax Input
454
455 hr_utility.trace('input value id');
456
457 --
458 FOR rec_paye_tax_element in csr_paye_tax_input_values(l_element_type_id) LOOP
459 IF UPPER(rec_paye_tax_element.name) = 'AUSTRALIAN RESIDENT' THEN
460 l_inp_value_id_table(1) := rec_paye_tax_element.input_value_id;
461
462 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX FREE THRESHOLD' THEN
463 l_inp_value_id_table(2) := rec_paye_tax_element.input_value_id;
464
465 ELSIF UPPER(rec_paye_tax_element.name) = 'REBATE AMOUNT' THEN
466 l_inp_value_id_table(3) := rec_paye_tax_element.input_value_id;
467
468 ELSIF UPPER(rec_paye_tax_element.name) = 'FTA CLAIM' THEN
469 l_inp_value_id_table(4) := rec_paye_tax_element.input_value_id;
470
471 ELSIF UPPER(rec_paye_tax_element.name) = 'SAVINGS REBATE' THEN
472 l_inp_value_id_table(5) := rec_paye_tax_element.input_value_id;
473
474 ELSIF UPPER(rec_paye_tax_element.name) = 'HECS' THEN
475 l_inp_value_id_table(6) := rec_paye_tax_element.input_value_id;
476
477 ELSIF UPPER(rec_paye_tax_element.name) = 'DATE DECLARATION SIGNED' THEN
478 l_inp_value_id_table(7) := rec_paye_tax_element.input_value_id;
479
480 ELSIF UPPER(rec_paye_tax_element.name) = 'MEDICARE LEVY VARIATION' THEN
481 l_inp_value_id_table(8) := rec_paye_tax_element.input_value_id;
482
483 ELSIF UPPER(rec_paye_tax_element.name) = 'SPOUSE' THEN
484 l_inp_value_id_table(9) := rec_paye_tax_element.input_value_id;
485
486 ELSIF UPPER(rec_paye_tax_element.name) = 'DEPENDENT CHILDREN' THEN
487 l_inp_value_id_table(10) := rec_paye_tax_element.input_value_id;
488
489 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX VARIATION TYPE' THEN
490 l_inp_value_id_table(11) := rec_paye_tax_element.input_value_id;
491
492 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX VARIATION AMOUNT' THEN
493 l_inp_value_id_table(12) := rec_paye_tax_element.input_value_id;
494
495 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX SCALE' THEN
496 l_inp_value_id_table(13) := rec_paye_tax_element.input_value_id;
497
498 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX FILE NUMBER' THEN
499 l_inp_value_id_table(14) := rec_paye_tax_element.input_value_id;
500
501 END IF;
502 END LOOP;
503
504 --
505 -- Get the element link id for the tax information element
506 --
507 l_element_link_id := hr_entry_api.get_link
508 (p_assignment_id => p_assignment_id
509 ,p_element_type_id => l_element_type_id
510 ,p_session_date => l_session_date);
511
512
513 IF (l_element_link_id IS NULL OR l_element_link_id = 0)
514 THEN
515 hr_utility.set_message(801,'HR_AU_ELE_LNK_NOT_FND');
516 hr_utility.raise_error;
517 END IF;
518
519
520 IF (l_mode IN ('CORRECTION','UPDATE','UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE')) THEN
521 -----------------------------------------------------------------------------
522 -- Get the element entry of the tax element entry that is to be updated
523 ------------------------------------------------------------------------------
524 hr_utility.set_location('hr_au_tax_api.maintain_tax_info' ,7);
525
526 OPEN csr_ele_entry(l_element_link_id, l_inp_value_id_table(1));
527 FETCH csr_ele_entry INTO l_element_entry_id,l_object_version_number;
528 IF (csr_ele_entry%NOTFOUND) THEN
529 CLOSE csr_ele_entry;
530 hr_utility.set_message(801,'HR_AU_ELE_ENT_NOT_FND');
531 hr_utility.raise_error;
532 END IF;
533 CLOSE csr_ele_entry;
534
535
536 /*Bug# 4244787 - This piece of code added to change mode from UPDATE_CHANGE_INSERT to UPDATE when there are no
537 future element entry for Tax Information*/
538 IF l_mode = 'UPDATE_CHANGE_INSERT' THEN
539 OPEN csr_fut_ele_entry(l_element_entry_id, l_session_date);
540 FETCH csr_fut_ele_entry INTO l_start_date,l_end_date;
541 IF csr_fut_ele_entry%NOTFOUND THEN
542 l_mode := 'UPDATE';
543 END IF ;
544 CLOSE csr_fut_ele_entry;
545 END IF ;
546
547 -- Bug 2145933 -- Set the Tax Scales to S,C,I for Tax Scales 11,12,13 respectively
548
549 IF (l_tax_scale = 11 ) THEN
550 l_upd_tax_scale := 'S';
551 ELSIF (l_tax_scale = 12) THEN
552 l_upd_tax_scale := 'I';
553 ELSIF (l_tax_scale = 13) THEN
554 l_upd_tax_scale := 'C';
555 ELSE
556 l_upd_tax_scale := l_tax_scale;
557 END IF;
558
559 hr_utility.trace('Session Date = '||l_session_date);
560 hr_utility.trace('to_date = '||to_date('01/07/2008','DD/MM/YYYY'));
561
562 /* Bug 7042960: 2008 Statutory Updates - FTA Claim Changes */
563 IF (l_session_date >= to_date('01/07/2008','DD/MM/YYYY')) THEN
564 open get_fta_claim_flag(p_fta_claim_flag);
565 fetch get_fta_claim_flag into l_fta_claim_flag;
566 close get_fta_claim_flag;
567 hr_utility.trace('Inside IF: l_fta_claim_flag Value = '||l_fta_claim_flag);
568 ELSE
569 l_fta_claim_flag := p_fta_claim_flag;
570 hr_utility.trace('Inside ELSE: l_fta_claim_flag Value = '||l_fta_claim_flag);
571 END IF;
572
573 -- Check if the API is called from Tax Declaration form or not
574
575 /* Bug No : 2601218 - Check if the any of reportable tax field is changed
576 and pass the parameters to the core api accordingly */
577
578 l_calling_source := pay_au_tfn_magtape.get_value();
579
580 IF l_calling_source = 'FORM' THEN
581 l_entry_information_category := p_entry_information_category;
582 l_entry_information1 := p_entry_information1;
583 ELSE
584
585 open get_prev_database_tax_fields(
586 l_element_entry_id,
587 l_inp_value_id_table(1),
588 l_inp_value_id_table(2),
589 l_inp_value_id_table(4),
590 l_inp_value_id_table(5),
591 l_inp_value_id_table(6),
592 l_inp_value_id_table(7),
593 l_inp_value_id_table(9),
594 l_inp_value_id_table(14),
595 p_effective_start_date);
596 fetch get_prev_database_tax_fields into
597 l_prev_australian_res_flag ,
598 l_prev_tax_free_threshold_flag ,
599 l_prev_fta_claim_flag ,
600 l_prev_basis_of_payment ,
601 l_prev_hecs_flag ,
602 l_prev_sfss_flag ,
603 l_prev_declaration_signed_date ,
604 l_prev_rebate_flag ,
605 l_prev_tax_file_number ,
606 l_prev_entry_information1 ;
607
608 IF get_prev_database_tax_fields%notfound then
609 l_prev_record_exists := 'N';
610 END IF;
611
612 close get_prev_database_tax_fields;
613
614 open get_passed_tax_field_values(
615 p_australian_resident_flag ,
616 p_tax_free_threshold_flag ,
617 l_fta_claim_flag , /* Bug 7042960 */
618 p_savings_rebate_flag ,
619 p_help_sfss_flag , /* Bug#5258625 */
620 p_declaration_signed_date ,
621 p_spouse_mls_flag ,
622 p_tax_file_number);
623
624 fetch get_passed_tax_field_values into
625 l_curr_australian_res_flag ,
626 l_curr_tax_free_threshold_flag ,
627 l_curr_fta_claim_flag ,
628 l_curr_basis_of_payment ,
629 l_curr_hecs_flag ,
630 l_curr_sfss_flag ,
631 l_curr_declaration_signed_date ,
632 l_curr_rebate_flag ,
633 l_curr_tax_file_number ;
634 close get_passed_tax_field_values;
635
636 IF(l_curr_australian_res_flag <> l_prev_australian_res_flag or
637 l_curr_tax_free_threshold_flag <> l_prev_tax_free_threshold_flag or
638 l_curr_fta_claim_flag <> l_prev_fta_claim_flag or
639 l_curr_basis_of_payment <> l_prev_basis_of_payment or
640 l_curr_hecs_flag <> l_prev_hecs_flag or
641 l_curr_sfss_flag <> l_prev_sfss_flag or
642 l_curr_declaration_signed_date <> l_prev_declaration_signed_date or
643 l_curr_rebate_flag <> l_prev_rebate_flag or
644 l_curr_tax_file_number <> l_prev_tax_file_number or
645 l_prev_record_exists = 'N' ) THEN
646
647 l_entry_information_category := 'AU_TAX DEDUCTIONS';
648 l_entry_information1 := fnd_date.date_to_canonical(sysdate);
649 ELSE
650 l_entry_information_category := 'AU_TAX DEDUCTIONS';
651 l_entry_information1 := l_prev_entry_information1;
652 END IF;
653
654 END IF;
655
656 -- Pass entry_information1 as null if basis of payment is null
657 IF l_curr_basis_of_payment = 'X' THEN
658 l_entry_information_category := 'AU_TAX DEDUCTIONS';
659 l_entry_information1 := null;
660 END IF;
661
662 hr_utility.trace('Upd Tax Scale' || l_upd_tax_scale);
663 hr_utility.trace('fta_claim_flag' || p_fta_claim_flag);
664 py_element_entry_api.update_element_entry
665 (p_validate => p_validate
666 ,p_datetrack_update_mode=> l_mode
667 ,p_effective_date => l_session_date
668 ,p_business_group_id => p_business_group_id
669 ,p_element_entry_id => l_element_entry_id
670 ,p_object_version_number=> l_object_version_number
671 ,p_attribute_category => p_attribute_category
672 ,p_attribute1 => p_attribute1
673 ,p_attribute2 => p_attribute2
674 ,p_attribute3 => p_attribute3
675 ,p_attribute4 => p_attribute4
676 ,p_attribute5 => p_attribute5
677 ,p_attribute6 => p_attribute6
678 ,p_attribute7 => p_attribute7
679 ,p_attribute8 => p_attribute8
680 ,p_attribute9 => p_attribute9
681 ,p_attribute10 => p_attribute10
682 ,p_attribute11 => p_attribute11
683 ,p_attribute12 => p_attribute12
684 ,p_attribute13 => p_attribute13
685 ,p_attribute14 => p_attribute14
686 ,p_attribute15 => p_attribute15
687 ,p_attribute16 => p_attribute16
688 ,p_attribute17 => p_attribute17
689 ,p_attribute18 => p_attribute18
690 ,p_attribute19 => p_attribute19
691 ,p_attribute20 => p_attribute20
692 ,p_input_value_id1 => l_inp_value_id_table(1)
693 ,p_input_value_id2 => l_inp_value_id_table(2)
694 ,p_input_value_id3 => l_inp_value_id_table(3)
695 ,p_input_value_id4 => l_inp_value_id_table(4)
696 ,p_input_value_id5 => l_inp_value_id_table(5)
697 ,p_input_value_id6 => l_inp_value_id_table(6)
698 ,p_input_value_id7 => l_inp_value_id_table(7)
699 ,p_input_value_id8 => l_inp_value_id_table(8)
700 ,p_input_value_id9 => l_inp_value_id_table(9)
701 ,p_input_value_id10 => l_inp_value_id_table(10)
702 ,p_input_value_id11 => l_inp_value_id_table(11)
703 ,p_input_value_id12 => l_inp_value_id_table(12)
704 ,p_input_value_id13 => l_inp_value_id_table(13)
705 ,p_input_value_id14 => l_inp_value_id_table(14)
706 ,p_entry_value1 => p_australian_resident_flag
707 ,p_entry_value2 => p_tax_free_threshold_flag
708 ,p_entry_value3 => p_rebate_amount
709 ,p_entry_value4 => l_fta_claim_flag /* Bug 7042960 */
710 ,p_entry_value5 => p_savings_rebate_flag
711 ,p_entry_value6 => p_help_sfss_flag /* Bug#5258625 */
712 ,p_entry_value7 => p_declaration_signed_date
713 ,p_entry_value8 => p_medicare_levy_variation_code
714 ,p_entry_value9 => p_spouse_mls_flag
715 ,p_entry_value10 => p_dependent_children
716 ,p_entry_value11 => p_tax_variation_type
717 ,p_entry_value12 => p_tax_variation_amount
718 ,p_entry_value13 => l_upd_tax_scale /* Bug 2145933 */
719 ,p_entry_value14 => p_tax_file_number
720 ,p_entry_information_category => l_entry_information_category
721 ,p_entry_information1 => l_entry_information1
722 ,p_override_user_ent_chk => 'Y'
723 ,p_effective_start_date => p_effective_start_date
724 ,p_effective_end_date => p_effective_end_date
725 ,p_update_warning => p_update_warning);
726 END IF;
727 END maintain_PAYE_tax_info;
728
729
730 PROCEDURE maintain_SUPER_info
731 (p_validate IN BOOLEAN DEFAULT FALSE
732 ,p_assignment_id IN NUMBER
733 ,p_effective_start_date IN OUT nocopy DATE
734 ,p_effective_end_date IN OUT nocopy DATE
735 ,p_session_date IN DATE
736 ,p_mode IN VARCHAR2
737 ,p_business_group_id IN NUMBER
738 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
739 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
740 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
741 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
742 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
743 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
744 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
745 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
746 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
747 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
748 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
749 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
750 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
751 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
752 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
753 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
754 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
755 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
756 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
757 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
758 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
759 ,p_tfn_for_super_flag IN VARCHAR2 DEFAULT NULL
760 ,p_update_warning OUT nocopy BOOLEAN
761 ) IS
762
763
764 type number_table is table of number not null index by binary_integer;
765 type varchar2_table is table of varchar2(60) index by binary_integer;
766
767 l_inp_value_id_table number_table;
768 l_scr_value_table varchar2_table;
769
770 l_dummy NUMBER := NULL;
771 l_element_type_id NUMBER :=0;
772 l_element_link_id NUMBER :=0;
773 l_element_entry_id NUMBER :=0;
774 l_object_version_number NUMBER;
775 l_session_date DATE;
776
777 l_prev_spr_flag_value VARCHAR2(1);
778 l_calling_source VARCHAR2(10);
779 l_paye_element_entry_id NUMBER;
780 l_paye_object_version_number NUMBER;
781 l_paye_effective_start_date DATE;
782 l_paye_effective_end_date DATE;
783
784 -- Bug 3875404 - Local Variable added to l_tax_effective_date to support NOCOPY construct.
785 l_tax_effective_date DATE;
786
787 CURSOR csr_paye_tax_element IS
788 SELECT pet.element_type_id
789 FROM pay_element_types_f pet
790 WHERE pet.element_name = 'Superannuation Guarantee Information' -- Bug No: 3648796
791 AND l_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
792 AND legislation_code = 'AU';
793
794
795 CURSOR csr_paye_tax_input_values(p_element_type_id pay_input_values_f.element_type_id%TYPE) IS
796 SELECT piv.input_value_id
797 ,piv.name
798 FROM pay_input_values_f piv
799 WHERE piv.element_type_id = p_element_type_id
800 AND l_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
801
802
803 CURSOR csr_ele_entry (p_element_link NUMBER, p_inp_val NUMBER)IS
804 SELECT pee.element_entry_id
805 ,object_version_number
806 FROM pay_element_entries_f pee,
807 pay_element_entry_values_f pev
808 WHERE pee.assignment_id = p_assignment_id
809 AND l_session_date BETWEEN pee.effective_start_date AND pee.effective_end_date
810 AND pee.element_link_id = p_element_link
811 AND pev.element_entry_id = pee.element_entry_id
812 AND l_session_date BETWEEN pev.effective_start_date AND pev.effective_end_date
813 AND pev.input_value_id = p_inp_val;
814
815 /* Bug 2601218 : Cursor to get the database value of Superannuation flag */
816
817 CURSOR get_prev_tfn_super_value(p_element_entry_id NUMBER,
818 p_spr_flag_input_value_id NUMBER,
819 p_effective_start_date DATE) IS
820 SELECT nvl(screen_entry_value,'N')
821 FROM pay_element_entry_values_f
822 WHERE element_entry_id = p_element_entry_id
823 AND input_value_id = p_spr_flag_input_value_id
824 AND p_effective_start_date between effective_start_date and effective_end_date;
825
826 /* Bug 2601218 : Cursor to get the 'Tax Information' element entry details
827 for the current where Superannuation is updated */
828
829 CURSOR get_tax_info_to_update(p_effective_start_date DATE,
830 p_assignment_id NUMBER) IS
831 SELECT pee.element_entry_id,
832 pee.object_version_number,
833 pee.effective_start_date,
834 pee.effective_end_date
835 FROM pay_element_types_f pet,
836 pay_element_links_f pel,
837 pay_element_entries_f pee
838 WHERE pet.element_name = 'Tax Information'
839 AND pel.element_type_id = pet.element_type_id
840 AND pee.element_link_id = pel.element_link_id
841 AND pee.assignment_id = p_assignment_id
842 AND pel.effective_start_date between pet.effective_start_date and pet.effective_end_date
843 AND p_effective_start_date between pee.effective_start_date and pee.effective_end_date;
844
845
846 BEGIN
847 l_session_date := TRUNC(p_session_date);
848
849 --
850 -- Get the element type id for the Tax element
851 --
852 OPEN csr_paye_tax_element;
853 FETCH csr_paye_tax_element INTO l_element_type_id;
854 IF (csr_paye_tax_element%NOTFOUND)
855 THEN
856 CLOSE csr_paye_tax_element;
857 hr_utility.set_message(801,'HR_AU_NZ_ELE_TYP_NOT_FND');
858 hr_utility.raise_error;
859 END IF;
860 CLOSE csr_paye_tax_element;
861
862 --
863 -- Get the Input Value Id for each Tax Input
864 --
865 FOR rec_paye_tax_element in csr_paye_tax_input_values(l_element_type_id) LOOP
866 IF UPPER(rec_paye_tax_element.name) = 'TFN FOR SUPERANNUATION' THEN
867 l_inp_value_id_table(1) := rec_paye_tax_element.input_value_id;
868 END IF;
869 END LOOP;
870
871 --
872 -- Get the element link id for the tax information element
873 --
874 l_element_link_id := hr_entry_api.get_link
875 (p_assignment_id => p_assignment_id
876 ,p_element_type_id => l_element_type_id
877 ,p_session_date => l_session_date);
878 IF (l_element_link_id IS NULL OR l_element_link_id = 0)
879 THEN
880 hr_utility.set_message(801,'HR_AU_ELE_LNK_NOT_FND');
881 hr_utility.raise_error;
882 END IF;
883
884 IF (p_mode IN ('CORRECTION','UPDATE','UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE')) THEN
885
886 -----------------------------------------------------------------------------
887 -- Get the element entry of the tax element entry that is to be updated
888 ------------------------------------------------------------------------------
889
890 hr_utility.set_location('hr_au_tax_api.maintain_tax_info' ,7);
891
892 OPEN csr_ele_entry(l_element_link_id, l_inp_value_id_table(1));
893 FETCH csr_ele_entry INTO l_element_entry_id,l_object_version_number;
894 IF (csr_ele_entry%NOTFOUND) THEN
895 CLOSE csr_ele_entry;
896 hr_utility.set_message(801,'HR_AU_ELE_ENT_NOT_FND');
897 hr_utility.raise_error;
898 END IF;
899 CLOSE csr_ele_entry;
900
901 -- Bug 2601218 : Update 'Tax Information' segment 1 as last update date
902
903 l_calling_source := pay_au_tfn_magtape.get_value();
904
905 IF l_calling_source <> 'FORM' THEN
906
907 OPEN get_prev_tfn_super_value(
908 l_element_entry_id ,
909 l_inp_value_id_table(1),
910 p_effective_start_date );
911 FETCH get_prev_tfn_super_value INTO l_prev_spr_flag_value;
912 CLOSE get_prev_tfn_super_value;
913
914 IF nvl(p_tfn_for_super_flag,'N') <> nvl(l_prev_spr_flag_value,'N') THEN
915
916 OPEN get_tax_info_to_update(p_effective_start_date ,
917 p_assignment_id );
918 FETCH get_tax_info_to_update INTO l_paye_element_entry_id,
919 l_paye_object_version_number,
920 l_paye_effective_start_date,
921 l_paye_effective_end_date;
922 CLOSE get_tax_info_to_update;
923
924 -- Bug 3875404 - Stored the Value in a temporary variable for IN parameter binding.
925 l_tax_effective_date := l_paye_effective_start_date ;
926
927 py_element_entry_api.update_element_entry
928 (p_validate => p_validate
929 ,p_datetrack_update_mode => 'CORRECTION'
930 ,p_effective_date => l_tax_effective_date
931 ,p_business_group_id => p_business_group_id
932 ,p_element_entry_id => l_paye_element_entry_id
933 ,p_object_version_number => l_paye_object_version_number
934 ,p_entry_information_category => 'AU_TAX_DEDUCTIONS'
935 ,p_entry_information1 => fnd_date.date_to_canonical(sysdate)
936 ,p_override_user_ent_chk => 'Y'
937 ,p_effective_start_date => l_paye_effective_start_date
938 ,p_effective_end_date => l_paye_effective_end_date
939 ,p_update_warning => p_update_warning);
940
941 END IF;
942
943 END IF;
944
945 py_element_entry_api.update_element_entry
946 (p_validate => p_validate
947 ,p_datetrack_update_mode => p_mode
948 ,p_effective_date => l_session_date
949 ,p_business_group_id => p_business_group_id
950 ,p_element_entry_id => l_element_entry_id
951 ,p_object_version_number => l_object_version_number
952 ,p_attribute_category => p_attribute_category
953 ,p_attribute1 => p_attribute1
954 ,p_attribute2 => p_attribute2
955 ,p_attribute3 => p_attribute3
956 ,p_attribute4 => p_attribute4
957 ,p_attribute5 => p_attribute5
958 ,p_attribute6 => p_attribute6
959 ,p_attribute7 => p_attribute7
960 ,p_attribute8 => p_attribute8
961 ,p_attribute9 => p_attribute9
962 ,p_attribute10 => p_attribute10
963 ,p_attribute11 => p_attribute11
964 ,p_attribute12 => p_attribute12
965 ,p_attribute13 => p_attribute13
966 ,p_attribute14 => p_attribute14
967 ,p_attribute15 => p_attribute15
968 ,p_attribute16 => p_attribute16
969 ,p_attribute17 => p_attribute17
970 ,p_attribute18 => p_attribute18
971 ,p_attribute19 => p_attribute19
972 ,p_attribute20 => p_attribute20
973 ,p_input_value_id1 => l_inp_value_id_table(1)
974 ,p_entry_value1 => p_tfn_for_super_flag
975 ,p_override_user_ent_chk => 'Y'
976 ,p_effective_start_date => p_effective_start_date
977 ,p_effective_end_date => p_effective_end_date
978 ,p_update_warning => p_update_warning);
979
980 END IF;
981 END maintain_SUPER_info;
982
983
984 FUNCTION tax_scale
985 (p_tax_file_number IN VARCHAR2
986 ,p_australian_resident_flag IN VARCHAR2
987 ,p_tax_free_threshold_flag IN VARCHAR2
988 ,p_lev_lod_flg IN VARCHAR2
989 ,p_medicare_levy_variation_code IN VARCHAR2
990 ,p_tax_variation_type IN VARCHAR2
991 )
992 RETURN INTEGER IS
993
994 L_valid_tfn_provided BOOLEAN := FALSE;
995 L_tax_scale INTEGER; -- Bug No: 3648796
996 l_procedure varchar2(60); -- Bug No: 3648796
997
998 BEGIN
999 l_procedure := 'tax_scale'; -- Bug No: 3648796
1000 L_tax_scale := -1; -- Bug No: 3648796
1001
1002 hr_utility.set_location(g_package||l_procedure, 1);
1003 hr_utility.trace('p_tax_file_number - '||p_tax_file_number);
1004 hr_utility.trace('p_australian_resident_flag - '||p_australian_resident_flag);
1005 hr_utility.trace('p_tax_free_threshold_flag - '||p_tax_free_threshold_flag);
1006 hr_utility.trace('p_lev_lod_flg - '||p_lev_lod_flg);
1007 hr_utility.trace('p_medicare_levy_variation_code - '||p_medicare_levy_variation_code);
1008 hr_utility.trace('p_tax_variation_type - '||p_tax_variation_type);
1009 --
1010 -- Check if a valid TFN has been supplied
1011 IF nvl(P_Tax_File_number,'000 000 000') ='000 000 000' THEN
1012 L_valid_tfn_provided := FALSE;
1013 ELSE
1014 L_valid_tfn_provided := TRUE;
1015 END IF;
1016
1017 -- TAX SCALE 8
1018 IF P_tax_variation_Type = 'E' THEN
1019 L_tax_scale := 8;
1020
1021 -- No further processing needed.
1022 -- Bug 980658
1023 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1024 return (L_tax_scale);
1025 END IF;
1026
1027 -- TAX SCALE 9
1028 IF P_tax_variation_Type = 'P' THEN
1029 L_tax_scale := 9;
1030 -- No further processing needed.
1031 -- Bug 980658
1032 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1033 return (L_tax_scale);
1034 END IF;
1035
1036 -- TAX SCALE 10
1037 IF P_tax_variation_Type = 'F' THEN
1038 L_tax_scale := 10;
1039 -- No further processing needed.
1040 -- Bug 980658
1041 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1042 return (L_tax_scale);
1043 END IF;
1044
1045 -- TAX SCALE 4
1046 IF not L_valid_tfn_provided THEN
1047
1048 L_tax_scale := 4;
1049
1050 -- No further processing necessary, is no valid TFN supplied
1051 -- then always scale 4. Refer to bug 971984
1052 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1053 return (L_tax_scale);
1054 END IF;
1055
1056 -- TAX SCALE 3
1057 IF L_valid_tfn_provided
1058 AND substr(P_australian_resident_flag,1,1) = 'N' THEN /* Bug 2145933 */
1059 L_tax_scale := 3;
1060
1061 -- No further processing needed.
1062 -- If a person has a TFN and is non resident the scale is
1063 -- always 3, refer to bug 971982
1064 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1065 return (L_tax_scale);
1066 END IF;
1067
1068 -- TAX SCALE 1
1069 IF L_valid_tfn_provided
1070 AND P_tax_free_threshold_flag = 'N'
1071 AND substr(P_australian_resident_flag,1,1) = 'Y' THEN /* Bug 2145933 */
1072 L_tax_scale := 1;
1073
1074 -- No further processing needed.
1075 -- Bug 971980
1076 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1077 return (L_tax_scale);
1078 END IF;
1079
1080 -- Bug 2145933 - Tax Scales 11 - 13 based on Tax Scales C,S,I
1081
1082 IF L_valid_tfn_provided
1083 AND substr(p_australian_resident_flag,2,1) = 'S' THEN
1084 L_tax_scale := 11;
1085 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1086 return(L_tax_scale);
1087 END IF;
1088
1089 IF L_valid_tfn_provided
1090 AND substr(p_australian_resident_flag,2,1) = 'I' THEN
1091 L_tax_scale := 12;
1092 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1093 return(L_tax_scale);
1094 END IF;
1095
1096 IF L_valid_tfn_provided
1097 AND substr(p_australian_resident_flag,2,1) = 'C' THEN
1098 L_tax_scale := 13;
1099 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1100 return(L_tax_scale);
1101 END IF;
1102
1103
1104 -- TAX SCALE 5
1105 IF L_valid_tfn_provided
1106 AND substr(P_australian_resident_flag,1,1) = 'Y' /* Bug 2145933 */
1107 AND P_tax_free_threshold_flag = 'Y'
1108 AND P_medicare_levy_variation_code = 'F' THEN
1109 L_tax_scale := 5;
1110 -- No further processing needed.
1111 -- Bug 971978
1112 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1113 return (L_tax_scale);
1114 END IF;
1115
1116 -- TAX SCALE 6
1117 IF L_valid_tfn_provided
1118 AND substr(P_australian_resident_flag,1,1) = 'Y' /* Bug 2145933 */
1119 AND P_tax_free_threshold_flag = 'Y'
1120 AND p_medicare_levy_variation_code in ('H','HA') THEN
1121 L_tax_scale := 6;
1122 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1123 return (L_tax_scale);
1124 END IF;
1125
1126 -- TAX SCALE 2
1127 IF L_valid_tfn_provided
1128 AND substr(P_australian_resident_flag,1,1) = 'Y' /* Bug 2145933 */
1129 AND P_tax_free_threshold_flag = 'Y'
1130 AND P_lev_lod_flg = 'Y' THEN
1131 L_tax_scale := 2;
1132 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1133 return (L_tax_scale);
1134 END IF;
1135
1136 -- TAX SCALE 7
1137 IF L_valid_tfn_provided
1138 AND substr(P_australian_resident_flag,1,1) = 'Y' /* Bug 2145933 */
1139 AND P_tax_free_threshold_flag = 'Y'
1140 AND P_lev_lod_flg = 'N' THEN
1141 L_tax_scale := 7;
1142 hr_utility.trace('tax_scale - '||to_char(l_tax_scale));
1143 return (L_tax_scale);
1144 END IF;
1145
1146 return (L_tax_scale);
1147
1148 END tax_scale;
1149 --
1150 -- Function to validate the national identifier
1151 -- REturn Codes
1152 -- TRUE - VALID
1153 -- FALSE - INVALID
1154 PROCEDURE Validate_TFN
1155 (p_tax_file_number IN VARCHAR2)
1156 IS
1157 L_VALID BOOLEAN := FALSE;
1158 L_Weighted_result NUMBER;
1159 L_Remainder NUMBER;
1160
1161 BEGIN
1162
1163 -- Check the format of the TFN is 111 111 111
1164 -- Check length is 11 characters
1165 L_VALID := length (P_Tax_File_Number) <= 11;
1166
1167 -- Check space is in position 4
1168 IF L_VALID THEN
1169 L_VALID := substr (P_tax_file_number,4,1) = ' ';
1170 END IF;
1171
1172 -- Check space is in position 8
1173 IF L_VALID THEN
1174 L_VALID := substr (P_tax_file_number,8,1) = ' ';
1175 END IF;
1176
1177 -- Obtain the weighted result
1178 l_weighted_result :=
1179 TO_NUMBER (substr (p_tax_file_number,1,1) ) * 10 +
1180 TO_NUMBER (substr (p_tax_file_number,2,1) ) * 7 +
1181 TO_NUMBER (substr (p_tax_file_number,3,1) ) * 8 +
1182 TO_NUMBER (substr (p_tax_file_number,5,1) ) * 4 +
1183 TO_NUMBER (substr (p_tax_file_number,6,1) ) * 6 +
1184 TO_NUMBER (substr (p_tax_file_number,7,1) ) * 3+
1185 TO_NUMBER (substr (p_tax_file_number,9,1) ) * 5 +
1186 TO_NUMBER (substr (p_tax_file_number,10,1) ) * 2 +
1187 TO_NUMBER (substr (p_tax_file_number,11,1)
1188 );
1189
1190 l_weighted_result := l_weighted_result/11;
1191
1192 l_remainder := l_weighted_result - trunc(l_weighted_result,0);
1193
1194
1195 -- IF OK then return true
1196 IF l_valid and (l_remainder = 0) THEN
1197 null;
1198
1199 -- If a valid format and one of the secret nos then this is OK
1200 ELSIF l_valid and p_tax_file_number in ('111 111 111','333 333 333',
1201 '444 444 444','987 654 321',
1202 '222 222 222' ) THEN
1203 null;
1204
1205 ELSE
1206 hr_utility.set_message(801, 'HR_AU_INVALID_NATIONAL_ID');
1207 hr_utility.raise_error;
1208
1209 END IF;
1210 EXCEPTION WHEN OTHERS THEN
1211 hr_utility.set_message(801, 'HR_AU_INVALID_NATIONAL_ID');
1212 hr_utility.raise_error;
1213
1214 END VALIDATE_TFN;
1215 --
1216 --
1217 ---------------------------------------------------------------------------------------------
1218 -- PRIVATE PROCEDURE get_paye_input_ids
1219 ---------------------------------------------------------------------------------------------
1220 --
1221 procedure get_paye_input_ids
1222 (p_effective_date in date
1223 ,p_element_type_id in out nocopy number
1224 ,p_inp_value_id_table in out nocopy paye_number_table
1225 ) is
1226 --
1227 l_procedure constant varchar2(60) := 'get_paye_input_ids';
1228 --
1229 cursor csr_paye_input_values
1230 (p_element_type_id pay_input_values_f.element_type_id%type
1231 ,p_effective_date date
1232 ) is
1233 select piv.input_value_id
1234 , piv.name
1235 from pay_input_values_f piv
1236 where piv.element_type_id = p_element_type_id
1237 and p_effective_date between piv.effective_start_date and piv.effective_end_date
1238 order by piv.display_sequence;
1239 --
1240 cursor csr_paye_element
1241 (p_effective_date date
1242 ) is
1243 select pet.element_type_id
1244 from pay_element_types_f pet
1245 where pet.element_name = g_paye_element -- Bug No: 3648796
1246 and p_effective_date between pet.effective_start_date and pet.effective_end_date
1247 and legislation_code = g_legislation_code;
1248 --
1249 begin
1250 --
1251 -- get the element type id for the paye element
1252 --
1253 hr_utility.set_location(g_package||l_procedure, 1);
1254 --
1255 open csr_paye_element(p_effective_date);
1256 fetch csr_paye_element
1257 into p_element_type_id;
1258 if (csr_paye_element%notfound)
1259 then
1260 close csr_paye_element;
1261 hr_utility.trace('p_effective_date: '||to_char(p_effective_date,'MM/DD/YYYY'));
1262 hr_utility.set_message(801,'HR_AU_NZ_ELE_TYP_NOT_FND');
1263 hr_utility.raise_error;
1264 end if;
1265 close csr_paye_element;
1266 --
1267 -- get the input value id for each tax input
1268 --
1269 for rec_paye_element in csr_paye_input_values(p_element_type_id, p_effective_date)
1270 loop
1271 if upper(rec_paye_element.name) = g_paye_input1
1272 then
1273 p_inp_value_id_table(1) := rec_paye_element.input_value_id;
1274 --
1275 elsif upper(rec_paye_element.name) = g_paye_input2
1276 then
1277 p_inp_value_id_table(2) := rec_paye_element.input_value_id;
1278 --
1279 elsif upper(rec_paye_element.name) = g_paye_input3
1280 then
1281 p_inp_value_id_table(3) := rec_paye_element.input_value_id;
1282 --
1283 elsif upper(rec_paye_element.name) = g_paye_input4
1284 then
1285 p_inp_value_id_table(4) := rec_paye_element.input_value_id;
1286 --
1287 elsif upper(rec_paye_element.name) = g_paye_input5
1288 then
1289 p_inp_value_id_table(5) := rec_paye_element.input_value_id;
1290 --
1291 elsif upper(rec_paye_element.name) = g_paye_input6
1292 then
1293 p_inp_value_id_table(6) := rec_paye_element.input_value_id;
1294 --
1295 elsif upper(rec_paye_element.name) = g_paye_input7
1296 then
1297 p_inp_value_id_table(7) := rec_paye_element.input_value_id;
1298 --
1299 elsif upper(rec_paye_element.name) = g_paye_input8
1300 then
1301 p_inp_value_id_table(8) := rec_paye_element.input_value_id;
1302 --
1303 elsif upper(rec_paye_element.name) = g_paye_input9
1304 then
1305 p_inp_value_id_table(9) := rec_paye_element.input_value_id;
1306 --
1307 elsif upper(rec_paye_element.name) = g_paye_input10
1308 then
1309 p_inp_value_id_table(10) := rec_paye_element.input_value_id;
1310 --
1311 elsif upper(rec_paye_element.name) = g_paye_input11
1312 then
1313 p_inp_value_id_table(11) := rec_paye_element.input_value_id;
1314 --
1315 elsif upper(rec_paye_element.name) = g_paye_input12
1316 then
1317 p_inp_value_id_table(12) := rec_paye_element.input_value_id;
1318 --
1319 elsif upper(rec_paye_element.name) = g_paye_input13
1320 then
1321 p_inp_value_id_table(13) := rec_paye_element.input_value_id;
1322 --
1323 elsif upper(rec_paye_element.name) = g_paye_input14
1324 then
1325 p_inp_value_id_table(14) := rec_paye_element.input_value_id;
1326 --
1327
1328
1329 elsif upper(rec_paye_element.name) = g_paye_input15
1330 then
1331 p_inp_value_id_table(15) := rec_paye_element.input_value_id; --
1332
1333 else
1334 hr_utility.trace('p_element_type_id: '||to_char(p_element_type_id));
1335 hr_utility.trace('Input name: '||rec_paye_element.name);
1336 hr_utility.trace('p_effective_date: '||to_char(p_effective_date,'MM/DD/YYYY'));
1337 hr_utility.set_message(801,'HR_NZ_INPUT_VALUE_NOT_FOUND');
1338 hr_utility.raise_error;
1339 end if;
1340 end loop;
1341 --
1342 hr_utility.set_location(g_package||l_procedure, 10);
1343 --
1344 end get_paye_input_ids;
1345 --
1346 ---------------------------------------------------------------------------------------------
1347 -- PRIVATE FUNCTION valid_business_group
1348 ---------------------------------------------------------------------------------------------
1349 --
1350 function valid_business_group
1351 (p_business_group_id number
1352 ) return boolean is
1353 --
1354 l_procedure constant varchar2(60) := 'valid_business_group';
1355 l_legislation_code varchar2(30);
1356 --
1357 cursor csr_per_business_groups
1358 is
1359 select legislation_code
1360 from per_business_groups
1361 where business_group_id = p_business_group_id;
1362 --
1363 begin
1364 hr_utility.set_location(g_package||l_procedure, 1);
1365 open csr_per_business_groups;
1366 fetch csr_per_business_groups
1367 into l_legislation_code;
1368 if csr_per_business_groups%notfound
1369 then
1370 close csr_per_business_groups;
1371 hr_utility.set_location(g_package||l_procedure, 2);
1372 hr_utility.trace('p_business_group_id: '||to_char(p_business_group_id));
1373 return false;
1374 end if;
1375 close csr_per_business_groups;
1376 --
1377 hr_utility.set_location(g_package||l_procedure, 10);
1378 if l_legislation_code = g_legislation_code
1379 then
1380 return true;
1381 else
1382 return false;
1383 end if;
1384 --
1385 end valid_business_group;
1386 --
1387 ---------------------------------------------------------------------------------------------
1388 -- PUBLIC PROCEDURE create_paye_tax_info
1389 ---------------------------------------------------------------------------------------------
1390 --
1391 procedure create_paye_tax_info
1392 (p_validate in boolean default false
1393 ,p_effective_date in date
1394 ,p_business_group_id in number
1395 ,p_original_entry_id in number default null
1396 ,p_assignment_id in number
1397 ,p_entry_type in varchar2
1398 ,p_cost_allocation_keyflex_id in number default null
1399 ,p_updating_action_id in number default null
1400 ,p_comment_id in number default null
1401 ,p_reason in varchar2 default null
1402 ,p_target_entry_id in number default null
1403 ,p_subpriority in number default null
1404 ,p_date_earned in date default null
1405 ,p_attribute_category in varchar2 default null
1406 ,p_attribute1 in varchar2 default null
1407 ,p_attribute2 in varchar2 default null
1408 ,p_attribute3 in varchar2 default null
1409 ,p_attribute4 in varchar2 default null
1410 ,p_attribute5 in varchar2 default null
1411 ,p_attribute6 in varchar2 default null
1412 ,p_attribute7 in varchar2 default null
1413 ,p_attribute8 in varchar2 default null
1414 ,p_attribute9 in varchar2 default null
1415 ,p_attribute10 in varchar2 default null
1416 ,p_attribute11 in varchar2 default null
1417 ,p_attribute12 in varchar2 default null
1418 ,p_attribute13 in varchar2 default null
1419 ,p_attribute14 in varchar2 default null
1420 ,p_attribute15 in varchar2 default null
1421 ,p_attribute16 in varchar2 default null
1422 ,p_attribute17 in varchar2 default null
1423 ,p_attribute18 in varchar2 default null
1424 ,p_attribute19 in varchar2 default null
1425 ,p_attribute20 in varchar2 default null
1426 ,p_australian_resident_flag in varchar2
1427 ,p_tax_free_threshold_flag in varchar2
1428 ,p_rebate_amount in number default null
1429 ,p_fta_claim_flag in varchar2
1430 ,p_savings_rebate_flag in varchar2
1431 ,p_hecs_sfss_flag in varchar2
1432 ,p_declaration_signed_date in varchar2
1433 ,p_medicare_levy_variation_code in varchar2
1434 ,p_spouse_mls_flag in varchar2
1435 ,p_dependent_children in varchar2 default null
1436 ,p_tax_variation_type in varchar2
1437 ,p_tax_variation_amount in number default null
1438 ,p_tax_file_number in varchar2
1439 ,p_effective_start_date out nocopy date
1440 ,p_effective_end_date out nocopy date
1441 ,p_element_entry_id out nocopy number
1442 ,p_object_version_number out nocopy number
1443 ,p_create_warning out nocopy boolean
1444 ) is
1445 --
1446 type varchar2_table is table of varchar2(60) index by binary_integer;
1447 --
1448 l_procedure varchar2(33); -- Bug No: 3648796
1449 l_inp_value_id_table paye_number_table;
1450 l_effective_start_date date;
1451 l_effective_end_date date;
1452 l_element_entry_id number;
1453 l_object_version_number number;
1454 l_create_warning boolean;
1455 l_element_type_id number;
1456 l_element_link_id number;
1457 --
1458 -- tax scale temp variable
1459 l_update_warning boolean;
1460 --
1461 begin
1462 l_procedure := 'create_paye_tax_info'; -- Bug No: 3648796
1463
1464 hr_utility.set_location(g_package||l_procedure, 1);
1465 --
1466 -- Ensure business group supplied is Australian
1467 --
1468 if not valid_business_group(p_business_group_id)
1469 then
1470 hr_utility.set_location(g_package||l_procedure, 2);
1471 hr_utility.set_message(801,'HR_7208_API_BUS_GRP_INVALID');
1472 hr_utility.raise_error;
1473 end if;
1474 --
1475 -- Get Element type id and input value ids
1476 --
1477 get_paye_input_ids(p_effective_date, l_element_type_id, l_inp_value_id_table);
1478 --
1479 -- Get the element link id for the Superannuation Contribution element
1480 --
1481 l_element_link_id := hr_entry_api.get_link
1482 (p_assignment_id => p_assignment_id
1483 ,p_element_type_id => l_element_type_id
1484 ,p_session_date => p_effective_date
1485 );
1486 if (l_element_link_id is null or l_element_link_id = 0)
1487 then
1488 hr_utility.set_message(801,'HR_AU_NZ_ELE_LNK_NOT_FND');
1489 hr_utility.raise_error;
1490 end if;
1491 --
1492 validate_tfn(p_tax_file_number);
1493 --
1494 py_element_entry_api.create_element_entry
1495 (p_validate => p_validate
1496 ,p_effective_date => p_effective_date
1497 ,p_business_group_id => p_business_group_id
1498 ,p_original_entry_id => p_original_entry_id
1499 ,p_assignment_id => p_assignment_id
1500 ,p_element_link_id => l_element_link_id
1501 ,p_entry_type => p_entry_type
1502 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
1503 ,p_updating_action_id => p_updating_action_id
1504 ,p_comment_id => p_comment_id
1505 ,p_reason => p_reason
1506 ,p_target_entry_id => p_target_entry_id
1507 ,p_subpriority => p_subpriority
1508 ,p_date_earned => p_date_earned
1509 ,p_attribute_category => p_attribute_category
1510 ,p_attribute1 => p_attribute1
1511 ,p_attribute2 => p_attribute2
1512 ,p_attribute3 => p_attribute3
1513 ,p_attribute4 => p_attribute4
1514 ,p_attribute5 => p_attribute5
1515 ,p_attribute6 => p_attribute6
1516 ,p_attribute7 => p_attribute7
1517 ,p_attribute8 => p_attribute8
1518 ,p_attribute9 => p_attribute9
1519 ,p_attribute10 => p_attribute10
1520 ,p_attribute11 => p_attribute11
1521 ,p_attribute12 => p_attribute12
1522 ,p_attribute13 => p_attribute13
1523 ,p_attribute14 => p_attribute14
1524 ,p_attribute15 => p_attribute15
1525 ,p_attribute16 => p_attribute16
1526 ,p_attribute17 => p_attribute17
1527 ,p_attribute18 => p_attribute18
1528 ,p_attribute19 => p_attribute19
1529 ,p_attribute20 => p_attribute20
1530 ,p_override_user_ent_chk => 'Y'
1531 ,p_effective_start_date => p_effective_start_date
1532 ,p_effective_end_date => p_effective_end_date
1533 ,p_element_entry_id => l_element_entry_id
1534 ,p_object_version_number => l_object_version_number
1535 ,p_create_warning => l_create_warning
1536 );
1537 --
1538 maintain_PAYE_tax_info
1539 (p_validate => p_validate
1540 ,p_assignment_id => p_assignment_id
1541 ,p_effective_start_date => l_effective_start_date
1542 ,p_effective_end_date => l_effective_end_date
1543 ,p_session_date => p_effective_date
1544 ,p_mode => 'CORRECTION'
1545 ,p_business_group_id => p_business_group_id
1546 ,p_attribute_category => p_attribute_category
1547 ,p_attribute1 => p_attribute1
1548 ,p_attribute2 => p_attribute2
1549 ,p_attribute3 => p_attribute3
1550 ,p_attribute4 => p_attribute4
1551 ,p_attribute5 => p_attribute5
1552 ,p_attribute6 => p_attribute6
1553 ,p_attribute7 => p_attribute7
1554 ,p_attribute8 => p_attribute8
1555 ,p_attribute9 => p_attribute9
1556 ,p_attribute10 => p_attribute10
1557 ,p_attribute11 => p_attribute11
1558 ,p_attribute12 => p_attribute12
1559 ,p_attribute13 => p_attribute13
1560 ,p_attribute14 => p_attribute14
1561 ,p_attribute15 => p_attribute15
1562 ,p_attribute16 => p_attribute16
1563 ,p_attribute17 => p_attribute17
1564 ,p_attribute18 => p_attribute18
1565 ,p_attribute19 => p_attribute19
1566 ,p_attribute20 => p_attribute20
1567 ,p_entry_information_category => 'AU_TAX DEDUCTIONS'
1568 ,p_entry_information1 => fnd_date.date_to_canonical(sysdate)
1569 ,p_australian_resident_flag => p_australian_resident_flag
1570 ,p_tax_free_threshold_flag => p_tax_free_threshold_flag
1571 ,p_rebate_amount => p_rebate_amount
1572 ,p_fta_claim_flag => p_fta_claim_flag
1573 ,p_savings_rebate_flag => p_savings_rebate_flag
1574 ,p_help_sfss_flag => p_hecs_sfss_flag /* Bug#5258625 */
1575 ,p_declaration_signed_date => p_declaration_signed_date
1576 ,p_medicare_levy_variation_code => p_medicare_levy_variation_code
1577 ,p_spouse_mls_flag => p_spouse_mls_flag
1578 ,p_dependent_children => p_dependent_children
1579 ,p_tax_variation_type => p_tax_variation_type
1580 ,p_tax_variation_amount => p_tax_variation_amount
1581 ,p_tax_file_number => p_tax_file_number
1582 ,p_update_warning => l_update_warning
1583 );
1584 --
1585 hr_utility.set_location(g_package||l_procedure, 30);
1586
1587 end create_paye_tax_info;
1588
1589 ---------------------------------------------------------------------------------------------
1590 -- PUBLIC PROCEDURE update_adi_tax_crp
1591 ---------------------------------------------------------------------------------------------
1592
1593 procedure update_adi_tax_crp
1594 (p_validate in boolean default false
1595 ,p_assignment_id in number
1596 ,p_hire_date in date
1597 ,p_business_group_id in number
1598 ,p_payroll_id in number
1599 ,p_legal_employer in varchar2
1600 ,p_tax_file_number in varchar2
1601 ,p_tax_free_threshold in varchar2
1602 ,p_australian_resident in varchar2
1603 ,p_hecs in varchar2
1604 ,p_sfss in varchar2
1605 ,p_leave_loading in varchar2
1606 ,p_basis_of_payment in varchar2
1607 ,p_declaration_signed_date in varchar2
1608 ,p_medicare_levy_surcharge in varchar2
1609 ,p_medicare_levy_exemption in varchar2
1610 ,p_medicare_levy_dep_children in varchar2 default null
1611 ,p_medicare_levy_spouse in varchar2
1612 ,p_tax_variation_type in varchar2
1613 ,p_tax_variation_amount in number default null
1614 ,p_tax_variation_bonus in varchar2
1615 ,p_rebate_amount in number default null
1616 ,p_savings_rebate in varchar2
1617 ,p_ftb_claim in varchar2
1618 ,p_senior_australian in varchar2
1619 ,p_effective_date in date default null
1620 ) IS
1621 cursor csr_tax_element is
1622 select pet.element_type_id
1623 from pay_element_types_f pet
1624 where pet.element_name = g_paye_element
1625 and p_hire_date between pet.effective_start_date and pet.effective_end_date
1626 and legislation_code = g_legislation_code;
1627 --
1628 cursor csr_asg_version is
1629 select object_version_number
1630 from per_assignments_f
1631 where assignment_id = p_assignment_id
1632 and business_group_id = p_business_group_id;
1633 --
1634 cursor csr_element_entry(p_element_link_id number) is
1635 select element_entry_id
1636 from pay_element_entries_f
1637 where assignment_id = p_assignment_id
1638 and element_link_id = p_element_link_id;
1639 --
1640 -- Cursor to check the element links.
1641 -- Checks if the element is linked to a payroll or linked to all payrolls
1642 --
1643 cursor csr_element_link
1644 (p_element_type_id pay_element_types_f.element_type_id%type
1645 ,p_payroll_id pay_element_links_f.payroll_id%type
1646 ,p_business_group_id pay_element_links_f.business_group_id%type
1647 ,p_hire_date date
1648 ) is
1649 select element_link_id
1650 , object_version_number
1651 from pay_element_links_f
1652 where element_type_id = p_element_type_id
1653 and business_group_id = p_business_group_id
1654 and (payroll_id = p_payroll_id or link_to_all_payrolls_flag is not null)
1655 and p_hire_date between effective_start_date and effective_end_date;
1656 --
1657 l_procedure varchar2(100) := g_package||'update_adi_tax_crp';
1658 --
1659 l_effective_start_date per_all_assignments_f.effective_start_date%type;
1660 l_effective_end_date per_all_assignments_f.effective_end_date%type;
1661 l_element_entry_id pay_element_entries_f.element_entry_id%type;
1662 l_element_type_id pay_element_types_f.element_type_id%type;
1663 l_element_link_id pay_element_links_f.element_link_id%type;
1664 l_object_version_number per_all_assignments_f.object_version_number%type;
1665 l_create_warning boolean;
1666 l_update_warning boolean;
1667 l_cagr_grade_def_id number;
1668 l_cagr_concatenated_segments varchar2(2000);
1669 l_comment_id number;
1670 l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type;
1671 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
1672 l_no_managers_warning boolean;
1673 l_other_manager_warning boolean;
1674 l_special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type;
1675 l_people_group_id per_all_assignments_f.people_group_id%type;
1676 l_group_name pay_people_groups.group_name%type;
1677 l_org_now_no_manager_warning boolean;
1678 l_spp_delete_warning boolean;
1679 l_entries_changed_warning varchar2(1);
1680 l_tax_district_changed_warning boolean;
1681 l_australian_resident_flag varchar2(20);
1682 l_hecs_sfss_flag varchar2(10);
1683 l_fta_claim_flag varchar2(10);
1684 l_spouse_mls_flag varchar2(20);
1685 l_tax_variation_type varchar2(20);
1686 --
1687 begin
1688 g_debug := hr_utility.debug_enabled;
1689 if g_debug then
1690 hr_utility.set_location(l_procedure,10);
1691 hr_utility.trace('p_assignment_id => '||p_assignment_id);
1692 hr_utility.trace('p_business_group_id => '||p_business_group_id);
1693 hr_utility.trace('p_hire_date => '||p_hire_date);
1694 hr_utility.trace('p_payroll_id => '||to_char(p_payroll_id));
1695 END if;
1696 --
1697 -- Need the object_version_number to update the assignment
1698 --
1699 open csr_asg_version;
1700 fetch csr_asg_version into l_object_version_number;
1701 close csr_asg_version;
1702 --
1703 -- Update assignment with leave loading flag and Legal Employer.
1704 -- We are able to pass on the variables as is here since they are not overloaded
1705 -- for this API.
1706 -- The main purpose of this API is to enter field related to Australian Tax
1707 -- therefore additional fields (e.g. descriptive flex) are not provided.
1708 --
1709 hr_au_assignment_api.update_au_emp_asg
1710 (p_validate => p_validate
1711 ,p_effective_date => p_hire_date
1712 ,p_datetrack_update_mode => 'CORRECTION' -- hard code this mode since we only use this API for RI
1713 ,p_assignment_id => p_assignment_id
1714 ,p_object_version_number => l_object_version_number -- out parameter
1715 ,p_legal_employer_id => p_legal_employer
1716 ,p_lev_lod_flg => p_leave_loading
1717 ,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
1718 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
1719 ,p_comment_id => l_comment_id -- out parameter
1720 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
1721 ,p_effective_start_date => l_effective_start_date -- out parameter
1722 ,p_effective_end_date => l_effective_end_date -- out parameter
1723 ,p_concatenated_segments => l_concatenated_segments -- out parameter
1724 ,p_no_managers_warning => l_no_managers_warning -- out parameter
1725 ,p_other_manager_warning => l_other_manager_warning -- out parameter
1726 );
1727 IF g_debug THEN
1728 hr_utility.set_location(l_procedure,20);
1729 hr_utility.trace('l_object_version_number => '||l_object_version_number);
1730 END if;
1731
1732 --
1733 -- The intended use of the following API call is to allow the update of
1734 -- payroll only ,for the assignment on implementation (creation) of employee
1735 -- tax information. This means that the majority
1736 -- of the out parameters will not need to be returned.
1737 --
1738 hr_assignment_api.update_emp_asg_criteria
1739 (p_effective_date => p_hire_date
1740 ,p_datetrack_update_mode => 'CORRECTION'
1741 ,p_assignment_id => p_assignment_id
1742 ,p_validate => p_validate
1743 ,p_payroll_id => p_payroll_id
1744 ,p_object_version_number => l_object_version_number -- from when we updated the asg earlier
1745 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1746 ,p_people_group_id => l_people_group_id
1747 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1748 ,p_group_name => l_group_name
1749 ,p_effective_start_date => l_effective_start_date
1750 ,p_effective_end_date => l_effective_end_date
1751 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1752 ,p_other_manager_warning => l_other_manager_warning
1753 ,p_spp_delete_warning => l_spp_delete_warning
1754 ,p_entries_changed_warning => l_entries_changed_warning
1755 ,p_tax_district_changed_warning => l_tax_district_changed_warning
1756 ,p_concatenated_segments => l_concatenated_segments
1757 );
1758
1759 IF g_debug THEN
1760 hr_utility.set_location(l_procedure,30);
1761 hr_utility.trace('p_australian_resident_flag => '||p_australian_resident);
1762 hr_utility.trace('p_senior_australian => '||p_senior_australian);
1763 hr_utility.trace('p_payroll_id => '||to_char(p_payroll_id));
1764
1765 END if;
1766
1767 --
1768 -- Create the "Tax Information"
1769 -- We call the existing API since it already encapsulates the tax business logic and therefore
1770 -- it will be contained in a single location.
1771 -- Parameters (ie. Input Values) are overloaded for "Tax Information" so we need
1772 -- to "translate" the appropriate values before calling the main API.
1773 --
1774 -- ---------------------------------------
1775 -- AUSTRALIAN RESIDENT and SENIOR
1776 -- ---------------------------------------
1777 --p_austrlian_resident_flag values Y or N
1778 --p_seniour values C,I,N,S
1779 --p_australian_resident_flag values NN,YC,YI,YN,YS,N,Y
1780 --
1781 -- Senior flag cannot be set if not an Australian resident
1782 --
1783 if p_australian_resident = 'N' then
1784 l_australian_resident_flag := p_australian_resident;
1785 else
1786 l_australian_resident_flag := p_australian_resident || p_senior_australian;
1787 end if;
1788 if g_debug then
1789 hr_utility.set_location(l_procedure,31);
1790 end if;
1791
1792 -- ---------------------------------------
1793 -- PAYMENT BASIS and FTB CLAIM
1794 -- ---------------------------------------
1795 --p_ftb_claim_flag values Y or N
1796 --p_basis_of_payment values C,F,P
1797 --l_fta_claim_flag values N,NC,NF,NP,Y,YC,YF,YP
1798 --
1799 l_fta_claim_flag := p_ftb_claim || p_basis_of_payment;
1800 if g_debug then
1801 hr_utility.set_location(l_procedure,32);
1802 end if;
1803
1804 -- ---------------------------------------
1805 -- HECS and SFSS
1806 -- ---------------------------------------
1807 --p_hecs_flag values Y,N
1808 --p_sfss_flag values Y,N
1809 --l_hecs_sfss_flag values NY,YY,N,Y
1810 --
1811 if p_sfss = 'N' then
1812 l_hecs_sfss_flag := p_hecs;
1813 else
1814 l_hecs_sfss_flag := p_hecs || p_sfss;
1815 end if;
1816 --
1817 if g_debug then
1818 hr_utility.set_location(l_procedure,33);
1819 end if;
1820
1821 -- ---------------------------------------
1822 -- SPOUSE and MLS
1823 -- ---------------------------------------
1824 --p_spouse_flag values Y,N
1825 --p_medicare_levy_surcharge_flag values Y,N
1826 --p_spouse_mls_flag values Y,N,NY,YY
1827 --
1828 if p_medicare_levy_surcharge = 'N' then
1829 l_spouse_mls_flag := p_medicare_levy_spouse;
1830 else
1831 l_spouse_mls_flag := p_medicare_levy_spouse || p_medicare_levy_surcharge;
1832 end if;
1833
1834 -- ---------------------------------------
1835 -- TAX VARIATION and BONUS
1836 -- ---------------------------------------
1837 -- p_tax_variation_type values E,N,F,P
1838 -- p_tax_variation_bonus values Y,N
1839 -- l_tax_variation_type values E,EN,EY,F,FN,FY,N,P,PN,PY
1840 --
1841 if p_tax_variation_type = 'N' then
1842 l_tax_variation_type := p_tax_variation_type;
1843 else
1844 l_tax_variation_type := p_tax_variation_type || p_tax_variation_bonus;
1845 end if;
1846
1847 if g_debug then
1848 hr_utility.set_location(l_procedure,40);
1849 hr_utility.trace('l_australian_resident_flag => '||l_australian_resident_flag);
1850 hr_utility.trace('l_fta_claim_flag => '||l_fta_claim_flag);
1851 hr_utility.trace('l_hecs_sfss_flag => '||l_hecs_sfss_flag);
1852 hr_utility.trace('l_spouse_mls_flag => '||l_spouse_mls_flag);
1853 hr_utility.trace('l_tax_variation_type => '||l_tax_variation_type);
1854 end if;
1855
1856 -- If the Tax Information already exists for this assignment then
1857 -- call maintain_paye_tax_info... otherwise call call create_paye_tax_info.
1858 -- (After updating the payroll against the assignment the Tax element entry
1859 -- gets automatically created since it is a standard element. However we
1860 -- still allow for case when it does not exist.
1861 --
1862 -- First need to get the element_type_id to then find the element_link_id
1863 open csr_tax_element;
1864 fetch csr_tax_element
1865 into l_element_type_id;
1866 if (csr_tax_element%notfound)
1867 then
1868 close csr_tax_element;
1869 IF g_debug THEN
1870 hr_utility.set_location(l_procedure, 50);
1871 hr_utility.trace('p_effective_date: '||to_char(p_hire_date,'MM/DD/YYYY'));
1872 END if;
1873 hr_utility.set_message(801,'HR_AU_NZ_ELE_TYP_NOT_FND');
1874 hr_utility.raise_error;
1875 end if;
1876 close csr_tax_element;
1877
1878 if g_debug then
1879 hr_utility.set_location(l_procedure,60);
1880 hr_utility.trace('p_assignment_id => '||p_assignment_id);
1881 hr_utility.trace('l_element_type_id => '||l_element_type_id);
1882 end if;
1883 --
1884 -- Got the element_type_id so can now get the element_link_id
1885 l_element_link_id := hr_entry_api.get_link
1886 (p_assignment_id => p_assignment_id
1887 ,p_element_type_id => l_element_type_id
1888 ,p_session_date => p_hire_date
1889 );
1890 if (l_element_link_id is null or l_element_link_id = 0)
1891 then
1892 if g_debug then
1893 hr_utility.set_location(l_procedure, 61);
1894 end if;
1895 --
1896 -- It is possible that the current assignment is on a payroll for which an element link does
1897 -- not exist, therefore we need to check the link before we create.
1898 --
1899 open csr_element_link(l_element_type_id, p_payroll_id, p_business_group_id, p_hire_date);
1900 fetch csr_element_link
1901 into l_element_link_id
1902 , l_object_version_number;
1903 if csr_element_link%notfound then
1904 --
1905 -- Create the element link
1906 pay_element_link_api.CREATE_ELEMENT_LINK
1907 (P_EFFECTIVE_DATE => p_hire_date
1908 ,P_ELEMENT_TYPE_ID => l_element_type_id
1909 ,P_BUSINESS_GROUP_ID => p_business_group_id
1910 ,P_COSTABLE_TYPE => 'N'
1911 ,P_PAYROLL_ID => p_payroll_id
1912 ,P_LINK_TO_ALL_PAYROLLS_FLAG => 'N'
1913 ,P_STANDARD_LINK_FLAG => 'Y'
1914 ,P_COST_CONCAT_SEGMENTS => null
1915 ,P_BALANCE_CONCAT_SEGMENTS => null
1916 ,P_ELEMENT_LINK_ID => l_element_link_id
1917 ,P_COMMENT_ID => l_comment_id
1918 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1919 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1920 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1921 );
1922
1923 end if;
1924 --hr_utility.set_message(801,'HR_AU_NZ_ELE_LNK_NOT_FND');
1925 --hr_utility.raise_error;
1926 end if;
1927 --
1928 if g_debug then
1929 hr_utility.set_location(l_procedure, 62);
1930 hr_utility.trace('l_element_link_id = '||to_char(l_element_link_id));
1931 end if;
1932 --
1933 -- Verify whether an element entry exists.
1934 -- (This is where to use the element_link_id)
1935 --
1936 open csr_element_entry(l_element_link_id);
1937 fetch csr_element_entry into l_element_entry_id;
1938 if csr_element_entry%notfound then
1939 if g_debug then
1940 hr_utility.set_location(l_procedure,70);
1941 end if;
1942 --
1943 -- The element entry does not exist so call the CREATE API
1944 --
1945 close csr_element_entry;
1946 hr_au_tax_api.create_paye_tax_info
1947 (p_validate => false
1948 ,p_effective_date => p_hire_date
1949 ,p_business_group_id => p_business_group_id
1950 ,p_assignment_id => p_assignment_id
1951 ,p_entry_type => 'E' --p_entry_type
1952 ,p_australian_resident_flag => l_australian_resident_flag --AU_AUST_RES_SENR_AUS
1953 ,p_tax_free_threshold_flag => p_tax_free_threshold
1954 ,p_rebate_amount => p_rebate_amount
1955 ,p_fta_claim_flag => l_fta_claim_flag
1956 ,p_savings_rebate_flag => p_savings_rebate
1957 ,p_hecs_sfss_flag => l_hecs_sfss_flag
1958 ,p_declaration_signed_date => p_declaration_signed_date
1959 ,p_medicare_levy_variation_code => p_medicare_levy_exemption
1960 ,p_spouse_mls_flag => l_spouse_mls_flag
1961 ,p_dependent_children => p_medicare_levy_dep_children
1962 ,p_tax_variation_type => l_tax_variation_type
1963 ,p_tax_variation_amount => p_tax_variation_amount
1964 ,p_tax_file_number => p_tax_file_number
1965 ,p_effective_start_date => l_effective_start_date
1966 ,p_effective_end_date => l_effective_end_date
1967 ,p_element_entry_id => l_element_entry_id
1968 ,p_object_version_number => l_object_version_number
1969 ,p_create_warning => l_create_warning
1970 );
1971 else
1972 if g_debug then
1973 hr_utility.set_location(l_procedure,80);
1974 hr_utility.trace('dep children = '||p_medicare_levy_dep_children);
1975
1976 end if;
1977 --
1978 -- The element entry exists so call the UPDATE API
1979 --
1980 close csr_element_entry;
1981 maintain_PAYE_tax_info
1982 (p_validate => false
1983 ,p_assignment_id => p_assignment_id
1984 ,p_effective_start_date => l_effective_start_date
1985 ,p_effective_end_date => l_effective_end_date
1986 ,p_session_date => p_hire_date
1987 ,p_mode => 'CORRECTION'
1988 ,p_business_group_id => p_business_group_id
1989 ,p_entry_information_category => 'AU_TAX DEDUCTIONS'
1990 ,p_entry_information1 => fnd_date.date_to_canonical(sysdate)
1991 ,p_australian_resident_flag => l_australian_resident_flag
1992 ,p_tax_free_threshold_flag => p_tax_free_threshold
1993 ,p_rebate_amount => p_rebate_amount
1994 ,p_fta_claim_flag => l_fta_claim_flag
1995 ,p_savings_rebate_flag => p_savings_rebate
1996 ,p_help_sfss_flag => l_hecs_sfss_flag /* Bug#5258625 */
1997 ,p_declaration_signed_date => p_declaration_signed_date
1998 ,p_medicare_levy_variation_code => p_medicare_levy_exemption
1999 ,p_spouse_mls_flag => l_spouse_mls_flag
2000 ,p_dependent_children => p_medicare_levy_dep_children
2001 ,p_tax_variation_type => l_tax_variation_type
2002 ,p_tax_variation_amount => p_tax_variation_amount
2003 ,p_tax_file_number => p_tax_file_number
2004 ,p_update_warning => l_update_warning
2005 );
2006 end if;
2007 --
2008 if g_debug then
2009 hr_utility.set_location(l_procedure,90);
2010 end if;
2011 end update_adi_tax_crp;
2012
2013 END hr_au_tax_api ;