[Home] [Help]
PACKAGE BODY: APPS.AMS_THLDCHK_PVT
Source
1 PACKAGE BODY AMS_ThldChk_PVT as
2 /* $Header: amsvthcb.pls 120.2 2005/09/02 23:50:59 kbasavar noship $ */
3
4 --
5 -- NAME
6 -- AMS_ThldChk_PVT
7 --
8 -- HISTORY
9 -- 06/25/1999 ptendulk CREATED
10 -- 10/26/1999 ptendulk Modified according to new standards
11 -- 12/30/1999 ptendulk Modified as there won't be any lookup
12 -- for Operator.
13 -- Also Chk2 Source can be Events /Campaign
14 -- Modified code for that
15 -- 02/14/2000 ptendulk Changed the uom Validation in Record Validation
16 -- Changed the message names
17 -- 15-Feb-2001 ptendulk Changed the api for Hornet release
18 -- 1. chk1/chk2 Object ids were added.
19 -- 2. chk1 is not restricted to the current campaigns
20 -- metric.
21 -- 23-aug-2002 soagrawa Fixed bug# 2528692 - cannot create trigger of type
22 -- metric to workbook due to size of l_pk_value
23 --
24 --
25 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_ThldChk_PVT';
26 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvthcb.pls';
27
28 -- Debug mode
29 --g_debug boolean := FALSE;
30 --g_debug boolean := TRUE;
31
32 ----------------------------------------------------------------------------------------
33 ----------------------------------------------------------------------------------------
34 ----------------------------------------------------------------------------------------
35 ---------------------------------- Threshold Checks-------------------------------------
36 ----------------------------------------------------------------------------------------
37 ----------------------------------------------------------------------------------------
38 ----------------------------------------------------------------------------------------
39
40 /*************************** PRIVATE ROUTINES *********************************/
41
42
43 -- Start of Comments
44 --
45 -- NAME
46 -- Create_Thldchk
47 --
48 -- PURPOSE
49 -- This procedure is to create a row in ams_trigger_checks table that
50 -- satisfy caller needs
51 --
52 -- NOTES
53 --
54 --
55 -- HISTORY
56 -- 06/29/1999 ptendulk created
57 -- 10/26/1999 ptendulk Modified according to new standards
58 -- End of Comments
59
60 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
61 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
62 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
63
64 PROCEDURE Create_thldchk
65 ( p_api_version IN NUMBER,
66 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
67 p_commit IN VARCHAR2 := FND_API.G_False,
68 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2,
72
73 p_thldchk_Rec IN thldchk_rec_type,
74 x_trigger_check_id OUT NOCOPY NUMBER
75 )
76 IS
77
78 l_api_name CONSTANT VARCHAR2(30) := 'Create_Thldchk';
79 l_api_version CONSTANT NUMBER := 1.0;
80 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
81
82
83 -- Status Local Variables
84 l_return_status VARCHAR2(1); -- Return value from procedures
85 l_thldchk_rec thldchk_rec_type := p_thldchk_Rec;
86 l_thldchk_count NUMBER ;
87
88 CURSOR c_trig_chk_seq IS
89 SELECT ams_trigger_checks_s.NEXTVAL
90 FROM DUAL;
91
92 CURSOR c_check_seq(l_my_chk_id VARCHAR2) IS
93 SELECT COUNT(*)
94 FROM ams_trigger_checks
95 WHERE trigger_check_id = l_my_chk_id;
96
97 BEGIN
98
99 -- Standard Start of API savepoint
100 SAVEPOINT Create_Thldchk_PVT;
101
102 --
103 -- Debug Message
104 --
105 IF (AMS_DEBUG_HIGH_ON) THEN
106
107 AMS_Utility_PVT.debug_message(l_full_name||': start');
108 END IF;
109
110 --
111 -- Initialize message list IF p_init_msg_list is set to TRUE.
112 --
113 IF FND_API.to_Boolean( p_init_msg_list ) THEN
114 FND_MSG_PUB.initialize;
115 END IF;
116
117 --
118 -- Standard call to check for call compatibility.
119 --
120 IF NOT FND_API.Compatible_API_Call ( l_api_version,
121 p_api_version,
122 l_api_name,
123 G_PKG_NAME)
124 THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127
128 --
129 -- Initialize API return status to success
130 --
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 --
134 -- API body
135 --
136
137 --
138 -- Validate Trigger Check
139 --
140 IF (AMS_DEBUG_HIGH_ON) THEN
141
142 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
143 END IF;
144
145 Validate_Thldchk ( p_api_version => 1.0
146 ,p_init_msg_list => p_init_msg_list
147 ,p_validation_level => p_validation_level
148 ,x_return_status => l_return_status
149 ,x_msg_count => x_msg_count
150 ,x_msg_data => x_msg_data
151 ,p_thldchk_rec => l_thldchk_rec
152 );
153 -- dbms_output.put_line('Status After Validation : '||l_return_status);
154 -- If any errors happen abort API.
155 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
156 RAISE FND_API.G_EXC_ERROR;
157 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 --
162 -- Insert the Record in Trigger Checks table
163 --
164 IF (AMS_DEBUG_HIGH_ON) THEN
165
166 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
167 END IF;
168
169 --
170 -- Find the Unique Primary Key if not sent
171 --
172 IF l_thldchk_rec.trigger_check_id IS NULL THEN
173 LOOP
174 OPEN c_trig_chk_seq;
175 FETCH c_trig_chk_seq INTO l_thldchk_rec.trigger_check_id;
176 CLOSE c_trig_chk_seq;
177
178 OPEN c_check_seq(l_thldchk_rec.trigger_check_id);
179 FETCH c_check_seq INTO l_thldchk_count;
180 CLOSE c_check_seq;
181
182 EXIT WHEN l_thldchk_count = 0;
183 END LOOP;
184 END IF;
185
186
187 INSERT INTO ams_trigger_checks
188 (trigger_check_id
189
190 -- standard who columns
191 ,last_update_date
192 ,last_updated_by
193 ,creation_date
194 ,created_by
195 ,last_update_login
196
197 ,object_version_number
198 ,trigger_id
199 ,order_number
200 ,chk1_type
201 ,chk1_arc_source_code_from
202 ,chk1_act_object_id
203 ,chk1_source_code
204 ,chk1_source_code_metric_id
205 ,chk1_source_code_metric_type
206 ,chk1_to_chk2_operator_type
207 ,chk2_type
208 ,chk2_value
209 ,chk2_low_value
210 ,chk2_high_value
211 ,chk2_uom_code
212 ,chk2_currency_code
213 ,chk2_arc_source_code_from
214 ,chk2_act_object_id
215 ,chk2_source_code
216 ,chk2_source_code_metric_id
217 ,chk2_source_code_metric_type
218 ,chk2_workbook_name
219 ,chk2_workbook_owner
220 ,chk2_worksheet_name
221
222 )
223 VALUES
224 (
225 l_thldchk_rec.trigger_check_id
226
227 -- standard who columns
228 ,sysdate
229 ,FND_GLOBAL.User_Id
230 ,sysdate
231 ,FND_GLOBAL.User_Id
232 ,FND_GLOBAL.Conc_Login_Id
233
234 ,1 -- Object Version Number
235 ,l_thldchk_rec.trigger_id
236 ,1 -- Order Number
237 ,l_thldchk_rec.chk1_type
238 ,l_thldchk_rec.chk1_arc_source_code_from
239 ,l_thldchk_rec.chk1_act_object_id
240 ,l_thldchk_rec.chk1_source_code
241 ,l_thldchk_rec.chk1_source_code_metric_id
242 ,l_thldchk_rec.chk1_source_code_metric_type
243 ,l_thldchk_rec.chk1_to_chk2_operator_type
244 ,l_thldchk_rec.chk2_type
245 ,l_thldchk_rec.chk2_value
246 ,l_thldchk_rec.chk2_low_value
247 ,l_thldchk_rec.chk2_high_value
248 ,l_thldchk_rec.chk2_uom_code
249 ,l_thldchk_rec.chk2_currency_code
250 ,l_thldchk_rec.chk2_arc_source_code_from
251 ,l_thldchk_rec.chk2_act_object_id
252 ,l_thldchk_rec.chk2_source_code
253 ,l_thldchk_rec.chk2_source_code_metric_id
254 ,l_thldchk_rec.chk2_source_code_metric_type
255 ,l_thldchk_rec.chk2_workbook_name
256 ,l_thldchk_rec.chk2_workbook_owner
257 ,l_thldchk_rec.chk2_worksheet_name
258
259 );
260
261 -- set OUT value
262 x_trigger_check_id := l_thldchk_rec.trigger_check_id;
263
264 --
265 -- END of API body.
266 --
267
268 --
269 -- Standard check of p_commit.
270 --
271 IF FND_API.To_Boolean ( p_commit )
272 THEN
273 COMMIT WORK;
274 END IF;
275
276 --
277 -- Standard call to get message count AND IF count is 1, get message info.
278 --
279 FND_MSG_PUB.Count_AND_Get
280 ( p_count => x_msg_count,
281 p_data => x_msg_data,
282 p_encoded => FND_API.G_FALSE
283 );
284
285 IF (AMS_DEBUG_HIGH_ON) THEN
286
287
288
289 AMS_Utility_PVT.debug_message(l_full_name ||': end');
290
291 END IF;
292
293
294 EXCEPTION
295 WHEN FND_API.G_EXC_ERROR THEN
296 ROLLBACK TO Create_Thldchk_PVT;
297 x_return_status := FND_API.G_RET_STS_ERROR ;
298
299 FND_MSG_PUB.Count_AND_Get
300 ( p_count => x_msg_count,
301 p_data => x_msg_data,
302 p_encoded => FND_API.G_FALSE
303 );
304
305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
306 ROLLBACK TO Create_Thldchk_PVT;
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
308
309 FND_MSG_PUB.Count_AND_Get
310 ( p_count => x_msg_count,
311 p_data => x_msg_data,
312 p_encoded => FND_API.G_FALSE
313 );
314
315 WHEN OTHERS THEN
316 ROLLBACK TO Create_Thldchk_PVT;
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318
319 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
320 THEN
321 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
322 END IF;
323
324 FND_MSG_PUB.Count_AND_Get
325 ( p_count => x_msg_count,
326 p_data => x_msg_data,
327 p_encoded => FND_API.G_FALSE
328 );
329
330 END Create_Thldchk;
331
332
333 -- Start of Comments
334 --
335 -- NAME
336 -- Delete_Thldchk
337 --
338 -- PURPOSE
339 -- This procedure is to delete a ams_trigger_checks table that satisfy caller needs
340 --
341 -- NOTES
342 -- This procedure won't be used from Hornet release as triggers will have to have
343 -- check associated with it.
344 --
345 -- HISTORY
346 -- 06/29/1999 ptendulk created
347 -- 10/26/1999 ptendulk Modified according to new standards
348 -- End of Comments
349
350 PROCEDURE Delete_Thldchk
351 ( p_api_version IN NUMBER,
352 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
353 p_commit IN VARCHAR2 := FND_API.G_False,
354
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358
359 p_trigger_check_id IN NUMBER,
360 p_object_version_number IN NUMBER
361 )
362 IS
363
364 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Thldchk';
365 l_api_version CONSTANT NUMBER := 1.0;
366 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
367
368 BEGIN
369 --
370 -- Standard Start of API savepoint
371 --
372 SAVEPOINT Delete_Thldchk_PVT;
373
374 --
375 -- Debug Message
376 --
377 IF (AMS_DEBUG_HIGH_ON) THEN
378
379 AMS_Utility_PVT.debug_message(l_full_name||': start');
380 END IF;
381
382 --
383 -- Initialize message list IF p_init_msg_list is set to TRUE.
384 --
385 IF FND_API.to_Boolean( p_init_msg_list ) THEN
386 FND_MSG_PUB.initialize;
387 END IF;
388
389 --
390 -- Standard call to check for call compatibility.
391 --
392 IF NOT FND_API.Compatible_API_Call (l_api_version,
393 p_api_version,
394 l_api_name,
395 G_PKG_NAME)
396 THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398 END IF;
399
400 -- Initialize API return status to success
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 --
404 -- API body
405 --
406
407 --
408 -- Debug Message
409 --
410 IF (AMS_DEBUG_HIGH_ON) THEN
411
412 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
413 END IF;
414
415 --
416 -- Debug Message
417 --
418
419 -- Call Private API to cascade delete any children data if necessary
420
421 DELETE FROM ams_trigger_checks
422 WHERE trigger_check_id = p_trigger_check_id
423 AND object_version_number = p_object_version_number ;
424
425 IF (SQL%NOTFOUND) THEN
426 -- Error, check the msg level and added an error message to the
427 -- API message list
428 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
429 THEN -- MMSG
430 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
431 FND_MSG_PUB.Add;
432 END IF;
433
434 RAISE FND_API.G_EXC_ERROR;
435 END IF;
436
437 --
438 -- END of API body.
439 --
440 --
441 -- Standard check of p_commit.
442 --
443 IF FND_API.To_Boolean ( p_commit )
444 THEN
445 COMMIT WORK;
446 END IF;
447
448 --
449 -- Standard call to get message count AND IF count is 1, get message info.
450 --
451 FND_MSG_PUB.Count_AND_Get
452 ( p_count => x_msg_count,
453 p_data => x_msg_data,
454 p_encoded => FND_API.G_FALSE
455 );
456
457 EXCEPTION
458 WHEN FND_API.G_EXC_ERROR THEN
459 ROLLBACK TO Delete_Thldchk_PVT;
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461
462 FND_MSG_PUB.Count_AND_Get
463 ( p_count => x_msg_count,
464 p_data => x_msg_data,
465 p_encoded => FND_API.G_FALSE
466 );
467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
468
472 FND_MSG_PUB.Count_AND_Get
469 ROLLBACK TO Delete_Thldchk_PVT;
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471
473 ( p_count => x_msg_count,
474 p_data => x_msg_data,
475 p_encoded => FND_API.G_FALSE
476 );
477 WHEN OTHERS THEN
478
479 ROLLBACK TO Delete_Thldchk_PVT;
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
481
482 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
483 THEN
484 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
485 END IF;
486
487 FND_MSG_PUB.Count_AND_Get
488 ( p_count => x_msg_count,
489 p_data => x_msg_data,
490 p_encoded => FND_API.G_FALSE
491 );
492 END Delete_Thldchk;
493
494 -- Start of Comments
495 --
496 -- NAME
497 -- Lock_Thldchk
498 --
499 -- PURPOSE
500 -- This procedure is to lock a ams_trigger_checks table that satisfy caller needs
501 --
502 -- NOTES
503 --
504 --
505 -- HISTORY
506 -- 06/29/1999 ptendulk created
507 -- 10/26/1999 ptendulk Modified according to new standards
508 -- End of Comments
509
510 PROCEDURE Lock_Thldchk
511 ( p_api_version IN NUMBER,
512 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
513
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2,
517
518 p_trigger_check_id IN NUMBER,
519 p_object_version_number IN NUMBER
520 )
521 IS
522
523 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Thldchk';
524 l_api_version CONSTANT NUMBER := 1.0;
525 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
526
527
528 -- Status Local Variables
529 l_check_id NUMBER; -- Return value from procedures
530
531 CURSOR C_ams_trigger_checks IS
532 SELECT trigger_check_id
533 FROM ams_trigger_checks
534 WHERE trigger_check_id = p_trigger_check_id
535 AND object_version_number = p_object_version_number
536 FOR UPDATE of trigger_check_id NOWAIT;
537
538 BEGIN
539 --
540 -- Debug Message
541 --
542 IF (AMS_DEBUG_HIGH_ON) THEN
543
544 AMS_Utility_PVT.debug_message(l_full_name||': start');
545 END IF;
546
547 --
548 -- Initialize message list if p_init_msg_list is set to TRUE.
549 --
550 IF FND_API.to_boolean(p_init_msg_list) THEN
551 FND_MSG_PUB.initialize;
552 END IF;
553
554 --
555 -- Standard call to check for call compatibility.
556 --
557 IF NOT FND_API.Compatible_API_Call ( l_api_version,
558 p_api_version,
559 l_api_name,
560 G_PKG_NAME)
561 THEN
562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 END IF;
564
565 -- Initialize API return status to success
566 x_return_status := FND_API.G_RET_STS_SUCCESS;
567
568 --
569 -- API body
570 --
571
572 --
573 -- Lock the Trigger Check
574 --
575 IF (AMS_DEBUG_HIGH_ON) THEN
576
577 AMS_Utility_PVT.debug_message(l_full_name||': lock');
578 END IF;
579
580 -- Perform the database operation
581 OPEN c_ams_trigger_checks;
582 FETCH c_ams_trigger_checks INTO l_check_id;
583 IF (c_ams_trigger_checks%NOTFOUND) THEN
584 CLOSE c_ams_trigger_checks;
585 -- Error, check the msg level and added an error message to the
586 -- API message list
587 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
588 THEN -- MMSG
589 FND_MESSAGE.Set_Name('FND', 'AMS_API_RECORD_NOT_FOUND');
590 FND_MSG_PUB.Add;
591 END IF;
592
593 RAISE FND_API.G_EXC_ERROR;
594 END IF;
595
596 CLOSE C_ams_trigger_checks;
597
598 --
599 -- END of API body.
600 --
601
602 --
603 -- Standard call to get message count AND IF count is 1, get message info.
604 --
605 FND_MSG_PUB.Count_AND_Get
606 ( p_count => x_msg_count,
607 p_data => x_msg_data,
608 p_encoded => FND_API.G_FALSE
609 );
610 --
611 -- Debug Message
612 --
613 IF (AMS_DEBUG_HIGH_ON) THEN
614
615 AMS_Utility_PVT.debug_message(l_full_name ||': end');
616 END IF;
617
618 EXCEPTION
619 WHEN FND_API.G_EXC_ERROR THEN
620 x_return_status := FND_API.G_RET_STS_ERROR ;
621
622 FND_MSG_PUB.Count_AND_Get
623 ( p_count => x_msg_count,
624 p_data => x_msg_data,
625 p_encoded => FND_API.G_FALSE
626 );
627
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
630
634 p_encoded => FND_API.G_FALSE
631 FND_MSG_PUB.Count_AND_Get
632 ( p_count => x_msg_count,
633 p_data => x_msg_data,
635 );
636
637 WHEN AMS_UTILITY_PVT.RESOURCE_LOCKED THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
639
640 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
641 THEN -- MMSG
642 FND_MESSAGE.SET_NAME('AMS','AMS_API_RESOURCE_LOCKED');
643 FND_MSG_PUB.Add;
644 END IF;
645
646 FND_MSG_PUB.Count_AND_Get
647 ( p_count => x_msg_count,
648 p_data => x_msg_data,
649 p_encoded => FND_API.G_FALSE
650 );
651
652 WHEN OTHERS THEN
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
654
655 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
656 THEN
657 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
658 END IF;
659
660 FND_MSG_PUB.Count_AND_Get
661 ( p_count => x_msg_count,
662 p_data => x_msg_data,
663 p_encoded => FND_API.G_FALSE
664 );
665
666 END Lock_Thldchk;
667
668 -- Start of Comments
669 --
670 -- NAME
671 -- Update_Thldchk
672 --
673 -- PURPOSE
674 -- This procedure is to update a ams_trigger_checks table that satisfy caller needs
675 --
676 -- NOTES
677 --
678 --
679 -- HISTORY
680 -- 06/29/1999 ptendulk created
681 -- 10/26/1999 ptendulk Modified according to new standards
682 -- End of Comments
683
684 PROCEDURE Update_Thldchk
685 ( p_api_version IN NUMBER,
686 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
687 p_commit IN VARCHAR2 := FND_API.G_False,
688 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
689
690 x_return_status OUT NOCOPY VARCHAR2,
691 x_msg_count OUT NOCOPY NUMBER,
692 x_msg_data OUT NOCOPY VARCHAR2,
693
694 p_thldchk_rec IN thldchk_rec_type
695 )
696 IS
697
698 l_api_name CONSTANT VARCHAR2(30) := 'Update_Thldchk';
699 l_api_version CONSTANT NUMBER := 1.0;
700 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
701 --
702 -- Status Local Variables
703 --
704 l_return_status VARCHAR2(1); -- Return value from procedures
705 l_thldchk_rec thldchk_rec_type ;
706
707 BEGIN
708 --
709 -- Standard Start of API savepoint
710 --
711 SAVEPOINT Update_Thldchk_PVT;
712
713 --
714 -- Debug Message
715 --
716 IF (AMS_DEBUG_HIGH_ON) THEN
717
718 AMS_Utility_PVT.debug_message(l_full_name||': start');
719 END IF;
720
721 --
722 -- Initialize message list IF p_init_msg_list is set to TRUE.
723 --
724 IF FND_API.to_Boolean( p_init_msg_list ) THEN
725 FND_MSG_PUB.initialize;
726 END IF;
727
728 --
729 -- Standard call to check for call compatibility.
730 --
731 IF NOT FND_API.Compatible_API_Call ( l_api_version,
732 p_api_version,
733 l_api_name,
734 G_PKG_NAME)
735 THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738
739 --
740 -- Initialize API return status to success
741 --
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743
744 --
745 -- API body
746 --
747
748 --
749 -- Debug Message
750 --
751 IF (AMS_DEBUG_HIGH_ON) THEN
752
753 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
754 END IF;
755
756 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
757 Check_thldchk_Items(
758 p_thldchk_rec => p_thldchk_rec,
759 p_validation_mode => JTF_PLSQL_API.g_update,
760 x_return_status => l_return_status
761 );
762
763 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
764 RAISE FND_API.g_exc_unexpected_error;
765 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
766 RAISE FND_API.g_exc_error;
767 END IF;
768 END IF;
769
770 -- replace g_miss_char/num/date with current column values
771 Complete_Thldchk_Rec(p_thldchk_rec, l_thldchk_rec);
772
773 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
774 Check_Thldchk_Record(
775 p_thldchk_rec => p_thldchk_rec,
776 p_complete_rec => l_thldchk_rec,
777 x_return_status => l_return_status
778 );
779 -- dbms_output.put_line('Status after Validation : '||l_return_status);
780 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
781 RAISE FND_API.g_exc_unexpected_error;
782 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
783 RAISE FND_API.g_exc_error;
787
784 END IF;
785 END IF;
786
788 UPDATE ams_trigger_checks
789 SET
790 last_update_date = SYSDATE
791 ,last_updated_by = FND_GLOBAL.User_Id
792 ,last_update_login = FND_GLOBAL.Conc_Login_Id
793
794 ,object_version_number = l_thldchk_rec.object_version_number + 1
795 ,trigger_id = l_thldchk_rec.trigger_id
796 ,order_number = l_thldchk_rec.order_number
797 ,chk1_type = l_thldchk_rec.chk1_type
798 ,chk1_arc_source_code_from = l_thldchk_rec.chk1_arc_source_code_from
799 ,chk1_act_object_id = l_thldchk_rec.chk1_act_object_id
800 ,chk1_source_code = l_thldchk_rec.chk1_source_code
801 ,chk1_source_code_metric_id = l_thldchk_rec.chk1_source_code_metric_id
802 ,chk1_source_code_metric_type = l_thldchk_rec.chk1_source_code_metric_type
803 ,chk1_to_chk2_operator_type = l_thldchk_rec.chk1_to_chk2_operator_type
804 ,chk2_type = l_thldchk_rec.chk2_type
805 ,chk2_value = l_thldchk_rec.chk2_value
806 ,chk2_low_value = l_thldchk_rec.chk2_low_value
807 ,chk2_high_value = l_thldchk_rec.chk2_high_value
808 ,chk2_uom_code = l_thldchk_rec.chk2_uom_code
809 ,chk2_currency_code = l_thldchk_rec.chk2_currency_code
810 ,chk2_source_code = l_thldchk_rec.chk2_source_code
811 ,chk2_arc_source_code_from = l_thldchk_rec.chk2_arc_source_code_from
812 ,chk2_act_object_id = l_thldchk_rec.chk2_act_object_id
813 ,chk2_source_code_metric_id = l_thldchk_rec.chk2_source_code_metric_id
814 ,chk2_source_code_metric_type = l_thldchk_rec.chk2_source_code_metric_type
815 ,chk2_workbook_name = l_thldchk_rec.chk2_workbook_name
816 ,chk2_workbook_owner = l_thldchk_rec.chk2_workbook_owner
817 ,chk2_worksheet_name = l_thldchk_rec.chk2_worksheet_name
818 WHERE trigger_check_id = l_thldchk_Rec.trigger_check_id
819 AND object_version_number = l_thldchk_Rec.object_version_number;
820
821 IF (SQL%NOTFOUND) THEN
822 -- Error, check the msg level and added an error message to the
823 -- API message list
824 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
825 THEN -- MMSG
826 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
827 FND_MSG_PUB.Add;
828 END IF;
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831
832 --
833 -- END of API body.
834 --
835
836 -- Standard check of p_commit.
837 IF FND_API.To_Boolean ( p_commit )
838 THEN
839 COMMIT WORK;
840 END IF;
841
842 --
843 -- Standard call to get message count AND IF count is 1, get message info.
844 --
845 FND_MSG_PUB.Count_AND_Get
846 ( p_count => x_msg_count,
847 p_data => x_msg_data,
848 p_encoded => FND_API.G_FALSE
849 );
850
851 --
852 -- Debug Message
853 --
854 IF (AMS_DEBUG_HIGH_ON) THEN
855
856 AMS_Utility_PVT.debug_message(l_full_name ||': end');
857 END IF;
858
859 EXCEPTION
860 WHEN FND_API.G_EXC_ERROR THEN
861 ROLLBACK TO Update_Thldchk_PVT;
862 x_return_status := FND_API.G_RET_STS_ERROR ;
863
864 FND_MSG_PUB.Count_AND_Get
865 ( p_count => x_msg_count,
866 p_data => x_msg_data,
867 p_encoded => FND_API.G_FALSE
868 );
869
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871 ROLLBACK TO Update_Thldchk_PVT;
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873
874 FND_MSG_PUB.Count_AND_Get
875 ( p_count => x_msg_count,
876 p_data => x_msg_data,
877 p_encoded => FND_API.G_FALSE
878 );
879
880 WHEN OTHERS THEN
881 ROLLBACK TO Update_Thldchk_PVT;
882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
883
884 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
885 THEN
886 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
887 END IF;
888
889 FND_MSG_PUB.Count_AND_Get
890 ( p_count => x_msg_count,
891 p_data => x_msg_data,
892 p_encoded => FND_API.G_FALSE
893 );
894 END Update_Thldchk;
895
896 -- Start of Comments
897 --
898 -- NAME
899 -- Validate_ThldChk
900 --
901 -- PURPOSE
902 -- This procedure is to validate a ams_trigger_checks table that satisfy caller needs
903 --
904 -- NOTES
905 --
906 --
907 -- HISTORY
908 -- 06/28/1999 ptendulk created
909 -- 10/26/1999 ptendulk Modified according to new standards
910 -- End of Comments
911
912 PROCEDURE Validate_Thldchk
913 ( p_api_version IN NUMBER,
914 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
915 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
919
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_msg_count OUT NOCOPY NUMBER,
918 x_msg_data OUT NOCOPY VARCHAR2,
920 p_thldchk_Rec IN thldchk_rec_type
921
922 ) IS
923
924 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Thldchk';
925 l_api_version CONSTANT NUMBER := 1.0;
926 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
927
928
929 -- Status Local Variables
930 l_return_status VARCHAR2(1); -- Return value from procedures
931
932 BEGIN
933
934 --
935 -- Debug Message
936 --
937 IF (AMS_DEBUG_HIGH_ON) THEN
938
939 AMS_Utility_PVT.debug_message(l_full_name||': start');
940 END IF;
941
942 -- Initialize message list if p_init_msg_list is set to TRUE.
943 IF FND_API.to_Boolean( p_init_msg_list ) THEN
944 FND_MSG_PUB.initialize;
945 END IF;
946
947 -- Standard call to check for call compatibility.
948 IF NOT FND_API.Compatible_API_Call ( l_api_version,
949 p_api_version,
950 l_api_name,
951 G_PKG_NAME)
952 THEN
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954 END IF;
955
956 --
957 -- Initialize API return status to success
958 --
959 x_return_status := FND_API.G_RET_STS_SUCCESS;
960
961 --
962 -- API body
963 --
964 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
965 Check_Thldchk_Items(
966 p_thldchk_rec => p_thldchk_rec,
967 p_validation_mode => JTF_PLSQL_API.g_create,
968 x_return_status => l_return_status
969 );
970
971 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
972 RAISE FND_API.g_exc_unexpected_error;
973 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
974 RAISE FND_API.g_exc_error;
975 END IF;
976 END IF;
977
978 --
979 -- Debug Message
980 --
981 IF (AMS_DEBUG_HIGH_ON) THEN
982
983 AMS_Utility_PVT.debug_message(l_full_name||': check record');
984 END IF;
985
986 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
987 Check_Thldchk_Record(
988 p_thldchk_rec => p_thldchk_rec,
989 p_complete_rec => NULL,
990 x_return_status => l_return_status
991 );
992 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
993 RAISE FND_API.g_exc_unexpected_error;
994 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
995 RAISE FND_API.g_exc_error;
996 END IF;
997 END IF;
998
999 --
1000 -- Standard call to get message count AND IF count is 1, get message info.
1001 --
1002 FND_MSG_PUB.Count_AND_Get
1003 ( p_count => x_msg_count,
1004 p_data => x_msg_data,
1005 p_encoded => FND_API.G_FALSE
1006 );
1007
1008 --
1009 -- Debug Message
1010 --
1011 IF (AMS_DEBUG_HIGH_ON) THEN
1012
1013 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1014 END IF;
1015
1016 EXCEPTION
1017
1018 WHEN FND_API.G_EXC_ERROR THEN
1019 x_return_status := FND_API.G_RET_STS_ERROR ;
1020 FND_MSG_PUB.Count_AND_Get
1021 ( p_count => x_msg_count,
1022 p_data => x_msg_data,
1023 p_encoded => FND_API.G_FALSE
1024 );
1025
1026 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1028 FND_MSG_PUB.Count_AND_Get
1029 ( p_count => x_msg_count,
1030 p_data => x_msg_data,
1031 p_encoded => FND_API.G_FALSE
1032 );
1033
1034 WHEN OTHERS THEN
1035 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1036 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1037 THEN
1038 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1039 END IF;
1040
1041 FND_MSG_PUB.Count_AND_Get
1042 ( p_count => x_msg_count,
1043 p_data => x_msg_data,
1044 p_encoded => FND_API.G_FALSE
1045 );
1046
1047 END Validate_thldChk;
1048
1049
1050 -- Start of Comments
1051 --
1052 -- NAME
1053 ---- Check_Thldchk_Req_Items
1054 --
1055 -- PURPOSE
1056 -- This procedure is to check required parameters that satisfy caller needs.
1057 --
1058 -- NOTES
1059 --
1060 --
1061 -- HISTORY
1062 -- 02/28/1999 ptendulk created
1063 -- 10/26/1999 ptendulk Modified according to new standards
1064 -- End of Comments
1065
1066 PROCEDURE Check_Thldchk_Req_Items
1067 ( p_thldchk_rec IN thldchk_rec_type,
1068 x_return_status OUT NOCOPY VARCHAR2
1072 BEGIN
1069 )
1070 IS
1071
1073 -- Initialize API/Procedure return status to success
1074 x_return_status := FND_API.G_Ret_Sts_Success;
1075
1076 --
1077 -- Trigger ID
1078 --
1079 IF p_thldchk_rec.trigger_id IS NULL
1080 THEN
1081 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_TRIG_ID');
1082 x_return_status := FND_API.G_RET_STS_ERROR;
1083 RETURN;
1084 END IF;
1085
1086 --
1087 -- Chk1_type
1088 --
1089 IF p_thldchk_rec.chk1_type IS NULL
1090 THEN
1091 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_CHK1_TYPE');
1092 x_return_status := FND_API.G_RET_STS_ERROR;
1093 RETURN;
1094 END IF;
1095
1096 --
1097 -- Chk2_type
1098 --
1099 IF p_thldchk_rec.chk2_type IS NULL
1100 THEN
1101 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_CHK2_TYPE');
1102 x_return_status := FND_API.G_RET_STS_ERROR;
1103 RETURN;
1104 END IF;
1105
1106 --
1107 -- Chk1_to_chk2_operator_type
1108 --
1109 IF p_thldchk_rec.chk1_to_chk2_operator_type IS NULL
1110 THEN
1111 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_OPERATOR');
1112 x_return_status := FND_API.G_RET_STS_ERROR;
1113 -- If any errors happen abort API/Procedure.
1114 RETURN;
1115 END IF;
1116
1117 --
1118 -- chk1_source_code_metric_id
1119 --
1120 IF p_thldchk_rec.chk1_source_code_metric_id IS NULL
1121 THEN
1122 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_MET');
1123 x_return_status := FND_API.G_RET_STS_ERROR;
1124 RETURN;
1125 END IF;
1126
1127 IF p_thldchk_rec.chk1_act_object_id IS NULL
1128 THEN
1129 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_CHK1_OBJ');
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 RETURN;
1132 END IF;
1133
1134 --
1135 -- chk1_source_code_metric_type
1136 --
1137 IF p_thldchk_rec.chk1_source_code_metric_type IS NULL
1138 THEN
1139 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_MET_TYPE');
1140 x_return_status := FND_API.G_RET_STS_ERROR;
1141 -- If any errors happen abort API/Procedure.
1142 RETURN;
1143 END IF;
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 NULL;
1148 END Check_Thldchk_Req_Items;
1149
1150 -- Start of Comments
1151 --
1152 -- NAME
1153 -- Check_ThldChk_uk_Items
1154 --
1155 -- PURPOSE
1156 -- This procedure is to validate Unique Key in AMS_TRIGGER_CHECKs
1157 --
1158 -- NOTES
1159 --
1160 --
1161 -- HISTORY
1162 -- 06/28/1999 ptendulk created
1163 -- 10/26/1999 ptendulk Modified according to new standards
1164 -- End of Comments
1165 PROCEDURE Check_Thldchk_Uk_Items(
1166 p_thldchk_rec IN thldchk_rec_type,
1167 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1168 x_return_status OUT NOCOPY VARCHAR2
1169 )
1170 IS
1171 l_valid_flag VARCHAR2(1);
1172 l_where_clause VARCHAR2(500);
1173 BEGIN
1174
1175 x_return_status := FND_API.g_ret_sts_success;
1176
1177 -- For create_thldchk, when trigger_check_id is passed in, we need to
1178 -- check if this trigger_check_id is unique.
1179 IF p_validation_mode = JTF_PLSQL_API.g_create
1180 AND p_thldchk_rec.trigger_check_id IS NOT NULL
1181 THEN
1182 IF AMS_Utility_PVT.check_uniqueness(
1183 'ams_trigger_checks',
1184 'trigger_check_id = ' || p_thldchk_rec.trigger_check_id
1185 ) = FND_API.g_false
1186 THEN
1187 AMS_Utility_PVT.Error_Message('AMS_TRIG_DUPLICATE_CHECK');
1188 x_return_status := FND_API.g_ret_sts_error;
1189 RETURN;
1190 END IF;
1191 END IF;
1192 -- check other unique items
1193
1194 -- Check if Trigger_id is unique. Need to handle create and
1195 -- update differently.
1196 -- Unique TRIGGER_NAME and TRIGGER_CREATED_FOR
1197 l_where_clause := ' trigger_id = '|| p_thldchk_rec.trigger_id ;
1198
1199 -- For Updates, must also check that uniqueness is not checked against the same record.
1200 IF p_validation_mode <> JTF_PLSQL_API.g_create THEN
1201 l_where_clause := l_where_clause || ' AND trigger_check_id <> ' || p_thldchk_rec.trigger_check_id;
1202 END IF;
1203
1204 IF AMS_Utility_PVT.Check_Uniqueness(
1205 p_table_name => 'ams_trigger_checks',
1206 p_where_clause => l_where_clause
1207 ) = FND_API.g_false
1208 THEN
1209 AMS_Utility_PVT.Error_Message('AMS_TRIG_DUP_TRIG_ID');
1210 x_return_status := FND_API.g_ret_sts_error;
1211 RETURN;
1212 END IF;
1213
1214 END Check_Thldchk_Uk_Items;
1215
1216 -- Start of Comments
1217 --
1218 -- NAME
1219 -- Check_ThldChk_FK_Items
1220 --
1221 -- PURPOSE
1222 -- This procedure is to validate ams_trigger_checks Foreign Key items
1223 --
1224 -- NOTES
1225 --
1226 --
1227 -- HISTORY
1228 -- 06/28/1999 ptendulk Created
1229 -- 10/26/1999 ptendulk Modified according to new standards
1230 -- End of Comments
1234 )
1231 PROCEDURE Check_Thldchk_Fk_Items(
1232 p_thldchk_rec IN thldchk_rec_type,
1233 x_return_status OUT NOCOPY VARCHAR2
1235 IS
1236 l_table_name VARCHAR2(30);
1237 l_pk_name VARCHAR2(30);
1238 l_pk_value VARCHAR2(30);
1239 l_pk_data_type VARCHAR2(30);
1240 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1241 BEGIN
1242 --
1243 -- Initialize API/Procedure return status to success
1244 --
1245 x_return_status := FND_API.g_ret_sts_success;
1246
1247 --
1248 -- Trigger ID
1249 --
1250 IF p_thldchk_rec.trigger_id <> FND_API.G_MISS_NUM
1251 THEN
1252 l_table_name := 'AMS_TRIGGERS' ;
1253 l_pk_name := 'trigger_id' ;
1254 l_pk_value := p_thldchk_rec.trigger_id;
1255 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
1256 l_additional_where_clause := NULL ;
1257
1258 IF AMS_Utility_PVT.Check_Fk_Exists
1259 (p_table_name => l_table_name
1260 ,p_PK_name => l_pk_name
1261 ,p_PK_value => l_pk_value
1262 ,p_pk_data_type => l_pk_data_type
1263 ,p_additional_where_clause => l_additional_where_clause
1264 ) = FND_API.G_FALSE THEN
1265 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_TRIGGER_ID');
1266 x_return_status := FND_API.G_RET_STS_ERROR;
1267 -- If any errors happen abort API/Procedure.
1268 RETURN;
1269 END IF;
1270
1271 END IF;
1272
1273 End Check_Thldchk_Fk_Items ;
1274
1275 -- Start of Comments
1276 --
1277 -- NAME
1278 -- Check_ThldChk_Lookup_Items
1279 --
1280 -- PURPOSE
1281 -- This procedure is to validate ams_trigger_checks Lookup items
1282 --
1283 -- NOTES
1284 --
1285 --
1286 -- HISTORY
1287 -- 06/28/1999 ptendulk Created
1288 -- 10/26/1999 ptendulk Modified according to new standards
1289 -- End of Comments
1290 PROCEDURE Check_Thldchk_Lookup_Items(
1291 p_thldchk_rec IN thldchk_rec_type,
1292 x_return_status OUT NOCOPY VARCHAR2
1293 )
1294 IS
1295 l_table_name VARCHAR2(30);
1296 l_pk_name VARCHAR2(30);
1297 l_pk_value VARCHAR2(30);
1298 l_pk_data_type VARCHAR2(30);
1299 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1300 BEGIN
1301 --
1302 -- Initialize API/Procedure return status to success
1303 --
1304 x_return_status := FND_API.g_ret_sts_success;
1305
1306 --
1307 -- Trigger ID
1308 --
1309 -- Check Chk2_type
1310 IF p_thldchk_rec.chk2_type <> FND_API.G_MISS_CHAR
1311 THEN
1312 IF AMS_Utility_PVT.Check_Lookup_Exists
1313 ( p_lookup_table_name => 'AMS_LOOKUPS'
1314 ,p_lookup_type => 'AMS_TRIGGER_CHK_TYPE'
1315 ,p_lookup_code => p_thldchk_rec.chk2_type
1316 ) = FND_API.G_FALSE
1317 THEN
1318 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_CHECK_TYPE');
1319 x_return_status := FND_API.G_RET_STS_ERROR;
1320 RETURN;
1321 END IF;
1322 END IF;
1323
1324 --
1325 -- Check CHK1_SOURCE_CODE_METRIC_TYPE
1326 --
1327 IF p_thldchk_rec.chk1_source_code_metric_type <> FND_API.G_MISS_CHAR
1328 THEN
1329 IF AMS_Utility_PVT.Check_Lookup_Exists
1330 ( p_lookup_table_name => 'AMS_LOOKUPS'
1331 ,p_lookup_type => 'AMS_MONITOR_CHK_METRIC_TYPE'
1332 ,p_lookup_code => p_thldchk_rec.chk1_source_code_metric_type
1333 ) = FND_API.G_FALSE
1334 THEN
1335 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_METRIC_TYPE');
1336 x_return_status := FND_API.G_RET_STS_ERROR;
1337 RETURN;
1338 END IF;
1339 END IF;
1340
1341 End Check_Thldchk_Lookup_Items ;
1342
1343 -- Start of Comments
1344 --
1345 -- NAME
1346 -- Check_ThldChk_Items
1347 --
1348 -- PURPOSE
1349 -- This procedure is to validate ams_trigger_checks items
1350 --
1351 -- NOTES
1352 --
1353 --
1354 -- HISTORY
1355 -- 06/28/1999 ptendulk Created
1356 -- 10/26/1999 ptendulk Modified according to new standards
1357 -- End of Comments
1358 PROCEDURE Check_Thldchk_Items(
1359 p_thldchk_rec IN thldchk_rec_type,
1360 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1361 x_return_status OUT NOCOPY VARCHAR2
1362 )
1363 IS
1364 BEGIN
1365
1366 Check_Thldchk_Req_Items(
1367 p_thldchk_rec => p_thldchk_rec,
1368 x_return_status => x_return_status
1369 );
1370 -- dbms_output.put_line('Status After REQ Test : '||x_return_status);
1371 IF x_return_status <> FND_API.g_ret_sts_success THEN
1372 RETURN;
1373 END IF;
1374
1375 Check_Thldchk_Uk_Items(
1376 p_thldchk_rec => p_thldchk_rec,
1377 p_validation_mode => p_validation_mode,
1378 x_return_status => x_return_status
1382 RETURN;
1379 );
1380 -- dbms_output.put_line('Status After UK Test : '||x_return_status);
1381 IF x_return_status <> FND_API.g_ret_sts_success THEN
1383 END IF;
1384
1385 Check_Thldchk_Fk_Items(
1386 p_thldchk_rec => p_thldchk_rec,
1387 x_return_status => x_return_status
1388 );
1389 -- dbms_output.put_line('Status After FK Test : '||x_return_status);
1390 IF x_return_status <> FND_API.g_ret_sts_success THEN
1391 RETURN;
1392 END IF;
1393
1394 Check_Thldchk_Lookup_Items(
1395 p_thldchk_rec => p_thldchk_rec,
1396 x_return_status => x_return_status
1397 );
1398 -- dbms_output.put_line('Status After LOOKUP Test : '||x_return_status);
1399 IF x_return_status <> FND_API.g_ret_sts_success THEN
1400 RETURN;
1401 END IF;
1402
1403 END Check_Thldchk_Items;
1404
1405 -- Start of Comments
1406 --
1407 -- NAME
1408 -- Validate_Thldchk_Record
1409 --
1410 -- PURPOSE
1411 -- This procedure is to validate ams_trigger_checks table.
1412 -- This is an example if you need to call validation procedure from the UI site.
1413 --
1414 -- NOTES
1415 --
1416 --
1417 -- HISTORY
1418 -- 07/26/1999 ptendulk Created
1419 -- 10/26/1999 ptendulk Modified according to new standards
1420 -- End of Comments
1421 PROCEDURE Check_Thldchk_Record(
1422 p_thldchk_rec IN thldchk_rec_type,
1423 p_complete_rec IN thldchk_rec_type,
1424 x_return_status OUT NOCOPY VARCHAR2
1425 )
1426 IS
1427 l_thldchk_rec thldchk_rec_type := p_thldchk_rec ;
1428
1429 l_table_name VARCHAR2(30);
1430 l_pk_name VARCHAR2(30);
1431 l_pk_value VARCHAR2(30);
1432 -- added by soagrawa on 23-aug-2002 for bug# 2528692
1433 l_pk_wb_value VARCHAR2(254);
1434 l_pk_data_type VARCHAR2(30);
1435 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1436
1437 CURSOR c_met_det(l_act_met_id NUMBER) IS
1438 SELECT act.metric_uom_code metric_uom_code,
1439 act.transaction_currency_code transaction_currency_code,
1440 met.uom_type
1441 FROM ams_act_metrics_all act,ams_metrics_all_b met
1442 WHERE act.activity_metric_id = l_act_met_id
1443 AND act.metric_id = met.metric_id ;
1444
1445 l_chk1_met_rec c_met_det%ROWTYPE;
1446 l_chk2_met_rec c_met_det%ROWTYPE;
1447
1448 -- CURSOR c_source_det(l_source_code VARCHAR2) IS
1449 -- SELECT campaign_id
1450 -- FROM ams_campaigns_vl
1451 -- WHERE source_code = l_source_code ;
1452
1453 -- Added By ptendulk on Dec30-1999 as
1454 -- Check2 can be coming from Events
1455
1456 -- CURSOR c_source_det_eveh(l_source_code VARCHAR2) IS
1457 -- SELECT event_header_id
1458 -- FROM ams_event_headers_vl
1459 -- WHERE source_code = l_source_code ;
1460
1461 -- CURSOR c_source_det_eveo(l_source_code VARCHAR2) IS
1462 -- SELECT event_offer_id
1463 -- FROM ams_event_offers_vl
1464 -- WHERE source_code = l_source_code ;
1465
1466 -- l_chk1_camp_id NUMBER ;
1467 -- l_chk2_source_id NUMBER ;
1468
1469
1470 BEGIN
1471 --
1472 -- Initialize the Out Variable
1473 --
1474 x_return_status := FND_API.g_ret_sts_success;
1475
1476 -- Check start date time
1477 IF p_thldchk_rec.chk1_arc_source_code_from <> FND_API.G_MISS_CHAR
1478 OR p_thldchk_rec.chk1_act_object_id <> FND_API.G_MISS_NUM
1479 OR p_thldchk_rec.chk1_source_code_metric_id <> FND_API.G_MISS_NUM
1480 OR p_thldchk_rec.chk1_source_code_metric_type <> FND_API.G_MISS_CHAR
1481 OR p_thldchk_rec.chk1_to_chk2_operator_type <> FND_API.G_MISS_CHAR
1482 OR p_thldchk_rec.chk2_type <> FND_API.G_MISS_CHAR
1483 OR p_thldchk_rec.chk2_value <> FND_API.G_MISS_NUM
1484 OR p_thldchk_rec.chk2_low_value <> FND_API.G_MISS_NUM
1485 OR p_thldchk_rec.chk2_high_value <> FND_API.G_MISS_NUM
1486 --OR p_thldchk_rec.chk2_uom_code <> FND_API.G_MISS_CHAR
1487 --OR p_thldchk_rec.chk2_currency_code <> FND_API.G_MISS_CHAR
1488 --OR p_thldchk_rec.chk2_source_code <> FND_API.G_MISS_CHAR
1489 OR p_thldchk_rec.chk2_arc_source_code_from <> FND_API.G_MISS_CHAR
1490 OR p_thldchk_rec.chk2_act_object_id <> FND_API.G_MISS_NUM
1491 OR p_thldchk_rec.chk2_source_code_metric_id <> FND_API.G_MISS_NUM
1492 OR p_thldchk_rec.chk2_source_code_metric_type <> FND_API.G_MISS_CHAR
1493 OR p_thldchk_rec.chk2_workbook_name <> FND_API.G_MISS_CHAR
1494 THEN
1495
1496 IF p_thldchk_rec.chk1_arc_source_code_from = FND_API.G_MISS_CHAR THEN
1497 l_thldchk_rec.chk1_arc_source_code_from := p_complete_rec.chk1_arc_source_code_from ;
1498 END IF;
1499
1500 IF p_thldchk_rec.chk1_act_object_id = FND_API.G_MISS_NUM THEN
1501 l_thldchk_rec.chk1_act_object_id := p_complete_rec.chk1_act_object_id ;
1502 END IF;
1503
1504 --IF p_thldchk_rec.chk1_source_code = FND_API.G_MISS_CHAR THEN
1505 -- l_thldchk_rec.chk1_source_code := p_complete_rec.chk1_source_code ;
1506 --END IF;
1507
1508 IF p_thldchk_rec.chk1_source_code_metric_id = FND_API.G_MISS_NUM THEN
1509 l_thldchk_rec.chk1_source_code_metric_id := p_complete_rec.chk1_source_code_metric_id ;
1513 l_thldchk_rec.chk1_source_code_metric_type := p_complete_rec.chk1_source_code_metric_type ;
1510 END IF;
1511
1512 IF p_thldchk_rec.chk1_source_code_metric_type = FND_API.G_MISS_CHAR THEN
1514 END IF;
1515
1516 IF p_thldchk_rec.chk1_to_chk2_operator_type = FND_API.G_MISS_CHAR THEN
1517 l_thldchk_rec.chk1_to_chk2_operator_type := p_complete_rec.chk1_to_chk2_operator_type ;
1518 END IF;
1519
1520 IF p_thldchk_rec.chk2_type = FND_API.G_MISS_CHAR THEN
1521 l_thldchk_rec.chk2_type := p_complete_rec.chk2_type ;
1522 END IF;
1523
1524 IF p_thldchk_rec.chk2_value = FND_API.G_MISS_NUM THEN
1525 l_thldchk_rec.chk2_value := p_complete_rec.chk2_value ;
1526 END IF;
1527
1528 IF p_thldchk_rec.chk2_low_value = FND_API.G_MISS_NUM THEN
1529 l_thldchk_rec.chk2_low_value := p_complete_rec.chk2_low_value ;
1530 END IF;
1531
1532 IF p_thldchk_rec.chk2_high_value = FND_API.G_MISS_NUM THEN
1533 l_thldchk_rec.chk2_high_value := p_complete_rec.chk2_high_value ;
1534 END IF;
1535
1536 IF p_thldchk_rec.chk2_uom_code = FND_API.G_MISS_CHAR THEN
1537 l_thldchk_rec.chk2_uom_code := p_complete_rec.chk2_uom_code ;
1538 END IF;
1539
1540 IF p_thldchk_rec.chk2_currency_code = FND_API.G_MISS_CHAR THEN
1541 l_thldchk_rec.chk2_currency_code := p_complete_rec.chk2_currency_code ;
1542 END IF;
1543
1544 --IF p_thldchk_rec.chk2_source_code = FND_API.G_MISS_CHAR THEN
1545 -- l_thldchk_rec.chk2_source_code := p_complete_rec.chk2_source_code ;
1546 --END IF;
1547
1548 IF p_thldchk_rec.chk2_arc_source_code_from = FND_API.G_MISS_CHAR THEN
1549 l_thldchk_rec.chk2_arc_source_code_from := p_complete_rec.chk2_arc_source_code_from ;
1550 END IF;
1551
1552 IF p_thldchk_rec.chk2_act_object_id = FND_API.G_MISS_NUM THEN
1553 l_thldchk_rec.chk2_act_object_id := p_complete_rec.chk2_act_object_id ;
1554 END IF;
1555
1556 IF p_thldchk_rec.chk2_source_code_metric_id = FND_API.G_MISS_NUM THEN
1557 l_thldchk_rec.chk2_source_code_metric_id := p_complete_rec.chk2_source_code_metric_id ;
1558 END IF;
1559
1560 IF p_thldchk_rec.chk2_source_code_metric_type = FND_API.G_MISS_CHAR THEN
1561 l_thldchk_rec.chk2_source_code_metric_type := p_complete_rec.chk2_source_code_metric_type ;
1562 END IF;
1563
1564 IF p_thldchk_rec.chk2_workbook_name = FND_API.G_MISS_CHAR THEN
1565 l_thldchk_rec.chk2_workbook_name := p_complete_rec.chk2_workbook_name ;
1566 END IF;
1567
1568
1569 --OPEN c_source_det(l_thldchk_rec.chk1_source_code);
1570 --FETCH c_source_det INTO l_chk1_camp_id ;
1571 --CLOSE c_source_det ;
1572 --
1573 -- Check Chk1_source_code_Metric_id
1574 --
1575
1576 IF (AMS_DEBUG_HIGH_ON) THEN
1577
1578
1579
1580 AMS_Utility_PVT.debug_message('Sonali 1');
1581
1582 END IF;
1583
1584 l_table_name := 'ams_act_metrics_all' ;
1585 l_pk_name := 'activity_metric_id' ;
1586 l_pk_value := l_thldchk_rec.chk1_source_code_metric_id ;
1587 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
1588 l_additional_where_clause := ' arc_act_metric_used_by = '||''''
1589 ||l_thldchk_rec.chk1_arc_source_code_from||''''
1590 ||' AND act_metric_used_by_id = '||l_thldchk_rec.chk1_act_object_id ;
1591
1592 IF (AMS_DEBUG_HIGH_ON) THEN
1593
1594
1595
1596 AMS_Utility_PVT.debug_message('Sonali 2');
1597
1598 END IF;
1599
1600 /* IF AMS_Utility_PVT.Check_FK_Exists (
1601 p_table_name => l_table_name
1602 ,p_pk_name => l_pk_name
1603 ,p_pk_value => l_pk_value
1604 ,p_pk_data_type => l_pk_data_type
1605 ,p_additional_where_clause => l_additional_where_clause
1606 ) = FND_API.G_FALSE
1607 THEN
1608 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_CREATED_FOR');
1609 x_return_status := FND_API.G_RET_STS_ERROR;
1610 RETURN;
1611 END IF;
1612 */
1613 IF (AMS_DEBUG_HIGH_ON) THEN
1614
1615
1616
1617 AMS_Utility_PVT.debug_message('Sonali 3');
1618
1619 END IF;
1620
1621 --
1622 -- Do the not null check first
1623 --
1624 IF l_thldchk_rec.chk2_type = 'METRIC' THEN
1625 IF (l_thldchk_rec.chk2_source_code_metric_id IS NULL OR
1626 l_thldchk_rec.chk2_arc_source_code_from IS NULL OR
1627 l_thldchk_rec.chk2_act_object_id IS NULL )
1628 THEN
1629 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_CHK2_OBJ');
1630 x_return_status := FND_API.G_RET_STS_ERROR;
1631 RETURN;
1632 ELSIF l_thldchk_rec.chk2_source_code_metric_type IS NULL
1633 THEN
1634 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_MET_TYPE');
1635 x_return_status := FND_API.G_RET_STS_ERROR;
1636 RETURN;
1637 END IF ;
1638
1639 IF (AMS_DEBUG_HIGH_ON) THEN
1640
1641
1642
1643 AMS_Utility_PVT.debug_message('Sonali 4');
1644
1648 -- Chk2_source code_metric_id
1645 END IF;
1646
1647 --
1649 --
1650 IF (p_thldchk_rec.chk2_source_code_metric_id IS NOT NULL AND
1651 p_thldchk_rec.chk2_source_code_metric_id <> FND_API.G_MISS_NUM ) OR
1652 (p_thldchk_rec.chk2_act_object_id IS NOT NULL AND
1653 p_thldchk_rec.chk2_act_object_id <> FND_API.G_MISS_NUM )
1654 THEN
1655 -- Added By ptendulk on Dec30-1999 as
1656 -- Check2 can be coming from Events
1657 -- commented by ptendulk on 15-Feb-2001 as not required for the hornet
1658 --IF l_thldchk_rec.chk2_arc_source_code_from = 'CAMP' THEN
1659 -- OPEN c_source_det(l_thldchk_rec.chk2_source_code);
1660 -- FETCH c_source_det INTO l_chk2_source_id ;
1661 -- CLOSE c_source_det ;
1662 --ELSIF l_thldchk_rec.chk2_arc_source_code_from = 'EVEH' THEN
1663 -- OPEN c_source_det_eveh(l_thldchk_rec.chk2_source_code);
1664 -- FETCH c_source_det_eveh INTO l_chk2_source_id ;
1665 -- CLOSE c_source_det_eveh ;
1666 --ELSIF l_thldchk_rec.chk2_arc_source_code_from = 'EVEO' THEN
1667 -- OPEN c_source_det_eveo(l_thldchk_rec.chk2_source_code);
1668 -- FETCH c_source_det_eveo INTO l_chk2_source_id ;
1669 -- CLOSE c_source_det_eveo ;
1670 --END IF;
1671
1672 l_table_name := 'ams_act_metrics_all' ;
1673 l_pk_name := 'activity_metric_id' ;
1674 l_pk_value := l_thldchk_rec.chk2_source_code_metric_id ;
1675 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
1676 l_additional_where_clause := ' arc_act_metric_used_by = '||''''
1677 ||l_thldchk_rec.chk2_arc_source_code_from||''''
1678 ||' AND act_metric_used_by_id = '||l_thldchk_rec.chk2_act_object_id ;
1679
1680 IF (AMS_DEBUG_HIGH_ON) THEN
1681
1682
1683
1684 AMS_Utility_PVT.debug_message('Sonali 5');
1685
1686 END IF;
1687 IF AMS_Utility_PVT.Check_FK_Exists (
1688 p_table_name => l_table_name
1689 ,p_pk_name => l_pk_name
1690 ,p_pk_value => l_pk_value
1691 ,p_pk_data_type => l_pk_data_type
1692 ,p_additional_where_clause => l_additional_where_clause
1693 ) = FND_API.G_FALSE
1694 THEN
1695 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_CHK2_MET');
1696 x_return_status := FND_API.G_RET_STS_ERROR;
1697 RETURN;
1698 END IF;
1699 END IF;
1700
1701 END IF;
1702
1703 IF l_thldchk_rec.chk2_type = 'DIWB' THEN
1704 IF l_thldchk_rec.chk2_workbook_name IS NULL
1705 THEN
1706 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_WB_NAME');
1707 x_return_status := FND_API.G_RET_STS_ERROR;
1708 RETURN;
1709 ELSE
1710 --
1711 -- Chk2_workbook_name
1712 --
1713
1714 IF (AMS_DEBUG_HIGH_ON) THEN
1715
1716
1717
1718 AMS_Utility_PVT.debug_message('Sonali 6');
1719
1720 END IF;
1721
1722 IF (p_thldchk_rec.chk2_workbook_name IS NOT NULL AND
1723 p_thldchk_rec.chk2_workbook_name <> FND_API.G_MISS_CHAR )
1724 THEN
1725 l_table_name := 'ams_discoverer_sql' ;
1726 l_pk_name := 'workbook_name' ;
1727 -- modified by soagrawa on 23-aug-2002 for bug# 2528692
1728 l_pk_wb_value := l_thldchk_rec.chk2_workbook_name ;
1729 l_pk_data_type := AMS_Utility_PVT.G_VARCHAR2;
1730 l_additional_where_clause := NULL ;
1731
1732 IF (AMS_DEBUG_HIGH_ON) THEN
1733
1734
1735
1736 AMS_Utility_PVT.debug_message('Sonali 7');
1737
1738 END IF;
1739
1740 IF AMS_Utility_PVT.Check_FK_Exists (
1741 p_table_name => l_table_name
1742 ,p_pk_name => l_pk_name
1743 -- modified by soagrawa on 23-aug-2002 for bug# 2528692
1744 ,p_pk_value => l_pk_wb_value
1745 ,p_pk_data_type => l_pk_data_type
1746 ,p_additional_where_clause => l_additional_where_clause
1747 ) = FND_API.G_FALSE
1748 THEN
1749 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_WB_NAME');
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751 RETURN;
1752 END IF;
1753 END IF;
1754 END IF;
1755 END IF ;
1756
1757 IF l_thldchk_rec.chk2_type = 'STATIC_VALUE' THEN
1758 --
1759 -- Chk2_low_value and High Value
1760 --
1761 IF l_thldchk_rec.chk1_to_chk2_operator_type = 'BETWEEN' THEN
1762 IF l_thldchk_rec.chk2_low_value IS NULL OR
1763 l_thldchk_rec.chk2_high_value IS NULL
1767 RETURN;
1764 THEN
1765 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_CHK2_RANGE');
1766 x_return_status := FND_API.G_RET_STS_ERROR;
1768 ELSE
1769 IF l_thldchk_rec.chk2_low_value > l_thldchk_rec.chk2_high_value
1770 THEN
1771 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_CHK2_RANGE');
1772 x_return_status := FND_API.G_RET_STS_ERROR;
1773 RETURN;
1774 END IF;
1775 END IF ;
1776 ELSE
1777 IF l_thldchk_rec.chk2_value IS NULL THEN
1778 AMS_Utility_PVT.Error_Message('AMS_TRIG_MISSING_VALUE');
1779 x_return_status := FND_API.G_RET_STS_ERROR ;
1780 RETURN ;
1781 END IF ;
1782 END IF;
1783
1784 END IF ;
1785
1786 -- Start of code commented by ptendulk on 15-Feb-2001 as UOM and frequency will not be used any more
1787 -- dbms_output.put_line('Stat Before Value Check : '||x_return_status);
1788 -- When The RHS of the Check is Value then the Default UOM for the Value will be
1789 -- UOM of the Metric , Same applies for Currencies. But user can always change it.
1790 --
1791 --IF l_thldchk_rec.chk2_value IS NOT NULL OR
1792 -- l_thldchk_rec.chk2_low_value IS NOT NULL OR
1793 -- l_thldchk_rec.chk2_high_value IS NOT NULL
1794 --THEN
1795 -- -- Following code is changed by ptendulk on 14th Feb 2000 as
1796 -- -- UOM is not mandatory now in Metrics now.
1797 -- -- If the uom of chk1 is null then uom of chk2 has to be null
1798 -- OPEN c_met_det(l_thldchk_rec.chk1_source_code_metric_id);
1799 -- FETCH c_met_det INTO l_chk1_met_rec ;
1800 -- CLOSE c_met_det ;
1801
1802 -- IF l_thldchk_rec.chk2_uom_code IS NOT NULL
1803 -- THEN
1804 -- IF l_chk1_met_rec.metric_uom_code IS NULL THEN
1805 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1806 -- THEN
1807 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_UOM_NULL');
1808 -- FND_MSG_PUB.Add;
1809 -- END IF;
1810 -- x_return_status := FND_API.G_RET_STS_ERROR;
1811 -- RETURN;
1812 -- ELSE
1813 -- --
1814 -- --If the RHS of the Check is Static Value Check UOM Entered is Valid
1815 -- --
1816 -- l_table_name := 'MTL_UNITS_OF_MEASURE';
1817 -- l_pk_name := 'UOM_CODE';
1818 -- l_pk_value := l_thldchk_rec.chk2_uom_code;
1819 -- l_pk_data_type := AMS_Utility_PVT.G_VARCHAR2;
1820 -- l_additional_where_clause := ' uom_class = '||''''||l_chk1_met_rec.uom_type||'''';
1821
1822 -- IF AMS_Utility_PVT.Check_FK_Exists (
1823 -- p_table_name => l_table_name
1824 -- ,p_pk_name => l_pk_name
1825 -- ,p_pk_value => l_pk_value
1826 -- ,p_pk_data_type => l_pk_data_type
1827 -- ,p_additional_where_clause => l_additional_where_clause
1828 -- ) = FND_API.G_FALSE
1829 -- THEN
1830 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1831 -- THEN
1832 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_UOM');
1833 -- FND_MSG_PUB.Add;
1834 -- END IF;
1835
1836 -- x_return_status := FND_API.G_RET_STS_ERROR;
1837 -- RETURN;
1838 -- END IF; -- Check_FK_Exists--
1839 -- END IF;
1840 -- ELSE
1841 -- IF l_chk1_met_rec.metric_uom_code IS NOT NULL THEN
1842 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1843 -- THEN
1844 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_UOM_NULL');
1845 -- FND_MSG_PUB.Add;
1846 -- END IF;
1847
1848 -- x_return_status := FND_API.G_RET_STS_ERROR;
1849 -- RETURN;
1850 -- END IF ;
1851 --END IF;
1852
1853
1854 -- IF l_thldchk_rec.chk2_currency_code IS NOT NULL THEN
1855 -- IF l_chk1_met_rec.transaction_currency_code IS NULL THEN
1856 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1857 -- THEN
1858 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_CURR_NULL');
1859 -- FND_MSG_PUB.Add;
1860 -- END IF;
1861
1862 -- x_return_status := FND_API.G_RET_STS_ERROR;
1863 -- RETURN;
1864 -- ELSE
1865 -- --
1866 -- -- If the RHS of the Check is Static Value Check Currency Entered is Valid
1867 -- --
1868 -- l_table_name := 'FND_CURRENCIES';
1869 -- l_pk_name := 'CURRENCY_CODE';
1870 -- l_pk_value := l_thldchk_rec.chk2_currency_code;
1871 -- l_pk_data_type := AMS_Utility_PVT.G_VARCHAR2;
1872 -- l_additional_where_clause := ' enabled_flag = '||''''||'Y'||'''';
1873
1877 -- ,p_pk_value => l_pk_value
1874 -- IF AMS_Utility_PVT.Check_FK_Exists (
1875 -- p_table_name => l_table_name
1876 -- ,p_pk_name => l_pk_name
1878 -- ,p_pk_data_type => l_pk_data_type
1879 -- ,p_additional_where_clause => l_additional_where_clause
1880 -- ) = FND_API.G_FALSE
1881 -- THEN
1882 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1883 -- THEN
1884 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_CURR');
1885 -- FND_MSG_PUB.Add;
1886 -- END IF;
1887
1888 -- x_return_status := FND_API.G_RET_STS_ERROR;
1889 -- RETURN;
1890 -- END IF; -- Check_FK_Exists
1891 -- END IF;
1892 -- ELSE
1893 -- IF l_chk1_met_rec.transaction_currency_code IS NOT NULL THEN
1894 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1895 -- THEN
1896 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_CURR_NULL');
1897 -- FND_MSG_PUB.Add;
1898 --END IF;
1899
1900 --x_return_status := FND_API.G_RET_STS_ERROR;
1901 -- RETURN;
1902 -- END IF ;
1903 -- END IF;
1904 --END IF;
1905 -- Start of code commented by ptendulk on 15-Feb-2001 as UOM and frequency will not be used any more
1906
1907 -- dbms_output.put_line('Before Metric Comparison : '||x_return_status);
1908 -- Check the UOM and The Currency code of both Metrics are same
1909 -- if the RHS of Check is Metrics
1910 --IF l_thldchk_rec.chk2_type = 'METRIC' THEN
1911 -- -- If the RHS of the Check is Metric then Check that the RHS and LHS Metric
1912 -- -- Has same Currency as well as same UOM
1913 -- OPEN c_met_det(l_thldchk_rec.chk1_source_code_metric_id);
1914 -- FETCH c_met_det INTO l_chk1_met_rec ;
1915 --- CLOSE c_met_det ;
1916
1917 -- OPEN c_met_det(l_thldchk_rec.chk2_source_code_metric_id);
1918 -- FETCH c_met_det INTO l_chk2_met_rec ;
1919 -- CLOSE c_met_det ;
1920
1921 -- IF l_chk1_met_rec.transaction_currency_code <> l_chk2_met_rec.transaction_currency_code
1922 -- OR l_chk1_met_rec.metric_uom_code <> l_chk2_met_rec.metric_uom_code THEN
1923 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1924 -- THEN
1925 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_UOM_CURR');
1926 -- FND_MSG_PUB.Add;
1927 -- END IF;
1928
1929 -- x_return_status := FND_API.G_RET_STS_ERROR;
1930 -- RETURN;
1931
1932 -- END IF ;
1933 --END IF;
1934 -- Start of code commented by ptendulk on 15-Feb-2001 as UOM and frequency will not be used any more
1935 END IF;
1936
1937 END Check_Thldchk_Record;
1938
1939 -- Start of Comments
1940 --
1941 -- NAME
1942 -- Init_thldck_rec
1943 --
1944 -- PURPOSE
1945 -- This procedure is to Initialize the check Record before Updation
1946 --
1947 -- NOTES
1948 --
1949 --
1950 -- HISTORY
1951 -- 10/26/1999 ptendulk Created
1952 -- End of Comments
1953 PROCEDURE Init_Thldchk_Rec(
1954 x_thldchk_rec OUT NOCOPY thldchk_rec_type
1955 )
1956 IS
1957 BEGIN
1958 x_thldchk_rec.trigger_check_id := FND_API.G_MISS_NUM ;
1959 x_thldchk_rec.last_update_date := FND_API.G_MISS_DATE ;
1960 x_thldchk_rec.last_updated_by := FND_API.G_MISS_NUM ;
1961 x_thldchk_rec.creation_date := FND_API.G_MISS_DATE ;
1962 x_thldchk_rec.created_by := FND_API.G_MISS_NUM ;
1963 x_thldchk_rec.last_update_login := FND_API.G_MISS_NUM ;
1964 x_thldchk_rec.object_version_number := FND_API.G_MISS_NUM ;
1965 x_thldchk_rec.trigger_id := FND_API.G_MISS_NUM ;
1966 x_thldchk_rec.order_number := FND_API.G_MISS_NUM ;
1967 x_thldchk_rec.chk1_type := FND_API.G_MISS_CHAR ;
1968 x_thldchk_rec.chk1_arc_source_code_from := FND_API.G_MISS_CHAR ;
1969 x_thldchk_rec.chk1_act_object_id := FND_API.G_MISS_NUM ;
1970 x_thldchk_rec.chk1_source_code := FND_API.G_MISS_CHAR ;
1971 x_thldchk_rec.chk1_source_code_metric_id := FND_API.G_MISS_NUM ;
1972 x_thldchk_rec.chk1_source_code_metric_type := FND_API.G_MISS_CHAR ;
1973 x_thldchk_rec.chk1_to_chk2_operator_type := FND_API.G_MISS_CHAR ;
1974 x_thldchk_rec.chk2_type := FND_API.G_MISS_CHAR ;
1975 x_thldchk_rec.chk2_value := FND_API.G_MISS_NUM ;
1976 x_thldchk_rec.chk2_low_value := FND_API.G_MISS_NUM ;
1977 x_thldchk_rec.chk2_high_value := FND_API.G_MISS_NUM ;
1978 x_thldchk_rec.chk2_uom_code := FND_API.G_MISS_CHAR ;
1979 x_thldchk_rec.chk2_currency_code := FND_API.G_MISS_CHAR ;
1980 x_thldchk_rec.chk2_source_code := FND_API.G_MISS_CHAR ;
1981 x_thldchk_rec.chk2_arc_source_code_from := FND_API.G_MISS_CHAR ;
1982 x_thldchk_rec.chk2_act_object_id := FND_API.G_MISS_NUM ;
1983 x_thldchk_rec.chk2_source_code_metric_id := FND_API.G_MISS_NUM ;
1987 x_thldchk_rec.chk2_worksheet_name := FND_API.G_MISS_CHAR ;
1984 x_thldchk_rec.chk2_source_code_metric_type := FND_API.G_MISS_CHAR ;
1985 x_thldchk_rec.chk2_workbook_name := FND_API.G_MISS_CHAR ;
1986 x_thldchk_rec.chk2_workbook_owner := FND_API.G_MISS_CHAR ;
1988
1989
1990 END Init_Thldchk_Rec ;
1991
1992 -- Start of Comments
1993 --
1994 -- NAME
1995 -- Complete_Thldchk_Rec
1996 --
1997 -- PURPOSE
1998 -- This procedure is to Initialize the check Record before Updation
1999 --
2000 -- NOTES
2001 --
2002 --
2003 -- HISTORY
2004 -- 10/26/1999 ptendulk Created
2005 -- End of Comments
2006
2007 PROCEDURE Complete_Thldchk_rec(
2008 p_thldchk_rec IN thldchk_rec_type,
2009 x_complete_rec OUT NOCOPY thldchk_rec_type
2010 )
2011 IS
2012
2013 CURSOR c_thldchk IS
2014 SELECT *
2015 FROM ams_trigger_checks
2016 WHERE trigger_check_id = p_thldchk_rec.trigger_check_id;
2017
2018 l_thldchk_rec c_thldchk%ROWTYPE;
2019
2020 BEGIN
2021
2022 x_complete_rec := p_thldchk_rec;
2023
2024 OPEN c_thldchk;
2025 FETCH c_thldchk INTO l_thldchk_rec;
2026 IF c_thldchk%NOTFOUND THEN
2027 CLOSE c_thldchk;
2028 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2029 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2030 FND_MSG_PUB.add;
2031 END IF;
2032 RAISE FND_API.g_exc_error;
2033 END IF;
2034 CLOSE c_thldchk;
2035
2036 IF p_thldchk_rec.trigger_id = FND_API.g_miss_num THEN
2037 x_complete_rec.trigger_id := l_thldchk_rec.trigger_id;
2038 END IF;
2039
2040 IF p_thldchk_rec.order_number = FND_API.g_miss_num THEN
2041 x_complete_rec.order_number := l_thldchk_rec.order_number;
2042 END IF;
2043
2044 IF p_thldchk_rec.chk1_type = FND_API.g_miss_char THEN
2045 x_complete_rec.chk1_type := l_thldchk_rec.chk1_type;
2046 END IF;
2047
2048 IF p_thldchk_rec.chk1_arc_source_code_from = FND_API.g_miss_char THEN
2049 x_complete_rec.chk1_arc_source_code_from := l_thldchk_rec.chk1_arc_source_code_from;
2050 END IF;
2051
2052 IF p_thldchk_rec.chk1_act_object_id = FND_API.g_miss_num THEN
2053 x_complete_rec.chk1_act_object_id := l_thldchk_rec.chk1_act_object_id;
2054 END IF;
2055
2056 IF p_thldchk_rec.chk1_source_code = FND_API.g_miss_char THEN
2057 x_complete_rec.chk1_source_code := l_thldchk_rec.chk1_source_code;
2058 END IF;
2059
2060 IF p_thldchk_rec.chk1_source_code_metric_id = FND_API.g_miss_num THEN
2061 x_complete_rec.chk1_source_code_metric_id := l_thldchk_rec.chk1_source_code_metric_id;
2062 END IF;
2063
2064 IF p_thldchk_rec.chk1_source_code_metric_type = FND_API.g_miss_char THEN
2065 x_complete_rec.chk1_source_code_metric_type := l_thldchk_rec.chk1_source_code_metric_type;
2066 END IF;
2067
2068 IF p_thldchk_rec.chk1_to_chk2_operator_type = FND_API.g_miss_char THEN
2069 x_complete_rec.chk1_to_chk2_operator_type := l_thldchk_rec.chk1_to_chk2_operator_type;
2070 END IF;
2071
2072 IF p_thldchk_rec.chk2_type = FND_API.g_miss_char THEN
2073 x_complete_rec.chk2_type := l_thldchk_rec.chk2_type ;
2074 END IF;
2075
2076 IF p_thldchk_rec.chk2_value = FND_API.g_miss_num THEN
2077 x_complete_rec.chk2_value := l_thldchk_rec.chk2_value;
2078 END IF;
2079
2080 IF p_thldchk_rec.chk2_low_value = FND_API.g_miss_num THEN
2081 x_complete_rec.chk2_low_value := l_thldchk_rec.chk2_low_value;
2082 END IF;
2083
2084 IF p_thldchk_rec.chk2_high_value = FND_API.g_miss_num THEN
2085 x_complete_rec.chk2_high_value:= l_thldchk_rec.chk2_high_value;
2086 END IF;
2087
2088 IF p_thldchk_rec.chk2_uom_code = FND_API.g_miss_char THEN
2089 x_complete_rec.chk2_uom_code := l_thldchk_rec.chk2_uom_code;
2090 END IF;
2091
2092 IF p_thldchk_rec.chk2_currency_code = FND_API.g_miss_char THEN
2093 x_complete_rec.chk2_currency_code:= l_thldchk_rec.chk2_currency_code;
2094 END IF;
2095
2096 IF p_thldchk_rec.chk2_source_code = FND_API.g_miss_char THEN
2097 x_complete_rec.chk2_source_code := l_thldchk_rec.chk2_source_code;
2098 END IF;
2099
2100 IF p_thldchk_rec.chk2_arc_source_code_from = FND_API.g_miss_char THEN
2101 x_complete_rec.chk2_arc_source_code_from := l_thldchk_rec.chk2_arc_source_code_from;
2102 END IF;
2103
2104 IF p_thldchk_rec.chk2_act_object_id = FND_API.g_miss_num THEN
2105 x_complete_rec.chk2_act_object_id := l_thldchk_rec.chk2_act_object_id;
2106 END IF;
2107
2108 IF p_thldchk_rec.chk2_source_code_metric_id = FND_API.g_miss_num THEN
2109 x_complete_rec.chk2_source_code_metric_id := l_thldchk_rec.chk2_source_code_metric_id;
2110 END IF;
2111
2112 IF p_thldchk_rec.chk2_source_code_metric_type = FND_API.g_miss_char THEN
2113 x_complete_rec.chk2_source_code_metric_type := l_thldchk_rec.chk2_source_code_metric_type ;
2114 END IF;
2115
2116 IF p_thldchk_rec.chk2_workbook_name = FND_API.g_miss_char THEN
2117 x_complete_rec.chk2_workbook_name := l_thldchk_rec.chk2_workbook_name ;
2118 END IF;
2119
2120 IF p_thldchk_rec.chk2_workbook_owner = FND_API.g_miss_char THEN
2121 x_complete_rec.chk2_workbook_owner := l_thldchk_rec.chk2_workbook_owner ;
2122 END IF;
2123
2124 IF p_thldchk_rec.chk2_worksheet_name = FND_API.g_miss_char THEN
2125 x_complete_rec.chk2_worksheet_name := l_thldchk_rec.chk2_worksheet_name ;
2126 END IF;
2127 END Complete_Thldchk_rec ;
2128
2129 END AMS_ThldChk_PVT;