[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_DYNAMIC_GROUPS_PKG
Source
1 package body JTF_RS_DYNAMIC_GROUPS_PKG as
2 /* $Header: jtfrstdb.pls 120.0 2005/05/11 08:22:08 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_GROUP_ID in NUMBER,
6 X_GROUP_NUMBER in VARCHAR2,
7 X_USAGE in VARCHAR2,
8 X_START_DATE_ACTIVE in DATE,
9 X_END_DATE_ACTIVE in DATE,
10 X_SQL_TEXT in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_ATTRIBUTE_CATEGORY in VARCHAR2,
27 X_GROUP_NAME in VARCHAR2,
28 X_GROUP_DESC in VARCHAR2,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from JTF_RS_DYNAMIC_GROUPS_B
36 where GROUP_ID = X_GROUP_ID
37 ;
38 begin
39 insert into JTF_RS_DYNAMIC_GROUPS_B (
40 GROUP_ID,
41 GROUP_NUMBER,
42 USAGE,
43 START_DATE_ACTIVE,
44 END_DATE_ACTIVE,
45 SQL_TEXT,
46 OBJECT_VERSION_NUMBER,
47 ATTRIBUTE1,
48 ATTRIBUTE2,
49 ATTRIBUTE3,
50 ATTRIBUTE4,
51 ATTRIBUTE5,
52 ATTRIBUTE6,
53 ATTRIBUTE7,
54 ATTRIBUTE8,
55 ATTRIBUTE9,
56 ATTRIBUTE10,
57 ATTRIBUTE11,
58 ATTRIBUTE12,
59 ATTRIBUTE13,
60 ATTRIBUTE14,
61 ATTRIBUTE15,
62 ATTRIBUTE_CATEGORY,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 ) values (
69 X_GROUP_ID,
70 X_GROUP_NUMBER,
71 X_USAGE,
72 X_START_DATE_ACTIVE,
73 X_END_DATE_ACTIVE,
74 X_SQL_TEXT,
75 1,
76 X_ATTRIBUTE1,
77 X_ATTRIBUTE2,
78 X_ATTRIBUTE3,
79 X_ATTRIBUTE4,
80 X_ATTRIBUTE5,
81 X_ATTRIBUTE6,
82 X_ATTRIBUTE7,
83 X_ATTRIBUTE8,
84 X_ATTRIBUTE9,
85 X_ATTRIBUTE10,
86 X_ATTRIBUTE11,
87 X_ATTRIBUTE12,
88 X_ATTRIBUTE13,
89 X_ATTRIBUTE14,
90 X_ATTRIBUTE15,
91 X_ATTRIBUTE_CATEGORY,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_LOGIN
97 );
98
99 insert into JTF_RS_DYNAMIC_GROUPS_TL (
100 GROUP_ID,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_LOGIN,
106 GROUP_NAME,
107 GROUP_DESC,
108 LANGUAGE,
109 SOURCE_LANG
110 ) select
111 X_GROUP_ID,
112 X_CREATED_BY,
113 X_CREATION_DATE,
114 X_LAST_UPDATED_BY,
115 X_LAST_UPDATE_DATE,
116 X_LAST_UPDATE_LOGIN,
117 X_GROUP_NAME,
118 X_GROUP_DESC,
119 L.LANGUAGE_CODE,
120 userenv('LANG')
121 from FND_LANGUAGES L
122 where L.INSTALLED_FLAG in ('I', 'B')
123 and not exists
124 (select NULL
125 from JTF_RS_DYNAMIC_GROUPS_TL T
126 where T.GROUP_ID = X_GROUP_ID
127 and T.LANGUAGE = L.LANGUAGE_CODE);
128
129 open c;
130 fetch c into X_ROWID;
131 if (c%notfound) then
132 close c;
133 raise no_data_found;
134 end if;
135 close c;
136
137 end INSERT_ROW;
138
139 procedure LOCK_ROW (
140 X_GROUP_ID in NUMBER,
141 X_OBJECT_VERSION_NUMBER in NUMBER
142 ) is
143 cursor c is select
144 OBJECT_VERSION_NUMBER
145 from JTF_RS_DYNAMIC_GROUPS_B
146 where GROUP_ID = X_GROUP_ID
147 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
148 for update of GROUP_ID nowait;
149 recinfo c%rowtype;
150
151 begin
152 open c;
153 fetch c into recinfo;
154 if (c%notfound) then
155 close c;
156 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
157 app_exception.raise_exception;
158 end if;
159
160 close c;
161
162 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
163 null;
164 else
165 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166 app_exception.raise_exception;
167 end if;
168
169 end LOCK_ROW;
170
171 procedure UPDATE_ROW (
172 X_GROUP_ID in NUMBER,
173 X_GROUP_NUMBER in VARCHAR2,
174 X_USAGE in VARCHAR2,
175 X_START_DATE_ACTIVE in DATE,
176 X_END_DATE_ACTIVE in DATE,
177 X_SQL_TEXT in VARCHAR2,
178 X_OBJECT_VERSION_NUMBER in NUMBER,
179 X_ATTRIBUTE1 in VARCHAR2,
180 X_ATTRIBUTE2 in VARCHAR2,
181 X_ATTRIBUTE3 in VARCHAR2,
182 X_ATTRIBUTE4 in VARCHAR2,
183 X_ATTRIBUTE5 in VARCHAR2,
184 X_ATTRIBUTE6 in VARCHAR2,
185 X_ATTRIBUTE7 in VARCHAR2,
186 X_ATTRIBUTE8 in VARCHAR2,
187 X_ATTRIBUTE9 in VARCHAR2,
188 X_ATTRIBUTE10 in VARCHAR2,
189 X_ATTRIBUTE11 in VARCHAR2,
190 X_ATTRIBUTE12 in VARCHAR2,
191 X_ATTRIBUTE13 in VARCHAR2,
192 X_ATTRIBUTE14 in VARCHAR2,
193 X_ATTRIBUTE15 in VARCHAR2,
194 X_ATTRIBUTE_CATEGORY in VARCHAR2,
195 X_GROUP_NAME in VARCHAR2,
196 X_GROUP_DESC in VARCHAR2,
197 X_LAST_UPDATE_DATE in DATE,
198 X_LAST_UPDATED_BY in NUMBER,
199 X_LAST_UPDATE_LOGIN in NUMBER
200 ) is
201 begin
202 update JTF_RS_DYNAMIC_GROUPS_B set
203 GROUP_NUMBER = X_GROUP_NUMBER,
204 USAGE = X_USAGE,
205 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
206 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
207 SQL_TEXT = X_SQL_TEXT,
208 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
209 ATTRIBUTE1 = X_ATTRIBUTE1,
210 ATTRIBUTE2 = X_ATTRIBUTE2,
211 ATTRIBUTE3 = X_ATTRIBUTE3,
212 ATTRIBUTE4 = X_ATTRIBUTE4,
213 ATTRIBUTE5 = X_ATTRIBUTE5,
214 ATTRIBUTE6 = X_ATTRIBUTE6,
215 ATTRIBUTE7 = X_ATTRIBUTE7,
216 ATTRIBUTE8 = X_ATTRIBUTE8,
217 ATTRIBUTE9 = X_ATTRIBUTE9,
218 ATTRIBUTE10 = X_ATTRIBUTE10,
219 ATTRIBUTE11 = X_ATTRIBUTE11,
220 ATTRIBUTE12 = X_ATTRIBUTE12,
221 ATTRIBUTE13 = X_ATTRIBUTE13,
222 ATTRIBUTE14 = X_ATTRIBUTE14,
223 ATTRIBUTE15 = X_ATTRIBUTE15,
224 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
225 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
226 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
227 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
228 where GROUP_ID = X_GROUP_ID;
229
230 if (sql%notfound) then
231 raise no_data_found;
232 end if;
233
234 update JTF_RS_DYNAMIC_GROUPS_TL set
235 GROUP_NAME = X_GROUP_NAME,
236 GROUP_DESC = X_GROUP_DESC,
237 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
238 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
240 SOURCE_LANG = userenv('LANG')
241 where GROUP_ID = X_GROUP_ID
242 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
243
244 if (sql%notfound) then
245 raise no_data_found;
246 end if;
247 end UPDATE_ROW;
248
249 procedure DELETE_ROW (
250 X_GROUP_ID in NUMBER
251 ) is
252 begin
253 delete from JTF_RS_DYNAMIC_GROUPS_TL
254 where GROUP_ID = X_GROUP_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260 delete from JTF_RS_DYNAMIC_GROUPS_B
261 where GROUP_ID = X_GROUP_ID;
262
263 if (sql%notfound) then
264 raise no_data_found;
265 end if;
266 end DELETE_ROW;
267
268 procedure ADD_LANGUAGE
269 is
270 begin
271 delete from JTF_RS_DYNAMIC_GROUPS_TL T
272 where not exists
273 (select NULL
274 from JTF_RS_DYNAMIC_GROUPS_B B
275 where B.GROUP_ID = T.GROUP_ID
276 );
277
278 update JTF_RS_DYNAMIC_GROUPS_TL T set (
279 GROUP_NAME,
280 GROUP_DESC
281 ) = (select
282 B.GROUP_NAME,
283 B.GROUP_DESC
284 from JTF_RS_DYNAMIC_GROUPS_TL B
285 where B.GROUP_ID = T.GROUP_ID
286 and B.LANGUAGE = T.SOURCE_LANG)
287 where (
288 T.GROUP_ID,
289 T.LANGUAGE
290 ) in (select
291 SUBT.GROUP_ID,
292 SUBT.LANGUAGE
293 from JTF_RS_DYNAMIC_GROUPS_TL SUBB, JTF_RS_DYNAMIC_GROUPS_TL SUBT
294 where SUBB.GROUP_ID = SUBT.GROUP_ID
295 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
296 and (SUBB.GROUP_NAME <> SUBT.GROUP_NAME
297 or SUBB.GROUP_DESC <> SUBT.GROUP_DESC
298 or (SUBB.GROUP_DESC is null and SUBT.GROUP_DESC is not null)
299 or (SUBB.GROUP_DESC is not null and SUBT.GROUP_DESC is null)
300 ));
301
302 insert into JTF_RS_DYNAMIC_GROUPS_TL (
303 GROUP_ID,
304 CREATED_BY,
305 CREATION_DATE,
306 LAST_UPDATED_BY,
307 LAST_UPDATE_DATE,
308 LAST_UPDATE_LOGIN,
309 GROUP_NAME,
310 GROUP_DESC,
311 LANGUAGE,
312 SOURCE_LANG
313 ) select
314 B.GROUP_ID,
315 B.CREATED_BY,
316 B.CREATION_DATE,
317 B.LAST_UPDATED_BY,
318 B.LAST_UPDATE_DATE,
319 B.LAST_UPDATE_LOGIN,
320 B.GROUP_NAME,
321 B.GROUP_DESC,
322 L.LANGUAGE_CODE,
323 B.SOURCE_LANG
324 from JTF_RS_DYNAMIC_GROUPS_TL B, FND_LANGUAGES L
325 where L.INSTALLED_FLAG in ('I', 'B')
326 and B.LANGUAGE = userenv('LANG')
327 and not exists
328 (select NULL
329 from JTF_RS_DYNAMIC_GROUPS_TL T
330 where T.GROUP_ID = B.GROUP_ID
331 and T.LANGUAGE = L.LANGUAGE_CODE);
332 end ADD_LANGUAGE;
333
334
335 Procedure TRANSLATE_ROW
336 (x_group_id in number,
337 x_group_name in varchar2,
338 x_group_desc in varchar2,
339 x_Last_update_date in date,
340 x_last_updated_by in number,
341 x_last_update_login in number)
342 is
343 begin
344
345 Update jtf_rs_dynamic_groups_tl set
346 group_name = nvl(x_group_name,group_name),
347 group_desc = nvl(x_group_desc,group_desc),
348 last_update_date = nvl(x_last_update_date,sysdate),
349 last_updated_by = x_last_updated_by,
350 last_update_login = 0,
351 source_lang = userenv('LANG')
352 where group_id = x_group_id
353 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
354
355 if (sql%notfound) then
356 raise no_data_found;
357 end if;
358 end TRANSLATE_ROW;
359
360 end JTF_RS_DYNAMIC_GROUPS_PKG;