[Home] [Help]
PACKAGE BODY: APPS.EAM_WORKPERMIT_WORKFLOW_PVT
Source
1 PACKAGE BODY EAM_WORKPERMIT_WORKFLOW_PVT AS
2 /* $Header: EAMVWPWB.pls 120.5 2011/07/11 12:52:39 vboddapa noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2009 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME: EAMVWPWB.pls
9 --
10 -- DESCRIPTION: Body of package EAM_WORKPERMIT_WORKFLOW_PVT
11 --
12 -- NOTES
13 --
14 -- HISTORY
15 --
16 -- 18-FEB-2010 Madhuri Shah 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 Work Permit 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_permit_id number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
119 itemkey => itemkey, aname => 'PERMIT_ID');
120 l_permit_name varchar2(240):= wf_engine.GetItemAttrtext( itemtype => itemtype,
121 itemkey => itemkey, aname => 'PERMIT_NAME');
122 l_new_system_status number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
123 itemkey => itemkey, aname => 'NEW_SYSTEM_STATUS');
124 l_new_permit_status number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
125 itemkey => itemkey, aname => 'NEW_PERMIT_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_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
135 l_old_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
136 lx_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
137 l_permit_wo_association_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
138 l_permit_clearance_asso_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
139
140 l_output_dir VARCHAR2(512);
141 l_return_status VARCHAR2(1);
142 l_msg_count NUMBER;
143 BEGIN
144
145 -- This procedure will call work permit public API( procedure PROCESS_PERMIT ) for processing of the work permit record with the new status and the pending flag as 'N'
146 If (funcmode = 'RUN') then
147
148 /* get output directory path from database */
149 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
150
151 -- l_work_permit_header_rec.user_id := fnd_global.user_id;
152 -- l_work_permit_header_rec.responsibility_id := fnd_global.resp_id;
153 l_work_permit_header_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
154 l_work_permit_header_rec.header_id := l_permit_id;
155 l_work_permit_header_rec.batch_id := 1;
156 l_work_permit_header_rec.permit_id := l_permit_id;
157 l_work_permit_header_rec.organization_id := l_organization_id;
158 l_work_permit_header_rec.status_type := l_new_system_status;
159 l_work_permit_header_rec.user_defined_status_id := l_new_permit_status;
160 l_work_permit_header_rec.pending_flag := 'N';
161 l_work_permit_header_rec.permit_id := l_permit_id;
162 l_work_permit_header_rec.permit_name := l_permit_name;
163
164 -- To populate existing values
165 EAM_PERMIT_VALIDATE_PVT.Check_Existence
166 ( p_work_permit_header_rec => l_work_permit_header_rec
167 , x_work_permit_header_rec => l_old_work_permit_header_rec
168 , x_mesg_token_Tbl => l_Mesg_Token_Tbl
169 , x_return_Status => l_return_status
170 );
171
172 l_work_permit_header_rec.description := l_old_work_permit_header_rec.description;
173 l_work_permit_header_rec.valid_from := l_old_work_permit_header_rec.valid_from;
174 l_work_permit_header_rec.valid_to := l_old_work_permit_header_rec.valid_to;
175 l_work_permit_header_rec.approved_by := FND_GLOBAL.user_id;
176
177 EAM_PROCESS_PERMIT_PVT.PROCESS_WORK_PERMIT(
178 p_bo_identifier => 'EAM'
179 , p_api_version_number => 1.0
180 , p_init_msg_list => TRUE
181 , p_commit => 'N'
182 , p_work_permit_header_rec => l_work_permit_header_rec
183 , p_permit_wo_association_tbl => l_permit_wo_association_tbl
184 ,p_permit_clearance_asso_tbl => l_permit_clearance_asso_tbl
185 , x_work_permit_header_rec => lx_work_permit_header_rec
186 , x_return_status => l_return_status
187 , x_msg_count => l_msg_count
188 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
189 , p_output_dir => l_output_dir
190 , p_debug_filename => 'workflowpermitapproved.log'
191 , p_debug_file_mode => 'W'
192 );
193
194 if nvl(l_return_status, 'S') <> 'S' then
195 l_return_status := FND_API.G_RET_STS_ERROR;
196 RAISE l_status_error;
197 ELSE
198 COMMIT;
199 end if;
200
201 resultout := 'COMPLETE:';
202 return;
203 end if;
204
205 if (funcmode = 'CANCEL') then
206 resultout := 'COMPLETE:';
207 return;
208 end if;
209
210 if (funcmode = 'TIMEOUT') then
211 resultout := 'COMPLETE:';
212 return;
213 end if;
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 wf_core.context('EAMWPREL','UPDATE_STATUS_APPROVED',
218 itemtype, itemkey, actid, funcmode);
219 raise;
220 END Update_Status_Approved;
221
222
223
224 /********************************************************************
225 * Procedure : Update_Status_Rejected
226 * Purpose : Procedure called from Work Permit Release Approval when the workflow is Rejected
227 *********************************************************************/
228 PROCEDURE Update_Status_Rejected
229 ( itemtype in varchar2
230 , itemkey in varchar2
231 , actid in number
232 , funcmode in varchar2
233 , resultout out NOCOPY varchar2
234 )IS
235
236 l_permit_id number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
237 itemkey => itemkey, aname => 'PERMIT_ID');
238 l_permit_name varchar2(240):= wf_engine.GetItemAttrtext( itemtype => itemtype,
239 itemkey => itemkey, aname => 'PERMIT_NAME');
240 l_new_system_status number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
241 itemkey => itemkey, aname => 'NEW_SYSTEM_STATUS');
242 l_new_permit_status number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
243 itemkey => itemkey, aname => 'NEW_PERMIT_STATUS');
244 l_organization_id number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
245 itemkey => itemkey, aname => 'ORGANIZATION_ID');
246
247 l_request_id number;
248 l_err_msg varchar2(2000);
249 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
250 l_status_error EXCEPTION;
251
252 l_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
253 lx_work_permit_header_rec EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type;
254 l_permit_wo_association_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
255 l_permit_clearance_asso_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
256
257 l_output_dir VARCHAR2(512);
258 l_return_status VARCHAR2(1);
259 l_msg_count NUMBER;
260
261 BEGIN
262
263 If (funcmode = 'RUN') then
264
265
266 /* get output directory path from database */
267 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
268
269 l_work_permit_header_rec.user_id := fnd_global.user_id;
270 l_work_permit_header_rec.responsibility_id := fnd_global.resp_id;
271 l_work_permit_header_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
272 l_work_permit_header_rec.header_id := l_permit_id;
273 l_work_permit_header_rec.batch_id := 1;
274 l_work_permit_header_rec.permit_id := l_permit_id;
275 l_work_permit_header_rec.organization_id := l_organization_id;
276 l_work_permit_header_rec.status_type :=7; --Cancelled
277 l_work_permit_header_rec.user_defined_status_id := 99; --Cancelled by Approver
278 l_work_permit_header_rec.pending_flag := 'N';
279
280
281 EAM_PROCESS_PERMIT_PVT.PROCESS_WORK_PERMIT(
282 p_bo_identifier => 'EAM'
283 , p_api_version_number => 1.0
284 , p_init_msg_list => TRUE
285 , p_commit => 'N'
286 , p_work_permit_header_rec => l_work_permit_header_rec
287 , p_permit_wo_association_tbl => l_permit_wo_association_tbl
288 , p_permit_clearance_asso_tbl => l_permit_clearance_asso_tbl
289 , x_work_permit_header_rec => lx_work_permit_header_rec
290 , x_return_status => l_return_status
291 , x_msg_count => l_msg_count
292 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
293 , p_output_dir => l_output_dir
294 , p_debug_filename => 'workflowpermitapproved.log'
295 , p_debug_file_mode => 'W'
296 );
297
298 if nvl(l_return_status, 'S') <> 'S' then
299 l_return_status := FND_API.G_RET_STS_ERROR;
300 RAISE l_status_error;
301 ELSE
302 COMMIT;
303 end if;
304
305 resultout := 'COMPLETE:';
306 return;
307 end if;
308
309 if (funcmode = 'CANCEL') then
310 resultout := 'COMPLETE:';
311 return;
312 end if;
313
314 if (funcmode = 'TIMEOUT') then
315 resultout := 'COMPLETE:';
316 return;
317 end if;
318
319
320 EXCEPTION
321 when others then
322 wf_core.context('EAMWPREL','UPDATE_STATUS_REJECTED',
323 itemtype, itemkey, actid, funcmode);
324 raise;
325 END Update_Status_Rejected;
326
327
328
329
330 /********************************************************************
331 * Procedure : Get_Next_Approver
332 * Purpose : Procedure called from Work Permit Release Approval to
333 find the next approver
334 *********************************************************************/
335 procedure Get_Next_Approver(itemtype in varchar2,
336 itemkey in varchar2,
337 actid in number,
338 funcmode in varchar2,
339 resultout out NOCOPY varchar2) IS
340 E_FAILURE EXCEPTION;
341 l_transaction_id number;
342 l_next_approver ame_util.approverRecord2;
343 l_next_approvers ame_util.approversTable2;
344 l_next_approvers_count number;
345 l_approver_index number;
346 l_is_approval_complete VARCHAR2(1);
347 l_transaction_type VARCHAR2(200);
348 l_role_users WF_DIRECTORY.UserTable;
349 l_role_name VARCHAR2(320) ;
350 l_role_display_name VARCHAR2(360) ;
351
352 BEGIN
353
354 IF (funcmode = 'RUN') THEN
355
356 l_transaction_id := TO_NUMBER(itemkey);
357 l_transaction_type := 'oracle.apps.eam.permit.release.approval';
358
359 wf_engine.SetItemAttrText( itemtype => itemtype,
360 itemkey => itemkey,
361 aname => 'AME_TRANSACTION_TYPE',
362 avalue => l_transaction_type);
363
364 wf_engine.SetItemAttrText( itemtype => itemtype,
365 itemkey => itemkey,
366 aname => 'AME_TRANSACTION_ID',
367 avalue => l_transaction_id );
368
369 ame_api2.getNextApprovers4(applicationIdIn=>426,
370 transactionTypeIn=>l_transaction_type,
371 transactionIdIn=>l_transaction_id,
372 flagApproversAsNotifiedIn => ame_util.booleanTrue,
373 approvalProcessCompleteYNOut => l_is_approval_complete,
374 nextApproversOut=>l_next_approvers);
375
376 if (l_is_approval_complete = ame_util.booleanTrue) then
377 resultout:='COMPLETE:'||'APPROVAL_COMPLETE';
378 return;
379
380 elsif (l_next_approvers.Count = 0) then
381
382 ame_api2.getPendingApprovers(applicationIdIn=>426,
383 transactionTypeIn=>l_transaction_type,
384 transactionIdIn=>l_transaction_id,
385 approvalProcessCompleteYNOut => l_is_approval_complete,
386 approversOut =>l_next_approvers);
387 end if;
388
389 l_next_approvers_count := l_next_approvers.Count;
390
391
392 if (l_next_approvers_count = 0) then
393 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
394 return;
395 end if;
396
397 if (l_next_approvers_count > 0) then
398 resultout:='COMPLETE:'||'VALID_APPROVER';
399 --return;
400 end if;
401
402 if (l_next_approvers_count = 1) then
403 l_next_approver:=l_next_approvers(l_next_approvers.first());
404 wf_engine.SetItemAttrText( itemtype => itemType,
405 itemkey => itemkey,
406 aname => 'APPROVER_USER_NAME' ,
407 avalue => l_next_approver.name);
408
409 wf_engine.SetItemAttrText( itemtype => itemType,
410 itemkey => itemkey,
411 aname => 'APPROVER_DISPLAY_NAME' ,
412 avalue => l_next_approver.display_name);
413 resultout:='COMPLETE:'||'VALID_APPROVER';
414 --return;
415 end if;
416
417 l_approver_index := l_next_approvers.first();
418
419 while ( l_approver_index is not null ) loop
420 l_role_users(l_approver_index):= l_next_approvers(l_approver_index).name ;
421
422 l_approver_index := l_next_approvers.next(l_approver_index);
423
424
425 end loop;
426
427 wf_directory.CreateAdHocRole2( role_name => l_role_name
428 ,role_display_name => l_role_display_name
429 ,language => NULL
430 ,territory => NULL
431 ,role_description => 'EAM ROLE DESC'
432 ,notification_preference => null
433 ,role_users => l_role_users
434 ,email_address => null
435 ,fax => null
436 ,status => 'ACTIVE'
437 ,expiration_date => null
438 ,parent_orig_system => null
439 ,parent_orig_system_id => null
440 ,owner_tag => null
441 );
442
443
444 wf_engine.setitemattrtext(itemtype => itemtype,
445 itemkey => itemkey,
446 aname => 'RECIPIENT_ROLE',
447 avalue => l_role_name
448 );
449 return;
450
451 END IF; -- run
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 RAISE;
456 END Get_Next_Approver;
457
458
459
460 /********************************************************************
461 * Procedure : Update_AME_With_Response
462 * Purpose : Procedure called from Permit Release Approval when an approver
463 responds to a notification
464 *********************************************************************/
465 procedure Update_AME_With_Response
466 ( itemtype in varchar2,
467 itemkey in varchar2,
468 actid in number,
469 funcmode in varchar2,
470 resultout out NOCOPY varchar2
471 ) IS
472 E_FAILURE EXCEPTION;
473 l_transaction_id number;
474 l_nid number;
475 l_gid number;
476 l_approver_name varchar2(240);
477 l_result varchar2(100);
478 l_ame_status varchar2(20);
479 l_original_approver_name varchar2(240);
480 l_forwardeeIn ame_util.approverRecord2;
481
482
483 BEGIN
484 IF (funcmode = 'RUN') THEN
485
486 l_transaction_id := itemkey;
487 l_gid := WF_ENGINE.context_nid;
488
489 SELECT responder,notification_id
490 into l_approver_name,l_nid
491 FROM wf_notifications
492 WHERE group_id=l_gid
493 AND status = 'CLOSED';
494
495 l_result := Wf_Notification.GetAttrText(l_nid, 'RESULT');
496
497
498 if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
499
500 l_ame_status := ame_util.approvedStatus;
501 elsif (l_result = 'REJECTED') then
502 l_ame_status := ame_util.rejectStatus;
503 else -- reject for lack of information, conservative approach
504 l_ame_status := ame_util.rejectStatus;
505 end if;
506 --Set approver as approved or rejected based on approver response
507 ame_api2.updateApprovalStatus2(applicationIdIn=>426,
508 transactionTypeIn=>'oracle.apps.eam.permit.release.approval',
509 transactionIdIn=>l_transaction_id,
510 approvalStatusIn => l_ame_status,
511 approverNameIn => l_approver_name);
512
513 ELSIF ( funcmode = 'TRANSFER' ) THEN
514
515 l_transaction_id := itemkey;
516 l_forwardeeIn.name :=WF_ENGINE.context_new_role;
517 l_original_approver_name:= WF_ENGINE.context_original_recipient;
518
519
520 ame_api2.updateApprovalStatus2(applicationIdIn=>426,
521 transactionTypeIn=>'oracle.apps.eam.permit.release.approval',
522 transactionIdIn=>l_transaction_id,
523 approvalStatusIn => 'FORWARD',
524 approverNameIn => l_original_approver_name,
525 forwardeeIn => l_forwardeeIn );
526
527 END IF; -- run
528
529 resultout:= wf_engine.eng_completed || ':' || l_result;
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 RAISE;
534 END Update_AME_With_Response;
535
536
537
538 /********************************************************************
539 * Procedure : Is_Approval_Required_Released
540 * Purpose : This procedure will check if the approval is required for
541 the work permit release
542 *********************************************************************/
543
544 PROCEDURE Is_Approval_Required_Released
545 ( p_old_wp_rec IN EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
546 , p_new_wp_rec IN EAM_PROCESS_SAFETY_PUB.eam_wp_header_rec_type
547 , x_approval_required OUT NOCOPY BOOLEAN
548 , x_workflow_name OUT NOCOPY VARCHAR2
549 , x_workflow_process OUT NOCOPY VARCHAR2
550 )IS
551 BEGIN
552
553 IF(p_new_wp_rec.status_type =3 AND --status is released
554 ((p_new_wp_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_CREATE) OR
555 ((p_new_wp_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE) AND
556 ((p_old_wp_rec.status_type IN (1,17,7) ) OR (p_old_wp_rec.status_type=6) ) ) )
557 )THEN
558 x_approval_required := TRUE;
559 x_workflow_name := 'EAMWPREL';
560 x_workflow_process := 'EAM_WP_RELEASE_APPROVAL';
561 END IF;
562
563 END Is_Approval_Required_Released;
564
565 END EAM_WORKPERMIT_WORKFLOW_PVT;
566
567