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