1 PACKAGE BODY AMS_DM_TARGET_PVT as
2 /* $Header: amsvdtgb.pls 120.0 2005/05/31 19:31:20 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_DM_TARGET_PVT
7 -- Purpose
8 --
9 -- History
10 -- 10-Apr-2002 nyostos Created.
11 -- 30-Jan-2003 nyostos Changed target name uniqueness code.
12 -- 12-Feb-2004 rosharma Bug # 3436093.
13 --
14 -- NOTE
15 --
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_DM_TARGET_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdtgb.pls';
22
23 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
24 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
25
26
27 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
28 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
29 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
30
31 PROCEDURE Complete_dm_target_Rec (
32 p_dm_target_rec IN dm_target_rec_type,
33 x_complete_rec OUT NOCOPY dm_target_rec_type
34 );
35
36
37 -- Hint: Primary key needs to be returned.
38 PROCEDURE Create_Dmtarget(
39 p_api_version_number IN NUMBER,
40 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
41 p_commit IN VARCHAR2 := FND_API.G_FALSE,
42 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
43
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47
48 p_dm_target_rec IN dm_target_rec_type := g_miss_dm_target_rec,
49 x_target_id OUT NOCOPY NUMBER
50 )
51
52 IS
53 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Dmtarget';
54 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
55 l_return_status_full VARCHAR2(1);
56 l_object_version_number NUMBER := 1;
57 l_org_id NUMBER := FND_API.G_MISS_NUM;
58 l_TARGET_ID NUMBER;
59 l_dummy NUMBER;
60 l_dm_target_rec AMS_DM_TARGET_PVT.dm_target_rec_type := p_dm_target_rec;
61
62 CURSOR c_id IS
63 SELECT AMS_DM_TARGETS_B_s.NEXTVAL
64 FROM dual;
65
66 CURSOR c_id_exists (l_id IN NUMBER) IS
67 SELECT 1
68 FROM AMS_DM_TARGETS_VL
69 WHERE TARGET_ID = l_id;
70
71 BEGIN
72 -- Standard Start of API savepoint
73 SAVEPOINT CREATE_Dmtarget_PVT;
74
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
77 p_api_version_number,
78 l_api_name,
79 G_PKG_NAME)
80 THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90 -- Debug Message
91 IF (AMS_DEBUG_HIGH_ON) THEN
92
93 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
94 END IF;
95
96
97 -- Initialize API return status to SUCCESS
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 -- Local variable initialization
101
102 IF l_dm_target_rec.TARGET_ID IS NULL OR l_dm_target_rec.TARGET_ID = FND_API.g_miss_num THEN
103 LOOP
104 l_dummy := NULL;
105 OPEN c_id;
106 FETCH c_id INTO l_dm_target_rec.TARGET_ID;
107 CLOSE c_id;
108
109 OPEN c_id_exists(l_dm_target_rec.TARGET_ID);
110 FETCH c_id_exists INTO l_dummy;
111 CLOSE c_id_exists;
112
113 EXIT WHEN l_dummy IS NULL;
114 END LOOP;
115 END IF;
116
117 IF (AMS_DEBUG_HIGH_ON) THEN
118
119
120
121 AMS_UTILITY_PVT.debug_message( l_api_name || ' New Target ID to Insert = ' || l_dm_target_rec.TARGET_ID );
122
123 END IF;
124
125 -- =========================================================================
126 -- Validate Environment
127 -- =========================================================================
128
129 IF FND_GLOBAL.User_Id IS NULL THEN
130 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133
134 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
135 THEN
136 -- Invoke validation procedures
137 Validate_dmtarget(
138 p_api_version_number => 1.0,
139 p_init_msg_list => FND_API.G_FALSE,
140 p_validation_level => p_validation_level,
141 p_validation_mode => JTF_PLSQL_API.g_create,
142 p_dm_target_rec => l_dm_target_rec,
143 x_return_status => x_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data);
146 END IF;
147
148 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
149 RAISE FND_API.G_EXC_ERROR;
150 END IF;
151
152
153 -- Debug Message
154 IF (AMS_DEBUG_HIGH_ON) THEN
155
156 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
157 END IF;
158
159 -- Invoke table handler(AMS_DM_TARGETS_B_PKG.Insert_Row)
160 AMS_DM_TARGETS_B_PKG.Insert_Row(
161 px_target_id => l_dm_target_rec.target_id,
162 p_last_update_date => SYSDATE,
163 p_last_updated_by => G_USER_ID,
164 p_creation_date => SYSDATE,
165 p_created_by => G_USER_ID,
166 p_last_update_login => G_LOGIN_ID,
167 px_object_version_number => l_object_version_number,
168 p_active_flag => l_dm_target_rec.active_flag,
169 p_model_type => l_dm_target_rec.model_type,
170 p_data_source_id => l_dm_target_rec.data_source_id,
171 p_source_field_id => l_dm_target_rec.source_field_id,
172 p_target_name => l_dm_target_rec.target_name,
173 p_description => l_dm_target_rec.description,
174 p_target_source_id => l_dm_target_rec.target_source_id
175 );
176
177 x_target_id := l_dm_target_rec.target_id;
178
179 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182
183 /* obsoleted rosharma 11.5.10 for Audience data source uptake
184 -- After successfully inserting the Target, enable the data source
185 -- associated with it if the target is active
186 IF l_dm_target_rec.active_flag = 'Y' THEN
187 update ams_list_src_types
188 set enabled_flag = 'Y'
189 where list_source_type_id = l_dm_target_rec.data_source_id;
190 END IF;
191 */
192
193 --
194 -- End of API body
195 --
196
197 -- Standard check for p_commit
198 IF FND_API.to_Boolean( p_commit ) THEN
199 COMMIT WORK;
200 END IF;
201
202
203 -- Debug Message
204 IF (AMS_DEBUG_HIGH_ON) THEN
205
206 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
207 END IF;
208
209 -- Standard call to get message count and if count is 1, get message info.
210 FND_MSG_PUB.Count_And_Get
211 (p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214 EXCEPTION
215
216 WHEN AMS_Utility_PVT.resource_locked THEN
217 x_return_status := FND_API.g_ret_sts_error;
218 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
219
220 WHEN FND_API.G_EXC_ERROR THEN
221 ROLLBACK TO CREATE_Dmtarget_PVT;
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 -- Standard call to get message count and if count=1, get the message
224 FND_MSG_PUB.Count_And_Get (
225 p_encoded => FND_API.G_FALSE,
226 p_count => x_msg_count,
227 p_data => x_msg_data
228 );
229
230 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
231 ROLLBACK TO CREATE_Dmtarget_PVT;
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 -- Standard call to get message count and if count=1, get the message
234 FND_MSG_PUB.Count_And_Get (
235 p_encoded => FND_API.G_FALSE,
236 p_count => x_msg_count,
237 p_data => x_msg_data
238 );
239
240 WHEN OTHERS THEN
241 ROLLBACK TO CREATE_Dmtarget_PVT;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
244 THEN
245 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
246 END IF;
247 -- Standard call to get message count and if count=1, get the message
248 FND_MSG_PUB.Count_And_Get (
249 p_encoded => FND_API.G_FALSE,
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253 End Create_Dmtarget;
254
255
256 PROCEDURE Update_Dmtarget(
257 p_api_version_number IN NUMBER,
258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
260 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
261
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2,
265
266 p_dm_target_rec IN dm_target_rec_type,
267 x_object_version_number OUT NOCOPY NUMBER
268 )
269
270 IS
271 CURSOR c_get_dmtarget(p_target_id IN NUMBER) IS
272 SELECT *
273 FROM AMS_DM_TARGETS_VL
274 WHERE target_id = p_target_id;
275
276 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Dmtarget';
277 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
278
279 -- Local Variables
280 l_object_version_number NUMBER;
281 l_TARGET_ID NUMBER;
282 l_ref_dm_target_rec c_get_Dmtarget%ROWTYPE ;
283 l_tar_dm_target_rec AMS_DM_TARGET_PVT.dm_target_rec_type := P_dm_target_rec;
284 l_rowid ROWID;
285
286
287 BEGIN
288 -- Standard Start of API savepoint
289 SAVEPOINT UPDATE_Dmtarget_PVT;
290
291 -- Standard call to check for call compatibility.
292 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
293 p_api_version_number,
294 l_api_name,
295 G_PKG_NAME)
296 THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Initialize message list if p_init_msg_list is set to TRUE.
301 IF FND_API.to_Boolean( p_init_msg_list )
302 THEN
303 FND_MSG_PUB.initialize;
304 END IF;
305
306 -- Debug Message
307 IF (AMS_DEBUG_HIGH_ON) THEN
308
309 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
310 END IF;
311
312
313 -- Initialize API return status to SUCCESS
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 -- Complete missing entries in the record before updating
317 Complete_dm_target_Rec(
318 p_dm_target_rec => p_dm_target_rec,
319 x_complete_rec => l_tar_dm_target_rec
320 );
321
322 -- Debug Message
323 IF (AMS_DEBUG_HIGH_ON) THEN
324
325 AMS_UTILITY_PVT.debug_message('Private API: - Open Target Reference Cursor');
326 END IF;
327
328 -- get the reference target, which contains
329 -- data before the update operation.
330 OPEN c_get_Dmtarget( l_tar_dm_target_rec.target_id);
331 FETCH c_get_Dmtarget INTO l_ref_dm_target_rec ;
332
333 IF ( c_get_Dmtarget%NOTFOUND) THEN
334 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET', p_token_name => 'INFO', p_token_value => 'Dmtarget') ;
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337
338 -- Debug Message
339 IF (AMS_DEBUG_HIGH_ON) THEN
340
341 AMS_UTILITY_PVT.debug_message('Private API: - Close Target Reference Cursor');
342 END IF;
343 CLOSE c_get_Dmtarget;
344
345 IF (l_tar_dm_target_rec.object_version_number is NULL or
346 l_tar_dm_target_rec.object_version_number = FND_API.G_MISS_NUM ) Then
347 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING', p_token_name => 'COLUMN', p_token_value => 'Last_Update_Date') ;
348 raise FND_API.G_EXC_ERROR;
349 End if;
350
351 -- Check Whether record has been changed by someone else
352 IF (l_tar_dm_target_rec.object_version_number <> l_ref_dm_target_rec.object_version_number) Then
353 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED', p_token_name => 'INFO', p_token_value => 'Dmtarget') ;
354 raise FND_API.G_EXC_ERROR;
355 End if;
356
357 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
358 THEN
359
360 -- Invoke validation procedures
361 Validate_dmtarget(
362 p_api_version_number => 1.0,
363 p_init_msg_list => FND_API.G_FALSE,
364 p_validation_level => p_validation_level,
365 p_validation_mode => JTF_PLSQL_API.g_update,
366 p_dm_target_rec => l_tar_dm_target_rec,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data);
370 END IF;
371
372 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
373 RAISE FND_API.G_EXC_ERROR;
374 END IF;
375
376
377 -- Debug Message
378 IF (AMS_DEBUG_HIGH_ON) THEN
379
380 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
381 END IF;
382 IF (AMS_DEBUG_HIGH_ON) THEN
383
384 AMS_UTILITY_PVT.debug_message('Updating .........');
385 END IF;
386 IF (AMS_DEBUG_HIGH_ON) THEN
387
388 AMS_UTILITY_PVT.debug_message('p_target_id = ' || p_dm_target_rec.target_id);
389 END IF;
390 IF (AMS_DEBUG_HIGH_ON) THEN
391
392 AMS_UTILITY_PVT.debug_message('p_last_update_date = ' || SYSDATE);
393 END IF;
394 IF (AMS_DEBUG_HIGH_ON) THEN
395
396 AMS_UTILITY_PVT.debug_message('p_last_updated_by = ' || G_USER_ID);
397 END IF;
398 IF (AMS_DEBUG_HIGH_ON) THEN
399
400 AMS_UTILITY_PVT.debug_message('p_creation_date = ' || SYSDATE);
401 END IF;
402 IF (AMS_DEBUG_HIGH_ON) THEN
403
404 AMS_UTILITY_PVT.debug_message('p_created_by = ' || G_USER_ID);
405 END IF;
406 IF (AMS_DEBUG_HIGH_ON) THEN
407
408 AMS_UTILITY_PVT.debug_message('p_last_update_login = ' || G_LOGIN_ID);
409 END IF;
410 IF (AMS_DEBUG_HIGH_ON) THEN
411
412 AMS_UTILITY_PVT.debug_message('p_object_version_number = ' || l_tar_dm_target_rec.object_version_number);
413 END IF;
414 IF (AMS_DEBUG_HIGH_ON) THEN
415
416 AMS_UTILITY_PVT.debug_message('p_active_flag = ' || l_tar_dm_target_rec.active_flag);
417 END IF;
418 IF (AMS_DEBUG_HIGH_ON) THEN
419
420 AMS_UTILITY_PVT.debug_message('p_model_type = ' || l_tar_dm_target_rec.model_type);
421 END IF;
422 IF (AMS_DEBUG_HIGH_ON) THEN
423
424 AMS_UTILITY_PVT.debug_message('p_data_source_id = ' || l_tar_dm_target_rec.data_source_id);
425 END IF;
426 IF (AMS_DEBUG_HIGH_ON) THEN
427
428 AMS_UTILITY_PVT.debug_message('p_source_field_id = ' || l_tar_dm_target_rec.source_field_id);
429 END IF;
430 IF (AMS_DEBUG_HIGH_ON) THEN
431
432 AMS_UTILITY_PVT.debug_message('p_target_name = ' || l_tar_dm_target_rec.target_name);
433 END IF;
434 IF (AMS_DEBUG_HIGH_ON) THEN
435
436 AMS_UTILITY_PVT.debug_message('p_description = ' || l_tar_dm_target_rec.description);
437 END IF;
438
439 BEGIN
440 -- Invoke table handler(AMS_DM_TARGETS_B_PKG.Update_Row)
441 AMS_DM_TARGETS_B_PKG.Update_Row(
442 p_target_id => l_tar_dm_target_rec.target_id,
443 p_last_update_date => SYSDATE,
444 p_last_updated_by => G_USER_ID,
445 p_creation_date => SYSDATE,
446 p_created_by => G_USER_ID,
447 p_last_update_login => G_LOGIN_ID,
448 p_object_version_number => l_tar_dm_target_rec.object_version_number,
449 p_active_flag => l_tar_dm_target_rec.active_flag,
450 p_model_type => l_tar_dm_target_rec.model_type,
451 p_data_source_id => l_tar_dm_target_rec.data_source_id,
452 p_source_field_id => l_tar_dm_target_rec.source_field_id,
453 p_target_name => l_tar_dm_target_rec.target_name,
454 p_description => l_tar_dm_target_rec.description,
455 p_target_source_id => l_tar_dm_target_rec.target_source_id
456 );
457
458 EXCEPTION
459 WHEN NO_DATA_FOUND THEN
460 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
461 RAISE FND_API.G_EXC_ERROR;
462 END;
463
464 x_object_version_number := l_tar_dm_target_rec.object_version_number + 1;
465
466 --
467 -- End of API body.
468 --
469
470 -- Standard check for p_commit
471 IF FND_API.to_Boolean( p_commit )
472 THEN
473 COMMIT WORK;
474 END IF;
475
476
477 -- Debug Message
478 IF (AMS_DEBUG_HIGH_ON) THEN
479
480 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
481 END IF;
482
483 -- Standard call to get message count and if count is 1, get message info.
484 FND_MSG_PUB.Count_And_Get
485 (p_count => x_msg_count,
486 p_data => x_msg_data
487 );
488 EXCEPTION
489
490 WHEN AMS_Utility_PVT.resource_locked THEN
491 x_return_status := FND_API.g_ret_sts_error;
492 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
493
494 WHEN FND_API.G_EXC_ERROR THEN
495 ROLLBACK TO UPDATE_Dmtarget_PVT;
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 -- Standard call to get message count and if count=1, get the message
498 FND_MSG_PUB.Count_And_Get (
499 p_encoded => FND_API.G_FALSE,
500 p_count => x_msg_count,
501 p_data => x_msg_data
502 );
503
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505 ROLLBACK TO UPDATE_Dmtarget_PVT;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 -- Standard call to get message count and if count=1, get the message
508 FND_MSG_PUB.Count_And_Get (
509 p_encoded => FND_API.G_FALSE,
510 p_count => x_msg_count,
511 p_data => x_msg_data
512 );
513
514 WHEN OTHERS THEN
515 ROLLBACK TO UPDATE_Dmtarget_PVT;
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
518 THEN
519 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
520 END IF;
521 -- Standard call to get message count and if count=1, get the message
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 End Update_Dmtarget;
528
529
530 PROCEDURE Delete_Dmtarget(
531 p_api_version_number IN NUMBER,
532 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
533 p_commit IN VARCHAR2 := FND_API.G_FALSE,
534 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
535 x_return_status OUT NOCOPY VARCHAR2,
536 x_msg_count OUT NOCOPY NUMBER,
537 x_msg_data OUT NOCOPY VARCHAR2,
538 p_target_id IN NUMBER,
539 p_object_version_number IN NUMBER
540 )
541
542 IS
543 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Dmtarget';
544 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
545 l_object_version_number NUMBER;
546 l_no_of_models NUMBER;
547 l_no_of_active_targets NUMBER;
548 l_datasource_id NUMBER;
549
550 -- Cursor to check if target is used in any models
551 CURSOR c_target_used (l_id IN NUMBER) IS
552 SELECT count(*)
553 FROM AMS_DM_MODELS_VL
554 WHERE TARGET_ID = l_id;
555
556 -- Cursor to get the data source id for the target
557 CURSOR c_datasource_id (l_tgtId IN NUMBER) IS
558 SELECT data_source_id
559 FROM ams_dm_targets_vl
560 WHERE target_id = l_tgtId;
561
562 -- Cursor to count the active targets defined for a data source
563 CURSOR c_target_count (l_dsId IN NUMBER) IS
564 SELECT count(*)
565 FROM AMS_DM_TARGETS_VL
566 WHERE DATA_SOURCE_ID = l_dsId
567 AND ACTIVE_FLAG = 'Y';
568
569
570
571 BEGIN
572 -- Standard Start of API savepoint
573 SAVEPOINT DELETE_Dmtarget_PVT;
574
575 -- Standard call to check for call compatibility.
576 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
577 p_api_version_number,
578 l_api_name,
579 G_PKG_NAME)
580 THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583
584 -- Initialize message list if p_init_msg_list is set to TRUE.
585 IF FND_API.to_Boolean( p_init_msg_list )
586 THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589
590 -- Debug Message
591 IF (AMS_DEBUG_HIGH_ON) THEN
592
593 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
594 END IF;
595
596
597 -- Initialize API return status to SUCCESS
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599
600 -- Validate that the Target is not used by any model
601 OPEN c_target_used(p_target_id);
602 FETCH c_target_used INTO l_no_of_models;
603 CLOSE c_target_used;
604 IF l_no_of_models > 0 THEN
605 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_DM_TARGET_USED');
606 x_return_status := FND_API.g_ret_sts_error;
607 RAISE FND_API.G_EXC_ERROR;
608 END IF;
609
610 -- First Delete Target Values associated with this target (if any)
611 IF (AMS_DEBUG_HIGH_ON) THEN
612
613 AMS_UTILITY_PVT.debug_message( 'Private API: Going to Delete Target Values Associated with this Target ');
614 END IF;
615
616 AMS_Dm_Target_Value_PVT.Delete_TgtValues_For_Target ( p_TARGET_ID );
617
618 IF (AMS_DEBUG_HIGH_ON) THEN
619
620
621
622 AMS_UTILITY_PVT.debug_message( 'Private API: After Deleting Target Values Associated with this Target ');
623
624 END IF;
625
626 -- added rosharma for audience DS uptake
627 -- Delete Child Data Sources associated with this target (if any)
628 IF (AMS_DEBUG_HIGH_ON) THEN
629
630 AMS_UTILITY_PVT.debug_message( 'Private API: Going to Delete Child Data Sources Associated with this Target ');
631 END IF;
632
633 AMS_Dm_Target_Sources_PVT.delete_tgtsources_for_target ( p_TARGET_ID );
634
635 IF (AMS_DEBUG_HIGH_ON) THEN
636
637
638
639 AMS_UTILITY_PVT.debug_message( 'Private API: After Deleting Child Data Sources Associated with this Target ');
640
641 END IF;
642
643 /* obsoleted rosharma 11.5.10 for Audience data source uptake
644 -- Get the data source id for this target record before deleting it
645 OPEN c_datasource_id(p_TARGET_ID);
646 FETCH c_datasource_id INTO l_datasource_id;
647 CLOSE c_datasource_id;
648 */
649
650
651 --
652 -- Api body
653 --
654 -- Debug Message
655 IF (AMS_DEBUG_HIGH_ON) THEN
656
657 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
658 END IF;
659
660 -- Invoke table handler(AMS_DM_TARGETS_B_PKG.Delete_Row)
661 AMS_DM_TARGETS_B_PKG.Delete_Row(
662 p_TARGET_ID => p_TARGET_ID);
663
664 /* obsoleted rosharma 11.5.10 for Audience data source uptake
665 -- After successfully deleteing the target, check if there are no more
666 -- active targets for the associated datasource. If none exist, then disable the datasource.
667 OPEN c_target_count(l_datasource_id);
668 FETCH c_target_count INTO l_no_of_active_targets;
669 CLOSE c_target_count;
670
671 IF l_no_of_active_targets = 0 THEN
672 update ams_list_src_types
673 set enabled_flag = 'N'
674 where list_source_type_id = l_datasource_id;
675 END IF;
676 */
677
678 --
679 -- End of API body
680 --
681
682 -- Standard check for p_commit
683 IF FND_API.to_Boolean( p_commit )
684 THEN
685 COMMIT WORK;
686 END IF;
687
688
689 -- Debug Message
690 IF (AMS_DEBUG_HIGH_ON) THEN
691
692 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
693 END IF;
694
695 -- Standard call to get message count and if count is 1, get message info.
696 FND_MSG_PUB.Count_And_Get
697 (p_count => x_msg_count,
698 p_data => x_msg_data
699 );
700 EXCEPTION
701
702 WHEN AMS_Utility_PVT.resource_locked THEN
703 x_return_status := FND_API.g_ret_sts_error;
704 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
705
706 WHEN FND_API.G_EXC_ERROR THEN
707 ROLLBACK TO DELETE_Dmtarget_PVT;
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 -- Standard call to get message count and if count=1, get the message
710 FND_MSG_PUB.Count_And_Get (
711 p_encoded => FND_API.G_FALSE,
712 p_count => x_msg_count,
713 p_data => x_msg_data
714 );
715
716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
717 ROLLBACK TO DELETE_Dmtarget_PVT;
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
719 -- Standard call to get message count and if count=1, get the message
720 FND_MSG_PUB.Count_And_Get (
721 p_encoded => FND_API.G_FALSE,
722 p_count => x_msg_count,
723 p_data => x_msg_data
724 );
725
726 WHEN OTHERS THEN
727 ROLLBACK TO DELETE_Dmtarget_PVT;
728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
729 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
730 THEN
731 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
732 END IF;
733 -- Standard call to get message count and if count=1, get the message
734 FND_MSG_PUB.Count_And_Get (
735 p_encoded => FND_API.G_FALSE,
736 p_count => x_msg_count,
737 p_data => x_msg_data
738 );
739 End Delete_Dmtarget;
740
741
742
743 -- Hint: Primary key needs to be returned.
744 PROCEDURE Lock_Dmtarget(
745 p_api_version_number IN NUMBER,
746 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
747
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2,
751
752 p_target_id IN NUMBER,
753 p_object_version IN NUMBER
754 )
755
756 IS
757 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Dmtarget';
758 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
759 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
760 l_TARGET_ID NUMBER;
761
762 CURSOR c_Dmtarget IS
763 SELECT TARGET_ID
764 FROM AMS_DM_TARGETS_B
765 WHERE TARGET_ID = p_TARGET_ID
766 AND object_version_number = p_object_version
767 FOR UPDATE NOWAIT;
768
769 BEGIN
770
771 -- Debug Message
772 IF (AMS_DEBUG_HIGH_ON) THEN
773
774 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
775 END IF;
776
777 -- Initialize message list if p_init_msg_list is set to TRUE.
778 IF FND_API.to_Boolean( p_init_msg_list )
779 THEN
780 FND_MSG_PUB.initialize;
781 END IF;
782
783 -- Standard call to check for call compatibility.
784 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
785 p_api_version_number,
786 l_api_name,
787 G_PKG_NAME)
788 THEN
789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
790 END IF;
791
792
793 -- Initialize API return status to SUCCESS
794 x_return_status := FND_API.G_RET_STS_SUCCESS;
795
796
797 ------------------------ lock -------------------------
798
799 IF (AMS_DEBUG_HIGH_ON) THEN
800
801
802
803 AMS_Utility_PVT.debug_message(l_full_name||': start');
804
805 END IF;
806 OPEN c_Dmtarget;
807
808 FETCH c_Dmtarget INTO l_TARGET_ID;
809
810 IF (c_Dmtarget%NOTFOUND) THEN
811 CLOSE c_Dmtarget;
812 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
813 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
814 FND_MSG_PUB.add;
815 END IF;
816 RAISE FND_API.g_exc_error;
817 END IF;
818
819 CLOSE c_Dmtarget;
820
821 -------------------- finish --------------------------
822 FND_MSG_PUB.count_and_get(
823 p_encoded => FND_API.g_false,
824 p_count => x_msg_count,
825 p_data => x_msg_data);
826 IF (AMS_DEBUG_HIGH_ON) THEN
827
828 AMS_Utility_PVT.debug_message(l_full_name ||': end');
829 END IF;
830 EXCEPTION
831
832 WHEN AMS_Utility_PVT.resource_locked THEN
833 x_return_status := FND_API.g_ret_sts_error;
834 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
835
836 WHEN FND_API.G_EXC_ERROR THEN
837 ROLLBACK TO LOCK_Dmtarget_PVT;
838 x_return_status := FND_API.G_RET_STS_ERROR;
839 -- Standard call to get message count and if count=1, get the message
840 FND_MSG_PUB.Count_And_Get (
841 p_encoded => FND_API.G_FALSE,
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845
846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
847 ROLLBACK TO LOCK_Dmtarget_PVT;
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 -- Standard call to get message count and if count=1, get the message
850 FND_MSG_PUB.Count_And_Get (
851 p_encoded => FND_API.G_FALSE,
852 p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855
856 WHEN OTHERS THEN
857 ROLLBACK TO LOCK_Dmtarget_PVT;
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
859 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
860 THEN
861 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
862 END IF;
863 -- Standard call to get message count and if count=1, get the message
864 FND_MSG_PUB.Count_And_Get (
865 p_encoded => FND_API.G_FALSE,
866 p_count => x_msg_count,
867 p_data => x_msg_data
868 );
869 End Lock_Dmtarget;
870
871
872 PROCEDURE check_dm_target_uk_items(
873 p_dm_target_rec IN dm_target_rec_type,
874 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
875 x_return_status OUT NOCOPY VARCHAR2)
876 IS
877 l_valid_flag VARCHAR2(1);
878 l_number NUMBER;
879
880 CURSOR c_target_name
881 IS SELECT 1 from dual
882 WHERE EXISTS ( SELECT * from ams_dm_targets_vl
883 WHERE UPPER(target_name) = UPPER(p_dm_target_rec.target_name)) ;
884 CURSOR c_target_name_updt
885 IS SELECT 1 from dual
886 WHERE EXISTS ( SELECT * from ams_dm_targets_vl
887 WHERE UPPER(target_name) = UPPER(p_dm_target_rec.target_name)
888 AND target_id <> p_dm_target_rec.target_id );
889
890 l_dummy NUMBER ;
891
892 /* commented rosharma. not needed anymore for 11.5.10 and hence
893 CURSOR c_unique_values (l_dsId IN NUMBER, l_sfId IN NUMBER, l_modelType IN VARCHAR2, l_targetId IN NUMBER) IS
894 SELECT count(*)
895 FROM AMS_DM_TARGETS_VL
896 WHERE DATA_SOURCE_ID = l_dsId
897 AND SOURCE_FIELD_ID = l_sfId
898 AND MODEL_TYPE = l_modelType
899 AND TARGET_ID <> l_targetId;
900 */
901
902 BEGIN
903 IF (AMS_DEBUG_HIGH_ON) THEN
904
905 AMS_UTILITY_PVT.debug_message('Private API:check_dm_target_uk_items');
906 END IF;
907 x_return_status := FND_API.g_ret_sts_success;
908
909 --Validate unique target_id
910 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
911 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
912 'AMS_DM_TARGETS_B',
913 'TARGET_ID = ''' || p_dm_target_rec.TARGET_ID ||''''
914 );
915 ELSE
916 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
917 'AMS_DM_TARGETS_B',
918 'TARGET_ID = ''' || p_dm_target_rec.TARGET_ID ||
919 ''' AND TARGET_ID <> ' || p_dm_target_rec.TARGET_ID
920 );
921 END IF;
922
923 IF l_valid_flag = FND_API.g_false THEN
924 AMS_Utility_PVT.Error_Message('AMS_API_DUPLICATE_ID', 'ID_FIELD','TARGET_ID');
925 x_return_status := FND_API.g_ret_sts_error;
926 RETURN;
927 END IF;
928
929 --Validate unique target_name
930 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
931 OPEN c_target_name ;
932 FETCH c_target_name INTO l_dummy;
933 CLOSE c_target_name ;
934 ELSE
935 OPEN c_target_name_updt ;
936 FETCH c_target_name_updt INTO l_dummy;
937 CLOSE c_target_name_updt ;
938 END IF;
939
940 IF l_dummy IS NOT NULL THEN
941 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_DM_DUP_TARGET_NAME');
942 x_return_status := FND_API.g_ret_sts_error;
943 RETURN;
944 END IF;
945
946 /* commented rosharma. not needed anymore for 11.5.10 and hence
947 --Validate unique model_type + data_source_id + source_field_id combination
948 OPEN c_unique_values(p_dm_target_rec.data_source_id, p_dm_target_rec.source_field_id,
949 p_dm_target_rec.model_type, p_dm_target_rec.target_id);
950 FETCH c_unique_values INTO l_number;
951 CLOSE c_unique_values;
952 IF (AMS_DEBUG_HIGH_ON) THEN
953
954 AMS_UTILITY_PVT.debug_message('l_number ' || l_number);
955 END IF;
956 IF l_number > 0 THEN
957 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_DM_DUP_MODEL_TARGET');
958 x_return_status := FND_API.g_ret_sts_error;
959 RETURN;
960 END IF;
961 */
962
963
964
965 END check_dm_target_uk_items;
966
967 PROCEDURE check_dm_target_req_items(
968 p_dm_target_rec IN dm_target_rec_type,
969 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
970 x_return_status OUT NOCOPY VARCHAR2
971 )
972 IS
973 BEGIN
974 x_return_status := FND_API.g_ret_sts_success;
975
976 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
977
978 IF (AMS_DEBUG_HIGH_ON) THEN
979
980
981
982 ams_utility_pvt.debug_message('Private API:check_dm_target_req_items for CREATE');
983
984 END IF;
985
986 IF p_dm_target_rec.target_id = FND_API.g_miss_num OR p_dm_target_rec.target_id IS NULL THEN
987 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_ID');
988 x_return_status := FND_API.g_ret_sts_error;
989 END IF;
990 IF p_dm_target_rec.active_flag = FND_API.g_miss_char OR p_dm_target_rec.active_flag IS NULL THEN
991 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','ACTIVE_FLAG');
992 x_return_status := FND_API.g_ret_sts_error;
993 END IF;
994 IF p_dm_target_rec.model_type = FND_API.g_miss_char OR p_dm_target_rec.model_type IS NULL THEN
995 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','MODEL_TYPE');
996 x_return_status := FND_API.g_ret_sts_error;
997 END IF;
998 IF p_dm_target_rec.data_source_id = FND_API.g_miss_num OR p_dm_target_rec.data_source_id IS NULL THEN
999 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','DATA_SOURCE_ID');
1000 x_return_status := FND_API.g_ret_sts_error;
1001 END IF;
1002 IF p_dm_target_rec.source_field_id = FND_API.g_miss_num OR p_dm_target_rec.source_field_id IS NULL THEN
1003 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','SOURCE_FIELD_ID');
1004 x_return_status := FND_API.g_ret_sts_error;
1005 END IF;
1006 -- added rosharma for audience DS uptake
1007 IF p_dm_target_rec.target_source_id = FND_API.g_miss_num OR p_dm_target_rec.target_source_id IS NULL THEN
1008 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_SOURCE_ID');
1009 x_return_status := FND_API.g_ret_sts_error;
1010 END IF;
1011 ELSE
1012 IF (AMS_DEBUG_HIGH_ON) THEN
1013
1014 ams_utility_pvt.debug_message('Private API:check_dm_target_req_items for UPDATE');
1015 END IF;
1016
1017 IF p_dm_target_rec.target_id IS NULL THEN
1018 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_ID');
1019 x_return_status := FND_API.g_ret_sts_error;
1020 END IF;
1021 IF p_dm_target_rec.last_update_date IS NULL THEN
1022 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATE_DATE');
1023 x_return_status := FND_API.g_ret_sts_error;
1024 END IF;
1025 IF p_dm_target_rec.last_updated_by IS NULL THEN
1026 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATED_BY');
1027 x_return_status := FND_API.g_ret_sts_error;
1028 END IF;
1029 IF p_dm_target_rec.creation_date IS NULL THEN
1030 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','CREATION_DATE');
1031 x_return_status := FND_API.g_ret_sts_error;
1032 END IF;
1033 IF p_dm_target_rec.created_by IS NULL THEN
1034 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','CREATED_BY');
1035 x_return_status := FND_API.g_ret_sts_error;
1036 END IF;
1037 IF p_dm_target_rec.active_flag IS NULL THEN
1038 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','ACTIVE_FLAG');
1039 x_return_status := FND_API.g_ret_sts_error;
1040 END IF;
1041 IF p_dm_target_rec.model_type IS NULL THEN
1042 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','MODEL_TYPE');
1043 x_return_status := FND_API.g_ret_sts_error;
1044 END IF;
1045 IF p_dm_target_rec.data_source_id IS NULL THEN
1046 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','DATA_SOURCE_ID');
1047 x_return_status := FND_API.g_ret_sts_error;
1048 END IF;
1049 IF p_dm_target_rec.source_field_id IS NULL THEN
1050 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','SOURCE_FIELD_ID');
1051 x_return_status := FND_API.g_ret_sts_error;
1052 END IF;
1053 IF (p_dm_target_rec.object_version_number IS NULL OR p_dm_target_rec.object_version_number = FND_API.G_MISS_NUM) THEN
1054 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','OBJECT_VERSION_NUMBER');
1055 x_return_status := FND_API.g_ret_sts_error;
1056 END IF;
1057 -- added rosharma for audience DS uptake
1058 IF p_dm_target_rec.target_source_id = FND_API.g_miss_num OR p_dm_target_rec.target_source_id IS NULL THEN
1059 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_SOURCE_ID');
1060 x_return_status := FND_API.g_ret_sts_error;
1061 END IF;
1062 END IF;
1063
1064 END check_dm_target_req_items;
1065
1066 PROCEDURE check_dm_target_FK_items(
1067 p_dm_target_rec IN dm_target_rec_type,
1068 x_return_status OUT NOCOPY VARCHAR2
1069 )
1070 IS
1071 BEGIN
1072 IF (AMS_DEBUG_HIGH_ON) THEN
1073
1074 AMS_UTILITY_PVT.debug_message('Private API:check_dm_target_FK_items');
1075 END IF;
1076 x_return_status := FND_API.g_ret_sts_success;
1077
1078 --------------------data_source_id---------------------------
1079 IF p_dm_target_rec.data_source_id <> FND_API.g_miss_num THEN
1080 IF AMS_Utility_PVT.check_fk_exists(
1081 'ams_list_src_types',
1082 'list_source_type_id',
1083 p_dm_target_rec.data_source_id
1084 ) = FND_API.g_false
1085 THEN
1086 AMS_Utility_PVT.error_message ('AMS_API_INVALID_FK', 'COLUMN_NAME', 'DATA_SOURCE_ID');
1087 x_return_status := FND_API.g_ret_sts_error;
1088 END IF;
1089 END IF;
1090
1091
1092 --------------------source_field_id---------------------------
1093 IF p_dm_target_rec.source_field_id <> FND_API.g_miss_num THEN
1094 IF AMS_Utility_PVT.check_fk_exists(
1095 'ams_list_src_fields',
1096 'list_source_field_id',
1097 p_dm_target_rec.source_field_id
1098 ) = FND_API.g_false
1099 THEN
1100 AMS_Utility_PVT.error_message ('AMS_API_INVALID_FK', 'COLUMN_NAME', 'SOURCE_FIELD_ID');
1101 x_return_status := FND_API.g_ret_sts_error;
1102 END IF;
1103 END IF;
1104
1105
1106 -- added rosharma for audience DS uptake
1107 --------------------target_source_id---------------------------
1108 IF p_dm_target_rec.target_source_id <> FND_API.g_miss_num THEN
1109 IF AMS_Utility_PVT.check_fk_exists(
1110 'ams_list_src_types',
1111 'list_source_type_id',
1112 p_dm_target_rec.target_source_id
1113 ) = FND_API.g_false
1114 THEN
1115 AMS_Utility_PVT.error_message ('AMS_API_INVALID_FK', 'COLUMN_NAME', 'TARGET_SOURCE_ID');
1116 x_return_status := FND_API.g_ret_sts_error;
1117 END IF;
1118 END IF;
1119 END check_dm_target_FK_items;
1120
1121 PROCEDURE check_dm_target_Lookup_items(
1122 p_dm_target_rec IN dm_target_rec_type,
1123 x_return_status OUT NOCOPY VARCHAR2
1124 )
1125 IS
1126 BEGIN
1127 IF (AMS_DEBUG_HIGH_ON) THEN
1128
1129 AMS_UTILITY_PVT.debug_message('Private API:check_dm_target_Lookup_items');
1130 END IF;
1131 x_return_status := FND_API.g_ret_sts_success;
1132
1133 ----------------------- model_type ------------------------
1134 IF p_dm_target_rec.model_type <> FND_API.g_miss_char THEN
1135 IF AMS_Utility_PVT.check_lookup_exists(
1136 p_lookup_type => 'AMS_DM_MODEL_TYPE',
1137 p_lookup_code => p_dm_target_rec.model_type
1138 ) = FND_API.g_false
1139 THEN
1140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1141 THEN
1142 FND_MESSAGE.set_name('AMS', 'AMS_API_INVALID_LOOKUP');
1143 FND_MESSAGE.set_token ('LOOKUP_CODE', p_dm_target_rec.model_type);
1144 FND_MESSAGE.set_token ('COLUMN_NAME', 'MODEL_TYPE');
1145 FND_MSG_PUB.add;
1146 END IF;
1147
1148 x_return_status := FND_API.g_ret_sts_error;
1149 END IF;
1150 END IF;
1151
1152 END check_dm_target_Lookup_items;
1153
1154 PROCEDURE Check_dm_target_Items (
1155 P_dm_target_rec IN dm_target_rec_type,
1156 p_validation_mode IN VARCHAR2,
1157 x_return_status OUT NOCOPY VARCHAR2
1158 )
1159 IS
1160 BEGIN
1161
1162
1163 -- Check Items Uniqueness API calls
1164 check_dm_target_uk_items(
1165 p_dm_target_rec => p_dm_target_rec,
1166 p_validation_mode => p_validation_mode,
1167 x_return_status => x_return_status);
1168 IF x_return_status <> FND_API.g_ret_sts_success THEN
1169 RETURN;
1170 END IF;
1171
1172 -- Check Items Required/NOT NULL API calls
1173 check_dm_target_req_items(
1174 p_dm_target_rec => p_dm_target_rec,
1175 p_validation_mode => p_validation_mode,
1176 x_return_status => x_return_status);
1177 IF x_return_status <> FND_API.g_ret_sts_success THEN
1178 RETURN;
1179 END IF;
1180
1181 -- Check Items Foreign Keys API calls
1182 check_dm_target_FK_items(
1183 p_dm_target_rec => p_dm_target_rec,
1184 x_return_status => x_return_status);
1185 IF x_return_status <> FND_API.g_ret_sts_success THEN
1186 RETURN;
1187 END IF;
1188
1189 -- Check Items Lookups
1190 check_dm_target_Lookup_items(
1191 p_dm_target_rec => p_dm_target_rec,
1192 x_return_status => x_return_status);
1193 IF x_return_status <> FND_API.g_ret_sts_success THEN
1194 RETURN;
1195 END IF;
1196
1197 END Check_dm_target_Items;
1198
1199
1200
1201 PROCEDURE Complete_dm_target_Rec (
1202 p_dm_target_rec IN dm_target_rec_type,
1203 x_complete_rec OUT NOCOPY dm_target_rec_type)
1204 IS
1205 l_return_status VARCHAR2(1);
1206
1207 CURSOR c_complete IS
1208 SELECT *
1209 FROM ams_dm_targets_vl
1210 WHERE target_id = p_dm_target_rec.target_id;
1211 l_dm_target_rec c_complete%ROWTYPE;
1212
1213 BEGIN
1214 -- Debug Message
1215 IF (AMS_DEBUG_HIGH_ON) THEN
1216
1217 AMS_UTILITY_PVT.debug_message('Private API: Complete_dm_target_Rec start');
1218 END IF;
1219
1220 x_complete_rec := p_dm_target_rec;
1221
1222 OPEN c_complete;
1223 FETCH c_complete INTO l_dm_target_rec;
1224 CLOSE c_complete;
1225
1226 -- target_id
1227 IF p_dm_target_rec.target_id = FND_API.g_miss_num THEN
1228 x_complete_rec.target_id := l_dm_target_rec.target_id;
1229 END IF;
1230
1231 -- last_update_date
1232 IF p_dm_target_rec.last_update_date = FND_API.g_miss_date THEN
1233 x_complete_rec.last_update_date := l_dm_target_rec.last_update_date;
1234 END IF;
1235
1236 -- last_updated_by
1237 IF p_dm_target_rec.last_updated_by = FND_API.g_miss_num THEN
1238 x_complete_rec.last_updated_by := l_dm_target_rec.last_updated_by;
1239 END IF;
1240
1241 -- creation_date
1242 IF p_dm_target_rec.creation_date = FND_API.g_miss_date THEN
1243 x_complete_rec.creation_date := l_dm_target_rec.creation_date;
1244 END IF;
1245
1246 -- created_by
1247 IF p_dm_target_rec.created_by = FND_API.g_miss_num THEN
1248 x_complete_rec.created_by := l_dm_target_rec.created_by;
1249 END IF;
1250
1251 -- last_update_login
1252 IF p_dm_target_rec.last_update_login = FND_API.g_miss_num THEN
1253 x_complete_rec.last_update_login := l_dm_target_rec.last_update_login;
1254 END IF;
1255
1256 -- object_version_number
1257 IF p_dm_target_rec.object_version_number = FND_API.g_miss_num THEN
1258 x_complete_rec.object_version_number := l_dm_target_rec.object_version_number;
1259 END IF;
1260
1261 -- active_flag
1262 IF p_dm_target_rec.active_flag = FND_API.g_miss_char THEN
1263 x_complete_rec.active_flag := l_dm_target_rec.active_flag;
1264 END IF;
1265
1266 -- model_type
1267 IF p_dm_target_rec.model_type = FND_API.g_miss_char THEN
1268 x_complete_rec.model_type := l_dm_target_rec.model_type;
1269 END IF;
1270
1271 -- data_source_id
1272 IF p_dm_target_rec.data_source_id = FND_API.g_miss_num THEN
1273 x_complete_rec.data_source_id := l_dm_target_rec.data_source_id;
1274 END IF;
1275
1276 -- source_field_id
1277 IF p_dm_target_rec.source_field_id = FND_API.g_miss_num THEN
1278 x_complete_rec.source_field_id := l_dm_target_rec.source_field_id;
1279 END IF;
1280
1281 -- target_source_id
1282 IF p_dm_target_rec.target_source_id = FND_API.g_miss_num THEN
1283 x_complete_rec.target_source_id := l_dm_target_rec.target_source_id;
1284 END IF;
1285
1286 -- Note: Developers need to modify the procedure
1287 -- to handle any business specific requirements.
1288
1289 -- Debug Message
1290 IF (AMS_DEBUG_HIGH_ON) THEN
1291
1292 AMS_UTILITY_PVT.debug_message('Private API: Complete_dm_target_Rec end');
1293 END IF;
1294
1295 END Complete_dm_target_Rec;
1296
1297 PROCEDURE Validate_dmtarget(
1298 p_api_version_number IN NUMBER,
1299 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1300 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1301 p_dm_target_rec IN dm_target_rec_type,
1302 p_validation_mode IN VARCHAR2,
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 x_msg_count OUT NOCOPY NUMBER,
1305 x_msg_data OUT NOCOPY VARCHAR2
1306 )
1307 IS
1308 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Dmtarget';
1309 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1310 l_object_version_number NUMBER;
1311 l_dm_target_rec AMS_DM_TARGET_PVT.dm_target_rec_type;
1312
1313 BEGIN
1314 -- Standard Start of API savepoint
1315 SAVEPOINT VALIDATE_Dmtarget_;
1316
1317 -- Debug Message
1318 IF (AMS_DEBUG_HIGH_ON) THEN
1319
1320 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1321 END IF;
1322
1323
1324 -- Standard call to check for call compatibility.
1325 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1326 p_api_version_number,
1327 l_api_name,
1328 G_PKG_NAME)
1329 THEN
1330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1331 END IF;
1332
1333 -- Initialize message list if p_init_msg_list is set to TRUE.
1334 IF FND_API.to_Boolean( p_init_msg_list )
1335 THEN
1336 FND_MSG_PUB.initialize;
1337 END IF;
1338
1339 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1340 Check_dm_target_Items(
1341 p_dm_target_rec => p_dm_target_rec,
1342 p_validation_mode => p_validation_mode,
1343 x_return_status => x_return_status
1344 );
1345
1346 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1347 RAISE FND_API.G_EXC_ERROR;
1348 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1350 END IF;
1351 END IF;
1352
1353 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1354 Validate_dm_target_Rec(
1355 p_api_version_number => 1.0,
1356 p_init_msg_list => FND_API.G_FALSE,
1357 x_return_status => x_return_status,
1358 x_msg_count => x_msg_count,
1359 x_msg_data => x_msg_data,
1360 p_dm_target_rec => l_dm_target_rec);
1361
1362 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1363 RAISE FND_API.G_EXC_ERROR;
1364 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1366 END IF;
1367 END IF;
1368
1369
1370 -- Initialize API return status to SUCCESS
1371 x_return_status := FND_API.G_RET_STS_SUCCESS;
1372
1373
1374 -- Debug Message
1375 IF (AMS_DEBUG_HIGH_ON) THEN
1376
1377 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1378 END IF;
1379
1380 -- Standard call to get message count and if count is 1, get message info.
1381 FND_MSG_PUB.Count_And_Get
1382 (p_count => x_msg_count,
1383 p_data => x_msg_data
1384 );
1385 EXCEPTION
1386
1387 WHEN AMS_Utility_PVT.resource_locked THEN
1388 x_return_status := FND_API.g_ret_sts_error;
1389 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1390
1391 WHEN FND_API.G_EXC_ERROR THEN
1392 ROLLBACK TO VALIDATE_Dmtarget_;
1393 x_return_status := FND_API.G_RET_STS_ERROR;
1394 -- Standard call to get message count and if count=1, get the message
1395 FND_MSG_PUB.Count_And_Get (
1396 p_encoded => FND_API.G_FALSE,
1397 p_count => x_msg_count,
1398 p_data => x_msg_data
1399 );
1400
1401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1402 ROLLBACK TO VALIDATE_Dmtarget_;
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404 -- Standard call to get message count and if count=1, get the message
1405 FND_MSG_PUB.Count_And_Get (
1406 p_encoded => FND_API.G_FALSE,
1407 p_count => x_msg_count,
1408 p_data => x_msg_data
1409 );
1410
1411 WHEN OTHERS THEN
1412 ROLLBACK TO VALIDATE_Dmtarget_;
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1415 THEN
1416 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1417 END IF;
1418 -- Standard call to get message count and if count=1, get the message
1419 FND_MSG_PUB.Count_And_Get (
1420 p_encoded => FND_API.G_FALSE,
1421 p_count => x_msg_count,
1422 p_data => x_msg_data
1423 );
1424 End Validate_Dmtarget;
1425
1426
1427 PROCEDURE Validate_dm_target_rec(
1428 p_api_version_number IN NUMBER,
1429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1430 x_return_status OUT NOCOPY VARCHAR2,
1431 x_msg_count OUT NOCOPY NUMBER,
1432 x_msg_data OUT NOCOPY VARCHAR2,
1433 p_dm_target_rec IN dm_target_rec_type
1434 )
1435 IS
1436 BEGIN
1437 -- Debug Message
1438 IF (AMS_DEBUG_HIGH_ON) THEN
1439
1440 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_target_rec');
1441 END IF;
1442
1443 -- Initialize message list if p_init_msg_list is set to TRUE.
1444 IF FND_API.to_Boolean( p_init_msg_list )
1445 THEN
1446 FND_MSG_PUB.initialize;
1447 END IF;
1448
1449 -- Initialize API return status to SUCCESS
1450 x_return_status := FND_API.G_RET_STS_SUCCESS;
1451
1452 -- Hint: Validate data
1453 -- If data not valid
1454 -- THEN
1455 -- x_return_status := FND_API.G_RET_STS_ERROR;
1456
1457 -- Standard call to get message count and if count is 1, get message info.
1458 FND_MSG_PUB.Count_And_Get
1459 (p_count => x_msg_count,
1460 p_data => x_msg_data
1461 );
1462 END Validate_dm_target_Rec;
1463
1464 --
1465 -- Purpose
1466 -- Validate access privileges of the selected
1467 -- target. Access privileges can include team
1468 -- based access.
1469 --
1470 PROCEDURE check_access (
1471 p_dm_target_rec IN dm_target_rec_type,
1472 p_validation_mode IN VARCHAR2,
1473 x_return_status OUT NOCOPY VARCHAR2
1474 );
1475
1476 --
1477 -- History
1478 PROCEDURE check_access (
1479 p_dm_target_rec IN dm_target_rec_type,
1480 p_validation_mode IN VARCHAR2,
1481 x_return_status OUT NOCOPY VARCHAR2
1482 )
1483 IS
1484 L_TARGET_QUALIFIER CONSTANT VARCHAR2(30) := 'TARGET';
1485 L_ACCESS_TYPE_USER CONSTANT VARCHAR2(30) := 'USER';
1486
1487 -- user id of the currently logged in user.
1488 l_owner_user_id NUMBER := AMS_Utility_PVT.get_resource_id (FND_GLOBAL.user_id);
1489 BEGIN
1490 -- Initialize API return status to SUCCESS
1491 x_return_status := FND_API.G_RET_STS_SUCCESS;
1492
1493 IF (AMS_DEBUG_HIGH_ON) THEN
1494
1495
1496
1497 ams_utility_pvt.debug_message ('qualifier: ' || l_target_qualifier || ' id: ' || p_dm_target_rec.target_id || ' resource: ' || l_owner_user_id);
1498
1499 END IF;
1500 -- validate access privileges
1501 IF AMS_Access_PVT.check_update_access (
1502 p_object_id => p_dm_target_rec.target_id,
1503 p_object_type => L_TARGET_QUALIFIER,
1504 p_user_or_role_id => l_owner_user_id,
1505 p_user_or_role_type => L_ACCESS_TYPE_USER) = 'N' THEN
1506 AMS_Utility_PVT.error_message ('AMS_TARGET_NO_UPDATE_ACCESS');
1507 x_return_status := FND_API.G_RET_STS_ERROR;
1508 END IF;
1509 END check_access;
1510
1511 -- ==============================================================================
1512 -- Start of Comments
1513 -- ==============================================================================
1514 -- API Name
1515 -- Handle_Data_Source_Disabling
1516 -- Type
1517 -- Private
1518 -- Pre-Req
1519 --
1520 -- Parameters
1521 --
1522 -- IN
1523 -- p_data_source_id IN NUMBER
1524 --
1525 -- Version : Current version 1.0
1526 --
1527 -- History
1528 --
1529 -- NOTE
1530 --
1531 -- End of Comments
1532 -- ==============================================================================
1533
1534 PROCEDURE Handle_Data_Source_Disabling(
1535 p_data_source_id IN NUMBER
1536 )
1537
1538 IS
1539 L_API_NAME CONSTANT VARCHAR2(30) := 'Handle_Data_Source_Disabling';
1540 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1541
1542 l_user_status_id NUMBER;
1543 l_status_code VARCHAR2(30);
1544
1545 CURSOR c_user_status_id (p_status_type IN VARCHAR2, p_status_code IN VARCHAR2) IS
1546 SELECT user_status_id
1547 FROM ams_user_statuses_b
1548 WHERE system_status_type = p_status_type
1549 AND system_status_code = p_status_code
1550 AND default_flag = 'Y'
1551 AND enabled_flag = 'Y'
1552 ;
1553
1554 CURSOR c_pass_status_code (p_user_status_id IN NUMBER) IS
1555 SELECT system_status_code
1556 FROM ams_user_statuses_vl
1557 WHERE user_status_id = p_user_status_id;
1558
1559 BEGIN
1560 UPDATE ams_dm_targets_b
1561 SET active_flag = 'N',
1562 last_update_date = SYSDATE,
1563 last_updated_by = FND_GLOBAL.user_id
1564 WHERE (data_source_id = p_data_source_id OR target_source_id = p_data_source_id)
1565 AND active_flag = 'Y'
1566 ;
1567
1568 OPEN c_user_status_id('AMS_DM_MODEL_STATUS' , 'INVALID');
1569 FETCH c_user_status_id INTO l_user_status_id;
1570 CLOSE c_user_status_id;
1571
1572 OPEN c_pass_status_code (l_user_status_id);
1573 FETCH c_pass_status_code INTO l_status_code;
1574 CLOSE c_pass_status_code;
1575
1576 UPDATE ams_dm_models_all_b a
1577 SET a.status_code = l_status_code,
1578 a.user_status_id = l_user_status_id,
1579 a.last_update_date = SYSDATE,
1580 a.last_updated_by = FND_GLOBAL.user_id,
1581 a.status_date = SYSDATE
1582 WHERE a.status_code = 'AVAILABLE'
1583 AND (EXISTS
1584 (SELECT 1
1585 from ams_dm_targets_b b
1586 where b.target_id=a.target_id
1587 and b.data_source_id=p_data_source_id)
1588 OR EXISTS
1589 (SELECT 1
1590 from ams_dm_target_sources c
1591 where c.target_id=a.target_id
1592 and c.data_source_id=p_data_source_id));
1593
1594 End Handle_Data_Source_Disabling;
1595
1596 -- ==============================================================================
1597 -- Start of Comments
1598 -- ==============================================================================
1599 -- API Name
1600 -- Handle_Data_Source_Enabling
1601 -- Type
1602 -- Private
1603 -- Pre-Req
1604 --
1605 -- Parameters
1606 --
1607 -- IN
1608 -- p_data_source_id IN NUMBER
1609 --
1610 -- Version : Current version 1.0
1611 --
1612 -- History
1613 --
1614 -- NOTE
1615 --
1616 -- End of Comments
1617 -- ==============================================================================
1618
1619 PROCEDURE Handle_Data_Source_Enabling(
1620 p_data_source_id IN NUMBER
1621 )
1622
1623 IS
1624 L_API_NAME CONSTANT VARCHAR2(30) := 'Handle_Data_Source_Enabling';
1625 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1626
1627 BEGIN
1628 UPDATE ams_dm_targets_b a
1629 SET a.active_flag = 'Y',
1630 a.last_update_date = SYSDATE,
1631 a.last_updated_by = FND_GLOBAL.user_id
1632 WHERE (a.data_source_id = p_data_source_id OR a.target_source_id = p_data_source_id)
1633 AND EXISTS (SELECT 1 FROM ams_list_src_types b WHERE b.list_source_type_id = a.data_source_id AND b.enabled_flag = 'Y')
1634 AND EXISTS (SELECT 1 FROM ams_list_src_types c WHERE c.list_source_type_id = a.target_source_id AND c.enabled_flag = 'Y')
1635 AND (EXISTS (SELECT 1 FROM ams_list_src_type_assocs d WHERE d.MASTER_SOURCE_TYPE_ID = a.data_source_id AND d.SUB_SOURCE_TYPE_ID = a.target_source_id AND d.enabled_flag = 'Y')
1636 OR a.data_source_id = a.target_source_id)
1637 AND EXISTS (SELECT 1 FROM ams_dm_target_values_b e where e.target_id = a.target_id)
1638 AND a.active_flag = 'N'
1639 ;
1640 End Handle_Data_Source_Enabling;
1641
1642 -- ==============================================================================
1643 -- Start of Comments
1644 -- ==============================================================================
1645 -- API Name
1646 -- Handle_DS_Assoc_Enabling
1647 -- Type
1648 -- Private
1649 -- Pre-Req
1650 --
1651 -- Parameters
1652 --
1653 -- IN
1654 -- p_master_source_id IN NUMBER
1655 -- p_sub_source_id IN NUMBER
1656 --
1657 -- Version : Current version 1.0
1658 --
1659 -- History
1660 --
1661 -- NOTE
1662 --
1663 -- End of Comments
1664 -- ==============================================================================
1665
1666 PROCEDURE Handle_DS_Assoc_Enabling(
1667 p_master_source_id IN NUMBER,
1668 p_sub_source_id IN NUMBER
1669 )
1670 IS
1671 L_API_NAME CONSTANT VARCHAR2(30) := 'Handle_DS_Assoc_Enabling';
1672 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1673
1674 BEGIN
1675 UPDATE ams_dm_targets_b a
1676 SET a.active_flag = 'Y',
1677 a.last_update_date = SYSDATE,
1678 a.last_updated_by = FND_GLOBAL.user_id
1679 WHERE (a.data_source_id = p_master_source_id AND a.target_source_id = p_sub_source_id)
1680 AND EXISTS (SELECT 1 FROM ams_list_src_types b WHERE b.list_source_type_id = a.data_source_id AND b.enabled_flag = 'Y')
1681 AND EXISTS (SELECT 1 FROM ams_list_src_types c WHERE c.list_source_type_id = a.target_source_id AND c.enabled_flag = 'Y')
1682 AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs d WHERE d.MASTER_SOURCE_TYPE_ID = a.data_source_id AND d.SUB_SOURCE_TYPE_ID = a.target_source_id AND d.enabled_flag = 'Y')
1683 AND EXISTS (SELECT 1 FROM ams_dm_target_values_b e where e.target_id = a.target_id)
1684 AND a.active_flag = 'N'
1685 ;
1686 End Handle_DS_Assoc_Enabling;
1687
1688 -- ==============================================================================
1689 -- Start of Comments
1690 -- ==============================================================================
1691 -- API Name
1692 -- Handle_DS_Assoc_Disabling
1693 -- Type
1694 -- Private
1695 -- Pre-Req
1696 --
1697 -- Parameters
1698 --
1699 -- IN
1700 -- p_master_source_id IN NUMBER
1701 -- p_sub_source_id IN NUMBER
1702 --
1703 -- Version : Current version 1.0
1704 --
1705 -- History
1706 --
1707 -- NOTE
1708 --
1709 -- End of Comments
1710 -- ==============================================================================
1711
1712 PROCEDURE Handle_DS_Assoc_Disabling(
1713 p_master_source_id IN NUMBER,
1714 p_sub_source_id IN NUMBER
1715 )
1716 IS
1717 L_API_NAME CONSTANT VARCHAR2(30) := 'Handle_DS_Assoc_Disabling';
1718 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1719
1720 l_user_status_id NUMBER;
1721 l_status_code VARCHAR2(30);
1722
1723 CURSOR c_user_status_id (p_status_type IN VARCHAR2, p_status_code IN VARCHAR2) IS
1724 SELECT user_status_id
1725 FROM ams_user_statuses_b
1726 WHERE system_status_type = p_status_type
1727 AND system_status_code = p_status_code
1728 AND default_flag = 'Y'
1729 AND enabled_flag = 'Y'
1730 ;
1731
1732 CURSOR c_pass_status_code (p_user_status_id IN NUMBER) IS
1733 SELECT system_status_code
1734 FROM ams_user_statuses_vl
1735 WHERE user_status_id = p_user_status_id;
1736
1737 BEGIN
1738 UPDATE ams_dm_targets_b
1739 SET active_flag = 'N',
1740 last_update_date = SYSDATE,
1741 last_updated_by = FND_GLOBAL.user_id
1742 WHERE (data_source_id = p_master_source_id AND target_source_id = p_sub_source_id)
1743 AND active_flag = 'Y'
1744 ;
1745
1746 OPEN c_user_status_id('AMS_DM_MODEL_STATUS' , 'INVALID');
1747 FETCH c_user_status_id INTO l_user_status_id;
1748 CLOSE c_user_status_id;
1749
1750 OPEN c_pass_status_code (l_user_status_id);
1751 FETCH c_pass_status_code INTO l_status_code;
1752 CLOSE c_pass_status_code;
1753
1754 UPDATE ams_dm_models_all_b a
1755 SET a.status_code = l_status_code,
1756 a.user_status_id = l_user_status_id,
1757 a.last_update_date = SYSDATE,
1758 a.last_updated_by = FND_GLOBAL.user_id,
1759 a.status_date = SYSDATE
1760 WHERE a.status_code = 'AVAILABLE'
1761 AND (EXISTS
1762 (SELECT 1
1763 from ams_dm_targets_b b
1764 where b.target_id=a.target_id
1765 and b.data_source_id=p_master_source_id)
1766 OR EXISTS
1767 (SELECT 1
1768 from ams_dm_target_sources c
1769 where c.target_id=a.target_id
1770 and c.data_source_id=p_sub_source_id));
1771
1772 DELETE FROM ams_dm_target_sources
1773 WHERE target_id IN (SELECT target_id FROM ams_dm_targets_b WHERE data_source_id = p_master_source_id AND target_source_id <> p_sub_source_id)
1774 AND data_source_id = p_sub_source_id
1775 ;
1776
1777 End Handle_DS_Assoc_Disabling;
1778
1779 PROCEDURE is_target_enabled(
1780 p_target_id IN NUMBER,
1781 x_is_enabled OUT NOCOPY BOOLEAN
1782 )
1783 IS
1784 L_API_NAME CONSTANT VARCHAR2(30) := 'is_target_enabled';
1785
1786 l_target_status VARCHAR2(1);
1787
1788 CURSOR c_target_status(p_tgt_id IN NUMBER) IS
1789 SELECT active_flag from ams_dm_targets_b where target_id = p_tgt_id;
1790
1791 BEGIN
1792 x_is_enabled := FALSE;
1793
1794 OPEN c_target_status(p_target_id);
1795 FETCH c_target_status INTO l_target_status;
1796 CLOSE c_target_status;
1797
1798 IF l_target_status = 'Y' THEN
1799 x_is_enabled := TRUE;
1800 END IF;
1801 END is_target_enabled;
1802
1803 -- ==============================================================================
1804 -- Start of Comments
1805 -- ==============================================================================
1806 -- API Name
1807 -- in_list
1808 -- Type
1809 -- Public
1810 -- Pre-Req
1811 --
1812 -- Parameters
1813 --
1814 -- IN
1815 -- p_string IN VARCHAR2 Required
1816 --
1817 -- OUT
1818 -- None
1819 --
1820 -- Version : Current version 1.0
1821 -- History
1822 -- 11-May-2005 srivikri Created. Fix for bug 4360174
1823 --
1824 -- This function is used for binding a list of numbers in an IN clause.
1825 -- The parameter p_string contains a list of numbers separated by "," (comma)
1826 -- The function parses the list of numbers and returns a PL/SQL table of
1827 -- data type NUMBER.
1828 -- This function is used in java/mining/DataSourceFieldsLOV.java
1829 --
1830 -- End of Comments
1831 -- ==============================================================================
1832 --
1833 FUNCTION in_list ( p_string IN VARCHAR2 ) RETURN JTF_NUMBER_TABLE
1834 IS
1835 l_table JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1836 l_string LONG DEFAULT p_string || ',';
1837 l_num NUMBER;
1838 BEGIN
1839
1840 LOOP
1841 EXIT WHEN l_string IS NULL;
1842 l_table.extend;
1843 l_num := instr( l_string, ',' );
1844 l_table( l_table.count ) := substr( l_string, 1, l_num-1 );
1845 l_string := substr( l_string, l_num+1 );
1846 END LOOP;
1847 RETURN l_table;
1848 END in_list;
1849
1850 END AMS_DM_TARGET_PVT;
[Home] [Help]