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

Source


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