[Home] [Help]
PACKAGE BODY: APPS.CUG_SR_ATTRIBUTE_INTF
Source
1 PACKAGE BODY CUG_SR_ATTRIBUTE_INTF AS
2 /* $Header: CUGATTPB.pls 120.3 2008/02/21 04:27:04 amganapa ship $ */
3
4 PROCEDURE CREATE_ATTR_TEMPLATE (errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY VARCHAR2,
6 p_date IN VARCHAR2)
7 IS
8 /* Cursor to select attribute list for template */
9 Cursor c_sr_attr (p_start_date date) is
10 select inctype.name sr_type
11 , srattr.incident_type_id sr_type_id
12 , lkp.description sr_name
13 , srattr.sr_attribute_code sr_code
14 , srattr.sr_attr_mandatory_flag mandatory_flag
15 , srattr.sr_attribute_list_name sr_attr_list_name
16 , srattr.sr_attr_default_value default_value
17 , srattr.sr_attr_displayed_flag displayed_flag
18 , srattr.start_date_active start_date_active
19 , srattr.end_date_active end_date_active
20 , srattr.last_update_date last_update_date
21 , lkp.start_Date_active lkup_start_Date
22 , lkp.end_date_active lkup_end_Date
23 ,srattr.sr_type_attr_seq_num
24 from cug_sr_type_attr_maps_vl srattr
25 , cs_incident_types_vl inctype
26 , fnd_lookup_values lkp
27 where srattr.incident_type_id = inctype.incident_type_id
28 and srattr.sr_attribute_code = lkp.lookup_code
29 and lkp.lookup_type = 'CUG_SR_TYPE_ATTRIBUTES'
30 and lkp.language = userenv('lang')
31 and (trunc(srattr.last_update_date) >= trunc(p_start_date)
32 or trunc(lkp.last_update_date) >= trunc(p_start_date))
33 -- and trunc(srattr.last_update_date) >= trunc(p_start_date)
34 order by sr_type , srattr.sr_type_attr_seq_num;
35
36 /* Cursor to select attribute ListName */
37 Cursor C_attr_listName (P_attr_code varchar2, p_start_date date) is
38 select lookup_code
39 , meaning
40 from fnd_lookup_values
41 where lookup_code = p_attr_code
42 and trunc(last_update_date) >= trunc(p_start_date);
43
44 /* Cursor to check Existence of template in CS schema) */
45 cursor c_template_exists (p_template_name varchar2) IS
46 select lnk.template_id template_id
47 from cs_tp_template_links lnk
48 , cs_tp_templates_vl tmpl
49 where tmpl.template_id = lnk.template_id
50 and tmpl.name = p_template_name;
51
52 /* Cursor to check Existence of template attributes */
53 cursor c_template_attr_exists (p_templ_id number, p_attr_name varchar2) IS
54 SELECT tmpq.template_id template_id
55 , tmpq.question_id question_id
56 , quest.lookup_id lookup_id
57 , quest.name qname
58 , quest.text qtext
59 , quest.description qdesc
60 FROM cs_tp_template_questions tmpq
61 , cs_tp_questions_vl quest
62 WHERE tmpq.question_id = quest.question_id
63 AND tmpq.template_id = p_templ_id
64 AND quest.name = p_attr_name;
65
66 /* Cursor to query records from FND_LOOKUP table for List_Name*/
67 cursor c_template_choice_lookup (p_lookup_type varchar2) IS
68 SELECT lookup_code lookup_code
69 , meaning meaning
70 , description description
71 , start_date_active start_date_active
72 , end_date_active end_date_active
73 , last_update_date last_update_date
74 FROM fnd_lookup_values
75 WHERE lookup_type = p_lookup_type
76 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
77 and trunc(nvl(end_date_active,sysdate))
78 AND language = userenv('LANG');
79
80 /* Cursor to query record from CS_TP_CHOICES_VL for deleting records from choices */
81 cursor c_tp_choice (p_lookup_id number) IS
82 select choice_id, value, default_choice_flag
83 from cs_tp_choices_vl
84 where lookup_id = p_lookup_id;
85
86 /* Cursor to query record from CS_TP_FREETEXTS for deleting records from freetext */
87 cursor c_tp_freetext (p_lookup_id number) IS
88 select freetext_id, lookup_id
89 from cs_tp_freetexts
90 where lookup_id = p_lookup_id;
91
92 /* Defining local variables*/
93 l_sr_attr c_sr_attr%ROWTYPE;
94 l_attr_listName c_attr_listName%ROWTYPE;
95 l_tmpl_exists c_template_exists%ROWTYPE;
96 l_tmpl_attr_exists c_template_attr_exists%ROWTYPE;
97 l_tmpl_choice_lookup c_template_choice_lookup%ROWTYPE;
98 l_tp_choice c_tp_choice%ROWTYPE;
99 l_tp_freetext c_tp_freetext%ROWTYPE;
100
101 l_attr_update_flag boolean;
102 l_update_flag boolean ;
103 l_tmpl_id number := 0;
104 l_tmpl_quest_id number := 0;
105 l_tmpl_quest_lookup_id number := 0;
106 l_tmpl_quest_choice_id number := 0;
107 l_tmpl_quest_freetext_id number := 0;
108 l_rowid varchar2(30);
109 l_date date;
110
111 /* Defining Record type's for insert/update*/
112
113 l_template_rec cs_tp_templates_pvt.template;
114 l_template_link_list cs_tp_templates_pvt.template_link_list;
115 l_template_question_list cs_tp_questions_pvt.question;
116 l_template_question_choice cs_tp_choices_pvt.choice;
117 --l_template_quest_choice_list cs_tp_choices_pvt.choice_list;
118 l_template_question_freetext cs_tp_choices_pvt.freetext;
119
120
121 BEGIN
122
123 /* Opening the main cursor to fetch new/update sr attribute records to be processed */
124
125 fnd_file.put_line(fnd_file.log,'Start of SR Attribute Interface logic');
126 fnd_file.put_line(fnd_file.log,'Parameter Start Date :' || p_date);
127
128 select to_date(p_date, 'YYYY/MM/DD HH24:MI:SS') INTO l_date from dual;
129
130 fnd_file.put_line(fnd_file.log,'Parameter Start Date :' || to_char(l_date,'DD-MON-YYYY'));
131
132 --FOR l_sr_attr in c_sr_attr LOOP
133 OPEN c_sr_attr (l_date);
134 FETCH c_sr_attr INTO l_sr_attr;
135
136 WHILE (c_sr_attr%FOUND) loop
137
138 fnd_file.put_line(fnd_file.log, '***----------------------- Start of New Record processing ---------------------***');
139 fnd_file.put_line(fnd_file.log,'l_sr_attr loop, SR Type :' || l_sr_attr.sr_type);
140
141 l_update_flag := false;
142 l_attr_update_flag := false;
143
144 l_template_rec.mTemplateName := l_sr_attr.sr_type;
145 l_template_rec.mstartdate := l_sr_attr.start_date_active;
146 l_template_rec.menddate := l_sr_attr.end_date_active;
147 l_template_rec.mdefaultflag := 'F';
148 l_template_rec.mlast_updated_date := l_sr_attr.last_update_date;
149 l_template_rec.mtemplateid := NULL;
150 l_template_link_list(1).mJTF_OBJECT_CODE := 'IBU_TP_SR_TYPE';
151 l_template_link_list(1).mOther_id := l_sr_attr.sr_type_id;
152 l_template_link_list(1).mLast_Updated_Date := l_sr_attr.last_update_date;
153 -- l_template_link_list.lookup_code := NULL;
154 -- l_template_link_list.lookup_type := NULL;
155 -- l_template_rec.short_code := NULL; -- maps to cs_tp_templates_b.attribute1
156 -- l_template_link_list.mlinkid := NULL;
157 -- l_template_link_list.mlinkname := NULL;
158 -- l_template_link_list.mlinkdesc := NULL;
159
160 -- open the cursor of template_exists to verify if template already created
161 open c_template_exists (l_sr_attr.sr_type);
162 fetch c_template_exists into l_tmpl_exists;
163
164 /* Check if the cursor fetched any records */
165 if c_template_exists%NOTFOUND then
166 -- Calling the create template API to create new template record
167 fnd_file.put_line(fnd_file.log,'Template does not exists for SR Type : ' || l_sr_attr.sr_type);
168 fnd_file.put_line(fnd_file.log,'Calling Create Template API, SR Type : ' || l_sr_attr.sr_type);
169 CS_TP_TEMPLATES_PVT.Add_Template
170 (
171 p_api_version_number => l_api_version,
172 p_init_msg_list => l_init_msg_list_true,
173 p_commit => l_init_commit_true,
174 p_one_template => l_template_rec,
175 x_msg_count => x_msg_count,
176 x_msg_data => x_msg_data,
177 x_return_status => x_return_status,
178 x_template_id => l_tmpl_id
179 );
180
181 -- Calling the create template link API to create new template link record
182 fnd_file.put_line(fnd_file.log,'Template does not exists for SR Type : ' || l_sr_attr.sr_type);
183 fnd_file.put_line(fnd_file.log,'Calling Create Template Link API, SR Type : ' || l_sr_attr.sr_type);
184 CS_TP_TEMPLATES_PVT.update_template_links
185 (
186 p_api_version_number => l_api_version,
187 p_init_msg_list => l_init_msg_list_true,
188 p_commit => l_init_commit_true,
189 p_template_id => l_tmpl_id,
190 p_jtf_object_code => l_template_link_list(1).mjtf_object_code,
191 p_template_links => l_template_link_list,
192 x_msg_count => x_msg_count,
193 x_msg_data => x_msg_data,
194 x_return_status => x_return_status
195 );
196 else
197 fnd_file.put_line(fnd_file.log,'Template already defined for SR Type : '|| l_sr_attr.sr_type);
198 l_tmpl_id := l_tmpl_exists.template_id;
199 end if;
200 close c_template_exists;
201
202 fnd_file.put_line(fnd_file.log,'Attribute Code: ' || l_sr_attr.sr_name);
203
204 -- Check for if attribute StartDateActive and EndDateActive has value
205 -- Added the trunc for bug fix 2435523
206
207 -- Start of changes for bug fix 3547950, by aneemuch 02-Apr-2004
208
209 /* if ( (trunc(nvl(l_sr_attr.start_date_active, sysdate)) <= trunc(sysdate))
210 and (trunc(nvl(l_sr_attr.end_date_active, sysdate)) >= trunc(sysdate))
211 and l_sr_attr.displayed_flag = 'Y') then
212 */
213
214 if (( (trunc(nvl(l_sr_attr.start_date_active, sysdate)) <= trunc(sysdate)
215 and trunc(nvl(l_sr_attr.end_date_active, sysdate)) >= trunc(sysdate))
216 and (trunc(nvl(l_sr_attr.lkup_start_date, sysdate)) <= trunc(sysdate)
217 and trunc(nvl(l_sr_attr.lkup_end_date, sysdate)) >= trunc(sysdate)))
218 and l_sr_attr.displayed_flag = 'Y') then
219
220 -- End of changes for bug fix 3547950, by aneemuch 02-Apr-2004
221
222 fnd_file.put_line(fnd_file.log,'Attribute is valid for start and end date, SR Type: '
223 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name
224 || ' And Display_Flag is set to YES ');
225
226 l_template_question_list.mquestionname := l_sr_attr.sr_name;
227
228 if (l_sr_attr.sr_attr_list_name is NULL) then
229 l_template_question_list.manswertype := 'FREETEXT';
230 else
231 l_template_question_list.manswertype := 'CHOICE';
232 end if;
233
234 if (l_sr_attr.mandatory_flag = 'Y') then
235 l_template_question_list.mmandatoryflag := 'T';
236 else
237 l_template_question_list.mmandatoryflag := 'F';
238 end if;
239
240 l_template_question_list.mscoringflag := FND_API.G_FALSE;
241 l_template_question_list.mlast_updated_date := l_sr_attr.last_update_date;
242
243 /* Check if attribute questions Exists */
244 open c_template_attr_exists (l_tmpl_id, l_sr_attr.sr_name);
245 fetch c_template_attr_exists into l_tmpl_attr_exists;
246
247 /* Check if the cursor fetched any records */
248 fnd_file.put_line(fnd_file.log,'Check if attribute already defined in CS table, SR Type: '
249 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
250
251 if c_template_attr_exists%NOTFOUND then
252 fnd_file.put_line(fnd_file.log,'attribute not defined in CS table, SR Type: '
253 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
254
255 -- Calling the create template question API to create new template question record
256 l_attr_update_flag := false;
257 l_template_question_list.mquestionid := NULL;
258 l_template_question_list.mlookupid := NULL;
259
260 fnd_file.put_line(fnd_file.log,'Calling the Add_question API,SR Type: '
261 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
262 CS_TP_QUESTIONS_PVT.Add_Question
263 (
264 p_api_version_number => l_api_version,
265 p_init_msg_list => l_init_msg_list_true,
266 p_commit => l_init_commit_true,
267 p_one_question => l_template_question_list,
268 p_template_id => l_tmpl_id,
269 x_msg_count => x_msg_count,
270 x_msg_data => x_msg_data,
271 x_return_status => x_return_status,
272 x_question_id => l_tmpl_quest_id
273 );
274 else
275 -- Calling the update template question API to update template question record
276 fnd_file.put_line(fnd_file.log,'Attribute defined in CS table, SR Type: '
277 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
278 l_attr_update_flag := true;
279 l_tmpl_quest_id := l_tmpl_attr_exists.question_id;
280 l_template_question_list.mquestionid := l_tmpl_attr_exists.question_id;
281 l_template_question_list.mlookupid := l_tmpl_attr_exists.lookup_id;
282 l_template_question_list.mlast_updated_date := to_char(l_sr_attr.last_update_date, 'MM/DD/YYYY/SSSSS');
283
284 fnd_file.put_line(fnd_file.log,'Calling the Update_question API, SR Type: '
285 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
286
287 CS_TP_QUESTIONS_PVT.Update_Question
288 (
289 p_api_version_number => l_api_version,
290 p_init_msg_list => l_init_msg_list_true,
291 p_commit => l_init_commit_true,
292 p_one_question => l_template_question_list,
293 x_msg_count => x_msg_count,
294 x_msg_data => x_msg_data,
295 x_return_status => x_return_status
296 );
297
298 fnd_file.put_line(fnd_file.log,'Calling CS_TP_LOOKUPS_PKG.UPDATE_ROW API, SR Type: '
299 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
300
301 CS_TP_LOOKUPS_PKG.UPDATE_ROW
302 (
303 X_ROWID => l_rowid,
304 X_LOOKUP_ID => l_template_question_list.mlookupid,
305 X_LOOKUP_TYPE => l_template_question_list.manswertype,
306 X_DEFAULT_VALUE => l_sr_attr.default_value, --Bug 6705077
307 X_CREATION_DATE => sysdate,
308 X_CREATED_BY => FND_GLOBAL.user_id,
309 X_LAST_UPDATE_DATE => sysdate,
310 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
311 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
312 X_START_DATE_ACTIVE => NULL,
313 X_END_DATE_ACTIVE => NULL,
314 X_ATTRIBUTE_CATEGORY => NULL,
315 X_ATTRIBUTE1 => NULL,
316 X_ATTRIBUTE2 => NULL,
317 X_ATTRIBUTE3 => NULL,
318 X_ATTRIBUTE4 => NULL,
319 X_ATTRIBUTE5 => NULL,
320 X_ATTRIBUTE6 => NULL,
321 X_ATTRIBUTE7 => NULL,
322 X_ATTRIBUTE8 => NULL,
323 X_ATTRIBUTE9 => NULL,
324 X_ATTRIBUTE10 => NULL,
325 X_ATTRIBUTE11 => NULL,
326 X_ATTRIBUTE12 => NULL,
327 X_ATTRIBUTE13 => NULL,
328 X_ATTRIBUTE14 => NULL,
329 X_ATTRIBUTE15 => NULL
330 );
331
332 --IF FND_API.To_Boolean( l_init_commit_true ) THEN
333 -- COMMIT WORK;
334 --END IF;
335
336 end if;
337 close c_template_attr_exists;
338
339 /* get the template question lookupid */
340 select l.lookup_id into l_tmpl_quest_lookup_id
341 from cs_tp_questions_vl q
342 , cs_tp_lookups l
343 , cs_tp_template_questions tq
344 where q.lookup_id = l.lookup_id
345 and tq.question_id = q.question_id
346 and tq.template_id = l_tmpl_id
347 and q.question_id = l_tmpl_quest_id;
348
349 fnd_file.put_line(fnd_file.log,'Check if attribute is freetext or LOV, SR Type: '
350 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name ||l_sr_attr.sr_attr_list_name);
351
352 /* Check for if the Attribute has LOV for answer */
353 if l_sr_attr.sr_attr_list_name is NULL then
354 fnd_file.put_line(fnd_file.log, 'attribute is a FREETEXT');
355 -- Means, attribute is a freetext attribute
356
357 /* Check if the attribute was previously defined as LOV */
358 if l_attr_update_flag = TRUE then
359 -- only to do this if the attribute is in update mode
360 -- Call the delete program to delete all the records from the cs_tp_choices_tl
361
362 fnd_file.put_line(fnd_file.log,'Calling the delete Choice API, attribute is freetext and attribute is in updatemode, SR Type: '
363 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
364 open c_tp_choice (l_tmpl_quest_lookup_id);
365 fetch c_tp_choice into l_tp_choice;
366 while (c_tp_choice%FOUND) loop
367 CS_TP_CHOICES_PVT.DELETE_CHOICE
368 (
369 p_api_version_number => l_api_version,
370 p_init_msg_list => l_init_msg_list_true,
371 p_commit => l_init_commit_true,
372 p_choice_id => l_tp_choice.choice_id,
373 x_msg_count => x_msg_count,
374 x_msg_data => x_msg_data,
375 x_return_status => x_return_status
376 );
377 fetch c_tp_choice into l_tp_choice;
378 end loop;
379 close c_tp_choice;
380 end if;
381
382 -- Call the create FreeText API to create a new record
383 l_template_question_freetext.mfreetextsize := 5;
384 l_template_question_freetext.mfreetextdefaulttext := NULL;
385 l_template_question_freetext.mlookupid :=l_tmpl_quest_lookup_id;
386 l_template_question_freetext.mlast_updated_date:= l_sr_attr.last_update_date;
387
388 fnd_file.put_line(fnd_file.log,'Calling the Add_Freetext API, SR Type: '
389 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
390 CS_TP_CHOICES_PVT.ADD_FREETEXT
391 (
392 p_api_version_number => l_api_version,
393 p_init_msg_list => l_init_msg_list_true,
394 p_commit => l_init_commit_true,
395 p_one_freetext => l_template_question_freetext,
396 x_msg_count => x_msg_count,
397 x_msg_data => x_msg_data,
398 x_return_status => x_return_status,
399 x_freetext_id => l_tmpl_quest_freetext_id
400 );
401 else
402 -- Means, attribute is defined as a LOV column
403 fnd_file.put_line(fnd_file.log, 'attribute is a LOV');
404 if l_attr_update_flag = TRUE then
405 -- only to do this if the attribute is in update mode
406 -- Call the delete program to delete all the records from the cs_tp_choices_tl
407 fnd_file.put_line(fnd_file.log,'Calling the delete Choice API, attribute is in update mode, SR Type: '
408 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
409
410 open c_tp_choice (l_tmpl_quest_lookup_id);
411 fetch c_tp_choice into l_tp_choice;
412 while (c_tp_choice%FOUND) loop
413 CS_TP_CHOICES_PVT.DELETE_CHOICE
414 (
415 p_api_version_number => l_api_version,
416 p_init_msg_list => l_init_msg_list_true,
417 p_commit => l_init_commit_true,
418 p_choice_id => l_tp_choice.choice_id,
419 x_msg_count => x_msg_count,
420 x_msg_data => x_msg_data,
421 x_return_status => x_return_status
422 );
423 fetch c_tp_choice into l_tp_choice;
424 end loop;
425 close c_tp_choice;
426
427 -- Also, call delete FreeText API to delete record from freetext table if previously defined as freetext
428 fnd_file.put_line(fnd_file.log,'Calling the delete Free_Text API incase attribute was defined as freetext before, attribute is in update mode, SR Type: '
429 || l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
430 open c_tp_freetext (l_tmpl_quest_lookup_id);
431 fetch c_tp_freetext into l_tp_freetext;
432 while (c_tp_freetext%FOUND) loop
433 CS_TP_FREETEXTS_PKG.DELETE_ROW(l_tp_freetext.freetext_id);
434 fetch c_tp_freetext into l_tp_freetext;
435 end loop;
436 close c_tp_freetext;
437
438 end if;
439
440 -- Call the create question API to create new record in choices table
441
442 OPEN c_template_choice_lookup (l_sr_attr.sr_attr_list_name);
443 FETCH c_template_choice_lookup INTO l_tmpl_choice_lookup;
444 WHILE (c_template_choice_lookup%FOUND) LOOP
445 l_template_question_choice.mchoiceid := NULL;
446 l_template_question_choice.mchoicename := l_tmpl_choice_lookup.description;
447 l_template_question_choice.mlookupid := l_tmpl_quest_lookup_id;
448 l_template_question_choice.mscore := 0;
449 l_template_question_choice.mlast_updated_date := l_tmpl_choice_lookup.last_update_date;
450
451 fnd_file.put_line(fnd_file.log,'Calling the Add_Choice API, SR Type: ' || l_sr_attr.sr_type ||
452 ' Attribute : '|| l_sr_attr.sr_name || ' Choice : ' ||l_tmpl_choice_lookup.meaning ||'- DESC:'||l_tmpl_choice_lookup.description);
453 CS_TP_CHOICES_PVT.ADD_CHOICE
454 (
455 p_api_version_number => l_api_version,
456 p_init_msg_list => l_init_msg_list_true,
457 p_commit => l_init_commit_true,
458 p_one_choice => l_template_question_choice,
459 x_msg_count => x_msg_count,
460 x_msg_data => x_msg_data,
461 x_return_status => x_return_status,
462 x_choice_id => l_tmpl_quest_choice_id
463 );
464
465 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
466
467 FETCH c_template_choice_lookup INTO l_tmpl_choice_lookup;
468
469 end loop;
470 close c_template_choice_lookup;
471 end if;
472 ELSE -- Else condition of StartDateActive and EndDateActive
473 -- Delete records from the CS table for the attribute that has been
474 -- End dated or has been marked for future date or the display flag is set to NO
475 -- Current API's of iSupport does not support end_date update
476
477 -- Start of changed for bug fix 3547950, by aneemuch
478
479 /* fnd_file.put_line(fnd_file.log,'SR Type: ' || l_sr_attr.sr_type ||
480 ' StartDate : ' || l_sr_attr.start_date_active ||
481 ' EndDateActive : ' || l_sr_attr.end_date_active ||
482 ' DisplayedFlag : ' || l_sr_attr.displayed_flag);
483 */
484
485 fnd_file.put_line(fnd_file.log,'SR Type: ' || l_sr_attr.sr_type ||
486 ' StartDate : ' || l_sr_attr.start_date_active ||
487 ' EndDateActive : ' || l_sr_attr.end_date_active ||
488 ' DisplayedFlag : ' || l_sr_attr.displayed_flag ||
489 ' LkupStartDate: '|| l_sr_attr.lkup_start_Date||
490 ' LkupEndDate: ' || l_sr_attr.lkup_end_date);
491
492 -- End of changed for bug fix 3547950, by aneemuch
493
494 open c_template_attr_exists (l_tmpl_id, l_sr_attr.sr_name);
495 fetch c_template_attr_exists into l_tmpl_attr_exists;
496
497 /* Check if the cursor fetched any records */
498 if c_template_attr_exists%FOUND then
499 fnd_file.put_line(fnd_file.log,'Calling Delete Question API for attribute is enddated, SR Type: ' || l_sr_attr.sr_type);
500 cs_tp_questions_pvt.Delete_Question
501 (
502 p_api_version_number => l_api_version,
503 p_init_msg_list => l_init_msg_list_true,
504 p_commit => l_init_commit_true,
505 p_Question_ID => l_tmpl_attr_exists.question_id,
506 p_Template_ID => l_tmpl_attr_exists.template_id,
507 x_msg_count => x_msg_count,
508 x_msg_data => x_msg_data,
509 x_return_status => x_return_status
510 );
511 end if;
512 close c_template_attr_exists;
513 END IF;
514 COMMIT WORK;
515
516 fnd_file.put_line(fnd_file.log, '***-------------------- End of Current Record processing ---------------------***');
517 FETCH c_sr_attr INTO l_sr_attr;
518
519 end loop; --for;
520 retcode := 0;
521
522 EXCEPTION
523 WHEN FND_API.G_EXC_ERROR then
524
525 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
526 retcode :=2;
527
528 WHEN OTHERS THEN
529 Errbuf := fnd_message.get||' '||SQLERRM;
530 Retcode := 2;
531 fnd_file.put_line(fnd_file.log,'others '||sqlerrm||to_char(sqlcode));
532
533 END CREATE_ATTR_TEMPLATE;
534
535
536
537 PROCEDURE Update_Attr_ListName ( errbuf OUT NOCOPY VARCHAR2,
538 retcode OUT NOCOPY VARCHAR2,
539 p_lookup_type IN VARCHAR2)
540 IS
541
542 /* Cursor to query record from cug_sr_type_attr_maps_v table */
543 Cursor c_cug_sr_attr_ListName (p_lookup_type varchar2) is
544 select cs.name tmpl_name
545 , attr.sr_attribute_code attr_code
546 , lkps.lookup_type list_name
547 , lkps1.description sr_name
548 , attr.start_date_Active start_date_active
549 , attr.end_date_active end_date_active
550 from cug_sr_type_attr_maps_vl attr
551 , fnd_lookup_types lkps
552 , fnd_lookups lkps1
553 , cs_incident_types_vl cs
554 where lkps.lookup_type = attr.sr_attribute_list_name
555 and attr.sr_attribute_list_name = p_lookup_type
556 and lkps1.lookup_code = attr.sr_attribute_code
557 and cs.incident_type_id = attr.incident_type_id
558 and (trunc(nvl(attr.end_date_active,sysdate)) >= trunc(sysdate));
559 --and (attr.end_date_active is NULL or attr.end_date_active >= sysdate);
560
561 /* Cursor to query record from fnd_lookup table for a ListName */
562 Cursor c_fnd_lookup (p_lookup_type varchar2) is
563 select lookup_code lookup_code
564 , meaning meaning
565 , description description
566 , lookup_type lookup_type
567 , start_date_active start_date_active
568 , end_date_active end_date_active
569 , last_update_date last_update_date
570 from fnd_lookup_values
571 where trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
572 and trunc(nvl(end_date_active,sysdate))
573 and lookup_type = p_lookup_type
574 and language = userenv('LANG');
575
576 /* Cursor to query record from questions table */
577 Cursor c_tp_question (p_attr_name varchar2, p_tmpl_name varchar2) is
578 select tmpl.name tmpl_name
579 , qvl.name name
580 , qvl.text text
581 , qvl.lookup_id lookup_id
582 , ch.lookup_type choice_id
583 from cs_tp_questions_vl qvl
584 , cs_tp_lookups ch
585 , cs_tp_template_questions tmpl_qa
586 , cs_tp_templates_vl tmpl
587 where qvl.lookup_id = ch.lookup_id
588 and ch.lookup_type = 'CHOICE'
589 and tmpl_qa.question_id = qvl.question_id
590 and tmpl_qa.template_id = tmpl.template_id
591 and qvl.name = p_attr_name
592 and tmpl.name = p_tmpl_name;
593
594 /* Cursor to query records from choices */
595 Cursor c_tp_choice (p_lookup_id number) is
596 select choice_id
597 , lookup_id
598 , value
599 from cs_tp_choices_vl
600 where lookup_id = p_lookup_id;
601
602 /* Define recordtypes for the above cursor */
603 l_cug_sr_attr_ListName c_cug_sr_attr_ListName%ROWTYPE;
604 l_fnd_lookup c_fnd_lookup%ROWTYPE;
605 l_tp_question c_tp_question%ROWTYPE;
606 l_tp_choice c_tp_choice%ROWTYPE;
607
608 l_lookup_id number := 0;
609 l_choice_id number := 0;
610
611 /* Define recordtype for Questions to be created */
612 l_templ_question_list cs_tp_choices_pvt.choice;
613
614 BEGIN
615
616 fnd_file.put_line(fnd_file.log,'Start of program');
617 OPEN c_cug_sr_attr_ListName (P_lookup_type);
618 fetch c_cug_sr_attr_ListName into l_cug_sr_attr_ListName;
619 WHILE (c_cug_sr_attr_ListName%FOUND) LOOP
620
621 fnd_file.put_line(fnd_file.log,'Attribute Name : ' || l_cug_sr_attr_ListName.sr_name ||
622 ' LOV Name : ' || l_cug_sr_attr_ListName.list_name);
623
624 /* open cursor for choices table and call delete api to delete existing record */
625 OPEN c_tp_question (l_cug_sr_attr_ListName.sr_name, l_cug_sr_attr_ListName.tmpl_name);
626 FETCH c_tp_question into l_tp_question;
627 while (c_tp_question%FOUND) loop
628 l_lookup_id := l_tp_question.lookup_id;
629
630 -- call delete choice api to delete records first
631 OPEN c_tp_choice (l_lookup_id);
632 Fetch c_tp_choice into l_tp_choice;
633
634 fnd_file.put_line(fnd_file.log,'Delete Choice before re-creating, '
635 || 'SR Type : ' || l_cug_sr_attr_ListName.tmpl_name
636 || 'Attribute Name : '
637 || l_cug_sr_attr_ListName.sr_name || ' LOV Name : '
638 || l_cug_sr_attr_ListName.list_name);
639
640 while (c_tp_choice%FOUND) loop
641 -- fnd_file.put_line(fnd_file.log,'Delete Choice before re-creating, '
642 -- ||'SR Type : ' || l_cug_sr_attr_ListName.tmpl_name
643 -- ||' Attribute Name : ' || l_cug_sr_attr_ListName.sr_name || ' LOV Name : '
644 -- || l_cug_sr_attr_ListName.list_name
645 -- || ' ChoiceID : ' || l_tp_choice.value);
646
647 CS_TP_CHOICES_PVT.DELETE_CHOICE
648 (
649 p_api_version_number => l_api_version,
650 p_init_msg_list => l_init_msg_list_true,
651 p_commit => l_init_commit_true,
652 p_choice_id => l_tp_choice.choice_id,
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_data,
655 x_return_status => x_return_status
656 );
657
658 fetch c_tp_choice into l_tp_choice;
659 end loop;
660 close c_tp_choice;
661
662 /* open the fnd_lookup cursor to re-create choices */
663 OPEN c_fnd_lookup (p_lookup_type);
664 FETCH c_fnd_lookup into l_fnd_lookup;
665
666 fnd_file.put_line(fnd_file.log,'Re-creating choices, SR Type : ' ||
667 l_cug_sr_attr_ListName.tmpl_name || ' Attribute Name : ' ||
668 l_cug_sr_attr_ListName.sr_name || ' LOV Name : ' || l_cug_sr_attr_ListName.list_name);
669
670 while (c_fnd_lookup%FOUND) loop
671 l_templ_question_list.mchoiceid := NULL;
672 l_templ_question_list.mchoicename := l_fnd_lookup.description;
673 l_templ_question_list.mlookupid := l_lookup_id;
674 l_templ_question_list.mscore := 0;
675 l_templ_question_list.mlast_updated_date := l_fnd_lookup.last_update_date;
676 l_templ_question_list.mdefaultchoiceflag := FND_API.G_FALSE;
677
678 -- fnd_file.put_line(fnd_file.log,'Re-creating choices, Attribute Name : ' ||
679 -- l_cug_sr_attr_ListName.sr_name || ' LOV Name : ' || l_cug_sr_attr_ListName.list_name
680 -- || ' ChoiceName : ' || l_fnd_lookup.description);
681
682 CS_TP_CHOICES_PVT.ADD_CHOICE
683 (
684 p_api_version_number => l_api_version,
685 p_init_msg_list => l_init_msg_list_true,
686 p_commit => l_init_commit_true,
687 p_one_choice => l_templ_question_list,
688 x_msg_count => x_msg_count,
689 x_msg_data => x_msg_data,
690 x_return_status => x_return_status,
691 x_choice_id => l_choice_id
692 );
693
694 FETCH c_fnd_lookup into l_fnd_lookup;
695 end loop;
696 close c_fnd_lookup;
697
698 FETCH c_tp_question into l_tp_question;
699 end loop;
700 close c_tp_question;
701
702 COMMIT WORK;
703
704 FETCH c_cug_sr_attr_ListName into l_cug_sr_attr_ListName;
705 end loop;
706 close c_cug_sr_attr_ListName;
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 Errbuf := fnd_message.get||' '||SQLERRM;
711 Retcode := 2;
712 fnd_file.put_line(fnd_file.log,'others '||sqlerrm||to_char(sqlcode));
713
714 END Update_Attr_ListName;
715
716 END CUG_SR_ATTRIBUTE_INTF;