[Home] [Help]
PACKAGE BODY: APPS.FND_DESCR_FLEX_CONTEXTS_PKG
Source
1 package body FND_DESCR_FLEX_CONTEXTS_PKG as
2 /* $Header: AFFFDFCB.pls 120.2.12010000.1 2008/07/25 14:13:45 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
8 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_GLOBAL_FLAG in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_DESCRIPTIVE_FLEX_CONTEXT_NAM in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from FND_DESCR_FLEX_CONTEXTS
20 where APPLICATION_ID = X_APPLICATION_ID
21 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
22 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
23 ;
24 begin
25 insert into FND_DESCR_FLEX_CONTEXTS (
26 APPLICATION_ID,
27 DESCRIPTIVE_FLEXFIELD_NAME,
28 DESCRIPTIVE_FLEX_CONTEXT_CODE,
29 ENABLED_FLAG,
30 GLOBAL_FLAG,
31 CREATION_DATE,
32 CREATED_BY,
33 LAST_UPDATE_DATE,
34 LAST_UPDATED_BY,
35 LAST_UPDATE_LOGIN
36 ) values (
37 X_APPLICATION_ID,
38 X_DESCRIPTIVE_FLEXFIELD_NAME,
39 X_DESCRIPTIVE_FLEX_CONTEXT_COD,
40 X_ENABLED_FLAG,
41 X_GLOBAL_FLAG,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into FND_DESCR_FLEX_CONTEXTS_TL (
50 APPLICATION_ID,
51 DESCRIPTIVE_FLEXFIELD_NAME,
52 DESCRIPTIVE_FLEX_CONTEXT_CODE,
53 DESCRIPTION,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 CREATION_DATE,
57 CREATED_BY,
58 LAST_UPDATE_LOGIN,
59 DESCRIPTIVE_FLEX_CONTEXT_NAME,
60 LANGUAGE,
61 SOURCE_LANG
62 ) select
63 X_APPLICATION_ID,
64 X_DESCRIPTIVE_FLEXFIELD_NAME,
65 X_DESCRIPTIVE_FLEX_CONTEXT_COD,
66 X_DESCRIPTION,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATED_BY,
69 X_CREATION_DATE,
70 X_CREATED_BY,
71 X_LAST_UPDATE_LOGIN,
72 X_DESCRIPTIVE_FLEX_CONTEXT_NAM,
73 L.LANGUAGE_CODE,
74 userenv('LANG')
75 from FND_LANGUAGES L
76 where L.INSTALLED_FLAG in ('I', 'B')
77 and not exists
78 (select NULL
79 from FND_DESCR_FLEX_CONTEXTS_TL T
80 where T.APPLICATION_ID = X_APPLICATION_ID
81 and T.DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
82 and T.DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
83 and T.LANGUAGE = L.LANGUAGE_CODE);
84
85 open c;
86 fetch c into X_ROWID;
87 if (c%notfound) then
88 close c;
89 raise no_data_found;
90 end if;
91 close c;
92
93 end INSERT_ROW;
94
95 procedure LOCK_ROW (
96 X_APPLICATION_ID in NUMBER,
97 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
98 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
99 X_ENABLED_FLAG in VARCHAR2,
100 X_GLOBAL_FLAG in VARCHAR2,
101 X_DESCRIPTION in VARCHAR2,
102 X_DESCRIPTIVE_FLEX_CONTEXT_NAM in VARCHAR2
103 ) is
104 cursor c is select
105 ENABLED_FLAG,
106 GLOBAL_FLAG
107 from FND_DESCR_FLEX_CONTEXTS
108 where APPLICATION_ID = X_APPLICATION_ID
109 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
110 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
111 for update of APPLICATION_ID nowait;
112 recinfo c%rowtype;
113
114 cursor c1 is select
115 DESCRIPTION,
116 DESCRIPTIVE_FLEX_CONTEXT_NAME
117 from FND_DESCR_FLEX_CONTEXTS_TL
118 where APPLICATION_ID = X_APPLICATION_ID
119 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
120 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
121 and LANGUAGE = userenv('LANG')
122 for update of APPLICATION_ID nowait;
123 tlinfo c1%rowtype;
124
125 begin
126 open c;
127 fetch c into recinfo;
128 if (c%notfound) then
129 close c;
130 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
131 app_exception.raise_exception;
132 end if;
133 close c;
134 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
135 AND (recinfo.GLOBAL_FLAG = X_GLOBAL_FLAG)
136 ) then
137 null;
138 else
139 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
140 app_exception.raise_exception;
141 end if;
142
143 open c1;
144 fetch c1 into tlinfo;
145 if (c1%notfound) then
146 close c1;
147 return;
148 end if;
149 close c1;
150
151 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
152 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
153 AND (tlinfo.DESCRIPTIVE_FLEX_CONTEXT_NAME = X_DESCRIPTIVE_FLEX_CONTEXT_NAM)
154 ) then
155 null;
156 else
157 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
158 app_exception.raise_exception;
159 end if;
160 return;
161 end LOCK_ROW;
162
163 procedure UPDATE_ROW (
164 X_APPLICATION_ID in NUMBER,
165 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
166 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2,
167 X_ENABLED_FLAG in VARCHAR2,
168 X_GLOBAL_FLAG in VARCHAR2,
169 X_DESCRIPTION in VARCHAR2,
170 X_DESCRIPTIVE_FLEX_CONTEXT_NAM in VARCHAR2,
171 X_LAST_UPDATE_DATE in DATE,
172 X_LAST_UPDATED_BY in NUMBER,
173 X_LAST_UPDATE_LOGIN in NUMBER
174 ) is
175 begin
176 update FND_DESCR_FLEX_CONTEXTS set
177 ENABLED_FLAG = X_ENABLED_FLAG,
178 GLOBAL_FLAG = X_GLOBAL_FLAG,
179 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
182 where APPLICATION_ID = X_APPLICATION_ID
183 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
184 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189
190 update FND_DESCR_FLEX_CONTEXTS_TL set
191 DESCRIPTION = X_DESCRIPTION,
192 DESCRIPTIVE_FLEX_CONTEXT_NAME = X_DESCRIPTIVE_FLEX_CONTEXT_NAM,
193 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
196 SOURCE_LANG = userenv('LANG')
197 where APPLICATION_ID = X_APPLICATION_ID
198 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
199 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD
200 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
201
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205 end UPDATE_ROW;
206
207 procedure DELETE_ROW (
208 X_APPLICATION_ID in NUMBER,
209 X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
210 X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2
211 ) is
212 begin
213 delete from FND_DESCR_FLEX_CONTEXTS
214 where APPLICATION_ID = X_APPLICATION_ID
215 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
216 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD;
217
218 if (sql%notfound) then
219 raise no_data_found;
220 end if;
221
222 delete from FND_DESCR_FLEX_CONTEXTS_TL
223 where APPLICATION_ID = X_APPLICATION_ID
224 and DESCRIPTIVE_FLEXFIELD_NAME = X_DESCRIPTIVE_FLEXFIELD_NAME
225 and DESCRIPTIVE_FLEX_CONTEXT_CODE = X_DESCRIPTIVE_FLEX_CONTEXT_COD;
226
227 if (sql%notfound) then
228 raise no_data_found;
229 end if;
230 end DELETE_ROW;
231
232 procedure ADD_LANGUAGE
233 is
234 begin
235
236 /* Mar/19/03 requested by Ric Ginsberg */
237 /* The following delete and update statements are commented out */
238 /* as a quick workaround to fix the time-consuming table handler issue */
239 /* Eventually we'll need to turn them into a separate fix_language procedure */
240 /*
241 delete from FND_DESCR_FLEX_CONTEXTS_TL T
242 where not exists
243 (select NULL
244 from FND_DESCR_FLEX_CONTEXTS B
245 where B.APPLICATION_ID = T.APPLICATION_ID
246 and B.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
247 and B.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.DESCRIPTIVE_FLEX_CONTEXT_CODE
248 );
249
250 update FND_DESCR_FLEX_CONTEXTS_TL T set (
251 DESCRIPTION,
252 DESCRIPTIVE_FLEX_CONTEXT_NAME
253 ) = (select
254 B.DESCRIPTION,
255 B.DESCRIPTIVE_FLEX_CONTEXT_NAME
256 from FND_DESCR_FLEX_CONTEXTS_TL B
257 where B.APPLICATION_ID = T.APPLICATION_ID
258 and B.DESCRIPTIVE_FLEXFIELD_NAME = T.DESCRIPTIVE_FLEXFIELD_NAME
259 and B.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.DESCRIPTIVE_FLEX_CONTEXT_CODE
260 and B.LANGUAGE = T.SOURCE_LANG)
261 where (
262 T.APPLICATION_ID,
263 T.DESCRIPTIVE_FLEXFIELD_NAME,
264 T.DESCRIPTIVE_FLEX_CONTEXT_CODE,
265 T.LANGUAGE
266 ) in (select
267 SUBT.APPLICATION_ID,
268 SUBT.DESCRIPTIVE_FLEXFIELD_NAME,
269 SUBT.DESCRIPTIVE_FLEX_CONTEXT_CODE,
270 SUBT.LANGUAGE
271 from FND_DESCR_FLEX_CONTEXTS_TL SUBB, FND_DESCR_FLEX_CONTEXTS_TL SUBT
272 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
273 and SUBB.DESCRIPTIVE_FLEXFIELD_NAME = SUBT.DESCRIPTIVE_FLEXFIELD_NAME
274 and SUBB.DESCRIPTIVE_FLEX_CONTEXT_CODE = SUBT.DESCRIPTIVE_FLEX_CONTEXT_CODE
275 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
276 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
277 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
278 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
279 or SUBB.DESCRIPTIVE_FLEX_CONTEXT_NAME <> SUBT.DESCRIPTIVE_FLEX_CONTEXT_NAME
280 ));
281 */
282
283 insert into FND_DESCR_FLEX_CONTEXTS_TL (
284 APPLICATION_ID,
285 DESCRIPTIVE_FLEXFIELD_NAME,
286 DESCRIPTIVE_FLEX_CONTEXT_CODE,
287 DESCRIPTION,
288 LAST_UPDATE_DATE,
289 LAST_UPDATED_BY,
290 CREATION_DATE,
291 CREATED_BY,
292 LAST_UPDATE_LOGIN,
293 DESCRIPTIVE_FLEX_CONTEXT_NAME,
294 LANGUAGE,
295 SOURCE_LANG
296 ) select
297 B.APPLICATION_ID,
298 B.DESCRIPTIVE_FLEXFIELD_NAME,
299 B.DESCRIPTIVE_FLEX_CONTEXT_CODE,
300 B.DESCRIPTION,
301 B.LAST_UPDATE_DATE,
302 B.LAST_UPDATED_BY,
303 B.CREATION_DATE,
304 B.CREATED_BY,
305 B.LAST_UPDATE_LOGIN,
306 B.DESCRIPTIVE_FLEX_CONTEXT_NAME,
307 L.LANGUAGE_CODE,
308 B.SOURCE_LANG
309 from FND_DESCR_FLEX_CONTEXTS_TL B, FND_LANGUAGES L
310 where L.INSTALLED_FLAG in ('I', 'B')
311 and B.LANGUAGE = userenv('LANG')
312 and not exists
313 (select NULL
314 from FND_DESCR_FLEX_CONTEXTS_TL T
315 where T.APPLICATION_ID = B.APPLICATION_ID
316 and T.DESCRIPTIVE_FLEXFIELD_NAME = B.DESCRIPTIVE_FLEXFIELD_NAME
317 and T.DESCRIPTIVE_FLEX_CONTEXT_CODE = B.DESCRIPTIVE_FLEX_CONTEXT_CODE
318 and T.LANGUAGE = L.LANGUAGE_CODE);
319 end ADD_LANGUAGE;
320
321 PROCEDURE load_row
322 (x_application_short_name IN VARCHAR2,
323 x_descriptive_flexfield_name IN VARCHAR2,
324 x_descriptive_flex_context_cod IN VARCHAR2,
325 x_who IN fnd_flex_loader_apis.who_type,
326 x_enabled_flag IN VARCHAR2,
327 x_global_flag IN VARCHAR2,
328 x_description IN VARCHAR2,
329 x_descriptive_flex_context_nam IN VARCHAR2)
330 IS
331 l_application_id NUMBER;
332 l_rowid VARCHAR2(64);
333 BEGIN
334 SELECT application_id
335 INTO l_application_id
336 FROM fnd_application
337 WHERE application_short_name = x_application_short_name;
338
339 BEGIN
340 fnd_descr_flex_contexts_pkg.update_row
341 (X_APPLICATION_ID => l_application_id,
342 X_DESCRIPTIVE_FLEXFIELD_NAME => x_descriptive_flexfield_name,
343 X_DESCRIPTIVE_FLEX_CONTEXT_COD => x_descriptive_flex_context_cod,
344 X_ENABLED_FLAG => x_enabled_flag,
345 X_GLOBAL_FLAG => x_global_flag,
346 X_DESCRIPTION => x_description,
347 X_DESCRIPTIVE_FLEX_CONTEXT_NAM => x_descriptive_flex_context_nam,
348 X_LAST_UPDATE_DATE => x_who.last_update_date,
349 X_LAST_UPDATED_BY => x_who.last_updated_by,
350 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
351 EXCEPTION
352 WHEN no_data_found THEN
353 fnd_descr_flex_contexts_pkg.insert_row
354 (X_ROWID => l_rowid,
355 X_APPLICATION_ID => l_application_id,
356 X_DESCRIPTIVE_FLEXFIELD_NAME => x_descriptive_flexfield_name,
357 X_DESCRIPTIVE_FLEX_CONTEXT_COD => x_descriptive_flex_context_cod,
358 X_ENABLED_FLAG => x_enabled_flag,
359 X_GLOBAL_FLAG => x_global_flag,
360 X_DESCRIPTION => x_description,
361 X_DESCRIPTIVE_FLEX_CONTEXT_NAM => x_descriptive_flex_context_nam,
362 X_CREATION_DATE => x_who.creation_date,
363 X_CREATED_BY => x_who.created_by,
364 X_LAST_UPDATE_DATE => x_who.last_update_date,
365 X_LAST_UPDATED_BY => x_who.last_updated_by,
366 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
367 END;
368 END load_row;
369
370 PROCEDURE translate_row
371 (x_application_short_name IN VARCHAR2,
372 x_descriptive_flexfield_name IN VARCHAR2,
373 x_descriptive_flex_context_cod IN VARCHAR2,
374 x_who IN fnd_flex_loader_apis.who_type,
375 x_description IN VARCHAR2,
376 x_descriptive_flex_context_nam IN VARCHAR2)
377 IS
378 BEGIN
379 UPDATE fnd_descr_flex_contexts_tl SET
380 description = Nvl(x_description, description),
381 descriptive_flex_context_name = Nvl(x_descriptive_flex_context_nam,
382 descriptive_flex_context_name),
383 last_update_date = x_who.last_update_date,
384 last_updated_by = x_who.last_updated_by,
385 last_update_login = x_who.last_update_login,
386 source_lang = userenv('LANG')
387 WHERE application_id = (SELECT application_id
388 FROM fnd_application
389 WHERE application_short_name = x_application_short_name)
390 AND descriptive_flexfield_name = x_descriptive_flexfield_name
391 AND descriptive_flex_context_code = x_descriptive_flex_context_cod
392 AND userenv('LANG') in (language, source_lang);
393 END translate_row;
394
395 end FND_DESCR_FLEX_CONTEXTS_PKG;