[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_SRC_TYPES_PKG
Source
1 PACKAGE BODY ams_list_src_types_pkg AS
2 /* $Header: amsllstb.pls 120.2 2006/06/07 08:41:17 bmuthukr noship $ */
3
4 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
7
8 PROCEDURE insert_row (
9 x_rowid IN OUT NOCOPY VARCHAR2,
10 x_list_source_type_id IN NUMBER,
11 x_object_version_number IN NUMBER,
12 x_list_source_name IN VARCHAR2,
13 x_list_source_type IN VARCHAR2,
14 x_source_type_code IN VARCHAR2,
15 x_source_object_name IN VARCHAR2,
16 x_master_source_type_flag IN VARCHAR2,
17 x_source_object_pk_field IN VARCHAR2,
18 x_enabled_flag IN VARCHAR2,
19 x_description IN VARCHAR2,
20 X_JAVA_CLASS_NAME IN VARCHAR2,
21 x_view_application_id in number,
22 x_ARC_ACT_SRC_USED_BY in varchar2,
23 x_SOURCE_CATEGORY in varchar2,
24 x_import_type in varchar2,
25 x_creation_date IN DATE,
26 x_created_by IN NUMBER,
27 x_last_update_date IN DATE,
28 x_last_updated_by IN NUMBER,
29 x_last_update_login IN NUMBER,
30 x_BASED_ON_TCA_FLAG IN varchar2
31 ) IS
32 l_import_type VARCHAR2(30);
33 CURSOR c IS SELECT rowid FROM ams_list_src_types
34 WHERE list_source_type_id = x_list_source_type_id;
35
36 CURSOR c_import_type(code IN VARCHAR2) IS SELECT lookup_code FROM ams_lookups
37 WHERE lookup_type = 'AMS_IMPORT_TYPE' and enabled_flag='Y'
38 AND lookup_code = code;
39 BEGIN
40 if x_import_type is not null or
41 x_import_type <> FND_API.G_MISS_CHAR then
42 OPEN c_import_type(x_import_type);
43 FETCH c_import_type into l_import_type;
44 IF (c_import_type%NOTFOUND) THEN
45 CLOSE c_import_type;
46 FND_MESSAGE.SET_NAME('AMS', 'AMS_INVALID_IMPORT_TYPE');
47 APP_EXCEPTION.RAISE_EXCEPTION;
48 END IF;
49 CLOSE c_import_type;
50 end if;
51
52 INSERT INTO ams_list_src_types (
53 list_source_type_id,
54 last_update_date,
55 last_updated_by,
56 creation_date,
57 created_by,
58 last_update_login,
59 object_version_number,
60 list_source_name,
61 list_source_type,
62 source_type_code,
63 source_object_name,
64 master_source_type_flag,
65 source_object_pk_field,
66 enabled_flag,
67 description,
68 java_class_name,
69 view_application_id ,
70 ARC_ACT_SRC_USED_BY ,
71 SOURCE_CATEGORY ,
72 IMPORT_TYPE,
73 BASED_ON_TCA_FLAG
74 )
75 values (
76 x_list_source_type_id,
77 x_last_update_date,
78 x_last_updated_by,
79 x_creation_date,
80 x_created_by,
81 x_last_update_login,
82 x_object_version_number,
83 x_list_source_name,
84 x_list_source_type,
85 x_source_type_code,
86 x_source_object_name,
87 x_master_source_type_flag,
88 x_source_object_pk_field,
89 x_enabled_flag,
90 x_description,
91 x_java_class_name,
92 x_view_application_id ,
93 x_ARC_ACT_SRC_USED_BY ,
94 x_SOURCE_CATEGORY ,
95 x_IMPORT_TYPE,
96 x_BASED_ON_TCA_FLAG
97 );
98 insert into AMS_LIST_SRC_TYPES_TL (
99 LANGUAGE,
100 SOURCE_LANG,
101 LIST_SOURCE_NAME,
102 DESCRIPTION,
103 LIST_SOURCE_TYPE_ID,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_BY,
106 CREATION_DATE,
107 CREATED_BY,
108 LAST_UPDATE_LOGIN
109 ) select
110 l.language_code,
111 userenv('LANG'),
112 x_list_source_name,
113 x_description,
114 x_list_source_type_id,
115 --Modified for bug 5237401. bmuthukr
116 /*
117 sysdate,
118 FND_GLOBAL.user_id,
119 sysdate,
120 FND_GLOBAL.user_id,
121 */
122 x_last_update_date,
123 x_last_updated_by,
124 x_creation_date,
125 x_created_by,
126 --
127 FND_GLOBAL.conc_login_id
128 from FND_LANGUAGES L
129 where L.INSTALLED_FLAG in ('I', 'B')
130 and not exists
131 (select NULL
132 from AMS_LIST_SRC_TYPES_TL T
133 where T.LIST_SOURCE_TYPE_ID = x_list_source_type_id
134 and T.LANGUAGE = L.LANGUAGE_CODE);
135
136
137 OPEN C;
138 FETCH C INTO x_rowid;
139 IF (C%NOTFOUND) THEN
140 CLOSE C;
141 RAISE NO_DATA_FOUND;
142 END IF;
143 CLOSE C;
144
145 END insert_row;
146
147 PROCEDURE lock_row (
148 x_list_source_type_id IN NUMBER,
149 x_object_version_number IN NUMBER,
150 x_list_source_name IN VARCHAR2,
151 x_list_source_type IN VARCHAR2,
152 x_source_type_code IN VARCHAR2,
153 x_source_object_name IN VARCHAR2,
154 x_master_source_type_flag IN VARCHAR2,
155 x_source_object_pk_field IN VARCHAR2,
156 x_enabled_flag IN VARCHAR2,
157 x_description IN VARCHAR2,
158 X_JAVA_CLASS_NAME IN VARCHAR2,
159 x_view_application_id in number,
160 x_ARC_ACT_SRC_USED_BY in varchar2,
161 x_SOURCE_CATEGORY in varchar2,
162 x_import_type in varchar2,
163 x_BASED_ON_TCA_FLAG IN varchar2
164 ) IS
165 CURSOR C1 IS SELECT
166 object_version_number,
167 list_source_name,
168 list_source_type,
169 source_type_code,
170 source_object_name,
171 master_source_type_flag,
172 source_object_pk_field,
173 enabled_flag,
174 description,
175 java_class_name,
176 view_application_id ,
177 ARC_ACT_SRC_USED_BY ,
178 SOURCE_CATEGORY ,
179 IMPORT_TYPE,
180 BASED_ON_TCA_FLAG
181 FROM ams_list_src_types
182 WHERE list_source_type_id = x_list_source_type_id
183 FOR UPDATE OF list_source_type_id NOWAIT;
184 BEGIN
185 FOR TLINFO IN C1 LOOP
186 IF ( ((TLINFO.description = x_description)
187 or ((TLINFO.description IS NULL) and (x_description IS NULL)))
188 and ((TLINFO.object_version_number = x_object_version_number)
189 or ((TLINFO.object_version_number IS NULL) and (x_object_version_number IS NULL)))
190 and (TLINFO.list_source_name = x_list_source_name)
191 and (TLINFO.list_source_type = x_list_source_type)
192 and (TLINFO.source_type_code = x_source_type_code)
193 and (TLINFO.source_object_name = x_source_object_name)
194 and (TLINFO.master_source_type_flag = x_master_source_type_flag)
195 and ((TLINFO.source_object_pk_field = x_source_object_pk_field)
196 or ((TLINFO.source_object_pk_field IS NULL) and (x_source_object_pk_field IS NULL)))
197 and (TLINFO.enabled_flag = x_enabled_flag)
198 and (TLINFO.BASED_ON_TCA_FLAG = X_BASED_ON_TCA_FLAG)
199 ) THEN
200 NULL;
201 ELSE
202 FND_MESSAGE.SET_NAME('fnd', 'form_record_changed');
203 APP_EXCEPTION.RAISE_EXCEPTION;
204 END IF;
205 END LOOP;
206 RETURN;
207 END lock_row;
208
209 PROCEDURE update_row (
210 x_list_source_type_id IN NUMBER,
211 x_object_version_number IN NUMBER,
212 x_list_source_name IN VARCHAR2,
213 x_list_source_type IN VARCHAR2,
214 x_source_type_code IN VARCHAR2,
215 x_source_object_name IN VARCHAR2,
216 x_master_source_type_flag IN VARCHAR2,
217 x_source_object_pk_field IN VARCHAR2,
218 x_enabled_flag IN VARCHAR2,
219 x_description IN VARCHAR2,
220 X_JAVA_CLASS_NAME IN VARCHAR2,
221 x_view_application_id in number,
222 x_ARC_ACT_SRC_USED_BY in varchar2,
223 x_SOURCE_CATEGORY in varchar2,
224 x_IMPORT_TYPE in varchar2,
225 x_last_update_date IN DATE,
226 x_last_updated_by IN NUMBER,
227 x_last_update_login IN NUMBER,
228 x_BASED_ON_TCA_FLAG IN varchar2
229 ) IS
230 l_import_type VARCHAR2(30);
231 CURSOR c_import_type(code IN VARCHAR2) IS SELECT lookup_code FROM ams_lookups
232 WHERE lookup_type = 'AMS_IMPORT_TYPE' and enabled_flag='Y'
233 AND lookup_code = code;
234 BEGIN
235 if x_import_type is not null or
236 x_import_type <> FND_API.G_MISS_CHAR then
237 OPEN c_import_type(x_import_type);
238 FETCH c_import_type into l_import_type;
239 IF (c_import_type%NOTFOUND) THEN
240 CLOSE c_import_type;
241 FND_MESSAGE.SET_NAME('AMS', 'AMS_INVALID_IMPORT_TYPE');
242 APP_EXCEPTION.RAISE_EXCEPTION;
243 END IF;
244 CLOSE c_import_type;
245 end if;
246
247 UPDATE ams_list_src_types SET
248 object_version_number = x_object_version_number,
249 list_source_name = x_list_source_name,
250 list_source_type = x_list_source_type,
251 source_type_code = x_source_type_code,
252 source_object_name = x_source_object_name,
253 master_source_type_flag = x_master_source_type_flag,
254 source_object_pk_field = x_source_object_pk_field,
255 enabled_flag = x_enabled_flag,
256 description = x_description,
257 JAVA_CLASS_NAME = x_java_class_name,
258 view_application_id = x_VIEW_APPLICATION_ID,
259 ARC_ACT_SRC_USED_BY = x_ARC_ACT_SRC_USED_BY,
260 SOURCE_CATEGORY = x_SOURCE_CATEGORY,
261 IMPORT_TYPE = x_IMPORT_TYPE,
262 last_update_date = x_last_update_date,
263 last_updated_by = x_last_updated_by,
264 last_update_login = x_last_update_login,
265 BASED_ON_TCA_FLAG = X_BASED_ON_TCA_FLAG
266 WHERE list_source_type_id = x_list_source_type_id;
267
268 update AMS_LIST_SRC_TYPES_TL set
269 LIST_SOURCE_NAME = x_list_source_name,
270 DESCRIPTION = x_description,
271 LAST_UPDATE_DATE = sysdate,
272 --for bug 5237401
273 -- LAST_UPDATE_BY = FND_GLOBAL.user_id,
274 last_update_by = x_last_updated_by,
275 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
276 SOURCE_LANG = userenv('LANG')
277 WHERE list_source_type_id = x_list_source_type_id
278 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
279
280 IF (SQL%NOTFOUND) THEN
281 RAISE NO_DATA_FOUND;
282 END IF;
283 END update_row;
284
285 PROCEDURE delete_row (
286 x_list_source_type_id IN NUMBER
287 ) IS
288 BEGIN
289 DELETE FROM ams_list_src_types
290 WHERE list_source_type_id = x_list_source_type_id;
291
292 IF (SQL%NOTFOUND) THEN
293 RAISE NO_DATA_FOUND;
294 END IF;
295
296 END delete_row;
297
298 PROCEDURE load_row (
299 X_LIST_SOURCE_TYPE_ID in NUMBER,
300 X_LIST_SOURCE_NAME in VARCHAR2,
301 X_LIST_SOURCE_TYPE in VARCHAR2,
302 X_SOURCE_TYPE_CODE in VARCHAR2,
303 X_SOURCE_OBJECT_NAME in VARCHAR2,
304 X_MASTER_SOURCE_TYPE_FLAG in VARCHAR2,
305 X_SOURCE_OBJECT_PK_FIELD in VARCHAR2,
306 X_ENABLED_FLAG in VARCHAR2,
307 X_DESCRIPTION in VARCHAR2,
308 X_JAVA_CLASS_NAME IN VARCHAR2,
309 x_view_application_id in number,
310 x_ARC_ACT_SRC_USED_BY in varchar2,
311 x_SOURCE_CATEGORY in varchar2,
312 x_import_type in varchar2,
313 x_owner IN VARCHAR2,
314 x_custom_mode IN VARCHAR2,
315 x_BASED_ON_TCA_FLAG 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_source_type_id number;
323 l_last_updated_by number;
324
325 CURSOR c_obj_verno IS
326 SELECT object_version_number, last_updated_by
327 FROM ams_list_src_types
328 WHERE list_source_type_id = x_list_source_type_id;
329
330 CURSOR c_chk_exists is
331 SELECT 'x'
332 FROM ams_list_src_types
333 WHERE list_source_type_id = x_list_source_type_id;
334
335 CURSOR c_get_id is
336 SELECT ams_list_src_types_s.NEXTVAL
337 FROM DUAL;
338 BEGIN
339 if X_OWNER = 'SEED' then
340 l_user_id := 1;
341 elsif X_OWNER = 'ORACLE' then
342 l_user_id := 2;
343 elsif X_OWNER = 'SYSADMIN' THEN
344 l_user_id := 0;
345 end if;
346
347 OPEN c_chk_exists;
348 FETCH c_chk_exists INTO l_dummy_char;
349 IF c_chk_exists%notfound THEN
350 CLOSE c_chk_exists;
351
352 IF x_list_source_type_id IS NULL THEN
353 OPEN c_get_id;
354 FETCH c_get_id INTO l_list_source_type_id;
355 CLOSE c_get_id;
356 ELSE
357 l_list_source_type_id := x_list_source_type_id;
358 END IF;
359 l_obj_verno := 1;
360
361 ams_list_src_types_pkg.Insert_Row (
362 X_ROWID => l_row_id,
363 X_LIST_SOURCE_TYPE_ID => l_list_source_type_id,
364 X_OBJECT_VERSION_NUMBER => l_obj_verno,
365 X_LIST_SOURCE_NAME => x_list_source_name,
366 X_LIST_SOURCE_TYPE => x_list_source_type,
367 X_SOURCE_TYPE_CODE => x_source_type_code,
368 X_SOURCE_OBJECT_NAME => x_source_object_name,
369 X_MASTER_SOURCE_TYPE_FLAG => x_master_source_type_flag,
370 X_SOURCE_OBJECT_PK_FIELD => x_source_object_pk_field,
371 X_ENABLED_FLAG => x_enabled_flag,
372 X_DESCRIPTION => x_description,
373 X_JAVA_CLASS_NAME => x_java_class_name,
374 x_view_application_id => x_VIEW_APPLICATION_ID,
375 x_ARC_ACT_SRC_USED_BY => x_ARC_ACT_SRC_USED_BY,
376 x_SOURCE_CATEGORY => x_SOURCE_CATEGORY,
377 x_IMPORT_TYPE => x_IMPORT_TYPE,
378 X_CREATION_DATE => SYSDATE,
379 X_CREATED_BY => l_user_id,
380 X_LAST_UPDATE_DATE => SYSDATE,
381 X_LAST_UPDATED_BY => l_user_id,
382 X_LAST_UPDATE_LOGIN => 0,
383 x_BASED_ON_TCA_FLAG => x_BASED_ON_TCA_FLAG
384 );
385 ELSE
386 CLOSE c_chk_exists;
387 OPEN c_obj_verno;
388 FETCH c_obj_verno INTO l_obj_verno,l_last_updated_by;
389 CLOSE c_obj_verno;
390
391
392 if (l_last_updated_by in (1,2,0) OR
393 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
394
395 ams_list_src_types_pkg.Update_Row (
396 X_LIST_SOURCE_TYPE_ID => x_list_source_type_id,
397 X_OBJECT_VERSION_NUMBER => l_obj_verno,
398 X_LIST_SOURCE_NAME => x_list_source_name,
399 X_LIST_SOURCE_TYPE => x_list_source_type,
400 X_SOURCE_TYPE_CODE => x_source_type_code,
401 X_SOURCE_OBJECT_NAME => x_source_object_name,
402 X_MASTER_SOURCE_TYPE_FLAG => x_master_source_type_flag,
403 X_SOURCE_OBJECT_PK_FIELD => x_source_object_pk_field,
404 X_ENABLED_FLAG => x_enabled_flag,
405 X_DESCRIPTION => x_description,
406 X_JAVA_CLASS_NAME =>X_JAVA_CLASS_NAME,
407 x_view_application_id =>x_VIEW_APPLICATION_ID,
408 x_ARC_ACT_SRC_USED_BY =>x_ARC_ACT_SRC_USED_BY,
409 x_SOURCE_CATEGORY =>x_SOURCE_CATEGORY,
410 x_IMPORT_TYPE => x_IMPORT_TYPE,
411 X_LAST_UPDATE_DATE => SYSDATE,
412 X_LAST_UPDATED_BY => l_user_id,
413 X_LAST_UPDATE_LOGIN => 0,
414 x_BASED_ON_TCA_FLAG => x_BASED_ON_TCA_FLAG
415 );
416 end if;
417
418 END IF;
419 END load_row;
420
421 procedure ADD_LANGUAGE
422 is
423 begin
424 delete from AMS_LIST_SRC_TYPES_TL T
425 where not exists
426 (select NULL
427 from AMS_LIST_SRC_TYPES B
428 where B.LIST_SOURCE_TYPE_ID = T.LIST_SOURCE_TYPE_ID
429 );
430
431 update AMS_LIST_SRC_TYPES_TL T set (
432 LIST_SOURCE_NAME,
433 DESCRIPTION
434 ) = (select
435 B.LIST_SOURCE_NAME,
436 B.DESCRIPTION
437 from AMS_LIST_SRC_TYPES_TL B
438 where B.LIST_SOURCE_TYPE_ID = T.LIST_SOURCE_TYPE_ID
439 and B.LANGUAGE = T.SOURCE_LANG)
440 where (
441 T.LIST_SOURCE_TYPE_ID,
442 T.LANGUAGE
443 ) in (select
444 SUBT.LIST_SOURCE_TYPE_ID,
445 SUBT.LANGUAGE
446 from AMS_LIST_SRC_TYPES_TL SUBB, AMS_LIST_SRC_TYPES_TL SUBT
447 where SUBB.LIST_SOURCE_TYPE_ID = SUBT.LIST_SOURCE_TYPE_ID
448 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
449 and (SUBB.LIST_SOURCE_NAME <> SUBT.LIST_SOURCE_NAME
450 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
451 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
452 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
453 ));
454
455 insert into AMS_LIST_SRC_TYPES_TL (
456 LIST_SOURCE_TYPE_ID,
457 LAST_UPDATE_DATE,
458 LAST_UPDATE_BY,
459 CREATION_DATE,
460 CREATED_BY,
461 LAST_UPDATE_LOGIN,
462 LIST_SOURCE_NAME,
463 DESCRIPTION,
464 LANGUAGE,
465 SOURCE_LANG
466 ) select /*+ ORDERED */
467 B.LIST_SOURCE_TYPE_ID,
468 B.LAST_UPDATE_DATE,
469 B.LAST_UPDATE_BY,
470 B.CREATION_DATE,
471 B.CREATED_BY,
472 B.LAST_UPDATE_LOGIN,
473 B.LIST_SOURCE_NAME,
474 B.DESCRIPTION,
475 L.LANGUAGE_CODE,
476 B.SOURCE_LANG
477 from AMS_LIST_SRC_TYPES_TL B, FND_LANGUAGES L
478 where L.INSTALLED_FLAG in ('I', 'B')
479 and B.LANGUAGE = userenv('LANG')
480 and not exists
481 (select NULL
482 from AMS_LIST_SRC_TYPES_TL T
483 where T.LIST_SOURCE_TYPE_ID = B.LIST_SOURCE_TYPE_ID
484 and T.LANGUAGE = L.LANGUAGE_CODE);
485 end ADD_LANGUAGE;
486 procedure TRANSLATE_ROW(
487 X_LIST_SOURCE_TYPE_ID in NUMBER,
488 X_LIST_SOURCE_NAME in VARCHAR2,
489 X_DESCRIPTION in VARCHAR2,
490 x_owner in VARCHAR2,
491 x_custom_mode in VARCHAR2
492 ) is
493
494 cursor c_last_updated_by is
495 select last_update_by
496 FROM ams_list_src_types_tl
497 where list_source_type_id = x_list_source_type_id
498 and USERENV('LANG') = LANGUAGE;
499
500 l_last_updated_by number;
501
502
503 begin
504
505
506 open c_last_updated_by;
507 fetch c_last_updated_by into l_last_updated_by;
508 close c_last_updated_by;
509
510 if (l_last_updated_by in (1,2,0) OR
511 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
512
513 update AMS_LIST_SRC_TYPES_TL set
514 list_source_name = nvl(x_list_source_name, list_source_name),
515 description = nvl(x_description, description),
516 source_lang = userenv('LANG'),
517 last_update_date = sysdate,
518 last_update_by = decode(x_owner, 'SEED', 1, 'ORACLE', 2, 'SYSADMIN', 0, -1),
519 last_update_login = 0
520 where list_source_type_id = x_list_source_type_id
521 and userenv('LANG') in (language, source_lang);
522
523 end if;
524 end TRANSLATE_ROW;
525
526 END ams_list_src_types_pkg;