[Home] [Help]
PACKAGE BODY: APPS.XDP_FE_ATTRIBUTE_VAL_PKG
Source
1 package body XDP_FE_ATTRIBUTE_VAL_PKG as
2 /* $Header: XDPFEAVB.pls 120.2 2005/07/14 22:49:31 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_FE_ATTRIBUTE_ID in NUMBER,
6 X_FE_GENERIC_CONFIG_ID in NUMBER,
7 X_FE_ATTRIBUTE_VALUE in VARCHAR2,
8 X_DISPLAY_NAME in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from XDP_FE_ATTRIBUTE_VAL
17 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
18 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID
19 ;
20 begin
21 insert into XDP_FE_ATTRIBUTE_VAL (
22 FE_ATTRIBUTE_ID,
23 FE_GENERIC_CONFIG_ID,
24 FE_ATTRIBUTE_VALUE,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_FE_ATTRIBUTE_ID,
32 X_FE_GENERIC_CONFIG_ID,
33 X_FE_ATTRIBUTE_VALUE,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into XDP_FE_ATTRIBUTE_VAL_TL (
42 FE_ATTRIBUTE_ID,
43 FE_GENERIC_CONFIG_ID,
44 DISPLAY_NAME,
45 DESCRIPTION,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATE_LOGIN,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_FE_ATTRIBUTE_ID,
55 X_FE_GENERIC_CONFIG_ID,
56 X_DISPLAY_NAME,
57 X_DESCRIPTION,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from XDP_FE_ATTRIBUTE_VAL_TL T
70 where T.FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
71 and T.FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73
74 open c;
75 fetch c into X_ROWID;
76 if (c%notfound) then
77 close c;
78 raise no_data_found;
79 end if;
80 close c;
81
82 end INSERT_ROW;
83
84 procedure LOCK_ROW (
85 X_FE_ATTRIBUTE_ID in NUMBER,
86 X_FE_GENERIC_CONFIG_ID in NUMBER,
87 X_FE_ATTRIBUTE_VALUE in VARCHAR2,
88 X_DISPLAY_NAME in VARCHAR2,
89 X_DESCRIPTION in VARCHAR2
90 ) is
91 cursor c is select
92 FE_ATTRIBUTE_VALUE
93 from XDP_FE_ATTRIBUTE_VAL
94 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
95 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID
96 for update of FE_ATTRIBUTE_ID nowait;
97 recinfo c%rowtype;
98
99 cursor c1 is select
100 DISPLAY_NAME,
101 DESCRIPTION,
102 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
103 from XDP_FE_ATTRIBUTE_VAL_TL
104 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
105 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of FE_ATTRIBUTE_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( ((recinfo.FE_ATTRIBUTE_VALUE = X_FE_ATTRIBUTE_VALUE)
118 OR ((recinfo.FE_ATTRIBUTE_VALUE is null) AND (X_FE_ATTRIBUTE_VALUE is null)))
119 ) then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125
126 for tlinfo in c1 loop
127 if (tlinfo.BASELANG = 'Y') then
128 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
129 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
130 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137 end if;
138 end loop;
139 return;
140 end LOCK_ROW;
141
142 procedure UPDATE_ROW (
143 X_FE_ATTRIBUTE_ID in NUMBER,
144 X_FE_GENERIC_CONFIG_ID in NUMBER,
145 X_FE_ATTRIBUTE_VALUE in VARCHAR2,
146 X_DISPLAY_NAME in VARCHAR2,
147 X_DESCRIPTION in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update XDP_FE_ATTRIBUTE_VAL set
154 FE_ATTRIBUTE_VALUE = X_FE_ATTRIBUTE_VALUE,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
158 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
159 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID;
160
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164
165 update XDP_FE_ATTRIBUTE_VAL_TL set
166 DISPLAY_NAME = X_DISPLAY_NAME,
167 DESCRIPTION = X_DESCRIPTION,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
171 SOURCE_LANG = userenv('LANG')
172 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
173 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID
174 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179 end UPDATE_ROW;
180
181 procedure DELETE_ROW (
182 X_FE_ATTRIBUTE_ID in NUMBER,
183 X_FE_GENERIC_CONFIG_ID in NUMBER
184 ) is
185 begin
186 delete from XDP_FE_ATTRIBUTE_VAL_TL
187 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
188 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 delete from XDP_FE_ATTRIBUTE_VAL
195 where FE_ATTRIBUTE_ID = X_FE_ATTRIBUTE_ID
196 and FE_GENERIC_CONFIG_ID = X_FE_GENERIC_CONFIG_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end DELETE_ROW;
202
203 procedure ADD_LANGUAGE
204 is
205 begin
206 delete from XDP_FE_ATTRIBUTE_VAL_TL T
207 where not exists
208 (select NULL
209 from XDP_FE_ATTRIBUTE_VAL B
210 where B.FE_ATTRIBUTE_ID = T.FE_ATTRIBUTE_ID
211 and B.FE_GENERIC_CONFIG_ID = T.FE_GENERIC_CONFIG_ID
212 );
213
214 update XDP_FE_ATTRIBUTE_VAL_TL T set (
215 DISPLAY_NAME,
216 DESCRIPTION
217 ) = (select
218 B.DISPLAY_NAME,
219 B.DESCRIPTION
220 from XDP_FE_ATTRIBUTE_VAL_TL B
221 where B.FE_ATTRIBUTE_ID = T.FE_ATTRIBUTE_ID
222 and B.FE_GENERIC_CONFIG_ID = T.FE_GENERIC_CONFIG_ID
223 and B.LANGUAGE = T.SOURCE_LANG)
224 where (
225 T.FE_ATTRIBUTE_ID,
226 T.FE_GENERIC_CONFIG_ID,
227 T.LANGUAGE
228 ) in (select
229 SUBT.FE_ATTRIBUTE_ID,
230 SUBT.FE_GENERIC_CONFIG_ID,
231 SUBT.LANGUAGE
232 from XDP_FE_ATTRIBUTE_VAL_TL SUBB, XDP_FE_ATTRIBUTE_VAL_TL SUBT
233 where SUBB.FE_ATTRIBUTE_ID = SUBT.FE_ATTRIBUTE_ID
234 and SUBB.FE_GENERIC_CONFIG_ID = SUBT.FE_GENERIC_CONFIG_ID
235 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
236 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
237 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
238 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
239 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
240 ));
241
242 insert into XDP_FE_ATTRIBUTE_VAL_TL (
243 FE_ATTRIBUTE_ID,
244 FE_GENERIC_CONFIG_ID,
245 DISPLAY_NAME,
246 DESCRIPTION,
247 CREATED_BY,
248 CREATION_DATE,
249 LAST_UPDATED_BY,
250 LAST_UPDATE_DATE,
251 LAST_UPDATE_LOGIN,
252 LANGUAGE,
253 SOURCE_LANG
254 ) select
255 B.FE_ATTRIBUTE_ID,
256 B.FE_GENERIC_CONFIG_ID,
257 B.DISPLAY_NAME,
258 B.DESCRIPTION,
259 B.CREATED_BY,
260 B.CREATION_DATE,
261 B.LAST_UPDATED_BY,
262 B.LAST_UPDATE_DATE,
263 B.LAST_UPDATE_LOGIN,
264 L.LANGUAGE_CODE,
265 B.SOURCE_LANG
266 from XDP_FE_ATTRIBUTE_VAL_TL B, FND_LANGUAGES L
267 where L.INSTALLED_FLAG in ('I', 'B')
268 and B.LANGUAGE = userenv('LANG')
269 and not exists
270 (select NULL
271 from XDP_FE_ATTRIBUTE_VAL_TL T
272 where T.FE_ATTRIBUTE_ID = B.FE_ATTRIBUTE_ID
273 and T.FE_GENERIC_CONFIG_ID = B.FE_GENERIC_CONFIG_ID
274 and T.LANGUAGE = L.LANGUAGE_CODE);
275 end ADD_LANGUAGE;
276
277 procedure LOAD_ROW (
278 X_FE_ATTRIBUTE_ID in NUMBER,
279 X_FE_GENERIC_CONFIG_ID in NUMBER,
280 X_FE_ATTRIBUTE_VALUE in VARCHAR2,
281 X_DISPLAY_NAME in VARCHAR2,
282 X_DESCRIPTION in VARCHAR2,
283 X_OWNER in VARCHAR2) IS
284 begin
285
286 declare
287 user_id number := 0;
288 row_id varchar2(64);
289
290 begin
291
292 /* The following derivation has been replaced with the FND API. dputhiye 15-JUL-2005. R12 ATG "Seed Version by Date" Uptake */
293 --if (X_OWNER = 'SEED') then
294 -- user_id := 1;
295 --end if;
296 user_id := fnd_load_util.owner_id(X_OWNER);
297
298 XDP_FE_ATTRIBUTE_VAL_PKG.UPDATE_ROW (
299 X_FE_ATTRIBUTE_ID => X_FE_ATTRIBUTE_ID,
300 X_FE_GENERIC_CONFIG_ID => X_FE_GENERIC_CONFIG_ID,
301 X_FE_ATTRIBUTE_VALUE => X_FE_ATTRIBUTE_VALUE,
302 X_DISPLAY_NAME => X_DISPLAY_NAME,
303 X_DESCRIPTION => X_DESCRIPTION,
304 X_LAST_UPDATE_DATE => sysdate,
305 X_LAST_UPDATED_BY => user_id,
306 X_LAST_UPDATE_LOGIN => 0);
307
308 exception
309 when NO_DATA_FOUND then
310 XDP_FE_ATTRIBUTE_VAL_PKG.INSERT_ROW (
311 X_ROWID => row_id,
312 X_FE_ATTRIBUTE_ID => X_FE_ATTRIBUTE_ID,
313 X_FE_GENERIC_CONFIG_ID => X_FE_GENERIC_CONFIG_ID,
314 X_FE_ATTRIBUTE_VALUE => X_FE_ATTRIBUTE_VALUE,
315 X_DISPLAY_NAME => X_DISPLAY_NAME,
316 X_DESCRIPTION => X_DESCRIPTION,
317 X_CREATION_DATE => sysdate,
318 X_CREATED_BY => user_id,
319 X_LAST_UPDATE_DATE => sysdate,
320 X_LAST_UPDATED_BY => user_id,
321 X_LAST_UPDATE_LOGIN => 0);
322 end;
323 end LOAD_ROW;
324
325 procedure TRANSLATE_ROW (
326 X_FE_ATTRIBUTE_ID in NUMBER,
327 X_FE_GENERIC_CONFIG_ID in NUMBER,
328 X_DISPLAY_NAME in VARCHAR2,
329 X_DESCRIPTION in VARCHAR2,
330 X_OWNER in VARCHAR2) IS
331
332 begin
333
334 -- only update rows that have not been altered by user
335
336 update XDP_FE_ATTRIBUTE_VAL_TL
337 set display_name = X_DISPLAY_NAME,
338 description = X_DESCRIPTION,
339 source_lang = userenv('LANG'),
340 last_update_date = sysdate,
341 --last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
342 last_updated_by = fnd_load_util.owner_id(X_OWNER), /*dputhiye 15-JUL-2005. DECODE replaced with FND API.*/
343 last_update_login = 0
344 where fe_attribute_id = X_FE_ATTRIBUTE_ID
345 and fe_generic_config_id = X_FE_GENERIC_CONFIG_ID
346 and userenv('LANG') in (language, source_lang);
347
348 end TRANSLATE_ROW;
349
350
351 end XDP_FE_ATTRIBUTE_VAL_PKG;