[Home] [Help]
PACKAGE BODY: APPS.ARP_REVERSE_RECEIPT
Source
1 PACKAGE BODY ARP_REVERSE_RECEIPT AS
2 /* $Header: ARREREVB.pls 120.31 2010/11/15 07:29:29 npanchak ship $*/
3
4 /* =======================================================================
5 | Global Data Types
6 + ======================================================================*/
7 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
8
9 --
10 -- Private procedures used by the package
11 --
12 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13
14 PROCEDURE validate_args(
15 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
16 p_reversal_category IN VARCHAR2,
17 p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
18 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
19 p_reversal_reason_code IN VARCHAR2 );
20 --
21 PROCEDURE update_current_cr_rec(
22 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE,
23 p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
24 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
25 p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
26 p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
27 p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
28 p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
29 p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
30 p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
31 p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
32 p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
33 p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
34 p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
35 p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
36 p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
37 p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
38 p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
39 p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
40 p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
41 p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
42 p_attribute15 IN ar_cash_receipts.attribute15%TYPE );
43 --
44 PROCEDURE update_current_crh_record(
45 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
46 p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
47 p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
48 p_crh_id_new IN ar_cash_receipt_history.cash_receipt_history_id%TYPE );
49 --
50 PROCEDURE insert_reversal_crh_record(
51 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
52 p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
53 p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
54 p_clear_batch_id IN VARCHAR2,
55 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE
56 );
57 --
58 PROCEDURE insert_reversal_mcd_record(
59 p_mcd_rec IN OUT NOCOPY ar_misc_cash_distributions%ROWTYPE,
60 p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
61 p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE );
62 --
63 PROCEDURE insert_reversal_dist_rec(
64 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
65 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE );
66 --
67 FUNCTION check_cb( p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE )
68 RETURN BOOLEAN;
69 --
70 PROCEDURE modify_update_ps_rec(
71 p_cr_id IN ar_payment_schedules.cash_receipt_id%TYPE,
72 p_reversal_gl_date IN DATE,
73 p_reversal_date IN DATE );
74 --
75 PROCEDURE modify_update_bat_rec( p_bat_id IN ar_batches.batch_id%TYPE,
76 p_cr_amount IN ar_cash_receipts.amount%TYPE,
77 p_status IN VARCHAR2 );
78 --
79 PROCEDURE validate_dm_reversal_args(
80 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
81 p_cc_id IN ra_cust_trx_line_gl_dist.code_combination_id%TYPE,
82 p_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
83 p_reversal_gl_date IN
84 ar_cash_receipt_history.reversal_gl_date%TYPE,
85 p_reversal_date IN
86 ar_cash_receipts.reversal_date%TYPE,
87 p_reversal_reason_code IN VARCHAR2);
88 --
89 --
90 -- Externally visible procedure
91 --
92 /*===========================================================================+
93 | PROCEDURE |
94 | reverse |
95 | |
96 | DESCRIPTION |
97 | Reverse an receipt n |
98 | |
99 | SCOPE - PUBLIC |
100 | |
101 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
102 | arp_process_application.reverse - Reverse an application |
103 | arp_cash_receipts_pkg.fetch_p - Fetch a record from ar_cash_receipts |
104 | arp_app_pkg.lock_p - lock a record in |
105 | AR_RECEIVABLE_APPLICATIONS table |
106 | |
107 | ARGUMENTS : IN: |
108 | p_cr_id - Cahs receipt ID |
109 | p_reversal_category - Reversal Category |
110 | p_reversal_gl_date - Reversal GL date |
111 | p_reversal_date - Reversal Date |
112 | p_reversal_reason_code - Reason for reversal |
113 | p_reversal_comments - Reversal comments |
114 | p_clear_batch_id - Flag to denote if the batch Id |
115 | should be nulled out NOCOPY or not |
116 | p_module_name - Name of module that called this proc. |
117 | p_module_version - Version of the module that called |
118 | this procedure |
119 | OUT: |
120 | |
121 | RETURNS : NONE |
122 | |
123 | NOTES |
124 | |
125 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
126 | |
127 +===========================================================================*/
128 PROCEDURE reverse (
129 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
130 p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
131 p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
132 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
133 p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
134 p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
135 p_clear_batch_id IN ar_cash_receipt_history.batch_id%TYPE,
136 p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
137 p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
138 p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
139 p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
140 p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
141 p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
142 p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
143 p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
144 p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
145 p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
146 p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
147 p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
148 p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
149 p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
150 p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
151 p_attribute15 IN ar_cash_receipts.attribute15%TYPE,
152 p_module_name IN VARCHAR2,
153 p_module_version IN VARCHAR2,
154 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE,
155 p_called_from IN VARCHAR2 DEFAULT NULL) IS /* jrautiai BR implementation */
156 --
157 l_cr_rec ar_cash_receipts%ROWTYPE;
158 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
159 l_crh_rec ar_cash_receipt_history%ROWTYPE;
160 l_batches_rec ar_batches%ROWTYPE;
161 l_mcd_rec ar_misc_cash_distributions%ROWTYPE;
162 /*CCR-add cursor for receipt method pkt */
163
164 --
165 l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
166 l_ra_id ar_receivable_applications.receivable_application_id%TYPE;
167 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
168 l_mcd_id ar_misc_cash_distributions.misc_cash_distribution_id%TYPE;
169 l_rm_code ar_receipt_methods.payment_type_code%TYPE;
170 l_rt_type ar_receivables_trx.type%TYPE;
171
172 --
173 l_gl_date_closed DATE;
174 l_actual_date_closed DATE;
175
176 v_credit_card BOOLEAN; --CCRR pkt
177
178 l_batch_id ar_batches.batch_id%TYPE;
179 ln_bal_due_remaining NUMBER; /* placeholder for bug 584303 */
180 l_return_status VARCHAR2(1);
181 l_msg_count NUMBER;
182 l_msg_data VARCHAR2(2000);
183
184 -- Bug 7241111
185 l_llca_exist varchar(1) := 'N';
186
187 unapply_netting_err EXCEPTION;
188 cancel_refund_err EXCEPTION;
189
190 --
191 TYPE l_ps_ra_record IS RECORD
192 (
193 acctd_amount_applied NUMBER,
194 amount_applied NUMBER,
195 earned_discount_taken NUMBER,
196 unearned_discount_taken NUMBER,
197 acctd_earned_discount_taken NUMBER,
198 acctd_unearned_discount_taken NUMBER,
199 line_applied NUMBER,
200 tax_applied NUMBER,
201 freight_applied NUMBER,
202 receivables_charges_applied NUMBER
203 );
204 --
205 /* 08-AUG-2000 J Rautiainen BR Implementation */
206 CURSOR ar_ra_C( p_cr_id ar_cash_receipts.cash_receipt_id%TYPE ) IS
207 SELECT receivable_application_id
208 , receivables_trx_id
209 , applied_payment_schedule_id
210 , application_ref_id
211 FROM ar_receivable_applications
212 WHERE cash_receipt_id = p_cr_id
213 AND ( (
214 status||'' in ('APP', 'ACC','ACTIVITY','OTHER ACC')
215 AND display = 'Y'
216 )
217 OR
218 (
219 status||'' in ('UNAPP', 'UNID')
220 )
221 )
222 AND reversal_gl_date is NULL
223 ORDER BY decode(status,'APP',1,'ACTIVITY',2,'ACC',3,'OTHER ACC',4,'UNID',5,'UNAPP',6); --VAT 11.5 for pairing UNAPP records
224 --
225 CURSOR ar_mcd_C( p_cr_id ar_cash_receipts.cash_receipt_id%TYPE ) IS
226 SELECT *
227 FROM ar_misc_cash_distributions
228 WHERE cash_receipt_id = p_cr_id
229 AND reversal_gl_date is null;
230 --
231 /*CCRR- need to find the application id associated with the Negative Misc receipt pkt*/
232 /* bug3635777 : Added table ar_cash_receipts to avoid FTS on ra table */
233
234 CURSOR ar_rc_rec( p_cr_id ar_cash_receipts.cash_receipt_id%TYPE ) IS
235 SELECT ra.receivable_application_id
236 FROM ar_receivable_applications ra ,ar_cash_receipts cr
237 WHERE cr.reference_id = ra.cash_receipt_id
238 AND cr.cash_receipt_id = p_cr_id
239 AND ra.application_ref_id = p_cr_id
240 AND ra.application_ref_type = 'MISC_RECEIPT';
241
242 --
243 /* CCRR-need to find the receipt payment type code to determine if the MISC receipt is of type Credit_card pkt*/
244 CURSOR ar_rm_C(p_receipt_method_id ar_receipt_methods.receipt_method_id%TYPE) is
245 Select payment_channel_code
246 FROM ar_receipt_methods
247 where receipt_method_id = p_receipt_method_id;
248
249 BEGIN
250 IF PG_DEBUG in ('Y', 'C') THEN
251 arp_standard.debug('validate_args: ' || 'arp_reverse_receipt.reverse() +');
252 arp_standard.debug('validate_args: ' || 'cr_id = '||to_char( p_cr_id ) );
253 END IF;
254 -- Validate input arguments
255 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
256 validate_args( p_cr_id, p_reversal_category,
257 p_reversal_gl_date, p_reversal_date,
258 p_reversal_reason_code );
259 END IF;
260 --
261 -- Populate the ar_cash_receipts record from
262 -- ar_cash_receipts table. Use cash_receipt_id for selection.
263 --
264 l_cr_rec.cash_receipt_id := p_cr_id;
265 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
266 --
267 -- Check if CB is associated with the application and if CB has a activity
268 -- or is posted. If so, return error message.
269 --
270 IF ( check_cb( p_cr_id ) = FALSE ) THEN
271 IF PG_DEBUG in ('Y', 'C') THEN
272 arp_standard.debug('validate_args: ' || 'Check CB Failed' );
273 END IF;
274 FND_MESSAGE.set_name('AR', 'AR_DEBIT_REVERSE');
275 APP_EXCEPTION.raise_exception;
276 END IF ;
277 --
278 --
279
280 --{
281 -- Bug 7241111 to retain the old application record under activity details
282
283 begin
284 select 'Y' into l_llca_exist
285 from ar_activity_details
286 where cash_receipt_id = p_cr_id
287 and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'Y';
288
289 exception
290 when too_many_rows then
291 l_llca_exist := 'Y';
292 when no_data_found then
293 l_llca_exist := 'N';
294 when others then
295 l_llca_exist := 'N';
296 end;
297
298
299
300 IF NVL(l_llca_exist,'N') = 'Y' THEN
301
302 INSERT INTO AR_ACTIVITY_DETAILS(
303 CASH_RECEIPT_ID,
304 CUSTOMER_TRX_LINE_ID,
305 ALLOCATED_RECEIPT_AMOUNT,
306 AMOUNT,
307 TAX,
308 FREIGHT,
309 CHARGES,
310 LAST_UPDATE_DATE,
311 LAST_UPDATED_BY,
312 LINE_DISCOUNT,
313 TAX_DISCOUNT,
314 FREIGHT_DISCOUNT,
315 LINE_BALANCE,
316 TAX_BALANCE,
317 CREATION_DATE,
318 CREATED_BY,
319 LAST_UPDATE_LOGIN,
320 COMMENTS,
321 APPLY_TO,
322 ATTRIBUTE1,
323 ATTRIBUTE2,
324 ATTRIBUTE3,
325 ATTRIBUTE4,
326 ATTRIBUTE5,
327 ATTRIBUTE6,
328 ATTRIBUTE7,
329 ATTRIBUTE8,
330 ATTRIBUTE9,
331 ATTRIBUTE10,
332 ATTRIBUTE11,
333 ATTRIBUTE12,
334 ATTRIBUTE13,
335 ATTRIBUTE14,
336 ATTRIBUTE15,
337 ATTRIBUTE_CATEGORY,
338 GROUP_ID,
339 REFERENCE1,
340 REFERENCE2,
341 REFERENCE3,
342 REFERENCE4,
343 REFERENCE5,
344 OBJECT_VERSION_NUMBER,
345 CREATED_BY_MODULE,
346 SOURCE_ID,
347 SOURCE_TABLE,
348 LINE_ID,
349 CURRENT_ACTIVITY_FLAG)
350 SELECT
351 LLD.CASH_RECEIPT_ID,
352 LLD.CUSTOMER_TRX_LINE_ID,
353 LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
354 LLD.AMOUNT*-1,
355 LLD.TAX*-1,
356 LLD.FREIGHT*-1,
357 LLD.CHARGES*-1,
358 LLD.LAST_UPDATE_DATE,
359 LLD.LAST_UPDATED_BY,
360 LLD.LINE_DISCOUNT,
361 LLD.TAX_DISCOUNT,
362 LLD.FREIGHT_DISCOUNT,
363 LLD.LINE_BALANCE,
364 LLD.TAX_BALANCE,
365 LLD.CREATION_DATE,
366 LLD.CREATED_BY,
367 LLD.LAST_UPDATE_LOGIN,
368 LLD.COMMENTS,
369 LLD.APPLY_TO,
370 LLD.ATTRIBUTE1,
371 LLD.ATTRIBUTE2,
372 LLD.ATTRIBUTE3,
373 LLD.ATTRIBUTE4,
374 LLD.ATTRIBUTE5,
375 LLD.ATTRIBUTE6,
376 LLD.ATTRIBUTE7,
377 LLD.ATTRIBUTE8,
378 LLD.ATTRIBUTE9,
379 LLD.ATTRIBUTE10,
380 LLD.ATTRIBUTE11,
381 LLD.ATTRIBUTE12,
382 LLD.ATTRIBUTE13,
383 LLD.ATTRIBUTE14,
384 LLD.ATTRIBUTE15,
385 LLD.ATTRIBUTE_CATEGORY,
386 LLD.GROUP_ID,
387 LLD.REFERENCE1,
388 LLD.REFERENCE2,
389 LLD.REFERENCE3,
390 LLD.REFERENCE4,
391 LLD.REFERENCE5,
392 LLD.OBJECT_VERSION_NUMBER,
393 LLD.CREATED_BY_MODULE,
394 LLD.SOURCE_ID,
395 LLD.SOURCE_TABLE,
396 ar_Activity_details_s.nextval,
397 'R'
398 FROM ar_Activity_details LLD
399 where LLD.cash_receipt_id = p_cr_id
400 and nvl(LLD.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
401
402 UPDATE ar_Activity_details dtl
403 set CURRENT_ACTIVITY_FLAG = 'N'
404 where dtl.cash_receipt_id = p_cr_id
405 and nvl(dtl.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
406
407 END IF;
408
409
410
411 --}
412 arp_cr_history_pkg.fetch_f_crid( p_cr_id, l_crh_rec_old );
413 --
414 l_crh_rec := l_crh_rec_old;
415 --
416 -- Insert new receipt history record and get back the
417 -- new cash_receipt_history_id into l_crh_id_new
418 --
419 insert_reversal_crh_record( l_crh_rec,
420 p_reversal_gl_date, p_reversal_date,
421 p_clear_batch_id, p_crh_id ); /* Bug fix 3079331*/
422
423 /*p_crh_id := l_crh_rec.cash_receipt_history_id;*/
424 --
425 -- Update old cash receipt history record to set all reversal columns.
426 --
427 update_current_crh_record( l_crh_rec_old, p_reversal_gl_date,
428 p_reversal_date, l_crh_rec.cash_receipt_history_id );
429 --
430
431 -- Insert opposing Journal Entries into ar_distributions to
432 -- back out NOCOPY all existing entries belong to this cash receipt
433 --
434 insert_reversal_dist_rec( p_cr_id,l_crh_rec.cash_receipt_history_id );
435 --
436
437 --
438 IF PG_DEBUG in ('Y', 'C') THEN
439 arp_standard.debug('validate_args: ' || 'cr_rec.type = '||l_cr_rec.type );
440 END IF;
441 -- if receipt_type is 'MISC', fetch the distribution record and
442 -- for each fetched record, insert its opposite record.
443 IF ( l_cr_rec.type = 'MISC' ) THEN
444 OPEN ar_mcd_C( p_cr_id );
445 LOOP
446 FETCH ar_mcd_C INTO l_mcd_rec;
447 EXIT WHEN ar_mcd_C%NOTFOUND;
448 --
449 insert_reversal_mcd_record( l_mcd_rec, p_reversal_gl_date,
450 p_reversal_date );
451 --
452 END LOOP;
453 CLOSE ar_mcd_C;
454
455 /* CCRR--Check to see if this 'MISC' receipt is a negative Credit_Card receipt pkt */
456
457 /*Find out NOCOPY if the receipt method for this 'MISC' receipt is of CREDIT_CARD */
458 v_credit_card := FALSE;
459 OPEN ar_rm_C(l_cr_rec.receipt_method_id);
460 FETCH ar_rm_C INTO l_rm_code;
461 if l_rm_code = 'CREDIT_CARD' then
462 v_credit_card := TRUE;
463 end if;
464 CLOSE ar_rm_C;
465
466 /* if the 'MISC' receipt is negative and the receipt method is Credit_card
467 then unapply the CCR on the Cash Receipt associated with this Misc receipt- pkt*/
468
469 IF (l_cr_rec.amount < 0 and v_credit_card and
470 nvl(p_called_from,'NONE') not in ('UNAPPLY_CCR')) then
471 /* this is a negative Misc Receipt*/
472 /* Get the application receipt id that is associated with this negative Misc receipt pkt */
473 OPEN ar_rc_rec(p_cr_id);
474 FETCH ar_rc_rec INTO l_receivable_application_id;
475 CLOSE ar_rc_rec;
476
477 /*call the receipt api to unapply the ccr on the cash receipt pkt*/
478 declare
479 l_return_status varchar2(1);
480 l_msg_count number;
481 l_msg_data varchar2(2000);
482 l_msg_index number;
483
484
485 BEGIN
486 --call the entity handler
487 arp_process_application.reverse(
488 l_receivable_application_id,
489 p_reversal_gl_date,
490 trunc(sysdate),
491 'ARREREVB_MISC',
492 NULL,
493 ln_bal_due_remaining,
494 'REVERSE_MISC');
495 EXCEPTION
496 WHEN OTHERS THEN
497 IF ar_rc_rec%ISOPEN THEN
498 CLOSE ar_rc_rec;
499 END IF;
500 --
501 IF PG_DEBUG in ('Y', 'C') THEN
502 arp_standard.debug('validate_args: ' || 'EXCEPTION: arp_reverse_receipt.reverse' );
503 END IF;
504 RAISE;
505 END;
506 end if; --(l_cr_rec.amount < 0 and v_credit_card)
507 ELSIF ( l_cr_rec.type = 'CASH' ) THEN
508 --
509 -- If receipt_type is 'CASH', fetch each receivable_application_id from
510 -- ar_receivable_applications for the cash_receipt
511 -- For each record fetched, reverse application
512 -- Update ar_payment_schedule record with amount_due_remaining = 0,
513 -- acctd_amount_due_remaining = 0, amount_applied = 0,
514 -- actual_date_closed,last_update_date, last_updated_by, gl_date_closed,
515 -- status = 'CL'
516 -- Update ar_cash_receipts, set status to reversal_category,
517 -- last_update_date = sysdate, last_updated_by = user_id,
518 -- reversal_comments and attributes.
519 --
520
521 FOR l_ra_rec IN ar_ra_C( p_cr_id )
522 LOOP
523 --
524 -- The flag 'Y' is to denote that the procedure
525 -- ar_process_cash_application.reverse is called by another
526 -- procedure and the GL_DATE validation need not be done again.
527 -- If the flag is not 'Y', this implies the procedure is called
528 -- by a form, and GL_DATE validation needs to be done and 'UNAPP'
529 -- record needs to be inserted by calling
530 -- ar_receivable_applications_pkg.insert
531 --
532
533 -- bug 584303: added ln_bal_due_remaining.. Don't need to
534 -- do anything with this.. it is just a place holder.
535
536 -- Bug 2751910 - call receipt api to reverse netting applications
537 -- Bug 3829332 - reversal gl_date passed in to ensure same date
538 -- used as corresponding UNAPP application.
539 IF l_ra_rec.receivables_trx_id = -16 THEN
540 ar_receipt_api_pub.unapply_open_receipt(
541 p_api_version => 1.0
542 , p_init_msg_list => NULL
543 , p_commit => NULL
544 , p_validation_level => NULL
545 , x_return_status => l_return_status
546 , x_msg_count => l_msg_count
547 , x_msg_data => l_msg_data
548 , p_receivable_application_id => l_ra_rec.receivable_application_id
549 , p_reversal_gl_date => p_reversal_gl_date
550 , p_called_from => 'ARREREVB' -- bug 2855180
551 );
552 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
553 THEN
554 RAISE unapply_netting_err;
555 END IF;
556
557 ELSE
558 IF l_ra_rec.applied_payment_schedule_id = -8 THEN
559 ar_refunds_pvt.cancel_refund(
560 p_application_ref_id => l_ra_rec.application_ref_id
561 , p_gl_date => p_reversal_gl_date
562 , x_return_status => l_return_status
563 , x_msg_count => l_msg_count
564 , x_msg_data => l_msg_data);
565 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
566 THEN
567 RAISE cancel_refund_err;
568 END IF;
569 END IF;
570
571 arp_process_application.reverse(
572 l_ra_rec.receivable_application_id,
573 p_reversal_gl_date, p_reversal_date, 'ARREREVB', NULL,
574 ln_bal_due_remaining,p_called_from ); /* jrautiai BR implementation */
575 END IF;
576 END LOOP;
577 --
578 -- Update the payment schedule record of the payment, set
579 -- amount_dur_remaining = 0, amount_applied = 0 and status = 'CL
580 -- actual_date_closed, last_update_date, last_updated_by,
581 -- gl_date_closed, status = 'CL'
582 --
583 modify_update_ps_rec( p_cr_id, p_reversal_gl_date,
584 p_reversal_date);
585
586 END IF;
587 --
588 --
589 update_current_cr_rec( l_cr_rec, p_reversal_category,
590 p_reversal_date, p_reversal_reason_code,
591 p_reversal_comments,
592 p_attribute_category, p_attribute1,
593 p_attribute2, p_attribute3, p_attribute4,
594 p_attribute5, p_attribute6, p_attribute7,
595 p_attribute8, p_attribute9, p_attribute10,
596 p_attribute11, p_attribute12, p_attribute13,
597 p_attribute14, p_attribute15 );
598 --
599 --
600 arp_cr_util.get_batch_id( p_cr_id, l_batch_id );
601 IF ( l_batch_id is NOT NULL )
602 THEN
603 modify_update_bat_rec( l_batch_id, l_cr_rec.amount, p_reversal_category );
604 END IF;
605 --
606 IF PG_DEBUG in ('Y', 'C') THEN
607 arp_standard.debug('validate_args: ' || 'arp_reverse_receipt.reverse() -');
608 END IF;
609 --
610 EXCEPTION
611 WHEN unapply_netting_err THEN
612 IF ar_ra_C%ISOPEN THEN
613 CLOSE ar_ra_C;
614 END IF;
615 --
616 IF PG_DEBUG in ('Y', 'C') THEN
617 arp_standard.debug('validate_args: ' || 'EXCEPTION: arp_reverse_receipt.reverse - error calling ar_receipt_api_pub.unapply_open_receipt' );
618 END IF;
619 RAISE;
620 WHEN cancel_refund_err THEN
621 IF ar_ra_C%ISOPEN THEN
622 CLOSE ar_ra_C;
623 END IF;
624 --
625 IF PG_DEBUG in ('Y', 'C') THEN
626 arp_standard.debug('EXCEPTION: arp_reverse_receipt.reverse - error calling ar_refunds_pvt.cancel_refund' );
627 END IF;
628 RAISE;
629 WHEN OTHERS THEN
630 IF ar_mcd_C%ISOPEN THEN
631 CLOSE ar_mcd_C;
632 END IF;
633 --
634 IF PG_DEBUG in ('Y', 'C') THEN
635 arp_standard.debug('validate_args: ' || 'EXCEPTION: arp_reverse_receipt.reverse' );
636 END IF;
637 RAISE;
638 --
639 END reverse;
640 --
641 /*===========================================================================+
642 | PROCEDURE |
643 | validate_args |
644 | |
645 | DESCRIPTION |
646 | Validate arguments passed to reverse procedure |
647 | |
648 | SCOPE - PRIVATE |
649 | |
650 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
651 | |
652 | ARGUMENTS : IN: |
653 | p_cr_id - Cahs receipt ID |
654 | p_reversal_category - Reversal Category |
655 | p_reversal_gl_date - Reversal GL date |
656 | p_reversal_date - Reversal Date |
657 | p_reversal_reason_code - Reason for reversal |
658 | OUT: |
659 | |
660 | RETURNS : NONE |
661 | |
662 | NOTES |
663 | |
664 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
665 | |
666 +===========================================================================*/
667 PROCEDURE validate_args( p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
668 p_reversal_category IN VARCHAR2,
669 p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
670 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
671 p_reversal_reason_code IN VARCHAR2 ) IS
672 BEGIN
673 IF PG_DEBUG in ('Y', 'C') THEN
674 arp_standard.debug( 'arp_reverse_receipt.validate_args() +');
675 END IF;
676 --
677 IF ( p_cr_id is NULL OR p_reversal_category is NULL OR
678 p_reversal_gl_date is NULL OR p_reversal_date is NULL OR
679 p_reversal_reason_code is NULL ) THEN
680 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
681 APP_EXCEPTION.raise_exception;
682 END IF;
683 --
684 -- Validate gl date. If it is invalid, print an error message
685 --
686 IF ( arp_util.is_gl_date_valid( p_reversal_gl_date ) = FALSE ) THEN
687 IF PG_DEBUG in ('Y', 'C') THEN
688 arp_standard.debug('validate_args: ' || 'invalid gl date' );
689 END IF;
690 FND_MESSAGE.set_name ('AR', 'AR_INF_GL_DATE' );
691 APP_EXCEPTION.raise_exception;
692 END IF;
693 --
694 IF PG_DEBUG in ('Y', 'C') THEN
695 arp_standard.debug( 'arp_reverse_receipt.validate_args() -');
696 END IF;
697 EXCEPTION
698 WHEN OTHERS THEN
699 IF PG_DEBUG in ('Y', 'C') THEN
700 arp_standard.debug('validate_args: ' ||
701 'EXCEPTION: arp_reverse_receipt.validate_args' );
702 END IF;
703 RAISE;
704 --
705 END validate_args;
706 --
707 /*===========================================================================+
708 | PROCEDURE |
709 | insert_reversal_dist_rec |
710 | |
711 | DESCRIPTION |
712 | Prepare the ar_distributions record for insertion into AR_DISTRIBUTIONs|
713 | table |
714 | |
715 | SCOPE - PRIVATE |
716 | |
717 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
718 | arp_distributions_pkg.insert_p - Insert table handler for |
719 | AR_DISTRIBUTIONS table |
720 | |
721 | ARGUMENTS : IN: |
722 | p_crh_id - Cash receipt history id |
723 | OUT: |
724 | |
725 | RETURNS : NONE |
726 | |
727 | NOTES |
728 | |
729 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
730 | 04-JAN-99 D. Jancis Modified for VAT changes for 11.5, added |
731 | currency_code, currency_conversion_rate, |
732 | currency_conversion_type, |
733 | currency_conversion_date, third_party_id, |
734 | third_pary_sub_id. |
735 | |
736 | 27-Jun-02 D.Jancis Modified for mrc trigger replacement. |
737 | added call to ar_mrc_engine2 for processing|
738 | ar_distributions inserts. |
739 +===========================================================================*/
740 PROCEDURE insert_reversal_dist_rec(
741 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
742 p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
743 --
744 l_dist_rec ar_distributions%ROWTYPE;
745 l_cr_rec ar_cash_receipts%ROWTYPE; /* added for VAT */
746 l_crh_rec ar_cash_receipt_history%ROWTYPE; /* added for VAT */
747 --
748 CURSOR ar_dist_C(
749 p_cr_id ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
750 SELECT
751 dist.source_type,
752 dist.code_combination_id,
753 nvl(SUM( nvl(dist.AMOUNT_DR,0)),0) -
754 nvl(SUM( nvl(dist.AMOUNT_CR,0) ),0) amount_cr,
755 nvl(SUM( nvl(dist.ACCTD_AMOUNT_DR,0) ),0) -
756 nvl(SUM( nvl(dist.ACCTD_AMOUNT_CR,0) ),0) acctd_amount_cr
757 FROM ar_distributions dist,
758 ar_cash_receipt_history crh
759 WHERE dist.source_id = crh.cash_receipt_history_id
760 AND crh.cash_receipt_id = p_cr_id
761 ANd dist.source_table = 'CRH'
762 GROUP BY dist.source_type,
763 dist.code_combination_id;
764
765 --Bug#2750340
766 l_xla_ev_rec arp_xla_events.xla_events_type;
767
768 BEGIN
769
770 IF PG_DEBUG in ('Y', 'C') THEN
771 arp_standard.debug( 'arp_reverse_receipt.insert_reversal_dist_rec() +');
772 arp_standard.debug('insert_reversal_dist_rec: ' || '-- cr_id = '||to_char( p_cr_id ) );
773 arp_standard.debug('insert_reversal_dist_rec: ' || '-- crh_id = '||to_char( p_crh_id ) );
774 arp_standard.debug('insert_reversal_dist_rec: ' || '-- Fetching the cash receipt record -- ');
775 END IF;
776 -- Fetch the cash receipt record
777 l_cr_rec.cash_receipt_id := p_cr_id;
778 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
779
780 -- Fetch the history record
781 arp_cr_history_pkg.fetch_p( p_crh_id, l_crh_rec );
782
783 -- 11.5 VAT changes:
784 l_dist_rec.currency_code := l_cr_rec.currency_code;
785 l_dist_rec.currency_conversion_rate := l_crh_rec.exchange_rate;
786 l_dist_rec.currency_conversion_type := l_crh_rec.exchange_rate_type;
787 l_dist_rec.currency_conversion_date := l_crh_rec.exchange_date;
788 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
789 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
790
791
792 --
793 FOR l_dist_cursor_rec IN ar_dist_C( p_cr_id )
794 LOOP
795 l_dist_rec.source_type := l_dist_cursor_rec.source_type;
796 l_dist_rec.source_table := 'CRH';
797 l_dist_rec.source_id := p_crh_id;
798 l_dist_rec.code_combination_id := l_dist_cursor_rec.code_combination_id;
799
800 IF ( l_dist_cursor_rec.amount_cr < 0 )
801 THEN
802 l_dist_rec.amount_dr := -l_dist_cursor_rec.amount_cr;
803 l_dist_rec.amount_cr := NULL;
804 ELSE
805 l_dist_rec.amount_dr := NULL;
806 l_dist_rec.amount_cr := l_dist_cursor_rec.amount_cr;
807 END IF;
808
809 IF ( l_dist_cursor_rec.acctd_amount_cr < 0 )
810 THEN
811 l_dist_rec.acctd_amount_dr := -l_dist_cursor_rec.acctd_amount_cr;
812 l_dist_rec.acctd_amount_cr := NULL;
813 ELSE
814 l_dist_rec.acctd_amount_dr := NULL;
815 l_dist_rec.acctd_amount_cr := l_dist_cursor_rec.acctd_amount_cr;
816 END IF;
817
818 --
819 arp_distributions_pkg.insert_p( l_dist_rec, l_dist_rec.line_id );
820
821 /* need to insert records into the MRC table. Calling new
822 mrc engine */
823
824 ar_mrc_engine2.maintain_mrc_data2(
825 p_event_mode => 'INSERT',
826 p_table_name => 'AR_DISTRIBUTIONS',
827 p_mode => 'SINGLE',
828 p_key_value => l_dist_rec.line_id,
829 p_row_info => l_dist_rec);
830
831 END LOOP;
832 --
833
834 --Bug#2750340
835 l_xla_ev_rec.xla_from_doc_id := p_cr_id;
836 l_xla_ev_rec.xla_to_doc_id := p_cr_id;
837 l_xla_ev_rec.xla_doc_table := 'CRH';
838 l_xla_ev_rec.xla_mode := 'O';
839 l_xla_ev_rec.xla_call := 'B';
840 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
841
842
843 IF PG_DEBUG in ('Y', 'C') THEN
844 arp_standard.debug( 'arp_reverse_receipt.insert_reversal_dist_rec() -');
845 END IF;
846 EXCEPTION
847 --
848 WHEN OTHERS THEN
849 IF PG_DEBUG in ('Y', 'C') THEN
850 arp_standard.debug('insert_reversal_dist_rec: ' ||
851 'EXCEPTION: arp_reverse_receipt.insert_reversal_dist_rec' );
852 END IF;
853 RAISE;
854 --
855 END insert_reversal_dist_rec;
856 --
857 /*===========================================================================+
858 | PROCEDURE |
859 | insert_reversal_crh_record |
860 | |
861 | DESCRIPTION |
862 | Prepare for insertion of Insert the reversal cash receipt history recor|
863 | |
864 | SCOPE - PRIVATE |
865 | |
866 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
867 | arp_cr_history_pkg.insert_p -Insertion table handler for |
868 | AR_CASH_RECEIPTS_HISTORY table |
869 | |
870 | ARGUMENTS : IN: |
871 | p_crh_rec - Cash receipt history record structure |
872 | p_reversal_gl_date - Reversal GL date |
873 | p_reversal_date - Reversal Date |
874 | p_clear_batch_id - Flag to denote if the batch Id |
875 | should be nulled out NOCOPY or not |
876 | this procedure |
877 | OUT: |
878 | p_crh_id - Id of inserted ar_cash_receipt_history row |
879 | |
880 | RETURNS : NONE |
881 | |
882 | NOTES |
883 | |
884 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
885 | 05/02/95 - Removed comments around |
886 | p_crh_rec.created_from variable |
887 | - Assigned 'ARP_PROCESS_RECIPTS.REVERSE'
888 | created_from variables |
889 | |
890 +===========================================================================*/
891 PROCEDURE insert_reversal_crh_record(
892 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
893 p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
894 p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
895 p_clear_batch_id IN VARCHAR2,
896 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE
897 ) IS
898 --Bug#2750340
899 l_xla_ev_rec arp_xla_events.xla_events_type;
900
901 BEGIN
902 IF PG_DEBUG in ('Y', 'C') THEN
903 arp_standard.debug( 'arp_reverse_receipt.insert_reversal_crh_record() +');
904 END IF;
905 --
906 -- The amount columns are not updated to 0. This was done in Rel 10.5
907 -- For more info on this look at /appldev/ar/7.1/upgrade/sql/ar760u15.sql
908 -- file
909 --
910 /***
911 -- p_crh_rec.amount := 0;
912 -- p_crh_rec.acctd_amount := 0;
913 -- p_crh_rec.factor_discount_amount := 0;
914 -- p_crh_rec.acctd_factor_discount_amount := 0;
915 ***/
916 --
917
918 -- This is a new design for 10.6, the factor_flag value should
919 -- stay the same as the prior history record.
920 /***
921 -- p_crh_rec.factor_flag := 'N';
922 ***/
923
924 p_crh_rec.first_posted_record_flag := 'N';
925 p_crh_rec.current_record_flag := 'Y';
926 p_crh_rec.gl_date := p_reversal_gl_date;
927
928 IF ( p_crh_rec.status = 'APPROVED' )
929 THEN
930 p_crh_rec.postable_flag := 'N';
931 ELSE
932 p_crh_rec.postable_flag := 'Y';
933 END IF;
934
935 p_crh_rec.trx_date := p_reversal_date;
936 --
937 IF ( p_clear_batch_id IS NOT NULL ) THEN
938 p_crh_rec.batch_id := p_clear_batch_id;
939 ELSE
940 IF ( p_crh_rec.status <> 'CLEARED' ) THEN
941 p_crh_rec.batch_id := NULL;
942 END IF;
943 END IF;
944 --
945 p_crh_rec.status := 'REVERSED';
946 p_crh_rec.gl_posted_date := NULL;
947 p_crh_rec.posting_control_id := -3;
948 --
949 p_crh_rec.created_from := 'ARP_REVERSE_RECEIPT.REVERSE';
950 --
951 p_crh_rec.prv_stat_cash_receipt_hist_id :=
952 p_crh_rec.cash_receipt_history_id;
953 --
954 -- For each row selected Insert a new cash receipt history record with
955 -- status 'REVERSED'.
956 --
957 arp_cr_history_pkg.insert_p( p_crh_rec, p_crh_rec.cash_receipt_history_id );
958 p_crh_id := p_crh_rec.cash_receipt_history_id;
959 --
960 --Bug#2750340
961 l_xla_ev_rec.xla_from_doc_id := p_crh_rec.cash_receipt_id;
962 l_xla_ev_rec.xla_to_doc_id := p_crh_rec.cash_receipt_id;
963 l_xla_ev_rec.xla_doc_table := 'CRH';
964 l_xla_ev_rec.xla_mode := 'O';
965 l_xla_ev_rec.xla_call := 'B';
966 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
967
968 IF PG_DEBUG in ('Y', 'C') THEN
969 arp_standard.debug( 'arp_reverse_receipt.insert_reversal_crh_record() -');
970 END IF;
971 EXCEPTION
972 WHEN OTHERS THEN
973 IF PG_DEBUG in ('Y', 'C') THEN
974 arp_standard.debug('insert_reversal_crh_record: ' ||
975 'EXCEPTION: arp_reverse_receipt.insert_reversal_crh_record' );
976 END IF;
977 RAISE;
978 END insert_reversal_crh_record;
979 --
980 /*===========================================================================+
981 | PROCEDURE |
982 | update_current_crh_record |
983 | |
984 | DESCRIPTION |
985 | Update the current Cash receipt history record |
986 | |
987 | SCOPE - PRIVATE |
988 | |
989 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
990 | arp_cr_history_pkg.update_p - cash receipt history update table |
991 | handler |
992 | |
993 | ARGUMENTS : IN: |
994 | p_crh_rec - cash receipt history record structure |
995 | p_reversal_gl_date - Reversal GL date |
996 | p_reversal_date - Reversal Date |
997 | p_crh_id_new - Id of newly inserted cash receipt |
998 | history row |
999 | OUT: |
1000 | |
1001 | RETURNS : NONE |
1002 | |
1003 | NOTES |
1004 | |
1005 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1006 | |
1007 +===========================================================================*/
1008 PROCEDURE update_current_crh_record(
1009 p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
1010 p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
1011 p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
1012 p_crh_id_new IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
1013
1014 --Bug#2750340
1015 l_xla_ev_rec arp_xla_events.xla_events_type;
1016
1017 BEGIN
1018 IF PG_DEBUG in ('Y', 'C') THEN
1019 arp_standard.debug( 'arp_reverse_receipt.update_current_crh_record() +');
1020 arp_standard.debug('update_current_crh_record: ' || 'crh_id_new = '||to_char( p_crh_id_new ) );
1021 END IF;
1022
1023 p_crh_rec.current_record_flag := NULL;
1024 p_crh_rec.reversal_gl_date := p_reversal_gl_date;
1025 p_crh_rec.reversal_posting_control_id := -3;
1026 p_crh_rec.reversal_cash_receipt_hist_id :=
1027 p_crh_id_new;
1028 p_crh_rec.reversal_created_from := 'ARP_REVERSE_RECEIPT.REVERSE';
1029 --
1030 -- For each row selected Insert a new cash receipt history record with
1031 -- status 'REVERSED'.
1032 --
1033 arp_cr_history_pkg.update_p( p_crh_rec );
1034 --
1035 --Bug#2750340
1036 l_xla_ev_rec.xla_from_doc_id := p_crh_rec.cash_receipt_id;
1037 l_xla_ev_rec.xla_to_doc_id := p_crh_rec.cash_receipt_id;
1038 l_xla_ev_rec.xla_doc_table := 'CRH';
1039 l_xla_ev_rec.xla_mode := 'O';
1040 l_xla_ev_rec.xla_call := 'B';
1041 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1042
1043 IF PG_DEBUG in ('Y', 'C') THEN
1044 arp_standard.debug( 'arp_reverse_receipt.update_current_crh_record() -');
1045 END IF;
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 IF PG_DEBUG in ('Y', 'C') THEN
1049 arp_standard.debug('update_current_crh_record: ' ||
1050 'EXCEPTION: arp_reverse_receipt.update_current_crh_record' );
1051 END IF;
1052 RAISE;
1053 --
1054 END update_current_crh_record;
1055 --
1056 /*===========================================================================+
1057 | PROCEDURE |
1058 | insert_reversal_mcd_record |
1059 | |
1060 | DESCRIPTION |
1061 | Prepare for insertion of Insert the reversal misc cash distribution rec|
1062 | |
1063 | SCOPE - PRIVATE |
1064 | |
1065 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1066 | arp_misc_cash_dist_pkg.insert_p - Insertion table handler for |
1067 | AR_MISC_CASH_DISTRIBUTIONS table|
1068 | |
1069 | ARGUMENTS : IN: |
1070 | p_mcd_rec - Misc cash distributions receord structure |
1071 | p_reversal_gl_date - Reversal GL date |
1072 | p_reversal_date - Reversal Date |
1073 | OUT: |
1074 | |
1075 | RETURNS : NONE |
1076 | |
1077 | NOTES |
1078 | |
1079 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1080 | 05/02/95 - Removed comments around |
1081 | p_mcd_rec.created_from variable |
1082 | - Assigned 'ARP_PROCESS_RECIPTS.REVERSE'
1083 | created_from variables |
1084 | |
1085 +===========================================================================*/
1086 PROCEDURE insert_reversal_mcd_record(
1087 p_mcd_rec IN OUT NOCOPY ar_misc_cash_distributions%ROWTYPE,
1088 p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
1089 p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE ) IS
1090 l_mcd_id ar_misc_cash_distributions.misc_cash_distribution_id%TYPE;
1091 l_ae_doc_rec ae_doc_rec_type;
1092
1093 BEGIN
1094 IF PG_DEBUG in ('Y', 'C') THEN
1095 arp_standard.debug( 'arp_reverse_receipt.insert_reversal_mcd_record() +');
1096 END IF;
1097 --
1098 p_mcd_rec.gl_date := p_reversal_gl_date;
1099 p_mcd_rec.apply_date := p_reversal_date;
1100 p_mcd_rec.amount := -p_mcd_rec.amount;
1101 p_mcd_rec.acctd_amount := -p_mcd_rec.acctd_amount;
1102 p_mcd_rec.posting_control_id := -3;
1103 p_mcd_rec.created_from := 'ARP_REVERSE_RECEIPT.REVERSE';
1104 P_mcd_rec.gl_posted_date := NULL;
1105 --
1106 -- For insertion use cash_receipt_id and reversal_gl_date=NULL
1107 --
1108 arp_misc_cash_dist_pkg.insert_p( p_mcd_rec, l_mcd_id );
1109
1110 --
1111 --Release 11.5 VAT changes, reverse accounting associated with old MCD
1112 --and create new distributions with new MCD id
1113 --
1114 l_ae_doc_rec.document_type := 'RECEIPT';
1115 l_ae_doc_rec.document_id := p_mcd_rec.cash_receipt_id;
1116 l_ae_doc_rec.accounting_entity_level := 'ONE';
1117 l_ae_doc_rec.source_table := 'MCD';
1118 l_ae_doc_rec.source_id := l_mcd_id;
1119 l_ae_doc_rec.source_id_old := p_mcd_rec.misc_cash_distribution_id;
1120 l_ae_doc_rec.other_flag := 'REVERSE';
1121 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1122
1123 IF PG_DEBUG in ('Y', 'C') THEN
1124 arp_standard.debug( 'arp_reverse_receipt.insert_reversal_mcd_record() -');
1125 END IF;
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 IF PG_DEBUG in ('Y', 'C') THEN
1129 arp_standard.debug('insert_reversal_mcd_record: ' ||
1130 'EXCEPTION: arp_reverse_receipt.insert_reversal_mcd_record' );
1131 END IF;
1132 RAISE;
1133 --
1134 END insert_reversal_mcd_record;
1135 --
1136 /*===========================================================================+
1137 | FUNCTION |
1138 | check_cb |
1139 | |
1140 | DESCRIPTION |
1141 | Check if there is any activity associated with a charge back of a |
1142 | cash receipt |
1143 | |
1144 | SCOPE - PRIVATE |
1145 | |
1146 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1147 | |
1148 | ARGUMENTS : IN: |
1149 | p_cr_id - Cash receipt id |
1150 | OUT: |
1151 | |
1152 | RETURNS : TRUE / FALSE |
1153 | |
1154 | NOTES - This could be converted to a PUBLIC function later |
1155 | |
1156 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1157 | 05/03/95- Fixed a bug in check_cb function |
1158 | The last line in the SQL statement |
1159 | should have been adj.receivables_trx_id |
1160 | <> arp_global.... instead of = arp_gl...|
1161 | |
1162 +===========================================================================*/
1163 FUNCTION check_cb( p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE )
1164 RETURN BOOLEAN IS
1165 l_count NUMBER DEFAULT 0;
1166 BEGIN
1167 IF PG_DEBUG in ('Y', 'C') THEN
1168 arp_standard.debug( 'arp_reverse_receipt.check_cb() +');
1169 arp_standard.debug('check_cb: ' || 'cr_id = '||to_char( p_cr_id ) );
1170 END IF;
1171 --
1172 SELECT COUNT(*)
1173 INTO l_count
1174 FROM ar_payment_schedules ps,
1175 ra_cust_trx_line_gl_dist ctlg
1176 WHERE ps.associated_cash_receipt_id = p_cr_id
1177 AND ps.class = 'CB'
1178 AND ps.customer_trx_id = ctlg.customer_trx_id
1179 AND ( NVL( ps.amount_applied, 0 ) <> 0
1180 OR NVL( ps.amount_credited, 0 ) <> 0
1181 OR 0 <> ( SELECT sum( adj.amount )
1182 FROM ar_adjustments adj
1183 WHERE adj.payment_schedule_id = ps.payment_schedule_id
1184 AND adj.receivables_trx_id <>
1185 arp_global.G_CB_REV_RT_ID
1186 )
1187 );
1188 IF ( l_count > 0 ) THEN
1189 RETURN FALSE;
1190 ELSE
1191 RETURN TRUE;
1192 END IF;
1193 --
1194 IF PG_DEBUG in ('Y', 'C') THEN
1195 arp_standard.debug( 'arp_reverse_receipt.check_cb() -');
1196 END IF;
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 IF PG_DEBUG in ('Y', 'C') THEN
1200 arp_standard.debug( 'EXCEPTION: arp_reverse_receipt.check_cb' );
1201 END IF;
1202 RAISE;
1203 END check_cb;
1204 --
1205 /*===========================================================================+
1206 | PROCEDURE |
1207 | modify_update_ps_rec |
1208 | |
1209 | DESCRIPTION |
1210 | Prepare for updation into payment schedule record |
1211 | |
1212 | SCOPE - PRIVATE |
1213 | |
1214 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1215 | arp_ps_util.get_closed_dates - Get closed dates |
1216 | arp_ps_pkg.fetch_f_cr_id - Fetch from payment |
1217 | Schedule table handler useing cash receipt id |
1218 | arp_ps_pkg.update_p - update payment schedule row |
1219 | |
1220 | ARGUMENTS : IN: |
1221 | p_cr_id - Cash receipt id |
1222 | p_reversal_gl_date - Reversal GL date |
1223 | p_reversal_date - Reversal Date |
1224 | this procedure |
1225 | OUT: |
1226 | |
1227 | RETURNS : NONE |
1228 | |
1229 | NOTES |
1230 | |
1231 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1232 | |
1233 +===========================================================================*/
1234 PROCEDURE modify_update_ps_rec(
1235 p_cr_id IN ar_payment_schedules.cash_receipt_id%TYPE,
1236 p_reversal_gl_date IN DATE,
1237 p_reversal_date IN DATE ) IS
1238 l_gl_date_closed DATE;
1239 l_actual_date_closed DATE;
1240 l_ps_rec ar_payment_schedules%ROWTYPE;
1241 BEGIN
1242 IF PG_DEBUG in ('Y', 'C') THEN
1243 arp_standard.debug( 'arp_reverse_receipt.modify_update_ps_rec() +' );
1244 arp_standard.debug('modify_update_ps_rec: ' || to_char( p_cr_id ) );
1245 arp_standard.debug('modify_update_ps_rec: ' || 'cr_id = '||to_char( p_cr_id ) );
1246 END IF;
1247 --
1248 arp_ps_pkg.fetch_fk_cr_id( p_cr_id, l_ps_rec );
1249 arp_ps_util.get_closed_dates( l_ps_rec.payment_schedule_id,
1250 p_reversal_gl_date,
1251 p_reversal_date,
1252 l_gl_date_closed,
1253 l_actual_date_closed, 'PMT' );
1254 --
1255 l_ps_rec.amount_due_remaining := 0;
1256 l_ps_rec.acctd_amount_due_remaining := 0;
1257 l_ps_rec.amount_applied := 0;
1258 l_ps_rec.actual_date_closed := l_actual_date_closed;
1259 l_ps_rec.gl_date_closed := l_gl_date_closed;
1260 l_ps_rec.status := 'CL';
1261 --
1262 arp_ps_pkg.update_p( l_ps_rec );
1263 --
1264 IF PG_DEBUG in ('Y', 'C') THEN
1265 arp_standard.debug( 'arp_reverse_receipt.modify_update_ps_rec() +' );
1266 END IF;
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 IF PG_DEBUG in ('Y', 'C') THEN
1270 arp_standard.debug('modify_update_ps_rec: ' ||
1271 'EXCEPTION: arp_reverse_receipt.modify_update_ps_rec' );
1272 END IF;
1273 RAISE;
1274 --
1275 END modify_update_ps_rec;
1276 --
1277 /*===========================================================================+
1278 | PROCEDURE |
1279 | update_current_cr_rec |
1280 | |
1281 | DESCRIPTION |
1282 | Prepare for updation of current cash receipts row |
1283 | |
1284 | SCOPE - PRIVATE |
1285 | |
1286 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1287 | arp_cash_receipts_pkg.update_p - Update payment schedule handler |
1288 | |
1289 | ARGUMENTS : IN: |
1290 | p_crh_rec - Cash receipt history record structure |
1291 | p_reversal_gl_date - Reversal GL date |
1292 | p_reversal_date - Reversal Date |
1293 | p_clear_batch_id - Flag to denote if the batch Id |
1294 | should be nulled out NOCOPY or not |
1295 | this procedure |
1296 | OUT: |
1297 | |
1298 | RETURNS : NONE |
1299 | |
1300 | NOTES |
1301 | |
1302 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1303 | 05/10/95- Nulled out NOCOPY selected_remittance_batch_id |
1304 | column |
1305 | |
1306 +===========================================================================*/
1307 PROCEDURE update_current_cr_rec(
1308 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE,
1309 p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
1310 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
1311 p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
1312 p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
1313 p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
1314 p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
1315 p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
1316 p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
1317 p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
1318 p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
1319 p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
1320 p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
1321 p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
1322 p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
1323 p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
1324 p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
1325 p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
1326 p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
1327 p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
1328 p_attribute15 IN ar_cash_receipts.attribute15%TYPE ) IS
1329 BEGIN
1330 IF PG_DEBUG in ('Y', 'C') THEN
1331 arp_standard.debug( 'arp_reverse_receipt.update_current_cr_rec() +' );
1332 END IF;
1333 --
1334 p_cr_rec.selected_remittance_batch_id := NULL;
1335 p_cr_rec.reversal_category := p_reversal_category;
1336 p_cr_rec.status := p_reversal_category;
1337 p_cr_rec.reversal_date := p_reversal_date;
1338 p_cr_rec.reversal_comments := p_reversal_comments;
1339 p_cr_rec.reversal_date := p_reversal_date;
1340 p_cr_rec.reversal_reason_code := p_reversal_reason_code;
1341 p_cr_rec.attribute_category := p_attribute_category;
1342 p_cr_rec.attribute1 := p_attribute1;
1343 p_cr_rec.attribute2 := p_attribute2;
1344 p_cr_rec.attribute3 := p_attribute3;
1345 p_cr_rec.attribute4 := p_attribute4;
1346 p_cr_rec.attribute5 := p_attribute5;
1347 p_cr_rec.attribute6 := p_attribute6;
1348 p_cr_rec.attribute7 := p_attribute7;
1349 p_cr_rec.attribute8 := p_attribute8;
1350 p_cr_rec.attribute9 := p_attribute9;
1351 p_cr_rec.attribute10 := p_attribute10;
1352 p_cr_rec.attribute11 := p_attribute11;
1353 p_cr_rec.attribute12 := p_attribute12;
1354 p_cr_rec.attribute13 := p_attribute13;
1355 p_cr_rec.attribute14 := p_attribute14;
1356 p_cr_rec.attribute15 := p_attribute15;
1357 ---
1358 arp_cash_receipts_pkg.update_p( p_cr_rec );
1359 ---
1360 IF PG_DEBUG in ('Y', 'C') THEN
1361 arp_standard.debug( 'arp_reverse_receipt.update_current_cr_rec() -');
1362 END IF;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 IF PG_DEBUG in ('Y', 'C') THEN
1366 arp_standard.debug('update_current_cr_rec: ' ||
1367 'EXCEPTION: arp_reverse_receipt.update_current_cr_rec' );
1368 END IF;
1369 RAISE;
1370 --
1371 END update_current_cr_rec;
1372 --
1373 /*===========================================================================+
1374 | PROCEDURE |
1375 | modify_update_bat_rec |
1376 | |
1377 | DESCRIPTION |
1378 | Prepare for updation into AR_BATCHES table |
1379 | |
1380 | SCOPE - PRIVATE |
1381 | |
1382 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1383 | arp_cr_batches_pkg.fetch_p - Fetch a row from AR_BATCHES row |
1384 | arp_cr_batches_pkg.update_p - Update a row in AR_BATCHES row |
1385 | |
1386 | ARGUMENTS : IN: |
1387 | p_bat_id - AR_BATCHES batch id |
1388 | p_cr_amount - Cash receipt amount |
1389 | p_status - Reversal category of receipt |
1390 | OUT: |
1391 | |
1392 | RETURNS : NONE |
1393 | |
1394 | NOTES |
1395 | |
1396 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1397 | |
1398 | 30-SEP-96 Shiv Ragunat Bug 398344, Commented Out NOCOPY Updation of |
1399 | Control_Count And Control_Amount in |
1400 | ar_batches . |
1401 | By definition - These 2 columns will no |
1402 | longer will be updated. |
1403 +===========================================================================*/
1404 PROCEDURE modify_update_bat_rec( p_bat_id IN ar_batches.batch_id%TYPE,
1405 p_cr_amount IN ar_cash_receipts.amount%TYPE,
1406 p_status IN VARCHAR2 ) IS
1407 l_bat_rec ar_batches%ROWTYPE;
1408 l_status ar_batches.status%TYPE;
1409 BEGIN
1410 --
1411 IF PG_DEBUG in ('Y', 'C') THEN
1412 arp_standard.debug( 'arp_reverse_receipt.modify_update_bat_rec() +');
1413 arp_standard.debug('modify_update_bat_rec: ' || 'cr_count = '||to_char( p_cr_amount ) );
1414 arp_standard.debug('modify_update_bat_rec: ' || 'cr_amount = '||to_char( l_bat_rec.control_amount ) );
1415 END IF;
1416 --
1417 -- If there are no batches associated with the receipt, then return
1418 --
1419 BEGIN
1420 SELECT *
1421 INTO l_bat_rec
1422 FROM ar_batches
1423 WHERE batch_id = p_bat_id;
1424 --
1425 EXCEPTION
1426 WHEN NO_DATA_FOUND THEN
1427 IF PG_DEBUG in ('Y', 'C') THEN
1428 arp_standard.debug('modify_update_bat_rec: ' || 'No Batches associated with the receipt' );
1429 END IF;
1430 RETURN;
1431 WHEN OTHERS THEN
1432 IF PG_DEBUG in ('Y', 'C') THEN
1433 arp_standard.debug('modify_update_bat_rec: ' ||
1434 'EXCEPTION: arp_reverse_receipt.modify_update_bat_rec' );
1435 END IF;
1436 END;
1437 --
1438 -- determine if the batch has any unposted quick cash receipt
1439 -- in the AR_INTERIM_CASH_RECEIPTS table
1440 --
1441 IF PG_DEBUG in ('Y', 'C') THEN
1442 arp_standard.debug('modify_update_bat_rec: ' || 'p_status = '||p_status );
1443 END IF;
1444 --
1445 --
1446 --
1447 -- Shiv Ragunat - 9/30/96
1448 -- Commenting it out NOCOPY as part of fix for Bug 398344
1449 -- By Definition - Control Count and Control AMount
1450 -- Will no longer be updated.
1451 --
1452 --
1453 --
1454 /* IF ( p_status <> 'REV' AND p_status IS NOT NULL) THEN
1455 l_bat_rec.control_count := l_bat_rec.control_count - 1;
1456 l_bat_rec.control_amount := l_bat_rec.control_amount - p_cr_amount;
1457 END IF;
1458 --
1459 IF ( l_bat_rec.control_count = 0 ) THEN
1460 IF ( l_bat_rec.control_amount = 0 ) THEN
1461 IF ( p_status = 'APP' OR p_status = 'NSF' OR
1462 p_status = 'STOP' ) THEN
1463 l_bat_rec.status := 'CL';
1464 ELSE
1465 l_bat_rec.status := 'OP';
1466 END IF;
1467 ELSE
1468 l_bat_rec.status := 'OOB';
1469 END IF;
1470 ELSE
1471 l_bat_rec.status := 'OOB';
1472 END IF; */
1473
1474
1475
1476 --
1477 arp_cr_batches_pkg.update_p( l_bat_rec );
1478 --
1479 IF PG_DEBUG in ('Y', 'C') THEN
1480 arp_standard.debug( 'arp_reverse_receipt.modify_update_bat_rec() -');
1481 END IF;
1482 EXCEPTION
1483 WHEN OTHERS THEN
1484 IF PG_DEBUG in ('Y', 'C') THEN
1485 arp_standard.debug('modify_update_bat_rec: ' ||
1486 'EXCEPTION: arp_reverse_receipt.modify_update_bat_rec' );
1487 END IF;
1488 RAISE;
1489
1490 END modify_update_bat_rec;
1491 --
1492 /*===========================================================================+
1493 | PROCEDURE |
1494 | debit_memo_reversal |
1495 | |
1496 | DESCRIPTION |
1497 | Create a debit memo( basically an invoice for the receipt applied |
1498 | amount. This happens when a check bounces and the customer needs to |
1499 | charged for the check amount. This involves creation of a new invoice |
1500 | (debit memo) for the amount of the check( receipt ). |
1501 | |
1502 | SCOPE - PUBLIC |
1503 | |
1504 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1505 | arp_app_pkg.lock_p - lock a record in |
1506 | AR_RECEIVABLE_APPLICATIONS table |
1507 | |
1508 | ARGUMENTS : IN: |
1509 | p_cr_id - Cash receipt ID |
1510 | p_reversal_gl_date - Reversal GL date |
1511 | p_reversal_date - Reversal Date |
1512 | p_reversal_reason_code - Reason for reversal |
1513 | p_reversal_comments - Reversal comments |
1514 | p_cust_trx_type_id - Transaction type Id |
1515 | p_module_name - Name of module that called this proc. |
1516 | p_module_version - Version of the module that called |
1517 | this procedure |
1518 | OUT: |
1519 | |
1520 | RETURNS : NONE |
1521 | |
1522 | NOTES |
1523 | |
1524 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
1525 | |
1526 | 05-FEB-1996 OSTEINME Added parameters to allow function to return |
1527 | trx_number and document number data |
1528 | modified function to determine trx_number |
1529 | and document number |
1530 | 7/31/1996 Harri Kaukovuo Fixed procedure to use document numbers |
1531 | only if profile option says so. |
1532 | Changed the document number datatypes to be |
1533 | NUMBER instead of BINARY_INTEGER because of |
1534 | future expansion of document number range. |
1535 | 12-SEP-1996 OSTEINME modified parameters to debit_memo_reversal |
1536 | to allow for document number info to be |
1537 | passed it. |
1538 | 19-AUG-1997 OSTEINME added two new parameters (NULL) to call to |
1539 | procedure insert_line for Rel. 11. |
1540 | 21-NOV-1997 Karen Murphy Bug 522837. |
1541 | Removed TO_NUMBER( p_module_version ) |
1542 | in calls to the transactions workbench |
1543 | as this causes problems in environments |
1544 | using different number formatting, e.g. Spain|
1545 | and Germany. Passing NULL instead as this |
1546 | functionality has not been implemented. |
1547 | 09-FEB-1999 Debbie Jancis Modified for 11.5 BOE changes to pass the |
1548 | trx_number if the dm_inherit_receipt_num_flag|
1549 | is set |
1550 | 17-FEB-1999 Ramakant Alat Updating the TRX_NUMBER with Document Number |
1551 | if the COPY option is set at Bacth Source |
1552 | 08-SEP-1999 J Rautiainen BugFix for bug 976703. Cursor customer_C was |
1553 | split into two separate cursors for |
1554 | performance reasons |
1555 | 29-MAR-2000 V Crisostomo Bug 753554 : Modify method of selecting crh |
1556 | to get ccids from |
1557 | 12-APR-2000 Skoukunt Bug 1063133 : Added 2nd i/p parameter for the|
1558 | cursor bill_to_customer_C |
1559 +===========================================================================*/
1560
1561 PROCEDURE debit_memo_reversal(
1562 p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE,
1563 p_cc_id IN ra_cust_trx_line_gl_dist.code_combination_id%TYPE,
1564 p_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
1565 p_cust_trx_type IN ra_cust_trx_types.name%TYPE,
1566 p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
1567 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
1568 p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
1569 p_reversal_reason_code IN
1570 ar_cash_receipts.reversal_reason_code%TYPE,
1571 p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
1572 p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
1573 p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
1574 p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
1575 p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
1576 p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
1577 p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
1578 p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
1579 p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
1580 p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
1581 p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
1582 p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
1583 p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
1584 p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
1585 p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
1586 p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
1587 p_attribute15 IN ar_cash_receipts.attribute15%TYPE,
1588 p_dm_number OUT NOCOPY ar_payment_schedules.trx_number%TYPE,
1589 p_dm_doc_sequence_value IN ra_customer_trx.doc_sequence_value%TYPE,
1590 p_dm_doc_sequence_id IN ra_customer_trx.doc_sequence_id%TYPE,
1591 p_status IN OUT NOCOPY VARCHAR2,
1592 p_module_name IN VARCHAR2,
1593 p_module_version IN VARCHAR2 ) IS
1594
1595 l_receipt_method_name ar_receipt_methods.name%TYPE;
1596 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
1597 l_customer_id ar_cash_receipts.pay_from_customer%TYPE;
1598
1599 l_currency_code ar_cash_receipts.currency_code%TYPE;
1600 l_exchange_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
1601 l_exchange_rate ar_cash_receipts.exchange_rate%TYPE;
1602 l_exchange_date ar_cash_receipts.exchange_date%TYPE;
1603
1604 l_amount ar_cash_receipts.amount%TYPE;
1605
1606 l_description ra_customer_trx_lines.description%TYPE;
1607
1608 l_rev1_cc_id ar_cash_receipt_history.account_code_combination_id%TYPE;
1609 l_rev1_amount ar_cash_receipt_history.amount%TYPE;
1610 l_rev2_cc_id ar_cash_receipt_history.bank_charge_account_ccid%TYPE;
1611 l_rev2_amount ar_cash_receipt_history.factor_discount_amount%TYPE;
1612
1613 l_ct_rec ra_customer_trx%ROWTYPE;
1614 l_ct_lines_rec ra_customer_trx_lines%ROWTYPE;
1615 l_comm_rec arp_process_commitment.commitment_rec_type;
1616
1617 l_trx_number ra_customer_trx.trx_number%TYPE;
1618 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
1619 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE;
1620
1621 l_count NUMBER := 0;
1622
1623 l_rule_start_date ra_customer_trx_lines.rule_start_date%type;
1624 l_accounting_rule_duration ra_customer_trx_lines.accounting_rule_duration%type;
1625 l_gl_date_dummy ra_cust_trx_line_gl_dist.gl_date%type;
1626 l_trx_date_dummy ra_customer_trx.trx_date%type;
1627 l_status_dummy varchar2(100);
1628
1629 l_commit_cust_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
1630 l_rowid rowid;
1631
1632 l_dm_number ar_payment_schedules.trx_number%TYPE;
1633 --l_sequence_name VARCHAR2(500);
1634 --l_sequence_id NUMBER;
1635 --l_sequence_assignment_id NUMBER;
1636 --l_sequence_value NUMBER;
1637 l_cr_id NUMBER;
1638 l_crhid NUMBER;
1639
1640 l_term_end_date DATE; /*5084781*/
1641
1642 /* for 11.5 BOE Changes */
1643 /*l_rcpt_inherit_inv_num_flag VARCHAR2(1); Bug 3246178*/
1644 l_dm_inherit_rcpt_num_flag VARCHAR2(1);
1645 l_does_it_exist VARCHAR2(1);
1646
1647 /* Document Sequencing Project Changes */
1648 l_copy_doc_number_flag RA_BATCH_SOURCES.copy_doc_number_flag%TYPE;
1649
1650 /*Legal Entity for DM reversal 5126184*/
1651 l_legal_entity_id ra_customer_trx.legal_entity_id%TYPE;
1652
1653 /* 08-SEP-1999 J Rautiainen BugFix for bug 976703
1654 * Cursor customer_C was split into two separate cursors for performance
1655 * reasons */
1656 -- Added 2nd i/p parameter to fix bug 1063133
1657 CURSOR bill_to_customer_C( l_customer_id hz_cust_accounts.cust_account_id%TYPE,
1658 l_site_use_id ar_cash_receipts.customer_site_use_id%TYPE ) IS
1659 SELECT su1.site_use_id site_use_id,
1660 su1.cust_acct_site_id address_id
1661 FROM hz_cust_site_uses su1,
1662 hz_cust_acct_sites add1
1663 WHERE add1.cust_account_id = l_customer_id
1664 AND add1.cust_acct_site_id = su1.cust_acct_site_id
1665 /* 02-JUN-2000 J Rautiainen BR Implementation
1666 * The site can also be DRAWEE */
1667 AND su1.site_use_code in ('BILL_TO','DRAWEE')
1668 AND su1.site_use_id = nvl(l_site_use_id,su1.site_use_id)
1669 ORDER BY su1.primary_flag desc;
1670
1671 CURSOR ship_to_customer_C( l_customer_id hz_cust_accounts.cust_account_id%TYPE ) IS
1672 SELECT DECODE( su2.site_use_id,
1673 NULL, NULL, add2.cust_account_id ) ship_to_customer_id,
1674 su2.site_use_id ship_to_site_use_id
1675 FROM hz_cust_site_uses su2,
1676 hz_cust_acct_sites add2
1677 WHERE add2.cust_account_id = l_customer_id
1678 AND add2.cust_acct_site_id = su2.cust_acct_site_id
1679 AND su2.site_use_code = 'SHIP_TO'
1680 ORDER BY su2.primary_flag desc;
1681
1682 BEGIN
1683 --
1684 -- arp_standard.enable_debug;
1685 IF PG_DEBUG in ('Y', 'C') THEN
1686 arp_standard.debug( 'arp_reverse_receipt.debit_memo_reversal() +');
1687 arp_standard.debug('debit_memo_reversal: ' || 'Parameters:');
1688 arp_standard.debug('debit_memo_reversal: ' || 'p_cr_id = ' || p_cr_rec.cash_receipt_id);
1689 arp_standard.debug('debit_memo_reversal: ' || 'p_cc_id = ' || p_cc_id);
1690 arp_standard.debug('debit_memo_reversal: ' || 'p_cust_trx_type_id = ' || p_cust_trx_type_id);
1691 arp_standard.debug('debit_memo_reversal: ' || 'p_cust_trx_type = ' || p_cust_trx_type);
1692 arp_standard.debug('debit_memo_reversal: ' || 'p_reversal_gl_date = ' || TO_CHAR(p_reversal_gl_date));
1693 arp_standard.debug('debit_memo_reversal: ' || 'p_reversal_date = ' || TO_CHAR(p_reversal_date));
1694 arp_standard.debug('debit_memo_reversal: ' || 'p_reversal_reason_code = ' || p_reversal_reason_code);
1695 arp_standard.debug('debit_memo_reversal: ' || 'p_module_name = ' || p_module_name);
1696 arp_standard.debug('debit_memo_reversal: ' || 'p_module_version = ' || p_module_version);
1697 END IF;
1698
1699 l_cr_id := p_cr_rec.cash_receipt_id;
1700
1701 IF ( p_module_name IS NOT NULL and p_module_version IS NOT NULL ) THEN
1702 validate_dm_reversal_args( l_cr_id, p_cc_id,
1703 p_cust_trx_type_id, p_reversal_gl_date,
1704 p_reversal_date, p_reversal_reason_code );
1705 END IF;
1706 --
1707 -- Validate gl date. If it is invalid, print an error message
1708 --
1709 IF ( arp_util.is_gl_date_valid( p_reversal_gl_date ) = FALSE ) THEN
1710 IF PG_DEBUG in ('Y', 'C') THEN
1711 arp_standard.debug('debit_memo_reversal: ' || 'invalid gl date' );
1712 END IF;
1713 FND_MESSAGE.set_name ('AR', 'AR_INF_GL_DATE' );
1714 APP_EXCEPTION.raise_exception;
1715 END IF;
1716 --
1717 -- Get receipt method name, currency code, exchange rate info and
1718 -- receipt amount
1719 --
1720 -- BOE changes: get the dm_inherit_receipt_num_flag to deterime
1721 -- if we need to populate the trx_number.
1722 /*Bug 3246178*/
1723
1724 SELECT rm.name,
1725 cr.pay_from_customer,
1726 cr.receipt_number,
1727 currency_code,
1728 exchange_rate_type,
1729 exchange_rate,
1730 exchange_date,
1731 amount,
1732 rm.dm_inherit_receipt_num_flag,
1733 cr.legal_entity_id
1734 INTO l_receipt_method_name,
1735 l_customer_id,
1736 l_receipt_number,
1737 l_currency_code,
1738 l_exchange_rate_type,
1739 l_exchange_rate,
1740 l_exchange_date,
1741 l_amount,
1742 l_dm_inherit_rcpt_num_flag,
1743 l_legal_entity_id --5126184
1744 FROM
1745 ar_receipt_methods rm
1746 , ar_cash_receipts cr
1747 WHERE cr.cash_receipt_id = l_cr_id
1748 AND rm.receipt_method_id = cr.receipt_method_id;
1749 --
1750 -- Get description to be used during RA_CUSTOMER_TRX_LINES insertion
1751 --
1752
1753 /* Bug 4684829 Changing AR_MEMO_LINES to AR_MEMO_LINES_B and AR_MEMO_LINES_TL
1754 We can directly use ar_memo_lines_vl too but case bug for a bug in
1755 ar_memo_lines_vl is dtill pending. */
1756
1757 SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(T.description,
1758 '&'||'payment_number'||'&',
1759 l_receipt_number),
1760 '&'||'PAYMENT_NUMBER'||'&',
1761 l_receipt_number),
1762 '&'||'receipt_number'||'&',
1763 l_receipt_number),
1764 '&'||'RECEIPT_NUMBER'||'&',
1765 l_receipt_number),
1766 '&'||'payment_method'||'&',
1767 l_receipt_method_name ),
1768 '&'||'PAYMENT_METHOD'||'&',
1769 l_receipt_method_name )
1770 INTO l_description
1771 FROM ar_memo_lines_b B ,ar_memo_lines_tl T
1772 WHERE B.MEMO_LINE_ID = T.MEMO_LINE_ID
1773 AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)
1774 AND T.LANGUAGE = userenv('LANG')
1775 AND mo_global.check_access(B.ORG_ID) = 'Y'
1776 AND B.memo_line_id = 2;
1777
1778
1779
1780 --
1781 -- Get Revenue account ccid's and amounts
1782 -- Bug 753554 : instead of using current_record_flag to pick the record to
1783 -- get ccids from, use the highest crh_id whose status <> RISK_ELIMINATED
1784
1785 SELECT max(cash_receipt_history_id)
1786 INTO l_crhid
1787 FROM ar_cash_receipt_history
1788 WHERE cash_receipt_id = l_cr_id
1789 AND status <> 'RISK_ELIMINATED';
1790
1791 SELECT account_code_combination_id,
1792 bank_charge_account_ccid,
1793 amount,
1794 NVL(factor_discount_amount,0)
1795 INTO l_rev1_cc_id,
1796 l_rev2_cc_id,
1797 l_rev1_amount,
1798 l_rev2_amount
1799 FROM ar_cash_receipt_history
1800 WHERE cash_receipt_id = l_cr_id
1801 AND cash_receipt_history_id = l_crhid;
1802
1803 IF PG_DEBUG in ('Y', 'C') THEN
1804 arp_standard.debug('debit_memo_reversal: ' || 'l_rev1_cc_id = ' || l_rev1_cc_id);
1805 arp_standard.debug('debit_memo_reversal: ' || 'l_rev2_cc_id = ' || l_rev2_cc_id);
1806 arp_standard.debug('debit_memo_reversal: ' || 'l_rev1_amount = ' || l_rev1_amount);
1807 arp_standard.debug('debit_memo_reversal: ' || 'l_rev2_amount = ' || l_rev2_amount);
1808 arp_standard.debug('debit_memo_reversal: ' || 'l_cr_id = ' || l_cr_id);
1809 END IF;
1810
1811 --
1812 -- Call invoice side transaction header entity handler
1813 --
1814 l_ct_rec.cust_trx_type_id := p_cust_trx_type_id;
1815 l_ct_rec.invoice_currency_code := l_currency_code;
1816 l_ct_rec.exchange_rate_type := l_exchange_rate_type;
1817 l_ct_rec.exchange_date := l_exchange_date;
1818 l_ct_rec.exchange_rate := l_exchange_rate;
1819 l_ct_rec.created_from := p_module_name;
1820 l_ct_rec.trx_date := p_reversal_date;
1821 l_ct_rec.batch_source_id := 11;
1822 l_ct_rec.status_trx := 'OP';
1823 l_ct_rec.sold_to_customer_id := l_customer_id;
1824 l_ct_rec.bill_to_customer_id := l_customer_id;
1825 l_ct_rec.term_id := 5;
1826 l_ct_rec.complete_flag := 'Y';
1827 l_ct_rec.primary_salesrep_id := -3;
1828 l_ct_rec.reason_code := p_reversal_reason_code;
1829 l_ct_rec.legal_entity_id := l_legal_entity_id ; --5126184
1830
1831 /*5084781 Begin*/
1832 select end_date_active
1833 into l_term_end_date
1834 from ra_terms where term_id = 5;
1835
1836 IF (NVL(l_term_end_date, to_date('31-12-4712','DD-MM-YYYY')) < p_reversal_date) THEN
1837 FND_MESSAGE.SET_NAME('AR','AR_RW_PAYMENT_TERM_END_DATED');
1838 fnd_msg_pub.Add;
1839 APP_EXCEPTION.raise_exception;
1840 END IF;
1841 /*5084781 End*/
1842
1843 --
1844 -- Get sold_to_customer_id, sold_to_site_use_id, bill_to_customer_id,
1845 -- bill_to_site_use_id, ship_to_customer_id, ship_to_site_use_id
1846 --
1847 /* 08-SEP-1999 J Rautiainen BugFix for bug 976703
1848 * Cursor customer_C was split into two separate cursors for performance
1849 * reasons. Here the fetching is splitted into two steps. The shipping
1850 * site is not mandatory, so the possible error message is not dependent of it */
1851 -- Added p_cr_rec.customer_site_use_id to fix bug 1063133
1852 FOR l_bill_to_customer_rec IN bill_to_customer_C( l_customer_id,
1853 p_cr_rec.customer_site_use_id )
1854 LOOP
1855 l_count := l_count + 1;
1856 --
1857 l_ct_rec.sold_to_site_use_id := l_bill_to_customer_rec.site_use_id;
1858 l_ct_rec.bill_to_site_use_id := l_bill_to_customer_rec.site_use_id;
1859 --
1860 -- Exit after fetching 1 row
1861 --
1862 EXIT;
1863 --
1864 END LOOP;
1865 --
1866 -- If not even a single row is found, then error out NOCOPY
1867 --
1868 IF ( l_count = 0 ) THEN
1869 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
1870 FND_MESSAGE.set_token( 'GENERIC_MESSAGE', SQLERRM );
1871 APP_EXCEPTION.raise_exception;
1872 END IF;
1873
1874 l_ct_rec.ship_to_customer_id := NULL;
1875 l_ct_rec.ship_to_site_use_id := NULL;
1876 /* Fix bug 1063133
1877 FOR l_ship_to_customer_rec IN ship_to_customer_C( l_customer_id )
1878 LOOP
1879
1880 l_ct_rec.ship_to_customer_id := l_ship_to_customer_rec.ship_to_customer_id;
1881 l_ct_rec.ship_to_site_use_id := l_ship_to_customer_rec.ship_to_site_use_id;
1882 --
1883 -- Exit after fetching 1 row
1884 --
1885 EXIT;
1886 --
1887 END LOOP;
1888 */
1889
1890 -- If at least one row is found, then call invoice header EH and exit
1891 --
1892
1893 -- Before we call the invoice header EH, we need to deterime if we
1894 -- need to populate the trx_number with the receipt_number.
1895
1896 /*Bug 3246178 Removed the condition to check
1897 receipt_inherit_inv_num_flag to populate the trx_number*/
1898 IF (l_dm_inherit_rcpt_num_flag = 'Y') THEN
1899 l_ct_rec.trx_number := SUBSTR(l_receipt_number,1,20);
1900
1901 /* need to check if this trx_number exists (ie. there is
1902 DM with the same trx_number and same batch source */
1903
1904 SELECT decode ( max(dummy), NULL, 'N','Y')
1905 INTO l_does_it_exist
1906 from dual
1907 where exists (select trx_number from
1908 ra_customer_trx, ra_cust_trx_types
1909 where trx_number = l_ct_rec.trx_number
1910 and batch_source_id = l_ct_rec.batch_source_id
1911 and ra_customer_trx.cust_trx_type_id =
1912 ra_cust_trx_types.cust_trx_type_id
1913 and ra_cust_trx_types.type = 'DM');
1914
1915 /* if a DM exists with the same number and the same batch source,
1916 then we use existing functionality and call the EH without a
1917 trx_number */
1918
1919 IF (l_does_it_exist = 'Y') THEN
1920 l_ct_rec.trx_number := '';
1921 END IF;
1922 END IF;
1923
1924
1925 -- 11/21/97 Karen Murphy
1926 -- Bug 522837
1927 -- Removed TO_NUMBER( p_module_version ) as this causes problems
1928 -- in envrionments using different number formatting, e.g. Spain
1929 -- and Germany. Passing NULL instead as this functionality has
1930 -- not been implemented.
1931 arp_process_header.insert_header( p_module_name,
1932 NULL, -- p_module_version
1933 l_ct_rec, 'DM_REV', p_reversal_gl_date,
1934 NULL, l_comm_rec, l_trx_number,
1935 l_customer_trx_id,
1936 l_commit_cust_trx_line_id,
1937 l_rowid,
1938 p_status,
1939 p_cc_id );
1940 --
1941 -- Call invoice lines EH
1942 --
1943 l_ct_lines_rec.customer_trx_id := l_customer_trx_id;
1944 l_ct_lines_rec.description := l_description;
1945 l_ct_lines_rec.line_type := 'LINE';
1946 l_ct_lines_rec.line_number := 1;
1947 l_ct_lines_rec.quantity_ordered := 1;
1948 l_ct_lines_rec.quantity_invoiced := 1;
1949 l_ct_lines_rec.unit_selling_price := l_amount;
1950 l_ct_lines_rec.extended_amount := l_amount;
1951 l_ct_lines_rec.revenue_amount := l_amount;
1952 --
1953 -- trx_date, gl_date will be fetched from header record. Also need to pass
1954 -- Check which function updates PS table and the REVERSED_CASH_RECEIPT_ID
1955 -- column in PS table should get the l_cr_id value. Do not know which
1956 -- procedure arp_process_line.insert_line or
1957 -- arp_process_header.post_commit updates PS table
1958 -- in l_cr_id - dandy
1959 --
1960
1961 IF PG_DEBUG in ('Y', 'C') THEN
1962 arp_standard.debug('Debit_Memo_Reversal: Before calling arp_p_l.insert_line');
1963 END IF;
1964
1965 /* added two new NULL parameters to call to insert_line, due to spec
1966 changes in transaction package (OSTEINME, 8/19/97) */
1967
1968 -- 11/21/97 Karen Murphy
1969 -- Bug 522837
1970 -- Removed TO_NUMBER( p_module_version ) as this causes problems
1971 -- in envrionments using different number formatting, e.g. Spain
1972 -- and Germany. Passing NULL instead as this functionality has
1973 -- not been implemented.
1974
1975 arp_process_line.insert_line( p_module_name,
1976 NULL, -- p_module_version
1977 l_ct_lines_rec,
1978 NULL,
1979 l_customer_trx_line_id,
1980 'DM_REV',
1981 l_rev1_cc_id,
1982 l_rev2_cc_id,
1983 l_rev1_amount,
1984 l_rev2_amount,
1985 l_rule_start_date,
1986 l_accounting_rule_duration,
1987 l_gl_date_dummy,
1988 l_trx_date_dummy,
1989 NULL, -- added for Rel. 11
1990 NULL, -- added for Rel. 11
1991 l_status_dummy );
1992
1993
1994 IF PG_DEBUG in ('Y', 'C') THEN
1995 arp_standard.debug('debit_memo_reversal: ' || 'l_status_dummy after insert_line:' || l_status_dummy);
1996 END IF;
1997
1998 -- if first call (to insert_header) was successful, then return
1999 -- result of insert_line, otherwise return first error message.
2000
2001 IF (p_status = 'OK') THEN
2002 p_status := l_status_dummy;
2003 END IF;
2004
2005 IF PG_DEBUG in ('Y', 'C') THEN
2006 arp_standard.debug('Debit_Memo_Reversal: Before calling arp_p_h.post_commit');
2007 END IF;
2008 -- 11/21/97 Karen Murphy
2009 -- Bug 522837
2010 -- Removed TO_NUMBER( p_module_version ) as this causes problems
2011 -- in envrionments using different number formatting, e.g. Spain
2012 -- and Germany. Passing NULL instead as this functionality has
2013 -- not been implemented.
2014
2015 arp_process_header.post_commit( p_module_name,
2016 NULL, -- p_module_version
2017 l_customer_trx_id,
2018 NULL, 'Y', NULL, NULL, 'A', NULL, NULL,
2019 l_cr_id );
2020
2021 IF PG_DEBUG in ('Y', 'C') THEN
2022 arp_standard.debug('Debit_Memo_Reversal: After calling arp_p_h.post_commit');
2023 END IF;
2024
2025 BEGIN
2026 SELECT
2027 NVL(copy_doc_number_flag, 'N')
2028 INTO
2029 l_copy_doc_number_flag
2030 FROM
2031 ra_batch_sources
2032 WHERE
2033 batch_source_id = l_ct_rec.batch_source_id;
2034 EXCEPTION
2035 WHEN NO_DATA_FOUND THEN
2036 l_copy_doc_number_flag := 'N';
2037 END;
2038
2039 IF PG_DEBUG in ('Y', 'C') THEN
2040 arp_standard.debug('debit_memo_reversal: ' || 'Get the Copy Doc Number Flag :' || l_copy_doc_number_flag );
2041 END IF;
2042
2043 -- update debit memo with document number :
2044 -- Also copy the Document Number into Trx Number if "Copy" Flag set at Batch Source.
2045
2046 /* Bug3328690 To update the reversed cash_receipt id in ra_customer_trx */
2047 /* Bug3347452 To Type Cast explicit p_dm_sequence_value to character
2048 in NVL as it raises -ORA-01722 invalid number */
2049
2050 IF l_copy_doc_number_flag = 'Y' THEN
2051 UPDATE ra_customer_trx
2052 SET DOC_SEQUENCE_VALUE = p_dm_doc_sequence_value,
2053 DOC_SEQUENCE_ID = p_dm_doc_sequence_id,
2054 /* Bug3347452 */
2055 /*TRX_NUMBER = NVL(p_dm_doc_sequence_value, TRX_NUMBER),*/
2056 TRX_NUMBER = NVL(TO_CHAR(p_dm_doc_sequence_value),TRX_NUMBER),
2057 OLD_TRX_NUMBER = DECODE(p_dm_doc_sequence_value, null,
2058 OLD_TRX_NUMBER,
2059 TRX_NUMBER),
2060 REVERSED_CASH_RECEIPT_ID=l_cr_id /*3328690 */
2061 WHERE customer_trx_id = (
2062 select customer_trx_id
2063 from ar_payment_schedules
2064 where class = 'DM'
2065 and reversed_cash_receipt_id = l_cr_id
2066 );
2067 UPDATE ar_payment_schedules
2068 /* Bug3347452 */
2069 /*SET TRX_NUMBER = NVL(p_dm_doc_sequence_value, TRX_NUMBER)*/
2070 SET TRX_NUMBER = NVL(TO_CHAR(p_dm_doc_sequence_value), TRX_NUMBER)
2071 WHERE reversed_cash_receipt_id = l_cr_id;
2072
2073 ELSE
2074 UPDATE ra_customer_trx
2075 SET DOC_SEQUENCE_VALUE = p_dm_doc_sequence_value,
2076 DOC_SEQUENCE_ID = p_dm_doc_sequence_id,
2077 REVERSED_CASH_RECEIPT_ID=l_cr_id /*3328690 */
2078 WHERE customer_trx_id = (
2079 select customer_trx_id
2080 from ar_payment_schedules
2081 where class = 'DM'
2082 and reversed_cash_receipt_id = l_cr_id
2083 );
2084 END IF;
2085
2086 --
2087 -- added 05-FEB-1996 OSTEINME: determine document number and transaction
2088 -- number.
2089 --
2090
2091 SELECT trx_number
2092 INTO l_dm_number
2093 FROM ar_payment_schedules
2094 WHERE reversed_cash_receipt_id = l_cr_id;
2095 -- AND class = 'DM';
2096
2097 p_dm_number := l_dm_number;
2098
2099 IF PG_DEBUG in ('Y', 'C') THEN
2100 arp_standard.debug('debit_memo_reversal: ' || 'dm trx number: ' || l_dm_number);
2101 END IF;
2102
2103 -- update cash receipt:
2104
2105 update_current_cr_rec( p_cr_rec, p_reversal_category,
2106 p_reversal_date, p_reversal_reason_code,
2107 p_reversal_comments,
2108 p_attribute_category, p_attribute1,
2109 p_attribute2, p_attribute3, p_attribute4,
2110 p_attribute5, p_attribute6, p_attribute7,
2111 p_attribute8, p_attribute9, p_attribute10,
2112 p_attribute11, p_attribute12, p_attribute13,
2113 p_attribute14, p_attribute15 );
2114
2115
2116 IF PG_DEBUG in ('Y', 'C') THEN
2117 arp_standard.debug( 'arp_reverse_receipt.debit_memo_reversal() -');
2118 END IF;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 IF PG_DEBUG in ('Y', 'C') THEN
2123 arp_standard.debug('debit_memo_reversal: ' ||
2124 'EXCEPTION: arp_reverse_receipt.debit_memo_reversal' );
2125 END IF;
2126 RAISE;
2127 END debit_memo_reversal;
2128 --
2129 /*===========================================================================+
2130 | PROCEDURE |
2131 | validate_dm_reversal_args |
2132 | |
2133 | DESCRIPTION |
2134 | Validate arguments passed to debit_memo_reversal procedure |
2135 | |
2136 | SCOPE - PRIVATE |
2137 | |
2138 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2139 | |
2140 | ARGUMENTS : IN: |
2141 | p_cr_id - Cash receipt ID |
2142 | p_cc_id - Receipt code combination Id |
2143 | p_reversal_gl_date - Reversal GL date |
2144 | p_reversal_date - Reversal Date |
2145 | p_reversal_reason_code - Reason for reversal |
2146 | OUT: |
2147 | |
2148 | RETURNS : NONE |
2149 | |
2150 | NOTES |
2151 | |
2152 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
2153 | |
2154 +===========================================================================*/
2155 PROCEDURE validate_dm_reversal_args(
2156 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2157 p_cc_id IN ra_cust_trx_line_gl_dist.code_combination_id%TYPE,
2158 p_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
2159 p_reversal_gl_date IN
2160 ar_cash_receipt_history.reversal_gl_date%TYPE,
2161 p_reversal_date IN
2162 ar_cash_receipts.reversal_date%TYPE,
2163 p_reversal_reason_code IN VARCHAR2) IS
2164 BEGIN
2165 IF PG_DEBUG in ('Y', 'C') THEN
2166 arp_standard.debug( 'arp_reverse_receipt.validate_dm_reversal_args() +');
2167 END IF;
2168 --
2169 IF ( p_cr_id IS NULL OR p_cc_id IS NULL OR
2170 p_cust_trx_type_id IS NULL OR p_reversal_gl_date IS NULL OR
2171 p_reversal_date IS NULL OR p_reversal_reason_code IS NULL ) THEN
2172 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
2173 APP_EXCEPTION.raise_exception;
2174 END IF;
2175 --
2176 IF PG_DEBUG in ('Y', 'C') THEN
2177 arp_standard.debug( 'arp_reverse_receipt.validate_dm_reversal_args() -');
2178 END IF;
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 IF PG_DEBUG in ('Y', 'C') THEN
2182 arp_standard.debug('validate_dm_reversal_args: ' ||
2183 'EXCEPTION: arp_reverse_receipt.validate_dm_reversal_args' );
2184 END IF;
2185 RAISE;
2186 --
2187 END validate_dm_reversal_args;
2188 --
2189 /*===========================================================================+
2190 | FUNCTION |
2191 | receipt_has_non_cancel_claims |
2192 | |
2193 | DESCRIPTION |
2194 | checks if any claims on receipt cannot be cancelled |
2195 | |
2196 | SCOPE - PUBLIC |
2197 | |
2198 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2199 | |
2200 | ARGUMENTS : IN: |
2201 | p_cr_id - Cash receipt ID |
2202 | p_include_trx_claims - include invoice related claims |
2203 | OUT: |
2204 | |
2205 | RETURNS : BOOLEAN |
2206 | |
2207 | NOTES |
2208 | |
2209 | MODIFICATION HISTORY |
2210 | |
2211 | jbeckett 25-MAR-02 Created (bug 2232366) |
2212 | jbeckett 01-MAY-02 Bug 2353144 - use check_cancel_deduction function to |
2213 | determine if claim is cancellable |
2214 +===========================================================================*/
2215 FUNCTION receipt_has_non_cancel_claims(
2216 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2217 p_include_trx_claims IN VARCHAR2 DEFAULT 'Y')
2218 RETURN BOOLEAN
2219 IS
2220 CURSOR c_claim_count IS
2221 SELECT count(*)
2222 FROM ar_receivable_applications
2223 WHERE cash_receipt_id = p_cr_id
2224 AND display = 'Y'
2225 AND applied_payment_schedule_id = DECODE(p_include_trx_claims,
2226 'N',-4,applied_payment_schedule_id)
2227 AND application_ref_type = 'CLAIM';
2228
2229 CURSOR c_claims IS
2230 SELECT secondary_application_ref_id
2231 FROM ar_receivable_applications
2232 WHERE cash_receipt_id = p_cr_id
2233 AND display = 'Y'
2234 AND applied_payment_schedule_id = DECODE(p_include_trx_claims,
2235 'N',-4,applied_payment_schedule_id)
2236 AND application_ref_type = 'CLAIM';
2237 l_claim_count NUMBER := 0;
2238
2239 BEGIN
2240
2241 IF PG_DEBUG in ('Y', 'C') THEN
2242 arp_standard.debug( 'arp_reverse_receipt.receipt_has_non_cancel_claims() +');
2243 END IF;
2244
2245 OPEN c_claim_count;
2246 FETCH c_claim_count INTO l_claim_count;
2247 CLOSE c_claim_count;
2248 IF l_claim_count = 0
2249 THEN
2250 RETURN FALSE;
2251 END IF;
2252 FOR c1 in c_claims LOOP
2253 IF NOT OZF_Claim_GRP.Check_Cancell_Deduction(
2254 p_claim_id => c1.secondary_application_ref_id)
2255 THEN
2256 RETURN TRUE;
2257 END IF;
2258 END LOOP;
2259 RETURN FALSE;
2260
2261 IF PG_DEBUG in ('Y', 'C') THEN
2262 arp_standard.debug( 'arp_reverse_receipt.receipt_has_non_cancel_claims() -');
2263 END IF;
2264
2265 END receipt_has_non_cancel_claims;
2266 --
2267 /*===========================================================================+
2268 | PROCEDURE |
2269 | cancel_claims |
2270 | |
2271 | DESCRIPTION |
2272 | cancels all claims on receipt |
2273 | |
2274 | SCOPE - PUBLIC |
2275 | |
2276 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2277 | |
2278 | ARGUMENTS : IN: |
2279 | p_cr_id - Cash receipt ID |
2280 | p_include_trx_claims - include invoice related claims |
2281 | OUT: |
2282 | x_return_status |
2283 | x_msg_count |
2284 | x_msg_data |
2285 | |
2286 | RETURNS : BOOLEAN |
2287 | |
2288 | NOTES |
2289 | |
2290 | MODIFICATION HISTORY |
2291 | |
2292 | jbeckett 25-MAR-02 Created (bug 2232366) |
2293 | jbeckett 01-MAY-02 Bug 2353144 - use check_cancel_deduction function to |
2294 | determine if claim is cancellable |
2295 | jbeckett 28-FEB-03 Bug 2751910 - update claims to 0 instead of cancelling |
2296 +===========================================================================*/
2297 PROCEDURE cancel_claims (p_cr_id IN NUMBER,
2298 p_include_trx_claims IN VARCHAR2,
2299 x_return_status OUT NOCOPY VARCHAR2,
2300 x_msg_count OUT NOCOPY NUMBER,
2301 x_msg_data OUT NOCOPY VARCHAR2)
2302 IS
2303 l_claim_id NUMBER;
2304 l_invoice_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
2305 l_claim_reason_code_id NUMBER;
2306 l_claim_reason_name VARCHAR2(80);
2307 l_claim_number VARCHAR2(30);
2308 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
2309 l_claim_amount NUMBER;
2310
2311 CURSOR c_claims IS
2312 SELECT secondary_application_ref_id,
2313 application_ref_num,
2314 applied_payment_schedule_id,
2315 applied_customer_trx_id,
2316 amount_applied,
2317 apply_date
2318 FROM ar_receivable_applications
2319 WHERE cash_receipt_id = p_cr_id
2320 AND display = 'Y'
2321 AND applied_payment_schedule_id = DECODE(p_include_trx_claims,
2322 'N',-4,applied_payment_schedule_id)
2323 AND application_ref_type = 'CLAIM';
2324
2325 BEGIN
2326 IF PG_DEBUG in ('Y', 'C') THEN
2327 arp_standard.debug( 'arp_reverse_receipt.cancel_claims() +');
2328 END IF;
2329 FOR c1 in c_claims LOOP
2330 IF OZF_Claim_GRP.Check_Cancell_Deduction(
2331 p_claim_id => c1.secondary_application_ref_id)
2332 THEN
2333 IF c1.applied_payment_schedule_id = -4
2334 THEN
2335 l_invoice_ps_id := NULL;
2336 l_claim_id := c1.secondary_application_ref_id;
2337 l_claim_amount := 0;
2338 ELSE
2339 l_invoice_ps_id := c1.applied_payment_schedule_id;
2340 l_claim_id := NULL;
2341 -- Bug 2946734 - invoice claims not zeroized
2342 SELECT amount_due_remaining
2343 INTO l_claim_amount
2344 FROM ar_payment_schedules
2345 WHERE payment_schedule_id = l_invoice_ps_id;
2346 l_claim_amount := l_claim_amount + c1.amount_applied;
2347 END IF;
2348
2349 arp_process_application.update_claim(
2350 p_claim_id => l_claim_id
2351 , p_invoice_ps_id => l_invoice_ps_id
2352 , p_customer_trx_id => c1.applied_customer_trx_id
2353 , p_amount => l_claim_amount
2354 , p_amount_applied => c1.amount_applied
2355 , p_apply_date => c1.apply_date
2356 , p_cash_receipt_id => p_cr_id
2357 , p_receipt_number => null
2358 , p_action_type => 'U'
2359 , x_claim_reason_code_id => l_claim_reason_code_id
2360 , x_claim_reason_name => l_claim_reason_name
2361 , x_claim_number => l_claim_number
2362 , x_return_status => x_return_status
2363 , x_msg_count => x_msg_count
2364 , x_msg_data => x_msg_data);
2365 IF x_return_status <> 'S'
2366 THEN
2367 RETURN;
2368 END IF;
2369 END IF;
2370 END LOOP;
2371 IF PG_DEBUG in ('Y', 'C') THEN
2372 arp_standard.debug( 'arp_reverse_receipt.cancel_claims() -');
2373 END IF;
2374 END cancel_claims;
2375 --
2376 /*===========================================================================+
2377 | FUNCTION |
2378 | receipt_has_claims |
2379 | |
2380 | DESCRIPTION |
2381 | checks if any claims exist on receipt |
2382 | |
2383 | SCOPE - PUBLIC |
2384 | |
2385 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2386 | |
2387 | ARGUMENTS : IN: |
2388 | p_cr_id - Cash receipt ID |
2389 | OUT: |
2390 | |
2391 | RETURNS : BOOLEAN |
2392 | |
2393 | NOTES |
2394 | |
2395 | MODIFICATION HISTORY |
2396 | |
2397 | jbeckett 24-JUN-02 Created (bug 2420941) |
2398 +===========================================================================*/
2399 FUNCTION receipt_has_claims(
2400 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE)
2401 RETURN BOOLEAN
2402 IS
2403 CURSOR c_claim_count IS
2404 SELECT count(*)
2405 FROM ar_receivable_applications
2406 WHERE cash_receipt_id = p_cr_id
2407 AND display = 'Y'
2408 AND application_ref_type = 'CLAIM';
2409
2410 l_claim_count NUMBER := 0;
2411
2412 BEGIN
2413
2414 IF PG_DEBUG in ('Y', 'C') THEN
2415 arp_standard.debug( 'arp_reverse_receipt.receipt_has_claims() +');
2416 END IF;
2417
2418 OPEN c_claim_count;
2419 FETCH c_claim_count INTO l_claim_count;
2420 CLOSE c_claim_count;
2421 IF l_claim_count = 0
2422 THEN
2423 RETURN FALSE;
2424 ELSE
2425 RETURN TRUE;
2426 END IF;
2427
2428 IF PG_DEBUG in ('Y', 'C') THEN
2429 arp_standard.debug( 'arp_reverse_receipt.receipt_has_claims() -');
2430 END IF;
2431
2432 END receipt_has_claims;
2433 --
2434
2435 /*===========================================================================+
2436 | PROCEDURE |
2437 | check_netted_receipts |
2438 | |
2439 | DESCRIPTION |
2440 | checks if payment netting unapplication will cause netted receipt to go|
2441 | negative |
2442 | |
2443 | SCOPE - PUBLIC |
2444 | |
2445 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2446 | ar_receipt_val_pvt.validate_unapp_open_receipt |
2447 | |
2448 | ARGUMENTS : IN: |
2449 | p_cr_id - Cash receipt ID |
2450 | |
2451 | OUT: |
2452 | x_return_status
2453 | |
2454 | NOTES |
2455 | |
2456 | MODIFICATION HISTORY |
2457 | |
2458 | jbeckett 16-JUL-03 Created (bug 3048023) |
2459 +===========================================================================*/
2460 PROCEDURE check_netted_receipts(
2461 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2462 x_return_status OUT NOCOPY VARCHAR2)
2463 IS
2464
2465 CURSOR c_netted_receipts IS
2466 SELECT ps.cash_receipt_id, app.amount_applied
2467 FROM ar_receivable_applications app,
2468 ar_payment_schedules ps
2469 WHERE app.applied_payment_schedule_id = ps.payment_schedule_id
2470 AND app.cash_receipt_id = p_cr_id
2471 AND app.display = 'Y'
2472 AND app.receivables_trx_id = -16;
2473
2474 l_return_status VARCHAR(1);
2475
2476 BEGIN
2477 IF PG_DEBUG in ('Y', 'C') THEN
2478 arp_util.debug('arp_reverse_receipt.check_netted_receipts()+');
2479 END IF;
2480
2481 x_return_status := 'S';
2482 FOR c1 in c_netted_receipts LOOP
2483 ar_receipt_val_pvt.validate_unapp_open_receipt(
2484 p_applied_cash_receipt_id => c1.cash_receipt_id,
2485 p_amount_applied => c1.amount_applied,
2486 p_return_status => l_return_status);
2487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2488 x_return_status := 'E';
2489 FND_MESSAGE.SET_NAME('AR','AR_RW_NET_RVSL_OVERDRAWS_RCT');
2490 FND_MSG_PUB.Add;
2491 EXIT;
2492 END IF;
2493 END LOOP;
2494
2495 IF PG_DEBUG in ('Y', 'C') THEN
2496 arp_util.debug('arp_reverse_receipt.check_netted_receipts()-');
2497 END IF;
2498
2499 EXCEPTION
2500 WHEN others THEN
2501 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
2502 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',SQLERRM);
2503 FND_MSG_PUB.Add;
2504 x_return_status := FND_API.G_RET_STS_ERROR ;
2505 IF PG_DEBUG in ('Y', 'C') THEN
2506 arp_util.debug('EXCEPTION :arp_revers_receipt.check_netted_receipts '||SQLERRM);
2507 END IF;
2508 END check_netted_receipts;
2509 --
2510 /*===========================================================================+
2511 | FUNCTION |
2512 | receipt_has_processed_refunds |
2513 | |
2514 | DESCRIPTION |
2515 | checks if any refunds on receipt cannot be cancelled |
2516 | |
2517 | SCOPE - PUBLIC |
2518 | |
2519 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2520 | |
2521 | ARGUMENTS : IN: |
2522 | p_cr_id - Cash receipt ID |
2523 | OUT: |
2524 | |
2525 | RETURNS : BOOLEAN |
2526 | |
2527 | NOTES |
2528 | |
2529 | MODIFICATION HISTORY |
2530 | |
2531 | jbeckett 23-DEC-05 Created (bug 4861233) |
2532 +===========================================================================*/
2533 FUNCTION receipt_has_processed_refunds(
2534 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE)
2535 RETURN BOOLEAN
2536 IS
2537
2538 CURSOR c_refunds IS
2539 SELECT application_ref_id
2540 FROM ar_receivable_applications
2541 WHERE cash_receipt_id = p_cr_id
2542 AND display = 'Y'
2543 AND applied_payment_schedule_id = -8;
2544 l_refund_count NUMBER ;
2545 l_error_code VARCHAR2(240);
2546 l_debug_info VARCHAR2(4000);
2547
2548 BEGIN
2549
2550 IF PG_DEBUG in ('Y', 'C') THEN
2551 arp_standard.debug( 'arp_reverse_receipt.receipt_has_processed_refunds() +');
2552 END IF;
2553
2554 FOR c1 in c_refunds LOOP
2555 BEGIN
2556 IF NOT AP_Cancel_PKG.Is_Invoice_Cancellable(
2557 P_invoice_id => c1.application_ref_id,
2558 P_error_code => l_error_code,
2559 P_debug_info => l_debug_info,
2560 P_calling_sequence => 'ARREREVB.receipt_has_processed_refunds') THEN
2561 RETURN TRUE;
2562 END IF;
2563 EXCEPTION WHEN OTHERS THEN
2564 arp_standard.debug('l_error_code: '||l_error_code);
2565 arp_standard.debug('l_debug_info: '||l_debug_info);
2566 arp_standard.debug('Unexpected error encountered calling ap_cancel_pkg.is_invoice_cancellable: '||sqlerrm);
2567 RETURN TRUE;
2568 END;
2569 END LOOP;
2570 RETURN FALSE;
2571
2572 IF PG_DEBUG in ('Y', 'C') THEN
2573 arp_standard.debug( 'arp_reverse_receipt.receipt_has_processed_refunds() -');
2574 END IF;
2575 END receipt_has_processed_refunds;
2576 --
2577
2578 /*===========================================================================+
2579 | Function |
2580 | check_settlement_status |
2581 | |
2582 | DESCRIPTION |
2583 | checks if receipt settlement is successfuly in IBY, then only allow |
2584 | receipt reversal |
2585 | |
2586 | SCOPE - PUBLIC |
2587 | |
2588 | |
2589 | ARGUMENTS : IN: |
2590 | p_extension_id - Paymant Trxn Extension ID |
2591 | |
2592 | RETURN: BOOLEAN |
2593 | TRUE: Do not allow receipt reversal |
2594 | False: Allow receipt reversal |
2595 | |
2596 | NOTES |
2597 | |
2598 | MODIFICATION HISTORY |
2599 | |
2600 | SPDIXIT 20-JAN-2009 Created |
2601 +===========================================================================*/
2602 FUNCTION check_settlement_status(
2603 p_extension_id IN NUMBER
2604 )
2605 RETURN BOOLEAN IS
2606 l_status NUMBER ;
2607 l_receipt_status varchar2(10) := 'N';
2608
2609 Cursor get_receipt_status IS
2610 select 'Y'
2611 from ar_cash_receipts cr
2612 where cr.payment_trxn_extension_id = p_extension_id
2613 and exists (
2614 Select 1 from xla_transaction_entities
2615 where entity_code = 'RECEIPTS'
2616 and nvl(source_id_int_1 , -99) = cr.cash_receipt_id
2617 and application_id = 222
2618 and ledger_id = cr.set_of_books_id
2619 and upg_batch_id is not null ) ;
2620
2621 Cursor get_settlement_status IS
2622 SELECT summ.status
2623 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
2624 WHERE op.trxn_extension_id = p_extension_id
2625 AND op.transactionid = summ.transactionid
2626 AND summ.reqtype in ('ORAPMTCAPTURE', 'ORAPMTRETURN',
2627 'ORAPMTCREDIT', 'ORAPMTVOID', 'ORAPMTBATCHREQ', 'ORAPMTEFTCLOSEBATCH')
2628 ORDER BY summ.trxnmid desc;
2629
2630 BEGIN
2631 IF PG_DEBUG in ('Y', 'C') THEN
2632 arp_util.debug('arp_reverse_receipt.check_settlement_status()+');
2633 END IF;
2634
2635 Open get_receipt_status ;
2636 fetch get_receipt_status into l_receipt_status;
2637 close get_receipt_status;
2638
2639 /* Upgraded Receipt - return success */
2640 IF NVL(l_receipt_status, 'N') = 'Y' THEN
2641 RETURN FALSE;
2642
2643 /* Call below code only when receipt is not upgraded. Only for R12 receipts.*/
2644 Else
2645 Open get_settlement_status ;
2646 fetch get_settlement_status into l_status;
2647 close get_settlement_status;
2648
2649 if nvl(l_status, -9999) <> 0 then
2650 -- This is a error status, so error has to be raised by returning TRUE.
2651 RETURN TRUE;
2652 else
2653 -- Only status = 0 are success cases whose settlement is completed in Payments.
2654 RETURN FALSE;
2655 End IF;
2656 END IF;
2657
2658 IF PG_DEBUG in ('Y', 'C') THEN
2659 arp_util.debug('arp_reverse_receipt.check_settlement_status()-');
2660 END IF;
2661
2662 EXCEPTION
2663 WHEN OTHERS THEN
2664 IF PG_DEBUG in ('Y', 'C') THEN
2665 arp_util.debug('Exception: arp_reverse_receipt.check_settlement_status() ' || sqlerrm);
2666 END IF;
2667 RETURN TRUE;
2668
2669 END check_settlement_status;
2670
2671
2672 END ARP_REVERSE_RECEIPT;