[Home] [Help]
PACKAGE BODY: APPS.EAM_ISOLATION_WORKFLOW_PVT
Source
1 PACKAGE BODY EAM_ISOLATION_WORKFLOW_PVT AS
2 /* $Header: EAMVISWB.pls 120.6 2011/07/11 12:48:37 vboddapa noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2009 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME: EAMVISWB.pls
9 --
10 -- DESCRIPTION: Body of package EAM_ISOLATION_WORKFLOW_PVT
11 --
12 -- NOTES
13 --
14 -- HISTORY
15 --
16 -- 18-FEB-2010 Venkateswarlu Boddapati Initial Creation
17 ***************************************************************************/
18
19 /********************************************************************
20 * Procedure : Launch_Workflow
21 * Purpose : Function called from subscription .This will in turn launch the workflow
22 *********************************************************************/
23
24 function Launch_Workflow
25 ( p_subscription_guid in raw
26 , p_event in out NOCOPY wf_event_t
27 ) return varchar2 IS
28
29 Debug_File UTL_FILE.FILE_TYPE;
30 x_return_status VARCHAR2(50);
31 l_param_list wf_parameter_list_t;
32 l_param wf_parameter_t;
33 l_param_idx NUMBER;
34 l_name VARCHAR2(200);
35 l_value VARCHAR2(200);
36 l_item_type VARCHAR2(200);
37 l_item_key VARCHAR2(200);
38 l_wf_process VARCHAR2(200);
39 BEGIN
40
41
42 l_param_list:=p_event.getParameterList;
43
44 l_param_idx := l_param_list.FIRST;
45 while ( l_param_idx is not null) loop
46
47 l_param := l_param_list(l_param_idx);
48
49 IF(l_param.name = 'WORKFLOW_NAME') THEN
50 l_item_type := l_param.value;
51 END IF;
52
53 IF(l_param.name = 'WORKFLOW_PROCESS') THEN
54 l_wf_process := l_param.value;
55 END IF;
56
57 l_param_idx := l_param_list.NEXT(l_param_idx);
58 end loop;
59
60 IF(l_item_type IS NULL OR l_wf_process IS NULL) THEN
61 RETURN 'ERROR';
62 END IF;
63
64 l_item_key := p_event.getEventKey;
65
66 wf_engine.CreateProcess( itemtype =>l_item_type,
67 itemkey => l_item_key,
68 process => l_wf_process );
69
70
71 l_param_idx := l_param_list.FIRST;
72 while ( l_param_idx is not null) loop
73
74 l_param := l_param_list(l_param_idx);
75
76 BEGIN
77
78 wf_engine.SetItemAttrText( itemtype => l_item_type,
79 itemkey => l_item_key,
80 aname => l_param.name,
81 avalue => l_param.value);
82
83 EXCEPTION
84 WHEN OTHERS THEN --if attribute in event is not present in workflow...an exception will be thrown.
85 NULL;
86 END;
87
88 l_param_idx := l_param_list.NEXT(l_param_idx);
89 end loop;
90
91 wf_engine.StartProcess( itemtype => l_item_type,
92 itemkey => l_item_key);
93
94
95 x_return_status := 'SUCCESS';
96 return x_return_status;
97
98 exception
99 when others then
100 return 'ERROR';
101
102 END Launch_Workflow;
103
104
105
106 /********************************************************************
107 * Procedure : Update_Status_Approved
108 * Purpose : Procedure called from Isolation Release Approval when the workflow is approved
109 *********************************************************************/
110 PROCEDURE Update_Status_Approved
111 ( itemtype in varchar2
112 , itemkey in varchar2
113 , actid in number
114 , funcmode in varchar2
115 , resultout out NOCOPY varchar2
116 )IS
117
118 l_isolation_id number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
119 itemkey => itemkey, aname => 'ISOLATION_ID');
120 l_isolation_name varchar2(240):= wf_engine.GetItemAttrtext( itemtype => itemtype,
121 itemkey => itemkey, aname => 'ISOLATION_NAME');
122 l_new_system_status number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
123 itemkey => itemkey, aname => 'NEW_SYSTEM_STATUS');
124 l_new_user_status number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
125 itemkey => itemkey, aname => 'NEW_USER_STATUS');
126 l_organization_id number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
127 itemkey => itemkey, aname => 'ORGANIZATION_ID');
128
129 l_request_id number;
130 l_err_msg varchar2(2000);
131 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
132 l_status_error EXCEPTION;
133
134 l_isolation_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
135 l_old_isolation_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
136 lx_isolation_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
137 l_iso_establishment_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
138 l_iso_reestablishment_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
139 l_isolated_asset_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
140
141 l_output_dir VARCHAR2(512);
142 l_return_status VARCHAR2(1);
143 l_msg_count NUMBER;
144 BEGIN
145
146 -- This procedure will call work isolation public API( procedure PROCESS_isolation ) for processing of the isolation record with the new status and the pending flag as 'N'
147 If (funcmode = 'RUN') then
148
149 /* get output directory path from database */
150 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
151
152 -- l_isolation_header_rec.user_id := fnd_global.user_id;
153 -- l_isolation_header_rec.responsibility_id := fnd_global.resp_id;
154 l_isolation_header_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
155 l_isolation_header_rec.header_id := l_isolation_id;
156 l_isolation_header_rec.batch_id := 1;
157 l_isolation_header_rec.isolation_id := l_isolation_id;
158 l_isolation_header_rec.isolation_name := l_isolation_name;
159 l_isolation_header_rec.organization_id := l_organization_id;
160 l_isolation_header_rec.system_status := l_new_system_status;
161 l_isolation_header_rec.user_defined_status := l_new_user_status;
162 l_isolation_header_rec.pending_flag := 'N';
163
164 -- To populate existing values
165 EAM_ISOLATION_VALIDATE_PVT.Check_Existence
166 ( p_iso_header_rec => l_isolation_header_rec
167 , x_iso_header_rec => l_old_isolation_header_rec
168 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
169 , x_return_Status => l_return_status
170 );
171
172 l_isolation_header_rec.description := l_old_isolation_header_rec.description;
173 --l_isolation_header_rec.valid_from := l_old_isolation_header_rec.valid_from;
174 --l_isolation_header_rec.valid_to := l_old_isolation_header_rec.valid_to;
175 l_isolation_header_rec.approved_by := FND_GLOBAL.user_id;
176
177 EAM_PROCESS_ISOLATION_PVT.PROCESS_ISOLATION(
178 p_bo_identifier => 'EAM'
179 , p_api_version_number => 1.0
180 , p_init_msg_list => TRUE
181 , p_commit => 'N'
182 , p_iso_header_rec => l_isolation_header_rec
183 , p_establish_iso_tbl => l_iso_establishment_tbl
184 , p_re_establish_iso_tbl => l_iso_reestablishment_tbl
185 , p_isolated_asset_tbl => l_isolated_asset_tbl
186 , x_isolation_id => l_isolation_id
187 , x_return_status => l_return_status
188 , x_msg_count => l_msg_count
189 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
190 , p_output_dir => l_output_dir
191 , p_debug_filename => 'workflowisolationapproved.log'
192 , p_debug_file_mode => 'W'
193 );
194
195 if nvl(l_return_status, 'S') <> 'S' then
196 l_return_status := FND_API.G_RET_STS_ERROR;
197 RAISE l_status_error;
198 ELSE
199 COMMIT;
200 end if;
201
202 resultout := 'COMPLETE:';
203 return;
204 end if;
205
206 if (funcmode = 'CANCEL') then
207 resultout := 'COMPLETE:';
208 return;
209 end if;
210
211 if (funcmode = 'TIMEOUT') then
212 resultout := 'COMPLETE:';
213 return;
214 end if;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 wf_core.context('EAMWIREL','UPDATE_STATUS_APPROVED',
219 itemtype, itemkey, actid, funcmode);
220 raise;
221 END Update_Status_Approved;
222
223
224
225 /********************************************************************
226 * Procedure : Update_Status_Rejected
227 * Purpose : Procedure called from Isolation Release Approval when the workflow is Rejected
228 *********************************************************************/
229 PROCEDURE Update_Status_Rejected
230 ( itemtype in varchar2
231 , itemkey in varchar2
232 , actid in number
233 , funcmode in varchar2
234 , resultout out NOCOPY varchar2
235 )IS
236
237 l_isolation_id number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
238 itemkey => itemkey, aname => 'ISOLATION_ID');
239 l_isolation_name varchar2(240):= wf_engine.GetItemAttrtext( itemtype => itemtype,
240 itemkey => itemkey, aname => 'ISOLATION_NAME');
241 l_new_system_status number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
242 itemkey => itemkey, aname => 'NEW_SYSTEM_STATUS');
243 l_new_user_status number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
244 itemkey => itemkey, aname => 'NEW_USER_STATUS');
245 l_organization_id number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
246 itemkey => itemkey, aname => 'ORGANIZATION_ID');
247
248 l_request_id number;
249 l_err_msg varchar2(2000);
250 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
251 l_status_error EXCEPTION;
252
253 l_isolation_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
254 lx_isolation_header_rec EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type;
255 l_iso_establishment_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
256 l_iso_reestablishment_tbl EAM_PROCESS_SAFETY_PUB.eam_establishment_tbl_type;
257 l_isolated_asset_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
258
259 l_output_dir VARCHAR2(512);
260 l_return_status VARCHAR2(1);
261 l_msg_count NUMBER;
262
263 BEGIN
264
265 If (funcmode = 'RUN') then
266
267
268 /* get output directory path from database */
269 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
270
271 l_isolation_header_rec.user_id := fnd_global.user_id;
272 l_isolation_header_rec.responsibility_id := fnd_global.resp_id;
273 l_isolation_header_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
274 l_isolation_header_rec.header_id := l_isolation_id;
275 l_isolation_header_rec.batch_id := 1;
276 l_isolation_header_rec.isolation_id := l_isolation_id;
277 l_isolation_header_rec.organization_id := l_organization_id;
278 l_isolation_header_rec.system_status :=7; --Cancelled
279 l_isolation_header_rec.user_defined_status := 199; --Cancelled by Approver
280 l_isolation_header_rec.pending_flag := 'N';
281
282
283 EAM_PROCESS_ISOLATION_PVT.PROCESS_ISOLATION(
284 p_bo_identifier => 'EAM'
285 , p_api_version_number => 1.0
286 , p_init_msg_list => TRUE
287 , p_commit => 'N'
288 , p_iso_header_rec => l_isolation_header_rec
289 , p_establish_iso_tbl => l_iso_establishment_tbl
290 , p_re_establish_iso_tbl => l_iso_reestablishment_tbl
291 , p_isolated_asset_tbl => l_isolated_asset_tbl
292 , x_isolation_id => l_isolation_id
293 , x_return_status => l_return_status
294 , x_msg_count => l_msg_count
295 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
296 , p_output_dir => l_output_dir
297 , p_debug_filename => 'workflowisolationapproved.log'
298 , p_debug_file_mode => 'W'
299 );
300
301 if nvl(l_return_status, 'S') <> 'S' then
302 l_return_status := FND_API.G_RET_STS_ERROR;
303 RAISE l_status_error;
304 ELSE
305 COMMIT;
306 end if;
307
308 resultout := 'COMPLETE:';
309 return;
310 end if;
311
312 if (funcmode = 'CANCEL') then
313 resultout := 'COMPLETE:';
314 return;
315 end if;
316
317 if (funcmode = 'TIMEOUT') then
318 resultout := 'COMPLETE:';
319 return;
320 end if;
321
322
323 EXCEPTION
324 when others then
325 wf_core.context('EAMWIREL','UPDATE_STATUS_REJECTED',
326 itemtype, itemkey, actid, funcmode);
327 raise;
328 END Update_Status_Rejected;
329
330
331
332
333 /********************************************************************
334 * Procedure : Get_Next_Approver
335 * Purpose : Procedure called from Isolation Release Approval to
336 find the next approver
337 *********************************************************************/
338 procedure Get_Next_Approver(itemtype in varchar2,
339 itemkey in varchar2,
340 actid in number,
341 funcmode in varchar2,
342 resultout out NOCOPY varchar2) IS
343 E_FAILURE EXCEPTION;
344 l_transaction_id number;
345 l_next_approver ame_util.approverRecord2;
346 l_next_approvers ame_util.approversTable2;
347 l_next_approvers_count number;
348 l_approver_index number;
349 l_is_approval_complete VARCHAR2(1);
350 l_transaction_type VARCHAR2(200);
351 l_role_users WF_DIRECTORY.UserTable;
352 l_role_name VARCHAR2(320) ;
353 l_role_display_name VARCHAR2(360) ;
354
355 BEGIN
356
357 IF (funcmode = 'RUN') THEN
358
359 l_transaction_id := TO_NUMBER(itemkey);
360 l_transaction_type := 'oracle.apps.eam.isolation.release.approval';
361
362 wf_engine.SetItemAttrText( itemtype => itemtype,
363 itemkey => itemkey,
364 aname => 'AME_TRANSACTION_TYPE',
365 avalue => l_transaction_type);
366
367 wf_engine.SetItemAttrText( itemtype => itemtype,
368 itemkey => itemkey,
369 aname => 'AME_TRANSACTION_ID',
370 avalue => l_transaction_id );
371
372 ame_api2.getNextApprovers4(applicationIdIn=>426,
373 transactionTypeIn=>l_transaction_type,
374 transactionIdIn=>l_transaction_id,
375 flagApproversAsNotifiedIn => ame_util.booleanTrue,
376 approvalProcessCompleteYNOut => l_is_approval_complete,
377 nextApproversOut=>l_next_approvers);
378
379 if (l_is_approval_complete = ame_util.booleanTrue) then
380 resultout:='COMPLETE:'||'APPROVAL_COMPLETE';
381 return;
382
383 elsif (l_next_approvers.Count = 0) then
384
385 ame_api2.getPendingApprovers(applicationIdIn=>426,
386 transactionTypeIn=>l_transaction_type,
387 transactionIdIn=>l_transaction_id,
388 approvalProcessCompleteYNOut => l_is_approval_complete,
389 approversOut =>l_next_approvers);
390 end if;
391
392 l_next_approvers_count := l_next_approvers.Count;
393
394
395 if (l_next_approvers_count = 0) then
396 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
397 return;
398 end if;
399
400 if (l_next_approvers_count > 0) then
401 resultout:='COMPLETE:'||'VALID_APPROVER';
402 --return;
403 end if;
404
405 if (l_next_approvers_count = 1) then
406 l_next_approver:=l_next_approvers(l_next_approvers.first());
407 wf_engine.SetItemAttrText( itemtype => itemType,
408 itemkey => itemkey,
409 aname => 'APPROVER_USER_NAME' ,
410 avalue => l_next_approver.name);
411
412 wf_engine.SetItemAttrText( itemtype => itemType,
413 itemkey => itemkey,
414 aname => 'APPROVER_DISPLAY_NAME' ,
415 avalue => l_next_approver.display_name);
416 resultout:='COMPLETE:'||'VALID_APPROVER';
417 --return;
418 end if;
419
420 l_approver_index := l_next_approvers.first();
421
422 while ( l_approver_index is not null ) loop
423 l_role_users(l_approver_index):= l_next_approvers(l_approver_index).name ;
424
425 l_approver_index := l_next_approvers.next(l_approver_index);
426
427
428 end loop;
429
430 wf_directory.CreateAdHocRole2( role_name => l_role_name
431 ,role_display_name => l_role_display_name
432 ,language => NULL
433 ,territory => NULL
434 ,role_description => 'EAM ROLE DESC'
435 ,notification_preference => null
436 ,role_users => l_role_users
437 ,email_address => null
438 ,fax => null
439 ,status => 'ACTIVE'
440 ,expiration_date => null
441 ,parent_orig_system => null
442 ,parent_orig_system_id => null
443 ,owner_tag => null
444 );
445
446
447 wf_engine.setitemattrtext(itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'RECIPIENT_ROLE',
450 avalue => l_role_name
451 );
452 return;
453
454 END IF; -- run
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 RAISE;
459 END Get_Next_Approver;
460
461
462
463 /********************************************************************
464 * Procedure : Update_AME_With_Response
465 * Purpose : Procedure called from Isolation Release Approval when an approver
466 responds to a notification
467 *********************************************************************/
468 procedure Update_AME_With_Response
469 ( itemtype in varchar2,
470 itemkey in varchar2,
471 actid in number,
472 funcmode in varchar2,
473 resultout out NOCOPY varchar2
474 ) IS
475 E_FAILURE EXCEPTION;
476 l_transaction_id number;
477 l_nid number;
478 l_gid number;
479 l_approver_name varchar2(240);
480 l_result varchar2(100);
481 l_ame_status varchar2(20);
482 l_original_approver_name varchar2(240);
483 l_forwardeeIn ame_util.approverRecord2;
484
485
486 BEGIN
487 IF (funcmode = 'RUN') THEN
488
489 l_transaction_id := itemkey;
490 l_gid := WF_ENGINE.context_nid;
491
492 SELECT responder,notification_id
493 into l_approver_name,l_nid
494 FROM wf_notifications
495 WHERE group_id=l_gid
496 AND status = 'CLOSED';
497
498 l_result := Wf_Notification.GetAttrText(l_nid, 'RESULT');
499
500
501 if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
502
503 l_ame_status := ame_util.approvedStatus;
504 elsif (l_result = 'REJECTED') then
505 l_ame_status := ame_util.rejectStatus;
506 else -- reject for lack of information, conservative approach
507 l_ame_status := ame_util.rejectStatus;
508 end if;
509 --Set approver as approved or rejected based on approver response
510 ame_api2.updateApprovalStatus2(applicationIdIn=>426,
511 transactionTypeIn=>'oracle.apps.eam.isolation.release.approval',
512 transactionIdIn=>l_transaction_id,
513 approvalStatusIn => l_ame_status,
514 approverNameIn => l_approver_name);
515
516 ELSIF ( funcmode = 'TRANSFER' ) THEN
517
518 l_transaction_id := itemkey;
519 l_forwardeeIn.name :=WF_ENGINE.context_new_role;
520 l_original_approver_name:= WF_ENGINE.context_original_recipient;
521
522
523 ame_api2.updateApprovalStatus2(applicationIdIn=>426,
524 transactionTypeIn=>'oracle.apps.eam.isolation.release.approval',
525 transactionIdIn=>l_transaction_id,
526 approvalStatusIn => 'FORWARD',
527 approverNameIn => l_original_approver_name,
528 forwardeeIn => l_forwardeeIn );
529
530 END IF; -- run
531
532 resultout:= wf_engine.eng_completed || ':' || l_result;
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 RAISE;
537 END Update_AME_With_Response;
538
539
540
541 /********************************************************************
542 * Procedure : Is_Approval_Required_Released
543 * Purpose : This procedure will check if the approval is required for
544 the work isolation release
545 *********************************************************************/
546
547 PROCEDURE Is_Approval_Required_Released
548 ( p_old_isolation_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
549 , p_new_isolation_rec IN EAM_PROCESS_SAFETY_PUB.eam_iso_header_rec_type
550 , x_approval_required OUT NOCOPY BOOLEAN
551 , x_workflow_name OUT NOCOPY VARCHAR2
552 , x_workflow_process OUT NOCOPY VARCHAR2
553 )IS
554 BEGIN
555
556 IF(p_new_isolation_rec.system_status =3 AND --status is released
557 ( (p_new_isolation_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_CREATE) OR
558 (p_new_isolation_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE AND
559 p_old_isolation_rec.system_status IN (17,7)) )
560 )THEN
561 x_approval_required := TRUE;
562 x_workflow_name := 'EAMISREL';
563 x_workflow_process := 'EAM_ISOLATION_RELEASE_APPROVAL';
564 END IF;
565
566 END Is_Approval_Required_Released;
567
568 END EAM_ISOLATION_WORKFLOW_PVT;