[Home] [Help]
PACKAGE BODY: APPS.CS_CIC_ATTRIBUTE_PUB
Source
1 PACKAGE BODY CS_CIC_ATTRIBUTE_PUB AS
2 /* $Header: cscicatb.pls 120.12.12020000.3 2013/03/01 20:04:26 spamujul ship $ */
3
4
5 PROCEDURE CREATE_CIC_ATTRIBUTE_NOTE
6 (
7 p_incident_id IN NUMBER,
8 p_attribute_note IN CLOB,
9 p_address_note IN VARCHAR2,
10 p_created_by IN NUMBER,
11 p_last_update_login IN NUMBER
12 )
13 IS
14 l_return_status VARCHAR2(30);
15 l_msg_data VARCHAR2(240);
16 l_msg_count NUMBER;
17 l_note_id NUMBER;
18 l_note_date DATE;
19 l_attribute_note_summary VARCHAR2(2000);
20 l_note_status VARCHAR2(100);
21 l_note_substr VARCHAR2(2010);
22 l_note_context_tab_dflt JTF_NOTES_PUB.jtf_note_contexts_tbl_type;
23 BEGIN
24 SELECT SYSDATE
25 INTO l_note_date
26 FROM sys.dual;
27
28 FND_PROFILE.Get('CS_EXT_ATTR_NOTE_STATUS', l_note_status);
29
30 IF (p_address_note IS NOT NULL) THEN
31 --- Address Notes ---
32 JTF_NOTES_PUB.Create_note ( p_api_version => 1.0 ,
33 p_init_msg_list => 'T',
34 p_commit => 'T',
35 p_validation_level => csc_core_utils_pvt.g_valid_level_none,
36 x_return_status => l_return_status ,
37 x_msg_count => l_msg_count,
38 x_msg_data => l_msg_data ,
39 p_source_object_id => p_incident_id,
40 p_source_object_code => 'SR',
41 p_notes => p_address_note ,
42 p_entered_by => p_created_by,
43 p_entered_date => l_note_date,
44 x_jtf_note_id => l_note_id ,
45 p_last_update_date => l_note_date,
46 p_last_updated_by => p_created_by,
47 p_creation_date => l_note_date,
48 p_created_by => p_created_by,
49 p_last_update_login => p_last_update_login,
50 p_note_type => 'CUG_SR_ATTR_DETAILS',
51 p_note_status => l_note_status,
52 p_jtf_note_contexts_tab => l_note_context_tab_dflt );
53
54 IF ( l_return_status ) <> 'S' THEN
55 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
56 FND_LOG.STRING(log_level => FND_LOG.LEVEL_ERROR,
57 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTRIBUTE_NOTE',
58 message => 'Address Notes Creation Failed with errors: '||l_msg_data);
59 END IF;
60 ELSE
61 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
62 FND_LOG.STRING(log_level => FND_LOG.LEVEL_STATEMENT,
63 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTRIBUTE_NOTE',
64 message => 'Address Notes Created Successfully');
65 END IF;
66 END IF;
67
68 END IF;
69
70 l_note_substr := SUBSTR(p_attribute_note,1,2005);
71
72 IF(to_number(LENGTH(l_note_substr)) > 2000) THEN
73 l_attribute_note_summary := SUBSTR(p_attribute_note,1,2000);
74 ELSE
75 l_attribute_note_summary := p_attribute_note;
76 END IF;
77
78 IF (l_attribute_note_summary IS NOT NULL) THEN
79
80 --- Attribute Notes ---
81 JTF_NOTES_PUB.CREATE_NOTE ( p_api_version => 1.0 ,
82 p_init_msg_list => 'T',
83 p_commit => 'T',
84 p_validation_level => csc_core_utils_pvt.g_valid_level_none,
85 p_source_object_id => p_incident_id,
86 p_source_object_code => 'SR',
87 p_notes => l_attribute_note_summary,
88 p_notes_detail => p_attribute_note,
89 p_entered_by => p_created_by,
90 p_entered_date => l_note_date,
91 x_jtf_note_id => l_note_id ,
92 p_last_update_date => l_note_date,
93 p_last_updated_by => p_created_by,
94 p_creation_date => l_note_date,
95 p_created_by => p_created_by,
96 p_last_update_login => p_last_update_login,
97 p_note_type => 'CUG_SR_ATTR_DETAILS',
98 p_note_status => l_note_status,
99 p_jtf_note_contexts_tab => l_note_context_tab_dflt,
100 x_return_status => l_return_status,
101 x_msg_count => l_msg_count,
102 x_msg_data => l_msg_data );
103
104 IF ( l_return_status ) <> 'S' THEN
105 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
106 FND_LOG.STRING(log_level => FND_LOG.LEVEL_ERROR,
107 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTRIBUTE_NOTE',
108 message => 'Attributes Notes Creation Failed with errors: '||l_msg_data);
109 END IF;
110 ELSE
111 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
112 FND_LOG.STRING(log_level => FND_LOG.LEVEL_STATEMENT,
113 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTRIBUTE_NOTE',
114 message => 'Attributes Notes Created Successfully');
115 END IF;
116 END IF;
117
118 END IF;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
123 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
124 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTRIBUTE_NOTE',
125 message => 'SQL Exception: '||SQLERRM);
126 END IF;
127 END CREATE_CIC_ATTRIBUTE_NOTE;
128
129
130 PROCEDURE CIC_AUTO_GENERATE_TASKS
131 (
132 p_incident_id IN NUMBER,
133 x_return_status OUT NOCOPY VARCHAR2,
134 p_incident_type_id IN NUMBER DEFAULT NULL
135 )
136 IS
137 l_api_version NUMBER := 1.0 ;
138 l_init_msg_list VARCHAR2(2000) := 'T' ;
139 l_commit VARCHAR2(2000) := 'T' ;
140 l_validation_level NUMBER ;
141 l_serv_req_rec cs_servicerequest_pub.service_request_rec_type ;
142 l_task_template_group_rec jtf_task_inst_templates_pub.task_template_group_info ;
143 l_task_template_table jtf_task_inst_templates_pub.task_template_info_tbl ;
144 l_auto_task_gen_rec cs_autogen_task_pvt.auto_task_gen_rec_type ;
145 l_return_status VARCHAR2(2000) ;
146 l_msg_count NUMBER ;
147 l_msg_data VARCHAR2(2000) ;
148 l_msg_index_out NUMBER ;
149 l_ea_task_tbl_type cs_ea_autogen_tasks_pvt.ea_sr_attr_table_type ;
150 l_auto_task_gen_attempted VARCHAR2(1) ;
151 l_field_service_task_created VARCHAR2(1) ;
152 j NUMBER := 0 ;
153 l_task_count NUMBER ;
154 l_topmost_tab_page VARCHAR2(50) ;
155
156 CURSOR get_sr_details IS
157 SELECT *
158 FROM cs_incidents_all_b
159 WHERE incident_id = p_incident_id ;
160
161 CURSOR get_ea_attributes IS
162 SELECT sr_attribute_code,
163 sr_attribute_value
164 FROM cug_incidnt_attr_vals_vl
165 WHERE incident_id = p_incident_id ;
166
167 l_sr_rec get_sr_details%ROWTYPE ;
168 BEGIN
169 -- Set Savepoint for Auto Task
170 SAVEPOINT AUTO_TASK;
171 OPEN get_sr_details ;
172 FETCH get_sr_details
173 INTO l_sr_rec;
174 IF p_incident_type_id IS NOT NULL THEN
175 l_serv_req_rec.TYPE_ID := p_incident_type_id ;
176 ELSE
177 l_serv_req_rec.TYPE_ID := l_sr_rec.incident_type_id ;
178 END IF;
179 l_serv_req_rec.STATUS_ID := l_sr_rec.incident_status_id ;
180 l_serv_req_rec.SEVERITY_ID := l_sr_rec.incident_severity_id ;
181 l_serv_req_rec.URGENCY_ID := l_sr_rec.incident_urgency_id;
182 l_serv_req_rec.OWNER_ID := l_sr_rec.incident_owner_id;
183 l_serv_req_rec.OWNER_GROUP_ID := l_sr_rec.owner_group_id;
184 l_serv_req_rec.PUBLISH_FLAG := l_sr_rec.publish_flag;
185 l_serv_req_rec.SUMMARY := l_sr_rec.summary;
186 l_serv_req_rec.CALLER_TYPE := l_sr_rec.caller_type;
187 l_serv_req_rec.CUSTOMER_ID := l_sr_rec.customer_id;
188 l_serv_req_rec.CUSTOMER_NUMBER := l_sr_rec.customer_number;
189 l_serv_req_rec.EMPLOYEE_ID := l_sr_rec.employee_id ;
190 l_serv_req_rec.PROBLEM_CODE := l_sr_rec.problem_code;
191 l_serv_req_rec.BILL_TO_SITE_USE_ID := l_sr_rec.bill_to_site_use_id ;
192 l_serv_req_rec.SHIP_TO_SITE_USE_ID := l_sr_rec.bill_to_site_use_id ;
193 l_serv_req_rec.SHIP_TO_CONTACT_ID := l_sr_rec.ship_to_site_use_id ;
194 l_serv_req_rec.RESOLUTION_CODE := l_sr_rec.resolution_code ;
195 l_serv_req_rec.ACT_RESOLUTION_DATE := l_sr_rec.actual_resolution_date ;
196 l_serv_req_rec.CONTRACT_SERVICE_ID := l_sr_rec.contract_service_id ;
197 l_serv_req_rec.CONTRACT_ID := l_sr_rec.contract_id ;
198 l_serv_req_rec.PROJECT_NUMBER := l_sr_rec.project_number ;
199 l_serv_req_rec.ACCOUNT_ID := l_sr_rec.account_id ;
200 l_serv_req_rec.RESOURCE_TYPE := l_sr_rec.resource_type ;
201 l_serv_req_rec.SR_CREATION_CHANNEL := l_sr_rec.sr_creation_channel ;
202 l_serv_req_rec.OBLIGATION_DATE := l_sr_rec.obligation_date ;
203 l_serv_req_rec.SITE_ID := l_sr_rec.site_id ;
204 l_serv_req_rec.CUSTOMER_SITE_ID := l_sr_rec.customer_site_id ;
205 l_serv_req_rec.TERRITORY_ID := l_sr_rec.territory_id ;
206 l_serv_req_rec.CATEGORY_ID := l_sr_rec.category_id ;
207 l_serv_req_rec.GROUP_TYPE := l_sr_rec.group_type ;
208 l_serv_req_rec.GROUP_TERRITORY_ID := l_sr_rec.group_territory_id ;
209 l_serv_req_rec.CATEGORY_SET_ID := l_sr_rec.category_set_id ;
210 l_serv_req_rec.INCIDENT_LOCATION_ID := l_sr_rec.incident_location_id ;
211 l_serv_req_rec.INCIDENT_ADDRESS := l_sr_rec.incident_address ;
212 l_serv_req_rec.INCIDENT_CITY := l_sr_rec.incident_city ;
216 l_serv_req_rec.INCIDENT_POSTAL_CODE := l_sr_rec.incident_postal_code ;
213 l_serv_req_rec.INCIDENT_STATE := l_sr_rec.incident_state ;
214 l_serv_req_rec.INCIDENT_COUNTRY := l_sr_rec.incident_country ;
215 l_serv_req_rec.INCIDENT_PROVINCE := l_sr_rec.incident_province ;
217 l_serv_req_rec.INCIDENT_COUNTY := l_sr_rec.incident_county ;
218 l_serv_req_rec.BILL_TO_ACCOUNT_ID := l_sr_rec.bill_to_account_id ;
219 l_serv_req_rec.SHIP_TO_ACCOUNT_ID := l_sr_rec.ship_to_account_id ;
220 l_serv_req_rec.BILL_TO_PARTY_ID := l_sr_rec.bill_to_party_id ;
221 l_serv_req_rec.SHIP_TO_PARTY_ID := l_sr_rec.ship_to_party_id ;
222 l_serv_req_rec.BILL_TO_SITE_ID := l_sr_rec.bill_to_site_id ;
223 l_serv_req_rec.SHIP_TO_SITE_ID := l_sr_rec.ship_to_site_id ;
224 l_serv_req_rec.INCIDENT_LOCATION_TYPE := l_sr_rec.incident_location_type ;
225 l_serv_req_rec.exp_resolution_date := l_sr_rec.expected_resolution_date;
226
227 CLOSE get_sr_details ;
228
229 FOR i IN get_ea_attributes
230 LOOP
231 l_ea_task_tbl_type(j).sr_attribute_code := i.sr_attribute_code ;
232 l_ea_task_tbl_type(j).sr_attribute_value := i.sr_attribute_value ;
233 j := j + 1 ;
234 END LOOP;
235
236 CS_EA_AUTOGEN_TASKS_PVT.CREATE_EXTND_ATTR_TASKS( p_api_version => 1.0 ,
237 p_init_msg_list => 'T' ,
238 p_commit => 'T' ,
239 p_sr_rec => l_serv_req_rec,
240 p_sr_attributes_tbl => l_ea_task_tbl_type,
241 p_request_id => p_incident_id ,
242 p_incident_number => l_sr_rec.incident_number ,
243 x_return_status => l_return_status,
244 x_msg_count => l_msg_count,
245 x_msg_data => l_msg_data,
246 x_auto_task_gen_attempted => l_auto_task_gen_attempted,
247 x_field_service_Task_created => l_field_service_task_created );
248
249 IF ( l_return_status ) <> 'S' THEN
250 ROLLBACK TO AUTO_TASK;
251
252 x_return_status := 'F';
253 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
254 FND_LOG.STRING(log_level => FND_LOG.LEVEL_ERROR,
255 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CIC_AUTO_GENERATE_TASKS',
256 message => 'Auto Task Creation Failed with errors: '||l_msg_data);
257 END IF;
258 ELSE
259 x_return_status := 'S';
260 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
261 FND_LOG.STRING(log_level => FND_LOG.LEVEL_STATEMENT,
262 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CIC_AUTO_GENERATE_TASKS',
263 message => 'Auto Task Creation Successfully');
264 END IF;
265 END IF;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 ROLLBACK TO AUTO_TASK;
270
271 x_return_status := 'F';
272 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
273 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
274 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CIC_AUTO_GENERATE_TASKS',
275 message => 'SQL Exception: '||SQLERRM);
276 END IF;
277
278 END CIC_AUTO_GENERATE_TASKS;
279
280
281 PROCEDURE CREATE_CIC_ATTR_RECORD
282 ( p_incident_type_id IN NUMBER,
283 p_incident_id IN NUMBER,
284 p_override_addr_valid_flag IN VARCHAR2,
285 p_address_summary IN VARCHAR2,
286 p_created_by IN NUMBER,
287 p_last_update_login IN NUMBER,
288 p_cic_attribute_code IN JTF_VARCHAR2_TABLE_100,
289 p_cic_attr_value IN JTF_VARCHAR2_TABLE_2000,
290 p_cic_attr_lookup IN JTF_VARCHAR2_TABLE_100
291 )
292 IS
293 l_create_attribute_note CLOB := NULL;
294 l_create_address_note VARCHAR2(2000) := NULL;
295 l_attr_name VARCHAR2(240);
296 l_attr_value VARCHAR2(1997);
297 l_attr_description VARCHAR2(1997); --Added for Bug 12379451
298 l_old_values VARCHAR2(1997) := NULL;
299 l_new_values VARCHAR2(1997) := NULL;
300 l_old_constant VARCHAR2(100);
301 l_new_constant VARCHAR2(100);
302 l_incident_attr_val_id NUMBER;
303 l_object_version_number NUMBER;
304 l_row_id VARCHAR2(200);
305 l_creation_date DATE;
306 l_created_by NUMBER(15);
307 l_last_updated_by NUMBER(15);
308 l_return_status VARCHAR2(2);
309 l_ATTRIBUTE1 VARCHAR2(150);
310 l_ATTRIBUTE2 VARCHAR2(150);
311 l_ATTRIBUTE3 VARCHAR2(150);
312 l_ATTRIBUTE4 VARCHAR2(150);
313 l_ATTRIBUTE5 VARCHAR2(150);
314 l_ATTRIBUTE6 VARCHAR2(150);
315 l_ATTRIBUTE7 VARCHAR2(150);
316 l_ATTRIBUTE8 VARCHAR2(150);
317 l_ATTRIBUTE9 VARCHAR2(150);
318 l_ATTRIBUTE10 VARCHAR2(150);
319 l_ATTRIBUTE11 VARCHAR2(150);
320 l_ATTRIBUTE12 VARCHAR2(150);
321 l_ATTRIBUTE13 VARCHAR2(150);
325 l_values_exist CHAR(1) :='N';
322 l_ATTRIBUTE14 VARCHAR2(150);
323 l_ATTRIBUTE15 VARCHAR2(150);
324 l_ATTRIBUTE_CATEGORY VARCHAR2(150);
326 BEGIN
327 SELECT Description
328 INTO l_new_constant
329 FROM Fnd_Lookup_values
330 WHERE Lookup_Code = 'CUG_SR_AUTONOTE_NEW'
331 AND Lookup_Type = 'CUG_SR_AUTONOTE_CONSTANT'
332 AND language = userenv('LANG');
333 l_create_attribute_note := l_new_constant || fnd_global.newline();
334 IF (p_address_summary IS NOT NULL) THEN
335 l_create_address_note := 'Incident Address' || fnd_global.newline() || p_address_summary;
336 ELSE
337 l_create_address_note := NULL;
338 END IF;
339 -- Set the WHO column values.
340 SELECT SYSDATE
341 INTO l_creation_date
342 FROM sys.dual;
343
344 l_object_version_number := 1;
345 l_ATTRIBUTE1 := NULL;
346 l_ATTRIBUTE2 := NULL;
347 l_ATTRIBUTE3 := NULL;
348 l_ATTRIBUTE4 := NULL;
349 l_ATTRIBUTE5 := NULL;
350 l_ATTRIBUTE6 := NULL;
351 l_ATTRIBUTE7 := NULL;
352 l_ATTRIBUTE8 := NULL;
353 l_ATTRIBUTE9 := NULL;
354 l_ATTRIBUTE10 := NULL;
355 l_ATTRIBUTE11 := NULL;
356 l_ATTRIBUTE12 := NULL;
357 l_ATTRIBUTE13 := NULL;
358 l_ATTRIBUTE14 := NULL;
359 l_ATTRIBUTE15 := NULL;
360 l_ATTRIBUTE_CATEGORY := NULL;
361 -- Begin fix for 12.2.2 CIC changes
362 FOR i IN p_cic_attr_value.FIRST .. p_cic_attr_value.LAST
363 LOOP
364 IF l_values_exist <> 'Y' THEN
365 IF p_cic_attr_value(i) IS NOT NULL THEN
366 l_values_exist :='Y';
367 END IF;
368 END IF;
369 END LOOP;
370 IF l_values_exist ='Y' THEN
371 -- End fix for 12.2.2 CIC changes
372 FOR i IN p_cic_attribute_code.FIRST .. p_cic_attribute_code.LAST
373 LOOP
374 SELECT Description
375 INTO l_attr_name
376 FROM Fnd_Lookups
377 WHERE Lookup_Code = p_cic_attribute_code(i)
378 AND Lookup_Type = 'CUG_SR_TYPE_ATTRIBUTES';
379
380 IF (p_cic_attr_lookup(i) IS NOT NULL ) THEN
381 BEGIN
382 SELECT Meaning ,description
383 INTO l_attr_value ,l_attr_description
384 FROM Fnd_Lookups
385 WHERE Lookup_Code = p_cic_attr_value(i)
386 AND Lookup_Type = p_cic_attr_lookup(i);
387 EXCEPTION
388 WHEN OTHERS THEN
389 l_attr_value := null; -- Added the code by spamujul for bug 12379451
390 l_attr_description := null; -- Added the code by spamujul for bug 12379451
391 END ;
392
393 ELSE
394 l_attr_value := p_cic_attr_value(i);
395 l_attr_description := p_cic_attr_value(i); -- Added the code by spamujul for bug 12379451
396 END IF;
397
398 l_create_attribute_note := l_create_attribute_note || l_attr_name || ' = ' || l_attr_value || fnd_global.newline();
399
400 -- Generate the New Sequence # to store in INCIDNT_ATTR_VAL_ID.
401 SELECT CUG_INCIDNT_ATTR_VALS_B_S.nextval
402 INTO l_incident_attr_val_id
403 FROM sys.dual;
404
405 --- Create Attributes ---
406 CUG_INCIDNT_ATTR_VALS_PKG.INSERT_ROW( X_ROWID => l_row_id,
407 X_INCIDNT_ATTR_VAL_ID => l_incident_attr_val_id,
408 X_OBJECT_VERSION_NUMBER => l_object_version_number,
409 X_INCIDENT_ID => p_incident_id,
410 X_SR_ATTRIBUTE_CODE => p_cic_attribute_code(i),
411 X_OVERRIDE_ADDR_VALID_FLAG => p_override_addr_valid_flag,
412 X_ATTRIBUTE1 => l_ATTRIBUTE1,
413 X_ATTRIBUTE2 => l_ATTRIBUTE2,
414 X_ATTRIBUTE3 => l_ATTRIBUTE3,
415 X_ATTRIBUTE4 => l_ATTRIBUTE4,
416 X_ATTRIBUTE5 => l_ATTRIBUTE5,
417 X_ATTRIBUTE6 => l_ATTRIBUTE6,
418 X_ATTRIBUTE7 => l_ATTRIBUTE7,
419 X_ATTRIBUTE8 => l_ATTRIBUTE8,
420 X_ATTRIBUTE9 => l_ATTRIBUTE9,
421 X_ATTRIBUTE10 => l_ATTRIBUTE10,
422 X_ATTRIBUTE11 => l_ATTRIBUTE11,
423 X_ATTRIBUTE12 => l_ATTRIBUTE12,
424 X_ATTRIBUTE13 => l_ATTRIBUTE13,
425 X_ATTRIBUTE14 => l_ATTRIBUTE14,
426 X_ATTRIBUTE15 => l_ATTRIBUTE15,
427 X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY,
428 X_SR_ATTRIBUTE_VALUE => l_attr_description, -- p_cic_attr_value(i), -- Added l_attr_description for bug 12379451
429 X_CREATED_BY => p_created_by,
430 X_LAST_UPDATED_BY => p_created_by,
431 X_LAST_UPDATE_LOGIN => p_last_update_login,
432 X_CREATION_DATE => l_creation_date,
433 X_LAST_UPDATE_DATE => l_creation_date );
434
435 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
436 FND_LOG.STRING(log_level => FND_LOG.LEVEL_STATEMENT,
437 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTR_RECORD',
438 message => 'Attribute Code = '||p_cic_attribute_code(i)||'; Value ='||p_cic_attr_value(i));
439 END IF;
440 END LOOP;
441 CREATE_CIC_ATTRIBUTE_NOTE ( p_incident_id,
445 p_last_update_login );
442 l_create_attribute_note,
443 NULL,
444 p_created_by,
446 IF NVL(fnd_profile.value('CS_SR_AUTO_TASK_CREATE'),'XXX') = 'TASK_CONF' THEN
447 -- Create Auto Task for CIC Attributes --
448 CIC_AUTO_GENERATE_TASKS ( p_incident_id,
449 l_return_status,
450 p_incident_type_id);
451 END IF;
452 -- Begin fix for 12.2.2 CIC changes
453 END IF;
454 -- Create Address and Attribute Notes --
455 IF p_address_summary IS NOT NULL THEN
456 CREATE_CIC_ATTRIBUTE_NOTE ( p_incident_id,
457 NULL,
458 l_create_address_note,
459 p_created_by,
460 p_last_update_login );
461 END IF;
462 l_values_exist :='N';
463 -- End fix for 12.2.2 CIC changes
464 EXCEPTION
465 WHEN OTHERS THEN
466 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
467 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
468 module => 'CS.CS_CIC_ATTRIBUTE_PUB.CREATE_CIC_ATTR_RECORD',
469 message => 'SQL Exception: '||SQLERRM);
470 END IF;
471
472 END CREATE_CIC_ATTR_RECORD;
473
474
475
476 PROCEDURE UPDATE_CIC_ATTR_RECORD (
477 p_incident_attr_val_id IN JTF_NUMBER_TABLE,
478 p_incident_type_id IN NUMBER,
479 p_incident_id IN NUMBER,
480 p_updated_by IN NUMBER,
481 p_last_update_login IN NUMBER,
482 p_cic_attribute_code IN JTF_VARCHAR2_TABLE_100,
483 p_cic_attr_value IN JTF_VARCHAR2_TABLE_2000,
484 p_cic_attr_lookup IN JTF_VARCHAR2_TABLE_100,
485 p_override_addr_valid_flag IN VARCHAR2,
486 p_address_summary IN VARCHAR2
487 )
488 IS
489 l_attr_name VARCHAR2(240);
490 l_attr_value VARCHAR2(1997) := NULL;
491 l_old_attr_values VARCHAR2(1997) := NULL;
492 l_old_values VARCHAR2(10000) := NULL;
493 l_new_values VARCHAR2(10000) := NULL;
494 l_update_attribute_note VARCHAR2(20000) := NULL;
495 l_update_note_flag VARCHAR2(1) := 'N';
496 l_override_addr_valid_flag VARCHAR2(1);
497 l_incident_attr_val_id NUMBER;
498 l_object_version_number NUMBER;
499 l_last_update_date DATE;
500 l_row_id VARCHAR2(200);
501 l_ATTRIBUTE1 VARCHAR2(150);
502 l_ATTRIBUTE2 VARCHAR2(150);
503 l_ATTRIBUTE3 VARCHAR2(150);
504 l_ATTRIBUTE4 VARCHAR2(150);
505 l_ATTRIBUTE5 VARCHAR2(150);
506 l_ATTRIBUTE6 VARCHAR2(150);
507 l_ATTRIBUTE7 VARCHAR2(150);
508 l_ATTRIBUTE8 VARCHAR2(150);
509 l_ATTRIBUTE9 VARCHAR2(150);
510 l_ATTRIBUTE10 VARCHAR2(150);
511 l_ATTRIBUTE11 VARCHAR2(150);
512 l_ATTRIBUTE12 VARCHAR2(150);
513 l_ATTRIBUTE13 VARCHAR2(150);
514 l_ATTRIBUTE14 VARCHAR2(150);
515 l_ATTRIBUTE15 VARCHAR2(150);
516 l_ATTRIBUTE_CATEGORY VARCHAR2(150);
517 l_values_exist CHAR(1) :='N'; -- Added by spamujul for 12.2.2 CIC attribute
518 l_value_count NUMBER(10); -- -- Added by spamujul for 12.2.2 CIC attribute
519 BEGIN
520 -- Begin fix for 12.2.2 CIC changes
521 BEGIN
522 SELECT count(1)
523 INTO l_value_count
524 FROM CUG_INCIDNT_ATTR_VALS_B
525 WHERE INCIDENT_ID = p_incident_id;
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 l_values_exist :='N';
529 WHEN OTHERS THEN
530 l_values_exist :='N';
531 END;
532 IF l_value_count = 0 THEN
533 l_values_exist :='N';
534 ELSE
535 l_values_exist :='Y';
536 END IF;
537 IF l_values_exist ='Y' THEN
538 -- End fix for 12.2.2 CIC changes
539 SELECT Description
540 INTO l_old_values
541 FROM Fnd_Lookup_values
542 WHERE Lookup_Code = 'CUG_SR_AUTONOTE_OLD'
543 AND Lookup_Type = 'CUG_SR_AUTONOTE_CONSTANT'
544 AND language = userenv('LANG');
545
546 SELECT Description
547 INTO l_new_values
548 FROM Fnd_Lookup_values
549 WHERE Lookup_Code = 'CUG_SR_AUTONOTE_NEW'
550 AND Lookup_Type = 'CUG_SR_AUTONOTE_CONSTANT'
551 AND language = userenv('LANG');
552
553 -- Set the WHO column values.
554 SELECT SYSDATE
555 INTO l_last_update_date
556 FROM sys.dual;
557
558 l_ATTRIBUTE1 := NULL;
559 l_ATTRIBUTE2 := NULL;
560 l_ATTRIBUTE3 := NULL;
561 l_ATTRIBUTE4 := NULL;
562 l_ATTRIBUTE5 := NULL;
563 l_ATTRIBUTE6 := NULL;
564 l_ATTRIBUTE7 := NULL;
565 l_ATTRIBUTE8 := NULL;
566 l_ATTRIBUTE9 := NULL;
567 l_ATTRIBUTE10 := NULL;
568 l_ATTRIBUTE11 := NULL;
569 l_ATTRIBUTE12 := NULL;
570 l_ATTRIBUTE13 := NULL;
574
571 l_ATTRIBUTE14 := NULL;
572 l_ATTRIBUTE15 := NULL;
573 l_ATTRIBUTE_CATEGORY := NULL;
575 l_new_values := l_new_values || fnd_global.newline();
576 l_old_values := l_old_values || fnd_global.newline();
577
578 IF p_incident_id IS NOT NULL THEN
579
580 FOR i IN p_incident_attr_val_id.FIRST .. p_incident_attr_val_id.LAST
581 LOOP
582 SELECT Description
583 INTO l_attr_name
584 FROM Fnd_Lookups
585 WHERE Lookup_Code = p_cic_attribute_code(i)
586 AND Lookup_Type = 'CUG_SR_TYPE_ATTRIBUTES';
587
588 IF (p_cic_attr_lookup(i) IS NOT NULL) THEN
589 BEGIN
590 SELECT Description
591 INTO l_attr_value
592 FROM Fnd_Lookups
593 WHERE Lookup_Code = p_cic_attr_value(i)
594 AND Lookup_Type = p_cic_attr_lookup(i);
595 EXCEPTION
596 WHEN OTHERS THEN
597 l_attr_value := p_cic_attr_value(i);
598 END ;
599
600 ELSE
601 l_attr_value := p_cic_attr_value(i);
602 END IF;
603
604 -- To retrieve old attribute values --
605 SELECT Object_Version_Number ,
606 Override_Addr_Valid_Flag,
607 Sr_Attribute_Value
608 INTO l_object_version_number ,
609 l_override_addr_valid_flag,
610 l_old_attr_values
611 FROM cug_incidnt_attr_vals_vl
612 WHERE INCIDNT_ATTR_VAL_ID = p_incident_attr_val_id(i);
613
614 --- Update Attribute ---
615 l_old_values := l_old_values || l_attr_name ||'=' || l_old_attr_values || fnd_global.newline() ;
616 l_new_values := l_new_values || l_attr_name ||'=' || l_attr_value || fnd_global.newline() ;
617
618 IF NVL(p_cic_attr_value(i),'XXX') <> NVL(l_old_attr_values,'XXX') THEN
619 l_update_note_flag := 'Y';
620 END IF;
621
622 l_object_version_number := l_object_version_number + 1;
623
624 IF NVL(p_cic_attr_value(i),'XXX') <> NVL(l_old_attr_values,'XXX') THEN
625 CUG_INCIDNT_ATTR_VALS_PKG.UPDATE_ROW( X_INCIDNT_ATTR_VAL_ID => p_incident_attr_val_id(i),
626 X_OBJECT_VERSION_NUMBER => l_object_version_number,
627 X_INCIDENT_ID => p_incident_id,
628 X_SR_ATTRIBUTE_CODE => p_cic_attribute_code(i),
629 X_OVERRIDE_ADDR_VALID_FLAG => l_override_addr_valid_flag,
630 X_ATTRIBUTE1 => l_ATTRIBUTE1,
631 X_ATTRIBUTE2 => l_ATTRIBUTE2,
632 X_ATTRIBUTE3 => l_ATTRIBUTE3,
633 X_ATTRIBUTE4 => l_ATTRIBUTE4,
634 X_ATTRIBUTE5 => l_ATTRIBUTE5,
635 X_ATTRIBUTE6 => l_ATTRIBUTE6,
636 X_ATTRIBUTE7 => l_ATTRIBUTE7,
637 X_ATTRIBUTE8 => l_ATTRIBUTE8,
638 X_ATTRIBUTE9 => l_ATTRIBUTE9,
639 X_ATTRIBUTE10 => l_ATTRIBUTE10,
640 X_ATTRIBUTE11 => l_ATTRIBUTE11,
641 X_ATTRIBUTE12 => l_ATTRIBUTE12,
642 X_ATTRIBUTE13 => l_ATTRIBUTE13,
643 X_ATTRIBUTE14 => l_ATTRIBUTE14,
644 X_ATTRIBUTE15 => l_ATTRIBUTE15,
645 X_ATTRIBUTE_CATEGORY => l_ATTRIBUTE_CATEGORY,
646 X_SR_ATTRIBUTE_VALUE => l_attr_value, -- p_cic_attr_value(i), -- Added l_attr_value for bug 12379451
647 X_LAST_UPDATE_DATE => l_last_update_date,
648 X_LAST_UPDATED_BY => p_updated_by,
649 X_LAST_UPDATE_LOGIN => p_last_update_login);
650
651 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
652 FND_LOG.STRING(log_level => FND_LOG.LEVEL_STATEMENT,
653 module => 'CS.CS_CIC_ATTRIBUTE_PUB.UPDATE_CIC_ATTR_RECORD',
654 message => 'Attribute ID = '||p_incident_attr_val_id(i)||'; Attribute Code = '||p_cic_attribute_code(i)||'; Value ='||p_cic_attr_value(i));
655 END IF;
656
657 END IF;
658 END LOOP;
659
660 l_update_attribute_note := l_new_values || fnd_global.newline() || l_old_values;
661 IF l_update_note_flag <> 'N' THEN
662 -- Create Update Attribute Note --
663 CREATE_CIC_ATTRIBUTE_NOTE ( p_incident_id,
664 l_update_attribute_note,
665 NULL,
666 p_updated_by,
667 p_last_update_login );
668 END IF;
669 END IF;
670 -- Begin fix for 12.2.2 CIC changes
671 ELSE
672 CREATE_CIC_ATTR_RECORD( p_incident_type_id,
673 p_incident_id,
674 p_override_addr_valid_flag,
675 p_address_summary,
676 p_updated_by,
677 p_last_update_login,
678 p_cic_attribute_code,
679 p_cic_attr_value,
680 p_cic_attr_lookup
681 );
682 END IF;
683 -- End fix for 12.2.2 CIC changes
684 EXCEPTION
685 WHEN OTHERS THEN
686 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
687 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
688 module => 'CS.CS_CIC_ATTRIBUTE_PUB.UPDATE_CIC_ATTR_RECORD',
689 message => 'SQL Exception: '||SQLERRM);
690 END IF;
691
692 END UPDATE_CIC_ATTR_RECORD;
693
694
695 PROCEDURE DELETE_CIC_ATTR_RECORD
696 (
697 p_incident_attr_val_id IN JTF_NUMBER_TABLE
698 )
699 IS
700 l_return_status VARCHAR2(30);
701 BEGIN
702 FOR i IN p_incident_attr_val_id.FIRST .. p_incident_attr_val_id.LAST
703 LOOP
704 CUG_INCIDNT_ATTR_VALS_PKG.DELETE_ROW ( p_incident_attr_val_id(i) );
705 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
706 FND_LOG.STRING(log_level => FND_LOG.LEVEL_STATEMENT,
707 module => 'CS.CS_CIC_ATTRIBUTE_PUB.DELETE_CIC_ATTR_RECORD',
708 message => 'Deleted Attribute ID: '||p_incident_attr_val_id(i));
709 END IF;
710 END LOOP;
711 -- commit;
712
713
714 EXCEPTION
715 WHEN OTHERS THEN
716 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
717 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
718 module => 'CS.CS_CIC_ATTRIBUTE_PUB.DELETE_CIC_ATTR_RECORD',
719 message => 'SQL Exception: '||SQLERRM);
720 END IF;
721
722 END DELETE_CIC_ATTR_RECORD;
723
724
725 END CS_CIC_ATTRIBUTE_PUB;