1 PACKAGE BODY HR_GB_UTILITY AS
2 /* $Header: hrgbutil.pkb 120.4 2010/07/19 11:45:38 npannamp noship $ */
3 -----------------------------------------------------------------------
4 function push_pay_message(p_applid in number,
5 p_msg_name in varchar2,
6 p_level in varchar2) return number is
7 --
8 l_number number;
9 --
10 begin
11 begin
12 l_number := 0;
13 pay_core_utils.push_message(p_applid => p_applid,
14 p_msg_name => p_msg_name,
15 p_level => p_level);
16 --
17 exception when others then
18 l_number := 1;
19 end;
20 return l_number;
21 end push_pay_message;
22 ------------------------------------------------------------------------
23 function push_pay_token(p_tok_name in varchar2,
24 p_tok_value in varchar2) return number is
25 --
26 l_number number;
27 --
28 begin
29 begin
30 l_number := 0;
31 pay_core_utils.push_token(p_tok_name => p_tok_name,
32 p_tok_value => p_tok_value);
33 --
34 exception when others then
35 l_number := 1;
36 end;
37 return l_number;
38 end push_pay_token;
39 -----------------------------------------------------------------------
40 function place_message_stack(p_pactid in number) return number is
41 --
45 begin
42 -- Commented out code as this should not be used.
43 l_number number;
44 --
46 begin
47 l_number := 0;
48 -- pay_core_utils.mesg_stack_error_hdlr(p_pactid => p_pactid);
49 --
50 exception when others then
51 l_number := 1;
52 end;
53 return l_number;
54 end place_message_stack;
55 ----------------------------------------------------------------------
56 function lookup_valid (p_lookup_type in varchar2,
57 p_lookup_code in varchar2,
58 p_effective_date in date) return boolean is
59 --
60 cursor csr_lookup_code(c_lookup_type in varchar2,
61 c_lookup_code in varchar2,
62 c_effective_date in date) is
63 select 1 from dual where exists
64 (select hlu.lookup_code
65 from hr_lookups hlu
66 where hlu.lookup_type = c_lookup_type
67 and hlu.lookup_code = c_lookup_code
68 and c_effective_date between
69 nvl(hlu.start_date_active, p_effective_date)
70 and nvl(hlu.end_date_active, p_effective_date)
71 and hlu.enabled_flag = 'Y');
72 --
73 l_valid boolean := TRUE;
74 l_exist_number number;
75 --
76 begin -- lookup_valid
77 BEGIN
78 --
79 open csr_lookup_code(p_lookup_type, p_lookup_code, p_effective_date);
80 fetch csr_lookup_code into l_exist_number;
81 --
82 if csr_lookup_code%NOTFOUND or l_exist_number <> 1 then
83 l_valid := FALSE;
84 end if;
85 close csr_lookup_code;
86 EXCEPTION WHEN OTHERS THEN
87 l_valid := FALSE;
88 END;
89 --
90 return l_valid;
91 --
92 end lookup_valid;
93 -----------------------------------------------------------------------
94 -- function: ni_validate
95 -- description: takes in a NI Number and the effective date,
96 -- then checks whether the format is valid, and whether
97 -- the prefix and suffix are within the allowable range
98 -- of the lookups.
99 -----------------------------------------------------------------------
100 function ni_validate(p_ni_number in varchar2,
101 p_effective_date in date) return number is
102 --
103 l_validate_ni_code varchar2(30);
104 l_return_code number;
105 l_exist_number number;
106 l_ni_number varchar2(30);
107 l_ni_prefix varchar2(2);
108 l_ni_suffix varchar2(1);
109
110 -- l_char_chk constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; Bug 6607067
111 l_char_chk constant varchar2(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ';
112 l_number_chk constant varchar2(10) := '0123456789';
113 l_translated_value varchar2(200);
114 l_invalid_char constant varchar2(1) := '~'; -- required for translate
115
116 --
117 BEGIN
118 --
119 -- Default the return code, and set NI number to upper, incase passed in lower
120 --
121 l_return_code := 0;
122
123 -- Added for bug fix : 5381242
124 l_translated_value :=
125 translate(p_ni_number,
126 l_invalid_char||l_char_chk||l_number_chk,
127 l_invalid_char);
128
129 if l_translated_value is not null then
130 hr_utility.trace('Lower case chars found: '||l_translated_value);
131 l_return_code := 5; -- Not valid
132 end if;
133
134 l_ni_number := upper(p_ni_number);
135 --
136 -- NULL is an allowable NI Number, if this is the case then return 0 (valid)
137 IF p_ni_number is null then
138 l_return_code := 0;
139 --
140 ELSE
141 -- Check that the NI Number is the correct format, AADDDDDDA,
142 -- where A=Alpha Character, D = Digit. Returns full NI number back if OK, or
143 -- 0 if not ok.
144 --
145 -- Bug Fix 6607067
146 -- l_validate_ni_code := hr_ni_chk_pkg.chk_nat_id_format(p_ni_number, 'AADDDDDDA');
147 if length(trim(p_ni_number))=8 then
148 l_validate_ni_code := hr_ni_chk_pkg.chk_nat_id_format(trim(p_ni_number), 'AADDDDDD');
149 else
150 l_validate_ni_code := hr_ni_chk_pkg.chk_nat_id_format(p_ni_number, 'AADDDDDDA');
151 end if;
152 --
153 if l_validate_ni_code = '0' then
154 -- The NI Number is not in the right format, send the apt code.
155 l_return_code := 1;
156 else
157 /* New code for NISPACE Bug 6607067*/
158 if length(trim(p_ni_number))=8 then
159 -- NI Number has SPACE suffix, check prefix alone
160 l_ni_prefix := substr(l_ni_number,1,2);
161 --
162 if not(lookup_valid('GB_NI_VALIDATION_PREFIX', l_ni_prefix, p_effective_date)) then
163 -- just prefix invalid
164 l_return_code := 2;
165 end if;
166 --
167 else
168 /* End of NISPACE Bug 6607067*/
169 -- NI Number in right format, check prefix and suffix
170 l_ni_prefix := substr(l_ni_number,1,2);
171 l_ni_suffix := substr(l_ni_number,9,1);
172 --
173 if not(lookup_valid('GB_NI_VALIDATION_PREFIX', l_ni_prefix, p_effective_date)) then
174 -- Prefix is invalid, is suffix also
175 if not(lookup_valid('GB_NI_VALIDATION_SUFFIX', l_ni_suffix, p_effective_date)) then
176 -- both invalid
177 l_return_code := 4;
178 else
179 -- just prefix invalid
180 l_return_code := 2;
181 end if;
182 else
186 end if;
183 if not(lookup_valid('GB_NI_VALIDATION_SUFFIX', l_ni_suffix, p_effective_date)) then
184 -- just suffix invalid
185 l_return_code := 3;
187 end if;
188 --
189 end if; -- bug 6607067 Valid NI number
190 end if; -- validation code
191 END IF; -- null NI Number
192 --
193 RETURN l_return_code;
194 --
195 END ni_validate;
196 --------------------------------------------------------------------
197 -- NAME tax_code_validate --
198 -- --
199 --DESCRIPTION --
200 -- This Function uses the TAX_CODE validation fast formula and --
201 -- returns an error message if an incorrect tax code is entered. --
202 -- Copied from pyudet and externalised so can be called from --
203 -- external plsql code. --
204 --------------------------------------------------------------------
205 --
206 function tax_code_validate (p_tax_code in varchar2,
207 p_effective_date in date,
208 p_assignment_id in number)
209 return VARCHAR2 IS
210 --
211 l_formula_id ff_formulas_f.formula_id%type;
212 l_effective_start_date ff_formulas_f.effective_start_date%type;
213 l_inputs ff_exec.inputs_t;
214 l_outputs ff_exec.outputs_t;
215 l_return_value varchar2(50):= null;
216 l_formula_mesg varchar2(50):= null;
217 l_status_value varchar2(2):= null;
218 l_proc varchar2(72) := 'hr_gb_utility.tax_code_validate';
219 --
220 begin
221 --
222 hr_utility.set_location('Entering:'|| l_proc, 5);
223 --
224 BEGIN
225 select formula_id,effective_start_date
226 into l_formula_id, l_effective_start_date
227 from ff_formulas_f
228 where formula_name='TAX_CODE'
229 and business_group_id is null
230 and legislation_code='GB'
231 and p_effective_date between effective_start_date and effective_end_date;
232 EXCEPTION WHEN NO_DATA_FOUND THEN
233 hr_utility.trace('Error: TAX_CODE formula not defined');
234 l_return_value := 'TAX_CODE formula not found';
235 END;
236 --
237 hr_utility.set_location(l_proc, 10);
238 --
239 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
240 --
241 for l_in_cnt in
242 l_inputs.first..l_inputs.last
243 loop
244 if l_inputs(l_in_cnt).name = 'ENTRY_VALUE' then
245 l_inputs(l_in_cnt).value := ltrim(p_tax_code);
246 end if;
247 if l_inputs(l_in_cnt).name = 'DATE_EARNED' then
248 -- Bug 6888656 modified to_char to fnd_date.date_to_canonical
249 -- l_inputs(l_in_cnt).value := to_char(p_effective_date,'DD-MON-YYYY');
250 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_effective_date);
251 end if;
252 if l_inputs(l_in_cnt).name = 'ASSIGNMENT_ID' then
253 l_inputs(l_in_cnt).value := to_char(p_assignment_id);
254 end if;
255 end loop;
256 --
257 hr_utility.set_location(l_proc, 15);
258 --
259 ff_exec.run_formula(l_inputs,l_outputs);
260 --
261 for l_out_cnt in
262 l_outputs.first..l_outputs.last
263 loop
264 if l_outputs(l_out_cnt).name='FORMULA_MESSAGE' then
265 l_formula_mesg := l_outputs(l_out_cnt).value;
266 end if;
267
268 if l_outputs(l_out_cnt).name='FORMULA_STATUS' then
269 l_status_value := l_outputs(l_out_cnt).value;
270 end if;
271 end loop;
272 --
273 hr_utility.set_location('Leaving:'|| l_proc, 20);
274 --
275 if l_status_value = 'E' and
276 l_formula_mesg is null then
277 l_return_value := 'TAX_CODE Formula error';
278 else
279 l_return_value := l_formula_mesg;
280 end if;
281 return l_return_value;
282 exception
283 when NO_DATA_FOUND then
284 hr_utility.set_location('Leaving:'|| l_proc, 22);
285 --
286 return l_return_value;
287 --
288 end tax_code_validate;
289 ---------------------------------------------------------------------------
290 procedure DERIVE_HR_LOC_ADDRESS
291 (p_tax_name in varchar2,
292 p_style in varchar2,
293 p_address_line_1 in varchar2,
294 p_address_line_2 in varchar2,
295 p_address_line_3 in varchar2,
296 p_town_or_city in varchar2,
297 p_country in varchar2,
298 p_postal_code in varchar2,
299 p_region_1 in varchar2,
300 p_region_2 in varchar2,
301 p_region_3 in varchar2,
302 p_telephone_number_1 in varchar2,
303 p_telephone_number_2 in varchar2,
304 p_telephone_number_3 in varchar2,
305 p_loc_information13 in varchar2,
306 p_loc_information14 in varchar2,
307 p_loc_information15 in varchar2,
308 p_loc_information16 in varchar2,
312 p_attribute2 in varchar2,
309 p_loc_information17 in varchar2,
310 p_attribute_category in varchar2,
311 p_attribute1 in varchar2,
313 p_attribute3 in varchar2,
314 p_attribute4 in varchar2,
315 p_attribute5 in varchar2,
316 p_attribute6 in varchar2,
317 p_attribute7 in varchar2,
318 p_attribute8 in varchar2,
319 p_attribute9 in varchar2,
320 p_attribute10 in varchar2,
321 p_attribute11 in varchar2,
322 p_attribute12 in varchar2,
323 p_attribute13 in varchar2,
324 p_attribute14 in varchar2,
325 p_attribute15 in varchar2,
326 p_attribute16 in varchar2,
327 p_attribute17 in varchar2,
328 p_attribute18 in varchar2,
329 p_attribute19 in varchar2,
330 p_attribute20 in varchar2,
331 p_global_attribute_category in varchar2,
332 p_global_attribute1 in varchar2,
333 p_global_attribute2 in varchar2,
334 p_global_attribute3 in varchar2,
335 p_global_attribute4 in varchar2,
336 p_global_attribute5 in varchar2,
337 p_global_attribute6 in varchar2,
338 p_global_attribute7 in varchar2,
339 p_global_attribute8 in varchar2,
340 p_global_attribute9 in varchar2,
341 p_global_attribute10 in varchar2,
342 p_global_attribute11 in varchar2,
343 p_global_attribute12 in varchar2,
344 p_global_attribute13 in varchar2,
345 p_global_attribute14 in varchar2,
346 p_global_attribute15 in varchar2,
347 p_global_attribute16 in varchar2,
348 p_global_attribute17 in varchar2,
349 p_global_attribute18 in varchar2,
350 p_global_attribute19 in varchar2,
351 p_global_attribute20 in varchar2,
352 p_loc_information18 in varchar2,
353 p_loc_information19 in varchar2,
354 p_loc_information20 in varchar2,
355 p_derived_locale out nocopy varchar2
356 ) is
357 begin
358 p_derived_locale := p_town_or_city || ', ' || p_country;
359 end;
360 --
361 procedure DERIVE_PER_ADD_ADDRESS
362 (p_style in varchar2,
363 p_address_line1 in varchar2,
364 p_address_line2 in varchar2,
365 p_address_line3 in varchar2,
366 p_country in varchar2,
367 p_date_to in date,
368 p_postal_code in varchar2,
369 p_region_1 in varchar2,
370 p_region_2 in varchar2,
371 p_region_3 in varchar2,
372 p_telephone_number_1 in varchar2,
373 p_telephone_number_2 in varchar2,
374 p_telephone_number_3 in varchar2,
375 p_town_or_city in varchar2,
376 p_addr_attribute_category in varchar2,
377 p_addr_attribute1 in varchar2,
378 p_addr_attribute2 in varchar2,
379 p_addr_attribute3 in varchar2,
380 p_addr_attribute4 in varchar2,
381 p_addr_attribute5 in varchar2,
382 p_addr_attribute6 in varchar2,
383 p_addr_attribute7 in varchar2,
384 p_addr_attribute8 in varchar2,
385 p_addr_attribute9 in varchar2,
386 p_addr_attribute10 in varchar2,
387 p_addr_attribute11 in varchar2,
388 p_addr_attribute12 in varchar2,
389 p_addr_attribute13 in varchar2,
390 p_addr_attribute14 in varchar2,
391 p_addr_attribute15 in varchar2,
392 p_addr_attribute16 in varchar2,
393 p_addr_attribute17 in varchar2,
394 p_addr_attribute18 in varchar2,
395 p_addr_attribute19 in varchar2,
396 p_addr_attribute20 in varchar2,
397 p_add_information13 in varchar2,
398 p_add_information14 in varchar2,
399 p_add_information15 in varchar2,
400 p_add_information16 in varchar2,
401 p_add_information17 in varchar2,
402 p_add_information18 in varchar2,
403 p_add_information19 in varchar2,
404 p_add_information20 in varchar2,
405 p_derived_locale out nocopy varchar2
406 ) is
407 begin
408 p_derived_locale := p_town_or_city || ', ' ||
409 p_region_2 || ', ' ||
410 p_country;
411
412 end;
413 --
414 FUNCTION per_gb_full_name(
415
416 p_first_name in varchar2
417 ,p_middle_names in varchar2
418 ,p_last_name in varchar2
419 ,p_known_as in varchar2
420 ,p_title in varchar2
421 ,p_suffix in varchar2
422 ,p_pre_name_adjunct in varchar2
423 ,p_per_information1 in varchar2
424 ,p_per_information2 in varchar2
425 ,p_per_information3 in varchar2
426 ,p_per_information4 in varchar2
427 ,p_per_information5 in varchar2
428 ,p_per_information6 in varchar2
429 ,p_per_information7 in varchar2
430 ,p_per_information8 in varchar2
431 ,p_per_information9 in varchar2
432 ,p_per_information10 in varchar2
433 ,p_per_information11 in varchar2
434 ,p_per_information12 in varchar2
435 ,p_per_information13 in varchar2
436 ,p_per_information14 in varchar2
437 ,p_per_information15 in varchar2
438 ,p_per_information16 in varchar2
439 ,p_per_information17 in varchar2
440 ,p_per_information18 in varchar2
441 ,p_per_information19 in varchar2
442 ,p_per_information20 in varchar2
443 ,p_per_information21 in varchar2
444 ,p_per_information22 in varchar2
445 ,p_per_information23 in varchar2
446 ,p_per_information24 in varchar2
447 ,p_per_information25 in varchar2
448 ,p_per_information26 in varchar2
449 ,p_per_information27 in varchar2
450 ,p_per_information28 in varchar2
451 ,p_per_information29 in varchar2
452 ,p_per_information30 in VARCHAR2
453 )
454 RETURN VARCHAR2 IS
455 --
456 l_full_name per_all_people_f.full_name%TYPE;
457 --
458 l_title hr_lookups.meaning%TYPE;
459 l_part2 varchar2(240);
460 BEGIN
461 --
462 -- bug fix for 2504672
463 /*
464 l_full_name := substr(p_last_name||', '||p_title||' '||
465 p_first_name ||' '||p_middle_names,1,240);
466 */
467
468 -- bug fix for 2581959
469 if p_title IS NOT NULL and
470 fnd_profile.value('PER_USE_TITLE_IN_FULL_NAME') = 'Y' then
471 SELECT meaning
472 INTO l_title
473 FROM hr_lookups
474 WHERE lookup_type = 'TITLE'
475 AND p_title = lookup_code
476 AND application_id = 800;
477 end if;
478
479 /********************************************
480 * Bug 2581959 - *
481 *******************************************/
482
483 /*
484 select trim(
485 substr(
486 decode(l_title,'','',null,'',l_title || ' ') ||
487 decode(p_first_name,'','',null,'',p_first_name || ' ') ||
488 decode(p_middle_names,'','',null,'',p_middle_names) ,
489 1,240)
490 )
491 into l_part2 from dual;
492
493 if length(l_part2) > 1 then
494 select trim(substr(decode(p_last_name,'','',null,'',p_last_name || ', ') || l_part2,1,240))
495 into l_full_name from dual;
496 else
497 Select trim(substr(decode(p_last_name,'','',null,'',p_last_name),1,240))
498 into l_full_name from dual;
499 end if;
500 */
501
502 -- Note this is only a select because PL/SQL can't cope with DECODEs
503 --
504 SELECT rtrim(substrb(DECODE(p_pre_name_adjunct,'','',p_pre_name_adjunct||' ')||
505 p_last_name||','||DECODE(l_title,'','',
506 ' '||l_title)||DECODE(p_first_name,'','',
507 ' '||p_first_name)||DECODE(p_middle_names,'','',
508 ' '||p_middle_names)||
509 DECODE(p_suffix,'','',' '||p_suffix)||
510 DECODE(p_known_as,'','',
511 ' ('||p_known_as||')'),1,240))
512 INTO l_full_name
513 FROM sys.dual ;
514 --
515 --
516
517 return l_full_name;
518 --
519 END;
520 --
521 END HR_GB_UTILITY;
[Home] [Help]