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