[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_TABLE_ATTRIBUTES_PKG
Source
1 package body JTF_RS_TABLE_ATTRIBUTES_PKG as
2 /* $Header: jtfrstwb.pls 120.0 2005/05/11 08:22:40 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ATTRIBUTE_ID in NUMBER,
6 X_ATTRIBUTE_NAME in VARCHAR2,
7 X_ATTRIBUTE_ACCESS_LEVEL in VARCHAR2,
8 X_ATTRIBUTE1 in VARCHAR2,
9 X_ATTRIBUTE2 in VARCHAR2,
10 X_ATTRIBUTE3 in VARCHAR2,
11 X_ATTRIBUTE4 in VARCHAR2,
12 X_ATTRIBUTE5 in VARCHAR2,
13 X_ATTRIBUTE6 in VARCHAR2,
14 X_ATTRIBUTE7 in VARCHAR2,
15 X_ATTRIBUTE8 in VARCHAR2,
16 X_ATTRIBUTE9 in VARCHAR2,
17 X_ATTRIBUTE10 in VARCHAR2,
18 X_ATTRIBUTE11 in VARCHAR2,
19 X_ATTRIBUTE12 in VARCHAR2,
20 X_ATTRIBUTE13 in VARCHAR2,
21 X_ATTRIBUTE14 in VARCHAR2,
22 X_ATTRIBUTE15 in VARCHAR2,
23 X_ATTRIBUTE_CATEGORY in VARCHAR2,
24 X_OBJECT_VERSION_NUMBER in NUMBER,
25 X_USER_ATTRIBUTE_NAME in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER
31 ) is
32 cursor C is select ROWID from JTF_RS_TABLE_ATTRIBUTES_B
33 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
34 ;
35 begin
36 insert into JTF_RS_TABLE_ATTRIBUTES_B (
37 ATTRIBUTE_ID,
38 ATTRIBUTE_NAME,
39 ATTRIBUTE_ACCESS_LEVEL,
40 ATTRIBUTE1,
41 ATTRIBUTE2,
42 ATTRIBUTE3,
43 ATTRIBUTE4,
44 ATTRIBUTE5,
45 ATTRIBUTE6,
46 ATTRIBUTE7,
47 ATTRIBUTE8,
48 ATTRIBUTE9,
49 ATTRIBUTE10,
50 ATTRIBUTE11,
51 ATTRIBUTE12,
52 ATTRIBUTE13,
53 ATTRIBUTE14,
54 ATTRIBUTE15,
55 ATTRIBUTE_CATEGORY,
56 OBJECT_VERSION_NUMBER,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_LOGIN
62 ) values (
63 X_ATTRIBUTE_ID,
64 X_ATTRIBUTE_NAME,
65 X_ATTRIBUTE_ACCESS_LEVEL,
66 X_ATTRIBUTE1,
67 X_ATTRIBUTE2,
68 X_ATTRIBUTE3,
69 X_ATTRIBUTE4,
70 X_ATTRIBUTE5,
71 X_ATTRIBUTE6,
72 X_ATTRIBUTE7,
73 X_ATTRIBUTE8,
74 X_ATTRIBUTE9,
75 X_ATTRIBUTE10,
76 X_ATTRIBUTE11,
77 X_ATTRIBUTE12,
78 X_ATTRIBUTE13,
79 X_ATTRIBUTE14,
80 X_ATTRIBUTE15,
81 X_ATTRIBUTE_CATEGORY,
82 1,
83 X_CREATION_DATE,
84 X_CREATED_BY,
85 X_LAST_UPDATE_DATE,
86 X_LAST_UPDATED_BY,
87 X_LAST_UPDATE_LOGIN
88 );
89
90 insert into JTF_RS_TABLE_ATTRIBUTES_TL (
91 ATTRIBUTE_ID,
92 USER_ATTRIBUTE_NAME,
93 CREATED_BY,
94 CREATION_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_DATE,
97 LAST_UPDATE_LOGIN,
98 LANGUAGE,
99 SOURCE_LANG
100 ) select
101 X_ATTRIBUTE_ID,
102 X_USER_ATTRIBUTE_NAME,
103 X_CREATED_BY,
104 X_CREATION_DATE,
105 X_LAST_UPDATED_BY,
106 X_LAST_UPDATE_DATE,
107 X_LAST_UPDATE_LOGIN,
108 L.LANGUAGE_CODE,
109 userenv('LANG')
110 from FND_LANGUAGES L
111 where L.INSTALLED_FLAG in ('I', 'B')
112 and not exists
113 (select NULL
114 from JTF_RS_TABLE_ATTRIBUTES_TL T
115 where T.ATTRIBUTE_ID = X_ATTRIBUTE_ID
116 and T.LANGUAGE = L.LANGUAGE_CODE);
117
118 open c;
119 fetch c into X_ROWID;
120 if (c%notfound) then
121 close c;
122 raise no_data_found;
123 end if;
124 close c;
125
126 end INSERT_ROW;
127
128 procedure LOCK_ROW (
129 X_ATTRIBUTE_ID in NUMBER,
130 X_OBJECT_VERSION_NUMBER in NUMBER
131 ) is
132 cursor c is select
133 OBJECT_VERSION_NUMBER
134 from JTF_RS_TABLE_ATTRIBUTES_VL
135 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
136 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
137 for update of ATTRIBUTE_ID nowait;
138 recinfo c%rowtype;
139
140 begin
141 open c;
142 fetch c into recinfo;
143 if (c%notfound) then
144 close c;
145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146 app_exception.raise_exception;
147 end if;
148 close c;
149
150 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
151 null;
152 else
153 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
154 app_exception.raise_exception;
155 end if;
156
157 end LOCK_ROW;
158
159 procedure UPDATE_ROW (
160 X_ATTRIBUTE_ID in NUMBER,
161 X_ATTRIBUTE_NAME in VARCHAR2,
162 X_ATTRIBUTE_ACCESS_LEVEL in VARCHAR2,
163 X_ATTRIBUTE1 in VARCHAR2,
164 X_ATTRIBUTE2 in VARCHAR2,
165 X_ATTRIBUTE3 in VARCHAR2,
166 X_ATTRIBUTE4 in VARCHAR2,
167 X_ATTRIBUTE5 in VARCHAR2,
168 X_ATTRIBUTE6 in VARCHAR2,
169 X_ATTRIBUTE7 in VARCHAR2,
170 X_ATTRIBUTE8 in VARCHAR2,
171 X_ATTRIBUTE9 in VARCHAR2,
172 X_ATTRIBUTE10 in VARCHAR2,
173 X_ATTRIBUTE11 in VARCHAR2,
174 X_ATTRIBUTE12 in VARCHAR2,
175 X_ATTRIBUTE13 in VARCHAR2,
176 X_ATTRIBUTE14 in VARCHAR2,
177 X_ATTRIBUTE15 in VARCHAR2,
178 X_ATTRIBUTE_CATEGORY in VARCHAR2,
179 X_OBJECT_VERSION_NUMBER in NUMBER,
180 X_USER_ATTRIBUTE_NAME in VARCHAR2,
181 X_LAST_UPDATE_DATE in DATE,
182 X_LAST_UPDATED_BY in NUMBER,
183 X_LAST_UPDATE_LOGIN in NUMBER
184 ) is
185 begin
186 update JTF_RS_TABLE_ATTRIBUTES_B set
187 ATTRIBUTE_NAME = X_ATTRIBUTE_NAME,
188 ATTRIBUTE_ACCESS_LEVEL = X_ATTRIBUTE_ACCESS_LEVEL,
189 ATTRIBUTE1 = X_ATTRIBUTE1,
190 ATTRIBUTE2 = X_ATTRIBUTE2,
191 ATTRIBUTE3 = X_ATTRIBUTE3,
192 ATTRIBUTE4 = X_ATTRIBUTE4,
193 ATTRIBUTE5 = X_ATTRIBUTE5,
194 ATTRIBUTE6 = X_ATTRIBUTE6,
195 ATTRIBUTE7 = X_ATTRIBUTE7,
196 ATTRIBUTE8 = X_ATTRIBUTE8,
197 ATTRIBUTE9 = X_ATTRIBUTE9,
198 ATTRIBUTE10 = X_ATTRIBUTE10,
199 ATTRIBUTE11 = X_ATTRIBUTE11,
200 ATTRIBUTE12 = X_ATTRIBUTE12,
201 ATTRIBUTE13 = X_ATTRIBUTE13,
202 ATTRIBUTE14 = X_ATTRIBUTE14,
203 ATTRIBUTE15 = X_ATTRIBUTE15,
204 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
205 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
206 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
207 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
208 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
209 where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
210
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214
215 update JTF_RS_TABLE_ATTRIBUTES_TL set
216 USER_ATTRIBUTE_NAME = X_USER_ATTRIBUTE_NAME,
217 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
218 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
219 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
220 SOURCE_LANG = userenv('LANG')
221 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
222 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
223
224 if (sql%notfound) then
225 raise no_data_found;
226 end if;
227 end UPDATE_ROW;
228
229 procedure DELETE_ROW (
230 X_ATTRIBUTE_ID in NUMBER
231 ) is
232 begin
233 delete from JTF_RS_TABLE_ATTRIBUTES_TL
234 where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
235
236 if (sql%notfound) then
237 raise no_data_found;
238 end if;
239
240 delete from JTF_RS_TABLE_ATTRIBUTES_B
241 where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246 end DELETE_ROW;
247
248 procedure ADD_LANGUAGE
249 is
250 begin
251 delete from JTF_RS_TABLE_ATTRIBUTES_TL T
252 where not exists
253 (select NULL
254 from JTF_RS_TABLE_ATTRIBUTES_B B
255 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
256 );
257
258 update JTF_RS_TABLE_ATTRIBUTES_TL T set (
259 USER_ATTRIBUTE_NAME
260 ) = (select
261 B.USER_ATTRIBUTE_NAME
262 from JTF_RS_TABLE_ATTRIBUTES_TL B
263 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
264 and B.LANGUAGE = T.SOURCE_LANG)
265 where (
266 T.ATTRIBUTE_ID,
267 T.LANGUAGE
268 ) in (select
269 SUBT.ATTRIBUTE_ID,
270 SUBT.LANGUAGE
271 from JTF_RS_TABLE_ATTRIBUTES_TL SUBB, JTF_RS_TABLE_ATTRIBUTES_TL SUBT
272 where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
273 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
274 and (SUBB.USER_ATTRIBUTE_NAME <> SUBT.USER_ATTRIBUTE_NAME
275 ));
276
277 insert into JTF_RS_TABLE_ATTRIBUTES_TL (
278 ATTRIBUTE_ID,
279 USER_ATTRIBUTE_NAME,
280 CREATED_BY,
281 CREATION_DATE,
282 LAST_UPDATED_BY,
283 LAST_UPDATE_DATE,
284 LAST_UPDATE_LOGIN,
285 LANGUAGE,
286 SOURCE_LANG
287 ) select
288 B.ATTRIBUTE_ID,
289 B.USER_ATTRIBUTE_NAME,
290 B.CREATED_BY,
291 B.CREATION_DATE,
292 B.LAST_UPDATED_BY,
293 B.LAST_UPDATE_DATE,
294 B.LAST_UPDATE_LOGIN,
295 L.LANGUAGE_CODE,
296 B.SOURCE_LANG
297 from JTF_RS_TABLE_ATTRIBUTES_TL B, FND_LANGUAGES L
298 where L.INSTALLED_FLAG in ('I', 'B')
299 and B.LANGUAGE = userenv('LANG')
300 and not exists
301 (select NULL
302 from JTF_RS_TABLE_ATTRIBUTES_TL T
303 where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
304 and T.LANGUAGE = L.LANGUAGE_CODE);
305 end ADD_LANGUAGE;
306
307 Procedure TRANSLATE_ROW
308 (x_attribute_id in number,
309 x_user_attribute_name in varchar2,
310 x_last_update_date in date,
311 x_last_updated_by in number,
312 x_last_update_login in number)
313 is
314 begin
315
316 update jtf_rs_table_attributes_tl set
317 user_attribute_name = nvl(x_user_attribute_name,user_attribute_name),
318 last_update_date = nvl(x_last_update_date,sysdate),
319 last_updated_by = x_last_updated_by,
320 last_update_login = 0,
321 source_lang = userenv('LANG')
322 where attribute_id = x_attribute_id
323 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
324
325 if (sql%notfound) then
326 raise no_data_found;
327 end if;
328 end TRANSLATE_ROW;
329
330 procedure LOAD_ROW (
331 X_ROWID in out NOCOPY VARCHAR2,
332 X_ATTRIBUTE_ID in NUMBER,
333 X_ATTRIBUTE_NAME in VARCHAR2,
334 X_ATTRIBUTE_ACCESS_LEVEL in VARCHAR2,
335 X_ATTRIBUTE1 in VARCHAR2,
336 X_ATTRIBUTE2 in VARCHAR2,
337 X_ATTRIBUTE3 in VARCHAR2,
338 X_ATTRIBUTE4 in VARCHAR2,
339 X_ATTRIBUTE5 in VARCHAR2,
340 X_ATTRIBUTE6 in VARCHAR2,
341 X_ATTRIBUTE7 in VARCHAR2,
342 X_ATTRIBUTE8 in VARCHAR2,
343 X_ATTRIBUTE9 in VARCHAR2,
344 X_ATTRIBUTE10 in VARCHAR2,
345 X_ATTRIBUTE11 in VARCHAR2,
346 X_ATTRIBUTE12 in VARCHAR2,
347 X_ATTRIBUTE13 in VARCHAR2,
348 X_ATTRIBUTE14 in VARCHAR2,
349 X_ATTRIBUTE15 in VARCHAR2,
350 X_ATTRIBUTE_CATEGORY in VARCHAR2,
351 X_OBJECT_VERSION_NUMBER in NUMBER,
352 X_USER_ATTRIBUTE_NAME in VARCHAR2,
353 X_CREATION_DATE in DATE,
354 X_CREATED_BY in NUMBER,
355 X_LAST_UPDATE_DATE in DATE,
356 X_LAST_UPDATED_BY in NUMBER,
357 X_LAST_UPDATE_LOGIN in NUMBER,
358 X_OWNER in VARCHAR2
359 ) is
360 l_row_id rowid;
361 l_user_id number;
362 l_last_updated_by number := -1;
363
364 CURSOR c_last_updated IS
365 SELECT last_updated_by from JTF_RS_TABLE_ATTRIBUTES_VL
366 WHERE attribute_id = X_ATTRIBUTE_ID;
367
368 begin
369 if (X_OWNER = 'SEED') then
370 l_user_id := 1;
371 else
372 l_user_id := 0;
373 end if;
374
375 OPEN c_last_updated;
376 FETCH c_last_updated into l_last_updated_by;
377 IF c_last_updated%NOTFOUND THEN
378 JTF_RS_TABLE_ATTRIBUTES_PKG.INSERT_ROW(
379 X_ROWID => X_ROWID,
380 X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
381 X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME,
382 X_ATTRIBUTE_ACCESS_LEVEL => X_ATTRIBUTE_ACCESS_LEVEL,
383 X_ATTRIBUTE1 => X_ATTRIBUTE1,
384 X_ATTRIBUTE2 => X_ATTRIBUTE2,
385 X_ATTRIBUTE3 => X_ATTRIBUTE3,
386 X_ATTRIBUTE4 => X_ATTRIBUTE4,
387 X_ATTRIBUTE5 => X_ATTRIBUTE5,
388 X_ATTRIBUTE6 => X_ATTRIBUTE6,
389 X_ATTRIBUTE7 => X_ATTRIBUTE7,
390 X_ATTRIBUTE8 => X_ATTRIBUTE8,
391 X_ATTRIBUTE9 => X_ATTRIBUTE9,
392 X_ATTRIBUTE10 => X_ATTRIBUTE10,
393 X_ATTRIBUTE11 => X_ATTRIBUTE11,
394 X_ATTRIBUTE12 => X_ATTRIBUTE12,
395 X_ATTRIBUTE13 => X_ATTRIBUTE13,
396 X_ATTRIBUTE14 => X_ATTRIBUTE14,
397 X_ATTRIBUTE15 => X_ATTRIBUTE15,
398 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
399 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
400 X_USER_ATTRIBUTE_NAME => X_USER_ATTRIBUTE_NAME,
401 X_CREATION_DATE => sysdate,
402 X_CREATED_BY => l_user_id,
403 X_LAST_UPDATE_DATE => sysdate,
404 X_LAST_UPDATED_BY => l_user_id,
405 X_LAST_UPDATE_LOGIN => 0);
406 ELSIF c_last_updated%FOUND THEN
407 IF l_last_updated_by = 1 THEN
408 JTF_RS_TABLE_ATTRIBUTES_PKG.UPDATE_ROW(
409 X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
410 X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME,
411 X_ATTRIBUTE_ACCESS_LEVEL => X_ATTRIBUTE_ACCESS_LEVEL,
412 X_ATTRIBUTE1 => X_ATTRIBUTE1,
413 X_ATTRIBUTE2 => X_ATTRIBUTE2,
414 X_ATTRIBUTE3 => X_ATTRIBUTE3,
415 X_ATTRIBUTE4 => X_ATTRIBUTE4,
416 X_ATTRIBUTE5 => X_ATTRIBUTE5,
417 X_ATTRIBUTE6 => X_ATTRIBUTE6,
418 X_ATTRIBUTE7 => X_ATTRIBUTE7,
419 X_ATTRIBUTE8 => X_ATTRIBUTE8,
420 X_ATTRIBUTE9 => X_ATTRIBUTE9,
421 X_ATTRIBUTE10 => X_ATTRIBUTE10,
422 X_ATTRIBUTE11 => X_ATTRIBUTE11,
423 X_ATTRIBUTE12 => X_ATTRIBUTE12,
424 X_ATTRIBUTE13 => X_ATTRIBUTE13,
425 X_ATTRIBUTE14 => X_ATTRIBUTE14,
426 X_ATTRIBUTE15 => X_ATTRIBUTE15,
427 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
428 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
429 X_USER_ATTRIBUTE_NAME => X_USER_ATTRIBUTE_NAME,
430 X_LAST_UPDATE_DATE => sysdate,
431 X_LAST_UPDATED_BY => l_user_id,
432 X_LAST_UPDATE_LOGIN => 0);
433 END IF;
434 END IF;
435 CLOSE c_last_updated;
436 End LOAD_ROW;
437
438 end JTF_RS_TABLE_ATTRIBUTES_PKG;