eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IEX_UWQ_PROM_ENUMS_PVT

Source


1 PACKAGE BODY IEX_UWQ_PROM_ENUMS_PVT AS
2 /* $Header: iexenprb.pls 120.22.12010000.10 2010/05/17 11:36:11 pnaveenk ship $ */
3 
4 -- Sub-Program Units
5 
6 PG_DEBUG NUMBER(2);
7 
8 PROCEDURE SET_MO_GLOBAL IS
9 L VARCHAR2(240);
10 CURSOR C_ORG_ID IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS
11     WHERE MO_GLOBAL.CHECK_ACCESS(ORGANIZATION_ID) = 'Y';
12 BEGIN
13 
14   MO_GLOBAL.INIT('IEX');
15   MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
16 
17 
18   FOR I_ORG IN C_ORG_ID LOOP
19   MO_GLOBAL.SET_POLICY_CONTEXT('S',I_ORG.ORGANIZATION_ID);
20 
21   L := IEX_UTILITIES.get_cache_value('GL_CURRENCY'||I_ORG.ORGANIZATION_ID,
22      'SELECT  GLSOB.CURRENCY_CODE CURRENCY from GL_SETS_OF_BOOKS GLSOB, AR_SYSTEM_PARAMETERS ARSYS WHERE ARSYS.SET_OF_BOOKS_ID ' ||
23      ' = GLSOB.SET_OF_BOOKS_ID');
24   L := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE'||I_ORG.ORGANIZATION_ID, 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
25   END LOOP;
26 
27   MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
28 
29 END SET_MO_GLOBAL;
30 
31 PROCEDURE ENUMERATE_PROM_NODES
32   (P_RESOURCE_ID      IN NUMBER
33   ,P_LANGUAGE         IN VARCHAR2
34   ,P_SOURCE_LANG      IN VARCHAR2
35   ,P_SEL_ENUM_ID      IN NUMBER
36   )
37   AS
38 
39   l_node_label VARCHAR2(200);
40   l_ld_list  IEU_PUB.EnumeratorDataRecordList;
41 
42   l_node_counter           NUMBER;
43   l_bind_list IEU_PUB.BindVariableRecordList ;
44   l_Access   varchar2(10);
45   l_Level    varchar2(15);
46   l_person_id NUMBER;
47 
48 
49   CURSOR c_person IS
50     select source_id
51     from jtf_rs_resource_extns
52     where resource_id = p_resource_id;
53 
54   CURSOR c_del_new_nodes is
55     select lookup_code, meaning from fnd_lookup_values
56     where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
57 
58   CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
59     SELECT meaning
60     FROM fnd_lookup_values
61     WHERE lookup_type = in_lookup_type
62     AND lookup_code = in_lookup_code
63     AND LANGUAGE = userenv('LANG');
64 
65   CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
66     SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
67     FROM ieu_uwq_sel_enumerators
68     WHERE sel_enum_id = in_sel_enum_id;
69 
70   -- Added for bug 8708271 PNAVEENK
71   CURSOR c_ml_setup IS
72   select DEFINE_PARTY_RUNNING_LEVEL,DEFINE_OU_RUNNING_LEVEL
73   from IEX_QUESTIONNAIRE_ITEMS;
74   -- End for bug 8708271
75   l_sel_enum_rec c_sel_enum%ROWTYPE;
76 
77   l_Complete_Days varchar2(40);
78   l_data_source VARCHAR2(1000);
79   l_default_where VARCHAR2(1000);
80   l_security_where VARCHAR2(2000);
81   l_node_where VARCHAR2(2000);
82   l_uwq_where VARCHAR2(1000);
83  -- l_org_id NUMBER; -- commented for bug 8826561 PNAVEENK
84 
85   type tbl_wclause is table of varchar2(500) index by binary_integer;
86 
87   l_wclause tbl_wclause;
88   l_str1  VARCHAR2(2000);
89   l_str2  VARCHAR2(2000);
90   l_str_and VARCHAR2(100);
91   l_str_prom VARCHAR2(1000);
92   l_bkr_filter VARCHAR2(240);
93   l_str_bkr VARCHAR2(1000);
94   l_check NUMBER(5);
95   -- Begin -jypark- 05/25/05 - 4608220 - Added profile to show/hide UWQ sub-nodes
96   l_EnableNodes   varchar2(10);
97   -- End -jypark- 05/25/05 - 4608220 - Added profile to show/hide UWQ sub-nodes
98   l_party_override varchar2(1); -- Added for bug 8708271 PNAVEENK
99   l_org_override varchar2(1); -- Added for bug 8708271 PNAVEENK
100 BEGIN
101 
102   -- SAVEPOINT start_prom_enumeration;
103   -- Moac Changes start. Set the policy context.
104   MO_GLOBAL.INIT('IEX');
105   MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
106   -- Moac Changes End. Set the policy context.
107 
108   l_Complete_Days  := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
109   l_bkr_filter  := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
110   --l_org_id  := TO_NUMBER(oe_profile.value('OE_ORGANIZATION_ID', fnd_profile.value('ORG_ID'))); -- commented for bug 8826561 PNAVEENK
111   l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
112   l_Level  := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
113 
114   l_node_counter := 0;
115   If (l_Access = 'T') then
116    SELECT count(*) into l_check from iex_assignments where
117    alt_resource_id =  p_RESOURCE_ID
118    AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
119    AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
120    AND NVL(DELETED_FLAG,'N') = 'N';
121   End If;
122 
123   l_str_and  := ' AND ';
124   l_str_prom  := ' AND NUMBER_OF_PROMISES > 0 ';
125 
126 
127   OPEN c_sel_enum(p_sel_enum_id);
128   FETCH c_sel_enum INTO l_sel_enum_rec;
129   CLOSE c_sel_enum;
130 
131   OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
132   FETCH c_node_label INTO l_node_label;
133   CLOSE c_node_label;
134   -- Start for bug 8708271 PNAVEENK
135   open c_ml_setup;
136   fetch c_ml_setup into l_party_override,l_org_override;
137   close c_ml_setup;
138   -- End for bug 8708271
139   --Bug4775893. Fix by LKKUMAR on 14-Dec-2005.  Start.
140   l_wclause(1) :=
141         ' (RESOURCE_ID = :RESOURCE_ID ' ||
142            ' OR  :RESOURCE_ID ' ||
143              ' IN  (select iea.alt_resource_id '||
144              ' FROM  iex_assignments iea ' ||
145              ' WHERE  nvl(iea.deleted_flag,''N'') = ''N'' ' ||
146              ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
147              ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) ' ||
148 	     ' AND iea.alt_resource_id = :RESOURCE_ID ' ||
149 	     ' AND iea.resource_id =  ' ||  l_sel_enum_rec.work_q_view_for_primary_node ||'.RESOURCE_ID )) ' ||
150   	     ' AND ' ||      ' (UWQ_STATUS IS NULL or  UWQ_STATUS = :UWQ_STATUS or ' ||
151              ' (trunc(UWQ_ACTIVE_DATE) <= trunc(SYSDATE) and UWQ_STATUS = ''PENDING'' )) ';
152 
153   l_wclause(2) :=
154         ' (RESOURCE_ID = :RESOURCE_ID  ' ||
155            ' OR  :RESOURCE_ID ' ||
156              ' IN  (select iea.alt_resource_id '||
157              ' FROM  iex_assignments iea ' ||
158              ' WHERE  nvl(iea.deleted_flag,''N'') = ''N'' ' ||
159              ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
160              ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) ' ||
161 	     ' AND iea.alt_resource_id = :RESOURCE_ID ' ||
162 	     ' AND iea.resource_id =  ' ||  l_sel_enum_rec.work_q_view_for_primary_node ||'.RESOURCE_ID )) ' ||
163              ' AND' || ' (UWQ_STATUS = :UWQ_STATUS and (trunc(UWQ_ACTIVE_DATE) > trunc(SYSDATE))) ';
164 
165   l_wclause(3) :=
166         ' (RESOURCE_ID = :RESOURCE_ID '   ||
167            ' OR  :RESOURCE_ID ' ||
168              ' IN  (select iea.alt_resource_id '||
169              ' FROM  iex_assignments iea ' ||
170              ' WHERE  nvl(iea.deleted_flag,''N'') = ''N'' ' ||
171              ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
172              ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) ' ||
173 	     ' AND iea.alt_resource_id = :RESOURCE_ID ' ||
174 	     ' AND iea.resource_id =  ' ||  l_sel_enum_rec.work_q_view_for_primary_node ||'.RESOURCE_ID )) ' ||
175 	     ' AND ' ||
176 	     ' (UWQ_STATUS = :UWQ_STATUS and (trunc(UWQ_COMPLETE_DATE) + ' || l_Complete_Days || ' >  trunc(SYSDATE))) ';
177  --Start for bug#7574861 by PNAVEENK
178  /* If (l_check >0 ) then
179 
180   l_str1   :=
181  ' OR  ' || l_sel_enum_rec.work_q_view_for_primary_node || '.PARTY_ID' || ' IN (SELECT distinct hp.party_id ' ||
182           ' FROM hz_customer_profiles hp,ar_collectors ac, ' ||
183           ' iex_assignments iea ' ||
184           ' WHERE hp.collector_id = ac.collector_id ' ||
185           ' AND ac.resource_id = iea.resource_id ' ||
186           ' AND  iea.alt_employee_id = :PERSON_ID ' ||
187           ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
188           ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) )';
189      l_security_where := l_str1 ;
190   Else
191   l_str1 :=
192   ' OR  ' || l_sel_enum_rec.work_q_view_for_primary_node || '.PARTY_ID' || ' IN (SELECT distinct hp.party_id ' ||
193            ' FROM hz_customer_profiles hp,ar_collectors ac ' ||
194            ' WHERE hp.collector_id = ac.collector_id ' ||
195            ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
196            ' AND ac.employee_id = :PERSON_ID ';
197   l_str2 := ' UNION ALL SELECT hp.party_id ' ||
198           ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
199           ' WHERE hp.collector_id = ac.collector_id ' ||
200           ' AND ac.resource_ID = jtgrp.group_id ' ||
201           ' AND ac.employee_id = :PERSON_ID ' ||
202           ' AND ac.resource_type = ''RS_GROUP'' ' ||
203           ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ) ' ;
204 
205    l_security_where := l_str1 ||l_str2;
206   END IF; */
207   --End for bug#7574861 by PNAVEENK
208   --Bug4775893. Fix by LKKUMAR on 14-Dec-2005. End.
209 
210   -- changed for bug#7693986 by PNAVEENk on 12-1-2009
211    IF p_sel_enum_id = 13069 THEN
212     l_str_bkr := ' AND NOT EXISTS (SELECT 1  FROM iex_bankruptcies bkr WHERE bkr.party_id = ' || l_sel_enum_rec.work_q_view_for_primary_node || '.customer_id and NVL(BKR.DISPOSITION_CODE,''GRANTED'') in (''GRANTED'',''NEGOTIATION'') ) ' ;
213   ELSE
214     l_str_bkr :=  ' AND NOT EXISTS (SELECT 1 FROM iex_bankruptcies bkr WHERE bkr.party_id = ' || l_sel_enum_rec.work_q_view_for_primary_node || '.party_id and NVL(BKR.DISPOSITION_CODE,''GRANTED'') in (''GRANTED'',''NEGOTIATION'') ) ' ;
215   END IF;
216   -- end for bug#7693986
217   IF p_sel_enum_id = 13066 THEN
218     l_data_source := 'IEX_PROMISES_UWQ';
219   END IF;
220 
221   --Bug4775893. Fix by LKKUMAR on 14-Dec-2005. Start.
222   l_default_where := ' ( RESOURCE_ID = :RESOURCE_ID and :UWQ_STATUS = :UWQ_STATUS ' ||
223                      ' OR  :RESOURCE_ID ' ||
224       	             ' IN  (select iea.alt_resource_id '||
225                      ' FROM  iex_assignments iea ' ||
226                      ' WHERE  nvl(iea.deleted_flag,''N'') = ''N'' ' ||
227                      ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
228                      ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) ' ||
229 		     ' AND iea.alt_resource_id = :RESOURCE_ID ' ||
230 		     ' AND iea.resource_id =  ' ||  l_sel_enum_rec.work_q_view_for_primary_node ||'.RESOURCE_ID )) ';
231 --Bug4775893. Fix by LKKUMAR on 14-Dec-2005. End.
232    -- Start for bug 8708271 PNAVEENK
233    if l_party_override = 'Y' or l_org_override ='Y' then
234 
235       l_default_where := l_default_where || ' AND iex_utilities.get_party_running_level (party_id , org_id ) = ''DELINQUENCY'' ';
236    else
237       null;
238    end if;
239    -- End for bug 8708271
240   l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
241   l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
242   l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
243   l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
244 
245   l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
246   l_bind_list(1).bind_var_value := 'ALL';
247   l_bind_list(1).bind_var_data_type := 'CHAR' ;
248 
249 
250   l_bind_list(2).bind_var_name := ':RESOURCE_ID';
251 
252   l_bind_list(2).bind_var_value := p_resource_id;
253 
254   l_bind_list(2).bind_var_data_type := 'NUMBER' ;
255 
256   IF ( l_access in ('F', 'P')) THEN
257     IF l_bkr_filter = 'Y' THEN
258       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_bkr;
259     ELSE
260       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where;
261     END IF;
262 
263     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
264   ELSE
265     OPEN c_person;
266     FETCH c_person INTO l_person_id;
267     CLOSE c_person;
268     -- For bug#7574861 Bind variables commented by PNAVEENK
269    --Commented
270   --  l_bind_list(3).bind_var_name := ':PERSON_ID' ;
271   --  l_bind_list(3).bind_var_value := l_person_id;
272   --  l_bind_list(3).bind_var_data_type := 'NUMBER' ;
273 
274     IF l_bkr_filter = 'Y' THEN
275       l_ld_list(l_node_counter).WHERE_CLAUSE := '( ' ||  l_default_where  || ' ) ' || l_str_bkr; --Changed for bug#7574861 by PNAVEENK
276     ELSE
277       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where;   --Changed for bug#7574861 by PNAVEENK
278     END IF;
279 
280     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
281   END IF;
282 
283   l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
284   l_ld_list(l_node_counter).NODE_TYPE := 0;
285   l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
286   l_ld_list(l_node_counter).NODE_DEPTH := 1;
287 
288 
289   l_node_counter := l_node_counter + 1;
290 
291   -- Begin -jypark- 05/25/05 - 4608220 - Added profile to show/hide UWQ sub-nodes
292   l_EnableNodes :=  NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
293 
294   if (l_EnableNodes <> 'N') then
295   -- End -jypark- 05/25/05 - 4608220 - Added profile to show/hide UWQ sub-nodes
296 
297   FOR cur_rec IN c_del_new_nodes LOOP
298     IF (cur_rec.lookup_code = 'ACTIVE') THEN
299       l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
300       l_bind_list(1).bind_var_value := 'ACTIVE';
301       l_bind_list(1).bind_var_data_type := 'CHAR' ;
302       l_uwq_where := l_wclause(1);
303       l_node_where := l_default_where || ' AND ACTIVE_PROMISES > 0 ';
304     ELSIF (cur_rec.lookup_code = 'PENDING') THEN
305       l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
306       l_bind_list(1).bind_var_value := 'PENDING';
307       l_bind_list(1).bind_var_data_type := 'CHAR' ;
308       l_uwq_where := l_wclause(2);
309       l_node_where := l_default_where || ' AND PENDING_PROMISES > 0 ';
310     ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
311       l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
312       l_bind_list(1).bind_var_value := 'COMPLETE';
313       l_bind_list(1).bind_var_data_type := 'CHAR' ;
314       l_uwq_where := l_wclause(3);
315       l_node_where := l_default_where || ' AND COMPLETE_PROMISES > 0 ';
316     END IF;
317 
318     l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
319     l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
320     l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
321 
322     l_bind_list(2).bind_var_name := ':RESOURCE_ID' ;
323 
324     l_bind_list(2).bind_var_value := p_resource_id;
325 
326     l_bind_list(2).bind_var_data_type := 'NUMBER' ;
327 
328     IF ( l_access in ('P', 'F')) THEN
329       IF l_bkr_filter = 'Y' THEN
330         l_ld_list(l_node_counter).WHERE_CLAUSE :=  l_uwq_where || l_str_bkr;
331       ELSE
332         l_ld_list(l_node_counter).WHERE_CLAUSE :=  l_uwq_where;
333       END IF;
334 
335       l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
336     ELSE
337       IF l_bkr_filter = 'Y' THEN
338         l_ld_list(l_node_counter).WHERE_CLAUSE := ' ( ' ||  l_uwq_where ||  ' ) ' || l_str_bkr; --Changed for bug#7574861 by PNAVEENK
339       ELSE
340         l_ld_list(l_node_counter).WHERE_CLAUSE :=  l_uwq_where; --Changed for bug#7574861 by PNAVEENK
341       END IF;
342      --Commented for bug#7574861 by PNAVEENK
343      -- l_bind_list(3).bind_var_name := ':PERSON_ID' ;
344      -- l_bind_list(3).bind_var_value := l_person_id;
345      -- l_bind_list(3).bind_var_data_type := 'NUMBER' ;
346       l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
347     END IF;
348 
349     l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
350     l_ld_list(l_node_counter).NODE_TYPE := 0;
351     l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
352     l_ld_list(l_node_counter).NODE_DEPTH := 2;
353 
354     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
355 
356     l_node_counter := l_node_counter + 1;
357   END LOOP;
358   -- Begin -jypark- 05/25/05 - 4608220 - Added profile to show/hide UWQ sub-nodes
359   end if;
360   -- End -jypark- 05/25/05 - 4608220 - Added profile to show/hide UWQ sub-nodes
361 
362 
363   IEU_PUB.ADD_UWQ_NODE_DATA
364   (P_RESOURCE_ID,
365    P_SEL_ENUM_ID,
366    l_ld_list
367   );
368 
369 
370 EXCEPTION
371   WHEN OTHERS THEN
372     RAISE;
373 
374 END ENUMERATE_PROM_NODES;
375 
376 PROCEDURE ENUMERATE_CU_PROM_NODES
377   (P_RESOURCE_ID      IN NUMBER
378   ,P_LANGUAGE         IN VARCHAR2
379   ,P_SOURCE_LANG      IN VARCHAR2
380   ,P_SEL_ENUM_ID      IN NUMBER
381   )
382   AS
383 
384   l_node_label VARCHAR2(200);
385   l_ld_list  IEU_PUB.EnumeratorDataRecordList;
386 
387   l_node_counter           NUMBER;
388   l_bind_list IEU_PUB.BindVariableRecordList ;
389   l_Access   varchar2(10);
390   l_Level    varchar2(15);
391 
392 
393   CURSOR c_del_new_nodes is
394     select lookup_code, meaning from fnd_lookup_values
395     where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
396 
397   CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
398     SELECT meaning
399     FROM fnd_lookup_values
400     WHERE lookup_type = in_lookup_type
401     AND lookup_code = in_lookup_code
402     AND LANGUAGE = userenv('LANG');
403 
404   CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
405     SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
406     FROM ieu_uwq_sel_enumerators
407     WHERE sel_enum_id = in_sel_enum_id;
408 
409   l_sel_enum_rec c_sel_enum%ROWTYPE;
410 
411   l_Complete_Days varchar2(40);
412   l_data_source VARCHAR2(1000);
413   l_default_where VARCHAR2(1000);
414   l_security_where VARCHAR2(2000);
415   l_node_where VARCHAR2(2000);
416   l_uwq_where VARCHAR2(1000);
417 -- l_org_id NUMBER; -- commented for bug 8826561 PNAVEENK
418 
419   type tbl_wclause is table of varchar2(500) index by binary_integer;
420 
421   l_wclause tbl_wclause;
422   l_str1  VARCHAR2(2000);
423   l_str_and VARCHAR2(100);
424   l_str_or  VARCHAR2(10);
425   l_str_prom VARCHAR2(1000);
426   l_bkr_filter VARCHAR2(240);
427   l_str_bkr VARCHAR2(1000);
428   l_view_name VARCHAR2(240);
429   l_refresh_view_name VARCHAR2(240);
430   l_check NUMBER(5);
431 
432   l_EnableNodes   varchar2(10);
433 
434   l_collector_id number;
435 
436     CURSOR c_collector_id IS
437         SELECT collector_id from AR_COLLECTORS where resource_id = p_resource_id
438           and resource_type = 'RS_RESOURCE';
439 
440     CURSOR c_person IS
441         select source_id
442         from jtf_rs_resource_extns
443         where resource_id = p_resource_id;
444 
445   l_person_id number;
446 
447     CURSOR c_strategy_level IS
448       SELECT PREFERENCE_VALUE
449   	FROM IEX_APP_PREFERENCES_B
450       WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
451       and org_id is null
452       and enabled_flag='Y';
453 
454   l_strategy_level VARCHAR2(30);
455   l_temp_str	     varchar2(5);  -- Added for bug#8537638 PNAVEENK 11-8-2009
456     --begin bug#6717849 schekuri 31-Jul-2009
457   l_level_count number;
458   cursor c_multi_level
459   is select lookup_code
460   from iex_lookups_v
461   where lookup_type='IEX_RUNNING_LEVEL'
462   and lookup_code= 'CUSTOMER'
463   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
464   --end bug#6717849 schekuri 31-Jul-2009
465 
466 BEGIN
467 
468   SET_MO_GLOBAL;
469 
470   l_Complete_Days  := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
471   l_bkr_filter  := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
472  -- l_org_id  := TO_NUMBER(oe_profile.value('OE_ORGANIZATION_ID', fnd_profile.value('ORG_ID'))); -- commented for bug 8826561 PNAVEENK
473   l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
474   l_Level  := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
475 
476  /* IF (l_Access = 'T') THEN
477    SELECT count(*) INTO l_check FROM iex_assignments where
478    alt_resource_id = p_RESOURCE_ID
479    AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
480    AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
481    AND NVL(DELETED_FLAG,'N') = 'N';
482   END IF;
483 
484 
485   l_str_and  := ' AND ';
486   l_str_prom := ' AND NUMBER_OF_PROMISES > 0 ';
487   l_str_or := ' OR ';
488   */
489   l_node_counter := 0;
490 
491 -- !!
492 -- !!!!!!!!!! BUILDING MAIN NODE WHERE CLAUSE
493 -- !!
494 
495   OPEN c_sel_enum(p_sel_enum_id);
496   FETCH c_sel_enum INTO l_sel_enum_rec;
497   CLOSE c_sel_enum;
498 
499   OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
500   FETCH c_node_label INTO l_node_label;
501   CLOSE c_node_label;
502 
503 -- Start bug 5874874 gnramasa 25-Apr-07
504   l_data_source := 'IEX_CU_PRO_ALL_UWQ';
505   l_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
506   l_refresh_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
507 
508 /*
509   l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
510     IF (l_check  >0 ) THEN
511        l_str1   := ASSIGNMENTS_WHERE_CLAUSE('PARTY', l_view_name) || ' AND ' || RESOURCES_WHERE_CLAUSE('PARTY', l_view_name) || ' ) ';
512     ELSE
513        l_str1   :=  RESOURCES_WHERE_CLAUSE('PARTY', l_view_name);
514     END IF;
515 
516   l_security_where := l_str1 ;
517   l_default_where := ' ( RESOURCE_ID = :RESOURCE_ID OR'  || ASSIGNMENTS_WHERE_CLAUSE('PARTY', l_view_name) || ' ) ';
518 
519   l_bind_list(1).bind_var_name := ':RESOURCE_ID';
520   l_bind_list(1).bind_var_value := 1;
521   l_bind_list(1).bind_var_data_type := 'NUMBER' ;
522 
523   --Begin - Bug#5344878 - Andre Araujo - Need to add bind variables
524   l_bind_list(2).bind_var_name := ':REAL_RESOURCE_ID';
525   l_bind_list(2).bind_var_value := p_resource_id;
526   l_bind_list(2).bind_var_data_type := 'NUMBER' ;
527   --End - Bug#5344878 - Andre Araujo - Need to add bind variables
528 
529   IF ( l_access in ('F', 'P')) THEN
530     IF l_bkr_filter = 'Y' THEN
531       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_prom || l_str_bkr;
532     ELSE
533       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_prom;
534     END IF;
535 
536     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
537   ELSE
538 
539      -- No count view when the security is enabled
540     l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
541     OPEN c_person;
542     FETCH c_person INTO l_person_id;
543     CLOSE c_person;
544 
545 
546     l_bind_list(3).bind_var_name := ':PERSON_ID' ;
547     l_bind_list(3).bind_var_value := l_person_id;
548     l_bind_list(3).bind_var_data_type := 'NUMBER' ;
549 
550     IF l_bkr_filter = 'Y' THEN
551       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_prom || l_str_bkr;
552     ELSE
553       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_prom;
554     END IF;
555 
556     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
557   END IF;
558   */
559   OPEN c_strategy_level;
560   FETCH c_strategy_level INTO l_strategy_level;
561   CLOSE c_strategy_level;
562 
563       --begin bug#6717849 schekuri 31-Jul-2009
564   select count(1)
565   into l_level_count
566   from iex_lookups_v
567   where lookup_type='IEX_RUNNING_LEVEL'
568   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
569 
570   if l_level_count>1 then
571   open c_multi_level;
572   fetch c_multi_level into l_strategy_level;
573   close c_multi_level;
574   end if;
575   --end bug#6717849 schekuri 31-Jul-2009
576 
577  IF l_strategy_level = 'CUSTOMER' THEN
578 
579       l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
580       l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
581       l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
582       l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
583       l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
584 
585    --   l_default_where :=  ' IEU_PARAM_PK_COL=''PARTY_ID'' and '||attach_where_clause(p_resource_id); --bug#6717849 schekuri 31-Jul-2009
586         l_default_where :=  ' IEU_PARAM_PK_COL=''PARTY_ID'' ' ||attach_where_clause(p_resource_id); -- bug 8537638 PNAVEENK 11-8-2009
587       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where;
588 
589       IF (l_access = 'T' ) THEN
590         OPEN c_collector_id;
591         FETCH c_collector_id INTO l_collector_id;
592         CLOSE c_collector_id;
593 
594         OPEN c_person;
595         FETCH c_person INTO l_person_id;
596         CLOSE c_person;
597 
598         l_bind_list(1).bind_var_name := ':PERSON_ID' ;
599         l_bind_list(1).bind_var_value := l_person_id;
600         l_bind_list(1).bind_var_data_type := 'NUMBER' ;
601 
602         l_bind_list(2).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
603         l_bind_list(2).bind_var_value := p_resource_id;
604         l_bind_list(2).bind_var_data_type := 'NUMBER' ;
605 
606         l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
607       ELSE    -- bug 9453423 PNAVEENK
608        IF l_default_where IS NULL THEN
609           l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID';
610 
611 	  l_bind_list(1).bind_var_name := ':RESOURCE_ID';
612           l_bind_list(1).bind_var_value := 1;
613           l_bind_list(1).bind_var_data_type := 'NUMBER' ;
614 
615           l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
616        END IF;
617       END IF;
618    ELSE  /* IF l_strategy_level <> 'CUSTOMER' */
619 
620       l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
621       l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
622       l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
623       l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
624 
625       l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
626 
627       l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
628       l_bind_list(1).bind_var_value := 'ALL';
629       l_bind_list(1).bind_var_data_type := 'CHAR' ;
630 
631       l_bind_list(2).bind_var_name := ':RESOURCE_ID';
632       l_bind_list(2).bind_var_value := -1;
633       l_bind_list(2).bind_var_data_type := 'NUMBER' ;
634 
635       l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
636    END IF;
637 -- End bug 5874874 gnramasa 25-Apr-07
638   l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
639   l_ld_list(l_node_counter).NODE_TYPE := 0;
640   l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
641   l_ld_list(l_node_counter).NODE_DEPTH := 1;
642 
643 
644   IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
645      iex_debug_pub.LogMessage('ENUMERATE_CU_PROM_NODES: main final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
646   END IF;
647 
648   l_node_counter := l_node_counter + 1;
649   l_EnableNodes :=  NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
650 
651   if (l_EnableNodes <> 'N') then
652     l_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
653     l_refresh_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
654   -- Start for bug 8537638 PNAVEENK 11-8-2009
655    if l_ld_list(0).WHERE_CLAUSE is not null then
656 	l_temp_str := ' and ';
657       else
658 	l_temp_str := ' ';
659       end if;
660 
661   FOR cur_rec IN c_del_new_nodes LOOP
662     IF l_strategy_level = 'CUSTOMER' THEN
663       IF (cur_rec.lookup_code = 'ACTIVE') THEN
664         l_data_source := 'IEX_CU_PRO_ACT_UWQ';
665         --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and active_promises is not null';
666 	--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(active_promises,0)  > 0 ';
667         l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(active_promises,0)  > 0 ';
668       ELSIF (cur_rec.lookup_code = 'PENDING') THEN
669         l_data_source := 'IEX_CU_PRO_PEND_UWQ';
670         --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and pending_promises is not null';
671 	--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(pending_promises,0)  > 0 ';
672          l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(pending_promises,0)  > 0 ';
673       ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
674         l_data_source := 'IEX_CU_PRO_COMP_UWQ';
675         --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and complete_promises is not null';
676 	--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(complete_promises,0)  > 0 ';
677           l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(complete_promises,0)  > 0 ';
678       END IF;
679      -- end for bug 8537638
680 
681     l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
682     l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
683     l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
684     l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
685     l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where;
686     l_ld_list(l_node_counter).BIND_VARS    := l_ld_list(0).BIND_VARS;
687   ELSE  /* IF l_strategy_level <> 'CUSTOMER' */
688 
689     l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
690     l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
691     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
692     l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
693 
694     l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
695 
696     l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
697     l_bind_list(1).bind_var_value := 'ALL';
698     l_bind_list(1).bind_var_data_type := 'CHAR' ;
699 
700     l_bind_list(2).bind_var_name := ':RESOURCE_ID';
701     l_bind_list(2).bind_var_value := -1;
702     l_bind_list(2).bind_var_data_type := 'NUMBER' ;
703 
704     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
705   END IF;
706 
707     l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
708     l_ld_list(l_node_counter).NODE_TYPE := 0;
709     l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
710     l_ld_list(l_node_counter).NODE_DEPTH := 2;
711 
712     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
713 
714     IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
715 	    iex_debug_pub.LogMessage('ENUMERATE_CU_PROM_NODES: Subnode final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
716     END IF;
717 
718 
719     l_node_counter := l_node_counter + 1;
720   END LOOP;
721   END IF;
722 
723   IEU_PUB.ADD_UWQ_NODE_DATA
724   (P_RESOURCE_ID,
725    P_SEL_ENUM_ID,
726    l_ld_list
727   );
728 
729 
730 EXCEPTION
731   WHEN OTHERS THEN
732     if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
733        FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'iex',  'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM );
734     End if;
735     RAISE;
736 
737 END ENUMERATE_CU_PROM_NODES;
738 
739 
740 PROCEDURE ENUMERATE_ACC_PROM_NODES
741   (P_RESOURCE_ID      IN NUMBER
742   ,P_LANGUAGE         IN VARCHAR2
743   ,P_SOURCE_LANG      IN VARCHAR2
744   ,P_SEL_ENUM_ID      IN NUMBER
745   )
746   AS
747 
748   l_node_label VARCHAR2(200);
749   l_ld_list  IEU_PUB.EnumeratorDataRecordList;
750 
751   l_node_counter           NUMBER;
752   l_bind_list IEU_PUB.BindVariableRecordList ;
753   l_Access   varchar2(10);
754   l_Level    varchar2(15);
755   l_person_id NUMBER;
756   l_check    NUMBER;
757 
758   CURSOR c_del_new_nodes is
759     select lookup_code, meaning from fnd_lookup_values
760     where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
761 
762   CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
763     SELECT meaning
764     FROM fnd_lookup_values
765     WHERE lookup_type = in_lookup_type
766     AND lookup_code = in_lookup_code
767     AND LANGUAGE = userenv('LANG');
768 
769   CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
770     SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
771     FROM ieu_uwq_sel_enumerators
772     WHERE sel_enum_id = in_sel_enum_id;
773 
774   l_sel_enum_rec c_sel_enum%ROWTYPE;
775 
776   l_Complete_Days varchar2(40);
777   l_data_source VARCHAR2(1000);
778   l_default_where VARCHAR2(1000);
779   l_security_where VARCHAR2(2000);
780   l_node_where VARCHAR2(2000);
781   l_uwq_where VARCHAR2(1000);
782  -- l_org_id NUMBER;  -- commented for bug 8826561 PNAVEENK
783 
784   type tbl_wclause is table of varchar2(500) index by binary_integer;
785 
786   l_wclause tbl_wclause;
787   l_str1  VARCHAR2(2000);
788   l_str_and VARCHAR2(100);
789   l_str_prom VARCHAR2(1000);
790   l_bkr_filter VARCHAR2(240);
791   l_str_bkr VARCHAR2(1000);
792   l_view_name VARCHAR2(240);
793   l_refresh_view_name VARCHAR2(240);
794   l_EnableNodes   varchar2(10);
795 
796   l_collector_id number;
797   l_resource_id number;
798 
799   CURSOR c_collector_id IS
800       SELECT collector_id from AR_COLLECTORS where resource_id = p_resource_id
801         and resource_type = 'RS_RESOURCE';
802 
803   CURSOR c_person IS
804       select source_id
805       from jtf_rs_resource_extns
806       where resource_id = p_resource_id;
807 
808   CURSOR c_strategy_level IS
809     SELECT PREFERENCE_VALUE
810     FROM IEX_APP_PREFERENCES_B
811     WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
812     and org_id is null
813     and enabled_flag='Y';
814 
815   l_strategy_level VARCHAR2(30);
816   l_temp_str	     varchar2(5); -- Added for bug 8537638 PNAVEENK 11-8-2009
817     --begin bug#6717849 schekuri 31-Jul-2009
818   l_level_count number;
819   cursor c_multi_level
820   is select lookup_code
821   from iex_lookups_v
822   where lookup_type='IEX_RUNNING_LEVEL'
823   and lookup_code= 'ACCOUNT'
824   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
825   --end bug#6717849 schekuri 31-Jul-2009
826 
827 BEGIN
828 
829   SET_MO_GLOBAL;
830 
831   l_Complete_Days  := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
832   l_bkr_filter  := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
833  -- l_org_id  := TO_NUMBER(oe_profile.value('OE_ORGANIZATION_ID', fnd_profile.value('ORG_ID'))); -- commented for bug 8826561 PNAVEENK
834 
835   l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
836   /*
837   l_Level  := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'), 'PARTY');
838 
839   -- !!
840   -- !!!!!!!!!! BUILDING MAIN NODE WHERE CLAUSE
841   -- !!
842 
843   l_str_and  := ' AND ';
844   l_str_prom  := ' AND NUMBER_OF_PROMISES > 0 ';
845   */
846   l_node_counter := 0;
847 
848   OPEN c_sel_enum(p_sel_enum_id);
849   FETCH c_sel_enum INTO l_sel_enum_rec;
850   CLOSE c_sel_enum;
851 
852   OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
853   FETCH c_node_label INTO l_node_label;
854   CLOSE c_node_label;
855   -- Start bug 5874874 gnramasa 25-Apr-07
856   l_data_source := 'IEX_ACC_PRO_ALL_UWQ';
857   l_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
858   l_refresh_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
859 
860   /*
861   l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
862 
863 
864   IF (l_Access = 'T') then
865    SELECT count(*) INTO l_check FROM iex_assignments where
866    alt_resource_id =  P_RESOURCE_ID
867    AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
868    AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
869    AND NVL(DELETED_FLAG,'N') = 'N';
870   END IF;
871 
872   IF (l_Level = 'PARTY') then
873       If (l_check > 0) then
874        l_str1   := ASSIGNMENTS_WHERE_CLAUSE('PARTY', l_sel_enum_rec.work_q_view_for_primary_node) || ' AND ' || RESOURCES_WHERE_CLAUSE('PARTY', l_sel_enum_rec.work_q_view_for_primary_node) || ' ) ';
875       Else
876        l_str1   :=  RESOURCES_WHERE_CLAUSE('PARTY', l_sel_enum_rec.work_q_view_for_primary_node);
877       End If;
878   ELSE
879       If (l_check > 0) then
880        l_str1   := ASSIGNMENTS_WHERE_CLAUSE('ACCOUNT', l_sel_enum_rec.work_q_view_for_primary_node) || ' AND ' || RESOURCES_WHERE_CLAUSE('ACCOUNT', l_sel_enum_rec.work_q_view_for_primary_node) || ' ) ';
881       Else
882        l_str1   :=  RESOURCES_WHERE_CLAUSE('ACCOUNT', l_sel_enum_rec.work_q_view_for_primary_node);
883       End If;
884    END IF;
885 
886    l_security_where := l_str1;
887 
888    l_default_where := ' ( RESOURCE_ID = :RESOURCE_ID OR'  || ASSIGNMENTS_WHERE_CLAUSE('ACCOUNT', l_view_name) || ' ) ';
889 
890 
891   l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
892   l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
893   l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
894   l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
895   l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
896 
897   l_bind_list(1).bind_var_name := ':RESOURCE_ID';
898   l_bind_list(1).bind_var_value := 1;
899   l_bind_list(1).bind_var_data_type := 'NUMBER' ;
900 
901 
902   l_bind_list(2).bind_var_name := ':REAL_RESOURCE_ID';
903   l_bind_list(2).bind_var_value := p_resource_id;
904   l_bind_list(2).bind_var_data_type := 'NUMBER' ;
905 
906 
907   IF ( l_access in ('F', 'P')) THEN
908     IF l_bkr_filter = 'Y' THEN
909       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_prom || l_str_bkr;
910     ELSE
911       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_prom;
912     END IF;
913 
914     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
915   ELSE
916     -- No count view when the security is enabled
917     l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
918 
919     OPEN c_person;
920     FETCH c_person INTO l_person_id;
921     CLOSE c_person;
922 
923     l_bind_list(3).bind_var_name := ':PERSON_ID' ;
924     l_bind_list(3).bind_var_value := l_person_id;
925     l_bind_list(3).bind_var_data_type := 'NUMBER' ;
926 
927     IF l_bkr_filter = 'Y' THEN
928       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_prom || l_str_bkr;
929     ELSE
930       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_prom;
931     END IF;
932 
933     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
934   END IF;
935   */
936     OPEN c_strategy_level;
937     FETCH c_strategy_level INTO l_strategy_level;
938     CLOSE c_strategy_level;
939 
940         --begin bug#6717849 schekuri 31-Jul-2009
941   select count(1)
942   into l_level_count
943   from iex_lookups_v
944   where lookup_type='IEX_RUNNING_LEVEL'
945   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
946 
947   if l_level_count>1 then
948   open c_multi_level;
949   fetch c_multi_level into l_strategy_level;
950   close c_multi_level;
951   end if;
952   --end bug#6717849 schekuri 31-Jul-2009
953 
954     IF l_strategy_level = 'ACCOUNT' THEN
955 
956       l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
957       l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
958       l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
959       l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
960       l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
961 
962     --  l_default_where := ' IEU_PARAM_PK_COL=''CUST_ACCOUNT_ID'' and '||attach_where_clause(p_resource_id); --bug#6717849 schekuri 31-Jul-2009
963         l_default_where := ' IEU_PARAM_PK_COL=''CUST_ACCOUNT_ID'' ' ||attach_where_clause(p_resource_id); -- bug 8537638 PNAVEENK 11-8-2009
964       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where;
965 
966       IF (l_access = 'T' ) THEN
967         OPEN c_collector_id;
968         FETCH c_collector_id INTO l_collector_id;
969         CLOSE c_collector_id;
970 
971         OPEN c_person;
972         FETCH c_person INTO l_person_id;
973         CLOSE c_person;
974 
975         l_bind_list(1).bind_var_name := ':PERSON_ID' ;
976         l_bind_list(1).bind_var_value := l_person_id;
977         l_bind_list(1).bind_var_data_type := 'NUMBER' ;
978 
979         -- Begin fix bug #5660900-12/16/2006-Use resource_id instead of collector_id because employee is assigned to more 2 or more collectors
980         -- l_bind_list(2).bind_var_name := ':COLLECTOR_ID' ;
981         -- l_bind_list(2).bind_var_value := l_collector_id;
982         -- l_bind_list(2).bind_var_data_type := 'NUMBER' ;
983 
984         l_bind_list(2).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
985         l_bind_list(2).bind_var_value := p_resource_id;
986         l_bind_list(2).bind_var_data_type := 'NUMBER' ;
987 
988         -- End fix bug #5660900-12/16/2006-Use resource_id instead of collector_id because employee is assigned to more 2 or more collectors
989         l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
990        ELSE    -- bug 9453423 PNAVEENK
991         IF l_default_where IS NULL THEN
992           l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID';
993 
994 	  l_bind_list(1).bind_var_name := ':RESOURCE_ID';
995           l_bind_list(1).bind_var_value := 1;
996           l_bind_list(1).bind_var_data_type := 'NUMBER' ;
997 
998           l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
999         END IF;
1000        END IF;
1001 
1002     ELSE  /* IF l_strategy_level <> 'ACCOUNT' */
1003 
1004       l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
1005       l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
1006       l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1007       l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1008 
1009       l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
1010 
1011       l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
1012       l_bind_list(1).bind_var_value := 'ALL';
1013       l_bind_list(1).bind_var_data_type := 'CHAR' ;
1014 
1015       l_bind_list(2).bind_var_name := ':RESOURCE_ID';
1016       l_bind_list(2).bind_var_value := -1;
1017       l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1018 
1019       l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1020     END IF;
1021     -- End bug 5874874 gnramasa 25-Apr-07
1022   l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1023   l_ld_list(l_node_counter).NODE_TYPE := 0;
1024   l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1025   l_ld_list(l_node_counter).NODE_DEPTH := 1;
1026 
1027 
1028   IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1029      iex_debug_pub.LogMessage('ENUMERATE_ACC_PROM_NODES: main final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
1030   END IF;
1031 
1032 
1033   -- !!
1034   -- !!!!!!!!!! BUILDING SUB NODES WHERE CLAUSE
1035   -- !!
1036 
1037   l_node_counter := l_node_counter + 1;
1038 
1039 
1040   l_EnableNodes :=  NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
1041 
1042   if (l_EnableNodes <> 'N') then
1043   l_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
1044   l_refresh_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
1045 
1046   -- Start for bug 8537638 PNAVEENK 11-8-2009
1047   if l_ld_list(0).WHERE_CLAUSE is not null then
1048 	l_temp_str := ' and ';
1049     else
1050 	l_temp_str := ' ';
1051     end if;
1052 
1053   FOR cur_rec IN c_del_new_nodes LOOP
1054    IF l_strategy_level = 'ACCOUNT' THEN
1055 	IF (cur_rec.lookup_code = 'ACTIVE') THEN
1056 		l_data_source := 'IEX_ACC_PRO_ACT_UWQ';
1057 		--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and active_promises is not null';
1058 		--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(active_promises,0)  > 0 ';
1059 	          l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(active_promises,0)  > 0 ';
1060 	ELSIF (cur_rec.lookup_code = 'PENDING') THEN
1061 		l_data_source := 'IEX_ACC_PRO_PEND_UWQ';
1062 		--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and pending_promises is not null';
1063 		--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(pending_promises,0)  > 0 ';
1064 	        l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(pending_promises,0)  > 0 ';
1065 	ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
1066 		l_data_source := 'IEX_ACC_PRO_COMP_UWQ';
1067 		--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and complete_promises is not null';
1068 		--l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(complete_promises,0) > 0 ';
1069 	        l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(complete_promises,0) > 0 ';
1070 	END IF;
1071         -- end for bug 8537638
1072 
1073 	l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1074 	l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1075 	l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1076 	l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1077 
1078 
1079 	l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where;
1080 	l_ld_list(l_node_counter).BIND_VARS    := l_ld_list(0).BIND_VARS;
1081    ELSE  /* IF l_strategy_level <> 'ACCOUNT' */
1082 
1083         l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1084         l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1085         l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1086         l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1087 
1088         l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
1089 
1090         l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
1091         l_bind_list(1).bind_var_value := 'ALL';
1092         l_bind_list(1).bind_var_data_type := 'CHAR' ;
1093 
1094         l_bind_list(2).bind_var_name := ':RESOURCE_ID';
1095         l_bind_list(2).bind_var_value := -1;
1096         l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1097 
1098         l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1099    END IF;
1100 
1101     l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1102     l_ld_list(l_node_counter).NODE_TYPE := 0;
1103     l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1104     l_ld_list(l_node_counter).NODE_DEPTH := 2;
1105 
1106     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1107 
1108     IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1109 	    iex_debug_pub.LogMessage('ENUMERATE_ACC_PROM_NODES: Subnode final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
1110     END IF;
1111 
1112     l_node_counter := l_node_counter + 1;
1113   END LOOP;
1114 
1115   END IF;
1116 
1117 
1118 
1119   IEU_PUB.ADD_UWQ_NODE_DATA
1120   (P_RESOURCE_ID,
1121    P_SEL_ENUM_ID,
1122    l_ld_list
1123   );
1124 
1125 
1126 EXCEPTION
1127   WHEN OTHERS THEN
1128     if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1129        FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'iex',  'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM );
1130     End if;
1131     RAISE;
1132 
1133 END ENUMERATE_ACC_PROM_NODES;
1134 
1135 PROCEDURE ENUMERATE_BILLTO_PROM_NODES
1136   (P_RESOURCE_ID      IN NUMBER
1137   ,P_LANGUAGE         IN VARCHAR2
1138   ,P_SOURCE_LANG      IN VARCHAR2
1139   ,P_SEL_ENUM_ID      IN NUMBER
1140   )
1141   AS
1142 
1143   l_node_label VARCHAR2(200);
1144   l_ld_list  IEU_PUB.EnumeratorDataRecordList;
1145 
1146   l_node_counter           NUMBER;
1147   l_bind_list IEU_PUB.BindVariableRecordList ;
1148   l_Access   varchar2(10);
1149   l_Level    varchar2(15);
1150   l_person_id NUMBER;
1151 
1152 
1153   CURSOR c_del_new_nodes is
1154     select lookup_code, meaning from fnd_lookup_values
1155     where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
1156 
1157   CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
1158     SELECT meaning
1159     FROM fnd_lookup_values
1160     WHERE lookup_type = in_lookup_type
1161     AND lookup_code = in_lookup_code
1162     AND LANGUAGE = userenv('LANG');
1163 
1164   CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
1165     SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
1166     FROM ieu_uwq_sel_enumerators
1167     WHERE sel_enum_id = in_sel_enum_id;
1168 
1169   l_sel_enum_rec c_sel_enum%ROWTYPE;
1170 
1171   l_data_source VARCHAR2(1000);
1172   l_default_where VARCHAR2(1000);
1173   l_security_where VARCHAR2(2000);
1174   l_node_where VARCHAR2(2000);
1175   l_uwq_where VARCHAR2(1000);
1176 
1177   type tbl_wclause is table of varchar2(500) index by binary_integer;
1178 
1179   l_wclause tbl_wclause;
1180   l_str_bkr VARCHAR2(1000);
1181   l_view_name VARCHAR2(240);
1182   l_refresh_view_name VARCHAR2(240);
1183   --l_org_id NUMBER; -- commented for bug 8826561 PNAVEENK
1184   l_Complete_Days VARCHAR2(40);
1185   l_bkr_filter VARCHAR2(240);
1186   l_str1  VARCHAR2(2000);
1187   l_str_and VARCHAR2(100);
1188   l_str_prom VARCHAR2(1000);
1189   l_check NUMBER;
1190   l_EnableNodes   varchar2(10);
1191   l_collector_id number;
1192   l_resource_id number;
1193 
1194   CURSOR c_collector_id IS
1195       SELECT collector_id from AR_COLLECTORS where resource_id = p_resource_id
1196         and resource_type = 'RS_RESOURCE';
1197 
1198   CURSOR c_person IS
1199       select source_id
1200       from jtf_rs_resource_extns
1201       where resource_id = p_resource_id;
1202 
1203   CURSOR c_strategy_level IS
1204     SELECT PREFERENCE_VALUE
1205 	FROM IEX_APP_PREFERENCES_B
1206     WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
1207     and org_id is null
1208     and enabled_flag='Y';
1209 
1210   l_strategy_level VARCHAR2(30);
1211   l_temp_str	   varchar2(5);  -- Added for bug 8537638 PNAVEENK 11-8-2009
1212     --begin bug#6717849 schekuri 31-Jul-2009
1213   l_level_count number;
1214   cursor c_multi_level
1215   is select lookup_code
1216   from iex_lookups_v
1217   where lookup_type='IEX_RUNNING_LEVEL'
1218   and lookup_code= 'BILL_TO'
1219   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1220   --end bug#6717849 schekuri 31-Jul-2009
1221 
1222 BEGIN
1223 
1224   SET_MO_GLOBAL;
1225 
1226   l_Complete_Days  := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
1227   l_bkr_filter  := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
1228   --l_org_id  := TO_NUMBER(oe_profile.value('OE_ORGANIZATION_ID', fnd_profile.value('ORG_ID'))); -- commented for bug 8826561 PNAVEENK
1229   l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
1230   l_node_counter := 0;
1231 
1232   OPEN c_sel_enum(p_sel_enum_id);
1233   FETCH c_sel_enum INTO l_sel_enum_rec;
1234   CLOSE c_sel_enum;
1235 
1236   OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
1237   FETCH c_node_label INTO l_node_label;
1238   CLOSE c_node_label;
1239 
1240   -- Start bug 5874874 gnramasa 25-Apr-07
1241   l_data_source := 'IEX_BILLTO_PRO_ALL_UWQ';
1242   l_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
1243   l_refresh_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
1244 
1245   /*
1246   l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
1247 
1248 
1249   IF l_Access = 'T' then
1250    SELECT count(*) INTO l_check FROM iex_assignments where
1251    alt_resource_id =  p_RESOURCE_ID
1252    AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
1253    AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
1254    AND NVL(DELETED_FLAG,'N') = 'N';
1255   END IF;
1256 
1257    IF (l_level = 'PARTY') then
1258      IF (l_check > 0 ) then
1259        l_str1   := ASSIGNMENTS_WHERE_CLAUSE('PARTY', l_sel_enum_rec.work_q_view_for_primary_node) || ' AND ' || RESOURCES_WHERE_CLAUSE('PARTY', l_sel_enum_rec.work_q_view_for_primary_node) || ' ) ';
1260      ELSE
1261        l_str1   :=  RESOURCES_WHERE_CLAUSE('PARTY', l_sel_enum_rec.work_q_view_for_primary_node);
1262      END IF;
1263    ELSIF (l_level = 'ACCOUNT') then
1264      IF (l_check > 0 ) then
1265        l_str1   := ASSIGNMENTS_WHERE_CLAUSE('ACCOUNT', l_sel_enum_rec.work_q_view_for_primary_node) || ' AND ' || RESOURCES_WHERE_CLAUSE('ACCOUNT', l_sel_enum_rec.work_q_view_for_primary_node) || ' ) ';
1266      ELSE
1267        l_str1   :=  RESOURCES_WHERE_CLAUSE('ACCOUNT', l_sel_enum_rec.work_q_view_for_primary_node);
1268      END IF;
1269    ELSE
1270      IF l_check > 0 then
1271        l_str1   := ASSIGNMENTS_WHERE_CLAUSE('BILLTO', l_sel_enum_rec.work_q_view_for_primary_node) || ' AND ' || RESOURCES_WHERE_CLAUSE('BILLTO', l_sel_enum_rec.work_q_view_for_primary_node) || ' ) ';
1272      ELSE
1273        l_str1   :=  RESOURCES_WHERE_CLAUSE('BILLTO', l_sel_enum_rec.work_q_view_for_primary_node);
1274      END IF;
1275   END IF;
1276 
1277   l_security_where := l_str1;
1278 
1279   l_default_where := ' ( RESOURCE_ID = :RESOURCE_ID OR'  || ASSIGNMENTS_WHERE_CLAUSE('BILLTO', l_view_name) || ' ) ';
1280 
1281 
1282   l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
1283   l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1284   l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1285   l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1286   l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1287 
1288   l_bind_list(1).bind_var_name := ':RESOURCE_ID';
1289   l_bind_list(1).bind_var_value := 1;
1290   l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1291 
1292   l_bind_list(2).bind_var_name := ':REAL_RESOURCE_ID';
1293   l_bind_list(2).bind_var_value := p_resource_id;
1294   l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1295 
1296   IF ( l_access in ('F', 'P')) THEN
1297     IF l_bkr_filter = 'Y' THEN
1298       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_prom || l_str_bkr;
1299     ELSE
1300       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_prom;
1301     END IF;
1302 
1303     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1304   ELSE
1305 
1306     -- No count view when the security is enabled
1307     l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
1308 
1309     OPEN c_person;
1310     FETCH c_person INTO l_person_id;
1311     CLOSE c_person;
1312 
1313     l_bind_list(3).bind_var_name := ':PERSON_ID' ;
1314     l_bind_list(3).bind_var_value := l_person_id;
1315     l_bind_list(3).bind_var_data_type := 'NUMBER' ;
1316 
1317     IF l_bkr_filter = 'Y' THEN
1318       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_prom || l_str_bkr;
1319     ELSE
1320       l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_prom;
1321     END IF;
1322 
1323     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1324   END IF;
1325  */
1326 
1327   OPEN c_strategy_level;
1328   FETCH c_strategy_level INTO l_strategy_level;
1329   CLOSE c_strategy_level;
1330 
1331       --begin bug#6717849 schekuri 31-Jul-2009
1332   select count(1)
1333   into l_level_count
1334   from iex_lookups_v
1335   where lookup_type='IEX_RUNNING_LEVEL'
1336   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1337 
1338   if l_level_count>1 then
1339   open c_multi_level;
1340   fetch c_multi_level into l_strategy_level;
1341   close c_multi_level;
1342   end if;
1343   --end bug#6717849 schekuri 31-Jul-2009
1344 
1345   IF l_strategy_level = 'BILL_TO' THEN
1346     l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
1347     l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1348     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1349     l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1350     l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1351 
1352    -- l_default_where :=' IEU_PARAM_PK_COL=''CUSTOMER_SITE_USE_ID'' and '||attach_where_clause(p_resource_id); --bug#6717849 schekuri 31-Jul-2009
1353       l_default_where :=' IEU_PARAM_PK_COL=''CUSTOMER_SITE_USE_ID'' ' || attach_where_clause(p_resource_id); -- bug 8537638 PNAVEENK 11-8-2009
1354     l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where;
1355 
1356     IF (l_access = 'T' ) THEN
1357       OPEN c_collector_id;
1358       FETCH c_collector_id INTO l_collector_id;
1359       CLOSE c_collector_id;
1360 
1361       OPEN c_person;
1362       FETCH c_person INTO l_person_id;
1363       CLOSE c_person;
1364 
1365       l_bind_list(1).bind_var_name := ':PERSON_ID' ;
1366       l_bind_list(1).bind_var_value := l_person_id;
1367       l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1368 
1369       l_bind_list(2).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
1370       l_bind_list(2).bind_var_value := p_resource_id;
1371       l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1372 
1373       l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1374     ELSE    -- bug 9453423 PNAVEENK
1375        IF l_default_where IS NULL THEN
1376           l_ld_list(l_node_counter).WHERE_CLAUSE := ' RESOURCE_ID = :RESOURCE_ID';
1377 
1378 	  l_bind_list(1).bind_var_name := ':RESOURCE_ID';
1379           l_bind_list(1).bind_var_value := 1;
1380           l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1381 
1382           l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1383        END IF;
1384     END IF;
1385   ELSE  /* IF l_strategy_level <> 'BILL_TO' */
1386 
1387     l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
1388     l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
1389     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1390     l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1391 
1392     l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
1393 
1394     l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
1395     l_bind_list(1).bind_var_value := 'ALL';
1396     l_bind_list(1).bind_var_data_type := 'CHAR' ;
1397 
1398     l_bind_list(2).bind_var_name := ':RESOURCE_ID';
1399     l_bind_list(2).bind_var_value := -1;
1400     l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1401 
1402     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1403   END IF;
1404 
1405 
1406   l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
1407   -- End bug 5874874 gnramasa 25-Apr-07
1408 
1409   l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1410   l_ld_list(l_node_counter).NODE_TYPE := 0;
1411   l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1412   l_ld_list(l_node_counter).NODE_DEPTH := 1;
1413 
1414   IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1415      iex_debug_pub.LogMessage('ENUMERATE_BILLTO_PROM_NODES: main final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
1416   END IF;
1417 
1418   -- !!
1419   -- !!!!!!!!!! BUILDING SUB NODES WHERE CLAUSE
1420   -- !!
1421 
1422   l_node_counter := l_node_counter + 1;
1423 
1424 
1425   l_EnableNodes :=  NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
1426 
1427   if (l_EnableNodes <> 'N') then
1428   l_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
1429   l_refresh_view_name := 'IEX_CU_PRO_ALL_UWQ_V';
1430 
1431   -- Start for bug 8537638 PNAVEENK 31-8-2009
1432   if l_ld_list(0).WHERE_CLAUSE is not null then
1433 	l_temp_str := ' and ';
1434     else
1435 	l_temp_str := ' ';
1436     end if;
1437 
1438   FOR cur_rec IN c_del_new_nodes LOOP
1439 	IF l_strategy_level = 'BILL_TO' THEN
1440 	    IF (cur_rec.lookup_code = 'ACTIVE') THEN
1441 	      l_data_source := 'IEX_BILLTO_PRO_ACT_UWQ';
1442 	      --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and active_promises is not null';
1443 	      --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(active_promises,0)  > 0 ';
1444 	      l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(active_promises,0)  > 0 ';
1445 	    ELSIF (cur_rec.lookup_code = 'PENDING') THEN
1446 	      l_data_source := 'IEX_BILLTO_PRO_PEND_UWQ';
1447 	      --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and pending_promises is not null';
1448 	      -- l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(pending_promises,0)  > 0 ';
1449 	        l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(pending_promises,0)  > 0 ';
1450 	    ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
1451 	      l_data_source := 'IEX_BILLTO_PRO_COMP_UWQ';
1452 	      --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and complete_promises is not null';
1453 	      --l_node_where := l_ld_list(0).WHERE_CLAUSE || ' and NVL(complete_promises,0)  > 0 ';
1454 	       l_node_where := l_ld_list(0).WHERE_CLAUSE || l_temp_str || ' NVL(complete_promises,0)  > 0 ';
1455 	    END IF;
1456            -- end for bug 8537638
1457 
1458 	l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1459 	l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1460 	l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1461 	l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1462 
1463 
1464 	l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where;
1465 	l_ld_list(l_node_counter).BIND_VARS    := l_ld_list(0).BIND_VARS;
1466 
1467 	ELSE  /* IF l_strategy_level <> 'BILL_TO' */
1468 
1469 		l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1470 		l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1471 		l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1472 		l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1473 
1474 		l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
1475 
1476 		l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
1477 		l_bind_list(1).bind_var_value := 'ALL';
1478 		l_bind_list(1).bind_var_data_type := 'CHAR' ;
1479 
1480 		l_bind_list(2).bind_var_name := ':RESOURCE_ID';
1481 		l_bind_list(2).bind_var_value := -1;
1482 		l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1483 
1484 		l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1485 	END IF;
1486 
1487     l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1488     l_ld_list(l_node_counter).NODE_TYPE := 0;
1489     l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1490     l_ld_list(l_node_counter).NODE_DEPTH := 2;
1491 
1492     l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1493 
1494 
1495     IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1496 	    iex_debug_pub.LogMessage('ENUMERATE_BILLTO_PROM_NODES: Subnode final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
1497     END IF;
1498 
1499     l_node_counter := l_node_counter + 1;
1500   END LOOP;
1501 
1502   END IF;
1503 
1504   IEU_PUB.ADD_UWQ_NODE_DATA
1505   (P_RESOURCE_ID,
1506    P_SEL_ENUM_ID,
1507    l_ld_list
1508   );
1509 
1510 
1511 EXCEPTION
1512   WHEN OTHERS THEN
1513     if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1514        FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'iex',  'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM );
1515     End if;
1516     RAISE;
1517 
1518 END ENUMERATE_BILLTO_PROM_NODES;
1519 
1520 
1521 FUNCTION ATTACH_WHERE_CLAUSE(P_RESOURCE_ID NUMBER) return varchar2 IS
1522 l_check number := 0;
1523 l_collector_id number;
1524 l_security_where varchar2(2000);
1525 
1526 l_Access varchar2(1)   := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
1527 l_bkr_filter varchar2(1) := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
1528 l_person_id number;
1529 
1530 l_bind_list IEU_PUB.BindVariableRecordList ;
1531 l_ld_list  IEU_PUB.EnumeratorDataRecordList;
1532 l_group_check number := 0;
1533 
1534 BEGIN
1535 
1536     IF (l_Access = 'T') THEN
1537       SELECT count(*) INTO l_check FROM iex_assignments where
1538       alt_resource_id = p_RESOURCE_ID
1539       AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
1540       AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
1541       AND NVL(DELETED_FLAG,'N') = 'N';
1542 
1543       select count(1) into l_group_check
1544       from ar_collectors where status='A' and
1545       nvl(inactive_date,sysdate)>=sysdate and resource_type='RS_GROUP';
1546 
1547 
1548      if l_check>0 or l_group_check>0 then
1549         l_security_where := ' :person_id = :person_id and collector_resource_id in (select resource_id from ar_collectors where resource_type = ''RS_RESOURCE'' and resource_id = :COLLECTOR_RESOURCE_ID ';
1550      else
1551 	    l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
1552      end if;
1553 
1554      if l_check>0 then
1555 	    l_security_where := l_security_where ||
1556                 ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
1557                 ' iea.alt_employee_id = :PERSON_ID '||
1558                 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
1559                 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
1560                 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
1561                 ' and ac.resource_id=iea.resource_id '||
1562                 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') ';
1563      end if;
1564 
1565      if l_group_check>0 then
1566         l_security_where := l_security_where ||
1567                 ' union all SELECT ac.resource_ID '||
1568 			    ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
1569 			    ' WHERE ac.resource_ID = jtgrp.group_id '||
1570 			    ' AND ac.resource_type = ''RS_GROUP'''||
1571 			    ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
1572 			    ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
1573      end if;
1574 
1575      if l_check>0 or l_group_check>0 then
1576 	    l_security_where := l_security_where || ' ) ';
1577      end if;
1578 
1579 
1580      IF (l_bkr_filter = 'Y') THEN
1581        l_security_where := l_security_where || ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
1582      END IF;
1583   ELSE  --full mode
1584      IF (l_bkr_filter = 'Y') THEN
1585        l_security_where := l_security_where || ' NUMBER_OF_BANKRUPTCIES = 0 ';
1586      END IF;
1587   END IF;
1588 
1589 
1590 
1591   IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1592    iex_debug_pub.LogMessage('RESOURCES_WHERE_CLAUSE: final where clause: ' || l_security_where  );
1593   END IF;
1594   -- Start for bug 8538637 PNAVEENK 11-8-2009
1595   if l_security_where is not null then
1596    l_security_where := 'and ' || l_security_where;
1597    return l_security_where;
1598   else
1599    return null;
1600   end if;
1601   -- end for bug 8538637
1602 EXCEPTION WHEN OTHERS THEN
1603  IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1604    	iex_debug_pub.LogMessage('Error occurred while constructing the where clause ' || sqlerrm);
1605  END IF;
1606 
1607  return null;
1608 
1609 END ATTACH_WHERE_CLAUSE;
1610 
1611 -- PL/SQL Block
1612 BEGIN
1613   PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
1614 END IEX_UWQ_PROM_ENUMS_PVT;