[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SOLN_CATEGORIES_PKG
Source
1 PACKAGE BODY CS_KB_SOLN_CATEGORIES_PKG AS
2 /* $Header: cskbcatb.pls 115.10 2003/11/21 22:30:56 mkettle noship $ */
3
4 procedure INSERT_ROW
5 (
6 X_ROWID in OUT NOCOPY VARCHAR2,
7 X_CATEGORY_ID in OUT NOCOPY NUMBER,
8 X_PARENT_CATEGORY_ID in NUMBER,
9 X_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER,
16 X_VISIBILITY_ID in NUMBER
17 )
18 IS
19 cursor getNewCategoryIdCsr is
20 select cs_kb_soln_categories_s.nextval
21 from dual;
22
23 cursor verifyRowCursor is
24 select ROWID
25 from CS_KB_SOLN_CATEGORIES_B
26 where CATEGORY_ID = X_CATEGORY_ID;
27 BEGIN
28
29 /* Get a new category id if none is passed */
30 IF (X_CATEGORY_ID IS NULL)
31 THEN
32 OPEN getNewCategoryIdCsr;
33 FETCH getNewCategoryIdCsr INTO X_CATEGORY_ID;
34 CLOSE getNewCategoryIdCsr;
35 END IF;
36
37 /* created base table record */
38 insert into CS_KB_SOLN_CATEGORIES_B
39 (
40 CATEGORY_ID,
41 PARENT_CATEGORY_ID,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 VISIBILITY_ID
48 )
49 values
50 (
51 X_CATEGORY_ID,
52 X_PARENT_CATEGORY_ID,
53 X_CREATION_DATE,
54 X_CREATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN,
58 X_VISIBILITY_ID
59 );
60
61 /* create translation table record(s) */
62 insert into CS_KB_SOLN_CATEGORIES_TL
63 (
64 CATEGORY_ID,
65 NAME,
66 DESCRIPTION,
67 CREATION_DATE,
68 CREATED_BY,
69 LAST_UPDATE_DATE,
70 LAST_UPDATED_BY,
71 LAST_UPDATE_LOGIN,
72 LANGUAGE,
73 SOURCE_LANG
74 )
75 select
76 X_CATEGORY_ID,
77 X_NAME,
78 X_DESCRIPTION,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_DATE,
82 X_LAST_UPDATED_BY,
83 X_LAST_UPDATE_LOGIN,
84 L.LANGUAGE_CODE,
85 userenv('LANG')
86 from FND_LANGUAGES L
87 where L.INSTALLED_FLAG in ('I', 'B')
88 and not exists
89 (select NULL
90 from CS_KB_SOLN_CATEGORIES_TL T
91 where T.CATEGORY_ID = X_CATEGORY_ID
92 and T.LANGUAGE = L.LANGUAGE_CODE);
93
94
95 OPEN verifyRowCursor;
96 FETCH verifyRowCursor INTO X_ROWID;
97 IF (verifyRowCursor%NOTFOUND)
98 THEN
99 CLOSE verifyRowCursor;
100 RAISE NO_DATA_FOUND;
101 ELSE
102 CLOSE verifyRowCursor;
103 END IF;
104
105 END INSERT_ROW;
106
107 procedure UPDATE_ROW
108 (
109 X_CATEGORY_ID in NUMBER,
110 X_PARENT_CATEGORY_ID in NUMBER,
111 X_NAME in VARCHAR2,
112 X_DESCRIPTION in VARCHAR2,
113 X_LAST_UPDATE_DATE in DATE,
114 X_LAST_UPDATED_BY in NUMBER,
115 X_LAST_UPDATE_LOGIN in NUMBER,
116 X_VISIBILITY_ID in NUMBER
117 )
118 is
119 begin
120 update cs_kb_soln_categories_b
121 set
122 parent_category_id = X_PARENT_CATEGORY_ID,
123 last_update_date = X_LAST_UPDATE_DATE,
124 last_updated_by = X_LAST_UPDATED_BY,
125 last_update_login = X_LAST_UPDATE_LOGIN,
126 visibility_id = x_visibility_id
127 where category_id = X_CATEGORY_ID;
128
129 if (SQL%NOTFOUND)
130 then
131 raise NO_DATA_FOUND;
132 end if;
133
134 update cs_kb_soln_categories_tl
135 set
136 name = X_NAME,
137 description = X_DESCRIPTION,
138 last_update_date = X_LAST_UPDATE_DATE,
139 last_updated_by = X_LAST_UPDATED_BY,
140 last_update_login = X_LAST_UPDATE_LOGIN,
141 source_lang = USERENV('LANG')
142 where category_id = X_CATEGORY_ID
143 AND USERENV('LANG') IN (language, source_lang);
144
145 if (SQL%NOTFOUND)
146 then
147 raise NO_DATA_FOUND;
148 end if;
149
150 end UPDATE_ROW;
151
152 procedure DELETE_ROW
153 (
154 X_CATEGORY_ID in NUMBER
155 )
156 is
157 begin
158 delete from cs_kb_soln_categories_tl
159 where category_id = X_CATEGORY_ID;
160
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164
165 delete from cs_kb_soln_categories_b
166 where category_id = X_CATEGORY_ID;
167
168 if (sql%notfound) then
169 raise no_data_found;
170 end if;
171 end DELETE_ROW;
172
173 procedure LOCK_ROW
174 (
175 X_CATEGORY_ID in NUMBER,
176 X_PARENT_CATEGORY_ID in NUMBER,
177 X_NAME in VARCHAR2,
178 X_DESCRIPTION in VARCHAR2,
179 X_CREATION_DATE in DATE,
180 X_CREATED_BY in NUMBER,
181 X_LAST_UPDATE_DATE in DATE,
182 X_LAST_UPDATED_BY in NUMBER,
183 X_LAST_UPDATE_LOGIN in NUMBER,
184 X_VISIBILITY_ID in NUMBER
185 )
186 is
187 begin
188 null;
189 end LOCK_ROW;
190
191 procedure ADD_LANGUAGE
192 is
193 begin
194 delete from CS_KB_SOLN_CATEGORIES_TL T
195 where not exists
196 (select NULL
197 from CS_KB_SOLN_CATEGORIES_B B
198 where B.CATEGORY_ID = T.CATEGORY_ID
199 );
200
201 update CS_KB_SOLN_CATEGORIES_TL T
202 set ( NAME, DESCRIPTION ) =
203 ( select
204 T2.NAME,
205 T2.DESCRIPTION
206 from CS_KB_SOLN_CATEGORIES_TL T2
207 where T2.CATEGORY_ID = T.CATEGORY_ID
208 and T2.LANGUAGE = T.SOURCE_LANG
209 )
210 where
211 (
212 T.CATEGORY_ID,
213 T.LANGUAGE
214 ) in
215 ( select
216 SUBT.CATEGORY_ID,
217 SUBT.LANGUAGE
218 from CS_KB_SOLN_CATEGORIES_TL SUBB, CS_KB_SOLN_CATEGORIES_TL SUBT
219 where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
220 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
221 and (SUBB.NAME <> SUBT.NAME
222 or (SUBB.NAME is null and SUBT.NAME is not null)
223 or (SUBB.NAME is not null and SUBT.NAME is null)
224 or (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
225 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
226 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null))
227 );
228
229 insert into CS_KB_SOLN_CATEGORIES_TL
230 (
231 CATEGORY_ID,
232 NAME,
233 DESCRIPTION,
234 CREATION_DATE,
235 CREATED_BY,
236 LAST_UPDATE_DATE,
237 LAST_UPDATED_BY,
238 LAST_UPDATE_LOGIN,
239 LANGUAGE,
240 SOURCE_LANG
241 ) select
242 T.CATEGORY_ID,
243 T.NAME,
244 T.DESCRIPTION,
245 T.CREATION_DATE,
246 T.CREATED_BY,
247 T.LAST_UPDATE_DATE,
248 T.LAST_UPDATED_BY,
249 T.LAST_UPDATE_LOGIN,
250 L.LANGUAGE_CODE,
251 T.SOURCE_LANG
252 from CS_KB_SOLN_CATEGORIES_TL T, FND_LANGUAGES L
253 where L.INSTALLED_FLAG in ('I', 'B')
254 and T.LANGUAGE = userenv('LANG')
255 and not exists
256 (select NULL
257 from CS_KB_SOLN_CATEGORIES_TL T2
258 where T2.CATEGORY_ID = T.CATEGORY_ID
259 and T2.LANGUAGE = L.LANGUAGE_CODE);
260 end ADD_LANGUAGE;
261
262 PROCEDURE TRANSLATE_ROW
263 (
264 X_CATEGORY_ID in NUMBER,
265 X_NAME in VARCHAR2,
266 X_DESCRIPTION in VARCHAR2,
267 X_OWNER in VARCHAR2
268 )
269 is
270 begin
271 update CS_KB_SOLN_CATEGORIES_TL
272 set
273 name = X_NAME,
274 description = X_DESCRIPTION,
275 last_update_date = sysdate,
276 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
277 last_update_login = 0,
278 source_lang = userenv('LANG')
279 where category_id = X_CATEGORY_ID
280 and userenv('LANG') in (language, source_lang);
281 end TRANSLATE_ROW;
282
283 PROCEDURE LOAD_ROW
284 (
285 X_CATEGORY_ID in NUMBER,
286 X_PARENT_CATEGORY_ID in NUMBER,
287 X_NAME in VARCHAR2,
288 X_DESCRIPTION in VARCHAR2,
289 X_OWNER in VARCHAR2,
290 X_VISIBILITY_ID in NUMBER
291 )
292 is
293 l_user_id number;
294 l_rowid varchar2(100);
295 l_category_id number := x_category_id;
296
297 CURSOR Check_Last_Updated_By IS
298 SELECT last_updated_by
299 FROM cs_kb_soln_categories_b
300 WHERE category_id = X_CATEGORY_ID;
301
302 l_last_upd_by NUMBER := NULL;
303
304 begin
305 if (x_owner = 'SEED') then
306 l_user_id := 1;
307 else
308 l_user_id := 0;
309 end if;
310
311 OPEN Check_Last_Updated_By;
312 FETCH Check_Last_Updated_By INTO l_last_upd_by;
313 CLOSE Check_Last_Updated_By;
314
315 IF l_last_upd_by = 1 OR
316 l_last_upd_by IS NULL THEN
317
318 update_row
319 ( x_category_id => x_category_id,
320 x_parent_category_id => x_parent_category_id,
321 x_name => x_name,
322 x_description => x_description,
323 x_last_update_date => sysdate,
324 x_last_updated_by => l_user_id,
325 x_last_update_login => 0,
326 x_visibility_id => x_visibility_id );
327 END IF;
328
329 exception
330 when no_data_found
331 then
332 insert_row
333 (
334 x_rowid => l_rowid,
335 x_category_id => l_category_id,
336 x_parent_category_id => x_parent_category_id,
337 x_name => x_name,
338 x_description => x_description,
339 x_creation_date => sysdate,
340 x_created_by => l_user_id,
341 x_last_update_date => sysdate,
342 x_last_updated_by => l_user_id,
343 x_last_update_login => 0,
344 x_visibility_id => x_visibility_id
345 );
346 end LOAD_ROW;
347
348 END CS_KB_SOLN_CATEGORIES_PKG;