[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_SRC_FIELDS_PKG
Source
1 PACKAGE BODY AMS_LIST_SRC_FIELDS_PKG as
2 /* $Header: amstlsfb.pls 120.3 2006/06/07 08:40:33 bmuthukr noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_LIST_SRC_FIELDS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_LIST_SRC_FIELDS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstlsfb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
38 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
39 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
40
41 PROCEDURE Insert_Row(
42 px_list_source_field_id IN OUT NOCOPY NUMBER,
43 p_last_update_date DATE,
44 p_last_updated_by NUMBER,
45 p_creation_date DATE,
46 p_created_by NUMBER,
47 p_last_update_login NUMBER,
48 px_object_version_number IN OUT NOCOPY NUMBER,
49 p_de_list_source_type_code VARCHAR2,
50 p_list_source_type_id NUMBER,
51 p_field_table_name VARCHAR2,
52 p_field_column_name VARCHAR2,
53 p_source_column_name VARCHAR2,
54 p_source_column_meaning VARCHAR2,
55 p_enabled_flag VARCHAR2,
56 p_start_position NUMBER,
57 p_end_position NUMBER,
58 p_FIELD_DATA_TYPE VARCHAR2,
59 p_FIELD_DATA_SIZE NUMBER ,
60 p_DEFAULT_UI_CONTROL VARCHAR2,
61 p_FIELD_LOOKUP_TYPE VARCHAR2,
62 p_FIELD_LOOKUP_TYPE_VIEW_NAME VARCHAR2,
63 p_ALLOW_LABEL_OVERRIDE VARCHAR2 ,
64 p_FIELD_USAGE_TYPE VARCHAR2,
65 p_dialog_enabled VARCHAR2,
66 p_analytics_flag VARCHAR2,
67 p_auto_binning_flag VARCHAR2,
68 p_no_of_buckets NUMBER,
69 p_attb_lov_id number,
70 p_lov_defined_flag varchar2,
71 p_column_type varchar2
72 )
73 IS
74 x_rowid VARCHAR2(30);
75
76
77 BEGIN
78
79
80 px_object_version_number := 1;
81
82
83 INSERT INTO AMS_LIST_SRC_FIELDS(
84 list_source_field_id,
85 last_update_date,
86 last_updated_by,
87 creation_date,
88 created_by,
89 last_update_login,
90 object_version_number,
91 de_list_source_type_code,
92 list_source_type_id,
93 field_table_name,
94 field_column_name,
95 source_column_name,
96 enabled_flag,
97 start_position,
98 end_position,
99 FIELD_DATA_TYPE ,
100 FIELD_DATA_SIZE ,
101 DEFAULT_UI_CONTROL ,
102 FIELD_LOOKUP_TYPE ,
103 FIELD_LOOKUP_TYPE_VIEW_NAME ,
104 ALLOW_LABEL_OVERRIDE ,
105 FIELD_USAGE_TYPE ,
106 dialog_enabled ,
107 analytics_flag ,
108 auto_binning_flag ,
109 no_of_buckets ,
110 attb_lov_id ,
111 lov_defined_flag ,
112 column_type
113 ) VALUES (
114 DECODE( px_list_source_field_id, FND_API.g_miss_num, NULL, px_list_source_field_id),
115 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
116 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
117 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
118 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
119 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
120 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
121 DECODE( p_de_list_source_type_code, FND_API.g_miss_char, NULL, p_de_list_source_type_code),
122 DECODE( p_list_source_type_id, FND_API.g_miss_num, NULL, p_list_source_type_id),
123 DECODE( p_field_table_name, FND_API.g_miss_char, NULL, p_field_table_name),
124 DECODE( p_field_column_name, FND_API.g_miss_char, NULL, p_field_column_name),
125 DECODE( p_source_column_name, FND_API.g_miss_char, NULL, p_source_column_name),
126 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag),
127 DECODE( p_start_position, FND_API.g_miss_num, NULL, p_start_position),
128 DECODE( p_end_position, FND_API.g_miss_num, NULL, p_end_position),
129 decode( p_FIELD_DATA_TYPE ,FND_API.g_miss_char,null,p_field_data_type),
130 decode( p_FIELD_DATA_SIZE ,FND_API.g_miss_num,null,p_field_data_size),
131 decode( p_DEFAULT_UI_CONTROL ,FND_API.g_miss_char,null,p_DEFAULT_UI_CONTROL ),
132 decode( p_FIELD_LOOKUP_TYPE,FND_API.g_miss_char,null,p_FIELD_LOOKUP_TYPE),
133 decode( p_FIELD_LOOKUP_TYPE_VIEW_NAME,FND_API.g_miss_char,null,p_FIELD_LOOKUP_TYPE_VIEW_NAME),
134 decode( p_ALLOW_LABEL_OVERRIDE ,FND_API.g_miss_char,null,p_ALLOW_LABEL_OVERRIDE ),
135 decode( p_FIELD_USAGE_TYPE,FND_API.g_miss_char,null,p_FIELD_USAGE_TYPE),
136 decode( p_dialog_enabled,FND_API.g_miss_char,null,p_dialog_enabled),
137 decode( p_analytics_flag,FND_API.g_miss_char,null,p_analytics_flag),
138 decode( p_auto_binning_flag,FND_API.g_miss_char,null,p_auto_binning_flag),
139 decode( p_no_of_buckets,FND_API.g_miss_char,null,p_no_of_buckets),
140 decode( p_attb_lov_id,FND_API.g_miss_num,null,p_attb_lov_id),
141 decode( p_lov_defined_flag,FND_API.g_miss_char,null,p_lov_defined_flag),
142 decode( p_column_type,FND_API.g_miss_char,null,p_column_type)
143 );
144
145 insert into AMS_LIST_SRC_FIELDS_TL (
146 LANGUAGE,
147 SOURCE_LANG,
148 source_column_meaning,
149 LIST_SOURCE_field_ID,
150 LAST_UPDATE_DATE,
151 LAST_UPDATE_BY,
152 CREATION_DATE,
153 CREATED_BY,
154 LAST_UPDATE_LOGIN
155 ) select
156 l.language_code,
157 userenv('LANG'),
158 DECODE( p_source_column_meaning, FND_API.g_miss_char, NULL, p_source_column_meaning),
159 DECODE( px_list_source_field_id, FND_API.g_miss_num, NULL, px_list_source_field_id),
160 --Modified for bug 5237401. bmuthukr
161 /*
162 sysdate,
163 FND_GLOBAL.user_id,
164 sysdate,
165 FND_GLOBAL.user_id,
166 */
167 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
168 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
169 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
170 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
171 --
172 FND_GLOBAL.conc_login_id
173 from FND_LANGUAGES L
174 where L.INSTALLED_FLAG in ('I', 'B')
175 and not exists
176 (select NULL
177 from AMS_LIST_SRC_FIELDS_TL T
178 where T.LIST_source_field_ID = px_LIST_SOURCE_field_ID
179 and T.LANGUAGE = L.LANGUAGE_CODE);
180
181 END Insert_Row;
182
183
184
185 ----------------------------------------------------------
186 ---- MEDIA ----
187 ----------------------------------------------------------
188
189 -- ========================================================
190 --
191 -- NAME
192 -- createUpdateBody
193 --
194 -- PURPOSE
195 --
196 -- NOTES
197 --
198 -- HISTORY
199 --
200 -- ========================================================
201 PROCEDURE Update_Row(
202 p_list_source_field_id NUMBER,
203 p_last_update_date DATE,
204 p_last_updated_by NUMBER,
205 p_creation_date DATE,
206 p_created_by NUMBER,
207 p_last_update_login NUMBER,
208 p_object_version_number NUMBER,
209 p_de_list_source_type_code VARCHAR2,
210 p_list_source_type_id NUMBER,
211 p_field_table_name VARCHAR2,
212 p_field_column_name VARCHAR2,
213 p_source_column_name VARCHAR2,
214 p_source_column_meaning VARCHAR2,
215 p_enabled_flag VARCHAR2,
216 p_start_position NUMBER,
217 p_end_position NUMBER,
218 p_FIELD_DATA_TYPE VARCHAR2,
219 p_FIELD_DATA_SIZE NUMBER ,
220 p_DEFAULT_UI_CONTROL VARCHAR2,
221 p_FIELD_LOOKUP_TYPE VARCHAR2,
222 p_FIELD_LOOKUP_TYPE_VIEW_NAME VARCHAR2,
223 p_ALLOW_LABEL_OVERRIDE VARCHAR2 ,
224 p_FIELD_USAGE_TYPE VARCHAR2,
225 p_dialog_enabled VARCHAR2,
226 p_analytics_flag VARCHAR2,
227 p_auto_binning_flag VARCHAR2,
228 p_no_of_buckets NUMBER,
229 p_attb_lov_id number,
230 p_lov_defined_flag varchar2,
231 p_column_type varchar2
232 )
233
234 IS
235 BEGIN
236 Update AMS_LIST_SRC_FIELDS
237 SET
238 list_source_field_id = DECODE( p_list_source_field_id, FND_API.g_miss_num, list_source_field_id, p_list_source_field_id),
239 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
240 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
241 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
242 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
243 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
244 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, 1, p_object_version_number+1 ),
245 de_list_source_type_code = DECODE( p_de_list_source_type_code, FND_API.g_miss_char, de_list_source_type_code, p_de_list_source_type_code),
246 list_source_type_id = DECODE( p_list_source_type_id, FND_API.g_miss_num, list_source_type_id, p_list_source_type_id),
247 field_table_name = DECODE( p_field_table_name, FND_API.g_miss_char, field_table_name, p_field_table_name),
248 field_column_name = DECODE( p_field_column_name, FND_API.g_miss_char, field_column_name, p_field_column_name),
249 source_column_name = DECODE( p_source_column_name, FND_API.g_miss_char, source_column_name, p_source_column_name),
250 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
251 start_position = DECODE( p_start_position, FND_API.g_miss_num, start_position, p_start_position),
252 end_position = DECODE( p_end_position, FND_API.g_miss_num, end_position, p_end_position),
253 FIELD_DATA_TYPE = decode( p_FIELD_DATA_TYPE ,FND_API.g_miss_char,FIELD_DATA_TYPE ,p_field_data_type),
254 FIELD_DATA_SIZE = decode( p_FIELD_DATA_SIZE ,FND_API.g_miss_num,FIELD_DATA_SIZE,p_field_data_size),
255 DEFAULT_UI_CONTROL= decode( p_DEFAULT_UI_CONTROL ,FND_API.g_miss_char,DEFAULT_UI_CONTROL,p_DEFAULT_UI_CONTROL ),
256 FIELD_LOOKUP_TYPE = decode( p_FIELD_LOOKUP_TYPE,FND_API.g_miss_char,FIELD_LOOKUP_TYPE,p_FIELD_LOOKUP_TYPE),
257 FIELD_LOOKUP_TYPE_VIEW_NAME = decode( p_FIELD_LOOKUP_TYPE_VIEW_NAME,FND_API.g_miss_char,FIELD_LOOKUP_TYPE_VIEW_NAME,p_FIELD_LOOKUP_TYPE_VIEW_NAME),
258 ALLOW_LABEL_OVERRIDE= decode( p_ALLOW_LABEL_OVERRIDE ,FND_API.g_miss_char,ALLOW_LABEL_OVERRIDE ,p_ALLOW_LABEL_OVERRIDE ),
259 FIELD_USAGE_TYPE= decode( p_FIELD_USAGE_TYPE,FND_API.g_miss_char,FIELD_USAGE_TYPE,p_FIELD_USAGE_TYPE),
260 dialog_enabled= decode( p_dialog_enabled,FND_API.g_miss_char,dialog_enabled,p_dialog_enabled),
261 analytics_flag = decode( p_analytics_flag,FND_API.g_miss_char,analytics_flag,p_analytics_flag),
262 auto_binning_flag = decode( p_auto_binning_flag,FND_API.g_miss_char,auto_binning_flag,p_auto_binning_flag),
263 no_of_buckets = decode( p_no_of_buckets,FND_API.g_miss_num,no_of_buckets,p_no_of_buckets),
264 attb_lov_id = decode( p_attb_lov_id,FND_API.g_miss_num,attb_lov_id,p_attb_lov_id),
265 lov_defined_flag = decode( p_lov_defined_flag,FND_API.g_miss_char,lov_defined_flag,p_lov_defined_flag),
266 column_type = decode( p_column_type,FND_API.g_miss_char,column_type,p_column_type)
267
268 WHERE LIST_SOURCE_FIELD_ID = p_LIST_SOURCE_FIELD_ID
269 AND object_version_number = p_object_version_number;
270
271 IF (SQL%NOTFOUND) THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274
275 update AMS_LIST_SRC_FIELDS_TL set
276 SOURCE_column_meaning = DECODE( p_source_column_meaning, FND_API.g_miss_char, source_column_meaning, p_source_column_meaning),
277 LAST_UPDATE_DATE = sysdate,
278 -- Modified for bug 5237401. bmuthukr
279 --LAST_UPDATE_BY = FND_GLOBAL.user_id,
280 last_update_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_update_by, p_last_updated_by),
281 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
282 SOURCE_LANG = userenv('LANG')
283 where list_source_field_id = p_list_source_field_id
284 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
285
286 IF (SQL%NOTFOUND) THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289
290
291 END Update_Row;
292
293
294 ----------------------------------------------------------
295 ---- MEDIA ----
296 ----------------------------------------------------------
297
298 -- ========================================================
299 --
300 -- NAME
301 -- createDeleteBody
302 --
303 -- PURPOSE
304 --
305 -- NOTES
306 --
307 -- HISTORY
308 --
309 -- ========================================================
310 PROCEDURE Delete_Row(
314 DELETE FROM AMS_LIST_SRC_FIELDS
311 p_LIST_SOURCE_FIELD_ID NUMBER)
312 IS
313 BEGIN
315 WHERE LIST_SOURCE_FIELD_ID = p_LIST_SOURCE_FIELD_ID;
316 If (SQL%NOTFOUND) then
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 End If;
319
320 DELETE FROM AMS_LIST_SRC_FIELDS_TL
321 WHERE LIST_SOURCE_FIELD_ID = p_LIST_SOURCE_FIELD_ID;
322 If (SQL%NOTFOUND) then
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 End If;
325 END Delete_Row ;
326
327
328
329 ----------------------------------------------------------
330 ---- MEDIA ----
331 ----------------------------------------------------------
332
333 -- ========================================================
334 --
335 -- NAME
336 -- createLockBody
337 --
338 -- PURPOSE
339 --
340 -- NOTES
341 --
342 -- HISTORY
343 --
344 -- ========================================================
345 PROCEDURE Lock_Row(
346 p_list_source_field_id NUMBER,
347 p_last_update_date DATE,
348 p_last_updated_by NUMBER,
349 p_creation_date DATE,
350 p_created_by NUMBER,
351 p_last_update_login NUMBER,
352 p_object_version_number NUMBER,
353 p_de_list_source_type_code VARCHAR2,
354 p_list_source_type_id NUMBER,
355 p_field_table_name VARCHAR2,
356 p_field_column_name VARCHAR2,
357 p_source_column_name VARCHAR2,
358 p_source_column_meaning VARCHAR2,
359 p_enabled_flag VARCHAR2,
360 p_start_position NUMBER,
361 p_end_position NUMBER,
362 p_FIELD_DATA_TYPE VARCHAR2,
363 p_FIELD_DATA_SIZE NUMBER ,
364 p_DEFAULT_UI_CONTROL VARCHAR2,
365 p_FIELD_LOOKUP_TYPE VARCHAR2,
366 p_FIELD_LOOKUP_TYPE_VIEW_NAME VARCHAR2,
367 p_ALLOW_LABEL_OVERRIDE VARCHAR2,
368 p_FIELD_USAGE_TYPE VARCHAR2,
369 p_dialog_enabled VARCHAR2,
370 p_analytics_flag VARCHAR2,
371 p_auto_binning_flag VARCHAR2,
372 p_no_of_buckets NUMBER,
373 p_attb_lov_id number,
374 p_lov_defined_flag varchar2,
375 p_column_type varchar2
376 )
377
378 IS
379 CURSOR C IS
380 SELECT *
381 FROM AMS_LIST_SRC_FIELDS
382 WHERE LIST_SOURCE_FIELD_ID = p_LIST_SOURCE_FIELD_ID
383 FOR UPDATE of LIST_SOURCE_FIELD_ID NOWAIT;
384 Recinfo C%ROWTYPE;
385 BEGIN
386 OPEN c;
387 FETCH c INTO Recinfo;
388 If (c%NOTFOUND) then
389 CLOSE c;
390 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
391 APP_EXCEPTION.RAISE_EXCEPTION;
392 END IF;
393 CLOSE C;
394 END Lock_Row;
395
396 PROCEDURE Insert_Row(
397 px_list_source_field_id IN OUT NOCOPY NUMBER,
398 p_last_update_date DATE,
399 p_last_updated_by NUMBER,
400 p_creation_date DATE,
401 p_created_by NUMBER,
402 p_last_update_login NUMBER,
403 px_object_version_number IN OUT NOCOPY NUMBER,
404 p_de_list_source_type_code VARCHAR2,
405 p_list_source_type_id NUMBER,
406 p_field_table_name VARCHAR2,
407 p_field_column_name VARCHAR2,
408 p_source_column_name VARCHAR2,
409 p_source_column_meaning VARCHAR2,
410 p_enabled_flag VARCHAR2,
411 p_start_position NUMBER,
412 p_end_position NUMBER,
413 p_FIELD_DATA_TYPE VARCHAR2,
414 p_FIELD_DATA_SIZE NUMBER ,
415 p_DEFAULT_UI_CONTROL VARCHAR2,
416 p_FIELD_LOOKUP_TYPE VARCHAR2,
417 p_FIELD_LOOKUP_TYPE_VIEW_NAME VARCHAR2,
418 p_ALLOW_LABEL_OVERRIDE VARCHAR2 ,
419 p_FIELD_USAGE_TYPE VARCHAR2,
420 p_dialog_enabled VARCHAR2,
421 p_analytics_flag VARCHAR2,
422 p_auto_binning_flag VARCHAR2,
423 p_no_of_buckets NUMBER,
424 p_attb_lov_id number,
425 p_lov_defined_flag varchar2,
426 p_USED_IN_LIST_ENTRIES VARCHAR2,
427 p_CHART_ENABLED_FLAG VARCHAR2,
428 p_DEFAULT_CHART_TYPE VARCHAR2,
429 p_USE_FOR_SPLITTING_FLAG VARCHAR2,
430 p_column_type varchar2
431 )
432 IS
433 x_rowid VARCHAR2(30);
434
435
436 BEGIN
437
438
439 px_object_version_number := 1;
440
441
442 INSERT INTO AMS_LIST_SRC_FIELDS(
443 list_source_field_id,
444 last_update_date,
445 last_updated_by,
446 creation_date,
447 created_by,
448 last_update_login,
449 object_version_number,
450 de_list_source_type_code,
451 list_source_type_id,
452 field_table_name,
453 field_column_name,
454 source_column_name,
455 enabled_flag,
456 start_position,
457 end_position,
458 FIELD_DATA_TYPE ,
459 FIELD_DATA_SIZE ,
460 DEFAULT_UI_CONTROL ,
461 FIELD_LOOKUP_TYPE ,
462 FIELD_LOOKUP_TYPE_VIEW_NAME ,
463 ALLOW_LABEL_OVERRIDE ,
467 auto_binning_flag ,
464 FIELD_USAGE_TYPE ,
465 dialog_enabled ,
466 analytics_flag ,
468 no_of_buckets ,
469 attb_lov_id ,
470 lov_defined_flag ,
471 USED_IN_LIST_ENTRIES ,
472 CHART_ENABLED_FLAG ,
473 DEFAULT_CHART_TYPE ,
474 USE_FOR_SPLITTING_FLAG ,
475 column_type
476 ) VALUES (
477 DECODE( px_list_source_field_id, FND_API.g_miss_num, NULL, px_list_source_field_id),
478 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
479 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
480 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
481 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
482 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
483 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
484 DECODE( p_de_list_source_type_code, FND_API.g_miss_char, NULL, p_de_list_source_type_code),
485 DECODE( p_list_source_type_id, FND_API.g_miss_num, NULL, p_list_source_type_id),
486 DECODE( p_field_table_name, FND_API.g_miss_char, NULL, p_field_table_name),
487 DECODE( p_field_column_name, FND_API.g_miss_char, NULL, p_field_column_name),
488 DECODE( p_source_column_name, FND_API.g_miss_char, NULL, p_source_column_name),
489 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag),
490 DECODE( p_start_position, FND_API.g_miss_num, NULL, p_start_position),
491 DECODE( p_end_position, FND_API.g_miss_num, NULL, p_end_position),
492 decode( p_FIELD_DATA_TYPE ,FND_API.g_miss_char,null,p_field_data_type),
493 decode( p_FIELD_DATA_SIZE ,FND_API.g_miss_num,null,p_field_data_size),
494 decode( p_DEFAULT_UI_CONTROL ,FND_API.g_miss_char,null,p_DEFAULT_UI_CONTROL ),
495 decode( p_FIELD_LOOKUP_TYPE,FND_API.g_miss_char,null,p_FIELD_LOOKUP_TYPE),
496 decode( p_FIELD_LOOKUP_TYPE_VIEW_NAME,FND_API.g_miss_char,null,p_FIELD_LOOKUP_TYPE_VIEW_NAME),
497 decode( p_ALLOW_LABEL_OVERRIDE ,FND_API.g_miss_char,null,p_ALLOW_LABEL_OVERRIDE ),
498 decode( p_FIELD_USAGE_TYPE,FND_API.g_miss_char,null,p_FIELD_USAGE_TYPE),
499 decode( p_dialog_enabled,FND_API.g_miss_char,null,p_dialog_enabled),
500 decode( p_analytics_flag,FND_API.g_miss_char,null,p_analytics_flag),
501 decode( p_auto_binning_flag,FND_API.g_miss_char,null,p_auto_binning_flag),
502 decode( p_no_of_buckets,FND_API.g_miss_char,null,p_no_of_buckets),
503 decode( p_attb_lov_id,FND_API.g_miss_num,null,p_attb_lov_id),
504 decode( p_lov_defined_flag,FND_API.g_miss_char,null,p_lov_defined_flag),
505 decode( p_used_in_list_entries,FND_API.g_miss_char,null,p_used_in_list_entries),
506 decode( p_chart_enabled_flag,FND_API.g_miss_char,null,p_chart_enabled_flag),
507 decode( p_default_chart_type,FND_API.g_miss_char,null,p_default_chart_type),
508 decode( p_use_for_splitting_flag,FND_API.g_miss_char,null,p_use_for_splitting_flag),
509 decode( p_column_type,FND_API.g_miss_char,null,p_column_type)
510 );
511
512 insert into AMS_LIST_SRC_FIELDS_TL (
513 LANGUAGE,
514 SOURCE_LANG,
515 source_column_meaning,
516 LIST_SOURCE_field_ID,
517 LAST_UPDATE_DATE,
518 LAST_UPDATE_BY,
519 CREATION_DATE,
520 CREATED_BY,
521 LAST_UPDATE_LOGIN
522 ) select
523 l.language_code,
524 userenv('LANG'),
525 DECODE( p_source_column_meaning, FND_API.g_miss_char, NULL, p_source_column_meaning),
526 DECODE( px_list_source_field_id, FND_API.g_miss_num, NULL, px_list_source_field_id),
527 --Added for bug 5237401. bmuthukr
528 /*
529 sysdate,
530 FND_GLOBAL.user_id,
531 sysdate,
532 FND_GLOBAL.user_id,
533 */
534 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
535 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
536 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
537 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
538 --
539 FND_GLOBAL.conc_login_id
540 from FND_LANGUAGES L
541 where L.INSTALLED_FLAG in ('I', 'B')
542 and not exists
543 (select NULL
544 from AMS_LIST_SRC_FIELDS_TL T
545 where T.LIST_source_field_ID = px_LIST_SOURCE_field_ID
546 and T.LANGUAGE = L.LANGUAGE_CODE);
547
548 END Insert_Row;
549
550
551
552 ----------------------------------------------------------
553 ---- MEDIA ----
554 ----------------------------------------------------------
555
556 -- ========================================================
557 --
558 -- NAME
559 -- createUpdateBody
560 --
561 -- PURPOSE
562 --
563 -- NOTES
564 --
565 -- HISTORY
566 --
567 -- ========================================================
568 PROCEDURE Update_Row(
569 p_list_source_field_id NUMBER,
570 p_last_update_date DATE,
571 p_last_updated_by NUMBER,
572 p_creation_date DATE,
573 p_created_by NUMBER,
574 p_last_update_login NUMBER,
575 p_object_version_number NUMBER,
576 p_de_list_source_type_code VARCHAR2,
577 p_list_source_type_id NUMBER,
578 p_field_table_name VARCHAR2,
579 p_field_column_name VARCHAR2,
580 p_source_column_name VARCHAR2,
581 p_source_column_meaning VARCHAR2,
582 p_enabled_flag VARCHAR2,
583 p_start_position NUMBER,
584 p_end_position NUMBER,
585 p_FIELD_DATA_TYPE VARCHAR2,
589 p_FIELD_LOOKUP_TYPE_VIEW_NAME VARCHAR2,
586 p_FIELD_DATA_SIZE NUMBER ,
587 p_DEFAULT_UI_CONTROL VARCHAR2,
588 p_FIELD_LOOKUP_TYPE VARCHAR2,
590 p_ALLOW_LABEL_OVERRIDE VARCHAR2 ,
591 p_FIELD_USAGE_TYPE VARCHAR2,
592 p_dialog_enabled VARCHAR2,
593 p_analytics_flag VARCHAR2,
594 p_auto_binning_flag VARCHAR2,
595 p_no_of_buckets NUMBER,
596 p_attb_lov_id number,
597 p_lov_defined_flag varchar2,
598 p_USED_IN_LIST_ENTRIES VARCHAR2,
599 p_CHART_ENABLED_FLAG VARCHAR2,
600 p_DEFAULT_CHART_TYPE VARCHAR2,
601 p_USE_FOR_SPLITTING_FLAG VARCHAR2,
602 p_column_type varchar2
603 )
604
605 IS
606 BEGIN
607 Update AMS_LIST_SRC_FIELDS
608 SET
609 list_source_field_id = DECODE( p_list_source_field_id, FND_API.g_miss_num, list_source_field_id, p_list_source_field_id),
610 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
611 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
612 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
613 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
614 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
615 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, 1, p_object_version_number+1 ),
616 de_list_source_type_code = DECODE( p_de_list_source_type_code, FND_API.g_miss_char, de_list_source_type_code, p_de_list_source_type_code),
617 list_source_type_id = DECODE( p_list_source_type_id, FND_API.g_miss_num, list_source_type_id, p_list_source_type_id),
618 field_table_name = DECODE( p_field_table_name, FND_API.g_miss_char, field_table_name, p_field_table_name),
619 field_column_name = DECODE( p_field_column_name, FND_API.g_miss_char, field_column_name, p_field_column_name),
620 source_column_name = DECODE( p_source_column_name, FND_API.g_miss_char, source_column_name, p_source_column_name),
621 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
622 start_position = DECODE( p_start_position, FND_API.g_miss_num, start_position, p_start_position),
623 end_position = DECODE( p_end_position, FND_API.g_miss_num, end_position, p_end_position),
624 FIELD_DATA_TYPE = decode( p_FIELD_DATA_TYPE ,FND_API.g_miss_char,FIELD_DATA_TYPE ,p_field_data_type),
625 FIELD_DATA_SIZE = decode( p_FIELD_DATA_SIZE ,FND_API.g_miss_num,FIELD_DATA_SIZE,p_field_data_size),
626 DEFAULT_UI_CONTROL= decode( p_DEFAULT_UI_CONTROL ,FND_API.g_miss_char,DEFAULT_UI_CONTROL,p_DEFAULT_UI_CONTROL ),
627 FIELD_LOOKUP_TYPE = decode( p_FIELD_LOOKUP_TYPE,FND_API.g_miss_char,FIELD_LOOKUP_TYPE,p_FIELD_LOOKUP_TYPE),
628 FIELD_LOOKUP_TYPE_VIEW_NAME = decode( p_FIELD_LOOKUP_TYPE_VIEW_NAME,FND_API.g_miss_char,FIELD_LOOKUP_TYPE_VIEW_NAME,p_FIELD_LOOKUP_TYPE_VIEW_NAME),
629 ALLOW_LABEL_OVERRIDE= decode( p_ALLOW_LABEL_OVERRIDE ,FND_API.g_miss_char,ALLOW_LABEL_OVERRIDE ,p_ALLOW_LABEL_OVERRIDE ),
630 FIELD_USAGE_TYPE= decode( p_FIELD_USAGE_TYPE,FND_API.g_miss_char,FIELD_USAGE_TYPE,p_FIELD_USAGE_TYPE),
631 dialog_enabled= decode( p_dialog_enabled,FND_API.g_miss_char,dialog_enabled,p_dialog_enabled),
632 analytics_flag = decode( p_analytics_flag,FND_API.g_miss_char,analytics_flag,p_analytics_flag),
633 auto_binning_flag = decode( p_auto_binning_flag,FND_API.g_miss_char,auto_binning_flag,p_auto_binning_flag),
634 no_of_buckets = decode( p_no_of_buckets,FND_API.g_miss_num,no_of_buckets,p_no_of_buckets),
635 attb_lov_id = decode( p_attb_lov_id,FND_API.g_miss_num,attb_lov_id,p_attb_lov_id),
636 lov_defined_flag = decode( p_lov_defined_flag,FND_API.g_miss_char,lov_defined_flag,p_lov_defined_flag),
637 used_in_list_entries = decode( p_used_in_list_entries,FND_API.g_miss_char,used_in_list_entries,p_used_in_list_entries),
638 chart_enabled_flag = decode( p_chart_enabled_flag,FND_API.g_miss_char,chart_enabled_flag,p_chart_enabled_flag),
639 default_chart_type = decode( p_default_chart_type,FND_API.g_miss_char,default_chart_type,p_default_chart_type),
640 use_for_splitting_flag = decode( p_use_for_splitting_flag,FND_API.g_miss_char,use_for_splitting_flag,p_use_for_splitting_flag),
641 column_type = decode( p_column_type,FND_API.g_miss_char,column_type,p_column_type)
642 WHERE LIST_SOURCE_FIELD_ID = p_LIST_SOURCE_FIELD_ID
643 AND object_version_number = p_object_version_number;
644
645 IF (SQL%NOTFOUND) THEN
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648
649 update AMS_LIST_SRC_FIELDS_TL set
650 SOURCE_column_meaning = DECODE( p_source_column_meaning, FND_API.g_miss_char, source_column_meaning, p_source_column_meaning),
651 LAST_UPDATE_DATE = sysdate,
652 -- Added for bug 5237401. bmuthukr.
653 --LAST_UPDATE_BY = FND_GLOBAL.user_id,
654 last_update_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_update_by, p_last_updated_by),
655 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
656 SOURCE_LANG = userenv('LANG')
657 where list_source_field_id = p_list_source_field_id
658 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
659
660 IF (SQL%NOTFOUND) THEN
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
662 END IF;
663
664
665 END Update_Row;
666
667 PROCEDURE load_row (
668 x_list_source_field_id IN NUMBER,
669 x_de_list_source_type_code IN VARCHAR2,
670 x_list_source_type_id IN NUMBER,
671 x_field_table_name IN VARCHAR2,
672 x_field_column_name IN VARCHAR2,
673 x_source_column_name IN VARCHAR2,
677 x_FIELD_DATA_TYPE VARCHAR2,
674 x_enabled_flag IN VARCHAR2,
675 x_start_position IN NUMBER,
676 x_end_position IN NUMBER,
678 x_FIELD_DATA_SIZE NUMBER ,
679 x_DEFAULT_UI_CONTROL VARCHAR2,
680 x_FIELD_LOOKUP_TYPE VARCHAR2,
681 x_FIELD_LOOKUP_TYPE_VIEW_NAME VARCHAR2,
682 x_ALLOW_LABEL_OVERRIDE VARCHAR2,
683 x_FIELD_USAGE_TYPE VARCHAR2,
684 x_dialog_enabled VARCHAR2,
685 x_source_column_meaning IN VARCHAR2,
686 x_owner IN VARCHAR2,
687 x_custom_mode IN VARCHAR2,
688 x_analytics_flag VARCHAR2,
689 x_auto_binning_flag VARCHAR2,
690 x_no_of_buckets NUMBER,
691 x_attb_lov_id number,
692 x_lov_defined_flag varchar2,
693 x_USED_IN_LIST_ENTRIES VARCHAR2,
694 x_CHART_ENABLED_FLAG VARCHAR2,
695 x_DEFAULT_CHART_TYPE VARCHAR2,
696 x_USE_FOR_SPLITTING_FLAG VARCHAR2,
697 x_column_type varchar2
698 )
699 IS
700 l_user_id number := 0;
701 l_obj_verno number;
702 l_dummy_char varchar2(1);
703 l_row_id varchar2(100);
704 l_list_source_field_id number;
705 l_last_updated_by number;
706
707 CURSOR c_obj_verno IS
708 SELECT object_version_number, last_updated_by
709 FROM ams_list_src_fields
710 WHERE list_source_field_id = x_list_source_field_id;
711
712 CURSOR c_chk_exists is
713 SELECT 'x'
714 FROM ams_list_src_fields
715 WHERE list_source_field_id = x_list_source_field_id;
716
717 CURSOR c_get_id is
718 SELECT ams_list_src_fields_s.NEXTVAL
719 FROM DUAL;
720 BEGIN
721 if X_OWNER = 'SEED' then
722 l_user_id := 1;
723 elsif X_OWNER = 'ORACLE' then
724 l_user_id := 2;
725 elsif X_OWNER = 'SYSADMIN' THEN
726 l_user_id := 0;
727 end if;
728
729 OPEN c_chk_exists;
730 FETCH c_chk_exists INTO l_dummy_char;
731 IF c_chk_exists%notfound THEN
732 CLOSE c_chk_exists;
733
734 IF x_list_source_field_id IS NULL THEN
735 OPEN c_get_id;
736 FETCH c_get_id INTO l_list_source_field_id;
737 CLOSE c_get_id;
738 ELSE
739 l_list_source_field_id := x_list_source_field_id;
740 END IF;
741
742 l_obj_verno := 1;
743
744 ams_list_src_fields_pkg.Insert_Row (
745 px_list_source_field_id => l_list_source_field_id,
746 p_last_update_date => SYSDATE,
747 p_last_updated_by => l_user_id,
748 p_creation_date => SYSDATE,
749 p_created_by => l_user_id,
750 p_last_update_login => 0,
751 px_object_version_number => l_obj_verno,
752 p_de_list_source_type_code => x_de_list_source_type_code,
753 p_list_source_type_id => x_list_source_type_id,
754 p_field_table_name => x_field_table_name,
755 p_field_column_name => x_field_column_name,
756 p_source_column_name => x_source_column_name,
757 p_source_column_meaning => x_source_column_meaning,
758 p_enabled_flag => x_enabled_flag,
759 p_start_position => x_start_position,
760 p_end_position => x_end_position,
761 p_FIELD_DATA_TYPE => x_FIELD_DATA_TYPE ,
762 p_FIELD_DATA_SIZE => x_FIELD_DATA_SIZE ,
763 p_DEFAULT_UI_CONTROL => x_DEFAULT_UI_CONTROL ,
764 p_FIELD_LOOKUP_TYPE => x_FIELD_LOOKUP_TYPE ,
765 p_FIELD_LOOKUP_TYPE_VIEW_NAME => x_FIELD_LOOKUP_TYPE_VIEW_NAME,
766 p_ALLOW_LABEL_OVERRIDE => x_ALLOW_LABEL_OVERRIDE ,
767 p_FIELD_USAGE_TYPE => x_FIELD_USAGE_TYPE ,
768 p_dialog_enabled => x_dialog_enabled,
769 p_analytics_flag => x_analytics_flag,
770 p_auto_binning_flag => x_auto_binning_flag,
771 p_no_of_buckets => x_no_of_buckets,
772 p_attb_lov_id => x_attb_lov_id ,
773 p_lov_defined_flag => x_lov_defined_flag ,
774 p_USED_IN_LIST_ENTRIES => x_USED_IN_LIST_ENTRIES ,
775 p_CHART_ENABLED_FLAG => x_CHART_ENABLED_FLAG ,
776 p_DEFAULT_CHART_TYPE => x_DEFAULT_CHART_TYPE ,
777 p_USE_FOR_SPLITTING_FLAG => x_USE_FOR_SPLITTING_FLAG,
778 p_column_type => x_column_type
779 );
780 ELSE
781 CLOSE c_chk_exists;
782 OPEN c_obj_verno;
783 FETCH c_obj_verno INTO l_obj_verno ,l_last_updated_by;
784 CLOSE c_obj_verno;
785
786 if (l_last_updated_by in (1,2,0) OR
787 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
788
789
790 ams_list_src_fields_pkg.Update_Row (
791 p_list_source_field_id => x_list_source_field_id,
792 p_last_update_date => SYSDATE,
793 p_last_updated_by => l_user_id,
794 p_last_update_login => 0,
795 p_creation_date => SYSDATE,
796 p_created_by => l_user_id,
797 p_object_version_number => l_obj_verno,
798 p_de_list_source_type_code => x_de_list_source_type_code,
799 p_list_source_type_id => x_list_source_type_id,
800 p_field_table_name => x_field_table_name,
801 p_field_column_name => x_field_column_name,
802 p_source_column_name => x_source_column_name,
803 p_enabled_flag => x_enabled_flag,
804 p_start_position => x_start_position,
805 p_end_position => x_end_position,
809 p_FIELD_LOOKUP_TYPE => x_FIELD_LOOKUP_TYPE ,
806 p_FIELD_DATA_TYPE => x_FIELD_DATA_TYPE ,
807 p_FIELD_DATA_SIZE => x_FIELD_DATA_SIZE ,
808 p_DEFAULT_UI_CONTROL => x_DEFAULT_UI_CONTROL ,
810 p_FIELD_LOOKUP_TYPE_VIEW_NAME => x_FIELD_LOOKUP_TYPE_VIEW_NAME,
811 p_ALLOW_LABEL_OVERRIDE => x_ALLOW_LABEL_OVERRIDE ,
812 p_FIELD_USAGE_TYPE => x_FIELD_USAGE_TYPE ,
813 p_dialog_enabled => x_dialog_enabled,
814 p_source_column_meaning => x_source_column_meaning,
815 p_analytics_flag => x_analytics_flag,
816 p_auto_binning_flag => x_auto_binning_flag,
817 p_no_of_buckets => x_no_of_buckets,
818 p_attb_lov_id => x_attb_lov_id ,
819 p_lov_defined_flag => x_lov_defined_flag ,
820 p_USED_IN_LIST_ENTRIES => x_USED_IN_LIST_ENTRIES ,
821 p_CHART_ENABLED_FLAG => x_CHART_ENABLED_FLAG ,
822 p_DEFAULT_CHART_TYPE => x_DEFAULT_CHART_TYPE ,
823 p_USE_FOR_SPLITTING_FLAG => x_USE_FOR_SPLITTING_FLAG,
824 p_column_type => x_column_type
825 );
826 end if;
827 END IF;
828 END load_row;
829
830 procedure TRANSLATE_ROW(
831 x_list_source_field_id IN NUMBER,
832 x_source_column_meaning IN VARCHAR2,
833 x_owner in VARCHAR2,
834 x_custom_mode in VARCHAR2
835 ) is
836
837 cursor c_last_updated_by is
838 select last_update_by
839 FROM AMS_LIST_SRC_FIELDS_TL
840 where list_source_field_id = x_list_source_field_id
841 and USERENV('LANG') = LANGUAGE;
842
843 l_last_updated_by number;
844
845
846 begin
847
848 open c_last_updated_by;
849 fetch c_last_updated_by into l_last_updated_by;
850 close c_last_updated_by;
851
852 if (l_last_updated_by in (1,2,0) OR
853 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
854
855 update AMS_LIST_SRC_FIELDS_TL set
856 source_column_meaning = nvl(x_source_column_meaning,
857 source_column_meaning),
858 source_lang = userenv('LANG'),
859 last_update_date = sysdate,
860 last_update_by = decode(x_owner, 'SEED', 1, 'ORACLE', 2, 'SYSADMIN', 0, -1),
861 last_update_login = 0
862 where list_source_field_id = x_list_source_field_id
863 and userenv('LANG') in (language, source_lang);
864
865 end if;
866 end TRANSLATE_ROW;
867
868 procedure ADD_LANGUAGE
869 is
870 begin
871 delete from AMS_LIST_SRC_FIELDS_TL T
872 where not exists
873 (select NULL
874 from AMS_LIST_SRC_FIELDS B
875 where B.LIST_SOURCE_FIELD_ID = T.LIST_SOURCE_FIELD_ID
876 );
877
878 update AMS_LIST_SRC_FIELDS_TL T set (
879 SOURCE_COLUMN_MEANING
880 ) = (select
881 B.SOURCE_COLUMN_MEANING
882 from AMS_LIST_SRC_FIELDS_TL B
883 where B.LIST_SOURCE_FIELD_ID = T.LIST_SOURCE_FIELD_ID
884 and B.LANGUAGE = T.SOURCE_LANG)
885 where (
886 T.LIST_SOURCE_FIELD_ID,
887 T.LANGUAGE
888 ) in (select
889 SUBT.LIST_SOURCE_FIELD_ID,
890 SUBT.LANGUAGE
891 from AMS_LIST_SRC_FIELDS_TL SUBB, AMS_LIST_SRC_FIELDS_TL SUBT
892 where SUBB.LIST_SOURCE_FIELD_ID = SUBT.LIST_SOURCE_FIELD_ID
893 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
894 and (SUBB.SOURCE_COLUMN_MEANING <> SUBT.SOURCE_COLUMN_MEANING
895 or (SUBB.SOURCE_COLUMN_MEANING is null and SUBT.SOURCE_COLUMN_MEANING is not null)
896 or (SUBB.SOURCE_COLUMN_MEANING is not null and SUBT.SOURCE_COLUMN_MEANING is null)
897 ));
898
899 insert into AMS_LIST_SRC_FIELDS_TL (
900 LAST_UPDATE_LOGIN,
901 SOURCE_COLUMN_MEANING,
902 CREATION_DATE,
903 CREATED_BY,
904 LIST_SOURCE_FIELD_ID,
905 LAST_UPDATE_DATE,
906 LAST_UPDATE_BY,
907 LANGUAGE,
908 SOURCE_LANG
909 ) select /*+ ORDERED */
910 B.LAST_UPDATE_LOGIN,
911 B.SOURCE_COLUMN_MEANING,
912 B.CREATION_DATE,
913 B.CREATED_BY,
914 B.LIST_SOURCE_FIELD_ID,
915 B.LAST_UPDATE_DATE,
916 B.LAST_UPDATE_BY,
917 L.LANGUAGE_CODE,
918 B.SOURCE_LANG
919 from AMS_LIST_SRC_FIELDS_TL B, FND_LANGUAGES L
920 where L.INSTALLED_FLAG in ('I', 'B')
921 and B.LANGUAGE = userenv('LANG')
922 and not exists
923 (select NULL
924 from AMS_LIST_SRC_FIELDS_TL T
925 where T.LIST_SOURCE_FIELD_ID = B.LIST_SOURCE_FIELD_ID
926 and T.LANGUAGE = L.LANGUAGE_CODE);
927 end ADD_LANGUAGE;
928
929 END AMS_LIST_SRC_FIELDS_PKG;