[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_FIELDS_PKG
Source
1 PACKAGE BODY AMS_LIST_FIELDS_PKG AS
2 /* $Header: amsllfdb.pls 115.3 2000/01/09 17:37:58 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_LIST_FIELD_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_FIELD_TABLE_NAME in VARCHAR2,
8 X_FIELD_COLUMN_NAME in VARCHAR2,
9 X_COLUMN_DATA_TYPE in VARCHAR2,
10 X_COLUMN_DATA_LENGTH in NUMBER,
11 X_ENABLED_FLAG in VARCHAR2,
12 X_LIST_TYPE_FIELD_APPLY_ON in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from AMS_LIST_FIELDS_B
21 where LIST_FIELD_ID = X_LIST_FIELD_ID
22 ;
23 begin
24 insert into AMS_LIST_FIELDS_B (
25 LIST_FIELD_ID,
26 OBJECT_VERSION_NUMBER,
27 FIELD_TABLE_NAME,
28 FIELD_COLUMN_NAME,
29 COLUMN_DATA_TYPE,
30 COLUMN_DATA_LENGTH,
31 ENABLED_FLAG,
32 LIST_TYPE_FIELD_APPLY_ON,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_LIST_FIELD_ID,
40 X_OBJECT_VERSION_NUMBER,
41 X_FIELD_TABLE_NAME,
42 X_FIELD_COLUMN_NAME,
43 X_COLUMN_DATA_TYPE,
44 X_COLUMN_DATA_LENGTH,
45 X_ENABLED_FLAG,
46 X_LIST_TYPE_FIELD_APPLY_ON,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into AMS_LIST_FIELDS_TL (
55 LIST_FIELD_ID,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_LOGIN,
61 DESCRIPTION,
62 LANGUAGE,
63 SOURCE_LANG
64 ) select
65 X_LIST_FIELD_ID,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATED_BY,
68 X_CREATION_DATE,
69 X_CREATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_DESCRIPTION,
72 L.LANGUAGE_CODE,
73 userenv('LANG')
74 from FND_LANGUAGES L
75 where L.INSTALLED_FLAG in ('I', 'B')
76 and not exists
77 (select NULL
78 from AMS_LIST_FIELDS_TL T
79 where T.LIST_FIELD_ID = X_LIST_FIELD_ID
80 and T.LANGUAGE = L.LANGUAGE_CODE);
81
82 open c;
83 fetch c into X_ROWID;
84 if (c%notfound) then
85 close c;
86 raise no_data_found;
87 end if;
88 close c;
89
90 end INSERT_ROW;
91
92 procedure LOCK_ROW (
93 X_LIST_FIELD_ID in NUMBER,
94 X_OBJECT_VERSION_NUMBER in NUMBER,
95 X_FIELD_TABLE_NAME in VARCHAR2,
96 X_FIELD_COLUMN_NAME in VARCHAR2,
97 X_COLUMN_DATA_TYPE in VARCHAR2,
98 X_COLUMN_DATA_LENGTH in NUMBER,
99 X_ENABLED_FLAG in VARCHAR2,
100 X_LIST_TYPE_FIELD_APPLY_ON in VARCHAR2,
101 X_DESCRIPTION in VARCHAR2
102 ) is
103 cursor c is select
104 OBJECT_VERSION_NUMBER,
105 FIELD_TABLE_NAME,
106 FIELD_COLUMN_NAME,
107 COLUMN_DATA_TYPE,
108 COLUMN_DATA_LENGTH,
109 ENABLED_FLAG,
110 LIST_TYPE_FIELD_APPLY_ON
111 from AMS_LIST_FIELDS_B
112 where LIST_FIELD_ID = X_LIST_FIELD_ID
113 for update of LIST_FIELD_ID nowait;
114 recinfo c%rowtype;
115
116 cursor c1 is select
117 DESCRIPTION,
118 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119 from AMS_LIST_FIELDS_TL
120 where LIST_FIELD_ID = X_LIST_FIELD_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122 for update of LIST_FIELD_ID nowait;
123 begin
124 open c;
125 fetch c into recinfo;
126 if (c%notfound) then
127 close c;
128 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129 app_exception.raise_exception;
130 end if;
131 close c;
132 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
133 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
134 AND (recinfo.FIELD_TABLE_NAME = X_FIELD_TABLE_NAME)
135 AND (recinfo.FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME)
136 AND (recinfo.COLUMN_DATA_TYPE = X_COLUMN_DATA_TYPE)
137 AND (recinfo.COLUMN_DATA_LENGTH = X_COLUMN_DATA_LENGTH)
138 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
139 AND ((recinfo.LIST_TYPE_FIELD_APPLY_ON = X_LIST_TYPE_FIELD_APPLY_ON)
140 OR ((recinfo.LIST_TYPE_FIELD_APPLY_ON is null) AND (X_LIST_TYPE_FIELD_APPLY_ON is null)))
141 ) then
142 null;
143 else
144 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
145 app_exception.raise_exception;
146 end if;
147
148 for tlinfo in c1 loop
149 if (tlinfo.BASELANG = 'Y') then
150 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
151 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158 end if;
159 end loop;
160 return;
164 X_LIST_FIELD_ID in NUMBER,
161 end LOCK_ROW;
162
163 procedure UPDATE_ROW (
165 X_OBJECT_VERSION_NUMBER in NUMBER,
166 X_FIELD_TABLE_NAME in VARCHAR2,
167 X_FIELD_COLUMN_NAME in VARCHAR2,
168 X_COLUMN_DATA_TYPE in VARCHAR2,
169 X_COLUMN_DATA_LENGTH in NUMBER,
170 X_ENABLED_FLAG in VARCHAR2,
171 X_LIST_TYPE_FIELD_APPLY_ON in VARCHAR2,
172 X_DESCRIPTION in VARCHAR2,
173 X_LAST_UPDATE_DATE in DATE,
174 X_LAST_UPDATED_BY in NUMBER,
175 X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 begin
178 update AMS_LIST_FIELDS_B set
179 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
180 FIELD_TABLE_NAME = X_FIELD_TABLE_NAME,
181 FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME,
182 COLUMN_DATA_TYPE = X_COLUMN_DATA_TYPE,
183 COLUMN_DATA_LENGTH = X_COLUMN_DATA_LENGTH,
184 ENABLED_FLAG = X_ENABLED_FLAG,
185 LIST_TYPE_FIELD_APPLY_ON = X_LIST_TYPE_FIELD_APPLY_ON,
186 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
187 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
189 where LIST_FIELD_ID = X_LIST_FIELD_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194
195 update AMS_LIST_FIELDS_TL set
196 DESCRIPTION = X_DESCRIPTION,
197 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
200 SOURCE_LANG = userenv('LANG')
201 where LIST_FIELD_ID = X_LIST_FIELD_ID
202 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end UPDATE_ROW;
208
209 procedure DELETE_ROW (
210 X_LIST_FIELD_ID in NUMBER
211 ) is
212 begin
213 delete from AMS_LIST_FIELDS_TL
214 where LIST_FIELD_ID = X_LIST_FIELD_ID;
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219
220 delete from AMS_LIST_FIELDS_B
221 where LIST_FIELD_ID = X_LIST_FIELD_ID;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226 end DELETE_ROW;
227
228 procedure ADD_LANGUAGE
229 is
230 begin
231 delete from AMS_LIST_FIELDS_TL T
232 where not exists
233 (select NULL
234 from AMS_LIST_FIELDS_B B
235 where B.LIST_FIELD_ID = T.LIST_FIELD_ID
236 );
237
238 update AMS_LIST_FIELDS_TL T set (
239 DESCRIPTION
240 ) = (select
241 B.DESCRIPTION
242 from AMS_LIST_FIELDS_TL B
243 where B.LIST_FIELD_ID = T.LIST_FIELD_ID
244 and B.LANGUAGE = T.SOURCE_LANG)
245 where (
249 SUBT.LIST_FIELD_ID,
246 T.LIST_FIELD_ID,
247 T.LANGUAGE
248 ) in (select
250 SUBT.LANGUAGE
251 from AMS_LIST_FIELDS_TL SUBB, AMS_LIST_FIELDS_TL SUBT
252 where SUBB.LIST_FIELD_ID = SUBT.LIST_FIELD_ID
253 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
254 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
255 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
256 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
257 ));
258
259 insert into AMS_LIST_FIELDS_TL (
260 LIST_FIELD_ID,
261 LAST_UPDATE_DATE,
262 LAST_UPDATED_BY,
263 CREATION_DATE,
264 CREATED_BY,
265 LAST_UPDATE_LOGIN,
266 DESCRIPTION,
267 LANGUAGE,
268 SOURCE_LANG
269 ) select
270 B.LIST_FIELD_ID,
271 B.LAST_UPDATE_DATE,
272 B.LAST_UPDATED_BY,
273 B.CREATION_DATE,
274 B.CREATED_BY,
275 B.LAST_UPDATE_LOGIN,
276 B.DESCRIPTION,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from AMS_LIST_FIELDS_TL B, FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and B.LANGUAGE = userenv('LANG')
282 and not exists
283 (select NULL
284 from AMS_LIST_FIELDS_TL T
285 where T.LIST_FIELD_ID = B.LIST_FIELD_ID
286 and T.LANGUAGE = L.LANGUAGE_CODE);
287 end ADD_LANGUAGE;
288
289 procedure TRANSLATE_ROW(
290 x_list_field_id in NUMBER
291 , x_description in VARCHAR2
292 , x_owner in VARCHAR2
293 )
294 IS
295 BEGIN
296 update ams_list_fields_tl set
297 description = nvl(x_description, description),
298 source_lang = userenv('LANG'),
299 last_update_date = sysdate,
300 last_updated_by = decode(x_owner, 'SEED', 1, 0),
301 last_update_login = 0
302 where list_field_id = x_list_field_id
303 and userenv('LANG') in (language, source_lang);
304 END Translate_Row;
305
306 PROCEDURE Load_Row (
307 X_LIST_FIELD_ID in NUMBER,
308 X_FIELD_TABLE_NAME in VARCHAR2,
309 X_FIELD_COLUMN_NAME in VARCHAR2,
310 X_COLUMN_DATA_TYPE in VARCHAR2,
311 X_COLUMN_DATA_LENGTH in NUMBER,
312 X_ENABLED_FLAG in VARCHAR2,
313 X_LIST_TYPE_FIELD_APPLY_ON in VARCHAR2,
314 X_DESCRIPTION in VARCHAR2,
315 x_OWNER IN VARCHAR2
316 )
317 IS
318 l_user_id number := 0;
319 l_obj_verno number;
320 l_dummy_char varchar2(1);
321 l_row_id varchar2(100);
322 l_list_field_id number;
323
324 CURSOR c_obj_verno IS
325 SELECT object_version_number
326 FROM ams_list_fields_b
327 WHERE list_field_id = X_LIST_FIELD_ID;
328
329 CURSOR c_chk_lfd_exists is
330 SELECT 'x'
331 FROM ams_list_fields_b
332 WHERE list_field_id = x_list_field_id;
333
334 CURSOR c_get_lfd_id is
338 if X_OWNER = 'SEED' then
335 SELECT ams_list_fields_b_s.NEXTVAL
336 FROM DUAL;
337 BEGIN
339 l_user_id := 1;
340 end if;
341
342 OPEN c_chk_lfd_exists;
343 FETCH c_chk_lfd_exists INTO l_dummy_char;
344 IF c_chk_lfd_exists%notfound THEN
345 CLOSE c_chk_lfd_exists;
346 IF x_list_field_id IS NULL THEN
347 OPEN c_get_lfd_id;
348 FETCH c_get_lfd_id INTO l_list_field_id;
349 CLOSE c_get_lfd_id;
350 ELSE
351 l_list_field_id := x_list_field_id;
352 END IF;
353
354 l_obj_verno := 1;
355
356 AMS_List_Fields_PKG.Insert_Row (
357 X_ROWID => l_row_id,
358 X_LIST_FIELD_ID => l_list_field_id,
359 X_OBJECT_VERSION_NUMBER => l_obj_verno,
360 X_FIELD_TABLE_NAME => x_field_table_name,
361 X_FIELD_COLUMN_NAME => x_field_column_name,
362 X_COLUMN_DATA_TYPE => x_column_data_type,
363 X_COLUMN_DATA_LENGTH => x_column_data_length,
364 X_ENABLED_FLAG => x_enabled_flag,
365 X_LIST_TYPE_FIELD_APPLY_ON => x_list_type_field_apply_on,
366 X_DESCRIPTION => x_description,
367 X_CREATION_DATE => SYSDATE,
368 X_CREATED_BY => l_user_id,
369 X_LAST_UPDATE_DATE => SYSDATE,
370 X_LAST_UPDATED_BY => l_user_id,
371 X_LAST_UPDATE_LOGIN => 0
372 );
373 ELSE
374 CLOSE c_chk_lfd_exists;
375 OPEN c_obj_verno;
376 FETCH c_obj_verno INTO l_obj_verno;
377 CLOSE c_obj_verno;
378
379 AMS_List_Fields_PKG.Update_Row (
380 x_list_field_id => x_list_field_id,
381 x_object_version_number => l_obj_verno,
382 x_field_table_name => x_field_table_name,
383 x_field_column_name => x_field_column_name,
384 x_column_data_type => x_column_data_type,
385 x_column_data_length => x_column_data_length,
386 x_enabled_flag => x_enabled_flag,
387 x_list_type_field_apply_on => x_list_type_field_apply_on,
388 x_description => x_description,
389 x_last_update_date => SYSDATE,
390 x_last_updated_by => l_user_id,
391 x_last_update_login => 0
392 );
393 END IF;
394 END Load_Row;
395
396 end AMS_LIST_FIELDS_PKG;