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