1 PACKAGE Hr_General2 AUTHID CURRENT_USER AS
2 /* $Header: hrgenrl2.pkh 120.5.12020000.3 2012/10/11 09:11:41 sclakkar 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 karthmoh 05-DEC-2011 115.32 12774028 Added new function reporting_user
78 which returns true if the user is
79 a reporting user.
80 sclakkar 11-OCT-2012 120.5.12020000.3 modifed for Bug#14664920
81 ----------------------------------------------------------------------------
82 */
83
84 PROCEDURE init_fndload (p_resp_appl_id IN NUMBER);
85
86 PROCEDURE init_fndload(p_resp_appl_id IN NUMBER
87 ,p_user_id IN NUMBER);
88
89 FUNCTION mask_characters(p_number IN VARCHAR2)
90 RETURN VARCHAR2;
91
92 FUNCTION is_person_type(p_person_id IN NUMBER,
93 p_person_type IN VARCHAR2,
94 p_effective_date IN DATE)
95 RETURN BOOLEAN;
96 --
97 -- --------------------------------------------------------------------------
98 -- |---------------< is_duplicate_person( Legislation Specific>--------------|
99 -- --------------------------------------------------------------------------
100 -- {Start Of Comments}
101 --
102 -- Description:
103 -- This function checks for the person with same name or national identifier
104 -- exists in the system or not. if so then it returns true else false.
105 --
106 --
107 -- In Parameters:
108 -- Name Reqd Type Description
109 -- p_first_name Y varchar2 First name of the person
110 -- p_last_name Y varchar2 Last name of the person
111 -- p_national_identifier Y varchar2 National identifier of the
112 -- person
113 -- p_date_of birth Y Date Date of birth of the person.
114 -- p_leg_code Y Varchar2 Legislation code of the
115 -- Business group to which
116 -- Person being created.
117 -- p_last_name_phonetic Y varchar2 Phonetic Last name of the
118 -- person used for JP
119 -- legislation.
120 -- p_first_name_phonetic Y varchar2 Phonetic first name of the
121 -- person used for JP
122 -- legislation.
123 --
124 --
125 -- Access Status:
126 -- Internal Development Use Only.
127 --
128 -- {End Of Comments}
129 --
130 -- Added for the fix of #3257115
131 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
132 ,p_last_name IN VARCHAR2
133 ,p_national_identifier IN VARCHAR2
134 ,p_date_of_birth IN DATE
135 ,p_leg_code IN VARCHAR2
136 ,p_first_name_phonetic IN VARCHAR2
137 ,p_last_name_phonetic IN VARCHAR2)
138 RETURN BOOLEAN;
139 -- Added for the fix of #3257115
140 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
141 ,p_last_name IN VARCHAR2
142 ,p_national_identifier IN VARCHAR2
143 ,p_date_of_birth IN DATE)
144 RETURN BOOLEAN;
145
146 -- START added for the fix of enh duplicate person #3988762
147
148 type party_id_rec is record
149 (
150 r_party_id per_all_people_f.party_id%type
151 ,r_person_id per_all_people_f.person_id%type
152 ,r_sec_status varchar2(200)
153 ,r_global_name per_all_people_f.global_name%type
154 ,r_BG_name hr_all_organization_units.name%type
155 ,r_location_code hr_locations_all.location_code%type
156 ,r_org_name hr_all_organization_units.name%type
157 ,r_postal_code per_addresses.postal_code%type
158 ,r_national_identifier per_all_people_f.national_identifier%type
159 ,r_bg_id per_all_people_f.business_group_id%type
160 );
161
162 type party_id_tbl is table of party_id_rec
163 index by binary_integer;
164
165 FUNCTION is_duplicate_person(p_first_name IN VARCHAR2
166 ,p_last_name IN VARCHAR2
167 ,p_national_identifier IN VARCHAR2
168 ,p_date_of_birth IN DATE
169 ,p_global_name IN VARCHAR2
170 ,p_dup_tbl OUT nocopy hr_general2.party_id_tbl)
171 RETURN BOOLEAN;
172
173 PROCEDURE is_duplicate_person(
174 p_business_group_id in per_all_people_f.business_group_id%TYPE
175 ,p_first_name IN VARCHAR2
176 ,p_last_name IN VARCHAR2
177 ,p_national_identifier IN VARCHAR2
178 ,p_date_of_birth IN DATE
179 ,p_per_information1 VARCHAR2 DEFAULT NULL
180 ,p_per_information2 VARCHAR2 DEFAULT NULL
181 ,p_per_information3 VARCHAR2 DEFAULT NULL
182 ,p_per_information4 VARCHAR2 DEFAULT NULL
183 ,p_per_information5 VARCHAR2 DEFAULT NULL
184 ,p_per_information6 VARCHAR2 DEFAULT NULL
185 ,p_per_information7 VARCHAR2 DEFAULT NULL
186 ,p_per_information8 VARCHAR2 DEFAULT NULL
187 ,p_per_information9 VARCHAR2 DEFAULT NULL
188 ,p_per_information10 VARCHAR2 DEFAULT NULL
189 ,p_per_information11 VARCHAR2 DEFAULT NULL
190 ,p_per_information12 VARCHAR2 DEFAULT NULL
191 ,p_per_information13 VARCHAR2 DEFAULT NULL
192 ,p_per_information14 VARCHAR2 DEFAULT NULL
193 ,p_per_information15 VARCHAR2 DEFAULT NULL
194 ,p_per_information16 VARCHAR2 DEFAULT NULL
195 ,p_per_information17 VARCHAR2 DEFAULT NULL
196 ,p_per_information18 VARCHAR2 DEFAULT NULL
197 ,p_per_information19 VARCHAR2 DEFAULT NULL
198 ,p_per_information20 VARCHAR2 DEFAULT NULL
199 ,p_per_information21 VARCHAR2 DEFAULT NULL
200 ,p_per_information22 VARCHAR2 DEFAULT NULL
201 ,p_per_information23 VARCHAR2 DEFAULT NULL
202 ,p_per_information24 VARCHAR2 DEFAULT NULL
203 ,p_per_information25 VARCHAR2 DEFAULT NULL
204 ,p_per_information26 VARCHAR2 DEFAULT NULL
205 ,p_per_information27 VARCHAR2 DEFAULT NULL
206 ,p_per_information28 VARCHAR2 DEFAULT NULL
207 ,p_per_information29 VARCHAR2 DEFAULT NULL
208 ,p_per_information30 VARCHAR2 DEFAULT NULL
209 ,p_duplicate_exists out nocopy integer
210 ,p_dup_clob OUT nocopy CLOB
211 );
212
213 -- END added for the fix of enh duplicate person #3988762
214
215 FUNCTION get_dup_external_name
216 RETURN VARCHAR2;
217
218 FUNCTION get_dup_no_match
219 RETURN VARCHAR2;
220
221 FUNCTION get_dup_no_security_char
222 RETURN VARCHAR2;
223
224 FUNCTION get_dup_security_status(p_party_id IN NUMBER
225 ,p_business_group_id IN NUMBER
226 ,p_person_id IN NUMBER DEFAULT NULL) -- modified for Bug14664920
227 RETURN VARCHAR2;
228
229 FUNCTION get_dup_full_name(p_title IN VARCHAR2
230 ,p_first_name in VARCHAR2
231 ,p_middle_name in VARCHAR2
232 ,p_last_name in VARCHAR2
233 ,p_suffix in VARCHAR2)
234 RETURN VARCHAR2;
235
236 FUNCTION get_dup_full_name(p_title IN VARCHAR2
237 ,p_first_name in VARCHAR2
238 ,p_middle_name in VARCHAR2
239 ,p_last_name in VARCHAR2
240 ,p_suffix in VARCHAR2
241 ,p_leg_code in varchar2
242 ,p_jp_fname varchar2
243 ,p_jp_lname varchar2)
244 RETURN VARCHAR2;
245
246 -- --------------------------------------------------------------------------
247 -- |-------------------------< show_status_type >---------------------------|
248 -- --------------------------------------------------------------------------
249 -- using the four flags.
250 -- this procedure is called from (but not limited to)
251 -- Assignments folder form
252
253
254 function show_status_type(p_status IN Varchar2
255 ,p_show_emp_flag in varchar2
256 ,p_show_apl_flag in varchar2
257 ,p_show_cwk_flag in varchar2
258 ,p_show_current_flag in varchar2)
259 RETURN Boolean;
260
261 -- --------------------------------------------------------------------------
262 -- |-------------------< return_status_assignment_type >--------------------|
263 -- --------------------------------------------------------------------------
264 --
265 --
266 -- This procedure returns various flags describing the valid usage of
267 -- a given assignment status type
268 -- e.g. TERM_ASSIGN refers to a non current employee assignment
269 -- so p_past_flag will be 'Y' and p_emp_flag will be 'Y'
270
271 procedure return_status_assignment_type
272 (p_status in varchar2
273 ,p_Current_flag out nocopy varchar2
274 ,p_past_flag out nocopy varchar2
275 ,p_cwk_flag out nocopy varchar2
276 ,p_emp_flag out nocopy varchar2
277 ,p_apl_flag out nocopy varchar2);
278
279 -- --------------------------------------------------------------------------
280 -- |-----------------------< return_status_types >--------------------------|
281 -- --------------------------------------------------------------------------
282 --
283 -- This function returns a list in the format ('ITEM1','ITEM2')
284 -- for use in the record group of assignment folder forms.
285 -- The items returned are those items from per_assignment_status_types
286 -- which show_status_type would return true for.
287
288 function return_status_types(p_show_emp_flag in varchar2
289 ,p_show_apl_flag in varchar2
290 ,p_show_cwk_flag in varchar2
291 ,p_show_current_flag in varchar2)
292 return varchar2;
293
294 -- --------------------------------------------------------------------------
295 -- |----------------< return_assignment_type_text >-------------------------|
296 -- --------------------------------------------------------------------------
297 --
298 -- This function returns the applicable assignment types for a given status
299
300 function return_assignment_type_text(p_status in varchar2)
301 return varchar2;
302 --
303 --
304 function validate_upload (
305 p_Upload_mode in varchar2,
306 p_Table_name in varchar2,
307 p_new_row_updated_by in varchar2,
308 p_new_row_update_date in date,
309 p_Table_key_name in varchar2,
310 p_table_key_value in varchar2)
311 return boolean;
312
313 -- --------------------------------------------------------------------------
314 -- |----------------------< IS_BG FUNCTION >--------------------------------|
315 -- --------------------------------------------------------------------------
316
317 --
318 -- This function checks to see if the business_group_id given is a valid id
319 -- for the legislation code. If it is valid, then true is returned
320 --
321
322 FUNCTION is_bg(
323 p_business_group_id in number,
324 p_legislation_code in varchar2)
325 return boolean;
326
327 -- --------------------------------------------------------------------------
328 -- |---------------< IS_LEGISLATION_INTSALL FUNCTION >----------------------|
329 -- --------------------------------------------------------------------------
330
331 --
332 -- This function checks to see if the legislation_code given has been
333 -- installed on the application
334 --
335
336 FUNCTION is_legislation_install(
337 p_application_short_name in varchar2,
338 p_legislation_code in varchar2)
339 return boolean;
340
341 -- --------------------------------------------------------------------------
342 -- |-------------------< get_oracle_db_version >----------------------------|
343 -- --------------------------------------------------------------------------
344 -- This function returns the current (major) ORACLE version number in the
345 -- format x.x (where x is a number).
346 FUNCTION get_oracle_db_version RETURN NUMBER;
347 --
348 -- --------------------------------------------------------------------------
349 -- |---------------------------------< set_ovn >----------------------------|
350 -- --------------------------------------------------------------------------
351 -- {Start Of Comments}
352 --
353 -- Description:
354 -- When a non-mandatory OBJECT_VERSION_NUMBER column is added to an
355 -- existing table this procedure should be called to populate the column
356 -- value for existing rows.
357 --
358 -- It is not necessary to call this procedure when the
359 -- OBJECT_VERSION_NUMBER column has been included in a new table.
360 --
361 -- Prerequisites:
362 -- A non-mandatory OBJECT_VERSION_NUMBER column, NUMBER datatype has been
363 -- added to a HRMS product table which could already contain data.
364 --
365 -- In Parameters:
366 -- Name Reqd Type Description
367 -- p_account_owner Y varchar2 Name of the database
368 -- account which owns the
369 -- database table.
370 -- p_table_name Y varchar2 'ALL' or the name of
371 -- one database table in
372 -- the owning account.
373 --
374 -- Post Success:
375 -- Where the OBJECT_VERSION_NUMBER column is null then an initial value
376 -- will be populated.
377 --
378 -- Post Failure:
379 -- An Application or RDBMS error will be raised. If the table has an
380 -- _OVN database trigger this may be left in a disabled state.
381 --
382 -- Access Status:
383 -- Internal Development Use Only.
384 --
385 -- {End Of Comments}
386 --
387 procedure set_ovn
388 (p_account_owner in varchar2
389 ,p_table_name in varchar2
390 );
391
392 -- --------------------------------------------------------------------------
393 -- |---------------< IS_LOCATION_LEGAL_ADR FUNCTION >----------------------|
394 -- --------------------------------------------------------------------------
395
396 --
397 -- This function checks to see if the location is a legal address
398 --
399 function is_location_legal_adr(p_location_id in NUMBER)
400 return BOOLEAN;
401
402 -- newly added vendor_id attribute decode function and fix for Bug#3136986
403 -----------------------------------------------------------------------
404 function DECODE_VENDOR ( p_vendor_id number) return varchar2 ;
405
406 -- --------------------------------------------------------------------------
407 -- |---------------< SUPERVISOR_ASSIGNMENTS_IN_USE >------------------------|
408 -- --------------------------------------------------------------------------
409
410 --
411 -- This function determines whether the current setup uses
412 -- supervisor assignments.
413 --
414 function supervisor_assignments_in_use
415 return VARCHAR2;
416 --
417 -- --------------------------------------------------------------------------
418 -- |---------------< HRMS_EFC_COLUMN >------------------------|
419 -- --------------------------------------------------------------------------
420 --
421 -- This function determines whether the column sent as parameter
422 -- is a candidate for EFC
423 --
424 function hrms_efc_column(p_table_name in VARCHAR2, p_column_name in VARCHAR2)
425 return VARCHAR2;
426 --
427
428
429 -- --------------------------------------------------------------------------
430 -- |----------------------< SERVER_SIDE_PROFILE_PUT >-----------------------|
431 -- --------------------------------------------------------------------------
432 -- {Start Of Comments}
433 --
434 -- Description:
435 -- This procedure is used to assign a value to a profile at Server Side.
436 --
437 -- In Parameters:
438 -- Name Reqd Type Description
439 -- NAME Y varchar2 Profile Name
440 -- which needs to be assigned a value.
441 --
442 -- VAL Y varchar2 Value for the Profile.
443 --
444 -- {End Of Comments}
445 --
446
447 procedure SERVER_SIDE_PROFILE_PUT(NAME in varchar2, VAL in varchar2);
448
449 --Added for the bug#12774028
450 function reporting_user
451 return boolean;
452
453
454 END Hr_General2;
[Home] [Help]