[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_LEADS_ENUMS_PVT
Source
4 -- Sub-Program Units
1 PACKAGE BODY AST_UWQ_LEADS_ENUMS_PVT AS
2 /* $Header: ASTENLDB.pls 120.1 2006/03/16 12:21:45 solin noship $ */
3
5
6
7
8 PROCEDURE ENUMERATE_LEADS_NODES
9 (P_RESOURCE_ID IN NUMBER
10 ,P_LANGUAGE IN VARCHAR2
11 ,P_SOURCE_LANG IN VARCHAR2
12 ,P_SEL_ENUM_ID IN NUMBER
13 )
14 AS
15
16 l_node_label VARCHAR2(1000);
17 l_ld_list IEU_PUB.EnumeratorDataRecordList;
18
19 l_node_counter NUMBER;
20 l_bind_list IEU_PUB.BindVariableRecordList ;
21 l_prior_days VARCHAR2(10) ;
22
23 -- SOLIN, bug 5094263
24 CURSOR c_get_node_label(c_lookup_type VARCHAR2, c_lookup_code VARCHAR2) IS
25 Select meaning
26 from ast_lookups
27 where lookup_type = c_lookup_type
28 and lookup_code = c_lookup_code;
29 -- SOLIN, end bug 5094263
30
31 CURSOR c_lead_nodes IS
32 SELECT status_code, meaning
33 FROM
34 as_statuses_vl
35 WHERE
36 enabled_flag = 'Y' and LEAD_FLAG = 'Y' and OPP_OPEN_STATUS_FLAG = 'Y'
37 ORDER BY 1;
38
39 /* Removed the USAGE indicator check
40 changes made by vimpi on 19th october 20001
41 CURSOR c_lead_nodes IS
42 select status_code,meaning
43 from as_statuses_vl
44 where enabled_flag = 'Y'
45 and lead_flag = 'Y'
46 and opp_open_status_flag = 'Y'
47 and usage_indicator in ('ALL','OS','PRM')
48 order by 1 ;
49 */
50
51 BEGIN
52
53 /* label, view, and where for main node taken from enum table anyway */
54
55 l_node_counter := 0;
56 l_prior_days := NVL(fnd_profile.value('AST_DEFAULT_PRIOR_DAYS'), '30');
57
58 SAVEPOINT start_lead_enumeration;
59
60 -- ***************************************
61 -- swkhanna 9/3
62 -- Base Node 'My Leads (Owner)' will be defaulted to 30 DAYS
63 -- ***************************************
64
65 -- SOLIN, bug 5094263
66 OPEN c_get_node_label('AST_UWQ_LABELS', 'LEADS_WORK_CLASS_LABEL');
67 FETCH c_get_node_label INTO l_node_label;
68 CLOSE c_get_node_label;
69 -- SOLIN, end bug 5094263
70
71 -- swkhanna 9/3
72 -- l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
73 -- l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
74 -- l_bind_list(1).bind_var_data_type := 'NUMBER' ;
75 -- swkhanna 9/3
76
77 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
78 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
79 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
80 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
81 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
82 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
83 -- swkhanna 9/3
84 -- added by vimpi on 7th dec to decrease cost
85 -- 4/29/03 commented by swkhanna
86 -- l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (sysdate - last_update_date) <= 30';
87 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
88 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 30)';
92 l_ld_list(l_node_counter).NODE_DEPTH := 1;
89 -- swkhanna 9/3
90 l_ld_list(l_node_counter).NODE_TYPE := 0;
91 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
93
94 l_node_counter := l_node_counter + 1;
95
96
97 -- ***************************************
98 -- swkhanna 9/5
99 -- Add another level of nodes with time line 30 DAYS
100 -- ****************************************
101 --SAVEPOINT start_lead_enumeration;
102 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
103 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
104 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
105 -- Get Node Label from message
106 fnd_message.set_name('AST','AST_UWQ_30_DAYS');
107 l_node_label := fnd_message.get;
108 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
109 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
110 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
111 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
112 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
113 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
114 -- l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| ' (sysdate - last_update_date) <= 30';
115 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
116 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 30)';
117 l_ld_list(l_node_counter).NODE_TYPE := 0;
118 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
119 l_ld_list(l_node_counter).NODE_DEPTH := 2;
120
121 l_node_counter := l_node_counter + 1;
122
123 ------------------------------------------------------------------------------------
124 -- Enumerate sub nodes for 30 days. The subnodes are based on the lead statuses
125 ------------------------------------------------------------------------------------
126
127 FOR cur_rec IN c_lead_nodes LOOP
128
129 --added bind var by vimpi on 1rst nov/2001
130 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
131 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
132 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
133
134 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
135 l_bind_list(2).bind_var_value := cur_rec.status_code ;
136 l_bind_list(2).bind_var_data_type := 'CHAR' ;
137
138 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
139 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
140 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
141 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
142 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
143 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
144 --added by vimpi on 7th december to lower the cost
145
146 -- l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE' || ' and (sysdate - last_update_date) <= 30';
147
148 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
149 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 30)';
150
151 l_ld_list(l_node_counter).NODE_TYPE := 0;
152 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
156 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
153 l_ld_list(l_node_counter).NODE_DEPTH := 3;
154 --added by vimpi on 2nd nov/01
155
157 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
158
159 l_node_counter := l_node_counter + 1;
160
161 END LOOP;
162 --
163 -- ***************************************
164 -- swkhanna 9/3
165 -- Add another level of nodes with time line 90 DAYS
166 -- ****************************************
167 --SAVEPOINT start_lead_enumeration;
168 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
169 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
170 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
171 -- Get Node Label from message
172 fnd_message.set_name('AST','AST_UWQ_90_DAYS');
173 l_node_label := fnd_message.get;
174 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
175 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
176 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
180 --l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| ' (sysdate - last_update_date) <= 90';
177 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
178 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
179 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
181
182 -- 4/29 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
183 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 90)';
184
185 l_ld_list(l_node_counter).NODE_TYPE := 0;
186 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
187 l_ld_list(l_node_counter).NODE_DEPTH := 2;
188
189 l_node_counter := l_node_counter + 1;
190
191 ------------------------------------------------------------------------------------
192 -- Enumerate sub nodes for 90 days. The subnodes are based on the lead statuses
193 ------------------------------------------------------------------------------------
194
195 FOR cur_rec IN c_lead_nodes LOOP
196
197 --added bind var by vimpi on 1rst nov/2001
198 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
199 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
200 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
201
202 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
203 l_bind_list(2).bind_var_value := cur_rec.status_code ;
204 l_bind_list(2).bind_var_data_type := 'CHAR' ;
205
206 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
207 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
208 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
209 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
210 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
211 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
212 --added by vimpi on 7th december to lower the cost
213
214 --l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE' || ' and (sysdate - last_update_date) <= 90';
215
216 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
217 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 90)';
218
219 l_ld_list(l_node_counter).NODE_TYPE := 0;
220 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
221 --l_ld_list(l_node_counter).NODE_DEPTH := 2;
222 l_ld_list(l_node_counter).NODE_DEPTH := 3;
223 --added by vimpi on 2nd nov/01
224
225 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
226 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
227
228 l_node_counter := l_node_counter + 1;
229
230 END LOOP;
231 -- ***************************************
232 -- swkhanna 9/3
233 -- Add another level of nodes with time line 6 MONTHS
234 -- ****************************************
235 --SAVEPOINT start_lead_enumeration;
236 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
237 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
238 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
239 -- Get Node Label from message
240 fnd_message.set_name('AST','AST_UWQ_6_MNTHS');
241 l_node_label := fnd_message.get;
242 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
243 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
244 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
245 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
246 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
247 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
248 --l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| '(sysdate - last_update_date) <= 180';
249
250 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
251 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 180)';
252
253 l_ld_list(l_node_counter).NODE_TYPE := 0;
254 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
255 l_ld_list(l_node_counter).NODE_DEPTH := 2;
256
257 l_node_counter := l_node_counter + 1;
258
259 ------------------------------------------------------------------------------------
260 -- Enumerate sub nodes for 180 days. The subnodes are based on the lead statuses
261 ------------------------------------------------------------------------------------
262
263 FOR cur_rec IN c_lead_nodes LOOP
264
265 --added bind var by vimpi on 1rst nov/2001
266 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
267 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
268 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
269
270 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
271 l_bind_list(2).bind_var_value := cur_rec.status_code ;
272 l_bind_list(2).bind_var_data_type := 'CHAR' ;
273
274 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
275 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
276 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
277 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
278 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
279 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
280 --added by vimpi on 7th december to lower the cost
281
285 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 180)';
282 -- l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE'|| ' and (sysdate - last_update_date) <= 180';
283
284 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
286
287 l_ld_list(l_node_counter).NODE_TYPE := 0;
288 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
289 --l_ld_list(l_node_counter).NODE_DEPTH := 2;
290 l_ld_list(l_node_counter).NODE_DEPTH := 3;
291 --added by vimpi on 2nd nov/01
292
293 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
294 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
295
296
297
298 l_node_counter := l_node_counter + 1;
299
300 END LOOP;
301
302
303 -- ***************************************
304 -- swkhanna 9/3
305 -- Add another level of nodes with time line 1 Year
306 -- ****************************************
307 --SAVEPOINT start_lead_enumeration;
308 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
309 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
310 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
311 -- Get Node Label from message
312 fnd_message.set_name('AST','AST_UWQ_1_YEAR');
313 l_node_label := fnd_message.get;
314 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
315 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
316 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
317 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
318 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
319 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
320 -- l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| '(sysdate - last_update_date) <= 365';
321
322 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
323 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 365)';
324
325 l_ld_list(l_node_counter).NODE_TYPE := 0;
326 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
327 l_ld_list(l_node_counter).NODE_DEPTH := 2;
328
329 l_node_counter := l_node_counter + 1;
330
331 ------------------------------------------------------------------------------------
332 -- Enumerate sub nodes for 365 days. The subnodes are based on the lead statuses
333 ------------------------------------------------------------------------------------
334
335 FOR cur_rec IN c_lead_nodes LOOP
336
337 --added bind var by vimpi on 1rst nov/2001
338 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
339 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
340 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
341
342 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
343 l_bind_list(2).bind_var_value := cur_rec.status_code ;
344 l_bind_list(2).bind_var_data_type := 'CHAR' ;
345
346 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
347 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
348 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
349 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
350 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
351 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
352 --added by vimpi on 7th december to lower the cost
353
354 --l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE'|| ' and (sysdate - last_update_date) <= 365';
355
356 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
357 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 365)';
358
359 l_ld_list(l_node_counter).NODE_TYPE := 0;
360 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
361 --l_ld_list(l_node_counter).NODE_DEPTH := 2;
362 l_ld_list(l_node_counter).NODE_DEPTH := 3;
363 --added by vimpi on 2nd nov/01
364
365 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
366 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
367
368
369
370 l_node_counter := l_node_counter + 1;
371
372 END LOOP;
373
374
375 -- ***************************************
376 -- swkhanna 9/3
377 -- Add another level of nodes with time line ALL
378 -- ****************************************
379 --SAVEPOINT start_lead_enumeration;
380 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
381 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
382 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
383 -- Get Node Label from message
384 fnd_message.set_name('AST','AST_UWQ_ALL_LEADS');
385 l_node_label := fnd_message.get;
386 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
387 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
388 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
389 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
390 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
391 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
395 l_ld_list(l_node_counter).NODE_DEPTH := 2;
392 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID ';
393 l_ld_list(l_node_counter).NODE_TYPE := 0;
394 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
396
397 l_node_counter := l_node_counter + 1;
398
399 ------------------------------------------------------------------------------------
400 -- Enumerate sub nodes for All Leads. The subnodes are based on the lead statuses
401 ------------------------------------------------------------------------------------
402
403 FOR cur_rec IN c_lead_nodes LOOP
404
405 --added bind var by vimpi on 1rst nov/2001
406 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
407 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
408 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
409
410 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
411 l_bind_list(2).bind_var_value := cur_rec.status_code ;
412 l_bind_list(2).bind_var_data_type := 'CHAR' ;
413
414 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
415 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
419 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
416 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
417 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_SALESLEAD_UWQ_REF_V';
418 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
420 --added by vimpi on 7th december to lower the cost
421
422 l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND
423 STATUS_CODE = :STATUS_CODE';
424 l_ld_list(l_node_counter).NODE_TYPE := 0;
425 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
426 --l_ld_list(l_node_counter).NODE_DEPTH := 2;
427 l_ld_list(l_node_counter).NODE_DEPTH := 3;
428 --added by vimpi on 2nd nov/01
429
430 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
431 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
432
433
434
435 l_node_counter := l_node_counter + 1;
436
437 END LOOP;
438
439
440
441
442
443
444 /* 9/3 commented by swkhanna. Not needed
445 --added by nprasad 06/17/2002 for the new node for leads last updated by specified number of days
446
447 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
448 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
449 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
450
451 -- aug23,2002, added following to get the node label from message so it can be translated
452 fnd_message.set_name('AST','AST_UWQ_OLDER_NODE');
453 fnd_message.set_token('PRIOR_DAYS',l_prior_days);
454 l_node_label := fnd_message.get;
455
456 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
457 --l_ld_list(l_node_counter).NODE_LABEL := 'Older than '|| l_prior_days || ' '||'days';
458 l_ld_list(l_node_counter).VIEW_NAME := 'AST_SALESLEAD_UWQ_V';
459 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_SALESLEAD_UWQ_DS';
460 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
461
462
463 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '||
464 'STATUS_CODE in (Select status_code from as_statuses_b where opp_open_status_flag=''Y'' and lead_flag = ''Y'' and enabled_flag = ''Y'') '||
465 'AND (sysdate - last_update_date) > NVL(fnd_profile.value(''AST_DEFAULT_PRIOR_DAYS''),30)';
466
467 l_ld_list(l_node_counter).NODE_TYPE := 0;
468 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
472 IEU_PUB.ADD_UWQ_NODE_DATA
469 l_ld_list(l_node_counter).NODE_DEPTH := 2;
470 */
471
473 (P_RESOURCE_ID,
474 P_SEL_ENUM_ID,
475 l_ld_list
476 );
477
478
479 EXCEPTION
480 WHEN OTHERS THEN
481 ROLLBACK TO start_lead_enumeration;
482 RAISE;
483
484 END ENUMERATE_LEADS_NODES;
485
486
487 PROCEDURE ENUMERATE_TEAM_LEADS_NODES
488 (P_RESOURCE_ID IN NUMBER
489 ,P_LANGUAGE IN VARCHAR2
490 ,P_SOURCE_LANG IN VARCHAR2
491 ,P_SEL_ENUM_ID IN NUMBER
492 )
493 AS
494
495 l_node_label VARCHAR2(1000);
496 l_ld_list IEU_PUB.EnumeratorDataRecordList;
497
498 l_node_counter NUMBER;
499 l_bind_list IEU_PUB.BindVariableRecordList ;
500 l_prior_days VARCHAR2(10) ;
501
502 -- SOLIN, bug 5094263
503 CURSOR c_get_node_label(c_lookup_type VARCHAR2, c_lookup_code VARCHAR2) IS
504 Select meaning
505 from ast_lookups
506 where lookup_type = c_lookup_type
507 and lookup_code = c_lookup_code;
508 -- SOLIN, end bug 5094263
509
510
511 CURSOR c_lead_nodes IS
512 SELECT status_code, meaning
513 FROM
514 as_statuses_vl
515 WHERE
516 enabled_flag = 'Y' and LEAD_FLAG = 'Y' and OPP_OPEN_STATUS_FLAG = 'Y'
517 ORDER BY 1;
518
519 BEGIN
520
521 /* label, view, and where for main node taken from enum table anyway */
522
523 l_node_counter := 0;
524 l_prior_days := NVL(fnd_profile.value('AST_DEFAULT_PRIOR_DAYS'), '30');
525 SAVEPOINT start_lead_enumeration;
526
527 -- ***************************************
528 -- swkhanna 9/3
529 -- Base Node 'My Leads ( saleateam)' will be defaulted to 30 DAYS
530 -- ***************************************
531
532 -- SOLIN, bug 5094263
533 OPEN c_get_node_label('AST_UWQ_LABELS', 'LEADS_TEAMWORK_CLASS_LABEL');
534 FETCH c_get_node_label INTO l_node_label;
535 CLOSE c_get_node_label;
536 -- SOLIN, end bug 5094263
537
538
539 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
540 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
541 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
542 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_REF_V';
543 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
544 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
545 -- added by vimpi on 7th dec to decrease cost
546 -- l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (sysdate - last_update_date) <= 30';
547
548 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
549 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 30)';
550
551 l_ld_list(l_node_counter).NODE_TYPE := 0;
552 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
553 l_ld_list(l_node_counter).NODE_DEPTH := 1;
554
555 l_node_counter := l_node_counter + 1;
556
557
558 -- ***************************************
559 -- swkhanna 9/3
560 -- Add another level of nodes with time line 30 DAYS
561 -- ****************************************
562 --SAVEPOINT start_lead_enumeration;
563 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
564 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
565 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
566 -- Get Node Label from message
567 fnd_message.set_name('AST','AST_UWQ_30_DAYS');
568 l_node_label := fnd_message.get;
569 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
570 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
571 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
572 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_REF_V';
573 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
574 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
575 --l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| ' (sysdate - last_update_date) <= 30';
576
580 l_ld_list(l_node_counter).NODE_TYPE := 0;
577 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
578 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 30)';
579
581 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
582 l_ld_list(l_node_counter).NODE_DEPTH := 2;
583
584 l_node_counter := l_node_counter + 1;
585 ------------------------------------------------------------------------------------
586 -- Enumerate sub nodes for 30 days. The subnodes are based on the lead statuses
587 ------------------------------------------------------------------------------------
588
589 FOR cur_rec IN c_lead_nodes LOOP
590
591 --added bind var by vimpi on 1rst nov/2001
592 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
593 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
594 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
595
596 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
597 l_bind_list(2).bind_var_value := cur_rec.status_code ;
598 l_bind_list(2).bind_var_data_type := 'CHAR' ;
599
600 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
601 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
602 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
603 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_S_V';
604 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
605 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
606 -- added by vimpi on 7th dec to decrease cost
607 -- l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE ' || ' and (sysdate - last_update_date) <= 30';
608
609 -- 4/29 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
610 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 30)';
611
612 l_ld_list(l_node_counter).NODE_TYPE := 0;
613 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
614 l_ld_list(l_node_counter).NODE_DEPTH := 3;
615
616 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
617 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
618
619 l_node_counter := l_node_counter + 1;
620
621 END LOOP;
622
623 -- ***************************************
624 -- swkhanna 9/3
625 -- Add another level of nodes with time line 90 DAYS
626 -- ****************************************
627 --SAVEPOINT start_lead_enumeration;
628 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
629 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
630 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
631 -- Get Node Label from message
632 fnd_message.set_name('AST','AST_UWQ_90_DAYS');
633 l_node_label := fnd_message.get;
634 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
635 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
636 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
637 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_REF_V';
638 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
639 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
640 --l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| ' (sysdate - last_update_date) <= 90';
641
642 --4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
643 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 90)';
644
645 l_ld_list(l_node_counter).NODE_TYPE := 0;
646 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
647 l_ld_list(l_node_counter).NODE_DEPTH := 2;
648
649 l_node_counter := l_node_counter + 1;
650 ------------------------------------------------------------------------------------
651 -- Enumerate sub nodes for 90 days. The subnodes are based on the lead statuses
652 ------------------------------------------------------------------------------------
653
654
655 FOR cur_rec IN c_lead_nodes LOOP
656
657 --added bind var by vimpi on 1rst nov/2001
658 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
659 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
660 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
661
662 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
663 l_bind_list(2).bind_var_value := cur_rec.status_code ;
664 l_bind_list(2).bind_var_data_type := 'CHAR' ;
665
666 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
670 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
667 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
668 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
669 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_S_V';
671 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
672 -- added by vimpi on 7th dec to decrease cost
673 -- l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE ' || ' and (sysdate - last_update_date) <= 90';
674
675 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
676 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 90)';
677
678 l_ld_list(l_node_counter).NODE_TYPE := 0;
679 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
680 l_ld_list(l_node_counter).NODE_DEPTH := 3;
681
682 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
683 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
684
685 l_node_counter := l_node_counter + 1;
686
687 END LOOP;
688
689 -- ***************************************
690 -- swkhanna 9/3
691 -- Add another level of nodes with time line 180 DAYS
692 -- ****************************************
693 --SAVEPOINT start_lead_enumeration;
694 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
695 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
696 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
697 -- Get Node Label from message
698 fnd_message.set_name('AST','AST_UWQ_6_MNTHS');
699 l_node_label := fnd_message.get;
700 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
701 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
702 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
706 --l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| ' (sysdate - last_update_date) <= 180';
703 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_REF_V';
704 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
705 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
707
708 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
709 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 180)';
710
711 l_ld_list(l_node_counter).NODE_TYPE := 0;
712 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
713 l_ld_list(l_node_counter).NODE_DEPTH := 2;
714
715 l_node_counter := l_node_counter + 1;
716 ------------------------------------------------------------------------------------
717 -- Enumerate sub nodes for 180 days. The subnodes are based on the lead statuses
718 ------------------------------------------------------------------------------------
719
720
721 FOR cur_rec IN c_lead_nodes LOOP
722
723 --added bind var by vimpi on 1rst nov/2001
724 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
725 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
726 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
727
728 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
729 l_bind_list(2).bind_var_value := cur_rec.status_code ;
730 l_bind_list(2).bind_var_data_type := 'CHAR' ;
731
732 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
733 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
734 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
735 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_S_V';
736 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
737 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
738 -- added by vimpi on 7th dec to decrease cost
739 --l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE ' || ' and (sysdate - last_update_date) <= 180';
740
741 -- 4/29/03/25 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
742 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 180)';
743
744 l_ld_list(l_node_counter).NODE_TYPE := 0;
745 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
746 l_ld_list(l_node_counter).NODE_DEPTH := 3;
747
748 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
749 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
750
751 l_node_counter := l_node_counter + 1;
752
753 END LOOP;
754
755
756 -- ***************************************
757 -- swkhanna 9/3
758 -- Add another level of nodes with time line 365 DAYS
759 -- ****************************************
760 --SAVEPOINT start_lead_enumeration;
761 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
762 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
763 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
764 -- Get Node Label from message
765 fnd_message.set_name('AST','AST_UWQ_1_YEAR');
766 l_node_label := fnd_message.get;
767 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
768 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
769 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
770 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_REF_V';
771 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
772 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
776 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND (creation_date >= sysdate - 365)';
773 --l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '|| ' (sysdate - last_update_date) <= 365';
774
775 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
777
778 l_ld_list(l_node_counter).NODE_TYPE := 0;
779 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
780 l_ld_list(l_node_counter).NODE_DEPTH := 2;
781
782 l_node_counter := l_node_counter + 1;
783 ------------------------------------------------------------------------------------
784 -- Enumerate sub nodes for 365 days. The subnodes are based on the lead statuses
785 ------------------------------------------------------------------------------------
786
787
788 FOR cur_rec IN c_lead_nodes LOOP
789
790 --added bind var by vimpi on 1rst nov/2001
791 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
792 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
793 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
794
795 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
796 l_bind_list(2).bind_var_value := cur_rec.status_code ;
797 l_bind_list(2).bind_var_data_type := 'CHAR' ;
798
799 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
800 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
801 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
802 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_S_V';
803 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
804 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
805 -- added by vimpi on 7th dec to decrease cost
806 -- l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE ' || ' and (sysdate - last_update_date) <= 365';
807
808 -- 4/29/03 swkhanna Bug 2877904, reformat the last_update_clause to creation_date
809 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE AND (creation_date >= sysdate - 365)';
810
811 l_ld_list(l_node_counter).NODE_TYPE := 0;
812 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
813 l_ld_list(l_node_counter).NODE_DEPTH := 3;
814
815 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
816 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
817
818 l_node_counter := l_node_counter + 1;
819
820 END LOOP;
821
822
823 -- ***************************************
824 -- swkhanna 9/3
825 -- Add another level of nodes with time line :ALL
826 -- ****************************************
827 --SAVEPOINT start_lead_enumeration;
828 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
829 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
830 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
831 -- Get Node Label from message
832 fnd_message.set_name('AST','AST_UWQ_ALL_LEADS');
833 l_node_label := fnd_message.get;
834 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
835 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
836 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
837 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_REF_V';
838 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
839 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
840 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID ';
841 l_ld_list(l_node_counter).NODE_TYPE := 0;
842 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
843 l_ld_list(l_node_counter).NODE_DEPTH := 2;
844
845 l_node_counter := l_node_counter + 1;
846 ------------------------------------------------------------------------------------
847 -- Enumerate sub nodes for all leads. The subnodes are based on the lead statuses
848 ------------------------------------------------------------------------------------
849
850
851 FOR cur_rec IN c_lead_nodes LOOP
852
853 --added bind var by vimpi on 1rst nov/2001
854 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
855 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
856 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
857
858 l_bind_list(2).bind_var_name := ':STATUS_CODE' ;
859 l_bind_list(2).bind_var_value := cur_rec.status_code ;
860 l_bind_list(2).bind_var_data_type := 'CHAR' ;
861
862 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
863 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
864 --3/21/03 swkhanna added the following line ie. REFRESH_VIEW_NAME for #2831426
865 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_S_V';
866 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
867 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
868 -- added by vimpi on 7th dec to decrease cost
869 l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND STATUS_CODE = :STATUS_CODE ' ;
870 l_ld_list(l_node_counter).NODE_TYPE := 0;
871 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
872 l_ld_list(l_node_counter).NODE_DEPTH := 3;
873
874 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
875 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
876
877 l_node_counter := l_node_counter + 1;
878
879 END LOOP;
880
881
882
883 /* swkhanna 9/5 not needed
887
884 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
885 l_bind_list(1).bind_var_value := P_RESOURCE_ID ;
886 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
888 -- aug23,2002, added following to get the node label from message so it can be translated
889 fnd_message.set_name('AST','AST_UWQ_OLDER_NODE');
890 fnd_message.set_token('PRIOR_DAYS',l_prior_days);
891 l_node_label := fnd_message.get;
892
893 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
894 --l_ld_list(l_node_counter).NODE_LABEL := 'Older than '|| l_prior_days || ' '||'days';
895 l_ld_list(l_node_counter).VIEW_NAME := 'AST_MYTEAM_SALESLEAD_UWQ_V';
896 l_ld_list(l_node_counter).DATA_SOURCE := 'AST_MYTEAM_SALESLEAD_UWQ_DS';
897 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
898
899 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND RESOURCE_ID+0 = :RESOURCE_ID AND '||
900 'STATUS_CODE in (Select status_code from as_statuses_b where opp_open_status_flag=''Y'' and lead_flag = ''Y'' and enabled_flag = ''Y'') '||
901 'AND (sysdate - last_update_date) > NVL(fnd_profile.value(''AST_DEFAULT_PRIOR_DAYS''),30)';
902
903 l_ld_list(l_node_counter).NODE_TYPE := 0;
904 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
905 l_ld_list(l_node_counter).NODE_DEPTH := 2;
906 */
907
908 IEU_PUB.ADD_UWQ_NODE_DATA
909 (P_RESOURCE_ID,
910 P_SEL_ENUM_ID,
911 l_ld_list
912 );
913
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 ROLLBACK TO start_lead_enumeration;
918 RAISE;
919
920 END ENUMERATE_TEAM_LEADS_NODES;
921
922 -- PL/SQL Block
923 END AST_UWQ_LEADS_ENUMS_PVT;