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