1 PACKAGE BODY pay_au_tfn_magtape_flags AS
2 /* $Header: pyautfnf.pkb 120.5.12010000.2 2008/08/18 08:32:08 skshin ship $*/
3 ------------------------------------------------------------------------------+
4
5 /* Bug 4066194
6 Package created from pay_au_tfn_magtape to avoid circular self references
7 which leads to package invalidation
8 */
9
10 ------------------------------------------------------------------------------+
11 -- This procedure populates the plsql table with values of the
12 -- assignment id and tax detail flags that need to be reported on the
13 -- magtape. The table is used in the magtape cursor 'c_tfn_payee' to get the
14 -- values of all reportable fields and print on the matape.
15 ------------------------------------------------------------------------------+
16
17 PROCEDURE populate_tfn_flags(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
18 p_business_group_id in per_business_groups.business_group_id%TYPE,
19 p_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
20 p_report_end_date in date) IS
21
22 -- Record used store the Superannuation details of the employee
23
24 TYPE spr_flag_record IS RECORD
25 (
26 k_assignment_id per_all_assignments_f.assignment_id%TYPE,
27 tfn_for_super pay_payautax_spr_ent_v.tfn_for_super_flag%TYPE
28 );
29
30 TYPE spr_flag_table IS TABLE OF spr_flag_record INDEX BY BINARY_INTEGER;
31
32 l_spr_flag_table spr_flag_table;
33
34
35 ----------------------------------------------------------------------------------
36 -- Cursor to select the tax details feild values for the current reporting period.
37 -- It selects
38 -- 1. All the employees who has the value of last_update_date segment of
39 -- 'Tax Information' element entry lying in the current reporting period range
40 -- OR
41 -- 2. Employee who are terminated in the current reporting period.
42 -- AND
43 -- 3. Employee who are not already reported with in the report period range.
44 ----------------------------------------------------------------------------------
45
46 /* Bug 2728358 - Added check for employee terminated on report end date */
47
48 /*Bug2920725 Corrected base tables to support security model*/
49 /* Bug 4514282 - Removed the view pay_au_tfn_tax_info_v*/
50
51 /* bUG 4925794 - Modified the cursor added per_people_f and its relative joins */
52 /* Bug#5864230 moved join pee.entry_information_category = 'AU_TAX DEDUCTIONS' inside expression */
53
54 CURSOR c_get_tfn_flag_values(c_business_group_id in per_business_groups.business_group_id%TYPE,
55 c_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
56 c_report_end_date in date)
57 IS
58 SELECT /*+ ORDERED */ pee.assignment_id
59 ,decode(eev0.screen_entry_value,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null)
60 , eev1.SCREEN_ENTRY_VALUE
61 , DECODE( eev3.screen_entry_value,
62 'N', 'N',
63 'Y', 'Y',
64 'NF','N',
65 'NP','N',
66 'NC','N',
67 'YF','Y',
68 'YP','Y',
69 'YC','Y',
70 'N'
71 )
72 , DECODE( eev3.screen_entry_value,
73 'Y', 'X',
74 'N', 'X',
75 'NF','F',
76 'NP','P',
77 'NC','C',
78 'YF','F',
79 'YP','P',
80 'YC','C',
81 'X')
82 , decode(eev5.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N',Null) hecs_flag
83 , decode(eev5.SCREEN_ENTRY_VALUE,'YY','Y','NY','Y','N') SFSS_ENTRY_VALUE
84 , to_char(fnd_date.canonical_to_date(eev6.SCREEN_ENTRY_VALUE),'ddmmyyyy')
85 , decode(decode(eev8.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(eev4.SCREEN_ENTRY_VALUE,'Y','Y','N'))
86 , eev13.SCREEN_ENTRY_VALUE
87 , pee.effective_start_date
88 , decode(sign(nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) - c_report_end_date),
89 1,null,pps.actual_termination_date) actual_termination_date
90 , decode(eev0.screen_entry_value,'YS','Y','YI','Y','YC','Y','NN','N','YN','N','Y','N','N','N',Null) /*bug7270073*/
91 FROM per_people_f pap /* Bug 4925794 */
92 , per_all_assignments_f paa,/*Bug 3012794*/
93 hr_soft_coding_keyflex hsc,
94 per_periods_of_service pps
95 , pay_element_entries_f pee
96 , pay_element_types_f pet
97 , pay_input_values_f piv0
98 , pay_element_entry_values_f eev0
99 , pay_input_values_f piv1
100 , pay_element_entry_values_f eev1
101 , pay_input_values_f piv3
102 , pay_element_entry_values_f eev3
103 , pay_input_values_f piv4
104 , pay_element_entry_values_f eev4
105 , pay_input_values_f piv5
106 , pay_element_entry_values_f eev5
107 , pay_input_values_f piv6
108 , pay_element_entry_values_f eev6
109 , pay_input_values_f piv8
110 , pay_element_entry_values_f eev8
111 , pay_input_values_f piv13
112 , pay_element_entry_values_f eev13
113 , hr_lookups hrl0
114 , hr_lookups hrl1
115 , hr_lookups hrl3
116 , hr_lookups hrl4
117 , hr_lookups hrl5
118 , hr_lookups hrl8
119 WHERE pap.business_group_id=c_business_group_id
120 and paa.business_group_id = pap.business_group_id
121 and pap.person_id=paa.person_id
122 and pps.person_id=paa.person_id
123 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
124 AND hsc.segment1 = c_legal_employer_id
125 AND pee.assignment_id = paa.assignment_id
126 AND pps.person_id = paa.person_id
127 AND pps.date_start= (select max(pps1.date_start)
128 from per_periods_of_service pps1
129 where pps1.person_id=pps.person_id
130 AND pps1.date_start <= c_report_end_date
131 ) /*Bug2751008*/
132 AND ( pee.entry_information_category = 'AU_TAX DEDUCTIONS' and
133 (trunc(fnd_date.canonical_to_date(pee.entry_information1)) BETWEEN c_report_end_date - 13 AND c_report_end_date
134 OR nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) BETWEEN c_report_end_date - 13 AND c_report_end_date
135 )
136 ) /* Bug#5864230 */
137 AND paa.effective_start_date = (SELECT max(effective_start_date)
138 FROM per_assignments_f a
139 WHERE a.assignment_id = paa.assignment_id)
140 and pap.effective_start_date=(select max(effective_start_date)
141 from per_people_f p
142 where p.person_id=pap.person_id) --Bug 4925794
143 AND pee.effective_start_date =
144 (SELECT max(pee1.effective_start_date)
145 FROM pay_element_types_f pet1
146 ,pay_element_links_f pel1
147 ,pay_element_entries_f pee1
148 WHERE pet1.element_name = 'Tax Information'
149 AND pet1.element_type_id = pel1.element_type_id
150 AND pel1.element_link_id = pee1.element_link_id
151 AND pee1.assignment_id = paa.assignment_id
152 AND pee1.entry_information1 is not null /*Bug 5356467*/
153 AND pee1.effective_start_date <= c_report_end_date
154 AND pel1.effective_start_date BETWEEN pet1.effective_start_date
155 AND pet1.effective_end_date
156 )
157 and pet.ELEMENT_NAME= 'Tax Information'
158 and pet.ELEMENT_TYPE_ID = piv0.ELEMENT_TYPE_ID
159 and eev0.INPUT_VALUE_ID = piv0.INPUT_VALUE_ID
160 and eev0.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
161 and (piv0.NAME) = 'Australian Resident'
162 and hrl0.lookup_type (+) = 'AU_AUST_RES_SENR_AUS'
163 and hrl0.lookup_code (+) = eev0.SCREEN_ENTRY_VALUE
164 and hrl0.enabled_flag (+)= 'Y'
165 and eev1.INPUT_VALUE_ID = piv1.INPUT_VALUE_ID
166 and eev1.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
167 and piv1.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
168 and (piv1.NAME) = 'Tax Free Threshold'
169 and hrl1.lookup_type (+) = 'YES_NO'
170 and hrl1.lookup_code (+) = eev1.SCREEN_ENTRY_VALUE
171 and hrl1.enabled_flag (+)= 'Y'
172 and eev3.INPUT_VALUE_ID = piv3.INPUT_VALUE_ID
173 and piv3.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
174 and eev3.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
175 and (piv3.NAME) = 'FTA Claim'
176 and hrl3.lookup_type (+) = 'HR_AU_FTA_PAYMENT_BASIS'
177 and hrl3.lookup_code (+) = eev3.SCREEN_ENTRY_VALUE
178 and hrl3.enabled_flag (+) = 'Y'
179 and eev4.INPUT_VALUE_ID = piv4.INPUT_VALUE_ID
180 and piv4.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
181 and eev4.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
182 and (piv4.NAME) = 'Savings Rebate'
183 and hrl4.lookup_type(+) = 'YES_NO'
184 and hrl4.lookup_code(+) = eev4.SCREEN_ENTRY_VALUE
185 and hrl4.enabled_flag (+) = 'Y'
186 and eev5.INPUT_VALUE_ID = piv5.INPUT_VALUE_ID
187 and piv5.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
188 and eev5.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
189 and (piv5.NAME) = 'HECS'
190 and hrl5.lookup_type(+) = 'AU_HECS_SFSS'
191 and hrl5.lookup_code (+) = eev5.SCREEN_ENTRY_VALUE
192 and hrl5.enabled_flag (+) = 'Y'
193 and eev6.INPUT_VALUE_ID = piv6.INPUT_VALUE_ID
194 and piv6.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
195 and eev6.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
196 and (piv6.NAME) = 'Date Declaration Signed'
197 and eev8.INPUT_VALUE_ID = piv8.INPUT_VALUE_ID
198 and piv8.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
199 and eev8.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
200 and (piv8.NAME) = 'Spouse'
201 and hrl8.lookup_type (+) = 'AU_SPOUSE_MLS'
202 and hrl8.lookup_code (+) = eev8.SCREEN_ENTRY_VALUE
203 and hrl8.enabled_flag (+) = 'Y'
204 and eev13.INPUT_VALUE_ID = piv13.INPUT_VALUE_ID
205 and piv13.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
206 and eev13.ELEMENT_ENTRY_ID= pee.ELEMENT_ENTRY_ID
207 and (piv13.NAME ) = 'Tax File Number'
208 and pee.effective_start_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
209 and eev0.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
210 and eev1.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
211 and eev3.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
212 and eev4.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
213 and eev5.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
214 and eev6.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
215 and eev8.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
216 and eev13.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
217 and eev0.effective_start_date between piv0.EFFECTIVE_START_DATE and piv0.EFFECTIVE_END_DATE
218 and eev1.effective_start_date between piv1.EFFECTIVE_START_DATE and piv1.EFFECTIVE_END_DATE
219 and eev3.effective_start_date between piv3.EFFECTIVE_START_DATE and piv3.EFFECTIVE_END_DATE
220 and eev4.effective_start_date between piv4.EFFECTIVE_START_DATE and piv4.EFFECTIVE_END_DATE
221 and eev5.effective_start_date between piv5.EFFECTIVE_START_DATE and piv5.EFFECTIVE_END_DATE
222 and eev6.effective_start_date between piv6.EFFECTIVE_START_DATE and piv6.EFFECTIVE_END_DATE
223 and eev8.effective_start_date between piv8.EFFECTIVE_START_DATE and piv8.EFFECTIVE_END_DATE
224 and eev13.effective_start_date between piv13.EFFECTIVE_START_DATE and piv13.EFFECTIVE_END_DATE
225 ;
226
227
228
229
230 ----------------------------------------------------------------------------------
231 -- Cursor to select the tax details(tfn for superannuation flag) field's value for
232 -- all the employees for whom the element entry for 'Superannuation Gurantee information'
233 -- element exists.
234 ----------------------------------------------------------------------------------
235
236 /*Bug2920725 Corrected base tables to support security model*/
237
238 CURSOR c_get_tfn_super_flag_value(c_business_group_id in per_business_groups.business_group_id%TYPE,
239 c_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
240 c_report_end_date in date) IS
241 SELECT pee.assignment_id
242 ,pev.screen_entry_value tfn_for_super_flag
243 FROM per_assignments_f paa,
244 hr_soft_coding_keyflex hsc,
245 pay_element_entry_values_f pev,
246 pay_input_values_f piv,
247 pay_element_types_f pet,
248 pay_element_entries_f pee,
249 hr_lookups hrl0
250 WHERE pet.element_name = 'Superannuation Guarantee Information'
251 AND pet.element_type_id = piv.element_type_id
252 AND pev.input_value_id = piv.input_value_id
253 AND pev.element_entry_id = pee.element_entry_id
254 AND piv.name = 'TFN for Superannuation'
255 AND paa.assignment_id = pee.assignment_id
256 AND paa.business_group_id = c_business_group_id
257 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
258 AND hsc.segment1 = c_legal_employer_id
259 AND hrl0.lookup_type (+) = 'YES_NO'
260 AND hrl0.lookup_code (+) = pev.screen_entry_value
261 AND hrl0.enabled_flag (+) = 'Y'
262 AND pee.effective_start_date = (SELECT max(pee1.effective_start_date)
263 FROM pay_element_entries_f pee1
264 WHERE pee1.element_entry_id = pee.element_entry_id
265 AND pee1.effective_start_date <= c_report_end_date
266 )
267 AND paa.effective_start_date = (SELECT max(effective_start_date)
268 FROM per_assignments_f a
269 WHERE a.assignment_id = paa.assignment_id
270 )
271 AND pev.effective_start_date = (SELECT max(pev1.effective_start_date)
272 FROM pay_element_entry_values_f pev1
273 WHERE pev1.element_entry_value_id = pev.element_entry_value_id
274 AND pev1.effective_start_date <= c_report_end_date
275 )
276 AND c_report_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
277 AND c_report_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
278 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
279 l_australian_res_flag pay_au_tfn_tax_info_v.australian_resident_flag%TYPE;
280 l_tax_free_threshold_flag pay_au_tfn_tax_info_v.tax_free_threshold_flag%TYPE;
281 l_fta_claim_flag pay_au_tfn_tax_info_v.fta_claim_flag%TYPE;
282 l_basis_of_payment pay_au_tfn_tax_info_v.basis_of_payment%TYPE;
283 l_hecs_flag pay_au_tfn_tax_info_v.hecs_flag%TYPE;
284 l_sfss_flag pay_au_tfn_tax_info_v.sfss_flag%TYPE;
285 l_declaration_signed_date pay_au_tfn_tax_info_v.declaration_signed_date%TYPE;
286 l_rebate_flag pay_au_tfn_tax_info_v.rebate_flag%TYPE;
287 l_tax_file_number pay_au_tfn_tax_info_v.tax_file_number%TYPE;
288 l_effective_start_date pay_au_tfn_tax_info_v.effective_start_date%TYPE;
292 l_senior_flag pay_au_tfn_tax_info_v.australian_resident_flag%TYPE; /*bug7270073*/
289 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
290 l_current_or_terminated varchar2(1);
291 l_tfn_for_super pay_payautax_spr_ent_v.tfn_for_super_flag%TYPE;
293
294 BEGIN
295
296 hr_utility.trace('Start of populate_tfn_flags procedure');
297
298 hr_utility.trace('l_business_group_id : ' || to_char(p_business_group_id));
299 hr_utility.trace('l_legal_employer_id : ' || p_legal_employer_id);
300 hr_utility.trace('l_report_end_date : ' || to_char(p_report_end_date, 'DD-MON-YYYY') );
301
302
303
304 -- Get the values of Tax Information reportable fields and
305 -- pupolate values into global table.
306
307 hr_utility.trace('Opening cursor c_get_tfn_flag_values');
308 OPEN c_get_tfn_flag_values(p_business_group_id,
309 p_legal_employer_id,
310 p_report_end_date );
311 LOOP
312 FETCH c_get_tfn_flag_values INTO
313 l_assignment_id ,
314 l_australian_res_flag ,
315 l_tax_free_threshold_flag ,
316 l_fta_claim_flag ,
317 l_basis_of_payment ,
318 l_hecs_flag ,
319 l_sfss_flag ,
320 l_declaration_signed_date ,
321 l_rebate_flag ,
322 l_tax_file_number ,
323 l_effective_start_date ,
324 l_actual_termination_date ,
325 l_senior_flag ; /*bug7270073*/
326 EXIT WHEN c_get_tfn_flag_values%NOTFOUND;
327
328 -- Employee with tax file number '111 111 111' who is not terminated
329 -- in the current report is not eligible to be printed on magtape
330
331 IF (l_tax_file_number = '111 111 111' AND nvl(l_actual_termination_date,p_report_end_date + 1)
332 NOT BETWEEN (p_report_end_date - 13) AND p_report_end_date) THEN
333 hr_utility.trace('Employee is not eligible for magtape');
334
335 ELSE
336 hr_utility.trace('Value stored for assignment id : ' || to_char(l_assignment_id));
337
338 -- populate the global table
339 g_tfn_flags_table(l_assignment_id).k_assignment_id := l_assignment_id ;
340 g_tfn_flags_table(l_assignment_id).australian_res_flag := l_australian_res_flag ;
341 g_tfn_flags_table(l_assignment_id).tax_free_threshold_flag := l_tax_free_threshold_flag;
342 g_tfn_flags_table(l_assignment_id).fta_claim_flag := l_fta_claim_flag ;
343 g_tfn_flags_table(l_assignment_id).basis_of_payment := l_basis_of_payment ;
344 g_tfn_flags_table(l_assignment_id).hecs_flag := l_hecs_flag ;
345 g_tfn_flags_table(l_assignment_id).sfss_flag := l_sfss_flag ;
346 g_tfn_flags_table(l_assignment_id).declaration_signed_date := l_declaration_signed_date;
347 g_tfn_flags_table(l_assignment_id).rebate_flag := l_rebate_flag ;
348 g_tfn_flags_table(l_assignment_id).tax_file_number := l_tax_file_number ;
349 g_tfn_flags_table(l_assignment_id).effective_start_date := l_effective_start_date ;
350 g_tfn_flags_table(l_assignment_id).senior_flag := l_senior_flag ; /*bug7270073*/
351
352 -- Store 'T' as 'current_or_terminated' if the employee is terminated in the current period
353 -- Other wise store 'C'
354
355 IF nvl(l_actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) BETWEEN (p_report_end_date - 13)
356 AND p_report_end_date THEN
357 g_tfn_flags_table(l_assignment_id).current_or_terminated := 'T';
358 ELSE
359 g_tfn_flags_table(l_assignment_id).current_or_terminated := 'C';
360 END IF;
361
362
363 END IF;
364 END LOOP;
365 hr_utility.trace('closing cursor c_get_tfn_flag_values');
366 CLOSE c_get_tfn_flag_values;
367
368
369 -- Get the value of tfn for super flag for all
370 -- employee and populate the values to a temprorary table
371
372 hr_utility.trace('Opening cursor c_get_tfn_super_flag_value');
373 OPEN c_get_tfn_super_flag_value(p_business_group_id,
374 p_legal_employer_id,
375 p_report_end_date );
376 LOOP
377 FETCH c_get_tfn_super_flag_value INTO
378 l_assignment_id,
379 l_tfn_for_super;
380 EXIT WHEN c_get_tfn_super_flag_value%NOTFOUND;
381 l_spr_flag_table(l_assignment_id).k_assignment_id := l_assignment_id;
382 l_spr_flag_table(l_assignment_id).tfn_for_super := l_tfn_for_super;
383 END LOOP;
384 hr_utility.trace('closing cursor c_get_tfn_super_flag_value');
385 CLOSE c_get_tfn_super_flag_value;
386
387
388
389 -- For all the eligible assignments populated in the global plsql table
390 -- get the value of 'TFN for Super' flag from the temporary table.
391 -- Populate the table with the proper value of the 'TFN for Super' flag.
392
396 for l_assignment_id in g_tfn_flags_table.first..g_tfn_flags_table.last
393 hr_utility.trace('Populating the supernnuation flag in the global table');
394
395 IF g_tfn_flags_table.count > 0 THEN
397 LOOP
398 IF g_tfn_flags_table.exists(l_assignment_id) THEN
399 IF l_spr_flag_table.exists(l_assignment_id) THEN
400 g_tfn_flags_table(l_assignment_id).tfn_for_super := l_spr_flag_table(l_assignment_id).tfn_for_super;
401 ELSE
402 g_tfn_flags_table(l_assignment_id).tfn_for_super := 'N';
403 END IF;
404 END IF;
405 END LOOP;
406 END IF;
407
408
409 hr_utility.trace('End of populate_tfn_flags procedure');
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 hr_utility.trace('Error in populate_tfn_flags');
414 RAISE;
415
416 END populate_tfn_flags;
417
418
419
420
421 ------------------------------------------------------------------------------+
422 -- This funciton returns the value of the tax detail fields depending on
423 -- the input provided.
424 -- It uses the plsql table, searches the assignment_id and returns the value
425 -- of the flag that is passed as the parameter.
426 ------------------------------------------------------------------------------+
427
428 FUNCTION get_tfn_flag_values(p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
429 p_flag_name in varchar2) RETURN varchar2 IS
430 BEGIN
431
432 hr_utility.trace('Start of get_tfn_flag_values function');
433 hr_utility.trace('Passed p_assignment_id : ' || to_char(p_assignment_id));
434
435 -- Check IF assignment exists in the plsql table of tax details values.
436 IF g_tfn_flags_table.exists(p_assignment_id) THEN
437
438 -- Return value of the tax detail field for the employee
439 hr_utility.trace('The value of the assignment_id passed : ' || to_char(g_tfn_flags_table(p_assignment_id).k_assignment_id));
440 -- Bug 2728374 : Corrected basis of payment string
441 IF p_flag_name = 'AUSTRALIAN_RESIDENT_FLAG' THEN
442 RETURN g_tfn_flags_table(p_assignment_id).australian_res_flag;
443
444 ELSIF p_flag_name = 'TAX_FREE_THRESHOLD_FLAG' THEN
445 RETURN g_tfn_flags_table(p_assignment_id).tax_free_threshold_flag;
446
447 ELSIF p_flag_name = 'FTA_CLAIM_FLAG' THEN
448 RETURN g_tfn_flags_table(p_assignment_id).fta_claim_flag;
449
450 ELSIF p_flag_name = 'BASIS_OF_PAYMENT' THEN
451 RETURN g_tfn_flags_table(p_assignment_id).basis_of_payment;
452
453 ELSIF p_flag_name = 'HECS_FLAG' THEN
454 RETURN g_tfn_flags_table(p_assignment_id).hecs_flag;
455
456 ELSIF p_flag_name = 'SFSS_FLAG' THEN
457 RETURN g_tfn_flags_table(p_assignment_id).sfss_flag;
458
459 ELSIF p_flag_name = 'DECLARATION_SIGNED_DATE' THEN
460 RETURN g_tfn_flags_table(p_assignment_id).declaration_signed_date;
461
462 ELSIF p_flag_name = 'REBATE_FLAG' THEN
463 RETURN g_tfn_flags_table(p_assignment_id).rebate_flag;
464
465 ELSIF p_flag_name = 'TAX_FILE_NUMBER' THEN
466 RETURN g_tfn_flags_table(p_assignment_id).tax_file_number;
467
468 ELSIF p_flag_name = 'EFFECTIVE_START_DATE' THEN
469 RETURN to_char(g_tfn_flags_table(p_assignment_id).effective_start_date,'DD-MON-YYYY');
470
471 ELSIF p_flag_name = 'CURRENT_OR_TERMINATED' THEN
472 RETURN g_tfn_flags_table(p_assignment_id).current_or_terminated;
473
474 ELSIF p_flag_name = 'TFN_FOR_SUPER' THEN
475 RETURN g_tfn_flags_table(p_assignment_id).tfn_for_super;
476
477 ELSIF p_flag_name = 'SENIOR_FLAG' THEN /*bug7270073*/
478 RETURN g_tfn_flags_table(p_assignment_id).senior_flag;
479 ELSE
480 RETURN 'N';
481 END IF;
482
483 ELSE
484 RETURN 'N';
485 END IF;
486
487 hr_utility.trace('End of get_tfn_flag_values function');
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 hr_utility.trace('Error in get_tfn_flag_values');
492 RAISE;
493 END get_tfn_flag_values;
494
495 END pay_au_tfn_magtape_flags;
[Home] [Help]