[Home] [Help]
PACKAGE BODY: APPS.AMS_TRIG_PVT
Source
1 PACKAGE BODY AMS_Trig_PVT as
2 /* $Header: amsvtrgb.pls 120.5 2006/04/20 01:36:53 srivikri noship $*/
3
4 --
5 -- NAME
6 -- AMS_Trig_PVT
7 --
8 -- HISTORY
9 -- 07/26/1999 ptendulk CREATED
10 -- 10/25/1999 ptendulk Modified According to new standards
11 -- 02/24/2000 ptendulk Add the code to update Object Attribute after
12 -- Deletion or addition
13 -- 02/26/2000 ptendulk Modified the Check_Record Procedure
14 -- 02/26/2000 ptendulk Modified the package to support the timezone
15 -- 07-Aug-2001 soagrawa Modified Check_Trig_Uk_Items (replaced call to ams_utility_pvt.check_uniqueness
16 -- with a manual check)
17 -- 24-sep-2001 soagrawa Removed security group id from everywhere
18 -- 10-Dec-2002 ptendulk Modified calculate_system_time api to combine two parameters into one
19 -- 22/apr/03 cgoyal added notify_flag, EXECUTE_SCHEDULE_FLAG for 11.5.8 backport
20 -- 08-jul-2003 cgoyal Modified data insertion in ams_triggers_tl for MLS
21 -- 30-jul-2003 anchaudh modified comparison operator to fix P1 bug# 3064909 in check_trig_record
22 -- 21-aug-2003 soagrawa Fixed bug 3108929 in check_trig_record
23 -- 27-aug-2003 soagrawa Fixed bug 3115141 in check_trig_record
24 -- 20-May-2004 dhsingh Modified Check_Trig_Uk_Items and c_trig_name_updt for better performance
25 -- 23-Feb-2006 srivikri Fix for bug 5053838 - Monitor activation CR
26 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_Trig_PVT';
27 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvtrgb.pls';
28
29
30 -- Debug mode
31 -- g_debug boolean := FALSE;
32 -- g_debug boolean := TRUE;
33
34 ----------------------------------------------------------------------------------------
35 ----------------------------------------------------------------------------------------
36 ----------------------------------------------------------------------------------------
37 ---------------------------------- Triggers --------------------------------------------
38 ----------------------------------------------------------------------------------------
39 ----------------------------------------------------------------------------------------
40 ----------------------------------------------------------------------------------------
41 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
42 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
43 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
44
45 PROCEDURE Calculate_System_Time(
46 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
47 x_msg_count OUT NOCOPY NUMBER ,
48 x_msg_data OUT NOCOPY VARCHAR2 ,
49 x_return_status OUT NOCOPY VARCHAR2 ,
50
51 -- Following code is modified by ptendulk combine p_trig_rec and x_trig_rec into one inout para
52 -- The change is done due to errors introduce due to nocopy changes.
53 -- p_trig_rec IN trig_rec_type ,
54 -- x_trig_rec OUT NOCOPY trig_rec_type ) ;
55
56 px_trig_rec IN OUT NOCOPY trig_rec_type );
57
58 /*************************** PRIVATE ROUTINES *********************************/
59
60 -- Start of Comments
61 --
62 -- NAME
63 -- Create_Trigger
64 --
65 -- PURPOSE
66 -- This procedure is to create a row in ams_triggers table that
67 -- satisfy caller needs
68 --
69 -- NOTES
70 --
71 --
72 -- HISTORY
73 -- 07/26/1999 ptendulk created
74 -- 10/25/1999 ptendulk Modified according to new standards
75 -- 02/24/2000 ptendulk Add the code to update Object Attribute after addition
76 -- 04/24/2000 ptendulk Added 6 Date fields and timezone id for timezone support
77 -- 14-Feb-2001 ptendulk Modified as triggers will have tl table to store name/desc
78 -- 22/apr/03 cgoyal added notify_flag and execute_schedule_flag for 11.5.8 backport
79 -- End of Comments
80
81 PROCEDURE Create_Trigger
82 ( p_api_version IN NUMBER,
83 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
84 p_commit IN VARCHAR2 := FND_API.G_FALSE,
85 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
86
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90
91 p_trig_Rec IN trig_rec_type,
92 x_trigger_id OUT NOCOPY NUMBER
93 ) IS
94
95 l_api_name CONSTANT VARCHAR2(30) := 'Create_Trigger';
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
98
99
100 -- Status Local Variables
101 l_return_status VARCHAR2(1); -- Return value from procedures
102 l_trig_rec trig_rec_type := p_trig_rec;
103
104
105 l_trig_count NUMBER;
106 x_rowid VARCHAR2(30);
107
108 CURSOR c_trig_seq IS
109 SELECT ams_triggers_s.NEXTVAL
110 FROM dual;
111
112 CURSOR c_trig_exists(l_my_trig_id IN NUMBER) IS
113 SELECT 1
114 FROM dual
115 WHERE EXISTS (SELECT 1
116 FROM ams_triggers
117 WHERE trigger_id = l_my_trig_id);
118
119 CURSOR c_trig_count(l_my_trig_id IN NUMBER) IS
120 SELECT COUNT(1)
121 FROM ams_triggers
122 WHERE trigger_id = l_my_trig_id;
123
124 BEGIN
125 --
126 -- Standard Start of API savepoint
127 --
128 SAVEPOINT Create_Trig_PVT;
129
130 IF (AMS_DEBUG_HIGH_ON) THEN
131
132
133
134 AMS_Utility_PVT.debug_message(l_full_name||': start');
135
136 END IF;
137
138 IF FND_API.to_boolean(p_init_msg_list) THEN
139 FND_MSG_PUB.initialize;
140 END IF;
141
142 --
143 -- Standard call to check for call compatibility.
144 --
145 IF NOT FND_API.Compatible_API_Call ( l_api_version,
146 p_api_version,
147 l_api_name,
148 G_PKG_NAME)
149 THEN
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 END IF;
152
153 --
154 -- Initialize API return status to success
155 --
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 --
159 -- API body
160 --
161
162 --
163 -- Perform the database operation
164 --
165
166 IF (AMS_DEBUG_HIGH_ON) THEN
167
168
169
170 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
171
172 END IF;
173
174 Validate_Trigger
175 ( p_api_version => 1.0
176 ,p_init_msg_list => p_init_msg_list
177 ,p_validation_level => p_validation_level
178 ,x_return_status => l_return_status
179 ,x_msg_count => x_msg_count
180 ,x_msg_data => x_msg_data
181
182 ,p_trig_rec => l_trig_rec
183 );
184
185 --
186 -- If any errors happen abort API.
187 --
188 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
189 RAISE FND_API.G_EXC_ERROR;
190 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192 END IF;
193
194 -- Following code is added by ptendulk on 26 Apr 2000 Give call to
195 -- Calculate System time api which will calculate the system date
196 -- for the user dates entered.
197 Calculate_System_Time(
198 p_init_msg_list => p_init_msg_list,
199 x_msg_count => x_msg_count,
200 x_msg_data => x_msg_data,
201 x_return_status => x_return_status,
202
203 -- Following code is modified by ptendulk combine p_trig_rec and x_trig_rec into one inout para
204 -- The change is done due to errors introduce due to nocopy changes.
205 -- p_trig_rec => p_trig_rec ,
206 -- x_trig_rec => l_trig_rec ) ;
207 px_trig_rec => l_trig_rec );
208
209 --
210 -- If any errors happen abort API.
211 --
212 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
213 RAISE FND_API.G_EXC_ERROR;
214 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217
218 --
219 -- Insert the Record
220 --
221 IF (AMS_DEBUG_HIGH_ON) THEN
222
223 AMS_Utility_PVT.debug_message(l_full_name ||': insert Trigger');
224 END IF;
225 IF (AMS_DEBUG_HIGH_ON) THEN
226
227 AMS_Utility_PVT.debug_message('Convert the system time'||p_trig_rec.arc_trigger_created_for||p_trig_rec.trigger_created_for_id);
228 END IF;
229
230 --
231 -- Find Unique Trigger ID if not sent
232 --
233 IF l_trig_rec.trigger_id IS NULL OR l_trig_rec.trigger_id = FND_API.G_MISS_NUM
234 THEN
235 LOOP
236 OPEN c_trig_seq;
237 FETCH c_trig_seq INTO l_trig_rec.trigger_id;
238 CLOSE c_trig_seq;
239
240 AMS_Utility_PVT.debug_message(l_full_name ||': insert Trigger id is '||l_trig_rec.trigger_id);
241
242 OPEN c_trig_exists(l_trig_rec.trigger_id);
243 FETCH c_trig_exists INTO l_trig_count;
244 CLOSE c_trig_exists;
245
246 AMS_Utility_PVT.debug_message(l_full_name ||': insert Trigger count is '||l_trig_count);
247
248 EXIT WHEN l_trig_count IS null;
249 END LOOP;
250 END IF;
251
252
253 INSERT INTO ams_triggers
254 (trigger_id
255 -- standard who columns
256 ,last_update_date
257 ,last_updated_by
258 ,creation_date
259 ,created_by
260 ,last_update_login
261 ,object_version_number
262 ,process_id
263 ,trigger_created_for_id
264 ,arc_trigger_created_for
265 ,triggering_type
266 ,trigger_name
267 ,view_application_id
268 ,timezone_id
269 ,user_start_date_time
270 ,start_date_time
271 ,user_last_run_date_time
272 ,last_run_date_time
273 ,user_next_run_date_time
274 ,next_run_date_time
275 ,user_repeat_daily_start_time
276 ,repeat_daily_start_time
277 ,user_repeat_daily_end_time
278 ,repeat_daily_end_time
279 ,repeat_frequency_type
280 ,repeat_every_x_frequency
281 ,user_repeat_stop_date_time
282 ,repeat_stop_date_time
283 ,metrics_refresh_type
284 ,description
285 -- removed by soagrawa on 24-sep-2001
286 -- ,security_group_id
287 --added by cgoyal for 11.5.8 backport
288 ,notify_flag
289 ,execute_schedule_flag
290 ,TRIGGERED_STATUS --anchaudh added for monitors,R12.
291 ,USAGE --anchaudh added for monitors,R12.
292 )
293 VALUES
294 (
295 l_trig_rec.trigger_id
296 -- standard who columns
297 ,SYSDATE
298 ,FND_GLOBAL.User_Id
299 ,SYSDATE
300 ,FND_GLOBAL.User_Id
301 ,FND_GLOBAL.Conc_Login_Id
302
303 ,1 -- Object Version Number
304 ,l_trig_rec.process_id
305 ,l_trig_rec.trigger_created_for_id
306 ,l_trig_rec.arc_trigger_created_for
307 ,l_trig_rec.triggering_type
308 ,NULL -- As trigger name will be stored in the tl table
309 ,l_trig_rec.view_application_id
310 ,l_trig_rec.timezone_id
311 ,l_trig_rec.user_start_date_time
312 ,l_trig_rec.start_date_time
313 ,l_trig_rec.user_last_run_date_time
314 ,l_trig_rec.last_run_date_time
315 ,l_trig_rec.user_next_run_date_time
316 ,l_trig_rec.next_run_date_time
317 ,l_trig_rec.user_repeat_daily_start_time
318 ,l_trig_rec.repeat_daily_start_time
319 ,l_trig_rec.user_repeat_daily_end_time
320 ,l_trig_rec.repeat_daily_end_time
321 ,l_trig_rec.repeat_frequency_type
322 ,l_trig_rec.repeat_every_x_frequency
323 ,l_trig_rec.user_repeat_stop_date_time
324 ,l_trig_rec.repeat_stop_date_time
325 ,l_trig_rec.metrics_refresh_type
326 ,NULL -- As Description will be stored in tl table.
327 -- removed by soagrawa on 24-sep-2001
328 -- ,l_trig_rec.security_group_id
329 --added by cgoyal for 11.5.8 backport
330 ,nvl(l_trig_rec.notify_flag,'N')
331 ,nvl(l_trig_rec.execute_schedule_flag,'N')
332 ,l_trig_rec.TRIGGERED_STATUS--anchaudh added for monitors,R12.
333 ,l_trig_rec.USAGE--anchaudh added for monitors,R12.
334 );
335 --cgoyal commented on 08/03 for MLS
336 /*
337 INSERT INTO ams_triggers_tl
338 (trigger_id
339 ,language
340 ,last_update_date
341 ,last_upated_by
342 ,creation_date
343 ,created_by
344 ,last_update_login
345 ,source_lang
346 ,trigger_name
347 ,description
348 -- removed by soagrawa on 24-sep-2001
349 -- ,security_group_id
350 )
351 VALUES
352 (l_trig_rec.trigger_id
353 ,USERENV('LANG')
354 ,SYSDATE
355 ,FND_GLOBAL.User_Id
356 ,SYSDATE
357 ,FND_GLOBAL.User_Id
358 ,FND_GLOBAL.Conc_Login_Id
359 ,USERENV('LANG')
360 ,l_trig_rec.trigger_name
361 ,l_trig_rec.description
362 -- removed by soagrawa on 24-sep-2001
363 -- ,l_trig_rec.security_group_id
364 ) ;
365 */
366 INSERT INTO ams_triggers_tl
367 (trigger_id
368 ,language
369 ,last_update_date
370 ,last_upated_by
371 ,creation_date
372 ,created_by
373 ,last_update_login
374 ,source_lang
375 ,trigger_name
376 ,description
377 -- removed by soagrawa on 24-sep-2001
378 -- ,security_group_id
379 )
380 SELECT
381 l_trig_rec.trigger_id,
382 l.language_code,
383 SYSDATE,
384 FND_GLOBAL.user_id,
385 SYSDATE,
386 FND_GLOBAL.user_id,
387 FND_GLOBAL.conc_login_id,
388 USERENV('LANG'),
389 l_trig_rec.trigger_name,
390 l_trig_rec.description
391 FROM fnd_languages l
392 WHERE l.installed_flag IN ('I','B')
393 AND NOT EXISTS(
394 SELECT NULL
395 FROM ams_triggers_tl t
396 WHERE t.trigger_id = l_trig_rec.trigger_id
397 AND t.language = l.language_code ) ;
398
399 AMS_Utility_PVT.debug_message(l_full_name ||': inserted in tl table');
400
401
402 -- Following code has been added by ptendulk on 24Feb2000
403 -- It will update the attribute in ams_object_attribites
404 -- as soon as segment is created for an activity
405
406 -- Following code is commented by ptendulk on 14-Feb-2001
410 -- AMS_ObjectAttribute_PVT.modify_object_attribute(
407 -- As from hornet release cue card attributes won't be stored in obj attr table.
408 -- indicate schedule has been defined for the campaign
409 --IF l_trig_rec.arc_trigger_created_for <> 'AMET' THEN
411 -- p_api_version => l_api_version,
412 -- p_init_msg_list => FND_API.g_false,
413 -- p_commit => FND_API.g_false,
414 -- p_validation_level => FND_API.g_valid_level_full,
415 -- x_return_status => l_return_status,
416 -- x_msg_count => x_msg_count,
417 -- x_msg_data => x_msg_data,
418
419 -- p_object_type => l_trig_rec.arc_trigger_created_for,
420 -- p_object_id => l_trig_rec.trigger_created_for_id,
421 -- p_attr => 'TRIG',
422 -- p_attr_defined_flag => 'Y' );
423 --
424 -- IF l_return_status = FND_API.g_ret_sts_error THEN
425 -- RAISE FND_API.g_exc_error;
426 -- ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
427 -- RAISE FND_API.g_exc_unexpected_error;
428 -- END IF;
429
430 --END IF;
431
432 --
433 -- set OUT value
434 --
435 x_trigger_id := l_trig_rec.trigger_id;
436
437 --
438 -- END of API body.
439 --
440
441 -- Standard check of p_commit.
442 IF FND_API.To_Boolean ( p_commit )
443 THEN
444 COMMIT WORK;
445 END IF;
446
447 --
448 -- Standard call to get message count AND IF count is 1, get message info.
449 --
450 FND_MSG_PUB.Count_AND_Get
451 ( p_count => x_msg_count,
452 p_data => x_msg_data,
453 p_encoded => FND_API.G_FALSE
454 );
455
456 IF (AMS_DEBUG_HIGH_ON) THEN
457
458
459
460 AMS_Utility_PVT.debug_message(l_full_name ||': end');
461
462 END IF;
463
464 EXCEPTION
465
466 WHEN FND_API.G_EXC_ERROR THEN
467
468 ROLLBACK TO Create_Trig_PVT;
469 x_return_status := FND_API.G_RET_STS_ERROR ;
470
471 FND_MSG_PUB.Count_AND_Get
472 ( p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => FND_API.G_FALSE
475 );
476
477
478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
479
480 ROLLBACK TO Create_Trig_PVT;
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
482
483 FND_MSG_PUB.Count_AND_Get
484 ( p_count => x_msg_count,
485 p_data => x_msg_data,
486 p_encoded => FND_API.G_FALSE
487 );
488
489 WHEN OTHERS THEN
490
491 ROLLBACK TO Create_Trig_PVT;
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
493
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
499 FND_MSG_PUB.Count_AND_Get
500 ( p_count => x_msg_count,
501 p_data => x_msg_data,
502 p_encoded => FND_API.G_FALSE
503 );
504 END Create_Trigger;
505
506 -- Start of Comments
507 --
508 -- NAME
509 -- Delete_Trigger
510 --
511 -- PURPOSE
512 -- This procedure is to delete a ams_triggers table that satisfy caller needs
513 --
514 -- NOTES
515 -- Buss. Rule : If the trigger is not repeating , delete trigger
516 -- If it is repeating , Delete trigger only if it hasn't run yet.
517 -- If it has run before Update the repeat_stop_date time with sysdate to
518 -- deactivate trigger
519 -- 24-Apr-2001 soagrawa
520 -- New Business Rule:
521 -- If exist schedule(s) associated with the trigger => do not delete, just deactivate
522 -- If trigger has not run yet => delete
523 -- If trigger has run => deactivate
524 --
525 -- 23-Feb-2006 srivikri
526 -- New buisiness rule:
527 -- Trigger can be activated by the user using an activate button
528 -- If trigger has not been activated => delete
529 -- If trigger is activated => do not delete
530 -- Refer bug 5053838
531 --
532 --
533 -- HISTORY
534 -- 07/26/1999 ptendulk created
535 -- 10/25/1999 ptendulk Modified according to new API standards
536 -- 02/24/2000 ptendulk Add the code to update Object Attribute after
537 -- Deletion
538 -- 14-Feb-2001 ptendulk Modified as triggers will have tl table to store name/desc
539 -- 24-Apr-2001 soagrawa Modified as per the new business rules
540 -- 23-Feb-2006 srivikri Modified as per new buisiness rules - refer bug 5053838
541 -- End of Comments
542
543 PROCEDURE Delete_Trigger
544 ( p_api_version IN NUMBER,
545 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
546 p_commit IN VARCHAR2 := FND_API.G_FALSE,
547
548 x_return_status OUT NOCOPY VARCHAR2,
552 p_trigger_id IN NUMBER,
549 x_msg_count OUT NOCOPY NUMBER,
550 x_msg_data OUT NOCOPY VARCHAR2,
551
553 p_object_version_number IN NUMBER
554 ) IS
555
556 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Trigger';
557 l_api_version CONSTANT NUMBER := 1.0;
558 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
559 l_return_status VARCHAR2(1);
560
561 CURSOR c_trig_det IS
562 SELECT repeat_frequency_type,
563 last_run_date_time,
564 arc_trigger_created_for,
565 trigger_created_for_id,
566 timezone_id,
567 start_date_time,
568 process_id
569 FROM ams_triggers
570 WHERE trigger_id = p_trigger_id ;
571
572 /*CURSOR c_assoc_sch IS
573 SELECT count(*)
574 FROM ams_campaign_schedules_b
575 WHERE trigger_id = p_trigger_id;
576 */
577 l_trig_rec c_trig_det%ROWTYPE ;
578 l_mode VARCHAR2(30);
579 l_dummy NUMBER;
580 l_user_date date ;
581 l_assoc_sch NUMBER;
582
583
584 BEGIN
585 --
586 -- Standard Start of API savepoint
587 --
588 SAVEPOINT Delete_Trig_PVT;
589
590 --
591 -- Debug Message
592 --
593 IF (AMS_DEBUG_HIGH_ON) THEN
594
595 AMS_Utility_PVT.debug_message(l_full_name||': start');
596 END IF;
597 -- dbms_output.put_line('entered API Call');
598 -- dbms_output.put_line('trigger ID to be deleted is ');
599 -- dbms_output.put_line(p_trigger_id);
600 --
601 -- Initialize message list IF p_init_msg_list is set to TRUE.
602 --
603 IF FND_API.to_Boolean( p_init_msg_list ) THEN
604 FND_MSG_PUB.initialize;
605 END IF;
606
607 --
608 -- Standard call to check for call compatibility.
609 --
610 IF NOT FND_API.Compatible_API_Call ( l_api_version,
611 p_api_version,
612 l_api_name,
613 G_PKG_NAME)
614 THEN
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 END IF;
617
618 --
619 -- Initialize API return status to success
620 --
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 --
624 -- Debug Message
625 --
626 IF (AMS_DEBUG_HIGH_ON) THEN
627
628 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
629 END IF;
630
631 --
632 -- API body
633 --
634 OPEN c_trig_det ;
635 FETCH c_trig_det INTO l_trig_rec ;
636 CLOSE c_trig_det ;
637
638
639 -----------------------------------------------------------------------
640 -- Following code is added by soagrawa on 04/24/01
641 -- Business rules for delete trigger have been modified
642 -- need to know if there are any schedules associated with the trigger
643 -----------------------------------------------------------------------
644
645 SELECT count(*)
646 INTO l_assoc_sch -- number of schedules associated with this triggerId
647 FROM ams_campaign_schedules_b
648 WHERE trigger_id = p_trigger_id;
649
650 -------------------------------------------------------
651 -- Following code is modified by soagrawa on 13-may-2003
652 -- Business rules for delete trigger have been modified
653 -------------------------------------------------------
654
655 AMS_Utility_PVT.Create_Log (
656 x_return_status => l_return_status,
657 p_arc_log_used_by => 'TRIG',
658 p_log_used_by_id => p_trigger_id,
659 p_msg_data => 'l_assoc_sch:'||l_assoc_sch,
660 p_msg_type => 'DEBUG'
661 );
662 IF l_assoc_sch>0 THEN -- if exist schedule(s) associated with this triggerId
663 -- throw error msg
664 AMS_Utility_PVT.Error_Message('AMS_TRIG_NO_DEL_CSC_ASSOC');
665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
666
667 ELSE -- no schedules are associated with this trigger
668 AMS_Utility_PVT.Create_Log (
669 x_return_status => l_return_status,
670 p_arc_log_used_by => 'TRIG',
671 p_log_used_by_id => p_trigger_id,
672 p_msg_data => 'process_id:'||l_trig_rec.process_id,
673 p_msg_type => 'DEBUG'
674 );
675
676 IF (l_trig_rec.process_id IS NULL OR l_trig_rec.process_id = '') THEN -- if trigger has not started yet
677
678 DELETE FROM AMS_triggers_tl
679 WHERE trigger_id = p_trigger_id ;
680 IF (SQL%NOTFOUND) THEN
681 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683 END IF;
684 AMS_Utility_PVT.Create_Log (
685 x_return_status => l_return_status,
686 p_arc_log_used_by => 'TRIG',
687 p_log_used_by_id => p_trigger_id,
688 p_msg_data => 'going to delete trigger_id:'||p_trigger_id,
689 p_msg_type => 'DEBUG'
693 WHERE trigger_id = p_trigger_id
690 );
691
692 DELETE FROM ams_triggers
694 AND object_version_number = p_object_version_number ;
695
696 IF (SQL%NOTFOUND) THEN
697 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
698 RAISE FND_API.G_EXC_ERROR;
699 ELSE
700 -- Delete the Checks and Actions Attached to the Trigger
701 DELETE FROM ams_trigger_checks
702 WHERE trigger_id = p_trigger_id ;
703
704 DELETE FROM ams_trigger_actions
705 WHERE trigger_id = p_trigger_id ;
706
707 END IF;
708
709
710 ELSE -- if trigger has started
711 -- throw error msg
712 AMS_Utility_PVT.Error_Message('AMS_TRIG_NO_DEL_TRIG_ACTIVE');
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714
715
716 END IF;
717 END IF;
718
719 --
720 -- END of API body.
721 --
722
723 --
724 -- Standard check of p_commit.
725 --
726 -- dbms_output.put_line('commit : '||p_commit);
727 IF FND_API.To_Boolean ( p_commit )
728 THEN
729 COMMIT WORK;
730 END IF;
731
732 --
733 -- Standard call to get message count AND IF count is 1, get message info.
734 --
735 FND_MSG_PUB.Count_AND_Get
736 ( p_count => x_msg_count,
737 p_data => x_msg_data,
738 p_encoded => FND_API.G_FALSE
739 );
740
741 --
742 -- Debug message
743 --
744 IF (AMS_DEBUG_HIGH_ON) THEN
745
746 AMS_Utility_PVT.debug_message(l_full_name ||': end');
747 END IF;
748
749 EXCEPTION
750
751 WHEN FND_API.G_EXC_ERROR THEN
752
753 ROLLBACK TO Delete_Trig_PVT;
754 x_return_status := FND_API.G_RET_STS_ERROR ;
755
756 FND_MSG_PUB.Count_AND_Get
757 ( p_count => x_msg_count,
758 p_data => x_msg_data,
759 p_encoded => FND_API.G_FALSE
760 );
761
762
763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
764
765 ROLLBACK TO Delete_Trig_PVT;
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
767
768 FND_MSG_PUB.Count_AND_Get
769 ( p_count => x_msg_count,
770 p_data => x_msg_data,
771 p_encoded => FND_API.G_FALSE
772 );
773
774
775 WHEN OTHERS THEN
776
777 ROLLBACK TO Delete_Trig_PVT;
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
779
780 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
781 THEN
782 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
783 END IF;
784
785 FND_MSG_PUB.Count_AND_Get
786 ( p_count => x_msg_count,
787 p_data => x_msg_data,
788 p_encoded => FND_API.G_FALSE
789 );
790
791 END Delete_Trigger;
792
793 -- Start of Comments
794 --
795 -- NAME
796 -- Lock_Trigger
797 --
798 -- PURPOSE
799 -- This procedure is to lock a ams_triggers table that satisfy caller needs
800 --
801 -- NOTES
802 --
803 --
804 -- HISTORY
805 -- 06/29/1999 ptendulk created
806 -- 10/25/1999 ptendulk Modified according to new API standards
807 -- End of Comments
808
809 PROCEDURE Lock_Trigger
810 ( p_api_version IN NUMBER,
811 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
812
813 x_return_status OUT NOCOPY VARCHAR2,
814 x_msg_count OUT NOCOPY NUMBER,
815 x_msg_data OUT NOCOPY VARCHAR2,
816
817 p_trigger_id IN NUMBER,
818 p_object_version_number IN NUMBER
819 ) IS
820
821 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Trigger';
822 l_api_version CONSTANT NUMBER := 1.0;
823 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
824
825
826 -- Status Local Variables
827 l_trigger_id NUMBER;
828
829 CURSOR C_ams_triggers IS
830 SELECT trigger_id
831 FROM ams_triggers
832 WHERE trigger_id = p_trigger_id
833 AND object_version_number = p_object_version_number
834 FOR UPDATE of trigger_id NOWAIT;
835
836 CURSOR c_trig_tl IS
837 SELECT trigger_id
838 FROM ams_triggers_tl
839 WHERE trigger_id = p_trigger_id
840 AND USERENV('LANG') IN (language, source_lang)
841 FOR UPDATE NOWAIT;
842
843 BEGIN
844
845 --
846 -- Debug Message
847 --
848 IF (AMS_DEBUG_HIGH_ON) THEN
849
850 AMS_Utility_PVT.debug_message(l_full_name||': start');
851 END IF;
852
853 --
857 FND_MSG_PUB.initialize;
854 -- Initialize message list if p_init_msg_list is set to TRUE.
855 --
856 IF FND_API.to_Boolean( p_init_msg_list ) THEN
858 END IF;
859
860 IF NOT FND_API.Compatible_API_Call ( l_api_version,
861 p_api_version,
862 l_api_name,
863 G_PKG_NAME)
864 THEN
865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
866 END IF;
867
868 --
869 -- Initialize API return status to success
870 --
871 x_return_status := FND_API.G_RET_STS_SUCCESS;
872
873 --
874 -- API body
875 --
876
877 IF (AMS_DEBUG_HIGH_ON) THEN
878
879
880
881 AMS_Utility_PVT.debug_message(l_full_name||': lock');
882
883 END IF;
884
885
886 -- Perform the database operation
887 OPEN C_ams_triggers;
888 FETCH C_ams_triggers INTO l_trigger_id ;
889 IF (C_ams_triggers%NOTFOUND) THEN
890 CLOSE C_ams_triggers;
891 -- Error, check the msg level and added an error message to the
892 -- API message list
893 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
894 THEN -- MMSG
895 FND_MESSAGE.Set_Name('FND', 'AMS_API_RECORD_NOT_FOUND');
896 FND_MSG_PUB.Add;
897 END IF;
898
899 RAISE FND_API.G_EXC_ERROR;
900 END IF;
901 CLOSE C_ams_triggers;
902
903 OPEN c_trig_tl ;
904 CLOSE c_trig_tl ;
905
906 --
907 -- END of API body.
908 --
909
910 --
911 -- Standard call to get message count AND IF count is 1, get message info.
912 --
913 FND_MSG_PUB.Count_AND_Get
914 ( p_count => x_msg_count,
915 p_data => x_msg_data,
916 p_encoded => FND_API.G_FALSE
917 );
918
919 IF (AMS_DEBUG_HIGH_ON) THEN
920
921
922
923 AMS_Utility_PVT.debug_message(l_full_name ||': end');
924
925 END IF;
926
927 EXCEPTION
928 WHEN FND_API.G_EXC_ERROR THEN
929 x_return_status := FND_API.G_RET_STS_ERROR ;
930
931 FND_MSG_PUB.Count_AND_Get
932 ( p_count => x_msg_count,
933 p_data => x_msg_data,
934 p_encoded => FND_API.G_FALSE
935 );
936 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
938
939 FND_MSG_PUB.Count_AND_Get
940 ( p_count => x_msg_count,
941 p_data => x_msg_data,
942 p_encoded => FND_API.G_FALSE
943 );
944
945 WHEN AMS_Utility_PVT.RESOURCE_LOCKED THEN
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
947
948 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
949 THEN -- MMSG
950 FND_MESSAGE.SET_NAME('AMS','AMS_API_RESOURCE_LOCKED');
951 FND_MSG_PUB.Add;
952 END IF;
953
954 FND_MSG_PUB.Count_AND_Get
955 ( p_count => x_msg_count,
956 p_data => x_msg_data,
957 p_encoded => FND_API.G_FALSE
958 );
959
960 WHEN OTHERS THEN
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
962
963 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
964 THEN
965 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
966 END IF;
967
968 FND_MSG_PUB.Count_AND_Get
969 ( p_count => x_msg_count,
970 p_data => x_msg_data,
971 p_encoded => FND_API.G_FALSE
972 );
973 END Lock_Trigger;
974
975 -- Start of Comments
976 --
977 -- NAME
978 -- Update_Trigger
979 --
980 -- PURPOSE
981 -- This procedure is to update a ams_triggers table that satisfy caller needs
982 --
983 -- NOTES
984 --
985 --
986 -- HISTORY
987 -- 07/26/1999 ptendulk created
988 -- 10/25/1999 ptendulk Modified According to new API standards
989 -- 04/24/2000 ptendulk Added 6 User date fields and time zone id for
990 -- timezone support
991 -- 22/apr/03 cgoyal added notify_flag and execute_schedule_flag for 11.5.8 backport
992 -- End of Comments
993
994 PROCEDURE Update_Trigger
995 ( p_api_version IN NUMBER,
996 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
997 p_commit IN VARCHAR2 := FND_API.G_FALSE,
998 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
999
1000 x_return_status OUT NOCOPY VARCHAR2,
1001 x_msg_count OUT NOCOPY NUMBER,
1002 x_msg_data OUT NOCOPY VARCHAR2,
1003
1004 p_trig_rec IN trig_rec_type
1005 ) IS
1006
1007 l_api_name CONSTANT VARCHAR2(30) := 'Update_Trigger';
1011
1008 l_api_version CONSTANT NUMBER := 1.0;
1009 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1010
1012 -- Status Local Variables
1013 l_return_status VARCHAR2(1); -- Return value from procedures
1014 l_trig_rec trig_rec_type := p_trig_rec;
1015
1016 CURSOR c_trig IS
1017 SELECT repeat_frequency_type,last_run_date_time,
1018 repeat_stop_date_time
1019 FROM ams_triggers
1020 WHERE trigger_id = p_trig_rec.trigger_id ;
1021
1022 l_trig_det_rec c_trig%ROWTYPE;
1023
1024 BEGIN
1025 --
1026 -- Standard Start of API savepoint
1027 --
1028 SAVEPOINT Update_Trig_PVT;
1029
1030 --
1031 -- Debug Message
1032 --
1033 IF (AMS_DEBUG_HIGH_ON) THEN
1034
1035 AMS_Utility_PVT.debug_message(l_full_name||': start');
1036 END IF;
1037
1038 --
1039 -- Initialize message list IF p_init_msg_list is set to TRUE.
1040 --
1041 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1042 FND_MSG_PUB.initialize;
1043 END IF;
1044
1045 --
1046 -- Standard call to check for call compatibility.
1047 --
1048 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1049 p_api_version,
1050 l_api_name,
1051 G_PKG_NAME)
1052 THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 --
1057 -- Initialize API return status to success
1058 --
1059 x_return_status := FND_API.G_RET_STS_SUCCESS;
1060
1061 --
1062 -- API body
1063 --
1064
1065 --
1066 -- Debug Message
1067 --
1068 IF (AMS_DEBUG_HIGH_ON) THEN
1069
1070 AMS_Utility_PVT.debug_message(l_full_name||': Validate');
1071 END IF;
1072
1073 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1074 check_trig_items(
1075 p_trig_rec => p_trig_rec,
1076 p_validation_mode => JTF_PLSQL_API.g_update,
1077 x_return_status => l_return_status
1078 );
1079 --dbms_output.put_line('After Item Validation : '||l_return_status);
1080 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1081 RAISE FND_API.g_exc_unexpected_error;
1082 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1083 RAISE FND_API.g_exc_error;
1084 END IF;
1085 END IF;
1086
1087 -- replace g_miss_char/num/date with current column values
1088 complete_trig_rec(p_trig_rec, l_trig_rec);
1089
1090 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1091 check_trig_record(
1092 p_trig_rec => p_trig_rec,
1093 p_complete_rec => l_trig_rec,
1094 x_return_status => l_return_status
1095 );
1096
1097 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1098 RAISE FND_API.g_exc_unexpected_error;
1099 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1100 RAISE FND_API.g_exc_error;
1101 END IF;
1102 END IF;
1103
1104 --dbms_output.put_line('After Validation : '||l_return_status);
1105
1106 -- Don't allow to update if the sysdate is greater than Repeat stop date
1107 OPEN c_trig ;
1108 FETCH c_trig INTO l_trig_det_rec ;
1109 CLOSE c_trig ;
1110
1111 IF l_trig_det_rec.repeat_frequency_type = 'NONE' THEN
1112 IF l_trig_det_rec.last_run_date_time IS NOT NULL THEN
1113 AMS_Utility_PVT.Error_Message('AMS_TRIG_ERR_UPDT_TRIG_FIRED');
1114 RAISE FND_API.G_EXC_ERROR;
1115 END IF;
1116 ELSE
1117 IF l_trig_det_rec.repeat_stop_date_time < SYSDATE THEN
1118 AMS_Utility_PVT.Error_Message('AMS_TRIG_ERR_UPDT_EXPIRED');
1119 RAISE FND_API.G_EXC_ERROR;
1120 END IF;
1121 END IF;
1122
1123 -- Following code is added by ptendulk on 26 Apr 2000 Give call to
1124 -- Calculate System time api which will calculate the system date
1125 -- for the user dates entered.
1126
1127 Calculate_System_Time(
1128 p_init_msg_list => p_init_msg_list,
1129 x_msg_count => x_msg_count,
1130 x_msg_data => x_msg_data,
1131 x_return_status => x_return_status,
1132 -- Following code is modified by ptendulk combine p_trig_rec and x_trig_rec into one inout para
1133 -- The change is done due to errors introduce due to nocopy changes.
1134 -- p_trig_rec => l_trig_rec ,
1135 -- x_trig_rec => l_trig_rec ) ;
1136 px_trig_rec => l_trig_rec );
1137
1138 --
1139 --Debug Message
1140 --
1141 IF (AMS_DEBUG_HIGH_ON) THEN
1142
1143 AMS_Utility_PVT.debug_message(l_full_name ||': update');
1144 END IF;
1145
1146 UPDATE ams_triggers
1147 SET
1148 last_update_date = SYSDATE
1149 ,last_updated_by = FND_GLOBAL.user_id
1150 ,last_update_login = FND_GLOBAL.conc_login_id
1151 ,object_version_number = l_trig_rec.object_version_number + 1
1152 ,process_id = l_trig_rec.process_id
1156 ,trigger_name = NULL -- As Name will be stored in tl table.
1153 ,trigger_created_for_id = l_trig_rec.trigger_created_for_id
1154 ,arc_trigger_created_for = l_trig_rec.arc_trigger_created_for
1155 ,triggering_type = l_trig_rec.triggering_type
1157 ,view_application_id = l_trig_rec.view_application_id
1158 ,timezone_id = l_trig_rec.timezone_id
1159 ,user_start_date_time = l_trig_rec.user_start_date_time
1160 ,start_date_time = l_trig_rec.start_date_time
1161 ,user_last_run_date_time = l_trig_rec.user_last_run_date_time
1162 ,last_run_date_time = l_trig_rec.last_run_date_time
1163 ,user_next_run_date_time = l_trig_rec.user_next_run_date_time
1164 ,next_run_date_time = l_trig_rec.next_run_date_time
1165 ,user_repeat_daily_start_time = l_trig_rec.user_repeat_daily_start_time
1166 ,repeat_daily_start_time = l_trig_rec.repeat_daily_start_time
1167 ,user_repeat_daily_end_time = l_trig_rec.user_repeat_daily_end_time
1168 ,repeat_daily_end_time = l_trig_rec.repeat_daily_end_time
1169 ,repeat_frequency_type = l_trig_rec.repeat_frequency_type
1170 ,repeat_every_x_frequency = l_trig_rec.repeat_every_x_frequency
1171 ,user_repeat_stop_date_time = l_trig_rec.user_repeat_stop_date_time
1172 ,repeat_stop_date_time = l_trig_rec.repeat_stop_date_time
1173 ,metrics_refresh_type = l_trig_rec.metrics_refresh_type
1174 ,description = null -- As description will be stored in tl table.
1175 -- removed by soagrawa on 24-sep-2001
1176 -- ,security_group_id = l_trig_rec.security_group_id
1177 ,notify_flag = l_trig_rec.notify_flag
1178 ,execute_schedule_flag = l_trig_rec.execute_schedule_flag
1179 ,TRIGGERED_STATUS = l_trig_rec.TRIGGERED_STATUS--anchaudh added for monitors,R12
1180 ,USAGE = l_trig_rec.USAGE--anchaudh added for monitors,R12
1181
1182 WHERE trigger_id = l_trig_rec.trigger_id
1183 AND object_version_number = l_trig_rec.object_version_number ;
1184
1185 IF (SQL%NOTFOUND) THEN
1186 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
1187 RAISE FND_API.g_exc_error;
1188 END IF;
1189
1190 UPDATE ams_triggers_tl
1191 SET
1192 last_update_date = SYSDATE,
1193 last_upated_by = FND_GLOBAL.user_id,
1194 creation_date = SYSDATE,
1195 created_by = FND_GLOBAL.user_id,
1196 last_update_login = FND_GLOBAL.user_id,
1197 source_lang = USERENV('LANG'),
1198 trigger_name = l_trig_rec.trigger_name,
1199 description = l_trig_rec.description
1200 -- removed by soagrawa on 24-sep-2001
1201 -- security_group_id = l_trig_rec.security_group_id
1202 WHERE trigger_id = l_trig_rec.trigger_id ;
1203 IF (SQL%NOTFOUND) THEN
1204 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
1205 RAISE FND_API.g_exc_error;
1206 END IF;
1207 --
1208 -- END of API body.
1209 --
1210
1211 --
1212 -- Standard check of p_commit.
1213 --
1214 IF FND_API.to_boolean(p_commit) THEN
1215 COMMIT;
1216 END IF;
1217
1218 --
1219 -- Get message count AND IF count is 1, get message info.
1220 --
1221 FND_MSG_PUB.count_and_get(
1222 p_encoded => FND_API.g_false,
1223 p_count => x_msg_count,
1224 p_data => x_msg_data
1225 );
1226
1227
1228 IF (AMS_DEBUG_HIGH_ON) THEN
1229 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1230 END IF;
1231
1232 EXCEPTION
1233 WHEN FND_API.G_EXC_ERROR THEN
1234
1235 ROLLBACK TO Update_Trig_PVT;
1236 x_return_status := FND_API.G_RET_STS_ERROR ;
1237
1238 FND_MSG_PUB.Count_AND_Get
1239 ( p_count => x_msg_count,
1240 p_data => x_msg_data,
1241 p_encoded => FND_API.G_FALSE
1242 );
1243
1244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1245
1246 ROLLBACK TO Update_Trig_PVT;
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1248
1249 FND_MSG_PUB.Count_AND_Get
1250 ( p_count => x_msg_count,
1251 p_data => x_msg_data,
1252 p_encoded => FND_API.G_FALSE
1253 );
1254
1255 WHEN OTHERS THEN
1256
1257 ROLLBACK TO Update_Trig_PVT;
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1259
1260 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1261 THEN
1262 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1263 END IF;
1264
1265 FND_MSG_PUB.Count_AND_Get
1266 ( p_count => x_msg_count,
1267 p_data => x_msg_data,
1268 p_encoded => FND_API.G_FALSE
1269 );
1270 END Update_Trigger;
1271
1272
1273 -- Start of Comments
1274 --
1275 -- NAME
1276 -- Calculate_System_Time
1280 -- for all the user entered times. It convert the time from user's timezone to
1277 --
1278 -- PURPOSE
1279 -- This procedure accepts the trigger record and calculates the system time
1281 -- the server's timezone
1282 --
1283 -- NOTES
1284 --
1285 --
1286 -- HISTORY
1287 -- 10/25/1999 ptendulk created
1288 -- 10-Dec-2002 ptendulk Modified the api parameters
1289 -- End of Comments
1290 PROCEDURE Calculate_System_Time(
1291 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1292 x_msg_count OUT NOCOPY NUMBER ,
1293 x_msg_data OUT NOCOPY VARCHAR2 ,
1294 x_return_status OUT NOCOPY VARCHAR2 ,
1295
1296 -- Following code is modified by ptendulk combine p_trig_rec and x_trig_rec into one inout para
1297 -- The change is done due to errors introduce due to nocopy changes.
1298 --p_trig_rec IN trig_rec_type ,
1299 --x_trig_rec IN OUT NOCOPY trig_rec_type )
1300 px_trig_rec IN OUT NOCOPY trig_rec_type)
1301 IS
1302 l_trig_rec trig_rec_type := px_trig_rec ;
1303
1304 BEGIN
1305 IF px_trig_rec.usage <> 'MONITOR' THEN
1306 -- USAGE IS TRIGGER
1307 IF px_trig_rec.user_start_date_time <> FND_API.G_MISS_DATE
1308 THEN
1309 AMS_UTILITY_PVT.Convert_Timezone(
1310 p_init_msg_list => p_init_msg_list ,
1311 x_return_status => x_return_status ,
1312 x_msg_count => x_msg_count ,
1313 x_msg_data => x_msg_data ,
1314
1315 p_user_tz_id => l_trig_rec.timezone_id , -- required
1316 p_in_time => l_trig_rec.user_start_date_time ,-- required
1317
1318 x_out_time => l_trig_rec.start_date_time
1319 );
1320
1321 END IF;
1322
1323 IF px_trig_rec.user_repeat_daily_start_time <> FND_API.G_MISS_DATE
1324 THEN
1325 AMS_UTILITY_PVT.Convert_Timezone(
1326 p_init_msg_list => p_init_msg_list ,
1327 x_return_status => x_return_status ,
1328 x_msg_count => x_msg_count ,
1329 x_msg_data => x_msg_data ,
1330
1331 p_user_tz_id => l_trig_rec.timezone_id , -- required
1332 p_in_time => l_trig_rec.user_repeat_daily_start_time ,-- required
1333
1334 x_out_time => l_trig_rec.repeat_daily_start_time
1335 );
1336 END IF ;
1337
1338 IF px_trig_rec.user_repeat_daily_end_time <> FND_API.G_MISS_DATE
1339 THEN
1340 AMS_UTILITY_PVT.Convert_Timezone(
1341 p_init_msg_list => p_init_msg_list ,
1342 x_return_status => x_return_status ,
1343 x_msg_count => x_msg_count ,
1344 x_msg_data => x_msg_data ,
1345
1346 p_user_tz_id => l_trig_rec.timezone_id , -- required
1347 p_in_time => l_trig_rec.user_repeat_daily_end_time ,-- required
1348
1349 x_out_time => l_trig_rec.repeat_daily_end_time
1350 );
1351 END IF ;
1352
1353 IF px_trig_rec.user_repeat_stop_date_time <> FND_API.G_MISS_DATE
1354 THEN
1355 AMS_UTILITY_PVT.Convert_Timezone(
1356 p_init_msg_list => p_init_msg_list ,
1357 x_return_status => x_return_status ,
1358 x_msg_count => x_msg_count ,
1359 x_msg_data => x_msg_data ,
1360
1361 p_user_tz_id => l_trig_rec.timezone_id , -- required
1362 p_in_time => l_trig_rec.user_repeat_stop_date_time ,-- required
1363
1364 x_out_time => l_trig_rec.repeat_stop_date_time
1365 );
1366
1367 END IF ;
1368 ELSE
1369 -- FOR MONITORS, THE TIMEZONE CONVERSION IS DONE BY OA.
1370 -- SO, THE REVERSE CONVERSION HAS TO BE DONE TO POPULATE user_date_time fields
1371 IF px_trig_rec.start_date_time <> FND_API.G_MISS_DATE
1372 THEN
1373 AMS_UTILITY_PVT.Convert_Timezone(
1374 p_init_msg_list => p_init_msg_list ,
1375 x_return_status => x_return_status ,
1376 x_msg_count => x_msg_count ,
1377 x_msg_data => x_msg_data ,
1378 p_user_tz_id => l_trig_rec.timezone_id ,
1379 p_in_time => l_trig_rec.start_date_time ,
1380 p_convert_type => 'USER' ,
1381 x_out_time => l_trig_rec.user_start_date_time
1382 );
1383
1384 END IF;
1385
1386 IF px_trig_rec.repeat_daily_start_time <> FND_API.G_MISS_DATE
1387 THEN
1388 AMS_UTILITY_PVT.Convert_Timezone(
1389 p_init_msg_list => p_init_msg_list ,
1390 x_return_status => x_return_status ,
1391 x_msg_count => x_msg_count ,
1392 x_msg_data => x_msg_data ,
1393 p_user_tz_id => l_trig_rec.timezone_id ,
1394 p_in_time => l_trig_rec.repeat_daily_start_time ,
1398 END IF ;
1395 p_convert_type => 'USER' ,
1396 x_out_time => l_trig_rec.user_repeat_daily_start_time
1397 );
1399
1400 IF px_trig_rec.repeat_daily_end_time <> FND_API.G_MISS_DATE
1401 THEN
1402 AMS_UTILITY_PVT.Convert_Timezone(
1403 p_init_msg_list => p_init_msg_list ,
1404 x_return_status => x_return_status ,
1405 x_msg_count => x_msg_count ,
1406 x_msg_data => x_msg_data ,
1407 p_user_tz_id => l_trig_rec.timezone_id ,
1408 p_in_time => l_trig_rec.repeat_daily_end_time ,
1409 p_convert_type => 'USER' ,
1410 x_out_time => l_trig_rec.user_repeat_daily_end_time
1411 );
1412 END IF ;
1413
1414 IF px_trig_rec.repeat_stop_date_time <> FND_API.G_MISS_DATE
1415 THEN
1416 AMS_UTILITY_PVT.Convert_Timezone(
1417 p_init_msg_list => p_init_msg_list ,
1418 x_return_status => x_return_status ,
1419 x_msg_count => x_msg_count ,
1420 x_msg_data => x_msg_data ,
1421 p_user_tz_id => l_trig_rec.timezone_id ,
1422 p_in_time => l_trig_rec.repeat_stop_date_time ,
1423 p_convert_type => 'USER' ,
1424 x_out_time => l_trig_rec.user_repeat_stop_date_time
1425 );
1426
1427 END IF ;
1428 END IF;
1429 px_trig_rec := l_trig_rec ;
1430 END Calculate_System_Time ;
1431
1432
1433
1434 -- Start of Comments
1435 --
1436 -- NAME
1437 -- Validate_Trigger
1438 --
1439 -- PURPOSE
1440 -- This procedure is to check required parameters that satisfy caller needs.
1441 --
1442 -- NOTES
1443 --
1444 --
1445 -- HISTORY
1446 -- 07/26/1999 ptendulk created
1447 -- 10/25/1999 ptendulk Modified according to new API standards
1448 -- End of Comments
1449
1450 PROCEDURE Validate_Trigger(
1451 p_api_version IN NUMBER,
1452 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1453 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1454
1455 x_return_status OUT NOCOPY VARCHAR2,
1456 x_msg_count OUT NOCOPY NUMBER,
1457 x_msg_data OUT NOCOPY VARCHAR2,
1458
1459 p_trig_rec IN trig_rec_type
1460 )
1461 IS
1462
1463 l_api_version CONSTANT NUMBER := 1.0;
1464 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Trigger';
1465 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1466 l_return_status VARCHAR2(1);
1467
1468 BEGIN
1469
1470 --
1471 -- Debug Message
1472 --
1473 IF (AMS_DEBUG_HIGH_ON) THEN
1474
1475 AMS_Utility_PVT.debug_message(l_full_name||': start');
1476 END IF;
1477
1478 IF FND_API.to_boolean(p_init_msg_list) THEN
1479 FND_MSG_PUB.initialize;
1480 END IF;
1481
1482 IF NOT FND_API.compatible_api_call(
1483 l_api_version,
1484 p_api_version,
1485 l_api_name,
1486 g_pkg_name
1487 ) THEN
1488 RAISE FND_API.g_exc_unexpected_error;
1489 END IF;
1490
1491 x_return_status := FND_API.g_ret_sts_success;
1492
1493 ---------------------- validate Trigger Items ------------------------
1494 --
1495 -- Debug Message
1496 --
1497 IF (AMS_DEBUG_HIGH_ON) THEN
1498
1499 AMS_Utility_PVT.debug_message(l_full_name||': check items');
1500 END IF;
1501
1502 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1503 Check_Trig_Items(
1504 p_trig_rec => p_trig_rec,
1505 p_validation_mode => JTF_PLSQL_API.g_create,
1506 x_return_status => l_return_status
1507 );
1508 --dbms_output.put_line('Stat After Item : '||l_return_status);
1509 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1510 RAISE FND_API.g_exc_unexpected_error;
1511 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1512 RAISE FND_API.g_exc_error;
1513 END IF;
1514 END IF;
1515
1516 ---------------------- validate Trigger Records ------------------------
1517 --
1518 -- Debug Message
1519 --
1520 IF (AMS_DEBUG_HIGH_ON) THEN
1521
1522 AMS_Utility_PVT.debug_message(l_full_name||': check record');
1523 END IF;
1524
1525 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1526 Check_Trig_Record(
1527 p_trig_rec => p_trig_rec,
1528 p_complete_rec => NULL,
1529 x_return_status => l_return_status
1530 );
1531 --dbms_output.put_line('Stat After Record : '||l_return_status);
1532 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1533 RAISE FND_API.g_exc_unexpected_error;
1534 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1535 RAISE FND_API.g_exc_error;
1536 END IF;
1537 END IF;
1541 p_encoded => FND_API.g_false,
1538
1539 -------------------- finish --------------------------
1540 FND_MSG_PUB.count_and_get(
1542 p_count => x_msg_count,
1543 p_data => x_msg_data
1544 );
1545
1546 IF (AMS_DEBUG_HIGH_ON) THEN
1547
1548
1549
1550 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1551
1552 END IF;
1553
1554 EXCEPTION
1555 WHEN FND_API.g_exc_error THEN
1556 x_return_status := FND_API.g_ret_sts_error;
1557 FND_MSG_PUB.count_and_get(
1558 p_encoded => FND_API.g_false,
1559 p_count => x_msg_count,
1560 p_data => x_msg_data
1561 );
1562
1563 WHEN FND_API.g_exc_unexpected_error THEN
1564 x_return_status := FND_API.g_ret_sts_unexp_error ;
1565 FND_MSG_PUB.count_and_get(
1566 p_encoded => FND_API.g_false,
1567 p_count => x_msg_count,
1568 p_data => x_msg_data
1569 );
1570
1571 WHEN OTHERS THEN
1572 x_return_status := FND_API.g_ret_sts_unexp_error;
1573 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1574 THEN
1575 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1576 END IF;
1577
1578 FND_MSG_PUB.count_and_get(
1579 p_encoded => FND_API.g_false,
1580 p_count => x_msg_count,
1581 p_data => x_msg_data
1582 );
1583
1584 END Validate_Trigger;
1585
1586
1587
1588 -- Start of Comments
1589 --
1590 -- NAME
1591 ---- Check_Trig_Req_Items
1592 --
1593 -- PURPOSE
1594 -- This procedure is to check required parameters that satisfy caller needs.
1595 --
1596 -- NOTES
1597 --
1598 --
1599 -- HISTORY
1600 -- 07/26/1999 ptendulk created
1601 -- 10/25/1999 ptendulk Modified according to new standards
1602 -- End of Comments
1603
1604 PROCEDURE Check_Trig_Req_Items
1605 ( p_trig_rec IN trig_rec_type,
1606 x_return_status OUT NOCOPY VARCHAR2
1607 ) IS
1608
1609 BEGIN
1610 -- Initialize API/Procedure return status to success
1611 x_return_status := FND_API.G_Ret_Sts_Success;
1612 --
1613 -- Trigger Created For ID
1614 --
1615 IF p_trig_rec.trigger_created_for_id IS NULL
1616 THEN
1617 -- missing required field
1618 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_CREATED_FOR');
1619 x_return_status := FND_API.G_RET_STS_ERROR;
1620 -- If any errors happen abort API/Procedure.
1621 RETURN;
1622 END IF;
1623
1624 --
1625 -- Trigger Created for
1626 --
1627 IF p_trig_rec.arc_trigger_created_for IS NULL
1628 THEN
1629 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_CREATED_FOR');
1630 x_return_status := FND_API.G_RET_STS_ERROR;
1631 -- If any errors happen abort API/Procedure.
1632 RETURN;
1633 END IF;
1634
1635 --
1636 -- Triggering type
1637 --
1638 IF p_trig_rec.triggering_type IS NULL
1639 THEN
1640 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_TRIG_TYPE');
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642 -- If any errors happen abort API/Procedure.
1643 RETURN;
1644 END IF;
1645
1646 --
1647 -- Repeat Frequency Type
1648 --
1649 IF p_trig_rec.repeat_frequency_type IS NULL
1650 THEN
1651 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_REP_FREQ_TYPE');
1652 x_return_status := FND_API.G_RET_STS_ERROR;
1653 -- If any errors happen abort API/Procedure.
1654 RETURN;
1655 END IF;
1656
1657 --
1658 -- Trigger Name
1659 --
1660 IF p_trig_rec.trigger_name IS NULL
1661 THEN
1662 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_TRIG_NAME');
1663 x_return_status := FND_API.G_RET_STS_ERROR;
1664 -- If any errors happen abort API/Procedure.
1665 RETURN;
1666 END IF;
1667
1668 --
1669 -- Application ID
1670 --
1671 IF p_trig_rec.view_application_id IS NULL
1672 THEN
1673 AMS_UTILITY_PVT.Error_Message('AMS_API_MISSING_APP_ID');
1674 x_return_status := FND_API.G_RET_STS_ERROR;
1675 -- If any errors happen abort API/Procedure.
1676 RETURN;
1677 END IF;
1678
1679 --
1680 -- Start Date time
1681 --
1682 IF p_trig_rec.user_start_date_time IS NULL
1683 THEN
1684 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_START_DT');
1685 x_return_status := FND_API.G_RET_STS_ERROR;
1686 -- If any errors happen abort API/Procedure.
1687 RETURN;
1688 END IF;
1689
1690 --
1691 -- Start Date time
1692 --
1693 IF p_trig_rec.timezone_id IS NULL
1694 THEN
1695 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_TIMEZONE');
1696 x_return_status := FND_API.G_RET_STS_ERROR;
1697 -- If any errors happen abort API/Procedure.
1698 RETURN;
1699 END IF;
1700
1701 END Check_Trig_Req_Items;
1702
1706 -- Validate_Trig_UK_Items
1703 -- Start of Comments
1704 --
1705 -- NAME
1707 --
1708 -- PURPOSE
1709 -- This procedure is to validate ams_triggers items
1710 --
1711 -- NOTES
1712 --
1713 --
1714 -- HISTORY
1715 -- 25-oct-1999 ptendulk created
1716 -- 07-aug-2001 soagrawa Replaced call to ams_utility_pvt.check_uniqueness with manual check
1717 -- End of Comments
1718
1719 PROCEDURE Check_Trig_Uk_Items(
1720 p_trig_rec IN trig_rec_type,
1721 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1722 x_return_status OUT NOCOPY VARCHAR2
1723 )
1724 IS
1725 l_valid_flag VARCHAR2(1);
1726 l_where_clause VARCHAR2(2000);
1727
1728 -- following 2 cursors c_trig_name , c_trig_name_updt added by soagrawa on 07-Aug-2001
1729 -- to replace call to Ams_Utility_Pvt.check_uniqueness
1730 CURSOR c_trig_name IS
1731 -- modified by dhsingh on 20.05.2004 for bug# 3631107
1732 -- SELECT 1 from dual
1733 -- WHERE EXISTS (SELECT *
1734 -- FROM AMS_TRIGGERS_VL
1735 -- WHERE UPPER(TRIGGER_NAME) = UPPER(p_trig_rec.trigger_name));
1736 SELECT 1 from dual
1737 WHERE EXISTS (SELECT *
1738 FROM AMS_TRIGGERS_TL
1739 WHERE UPPER(TRIGGER_NAME) = UPPER(p_trig_rec.trigger_name)
1740 AND language = USERENV('LANG'));
1741 -- end of modification by dhsingh
1742
1743 CURSOR c_trig_name_updt IS
1744 -- modified by dhsingh on 20.05.2004 for bug# 3631107
1745 -- SELECT 1 from dual
1746 -- WHERE EXISTS (SELECT *
1747 -- FROM AMS_TRIGGERS_VL
1748 -- WHERE UPPER(TRIGGER_NAME) = UPPER(p_trig_rec.trigger_name)
1749 -- AND TRIGGER_ID <> p_trig_rec.trigger_id);
1750 SELECT 1 from dual
1751 WHERE EXISTS (SELECT *
1752 FROM AMS_TRIGGERS_TL
1753 WHERE UPPER(TRIGGER_NAME) = UPPER(p_trig_rec.trigger_name)
1754 AND TRIGGER_ID <> p_trig_rec.trigger_id
1755 AND language = USERENV('LANG'));
1756 -- end of modification by dhsingh
1757
1758 l_dummy NUMBER ;
1759 -- end soagrawa 07-Aug-2001
1760
1761 BEGIN
1762
1763 x_return_status := FND_API.g_ret_sts_success;
1764
1765 -- For create_trigger, when trigger_id is passed in, we need to
1766 -- check if this trigger_id is unique.
1767 IF p_validation_mode = JTF_PLSQL_API.g_create
1768 AND p_trig_rec.trigger_id IS NOT NULL
1769 THEN
1770 IF AMS_Utility_PVT.check_uniqueness(
1771 'AMS_TRIGGERS',
1772 'TRIGGER_ID = ' || p_trig_rec.trigger_id
1773 ) = FND_API.g_false
1774 THEN
1775 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_DUP_TRIG_ID');
1776 x_return_status := FND_API.g_ret_sts_error;
1777 RETURN;
1778 END IF;
1779 END IF;
1780
1781 -- Check if Trigger_name is unique. Need to handle create and
1782 -- update differently.
1783
1784 -- modified by soagrawa on 07-aug-2001
1785 -- replaced use of ams_utility_pvt.check_uniqueness by manual check
1786 -- due to bug in check_uniqueness - does not handle AND in the condition
1787 /*
1788 -- Unique TRIGGER_NAME and TRIGGER_CREATED_FOR
1789 l_where_clause := ' UPPER(TRIGGER_NAME) = ''' || UPPER(p_trig_rec.trigger_name)||'''' ;
1790
1791 -- For Updates, must also check that uniqueness is not checked against the same record.
1792 IF p_validation_mode <> JTF_PLSQL_API.g_create THEN
1793 l_where_clause := l_where_clause || ' AND TRIGGER_ID <> ' || p_trig_rec.trigger_id;
1794 END IF;
1795
1796 IF AMS_Utility_PVT.Check_Uniqueness(
1797 p_table_name => 'AMS_TRIGGERS_VL',
1798 p_where_clause => l_where_clause
1799 ) = FND_API.g_false
1800 THEN
1801 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_DUP_TRIG_NAME');
1802 x_return_status := FND_API.g_ret_sts_error;
1803 RETURN;
1804 END IF;
1805 */
1806
1807 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
1808 OPEN c_trig_name_updt;
1809 FETCH c_trig_name_updt INTO l_dummy ;
1810 CLOSE c_trig_name_updt ;
1811 ELSE
1812 OPEN c_trig_name;
1813 FETCH c_trig_name INTO l_dummy ;
1814 CLOSE c_trig_name ;
1815 END IF ;
1816
1817 IF l_dummy IS NOT NULL THEN
1818 -- Duplicate Trigger
1819 AMS_Utility_PVT.Error_Message('AMS_TRIG_DUP_TRIG_NAME');
1820 x_return_status := FND_API.g_ret_sts_error;
1821 RETURN;
1822 END IF ;
1823
1824 -- end changes soagrawa 07-Aug-2001
1825
1826
1827
1828 END Check_Trig_Uk_Items;
1829
1830
1831 -- Start of Comments
1832 --
1833 -- NAME
1834 -- Check_Trig_fk_Items
1835 --
1836 -- PURPOSE
1837 -- This procedure is to validate ams_triggers items
1838 -- It will validates the Foreign keys
1839 --
1840 -- NOTES
1841 --
1842 --
1843 -- HISTORY
1844 -- 10/25/1999 ptendulk created
1845 -- End of Comments
1846 PROCEDURE Check_Trig_fk_Items
1847 ( p_trig_rec IN trig_rec_type,
1848 x_return_status OUT NOCOPY VARCHAR2
1849 )
1850 IS
1854 l_pk_data_type VARCHAR2(30);
1851 l_table_name VARCHAR2(30);
1852 l_pk_name VARCHAR2(30);
1853 l_pk_value VARCHAR2(30);
1855 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1856 BEGIN
1857 --
1858 -- Initialize the OUT parameter
1859 --
1860 x_return_status := FND_API.g_ret_sts_success ;
1861
1862 -- Check arc_trigger_created_for
1863 /* IF p_trig_rec.arc_trigger_created_for <> FND_API.G_MISS_CHAR THEN
1864 IF p_trig_rec.arc_trigger_created_for <> 'CAMP'
1865 -- Commented by ptendulk on 14-Oct-2001 as Metric is not using triggers for refresh.
1866 -- AND p_trig_rec.arc_trigger_created_for <> 'AMET'
1867 THEN
1868 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_INVALID_CREATED_FOR');
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 -- If any errors happen abort API/Procedure.
1871 RETURN;
1872 END IF;
1873 END IF;
1874 */
1875 l_table_name := 'FND_TIMEZONES_VL';
1876 l_pk_name := 'UPGRADE_TZ_ID' ;
1877 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
1878 l_pk_value := p_trig_rec.timezone_id ;
1879
1880 IF p_trig_rec.timezone_id <> FND_API.G_MISS_NUM THEN
1881 IF AMS_Utility_PVT.Check_FK_Exists (
1882 p_table_name => l_table_name
1883 ,p_pk_name => l_pk_name
1884 ,p_pk_value => l_pk_value
1885 ,p_pk_data_type => l_pk_data_type
1886 ,p_additional_where_clause => null
1887 ) = FND_API.G_FALSE
1888 THEN
1889 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_INVALID_TIMEZONE');
1890 x_return_status := FND_API.G_RET_STS_ERROR;
1891 RETURN;
1892 END IF;
1893 END IF ;
1894
1895 END Check_Trig_fk_Items ;
1896
1897 -- Start of Comments
1898 --
1899 -- NAME
1900 -- Check_Trig_lookup_Items
1901 --
1902 -- PURPOSE
1903 -- This procedure is to validate ams_triggers items
1904 -- It will validates the lookup keys
1905 -- NOTES
1906 --
1907 -- HISTORY
1908 -- 10/25/1999 ptendulk created
1909 -- End of Comments
1910
1911 PROCEDURE Check_Trig_Lookup_Items
1912 ( p_trig_rec IN trig_rec_type,
1913 x_return_status OUT NOCOPY VARCHAR2
1914 ) IS
1915 BEGIN
1916 --
1917 -- Initialize the OUT parameter
1918 --
1919 x_return_status := FND_API.g_ret_sts_success ;
1920 -- Check triggering_type
1921 IF p_trig_rec.triggering_type <> FND_API.G_MISS_CHAR
1922 THEN
1923 IF AMS_Utility_PVT.Check_Lookup_Exists
1924 ( p_lookup_table_name => 'AMS_LOOKUPS'
1925 ,p_lookup_type => 'AMS_TRIGGER_TYPE'
1926 ,p_lookup_code => p_trig_rec.triggering_type
1927 ) = FND_API.G_FALSE
1928 THEN
1929 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_INVALID_TRIGGER_TYPE');
1930 x_return_status := FND_API.G_RET_STS_ERROR;
1931 -- If any errors happen abort API/Procedure.
1932 RETURN;
1933 END IF;
1934 END IF;
1935
1936 -- Check repeat_frequency_type
1937 IF p_trig_rec.repeat_frequency_type <> FND_API.G_MISS_CHAR
1938 AND p_trig_rec.repeat_frequency_type IS NOT NULL
1939 THEN
1940 IF AMS_Utility_PVT.Check_Lookup_Exists
1941 ( p_lookup_table_name => 'AMS_LOOKUPS'
1942 ,p_lookup_type => 'AMS_TRIGGER_FREQUENCY_TYPE'
1943 ,p_lookup_code => p_trig_rec.repeat_frequency_type
1944 ) = FND_API.G_FALSE
1945 THEN
1946 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_INVALID_FREQ_TYPE');
1947 x_return_status := FND_API.G_RET_STS_ERROR;
1948 -- If any errors happen abort API/Procedure.
1949 RETURN;
1950 END IF;
1951 END IF;
1952
1953
1954 END Check_Trig_Lookup_Items ;
1955
1956 -- Start of Comments
1957 --
1958 -- NAME
1959 -- Check_Trig_Items
1960 --
1961 -- PURPOSE
1962 -- This procedure is to validate ams_triggers items
1963 -- NOTES
1964 --
1965 -- HISTORY
1966 -- 10/25/1999 ptendulk created
1967 -- End of Comments
1968
1969 PROCEDURE check_trig_items(
1970 p_trig_rec IN trig_rec_type,
1971 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1972 x_return_status OUT NOCOPY VARCHAR2
1973 )
1974 IS
1975 BEGIN
1976
1977 Check_Trig_Req_Items(
1978 p_trig_rec => p_trig_rec,
1979 x_return_status => x_return_status
1980 );
1981 IF x_return_status <> FND_API.g_ret_sts_success THEN
1982 RETURN;
1983 END IF;
1984 --dbms_output.put_line('After req : '||x_return_status);
1985 Check_Trig_UK_Items(
1986 p_trig_rec => p_trig_rec,
1987 p_validation_mode => p_validation_mode,
1988 x_return_status => x_return_status
1989 );
1990
1991 IF x_return_status <> FND_API.g_ret_sts_success THEN
1992 RETURN;
1993 END IF;
1994 --dbms_output.put_line('After uk : '||x_return_status);
1995 Check_Trig_Fk_Items(
1996 p_trig_rec => p_trig_rec,
2000 IF x_return_status <> FND_API.g_ret_sts_success THEN
1997 x_return_status => x_return_status
1998 );
1999
2001 RETURN;
2002 END IF;
2003 --dbms_output.put_line('After fk : '||x_return_status);
2004 Check_Trig_Lookup_Items(
2005 p_trig_rec => p_trig_rec,
2006 x_return_status => x_return_status
2007 );
2008
2009 IF x_return_status <> FND_API.g_ret_sts_success THEN
2010 RETURN;
2011 END IF;
2012
2013 END Check_Trig_Items;
2014
2015
2016 -- Start of Comments
2017 --
2018 -- NAME
2019 -- Find_End_Date
2020 --
2021 -- PURPOSE
2022 -- This procedure is to find the End Date for the Activities
2023 --
2024 -- NOTES
2025 --
2026 --
2027 -- HISTORY
2028 -- 02/26/2000 ptendulk created
2029 -- 04/24/2000 ptendulk Commented as its not being used
2030 -- End of Comments
2031 --PROCEDURE Find_End_Date
2032 -- (p_arc_act IN VARCHAR2,
2033 -- p_act_id IN NUMBER,
2034 -- x_dt OUT DATE)
2035 --IS
2036 --CURSOR c_camp IS
2037 -- SELECT actual_exec_end_date
2038 -- FROM ams_campaigns_vl
2039 -- WHERE campaign_id = p_act_id ;
2040 --
2041 --CURSOR c_eveh IS
2042 -- SELECT active_to_date
2043 -- FROM ams_event_headers_vl
2044 -- WHERE event_header_id = p_act_id ;
2045 --
2046 --CURSOR c_eveo IS
2047 -- SELECT event_end_date
2048 -- FROM ams_event_offers_vl
2049 -- WHERE event_offer_id = p_act_id ;
2050 --
2051 --BEGIN
2052 -- IF p_arc_act = 'CAMP' THEN
2053 -- OPEN c_camp ;
2054 -- FETCH c_camp INTO x_dt ;
2055 -- CLOSE c_camp ;
2056 -- ELSIF p_arc_act = 'EVEH' THEN
2057 -- OPEN c_eveh ;
2058 -- FETCH c_eveh INTO x_dt ;
2059 -- CLOSE c_eveh ;
2060 -- ELSIF p_arc_act = 'EVEO' THEN
2061 -- OPEN c_eveo ;
2062 -- FETCH c_eveo INTO x_dt ;
2063 -- CLOSE c_eveo ;
2064 -- END IF;
2065 --END Find_End_Date;
2066
2067 -- Start of Comments
2068 --
2069 -- NAME
2070 -- Validate_Trig_Record
2071 --
2072 -- PURPOSE
2073 -- This procedure is to validate ams_triggers table.
2074 -- This is an example if you need to call validation procedure from the UI site.
2075 --
2076 -- NOTES
2077 --
2078 --
2079 -- HISTORY
2080 -- 07/26/1999 ptendulk created
2081 -- 02/26/2000 ptendulk Added Validation for the Trigger end Date
2082 -- 30-jul-2003 anchaudh modified comparison operator to fix P1 bug# 3064909
2083 -- 21-aug-2003 soagrawa Fixed bug 3108929
2084 -- 27-aug-2003 soagrawa Fixed bug 3115141
2085 -- End of Comments
2086 PROCEDURE Check_Trig_Record(
2087 p_trig_rec IN trig_rec_type,
2088 p_complete_rec IN trig_rec_type,
2089 x_return_status OUT NOCOPY VARCHAR2
2090 )
2091 IS
2092
2093 l_start_date DATE;
2094 l_end_date DATE;
2095 l_daily_start_time DATE ;
2096 l_daily_end_time DATE ;
2097 l_trigger_created_for VARCHAR2(30);
2098 l_trigger_created_for_id NUMBER ;
2099
2100 l_table_name VARCHAR2(30);
2101 l_pk_name VARCHAR2(30);
2102 l_pk_value VARCHAR2(30);
2103 l_pk_data_type VARCHAR2(30);
2104 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2105
2106 l_repeat_freq VARCHAR2(30);
2107 l_act_dt DATE;
2108
2109 l_tz_end_date DATE;
2110 l_msg_count NUMBER ;
2111 l_msg_data VARCHAR2(2000);
2112
2113 BEGIN
2114 --
2115 -- Initialize the Out Variable
2116 --
2117 x_return_status := FND_API.g_ret_sts_success;
2118
2119 -- Check start date time
2120 IF (p_Trig_rec.user_repeat_stop_date_time IS NOT NULL AND
2121 p_Trig_rec.user_repeat_stop_date_time <> FND_API.G_MISS_DATE) OR
2122 p_trig_rec.user_start_date_time <> FND_API.G_MISS_DATE
2123 THEN
2124 IF p_trig_rec.user_start_date_time = FND_API.G_MISS_DATE THEN
2125 l_start_date := p_complete_rec.user_start_date_time;
2126 ELSE
2127 l_start_date := p_trig_rec.user_start_date_time;
2128 END IF ;
2129 IF p_trig_rec.user_repeat_stop_date_time = FND_API.G_MISS_DATE THEN
2130 l_end_date := p_complete_rec.user_repeat_stop_date_time ;
2131 ELSE
2132 l_end_date := p_trig_rec.user_repeat_stop_date_time ;
2133 END IF ;
2134 -- Following code is added by ptendulk on 26Feb2000
2135 -- IF p_trig_rec.trigger_created_for_id = FND_API.G_MISS_NUM THEN
2136 -- l_trigger_created_for_id := p_complete_rec.trigger_created_for_id ;
2137 -- ELSE
2138 -- l_trigger_created_for_id := p_trig_rec.trigger_created_for_id ;
2139 -- END IF;
2140 --
2141 -- IF p_trig_rec.arc_trigger_created_for = FND_API.G_MISS_CHAR THEN
2142 -- l_trigger_created_for := p_complete_rec.arc_trigger_created_for ;
2143 -- ELSE
2144 -- l_trigger_created_for := p_trig_rec.arc_trigger_created_for ;
2145 -- END IF;
2146 --
2147 IF l_end_date IS NOT NULL THEN
2151 FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_DT_RANGE');
2148 IF l_start_date > l_end_date THEN
2149 -- invalid item
2150 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2152 FND_MSG_PUB.Add;
2153 END IF;
2154 x_return_status := FND_API.G_RET_STS_ERROR;
2155 RETURN;
2156 END IF;
2157
2158 --following code is added by cgoyal for bugfix#3055863
2159 -- anchaudh modified operator to fix P1 bug# 3064909
2160 IF p_trig_rec.trigger_id IS NULL -- soagrawa added this on 21-aug-2003 for bug# 3108929, check only for create
2161 THEN
2162 -- soagrawa added time zone conversion on 27-aug-2003 for bug# 3115141
2163
2164 AMS_UTILITY_PVT.Convert_Timezone(
2165 p_init_msg_list => FND_API.G_FALSE ,
2166 x_return_status => x_return_status ,
2167 x_msg_count => l_msg_count ,
2168 x_msg_data => l_msg_data ,
2169 p_user_tz_id => p_trig_rec.timezone_id , -- required
2170 p_in_time => l_end_date , -- required
2171 x_out_time => l_tz_end_date
2172 );
2173
2174 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2175 RAISE FND_API.G_EXC_ERROR;
2176 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2178 END IF;
2179 -- end soagrawa for bug# 3115141
2180
2181 IF l_tz_end_date < SYSDATE THEN
2182 -- IF l_end_date < SYSDATE THEN
2183 -- IF l_end_date > SYSDATE THEN
2184 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2185 FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_END_DT');
2186 FND_MSG_PUB.Add;
2187 END IF;
2188 x_return_status := FND_API.G_RET_STS_ERROR;
2189 RETURN;
2190 END IF;
2191 END IF;
2192
2193 -- Following code is commented by ptendulk on 26th apr
2194 -- as trigger can fire after the campaign is expired
2195 -- Following code is added by ptendulk on 26Feb2000
2196 --
2197 -- Get the end Date for the Activity
2198 --
2199 -- Find_End_Date( p_arc_act => l_trigger_created_for,
2200 -- p_act_id => l_trigger_created_for_id,
2201 -- x_dt => l_act_dt ) ;
2202
2203 -- IF l_act_dt < l_end_date THEN
2204 -- -- invalid item
2205 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2206 -- THEN -- MMSG
2207 ---- DBMS_OUTPUT.Put_Line('Start Date time or End Date Time is invalid');
2208 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_DT_RANGE');
2209 -- FND_MSG_PUB.Add;
2210 -- END IF;
2211 -- x_return_status := FND_API.G_RET_STS_ERROR;
2212 -- -- If any errors happen abort API/Procedure.
2213 -- RETURN;
2214 -- END IF;
2215
2216 END IF;
2217 END IF;
2218 --dbms_output.put_line('After Date Check : '||x_return_status);
2219 -- Check Repeat daily start time
2220 IF (p_trig_rec.user_repeat_daily_start_time <> FND_API.G_MISS_DATE AND
2221 p_trig_rec.user_repeat_daily_start_time IS NOT NULL ) OR
2222 (p_trig_rec.user_repeat_daily_end_time <> FND_API.G_MISS_DATE AND
2223 p_trig_rec.user_repeat_daily_end_time IS NOT NULL )
2224 THEN
2225 IF p_trig_rec.user_repeat_daily_start_time = FND_API.G_MISS_DATE THEN
2226 l_daily_start_time := p_complete_rec.user_repeat_daily_start_time;
2227 ELSE
2228 l_daily_start_time := p_trig_rec.user_repeat_daily_start_time;
2229 END IF;
2230
2231 IF p_trig_rec.user_repeat_daily_end_time = FND_API.G_MISS_DATE THEN
2232 l_daily_end_time := p_complete_rec.user_repeat_daily_end_time;
2233 ELSE
2234 l_daily_end_time := p_trig_rec.user_repeat_daily_end_time;
2235 END IF;
2236
2237 IF (l_daily_start_time IS NULL AND l_daily_end_time IS NOT NULL )
2238 OR (l_daily_start_time IS NOT NULL AND l_daily_end_time IS NULL )
2239 THEN
2240 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_RPT_DAILY_TM');
2241 x_return_status := FND_API.G_RET_STS_ERROR;
2242 -- If any errors happen abort API/Procedure.
2243 RETURN;
2244 ELSIF l_daily_start_time > l_daily_end_time
2245 THEN
2246 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_INVALID_DAILY_RANGE');
2247 x_return_status := FND_API.G_RET_STS_ERROR;
2248 -- If any errors happen abort API/Procedure.
2249 RETURN;
2250 END IF;
2251 END IF;
2252
2253 --dbms_output.put_line('After time check : '||x_return_status);
2254 IF p_trig_rec.arc_trigger_created_for <> FND_API.G_MISS_CHAR
2255 OR p_trig_rec.trigger_created_for_id <> FND_API.G_MISS_NUM THEN
2256
2257 IF p_trig_rec.trigger_created_for_id = FND_API.G_MISS_NUM THEN
2258 l_trigger_created_for_id := p_complete_rec.trigger_created_for_id ;
2259 ELSE
2260 l_trigger_created_for_id := p_trig_rec.trigger_created_for_id ;
2261 END IF;
2262
2266 l_trigger_created_for := p_trig_rec.arc_trigger_created_for ;
2263 IF p_trig_rec.arc_trigger_created_for = FND_API.G_MISS_CHAR THEN
2264 l_trigger_created_for := p_complete_rec.arc_trigger_created_for ;
2265 ELSE
2267 END IF;
2268
2269
2270 -- Get table_name and pk_name for the ARC qualifier.
2271 AMS_Utility_PVT.Get_Qual_Table_Name_And_PK (
2272 p_sys_qual => l_trigger_created_for,
2273 x_return_status => x_return_status,
2274 x_table_name => l_table_name,
2275 x_pk_name => l_pk_name
2276 );
2277
2278 l_pk_value := l_trigger_created_for_id;
2279 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
2280 l_additional_where_clause := NULL;
2281 --dbms_output.put_line('Tab name : '||l_table_name);
2282 --dbms_output.put_line('pk name : '||l_pk_name);
2283 /* IF AMS_Utility_PVT.Check_FK_Exists (
2284 p_table_name => l_table_name
2285 ,p_pk_name => l_pk_name
2286 ,p_pk_value => l_pk_value
2287 ,p_pk_data_type => l_pk_data_type
2288 ,p_additional_where_clause => l_additional_where_clause
2289 ) = FND_API.G_FALSE
2290 THEN
2291 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_INVALID_CREATED_FOR');
2292 x_return_status := FND_API.G_RET_STS_ERROR;
2293 RETURN;
2294 END IF;
2295 */
2296 END IF;
2297 --dbms_output.put_line('After Camp Chk : '||x_return_status);
2298 -- Repeat Every X Frequency
2299 IF p_trig_rec.repeat_frequency_type <> FND_API.G_MISS_CHAR THEN
2300
2301 IF p_trig_rec.repeat_every_x_frequency = FND_API.G_MISS_NUM THEN
2302 l_repeat_freq := p_complete_rec.repeat_every_x_frequency ;
2303 ELSE
2304 l_repeat_freq := p_trig_rec.repeat_every_x_frequency ;
2305 END IF;
2306
2307 IF p_trig_rec.repeat_frequency_type <> 'NONE' AND
2308 l_repeat_freq IS NULL
2309 THEN
2310 AMS_UTILITY_PVT.Error_Message('AMS_TRIG_MISSING_EVERY_X_FREQ');
2311 x_return_status := FND_API.G_RET_STS_ERROR;
2312 -- If any errors happen abort API/Procedure.
2313 RETURN;
2314 END IF;
2315 END IF;
2316
2317 END Check_Trig_Record;
2318
2319 -- Start of Comments
2320 --
2321 -- NAME
2322 -- Init_Trig_Rec
2323 --
2324 -- PURPOSE
2325 -- This procedure is to Initialize the Record type before Updation.
2326 --
2327 -- NOTES
2328 --
2329 --
2330 -- HISTORY
2331 -- 10/26/1999 ptendulk created
2332 -- 22/apr/03 cgoyal added notify_flag and execute_schedule_flag
2333 -- End of Comments
2334 PROCEDURE Init_Trig_Rec(
2335 x_trig_rec OUT NOCOPY trig_rec_type
2336 )
2337 IS
2338 BEGIN
2339 x_trig_rec.trigger_id := FND_API.G_MISS_NUM ;
2340 x_trig_rec.last_update_date := FND_API.G_MISS_DATE ;
2341 x_trig_rec.last_updated_by := FND_API.G_MISS_NUM ;
2342 x_trig_rec.creation_date := FND_API.G_MISS_DATE ;
2343 x_trig_rec.created_by := FND_API.G_MISS_NUM ;
2344 x_trig_rec.last_update_login := FND_API.G_MISS_NUM ;
2345 x_trig_rec.object_version_number := FND_API.G_MISS_NUM ;
2346 x_trig_rec.process_id := FND_API.G_MISS_NUM ;
2347 x_trig_rec.trigger_created_for_id := FND_API.G_MISS_NUM ;
2348 x_trig_rec.arc_trigger_created_for := FND_API.G_MISS_CHAR ;
2349 x_trig_rec.triggering_type := FND_API.G_MISS_CHAR ;
2350 x_trig_rec.trigger_name := FND_API.G_MISS_CHAR ;
2351 x_trig_rec.view_application_id := FND_API.G_MISS_NUM ;
2352 x_trig_rec.timezone_id := FND_API.G_MISS_NUM ;
2353 x_trig_rec.user_start_date_time := FND_API.G_MISS_DATE ;
2354 x_trig_rec.start_date_time := FND_API.G_MISS_DATE ;
2355 x_trig_rec.user_last_run_date_time := FND_API.G_MISS_DATE ;
2356 x_trig_rec.last_run_date_time := FND_API.G_MISS_DATE ;
2357 x_trig_rec.user_next_run_date_time := FND_API.G_MISS_DATE ;
2358 x_trig_rec.next_run_date_time := FND_API.G_MISS_DATE ;
2359 x_trig_rec.user_repeat_daily_start_time := FND_API.G_MISS_DATE ;
2360 x_trig_rec.repeat_daily_start_time := FND_API.G_MISS_DATE ;
2361 x_trig_rec.user_repeat_daily_end_time := FND_API.G_MISS_DATE ;
2362 x_trig_rec.repeat_daily_end_time := FND_API.G_MISS_DATE ;
2363 x_trig_rec.repeat_frequency_type := FND_API.G_MISS_CHAR ;
2364 x_trig_rec.repeat_every_x_frequency := FND_API.G_MISS_NUM ;
2365 x_trig_rec.user_repeat_stop_date_time := FND_API.G_MISS_DATE ;
2366 x_trig_rec.repeat_stop_date_time := FND_API.G_MISS_DATE ;
2367 x_trig_rec.metrics_refresh_type := FND_API.G_MISS_CHAR ;
2368 x_trig_rec.description := FND_API.G_MISS_CHAR ;
2369 x_trig_rec.notify_flag := FND_API.G_MISS_CHAR ;
2370 x_trig_rec.execute_schedule_flag := FND_API.G_MISS_CHAR ;
2371 x_trig_rec.TRIGGERED_STATUS := FND_API.G_MISS_CHAR ;-- anchaudh added for R12 monitors.
2372 x_trig_rec.USAGE := FND_API.G_MISS_CHAR ;-- anchaudh added for R12 monitors.
2373 END Init_Trig_Rec ;
2374
2375 -- Start of Comments
2379 --
2376 --
2377 -- NAME
2378 -- Complete_Trig_rec
2380 -- PURPOSE
2381 -- This procedure is to complete the Rec type sent before Update
2382 --
2383 -- NOTES
2384 --
2385 --
2386 -- HISTORY
2387 -- 10/26/1999 ptendulk created
2388 -- 22-apr-03 cgoyal added NOTIFY_FLAG, EXECUTE_SCHEDULE_FLAG column defaulting
2389 -- End of Comments
2390
2391 PROCEDURE Complete_Trig_Rec(
2392 p_trig_rec IN trig_rec_type,
2393 x_complete_rec OUT NOCOPY trig_rec_type
2394 )
2395 IS
2396
2397 CURSOR c_trig IS
2398 SELECT *
2399 FROM ams_triggers_vl
2400 WHERE trigger_id = p_trig_rec.trigger_id;
2401
2402 l_trig_rec c_trig%ROWTYPE;
2403
2404 BEGIN
2405
2406 x_complete_rec := p_trig_rec;
2407
2408 OPEN c_trig;
2409 FETCH c_trig INTO l_trig_rec;
2410 IF c_trig%NOTFOUND THEN
2411 CLOSE c_trig;
2412 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2413 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2414 FND_MSG_PUB.add;
2415 END IF;
2416 RAISE FND_API.g_exc_error;
2417 END IF;
2418 CLOSE c_trig;
2419
2420 IF p_trig_rec.process_id = FND_API.G_MISS_NUM THEN
2421 x_complete_rec.process_id := l_trig_rec.process_id;
2422 END IF;
2423
2424 IF p_trig_rec.trigger_created_for_id = FND_API.G_MISS_NUM THEN
2425 x_complete_rec.trigger_created_for_id := l_trig_rec.trigger_created_for_id;
2426 END IF;
2427
2428 IF p_trig_rec.arc_trigger_created_for = FND_API.G_MISS_CHAR THEN
2429 x_complete_rec.arc_trigger_created_for := l_trig_rec.arc_trigger_created_for ;
2430 END IF;
2431
2432 IF p_trig_rec.triggering_type = FND_API.G_MISS_CHAR THEN
2433 x_complete_rec.triggering_type := l_trig_rec.triggering_type ;
2434 END IF;
2435
2436 IF p_trig_rec.trigger_name = FND_API.G_MISS_CHAR THEN
2437 x_complete_rec.trigger_name := l_trig_rec.trigger_name ;
2438 END IF;
2439
2440 IF p_trig_rec.view_application_id = FND_API.G_MISS_NUM THEN
2441 x_complete_rec.view_application_id := l_trig_rec.view_application_id ;
2442 END IF;
2443
2444 IF p_trig_rec.timezone_id = FND_API.G_MISS_NUM THEN
2445 x_complete_rec.timezone_id := l_trig_rec.timezone_id ;
2446 END IF;
2447
2448 IF p_trig_rec.user_start_date_time = FND_API.G_MISS_DATE THEN
2449 x_complete_rec.user_start_date_time := l_trig_rec.user_start_date_time ;
2450 END IF;
2451
2452 IF p_trig_rec.start_date_time = FND_API.G_MISS_DATE THEN
2453 x_complete_rec.start_date_time := l_trig_rec.start_date_time ;
2454 END IF;
2455
2456 IF p_trig_rec.user_last_run_date_time = FND_API.G_MISS_DATE THEN
2457 x_complete_rec.user_last_run_date_time := l_trig_rec.user_last_run_date_time ;
2458 END IF;
2459
2460 IF p_trig_rec.last_run_date_time = FND_API.G_MISS_DATE THEN
2461 x_complete_rec.last_run_date_time := l_trig_rec.last_run_date_time ;
2462 END IF;
2463
2464 IF p_trig_rec.user_next_run_date_time = FND_API.G_MISS_DATE THEN
2465 x_complete_rec.user_next_run_date_time := l_trig_rec.user_next_run_date_time ;
2466 END IF;
2467
2468 IF p_trig_rec.next_run_date_time = FND_API.G_MISS_DATE THEN
2469 x_complete_rec.next_run_date_time := l_trig_rec.next_run_date_time ;
2470 END IF;
2471
2472 IF p_trig_rec.user_repeat_daily_start_time = FND_API.G_MISS_DATE THEN
2473 x_complete_rec.user_repeat_daily_start_time := l_trig_rec.user_repeat_daily_start_time ;
2474 END IF;
2475
2476 IF p_trig_rec.repeat_daily_start_time = FND_API.G_MISS_DATE THEN
2477 x_complete_rec.repeat_daily_start_time := l_trig_rec.repeat_daily_start_time ;
2478 END IF;
2479
2480 IF p_trig_rec.user_repeat_daily_end_time = FND_API.G_MISS_DATE THEN
2481 x_complete_rec.user_repeat_daily_end_time := l_trig_rec.user_repeat_daily_end_time ;
2482 END IF;
2483
2484 IF p_trig_rec.repeat_daily_end_time = FND_API.G_MISS_DATE THEN
2485 x_complete_rec.repeat_daily_end_time := l_trig_rec.repeat_daily_end_time ;
2486 END IF;
2487
2488 IF p_trig_rec.repeat_frequency_type = FND_API.G_MISS_CHAR THEN
2489 x_complete_rec.repeat_frequency_type := l_trig_rec.repeat_frequency_type ;
2490 END IF;
2491
2492 IF p_trig_rec.repeat_every_x_frequency = FND_API.G_MISS_NUM THEN
2493 x_complete_rec.repeat_every_x_frequency := l_trig_rec.repeat_every_x_frequency;
2494 END IF;
2495
2496 IF p_trig_rec.repeat_stop_date_time = FND_API.G_MISS_DATE THEN
2497 x_complete_rec.repeat_stop_date_time := l_trig_rec.repeat_stop_date_time ;
2498 END IF;
2499
2500 IF p_trig_rec.user_repeat_stop_date_time = FND_API.G_MISS_DATE THEN
2501 x_complete_rec.user_repeat_stop_date_time := l_trig_rec.user_repeat_stop_date_time ;
2502 END IF;
2503
2504
2505
2506 IF p_trig_rec.metrics_refresh_type = FND_API.G_MISS_CHAR THEN
2507 x_complete_rec.metrics_refresh_type := l_trig_rec.metrics_refresh_type ;
2508 END IF;
2509
2510 IF p_trig_rec.description = FND_API.G_MISS_CHAR THEN
2511 x_complete_rec.description := l_trig_rec.description ;
2512 END IF;
2513
2514 -- CGOYAL added for 11.5.8 backport
2515 IF p_trig_rec.NOTIFY_FLAG = FND_API.G_MISS_CHAR THEN
2516 x_complete_rec.NOTIFY_FLAG := l_trig_rec.NOTIFY_FLAG ;
2517 END IF;
2518
2519 IF p_trig_rec.EXECUTE_SCHEDULE_FLAG = FND_API.G_MISS_CHAR THEN
2520 x_complete_rec.EXECUTE_SCHEDULE_FLAG := l_trig_rec.EXECUTE_SCHEDULE_FLAG ;
2521 END IF;
2522
2523 -- anchaudh added for R12 monitors.
2524 IF p_trig_rec.TRIGGERED_STATUS = FND_API.G_MISS_CHAR THEN
2525 x_complete_rec.TRIGGERED_STATUS := l_trig_rec.TRIGGERED_STATUS ;
2526 END IF;
2527
2528 IF p_trig_rec.USAGE = FND_API.G_MISS_CHAR THEN
2529 x_complete_rec.USAGE := l_trig_rec.USAGE ;
2530 END IF;
2531 --
2532
2533 END Complete_Trig_Rec ;
2534
2535 END AMS_Trig_PVT;