[Home] [Help]
PACKAGE BODY: APPS.AR_BILLS_MAINTAIN_STATUS_PUB
Source
1 PACKAGE BODY AR_BILLS_MAINTAIN_STATUS_PUB AS
2 /* $Header: ARBRSVEB.pls 115.12 2003/11/04 16:43:34 mraymond ship $ */
3
4
5 /* =======================================================================
6 | Bills Receivable status constants
7 * ======================================================================*/
8
9 C_INCOMPLETE CONSTANT VARCHAR2(30) := 'INCOMPLETE';
10 C_PENDING_REMITTANCE CONSTANT VARCHAR2(30) := 'PENDING_REMITTANCE';
11 C_PENDING_ACCEPTANCE CONSTANT VARCHAR2(30) := 'PENDING_ACCEPTANCE';
12 C_MATURED_PEND_RISK_ELIM CONSTANT VARCHAR2(30) := 'MATURED_PEND_RISK_ELIMINATION';
13 C_CLOSED CONSTANT VARCHAR2(30) := 'CLOSED';
14 C_REMITTED CONSTANT VARCHAR2(30) := 'REMITTED';
15 C_PROTESTED CONSTANT VARCHAR2(30) := 'PROTESTED';
16 C_FACTORED CONSTANT VARCHAR2(30) := 'FACTORED';
17 C_ENDORSED CONSTANT VARCHAR2(30) := 'ENDORSED';
18
19
20 /* =======================================================================
21 | Bills Receivable event constants
22 * ======================================================================*/
23
24 C_MATURITY_DATE CONSTANT VARCHAR2(30) := 'MATURITY_DATE';
25 C_MATURITY_DATE_UPDATED CONSTANT VARCHAR2(30) := 'MATURITY_DATE_UPDATED';
26 C_FORMATTED CONSTANT VARCHAR2(30) := 'FORMATTED';
27 C_COMPLETED CONSTANT VARCHAR2(30) := 'COMPLETED';
28 C_ACCEPTED CONSTANT VARCHAR2(30) := 'ACCEPTED';
29 C_SELECTED_REMITTANCE CONSTANT VARCHAR2(30) := 'SELECTED_REMITTANCE';
30 C_DESELECTED_REMITTANCE CONSTANT VARCHAR2(30) := 'DESELECTED_REMITTANCE';
31 C_CANCELLED CONSTANT VARCHAR2(30) := 'CANCELLED';
32 C_RISK_ELIMINATED CONSTANT VARCHAR2(30) := 'RISK_ELIMINATED';
33 C_RISK_UNELIMINATED CONSTANT VARCHAR2(30) := 'RISK_UNELIMINATED';
34 C_RECALLED CONSTANT VARCHAR2(30) := 'RECALLED';
35 C_EXCHANGED CONSTANT VARCHAR2(30) := 'EXCHANGED';
36 C_RELEASE_HOLD CONSTANT VARCHAR2(30) := 'RELEASE_HOLD';
37
38
39 /* =======================================================================
40 | Bills Receivable action constants
41 * ======================================================================*/
42
43 C_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
44 C_ACCEPT CONSTANT VARCHAR2(30) := 'ACCEPT';
45 C_COMPLETE_ACC CONSTANT VARCHAR2(30) := 'COMPLETE_ACC';
46 C_UNCOMPLETE CONSTANT VARCHAR2(30) := 'UNCOMPLETE';
47 C_HOLD CONSTANT VARCHAR2(30) := 'HOLD';
48 C_UNHOLD CONSTANT VARCHAR2(30) := 'RELEASE HOLD';
49 C_SELECT_REMIT CONSTANT VARCHAR2(30) := 'SELECT_REMIT';
50 C_DESELECT_REMIT CONSTANT VARCHAR2(30) := 'DESELECT_REMIT';
51 C_CANCEL CONSTANT VARCHAR2(30) := 'CANCEL';
52 C_UNPAID CONSTANT VARCHAR2(30) := 'UNPAID';
53 C_REMIT_STANDARD CONSTANT VARCHAR2(30) := 'REMIT_STANDARD';
54 C_FACTORE CONSTANT VARCHAR2(30) := 'FACTORE';
55 C_FACTORE_RECOURSE CONSTANT VARCHAR2(30) := 'FACTORE_RECOURSE';
56 C_RECALL CONSTANT VARCHAR2(30) := 'RECALL';
57 C_ELIMINATE_RISK CONSTANT VARCHAR2(30) := 'RISK ELIMINATION';
58 C_UNELIMINATE_RISK CONSTANT VARCHAR2(30) := 'REESTABLISH RISK';
59 C_PROTEST CONSTANT VARCHAR2(30) := 'PROTEST';
60 C_ENDORSE CONSTANT VARCHAR2(30) := 'ENDORSE';
61 C_ENDORSE_RECOURSE CONSTANT VARCHAR2(30) := 'ENDORSE_RECOURSE';
62 C_RESTATE CONSTANT VARCHAR2(30) := 'RESTATE';
63 C_EXCHANGE CONSTANT VARCHAR2(30) := 'EXCHANGE';
64 C_EXCHANGE_COMPLETE CONSTANT VARCHAR2(30) := 'EXCHANGE_COMPLETE';
65 C_EXCHANGE_UNCOMPLETE CONSTANT VARCHAR2(30) := 'EXCHANGE_UNCOMPLETE';
66 C_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
67 C_APPROVE_REMIT CONSTANT VARCHAR2(30) := 'REMITTANCE APPROVAL';
68
69 /* =======================================================================
70 | Bills Receivable remittance method code constants
71 * ======================================================================*/
72
73 C_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
74 C_FACTORING CONSTANT VARCHAR2(30) := 'FACTORING';
75
76
77 /*==============================================================================+
78 | FUNCTION |
79 | Is_Payment_Schedule_Reduced |
80 | |
81 | DESCRIPTION |
82 | Validates that the original amount is equal to the Amount Due Remaining |
83 | |
84 +==============================================================================*/
85
86 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
87
88 FUNCTION Is_Payment_Schedule_Reduced ( p_ps_rec IN ar_payment_schedules%ROWTYPE) RETURN BOOLEAN
89 IS
90
91 BEGIN
92
93 IF PG_DEBUG in ('Y', 'C') THEN
94 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_Payment_Schedule_Reduced()+');
95 END IF;
96
97 IF (p_ps_rec.amount_due_original = p_ps_rec.amount_due_remaining)
98 THEN
99 RETURN (FALSE);
100 ELSE
101 IF PG_DEBUG in ('Y', 'C') THEN
102 arp_util.debug('Is_Payment_Schedule_Reduced: ' || '>>>>>>>>>> Original amount is not equal to the remaining amount');
103 arp_util.debug('Is_Payment_Schedule_Reduced: ' || 'Original amount : ' || p_ps_rec.amount_due_original);
104 arp_util.debug('Is_Payment_Schedule_Reduced: ' || 'Remaining amount : ' || p_ps_rec.amount_due_remaining);
105 END IF;
106 RETURN (TRUE);
107 END IF;
108
109 IF PG_DEBUG in ('Y', 'C') THEN
110 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_Payment_Schedule_Reduced()-');
111 END IF;
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 IF PG_DEBUG in ('Y', 'C') THEN
116 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_Payment_Schedule_Reduced () ');
117 END IF;
118 RAISE;
119
120 END Is_Payment_Schedule_Reduced;
121
122
123
124 /*==============================================================================+
125 | FUNCTION |
126 | Is_BR_Reserved |
127 | |
128 | DESCRIPTION |
129 | Validates that the BR is not reserved : |
130 | -> reserved columns of the Payment Schedule should contain null values |
131 | |
132 +==============================================================================*/
133
134 FUNCTION Is_BR_Reserved (p_ps_rec IN ar_payment_schedules%ROWTYPE) RETURN BOOLEAN
135 IS
136
137 BEGIN
138
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Reserved()+');
141 END IF;
142
143
144 IF (p_ps_rec.reserved_type IS NULL AND p_ps_rec.reserved_value IS NULL)
145 THEN
146 IF PG_DEBUG in ('Y', 'C') THEN
147 arp_util.debug ('Is_BR_Reserved: ' || '>>>>>>>>>>> The BR is not reserved');
148 END IF;
149 RETURN (FALSE);
150 ELSE
151 IF PG_DEBUG in ('Y', 'C') THEN
152 arp_util.debug ('Is_BR_Reserved: ' || 'p_ps_rec.reserved_type : ' || p_ps_rec.reserved_type);
153 arp_util.debug ('Is_BR_Reserved: ' || 'p_ps_rec.reserved_value : ' || p_ps_rec.reserved_value);
154 END IF;
155 RETURN (TRUE);
156 END IF;
157
158 IF PG_DEBUG in ('Y', 'C') THEN
159 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Reserved()-');
160 END IF;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 IF PG_DEBUG in ('Y', 'C') THEN
165 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Reserved () ');
166 END IF;
167 RAISE;
168
169 END Is_BR_Reserved;
170
171
172
173 /*==============================================================================+
174 | FUNCTION |
175 | Is_BR_Hold |
176 | |
177 | DESCRIPTION |
178 | Validates that BR is on hold |
179 | |
180 +==============================================================================*/
181
182 FUNCTION Is_BR_Hold (p_ps_rec IN ar_payment_schedules%ROWTYPE) RETURN BOOLEAN
183 IS
184
185 BEGIN
186
187 IF PG_DEBUG in ('Y', 'C') THEN
188 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Hold()+');
189 END IF;
190
191 IF (p_ps_rec.reserved_type = 'USER')
192 THEN
193 IF PG_DEBUG in ('Y', 'C') THEN
194 arp_util.debug ('Is_BR_Hold: ' || '>>>>>>>>>>> The BR is on Hold');
195 END IF;
196 RETURN (TRUE);
197 ELSE
198 RETURN (FALSE);
199 END IF;
200
201 IF PG_DEBUG in ('Y', 'C') THEN
202 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Hold()-');
203 END IF;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF PG_DEBUG in ('Y', 'C') THEN
208 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Hold () ');
209 END IF;
210 RAISE;
211
212 END Is_BR_Hold;
213
214
215
216 /*==============================================================================+
217 | PROCEDURE Find_Prev_posted_trh |
218 | |
219 | DESCRIPTION |
220 | This function fetches the Previous posted transaction history record |
221 | |
222 | SCOPE - PUBLIC |
223 | |
224 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
225 | arp_util.debug |
226 | |
227 | ARGUMENTS : |
228 | IN OUT NOCOPY p_trh_rec - BR transaction history record |
229 | |
230 | |
231 | |
232 | MODIFICATION HISTORY |
233 | 04-JUL-2000 Tien TRAN Created |
234 | |
235 +==============================================================================*/
236
237 PROCEDURE Find_Prev_posted_trh (p_trh_rec IN OUT NOCOPY ar_transaction_history%ROWTYPE)
238
239 IS
240
241 /*------------------------------------------------------------+
242 | Cursor to fetch Previous posted transaction history record |
243 +------------------------------------------------------------*/
244
245 CURSOR Prev_trh_cur IS
246 SELECT *
247 FROM ar_transaction_history
248 WHERE (postable_flag = 'Y' OR nvl(event,'1') = 'MATURITY_DATE')
249 CONNECT BY PRIOR prv_trx_history_id = transaction_history_id
250 START WITH transaction_history_id = p_trh_rec.transaction_history_id
251 ORDER BY transaction_history_id desc;
252
253 BEGIN
254
255 IF PG_DEBUG in ('Y', 'C') THEN
256 arp_util.debug ('AR_BILLS_MAINTAIN_STATUS_PUB.Find_Prev_posted_trh()+');
257 END IF;
258
259 /*------------------------------------------------------+
260 | Fetch Previous posted transaction history record |
261 +-------------------------------------------------------*/
262
263 OPEN Prev_trh_cur;
264 FETCH Prev_trh_cur INTO p_trh_rec;
265
266 IF (Prev_trh_cur%NOTFOUND)
267 THEN
268 IF PG_DEBUG in ('Y', 'C') THEN
269 arp_util.debug ('Find_Prev_posted_trh: ' || 'Find_Previous transaction history record cannot be found' );
270 END IF;
271 END IF;
272
273 CLOSE Prev_trh_cur;
274
275 IF PG_DEBUG in ('Y', 'C') THEN
276 arp_util.debug ('AR_BILLS_MAINTAIN_STATUS_PUB.Find_Prev_posted_trh()-');
277 END IF;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_util.debug ('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Find_Prev_posted_trh ' || SQLERRM);
283 END IF;
284 IF (Prev_trh_cur%ISOPEN)
285 THEN
286 CLOSE Prev_trh_cur;
287 END IF;
288 RAISE;
289
290 END Find_Prev_posted_trh;
291
292
293
294 /*==============================================================================+
295 | PROCEDURE Find_Last_relevant_trh |
296 | |
297 | DESCRIPTION |
298 | This function fetches the last transaction history record with a |
299 | relevant event (different from MATURITY_DATE_UPDATED and FORMATTED) |
300 | |
301 | SCOPE - PUBLIC |
302 | |
303 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
304 | arp_util.debug |
305 | |
306 | ARGUMENTS : |
307 | IN OUT NOCOPY p_trh_rec - BR transaction history record |
308 | |
309 | |
310 | |
311 | MODIFICATION HISTORY |
312 | 04-JUL-2000 Tien TRAN Created |
313 | |
314 +==============================================================================*/
315
316 PROCEDURE Find_Last_relevant_trh (p_trh_rec IN OUT NOCOPY ar_transaction_history%ROWTYPE)
317
318 IS
319
320 /*--------------------------------------------------------------+
321 | Cursor to fetch last relevant transaction history record |
322 +--------------------------------------------------------------*/
323
324 CURSOR Prev_trh_cur IS
325 SELECT *
326 FROM ar_transaction_history
327 WHERE nvl(event,'A') NOT IN (C_MATURITY_DATE_UPDATED, C_FORMATTED)
328 CONNECT BY PRIOR prv_trx_history_id = transaction_history_id
329 START WITH transaction_history_id = p_trh_rec.transaction_history_id
330 ORDER BY transaction_history_id desc;
331
332 BEGIN
333
334 IF PG_DEBUG in ('Y', 'C') THEN
335 arp_util.debug ('AR_BILLS_MAINTAIN_STATUS_PUB.Find_Last_relevant_trh()+');
336 END IF;
337
338 /*------------------------------------------------------+
339 | Fetch last relevant transaction history record |
340 +-------------------------------------------------------*/
341
342 OPEN Prev_trh_cur;
343 FETCH Prev_trh_cur INTO p_trh_rec;
344
345 IF (Prev_trh_cur%NOTFOUND)
346 THEN
347 IF PG_DEBUG in ('Y', 'C') THEN
348 arp_util.debug ('Find_Last_relevant_trh: ' || 'Find_Previous transaction history record cannot be found' );
349 END IF;
350 END IF;
351
352 IF PG_DEBUG in ('Y', 'C') THEN
353 arp_util.debug ('Find_Last_relevant_trh: ' || 'Previous Relevant Status : ' || p_trh_rec.status);
354 arp_util.debug ('Find_Last_relevant_trh: ' || 'Previous Relevant Event : ' || p_trh_rec.event);
355 END IF;
356
357 CLOSE Prev_trh_cur;
358
359 IF PG_DEBUG in ('Y', 'C') THEN
360 arp_util.debug ('AR_BILLS_MAINTAIN_STATUS_PUB.Find_Last_relevant_trh()-');
361 END IF;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 IF PG_DEBUG in ('Y', 'C') THEN
366 arp_util.debug ('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Find_Last_relevant_trh ()' || SQLERRM);
367 END IF;
368 IF (Prev_trh_cur%ISOPEN)
369 THEN
370 CLOSE Prev_trh_cur;
371 END IF;
372 RAISE;
373
374 END Find_Last_relevant_trh;
375
376
377
378 /*==============================================================================+
379 | FUNCTION |
380 | Is_BR_Remit_Selected |
381 | |
382 | DESCRIPTION |
383 | Validates that the BR is selected for remittance |
384 | |
385 +==============================================================================*/
386
387 FUNCTION Is_BR_Remit_Selected (p_trh_id IN ar_transaction_history.transaction_history_id%TYPE) RETURN BOOLEAN
388 IS
389
390 l_trh_rec ar_transaction_history%ROWTYPE;
391
392 BEGIN
393
394 IF PG_DEBUG in ('Y', 'C') THEN
395 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Remit_Selected()+');
396 END IF;
397
398 /*----------------------------------------------+
399 | Find Current Relevant History Information |
400 +-----------------------------------------------*/
401
402 l_trh_rec.transaction_history_id := p_trh_id;
403 Find_last_relevant_trh (l_trh_rec);
404
405 IF (l_trh_rec.event = C_SELECTED_REMITTANCE)
406 THEN
407 IF PG_DEBUG in ('Y', 'C') THEN
408 arp_util.debug ('Is_BR_Remit_Selected: ' || '>>>>>>>>>>> The BR is Selected for Remittance');
409 END IF;
410 RETURN (TRUE);
411 ELSE
412 RETURN (FALSE);
413 END IF;
414
415 IF PG_DEBUG in ('Y', 'C') THEN
416 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Remit_Selected()-');
417 END IF;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF PG_DEBUG in ('Y', 'C') THEN
422 arp_util.debug ('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Remit_Selected () ');
423 arp_util.debug ('Is_BR_Remit_Selected: ' || ' p_trh_id : ' || p_trh_id);
424 END IF;
425 RAISE;
426
427 END Is_BR_Remit_Selected;
428
429
430
431 /*==============================================================================+
432 | FUNCTION |
433 | Is_BR_Risk_Eliminated |
434 | |
435 | DESCRIPTION |
436 | Validates that the BR has been risk eliminated |
437 | |
438 +==============================================================================*/
439
440 FUNCTION Is_BR_Risk_Eliminated (p_trh_id IN ar_transaction_history.transaction_history_id%TYPE) RETURN BOOLEAN
441 IS
442
443 l_trh_rec ar_transaction_history%ROWTYPE;
444 l_prev_trh_rec ar_transaction_history%ROWTYPE;
445
446
447 BEGIN
448
449
450 IF PG_DEBUG in ('Y', 'C') THEN
451 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Risk_Eliminated()+');
452 END IF;
453
454 l_trh_rec.transaction_history_id := p_trh_id;
455 Find_Last_relevant_trh (l_trh_rec);
456
457 IF (l_trh_rec.event = C_RISK_ELIMINATED)
458 THEN
459 l_prev_trh_rec.transaction_history_id := l_trh_rec.prv_trx_history_id;
460 Find_Last_relevant_trh (l_prev_trh_rec);
461
462 IF (l_prev_trh_rec.status = C_MATURED_PEND_RISK_ELIM)
463 THEN
464 RETURN (TRUE);
465 ELSE
466 /*----------------------------------------------+
467 | Risk Restoration on Closed Endorsed BR |
468 | is not activated currently |
469 +-----------------------------------------------*/
470
471 RETURN (FALSE);
472 END IF;
473 END IF;
474
475 RETURN (FALSE);
476
477 IF PG_DEBUG in ('Y', 'C') THEN
478 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Risk_Eliminated()-');
479 END IF;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF PG_DEBUG in ('Y', 'C') THEN
484 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Risk_Eliminated () ');
485 arp_util.debug('Is_BR_Risk_Eliminated: ' || 'p_trh_id : ' || p_trh_id);
486 END IF;
487 RAISE;
488
489 END Is_BR_Risk_Eliminated;
490
491
492
493 /*==============================================================================+
494 | FUNCTION |
495 | Is_BR_Matured |
496 | |
497 | DESCRIPTION |
498 | Check if the BR is matured |
499 | |
500 +==============================================================================*/
501
502 FUNCTION Is_BR_Matured (p_maturity_date IN DATE) RETURN BOOLEAN
503 IS
504
505 l_trh_rec ar_transaction_history%ROWTYPE;
506
507 BEGIN
508
509 IF PG_DEBUG in ('Y', 'C') THEN
510 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Matured()+');
511 END IF;
512
513
514 IF trunc(p_maturity_date) <= trunc(SYSDATE)
515 THEN
516 IF PG_DEBUG in ('Y', 'C') THEN
517 arp_util.debug ('Is_BR_Matured: ' || 'The BR is matured');
518 END IF;
519 RETURN (TRUE);
520 ELSE
521 IF PG_DEBUG in ('Y', 'C') THEN
522 arp_util.debug ('Is_BR_Matured: ' || 'The BR is not matured yet');
523 END IF;
524 RETURN (FALSE);
525 END IF;
526
527 IF PG_DEBUG in ('Y', 'C') THEN
528 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Matured()-');
529 END IF;
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 IF PG_DEBUG in ('Y', 'C') THEN
534 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Matured () ');
535 arp_util.debug('Is_BR_Matured: ' || 'p_maturity_date : ' || p_maturity_date);
536 END IF;
537 RAISE;
538
539 END Is_BR_Matured;
540
541
542
543 /*==============================================================================+
544 | FUNCTION |
545 | Is_Receipt_Cleared |
546 | |
547 | DESCRIPTION |
548 | Validates that the latest receipt for the BR has been cleared |
549 | |
550 +==============================================================================*/
551
552 FUNCTION Is_Receipt_Cleared (p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) RETURN BOOLEAN
553 IS
554
555 l_cr_id ar_receivable_applications.cash_receipt_id%TYPE;
556 l_crh_rec ar_cash_receipt_history%ROWTYPE;
557
558 BEGIN
559
560 IF PG_DEBUG in ('Y', 'C') THEN
561 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_Receipt_Cleared()+');
562 END IF;
563
564 SELECT max(cash_receipt_id)
565 INTO l_cr_id
566 FROM ar_receivable_applications
567 WHERE applied_customer_trx_id = p_customer_trx_id
568 OR link_to_customer_trx_id = p_customer_trx_id;
569
570 ARP_CR_HISTORY_PKG.fetch_f_crid(l_cr_id, l_crh_rec);
571
572 IF (l_crh_rec.status <> 'CLEARED')
573 THEN
574 RETURN (FALSE);
575 ELSE
576 RETURN (TRUE);
577 END IF;
578
579
580 IF PG_DEBUG in ('Y', 'C') THEN
581 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_Receipt_Cleared()-');
582 END IF;
583
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 IF PG_DEBUG in ('Y', 'C') THEN
587 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_Receipt_Cleared () ');
588 arp_util.debug('Is_Receipt_Cleared: ' || 'No receipt application has been found for the BR');
589 arp_util.debug('Is_Receipt_Cleared: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
590 END IF;
591 RAISE;
592
593 WHEN OTHERS THEN
594 IF PG_DEBUG in ('Y', 'C') THEN
595 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_Receipt_Cleared () ');
596 arp_util.debug('Is_Receipt_Cleared: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
597 END IF;
598 RAISE;
599
600 END Is_Receipt_Cleared;
601
602
603
604 /*==============================================================================+
605 | FUNCTION |
606 | Is_BR_Paid_In_One_Time |
607 | |
608 | DESCRIPTION |
609 | Validates that the BR was paid by one receipt or one endorsement adj |
610 | |
611 +==============================================================================*/
612
613 FUNCTION Is_BR_Paid_In_One_Time (p_trh_rec IN ar_transaction_history%ROWTYPE ,
614 p_ps_rec IN ar_payment_schedules%ROWTYPE ) RETURN BOOLEAN
615 IS
616
617 l_trx_rec RA_CUSTOMER_TRX%ROWTYPE;
618 l_ra_id AR_RECEIVABLE_APPLICATIONS.receivable_application_id%TYPE;
619 l_ra_rec AR_RECEIVABLE_APPLICATIONS%ROWTYPE;
620 l_prev_trh_rec AR_TRANSACTION_HISTORY%ROWTYPE;
621
622 BEGIN
623
624 IF PG_DEBUG in ('Y', 'C') THEN
625 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Paid_In_One_Time()+');
626 END IF;
627
628 /*----------------------------------------------+
629 | Find Previous Relevant History Information |
630 +-----------------------------------------------*/
631
632 l_prev_trh_rec.transaction_history_id := p_trh_rec.prv_trx_history_id;
633
634 Find_last_relevant_trh (l_prev_trh_rec);
635
636
637 /*----------------------------------------------+
638 | Unpaid : Endorsed and then Closed |
639 | ******** With or without Recourse |
640 +-----------------------------------------------*/
641
642 IF (l_prev_trh_rec.status = C_ENDORSED)
643 THEN
644 -- Only Endorsement for the whole amount is allowed, so return TRUE
645 RETURN (TRUE);
646
647
648 /*----------------------------------------------+
649 | Unpaid : Remitted and then Closed |
650 | ******** Factored and then Closed |
651 +-----------------------------------------------*/
652
653 ELSIF (p_trh_rec.event in (C_CLOSED, C_RISK_ELIMINATED))
654 THEN
655
656
657 SELECT max(receivable_application_id)
658 INTO l_ra_id
659 FROM ar_receivable_applications
660 WHERE applied_customer_trx_id = p_trh_rec.customer_trx_id;
661
662 IF PG_DEBUG in ('Y', 'C') THEN
663 arp_util.debug ('Is_BR_Paid_In_One_Time: ' || 'l_ra_id : ' || l_ra_id);
664 END IF;
665
666 arp_app_pkg.fetch_p (l_ra_id, l_ra_rec);
667
668
669 -- Validate that the BR was paid by a single receipt
670
671 IF (l_ra_rec.amount_applied <> p_ps_rec.amount_due_original)
672 THEN
673 IF PG_DEBUG in ('Y', 'C') THEN
674 arp_util.debug ('Is_BR_Paid_In_One_Time: ' || '>>>>>>>>>> The BR cannot be Unpaid, because the BR was not paid by a single receipt');
675 arp_util.debug ('Is_BR_Paid_In_One_Time: ' || 'p_ps_rec.amount_due_original : ' || p_ps_rec.amount_due_original);
676 arp_util.debug ('Is_BR_Paid_In_One_Time: ' || 'l_ra_rec.amount_applied : ' || l_ra_rec.amount_applied);
677 END IF;
678 RETURN (FALSE);
679 ELSE
680 RETURN (TRUE);
681 END IF;
682
683 END IF;
684
685 RETURN (FALSE);
686
687 IF PG_DEBUG in ('Y', 'C') THEN
688 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Paid_In_One_Time()-');
689 END IF;
690
691 EXCEPTION
692 WHEN NO_DATA_FOUND THEN
693 IF PG_DEBUG in ('Y', 'C') THEN
694 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Paid_In_One_Time () ');
695 arp_util.debug('Is_BR_Paid_In_One_Time: ' || 'No receipt application could be found for the BR');
696 END IF;
697 RETURN (FALSE);
698
699 WHEN OTHERS THEN
700 IF PG_DEBUG in ('Y', 'C') THEN
701 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_BR_Paid_In_One_Time () ');
702 END IF;
703 RAISE;
704
705 END Is_BR_Paid_In_One_Time;
706
707
708
709 /*==============================================================================+
710 | FUNCTION |
711 | Activities_Exist |
712 | |
713 | DESCRIPTION |
714 | Test if the BR has activities : receipt applications or adjustments |
715 | |
716 +===============================================================================*/
717
718
719 FUNCTION Activities_Exist (p_customer_trx_id IN NUMBER) RETURN BOOLEAN
720 IS
721
722 l_rec_count NUMBER;
723 l_adj_count NUMBER;
724
725
726 BEGIN
727
728 IF PG_DEBUG in ('Y', 'C') THEN
729 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Activities_Exist()+');
730 END IF;
731
732 /*----------------------------------------------+
733 | Check if the BR has receipt applications |
734 +-----------------------------------------------*/
735
736 SELECT count(*)
737 INTO l_rec_count
738 FROM ar_receivable_applications
739 WHERE applied_customer_trx_id = p_customer_trx_id
740 OR link_to_customer_trx_id = p_customer_trx_id;
741
742
743 /*----------------------------------------------+
744 | Check if the BR has adjustments against it |
745 +-----------------------------------------------*/
746
747 SELECT count(*)
748 INTO l_adj_count
749 FROM ar_adjustments
750 WHERE customer_trx_id = p_customer_trx_id;
751
752
753 IF (l_adj_count > 0) OR (l_rec_count > 0)
754 THEN
755 IF PG_DEBUG in ('Y', 'C') THEN
756 arp_util.debug ('Activities_Exist: ' || 'The BR has activities');
757 arp_util.debug ('Activities_Exist: ' || 'l_rec_count : ' || l_rec_count);
758 arp_util.debug ('Activities_Exist: ' || 'l_adj_count : ' || l_adj_count);
759 END IF;
760 RETURN (TRUE);
761 ELSE
762 RETURN (FALSE);
763 END IF;
764
765 IF PG_DEBUG in ('Y', 'C') THEN
766 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Activities_Exist()-');
767 END IF;
768
769 EXCEPTION
770 WHEN Others THEN
771 IF PG_DEBUG in ('Y', 'C') THEN
772 arp_util.debug('>>>>>>>>>> EXCEPTION : Activities_Exist () ');
773 END IF;
774 RAISE;
775
776 END Activities_Exist;
777
778
779
780 /*==============================================================================+
781 | FUNCTION |
782 | Has_been_posted |
783 | |
784 | DESCRIPTION |
785 | Checks whether the BR has been posted to GL or not |
786 | |
787 +==============================================================================*/
788
789 FUNCTION Has_been_posted (p_customer_trx_id IN NUMBER) RETURN BOOLEAN IS
790
791 l_count NUMBER;
792
793 BEGIN
794 IF PG_DEBUG in ('Y', 'C') THEN
795 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Has_been_posted()+');
796 END IF;
797
798 /*----------------------------------------------+
799 | If posting_control_id is <> -3, the BR has |
800 | been posted. |
801 +-----------------------------------------------*/
802
803 SELECT count(*)
804 INTO l_count
805 FROM AR_TRANSACTION_HISTORY
806 WHERE customer_trx_id = p_customer_trx_id
807 AND posting_control_id <> -3;
808
809 IF (l_count > 0)
810 THEN
811 IF PG_DEBUG in ('Y', 'C') THEN
812 arp_util.debug ('Has_been_posted: ' || 'The BR has been posted');
813 END IF;
814 RETURN TRUE;
815 ELSE
816 RETURN FALSE;
817 END IF;
818
819 IF PG_DEBUG in ('Y', 'C') THEN
820 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Has_been_posted()-');
821 END IF;
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 IF PG_DEBUG in ('Y', 'C') THEN
826 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Has_been_posted () ');
827 arp_util.debug('Has_been_posted: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
828 END IF;
829 RAISE;
830
831 END Has_been_posted;
832
833
834
835 /*===========================================================================+
836 | FUNCTION |
837 | Is_Acceptance_Required |
838 | |
839 | DESCRIPTION |
840 | This function determines if the BR requires acceptance or not |
841 | |
842 +===========================================================================*/
843
844 FUNCTION Is_Acceptance_Required (p_cust_trx_type_id IN ra_customer_trx.cust_trx_type_id%TYPE) RETURN BOOLEAN
845 IS
846
847 l_signed_flag VARCHAR2(1);
848 l_drawee_issued_flag VARCHAR2(1);
849
850 BEGIN
851
852 IF PG_DEBUG in ('Y', 'C') THEN
853 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_Acceptance_Required()+');
854 END IF;
855
856 SELECT signed_flag , drawee_issued_flag
857 INTO l_signed_flag, l_drawee_issued_flag
858 FROM ra_cust_trx_types
859 WHERE cust_trx_type_id = p_cust_trx_type_id;
860
861 IF PG_DEBUG in ('Y', 'C') THEN
862 arp_util.debug('Is_Acceptance_Required: ' || 'l_signed_flag : ' || l_signed_flag);
863 arp_util.debug('Is_Acceptance_Required: ' || 'l_drawee_issued_flag : ' || l_drawee_issued_flag);
864 END IF;
865
866 /*----------------------------------------------+
867 | The BR requires acceptance if : |
868 | signed_flag = 'Y' |
869 | and drawee_issued_flag = 'N' |
870 +-----------------------------------------------*/
871
872 IF (l_signed_flag = 'Y' AND l_drawee_issued_flag <> 'Y') THEN
873 return(true);
874 ELSE
875 return(false);
876 END IF;
877
878 IF PG_DEBUG in ('Y', 'C') THEN
879 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.Is_Acceptance_Required()-');
880 END IF;
881
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 IF PG_DEBUG in ('Y', 'C') THEN
885 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_Acceptance_Required () ');
886 arp_util.debug('Is_Acceptance_Required: ' || '>>>>>>>>>> Invalid Transaction Type');
887 arp_util.debug('Is_Acceptance_Required: ' || ' p_cust_trx_type_id = ' || p_cust_trx_type_id);
888 END IF;
889 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_INVALID_TRX_TYPE');
890 app_exception.raise_exception;
891
892
893 WHEN OTHERS THEN
894 IF PG_DEBUG in ('Y', 'C') THEN
895 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.Is_Acceptance_Required () ');
896 arp_util.debug('Is_Acceptance_Required: ' || 'p_cust_trx_type_id = ' || p_cust_trx_type_id);
897 END IF;
898 RAISE;
899
900 END Is_Acceptance_Required;
901
902
903
904 /*==============================================================================+
905 | FUNCTION |
906 | BR_Has_Assignment |
907 | |
908 | DESCRIPTION |
909 | Checks whether the BR has assignments or not |
910 | |
911 +==============================================================================*/
912
913 FUNCTION BR_Has_Assignment (p_customer_trx_id IN NUMBER) RETURN BOOLEAN IS
914
915 l_count NUMBER;
916
917 BEGIN
918 IF PG_DEBUG in ('Y', 'C') THEN
919 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.BR_Has_Assignment()+');
920 END IF;
921
922 SELECT count(*)
923 INTO l_count
924 FROM RA_CUSTOMER_TRX_LINES
925 WHERE customer_trx_id = p_customer_trx_id;
926
927 IF (l_count > 0)
928 THEN
929 RETURN (TRUE);
930 ELSE
931 RETURN (FALSE);
932 END IF;
933
934 IF PG_DEBUG in ('Y', 'C') THEN
935 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.BR_Has_Assignment()-');
936 END IF;
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 IF PG_DEBUG in ('Y', 'C') THEN
941 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.BR_Has_Assignment () ');
942 arp_util.debug('BR_Has_Assignment: ' || 'p_customer_trx_id = ' || p_customer_trx_id);
943 END IF;
944 RAISE;
945
946 END BR_Has_Assignment;
947
948
949
950 /*==============================================================================+
951 | PROCEDURE |
952 | Validate_actions |
953 | |
954 | DESCRIPTION |
955 | For a given BR, validates which actions are allowed |
956 | 13-DEC-02 VCRISOST Bug 2702173 : ps_id lost in call to fetch_p |
957 | declare local variable l_ps_id |
958 | |
959 +==============================================================================*/
960
961
962 PROCEDURE Validate_actions ( p_customer_trx_id IN NUMBER ,
963 p_complete_flag OUT NOCOPY VARCHAR2 ,
964 p_uncomplete_flag OUT NOCOPY VARCHAR2 ,
965 p_accept_flag OUT NOCOPY VARCHAR2 ,
966 p_cancel_flag OUT NOCOPY VARCHAR2 ,
967 p_select_remit_flag OUT NOCOPY VARCHAR2 ,
968 p_deselect_remit_flag OUT NOCOPY VARCHAR2 ,
969 p_approve_remit_flag OUT NOCOPY VARCHAR2 ,
970 p_hold_flag OUT NOCOPY VARCHAR2 ,
971 p_unhold_flag OUT NOCOPY VARCHAR2 ,
972 p_recall_flag OUT NOCOPY VARCHAR2 ,
973 p_eliminate_flag OUT NOCOPY VARCHAR2 ,
974 p_uneliminate_flag OUT NOCOPY VARCHAR2 ,
975 p_unpaid_flag OUT NOCOPY VARCHAR2 ,
976 p_protest_flag OUT NOCOPY VARCHAR2 ,
977 p_endorse_flag OUT NOCOPY VARCHAR2 ,
978 p_restate_flag OUT NOCOPY VARCHAR2 ,
979 p_exchange_flag OUT NOCOPY VARCHAR2 ,
980 p_delete_flag OUT NOCOPY VARCHAR2 )
981
982 IS
983
984 l_trh_rec AR_TRANSACTION_HISTORY%ROWTYPE;
985 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
986 l_trx_rec RA_CUSTOMER_TRX%ROWTYPE;
987 l_ps_id NUMBER;
988
989 BEGIN
990
991 IF PG_DEBUG in ('Y', 'C') THEN
992 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.validate_actions()+');
993 END IF;
994
995 p_complete_flag := 'N';
996 p_uncomplete_flag := 'N';
997 p_accept_flag := 'N';
998 p_cancel_flag := 'N';
999 p_select_remit_flag := 'N';
1000 p_deselect_remit_flag := 'N';
1001 p_approve_remit_flag := 'N';
1002 p_hold_flag := 'N';
1003 p_unhold_flag := 'N';
1004 p_recall_flag := 'N';
1005 p_eliminate_flag := 'N';
1006 p_uneliminate_flag := 'N';
1007 p_unpaid_flag := 'N';
1008 p_protest_flag := 'N';
1009 p_endorse_flag := 'N';
1010 p_restate_flag := 'N';
1011 p_exchange_flag := 'N';
1012 p_delete_flag := 'N';
1013
1014
1015 /*----------------------------------------------+
1016 | Validate the BR Identifier |
1017 +-----------------------------------------------*/
1018
1019 AR_BILLS_CREATION_VAL_PVT.Validate_Customer_Trx_ID (p_customer_trx_id);
1020 ARP_CT_PKG.fetch_p (l_trx_rec, p_customer_trx_id);
1021
1022
1023 /*----------------------------------------------+
1024 | Fetch The current transaction history record |
1025 +-----------------------------------------------*/
1026
1027 l_trh_rec.customer_trx_id := p_customer_trx_id;
1028 ARP_TRANSACTION_HISTORY_PKG.fetch_f_trx_id (l_trh_rec);
1029
1030
1031 /*----------------------------------------------+
1032 | Fetch The BR payment schedule information |
1033 +-----------------------------------------------*/
1034
1035 IF (l_trh_rec.status NOT IN (C_INCOMPLETE, C_PENDING_ACCEPTANCE, C_CANCELLED))
1036 THEN
1037 AR_BILLS_CREATION_LIB_PVT.Get_Payment_Schedule_Id (p_customer_trx_id, l_ps_rec.payment_schedule_id);
1038
1039 -- bug 2702173 : declare local variable to hold ps_id and pass it to fetch_p
1040 l_ps_id := l_ps_rec.payment_schedule_id;
1041
1042 arp_ps_pkg.fetch_p(l_ps_id, l_ps_rec);
1043 ELSE
1044 IF PG_DEBUG in ('Y', 'C') THEN
1045 arp_util.debug ('Validate_actions: ' || 'There is no payment schedule for the BR');
1046 END IF;
1047 END IF;
1048
1049
1050 /*----------------------------------------------+
1051 | INCOMPLETE |
1052 +-----------------------------------------------*/
1053
1054 IF (l_trh_rec.status = C_INCOMPLETE)
1055 THEN
1056
1057 IF (BR_Has_Assignment(p_customer_trx_id))
1058 THEN
1059 p_complete_flag := 'Y';
1060 END IF;
1061
1062 IF NOT (Has_Been_Posted(p_customer_trx_id))
1063 THEN
1064 p_delete_flag := 'Y';
1065 END IF;
1066
1067
1068
1069 /*----------------------------------------------+
1070 | PENDING_ACCEPTANCE |
1071 +-----------------------------------------------*/
1072
1073 ELSIF (l_trh_rec.status = C_PENDING_ACCEPTANCE)
1074 THEN
1075 p_accept_flag := 'Y';
1076 p_cancel_flag := 'Y';
1077 p_uncomplete_flag := 'Y';
1078
1079
1080
1081 /*----------------------------------------------+
1082 | PENDING_REMITTANCE |
1083 +-----------------------------------------------*/
1084
1085 ELSIF (l_trh_rec.status = C_PENDING_REMITTANCE)
1086 THEN
1087
1088 IF NOT (Is_BR_Reserved (l_ps_rec)) AND NOT (Is_Payment_Schedule_Reduced (l_ps_rec))
1089 THEN
1090 p_cancel_flag := 'Y';
1091 p_endorse_flag := 'Y';
1092 END IF;
1093
1094
1095 IF (Is_BR_Remit_Selected(l_trh_rec.transaction_history_id))
1096 THEN
1097 p_deselect_remit_flag := 'Y';
1098 p_approve_remit_flag := 'Y';
1099 END IF;
1100
1101
1102 IF Not(Is_BR_Reserved (l_ps_rec))
1103 THEN
1104 p_hold_flag := 'Y';
1105 p_unpaid_flag := 'Y';
1106 p_select_remit_flag := 'Y';
1107 END IF;
1108
1109
1110 IF (Is_BR_Hold(l_ps_rec))
1111 THEN
1112 p_unhold_flag := 'Y';
1113 END IF;
1114
1115
1116 IF (NOT Is_Acceptance_Required (l_ps_rec.cust_trx_type_id) AND
1117 NOT Has_been_posted (p_customer_trx_id) AND
1118 NOT Is_Payment_Schedule_Reduced (l_ps_rec) AND
1119 NOT Activities_Exist (p_customer_trx_id) AND
1120 NOT Is_BR_Reserved (l_ps_rec)
1121 )
1122 THEN
1123 p_uncomplete_flag := 'Y';
1124 END IF;
1125
1126
1127
1128 /*----------------------------------------------+
1129 | STANDARD REMITTED |
1130 +-----------------------------------------------*/
1131
1132 ELSIF (l_trh_rec.status = C_REMITTED)
1133 THEN
1134 p_recall_flag := 'Y';
1135
1136 IF Is_BR_Matured(l_ps_rec.due_date)
1137 THEN
1138 p_unpaid_flag := 'Y';
1139 END IF;
1140
1141
1142
1143 /*----------------------------------------------+
1144 | FACTORED REMITTED |
1145 +-----------------------------------------------*/
1146
1147 ELSIF (l_trh_rec.status = C_FACTORED)
1148 THEN
1149 p_recall_flag := 'Y';
1150
1151
1152
1153 /*----------------------------------------------+
1154 | MATURED PENDING RISK ELIMINATION |
1155 +-----------------------------------------------*/
1156
1157 ELSIF (l_trh_rec.status = C_MATURED_PEND_RISK_ELIM)
1158 THEN
1159 p_unpaid_flag := 'Y';
1160
1161 IF (Is_Receipt_Cleared (p_customer_trx_id))
1162 THEN
1163 p_eliminate_flag := 'Y';
1164 END IF;
1165
1166
1167
1168 /*----------------------------------------------+
1169 | CLOSED |
1170 +-----------------------------------------------*/
1171
1172 ELSIF (l_trh_rec.status = C_CLOSED)
1173 THEN
1174
1175 IF (Is_BR_Risk_Eliminated(l_trh_rec.transaction_history_id))
1176 THEN
1177 p_uneliminate_flag := 'Y';
1178 END IF;
1179
1180 IF (l_trh_rec.event <> C_EXCHANGED AND Is_BR_Paid_In_One_Time (l_trh_rec, l_ps_rec))
1181 AND (Is_BR_Matured (l_ps_rec.due_date))
1182 THEN
1183 p_unpaid_flag := 'Y';
1184 END IF;
1185
1186
1187
1188 /*----------------------------------------------+
1189 | UNPAID |
1190 +-----------------------------------------------*/
1191
1192 ELSIF (l_trh_rec.status = C_UNPAID)
1193 THEN
1194
1195 p_protest_flag := 'Y';
1196 p_restate_flag := 'Y';
1197
1198 IF NOT (Is_BR_Reserved (l_ps_rec)) AND NOT (Is_Payment_Schedule_Reduced (l_ps_rec))
1199 THEN
1200 p_cancel_flag := 'Y';
1201 p_endorse_flag := 'Y';
1202 p_exchange_flag := 'Y';
1203 END IF;
1204
1205 IF Not(Is_BR_Reserved (l_ps_rec))
1206 THEN
1207 p_hold_flag := 'Y';
1208 p_select_remit_flag := 'Y';
1209 END IF;
1210
1211
1212 IF (Is_BR_Hold(l_ps_rec))
1213 THEN
1214 p_unhold_flag := 'Y';
1215 END IF;
1216
1217
1218
1219 /*----------------------------------------------+
1220 | PROTESTED |
1221 +-----------------------------------------------*/
1222
1223 ELSIF (l_trh_rec.status = C_PROTESTED)
1224 THEN
1225 p_unpaid_flag := 'Y';
1226
1227
1228
1229 /*----------------------------------------------+
1230 | ENDORSED |
1231 +-----------------------------------------------*/
1232
1233 ELSIF (l_trh_rec.status = C_ENDORSED)
1234 THEN
1235 p_recall_flag := 'Y';
1236 -- p_eliminate_flag := 'Y';
1237
1238 IF Is_BR_Matured(l_ps_rec.due_date)
1239 THEN
1240 p_unpaid_flag := 'Y';
1241 END IF;
1242
1243
1244 END IF;
1245
1246
1247 IF PG_DEBUG in ('Y', 'C') THEN
1248 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.validate_actions()-');
1249 END IF;
1250
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 IF PG_DEBUG in ('Y', 'C') THEN
1254 arp_util.debug('>>>>>>>>>> EXCEPTION : validate_actions () ');
1255 arp_util.debug('Validate_actions: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
1256 END IF;
1257 RAISE;
1258
1259 END validate_actions;
1260
1261
1262 /*==============================================================================+
1263 | PROCEDURE |
1264 | New_Status_Event |
1265 | |
1266 | DESCRIPTION |
1267 | For a given BR and an action, returns the new status and event |
1268 | |
1269 +==============================================================================*/
1270
1271
1272 PROCEDURE New_Status_Event ( p_trx_rec IN ra_customer_trx%ROWTYPE ,
1273 p_action IN VARCHAR2 ,
1274 p_new_status OUT NOCOPY VARCHAR2 ,
1275 p_new_event OUT NOCOPY VARCHAR2 )
1276 IS
1277
1278 l_trh_rec ar_transaction_history%ROWTYPE;
1279 l_prev_trh_rec ar_transaction_history%ROWTYPE;
1280
1281 BEGIN
1282
1283 IF PG_DEBUG in ('Y', 'C') THEN
1284 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.New_Status_Event()+');
1285 END IF;
1286
1287 p_new_status := NULL;
1288 p_new_event := NULL;
1289
1290 /*----------------------------------------------+
1291 | Find the Last Relevant History Information |
1292 +-----------------------------------------------*/
1293
1294 SELECT transaction_history_id
1295 INTO l_trh_rec.transaction_history_id
1296 FROM ar_transaction_history
1297 WHERE customer_trx_id = p_trx_rec.customer_trx_id
1298 AND current_record_flag = 'Y';
1299
1300 Find_Last_relevant_trh (l_trh_rec) ;
1301
1302 l_prev_trh_rec.transaction_history_id := l_trh_rec.prv_trx_history_id;
1303
1304 /*----------------------------------------------+
1305 | COMPLETE |
1306 +-----------------------------------------------*/
1307
1308 IF (p_action = C_COMPLETE)
1309 THEN
1310 IF (is_acceptance_required(p_trx_rec.cust_trx_type_id))
1311 THEN
1312 p_new_status := C_PENDING_ACCEPTANCE;
1313 ELSE
1314 p_new_status := C_PENDING_REMITTANCE;
1315 END IF;
1316
1317 p_new_event := C_COMPLETED;
1318
1319
1320 /*----------------------------------------------+
1321 | ACCEPT |
1322 +-----------------------------------------------*/
1323
1324 ELSIF (p_action = C_ACCEPT)
1325 THEN
1326 p_new_status := C_PENDING_REMITTANCE ;
1327 p_new_event := C_ACCEPTED ;
1328
1329
1330 /*----------------------------------------------+
1331 | UNCOMPLETE |
1332 +-----------------------------------------------*/
1333
1334 ELSIF (p_action = C_UNCOMPLETE)
1335 THEN
1336 p_new_status := C_INCOMPLETE ;
1337 p_new_event := C_INCOMPLETE ;
1338
1339
1340 /*----------------------------------------------+
1341 | HOLD |
1342 +-----------------------------------------------*/
1343
1344 ELSIF (p_action = C_HOLD )
1345 THEN
1346 p_new_status := l_trh_rec.status ;
1347 p_new_event := C_HOLD ;
1348
1349
1350 /*----------------------------------------------+
1351 | UNHOLD |
1352 +-----------------------------------------------*/
1353
1354 ELSIF (p_action = C_UNHOLD )
1355 THEN
1356 p_new_status := l_trh_rec.status ;
1357 p_new_event := C_RELEASE_HOLD ;
1358
1359
1360 /*----------------------------------------------+
1361 | SELECT FOR REMITTANCE |
1362 +-----------------------------------------------*/
1363
1364 ELSIF (p_action = C_SELECT_REMIT)
1365 THEN
1366 p_new_status := C_PENDING_REMITTANCE ;
1367 p_new_event := C_SELECTED_REMITTANCE ;
1368
1369
1370 /*----------------------------------------------+
1371 | DESELECT FOR REMITTANCE |
1372 +-----------------------------------------------*/
1373
1374 ELSIF (p_action = C_DESELECT_REMIT)
1375 THEN
1376 IF (p_trx_rec.br_unpaid_flag = 'Y')
1377 THEN
1378 p_new_status := C_UNPAID;
1379 ELSE
1380 p_new_status := C_PENDING_REMITTANCE ;
1381 END IF;
1382
1383 p_new_event := C_DESELECTED_REMITTANCE ;
1384
1385
1386 /*----------------------------------------------+
1387 | CANCEL |
1388 +-----------------------------------------------*/
1389
1390 ELSIF (p_action = C_CANCEL)
1391 THEN
1392 p_new_status := C_CANCELLED ;
1393 p_new_event := C_CANCELLED ;
1394
1395
1396
1397 /*----------------------------------------------+
1398 | APPROVE STANDARD REMITTED |
1399 +-----------------------------------------------*/
1400
1401 ELSIF (p_action = C_REMIT_STANDARD)
1402 THEN
1403 p_new_status := C_REMITTED ;
1404 p_new_event := C_REMITTED ;
1405
1406
1407 /*----------------------------------------------+
1408 | APPROVE FACTORE WITHOUT RECOURSE |
1409 +-----------------------------------------------*/
1410
1411 ELSIF (p_action = C_FACTORE)
1412 THEN
1413 IF (l_trh_rec.status = C_FACTORED)
1414 THEN
1415 p_new_status := C_CLOSED ;
1416 p_new_event := C_CLOSED ;
1417 ELSE
1418 p_new_status := C_FACTORED ;
1419 p_new_event := C_FACTORED ;
1420 END IF;
1421
1422
1423 /*----------------------------------------------+
1424 | APPROVE FACTORE WITH RECOURSE |
1425 +-----------------------------------------------*/
1426
1427 ELSIF (p_action = C_FACTORE_RECOURSE)
1428 THEN
1429 p_new_status := C_FACTORED ;
1430 p_new_event := C_FACTORED ;
1431
1432
1433 /*----------------------------------------------+
1434 | ELIMINATE RISK |
1435 +-----------------------------------------------*/
1436
1437 ELSIF (p_action = C_ELIMINATE_RISK)
1438 THEN
1439 p_new_status := C_CLOSED ;
1440 p_new_event := C_RISK_ELIMINATED ;
1441
1442
1443 /*----------------------------------------------+
1444 | UNELIMINATE RISK |
1445 +-----------------------------------------------*/
1446
1447 ELSIF (p_action = C_UNELIMINATE_RISK)
1448 THEN
1449
1450 Find_Last_relevant_trh (l_prev_trh_rec) ;
1451
1452 p_new_status := l_prev_trh_rec.status ;
1453 p_new_event := C_RISK_UNELIMINATED ;
1454
1455
1456 /*----------------------------------------------+
1457 | ENDORSE WITH RECOURSE |
1458 +-----------------------------------------------*/
1459
1460 ELSIF (p_action = C_ENDORSE_RECOURSE)
1461 THEN
1462 p_new_status := C_ENDORSED ;
1463 p_new_event := C_ENDORSED ;
1464
1465
1466 /*----------------------------------------------+
1467 | ENDORSE WITHOUT RECOURSE |
1468 +-----------------------------------------------*/
1469
1470 ELSIF (p_action = C_ENDORSE)
1471 THEN
1472 IF (l_trh_rec.status = C_ENDORSED)
1473 THEN
1474 p_new_status := C_CLOSED ;
1475 p_new_event := C_CLOSED ;
1476 ELSE
1477 p_new_status := C_ENDORSED ;
1478 p_new_event := C_ENDORSED ;
1479 END IF;
1480
1481
1482 /*----------------------------------------------+
1483 | UNPAID |
1484 +-----------------------------------------------*/
1485
1486 ELSIF (p_action = C_UNPAID)
1487 THEN
1488 p_new_status := C_UNPAID ;
1489 p_new_event := C_UNPAID ;
1490
1491
1492 /*----------------------------------------------+
1493 | RESTATE |
1494 +-----------------------------------------------*/
1495
1496 ELSIF (p_action = C_RESTATE)
1497 THEN
1498 p_new_status := C_PENDING_REMITTANCE ;
1499 p_new_event := C_RESTATE ;
1500
1501
1502
1503 /*----------------------------------------------+
1504 | RECALL |
1505 +-----------------------------------------------*/
1506
1507 ELSIF (p_action = C_RECALL)
1508 THEN
1509
1510 IF (p_trx_rec.br_unpaid_flag = 'Y')
1511 THEN
1512 p_new_status := C_UNPAID ;
1513 ELSE
1514 p_new_status := C_PENDING_REMITTANCE ;
1515 END IF;
1516 p_new_event := C_RECALLED ;
1517
1518
1519 /*----------------------------------------------+
1520 | PROTEST |
1521 +-----------------------------------------------*/
1522
1523 ELSIF (p_action = C_PROTEST)
1524 THEN
1525 p_new_status := C_PROTESTED ;
1526 p_new_event := C_PROTESTED ;
1527
1528
1529 /*----------------------------------------------+
1530 | EXCHANGE |
1531 +-----------------------------------------------*/
1532
1533 ELSIF (p_action = C_EXCHANGE)
1534 THEN
1535 p_new_status := l_trh_rec.status ;
1536 p_new_event := C_EXCHANGED ;
1537
1538
1539
1540 /*----------------------------------------------+
1541 | EXCHANGE COMPLETE |
1542 +-----------------------------------------------*/
1543
1544 ELSIF (p_action = C_EXCHANGE_COMPLETE)
1545 THEN
1546 p_new_status := C_CLOSED ;
1547 p_new_event := C_EXCHANGED ;
1548
1549
1550 /*----------------------------------------------+
1551 | EXCHANGE UNCOMPLETE |
1552 +-----------------------------------------------*/
1553
1554 ELSIF (p_action = C_EXCHANGE_UNCOMPLETE)
1555 THEN
1556 p_new_status := C_UNPAID ;
1557 p_new_event := C_EXCHANGED ;
1558
1559 ELSE
1560
1561 IF PG_DEBUG in ('Y', 'C') THEN
1562 arp_util.debug ('New_Status_Event: ' || '>>>>>>>>>> The action ' || p_action || ' is invalid ');
1563 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_STATUS_PUB.New_Status_Event () ');
1564 END IF;
1565 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_ACTION_FORBIDDEN');
1566 FND_MESSAGE.SET_TOKEN('ACTION', p_action);
1567 FND_MESSAGE.SET_TOKEN('BRNUM', p_trx_rec.trx_number);
1568 app_EXCEPTION.raise_EXCEPTION;
1569
1570 END IF;
1571
1572 IF PG_DEBUG in ('Y', 'C') THEN
1573 arp_util.debug ('New_Status_Event: ' || 'New Status : ' || p_new_status);
1574 arp_util.debug ('New_Status_Event: ' || 'New Event : ' || p_new_event);
1575 arp_util.debug('AR_BILLS_MAINTAIN_STATUS_PUB.New_Status_Event()-');
1576 END IF;
1577
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 IF PG_DEBUG in ('Y', 'C') THEN
1581 arp_util.debug('>>>>>>>>>> EXCEPTION : New_Status_Event () ');
1582 arp_util.debug('New_Status_Event: ' || 'p_action = ' || p_action);
1583 END IF;
1584 RAISE;
1585
1586 END New_Status_Event;
1587
1588
1589 /*===========================================================================+
1590 | FUNCTION |
1591 | revision |
1592 | |
1593 | DESCRIPTION |
1594 | This function returns the revision number of this package. |
1595 | |
1596 | SCOPE - PUBLIC |
1597 | |
1598 | RETURNS : Revision number of this package |
1599 | |
1600 | MODIFICATION HISTORY |
1601 | 10 JAN 2001 John HALL Created |
1602 +===========================================================================*/
1603 FUNCTION revision RETURN VARCHAR2 IS
1604 BEGIN
1605 RETURN '$Revision: 115.12 $';
1606 END revision;
1607 --
1608
1609
1610 END AR_BILLS_MAINTAIN_STATUS_PUB ;