1 package hr_offer_custom AUTHID CURRENT_USER as
2 /* $Header: hrcustwf.pkh 115.5 2002/12/09 19:57:27 hjonnala ship $ */
3 -- ----------------------------------------------------------------------------
4 -- This is the generic product version
5 -- ----------------------------------------------------------------------------
6 --
7 -- ----------------------------------------------------------------------------
8 -- |------------------------< check_wf_question_status >----------------------|
9 -- ----------------------------------------------------------------------------
10 --
11 -- Description:
12 -- This function will return true if an active workflow exists using the
13 -- question passed in.
14 --
15 -- Pre Conditions:
16 --
17 -- In Arguments:
18 -- p_proposal_question_name -> The name of the question. This is one of the
19 -- questions which is hardcoded in the hr_letter program.
20 -- Post Success:
21 --
22 -- Post Failure:
23 --
24 -- Developer Implementation Notes:
25 -- This procedure can be customized (eg. May change so validates for a
26 -- workflow at any time in history that uses this question. Because if
27 -- we allow questions to be changed then past offer letters may look strange.)
28 --
29 -- Access Status:
30 -- Called from hr_update_questions_web.
31 --
32 -- ----------------------------------------------------------------------------
33 --
34 function check_wf_question_status (p_proposal_question_name varchar2)
35 return boolean;
36 --
37 -- ----------------------------------------------------------------------------
38 -- |-----------------------------< get_hr_routing1 >--------------------------|
39 -- ----------------------------------------------------------------------------
40 --
41 -- Description:
42 -- This function will return the person_id of the HR Routing 1 person
43 --
44 -- Pre Conditions:
45 -- The current hiring manager must exist.
46 --
47 -- In Arguments:
48 -- p_person_id -> The person_id of the hiring manager.
49 --
50 -- Post Success:
51 --
52 -- Post Failure:
53 -- This procedure should never fail.
54 --
55 -- Developer Implementation Notes:
56 -- This procedure can be customized.
57 --
58 -- Access Status:
59 -- Called from workflow/offer letter generation.
60 --
61 -- ----------------------------------------------------------------------------
62 function get_hr_routing1
63 (p_person_id in per_people_f.person_id%type)
64 return per_people_f.person_id%type;
65 -- ----------------------------------------------------------------------------
66 -- |-----------------------------< get_hr_routing2 >--------------------------|
67 -- ----------------------------------------------------------------------------
68 --
69 -- Description:
70 -- This function will return the person_id of the HR Routing 2 person
71 --
72 -- Pre Conditions:
73 -- The current hiring manager must exist.
74 --
75 -- In Arguments:
76 -- p_person_id -> The person_id of the hiring manager.
77 --
78 -- Post Success:
79 --
80 -- Post Failure:
81 -- This procedure should never fail.
82 --
83 -- Developer Implementation Notes:
84 -- This procedure can be customized.
85 --
86 -- Access Status:
87 -- Called from workflow/offer letter generation.
88 --
89 -- ----------------------------------------------------------------------------
90 function get_hr_routing2
91 (p_person_id in per_people_f.person_id%type)
92 return per_people_f.person_id%type;
93 -- ----------------------------------------------------------------------------
94 -- |-----------------------------< get_hr_routing3 >--------------------------|
95 -- ----------------------------------------------------------------------------
96 --
97 -- Description:
98 -- This function will return the person_id of the HR Routing 3 person
99 --
100 -- Pre Conditions:
101 -- The current hiring manager must exist.
102 --
103 -- In Arguments:
104 -- p_person_id -> The person_id of the hiring manager.
105 --
106 -- Post Success:
107 --
108 -- Post Failure:
109 -- This procedure should never fail.
110 --
111 -- Developer Implementation Notes:
112 -- This procedure can be customized.
113 --
114 -- Access Status:
115 -- Called from workflow/offer letter generation.
116 --
117 -- ----------------------------------------------------------------------------
118 function get_hr_routing3
119 (p_person_id in per_people_f.person_id%type)
120 return per_people_f.person_id%type;
121 -- ----------------------------------------------------------------------------
122 -- |---------------------------< get_candidate_details >----------------------|
123 -- ----------------------------------------------------------------------------
124 --
125 -- Description:
126 -- This procedure will return the person_id and p_candidate_disp_name
127 -- (first name + <space> + last name) for a given assignment_id as of the
128 -- system date.
129 --
130 -- Pre Conditions:
131 -- The assignment must exist
132 --
133 -- In Arguments:
134 -- p_candidate_assignment_id -> The assignment_id of the candidate
135 -- (applicant).
136 --
137 -- Post Success:
138 -- If the assignment exists, the out parameters p_candidate_person_id and
139 -- p_candidate_disp_name will be set accordingly.
140 -- If the assignment does not exist, the out parameters are set to null.
141 --
142 -- Post Failure:
143 -- This procedure should never fail.
144 --
145 -- Developer Implementation Notes:
146 -- This procedure currently works of the system date (i.e. sysdate). This is
147 -- intentional behaviour but in future may change to accept the effective
148 -- date as an in parameter.
149 --
150 -- Access Status:
151 -- Called from workflow.
152 --
153 -- ----------------------------------------------------------------------------
154 procedure get_candidate_details
155 (p_candidate_assignment_id in per_assignments_f.assignment_id%type
156 ,p_candidate_person_id out nocopy per_people_f.person_id%type
157 ,p_candidate_disp_name out nocopy varchar2
158 ,p_applicant_number out nocopy per_people_f.applicant_number%type);
159 -- ----------------------------------------------------------------------------
160 -- |-------------------------< get_hr_manager_details >-----------------------|
161 -- ----------------------------------------------------------------------------
162 --
163 -- Description:
164 -- This function will return the person_id of the HR manager
165 -- for the specified hiring manager person.
166 --
167 -- Pre Conditions:
168 -- The current hiring manager must exist.
169 --
170 -- In Arguments:
171 -- p_person_id -> The person_id of the hiring manager.
172 --
173 -- Post Success:
174 --
175 -- Post Failure:
176 -- This procedure should never fail.
177 --
178 -- Developer Implementation Notes:
179 -- This procedure can be customized.
180 --
181 -- Access Status:
182 -- Called from offer letter generation.
183 --
184 -- ----------------------------------------------------------------------------
185 function get_hr_manager_details
186 (p_person_id in per_people_f.person_id%type)
187 return per_people_f.person_id%type;
188 -- ----------------------------------------------------------------------------
189 -- |---------------------< set_training_admin_person >------------------------|
190 -- ----------------------------------------------------------------------------
191 --
192 -- Description:
193 -- This function will return the person_id of the Training Administrator
194 --
195 -- Pre Conditions:
196 -- The current training administrator must exist.
197 --
198 -- In Arguments:
199 --
200 -- Post Success:
201 --
202 -- Post Failure:
203 -- This procedure should never fail.
204 --
205 -- Developer Implementation Notes:
206 -- This procedure can be customized.
207 --
208 -- Access Status:
209 -- Called from offer letter generation.
210 --
211 -- ----------------------------------------------------------------------------
212 function set_training_admin_person
213 return number;
214 -- ----------------------------------------------------------------------------
215 -- |---------------------< set_supervisor_id >------------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 -- Description:
219 -- This function will return the person_id of the a persons supervisor
220 --
221 -- Pre Conditions:
222 -- A supervisor must exist. If not return null.
223 --
224 -- In Arguments:
225 --
226 -- Post Success:
227 --
228 -- Post Failure:
229 -- This procedure should never fail.
230 --
231 -- Developer Implementation Notes:
232 -- This procedure can be customized.
233 --
234 -- Access Status:
235 -- Called from offer letter generation.
236 --
237 -- ----------------------------------------------------------------------------
238 function set_supervisor_id (p_person_id in per_all_people_f.person_id%type)
239 return per_all_people_f.person_id%type ;
240 -- ----------------------------------------------------------------------------
241 -- |-----------------------------< get_next_approver >------------------------|
242 -- ----------------------------------------------------------------------------
243 --
244 -- Description:
245 -- This function will return the person_id of the next approver/supervisor
246 -- for the specified person. If an approver does not exist, null is returned.
247 --
248 -- Pre Conditions:
249 -- The current approver p_person_id must exist.
250 --
251 -- In Arguments:
252 -- p_person_id -> The person_id of the current approver.
253 --
254 -- Post Success:
255 -- If the current person has an approver/supervisor then the supervisor
256 -- person_id is returned. If a supervisor cannot be found then null is
257 -- returned.
258 --
259 -- Post Failure:
260 -- This procedure should never fail.
261 --
262 -- Developer Implementation Notes:
263 -- This procedure can be customized.
264 --
265 -- Access Status:
266 -- Called from workflow.
267 --
268 -- ----------------------------------------------------------------------------
269 function get_next_approver
270 (p_person_id in per_people_f.person_id%type)
271 return per_people_f.person_id%type;
272 -- ------------------------------------------------------------------------
273 -- |---------------------< get_url_string >----------------------------------|
274 -- ------------------------------------------------------------------------
275 --
276 -- Description:
277 -- This procedure returns the url string needed to build up urls when
278 -- running disconnected from the web server (such as from email for
279 -- workflow notifications).
280 -- ------------------------------------------------------------------------
281 function get_url_string
282 return varchar2;
283 -- ------------------------------------------------------------------------
284 -- |---------------------< get_vp_name >----------------------------------|
285 -- ------------------------------------------------------------------------
286 --
287 -- Description:
288 -- This procedure obtains the name of the Vice President that the
289 -- candidate will report to. It goes up the approval chain for a
290 -- candidate and finds the first person with a job name like '%Vice%'
291 -- Obviously this won't work in the police field.
292 --
293 -- Pre Conditions:
294 -- Insert a row into fnd_sessions for date tracked tables.
295 --
296 -- In Arguments:
297 -- Name Reqd Type Description
298 -- p_assignment_id Yes number Assignment ID of the candidate.
299 --
300 --
301 -- Post Success:
302 -- Out Arguments:
303 -- p_vp_name varchar2 first_name || last_name for VP
304 -- p_job_name varchar2 vice president's job name
305 --
306 -- Post Failure:
307 -- If the procedure can not find a vice president, it returns null and the
308 -- calling module is expected to either give an error or display nothing,
309 -- depending upon the business need.
310 --
311 -- Developer Implementation Notes:
312 --
313 -- Access Status:
314 --
315 -- -----------------------------------------------------------------------------
316 procedure get_vp_name
317 (p_assignment_id in number
318 ,p_vp_name out nocopy varchar2
319 ,p_job_name out nocopy varchar2);
320 --
321 -- ----------------------------------------------------------------------------
322 -- |--------------------------< check_final_approver >------------------------|
323 -- ----------------------------------------------------------------------------
324 --
325 -- Description:
326 -- This function will return a varchar2(1) value. 'Y' is returned if the
327 -- forwarding manager is the TOP approver for the specified candidate.
328 -- 'N' is returned if the forwarding manager is NOT the TOP approver.
329 -- 'E' is returned if the program can not find the top approver.
330 --
331 -- Pre Conditions:
332 -- The candidate and forward to manager's exist.
333 --
334 -- In Arguments:
335 -- p_candidate_assignment_id -> The assignment_id of the candidate.
336 -- p_fwd_to_mgr_id -> The person_id of the forwarding to manager (current
337 -- approver).
338 -- p_person_id -> The person_id of the hiring mgr.
339 --
340 -- Post Success:
341 -- 'Y', 'N' or 'E' (for error) will be returned.
342 --
343 -- Post Failure:
344 -- This function should never fail.
345 --
346 -- Developer Implementation Notes:
347 -- This procedure can be customized
348 --
349 -- Access Status:
350 -- Called from workflow.
351 --
352 -- ----------------------------------------------------------------------------
353 function check_final_approver
354 (p_candidate_assignment_id in per_assignments_f.assignment_id%type
355 ,p_fwd_to_mgr_id in per_people_f.person_id%type
356 ,p_person_id in per_people_f.person_id%type)
357 return varchar2;
358 -- ----------------------------------------------------------------------------
359 -- |-----------------------< check_if_in_approval_chain >---------------------|
360 -- ----------------------------------------------------------------------------
361 --
362 -- Description:
363 -- This function will return a boolean value. TRUE is returned if the
364 -- person specified is in the approval chain.
365 -- FALSE is returned if the person is NOT in the chain.
366 --
367 -- Pre Conditions:
368 -- The person must exist.
369 --
370 -- In Arguments:
371 -- p_candidate_assignment_id -> The assignment_id of the candidate.
372 -- p_fwd_to_mgr_id -> The person_id who is being checked
373 --
374 -- Post Success:
375 -- TRUE or FALSE will be returned.
376 --
377 -- Post Failure:
378 -- This function should never fail.
379 --
380 -- Developer Implementation Notes:
381 -- This procedure can be customized
382 --
383 -- Access Status:
384 -- Called from workflow.
385 --
386 -- ----------------------------------------------------------------------------
387 function check_if_in_approval_chain
388 (p_person_id in per_people_f.person_id%type
389 ,p_candidate_assignment_id in per_assignments_f.assignment_id%type)
390 return boolean;
391 -- ----------------------------------------------------------------------------
392 -- |--------------------------< get_signatories_details >---------------------|
396 -- This function will return a offer letter signatories for the specified
393 -- ----------------------------------------------------------------------------
394 --
395 -- Description:
397 -- hiring manager and candidate.
398 --
399 -- Pre Conditions:
400 -- The person and candidate must exist.
401 --
402 -- In Arguments:
403 -- p_candidate_assignment_id -> The assignment_id of the candidate.
404 -- p_person_id -> The person_id of the hiring manager.
405 --
406 -- Post Success:
407 -- The OUT arguments p_signatory_id1..3 will be optionally set with the
408 -- person_id's and position title.
409 --
410 -- Post Failure:
411 -- This procedure should never fail.
412 --
413 -- Developer Implementation Notes:
414 -- This procedure can be customized.
415 --
416 -- Access Status:
417 -- Called from offer letter generation.
418 --
419 -- ----------------------------------------------------------------------------
420 procedure get_signatories_details
421 (p_person_id in per_people_f.person_id%type
422 ,p_candidate_assignment_id in per_assignments_f.assignment_id%type
423 ,p_signatory_id1 out nocopy per_people_f.person_id%type
424 ,p_position_title1 out nocopy varchar2
425 ,p_signatory_id2 out nocopy per_people_f.person_id%type
426 ,p_position_title2 out nocopy varchar2
427 ,p_signatory_id3 out nocopy per_people_f.person_id%type
428 ,p_position_title3 out nocopy varchar2);
429 --
430 -- ----------------------------------------------------------------------------
431 -- |--------------------------< set_status_to_offer >-------------------------|
432 -- ----------------------------------------------------------------------------
433 --
434 -- Description:
435 -- This procedure will call the hr_assignment_api.offer_apl_asg to update the
436 -- status on the applicant assignment to OFFER.
437 --
438 -- Pre Conditions:
439 -- The applicant must exist.
440 --
441 -- In Arguments:
442 -- p_candidate_assignment_id -> The assignment_id of the candidate
443 -- (applicant).
444 -- Post Success:
445 -- The applicant assignment will be updated to the OFFER status.
446 --
447 -- Post Failure:
448 -- The API will raise an error which will be trapped and reported by
449 -- workflow.
450 --
451 -- Developer Implementation Notes:
452 --
453 --
454 -- Access Status:
455 -- Called from workflow.
456 --
457 -- ----------------------------------------------------------------------------
458 procedure set_status_to_offer
459 (p_candidate_assignment_id in per_assignments_f.assignment_id%type);
460 -- ----------------------------------------------------------------------------
461 -- |--------------------------< set_status_to_sent >--------------------------|
462 -- ----------------------------------------------------------------------------
463 --
464 -- Description:
465 -- This procedure will call the hr_assignment_api.offer_apl_asg to update the
466 -- status on the applicant assignment to a customized Offer Sent status
467 --
468 -- Pre Conditions:
469 -- The applicant must exist.
470 --
471 -- In Arguments:
472 -- p_candidate_assignment_id -> The assignment_id of the candidate
473 -- (applicant).
474 -- Post Success:
475 -- The applicant assignment will be updated to the OFFER status.
476 --
477 -- Post Failure:
478 -- The API will raise an error which will be trapped and reported by
479 -- workflow.
480 --
481 -- Developer Implementation Notes:
482 --
483 --
484 -- Access Status:
485 -- Called from workflow.
486 --
487 -- ----------------------------------------------------------------------------
488 procedure set_status_to_sent
489 (p_candidate_assignment_id in per_assignments_f.assignment_id%type);
490 --
491 -- ----------------------------------------------------------------------------
492 -- |--------------------------< insert_attachment >----------------------------|
493 -- ----------------------------------------------------------------------------
494 --
495 procedure insert_attachment
496 (p_attachment_text in long default null
497 ,p_entity_name in varchar2 default null
498 ,p_pk1_value in varchar2 default null
499 ,p_attached_document_id out nocopy
500 fnd_attached_documents.attached_document_id%TYPE
501 ,p_document_id out nocopy fnd_documents.document_id%TYPE
502 ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
503 ,p_rowid out nocopy varchar2
504 ,p_login_person_id in number); -- 10/14/97 Changed
505
506 -- ----------------------------------------------------------------------------
507 -- |--------------------------< update_attachment >----------------------------|
508 -- ----------------------------------------------------------------------------
509 --
510 procedure update_attachment
511 (p_attachment_text in long default null
512 ,p_entity_name in varchar2 default null
513 ,p_pk1_value in varchar2 default null
514 ,p_rowid in varchar2
515 ,p_login_person_id in number); -- 10/14/97 Changed
516
517 -- ----------------------------------------------------------------------------
518 -- |--------------------------< get_attachment >-------------------------------|
519 -- ----------------------------------------------------------------------------
520 --
521 procedure get_attachment
522 (p_attachment_text out nocopy long
523 ,p_entity_name in varchar2 default null
524 ,p_pk1_value in varchar2 default null
525 ,p_effective_date in varchar2
526 ,p_attached_document_id out nocopy
527 fnd_attached_documents.attached_document_id%TYPE
528 ,p_document_id out nocopy fnd_documents.document_id%TYPE
529 ,p_media_id out nocopy fnd_documents_tl.media_id%TYPE
530 ,p_rowid out nocopy varchar2
531 ,p_category_id out nocopy fnd_documents.category_id%type
532 ,p_seq_num out nocopy fnd_attached_documents.seq_num%type);
533
534 -- ----------------------------------------------------------------------------
535 -- |--------------------------< validate_phone_format >-----------------------|
536 -- ----------------------------------------------------------------------------
537 --
538 procedure validate_phone_format
539 (p_phone_num_in in varchar2
540 ,p_country_code in varchar2 default 'US'
541 ,p_phone_num_out out nocopy varchar2
542 ,p_phone_format_err out nocopy varchar2);
543 --
544 --
545 end hr_offer_custom;
[Home] [Help]