1 PACKAGE hr_util_misc_web AUTHID CURRENT_USER AS
2 /* $Header: hrutlmsw.pkh 120.3 2012/02/03 09:47:25 shpatro ship $ */
3
4 --
5 -- owa is the host concated with the agent:
6 --
7 g_owa varchar2(2000) := null;
8 g_image_dir varchar2(20) := '/OA_MEDIA/';
9 g_html_dir varchar2(20) := '/OA_HTML/';
10 g_static_html_dir varchar2(20) := '/OA_HTML/';
11 g_java_dir varchar2(20) := '/OA_JAVA/';
12 --
13 g_region_application_id constant integer := 601;
14 g_application_id constant integer := 800;
15 g_prompts icx_util.g_prompts_table;
16 g_title varchar(80);
17 --
18 -- Global Dates
19 --
20 g_sysdate_char varchar2(200) := to_char(trunc(sysdate), 'YYYY-MM-DD');
21 g_current_yr_char varchar2(4) := substr(g_sysdate_char, 1, 4);
22 g_sample_date_char varchar2(200) := g_current_yr_char || '-12-31' ;
23 g_sample_date date := to_date(g_sample_date_char, 'YYYY-MM-DD');
24 --When we save the flex date field, we use g_default_date_format so that it
25 --can be viewed in Oracle Application without date convertion error.
26 g_default_date_format varchar2(200) := 'RRRR/MM/DD';
27
28 --
29 -- Variables to mimic the functionality of CHR() functions.
30 --
31 g_null VARCHAR2(10) := CONVERT (
32 fnd_global.local_chr(0),
33 SUBSTR(userenv('LANGUAGE'),
34 INSTR(userenv('LANGUAGE'),'.') +1),
35 'WE8ISO8859P1');
36
37 g_line_feed VARCHAR2(10) := CONVERT (
38 fnd_global.local_chr(10),
39 SUBSTR(userenv('LANGUAGE'),
40 INSTR(userenv('LANGUAGE'),'.') +1),
41 'WE8ISO8859P1');
42
43 g_new_line VARCHAR2(10) := CONVERT (
44 fnd_global.local_chr(10),
45 SUBSTR(userenv('LANGUAGE'),
46 INSTR(userenv('LANGUAGE'),'.') +1),
47 'WE8ISO8859P1');
48
49 g_form_feed VARCHAR2(10) := CONVERT (
50 fnd_global.local_chr(12),
51 SUBSTR(userenv('LANGUAGE'),
52 INSTR(userenv('LANGUAGE'),'.') +1),
53 'WE8ISO8859P1');
54
55 g_carriage_return VARCHAR2(10) := CONVERT (
56 fnd_global.local_chr(13),
57 SUBSTR(userenv('LANGUAGE'),
61 g_space VARCHAR2(10) := CONVERT (
58 INSTR(userenv('LANGUAGE'),'.') +1),
59 'WE8ISO8859P1');
60
62 fnd_global.local_chr(32),
63 SUBSTR(userenv('LANGUAGE'),
64 INSTR(userenv('LANGUAGE'),'.') +1),
65 'WE8ISO8859P1');
66
67 g_ampersand VARCHAR2(10) := CONVERT (
68 fnd_global.local_chr(38),
69 SUBSTR(userenv('LANGUAGE'),
70 INSTR(userenv('LANGUAGE'),'.') +1),
71 'WE8ISO8859P1');
72
73 g_single_quote VARCHAR2(10) := CONVERT (
74 fnd_global.local_chr(39),
75 SUBSTR(userenv('LANGUAGE'),
76 INSTR(userenv('LANGUAGE'),'.') +1),
77 'WE8ISO8859P1');
78
79 g_comma VARCHAR2(10) := CONVERT (
80 fnd_global.local_chr(44),
81 SUBSTR(userenv('LANGUAGE'),
82 INSTR(userenv('LANGUAGE'),'.') +1),
83 'WE8ISO8859P1');
84
85 --
86 -- Record types used in the web forms
87 --
88 TYPE g_varchar2_tab_type IS TABLE OF varchar2(2000) INDEX BY BINARY_INTEGER;
89
90 --RECORD STRUCTURES
91 TYPE g_lookup_values_rec_type
92 is record
93 ( lookup_type VARCHAR2(30)
94 ,lookup_code varchar2(30)
95 ,meaning varchar2(80));
96
97 --Table structure
98 TYPE g_lookup_values_tab_type
99 is table of g_lookup_values_rec_type
100 INDEX BY BINARY_INTEGER;
101 --
102 -- Default Varchar2 PL/SQL Table
103 --
104 g_varchar2_tab_default g_varchar2_tab_type;
105 --
106 -- Declare an empty table for initialization
107 g_lookup_values_tab_default g_lookup_values_tab_type;
108 --
109 --
110 -- EXCEPTIONS
111 -- Use the following exceptions for checking date lookup code
112 g_invalid_time_period exception;
113 g_invalid_time_length exception;
114 g_invalid_time_unit exception;
115 --
116 -- Use g_date_error to check if such an error is detected by the subroutine
117 -- which has not handled issue of the error.
118 g_date_error exception;
119 --
120 -- Use this when the routine raising the error has completely handled
121 -- the situation.
122 g_error_handled exception;
123
124 --
125 -- Used in validate session when the product required to run the selected
126 -- function is not installed.
127 --
128 g_no_app_error exception;
129 --
130 -- use this variable to detect that validate_session has been entered and
131 -- detected a validation error; no need to run validate_session again
132 --
133 g_error_handled_var boolean;
134 -- ------------------------------------------------------------------------
135 -- get_nls_parameter
136 -- ------------------------------------------------------------------------
137 FUNCTION get_nls_parameter(p_parameter in varchar2)
138 RETURN VARCHAR2;
139
140 -- ------------------------------------------------------------------------
141 -- get_group_separator
142 -- ------------------------------------------------------------------------
143 FUNCTION get_group_separator
144 RETURN VARCHAR2;
145
146 -- ------------------------------------------------------------------------
147 -- get_currency_mask
148 -- ------------------------------------------------------------------------
149 FUNCTION get_currency_mask
150 RETURN VARCHAR2;
151
152
153 -- ------------------------------------------------------------------------
154 -- is_valid_number
155 -- ------------------------------------------------------------------------
156 FUNCTION is_valid_number(p_number in varchar2)
157 RETURN BOOLEAN;
158
159 -- ------------------------------------------------------------------------
160 -- is_valid_currency
161 -- ------------------------------------------------------------------------
162 FUNCTION is_valid_currency(p_currency in varchar2)
163 RETURN BOOLEAN;
164
165 -- ------------------------------------------------------------------------
166 -- get_language_code
167 -- ------------------------------------------------------------------------
168
169 FUNCTION get_language_code
170 RETURN varchar2;
171
172 -- ------------------------------------------------------------------------
173 -- get_image_directory
174 -- ------------------------------------------------------------------------
175
176 FUNCTION get_image_directory
177 RETURN varchar2;
178
179 -- ------------------------------------------------------------------------
180 -- get_calendar_file
181 -- ------------------------------------------------------------------------
182
183 FUNCTION get_calendar_file
184 RETURN varchar2;
185
186 -- ------------------------------------------------------------------------
187 -- get_html_directory
188 -- ------------------------------------------------------------------------
189
190 FUNCTION get_html_directory
191 RETURN varchar2;
192
193 -- ------------------------------------------------------------------------
194 -- get_person_rec
195 -- ------------------------------------------------------------------------
196
197 FUNCTION get_person_rec(p_effective_date in varchar2
198 ,p_person_id in number)
199 RETURN per_people_f%ROWTYPE;
200
204 -- Purpose: This function can be called to return the message text which
201 -- ------------------------------------------------------------------------
202 -- return_msg_text
203 --
205 -- can then be used for display in javascript alert or confirm box.
206 -- ------------------------------------------------------------------------
207 FUNCTION return_msg_text(p_message_name IN VARCHAR2
208 ,p_application_id IN VARCHAR2 DEFAULT 'PER')
209 RETURN VARCHAR2;
210 -- ------------------------------------------------------------------------
211 -- |----------------------< get_user_date_format>-------------------------|
212 -- ------------------------------------------------------------------------
213 function get_user_date_format
214 return varchar2;
215 --
216 -- ---------------------------------------------------------------------------
217 -- ------------------------ <build_date2char_expression>----------------------
218 -- ---------------------------------------------------------------------------
219 Function build_date2char_expression(p_date in date
220 ,p_date_format in varchar2)
221 return varchar2;
222 --
223 -- ---------------------------------------------------------------------------
224 -- ------------------------ <validate_date_lookup_code> ----------------------
225 -- ---------------------------------------------------------------------------
226 Function validate_date_lookup_code
227 (p_lookup_type in varchar2
228 ,p_effective_date in date default trunc(sysdate))
229 return hr_util_misc_web.g_lookup_values_tab_type;
230 --
231 --
232 -- ------------------------------------------------------------------------
233 -- insert_session_row
234 --
235 -- Description:
236 -- This procedure insert a record into the fnd_sessions table so that we
237 -- may select data from date-tracked tables. It's over-loaded to accept a
238 -- date field or a varchar2 encrypted date. It also checks the user's
239 -- security
240 -- ------------------------------------------------------------------------
241
242 PROCEDURE insert_session_row(p_effective_date in date default sysdate);
243
244 -- ------------------------------------------------------------------------
245 -- autonomous_commit_fnd_sess_row
246 --
247 -- Description:
248 -- This procedure inserts a record into the fnd_sessions table so that we
249 -- may select data from date-tracked tables. It commits the insert
250 -- by using an autonomous transaction.
251 -- This explicit commit is necessary because in V4 tech stack,
252 -- whenever there is an error or warning, FWK will issue a JDBC
253 -- JDBC rollback. Thus any fnd_session row inserted before the error
254 -- was issued will be rolled back.
255 -- This will create a problem, e.g. in Work Schedule, the Time Card
256 -- Approver segment uses a value set which points to per_all_people
257 -- view. That view uses fnd_session.effective_date. Thus, if we
258 -- don't commit this fnd_session row, on the second pass of submit
259 -- after an error has either been corrected or a warning has been
260 -- acknowledged, you will get an "invalid value" error from flex
261 -- field because fnd_session row is rolled back. Thus, we need to use
262 -- an autonomous transaction to commit the insert.
263 --
264 -- Updated for bug 1940440
265 -- ------------------------------------------------------------------------
266 PROCEDURE autonomous_commit_fnd_sess_row
267 (p_effective_date in date
268 ,p_session_id out nocopy number);
269
270
271 PROCEDURE remove_session_row;
272
273 -- ------------------------------------------------------------------------
274 -- validate_session
275 --
276 -- Description:
277 -- This procedure calls the Internet Commerce security routine that check
278 -- that the user has a full, appropriate 'cookie' for their web session.
279 -- It also obtains the Person_Id of the user.
280 -- ------------------------------------------------------------------------
281
282 PROCEDURE validate_session(p_person_id out nocopy number
283 ,p_check_ota in varchar2 default 'N'
284 ,p_check_ben in varchar2 default 'N'
285 ,p_check_pay in varchar2 default 'N'
286 ,p_icx_update in boolean default true
287 ,p_icx_commit in boolean default false);
288
289 PROCEDURE validate_session(p_person_id out nocopy number
290 ,p_web_username out nocopy varchar2
291 ,p_check_ota in varchar2 default 'N'
292 ,p_check_ben in varchar2 default 'N'
293 ,p_check_pay in varchar2 default 'N'
294 ,p_icx_update in boolean default true
295 ,p_icx_commit in boolean default false);
296
297 -- ------------------------------------------------------------------------
298 -- prepare_parameter
299 --
300 -- Description:
301 -- This procedure takes in a parameter and makes it URL ready by changing
302 -- spaces to '+' and placing a '&' at the front of the parmameter name
303 -- when p_prefix is true (the parameter is not first in the list).
304 -- ------------------------------------------------------------------------
305
306 FUNCTION prepare_parameter(p_name in varchar2
307 ,p_value in varchar2
308 ,p_prefix in boolean default true)
309 RETURN varchar2;
313
310 FUNCTION get_complete_url(p_url IN VARCHAR2 DEFAULT NULL) RETURN LONG;
311 --
312 FUNCTION get_owa_url RETURN VARCHAR2;
314 -- ------------------------------------------------------------------------
315 -- get_resume
316 -- ------------------------------------------------------------------------
317 -- get_resume procedure used in apply for job and Professional info modules
318
319 procedure get_resume(
320 p_person_id IN NUMBER DEFAULT NULL
321 ,p_resume out nocopy varchar2
322 ,p_rowid out nocopy varchar2
323 ,p_creation_date out nocopy varchar2);
324
325
326 procedure insert_attachment_v4
327 (p_attachment_text in CLOB default null -- Bug#13375642
328 ,p_entity_name in varchar2 default null
329 ,p_pk1_value in varchar2 default null
330 ,p_name in fnd_document_categories_tl.name%TYPE
331 ,p_rowid out nocopy varchar2
332 ,p_login_person_id in number);
333 -- ---------------------------------------------------------------------------
334 -- |--------------------------< insert_attachment >---------------------------
335 -- ---------------------------------------------------------------------------
336 procedure insert_attachment
337 (p_attachment_text in CLOB default null -- Bug#13375642
338 ,p_entity_name in varchar2 default null
339 ,p_pk1_value in varchar2 default null
340 ,p_name in fnd_document_categories_tl.name%TYPE
341 default 'HR_RESUME'
342 ,p_attached_document_id out
343 fnd_attached_documents.attached_document_id%TYPE
344 ,p_document_id out nocopy fnd_documents.document_id%TYPE
345 ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
346 ,p_rowid out nocopy varchar2
347 ,p_login_person_id in number); -- 10/14/97 Changed
348
349 -- ---------------------------------------------------------------------------
350 -- |--------------------------< update_attachment >---------------------------
351 -- ---------------------------------------------------------------------------
352 procedure update_attachment
353 (p_attachment_text in CLOB default null -- Bug#13375642
354 ,p_entity_name in varchar2 default null
355 ,p_pk1_value in varchar2 default null
356 ,p_rowid in varchar2
357 ,p_login_person_id in number); -- 10/14/97 Changed
358
359 -- ----------------------------------------------------------------------------
360 -- |--------------------------< get_attachment >-------------------------------|
361 -- ----------------------------------------------------------------------------
362 --
363 procedure get_attachment
364 (p_attachment_text out nocopy CLOB -- Bug#13375642
365 ,p_entity_name in varchar2 default null
366 ,p_pk1_value in varchar2 default null
367 ,p_effective_date in varchar2
368 ,p_attached_document_id out
369 fnd_attached_documents.attached_document_id%TYPE
370 ,p_document_id out nocopy fnd_documents.document_id%TYPE
371 ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
372 ,p_rowid out nocopy varchar2
373 ,p_category_id out nocopy fnd_documents.category_id%type
374 ,p_seq_num out nocopy fnd_attached_documents.seq_num%type
375 ,p_creation_date out nocopy fnd_documents_tl.creation_date%type
376 ,p_user_name in fnd_document_categories_tl.user_name%TYPE
377 DEFAULT 'HR_RESUME'
378 );
379
380 procedure get_attachment_v4
381 (p_attachment_text out nocopy CLOB -- Bug#13375642
382 ,p_entity_name in varchar2 default null
383 ,p_pk1_value in varchar2 default null
384 ,p_effective_date in date
385 ,p_name in fnd_document_categories_tl.name%TYPE
386 ,p_rowid out nocopy varchar2
387 );
388
389 ----------------------------------------------------------------------------
390 -- Fuction string to URL
391 ----------------------------------------------------------------------------
392 FUNCTION string_to_url ( p_url in varchar2) return varchar2;
393 FUNCTION isManager
394 (p_item_type IN VARCHAR2
395 ,p_item_key IN VARCHAR
396 ) RETURN BOOLEAN;
397
398 /*------------------------------------------------------------------------------
399 | Name : isSelfUpdating
400 | Purpose :
401 |
402 | Returns TRUE if the login person is same as the person being updated.
403 | FALSE, if the login person is different from person being updated.
404 +-----------------------------------------------------------------------------*/
405
406 FUNCTION isSelfUpdating
407 (p_item_type IN VARCHAR2
408 ,p_item_key IN VARCHAR
409 ) RETURN BOOLEAN;
410 FUNCTION get_called_from RETURN VARCHAR2;
411 FUNCTION get_fnd_form_function(p_function_id IN NUMBER)
412 RETURN fnd_form_functions%ROWTYPE;
413
414 /*------------------------------------------------------------------------------
415 | Name : get_process_name
416 |
417 | Purpose :
418 |
419 | This function will return the string which appears after
420 | 'p_process_name=' in the direct access menu function's.
421 | parameters.
422 | Usage :
423 | This function is to be used when the FND form Function is
424 | defined as exactly 'P_PROCESS_NAME=YourProcess&P_ITEM_TYPE=...'
425 | i.e P_PROCESS_NAMEis followed by &P_ITEM_TYPE
426 +-----------------------------------------------------------------------------*/
427 FUNCTION get_process_name RETURN VARCHAR2;
428
429 /*------------------------------------------------------------------------------
430 | Name : get_item_type
431 |
432 | Purpose :
433 |
434 | This function will only return the string which appears after
435 | 'p_item_type=' in the direct access menu function's.
436 | parameters.
437 | Usage :
438 | This function is used to get the item_type from FND form Function
439 +-----------------------------------------------------------------------------*/
440 FUNCTION get_item_type RETURN VARCHAR2;
441 /*
442 ||===========================================================================
443 || FUNCTION: get_business_group_id
444 ||---------------------------------------------------------------------------
445 ||
446 || Description:
447 || If p_person_id is passed, the function call returns
448 || Business Group ID for the current person. Otherwise,
449 || the Function call returns the Business Group ID
450 || for the current session's login responsibility.
451 || The defaulting levels are as defined in the
452 || package FND_PROFILE. It returns business group id
453 || value for a specific user/resp/appl combo.
454 || Default is user/resp/appl/site is current login.
455 ||
456 || Pre Conditions:
457 ||
458 || In Arguments:
459 ||
460 || out nocopy Arguments:
461 ||
462 || In out nocopy Arguments:
463 ||
464 || Post Success:
465 || Returns the business group id.
466 ||
467 || Post Failure:
468 ||
469 || Access Status:
470 || Public.
471 ||
472 ||===========================================================================
473 */
474
475 FUNCTION get_business_group_id
476 (p_person_id IN NUMBER DEFAULT NULL)
477 RETURN per_business_groups.business_group_id%TYPE;
478
479 /*
480 ||===========================================================================
481 || PROCEDURE check_business_group
482 ||===========================================================================
483 || Description: This procedure display error page if the passed person's
484 || business group is not same as the responsibility's
485 || business group.
486 ||===========================================================================
487 */
488 PROCEDURE check_business_group
489 (p_person_id IN NUMBER);
490
491 /*
492 ||===========================================================================
493 || PROCEDURE initialize_hr_globals
494 ||===========================================================================
495 || Description:
496 ||===========================================================================
497 */
498 PROCEDURE initialize_hr_globals(p_reset_errors IN VARCHAR2 DEFAULT 'Y');
499
500
501 -- ---------------------------------------------------------------------------
502 -- ------------------------ <decode_date_lookup_code> ------------------------
503 -- ---------------------------------------------------------------------------
504 Function decode_date_lookup_code
505 (p_date_compare_to_column in varchar2 default null
506 ,p_date_lookup_code in varchar2
507 ,p_effective_date in date default trunc(sysdate))
508 return varchar2;
509
510
511 END hr_util_misc_web;
[Home] [Help]