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