[Home] [Help]
PACKAGE BODY: APPS.WIP_EAM_WRAPPROVAL_PVT
Source
1 PACKAGE BODY WIP_EAM_WRAPPROVAL_PVT AS
2 /*$Header: WIPVWRAB.pls 120.14.12020000.3 2012/12/06 10:26:35 vboddapa ship $ */
3
4 PROCEDURE StartWRAProcess ( p_work_request_id in number,
5 p_asset_number in varchar2,
6 p_asset_group in number,
7 p_asset_location in number,
8 p_organization_id in number,
9 p_work_request_status_id in number,
10 p_work_request_priority_id in number,
11 p_work_request_owning_dept_id in number,
12 p_expected_resolution_date in date,
13 p_work_request_type_id in number,
14 p_maintenance_object_type in number default 3,
15 p_maintenance_object_id in number default null,
16 p_notes in varchar2,
17 p_notify_originator in number,
18 p_resultout OUT NOCOPY varchar2,
19 p_error_message OUT NOCOPY varchar2
20 ) IS
21
22 itemtype varchar2(8) := 'EAMWRAP';
23 itemkey varchar2(240);
24 --:= p_work_request_id ;
25 l_instance_number varchar2(30);
26 l_asset_group_segments varchar2(240);
27 l_asset_group_description varchar2(240);
28 l_asset_description varchar2(240);
29 l_priority_description varchar2(240);
30 l_workflow_process varchar2(30) := 'EAMWRAP_PROCESS';
31 l_role_name varchar2(80);
32 l_department_code varchar2(240);
33 l_location_codes varchar2(240);
34 l_work_request_type varchar2(240);
35 l_notes varchar2(4000);
36 -- l_eam_location_id number;
37 -- l_from_role varchar2(240);
38 l_stmt_number number;
39 l_NO_DEPT_RESP EXCEPTION;
40 l_resp_id varchar2(80);
41 l_resp_appl_id varchar2(20);
42 l_resp_string varchar2(20);
43 l_display_name varchar2(80);
44 l_asset_location number;
45 l_primary_approver_name fnd_user.user_name%type;
46 l_maintenance_object_id number;
47 l_asset_group_id number;
48 -- added bug 10399281
49 l_old_content_num Number;
50 l_new_content_num Number;
51 l_total_content_num NUMBER;
52 l_user_name VARCHAR2(50);
53 /* If multiple responsibility to dept , would pick up the first , but
54 this scenario should not exist as per design */
55
56 /* Bug 2112323 - For performance reasons, we shall use work flow API to get responsibility name
57 -- changing the select statement below as it selects only those
58 -- responsibilities that are tied to asset owning department.
59 -- now the user is able to select other depts in beda
60 cursor c_role_name is
61 select wfr.NAME
62 from wf_roles wfr ,
63 bom_eam_dept_approvers beda
64 where
65 beda.dept_id = p_work_request_owning_dept_id
66 and beda.organization_id = p_organization_id
67 and beda.responsibility_id = wfr.orig_system_id ;
68 */
69
70 cursor c_resp_name is
71 select beda.responsibility_id,beda.responsibility_application_id,fu.user_name
72 from bom_eam_dept_approvers beda, fnd_user fu
73 where
74 beda.dept_id = p_work_request_owning_dept_id
75 and beda.organization_id = p_organization_id
76 and fu.user_id(+) = beda.primary_approver_id;
77
78 --added bug 10399281
79
80 CURSOR desc_history_cur (p_work_request_id IN NUMBER) IS
81 select notes
82 from WIP_EAM_WORK_REQ_NOTES wrn
83 where work_request_id = p_work_request_id
84 order by work_request_note_id;
85
86
87 BEGIN
88 p_resultout := FND_API.G_RET_STS_SUCCESS;
89 l_resp_string := 'FND_RESP';
90
91 if p_maintenance_object_id is not null then
92 l_maintenance_object_id := p_maintenance_object_id;
93
94 select instance_number, inventory_item_id into l_instance_number, l_asset_group_id
95 from csi_item_instances where instance_id = l_maintenance_object_id;
96 else
97 if (p_asset_number is not null and p_asset_group is not null) then
98 select instance_number, instance_id into l_instance_number, l_maintenance_object_id
99 from csi_item_instances where serial_number = p_asset_number
100 and inventory_item_id = p_asset_group;
101 l_asset_group_id := p_asset_group;
102 else
103 l_instance_number:=null;
104 l_maintenance_object_id:=null;
105 end if;
106 end if;
107
108 l_stmt_number := 10 ;
109 /* Bug 2112323 -For performance reasons, we shall use work flow API to get responsibility name
110 Open c_role_name;
111 fetch c_role_name into l_role_name ;
112 if c_role_name%NOTFOUND then
113 close c_role_name ;
114 raise l_NO_DEPT_RESP ;
115 end if;
116 close c_role_name;
117 */
118
119 Open c_resp_name;
120 fetch c_resp_name into l_resp_id,l_resp_appl_id,l_primary_approver_name;
121 if c_resp_name%NOTFOUND then
122 close c_resp_name ;
123 raise l_NO_DEPT_RESP ;
124 end if;
125 close c_resp_name;
126
127 -- Added due to Bug 2112323
128 l_resp_appl_id := l_resp_string || l_resp_appl_id;
129
130 -- bug 3841128: Changing parameter of GetRoleName as orig_system seems to be changed to 'FND_RESP'
131 -- prior to 11.5.10, orig_system was FND_RESP concatenated with application id
132 --wf_directory.GetRoleName(l_resp_appl_id ,l_resp_id,l_role_name,l_display_name);
133 wf_directory.GetRoleName(l_resp_string ,l_resp_id,l_role_name,l_display_name);
134 -- end bug 3841128
135
136 -- End added due to Bug 2112323
137
138 if (l_primary_approver_name is not null) then
139 l_role_name := l_primary_approver_name;
140
141 end if;
142
143 -- create a new workflow process
144 l_stmt_number := 20 ;
145
146 -- select sequence value as itemkey
147 select wip_Eam_wrapproval_s.nextval
148 into itemkey
149 from dual;
150
151 wf_engine.CreateProcess( itemtype => itemtype,
152 itemkey => itemkey,
153 process => l_workflow_process,
154 owner_role=> FND_GLOBAL.USER_NAME);
155
156 /* Get Asset description */
157 l_stmt_number := 30 ;
158 begin
159 select cii.instance_description
160 into l_asset_description
161 from csi_item_instances cii
162 where cii.instance_id = l_maintenance_object_id;
163
164 exception
165 when No_Data_Found then
166 l_asset_description := null;
167 when others then
168 null;
169 end;
170
171 /* Get Asset Group description */
172 l_stmt_number := 40 ;
173 begin
174 select MSI.concatenated_segments, MSI.description
175 into l_asset_group_segments, l_asset_group_description
176 from mtl_system_items_kfv msi, mtl_parameters mp
177 where msi.organization_id = mp.organization_id
178 and mp.maint_organization_id = p_organization_id
179 and msi.inventory_item_id = l_asset_group_id
180 and rownum = 1;
181
182 exception
183 when No_Data_Found then
184 l_asset_group_segments := null;
185 l_asset_group_description := null;
186 when others then
187 null;
188 end;
189
190
191 /* Get Work Request Priority description */
192 l_stmt_number := 50 ;
193 begin
194 select ML.meaning
195 into l_priority_description
196 from mfg_lookups ML
197 where ml.lookup_code = p_work_request_priority_id
198 and ml.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' ;
199
200 exception
201 when No_Data_Found then
202 l_priority_description := null;
203 when others then
204 null;
205 end;
206
207 /* Get Department Code */
208 l_stmt_number := 60 ;
209 begin
210 select bd.department_code
211 into l_department_code
212 from bom_departments bd
213 where bd.organization_id = p_organization_id
214 and bd.department_id = p_work_request_owning_dept_id ;
215
216 exception
217 when No_Data_Found then
218 l_department_code := null;
219 when others then
220 null;
221 end;
222
223 /* sraval: if p_asset_location is null, check if asset has a location */
224 /* csprague: added check for p_asset_location = 0 fp: 4320910 */
225 if (p_asset_location is null or p_asset_location = 0) then
226 begin
227 select area_id
228 into l_asset_location
229 from eam_org_maint_defaults
230 where organization_id = p_organization_id
231 and object_type = 50
232 and object_id = l_maintenance_object_id;
233 exception
234 when no_data_found then
235 l_asset_location := null;
236 when others then
237 raise;
238 end;
239 else
240 l_asset_location := p_asset_location;
241
242 end if;
243
244
245
246 /* Get Location Code */
247 l_stmt_number := 70 ;
248 begin
249 select location_codes
250 into l_location_codes
251 from MTL_EAM_LOCATIONS
252 where organization_id = p_organization_id
253 and location_id = l_asset_location ;
254
255 exception
256 when No_Data_Found then
257 l_location_codes := null;
258 when others then
259 null;
260 end;
261
262 /* Get Work Request Type */
263 l_stmt_number := 75 ;
264 begin
265 select ml.meaning
266 into l_work_request_type
267 from MFG_LOOKUPS ml
268 where ml.lookup_code = p_work_request_type_id
269 and ml.lookup_type = 'WIP_EAM_WORK_REQ_TYPE' ;
270
271 exception
272 when No_Data_Found then
273 l_work_request_type := null;
274 when others then
275 null;
276 end;
277
278
279 /* Get header info. for notes
280 The below record should always exist, as even for empty comments
281 we insert the header info */
282 l_stmt_number := 76 ;
283 begin
284 /* select notes into l_notes
285 from WIP_EAM_WORK_REQ_NOTES wrn1
286 where work_request_id = p_work_request_id
287 and work_request_note_id in
288 (select min(work_request_note_id)
289 from WIP_EAM_WORK_REQ_NOTES wrn2
290 where wrn1.work_request_id = wrn2.work_request_id);
291
292 If l_notes is not null then
293 l_notes := l_notes || wf_core.newline || p_notes ;
294 Else
295 l_notes := p_notes ;
296 End if ; */
297 -- Changing the code for bug 10399281. Carry entire history ( last 4000 chars).
298 FOR desc_history_rec in desc_history_cur(p_work_request_id) LOOP
299
300 l_old_content_num:= Length(l_notes);
301 l_new_content_num:=Length(desc_history_rec.notes);
302 l_total_content_num:= l_old_content_num+l_new_content_num+Length(wf_core.newline);
303
304
305 IF (l_total_content_num)>4000 THEN
306 IF l_notes IS NOT NULL THEN
307 l_notes:=SubStr(l_notes,l_new_content_num+1)||wf_core.newline||desc_history_rec.notes ;
308 ELSE
309 l_notes:= desc_history_rec.notes;
310 END IF;
311 ELSE
312 l_notes:=l_notes ||wf_core.newline || desc_history_rec.notes;
313 END IF;
314
315
316 END LOOP ;
317
318 IF l_notes IS NULL THEN
319 l_notes:= p_notes;
320 END IF;
321
322
323 exception
324 when others then
325 l_notes := p_notes ;
326 null;
327 end ;
328
329
330 /* Set Attributes */
331 l_stmt_number := 80 ;
332 wf_engine.SetItemAttrNumber( itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'WORK_REQUEST_ID',
335 avalue => p_work_request_id );
336
337 wf_engine.SetItemAttrText( itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'ASSET_NUMBER',
340 avalue => l_instance_number);
341
342 wf_engine.SetItemAttrText(itemtype => itemtype,
343 itemkey => itemkey,
344 aname => 'ASSET_DESCRIPTION',
345 avalue => l_asset_description);
346
347 wf_engine.SetItemAttrNumber(itemtype => itemtype,
348 itemkey => itemkey,
349 aname => 'ASSET_GROUP',
350 avalue => p_asset_group);
351
352 wf_engine.SetItemAttrText(itemtype => itemtype,
353 itemkey => itemkey,
354 aname => 'ASSET_GROUP_SEGMENTS',
355 avalue => l_asset_group_segments);
356
357 wf_engine.SetItemAttrText(itemtype =>itemtype,
358 itemkey =>itemkey,
359 aname => 'ASSET_GROUP_DESCRIPTION',
360 avalue => l_asset_group_description);
361
362 wf_engine.SetItemAttrNumber( itemtype => itemtype,
363 itemkey => itemkey,
364 aname => 'WORK_REQUEST_STATUS_ID',
365 avalue => p_work_request_status_id);
366
367 wf_engine.SetItemAttrNumber( itemtype => itemtype,
368 itemkey => itemkey,
369 aname => 'WORK_REQUEST_PRIORITY_ID',
370 avalue => p_work_request_priority_id);
371
372 wf_engine.SetItemAttrText(itemtype =>itemtype,
373 itemkey =>itemkey,
374 aname => 'PRIORITY_DESCRIPTION',
375 avalue => l_priority_description);
376
377 wf_engine.SetItemAttrNumber( itemtype => itemtype,
378 itemkey => itemkey,
379 aname => 'WORK_REQUEST_OWNING_DEPT_ID',
380 avalue => p_work_request_owning_dept_id);
381
382 wf_engine.SetItemAttrText(itemtype=>itemtype,
383 itemkey =>itemkey,
384 aname=> 'DEPARTMENT_CODE',
385 avalue=> l_department_code);
386
387 wf_engine.SetItemAttrDate( itemtype => itemtype,
388 itemkey => itemkey,
389 aname => 'EXPECTED_RESOLUTION_DATE',
390 avalue => p_expected_resolution_date);
391
392 wf_engine.SetItemAttrText( itemtype => itemtype,
393 itemkey => itemkey,
394 aname => 'NOTES',
395 avalue => l_notes);
396
397 /* Responsibility associated to the Owning Dept in MSN */
398 wf_engine.SetItemAttrText(itemtype=>itemtype,
399 itemkey =>itemkey,
400 aname=> 'DEPT_RESPONSIBILTY',
401 avalue=> l_role_name);
402
403
404 wf_engine.SetItemAttrNumber( itemtype => itemtype,
405 itemkey => itemkey,
406 aname => 'ASSET_LOCATION',
407 avalue => l_asset_location);
408
409 wf_engine.SetItemAttrText(itemtype=>itemtype,
410 itemkey =>itemkey,
411 aname=> 'LOCATION_CODES',
412 avalue=> l_location_codes);
413
414 wf_engine.SetItemAttrNumber(itemtype=>itemtype,
415 itemkey =>itemkey,
416 aname=> 'WORK_REQUEST_TYPE_ID',
417 avalue=> p_work_request_type_id);
418
419 wf_engine.SetItemAttrText(itemtype=>itemtype,
420 itemkey =>itemkey,
421 aname=> 'WORK_REQUEST_TYPE',
422 avalue=> l_work_request_type);
423
424 -- From_Role is displayed on Notifn Summary screen
425 -- l_from_role := FND_GLOBAL.USER_NAME ;
426
427 BEGIN
428 SELECT user_name
429 INTO l_user_name
430 FROM fnd_user
431 WHERE user_id in (select created_by FROM wip_eam_work_requests where work_request_id = p_work_request_id);
432 EXCEPTION
433 When NO_DATA_FOUND then
434 l_user_name := FND_GLOBAL.USER_NAME ;
435 END;
436
437 wf_engine.SetItemAttrText(itemtype=>itemtype,
438 itemkey =>itemkey,
439 aname=> '#FROM_ROLE',
440 avalue=> l_user_name );
441
442 -- sraval: set originator attribute if notify_originator is 'Yes'
443 if ((p_notify_originator is not null) and (p_notify_originator = 1)) then
444 wf_engine.SetItemAttrText(itemtype=>itemtype,
445 itemkey =>itemkey,
446 aname=> 'WORK_REQUEST_ORIGINATOR',
447 avalue=> FND_GLOBAL.USER_NAME );
448 else
449 wf_engine.SetItemAttrText(itemtype=>itemtype,
450 itemkey =>itemkey,
451 aname=> 'WORK_REQUEST_ORIGINATOR',
452 avalue=> null);
453
454 end if;
455 /* Start Process */
456 l_stmt_number := 100 ;
457 wf_engine.StartProcess( itemtype => itemtype,
458 itemkey => itemkey);
459
460 /* Set workflow process to background for better performance */
461
462 update wip_eam_work_requests
463 set wf_item_type = itemtype,
464 wf_item_key = itemkey
465 where work_request_id = p_work_request_id;
466 l_stmt_number := 120 ;
467 --wf_engine.threshold := -1;
468
469 -- commit ;
470
471 EXCEPTION
472 When l_NO_DEPT_RESP then
473 p_resultout := FND_API.G_RET_STS_ERROR;
474 P_error_message := ' Work Request cannot be created as there are no ' ||
475 ' department approvers for the selected ' ||
476 ' [Asset : ' || P_asset_number || '] Assigned Department';
477 Fnd_message.Set_token('MESSAGE', P_error_message);
478 Fnd_msg_pub.Add;
479 When others then
480 wf_core.context('EAMWRAP','StartWRAProcess', itemtype, itemkey);
481 P_resultout := Fnd_api.G_ret_sts_error;
482 P_error_message := To_char(L_stmt_number) || ' EAMWRAP'
483 || ' StartWRAProcess'|| Itemtype || Itemkey;
484 Fnd_message.Set_token('MESSAGE', P_error_message);
485 Fnd_msg_pub.Add;
486 raise;
487
488 END StartWRAProcess;
489
490 /*FP of 8976785 R12 bug 9506684 */
491 PROCEDURE Update_FROM_ROLE( itemtype in varchar2,
492 itemkey in varchar2,
493 actid in number,
494 funcmode in varchar2,
495 resultout out NOCOPY varchar2) IS
496 l_approver_name varchar2(100);
497
498 BEGIN
499 If (funcmode = 'RESPOND') THEN
500
501 l_approver_name:=wf_engine.context_user;
502
503
504
505 -- gajs_test_mesg('l_approver_name in respond:'||l_approver_name);
506 wf_engine.SetItemAttrText(itemtype=>itemtype,
507 itemkey =>itemkey,
508 aname=> '#FROM_ROLE',
509 avalue=> l_approver_name);
510
511 --Bug 3494922: Set From Role 2 to the approver's user name
512 BEGIN
513 wf_engine.SetItemAttrText(itemtype=>itemtype,
514 itemkey =>itemkey,
515 aname=> 'FROM_ROLE2',
516 avalue=> l_approver_name);
517 EXCEPTION/*bug#4395553 - added for WF upgraded from pre11i10*/
518 WHEN OTHERS THEN
519 IF (wf_core.error_name = 'WFENG_ITEM_ATTR') THEN
520 wf_engine.AddItemAttr(itemtype=>itemtype,
521 itemkey =>itemkey,
522 aname=>'FROM_ROLE2');
523 wf_engine.SetItemAttrText( itemtype => itemtype,
524 itemkey => itemkey,
525 aname => 'FROM_ROLE2',
526 avalue => l_approver_name );
527 ELSE
528 raise;
529 END IF;
530 END;
531
532 set_employee_name(
533 itemtype=>itemtype,
534 itemkey =>itemkey,
535 actid => actid,
536 funcmode =>funcmode,
537 p_user_name => l_approver_name);
538
539
540 END IF;
541
542 END Update_FROM_ROLE;
543
544
545 /* Update status to 'Awaiting Work Order' in wip_eam_work_requests */
546 PROCEDURE Update_Status_Await_Wo( itemtype in varchar2,
547 itemkey in varchar2,
548 actid in number,
549 funcmode in varchar2,
550 resultout out NOCOPY varchar2) is
551
552 l_work_request_id number :=
553 wf_engine.GetItemAttrNumber( itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'WORK_REQUEST_ID');
556
557 l_comment varchar2(2000) :=
558 wf_engine.GetItemAttrText( itemtype => itemtype,
559 itemkey => itemkey,
560 aname => 'COMMENT');
561
562 l_role_name varchar2(80) :=
563 wf_engine.GetItemAttrText( itemtype=>itemtype,
564 itemkey =>itemkey,
565 aname=> 'DEPT_RESPONSIBILTY');
566
567 l_approver_name varchar2(100):=wf_engine.GetItemAttrText( itemtype=>itemtype,
568 itemkey =>itemkey,
569 aname=> '#FROM_ROLE');
570 l_work_request_note_id number;
571 l_last_updated_by number;
572 l_stmt_number number;
573 l_nid NUMBER;
574 BEGIN
575
576 l_stmt_number := 10;
577
578 If (funcmode = 'RUN') then
579
580 Update WIP_EAM_WORK_REQUESTS
581 set work_request_status_id = 3 ,
582 last_updated_by = FND_GLOBAL.USER_ID,
583 last_update_date = SYSDATE
584 Where work_request_id = l_work_request_id ;
585
586 -- Set the from role to be displayed on notifn summary
587
588
589
590 -- If l_comment is not null Then
591 l_stmt_number := 20;
592 select wip_eam_work_req_notes_s.nextval
593 into l_work_request_note_id
594 from dual ;
595
596 l_comment := ' *** '||
597 l_approver_name||
598 ' (' ||
599 to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
600 ||') *** ' || wf_core.newline ||
601 l_comment ;
602 /*
603 l_stmt_number := 30;
604 select orig_system_id
605 into l_last_updated_by
606 from wf_roles
607 where name = l_role_name ;
608 */
609 -- Assumption For SYSDATE , there is not much time lag betwn
610 -- comments entered on WF and time it reaches the below insert
611
612 l_stmt_number := 40;
613 Insert into WIP_EAM_WORK_REQ_NOTES
614 (WORK_REQUEST_NOTE_ID ,
615 LAST_UPDATE_DATE ,
616 LAST_UPDATED_BY ,
617 CREATION_DATE,
618 CREATED_BY ,
619 LAST_UPDATE_LOGIN,
620 WORK_REQUEST_ID ,
621 NOTES,
622 WORK_REQUEST_NOTE_TYPE,
623 NOTIFICATION_ID )
624 Values
625 ( l_work_request_note_id,
626 SYSDATE ,
627 FND_GLOBAL.USER_ID,
628 SYSDATE,
629 FND_GLOBAL.USER_ID,
630 null,
631 l_work_request_id,
632 l_comment ,
633 2,
634 null);
635 -- End If ; --- comment not null
636
637 resultout := 'COMPLETE:';
638 return;
639
640 End if;
641
642
643 if (funcmode = 'CANCEL') then
644
645 l_stmt_number := 50;
646 resultout := 'COMPLETE:';
647 return;
648
649 end if;
650
651 if (funcmode = 'TIMEOUT') then
652
653 l_stmt_number := 60;
654 resultout := 'COMPLETE:';
655 return;
656
657 end if;
658
659 EXCEPTION
660
661 when others then
662 wf_core.context('EAMWRAP','UPDATE_STATUS_AWAIT_WO '||to_char(l_stmt_number),
663 itemtype, itemkey, actid, funcmode);
664 raise;
665
666 END Update_Status_Await_Wo;
667
668
669 /* update status to Rejected in wip_eam_work_requests */
670 PROCEDURE Update_Status_Rejected( itemtype in varchar2,
671 itemkey in varchar2,
672 actid in number,
673 funcmode in varchar2,
674 resultout out NOCOPY varchar2) is
675
676 l_work_request_id number :=
677 wf_engine.GetItemAttrNumber( itemtype => itemtype,
678 itemkey => itemkey,
679 aname => 'WORK_REQUEST_ID');
680
681 l_comment varchar2(2000) :=
682 wf_engine.GetItemAttrText( itemtype => itemtype,
683 itemkey => itemkey,
684 aname => 'COMMENT');
685
686 l_role_name varchar2(80) :=
687 wf_engine.GetItemAttrText( itemtype=>itemtype,
688 itemkey =>itemkey,
689 aname=> 'DEPT_RESPONSIBILTY');
690
691 l_user_name varchar2(100):=wf_engine.GetItemAttrText( itemtype=>itemtype,
692 itemkey =>itemkey,
693 aname=> '#FROM_ROLE');
694
695 l_work_request_note_id number;
696 l_last_updated_by number;
697 l_stmt_number number;
698
699 BEGIN
700 l_stmt_number := 10;
701
702 If (funcmode = 'RUN') then
703
704 Update WIP_EAM_WORK_REQUESTS
705 set work_request_status_id = 5,
706 last_updated_by = FND_GLOBAL.USER_ID,
707 last_update_date = SYSDATE
708 Where work_request_id = l_work_request_id ;
709
710 -- Set the from role to be displayed on notifn summary
711
712 -- If l_comment is not null Then
713 l_stmt_number := 20;
714 select wip_eam_work_req_notes_s.nextval
715 into l_work_request_note_id
716 from dual ;
717
718 l_comment := ' *** '||
719 l_user_name||
720 ' (' ||
721 to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
722 ||') *** ' || wf_core.newline ||
723 l_comment;
724 /*
725 l_stmt_number := 30;
726 select orig_system_id
727 into l_last_updated_by
728 from wf_roles
729 where name = l_role_name ;
730 */
731
732 -- Assumption For SYSDATE , there is not much time lag betwn
733 -- comments entered on WF and time it reaches the below insert
734
735 l_stmt_number := 40;
736 Insert into WIP_EAM_WORK_REQ_NOTES
737 (WORK_REQUEST_NOTE_ID ,
738 LAST_UPDATE_DATE ,
739 LAST_UPDATED_BY ,
740 CREATION_DATE,
741 CREATED_BY ,
742 LAST_UPDATE_LOGIN,
743 WORK_REQUEST_ID ,
744 NOTES,
745 WORK_REQUEST_NOTE_TYPE,
746 NOTIFICATION_ID )
747 Values
748 ( l_work_request_note_id,
749 SYSDATE ,
750 FND_GLOBAL.USER_ID,
751 SYSDATE,
752 FND_GLOBAL.USER_ID,
753 null,
754 l_work_request_id,
755 l_comment ,
756 2,
757 null);
758 -- End If ; --- comment not null
759
760 resultout := 'COMPLETE:';
761 return;
762
763 End if;
764
765 if (funcmode = 'CANCEL') then
766
767 l_stmt_number := 50;
768 resultout := 'COMPLETE:';
769 return;
770
771 end if;
772
773 if (funcmode = 'TIMEOUT') then
774
775 l_stmt_number := 60;
776 resultout := 'COMPLETE:';
777 return;
778
779 end if;
780
781 EXCEPTION
782
783 when others then
784 wf_core.context('EAMWRAP','UPDATE_STATUS_REJECTED '|| to_char(l_stmt_number) ,itemtype, itemkey, actid, funcmode);
785 raise;
786 END Update_status_rejected;
787
788 /*Update status to 'Additional Information' in wip_eam_work_requests */
789
790 PROCEDURE Update_Status_Add( itemtype in varchar2,
791 itemkey in varchar2,
792 actid in number,
793 funcmode in varchar2,
794 resultout out NOCOPY varchar2) is
795
796 l_work_request_id number :=
797 wf_engine.GetItemAttrNumber( itemtype => itemtype,
798 itemkey => itemkey,
799 aname => 'WORK_REQUEST_ID');
800
801 l_comment varchar2(2000) :=
802 wf_engine.GetItemAttrText( itemtype => itemtype,
803 itemkey => itemkey,
804 aname => 'COMMENT');
805
806 l_previous_reassign_comment varchar2(2000) :=
807 wf_engine.GetItemAttrText( itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'PREVIOUS_REASSIGN_COMMENT');
810 -- From role
811 l_role_name varchar2(80) :=
812 wf_engine.GetItemAttrText( itemtype=>itemtype,
813 itemkey =>itemkey,
814 aname=> 'DEPT_RESPONSIBILTY');
815 -- To role
816 l_reassign_role_name varchar2(80) :=
817 wf_engine.GetItemAttrText( itemtype=>itemtype,
818 itemkey =>itemkey,
819 aname=> 'REASSIGN_ROLE');
820
821 l_user_name varchar2(100):=wf_engine.GetItemAttrText( itemtype=>itemtype,
822 itemkey =>itemkey,
823 aname=> '#FROM_ROLE');
824
825 l_work_request_note_id number ;
826 l_last_updated_by number ;
827 l_stmt_number number ;
828 BEGIN
829
830 IF funcmode = 'RUN' then
831
832 l_stmt_number := 10;
833 /* Bug: 3418639 - Commenting line below so that the From field in notification
834 is the same as the from field during creation of notification and
835 not the from field of the person who has changed status to Add. Info
836 wf_engine.SetItemAttrText(itemtype=>itemtype,
837 itemkey =>itemkey,
838 aname=> '#FROM_ROLE',
839 avalue=> FND_GLOBAL.USER_NAME);
840 */
841
842 -- 7 Aug '01 , as per discussion with Adey
843
844 If l_reassign_role_name is null then
845 l_reassign_role_name := l_role_name ;
846 End if ;
847
848
849 l_stmt_number := 15;
850 wf_engine.SetItemAttrText(itemtype=>itemtype,
851 itemkey =>itemkey,
852 aname=> 'DEPT_RESPONSIBILTY',
853 avalue=> l_reassign_role_name);
854
855 l_stmt_number := 20;
856 Update WIP_EAM_WORK_REQUESTS
857 set work_request_status_id = 2,
858 last_updated_by = FND_GLOBAL.USER_ID,
859 last_update_date = SYSDATE
860 Where work_request_id = l_work_request_id ;
861
862 -- If l_comment is not null Then
863 l_stmt_number := 30;
864 select wip_eam_work_req_notes_s.nextval
865 into l_work_request_note_id
866 from dual ;
867
868 /* l_stmt_number := 40;
869 select orig_system_id , display_name
870 into l_last_updated_by , l_display_name
871 from wf_roles
872 where name = l_role_name ;
873 */
874
875 l_stmt_number := 50;
876
877 -- set additional info comment before changing and nullifying comment, bug7480408
878 wf_engine.SetItemAttrText( itemtype => itemtype,
879 itemkey => itemkey,
880 aname => 'ADD_COMMENT',
881 avalue => l_comment);
882
883 l_comment := ' *** '||
884 l_user_name||
885 ' (' ||
886 to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
887 ||') *** ' || wf_core.newline ||
888 l_comment;
889
890
891 If l_previous_reassign_comment is null
892 Then
893 l_previous_reassign_comment := l_comment ;
894 Else
895 l_previous_reassign_comment := l_previous_reassign_comment ||
896 wf_core.newline || l_comment ;
897 End if ;
898
899 -- Assumption For SYSDATE , there is not much time lag betwn
900 -- comments entered on WF and time it reaches the below insert
901
902 l_stmt_number := 60;
903 Insert into WIP_EAM_WORK_REQ_NOTES
904 (WORK_REQUEST_NOTE_ID ,
905 LAST_UPDATE_DATE ,
906 LAST_UPDATED_BY ,
907 CREATION_DATE,
908 CREATED_BY ,
909 LAST_UPDATE_LOGIN,
910 WORK_REQUEST_ID ,
911 NOTES,
912 WORK_REQUEST_NOTE_TYPE,
913 NOTIFICATION_ID )
914 Values
915 ( l_work_request_note_id,
916 SYSDATE ,
917 FND_GLOBAL.USER_ID ,
918 SYSDATE,
919 FND_GLOBAL.USER_ID,
920 null,
921 l_work_request_id,
922 l_comment ,
923 2,
924 null);
925
926 /* begin
927 select TEXT_VALUE into l_previous_reassign_comment
928 from wf_item_attribute_values
929 where ITEM_TYPE = itemtype
930 and ITEM_KEY = itemkey
931 and NAME = 'PREVIOUS_REASSIGN_COMMENT' ;
932
933 Exception
934 When others then
935 null;
936 End ;
937 */
938
939 l_stmt_number := 70;
940 wf_engine.SetItemAttrText( itemtype => itemtype,
941 itemkey => itemkey,
942 aname => 'PREVIOUS_REASSIGN_COMMENT',
943 avalue => l_previous_reassign_comment);
944
945 -- Comment box is cleared so new comments can be entered
946 l_comment := null ;
947 l_stmt_number := 80;
948 wf_engine.SetItemAttrText( itemtype => itemtype,
949 itemkey => itemkey,
950 aname => 'COMMENT',
951 avalue => l_comment);
952 -- End if ; --- comment not null
953
954 resultout := 'COMPLETE:';
955 return;
956
957 End if; -- function mode
958
959 if (funcmode = 'CANCEL') then
960
961 l_stmt_number := 90;
962 resultout := 'COMPLETE:';
963 return;
964
965 end if;
966
967 if (funcmode = 'TIMEOUT') then
968
969 l_stmt_number := 100;
970 resultout := 'COMPLETE:';
971 return;
972
973 end if;
974
975 EXCEPTION
976
977 when others then
978 wf_core.context('EAMWRAP', 'UPDATE_STATUS_ADD '|| to_char(l_stmt_number),
979 itemtype, itemkey, actid, funcmode);
980 raise;
981
982 END Update_status_add;
983
984 procedure CHECK_NOTIFY_ORIGINATOR(
985 itemtype in varchar2,
986 itemkey in varchar2,
987 actid in number,
988 funcmode in varchar2,
989 resultout in out NOCOPY varchar2)
990 is
991 l_work_request_originator varchar2(100);
992 wf_yes varchar2(1) := 'Y';
993 wf_no varchar2(1) := 'N';
994 begin
995
996 --
997 -- RUN mode - normal process execution
998 --
999 if (funcmode = 'RUN') then
1000
1001 -- retrieve requestor, approver
1002 l_work_request_originator := wf_engine.GetItemAttrText(itemtype => itemtype
1003 ,itemkey => itemkey
1004 ,aname => 'WORK_REQUEST_ORIGINATOR'
1005 ,ignore_notfound=>true);/*Added for bug#4425039*/
1006
1007 if l_work_request_originator is null then
1008 resultout := wf_engine.eng_completed||':'||wf_no;
1009 else
1010 resultout := wf_engine.eng_completed||':'||wf_yes;
1011 end if;
1012 return;
1013 end if;
1014
1015
1016 --
1017 -- CANCEL mode - activity 'compensation'
1018 --
1019 -- This is in the event that the activity must be undone,
1020 -- for example when a process is reset to an earlier point
1021 -- due to a loop back.
1022 --
1023 if (funcmode = 'CANCEL') then
1024
1025 -- no result needed
1026 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1027 return;
1028 end if;
1029
1030
1031 --
1032 -- Other execution modes may be created in the future. Your
1033 -- activity will indicate that it does not implement a mode
1034 -- by returning null
1035 --
1036 resultout := wf_engine.eng_null;
1037 return;
1038
1039 exception
1040 when others then
1041 -- The line below records this function call in the error system
1042 -- in the case of an exception.
1043 wf_core.context('WIPVWRAB', 'CHECK_NOTIFY_ORIGINATOR',
1044 itemtype, itemkey, to_char(actid), funcmode);
1045 raise;
1046 end CHECK_NOTIFY_ORIGINATOR;
1047
1048 procedure set_employee_name
1049 ( itemtype in varchar2,
1050 itemkey in varchar2,
1051 actid in number,
1052 funcmode in varchar2,
1053 p_user_name in varchar2)
1054 is
1055 l_user_name varchar2(100);
1056 l_employee_name varchar2(300);
1057 begin
1058 -- select employee information
1059 begin
1060 SELECT nvl(first_name ||' '||last_name,p_user_name)
1061 INTO l_employee_name
1062 FROM PER_PEOPLE_F
1063 WHERE PERSON_ID=
1064 (select employee_id from fnd_user where user_name=p_user_name);
1065 exception
1066 when others then
1067 l_employee_name := p_user_name;
1068 end;
1069
1070 wf_engine.SetItemAttrText( itemtype => itemtype,
1071 itemkey => itemkey,
1072 aname => 'FROM_ROLE_EMPLOYEE',
1073 avalue => l_employee_name );
1074 end set_employee_name;
1075
1076 END WIP_EAM_WRAPPROVAL_PVT;