1 PACKAGE BODY AMS_Dm_Target_Value_PVT as
2 /* $Header: amsvdtvb.pls 115.9 2003/03/19 06:08:40 rosharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Dm_Target_Value_PVT
7 -- Purpose
8 --
9 -- History
10 -- 08-Oct-2002 nyostos Added value_condition column
11 -- 16-Oct-2002 choang Added target_operator and range_value, replacing value_condition
12 -- 28-Nov-2002 rosharma Added validation for numeric vs varchar target value depending on the field type
13 -- 17-Mar-2003 nyostos Added unqiueness check for target_operator + target_value in UPDATE mode.
14 -- Fix for bug 2853646.
15 -- 19-Feb-2003 rosharma Bug # 2853640
16 --
17 -- NOTE
18 --
19 -- End of Comments
20 -- ===============================================================
21
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_Dm_Target_Value_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdtvb.pls';
25
26 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
27 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
28
29
30 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
31 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
32 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
33
34 PROCEDURE Complete_dm_target_value_Rec (
35 p_dm_target_value_rec IN dm_target_value_rec_type,
36 x_complete_rec OUT NOCOPY dm_target_value_rec_type
37 );
38
39
40
41 -- Hint: Primary key needs to be returned.
42 PROCEDURE Create_Dm_Target_Value(
43 p_api_version_number IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_commit IN VARCHAR2 := FND_API.G_FALSE,
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51
52 p_dm_target_value_rec IN dm_target_value_rec_type := g_miss_dm_target_value_rec,
53 x_target_value_id OUT NOCOPY NUMBER
54 )
55
56 IS
57 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Dm_Target_Value';
58 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
59 l_return_status_full VARCHAR2(1);
60 l_object_version_number NUMBER := 1;
61 l_org_id NUMBER := FND_API.G_MISS_NUM;
62 l_TARGET_VALUE_ID NUMBER;
63 l_dummy NUMBER;
64 l_dm_target_value_rec AMS_Dm_Target_Value_PVT.dm_target_value_rec_type := p_dm_target_value_rec;
65 l_datasource_id NUMBER;
66
67 CURSOR c_id IS
68 SELECT AMS_DM_TARGET_VALUES_B_s.NEXTVAL
69 FROM dual;
70
71 CURSOR c_id_exists (l_id IN NUMBER) IS
72 SELECT 1
73 FROM AMS_DM_TARGET_VALUES_VL
74 WHERE TARGET_VALUE_ID = l_id;
75
76 -- Cursor to get the data source id for the target
77 CURSOR c_datasource_id (l_tgtId IN NUMBER) IS
78 SELECT data_source_id
79 FROM ams_dm_targets_vl
80 WHERE target_id = l_tgtId;
81
82
83 BEGIN
84 -- Standard Start of API savepoint
85 SAVEPOINT CREATE_Dm_Target_Value_PVT;
86
87 -- Standard call to check for call compatibility.
88 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
89 p_api_version_number,
90 l_api_name,
91 G_PKG_NAME)
92 THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END IF;
95
96 -- Initialize message list if p_init_msg_list is set to TRUE.
97 IF FND_API.to_Boolean( p_init_msg_list )
98 THEN
99 FND_MSG_PUB.initialize;
100 END IF;
101
102 -- Debug Message
103 IF (AMS_DEBUG_HIGH_ON) THEN
104
105 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
106 END IF;
107
108
109 -- Initialize API return status to SUCCESS
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 -- Local variable initialization
113 IF l_dm_target_value_rec.TARGET_VALUE_ID IS NULL OR l_dm_target_value_rec.TARGET_VALUE_ID = FND_API.g_miss_num THEN
114 LOOP
115 l_dummy := NULL;
116 OPEN c_id;
117 FETCH c_id INTO l_dm_target_value_rec.TARGET_VALUE_ID;
118 CLOSE c_id;
119
120 OPEN c_id_exists(l_dm_target_value_rec.TARGET_VALUE_ID);
121 FETCH c_id_exists INTO l_dummy;
122 CLOSE c_id_exists;
123
124 EXIT WHEN l_dummy IS NULL;
125 END LOOP;
126 END IF;
127
128 IF (AMS_DEBUG_HIGH_ON) THEN
129
130
131
132 AMS_UTILITY_PVT.debug_message( l_api_name || ' New Target Value ID to Insert = ' || l_dm_target_value_rec.TARGET_VALUE_ID);
133
134 END IF;
135
136 -- =========================================================================
137 -- Validate Environment
138 -- =========================================================================
139
140 IF FND_GLOBAL.User_Id IS NULL
141 THEN
142 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
147 THEN
148 -- Invoke validation procedures
149 Validate_dm_target_value(
150 p_api_version_number => 1.0,
151 p_init_msg_list => FND_API.G_FALSE,
152 p_validation_level => p_validation_level,
153 p_validation_mode => JTF_PLSQL_API.g_create,
154 p_dm_target_value_rec => l_dm_target_value_rec,
155 x_return_status => x_return_status,
156 x_msg_count => x_msg_count,
157 x_msg_data => x_msg_data);
158 END IF;
159
160 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
161 RAISE FND_API.G_EXC_ERROR;
162 END IF;
163
164
165 -- Debug Message
166 IF (AMS_DEBUG_HIGH_ON) THEN
167
168 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
169 END IF;
170
171 -- Invoke table handler(AMS_DM_TARGET_VALUES_B_PKG.Insert_Row)
172 AMS_DM_TARGET_VALUES_B_PKG.Insert_Row(
173 px_target_value_id => l_dm_target_value_rec.TARGET_VALUE_ID,
174 p_last_update_date => SYSDATE,
175 p_last_updated_by => G_USER_ID,
176 p_creation_date => SYSDATE,
177 p_created_by => G_USER_ID,
178 p_last_update_login => G_LOGIN_ID,
179 px_object_version_number => l_object_version_number,
180 p_target_id => l_dm_target_value_rec.target_id,
181 p_target_value => l_dm_target_value_rec.target_value,
182 p_target_operator => l_dm_target_value_rec.target_operator,
183 p_range_value => l_dm_target_value_rec.range_value,
184 p_description => l_dm_target_value_rec.description
185 );
186
187 -- Set the return value for the new target value id
188 x_target_value_id := l_dm_target_value_rec.target_value_id;
189
190 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193
194 IF (AMS_DEBUG_HIGH_ON) THEN
195
196
197
198 AMS_UTILITY_PVT.debug_message( l_api_name || ' Going to activate target: ' || l_dm_target_value_rec.target_id);
199
200 END IF;
201
202 -- After successfully inserting the target value record, Set the target to Active
203 update ams_dm_targets_b
204 set active_flag = 'Y'
205 where target_id = l_dm_target_value_rec.target_id;
206
207 -- Also enable the associated data source
208 -- Get the data source id for this target
209 OPEN c_datasource_id(l_dm_target_value_rec.target_id);
210 FETCH c_datasource_id INTO l_datasource_id;
211 CLOSE c_datasource_id;
212
213 update ams_list_src_types
214 set enabled_flag = 'Y',
215 object_version_number = object_version_number + 1
216 where list_source_type_id = l_datasource_id;
217
218 --
219 -- End of API body
220 --
221
222 -- Standard check for p_commit
223 IF FND_API.to_Boolean( p_commit ) THEN
224 COMMIT WORK;
225 END IF;
226
227 -- Debug Message
228 IF (AMS_DEBUG_HIGH_ON) THEN
229
230 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
231 END IF;
232
233 -- Standard call to get message count and if count is 1, get message info.
234 FND_MSG_PUB.Count_And_Get
235 (p_count => x_msg_count,
236 p_data => x_msg_data
237 );
238 EXCEPTION
239
240 WHEN AMS_Utility_PVT.resource_locked THEN
241 x_return_status := FND_API.g_ret_sts_error;
242 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
243
244 WHEN FND_API.G_EXC_ERROR THEN
245 ROLLBACK TO CREATE_Dm_Target_Value_PVT;
246 x_return_status := FND_API.G_RET_STS_ERROR;
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
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 ROLLBACK TO CREATE_Dm_Target_Value_PVT;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 -- Standard call to get message count and if count=1, get the message
258 FND_MSG_PUB.Count_And_Get (
259 p_encoded => FND_API.G_FALSE,
260 p_count => x_msg_count,
261 p_data => x_msg_data
262 );
263
264 WHEN OTHERS THEN
265 ROLLBACK TO CREATE_Dm_Target_Value_PVT;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
268 THEN
269 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
270 END IF;
271 -- Standard call to get message count and if count=1, get the message
272 FND_MSG_PUB.Count_And_Get (
273 p_encoded => FND_API.G_FALSE,
274 p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 End Create_Dm_Target_Value;
278
279
280 PROCEDURE Update_Dm_Target_Value(
281 p_api_version_number IN NUMBER,
282 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
283 p_commit IN VARCHAR2 := FND_API.G_FALSE,
284 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
285
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289
290 p_dm_target_value_rec IN dm_target_value_rec_type,
291 x_object_version_number OUT NOCOPY NUMBER
292 )
293
294 IS
295
296
297 CURSOR c_get_dm_target_value(p_target_value_id NUMBER) IS
298 SELECT *
299 FROM AMS_DM_TARGET_VALUES_VL
300 WHERE target_value_id = p_target_value_id;
301
302
303 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Dm_Target_Value';
304 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
305
306 -- Local Variables
307 l_object_version_number NUMBER;
308 l_TARGET_VALUE_ID NUMBER;
309 l_ref_dm_target_value_rec c_get_Dm_Target_Value%ROWTYPE ;
310 l_tar_dm_target_value_rec AMS_Dm_Target_Value_PVT.dm_target_value_rec_type := P_dm_target_value_rec;
311 l_rowid ROWID;
312
313 BEGIN
314 -- Standard Start of API savepoint
315 SAVEPOINT UPDATE_Dm_Target_Value_PVT;
316
317 -- Debug Message
318 IF (AMS_DEBUG_HIGH_ON) THEN
319
320 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
321 END IF;
322
323 -- Standard call to check for call compatibility.
324 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
325 p_api_version_number,
326 l_api_name,
327 G_PKG_NAME)
328 THEN
329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
330 END IF;
331
332 -- Initialize message list if p_init_msg_list is set to TRUE.
333 IF FND_API.to_Boolean( p_init_msg_list )
334 THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337
338
339 -- Initialize API return status to SUCCESS
340 x_return_status := FND_API.G_RET_STS_SUCCESS;
341
342 -- Debug Message
343 IF (AMS_DEBUG_HIGH_ON) THEN
344
345 AMS_UTILITY_PVT.debug_message('Private API: - Going to Complete Record');
346 END IF;
347
348
349 -- Complete missing entries in the record before updating
350 Complete_dm_target_value_Rec(
351 p_dm_target_value_rec => p_dm_target_value_rec,
352 x_complete_rec => l_tar_dm_target_value_rec
353 );
354
355 -- Debug Message
356 IF (AMS_DEBUG_HIGH_ON) THEN
357
358 AMS_UTILITY_PVT.debug_message('Private API: - Open Reference Cursor');
359 END IF;
360
361 IF (AMS_DEBUG_HIGH_ON) THEN
362
363
364
365 AMS_UTILITY_PVT.debug_message('target value id: ' || l_tar_dm_target_value_rec.target_value_id);
366
367 END IF;
368
369 -- get the reference target, which contains
370 -- data before the update operation.
371 OPEN c_get_Dm_Target_Value( l_tar_dm_target_value_rec.target_value_id);
372 FETCH c_get_Dm_Target_Value INTO l_ref_dm_target_value_rec ;
373
374 If ( c_get_Dm_Target_Value%NOTFOUND) THEN
375 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
376 p_token_name => 'INFO',
377 p_token_value => 'Dm_Target_Value') ;
378 RAISE FND_API.G_EXC_ERROR;
379 END IF;
380
381 -- Debug Message
382 IF (AMS_DEBUG_HIGH_ON) THEN
383
384 AMS_UTILITY_PVT.debug_message('Private API: - Close Reference Cursor');
385 END IF;
386 CLOSE c_get_Dm_Target_Value;
387
388
389 If (l_tar_dm_target_value_rec.object_version_number is NULL or
390 l_tar_dm_target_value_rec.object_version_number = FND_API.G_MISS_NUM ) Then
391 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
392 p_token_name => 'COLUMN',
393 p_token_value => 'Last_Update_Date') ;
394 raise FND_API.G_EXC_ERROR;
395 End if;
396 -- Check Whether record has been changed by someone else
397 If (l_tar_dm_target_value_rec.object_version_number <> l_ref_dm_target_value_rec.object_version_number) Then
398 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
399 p_token_name => 'INFO',
400 p_token_value => 'Dm_Target_Value') ;
401 raise FND_API.G_EXC_ERROR;
402 End if;
403
404 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
405 THEN
406 -- Invoke validation procedures
407 Validate_dm_target_value(
408 p_api_version_number => 1.0,
409 p_init_msg_list => FND_API.G_FALSE,
410 p_validation_level => p_validation_level,
411 p_validation_mode => JTF_PLSQL_API.g_update,
412 p_dm_target_value_rec => l_tar_dm_target_value_rec,
413 x_return_status => x_return_status,
414 x_msg_count => x_msg_count,
415 x_msg_data => x_msg_data);
416 END IF;
417
418 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421
422 -- Debug Message
423 IF (AMS_DEBUG_HIGH_ON) THEN
424
425 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
426 END IF;
427
428 -- Invoke table handler(AMS_DM_TARGET_VALUES_B_PKG.Update_Row)
429 AMS_DM_TARGET_VALUES_B_PKG.Update_Row(
430 p_target_value_id => l_tar_dm_target_value_rec.target_value_id,
431 p_last_update_date => SYSDATE,
432 p_last_updated_by => G_USER_ID,
433 p_last_update_login => G_LOGIN_ID,
434 p_object_version_number => l_tar_dm_target_value_rec.object_version_number + 1,
435 p_target_id => l_tar_dm_target_value_rec.target_id,
436 p_target_value => l_tar_dm_target_value_rec.target_value,
437 p_target_operator => l_tar_dm_target_value_rec.target_operator,
438 p_range_value => l_tar_dm_target_value_rec.range_value,
439 p_description => l_tar_dm_target_value_rec.description
440 );
441
442 x_object_version_number := l_tar_dm_target_value_rec.object_version_number + 1;
443
444 --
445 -- End of API body.
446 --
447
448 -- Standard check for p_commit
449 IF FND_API.to_Boolean( p_commit )
450 THEN
451 COMMIT WORK;
452 END IF;
453
454 -- Debug Message
455 IF (AMS_DEBUG_HIGH_ON) THEN
456
457 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
458 END IF;
459
460 -- Standard call to get message count and if count is 1, get message info.
461 FND_MSG_PUB.Count_And_Get
462 (p_count => x_msg_count,
463 p_data => x_msg_data
464 );
465 EXCEPTION
466
467 WHEN AMS_Utility_PVT.resource_locked THEN
468 x_return_status := FND_API.g_ret_sts_error;
469 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
470
471 WHEN FND_API.G_EXC_ERROR THEN
472 ROLLBACK TO UPDATE_Dm_Target_Value_PVT;
473 x_return_status := FND_API.G_RET_STS_ERROR;
474 -- Standard call to get message count and if count=1, get the message
475 FND_MSG_PUB.Count_And_Get (
476 p_encoded => FND_API.G_FALSE,
477 p_count => x_msg_count,
478 p_data => x_msg_data
479 );
480
481 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
482 ROLLBACK TO UPDATE_Dm_Target_Value_PVT;
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 -- Standard call to get message count and if count=1, get the message
485 FND_MSG_PUB.Count_And_Get (
486 p_encoded => FND_API.G_FALSE,
487 p_count => x_msg_count,
488 p_data => x_msg_data
489 );
490
491 WHEN OTHERS THEN
492 ROLLBACK TO UPDATE_Dm_Target_Value_PVT;
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
495 THEN
496 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
497 END IF;
498 -- Standard call to get message count and if count=1, get the message
499 FND_MSG_PUB.Count_And_Get (
500 p_encoded => FND_API.G_FALSE,
501 p_count => x_msg_count,
502 p_data => x_msg_data
503 );
504 End Update_Dm_Target_Value;
505
506 PROCEDURE Delete_TgtValues_For_Target ( p_target_id IN NUMBER)
507 IS
508 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_TgtValues_For_Target';
509
510 BEGIN
511 -- Standard Start of API savepoint
512 SAVEPOINT Delete_TgtValues_For_Target;
513
514 -- Debug Message
515 IF (AMS_DEBUG_HIGH_ON) THEN
516
517 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
518 END IF;
519
520 --
521 -- Api body
522 --
523 DELETE FROM ams_dm_target_values_tl
524 WHERE TARGET_VALUE_ID in (SELECT TARGET_VALUE_ID FROM AMS_DM_TARGET_VALUES_B WHERE TARGET_ID = p_TARGET_ID);
525
526 DELETE FROM AMS_DM_TARGET_VALUES_B
527 WHERE TARGET_ID = p_TARGET_ID;
528
529 --
530 -- End of API body
531 --
532
533 -- Debug Message
534 IF (AMS_DEBUG_HIGH_ON) THEN
535
536 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
537 END IF;
538
539 EXCEPTION
540
541 WHEN AMS_Utility_PVT.resource_locked THEN
542 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
543
544 WHEN FND_API.G_EXC_ERROR THEN
545 ROLLBACK TO Delete_TgtValues_For_Target;
546
547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548 ROLLBACK TO Delete_TgtValues_For_Target;
549
550 WHEN OTHERS THEN
551 ROLLBACK TO Delete_TgtValues_For_Target;
552
553 End Delete_TgtValues_For_Target;
554
555
556
557
558 PROCEDURE Delete_Dm_Target_Value(
559 p_api_version_number IN NUMBER,
560 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
561 p_commit IN VARCHAR2 := FND_API.G_FALSE,
562 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY NUMBER,
565 x_msg_data OUT NOCOPY VARCHAR2,
566 p_target_value_id IN NUMBER,
567 p_object_version_number IN NUMBER
568 )
569
570 IS
571 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Dm_Target_Value';
572 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
573 l_object_version_number NUMBER;
574 l_no_of_target_values NUMBER;
575 l_target_id NUMBER;
576 l_no_of_active_targets NUMBER;
577 l_datasource_id NUMBER;
578
579 -- Cursor to get the target id for the target value
580 CURSOR c_target_id (l_tgtValueId IN NUMBER) IS
581 SELECT target_id
582 FROM ams_dm_target_values_vl
583 WHERE target_value_id = l_tgtValueId;
584
585 -- Cursor to count the target values for a target
586 CURSOR c_target_values_count (l_tgtId IN NUMBER) IS
587 SELECT count(*)
588 FROM AMS_DM_TARGET_VALUES_VL
589 WHERE TARGET_ID = l_tgtId;
590
591 -- Cursor to get the data source id for the target
592 CURSOR c_datasource_id (l_tgtId IN NUMBER) IS
593 SELECT data_source_id
594 FROM ams_dm_targets_vl
595 WHERE target_id = l_tgtId;
596
597 -- Cursor to count the active targets defined for a data source
598 CURSOR c_target_count (l_dsId IN NUMBER) IS
599 SELECT count(*)
600 FROM AMS_DM_TARGETS_VL
601 WHERE DATA_SOURCE_ID = l_dsId
602 AND ACTIVE_FLAG = 'Y';
603
604 BEGIN
605 -- Standard Start of API savepoint
606 SAVEPOINT DELETE_Dm_Target_Value_PVT;
607
608 -- Standard call to check for call compatibility.
609 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
610 p_api_version_number,
611 l_api_name,
612 G_PKG_NAME)
613 THEN
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
615 END IF;
616
617 -- Initialize message list if p_init_msg_list is set to TRUE.
618 IF FND_API.to_Boolean( p_init_msg_list )
619 THEN
620 FND_MSG_PUB.initialize;
621 END IF;
622
623 -- Debug Message
624 IF (AMS_DEBUG_HIGH_ON) THEN
625
626 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
627 END IF;
628
629
630 -- Initialize API return status to SUCCESS
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632
633 --
634 -- Api body
635 --
636 -- Debug Message
637 IF (AMS_DEBUG_HIGH_ON) THEN
638
639 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
640 END IF;
641
642 -- Get the target id for this target value record before deleting it
643 OPEN c_target_id(p_target_value_id);
644 FETCH c_target_id INTO l_target_id;
645 CLOSE c_target_id;
646
647 IF (AMS_DEBUG_HIGH_ON) THEN
648
649
650
651 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Before Delete: TARGET_ID = ' || l_target_id );
652
653 END IF;
654
655 -- Invoke table handler(AMS_DM_TARGET_VALUES_B_PKG.Delete_Row)
656 AMS_DM_TARGET_VALUES_B_PKG.Delete_Row(
657 p_TARGET_VALUE_ID => p_TARGET_VALUE_ID);
658
659
660 -- After successfully deleteing the target value, check if there are no more
661 -- target values for the target. If none exist, then de-activate the target.
662 OPEN c_target_values_count(l_target_id);
663 FETCH c_target_values_count INTO l_no_of_target_values;
664 CLOSE c_target_values_count;
665
666
667 IF l_no_of_target_values = 0 THEN
668
669 -- Disable this target
670 update ams_dm_targets_b
671 set active_flag = 'N'
672 where target_id = l_target_id;
673
674 -- Also disable the associated data source if it has no more active targets
675 -- Get the data source id for this target
676 OPEN c_datasource_id(l_target_id);
677 FETCH c_datasource_id INTO l_datasource_id;
678 CLOSE c_datasource_id;
679
680 -- Count the number of active targets for the data source
681 OPEN c_target_count(l_datasource_id);
682 FETCH c_target_count INTO l_no_of_active_targets;
683 CLOSE c_target_count;
684
685 IF l_no_of_active_targets = 0 THEN
686 update ams_list_src_types
687 set enabled_flag = 'N'
688 where list_source_type_id = l_datasource_id;
689 END IF;
690
691 END IF;
692
693
694 --
695 -- End of API body
696 --
697
698 -- Standard check for p_commit
699 IF FND_API.to_Boolean( p_commit )
700 THEN
701 COMMIT WORK;
702 END IF;
703
704
705
706 -- Debug Message
707 IF (AMS_DEBUG_HIGH_ON) THEN
708
709 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
710 END IF;
711
712 -- Standard call to get message count and if count is 1, get message info.
713 FND_MSG_PUB.Count_And_Get
714 (p_count => x_msg_count,
715 p_data => x_msg_data
716 );
717 EXCEPTION
718
719 WHEN AMS_Utility_PVT.resource_locked THEN
720 x_return_status := FND_API.g_ret_sts_error;
721 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
722
723 WHEN FND_API.G_EXC_ERROR THEN
724 ROLLBACK TO DELETE_Dm_Target_Value_PVT;
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 -- Standard call to get message count and if count=1, get the message
727 FND_MSG_PUB.Count_And_Get (
728 p_encoded => FND_API.G_FALSE,
729 p_count => x_msg_count,
730 p_data => x_msg_data
731 );
732
733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
734 ROLLBACK TO DELETE_Dm_Target_Value_PVT;
735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
736 -- Standard call to get message count and if count=1, get the message
737 FND_MSG_PUB.Count_And_Get (
738 p_encoded => FND_API.G_FALSE,
739 p_count => x_msg_count,
740 p_data => x_msg_data
741 );
742
743 WHEN OTHERS THEN
744 ROLLBACK TO DELETE_Dm_Target_Value_PVT;
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
747 THEN
748 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
749 END IF;
750 -- Standard call to get message count and if count=1, get the message
751 FND_MSG_PUB.Count_And_Get (
752 p_encoded => FND_API.G_FALSE,
753 p_count => x_msg_count,
754 p_data => x_msg_data
755 );
756 End Delete_Dm_Target_Value;
757
758
759
760 -- Hint: Primary key needs to be returned.
761 PROCEDURE Lock_Dm_Target_Value(
762 p_api_version_number IN NUMBER,
763 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
764
765 x_return_status OUT NOCOPY VARCHAR2,
766 x_msg_count OUT NOCOPY NUMBER,
767 x_msg_data OUT NOCOPY VARCHAR2,
768
769 p_target_value_id IN NUMBER,
770 p_object_version IN NUMBER
771 )
772
773 IS
774 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Dm_Target_Value';
775 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
776 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
777 l_TARGET_VALUE_ID NUMBER;
778
779 CURSOR c_Dm_Target_Value IS
780 SELECT TARGET_VALUE_ID
781 FROM AMS_DM_TARGET_VALUES_B
782 WHERE TARGET_VALUE_ID = p_TARGET_VALUE_ID
783 AND object_version_number = p_object_version
784 FOR UPDATE NOWAIT;
785
786 BEGIN
787
788 -- Debug Message
789 IF (AMS_DEBUG_HIGH_ON) THEN
790
791 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
792 END IF;
793
794 -- Initialize message list if p_init_msg_list is set to TRUE.
795 IF FND_API.to_Boolean( p_init_msg_list )
796 THEN
797 FND_MSG_PUB.initialize;
798 END IF;
799
800 -- Standard call to check for call compatibility.
801 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
802 p_api_version_number,
803 l_api_name,
804 G_PKG_NAME)
805 THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809
810 -- Initialize API return status to SUCCESS
811 x_return_status := FND_API.G_RET_STS_SUCCESS;
812
813
814 ------------------------ lock -------------------------
815
816 IF (AMS_DEBUG_HIGH_ON) THEN
817
818
819
820 AMS_Utility_PVT.debug_message(l_full_name||': start');
821
822 END IF;
823 OPEN c_Dm_Target_Value;
824
825 FETCH c_Dm_Target_Value INTO l_TARGET_VALUE_ID;
826
827 IF (c_Dm_Target_Value%NOTFOUND) THEN
828 CLOSE c_Dm_Target_Value;
829 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
830 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
831 FND_MSG_PUB.add;
832 END IF;
833 RAISE FND_API.g_exc_error;
834 END IF;
835
836 CLOSE c_Dm_Target_Value;
837
838 -------------------- finish --------------------------
839 FND_MSG_PUB.count_and_get(
840 p_encoded => FND_API.g_false,
841 p_count => x_msg_count,
842 p_data => x_msg_data);
843 IF (AMS_DEBUG_HIGH_ON) THEN
844
845 AMS_Utility_PVT.debug_message(l_full_name ||': end');
846 END IF;
847 EXCEPTION
848
849 WHEN AMS_Utility_PVT.resource_locked THEN
850 x_return_status := FND_API.g_ret_sts_error;
851 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
852
853 WHEN FND_API.G_EXC_ERROR THEN
854 ROLLBACK TO LOCK_Dm_Target_Value_PVT;
855 x_return_status := FND_API.G_RET_STS_ERROR;
856 -- Standard call to get message count and if count=1, get the message
857 FND_MSG_PUB.Count_And_Get (
858 p_encoded => FND_API.G_FALSE,
859 p_count => x_msg_count,
860 p_data => x_msg_data
861 );
862
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 ROLLBACK TO LOCK_Dm_Target_Value_PVT;
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 -- Standard call to get message count and if count=1, get the message
867 FND_MSG_PUB.Count_And_Get (
868 p_encoded => FND_API.G_FALSE,
869 p_count => x_msg_count,
870 p_data => x_msg_data
871 );
872
873 WHEN OTHERS THEN
874 ROLLBACK TO LOCK_Dm_Target_Value_PVT;
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
876 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
877 THEN
878 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
879 END IF;
880 -- Standard call to get message count and if count=1, get the message
881 FND_MSG_PUB.Count_And_Get (
882 p_encoded => FND_API.G_FALSE,
883 p_count => x_msg_count,
884 p_data => x_msg_data
885 );
886 End Lock_Dm_Target_Value;
887
888
889 PROCEDURE check_uk_items(
890 p_dm_target_value_rec IN dm_target_value_rec_type,
891 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
892 x_return_status OUT NOCOPY VARCHAR2)
893 IS
894 l_valid_flag VARCHAR2(1);
895
896 CURSOR c_op_value IS
897 SELECT FND_API.g_false
898 FROM ams_dm_target_values_b tvb
899 WHERE tvb.target_id = p_dm_target_value_rec.target_id
900 AND tvb.target_operator = p_dm_target_value_rec.target_operator
901 AND tvb.target_value = p_dm_target_value_rec.target_value
902 ;
903
904 -- March 17, 2003 - nyostos
905 -- Added following query for UPDATE mode
906 CURSOR c_op_value_updt IS
907 SELECT FND_API.g_false
908 FROM ams_dm_target_values_b tvb
909 WHERE tvb.target_id = p_dm_target_value_rec.target_id
910 AND tvb.target_operator = p_dm_target_value_rec.target_operator
911 AND tvb.target_value = p_dm_target_value_rec.target_value
912 AND tvb.target_value_id <> p_dm_target_value_rec.target_value_id
913 ;
914
915 BEGIN
916 x_return_status := FND_API.g_ret_sts_success;
917
918 -- validate uniqueness of primary key
919 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
920 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
921 'AMS_DM_TARGET_VALUES_B',
922 'TARGET_VALUE_ID = ''' || p_dm_target_value_rec.TARGET_VALUE_ID ||''''
923 );
924 ELSE
925 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
926 'AMS_DM_TARGET_VALUES_B',
927 'TARGET_VALUE_ID = ''' || p_dm_target_value_rec.TARGET_VALUE_ID ||
928 ''' AND TARGET_VALUE_ID <> ' || p_dm_target_value_rec.TARGET_VALUE_ID
929 );
930 END IF;
931
932 IF l_valid_flag = FND_API.g_false THEN
933 AMS_Utility_PVT.Error_Message('AMS_API_DUPLICATE_ID', 'ID_FIELD','TARGET_VALUE_ID');
934 x_return_status := FND_API.g_ret_sts_error;
935 RETURN;
936 END IF;
937
938 -- validate uniqueness of target_id, target_operator and target_value
939 l_valid_flag := FND_API.g_true;
940 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
941 OPEN c_op_value;
942 FETCH c_op_value INTO l_valid_flag;
943 CLOSE c_op_value;
944 ELSE
945 -- March 17, 2003 - nyostos
946 -- Added following check for UPDATE mode
947 OPEN c_op_value_updt;
948 FETCH c_op_value_updt INTO l_valid_flag;
949 CLOSE c_op_value_updt;
950 END IF;
951
952 IF l_valid_flag = FND_API.g_false THEN
953 AMS_Utility_PVT.Error_Message('AMS_API_DUPLICATE_ID', 'ID_FIELD','TARGET_OPERATOR');
954 x_return_status := FND_API.g_ret_sts_error;
955 END IF;
956 END check_uk_items;
957
958 PROCEDURE check_req_items(
959 p_dm_target_value_rec IN dm_target_value_rec_type,
960 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
961 x_return_status OUT NOCOPY VARCHAR2
962 )
963 IS
964 BEGIN
965 x_return_status := FND_API.g_ret_sts_success;
966
967 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
968
969 IF (AMS_DEBUG_HIGH_ON) THEN
970
971
972
973 ams_utility_pvt.debug_message('Private API:check_req_items for CREATE');
974
975 END IF;
976
977 IF p_dm_target_value_rec.target_value_id = FND_API.g_miss_num OR p_dm_target_value_rec.target_value_id IS NULL THEN
978 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_VALUE_ID');
979 x_return_status := FND_API.g_ret_sts_error;
980 END IF;
981 IF p_dm_target_value_rec.target_id = FND_API.g_miss_num OR p_dm_target_value_rec.target_id IS NULL THEN
982 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_ID');
983 x_return_status := FND_API.g_ret_sts_error;
984 END IF;
985 IF p_dm_target_value_rec.target_value = FND_API.g_miss_char OR p_dm_target_value_rec.target_value IS NULL THEN
986 --changed rosharma 19-feb-2003 Bug # 2853640
987 --AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_VALUE');
988 AMS_Utility_PVT.Error_Message('AMS_DM_NO_TARVAL');
989 --end change rosharma 19-feb-2003 Bug # 2853640
990 x_return_status := FND_API.g_ret_sts_error;
991 END IF;
992 IF p_dm_target_value_rec.target_operator = FND_API.g_miss_char OR p_dm_target_value_rec.target_operator IS NULL THEN
993 --changed rosharma 19-feb-2003 Bug # 2853640
994 --AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_OPERATOR');
995 AMS_Utility_PVT.Error_Message('AMS_DM_NO_TARVAL_OP');
996 --end change rosharma 19-feb-2003 Bug # 2853640
997 x_return_status := FND_API.g_ret_sts_error;
998 END IF;
999 ELSE
1000 IF (AMS_DEBUG_HIGH_ON) THEN
1001
1002 ams_utility_pvt.debug_message('Private API:check_req_items for UPDATE');
1003 END IF;
1004 IF p_dm_target_value_rec.target_value_id IS NULL THEN
1005 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_VALUE_ID');
1006 x_return_status := FND_API.g_ret_sts_error;
1007 END IF;
1008 IF p_dm_target_value_rec.last_update_date IS NULL THEN
1009 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATE_DATE');
1010 x_return_status := FND_API.g_ret_sts_error;
1011 END IF;
1012 IF p_dm_target_value_rec.last_updated_by IS NULL THEN
1013 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATED_BY');
1014 x_return_status := FND_API.g_ret_sts_error;
1015 END IF;
1016 IF p_dm_target_value_rec.creation_date IS NULL THEN
1017 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','CREATION_DATE');
1018 x_return_status := FND_API.g_ret_sts_error;
1019 END IF;
1020 IF p_dm_target_value_rec.created_by IS NULL THEN
1021 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','CREATED_BY');
1022 x_return_status := FND_API.g_ret_sts_error;
1023 END IF;
1024 IF p_dm_target_value_rec.last_update_login IS NULL THEN
1025 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','LAST_UPDATE_LOGIN');
1026 x_return_status := FND_API.g_ret_sts_error;
1027 END IF;
1028 IF p_dm_target_value_rec.object_version_number IS NULL THEN
1029 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','OBJECT_VERSION_NUMBER');
1030 x_return_status := FND_API.g_ret_sts_error;
1031 END IF;
1032 IF p_dm_target_value_rec.target_id IS NULL THEN
1033 AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_ID');
1034 x_return_status := FND_API.g_ret_sts_error;
1035 END IF;
1036 IF p_dm_target_value_rec.target_value IS NULL THEN
1037 --changed rosharma 19-feb-2003 Bug # 2853640
1038 --AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_VALUE');
1039 AMS_Utility_PVT.Error_Message('AMS_DM_NO_TARVAL');
1040 --end change rosharma 19-feb-2003 Bug # 2853640
1041 x_return_status := FND_API.g_ret_sts_error;
1042 END IF;
1043 IF p_dm_target_value_rec.target_operator IS NULL THEN
1044 --changed rosharma 19-feb-2003 Bug # 2853640
1045 --AMS_Utility_PVT.Error_Message('AMS_API_MISSING_FIELD', 'MISS_FIELD','TARGET_OPERATOR');
1046 AMS_Utility_PVT.Error_Message('AMS_DM_NO_TARVAL_OP');
1047 --end change rosharma 19-feb-2003 Bug # 2853640
1048 x_return_status := FND_API.g_ret_sts_error;
1049 END IF;
1050 END IF;
1051
1052 END check_req_items;
1053
1054 PROCEDURE check_dm_target_value_FK_items(
1055 p_dm_target_value_rec IN dm_target_value_rec_type,
1056 x_return_status OUT NOCOPY VARCHAR2
1057 )
1058 IS
1059 BEGIN
1060 x_return_status := FND_API.g_ret_sts_success;
1061
1062 --------------------target_id---------------------------
1063 IF p_dm_target_value_rec.target_id <> FND_API.g_miss_num THEN
1064 IF AMS_Utility_PVT.check_fk_exists(
1065 'ams_dm_targets_b',
1066 'target_id',
1067 p_dm_target_value_rec.target_id
1068 ) = FND_API.g_false
1069 THEN
1070 AMS_Utility_PVT.error_message ('AMS_API_INVALID_FK', 'COLUMN_NAME', 'TARGET_ID');
1071 x_return_status := FND_API.g_ret_sts_error;
1072 END IF;
1073 END IF;
1074
1075 END check_dm_target_value_FK_items;
1076
1077 PROCEDURE check_Lookup_items(
1078 p_dm_target_value_rec IN dm_target_value_rec_type,
1079 x_return_status OUT NOCOPY VARCHAR2
1080 )
1081 IS
1082 BEGIN
1083 x_return_status := FND_API.g_ret_sts_success;
1084
1085 -- target operator --
1086 IF p_dm_target_value_rec.target_operator <> FND_API.g_miss_char THEN
1087 IF AMS_Utility_PVT.check_lookup_exists(
1088 p_lookup_type => 'AMS_DM_TARGET_OPERATORS',
1089 p_lookup_code => p_dm_target_value_rec.target_operator
1090 ) = FND_API.g_false
1091 THEN
1092 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1093 FND_MESSAGE.set_name('AMS', 'AMS_API_INVALID_LOOKUP');
1094 FND_MESSAGE.set_token ('LOOKUP_CODE', p_dm_target_value_rec.target_operator);
1095 FND_MESSAGE.set_token ('COLUMN_NAME', 'TARGET_OPERATOR');
1096 FND_MSG_PUB.add;
1097 END IF;
1098 x_return_status := FND_API.g_ret_sts_error;
1099 END IF;
1100 END IF;
1101 END check_Lookup_items;
1102
1103 PROCEDURE Check_dm_target_value_Items (
1104 P_dm_target_value_rec IN dm_target_value_rec_type,
1105 p_validation_mode IN VARCHAR2,
1106 x_return_status OUT NOCOPY VARCHAR2
1107 )
1108 IS
1109 BEGIN
1110
1111 -- Check Items Uniqueness API calls
1112
1113 check_uk_items(
1114 p_dm_target_value_rec => p_dm_target_value_rec,
1115 p_validation_mode => p_validation_mode,
1116 x_return_status => x_return_status);
1117 IF x_return_status <> FND_API.g_ret_sts_success THEN
1118 RETURN;
1119 END IF;
1120
1121 -- Check Items Required/NOT NULL API calls
1122 check_req_items(
1123 p_dm_target_value_rec => p_dm_target_value_rec,
1124 p_validation_mode => p_validation_mode,
1125 x_return_status => x_return_status);
1126 IF x_return_status <> FND_API.g_ret_sts_success THEN
1127 RETURN;
1128 END IF;
1129
1130 -- Check Items Foreign Keys API calls
1131 check_dm_target_value_FK_items(
1132 p_dm_target_value_rec => p_dm_target_value_rec,
1133 x_return_status => x_return_status);
1134 IF x_return_status <> FND_API.g_ret_sts_success THEN
1135 RETURN;
1136 END IF;
1137
1138 -- Check Items Lookups
1139 check_Lookup_items(
1140 p_dm_target_value_rec => p_dm_target_value_rec,
1141 x_return_status => x_return_status);
1142 IF x_return_status <> FND_API.g_ret_sts_success THEN
1143 RETURN;
1144 END IF;
1145
1146 END Check_dm_target_value_Items;
1147
1148
1149
1150 PROCEDURE Complete_dm_target_value_Rec (
1151 p_dm_target_value_rec IN dm_target_value_rec_type,
1152 x_complete_rec OUT NOCOPY dm_target_value_rec_type)
1153 IS
1154 l_return_status VARCHAR2(1);
1155
1156 CURSOR c_complete IS
1157 SELECT *
1158 FROM ams_dm_target_values_vl
1159 WHERE target_value_id = p_dm_target_value_rec.target_value_id;
1160 l_dm_target_value_rec c_complete%ROWTYPE;
1161 BEGIN
1162
1163 x_complete_rec := p_dm_target_value_rec;
1164
1165
1166 OPEN c_complete;
1167 FETCH c_complete INTO l_dm_target_value_rec;
1168 CLOSE c_complete;
1169
1170 -- target_value_id
1171 IF p_dm_target_value_rec.target_value_id = FND_API.g_miss_num THEN
1172 x_complete_rec.target_value_id := l_dm_target_value_rec.target_value_id;
1173 END IF;
1174
1175 -- last_update_date
1176 IF p_dm_target_value_rec.last_update_date = FND_API.g_miss_date THEN
1177 x_complete_rec.last_update_date := l_dm_target_value_rec.last_update_date;
1178 END IF;
1179
1180 -- last_updated_by
1181 IF p_dm_target_value_rec.last_updated_by = FND_API.g_miss_num THEN
1182 x_complete_rec.last_updated_by := l_dm_target_value_rec.last_updated_by;
1183 END IF;
1184
1185 -- creation_date
1186 IF p_dm_target_value_rec.creation_date = FND_API.g_miss_date THEN
1187 x_complete_rec.creation_date := l_dm_target_value_rec.creation_date;
1188 END IF;
1189
1190 -- created_by
1191 IF p_dm_target_value_rec.created_by = FND_API.g_miss_num THEN
1192 x_complete_rec.created_by := l_dm_target_value_rec.created_by;
1193 END IF;
1194
1195 -- last_update_login
1196 IF p_dm_target_value_rec.last_update_login = FND_API.g_miss_num THEN
1197 x_complete_rec.last_update_login := l_dm_target_value_rec.last_update_login;
1198 END IF;
1199
1200 -- object_version_number
1201 IF p_dm_target_value_rec.object_version_number = FND_API.g_miss_num THEN
1202 x_complete_rec.object_version_number := l_dm_target_value_rec.object_version_number;
1203 END IF;
1204
1205 -- target_id
1206 IF p_dm_target_value_rec.target_id = FND_API.g_miss_num THEN
1207 x_complete_rec.target_id := l_dm_target_value_rec.target_id;
1208 END IF;
1209
1210 -- target_value
1211 IF p_dm_target_value_rec.target_value = FND_API.g_miss_char THEN
1212 x_complete_rec.target_value := l_dm_target_value_rec.target_value;
1213 END IF;
1214
1215 -- target_operator
1216 IF p_dm_target_value_rec.target_operator = FND_API.g_miss_char THEN
1217 x_complete_rec.target_operator := l_dm_target_value_rec.target_operator;
1218 END IF;
1219
1220 -- range_value
1221 IF p_dm_target_value_rec.range_value = FND_API.g_miss_char THEN
1222 x_complete_rec.range_value := l_dm_target_value_rec.range_value;
1223 END IF;
1224
1225 -- description
1226 IF p_dm_target_value_rec.description = FND_API.g_miss_char THEN
1227 x_complete_rec.description := l_dm_target_value_rec.description;
1228 END IF;
1229
1230 END Complete_dm_target_value_Rec;
1231
1232 PROCEDURE Validate_dm_target_value(
1233 p_api_version_number IN NUMBER,
1234 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1235 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1236 p_dm_target_value_rec IN dm_target_value_rec_type,
1237 p_validation_mode IN VARCHAR2,
1238 x_return_status OUT NOCOPY VARCHAR2,
1239 x_msg_count OUT NOCOPY NUMBER,
1240 x_msg_data OUT NOCOPY VARCHAR2
1241 )
1242 IS
1243 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Dm_Target_Value';
1244 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1245 l_object_version_number NUMBER;
1246 -- l_dm_target_value_rec AMS_Dm_Target_Value_PVT.dm_target_value_rec_type;
1247
1248 BEGIN
1249 -- Standard Start of API savepoint
1250 SAVEPOINT VALIDATE_Dm_Target_Value_;
1251
1252 -- Standard call to check for call compatibility.
1253 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1254 p_api_version_number,
1255 l_api_name,
1256 G_PKG_NAME)
1257 THEN
1258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1259 END IF;
1260
1261 -- Initialize message list if p_init_msg_list is set to TRUE.
1262 IF FND_API.to_Boolean( p_init_msg_list )
1263 THEN
1264 FND_MSG_PUB.initialize;
1265 END IF;
1266 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1267 Check_dm_target_value_Items(
1268 p_dm_target_value_rec => p_dm_target_value_rec,
1269 p_validation_mode => p_validation_mode,
1270 x_return_status => x_return_status
1271 );
1272
1273 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1274 RAISE FND_API.G_EXC_ERROR;
1275 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1277 END IF;
1278 END IF;
1279
1280 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1281 Validate_dm_target_value_Rec(
1282 p_api_version_number => 1.0,
1283 p_init_msg_list => FND_API.G_FALSE,
1284 x_return_status => x_return_status,
1285 x_msg_count => x_msg_count,
1286 x_msg_data => x_msg_data,
1287 p_dm_target_value_rec => p_dm_target_value_rec);
1288
1289 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1290 RAISE FND_API.G_EXC_ERROR;
1291 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1293 END IF;
1294 END IF;
1295
1296
1297 -- Debug Message
1298 IF (AMS_DEBUG_HIGH_ON) THEN
1299
1300 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1301 END IF;
1302
1303
1304 -- Initialize API return status to SUCCESS
1305 x_return_status := FND_API.G_RET_STS_SUCCESS;
1306
1307
1308 -- Debug Message
1309 IF (AMS_DEBUG_HIGH_ON) THEN
1310
1311 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1312 END IF;
1313
1314 -- Standard call to get message count and if count is 1, get message info.
1315 FND_MSG_PUB.Count_And_Get
1316 (p_count => x_msg_count,
1317 p_data => x_msg_data
1318 );
1319 EXCEPTION
1320
1321 WHEN AMS_Utility_PVT.resource_locked THEN
1322 x_return_status := FND_API.g_ret_sts_error;
1323 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1324
1325 WHEN FND_API.G_EXC_ERROR THEN
1326 ROLLBACK TO VALIDATE_Dm_Target_Value_;
1327 x_return_status := FND_API.G_RET_STS_ERROR;
1328 -- Standard call to get message count and if count=1, get the message
1329 FND_MSG_PUB.Count_And_Get (
1330 p_encoded => FND_API.G_FALSE,
1331 p_count => x_msg_count,
1332 p_data => x_msg_data
1333 );
1334
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 ROLLBACK TO VALIDATE_Dm_Target_Value_;
1337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1338 -- Standard call to get message count and if count=1, get the message
1339 FND_MSG_PUB.Count_And_Get (
1340 p_encoded => FND_API.G_FALSE,
1341 p_count => x_msg_count,
1342 p_data => x_msg_data
1343 );
1344
1345 WHEN OTHERS THEN
1346 ROLLBACK TO VALIDATE_Dm_Target_Value_;
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1349 THEN
1350 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1351 END IF;
1352 -- Standard call to get message count and if count=1, get the message
1353 FND_MSG_PUB.Count_And_Get (
1354 p_encoded => FND_API.G_FALSE,
1355 p_count => x_msg_count,
1356 p_data => x_msg_data
1357 );
1358 End Validate_Dm_Target_Value;
1359
1360
1361 PROCEDURE Validate_dm_target_value_rec(
1362 p_api_version_number IN NUMBER,
1363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 x_msg_count OUT NOCOPY NUMBER,
1366 x_msg_data OUT NOCOPY VARCHAR2,
1367 p_dm_target_value_rec IN dm_target_value_rec_type
1368 )
1369 IS
1370 l_data_type VARCHAR2(30) := NULL;
1371
1372 -- Cursor to get the data type for the target field
1373 -- Added rosharma 28-Nov-2002
1374 CURSOR c_field_data_type (l_tgtId IN NUMBER) IS
1375 SELECT a.field_data_type
1376 FROM ams_list_src_fields a, ams_dm_targets_b b
1377 WHERE b.target_id = l_tgtId
1378 AND a.list_source_field_id = b.source_field_id;
1379
1380 BEGIN
1381 -- Debug Message
1382 IF (AMS_DEBUG_HIGH_ON) THEN
1383
1384 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_target_value_rec');
1385 END IF;
1386
1387 -- Initialize message list if p_init_msg_list is set to TRUE.
1388 IF FND_API.to_Boolean( p_init_msg_list )
1389 THEN
1390 FND_MSG_PUB.initialize;
1391 END IF;
1392
1393 -- Initialize API return status to SUCCESS
1394 x_return_status := FND_API.G_RET_STS_SUCCESS;
1395
1396 -- if target_operator = BETWEEN then range value is required
1397 -- and the range must be from lower value to higher value
1398 IF p_dm_target_value_rec.target_operator IS NOT NULL AND
1399 p_dm_target_value_rec.target_operator <> FND_API.g_miss_char THEN
1400 IF p_dm_target_value_rec.target_operator = 'BETWEEN' THEN
1401 IF p_dm_target_value_rec.range_value IS NULL OR
1402 p_dm_target_value_rec.range_value = FND_API.g_miss_char THEN
1403 AMS_Utility_PVT.error_message ('AMS_DM_TARVAL_NO_BETWEEN');
1404 x_return_status := FND_API.G_RET_STS_ERROR;
1405 ELSE
1406 DECLARE
1407 l_low NUMBER;
1408 l_high NUMBER;
1409 BEGIN
1410 -- try to convert to numbers to do
1411 -- between numbers, else use chars
1412 -- if invalid number exception thrown
1413 l_low := TO_NUMBER (p_dm_target_value_rec.target_value);
1414 l_high := TO_NUMBER (p_dm_target_value_rec.range_value);
1415 IF l_low > l_high THEN
1416 AMS_Utility_PVT.error_message ('AMS_DM_TARVAL_INVALID_RANGE');
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 END IF;
1419 EXCEPTION
1420 WHEN VALUE_ERROR THEN
1421 NULL; -- we don't care about char comparisons for between
1422 END;
1423 END IF;
1424 END IF;
1425 -- x_return_status := FND_API.G_RET_STS_ERROR;
1426 END IF;
1427
1428 -- start add rosharma 28-Nov-2002
1429 -- if target field type is NUMBER then value must be a number
1430 OPEN c_field_data_type(p_dm_target_value_rec.TARGET_ID);
1431 FETCH c_field_data_type INTO l_data_type;
1432 CLOSE c_field_data_type;
1433
1434 IF l_data_type = 'NUMBER' THEN
1435 DECLARE
1436 l_value NUMBER;
1437 BEGIN
1438 l_value := TO_NUMBER (p_dm_target_value_rec.target_value);
1439 IF p_dm_target_value_rec.target_operator IS NOT NULL AND
1440 p_dm_target_value_rec.target_operator <> FND_API.g_miss_char THEN
1441 IF p_dm_target_value_rec.target_operator = 'BETWEEN' THEN
1442 DECLARE
1443 l_high_value NUMBER;
1444 BEGIN
1445 l_high_value := TO_NUMBER (p_dm_target_value_rec.range_value);
1446 END;
1447 END IF;
1448 END IF;
1449 EXCEPTION
1450 WHEN VALUE_ERROR THEN
1451 AMS_Utility_PVT.error_message ('AMS_DM_TARVAL_NOT_NUMBER');
1452 x_return_status := FND_API.G_RET_STS_ERROR;
1453 END;
1454 END IF;
1455 -- end add rosharma 28-Nov-2002
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_value_Rec;
1463
1464 END AMS_Dm_Target_Value_PVT;
[Home] [Help]