1 PACKAGE BODY HR_SFLUTIL_SS AS
2 /* $Header: hrsflutlss.pkb 120.8.12000000.5 2007/09/27 10:47:14 dbatra 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
421
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;
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
544 raise;
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);
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 return wf_notification.getsubject(p_notification_id);
607 else
608 return getActionSubject(p_transaction_id);
609 end if;
610
611 return lv_subject;
612 exception
613 when others then
614 return p_transaction_id;
615 end getSubject;
616
617
618 procedure getSFLTransactionDetails (
619 p_transaction_id IN NUMBER
620 ,p_ntfId OUT NOCOPY NUMBER
621 ,p_itemType IN OUT NOCOPY VARCHAR2
622 ,p_itemKey OUT NOCOPY VARCHAR2 )
623
624 IS
625 c_proc constant varchar2(30) := 'getSFLTransactionDetails';
626 lv_item_type wf_item_activity_statuses.item_type%type;
627 lv_item_key wf_item_activity_statuses.item_key%type;
628 begin
629 g_debug := hr_utility.debug_enabled;
630 if g_debug then
631 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
632 end if;
633
634 -- check the wf_items table
635 begin
636 select item_type,item_key
637 into p_itemType,p_itemKey
638 from wf_items
639 where user_key=to_char(p_transaction_id)
640 and item_type=nvl(p_itemType,'HRSFL')
641 and end_date is null
642 and rownum<2;
643 exception
644 when no_data_found then
645 p_itemType := null;
646 p_itemKey := null;
647 p_ntfId := null;
648 when others then
649 p_itemType := null;
650 p_itemKey := null;
651 p_ntfId := null;
652 raise;
653 end;
654
655
656
657 exception
658 when others then
659 hr_utility.set_location('HR_SFLUTIL_SS.getSFLTransactionDetails errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
660 Wf_Core.Context('HR_SFLUTIL_SS', 'getSFLTransactionDetails', p_transaction_id);
661 raise;
662 end getSFLTransactionDetails;
663
664 procedure startSFLTransaction(p_transaction_id IN NUMBER
665 ,p_transaction_ref_table in varchar2
666 ,p_itemType IN OUT NOCOPY VARCHAR2
667 ,p_process_name IN VARCHAR2
668 ,p_itemKey OUT NOCOPY VARCHAR2 )
669 IS
670 lv_item_key wf_items.item_key%type;
671 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
672 BEGIN
673 -- get the item key from sequence
674 -- Get the next item key from the sequence
675 select hr_workflow_item_key_s.nextval
676 into p_itemKey
677 from sys.dual;
678
679 -- Create the Workflow Process
680 wf_engine.CreateProcess
681 (itemtype => p_itemType
682 ,itemkey => p_itemKey
683 ,process => p_process_name);
684
685 -- set owner role
686 wf_engine.setItemOwner(itemtype => p_itemType
687 ,itemkey => p_itemKey
688 ,owner => fnd_global.user_name);
689 -- set the user key
690 wf_engine.SetItemUserKey(itemtype => p_itemType
691 ,itemkey => p_itemKey
692 ,userkey => p_transaction_id);
693
697 -- HR_SFL_TRANS_REF_TABLE_ATTR
694 -- set the SFL transaction reference id, HR_SFL_TRANSACTION_REF_ID_ATTR
695 wf_engine.setitemattrnumber(p_itemType,p_itemKey,'HR_SFL_TRANSACTION_REF_ID_ATTR',p_transaction_id);
696
698 wf_engine.setitemattrtext(p_itemType,p_itemKey,'HR_SFL_TRANS_REF_TABLE_ATTR',p_transaction_ref_table);
699 -- set the parent item type and item key ,if any for this SFL
700 begin
701 if(p_transaction_id is not null) then
702 select * into lr_hr_api_transaction_rec from hr_api_transactions
703 where transaction_id=p_transaction_id;
704
705 wf_engine.setitemparent(p_itemType,p_itemKey,lr_hr_api_transaction_rec.item_type,lr_hr_api_transaction_rec.item_key,'SFL');
706 end if;
707 exception
708 when others then
709 null;
710 end;
711
712 -- Start the WF runtime process
713 wf_engine.startprocess
714 (itemtype => p_itemType
715 ,itemkey => p_itemKey);
716
717 commit;
718 --
719 EXCEPTION
720 WHEN others THEN
721 raise;
722 END startSFLTransaction;
723
724 procedure sendSFLNotification(p_transaction_id IN NUMBER,
725 p_transaction_ref_table in varchar2,
726 p_userName in varchar2,
727 p_reentryPageFunction in varchar2,
728 p_sflWFProcessName in varchar2,
729 p_notification_id out NOCOPY number)
730
731 IS
732 --
733 PRAGMA AUTONOMOUS_TRANSACTION;
734 --
735 c_proc constant varchar2(30) := 'getSFLTransactionDetails';
736 lv_item_type wf_items.item_type%type;
737 lv_item_key wf_items.item_type%type;
738 ln_notification_id wf_notifications.notification_id%type;
739 lv_process_name wf_items.root_activity%type;
740 ln_sfl_block_activity_id number;
741 lv_relaunchSFLLink varchar2(2000);
742 begin
743 hr_sflutil_ss.getsfltransactiondetails(p_transaction_id,
744 ln_notification_id,
745 lv_item_type,
746 lv_item_key);
747 if(lv_item_key is null) then
748 -- the process was never started so start a new wf process
749 -- and send notification to fnd_global.user_name
750 lv_item_type :='HRSFL';
751 lv_process_name :=nvl(p_sflWFProcessName,'HR_SFL_NOTIFICATION_JSP_PRC');
752 startSFLTransaction(p_transaction_id,
753 p_transaction_ref_table,
754 lv_item_type,
755 lv_process_name,
756 lv_item_key);
757 end if;
758
759 -- finally check one more time
760 if(lv_item_key is not null) then
761 wf_engine.setitemattrtext(lv_item_type,
762 lv_item_key,
763 'HR_SFL_USERNAME_ATTR',
764 nvl(p_userName,fnd_global.user_name));
765 -- HR_SFL_BLOCK_ID_ATTR
766 ln_sfl_block_activity_id :=wf_engine.GetItemAttrNumber(lv_item_type ,
767 lv_item_key,
768 'HR_SFL_BLOCK_ID_ATTR');
769 -- set the relaunch pagefunction
770 -- syntax JSP:/OA_HTML/OA.jsp?OAFunc=HR_WF_RELATED_APPS NtfId=-&#NID-
771 lv_relaunchSFLLink := 'JSP:/OA_HTML/OA.jsp?OAFunc='||nvl(p_reentryPageFunction,'')||'&'||'NtfId=-'||'&'||'#NID-';
772 wf_engine.setitemattrtext(lv_item_type,
773 lv_item_key,
774 'HR_SFL_RESURRECT_LINK_ATTR',
775 lv_relaunchSFLLink);
776
777 -- set the delete/reject link
778 -- HR_SFL_DELETE_LINK_ATTR
779 wf_engine.setitemattrtext(lv_item_type,
780 lv_item_key,
781 'HR_SFL_DELETE_LINK_ATTR',
782 lv_relaunchSFLLink||'&'||'pAction=DELETE');
783 -- now send the notification
784 wf_engine.completeactivity(lv_item_type,lv_item_key,
785 wf_engine.getactivitylabel(ln_sfl_block_activity_id),
786 wf_engine.eng_trans_default);
787
788 -- set the hr api transactions with the new notification id ??
789 p_notification_id := wf_engine.getitemattrnumber(lv_item_type,lv_item_key,'HR_LAST_SFL_NTF_ID_ATTR');
790
791 else
792 -- raise exception.
793 null;
794 end if;
795 commit;
796 --
797 EXCEPTION
798 WHEN others THEN
799 raise;
800 end sendSFLNotification;
801
802
803 procedure setSFLNtfDetails
804 (itemtype in varchar2
805 ,itemkey in varchar2
806 ,actid in number
807 ,funmode in varchar2
808 ,result in out nocopy varchar2)
809 is
810 -- local variables
811 ln_notification_id number;
812 begin
813 -- set the ntf id value to HR_LAST_SFL_NTF_ID_ATTR
814 wf_engine.setitemattrnumber(itemtype,itemkey,'HR_LAST_SFL_NTF_ID_ATTR',Wf_Engine.g_nid);
815 -- set the hr_api_transactions_table
816
817 result := wf_engine.eng_trans_default;
818 EXCEPTION
819 WHEN others THEN
820 raise;
821 end setSFLNtfDetails;
822
826 is
823 function getSFLStatusForUpdate(
824 p_currentTxnStatus in varchar2,
825 p_proposedTxnStatus in varchar2) RETURN VARCHAR2
827 --local variables
828 c_updateStatus hr_api_transactions.status%type;
829 begin
830
831 -- possible status of current transaction
832 -- RO Transactions returned to approver for correction
833 -- ROS Transactions returned to approver for correction and saved for later
834 -- RI Transactions returned to initiator for correction
835 -- RIS Transactions returned to initiator for correction and saved for later
836 -- N Transactions initiated but not submitted for approval
837 -- S Transactions saved for later
838 -- W Transactions in progress
839 -- Y Transactions submitted for approval
840 -- YS Transactions save for later by approver editing.
841
842 -- check if the current txn status is null
843 -- no more iteration if null return the same status as proposed
844 if(p_currentTxnStatus is null) then
845 return p_proposedTxnStatus;
846 end if;
847
848 -- check the current status
849 if(length(p_currentTxnStatus)=1) then
850 -- possible status N, S, W, Y
851 if(p_currentTxnStatus='Y') then
852 -- update the transaction status as pending approval SFL
853 c_updateStatus:= 'YS';
854 else
855 -- just SFL
856 c_updateStatus:='S';
857 end if;
858 else
859 -- fix for bug 4926377
860 if(p_currentTxnStatus='YS') then
861 -- update the transaction status as pending approval SFL
862 c_updateStatus:= 'YS';
863
864 -- so status is RFC
865 elsif(p_currentTxnStatus in('RI','RIS')) then
866 -- intiator RFC
867 c_updateStatus:='RIS';
868 elsif(p_currentTxnStatus in('RO','ROS')) then
869 -- is there any other status possible ???
870 -- possible status now could be RO ROS
871 c_updateStatus:='ROS';
872 else
873 -- return same status
874 c_updateStatus:=p_proposedTxnStatus;
875 end if;
876
877 end if;
878
879 return c_updateStatus;
880 exception
881 when others then
882 null;
883 end getSFLStatusForUpdate;
884
885
886
887
888
889 function isTxnOwner(p_transaction_id in number,
890 p_person_id in number) return boolean
891 is
892 -- local variables
893 l_returnStatus boolean;
894 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
895 ln_person_id number;
896 begin
897
898 -- set the default value
899 l_returnStatus := false;
900 ln_person_id := p_person_id;
901
902 if(p_transaction_id is not null) then
903 -- derive the transaction details
904 select * into lr_hr_api_transaction_rec from hr_api_transactions
905 where transaction_id=p_transaction_id;
906 end if;
907
908 --
909 if(ln_person_id= fnd_global.employee_id) then
910 l_returnStatus := true;
911 else
912 l_returnStatus :=false;
913 end if;
914 return l_returnStatus;
915 exception
916 when others then
917 raise;
918 end;
919
920 procedure processApprovalSubmit(p_transaction_id in number)
921 is
922 -- local variables
923 c_proc constant varchar2(30) := 'processApprovalSubmit';
924 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
925 ln_activity_id wf_item_activity_statuses.process_activity%type;
926 lv_loginPersonDispName per_all_people_f.full_name%type;
927 lv_loginPersonUserName fnd_user.user_name%type;
928 ln_loginPersonId fnd_user.employee_id%type;
929
930 begin
931 if g_debug then
932 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
933 end if;
934
935 -- code logic
936 begin
937 if(p_transaction_id is not null) then
938 select * into lr_hr_api_transaction_rec
939 from hr_api_transactions
940 where transaction_id=p_transaction_id;
941
942 if(lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS') then
943 -- appraisal specfic
944
945 -- set the item attributes specific to appraisals
946 -- get the wf role info for the login user
947 wf_directory.getusername
948 (p_orig_system => 'PER'
949 ,p_orig_system_id => fnd_global.employee_id
950 ,p_name => lv_loginPersonUserName
951 ,p_display_name => lv_loginPersonDispName);
952 --HR_APPRAISAL_FROM_USER_ATTR
953 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
954 ,lr_hr_api_transaction_rec.item_key
955 ,'HR_APPRAISAL_FROM_USER_ATTR',
956 fnd_global.user_name);
957 -- HR_APPRAISAL_FROM_NAME_ATTR
958 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
962
959 ,lr_hr_api_transaction_rec.item_key
960 ,'HR_APPRAISAL_FROM_NAME_ATTR'
961 ,lv_loginPersonDispName);
963 -- APPROVAL_COMMENT_COPY
964 -- ??? module need to handle in the UI layer.
965
966 -- set the blockid value
967 hr_appraisal_workflow_ss.getapprovalblockid(
968 lr_hr_api_transaction_rec.item_type,
969 lr_hr_api_transaction_rec.item_key,
970 ln_activity_id);
971
972
973 else
974 -- default logic
975 -- get the blockid value corresponding to the UI page
976 SELECT process_activity
977 into ln_activity_id
978 from
979 (select process_activity
980 FROM WF_ITEM_ACTIVITY_STATUSES IAS
981 WHERE ias.item_type = lr_hr_api_transaction_rec.item_type
982 and ias.item_key = lr_hr_api_transaction_rec.item_type
983 and ias.activity_status = 'NOTIFIED'
984 and ias.process_activity in (
985 select wpa.instance_id
986 FROM WF_PROCESS_ACTIVITIES WPA,
987 WF_ACTIVITY_ATTRIBUTES WAA,
988 WF_ACTIVITIES WA,
989 WF_ITEMS WI
990 WHERE wpa.process_item_type = ias.item_type
991 and wa.item_type = wpa.process_item_type
992 and wa.name = wpa.activity_name
993 and wi.item_type = ias.item_type
994 and wi.item_key = ias.item_key
995 and wi.begin_date >= wa.begin_date
996 and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
997 and waa.activity_item_type = wa.item_type
998 and waa.activity_name = wa.name
999 and waa.activity_version = wa.version
1000 and waa.type = 'FORM'
1001 )
1002 order by begin_date desc)
1003 where rownum<=1;
1004
1005 end if;
1006
1007 -- set the workflow status TRAN_SUBMIT to Y
1008 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
1009 ,lr_hr_api_transaction_rec.item_key
1010 ,'TRAN_SUBMIT'
1011 ,'Y');
1012
1013 -- now transition the workflow to process approval notifications
1014 if(lr_hr_api_transaction_rec.status in('YS','RI','RIS','RO','RIS')) then
1015 -- complete the flow in resubmit mode
1016 wf_engine.CompleteActivity(
1017 lr_hr_api_transaction_rec.item_type
1018 , lr_hr_api_transaction_rec.item_key
1019 , wf_engine.getactivitylabel(ln_activity_id)
1020 , 'RESUBMIT') ;
1021
1022 else
1023 -- else intial submit
1024 wf_engine.CompleteActivity(
1025 lr_hr_api_transaction_rec.item_type
1026 , lr_hr_api_transaction_rec.item_key
1027 , wf_engine.getactivitylabel(ln_activity_id)
1028 , wf_engine.eng_trans_default) ;
1029 end if;
1030 else
1031 -- raise error
1032 null;
1033 end if;
1034 exception
1035 when others then
1036 raise;
1037 end;
1038
1039
1040
1041 if (g_debug ) then
1042 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1043 end if;
1044
1045 exception
1046 when others then
1047 raise;
1048 end processApprovalSubmit;
1049
1050
1051 procedure closeSFLNotifications(p_transaction_id IN NUMBER
1052 ,p_approvalItemType in varchar2
1053 ,p_approvalItemKey in varchar2)
1054 is
1055 -- local variables
1056 c_proc constant varchar2(30) := 'closeSFLNotifications';
1057 lv_sfl_item_type wf_items.item_type%type;
1058 lv_sfl_item_key wf_items.item_key%type;
1059 ln_sfl_block_activity_id number;
1060
1061 begin
1062 if g_debug then
1063 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1064 end if;
1065
1066 -- close the SFL related WF process
1067 begin
1068
1069 -- check if there are any SFL transaction associated
1070 select item_type, item_key
1074 --and parent_item_type=nvl(p_approvalItemType,parent_item_type)
1071 into lv_sfl_item_type,lv_sfl_item_key
1072 from wf_items
1073 where user_key=to_char(p_transaction_id)
1075 --and parent_item_key=nvl(p_approvalItemKey,parent_item_key)
1076 and rownum<2;
1077
1078 if(lv_sfl_item_key is not null) then
1079 -- HR_SFL_BLOCK_ID_ATTR
1080 ln_sfl_block_activity_id :=wf_engine.GetItemAttrNumber(lv_sfl_item_type ,
1081 lv_sfl_item_key,
1082 'HR_SFL_BLOCK_ID_ATTR',true);
1083 if(ln_sfl_block_activity_id is not null) then
1084 -- set the item attribute for SFL transaction
1085 wf_engine.setitemattrtext(lv_sfl_item_type,lv_sfl_item_key,'HR_SEND_SFL_NTF_ATTR','N');
1086
1087 wf_engine.completeactivity(lv_sfl_item_type,lv_sfl_item_key,
1088 wf_engine.getactivitylabel(ln_sfl_block_activity_id),
1089 wf_engine.eng_trans_default);
1090 end if;
1091
1092 end if;
1093 exception
1094 when others then
1095 null;
1096 end;
1097
1098 if (g_debug ) then
1099 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1100 end if;
1101 exception
1102 when others then
1103 raise;
1104 end closeSFLNotifications;
1105
1106
1107 procedure closeOpenSFLNotification(p_transaction_id IN NUMBER)
1108 is
1109 -- local variables
1110 c_proc constant varchar2(40) := 'closeOpenSFLNotification';
1111 lv_sfl_item_type wf_items.item_type%type;
1112 lv_sfl_item_key wf_items.item_key%type;
1113 ln_sfl_block_activity_id number;
1114 ln_notification_id wf_notifications.notification_id%type;
1115
1116 begin
1117 g_debug := hr_utility.debug_enabled;
1118 if g_debug then
1119 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1120 end if;
1121
1122 -- check if there are any SFL transaction associated
1123 hr_utility.set_location('check if there are any SFL transaction associated', 2);
1124
1125 begin
1126 select item_type, item_key
1127 into lv_sfl_item_type,lv_sfl_item_key
1128 from wf_items
1129 where user_key=to_char(p_transaction_id)
1130 and rownum<2;
1131 exception
1132 when no_data_found then
1133 null;
1134
1135 end;
1136 hr_utility.set_location('lv_sfl_item_key:'||lv_sfl_item_key, 3);
1137
1138 if(lv_sfl_item_key is not null) then
1139 -- get the ntf id value to HR_LAST_SFL_NTF_ID_ATTR
1140 ln_notification_id:=
1141 wf_engine.getitemattrnumber(lv_sfl_item_type,
1142 lv_sfl_item_key,
1143 'HR_LAST_SFL_NTF_ID_ATTR',
1144 true);
1145 hr_utility.set_location('sfl ln_notification_id:'||ln_notification_id, 4);
1146 if(ln_notification_id is not null and OpenNotificationsExist(ln_notification_id)) then
1147 -- close the FYI notification
1148 hr_utility.set_location(' calling wf_notification.close for:'||ln_notification_id, 4);
1149 wf_notification.close(ln_notification_id,null);
1150 end if;
1151
1152 end if;
1153
1154
1155 if (g_debug ) then
1156 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1157 end if;
1158 exception
1159 when others then
1160 raise;
1161 end closeOpenSFLNotification;
1162
1163
1164
1165 END HR_SFLUTIL_SS;
[Home] [Help]