1 PACKAGE BODY JTS_CONFIG_VERSIONS_PKG as
2 /* $Header: jtstcvrb.pls 115.1 2002/06/07 11:53:20 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_CONFIG_VERSIONS_PKG
7 -- Purpose : Table Handler for JTS_CONFIG_VERSIONS_B AND _TL tables
8 -- History : 06-Jun-02 SHuh Created.
9 --
10 -- PROCEDURES
11 -- DELETE_ROW
12 -- LOAD_ROW
13 -- INSERT_ROW
14 -- UPDATE_ROW
15 -- ADD_LANGUAGE
16 -- --------------------------------------------------------------------
17
18 procedure INSERT_ROW (
19 X_ROWID in out VARCHAR2,
20 X_VERSION_ID in NUMBER,
21 X_VERSION_STATUS_CODE in VARCHAR2,
22 X_REPLAY_STATUS_CODE in VARCHAR2,
23 X_REPLAYED_ON in DATE,
24 X_REPLAYED_BY in NUMBER,
25 X_OBJECT_VERSION_NUMBER in NUMBER,
26 X_VERSION_NAME in VARCHAR2,
27 X_CONFIGURATION_ID in NUMBER,
28 X_VERSION_NUMBER in NUMBER,
29 X_QUEUE_NAME in VARCHAR2,
30 X_ATTRIBUTE_CATEGORY in VARCHAR2,
31 X_ATTRIBUTE1 in VARCHAR2,
32 X_ATTRIBUTE2 in VARCHAR2,
33 X_ATTRIBUTE3 in VARCHAR2,
34 X_ATTRIBUTE4 in VARCHAR2,
35 X_ATTRIBUTE5 in VARCHAR2,
36 X_ATTRIBUTE6 in VARCHAR2,
37 X_ATTRIBUTE7 in VARCHAR2,
38 X_ATTRIBUTE8 in VARCHAR2,
39 X_ATTRIBUTE9 in VARCHAR2,
40 X_ATTRIBUTE10 in VARCHAR2,
41 X_ATTRIBUTE11 in VARCHAR2,
42 X_ATTRIBUTE12 in VARCHAR2,
43 X_ATTRIBUTE13 in VARCHAR2,
44 X_ATTRIBUTE14 in VARCHAR2,
45 X_ATTRIBUTE15 in VARCHAR2,
46 X_SECURITY_GROUP_ID in NUMBER,
47 X_DESCRIPTION in VARCHAR2,
48 X_CREATION_DATE in DATE,
49 X_CREATED_BY in NUMBER,
50 X_LAST_UPDATE_DATE in DATE,
51 X_LAST_UPDATED_BY in NUMBER,
52 X_LAST_UPDATE_LOGIN in NUMBER
53 ) is
54 cursor C is select ROWID from JTS_CONFIG_VERSIONS_B
55 where VERSION_ID = X_VERSION_ID
56 ;
57 begin
58 insert into JTS_CONFIG_VERSIONS_B (
59 VERSION_STATUS_CODE,
60 REPLAY_STATUS_CODE,
61 REPLAYED_ON,
62 REPLAYED_BY,
63 VERSION_ID,
64 OBJECT_VERSION_NUMBER,
65 VERSION_NAME,
66 CONFIGURATION_ID,
67 VERSION_NUMBER,
68 QUEUE_NAME,
69 ATTRIBUTE_CATEGORY,
70 ATTRIBUTE1,
71 ATTRIBUTE2,
72 ATTRIBUTE3,
73 ATTRIBUTE4,
74 ATTRIBUTE5,
75 ATTRIBUTE6,
76 ATTRIBUTE7,
77 ATTRIBUTE8,
78 ATTRIBUTE9,
79 ATTRIBUTE10,
80 ATTRIBUTE11,
81 ATTRIBUTE12,
82 ATTRIBUTE13,
83 ATTRIBUTE14,
84 ATTRIBUTE15,
85 SECURITY_GROUP_ID,
86 CREATION_DATE,
87 CREATED_BY,
88 LAST_UPDATE_DATE,
89 LAST_UPDATED_BY,
90 LAST_UPDATE_LOGIN
91 ) values (
92 X_VERSION_STATUS_CODE,
93 X_REPLAY_STATUS_CODE,
94 X_REPLAYED_ON,
95 X_REPLAYED_BY,
96 X_VERSION_ID,
97 X_OBJECT_VERSION_NUMBER,
98 X_VERSION_NAME,
99 X_CONFIGURATION_ID,
100 X_VERSION_NUMBER,
101 X_QUEUE_NAME,
102 X_ATTRIBUTE_CATEGORY,
103 X_ATTRIBUTE1,
104 X_ATTRIBUTE2,
105 X_ATTRIBUTE3,
106 X_ATTRIBUTE4,
107 X_ATTRIBUTE5,
108 X_ATTRIBUTE6,
109 X_ATTRIBUTE7,
110 X_ATTRIBUTE8,
111 X_ATTRIBUTE9,
112 X_ATTRIBUTE10,
113 X_ATTRIBUTE11,
114 X_ATTRIBUTE12,
115 X_ATTRIBUTE13,
116 X_ATTRIBUTE14,
117 X_ATTRIBUTE15,
118 X_SECURITY_GROUP_ID,
119 X_CREATION_DATE,
120 X_CREATED_BY,
121 X_LAST_UPDATE_DATE,
122 X_LAST_UPDATED_BY,
123 X_LAST_UPDATE_LOGIN
124 );
125
126 insert into JTS_CONFIG_VERSIONS_TL (
127 VERSION_ID,
128 CONFIGURATION_ID,
129 DESCRIPTION,
130 CREATION_DATE,
131 CREATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN,
135 SECURITY_GROUP_ID,
136 LANGUAGE,
137 SOURCE_LANG
138 ) select
139 X_VERSION_ID,
140 X_CONFIGURATION_ID,
141 X_DESCRIPTION,
142 X_CREATION_DATE,
143 X_CREATED_BY,
144 X_LAST_UPDATE_DATE,
145 X_LAST_UPDATED_BY,
146 X_LAST_UPDATE_LOGIN,
147 X_SECURITY_GROUP_ID,
148 L.LANGUAGE_CODE,
149 userenv('LANG')
150 from FND_LANGUAGES L
151 where L.INSTALLED_FLAG in ('I', 'B')
152 and not exists
153 (select NULL
154 from JTS_CONFIG_VERSIONS_TL T
155 where T.VERSION_ID = X_VERSION_ID
156 and T.LANGUAGE = L.LANGUAGE_CODE);
157
158 open c;
159 fetch c into X_ROWID;
160 if (c%notfound) then
161 close c;
162 raise no_data_found;
163 end if;
164 close c;
165
166 end INSERT_ROW;
167
168 procedure LOCK_ROW (
169 X_VERSION_ID in NUMBER,
170 X_VERSION_STATUS_CODE in VARCHAR2,
171 X_REPLAY_STATUS_CODE in VARCHAR2,
172 X_REPLAYED_ON in DATE,
173 X_REPLAYED_BY in NUMBER,
174 X_OBJECT_VERSION_NUMBER in NUMBER,
175 X_VERSION_NAME in VARCHAR2,
176 X_CONFIGURATION_ID in NUMBER,
177 X_VERSION_NUMBER in NUMBER,
178 X_QUEUE_NAME in VARCHAR2,
179 X_ATTRIBUTE_CATEGORY in VARCHAR2,
180 X_ATTRIBUTE1 in VARCHAR2,
181 X_ATTRIBUTE2 in VARCHAR2,
182 X_ATTRIBUTE3 in VARCHAR2,
183 X_ATTRIBUTE4 in VARCHAR2,
184 X_ATTRIBUTE5 in VARCHAR2,
185 X_ATTRIBUTE6 in VARCHAR2,
186 X_ATTRIBUTE7 in VARCHAR2,
187 X_ATTRIBUTE8 in VARCHAR2,
188 X_ATTRIBUTE9 in VARCHAR2,
189 X_ATTRIBUTE10 in VARCHAR2,
190 X_ATTRIBUTE11 in VARCHAR2,
191 X_ATTRIBUTE12 in VARCHAR2,
192 X_ATTRIBUTE13 in VARCHAR2,
193 X_ATTRIBUTE14 in VARCHAR2,
194 X_ATTRIBUTE15 in VARCHAR2,
195 X_SECURITY_GROUP_ID in NUMBER,
196 X_DESCRIPTION in VARCHAR2
197 ) is
198 cursor c is select
199 VERSION_STATUS_CODE,
200 REPLAY_STATUS_CODE,
201 REPLAYED_ON,
202 REPLAYED_BY,
203 OBJECT_VERSION_NUMBER,
204 VERSION_NAME,
205 CONFIGURATION_ID,
206 VERSION_NUMBER,
207 QUEUE_NAME,
208 ATTRIBUTE_CATEGORY,
209 ATTRIBUTE1,
210 ATTRIBUTE2,
211 ATTRIBUTE3,
212 ATTRIBUTE4,
213 ATTRIBUTE5,
214 ATTRIBUTE6,
215 ATTRIBUTE7,
216 ATTRIBUTE8,
217 ATTRIBUTE9,
218 ATTRIBUTE10,
219 ATTRIBUTE11,
220 ATTRIBUTE12,
221 ATTRIBUTE13,
222 ATTRIBUTE14,
223 ATTRIBUTE15,
224 SECURITY_GROUP_ID
225 from JTS_CONFIG_VERSIONS_B
226 where VERSION_ID = X_VERSION_ID
227 for update of VERSION_ID nowait;
228 recinfo c%rowtype;
229
230 cursor c1 is select
231 DESCRIPTION,
232 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
233 from JTS_CONFIG_VERSIONS_TL
234 where VERSION_ID = X_VERSION_ID
235 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
236 for update of VERSION_ID nowait;
237 begin
238 open c;
239 fetch c into recinfo;
240 if (c%notfound) then
241 close c;
242 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
243 app_exception.raise_exception;
244 end if;
245 close c;
246 if ( ((recinfo.VERSION_STATUS_CODE = X_VERSION_STATUS_CODE)
247 OR ((recinfo.VERSION_STATUS_CODE is null) AND (X_VERSION_STATUS_CODE is null)))
248 AND ((recinfo.REPLAY_STATUS_CODE = X_REPLAY_STATUS_CODE)
249 OR ((recinfo.REPLAY_STATUS_CODE is null) AND (X_REPLAY_STATUS_CODE is null)))
250 AND ((recinfo.REPLAYED_ON = X_REPLAYED_ON)
251 OR ((recinfo.REPLAYED_ON is null) AND (X_REPLAYED_ON is null)))
252 AND ((recinfo.REPLAYED_BY = X_REPLAYED_BY)
253 OR ((recinfo.REPLAYED_BY is null) AND (X_REPLAYED_BY is null)))
254 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
255 AND (recinfo.VERSION_NAME = X_VERSION_NAME)
256 AND (recinfo.CONFIGURATION_ID = X_CONFIGURATION_ID)
257 AND (recinfo.VERSION_NUMBER = X_VERSION_NUMBER)
258 AND ((recinfo.QUEUE_NAME = X_QUEUE_NAME)
259 OR ((recinfo.QUEUE_NAME is null) AND (X_QUEUE_NAME is null)))
260 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
261 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
262 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
263 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
264 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
265 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
266 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
267 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
268 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
269 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
270 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
271 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
272 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
273 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
274 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
275 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
276 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
277 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
278 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
279 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
280 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
281 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
282 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
283 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
284 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
285 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
286 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
287 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
288 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
289 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
290 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
291 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
292 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
293 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID 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
301 for tlinfo in c1 loop
302 if (tlinfo.BASELANG = 'Y') then
303 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
304 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
305 ) then
306 null;
307 else
308 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
309 app_exception.raise_exception;
310 end if;
311 end if;
312 end loop;
313 return;
314 end LOCK_ROW;
315
316 procedure UPDATE_ROW (
317 X_VERSION_ID in NUMBER,
318 X_VERSION_STATUS_CODE in VARCHAR2,
319 X_REPLAY_STATUS_CODE in VARCHAR2,
320 X_REPLAYED_ON in DATE,
321 X_REPLAYED_BY in NUMBER,
322 X_OBJECT_VERSION_NUMBER in NUMBER,
323 X_VERSION_NAME in VARCHAR2,
324 X_CONFIGURATION_ID in NUMBER,
325 X_VERSION_NUMBER in NUMBER,
326 X_QUEUE_NAME in VARCHAR2,
327 X_ATTRIBUTE_CATEGORY in VARCHAR2,
328 X_ATTRIBUTE1 in VARCHAR2,
329 X_ATTRIBUTE2 in VARCHAR2,
330 X_ATTRIBUTE3 in VARCHAR2,
331 X_ATTRIBUTE4 in VARCHAR2,
332 X_ATTRIBUTE5 in VARCHAR2,
333 X_ATTRIBUTE6 in VARCHAR2,
334 X_ATTRIBUTE7 in VARCHAR2,
335 X_ATTRIBUTE8 in VARCHAR2,
336 X_ATTRIBUTE9 in VARCHAR2,
337 X_ATTRIBUTE10 in VARCHAR2,
338 X_ATTRIBUTE11 in VARCHAR2,
339 X_ATTRIBUTE12 in VARCHAR2,
340 X_ATTRIBUTE13 in VARCHAR2,
341 X_ATTRIBUTE14 in VARCHAR2,
342 X_ATTRIBUTE15 in VARCHAR2,
343 X_SECURITY_GROUP_ID in NUMBER,
344 X_DESCRIPTION in VARCHAR2,
345 X_LAST_UPDATE_DATE in DATE,
346 X_LAST_UPDATED_BY in NUMBER,
347 X_LAST_UPDATE_LOGIN in NUMBER
348 ) is
349 begin
350 update JTS_CONFIG_VERSIONS_B set
351 VERSION_STATUS_CODE = X_VERSION_STATUS_CODE,
352 REPLAY_STATUS_CODE = X_REPLAY_STATUS_CODE,
353 REPLAYED_ON = X_REPLAYED_ON,
354 REPLAYED_BY = X_REPLAYED_BY,
355 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
356 VERSION_NAME = X_VERSION_NAME,
357 CONFIGURATION_ID = X_CONFIGURATION_ID,
358 VERSION_NUMBER = X_VERSION_NUMBER,
359 QUEUE_NAME = X_QUEUE_NAME,
360 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
361 ATTRIBUTE1 = X_ATTRIBUTE1,
362 ATTRIBUTE2 = X_ATTRIBUTE2,
363 ATTRIBUTE3 = X_ATTRIBUTE3,
364 ATTRIBUTE4 = X_ATTRIBUTE4,
365 ATTRIBUTE5 = X_ATTRIBUTE5,
366 ATTRIBUTE6 = X_ATTRIBUTE6,
367 ATTRIBUTE7 = X_ATTRIBUTE7,
368 ATTRIBUTE8 = X_ATTRIBUTE8,
369 ATTRIBUTE9 = X_ATTRIBUTE9,
370 ATTRIBUTE10 = X_ATTRIBUTE10,
371 ATTRIBUTE11 = X_ATTRIBUTE11,
372 ATTRIBUTE12 = X_ATTRIBUTE12,
373 ATTRIBUTE13 = X_ATTRIBUTE13,
374 ATTRIBUTE14 = X_ATTRIBUTE14,
375 ATTRIBUTE15 = X_ATTRIBUTE15,
376 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
377 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
378 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
379 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
380 where VERSION_ID = X_VERSION_ID;
381
382 if (sql%notfound) then
383 raise no_data_found;
384 end if;
385
386 update JTS_CONFIG_VERSIONS_TL set
387 DESCRIPTION = X_DESCRIPTION,
388 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
389 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
390 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
391 SOURCE_LANG = userenv('LANG')
392 where VERSION_ID = X_VERSION_ID
393 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
394
395 if (sql%notfound) then
396 raise no_data_found;
397 end if;
398 end UPDATE_ROW;
399
400
401 -- Deletes a row from jts_config_versions
402 PROCEDURE DELETE_ROW(p_version_id IN NUMBER
403 ) IS
404 BEGIN
405 DELETE FROM jts_config_versions_b
406 WHERE version_id = p_version_id;
407
408 DELETE FROM jts_config_versions_tl
409 WHERE version_id = p_version_id;
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 APP_EXCEPTION.RAISE_EXCEPTION;
414 END DELETE_ROW;
415
416 procedure ADD_LANGUAGE
417 is
418 begin
419 delete from JTS_CONFIG_VERSIONS_TL T
420 where not exists
421 (select NULL
422 from JTS_CONFIG_VERSIONS_B B
423 where B.VERSION_ID = T.VERSION_ID
424 );
425
426 update JTS_CONFIG_VERSIONS_TL T set (
427 DESCRIPTION
428 ) = (select
429 B.DESCRIPTION
430 from JTS_CONFIG_VERSIONS_TL B
431 where B.VERSION_ID = T.VERSION_ID
432 and B.LANGUAGE = T.SOURCE_LANG)
433 where (
434 T.VERSION_ID,
435 T.LANGUAGE
436 ) in (select
437 SUBT.VERSION_ID,
438 SUBT.LANGUAGE
439 from JTS_CONFIG_VERSIONS_TL SUBB, JTS_CONFIG_VERSIONS_TL SUBT
440 where SUBB.VERSION_ID = SUBT.VERSION_ID
441 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
442 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
443 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
444 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
445 ));
446
447 insert into JTS_CONFIG_VERSIONS_TL (
448 VERSION_ID,
449 CONFIGURATION_ID,
450 DESCRIPTION,
451 CREATION_DATE,
452 CREATED_BY,
453 LAST_UPDATE_DATE,
454 LAST_UPDATED_BY,
455 LAST_UPDATE_LOGIN,
456 SECURITY_GROUP_ID,
457 LANGUAGE,
458 SOURCE_LANG
459 ) select
460 B.VERSION_ID,
461 B.CONFIGURATION_ID,
462 B.DESCRIPTION,
463 B.CREATION_DATE,
464 B.CREATED_BY,
465 B.LAST_UPDATE_DATE,
466 B.LAST_UPDATED_BY,
467 B.LAST_UPDATE_LOGIN,
468 B.SECURITY_GROUP_ID,
469 L.LANGUAGE_CODE,
470 B.SOURCE_LANG
471 from JTS_CONFIG_VERSIONS_TL B, FND_LANGUAGES L
472 where L.INSTALLED_FLAG in ('I', 'B')
473 and B.LANGUAGE = userenv('LANG')
474 and not exists
475 (select NULL
476 from JTS_CONFIG_VERSIONS_TL T
477 where T.VERSION_ID = B.VERSION_ID
478 and T.LANGUAGE = L.LANGUAGE_CODE);
479 end ADD_LANGUAGE;
480
481 -------------------------------------------------
482 -- Translates the description
483 -------------------------------------------------
484 PROCEDURE TRANSLATE_ROW (
485 p_version_id IN NUMBER,
486 p_owner IN VARCHAR2,
487 p_description IN VARCHAR2
488 )
489 IS
490 BEGIN
491 update jts_config_versions_tl set
492 description = nvl(p_description, description),
493 source_lang = userenv('LANG'),
494 last_update_date = sysdate,
495 last_updated_by = decode(p_owner, 'SEED', 1, 0),
496 last_update_login = 0
497 where version_id= p_version_id
498 and userenv('LANG') in (language, source_lang);
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 APP_EXCEPTION.RAISE_EXCEPTION;
503 END TRANSLATE_ROW;
504
505
506 END JTS_CONFIG_VERSIONS_PKG;
[Home] [Help]