1 PACKAGE BODY IEX_COLL_IND AS
2 /* $Header: iexvmtib.pls 120.17.12020000.2 2012/07/24 19:16:13 schekuri ship $ */
3 /*-------------------------------------------------------------------------+
4 | PUBLIC FUNCTION |
5 | |
6 | GET_AVG_DAYS_LATE |
7 | |
8 | DESCRIPTION |
9 | This function will compute for average days late |
10 | REQUIRES |
11 | |
12 | OPTIONAL |
13 | |
14 | RETURNS |
15 | Average Days Late |
16 | |
17 | NOTES |
18 | |
19 | EXAMPLE |
20 | |
21 | MODIFICATION HISTORY |
22 | |
23 +-------------------------------------------------------------------------*/
24
25 FUNCTION GET_AVG_DAYS_LATE(p_party_id IN NUMBER,
26 p_cust_account_id IN NUMBER,
27 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
28
29 l_avg_days_late NUMBER;
30 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
31 l_num_val NUMBER;
32 l_char_val VARCHAR2(1000);
33 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
34
35 BEGIN
36
37 IF p_party_id IS NOT NULL THEN
38 --Start of comment for Bug 8201317 14-Jun-2010 barathsr
39 /* SELECT sum(TRUNC(sysdate) - ps.due_date)
40 / COUNT(1)
41 INTO l_avg_days_late
42 FROM ar_payment_schedules ps, hz_cust_accounts ca
43 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
44 AND ps.class in ('INV','DEP','DM','CB')
45 AND ps.gl_date_closed > TRUNC(sysdate)
46 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
47 AND ps.status = 'OP'
48 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
49 AND ps.due_date < TRUNC(sysdate)
50 AND ps.payment_schedule_id <> -1
51 AND ca.cust_account_id = ps.customer_id
52 AND ca.party_id = p_party_id;
53 ELSIF p_cust_account_id IS NOT NULL THEN
54 SELECT sum(TRUNC(sysdate) - ps.due_date)
55 / COUNT(1)
56 INTO l_avg_days_late
57 FROM ar_payment_schedules ps
58 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
59 AND ps.class in ('INV','DEP','DM','CB')
60 AND ps.gl_date_closed > TRUNC(sysdate)
61 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
62 AND ps.status = 'OP'
63 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
64 AND ps.due_date < TRUNC(sysdate)
65 AND ps.payment_schedule_id <> -1
66 AND ps.customer_id = p_cust_account_id;
67 ELSIF p_customer_site_use_id IS NOT NULL THEN
68 SELECT sum(TRUNC(sysdate) - ps.due_date)
69 / COUNT(1)
70 INTO l_avg_days_late
71 FROM ar_payment_schedules ps
72 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
73 AND ps.class in ('INV','DEP','DM','CB')
74 AND ps.gl_date_closed > TRUNC(sysdate)
75 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
76 AND ps.status = 'OP'
77 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
78 AND ps.due_date < TRUNC(sysdate)
79 AND ps.payment_schedule_id <> -1
80 AND ps.customer_site_use_id = p_customer_site_use_id;*/
81 --End of comment for Bug 8201317 14-Jun-2010 barathsr
82 --Begin Bug 8201317 14-Jun-2010 barathsr
83 SELECT round(sum(TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
84 / COUNT(1)) AVG_DAYS_LATE
85 INTO l_avg_days_late
86 FROM ar_payment_schedules ps, iex_delinquencies del
87 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
88 and ps.payment_schedule_id=del.payment_schedule_id
89 AND ps.class in ('INV','DEP','DM','CB')
90 AND ps.due_date <= TRUNC(sysdate)
91 AND ps.payment_schedule_id <> -1
92 AND del.party_cust_id = p_party_id;
93 ELSIF p_cust_account_id IS NOT NULL THEN
94 SELECT round(sum(TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
95 / COUNT(1))AVG_DAYS_LATE
96 INTO l_avg_days_late
97 FROM ar_payment_schedules ps, iex_delinquencies del
98 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
99 and ps.payment_schedule_id=del.payment_schedule_id
100 AND ps.class in ('INV','DEP','DM','CB')
101 AND ps.due_date <= TRUNC(sysdate)
102 AND ps.payment_schedule_id <> -1
103 and ps.customer_id=p_cust_account_id;
104 ELSIF p_customer_site_use_id IS NOT NULL THEN
105 SELECT round(sum(TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
106 / COUNT(1))AVG_DAYS_LATE
107 INTO l_avg_days_late
108 FROM ar_payment_schedules ps,iex_delinquencies del
109 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
110 and ps.payment_schedule_id=del.payment_schedule_id
111 AND ps.class in ('INV','DEP','DM','CB')
112 AND ps.due_date <= TRUNC(sysdate)
113 AND ps.payment_schedule_id <> -1
114 AND ps.customer_site_use_id = p_customer_site_use_id;
115 --Begin Bug 8201317 14-Jun-2010 barathsr
116 END IF;
117
118 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
119 -- RETURN TO_CHAR(TRUNC(NVL(l_avg_days_late,0)));
120
121 l_num_val := TRUNC(NVL(l_avg_days_late,0));
122 l_char_val := RTRIM(TO_CHAR(l_num_val));
123
124 RETURN l_char_val;
125 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
129
130 END GET_AVG_DAYS_LATE;
131
132
133 /*-------------------------------------------------------------------------+
134 | PUBLIC FUNCTION |
135 | |
136 | GET_WTD_DAYS_LATE |
137 | |
138 | DESCRIPTION |
139 | This function will compute for weighted average |
140 | days late |
141 | Added calls to GET_ADJ_TOTAL and GET_APPS_TOTAL |
142 | REQUIRES |
143 | |
144 | OPTIONAL |
145 | |
146 | RETURNS |
147 | Weighted Average Days Late |
148 | |
149 | NOTES |
150 | |
151 | EXAMPLE |
152 | |
153 | MODIFICATION HISTORY |
154 | |
155 +-------------------------------------------------------------------------*/
156
157 FUNCTION GET_WTD_DAYS_LATE(p_party_id IN NUMBER,
158 p_cust_account_id IN NUMBER,
159 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
160
161 l_wtd_days_late NUMBER;
162 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
163 l_num_val NUMBER;
164 l_char_val VARCHAR2(1000);
165 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
166
167 BEGIN
168
169 IF p_party_id IS NOT NULL THEN
170 --Start of comment for Bug 8201317 14-Jun-2010 barathsr
171 /* SELECT sum
172 (
173 (
174 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
175 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
176 nvl(ps.acctd_amount_due_remaining, 0)
177 ) *
178 (TRUNC(sysdate) - ps.due_date)
179 ) /
180 sum (
181 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
182 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
183 nvl(ps.acctd_amount_due_remaining, 0)
184 )
185 INTO l_wtd_days_late
186 FROM ar_payment_schedules ps, hz_cust_accounts ca
187 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
188 AND ps.class in ('INV','DEP','DM','CB')
189 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
190 --AND ps.gl_date_closed > TRUNC(sysdate)
191 --AND ps.due_date < TRUNC(sysdate)
192 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
193 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
194 AND ps.status = 'OP'
195 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
196 AND ps.payment_schedule_id <> -1
197 AND ps.customer_id = ca.cust_account_id
198 AND ca.party_id = p_party_id;
199 ELSIF p_cust_account_id IS NOT NULL THEN
200 SELECT sum
201 (
202 (
203 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
204 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
205 nvl(ps.acctd_amount_due_remaining, 0)
206 ) *
207 (TRUNC(sysdate) - ps.due_date)
208 ) /
209 sum (
210 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
211 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
212 nvl(ps.acctd_amount_due_remaining, 0)
213 )
214 INTO l_wtd_days_late
215 FROM ar_payment_schedules ps
216 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
217 AND ps.class in ('INV','DEP','DM','CB')
218 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
219 -- AND ps.gl_date_closed > TRUNC(sysdate)
220 -- AND ps.due_date < TRUNC(sysdate)
221 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
222 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
223 AND ps.status = 'OP'
224 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
225 AND ps.payment_schedule_id <> -1
226 AND ps.customer_id = p_cust_account_id;
227 ELSIF p_customer_site_use_id IS NOT NULL THEN
228 SELECT sum
229 (
230 (
231 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
232 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
233 nvl(ps.acctd_amount_due_remaining, 0)
234 ) *
235 (TRUNC(sysdate) - ps.due_date)
236 ) /
237 sum (
238 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
239 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
240 nvl(ps.acctd_amount_due_remaining, 0)
241 )
242 INTO l_wtd_days_late
243 FROM ar_payment_schedules ps
244 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
245 AND ps.class in ('INV','DEP','DM','CB')
246 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
247 -- AND ps.gl_date_closed > TRUNC(sysdate)
248 -- AND ps.due_date < TRUNC(sysdate)
249 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
250 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
251 AND ps.status = 'OP'
252 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
253 AND ps.payment_schedule_id <> -1
254 AND ps.customer_site_use_id = p_customer_site_use_id;*/
255 --End of comment for Bug 8201317 14-Jun-2010 barathsr
256 --Begin Bug 8201317 14-Jun-2010 barathsr
257 SELECT round(sum
258 (
259 (
260 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
261 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
262 nvl(ps.acctd_amount_due_remaining, 0)
263 ) *
264 (TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
265 ) /
266 sum (
267 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
268 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
269 nvl(ps.acctd_amount_due_remaining, 0)
270 ))WTD_AVG_DAYS_LATE
271 INTO l_wtd_days_late
272 FROM ar_payment_schedules ps,iex_delinquencies del
273 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
274 and ps.payment_schedule_id=del.payment_schedule_id
275 AND ps.class in ('INV','DEP','DM','CB')
276 AND ps.due_date <= TRUNC(sysdate)
277 AND ps.payment_schedule_id <> -1
278 and del.party_cust_id=p_party_id;
279
280 ELSIF p_cust_account_id IS NOT NULL THEN
281 SELECT round(sum
282 (
283 (
284 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
285 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
286 nvl(ps.acctd_amount_due_remaining, 0)
287 ) *
288 (TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
289 ) /
290 sum (
291 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
292 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
293 nvl(ps.acctd_amount_due_remaining, 0)
294 ))WTD_AVG_DAYS_LATE
295 INTO l_wtd_days_late
296 FROM ar_payment_schedules ps, iex_delinquencies del
297 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
298 and ps.payment_schedule_id=del.payment_schedule_id
299 AND ps.class in ('INV','DEP','DM','CB')
300 AND ps.due_date <= TRUNC(sysdate)
301 and ps.payment_schedule_id <> -1
302 and ps.customer_id=p_cust_account_id;
303
304 ELSIF p_customer_site_use_id IS NOT NULL THEN
305 SELECT round(sum
306 (
307 (
308 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
309 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
310 nvl(ps.acctd_amount_due_remaining, 0)
311 ) *
312 (TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
313 ) /
314 sum (
315 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
316 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
317 nvl(ps.acctd_amount_due_remaining, 0)
318 ))WTD_AVG_DAYS_LATE
319 INTO l_wtd_days_late
320 FROM ar_payment_schedules ps, iex_delinquencies del
321 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
322 and ps.payment_schedule_id=del.payment_schedule_id
323 AND ps.class in ('INV','DEP','DM','CB')
324 AND ps.due_date <= TRUNC(sysdate)
325 and ps.payment_schedule_id <> -1
326 and ps.customer_site_use_id=p_customer_site_use_id;
327 --End Bug 8201317 14-Jun-2010 barathsr
328
329 END IF;
330
331 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
332 --RETURN TO_CHAR(TRUNC(NVL(l_wtd_days_late,0)));
333 l_num_val := TRUNC(NVL(l_wtd_days_late,0));
334 l_char_val := RTRIM(TO_CHAR(l_num_val));
335 RETURN l_char_val;
336 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
340 END GET_WTD_DAYS_LATE;
341
342 /*-------------------------------------------------------------------------+
343 | PUBLIC FUNCTION |
344 | |
345 | GET_WTD_DAYS_PAID |
346 | |
347 | DESCRIPTION |
348 | This function will compute for weighted average |
349 | days paid |
350 | REQUIRES |
351 | |
352 | OPTIONAL |
353 | |
354 | RETURNS |
355 | Weighted Average Days Paid |
356 | |
357 | NOTES |
358 | |
359 | EXAMPLE |
360 | |
361 | MODIFICATION HISTORY |
362 | |
363 +-------------------------------------------------------------------------*/
364
365 FUNCTION GET_WTD_DAYS_PAID(p_party_id IN NUMBER,
366 p_cust_account_id IN NUMBER,
367 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
368
369 l_wtd_days_paid NUMBER;
370 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
371 l_num_val NUMBER;
372 l_char_val VARCHAR2(1000);
373 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
374
375 BEGIN
376
377 IF p_party_id IS NOT NULL THEN
378 SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
379 / SUM(ra.amount_applied)
380 , 0) WEIGHTED_AVG_DAYS_PAID
381 INTO l_wtd_days_paid
382 FROM ar_receivable_applications ra,
383 ar_payment_schedules ps,
384 hz_cust_accounts ca
385 WHERE ps.customer_id = ca.cust_account_id
386 AND ca.party_id = p_party_id
387 AND ra.status = 'APP'
388 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
389 AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
390 AND ps.class in ('INV','DEP','DM','CB')
391 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
392 -- AND ps.gl_date_closed > TRUNC(sysdate)
393 -- AND ps.due_date < TRUNC(sysdate)
394 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
395 -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
396 AND ps.status = 'CL'
397 -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
398 AND ps.payment_schedule_id <> -1;
399 ELSIF p_cust_account_id IS NOT NULL THEN
400 SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
401 / SUM(ra.amount_applied)
402 , 0) WEIGHTED_AVG_DAYS_PAID
403 INTO l_wtd_days_paid
404 FROM ar_receivable_applications ra,
405 ar_payment_schedules ps
406 WHERE ps.customer_id = p_cust_account_id
407 AND ra.status = 'APP'
408 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
409 AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
410 AND ps.class in ('INV','DEP','DM','CB')
411 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
412 -- AND ps.gl_date_closed > TRUNC(sysdate)
413 -- AND ps.due_date < TRUNC(sysdate)
414 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
415 -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
416 AND ps.status = 'CL'
417 -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
418 AND ps.payment_schedule_id <> -1;
419 ELSIF p_customer_site_use_id IS NOT NULL THEN
420 SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
421 / SUM(ra.amount_applied)
422 , 0) WEIGHTED_AVG_DAYS_PAID
423 INTO l_wtd_days_paid
424 FROM ar_receivable_applications ra,
425 ar_payment_schedules ps
426 WHERE ps.customer_site_use_id = p_customer_site_use_id
427 AND ra.status = 'APP'
428 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
429 AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
430 AND ps.class in ('INV','DEP','DM','CB')
431 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
432 -- AND ps.gl_date_closed > TRUNC(sysdate)
433 -- AND ps.due_date < TRUNC(sysdate)
434 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
435 -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
436 AND ps.status = 'CL'
437 -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
438 AND ps.payment_schedule_id <> -1;
439 END IF;
440
441 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
442 --RETURN TO_CHAR(TRUNC(NVL(l_wtd_days_paid, 0)));
443 l_num_val := TRUNC(NVL(l_wtd_days_paid, 0));
444 l_char_val := RTRIM(TO_CHAR(l_num_val));
445 RETURN l_char_val;
446 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
450 END GET_WTD_DAYS_PAID;
451
452
453 /*-------------------------------------------------------------------------+
454 | PUBLIC FUNCTION |
455 | |
456 | GET_WTD_DAYS_TERMS |
457 | |
458 | DESCRIPTION |
459 | This function will compute for weighted average |
460 | days terms |
461 | REQUIRES |
462 | |
463 | OPTIONAL |
464 | |
465 | RETURNS |
466 | Weighted Average Days Terms |
467 | |
468 | NOTES |
469 | |
470 | EXAMPLE |
471 | |
472 | MODIFICATION HISTORY |
473 | |
474 +-------------------------------------------------------------------------*/
475
476 FUNCTION GET_WTD_DAYS_TERMS(p_party_id IN NUMBER,
477 p_cust_account_id IN NUMBER,
478 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
479
480 l_wtd_days_terms NUMBER;
481 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
482 l_num_val NUMBER;
483 l_char_val VARCHAR2(1000);
484 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
485
486 BEGIN
487
488 IF p_party_id IS NOT NULL THEN
489 SELECT sum
490 (
491 (
492 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
493 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
494 nvl(ps.acctd_amount_due_remaining, 0)
495 ) *
496 (ps.due_date - ps.trx_date)
497 ) /
498 sum (
499 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
500 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
501 nvl(ps.acctd_amount_due_remaining, 0)
502 )
503 INTO l_wtd_days_terms
504 FROM ar_payment_schedules ps, hz_cust_accounts ca
505 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
506 AND ps.class in ('INV','DEP','DM','CB')
507 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
508 -- AND ps.gl_date_closed > TRUNC(sysdate)
509 -- AND ps.due_date < TRUNC(sysdate)
510 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
511 AND ps.payment_schedule_id <> -1
512 AND ps.customer_id = ca.cust_account_id
513 AND ca.party_id = p_party_id;
514 ELSIF p_cust_account_id IS NOT NULL THEN
515 SELECT sum
516 (
517 (
518 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
519 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
520 nvl(ps.acctd_amount_due_remaining, 0)
521 ) *
522 (ps.due_date - ps.trx_date)
523 ) /
524 sum (
525 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
526 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
527 nvl(ps.acctd_amount_due_remaining, 0)
528 )
529 INTO l_wtd_days_terms
530 FROM ar_payment_schedules ps
531 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
532 AND ps.class in ('INV','DEP','DM','CB')
533 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
534 -- AND ps.gl_date_closed > TRUNC(sysdate)
535 -- AND ps.due_date < TRUNC(sysdate)
536 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
537 AND ps.payment_schedule_id <> -1
538 AND ps.customer_id = p_cust_account_id;
539 ELSIF p_customer_site_use_id IS NOT NULL THEN
540 SELECT sum
541 (
542 (
543 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
544 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
545 nvl(ps.acctd_amount_due_remaining, 0)
546 ) *
547 (ps.due_date - ps.trx_date)
548 ) /
549 sum (
550 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
551 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
552 nvl(ps.acctd_amount_due_remaining, 0)
553 )
554 INTO l_wtd_days_terms
555 FROM ar_payment_schedules ps
556 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
557 AND ps.class in ('INV','DEP','DM','CB')
558 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
559 -- AND ps.gl_date_closed > TRUNC(sysdate)
560 -- AND ps.due_date < TRUNC(sysdate)
561 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
562 AND ps.payment_schedule_id <> -1
563 AND ps.customer_site_use_id = p_customer_site_use_id;
564 END IF;
565
566 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
567 --RETURN TO_CHAR(TRUNC(NVL(l_wtd_days_terms, 0)));
568 l_num_val := TRUNC(NVL(l_wtd_days_terms, 0));
569 l_char_val := RTRIM(TO_CHAR(l_num_val));
570 RETURN l_char_val;
571 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
572
573 EXCEPTION
574 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
575 END GET_WTD_DAYS_TERMS;
576
577 FUNCTION GET_CEI(p_party_id IN NUMBER,
578 p_cust_account_id IN NUMBER,
579 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
580 l_cei NUMBER;
581 l_sales NUMBER;
582 l_beg_ar NUMBER;
583 l_end_ar NUMBER;
584 l_curr_ar NUMBER;
585 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
586 l_num_val NUMBER;
587 l_char_val VARCHAR2(1000);
588 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
589
590 BEGIN
591 /*-----------------------------------------------------------------------
592 CEI = ( Beginning Receivables + ( Credit Sales/ N*) - Ending Total Receivables) * 100
593 / (Beginning Receivables + ( Credit Sales/N*) - Ending Current Receivables)
594
595 *N= Number of Months Can do this monthly, quarterly , and annually
596 */
597
598 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
599 p_party_id, p_cust_account_id, p_customer_site_use_id);
600
601 l_beg_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(add_months(sysdate, -12)) - 1,
602 p_party_id, p_cust_account_id, p_customer_site_use_id);
603
604 l_end_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
605 p_party_id, p_cust_account_id, p_customer_site_use_id);
606
607 l_curr_ar := comp_curr_rec(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
608 p_party_id, p_cust_account_id, p_customer_site_use_id);
609
610 l_cei := (l_beg_ar + (l_sales / 12) - l_end_ar) * 100 / (l_beg_ar + (l_sales / 12) - l_curr_ar);
611
612 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
613 --RETURN TO_CHAR(TRUNC(NVL(l_cei, 0)));
614 l_num_val := TRUNC(NVL(l_cei, 0));
615 l_char_val := RTRIM(TO_CHAR(l_num_val));
616 RETURN l_char_val;
617 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
618
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 RETURN(TO_CHAR(0));
622 END GET_CEI;
623
624 FUNCTION GET_TRUE_DSO(p_party_id IN NUMBER,
625 p_cust_account_id IN NUMBER,
626 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
627 l_sales NUMBER;
628 l_beg_ar NUMBER;
629 l_end_ar NUMBER;
630 l_dso NUMBER;
631 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
632 l_num_val NUMBER;
633 l_char_val VARCHAR2(1000);
634 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
635
636 BEGIN
637
638 /*-----------------------------------------------------------------------
639 DSO = ( Period Average Receivables / Average Sales per day)
640
641 where tot outs rec = sum of all receivables less all receipts (use COMP_REM_REC)
642 avg sales per day = sum of all receivables (use COMP_TOT_REC) / days in period
643 -----------------------------------------------------------------------*/
644
645 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
646 p_party_id, p_cust_account_id, p_customer_site_use_id);
647
648 l_beg_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(add_months(sysdate, -12)) - 1,
649 p_party_id, p_cust_account_id, p_customer_site_use_id);
650
651 l_end_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
652 p_party_id, p_cust_account_id, p_customer_site_use_id);
653
654 if ( nvl(l_sales,0) = 0 ) then
655 l_dso := 0;
656 else
657 l_dso := (((l_beg_ar + l_end_ar)/2)/l_sales)*(TRUNC(sysdate) - TRUNC(add_months(sysdate, -12)));
658 end if;
659
660 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
661 --RETURN TO_CHAR(ROUND(nvl(l_dso,0), 0));
662 l_num_val := ROUND(nvl(l_dso,0), 0);
663 l_char_val := RTRIM(TO_CHAR(l_num_val));
664 RETURN l_char_val;
665 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
666
667 END GET_TRUE_DSO;
668
669 FUNCTION GET_CONV_DSO(p_party_id IN NUMBER,
670 p_cust_account_id IN NUMBER,
671 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
672 l_conv_dso NUMBER;
673 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
674 l_num_val NUMBER;
675 l_char_val VARCHAR2(1000);
676 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
677
678 BEGIN
679 IF p_party_id IS NOT NULL THEN
680 SELECT
681 ROUND(
682 ( (SUM( DECODE(PS.CLASS,
683 'INV', 1,
684 'DM', 1,
685 'CB', 1,
686 'DEP', 1,
687 'BR', 1,
688 0)
689 * PS.ACCTD_AMOUNT_DUE_REMAINING
690 ) * MAX(SP.CER_DSO_DAYS)
691 )
692 / DECODE(
693 SUM( DECODE(PS.CLASS,
694 'INV', 1,
695 'DM', 1,
696 'DEP', 1,
697 0)
698 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
699 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
700 0)) ,
701 0, 1,
702 SUM( DECODE(PS.CLASS,
703 'INV', 1,
704 'DM', 1,
705 'DEP', 1,
706 0)
707 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
708 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
709 0) )
710 )
711 ), 0) /* DSO */
712 INTO l_conv_dso
713 FROM ar_system_parameters sp,
714 hz_cust_accounts cust_acct,
715 ar_payment_schedules ps
716 WHERE ps.customer_id = cust_acct.cust_account_id
717 AND cust_acct.party_id = p_party_id
718 -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
719 AND ps.status = 'OP'
720 -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
721 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
722 ELSIF p_cust_account_id IS NOT NULL THEN
723 SELECT
724 ROUND(
725 ( (SUM( DECODE(PS.CLASS,
726 'INV', 1,
727 'DM', 1,
728 'CB', 1,
729 'DEP', 1,
730 'BR', 1,
731 0)
732 * PS.ACCTD_AMOUNT_DUE_REMAINING
733 ) * MAX(SP.CER_DSO_DAYS)
734 )
735 / DECODE(
736 SUM( DECODE(PS.CLASS,
737 'INV', 1,
738 'DM', 1,
739 'DEP', 1,
740 0)
741 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
742 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
743 0)) ,
744 0, 1,
745 SUM( DECODE(PS.CLASS,
746 'INV', 1,
747 'DM', 1,
748 'DEP', 1,
749 0)
750 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
751 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
752 0) )
753 )
754 ), 0) /* DSO */
755 INTO l_conv_dso
756 FROM ar_system_parameters sp,
757 ar_payment_schedules ps
758 WHERE ps.customer_id = p_cust_account_id
759 -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
760 AND ps.status = 'OP'
761 -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
762 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
763
764 ELSIF p_customer_site_use_id IS NOT NULL THEN
765 SELECT
766 ROUND(
767 ( (SUM( DECODE(PS.CLASS,
768 'INV', 1,
769 'DM', 1,
770 'CB', 1,
771 'DEP', 1,
772 'BR', 1,
773 0)
774 * PS.ACCTD_AMOUNT_DUE_REMAINING
775 ) * MAX(SP.CER_DSO_DAYS)
776 )
777 / DECODE(
778 SUM( DECODE(PS.CLASS,
779 'INV', 1,
780 'DM', 1,
781 'DEP', 1,
782 0)
783 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
784 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
785 0)) ,
786 0, 1,
787 SUM( DECODE(PS.CLASS,
788 'INV', 1,
789 'DM', 1,
790 'DEP', 1,
791 0)
792 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
793 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
794 0) )
795 )
796 ), 0) /* DSO */
797 INTO l_conv_dso
798 FROM ar_system_parameters sp,
799 ar_payment_schedules ps
800 WHERE ps.customer_site_use_id = p_customer_site_use_id
801 -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
802 AND ps.status = 'OP'
803 -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
804 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
805
806 END IF;
807
808 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
809 --RETURN TO_CHAR(ROUND(NVL(l_conv_dso, 0)));
810 l_num_val := ROUND(NVL(l_conv_dso, 0));
811 l_char_val := RTRIM(TO_CHAR(l_num_val));
812 RETURN l_char_val;
813 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
814
815
816 EXCEPTION
817 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
818 END GET_CONV_DSO;
819
820 FUNCTION GET_NSF_STOP_PMT_COUNT(p_party_id IN NUMBER,
821 p_cust_account_id IN NUMBER,
822 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
823 l_nsf_stop_payment_count NUMBER;
824 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
825 l_num_val NUMBER;
826 l_char_val VARCHAR2(1000);
827 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
828
829 BEGIN
830
831 IF p_party_id IS NOT NULL THEN
832 SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
833 INTO l_nsf_stop_payment_count
834 FROM ar_cash_receipts cr,
835 ar_cash_receipt_history crh,
836 hz_cust_accounts ca
837 WHERE cr.cash_receipt_id = crh.cash_receipt_id
838 AND crh.current_record_flag = 'Y'
839 AND crh.status = 'REVERSED'
840 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
841 -- AND cr.status = 'REV'
842 -- AND cr.status = 'NSF' -- bug 5613019
843 AND cr.status in ('NSF','REV') -- bug 5613019
844 -- END fix bug #4483830--20050714-jypark-change query for NSF info
845 --AND cr.reversal_category = 'NSF' -- big 5613019
846 AND cr.reversal_category in ('NSF','REV') -- bug 5613019
847 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
848 AND cr.pay_from_customer = ca.cust_account_id
849 AND ca.party_id = p_party_id;
850 ELSIF p_cust_account_id IS NOT NULL THEN
851 SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
852 INTO l_nsf_stop_payment_count
853 FROM ar_cash_receipts cr,
854 ar_cash_receipt_history crh
855 WHERE cr.cash_receipt_id = crh.cash_receipt_id
856 AND crh.current_record_flag = 'Y'
857 AND crh.status = 'REVERSED'
858 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
859 -- AND cr.status = 'REV'
860 -- AND cr.status = 'NSF' --bug 5613019
861 AND cr.status in ('NSF','REV') -- bug 5613019
862 -- END fix bug #4483830--20050714-jypark-change query for NSF info
863 --AND cr.reversal_category = 'NSF' -- bug 5613019
864 and cr.reversal_category in ('NSF','REV') -- bug 5613019
865 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
866 AND cr.pay_from_customer = p_cust_account_id;
867 ELSIF p_customer_site_use_id IS NOT NULL THEN
868 SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
869 INTO l_nsf_stop_payment_count
870 FROM ar_cash_receipts cr,
871 ar_cash_receipt_history crh
872 WHERE cr.cash_receipt_id = crh.cash_receipt_id
873 AND crh.current_record_flag = 'Y'
874 AND crh.status = 'REVERSED'
875 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
876 -- AND cr.status = 'REV'
877 -- AND cr.status = 'NSF' --bug 5613019
878 and cr.status in ('NSF','REV') -- bug 5613019
879 -- END fix bug #4483830--20050714-jypark-change query for NSF info
880 -- AND cr.reversal_category = 'NSF' -- bug 5613019
881 and cr.reversal_category in ('NSF','REV') -- bug 5613019
882 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
883 AND cr.customer_site_use_id = p_customer_site_use_id;
884 END IF;
885
886 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
887 --RETURN TO_CHAR(TRUNC(nvl(l_nsf_stop_payment_count, 0)));
888 l_num_val := TRUNC(nvl(l_nsf_stop_payment_count, 0));
889 l_char_val := RTRIM(TO_CHAR(l_num_val));
890 RETURN l_char_val;
891 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
892
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN RETURN(0);
895 END GET_NSF_STOP_PMT_COUNT;
896
897 FUNCTION GET_NSF_STOP_PMT_AMOUNT(p_party_id IN NUMBER,
898 p_cust_account_id IN NUMBER,
899 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
900 l_nsf_stop_payment_amount NUMBER;
901 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
902 l_num_val NUMBER;
903 l_char_val VARCHAR2(1000);
904 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
905
906 BEGIN
907
908 IF p_party_id IS NOT NULL THEN
909 SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
910 INTO l_nsf_stop_payment_amount
911 FROM ar_cash_receipts_all cr,
912 ar_cash_receipt_history_all crh,
913 hz_cust_accounts ca
914 WHERE cr.cash_receipt_id = crh.cash_receipt_id
915 AND crh.current_record_flag = 'Y'
916 AND crh.status = 'REVERSED'
917 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
918 -- AND cr.status = 'REV'
919 -- AND cr.status = 'NSF' -- bug 5613019
920 and cr.status in ('NSF','REV') -- bug 5613019
921 -- END fix bug #4483830--20050714-jypark-change query for NSF info
922 -- AND cr.reversal_category = 'NSF' --bug 5613019
923 and cr.reversal_category in ('NSF','REV') -- bug 5613019
924 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
925 AND cr.pay_from_customer = ca.cust_account_id
926 AND ca.party_id = p_party_id;
927 ELSIF p_cust_account_id IS NOT NULL THEN
928 SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
929 INTO l_nsf_stop_payment_amount
930 FROM ar_cash_receipts_all cr,
931 ar_cash_receipt_history_all crh
932 WHERE cr.cash_receipt_id = crh.cash_receipt_id
933 AND crh.current_record_flag = 'Y'
934 AND crh.status = 'REVERSED'
935 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
936 -- AND cr.status = 'REV'
937 -- AND cr.status = 'NSF' -- bug 5613019
938 and cr.status in ('NSF','REV') -- bug 5613019
939 -- END fix bug #4483830--20050714-jypark-change query for NSF info
940 -- AND cr.reversal_category = 'NSF' --bug 5613019
941 and cr.reversal_category in ('NSF','REV') -- bug 5613019
942 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
943 AND cr.pay_from_customer = p_cust_account_id;
944 ELSIF p_customer_site_use_id IS NOT NULL THEN
945 SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
946 INTO l_nsf_stop_payment_amount
947 FROM ar_cash_receipts_all cr,
948 ar_cash_receipt_history_all crh
949 WHERE cr.cash_receipt_id = crh.cash_receipt_id
950 AND crh.current_record_flag = 'Y'
951 AND crh.status = 'REVERSED'
952 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
953 -- AND cr.status = 'REV'
954 -- AND cr.status = 'NSF' -- bug5613019
955 and cr.status in ('NSF','REV') -- bug 5613019
956 -- END fix bug #4483830--20050714-jypark-change query for NSF info
957 -- AND cr.reversal_category = 'NSF' -- bug 5613019
958 and cr.reversal_category in ('NSF','REV') -- bug 5613019
959 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
960 AND cr.customer_site_use_id = p_customer_site_use_id;
961 END IF;
962
963 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
964 --RETURN TO_CHAR(nvl(l_nsf_stop_payment_amount, 0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 30));
965 l_num_val := nvl(l_nsf_stop_payment_amount, 0);
966 l_char_val := RTRIM(TO_CHAR(l_num_val,fnd_currency.get_format_mask(g_curr_rec.base_currency, 30)));
967 RETURN l_char_val;
968 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
969
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN RETURN(0);
972 END GET_NSF_STOP_PMT_AMOUNT;
973
974 FUNCTION GET_SALES(p_party_id IN NUMBER,
975 p_cust_account_id IN NUMBER,
976 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
977 l_sales NUMBER;
978 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
979 l_num_val NUMBER;
980 l_char_val VARCHAR2(1000);
981 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
982
983 BEGIN
984
985 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
986 p_party_id, p_cust_account_id, p_customer_site_use_id);
987
988 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
989 --RETURN(TO_CHAR(NVL(l_sales, 0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
990 l_num_val := NVL(l_sales, 0);
991 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
992 RETURN l_char_val;
993 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
994
995 END GET_SALES;
996
997
998 FUNCTION GET_DEDUCTION(p_party_id IN NUMBER,
999 p_cust_account_id IN NUMBER,
1000 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
1001 l_adj NUMBER;
1002 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1003 l_num_val NUMBER;
1004 l_char_val VARCHAR2(1000);
1005 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1006
1007 BEGIN
1008 IF p_party_id IS NOT NULL THEN
1009 SELECT
1010 sum( nvl(adj.acctd_amount,0))
1011 INTO l_adj
1012 FROM ar_payment_schedules ps, ar_adjustments adj, hz_cust_accounts ca
1013 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1014 AND ps.payment_schedule_id <> -1
1015 AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
1016 AND ps.customer_id = ca.cust_account_id
1017 AND ca.party_id = p_party_id
1018 AND adj.payment_schedule_id = ps.payment_schedule_id
1019 AND adj.status = 'A'
1020 AND adj.gl_date <= TRUNC(sysdate);
1021 ELSIF p_cust_account_id IS NOT NULL THEN
1022 SELECT
1023 sum( nvl(adj.acctd_amount,0))
1024 INTO l_adj
1025 FROM ar_payment_schedules ps, ar_adjustments adj
1026 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1027 AND ps.payment_schedule_id <> -1
1028 AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
1029 AND ps.customer_id = p_cust_account_id
1030 AND adj.payment_schedule_id = ps.payment_schedule_id
1031 AND adj.status = 'A'
1032 AND adj.gl_date <= TRUNC(sysdate);
1033 ELSIF p_customer_site_use_id IS NOT NULL THEN
1034 SELECT
1035 sum( nvl(adj.acctd_amount,0))
1036 INTO l_adj
1037 FROM ar_payment_schedules ps, ar_adjustments adj
1038 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1039 AND ps.payment_schedule_id <> -1
1040 AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
1041 AND ps.customer_site_use_id = p_customer_site_use_id
1042 AND adj.payment_schedule_id = ps.payment_schedule_id
1043 AND adj.status = 'A'
1044 AND adj.gl_date <= TRUNC(sysdate);
1045 END IF;
1046
1047 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1048 --RETURN(TO_CHAR(nvl(l_adj, 0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1049 l_num_val := nvl(l_adj, 0);
1050 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1051 RETURN l_char_val;
1052 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1053
1054 EXCEPTION
1055 WHEN NO_DATA_FOUND THEN RETURN(0);
1056 END GET_DEDUCTION;
1057
1058 /*-------------------------------------------------------------------------+
1059 | PUBLIC FUNCTION |
1060 | |
1061 | COMP_TOT_REC |
1062 | |
1063 | DESCRIPTION |
1064 | Given a date range, this function will compute the total original |
1065 | receivables within the date range |
1066 | If function is called with a null start date, then the function |
1067 | RETURNs total original receivables as of p_end_date |
1068 | |
1069 | REQUIRES |
1070 | start_date |
1071 | end_date |
1072 | |
1073 | OPTIONAL |
1074 | |
1075 | RETURNS |
1076 | total original receivables |
1077 | |
1078 | NOTES |
1079 | |
1080 | EXAMPLE |
1081 | |
1082 | MODIFICATION HISTORY |
1083 | |
1084 | |
1085 +-------------------------------------------------------------------------*/
1086
1087 FUNCTION COMP_TOT_REC(p_start_date IN DATE,
1088 p_end_date IN DATE,
1089 p_party_id IN NUMBER,
1090 p_cust_account_id IN NUMBER,
1091 p_customer_site_use_id IN NUMBER) RETURN NUMBER AS
1092 l_tot_rec NUMBER;
1093 l_temp_start DATE;
1094
1095 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1096 l_num_val NUMBER;
1097 l_char_val VARCHAR2(1000);
1098 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1099
1100 BEGIN
1101
1102 if p_start_date is null then
1103 -- default date to earliest date to pick up everything prior to
1104 -- p_end_date
1105 l_temp_start := to_date('01/01/1952','MM/DD/YYYY');
1106 else
1107 l_temp_start := p_start_date;
1108 end if;
1109
1110 IF p_party_id IS NOT NULL THEN
1111 SELECT SUM(arpcurr.functional_amount(
1112 ps.amount_due_original,
1113 g_curr_rec.base_currency,
1114 nvl(ps.exchange_rate,1),
1115 g_curr_rec.base_precision,
1116 g_curr_rec.base_min_acc_unit) +
1117 GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
1118 INTO l_tot_rec
1119 FROM ar_payment_schedules ps,
1120 hz_cust_accounts ca
1121 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1122 AND ps.payment_schedule_id <> -1
1123 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
1124 AND ps.customer_id = ca.cust_account_id
1125 AND ca.party_id = p_party_id;
1126 ELSIF p_cust_account_id IS NOT NULL THEN
1127 SELECT SUM(arpcurr.functional_amount(
1128 ps.amount_due_original,
1129 g_curr_rec.base_currency,
1130 nvl(ps.exchange_rate,1),
1131 g_curr_rec.base_precision,
1132 g_curr_rec.base_min_acc_unit) +
1133 GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
1134 INTO l_tot_rec
1135 FROM ar_payment_schedules ps
1136 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1137 AND ps.payment_schedule_id <> -1
1138 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
1139 AND ps.customer_id = p_cust_account_id;
1140 ELSIF p_customer_site_use_id IS NOT NULL THEN
1141 SELECT SUM(arpcurr.functional_amount(
1142 ps.amount_due_original,
1143 g_curr_rec.base_currency,
1144 nvl(ps.exchange_rate,1),
1145 g_curr_rec.base_precision,
1146 g_curr_rec.base_min_acc_unit) +
1147 GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
1148 INTO l_tot_rec
1149 FROM ar_payment_schedules ps
1150 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1151 AND ps.payment_schedule_id <> -1
1152 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
1153 AND ps.customer_site_use_id = p_customer_site_use_id;
1154 END IF;
1155
1156 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1157 --RETURN(nvl(l_tot_rec,0));
1158 l_num_val := nvl(l_tot_rec, 0);
1159 RETURN l_num_val;
1160 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1161
1162 EXCEPTION
1163 WHEN NO_DATA_FOUND THEN RETURN(0);
1164 END COMP_TOT_REC;
1165
1166
1167 /*-------------------------------------------------------------------------+
1168 | PUBLIC FUNCTION |
1169 | |
1170 | COMP_REM_REC |
1171 | |
1172 | DESCRIPTION |
1173 | Given a date range, this function will compute the total remaining |
1174 | receivables within the date range |
1175 | |
1176 | REQUIRES |
1177 | start_date |
1178 | end_date |
1179 | |
1180 | OPTIONAL |
1181 | |
1182 | RETURNS |
1183 | total remaining receivables |
1184 | |
1185 | NOTES |
1186 | |
1187 | EXAMPLE |
1188 | |
1189 | MODIFICATION HISTORY |
1190 | there is no record found |
1191 +-------------------------------------------------------------------------*/
1192
1193 FUNCTION COMP_REM_REC(p_start_date IN DATE,
1194 p_end_date IN DATE,
1195 p_party_id IN NUMBER,
1196 p_cust_account_id IN NUMBER,
1197 p_customer_site_use_id IN NUMBER) RETURN NUMBER IS
1198
1199 l_rem_sales NUMBER;
1200 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1201 l_num_val NUMBER;
1202 l_char_val VARCHAR2(1000);
1203 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1204
1205 BEGIN
1206
1207 l_rem_sales := 0;
1208
1209 IF p_party_id IS NOT NULL THEN
1210 -- compute Remaining balance for given date range
1211
1212 SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
1213 GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
1214 nvl(ps.acctd_amount_due_remaining,0))
1215 INTO l_rem_sales
1216 FROM ar_payment_schedules ps,
1217 hz_cust_accounts ca
1218 WHERE ps.gl_date between p_start_date and p_end_date
1219 AND ps.class in ('INV','DEP','DM','CB')
1220 AND ps.gl_date_closed > p_end_date
1221 AND ps.customer_id = ca.cust_account_id
1222 -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1223 AND ps.status = 'OP'
1224 -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1225 AND ca.party_id = p_party_id;
1226 ELSIF p_cust_account_id IS NOT NULL THEN
1227 -- compute Remaining balance for given date range
1228
1229 SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
1230 GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
1231 nvl(ps.acctd_amount_due_remaining,0))
1232 INTO l_rem_sales
1233 FROM ar_payment_schedules ps
1234 WHERE ps.gl_date between p_start_date and p_end_date
1235 AND ps.class in ('INV','DEP','DM','CB')
1236 AND ps.gl_date_closed > p_end_date
1237 -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1238 AND ps.status = 'OP'
1239 -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1240 AND ps.customer_id = p_cust_account_id;
1241 ELSIF p_customer_site_use_id IS NOT NULL THEN
1242 -- compute Remaining balance for given date range
1243
1244 SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
1245 GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
1246 nvl(ps.acctd_amount_due_remaining,0))
1247 INTO l_rem_sales
1248 FROM ar_payment_schedules ps
1249 WHERE ps.gl_date between p_start_date and p_end_date
1250 AND ps.class in ('INV','DEP','DM','CB')
1251 AND ps.gl_date_closed > p_end_date
1252 -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1253 AND ps.status = 'OP'
1254 -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1255 AND ps.customer_site_use_id = p_customer_site_use_id;
1256 END IF;
1257
1258 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1259 l_num_val := (NVL(l_rem_sales,0));
1260 RETURN l_num_val;
1261 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1262
1263 EXCEPTION
1264 WHEN NO_DATA_FOUND THEN RETURN(0);
1265 END COMP_REM_REC;
1266
1267 /*-------------------------------------------------------------------------+
1268 | PUBLIC FUNCTION |
1269 | |
1270 | comp_curr_rec |
1271 | |
1272 | DESCRIPTION |
1273 | Given a date range, this function will compute the total current |
1274 | receivables within the date range |
1275 | |
1276 | REQUIRES |
1277 | start_date |
1278 | end_date |
1279 | |
1280 | OPTIONAL |
1281 | |
1282 | RETURNS |
1283 | total current receivables |
1284 | |
1285 | NOTES |
1286 | |
1287 | EXAMPLE |
1288 | |
1289 | MODIFICATION HISTORY |
1290 +-------------------------------------------------------------------------*/
1291
1292 FUNCTION COMP_CURR_REC(p_start_date IN DATE,
1293 p_end_date IN DATE,
1294 p_party_id IN NUMBER,
1295 p_cust_account_id IN NUMBER,
1296 p_customer_site_use_id IN NUMBER) RETURN NUMBER IS
1297
1298 l_curr_rec NUMBER;
1299 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1300 l_num_val NUMBER;
1301 l_char_val VARCHAR2(1000);
1302 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1303
1304 BEGIN
1305
1306 l_curr_rec := 0;
1307
1308 IF p_party_id IS NOT NULL THEN
1309 -- compute Remaining balance for given date range
1310
1311 SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
1312 INTO l_curr_rec
1313 FROM ar_payment_schedules ps,
1314 hz_cust_accounts ca
1315 WHERE ps.gl_date between p_start_date and p_end_date
1316 AND ps.class in ('INV','DEP','DM','CB')
1317 AND ps.gl_date_closed > p_end_date
1318 AND ps.status = 'OP'
1319 AND ps.customer_id = ca.cust_account_id
1320 AND ca.party_id = p_party_id
1321 AND ps.due_date > p_end_date;
1322 ELSIF p_cust_account_id IS NOT NULL THEN
1323 -- compute Remaining balance for given date range
1324
1325 SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
1326 INTO l_curr_rec
1327 FROM ar_payment_schedules ps
1328 WHERE ps.gl_date between p_start_date and p_end_date
1329 AND ps.class in ('INV','DEP','DM','CB')
1330 AND ps.gl_date_closed > p_end_date
1331 AND ps.status = 'OP'
1332 AND ps.customer_id = p_cust_account_id
1333 AND ps.due_date > p_end_date;
1334 ELSIF p_customer_site_use_id IS NOT NULL THEN
1335 -- compute Remaining balance for given date range
1336
1337 SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
1338 INTO l_curr_rec
1339 FROM ar_payment_schedules ps
1340 WHERE ps.gl_date between p_start_date and p_end_date
1341 AND ps.class in ('INV','DEP','DM','CB')
1342 AND ps.gl_date_closed > p_end_date
1343 AND ps.status = 'OP'
1344 AND ps.customer_site_use_id = p_customer_site_use_id
1345 AND ps.due_date > p_end_date;
1346 END IF;
1347
1348 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1349 --RETURN (NVL(l_curr_rec,0));
1350 l_num_val := (NVL(l_curr_rec,0));
1351 RETURN l_num_val;
1352 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1353
1354 EXCEPTION
1355 WHEN NO_DATA_FOUND THEN RETURN(0);
1356 END COMP_CURR_REC;
1357
1358
1359 /*========================================================================
1360 | PRIVATE FUNCTION GET_APPS_TOTAL
1361 |
1362 | DESCRIPTION
1363 | Calculates the total applications against a payment_schedule
1364 |
1365 =======================================================================*/
1366
1367 FUNCTION GET_APPS_TOTAL(p_payment_schedule_id IN NUMBER,
1368 p_to_date IN DATE) RETURN NUMBER IS
1369 l_apps_tot NUMBER;
1370
1371 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1372 l_num_val NUMBER;
1373 l_char_val VARCHAR2(1000);
1374 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1375
1376 BEGIN
1377 SELECT sum( nvl(ra.acctd_amount_applied_to,0) +
1378 nvl(ra.acctd_earned_discount_taken,0) +
1379 nvl(ra.acctd_unearned_discount_taken,0))
1380 INTO l_apps_tot
1381 FROM ar_receivable_applications ra
1382 WHERE ra.applied_payment_schedule_id = p_payment_schedule_id
1383 AND ra.status = 'APP'
1384 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1385 AND ra.gl_date <= p_to_date;--Added for Bug 8201317 14-Jun-2010 barathsr
1386
1387 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1388 --RETURN NVL(l_apps_tot,0);
1389 l_num_val := NVL(l_apps_tot,0);
1390 RETURN l_num_val;
1391 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1392
1393 EXCEPTION
1394 WHEN NO_DATA_FOUND THEN RETURN(0);
1395
1396 END GET_APPS_TOTAL;
1397
1398 /*========================================================================
1399 | PRIVATE FUNCTION GET_ADJ_TOTAL
1400 |
1401 | DESCRIPTION
1402 | Calculates the total adjustments against a payment_schedule
1403 |
1404 *=======================================================================*/
1405
1406 FUNCTION GET_ADJ_TOTAL(p_payment_schedule_id IN NUMBER,
1407 p_to_date IN DATE) RETURN NUMBER IS
1408 l_adj_tot NUMBER;
1409
1410 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1411 l_num_val NUMBER;
1412 l_char_val VARCHAR2(1000);
1413 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1414
1415 BEGIN
1416 SELECT sum( nvl(a.acctd_amount,0))
1417 INTO l_adj_tot
1418 FROM ar_adjustments a
1419 WHERE a.payment_schedule_id = p_payment_schedule_id
1420 AND a.status = 'A'
1421 AND a.gl_date <= p_to_date;--Added for Bug 8201317 14-Jun-2010 barathsr
1422
1423 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1424 --RETURN nvl(l_adj_tot,0);
1425 l_num_val := nvl(l_adj_tot,0);
1426 RETURN l_num_val;
1427 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1428
1429 EXCEPTION
1430 WHEN NO_DATA_FOUND THEN RETURN(0);
1431
1432 END GET_ADJ_TOTAL;
1433
1434 FUNCTION GET_ADJ_FOR_TOT_REC(p_payment_schedule_id IN NUMBER,
1435 p_to_date IN DATE) RETURN NUMBER IS
1436 l_adj_for_tot_rec NUMBER;
1437
1438 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1439 l_num_val NUMBER;
1440 l_char_val VARCHAR2(1000);
1441 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1442
1443 BEGIN
1444 SELECT sum( nvl(a.acctd_amount,0))
1445 INTO l_adj_for_tot_rec
1446 FROM ar_adjustments a
1447 WHERE a.payment_schedule_id = p_payment_schedule_id
1448 AND a.status = 'A'
1449 AND a.gl_date <= p_to_date;
1450
1451 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1452 --RETURN nvl(l_adj_for_tot_rec,0);
1453 l_num_val := nvl(l_adj_for_tot_rec,0);
1454 RETURN l_num_val;
1455 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1456
1457 EXCEPTION
1458 WHEN NO_DATA_FOUND THEN RETURN(0);
1459
1460 END GET_ADJ_FOR_TOT_REC;
1461
1462 FUNCTION GET_CREDIT_LIMIT(p_party_id IN NUMBER,
1463 p_cust_account_id IN NUMBER,
1464 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
1465 l_credit_limit NUMBER;
1466 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1467 l_num_val NUMBER;
1468 l_char_val VARCHAR2(1000);
1469 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1470 -- Start for the bug#8630157 by PNAVEENK
1471 l_conversion_type VARCHAR(30);
1472 BEGIN
1473 l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
1474 IF p_party_id IS NOT NULL THEN
1475 -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1476 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1477 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1478 -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1479 -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1480 sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
1481 INTO l_credit_limit
1482 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
1483 -- ar_cmgt_setup_options cm_opt
1484 WHERE prof.party_id = p_party_id
1485 AND prof.site_use_id IS NULL
1486 AND prof.status = 'A'
1487 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
1488 AND prof_amt.cust_account_id = prof.cust_account_id
1489 --Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
1490 AND prof.cust_account_id = -1
1491 --End-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
1492 AND prof_amt.site_use_id IS NULL;
1493
1494 ELSIF p_cust_account_id IS NOT NULL THEN
1495 -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1496 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1497 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1498 -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1499 -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1500 sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
1501 INTO l_credit_limit
1502 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
1503 -- ar_cmgt_setup_options cm_opt
1504 WHERE prof.cust_account_id = p_cust_account_id
1505 AND prof.site_use_id IS NULL
1506 AND prof.status = 'A'
1507 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
1508 AND prof_amt.cust_account_id = p_cust_account_id
1509 AND prof_amt.site_use_id IS NULL;
1510 ELSIF p_customer_site_use_id IS NOT NULL THEN
1511 -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1512 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1513 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1514 -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1515 -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1516 sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
1517 INTO l_credit_limit
1518 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
1519 -- ar_cmgt_setup_options cm_opt
1520 WHERE prof.site_use_id = p_customer_site_use_id
1521 AND prof.status = 'A'
1522 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
1523 AND prof_amt.site_use_id = p_customer_site_use_id;
1524 END IF;
1525 -- End for the bug#8630157 by PNAVEENK
1526 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1527 -- RETURN (TO_CHAR(nvl(l_credit_limit,0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1528 --l_num_val := nvl(l_credit_limit,0);
1529 l_num_val := l_credit_limit;
1530 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1531 RETURN l_char_val;
1532 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1533
1534 EXCEPTION
1535 WHEN NO_DATA_FOUND THEN RETURN(0);
1536
1537 END GET_CREDIT_LIMIT;
1538
1539 FUNCTION GET_HIGH_CREDIT_YTD(p_party_id IN NUMBER,
1540 p_cust_account_id IN NUMBER,
1541 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
1542 l_high_credit_ytd NUMBER;
1543 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1544 l_num_val NUMBER;
1545 l_char_val VARCHAR2(1000);
1546 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1547 -- Start for the bug#8630157 by PNAVEENK
1548 l_conversion_type VARCHAR(30);
1549 BEGIN
1550 l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
1551 IF p_party_id IS NOT NULL THEN
1552 SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1553 -- sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1554 sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
1555 INTO l_high_credit_ytd
1556 FROM ar_trx_summary trx_summ,ar_system_parameters asp,--Added for Bug 9404646 09-Mar-2010 barathsr
1557 -- ar_cmgt_setup_options cm_opt,
1558 hz_cust_accounts ca
1559 WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
1560 AND trx_summ.cust_account_id = ca.cust_account_id
1561 AND ca.party_id = p_party_id;
1562 ELSIF p_cust_account_id IS NOT NULL THEN
1563 SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1564 -- sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1565 sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
1566 INTO l_high_credit_ytd
1567 FROM ar_trx_summary trx_summ,ar_system_parameters asp--Added for Bug 9404646 09-Mar-2010 barathsr
1568 -- ar_cmgt_setup_options cm_opt
1569 WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
1570 AND trx_summ.cust_account_id = p_cust_account_id;
1571 ELSIF p_customer_site_use_id IS NOT NULL THEN
1572 SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1573 -- sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1574 sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
1575 INTO l_high_credit_ytd
1576 FROM ar_trx_summary trx_summ,ar_system_parameters asp--Added for Bug 9404646 09-Mar-2010 barathsr
1577 -- ar_cmgt_setup_options cm_opt
1578 WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
1579 AND trx_summ.site_use_id = p_customer_site_use_id;
1580 END IF;
1581 -- End for the bug#8630157 by PNAVEENK
1582 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1583 --RETURN TO_CHAR(nvl(l_high_credit_ytd,0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50));
1584 l_num_val := nvl(l_high_credit_ytd,0);
1585 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1586 RETURN l_char_val;
1587 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1588
1589 EXCEPTION
1590 WHEN NO_DATA_FOUND THEN RETURN(0);
1591
1592 END GET_HIGH_CREDIT_YTD;
1593
1594 -- bug 12546745 PNAVEENK
1595
1596 function amount_due_org_ocm(p_party_id number,p_cust_account_id NUMBER ,p_customer_site_use_id number,p_start_date date, p_end_date date) return number is
1597 l_amount_due_org number;
1598 l_num_val NUMBER;
1599
1600 begin
1601
1602 IF p_party_id IS NOT NULL THEN
1603 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1604 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1605 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1606 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1607 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1608 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1609 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1610 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1611 INTO l_amount_due_org
1612 from gl_sets_of_books a,
1613 ar_system_parameters b,
1614 ar_trx_summary c,
1615 hz_cust_accounts d
1616 where a.set_of_books_id = b.set_of_books_id
1617 and b.org_id = c.org_id
1618 AND c.cust_account_id = d.cust_account_id
1619 AND d.party_id = p_party_id
1620 and as_of_date BETWEEN p_start_date AND p_end_date;
1621 ELSE IF p_cust_account_id IS NOT NULL THEN
1622 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1623 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1624 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1625 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1626 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1627 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1628 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1629 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1630 INTO l_amount_due_org
1631 from gl_sets_of_books a,
1632 ar_system_parameters b,
1633 ar_trx_summary c
1634 where a.set_of_books_id = b.set_of_books_id
1635 and b.org_id = c.org_id
1636 and c.cust_account_id = p_cust_account_id
1637 and as_of_date BETWEEN p_start_date AND p_end_date;
1638 ELSE IF p_customer_site_use_id IS NOT NULL THEN
1639 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1640 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1641 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1642 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1643 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1644 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1645 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1646 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
1647 INTO l_amount_due_org
1648 from gl_sets_of_books a,
1649 ar_system_parameters b,
1650 ar_trx_summary c
1651 where a.set_of_books_id = b.set_of_books_id
1652 and b.org_id = c.org_id
1653 AND c.site_use_id = p_customer_site_use_id
1654 and as_of_date BETWEEN p_start_date AND p_end_date;
1655 END IF;
1656 END IF;
1657 END IF;
1658
1659
1660
1661 l_num_val := ROUND(nvl(l_amount_due_org,0), 0);
1662 fnd_file.put_line(FND_FILE.LOG,' AMOUNT_DUE_ORG_OCM l_amount_due_org'|| l_num_val);
1663 RETURN l_num_val;
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 RETURN(0);
1667 END amount_due_org_ocm;
1668
1669 function get_adj_total_ocm(p_party_id number ,p_cust_account_id number ,p_customer_site_use_id number, p_start_date date, p_end_date date)
1670 return number is
1671 l_adj_total number;
1672 l_num_val NUMBER;
1673 l_char_val VARCHAR2(1000);
1674 begin
1675 IF p_party_id IS NOT NULL THEN
1676 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1677 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
1678 INTO l_adj_total
1679 from gl_sets_of_books a,
1680 ar_system_parameters b,
1681 ar_trx_summary c,
1682 hz_cust_accounts d
1683 where a.set_of_books_id = b.set_of_books_id
1684 and b.org_id = c.org_id
1685 AND c.cust_account_id = d.cust_account_id
1686 AND d.party_id = p_party_id
1687 and as_of_date BETWEEN p_start_date AND p_end_date;
1688 ELSE IF p_cust_account_id IS NOT NULL THEN
1689 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1690 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
1691 INTO l_adj_total
1692 from gl_sets_of_books a,
1693 ar_system_parameters b,
1694 ar_trx_summary c
1695 where a.set_of_books_id = b.set_of_books_id
1696 and b.org_id = c.org_id
1697 and c.cust_account_id = p_cust_account_id
1698 and as_of_date BETWEEN p_start_date AND p_end_date;
1699 ELSE IF p_customer_site_use_id IS NOT NULL THEN
1700 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1701 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
1702 INTO l_adj_total
1703 from gl_sets_of_books a,
1704 ar_system_parameters b,
1705 ar_trx_summary c
1706 where a.set_of_books_id = b.set_of_books_id
1707 and b.org_id = c.org_id
1708 AND c.site_use_id = p_customer_site_use_id
1709 and as_of_date BETWEEN p_start_date AND p_end_date;
1710 END IF;
1711 END IF;
1712 END IF;
1713
1714 l_num_val := ROUND(nvl(l_adj_total,0), 2);
1715 fnd_file.put_line(FND_FILE.LOG,' GET_ADJ_TOTAL_OCM l_adj_total'|| l_num_val);
1716 RETURN l_num_val;
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 RETURN(0);
1720 END get_adj_total_ocm;
1721
1722
1723 function comp_rem_rec_ocm(p_party_id number ,p_cust_account_id NUMBER ,p_customer_site_use_id number,p_start_date date,p_end_date date) return number is
1724 l_amount_due_rem number;
1725 l_num_val NUMBER;
1726
1727 begin
1728
1729 IF p_party_id IS NOT NULL THEN
1730 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1731 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1732 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1733 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1734 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1735 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1736 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1737 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1738 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1739 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1740 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1741 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1742 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1743 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1744 INTO l_amount_due_rem
1745 from gl_sets_of_books a,
1746 ar_system_parameters b,
1747 ar_trx_summary c,
1748 hz_cust_accounts d
1749 where a.set_of_books_id = b.set_of_books_id
1750 and b.org_id = c.org_id
1751 AND c.cust_account_id = d.cust_account_id
1752 AND d.party_id = p_party_id
1753 and as_of_date BETWEEN p_start_date AND p_end_date;
1754 ELSE IF p_cust_account_id IS NOT NULL THEN
1755 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1756 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1757 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1758 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1759 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1760 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1761 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1762 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1763 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1764 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1765 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1766 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1767 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1768 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1769 INTO l_amount_due_rem
1770 from gl_sets_of_books a,
1771 ar_system_parameters b,
1772 ar_trx_summary c
1773 where a.set_of_books_id = b.set_of_books_id
1774 and b.org_id = c.org_id
1775 and c.cust_account_id = p_cust_account_id
1776 and as_of_date BETWEEN p_start_date AND p_end_date;
1777 ELSE IF p_customer_site_use_id IS NOT NULL THEN
1778 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1779 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1780 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1781 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1782 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1783 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1784 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1785 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
1786 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1787 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1788 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1789 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
1790 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1791 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
1792 INTO l_amount_due_rem
1793 from gl_sets_of_books a,
1794 ar_system_parameters b,
1795 ar_trx_summary c
1796 where a.set_of_books_id = b.set_of_books_id
1797 and b.org_id = c.org_id
1798 AND c.site_use_id = p_customer_site_use_id
1799 and as_of_date BETWEEN p_start_date AND p_end_date;
1800 END IF;
1801 END IF;
1802 END IF;
1803
1804 l_num_val := ROUND(nvl(l_amount_due_rem,0), 2);
1805 fnd_file.put_line(FND_FILE.LOG,' COMP_REM_REC_OCM l_amount_due_rem '|| l_num_val);
1806 RETURN l_num_val;
1807
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 RETURN(0);
1811
1812 END comp_rem_rec_ocm;
1813
1814 PROCEDURE comp_rem_ar(p_party_id number ,p_cust_account_id NUMBER ,p_customer_site_use_id number,p_beg_ar IN OUT NOCOPY NUMBER,p_end_ar IN OUT NOCOPY NUMBER ) IS
1815 l_tot_rec NUMBER;
1816 l_tot_sales_in_year NUMBER;
1817 l_num_val NUMBER;
1818 BEGIN
1819
1820 IF p_party_id IS NOT NULL THEN
1821 select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1822 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1823 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1824 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1825 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1826 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1827 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1828 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1829 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1830 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1831 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1832 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1833 INTO l_tot_rec
1834 from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a, hz_cust_accounts d
1835 where b.set_of_books_id = a.set_of_books_id
1836 AND c.cust_account_id = d.cust_account_id
1837 AND d.party_id = p_party_id
1838 and c.org_id = b.org_id;
1839
1840 SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1841 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1842 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1843 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1844 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1845 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1846 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1847 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1848 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1849 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1850 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1851 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1852 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1853 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1854 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1855 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1856 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1857 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1858 INTO l_tot_sales_in_year
1859 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b, hz_cust_accounts d
1860 WHERE c.org_id = b.org_id
1861 AND c.cust_account_id = d.cust_account_id
1862 AND d.party_id = p_party_id
1863 AND b.set_of_books_id = a.set_of_books_id
1864 and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
1865
1866 ELSE IF p_cust_account_id IS NOT NULL THEN
1867 select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1868 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1869 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1870 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1871 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1872 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1873 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1874 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1875 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1876 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1877 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1878 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1879 INTO l_tot_rec
1880 from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
1881 where b.set_of_books_id = a.set_of_books_id
1882 AND c.cust_account_id = p_cust_account_id
1883 and c.org_id = b.org_id;
1884
1885 SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1886 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1887 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1888 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1889 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1890 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1891 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1892 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1893 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1894 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1895 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1896 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1897 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1898 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1899 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1900 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1901 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1902 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1903 INTO l_tot_sales_in_year
1904 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
1905 WHERE c.org_id = b.org_id
1906 AND c.cust_account_id = p_cust_account_id
1907 AND b.set_of_books_id = a.set_of_books_id
1908 and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
1909 ELSE IF p_customer_site_use_id IS NOT NULL THEN
1910 select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1911 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
1912 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1913 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
1914 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1915 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
1916 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1917 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
1918 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1919 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
1920 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1921 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
1922 INTO l_tot_rec
1923 from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
1924 where b.set_of_books_id = a.set_of_books_id
1925 AND c.site_use_id = p_customer_site_use_id
1926 and c.org_id = b.org_id;
1927
1928 SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1929 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
1930 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1931 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
1932 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1933 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
1934 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1935 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
1936 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1937 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
1938 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1939 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
1940 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1941 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
1942 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1943 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
1944 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
1945 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
1946 INTO l_tot_sales_in_year
1947 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
1948 WHERE c.org_id = b.org_id
1949 AND c.site_use_id = p_customer_site_use_id
1950 AND b.set_of_books_id = a.set_of_books_id
1951 and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
1952 END IF;
1953 END IF;
1954 END IF;
1955
1956
1957 l_num_val := ROUND(l_tot_rec - l_tot_sales_in_year,2);
1958 p_beg_ar := l_num_val;
1959
1960 l_num_val := ROUND(l_tot_rec,2);
1961 p_end_ar := l_num_val;
1962 fnd_file.put_line(FND_FILE.LOG,' comp_rem_ar p_beg_ar '|| p_beg_ar);
1963 fnd_file.put_line(FND_FILE.LOG,' comp_rem_ar p_end_ar '|| p_end_ar);
1964 EXCEPTION
1965 WHEN OTHERS THEN
1966 p_beg_ar := 0;
1967 p_end_ar := 0;
1968 END;
1969
1970
1971
1972 function comp_tot_rec_ocm(p_party_id NUMBER ,p_cust_account_id NUMBER ,p_customer_site_use_id NUMBER ,p_start_date date,p_end_date date) return number is
1973 l_tot_sales number;
1974 l_num_val NUMBER;
1975
1976 begin
1977 select nvl(amount_due_org_ocm(p_party_id,p_cust_account_id,p_customer_site_use_id,p_start_date,p_end_date),0)+
1978 nvl(get_adj_total_ocm(p_party_id,p_cust_account_id,p_customer_site_use_id,p_start_date , p_end_date),0)
1979 into l_tot_sales
1980 from dual;
1981
1982 l_num_val := ROUND(nvl(l_tot_sales,0), 2);
1983 fnd_file.put_line(FND_FILE.LOG,' COMP_TOT_REC_OCM l_tot_sales '|| l_num_val);
1984 RETURN l_num_val;
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 RETURN(0);
1988 END comp_tot_rec_ocm;
1989
1990 /* Invoice True DSO = (Sysdate - Invoice date) * Invoice Amount / Total Sales in Invoice Month
1991
1992 True DSO = Sum of all Invoices True DSO in last year*/
1993
1994
1995 FUNCTION get_true_dso_ocm(p_party_id number,p_cust_account_id number,p_customer_site_use_id number) RETURN VARCHAR2 IS
1996
1997
1998 /* Numerator (Sysdate - Invoice date) * Invoice Amount */
1999 cursor c_invoices_party (p_party_id number) is
2000 select (sysdate-gl_date)* amount_due_original amount, to_char(gl_date,'MM') month,payment_schedule_id
2001 from ar_payment_schedules_all a, hz_cust_accounts b
2002 where a.customer_id = b.cust_account_id
2003 AND b.party_id = p_party_id
2004 AND a.CLASS IN ('INV','DEP','DM','CB')
2005 and a.gl_date between trunc(sysdate-365) AND TRUNC(sysdate);
2006
2007 cursor c_invoices_acct (p_cust_account_id number) is
2008 select (sysdate-gl_date)* amount_due_original amount, to_char(gl_date,'MM') month,payment_schedule_id
2009 from ar_payment_schedules_all
2010 where customer_id = p_cust_account_id
2011 AND CLASS IN ('INV','DEP','DM','CB')
2012 and gl_date between trunc(sysdate-365) AND TRUNC(sysdate);
2013
2014 cursor c_invoices_site (p_customer_site_use_id number) is
2015 select (sysdate-gl_date)* amount_due_original amount, to_char(gl_date,'MM') month,payment_schedule_id
2016 from ar_payment_schedules_all
2017 where customer_site_use_id = p_customer_site_use_id
2018 AND CLASS IN ('INV','DEP','DM','CB')
2019 and gl_date between trunc(sysdate-365) AND TRUNC(sysdate);
2020
2021
2022 /*Denominator Total Sales month wise data */
2023
2024 cursor c_month_sales_party(p_party_id number) is
2025 select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2026 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2027 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2028 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2029 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2030 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2031 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2032 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2033 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2034 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2035 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b , hz_cust_accounts d
2036 WHERE c.cust_account_id = d.cust_account_id
2037 AND d.party_id = p_party_id
2038 and c.org_id = b.org_id
2039 AND b.set_of_books_id = a.set_of_books_id
2040 AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
2041 group by to_char(c.as_of_date,'MM');
2042
2043 cursor c_month_sales_acct(p_cust_account_id number) is
2044 select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2045 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2046 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2047 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2048 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2049 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2050 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2051 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2052 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2053 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2054 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2055 WHERE c.cust_account_id = p_cust_account_id
2056 and c.org_id = b.org_id
2057 AND b.set_of_books_id = a.set_of_books_id
2058 AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
2059 group by to_char(c.as_of_date,'MM');
2060
2061 cursor c_month_sales_site(p_customer_site_use_id number) is
2062 select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2063 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2064 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2065 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2066 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2067 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2068 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2069 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
2070 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2071 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
2072 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2073 WHERE c.site_use_id = p_customer_site_use_id
2074 and c.org_id = b.org_id
2075 AND b.set_of_books_id = a.set_of_books_id
2076 AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
2077 group by to_char(c.as_of_date,'MM');
2078
2079 type l_month_rec is record (l_amount number,l_month number);
2080
2081 type l_month_type is table of l_month_rec
2082 index by binary_integer;
2083
2084 l_month_trx l_month_type;
2085 l_month_amounts l_month_type;
2086
2087 j number;
2088 k number;
2089 l_dso number;
2090 l_invoice_dso number;
2091 l_num_val number;
2092 l_char_val VARCHAR2(100);
2093 begin
2094 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Start');
2095
2096 -- collect month wise total sales in last year
2097 IF p_party_id IS NOT NULL THEN
2098
2099 open c_month_sales_party(p_party_id);
2100 fetch c_month_sales_party bulk collect into l_month_trx;
2101 close c_month_sales_party;
2102
2103 ELSE IF p_cust_account_id IS NOT NULL THEN
2104
2105 open c_month_sales_acct(p_cust_account_id);
2106 fetch c_month_sales_acct bulk collect into l_month_trx;
2107 close c_month_sales_acct;
2108
2109 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2110
2111 open c_month_sales_site(p_customer_site_use_id);
2112 fetch c_month_sales_site bulk collect into l_month_trx;
2113 close c_month_sales_site;
2114
2115 END IF;
2116 END IF;
2117 END IF;
2118
2119 for j in 1..12 loop
2120 l_month_amounts(j).l_amount := 0;
2121 end loop;
2122 -- assign month wise total sales from Jan to dec in l_month_amounts
2123
2124 for j in 1..l_month_trx.count loop
2125 k:= l_month_trx(j).l_month;
2126 l_month_amounts(k).l_amount := l_month_trx(j).l_amount;
2127 end loop;
2128 j:= 1;
2129 -- set sales to zero if no sales in a particular month
2130
2131 for j in 1..12 loop
2132 if l_month_amounts(j).l_amount is null then
2133 l_month_amounts(j).l_amount := 0;
2134 end if;
2135 end loop;
2136
2137 j:= 1;
2138 for j in 1..12 loop
2139 fnd_file.put_line(FND_FILE.LOG,' GET_TRUE_DSO_OCM: Total sales in month '|| j|| ' is ' || l_month_amounts(j).l_amount);
2140 end loop;
2141
2142 l_dso := 0;
2143 -- Loop throgh all open invoices in last year
2144 IF p_party_id IS NOT NULL THEN
2145 for i in c_invoices_party(p_party_id) loop
2146 begin
2147 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Month of the Invoice ' || i.month);
2148 k := i.month;
2149 fnd_file.put_line(FND_FILE.LOG,k|| 'GET_TRUE_DSO_OCM month sales'||l_month_amounts(k).l_amount);
2150 l_invoice_dso := i.amount/l_month_amounts(i.month).l_amount;
2151 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: DSO of the invoice '||i.payment_schedule_id || ' is '|| l_invoice_dso);
2152 -- sum all invoices dso
2153 l_dso := l_dso + l_invoice_dso;
2154
2155 exception
2156 when others then
2157 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Exception in calculating True DSO of invoice '|| i.payment_schedule_id || sqlerrm);
2158 end;
2159 end loop;
2160 ELSE IF p_cust_account_id IS NOT NULL THEN
2161 for i in c_invoices_acct(p_cust_account_id) loop
2162 begin
2163 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Month of the Invoice ' || i.month);
2164 k := i.month;
2165 fnd_file.put_line(FND_FILE.LOG,k|| 'GET_TRUE_DSO_OCM month sales'||l_month_amounts(k).l_amount);
2166 l_invoice_dso := i.amount/l_month_amounts(i.month).l_amount;
2167 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: DSO of the invoice '||i.payment_schedule_id || ' is '|| l_invoice_dso);
2168 -- sum all invoices dso
2169 l_dso := l_dso + l_invoice_dso;
2170
2171 exception
2172 when others then
2173 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Exception in calculating True DSO of invoice '|| i.payment_schedule_id || sqlerrm);
2174 end;
2175 end loop;
2176 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2177 for i in c_invoices_site(p_customer_site_use_id) loop
2178 begin
2179 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Month of the Invoice ' || i.month);
2180 k := i.month;
2181 fnd_file.put_line(FND_FILE.LOG,k|| 'GET_TRUE_DSO_OCM month sales'||l_month_amounts(k).l_amount);
2182 l_invoice_dso := i.amount/l_month_amounts(i.month).l_amount;
2183 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: DSO of the invoice '||i.payment_schedule_id || ' is '|| l_invoice_dso);
2184 -- sum all invoices dso
2185 l_dso := l_dso + l_invoice_dso;
2186
2187 exception
2188 when others then
2189 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Exception in calculating True DSO of invoice '|| i.payment_schedule_id || sqlerrm);
2190 end;
2191 end loop;
2192 END IF;
2193 END IF;
2194
2195 END IF;
2196 l_num_val := ROUND(NVL(l_dso,0),2);
2197 l_char_val := RTRIM(TO_CHAR(l_num_val));
2198 fnd_file.put_line(fnd_file.LOG,'GET_TRUE_DSO_OCM: True DSO '|| l_char_val);
2199 RETURN l_char_val;
2200 exception
2201 when others then
2202 fnd_file.put_line(FND_FILE.LOG,'GET_TRUE_DSO_OCM: Exception in True DSO '|| sqlerrm);
2203 end;
2204
2205
2206 FUNCTION get_conv_dso_ocm(p_party_id number,p_cust_account_id number,p_customer_site_use_id number) RETURN VARCHAR2 IS
2207 l_conv_dso number;
2208 l_op_bal number;
2209 l_tot_bal number;
2210 l_num_val number;
2211 l_char_val VARCHAR2(1000);
2212 l_dso_days number;
2213 BEGIN
2214 --fnd_file.put_line(FND_FILE.LOG,'get_conv_dso_ocm: Start');
2215
2216 IF p_party_id IS NOT NULL THEN
2217 select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2218 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2219 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2220 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2221 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2222 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2223 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2224 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2225 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2226 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2227 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2228 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2229 INTO l_op_bal
2230 from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a,hz_cust_accounts d
2231 where c.cust_account_id = d.cust_account_id
2232 AND d.party_id = p_party_id
2233 AND b.set_of_books_id = a.set_of_books_id
2234 and c.org_id = b.org_id;
2235 ELSE IF p_cust_account_id IS NOT NULL THEN
2236 select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2237 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2238 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2239 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2240 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2241 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2242 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2243 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2244 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2245 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2246 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2247 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2248 INTO l_op_bal
2249 from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
2250 where c.cust_account_id= p_cust_account_id
2251 AND b.set_of_books_id = a.set_of_books_id
2252 and c.org_id = b.org_id;
2253 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2254 select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2255 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
2256 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2257 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
2258 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2259 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
2260 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2261 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
2262 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2263 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
2264 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2265 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
2266 INTO l_op_bal
2267 from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
2268 where c.site_use_id = p_customer_site_use_id
2269 AND b.set_of_books_id = a.set_of_books_id
2270 and c.org_id = b.org_id;
2271 END IF;
2272 END IF;
2273 END IF;
2274
2275 fnd_file.put_line(FND_FILE.LOG, 'GET_CONV_DSO_OCM :l_op_bal '|| l_op_bal);
2276
2277 IF p_party_id IS NOT NULL THEN
2278 SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2279 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2280 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2281 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2282 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2283 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2284 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2285 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2286 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2287 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2288 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2289 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2290 INTO l_tot_bal
2291 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b, hz_cust_accounts d
2292 WHERE c.cust_account_id = d.cust_account_id
2293 AND d.party_id = p_party_id
2294 and c.org_id = b.org_id
2295 AND b.set_of_books_id = a.set_of_books_id
2296 and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2297 ELSE IF p_cust_account_id IS NOT NULL THEN
2298 SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2299 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2300 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2301 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2302 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2303 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2304 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2305 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2306 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2307 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2308 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2309 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2310 INTO l_tot_bal
2311 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2312 WHERE c.cust_account_id = p_cust_account_id
2313 and c.org_id = b.org_id
2314 AND b.set_of_books_id = a.set_of_books_id
2315 and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2316 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2317 SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2318 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
2319 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2320 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
2321 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2322 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
2323 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2324 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
2325 nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2326 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
2327 NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2328 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
2329 INTO l_tot_bal
2330 FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
2331 WHERE c.site_use_id = p_customer_site_use_id
2332 and c.org_id = b.org_id
2333 AND b.set_of_books_id = a.set_of_books_id
2334 and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);
2335 END IF;
2336 END IF;
2337 END IF;
2338
2339
2340 SELECT cer_dso_days INTO l_dso_days FROM ar_system_parameters;
2341
2342 fnd_file.put_line(FND_FILE.LOG,'GET_CONV_DSO_OCM l_tot_bal '||l_tot_bal);
2343 l_conv_dso := l_op_bal/l_tot_bal*l_dso_days;
2344 --fnd_file.put_line(FND_FILE.LOG,'l_conv_dso '||l_conv_dso);
2345 l_num_val := ROUND(nvl(l_conv_dso,0), 2);
2346 l_char_val := RTRIM(TO_CHAR(l_num_val));
2347
2348 -- fnd_file.put_line(FND_FILE.LOG, 'CUST_ACCOUNT_ID ' || p_cust_account_id);
2349 fnd_file.put_line(FND_FILE.LOG,'GET_CONV_DSO_OCM l_conv_dso '|| l_char_val);
2350 RETURN l_char_val;
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353 RETURN(0);
2354 END get_conv_dso_ocm;
2355
2356
2357 FUNCTION GET_CEI_OCM(p_party_id IN NUMBER,
2358 p_cust_account_id IN NUMBER,
2359 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
2360 l_cei NUMBER;
2361 l_sales NUMBER;
2362 l_beg_ar NUMBER;
2363 l_end_ar NUMBER;
2364 l_curr_ar NUMBER;
2365 l_num_val NUMBER;
2366 l_char_val VARCHAR2(1000);
2367
2368 BEGIN
2369 /*-----------------------------------------------------------------------
2370 CEI = ( Beginning Receivables + ( Credit Sales/ N*) - Ending Total Receivables) * 100
2371 / (Beginning Receivables + ( Credit Sales/N*) - Ending Current Receivables)
2372
2373 *N= Number of Months Can do this monthly, quarterly , and annually
2374 */
2375
2376 l_sales := COMP_TOT_REC_ocm(p_party_id, p_cust_account_id, p_customer_site_use_id,TRUNC(add_months(sysdate, -12)), TRUNC(sysdate));
2377
2378 -- l_beg_ar := COMP_REM_REC_ocm(p_party_id, p_cust_account_id, p_customer_site_use_id,to_date('01/01/1952','MM/DD/YYYY'), TRUNC(add_months(sysdate, -12)) - 1);
2379
2380 -- l_end_ar := COMP_REM_REC_ocm(p_party_id, p_cust_account_id, p_customer_site_use_id,to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate));
2381 comp_rem_ar(p_party_id, p_cust_account_id, p_customer_site_use_id,l_beg_ar,l_end_ar);
2382 l_curr_ar := comp_curr_rec(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),p_party_id, p_cust_account_id, p_customer_site_use_id);
2383
2384 fnd_file.put_line(FND_FILE.LOG, 'GET_CEI_OCM l_sales ' || l_sales);
2385 fnd_file.put_line(FND_FILE.LOG, 'GET_CEI_OCM l_beg_ar ' || l_beg_ar);
2386 fnd_file.put_line(FND_FILE.LOG, 'GET_CEI_OCM l_end_ar ' || l_end_ar);
2387 fnd_file.put_line(FND_FILE.LOG, 'GET_CEI_OCM l_curr_ar ' || l_curr_ar);
2388 fnd_file.put_line(FND_FILE.LOG, 'GET_CEI_OCM l_cei ' || l_cei);
2389
2390 l_cei := (l_beg_ar + (l_sales / 12) - l_end_ar) * 100 / (l_beg_ar + (l_sales / 12) - l_curr_ar);
2391
2392 l_num_val := ROUND(TRUNC(NVL(l_cei, 0)));
2393 l_char_val := RTRIM(TO_CHAR(l_num_val));
2394 fnd_file.put_line(FND_FILE.LOG, 'GET_CEI_OCM l_cei ' || l_char_val);
2395 RETURN l_char_val;
2396
2397 EXCEPTION
2398 WHEN NO_DATA_FOUND THEN
2399 RETURN(TO_CHAR(0));
2400 END GET_CEI_OCM;
2401
2402 FUNCTION get_sales_ocm(p_party_id number,
2403 p_cust_account_id number,
2404 p_customer_site_use_id number) RETURN VARCHAR2 IS
2405 l_sales number;
2406 l_num_val number;
2407 l_char_val VARCHAR2(1000);
2408 BEGIN
2409 l_sales := COMP_TOT_REC_ocm(p_party_id, p_cust_account_id, p_customer_site_use_id,TRUNC(add_months(sysdate, -12)), TRUNC(sysdate));
2410
2411 l_num_val := ROUND(NVL(l_sales, 0),2);
2412 l_char_val := RTRIM(TO_CHAR(l_num_val));
2413 fnd_file.put_line(FND_FILE.LOG, 'get_sales_ocm l_sales ' || l_char_val);
2414 RETURN l_char_val;
2415 EXCEPTION
2416 WHEN OTHERS THEN
2417 RETURN(TO_CHAR(0));
2418 END get_sales_ocm;
2419
2420 FUNCTION get_deduction_ocm(p_party_id number,
2421 p_cust_account_id number,
2422 p_customer_site_use_id number) RETURN VARCHAR2 IS
2423 l_adj number;
2424 l_num_val number;
2425 l_char_val VARCHAR2(1000);
2426 BEGIN
2427 l_adj := get_adj_total_ocm(p_party_id,p_cust_account_id,p_customer_site_use_id,TRUNC(add_months(sysdate, -12)),TRUNC(sysdate));
2428 l_num_val := ROUND(NVL(l_adj,0),2);
2429 l_char_val := rtrim(TO_CHAR(l_num_val));
2430 fnd_file.put_line(FND_FILE.LOG, 'get_deduction_ocm l_adj ' || l_char_val);
2431 RETURN l_char_val;
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434 RETURN(TO_CHAR(0));
2435 END;
2436
2437 FUNCTION GET_NSF_STOP_PMT_COUNT_ocm(p_party_id IN NUMBER,
2438 p_cust_account_id IN NUMBER,
2439 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 IS
2440 l_nsf_count number;
2441 l_num_val NUMBER;
2442 l_char_val VARCHAR2(1000);
2443 begin
2444 IF p_party_id IS NOT NULL THEN
2445 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2446 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
2447 INTO l_nsf_count
2448 from gl_sets_of_books a,
2449 ar_system_parameters b,
2450 ar_trx_summary c,
2451 hz_cust_accounts d
2452 where a.set_of_books_id = b.set_of_books_id
2453 and b.org_id = c.org_id
2454 AND c.cust_account_id = d.cust_account_id
2455 AND d.party_id = p_party_id
2456 and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2457 ELSE IF p_cust_account_id IS NOT NULL THEN
2458 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2459 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
2460 INTO l_nsf_count
2461 from gl_sets_of_books a,
2462 ar_system_parameters b,
2463 ar_trx_summary c
2464 where a.set_of_books_id = b.set_of_books_id
2465 and b.org_id = c.org_id
2466 and c.cust_account_id = p_cust_account_id
2467 and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2468 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2469 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2470 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
2471 INTO l_nsf_count
2472 from gl_sets_of_books a,
2473 ar_system_parameters b,
2474 ar_trx_summary c
2475 where a.set_of_books_id = b.set_of_books_id
2476 and b.org_id = c.org_id
2477 AND c.site_use_id = p_customer_site_use_id
2478 and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2479 END IF;
2480 END IF;
2481 END IF;
2482
2483 l_num_val := ROUND(nvl(l_nsf_count,0), 2);
2484 fnd_file.put_line(FND_FILE.LOG,' GET_NSF_STOP_PMT_COUNT l_nsf_count'|| l_num_val);
2485 RETURN l_num_val;
2486 EXCEPTION
2487 WHEN OTHERS THEN
2488 RETURN(0);
2489 end;
2490
2491 FUNCTION GET_NSF_STOP_PMT_AMOUNT_ocm(p_party_id IN NUMBER,
2492 p_cust_account_id IN NUMBER,
2493 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 IS
2494 l_nsf_amount number;
2495 l_num_val NUMBER;
2496 l_char_val VARCHAR2(1000);
2497 begin
2498 IF p_party_id IS NOT NULL THEN
2499 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2500 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
2501 INTO l_nsf_amount
2502 from gl_sets_of_books a,
2503 ar_system_parameters b,
2504 ar_trx_summary c,
2505 hz_cust_accounts d
2506 where a.set_of_books_id = b.set_of_books_id
2507 and b.org_id = c.org_id
2508 AND c.cust_account_id = d.cust_account_id
2509 AND d.party_id = p_party_id
2510 and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2511 ELSE IF p_cust_account_id IS NOT NULL THEN
2512 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2513 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
2514 INTO l_nsf_amount
2515 from gl_sets_of_books a,
2516 ar_system_parameters b,
2517 ar_trx_summary c
2518 where a.set_of_books_id = b.set_of_books_id
2519 and b.org_id = c.org_id
2520 and c.cust_account_id = p_cust_account_id
2521 and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2522 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2523 select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
2524 fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
2525 INTO l_nsf_amount
2526 from gl_sets_of_books a,
2527 ar_system_parameters b,
2528 ar_trx_summary c
2529 where a.set_of_books_id = b.set_of_books_id
2530 and b.org_id = c.org_id
2531 AND c.site_use_id = p_customer_site_use_id
2532 and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2533 END IF;
2534 END IF;
2535 END IF;
2536
2537 l_num_val := ROUND(nvl(l_nsf_amount,0), 2);
2538 fnd_file.put_line(FND_FILE.LOG,' GET_NSF_STOP_PMT_AMOUNT l_nsf_amount'|| l_num_val);
2539 RETURN l_num_val;
2540 EXCEPTION
2541 WHEN OTHERS THEN
2542 RETURN(0);
2543 END GET_NSF_STOP_PMT_AMOUNT_ocm;
2544
2545 function get_wt_avg_days_paid_late(p_party_id NUMBER ,
2546 p_cust_account_id number,
2547 p_customer_site_use_id number) RETURN VARCHAR2 IS
2548
2549 l_wt_days_paid_late number;
2550 l_num_val NUMBER;
2551 l_char_val VARCHAR2(1000);
2552
2553 BEGIN
2554 IF p_party_id IS NOT NULL THEN
2555 SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2556 DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2557 SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2558 INTO l_wt_days_paid_late
2559 FROM gl_sets_of_books a,
2560 ar_system_parameters b ,
2561 ar_trx_summary c ,
2562 hz_cust_accounts d
2563 WHERE a.set_of_books_id = b.set_of_books_id
2564 AND b.org_id = c.org_id
2565 AND c.cust_account_id = d.cust_account_id
2566 AND d.party_id = p_party_id
2567 AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2568 ELSE IF p_cust_account_id IS NOT NULL THEN
2569 SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2570 DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2571 SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2572 INTO l_wt_days_paid_late
2573 FROM gl_sets_of_books a,
2574 ar_system_parameters b ,
2575 ar_trx_summary c
2576 -- , hz_cust_accounts d commented for Bug14167214 by bibeura
2577 WHERE a.set_of_books_id = b.set_of_books_id
2578 AND b.org_id = c.org_id
2579 AND c.cust_account_id = p_cust_account_id
2580 AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2581 ELSE IF p_customer_site_use_id IS NOT NULL THEN
2582 SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
2583 DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
2584 SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
2585 INTO l_wt_days_paid_late
2586 FROM gl_sets_of_books a,
2587 ar_system_parameters b ,
2588 ar_trx_summary c
2589 -- , hz_cust_accounts d commented for Bug14167214 by bibeura
2590 WHERE a.set_of_books_id = b.set_of_books_id
2591 AND b.org_id = c.org_id
2592 AND c.site_use_id = p_customer_site_use_id
2593 AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);
2594 END IF;
2595 END IF;
2596 END IF;
2597
2598 l_num_val := ROUND(nvl(l_wt_days_paid_late,0), 2);
2599 fnd_file.put_line(FND_FILE.LOG,' get_wt_avg_days_paid_late l_wt_days_paid_late '|| l_num_val);
2600 RETURN l_num_val;
2601 EXCEPTION
2602 WHEN OTHERS THEN
2603 RETURN(0);
2604 END;
2605
2606 -- end bug 12546745
2607
2608 BEGIN
2609 --Begin bug#5208170 schekuri 29-May-2006
2610 --Commented out these as the same values are getting populated in IEX_METRIC_PVT.GET_METRIC_INFO
2611 /*SELECT sob.currency_code,
2612 c.precision,
2613 c.minimum_accountable_unit
2614 INTO g_curr_rec.base_currency,
2615 g_curr_rec.base_precision,
2616 g_curr_rec.base_min_acc_unit
2617 FROM ar_system_parameters sysp,
2618 gl_sets_of_books sob,
2619 fnd_currencies c
2620 WHERE sob.set_of_books_id = sysp.set_of_books_id
2621 AND sob.currency_code = c.currency_code;
2622
2623 -- Past Year From and To
2624 SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
2625 TRUNC(sysdate) pastYearTo
2626 INTO g_curr_rec.past_year_from,
2627 g_curr_rec.past_year_to
2628 FROM dual;*/
2629 NULL;
2630 --End bug#5208170 schekuri 29-May-2006
2631 EXCEPTION
2632 WHEN OTHERS THEN
2633 NULL;
2634
2635 END;
[Home] [Help]