1 PACKAGE BODY hr_util_misc_web AS
2 /* $Header: hrutlmsw.pkb 120.2 2012/02/03 09:49:44 shpatro ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 g_package varchar2(31) := 'hr_util_misc_web.';
6 g_owa_package varchar2(2000) := hr_util_misc_web.g_owa||g_package;
7 --
8 g_image_directory varchar2(30) default null;
9 g_html_directory varchar2(30) default null;
10
11 -- ------------------------------------------------------------------------
12 -- get_nls_parameter
13 -- ------------------------------------------------------------------------
14 FUNCTION get_nls_parameter(p_parameter in varchar2)
15 RETURN VARCHAR2 IS
16
17 cursor csr_group_separator is
18 select value
19 from V$NLS_PARAMETERS
20 where parameter = p_parameter;
21
22 l_parameter V$NLS_PARAMETERS.value%type;
23 l_proc constant varchar2(100) := g_package || ' get_nls_parameter';
24
25
26 BEGIN
27 --
28 hr_utility.set_location('Entering: '|| l_proc,5);
29 open csr_group_separator;
30 fetch csr_group_separator into l_parameter;
31 close csr_group_separator;
32 hr_utility.set_location('Leaving: '|| l_proc,10);
33 RETURN (l_parameter);
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
38 RETURN null;
39
40 END get_nls_parameter;
41
42 -- ------------------------------------------------------------------------
43 -- get_currency_mask
44 -- ------------------------------------------------------------------------
45 FUNCTION get_currency_mask
46 RETURN VARCHAR2 IS
47 l_proc constant varchar2(100) := g_package || ' get_currency_mask';
48 BEGIN
49 hr_utility.set_location('Entering: '|| l_proc,5);
50 hr_utility.set_location('Leaving: '|| l_proc,10);
51 RETURN (substr(get_nls_parameter('NLS_CURRENCY'),1,1));
52
53 END get_currency_mask;
54
55 -- ------------------------------------------------------------------------
56 -- get_group_separator
57 -- ------------------------------------------------------------------------
58 FUNCTION get_group_separator
59 RETURN VARCHAR2 IS
60 l_proc constant varchar2(100) := g_package || ' get_group_separator';
61 BEGIN
62 hr_utility.set_location('Entering: '|| l_proc,5);
63 hr_utility.set_location('Leaving: '|| l_proc,10);
64 RETURN (substr(get_nls_parameter('NLS_NUMERIC_CHARACTERS'),2,1));
65
66 END get_group_separator;
67
68 -- ------------------------------------------------------------------------
69 -- is_valid_number
70 -- ------------------------------------------------------------------------
71 FUNCTION is_valid_number(p_number in varchar2)
72 RETURN BOOLEAN IS
73
74 l_group_separator V$NLS_PARAMETERS.value%type;
75 l_number number;
76 l_proc constant varchar2(100) := g_package || ' is_valid_number';
77 BEGIN
78 hr_utility.set_location('Entering: '|| l_proc,5);
79 l_group_separator := get_group_separator;
80
81 l_number := to_number
82 (replace(p_number,substr(l_group_separator,1,1)));
83 hr_utility.set_location('Leaving: '|| l_proc,10);
84 RETURN TRUE;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
89 RETURN FALSE;
90
91 END is_valid_number;
92
93 -- ------------------------------------------------------------------------
94 -- is_valid_currency
95 -- ------------------------------------------------------------------------
96 FUNCTION is_valid_currency(p_currency in varchar2)
97 RETURN BOOLEAN IS
98
99 l_group_separator V$NLS_PARAMETERS.value%type;
100 l_currency_mask V$NLS_PARAMETERS.value%type;
101 l_number number;
102 l_proc constant varchar2(100) := g_package || ' is_valid_currency';
103 BEGIN
104 hr_utility.set_location('Entering: '|| l_proc,5);
105 --
106 l_group_separator := get_group_separator;
107 l_currency_mask := get_currency_mask;
108
109 l_number := to_number
110 (replace(replace(p_currency
111 ,substr(l_group_separator,1,1))
112 ,substr(l_currency_mask,1,1)));
113 hr_utility.set_location('Leaving: '|| l_proc,10);
114 RETURN TRUE;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
119 RETURN FALSE;
120
121 END is_valid_currency;
122
123 -- ------------------------------------------------------------------------
124 -- ------------------------------------------------------------------------
125 -- get_language_code
126 -- ------------------------------------------------------------------------
127
128 FUNCTION get_language_code
129 RETURN varchar2 IS
130 l_proc constant varchar2(100) := g_package || ' get_language_code';
131 BEGIN
132 hr_utility.set_location('Entering: '|| l_proc,5);
133 hr_utility.set_location('Leaving: '|| l_proc,10);
134 RETURN(icx_sec.getID(icx_sec.PV_LANGUAGE_CODE));
135
136 END get_language_code;
137
138 -- ------------------------------------------------------------------------
139 -- get_image_directory
140 -- ------------------------------------------------------------------------
141
142 FUNCTION get_image_directory
143 RETURN varchar2 IS
144 l_proc constant varchar2(100) := g_package || ' get_image_directory';
145 BEGIN
146 hr_utility.set_location('Entering: '|| l_proc,5);
147 --
148 IF g_image_directory IS null THEN
149 g_image_directory := hr_util_misc_web.g_image_dir;
150 END IF;
151 --
152 hr_utility.set_location('Leaving: '|| l_proc,10);
153 RETURN g_image_directory;
154 --
155 END get_image_directory;
156
157 -- ------------------------------------------------------------------------
158 -- get_calendar_file
159 -- This function is required because the cabo_calendar.html file is located
160 -- in different directories in r11 and r115.
161 -- In r11 it is in: /OA_HTML/webtools/cabo_calendar.html
162 -- In r115 it is in: /OA_HTML/webtools/jslib/cabo_calendar.html
163 -- This is for r115 release.
164 -- ------------------------------------------------------------------------
165
166 FUNCTION get_calendar_file
167 RETURN VARCHAR2 IS
168 l_proc constant varchar2(100) := g_package || ' get_calendar_file';
169 BEGIN
170 hr_utility.set_location('Entering: '|| l_proc,5);
171 hr_utility.set_location('Leaving: '|| l_proc,10);
172 RETURN (hr_util_misc_web.g_html_dir || 'webtools/jslib/cabo_calendar.html');
173 END get_calendar_file;
174
175
176 -- ------------------------------------------------------------------------
177 -- get_html_directory
178 -- ------------------------------------------------------------------------
179
180 FUNCTION get_html_directory
181 RETURN varchar2 IS
182 l_proc constant varchar2(100) := g_package || ' get_html_directory';
183 BEGIN
184 hr_utility.set_location('Entering: '|| l_proc,5);
185 --
186 IF g_html_directory IS null THEN
187 g_html_directory := hr_util_misc_web.g_html_dir||get_language_code||'/';
188 END IF;
189 --
190 hr_utility.set_location('Leaving: '|| l_proc,10);
191 RETURN g_html_directory;
192 --
193 END get_html_directory;
194
195 -- ------------------------------------------------------------------------
196 -- get_person_rec
197 -- ------------------------------------------------------------------------
198
199 FUNCTION get_person_rec(p_effective_date in varchar2
200 ,p_person_id in number)
201 RETURN per_people_f%ROWTYPE IS
202
203 -----------------------------------------------------------------------------
204 -- Fix Bug 1615428:
205 -- In testing WebDB stateful mode, we found that in WF Notification
206 -- responsibility, the Business Group Id profile option is never set for the WF
207 -- Notification responsibility. This will cause no record to return because
208 -- the cursor is using the per_people_f view instead of the base table. Changed
209 -- the cursor to use per_all_people_f base table because if a person id is
210 -- passed to this function, the caller should have validation done first to
211 -- make sure if the person is within access or not.
212 -----------------------------------------------------------------------------
213 CURSOR csr_pp(p_person_id in per_people_f.person_id%type
214 ,p_legislation_code in varchar2) IS
215 SELECT pp.business_group_id
216 ,decode(p_legislation_code,'JP',pp.per_information18,pp.last_name)
217 last_name
218 ,decode(p_legislation_code,'JP',pp.per_information19,pp.first_name)
219 first_name
220 ,pp.person_type_id
221 ,pp.email_address
222 ,pp.title
223 ,pp.full_name
224 FROM per_all_people_f pp -- 02/09/2001 changed from per_people_f
225 WHERE pp.person_id = p_person_id;
226
227
228 -----------------------------------------------------------------------------
229 -- Fix Bug 1615428:
230 -- Intead of calling per_per_bus.return_legislation_code to derive the
231 -- legislation_code, we query legislation code here because per_per_bus api
232 -- uses per_people_f view instead of per_all_people_f base table. The view
233 -- will cause no rec found error when accessed from WF Notification
234 -- responsibility. In addition, per_per_bus.return_legislation_code function
235 -- uses two global variables in the package body: g_person_id and
236 -- g_legislation_code which may cause obscure error when running in WebDB
237 -- stateful mode.
238 -- The following cursor is copied from per_per_bus.return_legislation_code
239 -- with a change to the table per_all_people_f instead of per_people_f.
240 -----------------------------------------------------------------------------
241 cursor csr_leg_code is
242 select pbg.legislation_code
243 from per_business_groups pbg
244 , per_all_people_f per
245 where per.person_id = p_person_id
246 and pbg.business_group_id = per.business_group_id
247 and p_effective_date between per.effective_start_date
248 and per.effective_end_date
249 order by per.effective_start_date;
250
251
252 -- l_proc_name varchar2(200) default 'get_person_rec';
253 l_person_rec per_people_f%ROWTYPE;
254 l_legislation_code per_business_groups.legislation_code%type default null;
255 l_proc constant varchar2(100) := g_package || ' get_person_rec';
256
257 BEGIN
258 hr_utility.set_location('Entering: '|| l_proc,5);
259 l_legislation_code := null;
260 OPEN csr_leg_code;
261 hr_utility.trace('Going into Fetch after (OPEN csr_leg_code): '|| l_proc);
262 FETCH csr_leg_code into l_legislation_code;
263 IF csr_leg_code%NOTFOUND
264 THEN
265 CLOSE csr_leg_code;
266 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
267 hr_utility.trace(' Exception HR_7220_INVALID_PRIMARY_KEY ');
268 RAISE g_error_handled;
269 ELSE
270 CLOSE csr_leg_code;
271 END IF;
272
273 -- Now get the person record using the legislation code found.
274 OPEN csr_pp(p_person_id => p_person_id
275 ,p_legislation_code => l_legislation_code);
276 hr_utility.trace('Going into Fetch after (OPEN csr_pp(p_person_id p_legislation_code )): '|| l_proc);
277 FETCH csr_pp INTO l_person_rec.business_group_id
278 ,l_person_rec.last_name
279 ,l_person_rec.first_name
280 ,l_person_rec.person_type_id
281 ,l_person_rec.email_address
282 ,l_person_rec.title
283 ,l_person_rec.full_name;
284
285 IF csr_pp%NOTFOUND OR csr_pp%NOTFOUND IS null THEN
286 CLOSE csr_pp;
287 fnd_message.set_name('PER','HR_51396_WEB_PERSON_NOT_FND');
288 hr_utility.trace(' Exception HR_51396_WEB_PERSON_NOT_FND ');
289 RAISE g_error_handled;
290 END IF;
291 CLOSE csr_pp;
292 hr_utility.set_location('Leaving: '|| l_proc,20);
293 RETURN (l_person_rec);
294 --
295 EXCEPTION
296 WHEN others THEN
297 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
298 hr_utility.trace(' Exception ' || sqlerrm );
299 RAISE g_error_handled;
300 END get_person_rec;
301 --
302 -- ------------------------------------------------------------------------
303 -- return_msg_text
304 --
305 -- Purpose: This function can be called to return the message text which
306 -- can then be used for display in javascript alert or confirm box.
307 -- ------------------------------------------------------------------------
308 FUNCTION return_msg_text(p_message_name IN VARCHAR2
309 ,p_application_id IN VARCHAR2 DEFAULT 'PER')
310 RETURN VARCHAR2
311 IS
312 l_proc constant varchar2(100) := g_package || ' return_msg_text';
313 BEGIN
314 hr_utility.set_location('Entering: '|| l_proc,5);
315
316
317 fnd_message.set_name (p_application_id, p_message_name);
318 -- To fix 2095929
319 --RETURN fnd_message.get;
320 hr_utility.set_location('Leaving: '|| l_proc,10);
321 RETURN replace(fnd_message.get,'''','\''');
322 END return_msg_text;
323 -- ----------------------------------------------------------------------------
324 -- |--< Get_lookup_values >---------------------------------------------------|
325 -- ----------------------------------------------------------------------------
326 FUNCTION Get_lookup_values
327 ( p_lookup_type IN VARCHAR2
328 , p_schema IN VARCHAR2 DEFAULT 'HR'
329 )
330 RETURN g_lookup_values_tab_type
331 IS
332 l_array g_lookup_values_tab_type;
333 l_temp_array HR_GENERAL_UTILITIES.g_lookup_values_tab_type;
334 l_proc constant varchar2(100) := g_package || ' Get_lookup_values';
335 --
336 -- l_proc VARCHAR2 (72) := g_package || ' Get_lookup_values';
337 --
338 BEGIN
339 hr_utility.set_location('Entering: '|| l_proc,5);
340 --
341 l_temp_array := HR_GENERAL_UTILITIES.Get_lookup_values
342 (p_lookup_type => p_lookup_type);
343
344 FOR i IN 1..l_temp_array.count LOOP
345 l_array(i).lookup_type := l_temp_array(i).lookup_type;
346 l_array(i).lookup_code := l_temp_array(i).lookup_code;
347 l_array(i).meaning := l_temp_array(i).meaning;
348 END LOOP;
349 hr_utility.set_location('Leaving: '|| l_proc,10);
350 RETURN l_array;
351 END Get_lookup_values;
352 -- ----------------------------------------------------------------------------
353 -- ------------------------------------------------------------------------
354 -- ------------------------ < get_user_date_format> -----------------------
355 -- ------------------------------------------------------------------------
356 -- name:
357 -- get_user_date_format
358 --
359 -- description:
360 -- This function retrieves user's preference date format mask from
361 -- icx.
362 -- ------------------------------------------------------------------------
363 FUNCTION get_user_date_format
364 return varchar2
365 is
366 l_date_fmt varchar2(200);
367 l_person_id number;
368 l_proc constant varchar2(100) := g_package || ' get_user_date_format';
369 --
370 begin
371 hr_utility.set_location('Entering: '|| l_proc,5);
372 validate_session(p_person_id => l_person_id
373 ,p_icx_update => false);
374 l_date_fmt := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
375 --
376 hr_utility.set_location('Leaving: '|| l_proc,10);
377 return l_date_fmt;
378 --
379 Exception
380 when others then
381 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
382 hr_utility.trace(' Exception ' || sqlerrm );
383 raise g_date_error;
384 --
385 end get_user_date_format;
386 --
387 -- ---------------------------------------------------------------------------
388 -- ------------------------ <decode_date_lookup_code> ------------------------
389 -- ---------------------------------------------------------------------------
390 -- Usage: This function accepts input of a lookup code, a table column name
391 -- which must be in date data type. This function will decode the
392 -- lookup code and return an expression based on the date column passed
393 -- in. The expression is in the form of a sql where clause for building
394 -- the dynamic sql.
395 --
396 -- Input:
397 -- 1) p_date_compare_to_column
398 -- - This is the column name of a table. It must be in date data type.
399 -- For example, if you want to compare the Hire Date with a calculated
400 -- date, the value in this parameter would be 'date_start' or
401 -- 'ppos.date_start' (where pps is the alias for per_periods_of_service'
402 -- table if alias is used for the table).
403 --
404 -- 2) p_date_lookup_code
405 -- - The following examples depicts some of the formats represented:
406 -- Position
407 -- 123456789012 Lookup Code Meaning
408 -- ------------ --------------------
409 -- 001-WL-001-D within last 1 day
410 -- 020-WL-180-D within last 180 days
411 -- 200-WL-001-W within last week
412 -- 202-WL-050-W within last 50 weeks
413 -- 400-WL-001-M within last 1 month
414 -- 403-WL-003-M within last 3 months
415 -- 406-WL-006-M within last 6 months
416 -- 700-WL-001-Y within last 1 year
417 -- 710-WL-010-Y within last 10 years
418 --
419 -- 001-WN-001-D within next 1 day
420 -- 020-WN-180-D within next 180 days
421 -- 200-WN-001-W within next week
422 -- 202-WN-050-W within next 50 weeks
423 -- 400-WN-001-M within next 1 month
424 -- 403-WN-003-M within next 3 months
425 -- 406-WN-006-M within next 6 months
426 -- 700-WN-001-Y within next 1 year
427 -- 710-WN-010-Y within next 10 years
428 --
429 -- 001-MP-001-D more than 1 day in the past
430 -- 020-MP-180-D more than 180 days in the past
431 -- 200-MP-001-W more than 1 week in the past
432 -- 202-MP-050-W more than 50 weeks in the past
433 -- 400-MP-001-M more than 1 month in the past
434 -- 403-MP-003-M more than 3 months in the past
435 -- 406-MP-006-M more than 6 months in the past
436 -- 700-MP-001-Y more than 1 year in the past
437 -- 710-MP-010-Y more than 10 years in the past
438 --
439 -- 001-MF-001-D more than 1 day in the future
440 -- 020-MF-180-D more than 180 days in the future
441 -- 200-MF-001-W more than 1 week in the future
442 -- 202-MF-050-W more than 50 weeks in the future
443 -- 400-MF-001-M more than 1 month in the future
444 -- 403-MF-003-M more than 3 months in the future
445 -- 406-MF-006-M more than 6 months in the future
446 -- 700-MF-001-Y more than 1 year in the future
447 -- 710-MF-010-Y more than 10 years in the future
448 --
449 -- Position Meaning
450 -- -----------------------------------------------------------------------
451 -- 1-3 Collating Sequence
452 -- Numeric collating sequence for sorting the lookup code. The numeric
453 -- values are from 000 to 999. The lookup codes are sorted in
454 -- ascending order of this collating sequence.
455 -- These collating sequence digits are used in display page. They are
456 -- ignored by this function.
457 --
458 -- 4 delimiter
459 --
460 -- 5-6 Time Period
461 -- WL -> within last, specifies a range from a calculated date to
462 -- either today's date or some effective date
463 --
464 -- WN -> within next, specifies a range from either today's date or
465 -- some effective date to a calculated date.
466 --
467 -- MP -> more than x (days, weeks, months, or years) in the past.
468 --
469 -- MF -> more than x (days, weeks, months, or years) in the future.
470 --
471 -- 7 delimiter
472 --
473 -- 8-10 Length of Time
474 -- 999 -> numeric values from 001 thru 999
475 --
476 -- 11 delimiter
477 --
478 -- 12 Unit of Time
479 -- D -> day
480 -- W -> week
481 -- M -> month
482 -- Y -> year
483 --
484 -- NOTE: This function does not support quarter at this time.
485 --
486 -- 3) p_effective_date
487 -- This parameter is optional. If it is not passed in, it will use sysdate
488 -- (subtract or add) to derive the calculated the date. Otherwise, it will
489 -- use the date passed in.
490 --
491 -- Output:
492 -- ------
493 -- varchar2
494 -- - an expression in the form like, for example:
495 -- Lookup Code Expression
496 -- ------------ -------------------------------------------------------
497 -- 710-WL-010-Y ppos.date_start >= add_months(trunc(sysdate), -10*12)
498 -- and ppos.date_start <= trunc(sysdate)
499 --
500 -- 710-MP-010-Y ppos.date_start < add_months(trunc(sysdate), -10*12)
501 --
502 -- 710-WN-010-Y ppos.date_start >= trunc(sysdate) and
503 -- ppos.date_start <= add_months(trunc(sysdate), 10*12)
504 --
505 -- 710-MF-010-Y ppos.date_start >= add_months(trunc(sysdate), 10*12)
506 --
507 -- ---------------------------------------------------------------------------
508 Function decode_date_lookup_code
509 (p_date_compare_to_column in varchar2 default null
510 ,p_date_lookup_code in varchar2
511 ,p_effective_date in date default trunc(sysdate))
512 return varchar2 is
513 --
514 l_date_lookup_code varchar2(30) default null;
515 l_sql_expression varchar2(2000) default null;
516 l_date_range varchar2(10) default null;
517 l_length_of_time_char varchar2(10) default null;
518 l_length_of_time number(15) default null;
519 l_unit_of_time varchar2(10) default null;
520 l_months number(15) default null;
521 l_effective_date date default null;
522 l_effective_date_exp varchar2(2000) default null;
523 l_calculated_date date default null;
524 l_calculated_date_exp varchar2(2000) default null;
525 l_date_format varchar2(200) default null;
526 l_proc constant varchar2(100) := g_package || ' decode_date_lookup_code';
527 --
528 Begin
529 hr_utility.set_location('Entering: '|| l_proc,5);
530 --
531 IF p_date_lookup_code is null or p_date_compare_to_column is null THEN
532 goto done;
533 END IF;
534 --
535 -- get ICX user date format
536 l_date_format := get_user_date_format;
537 --
538 IF p_effective_date is null THEN
539 l_effective_date := trunc(sysdate);
540 ELSE
541 l_effective_date := p_effective_date;
542 END IF;
543 --
544 -- -------------------------------------------------------------------------
545 -- Build the effective date sql expression first.
546 -- -------------------------------------------------------------------------
547 l_effective_date_exp := build_date2char_expression
548 (p_date => l_effective_date
549 ,p_date_format => l_date_format);
550 --
551 -- Ignore the first 3 collating digits and the delimiter.
552 l_date_lookup_code := substr(p_date_lookup_code, 5);
553 --
554 -- Now the lookup code would look like XX-999-X
555 l_date_range := substr(l_date_lookup_code, 1, 2);
556 l_length_of_time_char := substr(l_date_lookup_code, 4, 3);
557 --
558 -- Convert the length of time to numeric
559 l_length_of_time := to_number(l_length_of_time_char);
560 --
561 l_unit_of_time := substr(l_date_lookup_code, 8);
562 --
563 IF upper(l_date_range) = 'WL' or upper(l_date_range) = 'MP' THEN
564 hr_utility.trace('In( IF upper(l_date_range) = WL or upper(l_date_range) = MP): '|| l_proc);
565 -- 'WL' -> Within Last
566 -- 'MP' -> more than x(days, weeks, months, years) in the past
567 -- We need to subtract the calculated date from the p_effective_date for
568 -- either 'WL' or 'MP'
569 IF upper(l_unit_of_time) = 'D' THEN -- Days
570 l_calculated_date := l_effective_date - l_length_of_time;
571 ELSIF upper(l_unit_of_time) = 'W' THEN -- Weeks
572 l_calculated_date := l_effective_date - l_length_of_time * 7;
573 ELSIF upper(l_unit_of_time) = 'M' THEN -- Months
574 l_calculated_date := add_months(l_effective_date,-(l_length_of_time));
575 ELSIF upper(l_unit_of_time) = 'Y' THEN -- Years
576 l_calculated_date :=
577 add_months(l_effective_date,-(l_length_of_time * 12));
578 END IF;
579 --
580 --------------------------------------------------------------------------
581 -- Construct the sql clause:
582 -- For example: p_date_compare_to_column = 'ppos.date_start'
583 -- p_effective_date is null
584 -- p_lookup_code = '710WL010Y'
585 -- where l_calculated_date=add_months(trunc(sysdate), -10*12)
586 --
587 -- l_sql_expression = 'ppos.date_start between '||
588 -- l_calculated_date ||
589 -- ' and trunc(sysdate)'
590 --
591 -- Need to call a function to convert the date to char first and then
592 -- use to_date('05-Aug-1998', 'dd-mon-rrrr') in the sql expression.
593 -- Otherwise, we would have created an expression like
594 -- 'ppos.hire_date >= 05-FEB-98 and ppos.hire_date <= 05-AUG-98' which
595 -- will cause invalid data type error because '05-FEB-98' is not in date
596 -- data type.
597 -- We need to get the date to char expression for the calculated first.
598 --------------------------------------------------------------------------
599 --
600 l_calculated_date_exp := build_date2char_expression
601 (p_date => l_calculated_date
602 ,p_date_format => l_date_format);
603 --
604 IF upper(l_date_range) = 'WL' THEN
605 l_sql_expression := p_date_compare_to_column || ' between '
606 || l_calculated_date_exp
607 || ' and ' || l_effective_date_exp;
608 ELSE
609 l_sql_expression := p_date_compare_to_column || ' < '
610 || l_calculated_date_exp;
611 END IF;
612 ELSE
613 hr_utility.trace('In else of ( IF upper(l_date_range) = WL or upper(l_date_range) = MP): '|| l_proc);
614 IF upper(l_date_range) = 'WN' or upper(l_date_range) = 'MF' THEN
615 -- 'WN' -> Within Next
616 -- 'MP' -> more than x(days, weeks, months, years) in the future
617 -- We need to add the calculated date from the p_effective_date for
618 -- either 'WN' or 'MF'
619 --
620 IF upper(l_unit_of_time) = 'D' THEN -- Days
621 l_calculated_date := l_effective_date + l_length_of_time * 2;
622 ELSIF upper(l_unit_of_time) = 'W' THEN -- Weeks
623 l_calculated_date := l_effective_date + l_length_of_time * 7;
624 ELSIF upper(l_unit_of_time) = 'M' THEN -- Months
625 l_calculated_date := add_months(l_effective_date, l_length_of_time);
626 ELSIF upper(l_unit_of_time) = 'Y' THEN -- Years
627 l_calculated_date :=
628 add_months(l_effective_date, l_length_of_time * 12);
629 END IF;
630 --
631 l_calculated_date_exp := build_date2char_expression
632 (p_date => l_calculated_date
633 ,p_date_format => l_date_format);
634 --
635 IF upper(l_date_range) = 'WN' THEN
636 l_sql_expression := p_date_compare_to_column || ' between '
637 || l_effective_date_exp
638 || ' and ' || l_calculated_date_exp;
639 ELSE
640 l_sql_expression := p_date_compare_to_column || ' >= '
641 || l_calculated_date_exp;
642 END IF;
643 --
644 END IF;
645 END IF;
646 --
647 <<done>>
648 hr_utility.set_location('Leaving: '|| l_proc,15);
649 return l_sql_expression;
650 --
651 End decode_date_lookup_code;
652 --
653 -- ---------------------------------------------------------------------------
654 -- ------------------------ <build_date2char_expression>----------------------
655 -- ---------------------------------------------------------------------------
656 -- This function will convert the input date parameter to character first and
657 -- then construct an sql expression which can be included in a dynamic sql
658 -- clause.
659 -- ---------------------------------------------------------------------------
660 Function build_date2char_expression(p_date in date
661 ,p_date_format in varchar2)
662 return varchar2 is
663 --
664 l_date_char varchar2(100) default null;
665 l_date2char_exp varchar2(2000) default null;
666 --
667 l_proc constant varchar2(100) := g_package || ' build_date2char_expression';
668 Begin
669 hr_utility.set_location('Entering: '|| l_proc,5);
670 --Convert the incoming date to character format first using the passed in
671 --date format.
672 l_date_char := to_char(p_date, p_date_format);
673 --
674 ---------------------------------------------------------------------------
675 -- chr(39) is single quote.
676 -- The following construct the string:
677 -- "to_date(''05-AUG-1998'', ''DD-MON-RRRR'')"
678 --
679 -- 05-MAY-1999
680 -- Changed by skamatka for Bug# 884748
681 -- Can't use chr functions!
682 ---------------------------------------------------------------------------
683 --
684 l_date2char_exp := 'to_date(' ||
685 '''' ||
686 l_date_char ||
687 '''' ||
688 ', ' ||
689 '''' ||
690 p_date_format ||
691 '''' ||
692 ')';
693 --
694 hr_utility.set_location('Leaving: '|| l_proc,10);
695 return l_date2char_exp;
696 --
697 EXCEPTION
698 When others THEN
699 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
700 hr_utility.trace(' Exception ' || sqlerrm );
701 raise g_date_error;
702 --
703 END build_date2char_expression;
704 --
705 -- ---------------------------------------------------------------------------
706 -- ------------------------ <validate_date_lookup_code> ----------------------
707 -- ---------------------------------------------------------------------------
708 -- This function will validate the date lookup code passed in to make sure that
709 -- the lookup code conforms to the pre-defined format. Any incorrect lookup
710 -- codes will be written to the error stack. The correct format will be
711 -- written to a record structure and return to the caller.
712 -- ---------------------------------------------------------------------------
713 --
714 Function validate_date_lookup_code
715 (p_lookup_type in varchar2
716 ,p_effective_date in date default trunc(sysdate))
717 return hr_util_misc_web.g_lookup_values_tab_type is
718 --
719 cursor get_lookup_code_meaning is
720 select lookup_code, meaning
721 from hr_lookups
722 where lookup_type = p_lookup_type
723 and enabled_flag = 'Y'
724 and nvl(p_effective_date, trunc(sysdate))
725 between nvl(start_date_active,hr_api.g_sot)
726 and nvl(end_date_active,hr_api.g_eot)
727 order by lookup_code;
728 --
729 l_lookup_code_meaning_row get_lookup_code_meaning%rowtype;
730 l_lookup_code_meaning_rec_tbl hr_util_misc_web.g_lookup_values_tab_type
731 default hr_util_misc_web.g_lookup_values_tab_default;
732 l_index binary_integer default 0;
733 l_length number default 0;
734 l_time_period varchar2(10) default null;
735 l_length_of_time varchar2(10) default null;
736 l_unit_of_time varchar2(10) default null;
737 l_number number default 0;
738 l_date_error varchar2(1) default null;
739 l_msg_text varchar2(2000) default null;
740 l_msg_text2 varchar2(2000) default null;
741 l_proc constant varchar2(100) := g_package || ' validate_date_lookup_code';
742 --
743 Begin
744 hr_utility.set_location('Entering: '|| l_proc,5);
745 --
746 IF p_lookup_type is null THEN
747 -- issue error
748 fnd_message.set_name('PER','HR_WEB_INVALID_DATE_LKUP_CODE');
749 l_msg_text := fnd_message.get;
750 --
751 fnd_message.set_name('PER','HR_WEB_INVALID_DATE_LKUP_TOKEN');
752 fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
753 fnd_message.set_token('LOOKUP_CODE'
754 ,l_lookup_code_meaning_row.lookup_code);
755 l_msg_text2 := fnd_message.get;
756 l_length := instr(l_msg_text2, '(');
757 l_msg_text := l_msg_text || substr(l_msg_text2, l_length);
758 --
759 hr_errors_api.addErrorToTable
760 (p_errorcode => ' '
761 ,p_errormsg => l_msg_text);
762 --
763 goto done;
764 END IF;
765 --
766 OPEN get_lookup_code_meaning;
767 LOOP
768 hr_utility.trace('Going into Fetch after (OPEN get_lookup_code_meaning ): '|| l_proc);
769 FETCH get_lookup_code_meaning into l_lookup_code_meaning_row;
770 exit when get_lookup_code_meaning%NOTFOUND;
771 -------------------------------------------------------------------------
772 -- We want to capture all the errors for each component of the code. Hence,
773 -- we need to set a switch to continue on and that switch will be checked
774 -- at the last component. If the switch is null, then we write the lookup
775 -- code to the record structure table.
776 --------------------------------------------------------------------------
777 l_date_error := null;
778 --
779 l_index := l_index + 1;
780 --
781 -- Validate the lookup format, which must be in '999-XX-999-X' format.
782 l_length := length(l_lookup_code_meaning_row.lookup_code);
783 IF l_length <> 12 THEN
784 l_date_error := 'Y';
785 goto check_if_err_found;
786 END IF;
787 --
788 l_time_period := substr(l_lookup_code_meaning_row.lookup_code, 5, 2);
789 l_length_of_time := substr(l_lookup_code_meaning_row.lookup_code, 8, 3);
790 l_unit_of_time := substr(l_lookup_code_meaning_row.lookup_code, 12, 1);
791 --
792 BEGIN -- Time Period block
793 IF upper(l_time_period) = 'WL' or
794 upper(l_time_period) = 'WN' or
795 upper(l_time_period) = 'MP' or
796 upper(l_time_period) = 'MF' THEN
797 null;
798 ELSE
799 raise hr_util_misc_web.g_invalid_time_period;
800 END IF;
801 --
802 EXCEPTION
803 WHEN g_invalid_time_period THEN
804 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
805 l_date_error := 'Y';
806 goto check_if_err_found;
807 --
808 WHEN others THEN
809 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
810 l_date_error := 'Y';
811 goto check_if_err_found;
812
813 END; -- End Time Period block
814 --
815 BEGIN
816 l_number := to_number(l_length_of_time);
817 --
818 -- IF l_length_of_time contains non-numeric values, it will issue
819 -- "ORA-06502: PL/SQL: numeric or value error".
820 --
821 Exception
822 When others THEN
823 hr_utility.set_location('EXCEPTION: '|| l_proc,565);
824 l_date_error := 'Y';
825 goto check_if_err_found;
826
827 END;
828 --
829 BEGIN
830 IF upper(l_unit_of_time) = 'D' or
831 upper(l_unit_of_time) = 'W' or
832 upper(l_unit_of_time) = 'M' or
833 upper(l_unit_of_time) = 'Y' THEN
834 --
835 IF l_date_error is null THEN
836 l_lookup_code_meaning_rec_tbl(l_index).lookup_code :=
837 l_lookup_code_meaning_row.lookup_code;
838 --
839 l_lookup_code_meaning_rec_tbl(l_index).meaning :=
840 l_lookup_code_meaning_row.meaning;
841 END IF;
842 ELSE
843 raise hr_util_misc_web.g_invalid_time_unit;
844 END IF;
845 --
846 Exception
847 WHEN g_invalid_time_unit THEN
848 hr_utility.set_location('EXCEPTION: '|| l_proc,570);
849 l_date_error := 'Y';
850 goto check_if_err_found;
851
852 END;
853 --
854 <<check_if_err_found>>
855 --
856 IF l_date_error = 'Y' THEN
857 fnd_message.set_name('PER','HR_WEB_INVALID_DATE_LKUP_CODE');
858 l_msg_text := fnd_message.get;
859 --
860 fnd_message.set_name('PER','HR_WEB_INVALID_DATE_LKUP_TOKEN');
861 fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
862 fnd_message.set_token('LOOKUP_CODE'
863 ,l_lookup_code_meaning_row.lookup_code);
864 l_msg_text2 := fnd_message.get;
865 l_length := instr(l_msg_text2, '(');
866 l_msg_text := l_msg_text || substr(l_msg_text2, l_length);
867 --
868 hr_errors_api.addErrorToTable
869 (p_errorcode => ' '
870 ,p_errormsg => l_msg_text);
871 END IF;
872 --
873
874 END LOOP;
875 --
876 <<done>>
877 hr_utility.set_location('Leaving: '|| l_proc,15);
878 return l_lookup_code_meaning_rec_tbl;
879 --
880 Exception
881 When others THEN
882 hr_utility.set_location('EXCEPTION: '|| l_proc,575);
883 -- Add error to error table
884 fnd_message.set_name('PER','HR_WEB_INVALID_DATE_LKUP_CODE');
885 l_msg_text := fnd_message.get;
886 --
887 fnd_message.set_name('PER','HR_WEB_INVALID_DATE_LKUP_TOKEN');
888 fnd_message.set_token('lookup_type', p_lookup_type);
889 fnd_message.set_token('lookup_code'
890 ,l_lookup_code_meaning_row.lookup_code);
891 l_msg_text2 := fnd_message.get;
892 l_length := instr(l_msg_text2, '(');
893 l_msg_text := l_msg_text || substr(l_msg_text2, l_length);
894 --
895 hr_errors_api.addErrorToTable
896 (p_errorcode => ' '
897 ,p_errormsg => l_msg_text);
898
899 END validate_date_lookup_code;
900 --
901 -- ------------------------------------------------------------------------
902 -- insert_session_row
903 --
904 -- Description:
905 -- This procedure insert a record into the fnd_sessions table so that we
906 -- may select data from date-tracked tables. It's over-loaded to accept a
907 -- date field or a varchar2 encrypted date. It also checks the user's
908 -- security
909 --
910 -- Updated for bug 1994945
911 -- ------------------------------------------------------------------------
912 PROCEDURE insert_session_row(p_effective_date in date) IS
913 --
914 l_proc varchar2(100) := g_package || 'insert_session_row';
915 --
916 BEGIN
917 hr_utility.set_location('Entering: '|| l_proc,5);
918 --
919 /* g_debug := hr_utility.debug_enabled;
920 if g_debug then
921 l_proc := g_package || 'insert_session_row';
922 hr_utility.set_location('Entering : ' || l_proc, 5);
923 end if; */
924 --
925 dt_fndate.set_effective_date(trunc(p_effective_date));
926 --
927 /* if g_debug then
928 hr_utility.set_location('Leaving : ' || l_proc, 10);
929 end if; */
930 --
931 hr_utility.set_location('Leaving: '|| l_proc,10);
932 EXCEPTION
933 WHEN others THEN
934 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
935 null;
936 END insert_session_row;
937
938 -- ------------------------------------------------------------------------
939 -- autonomous_commit_fnd_sess_row
940 --
941 -- Description:
942 -- This procedure inserts a record into the fnd_sessions table so that we
943 -- may select data from date-tracked tables. It also checks the user's
944 -- security.
945 -- This procedure returns an output parameter on session_id to aid debugging,
946 -- especially when the caller is a Java program.
947 --
948 -- Updated for bug 1940440
949 -- ------------------------------------------------------------------------
950 PROCEDURE autonomous_commit_fnd_sess_row
951 (p_effective_date in date
952 ,p_session_id out nocopy number)
953 IS
954
955 PRAGMA AUTONOMOUS_TRANSACTION;
956 --
957
958 -- 09/18/2001 SRAMASAMY advised to select from dual instead of
959 -- using 'select session_id from fnd_sessions where userenv('sessionid') =
960 -- FND_SESSIONS.session_id' because of the hit on fnd_sessions table.
961 CURSOR get_session_id IS
962 SELECT userenv('sessionid')
963 FROM dual;
964
965 l_session_id number default null;
966 l_proc constant varchar2(100) := g_package || ' autonomous_commit_fnd_sess_row';
967
968
969 BEGIN
970
971 --
972 g_debug := hr_utility.debug_enabled;
973 if g_debug then
974 --l_proc := g_package || 'insert_session_row';
975 hr_utility.set_location('Entering : ' || l_proc, 5);
976 end if;
977 --
978 dt_fndate.set_effective_date(trunc(p_effective_date));
979 --
980 commit;
981
982 -- The following select query is to retreive the session_id just created.
983 -- This will aid debugging to the caller.
984 OPEN get_session_id;
985 hr_utility.trace('Going into Fetch after ( OPEN get_session_id): '|| l_proc);
986 FETCH get_session_id into l_session_id;
987 IF get_session_id%NOTFOUND
988 THEN
989 l_session_id := null;
990 END IF;
991
992 CLOSE get_session_id;
993
994 p_session_id := l_session_id;
995
996 if g_debug then
997 hr_utility.set_location('Leaving : ' || l_proc, 15);
998 end if;
999 --
1000 EXCEPTION
1001 WHEN others THEN
1002 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1003 rollback;
1004 raise;
1005 END autonomous_commit_fnd_sess_row;
1006
1007
1008 PROCEDURE remove_session_row IS
1009
1010 l_person_id per_people_f.person_id%type;
1011 l_proc constant varchar2(100) := g_package || ' remove_session_row';
1012 BEGIN
1013 hr_utility.set_location('Entering: '|| l_proc,5);
1014 delete from fnd_sessions
1015 where session_id = userenv('sessionid');
1016 hr_utility.set_location('Leaving : ' || l_proc, 10);
1017 EXCEPTION
1018 WHEN others THEN
1019 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1020 raise;
1021 END remove_session_row;
1022
1023 -- ------------------------------------------------------------------------
1024 -- validate_session
1025 --
1026 -- Description:
1027 -- This procedure calls the Internet Commerce security routine that check
1028 -- that the user has a full, appropriate 'cookie' for their web session.
1029 -- It also obtains the Person_Id of the user.
1030 -- ------------------------------------------------------------------------
1031
1032 PROCEDURE validate_session(p_person_id out nocopy number
1033 ,p_check_ota in varchar2 default 'N'
1034 ,p_check_ben in varchar2 default 'N'
1035 ,p_check_pay in varchar2 default 'N'
1036 ,p_icx_update in boolean default true
1037 ,p_icx_commit in boolean default false) IS
1038 --
1039 l_web_username varchar2(80) default null;
1040 l_person_id per_people_f.person_id%type;
1041 l_proc constant varchar2(100) := g_package || ' validate_session';
1042 --
1043 BEGIN
1044 hr_utility.set_location('Entering: '|| l_proc,5);
1045 hr_util_misc_web.validate_session
1046 (p_person_id => l_person_id
1047 ,p_web_username => l_web_username
1048 ,p_check_ota => p_check_ota
1049 ,p_check_ben => p_check_ben
1050 ,p_check_pay => p_check_pay
1051 ,p_icx_update => p_icx_update
1052 ,p_icx_commit => p_icx_commit);
1053 p_person_id := l_person_id;
1054 hr_utility.set_location('Leaving : ' || l_proc, 10);
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1058 NULL;
1059 END validate_session;
1060
1061 PROCEDURE validate_session(p_person_id out nocopy number
1062 ,p_web_username out nocopy varchar2
1063 ,p_check_ota in varchar2 default 'N'
1064 ,p_check_ben in varchar2 default 'N'
1065 ,p_check_pay in varchar2 default 'N'
1066 ,p_icx_update in boolean default true
1067 ,p_icx_commit in boolean default false) IS
1068
1069 CURSOR csr_hr_installation_status(p_application_id number) IS
1070 SELECT fpi.status status
1071 FROM fnd_product_installations fpi
1072 WHERE fpi.application_id = p_application_id;
1073
1074 l_web_user_id number;
1075 l_web_username varchar2(80) default null;
1076
1077 l_cookie owa_cookie.cookie;
1078 l_person_id per_people_f.person_id%TYPE;
1079 l_hr_installation_status fnd_product_installations.status%TYPE default null;
1080 l_proc constant varchar2(100) := g_package || ' validate_session';
1081
1082 BEGIN
1083 hr_utility.set_location('Entering: '|| l_proc,5);
1084 IF NOT(icx_sec.validateSession(c_update => p_icx_update
1085 ,c_commit => p_icx_commit)) THEN
1086 RAISE g_error_handled;
1087 ELSE
1088 -- ensure HR is fully installed
1089 -- If not then raise an error
1090 OPEN csr_hr_installation_status(p_application_id => 800);
1091 hr_utility.trace('Going into Fetch after(OPEN csr_hr_installation_status(p_application_id 800) ): '|| l_proc);
1092 FETCH csr_hr_installation_status into l_hr_installation_status;
1093 CLOSE csr_hr_installation_status;
1094 IF NOT l_hr_installation_status = 'I' THEN
1095 --
1096 fnd_message.set_name('PER', 'HR_7079_HR_NOT_INSTALLED');
1097 RAISE g_no_app_error;
1098 END IF;
1099 --
1100 IF p_check_ota = 'Y' THEN
1101 -- ensure ota is fully installed
1102 -- If not then raise an error
1103 OPEN csr_hr_installation_status(p_application_id => 810);
1104 hr_utility.trace('Going into Fetch after(OPEN csr_hr_installation_status(p_application_id 810) ): '|| l_proc);
1105 FETCH csr_hr_installation_status into l_hr_installation_status;
1106 CLOSE csr_hr_installation_status;
1107 IF NOT l_hr_installation_status = 'I' THEN
1108 --
1109 fnd_message.set_name('OTA','OTA_13629_WEB_OTA_NOT_INSTALL');
1110 RAISE g_no_app_error;
1111 END IF;
1112 END IF;
1113 IF p_check_ben = 'Y' THEN
1114 -- ensure benefits is fully installed
1115 -- If not then raise an error
1116 -- We don't know the benefits number yet...must change that here:
1117 OPEN csr_hr_installation_status(p_application_id => 1234);
1118 hr_utility.trace('Going into Fetch after( OPEN csr_hr_installation_status(p_application_id => 1234)) ): '|| l_proc);
1119 FETCH csr_hr_installation_status into l_hr_installation_status;
1120 CLOSE csr_hr_installation_status;
1121 IF NOT l_hr_installation_status = 'I' THEN
1122 --
1123 -- We don't have a abbrev or message yet, chang that here:
1124 fnd_message.set_name('BEN','BEN_???_BEN_NOT_INSTALLED');
1125 RAISE g_no_app_error;
1126 END IF;
1127 END IF;
1128 IF p_check_pay = 'Y' THEN
1129 -- ensure Payroll is fully installed
1130 -- If not then raise an error
1131 OPEN csr_hr_installation_status(p_application_id => 801);
1132 hr_utility.trace('Going into Fetch after(OPEN csr_hr_installation_status(p_application_id => 801) ): '|| l_proc);
1133 FETCH csr_hr_installation_status into l_hr_installation_status;
1134 CLOSE csr_hr_installation_status;
1135 IF NOT l_hr_installation_status = 'I' THEN
1136 --
1137 -- We don't have a abbrev or message yet, chang that here:
1138 fnd_message.set_name('PAY','PAY_78031_PAY_NOT_INSTALLED');
1139 RAISE g_no_app_error;
1140 END IF;
1141 END IF;
1142
1143 --
1144 -- getid with a parm of 10 returns the web user id.
1145 -- we don't need this id in our code, but getid
1146 -- also returns a -1 into the web user id if we are in
1147 -- a psuedo session situation: this we do need to know
1148 -- so that we can manually get the person information when
1149 -- there is a psuedo session.
1150 l_web_user_id := icx_sec.getID(n_param => 10);
1151 --
1152 -- determine if the web user is -1 (pseudo session)
1153 IF l_web_user_id = -1 THEN
1154 hr_utility.trace('In if( IF l_web_user_id = -1 ) ): '|| l_proc);
1155 -- as we are in a pseudo session get the cookie record
1156 -- for the cookie WF_SESSION
1157 l_cookie := owa_cookie.get('WF_SESSION');
1158 -- ensure the cookie exists
1159 IF l_cookie.num_vals > 0 THEN
1160 -- as the cookie does exist get the web username from
1161 -- the workflow system
1162 l_web_username := wf_notification.accesscheck
1163 (l_cookie.vals(l_cookie.num_vals));
1164 --
1165 -- getid with a parm of 9 returns the internal-contact-id,
1166 l_person_id := icx_sec.getID(n_param => 9);
1167 --
1168 ELSE
1169 -- the WF_SESSION cookie does not exist. a serious error
1170 -- has ocurred which must be reported
1171 --
1172 fnd_message.set_name('PER','HR_51393_WEB_COOKIE_ERROR');
1173 hr_utility.trace(' Exception HR_51393_WEB_COOKIE_ERROR ');
1174 RAISE g_error_handled;
1175 END IF;
1176 ELSE
1177 hr_utility.trace('In else of if( IF l_web_user_id = -1 ) ): '|| l_proc);
1178 l_person_id := icx_sec.getID(n_param => 9);
1179 --
1180 -- getid with a parm of 99 returns the web user name.
1181 l_web_username := icx_sec.getID(n_param => 99);
1182 END IF;
1183 END IF;
1184 p_person_id := l_person_id;
1185 p_web_username := l_web_username;
1186 g_error_handled_var := FALSE;
1187 hr_utility.set_location('Leaving: '|| l_proc,40);
1188 EXCEPTION
1189 WHEN g_no_app_error THEN
1190 hr_utility.trace(' validate_session ' || SQLERRM );
1191 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1192 raise;
1193 WHEN TOO_MANY_ROWS then
1194 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1195 -- too many rows will be returned if the csr_iwu returns more than
1196 -- one person id for the web user.
1197 fnd_message.set_name('PER','HR_51776_WEB_TOO_MANY_USERS');
1198 hr_utility.trace(' Exception HR_51776_WEB_TOO_MANY_USERS ');
1199 WHEN g_error_handled then
1200 hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1201 g_error_handled_var := TRUE;
1202 raise;
1203 WHEN others then
1204 hr_utility.set_location('EXCEPTION: '|| l_proc,570);
1205 hr_utility.trace(' Exception ' || sqlerrm );
1206 raise;
1207 END validate_session;
1208 -- ------------------------------------------------------------------------
1209 -- prepare_parameter
1210 --
1211 -- Description:
1212 -- This procedure takes in a parameter and makes it URL ready by changing
1213 -- spaces to '+' and placing a '&' at the front of the parmameter name
1214 -- when p_prefix is true (the parameter is not first in the list).
1215 -- ------------------------------------------------------------------------
1216
1217 FUNCTION prepare_parameter(p_name in varchar2
1218 ,p_value in varchar2
1219 ,p_prefix in boolean default true)
1220 RETURN varchar2 IS
1221
1222 l_prefix varchar2(1);
1223 l_proc constant varchar2(100) := g_package || ' prepare_parameter';
1224
1225 BEGIN
1226 hr_utility.set_location('Entering: '|| l_proc,5);
1227 IF p_value IS NOT null THEN
1228 IF p_prefix THEN
1229 l_prefix := '&';
1230 END IF;
1231 hr_utility.set_location('Leaving: '|| l_proc,10);
1232 RETURN(l_prefix||p_name||'='||replace(p_value, ' ', '+'));
1233 ELSE
1234 hr_utility.set_location('Leaving: '|| l_proc,20);
1235 RETURN(null);
1236 END IF;
1237 END prepare_parameter;
1238 -------------------------------------------------------------------------------
1239 -- This function will append the given url to the
1240 -- value returned from get_owa_url to get a complete url.
1241 -- If no URL is passed to this function then the owa URL
1242 -- is returned.
1243 -------------------------------------------------------------------------------
1244 FUNCTION get_complete_url(p_url IN VARCHAR2 DEFAULT NULL) RETURN LONG IS
1245 l_url LONG;
1246 l_proc constant varchar2(100) := g_package || ' get_complete_url';
1247 BEGIN
1248 hr_utility.set_location('Entering: '|| l_proc,5);
1249 IF p_url IS NOT NULL THEN
1250 l_url := hr_util_misc_web.get_owa_url || p_url;
1251 ELSE
1252 l_url := hr_util_misc_web.get_owa_url;
1253 END IF;
1254 hr_utility.set_location('Leaving: '|| l_proc,10);
1255 RETURN l_url;
1256 END get_complete_url;
1257
1258 -- This function will return a url with following format
1259 -- http://<hostname>:<server port>/<DAD NAME>/<PLSQL AGENT NAME>/
1260 -- e.g. http://myhost.com:1234/test/owa/
1261
1262 FUNCTION get_owa_url RETURN VARCHAR2 IS
1263 l_owa VARCHAR2(2000);
1264 l_proc constant varchar2(100) := g_package || ' get_owa_url';
1265 BEGIN
1266 hr_utility.set_location('Entering: '|| l_proc,5);
1267 -- Fix for bug 894682
1268 l_owa := FND_WEB_CONFIG.PLSQL_AGENT;
1269 hr_utility.set_location('Leaving: '|| l_proc,10);
1270 RETURN l_owa;
1271 END get_owa_url;
1272
1273 -- ------------------------------------------------------------------------
1274 -- get_resume
1275 -- ------------------------------------------------------------------------
1276 -- This procedure is used in 'Apply for Job' and 'Professional Info' Modules
1277 -- Duplicated from hrcustwf.pkb (hr_offer_custom)
1278
1279 procedure get_resume
1280 (p_person_id IN NUMBER DEFAULT NULL
1281 ,p_resume out nocopy varchar2
1282 ,p_rowid out nocopy varchar2
1283 ,p_creation_date out nocopy varchar2) is
1284 --
1285 l_person_id per_people_f.person_id%type;
1286 l_resume_from_database varchar2(32000);
1287 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1288 default null;
1289 l_document_id fnd_documents.document_id%TYPE default null;
1290 l_media_id fnd_documents_tl.media_id%TYPE default null;
1291 l_attachment_text CLOB default null; -- Bug#13375642
1292 l_rowid varchar2(50) default null;
1293 l_category_id fnd_documents.category_id%type default null;
1294 l_seq_num fnd_attached_documents.seq_num%type default 0;
1295 l_creation_date fnd_documents_tl.creation_date%TYPE default null;
1296 l_proc constant varchar2(100) := g_package || ' get_resume';
1297 begin
1298 hr_utility.set_location('Entering: '|| l_proc,5);
1299 ----------------------------------------------------------------------------
1300 -- 10/15/97
1301 -- The following validate_session is not for security check because this
1302 -- procedure is not registered in fnd_enabled_plsql table. The real reason
1303 -- is for retrieving the login person_id for use in later calls to other
1304 -- procedures.
1305 ----------------------------------------------------------------------------
1306 ----------------------------------------------------------------------------
1307 -- 12-SEP-1998
1308 -- If person id is passed then, reusme for this person is fetched else,
1309 -- resume for the logged in person is retrieved.
1310 ----------------------------------------------------------------------------
1311 IF p_person_id IS NULL THEN
1312 hr_util_misc_web.validate_session(p_person_id => l_person_id
1313 ,p_icx_update => false -- 10/15/97 Changed
1314 ,p_icx_commit => false); -- 10/15/97 Changed
1315 ELSE
1316 l_person_id := p_person_id;
1317 END IF;
1318 --
1319 --get resume from database
1320 --
1321 -- Bug #1180110 Fix
1322 -- Changed the p_entity_name to use 'PER_PEOPLE_F' so that the Resume
1323 -- category attachment can be viewed via Forms.
1324
1325 get_attachment
1326 (p_attachment_text => l_resume_from_database
1327 ,p_entity_name => 'PER_PEOPLE_F'
1328 ,p_pk1_value => to_char(l_person_id)
1329 ,p_effective_date => sysdate
1330 ,p_attached_document_id => l_attached_document_id
1331 ,p_document_id => l_document_id
1332 ,p_media_id => l_media_id
1333 ,p_rowid => l_rowid
1334 ,p_category_id => l_category_id
1335 ,p_seq_num => l_seq_num
1336 ,p_user_name => 'HR_RESUME'
1337 ,p_creation_date => l_creation_date);
1338 p_resume := l_resume_from_database;
1339 p_rowid := l_rowid;
1340 p_creation_date := l_creation_date;
1341 --
1342 hr_utility.set_location('Leaving: '|| l_proc,10);
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1346 -- an error has occurred but because we are producing HTML
1347 -- just raise the exception
1348 p_resume := null;
1349 p_rowid := null;
1350 hr_utility.trace(' Exception ' || sqlerrm);
1351 raise;
1352 end get_resume;
1353
1354
1355 procedure insert_attachment_v4
1356 (p_attachment_text in CLOB default null -- Bug#13375642
1357 ,p_entity_name in varchar2 default null
1358 ,p_pk1_value in varchar2 default null
1359 ,p_name in fnd_document_categories_tl.name%TYPE
1360 ,p_rowid out nocopy varchar2
1361 ,p_login_person_id in number) is
1362
1363 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE;
1364 l_document_id fnd_documents.document_id%TYPE;
1365 l_media_id fnd_documents_tl.media_id%TYPE;
1366 l_proc constant varchar2(100) := g_package || ' insert_attachment_v4';
1367 begin
1368 hr_utility.set_location('Entering: '|| l_proc,5);
1369 insert_attachment
1370 (p_attachment_text => p_attachment_text
1371 ,p_entity_name => p_entity_name
1372 ,p_pk1_value => p_pk1_value
1373 ,p_name => p_name
1374 ,p_rowid => p_rowid
1375 ,p_login_person_id => p_login_person_id
1376 ,p_attached_document_id => l_attached_document_id
1377 ,p_document_id => l_document_id
1378 ,p_media_id => l_media_id);
1379 hr_utility.set_location('Leaving: '|| l_proc,10);
1380 end insert_attachment_v4;
1381 -- ----------------------------------------------------------------------------
1382 -- |--------------------------< insert_attachment >----------------------------|
1383 -- ----------------------------------------------------------------------------
1384 -- Duplicated from hrcustwf.pkb (hr_offer_custom)
1385 procedure insert_attachment
1386 (p_attachment_text in CLOB default null -- Bug#13375642
1387 ,p_entity_name in varchar2 default null
1388 ,p_pk1_value in varchar2 default null
1389 ,p_name in fnd_document_categories_tl.name%TYPE
1390 default 'HR_RESUME'
1391 ,p_attached_document_id out
1392 fnd_attached_documents.attached_document_id%TYPE
1393 ,p_document_id out
1394 fnd_documents.document_id%TYPE
1395 ,p_media_id out
1396 fnd_documents_tl.media_id%TYPE
1397 ,p_rowid out nocopy varchar2
1398 ,p_login_person_id in number) is -- 10/14/97 Changed
1399
1400 -- [CUSTOMIZE]
1401 -- Call fnd_attached_documents_pkg.insert_row api to insert into fnd_documents
1402 -- table. If customer uses third party software to store the resume, modify
1403 -- the code here.
1404
1405 l_rowid varchar2(50) default null;
1406 l_media_id fnd_documents_tl.media_id%TYPE;
1407 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1408 default null;
1409 l_document_id fnd_documents.document_id%TYPE default null;
1410 l_category_id fnd_document_categories.category_id%TYPE
1411 default null;
1412 l_datatype_id fnd_document_datatypes.datatype_id%TYPE default 2;
1413 l_language varchar2(30) default 'AMERICAN';
1414 l_seq_num fnd_attached_documents.seq_num%type;
1415 l_attachment_text CLOB; -- Bug#13375642
1416 l_proc constant varchar2(100) := g_package || ' insert_attachment';
1417 cursor csr_get_seq_num is
1418 select nvl(max(seq_num),0) + 10
1419 from fnd_attached_documents
1420 where entity_name = p_entity_name
1421 and pk1_value = p_pk1_value
1422 and pk2_value is null
1423 and pk3_value is null
1424 and pk4_value is null
1425 and pk5_value is null;
1426
1427 cursor csr_get_category_id (csr_p_lang in varchar2) is
1428 select category_id
1429 from fnd_document_categories_tl
1430 where language = csr_p_lang
1431 and name = p_name;
1432 --
1433
1434 Begin
1435 hr_utility.set_location('Entering: '|| l_proc,5);
1436 --
1437 -- Get language
1438 select userenv('LANG') into l_language from dual;
1439 --
1440 -- Get seq num
1441 --
1442 l_seq_num := 0;
1443 open csr_get_seq_num;
1444 hr_utility.trace('Going into Fetch after ( open csr_get_seq_num): '|| l_proc);
1445 fetch csr_get_seq_num into l_seq_num;
1446 close csr_get_seq_num;
1447 --
1448 -- Get category ID
1449 --
1450 open csr_get_category_id (csr_p_lang => l_language);
1451 hr_utility.trace('Going into Fetch after (open csr_get_category_id (csr_p_lang => l_language)m): '|| l_proc);
1452 fetch csr_get_category_id into l_category_id;
1453 if csr_get_category_id%notfound then
1454 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1455 hr_utility.raise_error;
1456 end if;
1457 close csr_get_category_id;
1458 --
1459 -- get sequence id for attached_document_id
1460 select fnd_attached_documents_s.nextval
1461 into l_attached_document_id
1462 from sys.dual;
1463
1464 -- Insert document to fnd_documents_long_text
1465 --
1466 fnd_attached_documents_pkg.insert_row
1467 (x_rowid => l_rowid
1468 ,x_attached_document_id => l_attached_document_id
1469 ,x_document_id => l_document_id
1470 ,x_creation_date => trunc(sysdate)
1471 ,x_created_by => p_login_person_id --10/14/97Chg
1472 ,x_last_update_date => trunc(sysdate)
1473 ,x_last_updated_by => p_login_person_id --10/14/97Chg
1474 ,x_seq_num => l_seq_num
1475 ,x_entity_name => p_entity_name
1476 ,x_column1 => 'PERSON_ID'
1477 ,x_pk1_value => p_pk1_value
1478 ,x_pk2_value => null
1479 ,x_pk3_value => null
1480 ,x_pk4_value => null
1481 ,x_pk5_value => null
1482 ,x_automatically_added_flag => 'N'
1483 ,x_datatype_id => l_datatype_id
1484 ,x_category_id => l_category_id
1485 ,x_security_type => 4
1486 ,x_publish_flag =>'N'
1487 ,x_usage_type =>'O'
1488 ,x_language => l_language
1489 ,x_media_id => l_media_id
1490 ,x_doc_attribute_category => null
1491 ,x_doc_attribute1 => null
1492 ,x_doc_attribute2 => null
1493 ,x_doc_attribute3 => null
1494 ,x_doc_attribute4 => null
1495 ,x_doc_attribute5 => null
1496 ,x_doc_attribute6 => null
1497 ,x_doc_attribute7 => null
1498 ,x_doc_attribute8 => null
1499 ,x_doc_attribute9 => null
1500 ,x_doc_attribute10 => null
1501 ,x_doc_attribute11 => null
1502 ,x_doc_attribute12 => null
1503 ,x_doc_attribute13 => null
1504 ,x_doc_attribute14 => null
1505 ,x_doc_attribute15 => null);
1506 --
1507
1508 -- Now insert into fnd_documents_long_text using the media_id
1509 -- generated from the above api call
1510 --
1511 --replace chr(13)chr(10) with chr(10)
1512 l_attachment_text := replace(p_attachment_text,
1513 g_carriage_return||g_line_feed,g_line_feed);
1514 insert into fnd_documents_long_text
1515 (media_id
1516 ,long_text)
1517 values
1518 (l_media_id
1519 ,l_attachment_text);
1520
1521 p_attached_document_id := l_attached_document_id;
1522 p_document_id := l_document_id;
1523 p_media_id := l_media_id;
1524 p_rowid := l_rowid;
1525
1526 hr_utility.set_location('Leaving: '|| l_proc,20);
1527 EXCEPTION
1528 When others then
1529 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1530 raise;
1531 --
1532 end insert_attachment;
1533
1534 -- ----------------------------------------------------------------------------
1535 -- |--------------------------< update_attachment >----------------------------|
1536 -- ----------------------------------------------------------------------------
1537 -- Duplicated from hrcustwf.pkb (hr_offer_custom)
1538
1539 procedure update_attachment
1540 (p_attachment_text in CLOB default null -- Bug#13375642
1541 ,p_entity_name in varchar2 default null
1542 ,p_pk1_value in varchar2 default null
1543 ,p_rowid in varchar2
1544 ,p_login_person_id in number) is -- 10/14/97 Changed
1545
1546 -- [CUSTOMIZE]
1547 -- Call fnd_attached_documents_pkg.update_row api to update fnd_documents
1548 -- table. If customer uses third party software to store the resume, modify
1549 -- the code here.
1550
1551 l_rowid varchar2(50);
1552 l_language varchar2(30) default 'AMERICAN';
1553 l_attachment_text CLOB; -- Bug#13375642
1554 l_proc constant varchar2(100) := g_package || ' update_attachment';
1555 data_error exception;
1556 --
1557 -- -------------------------------------------------------------
1558 -- Get the before update nullable fields so that we can
1559 -- preserve the values entered in 10SC GUI after the update.
1560 -- -------------------------------------------------------------
1561 cursor csr_get_attached_doc is
1562 select *
1563 from fnd_attached_documents
1564 where rowid = p_rowid;
1565 --
1566 cursor csr_get_doc(csr_p_document_id in number) is
1567 select *
1568 from fnd_documents
1569 where document_id = csr_p_document_id;
1570 --
1571 cursor csr_get_doc_tl (csr_p_lang in varchar2
1572 ,csr_p_document_id in number) is
1573 select *
1574 from fnd_documents_tl
1575 where document_id = csr_p_document_id
1576 and language = csr_p_lang;
1577 --
1578 l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
1579 l_doc_pre_upd csr_get_doc%rowtype;
1580 l_doc_tl_pre_upd csr_get_doc_tl%rowtype;
1581 --
1582 --
1583 Begin
1584 hr_utility.set_location('Entering: '|| l_proc,5);
1585 --
1586 -- Get language
1587 select userenv('LANG') into l_language from dual;
1588 --
1589 -- Get the before update nullable fields which are not used by the
1590 -- Web page to ensure the values are propagated.
1591 Open csr_get_attached_doc;
1592 hr_utility.trace('Going into Fetch after ( Open csr_get_attached_doc): '|| l_proc);
1593 fetch csr_get_attached_doc into l_attached_doc_pre_upd;
1594 IF csr_get_attached_doc%NOTFOUND THEN
1595 close csr_get_attached_doc;
1596 raise data_error;
1597 END IF;
1598
1599 Open csr_get_doc(l_attached_doc_pre_upd.document_id);
1600 hr_utility.trace('Going into Fetch after ( Open csr_get_doc(l_attached_doc_pre_upd.document_id)): '|| l_proc);
1601 fetch csr_get_doc into l_doc_pre_upd;
1602 IF csr_get_doc%NOTFOUND then
1603 close csr_get_doc;
1604 raise data_error;
1605 END IF;
1606
1607 Open csr_get_doc_tl (csr_p_lang => l_language
1608 ,csr_p_document_id => l_attached_doc_pre_upd.document_id);
1609 hr_utility.trace('Going into Fetch after (Open csr_get_doc_tl (csr_p_lang => l_language,csr_p_document_id => l_attached_doc_pre_upd.document_id)): '|| l_proc);
1610 fetch csr_get_doc_tl into l_doc_tl_pre_upd;
1611 IF csr_get_doc_tl%NOTFOUND then
1612 close csr_get_doc_tl;
1613 raise data_error;
1614 END IF;
1615
1616 -- Now, lock the rows.
1617 fnd_attached_documents_pkg.lock_row
1618 (x_rowid => p_rowid
1619 ,x_attached_document_id =>
1620 l_attached_doc_pre_upd.attached_document_id
1621 ,x_document_id => l_doc_pre_upd.document_id
1622 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1623 ,x_entity_name => l_attached_doc_pre_upd.entity_name
1624 ,x_column1 => l_attached_doc_pre_upd.column1
1625 ,x_pk1_value => l_attached_doc_pre_upd.pk1_value
1626 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1627 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1628 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1629 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1630 ,x_automatically_added_flag =>
1631 l_attached_doc_pre_upd.automatically_added_flag
1632 ,x_attribute_category =>
1633 l_attached_doc_pre_upd.attribute_category
1634 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1635 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1636 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1637 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1638 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1639 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1640 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1641 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1642 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1643 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1644 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1645 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1646 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1647 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1648 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1649 ,x_datatype_id => l_doc_pre_upd.datatype_id
1650 ,x_category_id => l_doc_pre_upd.category_id
1651 ,x_security_type => l_doc_pre_upd.security_type
1652 ,x_security_id => l_doc_pre_upd.security_id
1653 ,x_publish_flag => l_doc_pre_upd.publish_flag
1654 ,x_image_type => l_doc_pre_upd.image_type
1655 ,x_storage_type => l_doc_pre_upd.storage_type
1656 ,x_usage_type => l_doc_pre_upd.usage_type
1657 ,x_start_date_active => l_doc_pre_upd.start_date_active
1658 ,x_end_date_active => l_doc_pre_upd.end_date_active
1659 ,x_language => l_doc_tl_pre_upd.language
1660 ,x_description => l_doc_tl_pre_upd.description
1661 ,x_file_name => l_doc_tl_pre_upd.file_name
1662 ,x_media_id => l_doc_tl_pre_upd.media_id
1663 ,x_doc_attribute_category =>
1664 l_doc_tl_pre_upd.doc_attribute_category
1665 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1666 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1667 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1668 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1669 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1670 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1671 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1672 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1673 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1674 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1675 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1676 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1677 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1678 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1679 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1680
1681
1682 -- Update document to fnd_attached_documents, fnd_documents,
1683 -- fnd_documents_tl and fnd_documents_long_text
1684 --
1685 fnd_attached_documents_pkg.update_row
1686 (x_rowid => p_rowid
1687 ,x_attached_document_id =>
1688 l_attached_doc_pre_upd.attached_document_id
1689 ,x_document_id => l_doc_pre_upd.document_id
1690 ,x_last_update_date => trunc(sysdate)
1691 ,x_last_updated_by => p_login_person_id --10/14/97chg
1692 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1693 ,x_entity_name => p_entity_name
1694 ,x_column1 => 'PERSON_ID'
1695 ,x_pk1_value => p_pk1_value
1696 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1697 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1698 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1699 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1700 ,x_automatically_added_flag =>
1701 l_attached_doc_pre_upd.automatically_added_flag
1702 ,x_attribute_category =>
1703 l_attached_doc_pre_upd.attribute_category
1704 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1705 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1706 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1707 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1708 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1709 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1710 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1711 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1712 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1713 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1714 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1715 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1716 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1717 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1718 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1719 /* columns necessary for creating a document on the fly */
1720 ,x_datatype_id => l_doc_pre_upd.datatype_id
1721 ,x_category_id => l_doc_pre_upd.category_id
1722 ,x_security_type => l_doc_pre_upd.security_type
1723 ,x_security_id => l_doc_pre_upd.security_id
1724 ,x_publish_flag => l_doc_pre_upd.publish_flag
1725 ,x_image_type => l_doc_pre_upd.image_type
1726 ,x_storage_type => l_doc_pre_upd.storage_type
1727 ,x_usage_type => l_doc_pre_upd.usage_type
1728 ,x_start_date_active => trunc(sysdate)
1729 ,x_end_date_active => l_doc_pre_upd.end_date_active
1730 ,x_language => l_language
1731 ,x_description => l_doc_tl_pre_upd.description
1732 ,x_file_name => l_doc_tl_pre_upd.file_name
1733 ,x_media_id => l_doc_tl_pre_upd.media_id
1734 ,x_doc_attribute_category =>
1735 l_doc_tl_pre_upd.doc_attribute_category
1736 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1737 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1738 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1739 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1740 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1741 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1742 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1743 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1744 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1745 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1746 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1747 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1748 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1749 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1750 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15);
1751
1752 -- remove chr(13)
1753 l_attachment_text := replace(p_attachment_text,
1754 g_carriage_return||g_line_feed,g_line_feed);
1755 -- Now update the long text table
1756 update fnd_documents_long_text
1757 set long_text = l_attachment_text
1758 where media_id = l_doc_tl_pre_upd.media_id;
1759 hr_utility.set_location('Leaving: '|| l_proc,25);
1760 EXCEPTION
1761 when others then
1762 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1763 raise;
1764 --
1765 End update_attachment;
1766
1767 procedure get_attachment_v4
1768 (p_attachment_text out nocopy CLOB -- Bug#13375642
1769 ,p_entity_name in varchar2 default null
1770 ,p_pk1_value in varchar2 default null
1771 ,p_effective_date in date
1772 ,p_name in fnd_document_categories_tl.name%TYPE
1773 ,p_rowid out nocopy varchar2
1774 ) is
1775
1776 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE;
1777 l_document_id fnd_documents.document_id%TYPE;
1778 l_media_id fnd_documents_tl.media_id%TYPE;
1779 l_category_id fnd_documents.category_id%type;
1780 l_seq_num fnd_attached_documents.seq_num%type;
1781 l_creation_date fnd_documents_tl.creation_date%type;
1782 l_proc constant varchar2(100) := g_package || ' get_attachment_v4';
1783 begin
1784 hr_utility.set_location('Entering: '|| l_proc,5);
1785 get_attachment
1786 (p_attachment_text => p_attachment_text
1787 ,p_entity_name => p_entity_name
1788 ,p_pk1_value => p_pk1_value
1789 ,p_effective_date => to_char(nvl(p_effective_date, trunc(sysdate)))
1790 ,p_attached_document_id => l_attached_document_id
1791 ,p_document_id => l_document_id
1792 ,p_media_id => l_media_id
1793 ,p_rowid => p_rowid
1794 ,p_category_id => l_category_id
1795 ,p_seq_num => l_seq_num
1796 ,p_creation_date => l_creation_date
1797 ,p_user_name => p_name);
1798 hr_utility.set_location('Leaving: '|| l_proc,10);
1799
1800 exception
1801 when others then
1802 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1803 p_attachment_text := null;
1804 p_rowid := null;
1805
1806 end get_attachment_v4;
1807
1808 -- ----------------------------------------------------------------------------
1809 -- |--------------------------< get_attachment >------------------------------|
1810 -- ----------------------------------------------------------------------------
1811 --
1812 -- Duplicated from hrcustwf.pkb (hr_offer_custom), modified to get
1813 -- creation_date
1814
1815 procedure get_attachment
1816 (p_attachment_text out nocopy CLOB -- Bug#13375642
1817 ,p_entity_name in varchar2 default null
1818 ,p_pk1_value in varchar2 default null
1819 ,p_effective_date in varchar2
1820 ,p_attached_document_id out
1821 fnd_attached_documents.attached_document_id%TYPE
1822 ,p_document_id out nocopy fnd_documents.document_id%TYPE
1823 ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
1824 ,p_rowid out nocopy varchar2
1825 ,p_category_id out nocopy fnd_documents.category_id%type
1826 ,p_seq_num out nocopy fnd_attached_documents.seq_num%type
1827 ,p_creation_date out nocopy fnd_documents_tl.creation_date%type
1828 ,p_user_name in
1829 fnd_document_categories_tl.user_name%TYPE
1830 DEFAULT 'HR_RESUME'
1831 ) is
1832
1833 -- [CUSTOMIZE]
1834 -- Call fnd_attached_documents, fnd_documents_tl and
1835 -- fnd_documents_long_text tables. If customer uses third party
1836 -- software to store the resumes, modify the code here.
1837
1838 l_attached_document_id fnd_attached_documents.attached_document_id%TYPE
1839 default null;
1840 l_document_id fnd_documents.document_id%TYPE default null;
1841 l_media_id fnd_documents_tl.media_id%TYPE default null;
1842 l_attachment_text CLOB default null; -- Bug#13375642
1843 l_rowid varchar2(50) default null;
1844 l_category_id fnd_documents.category_id%type default null;
1845 l_language varchar2(30) default 'AMERICAN';
1846 l_seq_num fnd_attached_documents.seq_num%type default null;
1847 l_update_date date default null;
1848 l_creation_date fnd_documents_tl.creation_date%TYPE default null;
1849 l_proc constant varchar2(100) := g_package || ' get_attachment';
1850 cursor csr_get_category_id (csr_p_lang in varchar2) is
1851 select category_id
1852 from fnd_document_categories_tl
1853 where language = csr_p_lang
1854 and name = p_user_name;
1855
1856 cursor csr_attached_documents (csr_p_cat_id in number) is
1857 select fatd.rowid, fatd.attached_document_id, fatd.document_id, fatd.seq_num
1858 ,fd.last_update_date
1859 from fnd_attached_documents fatd
1860 ,fnd_documents fd
1861 where fd.category_id = csr_p_cat_id
1862 and fatd.entity_name = p_entity_name
1863 and fatd.pk1_value = p_pk1_value
1864 and fatd.document_id = fd.document_id
1865 and p_effective_date
1866 between nvl(fd.start_date_active, trunc(sysdate))
1867 and nvl(fd.end_date_active, hr_api.g_eot)
1868 order by fd.last_update_date desc,
1869 fd.document_id desc; -- retrieve the one updated the last
1870
1871 cursor csr_documents_tl (csr_p_document_id in number) is
1872 select media_id , creation_date
1873 from fnd_documents_tl
1874 where document_id = csr_p_document_id
1875 and media_id is not null;
1876
1877 cursor csr_documents_long_text (csr_p_media_id in number) is
1878 select long_text
1879 from fnd_documents_long_text
1880 where media_id = csr_p_media_id;
1881
1882 Begin
1883 hr_utility.set_location('Entering: '|| l_proc,5);
1884 --
1885 -- Get language
1886 select userenv('LANG') into l_language from dual;
1887 --
1888 -- -------------------------------------------------------------------------
1889 -- Retrieving a resume requires 4 steps:
1890 -- 1) Get Category ID.
1891 -- 2) Get the attached_document_id, document_id and other fields from
1892 -- the table join of fnd_attached_documents and fnd_documents. The
1893 -- result set can have more than 1 row and is sorted by descending
1894 -- order of the last_update_date. So, if there are multipe resumes
1895 -- returned (which could be possible because a user in 10SC Person
1896 -- form can add an attachment with the category of 'Resume'. When
1897 -- that happens, we only want the one which is updated most recently.
1898 -- 3) Use the document_id obtained from the 1st record of step 2 to
1899 -- get the media_id from fnd_documents_tl.
1900 -- 4) Use the media_id from step 3 to obtain the resume text from
1901 -- fnd_documents_long_text.
1902 -- -------------------------------------------------------------------------
1903 --
1904 -- -------------------------------------------------------------------------
1905 -- 1) Get Category ID.
1906 -- -------------------------------------------------------------------------
1907 open csr_get_category_id (csr_p_lang => l_language);
1908 hr_utility.trace('Going into Fetch after (open csr_get_category_id (csr_p_lang => l_language) ): '|| l_proc);
1909 fetch csr_get_category_id into l_category_id;
1910 if csr_get_category_id%notfound then
1911 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1912 hr_utility.raise_error;
1913 end if;
1914 close csr_get_category_id;
1915 --
1916 -- -------------------------------------------------------------------------
1917 -- 2) Get attached_document_id, document_id.
1918 -- -------------------------------------------------------------------------
1919 --
1920 Open csr_attached_documents (csr_p_cat_id => l_category_id);
1921 hr_utility.trace('Going into Fetch after (Open csr_attached_documents (csr_p_cat_id => l_category_id)) ): '|| l_proc);
1922 fetch csr_attached_documents into l_rowid, l_attached_document_id,
1923 l_document_id, l_seq_num, l_update_date;
1924
1925 IF csr_attached_documents%NOTFOUND THEN
1926 hr_utility.trace('In( IF csr_attached_documents%NOTFOUND): '|| l_proc);
1927 close csr_attached_documents;
1928 ELSE
1929 hr_utility.trace('In else of ( IF csr_attached_documents%NOTFOUND): '|| l_proc);
1930 open csr_documents_tl(csr_p_document_id => l_document_id);
1931 hr_utility.trace('Going into Fetch after (open csr_documents_tl(csr_p_document_id => l_document_id)): '|| l_proc);
1932 fetch csr_documents_tl into l_media_id , l_creation_date;
1933 IF csr_documents_tl%NOTFOUND THEN
1934
1935 close csr_attached_documents;
1936 close csr_documents_tl;
1937 raise hr_utility.hr_error;
1938 ELSE
1939 open csr_documents_long_text(csr_p_media_id => l_media_id);
1940 hr_utility.trace('Going into Fetch after (open csr_documents_long_text(csr_p_media_id => l_media_id)): '|| l_proc);
1941 fetch csr_documents_long_text into l_attachment_text;
1942 IF csr_documents_long_text%NOTFOUND THEN
1943 close csr_attached_documents;
1944 close csr_documents_tl;
1945 close csr_documents_long_text;
1946 raise hr_utility.hr_error;
1947 ELSE
1948 close csr_attached_documents;
1949 close csr_documents_tl;
1950 close csr_documents_long_text;
1951 END IF;
1952 END IF;
1953 END IF;
1954
1955 p_attachment_text := l_attachment_text;
1956 p_attached_document_id := l_attached_document_id;
1957 p_document_id := l_document_id;
1958 p_media_id := l_media_id;
1959 p_rowid := l_rowid;
1960 p_category_id := l_category_id;
1961 p_seq_num := l_seq_num;
1962 p_creation_date := l_creation_date;
1963 hr_utility.set_location('Leaving: '|| l_proc,35);
1964
1965 exception
1966 when hr_utility.hr_error THEN
1967 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1968 raise;
1969
1970 when others then
1971 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1972 hr_utility.trace(' Exception ' || sqlerrm );
1973 raise;
1974
1975 end get_attachment;
1976
1977
1978 ----------------------------------------------------------------------------
1979 --Fuction string to URL
1980 ----------------------------------------------------------------------------
1981 FUNCTION string_to_url ( p_url in varchar2) return varchar2
1982 /* "percent-sign"
1983 "plus-sign"
1984 "space"
1985 "ampersand"
1986 "question-mark"
1987 ...must be handled specially withing a URL parameter
1988
1989 For eg... (cat + s%) & ?mat
1990
1991 */
1992 IS
1993 v_url varchar2(32000);
1994 l_proc constant varchar2(100) := g_package || ' string_to_url';
1995 BEGIN
1996 hr_utility.set_location('Entering: '|| l_proc,5);
1997 /* The order of the next three "replace" calls matters! */
1998
1999 v_url := replace ( p_url, '%', '%25' );
2000 v_url := replace ( v_url, '+', '%2B' );
2001 v_url := replace ( v_url, ' ', '+' );
2002 v_url := replace ( v_url, '#', '%23');
2003
2004 /* but the order of the next "replace" calls doen't matter */
2005
2006 v_url := replace ( v_url, '&', '%26' );
2007 v_url := replace ( v_url, '?', '%3F' );
2008 hr_utility.set_location('Leaving: '|| l_proc,10);
2009 return v_url;
2010 END string_to_url;
2011
2012
2013 /*-----------------------------------------------------------------------------
2014 |
2015 | Name : isManager
2016 |
2017 | Purpose :
2018 | This functions returns TRUE if the logged in person is in
2019 | LM mode or returns FALSE if the logged in person is in
2020 | Employee Mode
2021 |
2022 | In Parameters :
2023 |
2024 | p_item_type = Workflow Item Type for the Current Process (HRSSA).
2025 | p_item_key = Workflow Item Key for the Current Process.
2026 |
2027 | Returns : BOOLEAN
2028 | TRUE = If it's a manager
2029 | FALSE = If it's a employee.
2030 +-----------------------------------------------------------------------------*/
2031 FUNCTION isManager
2032 (p_item_type IN VARCHAR2
2033 ,p_item_key IN VARCHAR
2034 ) RETURN BOOLEAN IS
2035 l_text_value VARCHAR2(2000);
2036 l_proc constant varchar2(100) := g_package || ' isManager';
2037 BEGIN
2038 hr_utility.set_location('Entering: '|| l_proc,5);
2039
2040
2041 l_text_value :=
2042 wf_engine.getItemAttrText
2043 (itemtype => p_item_type
2044 ,itemkey => p_item_key
2045 ,aname => 'P_PERSON_ID');
2046
2047 IF l_text_value IS NULL THEN
2048 hr_utility.set_location('Leaving: '|| l_proc,10);
2049 -- Since no value is set for P_PERSON_ID, it must be Employee.
2050 RETURN FALSE;
2051 ELSE
2052 -- Since P_PERSON_ID is set, it must be Manager.
2053 hr_utility.set_location('Leaving: '|| l_proc,15);
2054 RETURN TRUE;
2055 END IF;
2056
2057 EXCEPTION
2058 WHEN OTHERS THEN
2059
2060 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2061 RETURN FALSE;
2062 END isManager;
2063 /*------------------------------------------------------------------------------
2064 | Name : isSelfUpdating
2065 | Purpose :
2066 | This functions returns TRUE if the logged in person is same as
2067 | person being updated in LM mode.
2068 | Returns FALSE if the logged in person is different from the
2069 | person being updated.
2070 |
2071 | In Parameters :
2072 |
2073 | p_item_type = Workflow Item Type for the Current Process (HRSSA).
2074 | p_item_key = Workflow Item Key for the Current Process.
2075 |
2076 | Returns : BOOLEAN
2077 | TRUE = If it's a manager updating himself in LMDA mode or
2078 | If a person logged in EDA mode
2079 | FALSE = If it's a employee is updated by his manger in LMDA mode.
2080 +-----------------------------------------------------------------------------*/
2081
2082 FUNCTION isSelfUpdating
2083 (p_item_type IN VARCHAR2
2084 ,p_item_key IN VARCHAR
2085 ) RETURN BOOLEAN IS
2086 l_text_value VARCHAR2(2000);
2087 g_person_id per_all_people_f.person_id%TYPE;
2088 l_proc constant varchar2(100) := g_package || ' isSelfUpdating';
2089 BEGIN
2090 hr_utility.set_location('Entering: '|| l_proc,5);
2091 l_text_value :=
2092 wf_engine.getItemAttrText
2093 (itemtype => p_item_type
2094 ,itemkey => p_item_key
2095 ,aname => 'P_PERSON_ID');
2096
2097 -- get the g_person_id from validate session
2098 hr_util_misc_web.validate_session(p_person_id => g_person_id,p_icx_update => false);
2099
2100 IF l_text_value IS NULL OR to_number(l_text_value) = g_person_id THEN
2101 hr_utility.set_location('Leaving: '|| l_proc,10);
2102 RETURN TRUE; -- Manager updating him/herself or EDA mode
2103 ELSE
2104 hr_utility.set_location('Leaving: '|| l_proc,15);
2105 RETURN FALSE; -- Manager updating his/her employee
2106 END IF;
2107
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2111 RETURN TRUE;
2112 END isSelfUpdating;
2113 /*------------------------------------------------------------------------------
2114 | Name : get_called_from
2115 |
2116 | Purpose :
2117 |
2118 | This function will return the string which appears after
2119 | 'p_called_from=' for line manager direct access menu function's.
2120 | parameters.
2121 |
2122 | This code assumes that hr_person_search_tree_web.setup is taking
2123 | ONLY 1 parameter and which is p_called_from. So, if the definition for
2124 | hr_person_search_tree_web.setup is changed, then this function also
2125 | needs to be modified accordingly.
2126 +-----------------------------------------------------------------------------*/
2127 FUNCTION get_called_from RETURN VARCHAR2 IS
2128
2129 l_fnd_form_function fnd_form_functions%ROWTYPE;
2130 l_function_id fnd_Form_functions.function_id%TYPE;
2131 l_called_from fnd_Form_functions.parameters%TYPE;
2132 l_proc constant varchar2(100) := g_package || ' get_called_from';
2133 CURSOR csr_icx_session(p_session_id IN NUMBER) IS
2134 SELECT a.function_id
2135 FROM icx_sessions a
2136 WHERE session_id = p_session_id;
2137 BEGIN
2138 hr_utility.set_location('Entering: '|| l_proc,5);
2139 -- --------------------------------------------------------
2140 -- Get the Function ID
2141 -- --------------------------------------------------------
2142 OPEN csr_icx_session( icx_sec.getID(n_param => icx_sec.PV_SESSION_ID));
2143 hr_utility.trace('Going into Fetch after (csr_icx_session( icx_sec.getID(n_param => icx_sec.PV_SESSION_ID)) ): '|| l_proc);
2144 FETCH csr_icx_session INTO l_function_id;
2145 CLOSE csr_icx_session;
2146
2147 -- --------------------------------------------------------
2148 -- Get the function information
2149 -- --------------------------------------------------------
2150 l_fnd_form_function := get_fnd_form_function(l_function_id);
2151 l_called_from := SUBSTR(UPPER(l_fnd_form_function.parameters)
2152 ,LENGTH('P_CALLED_FROM=') + 1);
2153 hr_utility.set_location('Leaving: '|| l_proc,15);
2154 RETURN l_called_from;
2155 EXCEPTION
2156 WHEN OTHERS THEN
2157 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2158 RAISE;
2159 END get_called_from;
2160
2161 /*------------------------------------------------------------------------------
2162 | Name : get_fnd_form_function
2163 |
2164 | Purpose :
2165 |
2166 | This function will return all the information needed for any
2167 | fnd_form_function row.
2168 +-----------------------------------------------------------------------------*/
2169 FUNCTION get_fnd_form_function(p_function_id IN NUMBER)
2170 RETURN fnd_form_functions%ROWTYPE IS
2171 CURSOR csr_fnd_form_functions IS
2172 SELECT *
2173 FROM fnd_form_functions
2174 WHERE function_id = p_function_id;
2175 l_function fnd_form_functions%ROWTYPE;
2176 l_proc constant varchar2(100) := g_package || ' get_fnd_form_function';
2177 BEGIN
2178 hr_utility.set_location('Entering: '|| l_proc,5);
2179 OPEN csr_fnd_form_functions;
2180 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_form_functions ): '|| l_proc);
2181 FETCH csr_fnd_form_functions INTO l_function;
2182 CLOSE csr_fnd_form_functions;
2183 hr_utility.set_location('Leaving: '|| l_proc,15);
2184 RETURN l_function;
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2188 RAISE;
2189 END get_fnd_Form_function;
2190
2191
2192 /*------------------------------------------------------------------------------
2193 | Name : get_process_name
2194 |
2195 | Purpose :
2196 |
2197 | This function will return the string which appears after
2198 | 'p_process_name=' in the direct access menu function's.
2199 | parameters.
2200 | Usage :
2201 | This function is to be used when the FND form Function is
2202 | defined as exactly 'P_PROCESS_NAME=YourProcess&P_ITEM_TYPE=...'
2203 | i.e P_PROCESS_NAMEis followed by &P_ITEM_TYPE
2204 +-----------------------------------------------------------------------------*/
2205 FUNCTION get_process_name RETURN VARCHAR2 IS
2206
2207 l_fnd_form_function fnd_form_functions%ROWTYPE;
2208 l_function_id fnd_Form_functions.function_id%TYPE;
2209 l_called_from fnd_Form_functions.parameters%TYPE;
2210 l_proc constant varchar2(100) := g_package || ' get_process_name ';
2211 CURSOR csr_icx_session(p_session_id IN NUMBER) IS
2212 SELECT a.function_id
2213 FROM icx_sessions a
2214 WHERE session_id = p_session_id;
2215 BEGIN
2216 hr_utility.set_location('Entering: '|| l_proc,5);
2217 -- --------------------------------------------------------
2218 -- Get the Function ID
2219 -- --------------------------------------------------------
2220 OPEN csr_icx_session( icx_sec.getID(n_param => icx_sec.PV_SESSION_ID));
2221 hr_utility.trace('Going into Fetch after (OPEN csr_icx_session( icx_sec.getID(n_param => icx_sec.PV_SESSION_ID))): '|| l_proc);
2222 FETCH csr_icx_session INTO l_function_id;
2223 CLOSE csr_icx_session;
2224
2225 -- --------------------------------------------------------
2226 -- Get the function information
2227 -- --------------------------------------------------------
2228 l_fnd_form_function := get_fnd_form_function(l_function_id);
2229 l_called_from := SUBSTR(UPPER(l_fnd_form_function.parameters)
2230 ,LENGTH('P_PROCESS_NAME=') + 1);
2231 l_called_from := SUBSTR(UPPER(l_called_from)
2232 ,1
2233 ,INSTR(UPPER(l_called_from),'&P_ITEM') -1 );
2234 hr_utility.set_location('Leaving: '|| l_proc,15);
2235 RETURN l_called_from;
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2239 RAISE;
2240 END get_process_name;
2241
2242 /*------------------------------------------------------------------------------
2243 | Name : get_item_type
2244 |
2245 | Purpose :
2246 s function will return the string which appears after
2247 | 'p_item_type=' in the direct access menu function's.
2248 | parameters.
2249 | Usage :
2250 | This function is used to get the item_type from FND form Function
2251 +-----------------------------------------------------------------------------*/
2252 FUNCTION get_item_type RETURN VARCHAR2 is
2253
2254 l_fnd_form_function fnd_form_functions%ROWTYPE;
2255 l_function_id fnd_Form_functions.function_id%TYPE;
2256 l_called_from fnd_Form_functions.parameters%TYPE;
2257 l_number integer;
2258 l_proc constant varchar2(100) := g_package || ' get_item_type';
2259 CURSOR csr_icx_session(p_session_id IN NUMBER) IS
2260 SELECT a.function_id
2261 FROM icx_sessions a
2262 WHERE session_id = p_session_id;
2263 BEGIN
2264 hr_utility.set_location('Entering: '|| l_proc,5);
2265
2266 -- --------------------------------------------------------
2267 -- Get the Function ID
2268 -- --------------------------------------------------------
2269 OPEN csr_icx_session( icx_sec.getID(n_param => icx_sec.PV_SESSION_ID));
2270 hr_utility.trace('Going into Fetch after ( OPEN csr_icx_session( icx_sec.getID(n_param => icx_sec.PV_SESSION_ID))): '|| l_proc);
2271 FETCH csr_icx_session INTO l_function_id;
2272 CLOSE csr_icx_session;
2273
2274 -- --------------------------------------------------------
2275 -- Get the function information
2276 -- --------------------------------------------------------
2277 l_fnd_form_function := get_fnd_form_function(l_function_id);
2278 l_called_from := SUBSTR(UPPER(l_fnd_form_function.parameters)
2279 ,INSTR(UPPER(l_fnd_form_function.parameters)
2280 , '&P_ITEM_TYPE=')
2281 );
2282 l_number := INSTR(UPPER(l_called_from), '&P',4) ;
2283
2284 IF l_number <> 0 THEN
2285 l_number := l_number -14;
2286 l_called_from := SUBSTR(UPPER(l_called_from)
2287 ,14
2288 ,l_number );
2289 ELSE
2290 l_called_from := SUBSTR(UPPER(l_called_from)
2291 ,14);
2292 END IF;
2293 hr_utility.set_location('Leaving: '|| l_proc,15);
2294 RETURN l_called_from;
2295 EXCEPTION
2296 WHEN OTHERS THEN
2297 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2298 RAISE;
2299 END get_item_type;
2300
2301 /*
2302 ||===========================================================================
2303 || FUNCTION: get_business_group_id
2304 ||---------------------------------------------------------------------------
2305 ||
2306 || Description:
2307 || If p_person_id is passed, the function call returns
2308 || Business Group ID for the current person. Otherwise,
2309 || the Function call returns the Business Group ID
2310 || for the current session's login responsibility.
2311 || The defaulting levels are as defined in the
2312 || package FND_PROFILE. It returns business group id
2313 || value for a specific user/resp/appl combo.
2314 || Default is user/resp/appl/site is current login.
2315 ||
2316 || Pre Conditions:
2317 ||
2318 || In Arguments:
2319 ||
2320 || out nocopy Arguments:
2321 ||
2322 || In out nocopy Arguments:
2323 ||
2324 || Post Success:
2325 || Returns the business group id.
2326 ||
2327 || Post Failure:
2328 ||
2329 || Access Status:
2330 || Public.
2331 ||
2332 ||===========================================================================
2333 */
2334 FUNCTION get_business_group_id
2335 (p_person_id IN NUMBER DEFAULT NULL)
2336 RETURN per_business_groups.business_group_id%TYPE IS
2337
2338 -- Local Variables.
2339 ln_business_group_id per_business_groups.business_group_id%TYPE;
2340 ln_person_rec per_people_f%ROWTYPE;
2341 l_person_id per_people_f.person_id%type;
2342 l_proc constant varchar2(100) := g_package || ' get_business_group_id';
2343
2344 BEGIN
2345 hr_utility.set_location('Entering: '|| l_proc,5);
2346 IF p_person_id is null then
2347 validate_session(p_person_id => l_person_id
2348 ,p_icx_update => false);
2349 fnd_profile.get (
2350 name => 'PER_BUSINESS_GROUP_ID',
2351 val => ln_business_group_id
2352 );
2353 ELSE
2354 ln_person_rec := get_person_rec(p_effective_date => TRUNC(SYSDATE)
2355 ,p_person_id => p_person_id);
2356 ln_business_group_id := ln_person_rec.business_group_id;
2357 END IF;
2358 hr_utility.set_location('Leaving: '|| l_proc,10);
2359
2360 RETURN (ln_business_group_id);
2361
2362 EXCEPTION
2363 WHEN OTHERS THEN
2364 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2365 htp.p ('Exception in hr_utl'
2366 || g_package || '.get_business_group_id - '
2367 || SQLERRM || ' ' || SQLCODE
2368 );
2369 RETURN NULL;
2370 END get_business_group_id;
2371
2372 /*
2373 ||===========================================================================
2374 || PROCEDURE check_business_group
2375 ||===========================================================================
2376 || Description: This procedure display error page if the passed person is
2377 || not visible through the per_people_f view
2378 ||===========================================================================
2379 */
2380 PROCEDURE check_business_group
2381 (p_person_id IN NUMBER) IS
2382
2383 cursor check_person is
2384 select person_id from per_people_f
2385 where person_id = p_person_id;
2386
2387 l_person_id NUMBER;
2388 l_proc constant varchar2(100) := g_package || ' check_business_group';
2389 invalid_security_permission exception;
2390 BEGIN
2391
2392 hr_utility.set_location('Entering: '|| l_proc,5);
2393 open check_person;
2394 hr_utility.trace('Going into Fetch after (open check_person): '|| l_proc);
2395 fetch check_person into l_person_id;
2396 if check_person%notfound then
2397 close check_person;
2398 fnd_message.set_name('PER', 'HR_INVALID_SECURITY_PERMISSION');
2399 hr_utility.trace(' Exception in hr_util_misc_web.check_businss_group ' || SQLERRM );
2400 raise invalid_security_permission;
2401 end if;
2402 close check_person;
2403 /*
2404 IF get_business_group_id <> get_business_group_id(p_person_id) THEN
2405 fnd_message.set_name('PER', 'HR_WEB_BUS_GROUP_ERR');
2406 hr_util_disp_web.display_fatal_errors
2407 (p_message => fnd_message.get);
2408 END IF;
2409 */
2410
2411 hr_utility.set_location('Leaving: '|| l_proc,15);
2412 EXCEPTION
2413 when invalid_security_permission then
2414 hr_utility.trace(' Exception HR_INVALID_SECURITY_PERMISSION in hr_util_misc_web.check_business_group ' || SQLERRM );
2415 hr_utility.set_location('Leaving: '|| l_proc,15);
2416 raise;
2417 when others then
2418 hr_utility.trace(' Exception in hr_util_misc_web.check_business_group ' || SQLERRM );
2419 hr_utility.set_location('Leaving: '|| l_proc,15);
2420 raise;
2421 END check_business_group;
2422
2423 /*
2424 ||===========================================================================
2425 || PROCEDURE initialize_hr_globals
2426 ||===========================================================================
2427 || Description:
2428 ||===========================================================================
2429 */
2430 PROCEDURE initialize_hr_globals
2431 (p_reset_errors IN VARCHAR2 DEFAULT 'Y') IS
2432 l_proc constant varchar2(100) := g_package || ' initialize_hr_globals';
2433 BEGIN
2434 hr_utility.set_location('Entering: '|| l_proc,5);
2435 IF p_reset_errors = 'Y' THEN
2436 hr_errors_api.g_error := false;
2437 hr_errors_api.g_errorTable.delete;
2438 hr_errors_api.g_count := 0;
2439 END IF;
2440 hr_utility.set_location('Leaving: '|| l_proc,10);
2441 END initialize_hr_globals;
2442
2443 END hr_util_misc_web;
[Home] [Help]