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