[Home] [Help]
PACKAGE BODY: APPS.CS_TP_CHOICES_PVT
Source
1 package body CS_TP_CHOICES_PVT as
2 /* $Header: cstpcsmb.pls 120.1 2008/02/21 04:32:59 amganapa ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables and types
6 -- ---------------------------------------------------------
7 l_default_last_up_date_format CONSTANT VARCHAR2(30) := 'MM/DD/YYYY/SSSSS';
8 G_PKG_NAME CONSTANT VARCHAR2(100) := 'CS_TP_CHOCIES_PVT';
9
10 DEBUG CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
11 procedure Add_Choice (
12 p_api_version_number IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR := FND_API.G_FALSE,
15 p_One_Choice IN Choice,
16 X_Msg_Count OUT NOCOPY NUMBER,
17 X_Msg_Data OUT NOCOPY VARCHAR2,
18 X_Return_Status OUT NOCOPY VARCHAR2,
19 X_Choice_ID OUT NOCOPY NUMBER)
20 is
21 l_api_name CONSTANT VARCHAR2(30) := 'Add_Choice';
22 l_api_version CONSTANT NUMBER := 1.0;
23 Cursor LookupC is
24 select count (*) from CS_TP_LOOKUPS where LOOKUP_ID = p_One_Choice.mLookupID;
25 l_lookup_count NUMBER;
26 l_choice_id NUMBER;
27 l_ROWID VARCHAR2(30);
28 Cursor C is
29 select max(SEQUENCE_NUMBER) from CS_TP_CHOICES_VL;
30 l_current_date DATE :=FND_API.G_MISS_DATE;
31 l_created_by NUMBER :=FND_API.G_MISS_NUM;
32 l_login NUMBER :=FND_API.G_MISS_NUM;
33 l_max_sequence NUMBER;
34 begin
35 -- Initialize message list if p_init_msg_list is set to TRUE.
36 IF FND_API.to_Boolean( p_init_msg_list ) THEN
37 FND_MSG_PUB.initialize;
38 END IF;
39
40 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
41
42 -- Start API Body
43
44 -- Perform validation
45 IF (P_One_Choice.mChoiceName is NULL OR P_One_Choice.mChoiceName= FND_API.G_MISS_CHAR) THEN
46 X_Return_Status := FND_API.G_RET_STS_ERROR;
47 FND_MESSAGE.SET_NAME('CS','CS_TP_Choice_NAME_INVALID');
48 FND_MSG_PUB.Add;
49 RAISE FND_API.G_EXC_ERROR;
50 END IF;
51
55 FND_MSG_PUB.Add;
52 IF (P_One_Choice.mScore is NULL OR P_One_Choice.mScore = FND_API.G_MISS_NUM) then
53 X_Return_Status := FND_API.G_RET_STS_ERROR;
54 FND_MESSAGE.SET_NAME('CS','CS_TP_Choice_Score_INVALID');
56 RAISE FND_API.G_EXC_ERROR;
57 END IF;
58
59 open LookupC;
60 fetch LookupC into l_lookup_count;
61 close LookupC;
62 if (l_lookup_count <1 or l_lookup_count>1) then
63 X_Return_Status := FND_API.G_RET_STS_ERROR;
64 FND_MESSAGE.SET_NAME('CS','CS_TP_LookUPID_INVALID');
65 FND_MSG_PUB.Add;
66 raise FND_API.G_EXC_UNEXPECTED_ERROR;
67 end if;
68
69 open C;
70 fetch C into l_max_sequence;
71 if (C%notfound) then
72 l_max_sequence := 0;
73 end if;
74
75 close C;
76 if (l_max_sequence is NULL or l_max_sequence = FND_API.G_MISS_NUM) then
77 l_max_sequence :=0;
78 end if;
79
80 --Insert into the Cs_TO_CHOICES_B table, note start_date_active and end_date_active are null
81 l_current_date := sysdate;
82 l_created_by := FND_GLOBAL.user_id;
83 l_login := fnd_global.login_id;
84
85 select CS_TP_CHOICES_S.NEXTVAL into l_choice_id from dual;
86 CS_TP_CHOICES_PKG.INSERT_ROW (
87 X_ROWID => l_ROWID,
88 X_CHOICE_ID => l_choice_id,
89 X_LOOKUP_ID => P_One_Choice.mLookupID,
90 X_SEQUENCE_NUMBER => l_max_sequence +1,
91 X_START_DATE_ACTIVE => NULL,
92 X_END_DATE_ACTIVE => NULL,
93 X_SCORE => P_One_Choice.mScore,
94 X_VALUE => P_One_Choice.mChoiceName,
95 X_CREATION_DATE =>l_current_date,
96 X_CREATED_BY => l_created_by,
97 X_LAST_UPDATE_DATE =>l_current_date,
98 X_LAST_UPDATED_BY => l_created_by,
99 X_LAST_UPDATE_LOGIN => l_login
100 ,X_DEFAULT_FLAG => P_One_Choice.mDefaultChoiceFlag
101 );
102 X_Choice_ID := L_Choice_ID;
103 -- End of API Body
104
105 -- Standard check of p_commit.
106 IF FND_API.To_Boolean( p_commit ) THEN
107 COMMIT WORK;
108 END IF;
109 -- Standard call to get message count and if count is 1, get message info.
110 FND_MSG_PUB.Count_And_Get
111 (p_count => x_msg_count ,
112 p_data => x_msg_data
113 );
114
115 EXCEPTION
116 WHEN FND_API.G_EXC_ERROR THEN
117 FND_MSG_PUB.Count_And_Get
118 (p_count => x_msg_count ,
119 p_data => x_msg_data
120 );
121
122
123 WHEN OTHERS THEN
124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
125 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
126 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
127 END IF;
128 FND_MSG_PUB.Count_And_Get
129 (p_count => x_msg_count ,
130 p_data => x_msg_data
131 );
132 raise;
133 END Add_Choice;
134
135 procedure Delete_Choice (
136 p_api_version_number IN NUMBER,
137 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
138 p_commit IN VARCHAR := FND_API.G_FALSE,
139 P_Choice_ID IN NUMBER,
140 X_Msg_Count OUT NOCOPY NUMBER,
141 X_Msg_Data OUT NOCOPY VARCHAR2,
142 X_Return_Status OUT NOCOPY VARCHAR2
143 )
144 is
145 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Choice';
146 l_api_version CONSTANT NUMBER := 1.0;
147
148 begin
149 -- Initialize message list if p_init_msg_list is set to TRUE.
150 IF FND_API.to_Boolean( p_init_msg_list ) THEN
151 FND_MSG_PUB.initialize;
152 END IF;
153
154 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
155
156 -- Start API Body
157
158 -- delete choice
159
160 CS_TP_CHOICES_PKG.DELETE_ROW ( P_Choice_ID);
161
162 -- End of API Body
163
164 -- Standard check of p_commit.
165 IF FND_API.To_Boolean( p_commit ) THEN
166 COMMIT WORK;
167 END IF;
168 -- Standard call to get message count and if count is 1, get message info.
169 FND_MSG_PUB.Count_And_Get
170 (p_count => x_msg_count ,
171 p_data => x_msg_data
172 );
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
177 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
178 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
179 END IF;
180 FND_MSG_PUB.Count_And_Get
181 (p_count => x_msg_count ,
182 p_data => x_msg_data
183 );
184 raise;
185
186 end Delete_Choice;
187
188 procedure Sort_Choices (
189 p_api_version_number IN NUMBER,
190 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
191 p_commit IN VARCHAR := FND_API.G_FALSE,
192 P_Choices In Choice_List,
193 X_Msg_Count OUT NOCOPY NUMBER,
194 X_Msg_Data OUT NOCOPY VARCHAR2,
195 X_Return_Status OUT NOCOPY VARCHAR2
196 )
197
198 is
199 l_api_name CONSTANT VARCHAR2(30) := 'Sort_Choices';
200 l_api_version CONSTANT NUMBER := 1.0;
201 i NUMBER;
202 l_current_date DATE :=FND_API.G_MISS_DATE;
206 l_score NUMBER;
203 l_last_updated_by VARCHAR2(200) :=FND_API.G_MISS_CHAR;
204 l_login VARCHAR2(200) :=FND_API.G_MISS_CHAR;
205 l_lookup_id NUMBER;
207 l_value VARCHAR2(500);
208 Cursor last_updateC (V_Choice_ID Number) is
209 select last_update_date, LOOKUP_ID, SCORE, VALUE from CS_TP_CHOICES_VL where choice_ID = V_Choice_ID;
210 l_last_update_date DATE;
211 begin
212 -- Initialize message list if p_init_msg_list is set to TRUE.
213 IF FND_API.to_Boolean( p_init_msg_list ) THEN
214 FND_MSG_PUB.initialize;
215 END IF;
216
217 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
218
219 -- Start API Body
220 l_current_date := sysdate;
221 l_last_updated_by := fnd_global.user_id;
222 l_login := fnd_global.login_id;
223 if (P_Choices.COUNT>0) then
224 for i in P_Choices.FIRST..P_Choices.LAST loop
225 open last_updateC (P_Choices(i).mChoiceID);
226 fetch last_updateC into l_last_update_date, l_lookup_id, l_score, l_value;
227 if (last_updateC%notfound) then
228 close last_updateC;
229 raise no_data_found;
230 end if;
231 close last_updateC;
232 if (to_date( P_Choices(i).mLast_Updated_date, l_default_last_up_date_format) < l_last_update_date) then
233 X_Return_Status := FND_API.G_RET_STS_ERROR;
234 FND_MESSAGE.SET_NAME('CS','CS_TP_CHOICE_UPDATED');
235 FND_MSG_PUB.Add;
236 RAISE FND_API.G_EXC_ERROR;
237 end if;
238 CS_TP_CHOICES_PKG.UPDATE_ROW (
239 X_CHOICE_ID => P_Choices (i).mChoiceID,
240 X_LOOKUP_ID => l_lookup_id,
241 X_START_DATE_ACTIVE => null,
242 X_END_DATE_ACTIVE =>null,
243 X_SEQUENCE_NUMBER => i,
244 X_SCORE => l_score,
245 X_VALUE => l_value,
246 X_LAST_UPDATE_DATE => l_current_date,
247 X_LAST_UPDATED_BY => l_last_updated_by,
248 X_LAST_UPDATE_LOGIN => l_login
249 ,X_DEFAULT_FLAG => P_Choices (i).mDefaultChoiceFlag
250 );
251 end loop;
252 end if;
253
254 -- Standard check of p_commit.
255 IF FND_API.To_Boolean( p_commit ) THEN
256 COMMIT WORK;
257 END IF;
258 -- Standard call to get message count and if count is 1, get message info.
259 FND_MSG_PUB.Count_And_Get
260 (p_count => x_msg_count ,
261 p_data => x_msg_data
262 );
263
264 EXCEPTION
265 WHEN FND_API.G_EXC_ERROR THEN
266 FND_MSG_PUB.Count_And_Get
267 (p_count => x_msg_count ,
268 p_data => x_msg_data
269 );
270
271
272 WHEN OTHERS THEN
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
274 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
275 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
276 END IF;
277 FND_MSG_PUB.Count_And_Get
278 (p_count => x_msg_count ,
279 p_data => x_msg_data
280 );
281 raise;
282
283 end Sort_Choices;
284
285
286 procedure Show_Choices (
287 p_api_version_number IN NUMBER,
288 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
289 p_commit IN VARCHAR := FND_API.G_FALSE,
290 P_Lookup_Id IN NUMBER,
291 P_Display_Order IN VARCHAR2,
292 X_Msg_Count OUT NOCOPY NUMBER,
293 X_Msg_Data OUT NOCOPY VARCHAR2,
294 X_Return_Status OUT NOCOPY VARCHAR2,
295 X_Choice_List_To_Show OUT NOCOPY Choice_List
296 )
297
298 is
299 l_api_name CONSTANT VARCHAR2(30) := 'Show_Choices';
300 l_api_version CONSTANT NUMBER := 1.0;
301 i NUMBER;
302 l_CursorID NUMBER;
303 l_statement VARCHAR2(500);
304 l_CHOICE_ID NUMBER;
305 l_CHOICE_NAME VARCHAR2(1000);
306 l_LOOKUP_ID NUMBER;
307 l_SCORE NUMBER;
308 l_LAST_UPDATE_DATE DATE;
309
310 l_DEFAULT_CHOICE VARCHAR2(1);
311 l_total_choices_number NUMBER;
312 j NUMBER;
313 begin
314 -- Initialize message list if p_init_msg_list is set to TRUE.
315 IF FND_API.to_Boolean( p_init_msg_list ) THEN
316 FND_MSG_PUB.initialize;
317 END IF;
318
319 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
320
321 -- Start API Body
322
323 -- Construct query statement, open cursor, execute query statement, retrieve results
324 l_statement := 'SELECT CHOICE_ID, VALUE, LOOKUP_ID, SCORE, LAST_UPDATE_DATE, DEFAULT_CHOICE_FLAG from CS_TP_CHOICES_VL where LOOKUP_ID = : v_lookup_id';
325
326 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
327 l_statement := l_statement || ' ORDER BY SEQUENCE_NUMBER ';
328
329 elsif (P_Display_Order=ALPHABATICAL) then
330 l_statement := l_statement || ' ORDER BY VALUE ';
331 elsif (P_Display_Order = REVERSE_ALPHABATICAL) then
332 l_statement := l_statement || ' ORDER BY VALUE desc ';
333 elsif (P_Display_Order = CRONOLOGICAL) then
334 l_statement := l_statement || ' ORDER BY LAST_UPDATE_DATE ';
338 l_statement := l_statement || ' ORDER BY SEQUENCE_NUMBER ';
335 elsif (P_Display_Order = REVERSE_CRONOLOGICAL) then
336 l_statement := l_statement || ' ORDER BY LAST_UPDATE_DATE desc ';
337 else
339 end if;
340
341 l_CursorID := dbms_sql.open_cursor;
342 dbms_sql.parse (l_CursorID, l_statement, dbms_sql.NATIVE);
343
344 dbms_sql.bind_variable(l_CursorID, 'v_lookup_id', P_Lookup_ID);
345
346 dbms_sql.define_column(l_CursorID, 1, l_CHOICE_ID);
347 dbms_sql.define_column(l_CursorID, 2, l_CHOICE_NAME,1000);
348 dbms_sql.define_column(l_CursorID, 3, l_LOOKUP_ID);
349 dbms_sql.define_column(l_CursorID, 4, l_SCORE);
350 dbms_sql.define_column(l_CursorID, 5, l_LAST_UPDATE_DATE);
351
352 dbms_sql.define_column(l_CursorID, 6, l_DEFAULT_CHOICE,1);
353
354 l_total_choices_number:= dbms_sql.execute(l_CursorID);
355
356 j:=0;
357 while (dbms_sql.fetch_rows (l_CursorID) > 0) loop
358
359 dbms_sql.column_value(l_CursorID, 1, l_CHOICE_ID);
360 dbms_sql.column_value(l_CursorID, 2, l_CHOICE_NAME);
361 dbms_sql.column_value(l_CursorID, 3, l_LOOKUP_ID);
362 dbms_sql.column_value(l_CursorID, 4, l_SCORE);
363 dbms_sql.column_value(l_CursorID, 5, l_LAST_UPDATE_DATE);
364
365 dbms_sql.column_value(l_CursorID, 6, l_DEFAULT_CHOICE);
366 X_Choice_List_To_Show (j).mChoiceID :=l_CHOICE_ID;
367 X_Choice_List_To_Show (j).mChoiceName :=l_CHOICE_NAME;
368 X_Choice_List_To_Show (j).mLookupID:=l_LOOKUP_ID;
369 X_Choice_List_To_Show (j).mScore:=l_SCORE;
370 X_Choice_List_To_Show (j).mLast_Updated_Date:=to_char (l_LAST_UPDATE_DATE, l_default_last_up_date_format);
371
372 X_Choice_List_To_Show (j).mDefaultChoiceFlag := l_DEFAULT_CHOICE;
373 j:=j+1;
374
375 end loop;
376 dbms_sql.close_cursor(l_CursorID);
377 -- Standard check of p_commit.
378 IF FND_API.To_Boolean( p_commit) THEN
379 COMMIT WORK;
380 END IF;
381 -- Standard call to get message count and if count is 1, get message info.
382 FND_MSG_PUB.Count_And_Get
383 (p_count => x_msg_count,
384 p_data => x_msg_data
385 );
386
387 EXCEPTION
388 WHEN FND_API.G_EXC_ERROR THEN
389 FND_MSG_PUB.Count_And_Get
390 (p_count => x_msg_count ,
391 p_data => x_msg_data
392 );
393
394 WHEN OTHERS THEN
395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
396 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
397 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
398 END IF;
399 FND_MSG_PUB.Count_And_Get
400 (p_count => x_msg_count ,
401 p_data => x_msg_data
402 );
403 raise;
404 end Show_Choices;
405
406 procedure Update_Choices (
407 p_api_version_number IN NUMBER,
408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
409 p_commit IN VARCHAR := FND_API.G_FALSE,
410 P_Choices In Choice_List,
411 X_Msg_Count OUT NOCOPY NUMBER,
412 X_Msg_Data OUT NOCOPY VARCHAR2,
413 X_Return_Status OUT NOCOPY VARCHAR2
414 )
415
416 is
417 l_api_name CONSTANT VARCHAR2(30) := 'Update_Choices';
418 l_api_version CONSTANT NUMBER := 1.0;
419
420 Cursor last_updateC (V_Choice_ID Number) is
421 select last_update_date,sequence_number,value from CS_TP_CHOICES_VL
422 where choice_ID = V_Choice_ID;
423 l_last_update_date DATE;
424 l_sequence_number NUMBER;
425 l_value VARCHAR2(240);
426
427 i NUMBER;
428
429 l_last_updated_by NUMBER :=FND_API.G_MISS_NUM;
430 l_login NUMBER :=FND_API.G_MISS_NUM;
431 begin
432 -- Initialize message list if p_init_msg_list is set to TRUE.
433 IF FND_API.to_Boolean( p_init_msg_list ) THEN
434 FND_MSG_PUB.initialize;
435 END IF;
436
437 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
438
439 l_last_updated_by := fnd_global.user_id;
440 l_login := fnd_global.login_id;
441 if (P_Choices.COUNT>0) then
442 for i in P_Choices.FIRST..P_Choices.LAST loop
443
444 open last_updateC (P_Choices(i).mChoiceID);
445 fetch last_updateC into l_last_update_date, l_sequence_number, l_value;
446 if (last_updateC%notfound) then
447 close last_updateC;
448 raise no_data_found;
449 end if;
450 close last_updateC;
451
452 /*
453 if (to_date( P_Choices(i).mLast_Updated_date, l_default_last_up_date_format)
454 < l_last_update_date) then
455 X_Return_Status := FND_API.G_RET_STS_ERROR;
456 FND_MESSAGE.SET_NAME('CS','CS_TP_CHOICE_UPDATED');
457 FND_MSG_PUB.Add;
458 RAISE FND_API.G_EXC_ERROR;
459 end if;
460 */
461
462 CS_TP_CHOICES_PKG.UPDATE_ROW (
463 X_CHOICE_ID => P_Choices (i).mChoiceID,
464 X_LOOKUP_ID => P_Choices (i).mLookupID,
465 X_START_DATE_ACTIVE => null,
466 X_END_DATE_ACTIVE =>null,
470 X_LAST_UPDATE_DATE => sysdate,
467 X_SEQUENCE_NUMBER => l_sequence_number,
468 X_SCORE => P_Choices (i).mScore,
469 X_VALUE => l_value,
471 X_LAST_UPDATED_BY => l_last_updated_by,
472 X_LAST_UPDATE_LOGIN => l_login
473 ,X_DEFAULT_FLAG => P_Choices (i).mDefaultChoiceFlag
474 );
475
476
477 end loop;
478 end if;
479
480
481 -- Standard check of p_commit.
482 IF FND_API.To_Boolean( p_commit ) THEN
483 COMMIT WORK;
484 END IF;
485 -- Standard call to get message count and if count is 1, get message info.
486 FND_MSG_PUB.Count_And_Get
487 (p_count => x_msg_count ,
488 p_data => x_msg_data
489 );
490
491 EXCEPTION
492 WHEN FND_API.G_EXC_ERROR THEN
493 FND_MSG_PUB.Count_And_Get
494 (p_count => x_msg_count ,
495 p_data => x_msg_data
496 );
497
498
499 WHEN OTHERS THEN
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
502 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
503 END IF;
504 FND_MSG_PUB.Count_And_Get
505 (p_count => x_msg_count ,
506 p_data => x_msg_data
507 );
508 --raise;
509
510 end Update_Choices;
511
512 procedure Add_Freetext (
513 p_api_version_number IN NUMBER,
514 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
515 p_commit IN VARCHAR := FND_API.G_FALSE,
516 P_One_Freetext in Freetext,
517 X_Msg_Count OUT NOCOPY NUMBER,
518 X_Msg_Data OUT NOCOPY VARCHAR2,
519 X_Return_Status OUT NOCOPY VARCHAR2,
520 X_Freetext_ID OUT NOCOPY NUMBER
521 )
522
523 is
524 l_api_name CONSTANT VARCHAR2(30) := 'Add_Freetext';
525 l_api_version CONSTANT NUMBER := 1.0;
526
527 Cursor LookupC is
528 select count (*) from CS_TP_LOOKUPS where LOOKUP_ID = p_One_Freetext.mLookupID;
529 Cursor FreetextC is
530 select FREETEXT_ID from CS_TP_FREETEXTS where LOOKUP_ID= p_One_Freetext.mLookupID;
531
532
533 l_lookup_count NUMBER;
534 l_freetext_id NUMBER:=NULL;
535 l_freetext_update_id number;
536 l_ROWID VARCHAR2(30);
537 l_current_date DATE :=FND_API.G_MISS_DATE;
538 l_created_by NUMBER :=FND_API.G_MISS_NUM;
539 l_login NUMBER :=FND_API.G_MISS_NUM;
540 l_freetext_count NUMBER;
541 begin
542 -- Initialize message list if p_init_msg_list is set to TRUE.
543 IF FND_API.to_Boolean( p_init_msg_list ) THEN
544 FND_MSG_PUB.initialize;
545 END IF;
546
547 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
548
549 -- Start API Body
550
551 -- Perform validation
552 IF (p_One_Freetext.mFreetextSize is NULL OR p_One_Freetext.mFreetextSize= FND_API.G_MISS_NUM) THEN
553 X_Return_Status := FND_API.G_RET_STS_ERROR;
554 FND_MESSAGE.SET_NAME('CS','CS_TP_FreetextSize_INVALID');
555 FND_MSG_PUB.Add;
556 RAISE FND_API.G_EXC_ERROR;
557 END IF;
558 open LookupC;
559 fetch LookupC into l_lookup_count;
560 close LookupC;
561 if (l_lookup_count <1 or l_lookup_count>1) then
562 X_Return_Status := FND_API.G_RET_STS_ERROR;
563 raise FND_API.G_EXC_UNEXPECTED_ERROR;
564 end if;
565
566 l_current_date := sysdate;
567 l_created_by := FND_GLOBAL.user_id;
568 l_login := fnd_global.login_id;
569
570 open FreetextC;
571 fetch FreetextC into l_freetext_update_id;
572 close FreeTextC;
573
574 if (l_freetext_update_id is NULL ) then
575 select CS_TP_FREETEXTS_S.NEXTVAL into l_freetext_id from dual;
576 CS_TP_FREETEXTS_PKG.INSERT_ROW (
577 X_ROWID => l_ROWID,
578 X_FREETEXT_ID => l_freetext_id,
579 X_LOOKUP_ID => P_One_Freetext.mLookupID,
580 X_FREETEXT_SIZE => P_One_Freetext.mFreetextSize,
581 X_CREATION_DATE =>l_current_date,
582 X_CREATED_BY => l_created_by,
583 X_LAST_UPDATE_DATE =>l_current_date,
584 X_LAST_UPDATED_BY => l_created_by,
585 X_LAST_UPDATE_LOGIN => l_login );
586
587 elsif (l_freetext_update_id is not NULL ) then
588 CS_TP_FREETEXTS_PKG.UPDATE_ROW (
589 X_FREETEXT_ID => l_freetext_update_id,
590 X_FREETEXT_SIZE => P_One_Freetext.mFreetextSize,
591 X_LOOKUP_ID => P_One_Freetext.mLookUpID,
592 X_LAST_UPDATE_DATE => l_current_date,
593 X_LAST_UPDATED_BY => l_created_by,
594 X_LAST_UPDATE_LOGIN =>l_login
595 );
596 end if;
597 X_Freetext_ID := l_Freetext_ID ;
598 -- End of API Body
599
600 -- Standard check of p_commit.
601 IF FND_API.To_Boolean( p_commit ) THEN
602 COMMIT WORK;
603 END IF;
604 -- Standard call to get message count and if count is 1, get message info.
605 FND_MSG_PUB.Count_And_Get
606 (p_count => x_msg_count ,
607 p_data => x_msg_data
608 );
609
610 EXCEPTION
611 WHEN FND_API.G_EXC_ERROR THEN
612 FND_MSG_PUB.Count_And_Get
613 (p_count => x_msg_count ,
614 p_data => x_msg_data
615 );
616
617 /*
618 WHEN OTHERS THEN
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
620 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
621 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
622 END IF;
623 FND_MSG_PUB.Count_And_Get
624 (p_count => x_msg_count ,
625 p_data => x_msg_data
626 );
627 raise;
628 */
629 end Add_Freetext;
630
631 procedure Show_Freetext (
632 p_api_version_number IN NUMBER,
633 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
634 p_commit IN VARCHAR := FND_API.G_FALSE,
635 P_Lookup_ID IN NUMBER,
636 X_Msg_Count OUT NOCOPY NUMBER,
637 X_Msg_Data OUT NOCOPY VARCHAR2,
638 X_Return_Status OUT NOCOPY VARCHAR2,
639 X_Freetext OUT NOCOPY FREETEXT
640 )
641
642 is
643 l_api_name CONSTANT VARCHAR2(30) := 'Show_Freetext';
644 l_api_version CONSTANT NUMBER := 1.0;
645 Cursor freetextC is
646 SELECT ftxt.freetext_id, -- Bug 6705077
647 ftxt.freetext_size,
648 ftxt.lookup_id,
649 ftxt.last_update_date,
650 flkup.default_value
651 FROM cs_tp_freetexts ftxt,
652 cs_tp_lookups flkup
653 WHERE flkup.lookup_id = p_lookup_id
654 AND ftxt.lookup_id = flkup.lookup_id;
655
656 l_freetext freetextC%ROWTYPE;
657
658 begin
659 -- Initialize message list if p_init_msg_list is set to TRUE.
660 IF FND_API.to_Boolean( p_init_msg_list ) THEN
661 FND_MSG_PUB.initialize;
662 END IF;
663
664 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
665
666 -- Start API Body
667 open freetextC;
668
669
670
671 fetch freetextC into l_freetext;
672 if (freetextC%notfound) then
673 /*
674 IF (fND_API.to_boolean (DEBUG)) then
675 dbms_output.put_line ('Freetext cursor not found for the lookup id');
676 end if;
677 */
678 close freetextC;
679 end if;
680 X_Freetext.mFreetextID := l_freetext.FREETEXT_ID;
681 X_Freetext.mFreetextSize := l_freetext.FREETEXT_SIZE;
682 X_Freetext.mFreeTextDefaultText := l_freetext.DEFAULT_VALUE; -- Bug 6705077
683 X_Freetext.mLookupID :=l_freetext.LOOKUP_ID;
684 X_Freetext.mLast_Updated_Date :=l_freetext.LAST_UPDATE_DATE;
685
686 -- End of API Body
687
688 -- Standard check of p_commit.
689 IF FND_API.To_Boolean( p_commit ) THEN
690 COMMIT WORK;
691 END IF;
692 -- Standard call to get message count and if count is 1, get message info.
693 FND_MSG_PUB.Count_And_Get
694 (p_count => x_msg_count ,
695 p_data => x_msg_data
696 );
697
698 EXCEPTION
699 WHEN FND_API.G_EXC_ERROR THEN
700 FND_MSG_PUB.Count_And_Get
701 (p_count => x_msg_count ,
702 p_data => x_msg_data
703 );
704
705 /*
706 WHEN OTHERS THEN
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
708 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
709 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
710 END IF;
711 FND_MSG_PUB.Count_And_Get
712 (p_count => x_msg_count ,
713 p_data => x_msg_data
714 );
715 raise;
716
717 */
718 end Show_Freetext;
719
720 end CS_TP_CHOICES_PVT;