1 package body AMS_LIST_SRC_FIELDS_NEW_PKG as
2 /* $Header: amstdsab.pls 120.2 2005/08/31 13:37:18 vbhandar ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_LIST_SOURCE_FIELD_ID in NUMBER,
6 X_FIELD_LOOKUP_TYPE in VARCHAR2,
7 X_FIELD_LOOKUP_TYPE_VIEW_NAME in VARCHAR2,
8 X_ALLOW_LABEL_OVERRIDE in VARCHAR2,
9 X_FIELD_USAGE_TYPE in VARCHAR2,
10 X_DIALOG_ENABLED in VARCHAR2,
11 X_ANALYTICS_FLAG in VARCHAR2,
12 X_AUTO_BINNING_FLAG in VARCHAR2,
13 X_NO_OF_BUCKETS in NUMBER,
14 X_ATTB_LOV_ID in NUMBER,
15 X_TCA_COLUMN_ID in NUMBER,
16 X_USED_IN_LIST_ENTRIES in VARCHAR2,
17 X_CHART_ENABLED_FLAG in VARCHAR2,
18 X_DEFAULT_CHART_TYPE in VARCHAR2,
19 X_LOV_DEFINED_FLAG in VARCHAR2,
20 X_USE_FOR_SPLITTING_FLAG in VARCHAR2,
21 X_DEFAULT_UI_CONTROL in VARCHAR2,
22 X_OBJECT_VERSION_NUMBER in NUMBER,
23 X_DE_LIST_SOURCE_TYPE_CODE in VARCHAR2,
24 X_LIST_SOURCE_TYPE_ID in NUMBER,
25 X_FIELD_TABLE_NAME in VARCHAR2,
26 X_FIELD_COLUMN_NAME in VARCHAR2,
27 X_SOURCE_COLUMN_NAME in VARCHAR2,
28 X_ENABLED_FLAG in VARCHAR2,
29 X_START_POSITION in NUMBER,
30 X_END_POSITION in NUMBER,
31 X_SECURITY_GROUP_ID in NUMBER,
32 X_FIELD_DATA_TYPE in VARCHAR2,
33 X_FIELD_DATA_SIZE in NUMBER,
34 X_SOURCE_COLUMN_MEANING in VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER,
40 X_COLUMN_TYPE IN VARCHAR2
41 ) is
42 cursor C is select ROWID from AMS_LIST_SRC_FIELDS
43 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID
44 ;
45 begin
46 insert into AMS_LIST_SRC_FIELDS (
47 FIELD_LOOKUP_TYPE,
48 FIELD_LOOKUP_TYPE_VIEW_NAME,
49 ALLOW_LABEL_OVERRIDE,
50 FIELD_USAGE_TYPE,
51 DIALOG_ENABLED,
52 ANALYTICS_FLAG,
53 AUTO_BINNING_FLAG,
54 NO_OF_BUCKETS,
55 ATTB_LOV_ID,
56 TCA_COLUMN_ID,
57 USED_IN_LIST_ENTRIES,
58 CHART_ENABLED_FLAG,
59 DEFAULT_CHART_TYPE,
60 LOV_DEFINED_FLAG,
61 USE_FOR_SPLITTING_FLAG,
62 DEFAULT_UI_CONTROL,
63 LIST_SOURCE_FIELD_ID,
64 OBJECT_VERSION_NUMBER,
65 DE_LIST_SOURCE_TYPE_CODE,
66 LIST_SOURCE_TYPE_ID,
67 FIELD_TABLE_NAME,
68 FIELD_COLUMN_NAME,
69 SOURCE_COLUMN_NAME,
70 ENABLED_FLAG,
71 START_POSITION,
72 END_POSITION,
73 SECURITY_GROUP_ID,
74 FIELD_DATA_TYPE,
75 FIELD_DATA_SIZE,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN,
81 COLUMN_TYPE
82 ) values (
83 X_FIELD_LOOKUP_TYPE,
84 X_FIELD_LOOKUP_TYPE_VIEW_NAME,
85 X_ALLOW_LABEL_OVERRIDE,
86 X_FIELD_USAGE_TYPE,
87 X_DIALOG_ENABLED,
88 X_ANALYTICS_FLAG,
89 X_AUTO_BINNING_FLAG,
90 X_NO_OF_BUCKETS,
91 X_ATTB_LOV_ID,
92 X_TCA_COLUMN_ID,
93 X_USED_IN_LIST_ENTRIES,
94 X_CHART_ENABLED_FLAG,
95 X_DEFAULT_CHART_TYPE,
96 X_LOV_DEFINED_FLAG,
97 X_USE_FOR_SPLITTING_FLAG,
98 X_DEFAULT_UI_CONTROL,
99 X_LIST_SOURCE_FIELD_ID,
100 X_OBJECT_VERSION_NUMBER,
101 X_DE_LIST_SOURCE_TYPE_CODE,
102 X_LIST_SOURCE_TYPE_ID,
103 X_FIELD_TABLE_NAME,
104 X_FIELD_COLUMN_NAME,
105 X_SOURCE_COLUMN_NAME,
106 X_ENABLED_FLAG,
107 X_START_POSITION,
108 X_END_POSITION,
109 X_SECURITY_GROUP_ID,
110 X_FIELD_DATA_TYPE,
111 X_FIELD_DATA_SIZE,
112 X_CREATION_DATE,
113 X_CREATED_BY,
114 X_LAST_UPDATE_DATE,
115 X_LAST_UPDATED_BY,
116 X_LAST_UPDATE_LOGIN,
117 X_COLUMN_TYPE
118 );
119
120 insert into AMS_LIST_SRC_FIELDS_TL (
121 LIST_SOURCE_FIELD_ID,
122 LAST_UPDATE_DATE,
123 LAST_UPDATE_BY,
124 CREATION_DATE,
125 CREATED_BY,
126 LAST_UPDATE_LOGIN,
127 SOURCE_COLUMN_MEANING,
128 SECURITY_GROUP_ID,
129 LANGUAGE,
130 SOURCE_LANG
131 ) select
132 X_LIST_SOURCE_FIELD_ID,
133 X_LAST_UPDATE_DATE,
134 X_LAST_UPDATED_BY,
135 X_CREATION_DATE,
136 X_CREATED_BY,
137 X_LAST_UPDATE_LOGIN,
138 X_SOURCE_COLUMN_MEANING,
139 X_SECURITY_GROUP_ID,
140 L.LANGUAGE_CODE,
141 userenv('LANG')
142 from FND_LANGUAGES L
143 where L.INSTALLED_FLAG in ('I', 'B')
144 and not exists
145 (select NULL
146 from AMS_LIST_SRC_FIELDS_TL T
147 where T.LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID
148 and T.LANGUAGE = L.LANGUAGE_CODE);
149
150 open c;
151 fetch c into X_ROWID;
152 if (c%notfound) then
153 close c;
154 raise no_data_found;
155 end if;
156 close c;
157
158 end INSERT_ROW;
159
160 procedure LOCK_ROW (
161 X_LIST_SOURCE_FIELD_ID in NUMBER,
162 X_FIELD_LOOKUP_TYPE in VARCHAR2,
163 X_FIELD_LOOKUP_TYPE_VIEW_NAME in VARCHAR2,
164 X_ALLOW_LABEL_OVERRIDE in VARCHAR2,
165 X_FIELD_USAGE_TYPE in VARCHAR2,
166 X_DIALOG_ENABLED in VARCHAR2,
167 X_ANALYTICS_FLAG in VARCHAR2,
168 X_AUTO_BINNING_FLAG in VARCHAR2,
169 X_NO_OF_BUCKETS in NUMBER,
170 X_ATTB_LOV_ID in NUMBER,
171 X_TCA_COLUMN_ID in NUMBER,
172 X_USED_IN_LIST_ENTRIES in VARCHAR2,
173 X_CHART_ENABLED_FLAG in VARCHAR2,
174 X_DEFAULT_CHART_TYPE in VARCHAR2,
175 X_LOV_DEFINED_FLAG in VARCHAR2,
176 X_USE_FOR_SPLITTING_FLAG in VARCHAR2,
177 X_DEFAULT_UI_CONTROL in VARCHAR2,
178 X_OBJECT_VERSION_NUMBER in NUMBER,
179 X_DE_LIST_SOURCE_TYPE_CODE in VARCHAR2,
180 X_LIST_SOURCE_TYPE_ID in NUMBER,
181 X_FIELD_TABLE_NAME in VARCHAR2,
182 X_FIELD_COLUMN_NAME in VARCHAR2,
183 X_SOURCE_COLUMN_NAME in VARCHAR2,
184 X_ENABLED_FLAG in VARCHAR2,
185 X_START_POSITION in NUMBER,
186 X_END_POSITION in NUMBER,
187 X_SECURITY_GROUP_ID in NUMBER,
188 X_FIELD_DATA_TYPE in VARCHAR2,
189 X_FIELD_DATA_SIZE in NUMBER,
190 X_SOURCE_COLUMN_MEANING in VARCHAR2,
191 X_COLUMN_TYPE IN VARCHAR2
192 ) is
193 cursor c is select
194 FIELD_LOOKUP_TYPE,
195 FIELD_LOOKUP_TYPE_VIEW_NAME,
196 ALLOW_LABEL_OVERRIDE,
197 FIELD_USAGE_TYPE,
198 DIALOG_ENABLED,
199 ANALYTICS_FLAG,
200 AUTO_BINNING_FLAG,
201 NO_OF_BUCKETS,
202 ATTB_LOV_ID,
203 TCA_COLUMN_ID,
204 USED_IN_LIST_ENTRIES,
205 CHART_ENABLED_FLAG,
206 DEFAULT_CHART_TYPE,
207 LOV_DEFINED_FLAG,
208 USE_FOR_SPLITTING_FLAG,
209 DEFAULT_UI_CONTROL,
210 OBJECT_VERSION_NUMBER,
211 DE_LIST_SOURCE_TYPE_CODE,
212 LIST_SOURCE_TYPE_ID,
213 FIELD_TABLE_NAME,
214 FIELD_COLUMN_NAME,
215 SOURCE_COLUMN_NAME,
216 ENABLED_FLAG,
217 START_POSITION,
218 END_POSITION,
219 SECURITY_GROUP_ID,
220 FIELD_DATA_TYPE,
221 FIELD_DATA_SIZE,
222 COLUMN_TYPE
223 from AMS_LIST_SRC_FIELDS
224 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID
225 for update of LIST_SOURCE_FIELD_ID nowait;
226 recinfo c%rowtype;
227
228 cursor c1 is select
229 SOURCE_COLUMN_MEANING,
230 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
231 from AMS_LIST_SRC_FIELDS_TL
232 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID
233 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
234 for update of LIST_SOURCE_FIELD_ID nowait;
235 begin
236 open c;
237 fetch c into recinfo;
238 if (c%notfound) then
239 close c;
240 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
241 app_exception.raise_exception;
242 end if;
243 close c;
244 if ( ((recinfo.FIELD_LOOKUP_TYPE = X_FIELD_LOOKUP_TYPE)
245 OR ((recinfo.FIELD_LOOKUP_TYPE is null) AND (X_FIELD_LOOKUP_TYPE is null)))
246 AND ((recinfo.FIELD_LOOKUP_TYPE_VIEW_NAME = X_FIELD_LOOKUP_TYPE_VIEW_NAME)
247 OR ((recinfo.FIELD_LOOKUP_TYPE_VIEW_NAME is null) AND (X_FIELD_LOOKUP_TYPE_VIEW_NAME is null)))
248 AND ((recinfo.ALLOW_LABEL_OVERRIDE = X_ALLOW_LABEL_OVERRIDE)
249 OR ((recinfo.ALLOW_LABEL_OVERRIDE is null) AND (X_ALLOW_LABEL_OVERRIDE is null)))
250 AND ((recinfo.FIELD_USAGE_TYPE = X_FIELD_USAGE_TYPE)
251 OR ((recinfo.FIELD_USAGE_TYPE is null) AND (X_FIELD_USAGE_TYPE is null)))
252 AND ((recinfo.DIALOG_ENABLED = X_DIALOG_ENABLED)
253 OR ((recinfo.DIALOG_ENABLED is null) AND (X_DIALOG_ENABLED is null)))
254 AND ((recinfo.ANALYTICS_FLAG = X_ANALYTICS_FLAG)
255 OR ((recinfo.ANALYTICS_FLAG is null) AND (X_ANALYTICS_FLAG is null)))
256 AND ((recinfo.AUTO_BINNING_FLAG = X_AUTO_BINNING_FLAG)
257 OR ((recinfo.AUTO_BINNING_FLAG is null) AND (X_AUTO_BINNING_FLAG is null)))
258 AND ((recinfo.NO_OF_BUCKETS = X_NO_OF_BUCKETS)
259 OR ((recinfo.NO_OF_BUCKETS is null) AND (X_NO_OF_BUCKETS is null)))
260 AND ((recinfo.ATTB_LOV_ID = X_ATTB_LOV_ID)
261 OR ((recinfo.ATTB_LOV_ID is null) AND (X_ATTB_LOV_ID is null)))
262 AND ((recinfo.TCA_COLUMN_ID = X_TCA_COLUMN_ID)
263 OR ((recinfo.TCA_COLUMN_ID is null) AND (X_TCA_COLUMN_ID is null)))
264 AND ((recinfo.USED_IN_LIST_ENTRIES = X_USED_IN_LIST_ENTRIES)
265 OR ((recinfo.USED_IN_LIST_ENTRIES is null) AND (X_USED_IN_LIST_ENTRIES is null)))
266 AND ((recinfo.CHART_ENABLED_FLAG = X_CHART_ENABLED_FLAG)
267 OR ((recinfo.CHART_ENABLED_FLAG is null) AND (X_CHART_ENABLED_FLAG is null)))
268 AND ((recinfo.DEFAULT_CHART_TYPE = X_DEFAULT_CHART_TYPE)
269 OR ((recinfo.DEFAULT_CHART_TYPE is null) AND (X_DEFAULT_CHART_TYPE is null)))
270 AND ((recinfo.LOV_DEFINED_FLAG = X_LOV_DEFINED_FLAG)
271 OR ((recinfo.LOV_DEFINED_FLAG is null) AND (X_LOV_DEFINED_FLAG is null)))
272 AND ((recinfo.USE_FOR_SPLITTING_FLAG = X_USE_FOR_SPLITTING_FLAG)
273 OR ((recinfo.USE_FOR_SPLITTING_FLAG is null) AND (X_USE_FOR_SPLITTING_FLAG is null)))
274 AND ((recinfo.DEFAULT_UI_CONTROL = X_DEFAULT_UI_CONTROL)
275 OR ((recinfo.DEFAULT_UI_CONTROL is null) AND (X_DEFAULT_UI_CONTROL is null)))
276 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
277 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
278 AND (recinfo.DE_LIST_SOURCE_TYPE_CODE = X_DE_LIST_SOURCE_TYPE_CODE)
279 AND (recinfo.LIST_SOURCE_TYPE_ID = X_LIST_SOURCE_TYPE_ID)
280 AND ((recinfo.FIELD_TABLE_NAME = X_FIELD_TABLE_NAME)
281 OR ((recinfo.FIELD_TABLE_NAME is null) AND (X_FIELD_TABLE_NAME is null)))
282 AND ((recinfo.FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME)
283 OR ((recinfo.FIELD_COLUMN_NAME is null) AND (X_FIELD_COLUMN_NAME is null)))
284 AND (recinfo.SOURCE_COLUMN_NAME = X_SOURCE_COLUMN_NAME)
285 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
286 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
287 AND ((recinfo.START_POSITION = X_START_POSITION)
288 OR ((recinfo.START_POSITION is null) AND (X_START_POSITION is null)))
289 AND ((recinfo.END_POSITION = X_END_POSITION)
290 OR ((recinfo.END_POSITION is null) AND (X_END_POSITION is null)))
291 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
292 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
293 AND ((recinfo.FIELD_DATA_TYPE = X_FIELD_DATA_TYPE)
294 OR ((recinfo.FIELD_DATA_TYPE is null) AND (X_FIELD_DATA_TYPE is null)))
295 AND ((recinfo.FIELD_DATA_SIZE = X_FIELD_DATA_SIZE)
296 OR ((recinfo.FIELD_DATA_SIZE is null) AND (X_FIELD_DATA_SIZE is null)))
297 AND ((recinfo.COLUMN_TYPE = X_COLUMN_TYPE)
298 OR ((recinfo.COLUMN_TYPE is null) AND (X_COLUMN_TYPE is null)))
299
300 ) then
301 null;
302 else
303 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
304 app_exception.raise_exception;
305 end if;
306
307 for tlinfo in c1 loop
308 if (tlinfo.BASELANG = 'Y') then
309 if ( ((tlinfo.SOURCE_COLUMN_MEANING = X_SOURCE_COLUMN_MEANING)
310 OR ((tlinfo.SOURCE_COLUMN_MEANING is null) AND (X_SOURCE_COLUMN_MEANING is null)))
311 ) then
312 null;
313 else
314 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
315 app_exception.raise_exception;
316 end if;
317 end if;
318 end loop;
319 return;
320 end LOCK_ROW;
321
322 procedure UPDATE_ROW (
323 X_LIST_SOURCE_FIELD_ID in NUMBER,
324 X_FIELD_LOOKUP_TYPE in VARCHAR2,
325 X_FIELD_LOOKUP_TYPE_VIEW_NAME in VARCHAR2,
326 X_ALLOW_LABEL_OVERRIDE in VARCHAR2,
327 X_FIELD_USAGE_TYPE in VARCHAR2,
328 X_DIALOG_ENABLED in VARCHAR2,
329 X_ANALYTICS_FLAG in VARCHAR2,
330 X_AUTO_BINNING_FLAG in VARCHAR2,
331 X_NO_OF_BUCKETS in NUMBER,
332 X_ATTB_LOV_ID in NUMBER,
333 X_TCA_COLUMN_ID in NUMBER,
334 X_USED_IN_LIST_ENTRIES in VARCHAR2,
335 X_CHART_ENABLED_FLAG in VARCHAR2,
336 X_DEFAULT_CHART_TYPE in VARCHAR2,
337 X_LOV_DEFINED_FLAG in VARCHAR2,
338 X_USE_FOR_SPLITTING_FLAG in VARCHAR2,
339 X_DEFAULT_UI_CONTROL in VARCHAR2,
340 X_OBJECT_VERSION_NUMBER in NUMBER,
341 X_DE_LIST_SOURCE_TYPE_CODE in VARCHAR2,
342 X_LIST_SOURCE_TYPE_ID in NUMBER,
343 X_FIELD_TABLE_NAME in VARCHAR2,
344 X_FIELD_COLUMN_NAME in VARCHAR2,
345 X_SOURCE_COLUMN_NAME in VARCHAR2,
346 X_ENABLED_FLAG in VARCHAR2,
347 X_START_POSITION in NUMBER,
348 X_END_POSITION in NUMBER,
349 X_SECURITY_GROUP_ID in NUMBER,
350 X_FIELD_DATA_TYPE in VARCHAR2,
351 X_FIELD_DATA_SIZE in NUMBER,
352 X_SOURCE_COLUMN_MEANING in VARCHAR2,
353 X_LAST_UPDATE_DATE in DATE,
354 X_LAST_UPDATED_BY in NUMBER,
355 X_LAST_UPDATE_LOGIN in NUMBER,
356 X_COLUMN_TYPE IN VARCHAR2
357 ) is
358 begin
359 update AMS_LIST_SRC_FIELDS set
360 FIELD_LOOKUP_TYPE = X_FIELD_LOOKUP_TYPE,
361 FIELD_LOOKUP_TYPE_VIEW_NAME = X_FIELD_LOOKUP_TYPE_VIEW_NAME,
362 ALLOW_LABEL_OVERRIDE = X_ALLOW_LABEL_OVERRIDE,
363 FIELD_USAGE_TYPE = X_FIELD_USAGE_TYPE,
364 DIALOG_ENABLED = X_DIALOG_ENABLED,
365 ANALYTICS_FLAG = X_ANALYTICS_FLAG,
366 AUTO_BINNING_FLAG = X_AUTO_BINNING_FLAG,
367 NO_OF_BUCKETS = X_NO_OF_BUCKETS,
368 ATTB_LOV_ID = X_ATTB_LOV_ID,
369 TCA_COLUMN_ID = X_TCA_COLUMN_ID,
370 USED_IN_LIST_ENTRIES = X_USED_IN_LIST_ENTRIES,
371 CHART_ENABLED_FLAG = X_CHART_ENABLED_FLAG,
372 DEFAULT_CHART_TYPE = X_DEFAULT_CHART_TYPE,
373 LOV_DEFINED_FLAG = X_LOV_DEFINED_FLAG,
374 USE_FOR_SPLITTING_FLAG = X_USE_FOR_SPLITTING_FLAG,
375 DEFAULT_UI_CONTROL = X_DEFAULT_UI_CONTROL,
376 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
377 DE_LIST_SOURCE_TYPE_CODE = X_DE_LIST_SOURCE_TYPE_CODE,
378 LIST_SOURCE_TYPE_ID = X_LIST_SOURCE_TYPE_ID,
379 FIELD_TABLE_NAME = X_FIELD_TABLE_NAME,
380 FIELD_COLUMN_NAME = X_FIELD_COLUMN_NAME,
381 SOURCE_COLUMN_NAME = X_SOURCE_COLUMN_NAME,
382 ENABLED_FLAG = X_ENABLED_FLAG,
383 START_POSITION = X_START_POSITION,
384 END_POSITION = X_END_POSITION,
385 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
386 FIELD_DATA_TYPE = X_FIELD_DATA_TYPE,
387 FIELD_DATA_SIZE = X_FIELD_DATA_SIZE,
388 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
389 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
390 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
391 COLUMN_TYPE =X_COLUMN_TYPE
392 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID;
393
394 if (sql%notfound) then
395 raise no_data_found;
396 end if;
397
398 update AMS_LIST_SRC_FIELDS_TL set
399 SOURCE_COLUMN_MEANING = X_SOURCE_COLUMN_MEANING,
400 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
401 LAST_UPDATE_BY = X_LAST_UPDATED_BY,
402 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
403 SOURCE_LANG = userenv('LANG')
404 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID
405 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
406
407 if (sql%notfound) then
408 raise no_data_found;
409 end if;
410 end UPDATE_ROW;
411
412 procedure DELETE_ROW (
413 X_LIST_SOURCE_FIELD_ID in NUMBER
414 ) is
415 begin
416 delete from AMS_LIST_SRC_FIELDS_TL
417 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID;
418
419 if (sql%notfound) then
420 raise no_data_found;
421 end if;
422
423 delete from AMS_LIST_SRC_FIELDS
424 where LIST_SOURCE_FIELD_ID = X_LIST_SOURCE_FIELD_ID;
425
426 if (sql%notfound) then
427 raise no_data_found;
428 end if;
429 end DELETE_ROW;
430
431 procedure ADD_LANGUAGE
432 is
433 begin
434 delete from AMS_LIST_SRC_FIELDS_TL T
435 where not exists
436 (select NULL
437 from AMS_LIST_SRC_FIELDS B
438 where B.LIST_SOURCE_FIELD_ID = T.LIST_SOURCE_FIELD_ID
439 );
440
441 update AMS_LIST_SRC_FIELDS_TL T set (
442 SOURCE_COLUMN_MEANING
443 ) = (select
444 B.SOURCE_COLUMN_MEANING
445 from AMS_LIST_SRC_FIELDS_TL B
446 where B.LIST_SOURCE_FIELD_ID = T.LIST_SOURCE_FIELD_ID
447 and B.LANGUAGE = T.SOURCE_LANG)
448 where (
449 T.LIST_SOURCE_FIELD_ID,
450 T.LANGUAGE
451 ) in (select
452 SUBT.LIST_SOURCE_FIELD_ID,
453 SUBT.LANGUAGE
454 from AMS_LIST_SRC_FIELDS_TL SUBB, AMS_LIST_SRC_FIELDS_TL SUBT
455 where SUBB.LIST_SOURCE_FIELD_ID = SUBT.LIST_SOURCE_FIELD_ID
456 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
457 and (SUBB.SOURCE_COLUMN_MEANING <> SUBT.SOURCE_COLUMN_MEANING
458 or (SUBB.SOURCE_COLUMN_MEANING is null and SUBT.SOURCE_COLUMN_MEANING is not null)
459 or (SUBB.SOURCE_COLUMN_MEANING is not null and SUBT.SOURCE_COLUMN_MEANING is null)
460 ));
461
462 insert into AMS_LIST_SRC_FIELDS_TL (
463 LIST_SOURCE_FIELD_ID,
464 LAST_UPDATE_DATE,
465 LAST_UPDATE_BY,
466 CREATION_DATE,
467 CREATED_BY,
468 LAST_UPDATE_LOGIN,
469 SOURCE_COLUMN_MEANING,
470 SECURITY_GROUP_ID,
471 LANGUAGE,
472 SOURCE_LANG
473 ) select /*+ ORDERED */
474 B.LIST_SOURCE_FIELD_ID,
475 B.LAST_UPDATE_DATE,
476 B.LAST_UPDATE_BY,
477 B.CREATION_DATE,
478 B.CREATED_BY,
479 B.LAST_UPDATE_LOGIN,
480 B.SOURCE_COLUMN_MEANING,
481 B.SECURITY_GROUP_ID,
482 L.LANGUAGE_CODE,
483 B.SOURCE_LANG
484 from AMS_LIST_SRC_FIELDS_TL B, FND_LANGUAGES L
485 where L.INSTALLED_FLAG in ('I', 'B')
486 and B.LANGUAGE = userenv('LANG')
487 and not exists
488 (select NULL
489 from AMS_LIST_SRC_FIELDS_TL T
490 where T.LIST_SOURCE_FIELD_ID = B.LIST_SOURCE_FIELD_ID
491 and T.LANGUAGE = L.LANGUAGE_CODE);
492 end ADD_LANGUAGE;
493
494 end AMS_LIST_SRC_FIELDS_NEW_PKG;
[Home] [Help]