[Home] [Help]
PACKAGE BODY: APPS.GHR_WF_PD_PKG
Source
1 PACKAGE BODY ghr_wf_pd_pkg AS
2 /* $Header: ghwfpd.pkb 115.8 2004/02/12 15:49:48 vravikan ship $ */
3 --
4 -- Procedure
5 -- StartPDProcess
6 --
7 -- Description
8 -- Start the PD workflow process for the given p_position_description_id
9 --
10 PROCEDURE StartPDProcess
11 ( p_position_description_id in number,
12 p_item_key in varchar2,
13 p_forward_to_name in varchar2
14 ) is
15 --
16 l_ItemType varchar2(30) := 'OF8';
17 l_ItemKey ghr_pd_routing_history.item_key%TYPE := p_item_key ;
18 l_from_name varchar2(500);
19 l_forward_from_display_name varchar2(100);
20 l_load_form varchar2(100);
21 l_load_pdrh varchar2(100);
22 l_category ghr_position_descriptions.category%TYPE;
23 l_occupational_code ghr_pd_classifications.occupational_code%TYPE;
24 l_grade_level ghr_pd_classifications.grade_level%TYPE;
25 l_official_title ghr_pd_classifications.official_title%TYPE;
26
27 --
28 l_pay_plan ghr_pd_classifications.pay_plan%TYPE;
29 l_current_status ghr_pd_routing_history.action_taken%TYPE;
30 l_date_initiated varchar2(15);
31 l_date_received varchar2(15);
32 l_routing_group varchar2(500);
33 --
34 begin
35 -- Creates a new runtime process for an application item (OF8)
36 --
37 wf_engine.createProcess( ItemType => l_ItemType,
38 ItemKey => l_ItemKey,
39 process => 'OF8' );
40 --
41 --
42 wf_engine.SetItemAttrText ( itemtype => l_ItemType,
43 itemkey => l_Itemkey,
44 aname => 'PD_ID',
45 avalue => p_position_description_id );
46 wf_engine.SetItemAttrText( itemtype => l_itemtype,
47 itemkey => l_itemkey,
48 aname => 'FWD_NAME',
49 avalue => p_forward_to_name );
50 l_load_form := 'GHRWSPDI:p_position_description_id=' || p_position_description_id
51 || ' p_inbox_query_only="NO"';
52 wf_engine.SetItemAttrText( itemtype => l_itemtype,
53 itemkey => l_itemkey,
54 aname => 'LOAD_PD',
55 avalue => l_load_form
56 );
57 l_load_pdrh := 'GHRWSPDH:p_position_description_id =' || p_position_description_id;
58 wf_engine.SetItemAttrText( itemtype => l_itemtype,
59 itemkey => l_itemkey,
60 aname => 'LOAD_PDRH',
61 avalue => l_load_pdrh
62 );
63 --
64 ghr_wf_pd_pkg.SetDestinationDetails (
65 p_position_description_id => p_position_description_id,
66 p_from_name => l_from_name,
67 p_category => l_category,
68 p_occupational_code => l_occupational_code,
69 p_grade_level => l_grade_level,
70 p_official_title => l_official_title,
71 p_current_status => l_current_status,
72 p_pay_plan => l_pay_plan,
73 p_routing_group => l_routing_group,
74 p_date_inititated => l_date_initiated,
75 p_date_received => l_date_received
76 );
77 --
78 --
79 --
80 wf_engine.SetItemAttrText( itemtype => l_itemtype,
81 itemkey => l_itemkey,
82 aname => 'FROM_NAME',
83 avalue => l_from_name
84 );
85 wf_engine.SetItemAttrText( itemtype => l_itemtype,
86 itemkey => l_itemkey,
87 aname => 'CATEGORY',
88 avalue => l_category
89 );
90 wf_engine.SetItemAttrText( itemtype => l_itemtype,
91 itemkey => l_itemkey,
92 aname => 'OCCUPATIONAL_CODE',
93 avalue => l_occupational_code
94 );
95 wf_engine.SetItemAttrText( itemtype => l_itemtype,
96 itemkey => l_itemkey,
97 aname => 'GRADE_LEVEL',
98 avalue => l_grade_level
99 );
100 wf_engine.SetItemAttrText( itemtype => l_itemtype,
101 itemkey => l_itemkey,
102 aname => 'OFFICIAL_TITLE',
103 avalue => l_official_title
104 );
105 wf_engine.SetItemAttrText( itemtype => l_itemtype,
106 itemkey => l_itemkey,
107 aname => 'PAY_PLAN',
108 avalue => l_pay_plan
109 );
110 wf_engine.SetItemAttrText( itemtype => l_itemtype,
111 itemkey => l_itemkey,
112 aname => 'ROUTING_GROUP',
113 avalue => l_routing_group
114 );
115
116 l_date_initiated := fnd_date.date_to_displaydate(sysdate);
117 wf_engine.SetItemAttrText( itemtype => l_itemtype,
118 itemkey => l_itemkey,
119 aname => 'DATE_INITIATED',
120 avalue => l_date_initiated
121 );
122 wf_engine.SetItemAttrText( itemtype => l_itemtype,
123 itemkey => l_itemkey,
124 aname => 'DATE_RECEIVED',
125 avalue => l_date_received || ' / ' || l_date_initiated
126 );
127 wf_engine.SetItemAttrText( itemtype => l_itemtype,
128 itemkey => l_itemkey,
129 aname => 'CURRENT_STATUS',
130 avalue => l_current_status
131 );
132 --
133 --
134 -- Start the PD workflow process for Position Description WF process
135 --
136 wf_engine.StartProcess ( ItemType => l_ItemType,
137 ItemKey => l_ItemKey );
138 --
139 --
140 --
141 end StartPDProcess;
142 --
143 --
144 PROCEDURE UpdateRHistoryProcess( itemtype in varchar2,
145 itemkey in varchar2,
146 actid in number,
147 funcmode in varchar2,
148 result in OUT NOCOPY varchar2) is
149 --
150 --
151 l_position_description_id ghr_position_descriptions.position_description_id%TYPE;
152 l_result VARCHAR2(4000);
153 --
154 begin
155 -- NOCOPY Changes
156 l_result := result;
157 if funcmode = 'RUN' then
158 l_position_description_id := wf_engine.GetItemAttrText (
159 itemtype => itemtype,
160 itemkey => itemkey,
161 aname => 'PD_ID');
162 ghr_pdh_api.upd_date_notif_sent (
163 p_position_description_id => l_position_description_id,
164 p_date_notification_sent => sysdate
165 );
166 -- no result needed
167 result := 'COMPLETE';
168 return;
169 end if;
170 --
171 -- Other execution modes may be created in the future.
172 -- Activity indicates that it does not implement a mode
173 -- by returning null
174 --
175 result := '';
176 return;
177 exception
178 when others then
179 -- The line below records this function call in the error system
180 -- in the case of an exception.
181 result := l_result;
182 wf_core.context('OF8', 'ghr_wf_pd_pkg.UpdateRHistoryProcess',itemtype, itemkey, to_char(actid), funcmode);
183 raise;
184 --
185 end UpdateRHistoryProcess;
186 --
187 --
188 --
189 procedure FindDestination( itemtype in varchar2,
190 itemkey in varchar2,
191 actid in number,
192 funcmode in varchar2,
193 result in OUT NOCOPY varchar2 ) is
194 --
195 --
196 l_user_name ghr_pd_routing_history.user_name%TYPE;
197 l_from_name VARCHAR2(500);
198 l_action_taken ghr_pd_routing_history.action_taken%TYPE;
199 l_groupbox_name ghr_groupboxes.name%TYPE;
200 l_category ghr_position_descriptions.category%TYPE;
201 l_occupational_code ghr_pd_classifications.occupational_code%TYPE;
202 l_official_title ghr_pd_classifications.official_title%TYPE;
203 l_grade_level ghr_pd_classifications.grade_level%TYPE;
204 --
205
206 l_pay_plan ghr_pd_classifications.pay_plan%TYPE;
207 l_current_status ghr_pd_routing_history.action_taken%TYPE;
208 l_date_initiated varchar2(15);
209 l_date_received varchar2(15);
210 l_routing_group varchar2(500);
211 l_position_description_id ghr_position_descriptions.position_description_id%TYPE;
212 l_result Varchar2(4000);
213 --
214 begin
215 --
216 --
217 -- NOCOPY CHANGES
218 l_result := result;
219 if funcmode = 'RUN' then
220 --
221 l_position_description_id := wf_engine.GetItemAttrText (
222 itemtype => itemtype,
223 itemkey => itemkey,
224 aname => 'PD_ID');
225 ghr_wf_pd_pkg.SetDestinationDetails (
226 p_position_description_id => l_position_description_id,
227 p_from_name => l_from_name,
228 p_category => l_category,
229 p_occupational_code => l_occupational_code,
230 p_grade_level => l_grade_level,
231 p_official_title => l_official_title,
232 p_current_status => l_current_status,
233 p_pay_plan => l_pay_plan,
234 p_routing_group => l_routing_group,
235 p_date_inititated => l_date_initiated,
236 p_date_received => l_date_received
237 );
238
239 wf_engine.SetItemAttrText( itemtype => itemtype,
240 itemkey => itemkey,
241 aname => 'FROM_NAME',
242 avalue => l_from_name
243 );
244 wf_engine.SetItemAttrText( itemtype => itemtype,
245 itemkey => itemkey,
246 aname => 'CATEGORY',
247 avalue => l_category
248 );
249 wf_engine.SetItemAttrText( itemtype => itemtype,
250 itemkey => itemkey,
251 aname => 'OCCUPATIONAL_CODE',
252 avalue => l_occupational_code
253 );
254 wf_engine.SetItemAttrText( itemtype => itemtype,
255 itemkey => itemkey,
256 aname => 'GRADE_LEVEL',
257 avalue => l_grade_level
258 );
259 wf_engine.SetItemAttrText( itemtype => itemtype,
260 itemkey => itemkey,
261 aname => 'OFFICIAL_TITLE',
262 avalue => l_official_title
263 );
264 wf_engine.SetItemAttrText( itemtype => itemtype,
265 itemkey => itemkey,
266 aname => 'PAY_PLAN',
267 avalue => l_pay_plan
268 );
269 wf_engine.SetItemAttrText( itemtype => itemtype,
270 itemkey => itemkey,
271 aname => 'ROUTING_GROUP',
272 avalue => l_routing_group
273 );
274 wf_engine.SetItemAttrText( itemtype => itemtype,
275 itemkey => itemkey,
276 aname => 'DATE_INITIATED',
277 avalue => l_date_initiated
278 );
279 wf_engine.SetItemAttrText( itemtype => itemtype,
280 itemkey => itemkey,
281 aname => 'DATE_RECEIVED',
282 avalue => l_date_received
283 );
284 wf_engine.SetItemAttrText( itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'CURRENT_STATUS',
287 avalue => l_current_status
288 );
289 --
290 --
291 ghr_wf_pd_pkg.GetDestinationDetails (
292 p_position_description_id => l_position_description_id,
293 p_action_taken => l_action_taken,
294 p_user_name => l_user_name,
295 p_groupbox_name => l_groupbox_name
296 );
297 if l_action_taken in ('CANCELED') then
298 result := 'COMPLETE:CANCELLED';
299 return;
300 elsif l_action_taken in ('CLASSIFIED','RECLASSIFIED') then
301 result := 'COMPLETE:CLASSIFIED';
302 return;
303 else
304 --
305 if l_user_name Is Not Null then
306 wf_engine.SetItemAttrText( itemtype => Itemtype,
307 itemkey => Itemkey,
308 aname => 'FWD_NAME',
309 avalue => l_user_name );
310 result := 'COMPLETE:CONTINUE';
311 return;
312 else
313
314 wf_engine.SetItemAttrText( itemtype => Itemtype,
315 itemkey => Itemkey,
316 aname => 'FWD_NAME',
317 avalue => l_groupbox_name );
318 result := 'COMPLETE:CONTINUE';
319 return;
320 end if;
321 --
322 end if;
323 --
324 --
325 end if;
326 --
327 -- Other execution modes may be created in the future.
328 -- Activity indicates that it does not implement a mode
329 -- by returning null
330 --
331 result := '';
332 return;
333 --
334 --
335 exception
336 when others then
337 -- The line below records this function call in the error system
338 -- in the case of an exception.
339 result := l_result;
340 wf_core.context('OF8', 'ghr_wf_pd_pkg.FindDestination',itemtype, itemkey, to_char(actid), funcmode);
341 raise;
342 --
343 end FindDestination;
344 --
345 --
346 PROCEDURE GetDestinationDetails (
347 p_position_description_id in NUMBER,
348 p_action_taken OUT NOCOPY varchar2,
349 p_user_name OUT NOCOPY varchar2,
350 p_groupbox_name OUT NOCOPY varchar2
351 ) IS
352
353 -- Local variables
354 l_pd_routing_history_id ghr_pd_routing_history.pd_routing_history_id%TYPE;
355 l_user_name ghr_pd_routing_history.user_name%TYPE;
356 l_groupbox_id ghr_pd_routing_history.groupbox_id%TYPE;
357 l_action_taken ghr_pd_routing_history.action_taken%TYPE;
358 l_groupbox_name ghr_groupboxes.name%TYPE;
359 --
360 cursor csr_pd_routing_history is
361 SELECT max(pd_routing_history_id)
362 FROM ghr_pd_routing_history
363 WHERE position_description_id = p_position_description_id;
364 --
365 cursor csr_pd_routing_details is
366 SELECT action_taken, user_name, groupbox_id
367 FROM ghr_pd_routing_history
368 WHERE pd_routing_history_id = l_pd_routing_history_id;
369 cursor csr_groupbox_details is
370 SELECT name
371 FROM GHR_GROUPBOXES
372 WHERE GROUPBOX_ID = l_groupbox_id;
373 --
374 --
375 begin
376 -- This function will select from routing history table based User/ Groupbox Name which happens.
377 -- to be the next destination.
378 --
379 open csr_pd_routing_history;
380 fetch csr_pd_routing_history into l_pd_routing_history_id;
381 if csr_pd_routing_history%notfound then
382 null;
383 -- ?? Check with ****
384 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
385 -- hr_utility.raise_error;
386 end if;
387 close csr_pd_routing_history;
388 --
389 -- Get Routing Details
390 open csr_pd_routing_details;
391 fetch csr_pd_routing_details into l_action_taken, l_user_name, l_groupbox_id;
392 if csr_pd_routing_details%notfound then
393 null;
394 -- ?? Check with ****
395 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
396 -- hr_utility.raise_error;
397 end if;
398 close csr_pd_routing_details;
399 --
400 --
401 --
402 if l_action_taken not in ('CANCELED','CLASSIFIED','RECLASSIFIED')
403 or l_action_taken Is Null then
404 if l_user_name is not null then
405 p_user_name := l_user_name;
406 else
407 open csr_groupbox_details;
408 fetch csr_groupbox_details into l_groupbox_name;
409 p_groupbox_name := l_groupbox_name;
410 if csr_groupbox_details%notfound then
411 null;
412 -- ?? Check with ****
413 -- hr_utility.set_message(8301,'GHR_38211_NOA_FAMILY_NOTFOUND');
414 -- hr_utility.raise_error;
415 end if;
416 close csr_groupbox_details;
417 end if;
418 elsif l_action_taken in ('CANCELED','CLASSIFIED','RECLASSIFIED') then
419 p_action_taken := l_action_taken;
420 else
421 p_action_taken := null;
422 end if;
423 --
424 --
425 EXCEPTION
426 WHEN OTHERS THEN
427 p_action_taken := NULL;
428 p_user_name := NULL;
429 p_groupbox_name := NULL;
430 END GetDestinationDetails;
431 --
432 PROCEDURE SetDestinationDetails (
433 p_position_description_id in NUMBER,
434 p_from_name OUT NOCOPY VARCHAR2,
435 p_category OUT NOCOPY varchar2,
436 p_occupational_code OUT NOCOPY varchar2,
437 p_grade_level OUT NOCOPY varchar2,
438 p_official_title OUT NOCOPY varchar2,
439 p_current_status OUT NOCOPY varchar2,
440 p_pay_plan OUT NOCOPY varchar2,
441 p_routing_group OUT NOCOPY varchar2,
442 p_date_inititated OUT NOCOPY varchar2,
443 p_date_received OUT NOCOPY varchar2
444 ) is
445 -- Local variables
446 l_category ghr_position_descriptions.category%TYPE;
447 l_pd_routing_history_id ghr_pd_routing_history.pd_routing_history_id%TYPE;
448 l_occupational_code ghr_pd_classifications.occupational_code%TYPE;
449 l_grade_level ghr_pd_classifications.grade_level%TYPE;
450 l_pd_classification_id ghr_pd_classifications.pd_classification_id%TYPE;
451 l_full_name per_people_f.full_name%TYPE;
452 l_description ghr_routing_groups.description%TYPE;
453 l_name varchar2(240);
454 l_official_title ghr_pd_classifications.official_title%TYPE;
455 --
456 l_pay_plan ghr_pd_classifications.pay_plan%TYPE;
457 l_action_taken ghr_pd_routing_history.action_taken%TYPE;
458 l_date_initiated ghr_pd_routing_history.date_notification_sent%TYPE;
459 l_date_received ghr_pd_routing_history.date_notification_sent%TYPE;
460 l_date_notification_sent ghr_pd_routing_history.date_notification_sent%TYPE;
461 --
462 l_routing_group_id ghr_position_descriptions.routing_group_id%TYPE;
463 l_routing_group_name ghr_routing_groups.name%TYPE;
464 l_count integer;
465 --
466 --
467 cursor csr_pd_details is
468 SELECT category, routing_group_id
469 FROM ghr_position_descriptions
470 WHERE position_description_id = p_position_description_id;
471 --
472 cursor csr_pdc is
473 SELECT count(*)
474 FROM ghr_pd_classifications
475 WHERE position_description_id = p_position_description_id;
476 --
477 cursor csr_pd_classification_details (l_class_grade_by in varchar2) is
478 SELECT occupational_code, grade_level, official_title, pay_plan
479 FROM ghr_pd_classifications
480 WHERE position_description_id = p_position_description_id
481 and class_grade_by = l_class_grade_by;
482 --
483 --
484 cursor csr_rgps is
485 SELECT name, description
486 FROM ghr_routing_groups
487 WHERE routing_group_id = l_routing_group_id;
488 --
489 cursor csr_get_routing_details is
490 SELECT action_taken, date_notification_sent from ghr_pd_routing_history
491 where position_description_id = p_position_description_id
492 order by pd_routing_history_id desc;
493 --
494 begin
495 -- This function will set the Workflow notification message attributes at each hop
496 --
497 -- Get from the category from PD table
498
499 open csr_pd_details;
500 fetch csr_pd_details into l_category, l_routing_group_id;
501 if csr_pd_details%notfound then
502 hr_utility.set_message(8301,'GHR_PD_ID_PRIMARY_KEY_INVALID');
503 hr_utility.raise_error;
504 end if;
505 close csr_pd_details;
506 if l_routing_group_id Is Not Null then
507 open csr_rgps;
508 fetch csr_rgps into l_routing_group_name, l_description;
509 if csr_rgps%notfound then
510 hr_utility.set_message(8301,'GHR_38050_INV_ROUTING_GROUP');
511 hr_utility.raise_error;
512 end if;
513 close csr_rgps;
514 end if;
515 open csr_pdc;
516 fetch csr_pdc into l_count;
517 if (not (csr_pdc%notfound)) and (l_count >= 1 ) then
518 --
519 -- Office of Personnel Management
520 open csr_pd_classification_details ('01');
521 fetch csr_pd_classification_details into
522 l_occupational_code, l_grade_level, l_official_title, l_pay_plan;
523 --
524 if csr_pd_classification_details%notfound then
525 -- Department, Agency, or Establishment
526 close csr_pd_classification_details;
527 open csr_pd_classification_details ('25');
528 fetch csr_pd_classification_details into
529 l_occupational_code, l_grade_level, l_official_title, l_pay_plan;
530 --
531 if csr_pd_classification_details%notfound then
532 -- Second Level Review
533 close csr_pd_classification_details;
534 open csr_pd_classification_details ('50');
535 fetch csr_pd_classification_details into
536 l_occupational_code, l_grade_level, l_official_title, l_pay_plan;
537 --
538 if csr_pd_classification_details%notfound then
539 -- First Level Review
540 close csr_pd_classification_details;
541 open csr_pd_classification_details ('75');
542 fetch csr_pd_classification_details into
543 l_occupational_code, l_grade_level, l_official_title, l_pay_plan;
544 --
545 if csr_pd_classification_details%notfound then
546 -- Recommended by Supervisor or Initiating Office
547 close csr_pd_classification_details;
548 open csr_pd_classification_details ('99');
549 fetch csr_pd_classification_details into
550 l_occupational_code, l_grade_level, l_official_title, l_pay_plan;
551 end if;
552 --
553 end if;
554 --
555 end if;
556 --
557 end if;
558 -- Close cursor this assumes that there is atleast one PDC record
559 close csr_pd_classification_details;
560 close csr_pdc;
561 end if;
562 --
563 --
564 -- Set out params
565 p_from_name := FND_GLOBAL.USER_NAME();
566 p_routing_group := l_routing_group_name || ' - ' || l_description;
567 p_category := l_category;
568 p_occupational_code := l_occupational_code;
569 p_grade_level := l_grade_level;
570 p_official_title := l_official_title;
571 p_pay_plan := l_pay_plan;
572 -- Get Routing Details
573 l_count := 0;
574 -- Open cursor
575 open csr_get_routing_details;
576 loop
577 fetch csr_get_routing_details into l_action_taken, l_date_notification_sent;
578 exit when csr_get_routing_details%NOTFOUND;
579 if l_count = 0 then
580 p_current_status := l_action_taken;
581 p_date_received := fnd_date.date_to_displaydate(sysdate);
582 l_count := 1;
583 end if;
584 --
585 if l_count = 1 and l_action_taken Is Not Null then
586 p_current_status := l_action_taken;
587 l_count := 2;
588 end if;
589 --
590 if l_action_taken in ('REOPENED','INITIATED') then
591 if l_date_notification_sent Is Not Null then
592 p_date_inititated := fnd_date.date_to_displaydate(l_date_notification_sent);
593 exit;
594 end if;
595 end if;
596 end loop;
597 close csr_get_routing_details;
598 --
599 EXCEPTION
600 WHEN OTHERS THEN
601 p_from_name := NULL;
602 p_category := NULL;
603 p_occupational_code := NULL;
604 p_grade_level := NULL;
605 p_official_title := NULL;
606 p_current_status := NULL;
607 p_pay_plan := NULL;
608 p_routing_group := NULL;
609 p_date_inititated := NULL;
610 p_date_received := NULL;
611 END SetDestinationDetails;
612 --
613 --
614 procedure CompleteBlockingOfPD ( p_position_description_id in Number) is
615 --
616 --
617 l_Item_Key ghr_pd_routing_history.item_key%TYPE;
618 --
619 cursor csr_pdh is
620 SELECT max(to_number(item_key))
621 FROM ghr_pd_routing_history
622 WHERE position_description_id = p_position_description_id;
623 --
624 begin
625 open csr_pdh;
626 fetch csr_pdh into l_item_key;
627 if csr_pdh%notfound then
628 hr_utility.set_message(8301,'GHR_PD_ID_PRIMARY_KEY_INVALID');
629 hr_utility.raise_error;
630 else
631 wf_engine.CompleteActivity('OF8', l_item_key, 'GH_NOTIFY_PD','COMPLETE');
632 end if;
633 close csr_pdh;
634 --
635 end;
636 --
637 --
638 PROCEDURE get_routing_group_details (
639 p_user_name IN fnd_user.user_name%TYPE
640 ,p_position_description_id IN
641 ghr_position_descriptions.position_description_id%TYPE
642 ,p_routing_group_id IN OUT NOCOPY NUMBER
643 ,p_initiator_flag IN OUT NOCOPY VARCHAR2
644 ,p_requester_flag IN OUT NOCOPY VARCHAR2
645 ,p_authorizer_flag IN OUT NOCOPY VARCHAR2
646 ,p_personnelist_flag IN OUT NOCOPY VARCHAR2
647 ,p_approver_flag IN OUT NOCOPY VARCHAR2
648 ,p_reviewer_flag IN OUT NOCOPY VARCHAR2) IS
649 CURSOR cur_rgr IS
650 -- Routing Group details
651 SELECT pei.pei_information3 routing_group_id
652 ,pei.pei_information4 initiator_flag
653 ,pei.pei_information5 requester_flag
654 ,pei.pei_information6 authorizer_flag
655 ,pei.pei_information7 personnelist_flag
656 ,pei.pei_information8 approver_flag
657 ,pei.pei_information9 reviewer_flag
658 FROM per_people_extra_info pei
659 ,fnd_user use
660 WHERE use.user_name = p_user_name
661 AND pei.person_id = use.employee_id
662 AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
663 AND pei.pei_information3 = (SELECT routing_group_id from
664 GHR_POSITION_DESCRIPTIONS
665 WHERE position_description_id = p_position_description_id);
666
667 -- NOCOPY CHANGES
668 l_routing_group_id NUMBER(20);
669 l_initiator_flag VARCHAR2(100);
670 l_requester_flag VARCHAR2(100);
671 l_authorizer_flag VARCHAR2(100);
672 l_personnelist_flag VARCHAR2(100);
673 l_approver_flag VARCHAR2(100);
674 l_reviewer_flag VARCHAR2(100);
675
676 BEGIN
677 -- NOCOPY CHANGES
678 l_routing_group_id := p_routing_group_id;
679 l_initiator_flag := p_initiator_flag;
680 l_requester_flag := p_requester_flag;
681 l_authorizer_flag := p_authorizer_flag;
682 l_personnelist_flag := p_personnelist_flag;
683 l_approver_flag := p_approver_flag;
684 l_reviewer_flag := p_reviewer_flag;
685
686 -- while we are here we may as well get the personal roles even though this maybe overwriten
687 -- by the group box roles later
688 FOR cur_rgr_rec IN cur_rgr LOOP
689 p_routing_group_id := cur_rgr_rec.routing_group_id;
690 p_initiator_flag := cur_rgr_rec.initiator_flag;
691 p_requester_flag := cur_rgr_rec.requester_flag;
692 p_authorizer_flag := cur_rgr_rec.authorizer_flag;
693 p_personnelist_flag := cur_rgr_rec.personnelist_flag;
694 p_approver_flag := cur_rgr_rec.approver_flag;
695 p_reviewer_flag := cur_rgr_rec.reviewer_flag;
696 END LOOP;
697 EXCEPTION
698 -- NOCOPY CHANGES
699 WHEN OTHERS THEN
700 p_routing_group_id := l_routing_group_id;
701 p_initiator_flag := l_initiator_flag;
702 p_requester_flag := l_requester_flag;
703 p_authorizer_flag := l_authorizer_flag;
704 P_personnelist_flag := l_personnelist_flag;
705 p_approver_flag := l_approver_flag;
706 p_reviewer_flag := l_reviewer_flag;
707
708 END get_routing_group_details;
709 --
710 --
711 -- ----------------------------------------------------------------------------
712 -- |-------------------------< item_attribute_exists >------------------------|
713 -- ----------------------------------------------------------------------------
714 function item_attribute_exists
715 (p_item_type in wf_items.item_type%type
716 ,p_item_key in wf_items.item_key%type
717 ,p_name in wf_item_attributes_tl.name%type)
718 return boolean is
719 -- --------------------------------------------------------------------------
720 -- declare local variables
721 -- --------------------------------------------------------------------------
722 l_dummy number(1);
723 l_return boolean := TRUE;
724 -- cursor determines if an attribute exists
725 cursor csr_wiav is
726 select 1
727 from wf_item_attribute_values wiav
728 where wiav.item_type = p_item_type
729 and wiav.item_key = p_item_key
730 and wiav.name = p_name;
731 --
732 --
733 begin
734 -- open the cursor
735 open csr_wiav;
736 fetch csr_wiav into l_dummy;
737 if csr_wiav%notfound then
738 -- item attribute does not exist so return false
739 l_return := FALSE;
740 end if;
741 close csr_wiav;
742 return(l_return);
743 end item_attribute_exists;
744 --
745 --
746 PROCEDURE CheckIfPDWfEnd ( itemtype in varchar2,
747 itemkey in varchar2,
748 actid in number,
749 funcmode in varchar2,
750 result in OUT NOCOPY varchar2) is
751 --
752 --
753 l_action_taken ghr_pd_routing_history.action_taken%TYPE;
754 l_position_description_id ghr_position_descriptions.position_description_id%TYPE;
755 l_load_form varchar2(100);
756 l_result VARCHAR2(4000);
757 --
758 cursor csr_pdh is
759 SELECT action_taken
760 FROM ghr_pd_routing_history
761 WHERE position_description_id = l_position_description_id
762 order by pd_routing_history_id desc;
763 --
764 begin
765 -- NOCOPY CHANGES
766 l_result := result;
767 if funcmode = 'RUN' then
768 l_position_description_id := wf_engine.GetItemAttrText (
769 itemtype => itemtype,
770 itemkey => itemkey,
771 aname => 'PD_ID');
772 open csr_pdh;
773 fetch csr_pdh into l_action_taken;
774 if csr_pdh%notfound then
775 hr_utility.set_message(8301,'GHR_PD_ID_PRIMARY_KEY_INVALID');
776 hr_utility.raise_error;
777 end if;
778 close csr_pdh;
779 l_load_form := 'GHRWSPDI:p_position_description_id=' || l_position_description_id
780 || ' p_inbox_query_only="YES"';
781 wf_engine.SetItemAttrText( itemtype => itemtype,
782 itemkey => itemkey,
783 aname => 'PD_FORM_RO',
784 avalue => l_load_form
785 );
786 --
787 if l_action_taken in ('CLASSIFIED','RECLASSIFIED') then
788 result := 'COMPLETE:YES';
789 return;
790 else
791 result := 'COMPLETE:NO';
792 return;
793 end if;
794 end if;
795 --
796 -- Other execution modes may be created in the future.
797 -- Activity indicates that it does not implement a mode
798 -- by returning null
799 --
800 result := '';
801 return;
802 exception
803 when others then
804 result := l_result;
805 -- The line below records this function call in the error system
806 -- in the case of an exception.
807 wf_core.context('OF8', 'ghr_wf_pd_pkg.CheckIfPDWfEnd',itemtype, itemkey, to_char(actid), funcmode);
808 raise;
809 --
810 end CheckIfPDWfEnd;
811 --
812 --
813 --
814 PROCEDURE EndPDProcess( itemtype in varchar2,
815 itemkey in varchar2,
816 actid in number,
817 funcmode in varchar2,
818 result in OUT NOCOPY varchar2) is
819 l_result VARCHAR2(4000);
820 begin
821 -- NOCOPY CHANGES
822 l_result := result;
823 if funcmode = 'RUN' then
824 result := 'COMPLETE:COMPLETED';
825 return;
826 end if;
827 --
828 -- Other execution modes may be created in the future.
829 -- Activity indicates that it does not implement a mode
830 -- by returning null
831 --
832 result := '';
833 return;
834 --
835 exception
836 when others then
837 result := l_result;
838 -- The line below records this function call in the error system
839 -- in the case of an exception.
840 wf_core.context('OF8', 'ghr_wf_pd_pkg.EndPDProcess',itemtype, itemkey, to_char(actid), funcmode);
841 raise;
842 --
843 end EndPDProcess;
844 --
845 --
846 end ghr_wf_pd_pkg;