[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_TARGET_SOURCES_PVT
Source
1 PACKAGE BODY AMS_Dm_Target_Sources_PVT as
2 /* $Header: amsvdtsb.pls 115.6 2004/06/16 12:27:36 rosharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Dm_Target_Sources_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Dm_Target_Sources_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdtsb.pls';
22
23 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
24 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
25
26 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
27 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
29 --
30 -- Foreward Procedure Declarations
31 --
32
33 PROCEDURE Default_Target_Source_Items (
34 p_target_source_rec_type_rec IN target_source_rec_type ,
35 x_target_source_rec_type_rec OUT NOCOPY target_source_rec_type
36 ) ;
37
38
39
40 -- Hint: Primary key needs to be returned.
41 -- ==============================================================================
42 -- Start of Comments
43 -- ==============================================================================
44 -- API Name
45 -- Create_Dm_Target_Sources
46 -- Type
47 -- Private
48 -- Pre-Req
49 --
50 -- Parameters
51 --
52 -- IN
53 -- p_api_version_number IN NUMBER Required
54 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
55 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
56 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
57 -- p_target_source_rec_type_rec IN target_source_rec_type Required
58 --
59 -- OUT
60 -- x_return_status OUT NOCOPY VARCHAR2
61 -- x_msg_count OUT NOCOPY NUMBER
62 -- x_msg_data OUT NOCOPY VARCHAR2
63 -- Version : Current version 1.0
64 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
65 -- and basic operation, developer must manually add parameters and business logic as necessary.
66 --
67 -- History
68 --
69 -- NOTE
70 --
71 -- End of Comments
72 -- ==============================================================================
73
74 PROCEDURE Create_Dm_Target_Sources(
75 p_api_version_number IN NUMBER,
76 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
77 p_commit IN VARCHAR2 := FND_API.G_FALSE,
78 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
79
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2,
83
84 p_target_source_rec_type_rec IN target_source_rec_type := g_miss_target_source_rec,
85 x_target_source_id OUT NOCOPY NUMBER
86 )
87
88 IS
89 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Dm_Target_Sources';
90 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
91 l_return_status_full VARCHAR2(1);
92 l_object_version_number NUMBER := 1;
93 l_org_id NUMBER := FND_API.G_MISS_NUM;
94 l_target_source_rec target_source_rec_type := p_target_source_rec_type_rec;
95 l_dummy NUMBER;
96 CURSOR c_id IS
97 SELECT ams_dm_target_sources_s.NEXTVAL
98 FROM dual;
99
100 CURSOR c_id_exists (l_id IN NUMBER) IS
101 SELECT 1
102 FROM AMS_DM_TARGET_SOURCES
103 WHERE target_source_id = l_id;
104 BEGIN
105 -- Standard Start of API savepoint
106 SAVEPOINT create_dm_target_sources_pvt;
107
108 -- Standard call to check for call compatibility.
109 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
110 p_api_version_number,
111 l_api_name,
112 G_PKG_NAME)
113 THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117
118 -- Initialize message list if p_init_msg_list is set to TRUE.
119 IF FND_API.to_Boolean( p_init_msg_list )
120 THEN
121 FND_MSG_PUB.initialize;
122 END IF;
123
124
125
126 -- Debug Message
127 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
128
129
130
131 -- Initialize API return status to SUCCESS
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 -- =========================================================================
135 -- Validate Environment
136 -- =========================================================================
137
138 IF FND_GLOBAL.USER_ID IS NULL
139 THEN
140 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
141 RAISE FND_API.G_EXC_ERROR;
142 END IF;
143
144
145 -- Local variable initialization
146
147 IF l_target_source_rec.target_source_id IS NULL OR l_target_source_rec.target_source_id = FND_API.g_miss_num THEN
148 LOOP
149 l_dummy := NULL;
150 OPEN c_id;
151 FETCH c_id INTO l_target_source_rec.target_source_id;
152 CLOSE c_id;
153
154 OPEN c_id_exists(l_target_source_rec.target_source_id);
155 FETCH c_id_exists INTO l_dummy;
156 CLOSE c_id_exists;
157 EXIT WHEN l_dummy IS NULL;
158 END LOOP;
159 END IF;
160
161 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
162 THEN
163 -- Debug message
164 AMS_UTILITY_PVT.debug_message('Private API: Validate_Dm_Target_Sources');
165
166 -- Invoke validation procedures
167 Validate_dm_target_sources(
168 p_api_version_number => 1.0,
169 p_init_msg_list => FND_API.G_FALSE,
170 p_validation_level => p_validation_level,
171 p_validation_mode => JTF_PLSQL_API.g_create,
172 p_target_source_rec_type_rec => l_target_source_rec,
173 x_return_status => x_return_status,
174 x_msg_count => x_msg_count,
175 x_msg_data => x_msg_data);
176 END IF;
177
178 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
179 RAISE FND_API.G_EXC_ERROR;
180 END IF;
181
182
183 -- Debug Message
184 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
185
186 -- Invoke table handler(Ams_Dm_Target_Sources_Pkg.Insert_Row)
187 Ams_Dm_Target_Sources_Pkg.Insert_Row(
188 px_target_source_id => l_target_source_rec.target_source_id,
189 p_last_update_date => SYSDATE,
190 p_last_updated_by => FND_GLOBAL.USER_ID,
191 p_creation_date => SYSDATE,
192 p_created_by => FND_GLOBAL.USER_ID,
193 p_last_update_login => FND_GLOBAL.conc_login_id,
194 px_object_version_number => l_object_version_number,
195 p_target_id => l_target_source_rec.target_id,
196 p_data_source_id => l_target_source_rec.data_source_id
197 );
198
199 x_target_source_id := l_target_source_rec.target_source_id;
200 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203 --
204 -- End of API body
205 --
206
207 -- Standard check for p_commit
208 IF FND_API.to_Boolean( p_commit )
209 THEN
210 COMMIT WORK;
211 END IF;
212
213
214 -- Debug Message
215 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
216
217
218 -- Standard call to get message count and if count is 1, get message info.
219 FND_MSG_PUB.Count_And_Get
220 (p_count => x_msg_count,
221 p_data => x_msg_data
222 );
223 EXCEPTION
224
225 WHEN AMS_Utility_PVT.resource_locked THEN
226 x_return_status := FND_API.g_ret_sts_error;
227 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
228
229 WHEN FND_API.G_EXC_ERROR THEN
230 ROLLBACK TO CREATE_Dm_Target_Sources_PVT;
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 -- Standard call to get message count and if count=1, get the message
233 FND_MSG_PUB.Count_And_Get (
234 p_encoded => FND_API.G_FALSE,
235 p_count => x_msg_count,
236 p_data => x_msg_data
237 );
238
239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
240 ROLLBACK TO CREATE_Dm_Target_Sources_PVT;
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 -- Standard call to get message count and if count=1, get the message
243 FND_MSG_PUB.Count_And_Get (
244 p_encoded => FND_API.G_FALSE,
245 p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248
249 WHEN OTHERS THEN
250 ROLLBACK TO CREATE_Dm_Target_Sources_PVT;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
255 END IF;
256 -- Standard call to get message count and if count=1, get the message
257 FND_MSG_PUB.Count_And_Get (
258 p_encoded => FND_API.G_FALSE,
259 p_count => x_msg_count,
260 p_data => x_msg_data
261 );
262 End Create_Dm_Target_Sources;
263
264
265 -- ==============================================================================
266 -- Start of Comments
267 -- ==============================================================================
268 -- API Name
269 -- Update_Dm_Target_Sources
270 -- Type
271 -- Private
272 -- Pre-Req
273 --
274 -- Parameters
275 --
276 -- IN
277 -- p_api_version_number IN NUMBER Required
278 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
279 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
280 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
281 -- p_target_source_rec_type_rec IN target_source_rec_type Required
282 --
283 -- OUT
284 -- x_return_status OUT NOCOPY VARCHAR2
285 -- x_msg_count OUT NOCOPY NUMBER
286 -- x_msg_data OUT NOCOPY VARCHAR2
287 -- Version : Current version 1.0
288 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
289 -- and basic operation, developer must manually add parameters and business logic as necessary.
290 --
291 -- History
292 -- 15-Dec-2003 choang pass in new object ver number to table handler; table
293 -- handler to only do update
294 --
295 -- NOTE
296 --
297 -- End of Comments
298 -- ==============================================================================
299
300 PROCEDURE Update_Dm_Target_Sources(
301 p_api_version_number IN NUMBER,
302 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
303 p_commit IN VARCHAR2 := FND_API.G_FALSE,
304 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
305
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2,
309
310 p_target_source_rec_type_rec IN target_source_rec_type
311 )
312
313 IS
314
315
316 CURSOR c_get_dm_target_sources(p_target_source_id NUMBER) IS
317 SELECT *
318 FROM AMS_DM_TARGET_SOURCES
319 WHERE target_source_id = p_target_source_id;
320
321
322 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Dm_Target_Sources';
323 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
324 -- Local Variables
325 l_new_object_version_number NUMBER := p_target_source_rec_type_rec.object_version_number + 1;
326 l_target_source_id NUMBER;
327 l_ref_target_source_rec c_get_Dm_Target_Sources%ROWTYPE ;
328 l_tar_target_source_rec target_source_rec_type := P_target_source_rec_type_rec;
329 l_rowid ROWID;
330
331 BEGIN
332 -- Standard Start of API savepoint
333 SAVEPOINT update_dm_target_sources_pvt;
334
335 -- Standard call to check for call compatibility.
336 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
337 p_api_version_number,
338 l_api_name,
339 G_PKG_NAME)
340 THEN
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END IF;
343
344
345 -- Initialize message list if p_init_msg_list is set to TRUE.
346 IF FND_API.to_Boolean( p_init_msg_list )
347 THEN
348 FND_MSG_PUB.initialize;
349 END IF;
350
351
352
353 -- Debug Message
354 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
355
356
357
358 -- Initialize API return status to SUCCESS
359 x_return_status := FND_API.G_RET_STS_SUCCESS;
360
361 -- Debug Message
362 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
363
364 OPEN c_get_Dm_Target_Sources( l_tar_target_source_rec.target_source_id);
365
366 FETCH c_get_Dm_Target_Sources INTO l_ref_target_source_rec ;
367
368 If ( c_get_Dm_Target_Sources%NOTFOUND) THEN
369 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
370 p_token_name => 'INFO',
371 p_token_value => 'Dm_Target_Sources') ;
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374 -- Debug Message
378
375 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
376 CLOSE c_get_Dm_Target_Sources;
377
379 If (l_tar_target_source_rec.object_version_number is NULL or
380 l_tar_target_source_rec.object_version_number = FND_API.G_MISS_NUM ) Then
381 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
382 p_token_name => 'COLUMN',
383 p_token_value => 'Last_Update_Date') ;
384 raise FND_API.G_EXC_ERROR;
385 End if;
386 -- Check Whether record has been changed by someone else
387 If (l_tar_target_source_rec.object_version_number <> l_ref_target_source_rec.object_version_number) Then
388 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
389 p_token_name => 'INFO',
390 p_token_value => 'Dm_Target_Sources') ;
391 raise FND_API.G_EXC_ERROR;
392 End if;
393
394
395 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
396 THEN
397 -- Debug message
398 AMS_UTILITY_PVT.debug_message('Private API: Validate_Dm_Target_Sources');
399
400 -- Invoke validation procedures
401 Validate_dm_target_sources(
402 p_api_version_number => 1.0,
403 p_init_msg_list => FND_API.G_FALSE,
404 p_validation_level => p_validation_level,
405 p_validation_mode => JTF_PLSQL_API.g_update,
406 p_target_source_rec_type_rec => p_target_source_rec_type_rec,
407 x_return_status => x_return_status,
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data);
410 END IF;
411
412 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
413 RAISE FND_API.G_EXC_ERROR;
414 END IF;
415
416
417 -- Debug Message
418 AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
419
420 -- Invoke table handler(Ams_Dm_Target_Sources_Pkg.Update_Row)
421 Ams_Dm_Target_Sources_Pkg.Update_Row(
422 p_target_source_id => p_target_source_rec_type_rec.target_source_id,
423 p_last_update_date => SYSDATE,
424 p_last_updated_by => FND_GLOBAL.USER_ID,
425 p_last_update_login => FND_GLOBAL.conc_login_id,
426 p_object_version_number => l_new_object_version_number,
427 p_target_id => p_target_source_rec_type_rec.target_id,
428 p_data_source_id => p_target_source_rec_type_rec.data_source_id
429 );
430 --
431 -- End of API body.
432 --
433
434 -- Standard check for p_commit
435 IF FND_API.to_Boolean( p_commit )
436 THEN
437 COMMIT WORK;
438 END IF;
439
440
441 -- Debug Message
442 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
443
444
445 -- Standard call to get message count and if count is 1, get message info.
446 FND_MSG_PUB.Count_And_Get
447 (p_count => x_msg_count,
448 p_data => x_msg_data
449 );
450 EXCEPTION
451
452 WHEN AMS_Utility_PVT.resource_locked THEN
453 x_return_status := FND_API.g_ret_sts_error;
454 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
455
456 WHEN FND_API.G_EXC_ERROR THEN
457 ROLLBACK TO UPDATE_Dm_Target_Sources_PVT;
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 -- Standard call to get message count and if count=1, get the message
460 FND_MSG_PUB.Count_And_Get (
461 p_encoded => FND_API.G_FALSE,
462 p_count => x_msg_count,
463 p_data => x_msg_data
464 );
465
466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
467 ROLLBACK TO UPDATE_Dm_Target_Sources_PVT;
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 -- Standard call to get message count and if count=1, get the message
470 FND_MSG_PUB.Count_And_Get (
471 p_encoded => FND_API.G_FALSE,
472 p_count => x_msg_count,
473 p_data => x_msg_data
474 );
475
476 WHEN OTHERS THEN
477 ROLLBACK TO UPDATE_Dm_Target_Sources_PVT;
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
480 THEN
481 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
482 END IF;
483 -- Standard call to get message count and if count=1, get the message
484 FND_MSG_PUB.Count_And_Get (
485 p_encoded => FND_API.G_FALSE,
486 p_count => x_msg_count,
487 p_data => x_msg_data
488 );
489 End Update_Dm_Target_Sources;
490
491
492 -- ==============================================================================
493 -- Start of Comments
494 -- ==============================================================================
495 -- API Name
496 -- Delete_Dm_Target_Sources
497 -- Type
498 -- Private
499 -- Pre-Req
500 --
501 -- Parameters
502 --
503 -- IN
504 -- p_api_version_number IN NUMBER Required
505 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
506 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
507 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
508 -- p_target_source_id IN NUMBER
509 -- p_object_version_number IN NUMBER Optional Default = NULL
510 --
511 -- OUT
512 -- x_return_status OUT NOCOPY VARCHAR2
516 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
513 -- x_msg_count OUT NOCOPY NUMBER
514 -- x_msg_data OUT NOCOPY VARCHAR2
515 -- Version : Current version 1.0
517 -- and basic operation, developer must manually add parameters and business logic as necessary.
518 --
519 -- History
520 --
521 -- NOTE
522 --
523 -- End of Comments
524 -- ==============================================================================
525
526 PROCEDURE Delete_Dm_Target_Sources(
527 p_api_version_number IN NUMBER,
528 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
529 p_commit IN VARCHAR2 := FND_API.G_FALSE,
530 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
531 x_return_status OUT NOCOPY VARCHAR2,
532 x_msg_count OUT NOCOPY NUMBER,
533 x_msg_data OUT NOCOPY VARCHAR2,
534 p_target_source_id IN NUMBER,
535 p_object_version_number IN NUMBER
536 )
537
538 IS
539 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Dm_Target_Sources';
540 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
541 l_object_version_number NUMBER;
542
543 CURSOR c_target_source (p_target_source_id IN NUMBER)
544 IS SELECT 1 FROM AMS_DM_TARGETS_B t, AMS_DM_TARGET_SOURCES s
545 WHERE s.target_source_id = p_target_source_id
546 AND s.target_id = t.target_id
547 AND s.data_source_id = t.target_source_id;
548
549 l_dummy NUMBER ;
550
551 BEGIN
552 -- Standard Start of API savepoint
553 SAVEPOINT delete_dm_target_sources_pvt;
554
555 -- Standard call to check for call compatibility.
556 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
557 p_api_version_number,
558 l_api_name,
559 G_PKG_NAME)
560 THEN
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF;
563
564
565 -- Initialize message list if p_init_msg_list is set to TRUE.
566 IF FND_API.to_Boolean( p_init_msg_list )
567 THEN
568 FND_MSG_PUB.initialize;
569 END IF;
570
571
572
573 -- Debug Message
574 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
575
576
577
578 -- Initialize API return status to SUCCESS
579 x_return_status := FND_API.G_RET_STS_SUCCESS;
580
581 OPEN c_target_source(p_target_source_id) ;
582 FETCH c_target_source INTO l_dummy;
583 CLOSE c_target_source ;
584
585 IF l_dummy IS NOT NULL THEN
586 AMS_Utility_PVT.error_message ('AMS_DM_CHLD_DS_DEL_ERR');
587 RAISE FND_API.G_EXC_ERROR;
588 END IF;
589 --
590 -- Api body
591 --
592 -- Debug Message
593 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
594
595 -- Invoke table handler(Ams_Dm_Target_Sources_Pkg.Delete_Row)
596 Ams_Dm_Target_Sources_Pkg.Delete_Row(
597 p_target_source_id => p_target_source_id,
598 p_object_version_number => p_object_version_number );
599 --
600 -- End of API body
601 --
602
603 -- Standard check for p_commit
604 IF FND_API.to_Boolean( p_commit )
605 THEN
606 COMMIT WORK;
607 END IF;
608
609
610 -- Debug Message
611 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
612
613
614 -- Standard call to get message count and if count is 1, get message info.
615 FND_MSG_PUB.Count_And_Get
616 (p_count => x_msg_count,
617 p_data => x_msg_data
618 );
619 EXCEPTION
620
621 WHEN AMS_Utility_PVT.resource_locked THEN
622 x_return_status := FND_API.g_ret_sts_error;
623 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
624
625 WHEN FND_API.G_EXC_ERROR THEN
626 ROLLBACK TO DELETE_Dm_Target_Sources_PVT;
627 x_return_status := FND_API.G_RET_STS_ERROR;
628 -- Standard call to get message count and if count=1, get the message
629 FND_MSG_PUB.Count_And_Get (
630 p_encoded => FND_API.G_FALSE,
631 p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634
635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
636 ROLLBACK TO DELETE_Dm_Target_Sources_PVT;
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
638 -- Standard call to get message count and if count=1, get the message
639 FND_MSG_PUB.Count_And_Get (
640 p_encoded => FND_API.G_FALSE,
641 p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644
645 WHEN OTHERS THEN
646 ROLLBACK TO DELETE_Dm_Target_Sources_PVT;
647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
648 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
649 THEN
650 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
651 END IF;
652 -- Standard call to get message count and if count=1, get the message
653 FND_MSG_PUB.Count_And_Get (
654 p_encoded => FND_API.G_FALSE,
655 p_count => x_msg_count,
656 p_data => x_msg_data
657 );
658 End Delete_Dm_Target_Sources;
659
660
661
662 -- Hint: Primary key needs to be returned.
666 -- API Name
663 -- ==============================================================================
664 -- Start of Comments
665 -- ==============================================================================
667 -- Lock_Dm_Target_Sources
668 -- Type
669 -- Private
670 -- Pre-Req
671 --
672 -- Parameters
673 --
674 -- IN
675 -- p_api_version_number IN NUMBER Required
676 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
677 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
678 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
679 -- p_target_source_rec_type_rec IN target_source_rec_type Required
680 --
681 -- OUT
682 -- x_return_status OUT NOCOPY VARCHAR2
683 -- x_msg_count OUT NOCOPY NUMBER
684 -- x_msg_data OUT NOCOPY VARCHAR2
685 -- Version : Current version 1.0
686 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
687 -- and basic operation, developer must manually add parameters and business logic as necessary.
688 --
689 -- History
690 --
691 -- NOTE
692 --
693 -- End of Comments
694 -- ==============================================================================
695
696 PROCEDURE Lock_Dm_Target_Sources(
697 p_api_version_number IN NUMBER,
698 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
699
700 x_return_status OUT NOCOPY VARCHAR2,
701 x_msg_count OUT NOCOPY NUMBER,
702 x_msg_data OUT NOCOPY VARCHAR2,
703
704 p_target_source_id IN NUMBER,
705 p_object_version IN NUMBER
706 )
707
708 IS
709 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Dm_Target_Sources';
710 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
711 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
712 l_target_source_id NUMBER;
713
714 BEGIN
715
716 -- Debug Message
717 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
718
719
720 -- Initialize message list if p_init_msg_list is set to TRUE.
721 IF FND_API.to_Boolean( p_init_msg_list )
722 THEN
723 FND_MSG_PUB.initialize;
724 END IF;
725
726
727
728 -- Standard call to check for call compatibility.
729 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
730 p_api_version_number,
731 l_api_name,
732 G_PKG_NAME)
733 THEN
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 END IF;
736
737
738
739 -- Initialize API return status to SUCCESS
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742
743 ------------------------ lock -------------------------
744 Ams_Dm_Target_Sources_Pkg.Lock_Row(l_target_source_id,p_object_version);
745
746
747 -------------------- finish --------------------------
748 FND_MSG_PUB.count_and_get(
749 p_encoded => FND_API.g_false,
750 p_count => x_msg_count,
751 p_data => x_msg_data);
752 AMS_Utility_PVT.debug_message(l_full_name ||': end');
753 EXCEPTION
754
755 WHEN AMS_Utility_PVT.resource_locked THEN
756 x_return_status := FND_API.g_ret_sts_error;
757 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
758
759 WHEN FND_API.G_EXC_ERROR THEN
760 ROLLBACK TO LOCK_Dm_Target_Sources_PVT;
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 -- Standard call to get message count and if count=1, get the message
763 FND_MSG_PUB.Count_And_Get (
764 p_encoded => FND_API.G_FALSE,
765 p_count => x_msg_count,
766 p_data => x_msg_data
767 );
768
769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770 ROLLBACK TO LOCK_Dm_Target_Sources_PVT;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 -- Standard call to get message count and if count=1, get the message
773 FND_MSG_PUB.Count_And_Get (
774 p_encoded => FND_API.G_FALSE,
775 p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778
779 WHEN OTHERS THEN
780 ROLLBACK TO LOCK_Dm_Target_Sources_PVT;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
783 THEN
784 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
785 END IF;
786 -- Standard call to get message count and if count=1, get the message
787 FND_MSG_PUB.Count_And_Get (
788 p_encoded => FND_API.G_FALSE,
789 p_count => x_msg_count,
790 p_data => x_msg_data
791 );
792 End Lock_Dm_Target_Sources;
793
794
795
796
797 PROCEDURE check_Target_Source_Uk_Items(
798 p_target_source_rec_type_rec IN target_source_rec_type,
799 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
800 x_return_status OUT NOCOPY VARCHAR2)
801 IS
802 l_valid_flag VARCHAR2(1);
803
804 BEGIN
805 x_return_status := FND_API.g_ret_sts_success;
806 IF p_validation_mode = JTF_PLSQL_API.g_create
807 AND p_target_source_rec_type_rec.target_source_id IS NOT NULL
808 THEN
809 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
813 END IF;
810 'ams_dm_target_sources',
811 'target_source_id = ''' || p_target_source_rec_type_rec.target_source_id ||''''
812 );
814
815 IF l_valid_flag = FND_API.g_false THEN
816 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_target_source_id_DUPLICATE');
817 x_return_status := FND_API.g_ret_sts_error;
818 END IF;
819
820 END check_Target_Source_Uk_Items;
821
822
823
824 PROCEDURE check_Target_Source_Req_Items(
825 p_target_source_rec_type_rec IN target_source_rec_type,
826 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
827 x_return_status OUT NOCOPY VARCHAR2
828 )
829 IS
830 BEGIN
831 x_return_status := FND_API.g_ret_sts_success;
832
833 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
834
835
836 IF p_target_source_rec_type_rec.target_source_id = FND_API.G_MISS_NUM OR p_target_source_rec_type_rec.target_source_id IS NULL THEN
837 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'TARGET_SOURCE_ID' );
838 x_return_status := FND_API.g_ret_sts_error;
839 END IF;
840
841
842 IF p_target_source_rec_type_rec.target_id = FND_API.G_MISS_NUM OR p_target_source_rec_type_rec.target_id IS NULL THEN
843 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'TARGET_ID' );
844 x_return_status := FND_API.g_ret_sts_error;
845 END IF;
846
847
848 IF p_target_source_rec_type_rec.data_source_id = FND_API.G_MISS_NUM OR p_target_source_rec_type_rec.data_source_id IS NULL THEN
849 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'DATA_SOURCE_ID' );
850 x_return_status := FND_API.g_ret_sts_error;
851 END IF;
852
853
854 ELSE
855
856
857 IF p_target_source_rec_type_rec.target_source_id = FND_API.G_MISS_NUM THEN
858 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'TARGET_SOURCE_ID' );
859 x_return_status := FND_API.g_ret_sts_error;
860 END IF;
861
862
863 IF p_target_source_rec_type_rec.target_id = FND_API.G_MISS_NUM THEN
864 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'TARGET_ID' );
865 x_return_status := FND_API.g_ret_sts_error;
866 END IF;
867
868
869 IF p_target_source_rec_type_rec.data_source_id = FND_API.G_MISS_NUM THEN
870 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD', 'DATA_SOURCE_ID' );
871 x_return_status := FND_API.g_ret_sts_error;
872 END IF;
873 END IF;
874
875 END check_Target_Source_Req_Items;
876
877
878
879 PROCEDURE check_Target_Source_Fk_Items(
880 p_target_source_rec_type_rec IN target_source_rec_type,
881 x_return_status OUT NOCOPY VARCHAR2
882 )
883 IS
884 BEGIN
885 IF (AMS_DEBUG_HIGH_ON) THEN
886
887 AMS_UTILITY_PVT.debug_message('Private API:check_Target_Source_Fk_Items');
888 END IF;
889 x_return_status := FND_API.g_ret_sts_success;
890
891 --------------------- data_source_id ------------------------
892 IF p_target_source_rec_type_rec.data_source_id <> FND_API.g_miss_num THEN
893 IF AMS_Utility_PVT.check_fk_exists(
894 'ams_list_src_types',
895 'LIST_SOURCE_TYPE_ID',
896 p_target_source_rec_type_rec.data_source_id
897 ) = FND_API.g_false
898 THEN
899 AMS_Utility_PVT.error_message ('AMS_API_INVALID_FK', 'COLUMN_NAME', 'DATA_SOURCE_ID');
900 x_return_status := FND_API.g_ret_sts_error;
901 END IF;
902 END IF;
903
904 --------------------- target_id ------------------------
905 IF p_target_source_rec_type_rec.target_id <> FND_API.g_miss_num THEN
906 IF AMS_Utility_PVT.check_fk_exists(
907 'ams_dm_targets_b',
908 'target_id',
909 p_target_source_rec_type_rec.target_id
910 ) = FND_API.g_false
911 THEN
912 AMS_Utility_PVT.error_message ('AMS_API_INVALID_FK', 'COLUMN_NAME', 'TARGET_ID');
913 x_return_status := FND_API.g_ret_sts_error;
914 END IF;
915 END IF;
916
917 END check_Target_Source_Fk_Items;
918
919
920
921 PROCEDURE check_Tgt_Src_Lookup_Items(
922 p_target_source_rec_type_rec IN target_source_rec_type,
923 x_return_status OUT NOCOPY VARCHAR2
924 )
925 IS
926 BEGIN
927 x_return_status := FND_API.g_ret_sts_success;
928
929 -- Enter custom code here
930
931 END check_Tgt_Src_Lookup_Items;
932
933
934
935 PROCEDURE Check_Target_Source_Items (
936 P_target_source_rec_type_rec IN target_source_rec_type,
937 p_validation_mode IN VARCHAR2,
938 x_return_status OUT NOCOPY VARCHAR2
939 )
940 IS
941 l_return_status VARCHAR2(1);
942 BEGIN
943
944 l_return_status := FND_API.g_ret_sts_success;
945 -- Check Items Uniqueness API calls
946
947 check_Target_Source_Uk_Items(
948 p_target_source_rec_type_rec => p_target_source_rec_type_rec,
949 p_validation_mode => p_validation_mode,
950 x_return_status => x_return_status);
951 IF x_return_status <> FND_API.g_ret_sts_success THEN
952 l_return_status := FND_API.g_ret_sts_error;
953 END IF;
954
955 -- Check Items Required/NOT NULL API calls
956
957 check_Target_Source_Req_Items(
958 p_target_source_rec_type_rec => p_target_source_rec_type_rec,
959 p_validation_mode => p_validation_mode,
960 x_return_status => x_return_status);
964 -- Check Items Foreign Keys API calls
961 IF x_return_status <> FND_API.g_ret_sts_success THEN
962 l_return_status := FND_API.g_ret_sts_error;
963 END IF;
965
966 check_Target_Source_Fk_Items(
967 p_target_source_rec_type_rec => p_target_source_rec_type_rec,
968 x_return_status => x_return_status);
969 IF x_return_status <> FND_API.g_ret_sts_success THEN
970 l_return_status := FND_API.g_ret_sts_error;
971 END IF;
972 -- Check Items Lookups
973
974 check_Tgt_Src_Lookup_Items(
975 p_target_source_rec_type_rec => p_target_source_rec_type_rec,
976 x_return_status => x_return_status);
977 IF x_return_status <> FND_API.g_ret_sts_success THEN
978 l_return_status := FND_API.g_ret_sts_error;
979 END IF;
980
981 x_return_status := l_return_status;
982
983 END Check_Target_Source_Items;
984
985
986
987
988
989 PROCEDURE Complete_Target_Source_Rec (
990 p_target_source_rec_type_rec IN target_source_rec_type,
991 x_complete_rec OUT NOCOPY target_source_rec_type)
992 IS
993 l_return_status VARCHAR2(1);
994
995 CURSOR c_complete IS
996 SELECT *
997 FROM ams_dm_target_sources
998 WHERE target_source_id = p_target_source_rec_type_rec.target_source_id;
999 l_target_source_rec_type_rec c_complete%ROWTYPE;
1000 BEGIN
1001 x_complete_rec := p_target_source_rec_type_rec;
1002
1003
1004 OPEN c_complete;
1005 FETCH c_complete INTO l_target_source_rec_type_rec;
1006 CLOSE c_complete;
1007
1008 -- target_source_id
1009 IF p_target_source_rec_type_rec.target_source_id IS NULL THEN
1010 x_complete_rec.target_source_id := l_target_source_rec_type_rec.target_source_id;
1011 END IF;
1012
1013 -- last_update_date
1014 IF p_target_source_rec_type_rec.last_update_date IS NULL THEN
1015 x_complete_rec.last_update_date := l_target_source_rec_type_rec.last_update_date;
1016 END IF;
1017
1018 -- last_updated_by
1019 IF p_target_source_rec_type_rec.last_updated_by IS NULL THEN
1020 x_complete_rec.last_updated_by := l_target_source_rec_type_rec.last_updated_by;
1021 END IF;
1022
1023 -- creation_date
1024 IF p_target_source_rec_type_rec.creation_date IS NULL THEN
1025 x_complete_rec.creation_date := l_target_source_rec_type_rec.creation_date;
1026 END IF;
1027
1028 -- created_by
1029 IF p_target_source_rec_type_rec.created_by IS NULL THEN
1030 x_complete_rec.created_by := l_target_source_rec_type_rec.created_by;
1031 END IF;
1032
1033 -- last_update_login
1034 IF p_target_source_rec_type_rec.last_update_login IS NULL THEN
1035 x_complete_rec.last_update_login := l_target_source_rec_type_rec.last_update_login;
1036 END IF;
1037
1038 -- object_version_number
1039 IF p_target_source_rec_type_rec.object_version_number IS NULL THEN
1040 x_complete_rec.object_version_number := l_target_source_rec_type_rec.object_version_number;
1041 END IF;
1042
1043 -- target_id
1044 IF p_target_source_rec_type_rec.target_id IS NULL THEN
1045 x_complete_rec.target_id := l_target_source_rec_type_rec.target_id;
1046 END IF;
1047
1048 -- data_source_id
1049 IF p_target_source_rec_type_rec.data_source_id IS NULL THEN
1050 x_complete_rec.data_source_id := l_target_source_rec_type_rec.data_source_id;
1051 END IF;
1052 -- Note: Developers need to modify the procedure
1053 -- to handle any business specific requirements.
1054 END Complete_Target_Source_Rec;
1055
1056
1057
1058
1059 PROCEDURE Default_Target_Source_Items ( p_target_source_rec_type_rec IN target_source_rec_type ,
1060 x_target_source_rec_type_rec OUT NOCOPY target_source_rec_type )
1061 IS
1062 l_target_source_rec_type_rec target_source_rec_type := p_target_source_rec_type_rec;
1063 BEGIN
1064 -- Developers should put their code to default the record type
1065 -- e.g. IF p_campaign_rec.status_code IS NULL
1066 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
1067 -- l_campaign_rec.status_code := 'NEW' ;
1068 -- END IF ;
1069 --
1070 NULL ;
1071 END;
1072
1073
1074
1075
1076 PROCEDURE Validate_Dm_Target_Sources(
1077 p_api_version_number IN NUMBER,
1078 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1079 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1080 p_target_source_rec_type_rec IN target_source_rec_type,
1081 p_validation_mode IN VARCHAR2,
1082 x_return_status OUT NOCOPY VARCHAR2,
1083 x_msg_count OUT NOCOPY NUMBER,
1084 x_msg_data OUT NOCOPY VARCHAR2
1085 )
1086 IS
1087 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Dm_Target_Sources';
1088 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1089 l_object_version_number NUMBER;
1090 l_target_source_rec_type_rec target_source_rec_type := p_target_source_rec_type_rec;
1091
1092 BEGIN
1093 -- Standard Start of API savepoint
1094 SAVEPOINT validate_dm_target_sources_;
1095
1096 -- Standard call to check for call compatibility.
1097 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1098 p_api_version_number,
1099 l_api_name,
1100 G_PKG_NAME)
1101 THEN
1102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1103 END IF;
1104
1105
1106 -- Initialize message list if p_init_msg_list is set to TRUE.
1107 IF FND_API.to_Boolean( p_init_msg_list )
1108 THEN
1112
1109 FND_MSG_PUB.initialize;
1110 END IF;
1111
1113 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1114 Check_Target_Source_Items(
1115 p_target_source_rec_type_rec => l_target_source_rec_type_rec,
1116 p_validation_mode => p_validation_mode,
1117 x_return_status => x_return_status
1118 );
1119
1120 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1121 RAISE FND_API.G_EXC_ERROR;
1122 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1124 END IF;
1125 END IF;
1126
1127 /*IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1128 Default_Target_Source_Items (p_target_source_rec_type_rec => p_target_source_rec_type_rec ,
1129 x_target_source_rec_type_rec => l_target_source_rec_type_rec) ;
1130 END IF ;
1131
1132
1133 Complete_Target_Source_Rec(
1134 p_target_source_rec_type_rec => l_target_source_rec_type_rec,
1135 x_complete_rec => l_target_source_rec_type_rec
1136 );*/
1137
1138 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1139 Validate_Target_Source_Rec(
1140 p_api_version_number => 1.0,
1141 p_init_msg_list => FND_API.G_FALSE,
1142 p_validation_mode => p_validation_mode,
1143 x_return_status => x_return_status,
1144 x_msg_count => x_msg_count,
1145 x_msg_data => x_msg_data,
1146 p_target_source_rec_type_rec => l_target_source_rec_type_rec);
1147
1148 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1149 RAISE FND_API.G_EXC_ERROR;
1150 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1152 END IF;
1153 END IF;
1154
1155
1156 -- Debug Message
1157 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1158
1159
1160
1161 -- Initialize API return status to SUCCESS
1162 x_return_status := FND_API.G_RET_STS_SUCCESS;
1163
1164
1165 -- Debug Message
1166 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1167
1168
1169 -- Standard call to get message count and if count is 1, get message info.
1170 FND_MSG_PUB.Count_And_Get
1171 (p_count => x_msg_count,
1172 p_data => x_msg_data
1173 );
1174 EXCEPTION
1175
1176 WHEN AMS_Utility_PVT.resource_locked THEN
1177 x_return_status := FND_API.g_ret_sts_error;
1178 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1179
1180 WHEN FND_API.G_EXC_ERROR THEN
1181 ROLLBACK TO VALIDATE_Dm_Target_Sources_;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 -- Standard call to get message count and if count=1, get the message
1184 FND_MSG_PUB.Count_And_Get (
1185 p_encoded => FND_API.G_FALSE,
1186 p_count => x_msg_count,
1187 p_data => x_msg_data
1188 );
1189
1190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1191 ROLLBACK TO VALIDATE_Dm_Target_Sources_;
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1193 -- Standard call to get message count and if count=1, get the message
1194 FND_MSG_PUB.Count_And_Get (
1195 p_encoded => FND_API.G_FALSE,
1196 p_count => x_msg_count,
1197 p_data => x_msg_data
1198 );
1199
1200 WHEN OTHERS THEN
1201 ROLLBACK TO VALIDATE_Dm_Target_Sources_;
1202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1203 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1204 THEN
1205 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1206 END IF;
1207 -- Standard call to get message count and if count=1, get the message
1208 FND_MSG_PUB.Count_And_Get (
1209 p_encoded => FND_API.G_FALSE,
1210 p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213 End Validate_Dm_Target_Sources;
1214
1215
1216 PROCEDURE Validate_Target_Source_Rec (
1217 p_api_version_number IN NUMBER,
1218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1219 p_validation_mode IN VARCHAR2,
1220 x_return_status OUT NOCOPY VARCHAR2,
1221 x_msg_count OUT NOCOPY NUMBER,
1222 x_msg_data OUT NOCOPY VARCHAR2,
1223 p_target_source_rec_type_rec IN target_source_rec_type
1224 )
1225 IS
1226 CURSOR c_check_underlying_obj(p_ds_id IN NUMBER, p_target_id IN NUMBER) IS
1227 SELECT 1
1228 FROM ams_dm_target_sources dts, ams_list_src_types lst1, ams_list_src_types lst2
1229 WHERE dts.target_id = p_target_id
1230 AND lst1.list_source_type_id = p_ds_id
1231 AND lst2.list_source_type_id = dts.data_source_id
1232 AND lst2.list_source_type_id <> p_ds_id
1233 AND lst1.source_object_name = lst2.source_object_name
1234 ;
1235
1236 l_dummy NUMBER := 0;
1237 BEGIN
1238 -- Initialize message list if p_init_msg_list is set to TRUE.
1239 IF FND_API.to_Boolean( p_init_msg_list )
1240 THEN
1241 FND_MSG_PUB.initialize;
1242 END IF;
1243
1244
1245
1246 -- Initialize API return status to SUCCESS
1247 x_return_status := FND_API.G_RET_STS_SUCCESS;
1248
1249 -- Hint: Validate data
1250 -- If data not valid
1251 -- THEN
1252 -- x_return_status := FND_API.G_RET_STS_ERROR;
1253
1254 -- Debug Message
1255 IF (AMS_DEBUG_HIGH_ON) THEN
1256 AMS_UTILITY_PVT.debug_message('Private API: Validate_Target_Source_Rec');
1257 END IF;
1258
1259 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1260
1261 OPEN c_check_underlying_obj(p_target_source_rec_type_rec.data_source_id, p_target_source_rec_type_rec.target_id);
1262 FETCH c_check_underlying_obj INTO l_dummy;
1263 CLOSE c_check_underlying_obj;
1264
1265 IF l_dummy <> 0 THEN
1266 AMS_Utility_PVT.error_message ('AMS_DM_CHILD_OBJ_EXISTS');
1267 x_return_status := FND_API.G_RET_STS_ERROR;
1268 END IF;
1269
1270 END IF;
1271 -- Standard call to get message count and if count is 1, get message info.
1272 FND_MSG_PUB.Count_And_Get
1273 (p_count => x_msg_count,
1274 p_data => x_msg_data
1275 );
1276 END Validate_Target_Source_Rec;
1277
1278 PROCEDURE delete_tgtsources_for_target ( p_target_id IN NUMBER)
1279 IS
1280 L_API_NAME CONSTANT VARCHAR2(30) := 'delete_tgtsources_for_target';
1281
1282 BEGIN
1283 -- Standard Start of API savepoint
1284 SAVEPOINT delete_tgtsources_for_target;
1285
1286 -- Debug Message
1287 IF (AMS_DEBUG_HIGH_ON) THEN
1288
1289 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1290 END IF;
1291
1292 --
1293 -- Api body
1294 --
1295 DELETE FROM ams_dm_target_sources
1296 WHERE TARGET_SOURCE_ID in (SELECT TARGET_SOURCE_ID FROM ams_dm_target_sources WHERE TARGET_ID = p_TARGET_ID);
1297
1298 --
1299 -- End of API body
1300 --
1301
1302 -- Debug Message
1303 IF (AMS_DEBUG_HIGH_ON) THEN
1304
1305 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1306 END IF;
1307
1308 EXCEPTION
1309
1310 WHEN AMS_Utility_PVT.resource_locked THEN
1311 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1312
1313 WHEN FND_API.G_EXC_ERROR THEN
1314 ROLLBACK TO delete_tgtsources_for_target;
1315
1316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1317 ROLLBACK TO delete_tgtsources_for_target;
1318
1319 WHEN OTHERS THEN
1320 ROLLBACK TO delete_tgtsources_for_target;
1321
1322 End delete_tgtsources_for_target;
1323
1324 END AMS_Dm_Target_Sources_PVT;