[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 2005/11/17 15:53:51 aneemuch noship $ */
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 BEGIN
446 IF (funmode = 'RUN') THEN
447 l_counter := 0;
448 -- l_task_prof_value := FND_PROFILE.value('CS_SR_AUTO_TASK_CREATE');
449 l_task_prof_value := WF_ENGINE.GetItemAttrText(
450 itemtype => itemtype,
451 itemkey => itemkey,
452 aname => 'CS_AUTO_TASK_PROFILE' );
453
454 IF l_task_prof_value = 'NONE' THEN
455 result := 'Y';
456 return;
457 END IF;
458
459 l_incident_id := WF_ENGINE.GetItemAttrNumber(
460 itemtype => itemtype,
461 itemkey => itemkey,
462 aname => 'REQUEST_ID' );
463
464 FOR l_rec IN l_incident_csr LOOP
465 l_sr_rec.type_id := l_rec.incident_type_id;
466 l_sr_rec.status_id := l_rec.incident_status_id;
467 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
468 l_sr_rec.severity_id := l_rec.incident_severity_id;
469 l_sr_rec.obligation_date := l_rec.obligation_date;
470 l_sr_rec.problem_code := l_rec.problem_code;
471 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
472 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
473 l_sr_rec.customer_id := l_rec.customer_id;
474 l_sr_rec.customer_number := l_rec.customer_number;
475 l_sr_rec.category_id := l_rec.category_id;
476 l_sr_rec.category_set_id := l_rec.category_set_id;
477 l_sr_rec.incident_location_id := l_rec.incident_location_id;
478 l_sr_rec.incident_location_type := l_rec.incident_location_type;
479 l_sr_rec.request_date := l_rec.incident_date;
480 l_sr_rec.type_id := l_rec.incident_type_id;
481 l_sr_rec.status_id := l_rec.incident_status_id;
482 l_sr_rec.severity_id := l_rec.incident_severity_id;
483 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
484 l_sr_rec.closed_date := l_rec.close_date;
485 l_sr_rec.owner_id := l_rec.incident_owner_id;
486 l_sr_rec.owner_group_id := l_rec.owner_group_id;
487 l_sr_rec.publish_flag := l_rec.publish_flag;
488 l_sr_rec.caller_type := l_rec.caller_type;
489 l_sr_rec.customer_id := l_rec.customer_id;
490 l_sr_rec.customer_number := l_rec.customer_number;
491 l_sr_rec.employee_id := l_rec.employee_id;
492 l_sr_rec.customer_product_id := l_rec.customer_product_id;
493 l_sr_rec.platform_id := l_rec.platform_id;
494 l_sr_rec.platform_version := l_rec.platform_version;
495 l_sr_rec.db_version := l_rec.db_version;
496 l_sr_rec.platform_version_id := l_rec.platform_version_id;
497 l_sr_rec.cp_component_id := l_rec.cp_component_id;
498 l_sr_rec.cp_component_version_id := l_rec.cp_component_version_id;
499 l_sr_rec.cp_subcomponent_id := l_rec.cp_subcomponent_id;
500 l_sr_rec.cp_subcomponent_version_id := l_rec.cp_subcomponent_version_id;
501 l_sr_rec.language_id := l_rec.language_id;
502 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
503 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
504 l_sr_rec.current_serial_number := l_rec.current_serial_number;
505 l_sr_rec.original_order_number := l_rec.original_order_number;
506 l_sr_rec.problem_code := l_rec.problem_code;
507 l_sr_rec.exp_resolution_date := l_rec.expected_resolution_date;
508 l_sr_rec.install_site_use_id := l_rec.install_site_use_id;
509 l_sr_rec.request_attribute_1 := l_rec.incident_attribute_1;
510 l_sr_rec.request_attribute_2 := l_rec.incident_attribute_2;
511 l_sr_rec.request_attribute_3 := l_rec.incident_attribute_3;
512 l_sr_rec.request_attribute_4 := l_rec.incident_attribute_4;
513 l_sr_rec.request_attribute_5 := l_rec.incident_attribute_5;
514 l_sr_rec.request_attribute_6 := l_rec.incident_attribute_6;
515 l_sr_rec.request_attribute_7 := l_rec.incident_attribute_7;
516 l_sr_rec.request_attribute_8 := l_rec.incident_attribute_8;
517 l_sr_rec.request_attribute_9 := l_rec.incident_attribute_9;
518 l_sr_rec.request_attribute_10 := l_rec.incident_attribute_10;
519 l_sr_rec.request_attribute_11 := l_rec.incident_attribute_11;
520 l_sr_rec.request_attribute_12 := l_rec.incident_attribute_12;
521 l_sr_rec.request_attribute_13 := l_rec.incident_attribute_13;
522 l_sr_rec.request_attribute_14 := l_rec.incident_attribute_14;
523 l_sr_rec.request_attribute_15 := l_rec.incident_attribute_15;
524 l_sr_rec.external_attribute_1 := l_rec.external_attribute_1;
525 l_sr_rec.external_attribute_2 := l_rec.external_attribute_2;
526 l_sr_rec.external_attribute_3 := l_rec.external_attribute_3;
527 l_sr_rec.external_attribute_4 := l_rec.external_attribute_4;
528 l_sr_rec.external_attribute_5 := l_rec.external_attribute_5;
529 l_sr_rec.external_attribute_6 := l_rec.external_attribute_6;
530 l_sr_rec.external_attribute_7 := l_rec.external_attribute_7;
531 l_sr_rec.external_attribute_8 := l_rec.external_attribute_8;
532 l_sr_rec.external_attribute_9 := l_rec.external_attribute_9;
533 l_sr_rec.external_attribute_10 := l_rec.external_attribute_10;
534 l_sr_rec.external_attribute_11 := l_rec.external_attribute_11;
535 l_sr_rec.external_attribute_12 := l_rec.external_attribute_12;
536 l_sr_rec.external_attribute_13 := l_rec.external_attribute_13;
537 l_sr_rec.external_attribute_14 := l_rec.external_attribute_14;
538 l_sr_rec.external_attribute_15 := l_rec.external_attribute_15;
539 l_sr_rec.external_context := l_rec.external_context;
540 l_sr_rec.bill_to_site_use_id := l_rec.bill_to_site_use_id;
541 l_sr_rec.bill_to_contact_id := l_rec.bill_to_contact_id;
542 l_sr_rec.ship_to_site_use_id := l_rec.ship_to_site_use_id;
543 l_sr_rec.ship_to_contact_id := l_rec.ship_to_contact_id;
544 l_sr_rec.resolution_code := l_rec.resolution_code;
545 l_sr_rec.act_resolution_date := l_rec.actual_resolution_date;
546 l_sr_rec.contract_service_id := l_rec.contract_service_id;
547 l_sr_rec.contract_id := l_rec.contract_id;
548 l_sr_rec.project_number := l_rec.project_number;
549 l_sr_rec.qa_collection_plan_id := l_rec.qa_collection_id;
550 l_sr_rec.account_id := l_rec.account_id;
551 l_sr_rec.resource_type := l_rec.resource_type;
552 l_sr_rec.resource_subtype_id := l_rec.resource_subtype_id;
553 l_sr_rec.sr_creation_channel := l_rec.sr_creation_channel;
554 l_sr_rec.obligation_date := l_rec.obligation_date;
555 l_sr_rec.time_zone_id := l_rec.time_zone_id;
556 l_sr_rec.time_difference := l_rec.time_difference;
557 l_sr_rec.site_id := l_rec.site_id;
558 l_sr_rec.customer_site_id := l_rec.customer_site_id;
559 l_sr_rec.territory_id := l_rec.territory_id;
560 l_sr_rec.cp_revision_id := l_rec.cp_revision_id;
561 l_sr_rec.inv_item_revision := l_rec.inv_item_revision;
562 l_sr_rec.inv_component_id := l_rec.inv_component_id;
563 l_sr_rec.inv_component_version := l_rec.inv_component_version;
564 l_sr_rec.inv_subcomponent_id := l_rec.inv_subcomponent_id;
565 l_sr_rec.inv_subcomponent_version := l_rec.inv_subcomponent_version;
566 l_sr_rec.tier := l_rec.tier;
567 l_sr_rec.tier_version := l_rec.tier_version;
568 l_sr_rec.operating_system := l_rec.operating_system;
569 l_sr_rec.operating_system_version := l_rec.operating_system_version;
570 l_sr_rec.database := l_rec.database;
571 l_sr_rec.cust_pref_lang_id := l_rec.cust_pref_lang_id;
572 l_sr_rec.category_id := l_rec.category_id;
573 l_sr_rec.group_type := l_rec.group_type;
574 l_sr_rec.group_territory_id := l_rec.group_territory_id;
575 l_sr_rec.inv_platform_org_id := l_rec.inv_platform_org_id;
576 l_sr_rec.component_version := l_rec.component_version;
577 l_sr_rec.subcomponent_version := l_rec.subcomponent_version;
578 l_sr_rec.comm_pref_code := l_rec.comm_pref_code;
579 l_sr_rec.cust_pref_lang_code := l_rec.cust_pref_lang_code;
580 l_sr_rec.last_update_channel := l_rec.last_update_channel;
581 l_sr_rec.category_set_id := l_rec.category_set_id;
582 l_sr_rec.external_reference := l_rec.external_reference;
583 l_sr_rec.system_id := l_rec.system_id;
584 l_sr_rec.error_code := l_rec.error_code;
585 l_sr_rec.incident_occurred_date := l_rec.incident_occurred_date;
586 l_sr_rec.incident_resolved_date := l_rec.incident_resolved_date;
587 l_sr_rec.inc_responded_by_date := l_rec.inc_responded_by_date;
588 l_sr_rec.incident_location_id := l_rec.incident_location_id;
589 l_sr_rec.incident_address := l_rec.incident_address;
590 l_sr_rec.incident_city := l_rec.incident_city;
591 l_sr_rec.incident_state := l_rec.incident_state;
592 l_sr_rec.incident_country := l_rec.incident_country;
593 l_sr_rec.incident_province := l_rec.incident_province;
594 l_sr_rec.incident_postal_code := l_rec.incident_postal_code;
595 l_sr_rec.incident_county := l_rec.incident_country;
596 l_sr_rec.cc_number := l_rec.credit_card_number;
597 l_sr_rec.cc_expiration_date := l_rec.credit_card_expiration_date;
598 l_sr_rec.cc_type_code := l_rec.credit_card_type_code;
599 l_sr_rec.cc_first_name := l_rec.credit_card_holder_fname;
600 l_sr_rec.cc_last_name := l_rec.credit_card_holder_lname;
601 l_sr_rec.cc_middle_name := l_rec.credit_card_holder_mname;
602 l_sr_rec.cc_id := l_rec.credit_card_id;
603 l_sr_rec.bill_to_account_id := l_rec.bill_to_account_id;
604 l_sr_rec.ship_to_account_id := l_rec.ship_to_account_id;
605 l_sr_rec.customer_phone_id := l_rec.customer_phone_id;
606 l_sr_rec.customer_email_id := l_rec.customer_email_id;
607 l_sr_rec.creation_program_code := l_rec.creation_program_code;
608 l_sr_rec.last_update_program_code := l_rec.last_update_program_code;
609 l_sr_rec.bill_to_party_id := l_rec.bill_to_party_id;
610 l_sr_rec.ship_to_party_id := l_rec.ship_to_party_id;
611 l_sr_rec.program_id := l_rec.program_id;
612 l_sr_rec.program_application_id := l_rec.program_application_id;
613 l_sr_rec.program_login_id := l_rec.program_login_id;
614 l_sr_rec.bill_to_site_id := l_rec.bill_to_site_id;
615 l_sr_rec.ship_to_site_id := l_rec.ship_to_site_id;
616 l_sr_rec.incident_point_of_interest := l_rec.incident_point_of_interest;
617 l_sr_rec.incident_cross_street := l_rec.incident_cross_street;
618 l_sr_rec.incident_direction_qualifier:= l_rec.incident_direction_qualifier;
619 l_sr_rec.incident_distance_qualifier := l_rec.incident_distance_qualifier;
620 l_sr_rec.incident_distance_qual_uom := l_rec.incident_distance_qual_uom;
621 l_sr_rec.incident_address2 := l_rec.incident_address2;
622 l_sr_rec.incident_address3 := l_rec.incident_address3;
623 l_sr_rec.incident_address4 := l_rec.incident_address4;
624 l_sr_rec.incident_address_style := l_rec.incident_address_style;
625 l_sr_rec.incident_addr_lines_phonetic:= l_rec.incident_addr_lines_phonetic;
626 l_sr_rec.incident_po_box_number := l_rec.incident_po_box_number;
627 l_sr_rec.incident_house_number := l_rec.incident_house_number;
628 l_sr_rec.incident_street_suffix := l_rec.incident_street_suffix;
629 l_sr_rec.incident_street := l_rec.incident_street;
630 l_sr_rec.incident_street_number := l_rec.incident_street_number;
631 l_sr_rec.incident_floor := l_rec.incident_floor;
632 l_sr_rec.incident_suite := l_rec.incident_suite;
633 l_sr_rec.incident_postal_plus4_code := l_rec.incident_postal_plus4_code;
634 l_sr_rec.incident_position := l_rec.incident_position;
635 l_sr_rec.incident_location_directions:= l_rec.incident_location_directions;
636 l_sr_rec.incident_location_description := l_rec.incident_location_description;
637 l_sr_rec.install_site_id := l_rec.install_site_id;
638
639 l_sr_rec2.type_id := l_rec.incident_type_id;
640 l_sr_rec2.status_id := l_rec.incident_status_id;
641 l_sr_rec2.urgency_id := l_rec.incident_urgency_id;
642 l_sr_rec2.severity_id := l_rec.incident_severity_id;
643 l_sr_rec2.obligation_date := l_rec.obligation_date;
644 l_sr_rec2.problem_code := l_rec.problem_code;
645 l_sr_rec2.inventory_item_id := l_rec.inventory_item_id;
646 l_sr_rec2.inventory_org_id := l_rec.inv_organization_id;
647 l_sr_rec2.customer_id := l_rec.customer_id;
648 l_sr_rec2.customer_number := l_rec.customer_number;
649 l_sr_rec2.category_id := l_rec.category_id;
650 l_sr_rec2.category_set_id := l_rec.category_set_id;
651 l_sr_rec2.incident_location_id := l_rec.incident_location_id;
652 l_sr_rec2.incident_location_type := l_rec.incident_location_type;
653 l_sr_rec2.request_date := l_rec.incident_date;
654 l_sr_rec2.type_id := l_rec.incident_type_id;
655 l_sr_rec2.status_id := l_rec.incident_status_id;
656 l_sr_rec2.severity_id := l_rec.incident_severity_id;
657 l_sr_rec2.urgency_id := l_rec.incident_urgency_id;
658 l_sr_rec2.closed_date := l_rec.close_date;
659 l_sr_rec2.owner_id := l_rec.incident_owner_id;
660 l_sr_rec2.owner_group_id := l_rec.owner_group_id;
661 l_sr_rec2.publish_flag := l_rec.publish_flag;
662 l_sr_rec2.caller_type := l_rec.caller_type;
663 l_sr_rec2.customer_id := l_rec.customer_id;
664 l_sr_rec2.customer_number := l_rec.customer_number;
665 l_sr_rec2.employee_id := l_rec.employee_id;
666 l_sr_rec2.customer_product_id := l_rec.customer_product_id;
667 l_sr_rec2.platform_id := l_rec.platform_id;
668 l_sr_rec2.platform_version := l_rec.platform_version;
669 l_sr_rec2.db_version := l_rec.db_version;
670 l_sr_rec2.platform_version_id := l_rec.platform_version_id;
671 l_sr_rec2.cp_component_id := l_rec.cp_component_id;
672 l_sr_rec2.cp_component_version_id := l_rec.cp_component_version_id;
673 l_sr_rec2.cp_subcomponent_id := l_rec.cp_subcomponent_id;
674 l_sr_rec2.cp_subcomponent_version_id := l_rec.cp_subcomponent_version_id;
675 l_sr_rec2.language_id := l_rec.language_id;
676 l_sr_rec2.inventory_item_id := l_rec.inventory_item_id;
677 l_sr_rec2.inventory_org_id := l_rec.inv_organization_id;
678 l_sr_rec2.current_serial_number := l_rec.current_serial_number;
679 l_sr_rec2.original_order_number := l_rec.original_order_number;
680 l_sr_rec2.problem_code := l_rec.problem_code;
681 l_sr_rec2.exp_resolution_date := l_rec.expected_resolution_date;
682 l_sr_rec2.install_site_use_id := l_rec.install_site_use_id;
683 l_sr_rec2.request_attribute_1 := l_rec.incident_attribute_1;
684 l_sr_rec2.request_attribute_2 := l_rec.incident_attribute_2;
685 l_sr_rec2.request_attribute_3 := l_rec.incident_attribute_3;
686 l_sr_rec2.request_attribute_4 := l_rec.incident_attribute_4;
687 l_sr_rec2.request_attribute_5 := l_rec.incident_attribute_5;
688 l_sr_rec2.request_attribute_6 := l_rec.incident_attribute_6;
689 l_sr_rec2.request_attribute_7 := l_rec.incident_attribute_7;
690 l_sr_rec2.request_attribute_8 := l_rec.incident_attribute_8;
691 l_sr_rec2.request_attribute_9 := l_rec.incident_attribute_9;
692 l_sr_rec2.request_attribute_10 := l_rec.incident_attribute_10;
693 l_sr_rec2.request_attribute_11 := l_rec.incident_attribute_11;
694 l_sr_rec2.request_attribute_12 := l_rec.incident_attribute_12;
695 l_sr_rec2.request_attribute_13 := l_rec.incident_attribute_13;
696 l_sr_rec2.request_attribute_14 := l_rec.incident_attribute_14;
697 l_sr_rec2.request_attribute_15 := l_rec.incident_attribute_15;
698 l_sr_rec2.external_attribute_1 := l_rec.external_attribute_1;
699 l_sr_rec2.external_attribute_2 := l_rec.external_attribute_2;
700 l_sr_rec2.external_attribute_3 := l_rec.external_attribute_3;
701 l_sr_rec2.external_attribute_4 := l_rec.external_attribute_4;
702 l_sr_rec2.external_attribute_5 := l_rec.external_attribute_5;
703 l_sr_rec2.external_attribute_6 := l_rec.external_attribute_6;
704 l_sr_rec2.external_attribute_7 := l_rec.external_attribute_7;
705 l_sr_rec2.external_attribute_8 := l_rec.external_attribute_8;
706 l_sr_rec2.external_attribute_9 := l_rec.external_attribute_9;
707 l_sr_rec2.external_attribute_10 := l_rec.external_attribute_10;
708 l_sr_rec2.external_attribute_11 := l_rec.external_attribute_11;
709 l_sr_rec2.external_attribute_12 := l_rec.external_attribute_12;
710 l_sr_rec2.external_attribute_13 := l_rec.external_attribute_13;
711 l_sr_rec2.external_attribute_14 := l_rec.external_attribute_14;
712 l_sr_rec2.external_attribute_15 := l_rec.external_attribute_15;
713 l_sr_rec2.external_context := l_rec.external_context;
714 l_sr_rec2.bill_to_site_use_id := l_rec.bill_to_site_use_id;
715 l_sr_rec2.bill_to_contact_id := l_rec.bill_to_contact_id;
716 l_sr_rec2.ship_to_site_use_id := l_rec.ship_to_site_use_id;
717 l_sr_rec2.ship_to_contact_id := l_rec.ship_to_contact_id;
718 l_sr_rec2.resolution_code := l_rec.resolution_code;
719 l_sr_rec2.act_resolution_date := l_rec.actual_resolution_date;
720 l_sr_rec2.contract_service_id := l_rec.contract_service_id;
721 l_sr_rec2.contract_id := l_rec.contract_id;
722 l_sr_rec2.project_number := l_rec.project_number;
723 l_sr_rec2.qa_collection_plan_id := l_rec.qa_collection_id;
724 l_sr_rec2.account_id := l_rec.account_id;
725 l_sr_rec2.resource_type := l_rec.resource_type;
726 l_sr_rec2.resource_subtype_id := l_rec.resource_subtype_id;
727 l_sr_rec2.sr_creation_channel := l_rec.sr_creation_channel;
728 l_sr_rec2.obligation_date := l_rec.obligation_date;
729 l_sr_rec2.time_zone_id := l_rec.time_zone_id;
730 l_sr_rec2.time_difference := l_rec.time_difference;
731 l_sr_rec2.site_id := l_rec.site_id;
732 l_sr_rec2.customer_site_id := l_rec.customer_site_id;
733 l_sr_rec2.territory_id := l_rec.territory_id;
734 l_sr_rec2.cp_revision_id := l_rec.cp_revision_id;
735 l_sr_rec2.inv_item_revision := l_rec.inv_item_revision;
736 l_sr_rec2.inv_component_id := l_rec.inv_component_id;
737 l_sr_rec2.inv_component_version := l_rec.inv_component_version;
738 l_sr_rec2.inv_subcomponent_id := l_rec.inv_subcomponent_id;
739 l_sr_rec2.inv_subcomponent_version := l_rec.inv_subcomponent_version;
740 l_sr_rec2.tier := l_rec.tier;
741 l_sr_rec2.tier_version := l_rec.tier_version;
742 l_sr_rec2.operating_system := l_rec.operating_system;
743 l_sr_rec2.operating_system_version := l_rec.operating_system_version;
744 l_sr_rec2.database := l_rec.database;
745 l_sr_rec2.cust_pref_lang_id := l_rec.cust_pref_lang_id;
746 l_sr_rec2.category_id := l_rec.category_id;
747 l_sr_rec2.group_type := l_rec.group_type;
748 l_sr_rec2.group_territory_id := l_rec.group_territory_id;
749 l_sr_rec2.inv_platform_org_id := l_rec.inv_platform_org_id;
750 l_sr_rec2.component_version := l_rec.component_version;
751 l_sr_rec2.subcomponent_version := l_rec.subcomponent_version;
752 l_sr_rec2.comm_pref_code := l_rec.comm_pref_code;
753 l_sr_rec2.cust_pref_lang_code := l_rec.cust_pref_lang_code;
754 l_sr_rec2.last_update_channel := l_rec.last_update_channel;
755 l_sr_rec2.category_set_id := l_rec.category_set_id;
756 l_sr_rec2.external_reference := l_rec.external_reference;
757 l_sr_rec2.system_id := l_rec.system_id;
758 l_sr_rec2.error_code := l_rec.error_code;
759 l_sr_rec2.incident_occurred_date := l_rec.incident_occurred_date;
760 l_sr_rec2.incident_resolved_date := l_rec.incident_resolved_date;
761 l_sr_rec2.inc_responded_by_date := l_rec.inc_responded_by_date;
762 l_sr_rec2.incident_location_id := l_rec.incident_location_id;
763 l_sr_rec2.incident_address := l_rec.incident_address;
764 l_sr_rec2.incident_city := l_rec.incident_city;
765 l_sr_rec2.incident_state := l_rec.incident_state;
766 l_sr_rec2.incident_country := l_rec.incident_country;
767 l_sr_rec2.incident_province := l_rec.incident_province;
768 l_sr_rec2.incident_postal_code := l_rec.incident_postal_code;
769 l_sr_rec2.incident_county := l_rec.incident_country;
770 l_sr_rec2.cc_number := l_rec.credit_card_number;
771 l_sr_rec2.cc_expiration_date := l_rec.credit_card_expiration_date;
772 l_sr_rec2.cc_type_code := l_rec.credit_card_type_code;
773 l_sr_rec2.cc_first_name := l_rec.credit_card_holder_fname;
774 l_sr_rec2.cc_last_name := l_rec.credit_card_holder_lname;
775 l_sr_rec2.cc_middle_name := l_rec.credit_card_holder_mname;
776 l_sr_rec2.cc_id := l_rec.credit_card_id;
777 l_sr_rec2.bill_to_account_id := l_rec.bill_to_account_id;
778 l_sr_rec2.ship_to_account_id := l_rec.ship_to_account_id;
779 l_sr_rec2.customer_phone_id := l_rec.customer_phone_id;
780 l_sr_rec2.customer_email_id := l_rec.customer_email_id;
781 l_sr_rec2.creation_program_code := l_rec.creation_program_code;
782 l_sr_rec2.last_update_program_code := l_rec.last_update_program_code;
783 l_sr_rec2.bill_to_party_id := l_rec.bill_to_party_id;
784 l_sr_rec2.ship_to_party_id := l_rec.ship_to_party_id;
785 l_sr_rec2.program_id := l_rec.program_id;
786 l_sr_rec2.program_application_id := l_rec.program_application_id;
787 l_sr_rec2.program_login_id := l_rec.program_login_id;
788 l_sr_rec2.bill_to_site_id := l_rec.bill_to_site_id;
789 l_sr_rec2.ship_to_site_id := l_rec.ship_to_site_id;
790 l_sr_rec2.incident_point_of_interest := l_rec.incident_point_of_interest;
791 l_sr_rec2.incident_cross_street := l_rec.incident_cross_street;
792 l_sr_rec2.incident_direction_qualifier:= l_rec.incident_direction_qualifier;
793 l_sr_rec2.incident_distance_qualifier := l_rec.incident_distance_qualifier;
794 l_sr_rec2.incident_distance_qual_uom := l_rec.incident_distance_qual_uom;
795 l_sr_rec2.incident_address2 := l_rec.incident_address2;
796 l_sr_rec2.incident_address3 := l_rec.incident_address3;
797 l_sr_rec2.incident_address4 := l_rec.incident_address4;
798 l_sr_rec2.incident_address_style := l_rec.incident_address_style;
799 l_sr_rec2.incident_addr_lines_phonetic:= l_rec.incident_addr_lines_phonetic;
800 l_sr_rec2.incident_po_box_number := l_rec.incident_po_box_number;
801 l_sr_rec2.incident_house_number := l_rec.incident_house_number;
802 l_sr_rec2.incident_street_suffix := l_rec.incident_street_suffix;
803 l_sr_rec2.incident_street := l_rec.incident_street;
804 l_sr_rec2.incident_street_number := l_rec.incident_street_number;
805 l_sr_rec2.incident_floor := l_rec.incident_floor;
806 l_sr_rec2.incident_suite := l_rec.incident_suite;
807 l_sr_rec2.incident_postal_plus4_code := l_rec.incident_postal_plus4_code;
808 l_sr_rec2.incident_position := l_rec.incident_position;
809 l_sr_rec2.incident_location_directions:= l_rec.incident_location_directions;
810 l_sr_rec2.incident_location_description := l_rec.incident_location_description;
811 l_sr_rec2.install_site_id := l_rec.install_site_id;
812
813 l_incident_number := l_rec.incident_number;
814
815 END LOOP;
816 -- l_sr_rec2 := l_sr_rec;
817
818 IF l_task_prof_value = 'TASK_TMPL' THEN
819
820 CS_AutoGen_Task_PVT.Auto_Generate_Tasks
821 (
822 p_api_version => l_api_version,
823 p_init_msg_list => l_init_msg_list,
824 p_commit => l_commit,
825 p_validation_level => fnd_api.g_valid_level_full,
826 p_incident_id => l_incident_id,
827 p_service_request_rec => l_sr_rec,
828 p_task_template_group_owner => NULL,
829 p_task_tmpl_group_owner_type=> NULL,
830 p_task_template_group_rec => l_task_template_group_rec,
831 p_task_template_table => l_task_template_table,
832 x_auto_task_gen_rec => l_auto_Task_gen_rec,
833 x_return_status => l_return_status,
834 x_msg_count => l_msg_count,
835 x_msg_data => l_msg_data );
836
837 ELSIF l_task_prof_value = 'TASK_CONF' THEN
838 l_counter := 0;
839 FOR l_ea_attr_rec in l_ea_attr_csr LOOP
840 l_counter := l_counter + 1;
841 l_ea_sr_attr_table_type(l_counter).sr_attribute_code := l_ea_attr_rec.sr_attribute_code;
842 l_ea_sr_attr_table_type(l_counter).sr_attribute_value := l_ea_attr_rec.sr_attribute_value;
843 END LOOP;
844 /*
845 OPEN l_ea_attr_csr;
846 FETCH l_ea_attr_csr BULK COLLECT INTO l_ea_sr_attr_table_type;
847 CLOSE l_ea_attr_csr;
848 */
849
850 CS_EA_AutoGen_Tasks_PVT.Create_Extnd_Attr_Tasks
851 (
852 p_api_version => l_api_version,
853 p_init_msg_list => l_init_msg_list,
854 p_commit => l_commit,
855 p_sr_rec => l_sr_rec2,
856 p_sr_attributes_tbl => l_ea_sr_attr_table_type,
857 p_REQUEST_ID => l_incident_id,
858 p_incident_number => l_incident_number,
859 x_return_status => l_return_status,
860 x_msg_count => l_msg_count,
861 x_msg_data => l_msg_data,
862 x_auto_task_gen_attempted => l_auto_task_gen_attemped,
863 x_field_service_Task_created => l_field_service_task_created
864 );
865 END IF;
866
867 IF (l_return_status <> 'S') THEN
868 for i in 1..l_msg_count loop
869 FND_MSG_PUB.Get(p_msg_index=>i,
870 p_encoded=>'F',
871 p_data=>l_msg_data,
872 p_msg_index_out=>l_msg_index_out);
873 l_err := l_err || l_msg_data ;
874 end loop;
875
876 -- l_note_type := fnd_profile.value('CS_SR_TASK_ERROR_NOTE_TYPE');
877 l_note_type := WF_ENGINE.GetItemAttrText(
878 itemtype => itemtype,
879 itemkey => itemkey,
880 aname => 'CS_TASK_FAILURE_NOTE_PROFILE' );
881
882 IF (l_note_type is null) THEN
883 fnd_msg_pub.initialize;
884 fnd_message.set_name ('CS', 'CS_EA_NULL_NOTE_TYPE');
885 fnd_msg_pub.add;
886
887 raise l_api_error;
888 END IF;
889
890 l_return_status := NULL;
891 l_login_id := fnd_global.login_id;
892 l_user_id := fnd_global.user_id ;
893
894 jtf_notes_pub.create_note
895 (
896 p_parent_note_id => l_parent_noted_id,
897 p_jtf_note_id => l_jtf_note_id,
898 p_api_version => 1,
899 p_init_msg_list => l_init_msg_list,
900 p_commit => l_commit,
901 p_validation_level => fnd_api.g_valid_level_full,
902 x_return_status => l_return_status,
903 x_msg_count => l_msg_count,
904 x_msg_data => l_msg_data,
905 p_entered_by => l_user_id,
906 p_entered_date => sysdate,
907 p_last_update_date => sysdate,
908 p_last_updated_by => l_user_id,
909 p_creation_date => sysdate,
910 p_created_by => l_user_id,
911 p_last_update_login => l_login_id,
912 p_source_object_id => l_incident_id,
913 p_source_object_code => 'SR',
914 p_notes => l_err,
915 p_notes_detail => l_err,
916 p_note_type => l_note_type,
917 p_note_status => 'P',
918 x_jtf_note_id => l_note_id
919 );
920 END IF;
921 END IF;
922
923 EXCEPTION
924 WHEN l_API_ERROR THEN
925 l_errmsg_name := NULL;
926 for i in 1..l_msg_count loop
927 FND_MSG_PUB.Get(p_msg_index=>i,
928 p_encoded=>'F',
929 p_data=>l_msg_data,
930 p_msg_index_out=>l_msg_index_out);
931 l_errmsg_name := l_errmsg_name || l_msg_data ;
932 end loop;
933 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Auto_Task_Create - '|| l_errmsg_name || ' ....(' || l_err || ')....',
934 itemtype, itemkey, actid, funmode);
935 RAISE;
936
937 WHEN OTHERS THEN
938 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Auto_Task_Create - ' || sqlerrm,
939 itemtype, itemkey, actid, funmode);
940 RAISE;
941
942 END Auto_Task_Create;
943
944
945 PROCEDURE Setup_Notify_Name
946 (
947 itemtype VARCHAR2,
948 itemkey VARCHAR2,
949 actid NUMBER,
950 funmode VARCHAR2,
951 result OUT NOCOPY VARCHAR2
952 )
953 IS
954
955 l_return_status VARCHAR2(1);
956 l_msg_count NUMBER;
957 l_msg_data VARCHAR2(2000);
958 l_incident_id NUMBER;
959 l_dup_found_at VARCHAR2(10);
960
961 l_owner_role VARCHAR2(100);
962 l_owner_name VARCHAR2(240);
963
964 l_errmsg_name VARCHAR2(30);
965 l_API_ERROR EXCEPTION;
966
967 CURSOR l_ServiceRequest_csr IS
968 SELECT emp.source_id incident_owner_id
969 FROM cs_incidents_all_vl inc ,jtf_rs_resource_extns emp
970 WHERE inc.INCIDENT_OWNER_ID = emp.resource_id AND
971 incident_id = l_incident_id;
972
973 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
974
975 CURSOR l_EA_Recipient_notify_csr IS
976 SELECT emp.source_id incident_owner_id
977 FROM cug_sr_type_dup_chk_info maps, cs_incidents_all_b sr, jtf_rs_resource_extns emp
978 WHERE maps.incident_type_id = sr.incident_type_id
979 AND maps.resource_id = emp.resource_id
980 AND sr.incident_id = l_incident_id;
981
982 l_EA_Recipient_Notify_rec l_EA_Recipient_Notify_csr%ROWTYPE;
983
984 BEGIN
985
986 IF (funmode = 'RUN') THEN
987
988 l_incident_id := WF_ENGINE.GetItemAttrNumber(
989 itemtype => itemtype,
990 itemkey => itemkey,
991 aname => 'REQUEST_ID' );
992
993 l_dup_found_at := WF_ENGINE.GetItemAttrText(
994 itemtype => itemtype,
995 itemkey => itemkey,
996 aname => 'CS_DUPLICATE_FOUND_AT' );
997
998 IF l_dup_found_at = 'BOTH' or l_dup_found_at = 'SR' THEN
999
1000 OPEN l_ServiceRequest_csr;
1001 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
1002
1003 IF (l_ServiceRequest_rec.incident_owner_id is not null) THEN
1004 -- Retrieve the role name for the request owner
1005 CS_WORKFLOW_PUB.Get_Employee_Role (
1006 p_api_version => 1.0,
1007 p_return_status => l_return_status,
1008 p_msg_count => l_msg_count,
1009 p_msg_data => l_msg_data,
1010 p_employee_id => l_ServiceRequest_rec.incident_owner_id,
1011 p_role_name => l_owner_role,
1012 p_role_display_name => l_owner_name );
1013
1014 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_owner_role is NULL) THEN
1015 wf_core.context(
1016 pkg_name => 'CS_WORKFLOW_PUB',
1017 proc_name => 'Get_Employee_Role',
1018 arg1 => 'p_employee_id=>'||to_char(l_ServiceRequest_rec.incident_owner_id));
1019 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
1020 raise l_API_ERROR;
1021 END IF;
1022
1023 WF_ENGINE.SetItemAttrText(
1024 itemtype => 'SERVEREQ',
1025 itemkey => itemkey,
1026 aname => 'OWNER_NAME',
1027 avalue => l_owner_name );
1028
1029 WF_ENGINE.SetItemAttrText(
1030 itemtype => 'SERVEREQ',
1031 itemkey => itemkey,
1032 aname => 'OWNER_ROLE',
1033 avalue => l_owner_role );
1034 END IF;
1035 CLOSE l_ServiceRequest_csr;
1036 END IF;
1037
1038 IF l_dup_found_at = 'BOTH' or l_dup_found_at = 'EA' THEN
1039 OPEN l_EA_Recipient_Notify_csr;
1040 FETCH l_EA_Recipient_Notify_csr INTO l_EA_Recipient_Notify_rec;
1041
1042 IF (l_EA_Recipient_Notify_rec.incident_owner_id is not null) THEN
1043 -- Retrieve the role name for the request owner
1044 CS_WORKFLOW_PUB.Get_Employee_Role (
1045 p_api_version => 1.0,
1046 p_return_status => l_return_status,
1047 p_msg_count => l_msg_count,
1048 p_msg_data => l_msg_data,
1049 p_employee_id => l_EA_Recipient_Notify_rec.incident_owner_id,
1050 p_role_name => l_owner_role,
1051 p_role_display_name => l_owner_name );
1052
1053 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_owner_role is NULL) THEN
1054 wf_core.context(
1055 pkg_name => 'CS_WORKFLOW_PUB',
1056 proc_name => 'Get_Employee_Role',
1057 arg1 => 'p_employee_id=>'||to_char(l_EA_Recipient_Notify_rec.incident_owner_id));
1058 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
1059 raise l_API_ERROR;
1060 END IF;
1061
1062 WF_ENGINE.SetItemAttrText(
1063 itemtype => 'SERVEREQ',
1064 itemkey => itemkey,
1065 aname => 'CS_EA_SR_OWNER_NOTIFY',
1066 avalue => l_owner_role );
1067
1068 /*
1069 WF_ENGINE.SetItemAttrText(
1070 itemtype => 'SERVEREQ',
1071 itemkey => itemkey,
1072 aname => 'CS_EA_SR_OWNER_NOTIFY',
1073 avalue => l_owner_name );
1074 */
1075 END IF;
1076 CLOSE l_EA_Recipient_Notify_csr;
1077 END IF;
1078
1079 IF l_dup_found_at = 'BOTH' THEN
1080 result := 'CS_BOTH';
1081 ELSIF l_dup_found_at = 'SR' THEN
1082 result := 'CS_SR';
1083 ELSIF l_dup_found_at = 'EA' THEN
1084 result := 'CS_EA';
1085 END IF;
1086
1087 END IF;
1088
1089 EXCEPTION
1090 WHEN l_API_ERROR THEN
1091 WF_CORE.Raise(l_errmsg_name);
1092
1093 WHEN OTHERS THEN
1094 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Setup_Notify_Name',
1095 itemtype, itemkey, actid, funmode);
1096 RAISE;
1097
1098 END Setup_Notify_Name;
1099
1100
1101 PROCEDURE Check_SR_Owner_To_Notify
1102 (
1103 itemtype VARCHAR2,
1104 itemkey VARCHAR2,
1105 actid NUMBER,
1106 funmode VARCHAR2,
1107 result OUT NOCOPY VARCHAR2
1108 )
1109 IS
1110 l_incident_id NUMBER;
1111
1112 CURSOR l_ServiceRequest_csr IS
1113 SELECT incident_owner_id
1114 FROM cs_incidents_all_b inc
1115 WHERE incident_id = l_incident_id;
1116
1117 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
1118
1119 BEGIN
1120
1121 IF (funmode = 'RUN') THEN
1122
1123 l_incident_id := WF_ENGINE.GetItemAttrNumber(
1124 itemtype => itemtype,
1125 itemkey => itemkey,
1126 aname => 'REQUEST_ID' );
1127
1128 OPEN l_ServiceRequest_csr;
1129 FETCH l_ServiceRequest_csr into l_ServiceRequest_rec;
1130
1131 IF l_ServiceRequest_rec.incident_owner_id IS NULL THEN
1132 result := 'N';
1133 ELSE
1134 result := 'Y';
1135 END IF;
1136 CLOSE l_ServiceRequest_csr;
1137 END IF;
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 WF_CORE.Context('CS_SR_WF_DUP_CHK_PVT', 'Setup_Notify_Name',
1141 itemtype, itemkey, actid, funmode);
1142 RAISE;
1143
1144 END Check_SR_Owner_To_Notify;
1145
1146 END CS_SR_WF_DUP_CHK_PVT;