[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.14 2005/06/03 20:38:29 vcrisost 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 BEGIN
1205
1206 IF PG_DEBUG in ('Y', 'C') THEN
1207 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment ()+');
1208 END IF;
1209
1210 /* SSA change */
1211 l_org_id := p_trh_rec.org_id;
1212 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1213 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1214 p_return_status =>l_org_return_status);
1215
1216 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1217
1218 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment : l_org_return_status <> SUCCESS');
1219 ELSE
1220
1221 IF PG_DEBUG in ('Y', 'C') THEN
1222 arp_util.debug( '--------------------------------------------------------------');
1223 arp_util.debug('PARAMETERS PASSED TO AR_ADJUST_PUB.Reverse_Adjustment : ');
1224 arp_util.debug( 'p_old_adjust_id : ' || p_adj_id);
1225 arp_util.debug( 'p_reversal_date : ' || p_trh_rec.trx_date);
1226 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date);
1227 arp_util.debug( 'p_comments : ' || p_trh_rec.comments);
1228 arp_util.debug( 'p_chk_approval_limits : ' || 'F');
1229 arp_util.debug( 'p_move_deferred_tax : ' || 'Y');
1230 arp_util.debug( 'p_called_from : ' || p_called_from);
1231 arp_util.debug( 'p_org_id : ' || p_trh_rec.org_id);
1232 arp_util.debug( '--------------------------------------------------------------');
1233 END IF;
1234
1235 AR_ADJUST_PUB.Reverse_Adjustment (
1236 p_api_name => 'AR_ADJUST_PUB' ,
1237 p_api_version => 1.0 ,
1238 p_init_msg_list => FND_API.G_TRUE ,
1239 p_msg_count => l_msg_count ,
1240 p_msg_data => l_msg_data ,
1241 p_return_status => l_return_status ,
1242 p_old_adjust_id => p_adj_id ,
1243 p_reversal_date => p_trh_rec.trx_date ,
1244 p_reversal_gl_date => p_trh_rec.gl_date ,
1245 p_comments => p_trh_rec.comments ,
1246 p_chk_approval_limits => 'F' ,
1247 p_move_deferred_tax => 'Y' ,
1248 p_new_adj_id => l_new_adjust_id ,
1249 p_called_from => p_called_from ,
1250 p_org_id => p_trh_rec.org_id);
1251
1252 IF (l_return_status <> 'S')
1253 THEN
1254 IF PG_DEBUG in ('Y', 'C') THEN
1255 arp_util.debug( '>>>>>>>>>> Problems during Adjustment Reversal');
1256 arp_util.debug( 'l_return_status : ' || l_return_status);
1257 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1258 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1259 END IF;
1260
1261
1262 IF (l_msg_count > 0)
1263 THEN
1264 Set_Api_Error;
1265 ELSE
1266 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_REVERSE_ADJ');
1267 app_exception.raise_exception;
1268 END IF;
1269
1270 END IF;
1271 END IF;
1272
1273 IF PG_DEBUG in ('Y', 'C') THEN
1274 arp_util.debug ( 'Adjustment ID : ' || l_new_adjust_id);
1275 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment ()-');
1276 END IF;
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 IF PG_DEBUG in ('Y', 'C') THEN
1281 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Adjustment : ' || SQLERRM);
1282 arp_util.debug( 'p_adj_id : ' || p_adj_id);
1283 END IF;
1284 RAISE;
1285
1286 END Reverse_Adjustment;
1287
1288
1289
1290 /*==============================================================================+
1291 | PROCEDURE |
1292 | Reverse_Assignments_Adjustment |
1293 | |
1294 | DESCRIPTION |
1295 | Reverse the adjustments created when standard transactions are exchanged|
1296 | Remove the adjustment ID in the assignment information |
1297 | Update the reserved columns of the PS of the exchanged transactions |
1298 | |
1299 | Called by UNCOMPLETE_BR and CANCEL_BR |
1300 | |
1301 +==============================================================================*/
1302
1303
1304 PROCEDURE Reverse_Assignments_Adjustment ( p_trh_rec IN AR_TRANSACTION_HISTORY%ROWTYPE ,
1305 p_acceptance_flag IN VARCHAR2 )
1306 IS
1307
1308 CURSOR assignment_cur IS
1309 SELECT br_adjustment_id, br_ref_payment_schedule_id, customer_trx_line_id, br_ref_customer_trx_id
1310 FROM ra_customer_trx_lines
1311 WHERE customer_trx_id = p_trh_rec.customer_trx_id;
1312
1313 assignment_rec assignment_cur%ROWTYPE ;
1314
1315 l_msg_data VARCHAR2(2000) ;
1316 l_msg VARCHAR2(240) ;
1317 l_msg_count NUMBER ;
1318 l_return_status VARCHAR2(1) ;
1319 l_ps_rec ar_payment_schedules%ROWTYPE ;
1320 l_trx_rec ra_customer_trx%ROWTYPE ;
1321 l_trh_rec ar_transaction_history%ROWTYPE ;
1322
1323 BEGIN
1324
1325 IF PG_DEBUG in ('Y', 'C') THEN
1326 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Assignments_Adjustment ()+');
1327 END IF;
1328
1329 /*------------------------------------------------------+
1330 | For each assignment, non accounting adjustment |
1331 | is reversed through the adjustment API |
1332 +-------------------------------------------------------*/
1333
1334 FOR assignment_rec IN assignment_cur LOOP
1335
1336 IF (p_acceptance_flag = 'N')
1337 THEN
1338
1339 Reverse_Adjustment (assignment_rec.br_adjustment_id, p_trh_rec, NULL);
1340
1341 /*----------------------------------------------+
1342 | Removes the Adjustment ID in the Assignment |
1343 | Information |
1344 +-----------------------------------------------*/
1345
1346
1347 arp_ctl_pkg.lock_p (assignment_rec.customer_trx_line_id);
1348
1349 UPDATE ra_customer_trx_lines
1350 SET br_adjustment_id = NULL
1351 WHERE customer_trx_line_id = assignment_rec.customer_trx_line_id;
1352
1353
1354 /*----------------------------------------------+
1355 | If the exchanged transaction is a BR, create |
1356 | an history record for the exchanged BR |
1357 +-----------------------------------------------*/
1358
1359 arp_ps_pkg.fetch_p (assignment_rec.br_ref_payment_schedule_id, l_ps_rec);
1360
1361 IF (AR_BILLS_CREATION_VAL_PVT.Is_Transaction_BR (l_ps_rec.cust_trx_type_id))
1362 THEN
1363
1364 ARP_CT_PKG.fetch_p (l_trx_rec, assignment_rec.br_ref_customer_trx_id);
1365
1366 l_trh_rec.customer_trx_id := assignment_rec.br_ref_customer_trx_id;
1367 ARP_TRANSACTION_HISTORY_PKG.lock_fetch_f_trx_id (l_trh_rec);
1368
1369 -- Fetch the new status and new event of the BR
1370 AR_BILLS_MAINTAIN_STATUS_PUB.New_Status_Event (
1371 p_trx_rec => l_trx_rec ,
1372 p_action => C_EXCHANGE_UNCOMPLETE ,
1373 p_new_status => l_trh_rec.status ,
1374 p_new_event => l_trh_rec.event );
1375
1376 l_trh_rec.transaction_history_id:= NULL ;
1377 l_trh_rec.current_record_flag := 'Y' ;
1378 l_trh_rec.prv_trx_history_id := NULL ;
1379 l_trh_rec.posting_control_id := -3 ;
1380 l_trh_rec.gl_posted_date := NULL ;
1381 l_trh_rec.first_posted_record_flag := 'N' ;
1382 l_trh_rec.created_from := 'ARBRMAIB' ;
1383 l_trh_rec.postable_flag := 'N' ;
1384 l_trh_rec.current_accounted_flag:= 'N' ;
1385
1386 arp_proc_transaction_history.insert_transaction_history (l_trh_rec ,
1387 l_trh_rec.transaction_history_id);
1388 END IF;
1389
1390 ELSE
1391
1392 update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, NULL, NULL);
1393 END IF;
1394
1395
1396 END LOOP;
1397
1398 IF PG_DEBUG in ('Y', 'C') THEN
1399 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Assignments_Adjustment ()-');
1400 END IF;
1401
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 IF PG_DEBUG in ('Y', 'C') THEN
1405 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Assignments_Adjustment () ');
1406 arp_util.debug('Reverse_Assignments_Adjustment: ' || 'p_customer_trx_id : ' || p_trh_rec.customer_trx_id);
1407 arp_util.debug('Reverse_Assignments_Adjustment: ' || 'p_acceptance_flag : ' || p_acceptance_flag);
1408 END IF;
1409 IF (assignment_cur%ISOPEN)
1410 THEN
1411 CLOSE assignment_cur;
1412 END IF;
1413 RAISE;
1414
1415 END Reverse_Assignments_Adjustment;
1416
1417
1418 /*==============================================================================+
1419 | PROCEDURE |
1420 | Find_Last_Receipt |
1421 | |
1422 | DESCRIPTION |
1423 | Find the last receipt applied on the BR |
1424 | |
1425 +==============================================================================*/
1426
1427
1428 PROCEDURE Find_Last_Receipt ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
1429 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE )
1430
1431 IS
1432
1433 BEGIN
1434
1435 IF PG_DEBUG in ('Y', 'C') THEN
1436 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt ()+');
1437 END IF;
1438
1439 SELECT cash_receipt_id
1440 INTO p_cash_receipt_id
1441 FROM ar_receivable_applications
1442 where receivable_application_id = (SELECT MAX(receivable_application_id)
1443 FROM ar_receivable_applications
1444 WHERE applied_customer_trx_id = p_customer_trx_id
1445 AND status = 'APP' );
1446
1447 IF PG_DEBUG in ('Y', 'C') THEN
1448 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt ()-');
1449 END IF;
1450
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 IF PG_DEBUG in ('Y', 'C') THEN
1454 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt () ');
1455 arp_util.debug ('Find_Last_Receipt: ' || 'No receipt was found for the BR');
1456 END IF;
1457 RAISE;
1458
1459 WHEN OTHERS THEN
1460 IF PG_DEBUG in ('Y', 'C') THEN
1461 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_Receipt () ');
1462 arp_util.debug('Find_Last_Receipt: ' || 'p_customer_trx_id : ' || p_customer_trx_id);
1463 END IF;
1464 RAISE;
1465
1466 END Find_Last_Receipt;
1467
1468
1469 /*==============================================================================+
1470 | PROCEDURE |
1471 | Find_Last_STD |
1472 | |
1473 | DESCRIPTION |
1474 | Find the last STD application on the BR |
1475 | |
1476 +==============================================================================*/
1477
1478
1479 PROCEDURE Find_Last_STD ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
1480 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE ,
1481 p_receivable_application_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE)
1482
1483 IS
1484
1485 CURSOR last_std_application_cur IS
1486 SELECT receivable_application_id, cash_receipt_id
1487 FROM ar_receivable_applications
1488 WHERE link_to_customer_trx_id = p_customer_trx_id
1489 AND status = 'ACTIVITY'
1490 AND applied_payment_schedule_id = -2
1491 AND display = 'Y'
1492 ORDER BY receivable_application_id DESC;
1493
1494 last_std_application_rec last_std_application_cur%ROWTYPE;
1495
1496
1497 BEGIN
1498
1499 IF PG_DEBUG in ('Y', 'C') THEN
1500 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_STD ()+');
1501 END IF;
1502
1503 /*-----------------------------------------------+
1504 | Fetch last Short Term Debt Application |
1505 +------------------------------------------------*/
1506
1507 OPEN last_std_application_cur;
1508 FETCH last_std_application_cur INTO last_std_application_rec;
1509
1510 IF (last_std_application_cur%NOTFOUND)
1511 THEN
1512 IF PG_DEBUG in ('Y', 'C') THEN
1513 arp_util.debug ('Find_Last_STD: ' || '>>>>>>>>>> Last Short Term Debt Application could not be found');
1514 END IF;
1515 CLOSE last_std_application_cur;
1516 APP_EXCEPTION.raise_exception;
1517 END IF;
1518
1519 p_cash_receipt_id := last_std_application_rec.cash_receipt_id;
1520 p_receivable_application_id := last_std_application_rec.receivable_application_id;
1521
1522 CLOSE last_std_application_cur;
1523
1524
1525 IF PG_DEBUG in ('Y', 'C') THEN
1526 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_STD ()-');
1527 END IF;
1528
1529 EXCEPTION
1530
1531 WHEN OTHERS THEN
1532 IF PG_DEBUG in ('Y', 'C') THEN
1533 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Find_Last_STD : ' || SQLERRM);
1534 END IF;
1535 IF (last_std_application_cur%ISOPEN)
1536 THEN
1537 CLOSE last_std_application_cur;
1538 END IF;
1539 RAISE;
1540
1541 END Find_Last_STD;
1542
1543
1544
1545 /*==============================================================================+
1546 | PROCEDURE |
1547 | Reverse_Receipt |
1548 | |
1549 | DESCRIPTION |
1550 | Reverses a receipt with the receipt API |
1551 | |
1552 +==============================================================================*/
1553
1554
1555 PROCEDURE Reverse_Receipt ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
1556 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
1557 p_reversal_reason IN VARCHAR2 ,
1558 p_called_from IN VARCHAR2 )
1559 IS
1560
1561 l_return_status VARCHAR2(1);
1562 l_msg_count NUMBER;
1563 l_msg_data VARCHAR2(2000);
1564 l_count NUMBER;
1565 l_org_return_status VARCHAR2(1);
1566 l_org_id NUMBER;
1567
1568 BEGIN
1569
1570 IF PG_DEBUG in ('Y', 'C') THEN
1571 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Receipt ()+');
1572 END IF;
1573
1574 /* SSA change */
1575 l_org_id := p_trh_rec.org_id;
1576 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1577 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1578 p_return_status =>l_org_return_status);
1579
1580 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1581
1582 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_receipt : l_org_return_status <> SUCCESS');
1583 ELSE
1584 /*------------------------------------------------------+
1585 | Reverse a receipt using Receipt API |
1586 +-------------------------------------------------------*/
1587
1588 IF PG_DEBUG in ('Y', 'C') THEN
1589 arp_util.debug( '--------------------------------------------------------------');
1590 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.Reverse : ' );
1591 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id );
1592 arp_util.debug( 'p_reversal_category_code : ' || 'REV' );
1593 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date );
1594 arp_util.debug( 'p_reversal_date : ' || p_trh_rec.trx_date );
1595 arp_util.debug( 'p_called_from : ' || p_called_from );
1596 arp_util.debug( 'p_reversal_reason_code : ' || p_reversal_reason );
1597 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
1598 arp_util.debug( '--------------------------------------------------------------');
1599 END IF;
1600
1601 AR_RECEIPT_API_PUB.Reverse (
1602 p_api_version => 1.0 ,
1603 p_init_msg_list => FND_API.G_TRUE ,
1604 x_return_status => l_return_status ,
1605 x_msg_count => l_msg_count ,
1606 x_msg_data => l_msg_data ,
1607 p_cash_receipt_id => p_cash_receipt_id ,
1608 p_reversal_category_code => 'REV' ,
1609 p_reversal_gl_date => p_trh_rec.gl_date ,
1610 p_reversal_date => p_trh_rec.trx_date ,
1611 p_called_from => p_called_from ,
1612 p_reversal_reason_code => p_reversal_reason ,
1613 p_org_id => p_trh_rec.org_id);
1614
1615
1616 IF PG_DEBUG in ('Y', 'C') THEN
1617 arp_util.debug ( 'REVERSAL RECEIPT API Return status : ' || l_return_status);
1618 arp_util.debug ( ' l_msg_count : ' || to_char(l_msg_count));
1619 END IF;
1620
1621
1622 IF (l_return_status <> 'S')
1623 THEN
1624 IF PG_DEBUG in ('Y', 'C') THEN
1625 arp_util.debug( '>>>>>>>>>> Problems during Receipt Reversal');
1626 arp_util.debug( 'l_return_status : ' || l_return_status);
1627 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1628 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1629 END IF;
1630
1631 IF (l_msg_count > 0)
1632 THEN
1633 Set_Api_Error;
1634 ELSE
1635 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_REVERSE_REC');
1636 app_exception.raise_exception;
1637 END IF;
1638
1639 END IF;
1640 END IF;
1641
1642 IF PG_DEBUG in ('Y', 'C') THEN
1643 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Receipt ()-');
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 IF PG_DEBUG in ('Y', 'C') THEN
1649 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Reverse_Receipt : ' || SQLERRM);
1650 arp_util.debug ( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1651 arp_util.debug ( 'p_reversal_reason : ' || p_reversal_reason);
1652 arp_util.debug ( 'p_called_from : ' || p_called_from);
1653 END IF;
1654 RAISE;
1655
1656 END Reverse_Receipt;
1657
1658
1659 /*==============================================================================+
1660 | PROCEDURE |
1661 | Unapply_Receipt |
1662 | |
1663 | DESCRIPTION |
1664 | Unapplies a receipt using the receipt API |
1665 | |
1666 +==============================================================================*/
1667
1668
1669 PROCEDURE Unapply_Receipt ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
1670 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
1671 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
1672 p_called_from IN VARCHAR2 )
1673 IS
1674
1675 l_return_status VARCHAR2(1);
1676 l_msg_count NUMBER;
1677 l_msg_data VARCHAR2(2000);
1678 l_count NUMBER;
1679 l_org_return_status VARCHAR2(1);
1680 l_org_id NUMBER;
1681
1682 BEGIN
1683
1684 IF PG_DEBUG in ('Y', 'C') THEN
1685 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt ()+');
1686 END IF;
1687
1688 /* SSA change */
1689 l_org_id := p_trh_rec.org_id;
1690 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1691 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1692 p_return_status =>l_org_return_status);
1693
1694 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1695
1696 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt : l_org_return_status <> SUCCESS');
1697 ELSE
1698
1699 /*------------------------------------------------------+
1700 | Unapply a receipt using Receipt API |
1701 +-------------------------------------------------------*/
1702
1703 IF PG_DEBUG in ('Y', 'C') THEN
1704 arp_util.debug( '--------------------------------------------------------------');
1705 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.Unapply : ' );
1706 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1707 arp_util.debug( 'p_customer_trx_id : ' || p_trh_rec.customer_trx_id);
1708 arp_util.debug( 'p_applied_payment_schedule_id : ' || p_ps_id);
1709 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date);
1710 arp_util.debug( 'p_called_from : ' || p_called_from);
1711 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
1712 arp_util.debug( '--------------------------------------------------------------');
1713 END IF;
1714
1715 AR_RECEIPT_API_PUB.Unapply (
1716 p_api_version => 1.0 ,
1717 p_init_msg_list => FND_API.G_TRUE ,
1718 x_return_status => l_return_status ,
1719 x_msg_count => l_msg_count ,
1720 x_msg_data => l_msg_data ,
1721 p_cash_receipt_id => p_cash_receipt_id ,
1722 p_customer_trx_id => p_trh_rec.customer_trx_id,
1723 p_applied_payment_schedule_id => p_ps_id ,
1724 p_reversal_gl_date => p_trh_rec.gl_date ,
1725 p_called_from => p_called_from ,
1726 p_org_id => p_trh_rec.org_id);
1727
1728
1729 IF PG_DEBUG in ('Y', 'C') THEN
1730 arp_util.debug ( 'UNAPPLY RECEIPT API Return status : ' || l_return_status);
1731 arp_util.debug ( ' l_msg_count : ' || to_char(l_msg_count));
1732 END IF;
1733
1734
1735 IF (l_return_status <> 'S')
1736 THEN
1737 IF PG_DEBUG in ('Y', 'C') THEN
1738 arp_util.debug( '>>>>>>>>>> Problems during Receipt Unapplication');
1739 arp_util.debug( 'l_return_status : ' || l_return_status);
1740 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1741 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1742 END IF;
1743
1744
1745 IF (l_msg_count > 0)
1746 THEN
1747 Set_Api_Error;
1748 ELSE
1749 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_UNAPPLY_REC');
1750 app_exception.raise_exception;
1751 END IF;
1752
1753 END IF;
1754 END IF;
1755
1756
1757 IF PG_DEBUG in ('Y', 'C') THEN
1758 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt ()-');
1759 END IF;
1760
1761 EXCEPTION
1762 WHEN OTHERS THEN
1763 IF PG_DEBUG in ('Y', 'C') THEN
1764 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Unapply_Receipt : ' || SQLERRM);
1765 arp_util.debug ( 'p_cash_receipt_id : ' || p_cash_receipt_id );
1766 arp_util.debug ( 'p_called_from : ' || p_called_from );
1767 arp_util.debug ( 'p_ps_id : ' || p_ps_id );
1768 arp_util.debug ( 'p_trh_rec.customer_trx_id : ' || p_trh_rec.customer_trx_id );
1769 END IF;
1770 RAISE;
1771
1772 END Unapply_Receipt;
1773
1774
1775 /*==============================================================================+
1776 | PROCEDURE |
1777 | Apply_STD |
1778 | |
1779 | DESCRIPTION |
1780 | Applies the receipt to Short Term Debt |
1781 | |
1782 +==============================================================================*/
1783
1784
1785 PROCEDURE Apply_STD ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE ,
1786 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
1787 p_apply_date IN DATE ,
1788 p_apply_gl_date IN DATE )
1789
1790
1791 IS
1792
1793 l_msg_count NUMBER;
1794 l_msg_data VARCHAR2(2000);
1795 l_return_status VARCHAR2(1);
1796
1797 l_receivables_trx_id ar_receivable_applications.receivables_trx_id%TYPE;
1798 l_cr_rec ar_cash_receipts%ROWTYPE;
1799
1800 l_secondary_application_ref_id NUMBER:= NULL;
1801 l_application_ref_type VARCHAR2(30):= NULL;
1802 l_application_ref_id NUMBER:= NULL;
1803 l_application_ref_num VARCHAR2(30);
1804 ln_rec_application_id ar_receivable_applications.receivable_application_id%type;
1805
1806 l_org_return_status VARCHAR2(1);
1807 l_org_id NUMBER;
1808 BEGIN
1809
1810 IF PG_DEBUG in ('Y', 'C') THEN
1811 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD ()+');
1812 END IF;
1813
1814
1815 /* SSA change */
1816 l_org_id := l_cr_rec.org_id;
1817 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1818 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1819 p_return_status =>l_org_return_status);
1820
1821 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1822
1823 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD l_org_return_status <> SUCCESS');
1824 ELSE
1825
1826 /*------------------------------------------------------+
1827 | Fetch the Cash Receipt Information |
1828 +-------------------------------------------------------*/
1829
1830 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
1831
1832 arp_cash_receipts_pkg.fetch_p (l_cr_rec);
1833
1834
1835 /*------------------------------------------------------+
1836 | Fetch the Receivables activity used for STD |
1837 +-------------------------------------------------------*/
1838
1839 SELECT br_std_receivables_trx_id
1840 INTO l_receivables_trx_id
1841 FROM ar_receipt_method_accounts
1842 WHERE remit_bank_acct_use_id = l_cr_rec.remit_bank_acct_use_id
1843 AND receipt_method_id = l_cr_rec.receipt_method_id;
1844
1845
1846 /*------------------------------------------------------+
1847 | Apply receipt to Short Term Debt using Receipt API |
1848 +-------------------------------------------------------*/
1849
1850 IF PG_DEBUG in ('Y', 'C') THEN
1851 arp_util.debug( '--------------------------------------------------------------');
1852 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.Activity_Application : ');
1853 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1854 arp_util.debug( 'p_amount_applied : ' || l_cr_rec.amount);
1855 arp_util.debug( 'p_applied_payment_schedule : ' || '-2');
1856 arp_util.debug( 'p_link_to_customer_trx_id : ' || p_customer_trx_id);
1857 arp_util.debug( 'p_receivables_trx_id : ' || l_receivables_trx_id);
1858 arp_util.debug( 'p_apply_date : ' || p_apply_date);
1859 arp_util.debug( 'p_apply_gl_date : ' || p_apply_gl_date);
1860 arp_util.debug( 'p_org_id : ' || to_char(l_cr_rec.org_id));
1861 arp_util.debug( '--------------------------------------------------------------');
1862 END IF;
1863
1864 ar_receipt_api_pub.activity_application(
1865 p_api_version => 1.0,
1866 p_init_msg_list => FND_API.G_TRUE,
1867 x_return_status => l_return_status,
1868 x_msg_count => l_msg_count,
1869 x_msg_data => l_msg_data,
1870 p_cash_receipt_id => p_cash_receipt_id,
1871 p_amount_applied => l_cr_rec.amount,
1872 p_applied_payment_schedule_id => -2,
1873 p_link_to_customer_trx_id => p_customer_trx_id,
1874 p_receivables_trx_id => l_receivables_trx_id ,
1875 p_apply_date => p_apply_date,
1876 p_apply_gl_date => p_apply_gl_date,
1877 p_secondary_application_ref_id => l_secondary_application_ref_id,
1878 p_application_ref_type => l_application_ref_type,
1879 p_application_ref_id => l_application_ref_id,
1880 p_application_ref_num => l_application_ref_num,
1881 p_receivable_application_id => ln_rec_application_id,
1882 p_org_id => l_cr_rec.org_id);
1883
1884
1885 IF (l_return_status <> 'S')
1886 THEN
1887 IF PG_DEBUG in ('Y', 'C') THEN
1888 arp_util.debug( '>>>>>>>>>> Problems during SHORT TERM DEBT APPLICATION');
1889 arp_util.debug( 'l_return_status : ' || l_return_status);
1890 arp_util.debug( 'l_msg_count : ' || l_msg_count);
1891 arp_util.debug( 'l_msg_data : ' || l_msg_data);
1892 END IF;
1893
1894
1895 IF (l_msg_count > 0)
1896 THEN
1897 Set_Api_Error;
1898 ELSE
1899 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_APPLY_REC');
1900 app_exception.raise_exception;
1901 END IF;
1902
1903 END IF;
1904 END IF;
1905
1906
1907 IF PG_DEBUG in ('Y', 'C') THEN
1908 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD ()-');
1909 END IF;
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 IF PG_DEBUG in ('Y', 'C') THEN
1914 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Apply_STD : ' || SQLERRM);
1915 END IF;
1916 RAISE;
1917
1918 END Apply_STD;
1919
1920
1921
1922
1923 /*==============================================================================+
1924 | PROCEDURE |
1925 | Unapply_STD |
1926 | |
1927 | DESCRIPTION |
1928 | Unapply the receipt from STD |
1929 | |
1930 +==============================================================================*/
1931
1932
1933 PROCEDURE Unapply_STD ( p_trh_rec IN ar_transaction_history%ROWTYPE ,
1934 p_called_from IN VARCHAR2 ,
1935 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE )
1936
1937 IS
1938
1939
1940 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1941
1942 l_msg_count NUMBER;
1943 l_msg_data VARCHAR2(2000);
1944 l_return_status VARCHAR2(1);
1945 l_org_return_status VARCHAR2(1);
1946 l_org_id NUMBER;
1947
1948 BEGIN
1949
1950 IF PG_DEBUG in ('Y', 'C') THEN
1951 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD ()+');
1952 END IF;
1953
1954 /* SSA change */
1955 l_org_id := p_trh_rec.org_id;
1956 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
1957 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
1958 p_return_status =>l_org_return_status);
1959
1960 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1961
1962 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD : l_org_return_status <> SUCCESS');
1963 ELSE
1964
1965 /*-----------------------------------------------+
1966 | Fetch last Short Term Debt Application |
1967 +------------------------------------------------*/
1968
1969 Find_last_STD (p_trh_rec.customer_trx_id, p_cash_receipt_id, l_receivable_application_id);
1970
1971
1972 /*-----------------------------------------------+
1973 | Unapply from Short Term Debt using Receipt API |
1974 +------------------------------------------------*/
1975
1976 IF PG_DEBUG in ('Y', 'C') THEN
1977 arp_util.debug( '----------------------------------------------------------------');
1978 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.ACTIVITY_UNAPPLICATION : ');
1979 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
1980 arp_util.debug( 'p_receivable_application_id: ' || l_receivable_application_id);
1981 arp_util.debug( 'p_reversal_gl_date : ' || p_trh_rec.gl_date);
1982 arp_util.debug( 'p_called_from : ' || p_called_from);
1983 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
1984 arp_util.debug( '----------------------------------------------------------------');
1985 END IF;
1986
1987 AR_RECEIPT_API_PUB.Activity_Unapplication(
1988 p_api_version => 1.0 ,
1989 p_init_msg_list => FND_API.G_TRUE ,
1990 x_return_status => l_return_status ,
1991 x_msg_count => l_msg_count ,
1992 x_msg_data => l_msg_data ,
1993 p_cash_receipt_id => p_cash_receipt_id,
1994 p_receivable_application_id => l_receivable_application_id,
1995 p_reversal_gl_date => p_trh_rec.gl_date,
1996 p_called_from => p_called_from,
1997 p_org_id => p_trh_rec.org_id);
1998
1999
2000 IF (l_return_status <> 'S')
2001 THEN
2002 IF PG_DEBUG in ('Y', 'C') THEN
2003 arp_util.debug( '>>>>>>>>>> Problems during SHORT TERM DEBT UNAPPLICATION');
2004 arp_util.debug( 'l_return_status : ' || l_return_status);
2005 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2006 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2007 END IF;
2008
2009
2010 IF (l_msg_count > 0)
2011 THEN
2012 Set_Api_Error;
2013 ELSE
2014 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_UNAPPLY_REC');
2015 app_exception.raise_exception;
2016 END IF;
2017
2018 END IF;
2019 END IF;
2020
2021 IF PG_DEBUG in ('Y', 'C') THEN
2022 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD ()-');
2023 END IF;
2024
2025 EXCEPTION
2026 WHEN OTHERS THEN
2027 IF PG_DEBUG in ('Y', 'C') THEN
2028 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Unapply_STD : ' || SQLERRM);
2029 END IF;
2030 RAISE;
2031
2032 END Unapply_STD;
2033
2034
2035
2036 /*==============================================================================+
2037 | PROCEDURE |
2038 | Unpaid |
2039 | |
2040 | DESCRIPTION |
2041 | BR - Unpaid |
2042 | |
2043 +==============================================================================*/
2044
2045 PROCEDURE Unpaid ( p_trh_rec IN OUT NOCOPY ar_transaction_history%ROWTYPE ,
2046 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
2047 p_remittance_batch_id IN ra_customer_trx.remittance_batch_id%TYPE,
2048 p_unpaid_reason IN VARCHAR2 )
2049 IS
2050
2051 l_adj_id ar_adjustments.adjustment_id%TYPE ;
2052 l_batch_rec ar_batches%ROWTYPE ;
2053 l_called_from VARCHAR2(30) := NULL ;
2054 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE ;
2055 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
2056 l_prev_trh_rec AR_TRANSACTION_HISTORY%ROWTYPE ;
2057
2058 BEGIN
2059
2060 IF PG_DEBUG in ('Y', 'C') THEN
2061 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unpaid ()+');
2062 END IF;
2063
2064 -- Fetch the remittance method if it exists
2065
2066 IF (p_remittance_batch_id IS NOT NULL)
2067 THEN
2068
2069 arp_cr_batches_pkg.fetch_p (p_remittance_batch_id, l_batch_rec);
2070
2071 IF (l_batch_rec.remit_method_code = C_STANDARD)
2072 THEN
2073 l_called_from := C_BR_REMITTED;
2074
2075 ELSIF (l_batch_rec.remit_method_code = C_FACTORING AND l_batch_rec.with_recourse_flag = 'Y')
2076 THEN
2077 l_called_from := C_BR_FACTORED_RECOURSE;
2078
2079 ELSIF (l_batch_rec.remit_method_code = C_FACTORING AND l_batch_rec.with_recourse_flag = 'N')
2080 THEN
2081 l_called_from := C_BR_FACTORED;
2082 END IF;
2083 END IF;
2084
2085
2086
2087 -- Fetch the previous relevant status of the BR
2088
2089 l_prev_trh_rec.transaction_history_id := p_trh_rec.prv_trx_history_id;
2090
2091 AR_BILLS_MAINTAIN_STATUS_PUB.Find_Last_Relevant_trh (l_prev_trh_rec);
2092
2093 IF PG_DEBUG in ('Y', 'C') THEN
2094 arp_util.debug ( 'Previous Relevant Status : ' || l_prev_trh_rec.status);
2095 END IF;
2096
2097
2098
2099 /*----------------------------------------------+
2100 | BR Pending Remittance is Unpaid OR |
2101 | BR Standard Remitted is Unpaid |
2102 +-----------------------------------------------*/
2103
2104 IF (p_trh_rec.status = C_PENDING_REMITTANCE)
2105 OR (p_trh_rec.status = C_REMITTED)
2106 THEN
2107 update_reserved_columns (p_ps_id, NULL , NULL);
2108 p_trh_rec.postable_flag := 'Y';
2109 p_trh_rec.current_accounted_flag:= 'Y';
2110
2111
2112 /*----------------------------------------------+
2113 | Protested BR is Unpaid |
2114 +-----------------------------------------------*/
2115
2116 ELSIF (p_trh_rec.status = C_PROTESTED)
2117 THEN
2118 p_trh_rec.postable_flag := 'N';
2119 p_trh_rec.current_accounted_flag:= 'N';
2120
2121
2122 /*----------------------------------------------+
2123 | BR Endorsed and Closed is Unpaid |
2124 +-----------------------------------------------*/
2125
2126 ELSIF (p_trh_rec.status = C_CLOSED) AND (l_prev_trh_rec.status = C_ENDORSED)
2127 THEN
2128
2129 /*----------------------------------------------+
2130 | Reverse the adjustment which closed the BR |
2131 +-----------------------------------------------*/
2132
2133 Find_Last_Adjustment (p_trh_rec.customer_trx_id, l_adj_id);
2134 Reverse_Adjustment (l_adj_id, p_trh_rec, NULL);
2135 p_trh_rec.postable_flag := 'Y';
2136 p_trh_rec.current_accounted_flag:= 'Y';
2137
2138
2139
2140 /*----------------------------------------------+
2141 | BR Remitted And Closed is Unpaid OR |
2142 | BR Factored And Closed is Unpaid OR |
2143 | BR Paid by a single Receipt is Unpaid |
2144 +-----------------------------------------------*/
2145
2146 ELSIF (p_trh_rec.status = C_CLOSED) AND
2147 (p_trh_rec.event = C_CLOSED OR p_trh_rec.event = C_RISK_ELIMINATED)
2148 THEN
2149
2150 /*----------------------------------------------+
2151 | Reverse the receipt which paid the BR |
2152 +-----------------------------------------------*/
2153
2154 Find_Last_Receipt (p_trh_rec.customer_trx_id, l_cash_receipt_id);
2155 Reverse_Receipt (p_trh_rec, l_cash_receipt_id, p_unpaid_reason, l_called_from);
2156 p_trh_rec.postable_flag := 'Y';
2157 p_trh_rec.current_accounted_flag:= 'Y';
2158
2159
2160 /*----------------------------------------------+
2161 | BR Matured Pend Risk Elim is Unpaid |
2162 +-----------------------------------------------*/
2163
2164 ELSIF (p_trh_rec.status = C_MATURED_PEND_RISK_ELIM)
2165 THEN
2166
2167 /*----------------------------------------------+
2168 | Unapply the receipt from Short Term Debt |
2169 | And Reverse the receipt |
2170 +-----------------------------------------------*/
2171
2172 Find_Last_STD (p_trh_rec.customer_trx_id, l_cash_receipt_id, l_receivable_application_id);
2173 Reverse_Receipt (p_trh_rec, l_cash_receipt_id, p_unpaid_reason, l_called_from);
2174 update_reserved_columns (p_ps_id, NULL , NULL);
2175 p_trh_rec.postable_flag := 'Y';
2176 p_trh_rec.current_accounted_flag:= 'Y';
2177
2178
2179
2180 /*----------------------------------------------+
2181 | BR Endorsed is Unpaid |
2182 +-----------------------------------------------*/
2183
2184 ELSIF (p_trh_rec.status = C_ENDORSED)
2185 THEN
2186
2187 /*----------------------------------------------+
2188 | Reject the adjustment created for the BR |
2189 +-----------------------------------------------*/
2190
2191 Find_Last_Adjustment (p_trh_rec.customer_trx_id, l_adj_id);
2192 Modify_Adjustment (l_adj_id, 'R');
2193 update_reserved_columns (p_ps_id, NULL , NULL);
2194 p_trh_rec.postable_flag := 'Y';
2195 p_trh_rec.current_accounted_flag:= 'Y';
2196
2197 ELSE
2198 IF PG_DEBUG in ('Y', 'C') THEN
2199 arp_util.debug( '>>>>>>>>>> Invalid Action - Case not implemented ');
2200 END IF;
2201 FND_MESSAGE.set_name ( 'AR', 'AR_BR_ACTION_FORBIDDEN' );
2202 FND_MESSAGE.Set_token ( 'ACTION', C_UNPAID);
2203 app_exception.raise_exception;
2204
2205 END IF;
2206
2207
2208 IF PG_DEBUG in ('Y', 'C') THEN
2209 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Unpaid ()-');
2210 END IF;
2211
2212 EXCEPTION
2213 WHEN OTHERS THEN
2214 IF PG_DEBUG in ('Y', 'C') THEN
2215 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Unpaid () ');
2216 END IF;
2217 RAISE;
2218
2219 END Unpaid;
2220
2221
2222 /*==============================================================================+
2223 | PROCEDURE |
2224 | Factore_Recourse |
2225 | |
2226 | DESCRIPTION |
2227 | BR - Factore with Recourse is approved : |
2228 | Create a receipt and apply it to Short Term Debt |
2229 | |
2230 +==============================================================================*/
2231
2232
2233 PROCEDURE Factore_Recourse ( p_batch_rec IN ar_batches%ROWTYPE ,
2234 p_ps_rec IN ar_payment_schedules%ROWTYPE ,
2235 p_trh_rec IN ar_transaction_history%ROWTYPE )
2236
2237 IS
2238
2239 l_cr_id NUMBER;
2240 l_count NUMBER;
2241 l_msg_count NUMBER;
2242 l_msg_data VARCHAR2(2000);
2243 l_return_status VARCHAR2(1);
2244
2245 l_receipt_number VARCHAR2(30) := NULL;
2246 l_receipt_inherit_inv_num_flag VARCHAR2(1);
2247
2248 l_called_from VARCHAR2(30) := C_BR_FACTORED_RECOURSE;
2249 l_exch_rate NUMBER;
2250
2251 l_org_return_status VARCHAR2(1);
2252 l_org_id NUMBER;
2253
2254 BEGIN
2255
2256 IF PG_DEBUG in ('Y', 'C') THEN
2257 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse ()+');
2258 END IF;
2259
2260
2261 /* SSA change */
2262 l_org_id := p_ps_rec.org_id;
2263 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
2264 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2265 p_return_status =>l_org_return_status);
2266
2267 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2268
2269 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse : l_org_return_status <> SUCCESS');
2270 ELSE
2271
2272 /*-----------------------------------------------+
2273 | Check if the receipt number is inherited |
2274 | from the BR. If not inherited, the receipt |
2275 | API will default it from sequence. |
2276 +------------------------------------------------*/
2277
2278 SELECT receipt_inherit_inv_num_flag
2279 INTO l_receipt_inherit_inv_num_flag
2280 FROM AR_RECEIPT_METHODS
2281 WHERE receipt_method_id = p_batch_rec.receipt_method_id;
2282
2283 IF PG_DEBUG in ('Y', 'C') THEN
2284 arp_util.debug ( 'Receipt_inherit_inv_num_flag : ' || l_receipt_inherit_inv_num_flag);
2285 END IF;
2286
2287 IF (l_receipt_inherit_inv_num_flag = 'Y')
2288 THEN
2289 l_receipt_number := p_ps_rec.trx_number;
2290 END IF;
2291
2292
2293 /*-----------------------------------------------+
2294 | Creation of the Receipt using the Receipt API|
2295 +------------------------------------------------*/
2296 -- bug 2649369 : when rate type is corporate, null out rate, as Receipt API expects none to be passed
2297 -- bug 3506385/3572968 : pass rate only when exchange_rate_type = 'User'
2298 -- this is the only case where Receipt API expects a rate
2299
2300 if p_ps_rec.exchange_rate_type <> 'User' then
2301 l_exch_rate := null;
2302 else
2303 l_exch_rate := p_ps_rec.exchange_rate;
2304 end if;
2305
2306 IF PG_DEBUG in ('Y', 'C') THEN
2307 arp_util.debug( '----------------------------------------------------------------');
2308 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.CREATE_CASH : ');
2309 arp_util.debug('p_currency_code : ' || p_ps_rec.invoice_currency_code);
2310 arp_util.debug('p_exchange_rate_type : ' || p_ps_rec.exchange_rate_type);
2311 arp_util.debug('p_exchange_rate : ' || l_exch_rate);
2312 arp_util.debug('p_exchange_rate_date : ' || p_ps_rec.exchange_date);
2313 arp_util.debug('p_remittance_bank_account_id : ' || p_batch_rec.remit_bank_acct_use_id);
2314 arp_util.debug('p_amount : ' || p_ps_rec.amount_due_original);
2315 arp_util.debug('p_receipt_method_id : ' || p_batch_rec.receipt_method_id);
2316 arp_util.debug('p_receipt_number : ' || l_receipt_number);
2317 arp_util.debug('p_customer_id : ' || p_ps_rec.customer_id);
2318 arp_util.debug('p_called_from : ' || l_called_from);
2319 arp_util.debug('p_receipt_date : ' || p_batch_rec.batch_date);
2320 arp_util.debug('p_gl_date : ' || p_batch_rec.gl_date);
2321 arp_util.debug('p_cr_id : ' || l_cr_id);
2322 arp_util.debug('p_org_id : ' || p_ps_rec.org_id);
2323 arp_util.debug('----------------------------------------------------------------');
2324 END IF;
2325
2326 ar_receipt_api_pub.create_cash(
2327 p_api_version => 1.0 ,
2328 p_init_msg_list => FND_API.G_TRUE ,
2329 x_return_status => l_return_status ,
2330 x_msg_count => l_msg_count ,
2331 x_msg_data => l_msg_data ,
2332 p_currency_code => p_ps_rec.invoice_currency_code ,
2333 p_exchange_rate_type => p_ps_rec.exchange_rate_type ,
2334 p_exchange_rate => l_exch_rate ,
2335 p_exchange_rate_date => p_ps_rec.exchange_date ,
2336 p_remittance_bank_account_id => p_batch_rec.remit_bank_acct_use_id,
2337 p_amount => p_ps_rec.amount_due_original ,
2338 p_receipt_method_id => p_batch_rec.receipt_method_id ,
2339 p_receipt_number => l_receipt_number ,
2340 p_customer_id => p_ps_rec.customer_id ,
2341 p_called_from => l_called_from ,
2342 p_receipt_date => p_batch_rec.batch_date ,
2343 p_gl_date => p_batch_rec.gl_date ,
2344 p_cr_id => l_cr_id ,
2345 p_org_id => p_ps_rec.org_id);
2346
2347
2348 IF PG_DEBUG in ('Y', 'C') THEN
2349 arp_util.debug( 'Cash Receipt Id created : ' || l_cr_id);
2350 arp_util.debug( 'Return status : ' || l_return_status);
2351 END IF;
2352
2353 IF (l_return_status <> 'S')
2354 THEN
2355 IF PG_DEBUG in ('Y', 'C') THEN
2356 arp_util.debug( '>>>>>>>>>> Problems during receipt creation');
2357 arp_util.debug( 'l_return_status : ' || l_return_status);
2358 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2359 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2360 END IF;
2361
2362
2363 IF (l_msg_count > 0)
2364 THEN
2365 Set_Api_Error;
2366 ELSE
2367 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_CREATE_REC');
2368 app_exception.raise_exception;
2369 END IF;
2370
2371 END IF;
2372
2373
2374 /*-----------------------------------------------+
2375 | Apply the receipt to Short Term Debt |
2376 +------------------------------------------------*/
2377
2378 Apply_STD (p_ps_rec.customer_trx_id, l_cr_id, p_batch_rec.batch_date, p_batch_rec.gl_date);
2379
2380
2381 /*----------------------------------------------+
2382 | UPD Cash Receipt History with Batch ID |
2383 +-----------------------------------------------*/
2384
2385
2386 /* Bug 1398843 */
2387 arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,p_batch_rec.batch_id);
2388
2389 END IF;
2390
2391 IF PG_DEBUG in ('Y', 'C') THEN
2392 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse ()-');
2393 END IF;
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 IF PG_DEBUG in ('Y', 'C') THEN
2398 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Factore_Recourse : ' || SQLERRM);
2399 END IF;
2400 RAISE;
2401
2402 END Factore_Recourse;
2403
2404
2405 /*==============================================================================+
2406 | PROCEDURE |
2407 | Factore_Without_Recourse |
2408 | |
2409 | DESCRIPTION |
2410 | BR - Factore without Recourse is approved : |
2411 | Create a receipt and apply it to the BR |
2412 | |
2413 +==============================================================================*/
2414
2415
2416 PROCEDURE Factore_Without_Recourse ( p_batch_rec IN ar_batches%ROWTYPE ,
2417 p_ps_rec IN ar_payment_schedules%ROWTYPE )
2418 IS
2419
2420 l_cr_id NUMBER;
2421 l_count NUMBER;
2422 l_msg_count NUMBER;
2423 l_msg_data VARCHAR2(2000);
2424 l_return_status VARCHAR2(1);
2425
2426 l_receipt_number VARCHAR2(20) := NULL;
2427 l_receipt_inherit_inv_num_flag VARCHAR2(1);
2428 l_exch_rate NUMBER;
2429 l_org_return_status VARCHAR2(1);
2430 l_org_id NUMBER;
2431
2432 BEGIN
2433
2434 IF PG_DEBUG in ('Y', 'C') THEN
2435 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse ()+');
2436 END IF;
2437
2438 /* SSA change */
2439 l_org_id := p_ps_rec.org_id;
2440 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
2441 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2442 p_return_status =>l_org_return_status);
2443
2444 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2445
2446 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse : l_org_return_status <> SUCCESS');
2447 ELSE
2448
2449 /*-----------------------------------------------+
2450 | Check if the receipt number is inherited |
2451 | from the BR. If not inherited, the receipt |
2452 | API will default it from sequence. |
2453 +------------------------------------------------*/
2454
2455 SELECT receipt_inherit_inv_num_flag
2456 INTO l_receipt_inherit_inv_num_flag
2457 FROM AR_RECEIPT_METHODS
2458 WHERE receipt_method_id = p_batch_rec.receipt_method_id;
2459
2460 IF PG_DEBUG in ('Y', 'C') THEN
2461 arp_util.debug ( 'Receipt_inherit_inv_num_flag : ' || l_receipt_inherit_inv_num_flag);
2462 END IF;
2463
2464 IF (l_receipt_inherit_inv_num_flag = 'Y')
2465 THEN
2466 l_receipt_number := p_ps_rec.trx_number;
2467 END IF;
2468
2469
2470 IF PG_DEBUG in ('Y', 'C') THEN
2471 arp_util.debug( '----------------------------------------------------------------');
2472 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.CREATE_AND_APPLY : ');
2473 arp_util.debug( 'p_applied_payment_schedule_id : ' || p_ps_rec.payment_schedule_id );
2474 arp_util.debug( 'p_amount : ' || p_ps_rec.amount_due_remaining);
2475 arp_util.debug( 'p_currency_code : ' || p_ps_rec.invoice_currency_code);
2476 arp_util.debug( 'p_exchange_rate : ' || p_ps_rec.exchange_rate);
2477 arp_util.debug( 'p_receipt_number : ' || l_receipt_number);
2478 arp_util.debug( 'p_receipt_method_id : ' || p_batch_rec.receipt_method_id);
2479 arp_util.debug( 'p_customer_id : ' || p_ps_rec.customer_id);
2480 arp_util.debug( 'remit bank account use id : ' || p_batch_rec.remit_bank_acct_use_id);
2481 arp_util.debug( 'p_called_from : ' || C_BR_FACTORED);
2482 arp_util.debug( 'p_link_to_trx_hist_id : ' || NULL);
2483 arp_util.debug( 'p_org_id : ' || to_char(p_ps_rec.org_id));
2484 arp_util.debug( '----------------------------------------------------------------');
2485 END IF;
2486
2487 -- bug 3506385/3572968 : pass rate ONLY when exchange_rate_type = 'User'
2488 -- this is the only case where Receipt API expects a rate
2489
2490 if p_ps_rec.exchange_rate_type <> 'User' then
2491 l_exch_rate := null;
2492 else
2493 l_exch_rate := p_ps_rec.exchange_rate;
2494 end if;
2495
2496
2497 AR_RECEIPT_API_PUB.create_and_apply(
2498 p_api_version => 1.0 ,
2499 p_init_msg_list => FND_API.G_TRUE ,
2500 x_return_status => l_return_status ,
2501 x_msg_count => l_msg_count ,
2502 x_msg_data => l_msg_data ,
2503 p_currency_code => p_ps_rec.invoice_currency_code ,
2504 p_exchange_rate_type => p_ps_rec.exchange_rate_type ,
2505 p_exchange_rate => p_ps_rec.exchange_rate ,
2506 p_exchange_rate_date => p_ps_rec.exchange_date ,
2507 p_amount => p_ps_rec.amount_due_remaining ,
2508 p_receipt_number => l_receipt_number ,
2509 p_receipt_method_id => p_batch_rec.receipt_method_id ,
2510 p_customer_id => p_ps_rec.customer_id ,
2511 p_applied_payment_schedule_id => p_ps_rec.payment_schedule_id ,
2512 p_remittance_bank_account_id => p_batch_rec.remit_bank_acct_use_id,
2513 p_called_from => C_BR_FACTORED ,
2514 p_link_to_trx_hist_id => NULL ,
2515 p_receipt_date => p_batch_rec.batch_date ,
2516 p_gl_date => p_batch_rec.gl_date ,
2517 p_apply_date => p_batch_rec.batch_date ,
2518 p_apply_gl_date => p_batch_rec.gl_date ,
2519 p_cr_id => l_cr_id ,
2520 p_org_id => p_ps_rec.org_id);
2521
2522 IF PG_DEBUG in ('Y', 'C') THEN
2523 arp_util.debug( 'Cash Receipt Id created : ' || l_cr_id);
2524 arp_util.debug( 'Return status : ' || l_return_status);
2525 arp_util.debug( 'l_msg_count : ' || to_char(l_msg_count));
2526 END IF;
2527
2528
2529 IF (l_return_status <> 'S')
2530 THEN
2531 IF PG_DEBUG in ('Y', 'C') THEN
2532 arp_util.debug( '>>>>>>>>>> Problems during Receipt Creation and Application');
2533 arp_util.debug( 'l_return_status : ' || l_return_status);
2534 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2535 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2536 END IF;
2537
2538
2539 IF (l_msg_count > 0)
2540 THEN
2541 Set_Api_Error;
2542 ELSE
2543 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_CREATE_APPLY_REC');
2544 app_exception.raise_exception;
2545 END IF;
2546
2547 END IF;
2548
2549 /*----------------------------------------------+
2550 | UPD Cash Receipt History with Batch ID |
2551 +-----------------------------------------------*/
2552
2553
2554 /* Bug 1398843 */
2555 arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,p_batch_rec.batch_id);
2556
2557 END IF;
2558
2559
2560 IF PG_DEBUG in ('Y', 'C') THEN
2561 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse ()-');
2562 END IF;
2563
2564 EXCEPTION
2565 WHEN OTHERS THEN
2566 IF PG_DEBUG in ('Y', 'C') THEN
2567 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Factore_Without_Recourse : ' || SQLERRM);
2568 END IF;
2569 RAISE;
2570
2571 END Factore_Without_Recourse;
2572
2573
2574 /*==============================================================================+
2575 | PROCEDURE |
2576 | Apply_Receipt |
2577 | |
2578 | DESCRIPTION |
2579 | Receipt Application |
2580 | |
2581 +==============================================================================*/
2582
2583
2584 PROCEDURE Apply_Receipt (p_trh_rec IN ar_transaction_history%ROWTYPE ,
2585 p_ps_rec IN ar_payment_schedules%ROWTYPE ,
2586 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE ,
2587 p_called_from IN VARCHAR2 )
2588
2589 IS
2590
2591 l_msg_count NUMBER;
2592 l_msg_data VARCHAR2(2000);
2593 l_return_status VARCHAR2(1);
2594 l_move_deferred_tax VARCHAR2(1);
2595 l_org_return_status VARCHAR2(1);
2596 l_org_id NUMBER;
2597
2598 BEGIN
2599
2600 IF PG_DEBUG in ('Y', 'C') THEN
2601 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_Receipt ()+');
2602 END IF;
2603
2604 /* SSA change */
2605 l_org_id := p_trh_rec.org_id;
2606 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
2607 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
2608 p_return_status =>l_org_return_status);
2609
2610 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2611
2612 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_receipt : l_org_return_status <> SUCCESS');
2613 ELSE
2614
2615 /*----------------------------------------------+
2616 | Create normal application using Receipt API |
2617 +-----------------------------------------------*/
2618
2619
2620 IF (p_ps_rec.tax_remaining IS NOT NULL and p_ps_rec.tax_remaining <> 0)
2621 THEN
2622 l_move_deferred_tax := 'Y';
2623 ELSE
2624 l_move_deferred_tax := 'N';
2625 END IF;
2626
2627
2628 IF PG_DEBUG in ('Y', 'C') THEN
2629 arp_util.debug( '------------------------------------------------');
2630 arp_util.debug( 'PARAMETERS PASSED TO AR_RECEIPT_API_PUB.APPLY : ');
2631 arp_util.debug( 'p_cash_receipt_id : ' || p_cash_receipt_id);
2632 arp_util.debug( 'p_customer_trx_id : ' || p_trh_rec.customer_trx_id);
2633 arp_util.debug( 'p_applied_payment_schedule_id : ' || p_ps_rec.payment_schedule_id);
2634 arp_util.debug( 'p_amount_applied : ' || p_ps_rec.amount_due_remaining);
2635 arp_util.debug( 'p_apply_date : ' || p_trh_rec.trx_date);
2636 arp_util.debug( 'p_apply_gl_date : ' || p_trh_rec.gl_date);
2637 arp_util.debug( 'p_move_deffered_tax : ' || l_move_deferred_tax);
2638 arp_util.debug( 'p_org_id : ' || to_char(p_trh_rec.org_id));
2639 arp_util.debug( '------------------------------------------------');
2640 END IF;
2641
2642 AR_RECEIPT_API_PUB.Apply (
2643 p_api_version => 1.0 ,
2644 p_init_msg_list => FND_API.G_TRUE ,
2645 x_return_status => l_return_status ,
2646 x_msg_count => l_msg_count ,
2647 x_msg_data => l_msg_data ,
2648 p_cash_receipt_id => p_cash_receipt_id ,
2649 p_customer_trx_id => p_trh_rec.customer_trx_id ,
2650 p_applied_payment_schedule_id => p_ps_rec.payment_schedule_id ,
2651 p_amount_applied => p_ps_rec.amount_due_remaining ,
2652 p_apply_date => p_trh_rec.trx_date ,
2653 p_apply_gl_date => p_trh_rec.gl_date ,
2654 p_called_from => p_called_from ,
2655 p_move_deferred_tax => l_move_deferred_tax ,
2656 p_org_id => p_trh_rec.org_id);
2657
2658 IF (l_return_status <> 'S')
2659 THEN
2660 IF PG_DEBUG in ('Y', 'C') THEN
2661 arp_util.debug( '>>>>>>>>>> Problems during Receipt Application');
2662 arp_util.debug( 'l_return_status : ' || l_return_status);
2663 arp_util.debug( 'l_msg_count : ' || l_msg_count);
2664 arp_util.debug( 'l_msg_data : ' || l_msg_data);
2665 END IF;
2666
2667
2668 IF (l_msg_count > 0)
2669 THEN
2670 Set_Api_Error;
2671 ELSE
2672 FND_MESSAGE.SET_NAME ('AR', 'AR_BR_CANNOT_APPLY_REC');
2673 app_exception.raise_exception;
2674 END IF;
2675
2676 END IF;
2677 END IF;
2678
2679
2680 IF PG_DEBUG in ('Y', 'C') THEN
2681 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Apply_Receipt ()-');
2682 END IF;
2683
2684 EXCEPTION
2685 WHEN OTHERS THEN
2686 IF PG_DEBUG in ('Y', 'C') THEN
2687 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Apply_Receipt : ' || SQLERRM);
2688 END IF;
2689 RAISE;
2690
2691 END Apply_Receipt;
2692
2693
2694 /*==============================================================================+
2695 | PROCEDURE |
2696 | Link_Application_History |
2697 | |
2698 | DESCRIPTION |
2699 | Populates LINK_TO_TRX_HIST_ID on the application that closes the |
2700 | BR at the end of the remittance process (Factore Without Recourse) |
2701 | |
2702 +==============================================================================*/
2703
2704
2705 PROCEDURE Link_Application_History (p_trh_rec IN ar_transaction_history%ROWTYPE)
2706
2707 IS
2708
2709 CURSOR last_application_cur IS
2710 SELECT receivable_application_id
2711 FROM ar_receivable_applications
2712 WHERE applied_customer_trx_id = p_trh_rec.customer_trx_id
2713 AND status = 'APP'
2714 AND display = 'Y'
2715 ORDER BY receivable_application_id DESC;
2716
2717 last_application_rec last_application_cur%ROWTYPE;
2718
2719
2720 BEGIN
2721
2722 IF PG_DEBUG in ('Y', 'C') THEN
2723 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Link_Application_History ()+');
2724 END IF;
2725
2726 /*----------------------------------------------+
2727 | Fetch last Application |
2728 +-----------------------------------------------*/
2729
2730 OPEN last_application_cur;
2731 FETCH last_application_cur INTO last_application_rec;
2732
2733 IF (last_application_cur%NOTFOUND)
2734 THEN
2735 IF PG_DEBUG in ('Y', 'C') THEN
2736 arp_util.debug ('Link_Application_History: ' || '>>>>>>>>>> Last Application could not be found');
2737 END IF;
2738 CLOSE last_application_cur;
2739 APP_EXCEPTION.raise_exception;
2740 END IF;
2741
2742 CLOSE last_application_cur;
2743
2744
2745 /*----------------------------------------------+
2746 | Populate the LINK_TO_TRX_HIST_ID on the |
2747 | application that closes the BR for Factored |
2748 | without Recourse case. |
2749 +-----------------------------------------------*/
2750
2751 ARP_APP_PKG.lock_p (last_application_rec.receivable_application_id);
2752
2753
2754 /*----------------------------------------------+
2755 | Update the LINK_TO_TRX_HIST_ID on the |
2756 | application |
2757 +-----------------------------------------------*/
2758
2759 UPDATE ar_receivable_applications
2760 SET LINK_TO_TRX_HIST_ID = p_trh_rec.transaction_history_id
2761 WHERE receivable_application_id = last_application_rec.receivable_application_id;
2762
2763
2764 IF PG_DEBUG in ('Y', 'C') THEN
2765 arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Link_Application_History ()-');
2766 END IF;
2767
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 IF PG_DEBUG in ('Y', 'C') THEN
2771 arp_util.debug('>>>>>>>>>> EXCEPTION : AR_BILLS_MAINTAIN_LIB_PVT.Link_Application_History : ' || SQLERRM);
2772 END IF;
2773 IF (last_application_cur%ISOPEN)
2774 THEN
2775 CLOSE last_application_cur;
2776 END IF;
2777 RAISE;
2778
2779 END Link_Application_History;
2780
2781
2782 END AR_BILLS_MAINTAIN_LIB_PVT ;