[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_CHARGEBACK
Source
1 PACKAGE BODY ARP_PROCESS_CHARGEBACK AS
2 /* $Header: ARCECBB.pls 120.16 2007/01/12 10:14:29 ugummall ship $*/
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 NULL_VAR ar_payment_schedules%ROWTYPE; /* Added for Bug 460966 - Oracle 8 */
6 PROCEDURE validate_args_revcb(
7 p_cb_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
8 p_reversal_gl_date IN DATE,
9 p_reversal_date IN DATE );
10
11 PROCEDURE validate_args_cbrev_ct (
12 p_ct_id IN ra_customer_trx.customer_trx_id%TYPE );
13
14 PROCEDURE validate_args_cbrev_group(
15 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
16 p_ass_cr_id IN ar_adjustments.associated_cash_receipt_id%TYPE,
17 p_ct_count IN NUMBER );
18 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
19
20 /*===========================================================================+
21 | FUNCTION
22 | revision
23 | |
24 | DESCRIPTION |
25 | This function returns the revision number of this package.
26 | |
27 | SCOPE - PUBLIC |
28 | |
29 | RETURNS : Revision number of this package
30 | |
31 | MODIFICATION HISTORY
32 | 6/25/1996 Harri Kaukovuo Created
33 +===========================================================================*/
34
35 FUNCTION revision RETURN VARCHAR2 IS
36 BEGIN
37 RETURN '$Revision: 120.16 $';
38 END revision;
39
40 /*===========================================================================+
41 | PROCEDURE |
42 | reverse_chargeback - Reverse a charge back |
43 | |
44 | DESCRIPTION |
45 | This function reverses a charge back after checking that it can be done|
46 | |
47 | SCOPE - PUBLIC |
48 | |
49 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
50 | arp_process_adjustment.insert_reverse_actions |
51 | Setup to reverse an adjustment as part of chargeback reversal|
52 | arp_ps_util.update_reverse_actions |
53 | Setup to reverse the payment schedule associated with the |
54 | chargeback |
55 | |
56 | ARGUMENTS : IN: |
57 | p_cb_ct_id - Charge back Customer transaction Id |
58 | p_reversal_gl_date - Reversal GL date |
59 | p_reversal_date - Reversal Date |
60 | p_module_name - Module that called this procedure |
61 | p_module_version - Version of the module that called this |
62 | procedure |
63 | OUT: |
64 | |
65 | RETURNS : NONE |
66 | |
67 | NOTES |
68 | This procedure is originally converted from C -procedure armrcb
69 | which is in file armcb.lpc.
70 | |
71 | MODIFICATION HISTORY - 04/25/95 - Created by Ganesh Vaidee |
72 | 05/02/95 - Removed the comment on p_created_from |
73 | variable |
74 | 05/02/95 - Assigned 'REVERSE_CHARGEBACK' to |
75 | l_adj_rec.created_from variable |
76 | S.Nambiar 03/23/2001 Modified reverse_chargeback procedure to incorporate |
77 | the new functionality to create Chargeback aganist |
78 | receipts. |
79 | In case of receipt CB, p_type will be "RECEIPT" |
80 | in all other cases it will be defaulted to "TRANSACTION"
81 +===========================================================================*/
82 PROCEDURE reverse_chargeback(
83 p_cb_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
84 p_reversal_gl_date IN DATE,
85 p_reversal_date IN DATE,
86 p_module_name IN VARCHAR2,
87 p_module_version IN VARCHAR2,
88 p_type IN VARCHAR2 DEFAULT 'TRANSACTION' ) IS
89 l_amount NUMBER;
90 l_acctd_amount NUMBER;
91 l_ps_id NUMBER;
92 --
93 l_adj_rec ar_adjustments%ROWTYPE;
94 --
95 l_return_code VARCHAR2(20);
96 l_app_rec arp_global.app_rec_type;
97 --
98 l_msg_count number;
99 l_msg_data varchar2(250);
100 l_return_status varchar2(10);
101 l_new_adjustment_number ar_adjustments.adjustment_number%TYPE;
102 l_new_adjustment_id ar_adjustments.adjustment_id%TYPE;
103 l_mesg varchar2(250);
104 adj_api_failure exception;
105 --
106 BEGIN
107 IF PG_DEBUG in ('Y', 'C') THEN
108 arp_standard.debug( 'arp_process_chargeback.reverse_chargeback()+');
109 arp_standard.debug('reverse_chargeback: ' || to_char( p_cb_ct_id ) );
110 END IF;
111
112 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
113 validate_args_revcb( p_cb_ct_id, p_reversal_gl_date,
114 p_reversal_date );
115 END IF;
116 --
117 SELECT ps.amount_due_remaining,
118 -ps.amount_due_remaining,
119 ps.acctd_amount_due_remaining,
120 -ps.acctd_amount_due_remaining,
121 ps.payment_schedule_id,
122 ps.associated_cash_receipt_id,
123 NVL( -ps.amount_line_items_remaining, 0 ),
124 NVL( -ps.freight_remaining, 0 ),
125 NVL( -ps.tax_remaining, 0 ),
126 NVL( -ps.receivables_charges_remaining, 0 )
127 INTO l_amount,
128 l_adj_rec.amount,
129 l_acctd_amount,
130 l_adj_rec.acctd_amount,
131 l_ps_id,
132 l_adj_rec.associated_cash_receipt_id,
133 l_adj_rec.line_adjusted,
134 l_adj_rec.freight_adjusted,
135 l_adj_rec.tax_adjusted,
136 l_adj_rec.receivables_charges_adjusted
137 FROM ar_payment_schedules ps
138 WHERE ps.customer_trx_id = p_cb_ct_id
139 AND ps.class = 'CB';
140
141 SELECT dist.code_combination_id
142 INTO l_adj_rec.code_combination_id
143 FROM ra_cust_trx_line_gl_dist dist,
144 ra_customer_trx ct
145 WHERE ct.customer_trx_id = p_cb_ct_id
146 AND ct.customer_trx_id = dist.customer_trx_id
147 AND dist.account_class = 'REV';
148
149 --snambiar - while creating receipt chargebacks,there is no adjustment
150 --created. So while reversing,there is no adjustment to reverse. Instead,
151 --we need to create an adjustment to adjust the actual chargeback.
152
153 IF p_type <> 'TRANSACTION' THEN
154 BEGIN
155 --snambiar. For receipt chargeback,we will pass p_type as "RECEIPT"
156 --and in all other cases,p_type is defaulted to "TRANSACTION"
157 --Here we need to create the adjustment aganist the chargeback.
158
159 l_adj_rec.apply_date := p_reversal_date;
160 l_adj_rec.gl_date := p_reversal_gl_date;
161 --
162 l_adj_rec.customer_trx_id := p_cb_ct_id;
163 --
164 l_adj_rec.payment_schedule_id := l_ps_id;
165 l_adj_rec.receivables_trx_id := -12;
166 l_adj_rec.POSTABLE := 'Y';
167 l_adj_rec.type := 'INVOICE';
168 l_adj_rec.adjustment_type := 'M';
169 --
170 l_adj_rec.created_from := 'REVERSE_CHARGEBACK';
171 --
172 ar_adjust_pub.Create_Adjustment (
173 p_api_name => 'AR_ADJUST_PUB',
174 p_api_version => '1',
175 p_init_msg_list => FND_API.G_TRUE,
176 p_commit_flag => FND_API.G_FALSE,
177 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
178 p_msg_count => l_msg_count,
179 p_msg_data => l_msg_data,
180 p_return_status => l_return_status,
181 p_adj_rec => l_adj_rec,
182 p_new_adjust_number => l_new_adjustment_number,
183 p_new_adjust_id => l_new_adjustment_id);
184
185 IF l_return_status <> 'S'
186 THEN
187 IF l_msg_count > 1
188 THEN
189 fnd_msg_pub.reset ;
190 l_mesg := fnd_msg_pub.get(p_encoded=>FND_API.G_FALSE);
191 WHILE l_mesg IS NOT NULL
192 LOOP
193 IF PG_DEBUG in ('Y', 'C') THEN
194 arp_util.debug ('reverse_chargeback: ' || l_mesg,G_MSG_HIGH);
195 END IF;
196 l_mesg := fnd_msg_pub.get(p_encoded=>FND_API.G_FALSE);
197 END LOOP ;
198 ELSE
199 l_mesg := l_msg_data ;
200 END IF;
201
202 raise adj_api_failure;
203
204 END IF;
205
206 RETURN;
207 EXCEPTION
208 WHEN adj_api_failure THEN
209 IF PG_DEBUG in ('Y', 'C') THEN
210 arp_standard.debug('EXCEPTION: arp_process_chargeback.reverse_chargeback
211 receipt CB adjustment API failed' );
212 END IF;
213 RAISE;
214 WHEN OTHERS THEN
215 IF PG_DEBUG in ('Y', 'C') THEN
216 arp_standard.debug('EXCEPTION: arp_process_chargeback.reverse_chargeback
217 receipt CB adjustment failed' );
218 END IF;
219 RAISE;
220
221 END;
222
223 ELSE
224 --
225 l_adj_rec.apply_date := p_reversal_date;
226 l_adj_rec.gl_date := p_reversal_gl_date;
227 --
228 l_adj_rec.customer_trx_id := p_cb_ct_id;
229 --
230 l_adj_rec.payment_schedule_id := l_ps_id;
231 l_adj_rec.receivables_trx_id := arp_global.G_CB_REV_RT_ID;
232 l_adj_rec.POSTABLE := 'Y';
233 l_adj_rec.type := 'INVOICE';
234 l_adj_rec.adjustment_type := 'M';
235 --
236 l_adj_rec.created_from := 'REVERSE_CHARGEBACK';
237 --
238 arp_process_adjustment.insert_reverse_actions( l_adj_rec, NULL, NULL );
239 --
240 -- Populate the payment schedule data structure
241 --
242 l_app_rec.amount_applied := l_amount;
243 l_app_rec.line_applied := l_amount;
244 l_app_rec.acctd_amount_applied := l_acctd_amount;
245 --
246 -- Get closed dates
247 --
248 arp_ps_util.get_closed_dates( l_ps_id, p_reversal_gl_date,
249 p_reversal_date, l_app_rec.gl_date_closed,
250 l_app_rec.actual_date_closed, 'PMT' );
251 --
252 l_app_rec.ps_id := l_ps_id;
253 l_app_rec.trx_type := 'AR_ADJ';
254 l_app_rec.user_id := FND_GLOBAL.user_id;
255 --
256 arp_ps_util.update_reverse_actions( l_app_rec, NULL, NULL);
257 END IF;
258
259 IF PG_DEBUG in ('Y', 'C') THEN
260 arp_standard.debug( 'arp_process_chargeback.reverse_chargeback()-');
261 END IF;
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 IF PG_DEBUG in ('Y', 'C') THEN
266 arp_standard.debug('reverse_chargeback: ' ||
267 'EXCEPTION: arp_process_chargeback.reverse_chargeback' );
268 END IF;
269 RAISE;
270 END reverse_chargeback;
271 --
272 /*===========================================================================+
273 | PROCEDURE |
274 | validate_args_revcb |
275 | |
276 | DESCRIPTION |
277 | Validate arguments passed to reverse_chargeback procedure |
278 | |
279 | SCOPE - PRIVATE |
280 | |
281 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
282 | |
283 | ARGUMENTS : IN: |
284 | p_cb_ct_id - Charge back Customer transaction Id |
285 | p_reversal_gl_date - Reversal GL date |
286 | p_reversal_date - Reversal Date |
287 | OUT: |
288 | |
289 | RETURNS : NONE |
290 | |
291 | NOTES |
292 | |
293 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
294 | |
295 +===========================================================================*/
296 PROCEDURE validate_args_revcb(
297 p_cb_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
298 p_reversal_gl_date IN DATE,
299 p_reversal_date IN DATE ) IS
300 BEGIN
301 IF PG_DEBUG in ('Y', 'C') THEN
302 arp_standard.debug( '>>>>>>>> arp_process_chargeback.validate_args_revcb' );
303 END IF;
304 --
305 IF ( p_cb_ct_id is NULL OR p_reversal_gl_date is NULL OR
306 p_reversal_date is NULL ) THEN
307 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
308 APP_EXCEPTION.raise_exception;
309 END IF;
310 IF PG_DEBUG in ('Y', 'C') THEN
311 arp_standard.debug( '<<<<<<<< arp_process_chargeback.validate_args_revcb' );
312 END IF;
313 --
314 EXCEPTION
315 WHEN OTHERS THEN
316 IF PG_DEBUG in ('Y', 'C') THEN
317 arp_standard.debug('validate_args_revcb: ' ||
318 'EXCEPTION: arp_process_chargeback.validate_args_revcb' );
319 END IF;
320 RAISE;
321 END validate_args_revcb;
322 --
323 /*===========================================================================+
324 | PROCEDURE |
325 | validate_cb_reversal |
326 | |
327 | DESCRIPTION |
328 | Validate if a charge back can be reversed |
329 | |
330 | SCOPE - PUBLIC |
331 | |
332 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
333 | |
334 | ARGUMENTS : IN: |
335 | p_ps_id - Payment Schedule Id assocaited with a charge |
336 | back |
337 | p_ass_cr_id - Cash receipt Id associated with a charge |
338 | back |
339 | p_ct_count - ???????????? |
340 | p_module_version - version of module that called the |
341 | procedure |
342 | p_module_name - Name of the module that called the proc. |
343 | OUT: |
344 | |
345 | RETURNS : BOOLEAN |
346 | |
347 | NOTES |
348 | This is an ovreloaded procedure |
349 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
350 | |
351 +===========================================================================*/
352 FUNCTION validate_cb_reversal (
353 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
354 p_ass_cr_id IN ar_adjustments.associated_cash_receipt_id%TYPE,
355 p_ct_count IN NUMBER, p_module_name IN VARCHAR2,
356 p_module_version IN VARCHAR2 ) RETURN BOOLEAN IS
357 l_sum NUMBER DEFAULT 0;
358 --
359 l_test_failed BOOLEAN DEFAULT TRUE;
360 l_count NUMBER;
361 BEGIN
362 IF PG_DEBUG in ('Y', 'C') THEN
363 arp_standard.debug( 'arp_process_chargeback.validate_cb_reversal()+' );
364 arp_standard.debug( to_char( p_ps_id ) );
365 arp_standard.debug( to_char( p_ass_cr_id ) );
366 END IF;
367 --
368 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
369 validate_args_cbrev_group( p_ps_id, p_ass_cr_id, p_ct_count );
370 END IF;
371 --
372 -- Validate that the net of applications to the chargeback is zero.
373 --
374 IF PG_DEBUG in ('Y', 'C') THEN
375 arp_standard.debug('before BEGIN SELECT NVL in app_delete' );
376 END IF;
377 SELECT NVL( sum( ra.amount_applied), 0 )
378 INTO l_sum
379 FROM ar_receivable_applications ra
380 WHERE ra.applied_customer_trx_id in (
381 SELECT a.chargeback_customer_trx_id
382 FROM ar_adjustments b,
383 ar_adjustments a
384 WHERE a.receivables_trx_id = arp_global.G_CB_RT_ID
385 AND a.associated_cash_receipt_id = p_ass_cr_id
386 AND a.payment_schedule_id = p_ps_id
387 AND b.receivables_trx_id(+) =
388 arp_global.G_CB_REV_RT_ID
389 AND b.customer_trx_id(+) = a.chargeback_customer_trx_id
390 AND b.customer_trx_id is NULL );
391 --
392 IF PG_DEBUG in ('Y', 'C') THEN
393 arp_standard.debug( 'before 1st sum' );
394 arp_standard.debug( to_char( l_sum ) );
395 END IF;
396 IF ( l_sum <> 0 ) THEN
397 IF PG_DEBUG in ('Y', 'C') THEN
398 arp_standard.debug( 'Sum of Charge back applications is non-zero');
399 END IF;
400 l_test_failed := FALSE;
401 END IF;
402 --
403 l_sum := 0;
404 --
405 -- Validate that the net of adjustments to the chargeback is zero.
406 --
407 IF PG_DEBUG in ('Y', 'C') THEN
408 arp_standard.debug( 'inside SELECT nvl 2nd in app_delete' );
409 END IF;
410 SELECT NVL( sum( amount ), 0 )
411 INTO l_sum
412 FROM ar_adjustments
413 WHERE customer_trx_id in (
414 SELECT a.chargeback_customer_trx_id
415 FROM ar_adjustments a,
416 ar_adjustments b
417 WHERE a.receivables_trx_id = arp_global.G_CB_RT_ID
418 AND a.associated_cash_receipt_id = p_ass_cr_id
419 AND a.payment_schedule_id = p_ps_id
420 AND b.receivables_trx_id(+) =
421 arp_global.G_CB_REV_RT_ID
422 AND b.customer_trx_id(+) = a.chargeback_customer_trx_id
423 AND b.customer_trx_id is NULL )
424 AND NVL( postable, 'Y') = 'Y';
425 --
426 IF PG_DEBUG in ('Y', 'C') THEN
427 arp_standard.debug( 'before 2nd sum' );
428 arp_standard.debug( to_char( l_sum ) );
429 END IF;
430 --
431 IF ( l_sum <> 0 ) THEN
432 IF PG_DEBUG in ('Y', 'C') THEN
433 arp_standard.debug( 'Sum of Charge back applications is non-zero');
434 END IF;
435 l_test_failed := FALSE;
436 END IF;
437 --
438 IF ( l_test_failed = FALSE ) THEN
439 RETURN FALSE;
440 END IF;
441 --
442 -- Check if specified chargeback exist
443 --
444 IF PG_DEBUG in ('Y', 'C') THEN
445 arp_standard.debug( 'before SELECT COUNT(*) in app_delete' );
446 END IF;
447 --
448 SELECT count( distinct ct.customer_trx_id )
449 INTO l_count
450 FROM ra_customer_trx ct,
451 ar_payment_schedules ps
452 WHERE ct.customer_trx_id in (
453 SELECT a.chargeback_customer_trx_id
454 FROM ar_adjustments b,
455 ar_adjustments a
456 WHERE a.receivables_trx_id = arp_global.G_CB_RT_ID
457 AND a.associated_cash_receipt_id = p_ass_cr_id
458 AND a.payment_schedule_id = p_ps_id
459 AND b.receivables_trx_id(+) =
460 arp_global.G_CB_REV_RT_ID
461 AND b.customer_trx_id(+) =
462 a.chargeback_customer_trx_id
463 AND b.customer_trx_id is NULL )
464 AND ps.customer_trx_id = ct.customer_trx_id
465 AND ps.class = 'CB';
466
467 IF ( l_count <> p_ct_count ) THEN
468 IF PG_DEBUG in ('Y', 'C') THEN
469 arp_standard.debug( 'passed in count does not match selected count' );
470 END IF;
471 RETURN FALSE;
472 END IF;
473
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_standard.debug( 'arp_process_chargeback.validate_cb_reversal()-');
476 END IF;
477 RETURN TRUE;
478
479 EXCEPTION
480 WHEN OTHERS THEN
481 IF PG_DEBUG in ('Y', 'C') THEN
482 arp_standard.debug(
483 'EXCEPTION: arp_process_chargeback.validate_cb_reversal' );
484 END IF;
485 RAISE;
486 END validate_cb_reversal;
487 --
488 /*===========================================================================+
489 | PROCEDURE |
490 | validate_cb_reversal |
491 | |
492 | DESCRIPTION |
493 | Validate if a charge back can be reversed |
494 | |
495 | SCOPE - PUBLIC |
496 | |
497 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
498 | |
499 | ARGUMENTS : IN: |
500 | p_ct_id - Customer Trx ID associated with the charge back|
501 | p_ct_count - ???????????? |
502 | p_module_version - version of module that called the |
503 | procedure |
504 | p_module_name - Name of the module that called the proc. |
505 | OUT: |
506 | |
507 | RETURNS : BOOLEAN |
508 | |
509 | NOTES |
510 | This is an ovreloaded procedure |
511 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
512 | |
513 +===========================================================================*/
514 FUNCTION validate_cb_reversal ( p_ct_id IN ra_customer_trx.customer_trx_id%TYPE,
515 p_module_name IN VARCHAR2,
516 p_module_version IN VARCHAR2 ) RETURN BOOLEAN IS
517 l_sum NUMBER DEFAULT 0;
518 --
519 l_test_failed BOOLEAN DEFAULT TRUE;
520 l_count NUMBER;
521 BEGIN
522 IF PG_DEBUG in ('Y', 'C') THEN
523 arp_standard.debug( '>>>>>>>> arp_process_chargeback.validate_cb_reversal' );
524 arp_standard.debug( to_char( p_ct_id ) );
525 END IF;
526 --
527 IF ( p_module_name IS NOT NULL AND p_module_version IS NOT NULL ) THEN
528 validate_args_cbrev_ct( p_ct_id );
529 END IF;
530 --
531 BEGIN
532 --
533 -- Validate that the net of applications to the chargeback is zero.
534 --
535 IF PG_DEBUG in ('Y', 'C') THEN
536 arp_standard.debug( 'inside BEGIN SELECT NVL in app_delete' );
537 END IF;
538 SELECT NVL( sum( amount_applied), 0 )
539 INTO l_sum
540 FROM ar_receivable_applications
541 WHERE applied_customer_trx_id = p_ct_id;
542 --
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545 IF PG_DEBUG in ('Y', 'C') THEN
546 arp_standard.debug(
547 'arp_process_chargeback.validate_cb_reversal - NO_DATA_FOUND' );
548 END IF;
549 END;
550 --
551 IF PG_DEBUG in ('Y', 'C') THEN
552 arp_standard.debug( 'before 1st sum' );
553 arp_standard.debug( to_char( l_sum ) );
554 END IF;
555 --
556 IF ( l_sum <> 0 ) THEN
557 IF PG_DEBUG in ('Y', 'C') THEN
558 arp_standard.debug( 'Sum of Charge back applications is non-zero');
559 END IF;
560 l_test_failed := FALSE;
561 END IF;
562 --
563 l_sum := 0;
564 --
565 -- Validate that the net of adjustments to the chargeback is zero.
566 --
567 BEGIN
568 IF PG_DEBUG in ('Y', 'C') THEN
569 arp_standard.debug( 'inside SELECT nvl 2nd in app_delete' );
570 END IF;
571 SELECT NVL( sum( amount ), 0 )
572 INTO l_sum
573 FROM ar_adjustments
574 WHERE customer_trx_id = p_ct_id
575 AND NVL( postable, 'Y') = 'Y';
576 --
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 IF PG_DEBUG in ('Y', 'C') THEN
580 arp_standard.debug(
581 'No data found in RA table - validate_cb_reversal ' );
582 END IF;
583 END;
584 --
585 IF PG_DEBUG in ('Y', 'C') THEN
586 arp_standard.debug( 'before 2nd sum' );
587 arp_standard.debug( to_char( l_sum ) );
588 END IF;
589 --
590 IF ( l_sum <> 0 ) THEN
591 IF PG_DEBUG in ('Y', 'C') THEN
592 arp_standard.debug( 'Sum of Charge back applications is non-zero');
593 END IF;
594 l_test_failed := FALSE;
595 END IF;
596 --
597 IF ( l_test_failed = FALSE ) THEN
598 RETURN FALSE;
599 END IF;
600 --
601 -- Check if specified chargeback exist
602 --
603 IF PG_DEBUG in ('Y', 'C') THEN
604 arp_standard.debug( 'before SELECT COUNT(*) in app_delete' );
605 END IF;
606 BEGIN
607 SELECT count(*)
608 INTO l_count
609 FROM ra_customer_trx ct,
610 ar_payment_schedules ps
611 WHERE ct.customer_trx_id = p_ct_id
612 AND ps.customer_trx_id = ct.customer_trx_id
613 AND ps.class = 'CB';
614 EXCEPTION
615 WHEN NO_DATA_FOUND THEN
616 IF PG_DEBUG in ('Y', 'C') THEN
617 arp_standard.debug(
618 'arp_process_chargeback.validate_cb_reversa - NO_DATA_FOUND' );
619 END IF;
620 RETURN FALSE;
621 END;
622 --
623 -- At this point, validation is successful. Lock payment schedule recor
624 -- and return AR_M_SUCCESS. However, this should be done at a different
625 -- level.
626
627 --
628 IF PG_DEBUG in ('Y', 'C') THEN
629 arp_standard.debug( '<<<<<<<< arp_process_chargeback.validate_cb_reversal' );
630 END IF;
631 RETURN TRUE;
632 --
633 EXCEPTION
634 WHEN OTHERS THEN
635 IF PG_DEBUG in ('Y', 'C') THEN
636 arp_standard.debug(
637 'EXCEPTION: arp_process_chargeback.validate_cb_reversal' );
638 END IF;
639 RAISE;
640 END validate_cb_reversal;
641 /*===========================================================================+
642 | PROCEDURE |
643 | validate_args_cbrev_ct |
644 | |
645 | DESCRIPTION |
646 | This procedure validates input to validate_cb_reversal procedure |
647 | |
648 | SCOPE - PRIVATE |
649 | |
650 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
651 | |
652 | ARGUMENTS : IN: |
653 | p_ct_id - Customer Trx ID associated with the charge back |
654 | OUT: |
655 | |
656 | RETURNS : NONE |
657 | |
658 | NOTES |
659 | |
660 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
661 | |
662 +===========================================================================*/
663 PROCEDURE validate_args_cbrev_ct (
664 p_ct_id IN ra_customer_trx.customer_trx_id%TYPE ) IS
665 BEGIN
666 arp_standard.debug( '>>>>>>>> arp_process_chargeback.validate_args_cbrev_ct' );
667 IF ( p_ct_id is NULL ) THEN
668 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
669 APP_EXCEPTION.raise_exception;
670 END IF;
671 --
672 arp_standard.debug( '<<<<<<<< arp_process_chargeback.validate_args_cbrev_ct' );
673 EXCEPTION
674 WHEN OTHERS THEN
675 arp_standard.debug(
676 'EXCEPTION: arp_process_chargeback.validate_args_cbrev_ct' );
677 RAISE;
678 END;
679 /*===========================================================================+
680 | PROCEDURE |
681 | validate_args_cbrev_group |
682 | |
683 | DESCRIPTION |
684 | This procedure validates input to validate_cb_reversal procedure |
685 | |
686 | SCOPE - PRIVATE |
687 | |
688 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
689 | |
690 | ARGUMENTS : IN: |
691 | p_ps_id - Payment Schedule Id assocaited with a charge |
692 | back |
693 | p_ass_cr_id - Cash receipt Id associated with a charge |
694 | back |
695 | p_ct_count - ???????????? |
696 | OUT: |
697 | |
698 | RETURNS : NONE |
699 | |
700 | NOTES |
701 | |
702 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
703 | |
704 +===========================================================================*/
705 PROCEDURE validate_args_cbrev_group(
706 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
707 p_ass_cr_id IN ar_adjustments.associated_cash_receipt_id%TYPE,
708 p_ct_count IN NUMBER ) IS
709 BEGIN
710 IF PG_DEBUG in ('Y', 'C') THEN
711 arp_standard.debug( '>>>>>>>> arp_process_chargeback.validate_args_cbrev_group' );
712 END IF;
713 IF ( p_ps_id is NULL OR p_ass_cr_id is NULL OR p_ct_count is NULL ) THEN
714 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
715 APP_EXCEPTION.raise_exception;
716 END IF;
717 --
718 IF PG_DEBUG in ('Y', 'C') THEN
719 arp_standard.debug( '<<<<<<<< arp_process_chargeback.validate_args_cbrev_group' );
720 END IF;
721 EXCEPTION
722 WHEN OTHERS THEN
723 IF PG_DEBUG in ('Y', 'C') THEN
724 arp_standard.debug('validate_args_cbrev_group: ' ||
725 'EXCEPTION: arp_process_chargeback.validate_args_cbrev_group' );
726 END IF;
727 RAISE;
728 END validate_args_cbrev_group;
729
730 /*===========================================================================+
731 PROCEDURE
732 validate_args_create_cb()
733
734 DESCRIPTION
735 This procedure validates all important parameters that are needed
736 in creation of chargebacks.
737
738 SCOPE
739 Private
740
741 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
742 arp_util.validate_gl_date() Validates GL date
743
744 ARGUMENTS
745 IN
746 p_gl_date
747 p_cust_trx_type_id
748 p_inv_customer_trx_id
749 p_due_date
750 OUT
751
752 RETURNS
753 Nothing
754
755 NOTES
756
757 MODIFICATION HISTORY
758 10/10/1995 H.Kaukovuo Created
759 +===========================================================================*/
760 PROCEDURE validate_args_create_cb(
761 p_gl_date IN DATE
762 , p_cust_trx_type_id IN NUMBER
763 , p_inv_customer_trx_id IN NUMBER
764 , p_due_date IN DATE
765 )
766 IS
767 l_parameter_tokens VARCHAR2(100);
768
769 BEGIN
770 arp_standard.debug('arp_process_chargeback.validate_args_create_cb(+)' );
771
772 l_parameter_tokens := '';
773
774 -- ---------------------------------------------------
775 -- Search for invalid parameter. Stop immediately after
776 -- invalid parameter value. Display error message.
777 -- ---------------------------------------------------
778
779 IF (p_gl_date IS NULL) THEN
780 l_parameter_tokens := 'p_gl_date';
781 ELSIF (p_cust_trx_type_id IS NULL) THEN
782 l_parameter_tokens := 'p_cust_trx_type_id';
783 ELSIF (p_inv_customer_trx_id IS NULL) THEN
784 l_parameter_tokens := 'p_inv_customer_trx_id';
785 ELSIF (p_due_date IS NULL) THEN
786 l_parameter_tokens := 'p_due_date';
787 END IF;
788
789 -- ---------------------------------------------------
790 -- If one ore more variables were invalid, display the error
791 -- ---------------------------------------------------
792 IF (l_parameter_tokens <> '') THEN
793 fnd_message.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
794 fnd_message.set_token('PARAMETER',l_parameter_tokens);
795 fnd_message.set_token('PROCEDURE','ARP_PROCESS_CHARGEBACK.CREATE_CHARGEBACK');
796 app_exception.raise_exception;
797 END IF;
798
799 arp_standard.debug('arp_process_chargeback.validate_args_create_cb(-)' );
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 arp_standard.debug('-- Exception: Others: validate.args_create_cb');
804 app_exception.raise_exception;
805 END;
806
807 /*
808 ===========================================================================
809 PROCEDURE
810 init_adj_struct
811
812 DESCRIPTION
813 Initializes all adjustment variables.
814 All numeric variables are set to zero and all text variables are
815 set to NULL
816
817 NOTE
818 Strict conversion from C-code function armnadj() in armadj.lpc.
819
820 HISTORY
821 4/2/1996 Harri Kaukovuo Created
822 ===========================================================================
823 */
824 PROCEDURE init_adj_struct (
825 p_adj_rec IN OUT NOCOPY AR_ADJUSTMENTS%ROWTYPE) IS
826 BEGIN
827 IF PG_DEBUG in ('Y', 'C') THEN
828 arp_standard.debug ('arp_process_chargeback.init_adj_struct()+');
829 END IF;
830 p_adj_rec.adjustment_id := NULL;
831 p_adj_rec.amount := 0;
832 p_adj_rec.acctd_amount := 0;
833 p_adj_rec.apply_date := NULL;
834 p_adj_rec.gl_date := NULL;
835 p_adj_rec.code_combination_id := 0;
836 p_adj_rec.customer_trx_id := NULL;
837 p_adj_rec.customer_trx_line_id:= NULL;
838 p_adj_rec.subsequent_trx_id := NULL;
839 p_adj_rec.payment_schedule_id := NULL;
840 p_adj_rec.receivables_trx_id := NULL;
841 p_adj_rec.reason_code := NULL;
842 p_adj_rec.postable := NULL;
843 p_adj_rec.type := NULL;
844 p_adj_rec.adjustment_type := NULL;
845 p_adj_rec.associated_cash_receipt_id := NULL;
846 p_adj_rec.line_adjusted := NULL;
847 p_adj_rec.freight_adjusted := NULL;
848 p_adj_rec.tax_adjusted := NULL;
849 p_adj_rec.receivables_charges_adjusted := NULL;
850 p_adj_rec.chargeback_customer_trx_id := NULL;
851 p_adj_rec.created_from := NULL;
852 p_adj_rec.status := NULL;
853 p_adj_rec.request_id := NULL;
854 p_adj_rec.program_update_date := NULL;
855 p_adj_rec.program_id := NULL;
856 p_adj_rec.program_application_id := NULL;
857 IF PG_DEBUG in ('Y', 'C') THEN
858 arp_standard.debug ('arp_process_chargeback.init_adj_struct()-');
859 END IF;
860
861 END init_adj_struct;
862
863 /*
864 ===========================================================================
865 PROCEDURE
866 init_ps_struct
867
868 DESCRIPTION
869 Initializes all payment schedules variables.
870 All numeric variables are set to zero and all text variables are
871 set to NULL
872
873 NOTE
874 Strict conversion from C-code function armnps() in armps.lpc.
875 Payment_schedule_id is left NULL because table handler is going to
876 populate that value with sequence value.
877
878 HISTORY
879 4/2/1996 Harri Kaukovuo Created
880 ===========================================================================
881 */
882 PROCEDURE init_ps_struct (
883 p_ps_rec IN OUT NOCOPY AR_PAYMENT_SCHEDULES%ROWTYPE) IS
884 BEGIN
885 IF PG_DEBUG in ('Y', 'C') THEN
886 arp_standard.debug ('arp_process_chargeback.init_ps_struct()+');
887 END IF;
888 p_ps_rec.payment_schedule_id := NULL;
889 p_ps_rec.due_date := NULL;
890 p_ps_rec.amount_due_original := NULL;
891 p_ps_rec.amount_due_remaining := NULL;
892 p_ps_rec.acctd_amount_due_remaining := NULL;
893 p_ps_rec.number_of_due_dates := 0;
894 p_ps_rec.status := NULL;
895 p_ps_rec.invoice_currency_code := NULL;
896 -- p_ps_rec.payment_currency_code := NULL;
897 p_ps_rec.class := NULL;
898 p_ps_rec.cust_trx_type_id := NULL;
899 p_ps_rec.customer_id := NULL;
900 p_ps_rec.customer_site_use_id := NULL;
901 p_ps_rec.customer_trx_id := NULL;
902 p_ps_rec.cash_receipt_id := NULL;
903 p_ps_rec.associated_cash_receipt_id := NULL;
904 p_ps_rec.term_id := NULL;
905 p_ps_rec.terms_sequence_number := NULL;
906 p_ps_rec.gl_date_closed := NULL;
907 p_ps_rec.actual_date_closed := NULL;
908 p_ps_rec.discount_date := NULL;
909 p_ps_rec.amount_line_items_original := NULL;
910 p_ps_rec.amount_line_items_remaining := NULL;
911 p_ps_rec.amount_applied := NULL;
912 p_ps_rec.amount_adjusted := NULL;
913 p_ps_rec.amount_adjusted_pending := NULL;
914 p_ps_rec.amount_in_dispute := NULL;
915 p_ps_rec.amount_credited := NULL;
916 p_ps_rec.receivables_charges_remaining:= NULL;
917 p_ps_rec.freight_original := NULL;
918 p_ps_rec.freight_remaining := NULL;
919 p_ps_rec.tax_original := NULL;
920 p_ps_rec.tax_remaining := NULL;
921 p_ps_rec.discount_original := NULL;
922 p_ps_rec.discount_remaining := NULL;
923 p_ps_rec.discount_taken_earned := NULL;
924 p_ps_rec.discount_taken_unearned := NULL;
925 p_ps_rec.in_collection := NULL;
926 p_ps_rec.reversed_cash_receipt_id := NULL;
927 p_ps_rec.cash_applied_id_last := NULL;
928 p_ps_rec.cash_applied_date_last := NULL;
929 p_ps_rec.cash_applied_amount_last := NULL;
930 p_ps_rec.cash_applied_status_last := NULL;
931 p_ps_rec.cash_gl_date_last := NULL;
932 p_ps_rec.cash_receipt_id_last := NULL;
933 p_ps_rec.cash_receipt_date_last := NULL;
934 p_ps_rec.cash_receipt_amount_last := NULL;
935 p_ps_rec.cash_receipt_status_last := NULL;
936 p_ps_rec.exchange_rate_type := NULL;
937 p_ps_rec.exchange_date := NULL;
938 p_ps_rec.exchange_rate := NULL;
939 p_ps_rec.adjustment_id_last := NULL;
940 p_ps_rec.adjustment_date_last := NULL;
941 p_ps_rec.adjustment_gl_date_last := NULL;
942 p_ps_rec.adjustment_amount_last := NULL;
943 p_ps_rec.follow_up_date_last := NULL;
944 p_ps_rec.follow_up_code_last := NULL;
945 p_ps_rec.promise_date_last := NULL;
946 p_ps_rec.promise_amount_last := NULL;
947 p_ps_rec.collector_last := NULL;
948 p_ps_rec.call_date_last := NULL;
949 p_ps_rec.trx_number := NULL;
950 p_ps_rec.trx_date := NULL;
951 IF PG_DEBUG in ('Y', 'C') THEN
952 arp_standard.debug ('arp_process_chargeback.init_ps_struct()-');
953 END IF;
954
955 END init_ps_struct;
956 /*
957 ===========================================================================
958 PROCEDURE
959 init_app_struct
960
961 DESCRIPTION
962 Initializes all receivable application variables.
963 All numeric variables are set to zero and all text variables are
964 set to NULL
965
966 NOTE
967 Strict conversion from C-code function armnapp() in armapp.lpc.
968 receivable_application_id is left NULL because table handler is going to
969 populate that value with sequence value.
970
971 HISTORY
972 4/2/1996 Harri Kaukovuo Created
973 ===========================================================================
974 */
975 PROCEDURE init_app_struct (
976 p_app_rec IN OUT NOCOPY AR_RECEIVABLE_APPLICATIONS%ROWTYPE) IS
977 BEGIN
978 IF PG_DEBUG in ('Y', 'C') THEN
979 arp_standard.debug ('arp_process_chargeback.init_app_struct()+');
980 END IF;
981
982 p_app_rec.receivable_application_id := NULL;
983 p_app_rec.amount_applied := NULL;
984 -- p_app_rec.acctd_amount_applied := NULL;
985 p_app_rec.gl_date := NULL;
986 p_app_rec.code_combination_id := 0;
987 p_app_rec.display := NULL;
988 p_app_rec.apply_date := NULL;
989 p_app_rec.application_type := NULL;
990 p_app_rec.status := NULL;
991 p_app_rec.payment_schedule_id := 0;
992 -- p_app_rec.associated_cash_receipt_id := NULL;
993 p_app_rec.cash_receipt_id := NULL;
994 p_app_rec.applied_customer_trx_id := NULL;
995 p_app_rec.applied_customer_trx_line_id:= NULL;
996 p_app_rec.applied_payment_schedule_id := NULL;
997 p_app_rec.customer_trx_id := NULL;
998 p_app_rec.line_applied := NULL;
999 p_app_rec.tax_applied := NULL;
1000 p_app_rec.freight_applied := NULL;
1001 p_app_rec.receivables_charges_applied := NULL;
1002 p_app_rec.on_account_customer := NULL;
1003 p_app_rec.receivables_trx_id := NULL;
1004 p_app_rec.earned_discount_ccid := 0;
1005 p_app_rec.unearned_discount_ccid := 0;
1006 p_app_rec.earned_discount_taken := NULL;
1007 p_app_rec.acctd_earned_discount_taken := NULL;
1008 p_app_rec.unearned_discount_taken := NULL;
1009 p_app_rec.acctd_unearned_discount_taken := NULL;
1010 p_app_rec.days_late := NULL;
1011 p_app_rec.application_rule := NULL;
1012 p_app_rec.gl_posted_date := NULL;
1013
1014 IF PG_DEBUG in ('Y', 'C') THEN
1015 arp_standard.debug ('init_app_struct: ' || 'arp_process_chargeback.init_ps_struct()-');
1016 END IF;
1017
1018 END init_app_struct;
1019
1020 /*
1021 ===========================================================================+
1022 PROCEDURE
1023 create_chargeback()
1024
1025 DESCRIPTION
1026 This procedure will create a chargeback entry for given
1027 receipt/invoice pair.
1028 Procedure will create one default invoice line for quantity
1029 of 1 and amount of chargeback amount.
1030
1031 Assumptions
1032 - Overapplications and application creation signs
1033 should have been checked in client site with
1034 ARP_NON_DB_PKG.check_natural_application and
1035 ARP_NON_DB_PKG.check_creation_sign procedures.
1036
1037 The algorithm for creating chargebacks:
1038 1. Instantiate variables with default values
1039 2. Insert header RA_CUSTOMER_TRX
1040 3. Insert one line into RA_CUSTOMER_TRX_LINES
1041 4. Insert revenue account GL distribution
1042 into RA_CUST_TRX_LINE_GL_DIST
1043 5. Insert receivables account GL distribution
1044 into RA_CUST_TRX_LINE_GL_DIST
1045 6. Insert into AR_PAYMENT_SCHEDULES
1046 - Call event handler
1047
1048 7. Initialize, then populate app_struct and ps_struct
1049
1050 8. Update AR_PAYMENT_SCHEDULES
1051 9. Initialize variables for inserting a row into AR_ADJUSTMENTS
1052 10. Insert into AR_ADJUSTMENTS by calling armiadj
1053
1054 SCOPE
1055 Public
1056
1057 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1058 ARTUGBLB.pls
1059 - arp_trx_global.system_info.base_currency
1060 - arp_trx_global.system_info.base_precision
1061 - arp_trx_global.system_info.base_min_acc_unit
1062 ARCUTILB.pls
1063 - validate_gl_date
1064 ARTITRXB.pls Table handler for ra_customer_trx
1065 - insert_p
1066 ARTICTLB.pls Table handler for ra_customer_trx_lines
1067 - insert_p
1068 ARTILGDB.pls Table handler ra_cust_trx_line_gl_dist
1069 - insert_p
1070 ARTEADJS.pls Adjustment procedures
1071 - insert_adjustment
1072
1073 ARGUMENTS
1074 IN
1075 p_amount Chargeback amount
1076 p_acctd_amount Chargeback amount in base currency
1077 p_cr_trx_number Cash receipt number
1078 p_cash_receipt_id Cash receipt id
1079 p_inv_trx_number Invoice number to which chargeback
1080 is applied against
1081 p_inv_customer_trx_id Invoice ID to which chargeback is
1082 applied
1083 p_gl_id_ar_trade Receivables account code combination
1084 id
1085
1086 p_apply_date AI.APPLY_DATE
1087
1088 OUT
1089
1090 RETURNS
1091
1092 NOTES
1093
1094 MODIFICATION HISTORY
1095 10/09/1995 H.Kaukovuo Created
1096 7/29/1996 Harri Kaukovuo Bug 386242, term_id was not populated when
1097 ra_customer_trx row was created.
1098 7/30/1996 Harri Kaukovuo Bug fix 386662 document numbering was missing
1099 from the chargeback adjustment.
1100 11/17/1997 Genneva Wang Bug fix 412726 - (1) Creation of Chargeback
1101 should give message if GL Account assignment
1102 is missing. (2) Returns message if a document
1103 sequence has not been created for the
1104 Chargeback Adjustment activity.
1105 09/01/1999 Genneva Wang Bug fix: 976730 Redefault Chargeback GL Date
1106 if passed in chargeback GL date is invalid
1107 03/22/2001 S.Nambiar Program has been modified to create chargeback
1108 aganist receipt,which is a new functionality
1109 introduced for iClaim-deduction project. In this
1110 case, there won't be any adjustment created.
1111 3/26/2001 S.Nambiar Modified create_chargeback parameter list |
1112 and added p_remit_to_address_id and |
1113 p_bill_to_site_use_id for receipt chargeback |
1114 09/10/2002 S.Nambiar Bug 2444737 - Added transaction referece flex
1115 fields to create chargeback routine.
1116 01/15/2003 J.Beckett Bug 2751910 - added p_internal_notes
1117 09/23/2004 J.Beckett Bug 3879127 - p_acctd_amount is ignored and
1118 the chargeback acctd_amount is recalculated
1119 with respect to amount_due_remaining of
1120 originating invoice to ensure the sum of all
1121 chargebacks does not exceed the original
1122 invoice and to ensure the chargeback balances
1123 with the corresponding adjustment.
1124 05/25/2005 J.Beckett R12 LE uptake : Added legal_entity_id
1125 +===========================================================================
1126 */
1127
1128 procedure create_chargeback(
1129 p_amount IN NUMBER
1130 , p_acctd_amount IN NUMBER
1131 , p_trx_date IN DATE
1132 , p_gl_id_ar_trade IN NUMBER
1133 , p_gl_date IN DATE
1134
1135 , p_attribute_category IN VARCHAR2
1136 , p_attribute1 IN VARCHAR2
1137 , p_attribute2 IN VARCHAR2
1138 , p_attribute3 IN VARCHAR2
1139 , p_attribute4 IN VARCHAR2
1140 , p_attribute5 IN VARCHAR2
1141 , p_attribute6 IN VARCHAR2
1142 , p_attribute7 IN VARCHAR2
1143 , p_attribute8 IN VARCHAR2
1144 , p_attribute9 IN VARCHAR2
1145 , p_attribute10 IN VARCHAR2
1146 , p_attribute11 IN VARCHAR2
1147 , p_attribute12 IN VARCHAR2
1148 , p_attribute13 IN VARCHAR2
1149 , p_attribute14 IN VARCHAR2
1150 , p_attribute15 IN VARCHAR2
1151 , p_cust_trx_type_id IN NUMBER
1152 , p_set_of_books_id IN NUMBER
1153 , p_reason_code IN VARCHAR2
1154 , p_comments IN VARCHAR2
1155 , p_def_ussgl_trx_code_context IN VARCHAR2
1156 , p_def_ussgl_transaction_code IN VARCHAR2
1157
1158 -- For AR_PAYMENT_SCHEDULES
1159 , p_due_date IN DATE
1160 , p_customer_id IN NUMBER
1161 , p_cr_trx_number IN VARCHAR2
1162 , p_cash_receipt_id IN NUMBER
1163 , p_inv_trx_number IN VARCHAR2
1164 , p_apply_date IN DATE
1165 , p_receipt_gl_date IN DATE
1166
1167 -- We get rest of the TRX info with this ID
1168 , p_app_customer_trx_id IN NUMBER
1169 , p_app_terms_sequence_number IN NUMBER
1170
1171 , p_form_name IN VARCHAR2
1172
1173 , p_out_trx_number OUT NOCOPY VARCHAR2
1174 , p_out_customer_trx_id OUT NOCOPY NUMBER
1175 , p_doc_sequence_value IN OUT NOCOPY NUMBER
1176 , p_doc_sequence_id IN OUT NOCOPY NUMBER
1177 , p_exchange_rate_type IN VARCHAR2
1178 , p_exchange_date IN DATE
1179 , p_exchange_rate IN NUMBER
1180 , p_currency_code IN VARCHAR2
1181 , p_remit_to_address_id IN NUMBER DEFAULT 0
1182 , p_bill_to_site_use_id IN NUMBER DEFAULT 0
1183 --Bug 2444737
1184 , p_interface_header_context IN VARCHAR2
1185 , p_interface_header_attribute1 IN VARCHAR2
1186 , p_interface_header_attribute2 IN VARCHAR2
1187 , p_interface_header_attribute3 IN VARCHAR2
1188 , p_interface_header_attribute4 IN VARCHAR2
1189 , p_interface_header_attribute5 IN VARCHAR2
1190 , p_interface_header_attribute6 IN VARCHAR2
1191 , p_interface_header_attribute7 IN VARCHAR2
1192 , p_interface_header_attribute8 IN VARCHAR2
1193 , p_interface_header_attribute9 IN VARCHAR2
1194 , p_interface_header_attribute10 IN VARCHAR2
1195 , p_interface_header_attribute11 IN VARCHAR2
1196 , p_interface_header_attribute12 IN VARCHAR2
1197 , p_interface_header_attribute13 IN VARCHAR2
1198 , p_interface_header_attribute14 IN VARCHAR2
1199 , p_interface_header_attribute15 IN VARCHAR2
1200 , p_internal_notes IN VARCHAR2 -- bug 2751910
1201 , p_customer_reference IN VARCHAR2 -- bug 2751910
1202 , p_legal_entity_id IN NUMBER /* R12 LE uptake */
1203 )
1204 IS
1205
1206 l_ct_row ra_customer_trx%ROWTYPE;
1207 l_ctl_row ra_customer_trx_lines%ROWTYPE;
1208 l_ctlgd_row ra_cust_trx_line_gl_dist%ROWTYPE;
1209 l_ps_row ar_payment_schedules%ROWTYPE;
1210 l_adj_row ar_adjustments%ROWTYPE;
1211
1212 ln_app_payment_schedule_id NUMBER;
1213
1214 ln_line_applied NUMBER;
1215 ln_tax_applied NUMBER;
1216 ln_freight_applied NUMBER;
1217 ln_charges_applied NUMBER;
1218
1219 ln_acctd_amount_applied NUMBER;
1220 ln_acctd_earned_discount_taken NUMBER;
1221 ln_acctd_unearned_disc_taken NUMBER;
1222
1223 -- This stuff is for sequence numbering
1224 L_REC_NAME VARCHAR2(50);
1225 l_sequence_name VARCHAR2(500);
1226 l_sequence_id NUMBER;
1227 l_sequence_value NUMBER;
1228 l_sequence_assignment_id NUMBER;
1229
1230 l_receivable_activity_acct NUMBER;
1231 --
1232 l_chargeback_gl_date DATE;
1233 l_error_message VARCHAR2(128);
1234 l_defaulting_rule_used VARCHAR2(50);
1235 l_default_gl_date DATE;
1236 error_defaulting_gl_date EXCEPTION;
1237 l_receivables_trx_id ar_receivables_trx.receivables_trx_id%TYPE;
1238 l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE;
1239 l_remit_bank_acct_use_id ar_cash_receipts.remit_bank_acct_use_id%TYPE;
1240 l_cr_unapp_ccid ar_receivable_applications.code_combination_id%TYPE;
1241 l_inv_rec_ccid ra_cust_trx_line_gl_dist.code_combination_id%TYPE;
1242 l_id_dummy ar_receivable_applications.code_combination_id%TYPE;
1243 l_bill_to_customer_id ar_cash_receipts.pay_from_customer%TYPE;
1244 l_cb_activity_ccid ra_cust_trx_line_gl_dist.code_combination_id%TYPE;
1245 l_actual_ccid ra_cust_trx_line_gl_dist.code_combination_id%TYPE;
1246 --Bug2979254
1247 l_ev_rec arp_xla_events.xla_events_type;
1248 /* Bug 3879127 */
1249 ln_amount_due_remaining NUMBER;
1250 ln_acctd_amount_due_remaining NUMBER;
1251 ln_adr_tmp NUMBER;
1252 ln_acctd_adr_tmp NUMBER;
1253 l_acctd_amount NUMBER;
1254 l_term_end_date DATE; /*5084781*/
1255 l_rct_le_id ar_cash_receipts.legal_entity_id%TYPE; /* R12 LE uptake */
1256 ln_trx_number VARCHAR2(20);/*bug 5550153*/
1257 --Ajay : raising the business event for chargeback creation
1258
1259 CURSOR get_parent_ps (p_cust_trx_id IN NUMBER) IS
1260 SELECT customer_trx_id,
1261 payment_schedule_id,
1262 invoice_currency_code,
1263 due_date,
1264 amount_in_dispute,
1265 amount_due_original,
1266 amount_due_remaining,
1267 amount_adjusted,
1268 customer_id,
1269 customer_site_use_id,
1270 trx_date,
1271 amount_credited,
1272 status
1273 FROM ar_payment_schedules
1274 WHERE customer_trx_id = p_cust_trx_id;
1275
1276 l_prev_cust_old_state AR_BUS_EVENT_COVER.prev_cust_old_state_tab;
1277
1278 BEGIN
1279 IF PG_DEBUG in ('Y', 'C') THEN
1280 arp_standard.debug('arp_process_chargeback.create_chargeback()+');
1281 END IF;
1282
1283 l_chargeback_gl_date := p_gl_date;
1284 -- ----------------------------------------------------------
1285 -- Redefault Chargeback GL date if it is in a invalid period
1286 -- ----------------------------------------------------------
1287 IF (arp_util.is_gl_date_valid(l_chargeback_gl_date) ) THEN
1288 null;
1289 ELSE
1290 IF (arp_util.validate_and_default_gl_date(
1291 l_chargeback_gl_date,
1292 NULL,
1293 NULL,
1294 NULL,
1295 NULL,
1296 NULL,
1297 NULL,
1298 NULL,
1299 'N',
1300 NULL,
1301 arp_global.set_of_books_id,
1302 222,
1303 l_default_gl_date,
1304 l_defaulting_rule_used,
1305 l_error_message) = TRUE) THEN
1306 l_chargeback_gl_date := l_default_gl_date;
1307 ELSE
1308 RAISE error_defaulting_gl_date;
1309 END IF;
1310 END IF;
1311 --
1312 -- ---------------------------------------------------
1313 -- Validate parameters
1314 -- ---------------------------------------------------
1315
1316 arp_process_chargeback.validate_args_create_cb(
1317 l_chargeback_gl_date
1318 , p_cust_trx_type_id
1319 , p_app_customer_trx_id
1320 , p_due_date
1321 );
1322
1323 -- ---------------------------------------------------
1324 -- Set chargeback defaults
1325 -- ---------------------------------------------------
1326
1327 -- Initialize records
1328 init_adj_struct (l_adj_row);
1329 init_ps_struct (l_ps_row);
1330 -- init_app_struct (l_app_row);
1331
1332 -- First initialize chargeback header
1333 -- NOTE that old Forms 2.3 program did the initial insert into
1334 -- RA_CUSTOMER_TRX already in form level. In client/server model
1335 -- inserting into RA_CUSTOMER_TRX is done here.
1336
1337 -- Copy values from the applied transaction
1338
1339 -- snambiar - p_app_customer_trx_id -4 is used for receipt chargeback
1340 -- if p_app_customer_trx_id is -4, then fetch the details from
1341 -- for receipt PS insted of invoice PS
1342
1343 IF p_app_customer_trx_id <> -4 THEN
1344
1345 BEGIN
1346 SELECT
1347 ct.exchange_rate_type
1348 , ct.exchange_date
1349 , ct.exchange_rate
1350 , ct.bill_to_customer_id
1351 , ct.ship_to_customer_id
1352 , ct.sold_to_customer_id
1353 , ct.remit_to_address_id
1354 , ct.bill_to_site_use_id
1355 , ct.ship_to_site_use_id
1356 , ct.sold_to_site_use_id
1357 , ps.PAYMENT_SCHEDULE_ID
1358 , ct.invoice_currency_code
1359 , ct.primary_salesrep_id
1360 , ct.territory_id
1361 , ctlgd.code_combination_id
1362 /* Bug 3879127 new values needed for acctd_amount calculation */
1363 , ps.amount_due_remaining
1364 , ps.acctd_amount_due_remaining
1365 , NVL(p_legal_entity_id,ct.legal_entity_id)
1366 , ct.trx_number /*added for bug 5550153*/
1367 INTO
1368 l_ct_row.exchange_rate_type
1369 , l_ct_row.exchange_date
1370 , l_ct_row.exchange_rate
1371 , l_ct_row.bill_to_customer_id
1372 , l_ct_row.ship_to_customer_id
1373 , l_ct_row.sold_to_customer_id
1374 , l_ct_row.remit_to_address_id
1375 , l_ct_row.bill_to_site_use_id
1376 , l_ct_row.ship_to_site_use_id
1377 , l_ct_row.sold_to_site_use_id
1378 , ln_app_payment_schedule_id
1379 , l_ct_row.invoice_currency_code
1380 , l_ct_row.primary_salesrep_id
1381 , l_ct_row.territory_id
1382 , l_inv_rec_ccid
1383 /* Bug 3879127 */
1384 , ln_amount_due_remaining
1385 , ln_acctd_amount_due_remaining
1386 , l_ct_row.legal_entity_id /* R12 LE uptake */
1387 , ln_trx_number /*bug 5550153*/
1388 FROM
1389 ar_payment_schedules ps
1390 , ra_cust_trx_line_gl_dist ctlgd
1391 , ra_customer_trx ct
1392 WHERE
1393 ct.customer_trx_id = p_app_customer_trx_id
1394 AND ct.customer_trx_id = ps.customer_trx_id
1395 AND ct.customer_trx_id = ctlgd.customer_trx_id(+)
1396 AND ctlgd.account_class = 'REC'
1397 AND ctlgd.latest_rec_flag = 'Y'
1398 AND ps.terms_sequence_number = p_app_terms_sequence_number;
1399
1400 --Ajay : chargeback creation business event related stuff
1401 FOR i in get_parent_ps(p_app_customer_trx_id) LOOP
1402 l_prev_cust_old_state(i.payment_schedule_id).amount_due_remaining := i.amount_due_remaining;
1403 l_prev_cust_old_state(i.payment_schedule_id).status := i.status;
1404 l_prev_cust_old_state(i.payment_schedule_id).amount_credited := i.amount_credited;
1405
1406 END LOOP;
1407
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 IF PG_DEBUG in ('Y', 'C') THEN
1411 arp_standard.debug( '-- Exception: Select default values from old invoice');
1412 arp_standard.debug( '-- p_app_customer_trx_id='||
1413 TO_CHAR(p_app_customer_trx_id));
1414 END IF;
1415 RAISE;
1416 END;
1417 END IF;
1418
1419 BEGIN
1420 SELECT
1421 cr.pay_from_customer
1422 , cr.receipt_method_id
1423 , cr.remit_bank_acct_use_id
1424 , cr.legal_entity_id
1425 INTO
1426 l_bill_to_customer_id
1427 , l_receipt_method_id
1428 , l_remit_bank_acct_use_id
1429 , l_rct_le_id /* R12 LE uptake */
1430 FROM
1431 ar_cash_receipts cr
1432 WHERE
1433 cr.cash_receipt_id = p_cash_receipt_id;
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 IF PG_DEBUG in ('Y', 'C') THEN
1437 arp_standard.debug( '-- Exception: Select default values from the receipt');
1438 arp_standard.debug( '-- p_cash_receipt_id='|| TO_CHAR(p_cash_receipt_id));
1439 END IF;
1440 RAISE;
1441 END;
1442 --app_customer_trx_id -4 is passed only in case of chargeback
1443 IF p_app_customer_trx_id = -4 THEN
1444
1445 l_ct_row.bill_to_customer_id := l_bill_to_customer_id;
1446 l_ct_row.remit_to_address_id := p_remit_to_address_id;
1447 l_ct_row.bill_to_site_use_id := p_bill_to_site_use_id;
1448 l_ct_row.exchange_rate_type := p_exchange_rate_type;
1449 l_ct_row.exchange_date := p_exchange_date;
1450 l_ct_row.exchange_rate := p_exchange_rate;
1451 l_ct_row.invoice_currency_code:= p_currency_code;
1452
1453 l_ct_row.primary_salesrep_id := NULL;
1454 l_ct_row.territory_id := NULL;
1455 l_ct_row.ship_to_customer_id := NULL;
1456 l_ct_row.sold_to_customer_id := NULL;
1457 l_ct_row.ship_to_site_use_id := NULL;
1458 l_ct_row.sold_to_site_use_id := NULL;
1459 /* R12 LE uptake */
1460 l_ct_row.legal_entity_id := NVL(p_legal_entity_id,l_rct_le_id);
1461 END IF;
1462
1463 l_ct_row.customer_trx_id := NULL; -- This will be populated when header is inserted
1464 l_ct_row.trx_date := p_trx_date;
1465 l_ct_row.complete_flag := 'Y';
1466 l_ct_row.status_trx := 'OP';
1467 l_ct_row.batch_source_id := 12;
1468 l_ct_row.cust_trx_type_id := p_cust_trx_type_id;
1469 l_ct_row.created_from := p_form_name;
1470 l_ct_row.batch_id := NULL;
1471 l_ct_row.reason_code := p_reason_code;
1472 l_ct_row.attribute_category := p_attribute_category;
1473 l_ct_row.attribute1 := p_attribute1;
1474 l_ct_row.attribute2 := p_attribute2;
1475 l_ct_row.attribute3 := p_attribute3;
1476 l_ct_row.attribute4 := p_attribute4;
1477 l_ct_row.attribute5 := p_attribute5;
1478 l_ct_row.attribute6 := p_attribute6;
1479 l_ct_row.attribute7 := p_attribute7;
1480 l_ct_row.attribute8 := p_attribute8;
1481 l_ct_row.attribute9 := p_attribute9;
1482 l_ct_row.attribute10 := p_attribute10;
1483 l_ct_row.attribute11 := p_attribute11;
1484 l_ct_row.attribute12 := p_attribute12;
1485 l_ct_row.attribute13 := p_attribute13;
1486 l_ct_row.attribute14 := p_attribute14;
1487 l_ct_row.attribute15 := p_attribute15;
1488 l_ct_row.default_ussgl_trx_code_context := p_def_ussgl_trx_code_context;
1489 l_ct_row.default_ussgl_transaction_code :=
1490 p_def_ussgl_transaction_code;
1491 l_ct_row.doc_sequence_id := p_doc_sequence_id;
1492 l_ct_row.doc_sequence_value := p_doc_sequence_value;
1493 l_ct_row.comments := substrb(p_comments,1,240);
1494 l_ct_row.internal_notes := p_internal_notes; --Bug 2751910
1495 l_ct_row.customer_reference := p_customer_reference; --Bug 2751910
1496
1497 --Bug 2444737
1498 l_ct_row.interface_header_context := p_interface_header_context;
1499 l_ct_row.interface_header_attribute1 := p_interface_header_attribute1;
1500 l_ct_row.interface_header_attribute2 := p_interface_header_attribute2;
1501 l_ct_row.interface_header_attribute3 := p_interface_header_attribute3;
1502 l_ct_row.interface_header_attribute4 := p_interface_header_attribute4;
1503 l_ct_row.interface_header_attribute5 := p_interface_header_attribute5;
1504 l_ct_row.interface_header_attribute6 := p_interface_header_attribute6;
1505 l_ct_row.interface_header_attribute7 := p_interface_header_attribute7;
1506 l_ct_row.interface_header_attribute8 := p_interface_header_attribute8;
1507 l_ct_row.interface_header_attribute9 := p_interface_header_attribute9;
1508 l_ct_row.interface_header_attribute10 := p_interface_header_attribute10;
1509 l_ct_row.interface_header_attribute11 := p_interface_header_attribute11;
1510 l_ct_row.interface_header_attribute12 := p_interface_header_attribute12;
1511 l_ct_row.interface_header_attribute13 := p_interface_header_attribute13;
1512 l_ct_row.interface_header_attribute14 := p_interface_header_attribute14;
1513 l_ct_row.interface_header_attribute15 := p_interface_header_attribute15;
1514
1515 -- ---------------------------------------------------
1516 -- Insert the customer trx header with the term_id of
1517 -- of '5' which refers to the 'IMMEDIATE' payment
1518 -- term of 100% with 0 due dates
1519 -- ---------------------------------------------------
1520 l_ct_row.term_id := 5;
1521
1522 -- ---------------------------------------------------
1523 -- Select the default printing option according to the
1524 -- transaction type that was passed to the entity handler
1525 -- --------------------------------------------------
1526
1527 BEGIN -- Select printing option
1528 IF PG_DEBUG in ('Y', 'C') THEN
1529 arp_standard.debug( '-- Select printing option');
1530 END IF;
1531
1532 SELECT NVL(ctt.default_printing_option,'PRI')
1533 , DECODE(ctt.default_printing_option
1534 , 'NOT','N'
1535 , 'Y')
1536 INTO l_ct_row.printing_option,
1537 l_ct_row.printing_pending
1538 FROM ra_cust_trx_types ctt
1539 WHERE ctt.cust_trx_type_id = p_cust_trx_type_id;
1540
1541 /* 5084781 Begin*/
1542 select end_date_active
1543 into l_term_end_date
1544 from ra_terms where term_id = 5;
1545
1546 IF (NVL(l_term_end_date, to_date('31-12-4712','DD-MM-YYYY')) < p_trx_date ) THEN
1547 FND_MESSAGE.SET_NAME('AR','AR_RW_PAYMENT_TERM_END_DATED');
1548 APP_EXCEPTION.raise_exception;
1549 END IF;
1550 /*5084781 End*/
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 IF PG_DEBUG in ('Y', 'C') THEN
1555 arp_standard.debug( '-- Exception: Select printing option from DB');
1556 arp_standard.debug( '-- ctt.cust_trx_type_id='||
1557 TO_CHAR(p_cust_trx_type_id));
1558 END IF;
1559 RAISE;
1560 END; -- Select printing option
1561
1562 -- ---------------------------------------------------
1563 -- Insert header into RA_CUSTOMER_TRX table
1564 -- ---------------------------------------------------
1565
1566 -- Call table handler, get trx_number and customer_trx_id back
1567 -- The main procedure will return trx_number and customer_trx_id
1568 -- back to calling Form or procedure.
1569 arp_ct_pkg.insert_p(
1570 -- IN
1571 p_trx_rec => l_ct_row
1572 -- OUT
1573 , p_trx_number => l_ct_row.trx_number
1574 , p_customer_trx_id => l_ct_row.customer_trx_id
1575 );
1576
1577 p_out_trx_number := l_ct_row.trx_number;
1578 p_out_customer_trx_id := l_ct_row.customer_trx_id;
1579
1580 IF PG_DEBUG in ('Y', 'C') THEN
1581 arp_standard.debug ( '-- Transaction number and internal id values returned from');
1582 arp_standard.debug ( '-- table handler arp_ct_pkg.insert_p:');
1583 arp_standard.debug ( 'trx_number = '||l_ct_row.trx_number);
1584 arp_standard.debug ( 'customer_trx_id = '|| to_char(l_ct_row.customer_trx_id));
1585 arp_standard.debug ( '');
1586 END IF;
1587
1588 -- ---------------------------------------------------
1589 -- Get line type info for RA_CUSTOMER_TRX_LINES columns
1590 -- - memo_line_id = 1 means chargeback memo line
1591 -- ---------------------------------------------------
1592 IF p_app_customer_trx_id <> -4 THEN
1593 BEGIN
1594 IF PG_DEBUG in ('Y', 'C') THEN
1595 arp_standard.debug( '-- Get line type info');
1596 END IF;
1597 SELECT ml.memo_line_id
1598 , ml.line_type
1599 , REPLACE(REPLACE(REPLACE(
1600 REPLACE(ml.description,'&'||'invoice_number'||'&',ln_trx_number), ---bug 5550153
1601 '&'||'INVOICE_NUMBER'||'&',
1602 ln_trx_number), --- bug 5550153
1603 '&'||'receipt_number'||'&',
1604 p_cr_trx_number),
1605 '&'||'RECEIPT_NUMBER'||'&',
1606 p_cr_trx_number)
1607 , ml.uom_code
1608 INTO
1609 l_ctl_row.memo_line_id
1610 , l_ctl_row.line_type
1611 , l_ctl_row.description
1612 , l_ctl_row.uom_code
1613 FROM ar_memo_lines ml
1614 WHERE ml.memo_line_id = 1;
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 IF PG_DEBUG in ('Y', 'C') THEN
1619 arp_standard.debug( '-- Exception: SELECT FROM AR_MEMO_LINES');
1620 END IF;
1621 app_exception.raise_exception;
1622 END;
1623 ELSE
1624 IF PG_DEBUG in ('Y', 'C') THEN
1625 arp_standard.debug( '-- Get line type info');
1626 END IF;
1627 --snambiar in receipt chargeback case,put the receipt details in
1628 --the description instead of invoice details
1629 BEGIN
1630 SELECT ml.memo_line_id
1631 , ml.line_type
1632 , REPLACE(REPLACE(ml.description,'invoice','Receipt number'),'&'||'INVOICE_NUMBER'||'&',p_cr_trx_number)
1633 , ml.uom_code
1634 INTO
1635 l_ctl_row.memo_line_id
1636 , l_ctl_row.line_type
1637 , l_ctl_row.description
1638 , l_ctl_row.uom_code
1639 FROM ar_memo_lines ml
1640 WHERE ml.memo_line_id = 1;
1641
1642 EXCEPTION
1643 WHEN OTHERS THEN
1644 IF PG_DEBUG in ('Y', 'C') THEN
1645 arp_standard.debug( '-- Exception: SELECT FROM AR_MEMO_LINES');
1646 END IF;
1647 app_exception.raise_exception;
1648 END;
1649 END IF;
1650
1651 -- ---------------------------------------------------
1652 -- Set values for RA_CUSTOMER_TRX_LINES columns
1653 -- ---------------------------------------------------
1654
1655 l_ctl_row.customer_trx_id := l_ct_row.customer_trx_id;
1656 l_ctl_row.line_number := 1;
1657 l_ctl_row.taxable_flag := 'N';
1658 l_ctl_row.unit_selling_price := p_amount;
1659 l_ctl_row.quantity_invoiced := 1;
1660 l_ctl_row.extended_amount := p_amount;
1661 l_ctl_row.reason_code := p_reason_code;
1662 l_ctl_row.revenue_amount := p_amount;
1663 -- Copy the default USSGL values to chargeback lines
1664 l_ctl_row.default_ussgl_transaction_code :=
1665 l_ct_row.default_ussgl_transaction_code;
1666 l_ctl_row.default_ussgl_trx_code_context :=
1667 l_ct_row.default_ussgl_trx_code_context;
1668 l_ctl_row.set_of_books_id := p_set_of_books_id;
1669
1670 -- ---------------------------------------------------
1671 -- Insert line into RA_CUSTOMER_TRX_LINES table
1672 -- ---------------------------------------------------
1673
1674 arp_ctl_pkg.insert_p(
1675 -- IN
1676 p_line_rec => l_ctl_row
1677 -- OUT
1678 , p_customer_trx_line_id => l_ctl_row.customer_trx_line_id
1679 );
1680
1681 -- ---------------------------------------------------
1682 -- Insert revenue account distribution
1683 -- ---------------------------------------------------
1684
1685 -- ---------------------------------------------------
1686 -- Set values for RA_CUST_TRX_LINE_GL_DIST table columns
1687 -- The revenue distributions should have an account class
1688 -- of 'REV'
1689 -- ---------------------------------------------------
1690
1691 l_ctlgd_row.customer_trx_line_id := l_ctl_row.customer_trx_line_id;
1692 l_ctlgd_row.customer_trx_id := l_ct_row.customer_trx_id;
1693 l_ctlgd_row.posting_control_id := -3;
1694 l_ctlgd_row.gl_date := l_chargeback_gl_date;
1695 l_ctlgd_row.original_gl_date := l_chargeback_gl_date;
1696 l_ctlgd_row.account_class := 'REV';
1697 l_ctlgd_row.account_set_flag := 'N';
1698 l_ctlgd_row.latest_rec_flag := NULL;
1699 l_ctlgd_row.amount := p_amount;
1700 /* Bug 3879127 - acctd_amount is calculated using amount_due_remaining
1701 of original invoice instead of allowing it to default */
1702 IF p_app_customer_trx_id = -4
1703 THEN
1704 l_acctd_amount := p_acctd_amount;
1705 /* bug 4126057 set to passed acctd amt instead of null */
1706 ELSE
1707 arp_util.calc_acctd_amount(
1708 NULL
1709 , NULL
1710 , NULL
1711 , NVL(l_ct_row.exchange_rate,1) -- Exchange rate
1712 , '-' -- amount_applied must be subtracted from ADR
1713 , ln_amount_due_remaining -- Current ADR
1714 , ln_acctd_amount_due_remaining -- Current Acctd. ADR
1715 , p_amount -- Chargeback amount
1716 , ln_adr_tmp -- New ADR (OUT)
1717 , ln_acctd_adr_tmp -- New Acctd. ADR (OUT)
1718 , l_acctd_amount); -- Acct. amount
1719 -- (OUT)
1720 END IF;
1721 l_ctlgd_row.acctd_amount := l_acctd_amount;
1722 l_ctlgd_row.percent := 100;
1723 -- Copy the default values to distribution lines
1724 l_ctlgd_row.ussgl_transaction_code :=
1725 l_ct_row.default_ussgl_transaction_code;
1726 l_ctlgd_row.ussgl_transaction_code_context :=
1727 l_ct_row.default_ussgl_trx_code_context;
1728
1729 -- ---------------------------------------------------
1730 -- Get CB_ADJ activity accounting code combination
1731 -- ---------------------------------------------------
1732 BEGIN
1733 IF PG_DEBUG in ('Y', 'C') THEN
1734 arp_standard.debug( '-- Get CB_ADJ activity accounting code combination');
1735 END IF;
1736 SELECT rt.code_combination_id
1737 INTO l_cb_activity_ccid
1738 FROM ar_receivables_trx rt
1739 WHERE rt.receivables_trx_id = arp_global.G_CB_RT_ID
1740 AND rt.code_combination_id IS NOT NULL;
1741 EXCEPTION
1742 WHEN NO_DATA_FOUND THEN
1743 FND_MESSAGE.set_name('AR', 'AR_RW_NO_GL_ACCT' );
1744 app_exception.raise_exception;
1745 WHEN OTHERS THEN
1746 IF PG_DEBUG in ('Y', 'C') THEN
1747 arp_standard.debug( '-- Exception: select from ar_receivables_trx');
1748 END IF;
1749 END;
1750
1751 l_ctlgd_row.code_combination_id := l_cb_activity_ccid;
1752
1753 IF p_app_customer_trx_id = -4 THEN
1754 --Get CCID for receipt UNAPP
1755 arp_proc_rct_util.get_ccids(
1756 l_receipt_method_id,
1757 l_remit_bank_acct_use_id,
1758 l_id_dummy,
1759 l_cr_unapp_ccid,
1760 l_id_dummy,
1761 l_id_dummy,
1762 l_id_dummy,
1763 l_id_dummy,
1764 l_id_dummy,
1765 l_id_dummy,
1766 l_id_dummy,
1767 l_id_dummy);
1768
1769
1770 /* -------------------------------------------------------------------+
1771 | Balancing segment of chargeback application should be replaced with|
1772 | that of Receipt's UNAPP record |
1773 +--------------------------------------------------------------------*/
1774 -- Bugfix 1948917.
1775 IF NVL(FND_PROFILE.value('AR_DISABLE_REC_ACTIVITY_BALSEG_SUBSTITUTION'), 'N') = 'N' THEN
1776 arp_util.Substitute_Ccid(p_coa_id => arp_global.chart_of_accounts_id ,
1777 p_original_ccid => l_ctlgd_row.code_combination_id ,
1778 p_subs_ccid => l_cr_unapp_ccid ,
1779 p_actual_ccid => l_actual_ccid );
1780 ELSE
1781 l_actual_ccid := l_ctlgd_row.code_combination_id;
1782 END IF;
1783 ELSE
1784 -- Regular chargeback should replace the balancing segment of the invoice REC
1785 -- Bugfix 1948917.
1786 IF NVL(FND_PROFILE.value('AR_DISABLE_REC_ACTIVITY_BALSEG_SUBSTITUTION'), 'N') = 'N' THEN
1787 arp_util.Substitute_Ccid(p_coa_id => arp_global.chart_of_accounts_id ,
1788 p_original_ccid => l_ctlgd_row.code_combination_id ,
1789 p_subs_ccid => l_inv_rec_ccid ,
1790 p_actual_ccid => l_actual_ccid );
1791 ELSE
1792 l_actual_ccid := l_ctlgd_row.code_combination_id;
1793 END IF;
1794
1795
1796 END IF;
1797 l_ctlgd_row.code_combination_id := l_actual_ccid;
1798 -- ---------------------------------------------------
1799 -- Insert line into RA_CUST_TRX_LINE_GL_DIST table
1800 -- ---------------------------------------------------
1801
1802 arp_ctlgd_pkg.insert_p(
1803 -- IN
1804 p_dist_rec => l_ctlgd_row
1805 , p_exchange_rate => l_ct_row.exchange_rate
1806 , p_currency_code => arp_trx_global.system_info.base_currency
1807 , p_precision => arp_trx_global.system_info.base_precision
1808 , p_mau => arp_trx_global.system_info.base_min_acc_unit
1809 -- OUT
1810 , p_cust_trx_line_gl_dist_id
1811 => l_ctlgd_row.cust_trx_line_gl_dist_id
1812 );
1813
1814 -- ---------------------------------------------------
1815 -- Insert receivables account distribution
1816 -- ---------------------------------------------------
1817
1818 -- ---------------------------------------------------
1819 -- Set values for RA_CUST_TRX_LINE_GL_DIST table columns
1820 -- The receivables distributions should have an account class
1821 -- of 'REC'.
1822 -- The receivable account distributions records should
1823 -- not have a value in CUSTOMER_TRX_LINE_ID
1824 -- ---------------------------------------------------
1825
1826 l_ctlgd_row.customer_trx_line_id := NULL;
1827 l_ctlgd_row.customer_trx_id := l_ct_row.customer_trx_id;
1828 l_ctlgd_row.posting_control_id := -3;
1829 l_ctlgd_row.gl_date := l_chargeback_gl_date;
1830 l_ctlgd_row.original_gl_date := l_chargeback_gl_date;
1831 l_ctlgd_row.account_class := 'REC';
1832 l_ctlgd_row.account_set_flag := 'N';
1833 l_ctlgd_row.latest_rec_flag := 'Y';
1834 l_ctlgd_row.code_combination_id := p_gl_id_ar_trade;
1835 l_ctlgd_row.amount := p_amount;
1836 l_ctlgd_row.percent := 100;
1837 -- Copy the default values to distribution lines
1838 l_ctlgd_row.ussgl_transaction_code :=
1839 l_ct_row.default_ussgl_transaction_code;
1840 l_ctlgd_row.ussgl_transaction_code_context :=
1841 l_ct_row.default_ussgl_trx_code_context;
1842
1843 -- ---------------------------------------------------
1844 -- Insert line into RA_CUST_TRX_LINE_GL_DIST table
1845 -- ---------------------------------------------------
1846 arp_ctlgd_pkg.insert_p(
1847 -- IN
1848 p_dist_rec => l_ctlgd_row
1849 , p_exchange_rate => l_ct_row.exchange_rate
1850 , p_currency_code => arp_trx_global.system_info.base_currency
1851 , p_precision => arp_trx_global.system_info.base_precision
1852 , p_mau => arp_trx_global.system_info.base_min_acc_unit
1853 -- OUT
1854 , p_cust_trx_line_gl_dist_id
1855 => l_ctlgd_row.cust_trx_line_gl_dist_id
1856 );
1857
1858 -- ---------------------------------------------------
1859 -- Set values form AR_PAYMENT_SCHEDULES row.
1860 --
1861 -- Insert the payment schedule with the term_id of
1862 -- of '5' which refers to the 'IMMEDIATE' payment
1863 -- term of 100% with 0 due dates
1864 -- ---------------------------------------------------
1865
1866 l_ps_row.number_of_due_dates := 1;
1867 l_ps_row.status := 'OP';
1868 l_ps_row.class := 'CB';
1869 l_ps_row.cash_receipt_id := NULL;
1870 l_ps_row.term_id := 5;
1871 l_ps_row.terms_sequence_number := 1;
1872 l_ps_row.gl_date_closed := NULL;
1873 l_ps_row.actual_date_closed := NULL;
1874 l_ps_row.discount_date := NULL;
1875
1876 l_ps_row.due_date := p_due_date;
1877 l_ps_row.amount_due_original := p_amount;
1878 l_ps_row.amount_due_remaining := p_amount;
1879 l_ps_row.acctd_amount_due_remaining := l_acctd_amount; -- bug 3879127
1880 l_ps_row.invoice_currency_code := l_ct_row.invoice_currency_code;
1881 l_ps_row.gl_date := l_chargeback_gl_date;
1882 l_ps_row.cust_trx_type_id := p_cust_trx_type_id;
1883 l_ps_row.customer_id := p_customer_id;
1884 l_ps_row.customer_site_use_id := l_ct_row.bill_to_site_use_id;
1885 l_ps_row.customer_trx_id := l_ct_row.customer_trx_id;
1886 l_ps_row.associated_cash_receipt_id := p_cash_receipt_id;
1887 l_ps_row.amount_line_items_original := p_amount;
1888 l_ps_row.amount_line_items_remaining := p_amount;
1889 l_ps_row.exchange_rate_type := l_ct_row.exchange_rate_type;
1890 l_ps_row.exchange_date := l_ct_row.exchange_date;
1891 l_ps_row.exchange_rate := l_ct_row.exchange_rate;
1892 l_ps_row.trx_number := l_ct_row.trx_number;
1893 l_ps_row.trx_date := TRUNC(l_ct_row.trx_date); /* Bug 5758906 */
1894
1895 -- ---------------------------------------------------
1896 -- Insert row into AR_PAYMENT_SCHEDULES table
1897 -- ---------------------------------------------------
1898 arp_ps_pkg.insert_p(
1899 p_ps_rec => l_ps_row -- IN
1900 , p_ps_id => l_ps_row.payment_schedule_id -- OUT
1901 );
1902
1903 -- Clear out l_ps_row.payment_schedule_id, because it would
1904 -- cause chargeback PS row to be updated in
1905 -- arp_ps_util.update_invoice_related_columms
1906 l_ps_row.payment_schedule_id := NULL;
1907
1908 ln_line_applied := p_amount;
1909 ln_tax_applied := 0;
1910 ln_freight_applied := 0;
1911 ln_charges_applied := 0;
1912 ln_acctd_amount_applied := 0;
1913 ln_acctd_earned_discount_taken := 0;
1914 ln_acctd_unearned_disc_taken := 0;
1915
1916
1917 /*----------------------------------------------
1918 Calling ARP_XLA_EVENTS to create the CB_CREATE
1919 ----------------------------------------------*/
1920 -- BUG#2750340 : Call AR_XLA_EVENTS
1921 l_ev_rec.xla_from_doc_id := l_ct_row.customer_trx_id;
1922 l_ev_rec.xla_to_doc_id := l_ct_row.customer_trx_id;
1923 l_ev_rec.xla_req_id := NULL;
1924 l_ev_rec.xla_dist_id := NULL;
1925 l_ev_rec.xla_doc_table := 'CT';
1926 l_ev_rec.xla_doc_event := NULL;
1927 l_ev_rec.xla_mode := 'O';
1928 l_ev_rec.xla_call := 'B';
1929 arp_xla_events.create_events(p_xla_ev_rec => l_ev_rec );
1930
1931 /*
1932 -- ---------------------------------------------------
1933 -- Updates ar_payment_schedules
1934 -- ---------------------------------------------------
1935 arp_ps_util.update_invoice_related_columns(
1936 -- IN
1937 p_app_type => 'CB'
1938 , p_ps_id => p_app_customer_trx_id
1939 , p_amount_applied => p_amount
1940 , p_discount_taken_earned => 0
1941 , p_discount_taken_unearned => 0
1942 , p_apply_date => p_apply_date
1943 , p_gl_date => l_chargeback_gl_date
1944 -- OUT
1945 , p_acctd_amount_applied => ln_acctd_amount_applied
1946 , p_acctd_earned_discount_taken => ln_acctd_earned_discount_taken
1947 , p_acctd_unearned_disc_taken => ln_acctd_unearned_disc_taken
1948 , p_line_applied => ln_line_applied
1949 , p_tax_applied => ln_tax_applied
1950 , p_freight_applied => ln_freight_applied
1951 , p_charges_applied => ln_charges_applied
1952 , p_ps_rec => l_ps_row);
1953 */
1954
1955
1956 -- ---------------------------------------------------
1957 -- Set values for AR_ADJUSTMENTS columns
1958 -- ---------------------------------------------------
1959
1960 -- Caroline M Clyde December 23, 1997
1961 -- Log 597194
1962 --
1963 -- Added the SELECT statement below to retrieve the Receivables Activity
1964 -- Account. The adjustment should be created with this account and not
1965 -- the Receivables Account defined on the Transaction Type.
1966 --
1967 -- The Receivables Activity for id -11 is the 'Chargeback Adjustment'
1968 -- activity.
1969
1970 --snambiar - for receipt chargeback,there is no need to create any
1971 --adjustments. Hence making adjustment creation conditional for
1972 --p_app_customer_trx_id -4
1973
1974 IF (p_app_customer_trx_id <> -4) THEN
1975
1976 -- Identifier of customer transaction associated with this adjustment
1977 -- Store the adjusted invoice ID
1978 l_adj_row.code_combination_id := l_actual_ccid;
1979 l_adj_row.customer_trx_id := p_app_customer_trx_id;
1980 l_adj_row.payment_schedule_id := ln_app_payment_schedule_id;
1981 l_adj_row.receivables_trx_id := arp_global.G_CB_RT_ID;
1982 l_adj_row.postable := 'Y';
1983
1984 -- This type is just temporary 'CB' type in order to get
1985 -- update_adj_related_columns procedure work properly with
1986 -- chargebacks. If this were 'INVOICE', it would not work because
1987 -- line adjustment is hard coded to be the full amount of
1988 -- line_items_amount_due_remaining.
1989 l_adj_row.type := 'CB';
1990
1991 l_adj_row.adjustment_type := 'M';
1992 l_adj_row.associated_cash_receipt_id := p_cash_receipt_id;
1993
1994 -- Identifier of chargeback transaction associated with this adjustment
1995 -- Store the corresponding Chargeback ID
1996 l_adj_row.chargeback_customer_trx_id := l_ct_row.customer_trx_id;
1997 l_adj_row.created_from := p_form_name;
1998 l_adj_row.line_adjusted := (ln_line_applied * -1);
1999 l_adj_row.tax_adjusted := (ln_tax_applied * -1);
2000 l_adj_row.freight_adjusted := (ln_freight_applied * -1);
2001 l_adj_row.receivables_charges_adjusted:= (ln_charges_applied * -1);
2002
2003 l_adj_row.gl_date := TRUNC(l_chargeback_gl_date);
2004 l_adj_row.apply_date := TRUNC(p_apply_date);
2005 l_adj_row.amount := (p_amount * -1);
2006 l_adj_row.acctd_amount := (p_acctd_amount * -1);
2007
2008 -- Use the old information from Chargeback to populate rest of columns
2009 l_adj_row.subsequent_trx_id := 0;
2010 l_adj_row.customer_trx_line_id := 0;
2011 l_adj_row.status := 'A';
2012 l_adj_row.automatically_generated := 'A';
2013 l_adj_row.posting_control_id := -3;
2014 l_adj_row.ussgl_transaction_code :=
2015 l_ct_row.default_ussgl_transaction_code;
2016 l_adj_row.ussgl_transaction_code_context :=
2017 l_ct_row.default_ussgl_trx_code_context;
2018 l_adj_row.reason_code := p_reason_code;
2019 l_adj_row.comments := p_comments;
2020
2021 -- -----------------------------------------------------------------
2022 -- Get document numbers only if customer is using document numbering
2023 -- -----------------------------------------------------------------
2024 -- Profile option values:
2025 -- 'A' = always used
2026 -- 'P' = Partially Used
2027 -- 'N' = not used
2028 IF (NVL(fnd_profile.value('UNIQUE:SEQ_NUMBERS'),'N') <> 'N')
2029 THEN
2030 -- Set up sequential numbering stuff
2031 SELECT rt.name
2032 INTO l_rec_name
2033 FROM ar_receivables_trx rt
2034 WHERE rt.receivables_trx_id = arp_global.G_CB_RT_ID;
2035
2036
2037 -- Bug 686025/694300: instead of calling GET_SEQ_NAME, use
2038 -- proper AOL API GET_NEXT_SEQUENCE to get sequence number.
2039
2040 /*
2041 FND_SEQNUM.GET_SEQ_NAME(
2042 arp_standard.application_id
2043 , l_rec_name -- category code
2044 , arp_global.set_of_books_id
2045 , 'A'
2046 , l_adj_row.apply_date
2047 , l_sequence_name
2048 , l_sequence_id
2049 , l_sequence_assignment_id);
2050
2051 l_adj_row.doc_sequence_value :=
2052 fnd_seqnum.get_next_auto_seq(l_sequence_name);
2053 l_adj_row.doc_sequence_id := l_sequence_id;
2054
2055 */
2056
2057 BEGIN
2058
2059 l_adj_row.doc_sequence_value :=
2060 FND_SEQNUM.GET_NEXT_SEQUENCE(
2061 appid => arp_standard.application_id,
2062 cat_code => l_rec_name,
2063 sobid => arp_global.set_of_books_id,
2064 met_code => 'A',
2065 trx_date => l_adj_row.apply_date,
2066 dbseqnm => l_sequence_name,
2067 dbseqid => l_adj_row.doc_sequence_id);
2068
2069 IF PG_DEBUG in ('Y', 'C') THEN
2070 arp_standard.debug( 'doc sequence name = ' || l_sequence_name);
2071 arp_standard.debug( 'doc sequence id = ' || l_adj_row.doc_sequence_id);
2072 arp_standard.debug( 'doc sequence value = ' || l_adj_row.doc_sequence_value);
2073 END IF;
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 --Fix for Bug 1421614: For 'Partial' we should not raise the exception.
2077 IF NVL(fnd_profile.value('UNIQUE:SEQ_NUMBERS'),'N') = 'A' THEN
2078 FND_MESSAGE.set_name ('AR', 'AR_RW_NO_DOC_SEQ' );
2079 APP_EXCEPTION.raise_exception;
2080 END IF;
2081 END;
2082
2083 ELSE
2084 l_adj_row.doc_sequence_value := NULL;
2085 l_adj_row.doc_sequence_id := NULL;
2086 END IF;
2087
2088 -- ---------------------------------------------------
2089 -- Create adjustment against old invoice
2090 -- ---------------------------------------------------
2091 arp_process_adjustment.insert_adjustment(
2092 -- IN
2093 p_form_name => p_form_name
2094 , p_form_version => 1
2095 , p_adj_rec => l_adj_row
2096 -- OUT
2097 , p_adjustment_number => l_adj_row.adjustment_number
2098 , p_adjustment_id => l_adj_row.adjustment_id
2099 );
2100
2101 -- ---------------------------------------------------
2102 -- ---------------------------------------------------
2103 END IF; --for transaction adjustments not required for p_app_customer_trx_id -4
2104 --Ajay : chargeback creation business event related stuff
2105 AR_BUS_EVENT_COVER.Raise_Trx_Creation_Event
2106 ('CB',
2107 l_ct_row.customer_trx_id,
2108 l_prev_cust_old_state);
2109
2110 EXCEPTION
2111 WHEN OTHERS THEN
2112 IF PG_DEBUG in ('Y', 'C') THEN
2113 arp_standard.debug ('ARP_PROCESS_CHARGEBACK.CREATE_CHARGEBACK: Exception');
2114 arp_standard.debug ( 'Printing the contents of procedure parameters:');
2115 END IF;
2116 -- Print debug info
2117 IF PG_DEBUG in ('Y', 'C') THEN
2118 arp_standard.debug ( ' p_amount = '||TO_CHAR( p_amount ));
2119 arp_standard.debug ( ' p_acctd_amount = '||TO_CHAR( p_acctd_amount ));
2120 arp_standard.debug ( ' p_trx_date = '||TO_CHAR( p_trx_date ));
2121 arp_standard.debug ( ' p_gl_id_ar_trade = '||TO_CHAR( p_gl_id_ar_trade ));
2122 arp_standard.debug ( ' p_gl_date = '||TO_CHAR( l_chargeback_gl_date ));
2123 arp_standard.debug ( ' p_attribute_category = '||p_attribute_category );
2124 arp_standard.debug ( ' p_attribute1 = '||p_attribute1 );
2125 arp_standard.debug ( ' p_attribute2 = '||p_attribute2 );
2126 arp_standard.debug ( ' p_attribute3 = '||p_attribute3 );
2127 arp_standard.debug ( ' p_attribute4 = '||p_attribute4 );
2128 arp_standard.debug ( ' p_attribute5 = '||p_attribute5 );
2129 arp_standard.debug ( ' p_attribute6 = '||p_attribute6 );
2130 arp_standard.debug ( ' p_attribute7 = '||p_attribute7 );
2131 arp_standard.debug ( ' p_attribute8 = '||p_attribute8 );
2132 arp_standard.debug ( ' p_attribute9 = '||p_attribute9 );
2133 arp_standard.debug ( ' p_attribute10 = '||p_attribute10 );
2134 arp_standard.debug ( ' p_attribute11 = '||p_attribute11 );
2135 arp_standard.debug ( ' p_attribute12 = '||p_attribute12 );
2136 arp_standard.debug ( ' p_attribute13 = '||p_attribute13 );
2137 arp_standard.debug ( ' p_attribute14 = '||p_attribute14 );
2138 arp_standard.debug ( ' p_attribute15 = '||p_attribute15 );
2139 arp_standard.debug ( ' p_cust_trx_type_id = '||TO_CHAR( p_cust_trx_type_id ));
2140 arp_standard.debug ( ' p_set_of_books_id = '||TO_CHAR( p_set_of_books_id ));
2141 arp_standard.debug ( ' p_reason_code = '||p_reason_code );
2142 arp_standard.debug ( ' p_comments = '||p_comments );
2143 arp_standard.debug ( ' p_def_ussgl_trx_code_contex = '||p_def_ussgl_trx_code_context);
2144 arp_standard.debug ( ' p_def_ussgl_transaction_cod = '||p_def_ussgl_transaction_code);
2145 arp_standard.debug ( ' p_due_date = '||TO_CHAR( p_due_date ));
2146 arp_standard.debug ( ' p_customer_id = '||TO_CHAR( p_customer_id ));
2147 arp_standard.debug ( ' p_cr_trx_number = '||p_cr_trx_number );
2148 arp_standard.debug ( ' p_cash_receipt_id = '||TO_CHAR( p_cash_receipt_id ));
2149 arp_standard.debug ( ' p_inv_trx_number = '||p_inv_trx_number );
2150 arp_standard.debug ( ' p_apply_date = '||TO_CHAR( p_apply_date ));
2151 arp_standard.debug ( ' p_receipt_gl_date = '||TO_CHAR( p_receipt_gl_date ));
2152 arp_standard.debug ( ' p_app_customer_trx_id = '||TO_CHAR( p_app_customer_trx_id ));
2153 arp_standard.debug ( ' p_app_terms_sequence_number = '||TO_CHAR( p_app_terms_sequence_number ));
2154 arp_standard.debug ( ' p_form_name = '||p_form_name );
2155 arp_standard.debug ( ' p_doc_sequence_value = '||TO_CHAR( p_doc_sequence_value));
2156 arp_standard.debug ( ' p_doc_sequence_id = '||TO_CHAR( p_doc_sequence_id));
2157 END IF;
2158
2159 RAISE;
2160
2161 IF PG_DEBUG in ('Y', 'C') THEN
2162 arp_standard.debug('arp_process_chargeback.create_chargeback()-');
2163 END IF;
2164
2165 END create_chargeback;
2166
2167
2168 /*
2169 ===========================================================================+
2170 PROCEDURE
2171 update_chargeback()
2172
2173 DESCRIPTION
2174 This procedure will update chargeback comments and reason code.
2175 These are the only allowed updateable columns.
2176
2177 SCOPE
2178 Public
2179
2180 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2181
2182 ARGUMENTS
2183 IN
2184 p_customer_trx_id
2185 p_comments
2186 p_reason_code
2187
2188 OUT
2189 NONE
2190
2191 RETURNS
2192
2193 NOTES
2194
2195 MODIFICATION HISTORY
2196 4/15/1996 Harri Kaukovuo Created
2197 +===========================================================================
2198 */
2199
2200 PROCEDURE update_chargeback (
2201 p_customer_trx_id IN NUMBER
2202 , p_comments IN VARCHAR2
2203 , p_DEFAULT_USSGL_TRX_CODE IN VARCHAR2
2204 , p_reason_code IN VARCHAR2
2205 , p_ATTRIBUTE_CATEGORY IN VARCHAR2
2206 , p_attribute1 IN VARCHAR2
2207 , p_attribute2 IN VARCHAR2
2208 , p_attribute3 IN VARCHAR2
2209 , p_attribute4 IN VARCHAR2
2210 , p_attribute5 IN VARCHAR2
2211 , p_attribute6 IN VARCHAR2
2212 , p_attribute7 IN VARCHAR2
2213 , p_attribute8 IN VARCHAR2
2214 , p_attribute9 IN VARCHAR2
2215 , p_attribute10 IN VARCHAR2
2216 , p_attribute11 IN VARCHAR2
2217 , p_attribute12 IN VARCHAR2
2218 , p_attribute13 IN VARCHAR2
2219 , p_attribute14 IN VARCHAR2
2220 , p_attribute15 IN VARCHAR2) IS
2221
2222 l_trx_rec RA_CUSTOMER_TRX%ROWTYPE;
2223
2224 BEGIN
2225
2226 IF PG_DEBUG in ('Y', 'C') THEN
2227 arp_standard.debug ('arp_process_chargeback.update_chargeback()+');
2228 END IF;
2229
2230 -- ---------------------------------------------------
2231 -- First set all record parameters to dummy
2232 -- ---------------------------------------------------
2233 arp_ct_pkg.set_to_dummy (
2234 l_trx_rec);
2235
2236 l_trx_rec.customer_trx_id := p_customer_trx_id;
2237 l_trx_rec.comments := p_comments;
2238 l_trx_rec.reason_code := p_reason_code;
2239 l_trx_rec.DEFAULT_USSGL_TRANSACTION_CODE := p_DEFAULT_USSGL_TRX_CODE;
2240 l_trx_rec.attribute_category := p_attribute_category;
2241 l_trx_rec.attribute1 := p_attribute1;
2242 l_trx_rec.attribute2 := p_attribute2;
2243 l_trx_rec.attribute3 := p_attribute3;
2244 l_trx_rec.attribute4 := p_attribute4;
2245 l_trx_rec.attribute5 := p_attribute5;
2246 l_trx_rec.attribute6 := p_attribute6;
2247 l_trx_rec.attribute7 := p_attribute7;
2248 l_trx_rec.attribute8 := p_attribute8;
2249 l_trx_rec.attribute9 := p_attribute9;
2250 l_trx_rec.attribute10 := p_attribute10;
2251 l_trx_rec.attribute11 := p_attribute11;
2252 l_trx_rec.attribute12 := p_attribute12;
2253 l_trx_rec.attribute13 := p_attribute13;
2254 l_trx_rec.attribute14 := p_attribute14;
2255 l_trx_rec.attribute15 := p_attribute15;
2256
2257 -- ---------------------------------------------------
2258 -- Update
2259 -- ---------------------------------------------------
2260 arp_ct_pkg.update_p (
2261 l_trx_rec
2262 , p_customer_trx_id);
2263
2264 IF PG_DEBUG in ('Y', 'C') THEN
2265 arp_standard.debug ('arp_process_chargeback.update_chargeback()-');
2266 END IF;
2267
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 IF PG_DEBUG in ('Y', 'C') THEN
2271 arp_standard.debug ('EXCEPTION: arp_process_chargeback.update_chargeback()');
2272 arp_standard.debug ( 'ERROR in the program! Dump of parameter values:');
2273 arp_standard.debug ( ' p_customer_trx_id = '||TO_CHAR(p_customer_trx_id));
2274 arp_standard.debug ( ' p_comments = '||p_comments);
2275 arp_standard.debug ( ' p_DEFAULT_USSGL_TRX_CODE = '||p_DEFAULT_USSGL_TRX_CODE);
2276 arp_standard.debug ( ' p_reason_code = '||p_reason_code);
2277 END IF;
2278 RAISE;
2279 END update_chargeback;
2280
2281 /*===========================================================================+
2282 | PROCEDURE
2283 | delete_chargeback
2284 |
2285 | DESCRIPTION
2286 | This procedure deletes chargeback from database.
2287 |
2288 | SCOPE - PUBLIC
2289 |
2290 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2291 |
2292 | ARGUMENTS : IN:
2293 | OUT:
2294 |
2295 | RETURNS : NONE
2296 |
2297 | NOTES
2298 | This procedure is originally converted from C -procedure arxdcb
2299 | which is in file arxdcb.lpc
2300 |
2301 | Algorithm:
2302 | 1. Process parameters
2303 | 2. Call reverse_chargeback to insert adj and update payment schedule
2304 | of CB
2305 | 3. Call arp_process_adjustment.reverse_adjustment to insert
2306 | an opposing adjustment for the CB-ADJ
2307 | 4. Call xxxx to get max GL date and apply date
2308 | 5. Call xxx to update payment schedule of the debit item.
2309 |
2310 | MODIFICATION HISTORY
2311 | 4/15/1996 Harri Kaukovuo Created
2312 | 03/23/2001 S.Nambiar Modified delete_chargeback procedure
2313 | to incorporate the new functionality
2314 | to create Chargeback aganist receipts.
2315 | In case of receipt CB, p_type will be
2316 | "RECEIPT" in all other cases it will be
2317 | defaulted to "TRANSACTION"
2318 +===========================================================================*/
2319
2320 PROCEDURE delete_chargeback (
2321 p_customer_trx_id IN NUMBER
2322 , p_apply_date IN DATE
2323 , p_gl_date IN DATE
2324 , p_module_name IN VARCHAR2
2325 , p_module_version IN VARCHAR2
2326 , p_type IN VARCHAR2) IS
2327
2328 ln_adj_id NUMBER;
2329 ln_adj_amt NUMBER;
2330 ln_acctd_adj_amt NUMBER;
2331 ln_line_adj NUMBER;
2332 ln_tax_adj NUMBER;
2333 ln_frt_adj NUMBER;
2334 ln_chrg_adj NUMBER;
2335 lc_status VARCHAR2(40);
2336 l_ps_rec ar_payment_schedules%ROWTYPE;
2337
2338 l_out_line_adjusted NUMBER;
2339 l_out_tax_adjusted NUMBER;
2340 l_out_freight_adjusted NUMBER;
2341 l_out_charges_adjusted NUMBER;
2342 l_out_acctd_amount_adjusted NUMBER;
2343 /* New variables defined for bug 2399863 */
2344 l_line_adjusted NUMBER;
2345 l_tax_adjusted NUMBER;
2346 l_frt_adjusted NUMBER;
2347 l_charges_adjusted NUMBER;
2348
2349 BEGIN
2350
2351 arp_process_chargeback.reverse_chargeback (
2352 p_cb_ct_id => p_customer_trx_id
2353 , p_reversal_gl_date => p_gl_date
2354 , p_reversal_date => p_apply_date
2355 , p_module_name => p_module_name
2356 , p_module_version => p_module_version
2357 , p_type => p_type
2358 );
2359
2360 arp_ps_pkg.set_to_dummy (l_ps_rec);
2361
2362 IF p_type <> 'TRANSACTION' THEN
2363 --snambiar. For receipt chargeback,we will pass p_type as "RECEIPT"
2364 --and in all other cases,p_type is defaulted to "TRANSACTION"
2365 --Since we do not have an adjustment created for receipt chargeback,
2366 --we do not need to reverse the adjustment.
2367 NULL;
2368 ELSE
2369 --chargeback aganist Invoice,CM
2370 BEGIN
2371 SELECT
2372 adj.adjustment_id
2373 , adj.payment_schedule_id
2374 , adj.amount
2375 , adj.acctd_amount
2376 , NVL(adj.line_adjusted,0)
2377 , NVL(adj.tax_adjusted,0)
2378 , NVL(adj.freight_adjusted,0)
2379 , NVL(adj.receivables_charges_adjusted,0)
2380 , adj.status
2381 INTO
2382 ln_adj_id
2383 , l_ps_rec.payment_schedule_id
2384 , ln_adj_amt
2385 , ln_acctd_adj_amt
2386 , ln_line_adj
2387 , ln_tax_adj
2388 , ln_frt_adj
2389 , ln_chrg_adj
2390 , lc_status
2391 FROM ar_adjustments adj
2392 WHERE adj.chargeback_customer_trx_id = p_customer_trx_id
2393 AND adj.receivables_trx_id = -11;
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 RAISE;
2398 END;
2399
2400 arp_process_adjustment.reverse_adjustment (
2401 P_ADJ_ID => ln_adj_id
2402 , P_REVERSAL_GL_DATE => p_gl_date
2403 , P_REVERSAL_DATE => p_apply_date
2404 , P_MODULE_NAME => p_module_name
2405 , P_MODULE_VERSION => p_module_version);
2406
2407 arp_ps_util.get_closed_dates(
2408 p_ps_id => l_ps_rec.payment_schedule_id
2409 , p_gl_reversal_date => p_gl_date
2410 , p_reversal_date => p_apply_date
2411 , p_gl_date_closed => l_ps_rec.gl_date_closed -- OUT
2412 , p_actual_date_closed => l_ps_rec.actual_date_closed -- OUT
2413 , p_app_type => 'ADJ');
2414
2415 /* Bug 2399863
2416 For reversing the chargeback, pass the p_type as CBREV and
2417 pass the values for line, tax, frt and charges adjusted */
2418 l_line_adjusted := -ln_line_adj;
2419 l_tax_adjusted := -ln_tax_adj;
2420 l_frt_adjusted := -ln_frt_adj;
2421 l_charges_adjusted := -ln_chrg_adj;
2422 arp_ps_util.update_adj_related_columns (
2423 -- IN
2424 p_ps_id => l_ps_rec.payment_schedule_id
2425 , p_type => 'CBREV'
2426 , p_amount_adjusted => -ln_adj_amt
2427 , p_amount_adjusted_pending => 0
2428 , p_apply_date => p_apply_date
2429 , p_gl_date => p_gl_date
2430 , p_ps_rec => NULL_VAR /*Bug 460966 - Oracle 8 */
2431 -- OUT
2432 , p_line_adjusted => l_line_adjusted
2433 , p_tax_adjusted => l_tax_adjusted
2434 , p_freight_adjusted => l_frt_adjusted
2435 , p_charges_adjusted => l_charges_adjusted
2436 , p_acctd_amount_adjusted => l_out_acctd_amount_adjusted
2437 );
2438 l_out_line_adjusted := l_line_adjusted;
2439 l_out_tax_adjusted := l_tax_adjusted;
2440 l_out_freight_adjusted := l_frt_adjusted;
2441 l_out_charges_adjusted := l_charges_adjusted;
2442 END IF;
2443 EXCEPTION
2444 WHEN OTHERS THEN
2445 IF PG_DEBUG in ('Y', 'C') THEN
2446 arp_standard.debug ('EXCEPTION: arp_process_chargeback.delete_chargeback()');
2447 arp_standard.debug ('delete_chargeback: ' || 'ERROR in the program! Dump of parameter values:');
2448 arp_standard.debug ('delete_chargeback: ' || ' p_customer_trx_id = '|| TO_CHAR(p_customer_trx_id));
2449 arp_standard.debug ('delete_chargeback: ' || ' p_gl_date = '|| TO_CHAR(p_gl_date));
2450 arp_standard.debug ('delete_chargeback: ' || ' p_apply_date = '|| TO_CHAR(p_apply_date));
2451 arp_standard.debug ('delete_chargeback: ' || ' p_module_name = '|| p_module_name);
2452 arp_standard.debug ('delete_chargeback: ' || ' p_module_version = '|| p_module_version);
2453 END IF;
2454 RAISE;
2455
2456 END delete_chargeback;
2457
2458
2459 END ARP_PROCESS_CHARGEBACK;