1 PACKAGE BODY HR_SFLUTIL_SS AS
2 /* $Header: hrsflutlss.pkb 120.12.12020000.2 2012/07/04 23:56:16 amnaraya ship $ */
3
4 -- Package Variables
5 --
6 g_package constant varchar2(14) := 'hr_sflutil_ss.';
7 g_debug boolean ;
8
9 --5672792
10 function isCurrentTxnSFLClose ( p_transaction_id hr_api_transactions.transaction_id%type )
11 return varchar2
12 is
13 result varchar2(10) := null;
14 c_proc constant varchar2(50) :='isCurrentTxnSFLClose';
15 l_temp boolean;
16 p_item_type hr_api_transactions.item_type%type;
17 p_item_key hr_api_transactions.item_key%type;
18 begin
19 --hr_utility.trace_on(null,'Oracle');
20 hr_utility.set_location('p_transaction_id : ' || p_transaction_id , 0 );
21 g_debug := hr_utility.debug_enabled;
22 if g_debug then
23 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
24 end if;
25
26 begin
27 -- check if there are any SFL transaction associated
28 if g_debug then
29 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 2);
30 end if;
31
32 select item_type, item_key
33 into p_item_type,p_item_key
34 from wf_items
35 where user_key=to_char(p_transaction_id)
36 and rownum<2;
37 if g_debug then
38 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 3);
39 end if;
40 exception
41 when others then
42 -- null;
43 result := 'FALSE';
44 --hr_utility.trace_off();
45 return result;
46 end;
47
48 if HR_SFLUTIL_SS.OpenNotificationsExist(wf_engine.getitemattrnumber(p_item_type,p_item_key,'HR_LAST_SFL_NTF_ID_ATTR',true))=false then
49 if g_debug then
50 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 4);
51 end if;
52 result := 'TRUE';
53 else
54 if g_debug then
55 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 5);
56 end if;
57 result := 'FALSE';
58 end if;
59 if g_debug then
60 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 6);
61 end if;
62 return result;
63 exception
64 when others then
65 if g_debug then
66 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 7);
67 end if;
68 raise;
69 --hr_utility.trace_off();
70 end isCurrentTxnSFLClose;
71 --5672792
72
73
74 procedure sflBlock
75 (itemtype in varchar2
76 ,itemkey in varchar2
77 ,actid in number
78 ,funmode in varchar2
79 ,result in out nocopy varchar2)
80 is
81 --local variables
82 c_proc constant varchar2(8) :='sflBlock';
83 begin
84 g_debug := hr_utility.debug_enabled;
85 if g_debug then
86 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
87 end if;
88 -- Do nothing in cancel or timeout mode
89 if (funmode <> wf_engine.eng_run) then
90 result := wf_engine.eng_null;
91 return;
92 end if;
93 -- set the item attribute value with the current activity id
94 -- this will be used when the recpients notification is sent.
95 -- and to complete the blocked thread.
96 -- HR_SFL_BLOCK_ID_ATTR
97 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_SFL_BLOCK_ID_ATTR',actid);
98 wf_standard.block(itemtype,itemkey,actid,funmode,result);
99
100 if g_debug then
101 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
102 end if;
103
104 exception
105 when others then
106 Wf_Core.Context('HR_SFLUTIL_SS.sflBlock', itemtype,
107 itemkey, to_char(actid), funmode);
108 raise;
109 end sflBlock;
110
111
112
113 procedure closeSFLTransaction
114 (itemtype in varchar2
115 ,itemkey in varchar2
116 ,actid in number
117 ,funmode in varchar2
118 ,result in out nocopy varchar2)
119 is
120 --local variables
121 c_proc constant varchar2(30) :='closeSFLTransaction';
122 lv_sendNtf varchar2(4);
123
124 cursor sflNotificationsCursor is
125 select ias.notification_id notification_id
126 from wf_item_activity_statuses ias,
127 wf_notifications ntf
128 where ias.item_type = itemtype
129 and ias.item_key =itemkey
130 and ias.notification_id is not null
131 and ntf.notification_id = ias.notification_id
132 and ntf.status='OPEN'
133 union
134 select ias.notification_id notification_id
135 from wf_item_activity_statuses_h ias,
136 wf_notifications ntf
137 where ias.notification_id is not null
138 and ias.item_type = itemtype
139 and ias.item_key =itemkey
140 and ntf.notification_id = ias.notification_id
141 and ntf.status='OPEN' ;
142
143 begin
144 g_debug := hr_utility.debug_enabled;
145 if g_debug then
146 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
147 end if;
148
149 if (funmode = wf_engine.eng_run) then
150 -- check if we need to send notification or end the SFL process
151 -- HR_SEND_SFL_NTF_ATTR
152 lv_sendNtf := NVL(wf_engine.GetItemAttrText(itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'HR_SEND_SFL_NTF_ATTR',
155 ignore_notfound=>true),'N');
156 result := 'COMPLETE:'|| lv_sendNtf;
157
158 begin
159 -- close all notifications pertaining to this transaction
160 for ntfrow in sflNotificationsCursor loop
161 wf_notification.close(ntfrow.notification_id,'SYSADMIN');
162 end loop;
163 exception
164 when others then
165 -- close the cursor
166 if (sflNotificationsCursor%isopen) then
167 close sflNotificationsCursor;
168 end if;
169 end;
170
171 if g_debug then
172 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
173 end if;
174 end if;
175 exception
176 when others then
177 Wf_Core.Context('HR_SFLUTIL_SS.closeSFLTransaction', itemtype,
178 itemkey, to_char(actid), funmode);
179 raise;
180 end closeSFLTransaction;
181
182 function OpenNotificationsExist( nid in Number )
183 return Boolean is
184 --
185 dummy pls_integer;
186 --
187 begin
188 --
189 select 1
190 into dummy
191 from sys.dual
192 where exists ( select null
193 from wf_notifications
194 where notification_id = nid
195 and status = 'OPEN'
196 );
197 --
198 return(TRUE);
199 --
200 exception
201 when no_data_found then
202 --
203 return(FALSE);
204 --
205 when others then
206 --
207 wf_core.context('hr_sfl_util_ss', 'OpenNotifications', to_char(nid) );
208 raise;
209 end OpenNotificationsExist;
210
211
212
213 -- ---------------------------------------------------------------------------
214 -- public Procedure declarations
215 -- ---------------------------------------------------------------------------
216 --
217 -- ----------------------------------------------------------------------------
218 -- |------------------------------< Notify>-------------------|
219 -- ----------------------------------------------------------------------------
220 --
221 -- This procedure is a public wrapper to engine notification call
222 -- This reads the activity attributes and sends notification to the ROLE defined
223 -- in the activity attribute PERFORMER with the message conigured in the activity
224 -- attribute MESSAGE. And also can send to group if configured through the activity
225 -- attribute EXPANDROLES.
226 --
227 procedure Notify(itemtype in varchar2,
228 itemkey in varchar2,
229 actid in number,
230 funcmode in varchar2,
231 resultout in out nocopy varchar2)
232 is
233 msg varchar2(30);
234 msgtype varchar2(8);
235 prole wf_users.name%type;
236 expand_role varchar2(1);
237
238 colon pls_integer;
239 avalue varchar2(240);
240 notid pls_integer;
241 comments wf_notifications .user_comment%type;
242 document varchar2(240);
243 document_type varchar2(240);
244 ln_notification_id number;
245 begin
246 -- Do nothing in cancel or timeout mode
247 if (funcmode <> wf_engine.eng_run) then
248 resultout := wf_engine.eng_null;
249 return;
250 end if;
251
252 -- close the existing ntf id if any for this transaction
253 ln_notification_id :=wf_engine.getitemattrnumber(itemtype,itemkey,'HR_LAST_SFL_NTF_ID_ATTR',true);
254 if(ln_notification_id is not null and OpenNotificationsExist(ln_notification_id))then
255 wf_notification.close(ln_notification_id,'SYSADMIN');
256 end if;
257
258
259 --PERFORMER
260 prole := wf_engine.GetActivityAttrText(
261 itemtype => itemtype,
262 itemkey => itemkey,
263 actid => actid,
264 aname => 'PERFORMER');
265
266
267 if prole is null then
268 Wf_Core.Token('TYPE', itemtype);
269 Wf_Core.Token('ACTID', to_char(actid));
270 Wf_Core.Raise('WFENG_NOTIFICATION_PERFORMER');
271 end if;
272
273 -- message name and expand roles will be null. Get these from attributes
274 avalue := upper(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
275 actid, 'MESSAGE'));
276
277 -- let notification_send catch a missing message name.
278 expand_role := nvl(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
279 actid, 'EXPANDROLES'),'N');
280
281 -- parse out the message type if given
282 colon := instr(avalue, ':');
283 if colon = 0 then
284 msgtype := itemtype;
285 msg := avalue;
286 else
287 msgtype := substr(avalue, 1, colon - 1);
288 msg := substr(avalue, colon + 1);
289 end if;
290
291
292 -- Actually send the notification
293 Wf_Engine_Util.Notification_Send(itemtype, itemkey, actid,
294 msg, msgtype, prole, expand_role,
295 resultout);
296
297 notid:= Wf_Engine.g_nid ;
298
299 exception
300 when others then
301 Wf_Core.Context('HR_SFLUTIL_SS.Notify', itemtype,
302 itemkey, to_char(actid), funcmode);
303 raise;
304 end Notify;
305
306
307
308 procedure getSFLMsgSubject(document_id IN Varchar2,
309 display_type IN Varchar2,
310 document IN OUT NOCOPY varchar2,
311 document_type IN OUT NOCOPY Varchar2)
312 is
313 c_proc constant varchar2(30) := 'getSFLMsgSubject';
314 ln_transaction_id hr_api_transactions.transaction_id%type;
315 lv_parent_item_type wf_item_activity_statuses.item_type%type;
316 lv_parent_item_key wf_item_activity_statuses.item_key%type;
317 l_creator_person_id per_people_f.person_id%type;
318 l_creator_disp_name wf_users.display_name%type;
319 l_creator_username wf_users.name%type;
320 l_current_person_id per_people_f.person_id%type;
321 l_current_disp_name wf_users.display_name%type;
322 l_current_username wf_users.name%type;
323 lv_process_display_name wf_runnable_processes_v.display_name%type;
324 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
325 --ln_transaction_id hr_api_transactions.transaction_id%type;
326 lv_selected_func_prompt fnd_form_functions_vl.USER_FUNCTION_NAME%type;
327 lv_ntfSubMsg wf_item_attribute_values.text_value%type;
328 lv_TransCtx_xpath varchar2(20000) default 'Transaction/TransCtx';
329
330 begin
331 g_debug := hr_utility.debug_enabled;
332 if g_debug then
333 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
334 end if;
335
336 -- get the itemtype and item key for the notification id
337 if g_debug then
338 hr_utility.set_location('Calling hr_workflow_ss.get_item_type_and_key for NtfId:'||document_id, 11);
339 end if;
340 -- get the transaction id corresponding to this SFL notification, HR_SFL_TRANSACTION_REF_ID_ATTR
341 --ln_transaction_id := wf_notification.GetAttrNumber(document_id,'HR_SFL_TRANSACTION_REF_ID_ATTR');
342 ln_transaction_id := wf_notification.GetAttrNumber(document_id,'HR_TRANSACTION_REF_ID_ATTR');
343
344 --document := getSubject(ln_transaction_id,null);
345 -- set the document type
346 document_type := wf_notification.doc_html;
347 -- default ouptut
348 document :=ln_transaction_id;
349
350 if (ln_transaction_id is not null) then
351 begin
352 select * into lr_hr_api_transaction_rec from hr_api_transactions
353 where transaction_id=ln_transaction_id;
354 exception
355 when no_data_found then
356 if(hr_utility.debug_enabled) then
357 -- write debug statements
358 hr_utility.set_location('no record found for the transaction :'|| ln_transaction_id, 4);
359 end if;
360 return ;
361 when others then
362 return ;
363 end;
364 else
365 return ;
366 end if;
367
368 /*
369 begin
370 -- get the user function name
371 select USER_FUNCTION_NAME into lv_selected_func_prompt
372 from fnd_form_functions_vl fffv
373 where fffv.function_id=lr_hr_api_transaction_rec.FUNCTION_ID;
374 exception
375 when no_data_found then
376 if(hr_utility.debug_enabled) then
377 -- write debug statements
378 hr_utility.set_location('no record found in fnd_form_functions_vl for the id :'|| ln_transaction_id, 4);
379 end if;
380 when others then
381 -- fnd_message.set_name('PER', SQLERRM ||' '||to_char(SQLCODE));
382 --hr_utility.raise_error;
383 return;
384 end;
385
386 -- add the section display name
387 if(lr_hr_api_transaction_rec.section_display_name is not null) then
388 lv_selected_func_prompt:= lv_selected_func_prompt||' - ' ||lr_hr_api_transaction_rec.section_display_name;
389 end if;
390 */
391 if(lr_hr_api_transaction_rec.item_key is null) then
392 begin
393 lv_ntfSubMsg := hr_xml_util.get_node_value(ln_transaction_id,
394 'pNtfSubMsg',
395 lv_TransCtx_xpath,
396 NULL,
397 NULL,
398 NULL,
399 NULL,
400 NULL,
401 NULL,
402 NULL,
403 NULL,
404 NULL,
405 NULL,
406 NULL);
407 fnd_message.set_name('PER',lv_ntfSubMsg);
408 lv_ntfSubMsg := fnd_message.get;
409 if (lr_hr_api_transaction_rec.transaction_ref_table = 'PER_ALL_VACANCIES') then
410 lv_ntfSubMsg := lv_ntfSubMsg||' '||lr_hr_api_transaction_rec.api_addtnl_info;
411 end if;
412
413 exception
414 when others then
415 lv_ntfSubMsg := ln_transaction_id;
416 end;
417 else
418 lv_ntfSubMsg :=hr_workflow_ss.getprocessdisplayname(lr_hr_api_transaction_rec.item_type,
419 lr_hr_api_transaction_rec.item_key);
420 end if;
421
422 l_creator_person_id := lr_hr_api_transaction_rec.CREATOR_PERSON_ID;
423 l_current_person_id := lr_hr_api_transaction_rec.SELECTED_PERSON_ID;
424
425
426 if g_debug then
427 hr_utility.set_location('Creator_person_id:'||l_creator_person_id,15);
428 hr_utility.set_location('Current_person_id:'||l_current_person_id,16);
429 end if;
430 if g_debug then
431 hr_utility.set_location('Building subject for transaction:'||ln_transaction_id,17);
432 end if;
433 if(l_creator_person_id=l_current_person_id or lr_hr_api_transaction_rec.transaction_ref_table = 'PER_ALL_VACANCIES') then
434 if g_debug then
435 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_creator_person_id,18);
436 end if;
437
438 -- get creator display name from role
439 wf_directory.GetUserName
440 (p_orig_system => 'PER'
441 ,p_orig_system_id => l_creator_person_id
442 ,p_name => l_creator_username
443 ,p_display_name => l_creator_disp_name);
444
445
446 -- Subject pattern
447 -- "Change Job is saved for later"
448 if g_debug then
449 hr_utility.set_location('Getting message HR_SS_SFL_MSG_SUB_SELF',19);
450 end if;
451 fnd_message.set_name('PER','HR_SS_SFL_MSG_SUB_SELF');
452 fnd_message.set_token('USER_FUNCTION_NAME',lv_ntfSubMsg,false);
453 document := fnd_message.get;
454
455 else
456 -- get creator display name from role
457 if g_debug then
458 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_creator_person_id,20);
459 end if;
460 wf_directory.GetUserName
461 (p_orig_system => 'PER'
462 ,p_orig_system_id => l_creator_person_id
463 ,p_name => l_creator_username
464 ,p_display_name => l_creator_disp_name);
465
466 -- get current person display name from role
467 if g_debug then
468 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_current_person_id,21);
469 end if;
470 wf_directory.GetUserName
471 (p_orig_system => 'PER'
472 ,p_orig_system_id => l_current_person_id
473 ,p_name => l_current_username
474 ,p_display_name => l_current_disp_name);
475
476 -- check if the username/wfrole is null or display name is null
477 if(l_current_username is null OR l_current_disp_name is null) then
478 -- default to the value set in the item attribute CURRENT_PERSON_DISPLAY_NAME
479 begin
480 select decode(
481 fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME')
482 ,'FN',full_name,first_name||' '|| last_name||' '||suffix) FULL_NAME
483 into l_current_disp_name
484 from per_all_people_f
485 where person_id=l_current_person_id
486 and trunc(sysdate) between effective_start_date and effective_end_date;
487
488 exception -- start Bug 6055420
489 when no_data_found then
490 Begin
491 SELECT varchar2_value into l_current_disp_name
492 FROM hr_api_transaction_values
493 WHERE transaction_step_id IN
494 (SELECT transaction_step_id
495 FROM hr_api_transaction_steps
496 WHERE transaction_id = ln_transaction_id
497 AND api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API')
498 AND name = 'P_FULL_NAME'
499 AND 'NEW' =
500 (SELECT varchar2_value
501 FROM hr_api_transaction_values
502 WHERE transaction_step_id IN
503 (SELECT transaction_step_id
504 FROM hr_api_transaction_steps
505 WHERE transaction_id = ln_transaction_id
506 AND api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API')
507 AND name = 'P_ACTION_TYPE');
508 exception
509 when others then
510 l_current_disp_name:='';
511 end; -- end Bug 6055420
512 when others then
513 l_current_disp_name:='';
514 end;
515 end if;
516
517 -- Subject pattern
518 -- "Change Job for Doe, John (proposed by Bond, James) is saved for later"
519 if g_debug then
520 hr_utility.set_location('Getting message HR_SS_SFL_MSG_SUB_REPORTS',22);
521 end if;
522
523 fnd_message.set_name('PER','HR_SS_SFL_MSG_SUB_REPORTS');
524 fnd_message.set_token('USER_FUNCTION_NAME',lv_ntfSubMsg ,false);
525 fnd_message.set_token('SELECTED_PERSON_DISPLAY_NAME',l_current_disp_name,false);
526 fnd_message.set_token('CREATOR_PERSON_DISPLAY_NAME',l_creator_disp_name,false);
527 document := fnd_message.get;
528
529 end if;
530
531
532
533
534
535 if g_debug then
536 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
537 end if;
538
539 exception
540 when others then
541 document :=null;
542 hr_utility.set_location('HR_SFLUTIL_SS.getSFLMsgSubject errored : '||SQLERRM ||' '||to_char(SQLCODE), 40);
543 Wf_Core.Context('HR_SFLUTIL_SS', 'getSFLMsgSubject', document_id, display_type);
544 raise;
545 end getSFLMsgSubject;
546
547
548
549 function getActionSubject(p_transaction_id in number) return varchar2
550 is
551
552 ln_function_id hr_api_transactions.function_id%type;
553 lv_section_display_name hr_api_transactions.section_display_name%type;
554 lv_subject varchar2(240);
555 begin
556 -- default value
557 lv_subject:=p_transaction_id;
558
559 if (p_transaction_id is not null) then
560 begin
561
562 select fffv.user_function_name,hat.section_display_name
563 into lv_subject,lv_section_display_name
564 from hr_api_transactions hat,fnd_form_functions_vl fffv
565 where hat.transaction_id=p_transaction_id
566 and hat.function_id=fffv.function_id;
567
568 exception
569 when no_data_found then
570 if(hr_utility.debug_enabled) then
571 -- write debug statements
572 hr_utility.set_location('no record found for the transaction :'|| p_transaction_id, 4);
573 end if;
574 return lv_subject;
575 when others then
576 return lv_subject;
577 end;
578 else
579 return lv_subject;
580 end if;
581
582 -- add the section display name
583 -- this has translation issues, need to change
584 -- fnd message
585 if(lv_section_display_name is not null) then
586 lv_subject:= lv_subject||' - ' ||lv_section_display_name;
587 end if;
588
589 return lv_subject;
590
591 exception
592 when others then
593 return lv_subject;
594 end getActionSubject;
595
596
597 function getSubject(p_transaction_id in number,
598 p_notification_id in number) return varchar2
599 is
600 lv_subject varchar2(240);
601
602 begin
603
604 lv_subject := p_transaction_id;
605 if(p_notification_id is not null) then
606 -- Fix for bug 13800538
607 return wf_notification.getsubject(p_notification_id,'text/plain');
608 else
609 return getActionSubject(p_transaction_id);
610 end if;
611
612 return lv_subject;
613 exception
614 when others then
615 return p_transaction_id;
616 end getSubject;
617
618
619 procedure getSFLTransactionDetails (
620 p_transaction_id IN NUMBER
621 ,p_ntfId OUT NOCOPY NUMBER
622 ,p_itemType IN OUT NOCOPY VARCHAR2
623 ,p_itemKey OUT NOCOPY VARCHAR2 )
624
625 IS
626 c_proc constant varchar2(30) := 'getSFLTransactionDetails';
627 lv_item_type wf_item_activity_statuses.item_type%type;
628 lv_item_key wf_item_activity_statuses.item_key%type;
629 begin
630 g_debug := hr_utility.debug_enabled;
631 if g_debug then
632 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
633 end if;
634
635 -- check the wf_items table
636 begin
637 select item_type,item_key
638 into p_itemType,p_itemKey
639 from wf_items
640 where user_key=to_char(p_transaction_id)
641 and item_type=nvl(p_itemType,'HRSFL')
642 and end_date is null
643 and rownum<2;
644 exception
645 when no_data_found then
646 p_itemType := null;
647 p_itemKey := null;
648 p_ntfId := null;
649 when others then
650 p_itemType := null;
651 p_itemKey := null;
652 p_ntfId := null;
653 raise;
654 end;
655
656
657
658 exception
659 when others then
660 hr_utility.set_location('HR_SFLUTIL_SS.getSFLTransactionDetails errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
661 Wf_Core.Context('HR_SFLUTIL_SS', 'getSFLTransactionDetails', p_transaction_id);
662 raise;
663 end getSFLTransactionDetails;
664
665 procedure startSFLTransaction(p_transaction_id IN NUMBER
666 ,p_transaction_ref_table in varchar2
667 ,p_itemType IN OUT NOCOPY VARCHAR2
668 ,p_process_name IN VARCHAR2
669 ,p_itemKey OUT NOCOPY VARCHAR2 )
670 IS
671 lv_item_key wf_items.item_key%type;
672 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
673 BEGIN
674 -- get the item key from sequence
675 -- Get the next item key from the sequence
676 select hr_workflow_item_key_s.nextval
677 into p_itemKey
678 from sys.dual;
679
680 -- Create the Workflow Process
681 wf_engine.CreateProcess
682 (itemtype => p_itemType
683 ,itemkey => p_itemKey
684 ,process => p_process_name);
685
686 -- set owner role
687 wf_engine.setItemOwner(itemtype => p_itemType
688 ,itemkey => p_itemKey
689 ,owner => fnd_global.user_name);
690 -- set the user key
691 wf_engine.SetItemUserKey(itemtype => p_itemType
692 ,itemkey => p_itemKey
693 ,userkey => p_transaction_id);
694
695 -- set the SFL transaction reference id, HR_SFL_TRANSACTION_REF_ID_ATTR
696 wf_engine.setitemattrnumber(p_itemType,p_itemKey,'HR_SFL_TRANSACTION_REF_ID_ATTR',p_transaction_id);
697
698 -- HR_SFL_TRANS_REF_TABLE_ATTR
699 wf_engine.setitemattrtext(p_itemType,p_itemKey,'HR_SFL_TRANS_REF_TABLE_ATTR',p_transaction_ref_table);
700 -- set the parent item type and item key ,if any for this SFL
701 begin
702 if(p_transaction_id is not null) then
703 select * into lr_hr_api_transaction_rec from hr_api_transactions
704 where transaction_id=p_transaction_id;
705
706 wf_engine.setitemparent(p_itemType,p_itemKey,lr_hr_api_transaction_rec.item_type,lr_hr_api_transaction_rec.item_key,'SFL');
707 end if;
708 exception
709 when others then
710 null;
711 end;
712
713 -- Start the WF runtime process
714 wf_engine.startprocess
715 (itemtype => p_itemType
716 ,itemkey => p_itemKey);
717
718 commit;
719 --
720 EXCEPTION
721 WHEN others THEN
722 raise;
723 END startSFLTransaction;
724
725 procedure sendSFLNotification(p_transaction_id IN NUMBER,
726 p_transaction_ref_table in varchar2,
727 p_userName in varchar2,
728 p_reentryPageFunction in varchar2,
729 p_sflWFProcessName in varchar2,
730 p_notification_id out NOCOPY number)
731
732 IS
733 --
734 PRAGMA AUTONOMOUS_TRANSACTION;
735 --
736 c_proc constant varchar2(30) := 'getSFLTransactionDetails';
737 lv_item_type wf_items.item_type%type;
738 lv_item_key wf_items.item_type%type;
739 ln_notification_id wf_notifications.notification_id%type;
740 lv_process_name wf_items.root_activity%type;
741 ln_sfl_block_activity_id number;
742 lv_relaunchSFLLink varchar2(2000);
743 begin
744 hr_sflutil_ss.getsfltransactiondetails(p_transaction_id,
745 ln_notification_id,
746 lv_item_type,
747 lv_item_key);
748 if(lv_item_key is null) then
749 -- the process was never started so start a new wf process
750 -- and send notification to fnd_global.user_name
751 lv_item_type :='HRSFL';
752 lv_process_name :=nvl(p_sflWFProcessName,'HR_SFL_NOTIFICATION_JSP_PRC');
753 startSFLTransaction(p_transaction_id,
754 p_transaction_ref_table,
755 lv_item_type,
756 lv_process_name,
757 lv_item_key);
758 end if;
759
760 -- finally check one more time
761 if(lv_item_key is not null) then
762 wf_engine.setitemattrtext(lv_item_type,
763 lv_item_key,
764 'HR_SFL_USERNAME_ATTR',
765 nvl(p_userName,fnd_global.user_name));
766 -- HR_SFL_BLOCK_ID_ATTR
767 ln_sfl_block_activity_id :=wf_engine.GetItemAttrNumber(lv_item_type ,
768 lv_item_key,
769 'HR_SFL_BLOCK_ID_ATTR');
770 -- set the relaunch pagefunction
771 -- syntax JSP:/OA_HTML/OA.jsp?OAFunc=HR_WF_RELATED_APPS NtfId=-&#NID-
772 lv_relaunchSFLLink := 'JSP:/OA_HTML/OA.jsp?OAFunc='||nvl(p_reentryPageFunction,'')||'&'||'NtfId=-'||'&'||'#NID-';
773 wf_engine.setitemattrtext(lv_item_type,
774 lv_item_key,
775 'HR_SFL_RESURRECT_LINK_ATTR',
776 lv_relaunchSFLLink);
777
778 -- set the delete/reject link
779 -- HR_SFL_DELETE_LINK_ATTR
780 wf_engine.setitemattrtext(lv_item_type,
781 lv_item_key,
782 'HR_SFL_DELETE_LINK_ATTR',
783 lv_relaunchSFLLink||'&'||'pAction=DELETE');
784 -- now send the notification
785 wf_engine.completeactivity(lv_item_type,lv_item_key,
786 wf_engine.getactivitylabel(ln_sfl_block_activity_id),
787 wf_engine.eng_trans_default);
788
789 -- set the hr api transactions with the new notification id ??
790 p_notification_id := wf_engine.getitemattrnumber(lv_item_type,lv_item_key,'HR_LAST_SFL_NTF_ID_ATTR');
791
792 else
793 -- raise exception.
794 null;
795 end if;
796 commit;
797 --
798 EXCEPTION
799 WHEN others THEN
800 raise;
801 end sendSFLNotification;
802
803
804 procedure setSFLNtfDetails
805 (itemtype in varchar2
806 ,itemkey in varchar2
807 ,actid in number
808 ,funmode in varchar2
809 ,result in out nocopy varchar2)
810 is
811 -- local variables
812 ln_notification_id number;
813 begin
814 -- set the ntf id value to HR_LAST_SFL_NTF_ID_ATTR
815 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_LAST_SFL_NTF_ID_ATTR',Wf_Engine.g_nid);
816 -- set the hr_api_transactions_table
817
818 result := wf_engine.eng_trans_default;
819 EXCEPTION
820 WHEN others THEN
821 raise;
822 end setSFLNtfDetails;
823
824 function getSFLStatusForUpdate(
825 p_currentTxnStatus in varchar2,
826 p_proposedTxnStatus in varchar2) RETURN VARCHAR2
827 is
828 --local variables
829 c_updateStatus hr_api_transactions.status%type;
830 begin
831
832 -- possible status of current transaction
833 -- RO Transactions returned to approver for correction
834 -- ROS Transactions returned to approver for correction and saved for later
835 -- RI Transactions returned to initiator for correction
836 -- RIS Transactions returned to initiator for correction and saved for later
837 -- N Transactions initiated but not submitted for approval
838 -- S Transactions saved for later
839 -- W Transactions in progress
840 -- Y Transactions submitted for approval
841 -- YS Transactions save for later by approver editing.
842
843 -- check if the current txn status is null
844 -- no more iteration if null return the same status as proposed
845 if(p_currentTxnStatus is null) then
846 return p_proposedTxnStatus;
847 end if;
848
849 -- check the current status
850 if(length(p_currentTxnStatus)=1) then
851 -- possible status N, S, W, Y
852 if(p_currentTxnStatus='Y') then
853 -- update the transaction status as pending approval SFL
854 c_updateStatus:= 'YS';
855 else
856 -- just SFL
857 c_updateStatus:='S';
858 end if;
859 else
860 -- fix for bug 4926377
861 if(p_currentTxnStatus='YS') then
862 -- update the transaction status as pending approval SFL
863 c_updateStatus:= 'YS';
864
865 -- so status is RFC
866 elsif(p_currentTxnStatus in('RI','RIS')) then
867 -- intiator RFC
868 c_updateStatus:='RIS';
869 elsif(p_currentTxnStatus in('RO','ROS')) then
870 -- is there any other status possible ???
871 -- possible status now could be RO ROS
872 c_updateStatus:='ROS';
873 else
874 -- return same status
875 c_updateStatus:=p_proposedTxnStatus;
876 end if;
877
878 end if;
879
880 return c_updateStatus;
881 exception
882 when others then
883 null;
884 end getSFLStatusForUpdate;
885
886
887
888
889
890 function isTxnOwner(p_transaction_id in number,
891 p_person_id in number) return boolean
892 is
893 -- local variables
894 l_returnStatus boolean;
895 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
896 ln_person_id number;
897 begin
898
899 -- set the default value
900 l_returnStatus := false;
901 ln_person_id := p_person_id;
902
903 if(p_transaction_id is not null) then
904 -- derive the transaction details
905 select * into lr_hr_api_transaction_rec from hr_api_transactions
906 where transaction_id=p_transaction_id;
907 end if;
908
909 --
910 if(ln_person_id= fnd_global.employee_id) then
911 l_returnStatus := true;
912 else
913 l_returnStatus :=false;
914 end if;
915 return l_returnStatus;
916 exception
917 when others then
918 raise;
919 end;
920
921 procedure processApprovalSubmit(p_transaction_id in number)
922 is
923 -- local variables
924 c_proc constant varchar2(30) := 'processApprovalSubmit';
925 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
926 ln_activity_id wf_item_activity_statuses.process_activity%type;
927 lv_loginPersonDispName per_all_people_f.full_name%type;
928 lv_loginPersonUserName fnd_user.user_name%type;
929 ln_loginPersonId fnd_user.employee_id%type;
930
931 begin
932 if g_debug then
933 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
934 end if;
935
936 -- code logic
937 begin
938 if(p_transaction_id is not null) then
939 select * into lr_hr_api_transaction_rec
940 from hr_api_transactions
941 where transaction_id=p_transaction_id;
942
943 if(lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS') then
944 -- appraisal specfic
945
946 -- set the item attributes specific to appraisals
947 -- get the wf role info for the login user
948 wf_directory.getusername
949 (p_orig_system => 'PER'
950 ,p_orig_system_id => fnd_global.employee_id
951 ,p_name => lv_loginPersonUserName
952 ,p_display_name => lv_loginPersonDispName);
953 --HR_APPRAISAL_FROM_USER_ATTR
954 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
955 ,lr_hr_api_transaction_rec.item_key
956 ,'HR_APPRAISAL_FROM_USER_ATTR',
957 fnd_global.user_name);
958 -- HR_APPRAISAL_FROM_NAME_ATTR
959 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
960 ,lr_hr_api_transaction_rec.item_key
961 ,'HR_APPRAISAL_FROM_NAME_ATTR'
962 ,lv_loginPersonDispName);
963
964 -- APPROVAL_COMMENT_COPY
965 -- ??? module need to handle in the UI layer.
966
967 -- set the blockid value
968 hr_appraisal_workflow_ss.getapprovalblockid(
969 lr_hr_api_transaction_rec.item_type,
970 lr_hr_api_transaction_rec.item_key,
971 ln_activity_id);
972
973
974 else
975 -- default logic
976 -- get the blockid value corresponding to the UI page
977 SELECT process_activity
978 into ln_activity_id
979 from
980 (select process_activity
981 FROM WF_ITEM_ACTIVITY_STATUSES IAS
982 WHERE ias.item_type = lr_hr_api_transaction_rec.item_type
983 and ias.item_key = lr_hr_api_transaction_rec.item_type
984 and ias.activity_status = 'NOTIFIED'
985 and ias.process_activity in (
986 select wpa.instance_id
987 FROM WF_PROCESS_ACTIVITIES WPA,
988 WF_ACTIVITY_ATTRIBUTES WAA,
989 WF_ACTIVITIES WA,
990 WF_ITEMS WI
991 WHERE wpa.process_item_type = ias.item_type
992 and wa.item_type = wpa.process_item_type
993 and wa.name = wpa.activity_name
994 and wi.item_type = ias.item_type
995 and wi.item_key = ias.item_key
996 and wi.begin_date >= wa.begin_date
997 and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
998 and waa.activity_item_type = wa.item_type
999 and waa.activity_name = wa.name
1000 and waa.activity_version = wa.version
1001 and waa.type = 'FORM'
1002 )
1003 order by begin_date desc)
1004 where rownum<=1;
1005
1006 end if;
1007
1008 -- set the workflow status TRAN_SUBMIT to Y
1009 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
1010 ,lr_hr_api_transaction_rec.item_key
1011 ,'TRAN_SUBMIT'
1012 ,'Y');
1013
1014 -- now transition the workflow to process approval notifications
1015 if(lr_hr_api_transaction_rec.status in('YS','RI','RIS','RO','RIS')) then
1016 -- complete the flow in resubmit mode
1017 wf_engine.CompleteActivity(
1018 lr_hr_api_transaction_rec.item_type
1019 , lr_hr_api_transaction_rec.item_key
1020 , wf_engine.getactivitylabel(ln_activity_id)
1021 , 'RESUBMIT') ;
1022
1023 else
1024 -- else intial submit
1025 wf_engine.CompleteActivity(
1026 lr_hr_api_transaction_rec.item_type
1027 , lr_hr_api_transaction_rec.item_key
1028 , wf_engine.getactivitylabel(ln_activity_id)
1029 , wf_engine.eng_trans_default) ;
1030 end if;
1031 else
1032 -- raise error
1033 null;
1034 end if;
1035 exception
1036 when others then
1037 raise;
1038 end;
1039
1040
1041
1042 if (g_debug ) then
1043 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1044 end if;
1045
1046 exception
1047 when others then
1048 raise;
1049 end processApprovalSubmit;
1050
1051
1052 procedure closeSFLNotifications(p_transaction_id IN NUMBER
1053 ,p_approvalItemType in varchar2
1054 ,p_approvalItemKey in varchar2)
1055 is
1056 -- local variables
1057 c_proc constant varchar2(30) := 'closeSFLNotifications';
1058 lv_sfl_item_type wf_items.item_type%type;
1059 lv_sfl_item_key wf_items.item_key%type;
1060 ln_sfl_block_activity_id number;
1061
1062 begin
1063 if g_debug then
1064 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1065 end if;
1066
1067 -- close the SFL related WF process
1068 begin
1069
1070 -- check if there are any SFL transaction associated
1071 select item_type, item_key
1072 into lv_sfl_item_type,lv_sfl_item_key
1073 from wf_items
1074 where user_key=to_char(p_transaction_id)
1075 --and parent_item_type=nvl(p_approvalItemType,parent_item_type)
1076 --and parent_item_key=nvl(p_approvalItemKey,parent_item_key)
1077 and rownum<2;
1078
1079 if(lv_sfl_item_key is not null) then
1080 -- HR_SFL_BLOCK_ID_ATTR
1081 ln_sfl_block_activity_id :=wf_engine.GetItemAttrNumber(lv_sfl_item_type ,
1082 lv_sfl_item_key,
1083 'HR_SFL_BLOCK_ID_ATTR',true);
1084 if(ln_sfl_block_activity_id is not null) then
1085 -- set the item attribute for SFL transaction
1086 wf_engine.setitemattrtext(lv_sfl_item_type,lv_sfl_item_key,'HR_SEND_SFL_NTF_ATTR','N');
1087
1088 wf_engine.completeactivity(lv_sfl_item_type,lv_sfl_item_key,
1089 wf_engine.getactivitylabel(ln_sfl_block_activity_id),
1090 wf_engine.eng_trans_default);
1091 end if;
1092
1093 end if;
1094 exception
1095 when others then
1096 null;
1097 end;
1098
1099 if (g_debug ) then
1100 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1101 end if;
1102 exception
1103 when others then
1104 raise;
1105 end closeSFLNotifications;
1106
1107
1108 procedure closeOpenSFLNotification(p_transaction_id IN NUMBER)
1109 is
1110 -- local variables
1111 c_proc constant varchar2(40) := 'closeOpenSFLNotification';
1112 lv_sfl_item_type wf_items.item_type%type;
1113 lv_sfl_item_key wf_items.item_key%type;
1114 ln_sfl_block_activity_id number;
1115 ln_notification_id wf_notifications.notification_id%type;
1116
1117 begin
1118 g_debug := hr_utility.debug_enabled;
1119 if g_debug then
1120 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1121 end if;
1122
1123 -- check if there are any SFL transaction associated
1124 hr_utility.set_location('check if there are any SFL transaction associated', 2);
1125
1126 begin
1127 select item_type, item_key
1128 into lv_sfl_item_type,lv_sfl_item_key
1129 from wf_items
1130 where user_key=to_char(p_transaction_id)
1131 and rownum<2;
1132 exception
1133 when no_data_found then
1134 null;
1135
1136 end;
1137 hr_utility.set_location('lv_sfl_item_key:'||lv_sfl_item_key, 3);
1138
1139 if(lv_sfl_item_key is not null) then
1140 -- get the ntf id value to HR_LAST_SFL_NTF_ID_ATTR
1141 ln_notification_id:=
1142 wf_engine.getitemattrnumber(lv_sfl_item_type,
1143 lv_sfl_item_key,
1144 'HR_LAST_SFL_NTF_ID_ATTR',
1145 true);
1146 hr_utility.set_location('sfl ln_notification_id:'||ln_notification_id, 4);
1147 if(ln_notification_id is not null and OpenNotificationsExist(ln_notification_id)) then
1148 -- close the FYI notification
1149 hr_utility.set_location(' calling wf_notification.close for:'||ln_notification_id, 4);
1150 wf_notification.close(ln_notification_id,null);
1151 end if;
1152
1153 end if;
1154
1155
1156 if (g_debug ) then
1157 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1158 end if;
1159 exception
1160 when others then
1161 raise;
1162 end closeOpenSFLNotification;
1163
1164
1165
1166 END HR_SFLUTIL_SS;
[Home] [Help]