eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CS_TP_QUESTIONS_PKG

Source


1 package body CS_TP_QUESTIONS_PKG as
2 /* $Header: cstpqueb.pls 115.8 2002/12/04 18:15:32 wzli noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_QUESTION_ID in NUMBER,
6   X_LOOKUP_ID in NUMBER,
7   X_MANDTORY_FLAG in VARCHAR2,
8   X_SCORING_FLAG in VARCHAR2,
9   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
10   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
11   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
12   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
13   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
14   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
15   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
16   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
17   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
18   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
19   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
20   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
21   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
22   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
23   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
24   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
25 
26   X_NOTE_TYPE in VARCHAR2 DEFAULT NULL,
27   X_SHOW_ON_CREATION_FLAG in VARCHAR2 DEFAULT NULL,
28 
29   X_START_DATE_ACTIVE in DATE,
30   X_END_DATE_ACTIVE in DATE,
31   X_NAME in VARCHAR2,
32   X_TEXT in VARCHAR2,
33   X_DESCRIPTION in VARCHAR2,
34   X_CREATION_DATE in DATE,
35   X_CREATED_BY in NUMBER,
36   X_LAST_UPDATE_DATE in DATE,
37   X_LAST_UPDATED_BY in NUMBER,
38   X_LAST_UPDATE_LOGIN in NUMBER
39 ) is
40   cursor C is select ROWID from CS_TP_QUESTIONS_B
41     where QUESTION_ID = X_QUESTION_ID
42     ;
43 begin
44   insert into CS_TP_QUESTIONS_B (
45     QUESTION_ID,
46     LOOKUP_ID,
47     MANDTORY_FLAG,
48     SCORING_FLAG,
49     ATTRIBUTE_CATEGORY,
50     ATTRIBUTE1,
51     ATTRIBUTE2,
52     ATTRIBUTE3,
53     ATTRIBUTE4,
54     ATTRIBUTE5,
55     ATTRIBUTE6,
56     ATTRIBUTE7,
57     ATTRIBUTE8,
58     ATTRIBUTE9,
59     ATTRIBUTE10,
60     ATTRIBUTE11,
61     ATTRIBUTE12,
62     ATTRIBUTE13,
63     ATTRIBUTE14,
64     ATTRIBUTE15,
65     START_DATE_ACTIVE,
66     END_DATE_ACTIVE,
67     CREATION_DATE,
68     CREATED_BY,
69     LAST_UPDATE_DATE,
70     LAST_UPDATED_BY,
71     LAST_UPDATE_LOGIN,
72 
73     NOTE_TYPE                 ,
74     SHOW_ON_CREATION_FLAG
75 
76   ) values (
77     X_QUESTION_ID,
78     X_LOOKUP_ID,
79     X_MANDTORY_FLAG,
80     X_SCORING_FLAG,
81     X_ATTRIBUTE_CATEGORY,
82     X_ATTRIBUTE1,
83     X_ATTRIBUTE2,
84     X_ATTRIBUTE3,
85     X_ATTRIBUTE4,
86     X_ATTRIBUTE5,
87     X_ATTRIBUTE6,
88     X_ATTRIBUTE7,
89     X_ATTRIBUTE8,
90     X_ATTRIBUTE9,
91     X_ATTRIBUTE10,
92     X_ATTRIBUTE11,
93     X_ATTRIBUTE12,
94     X_ATTRIBUTE13,
95     X_ATTRIBUTE14,
96     X_ATTRIBUTE15,
97     X_START_DATE_ACTIVE,
98     X_END_DATE_ACTIVE,
99     X_CREATION_DATE,
100     X_CREATED_BY,
101     X_LAST_UPDATE_DATE,
102     X_LAST_UPDATED_BY,
103     X_LAST_UPDATE_LOGIN,
104 
105     X_NOTE_TYPE               ,
106     X_SHOW_ON_CREATION_FLAG
107   );
108 
109   insert into CS_TP_QUESTIONS_TL (
110     QUESTION_ID,
111     NAME,
112     TEXT,
113     DESCRIPTION,
114     CREATION_DATE,
115     CREATED_BY,
116     LAST_UPDATE_DATE,
117     LAST_UPDATED_BY,
118     LAST_UPDATE_LOGIN,
119     LANGUAGE,
120     SOURCE_LANG
121   ) select
122     X_QUESTION_ID,
123     X_NAME,
124     X_TEXT,
125     X_DESCRIPTION,
126     X_CREATION_DATE,
127     X_CREATED_BY,
128     X_LAST_UPDATE_DATE,
129     X_LAST_UPDATED_BY,
130     X_LAST_UPDATE_LOGIN,
131     L.LANGUAGE_CODE,
132     userenv('LANG')
133   from FND_LANGUAGES L
134   where L.INSTALLED_FLAG in ('I', 'B')
135   and not exists
136     (select NULL
137     from CS_TP_QUESTIONS_TL T
138     where T.QUESTION_ID = X_QUESTION_ID
139     and T.LANGUAGE = L.LANGUAGE_CODE);
140 
141   open c;
142   fetch c into X_ROWID;
143   if (c%notfound) then
144     close c;
145     raise no_data_found;
146   end if;
147   close c;
148 
149 end INSERT_ROW;
150 
151 procedure LOCK_ROW (
152   X_QUESTION_ID in NUMBER,
153   X_LOOKUP_ID in NUMBER,
154   X_MANDTORY_FLAG in VARCHAR2,
155   X_SCORING_FLAG in VARCHAR2,
156   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
157   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
158   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
159   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
160   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
161   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
162   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
163   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
164   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
165   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
166   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
167   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
168   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
169   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
170   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
171   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
172 
173   X_NOTE_TYPE in VARCHAR2 DEFAULT NULL,
174   X_SHOW_ON_CREATION_FLAG in VARCHAR2 DEFAULT NULL,
175 
176   X_START_DATE_ACTIVE in DATE,
177   X_END_DATE_ACTIVE in DATE,
178   X_NAME in VARCHAR2,
179   X_TEXT in VARCHAR2,
180   X_DESCRIPTION in VARCHAR2
181 ) is
182   cursor c is select
183       LOOKUP_ID,
184       MANDTORY_FLAG,
185       SCORING_FLAG,
186       ATTRIBUTE_CATEGORY,
187       ATTRIBUTE1,
188       ATTRIBUTE2,
189       ATTRIBUTE3,
190       ATTRIBUTE4,
191       ATTRIBUTE5,
192       ATTRIBUTE6,
193       ATTRIBUTE7,
194       ATTRIBUTE8,
195       ATTRIBUTE9,
196       ATTRIBUTE10,
197       ATTRIBUTE11,
198       ATTRIBUTE12,
199       ATTRIBUTE13,
200       ATTRIBUTE14,
201       ATTRIBUTE15,
202 
203 	 NOTE_TYPE,
204 	 SHOW_ON_CREATION_FLAG,
205 
206       START_DATE_ACTIVE,
207       END_DATE_ACTIVE
208     from CS_TP_QUESTIONS_B
209     where QUESTION_ID = X_QUESTION_ID
210     for update of QUESTION_ID nowait;
211   recinfo c%rowtype;
212 
213   cursor c1 is select
214       NAME,
215       TEXT,
216       DESCRIPTION,
217       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
218     from CS_TP_QUESTIONS_TL
219     where QUESTION_ID = X_QUESTION_ID
220     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
221     for update of QUESTION_ID nowait;
222 begin
223   open c;
224   fetch c into recinfo;
225   if (c%notfound) then
226     close c;
227     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
228     app_exception.raise_exception;
229   end if;
230   close c;
231   if (    (recinfo.LOOKUP_ID = X_LOOKUP_ID)
232       AND ((recinfo.MANDTORY_FLAG = X_MANDTORY_FLAG)
233            OR ((recinfo.MANDTORY_FLAG is null) AND (X_MANDTORY_FLAG is null)))
234       AND ((recinfo.SCORING_FLAG = X_SCORING_FLAG)
235            OR ((recinfo.SCORING_FLAG is null) AND (X_SCORING_FLAG is null)))
236       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
237            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
238       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
239            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
240       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
241            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
242       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
243            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
244       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
245            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
246       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
247            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
248       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
249            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
250       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
251            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
252       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
253            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
254       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
255            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
256       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
257            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
258       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
259            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
260       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
261            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
262       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
263            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
264       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
265            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
266       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
267            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
268       AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
269            OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
270       AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
271            OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
272 
273       AND ((recinfo.NOTE_TYPE = X_NOTE_TYPE)
274            OR ((recinfo.NOTE_TYPE is null) AND (X_NOTE_TYPE is null)))
275 	 AND ((recinfo.SHOW_ON_CREATION_FLAG = X_SHOW_ON_CREATION_FLAG)
276            OR ((recinfo.SHOW_ON_CREATION_FLAG is null) AND (X_SHOW_ON_CREATION_FLAG is null)))
277 
278   ) then
279     null;
280   else
281     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
282     app_exception.raise_exception;
283   end if;
284 
285   for tlinfo in c1 loop
286     if (tlinfo.BASELANG = 'Y') then
287       if (    ((tlinfo.NAME = X_NAME)
288                OR ((tlinfo.NAME is null) AND (X_NAME is null)))
289           AND (tlinfo.TEXT = X_TEXT)
290           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
291                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
292       ) then
293         null;
294       else
295         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
296         app_exception.raise_exception;
297       end if;
298     end if;
299   end loop;
300   return;
301 end LOCK_ROW;
302 
303 procedure UPDATE_ROW (
304   X_QUESTION_ID in NUMBER,
305   X_LOOKUP_ID in NUMBER,
306   X_MANDTORY_FLAG in VARCHAR2,
307   X_SCORING_FLAG in VARCHAR2,
308   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
309   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
310   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
311   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
312   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
313   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
314   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
315   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
316   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
317   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
318   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
319   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
320   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
321   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
322   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
323   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
324 
325   X_NOTE_TYPE in VARCHAR2 DEFAULT NULL,
326   X_SHOW_ON_CREATION_FLAG in VARCHAR2 DEFAULT NULL,
327 
328   X_START_DATE_ACTIVE in DATE,
329   X_END_DATE_ACTIVE in DATE,
330   X_NAME in VARCHAR2,
331   X_TEXT in VARCHAR2,
332   X_DESCRIPTION in VARCHAR2,
333   X_LAST_UPDATE_DATE in DATE,
334   X_LAST_UPDATED_BY in NUMBER,
335   X_LAST_UPDATE_LOGIN in NUMBER
336 ) is
337 begin
338   update CS_TP_QUESTIONS_B set
339     lookup_id     = decode(nvl(x_lookup_id,fnd_api.g_miss_num), fnd_api.g_miss_num, lookup_id, x_lookup_id),
340     mandtory_flag = decode(nvl(x_mandtory_flag, fnd_api.g_miss_char), fnd_api.g_miss_char , mandtory_flag, x_mandtory_flag),
344     attribute1  = decode(nvl(x_attribute1, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute1 , x_attribute1 ),
341     scoring_flag  = decode(nvl(x_scoring_flag,fnd_api.g_miss_char), fnd_api.g_miss_char ,  scoring_flag, x_scoring_flag),
342     attribute_category = decode(nvl(x_attribute_category,fnd_api.g_miss_char),
343                    fnd_api.g_miss_char, attribute_category, x_attribute_category),
345     attribute2  = decode(nvl(x_attribute2, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute2 , x_attribute2 ),
346     attribute3  = decode(nvl(x_attribute3, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute3 , x_attribute3 ),
347     attribute4  = decode(nvl(x_attribute4, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute4 , x_attribute4 ),
348     attribute5  = decode(nvl(x_attribute5, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute5 , x_attribute5 ),
349     attribute6  = decode(nvl(x_attribute6, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute6 , x_attribute6 ),
350     attribute7  = decode(nvl(x_attribute7, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute7 , x_attribute7 ),
351     attribute8  = decode(nvl(x_attribute8, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute8 , x_attribute8 ),
352     attribute9  = decode(nvl(x_attribute9, fnd_api.g_miss_char), fnd_api.g_miss_char, attribute9 , x_attribute9 ),
353     attribute10 = decode(nvl(x_attribute10,fnd_api.g_miss_char), fnd_api.g_miss_char, attribute10, x_attribute10),
357     attribute14 = decode(nvl(x_attribute14,fnd_api.g_miss_char), fnd_api.g_miss_char, attribute14, x_attribute14),
354     attribute11 = decode(nvl(x_attribute11,fnd_api.g_miss_char), fnd_api.g_miss_char, attribute11, x_attribute11),
355     attribute12 = decode(nvl(x_attribute12,fnd_api.g_miss_char), fnd_api.g_miss_char, attribute12, x_attribute12),
356     attribute13 = decode(nvl(x_attribute13,fnd_api.g_miss_char), fnd_api.g_miss_char, attribute13, x_attribute13),
358     attribute15 = decode(nvl(x_attribute15,fnd_api.g_miss_char), fnd_api.g_miss_char, attribute15, x_attribute15),
359     note_type   = decode(nvl(x_note_type, fnd_api.g_miss_char), fnd_api.g_miss_char, note_type, x_note_type),
360     show_on_creation_flag = decode(nvl(x_show_on_creation_flag, fnd_api.g_miss_char), fnd_api.g_miss_char,
361                             show_on_creation_flag, x_show_on_creation_flag),
362     start_date_active     = decode(nvl(x_start_date_active, fnd_api.g_miss_date), fnd_api.g_miss_date,
363                             start_date_active, x_start_date_active),
364     end_date_active       = decode(nvl(x_end_date_active, fnd_api.g_miss_date), fnd_api.g_miss_date,
365                             end_date_active, x_end_date_active),
366     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
367     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
368     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
369   where QUESTION_ID = X_QUESTION_ID;
370 
371   if (sql%notfound) then
372     raise no_data_found;
373   end if;
374 
375   update CS_TP_QUESTIONS_TL set
376     name = decode(nvl(x_name, fnd_api.g_miss_char), fnd_api.g_miss_char, name,x_name),
377     text = decode(nvl(x_text, fnd_api.g_miss_char), fnd_api.g_miss_char, text,x_text),
378     description = decode(nvl(x_description, fnd_api.g_miss_char), fnd_api.g_miss_char,description, x_description),
379     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
380     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
381     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
382     SOURCE_LANG = userenv('LANG')
383   where QUESTION_ID = X_QUESTION_ID
384   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
385 
386   if (sql%notfound) then
387     raise no_data_found;
388   end if;
389 end UPDATE_ROW;
390 
391 procedure DELETE_ROW (
392   X_QUESTION_ID in NUMBER
393 ) is
394 begin
395   delete from CS_TP_QUESTIONS_TL
396   where QUESTION_ID = X_QUESTION_ID;
397 
398   if (sql%notfound) then
399     raise no_data_found;
400   end if;
401 
402   delete from CS_TP_QUESTIONS_B
403   where QUESTION_ID = X_QUESTION_ID;
404 
405   if (sql%notfound) then
406     raise no_data_found;
407   end if;
408 end DELETE_ROW;
409 
410 procedure ADD_LANGUAGE
411 is
412 begin
413   delete from CS_TP_QUESTIONS_TL T
414   where not exists
415     (select NULL
416     from CS_TP_QUESTIONS_B B
417     where B.QUESTION_ID = T.QUESTION_ID
418     );
419 
420   update CS_TP_QUESTIONS_TL T set (
421       NAME,
422       TEXT,
423       DESCRIPTION
424     ) = (select
425       B.NAME,
426       B.TEXT,
427       B.DESCRIPTION
428     from CS_TP_QUESTIONS_TL B
429     where B.QUESTION_ID = T.QUESTION_ID
430     and B.LANGUAGE = T.SOURCE_LANG)
431   where (
432       T.QUESTION_ID,
433       T.LANGUAGE
434   ) in (select
435       SUBT.QUESTION_ID,
436       SUBT.LANGUAGE
437     from CS_TP_QUESTIONS_TL SUBB, CS_TP_QUESTIONS_TL SUBT
438     where SUBB.QUESTION_ID = SUBT.QUESTION_ID
439     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
440     and (SUBB.NAME <> SUBT.NAME
444       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
441       or (SUBB.NAME is null and SUBT.NAME is not null)
442       or (SUBB.NAME is not null and SUBT.NAME is null)
443       or SUBB.TEXT <> SUBT.TEXT
445       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
446       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
447   ));
448 
449   insert into CS_TP_QUESTIONS_TL (
450     QUESTION_ID,
451     NAME,
452     TEXT,
453     DESCRIPTION,
454     CREATION_DATE,
455     CREATED_BY,
456     LAST_UPDATE_DATE,
457     LAST_UPDATED_BY,
458     LAST_UPDATE_LOGIN,
459     LANGUAGE,
460     SOURCE_LANG
461   ) select
462     B.QUESTION_ID,
463     B.NAME,
464     B.TEXT,
465     B.DESCRIPTION,
466     B.CREATION_DATE,
467     B.CREATED_BY,
468     B.LAST_UPDATE_DATE,
469     B.LAST_UPDATED_BY,
470     B.LAST_UPDATE_LOGIN,
471     L.LANGUAGE_CODE,
472     B.SOURCE_LANG
473   from CS_TP_QUESTIONS_TL B, FND_LANGUAGES L
474   where L.INSTALLED_FLAG in ('I', 'B')
475   and B.LANGUAGE = userenv('LANG')
476   and not exists
477     (select NULL
478     from CS_TP_QUESTIONS_TL T
479     where T.QUESTION_ID = B.QUESTION_ID
480     and T.LANGUAGE = L.LANGUAGE_CODE);
481 end ADD_LANGUAGE;
482 
483 PROCEDURE LOAD_ROW(
484         x_question_id in number,
485         x_owner in varchar2,
486         x_name in varchar2,
487         x_text in varchar2,
488         x_description in varchar2,
489         x_mandatory_flag in varchar2,
490         x_scoring_flag in varchar2,
491         x_lookup_id in number,
492         x_start_date_active in date,
493         x_end_date_active in date,
494 	   X_NOTE_TYPE in VARCHAR2,
495 	   X_SHOW_ON_CREATION_FLAG in VARCHAR2 ) is
496 
497     l_mandatory_flag varchar2(1);
498     l_scoring_flag varchar2(1);
499     l_user_id number;
500     l_rowid varchar(30);
501 begin
502 
503      if (x_owner = 'SEED') then
504             l_user_id := 1;
505      else
506             l_user_id := 0;
507      end if;
508 
509      l_mandatory_flag := x_mandatory_flag;
510      l_scoring_flag := x_mandatory_flag;
511 
512      CS_TP_QUESTIONS_PKG.Update_Row(
513      X_QUESTION_ID => x_question_id,
514      X_LOOKUP_ID => x_lookup_id,
515      X_MANDTORY_FLAG => l_mandatory_flag,
516      X_SCORING_FLAG => l_scoring_flag,
517      X_Name => x_name,
518      X_Text => x_text,
519      X_Description => x_description,
520      x_start_date_active => x_start_date_active,
521      x_end_date_active => x_end_date_active,
522      X_Last_Update_Date => sysdate,
523      X_Last_Updated_By => l_user_id,
524      X_Last_Update_Login => 0,
525 	X_NOTE_TYPE => x_note_type,
526 	X_SHOW_ON_CREATION_FLAG => X_SHOW_ON_CREATION_FLAG );
527 
528      exception
529       when no_data_found then
530              CS_TP_QUESTIONS_PKG.Insert_Row(
531              X_Rowid => l_rowid,
532              X_QUESTION_ID => x_question_id,
533              X_LOOKUP_ID => x_lookup_id,
534              X_MANDTORY_FLAG => l_mandatory_flag,
535              X_SCORING_FLAG => l_scoring_flag,
539              X_Text => x_text,
536              X_START_DATE_ACTIVE => x_start_date_active,
537              X_END_DATE_ACTIVE => x_end_date_active,
538              X_NAME => x_name,
540              X_DESCRIPTION => x_description,
541              X_CREATION_DATE => sysdate,
542              X_CREATED_BY => l_user_id,
543              X_LAST_UPDATE_DATE => sysdate,
544              X_LAST_UPDATED_BY => l_user_id,
545              X_LAST_UPDATE_LOGIN => 0,
546              X_NOTE_TYPE => x_note_type,
547 		   X_SHOW_ON_CREATION_FLAG => X_SHOW_ON_CREATION_FLAG
548             );
549 
550 end;
551 
552 
553 PROCEDURE TRANSLATE_ROW(
554         x_question_id in number,
555     	x_owner in varchar2,
556         x_name in varchar2,
557         x_text in varchar2,
558         x_description in varchar2) is
559 
560         l_user_id number;
561         l_offset number;
562         l_amt    number;
563 begin
564 
565      -- Update translated non-clob portions for specified language
566      update CS_TP_QUESTIONS_TL set
567 	name = x_name,
568         text = x_text,
569         description = x_description,
570 	last_update_date  = sysdate,
571         last_updated_by   = decode(X_OWNER, 'SEED', 1, 0),
572         last_update_login = 0,
573         source_lang       = userenv('LANG')
574       where QUESTION_ID = to_number(X_QUESTION_ID)
575       and userenv('LANG') in (language, source_lang);
576 
577     exception
578       when no_data_found then null;
579 
583 end CS_TP_QUESTIONS_PKG;
580 end;
581 
582