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