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_CATEGORIES_PKG

Source


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