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