1 PACKAGE BODY ARP_STAGED_DUNNING as
2 /* $Header: ARCUSDLB.pls 115.10 2002/11/15 02:28:51 anukumar ship $ */
3
4 /*-------------------------------------------------------------------------+
5 | |
6 | PRIVATE VARIABLES |
7 | |
8 +-------------------------------------------------------------------------*/
9 -- to store SELECT Statement
10 sql_statement VARCHAR2(2000);
11
12
13 /*----------------------------------------------------------------------------*
14 | PUBLIC FUNCTION |
15 | staged_dunning( site IN site_type |
16 | ,parameter IN parameter_type |
17 | ,letter_tab IN OUT NOCOPY letter_id_tab |
18 | ,letter_count IN OUT NOCOPY NUMBER |
19 | ,single_letter_flag IN VARCHAR2) RETURN BOOLEAN AS |
20 | |
21 | |
22 | DESCRIPTION |
23 | Given an input list of parameters for a customer/site, get dunning let- |
24 | ters for on which the open payment schedules of this customer/site will|
25 | be printed. Return FALSE if NO letter could be found |
26 | |
27 | |
28 | MODIFIES |
29 | letter_tab store dunning letter information |
30 | letter_count count dunning_letters found |
31 | |
32 | RETURNS |
33 | TRUE - at least 1 dunning letter found |
34 | FALSE - no dunning letter found |
35 | |
36 | |
37 | HISTORY |
38 | 7/31/95 Christine Vogel Created |
39 | 8/7/96 Paul Rooney Modified to accept single_letter_flag |
40 | 8/25/96 Simon Jou Modified for staged dunning/credit memo |
41 *----------------------------------------------------------------------------*/
42
43 FUNCTION staged_dunning( site IN site_type
44 ,parameter IN parameter_type
45 ,letter_tab IN OUT NOCOPY letter_id_tab
46 ,letter_count IN OUT NOCOPY NUMBER
47 ,single_letter_flag IN VARCHAR2) RETURN BOOLEAN AS
48
49 -- define array to store distinct dunning levels
50 TYPE leveltab IS TABLE OF ar_payment_schedules.staged_dunning_level%TYPE
51 INDEX BY BINARY_INTEGER;
52 level_tab leveltab;
53
54 -- define cursor for the dynamic SELECT of the open payment schedules
55 ps_cursor integer;
56 sum_cursor integer;
57
58 -- define variables to select values in
59 ps_id NUMBER(15);
60 inv_code VARCHAR2(15);
61
62 curr_code VARCHAR2(15);
63 adr NUMBER;
64 sum_adr NUMBER;
65 days_late NUMBER;
66 st_dunning_level NUMBER(3);
67 dunning_level_override_date DATE;
68
69 -- define other variables
70 current_dun_date DATE;
71 ok_flag BOOLEAN;
72 prev_currency VARCHAR2(15);
73 dun_flag BOOLEAN;
74 dun_ok BOOLEAN;
75 change_flag BOOLEAN;
76 i NUMBER(4);
77 j NUMBER(4);
78 min_dun_amount NUMBER;
79 min_dun_inv_amount NUMBER;
80 help_level NUMBER(2);
81 ignore INTEGER;
82 id NUMBER;
83 error_message varchar2(2000);
84 level_tab_dummy leveltab;
85 letter_tab_dummy letter_id_tab;
86 t NUMBER(4);
87 sql_statement_s VARCHAR2(2000);
88
89 BEGIN
90
91 -- initialize the array
92 level_tab := level_tab_dummy;
93 letter_tab := letter_tab_dummy;
94
95 -- build the SELECT statement to retrieve the open payment schedules for cus-
96 -- tomer/site which are not Guarantees and are not unapproved adjustments
97 sql_statement :=
98 'SELECT ps.payment_schedule_id' ||
99 ',ps.invoice_currency_code' ||
100 ',ps.amount_due_remaining' ||
101 ',fnd_date.canonical_to_date(:b_dun_date) - ps.due_date ' ||
102 ',ps.staged_dunning_level' ||
103 ',ps.dunning_level_override_date' ||
104 ' FROM ar_payment_schedules ps';
105
106 sql_statement_s :=
107 'SELECT ps.invoice_currency_code, ' ||
108 ' sum(ps.amount_due_remaining) ' ||
109 ' FROM ar_payment_schedules ps';
110
111 if parameter.transaction_type_from is not null
112 OR
113 parameter.transaction_type_to is not null then
114 sql_statement := sql_statement ||
115 ', ra_cust_trx_types t ';
116 sql_statement_s := sql_statement_s ||
117 ', ra_cust_trx_types t ';
118
119 end if;
120
121 /* Refered to bug # 436336
122 Transaction types such as CM or PMT are also needed in deciding a
123 dunning letter.
124 The idea is that: besides just taking the individual INV type trx into
125 account, the sum of INV, CM and PMT of the same currency should be greater
126 or equal than minimum dunning amount for single dunning letter option.
127 Otherwise, the program may pick up some single trx which appears that it
128 should be dunned, and use that as an indication to choose a dunning letter.
129 But in actual fact, the program should have calculated the sum of the trxs
130 (INV, PMT and CM) and found out NOCOPY the sum would have been lesser than min
131 dunning amount and that letter should never have be chosen.
132 */
133
134 sql_statement := sql_statement ||
135 ' WHERE ps.customer_id = :b_customer_id ' ||
136 ' AND ps.status = ''OP''' ||
137 ' AND nvl(ps.exclude_from_dunning_flag,''N'') = ''N''' ||
138 ' AND ps.class NOT in (''GUAR'')';
139 sql_statement_s := sql_statement_s ||
140 ' WHERE ps.customer_id = :b_customer_id ' ||
141 ' AND ps.status = ''OP''' ||
142 ' AND nvl(ps.exclude_from_dunning_flag,''N'') = ''N''' ||
143 ' AND ps.class NOT in (''GUAR'')';
144 -- bug # 436336: changed form the below:
145 -- ' AND ps.class NOT in (''GUAR'', ''CM'', ''PMT'')';
146 /* 2107939
147 On Account and Unapplied Payments should only be considered if the
148 Dunning Letter Set has the 'Include Unapplied Receipts' field set to
149 Yes. Added the If condition below.
150 Start of bug fix for 2107939. */
151
152 IF site.include_payments = 'N' THEN
153 sql_statement := sql_statement ||
154 ' AND ps.class NOT IN (''PMT'')';
155 sql_statement_s := sql_statement_s ||
156 ' AND ps.class NOT IN (''PMT'')';
157 END IF;
158 /* End of bug fix for 2107939.*/
159 if site.grace_days = 'Y' then
160 sql_statement := sql_statement ||
161 ' AND nvl(ps.trx_date,fnd_date.canonical_to_date(:b_dun_date))+'||
162 ' to_number(:b_payment_grace_days) ' ||
163 ' <= fnd_date.canonical_to_date(:b_dun_date)';
164 sql_statement_s := sql_statement_s ||
165 ' AND nvl(ps.trx_date,fnd_date.canonical_to_date(:b_dun_date))+'||
166 ' to_number(:b_payment_grace_days) ' ||
167 ' <= fnd_date.canonical_to_date(:b_dun_date)';
168
169 else
170 sql_statement := sql_statement ||
171 ' AND nvl(ps.trx_date,fnd_date.canonical_to_date(:b_dun_date))'||
172 ' <= fnd_date.canonical_to_date(:b_dun_date)';
173 sql_statement_s := sql_statement_s ||
174 ' AND nvl(ps.trx_date,fnd_date.canonical_to_date(:b_dun_date))'||
175 ' <= fnd_date.canonical_to_date(:b_dun_date)';
176
177 end if;
178
179 if parameter.transaction_type_from is not null
180 OR
181 parameter.transaction_type_to is not null
182 then
183 sql_statement := sql_statement ||
184 ' AND ps.cust_trx_type_id = t.cust_trx_type_id ';
185 sql_statement_s := sql_statement_s ||
186 ' AND ps.cust_trx_type_id = t.cust_trx_type_id ';
187
188 end if;
189
190 if parameter.transaction_type_from is not null
191 AND
192 parameter.transaction_type_to is not null
193 AND
194 parameter.transaction_type_from = parameter.transaction_type_to
195 then
196 sql_statement := sql_statement ||
197 ' AND t.name = :b_transaction_type_from ';
198 sql_statement_s := sql_statement_s ||
199 ' AND t.name = :b_transaction_type_from ';
200 else
201 if parameter.transaction_type_from is not null
202 then
203 sql_statement := sql_statement ||
204 ' AND t.name||'''' >= :b_transaction_type_from ';
205 sql_statement_s := sql_statement_s ||
206 ' AND t.name||'''' >= :b_transaction_type_from ';
207 end if;
208 if parameter.transaction_type_to is not null
209 then
210 sql_statement := sql_statement ||
211 ' AND t.name||'''' <= :b_transaction_type_to ';
212 sql_statement_s := sql_statement_s ||
213 ' AND t.name||'''' <= :b_transaction_type_to ';
214 end if;
215 end if;
216
217 if site.dunning_level = 'S' then
218 sql_statement := sql_statement ||
219 ' AND ps.customer_site_use_id = :b_site_use_id';
220 sql_statement_s := sql_statement_s ||
221 ' AND ps.customer_site_use_id = :b_site_use_id';
222 end if;
223
224 if site.dun_disputed_items = 'N' then
225 sql_statement := sql_statement ||
226 ' AND nvl(ps.amount_in_dispute, 0) = 0 ' ||
227 ' AND NOT EXISTS(' ||
228 'SELECT ''Unapproved Adjustments ''' ||
229 ' FROM ar_adjustments adj' ||
230 ' WHERE adj.payment_schedule_id = ps.payment_schedule_id'||
231 ' AND adj.status NOT IN (''A'',''R'',''U''))';
232 sql_statement_s := sql_statement_s ||
233 ' AND nvl(ps.amount_in_dispute, 0) = 0 ' ||
234 ' AND NOT EXISTS(' ||
235 'SELECT ''Unapproved Adjustments ''' ||
236 ' FROM ar_adjustments adj' ||
237 ' WHERE adj.payment_schedule_id = ps.payment_schedule_id'||
238 ' AND adj.status NOT IN (''A'',''R'',''U''))';
239 end if;
240
241 sql_statement_s := sql_statement_s ||
242 ' AND ps.invoice_currency_code = :curr';
243
244 sql_statement := sql_statement ||
245 ' ORDER BY ps.invoice_currency_code';
246 sql_statement_s := sql_statement_s ||
247 ' GROUP BY ps.invoice_currency_code' ||
248 ' ORDER BY ps.invoice_currency_code';
249 -- bug #436336 : changed from the below:
250 -- ' ORDER BY ps.staged_dunning_level';
251
252 ps_cursor := dbms_sql.open_cursor;
253
254 dbms_sql.parse(ps_cursor, sql_statement, dbms_sql.v7 );
255
256 -- bind variables into placeholder
257 dbms_sql.bind_variable(ps_cursor,':b_dun_date',parameter.dun_date);
258 if site.dunning_level = 'S' then
259 dbms_sql.bind_variable(ps_cursor,':b_site_use_id',site.site_use_id);
260 end if;
261 dbms_sql.bind_variable(ps_cursor,':b_customer_id',site.customer_id);
262 if site.grace_days = 'Y' then
263 dbms_sql.bind_variable(ps_cursor,':b_payment_grace_days',
264 site.payment_grace_days);
265 end if;
266
267 if parameter.transaction_type_from is not null
268 AND
269 parameter.transaction_type_to is not null
270 AND
271 parameter.transaction_type_from = parameter.transaction_type_to
272 then
273 dbms_sql.bind_variable(ps_cursor,':b_transaction_type_from',
274 parameter.transaction_type_from );
275 else
276 if parameter.transaction_type_from is not null
277 then
278 dbms_sql.bind_variable(ps_cursor,':b_transaction_type_from',
279 parameter.transaction_type_from );
280 end if;
281 if parameter.transaction_type_to is not null
282 then
283 dbms_sql.bind_variable(ps_cursor,':b_transaction_type_to',
284 parameter.transaction_type_to );
285 end if;
286 end if;
287
288 -- specify columns to be selected in
289 dbms_sql.define_column(ps_cursor,1,ps_id);
290 dbms_sql.define_column(ps_cursor,2,curr_code,15);
291 dbms_sql.define_column(ps_cursor,3,adr);
292 dbms_sql.define_column(ps_cursor,4,days_late);
293 dbms_sql.define_column(ps_cursor,5,st_dunning_level);
294 dbms_sql.define_column(ps_cursor,6,dunning_level_override_date);
295
296 sum_adr := 0;
297 dun_ok := FALSE;
298 ignore := dbms_sql.execute(ps_cursor);
299 prev_currency := '0';
300 current_dun_date := fnd_date.canonical_to_date(parameter.dun_date);
301
302 <<Open_Payment_Loop>>
303 LOOP
304 if dbms_sql.fetch_rows(ps_cursor) <= 0 then
305 exit Open_Payment_Loop;
306 end if;
307
308 -- get fetched values from the variables
309 dbms_sql.column_value(ps_cursor,1,ps_id);
310 dbms_sql.column_value(ps_cursor,2,curr_code);
311 dbms_sql.column_value(ps_cursor,3,adr);
312 dbms_sql.column_value(ps_cursor,4,days_late);
313 dbms_sql.column_value(ps_cursor,5,st_dunning_level);
314 dbms_sql.column_value(ps_cursor,6,dunning_level_override_date);
315
316 if curr_code <> prev_currency then
317 ok_flag := ARP_STAGED_DUNNING.get_cpa(site,curr_code,min_dun_amount,
318 min_dun_inv_amount);
319 if ok_flag = FALSE then
320 exit Open_Payment_Loop;
321 end if;
322 prev_currency := curr_code;
323 end if;
324
325 dun_flag := FALSE;
326 if ((site.grace_days = 'N' and days_late >= 0 )
327 or
328 (site.grace_days = 'Y' and days_late >= site.payment_grace_days ))
329 AND
330 ( adr >= min_dun_inv_amount ) then
331 -- bug # 436336: changed from below:
332 -- ( adr >= min_dun_inv_amount ) then
333
334 dun_flag := ARP_STAGED_DUNNING.get_new_dunning_level(ps_id
335 ,st_dunning_level
336 ,current_dun_date
337 ,dunning_level_override_date
338 ,days_late
339 ,site.letter_set_id);
340
341 -- For bug# 436336
342 -- for this ps_id find out NOCOPY if the sum of INV, PMT or CM of the same currency
343 -- is <0. if so, then don't insert this transaction into the level tab;
344 -- Only will be done if a new dunning level has been assigned.
345
346 if (dun_flag = TRUE) then
347
348 -- Open cursor
349 sum_cursor := dbms_sql.open_cursor;
350 -- Parse cursor
351 dbms_sql.parse(sum_cursor, sql_statement_s, dbms_sql.v7 );
352 -- Bind variables
353
354 dbms_sql.bind_variable(sum_cursor,':b_dun_date',parameter.dun_date);
355 if site.dunning_level = 'S' then
356 dbms_sql.bind_variable(sum_cursor,':b_site_use_id',site.site_use_id);
357 end if;
358 dbms_sql.bind_variable(sum_cursor,':b_customer_id',site.customer_id);
359 if site.grace_days = 'Y' then
360 dbms_sql.bind_variable(sum_cursor,':b_payment_grace_days',
361 site.payment_grace_days);
362 end if;
363
364 if parameter.transaction_type_from is not null
365 AND
366 parameter.transaction_type_to is not null
367 AND
368 parameter.transaction_type_from = parameter.transaction_type_to
369 then
370 dbms_sql.bind_variable(sum_cursor,':b_transaction_type_from',
371 parameter.transaction_type_from );
372 else
373 if parameter.transaction_type_from is not null
374 then
375 dbms_sql.bind_variable(sum_cursor,':b_transaction_type_from',
376 parameter.transaction_type_from );
377 end if;
378 if parameter.transaction_type_to is not null
379 then
380 dbms_sql.bind_variable(sum_cursor,':b_transaction_type_to',
381 parameter.transaction_type_to );
382 end if;
383 end if;
384 dbms_sql.bind_variable(sum_cursor,':curr',
385 curr_code );
386
387 -- Define the output column
388 dbms_sql.define_column(sum_cursor, 1, inv_code, 15);
389 dbms_sql.define_column(sum_cursor, 2, sum_adr);
390
391 -- Execute
392 ignore := dbms_sql.execute(sum_cursor);
393
394 -- Fetch a row
395 if dbms_sql.fetch_rows(sum_cursor) <= 0 then
396 exit Open_Payment_Loop;
397 end if;
398
399 -- Get column's value
400 dbms_sql.column_value(sum_cursor, 1, inv_code);
401 dbms_sql.column_value(sum_cursor, 2, sum_adr);
402
403 -- Close cursor
404 dbms_sql.close_cursor(sum_cursor);
405 end if;
406
407 if (sum_adr < min_dun_amount) then
408 dun_flag := FALSE;
409 end if;
410
411 -- end of # bug 436336
412
413 -- CONTINUE processing only if open payment schedule should be dunned
414 -- get min_dun_amount and min_dun_inv_amount only if new dunning level
415 -- is in the range selected by user( parameter )
416
417 if dun_flag = TRUE
418 AND
419 st_dunning_level >= parameter.dunning_level_from
420 AND
421 st_dunning_level <= parameter.dunning_level_to then
422
423 -- save distinct dunning level into array
424 change_flag := FALSE;
425 i := 1;
426 <<Level_Loop>>
427 LOOP
428 BEGIN
429 if level_tab(i) = st_dunning_level then
430 change_flag := TRUE;
431 exit Level_Loop;
432 end if;
433 i := i + 1;
434 EXCEPTION
435 when NO_DATA_FOUND then
436 exit Level_Loop;
437 END;
438 END LOOP Level_Loop;
439 if change_flag = FALSE then -- level not yet in array
440 level_tab(i) := st_dunning_level;
441 end if;
442 end if; -- end of level in range
443 end if; -- end of open payment dunning
444 if dun_flag = TRUE then
445 dun_ok := TRUE;
446 end if;
447 --exit Open_Payment_Loop;
448 END LOOP Open_Payment_Loop;
449
450 -- return FALSE if no open payment schedule found or function get_cpa returned
451 -- FALSE
452 if ok_flag = FALSE OR dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
453 if dbms_sql.last_row_count <= 0 OR dun_ok = FALSE then
454 letter_count := 0;
455 dbms_sql.close_cursor(ps_cursor);
456 return(TRUE);
457 else
458 dbms_sql.close_cursor(ps_cursor);
459 return( FALSE );
460 end if;
461 end if;
462 dbms_sql.close_cursor(ps_cursor);
463
464
465 -- sort the array of dunning levels
466 change_flag := TRUE;
467 <<While_loop>>
468 WHILE( change_flag ) LOOP
469 i := 1;
470 change_flag := FALSE;
471 <<Change_Loop>>
472 LOOP
473 BEGIN
474 if level_tab(i) > level_tab(i+1) then
475 help_level := level_tab(i);
476 level_tab(i) := level_tab(i+1);
477 level_tab(i+1) := help_level;
478 change_flag := TRUE;
479 end if;
480 i := i + 1;
481 EXCEPTION
482 when NO_DATA_FOUND then
483 exit Change_Loop;
484 END;
485 END LOOP Change_Loop;
486 END LOOP While_Loop;
487
488
489 -- for each dunning level from array, find corresponding dunning letter
490 -- MAX_STAGED DUNNING distinct dunning letters will be supported. If more
491 -- return FALSE;
492
493 if nvl(single_letter_flag,'N') <> 'Y' then
494 i := 1;
495 end if;
496 <<Letter_Loop>>
497 LOOP
498 -- because NO_DATA_FOUND can be raised from SELECT and from table
499 -- define this block for table and next one for SELECT
500 BEGIN
501 help_level := level_tab(i);
502 EXCEPTION
503 when NO_DATA_FOUND then
504 exit Letter_Loop;
505 END ;
506 BEGIN
507 SELECT dlsl.dunning_letter_id
508 INTO id
509 FROM ar_dunning_letter_set_lines dlsl
510 WHERE dlsl.dunning_letter_set_id = site.letter_set_id
511 AND help_level BETWEEN dlsl.range_of_dunning_level_from
512 AND dlsl.range_of_dunning_level_to;
513
514 change_flag := FALSE;
515 j := 1;
516 <<Distinct_Letter_Loop>>
517 LOOP
518 BEGIN
519
520 if letter_tab(j) = id then
521 change_flag := TRUE;
522 exit Distinct_Letter_Loop;
523 end if;
524 j := j + 1;
525 EXCEPTION
526 when NO_DATA_FOUND then
527 exit Distinct_Letter_Loop;
528 END;
529 END LOOP Distinct_Level_Loop;
530 if change_flag = FALSE then -- letter not yet in array
531
532 letter_tab(j) := id;
533 end if;
534
535 i := i +1;
536 EXCEPTION
537 when OTHERS then
538 return( FALSE );
539 END;
540 END LOOP Letter_Loop;
541
542 letter_count := j ;
543
544 if letter_count > MAX_STAGED_DUNNING then
545 return(FALSE);
546 end if;
547
548 return( TRUE );
549 END staged_dunning;
550
551 /*----------------------------------------------------------------------------*
552 | PUBLIC FUNCTION |
553 | get_cpa( site IN site_type |
554 | ,curr_code IN VARCHAR2 |
555 | ,min_dun_amount IN OUT NOCOPY NUMBER |
556 | ,min_dun_inv_amount IN OUT NOCOPY NUMBER ) RETURN BOOLEAN |
557 | |
558 | DESCRIPTION |
559 | for a given customer/site and currency get the minimum dunning amount |
560 | and minimum dunning invoice amount |
561 | If the site has a CUSTOMER dunning level, then only the customer level |
562 | profile will be queried. If SITE then the site level profile will be |
563 | examined first. If this does not exist, then the values will be taken |
564 | from customer level. If this does not exist the amounts will be 0 |
565 | |
566 | MODIFIES |
567 | min_dun_amount store the amount found |
568 | min_dun_inv_amount store amount found |
569 | |
570 | RETURNS |
571 | TRUE if no error occured |
572 | FALSE else |
573 | |
574 | |
575 | KNOWN BUGS |
576 | |
577 | |
578 | HISTORY |
579 | 7/31/95 Christine Vogel Created |
580 | |
581 *----------------------------------------------------------------------------*/
582
583 FUNCTION get_cpa(site IN site_type
584 , curr_code IN VARCHAR2
585 , min_dun_amount OUT NOCOPY NUMBER
586 , min_dun_inv_amount OUT NOCOPY NUMBER ) RETURN BOOLEAN AS
587 BEGIN
588 if site.dunning_level = 'S' then
589 SELECT nvl(site_cpa.min_dunning_amount,
590 nvl(cust_cpa.min_dunning_amount, 0 ))
591 ,nvl(site_cpa.min_dunning_invoice_amount,
592 nvl(cust_cpa.min_dunning_invoice_amount, 0 ))
593 INTO min_dun_amount
594 ,min_dun_inv_amount
595 FROM hz_customer_profiles cust_cp
596 ,hz_cust_profile_amts cust_cpa
597 ,hz_customer_profiles site_cp
598 ,hz_cust_profile_amts site_cpa
599 where CUST_CP.CUST_ACCOUNT_ID = site.customer_id
600 AND cust_cp.site_use_id IS NULL
601 AND cust_cpa.cust_account_profile_id(+)= cust_cp.cust_account_profile_id
602 AND cust_cpa.currency_code(+) = curr_code
603 AND site_cp.cust_account_id(+) = cust_cp.cust_account_id
604 AND site_cp.site_use_id(+) = site.site_use_id
605 AND site_cpa.cust_account_profile_id(+)= site_cp.cust_account_profile_id
606 AND site_cpa.currency_code(+) = curr_code;
607 else
608 /* bug 2362943 : depending on profile value change where profile amounts are read from */
609
610 if FND_PROFILE.value( 'AR_USE_STATEMENTS_AND_DUNNING_SITE_PROFILE' ) = 'N' then
611
612 SELECT nvl(cust_cpa.min_dunning_amount, 0)
613 ,nvl(cust_cpa.min_dunning_invoice_amount, 0)
614 INTO min_dun_amount
615 ,min_dun_inv_amount
616 FROM hz_customer_profiles cust_cp
617 ,hz_cust_profile_amts cust_cpa
618 WHERE cust_cp.cust_account_id = site.customer_id
619 AND cust_cp.site_use_id IS NULL
620 AND cust_cpa.cust_account_profile_id(+)
621 = cust_cp.cust_account_profile_id
622 AND cust_cpa.currency_code(+) = curr_code;
623 else
624 SELECT NVL(min_dunning_amount, 0) ,
625 NVL(min_dunning_invoice_amount, 0)
626 INTO min_dun_amount,
627 min_dun_inv_amount
628 FROM hz_cust_profile_amts
629 WHERE CUST_ACCOUNT_PROFILE_ID =
630 (SELECT cust_account_profile_id
631 FROM hz_customer_profiles
632 WHERE site_use_id = arpt_sql_func_util.get_bill_id(site.site_use_id))
633 AND currency_code = curr_code
634 AND CUST_ACCOUNT_ID = site.customer_id;
635 end if;
636 end if;
637 return( TRUE );
638
639 EXCEPTION
640 when OTHERS then
641 return( FALSE );
642 END get_cpa;
643
644
645 /*----------------------------------------------------------------------------*
646 | PUBLIC FUNCTION |
647 | get_new_dunning_level( ps_id IN NUMBER |
648 | ,staged_dunning_level IN OUT NOCOPY NUMBER |
649 | ,current_dun_date IN DATE |
650 | ,dunning_level_override_date IN DATE |
651 | ,days_late IN NUMBER |
652 | ,o_letter_set_id IN NUMBER ) RETURN BOOLEAN |
653 | |
654 | DESCRIPTION |
655 | for a payment schedule get the new dunning level. The user has the |
656 | possibility to update the dunning level. Thatfore , dunning level can |
657 | not be increased by 1 with every printing of a dunning letter |
658 | |
659 | |
660 | RETURNS |
661 | TRUE if open payment should be dunned, else return FALSE |
662 | |
663 | MODIFIES |
664 | staged_dunning_level |
665 | |
666 | |
667 | KNOWN BUGS |
668 | |
669 | |
670 | HISTORY |
671 | 7/31/95 Christine Vogel Created |
672 | 8/25/96 Simon Jou Modified for staged dunning/credit memo |
673 *----------------------------------------------------------------------------*/
674
675
676 FUNCTION get_new_dunning_level(ps_id IN NUMBER
677 ,staged_dunning_level IN OUT NOCOPY NUMBER
678 ,current_dun_date IN DATE
679 ,dunning_level_override_date IN DATE
680 ,days_late IN NUMBER
681 ,o_letter_set_id IN NUMBER ) RETURN BOOLEAN AS
682
683 last_print_date DATE;
684 min_dunning_days NUMBER;
685 previously_dunned BOOLEAN := TRUE;
686
687 -- cursor to get latest print for a given open payment schedule
688 -- Note: the new one is not in the ar_correspondence_pay_sched yet,
689 -- so this is the latest one for the existing records.
690
691 CURSOR last_print IS
692 SELECT c.correspondence_date
693 FROM ar_correspondence_pay_sched cp
694 ,ar_correspondences c
695 ,ar_dunning_letter_set_lines dlsl
696 WHERE cp.payment_schedule_id = ps_id
697 AND c.preliminary_flag = 'N'
698 AND cp.staged_dunning_level is NOT NULL
699 AND dlsl.dunning_letter_set_id = c.reference1
700 AND dlsl.dunning_letter_id = c.reference2
701 AND cp.correspondence_id = c.correspondence_id
702 ORDER BY c.correspondence_date DESC;
703
704 BEGIN
705
706 OPEN last_print;
707 FETCH last_print INTO last_print_date;
708
709 -- if payment not yet dunned, cursor exception %NOTFOUND
710 if last_print%NOTFOUND then
711 previously_dunned := FALSE;
712 end if;
713 CLOSE last_print;
714
715 SELECT min_days_between_dunning
716 INTO min_dunning_days
717 FROM ar_dunning_letter_set_lines
718 WHERE dunning_letter_set_id = o_letter_set_id
719 AND range_of_dunning_level_from <= (NVL(staged_dunning_level, 0)+1)
720 AND range_of_dunning_level_to >= (NVL(staged_dunning_level, 0)+1);
721
722 -- If the open payment was previously dunned then
723 -- see if its dunning level has been overriden, if not, see the
724 -- last print date plus the min dunning days < current_dunning_days.
725 -- If WAS overriden, then both the override date AND the last print date
726 -- have to satisfy the above criteria at the same time.
727 -- If not previously dunned then
728 -- see if minimum dunning days <= days_late
729 --
730 -- If any of the above condition is true, increment the dunning level and
731 -- return true; else return false and dunning stays the same.
732
733 IF previously_dunned THEN
734 IF dunning_level_override_date IS NULL THEN
735 IF last_print_date + min_dunning_days <= current_dun_date THEN
736 staged_dunning_level := nvl(staged_dunning_level,0) +1;
737 return(TRUE);
738 ELSE
739 return(FALSE);
740 END IF;
741 ELSE
742 IF (dunning_level_override_date + min_dunning_days <= current_dun_date)
743 AND (last_print_date + min_dunning_days <= current_dun_date) THEN
744 staged_dunning_level := nvl(staged_dunning_level,0) +1;
745 return(TRUE);
746 ELSE
747 return(FALSE);
748 END IF;
749 END IF;
750 ELSE
751 IF min_dunning_days <= days_late THEN
752 staged_dunning_level := nvl(staged_dunning_level,0) +1;
753 return(TRUE);
754 ELSE
755 return(FALSE);
756 END IF;
757 END IF;
758
759 EXCEPTION
760 when NO_DATA_FOUND then
761 return(FALSE);
762
763 END get_new_dunning_level;
764
765
766 END ARP_STAGED_DUNNING;
[Home] [Help]