[Home] [Help]
PACKAGE BODY: APPS.IEX_COLL_IND_PUB
Source
1 PACKAGE BODY IEX_COLL_IND_PUB AS
2 /* $Header: iexpmtib.pls 120.1 2010/12/24 09:27:43 snuthala noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_COLL_IND_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpmtib.pls';
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11 PG_DEBUG NUMBER(2);
12
13 g_base_currency_code gl_sets_of_books.currency_code%TYPE;
14 g_base_precision fnd_currencies.precision%type;
15 g_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
16
17 FUNCTION GET_CREDIT_LIMIT(p_party_id IN NUMBER,
18 p_cust_account_id IN NUMBER,
19 p_customer_site_use_id IN NUMBER,
20 p_org_id IN NUMBER) RETURN VARCHAR2 AS
21
22 l_credit_limit NUMBER;
23 l_num_val NUMBER;
24 l_char_val VARCHAR2(1000);
25 l_currency_code VARCHAR2(10);
26
27 BEGIN
28
29 If p_org_id is null then
30 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
31 end if;
32
33 --fnd_request.set_org_id(p_org_id);
34 mo_global.set_policy_context('S',p_org_id);
35
36 GET_COMMON(p_org_id);
37
38 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_true_dso - g_base_currency_code = ' ||g_base_currency_code);
39 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_true_dso - g_base_precision = ' ||g_base_precision);
40 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_true_dso - g_base_min_acc_unit = ' ||g_base_min_acc_unit);
41 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_true_dso - p_org_id = ' ||p_org_id);
42
43 IF p_party_id IS NOT NULL THEN
44 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL,
45 gl_currency_api.convert_amount_sql(prof_amt.currency_code,g_base_currency_code,
46 sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
47 INTO l_credit_limit
48 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
49 ar_cmgt_setup_options cm_opt
50 WHERE prof.party_id = p_party_id
51 AND prof.site_use_id IS NULL
52 AND prof.status = 'A'
53 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
54 AND prof_amt.cust_account_id = prof.cust_account_id
55 AND prof.cust_account_id = -1
56 AND prof_amt.site_use_id IS NULL;
57
58 ELSIF p_cust_account_id IS NOT NULL THEN
59 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL,
60 gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_base_currency_code,
61 sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
62 INTO l_credit_limit
63 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
64 ar_cmgt_setup_options cm_opt
65 WHERE prof.cust_account_id = p_cust_account_id
66 AND prof.site_use_id IS NULL
67 AND prof.status = 'A'
68 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
69 AND prof_amt.cust_account_id = p_cust_account_id
70 AND prof_amt.site_use_id IS NULL;
71 ELSIF p_customer_site_use_id IS NOT NULL THEN
72 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL,
73 gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_base_currency_code,
74 sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
75 INTO l_credit_limit
76 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
77 ar_cmgt_setup_options cm_opt
78 WHERE prof.site_use_id = p_customer_site_use_id
79 AND prof.status = 'A'
80 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
81 AND prof_amt.site_use_id = p_customer_site_use_id;
82 END IF;
83
84 l_num_val := l_credit_limit;
85 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_base_currency_code, 50)));
86 RETURN l_char_val;
87
88 EXCEPTION
89 when others then
90 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_credit_limit - Exception = ' ||SQLERRM);
91 RETURN(0);
92
93 END GET_CREDIT_LIMIT;
94
95
96 FUNCTION GET_WTD_DAYS_TERMS(p_party_id IN NUMBER,
97 p_cust_account_id IN NUMBER,
98 p_customer_site_use_id IN NUMBER,
99 p_org_id NUMBER) RETURN VARCHAR2 AS
100
101 l_wtd_days_terms NUMBER;
102 l_num_val NUMBER;
103 l_char_val VARCHAR2(1000);
104
105 BEGIN
106
107 If p_org_id is null then
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 end if;
110
111 --fnd_request.set_org_id(p_org_id);
112 mo_global.set_policy_context('S',p_org_id);
113
114
115 IF p_party_id IS NOT NULL THEN
116 SELECT sum
117 (
118 (
119 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
120 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
121 nvl(ps.acctd_amount_due_remaining, 0)
122 ) *
123 (ps.due_date - ps.trx_date)
124 ) /
125 sum (
126 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
127 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
128 nvl(ps.acctd_amount_due_remaining, 0)
129 )
130 INTO l_wtd_days_terms
131 FROM ar_payment_schedules ps, hz_cust_accounts ca
132 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
133 AND ps.class in ('INV','DEP','DM','CB')
134 AND ps.payment_schedule_id <> -1
135 AND ps.customer_id = ca.cust_account_id
136 AND ca.party_id = p_party_id;
137 ELSIF p_cust_account_id IS NOT NULL THEN
138 SELECT sum
139 (
140 (
141 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
142 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
143 nvl(ps.acctd_amount_due_remaining, 0)
144 ) *
145 (ps.due_date - ps.trx_date)
146 ) /
147 sum (
148 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
149 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
150 nvl(ps.acctd_amount_due_remaining, 0)
151 )
152 INTO l_wtd_days_terms
153 FROM ar_payment_schedules ps
154 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
155 AND ps.class in ('INV','DEP','DM','CB')
156 AND ps.payment_schedule_id <> -1
157 AND ps.customer_id = p_cust_account_id;
158 ELSIF p_customer_site_use_id IS NOT NULL THEN
159 SELECT sum
160 (
161 (
162 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
163 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
164 nvl(ps.acctd_amount_due_remaining, 0)
165 ) *
166 (ps.due_date - ps.trx_date)
167 ) /
168 sum (
169 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
170 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
171 nvl(ps.acctd_amount_due_remaining, 0)
172 )
173 INTO l_wtd_days_terms
174 FROM ar_payment_schedules ps
175 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
176 AND ps.class in ('INV','DEP','DM','CB')
177 AND ps.payment_schedule_id <> -1
178 AND ps.customer_site_use_id = p_customer_site_use_id;
179 END IF;
180
181 l_num_val := TRUNC(NVL(l_wtd_days_terms, 0));
182 l_char_val := RTRIM(TO_CHAR(l_num_val));
183 RETURN l_char_val;
184
185 EXCEPTION
186 when others then
187 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_wtd_days_terms - Exception = ' ||SQLERRM);
188 RETURN(TO_CHAR(0));
189 END GET_WTD_DAYS_TERMS;
190
191 FUNCTION GET_WTD_DAYS_LATE(p_party_id IN NUMBER,
192 p_cust_account_id IN NUMBER,
193 p_customer_site_use_id IN NUMBER,
194 p_org_id IN NUMBER) RETURN VARCHAR2 AS
195
196 l_wtd_days_late NUMBER;
197 l_num_val NUMBER;
198 l_char_val VARCHAR2(1000);
199
200 BEGIN
201
202 If p_org_id is null then
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 end if;
205
206 -- fnd_request.set_org_id(p_org_id);
207 mo_global.set_policy_context('S',p_org_id);
208
209 IF p_party_id IS NOT NULL THEN
210 SELECT sum
211 (
212 (
213 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
214 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
215 nvl(ps.acctd_amount_due_remaining, 0)
216 ) *
217 (TRUNC(sysdate) - ps.due_date)
218 ) /
219 sum (
220 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
221 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
222 nvl(ps.acctd_amount_due_remaining, 0)
223 )
224 INTO l_wtd_days_late
225 FROM ar_payment_schedules ps, hz_cust_accounts ca
226 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
227 AND ps.class in ('INV','DEP','DM','CB')
228 AND ps.status = 'OP'
229 AND ps.payment_schedule_id <> -1
230 AND ps.customer_id = ca.cust_account_id
231 AND ca.party_id = p_party_id;
232 ELSIF p_cust_account_id IS NOT NULL THEN
233 SELECT sum
234 (
235 (
236 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
237 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
238 nvl(ps.acctd_amount_due_remaining, 0)
239 ) *
240 (TRUNC(sysdate) - ps.due_date)
241 ) /
242 sum (
243 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
244 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
245 nvl(ps.acctd_amount_due_remaining, 0)
246 )
247 INTO l_wtd_days_late
248 FROM ar_payment_schedules ps
249 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
250 AND ps.class in ('INV','DEP','DM','CB')
251 AND ps.status = 'OP'
252 AND ps.payment_schedule_id <> -1
253 AND ps.customer_id = p_cust_account_id;
254 ELSIF p_customer_site_use_id IS NOT NULL THEN
255 SELECT sum
256 (
257 (
258 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
259 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
260 nvl(ps.acctd_amount_due_remaining, 0)
261 ) *
262 (TRUNC(sysdate) - ps.due_date)
263 ) /
264 sum (
265 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
266 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
267 nvl(ps.acctd_amount_due_remaining, 0)
268 )
269 INTO l_wtd_days_late
270 FROM ar_payment_schedules ps
271 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
272 AND ps.class in ('INV','DEP','DM','CB')
273 AND ps.status = 'OP'
274 AND ps.payment_schedule_id <> -1
275 AND ps.customer_site_use_id = p_customer_site_use_id;
276 END IF;
277
278 l_num_val := TRUNC(NVL(l_wtd_days_late,0));
279 l_char_val := RTRIM(TO_CHAR(l_num_val));
280 RETURN l_char_val;
281
282 EXCEPTION
283 when others then
284 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_wtd_days_late - Exception = ' ||SQLERRM);
285 RETURN(TO_CHAR(0));
286 END GET_WTD_DAYS_LATE;
287
288 FUNCTION GET_TRUE_DSO(p_party_id IN NUMBER,
289 p_cust_account_id IN NUMBER,
290 p_customer_site_use_id IN NUMBER,
291 p_org_id IN NUMBER) RETURN VARCHAR2 AS
292 l_sales NUMBER;
293 l_beg_ar NUMBER;
294 l_end_ar NUMBER;
295 l_dso NUMBER;
296 l_num_val NUMBER;
297 l_char_val VARCHAR2(1000);
298
299 BEGIN
300
301 If p_org_id is null then
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 end if;
304
305 -- fnd_request.set_org_id(p_org_id);
306 mo_global.set_policy_context('S',p_org_id);
307
308 GET_COMMON(p_org_id);
309
310 /*-----------------------------------------------------------------------
311 DSO = ( Period Average Receivables / Average Sales per day)
312
313 where tot outs rec = sum of all receivables less all receipts (use COMP_REM_REC)
314 avg sales per day = sum of all receivables (use COMP_TOT_REC) / days in period
315 -----------------------------------------------------------------------*/
316 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - g_base_currency_code = ' ||g_base_currency_code);
317 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - g_base_precision = ' ||g_base_precision);
318 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - g_base_min_acc_unit = ' ||g_base_min_acc_unit);
319
320 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
321 p_party_id, p_cust_account_id, p_customer_site_use_id);
322
323 l_beg_ar := IEX_COLL_IND.COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(add_months(sysdate, -12)) - 1,
324 p_party_id, p_cust_account_id, p_customer_site_use_id);
325
326 l_end_ar := IEX_COLL_IND.COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
327 p_party_id, p_cust_account_id, p_customer_site_use_id);
328
329 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - l_sales = ' ||l_sales);
330 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - l_beg_ar = ' ||l_beg_ar);
331 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - l_end_ar = ' ||l_end_ar);
332
333
334 if ( nvl(l_sales,0) = 0 ) then
335 l_dso := 0;
336 else
337 l_dso := (((l_beg_ar + l_end_ar)/2)/l_sales)*(TRUNC(sysdate) - TRUNC(add_months(sysdate, -12)));
338 end if;
339
340 l_num_val := ROUND(nvl(l_dso,0), 0);
341 l_char_val := RTRIM(TO_CHAR(l_num_val));
342
343 RETURN l_char_val;
344
345 exception
346 When others then
347 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.get_true_dso - Exception = ' ||SQLERRM);
348 return null;
349
350 END GET_TRUE_DSO;
351
352 FUNCTION COMP_TOT_REC(p_start_date IN DATE,
353 p_end_date IN DATE,
354 p_party_id IN NUMBER,
355 p_cust_account_id IN NUMBER,
356 p_customer_site_use_id IN NUMBER) RETURN NUMBER AS
357
358 l_tot_rec NUMBER;
359 l_temp_start DATE;
360 l_num_val NUMBER;
361 l_char_val VARCHAR2(1000);
362
363 BEGIN
364
365 if p_start_date is null then
366 l_temp_start := to_date('01/01/1952','MM/DD/YYYY');
367 else
368 l_temp_start := p_start_date;
369 end if;
370
371 IF p_party_id IS NOT NULL THEN
372 SELECT SUM(arpcurr.functional_amount(
373 ps.amount_due_original,
374 g_base_currency_code,
375 nvl(ps.exchange_rate,1),
376 g_base_precision,
377 g_base_min_acc_unit) +
378 IEX_COLL_IND.GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
379 INTO l_tot_rec
380 FROM ar_payment_schedules ps,
381 hz_cust_accounts ca
382 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
383 AND ps.payment_schedule_id <> -1
384 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
385 AND ps.customer_id = ca.cust_account_id
386 AND ca.party_id = p_party_id;
387 ELSIF p_cust_account_id IS NOT NULL THEN
388 SELECT SUM(arpcurr.functional_amount(
389 ps.amount_due_original,
390 g_base_currency_code,
391 nvl(ps.exchange_rate,1),
392 g_base_precision,
393 g_base_min_acc_unit) +
394 IEX_COLL_IND.GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
395 INTO l_tot_rec
396 FROM ar_payment_schedules ps
397 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
398 AND ps.payment_schedule_id <> -1
399 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
400 AND ps.customer_id = p_cust_account_id;
401 ELSIF p_customer_site_use_id IS NOT NULL THEN
402 SELECT SUM(arpcurr.functional_amount(
403 ps.amount_due_original,
404 g_base_currency_code,
405 nvl(ps.exchange_rate,1),
406 g_base_precision,
407 g_base_min_acc_unit) +
408 IEX_COLL_IND.GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
409 INTO l_tot_rec
410 FROM ar_payment_schedules ps
411 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
412 AND ps.payment_schedule_id <> -1
413 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
414 AND ps.customer_site_use_id = p_customer_site_use_id;
415 END IF;
416
417 l_num_val := nvl(l_tot_rec, 0);
418 RETURN l_num_val;
419
420 exception
421 When others then
422 iex_debug_pub.logmessage (' IEX_COLL_DSO_PUB.comp_tot_rec - Exception = ' ||SQLERRM);
423 return(0);
424 END COMP_TOT_REC;
425
426
427 Procedure Get_Common(p_org_id number) is
428
429 begin
430
431 select gll.currency_code, c.precision, c.minimum_accountable_unit
432 INTO g_base_currency_code,g_base_precision,g_base_min_acc_unit
433 from ar_system_parameters sp,
434 gl_ledgers_public_v gll,
435 fnd_currencies c
436 where
437 gll.ledger_id = sp.set_of_books_id
438 and gll.currency_code = c.currency_code;
439
440 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_common - g_base_currency_code = ' ||g_base_currency_code);
441 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_common - g_base_precision = ' ||g_base_precision);
442 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.get_common - g_base_min_acc_unit = ' ||g_base_min_acc_unit);
443
444
445 exception
446 when others then
447 iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.main selection - Exception = ' ||SQLERRM);
448 null;
449
450 end GET_COMMON;
451
452 --Start for bug 10091833 gnramasa 22nd Sep 10
453 FUNCTION GET_SALES(p_party_id IN NUMBER,
454 p_cust_account_id IN NUMBER,
455 p_customer_site_use_id IN NUMBER,
456 p_org_id IN NUMBER) RETURN VARCHAR2 AS
457 l_sales NUMBER;
458 l_num_val NUMBER;
459 l_char_val VARCHAR2(1000);
460
461 BEGIN
462
463 iex_debug_pub.logmessage ('IEX_COLL_IND_PUB.GET_SALES Start ');
464 mo_global.set_policy_context('S',p_org_id);
465
466 iex_debug_pub.logmessage ('IEX_COLL_IND_PUB.GET_SALES: Calling GET_COMMON with org_id: ' || p_org_id);
467 GET_COMMON(p_org_id);
468 iex_debug_pub.logmessage ('IEX_COLL_IND_PUB.GET_SALES: After Calling GET_COMMON');
469
470 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
471 p_party_id, p_cust_account_id, p_customer_site_use_id);
472
473 iex_debug_pub.logmessage ('IEX_COLL_IND_PUB.GET_SALES: l_sales: ' || l_sales);
474 l_num_val := NVL(l_sales, 0);
475 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_base_currency_code, 50)));
476 iex_debug_pub.logmessage ('IEX_COLL_IND_PUB.GET_SALES: l_char_val: ' || l_char_val);
477 RETURN l_char_val;
478 exception
479 when others then
480 iex_debug_pub.logmessage ('IEX_COLL_IND_PUB.GET_SALES - Exception = ' ||SQLERRM);
481 null;
482 END GET_SALES;
483 --End for bug 10091833 gnramasa 22nd Sep 10
484
485 END IEX_COLL_IND_PUB;