[Home] [Help]
PACKAGE BODY: APPS.ARP_COLL_IND
Source
1 PACKAGE BODY ARP_COLL_IND AS
2 /* $Header: ARCOLINB.pls 115.16 2003/10/10 14:23:45 mraymond ship $ */
3
4
5 /*========================================================================
6 | Prototype Declarations
7 *=======================================================================*/
8
9 /*========================================================================
10 | PRIVATE PROCEDURE Get_Currency_Details
11 |
12 | DESCRIPTION
13 | Retrieves Currency, precision and min acct unit
14 | -----------------------------------------------------------
15 |
16 | PARAMETERS
17 | p_sob_id Set of Books Id
18 | p_call_from Which application (AR or GL) we are being called by
19 |
20 =======================================================================*/
21
22 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
23
24 PROCEDURE Get_Currency_Details(
25 p_sob_id IN NUMBER
26 ,p_call_from IN NUMBER DEFAULT 222
27 ) IS
28 BEGIN
29
30 if p_sob_id is null then -- Use SOB Id from AR_SYSTEM_PARAMETERS
31
32 SELECT sob.currency_code,
33 c.precision,
34 c.minimum_accountable_unit
35 INTO curr_rec.base_currency,
36 curr_rec.base_precision,
37 curr_rec.base_min_acc_unit
38 FROM ar_system_parameters sysp,
39 gl_sets_of_books sob,
40 fnd_currencies c
41 WHERE sob.set_of_books_id = sysp.set_of_books_id
42 AND sob.currency_code = c.currency_code;
43
44 else -- SOB Id is supplied, so use it
45
46 SELECT sob.currency_code,
47 c.precision,
48 c.minimum_accountable_unit
49 INTO curr_rec.base_currency,
50 curr_rec.base_precision,
51 curr_rec.base_min_acc_unit
52 FROM gl_sets_of_books sob,
53 fnd_currencies c
54 WHERE sob.set_of_books_id = p_sob_id
55 AND sob.currency_code = c.currency_code;
56
57 end if;
58
59 EXCEPTION
60
61 when no_data_found then
62
63 if p_call_from = 222 then -- Called by AR responsibility
64
65 IF PG_DEBUG in ('Y', 'C') THEN
66 arp_standard.debug( 'Get_Currency_Details - NO_DATA_FOUND' );
67 END IF;
68 RAISE;
69
70 elsif p_call_from = 101 then -- Called by GL responsibility
71
72 raise_application_error( -20000,
73 'No data found in Get_Currency_Details' );
74
75 end if;
76
77 END Get_Currency_Details;
78
79 /*-------------------------------------------------------------------------+
80 | PUBLIC FUNCTION |
81 | |
82 | comp_tot_rec |
83 | |
84 | DESCRIPTION |
85 | Given a date range, this function will compute the total original |
86 | receivables within the date range |
87 | If function is called with a null start date, then the function |
88 | returns total original receivables as of pend_date |
89 | |
90 | REQUIRES |
91 | start_date |
92 | end_date |
93 | set_of_books_id |
94 | call_from |
95 | |
96 | OPTIONAL |
97 | customer_id |
98 | site_id |
99 | |
100 | RETURNS |
101 | total original receivables |
102 | |
103 | NOTES |
104 | |
105 | EXAMPLE |
106 | |
107 | MODIFICATION HISTORY |
108 | 05-Aug-98 Victoria Smith Created. |
109 |
110 | |
111 +-------------------------------------------------------------------------*/
112
113 FUNCTION comp_tot_rec(pstart_date IN DATE,
114 pend_date IN DATE,
115 psob_id IN NUMBER,
116 pcall_from IN NUMBER DEFAULT 222,
117 pcust_id IN NUMBER DEFAULT -1,
118 psite_id IN NUMBER DEFAULT -1)
119 RETURN NUMBER IS
120
121 tot_rec NUMBER;
122 temp_start DATE;
123
124 BEGIN
125
126 /* Get Currency Details to calculate acctd_amount_due_original*/
127 Get_Currency_Details( psob_id, pcall_from );
128
129 if pstart_date is null then
130 -- default date to earliest date to pick up everything prior to
131 -- pend_date
132 temp_start := to_date('01/01/1952','MM/DD/YYYY');
133 else
134 temp_start := pstart_date;
135 end if;
136
137 if pcall_from = 222 then -- Called by AR responsibility
138
139 SELECT SUM(arpcurr.functional_amount(
140 ps.amount_due_original,
141 curr_rec.base_currency,
142 nvl(ps.exchange_rate,1),
143 curr_rec.base_precision,
144 curr_rec.base_min_acc_unit) +
145 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pend_date))
146 INTO tot_rec
147 FROM ar_payment_schedules ps
148 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
149 AND ps.payment_schedule_id <> -1
150 AND ps.gl_date BETWEEN temp_start AND pend_date
151 AND (pcust_id = -1 OR
152 (pcust_id <> -1 AND ps.customer_id = pcust_id))
153 AND (psite_id = -1 OR
154 (psite_id <> -1 AND ps.customer_site_use_id = psite_id));
155
156 elsif pcall_from = 101 then -- Called by GL responsibility
157
158 -- SELECT clause modified by S.Bhattal to avoid calling package ARPCURR
159 -- (so that we don't crash in ARPCURR's initialization section, which
160 -- refers to multi-org view AR_SYSTEM_PARAMETERS)
161
162 SELECT SUM( gl_currency_api.convert_closest_amount_sql(
163 ps.invoice_currency_code
164 ,curr_rec.base_currency
165 ,to_date(null)
166 ,'User'
167 ,nvl(ps.exchange_rate,1)
168 ,ps.amount_due_original
169 ,to_number(null)
170 )
171 + Get_Adj_For_Tot_Rec_GL(ps.payment_schedule_id,pend_date)
172 )
173 INTO tot_rec
174 FROM ar_payment_schedules_all ps,
175 ra_customer_trx_all trx
176 WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
177 AND ps.payment_schedule_id <> -1
178 AND ps.customer_trx_id = trx.customer_trx_id
179 AND trx.set_of_books_id = psob_id
180 AND ps.gl_date BETWEEN temp_start AND pend_date
181 AND (pcust_id = -1 OR
182 (pcust_id <> -1 AND ps.customer_id = pcust_id))
183 AND (psite_id = -1 OR
184 (psite_id <> -1 AND ps.customer_site_use_id = psite_id));
185 end if;
186
187 return(nvl(tot_rec,0));
188
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN return(0);
191
192 END comp_tot_rec;
193
194 /*-------------------------------------------------------------------------+
195 | PUBLIC FUNCTION |
196 | |
197 | comp_rem_rec |
198 | |
199 | DESCRIPTION |
200 | Given a date range, this function will compute the total remaining |
201 | receivables within the date range |
202 | |
203 | REQUIRES |
204 | start_date |
205 | end_date |
206 | set_of_books_id |
207 | call_from |
208 | |
209 | OPTIONAL |
210 | customer_id |
211 | site_id |
212 | |
213 | RETURNS |
214 | total remaining receivables |
215 | |
216 | NOTES |
217 | |
218 | EXAMPLE |
219 | |
220 | MODIFICATION HISTORY |
221 | 05-Aug-98 Victoria Smith Created. |
222 | 24-JAN-02 P.LAU Modified function to return 0 when |
223 | there is no record found |
224 +-------------------------------------------------------------------------*/
225
226 FUNCTION comp_rem_rec(pstart_date IN DATE,
227 pend_date IN DATE,
228 psob_id IN NUMBER,
229 pcall_from IN NUMBER DEFAULT 222,
230 pcust_id IN NUMBER DEFAULT -1,
231 psite_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
232
233 rem_sales NUMBER;
234 BEGIN
235
236 rem_sales := 0;
237
238 if pcall_from = 222 then -- Called by AR responsibility
239
240 -- compute Remaining balance for given date range
241
242 SELECT sum(Get_Apps_Total(ps.payment_schedule_id,pend_date) -
243 Get_Adj_Total(ps.payment_schedule_id,pend_date) +
244 nvl(ps.acctd_amount_due_remaining,0))
245 INTO rem_sales
246 FROM ar_payment_schedules ps
247 WHERE ps.gl_date between pstart_date and pend_date
248 AND ps.class in ('INV','DEP','DM','CB')
249 AND ps.gl_date_closed > pend_date
250 AND (pcust_id = -1 OR
251 (pcust_id <> -1 AND ps.customer_id = pcust_id));
252
253 elsif pcall_from = 101 then -- Called by GL responsibility
254
255 -- compute Remaining balance for given date range
256
257 SELECT sum(Get_Apps_Total_GL(ps.payment_schedule_id,pend_date) -
258 Get_Adj_Total_GL(ps.payment_schedule_id,pend_date) +
259 nvl(ps.acctd_amount_due_remaining,0))
260 INTO rem_sales
261 FROM ar_payment_schedules_all ps,
262 ra_customer_trx_all trx
263 WHERE ps.gl_date between pstart_date and pend_date
264 AND ps.class in ('INV','DEP','DM','CB')
265 AND ps.customer_trx_id = trx.customer_trx_id
266 AND trx.set_of_books_id = psob_id
267 AND ps.gl_date_closed > pend_date
268 AND (pcust_id = -1 OR
269 (pcust_id <> -1 AND ps.customer_id = pcust_id));
270 end if;
271
272 return(nvl(rem_sales,0));
273
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN return(0);
276
277 END comp_rem_rec;
278
279 /*-------------------------------------------------------------------------+
280 | PUBLIC FUNCTION |
281 | |
282 | comp_dso |
283 | |
284 | DESCRIPTION |
285 | Given a date range, this function will compute DSO |
286 | |
287 | REQUIRES |
288 | pstart_date |
289 | pas_of_date |
290 | set_of_books_id |
291 | call_from |
292 | |
293 | OPTIONAL |
294 | customer_id |
295 | site_id |
296 | |
297 | RETURNS |
298 | Days Sales Outstanding |
299 | |
300 | NOTES |
301 | |
302 | EXAMPLE |
303 | |
304 | MODIFICATION HISTORY |
305 | 05-Aug-98 Victoria Smith Created. |
306 | 07-AUG-98 Victoria Smith Modified parameters passed |
307 | |
308 +-------------------------------------------------------------------------*/
309
310 FUNCTION comp_dso(pstart_date IN DATE,
311 pas_of_date IN DATE,
312 psob_id IN NUMBER,
313 pcall_from IN NUMBER DEFAULT 222,
314 pcust_id IN NUMBER DEFAULT -1,
315 psite_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
316
317 sales NUMBER;
318 beg_ar NUMBER;
319 end_ar NUMBER;
320 dso NUMBER;
321
322 begin
323
324 /*-----------------------------------------------------------------------
325 DSO = ( Period Average Receivables / Average Sales per day)
326
327 where tot outs rec = sum of all receivables less all receipts (use comp_rem_rec)
328 avg sales per day = sum of all receivables (use comp_tot_rec) / days in period
329 -----------------------------------------------------------------------*/
330
331 sales := comp_tot_rec(pstart_date, pas_of_date,
332 psob_id, pcall_from, pcust_id,psite_id);
333
334 beg_ar := comp_rem_rec(to_date('01/01/1952','MM/DD/YYYY'), pstart_date - 1,
335 psob_id, pcall_from, pcust_id, psite_id);
336
337 end_ar := comp_rem_rec(to_date('01/01/1952','MM/DD/YYYY'), pas_of_date,
338 psob_id, pcall_from, pcust_id, psite_id);
339
340 if ( nvl(sales,0) = 0 ) then
341 dso := 0;
342 else
343 dso := (((beg_ar + end_ar)/2)/sales)*(pas_of_date - pstart_date);
344 end if;
345
346 return nvl(dso,0);
347
348 end comp_dso;
349
350 /*-------------------------------------------------------------------------+
351 | PRIVATE FUNCTION |
352 | |
353 | is_ar_installed |
354 | |
355 | DESCRIPTION |
356 | This function determines whether AR is fully installed or not. |
357 | |
358 | REQUIRES |
359 | No parameters. |
360 | |
361 | RETURNS |
362 | 'Y' (Yes) or 'N' (No). |
363 | |
364 | NOTES |
365 | This private function is called by the following public functions; |
366 | COMP_DSO_GL |
367 | COMP_TURNOVER_GL |
368 | |
369 | EXAMPLE |
370 | |
371 | MODIFICATION HISTORY |
372 | 08-Aug-00 S.Bhattal (FII) Bug 1366961 - created. |
373 | |
374 +-------------------------------------------------------------------------*/
375
376 FUNCTION is_ar_installed RETURN VARCHAR2 IS
377
378 l_industry fnd_product_installations.industry%type;
379 l_oracle_schema varchar2(30);
380 l_status fnd_product_installations.status%type;
381
382 l_return_value boolean;
383
384 begin
385
386 l_return_value := fnd_installation.get_app_info(
387 application_short_name => 'AR'
388 ,status => l_status
389 ,industry => l_industry
390 ,oracle_schema => l_oracle_schema
391 );
392
393 if l_return_value = true then -- Function call ok
394
395 if l_status = 'I' then -- Fully Installed
396 return('Y');
397 else
398 return('N');
399 end if;
400
401 else
402 return('N');
403 end if;
404
405 exception
406
407 when others then
408 raise_application_error( -20000, 'Error in is_ar_installed: ' ||
409 SQLERRM );
410
411 end is_ar_installed;
412
413 /*-------------------------------------------------------------------------+
414 | PUBLIC FUNCTION |
415 | |
416 | comp_dso_gl |
417 | |
418 | DESCRIPTION |
419 | This is a cover function for use in the GL Summary report, it is |
420 | basically a call to comp_dso with 2 additional parameters |
421 | preport_name, preport_params |
422 | Given a date range, this function will compute DSO |
423 | |
424 | REQUIRES |
425 | pstart_date |
426 | pas_of_date |
427 | set_of_books_id |
428 | preport_name |
429 | preport_params |
430 | |
431 | RETURNS |
432 | Days Sales Outstanding |
433 | |
434 | NOTES |
435 | |
436 | EXAMPLE |
437 | |
438 | MODIFICATION HISTORY |
439 | 11-Sep-98 Victoria Smith Created. |
440 | |
441 | 07-Aug-00 S.Bhattal (FII) Test whether AR is installed, |
442 | return 0 when AR is not installed |
443 | 24-JAN-02 P.LAU Modified function to return 0 when |
444 | there is no record found |
445 | |
446 +-------------------------------------------------------------------------*/
447
448 FUNCTION comp_dso_gl(pstart_date IN DATE,
449 pas_of_date IN DATE,
450 psob_id IN NUMBER,
451 preport_name OUT NOCOPY VARCHAR2,
452 preport_params OUT NOCOPY VARCHAR2,
453 pcust_id IN NUMBER DEFAULT -1,
454 psite_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
455
456 l_dso number;
457 l_install_flag varchar2(1);
458
459 begin
460
461 l_install_flag := is_ar_installed;
462
463 if l_install_flag = 'Y' then
464 preport_name := 'FIIARDSO';
465
466 preport_params := 'AS_OF_DATE=' ||
467 to_char( pas_of_date, 'DD-MON-YYYY' ) || '*' || 'P_SOB_ID=' || psob_id;
468
469 l_dso := comp_dso( pstart_date, pas_of_date, psob_id, 101, pcust_id,
470 psite_id );
471 else
472 preport_name := 'N/A';
473 preport_params := 'N/A';
474 l_dso := 0;
475 end if;
476
477 l_dso := round(l_dso, 10 );
478
479 if length( to_char(l_dso) ) > 42 then
480 l_dso := to_number( substr( to_char(l_dso),1,42 ) );
481 end if;
482
483 return(nvl(l_dso,0));
484
485 end comp_dso_gl;
486
487 /*-------------------------------------------------------------------------+
488 | PUBLIC FUNCTION |
489 | |
490 | comp_turnover |
491 | |
492 | DESCRIPTION |
493 | Given a date range, this function will compute AR Turnover |
494 | |
495 | REQUIRES |
496 | start_date |
497 | end_date |
498 | set_of_books_id |
499 | call_from |
500 | |
501 | OPTIONAL |
502 | customer_id |
503 | site_id |
504 | |
505 | RETURNS |
506 | AR Turnover |
507 | |
508 | NOTES |
509 | |
510 | EXAMPLE |
511 | |
512 | MODIFICATION HISTORY |
513 | 05-Aug-98 Victoria Smith Created. |
514 | |
515 +-------------------------------------------------------------------------*/
516
517 FUNCTION comp_turnover(pstart_date IN DATE,
518 pend_date IN DATE,
519 psob_id IN NUMBER,
520 pcall_from IN NUMBER DEFAULT 222,
521 pcust_id IN NUMBER DEFAULT -1,
522 psite_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
523
524 sales NUMBER;
525 turnover NUMBER;
526 beg_ar NUMBER;
527 end_ar NUMBER;
528 avg_ar NUMBER;
529
530 begin
531 /*--------------------------------------------------------------------------
532 Turnover = Net Sales / Average Net Accounts Receivables
533
534 where Net Sales = sum of amount due original within period (use comp_tot_rec)
535 Ave Net Accts Rec =(Beginning Receivables + Ending Receivables ) / 2
536 --------------------------------------------------------------------------*/
537
538 sales := comp_tot_rec(pstart_date, pend_date,
539 psob_id, pcall_from, pcust_id, psite_id);
540
541 beg_ar := comp_rem_rec(to_date('01/01/1952','MM/DD/YYYY'), pstart_date - 1,
542 psob_id, pcall_from, pcust_id, psite_id);
543
544 end_ar := comp_rem_rec(to_date('01/01/1952','MM/DD/YYYY'), pend_date,
545 psob_id,pcall_from, pcust_id, psite_id);
546
547 avg_ar := (beg_ar + end_ar) / 2;
548
549 if ( nvl(avg_ar,0) = 0 ) then
550 turnover := 0;
551 else
552 turnover := sales / avg_ar;
553 end if;
554
555 return nvl(turnover,0);
556
557 end comp_turnover;
558
559 /*-------------------------------------------------------------------------+
560 | PUBLIC FUNCTION |
561 | |
562 | comp_turnover_gl |
563 | |
564 | DESCRIPTION |
565 | This is a cover function for use in the GL Summary report, it is |
566 | basically a call to comp_turnover with 2 additional parameters |
567 | preport_name, preport_params |
568 | Given a date range, this function will compute AR Turnover |
569 | |
570 | REQUIRES |
571 | start_date |
572 | end_date |
573 | set_of_books_id |
574 | preport_name |
575 | preport_params |
576 | |
577 | RETURNS |
578 | AR Turnover |
579 | |
580 | NOTES |
581 | |
582 | EXAMPLE |
583 | |
584 | MODIFICATION HISTORY |
585 | 11-Sep-98 Victoria Smith Created. |
586 | |
587 | 07-Aug-00 S.Bhattal (FII) Test whether AR is installed, |
588 | return 0 when AR is not installed |
589 | 24-JAN-02 P.LAU Modified function to return 0 when |
590 | there is no record found |
591 | |
592 +-------------------------------------------------------------------------*/
593
594 FUNCTION comp_turnover_gl(pstart_date IN DATE,
595 pend_date IN DATE,
596 psob_id IN NUMBER,
597 preport_name OUT NOCOPY VARCHAR2,
598 preport_params OUT NOCOPY VARCHAR2,
599 pcust_id IN NUMBER DEFAULT -1,
600 psite_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
601
602 l_turnover number;
603 l_install_flag varchar2(1);
604
605 begin
606
607 l_install_flag := is_ar_installed;
608
609 if l_install_flag = 'Y' then
610 preport_name := 'FIIARTRN';
611
612 preport_params := 'AS_OF_DATE=' ||
613 to_char( pend_date, 'DD-MON-YYYY' ) || '*' || 'P_SOB_ID=' || psob_id;
614
615 l_turnover := comp_turnover( pstart_date, pend_date, psob_id, 101,
616 pcust_id, psite_id );
617 else
618 preport_name := 'N/A';
619 preport_params := 'N/A';
620 l_turnover := 0;
621 end if;
622
623 l_turnover := round(l_turnover, 10 );
624
625 if length( to_char(l_turnover) ) > 42 then
626 l_turnover:= to_number( substr( to_char(l_turnover),1,42 ) );
627 end if;
628
629 return(nvl(l_turnover,0));
630
631 end comp_turnover_gl;
632
633 /*========================================================================
634 | PRIVATE FUNCTION Get_Apps_Total
635 |
636 | DESCRIPTION
637 | Calculates the total applications against a payment_schedule
638 |
639 =======================================================================*/
640
641 FUNCTION Get_Apps_Total(pay_sched_id IN NUMBER,
642 pto_date IN DATE) RETURN NUMBER IS
643 apps_tot NUMBER;
644
645 BEGIN
646 SELECT sum( nvl(ra.acctd_amount_applied_to,0) +
647 nvl(ra.acctd_earned_discount_taken,0) +
648 nvl(ra.acctd_unearned_discount_taken,0))
649 INTO apps_tot
650 FROM ar_receivable_applications ra
651 WHERE ra.applied_payment_schedule_id = pay_sched_id
652 AND ra.status = 'APP'
653 AND nvl(ra.confirmed_flag,'Y') = 'Y'
654 AND ra.gl_date > pto_date;
655
656 RETURN NVL(apps_tot,0);
657
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN RETURN(0);
660
661 END Get_Apps_Total;
662
663 /*========================================================================
664 | PRIVATE FUNCTION Get_Adj_For_Tot_Rec
665 |
666 | DESCRIPTION
667 | Calculates the total adjustments against a payment_schedule
668 | to obtain total receivables in a period.
669 |
670 *=======================================================================*/
671
672 FUNCTION Get_Adj_For_Tot_Rec(pay_sched_id IN NUMBER,
673 pto_date IN DATE) RETURN NUMBER IS
674 adj_for_tot_rec NUMBER;
675
676 BEGIN
677 SELECT sum( nvl(a.acctd_amount,0))
678 INTO adj_for_tot_rec
679 FROM ar_adjustments a
680 WHERE a.payment_schedule_id = pay_sched_id
681 AND a.status = 'A'
682 AND a.gl_date <= pto_date;
683
684 RETURN nvl(adj_for_tot_rec,0);
685
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN RETURN(0);
688
689 END Get_Adj_For_Tot_Rec;
690
691 /*========================================================================
692 | PRIVATE FUNCTION Get_Adj_For_Tot_Rec_GL
693 |
694 | DESCRIPTION
695 | Calculates the total adjustments against a payment_schedule
696 | to obtain total receivables in a period. Uses '_all' table for GL
697 |
698 *=======================================================================*/
699
700 FUNCTION Get_Adj_For_Tot_Rec_GL(pay_sched_id IN NUMBER,
701 pto_date IN DATE) RETURN NUMBER IS
702 adj_for_tot_rec_gl NUMBER;
703
704 BEGIN
705 SELECT sum( nvl(a.acctd_amount,0))
706 INTO adj_for_tot_rec_gl
707 FROM ar_adjustments_all a
708 WHERE a.payment_schedule_id = pay_sched_id
709 AND a.status = 'A'
710 AND a.gl_date <= pto_date;
711
712 RETURN nvl(adj_for_tot_rec_gl,0);
713
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN RETURN(0);
716
717 END Get_Adj_For_Tot_Rec_GL;
718
719 /*========================================================================
720 | PRIVATE FUNCTION Get_Adj_Total
721 |
722 | DESCRIPTION
723 | Calculates the total adjustments against a payment_schedule
724 |
725 *=======================================================================*/
726
727 FUNCTION Get_Adj_Total(pay_sched_id IN NUMBER,
728 pto_date IN DATE) RETURN NUMBER IS
729 adj_tot NUMBER;
730
731 BEGIN
732 SELECT sum( nvl(a.acctd_amount,0))
733 INTO adj_tot
734 FROM ar_adjustments a
735 WHERE a.payment_schedule_id = pay_sched_id
736 AND a.status = 'A'
737 AND a.gl_date > pto_date;
738
739 RETURN nvl(adj_tot,0);
740
741 EXCEPTION
742 WHEN NO_DATA_FOUND THEN RETURN(0);
743
744 END Get_Adj_Total;
745
746
747 /*========================================================================
748 | PRIVATE FUNCTION Get_Apps_Total_GL
749 |
750 | DESCRIPTION
751 | Cover routine for GL to calculate the total applications against
752 | a payment_schedule . This routine is called from comp_rem_rec function
753 |
754 *=======================================================================*/
755
756 FUNCTION Get_Apps_Total_GL(pay_sched_id IN NUMBER,
757 pto_date IN DATE) RETURN NUMBER IS
758 apps_tot_gl NUMBER;
759
760 BEGIN
761 SELECT sum( nvl(ra.acctd_amount_applied_to,0) +
762 nvl(ra.acctd_earned_discount_taken,0) +
763 nvl(ra.acctd_unearned_discount_taken,0))
764 INTO apps_tot_gl
765 FROM ar_receivable_applications_all ra
766 WHERE ra.applied_payment_schedule_id = pay_sched_id
767 AND ra.status = 'APP'
768 AND nvl(ra.confirmed_flag,'Y') = 'Y'
769 AND ra.gl_date > pto_date;
770
771 RETURN NVL(apps_tot_gl,0);
772
773 EXCEPTION
774 WHEN NO_DATA_FOUND THEN RETURN(0);
775
776 END Get_Apps_Total_GL;
777
778 /*========================================================================
779 | PRIVATE FUNCTION Get_Adj_Total_GL
780 |
781 | DESCRIPTION
782 | Cover routine for GL to Calculate the total adjustments against a
783 | payment_schedule . This routine is called from comp_rem_rec function
784 |
785 *=======================================================================*/
786
787 FUNCTION Get_Adj_Total_GL(pay_sched_id IN NUMBER,
788 pto_date IN DATE) RETURN NUMBER IS
789 adj_tot_gl NUMBER;
790
791 BEGIN
792 SELECT sum( nvl(a.acctd_amount,0))
793 INTO adj_tot_gl
794 FROM ar_adjustments_all a
795 WHERE a.payment_schedule_id = pay_sched_id
796 AND a.status = 'A'
797 AND a.gl_date > pto_date;
798
799 RETURN nvl(adj_tot_gl,0);
800
801
802 EXCEPTION
803 WHEN NO_DATA_FOUND THEN RETURN(0);
804
805 END Get_Adj_Total_GL;
806
807
808 /*-------------------------------------------------------------------------+
809 | PUBLIC FUNCTION |
810 | |
811 | comp_wtd_days |
812 | |
813 | DESCRIPTION |
814 | Given an as of date, this function will compute for weighted average |
815 | days late |
816 |
817 | Added calls to Get_Adj_Total and Get_Apps_Total
818 | REQUIRES |
819 | start_date |
820 | as_of_date |
821 | set_of_books_id |
822 | |
823 | OPTIONAL |
824 | customer_id |
825 | |
826 | RETURNS |
827 | Weighted Average Days Late |
828 | |
829 | NOTES |
830 | |
831 | EXAMPLE |
832 | |
833 | MODIFICATION HISTORY |
834 | 19-Aug-98 Victoria Smith Created. |
835 | |
836 +-------------------------------------------------------------------------*/
837
838 FUNCTION comp_wtd_days(pstart_date IN DATE,
839 pas_of_date IN DATE,
840 psob_id IN NUMBER,
841 pcust_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
842
843 wtd_days NUMBER;
844 BEGIN
845
846 SELECT sum
847 (
848 (
849 Get_Apps_Total(ps.payment_schedule_id,pas_of_date) -
850 Get_Adj_Total(ps.payment_schedule_id,pas_of_date) +
851 nvl(ps.acctd_amount_due_remaining,0)
852 ) *
853 (pas_of_date-ps.due_date)
854 ) /
855 sum (
856 Get_Apps_Total(ps.payment_schedule_id,pas_of_date) -
857 Get_Adj_Total(ps.payment_schedule_id,pas_of_date) +
858 nvl(ps.acctd_amount_due_remaining,0)
859 )
860 INTO wtd_days
861 FROM ar_payment_schedules ps
862 WHERE ps.gl_date between pstart_date and pas_of_date
863 AND ps.class in ('INV','DEP','DM','CB')
864 AND ps.gl_date_closed > pas_of_date
865 AND ps.due_date < pas_of_date
866 AND ps.payment_schedule_id <> -1
867 AND (pcust_id = -1 OR
868 (pcust_id <> -1 AND ps.customer_id = pcust_id));
869
870 RETURN NVL(wtd_days,0);
871
872 EXCEPTION
873 WHEN NO_DATA_FOUND THEN RETURN(0);
874
875 END comp_wtd_days;
876
877 /*-------------------------------------------------------------------------+
878 | PUBLIC FUNCTION |
879 | |
880 | comp_wtd_bal |
881 | |
882 | DESCRIPTION |
883 | Given an as of date, this function will compute for weighted average |
884 | balance |
885 | |
886 | REQUIRES |
887 | start_date |
888 | as_of_date |
889 | set_of_books_id |
890 | |
891 | OPTIONAL |
892 | customer_id |
893 | |
894 | RETURNS |
895 | Weighted Average Balance |
896 | |
897 | NOTES |
898 | |
899 | EXAMPLE |
900 | |
901 | MODIFICATION HISTORY |
902 | 19-Aug-98 Victoria Smith Created. |
903 | |
904 +-------------------------------------------------------------------------*/
905
906 FUNCTION comp_wtd_bal(pstart_date IN DATE,
907 pas_of_date IN DATE,
908 psob_id IN NUMBER,
909 pcust_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
910 wtd_bal NUMBER;
911
912 BEGIN
913
914 SELECT sum(
915 (
916 Get_Apps_Total(ps.payment_schedule_id,pas_of_date) -
917 Get_Adj_Total(ps.payment_schedule_id,pas_of_date) +
918 nvl(ps.acctd_amount_due_remaining,0)
919 ) *
920 (pas_of_date-ps.due_date)
921 ) /
922 sum(
923 pas_of_date-ps.due_date
924 )
925 INTO wtd_bal
926 FROM ar_payment_schedules ps
927 WHERE ps.gl_date between pstart_date and pas_of_date
928 AND ps.class in ('INV','DEP','DM','CB')
929 AND ps.gl_date_closed > pas_of_date
930 AND ps.due_date < pas_of_date
931 AND ps.payment_schedule_id <> -1
932 AND (pcust_id = -1 OR
933 (pcust_id <> -1 AND ps.customer_id = pcust_id));
934
935 RETURN NVL(wtd_bal,0);
936
937 EXCEPTION
938 WHEN NO_DATA_FOUND THEN RETURN(0);
939
940 END comp_wtd_bal;
941
942 /*-------------------------------------------------------------------------+
943 | PUBLIC FUNCTION |
944 | |
945 | comp_above_amount |
946 | |
947 | DESCRIPTION |
948 | Given an as of date, this function will compute for total amount |
949 | above the split amount |
950 | |
951 | REQUIRES |
952 | start_date |
953 | as_of_date |
954 | set_of_books_id |
955 | split_amount |
956 | |
957 | OPTIONAL |
958 | customer_id |
959 | |
960 | RETURNS |
961 | Total Amount of transaction amounts over the split amount |
962 | |
963 | NOTES |
964 | |
965 | EXAMPLE |
966 | |
967 | MODIFICATION HISTORY |
968 | 28-Aug-98 Victoria Smith Created. |
969 | |
970 +-------------------------------------------------------------------------*/
971
972 FUNCTION comp_above_amount(pstart_date IN DATE,
973 pas_of_date IN DATE,
974 psob_id IN NUMBER,
975 psplit IN NUMBER,
976 pcust_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
977 above_amount NUMBER;
978 BEGIN
979
980 /* Get Currency Details to calculate acctd_amount_due_orginal*/
981
982 Get_Currency_Details( psob_id, 222 );
983
984 SELECT
985 sum(v_above_amount)
986 INTO
987 above_amount
988 FROM (
989 SELECT
990 SUM(
991 arpcurr.functional_amount(
992 ps.amount_due_original,
993 curr_rec.base_currency,
994 nvl(ps.exchange_rate,1),
995 curr_rec.base_precision,
996 curr_rec.base_min_acc_unit
997 ) +
998 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
999 ) v_above_amount
1000 FROM ar_payment_schedules ps
1001 WHERE ps.gl_date BETWEEN pstart_date AND pas_of_date
1002 AND ps.payment_schedule_id <> -1
1003 AND ps.class IN ('INV', 'DM', 'CB', 'DEP' )
1004 AND (pcust_id = -1 OR
1005 (pcust_id <> -1 AND ps.customer_id = pcust_id))
1006 GROUP BY ps.customer_trx_id
1007 HAVING SUM(
1008 arpcurr.functional_amount(
1009 ps.amount_due_original,
1010 curr_rec.base_currency,
1011 nvl(ps.exchange_rate,1),
1012 curr_rec.base_precision,
1013 curr_rec.base_min_acc_unit
1014 ) +
1015 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
1016 ) >= psplit
1017 );
1018
1019 return nvl(above_amount,0);
1020
1021 EXCEPTION
1022
1023 WHEN NO_DATA_FOUND THEN
1024 return(0);
1025
1026 END comp_above_amount;
1027
1028 /*-------------------------------------------------------------------------+
1029 | PUBLIC FUNCTION |
1030 | |
1031 | comp_above_count |
1032 | |
1033 | DESCRIPTION |
1034 | Given an as of date, this function will compute for total number |
1035 | of transactions with transaction amounts above the split amount |
1036 | |
1037 | REQUIRES |
1038 | start_date |
1039 | as_of_date |
1040 | set_of_books_id |
1041 | split_amount |
1042 | |
1043 | OPTIONAL |
1044 | customer_id |
1045 | |
1046 | RETURNS |
1047 | Total Count of transaction with amounts over the split amount |
1048 | |
1049 | NOTES |
1050 | |
1051 | EXAMPLE |
1052 | |
1053 | MODIFICATION HISTORY |
1054 | 28-Aug-98 Victoria Smith Created. |
1055 | 03-Jun-99 Victoria Smith Bug 900896 : performance fix |
1056 | |
1057 +-------------------------------------------------------------------------*/
1058
1059 FUNCTION comp_above_count(pstart_date IN DATE,
1060 pas_of_date IN DATE,
1061 psob_id IN NUMBER,
1062 psplit IN NUMBER,
1063 pcust_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
1064 above_count NUMBER;
1065
1066 BEGIN
1067
1068 /* Get currency details to calculate acctd_amount_due_original */
1069
1070 Get_Currency_Details( psob_id, 222 );
1071
1072 SELECT
1073 SUM(trx)
1074 INTO
1075 above_count
1076 FROM (
1077 SELECT
1078 1 trx
1079 FROM
1080 ar_payment_schedules ps
1081 WHERE
1082 ps.gl_date BETWEEN pstart_date AND pas_of_date
1083 and ps.class IN ('INV', 'DM', 'CB', 'DEP' )
1084 and ps.payment_schedule_id <> -1
1085 and (pcust_id = -1 OR
1086 (pcust_id <> -1 AND ps.customer_id = pcust_id))
1087 GROUP BY ps.customer_trx_id
1088 HAVING SUM(arpcurr.functional_amount(
1089 ps.amount_due_original,
1090 curr_rec.base_currency,
1091 nvl(ps.exchange_rate,1),
1092 curr_rec.base_precision,
1093 curr_rec.base_min_acc_unit
1094 ) +
1095 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
1096 ) >= psplit
1097 );
1098
1099 return nvl(above_count,0);
1100
1101 EXCEPTION
1102 WHEN NO_DATA_FOUND THEN return(0);
1103
1104 END comp_above_count;
1105
1106
1107 /*-------------------------------------------------------------------------+
1108 | PUBLIC FUNCTION |
1109 | |
1110 | comp_below_amount |
1111 | |
1112 | DESCRIPTION |
1113 | Given an as of date, this function will compute for total amount |
1114 | below the split amount |
1115 | |
1116 | REQUIRES |
1117 | start_date |
1118 | as_of_date |
1119 | set_of_books_id |
1120 | split_amount |
1121 | |
1122 | OPTIONAL |
1123 | customer_id |
1124 | |
1125 | RETURNS |
1126 | Total Amount of transaction amounts under the split amount |
1127 | |
1128 | NOTES |
1129 | |
1130 | EXAMPLE |
1131 | |
1132 | MODIFICATION HISTORY |
1133 | 28-Aug-98 Victoria Smith Created. |
1134 | |
1135 +-------------------------------------------------------------------------*/
1136
1137 FUNCTION comp_below_amount(pstart_date IN DATE,
1138 pas_of_date IN DATE,
1139 psob_id IN NUMBER,
1140 psplit IN NUMBER,
1141 pcust_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
1142 below_amount NUMBER;
1143 BEGIN
1144
1145 /* Get currency details to calculate acctd_amount_due_original */
1146
1147 Get_Currency_Details( psob_id, 222 );
1148
1149 SELECT
1150 SUM(v_below_amount)
1151 INTO
1152 below_amount
1153 FROM (
1154 SELECT
1155 SUM(
1156 arpcurr.functional_amount(
1157 ps.amount_due_original,
1158 curr_rec.base_currency,
1159 nvl(ps.exchange_rate,1),
1160 curr_rec.base_precision,
1161 curr_rec.base_min_acc_unit
1162 ) +
1163 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
1164 ) v_below_amount
1165 FROM
1166 ar_payment_schedules ps
1167 WHERE
1168 ps.gl_date BETWEEN pstart_date AND pas_of_date
1169 AND ps.class in ('INV', 'DM', 'CB', 'DEP' )
1170 AND ps.payment_schedule_id <> -1
1171 AND ( pcust_id = -1 OR
1172 (pcust_id <> -1 AND ps.customer_id = pcust_id))
1173 GROUP BY ps.customer_trx_id
1174 HAVING SUM(
1175 arpcurr.functional_amount(
1176 ps.amount_due_original,
1177 curr_rec.base_currency,
1178 nvl(ps.exchange_rate,1),
1179 curr_rec.base_precision,
1180 curr_rec.base_min_acc_unit
1181 ) +
1182 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
1183 ) < psplit
1184 );
1185
1186 return nvl(below_amount,0);
1187
1188 EXCEPTION
1189 WHEN NO_DATA_FOUND THEN return(0);
1190
1191 END comp_below_amount;
1192
1193 /*-------------------------------------------------------------------------+
1194 | PUBLIC FUNCTION |
1195 | |
1196 | comp_below_count |
1197 | |
1198 | DESCRIPTION |
1199 | Given an as of date, this function will compute for total number |
1200 | of transactions with transaction amounts below the split amount |
1201 | |
1202 | REQUIRES |
1203 | start_date |
1204 | as_of_date |
1205 | set_of_books_id |
1206 | split_amount |
1207 | |
1208 | OPTIONAL |
1209 | customer_id |
1210 | |
1211 | RETURNS |
1212 | Total Count of transaction with amounts under the split amount |
1213 | |
1214 | NOTES |
1215 | |
1216 | EXAMPLE |
1217 | |
1218 | MODIFICATION HISTORY |
1219 | 28-Aug-98 Victoria Smith Created. |
1220 | |
1221 +-------------------------------------------------------------------------*/
1222
1223 FUNCTION comp_below_count(pstart_date IN DATE,
1224 pas_of_date IN DATE,
1225 psob_id IN NUMBER,
1226 psplit IN NUMBER,
1227 pcust_id IN NUMBER DEFAULT -1) RETURN NUMBER IS
1228 below_count NUMBER;
1229
1230 BEGIN
1231
1232 /* Get currency details to calculate acctd_amount_due_original */
1233
1234 Get_Currency_Details( psob_id, 222 );
1235
1236 SELECT
1237 SUM(trx)
1238 INTO
1239 below_count
1240 FROM (
1241 SELECT
1242 1 trx
1243 FROM
1244 ar_payment_schedules ps
1245 WHERE
1246 ps.gl_date BETWEEN pstart_date AND pas_of_date
1247 and ps.class IN ('INV', 'DM', 'CB', 'DEP' )
1248 and ps.payment_schedule_id <> -1
1249 and (pcust_id = -1 OR
1250 (pcust_id <> -1 AND ps.customer_id = pcust_id))
1251 GROUP BY ps.customer_trx_id
1252 HAVING SUM(
1253 arpcurr.functional_amount(
1254 ps.amount_due_original,
1255 curr_rec.base_currency,
1256 nvl(ps.exchange_rate,1),
1257 curr_rec.base_precision,
1258 curr_rec.base_min_acc_unit
1259 ) +
1260 Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
1261 ) < psplit
1262 );
1263
1264 return nvl(below_count,0);
1265
1266 EXCEPTION
1267 WHEN NO_DATA_FOUND THEN return(0);
1268
1269 END comp_below_count;
1270
1271 END ARP_COLL_IND;