1 package body HR_GENERAL as
2 /* $Header: hrgenral.pkb 120.3.12010000.2 2008/10/07 08:21:44 nerao ship $ */
3 ------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1994 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name
11 General HR utilities
12 Purpose
13 To provide widely used functions in a single shared area
14 History
15 1 Mar 94 N Simpson Created
16 2 Mar 94 PK Attwood Added init_forms procedure
17 4 Mar 94 N Simpson Added constants and functions to
18 return them
19 27 Apr 94 N Simpson Added function LOCATION_VALID
20 27 Apr 94 N Simpson Corrected location_valid datatypes
21 11 May 94 PK Attwood Added extra exception condition to
22 init_forms procedure.
23 05 Oct 94 R Fine Changed call from dtfndate to dt_fndate
24 as package has been renamed
25 17 Oct 94 N Simpson Modified decode_lookup to avoid
26 cursor fetch if parameters are null
27 24 Nov 94 R Fine Suppressed index on business_group_id
28 16 Jan 95 N Simpson Added overloaded default_currency_code
29 using business group
30 15 Mar 95 R Fine Allowed a session date to be passed in
31 to init_forms. Now that we have to put
32 a row in fnd_sessions every time we
33 open a form, the initial value is not
34 always sysdate.
35 18 Apr 95 N Simpson Added check_HR_version
36 23 Jun 95 D Kerr Added bg_name and bg_legislation_code
37 to init_forms together with temp.
38 overloaded version.
39 4 Sep 95 N Simpson Changed error message in
40 default_currency_code.
41 11 Mar 96 A Forte Added functions DECODE_GRADE to
42 DECODE_ASS_STATUS_TYPE called from
43 peasg03v.sql to eliminate the need
44 for outer joins and enhance the
45 performance of the Assignments folder
46 PERWSFAS. Bug 343096.
47 16 Apr 96 D Kerr Bug 358870
48 Ensure that ses_yesterday is maintained
49 correctly in init_forms.
50 13 Jun 96 A.Mills Added function GET_SALARY used by the
51 view per_expanded_assignments_v1, to
52 speed performance of PERWILAS. Bug
53 336409. Also added sessionid check to
54 DECODE_PAYROLL.
55 09 DEC 96 Ty Hayden Added function GET_WORK_PHONE and
56 GET_HOME_PHONE
57 12 DEC 96 Ty Hayden Modified GET_WORK_PHONE and
58 GET_HOME_PHONE to be more efficient.
59 22 DEC 96 D. Kerr init_forms:removed unncessary overload
60 Removed p_commit and added
61 p_enable_hr_trace
62 02 APR 1997 HPATEL Added function CHK_APPLICATION_ID,
63 CORE_HR_APP_ID and VERTICAL_APP_ID
64 14 APR 1997 HPATEL Added calls CORE_HR_APP_ID and
65 VERTICAL_APP_ID to CHK_APPLICATION_ID
66
67 Name Date Versn Bug Text
68 ========================================================================
69 rfine 29-Apr-97 40.29 n/a Leapfrogged from 40.27 over special
70 p15.1 version. Only change is this
71 comment in change history.
72 dkerr 27-Jul-97 110.3 n/a Added :
73 char_to_bool
74 bool_to_char
75 get_application_short_name
76 hrms_object
77 dkerr 29-Jul-97 110.3 n/a Added PE_ to HRMS_OBJECT
78 dkerr 08-AUG-97 110.4 n/a Added OTV_ to HRMS_OBJECT
79 Added EFFECTIVE_DATE function.
80 Iharding Changed get_work_phone so that
81 PER_PEOPLE is not searched.
82 Sxshah 19 Aug 97 Banner now on eack line.
83 mhoyes 27-AUG-97 110.5 n/a Added chk_geocodes_installed.
84 iharding 10-SEP-97 110.8 495719 Suppressed index on PER_PAY_BASES
85 within function GET_SALARY
86 dkerr 20-OCT-97 110.9 n/a Added get_business_group_id
87 mshah 17-NOV-97 110.10 n/a Added proc set_calling_context
88 and function get_calling_context
89 and added call to set_calling_context
90 to proc init_forms, setting it to
91 'FORMS'
92 fychu 16-Dec-97 110.11 n/a Added get_phone_number function.
93 dkerr 20-MAR-98 40.32 643828 Added DECODE_PEOPLE_GROUP
94 ccarter 23-Dec-98 115.2 n/a Added chk_product_installed function
95 dkerr 23-Feb-99 115.3 n/a Added PQH and PQP as prefixes
96 dkerr 23-Mar-99 115.4 n/a hrms_object
97 avergori 23-Apr-99 115.5 n/a substringed c_pay_value to max of 30
98 chars to enable inserts into
99 pay_input_values_f.
100 ccarter 03-Jun-99 115.6 Added get_user_status function
101 ccarter 30-Jun-99 115.7 Change made to get_user_status function
102 skekkar 23-AUG-99 115.8 Added decode_territory ,
103 decode_organization and
104 decode_availability_status functions
105 hsajja 25-AUG-99 115.9 Added DECODE_POSITION_CURRENT_NAME
106 function
107 hsajja 27-AUG-99 115.10 Change made to VERTICAL_APP_ID function
108 to add 8302(PQH) and 8303(PQP)
109 darora 27-Sep-99 115.11 Included functions - DECODE_POSITION_LATEST_NAME,
110 and DECODE_STEP
111
112 rraina 29-Sep-99 115.12 985430 Added functions decode_ar_lookup and
113 hr_lookup_locations
114 hsajja 01-OCT-99 115.13 Added DECODE_LATEST_POSITION_DEF_ID,
115 DECODE_AVAIL_STATUS_START_DATE functions
116 hsajja 04-OCT-99 115.14 Added GET_POSITION_DATE_END function
117 Changed per_all_positions to
118 hr_all_positions in decode_position
119 function
120 hsajja 07-OCT-99 115.15 Added functions DECODE_PERSON_NAME,
121 DECODE_GRADE_RULE.
122 hsajja 12-OCT-99 115.16 Modified DECODE_POSITION_LATEST_NAME,
123 and DECODE_LATEST_POSITION_DEF_ID
124 hsajja 16-OCT-99 115.17 Modified DECODE_AVAIL_STATUS_START_DATE
125 mmillmor 10-Nov-1999 115.18 Added check to init_forms to check that
126 the business group id matches that of
127 the security profile.
128 rraina 10-NOV-1999 115.19 added decode_fnd_comm_lookup for ota views opt
129
130 dkerr 10-NOV-99 115.20 Added p_hr_trace_dest parameter to
131 init_forms
132 cxsimpso 28-DEC-99 115.21 Added get_validation_name function.
133 smcmilla 29-DEC-99 115.22 Added product id 453 to
134 core_hr_app_id function (HRI)
135 mbocutt 03/01/2000 115.23 1125512 Change get_business_group_id. Replace
136 usage of fnd_profile.value_wnps to use
137 fnd_profile.value. This routine writes
138 the profile value to cache thus aiding
139 performance.
140 alogue 14/02/2000 115.24 Utf8 support.
141 hsajja 21/02/2000 115.25 Included function DECODE_SHARED_TYPE
142 alogue 01/03/2000 115.26 Support of change to hr_locations.
143 rvydyana 24/05/2000 115.27 Added new function get_xbg_profile
144 mbocutt 06/06/2000 115.29 Fixed error in function descriptions
145 for get_work/home_phone
146 tclewis 29-feb-2000 115.30 added function maintain_tax_Records.
147 arashid 13-OCT-00 115.22 Added a cover routine for:
148 DBMS_DESCRIBE.DESCRIBE_PROCEDURE to
149 compile an invalid package. The new
150 routine is called: DESCRIBE_PROCEDURE
151 pattwood 16-NOV-2000 115.32 Changed DESCRIBE_PROCEDURE so it
152 only attempts to compile the package
153 header, instead of the header and
154 body. Required by hr_api_user_hooks
155 package changes for deferred
156 compilation changes.
157 stlocke 23-JAN-2001 115.33 Added procedure init-fndload.
158 stlocke 08-FEB-2001 115.34 Procedure init-fndload removed.
159 cnholmes 28-NOV-2001 115.35 Add iRecruitment to HRSM_OBJECT.
160 hsajja 17-JAN-2002 115.36 NLS fix: Changed per_shared_types to
161 per_shared_types_vl in function
162 DECODE_AVAILABILITY_STATUS
163 gsayers 05-FEB-2002 115.37 Added check to prevent '01/01/0001'-1
164 in init_forms.
165 gperry 08-FEB-2002 115.38 Fixed WWBUG 2110218.
166 Added userenv('sessionid') to query.
167 dkerr 13-MAY-2002 115.38 2372279 Added check for null security profile
168 to get_xbg_profile
169 sgoyal 07-JUN-2002 115.41 2406408 Local variable defined as varchar2(60) changed
170 to %type of organization name
171 sgoyal 07-JUN-2002 115.42 Local variable defined as varchar2(60) changed
172 to %type of location,position name
173 hsajja 28-AUG-2002 115.43 Modified DECODE_POSITION_LATEST_NAME,
174 and DECODE_LATEST_POSITION_DEF_ID
175 adhunter 29-AUG-2002 115.46 2534838: cursors in get_user_status need to restrict
176 to current language.
177 GSCC warning: "exit when" clause in loop
178 in DECODE_AVAIL_START_DATE not liked, changed to "if"
179 clause with "exit".
180 adhunter 07-OCT-2002 115.47 2610865 init_forms: remove "+0" from SELECT
181 b.legislation_code clause.
182 dsaxby 04-DEC-2002 115.48 2692195 Nocopy changes.
183 joward 09-DEC-2002 115.49 MLS enabled grade name
184 pmfletch 10-DEC-2002 115.50 Pointed decode_position_latest_name to select
185 from MLS table hr_all_positions_f_tl
186 joward 23-DEC-2002 115.51 MLS enabled job name
187 kjagadee 26-JUN-2003 115.52 2519443 Modified the cursor csr_amend_user_status
188 in function get_user_status, so that the
189 amends in status types will affect only the
190 concerned business group
191 ynegoro 23-JUL-2003 115.53 Added DECODE_GRADE_LADDER
192 njaladi 28-AUG-2003 115.54 2555987 Modified the size of v_meaning in
193 procedure DECODE_ASS_STATUS_TYPE from
194 30 to 80.
195 njaladi 05-SEP-2003 115.55 2555987 Modified the size of v_meaning in
196 procedure DECODE_ASS_STATUS_TYPE from
197 80 to %type.
198 adudekul 29-JAN-2004 115.56 3355231 Modified cursor csr_get_us_city_names in
199 function chk_geocodes_installed.
200 kkoh 31-AUG-2004 115.57 3078158 Modified function CORE_HR_APP_ID to include
201 application_id range of 800 - 859
202 Modified function VERTICAL_APP_ID to include
203 3491930 check on application_id 8403 Oracle Labor Distribution
204 Modified function HRMS_OBJECT to include
205 checks on application_short_name AME and PSP
206 kkoh 01-SEP-2004 115.58 Typo in change history
207 adhunter 06-OCT-2004 115.59 3902208 hr_lookup_locations is erroring when concatenated
208 address segs exceed 600 chars. Added substr
209 to limit returned details.
210 hsajja 10-DEC-2004 115.60 3663875 Changed function
211 DECODE_POSITION_LATEST_NAME
212 hsajja 10-DEC-2004 115.61 3663875 Removed old/previous duplicate function
213 DECODE_POSITION_LATEST_NAME
214 ========================================================================================
215 svittal 30-SEP-2005 120.1 Global Name Format from R12
216 changed decode_person_name
217 -------------------------------------------------------------------------------
218 rnemani 06-OCT-2008 120.3.12000000.2 New predicate "fnd_global.per_security_profile_id =-1" is
219 added to function get_xbg_profile'.
220 ------------------------------------------------------------------------------------------------
221 DO NOT ADD ANY FUTHER PROCEDURES / FUNCTIONS TO THIS FILE!
222
223 IF REQUIRED PLEASE ADD TO HR_GENRAL2 (hrgenrl2.pkh/pkb)
224
225 --------------------------------------------------------------------------------
226 */
227 --
228 -- Invalid package exception returned from DBMS_DESCRIBE.DESCRIBE_PROCEDURE.
229 --
230 invalid_package exception;
231 pragma exception_init(invalid_package, -20003);
232 --
233 c_start_of_time constant date := to_date ('01/01/0001','DD/MM/YYYY');
234 c_end_of_time constant date := to_date ('31/12/4712','DD/MM/YYYY');
235 --
236 -- The length of constant c_pay_value is restricted to 30 char to enable inserts
237 -- into pay_input_values_f and alike. No translation of a Pay Value should
238 -- ever be longer than 30 chars but the "meaning" column of the lookups table
239 -- is length 80, so the following substrb is included as a fail-safe.
240 --
241 c_pay_value constant varchar2(80)
242 := substrb(hr_general.decode_lookup ('NAME_TRANSLATIONS','PAY VALUE')
243 ,1,80);
244 c_money_unit constant varchar2(255)
245 := hr_general.decode_lookup ('UNITS','M');
246 g_dummy number (1); -- dummy variable for select statements
247 g_calling_context varchar2(30); -- global variable used by get_calling_context
248 -- and set_calling_context
249 -- ---------------------------------------------------------------------------
250 -- |----------------------< describe_procedure >-----------------------------|
251 -- ---------------------------------------------------------------------------
252 procedure describe_procedure
253 (object_name in varchar2
254 ,reserved1 in varchar2
255 ,reserved2 in varchar2
256 ,overload out nocopy dbms_describe.number_table
257 ,position out nocopy dbms_describe.number_table
258 ,level out nocopy dbms_describe.number_table
259 ,argument_name out nocopy dbms_describe.varchar2_table
260 ,datatype out nocopy dbms_describe.number_table
261 ,default_value out nocopy dbms_describe.number_table
262 ,in_out out nocopy dbms_describe.number_table
263 ,length out nocopy dbms_describe.number_table
264 ,precision out nocopy dbms_describe.number_table
265 ,scale out nocopy dbms_describe.number_table
266 ,radix out nocopy dbms_describe.number_table
267 ,spare out nocopy dbms_describe.number_table
268 ) is
269 l_package varchar2(128);
270 l_dotpos number;
271 compile boolean := false;
272 l_csr_sql integer;
273 l_rows integer;
274 begin
275 loop
276 begin
277 dbms_describe.describe_procedure
278 (object_name => object_name
279 ,reserved1 => reserved1
280 ,reserved2 => reserved2
281 ,overload => overload
282 ,position => position
283 ,level => level
284 ,argument_name => argument_name
285 ,datatype => datatype
286 ,default_value => default_value
287 ,in_out => in_out
288 ,length => length
289 ,precision => precision
290 ,scale => scale
291 ,radix => radix
292 ,spare => spare
293 );
294 compile := false;
295 exception
296 when invalid_package then
297 --
298 -- Set the compile flag once only.
299 --
300 if not compile then
301 compile := true;
302 --
303 -- Just reraise the exception if the code has already been here.
304 --
305 else
306 raise;
307 end if;
308 when others then
309 raise;
310 end;
311 --
312 -- Attempt to compile the invalid package.
313 --
314 if compile then
315 --
316 -- Avoid excessive looping.
317 --
318 begin
319 l_dotpos := instr(object_name, '.');
320 if l_dotpos > 1 then
321 l_package := substr(object_name, 1, l_dotpos-1);
322 l_csr_sql := dbms_sql.open_cursor;
323 dbms_sql.parse
324 (l_csr_sql
325 ,'ALTER PACKAGE ' || l_package || ' COMPILE SPECIFICATION'
326 ,dbms_sql.native
327 );
328 l_rows := dbms_sql.execute( l_csr_sql );
329 dbms_sql.close_cursor( l_csr_sql );
330 else
331 --
332 -- The name supplied is that of a standalone procedure/function
333 -- or some other odd name.
334 --
335 raise invalid_package;
336 end if;
337 exception
338 when others then
339 if dbms_sql.is_open( l_csr_sql ) then
340 dbms_sql.close_cursor( l_csr_sql );
341 end if;
342 --
343 -- Compilation failed so the package is still invalid.
344 --
345 raise invalid_package;
346 end;
347 --
348 -- DBMS_DESCRIBE.DESCRIBE_PROCEDURE succeeded so exit the loop.
349 --
350 else
351 exit;
352 end if;
353 end loop;
354 end describe_procedure;
355 ------------------------------------------------------------------------------
356 ------------------------------------------------------------------------------
357 -- Name
358 -- char_to_bool
359 -- Purpose
360 -- Converts a boolean char value eg. 'TRUE','FALSE' to the
361 -- the corresponding boolean value.
362 -- Arguments
363 -- p_value Char value to be converted.
364 -- Notes
365 -- Value is not case sensitive.
366 -- If not recognized then null is returned. Should possibly raise an error.
367 --
368 function char_to_bool (p_value in varchar2) return boolean is
369 l_return_value boolean ;
370 begin
371
372 if ( upper(p_value) = 'TRUE' )
373 then
374 l_return_value := true ;
375 elsif ( upper(p_value) = 'FALSE')
376 then
377 l_return_value := false ;
378 else
379 l_return_value := null ;
380 end if ;
381
382 return (l_return_value) ;
383
384 end char_to_bool ;
385 -------------------------------------------------------------------------
386 -- Name
387 -- bool_to_char
388 -- Purpose
389 -- Converts a boolean value to the corresponding character string.
390 -- This is useful for cases where a function is used in a view.
391 -- Arguments
392 -- p_value boolean value to be converted.
393 -- Notes
394 -- The values returned are the strings 'TRUE','FALSE' and null
395 --
396 function bool_to_char (p_value in boolean) return varchar2 is
397 l_return_value varchar2(10) ;
398 begin
399
400 if ( p_value = true )
401 then
402 l_return_value := 'TRUE' ;
403 elsif ( p_value = false )
404 then
405 l_return_value := 'FALSE' ;
406 else
407 l_return_value := null ;
408 end if;
409
410 return (l_return_value) ;
411
412 end bool_to_char ;
413 -------------------------------------------------------------------------
414 --------------------------------------------------------------------------------
415 procedure assert_condition (p_condition in boolean) is
416 --
417 -- Checks that assumptions made within pl/sql code are true. Use to check the
418 -- parameters to a pl/sql function or procedure before processing. If the
419 -- assumption made by a procedure (eg p_parameter is not null) is not true
420 -- then an error is raised to prevent processing from continuing.
421 --
422 begin
423 --
424 if not p_condition
425 then
426 raise value_error;
427 end if;
428 --
429 end assert_condition;
430 --
431 function get_business_group_id return number is
432 --
433 -- If the user has signed on through applications then returns the value
434 -- of the Business Group profile option otherwise returns null.
435 --
436 -- See 1125512 - Replace value_wnps with value due to performance issues
437 -- caused by value_wnps not writing profile option value to
438 -- cache.
439 -- - PROGMA restriction on this function has been removed as a
440 -- result of this change.
441 --
442 -- Note the check that the user has signed on is so that views which
443 -- would normally restrict on business group can be made to retrieve
444 -- all rows when running in sql*plus or other environments. By default
445 -- the Business Group Profile option is defined at site level to be
446 -- the Setup Business group.
447 --
448 begin
449
450 if ( fnd_global.user_id = -1 ) then
451 return(null);
452 else
453 return(fnd_profile.value('PER_BUSINESS_GROUP_ID'));
454 end if;
455
456 end get_business_group_id ;
457 --------------------------------------------------------------------------------
458 function CHK_APPLICATION_ID(p_application_id number) return varchar2 is
459 --
460 -- If the application id is between 800 and 859 or if it is 8301, 8302, 8303 or 8403
461 -- then return True otherwise return False
462 --
463 v_valid_application_id varchar2(10);
464 --
465 Begin
466 --
467 If core_hr_app_id(p_application_id)= 'TRUE' or vertical_app_id(p_application_id)='TRUE'
468 Then
469 v_valid_application_id := 'TRUE';
470 Else
471 v_valid_application_id := 'FALSE';
472 End If;
473 --
474 RETURN v_valid_application_id;
475 End chk_application_id;
476 --------------------------------------------------------------------------------
477 function CORE_HR_APP_ID(p_application_id number) return varchar2 is
478 --
479 -- If the application_id is between 800 and 859 then return True otherwise
480 -- return false
481 --
482 v_valid_application_id varchar2(10);
483 --
484 Begin
485 --
486 If p_application_id between 800 and 859
487 Then
488 v_valid_application_id := 'TRUE';
489 Elsif p_application_id = 453 Then
490 -- HRI application id
491 v_valid_application_id := 'TRUE';
492 Else
493 v_valid_application_id := 'FALSE';
494 End If;
495 --
496 RETURN v_valid_application_id;
497 End core_hr_app_id;
498 --------------------------------------------------------------------------------
499 function VERTICAL_APP_ID(p_application_id number) return varchar2 is
500 --
501 -- If application_id is 8301, 8302, 8303, 8403
502 -- then return True otherwise return false
503 --
504 v_valid_application_id varchar2(10);
505 --
506 Begin
507 --
508 If p_application_id in ( 8301, 8302, 8303, 8403 )
509 Then
510 v_valid_application_id := 'TRUE';
511 Else
512 v_valid_application_id := 'FALSE';
513 End If;
514 --
515 RETURN v_valid_application_id;
516 End vertical_app_id;
517 -------------------------------------------------------------------------
518 -- Name
519 -- hrms_object
520 -- Purpose
521 -- *** INTERNAL HRDEV USE ONLY ***
522 -- Determines whether given object has an HRMS prefix
523 -- Arguments
524 -- p_object_name - The name of the object to be checked
525 -- Notes
526 -- This function does not handle objects with a non-standard
527 -- prefix. Ideally these should be added.
528 --
529 function hrms_object (p_object_name in varchar2) return varchar2 is
530 begin
531
532 return (bool_to_char(
533
534 --
535 -- 'Core products'
536 --
537 --
538 -- Most HR is really PER but may find itself
539 -- in PAY
540 --
541 (instr(p_object_name,'HR') = 1 )
542 OR (instr(p_object_name,'PE_') = 1 )
543 OR (instr(p_object_name,'PER') = 1 )
544
545 OR (instr(p_object_name,'PAY') = 1 )
546 OR (instr(p_object_name,'PY') = 1 )
547
548 -- DT and FF are psuedo-products and required for PER
549 OR (instr(p_object_name,'DT') = 1 )
550 OR (instr(p_object_name,'FF') = 1 )
551
552
553 --
554 -- 'Optional Products'
555 --
556
557 --
558 -- OT is really
559 -- OTA
560 -- OTFV,OTFG - 'Business Views'
561 --
562 OR (instr(p_object_name,'OT') = 1 )
563
564 --
565 -- BEN - Oracle Advanced Benefits
566 --
567 OR (instr(p_object_name,'BEN') = 1 )
568
569 -- HX is really
570 -- HXT - Oracle Time Management
571 -- HXC - Oracle Time Capture
572 OR (instr(p_object_name,'HX') = 1 )
573
574 --
575 -- 'Verticalizations'
576 --
577 OR (instr(p_object_name,'GHR') = 1 )
578 OR (instr(p_object_name,'PQH') = 1 )
579 OR (instr(p_object_name,'PQP') = 1 )
580
581 --
582 -- 'Localizations'
583 --
584
585 OR (instr(p_object_name,'SSP') = 1 )
586
587 --
588 -- 'iRecruitment'
589 --
590
591 OR (instr(p_object_name,'IRC') = 1 )
592
593 --
594 -- AME - Oracle Approvals Management
595 --
596
597 OR (instr(p_object_name,'AME') = 1 )
598
599 --
600 -- PSP - Oracle Labor Distribution
601 --
602
603 OR (instr(p_object_name,'PSP') = 1 )
604
605
606 ));
607
608 end hrms_object;
609 -------------------------------------------------------------------------
610 function get_application_short_name (p_application_id in varchar2) return varchar2 is
611 l_return_value fnd_application.application_short_name%type ;
612 cursor c1 (p_app_id in number ) is
613 select fa.application_short_name
614 from fnd_application fa
615 where fa.application_id = p_app_id ;
616 begin
617
618 -- Special case the most common ones. This is to save a select
619 -- in calls to hr_utility.set_message.
620 if ( p_application_id = 800 )
621 then
622 l_return_value := 'PER' ;
623 elsif ( p_application_id = 801 )
624 then
625 l_return_value := 'PAY' ;
626 elsif ( p_application_id = 802 )
627 then
628 l_return_value := 'FF' ;
629 elsif ( p_application_id = 803 )
630 then
631 l_return_value := 'DT' ;
632 else
633 open c1(p_application_id) ;
634 fetch c1 into l_return_value ;
635 close c1 ;
636 end if;
637
638 return(l_return_value) ;
639
640 end get_application_short_name ;
641 -------------------------------------------------------------------------
642 -------------------------------------------------------------------------------e
643 function LOCATION_VALID (
644 --
645 --***************************************************************
646 --* Returns TRUE if the location is valid on the specified date *
647 --***************************************************************
648 --
649 p_location_id number,
650 p_date date,
651 p_error_if_invalid boolean default TRUE ) return boolean is
652 --
653 cursor csr_location is
654 select 1
655 from hr_locations
656 where location_id = p_location_id
657 and location_use = 'HR'
658 and nvl (inactive_date, c_end_of_time) >= p_date;
659 --
660 v_location_valid boolean;
661 --
662 begin
663 --
664 hr_utility.set_location ('HR_GENERAL.LOCATION_VALID',1);
665 --
666 open csr_location;
667 fetch csr_location into g_dummy;
668 v_location_valid := csr_location%found;
669 close csr_location;
670 --
671 if (not v_location_valid) and p_error_if_invalid then
672 hr_utility.set_message (801, 'HR_7104_LOC_LOCATION_INVALID');
673 hr_utility.raise_error;
674 end if;
675 --
676 return v_location_valid;
677 --
678 end location_valid;
679 -------------------------------------------------------------------------
680 -- Returns the session date if set otherwise trunc('sysdate');
681 -- Ideally this should use a cached variable in the datetrack package
682 --
683 function EFFECTIVE_DATE return date is
684 l_effective_date date ;
685 cursor c1 is
686 select effective_date
687 from fnd_sessions
688 where session_id = userenv('sessionid');
689 begin
690
691 open c1 ;
692 fetch c1 into l_effective_date ;
693 if c1%notfound then l_effective_date := trunc(sysdate) ;
694 end if;
695 close c1 ;
696
697 return (l_effective_date);
698
699 end effective_date ;
700 -------------------------------------------------------------------------
701 function START_OF_TIME return date is
702 begin
703 return c_start_of_time;
704 end start_of_time;
705 -------------------------------------------------------------------------
706 function END_OF_TIME return date is
707 begin
708 return c_end_of_time;
709 end end_of_time;
710 -------------------------------------------------------------------------
711 function PAY_VALUE return varchar2 is
712 begin
713 return c_pay_value;
714 end pay_value;
715 -------------------------------------------------------------------------
716 function MONEY_UNIT return varchar2 is
717 begin
718 return c_money_unit;
719 end money_unit;
720 -------------------------------------------------------------------------
721 function DEFAULT_CURRENCY_CODE (p_legislation_code varchar2) return varchar2 is
722 --
723 --**********************************************************
724 --* Returns the default currency for specified legislation *
725 --**********************************************************
726 --
727 cursor csr_legislation is
728 select rule_mode
729 from pay_legislation_rules
730 where legislation_code = p_legislation_code
731 and rule_type = 'DC';
732 --
733 cursor csr_currency is
734 select currency_code
735 from fnd_currencies CURRENCY,
736 fnd_sessions SESH
737 where currency.enabled_flag = 'Y'
738 and currency.issuing_territory_code = p_legislation_code
739 and sesh.session_id = userenv ('sessionid')
740 and sesh.effective_date between nvl(currency.start_date_active,
741 sesh.effective_date)
742 and nvl(currency.end_date_active,
743 sesh.effective_date);
744 --
745 v_default_currency varchar2(255) := null;
746 --
747 begin
748 hr_utility.set_location ('hr_general.default_currency_code',1);
749 --
750 -- Find the user-specified default currency
751 --
752 open csr_legislation;
753 fetch csr_legislation into v_default_currency;
754 --
755 -- If no default is specified, then find the first currency available
756 --
757 if csr_legislation%notfound then
758 open csr_currency;
759 fetch csr_currency into v_default_currency;
760 --
761 -- If no currency is available, then return an error
762 --
763 if csr_currency%notfound then
764 close csr_currency;
765 hr_utility.set_message(801, 'HR_7989_HR_DEFAULT_CURRENCY');
766 hr_utility.raise_error;
767 else
768 close csr_currency;
769 end if;
770 --
771 end if;
772 --
773 close csr_legislation;
774 return v_default_currency;
775 --
776 end default_currency_code;
777 ---------------------------------------------------------------------------
778 function DEFAULT_CURRENCY_CODE (p_business_group_id number) return varchar2
779 --*****************************************************************************
780 --* Returns the default currency code for the specified business group ID
781 --* NB For data legacy reasons, the default currency for the business group
782 --* is derived differently from that for the legislation. In some cases,
783 --* eg PAYWSDET, both default derivation methods are in use.
784 --*****************************************************************************
785 --
786 is
787 --
788 cursor csr_default_currency is
789 select currency_code
790 from per_business_groups_perf
791 where business_group_id = p_business_group_id;
792 --
793 currency per_business_groups_perf.currency_code%type;
794 --
795 begin
796 --
797 open csr_default_currency;
798 fetch csr_default_currency into currency;
799 close csr_default_currency;
800 --
801 return currency;
802 --
803 end default_currency_code;
804 ---------------------------------------------------------------------------
805 function DECODE_LOOKUP (
806 --******************************************************************************
807 --* Returns the meaning for a lookup code of a specified type.
808 --******************************************************************************
809 --
810 p_lookup_type varchar2,
811 p_lookup_code varchar2) return varchar2 is
812 --
813 cursor csr_lookup is
814 select meaning
815 from hr_lookups
816 where lookup_type = p_lookup_type
817 and lookup_code = p_lookup_code;
818 --
819 v_meaning varchar2(80) := null;
820 --
821 begin
822 --
823 -- Only open the cursor if the parameters are going to retrieve anything
824 --
825 if p_lookup_type is not null and p_lookup_code is not null then
826 --
827 open csr_lookup;
828 fetch csr_lookup into v_meaning;
829 close csr_lookup;
830 --
831 end if;
832 --
833 return v_meaning;
834 --
835 end decode_lookup;
836 ---------------------------------------------------------------------------
837 function DECODE_FND_COMM_LOOKUP (
838 --******************************************************************************
839 --* Returns the meaning for a lookup code of a specified type.
840 --******************************************************************************
841 --
842 p_lookup_type varchar2,
843 p_lookup_code varchar2) return varchar2 is
844 --
845 cursor csr_lookup is
846 select meaning
847 from fnd_common_lookups
848 where lookup_type = p_lookup_type
849 and lookup_code = p_lookup_code
850 and APPLICATION_ID = 800;
851 --
852 v_meaning varchar2(80) := null;
853 --
854 begin
855 --
856 -- Only open the cursor if the parameters are going to retrieve anything
857 --
858 if p_lookup_type is not null and p_lookup_code is not null then
859 --
860 open csr_lookup;
861 fetch csr_lookup into v_meaning;
862 close csr_lookup;
863 --
864 end if;
865 --
866 return v_meaning;
867 --
868 end decode_fnd_comm_lookup;
869
870 ---------------------------------------------------------------------------
871 function DECODE_GRADE (
872
873 --
874 p_grade_id number) return varchar2 is
875 --
876 cursor csr_lookup is
877 select name
878 from per_grades_vl
879 where grade_id = p_grade_id;
880 --
881 v_meaning varchar2(240) := null;
882 --
883 begin
884 --
885 -- Only open the cursor if the parameter is going to retrieve anything
886 --
887 if p_grade_id is not null then
888 --
889 open csr_lookup;
890 fetch csr_lookup into v_meaning;
891 close csr_lookup;
892 --
893 end if;
894 return v_meaning;
895 end decode_grade;
896 ---------------------------------------------------------------------------
897 function DECODE_GRADE_LADDER (
898
899 --
900 p_grade_ladder_pgm_id number) return varchar2 is
901 --
902 cursor csr_lookup is
903 select name
904 from ben_pgm_f pgm
905 ,fnd_sessions s
906 where pgm_id = p_grade_ladder_pgm_id
907 and s.effective_date between
908 pgm.effective_start_date and pgm.effective_end_date
909 and s.session_id = userenv ('sessionid');
910 --
911 v_meaning varchar2(240) := null;
912 --
913 begin
914 --
915 -- Only open the cursor if the parameter is going to retrieve anything
916 --
917 if p_grade_ladder_pgm_id is not null then
918 --
919 open csr_lookup;
920 fetch csr_lookup into v_meaning;
921 close csr_lookup;
922 --
923 end if;
924 return v_meaning;
925 end decode_grade_ladder;
926
927 ---------------------------------------------------------------------------
928 function DECODE_PAYROLL (
929
930 --
931 p_payroll_id number) return varchar2 is
932 --
933 cursor csr_lookup is
934 select payroll_name
935 from pay_all_payrolls_f pay, fnd_sessions f
936 where payroll_id = p_payroll_id
937 and f.effective_date between
938 pay.effective_start_date and pay.effective_end_date
939 and f.session_id = userenv ('sessionid');
940 --
941 v_meaning varchar2(80) := null;
942 --
943 begin
944 --
945 -- Only open the cursor if the parameter is going to retrieve anything
946 --
947 if p_payroll_id is not null then
948 --
949 open csr_lookup;
950 fetch csr_lookup into v_meaning;
951 close csr_lookup;
952 --
953 end if;
954 return v_meaning;
955 end decode_payroll;
956 -----------------------------------------------------------------------
957 function GET_SALARY (
958 --
959 p_pay_basis_id number,
960 p_assignment_id number) return varchar2 is
961 --
962 -- This cursor gets the screen_entry_value from pay_element_entry_values_f.
963 -- This is the salary amount
964 -- obtained when the pay basis isn't null. The pay basis and assignment_id
965 -- are passed in by the view. A check is made on the effective date of
966 -- pay_element_entry_values_f and pay_element_entries_f as they're datetracked.
967 --
968 cursor csr_lookup is
969 select eev.screen_entry_value
970 from pay_element_entry_values_f eev,
971 per_pay_bases ppb,
972 pay_element_entries_f pe,
973 fnd_sessions f
974 where ppb.pay_basis_id +0 = p_pay_basis_id
975 and pe.assignment_id = p_assignment_id
976 and eev.input_value_id = ppb.input_value_id
977 and eev.element_entry_id = pe.element_entry_id
978 and f.effective_date between
979 eev.effective_start_date and eev.effective_end_date
980 and f.effective_date between
981 pe.effective_start_date and pe.effective_end_date
982 and f.session_id = userenv ('sessionid');
983 --
984 v_meaning varchar2(60) := null;
985 begin
986 --
987 -- Only open the cursor if the parameter may retrieve anything
988 -- In practice, p_assignment_id is always going to be non null;
989 -- p_pay_basis_id may be null, though. If it is, don't bother trying
990 -- to fetch a salary.
991 --
992 -- If we do have a pay basis, try and get a salary. There may not be one,
993 -- in which case no problem: just return null.
994 --
995 if p_pay_basis_id is not null and p_assignment_id is not null then
996 open csr_lookup;
997 fetch csr_lookup into v_meaning;
998 close csr_lookup;
999 end if;
1000 --
1001 -- Return the salary value, if this does not exist, return a null value.
1002 --
1003 return v_meaning;
1004 end get_salary;
1005 --
1006 -----------------------------------------------------------------------
1007 function DECODE_JOB (
1008
1009 --
1010 p_job_id number) return varchar2 is
1011 --
1012 cursor csr_lookup is
1013 select name
1014 from per_jobs_vl
1015 where job_id = p_job_id;
1016 --
1017 v_meaning per_jobs.name%TYPE := null;
1018 --
1019 begin
1020 --
1021 -- Only open the cursor if the parameter is going to retrieve anything
1022 --
1023 if p_job_id is not null then
1024 --
1025 open csr_lookup;
1026 fetch csr_lookup into v_meaning;
1027 close csr_lookup;
1028 --
1029 end if;
1030 --
1031 return v_meaning;
1032 end decode_job;
1033 -------------------------------------------------------------------------
1034 function DECODE_POSITION (
1035
1036 --
1037 p_position_id number) return varchar2 is
1038 --
1039 cursor csr_lookup is
1040 select name
1041 from hr_all_positions
1042 where position_id = p_position_id;
1043 --
1044 v_meaning hr_all_positions.name%TYPE := null;
1045 --
1046 begin
1047 --
1048 -- Only open the cursor if the parameter is going to retrieve anything
1049 --
1050 if p_position_id is not null then
1051 --
1052 open csr_lookup;
1053 fetch csr_lookup into v_meaning;
1054 close csr_lookup;
1055 --
1056 end if;
1057 return v_meaning;
1058 end decode_position;
1059 ------------------------------------------------------------------------
1060 function DECODE_LOCATION (
1061
1062 --
1063 p_location_id number) return varchar2 is
1064 --
1065 cursor csr_lookup is
1066 select location_code
1067 from hr_locations
1068 where location_id = p_location_id
1069 and location_use = 'HR';
1070 --
1071 v_meaning hr_locations.location_code%TYPE := null;
1072 --
1073 begin
1074 --
1075 -- Only open the cursor if the parameter is going to retrieve anything
1076 --
1077 if p_location_id is not null then
1078 --
1079 open csr_lookup;
1080 fetch csr_lookup into v_meaning;
1081 close csr_lookup;
1082 --
1083 end if;
1084 return v_meaning;
1085 end decode_location;
1086 -----------------------------------------------------------------------
1087 function DECODE_PAY_BASIS (
1088
1089 --
1090 p_pay_basis_id number) return varchar2 is
1091 --
1092 cursor csr_lookup is
1093 select name
1094 from per_pay_bases
1095 where pay_basis_id = p_pay_basis_id;
1096 --
1097 v_meaning varchar2(30) := null;
1098 --
1099 begin
1100 --
1101 -- Only open the cursor if the parameter is going to retrieve anything
1102 --
1103 if p_pay_basis_id is not null then
1104 --
1105 open csr_lookup;
1106 fetch csr_lookup into v_meaning;
1107 close csr_lookup;
1108 --
1109 end if;
1110 return v_meaning;
1111 end decode_pay_basis;
1112 ------------------------------------------------------------------------
1113 function DECODE_ASS_STATUS_TYPE (
1114
1115 --
1116 p_assignment_status_type_id number,
1117 p_business_group_id number) return varchar2 is
1118 --
1119 cursor csr_lookup is
1120 select user_status
1121 from per_ass_status_type_amends
1122 where assignment_status_type_id = p_assignment_status_type_id
1123 and business_group_id = p_business_group_id;
1124 --
1125 v_meaning per_ass_status_type_amends.user_status%type := null; --#2555987 changed size from 30 to %type.
1126 --
1127 begin
1128 --
1129 -- Only open the cursor if the parameter is going to retrieve anything
1130 --
1131 if p_assignment_status_type_id is not null then
1132 --
1133 open csr_lookup;
1134 fetch csr_lookup into v_meaning;
1135 close csr_lookup;
1136 --
1137 end if;
1138 --
1139 return v_meaning;
1140 end decode_ass_status_type;
1141
1142 --------------------------------------------------------------------------------
1143 -- -------------------------< get_phone_number >--------------------------------
1144 --------------------------------------------------------------------------------
1145 -- This function will return phone number by phone_type which is passed in and
1146 -- by effective_date from the per_phones table. If effective_date is not passed
1147 -- in, it will use the either the session date or sysdate.
1148 --
1149 -- NOTES:
1150 -- This is a generic procedure to get phone number by phone_type. The
1151 -- phone number returned will be as follows:
1152 -- 1) If there are more than 1 records active for a give phone_type within
1153 -- the effective_date passed, return the latest date_from, then date_to.
1154 -- Note that Date_to can be null. We do not use an nvl(date_to, ..)
1155 -- in the order by clause so that the null value date_to record will be
1156 -- sorted first. For example:
1157 -- Phone ID Phone Type Phone Number Date From Date To
1158 -- -------- ---------- ------------- ------------- -------------
1159 -- 1 WF 650-001-0001 01-Dec-97 31-Dec-97
1160 -- 2 WF 650-002-0002 01-Dec-97 <null>
1161 -- 3 WF 650-003-0003 05-Dec-97 16-Dec-97
1162 --
1163 -- IF the effective date = 10-Dec-97, then all 3 records are effective
1164 -- as of 10-Dec-97. With the order by clause coded as "date_from desc,
1165 -- date_to desc", phone_id 2 will be sorted ahead of phone_id 1
1166 -- because of the null value in the date_to field. We want the no
1167 -- expiration date to be retrieved first in the case where there are
1168 -- multiple records with the same date_from date.
1169 -- Hence, the result set will appear as follows:
1170 -- Phone ID Phone Type Phone Number Date From Date To
1171 -- -------- ---------- ------------- ------------- -------------
1172 -- 3 WF 650-003-0003 05-Dec-97 16-Dec-97
1173 -- 2 WF 650-002-0002 01-Dec-97 <null>
1174 -- 1 WF 650-001-0001 01-Dec-97 31-Dec-97
1175
1176 -- 2) If no date is passed in, the system will use fnd_session effective date-- or sysdate to retrieve the record.
1177 -- 3) Return null value if no record found for the type, person_id and
1178 -- the specific date.
1179 --
1180 --------------------------------------------------------------------------------
1181 function GET_PHONE_NUMBER
1182 (p_person_id in number
1183 ,p_phone_type in varchar2
1184 ,p_effective_date in date default null) return varchar2 is
1185
1186
1187 cursor csr_phones(c_effective_date in date) is
1188 select phn.phone_number
1189 ,phn.date_from
1190 ,phn.date_to
1191 from per_phones phn
1192 where phn.parent_id = p_person_id
1193 and phn.parent_table = 'PER_ALL_PEOPLE_F'
1194 and phn.phone_type = p_phone_type
1195 and c_effective_date between phn.date_from and
1196 nvl(phn.date_to,c_effective_date)
1197 order by phn.date_from DESC -- This is not a mistake of not using
1198 ,phn.date_to DESC; -- nvl(date_to, c_effective_date) in the order
1199 -- by clause because we want the null date_to
1200 -- record to be sorted first.
1201
1202 l_effective_date date;
1203 --
1204 BEGIN
1205
1206 IF p_effective_date is null THEN
1207 l_effective_date := hr_general.effective_date;
1208 ELSE
1209 l_effective_date := p_effective_date;
1210 END IF;
1211 --
1212 For c_get_phones in csr_phones(c_effective_date => l_effective_date)
1213 LOOP
1214 return c_get_phones.phone_number;
1215 END LOOP;
1216 --
1217 Return null;
1218 --
1219 Exception
1220 WHEN no_data_found THEN
1221 return null;
1222 --
1223 --
1224 When others THEN
1225 raise;
1226
1227 END get_phone_number;
1228
1229 --------------------------------------------------------------------------------
1230 -- This function will return the work phone number from PER_PHONES
1231 --
1232 function GET_WORK_PHONE (
1233 p_person_id number) return varchar2 is
1234 --
1235 l_per_people_phone varchar2(60);
1236 l_per_phones_phone varchar2(60);
1237 l_effective_date date;
1238 cursor csr_phones1(c_effective_date in date) is
1239 select phone_number
1240 from per_phones phn
1241 where phn.parent_id = p_person_id
1242 and phn.parent_table = 'PER_ALL_PEOPLE_F'
1243 and phn.phone_type = 'W1'
1244 and c_effective_date between phn.date_from and
1245 nvl(phn.date_to,c_effective_date);
1246
1247
1248 begin
1249 l_effective_date := hr_general.effective_date;
1250 open csr_phones1(l_effective_date);
1251 fetch csr_phones1 into l_per_phones_phone;
1252 close csr_phones1;
1253 return l_per_phones_phone;
1254
1255 end get_work_phone;
1256 --------------------------------------------------------------------------------
1257 -- This function will return the home phone number from PER_PHONES
1258 --
1259 function GET_HOME_PHONE (
1260
1261 p_person_id number) return varchar2 is
1262 --
1263 l_per_address_phone varchar2(60);
1264 l_per_phones_phone varchar2(60);
1265
1266 cursor csr_phones1 is
1267 select phone_number
1268 from per_phones phn,
1269 fnd_sessions f
1270 where phn.parent_id = p_person_id
1271 and phn.parent_table = 'PER_ALL_PEOPLE_F'
1272 and phn.phone_type = 'H1'
1273 and f.effective_date between phn.date_from and
1274 nvl(phn.date_to,f.effective_date)
1275 and f.session_id = userenv ('sessionid');
1276
1277 cursor csr_phones2 is
1278 select telephone_number_1
1279 from per_addresses adr,
1280 fnd_sessions f
1281 where adr.person_id = p_person_id
1282 and adr.primary_flag = 'Y'
1283 and f.effective_date between adr.date_from and
1284 nvl(adr.date_to,f.effective_date)
1285 and f.session_id = userenv ('sessionid');
1286
1287 begin
1288
1289 open csr_phones1;
1290 fetch csr_phones1 into l_per_phones_phone;
1291 close csr_phones1;
1292
1293 if l_per_phones_phone is not null then
1294 return l_per_phones_phone;
1295 else
1296 open csr_phones2;
1297 fetch csr_phones2 into l_per_address_phone;
1298 close csr_phones2;
1299 return l_per_address_phone;
1300 end if;
1301 end get_home_phone;
1302
1303 --------------------------------------------------------------------------------
1304 function DECODE_PEOPLE_GROUP (
1305 p_people_group_id number) return varchar2 is
1306 --
1307 cursor csr_lookup is
1308 select group_name
1309 from pay_people_groups
1310 where people_group_id = p_people_group_id;
1311 --
1312 v_meaning pay_people_groups.group_name%type := null;
1313 --
1314 begin
1315 --
1316 -- Only open the cursor if the parameter is going to retrieve anything
1317 --
1318 if p_people_group_id is not null then
1319 --
1320 open csr_lookup;
1321 fetch csr_lookup into v_meaning;
1322 close csr_lookup;
1323 --
1324 end if;
1325 --
1326 return v_meaning;
1327 end decode_people_group;
1328 --------------------------------------------------------------------------------
1329 procedure check_HR_version (
1330 --
1331 -- Checks the in-use form version against the latest version of the form
1332 -- If there is a mismatch then an error is raised
1333 --
1334 p_object_name varchar2,
1335 p_actual_version varchar2) is
1336 --
1337 l_dbms_cursor integer;
1338 l_dummy integer;
1339 -- The text below will be run dynamically after substituting the parameters
1340 l_text varchar2 (2000) :=
1341 'begin '||
1342 'if hr_version.<variable_name> != ''<actual_version>'' then'||
1343 ' hr_utility.set_message (801, ''HR_7345_INVALID_FILE'');'||
1344 ' hr_utility.set_message_token (''FILENAME'',''<object_name>'');'||
1345 ' hr_utility.set_message_token (''OLD_VERSION'', ''<actual_version>'');'||
1346 ' hr_utility.set_message_token (''LATEST_VERSION'', hr_version.<variable_name>);'||
1347 ' hr_utility.raise_error;'||
1348 'end if;'||
1349 'end;';
1350 --
1351 begin
1352 --
1353 -- Substitute the embedded variables in the dynamic pl/sql above
1354 --
1355 l_text := replace (l_text, '<variable_name>', p_object_name);
1356 l_text := replace (l_text, '<object_name>', p_object_name);
1357 l_text := replace (l_text, '<actual_version>', p_actual_version);
1358 --
1359 -- Run the dynamic pl/sql
1360 --
1361 l_dbms_cursor := dbms_sql.open_cursor;
1362 dbms_sql.parse ( l_dbms_cursor,
1363 l_text,
1364 dbms_sql.v7);
1365 l_dummy := dbms_sql.execute (l_dbms_cursor);
1366 dbms_sql.close_cursor (l_dbms_cursor);
1367 --
1368 exception
1369 when others then
1370 if dbms_sql.is_open (l_dbms_cursor) then
1371 dbms_sql.close_cursor (l_dbms_cursor);
1372 end if;
1373 raise;
1374 --
1375 end check_HR_version;
1376
1377 -- --------------------------------------------------------------------------
1378 --
1379 procedure set_calling_context(p_calling_context IN varchar2) is
1380 --
1381 -- Sets the global variable g_calling_context to p_calling_context
1382 -- The value 'FORMS' will be passed in if called from a form
1383 --
1384 begin
1385 --
1386 g_calling_context := p_calling_context;
1387 --
1388 end set_calling_context;
1389 --
1390 --------------------------------------------------------------------------------
1391 --
1392 PROCEDURE init_forms(p_business_group_id IN NUMBER,
1393 p_short_name OUT nocopy VARCHAR2,
1394 p_bg_name OUT nocopy VARCHAR2,
1395 p_bg_currency_code OUT nocopy VARCHAR2,
1396 p_legislation_code OUT nocopy VARCHAR2,
1397 p_session_date IN OUT nocopy DATE,
1398 p_ses_yesterday OUT nocopy DATE,
1399 p_start_of_time OUT nocopy DATE,
1400 p_end_of_time OUT nocopy DATE,
1401 p_sys_date OUT nocopy DATE,
1402 p_enable_hr_trace IN BOOLEAN,
1403 p_hr_trace_dest IN VARCHAR2 DEFAULT 'DBMS_PIPE'
1404
1405 /* This code not yet implemented
1406 p_form_name varchar2 default null,
1407 p_actual_version varchar2 default null
1408 */
1409
1410 ) IS
1411 --
1412 l_session_date date;
1413 l_commit_flag number; -- See note below
1414 --
1415 l_security_profile_id number;
1416 l_security_business_group_id number;
1417 --
1418 cursor sec_bg is
1419 select business_group_id
1420 from per_security_profiles
1421 where security_profile_id=l_security_profile_id;
1422 --
1423 begin
1424 --
1425 /* This code not yet implemented
1426 if p_form_name is not null and p_actual_version is not null then
1427 check_HR_version (p_form_name, p_actual_version);
1428 end if;
1429 */
1430 --
1431 if p_business_group_id is not null then
1432 -- Attempt to get business group details from database
1433 begin
1434 select b.legislation_code
1435 , b.short_name
1436 , b.name
1437 , b.currency_code
1438 into p_legislation_code
1439 , p_short_name
1440 , p_bg_name
1441 , p_bg_currency_code
1442 from per_business_groups b
1443 where b.business_group_id = p_business_group_id;
1444 exception
1445 when no_data_found then
1446 hr_utility.set_message('801', 'HR_6153_ALL_PROCEDURE_FAIL');
1447 hr_utility.set_message_token('PROCEDURE', 'INIT_FORMS');
1448 hr_utility.set_message_token('STEP', '1');
1449 hr_utility.raise_error;
1450 when too_many_rows then
1451 hr_utility.set_message('801', 'HR_6153_ALL_PROCEDURE_FAIL');
1452 hr_utility.set_message_token('PROCEDURE', 'INIT_FORMS');
1453 hr_utility.set_message_token('STEP', '2');
1454 hr_utility.raise_error;
1455 end;
1456 -- check that the security profile business group matches the
1457 -- set business group.
1458 l_security_profile_id:=fnd_profile.value('PER_SECURITY_PROFILE_ID');
1459 --
1460 open sec_bg;
1461 fetch sec_bg into l_security_business_group_id;
1462 if sec_bg%notfound then
1463 close sec_bg;
1464 -- the security profile does not exist, so raise an error.
1465 hr_utility.set_message('800', 'PER_52803_SEC_INV_BG');
1466 hr_utility.raise_error;
1467 else
1468 close sec_bg;
1469 if nvl(l_security_business_group_id,p_business_group_id)
1470 <>p_business_group_id then
1471 -- the security profile business group id is not null and
1472 -- it dies not match our business group id so raise an error.
1473 hr_utility.set_message('800', 'PER_52803_SEC_INV_BG');
1474 hr_utility.raise_error;
1475 end if;
1476 end if;
1477 --
1478 else -- p_business_group_id is null
1479 p_legislation_code := null;
1480 p_short_name := null;
1481 end if;
1482 --
1483 -- Call DateTrack procedure to get date values
1484 --
1485 dt_fndate.get_dates(
1486 p_ses_date => l_session_date,
1487 p_ses_yesterday_date => p_ses_yesterday,
1488 p_start_of_time => p_start_of_time,
1489 p_end_of_time => p_end_of_time,
1490 p_sys_date => p_sys_date,
1491 p_commit => l_commit_flag);
1492 --
1493 -- If there was a session date passed in, and it's not sysdate,
1494 -- update the row just inserted in fnd_sessions to the date passed
1495 -- in. This is not the most efficient way to do it, but it means no
1496 -- change is needed to dt_fndate.
1497 --
1498 if l_session_date = nvl (p_session_date, l_session_date) then
1499 p_session_date := l_session_date;
1500 else
1501
1502 dt_fndate.change_ses_date (p_session_date, l_commit_flag);
1503
1504 -- Bug 358870
1505 -- If this is the case then p_ses_yesterday needs to be
1506 -- to be re-set
1507 --
1508 -- Must prevent '01/01/0001'-1 as any earlier date would be invalid
1509 --
1510 if p_session_date = to_date('01/01/0001', 'DD/MM/YYYY') then
1511 p_ses_yesterday := null;
1512 else
1513 p_ses_yesterday := p_session_date - 1;
1514 end if;
1515
1516 end if;
1517 --
1518 -- Enable HR trace. This is done as each form starts up
1519 --
1520 if ( p_enable_hr_trace ) then
1521 hr_utility.trace_on('F','PID');
1522 hr_utility.set_trace_options('TRACE_DEST:'||p_hr_trace_dest);
1523 end if;
1524
1525
1526 -- DK 22-DEC-1996 Ideally we should remove p_commit_flag altogether
1527 -- but as we are not regenerating for Prod-16 this is not feasible
1528 if ( l_commit_flag = 1 ) then
1529 commit ;
1530 end if;
1531
1532 -- Bug no 581122 - Part of fix
1533 -- hr_general.set_calling_context(p_calling_context => 'FORMS');
1534
1535 end init_forms;
1536 --------------------------------------------------------------------------------
1537 --
1538 function chk_geocodes_installed
1539 return varchar2
1540 is
1541 --
1542 l_proc varchar2(72) := 'hr_general.chk_geocodes_installed';
1543 --
1544 l_exists varchar2(1);
1545 --
1546 -- Declare cursor.
1547 --
1548 -- Fix for Bug 3355231 starts here.
1549 -- Performance issue. Modified below cursor.
1550 --
1551 cursor csr_get_us_city_names
1552 is
1553 select 'Y' from dual
1554 where exists(select null
1555 from pay_us_city_names
1556 where rownum =1);
1557 --
1558 -- Fix for bug 3355231 ends here.
1559 --
1560 begin
1561 hr_utility.set_location('Entering:'|| l_proc, 1);
1562 --
1563 -- Check if any rows exist in the pay_us_city_names
1564 --
1565 open csr_get_us_city_names;
1566 fetch csr_get_us_city_names into l_exists;
1567 if csr_get_us_city_names%FOUND then
1568 return 'Y';
1569 else
1570 return 'N';
1571 end if;
1572 close csr_get_us_city_names;
1573 --
1574 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1575 --
1576 end chk_geocodes_installed;
1577 -- --------------------------------------------------------------------------
1578 -- This function will be called to determine if we need to maintain tax record
1579 -- If US payroll is installed or if GEO_codes are installed and the profile
1580 -- option PER_ENABLE_DTW4 is set to Yes we will maintain the tax record.
1581 -- by default or if PER_ENABLE_DTW4 is installed on the system the value will
1582 -- be 'Yes'.
1583 --
1584 function chk_maintain_tax_records
1585 return varchar2
1586 is
1587 --
1588 l_proc varchar2(72) := 'hr_general.chk_maintain_tax_records';
1589 --
1590 l_dtw4_profile_option_value VARCHAR2(3);
1591 begin
1592 hr_utility.set_location('Entering:'|| l_proc, 1);
1593 --
1594 -- Get the profile value for the PER_ENABLE_DTW4.
1595 --
1596 FND_PROFILE.GET('PER_ENABLE_DTW4',
1597 l_dtw4_profile_option_value);
1598 --
1599 IF hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1600 p_legislation => 'US') OR
1601 (hr_general.chk_geocodes_installed ='Y' and
1602 NVL(l_dtw4_profile_option_value,'Y') = 'Y' ) THEN
1603 return 'Y';
1604 else
1605 return 'N';
1606 end if;
1607
1608
1609 --
1610 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1611 --
1612 end chk_maintain_tax_records;
1613 -- --------------------------------------------------------------------------
1614 --
1615 --
1616 function get_calling_context return varchar2 is
1617 --
1618 -- Returns the value of g_calling_context
1619 --
1620 begin
1621 --
1622 return g_calling_context;
1623 --
1624 end get_calling_context;
1625 --
1626 -- --------------------------------------------------------------------------
1627 function chk_product_installed(p_application_id in number) return varchar2 is
1628 --
1629 l_installed varchar2(10);
1630 --
1631 cursor csr_install is
1632 select 'X'
1633 from fnd_product_installations
1634 where application_id = p_application_id
1635 and status = 'I';
1636 --
1637 begin
1638 --
1639 open csr_install;
1640 fetch csr_install into l_installed;
1641 if csr_install%FOUND then
1642 close csr_install;
1643 return 'TRUE';
1644 else
1645 close csr_install;
1646 return 'FALSE';
1647 end if;
1648 --
1649 end chk_product_installed;
1650 --
1651 -- -------------------------------------------------------------------------
1652 function get_user_status (p_assignment_status_type_id in number) return varchar2 is
1653 --
1654 l_user_status varchar2(80);
1655 --
1656 cursor csr_amend_user_status is
1657 select tl.user_status
1658 from per_ass_status_type_amends am,
1659 per_ass_status_type_amends_tl tl
1660 where am.assignment_status_type_id = p_assignment_status_type_id
1661 and am.business_group_id = get_business_group_id -- Bug #2519443
1662 and am.ass_status_type_amend_id =
1663 tl.ass_status_type_amend_id
1664 and tl.language=USERENV('LANG');
1665 --
1666 cursor csr_assign_user_status is
1667 select tl.user_status
1668 from per_assignment_status_types asg,
1669 per_assignment_status_types_tl tl
1670 where asg.assignment_status_type_id = p_assignment_status_type_id
1671 and asg.assignment_status_type_id =
1672 tl.assignment_status_type_id
1673 and tl.language=USERENV('LANG');
1674 --
1675 begin
1676 --
1677 open csr_amend_user_status;
1678 fetch csr_amend_user_status into l_user_status;
1679 if csr_amend_user_status%found then
1680 close csr_amend_user_status;
1681 return l_user_status;
1682 else
1683 close csr_amend_user_status;
1684 open csr_assign_user_status;
1685 fetch csr_assign_user_status into l_user_status;
1686 if csr_assign_user_status%found then
1687 close csr_assign_user_status;
1688 return l_user_status;
1689 end if;
1690 close csr_assign_user_status;
1691 end if;
1692 return l_user_status;
1693 --
1694 end;
1695 --
1696
1697 ------------------------------------------------------------------------
1698 function DECODE_TERRITORY (
1699
1700 --
1701 p_territory_code varchar2) return varchar2 is
1702 --
1703 cursor csr_lookup is
1704 select territory_short_name
1705 from fnd_territories_vl
1706 where territory_code = p_territory_code;
1707 --
1708 v_meaning varchar2(80) := null;
1709 --
1710 begin
1711 --
1712 -- Only open the cursor if the parameter is going to retrieve anything
1713 --
1714 if p_territory_code is not null then
1715 --
1716 open csr_lookup;
1717 fetch csr_lookup into v_meaning;
1718 close csr_lookup;
1719 --
1720 end if;
1721 return v_meaning;
1722 end decode_territory;
1723 -----------------------------------------------------------------------
1724
1725 function DECODE_ORGANIZATION (
1726
1727 --
1728 p_organization_id number) return varchar2 is
1729 --
1730 cursor csr_lookup is
1731 select name
1732 from hr_all_organization_units_tl
1733 where organization_id = p_organization_id
1734 and language = userenv('LANG');
1735 --
1736 v_meaning hr_all_organization_units_tl.name%TYPE := null;
1737 --
1738 begin
1739 --
1740 -- Only open the cursor if the parameter is going to retrieve anything
1741 --
1742 if p_organization_id is not null then
1743 --
1744 open csr_lookup;
1745 fetch csr_lookup into v_meaning;
1746 close csr_lookup;
1747 --
1748 end if;
1749 return v_meaning;
1750 end decode_organization;
1751 -----------------------------------------------------------------------
1752
1753 function DECODE_AVAILABILITY_STATUS (
1754
1755 --
1756 p_availability_status_id number) return varchar2 is
1757 --
1758 cursor csr_lookup is
1759 select shared_type_name
1760 from per_shared_types_vl
1761 where shared_type_id = p_availability_status_id;
1762 --
1763 v_meaning per_shared_types_vl.shared_type_name%TYPE := null;
1764 --
1765 begin
1766 --
1767 -- Only open the cursor if the parameter is going to retrieve anything
1768 --
1769 if p_availability_status_id is not null then
1770 --
1771 open csr_lookup;
1772 fetch csr_lookup into v_meaning;
1773 close csr_lookup;
1774 --
1775 end if;
1776 return v_meaning;
1777 end decode_availability_status;
1778 -----------------------------------------------------------------------
1779
1780 function DECODE_POSITION_CURRENT_NAME (
1781 --
1782 p_position_id number) return varchar2 is
1783 --
1784 cursor csr_position is
1785 select name
1786 from hr_positions_x
1787 where position_id = p_position_id;
1788 --
1789 v_position_current_name hr_positions_x.name%TYPE := null;
1790 --
1791 begin
1792 --
1793 -- Only open the cursor if the parameter is going to retrieve anything
1794 --
1795 if p_position_id is not null then
1796 --
1797 open csr_position;
1798 fetch csr_position into v_position_current_name;
1799 close csr_position;
1800 --
1801 end if;
1802 return v_position_current_name;
1803 --
1804 end decode_position_current_name;
1805 --
1806 -----------------------------------------------------------------------
1807 --
1808 function DECODE_POSITION_LATEST_NAME (
1809 --
1810 p_position_id in number,
1811 p_effective_date in date default null) return varchar2 is
1812 --
1813 cursor csr_latest_position is
1814 select pft.name
1815 from hr_all_positions_f_tl pft
1816 where pft.position_id = p_position_id
1817 and pft.language = userenv('LANG');
1818 --
1819 cursor csr_date_eff_position(p_position_id number, p_effective_date date) is
1820 select psf.name
1821 from hr_all_positions_f psf
1822 where psf.position_id = p_position_id
1823 and p_effective_date between
1824 psf.effective_start_date and psf.effective_end_date;
1825 --
1826 cursor c_session_date is
1827 select effective_date
1828 from fnd_sessions
1829 where session_id = userenv('sessionid');
1830 --
1831 v_position_latest_name hr_all_positions_f_tl.name%TYPE := null;
1832 l_hr_pos_name_profile_value varchar2(20);
1833 l_effective_date date;
1834 --
1835 begin
1836 --
1837 -- Only open the cursor if the parameter is going to retrieve anything
1838 --
1839 if p_position_id is not null then
1840 --
1841 fnd_profile.get('HR_POSITION_NAME',l_hr_pos_name_profile_value);
1842 --
1843 if l_hr_pos_name_profile_value is null or l_hr_pos_name_profile_value = 'L' then
1844 --
1845 open csr_latest_position;
1846 fetch csr_latest_position into v_position_latest_name;
1847 close csr_latest_position;
1848 --
1849 else
1850 if p_effective_date is not null then
1851 l_effective_date := p_effective_date;
1852 else
1853 open c_session_date;
1854 fetch c_session_date into l_effective_date;
1855 close c_session_date;
1856 --
1857 if l_effective_date is null then
1858 l_effective_date := trunc(sysdate);
1859 end if;
1860 --
1861 end if;
1862 open csr_date_eff_position(p_position_id, l_effective_date);
1863 fetch csr_date_eff_position into v_position_latest_name;
1864 close csr_date_eff_position;
1865 end if;
1866 end if;
1867 return v_position_latest_name;
1868 --
1869 end decode_position_latest_name;
1870 --
1871 -----------------------------------------------------------------------
1872
1873 function DECODE_STEP (
1874 --
1875 p_step_id number
1876 , p_effective_date date) return varchar2 is
1877 --
1878 cursor csr_step is
1879 select psp.spinal_point
1880 from per_spinal_point_steps_f sps, per_spinal_points psp
1881 where sps.step_id = p_step_id
1882 and p_effective_date between sps.effective_start_date and sps.effective_end_date
1883 and sps.spinal_point_id = psp.spinal_point_id;
1884 --
1885 v_spinal_point varchar2(2000) := null;
1886 --
1887 begin
1888 --
1889 -- Only open the cursor if the parameter is going to retrieve anything
1890 --
1891 if p_step_id is not null and p_effective_date is not null then
1892 --
1893 open csr_step;
1894 fetch csr_step into v_spinal_point;
1895 close csr_step;
1896 --
1897 end if;
1898 return v_spinal_point;
1899 --
1900 end decode_step;
1901 --
1902 -----------------------------------------------------------------------
1903 function decode_ar_lookup
1904 (
1905 p_lookup_type varchar2,
1906 p_lookup_code varchar2) return varchar2 is
1907 --
1908 cursor csr_lookup is
1909 select meaning
1910 from ar_lookups
1911 where lookup_type = p_lookup_type
1912 and lookup_code = p_lookup_code;
1913 --
1914 v_meaning varchar2(80) := null;
1915 begin
1916 if p_lookup_type is not null and p_lookup_code is not null then
1917 --
1918 open csr_lookup;
1919 fetch csr_lookup into v_meaning;
1920 close csr_lookup;
1921 end if;
1922 --
1923 return v_meaning;
1924 --
1925 end decode_ar_lookup;
1926 ----------------
1927
1928 function hr_lookup_locations
1929 (
1930 p_location_id number)
1931 return varchar2 is
1932 --
1933 -- 3902208 added substrb function to cursor
1934 --
1935 cursor csr_addr_lookup is
1936 select
1937 substrb(
1938 LOC1.ADDRESS_LINE_1||
1939 decode(LOC1.ADDRESS_LINE_1,null,'',', ')|| LOC1.ADDRESS_LINE_2||
1940 decode(LOC1.ADDRESS_LINE_2,null,'',', ')|| LOC1.ADDRESS_LINE_3||
1941 decode(LOC1.ADDRESS_LINE_3,null,'',', ')||
1942 LOC1.TOWN_OR_CITY||decode(LOC1.TOWN_OR_CITY,null,'',', ')||
1943 LOC1.REGION_1||decode(LOC1.REGION_1,null,'',', ')|| LOC1.REGION_2||
1944 decode(LOC1.REGION_2,null,'',', ')||
1945 LOC1.REGION_3||decode(LOC1.REGION_3,null,'',', ')|| LOC1.POSTAL_CODE||
1946 decode(LOC1.POSTAL_CODE,null,'',', ')|| LOC1.COUNTRY||
1947 decode(LOC1.COUNTRY,null,' ',', ')
1948 ,1,600)
1949 from hr_locations LOC1
1950 where location_id = p_location_id
1951 and location_use = 'HR';
1952 --
1953 v_address varchar2(600) := null;
1954 begin
1955 if p_location_id is not null then
1956 --
1957 open csr_addr_lookup;
1958 fetch csr_addr_lookup into v_address;
1959 close csr_addr_lookup;
1960 end if;
1961 --
1962 return v_address;
1963 --
1964 end hr_lookup_locations;
1965 -----------------------------------------------------------------------
1966 --
1967 function DECODE_LATEST_POSITION_DEF_ID (
1968 --
1969 p_position_id number) return number is
1970 --
1971 cursor csr_position is
1972 select position_definition_id
1973 from hr_all_positions_f
1974 where position_id = p_position_id
1975 and effective_end_date = hr_general.end_of_time;
1976 --
1977 v_position_definition_id number(20) := null;
1978 --
1979 begin
1980 --
1981 -- Only open the cursor if the parameter is going to retrieve anything
1982 --
1983 if p_position_id is not null then
1984 --
1985 open csr_position;
1986 fetch csr_position into v_position_definition_id;
1987 close csr_position;
1988 --
1989 end if;
1990 return v_position_definition_id;
1991 --
1992 end decode_latest_position_def_id;
1993
1994 --------------------------------------------------------------
1995 function DECODE_AVAIL_STATUS_START_DATE (
1996 --
1997 p_position_id in number,
1998 p_availability_status_id number,p_effective_date date) return date is
1999 --
2000 cursor csr_avail_status is
2001 select psf.availability_status_id, psf.effective_start_date
2002 from hr_all_positions_f psf
2003 where psf.position_id = p_position_id
2004 and psf.effective_start_date < p_effective_date
2005 order by psf.effective_start_date desc;
2006 --
2007 v_avail_status_start_dt date := p_effective_date;
2008 l_availability_status_id number(15);
2009 l_effective_start_date date;
2010 --
2011 begin
2012 --
2013 -- Only open the cursor if the parameter is going to retrieve anything
2014 --
2015 if p_position_id is not null
2016 and p_availability_status_id is not null
2017 and p_effective_date is not null then
2018 --
2019 open csr_avail_status;
2020 loop
2021 fetch csr_avail_status into l_availability_status_id, l_effective_start_date;
2022
2023 if l_availability_status_id <> p_availability_status_id
2024 or csr_avail_status%notfound then exit;
2025 end if;
2026 v_avail_status_start_dt := l_effective_start_date;
2027 end loop;
2028 close csr_avail_status;
2029 --
2030 end if;
2031 return nvl(v_avail_status_start_dt,p_effective_date);
2032 --
2033 end DECODE_AVAIL_STATUS_START_DATE;
2034 --------------------------------------------------------------
2035 function GET_POSITION_DATE_END (p_position_id in number) return date is
2036 --
2037 l_effective_start_date date;
2038 --
2039 cursor csr_date_end is
2040 select psf.effective_start_date - 1
2041 from hr_all_positions_f psf, per_shared_types sht
2042 where psf.position_id = p_position_id
2043 and psf.availability_status_id = sht.shared_type_id
2044 and sht.system_type_cd in ('DELETED','ELIMINATED');
2045 --
2046 begin
2047 if p_position_id is not null then
2048 --
2049 open csr_date_end;
2050 fetch csr_date_end into l_effective_start_date;
2051 close csr_date_end;
2052 --
2053 end if;
2054 return l_effective_start_date;
2055 end;
2056 --------------------------------------------------------------
2057 function DECODE_PERSON_NAME ( p_person_id number) return varchar2 is
2058 --
2059 l_full_name varchar2(240);
2060 --
2061 --
2062 begin
2063 if p_person_id is not null then
2064 --
2065 l_full_name := hr_person_name.get_person_name(p_person_id,trunc(sysdate));
2066 --
2067 end if;
2068 return l_full_name;
2069 end;
2070 --------------------------------------------------------------
2071 function DECODE_GRADE_RULE ( p_grade_rule_id number) return varchar2 is
2072 --
2073 cursor csr_grade_rule is
2074 select pr.name
2075 from pay_grade_rules pgr, pay_rates pr
2076 where pgr.rate_id = pr.rate_id
2077 and pgr.grade_rule_id = p_grade_rule_id;
2078 --
2079 l_pay_rate varchar2(240);
2080 --
2081 begin
2082 --
2083 if p_grade_rule_id is not null then
2084 --
2085 open csr_grade_rule;
2086 fetch csr_grade_rule into l_pay_rate;
2087 close csr_grade_rule;
2088 --
2089 end if;
2090 return l_pay_rate;
2091 --
2092 end;
2093 --------------------------------------------------------------
2094 FUNCTION get_validation_name (p_target_location IN VARCHAR2
2095 ,p_field_name IN VARCHAR2
2096 ,p_legislation_code IN VARCHAR2
2097 ,p_validation_type IN VARCHAR2) return VARCHAR2 IS
2098 --
2099 CURSOR c_plfi IS
2100 SELECT validation_name
2101 FROM pay_legislative_field_info
2102 WHERE UPPER(target_location) = UPPER(p_target_location)
2103 AND UPPER(field_name) = UPPER(p_field_name)
2104 AND UPPER(legislation_code) = UPPER(p_legislation_code)
2105 AND UPPER(validation_type) = UPPER(p_validation_type);
2106 --
2107 l_validation_name pay_legislative_field_info.validation_name%TYPE;
2108 --
2109 BEGIN
2110 --
2111 OPEN c_plfi;
2112 FETCH c_plfi into l_validation_name;
2113 IF c_plfi%found THEN
2114 CLOSE c_plfi;
2115 -- return the localization lookup type
2116 RETURN l_validation_name;
2117 ELSE
2118 CLOSE c_plfi;
2119 -- return the supplied core lookup type
2120 RETURN p_validation_type;
2121 END IF;
2122 --
2123 END get_validation_name;
2124 --------------------------------------------------------------
2125 function decode_shared_type (
2126 p_shared_type_id number) return varchar2 is
2127 begin
2128 return(decode_availability_status(p_shared_type_id));
2129 end;
2130 --------------------------------------------------------------
2131 --------------------------------------------------------------
2132 function get_xbg_profile return varchar2 is
2133 --
2134 -- Returns the value of the 'HR: Cross Business Group' profile option
2135 -- unless either :
2136 --
2137 -- A. No applications context is established
2138 -- OR B. The 'HR:Security Profile' profile option is null.
2139 --
2140 -- In these two cases the value 'Y' is returned.
2141 -- The assumption for these cases is that the user needs an unrestricted
2142 -- view. For example they are accessing from outside applications or in a
2143 -- responsibility like one used for workflow notifications
2144 --
2145 -- Bug 2372279
2146 -- Condition B. is to address the issues bugs like 2111280
2147 -- This is that some code may run with an applications context but
2148 -- have no security profile in which case we ignore the profile
2149 -- option and allow a cross business group view. This guarantees
2150 -- that a secure view behaves like the base table if the security
2151 -- profile is not set.
2152 --
2153
2154 --
2155 begin
2156
2157 --
2158 -- bug2372279
2159 --
2160 -- Using fnd_global.per_security_profile_id for performance
2161 -- reasons. hr_security.get_security_profile should probably
2162 -- be used as it looks like reporting users are getting an
2163 -- unrestricted bg view but will address this later as a
2164 -- separate pre-existing issue.
2165 --
2166 if ( fnd_global.user_id = -1
2167 or fnd_global.per_security_profile_id is null
2168 or fnd_global.per_security_profile_id = -1 )
2169 then
2170 return 'Y' ;
2171 else
2172 return( fnd_profile.value('HR_CROSS_BUSINESS_GROUP') );
2173 end if;
2174
2175 end get_xbg_profile;
2176 --------------------------------------------------------------
2177 end HR_GENERAL;
[Home] [Help]