[Home] [Help]
PACKAGE BODY: APPS.CS_TP_QUESTIONS_PVT
Source
1 package body CS_TP_QUESTIONS_PVT as
2 /* $Header: cstpqsmb.pls 115.15 2002/12/04 03:30:13 wzli noship $ */
3 -- ---------------------------------------------------------
4 -- Define global variables and types
5 -- ---------------------------------------------------------
6 l_default_last_up_date_format CONSTANT VARCHAR2(30) := 'MM/DD/YYYY/SSSSS';
7 G_PKG_NAME CONSTANT VARCHAR2(100) := 'CS_TP_QUESTIONS_PVT';
8
9 DEBUG CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
10 -- ---------------------------------------------------------
11 -- Define private procedures/functions (not in package spec)
12 -- ---------------------------------------------------------
13 function get_user_id return NUMBER
14 as
15 begin
16 return FND_GLOBAL.USER_ID;
17 end get_user_id;
18
19
20 function get_date_format_from_user(p_user_id IN NUMBER) return VARCHAR2
21 as
22 begin
23
24 -- get the default date format for this user
25 return FND_PROFILE.VALUE_SPECIFIC(
26 'ICX_DATE_FORMAT_MASK',
27 p_user_id,
28 null,
29 null);
30 exception
31 when others then
32 return 'MON-DD-YYYY'; -- use this one as default
33 end get_date_format_from_user;
34
35 function get_date_format return VARCHAR2
36 as
37 begin
38 return get_date_format_from_user(get_user_id);
39 end get_date_format;
40
41
42 -- ---------------------------------------------------------
43 -- Define public procedures
44 -- ---------------------------------------------------------
45
46
47 procedure Add_Question (
48 p_api_version_number IN NUMBER,
49 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
50 p_commit IN VARCHAR := FND_API.G_FALSE,
51 P_One_Question IN Question,
52 p_Template_ID IN NUMBER,
53 X_Msg_Count OUT NOCOPY NUMBER,
54 X_Msg_Data OUT NOCOPY VARCHAR2,
55 X_Return_Status OUT NOCOPY VARCHAR2,
56 X_Question_ID OUT NOCOPY NUMBER)
57
58
59 is
60 l_api_name CONSTANT VARCHAR2(30) := 'Add_Question';
61 l_api_version CONSTANT NUMBER := 1.0;
62 l_question_id NUMBER :=FND_API.G_MISS_NUM;
63 l_lookup_id NUMBER :=FND_API.G_MISS_NUM;
64 l_current_date DATE :=FND_API.G_MISS_DATE;
65 l_created_by NUMBER :=FND_API.G_MISS_NUM;
66 l_login NUMBER :=FND_API.G_MISS_NUM;
67 l_ROWID VARCHAR2(30);
68 Cursor C is
69 select max(SEQUENCE_NUMBER) from CS_TP_TEMPLATE_QUESTIONS;
70 l_max_sequence NUMBER;
71
72 begin
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list ) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77
78 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
79
80 -- Start API Body
81
82 -- Perform validation
83
84 IF (P_One_Question.mQuestionName is NULL OR P_One_Question.mQuestionName= FND_API.G_MISS_CHAR) THEN
85 X_Return_Status := FND_API.G_RET_STS_ERROR;
86 FND_MESSAGE.SET_NAME('CS','CS_TP_Question_NAME_INVALID');
87 FND_MSG_PUB.Add;
88 RAISE FND_API.G_EXC_ERROR;
89 END IF;
90
91 IF (P_One_Question.mAnswerType is NULL OR P_One_Question.mAnswerType= FND_API.G_MISS_CHAR OR length(P_One_Question.mAnswerType)<=0)THEN
92 X_Return_Status := FND_API.G_RET_STS_ERROR;
93 FND_MESSAGE.SET_NAME('CS','CS_TP_Question_Answer_INVALID');
94 FND_MSG_PUB.Add;
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 l_current_date := sysdate;
99 l_created_by := FND_GLOBAL.user_id;
100 l_login := fnd_global.login_id;
101
102 --Create Lookup
103 select CS_TP_LOOKUP_S.NEXTVAL into l_lookup_id from dual;
104
105 --Insert a new lookup, note DEFAULT_VALUE, X_START_DATE_ACTIVE, X_END_DATE_ACTIVE are null
106 CS_TP_LOOKUPS_PKG.INSERT_ROW (
107 X_ROWID => l_ROWID,
108 X_LOOKUP_ID => l_lookup_id,
109 X_LOOKUP_TYPE => P_One_Question.mAnswerType,
110 X_DEFAULT_VALUE => NULL,
111 X_CREATION_DATE => l_current_date,
112 X_CREATED_BY => l_created_by,
113 X_LAST_UPDATE_DATE => l_current_date,
114 X_LAST_UPDATED_BY => l_created_by,
115 X_LAST_UPDATE_LOGIN => l_login,
116 X_START_DATE_ACTIVE => NULL,
117 X_END_DATE_ACTIVE => NULL);
118
119
120 --Get the question id from the next available sequence number
121 select CS_TP_QUESTIONS_S.NEXTVAL into l_question_id from dual;
122
123 --insert into question table
124
125 CS_TP_QUESTIONS_PKG.INSERT_ROW (
126 X_ROWID => l_ROWID,
127 X_QUESTION_ID => l_question_id,
128 X_LOOKUP_ID => l_lookup_id,
129 X_MANDTORY_FLAG => P_One_Question.mMandatoryFlag,
130 X_SCORING_FLAG => P_One_Question.mScoringFlag,
131 X_START_DATE_ACTIVE => NULL,
132 X_END_DATE_ACTIVE => NULL,
133 X_NAME => P_One_Question.mQuestionName,
134 -- X_TEXT => P_One_Question.mQuestionName,
135 X_TEXT => 'temp question text',
136 X_DESCRIPTION => NULL,
137 X_CREATION_DATE => l_current_date,
138 X_CREATED_BY => l_created_by,
139 X_LAST_UPDATE_DATE => l_current_date,
140 X_LAST_UPDATED_BY =>l_created_by,
141 X_LAST_UPDATE_LOGIN => l_login,
142 X_NOTE_TYPE => P_One_Question.mNoteType,
143 X_SHOW_ON_CREATION_FLAG => P_One_Question.mShowOnCreationFlag
144 );
145 open C;
146 fetch C into l_max_sequence;
147 if (C%notfound) then
148 l_max_sequence := 0;
149 end if;
150 close C;
151 if (l_max_sequence is NULL) then
152 l_max_sequence := 0;
153 end if;
154
155
156 --insert into the question template linking table
157
158 CS_TP_TEMPLATE_QUESTIONS_PKG.INSERT_ROW (
159 X_ROWID => l_ROWID,
160 X_TEMPLATE_ID => P_Template_ID,
161 X_QUESTION_ID => l_question_id,
162 X_SEQUENCE_NUMBER => l_max_sequence + 1,
163 X_CREATION_DATE => l_current_date,
164 X_CREATED_BY => l_created_by,
165 X_LAST_UPDATE_DATE => l_current_date,
166 X_LAST_UPDATED_BY =>l_created_by,
167 X_LAST_UPDATE_LOGIN => l_login,
168 X_NOTE_TYPE => P_One_Question.mNoteType,
169 X_SHOW_ON_CREATION_FLAG => P_One_Question.mShowOnCreationFlag
170 );
171 X_Question_ID :=l_question_id;
172 -- End of API Body
173
174 -- Standard check of p_commit.
175 IF FND_API.To_Boolean( p_commit ) THEN
176 COMMIT WORK;
177 END IF;
178 -- Standard call to get message count and if count is 1, get message info.
179 FND_MSG_PUB.Count_And_Get
180 (p_count => x_msg_count ,
181 p_data => x_msg_data
182 );
183
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 FND_MSG_PUB.Count_And_Get
187 (p_count => x_msg_count ,
188 p_data => x_msg_data
189 );
190
191
192 WHEN OTHERS THEN
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
194 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
195 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
196 END IF;
197 FND_MSG_PUB.Count_And_Get
198 (p_count => x_msg_count ,
199 p_data => x_msg_data
200 );
201 raise;
202 END Add_Question;
203
204 procedure UPDATE_QUESTION (
205 p_api_version_number IN NUMBER,
206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
207 p_commit IN VARCHAR := FND_API.G_FALSE,
208 P_One_Question IN Question,
209 X_Msg_Count OUT NOCOPY NUMBER,
210 X_Msg_Data OUT NOCOPY VARCHAR2,
211 X_Return_Status OUT NOCOPY VARCHAR2)
212 is
213 l_api_name CONSTANT VARCHAR2(30) := 'Update_Question';
214 l_api_version CONSTANT NUMBER := 1.0;
215
216 l_current_date DATE :=FND_API.G_MISS_DATE;
217 l_last_updated_by VARCHAR2(200) :=FND_API.G_MISS_CHAR;
218 l_login VARCHAR2(200) :=FND_API.G_MISS_CHAR;
219 l_last_updated_date DATE;
220 l_lookup_id NUMBER;
221 cursor c is
222 select last_update_date, LOOKUP_ID from CS_TP_QUESTIONS_B
223 where QUESTION_ID = P_One_Question.mQuestionID;
224
225 begin
226 -- Initialize message list if p_init_msg_list is set to TRUE.
227 IF FND_API.to_Boolean( p_init_msg_list ) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
232
233 -- Start API Body
234
235 -- Perform validation
236
237 IF (P_One_Question.mQuestionName is NULL OR P_One_Question.mQuestionName= FND_API.G_MISS_CHAR) THEN
238 X_Return_Status := FND_API.G_RET_STS_ERROR;
239 FND_MESSAGE.SET_NAME('CS','CS_TP_Question_NAME_INVALID');
240 FND_MSG_PUB.Add;
241 RAISE FND_API.G_EXC_ERROR;
242 END IF;
243
244 IF (P_One_Question.mAnswerType is NULL OR P_One_Question.mAnswerType= FND_API.G_MISS_CHAR OR length(P_One_Question.mAnswerType)<=0)THEN
245 X_Return_Status := FND_API.G_RET_STS_ERROR;
246 FND_MESSAGE.SET_NAME('CS','CS_TP_Question_Answer_INVALID');
247 FND_MSG_PUB.Add;
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250
251 --check to see if the question is modified after the client's query
252 open c;
253 fetch c into l_last_updated_date, l_lookup_id;
254 if (c%notfound) then
255 close c;
256 X_Return_Status := FND_API.G_RET_STS_ERROR;
257 raise no_data_found;
258 end if;
259 close c;
260 -- is the last updated date from db later than the date from client
261 if (l_last_updated_date > TO_DATE (P_One_Question.mLast_Updated_Date, l_default_last_up_date_format )) then
262 X_Return_Status := FND_API.G_RET_STS_ERROR;
263 FND_MESSAGE.SET_NAME('CS','CS_TP_QUESTION_UPDATED');
264 FND_MSG_PUB.Add;
265 RAISE FND_API.G_EXC_ERROR;
266 end if;
267
268 -- Update the question table , note lookup_id is not modified.
269 l_current_date := sysdate;
270 l_last_updated_by := fnd_global.user_id;
271 l_login := fnd_global.login_id;
272
273 CS_TP_QUESTIONS_PKG.UPDATE_ROW (
274 X_QUESTION_ID => P_One_Question.mQuestionID,
275 X_LOOKUP_ID => l_lookup_id,
276 X_MANDTORY_FLAG =>P_One_Question.mMandatoryFlag,
277 X_SCORING_FLAG =>P_One_Question.mScoringFlag,
278 X_START_DATE_ACTIVE => NULL,
279 X_END_DATE_ACTIVE =>NULL,
280 X_NAME =>P_One_Question.mQuestionName,
281 --X_TEXT =>P_One_Question.mQuestionName,
282 X_TEXT =>'temp question text',
283 X_DESCRIPTION => NULL,
284 X_LAST_UPDATE_DATE => l_current_date,
285 X_LAST_UPDATED_BY => l_last_updated_by,
286 X_LAST_UPDATE_LOGIN => l_login,
287 X_NOTE_TYPE => P_One_Question.mNoteType,
288 X_SHOW_ON_CREATION_FLAG => P_One_Question.mShowOnCreationFlag
289
290 );
291 -- End of API Body
292
293 -- Standard check of p_commit.
294 IF FND_API.To_Boolean( p_commit ) THEN
295 COMMIT WORK;
296 END IF;
297 -- Standard call to get message count and if count is 1, get message info.
298 FND_MSG_PUB.Count_And_Get
299 (p_count => x_msg_count ,
300 p_data => x_msg_data
301 );
302
303 EXCEPTION
304 WHEN FND_API.G_EXC_ERROR THEN
305 FND_MSG_PUB.Count_And_Get
306 (p_count => x_msg_count ,
307 p_data => x_msg_data
308 );
309
310
311 WHEN OTHERS THEN
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
313 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
314 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
315 END IF;
316 FND_MSG_PUB.Count_And_Get
317 (p_count => x_msg_count ,
318 p_data => x_msg_data
319 );
320 raise;
321 end UPDATE_QUESTION;
322
323 procedure Delete_Question (
324 p_api_version_number IN NUMBER,
325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
326 p_commit IN VARCHAR := FND_API.G_FALSE,
327 p_Question_ID IN NUMBER,
328 p_Template_ID IN NUMBER,
329 X_Msg_Count OUT NOCOPY NUMBER,
330 X_Msg_Data OUT NOCOPY VARCHAR2,
331 X_Return_Status OUT NOCOPY VARCHAR2)
332
333 is
334 type choice_list is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
335 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Question';
336 l_api_version CONSTANT NUMBER := 1.0;
337
338 l_choice_list choice_list;
339 l_freetext_id NUMBER;
340 l_freetext_num NUMBER;
341 l_lookup_id Number;
342 i NUMBER;
343 Cursor lookup_c is
344 select lookup_id from CS_TP_QUESTIONS_B where question_id = p_Question_ID;
345
346 Cursor
347 freetext_num_c (v_lookup_id NUMBER)
348 is select count (*) from CS_TP_FREETEXTS where LOOKUP_ID = v_lookup_id;
349 Cursor
350 free_text_c (v_lookup_id NUMBER) is
351 select freetext_id from CS_TP_FREETEXTS where LOOKUP_ID = v_lookup_id;
352 Cursor
353 choice_c (v_lookup_id NUMBER) is
354 select choice_id from CS_TP_CHOICES_VL where LOOKUP_ID = v_lookup_id;
355
356
357 begin
358 -- Initialize message list if p_init_msg_list is set to TRUE.
359 IF FND_API.to_Boolean( p_init_msg_list ) THEN
360 FND_MSG_PUB.initialize;
361 END IF;
362
363 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
364
365 -- Start API Body
366 open lookup_c;
367 fetch lookup_c into l_lookup_id;
368 if (lookup_c%notfound) then
369 close lookup_c;
370 raise no_data_found;
371 end if;
372 close lookup_c;
373
374 --delete template question link
375 CS_TP_TEMPLATE_QUESTIONS_PKG.DELETE_ROW (
376 X_TEMPLATE_ID => p_Template_ID,
377 X_QUESTION_ID => p_Question_ID);
378 --delete lookup
379 CS_TP_LOOKUPS_PKG.DELETE_ROW (l_lookup_id);
380 --delete question
381 CS_TP_QUESTIONS_PKG.DELETE_ROW (X_QUESTION_ID => p_Question_ID);
382
383
384
385 --delete freetext or choices
386 open freetext_num_c(l_lookup_id);
387 fetch freetext_num_c into l_freetext_num;
388 close freetext_num_c;
389 if (l_freetext_num=1) then
390 open free_text_c(l_lookup_id);
391 fetch free_text_c into l_freetext_id;
392 close free_text_c;
393 CS_TP_FREETEXTS_PKG.DELETE_ROW (
394 X_FREETEXT_ID => l_freetext_id);
395
396 elsif (l_freetext_num>1) then
397 X_Return_Status := FND_API.G_RET_STS_ERROR;
398 raise FND_API.G_EXC_UNEXPECTED_ERROR;
399 end if;
400
401 --delete choice
402 open choice_c (l_lookup_id);
403 i:=0;
404 loop
405 fetch choice_c into l_choice_list (i);
406 exit when (choice_c%notfound);
407 i:=i+1;
408 end loop;
409 close choice_c;
410
411 if (l_choice_list.COUNT > 0) then
412 for i in l_choice_list.FIRST..l_choice_list.LAST loop
413 CS_TP_CHOICES_PKG.DELETE_ROW (
414 X_CHOICE_ID => l_choice_list (i)
415 );
416 end loop;
417 end if;
418 -- End of API Body
419
420 -- Standard check of p_commit.
421 IF FND_API.To_Boolean( p_commit ) THEN
422 COMMIT WORK;
423 END IF;
424 -- Standard call to get message count and if count is 1, get message info.
425 FND_MSG_PUB.Count_And_Get
426 (p_count => x_msg_count ,
427 p_data => x_msg_data
428 );
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
433 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
434 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
435 END IF;
436 FND_MSG_PUB.Count_And_Get
437 (p_count => x_msg_count ,
438 p_data => x_msg_data
439 );
440 raise;
441
442 end Delete_Question;
443
444
445 procedure Sort_Questions (
446 p_api_version_number IN NUMBER,
447 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
448 p_commit IN VARCHAR := FND_API.G_FALSE,
449 P_Questions IN Question_List,
450 P_Template_ID IN NUMBER,
451 X_Msg_Count OUT NOCOPY NUMBER,
452 X_Msg_Data OUT NOCOPY VARCHAR2,
453 X_Return_Status OUT NOCOPY VARCHAR2
454 )
455
456 is
457 l_api_name CONSTANT VARCHAR2(30) := 'Sort_Questions';
458 l_api_version CONSTANT NUMBER := 1.0;
459 i NUMBER;
460 l_current_date DATE :=FND_API.G_MISS_DATE;
461 l_last_updated_by VARCHAR2(200) :=FND_API.G_MISS_CHAR;
462 l_login VARCHAR2(200) :=FND_API.G_MISS_CHAR;
463 Cursor last_updateC (V_Question_ID Number) is
464 select last_update_date from CS_TP_QUESTIONS_VL where QUESTION_ID = V_Question_ID ;
465 l_last_update_date DATE;
466 begin
467 -- Initialize message list if p_init_msg_list is set to TRUE.
468 IF FND_API.to_Boolean( p_init_msg_list ) THEN
469 FND_MSG_PUB.initialize;
470 END IF;
471
472 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
473
474 -- Start API Body
475 l_current_date := sysdate;
476 l_last_updated_by := fnd_global.user_id;
477 l_login := fnd_global.login_id;
478
479 if (P_Questions.COUNT>0) then
480 for i in P_Questions.FIRST..P_Questions.LAST loop
481 open last_updateC (P_Questions(i).mQuestionID);
482 fetch last_updateC into l_last_update_date;
483 if (last_updateC%notfound) then
484 close last_updateC;
485 raise no_data_found;
486 end if;
487 close last_updateC;
488 if (to_date( P_Questions(i).mLast_Updated_date, l_default_last_up_date_format) < l_last_update_date) then
489 X_Return_Status := FND_API.G_RET_STS_ERROR;
490 FND_MESSAGE.SET_NAME('CS','CS_TP_QUESTION_UPDATED');
491 FND_MSG_PUB.Add;
492 RAISE FND_API.G_EXC_ERROR;
493 end if;
494
495 CS_TP_TEMPLATE_QUESTIONS_PKG.UPDATE_ROW (
496 X_TEMPLATE_ID => P_Template_ID,
497 X_QUESTION_ID => P_Questions (i).mQuestionID,
498 X_NOTE_TYPE => P_Questions (i).mNoteType,
499 X_SHOW_ON_CREATION_FLAG =>
500 P_Questions (i).mShowOnCreationFlag,
501 X_SEQUENCE_NUMBER => i,
502 X_LAST_UPDATE_DATE => l_current_date,
503 X_LAST_UPDATED_BY => l_last_updated_by,
504 X_LAST_UPDATE_LOGIN => l_login );
505 end loop;
506 end if;
507
508 -- Standard check of p_commit.
509 IF FND_API.To_Boolean( p_commit ) THEN
510 COMMIT WORK;
511 END IF;
512 -- Standard call to get message count and if count is 1, get message info.
513 FND_MSG_PUB.Count_And_Get
514 (p_count => x_msg_count ,
515 p_data => x_msg_data
516 );
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 FND_MSG_PUB.Count_And_Get
521 (p_count => x_msg_count ,
522 p_data => x_msg_data
523 );
524
525
526 WHEN OTHERS THEN
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
529 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
530 END IF;
531 FND_MSG_PUB.Count_And_Get
532 (p_count => x_msg_count ,
533 p_data => x_msg_data
534 );
535 raise;
536 end SORT_QUESTIONS;
537
538 procedure Show_Questions (
539 p_api_version_number IN NUMBER,
540 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
541 p_commit IN VARCHAR := FND_API.G_FALSE,
542 p_Template_ID IN NUMBER,
543 P_Start_Question IN NUMBER,
544 P_End_Question IN NUMBER,
545 P_Display_Order IN VARCHAR2,
546 X_Msg_Count OUT NOCOPY NUMBER,
547 X_Msg_Data OUT NOCOPY VARCHAR2,
548 X_Return_Status OUT NOCOPY VARCHAR2,
549 X_Question_List_To_Show OUT NOCOPY Question_List,
550 X_Total_Questions OUT NOCOPY NUMBER,
551 X_Retrieved_Question_Number OUT NOCOPY NUMBER
552 )
553 is
554 l_api_name CONSTANT VARCHAR2(30) := 'Show_Questions';
555 l_api_version CONSTANT NUMBER := 1.0;
556 l_statement VARCHAR2(1000);
557 L_QUESTION_ID NUMBER;
558 L_QUESTION_NAME VARCHAR2(2000);
559 L_ANSWER_TYPE VARCHAR2(60);
560 L_MANDATORY_FLAG VARCHAR2(60);
561 L_SCORING_FLAG VARCHAR2(60);
562 L_LOOKUP_ID NUMBER;
563 L_LAST_UPDATED_DATE DATE;
564 l_CursorID INTEGER;
565 i NUMBER;
566 j NUMBER;
567 L_Start_Question NUMBER;
568 L_End_Question NUMBER;
569 L_Total_Questions_NotUsed NUMBER;
570
571 L_NOTE_TYPE VARCHAR2(30);
572 L_SHOW_ON_CREATION_FLAG VARCHAR2(1);
573 begin
574 -- Initialize message list if p_init_msg_list is set to TRUE.
575 IF FND_API.to_Boolean( p_init_msg_list ) THEN
576 FND_MSG_PUB.initialize;
577 END IF;
578
579 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
580
581 -- Start API Body
582
583 L_Start_Question := P_Start_Question;
584 L_End_Question := P_End_Question;
585 -- Check for null L_Start_Question and P_End_Question
586 if (L_Start_Question is NULL or L_Start_Question = FND_API.G_MISS_NUM) then
587 L_Start_Question :=1;
588 end if;
589 --If L_End_Question is NULL, set it to G_MISS_NUM which should be a greater than index of the last question
590 if (L_End_Question is NULL or L_End_Question = FND_API.G_MISS_NUM) then
591 L_End_Question :=FND_API.G_MISS_NUM;
592 end if;
593 -- validation
594 if (L_Start_Question > L_End_Question OR L_Start_Question<=0 OR L_End_Question<=0) then
595 X_Return_Status := FND_API.G_RET_STS_ERROR;
596 raise FND_API.G_EXC_UNEXPECTED_ERROR;
597 end if;
598 -- Construct query statement, open cursor, execute query statement, retrieve results
599 l_statement := 'SELECT Q.QUESTION_ID, Q.NAME, L.LOOKUP_TYPE, Q.MANDTORY_FLAG, Q.SCORING_FLAG, Q.LOOKUP_ID, Q.LAST_UPDATE_DATE, Q.NOTE_TYPE, Q.SHOW_ON_CREATION_FLAG '
600 || ' FROM CS_TP_QUESTIONS_VL Q, CS_TP_LOOKUPS L, CS_TP_TEMPLATE_QUESTIONS TQ' ||
601 ' where Q.LOOKUP_ID = L.LOOKUP_ID and TQ.QUESTION_ID = Q.QUESTION_ID and TQ.TEMPLATE_ID=:v_Template_ID';
602
603 if (P_Display_Order is NULL OR P_Display_Order=FND_API.G_MISS_CHAR OR length(P_Display_Order)<=0 OR P_Display_Order =NORMAL) then
604 l_statement := l_statement || ' ORDER BY TQ.SEQUENCE_NUMBER ';
605
606 elsif (P_Display_Order=ALPHABATICAL) then
607 l_statement := l_statement || ' ORDER BY Q.NAME ';
608 elsif (P_Display_Order = REVERSE_ALPHABATICAL) then
609 l_statement := l_statement || ' ORDER BY Q.NAME desc ';
610 elsif (P_Display_Order = CRONOLOGICAL) then
611 l_statement := l_statement || ' ORDER BY LAST_UPDATE_DATE ';
612 elsif (P_Display_Order = REVERSE_CRONOLOGICAL) then
613 l_statement := l_statement || ' ORDER BY LAST_UPDATE_DATE desc ';
614 else
615 l_statement := l_statement || ' ORDER BY TQ.SEQUENCE_NUMBER ';
616 end if;
617 l_CursorID := dbms_sql.open_cursor;
618
619 /*
620 IF (FND_API.To_Boolean (DEBUG)) then
621 dbms_output.put_line ('Statement is ' || substr (l_statement, 1, 200));
622 dbms_output.put_line ( substr (l_statement, 201, 400));
623 end if;
624 */
625
626 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
627
628 dbms_sql.bind_variable(l_CursorID, 'v_Template_ID', P_Template_ID);
629
630 dbms_sql.define_column(l_CursorID, 1, L_QUESTION_ID);
631 dbms_sql.define_column(l_CursorID, 2, L_QUESTION_NAME,2000);
632 dbms_sql.define_column(l_CursorID, 3, L_ANSWER_TYPE, 60);
633 dbms_sql.define_column(l_CursorID, 4, L_MANDATORY_FLAG, 60 );
634 dbms_sql.define_column(l_CursorID, 5, L_SCORING_FLAG, 60);
635 dbms_sql.define_column(l_CursorID, 6, L_LOOKUP_ID);
636 dbms_sql.define_column(l_CursorID, 7, L_LAST_UPDATED_DATE);
637
638 dbms_sql.define_column(l_CursorID, 8, L_NOTE_TYPE, 30);
639 dbms_sql.define_column(l_CursorID, 9, L_SHOW_ON_CREATION_FLAG,1);
640
641
642 L_Total_Questions_NotUsed := dbms_sql.execute(l_CursorID);
643
644 i:=1;
645 j:=0;
646
647 while (dbms_sql.fetch_rows(l_CursorID) > 0) loop
648 if (i>= L_Start_Question AND i<=L_End_Question) then
649 dbms_sql.column_value(l_CursorID, 1, L_QUESTION_ID);
650 dbms_sql.column_value(l_CursorID, 2, L_QUESTION_NAME);
651 dbms_sql.column_value(l_CursorID, 3, L_ANSWER_TYPE);
652 dbms_sql.column_value(l_CursorID, 4, L_MANDATORY_FLAG);
653 dbms_sql.column_value(l_CursorID, 5, L_SCORING_FLAG);
654 dbms_sql.column_value(l_CursorID, 6, L_LOOKUP_ID);
655 dbms_sql.column_value(l_CursorID, 7, L_LAST_UPDATED_DATE);
656
657 dbms_sql.column_value(l_CursorID, 8, L_NOTE_TYPE);
658 dbms_sql.column_value(l_CursorID, 9, L_SHOW_ON_CREATION_FLAG);
659
660 X_Question_List_To_Show(j).mQuestionID := L_Question_ID;
661 X_Question_List_To_Show(j).mQuestionName := L_Question_NAME;
662 X_Question_List_To_Show(j).mAnswerType := L_ANSWER_TYPE;
663 X_Question_List_To_Show(j).mMandatoryFlag:= L_MANDATORY_FLAG;
664 X_Question_List_To_Show(j).mScoringFlag :=L_SCORING_FLAG;
665 X_Question_List_To_Show(j).mLookUpID := L_LOOKUP_ID;
666 X_Question_List_To_Show(j).mLast_Updated_Date := to_char( L_LAST_UPDATED_DATE, l_default_last_up_date_format);
667
668 X_Question_List_To_Show(j).mNoteType := L_NOTE_TYPE;
669 X_Question_List_To_Show(j).mShowOnCreationFlag :=
670 L_SHOW_ON_CREATION_FLAG;
671 j:=j+1;
672 elsif (i > L_End_Question) then
673 --exit;
674 null;
675 end if;
676 i:=i+1;
677 end loop;
678
679 dbms_sql.close_cursor(l_CursorID);
680 X_Retrieved_Question_Number := j;
681 X_Total_Questions := i-1;
682 -- Standard check of p_commit.
683 IF FND_API.To_Boolean( p_commit) THEN
684 COMMIT WORK;
685 END IF;
686 -- Standard call to get message count and if count is 1, get message info.
687 FND_MSG_PUB.Count_And_Get
688 (p_count => x_msg_count,
689 p_data => x_msg_data
690 );
691
692 EXCEPTION
693 WHEN FND_API.G_EXC_ERROR THEN
694 FND_MSG_PUB.Count_And_Get
695 (p_count => x_msg_count ,
696 p_data => x_msg_data
697 );
698
699 WHEN OTHERS THEN
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
701 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
702 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
703 END IF;
704 FND_MSG_PUB.Count_And_Get
705 (p_count => x_msg_count ,
706 p_data => x_msg_data
707 );
708 raise;
709
710 end Show_Questions;
711
712 procedure Show_Question (
713 p_api_version_number IN NUMBER,
714 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
715 p_commit IN VARCHAR := FND_API.G_FALSE,
716 P_Question_ID IN NUMBER,
717 X_Msg_Count OUT NOCOPY NUMBER,
718 X_Msg_Data OUT NOCOPY VARCHAR2,
719 X_Return_Status OUT NOCOPY VARCHAR2,
720 X_Question_To_Show OUT NOCOPY Question
721 )
722
723 is
724 l_api_name CONSTANT VARCHAR2(30) := 'Show_Question';
725 l_api_version CONSTANT NUMBER := 1.0;
726 cursor C (v_Question_ID NUMBER) is
727 SELECT Q.QUESTION_ID, Q.NAME, L.LOOKUP_TYPE, Q.MANDTORY_FLAG, Q.SCORING_FLAG, Q.LOOKUP_ID, Q.LAST_UPDATE_DATE, Q.NOTE_TYPE, Q.SHOW_ON_CREATION_FLAG
728 FROM CS_TP_QUESTIONS_VL Q, CS_TP_LOOKUPS L where Q.LOOKUP_ID = L.LOOKUP_ID and Q.QUESTION_ID = v_Question_ID;
729 One_Question C%ROWTYPE;
730
731 begin
732 -- Initialize message list if p_init_msg_list is set to TRUE.
733 IF FND_API.to_Boolean( p_init_msg_list ) THEN
734 FND_MSG_PUB.initialize;
735 END IF;
736
737 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
738
739 -- Start API Body
740 open C (P_Question_ID);
741 fetch C into One_Question;
742 if (C%notfound) then
743 close C;
744 X_Return_Status := FND_API.G_RET_STS_ERROR;
745 raise no_data_found;
746 end if;
747 close C;
748 X_Question_To_Show.mQuestionID := One_Question.Question_ID;
749 X_Question_To_Show.mQuestionName := One_Question.Name;
750 X_Question_To_Show.mAnswerType := One_Question.Lookup_Type ;
751 X_Question_To_Show.mMandatoryFlag:= One_Question.Mandtory_Flag;
752 X_Question_To_Show.mScoringFlag := One_Question.Scoring_Flag;
753 X_Question_To_Show.mLookUpID := One_Question.LookUp_ID;
754 X_Question_To_Show.mLast_Updated_Date := to_char( One_Question.Last_Update_Date , l_default_last_up_date_format);
755 X_Question_To_Show.mNoteType := One_Question.Note_Type;
756 X_Question_To_Show.mShowOnCreationFlag := One_Question.Show_On_Creation_Flag;
757
758 -- Standard check of p_commit.
759 IF FND_API.To_Boolean( p_commit) THEN
760 COMMIT WORK;
761 END IF;
762 -- Standard call to get message count and if count is 1, get message info.
763 FND_MSG_PUB.Count_And_Get
764 (p_count => x_msg_count ,
765 p_data => x_msg_data
766 );
767
768 EXCEPTION
769 WHEN FND_API.G_EXC_ERROR THEN
770 FND_MSG_PUB.Count_And_Get
771 (p_count => x_msg_count ,
772 p_data => x_msg_data
773 );
774
775 WHEN OTHERS THEN
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
777 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
778 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
779 END IF;
780 FND_MSG_PUB.Count_And_Get
781 (p_count => x_msg_count ,
782 p_data => x_msg_data
783 );
784 raise;
785
786 end Show_Question;
787
788 end CS_TP_QUESTIONS_PVT;