[Home] [Help]
PACKAGE BODY: APPS.CS_KB_USED_SUM_DEFS_PKG
Source
1 PACKAGE BODY CS_KB_USED_SUM_DEFS_PKG AS
2 /* $Header: cskbusdb.pls 115.9 2002/12/02 23:04:12 mkettle ship $ */
3
4
5 --
6 -- Create a new empty Used_Sum_Def and returns id.
7 --
8 FUNCTION Create_Used_Sum_Def(
9 p_days in NUMBER,
10 p_default_flag in VARCHAR2,
11 p_activated_flag in VARCHAR2,
12 p_name in varchar2,
13 p_desc in varchar2,
14 P_ATTRIBUTE_CATEGORY in VARCHAR2,
15 P_ATTRIBUTE1 in VARCHAR2,
16 P_ATTRIBUTE2 in VARCHAR2,
17 P_ATTRIBUTE3 in VARCHAR2,
18 P_ATTRIBUTE4 in VARCHAR2,
19 P_ATTRIBUTE5 in VARCHAR2,
20 P_ATTRIBUTE6 in VARCHAR2,
21 P_ATTRIBUTE7 in VARCHAR2,
22 P_ATTRIBUTE8 in VARCHAR2,
23 P_ATTRIBUTE9 in VARCHAR2,
24 P_ATTRIBUTE10 in VARCHAR2,
25 P_ATTRIBUTE11 in VARCHAR2,
26 P_ATTRIBUTE12 in VARCHAR2,
27 P_ATTRIBUTE13 in VARCHAR2,
28 P_ATTRIBUTE14 in VARCHAR2,
29 P_ATTRIBUTE15 in VARCHAR2
30 ) return number is
31 l_date date;
32 l_created_by number;
33 l_login number;
34 l_count pls_integer;
35 l_id number;
36 l_rowid varchar2(30);
37 begin
38
39 -- Check params
40 if(p_desc is null OR p_name is NULL) then
41 goto error_found;
42 end if;
43
44
45 --prepare data, then insert new def
46 select cs_kb_used_sum_defs_s.nextval into l_id from dual;
47 l_date := sysdate;
48 l_created_by := fnd_global.user_id;
49 l_login := fnd_global.login_id;
50
51 CS_KB_USED_SUM_DEFS_PKG.Insert_Row(
52 X_Rowid => l_rowid,
53 X_Def_Id => l_id,
54 X_days => p_days,
55 X_Default_Flag => p_default_flag,
56 X_Activated_Flag => p_activated_flag,
57 X_Name => p_name,
58 X_Description => p_desc,
59 X_Creation_Date => l_date,
60 X_Created_By => l_created_by,
61 X_Last_Update_Date => l_date,
62 X_Last_Updated_By => l_created_by,
63 X_Last_Update_Login => l_login,
64 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
65 X_ATTRIBUTE1 => P_ATTRIBUTE1,
66 X_ATTRIBUTE2 => P_ATTRIBUTE2,
67 X_ATTRIBUTE3 => P_ATTRIBUTE3,
68 X_ATTRIBUTE4 => P_ATTRIBUTE4,
69 X_ATTRIBUTE5 => P_ATTRIBUTE5,
70 X_ATTRIBUTE6 => P_ATTRIBUTE6,
71 X_ATTRIBUTE7 => P_ATTRIBUTE7,
72 X_ATTRIBUTE8 => P_ATTRIBUTE8,
73 X_ATTRIBUTE9 => P_ATTRIBUTE9,
74 X_ATTRIBUTE10 => P_ATTRIBUTE10,
75 X_ATTRIBUTE11 => P_ATTRIBUTE11,
76 X_ATTRIBUTE12 => P_ATTRIBUTE12,
77 X_ATTRIBUTE13 => P_ATTRIBUTE13,
78 X_ATTRIBUTE14 => P_ATTRIBUTE14,
79 X_ATTRIBUTE15 => P_ATTRIBUTE15
80 );
81 return l_id;
82
83 <<error_found>>
84 return ERROR_STATUS;
85 end Create_Used_Sum_Def;
86
87
88
89 -- Update Used_Sum_Def data
90 --
91 FUNCTION Update_Used_Sum_Def(
92 p_def_id in number,
93 p_days in NUMBER,
94 p_default_flag in VARCHAR2,
95 p_activated_flag in VARCHAR2,
96 p_name in varchar2,
97 p_desc in varchar2,
98 P_ATTRIBUTE_CATEGORY in VARCHAR2,
99 P_ATTRIBUTE1 in VARCHAR2,
100 P_ATTRIBUTE2 in VARCHAR2,
101 P_ATTRIBUTE3 in VARCHAR2,
102 P_ATTRIBUTE4 in VARCHAR2,
103 P_ATTRIBUTE5 in VARCHAR2,
104 P_ATTRIBUTE6 in VARCHAR2,
105 P_ATTRIBUTE7 in VARCHAR2,
106 P_ATTRIBUTE8 in VARCHAR2,
107 P_ATTRIBUTE9 in VARCHAR2,
108 P_ATTRIBUTE10 in VARCHAR2,
109 P_ATTRIBUTE11 in VARCHAR2,
110 P_ATTRIBUTE12 in VARCHAR2,
111 P_ATTRIBUTE13 in VARCHAR2,
112 P_ATTRIBUTE14 in VARCHAR2,
113 P_ATTRIBUTE15 in VARCHAR2
114 ) return number is
115 l_ret number;
116 l_date date;
117 l_updated_by number;
118 l_login number;
119 l_count pls_integer;
120 begin
121
122 -- validate params
123 if(p_def_id is null) then
124 goto error_found;
125 end if;
126
127 --prepare data, then insert
128 l_date := sysdate;
129 l_updated_by := fnd_global.user_id;
130 l_login := fnd_global.login_id;
131
132 CS_KB_USED_SUM_DEFS_PKG.Update_Row(
133 X_def_Id => p_def_id,
134 X_days => p_days,
135 X_Default_Flag => p_default_flag,
136 X_Activated_Flag => p_activated_flag,
137 X_Name => p_name,
138 X_Description => p_desc,
139 X_Last_Update_Date => l_date,
140 X_Last_Updated_By => l_updated_by,
141 X_Last_Update_Login => l_login,
142 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
143 X_ATTRIBUTE1 => P_ATTRIBUTE1,
144 X_ATTRIBUTE2 => P_ATTRIBUTE2,
145 X_ATTRIBUTE3 => P_ATTRIBUTE3,
146 X_ATTRIBUTE4 => P_ATTRIBUTE4,
147 X_ATTRIBUTE5 => P_ATTRIBUTE5,
148 X_ATTRIBUTE6 => P_ATTRIBUTE6,
149 X_ATTRIBUTE7 => P_ATTRIBUTE7,
150 X_ATTRIBUTE8 => P_ATTRIBUTE8,
151 X_ATTRIBUTE9 => P_ATTRIBUTE9,
152 X_ATTRIBUTE10 => P_ATTRIBUTE10,
153 X_ATTRIBUTE11 => P_ATTRIBUTE11,
154 X_ATTRIBUTE12 => P_ATTRIBUTE12,
155 X_ATTRIBUTE13 => P_ATTRIBUTE13,
156 X_ATTRIBUTE14 => P_ATTRIBUTE14,
157 X_ATTRIBUTE15 => P_ATTRIBUTE15);
158
159 return OKAY_STATUS;
160 <<error_found>>
161 return ERROR_STATUS;
162 exception
163 when others then
164 return ERROR_STATUS;
165 end Update_Used_Sum_Def;
166
167 --
168 -- Delete
169
170 FUNCTION Delete_Used_Sum_Def(
171 p_def_id in number
172 ) return number is
173 l_ret number;
174 l_count pls_integer;
175 begin
176 if p_def_id is null or p_def_id <= 0 then return ERROR_STATUS; end if;
177
178 select count(*) into l_count
179 from cs_kb_used_sum_defs_b
180 where def_id = p_def_id;
181 if(l_count <= 0) then return ERROR_STATUS; end if;
182
183 CS_KB_USED_SUM_DEFS_PKG.Delete_Row(
184 X_Def_Id => p_def_id);
185
186 return OKAY_STATUS;
187 <<error_found>>
188 return ERROR_STATUS;
189
190 exception
191 WHEN OTHERS THEN
192 return ERROR_STATUS;
193 end Delete_Used_Sum_Def;
194
195
196 procedure INSERT_ROW (
197 X_ROWID in OUT NOCOPY VARCHAR2,
198 X_DEF_ID in NUMBER,
199 X_DAYS in NUMBER,
200 X_DEFAULT_FLAG in VARCHAR2,
201 X_ACTIVATED_FLAG in VARCHAR2,
202 X_ATTRIBUTE_CATEGORY in VARCHAR2,
203 X_ATTRIBUTE1 in VARCHAR2,
204 X_ATTRIBUTE2 in VARCHAR2,
205 X_ATTRIBUTE3 in VARCHAR2,
206 X_ATTRIBUTE4 in VARCHAR2,
207 X_ATTRIBUTE5 in VARCHAR2,
208 X_ATTRIBUTE6 in VARCHAR2,
209 X_ATTRIBUTE7 in VARCHAR2,
210 X_ATTRIBUTE8 in VARCHAR2,
211 X_ATTRIBUTE9 in VARCHAR2,
212 X_ATTRIBUTE10 in VARCHAR2,
213 X_ATTRIBUTE11 in VARCHAR2,
214 X_ATTRIBUTE12 in VARCHAR2,
215 X_ATTRIBUTE13 in VARCHAR2,
216 X_ATTRIBUTE14 in VARCHAR2,
217 X_ATTRIBUTE15 in VARCHAR2,
218 X_NAME in VARCHAR2,
219 X_DESCRIPTION in VARCHAR2,
220 X_CREATION_DATE in DATE,
221 X_CREATED_BY in NUMBER,
222 X_LAST_UPDATE_DATE in DATE,
223 X_LAST_UPDATED_BY in NUMBER,
224 X_LAST_UPDATE_LOGIN in NUMBER
225 ) is
226 cursor C is select ROWID from CS_KB_USED_SUM_DEFS_B
227 where DEF_ID = X_DEF_ID
228 ;
229 begin
230 insert into CS_KB_USED_SUM_DEFS_B (
231 DEF_ID,
232 DAYS,
233 DEFAULT_FLAG,
234 ACTIVATED_FLAG,
235 ATTRIBUTE_CATEGORY,
236 ATTRIBUTE1,
237 ATTRIBUTE2,
238 ATTRIBUTE3,
239 ATTRIBUTE4,
240 ATTRIBUTE5,
241 ATTRIBUTE6,
242 ATTRIBUTE7,
243 ATTRIBUTE8,
244 ATTRIBUTE9,
245 ATTRIBUTE10,
246 ATTRIBUTE11,
247 ATTRIBUTE12,
248 ATTRIBUTE13,
249 ATTRIBUTE14,
250 ATTRIBUTE15,
251 CREATION_DATE,
252 CREATED_BY,
253 LAST_UPDATE_DATE,
254 LAST_UPDATED_BY,
255 LAST_UPDATE_LOGIN
256 ) values (
257 X_DEF_ID,
258 X_DAYS,
259 X_DEFAULT_FLAG,
260 X_ACTIVATED_FLAG,
261 X_ATTRIBUTE_CATEGORY,
262 X_ATTRIBUTE1,
263 X_ATTRIBUTE2,
264 X_ATTRIBUTE3,
265 X_ATTRIBUTE4,
266 X_ATTRIBUTE5,
267 X_ATTRIBUTE6,
268 X_ATTRIBUTE7,
269 X_ATTRIBUTE8,
270 X_ATTRIBUTE9,
271 X_ATTRIBUTE10,
272 X_ATTRIBUTE11,
273 X_ATTRIBUTE12,
274 X_ATTRIBUTE13,
275 X_ATTRIBUTE14,
276 X_ATTRIBUTE15,
277 X_CREATION_DATE,
278 X_CREATED_BY,
279 X_LAST_UPDATE_DATE,
280 X_LAST_UPDATED_BY,
281 X_LAST_UPDATE_LOGIN
282 );
283
284 insert into CS_KB_USED_SUM_DEFS_TL (
285 DEF_ID,
286 NAME,
287 DESCRIPTION,
288 CREATION_DATE,
289 CREATED_BY,
290 LAST_UPDATE_DATE,
291 LAST_UPDATED_BY,
292 LAST_UPDATE_LOGIN,
293 LANGUAGE,
294 SOURCE_LANG
295 ) select
296 X_DEF_ID,
297 X_NAME,
298 X_DESCRIPTION,
299 X_CREATION_DATE,
300 X_CREATED_BY,
301 X_LAST_UPDATE_DATE,
302 X_LAST_UPDATED_BY,
303 X_LAST_UPDATE_LOGIN,
304 L.LANGUAGE_CODE,
305 userenv('LANG')
306 from FND_LANGUAGES L
307 where L.INSTALLED_FLAG in ('I', 'B')
308 and not exists
309 (select NULL
310 from CS_KB_USED_SUM_DEFS_TL T
311 where T.DEF_ID = X_DEF_ID
312 and T.LANGUAGE = L.LANGUAGE_CODE);
313
314 open c;
315 fetch c into X_ROWID;
316 if (c%notfound) then
317 close c;
318 raise no_data_found;
319 end if;
320 close c;
321
322 end INSERT_ROW;
323
324 procedure LOCK_ROW (
325 X_DEF_ID in NUMBER,
326 X_DAYS in NUMBER,
327 X_DEFAULT_FLAG in VARCHAR2,
328 X_ACTIVATED_FLAG in VARCHAR2,
329 X_ATTRIBUTE_CATEGORY in VARCHAR2,
330 X_ATTRIBUTE1 in VARCHAR2,
331 X_ATTRIBUTE2 in VARCHAR2,
332 X_ATTRIBUTE3 in VARCHAR2,
333 X_ATTRIBUTE4 in VARCHAR2,
334 X_ATTRIBUTE5 in VARCHAR2,
335 X_ATTRIBUTE6 in VARCHAR2,
336 X_ATTRIBUTE7 in VARCHAR2,
337 X_ATTRIBUTE8 in VARCHAR2,
338 X_ATTRIBUTE9 in VARCHAR2,
339 X_ATTRIBUTE10 in VARCHAR2,
340 X_ATTRIBUTE11 in VARCHAR2,
341 X_ATTRIBUTE12 in VARCHAR2,
342 X_ATTRIBUTE13 in VARCHAR2,
343 X_ATTRIBUTE14 in VARCHAR2,
344 X_ATTRIBUTE15 in VARCHAR2,
345 X_NAME in VARCHAR2,
346 X_DESCRIPTION in VARCHAR2
347 ) is
348 cursor c is select
349 DAYS,
350 DEFAULT_FLAG,
351 ACTIVATED_FLAG,
352 ATTRIBUTE_CATEGORY,
353 ATTRIBUTE1,
354 ATTRIBUTE2,
355 ATTRIBUTE3,
356 ATTRIBUTE4,
357 ATTRIBUTE5,
358 ATTRIBUTE6,
359 ATTRIBUTE7,
360 ATTRIBUTE8,
361 ATTRIBUTE9,
362 ATTRIBUTE10,
363 ATTRIBUTE11,
364 ATTRIBUTE12,
365 ATTRIBUTE13,
366 ATTRIBUTE14,
367 ATTRIBUTE15
368 from CS_KB_USED_SUM_DEFS_B
369 where DEF_ID = X_DEF_ID
370 for update of DEF_ID nowait;
371 recinfo c%rowtype;
372
373 cursor c1 is select
374 NAME,
375 DESCRIPTION,
376 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
377 from CS_KB_USED_SUM_DEFS_TL
378 where DEF_ID = X_DEF_ID
379 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
380 for update of DEF_ID nowait;
381 begin
382 open c;
383 fetch c into recinfo;
384 if (c%notfound) then
385 close c;
386 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
387 app_exception.raise_exception;
388 end if;
389 close c;
390 if ( ((recinfo.DAYS = X_DAYS)
391 OR ((recinfo.DAYS is null) AND (X_DAYS is null)))
392 AND ((recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
393 OR ((recinfo.DEFAULT_FLAG is null) AND (X_DEFAULT_FLAG is null)))
394 AND ((recinfo.ACTIVATED_FLAG = X_ACTIVATED_FLAG)
395 OR ((recinfo.ACTIVATED_FLAG is null) AND (X_ACTIVATED_FLAG is null)))
396 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
397 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
398 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
399 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
400 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
401 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
402 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
403 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
404 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
405 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
406 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
407 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
408 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
409 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
410 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
411 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
412 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
413 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
414 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
415 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
416 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
417 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
418 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
419 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
420 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
421 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
422 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
423 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
424 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
425 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
426 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
427 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
428 ) then
429 null;
430 else
431 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
432 app_exception.raise_exception;
433 end if;
434
435 for tlinfo in c1 loop
436 if (tlinfo.BASELANG = 'Y') then
437 if ( ((tlinfo.NAME = X_NAME)
438 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
439 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
440 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
441 ) then
442 null;
443 else
444 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
445 app_exception.raise_exception;
446 end if;
447 end if;
448 end loop;
449 return;
450 end LOCK_ROW;
451
452 procedure UPDATE_ROW (
453 X_DEF_ID in NUMBER,
454 X_DAYS in NUMBER,
455 X_DEFAULT_FLAG in VARCHAR2,
456 X_ACTIVATED_FLAG in VARCHAR2,
457 X_ATTRIBUTE_CATEGORY in VARCHAR2,
458 X_ATTRIBUTE1 in VARCHAR2,
459 X_ATTRIBUTE2 in VARCHAR2,
460 X_ATTRIBUTE3 in VARCHAR2,
461 X_ATTRIBUTE4 in VARCHAR2,
462 X_ATTRIBUTE5 in VARCHAR2,
463 X_ATTRIBUTE6 in VARCHAR2,
464 X_ATTRIBUTE7 in VARCHAR2,
465 X_ATTRIBUTE8 in VARCHAR2,
466 X_ATTRIBUTE9 in VARCHAR2,
467 X_ATTRIBUTE10 in VARCHAR2,
468 X_ATTRIBUTE11 in VARCHAR2,
469 X_ATTRIBUTE12 in VARCHAR2,
470 X_ATTRIBUTE13 in VARCHAR2,
471 X_ATTRIBUTE14 in VARCHAR2,
472 X_ATTRIBUTE15 in VARCHAR2,
473 X_NAME in VARCHAR2,
474 X_DESCRIPTION in VARCHAR2,
475 X_LAST_UPDATE_DATE in DATE,
476 X_LAST_UPDATED_BY in NUMBER,
480 update CS_KB_USED_SUM_DEFS_B set
477 X_LAST_UPDATE_LOGIN in NUMBER
478 ) is
479 begin
481 DAYS = X_DAYS,
482 DEFAULT_FLAG = X_DEFAULT_FLAG,
483 ACTIVATED_FLAG = X_ACTIVATED_FLAG,
484 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
485 ATTRIBUTE1 = X_ATTRIBUTE1,
486 ATTRIBUTE2 = X_ATTRIBUTE2,
487 ATTRIBUTE3 = X_ATTRIBUTE3,
488 ATTRIBUTE4 = X_ATTRIBUTE4,
489 ATTRIBUTE5 = X_ATTRIBUTE5,
490 ATTRIBUTE6 = X_ATTRIBUTE6,
491 ATTRIBUTE7 = X_ATTRIBUTE7,
492 ATTRIBUTE8 = X_ATTRIBUTE8,
493 ATTRIBUTE9 = X_ATTRIBUTE9,
494 ATTRIBUTE10 = X_ATTRIBUTE10,
495 ATTRIBUTE11 = X_ATTRIBUTE11,
496 ATTRIBUTE12 = X_ATTRIBUTE12,
497 ATTRIBUTE13 = X_ATTRIBUTE13,
498 ATTRIBUTE14 = X_ATTRIBUTE14,
499 ATTRIBUTE15 = X_ATTRIBUTE15,
500 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
501 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
502 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
503 where DEF_ID = X_DEF_ID;
504
505 if (sql%notfound) then
506 raise no_data_found;
507 end if;
508
509 update CS_KB_USED_SUM_DEFS_TL set
510 NAME = X_NAME,
511 DESCRIPTION = X_DESCRIPTION,
512 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
513 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
514 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
515 SOURCE_LANG = userenv('LANG')
516 where DEF_ID = X_DEF_ID
517 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
518
519 if (sql%notfound) then
520 raise no_data_found;
521 end if;
522 end UPDATE_ROW;
523
524 procedure DELETE_ROW (
525 X_DEF_ID in NUMBER
526 ) is
527 begin
528 delete from CS_KB_USED_SUM_DEFS_TL
529 where DEF_ID = X_DEF_ID;
530
531 if (sql%notfound) then
532 raise no_data_found;
533 end if;
534
535 delete from CS_KB_USED_SUM_DEFS_B
536 where DEF_ID = X_DEF_ID;
537
538 if (sql%notfound) then
539 raise no_data_found;
540 end if;
541 end DELETE_ROW;
542
543 procedure ADD_LANGUAGE
544 is
545 begin
546 delete from CS_KB_USED_SUM_DEFS_TL T
547 where not exists
548 (select NULL
549 from CS_KB_USED_SUM_DEFS_B B
550 where B.DEF_ID = T.DEF_ID
551 );
552
553 update CS_KB_USED_SUM_DEFS_TL T set (
554 NAME,
555 DESCRIPTION
556 ) = (select
557 B.NAME,
558 B.DESCRIPTION
559 from CS_KB_USED_SUM_DEFS_TL B
560 where B.DEF_ID = T.DEF_ID
561 and B.LANGUAGE = T.SOURCE_LANG)
562 where (
563 T.DEF_ID,
564 T.LANGUAGE
565 ) in (select
566 SUBT.DEF_ID,
567 SUBT.LANGUAGE
568 from CS_KB_USED_SUM_DEFS_TL SUBB, CS_KB_USED_SUM_DEFS_TL SUBT
569 where SUBB.DEF_ID = SUBT.DEF_ID
570 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
571 and (SUBB.NAME <> SUBT.NAME
572 or (SUBB.NAME is null and SUBT.NAME is not null)
573 or (SUBB.NAME is not null and SUBT.NAME is null)
574 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
575 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
576 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
577 ));
578
579 insert into CS_KB_USED_SUM_DEFS_TL (
580 DEF_ID,
581 NAME,
582 DESCRIPTION,
583 CREATION_DATE,
584 CREATED_BY,
585 LAST_UPDATE_DATE,
586 LAST_UPDATED_BY,
587 LAST_UPDATE_LOGIN,
588 LANGUAGE,
589 SOURCE_LANG
590 ) select
591 B.DEF_ID,
592 B.NAME,
593 B.DESCRIPTION,
594 B.CREATION_DATE,
595 B.CREATED_BY,
596 B.LAST_UPDATE_DATE,
597 B.LAST_UPDATED_BY,
598 B.LAST_UPDATE_LOGIN,
599 L.LANGUAGE_CODE,
600 B.SOURCE_LANG
601 from CS_KB_USED_SUM_DEFS_TL B, FND_LANGUAGES L
602 where L.INSTALLED_FLAG in ('I', 'B')
603 and B.LANGUAGE = userenv('LANG')
604 and not exists
605 (select NULL
606 from CS_KB_USED_SUM_DEFS_TL T
607 where T.DEF_ID = B.DEF_ID
608 and T.LANGUAGE = L.LANGUAGE_CODE);
609 end ADD_LANGUAGE;
610
611 PROCEDURE TRANSLATE_ROW(
612 x_def_id in number,
613 x_days in number,
614 x_default_flag in varchar,
615 x_activated_flag in varchar,
616 x_owner in varchar2,
617 x_name in varchar2,
618 x_description in varchar2) is
619
620 begin
621
622 update CS_KB_USED_SUM_DEFS_TL set
623 NAME = x_name,
624 DESCRIPTION = x_description,
625 LAST_UPDATE_DATE = sysdate,
626 LAST_UPDATED_BY = decode(x_owner, 'SEED',1, 0),
627 LAST_UPDATE_LOGIN = 0,
628 SOURCE_LANG = userenv('LANG')
629
630 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
631 and DEF_ID = X_DEF_ID;
632
633
634 if (sql%notfound) then
635 raise no_data_found;
636 end if;
637
638 end;
639
640 PROCEDURE LOAD_ROW(
641 x_def_id in number,
642 x_days in number,
643 x_default_flag in varchar,
647 x_description in varchar2) is
644 x_activated_flag in varchar,
645 x_owner in varchar2,
646 x_name in varchar2,
648 l_user_id number;
649 l_rowid varchar2(100);
650 begin
651
652 if (x_owner = 'SEED') then
653 l_user_id := 1;
654 else
655 l_user_id := 0;
656 end if;
657
658 CS_KB_USED_SUM_DEFS_PKG.Update_Row(
659 X_Def_Id => x_def_id,
660 X_Days => x_days,
661 X_Default_Flag => x_default_flag,
662 X_Activated_Flag => x_activated_flag,
663 X_Attribute_Category => null,
664 X_Attribute1 => null,
665 X_Attribute2 => null,
666 X_Attribute3 => null,
667 X_Attribute4 => null,
668 X_Attribute5 => null,
669 X_Attribute6 => null,
670 X_Attribute7 => null,
674 X_Attribute11 => null,
671 X_Attribute8 => null,
672 X_Attribute9 => null,
673 X_Attribute10 => null,
675 X_Attribute12 => null,
676 X_Attribute13 => null,
677 X_Attribute14 => null,
678 X_Attribute15 => null,
679 X_Name => x_name,
680 X_Description => x_description,
681 X_Last_Update_Date => sysdate,
682 X_Last_Updated_By => l_user_id,
683 X_Last_Update_Login => 0);
684
685 exception
686 when no_data_found then
687 CS_KB_USED_SUM_DEFS_PKG.Insert_Row(
688 X_Rowid => l_rowid,
689 X_Def_Id => x_def_id,
690 X_Days => x_days,
691 X_Default_Flag => x_default_flag,
692 X_Activated_Flag => x_activated_flag,
693 X_Attribute_Category => null,
694 X_Attribute1 => null,
695 X_Attribute2 => null,
696 X_Attribute3 => null,
697 X_Attribute4 => null,
698 X_Attribute5 => null,
699 X_Attribute6 => null,
700 X_Attribute7 => null,
701 X_Attribute8 => null,
702 X_Attribute9 => null,
703 X_Attribute10 => null,
704 X_Attribute11 => null,
705 X_Attribute12 => null,
706 X_Attribute13 => null,
707 X_Attribute14 => null,
708 X_Attribute15 => null,
709 X_Name => x_name,
710 X_Description => x_description,
711 X_Creation_Date => sysdate,
712 X_Created_By => l_user_id,
713 X_Last_Update_Date => sysdate,
714 X_Last_Updated_By => l_user_id,
715 X_Last_Update_Login => 0);
716
717 end;
718
719
720 end CS_KB_USED_SUM_DEFS_PKG;