[Home] [Help]
PACKAGE BODY: APPS.EAM_WORKORDER_WORKFLOW_PVT
Source
1 PACKAGE BODY EAM_WORKORDER_WORKFLOW_PVT AS
2 /* $Header: EAMVWWFB.pls 120.0 2005/06/08 02:47:14 appldev noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(50):= 'EAM_WORKORDER_WORKFLOW_PVT';
5 g_module_name CONSTANT VARCHAR2(60):= 'eam.plsql.' || g_pkg_name;
6
7 /* Function called from subscription .This will in turn laucnh the workflow
8 */
9 function Launch_Workflow
10 (p_subscription_guid in raw,
11 p_event in out NOCOPY wf_event_t)
12 return varchar2
13 is
14 Debug_File UTL_FILE.FILE_TYPE;
15 x_return_status VARCHAR2(50);
16 l_param_list wf_parameter_list_t;
17 l_param wf_parameter_t;
18 l_param_idx NUMBER;
19 l_name VARCHAR2(200);
20 l_value VARCHAR2(200);
21 l_item_type VARCHAR2(200);
22 l_item_key VARCHAR2(200);
23 l_wf_process VARCHAR2(200);
24
25 begin
26
27 l_param_list:=p_event.getParameterList;
28
29 l_param_idx := l_param_list.FIRST;
30 while ( l_param_idx is not null) loop
31
32 l_param := l_param_list(l_param_idx);
33
34 IF(l_param.name = 'WORKFLOW_NAME') THEN
35 l_item_type := l_param.value;
36 END IF;
37
38 IF(l_param.name = 'WORKFLOW_PROCESS') THEN
39 l_wf_process := l_param.value;
40 END IF;
41
42 l_param_idx := l_param_list.NEXT(l_param_idx);
43 end loop;
44
45 IF(l_item_type IS NULL OR l_wf_process IS NULL) THEN
46 RETURN 'ERROR';
47 END IF;
48
49 l_item_key := p_event.getEventKey;
50
51 wf_engine.CreateProcess( itemtype =>l_item_type,
52 itemkey => l_item_key,
53 process => l_wf_process );
54
55
56 l_param_idx := l_param_list.FIRST;
57 while ( l_param_idx is not null) loop
58
59 l_param := l_param_list(l_param_idx);
60
61 BEGIN
62
63 wf_engine.SetItemAttrText( itemtype => l_item_type,
64 itemkey => l_item_key,
65 aname => l_param.name,
66 avalue => l_param.value);
67
68 EXCEPTION
69 WHEN OTHERS THEN --if attribute in event is not present in workflow...an exception will be thrown.
70 NULL;
71 END;
72
73 l_param_idx := l_param_list.NEXT(l_param_idx);
74 end loop;
75
76 wf_engine.StartProcess( itemtype => l_item_type,
77 itemkey => l_item_key);
78
79
80 x_return_status := 'SUCCESS';
81 return x_return_status;
82
83 exception
84 when others then
85 return 'ERROR';
86 end Launch_Workflow;
87
88 /*Procedure called from Work Order Release Approval when the workflow is approved*/
89 PROCEDURE Update_Status_Approved( itemtype in varchar2,
90 itemkey in varchar2,
91 actid in number,
92 funcmode in varchar2,
93 resultout out NOCOPY varchar2) is
94 l_wip_entity_id number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
95 itemkey => itemkey, aname => 'WIP_ENTITY_ID');
96 l_wip_entity_name varchar2(240):= wf_engine.GetItemAttrtext( itemtype => itemtype,
97 itemkey => itemkey, aname => 'WIP_ENTITY_NAME');
98 l_new_system_status number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
99 itemkey => itemkey, aname => 'NEW_SYSTEM_STATUS');
100 l_new_wo_status number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
101 itemkey => itemkey, aname => 'NEW_WO_STATUS');
102 l_organization_id number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
103 itemkey => itemkey, aname => 'ORGANIZATION_ID');
104 l_request_id number;
105 l_err_msg varchar2(2000);
106 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
107 l_status_error EXCEPTION;
108
109 l_workorder_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
110 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
111 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
112 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
113 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
114 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
115 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
116 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
117 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
118
119
120 l_eam_wo_rec_out EAM_PROCESS_WO_PUB.eam_wo_rec_type;
121 l_eam_op_tbl_out EAM_PROCESS_WO_PUB.eam_op_tbl_type;
122 l_eam_op_network_tbl_out EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
123 l_eam_res_tbl_out EAM_PROCESS_WO_PUB.eam_res_tbl_type;
124 l_eam_res_usage_tbl_out EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
125 l_eam_res_inst_tbl_out EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
126 l_eam_sub_res_tbl_out EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
127 l_eam_mat_req_tbl_out EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
128 l_eam_direct_items_tbl_out EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
129
130 l_output_dir VARCHAR2(512);
131 l_return_status VARCHAR2(1);
132 l_msg_count NUMBER;
133 BEGIN
134
135 If (funcmode = 'RUN') then
136
137
138 /* get output directory path from database */
139 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
140
141 l_workorder_rec.user_id := fnd_global.user_id;
142 l_workorder_rec.responsibility_id := fnd_global.resp_id;
143 l_workorder_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
144 l_workorder_rec.header_id := l_wip_entity_Id;
145 l_workorder_rec.batch_id := 1;
146 l_workorder_rec.wip_entity_id := l_wip_entity_Id;
147 l_workorder_rec.organization_id := l_organization_id;
148 l_workorder_rec.status_type := l_new_system_status;
149 l_workorder_rec.user_defined_status_id := l_new_wo_status;
150 l_workorder_rec.pending_flag := 'N';
151
152 EAM_PROCESS_WO_PUB.PROCESS_WO
153 ( p_init_msg_list =>TRUE
154 , p_commit => 'N'
155 , p_eam_wo_rec => l_workorder_rec
156 , p_eam_op_tbl => l_eam_op_tbl
157 , p_eam_op_network_tbl => l_eam_op_network_tbl
158 , p_eam_res_tbl => l_eam_res_tbl
159 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
160 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
161 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
162 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
163 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
164 , x_eam_wo_rec => l_eam_wo_rec_out
165 , x_eam_op_tbl => l_eam_op_tbl_out
166 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
167 , x_eam_res_tbl => l_eam_res_tbl_out
168 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
169 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
170 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
171 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
172 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
173 , x_return_status => l_return_status
174 , x_msg_count => l_msg_count
175 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
176 , p_output_dir => l_output_dir
177 , p_debug_filename => 'workflowapproved.log'
178 , p_debug_file_mode => 'W'
179 );
180
181 if nvl(l_return_status, 'S') <> 'S' then
182 l_return_status := FND_API.G_RET_STS_ERROR;
183 -- if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
184 -- FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
185 -- 'Error while releasing using EAM_WO_CHANGE_STATUS_PVT.Change_Status');
186 -- end if;
187 RAISE l_status_error;
188 ELSE
189 COMMIT;
190 end if;
191
192 resultout := 'COMPLETE:';
193 return;
194 end if;
195
196 if (funcmode = 'CANCEL') then
197 resultout := 'COMPLETE:';
198 return;
199 end if;
200
201 if (funcmode = 'TIMEOUT') then
202 resultout := 'COMPLETE:';
203 return;
204 end if;
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 wf_core.context('EAMWOREL','UPDATE_STATUS_APPROVED',
209 itemtype, itemkey, actid, funcmode);
210 raise;
211 END Update_Status_Approved;
212
213
214 /* Procedure called from Work Order Release Approval when the workflow is Rejected
215 */
216 PROCEDURE Update_Status_Rejected( itemtype in varchar2,
217 itemkey in varchar2,
218 actid in number,
219 funcmode in varchar2,
220 resultout out NOCOPY varchar2) is
221 l_wip_entity_id number := wf_engine.GetItemAttrNumber( itemtype => itemtype,
222 itemkey => itemkey, aname => 'WIP_ENTITY_ID');
223 l_wip_entity_name varchar2(240):= wf_engine.GetItemAttrtext( itemtype => itemtype,
224 itemkey => itemkey, aname => 'WIP_ENTITY_NAME');
225 l_organization_id number:= wf_engine.GetItemAttrNumber( itemtype => itemtype,
226 itemkey => itemkey, aname => 'ORGANIZATION_ID');
227 l_request_id number;
228 l_err_msg varchar2(2000);
229 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
230 l_status_error EXCEPTION;
231
232 l_workorder_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
233 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
234 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
235 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
236 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
237 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
238 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
239 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
240 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
241
242
243 l_eam_wo_rec_out EAM_PROCESS_WO_PUB.eam_wo_rec_type;
244 l_eam_op_tbl_out EAM_PROCESS_WO_PUB.eam_op_tbl_type;
245 l_eam_op_network_tbl_out EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
246 l_eam_res_tbl_out EAM_PROCESS_WO_PUB.eam_res_tbl_type;
247 l_eam_res_usage_tbl_out EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
248 l_eam_res_inst_tbl_out EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
249 l_eam_sub_res_tbl_out EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
250 l_eam_mat_req_tbl_out EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
251 l_eam_direct_items_tbl_out EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
252
253 l_output_dir VARCHAR2(512);
254 l_return_status VARCHAR2(1);
255 l_msg_count NUMBER;
256
257 BEGIN
258
259 If (funcmode = 'RUN') then
260
261
262 /* get output directory path from database */
263 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
264
265 l_workorder_rec.user_id := fnd_global.user_id;
266 l_workorder_rec.responsibility_id := fnd_global.resp_id;
267 l_workorder_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
268 l_workorder_rec.header_id := l_wip_entity_Id;
269 l_workorder_rec.batch_id := 1;
270 l_workorder_rec.wip_entity_id := l_wip_entity_Id;
271 l_workorder_rec.organization_id := l_organization_id;
272 l_workorder_rec.status_type :=7; --Cancelled
273 l_workorder_rec.user_defined_status_id := 99; --Cancelled by Approver
274 l_workorder_rec.pending_flag := 'N';
275
276 EAM_PROCESS_WO_PUB.PROCESS_WO
277 ( p_init_msg_list =>TRUE
278 , p_commit => 'N'
279 , p_eam_wo_rec => l_workorder_rec
280 , p_eam_op_tbl => l_eam_op_tbl
281 , p_eam_op_network_tbl => l_eam_op_network_tbl
282 , p_eam_res_tbl => l_eam_res_tbl
283 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
284 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
285 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
286 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
287 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
288 , x_eam_wo_rec => l_eam_wo_rec_out
289 , x_eam_op_tbl => l_eam_op_tbl_out
290 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
291 , x_eam_res_tbl => l_eam_res_tbl_out
292 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
293 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
294 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
295 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
296 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
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 => 'workflowapproved.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 -- if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
308 -- FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
309 -- 'Error while releasing using EAM_WO_CHANGE_STATUS_PVT.Change_Status');
310 -- end if;
311 RAISE l_status_error;
312 ELSE
313 COMMIT;
314 end if;
315
316 resultout := 'COMPLETE:';
317 return;
318 end if;
319
320 if (funcmode = 'CANCEL') then
321 resultout := 'COMPLETE:';
322 return;
323 end if;
324
325 if (funcmode = 'TIMEOUT') then
326 resultout := 'COMPLETE:';
327 return;
328 end if;
329
330
331 EXCEPTION
332 when others then
333 wf_core.context('EAMWOREL','UPDATE_STATUS_REJECTED',
334 itemtype, itemkey, actid, funcmode);
335 raise;
336 END Update_Status_Rejected;
337
338 /* Procedure called from Work Order Release Approval to find the next approver
339 */
340 procedure Get_Next_Approver(itemtype in varchar2,
341 itemkey in varchar2,
342 actid in number,
343 funcmode in varchar2,
344 resultout out NOCOPY varchar2) IS
345 E_FAILURE EXCEPTION;
346 l_transaction_id number;
347 l_next_approver ame_util.approverRecord2;
348 l_next_approvers ame_util.approversTable2;
349 l_next_approvers_count number;
350 l_is_approval_complete VARCHAR2(1);
351 l_transaction_type VARCHAR2(200);
352
353 BEGIN
354
355 IF (funcmode = 'RUN') THEN
356
357 l_transaction_id := TO_NUMBER(itemkey);
358 l_transaction_type := 'oracle.apps.eam.workorder.release.approval';
359
360 wf_engine.SetItemAttrText( itemtype => itemtype,
361 itemkey => itemkey,
362 aname => 'AME_TRANSACTION_TYPE',
363 avalue => l_transaction_type);
364
365 wf_engine.SetItemAttrText( itemtype => itemtype,
366 itemkey => itemkey,
367 aname => 'AME_TRANSACTION_ID',
368 avalue => l_transaction_id );
369
370 --flagApproversAsNotifiedIn is set to false, later we update 1st approver as notified
371 ame_api2.getNextApprovers4(applicationIdIn=>426,
372 transactionTypeIn=>l_transaction_type,
373 transactionIdIn=>l_transaction_id,
374 flagApproversAsNotifiedIn => ame_util.booleanFalse,
375 approvalProcessCompleteYNOut => l_is_approval_complete,
376 nextApproversOut=>l_next_approvers);
377
378 if (l_is_approval_complete = ame_util.booleanTrue) then
379 resultout:='COMPLETE:'||'APPROVAL_COMPLETE';
380 return;
381 end if;
382 l_next_approvers_count := l_next_approvers.count;
383 if (l_next_approvers_count >= 1) then
384 l_next_approver := l_next_approvers(1);
385 else
386 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
387 return;
388 end if;
389 IF l_next_approver.approval_status = ame_util.exceptionStatus THEN
390 raise E_FAILURE;
391 END IF;
392 IF ((l_next_approver.name is null) and
393 (l_next_approver.display_name is null) and
394 (l_next_approver.orig_system is null) and
395 (l_next_approver.orig_system_id is null)) THEN
396 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
397 return;
398 ELSE
399 wf_engine.SetItemAttrText( itemtype => itemType,
400 itemkey => itemkey,
401 aname => 'APPROVER_USER_NAME' ,
402 avalue => l_next_approver.name);
403
404 wf_engine.SetItemAttrText( itemtype => itemType,
405 itemkey => itemkey,
406 aname => 'APPROVER_DISPLAY_NAME' ,
407 avalue => l_next_approver.display_name);
408 if (l_next_approvers_count = 1) then
409 resultout:='COMPLETE:'||'VALID_APPROVER';
410 else --multiple next approvers exist, they should be notified in parallel
411 resultout:='COMPLETE:'||'VALID_PARALLEL_APPROVER';
412 end if;
413
414 --Set first approver as notified, workflow handles one at a time
415 ame_api2.updateApprovalStatus2(applicationIdIn=>426,
416 transactionTypeIn=>'oracle.apps.eam.workorder.release.approval',
417 transactionIdIn=>l_transaction_id,
418 approvalStatusIn => ame_util.notifiedStatus,
419 approverNameIn => l_next_approver.name);
420 return;
421 END IF; -- approver is not null
422 END IF; -- run
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 RAISE;
427 END Get_Next_Approver;
428
429 /* Procedure called from Work Order Release Approval when an approver responds to a notification
430 */
431 procedure Update_AME_With_Response(itemtype in varchar2,
432 itemkey in varchar2,
433 actid in number,
434 funcmode in varchar2,
435 resultout out NOCOPY varchar2) IS
436 E_FAILURE EXCEPTION;
437 l_transaction_id number;
438 l_nid number;
439 l_approver_name varchar2(240);
440 l_result varchar2(100);
441 l_ame_status varchar2(20);
442
443 BEGIN
444 IF (funcmode = 'RUN') THEN
445
446 l_transaction_id := itemkey;
447 l_nid := WF_ENGINE.context_nid;
448 l_approver_name := WF_NOTIFICATION.GetAttrText(nid => l_nid,
449 aname => 'APPROVER_USER_NAME');
450
451 l_result := Wf_Notification.GetAttrText(l_nid, 'RESULT');
452
453 if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
454
455 l_ame_status := ame_util.approvedStatus;
456 elsif (l_result = 'REJECTED') then
457 l_ame_status := ame_util.rejectStatus;
458 else -- reject for lack of information, conservative approach
459 l_ame_status := ame_util.rejectStatus;
460 end if;
461 --Set approver as approved or rejected based on approver response
462 ame_api2.updateApprovalStatus2(applicationIdIn=>426,
463 transactionTypeIn=>'oracle.apps.eam.workorder.release.approval',
464 transactionIdIn=>l_transaction_id,
465 approvalStatusIn => l_ame_status,
466 approverNameIn => l_approver_name);
467
468 END IF; -- run
469
470 resultout:= wf_engine.eng_completed || ':' || l_result;
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 RAISE;
475 END Update_AME_With_Response;
476
477
478 /* Procedure called from the public package 'EAM_WORKFLOW_DETAILS_PUB'
479 This procedure will launch the seeded workflow when status is changed to Released
480 */
481 PROCEDURE Is_Approval_Required_Released
482 (
483 p_old_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type,
484 p_new_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type,
485 x_approval_required OUT NOCOPY BOOLEAN,
486 x_workflow_name OUT NOCOPY VARCHAR2,
487 x_workflow_process OUT NOCOPY VARCHAR2
488 )
489 IS
490 BEGIN
491
492 IF(p_new_wo_rec.status_type =3 AND --status is released
493 ((p_new_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_CREATE) OR
494 ((p_new_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE) AND
495 ((p_old_wo_rec.status_type IN (1,17,7) ) OR (p_old_wo_rec.status_type=6 AND p_old_wo_rec.date_released IS NULL) ) ) )
496 )THEN
497 x_approval_required := TRUE;
498 x_workflow_name := 'EAMWOREL';
499 x_workflow_process := 'EAM_WO_RELEASE_APPROVAL';
500 END IF;
501
502 END Is_Approval_Required_Released;
503
504 END EAM_WORKORDER_WORKFLOW_PVT;