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