[Home] [Help]
PACKAGE BODY: APPS.CSZ_INTERACTION_PVT
Source
1 PACKAGE BODY CSZ_INTERACTION_PVT
2 /* $Header: cszvintb.pls 120.0 2005/06/01 11:18:19 appldev noship $ */
3 AS
4 -- event types used in end_interaction
5 APPLY CONSTANT VARCHAR2(10) := 'Apply';
6 STOP CONSTANT VARCHAR2(10) := 'Stop';
7 CANCEL CONSTANT VARCHAR2(10) := 'Cancel';
8
9 -- Values seeded in jtf_ih_actions table for Service
10 SR_UPDATE CONSTANT VARCHAR(30) := 'Cs Sr Upd';
11 SR_CANCEL CONSTANT VARCHAR(30) := 'Cancel SR';
12 SR_MEDIA_TYPE CONSTANT VARCHAR(30) := 'TELEPHONE';
13 SR_MODULE CONSTANT VARCHAR(30) := 'Service Request';
14
15 /*------------------------------------------------------*/
16 /* procedure name: begin_interaction */
17 /* description : Creates a new interaction interaction */
18 /* record */
19 /* logic : Open MediaItem, then create interaction */
20 /* and add activity. */
21 /*------------------------------------------------------*/
22
23 PROCEDURE begin_interaction ( p_incident_id IN NUMBER,
24 p_cust_party_id IN NUMBER,
25 p_resp_appl_id IN NUMBER,
26 p_resp_id IN NUMBER,
27 p_user_id IN NUMBER,
28 p_login_id IN NUMBER,
29 p_direction IN VARCHAR2,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33 x_interaction_id OUT NOCOPY NUMBER,
34 x_creation_time OUT NOCOPY DATE )
35 AS
36 -- Media Item handling variables
37 l_media_rec JTF_IH_PUB.media_rec_type;
38 l_media_id NUMBER;
39
40 -- Interaction Handling Variables
41 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
42 l_interaction_id NUMBER;
43 l_wrap_id NUMBER;
44 l_outcome_id NUMBER;
45 l_outcome_short_desc VARCHAR2(100);
46 l_result_id NUMBER;
47 l_result_required VARCHAR2(100);
48 l_result_short_desc VARCHAR2(100);
49 l_reason_required VARCHAR2(100);
50 l_reason_id VARCHAR2(100);
51 l_reason_short_desc VARCHAR2(100);
52 l_action_value VARCHAR2(30);
53 l_action_id NUMBER;
54
55 -- Activity Handling Variables
56 l_activity_rec JTF_IH_PUB.activity_rec_type;
57 l_activity_id NUMBER;
58
59 -- local variables
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_init_msg_list VARCHAR2(10) := Fnd_Api.G_TRUE;
62 l_commit VARCHAR2(10) := Fnd_Api.G_FALSE;
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000);
65 l_return_status VARCHAR2(4);
66 begin
67 -- establish save point
68 SAVEPOINT begin_interaction_sp;
69
70 -- standard call to check for call compatibility.
71 IF Fnd_Api.to_boolean(l_init_msg_list)
72 THEN
73 Fnd_Msg_Pub.initialize;
74 END IF;
75
76 -- Initialize API return status to success
77 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
78
79 --open new media item and return media id.
80 begin
81 -- setup Media record.
82 l_media_rec.media_item_type := SR_MEDIA_TYPE;
83 --verify direction flag, set in media rec
84 if (p_direction <> 'INBOUND' AND p_direction <> 'OUTBOUND') then
85 x_return_status := Fnd_Api.G_RET_STS_ERROR;
86 Fnd_Message.set_name('CS','Error:Invalid Direction specified for Interaction.');
87 Fnd_Msg_Pub.ADD;
88 RAISE Fnd_Api.G_EXC_ERROR;
89 else
90 l_media_rec.direction := p_direction;
91 end if;
92
93 --verify p_resp_appl_id, p_resp_id, p_user_id, p_login_id are valid
94 if (p_resp_appl_id = null OR p_resp_appl_id = Fnd_Api.G_MISS_NUM) then
95 Fnd_Message.SET_NAME('CS','Invalid Application id found');
96 Fnd_Msg_Pub.ADD;
97 end if;
98
99 if (p_resp_id = null OR p_resp_id = Fnd_Api.G_MISS_NUM) then
100 Fnd_Message.SET_NAME('CS','Invalid Responsibility id found');
101 Fnd_Msg_Pub.ADD;
102 end if;
103
104 if (p_user_id = null OR p_user_id = Fnd_Api.G_MISS_NUM) then
105 Fnd_Message.SET_NAME('CS','Invalid User Id');
106 Fnd_Msg_Pub.ADD;
107 RAISE Fnd_Api.G_EXC_ERROR;
108 end if;
109
110 if (p_login_id = null OR p_login_id = Fnd_Api.G_MISS_NUM) then
111 Fnd_Message.SET_NAME('CS','Invalid User Login Id');
112 Fnd_Msg_Pub.ADD;
113 end if;
114
115 begin
116 -- open media item
117 JTF_IH_PUB.open_mediaitem ( l_api_version,
118 l_init_msg_list,
119 l_commit, -- commit flag
120 p_resp_appl_id,
121 p_resp_id,
122 p_user_id,
123 p_login_id,
124 l_return_status,
125 l_msg_count,
126 l_msg_data,
127 l_media_rec,
128 l_media_id ); -- returns media id
129 exception
130 when others then
131 -- throw error
132 Fnd_Message.SET_NAME('CS','Error while trying to Open MediaItem');
133 Fnd_Msg_Pub.ADD;
134 rollback to begin_interaction_sp;
135 x_return_status := Fnd_Api.G_RET_STS_ERROR;
136 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
137 p_count => l_msg_count,
138 p_data => l_msg_data);
139 end;
140 exception
141 when others then
142 rollback to begin_interaction_sp;
143 x_return_status := Fnd_Api.G_RET_STS_ERROR;
144 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
145 p_count => l_msg_count,
146 p_data => l_msg_data);
147 end;
148
149 -- Open interaction and return interaction id if successful
150 begin
151 -- setup Interaction record
152 l_interaction_rec.handler_id := p_resp_appl_id; --(application ID here!!!)
153 l_interaction_rec.party_id := p_cust_party_id; -- Customer Party ID
154
155 -- Agent resource id
156 begin
157 SELECT resource_id INTO l_interaction_rec.resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id;
158 exception
159 when NO_DATA_FOUND then
160 x_return_status := Fnd_Api.G_RET_STS_ERROR;
161 Fnd_Message.SET_NAME('CS','No resource Id found for User:' || p_user_id);
162 Fnd_Msg_Pub.ADD;
163 raise NO_DATA_FOUND;
164 end;
165 -- Profile Values from wrap up
166 begin
167 select FND_PROFILE.Value('CSC_CC_WRAPUP_INTERACTION_DEFAULTS') into l_wrap_id from dual;
168 exception
169 when NO_DATA_FOUND then
170 x_return_status := Fnd_Api.G_RET_STS_ERROR;
171 Fnd_Message.SET_NAME('CS','Profile "CSC_CC_WRAPUP_INTERACTION_DEFAULTS" could not be found');
172 Fnd_Msg_Pub.ADD;
173 raise NO_DATA_FOUND;
174 end;
175
176 -- Retreive default values for populating the result, reason, outcome fields in JTF_IH_ACTIVITIES table
177 begin
178 select outcome_id, result_id, reason_id into l_outcome_id, l_result_id, l_reason_id from jtf_ih_wrap_ups_vl where wrap_id = l_wrap_id;
179 exception
180 when NO_DATA_FOUND then
181 x_return_status := Fnd_Api.G_RET_STS_ERROR;
182 Fnd_Message.SET_NAME('CS','Outcome, result, reason codes could not be found');
183 Fnd_Msg_Pub.ADD;
184 raise NO_DATA_FOUND;
185 end;
186 l_action_value := SR_UPDATE;
187 -- Retreives the valid actionID for SR update actions.
188 begin
189 SELECT nvl(action_id,0) into l_action_id
190 FROM jtf_ih_actions_tl
191 WHERE action = l_action_value
192 AND rownum < 2;
193 exception
194 when NO_DATA_FOUND then
195 x_return_status := Fnd_Api.G_RET_STS_ERROR;
196 Fnd_Message.SET_NAME('CS','Action Id could not be found for SR Update action');
197 Fnd_Msg_Pub.ADD;
198 raise NO_DATA_FOUND;
199 end;
200
201 l_interaction_rec.outcome_id := l_outcome_id;
202 l_interaction_rec.reason_id := l_reason_id;
203 l_interaction_rec.result_id := l_result_id;
204
205 begin
206 JTF_IH_PUB.open_Interaction( l_api_version,
207 l_init_msg_list,
208 l_commit,
209 p_resp_appl_id,
210 p_resp_id,
211 p_user_id,
212 p_login_id,
213 l_return_status,
214 l_msg_count,
215 l_msg_data,
216 l_interaction_rec,
217 l_interaction_id );
218 exception
219 when others then
220 rollback to begin_interaction_sp;
221 x_return_status := Fnd_Api.G_RET_STS_ERROR;
222 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
223 p_count => l_msg_count,
224 p_data => l_msg_data);
225 end;
226 exception
227 when others then
228 rollback to begin_interaction_sp;
229 x_return_status := Fnd_Api.G_RET_STS_ERROR;
230 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
231 p_count => l_msg_count,
232 p_data => l_msg_data);
233 end;
234 -- Add activity for this interaction and return activity id if successful
235 begin
236 -- Add activity :
237 l_activity_rec.action_id := l_action_id; -- Created
238 l_activity_rec.interaction_id := l_interaction_id; -- ID of the interaction for this activity
239 l_activity_rec.doc_id := p_incident_id; -- ID of the interaction for this activity
240 l_activity_rec.media_id := l_media_id; -- ID of Phone call, e-mail (Media) - returned from the open_mediaitem call
241 l_activity_rec.outcome_id := l_outcome_id;
242 l_activity_rec.reason_id := l_reason_id;
243 l_activity_rec.result_id := l_result_id;
244 begin
245 select action_item_id into l_activity_rec.action_item_id from jtf_ih_action_items_tl where short_description = SR_MODULE;
246 exception
247 when NO_DATA_FOUND then
248 x_return_status := Fnd_Api.G_RET_STS_ERROR;
249 Fnd_Message.SET_NAME('CS','ActionItemId not found for "Service Request" module');
250 Fnd_Msg_Pub.ADD;
251 raise NO_DATA_FOUND;
252 end;
253 begin
254 JTF_IH_PUB.add_activity( l_api_version,
255 l_init_msg_list,
256 l_commit,
257 p_resp_appl_id,
258 p_resp_id,
259 p_user_id,
260 p_login_id,
261 l_return_status,
262 l_msg_count,
263 l_msg_data,
264 l_activity_rec,
265 l_activity_id );
266 exception
267 when others then
268 x_return_status := Fnd_Api.G_RET_STS_ERROR;
269 Fnd_Message.SET_NAME('CS','Error while creating Activity record');
270 Fnd_Msg_Pub.ADD;
271 raise Fnd_Api.G_EXC_ERROR;
272 end;
273 exception
274 when others then
275 rollback to begin_interaction_sp;
276 x_return_status := Fnd_Api.G_RET_STS_ERROR;
277 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
278 p_count => l_msg_count,
279 p_data => l_msg_data);
280 end;
281 -- set up return values when successful.
282 x_interaction_id := l_interaction_id;
283 x_creation_time := sysdate;
284
285 exception
286 when others then
287 rollback to begin_interaction_sp;
288 x_return_status := Fnd_Api.G_RET_STS_ERROR;
289 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
290 p_count => l_msg_count,
291 p_data => l_msg_data);
292 end;
293
294 /*------------------------------------------------------*/
295 /* procedure name: end_interaction */
296 /* description : Ends given interaction record */
297 /* logic : Update activity to closed, then update*/
298 /* interaction and close media item. */
299 /*------------------------------------------------------*/
300
301 PROCEDURE end_interaction
302 ( p_interaction_id IN NUMBER,
303 p_event IN VARCHAR2,
304 p_cust_party_id IN NUMBER,
305 p_resp_appl_id IN NUMBER,
306 p_resp_id IN NUMBER,
307 p_user_id IN NUMBER,
308 p_login_Id IN NUMBER,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2) as
312
313 l_media_rec JTF_IH_PUB.media_rec_type;
314 l_activity_rec JTF_IH_PUB.activity_rec_type;
315 l_activity_id NUMBER;
316 l_media_id NUMBER;
317
318 -- Interaction Handling Variables
319 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
320 l_interaction_id NUMBER;
321 l_wrap_id NUMBER;
322 l_outcome_id NUMBER;
323 l_outcome_short_desc VARCHAR2(100);
324 l_result_id NUMBER;
325 l_result_required VARCHAR2(100);
326 l_result_short_desc VARCHAR2(100);
327 l_reason_required VARCHAR2(100);
328 l_reason_id VARCHAR2(100);
329 l_reason_short_desc VARCHAR2(100);
330 l_action_value VARCHAR2(30);
331 l_action_id NUMBER;
332
333 -- local variables
334 l_api_version CONSTANT NUMBER := 1.0;
335 l_init_msg_list VARCHAR2(10) := Fnd_Api.G_TRUE;
336 l_commit VARCHAR2(10) := Fnd_Api.G_FALSE;
337 l_msg_count NUMBER;
338 l_msg_data VARCHAR2(2000);
339 l_return_status VARCHAR2(4);
340
341 begin
342 -- establish save point
343 SAVEPOINT end_interaction_sp;
344
345 -- standard call to check for call compatibility.
346 IF Fnd_Api.to_boolean(l_init_msg_list)
347 THEN
348 Fnd_Msg_Pub.initialize;
349 END IF;
350
351 -- Initialize API return status to success
352 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
353
354 -- retrieve media id for given interaction
355 begin
356 select media_id into l_media_id from jtf_ih_activities_vl
357 where interaction_id = p_interaction_id;
358 exception
359 when NO_DATA_FOUND then
360 x_return_status := Fnd_Api.G_RET_STS_ERROR;
361 -- dbms_output.put_line ('No Media Id found for corresponding Interaction Id:' || p_interaction_id);
362 Fnd_Message.SET_NAME('CS','No Media Id found for corresponding Interaction Id:' || p_interaction_id);
363 Fnd_Msg_Pub.ADD;
364 RAISE NO_DATA_FOUND;
365 when others then
366 -- dbms_output.put_line ('EndInteraction :: Error while retrieving media Id for Interaction Id:' || p_interaction_id);
367 x_return_status := Fnd_Api.G_RET_STS_ERROR;
368 Fnd_Message.SET_NAME('CS','EndInteraction :: Error while retrieving media Id for Interaction Id:' || p_interaction_id);
369 Fnd_Msg_Pub.ADD;
370 RAISE Fnd_Api.G_EXC_ERROR;
371 end;
372
373 if (p_event = APPLY OR p_event = STOP) then
374 l_action_value := SR_UPDATE;
375 else
376 l_action_value := SR_CANCEL;
377 end if;
378 -- dbms_output.put_line ('Action=' || l_action_value);
379 -- Retreives the valid actionID for \223SR update\224 actions.
380 begin
381 SELECT nvl(action_id,0) into l_action_id
382 FROM jtf_ih_actions_tl
383 WHERE action = l_action_value
384 AND rownum < 2;
385 exception
386 when NO_DATA_FOUND then
387 x_return_status := Fnd_Api.G_RET_STS_ERROR;
388 -- dbms_output.put_line ('No Action Id found for corresponding Action:' || l_action_value);
389 Fnd_Message.SET_NAME('CS','No Action Id found for corresponding Action:' || l_action_value);
390 Fnd_Msg_Pub.ADD;
391 Raise NO_DATA_FOUND;
392 when others then
393 -- dbms_output.put_line ('EndInteraction :: Error while retrieving action Id for Action:' || l_action_value);
394 x_return_status := Fnd_Api.G_RET_STS_ERROR;
395 Fnd_Message.SET_NAME('CS','EndInteraction :: Error while retrieving action Id for Action:' || l_action_value);
396 Fnd_Msg_Pub.ADD;
397 RAISE Fnd_Api.G_EXC_ERROR;
398 end;
399 -- dbms_output.put_line ('ActionID=' || l_action_id || ' Status=' || x_return_status);
400 l_activity_rec.action_id := l_action_id; -- Created
401 begin
402 select action_item_id into l_activity_rec.action_item_id from jtf_ih_action_items_tl where short_description = SR_MODULE;
403 exception
404 when NO_DATA_FOUND then
405 x_return_status := Fnd_Api.G_RET_STS_ERROR;
406 Fnd_Message.SET_NAME('CS','ActionItemId not found for "Service Request" module');
407 Fnd_Msg_Pub.ADD;
408 raise NO_DATA_FOUND;
409 end;
410 l_activity_rec.interaction_id := p_interaction_id;
411 l_activity_rec.end_date_time := sysdate;
412
413 -- ID of the interaction for this activity
414 begin
415 JTF_IH_PUB.update_activity(
416 l_api_version,
417 l_init_msg_list,
418 l_commit,
419 p_resp_appl_id,
420 p_resp_id,
421 p_user_id,
422 p_login_id,
423 l_return_status,
424 l_msg_count,
425 l_msg_data,
426 l_activity_rec,
427 l_activity_id );
428 exception
429 when others then
430 -- dbms_output.put_line ('Error after updateActivity-' || l_msg_data);
431 rollback to end_interaction_sp;
432 x_return_status := Fnd_Api.G_RET_STS_ERROR;
433 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
434 p_count => l_msg_count,
435 p_data => l_msg_data);
436 end;
437
438 -- dbms_output.put_line ('Updated Activity=' || l_activity_id || ' Status=' || x_return_status);
439 l_interaction_rec.handler_id := p_resp_appl_id; --(application ID here!!!)
440 l_interaction_rec.party_id := p_cust_party_id; -- Customer Party ID
441 l_interaction_rec.end_date_time := sysdate;
442 l_interaction_rec.interaction_id := p_interaction_id;
443 -- Agent resource id
444 begin
445 SELECT resource_id INTO l_interaction_rec.resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id;
446 exception
447 when NO_DATA_FOUND then
448 rollback to end_interaction_sp;
449 x_return_status := Fnd_Api.G_RET_STS_ERROR;
450 Fnd_Message.SET_NAME('CS','No resource Id found for User:' || p_user_id);
451 Fnd_Msg_Pub.ADD;
452 raise NO_DATA_FOUND;
453 end;
454 -- dbms_output.put_line ('Got Resource Id=' || l_interaction_rec.resource_id|| ' Status=' || x_return_status);
455
456 -- Profile Values from wrap up
457 begin
458 select FND_PROFILE.Value('CSC_CC_WRAPUP_INTERACTION_DEFAULTS') into l_wrap_id from dual;
459 exception
460 when NO_DATA_FOUND then
461 rollback to end_interaction_sp;
462 x_return_status := Fnd_Api.G_RET_STS_ERROR;
463 Fnd_Message.SET_NAME('CS','Profile "CSC_CC_WRAPUP_INTERACTION_DEFAULTS" could not be found');
464 Fnd_Msg_Pub.ADD;
465 raise NO_DATA_FOUND;
466 end;
467
468 -- dbms_output.put_line ('Got Profile Id=CSC_CC_WRAPUP_INTERACTION_DEFAULTS'|| ' Status=' || x_return_status);
469 -- Retreive default values for populating the result, reason, outcome fields in JTF_IH_ACTIVITIES table
470 begin
471 select outcome_id, result_id, reason_id into l_outcome_id, l_result_id, l_reason_id from jtf_ih_wrap_ups_vl where wrap_id = l_wrap_id;
472 exception
473 when NO_DATA_FOUND then
474 rollback to end_interaction_sp;
475 x_return_status := Fnd_Api.G_RET_STS_ERROR;
476 Fnd_Message.SET_NAME('CS','Outcome, result, reason codes could not be found');
477 Fnd_Msg_Pub.ADD;
478 raise NO_DATA_FOUND;
479 end;
480 -- dbms_output.put_line ('Got Outcome,reason,result Id=' || l_outcome_id || ' Status=' || x_return_status);
481
482 -- Close the Interaction
483 begin
484 JTF_IH_PUB.close_interaction( l_api_version,
485 l_init_msg_list,
486 l_commit,
487 p_resp_appl_id,
488 p_resp_id,
489 p_user_id,
490 p_login_id,
491 l_return_status,
492 l_msg_count,
493 l_msg_data,
494 l_interaction_rec );
495 exception
496 when others then
497 rollback to end_interaction_sp;
498 x_return_status := Fnd_Api.G_RET_STS_ERROR;
499 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
500 p_count => l_msg_count,
501 p_data => l_msg_data);
502 end;
503 l_media_rec.media_id := l_media_id;
504
505 -- dbms_output.put_line ('Glosed Interaction'|| ' Status=' || x_return_status );
506 -- Close instance of media item
507 begin
508 Jtf_Ih_Pub.close_MediaItem(
509 l_api_version,
510 l_init_msg_list,
511 l_commit,
512 p_resp_appl_id,
513 p_resp_id,
514 p_user_id,
515 p_login_id,
516 l_return_status,
517 l_msg_count,
518 l_msg_data,
519 l_media_rec );
520 exception
521 when others then
522 rollback to end_interaction_sp;
523 x_return_status := Fnd_Api.G_RET_STS_ERROR;
524 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
525 p_count => l_msg_count,
526 p_data => l_msg_data);
527 end;
528 -- dbms_output.put_line ('Closed Media Item'|| ' Status=' || x_return_status );
529 exception
530 when others then
531 rollback to end_interaction_sp;
532 x_return_status := Fnd_Api.G_RET_STS_ERROR;
533 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
534 p_count => l_msg_count,
535 p_data => l_msg_data);
536 end;
537
538
539 END;