1 PACKAGE Hr_General2 AS
2 /* $Header: hrgenrl2.pkh 120.4.12010000.2 2008/08/06 06:29:41 ubhat ship $ */
3 /*
4 +==========================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==========================================================================+
9 Name
10 General2 HR Utilities
11 Purpose
12 To provide widely used functions in a single shared area
13 Other general purpose routines can be found in hr_utility and
14 hr_chkfmt.
15
16 See the package body for details of the functions.
17
18 Change History
19 stlocke 08-FEB-2001 115.00 Created.
20 ekim 20-JUN-2001 115.1 Added mask_characters function.
21 asahay 23-JUL-2001 115.2 Added is_person_type function.
22 wstallar 28-SEP-2001 115.3 Added functions to support duplicate
23 person checking.
24 wstallar 28-SEP-2001 115.4 Added funtion to derive full name
25 for display in duplicate person LoV
26 acowan 27-FEB-2002 115.6 Added functions to return
27 assignments status usages
28 dcasemor 19-MAR-2002 115.8 Added chk_utf8_col_length.
29 dcasemo 15-APR-2002 115.9 Removed chk_utf8_col_length. This
30 now resides in a separate package
31 (hr_utf8_triggers).
32 acowan 16-MAY-2002 115.10 Added validate_upload procedure
33 for data checking entity uploads
34 skota 20-AUG-2002 115.11 GSCC changes
35 pkakar 15-OCT-2002 115.13 Added is_bg function for checking
36 the business_group_id is valid for
37 a specific legislation_code
38 (same as 115.12)
39 pkakar 16-OCT-2002 115.15 Added is_legislation_install for
40 checking to see if a certain
41 legislation has been installed
42 (same as 115.14)
43 prsundar 28-NOV-2002 115.16 Added overloaded procedure for
44 fnd_initload
45 gperry 10-DEC-2002 115.17 Added nocopy compiler directives.
46 dharris 06-Jan-2003 115.18 Added the PUBLIC
47 function get_oracle_db_version
48 pattwood 17-JAN-2003 115.19 Bug 2651140. Added set_ovn procedure
49 for populating the
50 object_version_number column value
51 when this column has been added to
52 an existing table. Code originally
53 located in hrsetovn.sql
54 fsheikh 30-JAN-2003 115.20 Bug 2706637. Added 3 JP legislation
55 specific parameter to retieve full
56 name as per JP format.
57 divicker 07-APR-2003 115.21 Default 3 JP leg parameters above to
58 make file back compatible for forms
59 divicker 07-APR-2003 115.22 Change from default to overload.
60 Forms compile but error at runtime
61 ASahay 09-SEP-2003 115.23 Added function is_location_legal_adr
62 sgudiwad 25-SEP-2003 115.24 3136986 Added function decode_vendor
63 njaladi 30-dec-2003 115.25 3257115 Added overloaded function for
64 is_duplicate_person for jp legislation
65 dcasemor 01-Mar-2004 115.26 Bug 3346940.
66 Added supervisor_assignments_in_use.
67 sgelvi 31-May-2006 115.27 Added hrms_efc_column function
68 risgupta 27-NOV-2006 115.30 Added two overloaded function for is_duplicate_person
69 and also defined a global PL/SQL table to hold
70 duplicate records for the fix of enh duplicate person
71 #3988762
72
73 ktithy 17-APR-2008 115.31 6961892 Added new procedure
74 SERVER_SIDE_PROFILE_PUT
75 which assigns a value to a profile
76 at Server Side.
77 ----------------------------------------------------------------------------
78 */
79
80 PROCEDURE init_fndload (p_resp_appl_id IN NUMBER);
81
82 PROCEDURE init_fndload(p_resp_appl_id IN NUMBER
83 ,p_user_id IN NUMBER);
84
85 FUNCTION mask_characters(p_number IN VARCHAR2)
86 RETURN VARCHAR2;
87
88 FUNCTION is_person_type(p_person_id IN NUMBER,
89 p_person_type IN VARCHAR2,
90 p_effective_date IN DATE)
91 RETURN BOOLEAN;
92 --
93 -- --------------------------------------------------------------------------
94 -- |---------------< is_duplicate_person( Legislation Specific>--------------|
95 -- --------------------------------------------------------------------------
96 -- {Start Of Comments}
97 --
98 -- Description:
99 -- This function checks for the person with same name or national identifier
100 -- exists in the system or not. if so then it returns true else false.
101 --
102 --
103 -- In Parameters:
104 -- Name Reqd Type Description
105 -- p_first_name Y varchar2 First name of the person
106 -- p_last_name Y varchar2 Last name of the person
107 -- p_national_identifier Y varchar2 National identifier of the
108 -- person
109 -- p_date_of birth Y Date Date of birth of the person.
110 -- p_leg_code Y Varchar2 Legislation code of the
111 -- Business group to which
112 -- Person being created.
113 -- p_last_name_phonetic Y varchar2 Phonetic Last name of the
114 -- person used for JP
115 -- legislation.
116 -- p_first_name_phonetic Y varchar2 Phonetic first name of the
117 -- person used for JP
118 -- legislation.
119 --
120 --
121 -- Access Status:
122 -- Internal Development Use Only.
123 --
124 -- {End Of Comments}
125 --
126 -- Added for the fix of #3257115
127 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
128 ,p_last_name IN VARCHAR2
129 ,p_national_identifier IN VARCHAR2
130 ,p_date_of_birth IN DATE
131 ,p_leg_code IN VARCHAR2
132 ,p_first_name_phonetic IN VARCHAR2
133 ,p_last_name_phonetic IN VARCHAR2)
134 RETURN BOOLEAN;
135 -- Added for the fix of #3257115
136 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
137 ,p_last_name IN VARCHAR2
138 ,p_national_identifier IN VARCHAR2
139 ,p_date_of_birth IN DATE)
140 RETURN BOOLEAN;
141
142 -- START added for the fix of enh duplicate person #3988762
143
144 type party_id_rec is record
145 (
146 r_party_id per_all_people_f.party_id%type
147 ,r_person_id per_all_people_f.person_id%type
148 ,r_sec_status varchar2(200)
149 ,r_global_name per_all_people_f.global_name%type
150 ,r_BG_name hr_all_organization_units.name%type
151 ,r_location_code hr_locations_all.location_code%type
152 ,r_org_name hr_all_organization_units.name%type
153 ,r_postal_code per_addresses.postal_code%type
154 ,r_national_identifier per_all_people_f.national_identifier%type
155 ,r_bg_id per_all_people_f.business_group_id%type
156 );
157
158 type party_id_tbl is table of party_id_rec
159 index by binary_integer;
160
161 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
162 ,p_last_name IN VARCHAR2
163 ,p_national_identifier IN VARCHAR2
164 ,p_date_of_birth IN DATE
165 ,p_global_name IN VARCHAR2
166 ,p_dup_tbl OUT nocopy hr_general2.party_id_tbl)
167 RETURN BOOLEAN;
168
169 PROCEDURE is_duplicate_person(
170 p_business_group_id in per_all_people_f.business_group_id%TYPE
171 ,p_first_name IN VARCHAR2
172 ,p_last_name IN VARCHAR2
173 ,p_national_identifier IN VARCHAR2
174 ,p_date_of_birth IN DATE
175 ,p_per_information1 VARCHAR2 DEFAULT NULL
176 ,p_per_information2 VARCHAR2 DEFAULT NULL
177 ,p_per_information3 VARCHAR2 DEFAULT NULL
178 ,p_per_information4 VARCHAR2 DEFAULT NULL
179 ,p_per_information5 VARCHAR2 DEFAULT NULL
180 ,p_per_information6 VARCHAR2 DEFAULT NULL
181 ,p_per_information7 VARCHAR2 DEFAULT NULL
182 ,p_per_information8 VARCHAR2 DEFAULT NULL
183 ,p_per_information9 VARCHAR2 DEFAULT NULL
184 ,p_per_information10 VARCHAR2 DEFAULT NULL
185 ,p_per_information11 VARCHAR2 DEFAULT NULL
186 ,p_per_information12 VARCHAR2 DEFAULT NULL
187 ,p_per_information13 VARCHAR2 DEFAULT NULL
188 ,p_per_information14 VARCHAR2 DEFAULT NULL
189 ,p_per_information15 VARCHAR2 DEFAULT NULL
190 ,p_per_information16 VARCHAR2 DEFAULT NULL
191 ,p_per_information17 VARCHAR2 DEFAULT NULL
192 ,p_per_information18 VARCHAR2 DEFAULT NULL
193 ,p_per_information19 VARCHAR2 DEFAULT NULL
194 ,p_per_information20 VARCHAR2 DEFAULT NULL
195 ,p_per_information21 VARCHAR2 DEFAULT NULL
196 ,p_per_information22 VARCHAR2 DEFAULT NULL
197 ,p_per_information23 VARCHAR2 DEFAULT NULL
198 ,p_per_information24 VARCHAR2 DEFAULT NULL
199 ,p_per_information25 VARCHAR2 DEFAULT NULL
200 ,p_per_information26 VARCHAR2 DEFAULT NULL
201 ,p_per_information27 VARCHAR2 DEFAULT NULL
202 ,p_per_information28 VARCHAR2 DEFAULT NULL
203 ,p_per_information29 VARCHAR2 DEFAULT NULL
204 ,p_per_information30 VARCHAR2 DEFAULT NULL
205 ,p_duplicate_exists out nocopy integer
206 ,p_dup_clob OUT nocopy CLOB
207 );
208
209 -- END added for the fix of enh duplicate person #3988762
210
211 FUNCTION get_dup_external_name
212 RETURN VARCHAR2;
213
214 FUNCTION get_dup_no_match
215 RETURN VARCHAR2;
216
217 FUNCTION get_dup_no_security_char
218 RETURN VARCHAR2;
219
220 FUNCTION get_dup_security_status(p_party_id IN NUMBER
221 ,p_business_group_id IN NUMBER)
222 RETURN VARCHAR2;
223
224 FUNCTION get_dup_full_name(p_title IN VARCHAR2
225 ,p_first_name in VARCHAR2
226 ,p_middle_name in VARCHAR2
227 ,p_last_name in VARCHAR2
228 ,p_suffix in VARCHAR2)
229 RETURN VARCHAR2;
230
231 FUNCTION get_dup_full_name(p_title IN VARCHAR2
232 ,p_first_name in VARCHAR2
233 ,p_middle_name in VARCHAR2
234 ,p_last_name in VARCHAR2
235 ,p_suffix in VARCHAR2
236 ,p_leg_code in varchar2
237 ,p_jp_fname varchar2
238 ,p_jp_lname varchar2)
239 RETURN VARCHAR2;
240
241 -- --------------------------------------------------------------------------
242 -- |-------------------------< show_status_type >---------------------------|
243 -- --------------------------------------------------------------------------
244 -- using the four flags.
245 -- this procedure is called from (but not limited to)
246 -- Assignments folder form
247
248
249 function show_status_type(p_status IN Varchar2
250 ,p_show_emp_flag in varchar2
251 ,p_show_apl_flag in varchar2
252 ,p_show_cwk_flag in varchar2
253 ,p_show_current_flag in varchar2)
254 RETURN Boolean;
255
256 -- --------------------------------------------------------------------------
257 -- |-------------------< return_status_assignment_type >--------------------|
258 -- --------------------------------------------------------------------------
259 --
260 --
261 -- This procedure returns various flags describing the valid usage of
262 -- a given assignment status type
263 -- e.g. TERM_ASSIGN refers to a non current employee assignment
264 -- so p_past_flag will be 'Y' and p_emp_flag will be 'Y'
265
266 procedure return_status_assignment_type
267 (p_status in varchar2
268 ,p_Current_flag out nocopy varchar2
269 ,p_past_flag out nocopy varchar2
270 ,p_cwk_flag out nocopy varchar2
271 ,p_emp_flag out nocopy varchar2
272 ,p_apl_flag out nocopy varchar2);
273
274 -- --------------------------------------------------------------------------
275 -- |-----------------------< return_status_types >--------------------------|
276 -- --------------------------------------------------------------------------
277 --
278 -- This function returns a list in the format ('ITEM1','ITEM2')
279 -- for use in the record group of assignment folder forms.
280 -- The items returned are those items from per_assignment_status_types
281 -- which show_status_type would return true for.
282
283 function return_status_types(p_show_emp_flag in varchar2
284 ,p_show_apl_flag in varchar2
285 ,p_show_cwk_flag in varchar2
286 ,p_show_current_flag in varchar2)
287 return varchar2;
288
289 -- --------------------------------------------------------------------------
290 -- |----------------< return_assignment_type_text >-------------------------|
291 -- --------------------------------------------------------------------------
292 --
293 -- This function returns the applicable assignment types for a given status
294
295 function return_assignment_type_text(p_status in varchar2)
296 return varchar2;
297 --
298 --
299 function validate_upload (
300 p_Upload_mode in varchar2,
301 p_Table_name in varchar2,
302 p_new_row_updated_by in varchar2,
303 p_new_row_update_date in date,
304 p_Table_key_name in varchar2,
305 p_table_key_value in varchar2)
306 return boolean;
307
308 -- --------------------------------------------------------------------------
309 -- |----------------------< IS_BG FUNCTION >--------------------------------|
310 -- --------------------------------------------------------------------------
311
315 --
312 --
313 -- This function checks to see if the business_group_id given is a valid id
314 -- for the legislation code. If it is valid, then true is returned
316
317 FUNCTION is_bg(
318 p_business_group_id in number,
319 p_legislation_code in varchar2)
320 return boolean;
321
322 -- --------------------------------------------------------------------------
323 -- |---------------< IS_LEGISLATION_INTSALL FUNCTION >----------------------|
324 -- --------------------------------------------------------------------------
325
326 --
327 -- This function checks to see if the legislation_code given has been
328 -- installed on the application
329 --
330
331 FUNCTION is_legislation_install(
332 p_application_short_name in varchar2,
333 p_legislation_code in varchar2)
334 return boolean;
335
336 -- --------------------------------------------------------------------------
337 -- |-------------------< get_oracle_db_version >----------------------------|
338 -- --------------------------------------------------------------------------
339 -- This function returns the current (major) ORACLE version number in the
340 -- format x.x (where x is a number).
341 FUNCTION get_oracle_db_version RETURN NUMBER;
342 --
343 -- --------------------------------------------------------------------------
344 -- |---------------------------------< set_ovn >----------------------------|
345 -- --------------------------------------------------------------------------
346 -- {Start Of Comments}
347 --
348 -- Description:
349 -- When a non-mandatory OBJECT_VERSION_NUMBER column is added to an
350 -- existing table this procedure should be called to populate the column
351 -- value for existing rows.
352 --
353 -- It is not necessary to call this procedure when the
354 -- OBJECT_VERSION_NUMBER column has been included in a new table.
355 --
356 -- Prerequisites:
357 -- A non-mandatory OBJECT_VERSION_NUMBER column, NUMBER datatype has been
358 -- added to a HRMS product table which could already contain data.
359 --
360 -- In Parameters:
361 -- Name Reqd Type Description
362 -- p_account_owner Y varchar2 Name of the database
363 -- account which owns the
364 -- database table.
365 -- p_table_name Y varchar2 'ALL' or the name of
366 -- one database table in
367 -- the owning account.
368 --
369 -- Post Success:
370 -- Where the OBJECT_VERSION_NUMBER column is null then an initial value
371 -- will be populated.
372 --
373 -- Post Failure:
374 -- An Application or RDBMS error will be raised. If the table has an
375 -- _OVN database trigger this may be left in a disabled state.
376 --
377 -- Access Status:
378 -- Internal Development Use Only.
379 --
380 -- {End Of Comments}
381 --
382 procedure set_ovn
383 (p_account_owner in varchar2
384 ,p_table_name in varchar2
385 );
386
387 -- --------------------------------------------------------------------------
388 -- |---------------< IS_LOCATION_LEGAL_ADR FUNCTION >----------------------|
389 -- --------------------------------------------------------------------------
390
391 --
392 -- This function checks to see if the location is a legal address
393 --
394 function is_location_legal_adr(p_location_id in NUMBER)
395 return BOOLEAN;
396
397 -- newly added vendor_id attribute decode function and fix for Bug#3136986
398 -----------------------------------------------------------------------
399 function DECODE_VENDOR ( p_vendor_id number) return varchar2 ;
400
401 -- --------------------------------------------------------------------------
402 -- |---------------< SUPERVISOR_ASSIGNMENTS_IN_USE >------------------------|
403 -- --------------------------------------------------------------------------
404
405 --
406 -- This function determines whether the current setup uses
407 -- supervisor assignments.
408 --
409 function supervisor_assignments_in_use
410 return VARCHAR2;
411 --
412 -- --------------------------------------------------------------------------
413 -- |---------------< HRMS_EFC_COLUMN >------------------------|
414 -- --------------------------------------------------------------------------
415 --
416 -- This function determines whether the column sent as parameter
417 -- is a candidate for EFC
418 --
419 function hrms_efc_column(p_table_name in VARCHAR2, p_column_name in VARCHAR2)
420 return VARCHAR2;
421 --
422
423
424 -- --------------------------------------------------------------------------
425 -- |----------------------< SERVER_SIDE_PROFILE_PUT >-----------------------|
426 -- --------------------------------------------------------------------------
427 -- {Start Of Comments}
428 --
429 -- Description:
430 -- This procedure is used to assign a value to a profile at Server Side.
431 --
432 -- In Parameters:
433 -- Name Reqd Type Description
434 -- NAME Y varchar2 Profile Name
435 -- which needs to be assigned a value.
436 --
437 -- VAL Y varchar2 Value for the Profile.
438 --
439 -- {End Of Comments}
443
440 --
441
442 procedure SERVER_SIDE_PROFILE_PUT(NAME in varchar2, VAL in varchar2);
444
445 END Hr_General2;
[Home] [Help]