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_STRAT_ENUMS_PVT

Source


1 PACKAGE BODY IEX_UWQ_STRAT_ENUMS_PVT AS
2 /* $Header: iexenstb.pls 120.31 2010/12/13 18:40:49 barathsr ship $ */
3 
4 -- Sub-Program Units
5 
6 PG_DEBUG NUMBER(2);
7 
8 PROCEDURE ENUMERATE_STRAT_NODES
9   (P_RESOURCE_ID      IN NUMBER
10   ,P_LANGUAGE         IN VARCHAR2
11   ,P_SOURCE_LANG      IN VARCHAR2
12   ,P_SEL_ENUM_ID      IN NUMBER
13   )
14   AS
15 
16   l_node_label VARCHAR2(200);
17   l_ld_list  IEU_PUB.EnumeratorDataRecordList;
18 
19   l_node_counter           NUMBER;
20   l_bind_list IEU_PUB.BindVariableRecordList ;
21   l_Access   varchar2(10);
22   l_person_id NUMBER;
23   l_Level   varchar2(15);
24   l_check    NUMBER;
25 
26   CURSOR c_person IS
27     select source_id
28     from jtf_rs_resource_extns
29     where resource_id = p_resource_id;
30 
31   CURSOR c_del_new_nodes is
32     select lookup_code, meaning from fnd_lookup_values
33     where lookup_type = 'IEX_UWQ_NODE_STATUS'
34     and lookup_code in ('ACTIVE', 'PENDING')  -- added by jypark 01/02/2003 not show 'COMPLETE' node
35     and LANGUAGE = userenv('LANG');
36 
37   CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
38     SELECT meaning
39     FROM fnd_lookup_values
40     WHERE lookup_type = in_lookup_type
41     AND lookup_code = in_lookup_code
42     AND LANGUAGE = userenv('LANG');
43 
44   CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
45     SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
46     FROM ieu_uwq_sel_enumerators
47     WHERE sel_enum_id = in_sel_enum_id;
48 
49   l_sel_enum_rec c_sel_enum%ROWTYPE;
50 
51   l_data_source VARCHAR2(1000);
52   l_default_where VARCHAR2(1000);
53   l_security_where VARCHAR2(2000);
54   l_node_where VARCHAR2(2000);
55   l_uwq_where VARCHAR2(1000);
56 
57   type tbl_wclause is table of varchar2(500) index by binary_integer;
58 
59   l_wclause tbl_wclause;
60   l_str_and VARCHAR2(10);
61    l_str_str VARCHAR2(1000);
62   l_str_bkr VARCHAR2(1000);
63   l_str_bkr_2 VARCHAR2(1000);
64  -- l_org_id NUMBER;  --commented for bug 8826561 PNAVEENK
65   l_Complete_Days VARCHAR2(40);
66   l_bkr_filter VARCHAR2(240);
67 
68   l_full_brk  VARCHAR2(2000);
69   l_str_or    VARCHAR2(10);
70   l_restrict_res_grp varchar2(700);
71   l_restrict_assign_resource varchar2(700);
72   l_work_item_prf_val varchar2(200);--Added for Bug 9769710 07-Dec-2010 barathsr
73   l_work_item_where varchar2(2000);--Added for Bug 9769710 07-Dec-2010 barathsr
74 
75   L VARCHAR2(240);
76 
77   CURSOR C_ORG_ID IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS
78     WHERE MO_GLOBAL.CHECK_ACCESS(ORGANIZATION_ID) = 'Y';
79   l_additional_str varchar2(200);
80 /* Added by gnramasa 25-Apr-2007 Bug 5874874 Display strategy only at that level */
81   l_strategy_level VARCHAR2(30);
82   l_new_resource_ID NUMBER;
83 
84 CURSOR c_strategy_level IS
85     SELECT PREFERENCE_VALUE
86 	FROM IEX_APP_PREFERENCES_B
87     WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
88     and org_id is null--Modified for Bug 9079394 by barathsr 03-Nov-2009
89     and enabled_flag='Y'; --bug#6717849 schekuri 31-Jul-2009
90 /* Added by gnramasa 25-Apr-2007 Bug 5874874 Display strategy only at that level */
91 
92  --begin bug#6717849 schekuri 31-Jul-2009
93   l_level_count number;
94   cursor c_multi_level(p_multi_level varchar2)
95   is select lookup_code
96   from iex_lookups_v
97   where lookup_type='IEX_RUNNING_LEVEL'
98   and lookup_code= p_multi_level
99   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
100   --end bug#6717849 schekuri 31-Jul-2009
101 
102 BEGIN
103 
104 
105   MO_GLOBAL.INIT('IEX');
106   MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
107 
108 
109   FOR I_ORG IN C_ORG_ID LOOP
110   MO_GLOBAL.SET_POLICY_CONTEXT('S',I_ORG.ORGANIZATION_ID);
111 
112   L := IEX_UTILITIES.get_cache_value('GL_CURRENCY'||I_ORG.ORGANIZATION_ID,
113      'SELECT  GLSOB.CURRENCY_CODE CURRENCY from GL_SETS_OF_BOOKS GLSOB, AR_SYSTEM_PARAMETERS ARSYS WHERE ARSYS.SET_OF_BOOKS_ID ' ||
114      ' = GLSOB.SET_OF_BOOKS_ID');
115   L := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE'||I_ORG.ORGANIZATION_ID, 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
116   END LOOP;
117 
118   MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
119 
120   -- SAVEPOINT start_str_enumeration;
121 
122   l_node_counter := 0;
123   l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
124   l_Level  := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
125 
126   l_Complete_Days  := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
127   l_bkr_filter  := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
128   l_work_item_prf_val := nvl(fnd_profile.value('IEX_SHOW_WORK_ITEM_TYPES'),'MANUAL'); --Added for Bug 9769710 07-Dec-2010 barathsr
129 --  l_org_id  := TO_NUMBER(oe_profile.value('OE_ORGANIZATION_ID', fnd_profile.value('ORG_ID'))); --commented for bug 8826561 PNAVEENK
130 
131   l_str_and  := ' AND ';
132   l_str_or   := ' OR ';
133   l_str_str  := ' AND NUMBER_OF_STRATEGIES > 0 ';
134 
135   OPEN c_strategy_level;
136   FETCH c_strategy_level INTO l_strategy_level;
137   CLOSE c_strategy_level;
138   --begin bug#6717849 schekuri 31-Jul-2009
139   select count(1)
140   into l_level_count
141   from iex_lookups_v
142   where lookup_type='IEX_RUNNING_LEVEL'
143   and  iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
144   --end bug#6717849 schekuri 31-Jul-2009
145 
146   OPEN c_sel_enum(p_sel_enum_id);
147   FETCH c_sel_enum INTO l_sel_enum_rec;
148   CLOSE c_sel_enum;
149 
150   OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
151   FETCH c_node_label INTO l_node_label;
152   CLOSE c_node_label;
153 
154   OPEN c_person;
155   FETCH c_person INTO l_person_id;
156   CLOSE c_person;
157 
158   l_new_resource_id := p_Resource_id;
159 
160   IF p_sel_enum_id = 13067 THEN
161     l_data_source := 'IEX_CU_STR_UWQ';
162     --begin bug#6717849 schekuri 31-Jul-2009
163       if l_level_count>1 then
164       open c_multi_level('CUSTOMER');
165       fetch c_multi_level into l_strategy_level;
166       close c_multi_level;
167      end if;
168      --End bug#6717849 schekuri 31-Jul-2009
169     /* Begin - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
170     if l_strategy_level <> 'DELINQUENCY' then
171 	l_restrict_assign_resource := ' and ieu_param_pk_col=''PARTY_ID'' ';
172     else
173 	l_restrict_assign_resource := ' and ieu_param_pk_col = NULL ';
174     end if;
175     /* End - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
176     /* Begin - Added by gnramasa 25-Apr-2007 Bug 5874874  Display strategy only at that level */
177     if l_strategy_level <>  'CUSTOMER' then
178        l_new_Resource_id := -1;
179        l_person_id := -1;
180     end if;
181    /* End - Added by gnramasa 25-Apr-2007 Bug 5874874  Display strategy only at that level */
182   ELSIF p_sel_enum_id = 13068 THEN
183     l_data_source := 'IEX_ACC_STR_UWQ';
184         --begin bug#6717849 schekuri 31-Jul-2009
185       if l_level_count>1 then
186       open c_multi_level('ACCOUNT');
187       fetch c_multi_level into l_strategy_level;
188       close c_multi_level;
189      end if;
190      --End bug#6717849 schekuri 31-Jul-2009
191     /* Begin - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
192     if l_strategy_level <> 'DELINQUENCY' then
193 	l_restrict_assign_resource := ' and ieu_param_pk_col=''CUST_ACCOUNT_ID'' ';
194     else
195 	l_restrict_assign_resource := ' and ieu_param_pk_col = NULL ';
196     end if;
197     /* End - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
198     /* Begin - Added by gnramasa 25-Apr-2007 Bug 5874874  Display strategy only at that level, destroy the bind value  */
199     if l_strategy_level <>  'ACCOUNT' then
200        l_new_Resource_id := -1;
201        l_person_id := -1;
202     end if;
203    /* End - Added by gnramasa 25-Apr-2007 Bug 5874874  Display strategy only at that level destroy the bind value */
204   ELSIF p_sel_enum_id = 13072 THEN
205     l_data_source := 'IEX_BILLTO_STR_UWQ';
206         --begin bug#6717849 schekuri 31-Jul-2009
207       if l_level_count>1 then
208       open c_multi_level('BILL_TO');
209       fetch c_multi_level into l_strategy_level;
210       close c_multi_level;
211      end if;
212      --End bug#6717849 schekuri 31-Jul-2009
213     /* Begin - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
214     if l_strategy_level <> 'DELINQUENCY' then
215 	l_restrict_assign_resource := ' and ieu_param_pk_col=''CUSTOMER_SITE_USE_ID'' ';
216     else
217 	l_restrict_assign_resource := ' and ieu_param_pk_col = NULL ';
218     end if;
219     /* End - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
220     /* Begin - Added by gnramasa 25-Apr-2007 Bug 5874874  Display strategy only at that level destroy the bind value*/
221     if l_strategy_level <>  'BILL_TO' then
222        l_new_Resource_id := -1;
223        l_person_id := -1;
224     end if;
225    /* End - Added by gnramasa 25-Apr-2007 Bug 5874874  Display strategy only at that level */
226   ELSIF p_sel_enum_id = 13069 THEN
227     l_data_source := 'IEX_STRATEGIES_UWQ';
228     /* Begin - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
229     if l_strategy_level = 'DELINQUENCY' then
230 	l_restrict_assign_resource := '';
231     end if;
232     /* End - Added by gnramasa 26-Sep-2008 Bug 7433430 Display strategy only at that level */
233   END IF;
234 
235   /*
236   l_default_where := ' RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
237 
238   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) ' ;
239 
240   l_str_bkr_2  := ' 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) ' ;
241 
242   --Bug4775893. Fix by LKKUMAR on 07-Dec-2005. Start.
243   l_full_brk := ' OR  :RESOURCE_ID ' ||
244    	        ' IN  (select iea.alt_resource_id '||
245                 ' FROM  iex_assignments iea ' ||
246                 ' WHERE  nvl(iea.deleted_flag,''N'') = ''N'' ' ||
247                 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
248                 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) ' ||
249 		' AND iea.resource_id =  ' ||  l_sel_enum_rec.work_q_view_for_primary_node ||'.RESOURCE_ID ) ' ;
250 
251   l_restrict_assign_resource := ' select iea.alt_resource_id '||
252           ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
253           ' iex_assignments iea ' ||
254           ' WHERE hp.collector_id = ac.collector_id ' ||
255           ' AND ac.resource_id = iea.alt_resource_id ' ||
256           ' AND  iea.alt_employee_id = :PERSON_ID  ' ||
257           ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
258           ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >=  TRUNC(SYSDATE) ' ||
259 	  ' AND ac.resource_id IS NOT NULL ';
260 
261   --Bug4775893. Fix by LKKUMAR on 07-Dec-2005. End.
262 
263   l_restrict_res_grp := ' select ac.resource_id '||
264           ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
265           ' WHERE hp.collector_id = ac.collector_id ' ||
266           ' AND  ac.employee_id = :PERSON_ID ' ||
267           ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
268 	  ' AND ac.resource_id IS NOT NULL  ' ||
269           ' UNION ALL  SELECT jtgrp.resource_id ' ||
270           ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
271           ' WHERE hp.collector_id = ac.collector_id ' ||
272           ' AND ac.resource_ID = jtgrp.group_id ' ||
273           ' AND ac.resource_type = ''RS_GROUP'' ' ||
274   	  ' AND ac.resource_id IS NOT NULL ' ||
275           ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
276 	  ' AND jtgrp.resource_id = :RESOURCE_ID ' ||
277 	  ' AND ' || l_sel_enum_rec.work_q_view_for_primary_node  ||
278           '.resource_id = :RESOURCE_ID ' ||
279           ' AND jtgrp.PERSON_ID = :PERSON_ID ';
280  */
281  /*
282   IF( p_sel_enum_id IN (13067,13068,13072) and l_level = 'PARTY') THEN
283     l_additional_str := ' AND hp.party_id = ' || l_sel_enum_rec.work_q_view_for_primary_node || '.party_id';
284   ELSIF (p_sel_enum_id IN (13068,13072) and l_level = 'ACCOUNT') THEN
285      l_additional_str := ' AND hp.cust_account_id = ' || l_sel_enum_rec.work_q_view_for_primary_node || '.cust_account_id';
286   ELSIF (p_sel_enum_id = 13072 and l_level = 'BILLTO') THEN
287      l_additional_str := ' AND hp.site_use_id = ' || l_sel_enum_rec.work_q_view_for_primary_node || '.site_use_id';
288   END IF;
289 */
290   l_default_where := ' :UWQ_STATUS = :UWQ_STATUS ';
291 
292   --Begin Bug 9769710 07-Dec-2010 barathsr
293   if l_work_item_prf_val is not null then
294     if l_work_item_prf_val = 'MANUAL AND AUTO' then
295       l_work_item_where := ' AND work_type  in ('||'''MANUAL'',''AUTOMATIC'''||')';
296     elsif l_work_item_prf_val = 'MANUAL AND WORKFLOW' then
297       l_work_item_where := ' AND work_type in ('||'''MANUAL'',''WORKFLOW'''||')';
298     elsif l_work_item_prf_val = 'ALL' then
299       l_work_item_where := ' AND work_type in ('||'''MANUAL'',''WORKFLOW'',''AUTOMATIC'''||')';
300     else
301       l_work_item_where := ' AND work_type in ('||'''MANUAL'''||')';
302     end if;
303   end if;
304    --End Bug 9769710 07-Dec-2010 barathsr
305 
306   -- Changed for bug#7693986 by PNAVEENK on 12-1-2009
307   IF p_sel_enum_id = 13069 THEN
308     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'') ) ' ;
309     l_Access := 'T';
310   ELSE
311     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'') ) ' ;
312   END IF;
313   -- End for bug#7693986
314 --Start bug 6908307 gnramasa 13th June 08
315 
316  IF (l_Access = 'T') THEN --commented for bug#7499019 by PNAVEENK on 18-Nov-2008
317 	   SELECT count(*) into l_check from iex_assignments where
318 	   alt_resource_id =  p_RESOURCE_ID
319 	   AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
320 	   AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
321 	   AND NVL(DELETED_FLAG,'N') = 'N';
322 
323 	  If (l_check > 0) then
324 
325 	--	l_restrict_assign_resource := l_restrict_assign_resource || ' and resource_id in (select resource_id from ar_collectors where '
326 	--				      || ' resource_type = ''RS_RESOURCE'' and resource_id = :RESOURCE_ID ';
327 	        l_restrict_assign_resource := l_restrict_assign_resource||' and resource_id in (select :RESOURCE_ID+0 from dual ';  --Added for bug#7499019 by PNAVEENK
328 		/* l_security_where := l_sel_enum_rec.work_q_view_for_primary_node || '.resource_id in ( '
329 		    || l_restrict_res_grp || ' UNION ALL ' || l_restrict_assign_resource || ' ) ' ;
330 		*/
331 		 l_restrict_assign_resource := l_restrict_assign_resource ||
332                             ' union all (SELECT iea.resource_id FROM iex_assignments iea where '||
333                             ' iea.alt_employee_id = :PERSON_ID '||
334                             ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
335                             ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
336                             ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' ))';
337                          --   ' and ac.resource_id=iea.resource_id '||      -- commented for bug#7499019 by PNAVEENK
338                          --   ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP''))) ';
339 
340                 l_bind_list(3).bind_var_name := ':PERSON_ID' ;
341                 l_bind_list(3).bind_var_value := l_person_id;
342                 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
343 
344 	  Else
345 		l_restrict_assign_resource := l_restrict_assign_resource||' and resource_id = :RESOURCE_ID ';
346 		/* l_security_where :=   l_sel_enum_rec.work_q_view_for_primary_node || '.resource_id in ( '
347 		  || l_restrict_res_grp  || ' ) ' ;
348 		*/
349 	  End If;
350  ELSE
351 --	l_restrict_assign_resource := l_restrict_assign_resource || ' and :RESOURCE_ID = :RESOURCE_ID ';
352         l_restrict_assign_resource := l_restrict_assign_resource||' and :RESOURCE_ID = :RESOURCE_ID ';
353  END IF;
354 --End bug 6908307 gnramasa 13th June 08
355 
356   l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
357   l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
358   l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
359   l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
360 
361   l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
362   l_bind_list(1).bind_var_value := 'ALL';
363   l_bind_list(1).bind_var_data_type := 'CHAR' ;
364 
365   l_bind_list(2).bind_var_name := ':RESOURCE_ID';
366   --l_bind_list(2).bind_var_value := p_resource_id;
367   l_bind_list(2).bind_var_value := l_new_resource_id;
368   l_bind_list(2).bind_var_data_type := 'NUMBER' ;
369 
370   /*
371   IF ( l_access in ('F', 'P')) THEN
372     IF p_sel_enum_id = 13069 THEN
373       IF l_bkr_filter = 'Y' THEN
374    	  l_ld_list(l_node_counter).WHERE_CLAUSE := '( ' || l_default_where  || l_full_brk || ' ) '|| l_str_bkr_2;
375       ELSE
376           l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_full_brk;
377       END IF;
378     ELSE
379       IF l_bkr_filter = 'Y' THEN
380 	l_ld_list(l_node_counter).WHERE_CLAUSE := '( ' || l_default_where || l_full_brk || ' ) ' || l_str_str || l_str_bkr;
381       ELSE
382         l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where ||  l_full_brk  || l_str_str ;
383       END IF;
384     END IF;
385 
386     l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
387 
388     IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
389      iex_debug_pub.LogMessage('ENUMERATE_STRATEGY_NODES: full Mode: main final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
390     END IF;
391 
392   ELSE
393     OPEN c_person;
394     FETCH c_person INTO l_person_id;
395     CLOSE c_person;
396 
397     l_bind_list(3).bind_var_name := ':PERSON_ID' ;
398     l_bind_list(3).bind_var_value := l_person_id;
399     l_bind_list(3).bind_var_data_type := 'NUMBER' ;
400 
401     IF p_sel_enum_id = 13069 THEN
402       IF l_bkr_filter = 'Y' THEN
403         l_ld_list(l_node_counter).WHERE_CLAUSE := '( ' || l_default_where || l_full_brk   || l_str_or
404 	                                           || l_security_where  || ' ) ' || l_str_bkr_2;
405       ELSE
406         l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_or || l_security_where || l_full_brk;
407       END IF;
408     ELSE
409       IF l_bkr_filter = 'Y' THEN
410 	l_ld_list(l_node_counter).WHERE_CLAUSE := ' ( ' || l_default_where || l_str_or
411                                          || l_security_where  || l_str_str ||   l_full_brk || ' ) ' || l_str_bkr ;
412       ELSE
413         l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_or || l_security_where
414 	                                          || l_str_str || l_full_brk;
415       END IF;
416     END IF;
417     */
418 /*  if l_check>0  then
419 
420     l_restrict_assign_resource := l_restrict_assign_resource ||
421                             ' union all (SELECT iea.resource_id FROM iex_assignments iea where '||
422                             ' iea.alt_employee_id = :PERSON_ID '||
423                             ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
424                             ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
425                             ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' ))';
426                          --   ' and ac.resource_id=iea.resource_id '||      -- commented for bug#7499019 by PNAVEENK
427                          --   ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP''))) ';
428 
429     l_bind_list(3).bind_var_name := ':PERSON_ID' ;
430     l_bind_list(3).bind_var_value := l_person_id;
431     l_bind_list(3).bind_var_data_type := 'NUMBER' ;
432 
433   end if;*/
434 
435   IF l_bkr_filter = 'Y' THEN
436     l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_restrict_assign_resource || l_str_bkr || l_work_item_where;
437   ELSE
438     l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_restrict_assign_resource|| l_work_item_where;
439   END IF;
440 
441   l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
442 
443   IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
444     iex_debug_pub.LogMessage('ENUMERATE_STRATEGY_NODES: restricted Mode: main final where clause: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
445   END IF;
446 
447   --END IF;
448 
449   l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
450   l_ld_list(l_node_counter).NODE_TYPE := 0;
451   l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
452   l_ld_list(l_node_counter).NODE_DEPTH := 1;
453 
454 
455   l_node_counter := l_node_counter + 1;
456 
457   IEU_PUB.ADD_UWQ_NODE_DATA
458   (P_RESOURCE_ID,
459    P_SEL_ENUM_ID,
460    l_ld_list
461   );
462 
463 EXCEPTION
464   WHEN OTHERS THEN
465     -- ROLLBACK TO start_str_enumeration;
466     --Begin - Bug#5344878 - Andre Araujo - Need to log exceptions
467     if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
468        FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'iex',  'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM );
469     End if;
470     --End - Bug#5344878 - Andre Araujo - Need to log exceptions
471     RAISE;
472 
473 END ENUMERATE_STRAT_NODES;
474 
475 BEGIN
476 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
477 -- PL/SQL Block
478 END IEX_UWQ_STRAT_ENUMS_PVT;