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