[Home] [Help]
PACKAGE BODY: APPS.HR_OFFER_WF
Source
1 package body hr_offer_wf as
2 /* $Header: hrofwrwf.pkb 115.3 2002/12/12 07:19:49 hjonnala ship $ */
3 --
4 g_owa2 varchar2(2000);
5 g_url varchar2(2000);
6 -- ------------------------------------------------------------------------
7 -- |----------------------< Start_Hroffer_Process>-------------------------|
8 -- ------------------------------------------------------------------------
9 --
10 -- Description
11 --
12 -- Initial the HR offer Workflow process
13 --
14 --
15 --
16 procedure Start_Hroffer_Process (p_hiring_mgr_id in number
17 ,p_candidate_assignment_id in number
18 ,p_process in varchar2
19 ,p_read_parameters in varchar2) is
20 --
21 l_ItemType wf_items.item_type%TYPE := 'HR_OFFER';
22 l_ItemKey wf_items.item_key%TYPE := p_candidate_assignment_id;
23 l_resubmit_offer_start_point wf_process_activities.instance_label%type;
24 --
25
26
27 --
28 begin
29 --
30 -- 04/14/97 Change Begins
31 IF p_read_parameters = 'R' THEN
32 --
33 l_resubmit_offer_start_point
34 := wf_engine.GetItemAttrText
35 (itemtype => l_ItemType,
36 itemkey => l_ItemKey,
37 aname => 'HR_RESUBMIT_OFFER_SAVEPOINT');
38 --
39 wf_engine.HandleError(itemtype => l_ItemType
40 ,itemkey => l_ItemKey
41 ,activity => l_resubmit_offer_start_point
42 ,command => 'RETRY');
43
44 goto process_end;
45
46 ELSIF p_read_parameters = 'B' THEN
47 g_bypass_next_apprvr := 'Y';
48 ELSE
49 g_bypass_next_apprvr := 'N';
50 END IF;
51 --
52 -- 04/14/97 Change Ends
53 --
54 wf_engine.CreateProcess ( ItemType => l_ItemType,
55 ItemKey => l_ItemKey,
56 process => p_process );
57 --
58 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
59 itemkey => l_ItemKey,
60 aname => 'CANDIDATE_ASSIGNMENT_ID',
61 avalue => p_candidate_assignment_id );
62 --
63 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
64 itemkey => l_ItemKey,
65 aname => 'HIRING_MGR_ID',
66 avalue => p_hiring_mgr_id );
67 --
68 wf_engine.StartProcess ( ItemType => l_ItemType,
69 ItemKey => l_ItemKey );
70 --
71 -- 04/14/97 Change begins
72 <<process_end>>
73 null;
74 -- 04/14/97 Change ends
75
76 end Start_Hroffer_Process;
77 --
78 -- ------------------------------------------------------------------------
79 -- |------------------------< Initialize >---------------------------------|
80 -- ------------------------------------------------------------------------
81 --
82 -- Description
83 --
84 -- Initialize Workflow Item Attributes
85 --
86 --
87 procedure Initialize( itemtype in varchar2,
88 itemkey in varchar2,
89 actid in number,
90 funmode in varchar2,
91 result out nocopy varchar2 ) is
92 --
93 l_hiring_mgr_id per_people_f.person_id%type;
94 l_hiring_mgr_username wf_users.name%type;
95 l_hiring_mgr_disp_name wf_users.display_name%type;
96 l_candidate_assignment_id per_assignments_f.assignment_id%type;
97 l_candidate_person_id per_people_f.person_id%type;
98 l_candidate_disp_name wf_users.display_name%type;
99 l_candidate_appl_number per_people_f.applicant_number%type;
100 l_fwd_from_username wf_users.name%type;
101 l_fwd_from_disp_name wf_users.display_name%type;
102 l_resubmit_offer_savepoint wf_process_activities.instance_label%type;
103 --
104 --
105 begin
106 --
107 if ( funmode = 'RUN' ) then
108 -- 03/07/97 We need to set the Bypass
109 -- attribute for later on processing to bypass the next
110 -- approver.
111 --
112
113 wf_engine.SetItemAttrText (itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'BYPASS',
116 avalue => g_bypass_next_apprvr);
117 -- 03/07/97 Change ends
118 --
119 -- Get hring manager details and store in item attrributes
120 --
121 l_hiring_mgr_id := wf_engine.GetItemAttrNumber
122 (itemtype => itemtype,
123 itemkey => itemkey,
124 aname => 'HIRING_MGR_ID' );
125 --
126 -- 03/07/97 Change ends
127
128 -- 04/14/97 Change begins
129 -- -----------------------------------------------------------
130 -- Need to save the instance label name for this activity.
131 -- The instance label name will be used for rollback all
132 -- workflow processes which have been completed from this
133 -- point when resubmit an offer.
134 -- -----------------------------------------------------------
135 l_resubmit_offer_savepoint :=
136 hr_workflow_utility.get_activity_instance_label (p_actid => actid);
137
138 --
139 wf_engine.SetItemAttrText (itemtype => itemtype,
140 itemkey => itemkey,
141 aname => 'HR_RESUBMIT_OFFER_SAVEPOINT',
142 avalue => l_resubmit_offer_savepoint);
143 --
144 -- 04/14/97 Change ends
145 --
146
147 wf_directory.GetUserName(p_orig_system => 'PER',
148 p_orig_system_id => l_hiring_mgr_id,
149 p_name => l_hiring_mgr_username,
150 p_display_name => l_hiring_mgr_disp_name );
151 --
152 wf_engine.SetItemAttrText (itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'HIRING_MGR_USERNAME',
155 avalue => l_hiring_mgr_username );
156 --
157 wf_engine.SetItemAttrText (itemtype => itemtype,
158 itemkey => itemkey,
159 aname => 'HIRING_MGR_DISP_NAME',
160 avalue => l_hiring_mgr_disp_name );
161 --
162 -- Set fwd to = hiring manager in case this is the only person in
163 -- approval chain.
164 --
165 --
166 wf_engine.SetItemAttrText ( itemtype => itemtype,
167 itemkey => itemkey,
168 aname => 'FWD_TO_USERNAME',
169 avalue => l_hiring_mgr_username );
170 --
171 wf_engine.SetItemAttrText ( itemtype => itemtype,
172 itemkey => itemkey,
173 aname => 'FWD_TO_DISP_NAME',
174 avalue => l_hiring_mgr_disp_name );
175 --
176 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
177 itemkey => itemkey,
178 aname => 'FWD_TO_ID',
179 avalue => l_hiring_mgr_id) ;
180 --
181 -- Set fwd from = hiring manager. If this hiring manager has no
182 -- supervisor, we won't ever call get_next_approver, so we have to
183 -- set all the variables for 'fwd from'.
184 --
185 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'FWD_FROM_ID',
188 avalue => l_hiring_mgr_id) ;
189
190 wf_directory.GetUserName( p_orig_system => 'PER',
191 p_orig_system_id => l_hiring_mgr_id,
192 p_name => l_fwd_from_username,
193 p_display_name => l_fwd_from_disp_name) ;
194 --
195 wf_engine.SetItemAttrText( itemtype => itemtype,
196 itemkey => itemkey,
197 aname => 'FWD_FROM_USERNAME',
198 avalue => l_fwd_from_username );
199 --
200 wf_engine.SetItemAttrText( itemtype => itemtype,
201 itemkey => itemkey,
202 aname => 'FWD_FROM_DISP_NAME',
203 avalue => l_fwd_from_disp_name );
204 --
205 -- Get candidate details and store in item attributess
206 --
207 l_candidate_assignment_id :=
208 wf_engine.GetItemAttrNumber(itemtype => itemtype,
209 itemkey => itemkey,
210 aname => 'CANDIDATE_ASSIGNMENT_ID' );
211 --
212 hr_offer_custom.get_candidate_details
213 (p_candidate_assignment_id => l_candidate_assignment_id,
214 p_candidate_person_id => l_candidate_person_id,
215 p_candidate_disp_name => l_candidate_disp_name,
216 p_applicant_number => l_candidate_appl_number);
217 --
218 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
219 itemkey => itemkey,
220 aname => 'CANDIDATE_PERSON_ID',
221 avalue => l_candidate_person_id);
222 --
223
224 wf_engine.SetItemAttrText ( itemtype => itemtype,
225 itemkey => itemkey,
226 aname => 'CANDIDATE_DISP_NAME',
227 avalue => l_candidate_disp_name );
228 --
229 --
230 wf_engine.SetItemAttrText ( itemtype => itemtype,
231 itemkey => itemkey,
232 aname => 'CANDIDATE_APPL_NUMBER',
233 avalue => l_candidate_appl_number );
234 --
235 elsif ( funmode = 'CANCEL' ) then
236 --
237 null;
238 --
239 end if;
240 --
241 end initialize;
242 --
243 -- ------------------------------------------------------------------------
244 -- |------------------------< Get_next_approver >-------------------------|
245 -- ------------------------------------------------------------------------
246 --
247 -- Description
248 --
249 -- Get a persons manager
250 --
251 --
252 procedure Get_Next_Approver ( itemtype in varchar2,
253 itemkey in varchar2,
254 actid in number,
255 funmode in varchar2,
256 result out nocopy varchar2 ) is
257 --
258 l_hiring_mgr_id per_people_f.person_id%type;
259 l_fwd_from_id per_people_f.person_id%type;
260 l_fwd_from_username wf_users.name%type;
261 l_fwd_from_disp_name wf_users.display_name%type;
262 l_fwd_to_id per_people_f.person_id%type;
263 l_fwd_to_username wf_users.name%type;
264 l_fwd_to_disp_name wf_users.display_name%type;
265
266 --
267 begin
268 --
269 if ( funmode = 'RUN' ) then
270 --
271 l_fwd_from_id := wf_engine.GetItemAttrNumber
272 (itemtype => itemtype,
273 itemkey => itemkey,
274 aname => 'FWD_FROM_ID' );
275 --
276 -- if null this must be the first time in
277 --
278 if ( l_fwd_from_id is null ) then
279 --
280 l_fwd_from_id := wf_engine.GetItemAttrNumber
281 (itemtype => itemtype,
282 itemkey => itemkey,
283 aname => 'HIRING_MGR_ID' );
284 --
285 wf_engine.SetItemAttrNumber( itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'FWD_FROM_ID',
288 avalue => l_fwd_from_id );
289 --
290 end if;
291 --
292 -- Get the username and display name for forward from person and
293 -- save to item attributes
294 --
295 wf_directory.GetUserName(p_orig_system => 'PER',
296 p_orig_system_id => l_fwd_from_id,
297 p_name => l_fwd_from_username,
298 p_display_name => l_fwd_from_disp_name) ;
299 --
300 wf_engine.SetItemAttrText( itemtype => itemtype,
301 itemkey => itemkey,
302 aname => 'FWD_FROM_USERNAME',
303 avalue => l_fwd_from_username );
304 --
305 wf_engine.SetItemAttrText( itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'FWD_FROM_DISP_NAME',
308 avalue => l_fwd_from_disp_name );
309 --
310 -- Get manager
311 --
312 l_fwd_to_id := hr_offer_custom.Get_Next_Approver
313 (p_person_id => l_fwd_from_id);
314 --
315 if ( l_fwd_to_id is null ) then
316 --
317 result := 'F';
318 --
319 else
320 --
321 wf_directory.GetUserName( p_orig_system => 'PER',
322 p_orig_system_id => l_fwd_to_id,
323 p_name => l_fwd_to_username,
324 p_display_name => l_fwd_to_disp_name) ;
325 --
326 wf_engine.SetItemAttrNumber( itemtype => itemtype,
327 itemkey => itemkey,
328 aname => 'FWD_TO_ID',
329 avalue => l_fwd_to_id );
330 --
331 wf_engine.SetItemAttrText( itemtype => itemtype,
332 itemkey => itemkey,
333 aname => 'FWD_TO_USERNAME',
334 avalue => l_fwd_to_username );
335
336
337 Wf_engine.SetItemAttrText( itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'FWD_TO_DISP_NAME',
340 avalue => l_fwd_to_disp_name );
341 --
342 -- set forward from = to foward to
343 --
344 wf_engine.SetItemAttrNumber( itemtype => itemtype,
345 itemkey => itemkey,
346 aname => 'FWD_FROM_ID',
347 avalue => l_fwd_to_id );
348 --
349 --
350 result := 'T';
351 --
352 end if;
353 --
354 elsif ( funmode = 'CANCEL' ) then
355 --
356 null;
357 --
358 end if;
359 --
360 end Get_next_approver;
361 --
362 -- ------------------------------------------------------------------------
363 -- |----------------------< Check_Final_Approver >-------------------------|
364 -- ------------------------------------------------------------------------
365 --
366 -- Description
367 --
368 -- Determine if this person is the final manager in the approval chain
369 --
370 --
371 procedure Check_Final_Approver( itemtype in varchar2,
372 itemkey in varchar2,
373 actid in number,
374 funmode in varchar2,
375 result out nocopy varchar2 ) is
376 --
377 l_hiring_mgr_id per_people_f.person_id%type;
378 l_fwd_to_id per_people_f.person_id%type;
379 l_candidate_assignment_id per_assignments_f.assignment_id%type;
380 --
381 begin
382 --
383 if ( funmode = 'RUN' ) then
384 --
385 --
386 l_hiring_mgr_id := wf_engine.GetItemAttrNumber
387 (itemtype => itemtype,
388 itemkey => itemkey,
389 aname => 'HIRING_MGR_ID' );
390 --
391 l_fwd_to_id := wf_engine.GetItemAttrNumber
392 (itemtype => itemtype,
393 itemkey => itemkey,
394 aname => 'FWD_TO_ID' );
395 --
396 l_candidate_assignment_id := wf_engine.GetItemAttrNumber
397 (itemtype => itemtype,
398 itemkey => itemkey,
399 aname => 'CANDIDATE_ASSIGNMENT_ID' );
400 --
401 -- check final approver returns a 'Y', 'N' or 'E' for error
402 --
403 result := ( hr_offer_custom.Check_Final_approver
404 (p_candidate_assignment_id => l_candidate_assignment_id,
405 p_fwd_to_mgr_id => l_fwd_to_id,
406 p_person_id => l_hiring_mgr_id ) );
407 --
408 elsif ( funmode = 'CANCEL' ) then
409 --
410 null;
411 --
412 end if;
413 --
414 end Check_Final_Approver;
415 --
416 -- ------------------------------------------------------------------------
417 -- |----------------------< Set_Status_To_Offer >-------------------------|
418 -- ------------------------------------------------------------------------
419 --
420 -- Description
421 --
422 -- Set the status of a candidates application to offer
423 --
424 --
425 procedure Set_Status_To_offer( itemtype in varchar2,
426 itemkey in varchar2,
427 actid in number,
428 funmode in varchar2,
429 result out nocopy varchar2 ) is
430 --
431 l_candidate_assignment_id per_assignments_f.assignment_id%type;
432 --
433 begin
434 --
435 if ( funmode = 'RUN' ) then
436 --
437 l_candidate_assignment_id := wf_engine.GetItemAttrNumber
438 (itemtype => itemtype,
439 itemkey => itemkey,
440 aname => 'CANDIDATE_ASSIGNMENT_ID' );
441 --
442 hr_offer_custom.set_status_to_offer
443 (p_candidate_assignment_id => l_candidate_assignment_id);
444 --
445 elsif ( funmode = 'CANCEL' ) then
446 --
447 null;
448 --
449 end if;
450 --
451 end Set_Status_To_offer;
452 --
453 -- ------------------------------------------------------------------------
454 -- |----------------------< Set_Status_To_Sent >---------------------------|
455 -- ------------------------------------------------------------------------
456 --
457 -- Description
458 --
459 -- Set the status of a candidates application to offer sent
460 --
461 --
462 procedure Set_Status_To_Sent( itemtype in varchar2,
463 itemkey in varchar2,
464 actid in number,
465 funmode in varchar2,
466 result out nocopy varchar2 ) is
467 --
468 l_candidate_assignment_id per_assignments_f.assignment_id%type;
469 --
470 begin
471 --
472 if ( funmode = 'RUN' ) then
473 --
474 l_candidate_assignment_id := wf_engine.GetItemAttrNumber
475 (itemtype => itemtype,
476 itemkey => itemkey,
477 aname => 'CANDIDATE_ASSIGNMENT_ID' );
478 --
479 hr_offer_custom.set_status_to_sent
480 (p_candidate_assignment_id => l_candidate_assignment_id);
481 --
482 elsif ( funmode = 'CANCEL' ) then
483 --
484 null;
485 --
486 end if;
487 --
488 end Set_Status_To_Sent;
489 --
490 -- ------------------------------------------------------------------------
491 -- |----------------------< Reset_Approval_Chain >-------------------------|
492 -- ------------------------------------------------------------------------
493 --
494 -- Description
495 --
496 -- Set the approval chain back to the hiring manager
497 --
498 --
499 procedure Reset_Approval_chain( itemtype in varchar2,
500 itemkey in varchar2,
501 actid in number,
502 funmode in varchar2,
503 result out nocopy varchar2 ) is
504 --
505 l_hiring_mgr_id per_people_f.person_id%type;
506 l_fwd_from_id per_people_f.person_id%type;
507 --
508 begin
509 --
510 if ( funmode = 'RUN' ) then
511 --
512 l_hiring_mgr_id := wf_engine.GetItemAttrNumber
513 (itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'HIRING_MGR_ID' );
516 --
517 wf_engine.SetItemAttrNumber(itemtype => itemtype,
518 itemkey => itemkey,
519 aname => 'FWD_FROM_ID',
520 avalue => l_hiring_mgr_id );
521 --
522 elsif ( funmode = 'CANCEL' ) then
523 --
524 null;
525 --
526 end if;
527 --
528 end Reset_Approval_chain;
529 --
530 -- ------------------------------------------------------------------------
531 -- |----------------------< Get_summary_URL >------------------------------|
532 -- ------------------------------------------------------------------------
533 --
534 -- Description
535 --
536 -- This function will return the URL for the approval managers to
537 -- navigate to a display-only version of the offer information.
538 -- The highlights form is one of the 'tab' forms whose
539 -- setup procedure is in hr_offer_resume_web.setup.
540 --
541 --
542 procedure Get_Summary_URL( itemtype in varchar2,
543 itemkey in varchar2,
544 actid in number,
545 funmode in varchar2,
546 result out nocopy varchar2 ) is
547 --
548 l_candidate_assignment_id per_assignments_f.assignment_id%type;
549 l_candidate_person_id per_people_f.person_id%type;
550 begin
551 --
552 if ( funmode = 'RUN' ) then
553 --
554 l_candidate_assignment_id := wf_engine.GetItemAttrNumber
555 (itemtype => itemtype,
556 itemkey => itemkey,
557 aname => 'CANDIDATE_ASSIGNMENT_ID' );
558 --
559 l_candidate_person_id := wf_engine.GetItemAttrNumber
560 (itemtype => itemtype,
561 itemkey => itemkey,
562 aname => 'CANDIDATE_PERSON_ID' );
563 --
564 g_owa2 := null; -- 10/29/97 Changed
565 --
566 g_owa2 := hr_offer_custom.get_url_string;
567 --
568 -- 10/29/97 Change Begins
569 IF g_owa2 is null THEN
570 null;
571 ELSE
572 IF substr(g_owa2, length(g_owa2),1) = '/' THEN
573 -- the user has entered a trailing slash, we don't need to append
574 -- one.
575 null;
576 ELSE
577 g_owa2 := g_owa2 || '/';
578 END IF;
579 END IF;
580 --
581 g_url := g_owa2||'hr_offer_resume_web.setup?'||
582 hr_util_web.prepare_parameter
583 (p_name => 'p_effective_date'
584 ,p_value => hr_date.date_to_canonical(sysdate)
585 ,p_prefix => false)||
586 hr_util_web.prepare_parameter
587 (p_name => 'p_applicant_person_id'
588 ,p_value => l_candidate_person_id)||
589 hr_util_web.prepare_parameter
590 (p_name => 'p_assignment_id'
591 ,p_value => l_candidate_assignment_id)||
592 hr_util_web.prepare_parameter
593 (p_name => 'p_tab'
594 ,p_value => 2);
595 --
596 -- 10/29/97 Change Ends
597 wf_engine.SetItemAttrText( itemtype => itemtype,
598 itemkey => itemkey,
599 aname => 'SUMMARY_URL',
600 avalue => g_url );
601 --
602 elsif ( funmode = 'CANCEL' ) then
603 --
604 null;
605 --
606 end if;
607 --
608 end Get_Summary_URL;
609 --
610 -- ------------------------------------------------------------------------
611 -- |------------------------< Get_Offer_URL >------------------------------|
612 -- ------------------------------------------------------------------------
613 --
614 -- Description
615 --
616 -- This function will return the URL for the hiring manager to navigate to
617 -- the offer page.
618 --
619 --
620 procedure Get_Offer_URL( itemtype in varchar2,
621 itemkey in varchar2,
622 actid in number,
623 funmode in varchar2,
624 result out nocopy varchar2 ) is
625 --
626 l_candidate_assignment_id per_assignments_f.assignment_id%type;
627 l_candidate_person_id per_people_f.person_id%type;
628 l_host varchar2(200);
629 --
630 --
631 begin
632 --
633 if ( funmode = 'RUN' ) then
634 --
635 l_candidate_assignment_id := wf_engine.GetItemAttrNumber
636 (itemtype => itemtype,
637 itemkey => itemkey,
638 aname => 'CANDIDATE_ASSIGNMENT_ID' );
639 --
640 l_candidate_person_id := wf_engine.GetItemAttrNumber
641 (itemtype => itemtype,
642 itemkey => itemkey,
643 aname => 'CANDIDATE_PERSON_ID' );
644 --
645 g_owa2 := null; -- 10/29/97 Changed
646 --
647 g_owa2 := hr_offer_custom.get_url_string;
648 --
649 -- 10/29/97 Change Begins
650 IF g_owa2 is null THEN
651 null;
652 ELSE
653 IF substr(g_owa2, length(g_owa2),1) = '/' THEN
654 -- the user has entered a trailing slash, we don't need to append
655 -- one.
656 null;
657 ELSE
658 g_owa2 := g_owa2 || '/';
659 END IF;
660 END IF;
661 -- 10/29/97 Change Ends
662 --
663 g_url := g_owa2||'hr_offer_form_web.setup?'||
664 hr_util_web.prepare_parameter
665 (p_name => 'p_effective_date'
666 ,p_value => hr_date.date_to_chardate(SYSDATE)
667 ,p_prefix => false)||
668 hr_util_web.prepare_parameter
669 (p_name => 'p_applicant_person_id'
670 ,p_value => l_candidate_person_id)||
671 hr_util_web.prepare_parameter
672 (p_name => 'p_assignment_id'
673 ,p_value => l_candidate_assignment_id);
674 --
675 wf_engine.SetItemAttrText( itemtype => itemtype,
676 itemkey => itemkey,
677 aname => 'OFFER_URL',
678 avalue => g_url );
679 --
680 elsif ( funmode = 'CANCEL' ) then
681 --
682 null;
683 --
684 end if;
685 --
686 end Get_Offer_URL;
687 --
688 -- ------------------------------------------------------------------------
689 -- |------------------------< Get_Letter_URL >-----------------------------|
690 -- ------------------------------------------------------------------------
691 --
692 -- Description
693 --
694 -- This function will return the URL for the hiring manager to navigate to
695 -- the offer page. The letter form is one of the 'tab' forms whose setup
696 -- procedure is in hr_offer_resume_web.setup.
697 --
698 --
699 procedure Get_Letter_URL( itemtype in varchar2,
700 itemkey in varchar2,
701 actid in number,
702 funmode in varchar2,
703 result out nocopy varchar2 ) is
704 --
705 l_candidate_assignment_id per_assignments_f.assignment_id%type;
706 l_candidate_person_id per_people_f.person_id%type;
707 --
708 --
709 begin
710 --
711 if ( funmode = 'RUN' ) then
712 --
713 l_candidate_assignment_id := wf_engine.GetItemAttrNumber
714 (itemtype => itemtype,
715 itemkey => itemkey,
716 aname => 'CANDIDATE_ASSIGNMENT_ID' );
717 --
718 l_candidate_person_id := wf_engine.GetItemAttrNumber
719 (itemtype => itemtype,
720 itemkey => itemkey,
721 aname => 'CANDIDATE_PERSON_ID' );
722 --
723 g_owa2 := null;
724 --
725 g_owa2 := hr_offer_custom.get_url_string;
726 --
727 -- 10/29/97 Change Begins
728 IF g_owa2 is null THEN
729 null;
730 ELSE
731 IF substr(g_owa2, length(g_owa2),1) = '/' THEN
732 -- the user has entered a trailing slash, we don't need to append
733 -- one.
734 null;
735 ELSE
736 g_owa2 := g_owa2 || '/';
737 END IF;
738 END IF;
739
740 g_url := g_owa2||'hr_offer_resume_web.setup?'||
741 hr_util_web.prepare_parameter
742 (p_name => 'p_effective_date'
743 ,p_value => hr_date.date_to_canonical(sysdate)
744 ,p_prefix => false)||
745 hr_util_web.prepare_parameter
746 (p_name => 'p_applicant_person_id'
747 ,p_value => l_candidate_person_id)||
748 hr_util_web.prepare_parameter
749 (p_name => 'p_assignment_id'
750 ,p_value => l_candidate_assignment_id)||
751 hr_util_web.prepare_parameter
752 (p_name => 'p_tab'
753 ,p_value => 3);
754 --
755 -- 10/29/97 Change Ends
756 --
757 wf_engine.SetItemAttrText( itemtype => itemtype,
758 itemkey => itemkey,
759 aname => 'LETTER_URL',
760 avalue => g_url );
761 --
762 elsif ( funmode = 'CANCEL' ) then
763 --
764 null;
765 --
766 end if;
767 --
768 end Get_Letter_URL;
769 --
770 -- ------------------------------------------------------------------------
771 -- |------------------------< Get_HR_Routing_Details >---------------------|
772 -- ------------------------------------------------------------------------
773 --
774 -- Description
775 --
776 -- This function sets the valuse of 3 HR roles. These roles can be used
777 -- as performers of notification activities.
778 --
779 procedure Get_HR_Routing_Details( itemtype in varchar2,
780 itemkey in varchar2,
781 actid in number,
782 funmode in varchar2,
783 result out nocopy varchar2 ) is
784
785 l_hiring_mgr_id per_people_f.person_id%type;
786 l_hr_routing_id per_people_f.person_id%type;
787 l_hr_routing_username wf_users.name%type;
788 l_hr_routing_disp_name wf_users.display_name%type;
789 --
790 begin
791 --
792 if ( funmode = 'RUN' ) then
793 --
794 l_hiring_mgr_id := wf_engine.GetItemAttrNumber
795 (itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'HIRING_MGR_ID' );
798 --
799 -- set the routing id 1
800 --
801 l_hr_routing_id := hr_offer_custom.get_hr_routing1
802 (p_person_id => l_hiring_mgr_id );
803 --
804 wf_directory.GetUserName( p_orig_system => 'PER',
805 p_orig_system_id => l_hr_routing_id,
806 p_name => l_hr_routing_username,
807 p_display_name => l_hr_routing_disp_name );
808 --
809 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
810 itemkey => itemkey,
811 aname => 'HR_ROUTING_ID1',
812 avalue => l_hr_routing_id ) ;
813
814
815 wf_engine.SetItemAttrText ( itemtype => itemtype,
816 itemkey => itemkey,
817 aname => 'HR_ROUTING_USERNAME1',
818 avalue => l_hr_routing_username );
819 --
820 wf_engine.SetItemAttrText ( itemtype => itemtype,
821 itemkey => itemkey,
822 aname => 'HR_ROUTING_DISP_NAME1',
823 avalue => l_hr_routing_disp_name );
824 --
825 -- set the routing id 2
826 --
827 l_hr_routing_id := hr_offer_custom.get_hr_routing2
828 (p_person_id => l_hiring_mgr_id );
829
830
831 wf_directory.GetUserName( p_orig_system => 'PER',
832 p_orig_system_id => l_hr_routing_id,
833 p_name => l_hr_routing_username,
834 p_display_name => l_hr_routing_disp_name );
835 --
836 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
837 itemkey => itemkey,
838 aname => 'HR_ROUTING_ID2',
839 avalue => l_hr_routing_id ) ;
840
841 wf_engine.SetItemAttrText ( itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'HR_ROUTING_USERNAME2',
844 avalue => l_hr_routing_username );
845 --
846 wf_engine.SetItemAttrText ( itemtype => itemtype,
847 itemkey => itemkey,
848 aname => 'HR_ROUTING_DISP_NAME2',
849 avalue => l_hr_routing_disp_name );
850
851 --
852 -- set the routing id 3
853 --
854 l_hr_routing_id := hr_offer_custom.get_hr_routing3
855 (p_person_id => l_hiring_mgr_id );
856
857 wf_directory.GetUserName( p_orig_system => 'PER',
858 p_orig_system_id => l_hr_routing_id,
859 p_name => l_hr_routing_username,
860 p_display_name => l_hr_routing_disp_name );
861 --
862 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
863 itemkey => itemkey,
864 aname => 'HR_ROUTING_ID3',
865 avalue => l_hr_routing_id ) ;
866
867
868 wf_engine.SetItemAttrText ( itemtype => itemtype,
869 itemkey => itemkey,
870 aname => 'HR_ROUTING_USERNAME3',
871 avalue => l_hr_routing_username );
872 --
873 wf_engine.SetItemAttrText ( itemtype => itemtype,
874 itemkey => itemkey,
875 aname => 'HR_ROUTING_DISP_NAME3',
876 avalue => l_hr_routing_disp_name );
877 --
878 elsif ( funmode = 'CANCEL' ) then
879 --
880 null;
881 --
882 end if;
883 --
884 end Get_HR_Routing_Details;
885 --
886 --
887 -- ------------------------------------------------------------------------
888 -- |------------------------< copy_approval_comment >---------------------|
889 -- ------------------------------------------------------------------------
890 procedure copy_approval_comment( itemtype in varchar2,
891 itemkey in varchar2,
892 actid in number,
893 funmode in varchar2,
894 result out nocopy varchar2 ) is
895 begin
896 --
897 if ( funmode = 'RUN' ) then
898 --
899 wf_engine.SetItemAttrText ( itemtype => itemtype,
900 itemkey => itemkey,
901 aname => 'APPROVAL_COMMENT_COPY',
902 avalue => wf_engine.GetItemAttrText( itemtype => itemtype,
903 itemkey => itemkey,
904 aname => 'APPROVAL_COMMENT')
905 );
906 --
907 elsif ( funmode = 'CANCEL' ) then
908 --
909 null;
910 --
911 end if;
912 --
913 end copy_approval_comment;
914 --
915 --
916 -- ------------------------------------------------------------------------
917 -- |------------------------< Get_HR_Candidate_Details >---------------------|
918 -- ------------------------------------------------------------------------
919 --
920 -- Description
921 --
922 -- This function sets the values of the some of candidate offer's terms
923 -- and condidtions.
924 --
925 procedure Get_HR_Candidate_Details( itemtype in varchar2,
926 itemkey in varchar2,
927 actid in number,
928 funmode in varchar2,
929 result out nocopy varchar2 ) is
930 -- Salary amount
931 CURSOR csr_salary_amount
932 (p_assignment_id in per_assignments_f.assignment_id%TYPE) is
933 select answer_value
934 from per_assign_proposal_answers
935 where assignment_id = p_assignment_id
936 and proposal_question_name = 'ANNUAL_SALARY';
937
938 -- Position title
939 CURSOR csr_position_title
940 (p_assignment_id in per_assignments_f.assignment_id%TYPE) is
941 select answer_value
942 from per_assign_proposal_answers
943 where assignment_id = p_assignment_id
944 and proposal_question_name = 'POSITION_TITLE';
945
946 -- Cost center/ Organization
947 CURSOR csr_organization
948 (p_assignment_id in per_assignments_f.assignment_id%TYPE) is
949 select hou.name
950 from per_assignments_x pax,
951 hr_all_organization_units hou -- 09/26/97 Changed
952 where pax.assignment_id = p_assignment_id
953 and pax.organization_id = hou.organization_id;
954
955 -- Sign-on bonus amount
956 CURSOR csr_sign_on_bonus_amount
957 (p_assignment_id in per_assignments_f.assignment_id%TYPE) is
958 select answer_value
959 from per_assign_proposal_answers
960 where assignment_id = p_assignment_id
961 and proposal_question_name = 'SIGN_ON_BONUS_AMOUNT';
962
963 -- Relocation maximum amount reimbursed
964 CURSOR csr_relocation_amount
965 (p_assignment_id in per_assignments_f.assignment_id%TYPE) is
966 select answer_value
967 from per_assign_proposal_answers
968 where assignment_id = p_assignment_id
969 and proposal_question_name = 'RELOCATION_MAXIMUM_AMOUNT_REIMBURSED';
970
971 -- Stock option shares
972 CURSOR csr_stock_option_shares
973 (p_assignment_id in per_assignments_f.assignment_id%TYPE) is
974 select answer_value
975 from per_assign_proposal_answers
976 where assignment_id = p_assignment_id
977 and proposal_question_name = 'STOCK_OPTION_SHARES';
978
979 position_title per_assign_proposal_answers.answer_value%TYPE;
980 salary_amount per_assign_proposal_answers.answer_value%TYPE;
981 relocation_amount per_assign_proposal_answers.answer_value%TYPE;
982 sign_on_bonus_amount per_assign_proposal_answers.answer_value%TYPE;
983 stock_option_shares per_assign_proposal_answers.answer_value%TYPE;
984 p_assignment_id per_assignments_f.assignment_id%TYPE := to_number(itemkey);
985 organization hr_organization_units.name%TYPE;
986
987 -- 05/08/97 Change Begins
988 --
989 l_candidate_person_id per_people_f.person_id%type;
990 l_candidate_disp_name wf_users.display_name%type;
991 l_candidate_appl_number per_people_f.applicant_number%type;
992 --
993 -- 05/08/97 Change Ends
994 --
995 begin
996 OPEN csr_position_title(p_assignment_id);
997 FETCH csr_position_title into position_title;
998 CLOSE csr_position_title;
999
1000 OPEN csr_organization(p_assignment_id);
1001 FETCH csr_organization into organization;
1002 CLOSE csr_organization;
1003
1004
1005 OPEN csr_salary_amount(p_assignment_id);
1006 FETCH csr_salary_amount into salary_amount;
1007 CLOSE csr_salary_amount;
1008
1009 OPEN csr_sign_on_bonus_amount(p_assignment_id);
1010 FETCH csr_sign_on_bonus_amount into sign_on_bonus_amount;
1011 CLOSE csr_sign_on_bonus_amount;
1012
1013 OPEN csr_relocation_amount(p_assignment_id);
1014 FETCH csr_relocation_amount into
1015 relocation_amount;
1016 CLOSE csr_relocation_amount;
1017
1018 OPEN csr_stock_option_shares(p_assignment_id);
1019 FETCH csr_stock_option_shares into stock_option_shares;
1020 CLOSE csr_stock_option_shares;
1021
1022
1023 --
1024 if ( funmode = 'RUN' ) then
1025 --
1026 --
1027 wf_engine.SetItemAttrText ( itemtype => itemtype,
1028 itemkey => itemkey,
1029 aname => 'SALARY_AMOUNT',
1030 avalue => salary_amount ) ;
1031
1032 wf_engine.SetItemAttrText ( itemtype => itemtype,
1033 itemkey => itemkey,
1034 aname => 'POSITION_TITLE',
1035 avalue => position_title);
1036
1037 --
1038 wf_engine.SetItemAttrText ( itemtype => itemtype,
1039 itemkey => itemkey,
1040 aname => 'ORGANIZATION',
1041 avalue => organization);
1042
1043 wf_engine.SetItemAttrText ( itemtype => itemtype,
1044 itemkey => itemkey,
1045 aname => 'BONUS_AMOUNT',
1046 avalue => sign_on_bonus_amount)
1047 ;
1048 --
1049 wf_engine.SetItemAttrText ( itemtype => itemtype,
1050 itemkey => itemkey,
1051 aname => 'STOCK_OPTION',
1052 avalue => stock_option_shares);
1053
1054 --
1055 wf_engine.SetItemAttrText ( itemtype => itemtype,
1056 itemkey => itemkey,
1057 aname => 'RELOCATION',
1058 avalue => relocation_amount);
1059
1060 --
1061 -- 05/08/97 Change Begins
1062 hr_offer_custom.get_candidate_details
1063 (p_candidate_assignment_id => p_assignment_id,
1064 p_candidate_person_id => l_candidate_person_id,
1065 p_candidate_disp_name => l_candidate_disp_name,
1066 p_applicant_number => l_candidate_appl_number);
1067 --
1068 --
1069 wf_engine.SetItemAttrText ( itemtype => itemtype,
1070 itemkey => itemkey,
1071 aname => 'CANDIDATE_DISP_NAME'
1072 ,
1073 avalue => l_candidate_disp_name
1074 );
1075 --
1076 -- 05/08/97 Change Ends
1077 --
1078 elsif ( funmode = 'CANCEL' ) then
1079 --
1080 null;
1081 --
1082 end if;
1083 --
1084 exception
1085 when others then
1086 null;
1087
1088 end Get_HR_Candidate_Details;
1089
1090 --
1091
1092 -- ------------------------------------------------------------------------
1093 -- ------------------------------------------------------------------------
1094 -- |------------------------< check_if_bypass >---------------------|
1095 -- ------------------------------------------------------------------------
1096 procedure check_if_bypass (itemtype in varchar2,
1097 itemkey in varchar2,
1098 actid in number,
1099 funmode in varchar2,
1100 result out nocopy varchar2 ) is
1101 bypass varchar2(10);
1102
1103 begin
1104 --
1105 if ( funmode = 'RUN' ) then
1106 --
1107 result := wf_engine.GetItemAttrText ( itemtype => itemtype,
1108 itemkey => itemkey,
1109 aname => 'BYPASS');
1110 --
1111 elsif ( funmode = 'CANCEL' ) then
1112 --
1113 null;
1114 --
1115 end if;
1116 --
1117 end check_if_bypass;
1118
1119 --
1120 --
1121 -- ------------------------------------------------------------------------
1122 -- |----------------------< Set_Bypass_To_No >---------------------------|
1123 -- ------------------------------------------------------------------------
1124 procedure Set_Bypass_To_No ( itemtype in varchar2,
1125 itemkey in varchar2,
1126 actid in number,
1127 funmode in varchar2,
1128 result out nocopy varchar2 ) is
1129 begin
1130 --
1131 if ( funmode = 'RUN' ) then
1132 --
1133 wf_engine.SetItemAttrText ( itemtype => itemtype,
1134 itemkey => itemkey,
1135 aname => 'BYPASS',
1136 avalue => 'N');
1137 --
1138 wf_engine.SetItemAttrText ( itemtype => itemtype,
1139 itemkey => itemkey,
1140 aname => 'APPROVAL_COMMENT_COPY',
1141 avalue => '');
1142 --
1143 elsif ( funmode = 'CANCEL' ) then
1144 --
1145 null;
1146 --
1147 end if;
1148 --
1149 end Set_Bypass_To_No;
1150 --
1151 --
1152 end hr_offer_wf;