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