DBA Data[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;