4
1 PACKAGE BODY pay_au_tfn_magtape_flags AS
2 /* $Header: pyautfnf.pkb 120.11 2011/05/25 07:52:57 skshin ship $*/
3 ------------------------------------------------------------------------------+
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,
22 -- Record used store the Superannuation details of the employee
19 p_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
20 p_report_end_date in date) IS
21
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 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.period_of_service_id = pps.period_of_service_id
124 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
125 AND hsc.segment1 = c_legal_employer_id
126 AND pee.assignment_id = paa.assignment_id
127 AND pps.person_id = paa.person_id
128 AND pps.date_start= (select max(pps1.date_start)
129 from per_periods_of_service pps1
133 AND ( pee.entry_information_category = 'AU_TAX DEDUCTIONS' and
130 where pps1.person_id=pps.person_id
131 AND pps1.date_start <= c_report_end_date
132 ) /*Bug2751008*/
134 (trunc(fnd_date.canonical_to_date(pee.entry_information1)) BETWEEN c_report_end_date - 13 AND c_report_end_date
135 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
136 )
137 ) /* Bug#5864230 */
138 AND paa.effective_start_date = (SELECT max(effective_start_date)
139 FROM per_assignments_f a
140 WHERE a.assignment_id = paa.assignment_id)
141 and pap.effective_start_date=(select max(effective_start_date)
142 from per_people_f p
143 where p.person_id=pap.person_id) --Bug 4925794
144 AND pee.effective_start_date =
145 (SELECT max(pee1.effective_start_date)
146 FROM pay_element_types_f pet1
147 ,pay_element_links_f pel1
148 ,pay_element_entries_f pee1
149 WHERE pet1.element_name = 'Tax Information'
150 AND pet1.element_type_id = pel1.element_type_id
151 AND pel1.element_link_id = pee1.element_link_id
152 AND pee1.assignment_id = paa.assignment_id
153 AND pee1.entry_information1 is not null /*Bug 5356467*/
154 AND pee1.effective_start_date <= c_report_end_date
155 AND pel1.effective_start_date BETWEEN pet1.effective_start_date
156 AND pet1.effective_end_date
157 )
158 and pet.ELEMENT_NAME= 'Tax Information'
159 and pet.ELEMENT_TYPE_ID = piv0.ELEMENT_TYPE_ID
160 and eev0.INPUT_VALUE_ID = piv0.INPUT_VALUE_ID
161 and eev0.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
162 and (piv0.NAME) = 'Australian Resident'
163 and hrl0.lookup_type (+) = 'AU_AUST_RES_SENR_AUS'
164 and hrl0.lookup_code (+) = eev0.SCREEN_ENTRY_VALUE
165 and hrl0.enabled_flag (+)= 'Y'
166 and eev1.INPUT_VALUE_ID = piv1.INPUT_VALUE_ID
167 and eev1.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
168 and piv1.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
169 and (piv1.NAME) = 'Tax Free Threshold'
170 and hrl1.lookup_type (+) = 'YES_NO'
171 and hrl1.lookup_code (+) = eev1.SCREEN_ENTRY_VALUE
172 and hrl1.enabled_flag (+)= 'Y'
173 and eev3.INPUT_VALUE_ID = piv3.INPUT_VALUE_ID
174 and piv3.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
175 and eev3.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
176 and (piv3.NAME) = 'FTA Claim'
177 and hrl3.lookup_type (+) = 'HR_AU_FTA_PAYMENT_BASIS'
178 and hrl3.lookup_code (+) = eev3.SCREEN_ENTRY_VALUE
179 and hrl3.enabled_flag (+) = 'Y'
180 and eev4.INPUT_VALUE_ID = piv4.INPUT_VALUE_ID
181 and piv4.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
182 and eev4.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
183 and (piv4.NAME) = 'Savings Rebate'
184 and hrl4.lookup_type(+) = 'YES_NO'
185 and hrl4.lookup_code(+) = eev4.SCREEN_ENTRY_VALUE
186 and hrl4.enabled_flag (+) = 'Y'
187 and eev5.INPUT_VALUE_ID = piv5.INPUT_VALUE_ID
188 and piv5.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
189 and eev5.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
190 and (piv5.NAME) = 'HECS'
191 and hrl5.lookup_type(+) = 'AU_HECS_SFSS'
192 and hrl5.lookup_code (+) = eev5.SCREEN_ENTRY_VALUE
193 and hrl5.enabled_flag (+) = 'Y'
194 and eev6.INPUT_VALUE_ID = piv6.INPUT_VALUE_ID
195 and piv6.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
196 and eev6.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
197 and (piv6.NAME) = 'Date Declaration Signed'
198 and eev8.INPUT_VALUE_ID = piv8.INPUT_VALUE_ID
199 and piv8.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
200 and eev8.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
201 and (piv8.NAME) = 'Spouse'
202 and hrl8.lookup_type (+) = 'AU_SPOUSE_MLS'
203 and hrl8.lookup_code (+) = eev8.SCREEN_ENTRY_VALUE
204 and hrl8.enabled_flag (+) = 'Y'
205 and eev13.INPUT_VALUE_ID = piv13.INPUT_VALUE_ID
206 and piv13.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
207 and eev13.ELEMENT_ENTRY_ID= pee.ELEMENT_ENTRY_ID
208 and (piv13.NAME ) = 'Tax File Number'
209 and pee.effective_start_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
210 and eev0.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
211 and eev1.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
212 and eev3.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
213 and eev4.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
214 and eev5.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
215 and eev6.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
216 and eev8.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
217 and eev13.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
218 and eev0.effective_start_date between piv0.EFFECTIVE_START_DATE and piv0.EFFECTIVE_END_DATE
219 and eev1.effective_start_date between piv1.EFFECTIVE_START_DATE and piv1.EFFECTIVE_END_DATE
223 and eev6.effective_start_date between piv6.EFFECTIVE_START_DATE and piv6.EFFECTIVE_END_DATE
220 and eev3.effective_start_date between piv3.EFFECTIVE_START_DATE and piv3.EFFECTIVE_END_DATE
221 and eev4.effective_start_date between piv4.EFFECTIVE_START_DATE and piv4.EFFECTIVE_END_DATE
222 and eev5.effective_start_date between piv5.EFFECTIVE_START_DATE and piv5.EFFECTIVE_END_DATE
224 and eev8.effective_start_date between piv8.EFFECTIVE_START_DATE and piv8.EFFECTIVE_END_DATE
225 and eev13.effective_start_date between piv13.EFFECTIVE_START_DATE and piv13.EFFECTIVE_END_DATE
226 ;
227
228
229
230
231 ----------------------------------------------------------------------------------
232 -- Cursor to select the tax details(tfn for superannuation flag) field's value for
233 -- all the employees for whom the element entry for 'Superannuation Gurantee information'
234 -- element exists.
235 ----------------------------------------------------------------------------------
236
237 /*Bug2920725 Corrected base tables to support security model*/
238
239 CURSOR c_get_tfn_super_flag_value(c_business_group_id in per_business_groups.business_group_id%TYPE,
240 c_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
241 c_report_end_date in date,
242 c_assignment_id per_assignments_f.assignment_id%TYPE ) IS
243 SELECT pee.assignment_id
244 ,pev.screen_entry_value tfn_for_super_flag
245 FROM per_assignments_f paa,
246 hr_soft_coding_keyflex hsc,
247 pay_element_entry_values_f pev,
248 pay_input_values_f piv,
249 pay_element_types_f pet,
250 pay_element_entries_f pee,
251 hr_lookups hrl0
252 WHERE pet.element_name = 'Superannuation Guarantee Information'
253 AND pet.element_type_id = piv.element_type_id
254 AND pev.input_value_id = piv.input_value_id
255 AND pev.element_entry_id = pee.element_entry_id
256 AND piv.name = 'TFN for Superannuation'
257 AND pee.assignment_id = c_assignment_id /*bug8634876*/
258 AND paa.assignment_id = pee.assignment_id
259 AND paa.business_group_id = c_business_group_id
260 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
261 AND hsc.segment1 = c_legal_employer_id
262 AND hrl0.lookup_type (+) = 'YES_NO'
263 AND hrl0.lookup_code (+) = pev.screen_entry_value
264 AND hrl0.enabled_flag (+) = 'Y'
265 AND pee.effective_start_date = (SELECT max(pee1.effective_start_date)
266 FROM pay_element_entries_f pee1
267 WHERE pee1.element_entry_id = pee.element_entry_id
268 AND pee1.effective_start_date <= c_report_end_date
269 )
270 AND paa.effective_start_date = (SELECT max(effective_start_date)
271 FROM per_assignments_f a
272 WHERE a.assignment_id = paa.assignment_id
273 )
274 AND pev.effective_start_date = (SELECT max(pev1.effective_start_date)
275 FROM pay_element_entry_values_f pev1
276 WHERE pev1.element_entry_value_id = pev.element_entry_value_id
277 AND pev1.effective_start_date <= c_report_end_date
278 )
279 AND c_report_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
280 AND c_report_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
281
282 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
283 l_australian_res_flag pay_au_tfn_tax_info_v.australian_resident_flag%TYPE;
284 l_tax_free_threshold_flag pay_au_tfn_tax_info_v.tax_free_threshold_flag%TYPE;
285 l_fta_claim_flag pay_au_tfn_tax_info_v.fta_claim_flag%TYPE;
286 l_basis_of_payment pay_au_tfn_tax_info_v.basis_of_payment%TYPE;
287 l_hecs_flag pay_au_tfn_tax_info_v.hecs_flag%TYPE;
288 l_sfss_flag pay_au_tfn_tax_info_v.sfss_flag%TYPE;
289 l_declaration_signed_date pay_au_tfn_tax_info_v.declaration_signed_date%TYPE;
290 l_rebate_flag pay_au_tfn_tax_info_v.rebate_flag%TYPE;
291 l_tax_file_number pay_au_tfn_tax_info_v.tax_file_number%TYPE;
292 l_effective_start_date pay_au_tfn_tax_info_v.effective_start_date%TYPE;
293 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
294 l_current_or_terminated varchar2(1);
295 l_tfn_for_super pay_payautax_spr_ent_v.tfn_for_super_flag%TYPE;
296 l_senior_flag pay_au_tfn_tax_info_v.australian_resident_flag%TYPE; /*bug7270073*/
297 l_super_assignment_id per_all_assignments_f.assignment_id%TYPE;
298
299 BEGIN
300
301 hr_utility.trace('Start of populate_tfn_flags procedure');
302
303 hr_utility.trace('l_business_group_id : ' || to_char(p_business_group_id));
304 hr_utility.trace('l_legal_employer_id : ' || p_legal_employer_id);
305 hr_utility.trace('l_report_end_date : ' || to_char(p_report_end_date, 'DD-MON-YYYY') );
306
307
308
309 -- Get the values of Tax Information reportable fields and
310 -- pupolate values into global table.
311
312 hr_utility.trace('Opening cursor c_get_tfn_flag_values');
313 OPEN c_get_tfn_flag_values(p_business_group_id,
314 p_legal_employer_id,
315 p_report_end_date );
319 l_australian_res_flag ,
316 LOOP
317 FETCH c_get_tfn_flag_values INTO
318 l_assignment_id ,
320 l_tax_free_threshold_flag ,
321 l_fta_claim_flag ,
322 l_basis_of_payment ,
323 l_hecs_flag ,
324 l_sfss_flag ,
325 l_declaration_signed_date ,
326 l_rebate_flag ,
327 l_tax_file_number ,
328 l_effective_start_date ,
329 l_actual_termination_date ,
330 l_senior_flag ; /*bug7270073*/
331 EXIT WHEN c_get_tfn_flag_values%NOTFOUND;
332
333 -- Employee with tax file number '111 111 111' who is not terminated
334 -- in the current report is not eligible to be printed on magtape
335
336 IF (l_tax_file_number = '111 111 111' AND nvl(l_actual_termination_date,p_report_end_date + 1)
337 NOT BETWEEN (p_report_end_date - 13) AND p_report_end_date) THEN
338 hr_utility.trace('Employee is not eligible for magtape');
339
340 ELSE
341 hr_utility.trace('Value stored for assignment id : ' || to_char(l_assignment_id));
342
343 -- populate the global table
344 g_tfn_flags_table(l_assignment_id).k_assignment_id := l_assignment_id ;
345 g_tfn_flags_table(l_assignment_id).australian_res_flag := l_australian_res_flag ;
346 g_tfn_flags_table(l_assignment_id).tax_free_threshold_flag := l_tax_free_threshold_flag;
347 g_tfn_flags_table(l_assignment_id).fta_claim_flag := l_fta_claim_flag ;
348 g_tfn_flags_table(l_assignment_id).basis_of_payment := l_basis_of_payment ;
349 g_tfn_flags_table(l_assignment_id).hecs_flag := l_hecs_flag ;
350 g_tfn_flags_table(l_assignment_id).sfss_flag := l_sfss_flag ;
351 g_tfn_flags_table(l_assignment_id).declaration_signed_date := l_declaration_signed_date;
352 g_tfn_flags_table(l_assignment_id).rebate_flag := l_rebate_flag ;
353 g_tfn_flags_table(l_assignment_id).tax_file_number := l_tax_file_number ;
354 g_tfn_flags_table(l_assignment_id).effective_start_date := l_effective_start_date ;
355 g_tfn_flags_table(l_assignment_id).senior_flag := l_senior_flag ; /*bug7270073*/
356
357 -- Store 'T' as 'current_or_terminated' if the employee is terminated in the current period
358 -- Other wise store 'C'
359
360 IF nvl(l_actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) BETWEEN (p_report_end_date - 13)
361 AND p_report_end_date THEN
362 g_tfn_flags_table(l_assignment_id).current_or_terminated := 'T';
363 ELSE
364 g_tfn_flags_table(l_assignment_id).current_or_terminated := 'C';
365 END IF;
366
367
368 END IF;
369 END LOOP;
370 hr_utility.trace('closing cursor c_get_tfn_flag_values');
371 CLOSE c_get_tfn_flag_values;
372
373
374 /* bug8634876
375 Populate all the eligible assignments populated in the global plsql table
376 with the proper value of the 'TFN for Super' flag.
377 */
378
379 hr_utility.trace('Populating the supernnuation flag in the global table');
380
381 IF g_tfn_flags_table.count > 0 THEN
382 for l_assignment_id in g_tfn_flags_table.first..g_tfn_flags_table.last
383 LOOP
384 IF g_tfn_flags_table.exists(l_assignment_id) THEN
385 OPEN c_get_tfn_super_flag_value(p_business_group_id,
386 p_legal_employer_id,
387 p_report_end_date,
388 g_tfn_flags_table(l_assignment_id).k_assignment_id);
389
390 FETCH c_get_tfn_super_flag_value INTO
391 l_super_assignment_id,
392 l_tfn_for_super;
393
394 IF c_get_tfn_super_flag_value%FOUND THEN
395 g_tfn_flags_table(l_assignment_id).tfn_for_super :=l_tfn_for_super;
396 ELSE
397 g_tfn_flags_table(l_assignment_id).tfn_for_super := 'N';
398 END IF;
399
400 CLOSE c_get_tfn_super_flag_value;
401 END IF;
402 END LOOP;
403 END IF;
404
405 hr_utility.trace('End of populate_tfn_flags procedure');
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 hr_utility.trace('Error in populate_tfn_flags');
410 RAISE;
411
412 END populate_tfn_flags;
413
414
415
416
417 ------------------------------------------------------------------------------+
418 -- This funciton returns the value of the tax detail fields depending on
419 -- the input provided.
420 -- It uses the plsql table, searches the assignment_id and returns the value
421 -- of the flag that is passed as the parameter.
422 ------------------------------------------------------------------------------+
423
424 FUNCTION get_tfn_flag_values(p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
425 p_flag_name in varchar2) RETURN varchar2 IS
426 BEGIN
427
428 hr_utility.trace('Start of get_tfn_flag_values function');
429 hr_utility.trace('Passed p_assignment_id : ' || to_char(p_assignment_id));
430
431 -- Check IF assignment exists in the plsql table of tax details values.
432 IF g_tfn_flags_table.exists(p_assignment_id) THEN
433
434 -- Return value of the tax detail field for the employee
435 hr_utility.trace('The value of the assignment_id passed : ' || to_char(g_tfn_flags_table(p_assignment_id).k_assignment_id));
436 -- Bug 2728374 : Corrected basis of payment string
437 IF p_flag_name = 'AUSTRALIAN_RESIDENT_FLAG' THEN
438 RETURN g_tfn_flags_table(p_assignment_id).australian_res_flag;
439
440 ELSIF p_flag_name = 'TAX_FREE_THRESHOLD_FLAG' THEN
441 RETURN g_tfn_flags_table(p_assignment_id).tax_free_threshold_flag;
442
443 ELSIF p_flag_name = 'FTA_CLAIM_FLAG' THEN
444 RETURN g_tfn_flags_table(p_assignment_id).fta_claim_flag;
445
446 ELSIF p_flag_name = 'BASIS_OF_PAYMENT' THEN
447 RETURN g_tfn_flags_table(p_assignment_id).basis_of_payment;
448
449 ELSIF p_flag_name = 'HECS_FLAG' THEN
450 RETURN g_tfn_flags_table(p_assignment_id).hecs_flag;
451
452 ELSIF p_flag_name = 'SFSS_FLAG' THEN
453 RETURN g_tfn_flags_table(p_assignment_id).sfss_flag;
454
455 ELSIF p_flag_name = 'DECLARATION_SIGNED_DATE' THEN
456 RETURN g_tfn_flags_table(p_assignment_id).declaration_signed_date;
457
458 ELSIF p_flag_name = 'REBATE_FLAG' THEN
459 RETURN g_tfn_flags_table(p_assignment_id).rebate_flag;
460
461 ELSIF p_flag_name = 'TAX_FILE_NUMBER' THEN
462 RETURN g_tfn_flags_table(p_assignment_id).tax_file_number;
463
464 ELSIF p_flag_name = 'EFFECTIVE_START_DATE' THEN
465 RETURN to_char(g_tfn_flags_table(p_assignment_id).effective_start_date,'DD-MON-YYYY');
466
467 ELSIF p_flag_name = 'CURRENT_OR_TERMINATED' THEN
468 RETURN g_tfn_flags_table(p_assignment_id).current_or_terminated;
469
470 ELSIF p_flag_name = 'TFN_FOR_SUPER' THEN
471 RETURN g_tfn_flags_table(p_assignment_id).tfn_for_super;
472
473 ELSIF p_flag_name = 'SENIOR_FLAG' THEN /*bug7270073*/
474 RETURN g_tfn_flags_table(p_assignment_id).senior_flag;
475 ELSE
476 RETURN 'N';
477 END IF;
478
479 ELSE
480 RETURN 'N';
481 END IF;
482
483 hr_utility.trace('End of get_tfn_flag_values function');
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 hr_utility.trace('Error in get_tfn_flag_values');
488 RAISE;
489 END get_tfn_flag_values;
490
491 ------------------------------------------------------------------------------+
492 -- 9000052 - Used to remove more than one spaces between words in the string
493 -- This function ensures that every word in the string passed is
494 -- seperated by exactly 1 space.
495 -----------------------------------------------------------------------------+
496 /* Changes for bug 9000052 start */
497 FUNCTION remove_extra_spaces(p_str in varchar2) return varchar2 IS
498 l_already_found boolean;
499 l_return varchar2(1000);
500 BEGIN
501 l_return := null;
502 l_already_found := false;
503
504 if p_str is null or p_str = '' then
505 return l_return;
506 end if;
507
508 for i in 1..length(p_str) loop
509 if substr(p_str,i,1) = ' ' then
510 if NOT l_already_found then
511 l_return := l_return||' ';
512 l_already_found := true;
513 end if;
514 else
515 if l_already_found then
516 l_already_found := false;
517 end if;
518 l_return := l_return||substr(p_str,i,1);
519 end if;
520 end loop;
521
522 return l_return;
523
524 END;
525 /* Changes for bug 9000052 end */
526
527 END pay_au_tfn_magtape_flags;
[Home] [Help]