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