[Home] [Help]
PACKAGE BODY: APPS.AR_BILLS_MAINTAIN_LIB_PVT
Source
1 PACKAGE BODY AR_BILLS_MAINTAIN_LIB_PVT AS
2 /* $Header: ARBRMALB.pls 120.16 2010/11/18 06:53:53 vpusulur 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_ENDORSED CONSTANT VARCHAR2(30) := 'ENDORSED';
17
18
19 /* =======================================================================
20 | Bills Receivable event constants
21 * ======================================================================*/
22
23 C_MATURITY_DATE CONSTANT VARCHAR2(30) := 'MATURITY_DATE';
24 C_RISK_ELIMINATED CONSTANT VARCHAR2(30) := 'RISK_ELIMINATED';
25 C_COMPLETED CONSTANT VARCHAR2(30) := 'COMPLETED';
26
27 /* =======================================================================
28 | Bills Receivable action constants
29 * ======================================================================*/
30
31 C_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
32 C_ACCEPT CONSTANT VARCHAR2(30) := 'ACCEPT';
33 C_COMPLETE_ACC CONSTANT VARCHAR2(30) := 'COMPLETE_ACC';
34 C_UNCOMPLETE CONSTANT VARCHAR2(30) := 'UNCOMPLETE';
35 C_HOLD CONSTANT VARCHAR2(30) := 'HOLD';
36 C_UNHOLD CONSTANT VARCHAR2(30) := 'RELEASE HOLD';
37 C_SELECT_REMIT CONSTANT VARCHAR2(30) := 'SELECT_REMIT';
38 C_DESELECT_REMIT CONSTANT VARCHAR2(30) := 'DESELECT_REMIT';
39 C_CANCEL CONSTANT VARCHAR2(30) := 'CANCEL';
40 C_UNPAID CONSTANT VARCHAR2(30) := 'UNPAID';
41 C_REMIT_STANDARD CONSTANT VARCHAR2(30) := 'REMIT_STANDARD';
42 C_FACTORE CONSTANT VARCHAR2(30) := 'FACTORE';
43 C_FACTORE_RECOURSE CONSTANT VARCHAR2(30) := 'FACTORE_RECOURSE';
44 C_RECALL CONSTANT VARCHAR2(30) := 'RECALL';
45 C_ELIMINATE_RISK CONSTANT VARCHAR2(30) := 'RISK ELIMINATION';
46 C_UNELIMINATE_RISK CONSTANT VARCHAR2(30) := 'REESTABLISH RISK';
47 C_PROTEST CONSTANT VARCHAR2(30) := 'PROTEST';
48 C_ENDORSE CONSTANT VARCHAR2(30) := 'ENDORSE';
49 C_ENDORSE_RECOURSE CONSTANT VARCHAR2(30) := 'ENDORSE_RECOURSE';
50 C_RESTATE CONSTANT VARCHAR2(30) := 'RESTATE';
51 C_EXCHANGE CONSTANT VARCHAR2(30) := 'EXCHANGE';
52 C_EXCHANGE_COMPLETE CONSTANT VARCHAR2(30) := 'EXCHANGE_COMPLETE';
53 C_EXCHANGE_UNCOMPLETE CONSTANT VARCHAR2(30) := 'EXCHANGE_UNCOMPLETE';
54 C_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
55
56
57 /* =======================================================================
58 | Bills Receivable remittance method code constants
59 * ======================================================================*/
60
61 C_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
62 C_FACTORING CONSTANT VARCHAR2(30) := 'FACTORING';
63
64
65 /* =======================================================================
66 | Parameter p_called_from for the Receipt API
67 * ======================================================================*/
68
69 C_BR_REMITTED CONSTANT VARCHAR2(30) := 'BR_REMITTED';
70 C_BR_FACTORED_RECOURSE CONSTANT VARCHAR2(30) := 'BR_FACTORED_WITH_RECOURSE';
71 C_BR_FACTORED CONSTANT VARCHAR2(30) := 'BR_FACTORED_WITHOUT_RECOURSE';
72
73
74
75 /*==============================================================================+
76 | PROCEDURE |
77 | Get_Doc_Seq |
78 | |
79 | DESCRIPTION |
80 | Document Sequence Routine |
81 | |
82 +==============================================================================*/
83
84
85 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
86
87 PROCEDURE Get_Doc_Seq ( p_appid IN NUMBER ,
88 p_trx_rec IN OUT NOCOPY RA_CUSTOMER_TRX%ROWTYPE,
89 p_sob_id IN NUMBER ,
90 p_met_code IN VARCHAR2) IS
91
92 l_cat_code VARCHAR2(20) ;
93 l_doc_seq_ret_stat NUMBER ;
94 l_doc_sequence_name VARCHAR2(50) ;
95 l_doc_sequence_type VARCHAR2(50) ;
96 l_doc_sequence_value NUMBER ;
97 l_db_sequence_name VARCHAR2(50) ;
98 l_seq_ass_id NUMBER ;
99 l_prd_tab_name VARCHAR2(50) ;
100 l_aud_tab_name VARCHAR2(50) ;
101 l_msg_flag VARCHAR2(1) ;
102 pg_profile_doc_seq VARCHAR2(1) ;
103 l_copy_doc_number_flag VARCHAR2(1) ;
104 l_count NUMBER ;
105
106 BEGIN
107
108 IF PG_DEBUG in ('Y', 'C') THEN
109 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Get_Doc_Seq ()+');
110 END IF;
111
112
113 SELECT count(*)
114 INTO l_count
115 FROM AR_TRANSACTION_HISTORY
116 WHERE customer_trx_id = p_trx_rec.customer_trx_id
117 AND event = C_COMPLETED;
118
119
120 IF (l_count = 0)
121 THEN
122
123 -- The BR has never been completed
124
125 pg_profile_doc_seq := fnd_profile.value('UNIQUE:SEQ_NUMBERS');
126
127 IF PG_DEBUG in ('Y', 'C') THEN
128 arp_util.debug( 'SEQ : '||NVL( pg_profile_doc_seq, 'N'));
129 END IF;
130
131 SELECT name
132 INTO l_cat_code
133 FROM ra_cust_trx_types
134 WHERE cust_trx_type_id = p_trx_rec.cust_trx_type_id;
135
136 IF ( NVL( pg_profile_doc_seq, 'N') <> 'N' )
137 THEN
138 BEGIN
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_util.debug( 'Seq numbering on');
141 END IF;
142
143 /*------------------------------+
144 | Get the document sequence. |
145 +------------------------------*/
146
147 l_doc_seq_ret_stat:=
148 fnd_seqnum.get_seq_info (
149 app_id => p_appid ,
150 cat_code => l_cat_code ,
151 sob_id => p_sob_id ,
152 met_code => p_met_code ,
153 trx_date => trunc(p_trx_rec.trx_date) ,
154 docseq_id => p_trx_rec.doc_sequence_id ,
155 docseq_type => l_doc_sequence_type ,
156 docseq_name => l_doc_sequence_name ,
157 db_seq_name => l_db_sequence_name ,
158 seq_ass_id => l_seq_ass_id ,
159 prd_tab_name => l_prd_tab_name ,
160 aud_tab_name => l_aud_tab_name ,
161 msg_flag => l_msg_flag ,
162 suppress_error => 'N' ,
163 suppress_warn => 'Y' );
164
165 --arp_util.debug('Doc sequence return status : '||to_char(nvl(l_doc_seq_ret_stat,-99)));
166 --arp_util.debug('l_doc_sequence_name : '||l_doc_sequence_name);
167 --arp_util.debug('l_doc_sequence_id : '||to_char(nvl(l_doc_sequence_id,-99)));
168
169
170 IF (l_doc_seq_ret_stat = -8) THEN
171
172 -- Sequential Numbering is always used and there is
173 -- no assignment for this set of parameters
174
175 IF PG_DEBUG in ('Y', 'C') THEN
176 arp_util.debug( '>>>>>>>>>> The doc sequence does not exist for the current document');
177 END IF;
178
179 FND_MESSAGE.Set_Name( 'AR','AR_BR_DOC_SEQ_NOT_EXIST_A');
180 app_exception.raise_exception;
181
182 ELSIF (l_doc_seq_ret_stat = -2) THEN
183
184 -- No assignment exists for the set of parameters
185 -- this is the case of Partially Used
186
187 IF PG_DEBUG in ('Y', 'C') THEN
188 arp_util.debug( '>>>>>>>>>> Warning : The doc sequence does not exist for the current document');
189 END IF;
190
191 END IF;
192
193 /* Bug 3632787 Needed to trim l_doc_sequence_type as I found that it was passing some null in the value */
194
195 l_doc_sequence_type := ltrim(rtrim(l_doc_sequence_type));
196
197 /* Bug 3632787 Added condition to check l_doc_sequence_type for all the three conditions */
198
199
200 IF ( l_doc_sequence_name IS NOT NULL) AND (p_trx_rec.doc_sequence_id IS NOT NULL)
201 AND (l_doc_sequence_type <> 'M')
202 THEN
203 /*------------------------------------+
204 | Automatic Document Numbering case |
205 +------------------------------------*/
206
207 IF PG_DEBUG in ('Y', 'C') THEN
208 arp_util.debug( 'Automatic Document Numbering case ');
209 END IF;
210
211 l_doc_seq_ret_stat := fnd_seqnum.get_seq_val (
212 p_appid ,
213 l_cat_code ,
214 p_sob_id ,
215 p_met_code ,
216 trunc(p_trx_rec.trx_date),
217 l_doc_sequence_value ,
218 p_trx_rec.doc_sequence_id);
219
220 IF (p_trx_rec.doc_sequence_value IS NOT NULL) THEN
221 --raise an error message because the user is not supposed to pass
222 --in a value for the document sequence number in this case.
223 IF PG_DEBUG in ('Y', 'C') THEN
224 arp_util.debug( '>>>>>>>>>> The user is not supposed to pass in a value in this case');
225 END IF;
226 FND_MESSAGE.Set_Name('AR', 'AR_BR_DOC_SEQ_AUTOMATIC');
227 app_exception.raise_exception;
228 END IF;
229
230 p_trx_rec.doc_sequence_value := l_doc_sequence_value;
231 arp_util.debug('l_doc_sequence_value :'||to_char(nvl(p_trx_rec.doc_sequence_value,-99)));
232
233
234
235 ELSIF (p_trx_rec.doc_sequence_id IS NOT NULL) AND (p_trx_rec.doc_sequence_value IS NOT NULL)
236 AND (l_doc_sequence_type = 'M')
237 THEN
238 /*-------------------------------------+
239 | Manual Document Numbering case |
240 | with the document value specified. |
241 | Use the specified value. |
242 +-------------------------------------*/
243
244 NULL;
245
246
247 ELSIF (p_trx_rec.doc_sequence_id IS NOT NULL) AND (p_trx_rec.doc_sequence_value IS NULL)
248 AND (l_doc_sequence_type = 'M')
249 THEN
250 /*-----------------------------------------+
251 | Manual Document Numbering case |
252 | with the document value not specified. |
253 | Generate a document value mandatory |
254 | error. |
255 +-----------------------------------------*/
256
257 IF (NVL(pg_profile_doc_seq,'N') = 'A')
258 THEN
259 IF PG_DEBUG in ('Y', 'C') THEN
260 arp_util.debug( '>>>>>>>>>> A - pg_profile_doc_seq : ' || pg_profile_doc_seq);
261 END IF;
262 FND_MESSAGE.Set_Name('AR', 'AR_BR_DOC_SEQ_VALUE_NULL_A');
263 FND_MESSAGE.Set_Token('SEQUENCE', l_doc_sequence_name);
264 app_exception.raise_exception;
265
266 ELSIF (NVL(pg_profile_doc_seq,'N') = 'P')
267 THEN
268 --Warning
269 IF PG_DEBUG in ('Y', 'C') THEN
270 arp_util.debug( '>>>>>>>>>> P - pg_profile_doc_seq : ' || pg_profile_doc_seq);
271 END IF;
272 FND_MESSAGE.SET_NAME('AR','AR_BR_DOC_SEQ_VALUE_NULL_P');
273 app_exception.raise_exception;
274
275 END IF;
276
277
278 END IF;
279
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282 /*------------------------------------------+
283 | No document assignment was found. |
284 | Generate an error if document numbering |
285 | is mandatory. |
286 +------------------------------------------*/
287 IF PG_DEBUG in ('Y', 'C') THEN
288 arp_util.debug( 'no_data_found raised');
289 END IF;
290
291 IF (pg_profile_doc_seq = 'A' ) THEN
292 IF PG_DEBUG in ('Y', 'C') THEN
293 arp_util.debug( '>>>>>>>>>> no_data_found raised - pg_profile_doc_seq = A');
294 END IF;
295 FND_MESSAGE.Set_Name( 'FND','UNIQUE-ALWAYS USED');
296 app_exception.raise_exception;
297 ELSE
298 IF PG_DEBUG in ('Y', 'C') THEN
299 arp_util.debug( '>>>>>>>>>> no_data_found raised - pg_profile_doc_seq : ' || pg_profile_doc_seq);
300 END IF;
301 p_trx_rec.doc_sequence_id := NULL;
302 p_trx_rec.doc_sequence_value := NULL;
303 END IF;
304
305 WHEN OTHERS THEN
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_util.debug( '>>>>>>>>>> Unhandled exception in doc sequence assignment');
308 END IF;
309 raise;
310
311 END;
312
313 END IF;
314
315
316 /*======================================================================+
317 | Copy Document Number to transaction number if "copy document to |
318 | transaction number" flag is checked in batch sources |
319 +=======================================================================*/
320
321 BEGIN
322
323 SELECT copy_doc_number_flag
324 INTO l_copy_doc_number_flag
325 FROM RA_BATCH_SOURCES
326 WHERE batch_source_id = p_trx_rec.batch_source_id;
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 IF PG_DEBUG in ('Y', 'C') THEN
331 arp_util.debug( '>>>>>>>>>> Bacth Source does not exist');
332 END IF;
333 FND_MESSAGE.Set_Name( 'AR','AR_BR_INVALID_BATCH_SOURCE');
334 app_exception.raise_exception;
335 WHEN OTHERS THEN
336 IF PG_DEBUG in ('Y', 'C') THEN
337 arp_util.debug( '>>>>>>>>>> Failed when fetching copy doc number flag');
338 END IF;
339 END;
340
341 IF PG_DEBUG in ('Y', 'C') THEN
342 arp_util.debug( 'Copy Doc Number Flag : ' || l_copy_doc_number_flag);
343 END IF;
344
345 IF (NVL(l_copy_doc_number_flag,'N') = 'Y') AND
346 (p_trx_rec.doc_sequence_value IS NOT NULL) AND
347 (p_trx_rec.old_trx_number IS NULL )
348 THEN
349 p_trx_rec.old_trx_number := p_trx_rec.trx_number;
350 p_trx_rec.trx_number := p_trx_rec.doc_sequence_value;
351 END IF;
352
353 IF PG_DEBUG in ('Y', 'C') THEN
354 arp_util.debug( 'Old Trx Number : ' || p_trx_rec.trx_number);
355 arp_util.debug( 'New Trx Number : ' || p_trx_rec.trx_number);
356 END IF;
357
358 END IF;
359
360 IF PG_DEBUG in ('Y', 'C') THEN
361 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Get_Doc_Seq ()-');
362 END IF;
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 IF PG_DEBUG in ('Y', 'C') THEN
367 arp_util.debug('>>>>>>>>>> EXCEPTION : Get_Doc_Seq () ');
368 arp_util.debug( 'p_appid = ' || p_appid);
369 arp_util.debug( 'p_sob_id = ' || p_sob_id);
370 arp_util.debug( 'p_met_code = ' || p_met_code);
371 END IF;
372 RAISE;
373
374 END Get_Doc_Seq;
375
376
377 /*==============================================================================+
378 | PROCEDURE |
379 | Set_API_Error |
380 | |
381 | DESCRIPTION |
382 | Put the error message generated by the adjustment API or the receipt API |
383 | on the message stack. |
384 | |
385 +==============================================================================*/
386
387 PROCEDURE Set_API_Error
388 IS
389
390 l_data varchar2(4000);
391 l_msg_index_out number;
392
393 BEGIN
394 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Set_API_Error ()+');
395
396 FND_MSG_PUB.Get (FND_MSG_PUB.G_FIRST, FND_API.G_TRUE, l_data, l_msg_index_out);
397 FND_MESSAGE.Set_Encoded (l_data);
398 app_exception.raise_exception;
399 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Set_API_Error ()-');
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 arp_util.debug('>>>>>>>>>> EXCEPTION : Set_API_Error () ');
404 RAISE;
405
406 END Set_API_Error;
407
408
409 /*==============================================================================+
410 | PROCEDURE |
411 | Get_Remittance_Batch |
412 | |
413 | DESCRIPTION |
414 | For a given BR, find the remittance batch information (if it exists) |
415 | |
416 +==============================================================================*/
417
418 PROCEDURE Get_Remittance_Batch ( p_customer_trx_id IN NUMBER ,
419 p_batch_rec OUT NOCOPY ar_batches%ROWTYPE)
420 IS
421
422 -- Added for bug # 2712726
423 -- ORASHID
424 --
425 l_nocopy_batch_id ar_batches.batch_id%TYPE;
426
427 BEGIN
428 IF PG_DEBUG in ('Y', 'C') THEN
429 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Get_Remittance_Batch ()+');
430 END IF;
431
432
433 -- Get the remittance batch id in ra_customer_trx_id
434
435 IF PG_DEBUG in ('Y', 'C') THEN
436 arp_util.debug ( 'Customer_Trx_Id : ' || p_customer_trx_id);
437 END IF;
438
439 SELECT remittance_batch_id
440 INTO p_batch_rec.batch_id
441 FROM ra_customer_trx
442 WHERE customer_trx_id = p_customer_trx_id;
443
444 -- Modified for bug # 2712726
445 -- ORASHID
446 --
447 l_nocopy_batch_id := p_batch_rec.batch_id;
448 arp_cr_batches_pkg.fetch_p(l_nocopy_batch_id, p_batch_rec);
449
450 IF PG_DEBUG in ('Y', 'C') THEN
451 arp_util.debug ( 'Remittance Batch ID : ' || p_batch_rec.batch_id);
452 arp_util.debug ( 'Remittance Method : ' || p_batch_rec.remit_method_code);
453 arp_util.debug ( 'With Recourse Flag : ' || p_batch_rec.with_recourse_flag);
454 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Get_Remittance_Batch ()-');
455 END IF;
456
457 EXCEPTION
458 WHEN NO_DATA_FOUND THEN
459 IF PG_DEBUG in ('Y', 'C') THEN
460 arp_util.debug( '>>>>>>>>>> The BR has not been remitted yet');
461 END IF;
462
463 WHEN OTHERS THEN
464 IF PG_DEBUG in ('Y', 'C') THEN
465 arp_util.debug('>>>>>>>>>> EXCEPTION : Get_Remittance_Batch () ');
466 arp_util.debug( 'p_customer_trx_id = ' || p_customer_trx_id);
467 END IF;
468 RAISE;
469
470 END Get_Remittance_Batch;
471
472
473
474 /*==============================================================================+
475 | PROCEDURE |
476 | Update_Reserved_Columns |
477 | |
478 | DESCRIPTION |
479 | Update the reserved type and reserved value of the payment schedule |
480 | |
481 +==============================================================================*/
482
483 PROCEDURE Update_Reserved_Columns ( p_payment_schedule_id IN NUMBER ,
484 p_reserved_type IN VARCHAR2,
485 p_reserved_value IN NUMBER )
486 IS
487
488 l_ps_rec ar_payment_schedules%ROWTYPE;
489
490 BEGIN
491 IF PG_DEBUG in ('Y', 'C') THEN
492 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Update_Reserved_Columns ()+ ');
493 END IF;
494
495 arp_ps_pkg.lock_p(p_payment_schedule_id);
496
497 arp_ps_pkg.set_to_dummy (l_ps_rec);
498
499 l_ps_rec.payment_schedule_id := p_payment_schedule_id;
500 l_ps_rec.reserved_type := p_reserved_type;
501 l_ps_rec.reserved_value := p_reserved_value;
502
503 arp_ps_pkg.update_p (l_ps_rec, l_ps_rec.payment_schedule_id);
504
505 IF PG_DEBUG in ('Y', 'C') THEN
506 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Update_Reserved_Columns ()- ');
507 END IF;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF PG_DEBUG in ('Y', 'C') THEN
512 arp_util.debug('>>>>>>>>>> EXCEPTION : Update_Reserved_Columns () ');
513 arp_util.debug( 'p_payment_schedule_id = ' || p_payment_schedule_id);
514 arp_util.debug( 'p_reserved_type = ' || p_reserved_type);
515 arp_util.debug( 'p_reserved_value = ' || p_reserved_value);
516 END IF;
517 RAISE;
518
519 END Update_Reserved_Columns;
520
521
522
523 /*==============================================================================+
524 | PROCEDURE |
525 | Default_Action_Dates |
526 | |
527 | DESCRIPTION |
528 | Defaults the action date and action gl date |
529 | Ex : Acceptance Date and Acceptance GL date |
530 | |
531 +==============================================================================*/
532
533
534 PROCEDURE Default_Action_Dates ( p_trx_date IN OUT NOCOPY AR_TRANSACTION_HISTORY.trx_date%TYPE,
535 p_gl_date IN OUT NOCOPY AR_TRANSACTION_HISTORY.gl_date%TYPE )
536
537 IS
538
539 l_return_status VARCHAR2(1);
540
541 BEGIN
542
543 IF PG_DEBUG in ('Y', 'C') THEN
544 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Default_Action_Dates()+ ');
545 END IF;
546
547 ---- Default the action date if NULL
548 IF (p_trx_date IS NULL) THEN
549 Select SYSDATE
550 into p_trx_date
551 from dual;
552 END IF;
553
554
555 ---- Default the action GL Date if NULL
556
557 IF (p_gl_date IS NULL)
558 THEN
559 AR_BILLS_CREATION_LIB_PVT.Default_gl_date(
560 sysdate ,
561 p_gl_date ,
562 l_return_status );
563 ELSE
564 AR_BILLS_CREATION_VAL_PVT.Validate_GL_Date (p_gl_date);
565 END IF;
566
567 IF PG_DEBUG in ('Y', 'C') THEN
568 arp_util.debug( 'Default_gl_date Return_status : '||l_return_status);
569 arp_util.debug( 'GL Date defaulted : ' || p_gl_date);
570 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Default_Action_Dates()-');
571 END IF;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 IF PG_DEBUG in ('Y', 'C') THEN
576 arp_util.debug('>>>>>>>>>> EXCEPTION : Default_Action_Dates () ');
577 arp_util.debug( 'p_trx_date = ' || p_trx_date);
578 arp_util.debug( 'p_gl_date = ' || p_gl_date);
579 END IF;
580 RAISE;
581
582 END Default_Action_Dates;
583
584
585
586
587 /*==============================================================================+
588 | PROCEDURE |
589 | Complete_Or_Accept |
590 | |
591 | DESCRIPTION |
592 | Depending on the action : |
593 | - BR Completion, No Acceptance Required |
594 | - BR Acceptance |
595 | |
596 +==============================================================================*/
597
598
599
600 PROCEDURE Complete_Or_Accept (p_trh_rec IN OUT NOCOPY ar_transaction_history%ROWTYPE)
601 IS
602
603 l_adj_acctd_amount NUMBER;
604
605 CURSOR assignment_cur IS
606 SELECT br_ref_customer_trx_id, br_ref_payment_schedule_id, extended_amount,
607 customer_trx_line_id, extended_acctd_amount
608 FROM ra_customer_trx_lines
609 WHERE customer_trx_id = p_trh_rec.customer_trx_id;
610
611 assignment_rec assignment_cur%ROWTYPE ;
612
613 l_trh_rec AR_TRANSACTION_HISTORY%ROWTYPE ;
614 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE ;
615 l_trx_rec RA_CUSTOMER_TRX%ROWTYPE ;
616 l_new_adjust_id NUMBER ;
617 l_move_deferred_tax VARCHAR2(1) := 'N' ;
618
619 BEGIN
620
621 IF PG_DEBUG in ('Y', 'C') THEN
622 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Complete_Or_Accept ()+');
623 END IF;
624
625 /*----------------------------------------------+
626 | For each assignment, create a non accounting |
627 | Adjustment with status A |
628 +-----------------------------------------------*/
629
630 FOR assignment_rec IN assignment_cur LOOP
631
632 arp_ps_pkg.fetch_p (assignment_rec.br_ref_payment_schedule_id, l_ps_rec);
633
634 IF (l_ps_rec.tax_remaining IS NOT NULL and l_ps_rec.tax_remaining <> 0)
635 THEN
636 l_move_deferred_tax := 'Y';
637 END IF;
638
639 Create_Adjustment ( p_trh_rec ,
640 assignment_rec.br_ref_customer_trx_id ,
641 l_ps_rec ,
642 assignment_rec.extended_amount ,
643 -15 ,
644 'A' ,
645 l_move_deferred_tax ,
646 l_new_adjust_id );
647
648
649 /*----------------------------------------------+
650 | Update the Assignment Information with |
651 | the Adjustment ID |
652 +-----------------------------------------------*/
653
654 arp_ctl_pkg.lock_p (assignment_rec.customer_trx_line_id);
655
656 UPDATE ra_customer_trx_lines
657 SET br_adjustment_id = l_new_adjust_id
658 WHERE customer_trx_line_id = assignment_rec.customer_trx_line_id;
659
660
661 /*----------------------------------------------+
662 | Update the reserved columns of the exchanged |
663 | Payment Schedule |
664 +-----------------------------------------------*/
665
666 update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, NULL, NULL);
667
668
669 /*----------------------------------------------+
670 | If the transaction to be exchanged is a BR, |
671 | create a CLOSED history record |
672 +-----------------------------------------------*/
673
674
675 IF (AR_BILLS_CREATION_VAL_PVT.Is_Transaction_BR (l_ps_rec.cust_trx_type_id))
676 THEN
677
678 ARP_CT_PKG.fetch_p (l_trx_rec, assignment_rec.br_ref_customer_trx_id);
679 l_trh_rec.customer_trx_id := assignment_rec.br_ref_customer_trx_id;
680 ARP_TRANSACTION_HISTORY_PKG.lock_fetch_f_trx_id (l_trh_rec);
681
682 -- Fetch the new status and new event of the BR
683
684 AR_BILLS_MAINTAIN_STATUS_PUB.New_Status_Event (
685 p_trx_rec => l_trx_rec ,
686 p_action => C_EXCHANGE_COMPLETE ,
687 p_new_status => l_trh_rec.status ,
688 p_new_event => l_trh_rec.event );
689
690 l_trh_rec.transaction_history_id:= NULL ;
691 l_trh_rec.current_record_flag := 'Y' ;
692 l_trh_rec.prv_trx_history_id := NULL ;
693 l_trh_rec.posting_control_id := -3 ;
694 l_trh_rec.gl_posted_date := NULL ;
695 l_trh_rec.first_posted_record_flag := 'N' ;
696 l_trh_rec.created_from := 'ARBRMAIB' ;
697 l_trh_rec.postable_flag := 'N' ;
698 l_trh_rec.current_accounted_flag:= 'N' ;
699
700 arp_proc_transaction_history.insert_transaction_history (l_trh_rec ,
701 l_trh_rec.transaction_history_id);
702 END IF;
703
704 END LOOP;
705
706 IF PG_DEBUG in ('Y', 'C') THEN
707 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Complete_Or_Accept ()-');
708 END IF;
709
710 EXCEPTION
711 WHEN OTHERS THEN
712 IF PG_DEBUG in ('Y', 'C') THEN
713 arp_util.debug('>>>>>>>>>> EXCEPTION : Complete_Or_Accept () ');
714 END IF;
715 IF (assignment_cur%ISOPEN)
716 THEN
717 CLOSE assignment_cur;
718 END IF;
719 RAISE;
720
721 END Complete_Or_Accept;
722
723
724 /*==============================================================================+
725 | PROCEDURE |
726 | Complete_Acc_Required |
727 | |
728 | DESCRIPTION |
729 | BR Pending Acceptance is Completed |
730 | |
731 +==============================================================================*/
732
733
734 PROCEDURE Complete_Acc_Required (p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE)
735 IS
736
737
738 CURSOR assignment_cur IS
739 SELECT br_ref_payment_schedule_id
740 FROM ra_customer_trx_lines
741 WHERE customer_trx_id = p_customer_trx_id;
742
743 assignment_rec assignment_cur%ROWTYPE ;
744
745
746 l_ps_rec AR_PAYMENT_SCHEDULES%ROWTYPE;
747
748 BEGIN
749
750 IF PG_DEBUG in ('Y', 'C') THEN
751 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Complete_Acc_Required ()+');
752 END IF;
753
754 /*----------------------------------------------+
755 | For each assignment, update the reserved |
756 | columns of the PS |
757 +-----------------------------------------------*/
758
759 FOR assignment_rec IN assignment_cur LOOP
760 update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, 'TRANSACTION', p_customer_trx_id);
761 END LOOP;
762
763 IF PG_DEBUG in ('Y', 'C') THEN
764 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Complete_Acc_Required ()-');
765 END IF;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 IF PG_DEBUG in ('Y', 'C') THEN
770 arp_util.debug('Complete_Acc_Required: ' || '>>>>>>>>>> EXCEPTION : Complete_Or_Accept () ');
771 arp_util.debug('Complete_Acc_Required: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
772 END IF;
773 IF (assignment_cur%ISOPEN)
774 THEN
775 CLOSE assignment_cur;
776 END IF;
777 RAISE;
778
779 END Complete_Acc_Required;
780
781
782 /*==============================================================================+
783 | PROCEDURE |
784 | Create_Adjustment |
785 | |
786 | DESCRIPTION |
787 | Calls the Adjustment API to create an adjustment |
788 | |
789 +==============================================================================*/
790
791
792 PROCEDURE Create_Adjustment ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
793 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
794 p_ps_rec IN ar_payment_schedules%ROWTYPE ,
795 p_amount IN NUMBER ,
796 p_receivables_trx_id IN ar_receivables_trx.receivables_trx_id%TYPE ,
797 p_status IN VARCHAR2 ,
798 p_move_deferred_tax IN VARCHAR2 ,
799 p_adj_id OUT NOCOPY ar_adjustments.adjustment_id%TYPE )
800 IS
801
802 adj_rec ar_adjustments%rowtype;
803 l_msg_count number :=0;
804 l_msg_data varchar2(2000);
805 l_return_status varchar2(1);
806 new_adj_num ar_adjustments.adjustment_number%type;
807 l_app_short_name varchar2(30);
808 l_message_name varchar2(30);
809 l_org_return_status VARCHAR2(1);
810 l_org_id NUMBER;
811
812 BEGIN
813
814 IF PG_DEBUG in ('Y', 'C') THEN
815 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Create_Adjustment ()+');
816 END IF;
817
818 /* SSA change */
819 l_org_id := p_trh_rec.org_id;
820 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
821 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
822 p_return_status =>l_org_return_status);
823
824 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
825
826 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Create_adjustment : l_org_return_status <> SUCCESS');
827 ELSE
828
829 adj_rec.type := 'INVOICE' ;
830 adj_rec.payment_schedule_id := p_ps_rec.payment_schedule_id ;
831 adj_rec.amount := -(p_amount) ;
832 adj_rec.customer_trx_id := p_customer_trx_id ;
833 adj_rec.receivables_trx_id := p_receivables_trx_id ;
834 adj_rec.created_from := 'ARBRMAIB' ;
835 adj_rec.apply_date := p_trh_rec.trx_date ;
836 adj_rec.gl_date := p_trh_rec.gl_date ;
837 adj_rec.reason_code := '' ;
838 adj_rec.comments := p_trh_rec.comments ;
839 adj_rec.status := p_status ;
840 adj_rec.org_id := p_trh_rec.org_id ;
841
842
843 IF PG_DEBUG in ('Y', 'C') THEN
844 arp_util.debug ( '-----------------------------------------');
845 arp_util.debug ('PARAMETERS PASSED TO AR_ADJUST_PUB.Create_Adjustment :');
846 arp_util.debug ( 'adj_rec.payment_schedule_id : ' || adj_rec.payment_schedule_id);
847 arp_util.debug ( 'adj_rec.amount : ' || adj_rec.amount);
848 arp_util.debug ( 'adj_rec.customer_trx_id : ' || adj_rec.customer_trx_id);
849 arp_util.debug ( 'adj_rec.receivables_trx_id : ' || adj_rec.receivables_trx_id);
850 arp_util.debug ( 'adj_rec.apply_date : ' || adj_rec.apply_date);
851 arp_util.debug ( 'adj_rec.gl_date : ' || adj_rec.gl_date);
852 arp_util.debug ( 'adj_rec.comments : ' || adj_rec.comments);
853 arp_util.debug ( 'adj_rec.status : ' || adj_rec.status);
854 arp_util.debug ( 'adj_rec.org_id : ' || adj_rec.org_id);
855 arp_util.debug ( 'p_move_deferred_tax : ' || p_move_deferred_tax);
856 arp_util.debug ( '-----------------------------------------');
857 END IF;
858
859 AR_ADJUST_PUB.Create_Adjustment (
860 p_api_name => 'AR_ADJUST_PUB' ,
861 p_api_version => 1.0 ,
862 p_init_msg_list => FND_API.G_TRUE ,
863 p_msg_count => l_msg_count ,
864 p_msg_data => l_msg_data ,
865 p_return_status => l_return_status ,
866 p_adj_rec => adj_rec ,
867 p_chk_approval_limits => 'F' ,
868 p_check_amount => 'F' ,
869 p_move_deferred_tax => p_move_deferred_tax ,
870 p_new_adjust_number => new_adj_num ,
871 p_new_adjust_id => p_adj_id ,
872 p_org_id => adj_rec.org_id);
873
874 IF PG_DEBUG in ('Y', 'C') THEN
875 arp_util.debug ( 'return status : ' || l_return_status);
876 arp_util.debug ( 'msg_count : ' || l_msg_count);
877 arp_util.debug ( 'msg_data : ' || l_msg_data);
878 END IF;
879
880
881 IF (l_return_status <> 'S')
882 THEN
883 IF PG_DEBUG in ('Y', 'C') THEN
884 arp_util.debug( '>>>>>>>>>> Problems during Adjustment Creation');
885 arp_util.debug( 'l_return_status : ' || l_return_status);
886 arp_util.debug( 'l_msg_count : ' || l_msg_count);
887 arp_util.debug( 'l_msg_data : ' || l_msg_data);
888 END IF;
889
890 IF (l_msg_count > 0)
891 THEN
892 Set_Api_Error;
893 ELSE
894 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_CREATE_ADJ');
895 app_exception.raise_exception;
896 END IF;
897
898 END IF;
899 END IF;
900
901 IF PG_DEBUG in ('Y', 'C') THEN
902 arp_util.debug ( 'Adjustment ID created : ' || p_adj_id);
903 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Create_Adjustment ()-');
904 END IF;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 IF PG_DEBUG in ('Y', 'C') THEN
909 arp_util.debug('>>>>>>>>>> EXCEPTION : Create_Adjustment () ');
910 arp_util.debug( 'p_customer_trx_id : ' || p_customer_trx_id);
911 arp_util.debug( 'p_receivables_trx_id : ' || p_receivables_trx_id);
912 arp_util.debug( 'p_status : ' || p_status);
913 arp_util.debug( 'p_amount : ' || p_amount);
914 arp_util.debug( 'p_move_deferred_tax : ' || p_move_deferred_tax);
915 END IF;
916 RAISE;
917
918 END Create_Adjustment;
919
920
921
922 /*==============================================================================+
923 | PROCEDURE |
924 | Find_Last_Adjustment |
925 | |
926 | DESCRIPTION |
927 | Find the last endorsement adjustment on the BR |
928 | |
929 +==============================================================================*/
930
931
932 PROCEDURE Find_Last_Adjustment (p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
933 p_adj_id OUT NOCOPY ar_adjustments.adjustment_id%TYPE )
934
935 IS
936
937 BEGIN
938
939 IF PG_DEBUG in ('Y', 'C') THEN
940 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Adjustment ()+');
941 END IF;
942
943 SELECT max(adjustment_id)
944 INTO p_adj_id
945 FROM ar_adjustments
946 WHERE customer_trx_id = p_customer_trx_id;
947
948 IF PG_DEBUG in ('Y', 'C') THEN
949 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Adjustment ()-');
950 END IF;
951
952 EXCEPTION
953 WHEN NO_DATA_FOUND THEN
954 IF PG_DEBUG in ('Y', 'C') THEN
955 arp_util.debug ('Find_Last_Adjustment: ' || 'No Endorsement Adjustment was found for the BR');
956 END IF;
957 RAISE;
958
959 WHEN OTHERS THEN
960 IF PG_DEBUG in ('Y', 'C') THEN
961 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Adjustment () ');
962 arp_util.debug('Find_Last_Adjustment: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
963 END IF;
964 RAISE;
965
966 END Find_Last_Adjustment;
967
968
969 /*==============================================================================+
970 | PROCEDURE |
971 | Approve_Adjustment |
972 | |
973 | DESCRIPTION |
974 | Calls the Adjustment API to approve an adjustment |
975 | |
976 +==============================================================================*/
977
978
979 PROCEDURE Approve_Adjustment ( p_adj_id IN ar_adjustments.adjustment_id%TYPE,
980 p_move_deferred_tax IN VARCHAR2 )
981 IS
982
983
984 l_msg_data VARCHAR2(2000) ;
985 l_msg_count NUMBER ;
986 l_return_status VARCHAR2(1) ;
987 l_adj_rec AR_ADJUSTMENTS%ROWTYPE ;
988
989 l_org_return_status VARCHAR2(1);
990 l_org_id NUMBER;
991
992 BEGIN
993
994 IF PG_DEBUG in ('Y', 'C') THEN
995 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Approve_Adjustment ()+');
996 END IF;
997
998 /* SSA change */
999 select org_id
1000 into l_org_id
1001 from ar_adjustments
1002 where adjustment_id = p_adj_id;
1003 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1004 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1005 p_return_status =>l_org_return_status);
1006
1007 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1008
1009 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Approve_Adjustment : l_org_return_status <> SUCCESS');
1010 ELSE
1011
1012
1013 IF PG_DEBUG in ('Y', 'C') THEN
1014 arp_util.debug( '--------------------------------------------------------------');
1015 arp_util.debug('PARAMETERS PASSED TO AR_ADJUST_PUB.Approve_Adjustment : ');
1016 arp_util.debug( 'p_old_adjust_id : ' || p_adj_id);
1017 arp_util.debug( 'p_chk_approval_limits : ' || 'F');
1018 arp_util.debug( 'p_move_deferred_tax : ' || p_move_deferred_tax);
1019 arp_util.debug( 'p_adj_rec.status : ' || l_adj_rec.status);
1020 arp_util.debug( 'p_org_id : ' || l_org_id);
1021 arp_util.debug( '--------------------------------------------------------------');
1022 END IF;
1023
1024
1025 AR_ADJUST_PUB.Approve_Adjustment (
1026 p_api_name => 'AR_ADJUST_PUB' ,
1027 p_api_version => 1.0 ,
1028 p_init_msg_list => FND_API.G_TRUE ,
1029 p_msg_count => l_msg_count ,
1030 p_msg_data => l_msg_data ,
1031 p_return_status => l_return_status ,
1032 p_adj_rec => l_adj_rec ,
1033 p_chk_approval_limits => FND_API.G_FALSE ,
1034 p_move_deferred_tax => p_move_deferred_tax ,
1035 p_old_adjust_id => p_adj_id ,
1036 p_org_id => l_org_id);
1037
1038 IF (l_return_status <> 'S')
1039 THEN
1040 IF PG_DEBUG in ('Y', 'C') THEN
1041 arp_util.debug( '>>>>>>>>>> Problems during Adjustment Approval');
1042 arp_util.debug( 'l_return_status : ' || l_return_status);
1043 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1044 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1045 END IF;
1046
1047
1048 IF (l_msg_count > 0)
1049 THEN
1050 Set_Api_Error;
1051 ELSE
1052 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_APPROVE_ADJ');
1053 app_exception.raise_exception;
1054 END IF;
1055
1056 END IF;
1057 END IF;
1058
1059 IF PG_DEBUG in ('Y', 'C') THEN
1060 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Approve_Adjustment ()-');
1061 END IF;
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 IF PG_DEBUG in ('Y', 'C') THEN
1066 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Approve_Adjustment : ' || SQLERRM);
1067 arp_util.debug( 'p_adj_id : ' || p_adj_id);
1068 END IF;
1069 RAISE;
1070
1071 END Approve_Adjustment;
1072
1073
1074 /*==============================================================================+
1075 | PROCEDURE |
1076 | Modify_Adjustment |
1077 | |
1078 | DESCRIPTION |
1079 | Calls the Adjustment API to modify the status of the adjustment |
1080 | |
1081 +==============================================================================*/
1082
1083
1084 PROCEDURE Modify_Adjustment ( p_adj_id IN AR_ADJUSTMENTS.adjustment_id%TYPE,
1085 p_status IN AR_ADJUSTMENTS.status%TYPE )
1086 IS
1087
1088
1089 l_msg_data VARCHAR2(2000) ;
1090 l_msg_count NUMBER ;
1091 l_return_status VARCHAR2(1) ;
1092 l_adj_rec ar_adjustments%ROWTYPE ;
1093 l_org_return_status VARCHAR2(1);
1094 l_org_id NUMBER;
1095
1096 BEGIN
1097
1098 IF PG_DEBUG in ('Y', 'C') THEN
1099 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Modify_Adjustment ()+');
1100 END IF;
1101
1102 /* SSA change */
1103 select org_id
1104 into l_org_id
1105 from ar_adjustments
1106 where adjustment_id = p_adj_id;
1107 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1108 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1109 p_return_status =>l_org_return_status);
1110
1111 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1112
1113 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Modify_Adjustment : l_org_return_status <> SUCCESS');
1114 ELSE
1115
1116 l_adj_rec.status := p_status;
1117
1118 IF PG_DEBUG in ('Y', 'C') THEN
1119 arp_util.debug( '--------------------------------------------------------------');
1120 arp_util.debug('PARAMETERS PASSED TO AR_ADJUST_PUB.Modify_Adjustment : ');
1121 arp_util.debug( 'p_old_adjust_id : ' || p_adj_id);
1122 arp_util.debug( 'p_chk_approval_limits : ' || 'F');
1123 arp_util.debug( 'p_adj_rec.status : ' || l_adj_rec.status);
1124 arp_util.debug( 'p_org_id : ' || l_org_id);
1125 arp_util.debug( '--------------------------------------------------------------');
1126 END IF;
1127
1128
1129 AR_ADJUST_PUB.Modify_Adjustment(
1130 p_api_name => 'AR_ADJUST_PUB' ,
1131 p_api_version => 1.0 ,
1132 p_init_msg_list => FND_API.G_TRUE ,
1133 p_msg_count => l_msg_count ,
1134 p_msg_data => l_msg_data ,
1135 p_return_status => l_return_status ,
1136 p_adj_rec => l_adj_rec ,
1137 p_chk_approval_limits => 'F' ,
1138 p_old_adjust_id => p_adj_id ,
1139 p_org_id => l_org_id );
1140
1141
1142 IF (l_return_status <> 'S')
1143 THEN
1144 IF PG_DEBUG in ('Y', 'C') THEN
1145 arp_util.debug( '>>>>>>>>>> Problems during Adjustment Modification');
1146 arp_util.debug( 'l_return_status : ' || l_return_status);
1147 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1148 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1149 END IF;
1150
1151
1152 IF (l_msg_count > 0)
1153 THEN
1154 Set_Api_Error;
1155 ELSE
1156 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_MODIFY_ADJ');
1157 app_exception.raise_exception;
1158 END IF;
1159
1160 END IF;
1161 END IF;
1162
1163
1164 IF PG_DEBUG in ('Y', 'C') THEN
1165 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Modify_Adjustment ()-');
1166 END IF;
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 IF PG_DEBUG in ('Y', 'C') THEN
1171 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Modify_Adjustment : ' || SQLERRM);
1172 arp_util.debug( 'p_adj_id : ' || p_adj_id);
1173 arp_util.debug( 'p_status : ' || p_status);
1174 END IF;
1175 RAISE;
1176
1177 END Modify_Adjustment;
1178
1179
1180 /*==============================================================================+
1181 | PROCEDURE |
1182 | Reverse_Adjustment |
1183 | |
1184 | DESCRIPTION |
1185 | Calls the Adjustment API to reverse an adjustment |
1186 | |
1187 +==============================================================================*/
1188
1189
1190 PROCEDURE Reverse_Adjustment ( p_adj_id IN ar_adjustments.adjustment_id%TYPE ,
1191 p_trh_rec IN ar_transaction_history%ROWTYPE ,
1192 p_called_from IN VARCHAR2 )
1193 IS
1194
1195
1196 l_msg_data VARCHAR2(2000) ;
1197 l_msg_count NUMBER ;
1198 l_return_status VARCHAR2(1) ;
1199 l_new_adjust_id NUMBER ;
1200
1201 l_org_return_status VARCHAR2(1);
1202 l_org_id NUMBER;
1203
1204 l_customer_trx ra_customer_trx%ROWTYPE;
1205
1206
1207 BEGIN
1208
1209 IF PG_DEBUG in ('Y', 'C') THEN
1210 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment ()+');
1211 END IF;
1212
1213 /* SSA change */
1214 l_org_id := p_trh_rec.org_id;
1215 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1216 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1217 p_return_status =>l_org_return_status);
1218
1219 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1220
1221 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment : l_org_return_status <> SUCCESS');
1222 ELSE
1223
1224 IF PG_DEBUG in ('Y', 'C') THEN
1225 arp_util.debug( '--------------------------------------------------------------');
1226 arp_util.debug('PARAMETERS PASSED TO AR_ADJUST_PUB.Reverse_Adjustment : ');
1227 arp_util.debug( 'p_old_adjust_id : ' || p_adj_id);
1228 arp_util.debug( 'p_reversal_date : ' || p_trh_rec.trx_date);
1229 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date);
1230 arp_util.debug( 'p_comments : ' || p_trh_rec.comments);
1231 arp_util.debug( 'p_chk_approval_limits : ' || 'F');
1232 arp_util.debug( 'p_move_deferred_tax : ' || 'Y');
1233 arp_util.debug( 'p_called_from : ' || p_called_from);
1234 arp_util.debug( 'p_org_id : ' || p_trh_rec.org_id);
1235 arp_util.debug( '--------------------------------------------------------------');
1236 END IF;
1237
1238 AR_ADJUST_PUB.Reverse_Adjustment (
1239 p_api_name => 'AR_ADJUST_PUB' ,
1240 p_api_version => 1.0 ,
1241 p_init_msg_list => FND_API.G_TRUE ,
1242 p_msg_count => l_msg_count ,
1243 p_msg_data => l_msg_data ,
1244 p_return_status => l_return_status ,
1245 p_old_adjust_id => p_adj_id ,
1246 p_reversal_date => p_trh_rec.trx_date ,
1247 p_reversal_gl_date => p_trh_rec.gl_date ,
1248 p_comments => p_trh_rec.comments ,
1249 p_chk_approval_limits => 'F' ,
1250 p_move_deferred_tax => 'Y' ,
1251 p_new_adj_id => l_new_adjust_id ,
1252 p_called_from => p_called_from ,
1253 p_org_id => p_trh_rec.org_id);
1254
1255 IF (l_return_status <> 'S')
1256 THEN
1257 IF PG_DEBUG in ('Y', 'C') THEN
1258 arp_util.debug( '>>>>>>>>>> Problems during Adjustment Reversal');
1259 arp_util.debug( 'l_return_status : ' || l_return_status);
1260 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1261 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1262 END IF;
1263
1264
1265 IF (l_msg_count > 0)
1266 THEN
1267 Set_Api_Error;
1268 ELSE
1269 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_REVERSE_ADJ');
1270 app_exception.raise_exception;
1271 END IF;
1272 ELSE -- Bug 8795268 Line Level for invoice is not maintained correctly.
1273 begin
1274
1275 select adj.customer_trx_id,ra.upgrade_method
1276 into l_customer_trx.customer_trx_id, l_customer_trx.upgrade_method
1277 from ar_adjustments adj,ra_customer_trx ra
1278 where adj.adjustment_id = l_new_adjust_id
1279 and ra.customer_trx_id = adj.customer_trx_id;
1280
1281 exception
1282 when others then
1283 arp_util.debug( '>>>>>>>>>> Problems during balance stamping select query');
1284 arp_util.debug ( 'Adjustment ID : ' || l_new_adjust_id);
1285 end;
1286
1287 IF l_customer_trx.upgrade_method = 'R12' THEN
1288 ARP_DET_DIST_PKG.set_original_rem_amt_r12(
1289 p_customer_trx => l_customer_trx,
1290 x_return_status => l_return_status,
1291 x_msg_count => l_msg_count,
1292 x_msg_data => l_msg_data,
1293 p_from_llca => 'N');
1294
1295 IF (l_return_status <> 'S')
1296 THEN
1297 IF PG_DEBUG in ('Y', 'C') THEN
1298 arp_util.debug( '>>>>>>>>>> Problems balance stamping');
1299 arp_util.debug( 'l_return_status : ' || l_return_status);
1300 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1301 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1302 END IF;
1303 END IF;
1304 END IF;
1305
1306 END IF;
1307 END IF;
1308
1309
1310
1311 IF PG_DEBUG in ('Y', 'C') THEN
1312 arp_util.debug ( 'Adjustment ID : ' || l_new_adjust_id);
1313 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment ()-');
1314 END IF;
1315
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 IF PG_DEBUG in ('Y', 'C') THEN
1319 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment : ' || SQLERRM);
1320 arp_util.debug( 'p_adj_id : ' || p_adj_id);
1321 END IF;
1322 RAISE;
1323
1324 END Reverse_Adjustment;
1325
1326
1327
1328 /*==============================================================================+
1329 | PROCEDURE |
1330 | Reverse_Assignments_Adjustment |
1331 | |
1332 | DESCRIPTION |
1333 | Reverse the adjustments created when standard transactions are exchanged|
1334 | Remove the adjustment ID in the assignment information |
1335 | Update the reserved columns of the PS of the exchanged transactions |
1336 | |
1337 | Called by UNCOMPLETE_BR and CANCEL_BR |
1338 | |
1339 +==============================================================================*/
1340
1341
1342 PROCEDURE Reverse_Assignments_Adjustment ( p_trh_rec IN AR_TRANSACTION_HISTORY%ROWTYPE ,
1343 p_acceptance_flag IN VARCHAR2 )
1344 IS
1345
1346 CURSOR assignment_cur IS
1347 SELECT br_adjustment_id, br_ref_payment_schedule_id, customer_trx_line_id, br_ref_customer_trx_id
1348 FROM ra_customer_trx_lines
1349 WHERE customer_trx_id = p_trh_rec.customer_trx_id;
1350
1351 assignment_rec assignment_cur%ROWTYPE ;
1352
1353 l_msg_data VARCHAR2(2000) ;
1354 l_msg VARCHAR2(240) ;
1355 l_msg_count NUMBER ;
1356 l_return_status VARCHAR2(1) ;
1357 l_ps_rec ar_payment_schedules%ROWTYPE ;
1358 l_trx_rec ra_customer_trx%ROWTYPE ;
1359 l_trh_rec ar_transaction_history%ROWTYPE ;
1360
1361 BEGIN
1362
1363 IF PG_DEBUG in ('Y', 'C') THEN
1364 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Assignments_Adjustment ()+');
1365 END IF;
1366
1367 /*------------------------------------------------------+
1368 | For each assignment, non accounting adjustment |
1369 | is reversed through the adjustment API |
1370 +-------------------------------------------------------*/
1371
1372 FOR assignment_rec IN assignment_cur LOOP
1373
1374 IF (p_acceptance_flag = 'N')
1375 THEN
1376
1377 Reverse_Adjustment (assignment_rec.br_adjustment_id, p_trh_rec, NULL);
1378
1379 /*----------------------------------------------+
1380 | Removes the Adjustment ID in the Assignment |
1381 | Information |
1382 +-----------------------------------------------*/
1383
1384
1385 arp_ctl_pkg.lock_p (assignment_rec.customer_trx_line_id);
1386
1387 UPDATE ra_customer_trx_lines
1388 SET br_adjustment_id = NULL
1389 WHERE customer_trx_line_id = assignment_rec.customer_trx_line_id;
1390
1391
1392 /*----------------------------------------------+
1393 | If the exchanged transaction is a BR, create |
1394 | an history record for the exchanged BR |
1395 +-----------------------------------------------*/
1396
1397 arp_ps_pkg.fetch_p (assignment_rec.br_ref_payment_schedule_id, l_ps_rec);
1398
1399 IF (AR_BILLS_CREATION_VAL_PVT.Is_Transaction_BR (l_ps_rec.cust_trx_type_id))
1400 THEN
1401
1402 ARP_CT_PKG.fetch_p (l_trx_rec, assignment_rec.br_ref_customer_trx_id);
1403
1404 l_trh_rec.customer_trx_id := assignment_rec.br_ref_customer_trx_id;
1405 ARP_TRANSACTION_HISTORY_PKG.lock_fetch_f_trx_id (l_trh_rec);
1406
1407 -- Fetch the new status and new event of the BR
1408 AR_BILLS_MAINTAIN_STATUS_PUB.New_Status_Event (
1409 p_trx_rec => l_trx_rec ,
1410 p_action => C_EXCHANGE_UNCOMPLETE ,
1411 p_new_status => l_trh_rec.status ,
1412 p_new_event => l_trh_rec.event );
1413
1414 l_trh_rec.transaction_history_id:= NULL ;
1415 l_trh_rec.current_record_flag := 'Y' ;
1416 l_trh_rec.prv_trx_history_id := NULL ;
1417 l_trh_rec.posting_control_id := -3 ;
1418 l_trh_rec.gl_posted_date := NULL ;
1419 l_trh_rec.first_posted_record_flag := 'N' ;
1420 l_trh_rec.created_from := 'ARBRMAIB' ;
1421 l_trh_rec.postable_flag := 'N' ;
1422 l_trh_rec.current_accounted_flag:= 'N' ;
1423
1424 arp_proc_transaction_history.insert_transaction_history (l_trh_rec ,
1425 l_trh_rec.transaction_history_id);
1426 END IF;
1427
1428 ELSE
1429
1430 update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, NULL, NULL);
1431 END IF;
1432
1433
1434 END LOOP;
1435
1436 IF PG_DEBUG in ('Y', 'C') THEN
1437 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Assignments_Adjustment ()-');
1438 END IF;
1439
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 IF PG_DEBUG in ('Y', 'C') THEN
1443 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Assignments_Adjustment () ');
1444 arp_util.debug('Reverse_Assignments_Adjustment: ' || 'p_customer_trx_id : ' || p_trh_rec.customer_trx_id);
1445 arp_util.debug('Reverse_Assignments_Adjustment: ' || 'p_acceptance_flag : ' || p_acceptance_flag);
1446 END IF;
1447 IF (assignment_cur%ISOPEN)
1448 THEN
1449 CLOSE assignment_cur;
1450 END IF;
1451 RAISE;
1452
1453 END Reverse_Assignments_Adjustment;
1454
1455
1456 /*==============================================================================+
1457 | PROCEDURE |
1458 | Find_Last_Receipt |
1459 | |
1460 | DESCRIPTION |
1461 | Find the last receipt applied on the BR |
1462 | |
1463 +==============================================================================*/
1464
1465
1466 PROCEDURE Find_Last_Receipt ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
1467 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE )
1468
1469 IS
1470
1471 BEGIN
1472
1473 IF PG_DEBUG in ('Y', 'C') THEN
1474 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt ()+');
1475 END IF;
1476
1477 SELECT cash_receipt_id
1478 INTO p_cash_receipt_id
1479 FROM ar_receivable_applications
1480 where receivable_application_id = (SELECT MAX(receivable_application_id)
1481 FROM ar_receivable_applications
1482 WHERE applied_customer_trx_id = p_customer_trx_id
1483 AND status = 'APP' );
1484
1485 IF PG_DEBUG in ('Y', 'C') THEN
1486 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt ()-');
1487 END IF;
1488
1489 EXCEPTION
1490 WHEN NO_DATA_FOUND THEN
1491 IF PG_DEBUG in ('Y', 'C') THEN
1492 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt () ');
1493 arp_util.debug ('Find_Last_Receipt: ' || 'No receipt was found for the BR');
1494 END IF;
1495 RAISE;
1496
1497 WHEN OTHERS THEN
1498 IF PG_DEBUG in ('Y', 'C') THEN
1499 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt () ');
1500 arp_util.debug('Find_Last_Receipt: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
1501 END IF;
1502 RAISE;
1503
1504 END Find_Last_Receipt;
1505
1506
1507 /*==============================================================================+
1508 | PROCEDURE |
1509 | Find_Last_STD |
1510 | |
1511 | DESCRIPTION |
1512 | Find the last STD application on the BR |
1513 | |
1514 +==============================================================================*/
1515
1516
1517 PROCEDURE Find_Last_STD ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
1518 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE ,
1519 p_receivable_application_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE)
1520
1521 IS
1522
1523 CURSOR last_std_application_cur IS
1524 SELECT receivable_application_id, cash_receipt_id
1525 FROM ar_receivable_applications
1526 WHERE link_to_customer_trx_id = p_customer_trx_id
1527 AND status = 'ACTIVITY'
1528 AND applied_payment_schedule_id = -2
1529 AND display = 'Y'
1530 ORDER BY receivable_application_id DESC;
1531
1532 last_std_application_rec last_std_application_cur%ROWTYPE;
1533
1534
1535 BEGIN
1536
1537 IF PG_DEBUG in ('Y', 'C') THEN
1538 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_STD ()+');
1539 END IF;
1540
1541 /*-----------------------------------------------+
1542 | Fetch last Short Term Debt Application |
1543 +------------------------------------------------*/
1544
1545 OPEN last_std_application_cur;
1546 FETCH last_std_application_cur INTO last_std_application_rec;
1547
1548 IF (last_std_application_cur%NOTFOUND)
1549 THEN
1550 IF PG_DEBUG in ('Y', 'C') THEN
1551 arp_util.debug ('Find_Last_STD: ' || '>>>>>>>>>> Last Short Term Debt Application could not be found');
1552 END IF;
1553 CLOSE last_std_application_cur;
1554 APP_EXCEPTION.raise_exception;
1555 END IF;
1556
1557 p_cash_receipt_id := last_std_application_rec.cash_receipt_id;
1558 p_receivable_application_id := last_std_application_rec.receivable_application_id;
1559
1560 CLOSE last_std_application_cur;
1561
1562
1563 IF PG_DEBUG in ('Y', 'C') THEN
1564 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_STD ()-');
1565 END IF;
1566
1567 EXCEPTION
1568
1569 WHEN OTHERS THEN
1570 IF PG_DEBUG in ('Y', 'C') THEN
1571 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_STD : ' || SQLERRM);
1572 END IF;
1573 IF (last_std_application_cur%ISOPEN)
1574 THEN
1575 CLOSE last_std_application_cur;
1576 END IF;
1577 RAISE;
1578
1579 END Find_Last_STD;
1580
1581
1582
1583 /*==============================================================================+
1584 | PROCEDURE |
1585 | Reverse_Receipt |
1586 | |
1587 | DESCRIPTION |
1588 | Reverses a receipt with the receipt API |
1589 | |
1590 +==============================================================================*/
1591
1592
1593 PROCEDURE Reverse_Receipt ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
1594 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
1595 p_reversal_reason IN VARCHAR2 ,
1596 p_called_from IN VARCHAR2 )
1597 IS
1598
1599 l_return_status VARCHAR2(1);
1600 l_msg_count NUMBER;
1601 l_msg_data VARCHAR2(2000);
1602 l_count NUMBER;
1603 l_org_return_status VARCHAR2(1);
1604 l_org_id NUMBER;
1605
1606 BEGIN
1607
1608 IF PG_DEBUG in ('Y', 'C') THEN
1609 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Receipt ()+');
1610 END IF;
1611
1612 /* SSA change */
1613 l_org_id := p_trh_rec.org_id;
1614 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1615 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1616 p_return_status =>l_org_return_status);
1617
1618 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1619
1620 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_receipt : l_org_return_status <> SUCCESS');
1621 ELSE
1622 /*------------------------------------------------------+
1623 | Reverse a receipt using Receipt API |
1624 +-------------------------------------------------------*/
1625
1626 IF PG_DEBUG in ('Y', 'C') THEN
1627 arp_util.debug( '--------------------------------------------------------------');
1628 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.Reverse : ' );
1629 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id );
1630 arp_util.debug( 'p_reversal_category_code : ' || 'REV' );
1631 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date );
1632 arp_util.debug( 'p_reversal_date : ' || p_trh_rec.trx_date );
1633 arp_util.debug( 'p_called_from : ' || p_called_from );
1634 arp_util.debug( 'p_reversal_reason_code : ' || p_reversal_reason );
1635 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
1636 arp_util.debug( '--------------------------------------------------------------');
1637 END IF;
1638
1639 AR_RECEIPT_API_PUB.Reverse (
1640 p_api_version => 1.0 ,
1641 p_init_msg_list => FND_API.G_TRUE ,
1642 x_return_status => l_return_status ,
1643 x_msg_count => l_msg_count ,
1644 x_msg_data => l_msg_data ,
1645 p_cash_receipt_id => p_cash_receipt_id ,
1646 p_reversal_category_code => 'REV' ,
1647 p_reversal_gl_date => p_trh_rec.gl_date ,
1648 p_reversal_date => p_trh_rec.trx_date ,
1649 p_called_from => p_called_from ,
1650 p_reversal_reason_code => p_reversal_reason ,
1651 p_org_id => p_trh_rec.org_id);
1652
1653
1654 IF PG_DEBUG in ('Y', 'C') THEN
1655 arp_util.debug ( 'REVERSAL RECEIPT API Return status : ' || l_return_status);
1656 arp_util.debug ( ' l_msg_count : ' || to_char(l_msg_count));
1657 END IF;
1658
1659
1660 IF (l_return_status <> 'S')
1661 THEN
1662 IF PG_DEBUG in ('Y', 'C') THEN
1663 arp_util.debug( '>>>>>>>>>> Problems during Receipt Reversal');
1664 arp_util.debug( 'l_return_status : ' || l_return_status);
1665 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1666 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1667 END IF;
1668
1669 IF (l_msg_count > 0)
1670 THEN
1671 Set_Api_Error;
1672 ELSE
1673 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_REVERSE_REC');
1674 app_exception.raise_exception;
1675 END IF;
1676
1677 END IF;
1678 END IF;
1679
1680 IF PG_DEBUG in ('Y', 'C') THEN
1681 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Receipt ()-');
1682 END IF;
1683
1684 EXCEPTION
1685 WHEN OTHERS THEN
1686 IF PG_DEBUG in ('Y', 'C') THEN
1687 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Receipt : ' || SQLERRM);
1688 arp_util.debug ( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1689 arp_util.debug ( 'p_reversal_reason : ' || p_reversal_reason);
1690 arp_util.debug ( 'p_called_from : ' || p_called_from);
1691 END IF;
1692 RAISE;
1693
1694 END Reverse_Receipt;
1695
1696
1697 /*==============================================================================+
1698 | PROCEDURE |
1699 | Unapply_Receipt |
1700 | |
1701 | DESCRIPTION |
1702 | Unapplies a receipt using the receipt API |
1703 | |
1704 +==============================================================================*/
1705
1706
1707 PROCEDURE Unapply_Receipt ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
1708 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
1709 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
1710 p_called_from IN VARCHAR2 )
1711 IS
1712
1713 l_return_status VARCHAR2(1);
1714 l_msg_count NUMBER;
1715 l_msg_data VARCHAR2(2000);
1716 l_count NUMBER;
1717 l_org_return_status VARCHAR2(1);
1718 l_org_id NUMBER;
1719
1720 BEGIN
1721
1722 IF PG_DEBUG in ('Y', 'C') THEN
1723 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt ()+');
1724 END IF;
1725
1726 /* SSA change */
1727 l_org_id := p_trh_rec.org_id;
1728 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1729 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1730 p_return_status =>l_org_return_status);
1731
1732 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1733
1734 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt : l_org_return_status <> SUCCESS');
1735 ELSE
1736
1737 /*------------------------------------------------------+
1738 | Unapply a receipt using Receipt API |
1739 +-------------------------------------------------------*/
1740
1741 IF PG_DEBUG in ('Y', 'C') THEN
1742 arp_util.debug( '--------------------------------------------------------------');
1743 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.Unapply : ' );
1744 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1745 arp_util.debug( 'p_customer_trx_id : ' || p_trh_rec.customer_trx_id);
1746 arp_util.debug( 'p_applied_payment_schedule_id : ' || p_ps_id);
1747 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date);
1748 arp_util.debug( 'p_called_from : ' || p_called_from);
1749 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
1750 arp_util.debug( '--------------------------------------------------------------');
1751 END IF;
1752
1753 AR_RECEIPT_API_PUB.Unapply (
1754 p_api_version => 1.0 ,
1755 p_init_msg_list => FND_API.G_TRUE ,
1756 x_return_status => l_return_status ,
1757 x_msg_count => l_msg_count ,
1758 x_msg_data => l_msg_data ,
1759 p_cash_receipt_id => p_cash_receipt_id ,
1760 p_customer_trx_id => p_trh_rec.customer_trx_id,
1761 p_applied_payment_schedule_id => p_ps_id ,
1762 p_reversal_gl_date => p_trh_rec.gl_date ,
1763 p_called_from => p_called_from ,
1764 p_org_id => p_trh_rec.org_id);
1765
1766
1767 IF PG_DEBUG in ('Y', 'C') THEN
1768 arp_util.debug ( 'UNAPPLY RECEIPT API Return status : ' || l_return_status);
1769 arp_util.debug ( ' l_msg_count : ' || to_char(l_msg_count));
1770 END IF;
1771
1772
1773 IF (l_return_status <> 'S')
1774 THEN
1775 IF PG_DEBUG in ('Y', 'C') THEN
1776 arp_util.debug( '>>>>>>>>>> Problems during Receipt Unapplication');
1777 arp_util.debug( 'l_return_status : ' || l_return_status);
1778 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1779 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1780 END IF;
1781
1782
1783 IF (l_msg_count > 0)
1784 THEN
1785 Set_Api_Error;
1786 ELSE
1787 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_UNAPPLY_REC');
1788 app_exception.raise_exception;
1789 END IF;
1790
1791 END IF;
1792 END IF;
1793
1794
1795 IF PG_DEBUG in ('Y', 'C') THEN
1796 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt ()-');
1797 END IF;
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801 IF PG_DEBUG in ('Y', 'C') THEN
1802 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt : ' || SQLERRM);
1803 arp_util.debug ( 'p_cash_receipt_id : ' || p_cash_receipt_id );
1804 arp_util.debug ( 'p_called_from : ' || p_called_from );
1805 arp_util.debug ( 'p_ps_id : ' || p_ps_id );
1806 arp_util.debug ( 'p_trh_rec.customer_trx_id : ' || p_trh_rec.customer_trx_id );
1807 END IF;
1808 RAISE;
1809
1810 END Unapply_Receipt;
1811
1812
1813 /*==============================================================================+
1814 | PROCEDURE |
1815 | Apply_STD |
1816 | |
1817 | DESCRIPTION |
1818 | Applies the receipt to Short Term Debt |
1819 | |
1820 +==============================================================================*/
1821
1822
1823 PROCEDURE Apply_STD ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
1824 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
1825 p_apply_date IN DATE ,
1826 p_apply_gl_date IN DATE )
1827
1828
1829 IS
1830
1831 l_msg_count NUMBER;
1832 l_msg_data VARCHAR2(2000);
1833 l_return_status VARCHAR2(1);
1834
1835 l_receivables_trx_id ar_receivable_applications.receivables_trx_id%TYPE;
1836 l_cr_rec ar_cash_receipts%ROWTYPE;
1837
1838 l_secondary_application_ref_id NUMBER:= NULL;
1839 l_application_ref_type VARCHAR2(30):= NULL;
1840 l_application_ref_id NUMBER:= NULL;
1841 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
1842 ln_rec_application_id ar_receivable_applications.receivable_application_id%type;
1843
1844 l_org_return_status VARCHAR2(1);
1845 l_org_id NUMBER;
1846 BEGIN
1847
1848 IF PG_DEBUG in ('Y', 'C') THEN
1849 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD ()+');
1850 END IF;
1851
1852
1853 /* SSA change */
1854 l_org_id := l_cr_rec.org_id;
1855 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1856 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1857 p_return_status =>l_org_return_status);
1858
1859 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1860
1861 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD l_org_return_status <> SUCCESS');
1862 ELSE
1863
1864 /*------------------------------------------------------+
1865 | Fetch the Cash Receipt Information |
1866 +-------------------------------------------------------*/
1867
1868 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
1869
1870 arp_cash_receipts_pkg.fetch_p (l_cr_rec);
1871
1872
1873 /*------------------------------------------------------+
1874 | Fetch the Receivables activity used for STD |
1875 +-------------------------------------------------------*/
1876
1877 SELECT br_std_receivables_trx_id
1878 INTO l_receivables_trx_id
1879 FROM ar_receipt_method_accounts
1880 WHERE remit_bank_acct_use_id = l_cr_rec.remit_bank_acct_use_id
1881 AND receipt_method_id = l_cr_rec.receipt_method_id;
1882
1883
1884 /*------------------------------------------------------+
1885 | Apply receipt to Short Term Debt using Receipt API |
1886 +-------------------------------------------------------*/
1887
1888 IF PG_DEBUG in ('Y', 'C') THEN
1889 arp_util.debug( '--------------------------------------------------------------');
1890 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.Activity_Application : ');
1891 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1892 arp_util.debug( 'p_amount_applied : ' || l_cr_rec.amount);
1893 arp_util.debug( 'p_applied_payment_schedule : ' || '-2');
1894 arp_util.debug( 'p_link_to_customer_trx_id : ' || p_customer_trx_id);
1895 arp_util.debug( 'p_receivables_trx_id : ' || l_receivables_trx_id);
1896 arp_util.debug( 'p_apply_date : ' || p_apply_date);
1897 arp_util.debug( 'p_apply_gl_date : ' || p_apply_gl_date);
1898 arp_util.debug( 'p_org_id : ' || to_char(l_cr_rec.org_id));
1899 arp_util.debug( '--------------------------------------------------------------');
1900 END IF;
1901
1902 ar_receipt_api_pub.activity_application(
1903 p_api_version => 1.0,
1904 p_init_msg_list => FND_API.G_TRUE,
1905 x_return_status => l_return_status,
1906 x_msg_count => l_msg_count,
1907 x_msg_data => l_msg_data,
1908 p_cash_receipt_id => p_cash_receipt_id,
1909 p_amount_applied => l_cr_rec.amount,
1910 p_applied_payment_schedule_id => -2,
1911 p_link_to_customer_trx_id => p_customer_trx_id,
1912 p_receivables_trx_id => l_receivables_trx_id ,
1913 p_apply_date => p_apply_date,
1914 p_apply_gl_date => p_apply_gl_date,
1915 p_secondary_application_ref_id => l_secondary_application_ref_id,
1916 p_application_ref_type => l_application_ref_type,
1917 p_application_ref_id => l_application_ref_id,
1918 p_application_ref_num => l_application_ref_num,
1919 p_receivable_application_id => ln_rec_application_id,
1920 p_org_id => l_cr_rec.org_id);
1921
1922
1923 IF (l_return_status <> 'S')
1924 THEN
1925 IF PG_DEBUG in ('Y', 'C') THEN
1926 arp_util.debug( '>>>>>>>>>> Problems during SHORT TERM DEBT APPLICATION');
1927 arp_util.debug( 'l_return_status : ' || l_return_status);
1928 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1929 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1930 END IF;
1931
1932
1933 IF (l_msg_count > 0)
1934 THEN
1935 Set_Api_Error;
1936 ELSE
1937 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_APPLY_REC');
1938 app_exception.raise_exception;
1939 END IF;
1940
1941 END IF;
1942 END IF;
1943
1944
1945 IF PG_DEBUG in ('Y', 'C') THEN
1946 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD ()-');
1947 END IF;
1948
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 IF PG_DEBUG in ('Y', 'C') THEN
1952 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD : ' || SQLERRM);
1953 END IF;
1954 RAISE;
1955
1956 END Apply_STD;
1957
1958
1959
1960
1961 /*==============================================================================+
1962 | PROCEDURE |
1963 | Unapply_STD |
1964 | |
1965 | DESCRIPTION |
1966 | Unapply the receipt from STD |
1967 | |
1968 +==============================================================================*/
1969
1970
1971 PROCEDURE Unapply_STD ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
1972 p_called_from IN VARCHAR2 ,
1973 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE )
1974
1975 IS
1976
1977
1978 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1979
1980 l_msg_count NUMBER;
1981 l_msg_data VARCHAR2(2000);
1982 l_return_status VARCHAR2(1);
1983 l_org_return_status VARCHAR2(1);
1984 l_org_id NUMBER;
1985
1986 BEGIN
1987
1988 IF PG_DEBUG in ('Y', 'C') THEN
1989 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD ()+');
1990 END IF;
1991
1992 /* SSA change */
1993 l_org_id := p_trh_rec.org_id;
1994 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1995 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1996 p_return_status =>l_org_return_status);
1997
1998 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1999
2000 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD : l_org_return_status <> SUCCESS');
2001 ELSE
2002
2003 /*-----------------------------------------------+
2004 | Fetch last Short Term Debt Application |
2005 +------------------------------------------------*/
2006
2007 Find_last_STD (p_trh_rec.customer_trx_id, p_cash_receipt_id, l_receivable_application_id);
2008
2009
2010 /*-----------------------------------------------+
2011 | Unapply from Short Term Debt using Receipt API |
2012 +------------------------------------------------*/
2013
2014 IF PG_DEBUG in ('Y', 'C') THEN
2015 arp_util.debug( '----------------------------------------------------------------');
2016 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.ACTIVITY_UNAPPLICATION : ');
2017 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
2018 arp_util.debug( 'p_receivable_application_id: ' || l_receivable_application_id);
2019 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date);
2020 arp_util.debug( 'p_called_from : ' || p_called_from);
2021 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
2022 arp_util.debug( '----------------------------------------------------------------');
2023 END IF;
2024
2025 AR_RECEIPT_API_PUB.Activity_Unapplication(
2026 p_api_version => 1.0 ,
2027 p_init_msg_list => FND_API.G_TRUE ,
2028 x_return_status => l_return_status ,
2029 x_msg_count => l_msg_count ,
2030 x_msg_data => l_msg_data ,
2031 p_cash_receipt_id => p_cash_receipt_id,
2032 p_receivable_application_id => l_receivable_application_id,
2033 p_reversal_gl_date => p_trh_rec.gl_date,
2034 p_called_from => p_called_from,
2035 p_org_id => p_trh_rec.org_id);
2036
2037
2038 IF (l_return_status <> 'S')
2039 THEN
2040 IF PG_DEBUG in ('Y', 'C') THEN
2041 arp_util.debug( '>>>>>>>>>> Problems during SHORT TERM DEBT UNAPPLICATION');
2042 arp_util.debug( 'l_return_status : ' || l_return_status);
2043 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2044 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2045 END IF;
2046
2047
2048 IF (l_msg_count > 0)
2049 THEN
2050 Set_Api_Error;
2051 ELSE
2052 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_UNAPPLY_REC');
2053 app_exception.raise_exception;
2054 END IF;
2055
2056 END IF;
2057 END IF;
2058
2059 IF PG_DEBUG in ('Y', 'C') THEN
2060 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD ()-');
2061 END IF;
2062
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 IF PG_DEBUG in ('Y', 'C') THEN
2066 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD : ' || SQLERRM);
2067 END IF;
2068 RAISE;
2069
2070 END Unapply_STD;
2071
2072
2073
2074 /*==============================================================================+
2075 | PROCEDURE |
2076 | Unpaid |
2077 | |
2078 | DESCRIPTION |
2079 | BR - Unpaid |
2080 | |
2081 +==============================================================================*/
2082
2083 PROCEDURE Unpaid ( p_trh_rec IN OUT NOCOPY ar_transaction_history%ROWTYPE ,
2084 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
2085 p_remittance_batch_id IN ra_customer_trx.remittance_batch_id%TYPE,
2086 p_unpaid_reason IN VARCHAR2 )
2087 IS
2088
2089 l_adj_id ar_adjustments.adjustment_id%TYPE ;
2090 l_batch_rec ar_batches%ROWTYPE ;
2091 l_called_from VARCHAR2(30) := NULL ;
2092 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE ;
2093 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
2094 l_prev_trh_rec AR_TRANSACTION_HISTORY%ROWTYPE ;
2095
2096 BEGIN
2097
2098 IF PG_DEBUG in ('Y', 'C') THEN
2099 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unpaid ()+');
2100 END IF;
2101
2102 -- Fetch the remittance method if it exists
2103
2104 IF (p_remittance_batch_id IS NOT NULL)
2105 THEN
2106
2107 arp_cr_batches_pkg.fetch_p (p_remittance_batch_id, l_batch_rec);
2108
2109 IF (l_batch_rec.remit_method_code = C_STANDARD)
2110 THEN
2111 l_called_from := C_BR_REMITTED;
2112
2113 ELSIF (l_batch_rec.remit_method_code = C_FACTORING AND l_batch_rec.with_recourse_flag = 'Y')
2114 THEN
2115 l_called_from := C_BR_FACTORED_RECOURSE;
2116
2117 ELSIF (l_batch_rec.remit_method_code = C_FACTORING AND l_batch_rec.with_recourse_flag = 'N')
2118 THEN
2119 l_called_from := C_BR_FACTORED;
2120 END IF;
2121 END IF;
2122
2123
2124
2125 -- Fetch the previous relevant status of the BR
2126
2127 l_prev_trh_rec.transaction_history_id := p_trh_rec.prv_trx_history_id;
2128
2129 AR_BILLS_MAINTAIN_STATUS_PUB.Find_Last_Relevant_trh (l_prev_trh_rec);
2130
2131 IF PG_DEBUG in ('Y', 'C') THEN
2132 arp_util.debug ( 'Previous Relevant Status : ' || l_prev_trh_rec.status);
2133 END IF;
2134
2135
2136
2137 /*----------------------------------------------+
2138 | BR Pending Remittance is Unpaid OR |
2139 | BR Standard Remitted is Unpaid |
2140 +-----------------------------------------------*/
2141
2142 IF (p_trh_rec.status = C_PENDING_REMITTANCE)
2143 OR (p_trh_rec.status = C_REMITTED)
2144 THEN
2145 update_reserved_columns (p_ps_id, NULL , NULL);
2146 p_trh_rec.postable_flag := 'Y';
2147 p_trh_rec.current_accounted_flag:= 'Y';
2148
2149
2150 /*----------------------------------------------+
2151 | Protested BR is Unpaid |
2152 +-----------------------------------------------*/
2153
2154 ELSIF (p_trh_rec.status = C_PROTESTED)
2155 THEN
2156 p_trh_rec.postable_flag := 'N';
2157 p_trh_rec.current_accounted_flag:= 'N';
2158
2159
2160 /*----------------------------------------------+
2161 | BR Endorsed and Closed is Unpaid |
2162 +-----------------------------------------------*/
2163
2164 ELSIF (p_trh_rec.status = C_CLOSED) AND (l_prev_trh_rec.status = C_ENDORSED)
2165 THEN
2166
2167 /*----------------------------------------------+
2168 | Reverse the adjustment which closed the BR |
2169 +-----------------------------------------------*/
2170
2171 Find_Last_Adjustment (p_trh_rec.customer_trx_id, l_adj_id);
2172 Reverse_Adjustment (l_adj_id, p_trh_rec, NULL);
2173 p_trh_rec.postable_flag := 'Y';
2174 p_trh_rec.current_accounted_flag:= 'Y';
2175
2176
2177
2178 /*----------------------------------------------+
2179 | BR Remitted And Closed is Unpaid OR |
2180 | BR Factored And Closed is Unpaid OR |
2181 | BR Paid by a single Receipt is Unpaid |
2182 +-----------------------------------------------*/
2183
2184 ELSIF (p_trh_rec.status = C_CLOSED) AND
2185 (p_trh_rec.event = C_CLOSED OR p_trh_rec.event = C_RISK_ELIMINATED)
2186 THEN
2187
2188 /*----------------------------------------------+
2189 | Reverse the receipt which paid the BR |
2190 +-----------------------------------------------*/
2191
2192 Find_Last_Receipt (p_trh_rec.customer_trx_id, l_cash_receipt_id);
2193 Reverse_Receipt (p_trh_rec, l_cash_receipt_id, p_unpaid_reason, l_called_from);
2194 p_trh_rec.postable_flag := 'Y';
2195 p_trh_rec.current_accounted_flag:= 'Y';
2196
2197
2198 /*----------------------------------------------+
2199 | BR Matured Pend Risk Elim is Unpaid |
2200 +-----------------------------------------------*/
2201
2202 ELSIF (p_trh_rec.status = C_MATURED_PEND_RISK_ELIM)
2203 THEN
2204
2205 /*----------------------------------------------+
2206 | Unapply the receipt from Short Term Debt |
2207 | And Reverse the receipt |
2208 +-----------------------------------------------*/
2209
2210 Find_Last_STD (p_trh_rec.customer_trx_id, l_cash_receipt_id, l_receivable_application_id);
2211 Reverse_Receipt (p_trh_rec, l_cash_receipt_id, p_unpaid_reason, l_called_from);
2212 update_reserved_columns (p_ps_id, NULL , NULL);
2213 p_trh_rec.postable_flag := 'Y';
2214 p_trh_rec.current_accounted_flag:= 'Y';
2215
2216
2217
2218 /*----------------------------------------------+
2219 | BR Endorsed is Unpaid |
2220 +-----------------------------------------------*/
2221
2222 ELSIF (p_trh_rec.status = C_ENDORSED)
2223 THEN
2224
2225 /*----------------------------------------------+
2226 | Reject the adjustment created for the BR |
2227 +-----------------------------------------------*/
2228
2229 Find_Last_Adjustment (p_trh_rec.customer_trx_id, l_adj_id);
2230 Modify_Adjustment (l_adj_id, 'R');
2231 update_reserved_columns (p_ps_id, NULL , NULL);
2232 p_trh_rec.postable_flag := 'Y';
2233 p_trh_rec.current_accounted_flag:= 'Y';
2234
2235 ELSE
2236 IF PG_DEBUG in ('Y', 'C') THEN
2237 arp_util.debug( '>>>>>>>>>> Invalid Action - Case not implemented ');
2238 END IF;
2239 FND_MESSAGE.set_name ( 'AR', 'AR_BR_ACTION_FORBIDDEN' );
2240 FND_MESSAGE.Set_token ( 'ACTION', C_UNPAID);
2241 app_exception.raise_exception;
2242
2243 END IF;
2244
2245
2246 IF PG_DEBUG in ('Y', 'C') THEN
2247 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unpaid ()-');
2248 END IF;
2249
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 IF PG_DEBUG in ('Y', 'C') THEN
2253 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Unpaid () ');
2254 END IF;
2255 RAISE;
2256
2257 END Unpaid;
2258
2259
2260 /*==============================================================================+
2261 | PROCEDURE |
2262 | Factore_Recourse |
2263 | |
2264 | DESCRIPTION |
2265 | BR - Factore with Recourse is approved : |
2266 | Create a receipt and apply it to Short Term Debt |
2267 | |
2268 +==============================================================================*/
2269
2270
2271 PROCEDURE Factore_Recourse ( p_batch_rec IN ar_batches%ROWTYPE ,
2272 p_ps_rec IN ar_payment_schedules%ROWTYPE ,
2273 p_trh_rec IN ar_transaction_history%ROWTYPE )
2274
2275 IS
2276
2277 l_cr_id NUMBER;
2278 l_count NUMBER;
2279 l_msg_count NUMBER;
2280 l_msg_data VARCHAR2(2000);
2281 l_return_status VARCHAR2(1);
2282
2283 l_receipt_number VARCHAR2(30) := NULL;
2284 l_receipt_inherit_inv_num_flag VARCHAR2(1);
2285
2286 l_called_from VARCHAR2(30) := C_BR_FACTORED_RECOURSE;
2287 l_exch_rate NUMBER;
2288
2289 l_org_return_status VARCHAR2(1);
2290 l_org_id NUMBER;
2291
2292 BEGIN
2293
2294 IF PG_DEBUG in ('Y', 'C') THEN
2295 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse ()+');
2296 END IF;
2297
2298
2299 /* SSA change */
2300 l_org_id := p_ps_rec.org_id;
2301 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
2302 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2303 p_return_status =>l_org_return_status);
2304
2305 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2306
2307 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse : l_org_return_status <> SUCCESS');
2308 ELSE
2309
2310 /*-----------------------------------------------+
2311 | Check if the receipt number is inherited |
2312 | from the BR. If not inherited, the receipt |
2313 | API will default it from sequence. |
2314 +------------------------------------------------*/
2315
2316 SELECT receipt_inherit_inv_num_flag
2317 INTO l_receipt_inherit_inv_num_flag
2318 FROM AR_RECEIPT_METHODS
2319 WHERE receipt_method_id = p_batch_rec.receipt_method_id;
2320
2321 IF PG_DEBUG in ('Y', 'C') THEN
2322 arp_util.debug ( 'Receipt_inherit_inv_num_flag : ' || l_receipt_inherit_inv_num_flag);
2323 END IF;
2324
2325 IF (l_receipt_inherit_inv_num_flag = 'Y')
2326 THEN
2327 l_receipt_number := p_ps_rec.trx_number;
2328 END IF;
2329
2330
2331 /*-----------------------------------------------+
2332 | Creation of the Receipt using the Receipt API|
2333 +------------------------------------------------*/
2334 -- bug 2649369 : when rate type is corporate, null out rate, as Receipt API expects none to be passed
2335 -- bug 3506385/3572968 : pass rate only when exchange_rate_type = 'User'
2336 -- this is the only case where Receipt API expects a rate
2337
2338 if p_ps_rec.exchange_rate_type <> 'User' then
2339 l_exch_rate := null;
2340 else
2341 l_exch_rate := p_ps_rec.exchange_rate;
2342 end if;
2343
2344 IF PG_DEBUG in ('Y', 'C') THEN
2345 arp_util.debug( '----------------------------------------------------------------');
2346 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.CREATE_CASH : ');
2347 arp_util.debug('p_currency_code : ' || p_ps_rec.invoice_currency_code);
2348 arp_util.debug('p_exchange_rate_type : ' || p_ps_rec.exchange_rate_type);
2349 arp_util.debug('p_exchange_rate : ' || l_exch_rate);
2350 arp_util.debug('p_exchange_rate_date : ' || p_ps_rec.exchange_date);
2351 arp_util.debug('p_remittance_bank_account_id : ' || p_batch_rec.remit_bank_acct_use_id);
2352 arp_util.debug('p_amount : ' || p_ps_rec.amount_due_original);
2353 arp_util.debug('p_receipt_method_id : ' || p_batch_rec.receipt_method_id);
2354 arp_util.debug('p_receipt_number : ' || l_receipt_number);
2355 arp_util.debug('p_customer_id : ' || p_ps_rec.customer_id);
2356 arp_util.debug('p_called_from : ' || l_called_from);
2357 arp_util.debug('p_receipt_date : ' || p_batch_rec.batch_date);
2358 arp_util.debug('p_gl_date : ' || p_batch_rec.gl_date);
2359 arp_util.debug('p_cr_id : ' || l_cr_id);
2360 arp_util.debug('p_org_id : ' || p_ps_rec.org_id);
2361 arp_util.debug('----------------------------------------------------------------');
2362 END IF;
2363
2364 ar_receipt_api_pub.create_cash(
2365 p_api_version => 1.0 ,
2366 p_init_msg_list => FND_API.G_TRUE ,
2367 x_return_status => l_return_status ,
2368 x_msg_count => l_msg_count ,
2369 x_msg_data => l_msg_data ,
2370 p_currency_code => p_ps_rec.invoice_currency_code ,
2371 p_exchange_rate_type => p_ps_rec.exchange_rate_type ,
2372 p_exchange_rate => l_exch_rate ,
2373 p_exchange_rate_date => p_ps_rec.exchange_date ,
2374 p_remittance_bank_account_id => p_batch_rec.remit_bank_acct_use_id,
2375 p_amount => p_ps_rec.amount_due_original ,
2376 p_receipt_method_id => p_batch_rec.receipt_method_id ,
2377 p_receipt_number => l_receipt_number ,
2378 p_customer_id => p_ps_rec.customer_id ,
2379 p_called_from => l_called_from ,
2380 p_receipt_date => p_batch_rec.batch_date ,
2381 p_gl_date => p_batch_rec.gl_date ,
2382 p_cr_id => l_cr_id ,
2383 p_org_id => p_ps_rec.org_id);
2384
2385
2386 IF PG_DEBUG in ('Y', 'C') THEN
2387 arp_util.debug( 'Cash Receipt Id created : ' || l_cr_id);
2388 arp_util.debug( 'Return status : ' || l_return_status);
2389 END IF;
2390
2391 IF (l_return_status <> 'S')
2392 THEN
2393 IF PG_DEBUG in ('Y', 'C') THEN
2394 arp_util.debug( '>>>>>>>>>> Problems during receipt creation');
2395 arp_util.debug( 'l_return_status : ' || l_return_status);
2396 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2397 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2398 END IF;
2399
2400
2401 IF (l_msg_count > 0)
2402 THEN
2403 Set_Api_Error;
2404 ELSE
2405 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_CREATE_REC');
2406 app_exception.raise_exception;
2407 END IF;
2408
2409 END IF;
2410
2411
2412 /*-----------------------------------------------+
2413 | Apply the receipt to Short Term Debt |
2414 +------------------------------------------------*/
2415
2416 Apply_STD (p_ps_rec.customer_trx_id, l_cr_id, p_batch_rec.batch_date, p_batch_rec.gl_date);
2417
2418
2419 /*----------------------------------------------+
2420 | UPD Cash Receipt History with Batch ID |
2421 +-----------------------------------------------*/
2422
2423
2424 /* Bug 1398843 */
2425 arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,p_batch_rec.batch_id);
2426
2427 END IF;
2428
2429 IF PG_DEBUG in ('Y', 'C') THEN
2430 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse ()-');
2431 END IF;
2432
2433 EXCEPTION
2434 WHEN OTHERS THEN
2435 IF PG_DEBUG in ('Y', 'C') THEN
2436 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse : ' || SQLERRM);
2437 END IF;
2438 RAISE;
2439
2440 END Factore_Recourse;
2441
2442
2443 /*==============================================================================+
2444 | PROCEDURE |
2445 | Factore_Without_Recourse |
2446 | |
2447 | DESCRIPTION |
2448 | BR - Factore without Recourse is approved : |
2449 | Create a receipt and apply it to the BR |
2450 | |
2451 +==============================================================================*/
2452
2453
2454 PROCEDURE Factore_Without_Recourse ( p_batch_rec IN ar_batches%ROWTYPE ,
2455 p_ps_rec IN ar_payment_schedules%ROWTYPE )
2456 IS
2457
2458 l_cr_id NUMBER;
2459 l_count NUMBER;
2460 l_msg_count NUMBER;
2461 l_msg_data VARCHAR2(2000);
2462 l_return_status VARCHAR2(1);
2463
2464 l_receipt_number VARCHAR2(20) := NULL;
2465 l_receipt_inherit_inv_num_flag VARCHAR2(1);
2466 l_exch_rate NUMBER;
2467 l_org_return_status VARCHAR2(1);
2468 l_org_id NUMBER;
2469
2470 BEGIN
2471
2472 IF PG_DEBUG in ('Y', 'C') THEN
2473 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse ()+');
2474 END IF;
2475
2476 /* SSA change */
2477 l_org_id := p_ps_rec.org_id;
2478 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
2479 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2480 p_return_status =>l_org_return_status);
2481
2482 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2483
2484 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse : l_org_return_status <> SUCCESS');
2485 ELSE
2486
2487 /*-----------------------------------------------+
2488 | Check if the receipt number is inherited |
2489 | from the BR. If not inherited, the receipt |
2490 | API will default it from sequence. |
2491 +------------------------------------------------*/
2492
2493 SELECT receipt_inherit_inv_num_flag
2494 INTO l_receipt_inherit_inv_num_flag
2495 FROM AR_RECEIPT_METHODS
2496 WHERE receipt_method_id = p_batch_rec.receipt_method_id;
2497
2498 IF PG_DEBUG in ('Y', 'C') THEN
2499 arp_util.debug ( 'Receipt_inherit_inv_num_flag : ' || l_receipt_inherit_inv_num_flag);
2500 END IF;
2501
2502 IF (l_receipt_inherit_inv_num_flag = 'Y')
2503 THEN
2504 l_receipt_number := p_ps_rec.trx_number;
2505 END IF;
2506
2507
2508 IF PG_DEBUG in ('Y', 'C') THEN
2509 arp_util.debug( '----------------------------------------------------------------');
2510 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.CREATE_AND_APPLY : ');
2511 arp_util.debug( 'p_applied_payment_schedule_id : ' || p_ps_rec.payment_schedule_id );
2512 arp_util.debug( 'p_amount : ' || p_ps_rec.amount_due_remaining);
2513 arp_util.debug( 'p_currency_code : ' || p_ps_rec.invoice_currency_code);
2514 arp_util.debug( 'p_exchange_rate : ' || p_ps_rec.exchange_rate);
2515 arp_util.debug( 'p_receipt_number : ' || l_receipt_number);
2516 arp_util.debug( 'p_receipt_method_id : ' || p_batch_rec.receipt_method_id);
2517 arp_util.debug( 'p_customer_id : ' || p_ps_rec.customer_id);
2518 arp_util.debug( 'remit bank account use id : ' || p_batch_rec.remit_bank_acct_use_id);
2519 arp_util.debug( 'p_called_from : ' || C_BR_FACTORED);
2520 arp_util.debug( 'p_link_to_trx_hist_id : ' || NULL);
2521 arp_util.debug( 'p_org_id : ' || to_char(p_ps_rec.org_id));
2522 arp_util.debug( '----------------------------------------------------------------');
2523 END IF;
2524
2525 -- bug 3506385/3572968 : pass rate ONLY when exchange_rate_type = 'User'
2526 -- this is the only case where Receipt API expects a rate
2527
2528 if p_ps_rec.exchange_rate_type <> 'User' then
2529 l_exch_rate := null;
2530 else
2531 l_exch_rate := p_ps_rec.exchange_rate;
2532 end if;
2533
2534
2535 AR_RECEIPT_API_PUB.create_and_apply(
2536 p_api_version => 1.0 ,
2537 p_init_msg_list => FND_API.G_TRUE ,
2538 x_return_status => l_return_status ,
2539 x_msg_count => l_msg_count ,
2540 x_msg_data => l_msg_data ,
2541 p_currency_code => p_ps_rec.invoice_currency_code ,
2542 p_exchange_rate_type => p_ps_rec.exchange_rate_type ,
2543 p_exchange_rate => p_ps_rec.exchange_rate ,
2544 p_exchange_rate_date => p_ps_rec.exchange_date ,
2545 p_amount => p_ps_rec.amount_due_remaining ,
2546 p_receipt_number => l_receipt_number ,
2547 p_receipt_method_id => p_batch_rec.receipt_method_id ,
2548 p_customer_id => p_ps_rec.customer_id ,
2549 p_applied_payment_schedule_id => p_ps_rec.payment_schedule_id ,
2550 p_remittance_bank_account_id => p_batch_rec.remit_bank_acct_use_id,
2551 p_called_from => C_BR_FACTORED ,
2552 p_link_to_trx_hist_id => NULL ,
2553 p_receipt_date => p_batch_rec.batch_date ,
2554 p_gl_date => p_batch_rec.gl_date ,
2555 p_apply_date => p_batch_rec.batch_date ,
2556 p_apply_gl_date => p_batch_rec.gl_date ,
2557 p_cr_id => l_cr_id ,
2558 p_org_id => p_ps_rec.org_id);
2559
2560 IF PG_DEBUG in ('Y', 'C') THEN
2561 arp_util.debug( 'Cash Receipt Id created : ' || l_cr_id);
2562 arp_util.debug( 'Return status : ' || l_return_status);
2563 arp_util.debug( 'l_msg_count : ' || to_char(l_msg_count));
2564 END IF;
2565
2566
2567 IF (l_return_status <> 'S')
2568 THEN
2569 IF PG_DEBUG in ('Y', 'C') THEN
2570 arp_util.debug( '>>>>>>>>>> Problems during Receipt Creation and Application');
2571 arp_util.debug( 'l_return_status : ' || l_return_status);
2572 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2573 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2574 END IF;
2575
2576
2577 IF (l_msg_count > 0)
2578 THEN
2579 Set_Api_Error;
2580 ELSE
2581 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_CREATE_APPLY_REC');
2582 app_exception.raise_exception;
2583 END IF;
2584
2585 END IF;
2586
2587 /*----------------------------------------------+
2588 | UPD Cash Receipt History with Batch ID |
2589 +-----------------------------------------------*/
2590
2591
2592 /* Bug 1398843 */
2593 arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,p_batch_rec.batch_id);
2594
2595 END IF;
2596
2597
2598 IF PG_DEBUG in ('Y', 'C') THEN
2599 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse ()-');
2600 END IF;
2601
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 IF PG_DEBUG in ('Y', 'C') THEN
2605 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse : ' || SQLERRM);
2606 END IF;
2607 RAISE;
2608
2609 END Factore_Without_Recourse;
2610
2611
2612 /*==============================================================================+
2613 | PROCEDURE |
2614 | Apply_Receipt |
2615 | |
2616 | DESCRIPTION |
2617 | Receipt Application |
2618 | |
2619 +==============================================================================*/
2620
2621
2622 PROCEDURE Apply_Receipt (p_trh_rec IN ar_transaction_history%ROWTYPE ,
2623 p_ps_rec IN ar_payment_schedules%ROWTYPE ,
2624 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
2625 p_called_from IN VARCHAR2 )
2626
2627 IS
2628
2629 l_msg_count NUMBER;
2630 l_msg_data VARCHAR2(2000);
2631 l_return_status VARCHAR2(1);
2632 l_move_deferred_tax VARCHAR2(1);
2633 l_org_return_status VARCHAR2(1);
2634 l_org_id NUMBER;
2635
2636 BEGIN
2637
2638 IF PG_DEBUG in ('Y', 'C') THEN
2639 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_Receipt ()+');
2640 END IF;
2641
2642 /* SSA change */
2643 l_org_id := p_trh_rec.org_id;
2644 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
2645 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2646 p_return_status =>l_org_return_status);
2647
2648 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2649
2650 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_receipt : l_org_return_status <> SUCCESS');
2651 ELSE
2652
2653 /*----------------------------------------------+
2654 | Create normal application using Receipt API |
2655 +-----------------------------------------------*/
2656
2657
2658 IF (p_ps_rec.tax_remaining IS NOT NULL and p_ps_rec.tax_remaining <> 0)
2659 THEN
2660 l_move_deferred_tax := 'Y';
2661 ELSE
2662 l_move_deferred_tax := 'N';
2663 END IF;
2664
2665
2666 IF PG_DEBUG in ('Y', 'C') THEN
2667 arp_util.debug( '------------------------------------------------');
2668 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.APPLY : ');
2669 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
2670 arp_util.debug( 'p_customer_trx_id : ' || p_trh_rec.customer_trx_id);
2671 arp_util.debug( 'p_applied_payment_schedule_id : ' || p_ps_rec.payment_schedule_id);
2672 arp_util.debug( 'p_amount_applied : ' || p_ps_rec.amount_due_remaining);
2673 arp_util.debug( 'p_apply_date : ' || p_trh_rec.trx_date);
2674 arp_util.debug( 'p_apply_gl_date : ' || p_trh_rec.gl_date);
2675 arp_util.debug( 'p_move_deffered_tax : ' || l_move_deferred_tax);
2676 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
2677 arp_util.debug( '------------------------------------------------');
2678 END IF;
2679
2680 AR_RECEIPT_API_PUB.Apply (
2681 p_api_version => 1.0 ,
2682 p_init_msg_list => FND_API.G_TRUE ,
2683 x_return_status => l_return_status ,
2684 x_msg_count => l_msg_count ,
2685 x_msg_data => l_msg_data ,
2686 p_cash_receipt_id => p_cash_receipt_id ,
2687 p_customer_trx_id => p_trh_rec.customer_trx_id ,
2688 p_applied_payment_schedule_id => p_ps_rec.payment_schedule_id ,
2689 p_amount_applied => p_ps_rec.amount_due_remaining ,
2690 p_apply_date => p_trh_rec.trx_date ,
2691 p_apply_gl_date => p_trh_rec.gl_date ,
2692 p_called_from => p_called_from ,
2693 p_move_deferred_tax => l_move_deferred_tax ,
2694 p_org_id => p_trh_rec.org_id);
2695
2696 IF (l_return_status <> 'S')
2697 THEN
2698 IF PG_DEBUG in ('Y', 'C') THEN
2699 arp_util.debug( '>>>>>>>>>> Problems during Receipt Application');
2700 arp_util.debug( 'l_return_status : ' || l_return_status);
2701 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2702 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2703 END IF;
2704
2705
2706 IF (l_msg_count > 0)
2707 THEN
2708 Set_Api_Error;
2709 ELSE
2710 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_APPLY_REC');
2711 app_exception.raise_exception;
2712 END IF;
2713
2714 END IF;
2715 END IF;
2716
2717
2718 IF PG_DEBUG in ('Y', 'C') THEN
2719 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_Receipt ()-');
2720 END IF;
2721
2722 EXCEPTION
2723 WHEN OTHERS THEN
2724 IF PG_DEBUG in ('Y', 'C') THEN
2725 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Apply_Receipt : ' || SQLERRM);
2726 END IF;
2727 RAISE;
2728
2729 END Apply_Receipt;
2730
2731
2732 /*==============================================================================+
2733 | PROCEDURE |
2734 | Link_Application_History |
2735 | |
2736 | DESCRIPTION |
2737 | Populates LINK_TO_TRX_HIST_ID on the application that closes the |
2738 | BR at the end of the remittance process (Factore Without Recourse) |
2739 | |
2740 +==============================================================================*/
2741
2742
2743 PROCEDURE Link_Application_History (p_trh_rec IN ar_transaction_history%ROWTYPE)
2744
2745 IS
2746
2747 CURSOR last_application_cur IS
2748 SELECT receivable_application_id
2749 FROM ar_receivable_applications
2750 WHERE applied_customer_trx_id = p_trh_rec.customer_trx_id
2751 AND status = 'APP'
2752 AND display = 'Y'
2753 ORDER BY receivable_application_id DESC;
2754
2755 last_application_rec last_application_cur%ROWTYPE;
2756
2757
2758 BEGIN
2759
2760 IF PG_DEBUG in ('Y', 'C') THEN
2761 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Link_Application_History ()+');
2762 END IF;
2763
2764 /*----------------------------------------------+
2765 | Fetch last Application |
2766 +-----------------------------------------------*/
2767
2768 OPEN last_application_cur;
2769 FETCH last_application_cur INTO last_application_rec;
2770
2771 IF (last_application_cur%NOTFOUND)
2772 THEN
2773 IF PG_DEBUG in ('Y', 'C') THEN
2774 arp_util.debug ('Link_Application_History: ' || '>>>>>>>>>> Last Application could not be found');
2775 END IF;
2776 CLOSE last_application_cur;
2777 APP_EXCEPTION.raise_exception;
2778 END IF;
2779
2780 CLOSE last_application_cur;
2781
2782
2783 /*----------------------------------------------+
2784 | Populate the LINK_TO_TRX_HIST_ID on the |
2785 | application that closes the BR for Factored |
2786 | without Recourse case. |
2787 +-----------------------------------------------*/
2788
2789 ARP_APP_PKG.lock_p (last_application_rec.receivable_application_id);
2790
2791
2792 /*----------------------------------------------+
2793 | Update the LINK_TO_TRX_HIST_ID on the |
2794 | application |
2795 +-----------------------------------------------*/
2796
2797 UPDATE ar_receivable_applications
2798 SET LINK_TO_TRX_HIST_ID = p_trh_rec.transaction_history_id
2799 WHERE receivable_application_id = last_application_rec.receivable_application_id;
2800
2801
2802 IF PG_DEBUG in ('Y', 'C') THEN
2803 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Link_Application_History ()-');
2804 END IF;
2805
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 IF PG_DEBUG in ('Y', 'C') THEN
2809 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Link_Application_History : ' || SQLERRM);
2810 END IF;
2811 IF (last_application_cur%ISOPEN)
2812 THEN
2813 CLOSE last_application_cur;
2814 END IF;
2815 RAISE;
2816
2817 END Link_Application_History;
2818
2819
2820 END AR_BILLS_MAINTAIN_LIB_PVT ;