1 package HR_GENERAL as
2 /* $Header: hrgenral.pkh 120.0 2005/05/29 02:30:57 appldev 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 Other general purpose routines can be found in hr_utility and hr_chkfmt.
15
16 See the package body for details of the functions.
17 History
18 1 Mar 94 N Simpson Created
19 2 Mar 94 PK Attwood Added init_forms procedure. Corrected
20 show errors statement.
21 4 Mar 94 N Simpson Added functions to return constants
22 27 Apr 94 N Simpson Added function LOCATION_VALID
23 27 Apr 94 N Simpson Corrected location_valid datatypes
24 17 Oct 94 N Simpson Added restrict_references to
25 decode_lookup
26 16 Jan 95 N Simpson Added overloaded DEFAULT_CURRENCY_CODE
27 using p_business_group_id
28 15 Mar 95 R Fine Allowed a session date to be passed in
29 to init_forms.
30 23 Jun 95 D Kerr Added bg_name and bg_legislation_code
31 to init_forms together with temp.
32 overloaded version.
33 11 Mar 95 A Forte Added function calls DECODE_GRADE to
34 DECODE_ASS_STATUS_TYPE which are called
35 from peasg03v.sql which allowed the
36 elimination of outer joins and enhanced
37 the performance of the asssignments
38 folder PERWSFAS. Bug 343096.
39 13 Jun 96 A.Mills Added function definition for
40 GET_SALARY, used by view per
41 _expanded_assignments_v1, for bug
42 336409.
43 09 DEC 96 Ty Hayden Added function GET_WORK_PHONE and
44 GET_HOME_PHONE
45 12 DEC 96 Ty Hayden Modified GET_WORK_PHONE and
46 GET_HOME_PHONE to be more efficient.
47
48 20 DEC 96 D. Kerr init_forms: removed temporary overload
49 Removed p_commit and added
50 p_enable_hr_trace
51 02 APR 1997 HPATEL Added functions CHK_APPLICATION_ID,
52 CORE_HR_APP_ID and VERTICAL_APP_ID
53 Name Date Versn Bug Text
54 ========================================================================
55 rfine 29-Apr-97 40.23 n/a Leapfrogged from 40.21 over special
56 p15.1 version. Only change is this
57 comment in change history.
58 dkerr 27-Jul-97 110.3 n/a Added :
59 char_to_bool
60 bool_to_char
61 get_application_short_name
62 hrms_object
63 19 Aug 97 Sxshah Banner now on eack line.
64 mhoyes 27-AUG-97 110.4 n/a Added chk_geocodes_installed.
65 dkerr 19-OCT-97 110.8 n/a Added get_business_group_id
66 mshah 09-OCT-97 110.9 n/a Added proc set_calling_context
67 and function get_calling_context
68 fychu 16-Dec-97 110.10 n/a Added function get_phone_number
69 from per_phones table.
70 fychu 19-Dec-97 110.11 n/a Fixed the version number in the log.
71 dkerr 06-Jan-98 110.12 n/a char_to_bool/bool_to_char now public
72 dkerr 20_MAR-98 40.25 643828 Added DECODE_PEOPLE_GROUP
73 ccarter 23-DEC-98 115.2 Added chk_product_installed function.
74 sxshah 13-Jan-99 115.3 Added g_data_migrator_mode variable
75 which will be used by the HRMS
76 data migrator process.
77 sxshah 29-Jan-99 115.4 Default for above var to 'N'
78 ccarter 03-Jun-99 115.5 Added get_user_status function.
79 skekkar 23-AUG-99 115.6 Added decode_territory ,
80 decode_organization and
81 decode_availability_status functions
82 hsajja 25-AUG-99 115.7 Added decode_position_current_name
83 function
84 darora 27-Sep-99 115.11 Included functions - DECODE_POSITION_LATEST_NAME,
85 and DECODE_STEP
86 rraina 29-Sep-99 115.9 985430 Added decode_ar_lookup and
87 hr_lookup_locations functions
88 hsajja 01-OCT-99 115.10 Added decode_latest_position_def_id
89 ,decode_avail_status_start_date function
90 hsajja 04-OCT-99 115.12 Added get_position_date_end function
91 pzwalker 05-OCT-99 115.12 removed pragma to decode_lookups
92 hsajja 07-OCT-99 115.13 Added functions DECODE_PERSON_NAME,
93 DECODE_GRADE_RULE.
94 hsajja 12-OCT-99 115.14 Added Commit at the end
95 rraina 10-NOV-1999 115.15 added decode_fnd_comm_lookup for ota views opt
96 dkerr 10-NOV-99 115.16 Added p_hr_trace_dest parameter to
97 init_forms
98 cxsimpso 28-DEC-99 115.17 Added get_validation_name function.
99 mbocutt 03/01/2000 115.18 1125512 Remove pragma from get_business_group_id
100 hsajja 21/02/2000 115.25 Included function DECODE_SHARED_TYPE
101 rvydyana 24/05/2000 115.27 Added new function get_xbg_profile
102 tcewis 29-FEB-00 115.21 added the function chk_maintain_tax
103 records.
104 arashid 13-OCT-00 115.22 Added a cover routine for:
105 DBMS_DESCRIBE.DESCRIBE_PROCEDURE to
106 compile an invalid package. The new
107 routine is called: DESCRIBE_PROCEDURE.
108 stlocke 23-JAN-2001 115.33 Added procedure init-fndload.
109 stlocke 08-FEB-2001 115.34 Procedure init-fndload removed.
110 hsajja 18-JAN-2002 115.25 added dbdrv command
111 hsajja 18-JAN-2002 115.26 replaced -- with rem before create package
112 adhunter 28-AUG-2002 115.27 correct gscc warning
113 dsaxby 04-DEC-2002 115.28 2692195 Nocopy changes.
114 ynegoro 23-JUL-2003 115.29 Added DECODE_GRADE_LADDER function
115 hsajja 10-DEC-2004 115.30 3663875 Changed DECODE_POSITION_LATEST_NAME
116 function
117 -------------------------------------------------------------------------------
118
119 DO NOT ADD ANY FUTHER PROCEDURES / FUNCTIONS TO THIS FILE!
120
121 IF REQUIRED PLEASE ADD TO HR_GENRAL2 (hrgenrl2.pkh/pkb)
122
123 -------------------------------------------------------------------------------
124
125 */
126 g_data_migrator_mode varchar2(1) := 'N';
127 -------------------------------------------------------------------------
128 --Due to bug 286699 you cannot use restrict references for boolean returns
129 --prior to 8.0.3/7.3.4
130 function char_to_bool (p_value in varchar2) return boolean ;
131 pragma restrict_references (char_to_bool, WNPS, RNPS, WNDS, RNDS);
132 -------------------------------------------------------------------------
133 function bool_to_char (p_value in boolean) return varchar2 ;
134 pragma restrict_references (bool_to_char, WNPS, RNPS, WNDS, RNDS);
135 -------------------------------------------------------------------------
136 procedure assert_condition (p_condition in boolean);
137 pragma restrict_references (assert_condition, WNPS, WNDS, RNPS, RNDS);
138 -------------------------------------------------------------------------
139 function GET_BUSINESS_GROUP_ID return number;
140 -------------------------------------------------------------------------
141 function CHK_APPLICATION_ID (p_application_id number) return varchar2;
142 pragma restrict_references (chk_application_id, WNPS, WNDS, RNPS, RNDS);
143 -------------------------------------------------------------------------
144 function CORE_HR_APP_ID (p_application_id number) return varchar2;
145 pragma restrict_references (core_hr_app_id, WNPS, WNDS, RNPS, RNDS);
146 -------------------------------------------------------------------------
147 function VERTICAL_APP_ID (p_application_id number) return varchar2;
148 pragma restrict_references (vertical_app_id, WNPS, WNDS, RNPS, RNDS);
149 -------------------------------------------------------------------------
150 function HRMS_OBJECT (p_object_name in varchar2) return varchar2;
151 pragma restrict_references (hrms_object, WNPS, WNDS);
152 -------------------------------------------------------------------------
153 function GET_APPLICATION_SHORT_NAME (p_application_id in varchar2) return varchar2;
154 pragma restrict_references (get_application_short_name, WNPS, WNDS, RNPS);
155 -------------------------------------------------------------------------
156 function EFFECTIVE_DATE return date;
157 pragma restrict_references (effective_date, WNPS, WNDS);
158 -------------------------------------------------------------------
159 function START_OF_TIME return date;
160 pragma restrict_references (start_of_time, WNPS,WNDS);
161 -------------------------------------------------------------------------
162 function END_OF_TIME return date;
163 pragma restrict_references (end_of_time, WNPS,WNDS);
164 -------------------------------------------------------------------------
165 function PAY_VALUE return varchar2;
166 pragma restrict_references (pay_value, WNPS,WNDS);
167 -------------------------------------------------------------------------
168 function MONEY_UNIT return varchar2;
169 -------------------------------------------------------------------------
170 function DEFAULT_CURRENCY_CODE (p_legislation_code varchar2) return varchar2;
171 -------------------------------------------------------------------------
172 function DEFAULT_CURRENCY_CODE (p_business_group_id number) return varchar2;
173 -------------------------------------------------------------------------
174 function LOCATION_VALID ( p_location_id number,
175 p_date date,
176 p_error_if_invalid boolean default TRUE
177 ) return boolean;
178 -------------------------------------------------------------------------
179 function DECODE_LOOKUP ( p_lookup_type varchar2,
180 p_lookup_code varchar2) return varchar2;
181 -- pragma restrict_references (decode_lookup, WNPS,WNDS);
182 -------------------------------------------------------------------------
183 function DECODE_FND_COMM_LOOKUP ( p_lookup_type varchar2,
184 p_lookup_code varchar2) return varchar2;
185 -- pragma restrict_references (decode_lookup, WNPS,WNDS);
186 ----------------------------------------------------------------------
187 function DECODE_GRADE (p_grade_id number) return varchar2;
188 pragma restrict_references (decode_grade, WNPS,WNDS);
189 ------------------------------------------------------------
190 function DECODE_GRADE_LADDER (p_grade_ladder_pgm_id number) return varchar2;
191 pragma restrict_references (decode_grade_ladder, WNPS,WNDS);
192 ------------------------------------------------------------
193 function DECODE_PAYROLL (p_payroll_id number) return varchar2;
194 pragma restrict_references (decode_payroll, WNPS, WNDS);
195 ------------------------------------------------------------
196 function GET_SALARY (p_pay_basis_id number, p_assignment_id number)
197 return varchar2;
198 pragma restrict_references (get_salary, WNPS, WNDS);
199 --------------------------------------------------------------------
200 function DECODE_JOB (p_job_id number) return varchar2;
201 pragma restrict_references (decode_job, WNPS, WNDS);
202 ------------------------------------------------------------
203 function DECODE_POSITION (p_position_id number) return varchar2;
204 pragma restrict_references (decode_position, WNPS, WNDS);
205 ------------------------------------------------------------
206 function DECODE_LOCATION (p_location_id number) return varchar2;
207 pragma restrict_references (decode_location, WNPS, WNDS);
208 ------------------------------------------------------------
209 function DECODE_PAY_BASIS (p_pay_basis_id number) return varchar2;
210 pragma restrict_references (decode_pay_basis, WNPS, WNDS);
211 ------------------------------------------------------------
212 function DECODE_ASS_STATUS_TYPE (p_assignment_status_type_id number,
213 p_business_group_id number)
214 return varchar2;
215 pragma restrict_references (decode_ass_status_type, WNPS, WNDS);
216 ------------------------------------------------------------
217 function GET_WORK_PHONE (
218 p_person_id number) return varchar2;
219 pragma restrict_references (get_work_phone, WNPS, WNDS);
220 ------------------------------------------------------------
221 function GET_HOME_PHONE (
222 p_person_id number) return varchar2;
223 pragma restrict_references (get_home_phone, WNPS, WNDS);
224 ------------------------------------------------------------
225 function DECODE_PEOPLE_GROUP (p_people_group_id number) return varchar2;
226 pragma restrict_references (decode_people_group, WNPS, WNDS);
227 ------------------------------------------------------------
228 function decode_ar_lookup (
229 p_lookup_type varchar2,
233 function hr_lookup_locations(
230 p_lookup_code varchar2) return varchar2;
231 --pragma restrict_references (decode_ar_lookup, RNPS,RNDS);
232 ------------------------------------------------------------------
234 p_location_id number) return varchar2;
235 pragma restrict_references (hr_lookup_locations,WNPS,WNDS);
236 -----------------------------------------------------------------------------
237 -- Name --
238 -- init_forms --
239 -- Purpose --
240 -- This procedure obtains session date from fnd_sessions and --
241 -- short_name, legislation_code values from per_business_groups. If --
242 -- there is no row in fnd_sessions for this session, one will be --
243 -- inserted. p_session_date will then set to trunc(sysdate). --
244 -- If a null business group id is past in p_short_name and --
245 -- p_legislation_code will be set to null. Otherwise their --
246 -- values are obtained from per_business_groups. --
247 -- Arguments --
248 -- In :- --
249 -- p_business_group_id should be set to the AOL business group profile --
250 -- value. --
251 -- Out :- --
252 -- p_short_name If p_business_group_id is not null p_short_name --
253 -- will be set to short_name from --
254 -- per_business_groups. If p_business_group_id is --
255 -- null p_short_name will be null. --
256 -- p_bg_name If p_business_group_id is not null p_bg_name --
257 -- is set to name from per_business_groups --
258 -- p_bg_currency_code If p_business_group_id is not null p_bg_currency_ --
259 -- code is set to currency_code from per_business --
260 -- groups.
261 -- p_legislation_code If p_business_group_id is not null --
262 -- p_legislation_code will be set to --
263 -- legislation_code from per_business_groups. If --
264 -- p_business_group_id is null p_legislation_code --
265 -- will be null. --
266 -- p_session_date is set to the session date from fnd_sessions. If --
267 -- no row existed in fnd_sessions p_session_date is --
268 -- set to trunc(sysdate). --
269 -- From 15.03.95., it is an IN OUT parameter. This --
270 -- is so a date other than sysdate can be passed --
271 -- in, and the new row in fnd_sessions has this date.--
272 -- p_ses_yesterday set to p_session_date minus one day. --
273 -- p_start_of_time set to 01-JAN-0001. --
274 -- p_end_of_time set to 31-DEC-4712. --
275 -- p_sys_date set to sysdate. --
276 -- p_enable_hr_trace Set to TRUE if trace is required for the forms --
277 -- session. --
278 -- p_hr_trace_dest If p_enable_hr_trace is TRUE then this parameter --
279 -- is used to set the TRACE_DEST option --
280 -- Notes --
281 -- None. --
282 -- --------------------------------------------------------------------------
283 --
284 --
285 procedure set_calling_context(p_calling_context IN VARCHAR2);
286 --
287 -----------------------------------------------------------------------------
288 --
289 PROCEDURE init_forms(p_business_group_id IN NUMBER,
290 p_short_name OUT nocopy VARCHAR2,
291 p_bg_name OUT nocopy VARCHAR2,
292 p_bg_currency_code OUT nocopy VARCHAR2,
293 p_legislation_code OUT nocopy VARCHAR2,
294 p_session_date IN OUT nocopy DATE,
295 p_ses_yesterday OUT nocopy DATE,
296 p_start_of_time OUT nocopy DATE,
297 p_end_of_time OUT nocopy DATE,
298 p_sys_date OUT nocopy DATE,
299 p_enable_hr_trace IN BOOLEAN,
300 p_hr_trace_dest IN VARCHAR2 DEFAULT 'DBMS_PIPE');
301 --
302 --
303 -- ---------------------------------------------------------------------------
304 -- |----------------------< chk_geocodes_installed >----------------------|
305 -- ---------------------------------------------------------------------------
306 --
307 -- Description:
308 -- Determines if GEOCODES is installed.
309 --
310 -- Pre-conditions:
311 -- None
312 --
313 -- In Arguments:
314 -- None
315 --
316 -- Out Arguments:
317 -- None
318 --
319 -- Post Success:
320 -- - When rows exist in the table pay_us_city_names then the value 'Y' is
321 -- returned.
325 -- Post Failure:
322 -- - When rows do not exist in the table pay_us_city_names then the value 'N' is
323 -- returned.
324 --
326 -- None
327 --
328 -- Access Status:
329 -- Public
330 --
331 -- {End Of Comments}
332 -- ----------------------------------------------------------------------------
333 --
334 function chk_geocodes_installed
335 return varchar2;
336 --
337 -- ----------------------------------------------------------------------------
338 --
339 function get_calling_context
340 return varchar2;
341 --
342 -- ---------------------------------------------------------------------------
343 -- |----------------------< get_phone_number >-----------------------------|
344 -- ---------------------------------------------------------------------------
345 -- Description:
346 -- Retrieve phone number by person_id, phone_type, and effective_date.
347 -- Different phone_types can be found in hr_common_lookups where lookup_type
348 -- = 'PHONE_TYPE'.
349 --
350
351 function get_phone_number
352 (p_person_id in number
353 ,p_phone_type in varchar2
354 ,p_effective_date in date default null)
355 return varchar2;
356 pragma restrict_references (get_phone_number, WNPS, WNDS);
357 --
358 -- ----------------------------------------------------------------------------
359 function chk_product_installed(p_application_id in number)
360 return varchar2;
361 -- ----------------------------------------------------------------------------
362 function get_user_status(p_assignment_status_type_id in number)
363 return varchar2;
364 -- ----------------------------------------------------------------------------
365 function DECODE_TERRITORY (p_territory_code varchar2) return varchar2;
366 ------------------------------------------------------------
367 function DECODE_ORGANIZATION (p_organization_id number) return varchar2;
368 ------------------------------------------------------------
369 function DECODE_AVAILABILITY_STATUS (p_availability_status_id number) return varchar2;
370 ------------------------------------------------------------
371 function DECODE_POSITION_CURRENT_NAME (p_position_id in number) return varchar2;
372 ------------------------------------------------------------
373 function DECODE_POSITION_LATEST_NAME (p_position_id in number,
374 p_effective_date in date default null
375 ) return varchar2;
376 ------------------------------------------------------------
377 function DECODE_STEP ( p_step_id number, p_effective_date date) return varchar2;
378 ------------------------------------------------------------
379 function DECODE_LATEST_POSITION_DEF_ID (p_position_id in number) return number;
380 ------------------------------------------------------------
381 function DECODE_AVAIL_STATUS_START_DATE (p_position_id in number,
382 p_availability_status_id number,p_effective_date date) return date;
383 ------------------------------------------------------------
384 function GET_POSITION_DATE_END (p_position_id in number) return date;
385 ------------------------------------------------------------
386 function DECODE_PERSON_NAME ( p_person_id number) return varchar2;
387 ------------------------------------------------------------
388 function DECODE_GRADE_RULE ( p_grade_rule_id number) return varchar2;
389 ------------------------------------------------------------------
390 -- Function to return localization specific lookup_type (validation_name)
391 -- for given core lookup_type (validation_type) or return core lookup_type
392 -- (validation_type) if none found.
393 --
394 function GET_VALIDATION_NAME(p_target_location VARCHAR2
395 ,p_field_name VARCHAR2
396 ,p_legislation_code VARCHAR2
397 ,p_validation_type VARCHAR2) return varchar2;
398 pragma restrict_references(get_validation_name, WNPS, WNDS);
399 -------------------------------------------------------------------
400 function decode_shared_type (
401 p_shared_type_id number) return varchar2;
402 -------------------------------------------------------------------
403 function get_xbg_profile return varchar2;
404 --pragma restrict_references (get_xbg_profile, WNPS, WNDS, RNPS, RNDS);
405 -------------------------------------------------------------------
406 function chk_maintain_tax_records return varchar2;
407 -- ---------------------------------------------------------------------------
408 -- |----------------------< describe_procedure >-----------------------------|
409 -- ---------------------------------------------------------------------------
410 -- Description:
411 -- Cover routine for DBMS_DESCRIBE.DESCRIBE_PROCEDURE. If describe_procedure
412 -- fails because a package is not compiled, it will attempt to compile the
413 -- package and call DESCRIBE_PROCEDURE again. Other DESCRIBE_PROCEDURE errors
414 -- will be propagated upwards.
415 -- Notes:
416 -- If the attempted package compilation fails, the standard -20003 exception
417 -- from DBMS_DESCRIBE.DESCRIBE_PROCEDURE will be returned. This procedure does
418 -- not raise any exceptions other then the ones raised by DESCRIBE_PROCEDURE.
419 -- ---------------------------------------------------------------------------
420 procedure describe_procedure
421 (object_name in varchar2
422 ,reserved1 in varchar2
423 ,reserved2 in varchar2
424 ,overload out nocopy dbms_describe.number_table
425 ,position out nocopy dbms_describe.number_table
426 ,level out nocopy dbms_describe.number_table
427 ,argument_name out nocopy dbms_describe.varchar2_table
431 ,length out nocopy dbms_describe.number_table
428 ,datatype out nocopy dbms_describe.number_table
429 ,default_value out nocopy dbms_describe.number_table
430 ,in_out out nocopy dbms_describe.number_table
432 ,precision out nocopy dbms_describe.number_table
433 ,scale out nocopy dbms_describe.number_table
434 ,radix out nocopy dbms_describe.number_table
435 ,spare out nocopy dbms_describe.number_table
436 );
437
438
439 end HR_GENERAL;
[Home] [Help]