[Home] [Help]
PACKAGE BODY: APPS.CS_WORKFLOW_PUB
Source
1 PACKAGE BODY CS_Workflow_PUB AS
2 /* $Header: cspwfb.pls 120.2 2008/01/14 12:14:16 vpremach ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_Workflow_PUB';
5
6 /****************************************************************************
7 Forward Declaration
8 ****************************************************************************/
9
10 PROCEDURE Get_Employee_ID (
11 p_api_version IN NUMBER,
12 p_init_msg_list IN VARCHAR2 ,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_count OUT NOCOPY NUMBER,
15 p_msg_data OUT NOCOPY VARCHAR2,
16 p_api_name IN VARCHAR2,
17 p_employee_id IN NUMBER ,
18 p_emp_last_name IN VARCHAR2 ,
19 p_emp_first_name IN VARCHAR2 ,
20 p_employee_id_out OUT NOCOPY NUMBER );
21
22 ------------------------------------------------------------------------------
23 -- Procedure : Get_Fnd_User_Role
24 -- Description : Get the Workflow role of the given FND user
25 -- Parameters :
26 -- IN : p_fnd_user_id IN NUMBER Required
27 -- Corresponds to the column USER_ID in the table
28 -- FND_USER, and identifies the Oracle Applications user
29 -- OUT : x_role_name OUT VARCHAR2(100)
30 -- Workflow role name of the Applications user
31 -- x_role_display_name OUT VARCHAR2(100)
32 -- Workflow role display name of the Applications user
33 ------------------------------------------------------------------------------
34
35 PROCEDURE Get_Fnd_User_Role
36 ( p_fnd_user_id IN NUMBER,
37 x_role_name OUT NOCOPY VARCHAR2,
38 x_role_display_name OUT NOCOPY VARCHAR2 );
39
40 /****************************************************************************
41 API Procedure Bodies
42 ****************************************************************************/
43
44 -- -------------------------------------------------------------------
45 -- Launch_Servereq_Workflow
46 -- -------------------------------------------------------------------
47
48 PROCEDURE Launch_Servereq_Workflow (
49 p_api_version IN NUMBER,
50 p_init_msg_list IN VARCHAR2 ,
51 p_commit IN VARCHAR2 ,
52 p_return_status OUT NOCOPY VARCHAR2,
53 p_msg_count OUT NOCOPY NUMBER,
54 p_msg_data OUT NOCOPY VARCHAR2,
55 p_request_number IN VARCHAR2,
56 p_initiator_user_id IN NUMBER ,
57 p_initiator_resp_id IN NUMBER ,
58 p_initiator_resp_appl_id IN NUMBER ,
59 p_itemkey OUT NOCOPY VARCHAR2,
60 p_nowait IN VARCHAR2 ) IS
61
62 l_api_name CONSTANT VARCHAR2(30) := 'Launch_Servereq_Workflow';
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_administrator VARCHAR2(100);
65 l_dummy VARCHAR2(240);
66 l_wf_process_id NUMBER;
67 l_request_id NUMBER;
68 l_nowait BOOLEAN := FALSE;
69 l_workflow_proc VARCHAR2(30);
70 l_web_workflow VARCHAR2(30);
71 l_web_entry_flag VARCHAR2(1);
72 l_close_flag VARCHAR2(1);
73 l_msg_index_out NUMBER;
74 l_resp_id NUMBER;
75 l_resp_appl_id NUMBER;
76 l_useR_id NUMBER;
77
78 --add an out parametr to Update_ServiceRequest API for wf_process_id
79 out_wf_process_id NUMBER;
80
81 l_ADMINISTRATOR_NOT_SET EXCEPTION;
82 l_RESET_ADMINISTRATOR EXCEPTION;
83 l_WORKFLOW_IN_PROGRESS EXCEPTION;
84 l_SR_NO_WORKFLOW EXCEPTION;
85 l_SR_CLOSED_STATUS EXCEPTION;
86
87 CURSOR l_WorkflowProcID_csr IS
88 SELECT cs_wf_process_id_s.nextval
89 FROM dual;
90
91 CURSOR l_ServeReq_csr IS
92 SELECT inc.incident_id,
93 inc.workflow_process_id,
94 inc.object_version_number,
95 type.workflow,
96 status.close_flag
97 FROM cs_incidents_all_b inc,
98 cs_incident_types type,
99 cs_incident_statuses status
100 WHERE inc.incident_number = p_request_number
101 AND type.incident_type_id = inc.incident_type_id
102 AND status.incident_status_id = inc.incident_status_id;
103
104 CURSOR l_Wf_ItemType IS -- Bug 6449697
105 SELECT item_type
106 FROM wf_activities
107 WHERE name = l_workflow_proc
108 AND trunc(NVL(begin_date,sysdate)) <= trunc(sysdate)
109 AND trunc(NVL(end_date,sysdate)) >= trunc(sysdate);
110
111 -- fix for # 1348309
112 -- do not need to update from workflow API
113 -- FOR UPDATE OF workflow_process_id;
114
115 /*** This cursor is the same as above. rmanabat . 032403 .
116 CURSOR l_ServeReq_NW_csr IS
117 SELECT incident_id, workflow_process_id,object_version_number
118 FROM cs_incidents_all_b
119 WHERE incident_number = p_request_number;
120 ****/
121
122 --fix for # 1348309
123 -- do not need to update from workflow API
124 -- FOR UPDATE OF workflow_process_id NOWAIT;
125 l_itemtype VARCHAR2(50);
126 l_itemkey VARCHAR2(240);
127 l_return_status VARCHAR2(1);
128 l_msg_count NUMBER;
129 l_msg_data VARCHAR2(2000);
130 l_initiator_role VARCHAR2(100);
131
132 -- For audit record
133 -- This is obsolete. rmanabat 11/21/02
134 --l_change_flags_rec CS_ServiceRequest_PVT.audit_flags_rec_type;
135
136 l_service_request_rec CS_ServiceRequest_PVT.service_request_rec_type;
137 l_notes CS_SERVICEREQUEST_PVT.notes_table;
138 l_contacts CS_SERVICEREQUEST_PVT.contacts_table;
139 l_object_version_number number;
140 l_interaction_id number;
141
142
143
144 BEGIN
145 -- API savepoint
146 SAVEPOINT Launch_Workflow_PUB;
147
148 -- Check version number
149 IF NOT FND_API.Compatible_API_Call( l_api_version,
150 p_api_version,
151 l_api_name,
152 G_PKG_NAME ) THEN
153 raise FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 IF FND_API.to_Boolean( p_init_msg_list ) THEN
157 FND_MSG_PUB.initialize;
158 END IF;
159
160 -- Initialize return status to SUCCESS
161 p_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 -- Set nowait option
164 IF FND_API.to_Boolean( p_nowait ) THEN
165 l_nowait := TRUE;
166 END IF;
167
168 -- Get the Workflow Administrator Role
169 -- rmanabat 11/20/01 . Fix for bug 2102121
170 BEGIN
171 l_administrator := FND_PROFILE.VALUE('CS_WF_ADMINISTRATOR');
172 EXCEPTION
173 WHEN NO_DATA_FOUND THEN
174 null;
175 END;
176
177 IF (l_administrator IS NULL) THEN
178 raise l_ADMINISTRATOR_NOT_SET;
179 END IF;
180
181 -- Taking this validation out because of performance issues.
182 -- Just need to check if workflow administrator is set.
183 -- bug 2196135 . rmanabat 01/25/02
184 /****************************
185 ELSE
186 BEGIN
187
188 SELECT 'x' INTO l_dummy
189 FROM WF_ROLES
190 WHERE name = l_administrator;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 -- Invalid Administrator Role
195 raise l_RESET_ADMINISTRATOR;
196
197 WHEN TOO_MANY_ROWS THEN
198 -- Okay here
199 null;
200 END;
201 END IF;
202 ***********************************/
203
204 /**** These cursors are the same . rmanabat 032403. ****/
205 /****
206 -- Get the last workflow process ID of this request and lock the record
207 IF ( l_nowait = TRUE ) THEN
208 OPEN l_ServeReq_NW_csr;
209 FETCH l_ServeReq_NW_csr INTO l_request_id, l_wf_process_id,
210 l_object_version_number;
211 ELSE
212 OPEN l_ServeReq_csr;
213 FETCH l_ServeReq_csr INTO l_request_id, l_wf_process_id,
214 l_object_version_number;
215 END IF;
216 ****/
217
218 OPEN l_ServeReq_csr;
219 FETCH l_ServeReq_csr
220 INTO l_request_id, l_wf_process_id, l_object_version_number,
221 l_workflow_proc, l_close_flag;
222 CLOSE l_ServeReq_csr;
223
224 -- Verify that the workflow is not active
225 IF (l_wf_process_id IS NOT NULL) THEN
226 IF (CS_Workflow_PKG.Is_Servereq_Item_Active(
227 p_request_number => p_request_number,
228 p_wf_process_id => l_wf_process_id ) = 'Y') THEN
229 raise l_WORKFLOW_IN_PROGRESS;
230 END IF;
231 END IF;
232
233 -- nmhatre 03/24/2000
234 -- commented out to take care of bug# 1213076.
235 -- We are not using web workflow any more so we need not select
236 -- web_workflow and sr_creation_channel columns.
237
238 -- Get the workflow process name for this request from the request type
239 -- SELECT type.workflow, type.web_workflow, inc.sr_creation_channel
240 -- INTO l_workflow_proc, l_web_workflow, l_web_entry_flag
241 -- FROM cs_incident_types type, cs_incidents_all_vl inc
242 -- WHERE inc.incident_number = p_request_number
243 -- AND inc.incident_type_id = type.incident_type_id;
244
245 -- Get the workflow process name for this request from the request type
246
247 /**** Combining this with the l_ServeReq_csr cursor above. ***/
248 /**** rmanabat. 032403 .
249 SELECT type.workflow
250 INTO l_workflow_proc
251 FROM cs_incident_types type, cs_incidents_all_vl inc
252 WHERE inc.incident_number = p_request_number
253 AND inc.incident_type_id = type.incident_type_id;
254 ****/
255
256 -- Select workflow specific to web service requests
257 -- IF (l_web_entry_flag = 'Y') THEN
258 -- l_workflow_proc := l_web_workflow;
259 -- END IF;
260
261 IF (l_workflow_proc IS NULL) THEN
262 raise l_SR_NO_WORKFLOW;
263 END IF;
264
265 -- Verify that the status of the service request is not 'Closed'
266
267 /**** Combining this with the l_ServeReq_csr cursor above. ***/
268 /**** rmanabat. 032403 .
269 SELECT status.close_flag INTO l_close_flag
270 FROM cs_incident_statuses status, cs_incidents_all_vl inc
271 WHERE inc.incident_number = p_request_number
272 AND inc.incident_status_id = status.incident_status_id;
273 ****/
274
275 IF (l_close_flag = 'Y') THEN
276 raise l_SR_CLOSED_STATUS;
277 END IF;
278
279 -- Get the new workflow process ID
280 OPEN l_WorkflowProcID_csr;
281 FETCH l_WorkflowProcID_csr INTO l_wf_process_id;
282 CLOSE l_WorkflowProcID_csr;
283
284 -- Construct the unique item key
285 l_itemkey := p_request_number||'-'||to_char(l_wf_process_id);
286 --dbms_output.put_line('item-key ' || l_itemkey);
287
288 -- Get the process initiator's workflow role name
289 IF (p_initiator_user_id IS NOT NULL) THEN
290 get_fnd_user_role
291 ( p_fnd_user_id => p_initiator_user_id,
292 x_role_name => l_initiator_role,
293 x_role_display_name => l_dummy );
294 END IF;
295
296 IF p_initiator_user_id = -1 OR
297 p_initiator_user_id IS NULL OR
298 p_initiator_user_id = FND_API.G_MISS_NUM THEN
299 l_user_id := FND_GLOBAL.User_Id;
300 ELSE
301 l_user_id := p_initiator_user_id;
302 END IF;
303
304
305 IF p_initiator_resp_id = -1 OR
306 p_initiator_resp_id IS NULL OR
307 p_initiator_resp_id = FND_API.G_MISS_NUM THEN
308 l_resp_id := FND_GLOBAL.resp_Id;
309 ELSE
310 l_resp_id := p_initiator_resp_id;
311 END IF;
312
313
314 IF p_initiator_resp_appl_id = -1 OR
315 p_initiator_resp_appl_id IS NULL OR
316 p_initiator_resp_appl_id = FND_API.G_MISS_NUM THEN
317 l_resp_appl_id := FND_GLOBAL.resp_appl_Id;
318 ELSE
319 l_resp_appl_id := p_initiator_resp_appl_id;
320 END IF;
321
322 --Begin Bug# 6449697
323 OPEN l_Wf_ItemType;
324 FETCH l_Wf_ItemType into l_itemtype;
325 CLOSE l_Wf_ItemType;
326 -- Create and launch the Workflow process
327 WF_ENGINE.CreateProcess(
328 itemtype => l_itemtype,
329 itemkey => l_itemkey,
330 process => l_workflow_proc );
331
332 WF_ENGINE.SetItemAttrText(
333 itemtype => l_itemtype,
334 itemkey => l_itemkey,
335 aname => 'USER_ID',
336 avalue => l_user_id );
337
338 WF_ENGINE.SetItemAttrText(
339 itemtype => l_itemtype,
340 itemkey => l_itemkey,
341 aname => 'RESP_ID',
342 avalue => l_resp_id );
343
344 WF_ENGINE.SetItemAttrText(
345 itemtype => l_itemtype,
346 itemkey => l_itemkey,
347 aname => 'RESP_APPL_ID',
348 avalue => l_resp_appl_id );
349
350 IF l_itemtype = 'SERVEREQ' THEN
351 WF_ENGINE.SetItemAttrText(
352 itemtype => l_itemtype,
353 itemkey => l_itemkey,
354 aname => 'INITIATOR_ROLE',
355 avalue => l_initiator_role );
356
357 WF_ENGINE.SetItemAttrText(
358 itemtype => l_itemtype,
359 itemkey => l_itemkey,
360 aname => 'WF_ADMINISTRATOR',
361 avalue => l_administrator );
362 END IF;
363
364 wf_engine.setitemowner
365 ( itemtype => l_itemtype,
366 itemkey => l_itemkey,
367 owner => l_initiator_role );
368
369 WF_ENGINE.StartProcess(
370 itemtype => l_itemtype,
371 itemkey => l_itemkey );
372
373 --Commented for bug 6449697
374 -- Create and launch the Workflow process
375 /*WF_ENGINE.CreateProcess(
376 itemtype => 'SERVEREQ',
377 itemkey => l_itemkey,
378 process => l_workflow_proc );
379
380 WF_ENGINE.SetItemAttrText(
381 itemtype => 'SERVEREQ',
382 itemkey => l_itemkey,
383 aname => 'INITIATOR_ROLE',
384 avalue => l_initiator_role );
385
386 WF_ENGINE.SetItemAttrText(
387 itemtype => 'SERVEREQ',
388 itemkey => l_itemkey,
389 aname => 'USER_ID',
390 avalue => l_user_id ); --5042407
391
392 WF_ENGINE.SetItemAttrText(
393 itemtype => 'SERVEREQ',
394 itemkey => l_itemkey,
395 aname => 'RESP_ID',
396 avalue => l_resp_id ); --5042407
397
398 WF_ENGINE.SetItemAttrText(
399 itemtype => 'SERVEREQ',
400 itemkey => l_itemkey,
401 aname => 'RESP_APPL_ID',
402 avalue => l_resp_appl_id ); --5042407
403
404 WF_ENGINE.SetItemAttrText(
405 itemtype => 'SERVEREQ',
406 itemkey => l_itemkey,
407 aname => 'WF_ADMINISTRATOR',
408 avalue => l_administrator );
409
410 wf_engine.setitemowner
411 ( itemtype => 'SERVEREQ',
412 itemkey => l_itemkey,
413 owner => l_initiator_role );
414
415 WF_ENGINE.StartProcess(
416 itemtype => 'SERVEREQ',
417 itemkey => l_itemkey );*/
418
419
420 /****** Fix for Bug # 1348309 & #1349526
421 The error is b'cos the create_audit API has been modified
422 We will no longer directly update the cs_incidents_all_b table.
423 We will be calling the Service request Update API to do the update.
424 This API will call the create audit API so we do not have to call this either
425
426 -- Update the workflow process ID of the request
427
428 IF (l_nowait = TRUE) THEN
429
430 UPDATE CS_INCIDENTS_ALL_B
431 SET workflow_process_id = l_wf_process_id
432 WHERE CURRENT OF l_ServeReq_NW_csr;
433 CLOSE l_ServeReq_NW_csr;
434
435 ELSE
436
437 UPDATE CS_INCIDENTS_ALL_B
438 SET workflow_process_id = l_wf_process_id
439 WHERE incident_number = p_request_number;
440
441 -- WHERE CURRENT OF l_ServeReq_csr;
442 CLOSE l_ServeReq_csr;
443
444 END IF;
445
446 -- Insert audit record
447 l_change_flags_rec.new_workflow := FND_API.G_TRUE;
448
449 CS_ServiceRequest_PVT.Create_Audit_Record (
450 p_api_version => 2.0,
451 p_init_msg_list => FND_API.G_FALSE,
452 p_commit => FND_API.G_FALSE,
453 x_return_status => l_return_status,
454 x_msg_count => l_msg_count,
455 x_msg_data => l_msg_data,
456 p_request_id => l_request_id,
457 p_change_flags => l_change_flags_rec,
458 p_wf_process_name => l_workflow_proc,
459 p_wf_process_itemkey => l_itemkey,
460 p_user_id => p_initiator_user_id );
461 ******/
462 --#1349526
463 --Jul/13/2000
464 -- Call the SR update API to update cs_incidents_all_b and to insert
465 -- into audit tables
466
467 --dbms_output.put_line('before Update wf_proc_id= ' || l_wf_process_id);
468 --dbms_output.put_line('before Update l_req_id= ' || l_request_id);
469
470 -- initialise
471
472 /**** No longer needed since we are going to explicitly update ****/
473 /**** the service request's workflow process id. This therefore ****/
474 /**** will not create an audit record for this update. Also, ****/
475 /**** the update API does not handle the p_called_by_workflow ****/
476 /**** anymore. rmanabat 032403 ****/
477 -- CS_ServiceRequest_PVT.initialize_rec(l_service_request_rec);
478
479 -- NOtes :
480 -- We do not pass p-commit to true to the update SR, since
481 -- we are explicitly doing a commit here
482 -- Also, object_version_number will not get incremented in the SR update
483 -- API when it is being called from workflow as it is
484 -- conflicting with an update issued from the main SR form
485 -- The SR update API takes care of this based on the
486 -- p_called_by_workflow parameter we pass. This should be true.
487 --Update SR API also has corresponding changes
488
489 --Nov/8/2000
490 --added a new out parameter to this API call,
491 --X_workflow_process_id since Update_ServiceRequest
492 --API has been changed
493
494 -- Added mandatory parameter last_update_program_code .11/19/02 rmanabat
495 /**** No longer needed . rmanabat 032403 ****/
496 --l_service_request_rec.last_update_program_code := 'SUPPORT.WF';
497
498 /**** No longer needed since we are going to explicitly update ****/
499 /**** the service request's workflow process id. This therefore ****/
500 /**** will not create an audit record for this update. Also, ****/
501 /**** the update API does not handle the p_called_by_workflow ****/
502 /**** anymore. rmanabat 032403 ****/
503
504 /******************
505 CS_ServiceRequest_PVT.Update_ServiceRequest
506 ( p_api_version => 3.0, -- Changed from 2.0 for 11.5.9
507 p_init_msg_list => fnd_api.g_false,
508 p_commit => fnd_api.g_false,
509 p_validation_level => fnd_api.g_valid_level_full,
510 x_return_status => l_return_status,
511 x_msg_count => l_msg_count,
512 x_msg_data => l_msg_data,
513 p_request_id => l_request_id,
514 p_object_version_number => l_object_version_number,
515 p_last_updated_by => p_initiator_user_id,
516 p_last_update_date => sysdate,
517 p_service_request_rec => l_service_request_rec,
518 p_notes => l_notes,
519 p_contacts => l_contacts,
520 p_called_by_workflow => FND_API.G_TRUE,
521 p_workflow_process_id => l_wf_process_id,
522 x_interaction_id => l_interaction_id,
523 x_workflow_process_id => out_wf_process_id
524 );
525
526 -- dbms_output.put_line('after Update call status' || l_return_status);
527
528 -- Check for possible errors returned by the API
529 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
530 raise FND_API.G_EXC_ERROR;
531 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
532 raise FND_API.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535 ***************************/
536
537 /**** Replaced Update API above with explicit update ****/
538 /**** rmanabat . 032403 ****/
539
540 UPDATE CS_INCIDENTS_ALL_B
541 SET workflow_process_id = l_wf_process_id
542 WHERE incident_id = l_request_id;
543
544
545 -- Set up return value
546 p_itemkey := l_itemkey;
547
548 /***
549 IF (FND_API.To_Boolean( p_commit ) and
550 l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
551 COMMIT WORK;
552 END IF;
553 ***/
554
555 IF (FND_API.To_Boolean( p_commit )) THEN
556 COMMIT WORK;
557 END IF;
558
559 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
560 p_data => p_msg_data,
561 p_encoded => FND_API.G_FALSE );
562
563 EXCEPTION
564 WHEN l_SR_NO_WORKFLOW THEN
565 ROLLBACK TO Launch_Workflow_PUB;
566 p_return_status := FND_API.G_RET_STS_ERROR;
567 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
568 FND_MESSAGE.SET_NAME('CS', 'CS_SR_NO_WORKFLOW');
569 FND_MSG_PUB.Add;
570 END IF;
571 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
572 p_data => p_msg_data,
573 p_encoded => FND_API.G_FALSE );
574
575 WHEN l_SR_CLOSED_STATUS THEN
576 ROLLBACK TO Launch_Workflow_PUB;
577 p_return_status := FND_API.G_RET_STS_ERROR;
578 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
579 FND_MESSAGE.SET_NAME('CS', 'CS_API_SR_WF_CLOSED_STATUS');
580 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME||'.'||l_api_name);
581 FND_MSG_PUB.Add;
582 END IF;
583 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
584 p_data => p_msg_data,
585 p_encoded => FND_API.G_FALSE );
586
587 WHEN l_ADMINISTRATOR_NOT_SET THEN
588 ROLLBACK TO Launch_Workflow_PUB;
589 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
591 FND_MESSAGE.SET_NAME('CS', 'CS_ALL_WF_ADMINISTRATOR');
592 FND_MSG_PUB.Add;
593 END IF;
594 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
595 p_data => p_msg_data,
596 p_encoded => FND_API.G_FALSE );
597
598 WHEN l_RESET_ADMINISTRATOR THEN
599 ROLLBACK TO Launch_Workflow_PUB;
600 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
602 FND_MESSAGE.SET_NAME('CS', 'CS_ALL_RESET_WF_ADMINI');
603 FND_MSG_PUB.Add;
604 END IF;
605 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
606 p_data => p_msg_data,
607 p_encoded => FND_API.G_FALSE );
608
609 WHEN l_WORKFLOW_IN_PROGRESS THEN
610 /****
611 IF (l_ServeReq_NW_csr%ISOPEN) THEN
612 CLOSE l_ServeReq_NW_csr;
613 ELSIF (l_ServeReq_csr%ISOPEN) THEN
614 CLOSE l_ServeReq_csr;
615 END IF;
616 ****/
617 IF (l_ServeReq_csr%ISOPEN) THEN
618 CLOSE l_ServeReq_csr;
619 END IF;
620
621 ROLLBACK TO Launch_Workflow_PUB;
622 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
624 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKFLOW_IN_PROGRESS');
625 FND_MSG_PUB.Add;
626 END IF;
627 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
628 p_data => p_msg_data,
629 p_encoded => FND_API.G_FALSE );
630
631 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
632 ROLLBACK TO Launch_Workflow_PUB;
633 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
635 FND_MESSAGE.Set_Name('CS', 'CS_SR_WF_RECORD_LOCKED');
636 FND_MSG_PUB.Add;
637 END IF;
638 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
639 p_data => p_msg_data,
640 p_encoded => FND_API.G_FALSE );
641
642 WHEN FND_API.G_EXC_ERROR THEN
643 /****
644 IF (l_ServeReq_NW_csr%ISOPEN) THEN
645 CLOSE l_ServeReq_NW_csr;
646 ELSIF (l_ServeReq_csr%ISOPEN) THEN
647 CLOSE l_ServeReq_csr;
648 END IF;
649 ****/
650 IF (l_ServeReq_csr%ISOPEN) THEN
651 CLOSE l_ServeReq_csr;
652 END IF;
653
654 ROLLBACK TO Launch_Workflow_PUB;
655 p_return_status := FND_API.G_RET_STS_ERROR;
656 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
657 p_data => p_msg_data,
658 p_encoded => FND_API.G_FALSE );
659
660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
661 /****
662 IF (l_ServeReq_NW_csr%ISOPEN) THEN
663 CLOSE l_ServeReq_NW_csr;
664 ELSIF (l_ServeReq_csr%ISOPEN) THEN
665 CLOSE l_ServeReq_csr;
666 END IF;
667 ****/
668 IF (l_ServeReq_csr%ISOPEN) THEN
669 CLOSE l_ServeReq_csr;
670 END IF;
671
672 ROLLBACK TO Launch_Workflow_PUB;
673 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
674 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
675 p_data => p_msg_data,
676 p_encoded => FND_API.G_FALSE );
677
678 WHEN OTHERS THEN
679 /****
680 IF (l_ServeReq_NW_csr%ISOPEN) THEN
681 CLOSE l_ServeReq_NW_csr;
682 ELSIF (l_ServeReq_csr%ISOPEN) THEN
683 CLOSE l_ServeReq_csr;
684 END IF;
685 ****/
686 IF (l_ServeReq_csr%ISOPEN) THEN
687 CLOSE l_ServeReq_csr;
688 END IF;
689
690 ROLLBACK TO Launch_Workflow_PUB;
691 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
693 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
694 l_api_name );
695 END IF;
696 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
697 p_data => p_msg_data,
698 p_encoded => FND_API.G_FALSE );
699
700 END Launch_Servereq_Workflow;
701
702
703 -- -------------------------------------------------------------------
704 -- Cancel_Servereq_Workflow
705 -- -------------------------------------------------------------------
706
707 PROCEDURE Cancel_Servereq_Workflow (
708 p_api_version IN NUMBER,
709 p_init_msg_list IN VARCHAR2 ,
710 p_commit IN VARCHAR2 ,
711 p_return_status OUT NOCOPY VARCHAR2,
712 p_msg_count OUT NOCOPY NUMBER,
713 p_msg_data OUT NOCOPY VARCHAR2,
714 p_request_number IN VARCHAR2,
715 p_wf_process_id IN NUMBER,
716 p_user_id IN NUMBER ) IS
717
718 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_Servereq_Workflow';
719 l_api_version CONSTANT NUMBER := 1.0;
720 l_itemkey VARCHAR2(240);
721 l_user_name VARCHAR2(100);
722 l_emp_name VARCHAR2(240);
723 l_aborted_by VARCHAR2(240);
724 l_owner_role VARCHAR2(100);
725 l_notification_id NUMBER;
726 l_dummy NUMBER := -1;
727 l_context VARCHAR2(100);
728 l_NOT_ACTIVE EXCEPTION;
729 l_itemtype VARCHAR2(10); --Bug 6449697
730
731 CURSOR l_itemtype_csr IS --Bug 6449697
732 Select item_type
733 from cs_incidents_all_b inc, cs_incident_types types, wf_activities wf
734 where inc.incident_number= p_request_number
735 and types.incident_type_id=inc.incident_type_id
736 and wf.name=types.workflow;
737
738 BEGIN
739 -- API savepoint
740 SAVEPOINT Cancel_Workflow_PUB;
741
742 -- Check version number
743 IF NOT FND_API.Compatible_API_Call( l_api_version,
744 p_api_version,
745 l_api_name,
746 G_PKG_NAME ) THEN
747 raise FND_API.G_EXC_UNEXPECTED_ERROR;
748 END IF;
749
750 IF FND_API.to_Boolean( p_init_msg_list ) THEN
751 FND_MSG_PUB.initialize;
752 END IF;
753
754 -- Initialize return status to SUCCESS
755 p_return_status := FND_API.G_RET_STS_SUCCESS;
756
757 --
758 -- First construct the item key
759 -- If we ever change the format of the itemkey, the following code
760 -- must be updated
761 --
762 l_itemkey := p_request_number||'-'||to_char(p_wf_process_id);
763
764 --
765 -- Make sure that the item is still active
766 --
767 IF (CS_Workflow_PKG.Is_Servereq_Item_Active (
768 p_request_number => p_request_number,
769 p_wf_process_id => p_wf_process_id ) = 'N') THEN
770 raise l_NOT_ACTIVE;
771 END IF;
772
773 --
774 -- Get the employee name of the user who is aborting the process
775 -- If we can't get that information, just use the FND username
776 --
777 SELECT fnd.user_name, emp.full_name
778 INTO l_user_name, l_emp_name
779 FROM fnd_user fnd, per_people_x emp
780 WHERE fnd.user_id = p_user_id
781 AND fnd.employee_id = emp.person_id (+);
782
783 IF (l_emp_name IS NOT NULL) THEN
784 l_aborted_by := l_emp_name;
785 ELSE
786 l_aborted_by := l_user_name;
787 END IF;
788 --Bug 6449697
789 OPEN l_itemtype_csr;
790 FETCH l_itemtype_csr into l_itemtype;
791 CLOSE l_itemtype_csr;
792
793 IF l_itemtype = 'SERVEREQ' THEN ----Bug 6449697
794 -- Call Workflow API to abort the process
795 WF_ENGINE.AbortProcess(
796 itemtype => 'SERVEREQ',
797 itemkey => l_itemkey );
798
799 -- Notify the current owner that the process has been aborted
800 l_owner_role := WF_ENGINE.GetItemAttrText(
801 itemtype => 'SERVEREQ',
802 itemkey => l_itemkey,
803 aname => 'OWNER_ROLE' );
804
805 -- Set up the context information for the callback function
806 l_context := 'SERVEREQ'||':'||l_itemkey||':'||to_char(l_dummy);
807
808 IF (l_owner_role IS NOT NULL) THEN
809
810 -- Note that we're using Workflow engine's callback function
811 l_notification_id := WF_Notification.Send(
812 role => l_owner_role,
813 msg_type => 'SERVEREQ',
814 msg_name => 'ABORT_MESG',
815 callback => 'WF_ENGINE.CB',
816 context => l_context );
817
818 WF_Notification.SetAttrText(
819 nid => l_notification_id,
820 aname => 'ABORT_USER',
821 avalue => l_aborted_by );
822 END IF;
823 --Bug 6449697
824 ELSIF l_itemtype = 'EAMSRAPR' THEN
825 WF_ENGINE.AbortProcess(
826 itemtype => 'EAMSRAPR',
827 itemkey => l_itemkey );
828 END IF;
829
830 IF FND_API.To_Boolean( p_commit ) THEN
831 COMMIT WORK;
832 END IF;
833
834 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
835 p_data => p_msg_data,
836 p_encoded => FND_API.G_FALSE );
837
838 EXCEPTION
839 WHEN l_NOT_ACTIVE THEN
840 ROLLBACK TO Cancel_Workflow_PUB;
841 p_return_status := FND_API.G_RET_STS_ERROR;
842 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
843 FND_MESSAGE.SET_NAME('CS', 'CS_SR_WORKFLOW_NOT_ACTIVE');
844 FND_MSG_PUB.Add;
845 END IF;
846 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
847 p_data => p_msg_data,
848 p_encoded => FND_API.G_FALSE );
849
850 WHEN OTHERS THEN
851 ROLLBACK TO Cancel_Workflow_PUB;
852 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
854 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
855 l_api_name );
856 END IF;
857 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
858 p_data => p_msg_data,
859 p_encoded => FND_API.G_FALSE );
860
861 END Cancel_Servereq_Workflow;
862
863
864 -- --------------------------------------------------------------------------------------
865 -- Decode_Servereq_Itemkey
866 --
867 -- A Service Request itemkey has the following format:
868 --
869 -- ' <Service Request Number>-<Workflow Process ID>'
870 --
871 -- For example, service request #100 with Workflow process ID of 200
872 -- has the following itemkey:
873 --
874 -- '100-200'
875 --
876 -- Date Name Desc
877 -- ---------- --------- ------------------------------------------------------------
878 -- 26-OCT-2005 aneemuch Changed l_pos := INSTR(l_itemkey, '-') to
879 -- l_dash_pos := instr(p_itemkey, '-',-1,1), per bug 4007088.
880 --
881 -- --------------------------------------------------------------------------------------
882
883 PROCEDURE Decode_Servereq_Itemkey(
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2 ,
886 p_return_status OUT NOCOPY VARCHAR2,
887 p_msg_count OUT NOCOPY NUMBER,
888 p_msg_data OUT NOCOPY VARCHAR2,
889 p_itemkey IN VARCHAR2,
890 p_request_number OUT NOCOPY VARCHAR2,
891 p_wf_process_id OUT NOCOPY NUMBER ) is
892
893 l_api_name CONSTANT VARCHAR2(30) := 'Decode_Servereq_Itemkey';
894 l_api_version CONSTANT NUMBER := 1.0;
895
896 l_dash_pos NUMBER;
897 l_INVALID_ITEMKEY EXCEPTION;
898
899 BEGIN
900 -- Check version number
901 IF NOT FND_API.Compatible_API_Call( l_api_version,
902 p_api_version,
903 l_api_name,
904 G_PKG_NAME ) THEN
905 raise FND_API.G_EXC_UNEXPECTED_ERROR;
906 END IF;
907
908 IF FND_API.to_Boolean( p_init_msg_list ) THEN
909 FND_MSG_PUB.initialize;
910 END IF;
911
912 -- Initialize return value
913 p_return_status := FND_API.G_RET_STS_SUCCESS;
914
915 --l_dash_pos := instr(p_itemkey, '-');
916 l_dash_pos := instr(p_itemkey, '-',-1,1); -- Bug # 4007088
917
918 IF (l_dash_pos = 0) THEN
919 raise l_INVALID_ITEMKEY;
920 END IF;
921
922 p_request_number := substr(p_itemkey, 1, l_dash_pos - 1);
923 p_wf_process_id := to_number(substr(p_itemkey,
924 l_dash_pos + 1,
925 length(p_itemkey) - l_dash_pos));
926
927 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
928 p_data => p_msg_data );
929
930 EXCEPTION
931 WHEN l_INVALID_ITEMKEY THEN
932 p_return_status := FND_API.G_RET_STS_ERROR;
933 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
934 FND_MESSAGE.SET_NAME('CS', 'CS_API_SR_INVALID_ARGUMENT');
935 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME||'.'||l_api_name);
936 FND_MESSAGE.SET_TOKEN('VALUE', p_itemkey);
937 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_ITEMKEY');
938 FND_MSG_PUB.Add;
939 END IF;
940 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
941 p_data => p_msg_data );
942
943 WHEN FND_API.G_EXC_ERROR THEN
944 p_return_status := FND_API.G_RET_STS_ERROR;
945 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
946 p_data => p_msg_data );
947
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
951 p_data => p_msg_data );
952
953 WHEN OTHERS THEN
954 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
956 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
957 l_api_name );
958 END IF;
959 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
960 p_data => p_msg_data );
961
962 END Decode_Servereq_Itemkey;
963
964
965 -- -------------------------------------------------------------------
966 -- Encode_Servereq_Itemkey
967 --
968 -- A Service Request itemkey has the following format:
969 --
970 -- ' <Service Request Number>-<Workflow Process ID>'
971 --
972 -- For example, service request #100 with Workflow process ID of 200
973 -- has the following itemkey:
974 --
975 -- '100-200'
976 --
977 -- -------------------------------------------------------------------
978
979 PROCEDURE Encode_Servereq_Itemkey(
980 p_api_version IN NUMBER,
981 p_init_msg_list IN VARCHAR2 ,
982 p_return_status OUT NOCOPY VARCHAR2,
983 p_msg_count OUT NOCOPY NUMBER,
984 p_msg_data OUT NOCOPY VARCHAR2,
985 p_request_number IN VARCHAR2,
986 p_wf_process_id IN NUMBER,
987 p_itemkey OUT NOCOPY VARCHAR2 ) IS
988
989 l_api_name CONSTANT VARCHAR2(30) := 'Encode_Servereq_Itemkey';
990 l_api_version CONSTANT NUMBER := 1.0;
991 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
992
993 BEGIN
994 -- Check version number
995 IF NOT FND_API.Compatible_API_Call( l_api_version,
996 p_api_version,
997 l_api_name,
998 G_PKG_NAME ) THEN
999 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1000 END IF;
1001
1002 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1003 FND_MSG_PUB.initialize;
1004 END IF;
1005
1006 -- Initialize return value
1007 p_return_status := FND_API.G_RET_STS_SUCCESS;
1008
1009 -- Validate arguments
1010 If (p_request_number IS NULL) THEN
1011 CS_ServiceRequest_UTIL.Add_Null_Parameter_Msg(
1012 p_token_an => l_api_name_full,
1013 p_token_np => 'p_request_number' );
1014 raise FND_API.G_EXC_ERROR;
1015 ELSIF (p_wf_process_id IS NULL) THEN
1016 CS_ServiceRequest_UTIL.Add_Null_Parameter_Msg(
1017 p_token_an => l_api_name_full,
1018 p_token_np => 'p_wf_process_id' );
1019 raise FND_API.G_EXC_ERROR;
1020 END IF;
1021
1022 p_itemkey := p_request_number||'-'||to_char(p_wf_process_id);
1023
1024 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1025 p_data => p_msg_data );
1026
1027 EXCEPTION
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 p_return_status := FND_API.G_RET_STS_ERROR;
1030 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1031 p_data => p_msg_data );
1032
1033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1034 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1036 p_data => p_msg_data );
1037
1038 WHEN OTHERS THEN
1039 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1040 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1041 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1042 l_api_name );
1043 END IF;
1044 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1045 p_data => p_msg_data );
1046
1047 END Encode_Servereq_Itemkey;
1048
1049
1050 -- -------------------------------------------------------------------
1051 -- Get_Employee_Role
1052 -- -------------------------------------------------------------------
1053
1054 PROCEDURE Get_Employee_Role (
1055 p_api_version IN NUMBER,
1056 p_init_msg_list IN VARCHAR2 ,
1057 p_return_status OUT NOCOPY VARCHAR2,
1058 p_msg_count OUT NOCOPY NUMBER,
1059 p_msg_data OUT NOCOPY VARCHAR2,
1060 p_employee_id IN NUMBER ,
1061 p_emp_last_name IN VARCHAR2 ,
1062 p_emp_first_name IN VARCHAR2 ,
1063 p_role_name OUT NOCOPY VARCHAR2,
1064 p_role_display_name OUT NOCOPY VARCHAR2 ) IS
1065
1066 l_api_name CONSTANT VARCHAR2(30) := 'Get_Employee_Role';
1067 l_api_version CONSTANT NUMBER := 1.0;
1068 l_return_status VARCHAR2(1);
1069 l_msg_count NUMBER;
1070 l_msg_data VARCHAR2(2000);
1071 l_employee_id NUMBER;
1072
1073 BEGIN
1074 -- Check version number
1075 IF NOT FND_API.Compatible_API_Call( l_api_version,
1076 p_api_version,
1077 l_api_name,
1078 G_PKG_NAME ) THEN
1079 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1080 END IF;
1081
1082 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1083 FND_MSG_PUB.initialize;
1084 END IF;
1085
1086 -- Initialize return status to SUCCESS
1087 p_return_status := FND_API.G_RET_STS_SUCCESS;
1088
1089 -- Validate the parameters and get the employee ID
1090 Get_Employee_ID(
1091 p_api_version => 1.0,
1092 p_init_msg_list => FND_API.G_FALSE,
1093 p_return_status => l_return_status,
1094 p_msg_count => l_msg_count,
1095 p_msg_data => l_msg_data,
1096 p_api_name => G_PKG_NAME||'.'||l_api_name,
1097 p_employee_id => p_employee_id,
1098 p_emp_last_name => p_emp_last_name,
1099 p_emp_first_name => p_emp_first_name,
1100 p_employee_id_out => l_employee_id );
1101
1102 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1103 raise FND_API.G_EXC_ERROR;
1104 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1105 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1106 END IF;
1107
1108 -- Call Workflow API to get the role
1109 -- If there is more than one role for this employee, the API will
1110 -- return the first one fetched. If no Workflow role exists for
1111 -- the employee, out variables will be NULL
1112 WF_DIRECTORY.GetRoleName(
1113 p_orig_system => 'PER',
1114 p_orig_system_id => l_employee_id,
1115 p_name => p_role_name,
1116 p_display_name => p_role_display_name );
1117
1118 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1119 p_data => p_msg_data );
1120
1121 EXCEPTION
1122 WHEN FND_API.G_EXC_ERROR THEN
1123 p_return_status := FND_API.G_RET_STS_ERROR;
1124 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1125 p_data => p_msg_data );
1126
1127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1128 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1129 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1130 p_data => p_msg_data );
1131
1132 WHEN OTHERS THEN
1133 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1135 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1136 l_api_name );
1137 END IF;
1138 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1139 p_data => p_msg_data );
1140
1141 END Get_Employee_Role;
1142
1143
1144 -- -------------------------------------------------------------------
1145 -- Get_Emp_Supervisor
1146 -- -------------------------------------------------------------------
1147
1148 PROCEDURE Get_Emp_Supervisor(
1149 p_api_version IN NUMBER,
1150 p_init_msg_list IN VARCHAR2 ,
1151 p_return_status OUT NOCOPY VARCHAR2,
1152 p_msg_count OUT NOCOPY NUMBER,
1153 p_msg_data OUT NOCOPY VARCHAR2,
1154 p_employee_id IN NUMBER ,
1155 p_emp_last_name IN VARCHAR2 ,
1156 p_emp_first_name IN VARCHAR2 ,
1157 p_supervisor_emp_id OUT NOCOPY NUMBER,
1158 p_supervisor_role OUT NOCOPY VARCHAR2,
1159 p_supervisor_name OUT NOCOPY VARCHAR2 ) IS
1160
1161 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Supervisor';
1162 l_api_version CONSTANT NUMBER := 1.0;
1163 l_msg_count NUMBER;
1164 l_msg_data VARCHAR2(2000);
1165 l_return_status VARCHAR2(1);
1166 l_supervisor_id NUMBER;
1167 l_employee_id NUMBER;
1168
1169 --
1170 -- Following cursor declaration is taken from HR_OFFER_CUSTOM package
1171 --
1172 CURSOR l_supervisor_csr( l_effective_date IN DATE,
1173 l_in_person_id IN NUMBER) IS
1174 SELECT ppf.person_id
1175 FROM per_assignments_f paf,
1176 per_people_f ppf
1177 WHERE paf.person_id = l_in_person_id
1178 AND paf.primary_flag = 'Y'
1179 AND l_effective_date BETWEEN paf.effective_start_date
1180 AND paf.effective_end_date
1181 AND ppf.person_id = paf.supervisor_id
1182 AND ppf.current_employee_flag = 'Y'
1183 AND l_effective_date BETWEEN ppf.effective_start_date
1184 AND ppf.effective_end_date;
1185
1186 BEGIN
1187
1188 -- Check version number
1189 IF NOT FND_API.Compatible_API_Call( l_api_version,
1190 p_api_version,
1191 l_api_name,
1192 G_PKG_NAME ) THEN
1193 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195
1196 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1197 FND_MSG_PUB.initialize;
1198 END IF;
1199
1200 -- Initialize return values
1201 p_return_status := FND_API.G_RET_STS_SUCCESS;
1202 p_supervisor_role := NULL;
1203 p_supervisor_name := NULL;
1204
1205 -- Validate the parameters and get the employee ID
1206 Get_Employee_ID(
1207 p_api_version => 1.0,
1208 p_init_msg_list => FND_API.G_FALSE,
1209 p_return_status => l_return_status,
1210 p_msg_count => l_msg_count,
1211 p_msg_data => l_msg_data,
1212 p_api_name => G_PKG_NAME||'.'||l_api_name,
1213 p_employee_id => p_employee_id,
1214 p_emp_last_name => p_emp_last_name,
1215 p_emp_first_name => p_emp_first_name,
1216 p_employee_id_out => l_employee_id );
1217
1218 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1219 raise FND_API.G_EXC_ERROR;
1220 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1221 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1222 END IF;
1223
1224 l_supervisor_id := NULL;
1225
1226 OPEN l_supervisor_csr(TRUNC(sysdate), l_employee_id);
1227 FETCH l_supervisor_csr INTO l_supervisor_id;
1228 CLOSE l_supervisor_csr;
1229
1230 p_supervisor_emp_id := l_supervisor_id;
1231
1232 IF (l_supervisor_id IS NOT NULL) THEN
1233
1234 CS_WORKFLOW_PUB.Get_Employee_Role (
1235 p_api_version => 1.0,
1236 p_return_status => l_return_status,
1237 p_msg_count => l_msg_count,
1238 p_msg_data => l_msg_data,
1239 p_employee_id => l_supervisor_id,
1240 p_role_name => p_supervisor_role,
1241 p_role_display_name => p_supervisor_name );
1242
1243 -- Check for possible errors returned by the API
1244 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1245 raise FND_API.G_EXC_ERROR;
1246 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1247 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1248 END IF;
1249
1250 END IF;
1251
1252 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1253 p_data => p_msg_data );
1254
1255 EXCEPTION
1256 WHEN FND_API.G_EXC_ERROR THEN
1257 p_return_status := FND_API.G_RET_STS_ERROR;
1258 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1259 p_data => p_msg_data );
1260
1261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1262 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1263 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1264 p_data => p_msg_data );
1265
1266 WHEN OTHERS THEN
1267 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1268 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1269 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1270 l_api_name );
1271 END IF;
1272 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1273 p_data => p_msg_data );
1274
1275 END Get_Emp_Supervisor;
1276
1277
1278 -- -----------------------------------------------------------------------
1279 -- Get_Emp_Fnd_User_ID
1280 -- -----------------------------------------------------------------------
1281
1282 PROCEDURE Get_Emp_Fnd_User_ID(
1283 p_api_version IN NUMBER,
1284 p_init_msg_list IN VARCHAR2 ,
1285 p_return_status OUT NOCOPY VARCHAR2,
1286 p_msg_count OUT NOCOPY NUMBER,
1287 p_msg_data OUT NOCOPY VARCHAR2,
1288 p_employee_id IN NUMBER ,
1289 p_emp_last_name IN VARCHAR2 ,
1290 p_emp_first_name IN VARCHAR2 ,
1291 p_fnd_user_id OUT NOCOPY NUMBER ) IS
1292
1293 l_api_name CONSTANT VARCHAR2(30) := 'Get_Emp_Fnd_User_ID';
1294 l_api_version CONSTANT NUMBER := 1.0;
1295 l_msg_count NUMBER;
1296 l_msg_data VARCHAR2(2000);
1297 l_return_status VARCHAR2(1);
1298 l_employee_id NUMBER;
1299
1300 CURSOR l_emp_csr IS
1301 SELECT user_id
1302 FROM fnd_user
1303 WHERE employee_id = l_employee_id;
1304
1305 BEGIN
1306
1307 -- Check version number
1308 IF NOT FND_API.Compatible_API_Call( l_api_version,
1309 p_api_version,
1310 l_api_name,
1311 G_PKG_NAME ) THEN
1312 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1313 END IF;
1314
1315 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1316 FND_MSG_PUB.initialize;
1317 END IF;
1318
1319 -- Initialize return value
1320 p_return_status := FND_API.G_RET_STS_SUCCESS;
1321
1322 -- Validate the parameters and get the employee ID
1323 Get_Employee_ID(
1324 p_api_version => 1.0,
1325 p_init_msg_list => FND_API.G_FALSE,
1326 p_return_status => l_return_status,
1327 p_msg_count => l_msg_count,
1328 p_msg_data => l_msg_data,
1329 p_api_name => G_PKG_NAME||'.'||l_api_name,
1330 p_employee_id => p_employee_id,
1331 p_emp_last_name => p_emp_last_name,
1332 p_emp_first_name => p_emp_first_name,
1333 p_employee_id_out => l_employee_id );
1334
1335 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1336 raise FND_API.G_EXC_ERROR;
1337 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1338 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1339 END IF;
1340
1341 -- Bug 654417: TOO_MANY_ROWS exception when SELECT INTO statement
1342 -- returns more than one row
1343 -- The FETCH statement raises neither NO_DATA_FOUND nor TOO_MANY_ROWS
1344 OPEN l_emp_csr;
1345 FETCH l_emp_csr INTO p_fnd_user_id;
1346 CLOSE l_emp_csr;
1347
1348 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1349 p_data => p_msg_data );
1350
1351 EXCEPTION
1352 WHEN FND_API.G_EXC_ERROR THEN
1353 p_return_status := FND_API.G_RET_STS_ERROR;
1354 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1355 p_data => p_msg_data );
1356
1357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1358 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1360 p_data => p_msg_data );
1361
1362 WHEN OTHERS THEN
1363 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1365 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1366 l_api_name );
1367 END IF;
1368 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
1369 p_data => p_msg_data );
1370
1371 END Get_Emp_Fnd_User_ID;
1372
1373
1374 ----------------------------------------------------------------------
1375 -- Launch_Action_Workflow
1376 -- This procedure launches a workflow process for the given service
1377 -- request action. It selects the workflow process to run base on
1378 -- the action type, and it initializes two item attributes
1379 -- INITIATOR_ROLE and WF_ADMINISTRATOR.
1380 ----------------------------------------------------------------------
1381
1382 /*PROCEDURE Launch_Action_Workflow
1383 ( p_api_version IN NUMBER,
1384 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1385 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1386 p_return_status OUT VARCHAR2,
1387 p_msg_count OUT NUMBER,
1388 p_msg_data OUT VARCHAR2,
1389 p_request_id IN NUMBER,
1390 p_action_number IN NUMBER,
1391 p_initiator_user_id IN NUMBER := NULL,
1392 p_initiator_resp_id IN NUMBER := NULL,
1393 p_initiator_resp_appl_id IN NUMBER := NULL,
1394 p_launched_by_dispatch IN VARCHAR2 := FND_API.G_FALSE,
1395 p_nowait IN VARCHAR2 := FND_API.G_FALSE,
1396 p_itemkey OUT VARCHAR2
1397 )
1398 IS
1399 l_api_name CONSTANT VARCHAR2(30) := 'Launch_Action_Workflow';
1400 l_api_version CONSTANT NUMBER := 1.0;
1401 l_itemtype CONSTANT VARCHAR2(30) := 'SRACTION';
1402
1403 l_nowait BOOLEAN := FALSE;
1404 l_administrator VARCHAR2(100);
1405 l_dummy VARCHAR2(240);
1406 l_request_action_id NUMBER;
1407 l_request_id NUMBER;
1408 l_wf_process_id NUMBER;
1409 l_workflow_proc VARCHAR2(30);
1410 l_itemkey VARCHAR2(240);
1411 l_return_status VARCHAR2(1);
1412 l_msg_count NUMBER;
1413 l_msg_data VARCHAR2(2000);
1414 l_initiator_role VARCHAR2(100);
1415 l_action_audit_id NUMBER;
1416
1417 l_exc_administrator_not_set EXCEPTION;
1418 l_exc_reset_administrator EXCEPTION;
1419 l_exc_workflow_in_progress EXCEPTION;
1420 l_exc_sr_no_workflow EXCEPTION;
1421
1422 CURSOR l_action_nw_csr IS
1423 SELECT incident_action_id, incident_id, workflow_process_id
1424 FROM cs_incident_actions
1425 WHERE incident_id = p_request_id
1426 AND action_num = p_action_number
1427 FOR UPDATE OF workflow_process_id NOWAIT;
1428
1429 CURSOR l_action_csr IS
1430 SELECT incident_action_id, incident_id, workflow_process_id
1431 FROM cs_incident_actions
1432 WHERE incident_id = p_request_id
1433 AND action_num = p_action_number
1434 FOR UPDATE OF workflow_process_id;
1435
1436 CURSOR l_wf_proc_id_csr IS
1437 SELECT cs_action_wf_proc_id_s.nextval
1438 FROM dual;
1439
1440 BEGIN
1441 -- Standard Start of API savepoint
1442 SAVEPOINT Launch_Action_Workflow_PUB;
1443
1444 -- Standard call to check for call compatibility
1445 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1446 p_api_version,
1447 l_api_name,
1448 G_PKG_NAME )
1449 THEN
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 END IF;
1452
1453 -- Initialize message list if p_init_msg_list is set to TRUE
1454 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1455 FND_MSG_PUB.Initialize;
1456 END IF;
1457
1458 -- Initialize API return status to SUCCESS
1459 p_return_status := FND_API.G_RET_STS_SUCCESS;
1460
1461 -- Set nowait option
1462 IF FND_API.To_Boolean( p_nowait ) THEN
1463 l_nowait := TRUE;
1464 END IF;
1465
1466 -- Get the Workflow Administrator Role
1467 l_administrator := FND_PROFILE.Value('CS_WF_ADMINISTRATOR');
1468
1469 --
1470 -- The Service Dispatch process activity is associated with the service
1471 -- request error process, which requires the customer to set up the
1472 -- 'Service: Workflow Administrator' profile as the performer of the service
1473 -- request error notification.
1474 --
1475 IF (l_administrator IS NULL) THEN
1476 RAISE l_exc_administrator_not_set;
1477 ELSE
1478 BEGIN
1479 SELECT 'x' INTO l_dummy
1480 FROM WF_ROLES
1481 WHERE name = l_administrator;
1482
1483 EXCEPTION
1484 WHEN NO_DATA_FOUND THEN
1485 -- Invalid administrator role
1486 RAISE l_exc_reset_administrator;
1487 WHEN TOO_MANY_ROWS THEN
1488 -- Okay here
1489 NULL;
1490 END;
1491 END IF;
1492
1493 -- Get the last workflow process ID of this request and lock the record
1494 IF (l_nowait = TRUE) THEN
1495 OPEN l_action_nw_csr;
1496 FETCH l_action_nw_csr INTO l_request_action_id, l_request_id, l_wf_process_id;
1497 ELSE
1498 OPEN l_action_csr;
1499 FETCH l_action_csr INTO l_request_action_id, l_request_id, l_wf_process_id;
1500 END IF;
1501
1502 -- Verify that the workflow is not active
1503 IF (l_wf_process_id IS NOT NULL) THEN
1504 IF (CS_Workflow_PKG.Is_Action_Item_Active
1505 ( p_request_id => p_request_id,
1506 p_action_number => p_action_number,
1507 p_wf_process_id => l_wf_process_id ) = 'Y') THEN
1508 RAISE l_exc_workflow_in_progress;
1509 END IF;
1510 END IF;
1511
1512 -- Get the workflow process name for this request from the request type
1513 SELECT type.workflow INTO l_workflow_proc
1514 FROM cs_incident_actions action, cs_incident_types type
1515 WHERE action.incident_id = p_request_id
1516 AND action.action_num = p_action_number
1517 AND action.action_type_id = type.incident_type_id;
1518
1519 IF (l_workflow_proc IS NULL) THEN
1520 RAISE l_exc_sr_no_workflow;
1521 END IF;
1522
1523 -- Get the new workflow process ID
1524 OPEN l_wf_proc_id_csr;
1525 FETCH l_wf_proc_id_csr INTO l_wf_process_id;
1526 CLOSE l_wf_proc_id_csr;
1527
1528 -- Construct the unique item key
1529 l_itemkey := p_request_id || '-' || p_action_number || '-' || l_wf_process_id;
1530
1531 -- Get the process initiator's workflow role name
1532 IF (p_initiator_user_id IS NOT NULL) THEN
1533 get_fnd_user_role
1534 ( p_fnd_user_id => p_initiator_user_id,
1535 x_role_name => l_initiator_role,
1536 x_role_display_name => l_dummy );
1537 END IF;
1538
1539 -- Create and launch the Workflow process
1540 WF_ENGINE.CreateProcess
1541 ( itemtype => l_itemtype,
1542 itemkey => l_itemkey,
1543 process => l_workflow_proc
1544 );
1545
1546 WF_ENGINE.SetItemAttrText
1547 ( itemtype => l_itemtype,
1548 itemkey => l_itemkey,
1549 aname => 'INITIATOR_ROLE',
1550 avalue => l_initiator_role
1551 );
1552
1553 WF_ENGINE.SetItemAttrText
1554 ( itemtype => l_itemtype,
1555 itemkey => l_itemkey,
1556 aname => 'USER_ID',
1557 avalue => p_initiator_user_id
1558 );
1559
1560 WF_ENGINE.SetItemAttrText
1561 ( itemtype => l_itemtype,
1562 itemkey => l_itemkey,
1563 aname => 'RESP_ID',
1564 avalue => p_initiator_resp_id
1565 );
1566
1567 WF_ENGINE.SetItemAttrText
1568 ( itemtype => l_itemtype,
1569 itemkey => l_itemkey,
1570 aname => 'RESP_APPL_ID',
1571 avalue => p_initiator_resp_appl_id
1572 );
1573
1574 WF_ENGINE.SetItemAttrText
1575 ( itemtype => l_itemtype,
1576 itemkey => l_itemkey,
1577 aname => 'WF_ADMINISTRATOR',
1578 avalue => l_administrator
1579 );
1580
1581 WF_ENGINE.SetItemAttrText
1582 ( itemtype => l_itemtype,
1583 itemkey => l_itemkey,
1584 aname => 'LAUNCHED_BY_DISPATCH',
1585 avalue => p_launched_by_dispatch
1586 );
1587
1588 wf_engine.setitemowner
1589 ( itemtype => l_itemtype,
1590 itemkey => l_itemkey,
1591 owner => l_initiator_role );
1592
1593 WF_ENGINE.StartProcess
1594 ( itemtype => l_itemtype,
1595 itemkey => l_itemkey
1596 );
1597
1598 -- Update the workflow process ID of the request
1599 IF (l_nowait = TRUE) THEN
1600 UPDATE cs_incident_actions
1601 SET workflow_process_id = l_wf_process_id
1602 WHERE CURRENT OF l_action_nw_csr;
1603
1604 CLOSE l_action_nw_csr;
1605 ELSE
1606 UPDATE cs_incident_actions
1607 SET workflow_process_id = l_wf_process_id
1608 WHERE CURRENT OF l_action_csr;
1609
1610 CLOSE l_action_csr;
1611 END IF;
1612
1613 -- Insert audit record
1614 SELECT cs_incident_action_audit_s.NEXTVAL INTO l_action_audit_id FROM dual;
1615
1616 INSERT INTO cs_incident_action_audit
1617 ( incident_action_audit_id,
1618 last_update_date,
1619 last_updated_by,
1620 creation_date,
1621 created_by,
1622 incident_action_id,
1623 incident_id,
1624 new_workflow_flag,
1625 workflow_process_name,
1626 workflow_process_itemkey
1627 )
1628 VALUES
1629 ( l_action_audit_id, -- INCIDENT_ACTION_AUDIT_ID
1630 SYSDATE, -- LAST_UPDATE_DATE
1631 p_initiator_user_id, -- LAST_UPDATED_BY
1632 SYSDATE, -- CREATION_DATE
1633 p_initiator_user_id, -- CREATED_BY
1634 l_request_action_id, -- INCIDENT_ACTION_ID
1635 l_request_id, -- INCIDENT_ID
1636 'Y', -- NEW_WORKFLOW_FLAG
1637 l_workflow_proc, -- WORKFLOW_PROCESS_NAME
1638 l_itemkey -- WORKFLOW_PROCESS_ITEMKEY
1639 );
1640
1641 -- Set up return value
1642 p_itemkey := l_itemkey;
1643
1644 IF FND_API.To_Boolean( p_commit ) THEN
1645 COMMIT WORK;
1646 END IF;
1647
1648 FND_MSG_PUB.Count_And_Get
1649 ( p_count => p_msg_count,
1650 p_data => p_msg_data,
1651 p_encoded => FND_API.G_FALSE
1652 );
1653
1654 EXCEPTION
1655 WHEN l_exc_administrator_not_set THEN
1656 ROLLBACK TO Launch_Action_Workflow_PUB;
1657 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1658 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1659 FND_MESSAGE.Set_Name('CS', 'CS_ALL_WF_ADMINISTRATOR');
1660 FND_MSG_PUB.Add;
1661 END IF;
1662 FND_MSG_PUB.Count_And_Get
1663 ( p_count => p_msg_count,
1664 p_data => p_msg_data,
1665 p_encoded => FND_API.G_FALSE
1666 );
1667 WHEN l_exc_reset_administrator THEN
1668 ROLLBACK TO Launch_Action_Workflow_PUB;
1669 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1670 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1671 FND_MESSAGE.Set_Name('CS', 'CS_ALL_RESET_WF_ADMINI');
1672 FND_MSG_PUB.Add;
1673 END IF;
1674 FND_MSG_PUB.Count_And_Get
1675 ( p_count => p_msg_count,
1676 p_data => p_msg_data,
1677 p_encoded => FND_API.G_FALSE
1678 );
1679 WHEN l_exc_workflow_in_progress THEN
1680 IF (l_action_nw_csr%ISOPEN) THEN
1681 CLOSE l_action_nw_csr;
1682 ELSIF (l_action_csr%ISOPEN) THEN
1683 CLOSE l_action_csr;
1684 END IF;
1685 ROLLBACK TO Launch_Action_Workflow_PUB;
1686 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1687 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1688 FND_MESSAGE.Set_Name('CS', 'CS_ACT_WORKFLOW_IN_PROGRESS');
1689 FND_MSG_PUB.Add;
1690 END IF;
1691 FND_MSG_PUB.Count_And_Get
1692 ( p_count => p_msg_count,
1693 p_data => p_msg_data,
1694 p_encoded => FND_API.G_FALSE
1695 );
1696 WHEN l_exc_sr_no_workflow THEN
1697 ROLLBACK TO Launch_Action_Workflow_PUB;
1698 p_return_status := FND_API.G_RET_STS_ERROR;
1699 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1700 FND_MESSAGE.Set_Name('CS', 'CS_ACT_NO_WORKFLOW');
1701 FND_MSG_PUB.Add;
1702 END IF;
1703 FND_MSG_PUB.Count_And_Get
1704 ( p_count => p_msg_count,
1705 p_data => p_msg_data,
1706 p_encoded => FND_API.G_FALSE
1707 );
1708 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1709 ROLLBACK TO Launch_Action_Workflow_PUB;
1710 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1711 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1712 FND_MESSAGE.Set_Name('CS', 'CS_ACT_WF_RECORD_LOCKED');
1713 FND_MSG_PUB.Add;
1714 END IF;
1715 FND_MSG_PUB.Count_And_Get
1716 ( p_count => p_msg_count,
1717 p_data => p_msg_data,
1718 p_encoded => FND_API.G_FALSE
1719 );
1720 WHEN FND_API.G_EXC_ERROR THEN
1721 IF (l_action_nw_csr%ISOPEN) THEN
1722 CLOSE l_action_nw_csr;
1723 ELSIF (l_action_csr%ISOPEN) THEN
1724 CLOSE l_action_csr;
1725 END IF;
1726 ROLLBACK TO Launch_Action_Workflow_PUB;
1727 p_return_status := FND_API.G_RET_STS_ERROR;
1728 FND_MSG_PUB.Count_And_Get
1729 ( p_count => p_msg_count,
1730 p_data => p_msg_data,
1731 p_encoded => FND_API.G_FALSE
1732 );
1733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1734 IF (l_action_nw_csr%ISOPEN) THEN
1735 CLOSE l_action_nw_csr;
1736 ELSIF (l_action_csr%ISOPEN) THEN
1737 CLOSE l_action_csr;
1738 END IF;
1739 ROLLBACK TO Launch_Action_Workflow_PUB;
1740 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741 FND_MSG_PUB.Count_And_Get
1742 ( p_count => p_msg_count,
1743 p_data => p_msg_data,
1744 p_encoded => FND_API.G_FALSE
1745 );
1746 WHEN OTHERS THEN
1747 IF (l_action_nw_csr%ISOPEN) THEN
1748 CLOSE l_action_nw_csr;
1749 ELSIF (l_action_csr%ISOPEN) THEN
1750 CLOSE l_action_csr;
1751 END IF;
1752 ROLLBACK TO Launch_Action_Workflow_PUB;
1753 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1754 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1755 FND_MSG_PUB.Add_Exc_Msg
1756 ( G_PKG_NAME,
1757 l_api_name
1758 );
1759 END IF;
1760 FND_MSG_PUB.Count_And_Get
1761 ( p_count => p_msg_count,
1762 p_data => p_msg_data,
1763 p_encoded => FND_API.G_FALSE
1764 );
1765
1766 END Launch_Action_Workflow;*/
1767
1768
1769 ----------------------------------------------------------------------
1770 -- Cancel_Action_Workflow
1771 ----------------------------------------------------------------------
1772
1773 PROCEDURE Cancel_Action_Workflow
1774 ( p_api_version IN NUMBER,
1775 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1776 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1777 p_return_status OUT NOCOPY VARCHAR2,
1778 p_msg_count OUT NOCOPY NUMBER,
1779 p_msg_data OUT NOCOPY VARCHAR2,
1780 p_request_id IN NUMBER,
1781 p_action_number IN NUMBER,
1782 p_wf_process_id IN NUMBER,
1783 p_abort_user_id IN NUMBER,
1784 p_launched_by_dispatch OUT NOCOPY VARCHAR2
1785 )
1786 IS
1787 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_Action_Workflow';
1788 l_api_version CONSTANT NUMBER := 1.0;
1789 l_itemtype CONSTANT VARCHAR2(30) := 'SRACTION';
1790 l_activityid CONSTANT NUMBER := -1;
1791
1792 l_itemkey VARCHAR2(240);
1793 l_user_name VARCHAR2(100);
1794 l_emp_name VARCHAR2(240);
1795 l_aborted_by VARCHAR2(240);
1796 l_context VARCHAR2(252);
1797 l_assignee_role VARCHAR2(100);
1798 l_notification_id NUMBER;
1799 l_dispatch_role VARCHAR2(100);
1800 l_dummy VARCHAR2(1);
1801
1802 l_exc_not_active EXCEPTION;
1803
1804 CURSOR l_dispatch_csr IS
1805 SELECT 'x'
1806 FROM wf_roles
1807 WHERE name = l_dispatch_role;
1808
1809 BEGIN
1810 -- Standard Start of API savepoint
1811 SAVEPOINT Cancel_Action_Workflow_PUB;
1812
1813 -- Standard call to check for call compatibility
1814 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1815 p_api_version,
1816 l_api_name,
1817 G_PKG_NAME )
1818 THEN
1819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1820 END IF;
1821
1822 -- Initialize message list if p_init_msg_list is set to TRUE
1823 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1824 FND_MSG_PUB.Initialize;
1825 END IF;
1826
1827 -- Initialize API return status to success
1828 p_return_status := FND_API.G_RET_STS_SUCCESS;
1829
1830 --
1831 -- First construct the item key
1832 -- If we ever change the format of the itemkey, the following code
1833 -- must be updated
1834 --
1835 l_itemkey := p_request_id || '-' || p_action_number || '-' || p_wf_process_id;
1836
1837 --
1838 -- Make sure that the item is still active
1839 --
1840 IF (CS_Workflow_PKG.Is_Action_Item_Active
1841 ( p_request_id => p_request_id,
1842 p_action_number => p_action_number,
1843 p_wf_process_id => p_wf_process_id ) = 'N') THEN
1844 RAISE l_exc_not_active;
1845 END IF;
1846
1847 --
1848 -- Get the employee name of the user who is aborting the process
1849 -- If we can't get that information, just use the FND username
1850 --
1851 SELECT fnd.user_name, emp.full_name
1852 INTO l_user_name, l_emp_name
1853 FROM fnd_user fnd, per_people_x emp
1854 WHERE fnd.user_id = p_abort_user_id
1855 AND fnd.employee_id = emp.person_id (+);
1856
1857 IF (l_emp_name IS NOT NULL) THEN
1858 l_aborted_by := l_emp_name;
1859 ELSE
1860 l_aborted_by := l_user_name;
1861 END IF;
1862
1863 -- Call Workflow API to abort the process
1864 WF_ENGINE.AbortProcess
1865 ( itemtype => l_itemtype,
1866 itemkey => l_itemkey );
1867
1868 -- Set up the context information for the callback function
1869 -- The format is <itemtype>:<itemkey>:<activityid>
1870 l_context := l_itemtype || ':' || l_itemkey || ':' || l_activityid;
1871
1872 -- Notify the current owner that the process has been aborted
1873 -- Note that we're using Workflow engine's callback function
1874 l_assignee_role := WF_ENGINE.GetItemAttrText
1875 ( itemtype => l_itemtype,
1876 itemkey => l_itemkey,
1877 aname => 'ASSIGNEE_ROLE'
1878 );
1879 IF (l_assignee_role IS NOT NULL) THEN
1880 l_notification_id := WF_Notification.Send
1881 ( role => l_assignee_role,
1882 msg_type => l_itemtype,
1883 msg_name => 'ABORT_MSG',
1884 callback => 'WF_ENGINE.CB',
1885 context => l_context
1886 );
1887 WF_Notification.SetAttrText
1888 ( nid => l_notification_id,
1889 aname => 'ABORT_USER',
1890 avalue => l_aborted_by
1891 );
1892 END IF;
1893
1894 -- Notify the dispatcher that the process has been aborted
1895 l_dispatch_role := WF_ENGINE.GetItemAttrText
1896 ( itemtype => l_itemtype,
1897 itemkey => l_itemkey,
1898 aname => 'DISPATCHER_ROLE'
1899 );
1900 IF (l_dispatch_role IS NOT NULL) THEN
1901 --
1902 -- Verify that the dispatch role exists in the workflow directory
1903 -- before sending the abort notification
1904 --
1905 OPEN l_dispatch_csr;
1906 FETCH l_dispatch_csr INTO l_dummy;
1907 IF (l_dispatch_csr%NOTFOUND) THEN
1908 -- Okay here; probably some other error occurred so the administrator
1909 -- wants to abort the process
1910 NULL;
1911 ELSE
1912 l_notification_id := WF_Notification.Send
1913 ( role => l_dispatch_role,
1914 msg_type => l_itemtype,
1915 msg_name => 'ABORT_MSG',
1916 callback => 'WF_ENGINE.CB',
1917 context => l_context
1918 );
1919 WF_Notification.SetAttrText
1920 ( nid => l_notification_id,
1921 aname => 'ABORT_USER',
1922 avalue => l_aborted_by
1923 );
1924 END IF;
1925 END IF;
1926
1927 p_launched_by_dispatch := WF_ENGINE.GetItemAttrText
1928 ( itemtype => l_itemtype,
1929 itemkey => l_itemkey,
1930 aname => 'LAUNCHED_BY_DISPATCH'
1931 );
1932
1933 IF FND_API.To_Boolean( p_commit ) THEN
1934 COMMIT WORK;
1935 END IF;
1936
1937 FND_MSG_PUB.Count_And_Get
1938 ( p_count => p_msg_count,
1939 p_data => p_msg_data,
1940 p_encoded => FND_API.G_FALSE
1941 );
1942
1943 EXCEPTION
1944 WHEN l_exc_not_active THEN
1945 ROLLBACK TO Cancel_Action_Workflow_PUB;
1946 p_return_status := FND_API.G_RET_STS_ERROR;
1947 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1948 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKFLOW_NOT_ACTIVE');
1949 FND_MSG_PUB.Add;
1950 END IF;
1951 FND_MSG_PUB.Count_And_Get
1952 ( p_count => p_msg_count,
1953 p_data => p_msg_data,
1954 p_encoded => FND_API.G_FALSE
1955 );
1956 WHEN OTHERS THEN
1957 ROLLBACK TO Cancel_Action_Workflow_PUB;
1958 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1960 FND_MSG_PUB.Add_Exc_Msg
1961 ( G_PKG_NAME,
1962 l_api_name
1963 );
1964 END IF;
1965 FND_MSG_PUB.Count_And_Get
1966 ( p_count => p_msg_count,
1967 p_data => p_msg_data,
1968 p_encoded => FND_API.G_FALSE
1969 );
1970
1971 END Cancel_Action_Workflow;
1972
1973
1974 --------------------------------------------------------------------------
1975 -- Decode_Action_Itemkey
1976 --
1977 -- A Service Request Action itemkey has the following format:
1978 --
1979 -- '<Service Request ID>-<Action Number>-<Workflow Process ID>'
1980 --
1981 --------------------------------------------------------------------------
1982
1983 PROCEDURE Decode_Action_Itemkey
1984 ( p_api_version IN NUMBER,
1985 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1986 p_return_status OUT NOCOPY VARCHAR2,
1987 p_msg_count OUT NOCOPY NUMBER,
1988 p_msg_data OUT NOCOPY VARCHAR2,
1989 p_itemkey IN VARCHAR2,
1990 p_request_id OUT NOCOPY NUMBER,
1991 p_action_number OUT NOCOPY NUMBER,
1992 p_wf_process_id OUT NOCOPY NUMBER
1993 )
1994 IS
1995 l_api_name CONSTANT VARCHAR2(30) := 'Decode_Action_Itemkey';
1996 l_api_version CONSTANT NUMBER := 1.0;
1997
1998 l_dash_pos1 NUMBER;
1999 l_dash_pos2 NUMBER;
2000
2001 l_exc_invalid_itemkey EXCEPTION;
2002
2003 BEGIN
2004 -- Standard call to check for call compatibility
2005 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2006 p_api_version,
2007 l_api_name,
2008 G_PKG_NAME )
2009 THEN
2010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2011 END IF;
2012
2013 -- Initialize message list if p_init_msg_list is set to TRUE
2014 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2015 FND_MSG_PUB.Initialize;
2016 END IF;
2017
2018 -- Initialize API return status to success
2019 p_return_status := FND_API.G_RET_STS_SUCCESS;
2020
2021 l_dash_pos1 := INSTR(p_itemkey, '-');
2022 l_dash_pos2 := INSTR(p_itemkey, '-', l_dash_pos1+1);
2023 IF ((l_dash_pos1 = 0) OR (l_dash_pos2 = 0)) THEN
2024 RAISE l_exc_invalid_itemkey;
2025 END IF;
2026
2027 p_request_id := SUBSTR(p_itemkey, 1, l_dash_pos1-1);
2028 p_action_number := SUBSTR(p_itemkey, l_dash_pos1+1, l_dash_pos2-l_dash_pos1-1);
2029 p_wf_process_id := SUBSTR(p_itemkey, l_dash_pos2+1);
2030
2031 FND_MSG_PUB.Count_And_Get
2032 ( p_count => p_msg_count,
2033 p_data => p_msg_data
2034 );
2035
2036 EXCEPTION
2037 WHEN l_exc_invalid_itemkey THEN
2038 p_return_status := FND_API.G_RET_STS_ERROR;
2039 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg
2040 ( p_token_an => G_PKG_NAME||'.'||l_api_name,
2041 p_token_v => p_itemkey,
2042 p_token_p => 'p_itemkey'
2043 );
2044 FND_MSG_PUB.Count_And_Get
2045 ( p_count => p_msg_count,
2046 p_data => p_msg_data
2047 );
2048 WHEN FND_API.G_EXC_ERROR THEN
2049 p_return_status := FND_API.G_RET_STS_ERROR;
2050 FND_MSG_PUB.Count_And_Get
2051 ( p_count => p_msg_count,
2052 p_data => p_msg_data
2053 );
2054 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2055 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2056 FND_MSG_PUB.Count_And_Get
2057 ( p_count => p_msg_count,
2058 p_data => p_msg_data
2059 );
2060 WHEN OTHERS THEN
2061 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2062 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2063 FND_MSG_PUB.Add_Exc_Msg
2064 ( G_PKG_NAME,
2065 l_api_name
2066 );
2067 END IF;
2068 FND_MSG_PUB.Count_And_Get
2069 ( p_count => p_msg_count,
2070 p_data => p_msg_data
2071 );
2072
2073 END Decode_Action_Itemkey;
2074
2075 /****************************************************************************
2076 Local Procedure Bodies
2077 ****************************************************************************/
2078
2079 -- -------------------------------------------------------------------
2080 -- LOCAL: Get_Employee_ID
2081 -- -------------------------------------------------------------------
2082
2083 PROCEDURE Get_Employee_ID (
2084 p_api_version IN NUMBER,
2085 p_init_msg_list IN VARCHAR2 ,
2086 p_return_status OUT NOCOPY VARCHAR2,
2087 p_msg_count OUT NOCOPY NUMBER,
2088 p_msg_data OUT NOCOPY VARCHAR2,
2089 p_api_name IN VARCHAR2,
2090 p_employee_id IN NUMBER ,
2091 p_emp_last_name IN VARCHAR2 ,
2092 p_emp_first_name IN VARCHAR2 ,
2093 p_employee_id_out OUT NOCOPY NUMBER ) IS
2094
2095 l_api_name CONSTANT VARCHAR2(30) := 'Get_Employee_ID';
2096 l_api_version CONSTANT NUMBER := 1.0;
2097
2098 l_dummy VARCHAR2(1);
2099
2100 l_INVALID_EMP_NAME EXCEPTION;
2101 l_DUPLICATE_VALUE EXCEPTION;
2102
2103 BEGIN
2104 -- Check version number
2105 IF NOT FND_API.Compatible_API_Call( l_api_version,
2106 p_api_version,
2107 l_api_name,
2108 G_PKG_NAME ) THEN
2109 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2110 END IF;
2111
2112 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2113 FND_MSG_PUB.initialize;
2114 END IF;
2115
2116 -- Initialize return status to SUCCESS
2117 p_return_status := FND_API.G_RET_STS_SUCCESS;
2118
2119 -- Verify arguments
2120 IF (p_employee_id IS NULL) THEN
2121 IF (p_emp_last_name IS NULL) AND
2122 (p_emp_first_name IS NULL) THEN
2123 CS_ServiceRequest_UTIL.Add_Null_Parameter_Msg(
2124 p_token_an => p_api_name,
2125 p_token_np => 'p_employee_id' );
2126 raise FND_API.G_EXC_ERROR;
2127 END IF;
2128 ELSE
2129 IF (p_emp_last_name IS NOT NULL) THEN
2130 CS_ServiceRequest_UTIL.Add_Param_Ignored_Msg(
2131 p_token_an => p_api_name,
2132 p_token_ip => 'p_emp_last_name');
2133 END IF;
2134
2135 IF (p_emp_first_name IS NOT NULL) THEN
2136 CS_ServiceRequest_UTIL.Add_Param_Ignored_Msg(
2137 p_token_an => p_api_name,
2138 p_token_ip => 'p_emp_first_name');
2139 END IF;
2140 END IF;
2141
2142 -- Get the employee ID from the name
2143 IF (p_employee_id IS NULL) THEN
2144
2145 BEGIN
2146 IF (p_emp_last_name IS NULL) THEN
2147
2148 SELECT person_id INTO p_employee_id_out
2149 FROM per_people_x
2150 WHERE first_name = p_emp_first_name
2151 AND employee_number IS NOT NULL;
2152
2153 ELSIF (p_emp_first_name IS NULL) THEN
2154
2155 SELECT person_id INTO p_employee_id_out
2156 FROM per_people_x
2157 WHERE last_name = p_emp_last_name
2158 AND employee_number IS NOT NULL;
2159
2160 ELSE
2161
2162 SELECT person_id INTO p_employee_id_out
2163 FROM per_people_x
2164 WHERE last_name = p_emp_last_name
2165 AND first_name = p_emp_first_name
2166 AND employee_number IS NOT NULL;
2167
2168 END IF;
2169
2170 EXCEPTION
2171 WHEN NO_DATA_FOUND THEN
2172 raise l_INVALID_EMP_NAME;
2173
2174 WHEN TOO_MANY_ROWS THEN
2175 raise l_DUPLICATE_VALUE;
2176 END;
2177
2178 ELSE
2179
2180 BEGIN
2181
2182 SELECT 'x' INTO l_dummy
2183 FROM per_people_x
2184 WHERE person_id = p_employee_id;
2185
2186 p_employee_id_out := p_employee_id;
2187
2188 EXCEPTION
2189 WHEN NO_DATA_FOUND THEN
2190 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
2191 p_token_an => P_api_name,
2192 p_token_v => to_char(p_employee_id),
2193 p_token_p => 'p_employee_id' );
2194 raise FND_API.G_EXC_ERROR;
2195 END;
2196
2197 END IF;
2198
2199 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2200 p_data => p_msg_data );
2201
2202 EXCEPTION
2203 WHEN l_INVALID_EMP_NAME THEN
2204 p_return_status := FND_API.G_RET_STS_ERROR;
2205 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2206 FND_MESSAGE.SET_NAME('CS', 'CS_API_SR_INVALID_EMP_NAME');
2207 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
2208 FND_MESSAGE.SET_TOKEN('FIRST_NAME', p_emp_first_name);
2209 FND_MESSAGE.SET_TOKEN('LAST_NAME', p_emp_last_name);
2210 FND_MSG_PUB.Add;
2211 END IF;
2212 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2213 p_data => p_msg_data );
2214
2215 WHEN l_DUPLICATE_VALUE THEN
2216 p_return_status := FND_API.G_RET_STS_ERROR;
2217 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2218 FND_MESSAGE.SET_NAME('CS', 'CS_API_SR_DUPLICATE_EMPLOYEE');
2219 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
2220 FND_MESSAGE.SET_TOKEN('FIRST_NAME', p_emp_first_name);
2221 FND_MESSAGE.SET_TOKEN('LAST_NAME', p_emp_last_name);
2222 FND_MSG_PUB.Add;
2223 END IF;
2224 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2225 p_data => p_msg_data );
2226
2227 WHEN FND_API.G_EXC_ERROR THEN
2228 p_return_status := FND_API.G_RET_STS_ERROR;
2229 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2230 p_data => p_msg_data );
2231
2232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2233 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2235 p_data => p_msg_data );
2236
2237 WHEN OTHERS THEN
2238 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2239 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2240 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2241 l_api_name );
2242 END IF;
2243 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
2244 p_data => p_msg_data );
2245
2246 END Get_Employee_ID;
2247
2248 ------------------------------------------------------------------------------
2249 -- Procedure : Get_Fnd_User_Role
2250 ------------------------------------------------------------------------------
2251
2252 PROCEDURE Get_Fnd_User_Role
2253 ( p_fnd_user_id IN NUMBER,
2254 x_role_name OUT NOCOPY VARCHAR2,
2255 x_role_display_name OUT NOCOPY VARCHAR2 )
2256 IS
2257 l_employee_id NUMBER;
2258 BEGIN
2259 -- map the FND user to employee ID
2260 SELECT employee_id INTO l_employee_id
2261 FROM fnd_user
2262 WHERE user_id = p_fnd_user_id;
2263
2264 IF (l_employee_id IS NOT NULL) THEN
2265 wf_directory.getrolename
2266 ( p_orig_system => 'PER',
2267 p_orig_system_id => l_employee_id,
2268 p_name => x_role_name,
2269 p_display_name => x_role_display_name );
2270 ELSE
2271 wf_directory.getrolename
2272 ( p_orig_system => 'FND_USR',
2273 p_orig_system_id => p_fnd_user_id,
2274 p_name => x_role_name,
2275 p_display_name => x_role_display_name );
2276 END IF;
2277 END Get_Fnd_User_Role;
2278
2279 END CS_Workflow_PUB;