[Home] [Help]
PACKAGE BODY: APPS.AMS_LISTSOURCETYPE_PVT
Source
1 Package Body AMS_ListSourceType_PVT AS
2 /* $Header: amsvlstb.pls 120.0.12020000.2 2012/11/16 11:31:30 annsrini ship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AMS_ListSourceType_PVT
7 --
8 -- PROCEDURES
9 -- Listed are the procedures not declared in the package
10 -- specs:
11 -- Check_ListSrcType_Req_Items
12 -- Check_ListSrcType_UK_Items
13 -- Check_ListSrcType_FK_Items
14 -- Check_ListSrcType_Lookup_Items
15 -- Check_ListSrcType_Flag_Items
16 --
17 -- HISTORY
18 -- 28-Jan-2000 choang Created.
19 -- 31-Jan-2000 choang Enabled cascade delete in delete API.
20 -- Fixed update (complete proc used g_miss_num
21 -- instead of g_miss_char for checking against
22 -- char fields) and create (UK validation
23 -- passed in code with enclosing quotes).
24 -- 06-May-2002 choang added generate_source_fields for analytics
25 -- data sources.
26 -- 06-Jun-2002 choang Exclude DATE fields in generate_source_fields
27 -- 03-Sep-2002 nyostos Added check that ANALYTICS data sources are
28 -- not used by models before deleting them.
29 -- 27-Jan-2003 nyostos Modified generate_source_fields so that it adds the
30 -- primary key in the Data Source Fields table when
31 -- creating ANALYTICS data sources.
32 -- 24-Mar-2003 choang bug 2866418 - added UK validation for list source
33 -- type and source type code.
34 -- 16-NOV-2012 annsrini Fix for bug 15876260 - Replaced all_tab_columns
35 -- with user_synonyms and dba_tab_columns
36 ------------------------------------------------------------
37
38 PROCEDURE Check_ListSrcType_Req_Items (
39 p_listsrctype_rec IN ListSourceType_Rec_Type,
40 x_return_status OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE Check_ListSrcType_UK_Items (
44 p_listsrctype_rec IN ListSourceType_Rec_Type,
45 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
46 x_return_status OUT NOCOPY VARCHAR2
47 );
48
49 PROCEDURE Check_ListSrcType_FK_Items (
50 p_listsrctype_rec IN ListSourceType_Rec_Type,
51 x_return_status OUT NOCOPY VARCHAR2
52 );
53
54 PROCEDURE Check_ListSrcType_Lookup_Items (
55 p_listsrctype_rec IN ListSourceType_Rec_Type,
56 x_return_status OUT NOCOPY VARCHAR2
57 );
58
59 PROCEDURE Check_ListSrcType_Flag_Items (
60 p_listsrctype_rec IN ListSourceType_Rec_Type,
61 x_return_status OUT NOCOPY VARCHAR2
62 );
63
64 PROCEDURE generate_source_fields (
65 p_listsrctype_rec IN ListSourceType_Rec_Type,
66 p_validation_level IN NUMBER,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2
70 );
71
72
73 --------------------------------------------------------------------
74 -- PROCEDURE
75 -- Create_ListSourceType
76 --
77 -- PURPOSE
78 -- Create a list source type entry.
79 --
80 -- PARAMETERS
81 -- p_listsrctype_rec: the record representing AMS_LIST_SRC_TYPES.
82 -- x_list_source_type_id: the list_source_type_id.
83 --
84 -- NOTES
85 -- 1. object_version_number will be set to 1.
86 -- 2. If list_source_type_id is passed in, the uniqueness will be checked.
87 -- Raise exception in case of duplicates.
88 -- 3. If list_source_type_id is not passed in, generate a unique one from
89 -- the sequence.
90 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
91 -- Raise exception for invalid flag.
92 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
93 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
94 --------------------------------------------------------------------
95 PROCEDURE Create_ListSourceType (
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
98 p_commit IN VARCHAR2 := FND_API.g_false,
99 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
100
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2,
104
105 p_listsrctype_rec IN ListSourceType_Rec_Type,
106 x_list_source_type_id OUT NOCOPY NUMBER
107 )
108 IS
109 l_api_version CONSTANT NUMBER := 1.0;
110 l_api_name CONSTANT VARCHAR2(30) := 'Create_ListSourceType';
111 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
112 L_DATA_SOURCE_ANALYTICS CONSTANT VARCHAR2(30) := 'ANALYTICS';
113
114 l_return_status VARCHAR2(1);
115 l_listsrctype_rec ListSourceType_Rec_Type := p_listsrctype_rec;
116 l_dummy NUMBER; -- Capture the exit condition for ID existence loop.
117
118 CURSOR c_seq IS
119 SELECT ams_list_src_types_s.NEXTVAL
120 FROM dual;
121
122 CURSOR c_id_exists (x_id IN NUMBER) IS
123 SELECT 1
124 FROM ams_list_src_types
125 WHERE list_source_type_id = x_id;
126 BEGIN
127 --------------------- initialize -----------------------
128 SAVEPOINT Create_ListSourceType;
129
130 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
131
132 IF FND_API.to_boolean (p_init_msg_list) THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135
136 IF NOT FND_API.compatible_api_call(
137 l_api_version,
138 p_api_version,
139 l_api_name,
140 g_pkg_name
141 ) THEN
142 RAISE FND_API.g_exc_unexpected_error;
143 END IF;
144
145 x_return_status := FND_API.g_ret_sts_success;
146
147 ----------------------- validate -----------------------
148 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
149
150 Validate_ListSourceType (
151 p_api_version => l_api_version,
152 p_init_msg_list => p_init_msg_list,
153 p_validation_level => p_validation_level,
154 x_return_status => l_return_status,
155 x_msg_count => x_msg_count,
156 x_msg_data => x_msg_data,
157 p_listsrctype_rec => l_listsrctype_rec
158 );
159
160 IF l_return_status = FND_API.g_ret_sts_error THEN
161 RAISE FND_API.g_exc_error;
162 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
163 RAISE FND_API.g_exc_unexpected_error;
164 END IF;
165
166 --
167 -- Check for the ID.
168 --
169 IF l_listsrctype_rec.list_source_type_id IS NULL THEN
170 LOOP
171 l_dummy := NULL;
172
173 --
174 -- If the ID is not passed into the API, then
175 -- grab a value from the sequence.
176 OPEN c_seq;
177 FETCH c_seq INTO l_listsrctype_rec.list_source_type_id;
178 CLOSE c_seq;
179
180 --
181 -- Check to be sure that the sequence does not exist.
182 OPEN c_id_exists (l_listsrctype_rec.list_source_type_id);
183 FETCH c_id_exists INTO l_dummy;
184 CLOSE c_id_exists;
185
186 --
187 -- If the value for the ID already exists, then
188 -- l_dummy would be populated with '1', otherwise,
189 -- it receives NULL.
190 EXIT WHEN l_dummy IS NULL;
191 END LOOP;
192 END IF;
193
194 -------------------------- insert --------------------------
195 AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
196
197 INSERT INTO ams_list_src_types (
198 list_source_type_id,
199 last_update_date,
200 last_updated_by,
201 creation_date,
202 created_by,
203 last_update_login,
204 object_version_number,
205 list_source_type,
206 source_type_code,
207 source_object_name,
208 master_source_type_flag,
209 source_object_pk_field,
210 enabled_flag,
211 view_application_id,
212 java_class_name,
213 import_type,
214 arc_act_src_used_by,
215 source_category
216 )
217 VALUES (
218 l_listsrctype_rec.list_source_type_id,
219 SYSDATE,
220 FND_GLOBAL.user_id,
221 SYSDATE,
222 FND_GLOBAL.user_id,
223 FND_GLOBAL.conc_login_id,
224 1, -- object_version_number
225 l_listsrctype_rec.list_source_type,
226 l_listsrctype_rec.source_type_code,
227 l_listsrctype_rec.source_object_name,
228 l_listsrctype_rec.master_source_type_flag,
229 l_listsrctype_rec.source_object_pk_field,
230 -- analytics data sources cannot be enabled when created
231 -- they need to have defined targets
232 DECODE (l_listsrctype_rec.list_source_type, L_DATA_SOURCE_ANALYTICS, 'N', l_listsrctype_rec.enabled_flag),
233 l_listsrctype_rec.view_application_id,
234 l_listsrctype_rec.java_class_name,
235 l_listsrctype_rec.import_type,
236 l_listsrctype_rec.arc_act_src_used_by,
237 l_listsrctype_rec.source_category
238 );
239
240 insert into AMS_LIST_SRC_TYPES_TL (
241 LANGUAGE,
242 SOURCE_LANG,
243 LIST_SOURCE_NAME,
244 DESCRIPTION,
245 LIST_SOURCE_TYPE_ID,
246 LAST_UPDATE_DATE,
247 LAST_UPDATE_BY,
248 CREATION_DATE,
249 CREATED_BY,
250 LAST_UPDATE_LOGIN
251 ) select
252 l.language_code,
253 userenv('LANG'),
254 l_listsrctype_rec.LIST_SOURCE_NAME,
255 l_listsrctype_rec.DESCRIPTION,
256 l_listsrctype_rec.LIST_SOURCE_TYPE_ID,
257 sysdate,
258 FND_GLOBAL.user_id,
259 sysdate,
260 FND_GLOBAL.user_id,
261 FND_GLOBAL.conc_login_id
262 from FND_LANGUAGES L
263 where L.INSTALLED_FLAG in ('I', 'B')
264 and not exists
265 (select NULL
266 from AMS_LIST_SRC_TYPES_TL T
267 where T.LIST_SOURCE_TYPE_ID = l_listsrctype_rec.LIST_SOURCE_TYPE_ID
268 and T.LANGUAGE = L.LANGUAGE_CODE);
269
270 IF l_listsrctype_rec.list_source_type = L_DATA_SOURCE_ANALYTICS THEN
271 generate_source_fields (
272 p_listsrctype_rec => l_listsrctype_rec,
273 p_validation_level => p_validation_level,
274 x_return_status => x_return_status,
275 x_msg_count => x_msg_count,
276 x_msg_data => x_msg_data
277 );
278 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281 END IF;
282
283 ------------------------- finish -------------------------------
284 --
285 -- Set the out variable.
286 x_list_source_type_id := l_listsrctype_rec.list_source_type_id;
287
288 IF FND_API.to_boolean(p_commit) THEN
289 COMMIT;
290 END IF;
291
292 FND_MSG_PUB.count_and_get(
293 p_encoded => FND_API.g_false,
294 p_count => x_msg_count,
295 p_data => x_msg_data
296 );
297
298 AMS_Utility_PVT.debug_message (l_full_name || ': End');
299
300 EXCEPTION
301 WHEN FND_API.g_exc_error THEN
302 ROLLBACK TO Create_ListSourceType;
303 x_return_status := FND_API.g_ret_sts_error;
304 FND_MSG_PUB.count_and_get (
305 p_encoded => FND_API.g_false,
306 p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309 WHEN FND_API.g_exc_unexpected_error THEN
310 ROLLBACK TO Create_ListSourceType;
311 x_return_status := FND_API.g_ret_sts_unexp_error ;
312 FND_MSG_PUB.count_and_get (
313 p_encoded => FND_API.g_false,
314 p_count => x_msg_count,
315 p_data => x_msg_data
316 );
317 WHEN OTHERS THEN
318 ROLLBACK TO Create_ListSourceType;
319 x_return_status := FND_API.g_ret_sts_unexp_error ;
320
321 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
322 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
323 END IF;
324
325 FND_MSG_PUB.count_and_get(
326 p_encoded => FND_API.g_false,
327 p_count => x_msg_count,
328 p_data => x_msg_data
329 );
330 END Create_ListSourceType;
331
332
333 --------------------------------------------------------------------
334 -- PROCEDURE
335 -- Delete_ListSourceType
336 --
337 -- PURPOSE
338 -- Delete a list source type entry.
339 --
340 -- PARAMETERS
341 -- p_list_source_type_id: the list_source_type_id
342 -- p_object_version: the object_version_number
343 --
344 -- ISSUES
345 -- Currently, we are not allowing people to delete list source type
346 -- entries. We may add some business rules for deletion though.
347 --
348 -- NOTES
349 -- 1. Raise exception if the object_version_number doesn't match.
350 --------------------------------------------------------------------
351 PROCEDURE Delete_ListSourceType (
352 p_api_version IN NUMBER,
353 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
354 p_commit IN VARCHAR2 := FND_API.g_false,
355 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
356
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2,
360
361 p_list_source_type_id IN NUMBER,
362 p_object_version IN NUMBER
363 )
364 IS
365 l_api_version CONSTANT NUMBER := 1.0;
366 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListSourceType';
367 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
368 cursor c_delete_sources is
369 select 'x'
370 from ams_list_src_types a
371 WHERE a.list_source_type_id = p_list_source_type_id
372 and exists (select 'x'
373 from ams_list_headers_all b
374 where b.list_source_type = a.source_type_code) ;
375 l_x char(1);
376
377
378 L_DATA_SOURCE_ANALYTICS CONSTANT VARCHAR2(30) := 'ANALYTICS';
379 l_no_of_models NUMBER;
380 l_list_source_type VARCHAR2(30);
381 l_target_id NUMBER;
382
383 -- Cursor to get the type of the data source
384 CURSOR c_get_ds_type (l_id IN NUMBER) IS
385 SELECT list_source_type
386 FROM ams_list_src_types t
387 WHERE t.list_source_type_id = l_id;
388
389 -- Cursor to check if ANALYTICS data source is used in any models
390 CURSOR c_analytics_ds_used (l_id IN NUMBER) IS
391 SELECT count(*)
392 FROM AMS_DM_MODELS_VL m, AMS_DM_TARGETS_VL t
393 WHERE m.TARGET_ID = t.TARGET_ID
394 AND t.data_source_id = l_id;
395
396 -- Cursor to get the target_ids associated with this data source
397 CURSOR c_get_ds_targets (l_id IN NUMBER) IS
398 SELECT target_id
399 FROM ams_dm_targets_vl t
400 WHERE t.data_source_id = l_id;
401
402 BEGIN
403 --------------------- initialize -----------------------
404 SAVEPOINT Delete_ListSourceType;
405
406 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
407
408 IF FND_API.to_boolean (p_init_msg_list) THEN
409 FND_MSG_PUB.initialize;
410 END IF;
411
412 IF NOT FND_API.compatible_api_call (
413 l_api_version,
414 p_api_version,
415 l_api_name,
416 g_pkg_name
417 ) THEN
418 RAISE FND_API.g_exc_unexpected_error;
419 END IF;
420
421 x_return_status := FND_API.g_ret_sts_success;
422
423 ------------------------ delete ------------------------
424 --added vb 06/18/2001
425 --do not allow delete of seeded data source
426
427 open c_delete_sources ;
428 loop
429 fetch c_delete_sources into l_x;
430 exit when c_delete_sources%notfound;
431 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
432 THEN
433 FND_MESSAGE.set_name ('AMS', 'AMS_API_CANNOT_DELETE');
434 FND_MSG_PUB.add;
435 END IF;
436 RAISE FND_API.g_exc_error;
437 end loop ;
438 close c_delete_sources ;
439 IF (p_list_source_type_id <= 10000) THEN
440 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
441 THEN
442 FND_MESSAGE.set_name ('AMS', 'AMS_API_CANNOT_DELETE');
443 FND_MSG_PUB.add;
444 END IF;
445 RAISE FND_API.g_exc_error;
446 END IF;
447
448 -- nyostos - added 09/03/2002
449 -- Do not allow delete of Analytics Data Source if used by any model
450 -- First get the type of the Data Source
451 OPEN c_get_ds_type(p_list_source_type_id);
452 FETCH c_get_ds_type INTO l_list_source_type;
453 CLOSE c_get_ds_type;
454
455 IF l_list_source_type IS NOT NULL AND l_list_source_type = L_DATA_SOURCE_ANALYTICS THEN
456 OPEN c_analytics_ds_used(p_list_source_type_id);
457 FETCH c_analytics_ds_used INTO l_no_of_models;
458 CLOSE c_analytics_ds_used;
459 IF l_no_of_models > 0 THEN
460 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_DM_DATASOURCE_USED');
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463 END IF;
464 -------------------- finish --------------------------
465
466 DELETE FROM ams_list_src_types
467 WHERE list_source_type_id = p_list_source_type_id
468 AND object_version_number = p_object_version
469 ;
470
471 DELETE FROM ams_list_src_types_tl
472 WHERE list_source_type_id = p_list_source_type_id
473 ;
474
475
476 IF (SQL%NOTFOUND) THEN
477 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
478 THEN
479 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
480 FND_MSG_PUB.add;
481 END IF;
482 RAISE FND_API.g_exc_error;
483 END IF;
484
485 -- choang - 31-Jan-2000
486 -- Add cascade delete funtionality.
487 ------------- Delete Child Records --------------------
488 -- AMS_LIST_SRC_FIELDS --
489 DELETE FROM ams_list_src_fields
490 WHERE list_source_type_id = p_list_source_type_id;
491
492 -- AMS_LIST_SRC_TYPE_ASSOCS --
493 DELETE FROM ams_list_src_type_assocs
494 WHERE master_source_type_id = p_list_source_type_id;
495
496 -------------------- finish --------------------------
497
498
499 -- nyostos - added 09/03/2002
500 -- Also delete all targets defined for this data source.
501 LOOP
502 l_target_id := NULL;
503
504 OPEN c_get_ds_targets(p_list_source_type_id);
505 FETCH c_get_ds_targets INTO l_target_id;
506 CLOSE c_get_ds_targets;
507
508 EXIT WHEN l_target_id IS NULL;
509
510 DELETE FROM ams_dm_targets_b
511 WHERE target_id = l_target_id;
512
513 DELETE FROM ams_dm_targets_tl
514 WHERE target_id = l_target_id;
515 END LOOP;
516
517 -------------------- finish --------------------------
518
519
520 IF FND_API.to_boolean (p_commit) THEN
521 COMMIT;
522 END IF;
523
524 FND_MSG_PUB.count_and_get (
525 p_encoded => FND_API.g_false,
526 p_count => x_msg_count,
527 p_data => x_msg_data
528 );
529
530 AMS_Utility_PVT.debug_message(l_full_name || ': End');
531
532 EXCEPTION
533 WHEN FND_API.g_exc_error THEN
534 ROLLBACK TO Delete_ListSourceType;
535 x_return_status := FND_API.g_ret_sts_error;
536 FND_MSG_PUB.count_and_get (
537 p_encoded => FND_API.g_false,
538 p_count => x_msg_count,
539 p_data => x_msg_data
540 );
541 WHEN FND_API.g_exc_unexpected_error THEN
542 ROLLBACK TO Delete_ListSourceType;
543 x_return_status := FND_API.g_ret_sts_unexp_error ;
544 FND_MSG_PUB.count_and_get (
545 p_encoded => FND_API.g_false,
546 p_count => x_msg_count,
547 p_data => x_msg_data
548 );
549 WHEN OTHERS THEN
550 ROLLBACK TO Delete_ListSourceType;
551 x_return_status := FND_API.g_ret_sts_unexp_error ;
552
553 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
554 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
555 END IF;
556
557 FND_MSG_PUB.count_and_get (
558 p_encoded => FND_API.g_false,
559 p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562 END Delete_ListSourceType;
563
564
565 --------------------------------------------------------------------
566 -- PROCEDURE
567 -- Lock_ListSourceType
568 --
569 -- PURPOSE
570 -- Lock a list source type entry.
571 --
572 -- PARAMETERS
573 -- p_list_source_type_id: the list_source_type_id
574 -- p_object_version: the object_version_number
575 --
576 -- ISSUES
577 --
578 -- NOTES
579 -- 1. Raise exception if the object_version_number doesn't match.
580 --------------------------------------------------------------------
581 PROCEDURE Lock_ListSourceType (
582 p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
584 p_commit IN VARCHAR2 := FND_API.g_false,
585 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
586
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590
591 p_list_source_type_id IN NUMBER,
592 p_object_version IN NUMBER
593 )
594 IS
595 l_api_version CONSTANT NUMBER := 1.0;
596 l_api_name CONSTANT VARCHAR2(30) := 'Lock_ListSourceType';
597 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
598
599 l_dummy NUMBER; -- Used by the lock cursor.
600
601 --
602 -- NOTE: Not necessary to distinguish between a record
603 -- which does not exist and one which has been updated
604 -- by another user. To get that distinction, remove
605 -- the object_version condition from the SQL statement
606 -- and perform comparison in the body and raise the
607 -- exception there.
608 CURSOR c_lock_req IS
609 SELECT object_version_number
610 FROM ams_list_src_types
611 WHERE list_source_type_id = p_list_source_type_id
612 AND object_version_number = p_object_version
613 FOR UPDATE NOWAIT;
614 BEGIN
615 --------------------- initialize -----------------------
616 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
617
618 IF FND_API.to_boolean (p_init_msg_list) THEN
619 FND_MSG_PUB.initialize;
620 END IF;
621
622 IF NOT FND_API.compatible_api_call (
623 l_api_version,
624 p_api_version,
625 l_api_name,
626 g_pkg_name
627 ) THEN
628 RAISE FND_API.g_exc_unexpected_error;
629 END IF;
630
631 x_return_status := FND_API.g_ret_sts_success;
632
633 ------------------------ lock -------------------------
634 OPEN c_lock_req;
635 FETCH c_lock_req INTO l_dummy;
636 IF c_lock_req%NOTFOUND THEN
637 CLOSE c_lock_req;
638 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
639 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
640 FND_MSG_PUB.add;
641 END IF;
642 RAISE FND_API.g_exc_error;
643 END IF;
644 CLOSE c_lock_req;
645
646 -------------------- finish --------------------------
647 FND_MSG_PUB.count_and_get (
648 p_encoded => FND_API.g_false,
649 p_count => x_msg_count,
650 p_data => x_msg_data
651 );
652
653 AMS_Utility_PVT.debug_message (l_full_name || ': End');
654
655 EXCEPTION
656 WHEN AMS_Utility_PVT.resource_locked THEN
657 x_return_status := FND_API.g_ret_sts_error;
658 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
659 FND_MESSAGE.set_name ('AMS', 'AMS_API_RESOURCE_LOCKED');
660 FND_MSG_PUB.add;
661 END IF;
662
663 FND_MSG_PUB.count_and_get(
664 p_encoded => FND_API.g_false,
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668 WHEN FND_API.g_exc_error THEN
669 x_return_status := FND_API.g_ret_sts_error;
670 FND_MSG_PUB.count_and_get (
671 p_encoded => FND_API.g_false,
672 p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675 WHEN FND_API.g_exc_unexpected_error THEN
676 x_return_status := FND_API.g_ret_sts_unexp_error ;
677 FND_MSG_PUB.count_and_get (
678 p_encoded => FND_API.g_false,
679 p_count => x_msg_count,
680 p_data => x_msg_data
681 );
682 WHEN OTHERS THEN
683 x_return_status := FND_API.g_ret_sts_unexp_error ;
684 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
685 THEN
686 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
687 END IF;
688
689 FND_MSG_PUB.count_and_get (
690 p_encoded => FND_API.g_false,
691 p_count => x_msg_count,
692 p_data => x_msg_data
693 );
694 END Lock_ListSourceType;
695
696
697 --------------------------------------------------------------------
698 -- PROCEDURE
699 -- Update_ListSourceType
700 --
701 -- PURPOSE
702 -- Update a list source type entry.
703 --
704 -- PARAMETERS
705 -- p_listsrctype_rec: the record representing AMS_LIST_SRC_TYPES.
706 --
707 -- NOTES
708 -- 1. Raise exception if the object_version_number doesn't match.
709 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
710 -- that column won't be updated.
711 --------------------------------------------------------------------
712 PROCEDURE Update_ListSourceType (
713 p_api_version IN NUMBER,
714 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
715 p_commit IN VARCHAR2 := FND_API.g_false,
716 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
717
718 x_return_status OUT NOCOPY VARCHAR2,
719 x_msg_count OUT NOCOPY NUMBER,
720 x_msg_data OUT NOCOPY VARCHAR2,
721
722 p_listsrctype_rec IN ListSourceType_Rec_Type
723 )
724 IS
725 l_api_version CONSTANT NUMBER := 1.0;
726 l_api_name CONSTANT VARCHAR2(30) := 'Update_ListSourceType';
727 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
728
729 l_listsrctype_rec ListSourceType_Rec_Type := p_listsrctype_rec;
730 l_return_status VARCHAR2(1);
731 BEGIN
732 --------------------- initialize -----------------------
733 SAVEPOINT Update_ListSourceType;
734
735 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
736
737 IF FND_API.to_boolean (p_init_msg_list) THEN
738 FND_MSG_PUB.initialize;
739 END IF;
740
741 IF NOT FND_API.compatible_api_call(
742 l_api_version,
743 p_api_version,
744 l_api_name,
745 g_pkg_name
746 ) THEN
747 RAISE FND_API.g_exc_unexpected_error;
748 END IF;
749
750 x_return_status := FND_API.g_ret_sts_success;
751
752 --added vb 06/28/2001
753 --do not allow update of seeded data source
754
755 /*
756 IF (l_listsrctype_rec.list_source_type_id <= 10000) THEN
757 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
758 THEN
759 FND_MESSAGE.set_name ('AMS', 'AMS_API_CANNOT_UPDATE');
760 FND_MSG_PUB.add;
761 END IF;
762 RAISE FND_API.g_exc_error;
763 END IF;
764 */
765
766 ----------------------- validate ----------------------
767 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
768
769 -- replace g_miss_char/num/date with current column values
770 Complete_ListSourceType_Rec (p_listsrctype_rec, l_listsrctype_rec);
771
772 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
773 Check_ListSourceType_Items (
774 p_listsrctype_rec => l_listsrctype_rec,
775 p_validation_mode => JTF_PLSQL_API.g_update,
776 x_return_status => l_return_status
777 );
778
779 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
780 RAISE FND_API.g_exc_unexpected_error;
781 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
782 RAISE FND_API.g_exc_error;
783 END IF;
784 END IF;
785
786 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
787 Check_ListSourceType_Record (
788 p_listsrctype_rec => p_listsrctype_rec,
789 p_complete_rec => l_listsrctype_rec,
790 x_return_status => l_return_status
791 );
792
793 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
794 RAISE FND_API.g_exc_unexpected_error;
795 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
796 RAISE FND_API.g_exc_error;
797 END IF;
798 END IF;
799
800 -------------------------- update --------------------
801 UPDATE ams_list_src_types
802 SET
803 last_update_date = SYSDATE,
804 last_updated_by = FND_GLOBAL.user_id,
805 last_update_login = FND_GLOBAL.conc_login_id,
806 object_version_number = object_version_number + 1,
807 list_source_type = l_listsrctype_rec.list_source_type,
808 source_type_code = l_listsrctype_rec.source_type_code,
809 source_object_name = l_listsrctype_rec.source_object_name,
810 master_source_type_flag = l_listsrctype_rec.master_source_type_flag,
811 source_object_pk_field = l_listsrctype_rec.source_object_pk_field,
812 enabled_flag = l_listsrctype_rec.enabled_flag,
813 view_application_id = l_listsrctype_rec.view_application_id,
814 java_class_name = l_listsrctype_rec.java_class_name,
815 import_type = l_listsrctype_rec.import_type,
816 arc_act_src_used_by = l_listsrctype_rec.arc_act_src_used_by,
817 source_category = l_listsrctype_rec.source_category
818 WHERE list_source_type_id = l_listsrctype_rec.list_source_type_id
819 AND object_version_number = l_listsrctype_rec.object_version_number
820 ;
821
822 IF (SQL%NOTFOUND) THEN
823 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
824 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
825 FND_MSG_PUB.add;
826 END IF;
827 RAISE FND_API.g_exc_error;
828 END IF;
829
830 update AMS_LIST_SRC_TYPES_TL set
831 LIST_SOURCE_NAME = l_listsrctype_rec.LIST_SOURCE_NAME,
832 DESCRIPTION = l_listsrctype_rec.DESCRIPTION,
833 LAST_UPDATE_DATE = sysdate,
834 LAST_UPDATE_BY = FND_GLOBAL.user_id,
835 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
836 SOURCE_LANG = userenv('LANG')
837 where list_source_type_id = l_listsrctype_rec.list_source_type_id
838 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
839
840 IF (SQL%NOTFOUND)THEN
841 ------------------------------------------------------------------
842 -- Error, check the msg level and added an error message to the --
843 -- API message list. --
844 ------------------------------------------------------------------
845 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
846 FND_MESSAGE.set_name('AMS', 'API_UNEXP_ERROR_IN_PROCESSING');
847 FND_MESSAGE.Set_Token('ROW', 'AMS_ListSourceType_PVT.Upd_AMS_LIST_SRC_TYPES_TL', TRUE);
848 FND_MSG_PUB.Add;
849 END IF;
850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
851 END IF;
852
853 -------------------- finish --------------------------
854 IF FND_API.to_boolean (p_commit) THEN
855 COMMIT;
856 END IF;
857
858 FND_MSG_PUB.count_and_get (
859 p_encoded => FND_API.g_false,
860 p_count => x_msg_count,
861 p_data => x_msg_data
862 );
863
864 AMS_Utility_PVT.debug_message (l_full_name || ': End');
865
866 EXCEPTION
867 WHEN FND_API.g_exc_error THEN
868 ROLLBACK TO Update_ListSourceType;
869 x_return_status := FND_API.g_ret_sts_error;
870 FND_MSG_PUB.count_and_get (
871 p_encoded => FND_API.g_false,
872 p_count => x_msg_count,
873 p_data => x_msg_data
874 );
875 WHEN FND_API.g_exc_unexpected_error THEN
876 ROLLBACK TO Update_ListSourceType;
877 x_return_status := FND_API.g_ret_sts_unexp_error ;
878 FND_MSG_PUB.count_and_get (
879 p_encoded => FND_API.g_false,
880 p_count => x_msg_count,
881 p_data => x_msg_data
882 );
883 WHEN OTHERS THEN
884 ROLLBACK TO Update_ListSourceType;
885 x_return_status := FND_API.g_ret_sts_unexp_error ;
886
887 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
888 THEN
889 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
890 END IF;
891
892 FND_MSG_PUB.count_and_get (
893 p_encoded => FND_API.g_false,
894 p_count => x_msg_count,
895 p_data => x_msg_data
896 );
897 END Update_ListSourceType;
898
899
900 --------------------------------------------------------------------
901 -- PROCEDURE
902 -- Validate_ListSourceType
903 --
904 -- PURPOSE
905 -- Validate a list source type entry.
906 --
907 -- PARAMETERS
908 -- p_listsrctype_rec: the record representing AMS_LIST_SRC_TYPES.
909 --
910 -- NOTES
911 -- 1. p_listsrctype_rec should be the complete list source type record. There
912 -- should not be any FND_API.g_miss_char/num/date in it.
913 -- 2. If FND_API.g_miss_char/num/date is in the record, then raise
914 -- an exception, as those values are not handled.
915 --------------------------------------------------------------------
916 PROCEDURE Validate_ListSourceType (
917 p_api_version IN NUMBER,
918 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
919 p_commit IN VARCHAR2 := FND_API.g_false,
920 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
921
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER,
924 x_msg_data OUT NOCOPY VARCHAR2,
925
926 p_listsrctype_rec IN ListSourceType_Rec_Type
927 )
928 IS
929 l_api_version CONSTANT NUMBER := 1.0;
930 l_api_name CONSTANT VARCHAR2(30) := 'Validate_ListSourceType';
931 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
932
933 l_return_status VARCHAR2(1);
934 BEGIN
935 --------------------- initialize -----------------------
936 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
937
938 IF FND_API.to_boolean (p_init_msg_list) THEN
939 FND_MSG_PUB.initialize;
940 END IF;
941
942 IF NOT FND_API.compatible_api_call (
943 l_api_version,
944 p_api_version,
945 l_api_name,
946 g_pkg_name
947 ) THEN
948 RAISE FND_API.g_exc_unexpected_error;
949 END IF;
950
951 x_return_status := FND_API.g_ret_sts_success;
952
953 ---------------------- validate ------------------------
954 AMS_Utility_PVT.debug_message (l_full_name || ': Check items');
955
956 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
957 Check_ListSourceType_Items (
958 p_listsrctype_rec => p_listsrctype_rec,
959 p_validation_mode => JTF_PLSQL_API.g_create,
960 x_return_status => l_return_status
961 );
962
963 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
964 RAISE FND_API.g_exc_unexpected_error;
965 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
966 RAISE FND_API.g_exc_error;
967 END IF;
968 END IF;
969
970 AMS_Utility_PVT.debug_message (l_full_name || ': Check record');
971
972 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
973 Check_ListSourceType_Record (
974 p_listsrctype_rec => p_listsrctype_rec,
975 p_complete_rec => NULL,
976 x_return_status => l_return_status
977 );
978
979 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
980 RAISE FND_API.g_exc_unexpected_error;
981 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
982 RAISE FND_API.g_exc_error;
983 END IF;
984 END IF;
985
986 -------------------- finish --------------------------
987 FND_MSG_PUB.count_and_get (
988 p_encoded => FND_API.g_false,
989 p_count => x_msg_count,
990 p_data => x_msg_data
991 );
992
993 AMS_Utility_PVT.debug_message (l_full_name || ': End');
994
995 EXCEPTION
996 WHEN FND_API.g_exc_error THEN
997 x_return_status := FND_API.g_ret_sts_error;
998 FND_MSG_PUB.count_and_get (
999 p_encoded => FND_API.g_false,
1000 p_count => x_msg_count,
1001 p_data => x_msg_data
1002 );
1003 WHEN FND_API.g_exc_unexpected_error THEN
1004 x_return_status := FND_API.g_ret_sts_unexp_error ;
1005 FND_MSG_PUB.count_and_get (
1006 p_encoded => FND_API.g_false,
1007 p_count => x_msg_count,
1008 p_data => x_msg_data
1009 );
1010 WHEN OTHERS THEN
1011 x_return_status := FND_API.g_ret_sts_unexp_error;
1012 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
1013 THEN
1014 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1015 END IF;
1016
1017 FND_MSG_PUB.count_and_get (
1018 p_encoded => FND_API.g_false,
1019 p_count => x_msg_count,
1020 p_data => x_msg_data
1021 );
1022 END Validate_ListSourceType;
1023
1024 PROCEDURE check_lstsrctype_business(
1025 p_listsrctype_rec IN ListSourceType_Rec_Type,
1026 x_return_status OUT NOCOPY VARCHAR2
1027 )
1028 IS
1029 l_import_type VARCHAR2(30);
1030 CURSOR c_import_type(code IN VARCHAR2) IS SELECT lookup_code FROM ams_lookups
1031 WHERE lookup_type = 'AMS_IMPORT_TYPE' and enabled_flag='Y'
1032 AND lookup_code = code;
1033
1034 cursor c_viewname(code in varchar2) is
1035 SELECT length(nvl(TRANSLATE(code,
1036 '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_',
1037 ' '), 0))
1038 FROM DUAL;
1039 l_cnt NUMBER := 1;
1040
1041 BEGIN
1042
1043 x_return_status := FND_API.g_ret_sts_success;
1044
1045 -- choang - 07-may-2002
1046 -- import type not relevant for analytics
1047 -- gjoby 29 - MAY -02 check only for import
1048 IF p_listsrctype_rec.list_source_type = 'IMPORT' THEN
1049 OPEN c_import_type(p_listsrctype_rec.import_type);
1050 FETCH c_import_type into l_import_type;
1051 IF (c_import_type%NOTFOUND) THEN
1052 CLOSE c_import_type;
1053 FND_MESSAGE.SET_NAME('AMS', 'AMS_INVALID_IMPORT_TYPE');
1054 FND_MSG_PUB.Add;
1055 x_return_status := FND_API.g_ret_sts_error;
1056 RETURN;
1057 END IF;
1058 CLOSE c_import_type;
1059 END IF;
1060
1061 -- View creation is based on source_type_code which
1062 -- contains characters, numbers, and understore characters
1063 OPEN c_viewname(p_listsrctype_rec.source_type_code);
1064 FETCH c_viewname into l_cnt;
1065 IF l_cnt > 1 THEN
1066 FND_MESSAGE.SET_NAME('AMS', 'AMS_INVALID_SOURCE_TYPE_CODE');
1067 FND_MSG_PUB.Add;
1068 x_return_status := FND_API.g_ret_sts_error;
1069 RETURN;
1070 END IF;
1071 CLOSE c_viewname;
1072
1073 END check_lstsrctype_business;
1074
1075
1076 ---------------------------------------------------------------------
1077 -- PROCEDURE
1078 -- Check_ListSrcType_Items
1079 --
1080 -- PURPOSE
1081 -- Perform the item level checking including unique keys,
1082 -- required columns, foreign keys, domain constraints.
1083 --
1084 -- PARAMETERS
1085 -- p_listsrctype_rec: the record to be validated
1086 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
1087 ---------------------------------------------------------------------
1088 PROCEDURE Check_ListSourceType_Items (
1089 p_listsrctype_rec IN ListSourceType_Rec_Type,
1090 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1091 x_return_status OUT NOCOPY VARCHAR2
1092 )
1093 IS
1094 BEGIN
1095 --
1096 -- Validate required items.
1097 Check_ListSrcType_Req_Items (
1098 p_listsrctype_rec => p_listsrctype_rec,
1099 x_return_status => x_return_status
1100 );
1101
1102 IF x_return_status <> FND_API.g_ret_sts_success THEN
1103 RETURN;
1104 END IF;
1105
1106 --
1107 -- Validate uniqueness.
1108 Check_ListSrcType_UK_Items (
1109 p_listsrctype_rec => p_listsrctype_rec,
1110 p_validation_mode => p_validation_mode,
1111 x_return_status => x_return_status
1112 );
1113
1114 IF x_return_status <> FND_API.g_ret_sts_success THEN
1115 RETURN;
1116 END IF;
1117
1118 Check_ListSrcType_FK_Items(
1119 p_listsrctype_rec => p_listsrctype_rec,
1120 x_return_status => x_return_status
1121 );
1122
1123 IF x_return_status <> FND_API.g_ret_sts_success THEN
1124 RETURN;
1125 END IF;
1126
1127 Check_ListSrcType_Lookup_Items (
1128 p_listsrctype_rec => p_listsrctype_rec,
1129 x_return_status => x_return_status
1130 );
1131
1132 IF x_return_status <> FND_API.g_ret_sts_success THEN
1133 RETURN;
1134 END IF;
1135
1136 Check_ListSrcType_Flag_Items(
1137 p_listsrctype_rec => p_listsrctype_rec,
1138 x_return_status => x_return_status
1139 );
1140
1141 IF x_return_status <> FND_API.g_ret_sts_success THEN
1142 RETURN;
1143 END IF;
1144
1145 AMS_UTILITY_PVT.debug_message('Private API: ' || 'before check_lstsrctype_business');
1146 check_lstsrctype_business(
1147 p_listsrctype_rec => p_listsrctype_rec,
1148 x_return_status => x_return_status);
1149 IF x_return_status <> FND_API.g_ret_sts_success THEN
1150 RETURN;
1151 END IF;
1152 AMS_UTILITY_PVT.debug_message('Private API: ' || 'after check_lstsrctype_business');
1153
1154 END Check_ListSourceType_Items;
1155
1156
1157 ---------------------------------------------------------------------
1158 -- PROCEDURE
1159 -- Check_ListSrcType_Record
1160 --
1161 -- PURPOSE
1162 -- Check the record level business rules.
1163 --
1164 -- PARAMETERS
1165 -- p_listsrctype_rec: the record to be validated; may contain attributes
1166 -- as FND_API.g_miss_char/num/date
1167 -- p_complete_rec: the complete record after all "g_miss" items
1168 -- have been replaced by current database values
1169 ---------------------------------------------------------------------
1170 PROCEDURE Check_ListSourceType_Record (
1171 p_listsrctype_rec IN ListSourceType_Rec_Type,
1172 p_complete_rec IN ListSourceType_Rec_Type := NULL,
1173 x_return_status OUT NOCOPY VARCHAR2
1174 )
1175 IS
1176 CURSOR c_reference (p_list_source_type_id IN NUMBER) IS
1177 SELECT list_source_type
1178 , source_type_code
1179 , source_object_name
1180 , master_source_type_flag
1181 , source_object_pk_field
1182 , enabled_flag
1183 , description
1184 , view_application_id
1185 , list_source_name
1186 , java_class_name
1187 , arc_act_src_used_by
1188 , source_category
1189 , import_type
1190 FROM AMS_LIST_SRC_TYPES_VL
1191 WHERE list_source_type_id = p_list_source_type_id
1192 ;
1193 l_reference_rec c_reference%ROWTYPE;
1194
1195 CURSOR c_target_exists (p_list_source_type_id IN NUMBER) IS
1196 SELECT 1
1197 FROM ams_dm_targets_b
1198 WHERE data_source_id = p_list_source_type_id
1199 AND active_flag = 'Y'
1200 ;
1201
1202 l_target_indicator NUMBER;
1203 BEGIN
1204 x_return_status := FND_API.g_ret_sts_success;
1205
1206 -- only perform these validations when an UPDATE
1207 -- API call is made.
1208 IF p_complete_rec.list_source_type_id IS NOT NULL THEN
1209 OPEN c_reference (p_listsrctype_rec.list_source_type_id);
1210 FETCH c_reference INTO l_reference_rec;
1211 CLOSE c_reference;
1212
1213 -- choang - 07-may-2002
1214 -- analytics data sources must have active targets
1215 -- defined for them before they can be made active.
1216 IF l_reference_rec.list_source_type = 'ANALYTICS' THEN
1217 IF l_reference_rec.enabled_flag = 'N' AND p_listsrctype_rec.enabled_flag = 'Y' THEN
1218 OPEN c_target_exists (p_listsrctype_rec.list_source_type_id);
1219 FETCH c_target_exists INTO l_target_indicator;
1220 CLOSE c_target_exists;
1221
1222 IF l_target_indicator IS NULL OR l_target_indicator <> 1 THEN
1223 AMS_Utility_PVT.error_message ('AMS_DM_NO_TARGETS');
1224 x_return_status := FND_API.g_ret_sts_error;
1225 END IF;
1226 END IF;
1227 END IF; -- if analytics data source
1228 END IF;
1229 END Check_ListSourceType_Record;
1230
1231
1232 ---------------------------------------------------------------------
1233 -- PROCEDURE
1234 -- Init_ListSourceType_Rec
1235 --
1236 -- PURPOSE
1237 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
1238 ---------------------------------------------------------------------
1239 PROCEDURE Init_ListSourceType_Rec (
1240 x_listsrctype_rec OUT NOCOPY ListSourceType_Rec_Type
1241 )
1242 IS
1243 BEGIN
1244 x_listsrctype_rec.list_source_type_id := FND_API.g_miss_num;
1245 x_listsrctype_rec.last_update_date := FND_API.g_miss_date;
1246 x_listsrctype_rec.last_updated_by := FND_API.g_miss_num;
1247 x_listsrctype_rec.creation_date := FND_API.g_miss_date;
1248 x_listsrctype_rec.created_by := FND_API.g_miss_num;
1249 x_listsrctype_rec.last_update_login := FND_API.g_miss_num;
1250 x_listsrctype_rec.object_version_number := FND_API.g_miss_num;
1251 x_listsrctype_rec.list_source_type := FND_API.g_miss_char;
1252 x_listsrctype_rec.list_source_name := FND_API.g_miss_char;
1253 x_listsrctype_rec.source_type_code := FND_API.g_miss_char;
1254 x_listsrctype_rec.source_object_name := FND_API.g_miss_char;
1255 x_listsrctype_rec.master_source_type_flag := FND_API.g_miss_char;
1256 x_listsrctype_rec.source_object_pk_field := FND_API.g_miss_char;
1257 x_listsrctype_rec.enabled_flag := FND_API.g_miss_char;
1258 x_listsrctype_rec.description := FND_API.g_miss_char;
1259 x_listsrctype_rec.view_application_id := FND_API.g_miss_num;
1260 END Init_ListSourceType_Rec;
1261
1262
1263 ---------------------------------------------------------------------
1264 -- PROCEDURE
1265 -- Complete_ListSourceType_Rec
1266 --
1267 -- PURPOSE
1268 -- For Update_ListSourceType, some attributes may be passed in as
1269 -- FND_API.g_miss_char/num/date if the user doesn't want to
1270 -- update those attributes. This procedure will replace the
1271 -- "g_miss" attributes with current database values.
1272 --
1273 -- PARAMETERS
1274 -- p_listdr_rec: the record which may contain attributes as
1275 -- FND_API.g_miss_char/num/date
1276 -- x_complete_rec: the complete record after all "g_miss" items
1277 -- have been replaced by current database values
1278 ---------------------------------------------------------------------
1279 PROCEDURE Complete_ListSourceType_Rec (
1280 p_listsrctype_rec IN ListSourceType_Rec_Type,
1281 x_complete_rec OUT NOCOPY ListSourceType_Rec_Type
1282 )
1283 IS
1284 CURSOR c_fields IS
1285 SELECT *
1286 FROM ams_list_src_types_vl
1287 WHERE list_source_type_id = p_listsrctype_rec.list_source_type_id
1288 ;
1289 l_listsrctype_rec c_fields%ROWTYPE;
1290 BEGIN
1291 x_complete_rec := p_listsrctype_rec;
1292
1293 OPEN c_fields;
1294 FETCH c_fields INTO l_listsrctype_rec;
1295 IF c_fields%NOTFOUND THEN
1296 CLOSE c_fields;
1297 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1298 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1299 FND_MSG_PUB.add;
1300 END IF;
1301 RAISE FND_API.g_exc_error;
1302 END IF;
1303 CLOSE c_fields;
1304
1305
1306 -- LIST_SOURCE_TYPE
1307 IF p_listsrctype_rec.list_source_type = FND_API.g_miss_char THEN
1308 x_complete_rec.list_source_type := l_listsrctype_rec.list_source_type;
1309 END IF;
1310
1311 -- LIST_SOURCE_NAME
1312 IF p_listsrctype_rec.list_source_name = FND_API.g_miss_char THEN
1313 x_complete_rec.list_source_name := l_listsrctype_rec.list_source_name;
1314 END IF;
1315
1316
1317 -- SOURCE_TYPE_CODE
1318 IF p_listsrctype_rec.source_type_code = FND_API.g_miss_char THEN
1319 x_complete_rec.source_type_code := l_listsrctype_rec.source_type_code;
1320 END IF;
1321
1322 -- SOURCE_OBJECT_NAME
1323 IF p_listsrctype_rec.source_object_name = FND_API.g_miss_char THEN
1324 x_complete_rec.source_object_name := l_listsrctype_rec.source_object_name;
1325 END IF;
1326
1327 -- MASTER_SOURCE_TYPE_FLAG
1328 IF p_listsrctype_rec.master_source_type_flag = FND_API.g_miss_char THEN
1329 x_complete_rec.master_source_type_flag := l_listsrctype_rec.master_source_type_flag;
1330 END IF;
1331
1332 -- SOURCE_OBJECT_PK_FIELD
1333 IF p_listsrctype_rec.source_object_pk_field = FND_API.g_miss_char THEN
1334 x_complete_rec.source_object_pk_field := l_listsrctype_rec.source_object_pk_field;
1335 END IF;
1336
1337 -- ENABLED_FLAG
1338 IF p_listsrctype_rec.enabled_flag = FND_API.g_miss_char THEN
1339 x_complete_rec.enabled_flag := l_listsrctype_rec.enabled_flag;
1340 END IF;
1341
1342 -- DESCRIPTION
1343 IF p_listsrctype_rec.description = FND_API.g_miss_char THEN
1344 x_complete_rec.description := l_listsrctype_rec.description;
1345 END IF;
1346
1347
1348 -- VIEW APPLICATION ID
1349 IF p_listsrctype_rec.view_application_id = FND_API.g_miss_num THEN
1350 x_complete_rec.view_application_id := l_listsrctype_rec.view_application_id;
1351 END IF;
1352
1353 END Complete_ListSourceType_Rec;
1354
1355
1356 ---------------------------------------------------------------------
1357 -- PROCEDURE
1358 -- Check_ListSrcType_Record
1359 --
1360 -- PURPOSE
1361 -- Check the record level business rules.
1362 --
1363 -- PARAMETERS
1364 -- p_listsrctype_rec: the record to be validated; may contain attributes
1365 -- as FND_API.g_miss_char/num/date
1366 -- p_complete_rec: the complete record after all "g_miss" items
1367 -- have been replaced by current database values
1368 ---------------------------------------------------------------------
1369 PROCEDURE Check_ListSrcType_Req_Items (
1370 p_listsrctype_rec IN ListSourceType_Rec_Type,
1371 x_return_status OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 BEGIN
1375 x_return_status := FND_API.g_ret_sts_success;
1376
1377
1378 -- list_source_type
1379 IF p_listsrctype_rec.list_source_type IS NULL THEN
1380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1381 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_LISTSRC_TYPE');
1382 FND_MSG_PUB.add;
1383 END IF;
1384
1385 x_return_status := FND_API.g_ret_sts_error;
1386 RETURN;
1387 END IF;
1388
1389 -- list_source_name
1390 IF p_listsrctype_rec.list_source_name IS NULL THEN
1391 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1392 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_LISTSRC_NAME');
1393 FND_MSG_PUB.add;
1394 END IF;
1395
1396 x_return_status := FND_API.g_ret_sts_error;
1397 RETURN;
1398 END IF;
1399
1400
1401 -- source_type_code
1402 IF p_listsrctype_rec.source_type_code IS NULL THEN
1403 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1404 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_SRCTYPE_CODE');
1405 FND_MSG_PUB.add;
1406 END IF;
1407
1408 x_return_status := FND_API.g_ret_sts_error;
1409 RETURN;
1410 END IF;
1411
1412 -- source_object_name
1413 IF p_listsrctype_rec.source_object_name IS NULL THEN
1414 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1415 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_SRCOBJ_NAME');
1416 FND_MSG_PUB.add;
1417 END IF;
1418
1419 x_return_status := FND_API.g_ret_sts_error;
1420 RETURN;
1421 END IF;
1422
1423 END Check_ListSrcType_Req_Items;
1424
1425 ---------------------------------------------------------------------
1426 -- PROCEDURE
1427 -- Check_ListSrcType_Record
1428 --
1429 -- PURPOSE
1430 -- Check the record level business rules.
1431 --
1432 -- PARAMETERS
1433 -- p_listsrctype_rec: the record to be validated; may contain attributes
1434 -- as FND_API.g_miss_char/num/date
1435 -- p_complete_rec: the complete record after all "g_miss" items
1436 -- have been replaced by current database values
1437 ---------------------------------------------------------------------
1438 PROCEDURE Check_ListSrcType_UK_Items (
1439 p_listsrctype_rec IN ListSourceType_Rec_Type,
1440 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1441 x_return_status OUT NOCOPY VARCHAR2
1442 )
1443 IS
1444 l_list_src_types_table CONSTANT VARCHAR2(30) := 'ams_list_src_types';
1445 l_valid_flag VARCHAR2(1);
1446 l_where_clause VARCHAR2(4000);
1447
1448 CURSOR c_name_create (p_name IN VARCHAR2) IS
1449 SELECT 1
1450 FROM ams_list_src_types_vl
1451 WHERE list_source_name = p_name;
1452
1453 CURSOR c_name_update (p_name IN VARCHAR2, p_id IN NUMBER) IS
1454 SELECT 'Y'
1455 FROM ams_list_src_types_vl
1456 WHERE list_source_name = p_name
1457 AND list_source_type_id <> p_id;
1458
1459 BEGIN
1460 l_where_clause := 'list_source_type = ''' || p_listsrctype_rec.list_source_type || ''' ' ||
1461 'AND source_type_code = ''' || p_listsrctype_rec.source_type_code || '''';
1462 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1463 -- Validate that the list_source_name is unique.
1464 OPEN c_name_create (p_listsrctype_rec.list_source_name);
1465 FETCH c_name_create INTO l_valid_flag;
1466 CLOSE c_name_create;
1467 IF l_valid_flag IS NOT NULL THEN
1468 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1469 FND_MESSAGE.set_name('AMS', 'AMS_LIST_DUPE_LISTSRC_NAME');
1470 FND_MSG_PUB.add;
1471 END IF;
1472 x_return_status := FND_API.g_ret_sts_error;
1473 l_valid_flag := NULL;
1474 END IF;
1475
1476 -- list_source_type and source_type_code
1477 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
1478 p_table_name => l_list_src_types_table,
1479 p_where_clause => l_where_clause
1480 );
1481 IF l_valid_flag = FND_API.g_false THEN
1482 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1483 FND_MESSAGE.set_name('AMS', 'AMS_LIST_DUPE_LISTSRC_TYPE');
1484 FND_MSG_PUB.add;
1485 END IF;
1486 x_return_status := FND_API.g_ret_sts_error;
1487 l_valid_flag := NULL;
1488 END IF;
1489 ELSE -- update operations have to exclude the current record (by ID)
1490 -- Validate that the list_source_name is unique.
1491 OPEN c_name_update (p_listsrctype_rec.list_source_name, p_listsrctype_rec.list_source_type_id);
1492 FETCH c_name_update INTO l_valid_flag;
1493 CLOSE c_name_update;
1494 IF l_valid_flag IS NOT NULL THEN
1495 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1496 FND_MESSAGE.set_name('AMS', 'AMS_LIST_DUPE_LISTSRC_NAME');
1497 FND_MSG_PUB.add;
1498 END IF;
1499 x_return_status := FND_API.g_ret_sts_error;
1500 l_valid_flag := NULL;
1501 END IF;
1502
1503 -- list_source_type and source_type_code
1504 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
1505 p_table_name => l_list_src_types_table,
1506 p_where_clause => l_where_clause || ' AND list_source_type_id <> ' || p_listsrctype_rec.list_source_type_id
1507 );
1508 IF l_valid_flag = FND_API.g_false THEN
1509 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1510 FND_MESSAGE.set_name('AMS', 'AMS_LIST_DUPE_LISTSRC_TYPE');
1511 FND_MSG_PUB.add;
1512 END IF;
1513 x_return_status := FND_API.g_ret_sts_error;
1514 l_valid_flag := NULL;
1515 END IF;
1516 END IF;
1517 END Check_ListSrcType_UK_Items;
1518
1519 ---------------------------------------------------------------------
1520 -- PROCEDURE
1521 -- Check_ListSrcType_Record
1522 --
1523 -- PURPOSE
1524 -- Check the record level business rules.
1525 --
1526 -- PARAMETERS
1527 -- p_listsrctype_rec: the record to be validated; may contain attributes
1528 -- as FND_API.g_miss_char/num/date
1529 -- p_complete_rec: the complete record after all "g_miss" items
1530 -- have been replaced by current database values
1531 ---------------------------------------------------------------------
1532 PROCEDURE Check_ListSrcType_FK_Items (
1533 p_listsrctype_rec IN ListSourceType_Rec_Type,
1534 x_return_status OUT NOCOPY VARCHAR2
1535 )
1536 IS
1537 ----------------------------------------------------
1538 -- NOTE:
1539 -- Do we need to add checking for the column pk
1540 -- field? How about the table field?
1541 ----------------------------------------------------
1542 BEGIN
1543 x_return_status := FND_API.g_ret_sts_success;
1544 END Check_ListSrcType_FK_Items;
1545
1546 ---------------------------------------------------------------------
1547 -- PROCEDURE
1548 -- Check_ListSrcType_Record
1549 --
1550 -- PURPOSE
1551 -- Check the record level business rules.
1552 --
1553 -- PARAMETERS
1554 -- p_listsrctype_rec: the record to be validated; may contain attributes
1555 -- as FND_API.g_miss_char/num/date
1556 -- p_complete_rec: the complete record after all "g_miss" items
1557 -- have been replaced by current database values
1558 ---------------------------------------------------------------------
1559 PROCEDURE Check_ListSrcType_Lookup_Items (
1560 p_listsrctype_rec IN ListSourceType_Rec_Type,
1561 x_return_status OUT NOCOPY VARCHAR2
1562 )
1563 IS
1564 BEGIN
1565 x_return_status := FND_API.g_ret_sts_success;
1566 --------------------- view application_id ------------------------
1567 IF p_listsrctype_rec.view_application_id <> FND_API.g_miss_num THEN
1568 IF AMS_Utility_PVT.check_fk_exists(
1569 'fnd_application',
1570 'application_id',
1571 p_listsrctype_rec.view_application_id
1572 ) = FND_API.g_false
1573 THEN
1574 AMS_Utility_PVT.Error_Message('AMS_LIST_BAD_APPLICATION_ID');
1575 x_return_status := FND_API.g_ret_sts_error;
1576 RETURN;
1577 END IF;
1578 END IF;
1579
1580 END Check_ListSrcType_Lookup_Items;
1581
1582 ---------------------------------------------------------------------
1583 -- PROCEDURE
1584 -- Check_ListSrcType_Flag_Items
1585 --
1586 -- PURPOSE
1587 -- Check the record level business rules.
1588 --
1589 -- PARAMETERS
1590 -- p_listsrctype_rec: the record to be validated; may contain attributes
1591 -- as FND_API.g_miss_char/num/date
1592 -- p_complete_rec: the complete record after all "g_miss" items
1593 -- have been replaced by current database values
1594 ---------------------------------------------------------------------
1595 PROCEDURE Check_ListSrcType_Flag_Items (
1596 p_listsrctype_rec IN ListSourceType_Rec_Type,
1597 x_return_status OUT NOCOPY VARCHAR2
1598 )
1599 IS
1600 BEGIN
1601 x_return_status := FND_API.g_ret_sts_success;
1602
1603 -- master_source_type_flag
1604 IF p_listsrctype_rec.master_source_type_flag <> FND_API.g_miss_char AND p_listsrctype_rec.master_source_type_flag IS NOT NULL THEN
1605 IF AMS_Utility_PVT.is_Y_or_N (p_listsrctype_rec.master_source_type_flag) = FND_API.g_false THEN
1606 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1607 FND_MESSAGE.set_name ('AMS', 'AMS_LIST_BAD_MASTERSRC_FLAG');
1608 FND_MSG_PUB.add;
1609 END IF;
1610
1611 x_return_status := FND_API.g_ret_sts_error;
1612 RETURN;
1613 END IF;
1614 END IF;
1615
1616 -- enabled_flag
1617 IF p_listsrctype_rec.enabled_flag <> FND_API.g_miss_char AND p_listsrctype_rec.enabled_flag IS NOT NULL THEN
1618 IF AMS_Utility_PVT.is_Y_or_N (p_listsrctype_rec.enabled_flag) = FND_API.g_false THEN
1619 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1620 FND_MESSAGE.set_name ('AMS', 'AMS_LIST_BAD_ENABLED_FLAG');
1621 FND_MSG_PUB.add;
1622 END IF;
1623
1624 x_return_status := FND_API.g_ret_sts_error;
1625 RETURN;
1626 END IF;
1627 END IF;
1628 END Check_ListSrcType_Flag_Items;
1629
1630
1631 ---------------------------------------------------------------------
1632 -- PROCEDURE
1633 -- generate_source_fields
1634 --
1635 -- PURPOSE
1636 -- Generate the fields associated to the data source using the
1637 -- column name to derive the source column meaning.
1638 --
1639 -- NOTE
1640 -- Called by the data source create API for analytics type
1641 -- data sources.
1642 --
1643 -- DISTINCT added because Apps views are created multiple times
1644 -- in MRC environments. ODM only supports mining on numeric
1645 -- fields; character fields can be converted to numbers, but dates
1646 -- cannot - INCLUDE only NUMBER and VARCHAR2 fields.
1647 --
1648 -- ENABLED_FLAG is used by list generation to determine
1649 -- if a field is to be used for populating AMS_LIST_ENTRIES.
1650 -- Analytics uses the ANALYTICS_FLAG to determine if the
1651 -- field is to be used for data mining.
1652 --
1653 -- FIELD_TABLE_NAME and FIELD_COLUMN name are not populated
1654 -- by default. The fields are required when enabling
1655 -- (ENABLED_FLAG = Y) the analytics data source field for
1656 -- list generation.
1657 --
1658 -- PARAMETERS
1659 -- p_listsrctype_rec: the data source record
1660 -- p_validation_level: the API validation level
1661 -- x_return_status: standard return status out param
1662 ---------------------------------------------------------------------
1663 PROCEDURE generate_source_fields (
1664 p_listsrctype_rec IN ListSourceType_Rec_Type,
1665 p_validation_level IN NUMBER,
1666 x_return_status OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER,
1668 x_msg_data OUT NOCOPY VARCHAR2
1669 )
1670 IS
1671 L_DEFAULT_NUM_BUCKETS CONSTANT NUMBER := 10;
1672
1673 -- CURSOR c_source (p_source_name IN VARCHAR2, p_pk IN VARCHAR2) IS
1674 -- SELECT DISTINCT column_name, data_type
1675 -- FROM sys.all_tab_columns
1676 -- WHERE table_name = p_source_name
1677 -- AND column_name <> p_pk
1678 -- AND data_type IN ('NUMBER', 'VARCHAR2')
1679 -- ;
1680
1681 -- Modified by nyostos on Jan 27, 2003 to remove condition that field is not the primary key
1682 CURSOR c_source (p_source_name IN VARCHAR2) IS
1683 /* SELECT DISTINCT column_name, data_type -- Fix for bug 15876260
1684 FROM sys.all_tab_columns
1685 WHERE table_name = p_source_name
1686 AND data_type IN ('NUMBER', 'VARCHAR2') ; */
1687
1688 SELECT column_name, data_type
1689 FROM user_synonyms syn, dba_tab_columns col
1690 WHERE syn.synonym_name = p_source_name
1691 AND col.owner = syn.table_owner
1692 AND col.table_name = syn.table_name
1693 AND data_type IN ('NUMBER', 'VARCHAR2')
1694 order by col.column_id;
1695
1696 l_field_rec AMS_List_Src_Field_PVT.list_src_field_rec_type;
1697 l_list_source_field_id NUMBER;
1698 BEGIN
1699 SAVEPOINT generate_source_fields;
1700
1701 x_return_status := FND_API.g_ret_sts_success;
1702
1703 l_field_rec.list_source_type_id := p_listsrctype_rec.list_source_type_id;
1704 l_field_rec.de_list_source_type_code := p_listsrctype_rec.source_type_code;
1705 l_field_rec.enabled_flag := 'N'; -- list gen flag
1706 l_field_rec.analytics_flag := 'Y';
1707 l_field_rec.auto_binning_flag := 'Y';
1708 l_field_rec.no_of_buckets := L_DEFAULT_NUM_BUCKETS;
1709
1710 -- Modified by nyostos on Jan 27, 2003 to remove condition that field is not the primary key
1711 -- FOR l_source_rec IN c_source (p_listsrctype_rec.source_object_name, p_listsrctype_rec.source_object_pk_field) LOOP
1712 FOR l_source_rec IN c_source (p_listsrctype_rec.source_object_name) LOOP
1713 l_field_rec.source_column_name := l_source_rec.column_name;
1714 -- convert underscores (_) to spaces and make initial caps.
1715 -- Example: COLUMN_NAME becomes "Column Name"
1716 l_field_rec.source_column_meaning := INITCAP (REPLACE (l_source_rec.column_name, '_', ' '));
1717 l_field_rec.field_data_type := l_source_rec.data_type;
1718
1719 AMS_List_Src_Field_PVT.Create_List_Src_Field (
1720 p_api_version_number => 1.0,
1721 p_init_msg_list => FND_API.G_FALSE,
1722 p_commit => FND_API.G_FALSE,
1723 p_validation_level => p_validation_level,
1724 x_return_status => x_return_status,
1725 x_msg_count => x_msg_count,
1726 x_msg_data => x_msg_data,
1727 p_list_src_field_rec => l_field_rec,
1728 x_list_source_field_id => l_list_source_field_id
1729 );
1730 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1731 RAISE FND_API.G_EXC_ERROR;
1732 END IF;
1733 END LOOP;
1734 EXCEPTION
1735 WHEN FND_API.G_EXC_ERROR THEN
1736 ROLLBACK TO generate_source_fields;
1737 x_return_status := FND_API.G_RET_STS_ERROR;
1738 -- Standard call to get message count and if count=1, get the message
1739 FND_MSG_PUB.Count_And_Get (
1740 p_encoded => FND_API.G_FALSE,
1741 p_count => x_msg_count,
1742 p_data => x_msg_data
1743 );
1744 END generate_source_fields;
1745
1746
1747 END AMS_ListSourceType_PVT;