[Home] [Help]
PACKAGE BODY: APPS.CS_SR_WF_DUP_CHK_PVT
Source
1 PACKAGE BODY CS_SR_WF_DUP_CHK_PVT AS
2 /* $Header: cswfdpcb.pls 120.1.12010000.2 2009/12/23 10:50:30 vpremach ship $ */
3
4 PROCEDURE Check_SR_Channel
5 (
6 itemtype VARCHAR2,
7 itemkey VARCHAR2,
8 actid NUMBER,
9 funmode VARCHAR2,
10 result OUT NOCOPY VARCHAR2
11 )
12 IS
13
14 l_incident_id NUMBER;
15
16 CURSOR l_ServiceRequest_csr IS
17 SELECT sr_creation_channel
18 FROM cs_incidents_all_b
19 WHERE incident_id = l_incident_id;
20
21 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
22
23 BEGIN
24
25 IF (funmode = 'RUN') THEN
26
27 -- Get the service request ID
28 l_incident_id := WF_ENGINE.GetItemAttrNumber(
29 itemtype => itemtype,
30 itemkey => itemkey,
31 aname => 'REQUEST_ID' );
32
33 -- Extract the service request record
34 OPEN l_ServiceRequest_csr;
35 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
36
37 IF l_ServiceRequest_rec.sr_creation_channel = 'WEB' or l_ServiceRequest_rec.sr_creation_channel = 'EMAIL' THEN
38 result := 'Y';
39 ELSE
40 result := 'N';
41 END IF;
42 CLOSE l_ServiceRequest_csr;
43 END IF;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Check_SR_Channel', itemtype, itemkey, actid, funmode);
48 RAISE;
49
50 END Check_SR_Channel;
51
52
53 PROCEDURE Check_SR_Updated
54 (
55 itemtype VARCHAR2,
56 itemkey VARCHAR2,
57 actid NUMBER,
58 funmode VARCHAR2,
59 result OUT NOCOPY VARCHAR2
60 )
61 IS
62 l_incident_id number;
63 l_duplicate_check_profile varchar2(80);
64 l_duplicate_criteria varchar2(80);
65 l_auto_task_profile varchar2(80);
66 l_task_error_note varchar2(80);
67
68 CURSOR l_ServiceRequest_csr IS
69 SELECT creation_date, last_update_date, object_version_number
70 FROM cs_incidents_all_b
71 WHERE incident_id = l_incident_id;
72
73 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
74
75 BEGIN
76 IF (funmode = 'RUN') THEN
77
78 -- Get the service request ID
79 l_incident_id := WF_ENGINE.GetItemAttrNumber(
80 itemtype => itemtype,
81 itemkey => itemkey,
82 aname => 'REQUEST_ID' );
83
84 -- Extract the service request record
85 OPEN l_ServiceRequest_csr;
86 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
87
88 IF l_ServiceRequest_rec.object_version_number = 1 THEN
89 result := 'N';
90
91 l_duplicate_check_profile := FND_PROFILE.Value('CS_SR_DUP_CHK_SR');
92 l_duplicate_criteria := FND_PROFILE.Value('CS_SR_DUP_CHK_CRITERIA');
93 l_auto_task_profile := FND_PROFILE.value('CS_SR_AUTO_TASK_CREATE');
94 l_task_error_note := FND_PROFILE.value('CS_SR_TASK_ERROR_NOTE_TYPE');
95
96 WF_ENGINE.SetItemAttrText(
97 itemtype => 'SERVEREQ',
98 itemkey => itemkey,
99 aname => 'CS_DUPLICATE_CHECK_PROFILE',
100 avalue => l_duplicate_check_profile );
101
102 WF_ENGINE.SetItemAttrText(
103 itemtype => 'SERVEREQ',
104 itemkey => itemkey,
105 aname => 'CS_DUPLICATE_CRITERIA',
106 avalue => l_duplicate_criteria);
107
108 WF_ENGINE.SetItemAttrText(
109 itemtype => 'SERVEREQ',
110 itemkey => itemkey,
111 aname => 'CS_AUTO_TASK_PROFILE',
112 avalue => l_auto_task_profile );
113
114 WF_ENGINE.SetItemAttrText(
115 itemtype => 'SERVEREQ',
116 itemkey => itemkey,
117 aname => 'CS_TASK_FAILURE_NOTE_PROFILE',
118 avalue => l_task_error_note );
119
120 ELSE
121 result := 'Y';
122 END IF;
123
124 END IF;
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Check_last_updated', itemtype, itemkey, actid, funmode);
129 RAISE;
130
131 END Check_SR_Updated;
132
133
134 PROCEDURE Check_Duplicate_Profile
135 (
136 itemtype VARCHAR2,
137 itemkey VARCHAR2,
138 actid NUMBER,
139 funmode VARCHAR2,
140 result OUT NOCOPY VARCHAR2
141 )
142
143 IS
144 l_incident_id NUMBER;
145 l_duplicate_check_profile varchar2(80);
146
147 CURSOR l_ServiceRequest_csr IS
148 SELECT creation_date, last_update_date
149 FROM cs_incidents_all_b
150 WHERE incident_id = l_incident_id;
151
152 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
153
154 BEGIN
155 IF (funmode = 'RUN') THEN
156
157 l_duplicate_check_profile := WF_ENGINE.GetItemAttrText(
158 itemtype => itemtype,
159 itemkey => itemkey,
160 aname => 'CS_DUPLICATE_CHECK_PROFILE' );
161
162 IF l_duplicate_check_profile = 'Y' THEN
163 result := 'Y';
164 ELSE
165 result := 'N';
166 END IF;
167
168 END IF;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Check_last_updated', itemtype, itemkey, actid, funmode);
173 RAISE;
174
175 END Check_Duplicate_Profile;
176
177
178 PROCEDURE Check_And_Perf_Dup_Check
179 (
180 itemtype VARCHAR2,
181 itemkey VARCHAR2,
182 actid NUMBER,
183 funmode VARCHAR2,
184 result OUT NOCOPY VARCHAR2
185 )
186 IS
187
188 l_incident_id NUMBER;
189 l_ea_dup_chk VARCHAR2(1);
190 l_api_version NUMBER := 1.0;
191 l_init_msg_list VARCHAR2(1) ;
192 l_commit VARCHAR2(1) ;
193 l_validation_level NUMBER;
194 l_incident_type_id NUMBER;
195 l_customer_product_id NUMBER;
196 l_current_serial_number varchar2(30);
197 l_inv_item_serial_number varchar2(30);
198 l_instance_serial_number varchar2(30);
199 l_customer_id NUMBER;
200 l_inventory_item_id NUMBER;
201 l_cs_extended_attr CS_SR_DUP_CHK_PVT.cs_extended_attr_tbl;
202 l_incident_address CS_SR_DUP_CHK_PVT.cs_incident_address_rec;
203 l_duplicate_flag VARCHAR2(1);
204 l_sr_dupl_rec CS_SR_DUP_CHK_PVT.Sr_Dupl_Tbl;
205 l_return_status VARCHAR2(1);
206 l_msg_count NUMBER;
207 l_msg_data VARCHAR2(2000);
208 l_dup_found_at VARCHAR2(10);
209 l_counter NUMBER;
210 l_dup_criteria_profile VARCHAR2(30);
211
212
213 CURSOR l_ServiceRequest_csr IS
214 SELECT incident_id, incident_number, customer_id, inventory_item_id, customer_product_id,
215 current_serial_number, item_serial_number,
216 incident_type_id, incident_location_id,
217 incident_Address, incident_city, incident_state, incident_country, incident_postal_code
218 FROM cs_incidents_all_b
219 WHERE incident_id = l_incident_id;
220
221 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
222
223 CURSOR l_EA_Dup_Chk_csr IS
224 SELECT conf.sr_dup_check_flag
225 FROM cug_sr_type_dup_chk_info conf, cs_incidents_all_b inc
226 WHERE conf.incident_type_id = inc.incident_type_id
227 AND inc.incident_id = l_incident_id;
228
229 CURSOR l_EA_Attr_csr IS
230 SELECT incident_id, sr_attribute_code, sr_attribute_value
231 FROM cug_incidnt_attr_vals_vl
232 WHERE incident_id = l_incident_id;
233
234 l_EA_Attr_rec l_EA_Attr_csr%ROWTYPE;
235
236 BEGIN
237 IF (funmode = 'RUN') THEN
238
239 l_duplicate_flag := 'N' ;
240
241 -- Get the service request ID
242 l_incident_id := WF_ENGINE.GetItemAttrNumber(
243 itemtype => itemtype,
244 itemkey => itemkey,
245 aname => 'REQUEST_ID' );
246
247 -- Extract the service request record
248 OPEN l_ServiceRequest_csr;
249 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
250 l_incident_type_id := l_ServiceRequest_rec.incident_type_id;
251
252 -- l_dup_criteria_profile := FND_PROFILE.Value('CS_SR_DUP_CHK_CRITERIA');
253 l_dup_criteria_profile := WF_ENGINE.GetItemAttrText(
254 itemtype => itemtype,
255 itemkey => itemkey,
256 aname => 'CS_DUPLICATE_CRITERIA' );
257
258
259 IF ( l_dup_criteria_profile = 'CS_DUP_CRIT_EA_ADDR' OR
260 l_dup_criteria_profile = 'CS_DUP_CRIT_WITHNO_SERIAL' OR
261 l_dup_criteria_profile = 'CS_DUP_CRIT_WITH_SERIAL') THEN
262
263 OPEN l_EA_Dup_Chk_csr;
264 FETCH l_EA_Dup_Chk_csr INTO l_ea_dup_chk;
265
266 IF l_EA_Dup_Chk_csr%FOUND and l_ea_dup_chk = 'Y' THEN
267
268 l_counter := 0;
269 FOR l_ea_attr_rec in l_ea_attr_csr LOOP
270 l_counter := l_counter + 1;
271 l_cs_extended_attr(l_counter).incident_type_id := l_ServiceRequest_rec.incident_type_id;
272 l_cs_extended_attr(l_counter).sr_attribute_code := l_EA_Attr_rec.sr_attribute_code;
273 l_cs_extended_attr(l_counter).sr_attribute_value := l_EA_Attr_rec.sr_attribute_value;
274 END LOOP;
275
276 /* OPEN l_EA_Attr_csr;
277 LOOP
278 FETCH l_EA_Attr_csr INTO l_EA_Attr_rec;
279 EXIT WHEN l_EA_Attr_csr%NOTFOUND;
280
281 l_counter := l_counter + 1;
282 l_cs_extended_attr(l_counter).incident_type_id := l_ServiceRequest_rec.incident_type_id;
283 l_cs_extended_attr(l_counter).sr_attribute_code := l_EA_Attr_rec.sr_attribute_code;
284 l_cs_extended_attr(l_counter).sr_attribute_value := l_EA_Attr_rec.sr_attribute_value;
285 END LOOP;
286 */
287
288 l_incident_address.Incident_address := l_ServiceRequest_rec.Incident_address;
289 l_incident_address.Incident_city := l_ServiceRequest_rec.Incident_city;
290 l_incident_address.incident_state := l_ServiceRequest_rec.Incident_state;
291 l_incident_address.incident_postal_code := l_ServiceRequest_rec.Incident_postal_code;
292 l_incident_address.incident_country := l_ServiceRequest_rec.Incident_country;
293 END IF;
294 CLOSE l_EA_Dup_Chk_Csr;
295
296 IF l_dup_criteria_profile = 'CS_DUP_CRIT_WITH_SERIAL' THEN
297 l_current_serial_number := l_ServiceRequest_rec.current_serial_number;
298 l_inv_item_serial_number := l_ServiceRequest_rec.item_serial_number;
299 END IF;
300
301 IF (l_dup_criteria_profile = 'CS_DUP_CRIT_WITHNO_SERIAL' OR
302 l_dup_criteria_profile = 'CS_DUP_CRIT_WITH_SERIAL') THEN
303 IF l_ServiceRequest_rec.customer_product_id IS NOT NULL THEN
304 l_customer_product_id := l_ServiceRequest_rec.customer_product_id;
305 l_current_serial_number := NULL;
306 l_inv_item_serial_number := NULL;
307 l_customer_id := NULL;
308 l_inventory_item_id := NULL;
309 ELSE
310 l_customer_product_id := NULL;
311 l_customer_id := l_ServiceRequest_rec.customer_id;
312 l_inventory_item_id := l_ServiceRequest_rec.inventory_item_id;
313 END IF;
314 END IF;
315
316 ELSIF l_dup_criteria_profile = 'CS_DUP_CRIT_INST_CUST_PROD' THEN
317 IF l_ServiceRequest_rec.customer_product_id IS NOT NULL THEN
318 l_customer_product_id := l_ServiceRequest_rec.customer_product_id;
319 l_current_serial_number := NULL;
320 l_inv_item_serial_number := NULL;
321 l_customer_id := NULL;
322 l_inventory_item_id := NULL;
323 ELSE
324 l_customer_product_id := NULL;
325 l_customer_id := l_ServiceRequest_rec.customer_id;
326 l_inventory_item_id := l_ServiceRequest_rec.inventory_item_id;
327 l_current_serial_number := NULL;
328 l_inv_item_serial_number := NULL;
329 END IF;
330
331 ELSIF l_dup_criteria_profile = 'CS_DUP_CRIT_INST_CUST_PROD_SER' THEN
332 IF l_ServiceRequest_rec.customer_product_id IS NOT NULL THEN
333 l_customer_product_id := l_ServiceRequest_rec.customer_product_id;
334 l_current_serial_number := NULL;
335 l_inv_item_serial_number := NULL;
336 l_customer_id := NULL;
337 l_inventory_item_id := NULL;
338 ELSE
339 l_customer_product_id := NULL;
340 l_customer_id := l_ServiceRequest_rec.customer_id;
341 l_inventory_item_id := l_ServiceRequest_rec.inventory_item_id;
342 l_current_serial_number := l_ServiceRequest_rec.current_serial_number;
343 l_inv_item_serial_number := l_ServiceRequest_rec.item_serial_number;
344 END IF;
345 END IF;
346
347 cs_sr_dup_chk_pvt.duplicate_check
348 (
349 p_api_version => l_api_version,
350 p_init_msg_list => l_init_msg_list,
351 p_commit => l_commit,
352 p_validation_level => l_validation_level,
353 p_incident_id => l_incident_id,
354 p_incident_type_id => l_incident_type_id,
355 p_customer_product_id => l_customer_product_id,
356 p_current_serial_number => l_current_serial_number,
357 p_instance_serial_number=> l_instance_serial_number,
358 p_inv_item_serial_number=> l_inv_item_serial_number,
359 p_customer_id => l_customer_id,
360 p_inventory_item_id => l_inventory_item_id,
361 p_cs_extended_attr => l_cs_extended_attr,
362 p_incident_address => l_incident_address,
363 x_duplicate_flag => l_duplicate_flag,
364 x_sr_dupl_rec => l_sr_dupl_rec,
365 x_dup_found_at => l_dup_found_at,
366 x_return_status => l_return_status,
367 x_msg_count => l_msg_count,
368 x_msg_data => l_msg_data
369 );
370
371 IF l_duplicate_flag = 'T' THEN
372 WF_ENGINE.SetItemAttrText(
373 itemtype => 'SERVEREQ',
374 itemkey => itemkey,
375 aname => 'CS_DUPLICATE_FOUND_AT',
376 avalue => l_dup_found_at );
377 result := 'Y';
378 ELSE
379 result := 'N';
380 END IF;
381
382 END IF;
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Check_And_Perf_Dup_Check', itemtype, itemkey, actid, funmode);
387 RAISE;
388
389 END Check_And_Perf_Dup_Check;
390
391
392 PROCEDURE Auto_Task_Create
393 (
394 itemtype VARCHAR2,
395 itemkey VARCHAR2,
396 actid NUMBER,
397 funmode VARCHAR2,
398 result OUT NOCOPY VARCHAR2
399 )
400 IS
401 l_incident_id NUMBER;
402 l_task_prof_value VARCHAR2(30);
403 l_api_version NUMBER := 1.0;
404 l_init_msg_list VARCHAR2(1) ;
405 l_commit VARCHAR2(1) ;
406 l_return_status VARCHAR2(1);
407 l_msg_count NUMBER;
408 l_msg_data VARCHAR2(4000);
409 l_msg_index_out NUMBER;
410 l_parent_noted_id NUMBER;
411 l_jtf_note_id NUMBER;
412
413 l_errmsg_name VARCHAR2(2000);
414 l_API_ERROR EXCEPTION;
415
416 l_sr_rec CS_ServiceRequest_PVT.service_request_rec_type;
417 l_sr_rec2 CS_ServiceRequest_PUB.service_request_rec_type;
418 l_auto_Task_gen_rec CS_AutoGen_Task_PVT.auto_task_gen_rec_type;
419 l_ea_sr_attr_table_type CS_EA_AutoGen_Tasks_PVT.EA_SR_ATTR_TABLE_TYPE;
420
421 l_counter NUMBER ;
422 l_auto_task_gen_attemped VARCHAR2(100);
423 l_note_type VARCHAR2(80);
424 l_field_service_task_created VARCHAR2(100);
425 l_incident_number CS_Incidents_All_B.incident_number%type;
426 l_task_template_group_rec JTF_TASK_INST_TEMPLATES_PUB.task_template_group_info;
427 l_task_template_table JTF_TASK_INST_TEMPLATES_PUB.task_template_info_tbl;
428
429 l_err VARCHAR2(4000);
430 l_login_id jtf_notes_b.last_update_login%TYPE;
431 l_user_id jtf_notes_b.last_updated_by%TYPE;
432 l_note_id NUMBER;
433
434 cursor l_incident_csr is
435 select * from cs_incidents_all_b
436 where incident_id = l_incident_id;
437 l_incident_rec l_incident_csr%ROWTYPE;
438
439 CURSOR l_EA_Attr_csr IS
440 SELECT sr_attribute_code, sr_attribute_value
441 FROM cug_incidnt_attr_vals_vl
442 WHERE incident_id = l_incident_id;
443 l_EA_Attr_rec l_EA_Attr_csr%ROWTYPE;
444
445 --Added for bug 8849552
446 CURSOR cur_tsk_obj_ver IS
447 SELECT object_version_number
448 FROM jtf_tasks_b
449 WHERE source_object_id = l_incident_id
450 and source_object_type_code = 'SR';
451 l_tsk_obj_ver_number NUMBER;
452
453 BEGIN
454 IF (funmode = 'RUN') THEN
455 l_counter := 0;
456 -- l_task_prof_value := FND_PROFILE.value('CS_SR_AUTO_TASK_CREATE');
457 l_task_prof_value := WF_ENGINE.GetItemAttrText(
458 itemtype => itemtype,
459 itemkey => itemkey,
460 aname => 'CS_AUTO_TASK_PROFILE' );
461
462 IF l_task_prof_value = 'NONE' THEN
463 result := 'Y';
464 return;
465 END IF;
466
467 l_incident_id := WF_ENGINE.GetItemAttrNumber(
468 itemtype => itemtype,
469 itemkey => itemkey,
470 aname => 'REQUEST_ID' );
471
472 --Added for bug 8849552
473 /* Start*/
474 OPEN cur_tsk_obj_ver;
475 FETCH cur_tsk_obj_ver into l_tsk_obj_ver_number;
476 IF (cur_tsk_obj_ver%NOTFOUND) THEN
477 l_tsk_obj_ver_number := 0;
478 END IF;
479 IF l_tsk_obj_ver_number <> 0 THEN
480 result := 'Y';
481 return;
482 END IF;
483 /* End*/
484
485 FOR l_rec IN l_incident_csr LOOP
486 l_sr_rec.type_id := l_rec.incident_type_id;
487 l_sr_rec.status_id := l_rec.incident_status_id;
488 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
489 l_sr_rec.severity_id := l_rec.incident_severity_id;
490 l_sr_rec.obligation_date := l_rec.obligation_date;
491 l_sr_rec.problem_code := l_rec.problem_code;
492 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
493 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
494 l_sr_rec.customer_id := l_rec.customer_id;
495 l_sr_rec.customer_number := l_rec.customer_number;
496 l_sr_rec.category_id := l_rec.category_id;
497 l_sr_rec.category_set_id := l_rec.category_set_id;
498 l_sr_rec.incident_location_id := l_rec.incident_location_id;
499 l_sr_rec.incident_location_type := l_rec.incident_location_type;
500 l_sr_rec.request_date := l_rec.incident_date;
501 l_sr_rec.type_id := l_rec.incident_type_id;
502 l_sr_rec.status_id := l_rec.incident_status_id;
503 l_sr_rec.severity_id := l_rec.incident_severity_id;
504 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
505 l_sr_rec.closed_date := l_rec.close_date;
506 l_sr_rec.owner_id := l_rec.incident_owner_id;
507 l_sr_rec.owner_group_id := l_rec.owner_group_id;
508 l_sr_rec.publish_flag := l_rec.publish_flag;
509 l_sr_rec.caller_type := l_rec.caller_type;
510 l_sr_rec.customer_id := l_rec.customer_id;
511 l_sr_rec.customer_number := l_rec.customer_number;
512 l_sr_rec.employee_id := l_rec.employee_id;
513 l_sr_rec.customer_product_id := l_rec.customer_product_id;
514 l_sr_rec.platform_id := l_rec.platform_id;
515 l_sr_rec.platform_version := l_rec.platform_version;
516 l_sr_rec.db_version := l_rec.db_version;
517 l_sr_rec.platform_version_id := l_rec.platform_version_id;
518 l_sr_rec.cp_component_id := l_rec.cp_component_id;
519 l_sr_rec.cp_component_version_id := l_rec.cp_component_version_id;
520 l_sr_rec.cp_subcomponent_id := l_rec.cp_subcomponent_id;
521 l_sr_rec.cp_subcomponent_version_id := l_rec.cp_subcomponent_version_id;
522 l_sr_rec.language_id := l_rec.language_id;
523 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
524 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
525 l_sr_rec.current_serial_number := l_rec.current_serial_number;
526 l_sr_rec.original_order_number := l_rec.original_order_number;
527 l_sr_rec.problem_code := l_rec.problem_code;
528 l_sr_rec.exp_resolution_date := l_rec.expected_resolution_date;
529 l_sr_rec.install_site_use_id := l_rec.install_site_use_id;
530 l_sr_rec.request_attribute_1 := l_rec.incident_attribute_1;
531 l_sr_rec.request_attribute_2 := l_rec.incident_attribute_2;
532 l_sr_rec.request_attribute_3 := l_rec.incident_attribute_3;
533 l_sr_rec.request_attribute_4 := l_rec.incident_attribute_4;
534 l_sr_rec.request_attribute_5 := l_rec.incident_attribute_5;
535 l_sr_rec.request_attribute_6 := l_rec.incident_attribute_6;
536 l_sr_rec.request_attribute_7 := l_rec.incident_attribute_7;
537 l_sr_rec.request_attribute_8 := l_rec.incident_attribute_8;
538 l_sr_rec.request_attribute_9 := l_rec.incident_attribute_9;
539 l_sr_rec.request_attribute_10 := l_rec.incident_attribute_10;
540 l_sr_rec.request_attribute_11 := l_rec.incident_attribute_11;
541 l_sr_rec.request_attribute_12 := l_rec.incident_attribute_12;
542 l_sr_rec.request_attribute_13 := l_rec.incident_attribute_13;
543 l_sr_rec.request_attribute_14 := l_rec.incident_attribute_14;
544 l_sr_rec.request_attribute_15 := l_rec.incident_attribute_15;
545 l_sr_rec.external_attribute_1 := l_rec.external_attribute_1;
546 l_sr_rec.external_attribute_2 := l_rec.external_attribute_2;
547 l_sr_rec.external_attribute_3 := l_rec.external_attribute_3;
548 l_sr_rec.external_attribute_4 := l_rec.external_attribute_4;
549 l_sr_rec.external_attribute_5 := l_rec.external_attribute_5;
550 l_sr_rec.external_attribute_6 := l_rec.external_attribute_6;
551 l_sr_rec.external_attribute_7 := l_rec.external_attribute_7;
552 l_sr_rec.external_attribute_8 := l_rec.external_attribute_8;
553 l_sr_rec.external_attribute_9 := l_rec.external_attribute_9;
554 l_sr_rec.external_attribute_10 := l_rec.external_attribute_10;
555 l_sr_rec.external_attribute_11 := l_rec.external_attribute_11;
556 l_sr_rec.external_attribute_12 := l_rec.external_attribute_12;
557 l_sr_rec.external_attribute_13 := l_rec.external_attribute_13;
558 l_sr_rec.external_attribute_14 := l_rec.external_attribute_14;
559 l_sr_rec.external_attribute_15 := l_rec.external_attribute_15;
560 l_sr_rec.external_context := l_rec.external_context;
561 l_sr_rec.bill_to_site_use_id := l_rec.bill_to_site_use_id;
562 l_sr_rec.bill_to_contact_id := l_rec.bill_to_contact_id;
563 l_sr_rec.ship_to_site_use_id := l_rec.ship_to_site_use_id;
564 l_sr_rec.ship_to_contact_id := l_rec.ship_to_contact_id;
565 l_sr_rec.resolution_code := l_rec.resolution_code;
566 l_sr_rec.act_resolution_date := l_rec.actual_resolution_date;
567 l_sr_rec.contract_service_id := l_rec.contract_service_id;
568 l_sr_rec.contract_id := l_rec.contract_id;
569 l_sr_rec.project_number := l_rec.project_number;
570 l_sr_rec.qa_collection_plan_id := l_rec.qa_collection_id;
571 l_sr_rec.account_id := l_rec.account_id;
572 l_sr_rec.resource_type := l_rec.resource_type;
573 l_sr_rec.resource_subtype_id := l_rec.resource_subtype_id;
574 l_sr_rec.sr_creation_channel := l_rec.sr_creation_channel;
575 l_sr_rec.obligation_date := l_rec.obligation_date;
576 l_sr_rec.time_zone_id := l_rec.time_zone_id;
577 l_sr_rec.time_difference := l_rec.time_difference;
578 l_sr_rec.site_id := l_rec.site_id;
579 l_sr_rec.customer_site_id := l_rec.customer_site_id;
580 l_sr_rec.territory_id := l_rec.territory_id;
581 l_sr_rec.cp_revision_id := l_rec.cp_revision_id;
582 l_sr_rec.inv_item_revision := l_rec.inv_item_revision;
583 l_sr_rec.inv_component_id := l_rec.inv_component_id;
584 l_sr_rec.inv_component_version := l_rec.inv_component_version;
585 l_sr_rec.inv_subcomponent_id := l_rec.inv_subcomponent_id;
586 l_sr_rec.inv_subcomponent_version := l_rec.inv_subcomponent_version;
587 l_sr_rec.tier := l_rec.tier;
588 l_sr_rec.tier_version := l_rec.tier_version;
589 l_sr_rec.operating_system := l_rec.operating_system;
590 l_sr_rec.operating_system_version := l_rec.operating_system_version;
591 l_sr_rec.database := l_rec.database;
592 l_sr_rec.cust_pref_lang_id := l_rec.cust_pref_lang_id;
593 l_sr_rec.category_id := l_rec.category_id;
594 l_sr_rec.group_type := l_rec.group_type;
595 l_sr_rec.group_territory_id := l_rec.group_territory_id;
596 l_sr_rec.inv_platform_org_id := l_rec.inv_platform_org_id;
597 l_sr_rec.component_version := l_rec.component_version;
598 l_sr_rec.subcomponent_version := l_rec.subcomponent_version;
599 l_sr_rec.comm_pref_code := l_rec.comm_pref_code;
600 l_sr_rec.cust_pref_lang_code := l_rec.cust_pref_lang_code;
601 l_sr_rec.last_update_channel := l_rec.last_update_channel;
602 l_sr_rec.category_set_id := l_rec.category_set_id;
603 l_sr_rec.external_reference := l_rec.external_reference;
604 l_sr_rec.system_id := l_rec.system_id;
605 l_sr_rec.error_code := l_rec.error_code;
606 l_sr_rec.incident_occurred_date := l_rec.incident_occurred_date;
607 l_sr_rec.incident_resolved_date := l_rec.incident_resolved_date;
608 l_sr_rec.inc_responded_by_date := l_rec.inc_responded_by_date;
609 l_sr_rec.incident_location_id := l_rec.incident_location_id;
610 l_sr_rec.incident_address := l_rec.incident_address;
611 l_sr_rec.incident_city := l_rec.incident_city;
612 l_sr_rec.incident_state := l_rec.incident_state;
613 l_sr_rec.incident_country := l_rec.incident_country;
614 l_sr_rec.incident_province := l_rec.incident_province;
615 l_sr_rec.incident_postal_code := l_rec.incident_postal_code;
616 l_sr_rec.incident_county := l_rec.incident_country;
617 l_sr_rec.cc_number := l_rec.credit_card_number;
618 l_sr_rec.cc_expiration_date := l_rec.credit_card_expiration_date;
619 l_sr_rec.cc_type_code := l_rec.credit_card_type_code;
620 l_sr_rec.cc_first_name := l_rec.credit_card_holder_fname;
621 l_sr_rec.cc_last_name := l_rec.credit_card_holder_lname;
622 l_sr_rec.cc_middle_name := l_rec.credit_card_holder_mname;
623 l_sr_rec.cc_id := l_rec.credit_card_id;
624 l_sr_rec.bill_to_account_id := l_rec.bill_to_account_id;
625 l_sr_rec.ship_to_account_id := l_rec.ship_to_account_id;
626 l_sr_rec.customer_phone_id := l_rec.customer_phone_id;
627 l_sr_rec.customer_email_id := l_rec.customer_email_id;
628 l_sr_rec.creation_program_code := l_rec.creation_program_code;
629 l_sr_rec.last_update_program_code := l_rec.last_update_program_code;
630 l_sr_rec.bill_to_party_id := l_rec.bill_to_party_id;
631 l_sr_rec.ship_to_party_id := l_rec.ship_to_party_id;
632 l_sr_rec.program_id := l_rec.program_id;
633 l_sr_rec.program_application_id := l_rec.program_application_id;
634 l_sr_rec.program_login_id := l_rec.program_login_id;
635 l_sr_rec.bill_to_site_id := l_rec.bill_to_site_id;
636 l_sr_rec.ship_to_site_id := l_rec.ship_to_site_id;
637 l_sr_rec.incident_point_of_interest := l_rec.incident_point_of_interest;
638 l_sr_rec.incident_cross_street := l_rec.incident_cross_street;
639 l_sr_rec.incident_direction_qualifier:= l_rec.incident_direction_qualifier;
640 l_sr_rec.incident_distance_qualifier := l_rec.incident_distance_qualifier;
641 l_sr_rec.incident_distance_qual_uom := l_rec.incident_distance_qual_uom;
642 l_sr_rec.incident_address2 := l_rec.incident_address2;
643 l_sr_rec.incident_address3 := l_rec.incident_address3;
644 l_sr_rec.incident_address4 := l_rec.incident_address4;
645 l_sr_rec.incident_address_style := l_rec.incident_address_style;
646 l_sr_rec.incident_addr_lines_phonetic:= l_rec.incident_addr_lines_phonetic;
647 l_sr_rec.incident_po_box_number := l_rec.incident_po_box_number;
648 l_sr_rec.incident_house_number := l_rec.incident_house_number;
649 l_sr_rec.incident_street_suffix := l_rec.incident_street_suffix;
650 l_sr_rec.incident_street := l_rec.incident_street;
651 l_sr_rec.incident_street_number := l_rec.incident_street_number;
652 l_sr_rec.incident_floor := l_rec.incident_floor;
653 l_sr_rec.incident_suite := l_rec.incident_suite;
654 l_sr_rec.incident_postal_plus4_code := l_rec.incident_postal_plus4_code;
655 l_sr_rec.incident_position := l_rec.incident_position;
656 l_sr_rec.incident_location_directions:= l_rec.incident_location_directions;
657 l_sr_rec.incident_location_description := l_rec.incident_location_description;
658 l_sr_rec.install_site_id := l_rec.install_site_id;
659
660 l_sr_rec2.type_id := l_rec.incident_type_id;
661 l_sr_rec2.status_id := l_rec.incident_status_id;
662 l_sr_rec2.urgency_id := l_rec.incident_urgency_id;
663 l_sr_rec2.severity_id := l_rec.incident_severity_id;
664 l_sr_rec2.obligation_date := l_rec.obligation_date;
665 l_sr_rec2.problem_code := l_rec.problem_code;
666 l_sr_rec2.inventory_item_id := l_rec.inventory_item_id;
667 l_sr_rec2.inventory_org_id := l_rec.inv_organization_id;
668 l_sr_rec2.customer_id := l_rec.customer_id;
669 l_sr_rec2.customer_number := l_rec.customer_number;
670 l_sr_rec2.category_id := l_rec.category_id;
671 l_sr_rec2.category_set_id := l_rec.category_set_id;
672 l_sr_rec2.incident_location_id := l_rec.incident_location_id;
673 l_sr_rec2.incident_location_type := l_rec.incident_location_type;
674 l_sr_rec2.request_date := l_rec.incident_date;
675 l_sr_rec2.type_id := l_rec.incident_type_id;
676 l_sr_rec2.status_id := l_rec.incident_status_id;
677 l_sr_rec2.severity_id := l_rec.incident_severity_id;
678 l_sr_rec2.urgency_id := l_rec.incident_urgency_id;
679 l_sr_rec2.closed_date := l_rec.close_date;
680 l_sr_rec2.owner_id := l_rec.incident_owner_id;
681 l_sr_rec2.owner_group_id := l_rec.owner_group_id;
682 l_sr_rec2.publish_flag := l_rec.publish_flag;
683 l_sr_rec2.caller_type := l_rec.caller_type;
684 l_sr_rec2.customer_id := l_rec.customer_id;
685 l_sr_rec2.customer_number := l_rec.customer_number;
686 l_sr_rec2.employee_id := l_rec.employee_id;
687 l_sr_rec2.customer_product_id := l_rec.customer_product_id;
688 l_sr_rec2.platform_id := l_rec.platform_id;
689 l_sr_rec2.platform_version := l_rec.platform_version;
690 l_sr_rec2.db_version := l_rec.db_version;
691 l_sr_rec2.platform_version_id := l_rec.platform_version_id;
692 l_sr_rec2.cp_component_id := l_rec.cp_component_id;
693 l_sr_rec2.cp_component_version_id := l_rec.cp_component_version_id;
694 l_sr_rec2.cp_subcomponent_id := l_rec.cp_subcomponent_id;
695 l_sr_rec2.cp_subcomponent_version_id := l_rec.cp_subcomponent_version_id;
696 l_sr_rec2.language_id := l_rec.language_id;
697 l_sr_rec2.inventory_item_id := l_rec.inventory_item_id;
698 l_sr_rec2.inventory_org_id := l_rec.inv_organization_id;
699 l_sr_rec2.current_serial_number := l_rec.current_serial_number;
700 l_sr_rec2.original_order_number := l_rec.original_order_number;
701 l_sr_rec2.problem_code := l_rec.problem_code;
702 l_sr_rec2.exp_resolution_date := l_rec.expected_resolution_date;
703 l_sr_rec2.install_site_use_id := l_rec.install_site_use_id;
704 l_sr_rec2.request_attribute_1 := l_rec.incident_attribute_1;
705 l_sr_rec2.request_attribute_2 := l_rec.incident_attribute_2;
706 l_sr_rec2.request_attribute_3 := l_rec.incident_attribute_3;
707 l_sr_rec2.request_attribute_4 := l_rec.incident_attribute_4;
708 l_sr_rec2.request_attribute_5 := l_rec.incident_attribute_5;
709 l_sr_rec2.request_attribute_6 := l_rec.incident_attribute_6;
710 l_sr_rec2.request_attribute_7 := l_rec.incident_attribute_7;
711 l_sr_rec2.request_attribute_8 := l_rec.incident_attribute_8;
712 l_sr_rec2.request_attribute_9 := l_rec.incident_attribute_9;
713 l_sr_rec2.request_attribute_10 := l_rec.incident_attribute_10;
714 l_sr_rec2.request_attribute_11 := l_rec.incident_attribute_11;
715 l_sr_rec2.request_attribute_12 := l_rec.incident_attribute_12;
716 l_sr_rec2.request_attribute_13 := l_rec.incident_attribute_13;
717 l_sr_rec2.request_attribute_14 := l_rec.incident_attribute_14;
718 l_sr_rec2.request_attribute_15 := l_rec.incident_attribute_15;
719 l_sr_rec2.external_attribute_1 := l_rec.external_attribute_1;
720 l_sr_rec2.external_attribute_2 := l_rec.external_attribute_2;
721 l_sr_rec2.external_attribute_3 := l_rec.external_attribute_3;
722 l_sr_rec2.external_attribute_4 := l_rec.external_attribute_4;
723 l_sr_rec2.external_attribute_5 := l_rec.external_attribute_5;
724 l_sr_rec2.external_attribute_6 := l_rec.external_attribute_6;
725 l_sr_rec2.external_attribute_7 := l_rec.external_attribute_7;
726 l_sr_rec2.external_attribute_8 := l_rec.external_attribute_8;
727 l_sr_rec2.external_attribute_9 := l_rec.external_attribute_9;
728 l_sr_rec2.external_attribute_10 := l_rec.external_attribute_10;
729 l_sr_rec2.external_attribute_11 := l_rec.external_attribute_11;
730 l_sr_rec2.external_attribute_12 := l_rec.external_attribute_12;
731 l_sr_rec2.external_attribute_13 := l_rec.external_attribute_13;
732 l_sr_rec2.external_attribute_14 := l_rec.external_attribute_14;
733 l_sr_rec2.external_attribute_15 := l_rec.external_attribute_15;
734 l_sr_rec2.external_context := l_rec.external_context;
735 l_sr_rec2.bill_to_site_use_id := l_rec.bill_to_site_use_id;
736 l_sr_rec2.bill_to_contact_id := l_rec.bill_to_contact_id;
737 l_sr_rec2.ship_to_site_use_id := l_rec.ship_to_site_use_id;
738 l_sr_rec2.ship_to_contact_id := l_rec.ship_to_contact_id;
739 l_sr_rec2.resolution_code := l_rec.resolution_code;
740 l_sr_rec2.act_resolution_date := l_rec.actual_resolution_date;
741 l_sr_rec2.contract_service_id := l_rec.contract_service_id;
742 l_sr_rec2.contract_id := l_rec.contract_id;
743 l_sr_rec2.project_number := l_rec.project_number;
744 l_sr_rec2.qa_collection_plan_id := l_rec.qa_collection_id;
745 l_sr_rec2.account_id := l_rec.account_id;
746 l_sr_rec2.resource_type := l_rec.resource_type;
747 l_sr_rec2.resource_subtype_id := l_rec.resource_subtype_id;
748 l_sr_rec2.sr_creation_channel := l_rec.sr_creation_channel;
749 l_sr_rec2.obligation_date := l_rec.obligation_date;
750 l_sr_rec2.time_zone_id := l_rec.time_zone_id;
751 l_sr_rec2.time_difference := l_rec.time_difference;
752 l_sr_rec2.site_id := l_rec.site_id;
753 l_sr_rec2.customer_site_id := l_rec.customer_site_id;
754 l_sr_rec2.territory_id := l_rec.territory_id;
755 l_sr_rec2.cp_revision_id := l_rec.cp_revision_id;
756 l_sr_rec2.inv_item_revision := l_rec.inv_item_revision;
757 l_sr_rec2.inv_component_id := l_rec.inv_component_id;
758 l_sr_rec2.inv_component_version := l_rec.inv_component_version;
759 l_sr_rec2.inv_subcomponent_id := l_rec.inv_subcomponent_id;
760 l_sr_rec2.inv_subcomponent_version := l_rec.inv_subcomponent_version;
761 l_sr_rec2.tier := l_rec.tier;
762 l_sr_rec2.tier_version := l_rec.tier_version;
763 l_sr_rec2.operating_system := l_rec.operating_system;
764 l_sr_rec2.operating_system_version := l_rec.operating_system_version;
765 l_sr_rec2.database := l_rec.database;
766 l_sr_rec2.cust_pref_lang_id := l_rec.cust_pref_lang_id;
767 l_sr_rec2.category_id := l_rec.category_id;
768 l_sr_rec2.group_type := l_rec.group_type;
769 l_sr_rec2.group_territory_id := l_rec.group_territory_id;
770 l_sr_rec2.inv_platform_org_id := l_rec.inv_platform_org_id;
771 l_sr_rec2.component_version := l_rec.component_version;
772 l_sr_rec2.subcomponent_version := l_rec.subcomponent_version;
773 l_sr_rec2.comm_pref_code := l_rec.comm_pref_code;
774 l_sr_rec2.cust_pref_lang_code := l_rec.cust_pref_lang_code;
775 l_sr_rec2.last_update_channel := l_rec.last_update_channel;
776 l_sr_rec2.category_set_id := l_rec.category_set_id;
777 l_sr_rec2.external_reference := l_rec.external_reference;
778 l_sr_rec2.system_id := l_rec.system_id;
779 l_sr_rec2.error_code := l_rec.error_code;
780 l_sr_rec2.incident_occurred_date := l_rec.incident_occurred_date;
781 l_sr_rec2.incident_resolved_date := l_rec.incident_resolved_date;
782 l_sr_rec2.inc_responded_by_date := l_rec.inc_responded_by_date;
783 l_sr_rec2.incident_location_id := l_rec.incident_location_id;
784 l_sr_rec2.incident_address := l_rec.incident_address;
785 l_sr_rec2.incident_city := l_rec.incident_city;
786 l_sr_rec2.incident_state := l_rec.incident_state;
787 l_sr_rec2.incident_country := l_rec.incident_country;
788 l_sr_rec2.incident_province := l_rec.incident_province;
789 l_sr_rec2.incident_postal_code := l_rec.incident_postal_code;
790 l_sr_rec2.incident_county := l_rec.incident_country;
791 l_sr_rec2.cc_number := l_rec.credit_card_number;
792 l_sr_rec2.cc_expiration_date := l_rec.credit_card_expiration_date;
793 l_sr_rec2.cc_type_code := l_rec.credit_card_type_code;
794 l_sr_rec2.cc_first_name := l_rec.credit_card_holder_fname;
795 l_sr_rec2.cc_last_name := l_rec.credit_card_holder_lname;
796 l_sr_rec2.cc_middle_name := l_rec.credit_card_holder_mname;
797 l_sr_rec2.cc_id := l_rec.credit_card_id;
798 l_sr_rec2.bill_to_account_id := l_rec.bill_to_account_id;
799 l_sr_rec2.ship_to_account_id := l_rec.ship_to_account_id;
800 l_sr_rec2.customer_phone_id := l_rec.customer_phone_id;
801 l_sr_rec2.customer_email_id := l_rec.customer_email_id;
802 l_sr_rec2.creation_program_code := l_rec.creation_program_code;
803 l_sr_rec2.last_update_program_code := l_rec.last_update_program_code;
804 l_sr_rec2.bill_to_party_id := l_rec.bill_to_party_id;
805 l_sr_rec2.ship_to_party_id := l_rec.ship_to_party_id;
806 l_sr_rec2.program_id := l_rec.program_id;
807 l_sr_rec2.program_application_id := l_rec.program_application_id;
808 l_sr_rec2.program_login_id := l_rec.program_login_id;
809 l_sr_rec2.bill_to_site_id := l_rec.bill_to_site_id;
810 l_sr_rec2.ship_to_site_id := l_rec.ship_to_site_id;
811 l_sr_rec2.incident_point_of_interest := l_rec.incident_point_of_interest;
812 l_sr_rec2.incident_cross_street := l_rec.incident_cross_street;
813 l_sr_rec2.incident_direction_qualifier:= l_rec.incident_direction_qualifier;
814 l_sr_rec2.incident_distance_qualifier := l_rec.incident_distance_qualifier;
815 l_sr_rec2.incident_distance_qual_uom := l_rec.incident_distance_qual_uom;
816 l_sr_rec2.incident_address2 := l_rec.incident_address2;
817 l_sr_rec2.incident_address3 := l_rec.incident_address3;
818 l_sr_rec2.incident_address4 := l_rec.incident_address4;
819 l_sr_rec2.incident_address_style := l_rec.incident_address_style;
820 l_sr_rec2.incident_addr_lines_phonetic:= l_rec.incident_addr_lines_phonetic;
821 l_sr_rec2.incident_po_box_number := l_rec.incident_po_box_number;
822 l_sr_rec2.incident_house_number := l_rec.incident_house_number;
823 l_sr_rec2.incident_street_suffix := l_rec.incident_street_suffix;
824 l_sr_rec2.incident_street := l_rec.incident_street;
825 l_sr_rec2.incident_street_number := l_rec.incident_street_number;
826 l_sr_rec2.incident_floor := l_rec.incident_floor;
827 l_sr_rec2.incident_suite := l_rec.incident_suite;
828 l_sr_rec2.incident_postal_plus4_code := l_rec.incident_postal_plus4_code;
829 l_sr_rec2.incident_position := l_rec.incident_position;
830 l_sr_rec2.incident_location_directions:= l_rec.incident_location_directions;
831 l_sr_rec2.incident_location_description := l_rec.incident_location_description;
832 l_sr_rec2.install_site_id := l_rec.install_site_id;
833
834 l_incident_number := l_rec.incident_number;
835
836 END LOOP;
837 -- l_sr_rec2 := l_sr_rec;
838
839 IF l_task_prof_value = 'TASK_TMPL' THEN
840
841 CS_AutoGen_Task_PVT.Auto_Generate_Tasks
842 (
843 p_api_version => l_api_version,
844 p_init_msg_list => l_init_msg_list,
845 p_commit => l_commit,
846 p_validation_level => fnd_api.g_valid_level_full,
847 p_incident_id => l_incident_id,
848 p_service_request_rec => l_sr_rec,
849 p_task_template_group_owner => NULL,
850 p_task_tmpl_group_owner_type=> NULL,
851 p_task_template_group_rec => l_task_template_group_rec,
852 p_task_template_table => l_task_template_table,
853 x_auto_task_gen_rec => l_auto_Task_gen_rec,
854 x_return_status => l_return_status,
855 x_msg_count => l_msg_count,
856 x_msg_data => l_msg_data );
857
858 ELSIF l_task_prof_value = 'TASK_CONF' THEN
859 l_counter := 0;
860 FOR l_ea_attr_rec in l_ea_attr_csr LOOP
861 l_counter := l_counter + 1;
862 l_ea_sr_attr_table_type(l_counter).sr_attribute_code := l_ea_attr_rec.sr_attribute_code;
863 l_ea_sr_attr_table_type(l_counter).sr_attribute_value := l_ea_attr_rec.sr_attribute_value;
864 END LOOP;
865 /*
866 OPEN l_ea_attr_csr;
867 FETCH l_ea_attr_csr BULK COLLECT INTO l_ea_sr_attr_table_type;
868 CLOSE l_ea_attr_csr;
869 */
870
871 CS_EA_AutoGen_Tasks_PVT.Create_Extnd_Attr_Tasks
872 (
873 p_api_version => l_api_version,
874 p_init_msg_list => l_init_msg_list,
875 p_commit => l_commit,
876 p_sr_rec => l_sr_rec2,
877 p_sr_attributes_tbl => l_ea_sr_attr_table_type,
878 p_REQUEST_ID => l_incident_id,
879 p_incident_number => l_incident_number,
880 x_return_status => l_return_status,
881 x_msg_count => l_msg_count,
882 x_msg_data => l_msg_data,
883 x_auto_task_gen_attempted => l_auto_task_gen_attemped,
884 x_field_service_Task_created => l_field_service_task_created
885 );
886 END IF;
887
888 IF (l_return_status <> 'S') THEN
889 for i in 1..l_msg_count loop
890 FND_MSG_PUB.Get(p_msg_index=>i,
891 p_encoded=>'F',
892 p_data=>l_msg_data,
893 p_msg_index_out=>l_msg_index_out);
894 l_err := l_err || l_msg_data ;
895 end loop;
896
897 -- l_note_type := fnd_profile.value('CS_SR_TASK_ERROR_NOTE_TYPE');
898 l_note_type := WF_ENGINE.GetItemAttrText(
899 itemtype => itemtype,
900 itemkey => itemkey,
901 aname => 'CS_TASK_FAILURE_NOTE_PROFILE' );
902
903 IF (l_note_type is null) THEN
904 fnd_msg_pub.initialize;
905 fnd_message.set_name ('CS', 'CS_EA_NULL_NOTE_TYPE');
906 fnd_msg_pub.add;
907
908 raise l_api_error;
909 END IF;
910
911 l_return_status := NULL;
912 l_login_id := fnd_global.login_id;
913 l_user_id := fnd_global.user_id ;
914
915 jtf_notes_pub.create_note
916 (
917 p_parent_note_id => l_parent_noted_id,
918 p_jtf_note_id => l_jtf_note_id,
919 p_api_version => 1,
920 p_init_msg_list => l_init_msg_list,
921 p_commit => l_commit,
922 p_validation_level => fnd_api.g_valid_level_full,
923 x_return_status => l_return_status,
924 x_msg_count => l_msg_count,
925 x_msg_data => l_msg_data,
926 p_entered_by => l_user_id,
927 p_entered_date => sysdate,
928 p_last_update_date => sysdate,
929 p_last_updated_by => l_user_id,
930 p_creation_date => sysdate,
931 p_created_by => l_user_id,
932 p_last_update_login => l_login_id,
933 p_source_object_id => l_incident_id,
934 p_source_object_code => 'SR',
935 p_notes => l_err,
936 p_notes_detail => l_err,
937 p_note_type => l_note_type,
938 p_note_status => 'P',
939 x_jtf_note_id => l_note_id
940 );
941 END IF;
942 END IF;
943
944 EXCEPTION
945 WHEN l_API_ERROR THEN
946 l_errmsg_name := NULL;
947 for i in 1..l_msg_count loop
948 FND_MSG_PUB.Get(p_msg_index=>i,
949 p_encoded=>'F',
950 p_data=>l_msg_data,
951 p_msg_index_out=>l_msg_index_out);
952 l_errmsg_name := l_errmsg_name || l_msg_data ;
953 end loop;
954 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Auto_Task_Create - '|| l_errmsg_name || ' ....(' || l_err || ')....',
955 itemtype, itemkey, actid, funmode);
956 RAISE;
957
958 WHEN OTHERS THEN
959 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Auto_Task_Create - ' || sqlerrm,
960 itemtype, itemkey, actid, funmode);
961 RAISE;
962
963 END Auto_Task_Create;
964
965
966 PROCEDURE Setup_Notify_Name
967 (
968 itemtype VARCHAR2,
969 itemkey VARCHAR2,
970 actid NUMBER,
971 funmode VARCHAR2,
972 result OUT NOCOPY VARCHAR2
973 )
974 IS
975
976 l_return_status VARCHAR2(1);
977 l_msg_count NUMBER;
978 l_msg_data VARCHAR2(2000);
979 l_incident_id NUMBER;
980 l_dup_found_at VARCHAR2(10);
981
982 l_owner_role VARCHAR2(100);
983 l_owner_name VARCHAR2(240);
984
985 l_errmsg_name VARCHAR2(30);
986 l_API_ERROR EXCEPTION;
987
988 CURSOR l_ServiceRequest_csr IS
989 SELECT emp.source_id incident_owner_id
990 FROM cs_incidents_all_vl inc ,jtf_rs_resource_extns emp
991 WHERE inc.INCIDENT_OWNER_ID = emp.resource_id AND
992 incident_id = l_incident_id;
993
994 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
995
996 CURSOR l_EA_Recipient_notify_csr IS
997 SELECT emp.source_id incident_owner_id
998 FROM cug_sr_type_dup_chk_info maps, cs_incidents_all_b sr, jtf_rs_resource_extns emp
999 WHERE maps.incident_type_id = sr.incident_type_id
1000 AND maps.resource_id = emp.resource_id
1001 AND sr.incident_id = l_incident_id;
1002
1003 l_EA_Recipient_Notify_rec l_EA_Recipient_Notify_csr%ROWTYPE;
1004
1005 BEGIN
1006
1007 IF (funmode = 'RUN') THEN
1008
1009 l_incident_id := WF_ENGINE.GetItemAttrNumber(
1010 itemtype => itemtype,
1011 itemkey => itemkey,
1012 aname => 'REQUEST_ID' );
1013
1014 l_dup_found_at := WF_ENGINE.GetItemAttrText(
1015 itemtype => itemtype,
1016 itemkey => itemkey,
1017 aname => 'CS_DUPLICATE_FOUND_AT' );
1018
1019 IF l_dup_found_at = 'BOTH' or l_dup_found_at = 'SR' THEN
1020
1021 OPEN l_ServiceRequest_csr;
1022 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
1023
1024 IF (l_ServiceRequest_rec.incident_owner_id is not null) THEN
1025 -- Retrieve the role name for the request owner
1026 CS_WORKFLOW_PUB.Get_Employee_Role (
1027 p_api_version => 1.0,
1028 p_return_status => l_return_status,
1029 p_msg_count => l_msg_count,
1030 p_msg_data => l_msg_data,
1031 p_employee_id => l_ServiceRequest_rec.incident_owner_id,
1032 p_role_name => l_owner_role,
1033 p_role_display_name => l_owner_name );
1034
1035 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_owner_role is NULL) THEN
1036 wf_core.context(
1037 pkg_name => 'CS_WORKFLOW_PUB',
1038 proc_name => 'Get_Employee_Role',
1039 arg1 => 'p_employee_id=>'||to_char(l_ServiceRequest_rec.incident_owner_id));
1040 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
1041 raise l_API_ERROR;
1042 END IF;
1043
1044 WF_ENGINE.SetItemAttrText(
1045 itemtype => 'SERVEREQ',
1046 itemkey => itemkey,
1047 aname => 'OWNER_NAME',
1048 avalue => l_owner_name );
1049
1050 WF_ENGINE.SetItemAttrText(
1051 itemtype => 'SERVEREQ',
1052 itemkey => itemkey,
1053 aname => 'OWNER_ROLE',
1054 avalue => l_owner_role );
1055 END IF;
1056 CLOSE l_ServiceRequest_csr;
1057 END IF;
1058
1059 IF l_dup_found_at = 'BOTH' or l_dup_found_at = 'EA' THEN
1060 OPEN l_EA_Recipient_Notify_csr;
1061 FETCH l_EA_Recipient_Notify_csr INTO l_EA_Recipient_Notify_rec;
1062
1063 IF (l_EA_Recipient_Notify_rec.incident_owner_id is not null) THEN
1064 -- Retrieve the role name for the request owner
1065 CS_WORKFLOW_PUB.Get_Employee_Role (
1066 p_api_version => 1.0,
1067 p_return_status => l_return_status,
1068 p_msg_count => l_msg_count,
1069 p_msg_data => l_msg_data,
1070 p_employee_id => l_EA_Recipient_Notify_rec.incident_owner_id,
1071 p_role_name => l_owner_role,
1072 p_role_display_name => l_owner_name );
1073
1074 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_owner_role is NULL) THEN
1075 wf_core.context(
1076 pkg_name => 'CS_WORKFLOW_PUB',
1077 proc_name => 'Get_Employee_Role',
1078 arg1 => 'p_employee_id=>'||to_char(l_EA_Recipient_Notify_rec.incident_owner_id));
1079 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
1080 raise l_API_ERROR;
1081 END IF;
1082
1083 WF_ENGINE.SetItemAttrText(
1084 itemtype => 'SERVEREQ',
1085 itemkey => itemkey,
1086 aname => 'CS_EA_SR_OWNER_NOTIFY',
1087 avalue => l_owner_role );
1088
1089 /*
1090 WF_ENGINE.SetItemAttrText(
1091 itemtype => 'SERVEREQ',
1092 itemkey => itemkey,
1093 aname => 'CS_EA_SR_OWNER_NOTIFY',
1094 avalue => l_owner_name );
1095 */
1096 END IF;
1097 CLOSE l_EA_Recipient_Notify_csr;
1098 END IF;
1099
1100 IF l_dup_found_at = 'BOTH' THEN
1101 result := 'CS_BOTH';
1102 ELSIF l_dup_found_at = 'SR' THEN
1103 result := 'CS_SR';
1104 ELSIF l_dup_found_at = 'EA' THEN
1105 result := 'CS_EA';
1106 END IF;
1107
1108 END IF;
1109
1110 EXCEPTION
1111 WHEN l_API_ERROR THEN
1112 WF_CORE.Raise(l_errmsg_name);
1113
1114 WHEN OTHERS THEN
1115 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Setup_Notify_Name',
1116 itemtype, itemkey, actid, funmode);
1117 RAISE;
1118
1119 END Setup_Notify_Name;
1120
1121
1122 PROCEDURE Check_SR_Owner_To_Notify
1123 (
1124 itemtype VARCHAR2,
1125 itemkey VARCHAR2,
1126 actid NUMBER,
1127 funmode VARCHAR2,
1128 result OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131 l_incident_id NUMBER;
1132
1133 CURSOR l_ServiceRequest_csr IS
1134 SELECT incident_owner_id
1135 FROM cs_incidents_all_b inc
1136 WHERE incident_id = l_incident_id;
1137
1138 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
1139
1140 BEGIN
1141
1142 IF (funmode = 'RUN') THEN
1143
1144 l_incident_id := WF_ENGINE.GetItemAttrNumber(
1145 itemtype => itemtype,
1146 itemkey => itemkey,
1147 aname => 'REQUEST_ID' );
1148
1149 OPEN l_ServiceRequest_csr;
1150 FETCH l_ServiceRequest_csr into l_ServiceRequest_rec;
1151
1152 IF l_ServiceRequest_rec.incident_owner_id IS NULL THEN
1153 result := 'N';
1154 ELSE
1155 result := 'Y';
1156 END IF;
1157 CLOSE l_ServiceRequest_csr;
1158 END IF;
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Setup_Notify_Name',
1162 itemtype, itemkey, actid, funmode);
1163 RAISE;
1164
1165 END Check_SR_Owner_To_Notify;
1166
1167 END CS_SR_WF_DUP_CHK_PVT;