1 PACKAGE BODY CS_WF_EVENT_PKG AS
2 /* $Header: cswfevtb.pls 120.23.12020000.2 2012/07/10 06:35:54 bkanimoz ship $ */
3
4 /****************************************************************************************
5
6 CS_Custom_Rule_Func
7
8 This custom rule function corresponds to the subscriptions for workflow
9 business events oracle.apps.cs.sr.ServiceRequest.created and
10 oracle.apps.cs.sr.ServiceRequest.updated .
11 These subscriptions executes the old BES converted seeded workflow CALL
12 SUPPORT, as well as any client custom BES workflows. Also, these subscriptions
13 are executed Synchronously, the same as the old seeded workflows. This is
14 important since the calling wrapper API needs to update the workflow_process_id
15 if the workflow was launched.
16
17 If this custom rule function is not able to launch the workflow process
18 because the proces is not BES compatible, then the CS event wrapper API
19 Raise_ServiceRequest_Event() will try to launch the workflow process using
20 the old non-BES workflow API calls, i.e., CreateProcess(), StartProcess() .
21 This provides backward compatibility for those clients who has custom
22 workflow processes but has not converted to BES.
23
24 ***************************************************************************************/
25
26 PROCEDURE Get_Fnd_User_Role
27 ( p_fnd_user_id IN NUMBER,
28 x_role_name OUT NOCOPY VARCHAR2,
29 x_role_display_name OUT NOCOPY VARCHAR2 );
30
31
32 FUNCTION CS_Custom_Rule_Func (p_subscription_guid in raw,
33 p_event in out nocopy WF_EVENT_T) return varchar2 is
34
35 l_event_name VARCHAR2(240) := p_event.getEventName( );
36 l_request_number VARCHAR2(64);
37 l_event_key VARCHAR2(240);
38 l_wf_process_id NUMBER;
39 l_item_key VARCHAR2(100);
40 l_user_id NUMBER;
41 l_manual_launch VARCHAR2(1);
42 l_raise_old_wf_flag VARCHAR2(1);
43
44
45 CURSOR sel_workflow_csr IS
46 SELECT nvl(status.close_flag,'N') close_flag,
47 inc.workflow_process_id,
48 cit.AUTOLAUNCH_WORKFLOW_FLAG,
49 cit.WORKFLOW,
50 inc.resource_type,
51 inc.incident_owner_id,
52 inc.incident_id,
53 inc.object_version_number
54 FROM cs_incident_statuses status,
55 cs_incidents_all_b inc,
56 cs_incident_types cit
57 WHERE inc.incident_number = l_request_number
58 AND inc.incident_status_id = status.incident_status_id
59 and cit.incident_type_id = inc.incident_type_id;
60
61 l_sel_workflow_rec sel_workflow_csr%ROWTYPE;
62
63 CURSOR l_servereq_csr IS
64 SELECT end_date
65 FROM wf_items
66 WHERE item_type = 'SERVEREQ'
67 AND item_key like l_request_number||'-%'
68 AND item_key NOT like l_request_number||'%EVT'
69 AND end_date IS NULL;
70
71 l_end_date DATE;
72
73
74 l_process_name VARCHAR2(30);
75
76 l_wf_proc_activity_name wf_process_activities.ACTIVITY_NAME%TYPE;
77 l_wf_activity_name WF_ACTIVITIES.name%TYPE;
78
79 CURSOR sel_workflow_act_csr IS
80 select wfa.name
81 from WF_ACTIVITIES wfa
82 where wfa.item_type = 'SERVEREQ'
83 and wfa.type ='EVENT'
84 and wfa.name = l_wf_proc_activity_name;
85
86
87 -- get the Name of the Start activity for the latest workflow process version
88 CURSOR sel_workflow_proc_act_csr IS
89 select wfpa.ACTIVITY_NAME
90 from wf_process_activities wfpa
91 where wfpa.PROCESS_ITEM_TYPE = 'SERVEREQ'
92 and wfpa.PROCESS_NAME = l_process_name
93 and wfpa.START_END = 'START'
94 ORDER BY wfpa.process_version DESC;
95
96
97
98 begin
99 -- <your executable statements>
100
101
102 -- Obtain values initialized from the parameter list.
103 l_request_number := p_event.GetValueForParameter('REQUEST_NUMBER');
104 l_user_id := p_event.GetValueForParameter('USER_ID');
105
106 -- rmanabat 01/06/03
107 l_manual_launch := p_event.GetValueForParameter('MANUAL_LAUNCH');
108
109 l_event_key := p_event.getEventKey();
110 l_item_key := SUBSTR(l_event_key, 1, INSTR(l_event_key, '-EVT') - 1);
111
112 --Changing the INSTR construct for bug#4007083- shdeshpa 12/02/2004
113 --l_wf_process_id := TO_NUMBER( SUBSTR(l_item_key, INSTR(l_item_key,'-')+1, length(l_item_key)) );
114
115 l_wf_process_id := TO_NUMBER( SUBSTR(l_item_key, INSTR(l_item_key,'-',-1,1)+1, length(l_item_key)) );
116
117
118 --INSERT INTO rm_tmp values (l_request_number, 'In Custom rule,l_manual_launch='||l_manual_launch,rm_tmp_seq.nextval);
119
120
121 OPEN sel_workflow_csr;
122 FETCH sel_workflow_csr INTO l_sel_workflow_rec;
123 IF (sel_workflow_csr%FOUND AND l_sel_workflow_rec.workflow IS NOT NULL) THEN
124
125
126 -- Before attempting to raise an event workflow,we check if the workflow process
127 -- is BES enabled, If not we will not call wf_engine.event().
128 -- This is important since calling event() api on a non BES workflow causes
129 -- it to raise an exception, which is handled in this custom rule function's
130 -- exception handler with a return status of 'ERROR'. A return status of ERROR
131 -- causes workflow to rollback any raised event performed, and since this is a
132 -- synchorous subsciption, the succeeding subscriptions(auto-notification)
133 -- will not be triggered since the event itself was rolled back.
134
135 l_process_name := l_sel_workflow_rec.workflow;
136
137 OPEN sel_workflow_proc_act_csr;
138 FETCH sel_workflow_proc_act_csr
139 INTO l_wf_proc_activity_name;
140 CLOSE sel_workflow_proc_act_csr;
141
142
143 OPEN sel_workflow_act_csr;
144 FETCH sel_workflow_act_csr
145 INTO l_wf_activity_name;
146
147 IF (sel_workflow_act_csr%FOUND) THEN
148 -- Workflow has a receive event as Start activity, so we can call
149 -- the wf_engine.event() api.
150
151
152 IF (l_sel_workflow_rec.AUTOLAUNCH_WORKFLOW_FLAG = 'Y') AND
153 (( l_sel_workflow_rec.resource_type = 'RS_EMPLOYEE' AND
154 -- Also need to check the owner_id being passed by CIC in order to launch
155 -- their workflow.
156 l_sel_workflow_rec.incident_owner_id IS NOT NULL) OR
157 l_sel_workflow_rec.workflow = 'CSEADUP') THEN --changed for bug 7484364
158 -- l_sel_workflow_rec.workflow <> 'CSEADUP') THEN --Added this for bug 6974942
159
160 IF(l_event_name = 'oracle.apps.cs.sr.ServiceRequest.created') THEN
161
162 l_raise_old_wf_flag := 'Y';
163
164 ELSIF(l_sel_workflow_rec.close_flag <> 'Y') THEN
165 OPEN l_servereq_csr;
166 FETCH l_servereq_csr INTO l_end_date;
167 IF (l_servereq_csr%NOTFOUND) THEN
168 l_raise_old_wf_flag := 'Y';
169 END IF;
170
171 END IF;
172
173
174 -- rmanabat 01/06/03
175 ELSIF (l_manual_launch = 'Y' AND
176 l_sel_workflow_rec.resource_type = 'RS_EMPLOYEE' AND
177 l_sel_workflow_rec.incident_owner_id IS NOT NULL AND
178 CS_Workflow_PKG.Is_Servereq_Item_Active
179 (p_request_number => l_request_number,
180 p_wf_process_id => l_sel_workflow_rec.workflow_process_id ) = 'N' AND
181 l_sel_workflow_rec.close_flag <> 'Y') THEN
182
183
184 l_raise_old_wf_flag := 'Y';
185
186 END IF;
187
188 END IF; /** IF (sel_workflow_act_csr%FOUND) **/
189
190 CLOSE sel_workflow_act_csr;
191
192 IF (l_raise_old_wf_flag = 'Y') THEN
193
194
195 --INSERT INTO rm_tmp values (l_request_number, 'Calling raise() from custom rule function ',rm_tmp_seq.nextval);
196
197 -- This will have a Synchronous subscription.
198 -- This will raise an exception when the workflow does not have a receive event
199 -- activity,i.e., the workflow is not BES enabled.
200 --BEGIN
201
202 WF_ENGINE.Event(
203 itemtype => 'SERVEREQ',
204 itemkey => l_item_key,
205 process_name => l_sel_workflow_rec.workflow,
206 event_message => p_event);
207 --EXCEPTION
208 -- Still return success because we don't want ither subscriptions to fail
209 -- --WHEN OTHERS THEN
210 -- null;
211 --END;
212
213
214 END IF;
215
216
217
218 END IF; /** IF (sel_workflow_csr%FOUND AND ... **/
219
220 CLOSE sel_workflow_csr;
221
222
223 return 'SUCCESS';
224
225
226 -- <optional code for WARNING>
227 -- WF_CORE.CONTEXT('<package name>', '<function name>',
228 -- p_event.getEventName( ), p_subscription_guid);
229 -- WF_EVENT.setErrorInfo(p_event, 'WARNING');
230 -- return 'WARNING';
231
232 EXCEPTION
233
234 /*************
235 WHEN l_UPDATE_FAILED THEN
236 IF sel_workflow_csr%ISOPEN THEN
237 CLOSE sel_workflow_csr;
238 END IF;
239 WF_CORE.CONTEXT('CS_WF_EVENT_PKG', 'CS_Custom_Rule_Func',
240 l_event_name , p_subscription_guid);
241 WF_EVENT.setErrorInfo(p_event, 'WARNING');
242 return 'WARNING';
243 ******/
244
245 WHEN others THEN
246
247 --INSERT INTO rm_tmp values (l_request_number, 'In custom rule function,WHEN others exception ',rm_tmp_seq.nextval);
248
249 IF sel_workflow_csr%ISOPEN THEN
250 CLOSE sel_workflow_csr;
251 END IF;
252 WF_CORE.CONTEXT('CS_WF_EVENT_PKG', 'CS_Custom_Rule_Func',
253 l_event_name , p_subscription_guid);
254 WF_EVENT.setErrorInfo(p_event, 'ERROR');
255 return 'WARNING';
256
257 END CS_Custom_Rule_Func;
258
259 /****************************************************************************************
260
261 CS_Custom_Rule_Func1
262 Added for 12.2 Auto Task Project - Workflow Enhancements by bkanimoz Feb 15,2011
263 Req:To launch a workflow on SR status transition
264
265 This custom rule function corresponds to the subscriptions for workflow
266 business events oracle.apps.cs.sr.ServiceRequest.statuschanged
267
268 This subscription will see if there are any workflow associated to the status transition.
269 If it is a BES workflow then it will call WF_ENGINE.Event .
270 If the proces is not BES compatible then it will try to launch the workflow process using
271 the old non-BES workflow API calls, i.e., CreateProcess(), StartProcess() .
272 This provides backward compatibility for those clients who has custom
273 workflow processes but has not converted to BES.
274 ***************************************************************************************/
275 FUNCTION CS_Custom_Rule_Func1 (p_subscription_guid in raw,
276 p_event in out nocopy WF_EVENT_T) return varchar2 is
277
278 l_event_name VARCHAR2(240) := p_event.getEventName( );
279 l_request_number VARCHAR2(64);
280 l_event_key VARCHAR2(240);
281 l_wf_process_id NUMBER;
282 l_item_key VARCHAR2(100);
283 l_user_id NUMBER;
284 l_manual_launch VARCHAR2(1);
285 l_raise_wf_flag VARCHAR2(1);
286 l_type_id NUMBER;
287 l_from_status_id NUMBER;
288 l_to_status_id NUMBER;
289 l_return_status VARCHAR2(1);
290 l_msg_count NUMBER;
291 l_msg_data VARCHAR2(2000);
292 l_itemtype VARCHAR2(50);
293 l_process_name VARCHAR2(30);
294 -- Start Bug 12411987
295 l_administrator VARCHAR2(100);
296 l_resp_appl_id NUMBER;
297 l_resp_id NUMBER;
298 l_ADMINISTRATOR_NOT_SET EXCEPTION;
299 l_initiator_role VARCHAR2(100);
300 l_dummy VARCHAR2(240);
301 -- End Bug 12411987
302
303 l_wf_proc_activity_name wf_process_activities.ACTIVITY_NAME%TYPE;
304 l_wf_activity_name WF_ACTIVITIES.name%TYPE;
305
306
307 CURSOR sel_workflow_csr IS
308 SELECT t1.workflow
309 FROM cs_sr_status_transitions t1,
310 cs_incident_types_b t2
311 WHERE t2.status_group_id=t1.status_group_id
312 AND t1.from_incident_status_id=l_from_status_id
313 AND t1.to_incident_status_id=l_to_status_id
314 AND t2.incident_type_id=l_type_id;
315
316 l_sel_workflow_rec sel_workflow_csr%ROWTYPE;
317
318 CURSOR l_servereq_csr IS
319 SELECT end_date
320 FROM wf_items
321 WHERE item_type = 'SERVEREQ'
322 AND item_key like l_request_number||'-%'
323 AND item_key NOT like l_request_number||'%EVT'
324 AND end_date IS NULL;
325
326 CURSOR sel_workflow_act_csr IS
327 SELECT wfa.name
328 FROM WF_ACTIVITIES wfa
329 WHERE wfa.item_type = 'SERVEREQ'
330 AND wfa.type ='EVENT'
331 AND wfa.name = l_wf_proc_activity_name;
332
333
334 -- get the Name of the Start activity for the latest workflow process version
335 CURSOR sel_workflow_proc_act_csr IS
336 select wfpa.ACTIVITY_NAME
337 from wf_process_activities wfpa
338 where wfpa.PROCESS_ITEM_TYPE = 'SERVEREQ'
339 and wfpa.PROCESS_NAME = l_process_name
340 and wfpa.START_END = 'START'
341 ORDER BY wfpa.process_version DESC;
342
343
344
345 BEGIN
346
347
348
349 If FND_PROFILE.VALUE('CS_SR_AUTO_WF_STATUS_TRANS') = 'Y' then
350
351 -- Obtain values initialized from the parameter list.
352 l_request_number := p_event.GetValueForParameter('REQUEST_NUMBER');
353 l_user_id := p_event.GetValueForParameter('USER_ID');
354 l_from_status_id := p_event.GetValueForParameter('PREV_STATUS_ID');
355 l_to_status_id := p_event.GetValueForParameter('CURR_STATUS_ID');
356 l_type_id := p_event.GetValueForParameter('PREV_TYPE_ID');
357
358 l_user_id := p_event.GetValueForParameter('USER_ID');
359 l_resp_id := p_event.GetValueForParameter('RESP_ID');
360 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
361
362
363 --insert into temp_bk values ('Inside custom_rule_func2 for l_request_number: '||l_request_number);
364 --insert into temp_bk values ('l_from_status_id : ' ||l_from_status_id ||'l_to_status_id : '||l_to_status_id);
365
366
367 l_manual_launch := p_event.GetValueForParameter('MANUAL_LAUNCH');
368
369
370 l_event_key := p_event.getEventKey();
371 l_item_key := SUBSTR(l_event_key, 1, INSTR(l_event_key, '-EVT') - 1);
372 l_wf_process_id := TO_NUMBER( SUBSTR(l_item_key, INSTR(l_item_key,'-',-1,1)+1, length(l_item_key)) );
373
374
375 OPEN sel_workflow_csr;
376 FETCH sel_workflow_csr INTO l_sel_workflow_rec;
377
378 IF (sel_workflow_csr%FOUND AND l_sel_workflow_rec.workflow IS NOT NULL) THEN
379
380
381 -- Before attempting to raise an event workflow,we check if the workflow process
382 -- is BES enabled, If not we will not call wf_engine.event().
383 -- This is important since calling event() api on a non BES workflow causes
384 -- it to raise an exception, which is handled in this custom rule function's
385 -- exception handler with a return status of 'ERROR'. A return status of ERROR
386 -- causes workflow to rollback any raised event performed, and since this is a
387 -- synchorous subsciption, the succeeding subscriptions(auto-notification)
388 -- will not be triggered since the event itself was rolled back.
389
390 l_process_name := l_sel_workflow_rec.workflow;
391
392
393 -- Start activity
394
395 OPEN sel_workflow_proc_act_csr;
396 FETCH sel_workflow_proc_act_csr
397 INTO l_wf_proc_activity_name;
398 CLOSE sel_workflow_proc_act_csr;
399
400 -- BES enabled
401
402 OPEN sel_workflow_act_csr;
403 FETCH sel_workflow_act_csr
404 INTO l_wf_activity_name;
405
406 IF (sel_workflow_act_csr%FOUND) THEN
407
408 l_raise_wf_flag := 'Y';
409 END IF;
410
411 CLOSE sel_workflow_act_csr;
412
413 IF (l_raise_wf_flag = 'Y') THEN
414
415 --BES enabled workflows
416
417 -- This will have a Synchronous subscription.
418 -- This will raise an exception when the workflow does not have a receive event
419 -- activity,i.e., the workflow is not BES enabled.
420 --BEGIN
421
422 WF_ENGINE.Event(
423 itemtype => 'SERVEREQ',
424 itemkey => l_item_key,
425 process_name => l_sel_workflow_rec.workflow,
426 event_message => p_event);
427
428 --EXCEPTION
429 -- Still return success because we don't want ither subscriptions to fail
430 -- --WHEN OTHERS THEN
431 -- null;
432 --END;
433
434 Else
435 -- for launching NON-BES workflows
436
437 l_itemtype :='SERVEREQ';
438
439 -- Create and launch the Workflow process
440 WF_ENGINE.CreateProcess(
441 itemtype => l_itemtype,
442 itemkey => l_item_key,
443 process => l_process_name );
444
445 WF_ENGINE.SetItemAttrText(
446 itemtype => l_itemtype,
447 itemkey => l_item_key,
448 aname => 'USER_ID',
449 --avalue => FND_GLOBAL.USER_ID );
450 avalue => l_user_id);
451 WF_ENGINE.SetItemAttrText(
452 itemtype => l_itemtype,
453 itemkey => l_item_key,
454 aname => 'RESP_ID',
455 --avalue => FND_GLOBAL.RESP_ID );
456 avalue => l_resp_id );
457
458 WF_ENGINE.SetItemAttrText(
459 itemtype => l_itemtype,
460 itemkey => l_item_key,
461 aname => 'RESP_APPL_ID',
462 --avalue => FND_GLOBAL.RESP_APPL_ID );
463 avalue => l_resp_appl_id );
464
465 -- Start Bug 12411987
466 BEGIN
467 l_administrator := FND_PROFILE.VALUE('CS_WF_ADMINISTRATOR');
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 null;
471 END;
472
473 IF (l_administrator IS NULL) THEN
474 raise l_ADMINISTRATOR_NOT_SET;
475 END IF;
476
477 get_fnd_user_role
478 ( p_fnd_user_id => l_user_id,
479 x_role_name => l_initiator_role,
480 x_role_display_name => l_dummy );
481
482
483 WF_ENGINE.SetItemAttrText(
484 itemtype => l_itemtype,
485 itemkey => l_item_key,
486 aname => 'INITIATOR_ROLE',
487 avalue => l_initiator_role );
488
489 WF_ENGINE.SetItemAttrText(
490 itemtype => l_itemtype,
491 itemkey => l_item_key,
492 aname => 'WF_ADMINISTRATOR',
493 avalue => l_administrator );
494
495
496 WF_ENGINE.SetItemOwner
497 ( itemtype => l_itemtype,
498 itemkey => l_item_key,
499 owner => l_initiator_role );
500 --End Bug 12411987
501 WF_ENGINE.StartProcess(
502 itemtype => l_itemtype,
503 itemkey => l_item_key );
504
505 END IF;
506
507
508
509 END IF; /** IF (sel_workflow_csr%FOUND AND ... **/
510
511 CLOSE sel_workflow_csr;
512 return 'SUCCESS';
513
514
515 END IF;
516
517 EXCEPTION
518
519 WHEN others THEN
520
521 --INSERT INTO temp_bk values (l_request_number, 'In custom rule function 1,WHEN others exception ');
522
523 IF sel_workflow_csr%ISOPEN THEN
524 CLOSE sel_workflow_csr;
525 END IF;
526 WF_CORE.CONTEXT('CS_WF_EVENT_PKG', 'CS_Custom_Rule_Func1',
527 l_event_name , p_subscription_guid);
528 WF_EVENT.setErrorInfo(p_event, 'ERROR');
529 return 'WARNING';
530
531 END CS_Custom_Rule_Func1;
532
533 /*************************************************************************
534 --
535 -- Raise_ServiceRequest_Event()
536 --
537 -- This is the wrapper API to be called by the Create/Update
538 -- Service Request API for validating event parameters and raising
539 -- the Workflow business events . Subscriptions to these business events
540 -- will in turn launch the old seeded workflows, client custom workflows,
541 -- as well as the new auto-notification and update workflows.
542 -- We are also able to execute Non BES client workflows , to maintain
543 -- backward compatibility.
544 --
545 -- Modification History:
546 --
547 -- Date Name Desc
548 -- ---------- --------- ---------------------------------------------
549 -- 04/29/04 RMANABAT Fix for bug 3628552. Replaced contact name
550 -- separator with ';' .
551 -- 06/09/04 RMANABAT Fix for bug 3663881. Changed #FROM_ROLE to SENDER_ROLE
552 -- obtained from profile default resource.
553 -- 20-Jul-2005 aneemuch Release 12.0 changes. Following change made
554 -- 1. Changed Raise_ServiceRequest_Event to
555 -- raise contacts BES only when party role
556 -- code is 'CONTACT'
557 -- 2. To raise associated party added BES
558 -- when associated party contact added
559 -- 31-Oct-2005 aneemuch Fixed FP bug 4007088.
560 -- 06-Mar-2006 spusegao Modified to use value from l_administrator variable to set the an item attribute.
561 -- WF_ADMINISTRATOR. Earlier the value from l_initiator_role was used. (Bug # 5080255)
562 -- 12-Mar-2007 PadmanabhaRao Modified the Cursor l_sel_party_name_csr as per perf bug 5730498.
563 -- 12-Jun-2007 BKANIMOZ Bug fix for 6069111.Added a Cursor get_status_id_csr.
564 -- 29-APR-2009 VPREMACH Bug 7580964. Notification rules should not be checked when raising the status changed event.
565 *************************************************************************/
566
567 PROCEDURE Raise_ServiceRequest_Event(
568 p_api_version IN NUMBER,
569 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
570 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
571 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
572 p_Event_Code IN VARCHAR2,
573 p_Incident_Number IN VARCHAR2,
574 p_USER_ID IN NUMBER DEFAULT FND_GLOBAL.USER_ID, -- p_last_updated_by from Update_ServiceREquest()
575 p_RESP_ID IN NUMBER, -- p_resp_id from Update_ServiceREquest()
576 p_RESP_APPL_ID IN NUMBER, -- p_resp_appl_id from Update_ServiceREquest()
577 p_Old_SR_Rec IN CS_ServiceRequest_PVT.service_request_rec_type,
578 p_New_SR_Rec IN CS_ServiceRequest_PVT.service_request_rec_type,
579 p_Contacts_Table IN CS_ServiceRequest_PVT.contacts_table,
580 p_Link_Rec IN CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE,
581 p_wf_process_id IN NUMBER, -- from Update_ServiceRequest() parameter list, important
582 -- to pass this to prevent unwanted recursive calls
583 p_owner_id IN NUMBER, -- passed by CIC
584 p_wf_manual_launch IN VARCHAR2 , -- flag for event raised from UI launch_wf().
585 -- p_Event_Code for manually launched workflow
586 -- should always be UPDATE_SERVICE_REQUEST
587 x_wf_process_id OUT NOCOPY NUMBER,
588 x_return_status OUT NOCOPY VARCHAR2,
589 x_msg_count OUT NOCOPY NUMBER,
590 x_msg_data OUT NOCOPY VARCHAR2) IS
591
592 l_dummy VARCHAR2(240);
593 l_initiator_role VARCHAR2(100);
594
595 l_param_list wf_parameter_list_t;
596 l_event_key VARCHAR2(240);
597 l_event_id NUMBER;
598 --l_request_status VARCHAR2(30);
599 l_linked_incident_number VARCHAR2(64);
600 l_old_request_status VARCHAR2(64);
601 l_new_request_status VARCHAR2(64);
602 l_business_event VARCHAR2(240);
603 l_contact_index BINARY_INTEGER;
604 l_new_contact_point_name VARCHAR2(2000);
605 l_new_contact_point_id VARCHAR2(2000);
606
607 l_new_associated_party_name VARCHAR2(2000);
608 l_new_associated_party_id VARCHAR2(2000);
609
610 l_contact_name VARCHAR2(360);
611 l_contact_party_id NUMBER;
612 l_administrator VARCHAR2(100);
613 --l_close_flag VARCHAR2(1);
614 l_raise_old_wf_flag VARCHAR2(1);
615 --l_msg_index_OUT NUMBER;
616
617
618 --l_service_request_rec CS_ServiceRequest_PVT.service_request_rec_type;
619 l_notes CS_SERVICEREQUEST_PVT.notes_table;
620 l_contacts CS_SERVICEREQUEST_PVT.contacts_table;
621 l_return_status VARCHAR2(1);
622 l_msg_count NUMBER;
623 l_msg_data VARCHAR2(2000);
624 l_interaction_id number;
625 out_wf_process_id NUMBER;
626
627 l_link_type_name VARCHAR2(240);
628
629 l_itemkey VARCHAR2(240);
630 l_pos NUMBER;
631
632 l_resource_id NUMBER;
633 l_role_display_name VARCHAR2(360);
634 l_role_name VARCHAR2(320);
635
636 l_INVALID_EVENT_ARGS EXCEPTION;
637 l_INVALID_EVENT_CODE EXCEPTION;
638 l_API_ERROR EXCEPTION;
639
640
641 /* Roopa - begin
642 Fix for bug 2788761
643 A SR contact can also be an employee which means
644 that we will have to check on per_people_f table also.
645 Formed a union because of this reason.
646 */
647 --Padmanabha Rao Modified the cursor for bug 5730498.
648 /* CURSOR l_sel_party_name_csr IS
649 SELECT P.PARTY_NAME
650 FROM HZ_PARTIES P,
651 CS_HZ_SR_CONTACT_POINTS C
652 WHERE P.PARTY_ID = l_contact_party_id AND C.CONTACT_TYPE <> 'EMPLOYEE' AND C.PARTY_ID= P.PARTY_ID
653 UNION
654 SELECT P.FULL_NAME
655 FROM PER_ALL_PEOPLE_F P
656 WHERE P.PERSON_ID = l_contact_party_id AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(P.EFFECTIVE_START_DATE, SYSDATE))
657 AND TRUNC(NVL(P.EFFECTIVE_END_DATE, SYSDATE))
658 AND EXISTS (SELECT 1
659 FROM CS_HZ_SR_CONTACT_POINTS C
660 WHERE C.CONTACT_TYPE = 'EMPLOYEE'
661 AND C.PARTY_ID = P.PERSON_ID) ;
662
663 --Padmanabha Rao commented below cursor for bug 5730498.
664 /*
665 CURSOR l_sel_party_name_csr IS
666 SELECT p.PARTY_NAME
667 FROM HZ_PARTIES p, cs_hz_sr_contact_points c
668 WHERE p.PARTY_ID = c.party_id and
669 c.contact_type <> 'EMPLOYEE' and c.party_id=l_contact_party_id
670 UNION
671 select p.full_name
672 from per_all_people_f p, cs_hz_sr_contact_points c
673 where p.person_id = c.party_id
674 and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(p.effective_start_date, SYSDATE))
675 and TRUNC(NVL(p.effective_end_date, SYSDATE))
676 and c.contact_type = 'EMPLOYEE' and c.party_id=l_contact_party_id;
677 */
678
679
680
681 /*
682 Roopa - end
683 Fix for bug 2788761
684 */
685
686
687 --Bkanimoz replaced the above cursor with the below 2 cursors for bug 9790566
688
689 CURSOR l_sel_party_name_csr_non_emp IS
690 SELECT /*+ ORDERED */ P.PARTY_NAME
691 FROM CS_HZ_SR_CONTACT_POINTS C
692 , HZ_PARTIES P
693 WHERE C.PARTY_ID = l_contact_party_id
694 AND C.CONTACT_TYPE <> 'EMPLOYEE'
695 AND C.PARTY_ID= P.PARTY_ID
696 AND ROWNUM <= 1;
697
698 CURSOR l_sel_party_name_csr_emp IS
699 SELECT P.FULL_NAME
700 FROM PER_ALL_PEOPLE_F P
701 WHERE P.PERSON_ID = l_contact_party_id
702 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(P.EFFECTIVE_START_DATE, SYSDATE)) AND TRUNC(NVL(P.EFFECTIVE_END_DATE, SYSDATE))
703 AND EXISTS (SELECT 1
704 FROM CS_HZ_SR_CONTACT_POINTS C
705 WHERE C.CONTACT_TYPE = 'EMPLOYEE'
706 AND C.PARTY_ID = P.PERSON_ID
707 );
708 --end bug 9790566
709
710
711 CURSOR l_sel_request_csr IS
712 SELECT nvl(status.close_flag,'N') close_flag,
713 inc.workflow_process_id,
714 cit.AUTOLAUNCH_WORKFLOW_FLAG,
715 cit.WORKFLOW,
716 inc.resource_type,
717 inc.incident_owner_id,
718 inc.incident_id,
719 inc.object_version_number
720 FROM cs_incident_statuses status,
721 cs_incidents_all_b inc,
722 cs_incident_types cit
723 WHERE inc.incident_number = p_Incident_Number
724 AND inc.incident_status_id = status.incident_status_id
725 and cit.incident_type_id = inc.incident_type_id;
726
727 l_sel_request_rec l_sel_request_csr%ROWTYPE;
728
729
730 /*
731 02/07/2003
732 Roopa - fix for bug # 2788610
733 The PREV_OWNER_ID wf attribute value should be
734 the source_id from jtf_rs_resource_extns table
735 and NOT incident_owner_id.
736 CS_WORKFLOW_PUB.Get_Employee_Role() procedure was failing because of this discripency
737 */
738
739 CURSOR l_cs_sr_get_empid_csr(p_resource_id IN NUMBER) IS
740 select emp.source_id , emp.resource_name
741 from jtf_rs_resource_extns_vl emp
742 where emp.resource_id = p_resource_id;
743 l_cs_sr_get_empid_rec l_cs_sr_get_empid_csr%ROWTYPE;
744 l_prev_employee_id NUMBER;
745
746 /**
747 This fixes the scenario :
748 An Auto-launch workflow was executed, and later aborted.
749 When the workflow is launched again using the UI
750 tools menu, the UI does a commit which makes another call
751 to the update_servicerequest() api. This prevents another
752 seeded workflow from launching by looking if one is already
753 running.
754 **/
755
756 CURSOR l_servereq_csr IS
757 SELECT end_date,item_key
758 FROM wf_items
759 WHERE item_type = 'SERVEREQ'
760 AND item_key like p_Incident_Number||'-%'
761 AND item_key NOT like p_Incident_Number||'%EVT'
762 AND end_date IS NULL;
763
764 l_end_date DATE;
765
766 CURSOR l_sel_adhocrole_csr(c_role_name IN VARCHAR2) IS
767 SELECT display_name,expiration_date
768 FROM wf_local_roles
769 WHERE name = c_role_name;
770 l_sel_adhocrole_rec l_sel_adhocrole_csr%ROWTYPE;
771
772
773 -- 5238921
774 CURSOR c_party_role_csr (p_party_role_code in VARCHAR2) IS
775 SELECT name
776 FROM cs_party_roles_vl
777 WHERE party_role_code = p_party_role_code;
778
779 l_new_party_role_name VARCHAR2(2000);
780 l_temp_new_party_role_name VARCHAR2(100);
781 -- 5238921_eof
782
783 --Bug Fix for 6069111.Added by bkanimoz on 12-Jun-2007
784 l_status_id NUMBER; --Bug 5948714
785
786 CURSOR get_status_id_csr IS
787 SELECT csat.INCIDENT_STATUS_ID
788 --INTO l_status_id
789 FROM CS_SR_ACTION_TRIGGERS csat,
790 CS_SR_ACTION_DETAILS csad,
791 CS_SR_EVENT_CODES_B cec
792 WHERE
793 cec.WF_BUSINESS_EVENT_ID = 'oracle.apps.cs.sr.ServiceRequest.statuschanged'
794 and csat.EVENT_CODE = cec.EVENT_CODE
795 and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
796 and TRUNC(NVL(csat.end_date_active, SYSDATE))
797 and csad.event_condition_id = csat.event_condition_id
798 and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
799 and TRUNC(NVL(csad.end_date_active, SYSDATE))
800 and csat.from_to_status_code IS not NULL
801 and csad.resolution_code IS NULL
802 and csat.incident_status_id IS NOT NULL
803 and csat.incident_status_id = (select incident_status_id from cs_incidents_all_b
804 where incident_number = p_incident_number)
805 and csad.action_code like 'NOTIFY%';
806
807
808
809
810 BEGIN
811
812
813 --dbms_output.put_line('Start of Raise_ServiceRequest_Event');
814
815 -- Initialize return status to SUCCESS
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 --INSERT INTO rm_tmp values (p_Incident_Number, 'In Start of BES wrapper ',rm_tmp_seq.nextval);
819
820 IF ( p_Incident_Number IS NULL) THEN
821
822 RAISE l_INVALID_EVENT_ARGS;
823
824 ELSIF (p_wf_process_id IS NOT NULL) THEN
825 --Do NOTHING. WE DON't HAVE TO RAISE a business event since this
826 --is just a recursive call to Update_ServiceRequest() API to update
827 --the workflow process id when a workflow is launched.
828 null;
829 --INSERT INTO rm_tmp values (p_Incident_Number, 'In BES wrapper,p_wf_process_id IS NOT NULL ',rm_tmp_seq.nextval);
830
831 ELSE
832
833 --INSERT INTO rm_tmp values (p_Incident_Number, 'In BES wrapper,p_wf_process_id IS NULL ',rm_tmp_seq.nextval);
834
835 -- Derive Role from User ID
836 IF (p_USER_ID IS NOT NULL) THEN
837 CS_WF_AUTO_NTFY_UPDATE_PKG.get_fnd_user_role
838 ( p_fnd_user_id => p_USER_ID,
839 x_role_name => l_initiator_role,
840 x_role_display_name => l_dummy );
841 END IF;
842
843
844 /******************************************************************
845 This section sets the Event Parameter List. These parameters are
846 converted to workflow item attributes.
847 *******************************************************************/
848
849 wf_event.AddParameterToList(p_name => 'REQUEST_NUMBER',
850 p_value => p_Incident_Number,
851 p_parameterlist => l_param_list);
852
853 /* Roopa - Begin - Fix for bug 3360069 */
854 /* Initializing the vars with FND_GLOBAL values if the input vars are null in value */
855 IF(p_USER_ID is NULL or p_USER_ID = -1) THEN
856 wf_event.AddParameterToList(p_name => 'USER_ID',
857 p_value => FND_GLOBAL.USER_ID,
858 p_parameterlist => l_param_list);
859 ELSE
860 wf_event.AddParameterToList(p_name => 'USER_ID',
861 p_value => p_USER_ID,
862 p_parameterlist => l_param_list);
863
864 END IF;
865
866 IF(p_RESP_ID is NULL or p_RESP_ID = -1) THEN
867 wf_event.AddParameterToList(p_name => 'RESP_ID',
868 p_value => FND_GLOBAL.RESP_ID,
869 p_parameterlist => l_param_list);
870 ELSE
871 wf_event.AddParameterToList(p_name => 'RESP_ID',
872 p_value => p_RESP_ID,
873 p_parameterlist => l_param_list);
874 END IF;
875
876 IF(p_RESP_APPL_ID is NULL or p_RESP_APPL_ID = -1) THEN
877 wf_event.AddParameterToList(p_name => 'RESP_APPL_ID',
878 p_value => FND_GLOBAL.RESP_APPL_ID,
879 p_parameterlist => l_param_list);
880 ELSE
881 wf_event.AddParameterToList(p_name => 'RESP_APPL_ID',
882 p_value => p_RESP_APPL_ID,
883 p_parameterlist => l_param_list);
884 END IF;
885 /* Roopa - End - Fix for bug 3360069 */
886
887 wf_event.AddParameterToList(p_name => 'INITIATOR_ROLE',
888 p_value => l_initiator_role,
889 p_parameterlist => l_param_list);
890
891 -- We need this parameter since manual launch will also unnecessarily launch the
892 -- auto-notify workflow. Validation not to process will have to be done in the
893 -- activity of the workflow itself.
894 wf_event.AddParameterToList(p_name => 'MANUAL_LAUNCH',
895 p_value => p_wf_manual_launch,
896 p_parameterlist => l_param_list);
897
898
899 l_resource_id := NVL(FND_PROFILE.VALUE('CS_SR_DEFAULT_SYSTEM_RESOURCE'), -1);
900 OPEN l_cs_sr_get_empid_csr(l_resource_id);
901 FETCH l_cs_sr_get_empid_csr INTO l_cs_sr_get_empid_rec;
902
903 l_role_name := 'CS_WF_ROLE_DUMMY';
904 OPEN l_sel_adhocrole_csr(l_role_name);
905 FETCH l_sel_adhocrole_csr INTO l_sel_adhocrole_rec;
906
907 IF (l_sel_adhocrole_csr%FOUND) THEN
908
909 -- expired adhoc role, renew expiration date.
910 IF (nvl(l_sel_adhocrole_rec.EXPIRATION_DATE, SYSDATE) < sysdate) THEN
911 wf_directory.SetAdHocRoleExpiration(role_name => l_role_name,
912 expiration_date => sysdate + 365);
913 END IF;
914
915 -- chnage display name if needed.
916 IF (l_sel_adhocrole_rec.display_name <> l_cs_sr_get_empid_rec.resource_name) THEN
917 l_role_display_name := l_cs_sr_get_empid_rec.resource_name;
918 wf_directory.SetAdHocRoleAttr(role_name => l_role_name,
919 display_name => l_role_display_name);
920 END IF;
921
922 ELSE
923
924 wf_directory.CreateAdHocRole(role_name => l_role_name,
925 role_display_name => l_role_display_name,
926 expiration_date => sysdate + 365);
927
928 l_role_display_name := l_cs_sr_get_empid_rec.resource_name;
929
930 wf_directory.SetAdHocRoleAttr(role_name => l_role_name,
931 display_name => l_role_display_name);
932 END IF;
933
934 IF l_sel_adhocrole_csr%ISOPEN THEN
935 CLOSE l_sel_adhocrole_csr;
936 END IF;
937 IF l_cs_sr_get_empid_csr%ISOPEN THEN
938 CLOSE l_cs_sr_get_empid_csr;
939 END IF;
940
941 wf_event.AddParameterToList(p_name => 'SENDER_ROLE',
942 p_value => l_role_name,
943 p_parameterlist=> l_param_list);
944
945
946 --dbms_output.put_line('Setting CS_WF_ADMINISTRATOR');
947
948 BEGIN
949 l_administrator := FND_PROFILE.VALUE('CS_WF_ADMINISTRATOR');
950
951 wf_event.AddParameterToList(p_name => 'WF_ADMINISTRATOR',
952 p_value => l_administrator,
953 p_parameterlist => l_param_list);
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 null;
957 END;
958
959 -- Setting SR Status Changed attribute
960 IF (p_Old_SR_Rec.STATUS_ID <> p_New_SR_Rec.STATUS_ID) THEN
961
962 SELECT name
963 INTO l_old_request_status
964 FROM cs_incident_statuses_vl
965 WHERE INCIDENT_STATUS_ID = p_Old_SR_Rec.STATUS_ID;
966
967 wf_event.AddParameterToList(p_name => 'REQUEST_STATUS_OLD',
968 p_value => l_old_request_status,
969 p_parameterlist => l_param_list);
970
971
972 -- added the below code for bug 11076570
973 -- A new parameter called as 'REQUEST_STATUS_NEW' is added to the event payload
974 -- This is to avoid missing/wrong notifications being sent when status change happens rapidly
975
976 SELECT name
977 INTO l_new_request_status
978 FROM cs_incident_statuses_vl
979 WHERE INCIDENT_STATUS_ID = p_New_SR_Rec.STATUS_ID;
980
981
982 wf_event.AddParameterToList(p_name => 'REQUEST_STATUS_NEW',
983 p_value =>l_new_request_status,
984 p_parameterlist => l_param_list);
985 END IF;
986
987
988 -- Setting SR Reassigned attribute
989
990 /* Roopa - begin
991 Fix for bug 2799545 - Changed the IF condition to include
992 null owner to not null owner
993 not null owner to null owner
994 */
995
996 IF (p_Old_SR_Rec.OWNER_ID <> p_New_SR_Rec.OWNER_ID OR
997 (p_Old_SR_Rec.OWNER_ID is null and p_New_SR_Rec.OWNER_ID is not null) OR
998 (p_New_SR_Rec.OWNER_ID is null and p_Old_SR_Rec.OWNER_ID is not null)) THEN
999
1000 /* Roopa - end
1001 Fix for bug 2799545
1002 */
1003
1004 /* Roopa - begin
1005 Fix for bug # 2788610
1006 */
1007 OPEN l_cs_sr_get_empid_csr(p_Old_SR_Rec.OWNER_ID);
1008 FETCH l_cs_sr_get_empid_csr INTO l_cs_sr_get_empid_rec;
1009 IF (l_cs_sr_get_empid_csr%FOUND) THEN
1010 wf_event.AddParameterToList(p_name => 'PREV_OWNER_ID',
1011 p_value => l_cs_sr_get_empid_rec.source_id,
1012 p_parameterlist => l_param_list);
1013 ELSE
1014 wf_event.AddParameterToList(p_name => 'PREV_OWNER_ID',
1015 p_value => p_Old_SR_Rec.OWNER_ID,
1016 p_parameterlist => l_param_list);
1017 END IF;
1018 CLOSE l_cs_sr_get_empid_csr;
1019 /* Roopa - end
1020 Fix for bug # 2788610
1021 */
1022
1023 END IF;
1024
1025 --dbms_output.put_line('Setting NTFY_LINK_TYPE ');
1026
1027 -- Setting Link Created/Deleted attribute
1028 IF (p_Link_Rec.LINK_TYPE_ID IS NOT NULL) THEN
1029
1030 SELECT name
1031 INTO l_link_type_name
1032 FROM CS_SR_LINK_TYPES_VL
1033 WHERE link_type_id = p_Link_Rec.LINK_TYPE_ID;
1034
1035 wf_event.AddParameterToList(p_name => 'NTFY_LINK_TYPE',
1036 p_value => l_link_type_name,
1037 p_parameterlist => l_param_list);
1038 END IF;
1039
1040
1041 /*******************************************************
1042 Important Note:
1043 Links treats the queried SR in the UI as the SUBJECT and
1044 the linked SR as the object. In our workflow logic, it is
1045 the opposite. So we have to reverse this assignment. In
1046 this case, p_Incident_Number is the subject number.
1047 ********************************************************/
1048
1049 IF (p_Link_Rec.OBJECT_NUMBER IS NOT NULL) THEN
1050
1051 wf_event.AddParameterToList(p_name => 'NTFY_LINKED_INCIDENT_NUMBER',
1052 p_value => p_Link_Rec.OBJECT_NUMBER,
1053 p_parameterlist => l_param_list);
1054 END IF;
1055
1056
1057 /******************************************************
1058 Setting New Contacts Added attributes.
1059 We build a list of new contact names to be used for the
1060 notification message, and a list of new contact point
1061 IDs to be used for recipients of the ntfxn. In the future,
1062 the contact point names might be queried in the PL/SQL
1063 document attribute of the message, instead of putting it
1064 in a single line of text.
1065 *****************************************************/
1066
1067 l_contact_index := p_Contacts_Table.FIRST;
1068
1069 WHILE l_contact_index IS NOT NULL LOOP
1070
1071 IF (p_Contacts_Table(l_contact_index).SR_CONTACT_POINT_ID IS NULL OR
1072 p_Contacts_Table(l_contact_index).SR_CONTACT_POINT_ID = FND_API.G_MISS_NUM ) THEN -- new contact
1073
1074 l_contact_party_id := p_Contacts_Table(l_contact_index).PARTY_ID;
1075
1076
1077 -- bug 9790566
1078 OPEN l_sel_party_name_csr_non_emp;
1079 FETCH l_sel_party_name_csr_non_emp INTO l_contact_name;
1080 IF l_sel_party_name_csr_non_emp%NOTFOUND THEN
1081 l_contact_name := NULL;
1082 END IF;
1083 CLOSE l_sel_party_name_csr_non_emp;
1084
1085 IF l_contact_name IS NULL THEN
1086 OPEN l_sel_party_name_csr_emp;
1087 FETCH l_sel_party_name_csr_emp INTO l_contact_name;
1088 IF l_sel_party_name_csr_emp%NOTFOUND THEN
1089 l_contact_name := NULL;
1090 END IF;
1091 CLOSE l_sel_party_name_csr_emp;
1092 END IF;
1093
1094
1095 /* OPEN l_sel_party_name_csr;
1096 FETCH l_sel_party_name_csr INTO l_contact_name;*/
1097
1098 -- IF (l_sel_party_name_csr%FOUND AND l_contact_name IS NOT NULL )THEN
1099 IF (l_contact_name IS NOT NULL )THEN
1100
1101 IF (p_Contacts_table(l_contact_index).party_role_code = 'CONTACT' OR
1102 p_Contacts_table(l_contact_index).party_role_code = FND_API.G_MISS_CHAR OR
1103 p_Contacts_table(l_contact_index).party_role_code IS NULL ) THEN
1104
1105 -- Check for l_new_contact_point_name list length not to exceed 2000 .
1106 IF (nvl(LENGTH(l_new_contact_point_name),0) + nvl(LENGTH(l_contact_name),0) + 1) <= 2000 THEN
1107 IF (l_new_contact_point_name IS NULL) THEN
1108
1109 l_new_contact_point_name := l_contact_name;
1110
1111 /************************************
1112 Roopa - begin
1113 Fix for bug 2788761
1114 contact_point_id will be hz_contact point id for contact type != employee
1115 contact_point_id will be per_all_people_f person id for contact type = employee
1116 since contact point id will be null for this contact type
1117 *************************************/
1118
1119 IF(p_Contacts_Table(l_contact_index).CONTACT_TYPE <> 'EMPLOYEE') THEN
1120 l_new_contact_point_id := TO_CHAR(p_Contacts_Table(l_contact_index).contact_point_id);
1121 ELSE
1122 l_new_contact_point_id := TO_CHAR(l_contact_party_id);
1123 END IF;
1124
1125 ELSE
1126
1127 l_new_contact_point_name := l_new_contact_point_name || ';' || l_contact_name;
1128 IF(p_Contacts_Table(l_contact_index).CONTACT_TYPE <> 'EMPLOYEE') THEN
1129 l_new_contact_point_id := l_new_contact_point_id || ' '
1130 || TO_CHAR(p_Contacts_Table(l_contact_index).contact_point_id);
1131 ELSE
1132 l_new_contact_point_id := l_new_contact_point_id || ' ' || TO_CHAR(l_contact_party_id);
1133 END IF;
1134
1135 END IF;
1136 ELSE
1137 EXIT;
1138 END IF;
1139 ELSE
1140 IF (nvl(LENGTH(l_new_associated_party_name),0) + nvl(LENGTH(l_contact_name),0) + 1) <= 2000 THEN
1141 IF (l_new_associated_party_name IS NULL) THEN
1142 l_new_associated_party_name := l_contact_name;
1143 l_new_associated_party_id := TO_CHAR(p_Contacts_Table(l_contact_index).contact_point_id);
1144 ELSE
1145 l_new_associated_party_name := l_new_associated_party_name || ';' || l_contact_name;
1146 l_new_associated_party_id := l_new_associated_party_id || ' ' ||
1147 TO_CHAR(p_Contacts_Table(l_contact_index).contact_point_id);
1148 END IF;
1149
1150 -- 5238921
1151 Open c_party_role_csr(p_Contacts_Table(l_contact_index).party_role_code);
1152 Fetch c_party_role_csr Into l_temp_new_party_role_name;
1153 Close c_party_role_csr;
1154
1155 IF l_new_party_role_name is null Then
1156 l_new_party_role_name := l_temp_new_party_role_name;
1157 ELSE
1158 l_new_party_role_name := l_new_party_role_name ||';'||l_temp_new_party_role_name;
1159 END IF;
1160 -- 5238921_eof
1161
1162 ELSE
1163 EXIT;
1164 END IF;
1165 END IF; -- party_role_code = 'CONTACTS'
1166
1167 END IF; -- (l_sel_party_name_csr%FOUND AND l_contact_name IS NOT NULL )
1168 -- CLOSE l_sel_party_name_csr;
1169
1170 END IF; -- IF (p_Contacts_Table(l_contact_index).SR ...
1171 l_contact_index := p_Contacts_Table.NEXT(l_contact_index);
1172
1173 END LOOP;
1174
1175 IF (l_new_contact_point_name IS NOT NULL) THEN
1176 wf_event.AddParameterToList(p_name => 'NEW_CONTACT_POINT_NAME',
1177 p_value => l_new_contact_point_name,
1178 p_parameterlist => l_param_list);
1179 wf_event.AddParameterToList(p_name => 'NEW_CONTACT_POINT_ID_LIST',
1180 p_value => l_new_contact_point_id,
1181 p_parameterlist => l_param_list);
1182 END IF;
1183
1184 IF (l_new_associated_party_name IS NOT NULL) THEN
1185 wf_event.AddParameterToList(p_name => 'NEW_ASSOCIATED_PARTY_NAME',
1186 p_value => l_new_associated_party_name,
1187 p_parameterlist => l_param_list);
1188 wf_event.AddParameterToList(p_name => 'NEW_ASSOCIATED_PARTY_ID_LIST',
1189 p_value => l_new_associated_party_id,
1190 p_parameterlist => l_param_list);
1191
1192 -- 523892
1193 wf_event.AddParameterToList(p_name => 'NEW_PARTY_ROLE_NAME',
1194 p_value => l_new_party_role_name,
1195 p_parameterlist => l_param_list);
1196
1197 -- 5238921_eof
1198 END IF;
1199
1200
1201
1202 /**********************************************************
1203 This section establishes the WF business events to raise.
1204 ***********************************************************/
1205
1206 IF (p_Event_Code = 'UPDATE_SERVICE_REQUEST') THEN
1207
1208 /* Roopa - begin
1209 Fix for bug # 2809232
1210 */
1211 wf_event.AddParameterToList(p_name => 'PREV_TYPE_ID',
1212 p_value => p_Old_SR_Rec.type_id,
1213 p_parameterlist => l_param_list);
1214
1215 wf_event.AddParameterToList(p_name => 'PREV_SEVERITY_ID',
1216 p_value => p_Old_SR_Rec.severity_id,
1217 p_parameterlist => l_param_list);
1218
1219 wf_event.AddParameterToList(p_name => 'PREV_STATUS_ID',
1220 p_value => p_Old_SR_Rec.status_id,
1221 p_parameterlist => l_param_list);
1222 --bkanimoz for 12.2 Workflow enhancements
1223 -- Req: To Launch a Workflow when SR status transition happens
1224 wf_event.AddParameterToList(p_name => 'CURR_STATUS_ID',
1225 p_value => p_New_SR_Rec.status_id,
1226 p_parameterlist => l_param_list);
1227
1228 wf_event.AddParameterToList(p_name => 'PREV_URGENCY_ID',
1229 p_value => p_Old_SR_Rec.urgency_id,
1230 p_parameterlist => l_param_list);
1231
1232 wf_event.AddParameterToList(p_name => 'PREV_SUMMARY',
1233 p_value => p_Old_SR_Rec.summary,
1234 p_parameterlist => l_param_list);
1235
1236 /* Roopa - end
1237 Fix for bug # 2809232
1238 */
1239
1240 -- Aside from raising the Update business event, we may have to raise
1241 -- other business events, i.e., Reassign, StatusUpdate. etc. This
1242 -- depends on what Service Request attributes was updated.
1243
1244 -- Check for Status Update event.
1245 IF (p_Old_SR_Rec.STATUS_ID <> p_New_SR_Rec.STATUS_ID) THEN
1246
1247 SELECT cs_wf_process_id_s.nextval
1248 INTO l_event_id
1249 FROM dual;
1250 -- Construct the unique event key
1251 l_event_key := p_Incident_Number ||'-'||to_char(l_event_id) || '-EVT';
1252 --Commented the below code for bug 7580964
1253 -- Bug fix for 6069111.Added by bkanimoz on 12-Jun-2007
1254
1255 /* OPEN get_status_id_csr;
1256 LOOP
1257 FETCH get_status_id_csr INTO l_status_id;
1258 EXIT WHEN get_status_id_csr%NOTFOUND;
1259 IF ( p_New_SR_Rec.STATUS_ID =l_status_id ) THEN*/
1260 BEGIN
1261 --RAISE the WF Business event.
1262 wf_event.raise(p_event_name => 'oracle.apps.cs.sr.ServiceRequest.statuschanged',
1263 p_event_key => l_event_key,
1264 p_parameters => l_param_list);
1265 EXCEPTION -- Added the exception for bug 8849523
1266 WHEN OTHERS THEN
1267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1268 END;
1269
1270 /* Roopa - begin
1271 Fix for bug 2798269
1272 */
1273 -- Standard check of p_commit
1274 IF FND_API.To_Boolean(p_commit) THEN
1275 COMMIT WORK;
1276 END IF;
1277 /* END IF;
1278 END LOOP;
1279 CLOSE get_status_id_csr;*/
1280 -- commit;
1281
1282 END IF;
1283
1284 /************************************
1285 Roopa - begin
1286 Fix for bug 2799545 - Changed the IF condition to include
1287 null owner to not null owner
1288 not null owner to null owner
1289 **************************************/
1290
1291 -- Check for SR reassigned event.
1292 IF (p_Old_SR_Rec.OWNER_ID <> p_New_SR_Rec.OWNER_ID OR
1293 (p_Old_SR_Rec.OWNER_ID is null and p_New_SR_Rec.OWNER_ID is not null) OR
1294 (p_New_SR_Rec.OWNER_ID is null and p_Old_SR_Rec.OWNER_ID is not null)) THEN
1295
1296 /* Roopa - end
1297 Fix for bug 2799545
1298 */
1299
1300 SELECT cs_wf_process_id_s.nextval
1301 INTO l_event_id
1302 FROM dual;
1303 -- Construct the unique event key
1304 l_event_key := p_Incident_Number ||'-'||to_char(l_event_id) || '-EVT';
1305
1306 --RAISE the WF Business event.
1307 wf_event.raise(p_event_name => 'oracle.apps.cs.sr.ServiceRequest.reassigned',
1308 p_event_key => l_event_key,
1309 p_parameters => l_param_list);
1310
1311 /* Roopa - begin
1312 Fix for bug 2798269
1313 */
1314 -- Standard check of p_commit
1315 IF FND_API.To_Boolean(p_commit) THEN
1316 COMMIT WORK;
1317 END IF;
1318 -- commit;
1319
1320 END IF;
1321
1322 /***********************************************
1323 Roopa - begin
1324 Fix for bug 2793681 - ADD_NEW_CONTACT_TO_SR is never passed as an event
1325 from the SR api we need to interpret the update condition and raise this
1326 event accordingly. inserting the following if .
1327 Roopa - end
1328 ***********************************************/
1329
1330 IF (l_new_contact_point_name IS NOT NULL) THEN
1331
1332 SELECT cs_wf_process_id_s.nextval
1333 INTO l_event_id
1334 FROM dual;
1335 -- Construct the unique event key
1336 l_event_key := p_Incident_Number ||'-'||to_char(l_event_id) || '-EVT';
1337
1338 --RAISE the WF Business event.
1339 wf_event.raise(p_event_name => 'oracle.apps.cs.sr.ServiceRequest.newcontactadded',
1340 p_event_key => l_event_key,
1341 p_parameters => l_param_list);
1342
1343 END IF;
1344
1345 -- To raise Associated Party Contact added business event
1346 --
1347 IF (l_new_associated_party_name IS NOT NULL) THEN
1348
1349 SELECT cs_wf_process_id_s.nextval
1350 INTO l_event_id
1351 FROM dual;
1352 -- Construct the unique event key
1353 l_event_key := p_Incident_Number ||'-'||to_char(l_event_id) || '-EVT';
1354
1355 --RAISE the WF Business event.
1356 wf_event.raise(p_event_name => 'oracle.apps.cs.sr.ServiceRequest.associatedpartyadded',
1357 p_event_key => l_event_key,
1358 p_parameters => l_param_list);
1359
1360 END IF;
1361
1362
1363 l_business_event := 'oracle.apps.cs.sr.ServiceRequest.updated';
1364
1365
1366 ELSIF (p_Event_Code = 'RELATIONSHIP_CREATE_FOR_SR' ) THEN
1367
1368 IF(p_Link_Rec.LINK_TYPE_ID IS NOT NULL) THEN
1369 /* Roopa - Fix for bug 3528510 */
1370 /* The following 2 parameters are added to the payload so that
1371 the business event wf will catch the non-SR -> SR link scenario
1372 */
1373 wf_event.AddParameterToList(p_name => 'LINK_SUBJECT_TYPE',
1374 p_value => p_link_rec.subject_type,
1375 p_parameterlist => l_param_list);
1376 wf_event.AddParameterToList(p_name => 'LINK_OBJECT_TYPE',
1377 p_value => p_link_rec.object_type,
1378 p_parameterlist => l_param_list);
1379 l_business_event := 'oracle.apps.cs.sr.ServiceRequest.relationshipcreated';
1380 ELSE
1381 RAISE l_INVALID_EVENT_ARGS;
1382 END IF;
1383
1384 ELSIF (p_Event_Code = 'RELATIONSHIP_DELETE_FOR_SR') THEN
1385
1386 IF (p_Link_Rec.LINK_TYPE_ID IS NOT NULL) THEN
1387 /* Roopa - Fix for bug 3528510 */
1388 /* The following 2 parameters are added to the payload so that
1389 the business event wf will catch the non-SR -> SR link scenario
1390 */
1391 wf_event.AddParameterToList(p_name => 'LINK_SUBJECT_TYPE',
1392 p_value => p_link_rec.subject_type,
1393 p_parameterlist => l_param_list);
1394 wf_event.AddParameterToList(p_name => 'LINK_OBJECT_TYPE',
1395 p_value => p_link_rec.object_type,
1396 p_parameterlist => l_param_list);
1397 l_business_event := 'oracle.apps.cs.sr.ServiceRequest.relationshipdeleted';
1398 ELSE
1399 RAISE l_INVALID_EVENT_ARGS;
1400 END IF;
1401
1402 /* Roopa - begin
1403 Fix for bug 2793681 - ADD_NEW_CONTACT_TO_SR is never passed as an event from the SR api
1404 we need to interpret the update condition and raise this
1405 event accordingly. Commenting out the following elsif
1406 Roopa - end*/
1407
1408 -- ELSIF (p_Event_Code = 'ADD_NEW_CONTACT_TO_SR') THEN
1409 -- IF (l_new_contact_point_name IS NOT NULL) THEN
1410 -- l_business_event := 'oracle.apps.cs.sr.ServiceRequest.newcontactadded';
1411 -- ELSE
1412 -- RAISE l_INVALID_EVENT_ARGS;
1413 -- END IF;
1414
1415 ELSIF (p_Event_Code = 'CREATE_SERVICE_REQUEST') THEN
1416
1417 l_business_event := 'oracle.apps.cs.sr.ServiceRequest.created';
1418
1419 ELSE
1420
1421 RAISE l_INVALID_EVENT_CODE;
1422
1423 END IF;
1424
1425 SELECT cs_wf_process_id_s.nextval
1426 INTO l_event_id
1427 FROM dual;
1428
1429 -- Construct the unique event key
1430 l_event_key := p_Incident_Number ||'-'||to_char(l_event_id) || '-EVT';
1431
1432
1433 --dbms_output.put_line('Raising the Workflow business : ' || l_business_event);
1434
1435 --INSERT INTO rm_tmp values (p_Incident_Number, 'Raising WF from event code ',rm_tmp_seq.nextval);
1436 --INSERT INTO rm_tmp values (p_Incident_Number, 'l_business_event='||l_business_event||' ,l_event_key= '|| l_event_key ,rm_tmp_seq.nextval);
1437
1438
1439 wf_event.raise(p_event_name => l_business_event,
1440 p_event_key => l_event_key,
1441 p_parameters => l_param_list);
1442
1443 l_param_list.DELETE;
1444
1445
1446 /* Roopa - begin
1447 Fix for bug 2798269
1448 */
1449 -- Standard check of p_commit
1450 IF FND_API.To_Boolean(p_commit) THEN
1451 COMMIT WORK;
1452 END IF;
1453 -- commit;
1454
1455
1456 /***************************************************************
1457 This section is for backward compatibility. This will enable
1458 client custom workflows which are not BES compatible, to be
1459 executed by using the old Workflow APIs to launch the workflow
1460 processes, i.e., CreateProcess() , StartProcess().
1461 All the old seeded workflows will be converted to BES and
1462 will be shipped out with release 11.5.9 .
1463 **************************************************************/
1464
1465
1466 IF (p_Event_Code = 'CREATE_SERVICE_REQUEST' OR p_Event_Code = 'UPDATE_SERVICE_REQUEST') THEN
1467
1468 OPEN l_sel_request_csr;
1469 FETCH l_sel_request_csr INTO l_sel_request_rec;
1470
1471 IF (l_sel_request_csr%FOUND) THEN
1472
1473 -- Check if the seeded workflow or client custom workflow process was
1474 -- launched via WF business event. It the process was launched, then
1475 -- update SR table with the workflow process ID used in the event.
1476 -- If not (workflow process may not be BES converted), try to launch
1477 -- the workflow using the old workflow APIs.
1478
1479
1480
1481 OPEN l_servereq_csr;
1482 FETCH l_servereq_csr
1483 INTO l_end_date,l_itemkey;
1484
1485 IF (l_servereq_csr%FOUND) THEN
1486
1487 /*********
1488 IF (CS_Workflow_PKG.Is_Servereq_Item_Active
1489 (p_request_number => p_Incident_Number,
1490 p_wf_process_id => l_event_id) = 'Y') THEN
1491 ********/
1492
1493 /* Roopa - 01/24/03
1494 It was decided that Update_ServiceRequest API need not be called
1495 just to update the workflow process id of the SR that too with full validation on.
1496 An explicit update should suffice. Hence commenting out the following code and
1497 replacing it with an explicit update statement
1498 */
1499 l_pos := INSTR(l_itemkey, '-',-1,1); -- Bug#4007088
1500 x_wf_process_id := SUBSTR(l_itemkey, l_pos+1);
1501
1502 --INSERT INTO rm_tmp values (p_Incident_Number, 'Update event raised successfuly,updating table.x_wf_process_id= '||x_wf_process_id||' l_itemkey='||l_itemkey,rm_tmp_seq.nextval);
1503
1504 --UPDATE CS_INCIDENTS_ALL_B set WORKFLOW_PROCESS_ID = l_event_id
1505 UPDATE CS_INCIDENTS_ALL_B set WORKFLOW_PROCESS_ID = x_wf_process_id
1506 WHERE INCIDENT_ID = l_sel_request_rec.incident_id;
1507
1508 /* Roopa - begin
1509 Fix for bug 2798269
1510 */
1511 -- Standard check of p_commit
1512 IF FND_API.To_Boolean(p_commit) THEN
1513 COMMIT WORK;
1514 END IF;
1515 -- commit;
1516 --x_wf_process_id := l_event_id;
1517
1518 /**********
1519 Seeded or custom client Workflow was NOT launched via business event.
1520 Therefore, try to launch using old WF api call.
1521 ***********/
1522 ELSIF (l_sel_request_rec.workflow IS NOT NULL) THEN
1523
1524 --INSERT INTO rm_tmp values (p_Incident_Number, 'Update event NOT raised successfuly, try launching old way. ',rm_tmp_seq.nextval);
1525
1526 IF (l_sel_request_rec.AUTOLAUNCH_WORKFLOW_FLAG = 'Y') AND
1527 (( l_sel_request_rec.resource_type = 'RS_EMPLOYEE' AND
1528 -- Also need to check the owner_id being passed by CIC in order to launch
1529 -- their workflow.
1530 l_sel_request_rec.incident_owner_id IS NOT NULL) OR
1531 l_sel_request_rec.workflow = 'CSEADUP') THEN --changed for bug 7484364
1532 -- l_sel_request_rec.workflow <> 'CSEADUP') THEN --Added this for bug 6974942
1533
1534 IF(p_Event_Code='CREATE_SERVICE_REQUEST') THEN
1535
1536 l_raise_old_wf_flag := 'Y';
1537
1538 -- Need this extra check for Update event.
1539
1540 ELSIF(CS_Workflow_PKG.Is_Servereq_Item_Active
1541 (p_request_number => p_Incident_Number,
1542 p_wf_process_id => l_sel_request_rec.workflow_process_id ) = 'N'
1543 AND l_sel_request_rec.close_flag <> 'Y') THEN
1544
1545 l_raise_old_wf_flag := 'Y';
1546
1547 END IF;
1548
1549 -- Workflow launched manually via UI's Tools Menu.
1550 ELSIF (
1551 -- AUTOLAUNCH_WORKFLOW_FLAG should no be checked here since you can
1552 -- manually launch a workflow with AUTOLAUNCH ON , i.e., when a workflow
1553 -- is aborted and restarted in the UI Tools menu.
1554 --l_sel_request_rec.AUTOLAUNCH_WORKFLOW_FLAG <> 'Y' AND
1555 l_sel_request_rec.resource_type = 'RS_EMPLOYEE' AND
1556 l_sel_request_rec.incident_owner_id IS NOT NULL
1557 -- rmanabat 01/06/03
1558 AND p_wf_manual_launch = 'Y' AND
1559 CS_Workflow_PKG.Is_Servereq_Item_Active
1560 (p_request_number => p_Incident_Number,
1561 p_wf_process_id => l_sel_request_rec.workflow_process_id ) = 'N'
1562 AND l_sel_request_rec.close_flag <> 'Y') THEN
1563
1564 l_raise_old_wf_flag := 'Y';
1565
1566 END IF;
1567
1568 IF (l_raise_old_wf_flag = 'Y') THEN
1569
1570 -- We will have to launch the workflow using the old wf API
1571 -- calls, i.e. , CreateProcess(), StartProcess() via Start_Servereq_Workflow().
1572
1573 --INSERT INTO rm_tmp values (p_Incident_Number, 'launching WF, calling old way. ',rm_tmp_seq.nextval);
1574
1575 /****************
1576 CS_Workflow_PKG.Start_Servereq_Workflow(
1577 p_request_number => p_Incident_Number,
1578 p_wf_process_name => l_sel_request_rec.workflow,
1579 p_initiator_user_id => p_USER_ID,
1580 p_initiator_resp_id => p_RESP_ID,
1581 p_initiator_resp_appl_id=> p_RESP_APPL_ID,
1582 --
1583 -- This flag should be set to 'N' when called from the
1584 -- Update/Create SR api, or any other API. This is only
1585 -- set to 'Y' when called from the tools menu of the SR UI.
1586 --
1587 p_wf_manual_launch => 'N',
1588 p_workflow_process_id => out_wf_process_id,
1589 x_msg_count => l_msg_count,
1590 x_msg_data => l_msg_data);
1591 ***************/
1592
1593 --dbms_output.put_line('Calling Launch_Servereq_Workflow ');
1594
1595 CS_Workflow_PUB.Launch_Servereq_Workflow(
1596 p_api_version => 1.0,
1597 p_init_msg_list => FND_API.G_TRUE,
1598 p_commit => p_commit,
1599 p_return_status => l_return_status,
1600 p_msg_count => l_msg_count,
1601 p_msg_data => l_msg_data,
1602 p_request_number => p_Incident_Number,
1603 p_initiator_user_id => p_USER_ID,
1604 p_initiator_resp_id => p_RESP_ID,
1605 p_initiator_resp_appl_id => p_RESP_APPL_ID,
1606 p_itemkey => l_itemkey,
1607 p_nowait => FND_API.G_TRUE);
1608
1609 --dbms_output.put_line('Calling Launch_Servereq_Workflow,return status= '|| l_return_status);
1610
1611 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1612 raise FND_API.G_EXC_ERROR;
1613 END IF;
1614
1615 l_pos := INSTR(l_itemkey, '-',-1,1); -- Bug#4007088
1616 x_wf_process_id := SUBSTR(l_itemkey, l_pos+1);
1617
1618 --x_wf_process_id := out_wf_process_id;
1619 x_msg_count := l_msg_count;
1620 x_msg_data := l_msg_data;
1621
1622 -- Show any messages returned FROM the Workflow API.
1623 -- Either put this here or on the calliing SR API.
1624
1625 END IF; -- IF (l_raise_old_wf_flag = 'Y')
1626
1627 END IF; -- IF (CS_Workflow_PKG.Is_Servereq_Item_Active ...
1628
1629 CLOSE l_servereq_csr;
1630
1631
1632 END IF; -- IF (l_sel_request_csr%FOUND)
1633
1634 CLOSE l_sel_request_csr;
1635
1636
1637 END IF; -- IF (p_Event_Code='CREATE_SERVICE_REQUEST' OR 'UPDATE_SERVICE_REQUEST')
1638
1639 l_param_list.DELETE;
1640
1641 END IF; -- IF (Event_Code IS NULL OR Incident_Number IS NULL ...
1642
1643
1644
1645 EXCEPTION
1646
1647 WHEN l_INVALID_EVENT_ARGS THEN
1648 --dbms_output.put_line('Exception : WHEN l_INVALID_EVENT_ARGS ');
1649 --l_param_list.DELETE;
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1652 FND_MSG_PUB.Add_Exc_Msg( 'CS_WF_EVENT_PKG',
1653 'Raise_ServiceRequest_Event' );
1654 END IF;
1655 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1656 p_data => x_msg_data,
1657 p_encoded => FND_API.G_FALSE );
1658
1659 WHEN l_INVALID_EVENT_CODE THEN
1660 --dbms_output.put_line('Exception : WHEN l_INVALID_EVENT_CODE');
1661 --l_param_list.DELETE;
1662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1663 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1664 FND_MSG_PUB.Add_Exc_Msg( 'CS_WF_EVENT_PKG',
1665 'Raise_ServiceRequest_Event' );
1666 END IF;
1667 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1668 p_data => x_msg_data,
1669 p_encoded => FND_API.G_FALSE );
1670
1671 WHEN FND_API.G_EXC_ERROR THEN
1672 --dbms_output.put_line('Exception : WHEN FND_API.G_EXC_ERROR');
1673 --l_param_list.DELETE;
1674 IF (l_sel_request_csr%ISOPEN) THEN
1675 CLOSE l_sel_request_csr;
1676 END IF;
1677 x_return_status := FND_API.G_RET_STS_ERROR;
1678 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1679 p_data => x_msg_data,
1680 p_encoded => FND_API.G_FALSE );
1681
1682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683 --dbms_output.put_line('Exception : WHEN FND_API.G_EXC_UNEXPECTED_ERROR');
1684 --l_param_list.DELETE;
1685 IF (l_sel_request_csr%ISOPEN) THEN
1686 CLOSE l_sel_request_csr;
1687 END IF;
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1689 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1690 p_data => x_msg_data,
1691 p_encoded => FND_API.G_FALSE );
1692
1693 WHEN OTHERS THEN
1694 --dbms_output.put_line('Exception : WHEN OTHERS');
1695 --l_param_list.DELETE;
1696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1697 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1698 FND_MSG_PUB.Add_Exc_Msg( 'CS_WF_EVENT_PKG',
1699 'Raise_ServiceRequest_Event' );
1700 END IF;
1701 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1702 p_data => x_msg_data,
1703 p_encoded => FND_API.G_FALSE );
1704
1705
1706
1707
1708 END Raise_ServiceRequest_Event;
1709
1710
1711 ------------------------------------------------------------------------------
1712 -- Procedure : Get_Fnd_User_Role
1713 ------------------------------------------------------------------------------
1714
1715 PROCEDURE Get_Fnd_User_Role
1716 ( p_fnd_user_id IN NUMBER,
1717 x_role_name OUT NOCOPY VARCHAR2,
1718 x_role_display_name OUT NOCOPY VARCHAR2 )
1719 IS
1720 l_employee_id NUMBER;
1721 BEGIN
1722 -- map the FND user to employee ID
1723 SELECT employee_id INTO l_employee_id
1724 FROM fnd_user
1725 WHERE user_id = p_fnd_user_id;
1726
1727 IF (l_employee_id IS NOT NULL) THEN
1728 wf_directory.getrolename
1729 ( p_orig_system => 'PER',
1730 p_orig_system_id => l_employee_id,
1731 p_name => x_role_name,
1732 p_display_name => x_role_display_name );
1733 ELSE
1734 wf_directory.getrolename
1735 ( p_orig_system => 'FND_USR',
1736 p_orig_system_id => p_fnd_user_id,
1737 p_name => x_role_name,
1738 p_display_name => x_role_display_name );
1739 END IF;
1740 END Get_Fnd_User_Role;
1741
1742
1743
1744
1745 END CS_WF_EVENT_PKG;
[Home] [Help]