1 PACKAGE BODY IEX_COLL_IND AS
2 /* $Header: iexvmtib.pls 120.10.12000000.3 2007/05/31 09:48:02 gnramasa 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 SELECT sum(TRUNC(sysdate) - ps.due_date)
39 / COUNT(1)
40 INTO l_avg_days_late
41 FROM ar_payment_schedules ps, hz_cust_accounts ca
42 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
43 AND ps.class in ('INV','DEP','DM','CB')
44 AND ps.gl_date_closed > TRUNC(sysdate)
45 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
46 AND ps.status = 'OP'
47 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
48 AND ps.due_date < TRUNC(sysdate)
49 AND ps.payment_schedule_id <> -1
50 AND ca.cust_account_id = ps.customer_id
51 AND ca.party_id = p_party_id;
52 ELSIF p_cust_account_id IS NOT NULL THEN
53 SELECT sum(TRUNC(sysdate) - ps.due_date)
54 / COUNT(1)
55 INTO l_avg_days_late
56 FROM ar_payment_schedules ps
57 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
58 AND ps.class in ('INV','DEP','DM','CB')
59 AND ps.gl_date_closed > TRUNC(sysdate)
60 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
61 AND ps.status = 'OP'
62 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
63 AND ps.due_date < TRUNC(sysdate)
64 AND ps.payment_schedule_id <> -1
65 AND ps.customer_id = p_cust_account_id;
66 ELSIF p_customer_site_use_id IS NOT NULL THEN
67 SELECT sum(TRUNC(sysdate) - ps.due_date)
68 / COUNT(1)
69 INTO l_avg_days_late
70 FROM ar_payment_schedules ps
71 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
72 AND ps.class in ('INV','DEP','DM','CB')
73 AND ps.gl_date_closed > TRUNC(sysdate)
74 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
75 AND ps.status = 'OP'
76 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
77 AND ps.due_date < TRUNC(sysdate)
78 AND ps.payment_schedule_id <> -1
79 AND ps.customer_site_use_id = p_customer_site_use_id;
80 END IF;
81
82 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
83 -- RETURN TO_CHAR(TRUNC(NVL(l_avg_days_late,0)));
84
85 l_num_val := TRUNC(NVL(l_avg_days_late,0));
86 l_char_val := RTRIM(TO_CHAR(l_num_val));
87
88 RETURN l_char_val;
89 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
90
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
93
94 END GET_AVG_DAYS_LATE;
95
96
97 /*-------------------------------------------------------------------------+
98 | PUBLIC FUNCTION |
99 | |
100 | GET_WTD_DAYS_LATE |
101 | |
102 | DESCRIPTION |
103 | This function will compute for weighted average |
104 | days late |
105 | Added calls to GET_ADJ_TOTAL and GET_APPS_TOTAL |
106 | REQUIRES |
107 | |
108 | OPTIONAL |
109 | |
110 | RETURNS |
111 | Weighted Average Days Late |
112 | |
113 | NOTES |
114 | |
115 | EXAMPLE |
116 | |
117 | MODIFICATION HISTORY |
118 | |
119 +-------------------------------------------------------------------------*/
120
121 FUNCTION GET_WTD_DAYS_LATE(p_party_id IN NUMBER,
122 p_cust_account_id IN NUMBER,
123 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
124
125 l_wtd_days_late NUMBER;
126 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
127 l_num_val NUMBER;
128 l_char_val VARCHAR2(1000);
129 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
130
131 BEGIN
132
133 IF p_party_id IS NOT NULL THEN
134 SELECT sum
135 (
136 (
137 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
138 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
139 nvl(ps.acctd_amount_due_remaining, 0)
140 ) *
141 (TRUNC(sysdate) - ps.due_date)
142 ) /
143 sum (
144 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
145 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
146 nvl(ps.acctd_amount_due_remaining, 0)
147 )
148 INTO l_wtd_days_late
149 FROM ar_payment_schedules ps, hz_cust_accounts ca
150 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
151 AND ps.class in ('INV','DEP','DM','CB')
152 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
153 --AND ps.gl_date_closed > TRUNC(sysdate)
154 --AND ps.due_date < TRUNC(sysdate)
155 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
156 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
157 AND ps.status = 'OP'
158 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
159 AND ps.payment_schedule_id <> -1
160 AND ps.customer_id = ca.cust_account_id
161 AND ca.party_id = p_party_id;
162 ELSIF p_cust_account_id IS NOT NULL THEN
163 SELECT sum
164 (
165 (
166 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
167 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
168 nvl(ps.acctd_amount_due_remaining, 0)
169 ) *
170 (TRUNC(sysdate) - ps.due_date)
171 ) /
172 sum (
173 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
174 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
175 nvl(ps.acctd_amount_due_remaining, 0)
176 )
177 INTO l_wtd_days_late
178 FROM ar_payment_schedules ps
179 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
180 AND ps.class in ('INV','DEP','DM','CB')
181 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
182 -- AND ps.gl_date_closed > TRUNC(sysdate)
183 -- AND ps.due_date < TRUNC(sysdate)
184 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
185 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
186 AND ps.status = 'OP'
187 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
188 AND ps.payment_schedule_id <> -1
189 AND ps.customer_id = p_cust_account_id;
190 ELSIF p_customer_site_use_id IS NOT NULL THEN
191 SELECT sum
192 (
193 (
194 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
195 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
196 nvl(ps.acctd_amount_due_remaining, 0)
197 ) *
198 (TRUNC(sysdate) - ps.due_date)
199 ) /
200 sum (
201 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
202 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
203 nvl(ps.acctd_amount_due_remaining, 0)
204 )
205 INTO l_wtd_days_late
206 FROM ar_payment_schedules ps
207 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
208 AND ps.class in ('INV','DEP','DM','CB')
209 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
210 -- AND ps.gl_date_closed > TRUNC(sysdate)
211 -- AND ps.due_date < TRUNC(sysdate)
212 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
213 -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
214 AND ps.status = 'OP'
215 -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
216 AND ps.payment_schedule_id <> -1
217 AND ps.customer_site_use_id = p_customer_site_use_id;
218 END IF;
219
220 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
221 --RETURN TO_CHAR(TRUNC(NVL(l_wtd_days_late,0)));
222 l_num_val := TRUNC(NVL(l_wtd_days_late,0));
223 l_char_val := RTRIM(TO_CHAR(l_num_val));
224 RETURN l_char_val;
225 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
229 END GET_WTD_DAYS_LATE;
230
231 /*-------------------------------------------------------------------------+
232 | PUBLIC FUNCTION |
233 | |
234 | GET_WTD_DAYS_PAID |
235 | |
236 | DESCRIPTION |
237 | This function will compute for weighted average |
238 | days paid |
239 | REQUIRES |
240 | |
241 | OPTIONAL |
242 | |
243 | RETURNS |
244 | Weighted Average Days Paid |
245 | |
246 | NOTES |
247 | |
248 | EXAMPLE |
249 | |
250 | MODIFICATION HISTORY |
251 | |
252 +-------------------------------------------------------------------------*/
253
254 FUNCTION GET_WTD_DAYS_PAID(p_party_id IN NUMBER,
255 p_cust_account_id IN NUMBER,
256 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
257
258 l_wtd_days_paid NUMBER;
259 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
260 l_num_val NUMBER;
261 l_char_val VARCHAR2(1000);
262 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
263
264 BEGIN
265
266 IF p_party_id IS NOT NULL THEN
267 SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
268 / SUM(ra.amount_applied)
272 ar_payment_schedules ps,
269 , 0) WEIGHTED_AVG_DAYS_PAID
270 INTO l_wtd_days_paid
271 FROM ar_receivable_applications ra,
273 hz_cust_accounts ca
274 WHERE ps.customer_id = ca.cust_account_id
275 AND ca.party_id = p_party_id
276 AND ra.status = 'APP'
277 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
278 AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
279 AND ps.class in ('INV','DEP','DM','CB')
280 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
281 -- AND ps.gl_date_closed > TRUNC(sysdate)
282 -- AND ps.due_date < TRUNC(sysdate)
283 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
284 -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
285 AND ps.status = 'CL'
286 -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
287 AND ps.payment_schedule_id <> -1;
288 ELSIF p_cust_account_id IS NOT NULL THEN
289 SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
290 / SUM(ra.amount_applied)
291 , 0) WEIGHTED_AVG_DAYS_PAID
292 INTO l_wtd_days_paid
293 FROM ar_receivable_applications ra,
294 ar_payment_schedules ps
295 WHERE ps.customer_id = p_cust_account_id
296 AND ra.status = 'APP'
297 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
298 AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
299 AND ps.class in ('INV','DEP','DM','CB')
300 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
301 -- AND ps.gl_date_closed > TRUNC(sysdate)
302 -- AND ps.due_date < TRUNC(sysdate)
303 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
304 -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
305 AND ps.status = 'CL'
306 -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
307 AND ps.payment_schedule_id <> -1;
308 ELSIF p_customer_site_use_id IS NOT NULL THEN
309 SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
310 / SUM(ra.amount_applied)
311 , 0) WEIGHTED_AVG_DAYS_PAID
312 INTO l_wtd_days_paid
313 FROM ar_receivable_applications ra,
314 ar_payment_schedules ps
315 WHERE ps.customer_site_use_id = p_customer_site_use_id
316 AND ra.status = 'APP'
317 AND ps.payment_schedule_id = ra.applied_payment_schedule_id
318 AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
319 AND ps.class in ('INV','DEP','DM','CB')
320 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
321 -- AND ps.gl_date_closed > TRUNC(sysdate)
322 -- AND ps.due_date < TRUNC(sysdate)
323 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
324 -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
325 AND ps.status = 'CL'
326 -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
327 AND ps.payment_schedule_id <> -1;
328 END IF;
329
330 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
331 --RETURN TO_CHAR(TRUNC(NVL(l_wtd_days_paid, 0)));
332 l_num_val := TRUNC(NVL(l_wtd_days_paid, 0));
333 l_char_val := RTRIM(TO_CHAR(l_num_val));
334 RETURN l_char_val;
335 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
339 END GET_WTD_DAYS_PAID;
340
341
342 /*-------------------------------------------------------------------------+
343 | PUBLIC FUNCTION |
344 | |
345 | GET_WTD_DAYS_TERMS |
346 | |
347 | DESCRIPTION |
348 | This function will compute for weighted average |
349 | days terms |
350 | REQUIRES |
351 | |
352 | OPTIONAL |
353 | |
354 | RETURNS |
355 | Weighted Average Days Terms |
356 | |
357 | NOTES |
358 | |
359 | EXAMPLE |
360 | |
361 | MODIFICATION HISTORY |
362 | |
363 +-------------------------------------------------------------------------*/
364
365 FUNCTION GET_WTD_DAYS_TERMS(p_party_id IN NUMBER,
366 p_cust_account_id IN NUMBER,
370 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
367 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
368
369 l_wtd_days_terms NUMBER;
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 sum
379 (
380 (
381 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
382 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
383 nvl(ps.acctd_amount_due_remaining, 0)
384 ) *
385 (ps.due_date - ps.trx_date)
386 ) /
387 sum (
388 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
389 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
390 nvl(ps.acctd_amount_due_remaining, 0)
391 )
392 INTO l_wtd_days_terms
393 FROM ar_payment_schedules ps, hz_cust_accounts ca
394 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
395 AND ps.class in ('INV','DEP','DM','CB')
396 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
397 -- AND ps.gl_date_closed > TRUNC(sysdate)
398 -- AND ps.due_date < TRUNC(sysdate)
399 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
400 AND ps.payment_schedule_id <> -1
401 AND ps.customer_id = ca.cust_account_id
402 AND ca.party_id = p_party_id;
403 ELSIF p_cust_account_id IS NOT NULL THEN
404 SELECT sum
405 (
406 (
407 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
408 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
409 nvl(ps.acctd_amount_due_remaining, 0)
410 ) *
411 (ps.due_date - ps.trx_date)
412 ) /
413 sum (
414 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
415 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
416 nvl(ps.acctd_amount_due_remaining, 0)
417 )
418 INTO l_wtd_days_terms
419 FROM ar_payment_schedules ps
420 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
421 AND ps.class in ('INV','DEP','DM','CB')
422 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
423 -- AND ps.gl_date_closed > TRUNC(sysdate)
424 -- AND ps.due_date < TRUNC(sysdate)
425 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
426 AND ps.payment_schedule_id <> -1
427 AND ps.customer_id = p_cust_account_id;
428 ELSIF p_customer_site_use_id IS NOT NULL THEN
429 SELECT sum
430 (
431 (
432 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
433 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
434 nvl(ps.acctd_amount_due_remaining, 0)
435 ) *
436 (ps.due_date - ps.trx_date)
437 ) /
438 sum (
439 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
440 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
441 nvl(ps.acctd_amount_due_remaining, 0)
442 )
443 INTO l_wtd_days_terms
444 FROM ar_payment_schedules ps
445 WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
446 AND ps.class in ('INV','DEP','DM','CB')
447 -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
448 -- AND ps.gl_date_closed > TRUNC(sysdate)
449 -- AND ps.due_date < TRUNC(sysdate)
450 -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
451 AND ps.payment_schedule_id <> -1
452 AND ps.customer_site_use_id = p_customer_site_use_id;
453 END IF;
454
455 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
456 --RETURN TO_CHAR(TRUNC(NVL(l_wtd_days_terms, 0)));
457 l_num_val := TRUNC(NVL(l_wtd_days_terms, 0));
458 l_char_val := RTRIM(TO_CHAR(l_num_val));
459 RETURN l_char_val;
460 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
461
462 EXCEPTION
463 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
464 END GET_WTD_DAYS_TERMS;
465
466 FUNCTION GET_CEI(p_party_id IN NUMBER,
467 p_cust_account_id IN NUMBER,
468 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
469 l_cei NUMBER;
470 l_sales NUMBER;
471 l_beg_ar NUMBER;
472 l_end_ar NUMBER;
473 l_curr_ar NUMBER;
474 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
475 l_num_val NUMBER;
476 l_char_val VARCHAR2(1000);
477 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
478
479 BEGIN
480 /*-----------------------------------------------------------------------
481 CEI = ( Beginning Receivables + ( Credit Sales/ N*) - Ending Total Receivables) * 100
482 / (Beginning Receivables + ( Credit Sales/N*) - Ending Current Receivables)
483
484 *N= Number of Months Can do this monthly, quarterly , and annually
485 */
486
490 l_beg_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(add_months(sysdate, -12)) - 1,
487 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
488 p_party_id, p_cust_account_id, p_customer_site_use_id);
489
491 p_party_id, p_cust_account_id, p_customer_site_use_id);
492
493 l_end_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
494 p_party_id, p_cust_account_id, p_customer_site_use_id);
495
496 l_curr_ar := comp_curr_rec(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
497 p_party_id, p_cust_account_id, p_customer_site_use_id);
498
499 l_cei := (l_beg_ar + (l_sales / 12) - l_end_ar) * 100 / (l_beg_ar + (l_sales / 12) - l_curr_ar);
500
501 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
502 --RETURN TO_CHAR(TRUNC(NVL(l_cei, 0)));
503 l_num_val := TRUNC(NVL(l_cei, 0));
504 l_char_val := RTRIM(TO_CHAR(l_num_val));
505 RETURN l_char_val;
506 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
507
508 EXCEPTION
509 WHEN NO_DATA_FOUND THEN
510 RETURN(TO_CHAR(0));
511 END GET_CEI;
512
513 FUNCTION GET_TRUE_DSO(p_party_id IN NUMBER,
514 p_cust_account_id IN NUMBER,
515 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
516 l_sales NUMBER;
517 l_beg_ar NUMBER;
518 l_end_ar NUMBER;
519 l_dso NUMBER;
520 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
521 l_num_val NUMBER;
522 l_char_val VARCHAR2(1000);
523 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
524
525 BEGIN
526
527 /*-----------------------------------------------------------------------
528 DSO = ( Period Average Receivables / Average Sales per day)
529
530 where tot outs rec = sum of all receivables less all receipts (use COMP_REM_REC)
531 avg sales per day = sum of all receivables (use COMP_TOT_REC) / days in period
532 -----------------------------------------------------------------------*/
533
534 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
535 p_party_id, p_cust_account_id, p_customer_site_use_id);
536
537 l_beg_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(add_months(sysdate, -12)) - 1,
538 p_party_id, p_cust_account_id, p_customer_site_use_id);
539
540 l_end_ar := COMP_REM_REC(to_date('01/01/1952','MM/DD/YYYY'), TRUNC(sysdate),
541 p_party_id, p_cust_account_id, p_customer_site_use_id);
542
543 if ( nvl(l_sales,0) = 0 ) then
544 l_dso := 0;
545 else
546 l_dso := (((l_beg_ar + l_end_ar)/2)/l_sales)*(TRUNC(sysdate) - TRUNC(add_months(sysdate, -12)));
547 end if;
548
549 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
550 --RETURN TO_CHAR(ROUND(nvl(l_dso,0), 0));
551 l_num_val := ROUND(nvl(l_dso,0), 0);
552 l_char_val := RTRIM(TO_CHAR(l_num_val));
553 RETURN l_char_val;
554 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
555
556 END GET_TRUE_DSO;
557
558 FUNCTION GET_CONV_DSO(p_party_id IN NUMBER,
559 p_cust_account_id IN NUMBER,
560 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
561 l_conv_dso NUMBER;
562 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
563 l_num_val NUMBER;
564 l_char_val VARCHAR2(1000);
565 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
566
567 BEGIN
568 IF p_party_id IS NOT NULL THEN
569 SELECT
570 ROUND(
571 ( (SUM( DECODE(PS.CLASS,
572 'INV', 1,
573 'DM', 1,
574 'CB', 1,
575 'DEP', 1,
576 'BR', 1,
577 0)
578 * PS.ACCTD_AMOUNT_DUE_REMAINING
579 ) * MAX(SP.CER_DSO_DAYS)
580 )
581 / DECODE(
582 SUM( DECODE(PS.CLASS,
583 'INV', 1,
584 'DM', 1,
585 'DEP', 1,
586 0)
587 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
588 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
589 0)) ,
590 0, 1,
591 SUM( DECODE(PS.CLASS,
592 'INV', 1,
593 'DM', 1,
594 'DEP', 1,
595 0)
596 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
597 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
598 0) )
599 )
600 ), 0) /* DSO */
601 INTO l_conv_dso
602 FROM ar_system_parameters sp,
606 AND cust_acct.party_id = p_party_id
603 hz_cust_accounts cust_acct,
604 ar_payment_schedules ps
605 WHERE ps.customer_id = cust_acct.cust_account_id
607 -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
608 AND ps.status = 'OP'
609 -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
610 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
611 ELSIF p_cust_account_id IS NOT NULL THEN
612 SELECT
613 ROUND(
614 ( (SUM( DECODE(PS.CLASS,
615 'INV', 1,
616 'DM', 1,
617 'CB', 1,
618 'DEP', 1,
619 'BR', 1,
620 0)
621 * PS.ACCTD_AMOUNT_DUE_REMAINING
622 ) * MAX(SP.CER_DSO_DAYS)
623 )
624 / DECODE(
625 SUM( DECODE(PS.CLASS,
626 'INV', 1,
627 'DM', 1,
628 'DEP', 1,
629 0)
630 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
631 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
632 0)) ,
633 0, 1,
634 SUM( DECODE(PS.CLASS,
635 'INV', 1,
636 'DM', 1,
637 'DEP', 1,
638 0)
639 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
640 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
641 0) )
642 )
643 ), 0) /* DSO */
644 INTO l_conv_dso
645 FROM ar_system_parameters sp,
646 ar_payment_schedules ps
647 WHERE ps.customer_id = p_cust_account_id
648 -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
649 AND ps.status = 'OP'
650 -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
651 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
652
653 ELSIF p_customer_site_use_id IS NOT NULL THEN
654 SELECT
655 ROUND(
656 ( (SUM( DECODE(PS.CLASS,
657 'INV', 1,
658 'DM', 1,
659 'CB', 1,
660 'DEP', 1,
661 'BR', 1,
662 0)
663 * PS.ACCTD_AMOUNT_DUE_REMAINING
664 ) * MAX(SP.CER_DSO_DAYS)
665 )
666 / DECODE(
667 SUM( DECODE(PS.CLASS,
668 'INV', 1,
669 'DM', 1,
670 'DEP', 1,
671 0)
672 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
673 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
674 0)) ,
675 0, 1,
676 SUM( DECODE(PS.CLASS,
677 'INV', 1,
678 'DM', 1,
679 'DEP', 1,
680 0)
681 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
682 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
683 0) )
684 )
685 ), 0) /* DSO */
686 INTO l_conv_dso
687 FROM ar_system_parameters sp,
688 ar_payment_schedules ps
689 WHERE ps.customer_site_use_id = p_customer_site_use_id
690 -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
691 AND ps.status = 'OP'
692 -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
693 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
694
695 END IF;
696
697 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
698 --RETURN TO_CHAR(ROUND(NVL(l_conv_dso, 0)));
699 l_num_val := ROUND(NVL(l_conv_dso, 0));
700 l_char_val := RTRIM(TO_CHAR(l_num_val));
701 RETURN l_char_val;
702 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
703
704
705 EXCEPTION
706 WHEN NO_DATA_FOUND THEN RETURN(TO_CHAR(0));
707 END GET_CONV_DSO;
708
709 FUNCTION GET_NSF_STOP_PMT_COUNT(p_party_id IN NUMBER,
710 p_cust_account_id IN NUMBER,
711 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
712 l_nsf_stop_payment_count NUMBER;
713 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
714 l_num_val NUMBER;
715 l_char_val VARCHAR2(1000);
716 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
717
721 SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
718 BEGIN
719
720 IF p_party_id IS NOT NULL THEN
722 INTO l_nsf_stop_payment_count
723 FROM ar_cash_receipts cr,
724 ar_cash_receipt_history crh,
725 hz_cust_accounts ca
726 WHERE cr.cash_receipt_id = crh.cash_receipt_id
727 AND crh.current_record_flag = 'Y'
728 AND crh.status = 'REVERSED'
729 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
730 -- AND cr.status = 'REV'
731 -- AND cr.status = 'NSF' -- bug 5613019
732 AND cr.status in ('NSF','REV') -- bug 5613019
733 -- END fix bug #4483830--20050714-jypark-change query for NSF info
734 --AND cr.reversal_category = 'NSF' -- big 5613019
735 AND cr.reversal_category in ('NSF','REV') -- bug 5613019
736 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
737 AND cr.pay_from_customer = ca.cust_account_id
738 AND ca.party_id = p_party_id;
739 ELSIF p_cust_account_id IS NOT NULL THEN
740 SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
741 INTO l_nsf_stop_payment_count
742 FROM ar_cash_receipts cr,
743 ar_cash_receipt_history crh
744 WHERE cr.cash_receipt_id = crh.cash_receipt_id
745 AND crh.current_record_flag = 'Y'
746 AND crh.status = 'REVERSED'
747 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
748 -- AND cr.status = 'REV'
749 -- AND cr.status = 'NSF' --bug 5613019
750 AND cr.status in ('NSF','REV') -- bug 5613019
751 -- END fix bug #4483830--20050714-jypark-change query for NSF info
752 --AND cr.reversal_category = 'NSF' -- bug 5613019
753 and cr.reversal_category in ('NSF','REV') -- bug 5613019
754 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
755 AND cr.pay_from_customer = p_cust_account_id;
756 ELSIF p_customer_site_use_id IS NOT NULL THEN
757 SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
758 INTO l_nsf_stop_payment_count
759 FROM ar_cash_receipts cr,
760 ar_cash_receipt_history crh
761 WHERE cr.cash_receipt_id = crh.cash_receipt_id
762 AND crh.current_record_flag = 'Y'
763 AND crh.status = 'REVERSED'
764 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
765 -- AND cr.status = 'REV'
766 -- AND cr.status = 'NSF' --bug 5613019
767 and cr.status in ('NSF','REV') -- bug 5613019
768 -- END fix bug #4483830--20050714-jypark-change query for NSF info
769 -- AND cr.reversal_category = 'NSF' -- bug 5613019
770 and cr.reversal_category in ('NSF','REV') -- bug 5613019
771 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
772 AND cr.customer_site_use_id = p_customer_site_use_id;
773 END IF;
774
775 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
776 --RETURN TO_CHAR(TRUNC(nvl(l_nsf_stop_payment_count, 0)));
777 l_num_val := TRUNC(nvl(l_nsf_stop_payment_count, 0));
778 l_char_val := RTRIM(TO_CHAR(l_num_val));
779 RETURN l_char_val;
780 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
781
782 EXCEPTION
783 WHEN NO_DATA_FOUND THEN RETURN(0);
784 END GET_NSF_STOP_PMT_COUNT;
785
786 FUNCTION GET_NSF_STOP_PMT_AMOUNT(p_party_id IN NUMBER,
787 p_cust_account_id IN NUMBER,
788 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
789 l_nsf_stop_payment_amount NUMBER;
790 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
791 l_num_val NUMBER;
792 l_char_val VARCHAR2(1000);
793 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
794
795 BEGIN
796
797 IF p_party_id IS NOT NULL THEN
798 SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
799 INTO l_nsf_stop_payment_amount
800 FROM ar_cash_receipts_all cr,
801 ar_cash_receipt_history_all crh,
802 hz_cust_accounts ca
803 WHERE cr.cash_receipt_id = crh.cash_receipt_id
804 AND crh.current_record_flag = 'Y'
805 AND crh.status = 'REVERSED'
806 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
807 -- AND cr.status = 'REV'
808 -- AND cr.status = 'NSF' -- bug 5613019
809 and cr.status in ('NSF','REV') -- bug 5613019
810 -- END fix bug #4483830--20050714-jypark-change query for NSF info
811 -- AND cr.reversal_category = 'NSF' --bug 5613019
812 and cr.reversal_category in ('NSF','REV') -- bug 5613019
813 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
814 AND cr.pay_from_customer = ca.cust_account_id
815 AND ca.party_id = p_party_id;
816 ELSIF p_cust_account_id IS NOT NULL THEN
817 SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
818 INTO l_nsf_stop_payment_amount
819 FROM ar_cash_receipts_all cr,
820 ar_cash_receipt_history_all crh
821 WHERE cr.cash_receipt_id = crh.cash_receipt_id
822 AND crh.current_record_flag = 'Y'
823 AND crh.status = 'REVERSED'
824 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
825 -- AND cr.status = 'REV'
826 -- AND cr.status = 'NSF' -- bug 5613019
827 and cr.status in ('NSF','REV') -- bug 5613019
828 -- END fix bug #4483830--20050714-jypark-change query for NSF info
829 -- AND cr.reversal_category = 'NSF' --bug 5613019
830 and cr.reversal_category in ('NSF','REV') -- bug 5613019
834 SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
831 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
832 AND cr.pay_from_customer = p_cust_account_id;
833 ELSIF p_customer_site_use_id IS NOT NULL THEN
835 INTO l_nsf_stop_payment_amount
836 FROM ar_cash_receipts_all cr,
837 ar_cash_receipt_history_all crh
838 WHERE cr.cash_receipt_id = crh.cash_receipt_id
839 AND crh.current_record_flag = 'Y'
840 AND crh.status = 'REVERSED'
841 -- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
842 -- AND cr.status = 'REV'
843 -- AND cr.status = 'NSF' -- bug5613019
844 and cr.status in ('NSF','REV') -- bug 5613019
845 -- END fix bug #4483830--20050714-jypark-change query for NSF info
846 -- AND cr.reversal_category = 'NSF' -- bug 5613019
847 and cr.reversal_category in ('NSF','REV') -- bug 5613019
848 AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
849 AND cr.customer_site_use_id = p_customer_site_use_id;
850 END IF;
851
852 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
853 --RETURN TO_CHAR(nvl(l_nsf_stop_payment_amount, 0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 30));
854 l_num_val := nvl(l_nsf_stop_payment_amount, 0);
855 l_char_val := RTRIM(TO_CHAR(l_num_val,fnd_currency.get_format_mask(g_curr_rec.base_currency, 30)));
856 RETURN l_char_val;
857 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
858
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN RETURN(0);
861 END GET_NSF_STOP_PMT_AMOUNT;
862
863 FUNCTION GET_SALES(p_party_id IN NUMBER,
864 p_cust_account_id IN NUMBER,
865 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
866 l_sales NUMBER;
867 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
868 l_num_val NUMBER;
869 l_char_val VARCHAR2(1000);
870 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
871
872 BEGIN
873
874 l_sales := COMP_TOT_REC(TRUNC(add_months(sysdate, -12)), TRUNC(sysdate),
875 p_party_id, p_cust_account_id, p_customer_site_use_id);
876
877 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
878 --RETURN(TO_CHAR(NVL(l_sales, 0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
879 l_num_val := NVL(l_sales, 0);
880 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
881 RETURN l_char_val;
882 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
883
884 END GET_SALES;
885
886
887 FUNCTION GET_DEDUCTION(p_party_id IN NUMBER,
888 p_cust_account_id IN NUMBER,
889 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
890 l_adj NUMBER;
891 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
892 l_num_val NUMBER;
893 l_char_val VARCHAR2(1000);
894 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
895
896 BEGIN
897 IF p_party_id IS NOT NULL THEN
898 SELECT
899 sum( nvl(adj.acctd_amount,0))
900 INTO l_adj
901 FROM ar_payment_schedules ps, ar_adjustments adj, hz_cust_accounts ca
902 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
903 AND ps.payment_schedule_id <> -1
904 AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
905 AND ps.customer_id = ca.cust_account_id
906 AND ca.party_id = p_party_id
907 AND adj.payment_schedule_id = ps.payment_schedule_id
908 AND adj.status = 'A'
909 AND adj.gl_date <= TRUNC(sysdate);
910 ELSIF p_cust_account_id IS NOT NULL THEN
911 SELECT
912 sum( nvl(adj.acctd_amount,0))
913 INTO l_adj
914 FROM ar_payment_schedules ps, ar_adjustments adj
915 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
916 AND ps.payment_schedule_id <> -1
917 AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
918 AND ps.customer_id = p_cust_account_id
919 AND adj.payment_schedule_id = ps.payment_schedule_id
920 AND adj.status = 'A'
921 AND adj.gl_date <= TRUNC(sysdate);
922 ELSIF p_customer_site_use_id IS NOT NULL THEN
923 SELECT
924 sum( nvl(adj.acctd_amount,0))
925 INTO l_adj
926 FROM ar_payment_schedules ps, ar_adjustments adj
927 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
928 AND ps.payment_schedule_id <> -1
929 AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
930 AND ps.customer_site_use_id = p_customer_site_use_id
931 AND adj.payment_schedule_id = ps.payment_schedule_id
932 AND adj.status = 'A'
933 AND adj.gl_date <= TRUNC(sysdate);
934 END IF;
935
936 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
937 --RETURN(TO_CHAR(nvl(l_adj, 0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
938 l_num_val := nvl(l_adj, 0);
939 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
940 RETURN l_char_val;
941 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
942
946
943 EXCEPTION
944 WHEN NO_DATA_FOUND THEN RETURN(0);
945 END GET_DEDUCTION;
947 /*-------------------------------------------------------------------------+
948 | PUBLIC FUNCTION |
949 | |
950 | COMP_TOT_REC |
951 | |
952 | DESCRIPTION |
953 | Given a date range, this function will compute the total original |
954 | receivables within the date range |
955 | If function is called with a null start date, then the function |
956 | RETURNs total original receivables as of p_end_date |
957 | |
958 | REQUIRES |
959 | start_date |
960 | end_date |
961 | |
962 | OPTIONAL |
963 | |
964 | RETURNS |
965 | total original receivables |
966 | |
967 | NOTES |
968 | |
969 | EXAMPLE |
970 | |
971 | MODIFICATION HISTORY |
972 | |
973 | |
974 +-------------------------------------------------------------------------*/
975
976 FUNCTION COMP_TOT_REC(p_start_date IN DATE,
977 p_end_date IN DATE,
978 p_party_id IN NUMBER,
979 p_cust_account_id IN NUMBER,
980 p_customer_site_use_id IN NUMBER) RETURN NUMBER AS
981 l_tot_rec NUMBER;
982 l_temp_start DATE;
983
984 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
985 l_num_val NUMBER;
986 l_char_val VARCHAR2(1000);
987 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
988
989 BEGIN
990
991 if p_start_date is null then
992 -- default date to earliest date to pick up everything prior to
993 -- p_end_date
994 l_temp_start := to_date('01/01/1952','MM/DD/YYYY');
995 else
996 l_temp_start := p_start_date;
997 end if;
998
999 IF p_party_id IS NOT NULL THEN
1000 SELECT SUM(arpcurr.functional_amount(
1001 ps.amount_due_original,
1002 g_curr_rec.base_currency,
1003 nvl(ps.exchange_rate,1),
1004 g_curr_rec.base_precision,
1005 g_curr_rec.base_min_acc_unit) +
1006 GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
1007 INTO l_tot_rec
1008 FROM ar_payment_schedules ps,
1009 hz_cust_accounts ca
1010 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1011 AND ps.payment_schedule_id <> -1
1012 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
1013 AND ps.customer_id = ca.cust_account_id
1014 AND ca.party_id = p_party_id;
1015 ELSIF p_cust_account_id IS NOT NULL THEN
1016 SELECT SUM(arpcurr.functional_amount(
1017 ps.amount_due_original,
1018 g_curr_rec.base_currency,
1019 nvl(ps.exchange_rate,1),
1020 g_curr_rec.base_precision,
1021 g_curr_rec.base_min_acc_unit) +
1022 GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
1023 INTO l_tot_rec
1024 FROM ar_payment_schedules ps
1025 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1026 AND ps.payment_schedule_id <> -1
1027 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
1028 AND ps.customer_id = p_cust_account_id;
1029 ELSIF p_customer_site_use_id IS NOT NULL THEN
1030 SELECT SUM(arpcurr.functional_amount(
1031 ps.amount_due_original,
1032 g_curr_rec.base_currency,
1033 nvl(ps.exchange_rate,1),
1034 g_curr_rec.base_precision,
1035 g_curr_rec.base_min_acc_unit) +
1036 GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
1037 INTO l_tot_rec
1038 FROM ar_payment_schedules ps
1039 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
1040 AND ps.payment_schedule_id <> -1
1041 AND ps.gl_date BETWEEN l_temp_start AND p_end_date
1042 AND ps.customer_site_use_id = p_customer_site_use_id;
1043 END IF;
1044
1045 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1046 --RETURN(nvl(l_tot_rec,0));
1047 l_num_val := nvl(l_tot_rec, 0);
1048 RETURN l_num_val;
1049 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1050
1051 EXCEPTION
1055
1052 WHEN NO_DATA_FOUND THEN RETURN(0);
1053 END COMP_TOT_REC;
1054
1056 /*-------------------------------------------------------------------------+
1057 | PUBLIC FUNCTION |
1058 | |
1059 | COMP_REM_REC |
1060 | |
1061 | DESCRIPTION |
1062 | Given a date range, this function will compute the total remaining |
1063 | receivables within the date range |
1064 | |
1065 | REQUIRES |
1066 | start_date |
1067 | end_date |
1068 | |
1069 | OPTIONAL |
1070 | |
1071 | RETURNS |
1072 | total remaining receivables |
1073 | |
1074 | NOTES |
1075 | |
1076 | EXAMPLE |
1077 | |
1078 | MODIFICATION HISTORY |
1079 | there is no record found |
1080 +-------------------------------------------------------------------------*/
1081
1082 FUNCTION COMP_REM_REC(p_start_date IN DATE,
1083 p_end_date IN DATE,
1084 p_party_id IN NUMBER,
1085 p_cust_account_id IN NUMBER,
1086 p_customer_site_use_id IN NUMBER) RETURN NUMBER IS
1087
1088 l_rem_sales NUMBER;
1089 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1090 l_num_val NUMBER;
1091 l_char_val VARCHAR2(1000);
1092 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1093
1094 BEGIN
1095
1096 l_rem_sales := 0;
1097
1098 IF p_party_id IS NOT NULL THEN
1099 -- compute Remaining balance for given date range
1100
1101 SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
1102 GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
1103 nvl(ps.acctd_amount_due_remaining,0))
1104 INTO l_rem_sales
1105 FROM ar_payment_schedules ps,
1106 hz_cust_accounts ca
1107 WHERE ps.gl_date between p_start_date and p_end_date
1108 AND ps.class in ('INV','DEP','DM','CB')
1109 AND ps.gl_date_closed > p_end_date
1110 AND ps.customer_id = ca.cust_account_id
1111 -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1112 AND ps.status = 'OP'
1113 -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1114 AND ca.party_id = p_party_id;
1115 ELSIF p_cust_account_id IS NOT NULL THEN
1116 -- compute Remaining balance for given date range
1117
1118 SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
1119 GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
1120 nvl(ps.acctd_amount_due_remaining,0))
1121 INTO l_rem_sales
1122 FROM ar_payment_schedules ps
1123 WHERE ps.gl_date between p_start_date and p_end_date
1124 AND ps.class in ('INV','DEP','DM','CB')
1125 AND ps.gl_date_closed > p_end_date
1126 -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1127 AND ps.status = 'OP'
1128 -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1129 AND ps.customer_id = p_cust_account_id;
1130 ELSIF p_customer_site_use_id IS NOT NULL THEN
1131 -- compute Remaining balance for given date range
1132
1133 SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
1134 GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
1135 nvl(ps.acctd_amount_due_remaining,0))
1136 INTO l_rem_sales
1137 FROM ar_payment_schedules ps
1138 WHERE ps.gl_date between p_start_date and p_end_date
1139 AND ps.class in ('INV','DEP','DM','CB')
1140 AND ps.gl_date_closed > p_end_date
1141 -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1142 AND ps.status = 'OP'
1143 -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
1144 AND ps.customer_site_use_id = p_customer_site_use_id;
1145 END IF;
1146
1147 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1148 l_num_val := (NVL(l_rem_sales,0));
1149 RETURN l_num_val;
1150 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1151
1152 EXCEPTION
1153 WHEN NO_DATA_FOUND THEN RETURN(0);
1154 END COMP_REM_REC;
1155
1156 /*-------------------------------------------------------------------------+
1157 | PUBLIC FUNCTION |
1158 | |
1162 | Given a date range, this function will compute the total current |
1159 | comp_curr_rec |
1160 | |
1161 | DESCRIPTION |
1163 | receivables within the date range |
1164 | |
1165 | REQUIRES |
1166 | start_date |
1167 | end_date |
1168 | |
1169 | OPTIONAL |
1170 | |
1171 | RETURNS |
1172 | total current receivables |
1173 | |
1174 | NOTES |
1175 | |
1176 | EXAMPLE |
1177 | |
1178 | MODIFICATION HISTORY |
1179 +-------------------------------------------------------------------------*/
1180
1181 FUNCTION COMP_CURR_REC(p_start_date IN DATE,
1182 p_end_date IN DATE,
1183 p_party_id IN NUMBER,
1184 p_cust_account_id IN NUMBER,
1185 p_customer_site_use_id IN NUMBER) RETURN NUMBER IS
1186
1187 l_curr_rec NUMBER;
1188 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1189 l_num_val NUMBER;
1190 l_char_val VARCHAR2(1000);
1191 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1192
1193 BEGIN
1194
1195 l_curr_rec := 0;
1196
1197 IF p_party_id IS NOT NULL THEN
1198 -- compute Remaining balance for given date range
1199
1200 SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
1201 INTO l_curr_rec
1202 FROM ar_payment_schedules ps,
1203 hz_cust_accounts ca
1204 WHERE ps.gl_date between p_start_date and p_end_date
1205 AND ps.class in ('INV','DEP','DM','CB')
1206 AND ps.gl_date_closed > p_end_date
1207 AND ps.status = 'OP'
1208 AND ps.customer_id = ca.cust_account_id
1209 AND ca.party_id = p_party_id
1210 AND ps.due_date > p_end_date;
1211 ELSIF p_cust_account_id IS NOT NULL THEN
1212 -- compute Remaining balance for given date range
1213
1214 SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
1215 INTO l_curr_rec
1216 FROM ar_payment_schedules ps
1217 WHERE ps.gl_date between p_start_date and p_end_date
1218 AND ps.class in ('INV','DEP','DM','CB')
1219 AND ps.gl_date_closed > p_end_date
1220 AND ps.status = 'OP'
1221 AND ps.customer_id = p_cust_account_id
1222 AND ps.due_date > p_end_date;
1223 ELSIF p_customer_site_use_id IS NOT NULL THEN
1224 -- compute Remaining balance for given date range
1225
1226 SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
1227 INTO l_curr_rec
1228 FROM ar_payment_schedules ps
1229 WHERE ps.gl_date between p_start_date and p_end_date
1230 AND ps.class in ('INV','DEP','DM','CB')
1231 AND ps.gl_date_closed > p_end_date
1232 AND ps.status = 'OP'
1233 AND ps.customer_site_use_id = p_customer_site_use_id
1234 AND ps.due_date > p_end_date;
1235 END IF;
1236
1237 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1238 --RETURN (NVL(l_curr_rec,0));
1239 l_num_val := (NVL(l_curr_rec,0));
1240 RETURN l_num_val;
1241 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1242
1243 EXCEPTION
1244 WHEN NO_DATA_FOUND THEN RETURN(0);
1245 END COMP_CURR_REC;
1246
1247
1248 /*========================================================================
1249 | PRIVATE FUNCTION GET_APPS_TOTAL
1250 |
1251 | DESCRIPTION
1252 | Calculates the total applications against a payment_schedule
1253 |
1254 =======================================================================*/
1255
1256 FUNCTION GET_APPS_TOTAL(p_payment_schedule_id IN NUMBER,
1257 p_to_date IN DATE) RETURN NUMBER IS
1258 l_apps_tot NUMBER;
1259
1260 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1261 l_num_val NUMBER;
1262 l_char_val VARCHAR2(1000);
1263 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1264
1265 BEGIN
1266 SELECT sum( nvl(ra.acctd_amount_applied_to,0) +
1267 nvl(ra.acctd_earned_discount_taken,0) +
1268 nvl(ra.acctd_unearned_discount_taken,0))
1269 INTO l_apps_tot
1270 FROM ar_receivable_applications ra
1271 WHERE ra.applied_payment_schedule_id = p_payment_schedule_id
1272 AND ra.status = 'APP'
1273 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1274 AND ra.gl_date > p_to_date;
1275
1279 RETURN l_num_val;
1276 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1277 --RETURN NVL(l_apps_tot,0);
1278 l_num_val := NVL(l_apps_tot,0);
1280 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1281
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN RETURN(0);
1284
1285 END GET_APPS_TOTAL;
1286
1287 /*========================================================================
1288 | PRIVATE FUNCTION GET_ADJ_TOTAL
1289 |
1290 | DESCRIPTION
1291 | Calculates the total adjustments against a payment_schedule
1292 |
1293 *=======================================================================*/
1294
1295 FUNCTION GET_ADJ_TOTAL(p_payment_schedule_id IN NUMBER,
1296 p_to_date IN DATE) RETURN NUMBER IS
1297 l_adj_tot NUMBER;
1298
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 SELECT sum( nvl(a.acctd_amount,0))
1306 INTO l_adj_tot
1307 FROM ar_adjustments a
1308 WHERE a.payment_schedule_id = p_payment_schedule_id
1309 AND a.status = 'A'
1310 AND a.gl_date > p_to_date;
1311
1312 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1313 --RETURN nvl(l_adj_tot,0);
1314 l_num_val := nvl(l_adj_tot,0);
1315 RETURN l_num_val;
1316 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1317
1318 EXCEPTION
1319 WHEN NO_DATA_FOUND THEN RETURN(0);
1320
1321 END GET_ADJ_TOTAL;
1322
1323 FUNCTION GET_ADJ_FOR_TOT_REC(p_payment_schedule_id IN NUMBER,
1324 p_to_date IN DATE) RETURN NUMBER IS
1325 l_adj_for_tot_rec NUMBER;
1326
1327 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1328 l_num_val NUMBER;
1329 l_char_val VARCHAR2(1000);
1330 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1331
1332 BEGIN
1333 SELECT sum( nvl(a.acctd_amount,0))
1334 INTO l_adj_for_tot_rec
1335 FROM ar_adjustments a
1336 WHERE a.payment_schedule_id = p_payment_schedule_id
1337 AND a.status = 'A'
1338 AND a.gl_date <= p_to_date;
1339
1340 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1341 --RETURN nvl(l_adj_for_tot_rec,0);
1342 l_num_val := nvl(l_adj_for_tot_rec,0);
1343 RETURN l_num_val;
1344 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1345
1346 EXCEPTION
1347 WHEN NO_DATA_FOUND THEN RETURN(0);
1348
1349 END GET_ADJ_FOR_TOT_REC;
1350
1351 FUNCTION GET_CREDIT_LIMIT(p_party_id IN NUMBER,
1352 p_cust_account_id IN NUMBER,
1353 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
1354 l_credit_limit NUMBER;
1355 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1356 l_num_val NUMBER;
1357 l_char_val VARCHAR2(1000);
1358 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1359
1360 BEGIN
1361 IF p_party_id IS NOT NULL THEN
1362 -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1363 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1364 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,
1365 -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1366 sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1367 INTO l_credit_limit
1368 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
1369 ar_cmgt_setup_options cm_opt
1370 WHERE prof.party_id = p_party_id
1371 AND prof.site_use_id IS NULL
1372 AND prof.status = 'A'
1373 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
1374 AND prof_amt.cust_account_id = prof.cust_account_id
1375 --Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
1376 AND prof.cust_account_id = -1
1377 --End-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
1378 AND prof_amt.site_use_id IS NULL;
1379
1380 ELSIF p_cust_account_id IS NOT NULL THEN
1381 -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1382 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1383 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,
1384 -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1385 sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1386 INTO l_credit_limit
1387 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
1388 ar_cmgt_setup_options cm_opt
1389 WHERE prof.cust_account_id = p_cust_account_id
1390 AND prof.site_use_id IS NULL
1391 AND prof.status = 'A'
1395 ELSIF p_customer_site_use_id IS NOT NULL THEN
1392 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
1393 AND prof_amt.cust_account_id = p_cust_account_id
1394 AND prof_amt.site_use_id IS NULL;
1396 -- Begin fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1397 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
1398 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,
1399 -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
1400 sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
1401 INTO l_credit_limit
1402 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
1403 ar_cmgt_setup_options cm_opt
1404 WHERE prof.site_use_id = p_customer_site_use_id
1405 AND prof.status = 'A'
1406 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
1407 AND prof_amt.site_use_id = p_customer_site_use_id;
1408 END IF;
1409
1410 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1411 -- RETURN (TO_CHAR(nvl(l_credit_limit,0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1412 --l_num_val := nvl(l_credit_limit,0);
1413 l_num_val := l_credit_limit;
1414 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1415 RETURN l_char_val;
1416 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1417
1418 EXCEPTION
1419 WHEN NO_DATA_FOUND THEN RETURN(0);
1420
1421 END GET_CREDIT_LIMIT;
1422
1423 FUNCTION GET_HIGH_CREDIT_YTD(p_party_id IN NUMBER,
1424 p_cust_account_id IN NUMBER,
1425 p_customer_site_use_id IN NUMBER) RETURN VARCHAR2 AS
1426 l_high_credit_ytd NUMBER;
1427 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1428 l_num_val NUMBER;
1429 l_char_val VARCHAR2(1000);
1430 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1431
1432 BEGIN
1433 IF p_party_id IS NOT NULL THEN
1434 SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1435 sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1436 INTO l_high_credit_ytd
1437 FROM ar_trx_summary trx_summ,
1438 ar_cmgt_setup_options cm_opt,
1439 hz_cust_accounts ca
1440 WHERE NVL(trx_summ.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
1441 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
1442 = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
1443 AND trx_summ.cust_account_id = ca.cust_account_id
1444 AND ca.party_id = p_party_id;
1445 ELSIF p_cust_account_id IS NOT NULL THEN
1446 SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1447 sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1448 INTO l_high_credit_ytd
1449 FROM ar_trx_summary trx_summ,
1450 ar_cmgt_setup_options cm_opt
1451 WHERE NVL(trx_summ.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
1452 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
1453 = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
1454 AND trx_summ.cust_account_id = p_cust_account_id;
1455 ELSIF p_customer_site_use_id IS NOT NULL THEN
1456 SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
1457 sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
1458 INTO l_high_credit_ytd
1459 FROM ar_trx_summary trx_summ,
1460 ar_cmgt_setup_options cm_opt
1461 WHERE NVL(trx_summ.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
1462 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
1463 = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
1464 AND trx_summ.site_use_id = p_customer_site_use_id;
1465 END IF;
1466
1467 --BEGIN-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1468 --RETURN TO_CHAR(nvl(l_high_credit_ytd,0), fnd_currency.get_format_mask(g_curr_rec.base_currency, 50));
1469 l_num_val := nvl(l_high_credit_ytd,0);
1470 l_char_val := RTRIM(TO_CHAR(l_num_val, fnd_currency.get_format_mask(g_curr_rec.base_currency, 50)));
1471 RETURN l_char_val;
1472 --END-FIX Bug#5247669-08/02/2006-jypark- simplified by using an intermediate varchar variable
1473
1474 EXCEPTION
1475 WHEN NO_DATA_FOUND THEN RETURN(0);
1476
1477 END GET_HIGH_CREDIT_YTD;
1478
1479 BEGIN
1480 --Begin bug#5208170 schekuri 29-May-2006
1481 --Commented out these as the same values are getting populated in IEX_METRIC_PVT.GET_METRIC_INFO
1482 /*SELECT sob.currency_code,
1483 c.precision,
1484 c.minimum_accountable_unit
1485 INTO g_curr_rec.base_currency,
1486 g_curr_rec.base_precision,
1487 g_curr_rec.base_min_acc_unit
1488 FROM ar_system_parameters sysp,
1489 gl_sets_of_books sob,
1490 fnd_currencies c
1491 WHERE sob.set_of_books_id = sysp.set_of_books_id
1492 AND sob.currency_code = c.currency_code;
1493
1494 -- Past Year From and To
1495 SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
1496 TRUNC(sysdate) pastYearTo
1497 INTO g_curr_rec.past_year_from,
1498 g_curr_rec.past_year_to
1499 FROM dual;*/
1500 NULL;
1501 --End bug#5208170 schekuri 29-May-2006
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 NULL;
1505
1506 END;
[Home] [Help]