[Home] [Help]
PACKAGE BODY: APPS.IEC_ADMIN_PVT
Source
1 PACKAGE BODY IEC_ADMIN_PVT as
2 /* $Header: IECADMB.pls 120.1 2005/06/16 09:10:41 appldev $ */
3
4
5
6 -- ===============================================================
7
8 -- Start of Comments
9
10 -- Package name
11
12 -- IEC_ADMIN_PVT
13
14 -- Purpose
15
16 -- Private api created for use by the Admin utility
17
18 -- History
19
20 -- 30-Apr-2001 msista Created.
21
22 -- 21-May-2001 msista Modified to work better.
23
24 -- 09-Jul-2001 gpagadal Removed Dialing Method.
25
26 -- 20-Jul-2001 gpagadal fnd_msg_pub.delete_msg() added.
27
28 -- 02-Aug-2001 gpagadal Fields added.
29
30 --
31
32 -- NOTE
33
34 --
35
36 -- End of Comments
37
38 -- ===============================================================
39
40
41
42
43
44 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEC_ADMIN_PVT';
45
46 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iecadmb.pls';
47
48
49
50 -- don't know if need these
51
52 --G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
53
54 --G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
55
56
57
58 --===================================================================
59
60 -- NAME
61
62 -- Update_Camp_Schedule
63
64 --
65
66 -- PURPOSE
67
68 -- Private api to Update Campaign schedules.
69
70 --
71
72 -- NOTES
73
74 -- 1. AO Admin will use this procedure to update Campaign Schedule
75
76 -- parameters.
77
78 --
79
80 -- HISTORY
81
82 -- 15-May-2001 MSISTA Created
83
84 -- 09-Jul-2001 GPAGADAL Modified
85
86 --===================================================================
87
88 PROCEDURE Update_Camp_Schedule(
89
90
91
92 p_campaign_schedule_id IN NUMBER,
93
94 -- dialing_method used for CTI disabled
95
96 p_dialing_method IN VARCHAR2,
97
98 p_calendar_id IN NUMBER,
99
100 p_abandon_limit IN NUMBER,
101
102
103
104 -- predictive timeout
105
106 p_predictive_timeout IN NUMBER,
107
108 p_user_status_id IN NUMBER,
109
110
111
112 p_user_id IN NUMBER,
113
114
115
116 x_msg_data OUT NOCOPY VARCHAR2,
117
118 x_return_value OUT NOCOPY NUMBER
119
120
121
122 )
123
124 IS
125
126
127
128 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Camp_Schedule';
129
130 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
131
132
133
134 l_camp_schedule_rec AMS_Camp_Schedule_PUB.schedule_rec_type;
135
136
137
138 l_msg_count NUMBER;
139
140 l_msg_data VARCHAR2(240);
141
142 l_one_msg VARCHAR2(100);
143
144 l_object_version_number NUMBER;
145
146
147
148 x_return_status VARCHAR2(10);
149
150
151
152 BEGIN
153
154
155
156 fnd_msg_pub.delete_msg();
157
158
159
160 -- Standard Start of API savepoint
161
162 SAVEPOINT UPDATE_camp_schedule;
163
164
165
166 -- Initialize outgoing parameters
167
168 x_msg_data := '';
169
170 x_return_value := 1;
171
172
173
174 -- Initialize API return status to SUCCESS
175
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178
179
180 l_camp_schedule_rec.schedule_id := p_campaign_schedule_id;
181
182 -- CTI disabled
183
184 IF NOT (p_dialing_method IS NULL) THEN
185
186 l_camp_schedule_rec.activity_attribute4 := p_dialing_method;
187
188 END IF;
189
190
191
192 IF NOT (p_calendar_id IS NULL) THEN
193
194 l_camp_schedule_rec.activity_attribute1 := p_calendar_id;
195
196 END IF;
197
198
199
200 IF NOT (p_abandon_limit IS NULL) THEN
201
202 l_camp_schedule_rec.activity_attribute5 := p_abandon_limit;
203
204 END IF;
205
206
207
208 IF NOT (p_predictive_timeout IS NULL) THEN
209
210 l_camp_schedule_rec.activity_attribute8 := p_predictive_timeout;
211
212 END IF;
213
214
215
216 IF NOT (p_user_status_id IS NULL ) THEN
217
218 IF l_camp_schedule_rec.user_status_id <> p_user_status_id THEN
219
220 l_camp_schedule_rec.user_status_id := p_user_status_id;
221
222 l_camp_schedule_rec.status_date := sysdate;
223
224 END IF;
225
226 END IF;
227
228
229
230 l_camp_schedule_rec.last_update_date := sysdate;
231
232 l_camp_schedule_rec.last_updated_by := p_user_id;
233
234
235
236 -- fetch the object_version_number from db
237
238 SELECT
239
240 OBJECT_VERSION_NUMBER, SOURCE_CODE into
241
242 l_camp_schedule_rec.object_version_number, l_camp_schedule_rec.source_code
243
244 from AMS_CAMPAIGN_SCHEDULES_B
245
246 WHERE SCHEDULE_ID = p_campaign_schedule_id;
247
248
249
250 AMS_Camp_Schedule_PUB.Update_Camp_Schedule( L_API_VERSION_NUMBER,
251
252 FND_API.G_FALSE,
253
254 FND_API.G_TRUE,
255
256 FND_API.G_VALID_LEVEL_FULL,
257
258 x_return_status,
259
260 l_msg_count,
261
262 l_msg_data,
263
264 l_camp_schedule_rec,
265
266 l_object_version_number
267
268 );
269
270
271
272 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
273
274 UPDATE AMS_CAMPAIGN_SCHEDULES_B
275
276 SET
277
278 ACTIVITY_ATTRIBUTE6 = 'Y'
279
280 WHERE
281
282 SCHEDULE_ID = p_campaign_schedule_id;
283
284 -- msista 11/29
285
286 COMMIT;
287
288 ELSE
289
290 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
291
292 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
293
294 x_msg_data := x_msg_data || ',' || l_msg_data;
295
296 END LOOP;
297
298 x_return_value := 0;
299
300 END IF;
301
302
303
304 EXCEPTION
305
306 WHEN OTHERS THEN
307
308 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
309
310 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
311
312 x_msg_data := x_msg_data || ',' || l_msg_data;
313
314 END LOOP;
315
316 x_msg_data := x_msg_data || ',' || 'IEC_ERR_UPD_CAMP_FAIL';
317
318 x_return_value := 0;
319
320
321
322 END Update_Camp_Schedule;
323
324
325
326
327
328
329
330
331
332
333
334 --===================================================================
335
336 -- NAME
337
338 -- Update_List_DialingMethod
339
340 --
341
342 -- PURPOSE
343
344 -- Private api to Update List headers.
345
346 --
347
348 -- NOTES
349
350 -- 1. AO Admin will use this procedure to update List Header
351
352 -- parameters.
353
354 --
355
356 -- HISTORY
357
358 -- 03-Aug-2001 GPAGADAL Created
359
360
361
362 --===================================================================
363
364 PROCEDURE Update_List_DialingMethod(
365
366
367
368 p_campaign_schedule_id IN NUMBER,
369
370 -- dialing_method used for CTI disabled
371
372 p_dialing_method IN VARCHAR2,
373
374
375
376 p_user_id IN NUMBER,
377
378
379
380 x_msg_data OUT NOCOPY VARCHAR2,
381
382 x_return_value OUT NOCOPY NUMBER
383
384
385
386 )
387
388 IS
389
390
391
392 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_List_DialingMethod';
393
394 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
395
396
397
398
399
400 l_list_header_rec AMS_LISTHEADER_PVT.list_header_rec_type;
401
402
403
404 l_msg_count NUMBER;
405
406 l_msg_data VARCHAR2(240);
407
408 l_one_msg VARCHAR2(100);
409
410 l_object_version_number NUMBER;
411
412
413
414 x_return_status VARCHAR2(10);
415
416
417
418 CURSOR l_list_headers(campaign_schedule_id NUMBER) IS
419
420 SELECT LIST_HEADER_ID FROM AMS_ACT_LISTS WHERE LIST_USED_BY_ID = campaign_schedule_id;
421
422
423
424 BEGIN
425
426
427
428 fnd_msg_pub.delete_msg();
429
430
431
432 -- Standard Start of API savepoint
433
434 SAVEPOINT UPDATE_list_header;
435
436
437
438 -- Initialize outgoing parameters
439
440 x_msg_data := '';
441
442 x_return_value := 1;
443
444
445
446 -- Initialize API return status to SUCCESS
447
448 x_return_status := FND_API.G_RET_STS_SUCCESS;
449
450
451
452 IF p_dialing_method = 'MAN' THEN
453
454
455
456 FOR l_list_header_id_rec IN l_list_headers(p_campaign_schedule_id) LOOP
457
458
459
460 -- Initialize list header rec
461
462 AMS_ListHeader_PVT.Init_ListHeader_Rec(l_list_header_rec);
463
464
465
466 l_list_header_rec.list_header_id := l_list_header_id_rec.LIST_HEADER_ID;
467
468
469
470 l_list_header_rec.dialing_method := 'MAN';
471
472 l_list_header_rec.last_update_date := sysdate;
473
474 l_list_header_rec.last_updated_by := p_user_id;
475
476
477
478 -- fetch the object_version_number from db
479
480 SELECT
481
482 OBJECT_VERSION_NUMBER into l_list_header_rec.object_version_number
483
484 FROM AMS_LIST_HEADERS_ALL
485
486 WHERE LIST_HEADER_ID = l_list_header_rec.list_header_id;
487
488
489
490 AMS_ListHeader_PUB.Update_ListHeader( l_api_version_number,
491
492 FND_API.G_FALSE,
493
494 FND_API.G_TRUE,
495
496 FND_API.G_VALID_LEVEL_FULL,
497
498 x_return_status,
499
500 l_msg_count,
501
502 l_msg_data,
503
504 l_list_header_rec
505
506 );
507
508
509
510 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
511
512 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
513
514 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
515
516 x_msg_data := x_msg_data || ',' || l_msg_data;
517
518 END LOOP;
519
520 x_return_value := 0;
521
522 END IF;
523
524
525
526 END LOOP;
527
528
529
530 END IF;
531
532
533
534 EXCEPTION
535
536 WHEN OTHERS THEN
537
538 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
539
540 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
541
542 x_msg_data := x_msg_data || ',' || l_msg_data;
543
544 END LOOP;
545
546 x_msg_data := x_msg_data || ',' || 'IEC_ERR_UPD_CAMP_FAIL';
547
548 x_return_value := 0;
549
550
551
552 END Update_List_DialingMethod;
553
554
555
559
556
557
558 --===================================================================
560 -- NAME
561
562 -- Update_List_Header
563
564 --
565
566 -- PURPOSE
567
568 -- Private api to Update List headers.
569
570 --
571
572 -- NOTES
573
574 -- 1. AO Admin will use this procedure to update List Header
575
576 -- parameters.
577
578 --
579
580 -- HISTORY
581
582 -- 30-Apr-2001 MSISTA Created
583
584 --===================================================================
585
586 PROCEDURE Update_List_Header(
587
588 p_list_header_id IN NUMBER,
589
590 p_dialing_method IN VARCHAR2,
591
592 p_list_priority IN NUMBER,
593
594 p_recycling_alg_id IN NUMBER,
595
596 p_release_control_alg_id IN NUMBER,
597
598 p_calendar_id IN NUMBER,
599
600 p_release_strategy IN VARCHAR2,
601
602 p_quantum IN NUMBER,
603
604 p_quota IN NUMBER := null,
605
606 p_quota_reset IN NUMBER := null,
607
608
609
610 p_user_id IN NUMBER,
611
612
613
614 x_msg_data OUT NOCOPY VARCHAR2,
615
616 x_return_value OUT NOCOPY NUMBER
617
618 )
619
620
621
622 IS
623
624
625
626 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_List_Header';
627
628 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
629
630
631
632 l_list_header_rec AMS_LISTHEADER_PVT.list_header_rec_type;
633
634
635
636 l_msg_count NUMBER;
637
638 l_msg_data VARCHAR2(240);
639
640 l_one_msg VARCHAR2(100);
641
642 l_object_version_number NUMBER;
643
644
645
646 x_return_status VARCHAR2(10);
647
648
649
650 BEGIN
651
652
653
654 fnd_msg_pub.delete_msg();
655
656
657
658
659
660 -- Standard Start of API savepoint
661
662 SAVEPOINT UPDATE_list_header;
663
664
665
666 -- Initialize outgoing parameter values
667
668 x_msg_data := '';
669
670 x_return_value := 1;
671
672
673
674 -- Initialize API return status to SUCCESS
675
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677
678
679
680 -- Initialize list header rec
681
682 AMS_ListHeader_PVT.Init_ListHeader_Rec(l_list_header_rec);
683
684
685
686 l_list_header_rec.list_header_id := p_list_header_id;
687
688 l_list_header_rec.dialing_method := p_dialing_method;
689
690 l_list_header_rec.list_priority := p_list_priority;
691
692 l_list_header_rec.release_control_alg_id := p_release_control_alg_id;
693
694 l_list_header_rec.release_strategy := p_release_strategy;
695
696 l_list_header_rec.quantum := p_quantum;
697
698 l_list_header_rec.calling_calendar_id := p_calendar_id;
699
700 --l_list_header_rec.recycling_alg_id := p_recycling_alg_id;
701
702 --l_list_header_rec.quota := p_quota;
703
704 --l_list_header_rec.quota_reset := p_quota_reset;
705
706
707
708 l_list_header_rec.last_update_date := sysdate;
709
710 l_list_header_rec.last_updated_by := p_user_id;
711
712
713
714 AMS_ListHeader_PVT.Update_ListHeader( l_api_version_number,
715
716 FND_API.G_FALSE,
717
718 FND_API.G_TRUE,
719
720 FND_API.G_VALID_LEVEL_FULL,
721
722 x_return_status,
723
724 l_msg_count,
725
726 l_msg_data,
727
728 l_list_header_rec
729
730 );
731
732
733
734 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
735
736 UPDATE AMS_LIST_HEADERS_ALL
737
738 SET
739
740 RECYCLING_ALG_ID = p_recycling_alg_id,
741
742 QUOTA = p_quota,
743
744 QUOTA_RESET = p_quota_reset,
745
746 CALL_CENTER_READY_FLAG = 'Y'
747
748 WHERE
749
750 LIST_HEADER_ID = p_list_header_id;
751
752 -- msista 11/29
753
754 COMMIT;
755
756 ELSE
757
758 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
759
760 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
761
765
762 x_msg_data := x_msg_data || ',' || l_msg_data;
763
764 END LOOP;
766 x_return_value := 0;
767
768 END IF;
769
770
771
772 EXCEPTION
773
774
775
776 WHEN OTHERS THEN
777
778 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
779
780 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
781
782 x_msg_data := x_msg_data || ',' || l_msg_data;
783
784 END LOOP;
785
786 x_msg_data := x_msg_data || ',' || 'IEC_ERR_UPD_LIST_FAIL';
787
788 x_return_value := 0;
789
790
791
792 END Update_List_Header;
793
794
795
796 --===================================================================
797
798 -- NAME
799
800 -- Delete_List_Subset
801
802 --
803
804 -- PURPOSE
805
806 -- Private api to delete a list subset and all dependent data in a cascading fashion.
807
808 --
809
810 -- NOTES
811
812 -- 1. AO Admin will use this procedure to delete a List subset.
813
814 --
815
816 -- HISTORY
817
818 -- 09-May-2001 MSISTA Created
819
820 --===================================================================
821
822 PROCEDURE Delete_List_Subset(
823
824 p_list_subset_id IN NUMBER,
825
826 p_user_id IN NUMBER,
827
828 x_msg_data OUT NOCOPY VARCHAR2,
829
830 x_return_value OUT NOCOPY NUMBER
831
832 )
833
834 IS
835
836
837
838 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_List_Subset';
839
840 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
841
842
843
844 BEGIN
845
846
847
848 -- Standard Start of API savepoint
849
850 SAVEPOINT Delete_List_Subset;
851
852
853
854 -- Initialize outgoing parameters
855
856 x_msg_data := '';
857
858 x_return_value := 1;
859
860
861
862 UPDATE IEC_G_LIST_SUBSETS SET STATUS_CODE='DELETED'
863
864 WHERE LIST_SUBSET_ID = p_list_subset_id;
865
866
867
868 COMMIT;
869
870
871
872 EXCEPTION
873
874
875
876 WHEN OTHERS THEN
877
878 x_msg_data := 'IEC_ERR_DEL_SUBSET_FAIL';
879
880 x_return_value := 0;
881
882
883
884 END Delete_List_Subset;
885
886
887
888
889
890 -- ===============================================================
891
892 -- Start of Comments
893
894 -- ===============================================================
895
896 -- API Name
897
898 -- GET_TG_ENTRY_LIST
899
900 --
901
902 -- Used by Target Group Entry List Page to show the list
903
904 -- of entries belonging to a certain target group.
905
906 -- This procedure also gathers data for the page header and footer.
907
908 --
909
910 -- Type: Private
911
912 --
913
914 -- Parameters
915
916 -- IN
917
918 -- P_LIST_HEADER_ID IN NUMBER
919
920 -- , P_SEARCH_COLUMN IN VARCHAR2
921
922 -- , P_SEARCH_OPERATOR IN VARCHAR2
923
924 -- , P_SEARCH_PARAM IN VARCHAR2
925
926 -- , P_ORDER_BY IN NUMBER
927
928 -- , P_NEXT_ROW IN NUMBER
929
930 -- , P_MAX_ROWS IN NUMBER
931
932 -- , P_ORDER IN VARCHAR2
933
934 --
935
936 -- OUT
937
938 -- X_HEADER_DATA OUT NOCOPY TG_ENTRY_CURSOR
939
940 -- , X_CONTACT_POINT_DATA OUT NOCOPY TG_ENTRY_CURSOR
941
942 -- , X_TOTAL_ENTRIES OUT NUMBER
943
944 --
945
946 -- Version : Current version 1.0
947
948 --
949
950 -- End of Comments
951
952 -- ===============================================================
953
954 PROCEDURE GET_TG_ENTRY_LIST
955
956 ( P_LIST_HEADER_ID IN NUMBER
957
958 , P_SEARCH_COLUMN IN VARCHAR2
959
960 , P_SEARCH_OPERATOR IN VARCHAR2
961
962 , P_SEARCH_PARAM IN VARCHAR2
963
964 , P_ORDER_BY IN NUMBER
965
966 , P_NEXT_ROW IN NUMBER
967
968 , P_MAX_ROWS IN NUMBER
969
970 , P_ORDER IN VARCHAR2
971
972 , X_HEADER_DATA OUT NOCOPY TG_ENTRY_CURSOR
973
974 , X_ENTRY_DATA OUT NOCOPY TG_ENTRY_CURSOR
975
976 , X_TOTAL_ENTRIES OUT NOCOPY NUMBER )
977
978 AS
979
980 l_header_cursor TG_ENTRY_CURSOR;
981
982 l_entries_cursor TG_ENTRY_CURSOR;
983
984
985
986 l_source_type_view VARCHAR2(500);
987
988
989
990 l_header_stmt VARCHAR2(2000);
991
992 l_footer_stmt VARCHAR2(2000);
993
994
995
996 l_row_count NUMBER(10);
997
998 l_cp_count_stmt VARCHAR2(1000);
999
1000 l_cp_valid_count_stmt VARCHAR2(1000);
1001
1002 l_cp_invalid_count_stmt VARCHAR2(2000);
1003
1004 l_entries_stmt_temp VARCHAR2(3000);
1005
1006 l_entries_stmt VARCHAR2(6000);
1010 l_search_clause VARCHAR2(3000);
1007
1008
1009
1011
1012
1013
1014 BEGIN
1015
1016
1017
1018 -- get the source type view for this list
1019
1020 IEC_COMMON_UTIL_PVT.Get_SourceTypeView(P_LIST_HEADER_ID, l_source_type_view);
1021
1022
1023
1024 -- First get the header data: campaign, schedule, and target group names
1025
1026 l_header_stmt := ' SELECT ' ||
1027
1028 ' campaigns.CAMPAIGN_NAME, schedules.SCHEDULE_NAME, lists_vl.LIST_NAME ' ||
1029
1030 ' FROM ' ||
1031
1032 ' AMS_LIST_HEADERS_VL lists_vl, AMS_ACT_LISTS act_lists, AMS_CAMPAIGN_SCHEDULES_VL schedules, AMS_CAMPAIGNS_VL campaigns ' ||
1033
1034 ' WHERE ' ||
1035
1036 ' lists_vl.LIST_HEADER_ID = :1 ' ||
1037
1038 ' AND lists_vl.LIST_HEADER_ID = act_lists.LIST_HEADER_ID ' ||
1039
1040 ' AND act_lists.LIST_USED_BY_ID = schedules.SCHEDULE_ID ' ||
1041
1042 ' AND schedules.CAMPAIGN_ID = campaigns.CAMPAIGN_ID ';
1043
1044
1045
1046 -- Populate cursor
1047
1048 OPEN l_header_cursor for l_header_stmt using P_LIST_HEADER_ID;
1049
1050 X_HEADER_DATA := l_header_cursor;
1051
1052
1053
1054 -- Next get the list of target group entries
1055
1056
1057
1058 -- Get the max row count
1059
1060 l_row_count := P_NEXT_ROW + P_MAX_ROWS;
1061
1062
1063
1064 -- temporary strings that will be used to build the final query
1065
1066 l_cp_count_stmt := ' Get_Contact_Point_Count( ' ||
1067
1068 ' NVL(PHONE_NUMBER_S1, RAW_PHONE_NUMBER_S1), ' ||
1069
1070 ' NVL(PHONE_NUMBER_S2, RAW_PHONE_NUMBER_S2), ' ||
1071
1072 ' NVL(PHONE_NUMBER_S3, RAW_PHONE_NUMBER_S3), ' ||
1073
1074 ' NVL(PHONE_NUMBER_S4, RAW_PHONE_NUMBER_S4), ' ||
1075
1076 ' NVL(PHONE_NUMBER_S5, RAW_PHONE_NUMBER_S5), ' ||
1077
1078 ' NVL(PHONE_NUMBER_S6, RAW_PHONE_NUMBER_S6) ) ';
1079
1080
1081
1082 l_cp_invalid_count_stmt := ' Get_Invalid_CP_Count( ' ||
1083
1084 ' NVL(PHONE_NUMBER_S1, RAW_PHONE_NUMBER_S1), REASON_CODE_S1, ' ||
1085
1086 ' NVL(PHONE_NUMBER_S2, RAW_PHONE_NUMBER_S2), REASON_CODE_S2, ' ||
1087
1088 ' NVL(PHONE_NUMBER_S3, RAW_PHONE_NUMBER_S3), REASON_CODE_S3, ' ||
1089
1090 ' NVL(PHONE_NUMBER_S4, RAW_PHONE_NUMBER_S4), REASON_CODE_S4, ' ||
1091
1092 ' NVL(PHONE_NUMBER_S5, RAW_PHONE_NUMBER_S5), REASON_CODE_S5, ' ||
1093
1094 ' NVL(PHONE_NUMBER_S6, RAW_PHONE_NUMBER_S6), REASON_CODE_S6 ) ';
1095
1096
1097
1098 l_cp_valid_count_stmt := ' ( ' || l_cp_count_stmt || ' - ' || l_cp_invalid_count_stmt || ' ) ';
1099
1100
1101
1102
1103
1104 l_search_clause := '';
1105
1106
1107
1108 IF P_SEARCH_PARAM IS NOT NULL THEN
1109
1110
1111
1112 IF P_SEARCH_COLUMN IN ('PERSON_FIRST_NAME', 'PERSON_LAST_NAME', 'LIST.LIST_ENTRY_ID', 'PARTY_ID' ) THEN
1113
1114 l_search_clause := ' AND ' || P_SEARCH_COLUMN || P_SEARCH_OPERATOR || '''' || P_SEARCH_PARAM || '''';
1115
1116 ELSE
1117
1118 IF P_SEARCH_COLUMN = 'NUM_CPS' THEN
1119
1120 l_search_clause := ' AND ' || l_cp_count_stmt || P_SEARCH_OPERATOR || P_SEARCH_PARAM;
1121
1122 ELSIF P_SEARCH_COLUMN = 'NUM_VALID_CPS' THEN
1123
1124 l_search_clause := ' AND ' || l_cp_valid_count_stmt || P_SEARCH_OPERATOR || P_SEARCH_PARAM;
1125
1126 ELSIF P_SEARCH_COLUMN = 'NUM_INVALID_CPS' THEN
1127
1128 l_search_clause := ' AND ' || l_cp_invalid_count_stmt || P_SEARCH_OPERATOR || P_SEARCH_PARAM;
1129
1130 END IF;
1131
1132 END IF;
1133
1134
1135
1136 END IF;
1137
1138
1139
1140 l_entries_stmt_temp := ' SELECT ' ||
1141
1142 ' PERSON_FIRST_NAME, PERSON_LAST_NAME, ' ||
1143
1144 ' LIST.LIST_ENTRY_ID LIST_ENTRY_ID, PARTY_ID, ' ||
1145
1146 l_cp_count_stmt || ' NUM_CPS, ' ||
1147
1148 l_cp_valid_count_stmt || ' NUM_VALID_CPS, ' ||
1149
1150 l_cp_invalid_count_stmt || ' NUM_INVALID_CPS ' ||
1151
1152 ' FROM ' ||
1153
1154 l_source_type_view || ' LIST, ' ||
1155
1156 ' IEC_O_VALIDATION_REPORT_DETS VAL ' ||
1157
1158 ' WHERE ' ||
1159
1160 ' LIST.LIST_HEADER_ID = :1 ' || -- P_LIST_HEADER_ID
1161
1162 ' AND LIST.ENABLED_FLAG = ''Y'' ' ||
1163
1164 ' AND LIST.LIST_HEADER_ID = VAL.LIST_HEADER_ID(+) ' ||
1165
1166 ' AND LIST.LIST_ENTRY_ID = VAL.LIST_ENTRY_ID(+) ' ||
1167
1168 l_search_clause ||
1169
1170 ' ORDER BY ' || P_ORDER_BY || ' ' || P_ORDER;
1171
1172
1173
1174 l_entries_stmt := ' SELECT ' ||
1175
1176 ' PERSON_FIRST_NAME, PERSON_LAST_NAME, ' ||
1177
1178 ' LIST_ENTRY_ID, PARTY_ID, ' ||
1179
1180 ' NUM_CPS, NUM_VALID_CPS, NUM_INVALID_CPS ' ||
1181
1182 ' FROM ' ||
1183
1184 ' ( SELECT PERSON_FIRST_NAME, PERSON_LAST_NAME, LIST_ENTRY_ID, PARTY_ID, NUM_CPS, NUM_VALID_CPS, NUM_INVALID_CPS ' ||
1185
1186 ' FROM ( ' || l_entries_stmt_temp || ' ) AA WHERE ROWNUM <= :2 ) A ' || -- l_row_count
1187
1188 ' WHERE ' ||
1189
1190 ' A.LIST_ENTRY_ID NOT IN ' ||
1191
1192 ' ( SELECT LIST_ENTRY_ID FROM ( ' || l_entries_stmt_temp || ' ) BB WHERE ROWNUM <= :4 ) ' || -- P_NEXT_ROW
1193
1194 ' ORDER BY ' || P_ORDER_BY || ' ' || P_ORDER;
1195
1196
1197
1198 -- Populate cursor
1202 P_LIST_HEADER_ID, P_NEXT_ROW;
1199
1200 OPEN l_entries_cursor for l_entries_stmt using P_LIST_HEADER_ID, l_row_count,
1201
1203
1204 X_ENTRY_DATA := l_entries_cursor;
1205
1206
1207
1208
1209
1210 -- Then get the footer data: number of entries in total
1211
1212 EXECUTE IMMEDIATE
1213
1214 ' SELECT COUNT(LIST_ENTRY_ID)
1215
1216 FROM ' || '( ' || l_entries_stmt_temp || ' ) A '
1217
1218 INTO X_TOTAL_ENTRIES
1219
1220 USING P_LIST_HEADER_ID;
1221
1222
1223
1224 return;
1225
1226
1227
1228 END GET_TG_ENTRY_LIST;
1229
1230
1231
1232
1233
1234 -- ===============================================================
1235
1236 -- Start of Comments
1237
1238 -- ===============================================================
1239
1240 -- API Name
1241
1242 -- GET_TG_ENTRY_DETAILS
1243
1244 --
1245
1246 -- Used by Target Group Entry Details Page to show the details
1247
1248 -- (including contact points) belonging to a certain entry.
1249
1250 -- This procedure also gathers data for the page header.
1251
1252 -- A list of timezones is also displayed on the page; their
1253
1254 -- translations are fetched here.
1255
1256 --
1257
1258 -- Type: Private
1259
1260 --
1261
1262 -- Parameters
1263
1264 -- IN
1265
1266 -- P_LIST_HEADER_ID IN NUMBER
1267
1268 -- , P_LIST_ENTRY_ID IN NUMBER
1269
1270 -- , P_LANGUAGE IN VARCHAR2
1271
1272 --
1273
1274 -- OUT
1275
1276 -- X_HEADER_DATA OUT NOCOPY TG_ENTRY_CURSOR
1277
1278 -- , X_CONTACT_POINT_DATA OUT NOCOPY TG_ENTRY_CURSOR
1279
1280 -- , X_TIME_ZONE_DATA OUT NOCOPY TG_ENTRY_CURSOR
1281
1282 --
1283
1284 -- Version : Current version 1.0
1285
1286 --
1287
1288 -- End of Comments
1289
1290 -- ===============================================================
1291
1292 PROCEDURE GET_TG_ENTRY_DETAILS
1293
1294 ( P_LIST_HEADER_ID IN NUMBER
1295
1296 , P_LIST_ENTRY_ID IN NUMBER
1297
1298 , P_LANGUAGE IN VARCHAR2
1299
1300 , X_HEADER_DATA OUT NOCOPY TG_ENTRY_CURSOR
1301
1302 , X_CONTACT_POINT_DATA OUT NOCOPY TG_ENTRY_CURSOR
1303
1304 , X_TIME_ZONE_DATA OUT NOCOPY TG_ENTRY_CURSOR )
1305
1306 AS
1307
1308 l_header_cursor TG_ENTRY_CURSOR;
1309
1310 l_contact_point_cursor TG_ENTRY_CURSOR;
1311
1312 l_time_zone_cursor TG_ENTRY_CURSOR;
1313
1314
1315
1316 l_header_stmt VARCHAR2(2000);
1317
1318 l_contact_point_stmt VARCHAR2(2000);
1319
1320 l_time_zone_stmt VARCHAR2(2000);
1321
1322 l_source_type_view VARCHAR2(500);
1323
1324
1325
1326 BEGIN
1327
1328
1329
1330 -- get the source type view for this list
1331
1332 IEC_COMMON_UTIL_PVT.Get_SourceTypeView(P_LIST_HEADER_ID, l_source_type_view);
1333
1334
1335
1336 -- First get the header data: campaign, schedule, and target group names
1337
1338 l_header_stmt := ' SELECT ' ||
1339
1340 ' campaigns.CAMPAIGN_NAME, schedules.SCHEDULE_NAME, lists_vl.LIST_NAME, ' ||
1341
1342 ' list_entries.PERSON_FIRST_NAME, list_entries.PERSON_LAST_NAME, list_entries.PARTY_ID ' ||
1343
1344 ' FROM ' ||
1345
1346 ' AMS_LIST_HEADERS_VL lists_vl, AMS_ACT_LISTS act_lists, AMS_CAMPAIGN_SCHEDULES_VL schedules, AMS_CAMPAIGNS_VL campaigns, ' ||
1347
1348 l_source_type_view || ' list_entries ' ||
1349
1350 ' WHERE ' ||
1351
1352 ' list_entries.LIST_ENTRY_ID = :1 ' ||
1353
1354 ' AND list_entries.LIST_HEADER_ID = :2 ' ||
1355
1356 ' AND list_entries.LIST_HEADER_ID = lists_vl.LIST_HEADER_ID ' ||
1357
1358 ' AND lists_vl.LIST_HEADER_ID = act_lists.LIST_HEADER_ID ' ||
1359
1360 ' AND act_lists.LIST_USED_BY_ID = schedules.SCHEDULE_ID ' ||
1361
1362 ' AND schedules.CAMPAIGN_ID = campaigns.CAMPAIGN_ID ';
1363
1364
1365
1366 -- Next get the list of contact points
1367
1368 l_contact_point_stmt := ' SELECT ' ||
1369
1370 ' CONTACT_POINT_ID_S1, PHONE_COUNTRY_CODE_S1, PHONE_AREA_CODE_S1, PHONE_NUMBER_S1, RAW_PHONE_NUMBER_S1, TIME_ZONE_S1, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S1), REASON_CODE_S1, ' ||
1371
1372 ' CONTACT_POINT_ID_S2, PHONE_COUNTRY_CODE_S2, PHONE_AREA_CODE_S2, PHONE_NUMBER_S2, RAW_PHONE_NUMBER_S2, TIME_ZONE_S2, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S2), REASON_CODE_S2, ' ||
1373
1374 ' CONTACT_POINT_ID_S3, PHONE_COUNTRY_CODE_S3, PHONE_AREA_CODE_S3, PHONE_NUMBER_S3, RAW_PHONE_NUMBER_S3, TIME_ZONE_S3, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S3), REASON_CODE_S3, ' ||
1375
1376 ' CONTACT_POINT_ID_S4, PHONE_COUNTRY_CODE_S4, PHONE_AREA_CODE_S4, PHONE_NUMBER_S4, RAW_PHONE_NUMBER_S4, TIME_ZONE_S4, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S4), REASON_CODE_S4, ' ||
1377
1378 ' CONTACT_POINT_ID_S5, PHONE_COUNTRY_CODE_S5, PHONE_AREA_CODE_S5, PHONE_NUMBER_S5, RAW_PHONE_NUMBER_S5, TIME_ZONE_S5, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S5), REASON_CODE_S5, ' ||
1379
1380 ' CONTACT_POINT_ID_S6, PHONE_COUNTRY_CODE_S6, PHONE_AREA_CODE_S6, PHONE_NUMBER_S6, RAW_PHONE_NUMBER_S6, TIME_ZONE_S6, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S6), REASON_CODE_S6 ' ||
1381
1382 ' FROM ' ||
1383
1384 l_source_type_view || ' LIST, ' ||
1385
1386 ' IEC_O_VALIDATION_REPORT_DETS VAL ' ||
1387
1388 ' WHERE ' ||
1389
1393
1390 ' LIST.LIST_HEADER_ID = :1 ' ||
1391
1392 ' AND LIST.LIST_HEADER_ID = VAL.LIST_HEADER_ID(+) ' ||
1394 ' AND LIST.LIST_ENTRY_ID = :2 ' ||
1395
1396 ' AND LIST.LIST_ENTRY_ID = VAL.LIST_ENTRY_ID(+) ';
1397
1398
1399
1400 -- Finally get the time zone info
1401
1402 l_time_zone_stmt := ' SELECT ' ||
1403
1404 ' B.UPGRADE_TZ_ID, T.TIMEZONE_CODE ' ||
1405
1406 ' FROM ' ||
1407
1408 ' FND_TIMEZONES_B B, FND_TIMEZONES_TL T ' ||
1409
1410 ' WHERE ' ||
1411
1412 ' B.TIMEZONE_CODE = T.TIMEZONE_CODE ' ||
1413
1414 ' AND T.LANGUAGE = :1 ' || -- P_LANGUAGE
1415
1416 ' ORDER BY T.TIMEZONE_CODE ASC ';
1417
1418
1419
1420 OPEN l_header_cursor for l_header_stmt using P_LIST_ENTRY_ID, P_LIST_HEADER_ID;
1421
1422 X_HEADER_DATA := l_header_cursor;
1423
1424
1425
1426 OPEN l_contact_point_cursor for l_contact_point_stmt using P_LIST_HEADER_ID, P_LIST_ENTRY_ID;
1427
1428 X_CONTACT_POINT_DATA := l_contact_point_cursor;
1429
1430
1431
1432 OPEN l_time_zone_cursor for l_time_zone_stmt using P_LANGUAGE;
1433
1434 X_TIME_ZONE_DATA := l_time_zone_cursor;
1435
1436
1437
1438 return;
1439
1440
1441
1442 END GET_TG_ENTRY_DETAILS;
1443
1444 PROCEDURE Copy_Calendar_Day(
1445
1446 p_calendar_id IN NUMBER,
1447 p_day_id IN NUMBER,
1448 p_copyto_code IN VARCHAR2,
1449 p_createdBy IN NUMBER,
1450 p_creationDate IN DATE,
1451 p_updatedBy IN NUMBER,
1452 p_updateDate IN DATE,
1453 p_updateLogin IN NUMBER,
1454 p_versionNumber IN NUMBER
1455 )
1456 AS
1457 l_day_copyto_id NUMBER;
1458 cursor c_copy is
1459 select CALLABLE_REGION_CODE, START_TIME, END_TIME, TYPE_CODE from iec_g_cal_callable_rgns
1460 where DAY_ID = p_day_id;
1461 BEGIN
1462 select day_id into l_day_copyto_id from iec_g_cal_days_b where CALENDAR_ID = p_calendar_id
1463 and DAY_CODE = 'DAY_OF_WEEK' and PATTERN_CODE = p_copyto_code;
1464
1465 delete from iec_g_cal_callable_rgns where DAY_ID = l_day_copyto_id;
1466 for v_copy in c_copy loop
1467 insert into iec_g_cal_callable_rgns(CALLABLE_REGION_ID, DAY_ID, CALLABLE_REGION_CODE,
1468 START_TIME, END_TIME, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
1469 OBJECT_VERSION_NUMBER, TYPE_CODE)
1470 values (IEC_G_CAL_CALLABLE_RGNS_S.NEXTVAL,l_day_copyto_id,v_copy.CALLABLE_REGION_CODE,
1471 v_copy.START_TIME, v_copy.END_TIME,p_createdBy, p_creationDate,p_updatedBy,p_updateDate,
1472 p_updateLogin, p_versionNumber,v_copy.TYPE_CODE);
1473 end loop;
1474 commit;
1475
1476 END Copy_Calendar_Day;
1477
1478 END IEC_ADMIN_PVT;
1479