[Home] [Help]
PACKAGE BODY: APPS.AMS_CTD_PVT
Source
1 PACKAGE BODY AMS_CTD_PVT AS
2 /* $Header: amsvctdb.pls 120.5 2006/09/06 17:35:51 dbiswas noship $ */
3
4 TYPE NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Ctd_PVT';
7 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvctdb.pls';
8
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
11
12 -- ===============================================================
13 -- Start of Comments
14 -- Package name
15 -- AMS_CTD_PVT
16 -- Purpose
17 --
18 -- This package contains all the program units for Click Through Destinations
19 --
20 -- History
21 -- 02/27/04 rrajesh bugfix: 3470296
22 -- NOTE
23 --
24 -- End of Comments
25 -- ===============================================================
26 -- Start of Comments
27 -- Name
28 -- write_debug_message
29 --
30 -- Purpose
31 -- This is a private procedure to write debug messages to the log table.
32 --
33 -- Private procedure to write debug message to FND_LOG table
34 -- ===============================================================
35 PROCEDURE write_debug_message(p_log_level NUMBER,
36 p_procedure_name VARCHAR2,
37 p_label VARCHAR2,
38 p_text VARCHAR2
39 )
40 IS
41 l_module_name VARCHAR2(400);
42 DELIMETER CONSTANT VARCHAR2(1) := '.';
43
44 BEGIN
45 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
46 -- Set the Module Name
47 l_module_name := 'ams'||DELIMETER||'plsql'||DELIMETER||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||'-'||p_label;
48
49
50 -- Log the Message
51 AMS_UTILITY_PVT.debug_message(p_log_level,
52 l_module_name,
53 p_text
54 );
55
56 END IF;
57
58 --dbms_output.put_line(l_module_name||': '||p_text);
59
60 END write_debug_message;
61 -- ===============================================================
62 -- Start of Comments
63 -- Name
64 -- DELETE_ASSOCIATION_AND_CTD
65 --
66 -- Purpose
67 -- This procedure deletes associations and all relevant CTD information
68 -- if no other object is using the CTD
69 --
70 Procedure DELETE_ASSOCIATION_AND_CTD(
71 p_ctd_id_list JTF_NUMBER_TABLE,
72 p_used_by VARCHAR2,
73 p_used_by_val1 VARCHAR2
74 )
75 IS
76 l_ctd_exists_count NUMBER;
77
78 /* bugfix: 3470296. Added by rrajesh on 02/27/04 */
79 CURSOR C_CHECK_CTD_ASSOCIATION_EXISTS
80 IS
81 SELECT ctd_id
82 FROM ams_ctd_associations
83 WHERE used_by_type = p_used_by AND used_by_val1 = p_used_by_val1;
84 /* End bugfix: 3470296.*/
85
86 /* Bugfix: 4261272. Fix for SQL repository issue: 11753011 */
87 /* CURSOR C_GET_NOT_ASSOCIATED_CTD
88 IS
89 SELECT CTD_LIST1.CTD_ID
90 FROM
91 (SELECT column_value ctd_id
92 FROM TABLE(CAST(p_ctd_id_list as JTF_NUMBER_TABLE)) ) CTD_LIST1
93 WHERE CTD_LIST1.CTD_ID NOT IN
94 (SELECT ASSOC.CTD_ID
95 FROM AMS_CTD_ASSOCIATIONS assoc,
96 (SELECT column_value ctd_id
97 FROM TABLE(CAST(p_ctd_id_list as JTF_NUMBER_TABLE))) ctd_list
98 WHERE assoc.ctd_id = ctd_list.ctd_id
99 ); */
100 CURSOR C_GET_NOT_ASSOCIATED_CTD
101 IS
102 SELECT CTD_LIST1.CTD_ID
103 FROM
104 (SELECT column_value ctd_id
105 FROM TABLE(CAST(p_ctd_id_list as JTF_NUMBER_TABLE)) ) CTD_LIST1
106 WHERE NOT EXISTS (select 1 from AMS_CTD_ASSOCIATIONS assoc WHERE
107 assoc.ctd_id = ctd_list1.ctd_id);
108 /* End Bugfix: 4261272. */
109
110 l_not_associated_ctd_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
111 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'DELETE_ASSOCIATION_AND_CTD';
112
113 BEGIN
114
115 /* bugfix: 3470296. Added by rrajesh on 02/27/04 */
116 -- Check if any CTD is associated to this; if not exit
117 OPEN C_CHECK_CTD_ASSOCIATION_EXISTS;
118 FETCH C_CHECK_CTD_ASSOCIATION_EXISTS INTO l_ctd_exists_count;
119 IF C_CHECK_CTD_ASSOCIATION_EXISTS%NOTFOUND THEN
120 CLOSE C_CHECK_CTD_ASSOCIATION_EXISTS;
121 RETURN;
122 END IF;
123 CLOSE C_CHECK_CTD_ASSOCIATION_EXISTS;
124 /* End bugfix: 3470296.*/
125
126 write_debug_message(FND_LOG.LEVEL_EVENT,
127 PROCEDURE_NAME,
128 'BEGIN',
129 'Number of CTDs to be deleted = '||to_char(p_ctd_id_list.count)|| 'for Used By = '||p_used_by|| 'Used By Id = '||p_used_by_val1
130 );
131 write_debug_message(FND_LOG.LEVEL_EVENT,
132 PROCEDURE_NAME,
133 'BEFORE_BULK_DELETE_ASSOCIATION',
134 'Before bulk delete of association'
135 );
136 -- Delete the association
137 FORALL i in p_ctd_id_list.FIRST .. p_ctd_id_list.LAST
138 DELETE FROM AMS_CTD_ASSOCIATIONS
139 WHERE CTD_ID = p_ctd_id_list(i)
140 and used_by_type = p_used_by
141 and used_by_val1 = p_used_by_val1;
142
143 write_debug_message(FND_LOG.LEVEL_EVENT,
144 PROCEDURE_NAME,
145 'AFTER_BULK_DELETE_ASSOCIATION',
146 'Associations Deleted Successfully!!'
147 );
148
149 -- Now, check if the CTDs are being used by any other object
150 -- if not, remove the CTD and all other related objects
151 OPEN C_GET_NOT_ASSOCIATED_CTD;
152 FETCH C_GET_NOT_ASSOCIATED_CTD
153 BULK COLLECT INTO l_not_associated_ctd_list;
154 CLOSE C_GET_NOT_ASSOCIATED_CTD;
155
156 write_debug_message(FND_LOG.LEVEL_EVENT,
157 PROCEDURE_NAME,
158 'AFTER_CURSOR_FETCH_C_GET_NOT_ASSOCIATED_CTD',
159 'Number of CTDs to be deleted = '||to_char(l_not_associated_ctd_list.count)
160 );
161
162 -- delete from AMS_CTDS
163 FORALL i in l_not_associated_ctd_list.FIRST .. l_not_associated_ctd_list.LAST
164 DELETE FROM AMS_CTDS
165 WHERE CTD_ID = l_not_associated_ctd_list(i);
166
167 write_debug_message(FND_LOG.LEVEL_EVENT,
168 PROCEDURE_NAME,
169 'AFTER_CTD_BULK_DELETE',
170 'CTDs deleted successfully!!'
171 );
172
173 -- delete from AMS_CTD_PARAM_VALUES
174 FORALL i in l_not_associated_ctd_list.FIRST .. l_not_associated_ctd_list.LAST
175 DELETE FROM AMS_CTD_PARAM_VALUES
176 WHERE CTD_ID = l_not_associated_ctd_list(i);
177
178 write_debug_message(FND_LOG.LEVEL_EVENT,
179 PROCEDURE_NAME,
180 'AFTER_CTD_PARAM_VALUES_BULK_DELETE',
181 'CTD Param Values deleted successfully!!'
182 );
183
184 -- delete from AMS_CTD_ADHOC_PARAM_VALUES
185 FORALL i in l_not_associated_ctd_list.FIRST .. l_not_associated_ctd_list.LAST
186 DELETE FROM AMS_CTD_ADHOC_PARAM_VALUES
187 WHERE CTD_ID = l_not_associated_ctd_list(i);
188
189 write_debug_message(FND_LOG.LEVEL_EVENT,
190 PROCEDURE_NAME,
191 'AFTER_CTD_ADHOC_PARAM_VALUES_BULK_DELETE',
192 'CTD Adhoc Param Values deleted successfully!!'
193 );
194
195 END DELETE_ASSOCIATION_AND_CTD;
196 -- ===============================================================
197 -- Start of Comments
198 -- Name
199 -- CREATE_ASSOCIATION
200 --
201 -- Purpose
202 -- This procedure creates new associations between CTD Ids and the
203 -- Used By and Used By PK1
204 --
205
206 Procedure CREATE_ASSOCIATION(
207 p_ctd_id_list JTF_NUMBER_TABLE,
208 p_used_by VARCHAR2,
209 p_used_by_val1 VARCHAR2
210 )
211 IS
212 l_assoc_seq_id_list NUMBER_TABLE;
213
214 CURSOR C_GET_NEXT_SEQ_ID
215 IS
216 SELECT ams_ctd_associations_s.nextval
217 FROM DUAL;
218
219 l_list_count NUMBER;
220 list_count NUMBER;
221 l_sequence_id NUMBER;
222 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'CREATE_ASSOCIATION';
223
224 BEGIN
225 write_debug_message(FND_LOG.LEVEL_EVENT,
226 PROCEDURE_NAME,
227 'BEGIN',
228 'Begin procedure CREATE_ASSOCIATION'
229 );
230
231 l_list_count := p_ctd_id_list.count;
232
233 write_debug_message(FND_LOG.LEVEL_EVENT,
234 PROCEDURE_NAME,
235 'WRITE_INPUT_PARAM',
236 'Input Param: Used By = '||p_used_by||' Used By val1 = '||p_used_by_val1||' Number of CTD Associations to be created ='||to_char(l_list_count)
237 );
238 IF (l_list_count > 0 ) THEN
239 FOR i in p_ctd_id_list.FIRST .. p_ctd_id_list.LAST
240 LOOP
241 OPEN C_GET_NEXT_SEQ_ID;
242 FETCH C_GET_NEXT_SEQ_ID
243 INTO l_sequence_id;
244 CLOSE C_GET_NEXT_SEQ_ID;
245
246 l_assoc_seq_id_list(i) := l_sequence_id;
247 END LOOP;
248
249 write_debug_message(FND_LOG.LEVEL_EVENT,
250 PROCEDURE_NAME,
251 'BEFORE_CTD_ASSOCIATIONS_BULK_UPLOAD',
252 'About to bulk upload into AMS_CTD_ASSOCIATIONS'
253 );
254
255 --Do a Bulk Upload
256 FORALL i in p_ctd_id_list.FIRST .. p_ctd_id_list.LAST
257 INSERT INTO
258 AMS_CTD_ASSOCIATIONS
259 (
260 association_id,
261 ctd_id,
262 used_by_type,
263 used_by_val1,
264 used_by_val2,
265 used_by_val3,
266 used_by_val4,
267 used_by_val5,
268 object_version_number,
269 last_update_date,
270 last_updated_by,
271 last_update_login,
272 creation_date,
273 created_by,
274 security_group_id
275 )
276 VALUES
277 (
278 l_assoc_seq_id_list(i),
279 p_ctd_id_list(i),
280 p_used_by,
281 p_used_by_val1,
282 null,
283 null,
284 null,
285 null,
286 1,
287 sysdate,
288 FND_GLOBAL.USER_ID,
289 FND_GLOBAL.USER_ID,
290 sysdate,
291 FND_GLOBAL.USER_ID,
292 null
293
294 );
295
296 write_debug_message(FND_LOG.LEVEL_EVENT,
297 PROCEDURE_NAME,
298 'AFTER_CTD_ASSOCIATIONS_BULK_UPLOAD',
299 'Bulk upload AMS_CTD_ASSOCIATIONS completed successfully!!'
300 );
301 END IF;
302
303
304 END CREATE_ASSOCIATION;
305
306 -- ===============================================================
307 -- Start of Comments
308 -- Name
309 -- CREATE_AND_DELETE_ASSOCIATION
310 --
311 -- Purpose
312 -- 1. Establish new associations between CTD Id and Used-By,Used By Id combination
313 -- if not already exists in the CTD Association table.
314 --
315 -- 2. Delete the associations which are not more valid
316 --
317 Procedure CREATE_AND_DELETE_ASSOCIATION(
318 p_ctd_id_list JTF_NUMBER_TABLE,
319 p_used_by VARCHAR2,
320 p_used_by_val1 VARCHAR2
321 )
322 IS
323 CURSOR C_GET_NEW_ASSOCIATION
324 IS
325 SELECT CTD_LIST.CTD_ID
326 FROM (SELECT column_value ctd_id
327 FROM TABLE(CAST(p_ctd_id_list as JTF_NUMBER_TABLE)) ) ctd_list
328 WHERE CTD_LIST.CTD_ID not in
329 (SELECT ASSOC.CTD_ID
330 FROM AMS_CTD_ASSOCIATIONS assoc
331 WHERE assoc.used_by_type = p_used_by
332 AND assoc.used_by_val1 = p_used_by_val1
333 );
334
335
336 CURSOR C_REMOVED_ASSOCIATIONS
337 IS
338 SELECT CTD_ID
339 FROM AMS_CTD_ASSOCIATIONS
340 WHERE used_by_type = p_used_by
341 AND used_by_val1 = p_used_by_val1
342 AND CTD_ID NOT IN
343 (SELECT column_value ctd_id
344 FROM TABLE(CAST(p_ctd_id_list as JTF_NUMBER_TABLE))
345 );
346
347 l_new_ctd_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
348 l_removed_ctd_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
349
350
351 BEGIN
352 -- Create the associations which do not already exist
353 OPEN C_GET_NEW_ASSOCIATION;
354 FETCH C_GET_NEW_ASSOCIATION
355 BULK COLLECT INTO l_new_ctd_id_list;
356 CLOSE C_GET_NEW_ASSOCIATION;
357
358 CREATE_ASSOCIATION(
359 p_ctd_id_list => l_new_ctd_id_list,
360 p_used_by => p_used_by,
361 p_used_by_val1 => p_used_by_val1
362 );
363
364 -- Get the CTDs which are no more associated with Cover letter
365 -- but it's still available in the DB
366 OPEN C_REMOVED_ASSOCIATIONS;
367 FETCH C_REMOVED_ASSOCIATIONS
368 BULK COLLECT INTO l_removed_ctd_id_list;
369 CLOSE C_REMOVED_ASSOCIATIONS;
370
371 IF (l_removed_ctd_id_list.exists(1)) THEN
372 DELETE_ASSOCIATION_AND_CTD(l_removed_ctd_id_list
373 ,p_used_by
374 ,p_used_by_val1
375 );
376 END IF;
377
378
379 END CREATE_AND_DELETE_ASSOCIATION;
380
381 -- ===============================================================
382 -- Start of Comments
383 -- Name
384 -- DELETE_ASSOCIATION_AND_CTD
385 --
386 -- Purpose
387 -- This procedure deletes associations and all relevant CTD information
388 -- if no other object is using the CTD
389 --
390 Procedure DELETE_ASSOCIATION_AND_CTD(
391 p_used_by VARCHAR2,
392 p_used_by_val1 VARCHAR2
393 )
394 IS
395 l_associated_ctd_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
396
397 CURSOR C_GET_ASSOCIATED_CTDS
398 IS
399 SELECT CTD_ID
400 FROM AMS_CTD_ASSOCIATIONS
401 WHERE USED_BY_TYPE = p_used_by
402 AND p_used_by_val1 = p_used_by_val1;
403
404 BEGIN
405
406 OPEN C_GET_ASSOCIATED_CTDS;
407 FETCH C_GET_ASSOCIATED_CTDS
408 BULK COLLECT INTO l_associated_ctd_list;
409 CLOSE C_GET_ASSOCIATED_CTDS;
410
411 IF (l_associated_ctd_list.exists(1)) THEN
412 DELETE_ASSOCIATION_AND_CTD(l_associated_ctd_list,
413 p_used_by,
414 p_used_by_val1
415 );
416 END IF;
417
418 END DELETE_ASSOCIATION_AND_CTD;
419
420
421 -- Hint: Primary key needs to be returned.
422 PROCEDURE Create_Ctd(
423 p_api_version_number IN NUMBER,
424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
427
428 x_return_status OUT NOCOPY VARCHAR2,
429 x_msg_count OUT NOCOPY NUMBER,
430 x_msg_data OUT NOCOPY VARCHAR2,
431
432 p_ctd_rec IN ctd_rec_type := g_miss_ctd_rec,
433 x_ctd_id OUT NOCOPY NUMBER
434 )
435
436 IS
437 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Ctd';
438 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
439 l_return_status_full VARCHAR2(1);
440 l_object_version_number NUMBER := 1;
441 l_org_id NUMBER := FND_API.G_MISS_NUM;
442 l_CTD_ID NUMBER;
443 l_dummy NUMBER;
444
448
445 CURSOR c_id IS
446 SELECT AMS_CTDS_s.NEXTVAL
447 FROM dual;
449 CURSOR c_id_exists (l_id IN NUMBER) IS
450 SELECT 1
451 FROM AMS_CTDS
452 WHERE CTD_ID = l_id;
453
454 BEGIN
455 -- Standard Start of API savepoint
456 SAVEPOINT CREATE_Ctd_PVT;
457
458 -- Standard call to check for call compatibility.
459 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
460 p_api_version_number,
461 l_api_name,
462 G_PKG_NAME)
463 THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
465 END IF;
466
467 -- Initialize message list if p_init_msg_list is set to TRUE.
468 IF FND_API.to_Boolean( p_init_msg_list )
469 THEN
470 FND_MSG_PUB.initialize;
471 END IF;
472
473 -- Debug Message
474 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
475
476
477 -- Initialize API return status to SUCCESS
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479
480 -- Local variable initialization
481
482 IF p_ctd_rec.CTD_ID IS NULL OR p_ctd_rec.CTD_ID = FND_API.g_miss_num THEN
483 LOOP
484 l_dummy := NULL;
485 OPEN c_id;
486 FETCH c_id INTO l_CTD_ID;
487 CLOSE c_id;
488
489 OPEN c_id_exists(l_CTD_ID);
490 FETCH c_id_exists INTO l_dummy;
491 CLOSE c_id_exists;
492 EXIT WHEN l_dummy IS NULL;
493 END LOOP;
494 END IF;
495
496 -- =========================================================================
497 -- Validate Environment
498 -- =========================================================================
499
500 IF FND_GLOBAL.User_Id IS NULL
501 THEN
502 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
503 RAISE FND_API.G_EXC_ERROR;
504 END IF;
505
506 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
507 THEN
508 -- Debug message
509 AMS_UTILITY_PVT.debug_message('Private API: Validate_Ctd');
510
511 -- Invoke validation procedures
512 Validate_ctd(
513 p_api_version_number => 1.0,
514 p_init_msg_list => FND_API.G_FALSE,
515 p_validation_level => p_validation_level,
516 p_ctd_rec => p_ctd_rec,
517 x_return_status => x_return_status,
518 x_msg_count => x_msg_count,
519 x_msg_data => x_msg_data);
520 END IF;
521
522 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
523 RAISE FND_API.G_EXC_ERROR;
524 END IF;
525
526
527 -- Debug Message
528 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
529
530 -- Invoke table handler(AMS_CTDS_PKG.Insert_Row)
531 AMS_CTDS_PKG.Insert_Row(
532 px_ctd_id => l_ctd_id,
533 p_action_id => p_ctd_rec.action_id,
534 p_forward_url => p_ctd_rec.forward_url,
535 p_track_url => p_ctd_rec.track_url,
536 p_activity_product_id => p_ctd_rec.activity_product_id,
537 p_activity_offer_id => p_ctd_rec.activity_offer_id,
538 px_object_version_number => l_object_version_number,
539 p_last_update_date => SYSDATE,
540 p_last_updated_by => G_USER_ID,
541 p_creation_date => SYSDATE,
542 p_created_by => G_USER_ID,
543 p_last_update_login => G_LOGIN_ID,
544 p_security_group_id => p_ctd_rec.security_group_id);
545
546 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
547 RAISE FND_API.G_EXC_ERROR;
548 END IF;
549
550 x_ctd_id := l_ctd_id;
551 --
552 -- End of API body
553 --
554
555 -- Standard check for p_commit
556 IF FND_API.to_Boolean( p_commit )
557 THEN
558 COMMIT WORK;
559 END IF;
560
561
562 -- Debug Message
563 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
564
565 -- Standard call to get message count and if count is 1, get message info.
566 FND_MSG_PUB.Count_And_Get
567 (p_count => x_msg_count,
568 p_data => x_msg_data
569 );
570 EXCEPTION
571
572 WHEN AMS_Utility_PVT.resource_locked THEN
573 x_return_status := FND_API.g_ret_sts_error;
574 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
575
576 WHEN FND_API.G_EXC_ERROR THEN
577 ROLLBACK TO CREATE_Ctd_PVT;
578 x_return_status := FND_API.G_RET_STS_ERROR;
579 -- Standard call to get message count and if count=1, get the message
580 FND_MSG_PUB.Count_And_Get (
581 p_encoded => FND_API.G_FALSE,
582 p_count => x_msg_count,
583 p_data => x_msg_data
584 );
585
586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
587 ROLLBACK TO CREATE_Ctd_PVT;
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 -- Standard call to get message count and if count=1, get the message
590 FND_MSG_PUB.Count_And_Get (
591 p_encoded => FND_API.G_FALSE,
592 p_count => x_msg_count,
593 p_data => x_msg_data
594 );
595
596 WHEN OTHERS THEN
597 ROLLBACK TO CREATE_Ctd_PVT;
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 END IF;
599 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
600 THEN
601 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
603 -- Standard call to get message count and if count=1, get the message
604 FND_MSG_PUB.Count_And_Get (
605 p_encoded => FND_API.G_FALSE,
606 p_count => x_msg_count,
607 p_data => x_msg_data
608 );
609 End Create_Ctd;
610
611
612 PROCEDURE Update_Ctd(
613 p_api_version_number IN NUMBER,
614 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
615 p_commit IN VARCHAR2 := FND_API.G_FALSE,
616 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
617
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2,
621
622 p_ctd_rec IN ctd_rec_type,
623 x_object_version_number OUT NOCOPY NUMBER
624 )
625
626 IS
627 CURSOR c_get_ctd(ctd_id NUMBER) IS
628 SELECT *
629 FROM AMS_CTDS;
630 -- Hint: Developer need to provide Where clause
631 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Ctd';
632 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
633 -- Local Variables
634 l_object_version_number NUMBER;
635 l_CTD_ID NUMBER;
636 l_ref_ctd_rec c_get_Ctd%ROWTYPE ;
637 l_tar_ctd_rec AMS_Ctd_PVT.ctd_rec_type := P_ctd_rec;
638 l_rowid ROWID;
639
640 BEGIN
641 -- Standard Start of API savepoint
642 SAVEPOINT UPDATE_Ctd_PVT;
643
644 -- Standard call to check for call compatibility.
645 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
646 p_api_version_number,
647 l_api_name,
648 G_PKG_NAME)
649 THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653 -- Initialize message list if p_init_msg_list is set to TRUE.
654 IF FND_API.to_Boolean( p_init_msg_list )
655 THEN
656 FND_MSG_PUB.initialize;
657 END IF;
658
659 -- Debug Message
660 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
661
662
663 -- Initialize API return status to SUCCESS
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665
666 -- Debug Message
667 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
668
669 /*
670 OPEN c_get_Ctd( l_tar_ctd_rec.ctd_id);
671
672 FETCH c_get_Ctd INTO l_ref_ctd_rec ;
673
674 If ( c_get_Ctd%NOTFOUND) THEN
675 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
676 p_token_name => 'INFO',
677 p_token_value => 'Ctd') ;
678 RAISE FND_API.G_EXC_ERROR;
679 END IF;
680 -- Debug Message
681 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
682 CLOSE c_get_Ctd;
683 */
684
685
686 If (l_tar_ctd_rec.object_version_number is NULL or
687 l_tar_ctd_rec.object_version_number = FND_API.G_MISS_NUM ) Then
688 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
689 p_token_name => 'COLUMN',
690 p_token_value => 'Last_Update_Date') ;
691 raise FND_API.G_EXC_ERROR;
692 End if;
693 -- Check Whether record has been changed by someone else
694 If (l_tar_ctd_rec.object_version_number <> l_ref_ctd_rec.object_version_number) Then
695 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
696 p_token_name => 'INFO',
697 p_token_value => 'Ctd') ;
698 raise FND_API.G_EXC_ERROR;
699 End if;
700 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
701 THEN
702 -- Debug message
703 AMS_UTILITY_PVT.debug_message('Private API: Validate_Ctd');
704
705 -- Invoke validation procedures
706 Validate_ctd(
707 p_api_version_number => 1.0,
708 p_init_msg_list => FND_API.G_FALSE,
709 p_validation_level => p_validation_level,
710 p_ctd_rec => p_ctd_rec,
711 x_return_status => x_return_status,
712 x_msg_count => x_msg_count,
713 x_msg_data => x_msg_data);
714 END IF;
715
716 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
717 RAISE FND_API.G_EXC_ERROR;
718 END IF;
719
720
721 -- Debug Message
722 AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
723
724 -- Invoke table handler(AMS_CTDS_PKG.Update_Row)
725 AMS_CTDS_PKG.Update_Row(
726 p_ctd_id => p_ctd_rec.ctd_id,
727 p_action_id => p_ctd_rec.action_id,
728 p_forward_url => p_ctd_rec.forward_url,
729 p_track_url => p_ctd_rec.track_url,
730 p_activity_product_id => p_ctd_rec.activity_product_id,
731 p_activity_offer_id => p_ctd_rec.activity_offer_id,
732 p_object_version_number => p_ctd_rec.object_version_number,
733 p_last_update_date => SYSDATE,
734 p_last_updated_by => G_USER_ID,
735 p_creation_date => SYSDATE,
736 p_created_by => G_USER_ID,
737 p_last_update_login => G_LOGIN_ID,
738 p_security_group_id => p_ctd_rec.security_group_id);
739 --
740 -- End of API body.
741 --
742
743 -- Standard check for p_commit
747 END IF;
744 IF FND_API.to_Boolean( p_commit )
745 THEN
746 COMMIT WORK;
748
749
750 -- Debug Message
751 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
752
753 -- Standard call to get message count and if count is 1, get message info.
754 FND_MSG_PUB.Count_And_Get
755 (p_count => x_msg_count,
756 p_data => x_msg_data
757 );
758 EXCEPTION
759
760 WHEN AMS_Utility_PVT.resource_locked THEN
761 x_return_status := FND_API.g_ret_sts_error;
762 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
763
764 WHEN FND_API.G_EXC_ERROR THEN
765 ROLLBACK TO UPDATE_Ctd_PVT;
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 -- Standard call to get message count and if count=1, get the message
768 FND_MSG_PUB.Count_And_Get (
769 p_encoded => FND_API.G_FALSE,
770 p_count => x_msg_count,
771 p_data => x_msg_data
772 );
773
774 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
775 ROLLBACK TO UPDATE_Ctd_PVT;
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 -- Standard call to get message count and if count=1, get the message
778 FND_MSG_PUB.Count_And_Get (
779 p_encoded => FND_API.G_FALSE,
780 p_count => x_msg_count,
781 p_data => x_msg_data
782 );
783
784 WHEN OTHERS THEN
785 ROLLBACK TO UPDATE_Ctd_PVT;
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
788 THEN
789 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
790 END IF;
791 -- Standard call to get message count and if count=1, get the message
792 FND_MSG_PUB.Count_And_Get (
793 p_encoded => FND_API.G_FALSE,
794 p_count => x_msg_count,
795 p_data => x_msg_data
796 );
797 End Update_Ctd;
798
799
800 PROCEDURE Delete_Ctd(
801 p_api_version_number IN NUMBER,
802 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
803 p_commit IN VARCHAR2 := FND_API.G_FALSE,
804 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
805 x_return_status OUT NOCOPY VARCHAR2,
806 x_msg_count OUT NOCOPY NUMBER,
807 x_msg_data OUT NOCOPY VARCHAR2,
808 p_ctd_id IN NUMBER,
809 p_object_version_number IN NUMBER
810 )
811
812 IS
813 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Ctd';
814 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
815 l_object_version_number NUMBER;
816
817 BEGIN
818 -- Standard Start of API savepoint
819 SAVEPOINT DELETE_Ctd_PVT;
820
821 -- Standard call to check for call compatibility.
822 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
823 p_api_version_number,
824 l_api_name,
825 G_PKG_NAME)
826 THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 END IF;
829
830 -- Initialize message list if p_init_msg_list is set to TRUE.
831 IF FND_API.to_Boolean( p_init_msg_list )
832 THEN
833 FND_MSG_PUB.initialize;
834 END IF;
835
836 -- Debug Message
837 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
838
839
840 -- Initialize API return status to SUCCESS
841 x_return_status := FND_API.G_RET_STS_SUCCESS;
842
843 --
844 -- Api body
845 --
846 -- Debug Message
847 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
848
849 -- Invoke table handler(AMS_CTDS_PKG.Delete_Row)
850 AMS_CTDS_PKG.Delete_Row(
851 p_CTD_ID => p_CTD_ID);
852 --
853 -- End of API body
854 --
855
856 -- Standard check for p_commit
857 IF FND_API.to_Boolean( p_commit )
858 THEN
859 COMMIT WORK;
860 END IF;
861
862
863 -- Debug Message
864 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
865
866 -- Standard call to get message count and if count is 1, get message info.
867 FND_MSG_PUB.Count_And_Get
868 (p_count => x_msg_count,
869 p_data => x_msg_data
870 );
871 EXCEPTION
872
873 WHEN AMS_Utility_PVT.resource_locked THEN
874 x_return_status := FND_API.g_ret_sts_error;
875 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
876
877 WHEN FND_API.G_EXC_ERROR THEN
878 ROLLBACK TO DELETE_Ctd_PVT;
879 x_return_status := FND_API.G_RET_STS_ERROR;
880 -- Standard call to get message count and if count=1, get the message
881 FND_MSG_PUB.Count_And_Get (
882 p_encoded => FND_API.G_FALSE,
883 p_count => x_msg_count,
884 p_data => x_msg_data
885 );
886
887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
888 ROLLBACK TO DELETE_Ctd_PVT;
889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
890 -- Standard call to get message count and if count=1, get the message
891 FND_MSG_PUB.Count_And_Get (
892 p_encoded => FND_API.G_FALSE,
893 p_count => x_msg_count,
894 p_data => x_msg_data
895 );
896
900 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
897 WHEN OTHERS THEN
898 ROLLBACK TO DELETE_Ctd_PVT;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
901 THEN
902 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
903 END IF;
904 -- Standard call to get message count and if count=1, get the message
905 FND_MSG_PUB.Count_And_Get (
906 p_encoded => FND_API.G_FALSE,
907 p_count => x_msg_count,
908 p_data => x_msg_data
909 );
910 End Delete_Ctd;
911
912
913
914 -- Hint: Primary key needs to be returned.
915 PROCEDURE Lock_Ctd(
916 p_api_version_number IN NUMBER,
917 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
918
919 x_return_status OUT NOCOPY VARCHAR2,
920 x_msg_count OUT NOCOPY NUMBER,
921 x_msg_data OUT NOCOPY VARCHAR2,
922
923 p_ctd_id IN NUMBER,
924 p_object_version IN NUMBER
925 )
926
927 IS
928 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Ctd';
929 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
930 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
931 l_CTD_ID NUMBER;
932
933 CURSOR c_Ctd IS
934 SELECT CTD_ID
935 FROM AMS_CTDS
936 WHERE CTD_ID = p_CTD_ID
937 AND object_version_number = p_object_version
938 FOR UPDATE NOWAIT;
939
940 BEGIN
941
942 -- Debug Message
943 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
944
945 -- Initialize message list if p_init_msg_list is set to TRUE.
946 IF FND_API.to_Boolean( p_init_msg_list )
947 THEN
948 FND_MSG_PUB.initialize;
949 END IF;
950
951 -- Standard call to check for call compatibility.
952 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
953 p_api_version_number,
954 l_api_name,
955 G_PKG_NAME)
956 THEN
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END IF;
959
960
961 -- Initialize API return status to SUCCESS
962 x_return_status := FND_API.G_RET_STS_SUCCESS;
963
964
965 ------------------------ lock -------------------------
966
967 AMS_Utility_PVT.debug_message(l_full_name||': start');
968 OPEN c_Ctd;
969
970 FETCH c_Ctd INTO l_CTD_ID;
971
972 IF (c_Ctd%NOTFOUND) THEN
973 CLOSE c_Ctd;
974 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
975 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
976 FND_MSG_PUB.add;
977 END IF;
978 RAISE FND_API.g_exc_error;
979 END IF;
980
981 CLOSE c_Ctd;
982
983 -------------------- finish --------------------------
984 FND_MSG_PUB.count_and_get(
985 p_encoded => FND_API.g_false,
986 p_count => x_msg_count,
987 p_data => x_msg_data);
988 AMS_Utility_PVT.debug_message(l_full_name ||': end');
989 EXCEPTION
990
991 WHEN AMS_Utility_PVT.resource_locked THEN
992 x_return_status := FND_API.g_ret_sts_error;
993 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
994
995 WHEN FND_API.G_EXC_ERROR THEN
996 ROLLBACK TO LOCK_Ctd_PVT;
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 -- Standard call to get message count and if count=1, get the message
999 FND_MSG_PUB.Count_And_Get (
1000 p_encoded => FND_API.G_FALSE,
1001 p_count => x_msg_count,
1002 p_data => x_msg_data
1003 );
1004
1005 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1006 ROLLBACK TO LOCK_Ctd_PVT;
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 -- Standard call to get message count and if count=1, get the message
1009 FND_MSG_PUB.Count_And_Get (
1010 p_encoded => FND_API.G_FALSE,
1011 p_count => x_msg_count,
1012 p_data => x_msg_data
1013 );
1014
1015 WHEN OTHERS THEN
1016 ROLLBACK TO LOCK_Ctd_PVT;
1017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1018 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1019 THEN
1020 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1021 END IF;
1022 -- Standard call to get message count and if count=1, get the message
1023 FND_MSG_PUB.Count_And_Get (
1024 p_encoded => FND_API.G_FALSE,
1025 p_count => x_msg_count,
1026 p_data => x_msg_data
1027 );
1028 End Lock_Ctd;
1029
1030
1031 PROCEDURE check_ctd_uk_items(
1032 p_ctd_rec IN ctd_rec_type,
1033 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1034 x_return_status OUT NOCOPY VARCHAR2)
1035 IS
1036 l_valid_flag VARCHAR2(1);
1037
1038 BEGIN
1039 x_return_status := FND_API.g_ret_sts_success;
1040 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1041 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1042 'AMS_CTDS',
1043 'CTD_ID = ''' || p_ctd_rec.CTD_ID ||''''
1044 );
1045 ELSE
1046 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1047 'AMS_CTDS',
1048 'CTD_ID = ''' || p_ctd_rec.CTD_ID ||
1049 ''' AND CTD_ID <> ' || p_ctd_rec.CTD_ID
1050 );
1051 END IF;
1052
1053 IF l_valid_flag = FND_API.g_false THEN
1057 END IF;
1054 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CTD_ID_DUPLICATE');
1055 x_return_status := FND_API.g_ret_sts_error;
1056 RETURN;
1058
1059 END check_ctd_uk_items;
1060
1061 PROCEDURE check_ctd_req_items(
1062 p_ctd_rec IN ctd_rec_type,
1063 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1064 x_return_status OUT NOCOPY VARCHAR2
1065 )
1066 IS
1067 BEGIN
1068 x_return_status := FND_API.g_ret_sts_success;
1069
1070 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1071
1072
1073 IF p_ctd_rec.ctd_id = FND_API.g_miss_num OR p_ctd_rec.ctd_id IS NULL THEN
1074 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_ctd_id');
1075 x_return_status := FND_API.g_ret_sts_error;
1076 RETURN;
1077 END IF;
1078
1079
1080 IF p_ctd_rec.action_id = FND_API.g_miss_num OR p_ctd_rec.action_id IS NULL THEN
1081 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_action_id');
1082 x_return_status := FND_API.g_ret_sts_error;
1083 RETURN;
1084 END IF;
1085
1086
1087 IF p_ctd_rec.object_version_number = FND_API.g_miss_num OR p_ctd_rec.object_version_number IS NULL THEN
1088 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_object_version_number');
1089 x_return_status := FND_API.g_ret_sts_error;
1090 RETURN;
1091 END IF;
1092
1093
1094 IF p_ctd_rec.last_update_date = FND_API.g_miss_date OR p_ctd_rec.last_update_date IS NULL THEN
1095 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_last_update_date');
1096 x_return_status := FND_API.g_ret_sts_error;
1097 RETURN;
1098 END IF;
1099
1100
1101 IF p_ctd_rec.last_updated_by = FND_API.g_miss_num OR p_ctd_rec.last_updated_by IS NULL THEN
1102 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_last_updated_by');
1103 x_return_status := FND_API.g_ret_sts_error;
1104 RETURN;
1105 END IF;
1106
1107
1108 IF p_ctd_rec.creation_date = FND_API.g_miss_date OR p_ctd_rec.creation_date IS NULL THEN
1109 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_creation_date');
1110 x_return_status := FND_API.g_ret_sts_error;
1111 RETURN;
1112 END IF;
1113
1114
1115 IF p_ctd_rec.created_by = FND_API.g_miss_num OR p_ctd_rec.created_by IS NULL THEN
1116 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_created_by');
1117 x_return_status := FND_API.g_ret_sts_error;
1118 RETURN;
1119 END IF;
1120 ELSE
1121
1122
1123 IF p_ctd_rec.ctd_id IS NULL THEN
1124 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_ctd_id');
1125 x_return_status := FND_API.g_ret_sts_error;
1126 RETURN;
1127 END IF;
1128
1129
1130 IF p_ctd_rec.action_id IS NULL THEN
1131 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_action_id');
1132 x_return_status := FND_API.g_ret_sts_error;
1133 RETURN;
1134 END IF;
1135
1136
1137 IF p_ctd_rec.object_version_number IS NULL THEN
1138 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_object_version_number');
1139 x_return_status := FND_API.g_ret_sts_error;
1140 RETURN;
1141 END IF;
1142
1143
1144 IF p_ctd_rec.last_update_date IS NULL THEN
1145 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_last_update_date');
1146 x_return_status := FND_API.g_ret_sts_error;
1147 RETURN;
1148 END IF;
1149
1150
1151 IF p_ctd_rec.last_updated_by IS NULL THEN
1152 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_last_updated_by');
1153 x_return_status := FND_API.g_ret_sts_error;
1154 RETURN;
1155 END IF;
1156
1157
1158 IF p_ctd_rec.creation_date IS NULL THEN
1159 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_creation_date');
1160 x_return_status := FND_API.g_ret_sts_error;
1161 RETURN;
1162 END IF;
1163
1164
1165 IF p_ctd_rec.created_by IS NULL THEN
1166 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ctd_NO_created_by');
1167 x_return_status := FND_API.g_ret_sts_error;
1168 RETURN;
1169 END IF;
1170 END IF;
1171
1172 END check_ctd_req_items;
1173
1174 PROCEDURE check_ctd_FK_items(
1175 p_ctd_rec IN ctd_rec_type,
1176 x_return_status OUT NOCOPY VARCHAR2
1177 )
1178 IS
1179 BEGIN
1180 x_return_status := FND_API.g_ret_sts_success;
1181
1182 -- Enter custom code here
1183
1184 END check_ctd_FK_items;
1185
1186 PROCEDURE check_ctd_Lookup_items(
1187 p_ctd_rec IN ctd_rec_type,
1188 x_return_status OUT NOCOPY VARCHAR2
1189 )
1190 IS
1191 BEGIN
1192 x_return_status := FND_API.g_ret_sts_success;
1193
1194 -- Enter custom code here
1195
1196 END check_ctd_Lookup_items;
1197
1198 PROCEDURE Check_ctd_Items (
1199 P_ctd_rec IN ctd_rec_type,
1200 p_validation_mode IN VARCHAR2,
1201 x_return_status OUT NOCOPY VARCHAR2
1202 )
1203 IS
1204 BEGIN
1205
1206 -- Check Items Uniqueness API calls
1207
1208 check_ctd_uk_items(
1209 p_ctd_rec => p_ctd_rec,
1210 p_validation_mode => p_validation_mode,
1211 x_return_status => x_return_status);
1212 IF x_return_status <> FND_API.g_ret_sts_success THEN
1213 RETURN;
1214 END IF;
1215
1216 -- Check Items Required/NOT NULL API calls
1217
1218 check_ctd_req_items(
1219 p_ctd_rec => p_ctd_rec,
1223 RETURN;
1220 p_validation_mode => p_validation_mode,
1221 x_return_status => x_return_status);
1222 IF x_return_status <> FND_API.g_ret_sts_success THEN
1224 END IF;
1225 -- Check Items Foreign Keys API calls
1226
1227 check_ctd_FK_items(
1228 p_ctd_rec => p_ctd_rec,
1229 x_return_status => x_return_status);
1230 IF x_return_status <> FND_API.g_ret_sts_success THEN
1231 RETURN;
1232 END IF;
1233 -- Check Items Lookups
1234
1235 check_ctd_Lookup_items(
1236 p_ctd_rec => p_ctd_rec,
1237 x_return_status => x_return_status);
1238 IF x_return_status <> FND_API.g_ret_sts_success THEN
1239 RETURN;
1240 END IF;
1241
1242 END Check_ctd_Items;
1243
1244
1245 PROCEDURE Complete_ctd_Rec (
1246 p_ctd_rec IN ctd_rec_type,
1247 x_complete_rec OUT NOCOPY ctd_rec_type)
1248 IS
1249 l_return_status VARCHAR2(1);
1250
1251 CURSOR c_complete IS
1252 SELECT *
1253 FROM ams_ctds
1254 WHERE ctd_id = p_ctd_rec.ctd_id;
1255 l_ctd_rec c_complete%ROWTYPE;
1256 BEGIN
1257 x_complete_rec := p_ctd_rec;
1258
1259
1260 OPEN c_complete;
1261 FETCH c_complete INTO l_ctd_rec;
1262 CLOSE c_complete;
1263
1264 -- ctd_id
1265 IF p_ctd_rec.ctd_id = FND_API.g_miss_num THEN
1266 x_complete_rec.ctd_id := l_ctd_rec.ctd_id;
1267 END IF;
1268
1269 -- action_id
1270 IF p_ctd_rec.action_id = FND_API.g_miss_num THEN
1271 x_complete_rec.action_id := l_ctd_rec.action_id;
1272 END IF;
1273
1274 -- forward_url
1275 IF p_ctd_rec.forward_url = FND_API.g_miss_char THEN
1276 x_complete_rec.forward_url := l_ctd_rec.forward_url;
1277 END IF;
1278
1279 -- track_url
1280 IF p_ctd_rec.track_url = FND_API.g_miss_char THEN
1281 x_complete_rec.track_url := l_ctd_rec.track_url;
1282 END IF;
1283
1284 -- activity_product_id
1285 IF p_ctd_rec.activity_product_id = FND_API.g_miss_num THEN
1286 x_complete_rec.activity_product_id := l_ctd_rec.activity_product_id;
1287 END IF;
1288
1289 -- activity_offer_id
1290 IF p_ctd_rec.activity_offer_id = FND_API.g_miss_num THEN
1294 -- object_version_number
1291 x_complete_rec.activity_offer_id := l_ctd_rec.activity_offer_id;
1292 END IF;
1293
1295 IF p_ctd_rec.object_version_number = FND_API.g_miss_num THEN
1296 x_complete_rec.object_version_number := l_ctd_rec.object_version_number;
1297 END IF;
1298
1299 -- last_update_date
1300 IF p_ctd_rec.last_update_date = FND_API.g_miss_date THEN
1301 x_complete_rec.last_update_date := l_ctd_rec.last_update_date;
1302 END IF;
1303
1304 -- last_updated_by
1305 IF p_ctd_rec.last_updated_by = FND_API.g_miss_num THEN
1306 x_complete_rec.last_updated_by := l_ctd_rec.last_updated_by;
1307 END IF;
1308
1309 -- creation_date
1310 IF p_ctd_rec.creation_date = FND_API.g_miss_date THEN
1311 x_complete_rec.creation_date := l_ctd_rec.creation_date;
1312 END IF;
1313
1314 -- created_by
1315 IF p_ctd_rec.created_by = FND_API.g_miss_num THEN
1316 x_complete_rec.created_by := l_ctd_rec.created_by;
1317 END IF;
1318
1319 -- last_update_login
1320 IF p_ctd_rec.last_update_login = FND_API.g_miss_num THEN
1321 x_complete_rec.last_update_login := l_ctd_rec.last_update_login;
1322 END IF;
1323
1324 -- security_group_id
1325 IF p_ctd_rec.security_group_id = FND_API.g_miss_num THEN
1326 x_complete_rec.security_group_id := l_ctd_rec.security_group_id;
1327 END IF;
1328 -- Note: Developers need to modify the procedure
1329 -- to handle any business specific requirements.
1330 END Complete_ctd_Rec;
1331 PROCEDURE Validate_ctd(
1332 p_api_version_number IN NUMBER,
1333 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1335 p_ctd_rec IN ctd_rec_type,
1336 x_return_status OUT NOCOPY VARCHAR2,
1337 x_msg_count OUT NOCOPY NUMBER,
1338 x_msg_data OUT NOCOPY VARCHAR2
1339 )
1340 IS
1341 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Ctd';
1342 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1343 l_object_version_number NUMBER;
1344 l_ctd_rec AMS_Ctd_PVT.ctd_rec_type;
1345
1346 BEGIN
1347 -- Standard Start of API savepoint
1348 SAVEPOINT VALIDATE_Ctd_;
1349
1350 -- Standard call to check for call compatibility.
1351 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1355 THEN
1352 p_api_version_number,
1353 l_api_name,
1354 G_PKG_NAME)
1356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1357 END IF;
1358
1359 -- Initialize message list if p_init_msg_list is set to TRUE.
1360 IF FND_API.to_Boolean( p_init_msg_list )
1361 THEN
1362 FND_MSG_PUB.initialize;
1363 END IF;
1364 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1365 Check_ctd_Items(
1366 p_ctd_rec => p_ctd_rec,
1367 p_validation_mode => JTF_PLSQL_API.g_update,
1368 x_return_status => x_return_status
1369 );
1370
1371 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1372 RAISE FND_API.G_EXC_ERROR;
1373 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1375 END IF;
1376 END IF;
1377
1378 Complete_ctd_Rec(
1379 p_ctd_rec => p_ctd_rec,
1380 x_complete_rec => l_ctd_rec
1381 );
1382
1383 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1384 Validate_ctd_Rec(
1385 p_api_version_number => 1.0,
1386 p_init_msg_list => FND_API.G_FALSE,
1387 x_return_status => x_return_status,
1388 x_msg_count => x_msg_count,
1389 x_msg_data => x_msg_data,
1390 p_ctd_rec => l_ctd_rec);
1391
1392 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1393 RAISE FND_API.G_EXC_ERROR;
1394 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 END IF;
1397 END IF;
1398
1399
1400 -- Debug Message
1401 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1402
1403
1404 -- Initialize API return status to SUCCESS
1405 x_return_status := FND_API.G_RET_STS_SUCCESS;
1406
1407
1408 -- Debug Message
1409 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1410
1411 -- Standard call to get message count and if count is 1, get message info.
1412 FND_MSG_PUB.Count_And_Get
1413 (p_count => x_msg_count,
1414 p_data => x_msg_data
1415 );
1416 EXCEPTION
1417
1418 WHEN AMS_Utility_PVT.resource_locked THEN
1419 x_return_status := FND_API.g_ret_sts_error;
1420 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1421
1422 WHEN FND_API.G_EXC_ERROR THEN
1423 ROLLBACK TO VALIDATE_Ctd_;
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 -- Standard call to get message count and if count=1, get the message
1426 FND_MSG_PUB.Count_And_Get (
1427 p_encoded => FND_API.G_FALSE,
1428 p_count => x_msg_count,
1429 p_data => x_msg_data
1430 );
1431
1432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1433 ROLLBACK TO VALIDATE_Ctd_;
1434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1435 -- Standard call to get message count and if count=1, get the message
1436 FND_MSG_PUB.Count_And_Get (
1437 p_encoded => FND_API.G_FALSE,
1438 p_count => x_msg_count,
1439 p_data => x_msg_data
1440 );
1441
1442 WHEN OTHERS THEN
1443 ROLLBACK TO VALIDATE_Ctd_;
1444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1445 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1446 THEN
1447 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1448 END IF;
1449 -- Standard call to get message count and if count=1, get the message
1450 FND_MSG_PUB.Count_And_Get (
1451 p_encoded => FND_API.G_FALSE,
1452 p_count => x_msg_count,
1453 p_data => x_msg_data
1454 );
1455 End Validate_Ctd;
1456
1457
1458 PROCEDURE Validate_ctd_rec(
1459 p_api_version_number IN NUMBER,
1460 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1461 x_return_status OUT NOCOPY VARCHAR2,
1462 x_msg_count OUT NOCOPY NUMBER,
1463 x_msg_data OUT NOCOPY VARCHAR2,
1464 p_ctd_rec IN ctd_rec_type
1465 )
1466 IS
1467 BEGIN
1468 -- Initialize message list if p_init_msg_list is set to TRUE.
1469 IF FND_API.to_Boolean( p_init_msg_list )
1470 THEN
1471 FND_MSG_PUB.initialize;
1472 END IF;
1473
1474 -- Initialize API return status to SUCCESS
1475 x_return_status := FND_API.G_RET_STS_SUCCESS;
1476
1477 -- Hint: Validate data
1478 -- If data not valid
1479 -- THEN
1480 -- x_return_status := FND_API.G_RET_STS_ERROR;
1481
1482 -- Debug Message
1483 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1484 -- Standard call to get message count and if count is 1, get message info.
1485 FND_MSG_PUB.Count_And_Get
1486 (p_count => x_msg_count,
1487 p_data => x_msg_data
1488 );
1489 END Validate_ctd_Rec;
1490
1491 --========================================================================
1492 -- PROCEDURE
1493 -- CHECK_MANDATORY_FIELDS
1494 --
1495 -- PURPOSE
1496 -- This api is created to be used for validating ctd mandatory fields during
1497 -- schedule status changes. Check ams_ctd_assoc_v
1498 --
1499 -- HISTORY
1503 PROCEDURE CHECK_MANDATORY_FIELDS(
1500 -- 30-Aug-2006 dbiswas Created.
1501 --========================================================================
1502
1504 P_ctd_rec IN ctd_rec_type,
1505 x_return_status OUT NOCOPY VARCHAR2
1506 )
1507 IS
1508
1509 BEGIN
1510 x_return_status := FND_API.g_ret_sts_success;
1511
1512 IF P_ctd_rec.ctd_id = FND_API.g_miss_num OR P_ctd_rec.ctd_id IS NULL THEN
1513 --AMS_Utility_PVT.Error_Message('AMS_ctd_NO_ctd_id');
1514 x_return_status := FND_API.g_ret_sts_error;
1515 FND_MESSAGE.set_name('AMS', 'AMS_ctd_NO_ctd_id');
1516 FND_MSG_PUB.add;
1517 RETURN;
1518 END IF;
1519
1520 IF P_ctd_rec.track_url IS NOT NULL THEN
1521 IF P_ctd_rec.forward_url IS NULL AND P_ctd_rec.action_id =1 THEN
1522 --AMS_Utility_PVT.Error_Message('AMS_ctd_NO_FORWARD_URL');
1523 x_return_status := FND_API.g_ret_sts_error;
1524 FND_MESSAGE.set_name('AMS', 'AMS_PU_REQ_FIELDS_NOT_MAPPED');
1525 FND_MSG_PUB.add;
1526 RETURN;
1527 ELSIF P_ctd_rec.action_id = 5 THEN -- Go to Section. Site and Section should be provided
1528 IF P_ctd_rec.forward_url like '%go=section%' AND (P_ctd_rec.forward_url not like '%minisite=%' OR P_ctd_rec.forward_url not like '%section=%') THEN
1529 x_return_status := FND_API.g_ret_sts_error;
1530 FND_MESSAGE.set_name('AMS', 'AMS_PU_REQ_FIELDS_NOT_MAPPED');
1531 FND_MSG_PUB.add;
1532 RETURN;
1533 END IF;
1534 RETURN;
1535
1536 ELSIF P_ctd_rec.action_id = 7 THEN -- Goto Site. Site should be provided
1537 IF P_ctd_rec.forward_url like '%go=catalog%' AND P_ctd_rec.forward_url not like '%minisite=%' THEN
1538 x_return_status := FND_API.g_ret_sts_error;
1539 FND_MESSAGE.set_name('AMS', 'AMS_PU_REQ_FIELDS_NOT_MAPPED');
1540 FND_MSG_PUB.add;
1541 RETURN;
1542 END IF;
1543 ELSIF P_ctd_rec.action_id = 8 AND P_ctd_rec.forward_url like '%TO_BE_COMPUTED%' THEN -- Goto Web Script. Script should be provided
1544 x_return_status := FND_API.g_ret_sts_error;
1545 FND_MESSAGE.set_name('AMS', 'AMS_PU_REQ_FIELDS_NOT_MAPPED');
1546 FND_MSG_PUB.add;
1547 RETURN;
1548 ELSIF P_ctd_rec.action_id = 9 THEN -- Goto Content item. Item and stylesheet should be provided
1549 IF P_ctd_rec.forward_url like '%cItemId=&%' OR P_ctd_rec.forward_url like '%stlId=&%' THEN
1550 x_return_status := FND_API.g_ret_sts_error;
1551 FND_MESSAGE.set_name('AMS', 'AMS_PU_REQ_FIELDS_NOT_MAPPED');
1552 FND_MSG_PUB.add;
1553 RETURN;
1554 END IF;
1555 END IF;
1556
1557 ELSIF P_ctd_rec.action_id IS NOT NULL AND P_ctd_rec.track_url IS NULL THEN
1558 x_return_status := FND_API.g_ret_sts_error;
1559 FND_MESSAGE.set_name('AMS', 'AMS_CTD_NO_TRACK_GEN');
1560 FND_MSG_PUB.add;
1561 RETURN;
1562 END IF;
1563
1564 END CHECK_MANDATORY_FIELDS;
1565
1566 END AMS_CTD_PVT;