[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_MLIST_ENUMS_PVT
Source
4 -- Sub-Program Units
1 PACKAGE BODY AST_UWQ_MLIST_ENUMS_PVT AS
2 /* $Header: ASTENMLB.pls 115.12 2004/08/10 06:40:59 rkumares ship $ */
3
5
6 PROCEDURE ENUMERATE_MLIST_NODES
7 (P_RESOURCE_ID IN NUMBER
8 ,P_LANGUAGE IN VARCHAR2
9 ,P_SOURCE_LANG IN VARCHAR2
10 ,P_SEL_ENUM_ID IN NUMBER
11 ) AS
12
13 l_node_label VARCHAR2(200);
14 l_ld_list IEU_PUB.EnumeratorDataRecordList;
15
16 l_node_counter NUMBER;
17 l_bind_list IEU_PUB.BindVariableRecordList ;
18
19 l_Profile varchar2(10) ;
23 SELECT distinct schedule_id, schedule_name, list_source_type
20 l_Access varchar2(10) ;
21
22 CURSOR c_mlist_nodes(pResourceID number) IS
24 FROM
25 ast_mlist1_uwq_v
26 WHERE
27 resource_id = pResourceID
28 ORDER BY 1;
29
30 CURSOR c_mlist1_nodes IS
31 SELECT schedule_id, schedule_name, list_source_type
32 FROM
33 ast_mlist1_all_uwq_v
34 ORDER BY 1;
35
36 lkp_type VARCHAR2(30) := 'AST_UWQ_LABELS';
37 lkp_code VARCHAR2(30) := 'MLIST_WORK_CLASS_LABEL';
38
39 BEGIN
40
41 /* label, view, and where for main node taken from enum table anyway */
42 l_node_counter := 0;
43
44 Select meaning into l_node_label
45 from ast_lookups
46 where lookup_type = lkp_type
47 and lookup_code = lkp_code;
48
49 l_Profile:=NVL(fnd_profile.value('AST_MLIST_ALL_CAMPAIGNS'),'N');
50 l_Access:= NVL(fnd_profile.value('AS_CUST_ACCESS'), 'F');
51
52 /* 'Y' - List All Campaign, 'N' - List only Assigned Campaign using Campaign Assignment */
53 if ( l_profile = 'Y' ) then
54
55 -- Bug # 3595753
56 /* Added the where clause in such a manner that it will always be resulting
57 in a true value, because the solution suggested by the base bug that by
58 changing the value of RES_CAT_ENUM_FLAG to 'N' will not look at the where clause
59 didn't help. So instead, changed the procedure to include the where clause. */
60
61 l_bind_list(1).bind_var_name := ':DUMMY_VAR' ;
62 l_bind_list(1).bind_var_value := 1 ;
63 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
64 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
65 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST1_ALL_UWQ_V';
66 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST1_ALL_UWQ_DS';
67 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
68 l_ld_list(l_node_counter).WHERE_CLAUSE := '1 = :DUMMY_VAR';
69 l_ld_list(l_node_counter).NODE_TYPE := 0;
70 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
71 l_ld_list(l_node_counter).NODE_DEPTH := 1;
72 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
73 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
74 l_node_counter := l_node_counter + 1;
75
76 SAVEPOINT start_mlist_enumeration;
77
78 FOR cur_rec IN c_mlist1_nodes LOOP
79
80 /* Need to check for security */
81 if l_Access in ('F','P') then
82
83 l_bind_list(1).bind_var_name := ':SCHEDULE_ID' ;
84 l_bind_list(1).bind_var_value := cur_rec.schedule_id ;
85 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
86
87 l_ld_list(l_node_counter).WHERE_CLAUSE := ' SCHEDULE_ID = :SCHEDULE_ID ';
88
89 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST_ALL_UWQ_V';
90 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST_ALL_UWQ_DS';
91
92 else
93
94 l_bind_list(1).bind_var_name := ':SCHEDULE_ID' ;
95 l_bind_list(1).bind_var_value := cur_rec.schedule_id ;
96 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
97
98 l_bind_list(2).bind_var_name := ':RESOURCE_ID' ;
99 l_bind_list(2).bind_var_value := P_RESOURCE_ID ;
100 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
101
102 l_ld_list(l_node_counter).WHERE_CLAUSE :=
103 --For perf bug 2829000
104
105 -- ' RESOURCE_ID = :RESOURCE_ID and SCHEDULE_ID = :SCHEDULE_ID ';
106 ' SCHEDULE_ID = :SCHEDULE_ID AND ' ||
107 ' EXISTS (SELECT /*+ no_unnest */ 1 ' ||
108 ' FROM AS_ACCESSES_ALL ASS ' ||
109 ' WHERE ASS.SALESFORCE_ID = :RESOURCE_ID AND ' ||
110 ' CUSTOMER_ID = ASS.CUSTOMER_ID ) ';
111
112 if (cur_rec.list_source_type = 'ORGANIZATION_CONTACT_LIST') then
113
114 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST_SECURE_UWQ_V';
115 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST_SECURE_UWQ_DS';
116
117 else
118 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST_ALLSECURE_UWQ_DS';
119 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST_ALLSECURE_UWQ_V';
120
121 end if;
122
123 end if;
124
125 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.schedule_name;
126 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
127 l_ld_list(l_node_counter).NODE_TYPE := 0;
128 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
129 l_ld_list(l_node_counter).NODE_DEPTH := 2;
130
131 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
132 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
133
134 l_node_counter := l_node_counter + 1;
135 END LOOP ;
136
137 else
138 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
139 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
140 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
141
142 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
143 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST1_UWQ_V';
144 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST1_UWQ_DS';
145 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
146 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID ';
147 l_ld_list(l_node_counter).NODE_TYPE := 0;
148 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
149 l_ld_list(l_node_counter).NODE_DEPTH := 1;
150 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
151 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
152
153 l_node_counter := l_node_counter + 1;
154
155 SAVEPOINT start_mlist1_enumeration;
156 FOR cur_rec IN c_mlist_nodes(p_Resource_ID) LOOP
157
161 l_bind_list(1).bind_var_name := ':SCHEDULE_ID' ;
158 /* Need to check for security */
159 if l_Access in ('F','P') then
160
162 l_bind_list(1).bind_var_value := cur_rec.schedule_id ;
163 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
164
165 l_ld_list(l_node_counter).WHERE_CLAUSE := ' SCHEDULE_ID = :SCHEDULE_ID ';
166
167 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST_UWQ_V';
168 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST_UWQ_DS';
169
170 else
171
172 l_bind_list(1).bind_var_name := ':SCHEDULE_ID' ;
173 l_bind_list(1).bind_var_value := cur_rec.schedule_id ;
174 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
175
176 l_bind_list(2).bind_var_name := ':RESOURCE_ID' ;
177 l_bind_list(2).bind_var_value := P_RESOURCE_ID ;
178 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
179
180 l_ld_list(l_node_counter).WHERE_CLAUSE :=
181 --For perf bug 2829000
182
183 -- ' RESOURCE_ID = :RESOURCE_ID and SCHEDULE_ID = :SCHEDULE_ID ';
184 ' SCHEDULE_ID = :SCHEDULE_ID AND ' ||
185 ' EXISTS (SELECT /*+ no_unnest */ 1 ' ||
186 ' FROM AS_ACCESSES_ALL ASS ' ||
187 ' WHERE ASS.SALESFORCE_ID = :RESOURCE_ID AND ' ||
188 ' CUSTOMER_ID = ASS.CUSTOMER_ID ) ';
189
190
191 if (cur_rec.list_source_type = 'ORGANIZATION_CONTACT_LIST') then
192
193 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST_SECURE_UWQ_V';
194 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST_SECURE_UWQ_DS';
195 else
196
197 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MLIST_ALLSECURE_UWQ_V';
198 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MLIST_ALLSECURE_UWQ_DS';
199 end if;
200
201 end if;
202
203 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.schedule_name;
204 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
205 l_ld_list(l_node_counter).NODE_TYPE := 0;
206 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
207 l_ld_list(l_node_counter).NODE_DEPTH := 2;
208
209 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
210 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
211
212 l_node_counter := l_node_counter + 1;
213 END LOOP ;
214
215
216 END IF ;
217 /* END 'Y' - List All Campaign */
218
219 -- END LOOP;
220
221 IEU_PUB.ADD_UWQ_NODE_DATA
222 (P_RESOURCE_ID,
223 P_SEL_ENUM_ID,
224 l_ld_list
225 );
226
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 ROLLBACK TO start_mlist_enumeration;
231 RAISE;
232
233 END ENUMERATE_MLIST_NODES;
234
235 -- PL/SQL Block
236 END AST_UWQ_MLIST_ENUMS_PVT;