[Home] [Help]
PACKAGE BODY: APPS.HR_APPROVAL_WF
Source
1 package body hr_approval_wf as
2 /* $Header: hrapprwf.pkb 120.0 2005/05/30 22:44:37 appldev noship $ */
3 -- ---------------------------------------------------------------------------
4 -- private package global declarations
5 -- ---------------------------------------------------------------------------
6 g_package constant varchar2(31) := 'hr_approval_wf.';
7
8 --
9 -- returns the supervisor id for the person id passed into the cursor.
10 cursor g_csr_pa(l_effective_date in date
11 ,l_in_person_id in per_people_f.person_id%type) is
12 select ppf.person_id
13 from per_assignments_f paf
14 ,per_people_f ppf
15 where paf.person_id = l_in_person_id
16 and paf.primary_flag = 'Y'
17 and (paf.assignment_type = 'E' and ppf.current_employee_flag = 'Y'
18 or paf.assignment_type = 'C' and ppf.current_npw_flag = 'Y')
19 and sysdate
20 between paf.effective_start_date
21 and paf.effective_end_date
22 and ppf.person_id = paf.supervisor_id
23 and sysdate
24 between ppf.effective_start_date
25 and ppf.effective_end_date;
26
27 -- returns a email address for the person id passed into the cursor.
28 CURSOR g_csr_email_address(l_effective_date in date
29 ,p_person_id in per_people_f.person_id%type) IS
30 SELECT email_address
31 FROM per_people_f pp
32 WHERE pp.person_id = p_person_id
33 AND l_effective_date
34 between pp.effective_start_date
35 and pp.effective_end_date;
36 --
37 -- returns vacancy info
38 CURSOR g_csr_vacancies(p_vacancy_id in number) IS
39 SELECT recruiter_id,job_id
40 FROM per_vacancies pv
41 WHERE pv.vacancy_id = p_vacancy_id;
42
43 --
44 g_vacancies g_csr_vacancies%rowtype;
45 --
46 --
47 -- ---------------------------------------------------------------------------
48 -- private procedure declarations
49 -- ---------------------------------------------------------------------------
50 --
51 -- ----------------------------------------------------------------------------
52 -- |------------------------------< set_custom_wf_globals >-------------------|
53 -- ----------------------------------------------------------------------------
54 --
55 -- This procedure sets the customized global variables with the standard wf
56 -- values
57 --
58 procedure set_custom_wf_globals
59 (p_itemtype in varchar2
60 ,p_itemkey in varchar2) is
61 begin
62 hr_approval_custom.g_itemtype := p_itemtype;
63 hr_approval_custom.g_itemkey := p_itemkey;
64 end set_custom_wf_globals;
65 --
66 -- --------------------------------------------------------------------
67 -- |------------------------< Set_Routing_Details >--------------------|
68 -- --------------------------------------------------------------------
69 --
70 -- Description
71 --
72 -- This function sets the attributes
73 -- APPROVAL_ROUTING_PERSON_ID<p_router_index>
74 -- APPROVAL_ROUTING_USERNAME<p_router_index>
75 -- APPROVAL_ROUTING_DISPLAY_NAME<p_router_index>
76 -- for a given p_routerr_index
77 --
78 procedure Set_Routing_Details
79 (p_item_type in varchar2
80 ,p_item_key in varchar2
81 ,p_approval_routing_id in per_people_f.person_id%type
82 ,p_router_index in number) is
83 --
84 l_approval_routing_username wf_users.name%type;
85 l_approval_routing_disp_name wf_users.display_name%type;
86 --
87 begin
88 --
89 wf_directory.GetUserName(p_orig_system => 'PER'
90 ,p_orig_system_id => p_approval_routing_id
91 ,p_name => l_approval_routing_username
92 ,p_display_name => l_approval_routing_disp_name);
93 --
94 wf_engine.SetItemAttrNumber (itemtype => p_item_type,
95 itemkey => p_item_key,
96 aname => 'APPROVAL_ROUTING_PERSON_ID'||p_router_index,
97 avalue => p_approval_routing_id ) ;
98
99
100 wf_engine.SetItemAttrText (itemtype => p_item_type,
101 itemkey => p_item_key,
102 aname => 'APPROVAL_ROUTING_USERNAME'||p_router_index,
103 avalue => l_approval_routing_username );
104 --
105 wf_engine.SetItemAttrText (itemtype => p_item_type,
106 itemkey => p_item_key,
107 aname => 'APPROVAL_ROUTING_DISPLAY_NAME'
108 ||p_router_index,
109 avalue => l_approval_routing_disp_name );
110 --
111 --
112 end Set_Routing_Details;
113 -- ------------------------------------------------------------------------
114 -- |------------------------------< Set_URL >-----------------------------|
115 -- ------------------------------------------------------------------------
116 --
117 -- Description
118 --
119 -- This procedure will set the item attribute
120 -- APPROVAL_URL<p_url_index> for a given p_url_index
121 -- with a given p_url
122 --
123 --
124 procedure Set_URL
125 (p_item_type in varchar2
126 ,p_item_key in varchar2
127 ,p_url in varchar2
128 ,p_url_index in number) is
129 --
130
131 begin
132 --
133 --
134 -- Set item attributes for the URL
135 --
136 wf_engine.SetItemAttrText(itemtype => p_item_type,
137 itemkey => p_item_key,
138 aname => 'APPROVAL_URL'||p_url_index,
139 avalue => p_url);
140 --
141 end Set_URL;
142 --
143 -- --------------------------------------------------------------------
144 -- |--------------------< create_item_attrib_if_notexist >---------|
145 -- --------------------------------------------------------------------
146 --
147 -- Description
148 --
149 -- This procedure checks to see if an item attribute exists. If it does
150 -- not the one is created
151 --
152 procedure create_item_attrib_if_notexist
153 (p_item_type in varchar2
154 ,p_item_key in varchar2
155 ,p_name in varchar2) is
156 --
157 l_dummy number(1);
158 -- cursor determines if an attribute exists
159 cursor csr_wiav is
160 select 1
161 from wf_item_attribute_values wiav
162 where wiav.item_type = p_item_type
163 and wiav.item_key = p_item_key
164 and wiav.name = p_name;
165 --
166 begin
167 -- open the cursor to determine if the a
168 open csr_wiav;
169 fetch csr_wiav into l_dummy;
170 if csr_wiav%notfound then
171 --
172 -- item attribute does not exist so create it
173 wf_engine.additemattr
174 (itemtype => p_item_type
175 ,itemkey => p_item_key
176 ,aname => p_name);
177 end if;
178 close csr_wiav;
179 --
180 end create_item_attrib_if_notexist;
181 --
182 --
183 -- ---------------------------------------------------------------------------
184 -- public procedure declarations
185 -- ---------------------------------------------------------------------------
186 --
187 --
188 -- ---------------------------------------------------------------------------
189 -- |-------------------------< intialize_item_attributes >--------------------|
190 -- ---------------------------------------------------------------------------
191 procedure initialize_item_attributes
192 (itemtype in varchar2
193 ,itemkey in varchar2
194 ,actid in number
195 ,funmode in varchar2
196 ,result out nocopy varchar2) is
197 -- -------------------------------------------------------------------------
198 -- local variables
199 -- -------------------------------------------------------------------------
200 l_proc_name varchar2(61) := g_package||'initialise_item_attributes';
201 l_person_id per_people_f.person_id%type;
202 l_creator_person_id per_people_f.person_id%type;
203 l_creator_username wf_users.name%type;
204 l_creator_disp_name wf_users.display_name%type;
205 l_candidate_assignment_id per_assignments_f.assignment_id%type;
206 l_candidate_person_id per_people_f.person_id%type;
207 l_candidate_disp_name wf_users.display_name%type;
208 l_candidate_appl_number per_people_f.applicant_number%type;
209 l_fwd_from_username wf_users.name%type;
210 l_fwd_from_disp_name wf_users.display_name%type;
211 l_url_index number default 1;
212 l_max_urls number default 20;
213 --
214 begin
215 -- check the workflow funmode value
216 if funmode = 'RUN' then
217 -- workflow is RUNing this procedure
218 --
219 --
220 -- Test that all attributes exist and if they don't create them
221 --
222 -- APPROVAL_COMMENT
223 create_item_attrib_if_notexist
224 (p_item_type => itemtype
225 ,p_item_key => itemkey
226 ,p_name => 'APPROVAL_COMMENT');
227 --
228 -- Comment by vtakru for MEE process as approval comments set
229 -- during the mee processes were being reset
230 -- wf_engine.SetItemAttrText
231 -- ( itemtype => itemtype,
232 -- itemkey => itemkey,
233 -- aname => 'APPROVAL_COMMENT',
234 -- avalue => '' );
235 --
236 -- APPROVAL_COMMENT_COPY
237 create_item_attrib_if_notexist
238 (p_item_type => itemtype
239 ,p_item_key => itemkey
240 ,p_name => 'APPROVAL_COMMENT_COPY');
241 --
242 --wf_engine.SetItemAttrText
243 -- ( itemtype => itemtype,
244 -- itemkey => itemkey,
245 -- aname => 'APPROVAL_COMMENT_COPY',
246 -- avalue => '' );
247 --
248 -- FORWARD_FROM_USERNAME
249 create_item_attrib_if_notexist
250 (p_item_type => itemtype
251 ,p_item_key => itemkey
252 ,p_name => 'FORWARD_FROM_USERNAME');
253 --
254 -- FORWARD_FROM_PERSON_ID
255 create_item_attrib_if_notexist
256 (p_item_type => itemtype
257 ,p_item_key => itemkey
258 ,p_name => 'FORWARD_FROM_PERSON_ID');
259 --
260 -- FORWARD_FROM_DISPLAY_NAME
261 create_item_attrib_if_notexist
262 (p_item_type => itemtype
263 ,p_item_key => itemkey
264 ,p_name => 'FORWARD_FROM_DISPLAY_NAME');
265 --
266 -- FORWARD_TO_USERNAME
267 create_item_attrib_if_notexist
268 (p_item_type => itemtype
269 ,p_item_key => itemkey
270 ,p_name => 'FORWARD_TO_USERNAME');
271 --
272 -- FORWARD_TO_PERSON_ID
273 create_item_attrib_if_notexist
274 (p_item_type => itemtype
275 ,p_item_key => itemkey
276 ,p_name => 'FORWARD_TO_PERSON_ID');
277 --
278 -- FORWARD_TO_DISPLAY_NAME
279 create_item_attrib_if_notexist
280 (p_item_type => itemtype
281 ,p_item_key => itemkey
282 ,p_name => 'FORWARD_TO_DISPLAY_NAME');
283 --
284 -- APPROVAL_CREATOR_USERNAME
285 create_item_attrib_if_notexist
286 (p_item_type => itemtype
287 ,p_item_key => itemkey
288 ,p_name => 'APPROVAL_CREATOR_USERNAME');
289 --
290 -- APPROVAL_CREATOR_PERSON_ID
291 create_item_attrib_if_notexist
292 (p_item_type => itemtype
293 ,p_item_key => itemkey
294 ,p_name => 'APPROVAL_CREATOR_PERSON_ID');
295 --
296 -- APPROVAL_CREATOR_DISPLAY_NAME
297 create_item_attrib_if_notexist
298 (p_item_type => itemtype
299 ,p_item_key => itemkey
300 ,p_name => 'APPROVAL_CREATOR_DISPLAY_NAME');
301 --
302 -- APPROVAL_ROUTING_USERNAME1
303 create_item_attrib_if_notexist
304 (p_item_type => itemtype
305 ,p_item_key => itemkey
306 ,p_name => 'APPROVAL_ROUTING_USERNAME1');
307 --
308 -- APPROVAL_ROUTING_PERSON_ID1
309 create_item_attrib_if_notexist
310 (p_item_type => itemtype
311 ,p_item_key => itemkey
312 ,p_name => 'APPROVAL_ROUTING_PERSON_ID1');
313 --
314 -- APPROVAL_ROUTING_DISPLAY_NAME1
315 create_item_attrib_if_notexist
316 (p_item_type => itemtype
317 ,p_item_key => itemkey
318 ,p_name => 'APPROVAL_ROUTING_DISPLAY_NAME1');
319 --
320 -- APPROVAL_ROUTING_USERNAME2
321 create_item_attrib_if_notexist
322 (p_item_type => itemtype
323 ,p_item_key => itemkey
324 ,p_name => 'APPROVAL_ROUTING_USERNAME2');
325 --
326 -- APPROVAL_ROUTING_PERSON_ID2
327 create_item_attrib_if_notexist
328 (p_item_type => itemtype
329 ,p_item_key => itemkey
330 ,p_name => 'APPROVAL_ROUTING_PERSON_ID2');
331 --
332 -- APPROVAL_ROUTING_DISPLAY_NAME2
333 create_item_attrib_if_notexist
334 (p_item_type => itemtype
335 ,p_item_key => itemkey
336 ,p_name => 'APPROVAL_ROUTING_DISPLAY_NAME2');
337 --
338 -- APPROVAL_ROUTING_USERNAME3
339 create_item_attrib_if_notexist
340 (p_item_type => itemtype
341 ,p_item_key => itemkey
342 ,p_name => 'APPROVAL_ROUTING_USERNAME3');
343 --
344 -- APPROVAL_ROUTING_PERSON_ID3
345 create_item_attrib_if_notexist
346 (p_item_type => itemtype
347 ,p_item_key => itemkey
348 ,p_name => 'APPROVAL_ROUTING_PERSON_ID3');
349 --
350 -- APPROVAL_ROUTING_DISPLAY_NAME3
351 create_item_attrib_if_notexist
352 (p_item_type => itemtype
353 ,p_item_key => itemkey
354 ,p_name => 'APPROVAL_ROUTING_DISPLAY_NAME3');
355 --
356 -- APPROVAL_ROUTING_USERNAME4
357 create_item_attrib_if_notexist
358 (p_item_type => itemtype
359 ,p_item_key => itemkey
360 ,p_name => 'APPROVAL_ROUTING_USERNAME4');
361 --
362 -- APPROVAL_ROUTING_PERSON_ID4
363 create_item_attrib_if_notexist
364 (p_item_type => itemtype
365 ,p_item_key => itemkey
366 ,p_name => 'APPROVAL_ROUTING_PERSON_ID4');
367 --
368 -- APPROVAL_ROUTING_DISPLAY_NAME4
369 create_item_attrib_if_notexist
370 (p_item_type => itemtype
371 ,p_item_key => itemkey
372 ,p_name => 'APPROVAL_ROUTING_DISPLAY_NAME4');
373 --
374 -- APPROVAL_ROUTING_USERNAME5
375 create_item_attrib_if_notexist
376 (p_item_type => itemtype
377 ,p_item_key => itemkey
378 ,p_name => 'APPROVAL_ROUTING_USERNAME5');
379 --
380 -- APPROVAL_ROUTING_PERSON_ID5
381 create_item_attrib_if_notexist
382 (p_item_type => itemtype
383 ,p_item_key => itemkey
384 ,p_name => 'APPROVAL_ROUTING_PERSON_ID5');
385 --
386 -- APPROVAL_ROUTING_DISPLAY_NAME5
387 create_item_attrib_if_notexist
388 (p_item_type => itemtype
389 ,p_item_key => itemkey
390 ,p_name => 'APPROVAL_ROUTING_DISPLAY_NAME5');
391 --
392 -- APPROVAL_URL1 to APPROVAL_URL20
393 loop
394 exit when l_url_index > l_max_urls;
395 create_item_attrib_if_notexist
396 (p_item_type => itemtype
397 ,p_item_key => itemkey
398 ,p_name => 'APPROVAL_URL'||to_char(l_url_index));
399 l_url_index := l_url_index + 1;
400 end loop;
401 --
402
403 -- Set the creator, forward to and forward from attributes
404 --
405 l_creator_person_id := wf_engine.GetItemAttrNumber
406 (itemtype => itemtype,
407 itemkey => itemkey,
408 aname => 'CREATOR_PERSON_ID' );
409 --
410 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
411 itemkey => itemkey,
412 aname => 'APPROVAL_CREATOR_PERSON_ID',
413 avalue => l_creator_person_id );
414 --
415 wf_directory.GetUserName( p_orig_system => 'PER',
416 p_orig_system_id => l_creator_person_id,
417 p_name => l_creator_username,
418 p_display_name => l_creator_disp_name );
419 --
420 wf_engine.SetItemAttrText ( itemtype => itemtype,
421 itemkey => itemkey,
422 aname => 'APPROVAL_CREATOR_USERNAME',
423 avalue => l_creator_username );
424 --
425 wf_engine.SetItemAttrText ( itemtype => itemtype,
426 itemkey => itemkey,
427 aname => 'APPROVAL_CREATOR_DISPLAY_NAME',
428 avalue => l_creator_disp_name );
429 --
430 -- Set forward to = creator in case this is the only person in
431 -- approval chain.
432 --
433 --
434 /*
435
436 wf_engine.SetItemAttrText ( itemtype => itemtype,
437 itemkey => itemkey,
438 aname => 'FORWARD_TO_USERNAME',
439 avalue => l_creator_username );
440 --
441 wf_engine.SetItemAttrText ( itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'FORWARD_TO_DISPLAY_NAME',
444 avalue => l_creator_disp_name );
445 --
446 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'FORWARD_TO_PERSON_ID',
449 avalue => l_creator_person_id) ;
450 */
451 -- fix for bug#2677648
452 -- FORWARD_TO_DISPLAY_NAME
453 -- set the attribute value to null
454 wf_engine.SetItemAttrText(itemtype => itemtype ,
455 itemkey => itemkey,
456 aname => 'FORWARD_TO_DISPLAY_NAME',
457 avalue=>null);
458 -- FORWARD_TO_USERNAME
459 -- set the attribute value to null
460 wf_engine.SetItemAttrText(itemtype => itemtype ,
461 itemkey => itemkey,
462 aname => 'FORWARD_TO_USERNAME',
463 avalue=>null);
464 -- FORWARD_TO_PERSON_ID
465 -- set the attribute value to null
466 wf_engine.SetItemAttrNumber(itemtype => itemtype ,
467 itemkey => itemkey,
468 aname => 'FORWARD_TO_PERSON_ID',
469 avalue=>null);
470
471 --
472 -- Set forward from = creator. If this creator has no
473 -- supervisor, we won't ever call get_next_approver, so we have to
474 -- set all the variables for 'forward from'.
475 --
476 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
477 itemkey => itemkey,
478 aname => 'FORWARD_FROM_PERSON_ID',
479 avalue => l_creator_person_id) ;
480 --
481 wf_engine.SetItemAttrText( itemtype => itemtype,
482 itemkey => itemkey,
483 aname => 'FORWARD_FROM_USERNAME',
484 avalue => l_creator_username);
485 --
486 wf_engine.SetItemAttrText( itemtype => itemtype,
487 itemkey => itemkey,
488 aname => 'FORWARD_FROM_DISPLAY_NAME',
489 avalue => l_creator_disp_name );
490
491 -- commented rajayara
492 -- Set the APPROVAL_COMMENT_COPY and APPROVAL_COMMENT attribute
493 -- to NULL
494 --
495 -- wf_engine.SetItemAttrText ( itemtype => itemtype,
496 -- itemkey => itemkey,
497 -- aname => 'APPROVAL_COMMENT_COPY',
498 -- avalue => NULL);
499 --
500 wf_engine.SetItemAttrText ( itemtype => itemtype,
501 itemkey => itemkey,
502 aname => 'APPROVAL_COMMENT',
503 avalue => NULL);
504 --
505 -- -----------------------------------------------------------------------
506 -- set workflow activity to the SUCCESS state to end workflow
507 -- -----------------------------------------------------------------------
508 result := 'COMPLETE:SUCCESS';
509 --
510 elsif funmode = 'CANCEL' then
511 -- workflow is calling in cancel mode (performing a loop reset) so ignore
512 null;
513 end if;
514 end initialize_item_attributes;
515 -- --------------------------------------------------------------------
516 -- |------------------------< set_routing_details1 >------------------|
517 -- --------------------------------------------------------------------
518 procedure set_routing_details1(itemtype in varchar2,
519 itemkey in varchar2,
520 actid in number,
521 funmode in varchar2,
522 result out nocopy varchar2 ) is
523 -- -------------------------------------------------------------------------
524 -- local variables
525 -- -------------------------------------------------------------------------
526 l_proc_name varchar2(61) := g_package||'set_routing_details1';
527 l_creator_person_id per_people_f.person_id%type;
528 l_approval_routing_id per_people_f.person_id%type;
529 --
530 begin
531 --
532 if ( funmode = 'RUN' ) then
533 --
534 l_creator_person_id :=
535 wf_engine.GetItemAttrNumber(itemtype => itemtype,
536 itemkey => itemkey,
537 aname => 'CREATOR_PERSON_ID' );
538 -- -----------------------------------------------------------------------
539 -- expose the wf control variables to the custom package
540 -- -----------------------------------------------------------------------
541 set_custom_wf_globals
542 (p_itemtype => itemtype
543 ,p_itemkey => itemkey);
544 --
545 -- get the routing id
546 --
547 l_approval_routing_id := hr_approval_custom.get_routing_details1
548 (p_person_id => l_creator_person_id);
549 --
550 -- Set the routing details
551 set_routing_details
552 (p_item_type => itemtype
553 ,p_item_key => itemkey
554 ,p_approval_routing_id => l_approval_routing_id
555 ,p_router_index => 1);
556 -- -----------------------------------------------------------------------
557 -- set workflow activity to the SUCCESS state to end workflow
558 -- -----------------------------------------------------------------------
559 result := 'COMPLETE:SUCCESS';
560 elsif ( funmode = 'CANCEL' ) then
561 --
562 null;
563 --
564 --
565 end if;
566 --
567 end set_routing_details1;
568 --
569 -- --------------------------------------------------------------------
570 -- |------------------------< set_routing_details2 >------------------|
571 -- --------------------------------------------------------------------
572 procedure set_routing_details2(itemtype in varchar2,
573 itemkey in varchar2,
574 actid in number,
575 funmode in varchar2,
576 result out nocopy varchar2 ) is
577 -- -------------------------------------------------------------------------
578 -- local variables
579 -- -------------------------------------------------------------------------
580 l_proc_name varchar2(61) := g_package||'set_routing_details2';
581 l_creator_person_id per_people_f.person_id%type;
582 l_approval_routing_id per_people_f.person_id%type;
583 --
584 begin
585 --
586 if ( funmode = 'RUN' ) then
587 --
588 l_creator_person_id :=
589 wf_engine.GetItemAttrNumber(itemtype => itemtype,
590 itemkey => itemkey,
591 aname => 'CREATOR_PERSON_ID' );
592 -- -----------------------------------------------------------------------
593 -- expose the wf control variables to the custom package
594 -- -----------------------------------------------------------------------
595 set_custom_wf_globals
596 (p_itemtype => itemtype
597 ,p_itemkey => itemkey);
598 --
599 -- get the routing id
600 --
601 l_approval_routing_id := hr_approval_custom.get_routing_details2
602 (p_person_id => l_creator_person_id);
603 --
604 -- Set the routing details
605 set_routing_details
606 (p_item_type => itemtype
607 ,p_item_key => itemkey
608 ,p_approval_routing_id => l_approval_routing_id
609 ,p_router_index => 2);
610 -- -----------------------------------------------------------------------
611 -- set workflow activity to the SUCCESS state to end workflow
612 -- -----------------------------------------------------------------------
613 result := 'COMPLETE:SUCCESS';
614 elsif ( funmode = 'CANCEL' ) then
615 --
616 null;
617 --
618 --
619 end if;
620 --
621 end set_routing_details2;
622 --
623 -- --------------------------------------------------------------------
624 -- |------------------------< set_routing_details3 >------------------|
625 -- Recruiter - used by Apply for a Job
626 -- --------------------------------------------------------------------
627 procedure set_routing_details3(itemtype in varchar2,
628 itemkey in varchar2,
629 actid in number,
630 funmode in varchar2,
631 result out nocopy varchar2 ) is
632 -- -------------------------------------------------------------------------
633 -- local variables
634 -- -------------------------------------------------------------------------
635 l_proc_name varchar2(61) := g_package||'set_routing_details3';
636 l_creator_person_id per_people_f.person_id%type;
637 l_approval_routing_id per_people_f.person_id%type;
638 l_vacancy_id per_vacancies.vacancy_id%type;
639 --
640 begin
641 --
642 if ( funmode = 'RUN' ) then
643 --
644 l_creator_person_id :=
645 wf_engine.GetItemAttrNumber(itemtype => itemtype,
646 itemkey => itemkey,
647 aname => 'CREATOR_PERSON_ID' );
648 -- -----------------------------------------------------------------------
649 -- expose the wf control variables to the custom package
650 -- -----------------------------------------------------------------------
651 set_custom_wf_globals
652 (p_itemtype => itemtype
653 ,p_itemkey => itemkey);
654 --
655 -- get the routing id
656 --
657 l_approval_routing_id := hr_approval_custom.get_routing_details3
658 (p_person_id => l_creator_person_id);
659 -- Set the routing details
660 set_routing_details
661 (p_item_type => itemtype
662 ,p_item_key => itemkey
663 ,p_approval_routing_id => l_approval_routing_id
664 ,p_router_index => 3);
665 -- -----------------------------------------------------------------------
666 -- set workflow activity to the SUCCESS state to end workflow
667 -- -----------------------------------------------------------------------
668 result := 'COMPLETE:SUCCESS';
669 elsif ( funmode = 'CANCEL' ) then
670 --
671 null;
672 --
673 --
674 end if;
675 --
676 end set_routing_details3;
677 --
678 -- --------------------------------------------------------------------
679 -- |------------------------< set_routing_details4 >------------------|
680 -- --------------------------------------------------------------------
681 --
682 -- This routing id is used for the Training Administrator in the
683 -- Signup for Class workflow notifications.
684 -- Note if set_routing_details4 changes, set_URL13 should change.
685 --
686 --
687 procedure set_routing_details4(itemtype in varchar2,
688 itemkey in varchar2,
689 actid in number,
690 funmode in varchar2,
691 result out nocopy varchar2 ) is
692 -- -------------------------------------------------------------------------
693 -- local variables
694 -- -------------------------------------------------------------------------
695 l_proc_name varchar2(61) := g_package||'set_routing_details4';
696 l_creator_person_id per_people_f.person_id%type;
697 l_approval_routing_id per_people_f.person_id%type;
698 --
699 begin
700 --
701 if ( funmode = 'RUN' ) then
702 --
703 l_creator_person_id :=
704 wf_engine.GetItemAttrNumber(itemtype => itemtype,
705 itemkey => itemkey,
706 aname => 'CREATOR_PERSON_ID' );
707 --
708 -- get the routing id
709 --
710 l_approval_routing_id := hr_approval_custom.get_routing_details4
711 (p_person_id => l_creator_person_id);
712 -- -----------------------------------------------------------------------
713 -- expose the wf control variables to the custom package
714 -- -----------------------------------------------------------------------
715 set_custom_wf_globals
716 (p_itemtype => itemtype
717 ,p_itemkey => itemkey);
718 --
719 -- get the routing id
720 --
721 set_routing_details
722 (p_item_type => itemtype
723 ,p_item_key => itemkey
724 ,p_approval_routing_id => l_approval_routing_id
725 ,p_router_index => 4);
726 -- -----------------------------------------------------------------------
727 -- set workflow activity to the SUCCESS state to end workflow
728 -- -----------------------------------------------------------------------
729 result := 'COMPLETE:SUCCESS';
730 elsif ( funmode = 'CANCEL' ) then
731 --
732 null;
733 --
734 --
735 end if;
736 --
737 end set_routing_details4;
738 --
739 -- --------------------------------------------------------------------
740 -- |------------------------< set_routing_details5 >------------------|
741 -- --------------------------------------------------------------------
742 procedure set_routing_details5(itemtype in varchar2,
743 itemkey in varchar2,
744 actid in number,
745 funmode in varchar2,
746 result out nocopy varchar2 ) is
747 -- -------------------------------------------------------------------------
748 -- local variables
749 -- -------------------------------------------------------------------------
750 l_proc_name varchar2(61) := g_package||'set_routing_details5';
751 l_creator_person_id per_people_f.person_id%type;
752 l_approval_routing_id per_people_f.person_id%type;
753 --
754 begin
755 --
756 if ( funmode = 'RUN' ) then
757 --
758 l_creator_person_id :=
759 wf_engine.GetItemAttrNumber(itemtype => itemtype,
760 itemkey => itemkey,
761 aname => 'CREATOR_PERSON_ID' );
762 -- -----------------------------------------------------------------------
763 -- expose the wf control variables to the custom package
764 -- -----------------------------------------------------------------------
765 set_custom_wf_globals
766 (p_itemtype => itemtype
767 ,p_itemkey => itemkey);
768 --
769 -- get the routing id
770 --
771 l_approval_routing_id := hr_approval_custom.get_routing_details5
772 (p_person_id => l_creator_person_id);
773 --
774 -- Set the routing details
775 set_routing_details
776 (p_item_type => itemtype
777 ,p_item_key => itemkey
778 ,p_approval_routing_id => l_approval_routing_id
779 ,p_router_index => 5);
780 -- -----------------------------------------------------------------------
781 -- set workflow activity to the SUCCESS state to end workflow
782 -- -----------------------------------------------------------------------
783 result := 'COMPLETE:SUCCESS';
784 elsif ( funmode = 'CANCEL' ) then
785 --
786 null;
787 --
788 --
789 end if;
790 --
791 end set_routing_details5;
792 -- --------------------------------------------------------------------
793 -- |----------------------------< set_URL1 >---------------------------|
794 -- --------------------------------------------------------------------
795 procedure set_URL1(itemtype in varchar2,
796 itemkey in varchar2,
797 actid in number,
798 funmode in varchar2,
799 result out nocopy varchar2 ) is
800 -- -------------------------------------------------------------------------
801 -- local variables
802 -- -------------------------------------------------------------------------
803 l_proc_name varchar2(61) := g_package||'set_URL1';
804 l_url varchar2(2000);
805 --
806 begin
807 --
808 if ( funmode = 'RUN' ) then
809 --
810 -- -----------------------------------------------------------------------
811 -- expose the wf control variables to the custom package
812 -- -----------------------------------------------------------------------
813 set_custom_wf_globals
814 (p_itemtype => itemtype
815 ,p_itemkey => itemkey);
816 --
817 -- get the url
818 --
819 l_url := hr_approval_custom.get_URL1;
820 --
821 -- Set the routing details
822 set_URL
823 (p_item_type => itemtype
824 ,p_item_key => itemkey
825 ,p_url => l_url
826 ,p_url_index => 1);
827 -- -----------------------------------------------------------------------
828 -- set workflow activity to the SUCCESS state to end workflow
829 -- -----------------------------------------------------------------------
830 result := 'COMPLETE:SUCCESS';
831 elsif ( funmode = 'CANCEL' ) then
832 --
833 null;
834 --
835 --
836 end if;
837 --
838 end set_URL1;
839 -- --------------------------------------------------------------------
840 -- |----------------------------< set_URL2 >---------------------------|
841 -- --------------------------------------------------------------------
842 procedure set_URL2(itemtype in varchar2,
843 itemkey in varchar2,
844 actid in number,
845 funmode in varchar2,
846 result out nocopy varchar2 ) is
847 -- -------------------------------------------------------------------------
848 -- local variables
849 -- -------------------------------------------------------------------------
850 l_proc_name varchar2(61) := g_package||'set_URL2';
851 l_url varchar2(2000);
852 --
853 begin
854 --
855 if ( funmode = 'RUN' ) then
856 --
857 -- -----------------------------------------------------------------------
858 -- expose the wf control variables to the custom package
859 -- -----------------------------------------------------------------------
860 set_custom_wf_globals
861 (p_itemtype => itemtype
862 ,p_itemkey => itemkey);
863 --
864 -- get the url
865 --
866 l_url := hr_approval_custom.get_URL2;
867 --
868 -- Set the routing details
869 set_URL
870 (p_item_type => itemtype
871 ,p_item_key => itemkey
872 ,p_url => l_url
873 ,p_url_index => 2);
874 -- -----------------------------------------------------------------------
875 -- set workflow activity to the SUCCESS state to end workflow
876 -- -----------------------------------------------------------------------
877 result := 'COMPLETE:SUCCESS';
878 elsif ( funmode = 'CANCEL' ) then
879 --
880 null;
881 --
882 --
883 end if;
884 --
885 end set_URL2;
886 -- --------------------------------------------------------------------
887 -- |----------------------------< set_URL3 >---------------------------|
888 -- --------------------------------------------------------------------
889 procedure set_URL3(itemtype in varchar2,
890 itemkey in varchar2,
891 actid in number,
892 funmode in varchar2,
893 result out nocopy varchar2 ) is
894 -- -------------------------------------------------------------------------
895 -- local variables
896 -- -------------------------------------------------------------------------
897 l_proc_name varchar2(61) := g_package||'set_URL3';
898 l_url varchar2(2000);
899 --
900 begin
901 --
902 if ( funmode = 'RUN' ) then
903 --
904 -- -----------------------------------------------------------------------
905 -- expose the wf control variables to the custom package
906 -- -----------------------------------------------------------------------
907 set_custom_wf_globals
908 (p_itemtype => itemtype
909 ,p_itemkey => itemkey);
910 --
911 -- get the url
912 --
913 l_url := hr_approval_custom.get_URL3;
914 --
915 -- Set the routing details
916 set_URL
917 (p_item_type => itemtype
918 ,p_item_key => itemkey
919 ,p_url => l_url
920 ,p_url_index => 3);
921 -- -----------------------------------------------------------------------
922 -- set workflow activity to the SUCCESS state to end workflow
923 -- -----------------------------------------------------------------------
924 result := 'COMPLETE:SUCCESS';
925 elsif ( funmode = 'CANCEL' ) then
926 --
927 null;
928 --
929 --
930 end if;
931 --
932 end set_URL3;
933 -- --------------------------------------------------------------------
934 -- |----------------------------< set_URL4 >---------------------------|
935 -- --------------------------------------------------------------------
936 procedure set_URL4(itemtype in varchar2,
937 itemkey in varchar2,
938 actid in number,
939 funmode in varchar2,
940 result out nocopy varchar2 ) is
941 -- -------------------------------------------------------------------------
942 -- local variables
943 -- -------------------------------------------------------------------------
944 l_proc_name varchar2(61) := g_package||'set_URL4';
945 l_url varchar2(2000);
946 --
947 begin
948 --
949 if ( funmode = 'RUN' ) then
950 --
951 -- -----------------------------------------------------------------------
952 -- expose the wf control variables to the custom package
953 -- -----------------------------------------------------------------------
954 set_custom_wf_globals
955 (p_itemtype => itemtype
956 ,p_itemkey => itemkey);
957 --
958 -- get the url
959 --
960 l_url := hr_approval_custom.get_URL4;
961 --
962 -- Set the routing details
963 set_URL
964 (p_item_type => itemtype
965 ,p_item_key => itemkey
966 ,p_url => l_url
967 ,p_url_index => 4);
968 -- -----------------------------------------------------------------------
969 -- set workflow activity to the SUCCESS state to end workflow
970 -- -----------------------------------------------------------------------
971 result := 'COMPLETE:SUCCESS';
972 elsif ( funmode = 'CANCEL' ) then
973 --
974 null;
975 --
976 --
977 end if;
978 --
979 end set_URL4;
980 -- --------------------------------------------------------------------
981 -- |----------------------------< set_URL5 >---------------------------|
982 -- View Employee Details - called by Apply for a Job
983 -- --------------------------------------------------------------------
984 procedure set_URL5(itemtype in varchar2,
985 itemkey in varchar2,
986 actid in number,
987 funmode in varchar2,
988 result out nocopy varchar2 ) is
989 -- -------------------------------------------------------------------------
990 -- local variables
991 -- -------------------------------------------------------------------------
992 l_proc_name varchar2(61) := g_package||'set_URL5';
993 l_url varchar2(2000);
994 l_person_id per_people_f.person_id%type;
995 --
996 begin
997 --
998 if ( funmode = 'RUN' ) then
999 --
1000 -- -----------------------------------------------------------------------
1001 -- expose the wf control variables to the custom package
1002 -- -----------------------------------------------------------------------
1003 set_custom_wf_globals
1004 (p_itemtype => itemtype
1005 ,p_itemkey => itemkey);
1006 --
1007 -- get the url
1008 --
1009 l_url := hr_approval_custom.get_URL5;
1010 -- Set the routing details
1011 set_URL
1012 (p_item_type => itemtype
1013 ,p_item_key => itemkey
1014 ,p_url => l_url
1015 ,p_url_index => 5);
1016 -- -----------------------------------------------------------------------
1017 -- set workflow activity to the SUCCESS state to end workflow
1018 -- -----------------------------------------------------------------------
1019 result := 'COMPLETE:SUCCESS';
1020 elsif ( funmode = 'CANCEL' ) then
1021 --
1022 null;
1023 --
1024 --
1025 end if;
1026 --
1027 end set_URL5;
1028 -- --------------------------------------------------------------------
1029 -- |----------------------------< set_URL6 >---------------------------|
1030 -- View Vacancy Details - called by Apply for a Job
1031 -- --------------------------------------------------------------------
1032 procedure set_URL6(itemtype in varchar2,
1033 itemkey in varchar2,
1034 actid in number,
1035 funmode in varchar2,
1036 result out nocopy varchar2 ) is
1037 -- -------------------------------------------------------------------------
1038 -- local variables
1039 -- -------------------------------------------------------------------------
1040 l_proc_name varchar2(61) := g_package||'set_URL6';
1041 l_url varchar2(2000);
1042 --
1043 begin
1044 --
1045 if ( funmode = 'RUN' ) then
1046 --
1047 -- -----------------------------------------------------------------------
1048 -- expose the wf control variables to the custom package
1049 -- -----------------------------------------------------------------------
1050 set_custom_wf_globals
1051 (p_itemtype => itemtype
1052 ,p_itemkey => itemkey);
1053 --
1054 -- get the url
1055 --
1056 l_url := hr_approval_custom.get_URL6;
1057 if l_url is null then
1058 l_url := icx_sec.jumpintofunction
1059 (p_application_id => 800
1060 ,p_function_code => 'PER_CALL_ON_FLOW_FUN'
1061 ,p_parameter1 => '800*PER_VACANCY_APPLICATION_F*' ||
1062 '800*PER_EMPK_VACANCIES_FP*' ||
1063 '800*PER_EMPK_VACANCIES_R*'||
1064 '800*PER_EMPK_VACANCY_DETAILS1_FP*PER_CURVAC_VACID_PK1*]'
1065 ,p_parameter2 => wf_engine.getitemattrtext
1066 (itemtype => itemtype,
1067 itemkey => itemkey,
1068 aname =>'PARAMETER1') );
1069 end if;
1070 --
1071 -- Set the routing details
1072 set_URL
1073 (p_item_type => itemtype
1074 ,p_item_key => itemkey
1075 ,p_url => l_url
1076 ,p_url_index => 6);
1077 -- -----------------------------------------------------------------------
1078 -- set workflow activity to the SUCCESS state to end workflow
1079 -- -----------------------------------------------------------------------
1080 result := 'COMPLETE:SUCCESS';
1081 elsif ( funmode = 'CANCEL' ) then
1082 --
1083 null;
1084 --
1085 --
1086 end if;
1087 --
1088 end set_URL6;
1089 -- --------------------------------------------------------------------
1090 -- |----------------------------< set_URL7 >---------------------------|
1091 -- View Job Details - called by Apply for a job
1092 -- --------------------------------------------------------------------
1093 procedure set_URL7(itemtype in varchar2,
1094 itemkey in varchar2,
1095 actid in number,
1096 funmode in varchar2,
1097 result out nocopy varchar2 ) is
1098 -- -------------------------------------------------------------------------
1099 -- local variables
1100 -- -------------------------------------------------------------------------
1101 l_proc_name varchar2(61) := g_package||'set_URL7';
1102 l_url varchar2(2000);
1103 l_vacancy_id per_vacancies.vacancy_id%type;
1104 l_job_id per_vacancies.job_id%type;
1105 --
1106 begin
1107 --
1108 if ( funmode = 'RUN' ) then
1109 --
1110 -- -----------------------------------------------------------------------
1111 -- expose the wf control variables to the custom package
1112 -- -----------------------------------------------------------------------
1113 set_custom_wf_globals
1114 (p_itemtype => itemtype
1115 ,p_itemkey => itemkey);
1116 --
1117 -- get the url
1118 --
1119 l_url := hr_approval_custom.get_URL7;
1120 if l_url is null then
1121 l_vacancy_id := to_number(wf_engine.getitemattrtext
1122 (itemtype => itemtype,
1123 itemkey => itemkey,
1124 aname =>'PARAMETER1'));
1125 open g_csr_vacancies(l_vacancy_id);
1126 fetch g_csr_vacancies into g_vacancies;
1127 close g_csr_vacancies;
1128 l_url := icx_sec.jumpintofunction
1129 (p_application_id => 800
1130 ,p_function_code => 'PER_CALL_ON_FLOW_FUN'
1131 ,p_parameter1 => '800*PER_VACANCY_APPLICATION_F*' ||
1132 '800*PER_EMPK_VACANCY)_DETAILS_FP*' ||
1133 '800*PER_EMPK_VACANCY_DETAILS_R*' ||
1134 '800*PER_EMPK_JOB_DETAILS_FP*PER_CURVAC_JOBID_PK2*]'
1135 ,p_parameter2 => to_char(g_vacancies.job_id));
1136 end if;
1137 --
1138 -- Set the routing details
1139 set_URL
1140 (p_item_type => itemtype
1141 ,p_item_key => itemkey
1142 ,p_url => l_url
1143 ,p_url_index => 7);
1144 -- -----------------------------------------------------------------------
1145 -- set workflow activity to the SUCCESS state to end workflow
1146 -- -----------------------------------------------------------------------
1147 result := 'COMPLETE:SUCCESS';
1148 elsif ( funmode = 'CANCEL' ) then
1149 --
1150 null;
1151 --
1152 --
1153 end if;
1154 --
1155 end set_URL7;
1156 -- --------------------------------------------------------------------
1157 -- |----------------------------< set_URL8 >---------------------------|
1158 -- Recruiter email - called by Apply for a Job
1159 -- --------------------------------------------------------------------
1160 procedure set_URL8(itemtype in varchar2,
1161 itemkey in varchar2,
1162 actid in number,
1163 funmode in varchar2,
1164 result out nocopy varchar2 ) is
1165 -- -------------------------------------------------------------------------
1166 -- local variables
1167 -- -------------------------------------------------------------------------
1168 l_proc_name varchar2(61) := g_package||'set_URL8';
1169 l_url varchar2(2000);
1170 l_email_address per_people_f.email_address%type;
1171 l_person_id per_vacancies.vacancy_id%type;
1172 --
1173 begin
1174 --
1175 if ( funmode = 'RUN' ) then
1176 --
1177 -- -----------------------------------------------------------------------
1178 -- expose the wf control variables to the custom package
1179 -- -----------------------------------------------------------------------
1180 set_custom_wf_globals
1181 (p_itemtype => itemtype
1182 ,p_itemkey => itemkey);
1183 --
1184 -- get the url
1185 --
1186 l_url := hr_approval_custom.get_URL8;
1187 if l_url is null then
1188 l_person_id := wf_engine.getitemattrnumber
1189 (itemtype => itemtype,
1190 itemkey => itemkey,
1191 aname =>'APPROVAL_ROUTING_PERSON_ID3');
1192 open g_csr_email_address(trunc(sysdate),l_person_id);
1193 fetch g_csr_email_address into l_email_address;
1194 close g_csr_email_address;
1195 l_url := 'mailto:'||l_email_address;
1196
1197 end if;
1198 --
1199 -- Set the routing details
1200 set_URL
1201 (p_item_type => itemtype
1202 ,p_item_key => itemkey
1203 ,p_url => l_url
1204 ,p_url_index => 8);
1205 -- -----------------------------------------------------------------------
1206 -- set workflow activity to the SUCCESS state to end workflow
1207 -- -----------------------------------------------------------------------
1208 result := 'COMPLETE:SUCCESS';
1209 elsif ( funmode = 'CANCEL' ) then
1210 --
1211 null;
1212 --
1213 --
1214 end if;
1215 --
1216 end set_URL8;
1217 -- --------------------------------------------------------------------
1218 -- |----------------------------< set_URL9 >---------------------------|
1219 -- Employee's supervisor email - called by Apply for a Job
1220 -- --------------------------------------------------------------------
1221 procedure set_URL9(itemtype in varchar2,
1222 itemkey in varchar2,
1223 actid in number,
1224 funmode in varchar2,
1225 result out nocopy varchar2 ) is
1226 -- -------------------------------------------------------------------------
1227 -- local variables
1228 -- -------------------------------------------------------------------------
1229 l_proc_name varchar2(61) := g_package||'set_URL9';
1230 l_url varchar2(2000);
1231 l_out_person_id per_people_f.person_id%type default null;
1232 l_person_id per_people_f.person_id%type default null;
1233 l_email_address per_people_f.email_address%type;
1234 --
1235 begin
1236 --
1237 if ( funmode = 'RUN' ) then
1238 --
1239 -- -----------------------------------------------------------------------
1240 -- expose the wf control variables to the custom package
1241 -- -----------------------------------------------------------------------
1242 set_custom_wf_globals
1243 (p_itemtype => itemtype
1244 ,p_itemkey => itemkey);
1245 --
1246 -- get the url
1247 --
1248 l_url := hr_approval_custom.get_URL9;
1249 if l_url is null then
1250 l_person_id := wf_engine.GetItemAttrNumber
1251 (itemtype => itemtype,
1252 itemkey => itemkey,
1253 aname => 'CREATOR_PERSON_ID' );
1254 open g_csr_pa(trunc(sysdate), l_person_id);
1255 fetch g_csr_pa into l_out_person_id;
1256 if g_csr_pa%found then
1257 open g_csr_email_address(trunc(sysdate),l_out_person_id);
1258 fetch g_csr_email_address into l_email_address;
1259 close g_csr_email_address;
1260 l_url := 'mailto:'||l_email_address;
1261 end if;
1262 close g_csr_pa;
1263 end if;
1264 --
1265 -- Set the routing details
1266 set_URL
1267 (p_item_type => itemtype
1268 ,p_item_key => itemkey
1269 ,p_url => l_url
1270 ,p_url_index => 9);
1271 -- -----------------------------------------------------------------------
1272 -- set workflow activity to the SUCCESS state to end workflow
1273 -- -----------------------------------------------------------------------
1274 result := 'COMPLETE:SUCCESS';
1275 elsif ( funmode = 'CANCEL' ) then
1276 --
1277 null;
1278 --
1279 --
1280 end if;
1281 --
1282 end set_URL9;
1283 -- --------------------------------------------------------------------
1284 -- |----------------------------< set_URL10 >---------------------------|
1285 -- Employee email - called by Apply for a Job
1286 -- --------------------------------------------------------------------
1287 procedure set_URL10(itemtype in varchar2,
1288 itemkey in varchar2,
1289 actid in number,
1290 funmode in varchar2,
1291 result out nocopy varchar2 ) is
1292 -- -------------------------------------------------------------------------
1293 -- local variables
1294 -- -------------------------------------------------------------------------
1295 l_proc_name varchar2(61) := g_package||'set_URL10';
1296 l_url varchar2(2000);
1297 l_person_id per_people_f.person_id%type default null;
1298 l_email_address per_people_f.email_address%type;
1299 --
1300 begin
1301 --
1302 if ( funmode = 'RUN' ) then
1303 --
1304 -- -----------------------------------------------------------------------
1305 -- expose the wf control variables to the custom package
1306 -- -----------------------------------------------------------------------
1307 set_custom_wf_globals
1308 (p_itemtype => itemtype
1309 ,p_itemkey => itemkey);
1310 --
1311 -- get the url
1312 --
1313 l_url := hr_approval_custom.get_URL10;
1314 if l_url is null then
1315 l_person_id := wf_engine.GetItemAttrNumber
1316 (itemtype => itemtype,
1317 itemkey => itemkey,
1318 aname => 'CREATOR_PERSON_ID' );
1319 open g_csr_email_address(trunc(sysdate),l_person_id);
1320 fetch g_csr_email_address into l_email_address;
1321 close g_csr_email_address;
1322 l_url := 'mailto:'||l_email_address;
1323 end if;
1324 --
1325 -- Set the routing details
1326 set_URL
1327 (p_item_type => itemtype
1328 ,p_item_key => itemkey
1329 ,p_url => l_url
1330 ,p_url_index => 10);
1331 -- -----------------------------------------------------------------------
1332 -- set workflow activity to the SUCCESS state to end workflow
1333 -- -----------------------------------------------------------------------
1334 result := 'COMPLETE:SUCCESS';
1335 elsif ( funmode = 'CANCEL' ) then
1336 --
1337 null;
1338 --
1339 --
1340 end if;
1341 --
1342 end set_URL10;
1343 -- --------------------------------------------------------------------
1344 -- |----------------------------< set_URL11 >---------------------------|
1345 -- --------------------------------------------------------------------
1346 -- This URL is used for Class (event) details from the Signup for Class
1347 -- workflow notifications.
1348 --
1349 procedure set_URL11(itemtype in varchar2,
1350 itemkey in varchar2,
1351 actid in number,
1352 funmode in varchar2,
1353 result out nocopy varchar2 ) is
1354 -- -------------------------------------------------------------------------
1355 -- local variables
1356 -- -------------------------------------------------------------------------
1357 l_proc_name varchar2(61) := g_package||'set_URL11';
1358 l_url varchar2(2000);
1359 l_event_id ota_events.event_id%type;
1360 --
1361 begin
1362 --
1363 if ( funmode = 'RUN' ) then
1364 --
1365 -- -----------------------------------------------------------------------
1366 -- expose the wf control variables to the custom package
1367 -- -----------------------------------------------------------------------
1368 set_custom_wf_globals
1369 (p_itemtype => itemtype
1370 ,p_itemkey => itemkey);
1371 --
1372 -- get the url
1373 --
1374 l_url := hr_approval_custom.get_URL11;
1375 if l_url is null then
1376 l_event_id := to_number(wf_engine.getitemattrtext
1377 (itemtype => itemtype
1378 ,itemkey => itemkey
1379 ,aname =>'PARAMETER1'));
1380
1381 -- this is the same URL to link to pages that have the
1382 -- 'signup' button, except that link has DETAIL instead of DETAIL1
1383 l_url := icx_sec.jumpintofunction
1384 (p_application_id => 800
1385 ,p_function_code => 'PER_CALL_ON_FLOW_FUN'
1386 ,p_parameter1 => '810*OTA_TRAINING_ADMINISTRATION_F*' ||
1387 '810*OTA_SCHEDULED_EVENTS_FP*' ||
1388 '810*OTA_SCHEDULED_EVENTS_R*' ||
1389 '810*OTA_SCHEDULED_EVENT_DETAIL1_FP*' ||
1390 'OTA_SEV_EVID_PK1*]'
1391 ,p_parameter2 => to_char(l_event_id) );
1392 end if;
1393 --
1394 -- Set the routing details
1395 set_URL
1396 (p_item_type => itemtype
1397 ,p_item_key => itemkey
1398 ,p_url => l_url
1399 ,p_url_index => 11);
1400 -- -----------------------------------------------------------------------
1401 -- set workflow activity to the SUCCESS state to end workflow
1402 -- -----------------------------------------------------------------------
1403 result := 'COMPLETE:SUCCESS';
1404 elsif ( funmode = 'CANCEL' ) then
1405 --
1406 null;
1407 --
1408 --
1409 end if;
1410 --
1411 end set_URL11;
1412 -- --------------------------------------------------------------------
1413 -- |----------------------------< set_URL12 >---------------------------|
1414 -- --------------------------------------------------------------------
1415 --
1416 -- This url is to be used for transitioning to an employee's transcript.
1417 -- It is used in the Signup for Class workflow notifications
1418 --
1419 procedure set_URL12(itemtype in varchar2,
1420 itemkey in varchar2,
1421 actid in number,
1422 funmode in varchar2,
1423 result out nocopy varchar2 ) is
1424 -- -------------------------------------------------------------------------
1425 -- local variables
1426 -- -------------------------------------------------------------------------
1427 l_proc_name varchar2(61) := g_package||'set_URL12';
1428 l_url varchar2(2000);
1429 l_person_id per_people_f.person_id%type;
1430 --
1431 begin
1432 --
1433 if ( funmode = 'RUN' ) then
1434 --
1435 -- -----------------------------------------------------------------------
1436 -- expose the wf control variables to the custom package
1437 -- -----------------------------------------------------------------------
1438 set_custom_wf_globals
1439 (p_itemtype => itemtype
1440 ,p_itemkey => itemkey);
1441 --
1442 -- get the url
1443 --
1444 l_url := hr_approval_custom.get_URL12;
1445 if l_url is null then
1446 l_person_id := wf_engine.GetItemAttrNumber
1447 (itemtype => itemtype
1448 ,itemkey => itemkey
1449 ,aname => 'CREATOR_PERSON_ID');
1450
1451 l_url := icx_sec.jumpintofunction
1452 (p_application_id => 800
1453 ,p_function_code => 'PER_CALL_ON_FLOW_FUN'
1454 ,p_parameter1 => '800*PER_HUMAN_RESOURCES_F*' ||
1455 '800*PER_EMPLOYEE_DETAIL_LINKS_FP*' ||
1456 '800*PER_EMPLOYEE_DETAILS_LINK3_R*' ||
1457 '800*PER_TRAINING_HISTORY_FP*' ||
1458 'PER_PERDET_PER_FK1*]'
1459 ,p_parameter2 => to_char(l_person_id) );
1460 end if;
1461 --
1462 -- Set the routing details
1463 set_URL
1464 (p_item_type => itemtype
1465 ,p_item_key => itemkey
1466 ,p_url => l_url
1467 ,p_url_index => 12);
1468 -- -----------------------------------------------------------------------
1469 -- set workflow activity to the SUCCESS state to end workflow
1470 -- -----------------------------------------------------------------------
1471 result := 'COMPLETE:SUCCESS';
1472 elsif ( funmode = 'CANCEL' ) then
1473 --
1474 null;
1475 --
1476 --
1477 end if;
1478 --
1479 end set_URL12;
1480 -- --------------------------------------------------------------------
1481 -- |----------------------------< set_URL13 >---------------------------|
1482 -- --------------------------------------------------------------------
1483 --
1484 -- This url is used to allow a user to send an email to the Training
1485 -- Administrator. The default logic should work fine, since the Routing
1486 -- ID4 is what we assume is loaded with the Training Administrator's Id.
1487 -- This is used in the Signup for Class workflow notifications.
1488 -- Note if set_routing_details4 changes, set_URL13 should change.
1489 --
1490 procedure set_URL13(itemtype in varchar2,
1491 itemkey in varchar2,
1492 actid in number,
1493 funmode in varchar2,
1494 result out nocopy varchar2 ) is
1495 -- -------------------------------------------------------------------------
1496 -- local variables
1497 -- -------------------------------------------------------------------------
1498 l_proc_name varchar2(61) := g_package||'set_URL13';
1499 l_url varchar2(2000);
1500 l_person_id per_people_f.person_id%type;
1501 l_email_address per_people_f.email_address%type;
1502 --
1503 begin
1504 --
1505 if ( funmode = 'RUN' ) then
1506 --
1507 -- -----------------------------------------------------------------------
1508 -- expose the wf control variables to the custom package
1509 -- -----------------------------------------------------------------------
1510 set_custom_wf_globals
1511 (p_itemtype => itemtype
1512 ,p_itemkey => itemkey);
1513 --
1514 -- get the url
1515 --
1516 l_url := hr_approval_custom.get_URL13;
1517 if l_url is null then
1518 l_person_id := wf_engine.GetItemAttrNumber
1519 (itemtype => itemtype,
1520 itemkey => itemkey,
1521 aname => 'APPROVAL_ROUTING_PERSON_ID4');
1522 open g_csr_email_address(trunc(sysdate),l_person_id);
1523 fetch g_csr_email_address into l_email_address;
1524 close g_csr_email_address;
1525 l_url := 'mailto:'||l_email_address;
1526 end if;
1527 --
1528 -- Set the routing details
1529 set_URL
1530 (p_item_type => itemtype
1531 ,p_item_key => itemkey
1532 ,p_url => l_url
1533 ,p_url_index => 13);
1534 -- -----------------------------------------------------------------------
1535 -- set workflow activity to the SUCCESS state to end workflow
1536 -- -----------------------------------------------------------------------
1537 result := 'COMPLETE:SUCCESS';
1538 elsif ( funmode = 'CANCEL' ) then
1539 --
1540 null;
1541 --
1542 --
1543 end if;
1544 --
1545 end set_URL13;
1546 -- --------------------------------------------------------------------
1547 -- |----------------------------< set_URL14 >---------------------------|
1548 -- --------------------------------------------------------------------
1549 --
1550 -- This URL is used in the Enroll in a Class workflow process.It is the Response
1551 -- URL that the Training Administrator uses to navigate to the Training
1552 -- Administrator Update page.
1553 -- This code does not need to be modified by the customer unless they
1554 -- change which pl/sql procedure is used to call this update page.
1555 --
1556 procedure set_URL14(itemtype in varchar2,
1557 itemkey in varchar2,
1558 actid in number,
1559 funmode in varchar2,
1560 result out nocopy varchar2 ) is
1561 -- -------------------------------------------------------------------------
1562 -- local variables
1563 -- -------------------------------------------------------------------------
1564 l_proc_name varchar2(61) := g_package||'set_URL14';
1565 l_url varchar2(2000);
1566 --
1567 begin
1568 --
1569 if ( funmode = 'RUN' ) then
1570 --
1571 -- -----------------------------------------------------------------------
1572 -- expose the wf control variables to the custom package
1573 -- -----------------------------------------------------------------------
1574 set_custom_wf_globals
1575 (p_itemtype => itemtype
1576 ,p_itemkey => itemkey);
1577 --
1578 -- get the url
1579 --
1580 l_url := hr_approval_custom.get_URL14;
1581 --
1582 if l_url is null then
1583 l_url :=
1584 'ota_class_signup_wf.url?'||
1585 hr_util_web.prepare_parameter
1586 (p_name => 'p_item_type'
1587 ,p_value => itemtype
1588 ,p_prefix => FALSE)||
1589 hr_util_web.prepare_parameter
1590 (p_name => 'p_item_key'
1591 ,p_value => itemkey);
1592
1593 end if;
1594 -- Set the routing details
1595 set_URL
1596 (p_item_type => itemtype
1597 ,p_item_key => itemkey
1598 ,p_url => l_url
1599 ,p_url_index => 14);
1600 -- -----------------------------------------------------------------------
1601 -- set workflow activity to the SUCCESS state to end workflow
1602 -- -----------------------------------------------------------------------
1603 result := 'COMPLETE:SUCCESS';
1604 elsif ( funmode = 'CANCEL' ) then
1605 --
1606 null;
1607 --
1608 --
1609 end if;
1610 --
1611 end set_URL14;
1612
1613 -- ------------------------------------------------------------------------
1614 -- |----------------------< Check_Final_Approver >-------------------------|
1615 -- ------------------------------------------------------------------------
1616 procedure Check_Final_Approver( itemtype in varchar2,
1617 itemkey in varchar2,
1618 actid in number,
1619 funmode in varchar2,
1620 result out nocopy varchar2 ) is
1621 --
1622 -- -------------------------------------------------------------------------
1623 -- local variables
1624 -- -------------------------------------------------------------------------
1625 l_proc_name varchar2(61) := g_package||'check_final_approver';
1626 l_creator_person_id per_people_f.person_id%type;
1627 l_forward_to_person_id per_people_f.person_id%type;
1628 --
1629 begin
1630 --
1631 if ( funmode = 'RUN' ) then
1632 --
1633 --
1634 l_creator_person_id := wf_engine.GetItemAttrNumber
1635 (itemtype => itemtype
1636 ,itemkey => itemkey
1637 ,aname => 'CREATOR_PERSON_ID');
1638 --
1639 l_forward_to_person_id := wf_engine.GetItemAttrNumber
1640 (itemtype => itemtype
1641 ,itemkey => itemkey
1642 ,aname => 'FORWARD_TO_PERSON_ID');
1643 -- -----------------------------------------------------------------------
1644 -- expose the wf control variables to the custom package
1645 -- -----------------------------------------------------------------------
1646 set_custom_wf_globals
1647 (p_itemtype => itemtype
1648 ,p_itemkey => itemkey);
1649 --
1650 -- call a custom check final approver. Returns a 'Yes', 'No' or 'Error'
1651 --
1652 result := 'COMPLETE:'||
1653 hr_approval_custom.Check_Final_approver
1654 (p_forward_to_person_id => nvl(l_forward_to_person_id,l_creator_person_id)
1655 ,p_person_id => l_creator_person_id );
1656 --
1657 elsif ( funmode = 'CANCEL' ) then
1658 --
1659 null;
1660 --
1661 end if;
1662 end Check_Final_Approver;
1663 --
1664 -- ------------------------------------------------------------------------
1665 -- |------------------------< Get_next_approver >-------------------------|
1666 -- ------------------------------------------------------------------------
1667 procedure Get_Next_Approver ( itemtype in varchar2,
1668 itemkey in varchar2,
1669 actid in number,
1670 funmode in varchar2,
1671 result out nocopy varchar2 ) is
1672 --
1673 -- -------------------------------------------------------------------------
1674 -- local variables
1675 -- -------------------------------------------------------------------------
1676 l_creator_person_id per_people_f.person_id%type;
1677 l_forward_from_person_id per_people_f.person_id%type;
1678 l_forward_from_username wf_users.name%type;
1679 l_forward_from_disp_name wf_users.display_name%type;
1680 l_forward_to_person_id per_people_f.person_id%type;
1681 l_forward_to_username wf_users.name%type;
1682 l_forward_to_disp_name wf_users.display_name%type;
1683 l_proc_name varchar2(61) := g_package||'get_next_approver';
1684 l_current_forward_to_id per_people_f.person_id%type;
1685 l_current_forward_from_id per_people_f.person_id%type;
1686 --
1687 begin
1688 --
1689 if ( funmode = 'RUN' ) then
1690 -- get the current forward from person
1691 l_current_forward_from_id :=
1692 nvl(wf_engine.GetItemAttrNumber
1693 (itemtype => itemtype
1694 ,itemkey => itemkey
1695 ,aname => 'FORWARD_FROM_PERSON_ID'),
1696 wf_engine.GetItemAttrNumber
1697 (itemtype => itemtype
1698 ,itemkey => itemkey
1699 ,aname => 'CREATOR_PERSON_ID'));
1700 -- get the current forward to person
1701 l_current_forward_to_id :=
1702 nvl(wf_engine.GetItemAttrNumber
1703 (itemtype => itemtype
1704 ,itemkey => itemkey
1705 ,aname => 'FORWARD_TO_PERSON_ID'),
1706 wf_engine.GetItemAttrNumber
1707 (itemtype => itemtype
1708 ,itemkey => itemkey
1709 ,aname => 'CREATOR_PERSON_ID'));
1710 -- -----------------------------------------------------------------------
1711 -- expose the wf control variables to the custom package
1712 -- -----------------------------------------------------------------------
1713 set_custom_wf_globals
1714 (p_itemtype => itemtype
1715 ,p_itemkey => itemkey);
1716 --
1717 -- set the next forward to
1718 --
1719 l_forward_to_person_id :=
1720 hr_approval_custom.Get_Next_Approver
1721 (p_person_id => l_current_forward_to_id);
1722 --
1723 if ( l_forward_to_person_id is null ) then
1724 --
1725 result := 'COMPLETE:F';
1726 --
1727 else
1728 --
1729 wf_directory.GetUserName
1730 (p_orig_system => 'PER'
1731 ,p_orig_system_id => l_forward_to_person_id
1732 ,p_name => l_forward_to_username
1733 ,p_display_name => l_forward_to_disp_name);
1734 --
1735 wf_engine.SetItemAttrNumber
1736 (itemtype => itemtype
1737 ,itemkey => itemkey
1738 ,aname => 'FORWARD_TO_PERSON_ID'
1739 ,avalue => l_forward_to_person_id);
1740 --
1741 wf_engine.SetItemAttrText
1742 (itemtype => itemtype
1743 ,itemkey => itemkey
1744 ,aname => 'FORWARD_TO_USERNAME'
1745 ,avalue => l_forward_to_username);
1746 --
1747 Wf_engine.SetItemAttrText
1748 (itemtype => itemtype
1749 ,itemkey => itemkey
1750 ,aname => 'FORWARD_TO_DISPLAY_NAME'
1751 ,avalue => l_forward_to_disp_name);
1752 --
1753 -- set forward from to old forward to
1754 --
1755 wf_engine.SetItemAttrNumber
1756 (itemtype => itemtype
1757 ,itemkey => itemkey
1758 ,aname => 'FORWARD_FROM_PERSON_ID'
1759 ,avalue => l_current_forward_to_id);
1760 --
1761 -- Get the username and display name for forward from person
1762 -- and save to item attributes
1763 --
1764 wf_directory.GetUserName
1765 (p_orig_system => 'PER'
1766 ,p_orig_system_id => l_current_forward_to_id
1767 ,p_name => l_forward_from_username
1768 ,p_display_name => l_forward_from_disp_name);
1769 --
1770 wf_engine.SetItemAttrText
1771 (itemtype => itemtype
1772 ,itemkey => itemkey
1773 ,aname => 'FORWARD_FROM_USERNAME'
1774 ,avalue => l_forward_from_username);
1775 --
1776 wf_engine.SetItemAttrText
1777 (itemtype => itemtype
1778 ,itemkey => itemkey
1779 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
1780 ,avalue => l_forward_from_disp_name);
1781 --
1782 result := 'COMPLETE:T';
1783 --
1784 end if;
1785 --
1786 elsif ( funmode = 'CANCEL' ) then
1787 --
1788 null;
1789 --
1790 end if;
1791 --
1792 end Get_next_approver;
1793 -- ------------------------------------------------------------------------
1794 -- |-------------------------< copy_approval_comment >---------------------|
1795 -- ------------------------------------------------------------------------
1796 procedure copy_approval_comment( itemtype in varchar2,
1797 itemkey in varchar2,
1798 actid in number,
1799 funmode in varchar2,
1800 result out nocopy varchar2 ) is
1801 -- -------------------------------------------------------------------------
1802 -- local variables
1803 -- -------------------------------------------------------------------------
1804 l_proc_name varchar2(61) := g_package||'copy_approval_comment';
1805 begin
1806 --
1807 if ( funmode = 'RUN' ) then
1808 --
1809 wf_engine.SetItemAttrText
1810 (itemtype => itemtype,
1811 itemkey => itemkey,
1812 aname => 'APPROVAL_COMMENT_COPY',
1813 avalue => wf_engine.GetItemAttrText
1814 (itemtype => itemtype,
1815 itemkey => itemkey,
1816 aname => 'APPROVAL_COMMENT'));
1817 --
1818 elsif ( funmode = 'CANCEL' ) then
1819 --
1820 null;
1821 --
1822 end if;
1823 --
1824 end copy_approval_comment;
1825 -- ---------------------------------------------------------------------------
1826 -- |-------------------------< set_current_person_to_creator >----------------|
1827 -- ---------------------------------------------------------------------------
1828 procedure set_current_person_to_creator
1829 (itemtype in varchar2
1830 ,itemkey in varchar2
1831 ,actid in number
1832 ,funmode in varchar2
1833 ,result out nocopy varchar2) is
1834 -- -------------------------------------------------------------------------
1835 -- local variables
1836 -- -------------------------------------------------------------------------
1837 l_proc_name varchar2(61) := g_package||'set_current_person_to_creator';
1838 --
1839 begin
1840 -- check the workflow funmode value
1841 if funmode = 'RUN' then
1842 -- workflow is RUNing this procedure
1843 --
1844 --
1845 -- Set the current_person_id to the creator_person_id
1846 --
1847 wf_engine.SetItemAttrText
1848 (itemtype => itemtype,
1849 itemkey => itemkey,
1850 aname => 'CURRENT_PERSON_ID',
1851 avalue => wf_engine.GetItemAttrNumber
1852 (itemtype => itemtype,
1853 itemkey => itemkey,
1854 aname => 'CREATOR_PERSON_ID' ));
1855 --
1856 elsif funmode = 'CANCEL' then
1857 -- workflow is calling in cancel mode (performing a loop reset) so ignore
1858 null;
1859 end if;
1860 end set_current_person_to_creator;
1861 -- ---------------------------------------------------------------------------
1862 -- |-------------------------< set_training_admin_person >-------------------|
1863 -- ---------------------------------------------------------------------------
1864 --
1865 -- Description
1866 --
1867 -- gets the value of the training administrator
1868 --
1869 --
1870 procedure set_training_admin_person
1871 (itemtype in varchar2
1872 ,itemkey in varchar2
1873 ,actid in number
1874 ,funmode in varchar2
1875 ,result out nocopy varchar2) is
1876 begin
1877 -- check the workflow funmode value
1878 if funmode = 'RUN' then
1879 -- workflow is RUNing this procedure
1880 --
1881 -- Set the current_person_id to the creator_person_id
1882 --
1883 hr_workflow_service.create_hr_directory_services
1884 (p_item_type => itemtype
1885 ,p_item_key => itemkey
1886 ,p_service_name => 'TRAINING_ADMIN'
1887 ,p_service_person_id => hr_offer_custom.set_training_admin_person);
1888 --
1889 elsif funmode = 'CANCEL' then
1890 -- workflow is calling in cancel mode (performing a loop reset) so ignore
1891 null;
1892 end if;
1893 end set_training_admin_person;
1894 --
1895 -- ---------------------------------------------------------------------------
1896 -- |-------------------------< set_supervisor_id >-------------------|
1897 -- ---------------------------------------------------------------------------
1898 --
1899 -- Description
1900 --
1901 -- gets the value of the supervisor id
1902 --
1903 --
1904 procedure set_supervisor_id
1905 (itemtype in varchar2
1906 ,itemkey in varchar2
1907 ,actid in number
1908 ,funmode in varchar2
1909 ,result out nocopy varchar2) is
1910 begin
1911 -- check the workflow funmode value
1912 if funmode = 'RUN' then
1913 -- workflow is RUNing this procedure
1914 --
1915 -- Set the current_person_id to the creator_person_id
1916 --
1917 hr_workflow_service.create_hr_directory_services
1918 (p_item_type => itemtype
1919 ,p_item_key => itemkey
1920 ,p_service_name => 'SUPERVISOR'
1921 ,p_service_person_id => hr_offer_custom.set_supervisor_id
1922 (wf_engine.getitemattrnumber
1923 (itemtype
1924 ,itemkey
1925 ,'CURRENT_PERSON_ID')));
1926
1927
1928 --
1929 elsif funmode = 'CANCEL' then
1930 -- workflow is calling in cancel mode (performing a loop reset) so ignore
1931 null;
1932 end if;
1933 end set_supervisor_id;
1934
1935 procedure set_forward_to
1936 (itemtype in varchar2
1937 ,itemkey in varchar2
1938 ,actid in number
1939 ,funmode in varchar2
1940 ,result out nocopy varchar2) is
1941 begin
1942 -- check the workflow funmode value
1943 if funmode = 'RUN' then
1944 -- workflow is RUNing this procedure
1945 --
1946 -- Set the current_person_id to the creator_person_id
1947 --
1948 wf_engine.SetItemAttrText (itemtype => itemtype,
1949 itemkey => itemkey,
1950 aname => 'FORWARD_TO_DISPLAY_NAME',
1951 avalue => wf_engine.getitemattrtext
1952 (itemtype => itemtype,
1953 itemkey => itemkey,
1954 aname =>'RECRUITER_DISPLAY_NAME') );
1955
1956 wf_engine.SetItemAttrText (itemtype => itemtype,
1957 itemkey => itemkey,
1958 aname => 'FORWARD_TO_USERNAME',
1959 avalue => wf_engine.getitemattrtext
1960 (itemtype => itemtype,
1961 itemkey => itemkey,
1962 aname =>'RECRUITER_USERNAME') );
1963
1964 elsif funmode = 'CANCEL' then
1965 -- workflow is calling in cancel mode (performing a loop reset) so ignore
1966 null;
1967 end if;
1968 end set_forward_to;
1969
1970
1971
1972 --
1973 -- ------------------------------------------------------------------------
1974 -- |------------------------< Get_next_approver >-------------------------|
1975 -- ------------------------------------------------------------------------
1976 --
1977 -- Description
1978 --
1979 -- Get the next approver in the chain
1980 -- Overloaded method returns the person id of the next approver in the chain
1981 -- or null if no approver exists.
1982 --
1983 --
1984 procedure Get_Next_Approver ( itemtype in varchar2,
1985 itemkey in varchar2,
1986 currentapproverid in per_people_f.person_id%type,
1987 personid out nocopy per_people_f.person_id%type) is
1988 -- -------------------------------------------------------------------------
1989 -- local variables
1990 -- -------------------------------------------------------------------------
1991 l_creator_person_id per_people_f.person_id%type;
1992 BEGIN
1993
1994 -- -----------------------------------------------------------------------
1995 -- expose the wf control variables to the custom package
1996 -- -----------------------------------------------------------------------
1997 set_custom_wf_globals
1998 (p_itemtype => itemtype
1999 ,p_itemkey => itemkey);
2000 --
2001 -- set the next forward to
2002 --
2003 personid :=
2004 hr_approval_custom.Get_Next_Approver
2005 (p_person_id => currentapproverid);
2006
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 RAISE;
2010 END Get_Next_Approver;
2011
2012
2013
2014 --
2015 end hr_approval_wf;