[Home] [Help]
PACKAGE BODY: APPS.CS_TP_TEMPLATES_PKG
Source
1 package body CS_TP_TEMPLATES_PKG as
2 /* $Header: cstptemb.pls 115.9 2002/12/04 18:52:53 wzli noship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME |
8 | DESCRIPTION |
9 | PL/SQL body for package: CS_TP_TEMPLATES_PKG |
10 | |
11 | HISTORY |
12 | 03-11-2002 Adding two new column: UNI_QUESTION_NOTE_FLAG, |
13 | UNI_QUESTION_NOTE_TYPE |
14 | 12-APR-2002 KLOU |
15 | 1. Modify Update_Row to check fnd_api.g_miss_XXX and null |
16 | when updating the table. |
17 | 15-MAY-2002 KLOU |
18 | 1. Modify Insert_Row to null into uni_question_note_flag |
19 | and uni_question_note_type if they are |
20 | fnd_api.g_miss_char. |
21 | 115.9 03-DEC-2002 WZLI changed OUT and IN OUT calls to use NOCOPY hint|
22 | to enable pass by reference. |
23 | |
24 +==========================================================================*/
25 procedure INSERT_ROW (
26 X_ROWID in out NOCOPY VARCHAR2,
27 X_TEMPLATE_ID in NUMBER,
28 X_DEFAULT_FLAG in VARCHAR2,
29 X_START_DATE_ACTIVE in DATE,
30 X_END_DATE_ACTIVE in DATE,
31 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
32 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
33 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
34 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
35 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
36 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
37 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
38 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
39 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
40 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
41 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
42 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
43 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
44 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
45 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
46 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
47
48 X_UNI_QUESTION_NOTE_FLAG in VARCHAR2 DEFAULT NULL,
49 X_UNI_QUESTION_NOTE_TYPE in VARCHAR2 DEFAULT NULL,
50
51 X_NAME in VARCHAR2,
52 X_DESCRIPTION in VARCHAR2,
53 X_CREATION_DATE in DATE,
54 X_CREATED_BY in NUMBER,
55 X_LAST_UPDATE_DATE in DATE,
56 X_LAST_UPDATED_BY in NUMBER,
57 X_LAST_UPDATE_LOGIN in NUMBER
58 ) is
59 cursor C is select ROWID from CS_TP_TEMPLATES_B
60 where TEMPLATE_ID = X_TEMPLATE_ID
61 ;
62 begin
63 insert into CS_TP_TEMPLATES_B (
64 TEMPLATE_ID,
65 DEFAULT_FLAG,
66 START_DATE_ACTIVE,
67 END_DATE_ACTIVE,
68 ATTRIBUTE_CATEGORY,
69 ATTRIBUTE1,
70 ATTRIBUTE2,
71 ATTRIBUTE3,
72 ATTRIBUTE4,
73 ATTRIBUTE5,
74 ATTRIBUTE6,
75 ATTRIBUTE7,
76 ATTRIBUTE8,
77 ATTRIBUTE9,
78 ATTRIBUTE10,
79 ATTRIBUTE11,
80 ATTRIBUTE12,
81 ATTRIBUTE13,
82 ATTRIBUTE14,
83 ATTRIBUTE15,
84
85 UNI_QUESTION_NOTE_FLAG ,
86 UNI_QUESTION_NOTE_TYPE ,
87
88 CREATION_DATE,
89 CREATED_BY,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN
93 ) values (
94 X_TEMPLATE_ID,
95 X_DEFAULT_FLAG,
96 X_START_DATE_ACTIVE,
97 X_END_DATE_ACTIVE,
98 X_ATTRIBUTE_CATEGORY,
99 X_ATTRIBUTE1,
100 X_ATTRIBUTE2,
101 X_ATTRIBUTE3,
102 X_ATTRIBUTE4,
103 X_ATTRIBUTE5,
104 X_ATTRIBUTE6,
105 X_ATTRIBUTE7,
106 X_ATTRIBUTE8,
107 X_ATTRIBUTE9,
108 X_ATTRIBUTE10,
109 X_ATTRIBUTE11,
110 X_ATTRIBUTE12,
111 X_ATTRIBUTE13,
112 X_ATTRIBUTE14,
113 X_ATTRIBUTE15,
114 decode(X_UNI_QUESTION_NOTE_FLAG, fnd_api.g_miss_char, null, X_UNI_QUESTION_NOTE_FLAG),
115 decode(X_UNI_QUESTION_NOTE_TYPE, fnd_api.g_miss_char, null, X_UNI_QUESTION_NOTE_TYPE),
116 X_CREATION_DATE,
117 X_CREATED_BY,
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATED_BY,
120 X_LAST_UPDATE_LOGIN
121 );
122
123 insert into CS_TP_TEMPLATES_TL (
124 TEMPLATE_ID,
125 NAME,
126 DESCRIPTION,
127 CREATION_DATE,
128 CREATED_BY,
129 LAST_UPDATE_DATE,
130 LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN,
132 LANGUAGE,
133 SOURCE_LANG
134 ) select
135 X_TEMPLATE_ID,
136 X_NAME,
137 X_DESCRIPTION,
138 X_CREATION_DATE,
139 X_CREATED_BY,
140 X_LAST_UPDATE_DATE,
141 X_LAST_UPDATED_BY,
142 X_LAST_UPDATE_LOGIN,
143 L.LANGUAGE_CODE,
144 userenv('LANG')
145 from FND_LANGUAGES L
146 where L.INSTALLED_FLAG in ('I', 'B')
147 and not exists
148 (select NULL
149 from CS_TP_TEMPLATES_TL T
150 where T.TEMPLATE_ID = X_TEMPLATE_ID
151 and T.LANGUAGE = L.LANGUAGE_CODE);
152
153 open c;
154 fetch c into X_ROWID;
155 if (c%notfound) then
156 close c;
157 raise no_data_found;
158 end if;
159 close c;
160
161 end INSERT_ROW;
162
163 procedure LOCK_ROW (
164 X_TEMPLATE_ID in NUMBER,
165 X_DEFAULT_FLAG in VARCHAR2,
166 X_START_DATE_ACTIVE in DATE,
167 X_END_DATE_ACTIVE in DATE,
168 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
169 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
170 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
171 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
172 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
173 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
174 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
175 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
176 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
177 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
178 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
179 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
180 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
181 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
182 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
183 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
184
185 X_UNI_QUESTION_NOTE_FLAG in VARCHAR2 DEFAULT NULL,
186 X_UNI_QUESTION_NOTE_TYPE in VARCHAR2 DEFAULT NULL,
187
188 X_NAME in VARCHAR2,
189 X_DESCRIPTION in VARCHAR2
190 ) is
191 cursor c is select
192 DEFAULT_FLAG,
193 START_DATE_ACTIVE,
194 END_DATE_ACTIVE,
195 ATTRIBUTE_CATEGORY,
196 ATTRIBUTE1,
197 ATTRIBUTE2,
198 ATTRIBUTE3,
199 ATTRIBUTE4,
200 ATTRIBUTE5,
201 ATTRIBUTE6,
202 ATTRIBUTE7,
203 ATTRIBUTE8,
204 ATTRIBUTE9,
205 ATTRIBUTE10,
206 ATTRIBUTE11,
207 ATTRIBUTE12,
208 ATTRIBUTE13,
209 ATTRIBUTE14,
210 ATTRIBUTE15,
211
212 UNI_QUESTION_NOTE_FLAG ,
213 UNI_QUESTION_NOTE_TYPE
214
215 from CS_TP_TEMPLATES_B
216 where TEMPLATE_ID = X_TEMPLATE_ID
217 for update of TEMPLATE_ID nowait;
218 recinfo c%rowtype;
219
220 cursor c1 is select
221 NAME,
222 DESCRIPTION,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from CS_TP_TEMPLATES_TL
225 where TEMPLATE_ID = X_TEMPLATE_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of TEMPLATE_ID nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( ((recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
238 OR ((recinfo.DEFAULT_FLAG is null) AND (X_DEFAULT_FLAG is null)))
239 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
240 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
241 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
242 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
243 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
244 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
245 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
246 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
247 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
248 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
249 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
250 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
251 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
252 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
253 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
254 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
255 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
256 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
257 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
258 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
259 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
260 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
261 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
262 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
263 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
264 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
265 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
266 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
267 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
268 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
269 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
270 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
271 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
272 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
273 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
274 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
275
276 AND ((recinfo.UNI_QUESTION_NOTE_FLAG = X_UNI_QUESTION_NOTE_FLAG)
277 OR ((recinfo.UNI_QUESTION_NOTE_FLAG is null) AND (X_UNI_QUESTION_NOTE_FLAG is null)))
278 AND ((recinfo.UNI_QUESTION_NOTE_TYPE = X_UNI_QUESTION_NOTE_TYPE)
279 OR ((recinfo.UNI_QUESTION_NOTE_TYPE is null) AND (X_UNI_QUESTION_NOTE_TYPE is null)))
280
281
282 ) then
283 null;
284 else
285 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
286 app_exception.raise_exception;
287 end if;
288
289 for tlinfo in c1 loop
290 if (tlinfo.BASELANG = 'Y') then
291 if ( (tlinfo.NAME = X_NAME)
292 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
293 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
294 ) then
295 null;
296 else
297 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
298 app_exception.raise_exception;
299 end if;
300 end if;
301 end loop;
302 return;
303 end LOCK_ROW;
304
305 procedure UPDATE_ROW (
306 X_TEMPLATE_ID in NUMBER,
307 X_DEFAULT_FLAG in VARCHAR2,
308 X_START_DATE_ACTIVE in DATE,
309 X_END_DATE_ACTIVE in DATE,
310 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
311 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
312 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
313 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
314 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
315 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
316 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
317 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
318 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
319 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
320 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
321 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
322 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
323 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
324 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
325 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
326
327 X_UNI_QUESTION_NOTE_FLAG in VARCHAR2 DEFAULT NULL,
328 X_UNI_QUESTION_NOTE_TYPE in VARCHAR2 DEFAULT NULL,
329
330 X_NAME in VARCHAR2,
331 X_DESCRIPTION in VARCHAR2,
332 X_LAST_UPDATE_DATE in DATE,
333 X_LAST_UPDATED_BY in NUMBER,
334 X_LAST_UPDATE_LOGIN in NUMBER
335 ) is
336 begin
337 /*
338 update CS_TP_TEMPLATES_B set
339 DEFAULT_FLAG = X_DEFAULT_FLAG,
340 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
341 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
342 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
343 ATTRIBUTE1 = X_ATTRIBUTE1,
344 ATTRIBUTE2 = X_ATTRIBUTE2,
345 ATTRIBUTE3 = X_ATTRIBUTE3,
346 ATTRIBUTE4 = X_ATTRIBUTE4,
347 ATTRIBUTE5 = X_ATTRIBUTE5,
348 ATTRIBUTE6 = X_ATTRIBUTE6,
349 ATTRIBUTE7 = X_ATTRIBUTE7,
350 ATTRIBUTE8 = X_ATTRIBUTE8,
351 ATTRIBUTE9 = X_ATTRIBUTE9,
352 ATTRIBUTE10 = X_ATTRIBUTE10,
353 ATTRIBUTE11 = X_ATTRIBUTE11,
354 ATTRIBUTE12 = X_ATTRIBUTE12,
355 ATTRIBUTE13 = X_ATTRIBUTE13,
356 ATTRIBUTE14 = X_ATTRIBUTE14,
357 ATTRIBUTE15 = X_ATTRIBUTE15,
358 UNI_QUESTION_NOTE_FLAG = X_UNI_QUESTION_NOTE_FLAG,
359 UNI_QUESTION_NOTE_TYPE = X_UNI_QUESTION_NOTE_TYPE,
360 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
361 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
362 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
363 where TEMPLATE_ID = X_TEMPLATE_ID;
364 */
365 Update CS_TP_TEMPLATES_B set
366 DEFAULT_FLAG = DECODE(NVL(X_DEFAULT_FLAG,FND_API.G_MISS_CHAR),
367 FND_API.G_MISS_CHAR, DEFAULT_FLAG, X_DEFAULT_FLAG),
368 START_DATE_ACTIVE = DECODE(NVL(X_START_DATE_ACTIVE, FND_API.G_MISS_DATE),
369 FND_API.G_MISS_DATE, START_DATE_ACTIVE, X_START_DATE_ACTIVE),
370 END_DATE_ACTIVE = DECODE(NVL(X_END_DATE_ACTIVE, FND_API.G_MISS_DATE),
374 ATTRIBUTE1 = DECODE(NVL(X_ATTRIBUTE1, FND_API.G_MISS_CHAR),
371 FND_API.G_MISS_DATE, END_DATE_ACTIVE, X_END_DATE_ACTIVE),
372 ATTRIBUTE_CATEGORY = DECODE(NVL(X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR),
373 FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, X_ATTRIBUTE_CATEGORY),
375 FND_API.G_MISS_CHAR, ATTRIBUTE1, X_ATTRIBUTE1 ),
376 ATTRIBUTE2 = DECODE(NVL(X_ATTRIBUTE2, FND_API.G_MISS_CHAR),
377 FND_API.G_MISS_CHAR, ATTRIBUTE2, X_ATTRIBUTE2 ),
378 ATTRIBUTE3 = DECODE(NVL(X_ATTRIBUTE3, FND_API.G_MISS_CHAR),
379 FND_API.G_MISS_CHAR, ATTRIBUTE3, X_ATTRIBUTE3 ),
380 ATTRIBUTE4 = DECODE(NVL(X_ATTRIBUTE4, FND_API.G_MISS_CHAR),
381 FND_API.G_MISS_CHAR, ATTRIBUTE4, X_ATTRIBUTE4 ),
382 ATTRIBUTE5 = DECODE(NVL(X_ATTRIBUTE5, FND_API.G_MISS_CHAR),
383 FND_API.G_MISS_CHAR, ATTRIBUTE5, X_ATTRIBUTE5 ),
384 ATTRIBUTE6 = DECODE(NVL(X_ATTRIBUTE6, FND_API.G_MISS_CHAR),
385 FND_API.G_MISS_CHAR, ATTRIBUTE6, X_ATTRIBUTE6 ),
386 ATTRIBUTE7 = DECODE(NVL(X_ATTRIBUTE7, FND_API.G_MISS_CHAR),
387 FND_API.G_MISS_CHAR, ATTRIBUTE7, X_ATTRIBUTE7 ),
388 ATTRIBUTE8 = DECODE(NVL(X_ATTRIBUTE8, FND_API.G_MISS_CHAR),
389 FND_API.G_MISS_CHAR, ATTRIBUTE8, X_ATTRIBUTE8 ),
390 ATTRIBUTE9 = DECODE(NVL(X_ATTRIBUTE9, FND_API.G_MISS_CHAR),
391 FND_API.G_MISS_CHAR, ATTRIBUTE9, X_ATTRIBUTE9 ),
392 ATTRIBUTE10 = DECODE(NVL(X_ATTRIBUTE10, FND_API.G_MISS_CHAR),
393 FND_API.G_MISS_CHAR, ATTRIBUTE10, X_ATTRIBUTE10),
394 ATTRIBUTE11 = DECODE(NVL(X_ATTRIBUTE11, FND_API.G_MISS_CHAR),
395 FND_API.G_MISS_CHAR, ATTRIBUTE11, X_ATTRIBUTE11),
396 ATTRIBUTE12 = DECODE(NVL(X_ATTRIBUTE12, FND_API.G_MISS_CHAR),
397 FND_API.G_MISS_CHAR, ATTRIBUTE12, X_ATTRIBUTE12),
398 ATTRIBUTE13 = DECODE(NVL(X_ATTRIBUTE13, FND_API.G_MISS_CHAR),
399 FND_API.G_MISS_CHAR, ATTRIBUTE13, X_ATTRIBUTE13),
400 ATTRIBUTE14 = DECODE(NVL(X_ATTRIBUTE14, FND_API.G_MISS_CHAR),
401 FND_API.G_MISS_CHAR, ATTRIBUTE14, X_ATTRIBUTE14),
402 ATTRIBUTE15 = DECODE(NVL(X_ATTRIBUTE15, FND_API.G_MISS_CHAR),
403 FND_API.G_MISS_CHAR, ATTRIBUTE15, X_ATTRIBUTE15),
404 UNI_QUESTION_NOTE_FLAG = DECODE(NVL(X_UNI_QUESTION_NOTE_FLAG, FND_API.G_MISS_CHAR),
405 FND_API.G_MISS_CHAR, UNI_QUESTION_NOTE_FLAG,
406 X_UNI_QUESTION_NOTE_FLAG),
407 UNI_QUESTION_NOTE_TYPE = DECODE(NVL(X_UNI_QUESTION_NOTE_TYPE,FND_API.G_MISS_CHAR),
408 FND_API.G_MISS_CHAR, UNI_QUESTION_NOTE_TYPE, X_UNI_QUESTION_NOTE_TYPE),
409 LAST_UPDATE_DATE = DECODE(NVL(X_LAST_UPDATE_DATE, FND_API.G_MISS_DATE),
410 FND_API.G_MISS_DATE, LAST_UPDATE_DATE, X_LAST_UPDATE_DATE),
411 LAST_UPDATED_BY = DECODE(NVL(X_LAST_UPDATED_BY, FND_API.G_MISS_NUM),
412 FND_API.G_MISS_NUM, LAST_UPDATED_BY, X_LAST_UPDATED_BY ),
413 LAST_UPDATE_LOGIN = DECODE(NVL(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM),
414 FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, X_LAST_UPDATE_LOGIN)
415 Where TEMPLATE_ID = X_TEMPLATE_ID;
416
417 if (sql%notfound) then
418 raise no_data_found;
419 end if;
420
421 update CS_TP_TEMPLATES_TL set
422 NAME = decode(nvl(X_NAME, fnd_api.g_miss_char),
423 fnd_api.g_miss_char, name, x_name),
424 DESCRIPTION = decode(nvl(X_DESCRIPTION, fnd_api.g_miss_char),
425 fnd_api.g_miss_char, description, x_description),
426 LAST_UPDATE_DATE = decode(nvl(X_LAST_UPDATE_DATE, fnd_api.g_miss_date),
427 fnd_api.g_miss_date, last_update_date, x_last_update_date),
428 LAST_UPDATED_BY = decode(nvl(X_LAST_UPDATED_BY, fnd_api.g_miss_num),
429 fnd_api.g_miss_num, last_updated_by, x_last_updated_by),
430 LAST_UPDATE_LOGIN = decode(nvl(X_LAST_UPDATE_LOGIN, fnd_api.g_miss_num),
431 fnd_api.g_miss_num, last_update_login, x_last_update_login),
432 SOURCE_LANG = userenv('LANG')
433 where TEMPLATE_ID = X_TEMPLATE_ID
434 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
435
436 if (sql%notfound) then
437 raise no_data_found;
438 end if;
439 end UPDATE_ROW;
440
441 procedure DELETE_ROW (
442 X_TEMPLATE_ID in NUMBER
443 ) is
444 begin
445 delete from CS_TP_TEMPLATES_TL
446 where TEMPLATE_ID = X_TEMPLATE_ID;
447
448 if (sql%notfound) then
449 raise no_data_found;
450 end if;
451
452 delete from CS_TP_TEMPLATES_B
453 where TEMPLATE_ID = X_TEMPLATE_ID;
454
455 if (sql%notfound) then
456 raise no_data_found;
457 end if;
458 end DELETE_ROW;
459
460 procedure ADD_LANGUAGE
461 is
462 begin
463 delete from CS_TP_TEMPLATES_TL T
464 where not exists
465 (select NULL
466 from CS_TP_TEMPLATES_B B
467 where B.TEMPLATE_ID = T.TEMPLATE_ID
468 );
469
470 update CS_TP_TEMPLATES_TL T set (
471 NAME,
472 DESCRIPTION
473 ) = (select
474 B.NAME,
475 B.DESCRIPTION
476 from CS_TP_TEMPLATES_TL B
477 where B.TEMPLATE_ID = T.TEMPLATE_ID
478 and B.LANGUAGE = T.SOURCE_LANG)
479 where (
480 T.TEMPLATE_ID,
481 T.LANGUAGE
482 ) in (select
483 SUBT.TEMPLATE_ID,
484 SUBT.LANGUAGE
485 from CS_TP_TEMPLATES_TL SUBB, CS_TP_TEMPLATES_TL SUBT
486 where SUBB.TEMPLATE_ID = SUBT.TEMPLATE_ID
487 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
488 and (SUBB.NAME <> SUBT.NAME
489 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
490 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
491 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
492 ));
493
494 insert into CS_TP_TEMPLATES_TL (
498 CREATION_DATE,
495 TEMPLATE_ID,
496 NAME,
497 DESCRIPTION,
499 CREATED_BY,
500 LAST_UPDATE_DATE,
501 LAST_UPDATED_BY,
502 LAST_UPDATE_LOGIN,
503 LANGUAGE,
504 SOURCE_LANG
505 ) select
506 B.TEMPLATE_ID,
507 B.NAME,
508 B.DESCRIPTION,
509 B.CREATION_DATE,
510 B.CREATED_BY,
511 B.LAST_UPDATE_DATE,
512 B.LAST_UPDATED_BY,
513 B.LAST_UPDATE_LOGIN,
514 L.LANGUAGE_CODE,
515 B.SOURCE_LANG
516 from CS_TP_TEMPLATES_TL B, FND_LANGUAGES L
517 where L.INSTALLED_FLAG in ('I', 'B')
518 and B.LANGUAGE = userenv('LANG')
519 and not exists
520 (select NULL
521 from CS_TP_TEMPLATES_TL T
522 where T.TEMPLATE_ID = B.TEMPLATE_ID
523 and T.LANGUAGE = L.LANGUAGE_CODE);
524 end ADD_LANGUAGE;
525
526 PROCEDURE LOAD_ROW(
527 x_template_id in number,
528 x_owner in varchar2,
529 x_name in varchar2,
530 x_description in varchar2,
531 x_default_flag in varchar2,
532 x_start_date_active in date,
533 x_end_date_active in date ,
534 x_uni_question_note_flag in varchar2,
535 x_uni_question_note_type in varchar2 ) is
536
537 l_default_flag varchar2(1);
538 l_start_date_active date;
539 l_end_date_active date;
540 l_user_id number;
541 l_rowid varchar(30);
542
543 begin
544
545 if (x_owner = 'SEED') then
546 l_user_id := 1;
547 else
548 l_user_id := 0;
549 end if;
550
551 l_default_flag := x_default_flag;
552 l_start_date_active := x_start_date_active;
553 l_end_date_active := x_end_date_active;
554
555 CS_TP_TEMPLATES_PKG.Update_Row(
556 X_TEMPLATE_ID => x_template_id,
557 X_DEFAULT_FLAG => l_default_flag,
558 X_START_DATE_ACTIVE => l_start_date_active ,
559 X_END_DATE_ACTIVE => l_end_date_active,
560 X_Name => x_name,
561 X_Description => x_description,
562 X_Last_Update_Date => sysdate,
563 X_Last_Updated_By => l_user_id,
564 X_Last_Update_Login => 0,
565 X_UNI_QUESTION_NOTE_FLAG => X_UNI_QUESTION_NOTE_FLAG,
566 X_UNI_QUESTION_NOTE_TYPE => X_UNI_QUESTION_NOTE_TYPE );
567
568 exception
569 when no_data_found then
570 CS_TP_TEMPLATES_PKG.Insert_Row(
571 X_Rowid => l_rowid,
572 X_TEMPLATE_ID => x_template_id,
573 X_DEFAULT_FLAG => l_default_flag,
574 X_START_DATE_ACTIVE => l_start_date_active,
575 X_END_DATE_ACTIVE => l_end_date_active,
576 X_NAME => x_name,
577 X_DESCRIPTION => x_description,
578 X_CREATION_DATE => sysdate,
579 X_CREATED_BY => l_user_id,
580 X_LAST_UPDATE_DATE => sysdate,
581 X_LAST_UPDATED_BY => l_user_id,
582 X_LAST_UPDATE_LOGIN => 0 ,
583 X_UNI_QUESTION_NOTE_FLAG => X_UNI_QUESTION_NOTE_FLAG,
584 X_UNI_QUESTION_NOTE_TYPE => X_UNI_QUESTION_NOTE_TYPE
585 );
586
587 end;
588
589 PROCEDURE TRANSLATE_ROW(
590 x_template_id in number,
591 x_owner in varchar2,
592 x_name in varchar2,
593 x_description in varchar2) is
594
595 l_name varchar2(500) := null;
596 l_description varchar2(30) := null;
597
598 begin
599
600 l_name := x_name;
601 l_description := x_description;
602
603 -- Update translated portions for specified language
604 update CS_TP_TEMPLATES_TL set
605 name = l_name,
606 description = l_description,
607 last_update_date = sysdate,
608 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
609 last_update_login = 0,
610 source_lang = userenv('LANG')
611 where TEMPLATE_ID = to_number(X_TEMPLATE_ID)
612 and userenv('LANG') in (language, source_lang);
613
614 exception
615 when no_data_found then null;
616
617 end;
618
619 end CS_TP_TEMPLATES_PKG;