[Home] [Help]
PACKAGE BODY: APPS.IEX_UWQ_DELIN_ENUMS_PVT
Source
1 PACKAGE BODY IEX_UWQ_DELIN_ENUMS_PVT AS
2 /* $Header: iexendlb.pls 120.36.12020000.3 2013/04/04 12:51:47 ehuh 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_DELIN_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 -- Territory Assignment Changes
45 l_Access varchar2(10);
46 l_Level varchar2(15);
47 l_person_id NUMBER;
48
49
50 CURSOR c_person IS
51 select source_id
52 from jtf_rs_resource_extns
53 where resource_id = p_resource_id;
54
55 CURSOR c_del_new_nodes is
56 select lookup_code, meaning from fnd_lookup_values
57 where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
58
59 CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
60 SELECT meaning
61 FROM fnd_lookup_values
62 WHERE lookup_type = in_lookup_type
63 AND lookup_code = in_lookup_code
64 AND LANGUAGE = userenv('LANG');
65
66 CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
67 SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
68 FROM ieu_uwq_sel_enumerators
69 WHERE sel_enum_id = in_sel_enum_id;
70 -- Start 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
76 -- start adding...for a bug 16494272
77 l_del_level varchar2(1):= 'N';
78 CURSOR c_check_del_level IS
79 select nvl(using_delinquency_level,'N') from iex_questionnaire_items;
80 -- end adding...for a bug 16494272
81
82 l_sel_enum_rec c_sel_enum%ROWTYPE;
83
84 l_Complete_Days varchar2(40);
85 l_data_source VARCHAR2(1000);
86 l_default_where VARCHAR2(1000);
87 l_security_where VARCHAR2(2000);
88 l_node_where VARCHAR2(2000);
89 l_uwq_where VARCHAR2(1000);
90 l_org_id NUMBER;
91
92 type tbl_wclause is table of varchar2(500) index by binary_integer;
93
94 l_wclause tbl_wclause;
95 l_str_and VARCHAR2(100);
96 l_str_del VARCHAR2(1000);
97 l_str_bkr VARCHAR2(1000);
98 l_str_bkr2 VARCHAR2(1000);
99
100 l_bkr_filter VARCHAR2(240);
101 l_check NUMBER;
102
103 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
104 l_EnableNodes varchar2(10);
105 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-node
106
107 l_additional_where VARCHAR2(2500);
108
109 -- Bug #6311505 bibeura 23-Oct-2007
110 l_strategy_level varchar2(100);
111 l_filter_col_str1 varchar2(1000);
112 l_filter_col_str2 varchar2(1000);
113 l_filter_cond_str varchar2(1000);
114
115 l_party_override varchar2(1); -- Added for bug 8708271 PNAVEENK
116 l_org_override varchar2(1); -- Added for bug 8708271 PNAVEENK
117
118 BEGIN
119
120 -- SAVEPOINT start_delin_enumeration;
121 --Moac Changes Start. Set the context.
122 MO_GLOBAL.INIT('IEX');
123 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
124 --Moac Changes End. Set the context.
125
126 l_str_and := ' AND ';
127 l_str_del := ' AND NUMBER_OF_DELINQUENCIES > 0 ';
128 l_node_counter := 0;
129 l_check := 0;
130
131 l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
132 l_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
133 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
134 l_bkr_filter := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
135 --Bug4221359. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
136 /* l_additional_where :=
137 ' UNION ALL SELECT hp.party_id ' ||
138 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp, ' ||
139 ' JTF_RS_GROUPS_DENORM jrg ' ||
140 ' WHERE hp.collector_id = ac.collector_id ' ||
141 ' AND ac.resource_ID = jtgrp.group_id ' ||
142 ' AND jtgrp.group_id = jrg.group_id ' ||
143 ' AND ac.resource_type = ''RS_GROUP'' ' ||
144 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
145 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';
146 */
147 --Bug4221359. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
148
149 --Begin Bug #6311505 bibeura 23-Oct-2007
150 select preference_value
151 into l_strategy_level
152 from iex_app_preferences_b
153 where preference_name='COLLECTIONS STRATEGY LEVEL'
154 and org_id is null
155 and enabled_flag='Y';
156 -- Start for bug 8708271 PNAVEENK
157 open c_ml_setup;
158 fetch c_ml_setup into l_party_override,l_org_override;
159 close c_ml_setup;
160
161 -- start adding... for a bug 16494272
162 Open c_check_del_level;
163 Fetch c_check_del_level into l_del_level;
164 Close c_check_del_level;
165 -- end adding... for a bug 16494272
166
167 if (l_party_override = 'Y' or l_org_override = 'Y')
168 and (l_del_level = 'Y') then -- added by a bug 16494272
169 l_strategy_level := 'DELINQUENCY';
170 end if;
171
172 if l_strategy_level='CUSTOMER' then
173 l_filter_col_str1 := 'customer_id in (select hp.party_id ';
174 l_filter_col_str2 := 'select hp.party_id ';
175 l_filter_cond_str := ' AND hp.cust_account_id = -1 '||
176 ' AND hp.site_use_id is null ';
177 elsif l_strategy_level='ACCOUNT' then
178 l_filter_col_str1 := 'cust_account_id in (select hp.cust_account_id ';
179 l_filter_col_str2 := 'select hp.cust_account_id ';
180 l_filter_cond_str := ' AND hp.cust_account_id <> -1 ' ||
181 ' AND hp.site_use_id is null ';
182 elsif l_strategy_level='BILL_TO' then
183 l_filter_col_str1 := 'site_use_id in (select hp.site_use_id ';
184 l_filter_col_str2 := 'select hp.site_use_id ';
185 l_filter_cond_str := ' AND hp.site_use_id is not null ';
186 else
187 l_filter_col_str1 := 'customer_id in (select hp.party_id ';
188 l_filter_col_str2 := 'select hp.party_id ';
189 l_filter_cond_str := ' ';
190 end if;
191 --End Bug #6311505 bibeura 23-Oct-2007
192
193 l_wclause(1) :=
194 ' (RESOURCE_ID = :RESOURCE_ID) and ' ||
195 ' (UWQ_STATUS IS NULL or UWQ_STATUS = :UWQ_STATUS or ' ||
196 ' (trunc(UWQ_ACTIVE_DATE) <= trunc(SYSDATE) and UWQ_STATUS = ''PENDING'' )) ';
197
198 l_wclause(2) :=
199 ' (RESOURCE_ID = :RESOURCE_ID) and ' ||
200 ' (UWQ_STATUS = :UWQ_STATUS and (trunc(UWQ_ACTIVE_DATE) > trunc(SYSDATE))) ';
201
202 l_wclause(3) :=
203 ' (RESOURCE_ID = :RESOURCE_ID) and ' ||
204 ' (UWQ_STATUS = :UWQ_STATUS and (trunc(UWQ_COMPLETE_DATE) + ' || l_Complete_Days || ' > trunc(SYSDATE))) ';
205
206 OPEN c_sel_enum(p_sel_enum_id);
207 FETCH c_sel_enum INTO l_sel_enum_rec;
208 CLOSE c_sel_enum;
209
210 OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
211 FETCH c_node_label INTO l_node_label;
212 CLOSE c_node_label;
213
214 l_data_source := 'IEX_DELINQUENCIES_UWQ';
215
216 l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
217 -- Start for bug#7693986 by PNAVEENK on 12-1-2009
218 /* l_str_bkr2 := ' AND NOT EXISTS (SELECT 1 FROM iex_bankruptcies bkr WHERE bkr.party_id = '
219 || l_sel_enum_rec.work_q_view_for_primary_node || '.customer_id '
220 --Bug5261831. Fix By LKKUMAR on 14-Jun-2006. Start.
221 || ' AND NVL(BKR.DISPOSITION_CODE,''GRANTED'') NOT IN (''DISMISSED'',''NEGOTIATION'',''WITHDRAWN'')) ';
222 --Bug5261831. Fix By LKKUMAR on 14-Jun-2006. End.
223 */
224
225 IF p_sel_enum_id = 13069 THEN
226 l_str_bkr2 := ' 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'') ) ' ;
227 -- Commented for bug#8536993 by PNAVEENK on 21-5-2009
228 -- ELSE
229 -- l_str_bkr2 := ' 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'') ) ' ;
230 -- end for bug#8536993
231 END IF;
232 -- End for bug#7693986
233 l_default_where := ' RESOURCE_ID = :RESOURCE_ID AND :UWQ_STATUS = :UWQ_STATUS ';
234
235 -- Territory Assignment Changes
236 IF (l_Access = 'T') THEN
237
238 begin
239 SELECT count(*) into l_check from iex_assignments where alt_resource_id = p_RESOURCE_ID
240 AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
241 AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
242 AND NVL(DELETED_FLAG,'N') = 'N';
243 exception
244 when others then l_check := 0;
245 end;
246 END IF;
247
248 If (l_check > 0) then
249 l_security_where :=
250 --'customer_id in (select hp.party_id '||
251 l_filter_col_str1|| -- Added for Bug #6311505 bibeura 23-Oct-2007
252 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
253 ' iex_assignments iea ' ||
254 ' WHERE hp.collector_id = ac.collector_id ' ||
255 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
256 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
257 ' AND ac.resource_id = iea.resource_id ' ||
258 ' AND iea.alt_employee_id = :PERSON_ID ' ||
259 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
260 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
261 --' UNION ALL select hp.party_id '||
262 ' UNION ALL '|| l_filter_col_str2 || -- Added for Bug #6311505 bibeura 23-Oct-2007
263 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
264 ' WHERE hp.collector_id = ac.collector_id ' ||
265 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
266 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
267 ' AND ac.employee_id = :PERSON_ID ' ||
268 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
269 ' UNION ALL ' || l_filter_col_str2 || -- Added for Bug #6311505 bibeura 23-Oct-2007
270 --' UNION ALL SELECT hp.party_id ' ||
271 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
272 ' WHERE hp.collector_id = ac.collector_id ' ||
273 ' AND ac.resource_ID = jtgrp.group_id ' ||
274 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
275 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
276 ' AND ac.resource_type = ''RS_GROUP'' ' ||
277 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
278 ' AND jtgrp.PERSON_ID = :PERSON_ID )'; -- Changed by gnramasa for bug 6363290 27-Aug-07
279
280 if l_del_level = 'Y' then -- added if statement by a bug 16494272
281 if l_party_override = 'Y' then
282 if l_org_override = 'Y' then
283 l_security_where := l_security_where || ' AND iex_utilities.get_party_running_level (iex_delinquencies_uwq_v.customer_id , iex_delinquencies_uwq_v.org_id ) = ''DELINQUENCY'' ';
284 else
285 l_security_where := l_security_where || ' and exists ( select 1 from hz_party_preferences hzprf where hzprf.value_varchar2= ''DELINQUENCY'' '
286 ||' and hzprf.module= ''COLLECTIONS''and hzprf.category = ''COLLECTIONS LEVEL'' '
287 ||' and hzprf.preference_code = ''PARTY_ID'' and hzprf.party_id= iex_delinquencies_uwq_v.customer_id) ';
288 end if;
289 else
290 if l_org_override = 'Y' then
291 l_security_where := l_security_where || ' and exists (select 1 from iex_app_preferences_b pref where pref.preference_name=''COLLECTIONS STRATEGY LEVEL'' '
292 ||' and pref.preference_value=''DELINQUENCY'' and pref.org_id = iex_delinquencies_uwq_v.org_id) ';
293 end if;
294 end if;
295 end if; -- added if statement by a bug 16494272
296
297 --Bug4221359. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
298 l_security_where := l_security_where || l_additional_where;
299 --Bug4221359. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
300 Else
301 l_security_where :=
302 --'customer_id in (select hp.party_id '||
303 l_filter_col_str1 || -- Added for Bug #6311505 bibeura 23-Oct-2007
304 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
305 ' WHERE hp.collector_id = ac.collector_id ' ||
306 l_filter_cond_str || -- Added for Bug #6311505 bibeura 23-Oct-2007
307 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
308 ' AND ac.employee_id = :PERSON_ID ' ||
309 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
310 --' UNION ALL SELECT hp.party_id ' ||
311 ' UNION ALL ' || l_filter_col_str2 || -- Added for Bug #6311505 bibeura 23-Oct-2007
312 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
313 ' WHERE hp.collector_id = ac.collector_id ' ||
314 ' AND ac.resource_ID = jtgrp.group_id ' ||
315 l_filter_cond_str || -- Added for Bug #6311505 bibeura 23-Oct-2007
316 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
317 ' AND ac.resource_type = ''RS_GROUP'' ' ||
318 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
319 ' AND jtgrp.PERSON_ID = :PERSON_ID )'; -- Changed by gnramasa for bug 6363290 27-Aug-07
320
321 if l_del_level = 'Y' then -- added if statement by a bug 16494272
322 if l_party_override = 'Y' then
323 if l_org_override = 'Y' then
324 l_security_where := l_security_where || ' AND iex_utilities.get_party_running_level (iex_delinquencies_uwq_v.customer_id , iex_delinquencies_uwq_v.org_id ) = ''DELINQUENCY'' ';
325 else
326 l_security_where := l_security_where || ' and exists ( select 1 from hz_party_preferences hzprf where hzprf.value_varchar2= ''DELINQUENCY'' '
327 ||' and hzprf.module=''COLLECTIONS'' and hzprf.category = ''COLLECTIONS LEVEL'' '
328 ||' and hzprf.preference_code = ''PARTY_ID'' and hzprf.party_id = iex_delinquencies_uwq_v.customer_id) ';
329 end if;
330 else
331 if l_org_override = 'Y' then
332 l_security_where := l_security_where || ' and exists (select 1 from iex_app_preferences_b pref where pref.preference_name=''COLLECTIONS STRATEGY LEVEL'' '
333 ||' and pref.preference_value=''DELINQUENCY'' and pref.org_id=iex_delinquencies_uwq_v.org_id) ';
334 end if;
335 end if;
336 -- end for bug 8708271
337 end if; -- added if statement by a bug 16494272
338
339 --Bug4221359. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
340 l_security_where := l_security_where || l_additional_where;
341 --Bug4221359. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
342 End If;
343
344
345 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
346 l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
347 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
348 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
349
350 l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
351 l_bind_list(1).bind_var_value := 'ALL';
352 l_bind_list(1).bind_var_data_type := 'CHAR' ;
353
354 l_bind_list(2).bind_var_name := ':RESOURCE_ID';
355 l_bind_list(2).bind_var_value := 1;
356 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
357
358 IF ( l_access in ('F', 'P')) THEN
359 IF l_bkr_filter = 'Y' THEN
360 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_bkr2;
361 ELSE
362 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where;
363 END IF;
364
365 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
366 ELSE
367 OPEN c_person;
368 FETCH c_person INTO l_person_id;
369 CLOSE c_person;
370
371 l_bind_list(3).bind_var_name := ':PERSON_ID' ;
372 l_bind_list(3).bind_var_value := l_person_id;
373 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
374
375 IF l_bkr_filter = 'Y' THEN
376 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_bkr2;
377 ELSE
378 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where;
379 END IF;
380
381 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
382 END IF;
383
384 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
385 l_ld_list(l_node_counter).NODE_TYPE := 0;
386 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
387 l_ld_list(l_node_counter).NODE_DEPTH := 1;
388
389
390 l_node_counter := l_node_counter + 1;
391
392 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
393 IEX_DEBUG_PUB.logMessage('l_ld_list(l_node_counter).WHERE_CLAUSE1: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
394 IEX_DEBUG_PUB.logMessage('l_ld_list(l_node_counter).WHERE_CLAUSE1: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
395 END IF;
396
397 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
398 l_EnableNodes := NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
399
400 if (l_EnableNodes <> 'N') then
401 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
402
403 FOR cur_rec IN c_del_new_nodes LOOP
404 IF (cur_rec.lookup_code = 'ACTIVE') THEN
405 l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
406 l_bind_list(1).bind_var_value := 'ACTIVE';
407 l_bind_list(1).bind_var_data_type := 'CHAR' ;
408 l_uwq_where := l_wclause(1);
409 l_node_where := l_default_where || ' AND ACTIVE_DELINQUENCIES > 0 ';
410 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
411 l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
412 l_bind_list(1).bind_var_value := 'PENDING';
413 l_bind_list(1).bind_var_data_type := 'CHAR' ;
414 l_uwq_where := l_wclause(2);
415 l_node_where := l_default_where || ' AND PENDING_DELINQUENCIES > 0 ';
416 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
417 l_bind_list(1).bind_var_name := ':UWQ_STATUS' ;
418 l_bind_list(1).bind_var_value := 'COMPLETE';
419 l_bind_list(1).bind_var_data_type := 'CHAR' ;
420 l_uwq_where := l_wclause(3);
421 l_node_where := l_default_where || ' AND COMPLETE_DELINQUENCIES > 0 ';
422 END IF;
423
424 If (l_check > 0) then
425 l_security_where :=
426 --'customer_id in (select hp.party_id '||
427 l_filter_col_str1|| -- Added for Bug #6311505 bibeura 23-Oct-2007
428 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
429 ' iex_assignments iea ' ||
430 ' WHERE hp.collector_id = ac.collector_id ' ||
431 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
432 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
433 ' AND ac.resource_id = iea.resource_id ' ||
434 ' AND iea.alt_employee_id = :PERSON_ID ' ||
435 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
436 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
437 --' UNION ALL select hp.party_id '||
438 ' UNION ALL ' || l_filter_col_str2 || -- Added for Bug #6311505 bibeura 23-Oct-2007
439 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
440 ' WHERE hp.collector_id = ac.collector_id ' ||
441 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
442 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
443 ' AND ac.employee_id = :PERSON_ID ' ||
444 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
445 --' UNION ALL SELECT hp.party_id ' ||
446 ' UNION ALL ' || l_filter_col_str2 || -- Added for Bug #6311505 bibeura 23-Oct-2007
447 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
448 ' WHERE hp.collector_id = ac.collector_id ' ||
449 ' AND ac.resource_ID = jtgrp.group_id ' ||
450 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
451 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
452 ' AND ac.resource_type = ''RS_GROUP'' ' ||
453 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
454 ' AND jtgrp.PERSON_ID = :PERSON_ID ) '; --Added ) to fix 10275372 12/13/2010 SNUTHALA
455 l_security_where := l_security_where || l_additional_where;
456 Else
457 l_security_where :=
458 --'customer_id in (select hp.party_id '||
459 l_filter_col_str1|| -- Added for Bug #6311505 bibeura 23-Oct-2007
460 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
461 ' WHERE hp.collector_id = ac.collector_id ' ||
462 l_filter_cond_str|| -- Added for Bug #6311505 bibeura 23-Oct-2007
463 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
464 ' AND ac.employee_id = :PERSON_ID ' ||
465 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
466 --' UNION ALL SELECT hp.party_id ' ||
467 ' UNION ALL ' || l_filter_col_str2 || -- Added for Bug #6311505 bibeura 23-Oct-2007
468 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
469 ' WHERE hp.collector_id = ac.collector_id ' ||
470 ' AND ac.resource_ID = jtgrp.group_id ' ||
471 l_filter_cond_str || -- Added for Bug #6311505 bibeura 23-Oct-2007
472 -- ' AND hp.cust_account_id = -1 ' || Bug4775052. Fix By LKKUMAR on 29-Nov-2005
473 ' AND ac.resource_type = ''RS_GROUP'' ' ||
474 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
475 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';--Added ) to fix 10275372 12/13/2010 SNUTHALA
476 l_security_where := l_security_where || l_additional_where;
477 End If;
478 --Start Added to fix 10275372 12/13/2010 SNUTHALA
479 if l_del_level = 'Y' then -- added if statement by a bug 16494272
480 if l_party_override = 'Y' then
481 if l_org_override = 'Y' then
482 l_security_where := l_security_where || ' AND iex_utilities.get_party_running_level (iex_delinquencies_uwq_v.customer_id , iex_delinquencies_uwq_v.org_id ) = ''DELINQUENCY'' ';
483 else
484 l_security_where := l_security_where || ' and exists ( select 1 from hz_party_preferences hzprf where hzprf.value_varchar2= ''DELINQUENCY'' '
485 ||' and hzprf.module=''COLLECTIONS'' and hzprf.category = ''COLLECTIONS LEVEL'' '
486 ||' and hzprf.preference_code = ''PARTY_ID'' and hzprf.party_id = iex_delinquencies_uwq_v.customer_id) ';
487 end if;
488 else
489 if l_org_override = 'Y' then
490 l_security_where := l_security_where || ' and exists (select 1 from iex_app_preferences_b pref where pref.preference_name=''COLLECTIONS STRATEGY LEVEL'' '
491 ||' and pref.preference_value=''DELINQUENCY'' and pref.org_id=iex_delinquencies_uwq_v.org_id) ';
492 end if;
493 end if;
494 end if; -- added if statement by a bug 16494272
495 --End Added to fix 10275372 12/13/2010 SNUTHALA
496
497 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
498 l_ld_list(l_node_counter).VIEW_NAME := l_sel_enum_rec.work_q_view_for_primary_node;
499 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
500
501 l_bind_list(2).bind_var_name := ':RESOURCE_ID' ;
502 l_bind_list(2).bind_var_value := 1;
503 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
504
505 IF ( l_access in ('P', 'F')) THEN
506 IF l_bkr_filter = 'Y' THEN
507 l_ld_list(l_node_counter).WHERE_CLAUSE := l_uwq_where || l_str_bkr2;
508 ELSE
509 l_ld_list(l_node_counter).WHERE_CLAUSE := l_uwq_where;
510 END IF;
511
512 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
513
514 ELSE
515 IF l_bkr_filter = 'Y' THEN
516 l_ld_list(l_node_counter).WHERE_CLAUSE := l_uwq_where || l_str_and || l_security_where || l_str_bkr2;
517 ELSE
518 l_ld_list(l_node_counter).WHERE_CLAUSE := l_uwq_where || l_str_and || l_security_where;
519 END IF;
520
521 l_bind_list(3).bind_var_name := ':PERSON_ID' ;
522 l_bind_list(3).bind_var_value := l_person_id;
523 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
524 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
525 END IF;
526
527 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
528 l_ld_list(l_node_counter).NODE_TYPE := 0;
529 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
530 l_ld_list(l_node_counter).NODE_DEPTH := 2;
531
532 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
533
534 l_node_counter := l_node_counter + 1;
535
536 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
537 IEX_DEBUG_PUB.logMessage('l_ld_list(l_node_counter).WHERE_CLAUSE2: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
538 IEX_DEBUG_PUB.logMessage('l_ld_list(l_node_counter).WHERE_CLAUSE2: ' || l_ld_list(l_node_counter).WHERE_CLAUSE);
539 END IF;
540
541 END LOOP;
542 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
543 end if;
544 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
545
546
547 IEU_PUB.ADD_UWQ_NODE_DATA
548 (P_RESOURCE_ID,
549 P_SEL_ENUM_ID,
550 l_ld_list
551 );
552
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 -- ROLLBACK TO start_delin_enumeration;
557 RAISE;
558
559 END ENUMERATE_DELIN_NODES;
560
561 -- added by jypark 09/26/2004 for performance
562
563 PROCEDURE ENUMERATE_CU_DELIN_NODES
564 (P_RESOURCE_ID IN NUMBER
565 ,P_LANGUAGE IN VARCHAR2
566 ,P_SOURCE_LANG IN VARCHAR2
567 ,P_SEL_ENUM_ID IN NUMBER
568 )
569 AS
570
571 l_node_label VARCHAR2(200);
572 l_ld_list IEU_PUB.EnumeratorDataRecordList;
573
574 l_node_counter NUMBER;
575 l_bind_list IEU_PUB.BindVariableRecordList ;
576 --Territory Assignment Changes.
577 l_Access Varchar2(10);
578 l_Level Varchar2(15);
579 l_person_id NUMBER;
580 l_check NUMBER;
581 l_collector_id NUMBER; -- 5874874
582
583 CURSOR c_person IS
584 select source_id
585 from jtf_rs_resource_extns
586 where resource_id = p_resource_id;
587
588 CURSOR c_del_new_nodes is
589 select lookup_code, meaning from fnd_lookup_values
590 where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
591
592 CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
593 SELECT meaning
594 FROM fnd_lookup_values
595 WHERE lookup_type = in_lookup_type
596 AND lookup_code = in_lookup_code
597 AND LANGUAGE = userenv('LANG');
598
599 CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
600 SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
601 FROM ieu_uwq_sel_enumerators
602 WHERE sel_enum_id = in_sel_enum_id;
603
604 CURSOR c_collector_id IS
605 SELECT collector_id from AR_COLLECTORS where resource_id = p_resource_id
606 and resource_type = 'RS_RESOURCE';
607
608 l_sel_enum_rec c_sel_enum%ROWTYPE;
609
610 l_Complete_Days varchar2(40);
611 l_data_source VARCHAR2(1000);
612 l_default_where VARCHAR2(1000);
613 l_security_where VARCHAR2(2000);
614 l_node_where VARCHAR2(2000);
615 l_uwq_where VARCHAR2(1000);
616 l_org_id NUMBER;
617
618 type tbl_wclause is table of varchar2(500) index by binary_integer;
619
620 l_str_and VARCHAR2(100);
621 l_str_del VARCHAR2(1000);
622 l_str_bkr VARCHAR2(1000);
623
624 l_bkr_filter VARCHAR2(240);
625 l_view_name VARCHAR2(240);
626 l_refresh_view_name VARCHAR2(240);
627 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
628 l_EnableNodes varchar2(10);
629 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-node
630
631 l_additional_where VARCHAR2(2000);
632
633 L VARCHAR2(240);
634
635 CURSOR C_ORG_ID IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS
636 WHERE MO_GLOBAL.CHECK_ACCESS(ORGANIZATION_ID) = 'Y';
637
638 CURSOR c_strategy_level IS
639 SELECT PREFERENCE_VALUE
640 FROM IEX_APP_PREFERENCES_B
641 WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
642 and org_id is null
643 and enabled_flag='Y';
644
645 l_strategy_level VARCHAR2(30);
646
647 l_group_check number;
648
649 --begin bug#6717849 schekuri 31-Jul-2009
650 l_level_count number;
651 cursor c_multi_level
652 is select lookup_code
653 from iex_lookups_v
654 where lookup_type='IEX_RUNNING_LEVEL'
655 and lookup_code= 'CUSTOMER'
656 and iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
657 --end bug#6717849 schekuri 31-Jul-2009
658
659 BEGIN
660 -- SAVEPOINT start_delin_enumeration;
661
662 SET_MO_GLOBAL;
663
664
665 l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
666 l_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
667 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
668 l_bkr_filter := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
669
670 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
671 /* l_additional_where :=
672 ' UNION ALL SELECT hp.party_id ' ||
673 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp, ' ||
674 ' JTF_RS_GROUPS_DENORM jrg ' ||
675 ' WHERE hp.collector_id = ac.collector_id ' ||
676 ' AND ac.resource_ID = jtgrp.group_id ' ||
677 ' AND jtgrp.group_id = jrg.group_id ' ||
678 ' AND hp.cust_account_id = -1 ' ||
679 ' AND ac.resource_type = ''RS_GROUP'' ' ||
680 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
681 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';
682 */
683 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
684
685
686 l_str_and := ' AND ';
687 l_str_del := ' AND NUMBER_OF_DELINQUENCIES > 0 ';
688 l_node_counter := 0;
689 l_check := 0;
690
691 OPEN c_sel_enum(p_sel_enum_id);
692 FETCH c_sel_enum INTO l_sel_enum_rec;
693 CLOSE c_sel_enum;
694
695 OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
696 FETCH c_node_label INTO l_node_label;
697 CLOSE c_node_label;
698
699 OPEN c_strategy_level;
700 FETCH c_strategy_level INTO l_strategy_level;
701 CLOSE c_strategy_level;
702
703 --begin bug#6717849 schekuri 31-Jul-2009
704 select count(1)
705 into l_level_count
706 from iex_lookups_v
707 where lookup_type='IEX_RUNNING_LEVEL'
708 and iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
709
710 if l_level_count>1 then
711 open c_multi_level;
712 fetch c_multi_level into l_strategy_level;
713 close c_multi_level;
714 end if;
715 --end bug#6717849 schekuri 31-Jul-2009
716
717 l_data_source := 'IEX_CU_DLN_ALL_UWQ';
718 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
719 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
720
721 IF l_strategy_level = 'CUSTOMER' THEN
722 l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
723
724 l_data_source := 'IEX_CU_DLN_ALL_UWQ';
725 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
726 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. Start.
727 -- l_refresh_view_name := 'IEX_CU_DLN_CNT_ALL_UWQ_V';
728 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
729 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. End.
730
731 l_default_where := ' RESOURCE_ID = :RESOURCE_ID and IEU_PARAM_PK_COL=''PARTY_ID'' ';
732
733 -- Territory Assignment Changes
734 IF (l_Access = 'T') THEN
735 OPEN c_collector_id;
736 FETCH c_collector_ID INTO l_collector_id;
737 CLOSE c_collector_id;
738
739 begin
740 SELECT count(*) INTO l_check FROM iex_assignments where
741 alt_resource_id = p_RESOURCE_ID
742 AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
743 AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
744 AND NVL(DELETED_FLAG,'N') = 'N';
745 exception
746 when others then l_check := 0;
747 end;
748
749 begin
750 select count(1) into l_group_check
751 from ar_collectors where status='A' and
752 nvl(inactive_date,sysdate)>=sysdate and resource_type='RS_GROUP';
753 exception
754 when others then l_group_check := 0;
755 end;
756
757 END IF;
758
759 --Start bug#5874874 gnramasa 25-Apr-2007
760 /* If (l_check > 0 ) then
761 l_security_where :=
762 'party_id in (select hp.party_id ' ||
763 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
764 ' iex_assignments iea ' ||
765 ' WHERE hp.collector_id = ac.collector_id ' ||
766 ' AND ac.resource_id = iea.resource_id ' ||
767 ' AND hp.cust_account_id = -1 ' ||
768 ' AND iea.alt_employee_id = :PERSON_ID ' ||
769 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
770 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
771 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
772 ' UNION ALL select hp.party_id '||
773 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
774 ' WHERE hp.collector_id = ac.collector_id ' ||
775 ' AND ac.employee_id = :PERSON_ID ' ||
776 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
777 ' AND hp.cust_account_id = -1 ' ||
778 ' UNION ALL SELECT hp.party_id ' ||
779 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
780 ' WHERE hp.collector_id = ac.collector_id ' ||
781 ' AND ac.resource_ID = jtgrp.group_id ' ||
782 ' AND hp.cust_account_id = -1 ' ||
783 ' AND ac.resource_type = ''RS_GROUP'' ' ||
784 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
785 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
786 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
787 l_security_where := l_security_where || l_additional_where;
788 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
789
790 Else
791 l_security_where :=
792 'party_id in (select hp.party_id '||
793 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
794 ' WHERE hp.collector_id = ac.collector_id ' ||
795 ' AND ac.employee_id = :PERSON_ID ' ||
796 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
797 ' AND hp.cust_account_id = -1 ' ||
798 ' UNION ALL SELECT hp.party_id ' ||
799 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
800 ' WHERE hp.collector_id = ac.collector_id ' ||
801 ' AND ac.resource_ID = jtgrp.group_id ' ||
802 ' AND hp.cust_account_id = -1 ' ||
803 ' AND ac.resource_type = ''RS_GROUP'' ' ||
804 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
805 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
806 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
807 l_security_where := l_security_where || l_additional_where;
808 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
809 End If; */
810
811 if l_check>0 or l_group_check>0 then
812 --l_security_where := ' :person_id = :person_id and collector_resource_id in (select :COLLECTOR_RESOURCE_ID from dual ';
813 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 ';
814
815 else
816 l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
817 end if;
818
819 if l_check>0 then
820 l_security_where := l_security_where || ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
821 ' iea.alt_employee_id = :PERSON_ID '||
822 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
823 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
824 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
825 ' and ac.resource_id=iea.resource_id '||
826 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') ';
827 end if;
828
829 if l_group_check>0 then
830 l_security_where := l_security_where || ' union all SELECT ac.resource_ID '||
831 ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
832 ' WHERE ac.resource_ID = jtgrp.group_id '||
833 ' AND ac.resource_type = ''RS_GROUP'''||
834 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
835 ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
836 end if;
837
838 if l_check>0 or l_group_check>0 then
839 l_security_where := l_security_where || ' ) ';
840 end if;
841 --End bug#5874874 gnramasa 25-Apr-2007
842
843 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
844 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
845 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
846 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
847 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
848
849 l_bind_list(1).bind_var_name := ':RESOURCE_ID';
850 l_bind_list(1).bind_var_value := 1;
851 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
852
853 IF ( l_access in ('F', 'P')) THEN
854 IF l_bkr_filter = 'Y' THEN
855 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_del || l_str_bkr;
856 ELSE
857 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_del;
858 END IF;
859 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
860 ELSE
861
862 /* No count view when the security is enabled */
863 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
864
865 OPEN c_person;
866 FETCH c_person INTO l_person_id;
867 CLOSE c_person;
868
869 l_bind_list(2).bind_var_name := ':PERSON_ID' ;
870 l_bind_list(2).bind_var_value := l_person_id;
871 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
872
873 l_bind_list(3).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
874 l_bind_list(3).bind_var_value := p_resource_id;
875 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
876
877 IF l_bkr_filter = 'Y' THEN
878 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_del || l_str_bkr;
879 ELSE
880 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_del;
881 END IF;
882
883 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
884 END IF;
885 ELSE /* IF l_strategy_level <> 'CUSTOMER' */
886
887 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
888 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
889 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
890 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
891 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
892 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID';
893
894 l_bind_list(1).bind_var_name := ':RESOURCE_ID';
895 l_bind_list(1).bind_var_value := -1;
896 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
897 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
898 END IF;
899
900 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
901 l_ld_list(l_node_counter).NODE_TYPE := 0;
902 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
903 l_ld_list(l_node_counter).NODE_DEPTH := 1;
904
905
906 l_node_counter := l_node_counter + 1;
907 --l_check := 0;
908
909 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
910 l_EnableNodes := NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
911
912 if (l_EnableNodes <> 'N') then
913 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
914
915 FOR cur_rec IN c_del_new_nodes LOOP
916 IF l_strategy_level = 'CUSTOMER' THEN
917 IF (cur_rec.lookup_code = 'ACTIVE') THEN
918 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. Start.
919 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND ACTIVE_DELINQUENCIES IS NOT NULL ';
920 l_data_source := 'IEX_CU_DLN_ACT_UWQ';
921 --l_view_name := 'IEX_CU_DLN_ACT_UWQ_V';
922 --l_refresh_view_name := 'IEX_CU_DLN_CNT_ACT_UWQ_V';
923 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
924 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
925 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. End.
926
927 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
928 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. Start.
929 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND PENDING_DELINQUENCIES IS NOT NULL ';
930 l_data_source := 'IEX_CU_DLN_PEND_UWQ';
931 -- l_view_name := 'IEX_CU_DLN_PEND_UWQ_V';
932 -- l_refresh_view_name := 'IEX_CU_DLN_CNT_PEND_UWQ_V';
933 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
934 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
935 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. End.
936
937 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
938 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. Start.
939 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND COMPLETE_DELINQUENCIES IS NOT NULL ';
940 l_data_source := 'IEX_CU_DLN_COMP_UWQ';
941 -- l_view_name := 'IEX_CU_DLN_COMP_UWQ_V';
942 -- l_refresh_view_name := 'IEX_CU_DLN_CNT_COMP_UWQ_V';
943 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
944 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
945 --Bug5237039. Performance Fix by LKKUMAR on 24-May-2006. End.
946
947 END IF;
948
949 --Start bug#5874874 gnramasa 25-Apr-2007
950 /* If (l_check > 0 ) then
951 l_security_where :=
952 'party_id in (select hp.party_id '||
953 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
954 ' iex_assignments iea ' ||
955 ' WHERE hp.collector_id = ac.collector_id ' ||
956 ' AND ac.resource_id = iea.resource_id ' ||
957 ' AND hp.cust_account_id = -1 ' ||
958 ' AND iea.alt_employee_id = :PERSON_ID ' ||
959 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
960 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
961 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
962 ' UNION ALL select hp.party_id '||
963 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
964 ' WHERE hp.collector_id = ac.collector_id ' ||
965 ' AND ac.employee_id = :PERSON_ID ' ||
966 ' AND hp.cust_account_id = -1 ' ||
967 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
968 ' UNION ALL SELECT hp.party_id ' ||
969 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
970 ' WHERE hp.collector_id = ac.collector_id ' ||
971 ' AND ac.resource_ID = jtgrp.group_id ' ||
972 ' AND hp.cust_account_id = -1 ' ||
973 ' AND ac.resource_type = ''RS_GROUP'' ' ||
974 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
975 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
976 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
977 l_security_where := l_security_where || l_additional_where;
978 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
979 Else
980 l_security_where :=
981 'party_id in (select hp.party_id '||
982 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
983 ' WHERE hp.collector_id = ac.collector_id ' ||
984 ' AND ac.employee_id = :PERSON_ID ' ||
985 ' AND hp.cust_account_id = -1 ' ||
986 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
987 ' UNION ALL SELECT hp.party_id ' ||
988 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
989 ' WHERE hp.collector_id = ac.collector_id ' ||
990 ' AND ac.resource_ID = jtgrp.group_id ' ||
991 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
992 ' AND hp.cust_account_id = -1 ' ||
993 ' AND ac.resource_type = ''RS_GROUP'' ' ||
994 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
995 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
996 l_security_where := l_security_where || l_additional_where;
997 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
998 End If; */
999
1000
1001 if l_check>0 or l_group_check>0 then
1002 --l_security_where := ' :person_id = :person_id and collector_resource_id in (select :COLLECTOR_RESOURCE_ID from dual ';
1003 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 ';
1004
1005 else
1006 l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
1007 end if;
1008
1009 if l_check>0 then
1010 l_security_where := l_security_where || ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
1011 ' iea.alt_employee_id = :PERSON_ID '||
1012 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
1013 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
1014 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
1015 ' and ac.resource_id=iea.resource_id '||
1016 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') ';
1017 end if;
1018
1019 if l_group_check>0 then
1020 l_security_where := l_security_where || ' union all SELECT ac.resource_ID '||
1021 ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
1022 ' WHERE ac.resource_ID = jtgrp.group_id '||
1023 ' AND ac.resource_type = ''RS_GROUP'''||
1024 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
1025 ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
1026 end if;
1027
1028 if l_check>0 or l_group_check>0 then
1029 l_security_where := l_security_where || ' ) ';
1030 end if;
1031 --End bug#5874874 gnramasa 25-Apr-2007
1032
1033 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1034 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1035 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1036 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1037
1038 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
1039 l_bind_list(1).bind_var_value := 1;
1040 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1041
1042 IF ( l_access in ('P', 'F')) THEN
1043 IF l_bkr_filter = 'Y' THEN
1044 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_bkr;
1045 ELSE
1046 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where;
1047 END IF;
1048
1049 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1050
1051 ELSE
1052 /* No count view when the security is enabled */
1053 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
1054
1055 IF l_bkr_filter = 'Y' THEN
1056 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_and || l_security_where || l_str_bkr;
1057 ELSE
1058 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_and || l_security_where;
1059 END IF;
1060
1061 l_bind_list(2).bind_var_name := ':PERSON_ID' ;
1062 l_bind_list(2).bind_var_value := l_person_id;
1063 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1064
1065 l_bind_list(3).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
1066 l_bind_list(3).bind_var_value := p_resource_id;
1067 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
1068 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1069 END IF;
1070 ELSE /* l_strategy_level <> 'CUSTOMER' THEN */
1071 IF (cur_rec.lookup_code = 'ACTIVE') THEN
1072 l_node_where := l_default_where;
1073 l_data_source := 'IEX_ACC_DLN_ACT_UWQ';
1074 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
1075 l_node_where := l_default_where;
1076 l_data_source := 'IEX_ACC_DLN_PEND_UWQ';
1077 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
1078 l_node_where := l_default_where;
1079 l_data_source := 'IEX_ACC_DLN_COMP_UWQ';
1080 END IF;
1081
1082 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1083 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1084 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1085 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1086 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID';
1087
1088 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
1089 l_bind_list(1).bind_var_value := -1;
1090 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1091
1092 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1093 END IF;
1094
1095 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1096 l_ld_list(l_node_counter).NODE_TYPE := 0;
1097 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1098 l_ld_list(l_node_counter).NODE_DEPTH := 2;
1099
1100 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1101
1102 l_node_counter := l_node_counter + 1;
1103 --l_check := 0;
1104 END LOOP;
1105 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1106 end if;
1107 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1108
1109
1110 IEU_PUB.ADD_UWQ_NODE_DATA
1111 (P_RESOURCE_ID,
1112 P_SEL_ENUM_ID,
1113 l_ld_list
1114 );
1115
1116
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 -- ROLLBACK TO start_delin_enumeration;
1120 RAISE;
1121
1122 END ENUMERATE_CU_DELIN_NODES;
1123
1124 -- added by jypark 10/11/2004 for performance
1125
1126 PROCEDURE ENUMERATE_ACC_DELIN_NODES
1127 (P_RESOURCE_ID IN NUMBER
1128 ,P_LANGUAGE IN VARCHAR2
1129 ,P_SOURCE_LANG IN VARCHAR2
1130 ,P_SEL_ENUM_ID IN NUMBER
1131 )
1132 AS
1133
1134 l_node_label VARCHAR2(200);
1135 l_ld_list IEU_PUB.EnumeratorDataRecordList;
1136
1137 l_node_counter NUMBER;
1138 l_bind_list IEU_PUB.BindVariableRecordList ;
1139 l_Access varchar2(10);
1140 l_Level varchar2(15);
1141 l_person_id NUMBER;
1142 l_check NUMBER;
1143 l_collector_id NUMBER;
1144
1145 CURSOR c_person IS
1146 select source_id
1147 from jtf_rs_resource_extns
1148 where resource_id = p_resource_id;
1149
1150 CURSOR c_del_new_nodes is
1151 select lookup_code, meaning from fnd_lookup_values
1152 where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
1153
1154 CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
1155 SELECT meaning
1156 FROM fnd_lookup_values
1157 WHERE lookup_type = in_lookup_type
1158 AND lookup_code = in_lookup_code
1159 AND LANGUAGE = userenv('LANG');
1160
1161 CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
1162 SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
1163 FROM ieu_uwq_sel_enumerators
1164 WHERE sel_enum_id = in_sel_enum_id;
1165
1166 CURSOR c_collector_id IS
1167 SELECT collector_id from AR_COLLECTORS where resource_id = p_resource_id
1168 and resource_type = 'RS_RESOURCE';
1169
1170 l_sel_enum_rec c_sel_enum%ROWTYPE;
1171
1172 l_Complete_Days varchar2(40);
1173 l_data_source VARCHAR2(1000);
1174 l_default_where VARCHAR2(1000);
1175 l_security_where VARCHAR2(2000);
1176 l_node_where VARCHAR2(2000);
1177 l_uwq_where VARCHAR2(1000);
1178 l_org_id NUMBER;
1179
1180 type tbl_wclause is table of varchar2(500) index by binary_integer;
1181
1182 l_str_and VARCHAR2(100);
1183 l_str_del VARCHAR2(1000);
1184 l_str_bkr VARCHAR2(1000);
1185
1186 l_bkr_filter VARCHAR2(240);
1187 l_view_name VARCHAR2(240);
1188 l_refresh_view_name VARCHAR2(240);
1189 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1190 l_EnableNodes varchar2(10);
1191 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-node
1192
1193 l_additional_where1 VARCHAR2(2000);
1194 l_additional_where2 VARCHAR2(2000);
1195
1196 L VARCHAR2(240);
1197
1198 CURSOR C_ORG_ID IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS
1199 WHERE MO_GLOBAL.CHECK_ACCESS(ORGANIZATION_ID) = 'Y';
1200
1201 CURSOR c_strategy_level IS
1202 SELECT PREFERENCE_VALUE
1203 FROM IEX_APP_PREFERENCES_B
1204 WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
1205 and org_id is null
1206 and enabled_flag='Y'; --bug#6717849 schekuri 31-Jul-2009
1207
1208 l_strategy_level VARCHAR2(30);
1209 l_group_check number;
1210
1211 --begin bug#6717849 schekuri 31-Jul-2009
1212 l_level_count number;
1213 cursor c_multi_level
1214 is select lookup_code
1215 from iex_lookups_v
1216 where lookup_type='IEX_RUNNING_LEVEL'
1217 and lookup_code= 'ACCOUNT'
1218 and iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1219 --end bug#6717849 schekuri 31-Jul-2009
1220
1221 BEGIN
1222
1223 SET_MO_GLOBAL;
1224
1225 l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
1226 l_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
1227 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
1228 l_bkr_filter := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
1229 l_str_and := ' AND ';
1230 l_str_del := ' AND NUMBER_OF_DELINQUENCIES > 0 ';
1231 l_node_counter := 0;
1232 l_check :=0;
1233
1234 OPEN c_sel_enum(p_sel_enum_id);
1235 FETCH c_sel_enum INTO l_sel_enum_rec;
1236 CLOSE c_sel_enum;
1237
1238 OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
1239 FETCH c_node_label INTO l_node_label;
1240 CLOSE c_node_label;
1241
1242 OPEN c_strategy_level;
1243 FETCH c_strategy_level INTO l_strategy_level;
1244 CLOSE c_strategy_level;
1245
1246 --begin bug#6717849 schekuri 31-Jul-2009
1247 select count(1)
1248 into l_level_count
1249 from iex_lookups_v
1250 where lookup_type='IEX_RUNNING_LEVEL'
1251 and iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1252
1253 if l_level_count>1 then
1254 open c_multi_level;
1255 fetch c_multi_level into l_strategy_level;
1256 close c_multi_level;
1257 end if;
1258 --end bug#6717849 schekuri 31-Jul-2009
1259
1260
1261 l_data_source := 'IEX_ACC_DLN_ALL_UWQ';
1262 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
1263 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
1264
1265 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1266 /* l_additional_where1 :=
1267 ' UNION ALL SELECT hp.party_id ' ||
1268 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp, ' ||
1269 ' JTF_RS_GROUPS_DENORM jrg ' ||
1270 ' WHERE hp.collector_id = ac.collector_id ' ||
1271 ' AND ac.resource_ID = jtgrp.group_id ' ||
1272 ' AND jtgrp.group_id = jrg.group_id ' ||
1273 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1274 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1275 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';
1276
1277 l_additional_where2 :=
1278 ' UNION ALL SELECT hp.party_id ' ||
1279 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp, ' ||
1280 ' JTF_RS_GROUPS_DENORM jrg ' ||
1281 ' WHERE hp.collector_id = ac.collector_id ' ||
1282 ' AND ac.resource_ID = jtgrp.group_id ' ||
1283 ' AND jtgrp.group_id = jrg.group_id ' ||
1284 ' AND hp.cust_account_id = -1 ' ||
1285 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1286 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1287 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';
1288 */
1289 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1290
1291 IF l_strategy_level = 'ACCOUNT' THEN
1292 l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
1293 l_default_where := ' RESOURCE_ID = :RESOURCE_ID and IEU_PARAM_PK_COL=''CUST_ACCOUNT_ID'' ';
1294
1295 -- Territory Assignment Changes.
1296 IF (l_Access = 'T') THEN
1297 OPEN c_collector_id;
1298 FETCH c_collector_id INTO l_collector_id;
1299 CLOSE c_collector_id;
1300
1301 begin
1302 SELECT count(*) INTO l_check FROM iex_assignments
1303 where alt_resource_id = P_RESOURCE_ID
1304 AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
1305 AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
1306 --Bug4646657 . Check for Deleted flag .Fixed by lkkumar. Start.
1307 AND NVL(DELETED_FLAG,'N') = 'N';
1308 --Bug4646657 . Check for Deleted flag .Fixed by lkkumar. End.
1309 exception
1310 when others then l_check := 0;
1311 end;
1312
1313 begin
1314 select count(1) into l_group_check
1315 from ar_collectors where status='A' and
1316 nvl(inactive_date,sysdate)>=sysdate and resource_type='RS_GROUP';
1317 exception
1318 when others then l_group_check := 0;
1319 end;
1320
1321 END IF;
1322
1323 /* IF l_Level = 'PARTY' then
1324 If (l_check > 0) then
1325 l_security_where :=
1326 'party_id in (select hp.party_id '||
1327 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
1328 ' iex_assignments iea ' ||
1329 ' WHERE hp.collector_id = ac.collector_id ' ||
1330 ' AND hp.cust_account_id = -1 ' ||
1331 ' AND ac.resource_id = iea.resource_id ' ||
1332 ' AND iea.alt_employee_id = :PERSON_ID '||
1333 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
1334 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
1335 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
1336 ' UNION ALL select hp.party_id '||
1337 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1338 ' WHERE hp.collector_id = ac.collector_id ' ||
1339 ' AND ac.employee_id = :PERSON_ID ' ||
1340 ' AND hp.cust_account_id = -1 ' ||
1341 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1342 ' UNION ALL SELECT hp.party_id ' ||
1343 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1344 ' WHERE hp.collector_id = ac.collector_id ' ||
1345 ' AND ac.resource_ID = jtgrp.group_id ' ||
1346 ' AND hp.cust_account_id = -1 ' ||
1347 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1348 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1349 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1350 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1351 l_security_where := l_security_where || l_additional_where2;
1352 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1353 Else
1354 l_security_where :=
1355 'party_id in (select hp.party_id '||
1356 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1357 ' WHERE hp.collector_id = ac.collector_id ' ||
1358 ' AND ac.employee_id = :PERSON_ID ' ||
1359 ' AND hp.cust_account_id = -1 ' ||
1360 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1361 ' UNION ALL SELECT hp.party_id ' ||
1362 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1363 ' WHERE hp.collector_id = ac.collector_id ' ||
1364 ' AND ac.resource_ID = jtgrp.group_id ' ||
1365 ' AND hp.cust_account_id = -1 ' ||
1366 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1367 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1368 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1369 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1370 l_security_where := l_security_where || l_additional_where2;
1371 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1372
1373 End If;
1374 ELSE
1375 If (l_check > 0) then
1376 l_security_where :=
1377 'party_id in (select hp.party_id '||
1378 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
1379 ' iex_assignments iea ' ||
1380 ' WHERE hp.collector_id = ac.collector_id ' ||
1381 ' AND ' || l_view_name ||
1382 '.cust_account_id = hp.cust_account_id ' ||
1383 ' AND ac.resource_id = iea.resource_id ' ||
1384 ' AND hp.site_use_id is NULL ' ||
1385 ' AND iea.alt_employee_id = :PERSON_ID ' ||
1386 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
1387 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
1388 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
1389 ' UNION ALL select hp.party_id '||
1390 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1391 ' WHERE hp.collector_id = ac.collector_id ' ||
1392 ' AND ' || l_view_name ||
1393 '.cust_account_id = hp.cust_account_id ' ||
1394 ' AND hp.site_use_id is NULL ' ||
1395 ' AND ac.employee_id = :PERSON_ID ' ||
1396 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1397 ' UNION ALL SELECT hp.party_id ' ||
1398 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1399 ' WHERE hp.collector_id = ac.collector_id ' ||
1400 ' AND ac.resource_ID = jtgrp.group_id ' ||
1401 ' AND ' || l_view_name ||
1402 '.cust_account_id = hp.cust_account_id ' ||
1403 ' AND hp.site_use_id is NULL ' ||
1404 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1405 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1406 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1407 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1408 l_security_where := l_security_where || l_additional_where1;
1409 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1410
1411 Else
1412 l_security_where :=
1413 'party_id in (select hp.party_id '||
1414 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1415 ' WHERE hp.collector_id = ac.collector_id ' ||
1416 ' AND ' || l_view_name ||
1417 '.cust_account_id = hp.cust_account_id ' ||
1418 ' AND hp.site_use_id is NULL ' ||
1419 ' AND ac.employee_id = :PERSON_ID ' ||
1420 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1421 ' UNION ALL SELECT hp.party_id ' ||
1422 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1423 ' WHERE hp.collector_id = ac.collector_id ' ||
1424 ' AND ac.resource_ID = jtgrp.group_id ' ||
1425 ' AND ' || l_view_name ||
1426 '.cust_account_id = hp.cust_account_id ' ||
1427 ' AND hp.site_use_id is NULL ' ||
1428 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1429 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1430 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1431 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1432 l_security_where := l_security_where || l_additional_where1;
1433 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1434
1435 END IF;
1436 END IF; */
1437
1438 if l_check>0 or l_group_check>0 then
1439 --l_security_where := ' :person_id = :person_id and collector_resource_id in (select :COLLECTOR_RESOURCE_ID from dual ';
1440 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 ';
1441
1442 else
1443 l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
1444 end if;
1445
1446 if l_check>0 then
1447 l_security_where := l_security_where || ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
1448 ' iea.alt_employee_id = :PERSON_ID '||
1449 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
1450 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
1451 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
1452 ' and ac.resource_id=iea.resource_id '||
1453 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') ';
1454 end if;
1455
1456 if l_group_check>0 then
1457 l_security_where := l_security_where || ' union all SELECT ac.resource_ID '||
1458 ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
1459 ' WHERE ac.resource_ID = jtgrp.group_id '||
1460 ' AND ac.resource_type = ''RS_GROUP'''||
1461 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
1462 ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
1463 end if;
1464
1465 if l_check>0 or l_group_check>0 then
1466 l_security_where := l_security_where || ' ) ';
1467 end if;
1468 --End bug#5874874 gnramasa 25-apr-2007
1469
1470 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
1471 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1472 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1473 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1474 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1475
1476 l_bind_list(1).bind_var_name := ':RESOURCE_ID';
1477 l_bind_list(1).bind_var_value := 1;
1478 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1479
1480 IF ( l_access in ('F', 'P')) THEN
1481 IF l_bkr_filter = 'Y' THEN
1482 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_del || l_str_bkr;
1483 ELSE
1484 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_del;
1485 END IF;
1486
1487 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1488 ELSE
1489 /* No count view when the security is enabled */
1490 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
1491
1492 OPEN c_person;
1493 FETCH c_person INTO l_person_id;
1494 CLOSE c_person;
1495
1496 l_bind_list(2).bind_var_name := ':PERSON_ID' ;
1497 l_bind_list(2).bind_var_value := l_person_id;
1498 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1499
1500 l_bind_list(3).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
1501 l_bind_list(3).bind_var_value := p_resource_id;
1502 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
1503
1504 IF l_bkr_filter = 'Y' THEN
1505 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_del || l_str_bkr;
1506 ELSE
1507 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_del;
1508 END IF;
1509
1510 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1511 END IF;
1512 ELSE /* IF l_strategy_level <> 'BILL_TO' */
1513
1514 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
1515 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1516 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1517 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1518 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1519
1520 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID';
1521 l_bind_list(1).bind_var_name := ':RESOURCE_ID';
1522 l_bind_list(1).bind_var_value := -1;
1523 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1524 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1525 END IF;
1526
1527 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1528 l_ld_list(l_node_counter).NODE_TYPE := 0;
1529 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1530 l_ld_list(l_node_counter).NODE_DEPTH := 1;
1531
1532
1533 l_node_counter := l_node_counter + 1;
1534 --l_check := 0;
1535
1536 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1537 l_EnableNodes := NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
1538
1539 if (l_EnableNodes <> 'N') then
1540 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1541
1542 FOR cur_rec IN c_del_new_nodes LOOP
1543 IF l_strategy_level = 'ACCOUNT' THEN
1544 IF (cur_rec.lookup_code = 'ACTIVE') THEN
1545 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND ACTIVE_DELINQUENCIES IS NOT NULL ';
1546 l_data_source := 'IEX_ACC_DLN_ACT_UWQ';
1547
1548 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
1549 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND PENDING_DELINQUENCIES IS NOT NULL ';
1550 l_data_source := 'IEX_ACC_DLN_PEND_UWQ';
1551
1552 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
1553 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND COMPLETE_DELINQUENCIES IS NOT NULL ';
1554 l_data_source := 'IEX_ACC_DLN_COMP_UWQ';
1555
1556 END IF;
1557
1558 -- Territory Assignment Change
1559 --Begin bug#5874874 gnramasa 25-Apr-2007
1560 /* IF l_Level = 'PARTY' then
1561 If (l_check > 0) then
1562 l_security_where :=
1563 'party_id in (select hp.party_id '||
1564 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
1565 ' iex_assignments iea ' ||
1566 ' WHERE hp.collector_id = ac.collector_id ' ||
1567 ' AND hp.cust_account_id = -1 ' ||
1568 ' AND ac.resource_id = iea.resource_id ' ||
1569 ' AND iea.alt_employee_id = :PERSON_ID ' ||
1570 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
1571 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
1572 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
1573 ' UNION ALL select hp.party_id '||
1574 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1575 ' WHERE hp.collector_id = ac.collector_id ' ||
1576 ' AND hp.cust_account_id = -1 ' ||
1577 ' AND ac.employee_id = :PERSON_ID ' ||
1578 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1579 ' UNION ALL SELECT hp.party_id ' ||
1580 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1581 ' WHERE hp.collector_id = ac.collector_id ' ||
1582 ' AND ac.resource_ID = jtgrp.group_id ' ||
1583 ' AND hp.cust_account_id = -1 ' ||
1584 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1585 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1586 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1587 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1588 l_security_where := l_security_where || l_additional_where2;
1589 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1590
1591 Else
1592 l_security_where :=
1593 'party_id in (select hp.party_id '||
1594 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1595 ' WHERE hp.collector_id = ac.collector_id ' ||
1596 ' AND hp.cust_account_id = -1 ' ||
1597 ' AND ac.employee_id = :PERSON_ID ' ||
1598 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1599 ' UNION ALL SELECT hp.party_id ' ||
1600 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1601 ' WHERE hp.collector_id = ac.collector_id ' ||
1602 ' AND ac.resource_ID = jtgrp.group_id ' ||
1603 ' AND hp.cust_account_id = -1 ' ||
1604 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1605 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1606 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1607 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1608 l_security_where := l_security_where || l_additional_where2;
1609 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1610
1611 End If;
1612 ELSE
1613 If (l_check > 0) then
1614 l_security_where :=
1615 'party_id in (select hp.party_id '||
1616 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
1617 ' iex_assignments iea ' ||
1618 ' WHERE hp.collector_id = ac.collector_id ' ||
1619 ' AND ' || l_view_name ||
1620 '.cust_account_id = hp.cust_account_id ' ||
1621 ' AND ac.resource_id = iea.resource_id ' ||
1622 ' AND hp.site_use_id is NULL ' ||
1623 ' AND iea.alt_employee_id = :PERSON_ID ' ||
1624 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
1625 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
1626 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
1627 ' UNION ALL select hp.party_id '||
1628 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1629 ' WHERE hp.collector_id = ac.collector_id ' ||
1630 ' AND ' || l_view_name ||
1631 '.cust_account_id = hp.cust_account_id ' ||
1632 ' AND hp.site_use_id is NULL ' ||
1633 ' AND ac.employee_id = :PERSON_ID '||
1634 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1635 ' UNION ALL SELECT hp.party_id ' ||
1636 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1637 ' WHERE hp.collector_id = ac.collector_id ' ||
1638 ' AND ac.resource_ID = jtgrp.group_id ' ||
1639 ' AND ' || l_view_name ||
1640 '.cust_account_id = hp.cust_account_id ' ||
1641 ' AND hp.site_use_id is NULL ' ||
1642 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1643 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1644 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1645 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1646 l_security_where := l_security_where || l_additional_where1;
1647 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1648
1649 Else
1650 l_security_where :=
1651 'party_id in (select hp.party_id '||
1652 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
1653 ' WHERE hp.collector_id = ac.collector_id ' ||
1654 ' AND ' || l_view_name ||
1655 '.cust_account_id = hp.cust_account_id ' ||
1656 ' AND hp.site_use_id is NULL ' ||
1657 ' AND ac.employee_id = :PERSON_ID '||
1658 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
1659 ' UNION ALL SELECT hp.party_id ' ||
1660 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
1661 ' WHERE hp.collector_id = ac.collector_id ' ||
1662 ' AND ac.resource_ID = jtgrp.group_id ' ||
1663 ' AND ' || l_view_name ||
1664 '.cust_account_id = hp.cust_account_id ' ||
1665 ' AND hp.site_use_id is NULL ' ||
1666 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1667 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1668 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
1669 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1670 l_security_where := l_security_where || l_additional_where1;
1671 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1672
1673 END IF;
1674 END IF; */
1675
1676 if l_check>0 or l_group_check>0 then
1677 --l_security_where := ' :person_id = :person_id and collector_resource_id in (select :COLLECTOR_RESOURCE_ID from dual ';
1678 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 ';
1679
1680 else
1681 l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
1682 end if;
1683
1684 if l_check>0 then
1685 l_security_where := l_security_where || ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
1686 ' iea.alt_employee_id = :PERSON_ID '||
1687 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
1688 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
1689 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
1690 ' and ac.resource_id=iea.resource_id '||
1691 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') ';
1692 end if;
1693
1694 if l_group_check>0 then
1695 l_security_where := l_security_where || ' union all SELECT ac.resource_ID '||
1696 ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
1697 ' WHERE ac.resource_ID = jtgrp.group_id '||
1698 ' AND ac.resource_type = ''RS_GROUP'''||
1699 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
1700 ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
1701 end if;
1702
1703 if l_check>0 or l_group_check>0 then
1704 l_security_where := l_security_where || ' ) ';
1705 end if;
1706 --End bug#5874874 gnramasa 25-Apr-2007
1707
1708 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1709 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1710 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1711 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1712
1713 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
1714 l_bind_list(1).bind_var_value := 1;
1715 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1716
1717 IF ( l_access in ('P', 'F')) THEN
1718 IF l_bkr_filter = 'Y' THEN
1719 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_bkr;
1720 ELSE
1721 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where;
1722 END IF;
1723
1724 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1725
1726 ELSE
1727 /* No count view when the security is enabled */
1728 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
1729
1730 IF l_bkr_filter = 'Y' THEN
1731 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_and || l_security_where || l_str_bkr;
1732 ELSE
1733 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_and || l_security_where;
1734 END IF;
1735
1736 l_bind_list(2).bind_var_name := ':PERSON_ID' ;
1737 l_bind_list(2).bind_var_value := l_person_id;
1738 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
1739
1740 l_bind_list(3).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
1741 l_bind_list(3).bind_var_value := p_resource_id;
1742 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
1743
1744 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1745 END IF;
1746
1747 ELSE /* l_strategy_level <> 'ACCOUNT' THEN */
1748 IF (cur_rec.lookup_code = 'ACTIVE') THEN
1749 l_node_where := l_default_where;
1750 l_data_source := 'IEX_ACC_DLN_ACT_UWQ';
1751 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
1752 l_node_where := l_default_where;
1753 l_data_source := 'IEX_ACC_DLN_PEND_UWQ';
1754 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
1755 l_node_where := l_default_where;
1756 l_data_source := 'IEX_ACC_DLN_COMP_UWQ';
1757 END IF;
1758
1759 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
1760 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
1761 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
1762 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
1763
1764 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
1765 l_bind_list(1).bind_var_value := -1;
1766 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
1767
1768 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
1769 END IF;
1770
1771 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
1772 l_ld_list(l_node_counter).NODE_TYPE := 0;
1773 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
1774 l_ld_list(l_node_counter).NODE_DEPTH := 2;
1775
1776 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
1777
1778 l_node_counter := l_node_counter + 1;
1779 -- l_check := 0;
1780 END LOOP;
1781 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1782 end if;
1783 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1784
1785
1786 IEU_PUB.ADD_UWQ_NODE_DATA
1787 (P_RESOURCE_ID,
1788 P_SEL_ENUM_ID,
1789 l_ld_list
1790 );
1791
1792
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 -- ROLLBACK TO start_delin_enumeration;
1796 RAISE;
1797
1798 END ENUMERATE_ACC_DELIN_NODES;
1799
1800 -- added by jypark 10/11/2004 for performance
1801
1802 PROCEDURE ENUMERATE_BILLTO_DELIN_NODES
1803 (P_RESOURCE_ID IN NUMBER
1804 ,P_LANGUAGE IN VARCHAR2
1805 ,P_SOURCE_LANG IN VARCHAR2
1806 ,P_SEL_ENUM_ID IN NUMBER
1807 )
1808 AS
1809
1810 l_node_label VARCHAR2(200);
1811 l_ld_list IEU_PUB.EnumeratorDataRecordList;
1812
1813 l_node_counter NUMBER;
1814 l_bind_list IEU_PUB.BindVariableRecordList ;
1815 --Territory Assignment Change
1816 l_Access varchar2(10);
1817 l_Level varchar2(15);
1818 l_check NUMBER;
1819 l_collector_id NUMBER;
1820 l_person_id NUMBER;
1821
1822
1823 CURSOR c_person IS
1824 select source_id
1825 from jtf_rs_resource_extns
1826 where resource_id = p_resource_id;
1827
1828 CURSOR c_del_new_nodes is
1829 select lookup_code, meaning from fnd_lookup_values
1830 where lookup_type = 'IEX_UWQ_NODE_STATUS' and LANGUAGE = userenv('LANG');
1831
1832 CURSOR c_node_label(in_lookup_type VARCHAR2,in_lookup_code VARCHAR2) IS
1833 SELECT meaning
1834 FROM fnd_lookup_values
1835 WHERE lookup_type = in_lookup_type
1836 AND lookup_code = in_lookup_code
1837 AND LANGUAGE = userenv('LANG');
1838
1839 CURSOR c_sel_enum(in_sel_enum_id NUMBER) IS
1840 SELECT work_q_view_for_primary_node, work_q_label_lu_type, work_q_label_lu_code
1841 FROM ieu_uwq_sel_enumerators
1842 WHERE sel_enum_id = in_sel_enum_id;
1843
1844 CURSOR c_collector_id IS
1845 SELECT collector_id from AR_COLLECTORS where resource_id = p_resource_id
1846 and resource_type = 'RS_RESOURCE';
1847
1848 l_sel_enum_rec c_sel_enum%ROWTYPE;
1849
1850 l_Complete_Days varchar2(40);
1851 l_data_source VARCHAR2(1000);
1852 l_default_where VARCHAR2(1000);
1853 l_security_where VARCHAR2(2000);
1854 l_node_where VARCHAR2(2000);
1855 l_uwq_where VARCHAR2(1000);
1856 l_org_id NUMBER;
1857
1858 type tbl_wclause is table of varchar2(500) index by binary_integer;
1859
1860 l_str_and VARCHAR2(100);
1861 l_str_del VARCHAR2(1000);
1862 l_str_bkr VARCHAR2(1000);
1863
1864 l_bkr_filter VARCHAR2(240);
1865 l_view_name VARCHAR2(240);
1866 l_refresh_view_name VARCHAR2(240);
1867 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
1868 l_EnableNodes varchar2(10);
1869 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-node
1870
1871 l_additional_where1 VARCHAR2(2000);
1872 l_additional_where2 VARCHAR2(2000);
1873
1874 L VARCHAR2(240);
1875
1876
1877 CURSOR C_ORG_ID IS SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS
1878 WHERE MO_GLOBAL.CHECK_ACCESS(ORGANIZATION_ID) = 'Y';
1879
1880 CURSOR c_strategy_level IS
1881 SELECT PREFERENCE_VALUE
1882 FROM IEX_APP_PREFERENCES_B
1883 WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL'
1884 and org_id is null
1885 and enabled_flag='Y';
1886
1887 l_strategy_level VARCHAR2(30);
1888 l_group_check number;
1889
1890 --begin bug#6717849 schekuri 31-Jul-2009
1891 l_level_count number;
1892 cursor c_multi_level
1893 is select lookup_code
1894 from iex_lookups_v
1895 where lookup_type='IEX_RUNNING_LEVEL'
1896 and lookup_code= 'BILL_TO'
1897 and iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1898 --end bug#6717849 schekuri 31-Jul-2009
1899
1900 BEGIN
1901
1902 SET_MO_GLOBAL;
1903
1904
1905 l_Access := NVL(FND_PROFILE.VALUE('IEX_CUST_ACCESS'), 'F');
1906 l_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
1907 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
1908 l_bkr_filter := NVL(fnd_profile.value('IEX_BANKRUPTCY_FILTER'), 'Y');
1909 l_str_and := ' AND ';
1910 l_str_del := ' AND NUMBER_OF_DELINQUENCIES > 0 ';
1911
1912 l_node_counter := 0;
1913 l_check := 0;
1914
1915 OPEN c_sel_enum(p_sel_enum_id);
1916 FETCH c_sel_enum INTO l_sel_enum_rec;
1917 CLOSE c_sel_enum;
1918
1919 OPEN c_node_label(l_sel_enum_rec.work_q_label_lu_type, l_sel_enum_rec.work_q_label_lu_code);
1920 FETCH c_node_label INTO l_node_label;
1921 CLOSE c_node_label;
1922
1923 OPEN c_strategy_level;
1924 FETCH c_strategy_level INTO l_strategy_level;
1925 CLOSE c_strategy_level;
1926
1927 --begin bug#6717849 schekuri 31-Jul-2009
1928 select count(1)
1929 into l_level_count
1930 from iex_lookups_v
1931 where lookup_type='IEX_RUNNING_LEVEL'
1932 and iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1933
1934 if l_level_count>1 then
1935 open c_multi_level;
1936 fetch c_multi_level into l_strategy_level;
1937 close c_multi_level;
1938 end if;
1939 --end bug#6717849 schekuri 31-Jul-2009
1940
1941 l_data_source := 'IEX_BILLTO_DLN_ALL_UWQ';
1942 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
1943 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
1944
1945 IF l_strategy_level = 'BILL_TO' THEN
1946 l_str_bkr := ' AND NUMBER_OF_BANKRUPTCIES = 0 ';
1947
1948 l_default_where := ' RESOURCE_ID = :RESOURCE_ID and IEU_PARAM_PK_COL=''CUSTOMER_SITE_USE_ID'' ';
1949
1950 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
1951 /* l_additional_where1 :=
1952 ' UNION ALL SELECT hp.party_id ' ||
1953 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp, ' ||
1954 ' JTF_RS_GROUPS_DENORM jrg ' ||
1955 ' WHERE hp.collector_id = ac.collector_id ' ||
1956 ' AND ac.resource_ID = jtgrp.group_id ' ||
1957 ' AND jtgrp.group_id = jrg.group_id ' ||
1958 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1959 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1960 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';
1961
1962 l_additional_where2 :=
1963 ' UNION ALL SELECT hp.party_id ' ||
1964 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp, ' ||
1965 ' JTF_RS_GROUPS_DENORM jrg ' ||
1966 ' WHERE hp.collector_id = ac.collector_id ' ||
1967 ' AND ac.resource_ID = jtgrp.group_id ' ||
1968 ' AND jtgrp.group_id = jrg.group_id ' ||
1969 ' AND hp.cust_account_id = -1 ' ||
1970 ' AND ac.resource_type = ''RS_GROUP'' ' ||
1971 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
1972 ' AND jtgrp.PERSON_ID = :PERSON_ID ) ';
1973 */
1974 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
1975
1976
1977 IF (l_Access = 'T') THEN
1978 OPEN c_collector_id;
1979 FETCH c_collector_id INTO l_collector_id;
1980 CLOSE c_collector_id;
1981
1982 begin
1983 SELECT count(*) INTO l_check FROM iex_assignments
1984 where alt_resource_id = p_RESOURCE_ID
1985 AND TRUNC(START_DATE) <= TRUNC(SYSDATE)
1986 AND TRUNC(NVL(END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
1987 --Bug4646657 . Check for Deleted flag .Fixed by lkkumar. Start.
1988 AND NVL(DELETED_FLAG,'N') = 'N';
1989 --Bug4646657 . Check for Deleted flag .Fixed by lkkumar. End.
1990 exception
1991 when others then l_check := 0;
1992 end;
1993
1994 begin
1995 select count(1) into l_group_check
1996 from ar_collectors where status='A' and
1997 nvl(inactive_date,sysdate)>=sysdate and resource_type='RS_GROUP';
1998 exception
1999 when others then l_group_check := 0;
2000 end;
2001
2002 END IF;
2003
2004 --Begin bug#5874874 gnramasa 25-Apr-2007
2005 /* IF l_Level = 'PARTY' then
2006 If (l_check > 0) then
2007 l_security_where :=
2008 'party_id in (select hp.party_id '||
2009 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
2010 ' iex_assignments iea ' ||
2011 ' WHERE hp.collector_id = ac.collector_id ' ||
2012 ' AND hp.cust_account_id = -1 ' ||
2013 ' AND ac.resource_id = iea.resource_id ' ||
2014 ' AND iea.alt_employee_id = :PERSON_ID ' ||
2015 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
2016 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
2017 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' '||
2018 ' UNION ALL select hp.party_id '||
2019 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2020 ' WHERE hp.collector_id = ac.collector_id ' ||
2021 ' AND hp.cust_account_id = -1 ' ||
2022 ' AND ac.employee_id = :PERSON_ID ' ||
2023 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2024 ' UNION ALL SELECT hp.party_id ' ||
2025 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2026 ' WHERE hp.collector_id = ac.collector_id ' ||
2027 ' AND ac.resource_ID = jtgrp.group_id ' ||
2028 ' AND hp.cust_account_id = -1 ' ||
2029 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2030 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2031 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2032 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2033 l_security_where := l_security_where || l_additional_where2;
2034 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2035 Else
2036 l_security_where :=
2037 'party_id in (select hp.party_id '||
2038 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2039 ' WHERE hp.collector_id = ac.collector_id ' ||
2040 ' AND hp.cust_account_id = -1 ' ||
2041 ' AND ac.employee_id = :PERSON_ID ' ||
2042 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2043 ' UNION ALL SELECT hp.party_id ' ||
2044 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2045 ' WHERE hp.collector_id = ac.collector_id ' ||
2046 ' AND ac.resource_ID = jtgrp.group_id ' ||
2047 ' AND hp.cust_account_id = -1 ' ||
2048 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2049 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2050 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2051 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2052 l_security_where := l_security_where || l_additional_where2;
2053 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2054 End If;
2055 ELSIF l_level = 'ACCOUNT' then
2056 If (l_check > 0) then
2057 l_security_where :=
2058 'party_id in (select hp.party_id '||
2059 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
2060 ' iex_assignments iea ' ||
2061 ' WHERE hp.collector_id = ac.collector_id ' ||
2062 ' AND ' || l_view_name ||
2063 '.cust_account_id = hp.cust_account_id ' ||
2064 ' AND ac.resource_id = iea.resource_id ' ||
2065 ' AND hp.site_use_id is NULL '||
2066 ' AND iea.alt_employee_id = :PERSON_ID ' ||
2067 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
2068 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
2069 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' '||
2070 ' UNION ALL select hp.party_id '||
2071 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2072 ' WHERE hp.collector_id = ac.collector_id ' ||
2073 ' AND ' || l_view_name ||
2074 '.cust_account_id = hp.cust_account_id ' ||
2075 ' AND hp.site_use_id is NULL '||
2076 ' AND ac.employee_id = :PERSON_ID ' ||
2077 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2078 ' UNION ALL SELECT hp.party_id ' ||
2079 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2080 ' WHERE hp.collector_id = ac.collector_id ' ||
2081 ' AND ac.resource_ID = jtgrp.group_id ' ||
2082 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2083 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2084 ' AND ' || l_view_name ||
2085 '.cust_account_id = hp.cust_account_id ' ||
2086 ' AND hp.site_use_id is NULL '||
2087 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2088 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2089 l_security_where := l_security_where || l_additional_where1;
2090 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2091 Else
2092 l_security_where :=
2093 'party_id in (select hp.party_id '||
2094 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2095 ' WHERE hp.collector_id = ac.collector_id ' ||
2096 ' AND ' || l_view_name ||
2097 '.cust_account_id = hp.cust_account_id ' ||
2098 ' AND hp.site_use_id is NULL '||
2099 ' AND ac.employee_id = :PERSON_ID ' ||
2100 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2101 ' UNION ALL SELECT hp.party_id ' ||
2102 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2103 ' WHERE hp.collector_id = ac.collector_id ' ||
2104 ' AND ac.resource_ID = jtgrp.group_id ' ||
2105 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2106 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2107 ' AND ' || l_view_name ||
2108 '.cust_account_id = hp.cust_account_id ' ||
2109 ' AND hp.site_use_id is NULL '||
2110 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2111 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2112 l_security_where := l_security_where || l_additional_where1;
2113 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2114 End If;
2115 Else
2116 If (l_check > 0) then
2117 l_security_where :=
2118 'party_id in (select hp.party_id '||
2119 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
2120 ' iex_assignments iea ' ||
2121 ' WHERE hp.collector_id = ac.collector_id ' ||
2122 ' AND ' || l_view_name ||
2123 '.site_use_id = hp.site_use_id ' ||
2124 ' AND ac.resource_id = iea.resource_id ' ||
2125 ' AND iea.alt_employee_id = :PERSON_ID ' ||
2126 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
2127 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
2128 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
2129 ' UNION ALL select hp.party_id '||
2130 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2131 ' WHERE hp.collector_id = ac.collector_id ' ||
2132 ' AND ' || l_view_name ||
2133 '.site_use_id = hp.site_use_id ' ||
2134 ' AND ac.employee_id = :PERSON_ID ' ||
2135 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2136 ' UNION ALL SELECT hp.party_id ' ||
2137 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2138 ' WHERE hp.collector_id = ac.collector_id ' ||
2139 ' AND ' || l_view_name ||
2140 '.site_use_id = hp.site_use_id ' ||
2141 ' AND ac.resource_ID = jtgrp.group_id ' ||
2142 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2143 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2144 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2145 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2146 l_security_where := l_security_where || l_additional_where1;
2147 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2148 Else
2149 l_security_where :=
2150 'party_id in (select hp.party_id '||
2151 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2152 ' WHERE hp.collector_id = ac.collector_id ' ||
2153 ' AND ' || l_view_name ||
2154 '.site_use_id = hp.site_use_id ' ||
2155 ' AND ac.employee_id = :PERSON_ID ' ||
2156 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2157 ' UNION ALL SELECT hp.party_id ' ||
2158 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2159 ' WHERE hp.collector_id = ac.collector_id ' ||
2160 ' AND ' || l_view_name ||
2161 '.site_use_id = hp.site_use_id ' ||
2162 ' AND ac.resource_ID = jtgrp.group_id ' ||
2163 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2164 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2165 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2166 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2167 l_security_where := l_security_where || l_additional_where1;
2168 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2169 End If;
2170 END IF; */
2171
2172 if l_check>0 or l_group_check>0 then
2173 --l_security_where := ' :person_id = :person_id and collector_resource_id in (select :COLLECTOR_RESOURCE_ID from dual ';
2174 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 ';
2175 else
2176 l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
2177 end if;
2178
2179 if l_check>0 then
2180 l_security_where := l_security_where || ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
2181 ' iea.alt_employee_id = :PERSON_ID '||
2182 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
2183 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
2184 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
2185 ' and ac.resource_id=iea.resource_id '||
2186 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') ';
2187 end if;
2188
2189 if l_group_check>0 then
2190 l_security_where := l_security_where || ' union all SELECT ac.resource_ID '||
2191 ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
2192 ' WHERE ac.resource_ID = jtgrp.group_id '||
2193 ' AND ac.resource_type = ''RS_GROUP'''||
2194 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
2195 ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
2196 end if;
2197
2198 if l_check>0 or l_group_check>0 then
2199 l_security_where := l_security_where || ' ) ';
2200 end if;
2201 -- End Bug#5874874 gnramasa 25-Apr-2007
2202
2203 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
2204 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
2205 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
2206 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
2207 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
2208
2209 l_bind_list(1).bind_var_name := ':RESOURCE_ID';
2210 l_bind_list(1).bind_var_value := 1;
2211 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
2212
2213 IF ( l_access in ('F', 'P')) THEN
2214 IF l_bkr_filter = 'Y' THEN
2215 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_del || l_str_bkr;
2216 ELSE
2217 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_del;
2218 END IF;
2219
2220 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
2221 ELSE
2222 /* No count view when the security is enabled */
2223 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
2224
2225 OPEN c_person;
2226 FETCH c_person INTO l_person_id;
2227 CLOSE c_person;
2228
2229 l_bind_list(2).bind_var_name := ':PERSON_ID' ;
2230 l_bind_list(2).bind_var_value := l_person_id;
2231 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
2232
2233 l_bind_list(3).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
2234 l_bind_list(3).bind_var_value := p_resource_id;
2235 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
2236
2237 IF l_bkr_filter = 'Y' THEN
2238 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_del || l_str_bkr;
2239 ELSE
2240 l_ld_list(l_node_counter).WHERE_CLAUSE := l_default_where || l_str_and || l_security_where || l_str_del;
2241 END IF;
2242
2243 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
2244 END IF;
2245 ELSE /* IF l_strategy_level <> 'BILL_TO' */
2246
2247 l_ld_list(l_node_counter).NODE_LABEL := l_node_label;
2248 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
2249 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
2250 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
2251 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
2252
2253 l_ld_list(l_node_counter).WHERE_CLAUSE := 'RESOURCE_ID = :RESOURCE_ID';
2254 l_bind_list(1).bind_var_name := ':RESOURCE_ID';
2255 l_bind_list(1).bind_var_value := -1;
2256 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
2257 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
2258 END IF;
2259
2260 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
2261 l_ld_list(l_node_counter).NODE_TYPE := 0;
2262 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
2263 l_ld_list(l_node_counter).NODE_DEPTH := 1;
2264
2265
2266 l_node_counter := l_node_counter + 1;
2267 --l_check := 0;
2268
2269 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
2270 l_EnableNodes := NVL(FND_PROFILE.VALUE('IEX_ENABLE_UWQ_STATUS'),'N');
2271
2272 if (l_EnableNodes <> 'N') then
2273 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
2274
2275 FOR cur_rec IN c_del_new_nodes LOOP
2276 IF l_strategy_level = 'BILL_TO' THEN
2277 IF (cur_rec.lookup_code = 'ACTIVE') THEN
2278 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND ACTIVE_DELINQUENCIES IS NOT NULL ';
2279 l_data_source := 'IEX_BILLTO_DLN_ACT_UWQ';
2280 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
2281 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
2282
2283 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
2284 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND PENDING_DELINQUENCIES IS NOT NULL ';
2285 l_data_source := 'IEX_BILLTO_DLN_PEND_UWQ';
2286 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
2287 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
2288
2289 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
2290 l_node_where := l_default_where || ' AND NUMBER_OF_DELINQUENCIES > 0 AND COMPLETE_DELINQUENCIES IS NOT NULL ';
2291 l_data_source := 'IEX_BILLTO_DLN_COMP_UWQ';
2292 l_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
2293 l_refresh_view_name := 'IEX_CU_DLN_ALL_UWQ_V';
2294
2295 END IF;
2296 --Begin bug#5874874 gnramasa 25-Apr-2007
2297 /*
2298 IF l_Level = 'PARTY' then
2299 If (l_check > 0) then
2300 l_security_where :=
2301 'party_id in (select hp.party_id '||
2302 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
2303 ' iex_assignments iea ' ||
2304 ' WHERE hp.collector_id = ac.collector_id ' ||
2305 ' AND hp.cust_account_id = -1 ' ||
2306 ' AND ac.resource_id = iea.resource_id ' ||
2307 ' AND iea.alt_employee_id = :PERSON_ID ' ||
2308 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
2309 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
2310 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
2311 ' UNION ALL select hp.party_id '||
2312 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2313 ' WHERE hp.collector_id = ac.collector_id ' ||
2314 ' AND hp.cust_account_id = -1 ' ||
2315 ' AND ac.employee_id = :PERSON_ID ' ||
2316 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2317 ' UNION ALL SELECT hp.party_id ' ||
2318 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2319 ' WHERE hp.collector_id = ac.collector_id ' ||
2320 ' AND ac.resource_ID = jtgrp.group_id ' ||
2321 ' AND hp.cust_account_id = -1 ' ||
2322 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2323 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2324 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2325 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2326 l_security_where := l_security_where || l_additional_where2;
2327 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2328 Else
2329 l_security_where :=
2330 'party_id in (select hp.party_id '||
2331 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2332 ' WHERE hp.collector_id = ac.collector_id ' ||
2333 ' AND hp.cust_account_id = -1 ' ||
2334 ' AND ac.employee_id = :PERSON_ID ' ||
2335 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2336 ' UNION ALL SELECT hp.party_id ' ||
2337 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2338 ' WHERE hp.collector_id = ac.collector_id ' ||
2339 ' AND ac.resource_ID = jtgrp.group_id ' ||
2340 ' AND hp.cust_account_id = -1 ' ||
2341 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2342 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2343 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2344 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2345 l_security_where := l_security_where || l_additional_where2;
2346 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2347 End If;
2348 ELSIF l_level = 'ACCOUNT' then
2349 If (l_check > 0) then
2350 l_security_where :=
2351 'party_id in (select hp.party_id '||
2352 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
2353 ' iex_assignments iea ' ||
2354 ' WHERE hp.collector_id = ac.collector_id ' ||
2355 ' AND ' || l_view_name ||
2356 '.cust_account_id = hp.cust_account_id ' ||
2357 ' AND ac.resource_id = iea.resource_id ' ||
2358 ' AND hp.site_use_id is NULL '||
2359 ' AND iea.alt_employee_id = :PERSON_ID ' ||
2360 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
2361 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
2362 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
2363 ' UNION ALL select hp.party_id '||
2364 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2365 ' WHERE hp.collector_id = ac.collector_id ' ||
2366 ' AND ' || l_view_name ||
2367 '.cust_account_id = hp.cust_account_id ' ||
2368 ' AND ac.employee_id = :PERSON_ID ' ||
2369 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2370 ' UNION ALL SELECT hp.party_id ' ||
2371 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2372 ' WHERE hp.collector_id = ac.collector_id ' ||
2373 ' AND ac.resource_ID = jtgrp.group_id ' ||
2374 ' AND ' || l_view_name ||
2375 '.cust_account_id = hp.cust_account_id ' ||
2376 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2377 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2378 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2379 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2380 l_security_where := l_security_where || l_additional_where1;
2381 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2382 Else
2383 l_security_where :=
2384 'party_id in (select hp.party_id '||
2385 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2386 ' WHERE hp.collector_id = ac.collector_id ' ||
2387 ' AND ' || l_view_name ||
2388 '.cust_account_id = hp.cust_account_id ' ||
2389 ' AND ac.employee_id = :PERSON_ID ' ||
2390 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2391 ' UNION ALL SELECT hp.party_id ' ||
2392 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2393 ' WHERE hp.collector_id = ac.collector_id ' ||
2394 ' AND ac.resource_ID = jtgrp.group_id ' ||
2395 ' AND ' || l_view_name ||
2396 '.cust_account_id = hp.cust_account_id ' ||
2397 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2398 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2399 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2400 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2401 l_security_where := l_security_where || l_additional_where1;
2402 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2403 End If;
2404 Else
2405 If (l_check > 0) then
2406 l_security_where :=
2407 'party_id in (select hp.party_id '||
2408 ' FROM hz_customer_profiles hp, ar_collectors ac, ' ||
2409 ' iex_assignments iea ' ||
2410 ' WHERE hp.collector_id = ac.collector_id ' ||
2411 ' AND ' || l_view_name ||
2412 '.site_use_id = hp.site_use_id ' ||
2413 ' AND ac.resource_id = iea.resource_id ' ||
2414 ' AND iea.alt_employee_id = :PERSON_ID ' ||
2415 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) ' ||
2416 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) ' ||
2417 ' AND NVL(IEA.DELETED_FLAG,''N'') = ''N'' ' ||
2418 ' UNION ALL select hp.party_id '||
2419 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2420 ' WHERE hp.collector_id = ac.collector_id ' ||
2421 ' AND ' || l_view_name ||
2422 '.site_use_id = hp.site_use_id ' ||
2423 ' AND ac.employee_id = :PERSON_ID ' ||
2424 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2425 ' UNION ALL SELECT hp.party_id ' ||
2426 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2427 ' WHERE hp.collector_id = ac.collector_id ' ||
2428 ' AND ac.resource_ID = jtgrp.group_id ' ||
2429 ' AND ' || l_view_name ||
2430 '.site_use_id = hp.site_use_id ' ||
2431 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2432 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2433 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2434 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2435 l_security_where := l_security_where || l_additional_where1;
2436 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2437
2438 Else
2439 l_security_where :=
2440 'party_id in (select hp.party_id '||
2441 ' FROM hz_customer_profiles hp, ar_collectors ac ' ||
2442 ' WHERE hp.collector_id = ac.collector_id ' ||
2443 ' AND ' || l_view_name ||
2444 '.site_use_id = hp.site_use_id ' ||
2445 ' AND ac.employee_id = :PERSON_ID ' ||
2446 ' AND ac.resource_type = ''RS_RESOURCE'' ' ||
2447 ' UNION ALL SELECT hp.party_id ' ||
2448 ' FROM hz_customer_profiles hp, ar_collectors ac , jtf_rs_group_members jtgrp ' ||
2449 ' WHERE hp.collector_id = ac.collector_id ' ||
2450 ' AND ac.resource_ID = jtgrp.group_id ' ||
2451 ' AND ' || l_view_name ||
2452 '.site_use_id = hp.site_use_id ' ||
2453 ' AND ac.resource_type = ''RS_GROUP'' ' ||
2454 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'' ' ||
2455 ' AND jtgrp.PERSON_ID = :PERSON_ID ';
2456 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. Start.
2457 l_security_where := l_security_where || l_additional_where1;
2458 --Bug4221349. Implement Group Hierarchy. Fix By LKKUMAR on 22-Mar-2006. End.
2459 End If;
2460 END IF;
2461 */
2462
2463 if l_check>0 or l_group_check>0 then
2464 --l_security_where := ' :person_id = :person_id and collector_resource_id in (select :COLLECTOR_RESOURCE_ID from dual ';
2465 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 ';
2466
2467 else
2468 l_security_where := ' :person_id = :person_id and collector_resource_id = :COLLECTOR_RESOURCE_ID ';
2469 end if;
2470
2471 if l_check>0 then
2472 l_security_where := l_security_where || ' union all SELECT ac.resource_id FROM iex_assignments iea,ar_collectors ac where '||
2473 ' iea.alt_employee_id = :PERSON_ID '||
2474 ' AND TRUNC(iea.START_DATE) <= TRUNC(SYSDATE) '||
2475 ' AND TRUNC(NVL(iea.END_DATE,SYSDATE)) >= TRUNC(SYSDATE) '||
2476 ' AND NVL(iea.DELETED_FLAG,''N'') = ''N'' '||
2477 ' and ac.resource_id=iea.resource_id '||
2478 ' and ac.resource_type in (''RS_RESOURCE'',''RS_GROUP'') '; --Bug#5691908 replaced RS_EMPLOYEE with RS_RESOURCE by schekuri 02-Feb-2007
2479 end if;
2480
2481 if l_group_check>0 then
2482 l_security_where := l_security_where || ' union all SELECT ac.resource_ID '||
2483 ' FROM ar_collectors ac , jtf_rs_group_members jtgrp '||
2484 ' WHERE ac.resource_ID = jtgrp.group_id '||
2485 ' AND ac.resource_type = ''RS_GROUP'''||
2486 ' AND NVL(jtgrp.delete_flag,''N'') = ''N'''||
2487 ' AND jtgrp.resource_ID = :COLLECTOR_RESOURCE_ID ';
2488 end if;
2489
2490 if l_check>0 or l_group_check>0 then
2491 l_security_where := l_security_where || ' ) ';
2492 end if;
2493 --End bug#5874874 gnramasa 25-Apr-2007
2494
2495 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
2496 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
2497 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
2498 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
2499
2500 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
2501 l_bind_list(1).bind_var_value := 1;
2502 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
2503
2504 IF ( l_access in ('P', 'F')) THEN
2505 IF l_bkr_filter = 'Y' THEN
2506 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_bkr;
2507 ELSE
2508 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where;
2509 END IF;
2510
2511 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
2512
2513 ELSE
2514 /* No count view when the security is enabled */
2515 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_view_name;
2516
2517 IF l_bkr_filter = 'Y' THEN
2518 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_and || l_security_where || l_str_bkr;
2519 ELSE
2520 l_ld_list(l_node_counter).WHERE_CLAUSE := l_node_where || l_str_and || l_security_where;
2521 END IF;
2522
2523 l_bind_list(2).bind_var_name := ':PERSON_ID' ;
2524 l_bind_list(2).bind_var_value := l_person_id;
2525 l_bind_list(2).bind_var_data_type := 'NUMBER' ;
2526
2527 l_bind_list(3).bind_var_name := ':COLLECTOR_RESOURCE_ID' ;
2528 l_bind_list(3).bind_var_value := p_resource_id;
2529 l_bind_list(3).bind_var_data_type := 'NUMBER' ;
2530
2531 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
2532 END IF;
2533 ELSE /* l_strategy_level <> 'BILL_TO' THEN */
2534 IF (cur_rec.lookup_code = 'ACTIVE') THEN
2535 l_node_where := l_default_where;
2536 l_data_source := 'IEX_ACC_DLN_ACT_UWQ';
2537 ELSIF (cur_rec.lookup_code = 'PENDING') THEN
2538 l_node_where := l_default_where;
2539 l_data_source := 'IEX_ACC_DLN_PEND_UWQ';
2540 ELSIF (cur_rec.lookup_code = 'COMPLETE') THEN
2541 l_node_where := l_default_where;
2542 l_data_source := 'IEX_ACC_DLN_COMP_UWQ';
2543 END IF;
2544
2545 l_ld_list(l_node_counter).NODE_LABEL := cur_rec.meaning;
2546 l_ld_list(l_node_counter).VIEW_NAME := l_view_name;
2547 l_ld_list(l_node_counter).DATA_SOURCE := l_data_source;
2548 l_ld_list(l_node_counter).REFRESH_VIEW_NAME := l_refresh_view_name;
2549
2550 l_bind_list(1).bind_var_name := ':RESOURCE_ID' ;
2551 l_bind_list(1).bind_var_value := -1;
2552 l_bind_list(1).bind_var_data_type := 'NUMBER' ;
2553
2554 l_ld_list(l_node_counter).BIND_VARS := IEU_PUB.SET_BIND_VAR_DATA(l_bind_list) ;
2555 END IF;
2556
2557 l_ld_list(l_node_counter).MEDIA_TYPE_ID := '';
2558 l_ld_list(l_node_counter).NODE_TYPE := 0;
2559 l_ld_list(l_node_counter).HIDE_IF_EMPTY := '';
2560 l_ld_list(l_node_counter).NODE_DEPTH := 2;
2561
2562 l_ld_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
2563
2564 l_node_counter := l_node_counter + 1;
2565 --l_check := 0;
2566 END LOOP;
2567 -- Begin - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
2568 end if;
2569 -- End - jypark - 05/25/05 - 4605402 - Added profile to show/hide UWQ sub-nodes
2570
2571
2572 IEU_PUB.ADD_UWQ_NODE_DATA
2573 (P_RESOURCE_ID,
2574 P_SEL_ENUM_ID,
2575 l_ld_list
2576 );
2577
2578
2579 EXCEPTION
2580 WHEN OTHERS THEN
2581 -- ROLLBACK TO start_delin_enumeration;
2582 RAISE;
2583
2584 END ENUMERATE_BILLTO_DELIN_NODES;
2585
2586
2587 BEGIN
2588
2589 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
2590
2591 END IEX_UWQ_DELIN_ENUMS_PVT;