[Home] [Help]
PACKAGE BODY: APPS.IEX_UWQ_VIEW_PKG
Source
1 PACKAGE BODY IEX_UWQ_VIEW_PKG AS
2 /* $Header: iextuvwb.pls 120.5.12010000.6 2010/02/16 11:05:14 pnaveenk ship $ */
3 PG_DEBUG NUMBER(2);
4 g_user_id NUMBER;
5 g_person_id NUMBER;
6 g_resource_id NUMBER;
7
8 CURSOR c_get_emp(x_userid NUMBER) is
9 SELECT e.employee_id
10 FROM per_employees_current_x e, fnd_user u
11 WHERE e.employee_id = u.employee_id
12 AND u.user_id = nvl(x_userid, -1);
13
14 CURSOR c_get_resource_id(x_person_id Number, x_userid NUMBER, p_category varchar2) IS
15 SELECT res.resource_id
16 FROM jtf_rs_resource_extns res
17 WHERE res.source_id = nvl(x_person_id, -1)
18 AND res.user_id = nvl(x_userid, -1)
19 AND trunc(res.START_DATE_ACTIVE) <= trunc(SYSDATE)
20 AND trunc(SYSDATE) <= trunc(NVL(RES.END_DATE_ACTIVE,SYSDATE))
21 AND res.category = p_category;
22
23 FUNCTION get_del_count(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_uwq_status VARCHAR2) RETURN NUMBER
24 IS
25 TYPE refCur IS REF CURSOR;
26 c_del_cnt refCur;
27 l_del_clause VARCHAR2(1000);
28 l_del_cnt NUMBER := 0;
29 l_object_id NUMBER;
30 c_bnkrpt_cnt refCur;
31 l_bnkrpt_clause VARCHAR2(1000);
32 l_bnkrpt_cnt NUMBER := 0;
33 l_enable_bkr_filter VARCHAR2(40);
34 l_Complete_Days varchar2(40);
35
36 BEGIN
37 l_enable_bkr_filter := NVL(FND_PROFILE.VALUE('IEX_BANKRUPTCY_FILTER'), 'Y');
38 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
39
40 IF p_party_id > 0 THEN
41 l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE party_cust_id = :party_id ' ||
42 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
43 -- ' AND status <> ''CURRENT'' ';
44 ' AND status NOT IN (''CURRENT'', ''CLOSE'') ';
45 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
46 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt WHERE bnkrpt.party_id = :party_id';
47 l_object_id := p_party_id;
48 ELSIF p_cust_account_id > 0 THEN
49 l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE cust_account_id = :cust_account_id ' ||
50 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
51 -- ' AND status <> ''CURRENT'' ';
52 ' AND status NOT IN (''CURRENT'', ''CLOSE'') ';
53 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
54 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
55 ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';
56 l_object_id := p_cust_account_id;
57 ELSIF p_site_use_id > 0 THEN
58 l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE customer_site_use_id = :site_use_id ' ||
59 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
60 -- ' AND status <> ''CURRENT'' ';
61 ' AND status NOT IN (''CURRENT'', ''CLOSE'') ';
62 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
63 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
64 ' WHERE bnkrpt.party_id = acc.party_id ' ||
65 ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
66 ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
67 ' AND site_use.site_use_id = :site_use_id';
68
69 l_object_id := p_site_use_id;
70 END IF;
71
72 IF l_enable_bkr_filter = 'Y' THEN
73 OPEN c_bnkrpt_cnt FOR l_bnkrpt_clause USING l_object_id;
74 FETCH c_bnkrpt_cnt INTO l_bnkrpt_cnt;
75 CLOSE c_bnkrpt_cnt;
76
77 IF l_bnkrpt_cnt > 0 THEN
78 return 0;
79 END IF;
80 END IF;
81
82 IF p_uwq_status = 'ACTIVE' THEN
83 l_del_clause := l_del_clause ||
84 ' AND (UWQ_STATUS IS NULL or UWQ_STATUS = ''ACTIVE'' or ' ||
85 ' (trunc(UWQ_ACTIVE_DATE) <= trunc(SYSDATE) and UWQ_STATUS = ''PENDING'' ) )';
86 ELSIF p_uwq_status = 'PENDING' THEN
87 l_del_clause := l_del_clause ||
88 ' AND (UWQ_STATUS = ''PENDING'' and (trunc(UWQ_ACTIVE_DATE) > trunc(SYSDATE)) )';
89 ELSIF p_uwq_status = 'COMPLETE' THEN
90 l_del_clause := l_del_clause ||
91 ' AND (UWQ_STATUS = ''COMPLETE'' and (trunc(UWQ_COMPLETE_DATE) + :complete_days > trunc(SYSDATE)) )';
92 END IF;
93
94
95 IF p_uwq_status = 'COMPLETE' THEN
96 OPEN c_del_cnt FOR l_del_clause USING l_object_id, l_complete_days;
97 FETCH c_del_cnt INTO l_del_cnt;
98 CLOSE c_del_cnt;
99 ELSE
100 OPEN c_del_cnt FOR l_del_clause USING l_object_id;
101 FETCH c_del_cnt INTO l_del_cnt;
102 CLOSE c_del_cnt;
103 END IF;
104
105 RETURN l_del_cnt;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 return -1;
110 END get_del_count;
111
112 --Start bug 6634879 gnramasa 20th Nov 07
113 FUNCTION get_pro_count(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_uwq_status VARCHAR2, p_org_id NUMBER) RETURN NUMBER
114 IS
115 TYPE refCur IS REF CURSOR;
116 c_pro_cnt refCur;
117 l_pro_clause VARCHAR2(2000);
118 l_pro_cnt NUMBER := 0;
119 l_object_id NUMBER;
120 c_bnkrpt_cnt refCur;
121 l_bnkrpt_clause VARCHAR2(1000);
122 l_bnkrpt_cnt NUMBER := 0;
123 l_enable_bkr_filter VARCHAR2(40);
124 l_Complete_Days varchar2(40);
125 BEGIN
126 l_enable_bkr_filter := NVL(FND_PROFILE.VALUE('IEX_BANKRUPTCY_FILTER'), 'Y');
127 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
128
129 IF p_party_id > 0 THEN
130 l_pro_clause := 'SELECT COUNT(1) ' ||
131 'FROM (' ||
132 ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
133 ' FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies_all del ' ||
134 ' WHERE ca.cust_account_id = pro.cust_account_id ' ||
135 ' AND pro.state = ''BROKEN_PROMISE'' ' ||
136 ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
137 ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
138 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
139 -- ' AND pro.resource_id = :resource_id ' ||
140 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
141 ' AND pro.delinquency_id = del.delinquency_id '||
142 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
143 -- ' AND del.status <> ''CURRENT'' ' ||
144 ' AND (del.status NOT IN (''CURRENT'', ''CLOSE'') ' ||
145 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
146 ' OR (del.status=''CURRENT'' and del.source_program_name=''IEX_CURR_INV'')) ' || --Added for bug 6446848 06-Jan-2009 barathsr
147 ' AND ca.party_id = :party_id ' ||
148 ' AND del.org_id = :org_id ' ||
149 ' UNION ALL ' ||
150 ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
151 ' FROM iex_promise_details pro, hz_cust_accounts ca ' ||
152 ' WHERE ca.cust_account_id = pro.cust_account_id ' ||
153 ' AND pro.state = ''BROKEN_PROMISE'' ' ||
154 ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
155 ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
156 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
157 -- ' AND pro.resource_id = :resource_id ' ||
158 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
159 ' AND pro.delinquency_id is null ' ||
160 ' AND ca.party_id = :party_id ' ||
161 ')';
162 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt WHERE bnkrpt.party_id = :party_id ';
163 l_bnkrpt_clause := l_bnkrpt_clause || ' and NVL(bnkrpt.DISPOSITION_CODE,''GRANTED'') in (''GRANTED'',''NEGOTIATION'') ';
164 l_object_id := p_party_id;
165 ELSIF p_cust_account_id > 0 THEN
166 l_pro_clause := 'SELECT COUNT(1) ' ||
167 'FROM (' ||
168 ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
169 ' FROM iex_promise_details pro, iex_delinquencies_all del ' ||
170 ' WHERE pro.delinquency_id = del.delinquency_id ' ||
171 ' AND pro.state = ''BROKEN_PROMISE'' ' ||
172 ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
173 ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
174 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
175 -- ' AND pro.resource_id = :resource_id ' ||
176 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
177 ' AND pro.cust_account_id = :cust_account_id ' ||
178 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
179 -- ' AND del.status <> ''CURRENT'' ' ||
180 ' AND (del.status NOT IN (''CURRENT'', ''CLOSE'') ' ||
181 ' OR (del.status=''CURRENT'' and del.source_program_name=''IEX_CURR_INV'')) ' || --Added for bug 6446848 06-Jan-2009 barathsr
182 ' AND del.org_id = :org_id ' ||
183 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
184 ' UNION ALL ' ||
185 ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
186 ' FROM iex_promise_details pro' ||
187 ' WHERE pro.state = ''BROKEN_PROMISE'' ' ||
188 ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
189 ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
190 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
191 -- ' AND pro.resource_id = :resource_id ' ||
192 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
193 ' AND pro.cust_account_id = :cust_account_id ' ||
194 ' AND pro.delinquency_id is null ' ||
195 ')';
196 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
197 ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';
198 l_bnkrpt_clause := l_bnkrpt_clause || ' and NVL(bnkrpt.DISPOSITION_CODE,''GRANTED'') in (''GRANTED'',''NEGOTIATION'') ';
199 l_object_id := p_cust_account_id;
200 ELSIF p_site_use_id > 0 THEN
201 l_pro_clause := 'SELECT COUNT(1) ' ||
202 'FROM (' ||
203 ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
204 ' FROM iex_promise_details pro, iex_delinquencies_all del ' ||
205 ' WHERE pro.state = ''BROKEN_PROMISE'' ' ||
206 ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
207 ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
208 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
209 -- ' AND pro.resource_id =:resource_id ' ||
210 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
211 ' AND pro.delinquency_id = del.delinquency_id '||
212 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
213 -- ' AND del.status <> ''CURRENT'' ' ||
214 ' AND (del.status NOT IN (''CURRENT'', ''CLOSE'') ' ||
215 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
216 ' OR (del.status=''CURRENT'' and del.source_program_name=''IEX_CURR_INV'')) ' || --Added for bug 6446848 06-Jan-2009 barathsr
217 ' AND del.customer_site_use_id = :site_use_id ' ||
218 ' AND del.org_id = :org_id ' ||
219 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise
220 ' UNION ALL ' ||
221 ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
222 ' FROM iex_promise_details pro, okc_k_headers_b okch ' ||
223 ' WHERE pro.state = ''BROKEN_PROMISE'' ' ||
224 ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
225 ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
226 ' AND pro.contract_id = okch.id ' ||
227 ' AND okch.bill_to_site_use_id = :site_use_id ' ||
228 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise
229 ')';
230 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
231 ' WHERE bnkrpt.party_id = acc.party_id ' ||
232 ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
233 ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
234 ' AND site_use.site_use_id = :site_use_id';
235 l_bnkrpt_clause := l_bnkrpt_clause || ' and NVL(bnkrpt.DISPOSITION_CODE,''GRANTED'') in (''GRANTED'',''NEGOTIATION'') ';
236 l_object_id := p_site_use_id;
237 END IF;
238
239 IF l_enable_bkr_filter = 'Y' THEN
240 OPEN c_bnkrpt_cnt FOR l_bnkrpt_clause USING l_object_id;
241 FETCH c_bnkrpt_cnt INTO l_bnkrpt_cnt;
242 CLOSE c_bnkrpt_cnt;
243
244 IF l_bnkrpt_cnt > 0 THEN
245 return 0;
246 END IF;
247 END IF;
248
249 IF p_uwq_status = 'ACTIVE' THEN
250 l_pro_clause := l_pro_clause ||
251 ' WHERE (uwq_status IS NULL or uwq_status = ''ACTIVE'' or ' ||
252 ' (trunc(uwq_active_date) <= trunc(SYSDATE) and uwq_status = ''PENDING'' ) )';
253 ELSIF p_uwq_status = 'PENDING' THEN
254 l_pro_clause := l_pro_clause ||
255 ' WHERE (uwq_status = ''PENDING'' and (trunc(uwq_active_date) > trunc(SYSDATE)) )';
256 ELSIF p_uwq_status = 'COMPLETE' THEN
257 l_pro_clause := l_pro_clause ||
258 ' WHERE (uwq_status = ''COMPLETE'' and (trunc(uwq_complete_date) + :complete_days > trunc(SYSDATE)) )';
259 END IF;
260
261 IF p_site_use_id >0 THEN
262 IF p_uwq_status = 'COMPLETE' THEN
263 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
264 -- OPEN c_pro_cnt FOR l_pro_clause USING g_resource_id, l_object_id, l_complete_days;
265 OPEN c_pro_cnt FOR l_pro_clause USING l_object_id, l_object_id, l_complete_days;
266 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
267 FETCH c_pro_cnt INTO l_pro_cnt;
268 CLOSE c_pro_cnt;
269 ELSE
270 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
271 -- OPEN c_pro_cnt FOR l_pro_clause USING g_resource_id, l_object_id;
272 OPEN c_pro_cnt FOR l_pro_clause USING l_object_id, p_org_id, l_object_id;
273 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
274 FETCH c_pro_cnt INTO l_pro_cnt;
275 CLOSE c_pro_cnt;
276 END IF;
277 ELSE
278 IF p_uwq_status = 'COMPLETE' THEN
279 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
280 -- OPEN c_pro_cnt FOR l_pro_clause USING g_resource_id, l_object_id, g_resource_id, l_object_id, l_complete_days;
281 OPEN c_pro_cnt FOR l_pro_clause USING l_object_id, l_object_id, l_complete_days;
282 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
283 FETCH c_pro_cnt INTO l_pro_cnt;
284 CLOSE c_pro_cnt;
285 ELSE
286 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
287 -- OPEN c_pro_cnt FOR l_pro_clause USING g_resource_id, l_object_id, g_resource_id, l_object_id;
288 OPEN c_pro_cnt FOR l_pro_clause USING l_object_id, p_org_id, l_object_id;
289 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
290 FETCH c_pro_cnt INTO l_pro_cnt;
291 CLOSE c_pro_cnt;
292 END IF;
293 END IF;
294
295
296 RETURN l_pro_cnt;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 return -1;
301 END get_pro_count;
302 --End bug 6634879 gnramasa 20th Nov 07
303
304 FUNCTION get_str_count(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_uwq_status VARCHAR2) RETURN NUMBER
305 IS
306 TYPE refCur IS REF CURSOR;
307 c_str_cnt refCur;
308 l_str_clause VARCHAR2(1000);
309 l_str_cnt NUMBER := 0;
310
311 l_object_id NUMBER;
312 c_bnkrpt_cnt refCur;
313 l_bnkrpt_clause VARCHAR2(1000);
314 l_bnkrpt_cnt NUMBER := 0;
315 l_Complete_Days varchar2(40);
316 l_enable_bkr_filter VARCHAR2(40);
317 BEGIN
318 l_enable_bkr_filter := NVL(FND_PROFILE.VALUE('IEX_BANKRUPTCY_FILTER'), 'Y');
319 l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
320
321 IF p_party_id > 0 THEN
322 l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
323 'WHERE wtm.strategy_id = str.strategy_id ' ||
324 'AND wtm.resource_id = :resource_id ' ||
325 'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
326 'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
327 'AND str.jtf_object_type = ''PARTY'' ' ||
328 'AND str.jtf_object_id = :party_id ';
329 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt WHERE bnkrpt.party_id = :party_id';
330 l_object_id := p_party_id;
331 ELSIF p_cust_account_id > 0 THEN
332 l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
333 'WHERE wtm.strategy_id = str.strategy_id ' ||
334 'AND wtm.resource_id = :resource_id ' ||
335 'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
336 'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
337 'AND str.jtf_object_type = ''IEX_ACCOUNT'' ' ||
338 'AND str.jtf_object_id = :cust_account_id ';
339 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
340 ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';
341 l_object_id := p_cust_account_id;
342 ELSIF p_site_use_id > 0 THEN
343 l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
344 'WHERE wtm.strategy_id = str.strategy_id ' ||
345 'AND wtm.resource_id = :resource_id '||
346 'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
347 'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
348 'AND str.jtf_object_type = ''IEX_BILLTO'' ' ||
349 'AND str.jtf_object_id = :site_use_id ';
350 l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
351 ' WHERE bnkrpt.party_id = acc.party_id ' ||
352 ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
353 ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
354 ' AND site_use.site_use_id = :site_use_id';
355
356 l_object_id := p_site_use_id;
357 END IF;
358
359 IF l_enable_bkr_filter = 'Y' THEN
360 OPEN c_bnkrpt_cnt FOR l_bnkrpt_clause USING l_object_id;
361 FETCH c_bnkrpt_cnt INTO l_bnkrpt_cnt;
362 CLOSE c_bnkrpt_cnt;
363
364 IF l_bnkrpt_cnt > 0 THEN
365 return 0;
366 END IF;
367 END IF;
368
369 IF p_uwq_status = 'ACTIVE' THEN
370 l_str_clause := l_str_clause ||
371 ' AND (UWQ_STATUS IS NULL or UWQ_STATUS = ''ACTIVE'' or ' ||
372 ' (trunc(UWQ_ACTIVE_DATE) <= trunc(SYSDATE) and UWQ_STATUS = ''PENDING'' ) )';
373 ELSIF p_uwq_status = 'PENDING' THEN
374 l_str_clause := l_str_clause ||
375 ' AND (UWQ_STATUS = ''PENDING'' and (trunc(UWQ_ACTIVE_DATE) > trunc(SYSDATE)) )';
376 ELSIF p_uwq_status = 'COMPLETE' THEN
377 l_str_clause := l_str_clause ||
378 ' AND (UWQ_STATUS = ''COMPLETE'' and (trunc(UWQ_COMPLETE_DATE) + :complete_days > trunc(SYSDATE)) )';
379 END IF;
380
381 IF p_uwq_status = 'COMPLETE' THEN
382 OPEN c_str_cnt FOR l_str_clause USING g_resource_id, l_object_id, l_complete_days;
383 FETCH c_str_cnt INTO l_str_cnt;
384 CLOSE c_str_cnt;
385 ELSE
386 OPEN c_str_cnt FOR l_str_clause USING g_resource_id, l_object_id;
387 FETCH c_str_cnt INTO l_str_cnt;
388 CLOSE c_str_cnt;
389 END IF;
390
391 RETURN l_str_cnt;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 return -1;
396 END get_str_count;
397
398 FUNCTION convert_amount(p_from_amount NUMBER, p_from_currency VARCHAR2) RETURN NUMBER
399 IS
400 l_set_of_books_id NUMBER;
401 l_conversion_type VARCHAR(30);
402 l_to_amount NUMBER;
403
404 CURSOR c_sob IS
405 SELECT set_of_books_id
406 FROM ar_system_parameters;
407 -- Start for the bug#8630157 by PNAVEENK
408 /* CURSOR c_rate_type IS
409 SELECT default_exchange_rate_type
410 FROM ar_cmgt_setup_options; */
411
412 BEGIN
413 OPEN c_sob;
414 FETCH c_sob INTO l_set_of_books_id;
415 CLOSE c_sob;
416
417 /* OPEN c_rate_type;
418 FETCH c_rate_type INTO l_conversion_type;
419 CLOSE c_rate_type; */
420 l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
421 l_to_amount := gl_currency_api.convert_amount(x_set_of_books_id => l_set_of_books_id,
422 x_from_currency => p_from_currency,
423 x_conversion_date => sysdate,
424 x_conversion_type => l_conversion_type,
425 x_amount => p_from_amount);
426 return l_to_amount;
427 EXCEPTION
428 WHEN OTHERS THEN
429 return -1;
430 END convert_amount;
431
432 FUNCTION get_last_payment_amount(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER) RETURN NUMBER
433 IS
434 TYPE refCur IS REF CURSOR;
435 c_amt refCur;
436 l_amt_clause VARCHAR2(1000);
437 l_amount NUMBER;
438 l_currency VARCHAR2(15);
439 l_object_id NUMBER;
440 BEGIN
441 IF p_party_id > 0 THEN
442
443 l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
444 ' FROM ar_trx_bal_summary o_summ, hz_cust_accounts acc' ||
445 ' WHERE o_summ.cust_account_id = acc.cust_account_id' ||
446 ' AND acc.party_id = :party_id ' ||
447 ' AND last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
448 ' FROM ar_trx_bal_summary ' ||
449 ' WHERE cust_account_id = o_summ.cust_account_id) ';
450
451 l_object_id := p_party_id;
452 ELSIF p_cust_account_id > 0 THEN
453 l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
454 ' FROM ar_trx_bal_summary o_summ' ||
455 ' WHERE o_summ.cust_account_id = :cust_account_id ' ||
456 ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
457 ' FROM ar_trx_bal_summary ' ||
458 ' WHERE cust_account_id = o_summ.cust_account_id) ';
459 l_object_id := p_cust_account_id;
460 ELSIF p_site_use_id > 0 THEN
461 l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
462 ' FROM ar_trx_bal_summary o_summ' ||
463 ' WHERE o_summ.site_use_id = :site_use_id ' ||
464 ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
465 ' FROM ar_trx_bal_summary ' ||
466 ' WHERE site_use_id = o_summ.site_use_id) ';
467 l_object_id := p_site_use_id;
468 END IF;
469
470 OPEN c_amt FOR l_amt_clause USING l_object_id;
471 FETCH c_amt INTO l_amount, l_currency;
472 CLOSE c_amt;
473
474 IF l_amount IS NOT NULL THEN
475 l_amount := iex_uwq_view_pkg.convert_amount(l_amount, l_currency);
476 END IF;
477
478 return l_amount;
479 --EXCEPTION
480 --WHEN OTHERS THEN
481 --return l_amount;
482 END get_last_payment_amount;
483
484 FUNCTION get_last_payment_number(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER) RETURN VARCHAR2
485 IS
486 TYPE refCur IS REF CURSOR;
487 c_no refCur;
488 l_no_clause VARCHAR2(1000);
489 l_number VARCHAR2(30);
490 l_currency VARCHAR2(15);
491 l_object_id NUMBER;
492 BEGIN
493 IF p_party_id > 0 THEN
494
495 l_no_clause := ' SELECT o_summ.last_payment_number ' ||
496 ' FROM ar_trx_bal_summary o_summ, hz_cust_accounts acc' ||
497 ' WHERE o_summ.cust_account_id = acc.cust_account_id' ||
498 ' AND acc.party_id = :party_id ' ||
499 ' AND last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
500 ' FROM ar_trx_bal_summary ' ||
501 ' WHERE cust_account_id = o_summ.cust_account_id) ';
502
503 l_object_id := p_party_id;
504 ELSIF p_cust_account_id > 0 THEN
505 l_no_clause := ' SELECT o_summ.last_payment_number ' ||
506 ' FROM ar_trx_bal_summary o_summ' ||
507 ' WHERE o_summ.cust_account_id = :cust_account_id ' ||
508 ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
509 ' FROM ar_trx_bal_summary ' ||
510 ' WHERE cust_account_id = o_summ.cust_account_id) ';
511 l_object_id := p_cust_account_id;
512 ELSIF p_site_use_id > 0 THEN
513 l_no_clause := ' SELECT o_summ.last_payment_number ' ||
514 ' FROM ar_trx_bal_summary o_summ' ||
515 ' WHERE o_summ.site_use_id = :site_use_id ' ||
516 ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
517 ' FROM ar_trx_bal_summary ' ||
518 ' WHERE site_use_id = o_summ.site_use_id) ';
519 l_object_id := p_site_use_id;
520 END IF;
521
522 OPEN c_no FOR l_no_clause USING l_object_id;
523 FETCH c_no INTO l_number;
524 CLOSE c_no;
525
526
527 return l_number;
528 --EXCEPTION
529 --WHEN OTHERS THEN
530 --return l_amount;
531 END get_last_payment_number;
532 FUNCTION get_score(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER) RETURN NUMBER
533 IS
534 CURSOR c_score(x_score_object_id NUMBER, x_score_object_code VARCHAR2) IS
535 SELECT a.score_value
536 FROM iex_score_histories a
537 WHERE a.creation_date =
538 (SELECT MAX(creation_date)
539 FROM iex_Score_histories
540 WHERE score_object_code = x_score_object_code
541 AND score_object_id = x_score_object_id)
542 AND a.score_object_code = x_score_object_code
543 AND a.score_object_id = x_score_object_id;
544
545 l_score NUMBER;
546 BEGIN
547 IF p_party_id > 0 THEN
548 OPEN c_score(p_party_id, 'PARTY');
549 FETCH c_score INTO l_score;
550 CLOSE c_score;
551 ELSIF p_cust_account_id > 0 THEN
552 OPEN c_score(p_cust_account_id, 'IEX_ACCOUNT');
553 FETCH c_score INTO l_score;
554 CLOSE c_score;
555 ELSIF p_site_use_id > 0 THEN
556 OPEN c_score(p_site_use_id, 'IEX_BILLTO');
557 FETCH c_score INTO l_score;
558 CLOSE c_score;
559 END IF;
560
561 return l_score;
562
563 END get_score;
564
565 --Start bug 6634879 gnramasa 20th Nov 07
566 FUNCTION get_broken_prm_amt(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_org_id NUMBER) RETURN NUMBER
567 IS
568 CURSOR c_party_amt IS
569 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
570 -- SELECT SUM(amount_due_remaining)
571 -- FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies del
572 -- WHERE pro.cust_account_id = ca.cust_account_id
573 -- AND ca.party_id = p_party_id
574 -- AND pro.status in ('COLLECTABLE', 'PENDING')
575 -- AND pro.state = 'BROKEN_PROMISE'
576 -- AND pro.amount_due_remaining > 0
577 -- AND pro.resource_id = g_resource_id
578 -- AND pro.delinquency_id = del.delinquency_id(+)
579 -- AND del.status(+) <> 'CURRENT';
580 SELECT SUM(amount_due_remaining)
581 FROM ( SELECT amount_due_remaining
582 FROM iex_promise_details pro, iex_delinquencies_all del
583 WHERE del.party_cust_id = p_party_id
584 AND pro.status in ('COLLECTABLE', 'PENDING')
585 AND pro.state = 'BROKEN_PROMISE'
586 AND pro.amount_due_remaining > 0
587 AND pro.delinquency_id = del.delinquency_id
588 AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
589 or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
590 AND del.org_id = p_org_id
591 UNION ALL
592 SELECT amount_due_remaining
593 FROM iex_promise_details pro, okc_k_headers_b okch, hz_cust_accounts ca
594 WHERE ca.cust_account_id = pro.cust_account_id
595 AND ca.party_id = p_party_id
596 AND pro.status in ('COLLECTABLE', 'PENDING')
597 AND pro.state = 'BROKEN_PROMISE'
598 AND pro.amount_due_remaining > 0
599 AND pro.contract_id = okch.id
600 );
601 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
602
603 CURSOR c_acc_amt IS
604 SELECT SUM(amount_due_remaining)
605 FROM iex_promise_details pro, iex_delinquencies_all del
606 WHERE pro.cust_account_id = p_cust_account_id
607 AND pro.status in ('COLLECTABLE', 'PENDING')
608 AND pro.state = 'BROKEN_PROMISE'
609 AND pro.amount_due_remaining > 0
610 AND pro.resource_id = g_resource_id
611 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
612 -- AND pro.delinquency_id = del.delinquency_id(+)
613 -- AND del.status(+) <> 'CURRENT';
614 AND pro.delinquency_id = del.delinquency_id
615 AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
616 OR (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
617 AND del.org_id = p_org_id;
618 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
619
620 CURSOR c_billto_amt IS
621 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
622 -- SELECT SUM(amount_due_remaining)
623 -- FROM iex_promise_details pro, iex_delinquencies del
624 -- WHERE del.customer_site_use_id = p_site_use_id
625 -- AND pro.status in ('COLLECTABLE', 'PENDING')
626 -- AND pro.state = 'BROKEN_PROMISE'
627 -- AND pro.amount_due_remaining > 0
628 -- AND pro.resource_id = g_resource_id
629 -- AND pro.delinquency_id = del.delinquency_id
630 -- AND del.status <> 'CURRENT';
631 -- AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
632 SELECT SUM(amount_due_remaining)
633 FROM ( SELECT amount_due_remaining
634 FROM iex_promise_details pro, iex_delinquencies_all del
635 WHERE del.customer_site_use_id = p_site_use_id
636 AND pro.status in ('COLLECTABLE', 'PENDING')
637 AND pro.state = 'BROKEN_PROMISE'
638 AND pro.amount_due_remaining > 0
639 AND pro.delinquency_id = del.delinquency_id
640 AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
641 or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
642 AND del.org_id = p_org_id
643 UNION ALL
644 SELECT amount_due_remaining
645 FROM iex_promise_details pro, okc_k_headers_b okch
646 WHERE okch.bill_to_site_use_id = p_site_use_id
647 AND pro.status in ('COLLECTABLE', 'PENDING')
648 AND pro.state = 'BROKEN_PROMISE'
649 AND pro.amount_due_remaining > 0
650 AND pro.contract_id = okch.id
651 );
652 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
653
654 l_broken_prm_amt NUMBER;
655 BEGIN
656 IF p_party_id > 0 THEN
657 OPEN c_party_amt;
658 FETCH c_party_amt INTO l_broken_prm_amt;
659 CLOSE c_party_amt;
660 ELSIF p_cust_account_id > 0 THEN
661 OPEN c_acc_amt;
662 FETCH c_acc_amt INTO l_broken_prm_amt;
663 CLOSE c_acc_amt;
664 ELSIF p_site_use_id > 0 THEN
665 OPEN c_billto_amt;
666 FETCH c_billto_amt INTO l_broken_prm_amt;
667 CLOSE c_billto_amt;
668 END IF;
669
670 return l_broken_prm_amt;
671
672 END get_broken_prm_amt;
673
674
675 FUNCTION get_prm_amt(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_org_id NUMBER) RETURN NUMBER
676 IS
677 CURSOR c_party_amt IS
678 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
679 -- SELECT SUM(promise_amount)
680 -- FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies del
681 -- WHERE pro.cust_account_id = ca.cust_account_id
682 -- AND ca.party_id = p_party_id
683 -- AND pro.status in ('COLLECTABLE', 'PENDING')
684 -- AND pro.state = 'BROKEN_PROMISE'
685 -- AND pro.amount_due_remaining > 0
686 -- AND pro.resource_id = g_resource_id
687 -- AND pro.delinquency_id = del.delinquency_id(+)
688 -- AND del.status(+) <> 'CURRENT';
689 -- AND pro.delinquency_id = del.delinquency_id
690 -- AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
691 SELECT SUM(promise_amount)
692 FROM ( SELECT promise_amount
693 FROM iex_promise_details pro, iex_delinquencies_all del
694 WHERE del.party_cust_id = p_party_id
695 AND pro.status in ('COLLECTABLE', 'PENDING')
696 AND pro.state = 'BROKEN_PROMISE'
697 AND pro.amount_due_remaining > 0
698 AND pro.delinquency_id = del.delinquency_id
699 AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
700 or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
701 AND del.org_id = p_org_id
702 UNION ALL
703 SELECT promise_amount
704 FROM iex_promise_details pro, okc_k_headers_b okch, hz_cust_accounts ca
705 WHERE ca.party_id = p_party_id
706 AND pro.cust_account_id = ca.cust_account_id
707 AND pro.status in ('COLLECTABLE', 'PENDING')
708 AND pro.state = 'BROKEN_PROMISE'
709 AND pro.amount_due_remaining > 0
710 AND pro.contract_id = okch.id
711 );
712 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
713
714 CURSOR c_acc_amt IS
715 SELECT SUM(promise_amount)
716 FROM iex_promise_details pro, iex_delinquencies_all del
717 WHERE pro.cust_account_id = p_cust_account_id
718 AND pro.amount_due_remaining > 0
719 AND pro.status in ('COLLECTABLE', 'PENDING')
720 AND pro.state = 'BROKEN_PROMISE'
721 AND pro.resource_id = g_resource_id
722 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
723 -- AND pro.delinquency_id = del.delinquency_id(+)
724 -- AND del.status(+) <> 'CURRENT';
725 AND pro.delinquency_id = del.delinquency_id
726 AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
727 or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
728 AND del.org_id = p_org_id;
729 --END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
730
731 CURSOR c_billto_amt IS
732 --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
733 -- SELECT SUM(promise_amount)
734 -- FROM iex_promise_details pro, iex_delinquencies del
735 -- WHERE del.customer_site_use_id = p_site_use_id
736 -- AND pro.amount_due_remaining > 0
737 -- AND pro.status in ('COLLECTABLE', 'PENDING')
738 -- AND pro.state = 'BROKEN_PROMISE'
739 -- AND pro.resource_id = g_resource_id
740 -- AND pro.delinquency_id = del.delinquency_id
741 -- AND del.status <> 'CURRENT';
742 SELECT SUM(promise_amount)
743 FROM ( SELECT promise_amount
744 FROM iex_promise_details pro, iex_delinquencies_all del
745 WHERE del.customer_site_use_id = p_site_use_id
746 AND pro.status in ('COLLECTABLE', 'PENDING')
747 AND pro.state = 'BROKEN_PROMISE'
748 AND pro.amount_due_remaining > 0
749 AND pro.delinquency_id = del.delinquency_id
750 AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
751 or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
752 AND del.org_id = p_org_id
753 UNION ALL
754 SELECT promise_amount
755 FROM iex_promise_details pro, okc_k_headers_b okch
756 WHERE okch.bill_to_site_use_id = p_site_use_id
757 AND pro.status in ('COLLECTABLE', 'PENDING')
758 AND pro.state = 'BROKEN_PROMISE'
759 AND pro.amount_due_remaining > 0
760 AND pro.contract_id = okch.id
761 );
762 --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
763
764 l_prm_amt NUMBER;
765 BEGIN
766 IF p_party_id > 0 THEN
767 OPEN c_party_amt;
768 FETCH c_party_amt INTO l_prm_amt;
769 CLOSE c_party_amt;
770 ELSIF p_cust_account_id > 0 THEN
771 OPEN c_acc_amt;
772 FETCH c_acc_amt INTO l_prm_amt;
773 CLOSE c_acc_amt;
774 ELSIF p_site_use_id > 0 THEN
775 OPEN c_billto_amt;
776 FETCH c_billto_amt INTO l_prm_amt;
777 CLOSE c_billto_amt;
778 END IF;
779
780 return l_prm_amt;
781
782 END get_prm_amt;
783 --End bug 6634879 gnramasa 20th Nov 07
784
785 FUNCTION get_contract_count(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_uwq_status VARCHAR2) RETURN NUMBER
786 IS
787 TYPE refCur IS REF CURSOR;
788 c_contract_cnt refCur;
789 l_contract_clause VARCHAR2(1000);
790 l_contract_cnt NUMBER := 0;
791 l_Complete_Days varchar2(40);
792 l_lease_enabled VARCHAR2(40);
793 BEGIN
794 --BEGIN-FIX Bug#4408860-06/06/2005-jypark-obsolete function because we're not showing this field in UWQ
795 -- l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
796 -- l_lease_enabled := NVL(FND_PROFILE.VALUE('IEX_LEASE_ENABLED'), 'N');
797 --
798 -- IF l_lease_enabled = 'N' THEN
799 -- return 0;
800 -- END IF;
801 --
802 -- IF p_site_use_id > 0 AND p_party_id > 0 THEN
803 -- l_contract_clause := 'SELECT COUNT(object_id) ' ||
804 -- ' FROM iex_cases_all_b icas, iex_case_objects icobj, ' ||
805 -- ' iex_case_definitions icdef, iex_delinquencies idel ' ||
806 -- ' WHERE icas.cas_id = icobj.cas_id ' ||
807 -- ' AND icas.party_id = :party_id ' ||
808 -- ' AND icdef.column_name = ''BILL_TO_ADDRESS_ID'' ' ||
809 -- ' AND icdef.column_value = :customer_site_use_id ' ||
810 -- ' AND icdef.cas_id = icas.cas_id '||
811 -- ' AND idel.case_id = icas.cas_id '||
812 -- ' AND idel.status <> ''CURRENT'' ';
813 -- ELSIF p_cust_account_id > 0 AND p_party_id > 0 THEN
814 -- l_contract_clause := 'SELECT COUNT(object_id) ' ||
815 -- ' FROM iex_cases_all_b icas, iex_case_objects icobj, ' ||
816 -- ' iex_case_definitions icdef, iex_delinquencies idel ' ||
817 -- ' WHERE icas.cas_id = icobj.cas_id ' ||
818 -- ' AND icas.party_id = :party_id ' ||
819 -- ' AND icdef.column_name = ''CUSTOMER_ACCOUNT'' ' ||
820 -- ' AND icdef.column_value = :cust_account_id ' ||
821 -- ' AND icdef.cas_id = icas.cas_id ' ||
822 -- ' AND idel.case_id = icas.cas_id ' ||
823 -- ' AND idel.status <> ''CURRENT'' ';
824 -- ELSIF p_party_id > 0 THEN
825 -- l_contract_clause := 'SELECT COUNT(object_id) ' ||
826 -- ' FROM iex_cases_all_b icas, iex_case_objects icobj, iex_delinquencies idel ' ||
827 -- ' WHERE icas.cas_id = icobj.cas_id ' ||
828 -- ' AND icas.party_id = :party_id ' ||
829 -- ' AND idel.case_id = icas.cas_id ' ||
830 -- ' AND idel.status <> ''CURRENT'' ';
831 -- END IF;
832 --
833 -- IF p_uwq_status = 'ACTIVE' THEN
834 -- l_contract_clause := l_contract_clause ||
835 -- ' AND (UWQ_STATUS IS NULL or UWQ_STATUS = ''ACTIVE'' or ' ||
836 -- ' (trunc(UWQ_ACTIVE_DATE) <= trunc(SYSDATE) and UWQ_STATUS = ''PENDING'' ) )';
837 -- ELSIF p_uwq_status = 'PENDING' THEN
838 -- l_contract_clause := l_contract_clause ||
839 -- ' AND (UWQ_STATUS = ''PENDING'' and (trunc(UWQ_ACTIVE_DATE) > trunc(SYSDATE)) )';
840 -- ELSIF p_uwq_status = 'COMPLETE' THEN
841 -- l_contract_clause := l_contract_clause ||
842 -- ' AND (UWQ_STATUS = ''COMPLETE'' and (trunc(UWQ_COMPLETE_DATE) + :complete_days > trunc(SYSDATE)) )';
843 -- END IF;
844 --
845 --
846 -- IF p_uwq_status = 'COMPLETE' THEN
847 -- IF p_site_use_id > 0 AND p_party_id > 0 THEN
848 -- OPEN c_contract_cnt FOR l_contract_clause USING p_party_id, p_site_use_id, l_complete_days;
849 -- FETCH c_contract_cnt INTO l_contract_cnt;
850 -- CLOSE c_contract_cnt;
851 -- ELSIF p_cust_account_id > 0 AND p_party_id > 0 THEN
852 -- OPEN c_contract_cnt FOR l_contract_clause USING p_party_id, p_cust_account_id, l_complete_days;
853 -- FETCH c_contract_cnt INTO l_contract_cnt;
854 -- CLOSE c_contract_cnt;
855 -- ELSIF p_party_id > 0 THEN
856 -- OPEN c_contract_cnt FOR l_contract_clause USING p_party_id, l_complete_days;
857 -- FETCH c_contract_cnt INTO l_contract_cnt;
858 -- CLOSE c_contract_cnt;
859 -- END IF;
860 -- ELSE
861 -- IF p_site_use_id > 0 AND p_party_id > 0 THEN
862 -- OPEN c_contract_cnt FOR l_contract_clause USING p_party_id, p_site_use_id;
863 -- FETCH c_contract_cnt INTO l_contract_cnt;
864 -- CLOSE c_contract_cnt;
865 -- ELSIF p_cust_account_id > 0 AND p_party_id > 0 THEN
866 -- OPEN c_contract_cnt FOR l_contract_clause USING p_party_id, p_cust_account_id;
867 -- FETCH c_contract_cnt INTO l_contract_cnt;
868 -- CLOSE c_contract_cnt;
869 -- ELSIF p_party_id > 0 THEN
870 -- OPEN c_contract_cnt FOR l_contract_clause USING p_party_id;
871 -- FETCH c_contract_cnt INTO l_contract_cnt;
872 -- CLOSE c_contract_cnt;
873 -- END IF;
874 -- END IF;
875 --End-FIX Bug#4408860-06/06/2005-jypark-obsolete function because we're not showing this field in UWQ
876
877 RETURN l_contract_cnt;
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 return 0;
882 END get_contract_count;
883
884 FUNCTION get_case_count(p_party_id NUMBER, p_cust_account_id NUMBER, p_site_use_id NUMBER, p_uwq_status VARCHAR2) RETURN NUMBER
885 IS
886 TYPE refCur IS REF CURSOR;
887 c_case_cnt refCur;
888 l_case_clause VARCHAR2(1000);
889 l_case_cnt NUMBER := 0;
890 l_Complete_Days varchar2(40);
891 l_lease_enabled VARCHAR2(40);
892 BEGIN
893 --Begin-FIX Bug#4408860-06/06/2005-jypark-obsolete function because we're not showing this field in UWQ
894 -- l_Complete_Days := NVL(FND_PROFILE.VALUE('IEX_UWQ_COMPLETION_DAYS'), 30);
895 -- l_lease_enabled := NVL(FND_PROFILE.VALUE('IEX_LEASE_ENABLED'), 'N');
896 --
897 -- IF l_lease_enabled = 'N' THEN
898 -- return 0;
899 -- END IF;
900 --
901 -- IF p_site_use_id > 0 AND p_party_id > 0 THEN
902 -- l_case_clause := 'SELECT COUNT(1) ' ||
903 -- ' FROM iex_cases_all_b icas, ' ||
904 -- ' iex_case_definitions icdef, iex_delinquencies idel ' ||
905 -- ' WHERE icas.party_id = :party_id ' ||
906 -- ' AND icdef.column_name = ''BILL_TO_ADDRESS_ID'' ' ||
907 -- ' AND icdef.column_value = :customer_site_use_id ' ||
908 -- ' AND icdef.cas_id = icas.cas_id '||
909 -- ' AND idel.case_id = icas.cas_id '||
910 -- ' AND idel.status <> ''CURRENT'' ';
911 -- ELSIF p_cust_account_id > 0 AND p_party_id > 0 THEN
912 -- l_case_clause := 'SELECT COUNT(1) ' ||
913 -- ' FROM iex_cases_all_b icas, ' ||
914 -- ' iex_case_definitions icdef, iex_delinquencies idel ' ||
915 -- ' WHERE icas.party_id = :party_id ' ||
916 -- ' AND icdef.column_name = ''CUSTOMER_ACCOUNT'' ' ||
917 -- ' AND icdef.column_value = :cust_account_id ' ||
918 -- ' AND icdef.cas_id = icas.cas_id ' ||
919 -- ' AND idel.case_id = icas.cas_id ' ||
920 -- ' AND idel.status <> ''CURRENT'' ';
921 -- ELSIF p_party_id > 0 THEN
922 -- l_case_clause := 'SELECT COUNT(1) ' ||
923 -- ' FROM iex_cases_all_b icas, iex_delinquencies idel ' ||
924 -- ' WHERE icas.party_id = :party_id ' ||
925 -- ' AND idel.case_id = icas.cas_id ' ||
926 -- ' AND idel.status <> ''CURRENT'' ';
927 -- END IF;
928 --
929 -- IF p_uwq_status = 'ACTIVE' THEN
930 -- l_case_clause := l_case_clause ||
931 -- ' AND (UWQ_STATUS IS NULL or UWQ_STATUS = ''ACTIVE'' or ' ||
932 -- ' (trunc(UWQ_ACTIVE_DATE) <= trunc(SYSDATE) and UWQ_STATUS = ''PENDING'' ) )';
933 -- ELSIF p_uwq_status = 'PENDING' THEN
934 -- l_case_clause := l_case_clause ||
935 -- ' AND (UWQ_STATUS = ''PENDING'' and (trunc(UWQ_ACTIVE_DATE) > trunc(SYSDATE)) )';
936 -- ELSIF p_uwq_status = 'COMPLETE' THEN
937 -- l_case_clause := l_case_clause ||
938 -- ' AND (UWQ_STATUS = ''COMPLETE'' and (trunc(UWQ_COMPLETE_DATE) + :complete_days > trunc(SYSDATE)) )';
939 -- END IF;
940 --
941 --
942 -- IF p_uwq_status = 'COMPLETE' THEN
943 -- IF p_site_use_id > 0 AND p_party_id > 0 THEN
944 -- OPEN c_case_cnt FOR l_case_clause USING p_party_id, p_site_use_id, l_complete_days;
945 -- FETCH c_case_cnt INTO l_case_cnt;
946 -- CLOSE c_case_cnt;
947 -- ELSIF p_cust_account_id > 0 AND p_party_id > 0 THEN
948 -- OPEN c_case_cnt FOR l_case_clause USING p_party_id, p_cust_account_id, l_complete_days;
949 -- FETCH c_case_cnt INTO l_case_cnt;
950 -- CLOSE c_case_cnt;
951 -- ELSIF p_party_id > 0 THEN
952 -- OPEN c_case_cnt FOR l_case_clause USING p_party_id, l_complete_days;
953 -- FETCH c_case_cnt INTO l_case_cnt;
954 -- CLOSE c_case_cnt;
955 -- END IF;
956 -- ELSE
957 -- IF p_site_use_id > 0 AND p_party_id > 0 THEN
958 -- OPEN c_case_cnt FOR l_case_clause USING p_party_id, p_site_use_id;
959 -- FETCH c_case_cnt INTO l_case_cnt;
960 -- CLOSE c_case_cnt;
961 -- ELSIF p_cust_account_id > 0 AND p_party_id > 0 THEN
962 -- OPEN c_case_cnt FOR l_case_clause USING p_party_id, p_cust_account_id;
963 -- FETCH c_case_cnt INTO l_case_cnt;
964 -- CLOSE c_case_cnt;
965 -- ELSIF p_party_id > 0 THEN
966 -- OPEN c_case_cnt FOR l_case_clause USING p_party_id;
967 -- FETCH c_case_cnt INTO l_case_cnt;
968 -- CLOSE c_case_cnt;
969 -- END IF;
970 -- END IF;
971 --End-FIX Bug#4408860-06/06/2005-jypark-obsolete function because we're not showing this field in UWQ
972
973 RETURN l_case_cnt;
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 return 0;
978 END get_case_count;
979
980 BEGIN
981
982 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
983 g_user_id := TO_NUMBER(NVL(FND_PROFILE.VALUE('USER_ID'), -1));
984
985 OPEN c_get_emp(g_user_id);
986 FETCH c_get_emp INTO g_person_id;
987 CLOSE c_get_emp;
988
989 OPEN c_get_resource_id(g_person_id, g_user_id,'EMPLOYEE');
990 FETCH c_get_resource_id INTO g_resource_id;
991 CLOSE c_get_resource_id;
992
993 END;