1 PACKAGE IRC_UTILITIES_PKG AUTHID CURRENT_USER AS
2 /* $Header: irutil.pkh 120.23.12020000.5 2013/02/07 12:02:00 pshreera ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------< SET_SAVEPOINT >-----------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- Wrapper to be called from java code. This sets a savepoint that can be rolled
11 -- back to.
12 --
13 -- Prerequisites:
14 --
15 -- In Parameters:
16 --
17 -- Post Success:
18 --
19 -- Post Failure:
20 --
21 -- Developer Implementation Notes:
22 --
23 -- {End Of Comments}
24
25 PROCEDURE SET_SAVEPOINT;
26
27
28 --
29 -- ----------------------------------------------------------------------------
30 -- |----------------------< ROLLBACK_TO_SAVEPOINT >-------------------------|
31 -- ----------------------------------------------------------------------------
32 -- {Start Of Comments}
33 --
34 -- Description:
35 -- Wrapper to be called from java code. This rolls back to the savepoint. Used
36 -- after an api_validate.
37 --
38 -- Prerequisites:
39 --
40 -- In Parameters:
41 --
42 -- Post Success:
43 --
44 -- Post Failure:
45 --
46 -- Developer Implementation Notes:
47 --
48 -- {End Of Comments}
49
50 PROCEDURE ROLLBACK_TO_SAVEPOINT;
51
52 -- -------------------------------------------------------------------
53 -- |--------------------< get_home_page_function >-------------------|
54 -- -------------------------------------------------------------------
55 -- {Start Of Comments}
56 --
57 -- Description:
58 -- Wrapper to be called from java code. This rerturns the function for the passed
59 -- in responsibility_id.
60 --
61 -- Prerequisites:
62 --
63 -- In Parameters:
64 --
65 -- Post Success:
66 --
67 -- Post Failure:
68 --
69 -- Developer Implementation Notes:
70 --
71 -- {End Of Comments}
72
73 procedure GET_HOME_PAGE_FUNCTION(p_responsibility_id in varchar2
74 ,p_function out nocopy varchar2);
75
76 function removeTags(p_in varchar2) return varchar2;
77 function removeTags(p_in clob) return varchar2;
78
79 -- ----------------------------------------------------------------------------
80 -- |-----------------------< GET_CURRENT_EMPLOYER >--------------------------|
81 -- ----------------------------------------------------------------------------
82 -- {Start Of Comments}
83 --
84 -- Description:
85 -- This functions returns a person's current employer.
86 --
87 -- Prerequisites:
88 -- This is a public function, mainly called by VO's representing search results
89 -- in the iRecruitment web module.
90 --
91 -- In Parameters:
92 -- A Person ID, and an effective date.
93 -- Post Success:
94 -- Returned varchar of current employer name.
95 -- Post Failure:
96 -- No explicit error catching occurs. An empty string returns if no row found.
97 --
98 -- Developer Implementation Notes:
99 --
100 -- {End Of Comments}
101
102 FUNCTION GET_CURRENT_EMPLOYER (p_person_id per_all_people_f.person_id%TYPE default null,
103 p_eff_date date default trunc(sysdate))
104 RETURN VARCHAR2;
105
106 FUNCTION GET_CURRENT_EMPLOYER_PTY (p_party_id number,
107 p_eff_date date default trunc(sysdate))
108 RETURN VARCHAR2;
109
110
111 -- ----------------------------------------------------------------------------
112 -- |-------------------------< GET_MAX_QUAL_TYPE >--------------------------|
113 -- ----------------------------------------------------------------------------
114 -- {Start Of Comments}
115 --
116 -- Description:
117 -- This functions returns the highest ranking qualification that a person
118 -- posesses.
119 --
120 -- Prerequisites:
121 -- This is a public function, mainly called by VO's representing search results
122 -- in the iRecruitment web module.
123 --
124 -- In Parameters:
125 -- A Person ID
126 -- Post Success:
127 -- Returned varchar of highest ranked qualification.
128 -- Post Failure:
129 -- No explicit error catching occurs. An empty string returns if no row found.
130 --
131 -- Developer Implementation Notes:
132 --
133 -- {End Of Comments}
134
135 FUNCTION GET_MAX_QUAL_TYPE (p_person_id per_all_people_f.person_id%TYPE default null)
136 RETURN VARCHAR2;
137 FUNCTION GET_MAX_QUAL_TYPE_PTY (p_party_id NUMBER)
138 RETURN VARCHAR2;
139 -- ----------------------------------------------------------------------------
140 -- |-----------------------< GET_EMP_UPT_FOR_PERSON >------------------------|
141 -- ----------------------------------------------------------------------------
142 -- {Start Of Comments}
143 --
144 -- Description:
145 -- This functions returns a person's User Person Type if they are an employee.
146 --
147 -- Prerequisites:
148 -- This is a public function, mainly called by VO's representing search results
149 -- in the iRecruitment web module.
150 --
151 -- In Parameters:
152 -- A Person ID, and an effective date.
153 -- Post Success:
154 -- Returned varchar of employee user person type.
155 -- Post Failure:
156 -- No explicit error catching occurs. An empty string returns if no row found.
157 --
158 -- Developer Implementation Notes:
159 --
160 -- {End Of Comments}
161
162 FUNCTION GET_EMP_UPT_FOR_PERSON (p_person_id per_all_people_f.person_id%TYPE default null,
163 p_eff_date date default trunc(sysdate))
164 RETURN VARCHAR2;
165 --
166 FUNCTION GET_EMP_UPT_FOR_PARTY (p_party_id number,
167 p_eff_date date default trunc(sysdate))
168 RETURN VARCHAR2;
169
170 -- ----------------------------------------------------------------------------
171 -- |-----------------------< GET_APL_UPT_FOR_PERSON >------------------------|
172 -- ----------------------------------------------------------------------------
173 -- {Start Of Comments}
174 --
175 -- Description:
176 -- This functions returns a person's User Person Type if they are an applicant.
177 -- If they are also an employee and not in the person viewing's security profile,
178 -- an empty string will be returned.
179 --
180 -- Prerequisites:
181 -- This is a public function, mainly called by VO's representing search results
182 -- in the iRecruitment web module.
183 --
184 -- In Parameters:
185 -- A Person ID, and an effective date.
186 -- Post Success:
187 -- Returned varchar of applicant user person type.
188 -- Post Failure:
189 -- No explicit error catching occurs. An empty string returns if no row found.
190 --
191 -- Developer Implementation Notes:
192 --
193 -- {End Of Comments}
194
195 FUNCTION GET_APL_UPT_FOR_PERSON (
196 p_person_id per_all_people_f.person_id%TYPE default null,
197 p_eff_date date default trunc(sysdate))
198 RETURN VARCHAR2;
199 --
200 FUNCTION GET_APL_UPT_FOR_PARTY (
201 p_party_id number,
202 p_eff_date date default trunc(sysdate))
203 RETURN VARCHAR2;
204
205
206 -- ----------------------------------------------------------------------------
207 -- |-----------------------< GET_EMP_SPT_FOR_PERSON >------------------------|
208 -- ----------------------------------------------------------------------------
209 -- {Start Of Comments}
210 --
211 -- Description:
212 -- This functions returns a person's Employee System Person Type
213 --
214 -- Prerequisites:
215 -- This is a public function, mainly called by VO's
216 -- in the iRecruitment web module.
217 --
218 -- In Parameters:
219 -- A Person ID, and an effective date.
220 -- Post Success:
221 -- Returned varchar of employee system person type.
222 -- Post Failure:
223 -- No explicit error catching occurs. An empty string returns if no row found.
224 --
225 -- Developer Implementation Notes:
226 --
227 -- {End Of Comments}
228
229 FUNCTION GET_EMP_SPT_FOR_PERSON (
230 p_person_id per_all_people_f.person_id%TYPE default null,
231 p_eff_date date default trunc(sysdate))
232 RETURN VARCHAR2;
233
234 --
235 -- -------------------------------------------------------------------------
236 -- |----------------------< get_recruitment_person_id >--------------------|
237 -- -------------------------------------------------------------------------
238 -- {Start Of Comments}
239 --
240 -- Description:
241 -- This functions returns the recruitment person id. It first checks if
242 -- the party has a person with a notification preference. If it doesn't
243 -- find a record it then checks if a person exists in the default
244 -- business group for the party. If not, the person with the earliest
245 -- start date is returned.
246 --
247 -- Prerequisites:
248 --
249 -- In Parameters:
250 -- A Person ID,
251 -- Post Success:
252 -- Returned person ID
253 -- Post Failure:
254 -- No explicit error catching occurs. A null returns if no row found.
255 --
256 -- Developer Implementation Notes:
257 --
258 -- {End Of Comments}
259 --
260 --
261 FUNCTION GET_RECRUITMENT_PERSON_ID
262 (p_person_id IN per_all_people_f.person_id%TYPE,
263 p_effective_date IN per_all_people_f.effective_start_date%TYPE default trunc(sysdate))
264 RETURN per_all_people_f.person_id%TYPE;
265
266 -- ----------------------------------------------------------------------------
267 -- |-----------------------< IS_OPEN_PARTY >-------------------------|
268 -- ----------------------------------------------------------------------------
269
270 FUNCTION IS_OPEN_PARTY (p_party_id number
271 ,p_eff_date date )
272 RETURN VARCHAR2;
273
274 -- ----------------------------------------------------------------------------
275 -- |-----------------------< is_internal_person >-------------------------|
276 -- ----------------------------------------------------------------------------
277
278 FUNCTION is_internal_person (p_person_id number
279 ,p_eff_date date)
280 RETURN VARCHAR2;
281 -- ----------------------------------------------------------------------------
282 -- |-----------------------< is_internal_email >-------------------------|
283 -- ----------------------------------------------------------------------------
284
285 FUNCTION is_internal_email (p_email_address varchar2
286 ,p_eff_date date)
287 RETURN VARCHAR2;
288
289 -- ----------------------------------------------------------------------------
290 -- |-----------------------< is_internal_person >-------------------------|
291 -- ----------------------------------------------------------------------------
292 FUNCTION is_internal_person (p_user_name varchar2
293 ,p_eff_date date )
294 RETURN VARCHAR2;
295
296 -- ----------------------------------------------------------------------------
297 -- |-----------------------< is_function_allowed >-------------------------|
298 -- ----------------------------------------------------------------------------
299 --
300 -- TEST
301 -- Test if function is accessible under current responsibility.
302 -- IN
303 -- function_name - function to test
304 -- p_test_maint_availability- 'Y' (default) means check if available for
305 -- current value of profile APPS_MAINTENANCE_MODE
306 -- 'N' means the caller is checking so it's
307 -- unnecessary to check.
308 -- RETURNS
309 -- TRUE if function is accessible
310 FUNCTION is_function_allowed(p_function_name varchar2
311 ,p_test_maint_availability in varchar2 default 'Y')
312 RETURN VARCHAR2;
313
314
315 -- ----------------------------------------------------------------------------
316 -- |-----------------------< GET_LAST_QUAL_PTY >----------------------------|
317 -- ----------------------------------------------------------------------------
318 -- {Start Of Comments}
319 --
320 -- Description:
321 -- This functions returns a person's latest qualifications associated to esablishment.
322 --
323 -- Prerequisites:
324 -- This is a public function, mainly called by VO's representing search results
325 -- in the iRecruitment web module.
326 --
327 -- In Parameters:
328 -- A Party ID.
329 -- Effective Date.
330 -- Post Success:
331 -- Returned varchar of latest qualification title.
332 -- Post Failure:
333 -- No explicit error catching occurs. An empty string returns if no row found.
334 --
335 -- Developer Implementation Notes:
336 -- Added GET_LAST_QUAL_PTY by deenath to fix Bug #4726469
337 --
338 -- {End Of Comments}
339 FUNCTION GET_LAST_QUAL_PTY(p_party_id NUMBER,
340 p_eff_date DATE DEFAULT TRUNC(SYSDATE))
341 RETURN VARCHAR2;
342
343 -- ----------------------------------------------------------------------------
344 -- |-----------------------< irc_applicant_tracking >-----------------------|
345 -- ----------------------------------------------------------------------------
346 procedure irc_applicant_tracking(
347 p_person_id in number
348 ,p_apl_profile_access_id in number
349 ,p_object_version_number out nocopy number
350 );
351
352 -- ----------------------------------------------------------------------------
353 -- |-----------------------< irc_mark_appl_considered >---------------------|
354 -- ----------------------------------------------------------------------------
355 procedure irc_mark_appl_considered(
356 p_effective_date in date
357 ,p_assignment_id in number
358 ,p_attempt_id in number
359 ,p_assignment_details_id in number
360 ,p_qualified in varchar2
361 ,p_considered in varchar2
362 ,p_update_mode in varchar2
363 ,p_details_version out nocopy number
367 );
364 ,p_effective_start_date out nocopy date
365 ,p_effective_end_date out nocopy date
366 ,p_object_version_number out nocopy number
368
369 -- ----------------------------------------------------------------------------
370 -- |-----------------------< irc_mark_appl_considered >---------------------|
371 -- ----------------------------------------------------------------------------
372 procedure irc_mark_appl_considered(
373 p_assignment_id in number
374 );
375
376 -- ----------------------------------------------------------------------------
377 -- |-----------------------< getAMETxnDetailsForOffer >---------------------|
378 -- ----------------------------------------------------------------------------
379 function getAMETxnDetailsForOffer (p_offerId in varchar2)
380 return varchar2;
381
382 -- ----------------------------------------------------------------------------
383 -- |-----------------------< copy_candidate_details >---------------------|
384 -- ----------------------------------------------------------------------------
385 procedure copy_candidate_details (
386 p_assignment_id in number
387 );
388
389 -- ----------------------------------------------------------------------------
390 -- |---------------------------< get_fte_factor >---------------------------|
391 -- ----------------------------------------------------------------------------
392
393 FUNCTION get_fte_factor(p_assignment_id number
394 ,p_asg_hours_per_year NUMBER
395 ,p_position_id NUMBER
396 ,p_organization_id NUMBER
397 ,p_business_group_id NUMBER
398 ,p_effective_date IN DATE)
399 RETURN NUMBER;
400
401 FUNCTION get_fte_factor(p_assignment_id number)
402 RETURN NUMBER;
403
404 --
405 --
406 -- ----------------------------------------------------------------------------
407 -- |---------------------< getVacancyRunFunctionUrl >--------------------------|
408 -- ----------------------------------------------------------------------------
409 --
410 function getVacancyRunFunctionUrl(
411 p_function_id number,
412 p_vacancy_id number,
413 p_posting_id number,
414 p_site_id number
415 ) return varchar2 ;
416 --
417 --
418
419 -- ----------------------------------------------------------------------------
420 -- |-----------------------< getJobSearchItems >-----------------------------|
421 -- ----------------------------------------------------------------------------
422
423 function getJobSearchItems(l_keyword varchar2 default null,
424 l_job varchar2 default null,
425 l_employee varchar2 default null,
426 l_contractor varchar2 default null,
427 l_dateposted varchar2 default null,
428 l_travelpercent number default null,
429 l_workathome varchar2 default null,
430 l_employmentstatus varchar2 default null,
431 l_min_salary in varchar2 default null,
432 l_currency in varchar2 default null,
433 l_lat in varchar2 default null,
434 l_long in varchar2 default null,
435 l_distance in varchar2 default null,
436 l_location in varchar2 default null,
437 l_attribute_category in varchar2 default null,
438 l_attribute1 in varchar2 default null,
439 l_attribute2 in varchar2 default null,
440 l_attribute3 in varchar2 default null,
441 l_attribute4 in varchar2 default null,
442 l_attribute5 in varchar2 default null,
443 l_attribute6 in varchar2 default null,
444 l_attribute7 in varchar2 default null,
445 l_attribute8 in varchar2 default null,
446 l_attribute9 in varchar2 default null,
447 l_attribute10 in varchar2 default null,
448 l_attribute11 in varchar2 default null,
449 l_attribute12 in varchar2 default null,
450 l_attribute13 in varchar2 default null,
451 l_attribute14 in varchar2 default null,
452 l_attribute15 in varchar2 default null,
453 l_attribute16 in varchar2 default null,
454 l_attribute17 in varchar2 default null,
455 l_attribute18 in varchar2 default null,
456 l_attribute19 in varchar2 default null,
457 l_attribute20 in varchar2 default null,
458 l_attribute21 in varchar2 default null,
459 l_attribute22 in varchar2 default null,
460 l_attribute23 in varchar2 default null,
461 l_attribute24 in varchar2 default null,
462 l_attribute25 in varchar2 default null,
463 l_attribute26 in varchar2 default null,
464 l_attribute27 in varchar2 default null,
465 l_attribute28 in varchar2 default null,
466 l_attribute29 in varchar2 default null,
467 l_attribute30 in varchar2 default null,
468 langcode in varchar2 default null,
469 enterprise in varchar2 default null,
470 l_drvdlocal in varchar2 default null,
471 l_locid in varchar2 default null)
472 return clob;
473
474 -- ----------------------------------------------------------------------------
475 -- |-----------------------< getJobSearchChannel >--------------------------|
476 -- ----------------------------------------------------------------------------
477
478 function getJobSearchChannel(langcode in varchar2 default null,
479 enterprise in varchar2 default null)
480 return clob;
481
482 -- ----------------------------------------------------------------------------
483 -- |-----------------------< GET_RATE_SQL >--------------------------|
484 -- ----------------------------------------------------------------------------
485
486 FUNCTION GET_RATE_SQL
487 (l_from_currency IN varchar2,
488 l_to_currency IN varchar2,
489 l_exchange_date IN date)
490 RETURN number;
491
492 -- ----------------------------------------------------------------------------
496 function get_exchange_date(profile_date in varchar2,
493 -- |-----------------------< get_exchange_date >--------------------------|
494 -- ----------------------------------------------------------------------------
495
497 l_from_currency in varchar2,
498 effective_date in date default sysdate
499 )
500 return date;
501 -- ----------------------------------------------------------------------------
502 -- |-----------------------< is_salary_basis_required >---------------------|
503 -- ----------------------------------------------------------------------------
504
505 function is_salary_basis_required(p_business_group_id in number,
506 p_organization_id in number,
507 p_position_id in number,
508 p_grade_id in number,
509 p_job_id in number
510 )
511 return varchar2;
512 -- ----------------------------------------------------------------------------
513 -- |-----------------------< is_proposed_salary_required >------------------|
514 -- ----------------------------------------------------------------------------
515
516 FUNCTION is_proposed_salary_required
517 (
518 p_salary_basis_id IN NUMBER
519 )
520 RETURN VARCHAR2;
521 --
522 -- ----------------------------------------------------------------------------
523 -- |---------------------------< GET_PERSON_TYPE >----------------------------|
524 -- ----------------------------------------------------------------------------
525
526 FUNCTION get_person_type (p_party_id number
527 ,p_eff_date date )
528 RETURN VARCHAR2;
529 --
530 --
531 -- ----------------------------------------------------------------------------
532 -- |---------------------------< get_rehire_recommendation >------------------|
533 -- ----------------------------------------------------------------------------
534 FUNCTION get_rehire_recommendation(p_party_id in number
535 ,p_eff_date in date )
536 RETURN VARCHAR2;
537 --
538 --
539 -- ----------------------------------------------------------------------------
540 -- |---------------------------< get_rehire_recom_code >----------------------|
541 -- ----------------------------------------------------------------------------
542
543 FUNCTION get_rehire_recom_code(p_party_id in number
544 ,p_eff_date in date )
545 RETURN VARCHAR2;
546 --
547 -- ----------------------------------------------------------------------------
548 -- |---------------------------< getMobileNumber >----------------------------|
549 -- ----------------------------------------------------------------------------
550
551 function getMobileNumber(pPersonIdIn in number) return varchar2;
552 --
553 -- ----------------------------------------------------------------------------
554 -- |---------------------------< is_supervisor >------------------------------|
555 -- ----------------------------------------------------------------------------
556 FUNCTION is_supervisor(p_person_id in number
557 ,p_vacancy_id in number)
558 RETURN VARCHAR2;
559 --
560
561 -- ----------------------------------------------------------------------------
562 -- |---------------------------< get_Appl_Doc_Id >----------------------------|
563 -- ----------------------------------------------------------------------------
564 FUNCTION get_Appl_Doc_Id(p_person_id in number default -1,
565 p_party_id in number default -1,
566 p_assignment_id in number)
567 RETURN NUMBER;
568
569 -- ----------------------------------------------------------------------------
570 -- |---------------------------< get_Appl_Doc_Name >----------------------------|
571 -- ----------------------------------------------------------------------------
572 FUNCTION get_Appl_Doc_Name(p_doc_id in varchar2)
573 RETURN VARCHAR2;
574
575 --
576
577 END IRC_UTILITIES_PKG;