1 PACKAGE BODY CS_KB_HISTORIES_PKG AS
2 /* $Header: cskbhib.pls 115.10 2002/12/02 21:57:35 mkettle ship $ */
3
4
5 FUNCTION Create_History(
6 p_name in varchar2,
7 p_desc in varchar2,
8 P_ATTRIBUTE_CATEGORY in VARCHAR2,
9 P_ATTRIBUTE1 in VARCHAR2,
10 P_ATTRIBUTE2 in VARCHAR2,
11 P_ATTRIBUTE3 in VARCHAR2,
12 P_ATTRIBUTE4 in VARCHAR2,
13 P_ATTRIBUTE5 in VARCHAR2,
14 P_ATTRIBUTE6 in VARCHAR2,
15 P_ATTRIBUTE7 in VARCHAR2,
16 P_ATTRIBUTE8 in VARCHAR2,
17 P_ATTRIBUTE9 in VARCHAR2,
18 P_ATTRIBUTE10 in VARCHAR2,
19 P_ATTRIBUTE11 in VARCHAR2,
20 P_ATTRIBUTE12 in VARCHAR2,
21 P_ATTRIBUTE13 in VARCHAR2,
22 P_ATTRIBUTE14 in VARCHAR2,
23 P_ATTRIBUTE15 in VARCHAR2
24 ) return number is
25 l_date date;
26 l_created_by number;
27 l_login number;
28 l_count pls_integer;
29 l_id number;
30 l_rowid varchar2(30);
31 begin
32
33 -- Check params
34
35 --prepare data, then insert new def
36 select cs_kb_histories_s.nextval into l_id from dual;
37 l_date := sysdate;
38 l_created_by := fnd_global.user_id;
39 l_login := fnd_global.login_id;
40
41 CS_KB_HISTORIES_PKG.Insert_Row(
42 X_Rowid => l_rowid,
43 X_History_Id => l_id,
44 X_history_name => null,
45 X_user_id => l_created_by,
46 X_entry_date => sysdate,
47 X_name => p_name,
48 X_description => p_desc,
49 X_Creation_Date => l_date,
50 X_Created_By => l_created_by,
51 X_Last_Update_Date => l_date,
52 X_Last_Updated_By => l_created_by,
53 X_Last_Update_Login => l_login,
54 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
55 X_ATTRIBUTE1 => P_ATTRIBUTE1,
56 X_ATTRIBUTE2 => P_ATTRIBUTE2,
57 X_ATTRIBUTE3 => P_ATTRIBUTE3,
58 X_ATTRIBUTE4 => P_ATTRIBUTE4,
59 X_ATTRIBUTE5 => P_ATTRIBUTE5,
60 X_ATTRIBUTE6 => P_ATTRIBUTE6,
61 X_ATTRIBUTE7 => P_ATTRIBUTE7,
62 X_ATTRIBUTE8 => P_ATTRIBUTE8,
63 X_ATTRIBUTE9 => P_ATTRIBUTE9,
64 X_ATTRIBUTE10 => P_ATTRIBUTE10,
65 X_ATTRIBUTE11 => P_ATTRIBUTE11,
66 X_ATTRIBUTE12 => P_ATTRIBUTE12,
67 X_ATTRIBUTE13 => P_ATTRIBUTE13,
68 X_ATTRIBUTE14 => P_ATTRIBUTE14,
69 X_ATTRIBUTE15 => P_ATTRIBUTE15
70 );
71 return l_id;
72
73 <<error_found>>
74 return ERROR_STATUS;
75
76 end Create_History;
77
78
79
80 --
81 -- Delete
82
83 FUNCTION Delete_History(
84 p_history_id in number
85 ) return number is
86 l_ret number;
87 l_count pls_integer;
88 begin
89 if p_history_id is null or p_history_id <= 0 then return ERROR_STATUS; end if;
90
91 select count(*) into l_count
92 from cs_kb_histories_b
93 where history_id = p_history_id;
94 if(l_count <= 0) then return ERROR_STATUS; end if;
95
96 CS_KB_HISTORIES_PKG.Delete_Row(
97 X_History_Id => p_history_id);
98
99 return OKAY_STATUS;
100 <<error_found>>
101 return ERROR_STATUS;
102
103 exception
104 WHEN OTHERS THEN
105 return ERROR_STATUS;
106 end Delete_History;
107
108
109
110 procedure INSERT_ROW (
111 X_ROWID in OUT NOCOPY VARCHAR2,
112 X_HISTORY_ID in NUMBER,
113 X_HISTORY_NAME in VARCHAR2,
114 X_USER_ID in NUMBER,
115 X_ENTRY_DATE in DATE,
116 X_NAME in VARCHAR2,
117 X_DESCRIPTION in VARCHAR2,
118 X_CREATION_DATE in DATE,
119 X_CREATED_BY in NUMBER,
120 X_LAST_UPDATE_DATE in DATE,
121 X_LAST_UPDATED_BY in NUMBER,
122 X_LAST_UPDATE_LOGIN in NUMBER,
123 X_ATTRIBUTE_CATEGORY in VARCHAR2,
124 X_ATTRIBUTE1 in VARCHAR2,
125 X_ATTRIBUTE2 in VARCHAR2,
126 X_ATTRIBUTE3 in VARCHAR2,
127 X_ATTRIBUTE4 in VARCHAR2,
128 X_ATTRIBUTE5 in VARCHAR2,
129 X_ATTRIBUTE6 in VARCHAR2,
130 X_ATTRIBUTE7 in VARCHAR2,
131 X_ATTRIBUTE8 in VARCHAR2,
132 X_ATTRIBUTE9 in VARCHAR2,
133 X_ATTRIBUTE10 in VARCHAR2,
134 X_ATTRIBUTE11 in VARCHAR2,
135 X_ATTRIBUTE12 in VARCHAR2,
136 X_ATTRIBUTE13 in VARCHAR2,
137 X_ATTRIBUTE14 in VARCHAR2,
138 X_ATTRIBUTE15 in VARCHAR2
139 ) is
140 cursor C is select ROWID from CS_KB_HISTORIES_B
141 where HISTORY_ID = X_HISTORY_ID
142 ;
143 begin
144 insert into CS_KB_HISTORIES_B (
145 HISTORY_ID,
146 HISTORY_NAME,
147 USER_ID,
148 ENTRY_DATE,
149 CREATION_DATE,
150 CREATED_BY,
151 LAST_UPDATE_DATE,
152 LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN,
154 ATTRIBUTE_CATEGORY,
155 ATTRIBUTE1,
156 ATTRIBUTE2,
157 ATTRIBUTE3,
158 ATTRIBUTE4,
159 ATTRIBUTE5,
160 ATTRIBUTE6,
161 ATTRIBUTE7,
162 ATTRIBUTE8,
163 ATTRIBUTE9,
164 ATTRIBUTE10,
165 ATTRIBUTE11,
166 ATTRIBUTE12,
167 ATTRIBUTE13,
168 ATTRIBUTE14,
169 ATTRIBUTE15
170 ) values (
171 X_HISTORY_ID,
172 X_HISTORY_NAME,
173 X_USER_ID,
174 X_ENTRY_DATE,
175 X_CREATION_DATE,
176 X_CREATED_BY,
177 X_LAST_UPDATE_DATE,
178 X_LAST_UPDATED_BY,
179 X_LAST_UPDATE_LOGIN,
180 X_ATTRIBUTE_CATEGORY,
181 X_ATTRIBUTE1,
182 X_ATTRIBUTE2,
183 X_ATTRIBUTE3,
184 X_ATTRIBUTE4,
185 X_ATTRIBUTE5,
186 X_ATTRIBUTE6,
187 X_ATTRIBUTE7,
188 X_ATTRIBUTE8,
189 X_ATTRIBUTE9,
190 X_ATTRIBUTE10,
191 X_ATTRIBUTE11,
192 X_ATTRIBUTE12,
193 X_ATTRIBUTE13,
194 X_ATTRIBUTE14,
195 X_ATTRIBUTE15
196 );
197
198 insert into CS_KB_HISTORIES_TL (
199 HISTORY_ID,
200 NAME,
201 DESCRIPTION,
202 CREATION_DATE,
203 CREATED_BY,
204 LAST_UPDATE_DATE,
205 LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN,
207 LANGUAGE,
208 SOURCE_LANG
209 ) select
210 X_HISTORY_ID,
211 X_NAME,
212 X_DESCRIPTION,
213 X_CREATION_DATE,
214 X_CREATED_BY,
215 X_LAST_UPDATE_DATE,
216 X_LAST_UPDATED_BY,
217 X_LAST_UPDATE_LOGIN,
218 L.LANGUAGE_CODE,
219 userenv('LANG')
220 from FND_LANGUAGES L
221 where L.INSTALLED_FLAG in ('I', 'B')
222 and not exists
223 (select NULL
224 from CS_KB_HISTORIES_TL T
225 where T.HISTORY_ID = X_HISTORY_ID
226 and T.LANGUAGE = L.LANGUAGE_CODE);
227
228 open c;
229 fetch c into X_ROWID;
230 if (c%notfound) then
231 close c;
232 raise no_data_found;
233 end if;
234 close c;
235
236 end INSERT_ROW;
237
238 procedure LOCK_ROW (
239 X_HISTORY_ID in NUMBER,
240 X_HISTORY_NAME in VARCHAR2,
241 X_USER_ID in NUMBER,
242 X_ENTRY_DATE in DATE,
243 X_NAME in VARCHAR2,
244 X_DESCRIPTION in VARCHAR2,
245 X_ATTRIBUTE_CATEGORY in VARCHAR2,
246 X_ATTRIBUTE1 in VARCHAR2,
247 X_ATTRIBUTE2 in VARCHAR2,
248 X_ATTRIBUTE3 in VARCHAR2,
249 X_ATTRIBUTE4 in VARCHAR2,
250 X_ATTRIBUTE5 in VARCHAR2,
251 X_ATTRIBUTE6 in VARCHAR2,
252 X_ATTRIBUTE7 in VARCHAR2,
253 X_ATTRIBUTE8 in VARCHAR2,
254 X_ATTRIBUTE9 in VARCHAR2,
255 X_ATTRIBUTE10 in VARCHAR2,
256 X_ATTRIBUTE11 in VARCHAR2,
257 X_ATTRIBUTE12 in VARCHAR2,
258 X_ATTRIBUTE13 in VARCHAR2,
259 X_ATTRIBUTE14 in VARCHAR2,
260 X_ATTRIBUTE15 in VARCHAR2
261 ) is
262 cursor c is select
263 HISTORY_ID,
264 HISTORY_NAME,
265 USER_ID,
266 ENTRY_DATE,
267 ATTRIBUTE_CATEGORY,
268 ATTRIBUTE1,
269 ATTRIBUTE2,
270 ATTRIBUTE3,
271 ATTRIBUTE4,
272 ATTRIBUTE5,
273 ATTRIBUTE6,
274 ATTRIBUTE7,
275 ATTRIBUTE8,
276 ATTRIBUTE9,
277 ATTRIBUTE10,
278 ATTRIBUTE11,
279 ATTRIBUTE12,
280 ATTRIBUTE13,
281 ATTRIBUTE14,
282 ATTRIBUTE15
283 from CS_KB_HISTORIES_B
284 where HISTORY_ID = X_HISTORY_ID
285 for update of HISTORY_ID nowait;
286 recinfo c%rowtype;
287
288 cursor c1 is select
289 NAME,
290 DESCRIPTION,
291 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
292 from CS_KB_HISTORIES_TL
293 where HISTORY_ID = X_HISTORY_ID
294 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
295 for update of HISTORY_ID nowait;
296 begin
297 open c;
298 fetch c into recinfo;
299 if (c%notfound) then
300 close c;
301 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
302 app_exception.raise_exception;
303 end if;
304 close c;
305 if (
306 ((recinfo.HISTORY_ID = X_HISTORY_ID)
307 OR ((recinfo.HISTORY_ID is null) AND (X_HISTORY_ID is null)))
308 AND ((recinfo.HISTORY_NAME = X_HISTORY_NAME)
309 OR ((recinfo.HISTORY_NAME is null) AND (X_HISTORY_NAME is null)))
310 AND ((recinfo.USER_ID = X_USER_ID)
311 OR ((recinfo.USER_ID is null) AND (X_USER_ID is null)))
312 AND ((recinfo.ENTRY_DATE = X_ENTRY_DATE)
313 OR ((recinfo.ENTRY_DATE is null) AND (X_ENTRY_DATE is null)))
314 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
315 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
316 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
317 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
318 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
319 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
320 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
321 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
322 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
323 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
324 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
325 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
326 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
327 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
328 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
329 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
330 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
331 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
332 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
333 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
334 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
335 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
336 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
337 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
338 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
339 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
340 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
341 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
342 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
343 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
344 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
345 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
346 ) then
347 null;
348 else
349 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
350 app_exception.raise_exception;
351 end if;
352
353 for tlinfo in c1 loop
354 if (tlinfo.BASELANG = 'Y') then
355 if ( ((tlinfo.NAME = X_NAME)
356 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
357 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
358 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
359 ) then
360 null;
361 else
362 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
363 app_exception.raise_exception;
364 end if;
365 end if;
366 end loop;
367 return;
368 end LOCK_ROW;
369
370 procedure UPDATE_ROW (
371 X_HISTORY_ID in NUMBER,
372 X_HISTORY_NAME in VARCHAR2,
373 X_USER_ID in NUMBER,
374 X_ENTRY_DATE in DATE,
375 X_NAME in VARCHAR2,
376 X_DESCRIPTION in VARCHAR2,
377 X_LAST_UPDATE_DATE in DATE,
378 X_LAST_UPDATED_BY in NUMBER,
379 X_LAST_UPDATE_LOGIN in NUMBER,
380 X_ATTRIBUTE_CATEGORY in VARCHAR2,
381 X_ATTRIBUTE1 in VARCHAR2,
382 X_ATTRIBUTE2 in VARCHAR2,
383 X_ATTRIBUTE3 in VARCHAR2,
384 X_ATTRIBUTE4 in VARCHAR2,
385 X_ATTRIBUTE5 in VARCHAR2,
386 X_ATTRIBUTE6 in VARCHAR2,
387 X_ATTRIBUTE7 in VARCHAR2,
388 X_ATTRIBUTE8 in VARCHAR2,
389 X_ATTRIBUTE9 in VARCHAR2,
390 X_ATTRIBUTE10 in VARCHAR2,
391 X_ATTRIBUTE11 in VARCHAR2,
392 X_ATTRIBUTE12 in VARCHAR2,
393 X_ATTRIBUTE13 in VARCHAR2,
394 X_ATTRIBUTE14 in VARCHAR2,
395 X_ATTRIBUTE15 in VARCHAR2
396 ) is
397 begin
398 update CS_KB_HISTORIES_B set
399 HISTORY_NAME = X_HISTORY_NAME,
400 USER_ID = X_USER_ID,
401 ENTRY_DATE = X_ENTRY_DATE,
402 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
403 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
404 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
405 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
406 ATTRIBUTE1 = X_ATTRIBUTE1,
407 ATTRIBUTE2 = X_ATTRIBUTE2,
408 ATTRIBUTE3 = X_ATTRIBUTE3,
409 ATTRIBUTE4 = X_ATTRIBUTE4,
410 ATTRIBUTE5 = X_ATTRIBUTE5,
411 ATTRIBUTE6 = X_ATTRIBUTE6,
412 ATTRIBUTE7 = X_ATTRIBUTE7,
413 ATTRIBUTE8 = X_ATTRIBUTE8,
414 ATTRIBUTE9 = X_ATTRIBUTE9,
415 ATTRIBUTE10 = X_ATTRIBUTE10,
416 ATTRIBUTE11 = X_ATTRIBUTE11,
417 ATTRIBUTE12 = X_ATTRIBUTE12,
418 ATTRIBUTE13 = X_ATTRIBUTE13,
419 ATTRIBUTE14 = X_ATTRIBUTE14,
420 ATTRIBUTE15 = X_ATTRIBUTE15
421 where HISTORY_ID = X_HISTORY_ID;
422
423 if (sql%notfound) then
424 raise no_data_found;
425 end if;
426
427 update CS_KB_HISTORIES_TL set
428 NAME = X_NAME,
432 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
429 DESCRIPTION = X_DESCRIPTION,
430 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
431 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
433 SOURCE_LANG = userenv('LANG')
434 where HISTORY_ID = X_HISTORY_ID
435 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
436
437 if (sql%notfound) then
438 raise no_data_found;
439 end if;
440 end UPDATE_ROW;
441
442 procedure DELETE_ROW (
443 X_HISTORY_ID in NUMBER
444 ) is
445 begin
446 delete from CS_KB_HISTORIES_TL
447 where HISTORY_ID = X_HISTORY_ID;
448
449 if (sql%notfound) then
450 raise no_data_found;
451 end if;
452
453 delete from CS_KB_HISTORIES_B
454 where HISTORY_ID = X_HISTORY_ID;
455
456 if (sql%notfound) then
457 raise no_data_found;
458 end if;
459 end DELETE_ROW;
460
461 procedure ADD_LANGUAGE
462 is
463 begin
464 delete from CS_KB_HISTORIES_TL T
465 where not exists
466 (select NULL
467 from CS_KB_HISTORIES_B B
468 where B.HISTORY_ID = T.HISTORY_ID
469 );
470
471 update CS_KB_HISTORIES_TL T set (
472 NAME,
473 DESCRIPTION
474 ) = (select
475 B.NAME,
476 B.DESCRIPTION
477 from CS_KB_HISTORIES_TL B
478 where B.HISTORY_ID = T.HISTORY_ID
479 and B.LANGUAGE = T.SOURCE_LANG)
480 where (
481 T.HISTORY_ID,
482 T.LANGUAGE
483 ) in (select
484 SUBT.HISTORY_ID,
485 SUBT.LANGUAGE
486 from CS_KB_HISTORIES_TL SUBB, CS_KB_HISTORIES_TL SUBT
487 where SUBB.HISTORY_ID = SUBT.HISTORY_ID
488 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
489 and (SUBB.NAME <> SUBT.NAME
490 or (SUBB.NAME is null and SUBT.NAME is not null)
491 or (SUBB.NAME is not null and SUBT.NAME is null)
492 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
493 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
494 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
495 ));
496
497 insert into CS_KB_HISTORIES_TL (
498 HISTORY_ID,
499 NAME,
500 DESCRIPTION,
501 CREATION_DATE,
502 CREATED_BY,
503 LAST_UPDATE_DATE,
504 LAST_UPDATED_BY,
505 LAST_UPDATE_LOGIN,
506 LANGUAGE,
507 SOURCE_LANG
508 ) select
509 B.HISTORY_ID,
510 B.NAME,
511 B.DESCRIPTION,
512 B.CREATION_DATE,
513 B.CREATED_BY,
514 B.LAST_UPDATE_DATE,
515 B.LAST_UPDATED_BY,
516 B.LAST_UPDATE_LOGIN,
517 L.LANGUAGE_CODE,
518 B.SOURCE_LANG
519 from CS_KB_HISTORIES_TL B, FND_LANGUAGES L
520 where L.INSTALLED_FLAG in ('I', 'B')
521 and B.LANGUAGE = userenv('LANG')
522 and not exists
523 (select NULL
524 from CS_KB_HISTORIES_TL T
525 where T.HISTORY_ID = B.HISTORY_ID
526 and T.LANGUAGE = L.LANGUAGE_CODE);
527 end ADD_LANGUAGE;
528
529 procedure TRANSLATE_ROW (
530 X_HISTORY_ID in VARCHAR2,
531 X_NAME in VARCHAR2,
532 X_DESCRIPTION in VARCHAR2,
533 X_OWNER in VARCHAR2
534 ) is
535 begin
536 update CS_KB_HISTORIES_TL set
537 NAME = X_NAME,
538 DESCRIPTION=X_DESCRIPTION,
539 last_update_date = sysdate,
540 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
541 last_update_login = 0,
542 source_lang = userenv('LANG')
543 where HISTORY_ID = to_number(X_HISTORY_ID)
544 and userenv('LANG') in (language, source_lang);
545 end TRANSLATE_ROW;
546
547 end CS_KB_HISTORIES_PKG;
[Home] [Help]