[Home] [Help]
PACKAGE BODY: APPS.HR_GENERAL_UTILITIES
Source
1 PACKAGE BODY HR_GENERAL_UTILITIES AS
2 /* $Header: hrgenutw.pkb 115.34 2004/03/18 01:35:54 rmoolave ship $*/
3 -- ----------------------------------------------------------------------------
7 l_industry varchar2(50);
4 -- |--< VARIABLES >-----------------------------------------------------------|
5 -- ----------------------------------------------------------------------------
6 l_status varchar2(50);
8 l_per_owner varchar2(30);
9 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
10 l_industry, l_per_owner);
11 g_fatal_error EXCEPTION;
12 PRAGMA EXCEPTION_INIT(g_fatal_error, -20001);
13 l_val_person_id per_people_f.person_id%TYPE;
14 g_replace_string VARCHAR2 (2) := '!#';
15 g_space varchar2(1) := ' ';
16 g_src_attribute VARCHAR2 (200) := ' SRC="'
17 || g_replace_string || '"';
18 g_type_attribute VARCHAR2 (200) := ' TYPE="'
19 || g_replace_string || '"';
20 g_language_attribute VARCHAR2 (200) := ' LANGUAGE="'
21 || g_replace_string || '"';
22 g_execute_handles g_varchar2_tab_type;
23 g_debug boolean := hr_utility.debug_enabled;
24 g_string_cache g_vc32k_tab_type;
25 c_error_msg varchar2 (2000);
26 c_login_msg varchar2 (2000);
27 --
28 -------------------------------------------------------------------------------
29 -- 04/08/2001 Bug 1713366 Fix:
30 -- The following global constant is for testing the old BPFD and the new BPFD (
31 -- which has an output parameter). This constant will indicate that beyond
32 -- certain number of parameters, the old BPFD which will create dynamic SQL with
33 -- literal will be used. For example, set g_num_dynamic_sql_parms to -1 will
34 -- force to use Execute_Dynamic_SQL procedure which uses dbms_sql package.
35 -------------------------------------------------------------------------------
36 g_num_dynamic_sql_parms constant number := 15;
37 --
38 -- ----------------------------------------------------------------------------
39 -- |--< reset_globals >-------------------------------------------------------|
40 -- | This procedure will be called at the end of rendering the work space |
41 -- | frame so that the global variables will have the initialized values in |
42 -- | WebDB stateful connection. |
43 -- ----------------------------------------------------------------------------
44 PROCEDURE reset_globals
45 IS
46 l_reset g_vc32k_tab_type;
47
48 BEGIN
49 l_val_person_id := null;
50 g_replace_string := '!#';
51 g_space := ' ';
52 g_src_attribute := ' SRC="' || g_replace_string || '"';
53 g_type_attribute := ' TYPE="' || g_replace_string || '"';
54 g_language_attribute := ' LANGUAGE="' || g_replace_string || '"';
55 g_execute_handles.delete;
56 g_string_cache.delete;
57 hr_general_utilities.g_separator := '!#';
58 hr_general_utilities.g_sysdate_char := to_char(trunc(sysdate), 'YYYY-MM-DD');
59 hr_general_utilities.g_current_yr_char :=
60 substr(hr_general_utilities.g_sysdate_char, 1, 4);
61 hr_general_utilities.g_sample_date_char :=
62 hr_general_utilities.g_current_yr_char || '-12-31';
63 hr_general_utilities.g_sample_date :=
64 to_date(hr_general_utilities.g_sample_date_char, 'YYYY-MM-DD');
65 hr_general_utilities.g_date_format :=
66 hr_session_utilities.get_user_date_format;
67 hr_general_utilities.g_attribute_application_id := 800;
68
69 --
70 END reset_globals;
71 --
72 --
73 -- ----------------------------------------------------------------------------
74 -- |--< Get_Person_Record >---------------------------------------------------|
75 -- ----------------------------------------------------------------------------
76 FUNCTION Get_Person_Record
77 ( p_person_id IN per_people_f.person_id%TYPE
78 , p_effective_date IN DATE DEFAULT SYSDATE
79 )
80 RETURN per_people_f%ROWTYPE
81 IS
82 l_person_rec per_people_f%ROWTYPE;
83 l_proc VARCHAR2 (72);
84
85 l_all_person_rec per_all_people_f%ROWTYPE;
86 --
87 CURSOR csr_people_rec (p_person_id per_people_f.person_id%TYPE)
88 IS
89 SELECT *
90 FROM per_all_people_f -- Fix 2082000
91 WHERE person_id = p_person_id
92 AND trunc(p_effective_date)
93 BETWEEN trunc(effective_start_date) AND trunc(effective_end_date);
94 BEGIN
95
96 g_debug := hr_utility.debug_enabled;
97 IF g_debug THEN
98 l_proc := g_package || ' Get_Person_Record';
99 hr_utility.set_location('Entering : ' || l_proc, 5);
100 END IF;
101
102
103 OPEN csr_people_rec (p_person_id);
104 FETCH csr_people_rec INTO l_all_person_rec;
105 IF csr_people_rec%FOUND THEN
106 CLOSE csr_people_rec;
107
108
109 IF g_debug THEN
110 hr_utility.set_location('Leaving : ' || l_proc, 10);
111 END IF;
112
113
114 -- Fix 2082000 start
115
116 l_person_rec.PERSON_ID := l_all_person_rec.PERSON_ID ;
117 l_person_rec.EFFECTIVE_START_DATE := l_all_person_rec.EFFECTIVE_START_DATE ;
118 l_person_rec.EFFECTIVE_END_DATE := l_all_person_rec.EFFECTIVE_END_DATE ;
119 l_person_rec.BUSINESS_GROUP_ID := l_all_person_rec.BUSINESS_GROUP_ID ;
120 l_person_rec.PERSON_TYPE_ID := l_all_person_rec.PERSON_TYPE_ID ;
121 l_person_rec.LAST_NAME := l_all_person_rec.LAST_NAME ;
122 l_person_rec.START_DATE := l_all_person_rec.START_DATE ;
123 l_person_rec.APPLICANT_NUMBER := l_all_person_rec.APPLICANT_NUMBER ;
124 l_person_rec.COMMENT_ID := l_all_person_rec.COMMENT_ID ;
125 l_person_rec.CURRENT_APPLICANT_FLAG := l_all_person_rec.CURRENT_APPLICANT_FLAG ;
126 l_person_rec.CURRENT_EMP_OR_APL_FLAG := l_all_person_rec.CURRENT_EMP_OR_APL_FLAG ;
127 l_person_rec.CURRENT_EMPLOYEE_FLAG := l_all_person_rec.CURRENT_EMPLOYEE_FLAG ;
128 l_person_rec.DATE_EMPLOYEE_DATA_VERIFIED := l_all_person_rec.DATE_EMPLOYEE_DATA_VERIFIED ;
129 l_person_rec.DATE_OF_BIRTH := l_all_person_rec.DATE_OF_BIRTH ;
130 l_person_rec.EMAIL_ADDRESS := l_all_person_rec.EMAIL_ADDRESS ;
131 l_person_rec.EMPLOYEE_NUMBER := l_all_person_rec.EMPLOYEE_NUMBER ;
132 l_person_rec.EXPENSE_CHECK_SEND_TO_ADDRESS := l_all_person_rec.EXPENSE_CHECK_SEND_TO_ADDRESS ;
133 l_person_rec.FAST_PATH_EMPLOYEE := l_all_person_rec.FAST_PATH_EMPLOYEE ;
134 l_person_rec.FIRST_NAME := l_all_person_rec.FIRST_NAME ;
135 l_person_rec.FULL_NAME := l_all_person_rec.FULL_NAME ;
136 l_person_rec.ORDER_NAME := l_all_person_rec.ORDER_NAME ;
137 l_person_rec.KNOWN_AS := l_all_person_rec.KNOWN_AS ;
138 l_person_rec.MARITAL_STATUS := l_all_person_rec.MARITAL_STATUS ;
139 l_person_rec.MIDDLE_NAMES := l_all_person_rec.MIDDLE_NAMES ;
140 l_person_rec.NATIONALITY := l_all_person_rec.NATIONALITY ;
141 l_person_rec.NATIONAL_IDENTIFIER := l_all_person_rec.NATIONAL_IDENTIFIER ;
142 l_person_rec.PREVIOUS_LAST_NAME := l_all_person_rec.PREVIOUS_LAST_NAME ;
143 l_person_rec.REGISTERED_DISABLED_FLAG := l_all_person_rec.REGISTERED_DISABLED_FLAG ;
144 l_person_rec.SEX := l_all_person_rec.SEX ;
145 l_person_rec.TITLE := l_all_person_rec.TITLE ;
146 l_person_rec.VENDOR_ID := l_all_person_rec.VENDOR_ID ;
147 l_person_rec.WORK_TELEPHONE := l_all_person_rec.WORK_TELEPHONE ;
148 l_person_rec.REQUEST_ID := l_all_person_rec.REQUEST_ID ;
149 l_person_rec.PROGRAM_APPLICATION_ID := l_all_person_rec.PROGRAM_APPLICATION_ID ;
150 l_person_rec.PROGRAM_ID := l_all_person_rec.PROGRAM_ID ;
151 l_person_rec.PROGRAM_UPDATE_DATE := l_all_person_rec.PROGRAM_UPDATE_DATE ;
152 l_person_rec.ATTRIBUTE_CATEGORY := l_all_person_rec.ATTRIBUTE_CATEGORY ;
153 l_person_rec.ATTRIBUTE1 := l_all_person_rec.ATTRIBUTE1 ;
154 l_person_rec.ATTRIBUTE2 := l_all_person_rec.ATTRIBUTE2 ;
155 l_person_rec.ATTRIBUTE3 := l_all_person_rec.ATTRIBUTE3 ;
156 l_person_rec.ATTRIBUTE4 := l_all_person_rec.ATTRIBUTE4 ;
157 l_person_rec.ATTRIBUTE5 := l_all_person_rec.ATTRIBUTE5 ;
158 l_person_rec.ATTRIBUTE6 := l_all_person_rec.ATTRIBUTE6 ;
159 l_person_rec.ATTRIBUTE7 := l_all_person_rec.ATTRIBUTE7 ;
160 l_person_rec.ATTRIBUTE8 := l_all_person_rec.ATTRIBUTE8 ;
161 l_person_rec.ATTRIBUTE9 := l_all_person_rec.ATTRIBUTE9 ;
162 l_person_rec.ATTRIBUTE10 := l_all_person_rec.ATTRIBUTE10 ;
163 l_person_rec.ATTRIBUTE11 := l_all_person_rec.ATTRIBUTE11 ;
164 l_person_rec.ATTRIBUTE12 := l_all_person_rec.ATTRIBUTE12 ;
165 l_person_rec.ATTRIBUTE13 := l_all_person_rec.ATTRIBUTE13 ;
166 l_person_rec.ATTRIBUTE14 := l_all_person_rec.ATTRIBUTE14 ;
167 l_person_rec.ATTRIBUTE15 := l_all_person_rec.ATTRIBUTE15 ;
168 l_person_rec.ATTRIBUTE16 := l_all_person_rec.ATTRIBUTE16 ;
169 l_person_rec.ATTRIBUTE17 := l_all_person_rec.ATTRIBUTE17 ;
170 l_person_rec.ATTRIBUTE18 := l_all_person_rec.ATTRIBUTE18 ;
171 l_person_rec.ATTRIBUTE19 := l_all_person_rec.ATTRIBUTE19 ;
172 l_person_rec.ATTRIBUTE20 := l_all_person_rec.ATTRIBUTE20 ;
173 l_person_rec.ATTRIBUTE21 := l_all_person_rec.ATTRIBUTE21 ;
174 l_person_rec.ATTRIBUTE22 := l_all_person_rec.ATTRIBUTE22 ;
175 l_person_rec.ATTRIBUTE23 := l_all_person_rec.ATTRIBUTE23 ;
176 l_person_rec.ATTRIBUTE24 := l_all_person_rec.ATTRIBUTE24 ;
177 l_person_rec.ATTRIBUTE25 := l_all_person_rec.ATTRIBUTE25 ;
178 l_person_rec.ATTRIBUTE26 := l_all_person_rec.ATTRIBUTE26 ;
179 l_person_rec.ATTRIBUTE27 := l_all_person_rec.ATTRIBUTE27 ;
180 l_person_rec.ATTRIBUTE28 := l_all_person_rec.ATTRIBUTE28 ;
181 l_person_rec.ATTRIBUTE29 := l_all_person_rec.ATTRIBUTE29 ;
182 l_person_rec.ATTRIBUTE30 := l_all_person_rec.ATTRIBUTE30 ;
183 l_person_rec.LAST_UPDATE_DATE := l_all_person_rec.LAST_UPDATE_DATE ;
184 l_person_rec.LAST_UPDATED_BY := l_all_person_rec.LAST_UPDATED_BY ;
185 l_person_rec.LAST_UPDATE_LOGIN := l_all_person_rec.LAST_UPDATE_LOGIN ;
186 l_person_rec.CREATED_BY := l_all_person_rec.CREATED_BY ;
187 l_person_rec.CREATION_DATE := l_all_person_rec.CREATION_DATE ;
188 l_person_rec.PER_INFORMATION_CATEGORY := l_all_person_rec.PER_INFORMATION_CATEGORY ;
189 l_person_rec.PER_INFORMATION1 := l_all_person_rec.PER_INFORMATION1 ;
190 l_person_rec.PER_INFORMATION2 := l_all_person_rec.PER_INFORMATION2 ;
191 l_person_rec.PER_INFORMATION3 := l_all_person_rec.PER_INFORMATION3 ;
192 l_person_rec.PER_INFORMATION4 := l_all_person_rec.PER_INFORMATION4 ;
193 l_person_rec.PER_INFORMATION5 := l_all_person_rec.PER_INFORMATION5 ;
194 l_person_rec.PER_INFORMATION6 := l_all_person_rec.PER_INFORMATION6 ;
195 l_person_rec.PER_INFORMATION7 := l_all_person_rec.PER_INFORMATION7 ;
196 l_person_rec.PER_INFORMATION8 := l_all_person_rec.PER_INFORMATION8 ;
197 l_person_rec.PER_INFORMATION9 := l_all_person_rec.PER_INFORMATION9 ;
198 l_person_rec.PER_INFORMATION10 := l_all_person_rec.PER_INFORMATION10 ;
199 l_person_rec.PER_INFORMATION11 := l_all_person_rec.PER_INFORMATION11 ;
200 l_person_rec.PER_INFORMATION12 := l_all_person_rec.PER_INFORMATION12 ;
201 l_person_rec.PER_INFORMATION13 := l_all_person_rec.PER_INFORMATION13 ;
202 l_person_rec.PER_INFORMATION14 := l_all_person_rec.PER_INFORMATION14 ;
203 l_person_rec.PER_INFORMATION15 := l_all_person_rec.PER_INFORMATION15 ;
204 l_person_rec.PER_INFORMATION16 := l_all_person_rec.PER_INFORMATION16 ;
205 l_person_rec.PER_INFORMATION17 := l_all_person_rec.PER_INFORMATION17 ;
206 l_person_rec.PER_INFORMATION18 := l_all_person_rec.PER_INFORMATION18 ;
207 l_person_rec.PER_INFORMATION19 := l_all_person_rec.PER_INFORMATION19 ;
208 l_person_rec.PER_INFORMATION20 := l_all_person_rec.PER_INFORMATION20 ;
209 l_person_rec.PER_INFORMATION21 := l_all_person_rec.PER_INFORMATION21 ;
210 l_person_rec.PER_INFORMATION22 := l_all_person_rec.PER_INFORMATION22 ;
211 l_person_rec.PER_INFORMATION23 := l_all_person_rec.PER_INFORMATION23 ;
212 l_person_rec.PER_INFORMATION24 := l_all_person_rec.PER_INFORMATION24 ;
213 l_person_rec.PER_INFORMATION25 := l_all_person_rec.PER_INFORMATION25 ;
214 l_person_rec.PER_INFORMATION26 := l_all_person_rec.PER_INFORMATION26 ;
215 l_person_rec.PER_INFORMATION27 := l_all_person_rec.PER_INFORMATION27 ;
216 l_person_rec.PER_INFORMATION28 := l_all_person_rec.PER_INFORMATION28 ;
217 l_person_rec.PER_INFORMATION29 := l_all_person_rec.PER_INFORMATION29 ;
218 l_person_rec.PER_INFORMATION30 := l_all_person_rec.PER_INFORMATION30 ;
219 l_person_rec.OBJECT_VERSION_NUMBER := l_all_person_rec.OBJECT_VERSION_NUMBER ;
220 l_person_rec.DATE_OF_DEATH := l_all_person_rec.DATE_OF_DEATH ;
221 l_person_rec.SUFFIX := l_all_person_rec.SUFFIX ;
222 l_person_rec.WORK_SCHEDULE := l_all_person_rec.WORK_SCHEDULE ;
223 l_person_rec.CORRESPONDENCE_LANGUAGE := l_all_person_rec.CORRESPONDENCE_LANGUAGE ;
224 l_person_rec.STUDENT_STATUS := l_all_person_rec.STUDENT_STATUS ;
225 l_person_rec.FTE_CAPACITY := l_all_person_rec.FTE_CAPACITY ;
226 l_person_rec.ON_MILITARY_SERVICE := l_all_person_rec.ON_MILITARY_SERVICE ;
227 l_person_rec.SECOND_PASSPORT_EXISTS := l_all_person_rec.SECOND_PASSPORT_EXISTS ;
231 l_person_rec.LAST_MEDICAL_TEST_DATE := l_all_person_rec.LAST_MEDICAL_TEST_DATE ;
228 l_person_rec.BACKGROUND_CHECK_STATUS := l_all_person_rec.BACKGROUND_CHECK_STATUS ;
229 l_person_rec.BACKGROUND_DATE_CHECK := l_all_person_rec.BACKGROUND_DATE_CHECK ;
230 l_person_rec.BLOOD_TYPE := l_all_person_rec.BLOOD_TYPE ;
232 l_person_rec.LAST_MEDICAL_TEST_BY := l_all_person_rec.LAST_MEDICAL_TEST_BY ;
233 l_person_rec.REHIRE_RECOMMENDATION := l_all_person_rec.REHIRE_RECOMMENDATION ;
234 l_person_rec.REHIRE_AUTHORIZOR := l_all_person_rec.REHIRE_AUTHORIZOR ;
235 l_person_rec.REHIRE_REASON := l_all_person_rec.REHIRE_REASON ;
236 l_person_rec.RESUME_EXISTS := l_all_person_rec.RESUME_EXISTS ;
237 l_person_rec.RESUME_LAST_UPDATED := l_all_person_rec.RESUME_LAST_UPDATED ;
238 l_person_rec.OFFICE_NUMBER := l_all_person_rec.OFFICE_NUMBER ;
239 l_person_rec.INTERNAL_LOCATION := l_all_person_rec.INTERNAL_LOCATION ;
240 l_person_rec.MAILSTOP := l_all_person_rec.MAILSTOP ;
241 l_person_rec.PROJECTED_START_DATE := l_all_person_rec.PROJECTED_START_DATE ;
242 l_person_rec.HONORS := l_all_person_rec.HONORS ;
243 l_person_rec.PRE_NAME_ADJUNCT := l_all_person_rec.PRE_NAME_ADJUNCT ;
244 l_person_rec.HOLD_APPLICANT_DATE_UNTIL := l_all_person_rec.HOLD_APPLICANT_DATE_UNTIL ;
245 l_person_rec.COORD_BEN_MED_PLN_NO := l_all_person_rec.COORD_BEN_MED_PLN_NO ;
246 l_person_rec.COORD_BEN_NO_CVG_FLAG := l_all_person_rec.COORD_BEN_NO_CVG_FLAG ;
247 l_person_rec.DPDNT_ADOPTION_DATE := l_all_person_rec.DPDNT_ADOPTION_DATE ;
248 l_person_rec.DPDNT_VLNTRY_SVCE_FLAG := l_all_person_rec.DPDNT_VLNTRY_SVCE_FLAG ;
249 l_person_rec.RECEIPT_OF_DEATH_CERT_DATE := l_all_person_rec.RECEIPT_OF_DEATH_CERT_DATE ;
250 l_person_rec.USES_TOBACCO_FLAG := l_all_person_rec.USES_TOBACCO_FLAG ;
251 l_person_rec.BENEFIT_GROUP_ID := l_all_person_rec.BENEFIT_GROUP_ID ;
252 l_person_rec.ORIGINAL_DATE_OF_HIRE := l_all_person_rec.ORIGINAL_DATE_OF_HIRE ;
253 l_person_rec.TOWN_OF_BIRTH := l_all_person_rec.TOWN_OF_BIRTH ;
254 l_person_rec.REGION_OF_BIRTH := l_all_person_rec.REGION_OF_BIRTH ;
255 l_person_rec.COUNTRY_OF_BIRTH := l_all_person_rec.COUNTRY_OF_BIRTH ;
256 l_person_rec.GLOBAL_PERSON_ID := l_all_person_rec.GLOBAL_PERSON_ID ;
257 l_person_rec.PARTY_ID := l_all_person_rec.PARTY_ID ;
258 l_person_rec.COORD_BEN_MED_EXT_ER := l_all_person_rec.COORD_BEN_MED_EXT_ER ;
259 l_person_rec.COORD_BEN_MED_PL_NAME := l_all_person_rec.COORD_BEN_MED_PL_NAME ;
260 l_person_rec.COORD_BEN_MED_INSR_CRR_NAME := l_all_person_rec.COORD_BEN_MED_INSR_CRR_NAME ;
261 l_person_rec.COORD_BEN_MED_INSR_CRR_IDENT := l_all_person_rec.COORD_BEN_MED_INSR_CRR_IDENT ;
262 l_person_rec.COORD_BEN_MED_CVG_STRT_DT := l_all_person_rec.COORD_BEN_MED_CVG_STRT_DT ;
263 l_person_rec.COORD_BEN_MED_CVG_END_DT := l_all_person_rec.COORD_BEN_MED_CVG_END_DT ;
264 l_person_rec.NPW_NUMBER := l_all_person_rec.NPW_NUMBER ;
265 l_person_rec.CURRENT_NPW_FLAG := l_all_person_rec.CURRENT_NPW_FLAG ;
266
267 -- Fix 2082000 End.
268
269 RETURN l_person_rec;
270 ELSE
271 CLOSE csr_people_rec;
272
273 RAISE hr_session_utilities.g_fatal_error;
274 END IF;
275 --
276 EXCEPTION
277 WHEN OTHERS THEN
278 hr_utility.set_message
279 ( hr_session_utilities.g_PER_application_id
280 , 'HR_52214_CM_NO_PERSON_DATA'
281 );
282 RAISE ;
283 END Get_Person_Record;
284 -- ----------------------------------------------------------------------------
285 -- |--< Get_Person_Details >---------------------------------------------------|
286 -- ----------------------------------------------------------------------------
287 FUNCTION Get_Person_Details
288 ( p_person_id IN per_all_people_f.person_id%TYPE
289 , p_effective_date IN DATE DEFAULT SYSDATE
290 )
291 RETURN g_person_details_rec_type
292 IS
293 l_person_rec g_person_details_rec_type;
294 l_proc VARCHAR2 (72);
295 --
296 CURSOR csr_people_rec (p_person_id per_all_people_f.person_id%TYPE)
297 IS
298 -- fix 2209594
299 SELECT last_name
300 ,first_name
301 ,full_name
302 ,middle_names
303 ,previous_last_name
304 ,suffix
305 ,title
306 ,business_group_id
307 FROM per_all_people_f
308 WHERE person_id = p_person_id
309 AND trunc(p_effective_date)
310 BETWEEN trunc(effective_start_date) AND trunc(effective_end_date);
311 BEGIN
312 g_debug := hr_utility.debug_enabled;
313 IF g_debug THEN
314 l_proc := g_package || ' Get_Person_Record';
315 hr_utility.set_location('Entering : ' || l_proc, 5);
316 END IF;
317
318
319 OPEN csr_people_rec (p_person_id);
320 FETCH csr_people_rec INTO l_person_rec;
321 IF csr_people_rec%FOUND THEN
322 CLOSE csr_people_rec;
323
324 IF g_debug THEN
325 hr_utility.set_location('Leaving : ' || l_proc, 10);
326 END IF;
327
328 RETURN l_person_rec;
329 ELSE
330 CLOSE csr_people_rec;
331 RAISE hr_session_utilities.g_fatal_error;
332 END IF;
333 --
337 ( hr_session_utilities.g_PER_application_id
334 EXCEPTION
335 WHEN OTHERS THEN
336 hr_utility.set_message
338 , 'HR_52214_CM_NO_PERSON_DATA'
339 );
340 RAISE ;
341 END Get_Person_Details;
342 -- ----------------------------------------------------------------------------
343 -- |--< Get_Business_Group >--------------------------------------------------|
344 -- ----------------------------------------------------------------------------
345 FUNCTION Get_Business_Group
346 RETURN per_people_f.business_group_id%TYPE
347 IS
348 BEGIN
349 RETURN hr_util_misc_web.get_business_group_id;
350 END Get_Business_Group;
351 -- ----------------------------------------------------------------------------
352 -- |--< Use_Message >---------------------------------------------------------|
353 -- ----------------------------------------------------------------------------
354 FUNCTION Use_Message
355 ( p_message_name IN VARCHAR2
356 , p_application_id IN VARCHAR2 DEFAULT 'PER'
357 )
358 RETURN VARCHAR2
359 IS
360 --
361 l_proc VARCHAR2 (72);
362 BEGIN
363 g_debug := hr_utility.debug_enabled;
364 IF g_debug THEN
365 l_proc := g_package || ' Use_Message';
366 hr_utility.set_location('Entering : ' || l_proc, 5);
367 END IF;
368
369
370 IF g_debug THEN
371 hr_utility.set_location('Leaving : ' || l_proc, 10);
372 END IF;
373
374 fnd_message.set_name (p_application_id, p_message_name);
375 RETURN fnd_message.get;
376 --
377 -- just returns null if not found
378 --
379 END Use_Message;
380 -- ----------------------------------------------------------------------------
381 -- |--< IFNOTNULL >-----------------------------------------------------------|
382 -- ----------------------------------------------------------------------------
383 -- a private procedure in htf/htp
384 -- ----------------------------------------------------------------------------
385 FUNCTION IFNOTNULL
386 ( str1 IN VARCHAR2
387 , str2 IN VARCHAR2
388 )
389 RETURN VARCHAR2
390 IS
391 l_proc VARCHAR2 (72);
392 BEGIN
393 g_debug := hr_utility.debug_enabled;
394 IF g_debug THEN
395 l_proc := g_package || ' IFNOTNULL';
396 hr_utility.set_location('Entering : ' || l_proc, 5);
397 END IF;
398
399
400 IF g_debug THEN
401 hr_utility.set_location('Leaving : ' || l_proc, 10);
402 END IF;
403
404 IF (str1 is NULL) THEN
405 RETURN (NULL);
406 ELSE
407 RETURN (str2);
408 END IF;
409 END IFNOTNULL;
410 -- ----------------------------------------------------------------------------
411 -- |--< Substitute_Value >----------------------------------------------------|
412 -- ----------------------------------------------------------------------------
413 FUNCTION Substitute_Value
414 ( p_new IN VARCHAR2 DEFAULT NULL
415 , p_current IN VARCHAR2 DEFAULT NULL
416 , p_force IN BOOLEAN DEFAULT FALSE
417 )
418 RETURN VARCHAR2
419 IS
420 l_return_val VARCHAR2 (32000);
421 --
422 BEGIN
423
424
425 IF p_force THEN
426 l_return_val := p_new;
427 ELSE
428 IF p_new IS NOT NULL THEN
429 l_return_val := p_new;
430 ELSE
431 l_return_val := p_current;
432 END IF;
433 END IF;
434 --
435
436
437 RETURN l_return_val;
438 END Substitute_Value;
439 -- ----------------------------------------------------------------------------
440 -- |--< Substitute_Value >---------------------------------------------------|
441 -- ----------------------------------------------------------------------------
442 PROCEDURE Substitute_Value
443 ( p_new IN VARCHAR2 DEFAULT NULL
444 , p_current IN OUT NOCOPY VARCHAR2
445 , p_force IN BOOLEAN DEFAULT FALSE
446 )
447 IS
448 l_current VARCHAR2 (32000);
449 --
450 --
451 BEGIN
452
453
454 l_current :=
455 Substitute_Value
456 ( p_new => p_new
457 , p_current => p_current
458 , p_force => p_force
459 );
460 p_current := l_current;
461
462
463 END Substitute_Value;
464 -- ----------------------------------------------------------------------------
465 -- |--< Substitute_Value >---------------------------------------------------|
466 -- ----------------------------------------------------------------------------
467 PROCEDURE Substitute_Value
468 ( p_new IN NUMBER DEFAULT NULL
469 , p_current IN OUT NOCOPY NUMBER
470 , p_force IN BOOLEAN DEFAULT FALSE
471 )
472 IS
473 l_current VARCHAR2 (2000) := to_char(p_current);
474 --
475 --
476 BEGIN
477
478 Substitute_Value
479 ( p_current => l_current
480 , p_new => to_char(p_new)
481 , p_force => p_force
482 );
483 p_current := to_number(l_current);
484
485 END Substitute_Value;
486 -- ----------------------------------------------------------------------------
487 -- |--< Substitute_Value >---------------------------------------------------|
488 -- ----------------------------------------------------------------------------
489 FUNCTION Substitute_Value
490 ( p_new IN BOOLEAN DEFAULT NULL
491 , p_current IN BOOLEAN DEFAULT NULL
492 , p_force IN BOOLEAN DEFAULT FALSE
493 )
494 RETURN BOOLEAN
495 IS
496 l_return_val BOOLEAN;
497 --
498
499 BEGIN
500
501
502 IF p_force THEN
503 l_return_val := p_new;
504 ELSE
505 IF p_new IS NOT NULL THEN
506 l_return_val := p_new;
507 ELSE
508 l_return_val := p_current;
509 END IF;
510 END IF;
511 --
512
513 RETURN l_return_val;
514 END Substitute_Value;
515 -- ----------------------------------------------------------------------------
516 -- |--< date2char >-----------------------------------------------------------|
517 -- ----------------------------------------------------------------------------
518 -- name:
519 -- date2char
520 --
521 -- description:
522 -- This function returns a varchar2 date in user preference date
523 -- format.
524 -- ----------------------------------------------------------------------------
525 FUNCTION date2char
526 ( p_date IN DATE
527 ,p_date_format IN VARCHAR2 DEFAULT g_date_format
528 )
529 RETURN VARCHAR2
530 IS
531 --
532 --
533 l_proc VARCHAR2 (72);
534 BEGIN
535 g_debug := hr_utility.debug_enabled;
536 IF g_debug THEN
537 l_proc := g_package || ' date2char';
538 hr_utility.set_location('Entering : ' || l_proc, 5);
539 END IF;
540
541 --
542
543 IF g_debug THEN
544 hr_utility.set_location('Leaving : ' || l_proc, 10);
545 END IF;
546
547 RETURN to_char(trunc(p_date), p_date_format);
548 --
549 -- will fail when trying to get g_date_format
550 -- no exception necessary
551 --
552 END date2char;
553 -- ----------------------------------------------------------------------------
554 -- |--< char2date >-----------------------------------------------------------|
555 -- ----------------------------------------------------------------------------
556 -- name:
557 -- char2date
558 --
559 -- description:
560 -- This function converts a varchar2 date data to date data type using
561 -- passed in date format mask.
562 -- ----------------------------------------------------------------------------
563 FUNCTION char2date
564 ( p_char_date IN VARCHAR2
565 , p_date_format IN VARCHAR2
566 )
567 RETURN date
568 IS
569 --
570 --
571 l_proc VARCHAR2 (72);
572 BEGIN
573 g_debug := hr_utility.debug_enabled;
574 IF g_debug THEN
575 l_proc := g_package || ' char2date';
576 hr_utility.set_location('Entering : ' || l_proc, 5);
577 END IF;
578
579
580 IF g_debug THEN
581 hr_utility.set_location('Leaving : ' || l_proc, 10);
582 END IF;
583
584 RETURN
585 TRUNC
586 ( TO_DATE
587 ( p_char_date, p_date_format
588 )
589 );
590 EXCEPTION
591 WHEN OTHERS THEN
592 RAISE;
593 END char2date;
594 -- ----------------------------------------------------------------------------
595 -- |--< IsDateValid >---------------------------------------------------------|
596 -- ----------------------------------------------------------------------------
597 -- attempts to to_date a text string
598 -- returns tru if succeeds
599 --
600 FUNCTION IsDateValid
601 ( p_string IN VARCHAR2
602 )
603 RETURN BOOLEAN
604 IS
605 l_date DATE;
609 l_proc VARCHAR2 (72);
606 l_boolean boolean;
607 --
608 --
610 BEGIN
611 g_debug := hr_utility.debug_enabled;
612 IF g_debug THEN
613 l_proc := g_package || ' IsDateValid';
614 hr_utility.set_location('Entering : ' || l_proc, 5);
615 END IF;
616
617 l_date := to_date (p_string, g_date_format);
618
619 IF g_debug THEN
620 hr_utility.set_location('Leaving : ' || l_proc, 10);
621 END IF;
622
623 RETURN TRUE;
624 EXCEPTION
625 WHEN OTHERS THEN
626 RETURN FALSE;
627 --
628 END IsDateValid;
629 -- ----------------------------------------------------------------------------
630 -- |--< Convert_To_Date >-----------------------------------------------------|
631 -- ----------------------------------------------------------------------------
632 -- if converting to date fails (date is not valid), then value returned is null
633 -- logic in calling code can handle this better than raising an exception
634 -- e.g. hr_general_utilities.IFNOTNULL(hr_general_utlities.ConvertToDate(...)..
635 --
636 FUNCTION Convert_To_Date
637 ( p_date_string IN VARCHAR2
638 )
639 RETURN DATE
640 IS
641 --
642 --
643 l_proc VARCHAR2 (72);
644 BEGIN
645
646 IF g_debug THEN
647 l_proc := g_package || ' Convert_To_Date';
648 hr_utility.set_location('Entering : ' || l_proc, 5);
649 END IF;
650
651
652 IF g_debug THEN
653 hr_utility.set_location('Leaving : ' || l_proc, 10);
654 END IF;
655
656 IF IsDateValid (p_date_string) THEN
657 RETURN
658 char2date
659 ( p_char_date => p_date_string
660 , p_date_format => g_date_format
661 );
662 ELSE
663 RETURN NULL;
664 END IF;
665 --
666 EXCEPTION
667 WHEN OTHERS THEN
668 RAISE;
669 END Convert_To_Date;
670 -- ----------------------------------------------------------------------------
671 -- |--< Validate_Between_Dates >----------------------------------------------|
672 -- ----------------------------------------------------------------------------
673 FUNCTION Validate_Between_Dates
674 ( p_date1 IN DATE
675 , p_date2 IN DATE
676 )
677 RETURN BOOLEAN
678 IS
679 --
680 --
681 l_proc VARCHAR2 (72);
682 BEGIN
683 g_debug := hr_utility.debug_enabled;
684 IF g_debug THEN
685 l_proc := g_package || ' Validate_Between_Dates';
686 hr_utility.set_location('Entering : ' || l_proc, 5);
687 END IF;
688
689
690 IF g_debug THEN
691 hr_utility.set_location('Leaving : ' || l_proc, 10);
692 END IF;
693
694 IF p_date2 >= p_date1 THEN
695 RETURN TRUE;
696 ELSE
697 RETURN FALSE;
698 END IF;
699 EXCEPTION
700 WHEN OTHERS THEN
701 RAISE;
702 END Validate_Between_Dates;
703 -- ----------------------------------------------------------------------------
704 -- |--< Get_Column_Data >-----------------------------------------------------|
705 -- ----------------------------------------------------------------------------
706 FUNCTION Get_Column_Data
707 ( p_table_name VARCHAR2
708 , p_column_name VARCHAR2
709 )
710 RETURN r_column_data_rec
711 IS
712 --
713 TYPE col_data
714 IS RECORD
715 ( c_length NUMBER
716 , c_precision NUMBER
717 , c_datatype VARCHAR2 (2000)
718 );
719 l_hold_data col_data;
720 l_data r_column_data_rec;
721 --
722 l_proc VARCHAR2 (72) := g_package || ' Get_Column_Data';
723 --
724 CURSOR csr_col_data
725 ( p_table_name VARCHAR2
726 , p_column_name VARCHAR2
727 )
728 IS
729 SELECT data_length, data_precision, data_type
730 FROM all_tab_columns
731 WHERE table_name = p_table_name
732 AND column_name = p_column_name
733 AND owner = l_per_owner;
734 BEGIN
735 g_debug := hr_utility.debug_enabled;
736 IF g_debug THEN
737 hr_utility.set_location('Entering : ' || l_proc, 5);
738 END IF;
739
740
741 OPEN csr_col_data
742 ( p_table_name => UPPER(p_table_name)
743 , p_column_name => UPPER(p_column_name)
744 );
745 FETCH csr_col_data INTO l_hold_data;
746 CLOSE csr_col_data;
747 --
748 IF INSTR(l_hold_data.c_datatype, 'CHAR') > 0 THEN
749 l_data.f_precision := l_hold_data.c_length;
750 ELSE
751 l_data.f_precision := l_hold_data.c_precision;
752 END IF;
753 --
754 l_data.f_datatype := l_hold_data.c_datatype ;
755 --
756
757 IF g_debug THEN
758 hr_utility.set_location('Leaving : ' || l_proc, 10);
759 END IF;
760
761
762 RETURN l_data;
763 EXCEPTION
764 WHEN OTHERS THEN
765 hr_utility.set_message
766 ( hr_session_utilities.g_PER_application_id
767 , 'HR_6153_ALL_PROCEDURE_FAIL'
768 );
769 hr_utility.set_message_token('PROCEDURE', l_proc);
770 hr_utility.set_message_token('STEP', sqlerrm);
771 RAISE hr_session_utilities.g_coding_error;
772 END Get_Column_Data;
773 -- ----------------------------------------------------------------------------
774 -- |--< Get_lookup_Meaning >--------------------------------------------------|
775 -- ----------------------------------------------------------------------------
776 FUNCTION Get_lookup_Meaning
777 ( p_lookup_type IN VARCHAR2
778 , p_lookup_code IN VARCHAR2
779 , p_schema IN VARCHAR2 DEFAULT 'HR'
780 )
784 l_meaning VARCHAR2 (2000);
781 RETURN VARCHAR2
782 IS
783 l_record g_lookup_values_rec_type;
785 l_proc VARCHAR2 (72);
786 --
787 CURSOR csr_hr_lookup
788 ( p_lookup_type VARCHAR2
789 , p_lookup_code VARCHAR2
790 )
791 IS
792 SELECT lookup_type, lookup_code, meaning
793 FROM hr_lookups
794 WHERE lookup_type = p_lookup_type
795 AND lookup_code = p_lookup_code
796 AND enabled_flag = 'Y'
797 AND sysdate between NVL(start_date_active, hr_api.g_sot)
798 AND NVL(end_date_active, hr_api.g_eot);
799 --
800 CURSOR csr_fnd_lookup
801 ( p_lookup_type VARCHAR2
802 , p_lookup_code VARCHAR2
803 )
804 IS
805 SELECT lookup_type, lookup_code, meaning
806 FROM fnd_lookups
807 WHERE lookup_type = p_lookup_type
808 AND lookup_code = p_lookup_code
809 AND enabled_flag = 'Y'
810 AND sysdate between NVL(start_date_active, hr_api.g_sot)
811 AND NVL(end_date_active, hr_api.g_eot);
812 BEGIN
813 g_debug := hr_utility.debug_enabled;
814 IF g_debug THEN
815 l_proc := g_package || ' Get_lookup_Meaning';
816 hr_utility.set_location('Entering : ' || l_proc, 5);
817 END IF;
818
819 IF p_schema = 'HR' THEN
820 OPEN csr_hr_lookup
821 ( p_lookup_type => p_lookup_type
822 , p_lookup_code => p_lookup_code
823 );
824 FETCH csr_hr_lookup INTO l_record;
825 IF csr_hr_lookup%FOUND THEN
826 l_meaning := l_record.meaning;
827 ELSE
828 NULL;
829 END IF;
830 --
831 CLOSE csr_hr_lookup;
832 ELSIF p_schema = 'FND' THEN
833 OPEN csr_fnd_lookup
834 ( p_lookup_type => p_lookup_type
835 , p_lookup_code => p_lookup_code
836 );
837 FETCH csr_fnd_lookup INTO l_record;
838 IF csr_fnd_lookup%FOUND THEN
839 l_meaning := l_record.meaning;
840 ELSE
841 NULL;
842 END IF;
843 --
844 CLOSE csr_fnd_lookup;
845 ELSE
846 NULL;
847 END IF;
848 --
849
850 IF g_debug THEN
851 hr_utility.set_location('Leaving : ' || l_proc, 10);
852 END IF;
853
854 RETURN l_meaning;
855 END Get_lookup_Meaning;
856 -- ----------------------------------------------------------------------------
857 -- |--< Get_lookup_values >---------------------------------------------------|
858 -- ----------------------------------------------------------------------------
859 FUNCTION Get_lookup_values
860 ( p_lookup_type IN VARCHAR2
861 , p_schema IN VARCHAR2 DEFAULT 'HR'
862 )
863 RETURN g_lookup_values_tab_type
864 IS
865 l_array g_lookup_values_tab_type;
866 --
867 l_proc VARCHAR2 (72) := g_package || ' Get_lookup_values';
868 --
869 CURSOR csr_hr_lookup
870 ( p_lookup_type VARCHAR2
871 )
872 IS
873 SELECT lookup_type, lookup_code, meaning
874 FROM hr_lookups
875 WHERE lookup_type = p_lookup_type
876 AND enabled_flag = 'Y'
877 AND sysdate between NVL(start_date_active, hr_api.g_sot)
878 AND NVL(end_date_active, hr_api.g_eot)
879 ORDER by meaning;
880
881 --
882 CURSOR csr_fnd_lookup
883 ( p_lookup_type VARCHAR2
884 )
885 IS
886 SELECT lookup_type, lookup_code, meaning
887 FROM fnd_lookups
888 WHERE lookup_type = p_lookup_type
889 AND enabled_flag = 'Y'
890 AND sysdate between NVL(start_date_active, hr_api.g_sot)
891 AND NVL(end_date_active, hr_api.g_eot)
892 ORDER by meaning;
893
894 BEGIN
895 g_debug := hr_utility.debug_enabled;
896
897 IF g_debug THEN
898 l_proc := g_package || ' Get_lookup_values';
899 hr_utility.set_location('Entering : ' || l_proc, 5);
900 END IF;
901
902 IF p_schema = 'HR' THEN
903 FOR CursorRecord
904 IN csr_hr_lookup
905 ( p_lookup_type => p_lookup_type
906 )
907 LOOP
908 l_array(csr_hr_lookup%ROWCOUNT).lookup_type
909 := CursorRecord.lookup_type;
910 l_array(csr_hr_lookup%ROWCOUNT).lookup_code
911 := CursorRecord.lookup_code;
912 l_array(csr_hr_lookup%ROWCOUNT).meaning
913 := CursorRecord.meaning;
914 END LOOP;
915 ELSIF p_schema = 'FND' THEN
916 FOR CursorRecord
917 IN csr_fnd_lookup
918 ( p_lookup_type => p_lookup_type
919 )
920 LOOP
921 l_array(csr_hr_lookup%ROWCOUNT).lookup_type
922 := CursorRecord.lookup_type;
923 l_array(csr_hr_lookup%ROWCOUNT).lookup_code
924 := CursorRecord.lookup_code;
925 l_array(csr_hr_lookup%ROWCOUNT).meaning
926 := CursorRecord.meaning;
927 END LOOP;
928 ELSE
929 NULL;
930 END IF;
931 --
932
933 IF g_debug THEN
934 hr_utility.set_location('Leaving : ' || l_proc, 10);
935 END IF;
936
937 RETURN l_array;
938 END Get_lookup_values;
939 -- ----------------------------------------------------------------------------
940 -- |--< DoLookupsExist >------------------------------------------------------|
941 -- ----------------------------------------------------------------------------
942 FUNCTION DoLookupsExist
943 ( p_lookup_type IN VARCHAR2
944 , p_schema IN VARCHAR2 DEFAULT 'HR'
945 )
946 RETURN BOOLEAN
947 IS
948 l_array g_lookup_values_tab_type;
949 --
950 l_proc VARCHAR2 (72);
951 BEGIN
952 g_debug := hr_utility.debug_enabled;
953 IF g_debug THEN
954 l_proc := g_package || ' DoLookupsExist';
958 l_array :=
955 hr_utility.set_location('Entering : ' || l_proc, 5);
956 END IF;
957
959 Get_Lookup_Values
960 ( p_lookup_type => p_lookup_type
961 , p_schema => p_schema
962 );
963 --
964
965 IF g_debug THEN
966 hr_utility.set_location('Leaving : ' || l_proc, 10);
967 END IF;
968
969 RETURN l_array.count > 0;
970 --
971 --exception not necessary
972 --
973 END DoLookupsExist;
974 -- ----------------------------------------------------------------------------
975 -- |--< Force_Date_Format >---------------------------------------------------|
976 -- ----------------------------------------------------------------------------
977 FUNCTION Force_Date_Format
978 ( p_char_date IN VARCHAR2
979 )
980 RETURN VARCHAR2
981 IS
982 --
983 l_proc VARCHAR2 (72);
984 BEGIN
985 g_debug := hr_utility.debug_enabled;
986 IF g_debug THEN
987 l_proc := g_package || ' Force_Date_Format';
988 hr_utility.set_location('Entering : ' || l_proc, 5);
989 END IF;
990
991
992 IF g_debug THEN
993 hr_utility.set_location('Leaving : ' || l_proc, 10);
994 END IF;
995
996 IF IsDateValid
997 ( p_string => p_char_date
998 )
999 THEN
1000 RETURN
1001 date2char
1002 ( p_date =>
1003 char2date
1004 ( p_char_date => p_char_date
1005 , p_date_format => g_date_format
1006 )
1007 , p_date_format => g_date_format
1008 );
1009 ELSE
1010 RETURN p_char_date; --NULL;
1011 END IF;
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 RAISE;
1015 END Force_Date_Format;
1016 -- ----------------------------------------------------------------------------
1017 -- |--< ScriptOpen >----------------------------------------------------------|
1018 -- ----------------------------------------------------------------------------
1019 PROCEDURE ScriptOpen
1020 ( p_js_library IN VARCHAR2 DEFAULT NULL
1021 )
1022 IS
1023 l_attributes VARCHAR2 (2000);
1024 l_lang_type VARCHAR2 (20) := 'javascript';
1025 --
1026 l_proc VARCHAR2 (72);
1027 BEGIN
1028 g_debug := hr_utility.debug_enabled;
1029 IF g_debug THEN
1030 l_proc := g_package || ' ScriptOpen';
1031 hr_utility.set_location('Entering : ' || l_proc, 5);
1032 END IF;
1033
1034 IF p_js_library IS NOT NULL THEN
1035 l_attributes :=
1036 l_attributes
1037 || REPLACE
1038 ( g_src_attribute
1039 , g_replace_string
1040 , p_js_library
1041 );
1042 ELSE
1043 NULL;
1044 END IF;
1045 --
1046 htp.p ('<SCRIPT ' || l_attributes || '>');
1047 htp.p ('<!-- start hiding');
1048 --
1049 --exception not necessary
1050 --
1051
1052 IF g_debug THEN
1053 hr_utility.set_location('Leaving : ' || l_proc, 10);
1054 END IF;
1055
1056 END ScriptOpen;
1057 -- ----------------------------------------------------------------------------
1058 -- |--< ScriptClose >---------------------------------------------------------|
1059 -- ----------------------------------------------------------------------------
1060 PROCEDURE ScriptClose
1061 IS
1062 --
1063 l_proc VARCHAR2 (72);
1064 BEGIN
1065 g_debug := hr_utility.debug_enabled;
1066 IF g_debug THEN
1067 l_proc := g_package || ' ScriptClose';
1068 hr_utility.set_location('Entering : ' || l_proc, 5);
1069 END IF;
1070
1071 htp.p ('<!-- end hiding -->');
1072 htp.p ('</SCRIPT>');
1073 --
1074 --exception not necessary
1075 --
1076
1077 IF g_debug THEN
1078 hr_utility.set_location('Leaving : ' || l_proc, 10);
1079 END IF;
1080
1081 END ScriptClose;
1082 -- ----------------------------------------------------------------------------
1083 -- |--< Add_Separators >------------------------------------------------------|
1084 -- ----------------------------------------------------------------------------
1085 FUNCTION Add_Separators
1086 ( p_instring IN VARCHAR2
1087 , p_start IN BOOLEAN DEFAULT FALSE
1088 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
1089 )
1090 RETURN VARCHAR2
1091 IS
1092 l_t_str VARCHAR2 (32000);
1093 l_ret_string VARCHAR2 (32000);
1094
1095 BEGIN
1096
1097
1098 --
1099 --
1100 -- need to encode tab, eol and cr
1101 -- leave these lines exactly as they are
1102 l_t_str := REPLACE (p_instring, '
1103 ', '%$*');
1104 --
1105 -- Replaced CHR(13) with the hr_util_misc_web.g_carriage_return
1106 l_t_str := REPLACE (l_t_str, hr_util_misc_web.g_carriage_return, '&@~');
1107 l_t_str := REPLACE (l_t_str, ' ', ']@*');
1108
1109 IF p_start THEN
1110 l_ret_string :=
1111 p_separator
1112 || l_t_str
1113 || p_separator;
1114 ELSE
1115 l_ret_string :=
1116 l_t_str
1117 || p_separator;
1118 END IF;
1119 --
1120
1121
1122 RETURN l_ret_string;
1123 --
1124 --exception not necessary
1125 --
1126 END Add_Separators;
1127 -- ----------------------------------------------------------------------------
1128 -- |--< Locate_Item_In_Separated_Str >---------------------------------------|
1129 -- ----------------------------------------------------------------------------
1130 FUNCTION Locate_Item_In_Separated_Str
1131 ( p_string IN VARCHAR2
1132 , p_item IN NUMBER
1133 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
1134 )
1135 RETURN NUMBER
1136 IS
1137 --
1138 l_proc VARCHAR2 (72);
1139 BEGIN
1140 g_debug := hr_utility.debug_enabled;
1141 IF g_debug THEN
1142 l_proc := g_package || ' Find_Item_In_String';
1143 hr_utility.set_location('Entering : ' || l_proc, 5);
1144 END IF;
1145
1146
1147 IF g_debug THEN
1148 hr_utility.set_location('Leaving : ' || l_proc, 10);
1149 END IF;
1150
1151 RETURN
1152 INSTR
1153 ( p_string, p_separator, 1, p_item
1154 );
1155 END Locate_Item_In_Separated_Str;
1156 -- ----------------------------------------------------------------------------
1157 -- |--< Find_Item_In_String >-------------------------------------------------|
1158 -- ----------------------------------------------------------------------------
1159 FUNCTION Find_Item_In_String
1160 ( p_item IN NUMBER
1161 , p_string IN VARCHAR2
1162 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
1163 )
1164 RETURN VARCHAR2
1165 IS
1166 BeginSep INTEGER;
1167 Endsep INTEGER;
1168 l_returnstr VARCHAR2 (32000);
1169 l_reduceval NUMBER := 2; -- needs to be 1 if the space character is used
1170 --
1171
1172 BEGIN
1173
1174 BeginSep :=
1175 Locate_Item_In_Separated_Str
1176 ( p_string => p_string
1177 , p_item => p_item
1178 , p_separator => p_separator
1179 );
1180 EndSep :=
1181 Locate_Item_In_Separated_Str
1182 ( p_string => p_string
1183 , p_item => p_item + 1
1184 , p_separator => p_separator
1185 );
1186 --
1187 IF p_separator = g_space THEN
1188 l_reduceval := 1;
1189 ELSE
1190 NULL;
1191 END IF;
1192 --
1193 l_returnstr :=
1194 SUBSTR
1195 ( p_string
1196 , BeginSep + length (p_separator)
1197 , EndSep - BeginSep - l_reduceval
1198 );
1199 --
1200 -- convert back to line feed, carriage return, tab
1201 -- do not modify these lines
1202 l_returnstr :=
1203 REPLACE (l_returnstr, '%$*', '
1204 ');
1205
1206 l_returnstr :=
1207 REPLACE (l_returnstr, '&@~', hr_util_misc_web.g_carriage_return); -- chr(13)
1208
1209 l_returnstr :=
1210 REPLACE (l_returnstr, ']@*', ' ');
1211
1212 --
1213 IF p_separator <> g_space THEN
1214 --
1215 -- trim if the separator is not the space character
1216 --
1217 l_returnstr :=
1218 RTRIM
1219 ( LTRIM
1220 ( l_returnstr
1221 )
1222 );
1223 ELSE
1224 NULL;
1225 END IF;
1226 --
1227
1228 --
1229 RETURN l_returnstr;
1230 --
1231 --exception not necessary
1232 --
1233 END Find_Item_In_String ;
1234 -- ----------------------------------------------------------------------------
1235 -- |--< Trim_Separator >-----------------------------------------------------|
1236 -- ----------------------------------------------------------------------------
1237 FUNCTION Trim_Separator
1238 ( p_string IN VARCHAR2
1239 , p_end IN VARCHAR2 DEFAULT 'RIGHT'
1240 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
1241 )
1242 RETURN VARCHAR2
1243 IS
1244 l_returnval VARCHAR2 (32000);
1245 l_separator_length NUMBER := LENGTH( p_separator);
1246 l_chk_string VARCHAR2 (200);
1247 l_string_length NUMBER := LENGTH(p_string);
1248 l_proc VARCHAR2 (72);
1249 BEGIN
1250 g_debug := hr_utility.debug_enabled;
1251 IF g_debug THEN
1252 l_proc := g_package || ' Trim_Separator';
1253 hr_utility.set_location('Entering : ' || l_proc, 5);
1254 END IF;
1255
1256 --
1257 IF p_string IS NOT NULL THEN
1258 IF UPPER(p_end) = 'RIGHT' THEN
1259 l_chk_string :=
1260 SUBSTR
1261 ( p_string
1262 , l_string_length - l_separator_length + 1
1263 , l_separator_length
1264 );
1265 ELSE
1266 l_chk_string :=
1267 SUBSTR
1268 ( p_string
1269 , 1
1270 , l_separator_length
1271 );
1272 END IF;
1273 --
1274 IF l_chk_string = p_separator THEN
1275 IF UPPER(p_end) = 'RIGHT' THEN
1276 l_returnval :=
1277 SUBSTR
1278 ( p_string
1279 , 1
1280 , l_string_length - l_separator_length
1281 );
1282 ELSE
1283 l_returnval :=
1284 SUBSTR
1285 ( p_string
1286 , l_separator_length + 1
1287 , l_string_length - l_separator_length
1288 );
1289 END IF;
1290 ELSE
1291 NULL;
1292 END IF;
1293 ELSE
1294 NULL;
1295 END IF;
1296 --
1297
1298 IF g_debug THEN
1299 hr_utility.set_location('Leaving : ' || l_proc, 10);
1300 END IF;
1301
1302 RETURN l_returnval;
1303 END Trim_Separator;
1304 -- ----------------------------------------------------------------------------
1305 -- |--< BPFD >---------------------------------------------------------------|
1306 -- ----------------------------------------------------------------------------
1307 FUNCTION BPFD
1308 ( p_string IN VARCHAR2
1309 )
1310 RETURN VARCHAR2
1311 IS
1312 l_icx_string VARCHAR2 (32000) := p_string;
1313 l_package VARCHAR2 (32000);
1314 l_procedure VARCHAR2 (32000);
1315 l_count NUMBER;
1316 l_sql_string VARCHAR2 (32000);
1317 l_offset NUMBER := 3; -- i.e. the number of fields before the list
1318 -- of parameter / values
1319 l_proc VARCHAR2 (72);
1320 BEGIN
1321 g_debug := hr_utility.debug_enabled;
1322 IF g_debug THEN
1323 l_proc := g_package || ' BPFD';
1324 hr_utility.set_location('Entering : ' || l_proc, 5);
1325 END IF;
1326
1327 l_package :=
1328 hr_general_utilities.Find_Item_In_String
1329 ( p_item => 1
1330 , p_string => l_icx_string
1331 );
1332 l_procedure :=
1333 hr_general_utilities.Find_Item_In_String
1334 ( p_item => 2
1335 , p_string => l_icx_string
1336 );
1337 l_count :=
1338 to_number
1339 ( hr_general_utilities.Find_Item_In_String
1340 ( p_item => 3
1341 , p_string => l_icx_string
1342 )
1343 );
1344 l_sql_string :=
1345 l_package
1346 || '.'
1347 || l_procedure
1348 || '(';
1349 --
1350 FOR Counter IN 1 .. 2 * l_count
1351 LOOP
1352 -- i.e. is an odd number, therefore ignore (equivalent to step)
1353 IF MOD (counter, 2) <> 0
1354 THEN
1355 IF Counter <> 1 THEN
1356 l_sql_string := l_sql_string || ',';
1357 ELSE
1358 NULL;
1359 END IF;
1360 --
1361 l_sql_string :=
1362 l_sql_string
1363 || hr_general_utilities.Find_Item_In_String
1364 ( p_item => Counter + l_offset
1365 , p_string => l_icx_string
1366 );
1367 BEGIN
1368 l_sql_string :=
1369 l_sql_string
1370 || '=>'
1371 || to_number
1372 ( hr_general_utilities.Find_Item_In_String
1373 ( p_item => Counter + l_offset + 1
1374 , p_string => l_icx_string
1375 )
1376 );
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 -- cannot convert a text string
1380 l_sql_string :=
1381 l_sql_string
1382 || '=>'
1383 || ''''
1384 || hr_general_utilities.Find_Item_In_String
1385 ( p_item => Counter + l_offset + 1
1386 , p_string => l_icx_string
1387 )
1388 || '''';
1389 --
1390 -- make sure that reserved words are transcribed correctly
1391 --
1392 l_sql_string :=
1393 REPLACE
1394 ( l_sql_string
1395 , '''NULL'''
1396 , 'NULL'
1397 );
1398 END;
1399 l_sql_string :=
1400 REPLACE
1401 ( l_sql_string
1402 , '''NULL'''
1403 , 'NULL'
1404 );
1405 l_sql_string :=
1406 REPLACE
1407 ( l_sql_string
1408 , '''TRUE'''
1409 , 'TRUE'
1410 );
1411 l_sql_string :=
1412 REPLACE
1413 ( l_sql_string
1414 , '''FALSE'''
1415 , 'FALSE'
1416 );
1417
1418 ELSE
1419 NULL;
1420 END IF;
1421 END LOOP;
1422 --
1423 -- close the opening parenthesis
1424 --
1425 l_sql_string := l_sql_string || ')';
1426
1427 IF g_debug THEN
1428 hr_utility.set_location('Leaving : ' || l_proc, 10);
1429 END IF;
1430
1431 RETURN l_sql_string;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 RAISE;
1435 END BPFD;
1436 --
1437 -- ----------------------------------------------------------------------------
1438 -- |--< BPFD >-----------------------------------------------------------------|
1439 -- | This function is overloaded. We keep the same name as BPFD because we |
1440 -- | want to make it easier to associate the code to the original BPFD. |
1441 -- | However, this function will replace all hard-coded literals in the parm |
1442 -- | values with bind variables for scalability. The bind values are stored in|
1443 -- | in the output parameter p_bind_values_tab of this overloaded function. |
1444 -- | So, the procedure invocation will look like this: |
1445 -- | per_appraisal_display_web.aprp01(parm1=> :1, parm2 => :2, ....); |
1446 -- ----------------------------------------------------------------------------
1447 FUNCTION BPFD
1448 ( p_string IN VARCHAR2
1449 ,p_bind_values_tab out nocopy hr_general_utilities.g_vc32k_tab_type
1450 ,p_use_bind_values out nocopy boolean
1451 )
1452 RETURN VARCHAR2
1453 IS
1454
1455 l_icx_string VARCHAR2 (32000) := p_string;
1456 l_package VARCHAR2 (32000);
1457 l_procedure VARCHAR2 (32000);
1458 l_count NUMBER;
1459 l_sql_string VARCHAR2 (32000);
1460 l_tmp_string VARCHAR2 (32000);
1461 l_offset NUMBER := 3; -- i.e. the number of fields before the list
1462 -- of parameter / values
1463 l_proc VARCHAR2 (72) := g_package || ' build_proc_from_decryption';
1464 l_bind_values_tab_out hr_general_utilities.g_vc32k_tab_type;
1465 l_bind_values_count integer := 0;
1466 l_empty_values_tab hr_general_utilities.g_vc32k_tab_type;
1467 l_use_bind_values boolean default null;
1468 --
1469 BEGIN
1470 g_debug := hr_utility.debug_enabled;
1471 IF g_debug THEN
1472 hr_utility.set_location('Entering : ' || l_proc, 5);
1473 END IF;
1474
1475 l_package :=
1476 hr_general_utilities.Find_Item_In_String
1477 ( p_item => 1
1478 , p_string => l_icx_string
1479 );
1480 l_procedure :=
1481 hr_general_utilities.Find_Item_In_String
1482 ( p_item => 2
1483 , p_string => l_icx_string
1484 );
1485 l_count :=
1486 to_number
1487 ( hr_general_utilities.Find_Item_In_String
1488 ( p_item => 3
1489 , p_string => l_icx_string
1490 )
1491 );
1492 l_sql_string :=
1493 l_package
1494 || '.'
1495 || l_procedure
1496 || '(';
1497
1498 -------------------------------------------------------------------------------
1499 -- 04/08/2001 Bug 1713366 Fix:
1500 -- We will compare the l_count which is the number of parameters for the
1501 -- dynamic SQL invocation with the global constant g_num_dynamic_sql_parms. If
1502 -- the l_count is > g_num_dynamic_sql_parms, we will use the old BPFD function,
1503 -- The dynamic SQL created will have literal values in it, e.g.
1504 -- per_appraisal_display_web.aprp07(p_appraisal_id =>4368,p_appraisee_id=>1234).
1505 --
1506 -- If the l_count is <= g_num_dynamic_sql_parms, then this BPFD will be used
1507 -- which will create a dynamic SQL with bind variables to hold the arguments,
1508 -- e.g. per_appraisal_display_web.aprp07(p_appraisal_id=>:1,p_appraisee_id=>:2).
1509 --
1510 -- If for some reason we want to revert to the old BPFD code, we can set the
1511 -- g_num_dynamic_sql_parms global constant to -1. This approach can be used for
1512 -- debugging when we suspect that Native Dynamic SQL is causing some obscure
1513 -- errors.
1514 -------------------------------------------------------------------------------
1515 --
1516 IF l_count > g_num_dynamic_sql_parms
1517 THEN
1518 -- Do the old BPFD way
1519 l_use_bind_values := false;
1520 FOR Counter IN 1 .. 2 * l_count
1521 LOOP
1522 -- i.e. is an odd number, therefore ignore (equivalent to step)
1523 IF MOD (counter, 2) <> 0
1524 THEN
1525 IF Counter <> 1 THEN
1526 l_sql_string := l_sql_string || ',';
1527 ELSE
1528 NULL;
1529 END IF;
1530 --
1531 l_sql_string := l_sql_string ||
1532 hr_general_utilities.Find_Item_In_String
1533 ( p_item => Counter + l_offset
1534 , p_string => l_icx_string
1535 );
1536 --
1537 BEGIN
1538 l_sql_string := l_sql_string || '=>' ||
1539 to_number ( hr_general_utilities.Find_Item_In_String
1540 ( p_item => Counter + l_offset + 1
1541 , p_string => l_icx_string
1542 )
1543 );
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 -- cannot convert a text string
1547 l_sql_string := l_sql_string || '=>' || ''''
1548 || hr_general_utilities.Find_Item_In_String
1549 ( p_item => Counter + l_offset + 1
1550 , p_string => l_icx_string
1551 ) || '''';
1552 --
1553 -- make sure that reserved words are transcribed correctly
1554 --
1555 l_sql_string := REPLACE ( l_sql_string , '''NULL''' , 'NULL');
1556 END;
1557 -- Don't know why the old BPFD checks for ''NULL'' again. Keep it
1558 -- here just in case.
1559 l_sql_string := REPLACE ( l_sql_string , '''NULL''' , 'NULL');
1560 --
1561 l_sql_string := REPLACE ( l_sql_string , '''TRUE''' , 'TRUE');
1562 --
1563 l_sql_string := REPLACE ( l_sql_string , '''FALSE''' , 'FALSE');
1564 --
1565 ELSE
1566 NULL;
1567 END IF;
1568 END LOOP;
1569 ELSE
1570 -- l_count is less than or equal to g_num_dynamic_sql_parms
1571 l_use_bind_values := true;
1572 FOR Counter IN 1 .. 2 * l_count
1573 LOOP
1574 -- i.e. is an odd number, therefore ignore (equivalent to step)
1575 IF MOD (counter, 2) <> 0
1576 THEN
1577 IF Counter <> 1 THEN
1578 l_sql_string := l_sql_string || ',';
1579 ELSE
1580 NULL;
1581 END IF;
1582 --
1583 l_tmp_string := null;
1584
1585 l_sql_string := l_sql_string ||
1586 hr_general_utilities.Find_Item_In_String
1587 ( p_item => Counter + l_offset
1588 , p_string => l_icx_string
1589 );
1590
1591 l_sql_string := l_sql_string || '=>';
1592
1593 -----------------------------------------------------------------------
1594 -- Bug 1713366 Fix:
1595 -- Literal SQL statement will not scale. Therefore, we need to convert
1596 -- all literal values to bind variables in the dynamic sql statement
1597 -- to improve performance.
1598 -- We replace the parameter values with bind variables and store the
1599 -- bind values in a table.
1600 -----------------------------------------------------------------------
1601 BEGIN
1602 --
1603 l_tmp_string := to_number
1604 ( hr_general_utilities.Find_Item_In_String
1605 ( p_item => Counter + l_offset + 1
1606 , p_string => l_icx_string
1607 )
1608 );
1609
1610 -- The dynamic sql will use bind variables for scalability
1611 -- E.g. per_appraisal_display_web.aprp07(p_appraisee_id => :1, ....)
1612 -- Increment the counter if it is a number
1613 l_bind_values_count := l_bind_values_count + 1;
1614 l_sql_string := l_sql_string || ':' || l_bind_values_count;
1615 l_bind_values_tab_out(l_bind_values_count) := l_tmp_string;
1616
1617 EXCEPTION
1618 WHEN OTHERS THEN
1619 -- cannot convert a text string
1620 -- We are going to use Native Dynamic SQL instead of placing the
1621 -- literal values in the parm, so we don't need to wrap the string
1622 -- with quotes around it.
1623 -- E.g. Literal SQL:
1624 -- per_appraisal_display_web.aprp07(p_calltype => 'PAGE', ...)
1625 -- Bind Variable SQL:
1626 -- per_appraisal_display_web.aprp07(p_calltype => :1, ...); --> The
1627 -- bind value for :1 will be stored in a variable as PAGE and thus
1628 -- no quotes are needed in the bind value.
1629 --
1630 l_tmp_string := hr_general_utilities.Find_Item_In_String
1631 ( p_item => Counter + l_offset + 1
1632 , p_string => l_icx_string);
1633
1634 -- Bug fix for 2061027 starts
1635 -- OTA uses the encryption function in which they pass String with
1636 -- quotes in them, we handle these quotes by enclosing them in
1637 -- escape character which again is a quote and store them in the
1638 -- ICX table, when the values are read back here we need to do the
1639 -- reverese so that the quotes don't cause an issue
1640
1641 l_tmp_string := REPLACE (l_tmp_string, '''''', '''');
1642
1643 -- bug fix 2061027 ends
1644
1645 -- make sure that reserved words are transcribed correctly
1646 -- For reserved words like NULL, TRUE and FALSE, we won't use
1647 -- the bind variables because Native Dynamic SQL does not support
1648 -- these values.
1649 --
1650 IF instr(l_tmp_string, 'NULL') > 0 OR
1651 instr(l_tmp_string, 'TRUE') > 0 OR
1652 instr(l_tmp_string, 'FALSE') > 0
1653 THEN
1654 IF instr(l_tmp_string, 'NULL') > 0
1655 THEN
1656 l_sql_string := l_sql_string || l_tmp_string;
1657 END IF;
1658 --
1659 IF instr(l_tmp_string, 'TRUE') > 0
1660 THEN
1661 l_sql_string := l_sql_string || l_tmp_string;
1662 END IF;
1663 --
1664 IF instr(l_tmp_string, 'FALSE') > 0
1665 THEN
1666 l_sql_string := l_sql_string || l_tmp_string;
1667 END IF;
1668 --
1669 ELSE
1670 -- The text is not one of the reserved values, thus we need to
1671 -- use the bind values.
1672 l_bind_values_count := l_bind_values_count + 1;
1673 l_sql_string := l_sql_string || ':' || l_bind_values_count;
1674 l_bind_values_tab_out(l_bind_values_count) := l_tmp_string;
1675 END IF;
1676 END;
1677 ELSE
1678 -- an even number, that means it is the parameter name, no special
1679 -- processing.
1680 NULL;
1681 END IF;
1682 END LOOP;
1683 END IF; -- end the g_num_dynamic_sql_parms check
1684 --
1685 -- close the opening parenthesis
1686 --
1687 l_sql_string := l_sql_string || ')';
1688
1689 p_bind_values_tab := l_bind_values_tab_out;
1690 p_use_bind_values := l_use_bind_values;
1691
1692 return l_sql_string;
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 p_bind_values_tab := l_empty_values_tab;
1697 hr_utility.set_message
1698 ( hr_session_utilities.g_PER_application_id
1699 , 'HR_6153_ALL_PROCEDURE_FAIL'
1700 );
1701 hr_utility.set_message_token('PROCEDURE', l_proc);
1702 hr_utility.set_message_token('STEP', sqlerrm);
1703 RAISE hr_session_utilities.g_coding_error;
1704
1705 END BPFD;
1706 --
1707 -- ----------------------------------------------------------------------------
1708 -- |--< Get_Date_Hint >-------------------------------------------------------|
1709 -- ----------------------------------------------------------------------------
1710 FUNCTION Get_Date_Hint
1711 RETURN VARCHAR2
1712 IS
1713 c_prompts icx_util.g_prompts_table;
1714 c_title VARCHAR2 (200);
1715 l_proc VARCHAR2 (72);
1716 BEGIN
1717 g_debug := hr_utility.debug_enabled;
1718 IF g_debug THEN
1719 l_proc := g_package || ' Get_Date_Hint';
1720 hr_utility.set_location('Entering : ' || l_proc, 5);
1721 END IF;
1722
1723 -- Bug #1200894 fix
1724 icx_util.getPrompts(601,'HR_PERSONAL_INFORMATION',c_title,c_prompts);
1725
1726 IF g_debug THEN
1727 hr_utility.set_location('Leaving : ' || l_proc, 10);
1728 END IF;
1729
1730 RETURN
1731 '('
1732 ||
1733 c_prompts (61) -- e.g.
1734 || ' '
1735 ||
1736 date2char
1740 END Get_Date_Hint;
1737 ( p_date => g_sample_date
1738 )
1739 || ')';
1741 -- ----------------------------------------------------------------------------
1742 -- |--< EPFS >----------------------------------------------------------------|
1743 -- ----------------------------------------------------------------------------
1744 FUNCTION EPFS
1745 ( p_string IN VARCHAR2
1746 , p_type IN VARCHAR2 DEFAULT 'S'
1747 )
1748 RETURN VARCHAR2
1749 IS
1750 l_max_length NUMBER := 1000;
1751 l_rows NUMBER;
1752 l_str_length NUMBER;
1753 l_str_start NUMBER := 1;
1754 l_sep_start BOOLEAN;
1755 l_icx_ids VARCHAR2 (2000);
1756 l_icx_count NUMBER := 0;
1757 l_tmp_id VARCHAR2 (2000);
1758 l_string VARCHAR2 (2000);
1759 l_code VARCHAR2 (2000);
1760 l_proc VARCHAR2 (72);
1761 BEGIN
1762 g_debug := hr_utility.debug_enabled;
1763 IF g_debug THEN
1764 l_proc := g_package || ' EPFS';
1765 hr_utility.set_location('Entering : ' || l_proc, 5);
1766 END IF;
1767
1768 --
1769 -- this breaks a long string up to 32k into l_max_length bits
1770 -- encrypts it and returns the encryption id
1771 -- the ids are built into a string and then encrypted
1772 -- the index string format is
1773 -- !# S !# 3 !# 1001 !# 1002 !# 1003 !#
1774 -- exec rows r1 r2 r3
1775 -- S - single execute
1776 -- G - global cache (i.e. ends up stored)
1777 -- start the string
1778 --
1779 l_code :=
1780 l_code
1781 || hr_general_utilities.Add_Separators
1782 ( p_instring => p_type
1783 , p_start => TRUE
1784 );
1785 --
1786 l_str_length := LENGTH(p_string);
1787 l_rows :=
1788 CEIL
1789 ( l_str_length
1790 / l_max_length
1791 );
1792 FOR Counter in 1 .. l_rows
1793 LOOP
1794 IF Counter = l_rows THEN
1795 l_string :=
1796 SUBSTR
1797 ( p_string
1798 , l_str_start
1799 , l_str_length - l_str_start + 1
1800 );
1801 ELSE
1802 l_string :=
1803 SUBSTR
1804 ( p_string
1805 , l_str_start
1806 , l_max_length
1807 );
1808 l_str_start := l_str_start + l_max_length;
1809 END IF;
1810 --
1811 -- encrypt the string
1812 --
1813 l_tmp_id :=
1814 icx_call.encrypt2
1815 ( c_string => l_string
1816 );
1817 --
1818 -- store the row index
1819 --
1820 l_icx_ids :=
1821 l_icx_ids
1822 || hr_general_utilities.Add_Separators
1823 ( p_instring => l_tmp_id
1824 );
1825 --
1826 -- count the number of icx rows
1827 --
1828 l_icx_count := l_icx_count + 1;
1829 END LOOP;
1830 --
1831 -- add the number of rows
1832 --
1833 l_icx_ids :=
1834 l_code
1835 || hr_general_utilities.Add_Separators
1836 ( p_instring => to_char(l_icx_count)
1837 )
1838 || l_icx_ids;
1839 --
1840 -- now encrypt the index values themselves
1841 --
1842 l_tmp_id :=
1843 icx_call.encrypt2
1844 ( c_string => l_icx_ids
1845 );
1846 --
1847 -- return this value
1848
1849 IF g_debug THEN
1850 hr_utility.set_location('Leaving : ' || l_proc, 10);
1851 END IF;
1852
1853 RETURN l_tmp_id;
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 RAISE;
1857 END EPFS;
1858 -- ----------------------------------------------------------------------------
1859 -- |--< ASEI >----------------------------------------------------------------|
1860 -- ----------------------------------------------------------------------------
1861 PROCEDURE ASEI
1862 ( p_text_id IN VARCHAR2
1863 )
1864 IS
1865 l_proc VARCHAR2 (72) := g_package || ' ASEI';
1866 BEGIN
1867 g_debug := hr_utility.debug_enabled;
1868 IF g_debug THEN
1869 l_proc := g_package || ' ASEI';
1870 hr_utility.set_location('Entering : ' || l_proc, 5);
1871 END IF;
1872
1873
1874 IF g_debug THEN
1875 hr_utility.set_location('Leaving : ' || l_proc, 10);
1876 END IF;
1877
1878 g_execute_handles (g_execute_handles.count + 1) := p_text_id;
1879 END ASEI;
1880 -- ----------------------------------------------------------------------------
1881 -- |--< CCEI >----------------------------------------------------------------|
1882 -- ----------------------------------------------------------------------------
1883 FUNCTION CCEI
1884 RETURN VARCHAR2
1885 IS
1886 l_string VARCHAR2 (2000);
1887 l_proc VARCHAR2 (72) := g_package || ' CCEI';
1888 BEGIN
1889 g_debug := hr_utility.debug_enabled;
1890 IF g_debug THEN
1891 hr_utility.set_location('Entering : ' || l_proc, 5);
1892 END IF;
1893
1894 IF g_execute_handles.count > 0 THEN
1895 l_string :=
1896 l_string
1897 || hr_general_utilities.Add_Separators
1898 ( p_instring => 'C'
1899 , p_start => TRUE
1900 );
1901 l_string :=
1902 l_string
1903 || hr_general_utilities.Add_Separators
1904 ( p_instring => to_char(g_execute_handles.count)
1905 );
1906 FOR Counter IN 1 .. g_execute_handles.count
1907 LOOP
1908 l_string :=
1909 l_string
1910 || hr_general_utilities.Add_Separators
1911 ( p_instring => g_execute_handles(Counter)
1912 );
1913 END LOOP;
1917 --
1914 ELSE
1915 RAISE hr_session_utilities.g_coding_error;
1916 END IF;
1918
1919 IF g_debug THEN
1920 hr_utility.set_location('Leaving : ' || l_proc, 10);
1921 END IF;
1922
1923 RETURN
1924 icx_call.encrypt2
1925 ( c_string => l_string
1926 );
1927 EXCEPTION
1928 WHEN OTHERS THEN
1929 --
1930 -- vc2 length may exceed 2000;
1931 -- this must be a coding error
1932 -- mechanism not really suitable for executing so many sql statements
1933 --
1934 hr_utility.set_message
1935 ( hr_session_utilities.g_PER_application_id
1936 , 'HR_6153_ALL_PROCEDURE_FAIL'
1937 );
1938 hr_utility.set_message_token('PROCEDURE', l_proc);
1939 hr_utility.set_message_token('STEP', sqlerrm);
1940 RAISE hr_session_utilities.g_coding_error;
1941 END CCEI;
1942 -- ----------------------------------------------------------------------------
1943 -- |--< EXPD >----------------------------------------------------------------|
1944 -- ----------------------------------------------------------------------------
1945 FUNCTION EXPD
1946 ( p_id IN VARCHAR2 DEFAULT NULL
1947 , p_string IN VARCHAR2 DEFAULT NULL
1948 )
1949 RETURN VARCHAR2
1950 IS
1951 l_index_string VARCHAR2 (32000);
1952 l_item2 NUMBER;
1953 l_offset NUMBER := 2;
1954 l_string VARCHAR2 (32000);
1955 l_proc VARCHAR2 (72);
1956 BEGIN
1957 g_debug := hr_utility.debug_enabled;
1958 IF g_debug THEN
1959 l_proc := g_package || ' EXPD';
1960 hr_utility.set_location('Entering : ' || l_proc, 5);
1961 END IF;
1962
1963
1964 IF p_string IS NULL THEN
1965 l_index_string :=
1966 icx_call.decrypt2
1967 ( c_text_id => p_id
1968 );
1969 ELSE
1970 l_index_string := p_string;
1971 END IF;
1972 --
1973 l_item2 :=
1974 to_number
1975 ( hr_general_utilities.Find_Item_In_String
1976 ( p_item => 2
1977 , p_string => l_index_string
1978 )
1979 );
1980 --
1981 FOR RowCounter IN 1 .. l_item2
1982 LOOP
1983 l_string :=
1984 l_string
1985 || icx_call.decrypt2
1986 ( c_text_id =>
1987 hr_general_utilities.Find_Item_In_String
1988 ( p_item => RowCounter + l_offset
1989 , p_string => l_index_string
1990 )
1991 );
1992 END LOOP;
1993 --
1994
1995 IF g_debug THEN
1996 hr_utility.set_location('Leaving : ' || l_proc, 10);
1997 END IF;
1998
1999 RETURN l_string;
2000 END EXPD;
2001 -- ----------------------------------------------------------------------------
2002 -- |--< DExL >----------------------------------------------------------------|
2003 -- ----------------------------------------------------------------------------
2004 PROCEDURE DExL
2005 ( i IN VARCHAR2
2006 )
2007 IS
2008 err_mesg varchar2 (2000);
2009 l_sql_string VARCHAR2 (32000);
2010 l_index_string VARCHAR2 (32000);
2011 l_item1 VARCHAR2 (2000);
2012 l_item2 NUMBER;
2013 l_proc VARCHAR2 (72):= g_package || ' DExL';
2014 l_bind_values_count integer default 0;
2015 l_bind_values_tab hr_general_utilities.g_vc32k_tab_type;
2016 l_bind_values_list varchar2(32000) default null;
2017 l_use_bind_values boolean default null;
2018
2019 BEGIN
2020 g_debug := hr_utility.debug_enabled;
2021 IF g_debug THEN
2022 hr_utility.set_location('Entering : ' || l_proc, 5);
2023 END IF;
2024
2025 --hrhtml2.debug('entered dex;');
2026 --
2027 -- reset the cache
2028 --
2029 -- hr_general_utilities.reset_g_cache;
2030 --
2031 -- decrypt the index string
2032 --
2033 l_index_string :=
2034 icx_call.decrypt2
2035 ( c_text_id => to_number(i)
2036 );
2037 --
2038 -- decrypt2 returns -1 if the session is invalid
2039 --
2040 IF l_index_string = '-1' THEN
2041 hr_utility.set_message
2042 ( hr_session_utilities.g_PER_application_id
2043 , 'PER_DEXL_SESSION_FAIL'
2044 );
2045 RAISE g_fatal_error;
2046 ELSE
2047 --
2048 -- extract the first two items to see which process to follow (S, G, C)
2049 --
2050 l_item1 :=
2051 hr_general_utilities.Find_Item_In_String
2052 ( p_item => 1
2053 , p_string => l_index_string
2054 );
2055 --
2056 l_item2 :=
2057 to_number
2058 ( hr_general_utilities.Find_Item_In_String
2059 ( p_item => 2
2060 , p_string => l_index_string
2061 )
2062 );
2063 --
2064 -- switch accordingly
2065 --
2066 IF l_item1 = 'C' THEN
2067 FOR Counter IN 1 .. l_item2
2068 LOOP
2069 hr_general_utilities.DEXL
2070 ( i =>
2071 hr_general_utilities.Find_Item_In_String
2072 ( p_item => Counter + 2
2073 , p_string => l_index_string
2074 )
2075 );
2076 END LOOP;
2077 ELSIF l_item1 = 'G' THEN
2078 --
2079 -- store the string in the global cache variable for later retrieval
2080 --------------------------------------------------------------------------------
2081 -- Bug 1615428:
2082 -- Need to clear g_cache global array for R11i WebDB stateful mode. Otherwise,
2083 -- g_string_cache will retain previous value and cause obscure error when
2087 -- bug 1911463
2084 -- clicking CANCEL button in MEE pages.
2085 -------------------------------------------------------------------------------
2086 --
2088 -- commenting the call to delete as this is causing errors in appraisels
2089 -- hr_general_utilities.g_string_cache.delete;
2090
2091 g_string_cache (g_string_cache.count + 1) :=
2092 EXPD
2093 ( p_string => l_index_string
2094 );
2095 ELSIF l_item1 = 'S' THEN
2096 --
2097 -- Build the executable link
2098 --
2099 -------------------------------------------------------------------------
2100 -- Bug 1713366:
2101 -- The l_sql_string results in literal SQL statement which makes it
2102 -- unreusable, especially in Appraisal.
2103 -- Will call an overloaded BPFD which will pass back 2 output parms:
2104 -- one is table records which contain values of the bind variables and
2105 -- the other output parm will indicate whether bind variable algorithm
2106 -- was used or not.
2107 -- NOTE: When the count of l_bind_values_tab output parm is zero, it
2108 -- does not mean that bind variable algorithm was not used, e.g.
2109 -- hr_mee_person_search_tree_web.display_container_bottom_frame()
2110 -- We can use Native Dynamic SQL to make the above invocation.
2111 ------------------------------------------------------------------------
2112 l_sql_string := hr_general_utilities.BPFD
2113 ( p_string => EXPD ( p_string => l_index_string)
2114 ,p_bind_values_tab => l_bind_values_tab
2115 ,p_use_bind_values => l_use_bind_values
2116 );
2117 --
2118 l_sql_string := 'begin ' || l_sql_string || '; end;';
2119
2120 l_bind_values_count := l_bind_values_tab.count;
2121
2122 IF l_bind_values_count = 0 and l_use_bind_values = TRUE
2123 THEN
2124 -- Use Native Dynamic SQL call
2125 -- No parameters, does not need to pass bind values
2126 EXECUTE IMMEDIATE l_sql_string;
2127 ELSIF l_bind_values_count = 1
2128 THEN
2129 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1);
2130 ELSIF l_bind_values_count = 2
2131 THEN
2132 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2133 ,l_bind_values_tab(2);
2134 ELSIF l_bind_values_count = 3
2135 THEN
2136 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2137 ,l_bind_values_tab(2)
2138 ,l_bind_values_tab(3);
2139 ELSIF l_bind_values_count = 4
2140 THEN
2141 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2142 ,l_bind_values_tab(2)
2143 ,l_bind_values_tab(3)
2144 ,l_bind_values_tab(4);
2145 ELSIF l_bind_values_count = 5
2146 THEN
2147 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2148 ,l_bind_values_tab(2)
2149 ,l_bind_values_tab(3)
2150 ,l_bind_values_tab(4)
2151 ,l_bind_values_tab(5);
2152 ELSIF l_bind_values_count = 6
2153 THEN
2154 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2155 ,l_bind_values_tab(2)
2156 ,l_bind_values_tab(3)
2157 ,l_bind_values_tab(4)
2158 ,l_bind_values_tab(5)
2159 ,l_bind_values_tab(6);
2160 ELSIF l_bind_values_count = 7
2161 THEN
2162 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2163 ,l_bind_values_tab(2)
2164 ,l_bind_values_tab(3)
2165 ,l_bind_values_tab(4)
2166 ,l_bind_values_tab(5)
2167 ,l_bind_values_tab(6)
2168 ,l_bind_values_tab(7);
2169 ELSIF l_bind_values_count = 8
2170 THEN
2171 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2172 ,l_bind_values_tab(2)
2173 ,l_bind_values_tab(3)
2174 ,l_bind_values_tab(4)
2175 ,l_bind_values_tab(5)
2176 ,l_bind_values_tab(6)
2177 ,l_bind_values_tab(7)
2178 ,l_bind_values_tab(8);
2179 ELSIF l_bind_values_count = 9
2180 THEN
2181 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2182 ,l_bind_values_tab(2)
2183 ,l_bind_values_tab(3)
2184 ,l_bind_values_tab(4)
2185 ,l_bind_values_tab(5)
2186 ,l_bind_values_tab(6)
2187 ,l_bind_values_tab(7)
2188 ,l_bind_values_tab(8)
2189 ,l_bind_values_tab(9);
2190 ELSIF l_bind_values_count = 10
2191 THEN
2192 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2196 ,l_bind_values_tab(5)
2193 ,l_bind_values_tab(2)
2194 ,l_bind_values_tab(3)
2195 ,l_bind_values_tab(4)
2197 ,l_bind_values_tab(6)
2198 ,l_bind_values_tab(7)
2199 ,l_bind_values_tab(8)
2200 ,l_bind_values_tab(9)
2201 ,l_bind_values_tab(10);
2202 ELSIF l_bind_values_count = 11
2203 THEN
2204 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2205 ,l_bind_values_tab(2)
2206 ,l_bind_values_tab(3)
2207 ,l_bind_values_tab(4)
2208 ,l_bind_values_tab(5)
2209 ,l_bind_values_tab(6)
2210 ,l_bind_values_tab(7)
2211 ,l_bind_values_tab(8)
2212 ,l_bind_values_tab(9)
2213 ,l_bind_values_tab(10)
2214 ,l_bind_values_tab(11);
2215 ELSIF l_bind_values_count = 12
2216 THEN
2217 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2218 ,l_bind_values_tab(2)
2219 ,l_bind_values_tab(3)
2220 ,l_bind_values_tab(4)
2221 ,l_bind_values_tab(5)
2222 ,l_bind_values_tab(6)
2223 ,l_bind_values_tab(7)
2224 ,l_bind_values_tab(8)
2225 ,l_bind_values_tab(9)
2226 ,l_bind_values_tab(10)
2227 ,l_bind_values_tab(11)
2228 ,l_bind_values_tab(12);
2229 ELSIF l_bind_values_count = 13
2230 THEN
2231 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2232 ,l_bind_values_tab(2)
2233 ,l_bind_values_tab(3)
2234 ,l_bind_values_tab(4)
2235 ,l_bind_values_tab(5)
2236 ,l_bind_values_tab(6)
2237 ,l_bind_values_tab(7)
2238 ,l_bind_values_tab(8)
2239 ,l_bind_values_tab(9)
2240 ,l_bind_values_tab(10)
2241 ,l_bind_values_tab(11)
2242 ,l_bind_values_tab(13);
2243 ELSIF l_bind_values_count = 14
2244 THEN
2245 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2246 ,l_bind_values_tab(2)
2247 ,l_bind_values_tab(3)
2248 ,l_bind_values_tab(4)
2249 ,l_bind_values_tab(5)
2250 ,l_bind_values_tab(6)
2251 ,l_bind_values_tab(7)
2252 ,l_bind_values_tab(8)
2253 ,l_bind_values_tab(9)
2254 ,l_bind_values_tab(10)
2255 ,l_bind_values_tab(11)
2256 ,l_bind_values_tab(14);
2257 ELSIF l_bind_values_count = 15
2258 THEN
2259 EXECUTE IMMEDIATE l_sql_string USING l_bind_values_tab(1)
2260 ,l_bind_values_tab(2)
2261 ,l_bind_values_tab(3)
2262 ,l_bind_values_tab(4)
2263 ,l_bind_values_tab(5)
2264 ,l_bind_values_tab(6)
2265 ,l_bind_values_tab(7)
2266 ,l_bind_values_tab(8)
2267 ,l_bind_values_tab(9)
2268 ,l_bind_values_tab(10)
2269 ,l_bind_values_tab(11)
2270 ,l_bind_values_tab(14)
2271 ,l_bind_values_tab(15);
2272 ELSE
2273 -- either the parameters are greater than 15 or the num of parameters
2274 -- are greater than the global constant g_num_dynamic_sql_parms, then
2275 -- use dbms_sql package by calling Execute_Dynamic_SQL procedure.
2276 -- From old code which uses literal SQL statement
2277
2278 IF g_debug THEN
2279 hr_utility.set_location('Leaving : ' || l_proc, 10);
2280 END IF;
2281
2282 Execute_Dynamic_SQL
2283 ( p_sql_string => l_sql_string
2284 );
2285 END IF; -- end of l_bind_values_count check
2286 -- end of bug 1713366
2287 END IF; -- end of l_item1 = 'S'
2288 end if;
2289 EXCEPTION
2290 WHEN g_fatal_error THEN
2291 hr_utility.set_message
2292 ( hr_session_utilities.g_PER_application_id
2293 , 'HR_6153_ALL_PROCEDURE_FAIL'
2294 );
2298 --
2295 hr_utility.set_message_token('PROCEDURE', l_proc);
2296 hr_utility.set_message_token('STEP', sqlerrm);
2297 htp.p (fnd_message.get);
2299 -- do not re-raise otherwise fails; nowhere to raise to (may be calling itself)
2300 --
2301 WHEN OTHERS THEN
2302 --
2303 -- an error trying to decode the string
2304 --
2305 hr_utility.set_message
2306 ( hr_session_utilities.g_PER_application_id
2307 , 'HR_6153_ALL_PROCEDURE_FAIL'
2308 );
2309 hr_utility.set_message_token('PROCEDURE', l_proc);
2310 hr_utility.set_message_token('STEP', sqlerrm);
2311 RAISE hr_session_utilities.g_coding_error;
2312 END DexL;
2313 -- ----------------------------------------------------------------------------
2314 -- |--< SDER >----------------------------------------------------------------|
2315 -- ----------------------------------------------------------------------------
2316 FUNCTION SDER
2317 RETURN VARCHAR2
2318 IS
2319 l_proc VARCHAR2 (72);
2320 BEGIN
2321 g_debug := hr_utility.debug_enabled;
2322 IF g_debug THEN
2323 l_proc := g_package || ' SDER';
2324 hr_utility.set_location('Entering : ' || l_proc, 5);
2325 END IF;
2326
2327
2328 IF g_debug THEN
2329 hr_utility.set_location('Leaving : ' || l_proc, 10);
2330 END IF;
2331
2332 RETURN 'hr_general_utilities.DEXL?i=';
2333 END SDER;
2334 -- ----------------------------------------------------------------------------
2335 -- |--< REGS >----------------------------------------------------------------|
2336 -- ----------------------------------------------------------------------------
2337 FUNCTION REGS
2338 ( p_index IN NUMBER
2339 )
2340 RETURN VARCHAR2
2341 IS
2342 l_string VARCHAR2 (32000);
2343 l_proc VARCHAR2 (72);
2344 BEGIN
2345 g_debug := hr_utility.debug_enabled;
2346 IF g_debug THEN
2347 l_proc := g_package || ' REGS';
2348 hr_utility.set_location('Entering : ' || l_proc, 5);
2349 END IF;
2350
2351 IF g_string_cache.exists (p_index) THEN
2352 l_string := g_string_cache(p_index);
2353 ELSE
2354 NULL;
2355 END IF;
2356 --
2357
2358 IF g_debug THEN
2359 hr_utility.set_location('Leaving : ' || l_proc, 10);
2360 END IF;
2361
2362 RETURN l_string;
2363 END REGS;
2364 -- ----------------------------------------------------------------------------
2365 -- |--< Reset_G_Cache >--------------------------------------------------------|
2366 -- ----------------------------------------------------------------------------
2367 PROCEDURE Reset_G_Cache
2368 IS
2369 l_reset g_vc32k_tab_type;
2370 l_proc VARCHAR2 (72);
2371 BEGIN
2372 g_debug := hr_utility.debug_enabled;
2373 IF g_debug THEN
2374 l_proc := g_package || ' Reset_G_Cache';
2375 hr_utility.set_location('Entering : ' || l_proc, 5);
2376 END IF;
2377
2378
2379 IF g_debug THEN
2380 hr_utility.set_location('Leaving : ' || l_proc, 10);
2381 END IF;
2382
2383 g_string_cache := l_reset;
2384
2385 END Reset_G_Cache;
2386 -- ----------------------------------------------------------------------------
2387 -- |--< Locate_Text >---------------------------------------------------------|
2388 -- ----------------------------------------------------------------------------
2389 FUNCTION Locate_Text
2390 ( p_search_in IN VARCHAR2
2391 , p_search_for IN VARCHAR2
2392 , p_search_after IN NUMBER DEFAULT 1
2393 , p_second_instance IN BOOLEAN DEFAULT FALSE
2394 , p_end_position IN BOOLEAN DEFAULT FALSE
2395 , p_ignore_case IN BOOLEAN DEFAULT TRUE
2396 , p_reverse IN BOOLEAN DEFAULT FALSE
2397 )
2398 RETURN NUMBER
2399 IS
2400 l_start_pos NUMBER;
2401 l_instance NUMBER := 1;
2402 l_returnval NUMBER;
2403 l_search_after NUMBER;
2404 l_search_in VARCHAR2 (32000);
2405 l_search_for VARCHAR2 (32000);
2406 l_proc VARCHAR2 (72);
2407 BEGIN
2408 g_debug := hr_utility.debug_enabled;
2409 IF g_debug THEN
2410 l_proc := g_package || ' Locate_Text';
2411 hr_utility.set_location('Entering : ' || l_proc, 5);
2412 END IF;
2413
2414 IF p_second_instance THEN
2415 l_instance := 2;
2416 ELSE
2417 NULL;
2418 END IF;
2419 --
2420 IF p_ignore_case THEN
2421 l_search_in := UPPER (p_search_in);
2422 l_search_for := UPPER (p_search_for);
2423 ELSE
2424 l_search_in := p_search_in;
2425 l_search_for := p_search_for;
2426 END IF;
2427 --
2428 IF p_reverse THEN
2429 l_search_after := -1 * (LENGTH (l_search_in) - p_search_after +1);
2430 ELSE
2431 l_search_after := p_search_after;
2432 END IF;
2433 --
2434 l_start_pos :=
2435 INSTR
2436 ( l_search_in
2437 , l_search_for
2438 , l_search_after
2439 , l_instance
2440 );
2441 --
2442 IF l_start_pos <> 0 THEN
2443 IF p_end_position THEN
2444 l_returnval := l_start_pos + LENGTH (p_search_for);
2445 ELSE
2446 l_returnval := l_start_pos;
2447 END IF;
2448 ELSE
2449 l_returnval := l_start_pos;
2450 END IF;
2451 --
2452
2453 IF g_debug THEN
2454 hr_utility.set_location('Leaving : ' || l_proc, 10);
2455 END IF;
2456
2457 RETURN l_returnval;
2458 END Locate_Text;
2459 -- ----------------------------------------------------------------------------
2460 -- |--< Count_Instances >-----------------------------------------------------|
2461 -- ----------------------------------------------------------------------------
2465 , p_ignore_case IN BOOLEAN DEFAULT TRUE
2462 FUNCTION Count_Instances
2463 ( p_search_in IN VARCHAR2
2464 , p_search_for IN VARCHAR2
2466 )
2467 RETURN NUMBER
2468 IS
2469 l_search_in VARCHAR2 (32000);
2470 l_search_for VARCHAR2 (200);
2471 l_position NUMBER := 0;
2472 l_counter NUMBER := 1;
2473 l_returnval NUMBER := 0;
2474 l_proc VARCHAR2 (72);
2475 BEGIN
2476 g_debug := hr_utility.debug_enabled;
2477 IF g_debug THEN
2478 l_proc := g_package || ' Count_Instances';
2479 hr_utility.set_location('Entering : ' || l_proc, 5);
2480 END IF;
2481
2482 IF p_ignore_case THEN
2483 l_search_in := UPPER ( p_search_in);
2484 l_search_for := UPPER (p_search_for);
2485 ELSE
2486 l_search_in := p_search_in;
2487 l_search_for := p_search_for;
2488 END IF;
2489 --
2490 LOOP
2491 l_position :=
2492 INSTR
2493 ( l_search_in
2494 , l_search_for
2495 , 1
2496 , l_counter
2497 );
2498 EXIT WHEN l_position = 0;
2499 l_counter := l_counter + 1;
2500 END LOOP;
2501
2502 --
2503 l_returnval := l_counter -1;
2504 --
2505
2506 IF g_debug THEN
2507 hr_utility.set_location('Leaving : ' || l_proc, 10);
2508 END IF;
2509
2510 RETURN l_returnval;
2511 END Count_Instances;
2512 -- ----------------------------------------------------------------------------
2513 -- |--< Execute_Dynamic_SQL >-------------------------------------------------|
2514 -- ----------------------------------------------------------------------------
2515 PROCEDURE Execute_Dynamic_SQL
2516 ( p_sql_string IN VARCHAR2
2517 )
2518 IS
2519 l_cursor_handle NUMBER;
2520 l_exec NUMBER;
2521 l_proc VARCHAR2 (72) := g_package || ' Execute_Dynamic_SQL';
2522 BEGIN
2523 g_debug := hr_utility.debug_enabled;
2524 IF g_debug THEN
2525 hr_utility.set_location('Entering : ' || l_proc, 5);
2526 END IF;
2527
2528 l_cursor_handle := dbms_sql.open_cursor;
2529 dbms_sql.parse
2530 ( l_cursor_handle
2531 , p_sql_string
2532 , dbms_sql.v7
2533 );
2534 l_exec := dbms_sql.execute (l_cursor_handle);
2535 dbms_sql.close_cursor(l_cursor_handle);
2536
2537 IF g_debug THEN
2538 hr_utility.set_location('Leaving : ' || l_proc, 10);
2539 END IF;
2540
2541 EXCEPTION
2542 WHEN OTHERS THEN
2543 hr_utility.set_message
2544 ( hr_session_utilities.g_PER_application_id
2545 , 'HR_6153_ALL_PROCEDURE_FAIL'
2546 );
2547 hr_utility.set_message_token('PROCEDURE', l_proc);
2548 hr_utility.set_message_token('STEP', sqlerrm);
2549 RAISE hr_session_utilities.g_coding_error;
2550 END Execute_Dynamic_SQL;
2551 -- ----------------------------------------------------------------------------
2552 -- |--< Set_Message_Txt_And_Number >------------------------------------------|
2553 -- ----------------------------------------------------------------------------
2554 FUNCTION Set_Message_Txt_And_Number
2555 ( p_application_id IN VARCHAR2 DEFAULT 'PER'
2556 , p_message_name IN VARCHAR2 DEFAULT NULL
2557 )
2558 RETURN r_error_msg_txt_number_rec
2559 IS
2560 l_record r_error_msg_txt_number_rec;
2561 l_get_encoded VARCHAR2 (2000);
2562 l_short_name VARCHAR2 (2000);
2563 l_msg_name VARCHAR2 (2000);
2564 l_proc VARCHAR2 (72) := g_package || ' Get_Print_Action';
2565 BEGIN
2566 g_debug := hr_utility.debug_enabled;
2567 IF g_debug THEN
2568 hr_utility.set_location('Entering : ' || l_proc, 5);
2569 END IF;
2570
2571
2572 IF g_debug THEN
2573 hr_utility.set_location('Leaving : ' || l_proc, 10);
2574 END IF;
2575
2576 IF p_message_name IS NOT NULL THEN
2577 fnd_message.set_name
2578 ( APPLICATION => p_application_id
2579 , NAME => p_message_name
2580 );
2581 ELSE
2582 NULL;
2583 END IF;
2584 l_get_encoded := fnd_message.get_encoded;
2585 fnd_message.parse_encoded
2586 ( ENCODED_MESSAGE => l_get_encoded
2587 , APP_SHORT_NAME => l_short_name
2588 , MESSAGE_NAME => l_msg_name
2589 );
2590 l_record.error_text :=
2591 hr_general_utilities.Use_Message
2592 ( p_message_name => l_msg_name
2593 , p_application_id => l_short_name
2594 );
2595 l_record.error_number :=
2596 fnd_message.get_number
2597 ( APPIN => l_short_name
2598 , NAMEIN => l_msg_name
2599 );
2600 RETURN l_record;
2601 EXCEPTION
2602 WHEN OTHERS THEN
2603 hr_utility.set_message
2604 ( hr_session_utilities.g_PER_application_id
2605 , 'HR_6153_ALL_PROCEDURE_FAIL'
2606 );
2607 hr_utility.set_message_token('PROCEDURE', l_proc);
2608 hr_utility.set_message_token('STEP', sqlerrm);
2609 RAISE hr_session_utilities.g_coding_error;
2610 END Set_Message_Txt_And_Number;
2611 -- ----------------------------------------------------------------------------
2612 -- |--< Set_Workflow_Section_Attribute >--------------------------------------|
2613 -- ----------------------------------------------------------------------------
2614 FUNCTION Set_Workflow_Section_Attribute
2615 ( p_item_type IN wf_items.item_type%TYPE DEFAULT NULL
2616 , p_item_key IN wf_items.item_key%TYPE DEFAULT NULL
2617 , p_actid IN NUMBER DEFAULT NULL
2618 , p_web_page_section_code IN VARCHAR2
2619 )
2620 RETURN VARCHAR2
2621 IS
2622 l_string VARCHAR2 (2000);
2623 l_proc VARCHAR2 (72);
2624 BEGIN
2625 g_debug := hr_utility.debug_enabled;
2626 IF g_debug THEN
2627 l_proc := g_package || ' Set_Workflow_Section_Attribute';
2628 hr_utility.set_location('Entering : ' || l_proc, 5);
2629 END IF;
2630
2631 IF hr_workflow_service.check_web_page_code
2632 ( p_item_type => p_item_type
2633 , p_item_key => p_item_key
2634 , p_actid => p_actid
2635 , p_web_page_section_code => p_web_page_section_code
2636 )
2637 THEN
2638 l_string :=
2639 hr_workflow_service.get_web_page_code
2640 ( p_item_type => p_item_type
2641 , p_item_key => p_item_key
2642 , p_actid => p_actid
2643 , p_web_page_section_code => p_web_page_section_code
2644 );
2645 ELSE
2646 NULL;
2647 END IF;
2648
2649 IF g_debug THEN
2650 hr_utility.set_location('Entering : ' || l_proc, 5);
2651 END IF;
2652
2653 RETURN
2654 l_string;
2655 END Set_Workflow_Section_Attribute;
2656 --
2657 -- bug 748569 fix: validate_session package initialization code eliminated
2658 -- handled by frame drawing procedures only
2659 --
2660 END HR_GENERAL_UTILITIES;