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