[Home] [Help]
PACKAGE BODY: APPS.HR_APPRAISAL_WORKFLOW_SS
Source
1 PACKAGE BODY hr_appraisal_workflow_ss AS
2 /* $Header: hrapwfss.pkb 120.10 2012/03/01 19:41:47 schowdhu ship $ */
3
4
5 -- Global Variables
6 gv_package CONSTANT VARCHAR2(100) DEFAULT 'hr_appraisal_workflow_ss';
7 g_debug boolean default false ;
8 g_invalid_appraisal_id exception;
9 g_invalid_participant_id exception;
10 g_orig_system constant varchar2(3) DEFAULT 'PER';
11 g_no_system_params exception;
12 g_oa_media constant varchar2(100) DEFAULT fnd_web_config.web_server||'OA_MEDIA/';
13 g_oa_html constant varchar2(100) DEFAULT fnd_web_config.jsp_agent;
14 --
15 -- Private Variables
16 --
17
18 --
19 -- PRIVATE FUNCTIONS
20 --
21 FUNCTION isAppraiseeFeebackAllowed
22 (p_appraisal_id IN number) RETURN VARCHAR2;
23
24 --
25 -- ----------------------------------------------------------------------------
26 -- |-------------------------< item_attribute_exists >------------------------|
27 -- ----------------------------------------------------------------------------
28 function item_attribute_exists
29 (p_item_type in wf_items.item_type%type
30 ,p_item_key in wf_items.item_key%type
31 ,p_name in wf_item_attribute_values.name%type)
32 return boolean is
33 -- --------------------------------------------------------------------------
34 -- declare local variables
35 -- --------------------------------------------------------------------------
36 l_dummy number(1);
37 l_return boolean := TRUE;
38 -- cursor determines if an attribute exists
39 cursor csr_wiav is
40 select 1
41 from wf_item_attribute_values wiav
42 where wiav.item_type = p_item_type
43 and wiav.item_key = p_item_key
44 and wiav.name = p_name;
45 --
46 begin
47 -- open the cursor
48 open csr_wiav;
49 fetch csr_wiav into l_dummy;
50 if csr_wiav%notfound then
51 -- item attribute does not exist so return false
52 l_return := FALSE;
53 end if;
54 close csr_wiav;
55 return(l_return);
56 end item_attribute_exists;
57
58
59
60 procedure setAppraisalSystemParams
61 (itemtype in varchar2
62 ,itemkey in varchar2
63 ,actid in number
64 ,funmode in varchar2
65 ,result in out nocopy varchar2) is
66 --local variables
67 ln_appraisal_id number;
68 l_system_params per_appraisals.system_params%type;
69 begin
70 -- Do nothing in cancel or timeout mode
71 if (funmode <> wf_engine.eng_run) then
72 result := wf_engine.eng_null;
73 return;
74 else
75 ln_appraisal_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'APPRAISAL_ID',true);
76 if(ln_appraisal_id is null) then
77 raise g_invalid_appraisal_id;
78 else
79 -- get the system params from per_appraisals
80 begin
81 select system_params
82 into l_system_params
83 from per_appraisals
84 where appraisal_id=ln_appraisal_id;
85
86 -- add the itemkey to the system params
87 l_system_params := l_system_params ||'&pItemKey='||itemkey;
88
89
90 -- update the itemkey value for the current transaction
91 update per_appraisals
92 set
93 system_params = l_system_params
94 where appraisal_id = ln_appraisal_id;
95 exception
96 when no_data_found then
97 raise g_no_system_params;
98 when others then
99 raise;
100 end;
101 end if;
102 end if;
103 result:= 'COMPLETE:';
104
105 exception
106 when others then
107 Wf_Core.Context(gv_package, '.setAppraisalSystemParams', itemtype,
108 itemkey, to_char(actid), funmode);
109 raise;
110 end setAppraisalSystemParams;
111
112
113
114 -- ----------------------------------------------------------------------------
115 -- |----------------------------< start_transaction >-------------------------|
116 -- ----------------------------------------------------------------------------
117 procedure start_transaction
118 (itemtype in varchar2
119 ,itemkey in varchar2
120 ,actid in number
121 ,funmode in varchar2
122 ,result in out nocopy varchar2)
123 is
124 -- --------------------------------------------------------------------------
125 -- declare local variables
126 -- --------------------------------------------------------------------------
127 l_proc varchar2(72);
128 l_transaction_privilege hr_api_transactions.transaction_privilege%type;
129 l_transaction_id hr_api_transactions.transaction_id%type;
130
131 l_function_id hr_api_transactions.function_id%TYPE;
132 ln_selected_person_id hr_api_transactions.selected_person_id%TYPE;
133 lv_process_name hr_api_transactions.process_name%TYPE;
134 lv_status hr_api_transactions.status%TYPE;
135 lv_section_display_name hr_api_transactions.section_display_name%TYPE;
136 ln_assignment_id hr_api_transactions.assignment_id%TYPE;
137 ld_trans_effec_date hr_api_transactions.transaction_effective_date%TYPE;
138 lv_transaction_type hr_api_transactions.transaction_type%TYPE;
139 ln_login_person_id number;
140 ln_appraisal_id number;
141 --
142 begin
143
144 g_debug := hr_utility.debug_enabled;
145
146 IF g_debug THEN
147 l_proc := gv_package||'start_transaction';
148 hr_utility.set_location('Entering:'|| l_proc, 5);
149 END IF;
150 ln_login_person_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CREATOR_PERSON_ID',true);
151 ln_assignment_id := wf_engine.getitemattrnumber(itemtype,itemkey,'ASSIGNMENT_ID',true);
152 ln_appraisal_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'APPRAISAL_ID',true);
153 ln_selected_person_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HR_APPRAISEE_PERSON_ID',true);
154 lv_process_name := wf_engine.GetItemAttrNumber(itemtype,itemkey,'PROCESS_NAME',true);
155
156 hr_transaction_ss.start_transaction(itemtype=>itemtype
157 ,itemkey=>itemkey
158 ,actid=>itemkey
159 ,funmode=>funmode
160 ,p_login_person_id=>ln_login_person_id
161 ,p_product_code=>'PER'
162 ,p_status=>'W'
163 ,p_function_id=>''
164 ,p_transaction_ref_table=>'PER_APPRAISALS'
165 ,p_transaction_ref_id=>ln_appraisal_id
166 ,p_transaction_type=>'#WF'
167 ,p_assignment_id=>ln_assignment_id
168 ,p_selected_person_id=>ln_selected_person_id
169 ,p_transaction_effective_date=>trunc(sysdate)
170 ,p_process_name=>lv_process_name
171 ,result=>result) ;
172 exception
173 when others then
174 raise;
175 --
176 end start_transaction;
177
178
179 PROCEDURE create_hr_transaction
180 ( p_itemtype in varchar2
181 , p_itemkey in varchar2
182 , p_actid in number
183 , p_funcmode in varchar2
184 , p_result in out nocopy varchar2
185 )
186 is
187 -- local variables
188 l_appraisal_id per_appraisals.appraisal_id%type;
189 l_main_appraiser_id per_appraisals.main_appraiser_id%type;
190 l_appraiser_person_id per_appraisals.appraiser_person_id%type;
191 l_appraisee_person_id per_appraisals.appraisee_person_id%type;
192 l_system_params per_appraisals.system_params%type;
193 l_system_type per_appraisals.system_type%type;
194 l_assignment_id per_appraisals.assignment_id%type;
195 l_username wf_users.name%type;
196 l_appraisee_user_name wf_users.name%type;
197 l_supervisor_user_name wf_users.name%type;
198 l_main_appraiser_user_name wf_users.name%type;
199 l_display_name wf_users.display_name%type;
200
201 begin
202 hr_utility.set_location('Entered:'|| gv_package || '.create_hr_transaction', 1);
203 -- get the appraisal_id from the item attribute , APPRAISAL_ID
204 l_appraisal_id:= wf_engine.GetItemAttrNumber (itemtype => p_itemtype ,
205 itemkey => p_itemkey ,
206 aname => 'APPRAISAL_ID',
207 ignore_notfound=>true);
208 -- query the other details from per_appraisals for the given l_appraisal_id
209 -- check if l_appraisal_id is null, if null throw an error
210 if(l_appraisal_id is not null) then
211 select APPRAISAL_ID, MAIN_APPRAISER_ID,APPRAISER_PERSON_ID,
212 APPRAISEE_PERSON_ID,SYSTEM_PARAMS,system_type,assignment_id
213 into l_appraisal_id,l_main_appraiser_id, l_appraiser_person_id,
214 l_appraisee_person_id,l_system_params,l_system_type,l_assignment_id
215 from per_appraisals
216 where APPRAISAL_ID=l_appraisal_id;
217 else
218 raise g_invalid_appraisal_id;
219 end if;
220
221 -- initialize the item attributes
222 -- HR_MAIN_APPRAISER
223 hr_workflow_service.create_hr_directory_services
224 (p_item_type => p_itemtype
225 ,p_item_key => p_itemkey
226 ,p_service_name => 'HR_MAIN_APPRAISER'
227 ,p_service_person_id => l_main_appraiser_id);
228 -- details for the current person record
229 --CURRENT_PERSON
230 hr_workflow_service.create_hr_directory_services
231 (p_item_type => p_itemtype
232 ,p_item_key => p_itemkey
233 ,p_service_name => 'CURRENT_PERSON'
234 ,p_service_person_id => l_appraisee_person_id);
235 --HR_APPRAISEE_USER_NAME_ATTR
236 -- get the role for the Appraisee
237 wf_directory.getrolename(g_orig_system,l_appraisee_person_id,l_appraisee_user_name,l_display_name);
238
239 if(item_attribute_exists(p_itemtype,p_itemkey,'HR_APPRAISEE_USER_NAME_ATTR')) then
240 wf_engine.setitemattrtext(p_itemtype,p_itemkey,'HR_APPRAISEE_USER_NAME_ATTR',l_appraisee_user_name);
241 else
242 wf_engine.additemattr(p_itemtype,p_itemkey,'HR_APPRAISEE_USER_NAME_ATTR',l_appraisee_user_name,null,null);
243 end if;
244 --SUPERVISOR_USERNAME
245 -- get the role for appraisee supervisor
246 wf_directory.getrolename(g_orig_system,l_main_appraiser_id,l_supervisor_user_name,l_display_name);
247 if(item_attribute_exists(p_itemtype,p_itemkey,'SUPERVISOR_USERNAME')) then
248 wf_engine.setitemattrtext(p_itemtype,p_itemkey,'SUPERVISOR_USERNAME',l_supervisor_user_name);
249 else
250 wf_engine.additemattr(p_itemtype,p_itemkey,'SUPERVISOR_USERNAME',l_supervisor_user_name,null,null);
251 end if;
252
253
254 --
255 --set the RFC call back function
256 if(item_attribute_exists(p_itemtype,p_itemkey,'HR_RFC_CB_ATTR')) then
257 wf_engine.setitemattrtext(p_itemtype,p_itemkey,'HR_RFC_CB_ATTR','hr_appraisal_workflow_ss.set_appraisal_rfc_status');
258 else
259 wf_engine.additemattr(p_itemtype,p_itemkey,'HR_RFC_CB_ATTR','hr_appraisal_workflow_ss.set_appraisal_rfc_status',null,null);
260 end if;
261
262 -- 06/02/03
263 -- 06/15/03
264 start_transaction( p_itemtype,p_itemkey, p_actid, p_funcmode , p_result );
265
266 -- 07/10/03
267 -- set the item key to the system params
268 setAppraisalSystemParams(p_itemtype,p_itemkey, p_actid, p_funcmode, p_result );
269 p_result:= wf_engine.eng_trans_default;
270
271
272 hr_utility.set_location('Leaving:'|| gv_package || '.create_hr_transaction', 10);
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 wf_core.Context(gv_package, '.create_hr_transaction', p_itemtype, p_itemkey, p_actid, p_funcmode);
277 hr_utility.trace(' exception in '||gv_package||'.create_hr_transaction : ' || sqlerrm);
278 raise;
279
280 end create_hr_transaction;
281
282 procedure build_link(document_id IN Varchar2,
283 display_type IN Varchar2,
284 document IN OUT NOCOPY varchar2,
285 document_type IN OUT NOCOPY Varchar2) is
286 c_proc varchar2(30) default 'GetItemAttrText';
287 lv_item_type wf_item_activity_statuses.item_type%type;
288 lv_item_key wf_item_activity_statuses.item_key%type;
289 lv_checkProfile VARCHAR2(10);
290 lv_profileValue VARCHAR2(1);
291 lv_status hr_api_transactions.status%type;
292 lv_link_label wf_message_attributes_vl.display_name%type;
293 lv_pageFunc wf_item_attribute_values.text_value%type;
294 lv_web_html_call fnd_form_functions_vl.web_html_call%type;
295 lv_params fnd_form_functions_vl.parameters%type;
296 lv_addtnlParams VARCHAR2(30) ;
297
298
299 begin
300 g_debug := hr_utility.debug_enabled;
301 if g_debug then
302 hr_utility.set_location('Entering:'|| gv_package||'.'||c_proc, 1);
303 end if;
304
305 -- get the itemtype and item key for the notification id
306 hr_workflow_ss.get_item_type_and_key(document_id,lv_item_type,lv_item_key);
307 -- IF (lv_checkProfile = 'N' OR lv_profileValue ='Y' ) THEN
308 -- get the translated display name for the url link
309 begin
310 select wma.display_name
311 into lv_link_label
312 from wf_notifications wn, wf_message_attributes_vl wma
313 where wn.notification_id = document_id
314 and wn.message_name = wma.message_name
315 and wma.message_type = lv_item_type
316 and wma.name = 'OBJECT_URL';
317 exception
318 when others then
319 lv_link_label:= 'OBJECT_URL';
320 end;
321
322 -- build the url link
323 -- get the link details
324 -- get the item attribute holding the FND function name corresponding
325 -- to the MDS document.
326 lv_pageFunc := nvl(wf_engine.GetItemAttrText(lv_item_type,lv_item_key,'HR_OAF_EDIT_URL_ATTR',TRUE),'PQH_SS_EFFDATE');
327 -- get the web_html_call value and params for this function
328 begin
329 select web_html_call,parameters
330 into lv_web_html_call,lv_params
331 from fnd_form_functions_vl
332 where function_name=lv_pageFunc;
333 exception
334 when no_data_found then
335 hr_utility.set_location('Unable to retrieve function details,web_html_call and parameters for:'||lv_pageFunc||' '|| gv_package||'.'||c_proc, 10);
336 when others then
337 raise;
338 end;
339 -- set the out variables
340 lv_addtnlParams := '&'||'retainAM=Y'||'&'||'NtfId='||'&'||'#NID';
341 document := '<tr><td> '||
342 -- '<IMG SRC="'||g_oa_media||'afedit.gif"/>'||
343 '</td><td>'||
344 '<a href='
345 --||g_oa_html
346 ||lv_web_html_call||nvl(lv_params,'')||lv_addtnlParams||'>'
347 ||lv_link_label||'</a></td></tr> ';
348 -- set the document type
349 document_type := wf_notification.doc_html;
350
351 -- else
352 -- document := null;
353 -- end if;
354
355 if g_debug then
356 hr_utility.set_location('Leaving:'|| gv_package||'.'||c_proc, 30);
357 end if;
358
359 exception
360 when others then
361 document := null;
362 document_type :=null;
363 hr_utility.set_location('hr_workflow_ss.build_edit_link errored : '||SQLERRM ||' '||to_char(SQLCODE), 20);
364 Wf_Core.Context('hr_workflow_ss', 'build_edit_link', document_id, display_type);
365 raise;
366 end build_link;
367
368
369 -- ---------------------------------------------------------------------------
370 -- public Procedure declarations
371 -- ---------------------------------------------------------------------------
372 --
373 -- ----------------------------------------------------------------------------
374 -- |------------------------------< Notify>-------------------|
375 -- ----------------------------------------------------------------------------
376 --
377 -- This procedure is a public wrapper to engine notification call
378 -- This reads the activity attributes and sends notification to the ROLE defined
379 -- in the activity attribute PERFORMER with the message conigured in the activity
380 -- attribute MESSAGE. And also can send to group if configured through the activity
381 -- attribute EXPANDROLES.
382 --
383 procedure Notify(itemtype in varchar2,
384 itemkey in varchar2,
385 actid in number,
386 funcmode in varchar2,
387 resultout in out nocopy varchar2)
388 is
389 msg varchar2(30);
390 msgtype varchar2(8);
391 prole wf_users.name%type; -- Fix 3210283.
392 expand_role varchar2(1);
393
394 colon pls_integer;
395 avalue varchar2(240);
396 notid pls_integer;
397 comments wf_notifications .user_comment%type;
398 document varchar2(240);
399 document_type varchar2(240);
400
401 begin
402 -- Do nothing in cancel or timeout mode
403 if (funcmode <> wf_engine.eng_run) then
404 resultout := wf_engine.eng_null;
405 return;
406 end if;
407
408
409 --PERFORMER
410 prole := wf_engine.GetActivityAttrText(
411 itemtype => itemtype,
412 itemkey => itemkey,
413 actid => actid,
414 aname => 'PERFORMER');
415
416
417 if prole is null then
418 Wf_Core.Token('TYPE', itemtype);
419 Wf_Core.Token('ACTID', to_char(actid));
420 Wf_Core.Raise('WFENG_NOTIFICATION_PERFORMER');
421 end if;
422
423 -- message name and expand roles will be null. Get these from attributes
424 avalue := upper(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
425 actid, 'MESSAGE'));
426
427 -- let notification_send catch a missing message name.
428 expand_role := nvl(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
429 actid, 'EXPANDROLES'),'N');
430
431 -- parse out the message type if given
432 colon := instr(avalue, ':');
433 if colon = 0 then
434 msgtype := itemtype;
435 msg := avalue;
436 else
437 msgtype := substr(avalue, 1, colon - 1);
438 msg := substr(avalue, colon + 1);
439 end if;
440
441
442 -- Actually send the notification
443 Wf_Engine_Util.Notification_Send(itemtype, itemkey, actid,
444 msg, msgtype, prole, expand_role,
445 resultout);
446
447 notid:= Wf_Engine.g_nid ;
448
449 exception
450 when others then
451 Wf_Core.Context(gv_package, 'Notify', itemtype,
452 itemkey, to_char(actid), funcmode);
453 raise;
454 end Notify;
455
456 procedure notify_appraisee_or_appraiser(itemtype in varchar2,
457 itemkey in varchar2,
458 actid in number,
459 funcmode in varchar2,
460 resultout in out nocopy varchar2)
461 is
462 --local variables
463 ignore_notfound boolean default true;
464
465 begin
466
467 if(funcmode=wf_engine.eng_run) then
468 --check if we need to notify Main Appraiser,
469 -- this will be the only mode this function will
470 -- run the first time. On all other occassions it is
471 -- in notified state and need to complete using the proper result code
472 -- get the item attribute value , HR_BLOCK_ATTR
473 if(wf_engine.getitemattrtext(itemtype,itemkey,'HR_BLOCK_ATTR',ignore_notfound)='N') then
474 resultout:='HR_MAIN_APPRAISER';
475 -- now reset item attribute so that next pass will block this activity
476 wf_engine.setitemattrtext(itemtype,itemkey,'HR_BLOCK_ATTR','Y');
477 -- check the item attribute 'HR_MAIN_APPRAISER_USERNAME' exists
478 -- do we need make the check ???
479 else
480 resultout:=wf_engine.eng_notified;
481 -- update the item attribute with the current activity id to
482 -- be completed from external java or pl/sql program.
483 -- HR_APPRAI_MAIN_BLOCK_ID_ATTR
484 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_APPRAI_MAIN_BLOCK_ID_ATTR',actid);
485 end if;
486 end if;
487
488
489
490
491 exception
492 when others then
493 Wf_Core.Context(gv_package, '.notify_appraisee_or_appraiser', itemtype,
494 itemkey, to_char(actid), funcmode);
495 raise;
496 end notify_appraisee_or_appraiser;
497 procedure reset_appr_ntf_status(itemtype in varchar2,
498 itemkey in varchar2,
499 actid in number,
500 funcmode in varchar2,
501 resultout in out nocopy varchar2)
502 is
503 --local variables
504
505 begin
506 -- Do nothing in cancel or timeout mode
507 if (funcmode <> wf_engine.eng_run) then
508 resultout := wf_engine.eng_null;
509 return;
510 end if;
511
512
513 --resultout := 'NOTIFIED';
514
515 exception
516 when others then
517 Wf_Core.Context(gv_package, '.reset_appr_ntf_status', itemtype,
518 itemkey, to_char(actid), funcmode);
519 raise;
520 end reset_appr_ntf_status;
521 procedure block(itemtype in varchar2,
522 itemkey in varchar2,
523 actid in number,
524 funcmode in varchar2,
525 resultout in out nocopy varchar2)
526 is
527 --local variables
528
529 begin
530 -- Do nothing in cancel or timeout mode
531 if (funcmode <> wf_engine.eng_run) then
532 resultout := wf_engine.eng_null;
533 return;
534 end if;
535
536
537 --resultout := 'NOTIFIED';
538
539 exception
540 when others then
541 Wf_Core.Context(gv_package, 'block', itemtype,
542 itemkey, to_char(actid), funcmode);
543 raise;
544 end block;
545 procedure find_next_participant(itemtype in varchar2,
546 itemkey in varchar2,
547 actid in number,
548 funcmode in varchar2,
549 resultout in out nocopy varchar2)
550 is
551 --local variables
552 lv_participants_list wf_item_attribute_values.text_value%type default '';
553 ln_particpant_person_id varchar2(100);
554 lv_particpant_user_name varchar2(320);
555 lv_particpant_display_name varchar2(360);
556 check_sep number;
557 ignore_notfound boolean default true;
558 test number;
559
560 begin
561 -- test mode
562 test := wf_engine.GetItemAttrNumber(itemtype,itemkey,'COUNTER',ignore_notfound);
563 -- update the counter
564 wf_engine.SetItemAttrNumber(itemtype,itemkey,'COUNTER',test+1);
565 if(test >10) then
566 resultout := 'COMPLETE:F';
567 return;
568 end if;
569
570 -- end test mode
571 -- Do nothing in cancel or timeout mode
572 if (funcmode <> wf_engine.eng_run) then
573 resultout := wf_engine.eng_null;
574 return;
575 end if;
576
577 if(funcmode=wf_engine.eng_run) then
578
579 -- get the list HR_APPRA_PARTIC_LIST_ID_ATTR
580 lv_participants_list := wf_engine.GetItemAttrText(itemtype,itemkey,'HR_APPRA_PARTIC_LIST_ID_ATTR',ignore_notfound);
581
582
583 -- check if the list is empty
584 if((lv_participants_list is null) or lv_participants_list='') then
585 -- we have reached end of list
586 resultout := 'COMPLETE:F';
587 return;
588 end if;
589
590 -- check if the value has any delimiter in it
591 select instr(lv_participants_list,hr_general_utilities.g_separator) into check_sep from dual;
592 if(check_sep=0) then
593 -- no seperator(delimiter) found
594 ln_particpant_person_id := lv_participants_list;
595 lv_participants_list := null;
596 else
597 -- reset the list
598 ln_particpant_person_id := substr(lv_participants_list,1,instr(lv_participants_list,hr_general_utilities.g_separator)-1);
599 lv_participants_list := substr(lv_participants_list,instr(lv_participants_list,hr_general_utilities.g_separator)+length(hr_general_utilities.g_separator));
600 end if;
601 -- get the role details for the participant and set the item attributes
602 wf_directory.getrolename(g_orig_system,ln_particpant_person_id,lv_particpant_user_name,lv_particpant_display_name);
603 -- set the details to the performer for the participant
604 -- HR_APPRA_PARTIC_USER_NAME_ATTR and HR_APPRA_PARTIC_DISP_NAME_ATTR , HR_APPRA_PARTICP_PER_ID_ATTR
605 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTIC_USER_NAME_ATTR',lv_particpant_user_name);
606 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTIC_DISP_NAME_ATTR',lv_particpant_display_name);
607 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTICP_PER_ID_ATTR',ln_particpant_person_id);
608
609 -- update the lv_participants_list into item attribute HR_APPRA_PARTIC_LIST_ID_ATTR
610 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTIC_LIST_ID_ATTR',lv_participants_list);
611 resultout := 'COMPLETE:T';
612
613 end if;
614 exception
615 when others then
616 Wf_Core.Context(gv_package, '.find_next_participant', itemtype,
617 itemkey, to_char(actid), funcmode);
618 raise;
619 end find_next_participant;
620
621 procedure branch_on_participant_type(itemtype in varchar2,
622 itemkey in varchar2,
623 actid in number,
624 funcmode in varchar2,
625 resultout in out nocopy varchar2)
626 is
627 --local variables
628 ln_participant_person_id wf_item_attribute_values.number_value%type ;
629 lv_particpant_user_name varchar2(320);
630 lv_particpant_display_name varchar2(360);
631 lv_participant_type per_participants.participation_type%type;
632 ln_participant_id per_participants.participant_id%type;
633 ln_appraisal_id number;
634 type_value varchar2(30);
635 begin
636 -- Do nothing in cancel or timeout mode
637 if (funcmode <> wf_engine.eng_run) then
638 resultout := wf_engine.eng_null;
639 return;
640 end if;
641
642 if(funcmode=wf_engine.eng_run) then
643 -- get the Appraisal id
644 ln_appraisal_id:= wf_engine.getitemattrNumber(itemtype,itemkey,'APPRAISAL_ID');
645 -- get the partcipant id
646 ln_participant_id := wf_engine.getitemattrNumber(itemtype,itemkey,'HR_APPRA_PARTICP_ID_ATTR');
647 --ln_participant_person_id := wf_engine.getitemattrNumber(itemtype,itemkey,'HR_APPRA_PARTICP_PER_ID_ATTR');
648
649 if(ln_appraisal_id is null) then
650 raise g_invalid_appraisal_id;
651 elsif (ln_participant_id is null) then
652 raise g_invalid_participant_id;
653 end if;
654 -- participant person id and type from per_participants
655 -- for the given appraisal id
656 select person_id,participation_type
657 into ln_participant_person_id,lv_participant_type
658 from per_participants
659 where participation_in_id = ln_appraisal_id
660 and participant_id=ln_participant_id;
661
662 -- get the role details for the participant and set the item attributes
663 wf_directory.getrolename(g_orig_system,ln_participant_person_id,lv_particpant_user_name,lv_particpant_display_name);
664 -- set the details to the performer for the participant
665 -- HR_APPRA_PARTIC_USER_NAME_ATTR and HR_APPRA_PARTIC_DISP_NAME_ATTR , HR_APPRA_PARTICP_PER_ID_ATTR
666 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTIC_USER_NAME_ATTR',lv_particpant_user_name);
667 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTIC_DISP_NAME_ATTR',lv_particpant_display_name);
668 wf_engine.setitemattrtext(itemtype,itemkey,'HR_APPRA_PARTICP_PER_ID_ATTR',ln_participant_person_id);
669
670 -- HR_APPRA_APPRAISER,HR_APPRA_OTHER_PARTICP,HR_APPRA_REVIEWER
671 -- MAINAP REVIEWER GROUPAPPRAISER OTHERPARTICIPANT
672
673 if(lv_participant_type='GROUPAPPRAISER') then
674 type_value := 'HR_APPRA_APPRAISER';
675 elsif(lv_participant_type='REVIEWER') then
676 type_value := 'HR_APPRA_REVIEWER';
677 elsif(lv_participant_type='OTHERPARTICIPANT') then
678 type_value := 'HR_APPRA_OTHER_PARTICP';
679 end if;
680
681 end if;
682
683 resultout := 'COMPLETE:'||type_value;
684
685
686
687 exception
688 when others then
689 Wf_Core.Context(gv_package, '.branch_on_participant_type', itemtype,
690 itemkey, to_char(actid), funcmode);
691 raise;
692 end branch_on_participant_type;
693
694
695 procedure participants_block
696 (itemtype in varchar2
697 ,itemkey in varchar2
698 ,actid in number
699 ,funmode in varchar2
700 ,result in out nocopy varchar2)
701 is
702 --local variables
703
704 begin
705 -- Do nothing in cancel or timeout mode
706 if (funmode <> wf_engine.eng_run) then
707 result := wf_engine.eng_null;
708 return;
709 end if;
710 -- set the item attribute value with the current activity id
711 -- this will be used when the participants notification is sent.
712 -- and to complete the blocked thread.
713 -- HR_APPRAI_PARTCI_BLOCK_ID_ATTR
714 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_APPRAI_PARTCI_BLOCK_ID_ATTR',actid);
715 WF_STANDARD.BLOCK(itemtype,itemkey,actid,funmode,result);
716
717 --resultout := 'NOTIFIED';
718
719 exception
720 when others then
721 Wf_Core.Context(gv_package, '.participants_block', itemtype,
722 itemkey, to_char(actid), funmode);
723 raise;
724 end participants_block;
725
726 procedure approvals_block
727 (itemtype in varchar2
728 ,itemkey in varchar2
729 ,actid in number
730 ,funmode in varchar2
731 ,result in out nocopy varchar2)
732 is
733 --local variables
734
735 begin
736 -- Do nothing in cancel or timeout mode
737 if (funmode <> wf_engine.eng_run) then
738 result := wf_engine.eng_null;
739 return;
740 end if;
741 -- set the item attribute value with the current activity id
742 -- this will be used when the participants notification is sent.
743 -- and to complete the blocked thread.
744 -- HR_APPRAI_PARTCI_BLOCK_ID_ATTR
745
746 if not hr_workflow_service.item_attribute_exists
747 (p_item_type => itemtype
748 ,p_item_key => itemkey
749 ,p_name => 'HR_COMPETENCE_ENHANCEMENT_SS') then
750 -- the item attribute does not exist so create it
751 wf_engine.additemattr
752 (itemtype => itemtype
753 ,itemkey => itemkey
754 ,aname => 'HR_COMPETENCE_ENHANCEMENT_SS');
755 end if;
756 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_APPRAI_APPR_BLOCK_ID_ATTR',actid);
757 WF_STANDARD.BLOCK(itemtype,itemkey,actid,funmode,result);
758
759 --resultout := 'NOTIFIED';
760
761 exception
762 when others then
763 Wf_Core.Context(gv_package, '.approvals_block', itemtype,
764 itemkey, to_char(actid), funmode);
765 raise;
766 end approvals_block;
767
768 procedure appraisee_or_appraiser_block
769 (itemtype in varchar2
770 ,itemkey in varchar2
771 ,actid in number
772 ,funmode in varchar2
773 ,result in out nocopy varchar2)
774 is
775 --local variables
776 ignore_notfound boolean default true;
777
778 begin
779
780 if(funmode=wf_engine.eng_run) then
781 result:=wf_engine.eng_notified;
782 -- update the item attribute with the current activity id to
783 -- be completed from external java or pl/sql program.
784 -- HR_APPRAI_MAIN_BLOCK_ID_ATTR
785 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_APPRAI_MAIN_BLOCK_ID_ATTR',actid);
786 end if;
787 exception
788 when others then
789 Wf_Core.Context(gv_package, '.appraisee_or_appraiser_block', itemtype,
790 itemkey, to_char(actid), funmode);
791 raise;
792 end appraisee_or_appraiser_block;
793
794
795 procedure getApprovalBlockId (p_itemType in VARCHAR2
796 ,p_itemKey in VARCHAR2
797 ,p_blockId OUT NOCOPY NUMBER)
798 is
799 lv_procedure_name varchar2(30) default 'getPageDetails';
800 ln_appr_main_block_id number;
801 ln_appr_particp_block_id number;
802 ln_appr_approval_block_id number;
803
804 begin
805 ln_appr_main_block_id := wf_engine.getitemattrnumber(getApprovalBlockId.p_itemType,getApprovalBlockId.p_itemKey,'HR_APPRAI_MAIN_BLOCK_ID_ATTR');
806 ln_appr_particp_block_id:= wf_engine.getitemattrnumber(getApprovalBlockId.p_itemType,getApprovalBlockId.p_itemKey,'HR_APPRAI_PARTCI_BLOCK_ID_ATTR');
807 begin
808 if(hr_utility.debug_enabled) then
809 -- write debug statements
810 hr_utility.set_location('Querying WF_ITEM_ACTIVITY_STATUSES for notified activity:'||lv_procedure_name||'with itemtype:', 3);
811 end if;
812
813 SELECT process_activity
814 into ln_appr_approval_block_id
815 FROM WF_ITEM_ACTIVITY_STATUSES IAS
816 WHERE ias.item_type = p_itemType
817 and ias.item_key = p_itemKey
818 and ias.activity_status = 'NOTIFIED'
819 and ias.process_activity not in
820 (getApprovalBlockId.ln_appr_main_block_id,getApprovalBlockId.ln_appr_particp_block_id);
821
822 exception
823 when no_data_found then
824 if(hr_utility.debug_enabled) then
825 -- write debug statements
826 hr_utility.set_location('no notified activity found in WF_ITEM_ACTIVITY_STATUSES for itemtype:'|| p_itemType||' and item key:'||p_itemType, 4);
827 end if;
828 ln_appr_approval_block_id := null;
829 when others then
830 ln_appr_approval_block_id := null;
831 end;
832
833 begin
834 -- finally if ln_appr_approval_block_id is null check if we have notified activities
835 if(ln_appr_approval_block_id is null) then
836 SELECT process_activity
837 into ln_appr_approval_block_id
838 FROM WF_ITEM_ACTIVITY_STATUSES IAS
839 WHERE ias.item_type = p_itemType
840 and ias.item_key = p_itemKey
841 and ias.activity_status = 'NOTIFIED'
842 and ias.notification_id is not null;
843 end if;
844 exception
845 when no_data_found then
846 wf_core.Context(gv_package, '.getApprovalBlockId', p_itemtype, p_itemkey);
847 hr_utility.trace(' exception in '||gv_package||'.getApprovalBlockId : ' || sqlerrm);
848 when others then
849 raise;
850 end;
851 p_blockId := ln_appr_approval_block_id;
852 exception
853 when others then
854 wf_core.Context(gv_package, '.getApprovalBlockId', p_itemtype, p_itemkey);
855 hr_utility.trace(' exception in '||gv_package||'.getApprovalBlockId : ' || sqlerrm);
856 raise;
857 end getApprovalBlockId;
858
859
860
861 PROCEDURE reset_main_appraiser
862 ( p_itemtype in varchar2
863 , p_itemkey in varchar2
864 , p_actid in number
865 , p_funcmode in varchar2
866 , p_result in out nocopy varchar2
867 )
868 is
869 -- local variables
870 l_appraisal_id per_appraisals.appraisal_id%type;
871 l_main_appraiser_id per_appraisals.main_appraiser_id%type;
872 l_role_name varchar2(320);
873 l_role_displayname varchar2(360);
874
875 begin
876 hr_utility.set_location('Entered:'|| gv_package || '.reset_main_appraiser', 1);
877 -- get the appraisal_id from the item attribute , APPRAISAL_ID
878 l_appraisal_id:= wf_engine.GetItemAttrNumber (itemtype => p_itemtype ,
879 itemkey => p_itemkey ,
880 aname => 'APPRAISAL_ID',
881 ignore_notfound=>true);
882 -- query the other details from per_appraisals for the given l_appraisal_id
883 -- check if l_appraisal_id is null, if null throw an error
884 if(l_appraisal_id is not null) then
885 select MAIN_APPRAISER_ID
886 into l_main_appraiser_id
887 from per_appraisals
888 where APPRAISAL_ID=l_appraisal_id;
889 else
890 raise g_invalid_appraisal_id;
891 end if;
892
893 -- initialize the item attributes
894 -- HR_MAIN_APPRAISER
895 hr_workflow_service.create_hr_directory_services
896 (p_item_type => p_itemtype
897 ,p_item_key => p_itemkey
898 ,p_service_name => 'HR_MAIN_APPRAISER'
899 ,p_service_person_id => l_main_appraiser_id);
900 -- reset the owner for the wf transaction
901 wf_directory.getRoleName(p_orig_system => 'PER'
902 ,p_orig_system_id => l_main_appraiser_id
903 ,p_name => l_role_name
904 ,p_display_name => l_role_displayname);
905
906 -- ---------------------------------------------------
907 -- Set the Item Owner
908 -- ---------------------------------------------------
909 wf_engine.setItemOwner(itemtype => p_itemtype
910 ,itemkey => p_itemkey
911 ,owner => l_role_name);
912 --CREATOR_PERSON
913 hr_workflow_service.create_hr_directory_services
914 (p_item_type => p_itemtype
915 ,p_item_key => p_itemkey
916 ,p_service_name => 'CREATOR_PERSON'
917 ,p_service_person_id => l_main_appraiser_id);
918 --
919
920 p_result := wf_engine.eng_trans_default;
921
922
923
924 hr_utility.set_location('Leaving:'|| gv_package || '.reset_main_appraiser', 10);
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 wf_core.Context(gv_package, '.reset_main_appraiser', p_itemtype, p_itemkey, p_actid, p_funcmode);
929 hr_utility.trace(' exception in '||gv_package||'.reset_main_appraiser : ' || sqlerrm);
930 raise;
931
932 end reset_main_appraiser;
933
934 PROCEDURE commit_transaction
935 ( p_itemtype in varchar2
936 , p_itemkey in varchar2
937 , p_actid in number
938 , p_funcmode in varchar2
939 , p_result in out nocopy varchar2
940 )
941 is
942 -- local variables
943 l_appraisal_id per_appraisals.appraisal_id%type;
944 lv_result varchar2(3);
945 begin
946 hr_utility.set_location('Entered:'|| gv_package || '.commit_transaction', 1);
947 hr_utility.set_location('calling hr_complete_appraisal_ss.complete_appr', 2);
948 hr_complete_appraisal_ss.complete_appr(p_itemtype,p_itemkey,lv_result);
949 hr_utility.set_location('returned from hr_complete_appraisal_ss.complete_appr with result:'
950 ||lv_result, 3);
951 /*
952 E : Error -- ntf to MA, HR/Sysadmin
953 W : Warning -- ntf MA
954 S: Success
955 */
956 if(lv_result='S' or lv_result='W') then
957 p_result:= 'COMPLETE:SUCCESS';
958 else
959 p_result:= 'COMPLETE:APPLICATION_ERROR';
960 end if;
961 hr_utility.set_location('Leaving:'|| gv_package || '.commit_transaction', 10);
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 wf_core.Context(gv_package, '.commit_transaction', p_itemtype, p_itemkey, p_actid, p_funcmode);
966 hr_utility.trace(' exception in '||gv_package||'.commit_transaction : ' || sqlerrm);
967 raise;
968
969 end commit_transaction;
970
971
972
973 PROCEDURE update_appraisal_system_status
974 ( p_itemtype in varchar2
975 , p_itemkey in varchar2
976 ,p_status in varchar2
977 )
978 is
979 -- local variables
980 l_appraisal_id per_appraisals.appraisal_id%type;
981 l_appraiser_person_id per_appraisals.appraiser_person_id%type;
982 l_object_version_number per_appraisals.object_version_number%type;
983 l_system_params per_appraisals.system_params%type;
984
985 begin
986 hr_utility.set_location('Entered:'|| gv_package || '.update_appraisal_system_status', 1);
987 -- get the appraisal_id from the item attribute , APPRAISAL_ID
988 if(hr_utility.debug_enabled) then
989 -- write debug statements
990 hr_utility.set_location('Calling wf_engine.GetItemAttrNumber for APPRAISAL_ID with itemtype:itemkey '||p_itemtype||':'||p_itemkey,2);
991 end if;
992 l_appraisal_id:= wf_engine.GetItemAttrNumber (itemtype => p_itemtype ,
993 itemkey => p_itemkey ,
994 aname => 'APPRAISAL_ID',
995 ignore_notfound=>true);
996 begin
997 -- get the required data from the per_appraisals for the update
998 select appraiser_person_id, object_version_number,system_params
999 into l_appraiser_person_id,l_object_version_number,l_system_params
1000 from per_appraisals
1001 where appraisal_id=l_appraisal_id;
1002 l_system_params := replace(l_system_params,'&pItemKey=' || p_itemkey ,'');
1003 exception
1004 when others then
1005 raise;
1006 end;
1007 -- call the api to update the system status
1008 if(hr_utility.debug_enabled) then
1009 -- write debug statements
1010 hr_utility.set_location('Calling hr_appraisals_api.update_appraisal with p_appraisal_id:
1011 p_object_version_number:p_appraiser_person_id:p_appraisal_system_status '
1012 || l_appraisal_id||':'||l_object_version_number||':'
1013 ||l_appraiser_person_id||p_status,3);
1014 end if;
1015
1016 hr_appraisals_api.update_appraisal(p_effective_date=>trunc(sysdate),
1017 p_appraisal_id=>l_appraisal_id,
1018 p_object_version_number=>l_object_version_number,
1019 p_appraiser_person_id=>l_appraiser_person_id,
1020 p_appraisal_system_status=>p_status --7210916 Bug Fix ,
1021 --p_system_params => l_system_params
1022 );
1023
1024 hr_utility.set_location('Leaving:'|| gv_package || '.update_appraisal_system_status', 10);
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 wf_core.Context(gv_package, '.update_appraisal_system_status', p_itemtype, p_itemkey);
1029 hr_utility.trace(' exception in '||gv_package||'.update_appraisal_system_status : ' || sqlerrm);
1030 raise;
1031
1032 end update_appraisal_system_status;
1033
1034 PROCEDURE set_appraisal_rfc_status
1035 ( p_itemtype in varchar2
1036 , p_itemkey in varchar2
1037 , p_actid in number
1038 , p_funcmode in varchar2
1039 , p_result in out nocopy varchar2
1040 )
1041 is
1042 -- local variables
1043
1044 begin
1045 hr_utility.set_location('Entered:'|| gv_package || '.set_appraisal_rfc_status', 1);
1046
1047 -- call the update_appraisal_system_status with proper status
1048 update_appraisal_system_status(p_itemtype=>p_itemtype, p_itemkey=>p_itemkey,p_status=>'RFC');
1049
1050 hr_utility.set_location('Leaving:'|| gv_package || '.set_appraisal_rfc_status', 10);
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 wf_core.Context(gv_package, '.set_appraisal_rfc_status', p_itemtype, p_itemkey, p_actid, p_funcmode);
1055 hr_utility.trace(' exception in '||gv_package||'.set_appraisal_rfc_status : ' || sqlerrm);
1056 raise;
1057
1058 end set_appraisal_rfc_status;
1059
1060 PROCEDURE set_appraisal_reject_status (
1061 p_itemtype IN VARCHAR2,
1062 p_itemkey IN VARCHAR2,
1063 p_actid IN NUMBER,
1064 p_funcmode IN VARCHAR2,
1065 p_result IN OUT NOCOPY VARCHAR2
1066 )
1067 IS
1068 CURSOR get_txn_id
1069 IS
1070 SELECT transaction_id
1071 FROM hr_api_transactions
1072 WHERE item_type = p_itemtype AND item_key = p_itemkey;
1073
1074 -- local variables
1075 notification_rec ame_util2.notificationrecord;
1076 l_current_forward_to_username wf_users.NAME%TYPE;
1077 l_is_parallel VARCHAR2 (10) DEFAULT NULL;
1078 original_forward_to_user wf_users.NAME%TYPE;
1079 l_role_name wf_users.NAME%TYPE;
1080 ntfidstartpos NUMBER DEFAULT NULL;
1081 startpos NUMBER DEFAULT NULL;
1082 endpos NUMBER DEFAULT NULL;
1083 prll_ntf VARCHAR2 (600) DEFAULT NULL;
1084 l_txn_id NUMBER;
1085 l_appr_list_clear_on_reject VARCHAR2 (3) := 'Y';
1086 BEGIN
1087 hr_utility.set_location ('Entered:' || gv_package || '.set_appraisal_reject_status', 1);
1088 -- call the update_appraisal_system_status with proper status
1089 update_appraisal_system_status (p_itemtype => p_itemtype, p_itemkey => p_itemkey, p_status => 'ONGOING');
1090
1091 --- Added for fixing bug#11661757
1092 OPEN get_txn_id;
1093
1094 FETCH get_txn_id
1095 INTO l_txn_id;
1096
1097 CLOSE get_txn_id;
1098
1099 l_appr_list_clear_on_reject :=
1100 wf_engine.getitemattrtext (itemtype => p_itemtype,
1101 itemkey => p_itemkey,
1102 aname => 'HR_APPR_LIST_CLEAR_ON_REJECT',
1103 ignore_notfound => TRUE
1104 );
1105
1106 IF (l_txn_id IS NOT NULL AND NVL (l_appr_list_clear_on_reject, 'N') = 'Y')
1107 THEN
1108 ame_api2.clearallapprovals (800, 'SSHRMS', l_txn_id);
1109 END IF;
1110
1111 -- end changes for bug#11661757
1112
1113 -- set the our param as *
1114 p_result := 'COMPLETE:*';
1115 wf_engine.setitemattrtext (p_itemtype, p_itemkey, 'TRAN_SUBMIT', 'N');
1116 notification_rec.notification_id :=
1117 wf_engine.getitemattrnumber (itemtype => p_itemtype, itemkey => p_itemkey,
1118 aname => 'HR_CONTEXT_NID_ATTR');
1119 l_is_parallel :=
1120 wf_engine.getitemattrtext (itemtype => p_itemtype,
1121 itemkey => p_itemkey,
1122 aname => 'HR_PARALLEL_ATTR',
1123 ignore_notfound => TRUE
1124 );
1125
1126 IF (l_is_parallel = 'PARALLEL')
1127 THEN
1128 IF (notification_rec.notification_id IS NOT NULL)
1129 THEN
1130 SELECT original_recipient
1131 INTO original_forward_to_user
1132 FROM wf_notifications
1133 WHERE notification_id = notification_rec.notification_id;
1134
1135 prll_ntf := wf_engine.getitemattrtext (p_itemtype, p_itemkey, 'PRLL_TRNSFR_DET', TRUE);
1136
1137 IF (INSTR (prll_ntf, notification_rec.notification_id) > 0)
1138 THEN
1139 ntfidstartpos := INSTR (prll_ntf, notification_rec.notification_id, 1, 1);
1140 startpos := INSTR (prll_ntf, ':', ntfidstartpos, 1);
1141 endpos := INSTR (prll_ntf, '|', ntfidstartpos, 1);
1142 original_forward_to_user := SUBSTR (prll_ntf, startpos + 1, endpos - startpos - 1);
1143 END IF;
1144
1145 l_current_forward_to_username := original_forward_to_user;
1146 wf_engine.setitemattrtext (p_itemtype,
1147 p_itemkey,
1148 'FORWARD_TO_DISPLAY_NAME',
1149 wf_directory.getroledisplayname (l_current_forward_to_username)
1150 );
1151 END IF;
1152 END IF;
1153
1154 hr_utility.set_location ('Leaving:' || gv_package || '.set_appraisal_reject_status', 10);
1155 EXCEPTION
1156 WHEN OTHERS
1157 THEN
1158 wf_core.CONTEXT (gv_package, '.set_appraisal_reject_status', p_itemtype, p_itemkey, p_actid, p_funcmode);
1159 hr_utility.TRACE (' exception in ' || gv_package || '.set_appraisal_reject_status : ' || SQLERRM);
1160 RAISE;
1161 END set_appraisal_reject_status;
1162
1163
1164
1165 PROCEDURE notify_appraisee_on_completion
1166 ( p_itemtype in varchar2
1167 , p_itemkey in varchar2
1168 , p_actid in number
1169 , p_funcmode in varchar2
1170 , p_result in out nocopy varchar2
1171 )
1172 is
1173 -- local variables
1174
1175 begin
1176 hr_utility.set_location('Entered:'|| gv_package || '.notify_appraisee_on_completion', 1);
1177 --bug 4403850, to support old appraisals on upgrade, set the appraisal system status to COMPLETED,
1178 -- if APPRFEEDBACK
1179 appraisee_commit_aft_feedback(p_itemtype, p_itemkey, p_actid, p_funcmode, p_result);
1180 hr_complete_appraisal_ss.send_notification(p_itemtype,
1181 p_itemkey,
1182 p_result);
1183 hr_utility.set_location('Leaving:'|| gv_package || '.set_appraisal_commit_status', 10);
1184
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 wf_core.Context(gv_package, '.notify_appraisee_on_completion', p_itemtype, p_itemkey, p_actid, p_funcmode);
1189 hr_utility.trace(' exception in '||gv_package||'.notify_appraisee_on_completion : ' || sqlerrm);
1190 raise;
1191
1192 end notify_appraisee_on_completion;
1193
1194
1195 procedure build_ma_compl_log_msg(document_id IN Varchar2,
1196 display_type IN Varchar2,
1197 document IN OUT NOCOPY varchar2,
1198 document_type IN OUT NOCOPY Varchar2) is
1199 c_proc varchar2(30) default 'build_ma_compl_log_msg';
1200 lv_item_type wf_item_activity_statuses.item_type%type;
1201 lv_item_key wf_item_activity_statuses.item_key%type;
1202 begin
1203 g_debug := hr_utility.debug_enabled;
1204 if g_debug then
1205 hr_utility.set_location('Entering:'|| gv_package||'.'||c_proc, 1);
1206 end if;
1207 -- get the itemtype and item key for the notification id
1208 hr_workflow_ss.get_item_type_and_key(document_id,lv_item_type,lv_item_key);
1209 -- build the log message, the log is restricted to 32k
1210 -- assumed the code setting the item attributes has the necessary format.
1211 document := wf_engine.GetItemAttrText(lv_item_type,
1212 lv_item_key,
1213 hr_complete_appraisal_ss.gv_upd_appr_status_log,
1214 true)
1215 ||wf_engine.GetItemAttrText(lv_item_type,
1216 lv_item_key,
1217 hr_complete_appraisal_ss.gv_apply_asses_comps_log,
1218 true)
1219 ||wf_engine.GetItemAttrText(lv_item_type,
1220 lv_item_key,
1221 hr_complete_appraisal_ss.gv_create_event_log,
1222 true)
1223 || wf_engine.GetItemAttrText(lv_item_type,
1224 lv_item_key,
1225 hr_complete_appraisal_ss.gv_upd_trn_act_status_log ,
1226 true);
1227
1228 -- set the document type
1229 document_type := wf_notification.doc_html;
1230
1231 if g_debug then
1232 hr_utility.set_location('Leaving:'|| gv_package||'.'||c_proc, 30);
1233 end if;
1234
1235 exception
1236 when others then
1237 document := null;
1238 document_type :=null;
1239 hr_utility.set_location('hr_appraisal_workflow_ss.build_ma_compl_log_msg errored : '
1240 ||SQLERRM ||' '||to_char(SQLCODE), 20);
1241 Wf_Core.Context('hr_workflow_ss', 'build_ma_compl_log_msg',
1242 document_id, display_type);
1243 raise;
1244 end build_ma_compl_log_msg;
1245
1246
1247 FUNCTION isAppraiseeFeebackAllowed
1248 (p_appraisal_id IN number) RETURN VARCHAR2
1249 IS
1250 l_provide_feedback PER_APPRAISALS.provide_overall_feedback%TYPE;
1251
1252 BEGIN
1253 SELECT NVL(appr.provide_overall_feedback,'N')
1254 INTO l_provide_feedback
1255 from per_appraisals appr
1256 where appr.appraisal_id = p_appraisal_id;
1257
1258 RETURN l_provide_feedback;
1259
1260 END isAppraiseeFeebackAllowed;
1261
1262 PROCEDURE appraisee_feedback_allowed
1263 ( p_itemtype in varchar2
1264 , p_itemkey in varchar2
1265 , p_actid in number
1266 , p_funcmode in varchar2
1267 , p_result in out nocopy varchar2
1268 )
1269 is
1270 -- local variables
1271 l_appraisal_id per_appraisals.appraisal_id%type;
1272
1273 l_log varchar2(4000);
1274 lv_chg_appr_status_log wf_item_attributes.text_default%TYPE;
1275 chg_appr_status varchar2(2);
1276 begin
1277 hr_utility.set_location('Entered:'|| gv_package || '.appraisee_feedback_allowed', 1);
1278
1279 l_appraisal_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype ,
1280 itemkey => p_itemkey ,
1281 aname => 'APPRAISAL_ID',
1282 ignore_notfound=>true);
1283
1284 if l_appraisal_id is null then
1285 l_log := l_log || 'Error: No Appraisal Id for this WorkFlow Transaction';
1286 hr_utility.trace(l_log);
1287 end if;
1288
1289
1290 hr_utility.trace('calling isAppraiseeFeebackAllowed');
1291 chg_appr_status := isAppraiseeFeebackAllowed(p_appraisal_id => l_appraisal_id);
1292 hr_utility.trace('returned from isAppraiseeFeebackAllowed with result:'
1293 ||chg_appr_status);
1294
1295 if ( chg_appr_status = 'Y' ) then
1296 p_result := 'COMPLETE:'||'Y' ; -- TBD shud be made 'Y' for testing
1297 else
1298 p_result := 'COMPLETE:'||'N' ;
1299 end if;
1300
1301
1302 hr_utility.set_location('Exiting:'|| gv_package || '.appraisee_feedback_allowed', 1);
1303
1304
1305 exception
1306 when others then
1307 wf_core.Context(gv_package,'.appraisee_feedback_allowed',p_itemtype, p_itemkey
1308 , p_actid,p_funcmode );
1309 hr_utility.trace('Exception in ' || gv_package || '.appraisee_feedback_allowed' ||
1310 sqlerrm );
1311 raise ;
1312 end appraisee_feedback_allowed;
1313
1314 PROCEDURE appraisee_commit_aft_feedback
1315 ( p_itemtype in varchar2
1316 , p_itemkey in varchar2
1317 , p_actid in number
1318 , p_funcmode in varchar2
1319 , p_result in out nocopy varchar2
1320 )
1321 is
1322 -- local variables
1323 l_appraisal_id per_appraisals.appraisal_id%type;
1324
1325 l_log varchar2(4000);
1326 lv_chg_appr_status_log wf_item_attributes.text_default%TYPE;
1327 chg_appr_status varchar2(2);
1328 begin
1329 hr_utility.set_location('Entered:'|| gv_package || '.appraisee_commit_aft_feedback', 1);
1330
1331 l_appraisal_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype ,
1332 itemkey => p_itemkey ,
1333 aname => 'APPRAISAL_ID',
1334 ignore_notfound=>true);
1335
1336 if l_appraisal_id is null then
1337 l_log := l_log || 'No Appraisal Id for this WorkFlow Transaction';
1338 --raise ; -- Should raise an Exception ?????
1339 end if;
1340
1341 hr_utility.set_location('calling hr_complete_appraisal_ss.change_appr_status', 2);
1342 hr_complete_appraisal_ss.change_appr_status(l_appraisal_id, p_itemtype,p_itemkey,
1343 lv_chg_appr_status_log,chg_appr_status);
1344
1345
1346 p_result := 'COMPLETE:SUCCESS';
1347 hr_utility.set_location('Exiting:'|| gv_package || '.appraisee_commit_aft_feedback', 1);
1348
1349 exception
1350 when others then
1351 wf_core.Context(gv_package,'.appraisee_commit_aft_feedback',p_itemtype, p_itemkey
1352 , p_actid,p_funcmode );
1353 hr_utility.trace('Exception in ' || gv_package || '.appraisee_commit_aft_feedback' ||
1354 sqlerrm );
1355 raise ;
1356 end appraisee_commit_aft_feedback ;
1357
1358 PROCEDURE notify_appraisee
1359 ( p_itemtype in varchar2
1360 , p_itemkey in varchar2
1361 , p_actid in number
1362 , p_funcmode in varchar2
1363 , p_result in out nocopy varchar2
1364 )
1365 is
1366 begin
1367
1368 if (p_funcmode <> wf_engine.eng_run) then
1369 p_result := wf_engine.eng_null;
1370 return;
1371 end if;
1372
1373 Notify(itemtype => p_itemtype,
1374 itemkey => p_itemkey,
1375 actid => p_actid,
1376 funcmode => p_funcmode,
1377 resultout=> p_result );
1378
1379 -- check if item attribute 'HR_APPR_NOTIF_BLOCK_ID_ATTR' already exists
1380 if not hr_workflow_service.item_attribute_exists
1381 (p_item_type => p_itemtype
1382 ,p_item_key => p_itemkey
1383 ,p_name => 'HR_APPR_NOTIF_BLOCK_ID_ATTR') then
1384 -- the item attribute does not exist so create it
1385 wf_engine.additemattr
1386 (itemtype => p_itemtype
1387 ,itemkey => p_itemkey
1388 ,aname => 'HR_APPR_NOTIF_BLOCK_ID_ATTR');
1389 end if;
1390
1391 wf_engine.setitemattrnumber(p_itemtype,p_itemkey,'HR_APPR_NOTIF_BLOCK_ID_ATTR',p_actid);
1392 WF_STANDARD.BLOCK(p_itemtype,p_itemkey,p_actid,p_funcmode,p_result); --TBD uncomment this line
1393
1394 exception
1395 when others then
1396 Wf_Core.Context(gv_package, '.notify_appraisee', p_itemtype,
1397 p_itemkey, to_char(p_actid), p_funcmode);
1398 raise;
1399 end notify_appraisee;
1400
1401 FUNCTION isAppraiser
1402 (
1403 p_notification_id in wf_notifications.item_key%type,
1404 p_loggedin_person_id in number
1405 )RETURN varchar2
1406 IS
1407 l_result varchar2(2) := 'N';
1408 l_person_id wf_roles.orig_system_id%type;
1409 BEGIN
1410
1411 select orig_system_id into l_person_id from WF_NOTIFICATIONS ,wf_roles
1412 WHERE
1413 notification_id=p_notification_id and
1414 recipient_role = name and
1415 orig_system = 'PER';
1416
1417 if (l_person_id= p_loggedin_person_id) then
1418 l_result := 'Y';
1419 end if;
1420
1421 return l_result;
1422 exception
1423 when others then
1424 return 'N';
1425
1426 END isAppraiser;
1427
1428 PROCEDURE send_emp_ntf_on_rejection (
1429 p_itemtype IN VARCHAR2,
1430 p_itemkey IN VARCHAR2,
1431 p_actid IN NUMBER,
1432 p_funcmode IN VARCHAR2,
1433 p_result OUT NOCOPY VARCHAR2
1434 )
1435 IS
1436 l_proc VARCHAR2 (100);
1437 lv_send_nft VARCHAR2 (30) := 'N';
1438
1439 BEGIN
1440 hr_utility.trace_on (null, 'KMG_TXN');
1441 l_proc := gv_package||'.send_emp_ntf_on_rejection';
1442 hr_utility.set_location('Entering:'|| l_proc, 1);
1443 lv_send_nft :=
1444 wf_engine.getitemattrtext (itemtype => p_itemtype,
1445 itemkey => p_itemkey,
1446 aname => 'HR_APPR_NTF_ON_REJ_REQD',
1447 ignore_notfound => TRUE
1448 );
1449
1450 IF lv_send_nft = 'Y'
1451 THEN
1452 hr_utility.TRACE ('In (if lv_send_nft = Y)' || l_proc);
1453 p_result := 'COMPLETE:' || 'Y';
1454 ELSE
1455 hr_utility.TRACE ('In (if lv_send_nft = N)' || l_proc);
1456 p_result := 'COMPLETE:' || 'N';
1457 END IF;
1458
1459 hr_utility.set_location ('Leaving' || l_proc, 20);
1460 hr_utility.trace_off;
1461 EXCEPTION
1462 WHEN OTHERS
1463 THEN
1464 hr_utility.set_location ('EXCEPTION' || l_proc, 555);
1465 wf_core.CONTEXT (gv_package,
1466 'send_emp_ntf_on_rejection',
1467 p_itemtype,
1468 p_itemkey,
1469 TO_CHAR (p_actid),
1470 p_funcmode
1471 );
1472 RAISE;
1473 END send_emp_ntf_on_rejection;
1474
1475
1476 end hr_appraisal_workflow_ss; -- Package body