[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_UTIL_PKG
Source
1 PACKAGE BODY AMS_LIST_UTIL_PKG as
2 /* $Header: amsvlutb.pls 115.7 2002/11/22 19:49:58 jieli ship $ */
3 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
4 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6
7 PROCEDURE get_supp_sql_string(
8 p_object_type in VARCHAR2,
9 p_object_id in NUMBER,
10 x_where_clause OUT NOCOPY VARCHAR2,
11 x_status OUT NOCOPY VARCHAR2
12 ) is
13 l_rule_id NUMBER;
14 l_list_header_id NUMBER;
15 l_media_id NUMBER;
16 l_campaign_id NUMBER;
17 l_string VARCHAR2(4000);
18 i NUMBER := 0;
19 l_col_string VARCHAR2(2000);
20 l_column VARCHAR2(60);
21 l_return_status VARCHAR2(1);
22
23
24
25 cursor c_get_camp_id is
26 select ac.campaign_id
27 from ams_campaign_schedules_vl ac
28 where ac.schedule_id = p_object_id;
29
30 cursor c_get_list(cur_campaign_id number) is
31 SELECT acr.list_header_id
32 FROM ams_list_cont_restrictions acr , ams_list_headers_all alh
33 WHERE acr.list_header_id = alh.list_header_id
34 and alh.status_code not in ('DRAFT','CANCELLED','ARCHIVED')
35 and alh.list_type = 'SUPPRESSION'
36 and ( acr.do_not_contact_flag = 'Y'
37 or acr.list_used_by_id = cur_campaign_id
38 or acr.media_id = 20
39 ) ;
40
41
42 begin
43 open c_get_camp_id ;
44 fetch c_get_camp_id into l_campaign_id;
45 close c_get_camp_id ;
46
47 i := 0;
48 open c_get_list(l_campaign_id);
49 loop
50 fetch c_get_list into l_list_header_id ;
51 exit when c_get_list%notfound;
52 if i = 0 then
53 l_string := l_string|| 'ale_01.list_header_id in ( ';
54 l_string := l_string|| l_list_header_id ;
55 else
56 l_string := l_string|| ','|| l_list_header_id;
57 end if;
58 i := 1;
59 end loop;
60 close c_get_list;
61 if i = 1 then
62 l_string := l_string|| ')';
63 x_where_clause := 'select ale_01.party_id ' ||
64 'from ams_list_entries ale_01 '||
65 'where ' || l_string ;
66 x_status := FND_API.G_RET_STS_SUCCESS ;
67 else
68 x_where_clause := '';
69 x_status := FND_API.G_RET_STS_ERROR ;
70 end if;
71
72
73
74 exception
75 when others then
76 x_where_clause := '';
77 x_status := FND_API.G_RET_STS_ERROR ;
78 end;
79
80 PROCEDURE get_supp_sql_string(
81 p_list_header_id in NUMBER,
82 p_table_alias in varchar2,
83 p_object_type in varchar2 ,--default null,
84 p_object_id in number ,--default null,
85 p_media_type in number ,--default 'EMAIL',
86 p_where_clause OUT NOCOPY varchar2
87 ) IS
88
89 l_rule_id NUMBER;
90 l_list_header_id NUMBER;
91 l_media_id NUMBER;
92 l_campaign_id NUMBER;
93 l_string VARCHAR2(4000);
94 i NUMBER := 0;
95 l_col_string VARCHAR2(2000);
96 l_column VARCHAR2(60);
97 l_return_status VARCHAR2(1);
98
99
100
101 cursor c_get_rule_id is
102 select am.dedupe_rule_id, ac.campaign_id, am.media_id
103 from ams_campaign_schedules_vl ac, ams_media_vl am
104 where ac.activity_id = am.media_id
105 and am.dedupe_rule_id is not null
106 and ac.schedule_id = p_object_id;
107
108 cursor c_get_list(cur_campaign_id number, cur_media_id number) is
109 SELECT acr.list_header_id
110 FROM ams_list_cont_restrictions acr , ams_list_headers_all alh
111 WHERE acr.list_header_id = alh.list_header_id
112 and alh.status_code not in ('DRAFT','CANCELLED','ARCHIVED')
113 and alh.list_type = 'SUPPRESSION'
114 and ( acr.do_not_contact_flag = 'Y'
115 or acr.list_used_by_id = cur_campaign_id
116 or acr.media_id = cur_media_id) ;
117
118 cursor c_rule_field(cur_rule_id number) is
119 SELECT field_column_name
120 FROM ams_list_rule_fields
121 WHERE list_rule_id = cur_rule_id;
122
123 begin
124 open c_get_rule_id ;
125 fetch c_get_rule_id into l_rule_id, l_campaign_id, l_media_id;
126 close c_get_rule_id ;
127
128 if l_rule_id is not null then
129 l_string := ' and not exists ( select '||''''|| 'x'||'''';
130 l_string := l_string||' from ams_list_entries where ' ;
131
132 i := 0;
133 open c_rule_field(l_rule_id);
134 LOOP
135 fetch c_rule_field into l_column;
136 exit when c_rule_field%notfound;
137 if i = 0 then
138 l_string := l_string|| 'ams_list_entries.'||l_column || ' = ';
139 l_string := l_string|| p_table_alias||'.'||l_column || ' ';
140 else
141 l_string := l_string|| ' and ams_list_entries.'||l_column || ' = ';
142 l_string := l_string|| p_table_alias||'.'||l_column || ' ';
143 end if;
144 i := 1;
145 END LOOP;
146 close c_rule_field;
147 l_string := l_string||'' ;
148
149
150 i := 0;
151 open c_get_list(l_campaign_id, l_media_id );
152 loop
153 fetch c_get_list into l_list_header_id ;
154 exit when c_get_list%notfound;
155 if i = 0 then
156 l_string := l_string|| 'ams_list_entries.list_header_id in ( ';
157 l_string := l_string|| l_list_header_id ;
158 else
159 l_string := l_string|| ','|| l_list_header_id;
160 end if;
161 i := 1;
162 end loop;
163 close c_get_list;
164 end if ;
165 if i = 1 then
166 l_string := l_string|| ')';
167 else
168 l_string := ' and 1 = 1';
169 end if;
170
171 p_where_clause := l_string;
172
173 exception
174 when others then
175 null;
176 end get_supp_sql_string;
177 END AMS_LIST_UTIL_PKG ;