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_FORUM_ATTRS_PKG

Source


1 PACKAGE BODY CS_FORUM_ATTRS_PKG AS
2 /* $Header: csfatb.pls 120.1 2005/06/22 12:15:44 appldev ship $ */
3 
4 procedure INSERT_ROW (
5   X_ATTRIBUTE_ID in NUMBER,
6   X_ATTRIBUTE_TYPE in VARCHAR2,
7   X_ATTRIBUTE_NAME in VARCHAR2,
8   X_NAME in VARCHAR2,
9   X_DESCRIPTION in VARCHAR2,
10   X_CREATION_DATE in DATE,
11   X_CREATED_BY in NUMBER,
12   X_LAST_UPDATE_DATE in DATE,
13   X_LAST_UPDATED_BY in NUMBER,
14   X_LAST_UPDATE_LOGIN in NUMBER,
15   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
16   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
17   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
18   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
19   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
20   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
21   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
22   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
23   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
24   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
25   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
26   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
27   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
28   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
29   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
30   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL
31 ) is
32   cursor C is select ATTRIBUTE_ID from CS_FORUM_ATTRS_B
33     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
34     ;
35 begin
36   insert into CS_FORUM_ATTRS_B (
37     ATTRIBUTE_ID,
38     ATTRIBUTE_TYPE,
39     ATTRIBUTE_NAME,
40     CREATION_DATE,
41     CREATED_BY,
42     LAST_UPDATE_DATE,
43     LAST_UPDATED_BY,
44     LAST_UPDATE_LOGIN,
45     ATTRIBUTE_CATEGORY,
46     ATTRIBUTE1,
47     ATTRIBUTE2,
48     ATTRIBUTE3,
49     ATTRIBUTE4,
50     ATTRIBUTE5,
51     ATTRIBUTE6,
52     ATTRIBUTE7,
53     ATTRIBUTE8,
54     ATTRIBUTE9,
55     ATTRIBUTE10,
56     ATTRIBUTE11,
57     ATTRIBUTE12,
58     ATTRIBUTE13,
59     ATTRIBUTE14,
60     ATTRIBUTE15
61   ) values (
62     X_ATTRIBUTE_ID,
63     X_ATTRIBUTE_TYPE,
64     X_ATTRIBUTE_NAME,
65     X_CREATION_DATE,
66     X_CREATED_BY,
67     X_LAST_UPDATE_DATE,
68     X_LAST_UPDATED_BY,
69     X_LAST_UPDATE_LOGIN,
70     X_ATTRIBUTE_CATEGORY,
71     X_ATTRIBUTE1,
72     X_ATTRIBUTE2,
73     X_ATTRIBUTE3,
74     X_ATTRIBUTE4,
75     X_ATTRIBUTE5,
76     X_ATTRIBUTE6,
77     X_ATTRIBUTE7,
78     X_ATTRIBUTE8,
79     X_ATTRIBUTE9,
80     X_ATTRIBUTE10,
81     X_ATTRIBUTE11,
82     X_ATTRIBUTE12,
83     X_ATTRIBUTE13,
84     X_ATTRIBUTE14,
85     X_ATTRIBUTE15
86   );
87 
88   insert into CS_FORUM_ATTRS_TL (
89     ATTRIBUTE_ID,
90     NAME,
91     DESCRIPTION,
92     CREATION_DATE,
93     CREATED_BY,
94     LAST_UPDATE_DATE,
95     LAST_UPDATED_BY,
96     LAST_UPDATE_LOGIN,
97     LANGUAGE,
98     SOURCE_LANG
99   ) select
100     X_ATTRIBUTE_ID,
101     X_NAME,
102     X_DESCRIPTION,
103     X_CREATION_DATE,
104     X_CREATED_BY,
105     X_LAST_UPDATE_DATE,
106     X_LAST_UPDATED_BY,
107     X_LAST_UPDATE_LOGIN,
108     L.LANGUAGE_CODE,
109     userenv('LANG')
110   from FND_LANGUAGES L
111   where L.INSTALLED_FLAG in ('I', 'B')
112   and not exists
113     (select NULL
114     from CS_FORUM_ATTRS_TL T
115     where T.ATTRIBUTE_ID = X_ATTRIBUTE_ID
116     and T.LANGUAGE = L.LANGUAGE_CODE);
117 
118   open c;
119 /*
120   fetch c into X_ROWID;
121   if (c%notfound) then
122     close c;
123     raise no_data_found;
124   end if;
125 */
126   close c;
127 
128 end INSERT_ROW;
129 
130 procedure LOCK_ROW (
131   X_ATTRIBUTE_ID in NUMBER,
132   X_ATTRIBUTE_TYPE in VARCHAR2,
133   X_ATTRIBUTE_NAME in VARCHAR2,
134   X_NAME in VARCHAR2,
135   X_DESCRIPTION in VARCHAR2,
136   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
137   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
138   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
139   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
140   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
141   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
142   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
143   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
144   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
145   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
146   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
147   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
148   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
149   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
150   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
151   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL
152 ) is
153   cursor c is select
154       ATTRIBUTE_ID,
155       ATTRIBUTE_TYPE,
156       ATTRIBUTE_NAME,
157       ATTRIBUTE_CATEGORY,
158       ATTRIBUTE1,
159       ATTRIBUTE2,
160       ATTRIBUTE3,
161       ATTRIBUTE4,
162       ATTRIBUTE5,
163       ATTRIBUTE6,
164       ATTRIBUTE7,
165       ATTRIBUTE8,
166       ATTRIBUTE9,
167       ATTRIBUTE10,
168       ATTRIBUTE11,
169       ATTRIBUTE12,
170       ATTRIBUTE13,
171       ATTRIBUTE14,
172       ATTRIBUTE15
173     from CS_FORUM_ATTRS_B
174     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
175     for update of ATTRIBUTE_ID nowait;
176   recinfo c%rowtype;
177 
178   cursor c1 is select
179       NAME,
180       DESCRIPTION,
181       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
182     from CS_FORUM_ATTRS_TL
183     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
184     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
185     for update of ATTRIBUTE_ID nowait;
186 begin
187   open c;
188   fetch c into recinfo;
189   if (c%notfound) then
190     close c;
191 /*    fnd_ATTRIBUTE.set_name('FND', 'FORM_RECORD_DELETED');   */
192     app_exception.raise_exception;
193   end if;
194   close c;
195   if (
196           ((recinfo.ATTRIBUTE_ID = X_ATTRIBUTE_ID)
197            OR ((recinfo.ATTRIBUTE_ID is null) AND (X_ATTRIBUTE_ID is null)))
198       AND ((recinfo.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME)
199            OR ((recinfo.ATTRIBUTE_NAME is null) AND (X_ATTRIBUTE_NAME is null)))
200       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
201            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
202       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
203            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
204       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
205            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
206       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
207            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
208       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
209            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
210       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
211            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
212       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
213            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
214       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
215            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
216       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
217            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
218       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
219            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
220       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
221            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
222       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
223            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
224       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
225            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
226       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
227            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
228       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
229            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
230       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
231            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
232   ) then
233     null;
234   else
235 /*    fnd_ATTRIBUTE.set_name('FND', 'FORM_RECORD_CHANGED');   */
236     app_exception.raise_exception;
237   end if;
238 
239   for tlinfo in c1 loop
240     if (tlinfo.BASELANG = 'Y') then
241       if (    ((tlinfo.NAME = X_NAME)
242                OR ((tlinfo.NAME is null) AND (X_NAME is null)))
243           AND ((X_DESCRIPTION = tlinfo.DESCRIPTION)
244                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
245       ) then
246         null;
247       else
248 /*        fnd_ATTRIBUTE.set_name('FND', 'FORM_RECORD_CHANGED');   */
249         app_exception.raise_exception;
250       end if;
251     end if;
252   end loop;
253   return;
254 end LOCK_ROW;
255 
256 procedure UPDATE_ROW (
257   X_ATTRIBUTE_ID in NUMBER,
258   X_ATTRIBUTE_TYPE in VARCHAR2,
259   X_ATTRIBUTE_NAME in VARCHAR2,
260   X_NAME in VARCHAR2,
261   X_DESCRIPTION in VARCHAR2,
262   X_LAST_UPDATE_DATE in DATE,
263   X_LAST_UPDATED_BY in NUMBER,
264   X_LAST_UPDATE_LOGIN in NUMBER,
265   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
266   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
267   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
268   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
269   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
270   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
271   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
272   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
273   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
274   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
275   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
276   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
277   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
278   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
279   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
280   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL
281 ) is
282 begin
283   update CS_FORUM_ATTRS_B set
284     ATTRIBUTE_TYPE = X_ATTRIBUTE_TYPE,
285     ATTRIBUTE_NAME = X_ATTRIBUTE_NAME,
286     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
287     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
288     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
289     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
290     ATTRIBUTE1 = X_ATTRIBUTE1,
291     ATTRIBUTE2 = X_ATTRIBUTE2,
292     ATTRIBUTE3 = X_ATTRIBUTE3,
293     ATTRIBUTE4 = X_ATTRIBUTE4,
294     ATTRIBUTE5 = X_ATTRIBUTE5,
295     ATTRIBUTE6 = X_ATTRIBUTE6,
296     ATTRIBUTE7 = X_ATTRIBUTE7,
297     ATTRIBUTE8 = X_ATTRIBUTE8,
298     ATTRIBUTE9 = X_ATTRIBUTE9,
299     ATTRIBUTE10 = X_ATTRIBUTE10,
300     ATTRIBUTE11 = X_ATTRIBUTE11,
301     ATTRIBUTE12 = X_ATTRIBUTE12,
302     ATTRIBUTE13 = X_ATTRIBUTE13,
303     ATTRIBUTE14 = X_ATTRIBUTE14,
304     ATTRIBUTE15 = X_ATTRIBUTE15
305   where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
306 
307   if (sql%notfound) then
308     raise no_data_found;
309   end if;
310 
311   update CS_FORUM_ATTRS_TL set
312     NAME = X_NAME,
313     DESCRIPTION = X_DESCRIPTION,
314     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
315     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
316     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
317     SOURCE_LANG = userenv('LANG')
318   where ATTRIBUTE_ID = X_ATTRIBUTE_ID
319   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
320 
321   if (sql%notfound) then
322     raise no_data_found;
323   end if;
324 end UPDATE_ROW;
325 
326 procedure DELETE_ROW (
327   X_ATTRIBUTE_ID in NUMBER
328 ) is
329 begin
330   delete from CS_FORUM_ATTRS_TL
331   where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
332 
333 /*
334   if (sql%notfound) then
335     raise no_data_found;
336   end if;
337 */
338 
339   delete from CS_FORUM_ATTRS_B
340   where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
341 /*
342   if (sql%notfound) then
343     raise no_data_found;
344   end if;
345 */
346 end DELETE_ROW;
347 
348 procedure ADD_LANGUAGE
349 is
350 begin
351   delete from CS_FORUM_ATTRS_TL T
352   where not exists
353     (select NULL
354     from CS_FORUM_ATTRS_B B
355     where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
356     );
357 
358   update CS_FORUM_ATTRS_TL T set (
359       NAME,
360       DESCRIPTION
361     ) = (select
362       B.NAME,
363       B.DESCRIPTION
364     from CS_FORUM_ATTRS_TL B
365     where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
366     and B.LANGUAGE = T.SOURCE_LANG)
367   where (
368       T.ATTRIBUTE_ID,
369       T.LANGUAGE
370   ) in (select
371       SUBT.ATTRIBUTE_ID,
372       SUBT.LANGUAGE
373     from CS_FORUM_ATTRS_TL SUBB, CS_FORUM_ATTRS_TL SUBT
374     where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
375     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
376     and (SUBB.NAME <> SUBT.NAME
377       or (SUBB.NAME is null and SUBT.NAME is not null)
378       or (SUBB.NAME is not null and SUBT.NAME is null)
379       or (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
380       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
381       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
382   ));
383 
384   insert into CS_FORUM_ATTRS_TL (
385     ATTRIBUTE_ID,
386     NAME,
387     DESCRIPTION,
388     CREATION_DATE,
389     CREATED_BY,
390     LAST_UPDATE_DATE,
391     LAST_UPDATED_BY,
392     LAST_UPDATE_LOGIN,
393     LANGUAGE,
394     SOURCE_LANG
395   ) select
396     B.ATTRIBUTE_ID,
397     B.NAME,
398     B.DESCRIPTION,
399     B.CREATION_DATE,
400     B.CREATED_BY,
401     B.LAST_UPDATE_DATE,
402     B.LAST_UPDATED_BY,
403     B.LAST_UPDATE_LOGIN,
404     L.LANGUAGE_CODE,
405     B.SOURCE_LANG
406   from CS_FORUM_ATTRS_TL B, FND_LANGUAGES L
407   where L.INSTALLED_FLAG in ('I', 'B')
408   and B.LANGUAGE = userenv('LANG')
409   and not exists
410     (select NULL
411     from CS_FORUM_ATTRS_TL T
412     where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
413     and T.LANGUAGE = L.LANGUAGE_CODE);
414 end ADD_LANGUAGE;
415 
416 
417 PROCEDURE TRANSLATE_ROW(
418         X_ATTRIBUTE_ID in number,
419         x_name in varchar2,
420         x_description in varchar2,
421         x_owner in varchar2
422         )
423 is
424 begin
425     update cs_forum_attrs_tl set
426         description = x_description,
427         name = x_name,
428         LAST_UPDATE_DATE = sysdate,
429         LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
430         LAST_UPDATE_LOGIN = 0,
431         SOURCE_LANG = userenv('LANG')
432         where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
433            and ATTRIBUTE_ID = X_ATTRIBUTE_ID;
434 end TRANSLATE_ROW;
435 
436 
437 procedure LOAD_ROW (
438   X_ATTRIBUTE_ID in NUMBER,
439   X_ATTRIBUTE_TYPE in VARCHAR2,
440   X_ATTRIBUTE_NAME in VARCHAR2,
441   X_NAME in VARCHAR2,
442   X_DESCRIPTION in VARCHAR2,
443   x_owner in varchar2
444 
445 ) is
446     l_user_id number;
447 
448 begin
449     if (x_owner = 'SEED') then
450            l_user_id := 1;
451     else
452            l_user_id := 0;
453     end if;
454 
455     CS_FORUM_ATTRS_PKG.Update_Row(
456         	X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
457        		X_ATTRIBUTE_TYPE => X_ATTRIBUTE_TYPE,
458             X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME,
459             X_NAME => X_NAME,
460             X_DESCRIPTION => X_DESCRIPTION,
461     		X_Last_Update_Date => sysdate,
462     		X_Last_Updated_By => l_user_id,
463     		X_Last_Update_Login => 0);
464 
465      exception
469        		X_ATTRIBUTE_TYPE => X_ATTRIBUTE_TYPE,
466       when no_data_found then
467         	CS_FORUM_ATTRS_PKG.Insert_Row(
468         	X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
470             X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME,
471             X_NAME => X_NAME,
472             X_DESCRIPTION => X_DESCRIPTION,
473     		X_Creation_Date => sysdate,
474     		X_Created_By => l_user_id,
475     		X_Last_Update_Date => sysdate,
476     		X_Last_Updated_By => l_user_id,
477     		X_Last_Update_Login => 0);
478 
479 end LOAD_ROW;
480 
481 
482 
483 end CS_FORUM_ATTRS_PKG;