[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_CREDIT
Source
1 PACKAGE BODY ARP_PROCESS_CREDIT AS
2 /* $Header: ARTECMRB.pls 120.29 2011/05/10 12:51:54 dgaurab ship $ */
3
4 pg_number_dummy number;
5 pg_date_dummy date;
6 pg_name_dummy varchar2(30);
7 pg_flag_dummy varchar2(1);
8 pg_earliest_date date;
9
10 pg_salesrep_required_flag ar_system_parameters.salesrep_required_flag%type;
11 pg_base_curr_code fnd_currencies.currency_code%type;
12 pg_base_precision fnd_currencies.precision%type;
13 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
14
15 -- TYPE credit_lines_type IS TABLE OF
16 -- ra_customer_trx_lines.customer_trx_line_id%type
17 -- INDEX BY BINARY_INTEGER;
18 --
19 -- pg_num_credit_lines number;
20 -- pg_credit_lines credit_lines_type;
21
22 /*===========================================================================+
23 | PROCEDURE |
24 | create_salescredits |
25 | |
26 | DESCRIPTION |
27 | Inserts salescredit records into RA_CUST_TRX_LINE_SALESREPS |
28 | - copies from the credited transaction line, if any |
29 | OR - inserts a row based on the primary salesperson specified on |
30 | the transaction |
31 | |
32 | SCOPE - PRIVATE |
33 | |
34 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
35 | arp_util.debug |
36 | arp_ctls_pkg.insert_f_cm_ct_ctl_id |
37 | |
38 | ARGUMENTS : IN: |
39 | p_customer_trx_id |
40 | p_customer_trx_line_id |
41 | p_memo_line_type |
42 | p_primary_salesrep_id |
43 | p_currency_code |
44 | OUT: |
45 | IN/ OUT: |
46 | |
47 | RETURNS : NONE |
48 | |
49 | NOTES |
50 | |
51 | MODIFICATION HISTORY |
52 | 11-AUG-95 Subash Chadalavada Created |
53 | |
54 +===========================================================================*/
55
56 PROCEDURE create_salescredits(
57 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
58 p_customer_trx_line_id IN
59 ra_customer_trx_lines.customer_trx_line_id%type,
60 p_memo_line_type IN ar_memo_lines.line_type%type,
61 p_primary_salesrep_id IN ra_salesreps.salesrep_id%type,
62 p_currency_code IN fnd_currencies.currency_code%type)
63 IS
64 l_line_salesrep_flag NUMBER := 0;
65 BEGIN
66
67 arp_util.debug('arp_process_credit.create_salescredits()+');
68
69 --Bug 12376682
70 SELECT count(*)
71 INTO l_line_salesrep_flag
72 FROM ra_cust_trx_line_salesreps sales,
73 ra_customer_trx_lines ctl
74 WHERE ctl.previous_customer_trx_id = sales.customer_trx_id
75 AND ctl.previous_customer_trx_line_id = sales.customer_trx_line_id
76 AND ctl.customer_trx_id = p_customer_trx_id
77 AND ctl.line_type = 'LINE'
78 AND ctl.customer_trx_line_id = nvl(p_customer_trx_line_id, ctl.customer_trx_line_id)
79 AND sales.salesrep_id <> -3;
80
81 IF (p_customer_trx_line_id IS NULL)
82 THEN
83
84 /*------------------------------------+
85 | Create salescredits for the CM. |
86 +------------------------------------*/
87 IF (( p_primary_salesrep_id IS NOT NULL)
88 AND
89 ( p_primary_salesrep_id <> -3
90 OR
91 pg_salesrep_required_flag = 'Y')
92 )
93 OR
94 l_line_salesrep_flag <> 0
95 THEN
96 arp_ctls_pkg.insert_f_cm_ct_ctl_id(p_customer_trx_id,
97 p_customer_trx_line_id,
98 p_currency_code);
99 END IF;
100 ELSIF (p_memo_line_type <> 'CHARGES') THEN
101
102 /*--------------------------------------+
103 | Charges do not have salescredits. |
104 | If this is a charges memo line, |
105 | then don't do any processing. |
106 +--------------------------------------*/
107
108 IF (( p_primary_salesrep_id IS NOT NULL)
109 AND
110 ( p_primary_salesrep_id <> -3
111 OR
112 pg_salesrep_required_flag = 'Y')
113 )
114 OR
115 l_line_salesrep_flag <> 0
116 THEN
117 arp_ctls_pkg.insert_f_cm_ct_ctl_id(p_customer_trx_id,
118 p_customer_trx_line_id,
119 p_currency_code);
120 END IF;
121 END IF;
122
123 arp_util.debug('arp_process_credit.create_salescredits()-');
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 arp_util.debug('EXCEPTION: arp_process_credit.create_salescredits()');
128 arp_util.debug('');
129 arp_util.debug('p_customer_trx_id = '||p_customer_trx_id);
130 arp_util.debug('p_customer_trx_line_id = '||p_customer_trx_line_id);
131 arp_util.debug('p_memo_line_type = '||p_memo_line_type);
132 arp_util.debug('p_primary_salesrep_id = '||p_primary_salesrep_id);
133 arp_util.debug('p_currency_code = '||p_currency_code);
134
135 RAISE;
136 END;
137
138 /*===========================================================================+
139 | PROCEDURE |
140 | credit_freight |
141 | |
142 | DESCRIPTION |
143 | create or updates freight lines for a credit memo |
144 | |
145 | SCOPE - PRIVATE |
146 | |
147 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
148 | arp_util.debug |
149 | |
150 | ARGUMENTS : IN: |
151 | p_form_name |
152 | p_form_version |
153 | p_credit_rec |
154 | p_trx_class |
155 | p_gl_date |
156 | p_credit_ccid |
157 | OUT: |
158 | p_customer_trx_line_id |
159 | IN/ OUT: |
160 | |
161 | RETURNS : NONE |
162 | |
163 | NOTES |
164 | |
165 | MODIFICATION HISTORY |
166 | 11-AUG-95 Subash Chadalavada Created |
167 | |
168 +===========================================================================*/
169
170 PROCEDURE credit_freight (
171 p_form_name IN varchar2,
172 p_form_version IN number,
173 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
174 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
175 p_prev_ct_id IN ra_customer_trx.customer_trx_id%type,
176 p_prev_ctl_id IN ra_customer_trx_lines.customer_trx_line_id%type,
177 p_credit_freight_amount IN ra_customer_trx_lines.extended_amount%type,
178 p_uncr_freight_amount IN ra_customer_trx_lines.extended_amount%type,
179 p_freight_type IN varchar2,
180 p_freight_ctlid IN ra_customer_trx_lines.customer_trx_line_id%type,
181 p_mode IN varchar2,
182 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
183 p_currency_code IN fnd_currencies.currency_code%type,
184 p_status OUT NOCOPY varchar2)
185 IS
186 l_credited_frt_ctlid ra_customer_trx.customer_trx_id%type;
187 l_freight_ctlid ra_customer_trx_lines.customer_trx_line_id%type;
188 l_sob_id ra_customer_trx_lines.set_of_books_id%type;
189 l_frt_rec ra_customer_trx_lines%rowtype;
190 BEGIN
191
192 arp_util.debug('arp_process_credit.credit_freight()+');
193
194 IF ( p_prev_ct_id IS NULL)
195 OR
196 ( p_prev_ct_id IS NOT NULL
197 AND
198 ( p_freight_type = 'H'
199 OR
200 ( p_freight_type = 'L'
201 AND
202 p_customer_trx_line_id IS NOT NULL
203 )
204 )
205 )
206 THEN
207
208 --
209 -- on-account CM case / regular CM with header freight /
210 -- regular CM and called at LINE level
211 --
212
213 IF (p_mode = 'INSERT')
214 THEN
215
216 l_frt_rec.customer_trx_id := p_customer_trx_id;
217 l_frt_rec.link_to_cust_trx_line_id := p_customer_trx_line_id;
218 l_frt_rec.previous_customer_trx_id := p_prev_ct_id;
219 l_frt_rec.previous_customer_trx_line_id := p_freight_ctlid;
220 l_frt_rec.line_type := 'FREIGHT';
221 l_frt_rec.line_number := 1;
222 l_frt_rec.extended_amount := p_credit_freight_amount;
223 l_frt_rec.revenue_amount := p_credit_freight_amount;
224
225 arp_process_freight.insert_freight(
226 p_form_name,
227 p_form_version,
228 l_frt_rec,
229 'CM',
230 p_gl_date,
231 null,
232 l_freight_ctlid,
233 p_status);
234
235 ELSIF (p_mode = 'UPDATE')
236 THEN
237
238 arp_ctl_pkg.set_to_dummy(l_frt_rec);
239
240 l_frt_rec.customer_trx_id := p_customer_trx_id;
241 l_frt_rec.link_to_cust_trx_line_id := p_customer_trx_line_id;
242 l_frt_rec.previous_customer_trx_line_id := p_freight_ctlid;
243 l_frt_rec.line_type := 'FREIGHT';
244 l_frt_rec.line_number := 1;
245 l_frt_rec.extended_amount := p_credit_freight_amount;
246 l_frt_rec.revenue_amount := p_credit_freight_amount;
247
248 arp_process_freight.update_freight(
249 p_form_name,
250 p_form_version,
251 p_customer_trx_id,
252 p_freight_ctlid,
253 l_frt_rec,
254 'CM',
255 p_gl_date,
256 null,
257 null,
258 null,
259 p_status);
260 ELSIF (p_mode = 'DELETE')
261 THEN
262 arp_ctlgd_pkg.delete_f_ct_ltctl_id_type(
263 p_customer_trx_id,
264 p_customer_trx_line_id,
265 'FREIGHT',
266 null,
267 null);
268
269 arp_ctl_pkg.delete_f_ct_ltctl_id_type(
270 p_customer_trx_id,
271 p_customer_trx_line_id,
272 'FREIGHT');
273 END IF;
274
275 ELSE
276
277 IF (p_mode = 'INSERT')
278 THEN
279
280 IF (p_customer_trx_line_id IS NULL)
281 THEN
282
283 /*-------------------------------------------+
284 | crediting freight for the entire invoice |
285 +-------------------------------------------*/
286
287 arp_ctl_pkg.insert_line_f_cm_ct_ctl_id(
288 p_customer_trx_id,
289 p_customer_trx_line_id,
290 p_prev_ct_id,
291 'FREIGHT',
292 0,
293 p_uncr_freight_amount,
294 p_credit_freight_amount,
295 p_currency_code);
296
297 --
298 -- call CM module to create FRIEGHT distributions
299 --
300 END IF;
301 ELSIF (p_mode = 'DELETE')
302 THEN
303 arp_ctlgd_pkg.delete_f_ct_ltctl_id_type(
304 p_customer_trx_id,
305 p_customer_trx_line_id,
306 'FREIGHT',
307 null,
308 null);
309
310 arp_ctl_pkg.delete_f_ct_ltctl_id_type(
311 p_customer_trx_id,
312 p_customer_trx_line_id,
313 'FREIGHT');
314 END IF;
315 END IF;
316
317 arp_util.debug('arp_process_credit.credit_freight()-');
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 arp_util.debug('EXCEPTION: arp_process_credit.credit_freight');
322 arp_util.debug('');
323 arp_util.debug('p_customer_trx_id : '||p_customer_trx_id);
324 arp_util.debug('p_customer_trx_line_id : '||p_customer_trx_line_id);
325 arp_util.debug('p_prev_ct_id : '||p_prev_ct_id);
326 arp_util.debug('p_prev_ctl_id : '||p_prev_ctl_id);
327 arp_util.debug('p_credit_freight_amount : '||p_credit_freight_amount);
328 arp_util.debug('p_uncr_freight_amount : '||p_uncr_freight_amount);
329 arp_util.debug('p_freight_type : '||p_freight_type);
330 arp_util.debug('p_freight_ctlid : '||p_freight_ctlid);
331 arp_util.debug('p_mode : '||p_mode);
332 arp_util.debug('p_gl_date : '||p_gl_date);
333 arp_util.debug('p_currency_code : '||p_currency_code);
334
335 RAISE;
336 END;
337
338 /*===========================================================================+
339 | PROCEDURE |
340 | default_credit_header |
341 | |
342 | DESCRIPTION |
343 | |
344 | SCOPE - PRIVATE |
345 | |
346 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
347 | arp_util.debug |
348 | |
349 | ARGUMENTS : IN: |
350 | OUT: |
351 | IN/ OUT: |
352 | |
353 | RETURNS : NONE |
354 | |
355 | NOTES |
356 | |
357 | MODIFICATION HISTORY |
358 | 11-AUG-95 Subash Chadalavada Created |
359 | |
360 +===========================================================================*/
361
362 PROCEDURE default_credit_header(
363 p_cm_rec IN ra_customer_trx%rowtype,
364 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
365 p_new_cm_rec IN OUT NOCOPY ra_customer_trx%rowtype)
366 /* Modified IN to IN OUT NOCOPY in the line above - bug 460970 - Oracle 8 */
367 IS
368 l_cr_txn_rec ra_customer_trx%rowtype;
369 BEGIN
370
371 arp_util.debug('arp_process_credit.default_credit_header()+');
372
373 p_new_cm_rec := p_cm_rec;
374 p_new_cm_rec.previous_customer_trx_id := p_prev_customer_trx_id;
375 p_new_cm_rec.complete_flag := 'N';
376
377 IF p_prev_customer_trx_id IS NULL
378 THEN
379 return;
380 END IF;
381
382 arp_ct_pkg.fetch_p(l_cr_txn_rec, p_prev_customer_trx_id);
383
384 IF (p_prev_customer_trx_id IS NOT NULL)
385 THEN
386
387 p_new_cm_rec.ship_to_customer_id := l_cr_txn_rec.ship_to_customer_id;
388 p_new_cm_rec.ship_to_address_id := l_cr_txn_rec.ship_to_address_id;
389 p_new_cm_rec.ship_to_site_use_id := l_cr_txn_rec.ship_to_site_use_id;
390 p_new_cm_rec.ship_to_contact_id := l_cr_txn_rec.ship_to_contact_id;
391
392 p_new_cm_rec.invoicing_rule_id := l_cr_txn_rec.invoicing_rule_id;
393 p_new_cm_rec.set_of_books_id := l_cr_txn_rec.set_of_books_id;
394
395 IF (p_new_cm_rec.bill_to_customer_id) IS NULL
396 THEN
397 p_new_cm_rec.bill_to_customer_id := l_cr_txn_rec.bill_to_customer_id;
398 p_new_cm_rec.bill_to_address_id := l_cr_txn_rec.bill_to_address_id;
399 p_new_cm_rec.bill_to_site_use_id := l_cr_txn_rec.bill_to_site_use_id;
400 p_new_cm_rec.bill_to_contact_id := l_cr_txn_rec.bill_to_contact_id;
401 END IF;
402
403 END IF;
404
405 IF (p_new_cm_rec.printing_option IS NULL)
406 THEN
407 -- populate printing option
408 SELECT ctt.default_printing_option,
409 decode(ctt.default_printing_option,
410 'NOT', 'N',
411 'PRI', 'Y',
412 null)
413 INTO p_new_cm_rec.printing_option,
414 p_new_cm_rec.printing_pending
415 FROM ra_cust_trx_types ctt
416 WHERE ctt.cust_trx_type_id = p_new_cm_rec.cust_trx_type_id;
417
418 END IF;
419
420 arp_util.debug('arp_process_credit.default_credit_header()-');
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 arp_util.debug('EXCEPTION: arp_process_credit.default_credit_header');
425 RAISE;
426 END;
427
428 /*===========================================================================+
429 | PROCEDURE |
430 | validate_insert_header |
431 | |
432 | DESCRIPTION |
433 | |
434 | SCOPE - PRIVATE |
435 | |
436 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
437 | arp_util.debug |
438 | |
439 | ARGUMENTS : IN: |
440 | OUT: |
441 | IN/ OUT: |
442 | |
443 | RETURNS : NONE |
444 | |
445 | NOTES |
446 | |
447 | MODIFICATION HISTORY |
448 | 11-AUG-95 Subash Chadalavada Created |
449 | |
450 +===========================================================================*/
451 PROCEDURE validate_insert_header
452 IS
453 BEGIN
454 arp_util.debug('arp_process_credit.validate_insert_header()+');
455
456 arp_util.debug('arp_process_credit.validate_insert_header()-');
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 arp_util.debug('EXCEPTION: arp_process_credit.validate_insert_header');
461 RAISE;
462
463 END;
464
465
466 /*===========================================================================+
467 | PROCEDURE |
468 | validate_update_header |
469 | |
470 | DESCRIPTION |
471 | |
472 | SCOPE - PRIVATE |
473 | |
474 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
475 | arp_util.debug |
476 | |
477 | ARGUMENTS : IN: |
478 | OUT: |
479 | IN/ OUT: |
480 | |
481 | RETURNS : NONE |
482 | |
483 | NOTES |
484 | |
485 | MODIFICATION HISTORY |
486 | 11-AUG-95 Subash Chadalavada Created |
487 | |
488 +===========================================================================*/
489 PROCEDURE validate_update_header
490 IS
491 BEGIN
492 arp_util.debug('arp_process_credit.validate_update_header()+');
493
494 arp_util.debug('arp_process_credit.validate_update_header()-');
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 arp_util.debug('EXCEPTION: arp_process_credit.validate_update_header');
499 RAISE;
500
501 END;
502
503 /*===========================================================================+
504 | PROCEDURE |
505 | validate_insert_line |
506 | |
507 | DESCRIPTION |
508 | |
509 | SCOPE - PRIVATE |
510 | |
511 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
512 | arp_util.debug |
513 | |
514 | ARGUMENTS : IN: |
515 | OUT: |
516 | IN/ OUT: |
517 | |
518 | RETURNS : NONE |
519 | |
520 | NOTES |
521 | |
522 | MODIFICATION HISTORY |
523 | 11-AUG-95 Subash Chadalavada Created |
524 | 03-SEP-97 Tasman Tang Fixed bug 547165: Change type |
525 | of p_line_rec to |
526 | ra_customer_trx_lines%rowtype |
527 | |
528 +===========================================================================*/
529 PROCEDURE validate_insert_line(
530 p_line_rec IN ra_customer_trx_lines%rowtype)
531 IS
532 BEGIN
533 arp_util.debug('arp_process_credit.validate_insert_line()+');
534
535 arp_trx_validate.check_dup_line_number(p_line_rec.line_number,
536 p_line_rec.customer_trx_id,
537 p_line_rec.customer_trx_line_id);
538
539 arp_util.debug('arp_process_credit.validate_insert_line()-');
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 arp_util.debug('EXCEPTION: arp_process_credit.validate_insert_line');
544 RAISE;
545
546 END;
547
548 /*===========================================================================+
549 | PROCEDURE |
550 | validate_update_line |
551 | |
552 | DESCRIPTION |
553 | |
554 | SCOPE - PRIVATE |
555 | |
556 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
557 | arp_util.debug |
558 | |
559 | ARGUMENTS : IN: |
560 | OUT: |
561 | IN/ OUT: |
562 | |
563 | RETURNS : NONE |
564 | |
565 | NOTES |
566 | |
567 | MODIFICATION HISTORY |
568 | 11-AUG-95 Subash Chadalavada Created |
569 | |
570 +===========================================================================*/
571 PROCEDURE validate_update_line
572 IS
573 BEGIN
574 arp_util.debug('arp_process_credit.validate_update_line()+');
575
576 arp_util.debug('arp_process_credit.validate_update_line()-');
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 arp_util.debug('EXCEPTION: arp_process_credit.validate_update_line');
581 RAISE;
582
583 END;
584
585
586 /*===========================================================================+
587 | PROCEDURE |
588 | default_credit_line |
589 | |
590 | DESCRIPTION |
591 | |
592 | SCOPE - PRIVATE |
593 | |
594 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
595 | arp_util.debug |
596 | |
597 | ARGUMENTS : IN: |
598 | OUT: |
599 | IN/ OUT: |
600 | |
601 | RETURNS : NONE |
602 | |
603 | NOTES |
604 | |
605 | MODIFICATION HISTORY |
606 | 11-AUG-95 Subash Chadalavada Created |
607 | |
608 | 15-May-03 Sahana Bug2880106- Modified logic in procedure |
609 | which defaults the quantity, amount and |
610 | unit selling price. |
611 | 16-DEC-04 VCrisostomo Bug 4072055 : retrieve UOM_CODE |
612 +===========================================================================*/
613
614 PROCEDURE default_credit_line(
615 p_cm_line IN ra_customer_trx_lines%rowtype,
616 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
617 p_prev_customer_trx_line_id IN
618 ra_customer_trx_lines.customer_trx_line_id%type,
619 p_line_amount IN number,
620 p_new_cm_line IN OUT NOCOPY ra_customer_trx_lines%rowtype)
621 /* Modified OUT NOCOPY to IN OUT NOCOPY in the line above - bug460970 - Oracle 8 */
622 IS
623 l_cr_txn_line ra_customer_trx_lines%rowtype;
624 l_trx_rec ra_customer_trx%rowtype;
625 l_calc_unit_price VARCHAR2(1); --2880106
626 BEGIN
627
628 arp_util.debug('arp_process_credit.default_credit_line()+');
629
630 p_new_cm_line := p_cm_line;
631 p_new_cm_line.customer_trx_id := p_customer_trx_id;
632 p_new_cm_line.extended_amount := p_line_amount;
633
634 IF (p_prev_customer_trx_line_id IS NOT NULL)
635 THEN
636
637 arp_ctl_pkg.fetch_p(l_cr_txn_line, p_prev_customer_trx_line_id);
638 arp_ct_pkg.fetch_p(l_trx_rec, p_customer_trx_id);
639
640
641 p_new_cm_line.previous_customer_trx_id :=
642 l_cr_txn_line.customer_trx_id;
643 p_new_cm_line.previous_customer_trx_line_id :=
644 l_cr_txn_line.customer_trx_line_id;
645 p_new_cm_line.set_of_books_id :=
646 l_cr_txn_line.set_of_books_id;
647 p_new_cm_line.initial_customer_trx_line_id :=
648 l_cr_txn_line.initial_customer_trx_line_id;
649
650 -- Bug 2507329 / 2580574 : retrieve sales order number
651 p_new_cm_line.sales_order := l_cr_txn_line.sales_order;
652
653 /*
654 Bug 8652288, Sales Oder Line, date,rev and channel are not being
655 populated when CM is created against an Invoice using CM API.
656 manishri, 15-SEP-2010
657 */
658 p_new_cm_line.sales_order_date := l_cr_txn_line.sales_order_date;
659 p_new_cm_line.sales_order_line := l_cr_txn_line.sales_order_line;
660 p_new_cm_line.sales_order_revision := l_cr_txn_line.sales_order_revision;
661 p_new_cm_line.sales_order_source := l_cr_txn_line.sales_order_source;
662 /* End of change */
663
664 -- Bug 4072055 : retrieve UOM code
665 p_new_cm_line.uom_code := l_cr_txn_line.uom_code;
666
667 IF (p_new_cm_line.line_type IS NULL)
668 THEN
669 IF (l_cr_txn_line.line_type = 'CB')
670 THEN
671 p_new_cm_line.line_type := 'LINE';
672 ELSE
673 p_new_cm_line.line_type := l_cr_txn_line.line_type;
674 END IF;
675 END IF;
676
677 --Bug6144741
678 IF (p_cm_line.warehouse_id is null)
679 THEN
680 p_new_cm_line.warehouse_id := l_cr_txn_line.warehouse_id;
681 END IF;
682
683 IF (p_cm_line.line_number IS NULL)
684 THEN
685 p_new_cm_line.line_number := l_cr_txn_line.line_number;
686 END IF;
687
688 IF (p_cm_line.description IS NULL)
689 THEN
690 p_new_cm_line.description := l_cr_txn_line.description;
691 END IF;
692
693 IF (p_cm_line.inventory_item_id IS NULL)
694 AND
695 (p_cm_line.memo_line_id IS NULL)
696 THEN
697 p_new_cm_line.inventory_item_id := l_cr_txn_line.inventory_item_id;
698 p_new_cm_line.memo_line_id := l_cr_txn_line.memo_line_id;
699 END IF;
700
701 p_new_cm_line.accounting_rule_id := l_cr_txn_line.accounting_rule_id;
702
703 IF p_new_cm_line.accounting_rule_id IS NULL
704 THEN
705 p_new_cm_line.autorule_complete_flag := null;
706 ELSE
707 IF (l_trx_rec.credit_method_for_rules = 'UNIT')
708 THEN
709 p_new_cm_line.last_period_to_credit :=
710 l_cr_txn_line.accounting_rule_duration;
711 ELSE
712 p_new_cm_line.last_period_to_credit := null;
713 END IF;
714 p_new_cm_line.autorule_complete_flag := 'N';
715 END IF;
716
717 /*Start of Bug2880106: Modified logic used to default quantity,
718 unit selling price and amount
719
720 CASE A: Amount is not passed then derive it from
721 - quantity and unit selling price if both are passed
722 - if only quantity is passed, the unit selling price is defaulted from the invoice line and the amount is calculated (Similar to behaviour of forms today)
723
724 CASE B: Amount is Passed.
725 - if both quantity and unit selling price is not passed, then leave them as null (similar to form behaviour)
726 - if only one is passed, derive it from the other. If one is zero, then the other is defaulted to zero.
727 - if qty * usp != amt, then rederive the unit selling price.
728 */
729 IF p_new_cm_line.extended_amount IS NULL THEN
730
731 IF p_new_cm_line.unit_selling_price IS NULL
732 THEN
733 p_new_cm_line.unit_selling_price :=
734 l_cr_txn_line.unit_selling_price;
735 END IF;
736 p_new_cm_line.extended_amount
737 := arpcurr.currround((p_new_cm_line.quantity_credited *
738 p_new_cm_line.unit_selling_price), l_trx_rec.invoice_currency_code) ;
739
740 ELSE -- Amount is not null
741
742 IF p_new_cm_line.unit_selling_price IS NULL AND
743 p_new_cm_line.quantity_credited IS NULL THEN
744 NULL;
745 ELSE -- one or niether is null
746 IF p_new_cm_line.quantity_credited IS NULL
747 THEN
748 IF p_new_cm_line.unit_selling_price <> 0 THEN
749 p_new_cm_line.quantity_credited :=
750 round((p_new_cm_line.extended_amount
751 /p_new_cm_line.unit_selling_price), 15);
752 ELSE
753 p_new_cm_line.quantity_credited := 0 ;
754 END IF;
755 END IF;
756
757 IF p_new_cm_line.unit_selling_price IS NOT NULL
758 THEN
759 IF p_new_cm_line.extended_amount <>
760 arpcurr.currround((p_new_cm_line.quantity_credited *
761 p_new_cm_line.unit_selling_price), l_trx_rec.invoice_currency_code)
762 THEN
763 l_calc_unit_price := 'Y';
764 ELSE
765 l_calc_unit_price := 'N';
766 END IF;
767 ELSE
768 l_calc_unit_price := 'Y';
769 END IF;
770
771 IF l_calc_unit_price = 'Y' THEN
772 IF p_new_cm_line.quantity_credited <> 0
773 THEN
774 p_new_cm_line.unit_selling_price :=
775 round(p_new_cm_line.extended_amount/
776 p_new_cm_line.quantity_credited,15);
777 ELSE -- quantity = 0
778 p_new_cm_line.unit_selling_price := 0;
779 END IF;
780 END IF; -- l_calc_unit_price = 'Y'
781 END IF;
782
783 END IF; -- Amount is Null
784
785 /*End of Bug2880106: Modified logic used to default quantity,
786 unit selling price and amount */
787
788 /* R12 eTax uptake: ship to values populated */
789 p_new_cm_line.ship_to_customer_id := l_cr_txn_line.ship_to_customer_id;
790 p_new_cm_line.ship_to_address_id := l_cr_txn_line.ship_to_address_id;
791 p_new_cm_line.ship_to_site_use_id := l_cr_txn_line.ship_to_site_use_id;
792 p_new_cm_line.ship_to_contact_id := l_cr_txn_line.ship_to_contact_id;
793
794 END IF;
795
796 arp_util.debug('arp_process_credit.default_credit_line()-');
797
798 EXCEPTION
799 WHEN OTHERS THEN
800 arp_util.debug('EXCEPTION: arp_process_credit.default_credit_line');
801 RAISE;
802
803 END;
804
805
806 PROCEDURE get_cm_amounts(
807 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
808 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
809 p_prev_ct_id IN ra_customer_trx.customer_trx_id%type,
810 p_prev_ctl_id IN ra_customer_trx_lines.customer_trx_line_id%type,
811 p_cm_complete_flag OUT NOCOPY varchar2,
812 p_cr_line_amount OUT NOCOPY number,
813 p_cr_tax_amount OUT NOCOPY number,
814 p_cr_freight_amount OUT NOCOPY number)
815 IS
816 l_complete_flag ra_customer_trx.complete_flag%type;
817 l_cr_line_amount number;
818 l_cr_tax_amount number;
819 l_cr_freight_amount number;
820 l_cr_frt_type varchar2(1);
821 l_cr_frt_ctlid number;
822 l_line_count number;
823 l_tax_line_count number;
824 l_freight_line_count number;
825
826 BEGIN
827
828 arp_util.debug('arp_process_credit.get_cm_amounts()+');
829
830 IF (p_customer_trx_id IS NOT NULL)
831 THEN
832
833 SELECT max(complete_flag)
834 INTO l_complete_flag
835 FROM ra_customer_trx
836 WHERE customer_trx_id = p_customer_trx_id;
837
838 SELECT count(ctl.customer_trx_line_id),
839 sum(extended_amount)
840 INTO l_line_count,
841 l_cr_line_amount
842 FROM ra_customer_trx_lines ctl
843 WHERE ctl.customer_trx_id = p_customer_trx_id
844 AND ctl.line_type IN ('CHARGES', 'LINE')
845 AND decode(p_customer_trx_line_id,
846 null, -99,
847 ctl.customer_trx_line_id) = nvl(p_customer_trx_line_id, -99);
848
849 IF (l_line_count > 0)
850 THEN
851 p_cr_line_amount := l_cr_line_amount;
852 END IF;
853
854 SELECT count(ctl.customer_trx_line_id),
855 sum(extended_amount)
856 INTO l_tax_line_count,
857 l_cr_tax_amount
858 FROM ra_customer_trx_lines ctl
859 WHERE ctl.customer_trx_id = p_customer_trx_id
860 AND ctl.line_type = 'TAX'
861 AND decode(p_customer_trx_line_id,
862 null, -99,
863 ctl.link_to_cust_trx_line_id) =
864 nvl(p_customer_trx_line_id, -99);
865
866 IF (l_tax_line_count > 0)
867 THEN
868 p_cr_tax_amount := l_cr_tax_amount;
869 END IF;
870
871 SELECT count(ctl.customer_trx_line_id),
872 sum(extended_amount),
873 decode(max(ctl.link_to_cust_trx_line_id),
874 null, 'H', 'L'),
875 max(ctl.customer_trx_line_id)
876 INTO l_freight_line_count,
877 l_cr_freight_amount,
878 l_cr_frt_type,
879 l_cr_frt_ctlid
880 FROM ra_customer_trx_lines ctl
881 WHERE ctl.customer_trx_id = p_customer_trx_id
882 AND ctl.line_type = 'FREIGHT'
883 AND decode(p_customer_trx_line_id,
884 null, -99,
885 ctl.link_to_cust_trx_line_id) =
886 nvl(p_customer_trx_line_id, -99);
887
888 IF (l_freight_line_count > 0)
889 THEN
890 p_cr_freight_amount := l_cr_freight_amount;
891 END IF;
892
893 END IF;
894
895 arp_util.debug('arp_process_credit.get_cm_amounts()-');
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 arp_util.debug('EXCEPTION: arp_process_credit.get_cm_amounts');
900
901 RAISE;
902
903 END;
904
905
906 /*===========================================================================+
907 | PROCEDURE |
908 | get_uncredit_amounts |
909 | |
910 | DESCRIPTION |
911 | |
912 | SCOPE - PRIVATE |
913 | |
914 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
915 | arp_util.debug |
916 | |
917 | ARGUMENTS : IN: |
918 | OUT: |
919 | IN/ OUT: |
920 | |
921 | RETURNS : NONE |
922 | |
923 | NOTES |
924 | |
925 | MODIFICATION HISTORY |
926 | 11-AUG-95 Subash Chadalavada Created |
927 | |
928 +===========================================================================*/
929
930 PROCEDURE get_uncredit_amounts(
931 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
932 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
933 p_prev_ct_id IN ra_customer_trx.customer_trx_id%type,
934 p_prev_ctl_id IN ra_customer_trx_lines.customer_trx_line_id%type,
935 p_mode IN varchar2,
936 p_uncr_line_amount OUT NOCOPY number,
937 p_uncr_tax_amount OUT NOCOPY number,
938 p_uncr_freight_amount OUT NOCOPY number,
939 p_memo_line_type OUT NOCOPY varchar2,
940 p_freight_type OUT NOCOPY varchar2,
941 p_freight_ctl_id OUT NOCOPY ra_customer_trx_lines.customer_trx_line_id%type)
942 IS
943 l_uncr_line_amount number;
944 l_uncr_tax_amount number;
945 l_uncr_freight_amount number;
946 l_line_count number;
947 l_memo_line_type ar_memo_lines.line_type%type;
948 l_tax_count number;
949 l_freight_count number;
950 l_freight_type varchar2(1);
951 l_freight_ctl_id ra_customer_trx_lines.customer_trx_line_id%type;
952
953 BEGIN
954
955 arp_util.debug('arp_process_credit.get_uncredit_amounts()+');
956
957 SELECT sum(nra.net_amount),
958 count(nra.customer_trx_line_id),
959 max(decode(p_prev_ctl_id,
960 null, null,
961 nvl(ml.line_type, 'LINE')))
962 INTO l_uncr_line_amount,
963 l_line_count,
964 l_memo_line_type
965 FROM ar_net_revenue_amount nra,
966 ra_customer_trx_lines ctl,
967 ar_memo_lines ml
968 WHERE nra.customer_trx_id = p_prev_ct_id
969 AND ctl.customer_trx_line_id = nra.customer_trx_line_id
970 AND ctl.memo_line_id = ml.memo_line_id (+)
971 AND decode(p_prev_ctl_id,
972 null, -99,
973 nra.customer_trx_line_id) = nvl(p_prev_ctl_id, -99)
974 AND nra.line_type in ('LINE', 'CB', 'CHARGES');
975
976 SELECT sum(nra.net_amount),
977 count(nra.customer_trx_line_id)
978 INTO l_uncr_tax_amount,
979 l_tax_count
980 FROM ar_net_revenue_amount nra,
981 ra_customer_trx_lines ctl
982 WHERE nra.customer_trx_id = p_prev_ct_id
983 AND nra.line_type = 'TAX'
984 AND ctl.customer_trx_line_id = nra.customer_trx_line_id
985 AND decode(p_prev_ctl_id,
986 null, -99,
987 ctl.link_to_cust_trx_line_id) = nvl(p_prev_ctl_id, -99);
988
989 SELECT max(decode(ctl.link_to_cust_trx_line_id,
990 null, 'H',
991 'L')),
992 sum(nra.net_amount),
993 max(ctl.customer_trx_line_id),
994 count(nra.customer_trx_line_id)
995 INTO l_freight_type,
996 l_uncr_freight_amount,
997 l_freight_ctl_id,
998 l_freight_count
999 FROM ar_net_revenue_amount nra,
1000 ra_customer_trx_lines ctl
1001 WHERE nra.customer_trx_id = p_prev_ct_id
1002 AND nra.line_type = 'FREIGHT'
1003 AND ctl.customer_trx_line_id = nra.customer_trx_line_id
1004 AND decode(p_prev_ctl_id,
1005 null, -99,
1006 ctl.link_to_cust_trx_line_id) = nvl(p_prev_ctl_id, -99);
1007
1008 IF (l_line_count > 0)
1009 THEN
1010 p_uncr_line_amount := l_uncr_line_amount;
1011 p_memo_line_type := l_memo_line_type;
1012 END IF;
1013
1014 IF (l_tax_count > 0)
1015 THEN
1016 p_uncr_tax_amount := l_uncr_tax_amount;
1017 END IF;
1018
1019 IF (l_freight_count > 0)
1020 THEN
1021 p_uncr_freight_amount := l_uncr_freight_amount;
1022 p_freight_type := l_freight_type;
1023 p_freight_ctl_id := l_freight_ctl_id;
1024 END IF;
1025
1026 arp_util.debug('arp_process_credit.get_uncredit_amounts()-');
1027
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 arp_util.debug('EXCEPTION: arp_process_credit.get_uncredit_amounts');
1031 RAISE;
1032 END;
1033
1034
1035 /*===========================================================================+
1036 | PROCEDURE |
1037 | get_credited_txn_balances |
1038 | |
1039 | DESCRIPTION |
1040 | |
1041 | SCOPE - PRIVATE |
1042 | |
1043 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1044 | arp_util.debug |
1045 | |
1046 | ARGUMENTS : IN: |
1047 | OUT: |
1048 | IN/ OUT: |
1049 | |
1050 | RETURNS : NONE |
1051 | |
1052 | NOTES |
1053 | |
1054 | MODIFICATION HISTORY |
1055 | 11-AUG-95 Subash Chadalavada Created |
1056 | |
1057 +===========================================================================*/
1058
1059 PROCEDURE get_credited_txn_balances(
1060 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1061 p_prev_customer_trx_line_id IN
1062 ra_customer_trx_lines.customer_trx_line_id%type,
1063 p_total_uncr_line_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1064 p_total_uncr_tax_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1065 p_total_uncr_freight_amount OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1066 p_trx_balance_due OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1067 p_line_balance_due OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1068 p_tax_balance_due OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1069 p_freight_balance_due OUT NOCOPY ra_customer_trx_lines.extended_amount%type)
1070 IS
1071 l_count number;
1072 l_total_uncr_line_amount ra_customer_trx_lines.extended_amount%type;
1073 l_total_uncr_tax_amount ra_customer_trx_lines.extended_amount%type;
1074 l_total_uncr_freight_amount ra_customer_trx_lines.extended_amount%type;
1075 l_trx_balance_due ra_customer_trx_lines.extended_amount%type;
1076 l_line_balance_due ra_customer_trx_lines.extended_amount%type;
1077 l_tax_balance_due ra_customer_trx_lines.extended_amount%type;
1078 l_freight_balance_due ra_customer_trx_lines.extended_amount%type;
1079 BEGIN
1080
1081 arp_util.debug('arp_process_credit.get_credited_txn_balances()+');
1082
1083 SELECT sum(decode(
1084 decode(nra.line_type,
1085 'CB', 'LINE',
1086 'CHARGES', 'LINE',
1087 nra.line_type),
1088 'LINE', nra.net_amount,
1089 0)),
1090 sum(decode(nra.line_type, 'TAX', nra.net_amount, 0)),
1091 sum(decode(nra.line_type, 'FREIGHT', nra.net_amount, 0))
1092 INTO l_total_uncr_line_amount,
1093 l_total_uncr_tax_amount,
1094 l_total_uncr_freight_amount
1095 FROM ar_net_revenue_amount nra,
1096 ra_customer_trx_lines ctl
1097 WHERE nra.customer_trx_id = p_prev_customer_trx_id
1098 AND ctl.customer_trx_line_id = nra.customer_trx_line_id
1099 AND ctl.line_type = nra.line_type
1100 AND decode(p_prev_customer_trx_line_id,
1101 null, -99,
1102 ctl.link_to_cust_trx_line_id) =
1103 nvl(p_prev_customer_trx_line_id, -99);
1104
1105 p_total_uncr_line_amount := l_total_uncr_line_amount;
1106 p_total_uncr_tax_amount := l_total_uncr_tax_amount;
1107 p_total_uncr_freight_amount := l_total_uncr_freight_amount;
1108
1109 IF (p_prev_customer_trx_line_id IS NULL)
1110 THEN
1111
1112 /*--------------------------------------------------------+
1113 | get balances from the payment schedule, if they exist |
1114 +--------------------------------------------------------*/
1115 SELECT count(*)
1116 INTO l_count
1117 FROM ar_payment_schedules
1118 WHERE customer_trx_id = p_prev_customer_trx_id;
1119
1120 IF (l_count > 0)
1121 THEN
1122 SELECT sum(nvl(amount_due_remaining, 0)),
1123 sum(nvl(amount_line_items_remaining, 0)),
1124 sum(nvl(tax_remaining, 0)),
1125 sum(nvl(freight_remaining, 0))
1126 INTO l_trx_balance_due,
1127 l_line_balance_due,
1128 l_tax_balance_due,
1129 l_freight_balance_due
1130 FROM ar_payment_schedules
1131 WHERE customer_trx_id = p_prev_customer_trx_id;
1132
1133 /*---------------------------------------------------------+
1134 | For CMs against a child of a deposit, add the amount of |
1135 | the commitment adjustment back to the due amounts |
1136 +--------------------------------------------------------*/
1137 SELECT l_trx_balance_due - sum(nvl(amount, 0)),
1138 l_line_balance_due - sum(nvl(amount, 0))
1139 INTO l_trx_balance_due,
1140 l_line_balance_due
1141 FROM ar_adjustments adj,
1142 ra_cust_trx_types commit_ctt,
1143 ra_customer_trx commit_trx,
1144 ra_customer_trx credited_trx
1145 WHERE commit_ctt.cust_trx_type_id = commit_trx.cust_trx_type_id
1146 AND commit_trx.customer_trx_id =
1147 credited_trx.initial_customer_trx_id
1148 AND credited_trx.customer_trx_id = p_prev_customer_trx_id
1149 AND commit_ctt.type = 'DEP'
1150 AND adj.customer_trx_id = p_prev_customer_trx_id
1151 AND adj.adjustment_type = 'C';
1152 ELSE
1153 l_line_balance_due := l_total_uncr_line_amount;
1154 l_tax_balance_due := l_total_uncr_tax_amount;
1155 l_freight_balance_due := l_total_uncr_freight_amount;
1156
1157 l_trx_balance_due := l_line_balance_due +
1158 l_tax_balance_due +
1159 l_freight_balance_due;
1160 END IF;
1161 END IF;
1162
1163 arp_util.debug('arp_process_credit.get_credited_txn_balances()-');
1164
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 arp_util.debug('EXCEPTION : arp_process_credit.get_credited_txn_balances');
1168 arp_util.debug('');
1169
1170 RAISE;
1171
1172 END;
1173
1174 /*===========================================================================+
1175 | PROCEDURE |
1176 | derive_credit_information |
1177 | |
1178 | DESCRIPTION |
1179 | |
1180 | SCOPE - PRIVATE |
1181 | |
1182 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1183 | arp_util.debug |
1184 | |
1185 | ARGUMENTS : IN: |
1186 | OUT: |
1187 | IN/ OUT: |
1188 | |
1189 | RETURNS : NONE |
1190 | |
1191 | NOTES |
1192 | |
1193 | MODIFICATION HISTORY |
1194 | 11-AUG-95 Subash Chadalavada Created |
1195 | 03-APR-02 Michael Raymond Bug 2217161 - added
1196 | p_submit_cm_dist parameter
1197 | to allow user to submit the
1198 | gl_dist portion of ARXTWCMI
1199 | as a concurrent request.
1200 | |
1201 +===========================================================================*/
1202 PROCEDURE derive_credit_information(
1203 p_form_name IN varchar2,
1204 p_form_version IN number,
1205 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1206 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1207 p_credit_line_amount IN ra_customer_trx_lines.extended_amount%type,
1208 p_credit_freight_amount IN ra_customer_trx_lines.extended_amount%type,
1209 p_currency_code IN fnd_currencies.currency_code%type,
1210 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
1211 p_primary_salesrep_id IN ra_salesreps.salesrep_id%type,
1212 p_compute_tax IN varchar2,
1213 p_line_percent IN number,
1214 p_credit_tax_amount IN OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1215 p_status OUT NOCOPY varchar2,
1216 p_submit_cm_dist IN varchar2 DEFAULT 'N')
1217
1218 IS
1219
1220 l_credited_ctlid ra_customer_trx_lines.customer_trx_line_id%type;
1221 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
1222
1223 l_line_rec ra_customer_trx_lines%rowtype;
1224
1225 l_freight_type varchar2(1);
1226 l_freight_ctl_id ra_customer_trx_lines.customer_trx_line_id%type;
1227 l_memo_line_type ar_memo_lines.line_type%type;
1228
1229 l_uncr_line_amount number;
1230 l_uncr_tax_amount number;
1231 l_uncr_freight_amount number;
1232
1233 l_credit_line_amount number;
1234 l_credit_tax_amount number;
1235 l_credit_freight_amount number;
1236 l_tax_amount number;
1237 l_tax_percent number;
1238
1239 l_failure_count number;
1240
1241 l_total_uncr_line_amount number;
1242 l_total_uncr_tax_amount number;
1243 l_total_uncr_freight_amount number;
1244 l_trx_balance_due number;
1245 l_line_balance_due number;
1246 l_tax_balance_due number;
1247 l_freight_balance_due number;
1248
1249 l_num_rows number;
1250
1251 l_cmm_status varchar2(30) := 'OK';
1252 l_all_line_status varchar2(30) := 'OK';
1253 l_line_status varchar2(30) := 'OK';
1254 l_tax_status varchar2(30) := 'OK';
1255 l_frt_status varchar2(30) := 'OK';
1256 BEGIN
1257
1258 arp_util.debug('arp_process_credit.derive_credit_information()+');
1259
1260 IF (nvl(pg_num_credit_lines, 0) > 0)
1261 THEN
1262
1263 /*-------------------------------------+
1264 | credit lines have been identified, |
1265 | credit uncredited portion |
1266 +-------------------------------------*/
1267
1268 FOR i IN 1..pg_num_credit_lines LOOP
1269 --
1270 -- get amounts to be credited
1271 --
1272 get_uncredit_amounts(p_customer_trx_id,
1273 null,
1274 p_prev_customer_trx_id,
1275 pg_credit_lines(i),
1276 'INSERT',
1277 l_total_uncr_line_amount,
1278 l_total_uncr_tax_amount,
1279 l_total_uncr_freight_amount,
1280 l_memo_line_type,
1281 l_freight_type,
1282 l_freight_ctl_id);
1283
1284 --
1285 -- call insert_lines to credit the line
1286 --
1287
1288 l_tax_amount := -1 * l_total_uncr_tax_amount;
1289
1290 l_line_status := 'OK';
1291
1292 arp_process_credit.insert_line(
1293 p_form_name,
1294 p_form_version,
1295 l_line_rec,
1296 -1 * l_total_uncr_line_amount,
1297 -1 * l_total_uncr_freight_amount,
1298 null,
1299 null,
1300 l_memo_line_type,
1301 p_gl_date,
1302 p_currency_code,
1303 p_primary_salesrep_id,
1304 p_compute_tax,
1305 p_customer_trx_id,
1306 p_prev_customer_trx_id,
1307 pg_credit_lines(i),
1308 l_tax_percent,
1309 l_tax_amount,
1310 l_customer_trx_line_id,
1311 l_line_status);
1312
1313 IF (NVL(l_line_status, 'OK') <> 'OK' ) THEN
1314 l_all_line_status := l_line_status;
1315 END IF;
1316
1317 END LOOP;
1318
1319 ELSE
1320
1321 arp_util.debug('derive_credit_information() : get_uncredit_amounts');
1322
1323 get_uncredit_amounts(p_customer_trx_id,
1324 null,
1325 p_prev_customer_trx_id,
1326 null,
1327 null,
1328 l_total_uncr_line_amount,
1329 l_total_uncr_tax_amount,
1330 l_total_uncr_freight_amount,
1331 l_memo_line_type,
1332 l_freight_type,
1333 l_freight_ctl_id);
1334
1335 arp_util.debug('Freight type : '||l_freight_type);
1336 arp_util.debug('Freight ctlid : '||l_freight_ctl_id);
1337
1338 l_credit_line_amount := p_credit_line_amount;
1339 l_credit_tax_amount := p_credit_tax_amount;
1340 l_credit_freight_amount := p_credit_freight_amount;
1341
1342 IF p_credit_line_amount IS NULL
1343 THEN
1344 IF ((p_credit_tax_amount IS NOT NULL)
1345 OR
1346 (p_credit_freight_amount IS NOT NULL
1347 -- Added the OR clause with the condition for l_freight_type = 'H' :bug 867191.
1348 AND
1349 (l_freight_type = 'L' OR l_freight_type = 'H')
1350 )
1351 )
1352 THEN
1353 l_credit_line_amount := 0;
1354 END IF;
1355 END IF;
1356
1357 IF (l_credit_line_amount IS NOT NULL)
1358 THEN
1359 /*------------------------------------+
1360 | create credit memo LINE lines |
1361 +------------------------------------*/
1362 arp_ctl_pkg.insert_line_f_cm_ct_ctl_id(p_customer_trx_id,
1363 null,
1364 p_prev_customer_trx_id,
1365 'LINE',
1366 p_line_percent,
1367 l_total_uncr_line_amount,
1368 l_credit_line_amount,
1369 p_currency_code,
1370 p_credit_tax_amount);
1371 END IF;
1372
1373 /*-------------------------------+
1374 | create sales credit lines |
1375 +-------------------------------*/
1376 IF (l_credit_line_amount IS NOT NULL)
1377 THEN
1378
1379 create_salescredits(
1380 p_customer_trx_id,
1381 NULL,
1382 NULL,
1383 p_primary_salesrep_id,
1384 p_currency_code);
1385
1386 END IF;
1387
1388 IF (l_credit_freight_amount IS NOT NULL)
1389 THEN
1390 /*------------------------------------+
1391 | create credit memo FREIGHT lines |
1392 +------------------------------------*/
1393 arp_util.debug('derive_credit_information() : credit_freight');
1394 credit_freight(
1395 p_form_name,
1396 p_form_version,
1397 p_customer_trx_id,
1398 null,
1399 p_prev_customer_trx_id,
1400 null,
1401 l_credit_freight_amount,
1402 l_total_uncr_freight_amount,
1403 l_freight_type,
1404 l_freight_ctl_id,
1405 'INSERT',
1406 p_gl_date,
1407 p_currency_code,
1408 l_frt_status);
1409
1410 END IF;
1411
1412 /*-------------------------------+
1413 | call credit memo module |
1414 +-------------------------------*/
1415 /* Bug 2217161 - supress call to arp_credit_memo_module
1416 if user elected to submit */
1417 IF (l_credit_line_amount IS NOT NULL AND
1418 p_submit_cm_dist = 'N')
1419 THEN
1420 BEGIN
1421 arp_util.debug('derive_credit_information() : '||
1422 'credit_transactions');
1423
1424 arp_credit_memo_module.credit_transactions(
1425 p_customer_trx_id,
1426 null,
1427 p_prev_customer_trx_id,
1428 null,
1429 null,
1430 l_failure_count);
1431 EXCEPTION
1432 WHEN arp_credit_memo_module.no_ccid THEN
1433 arp_util.debug('credit memo module exception : no_ccid');
1434 l_cmm_status := 'ARP_CREDIT_MEMO_MODULE.NO_CCID';
1435 WHEN NO_DATA_FOUND THEN
1436 arp_util.debug('credit memo module exception : no_data_found');
1437 null;
1438 WHEN app_exception.application_exception THEN
1439 arp_util.debug('credit memo module exception : app_exception ');
1440 RAISE;
1441 WHEN OTHERS THEN
1442 RAISE;
1443 END;
1444 END IF;
1445
1446 /* #AR CREDIT_MEMO
1447 MODE = "I"
1448 CUSTOMER_TRX_ID = ":CREDIT_MEMO.CUSTOMER_TRX_ID"
1449 PREVIOUS_CUSTOMER_TRX_ID = ":INVOICE.CUSTOMER_TRX_ID" */
1450
1451 END IF;
1452
1453 arp_util.debug('l_all_line_status : '||l_all_line_status);
1454 arp_util.debug('l_tax_status : '||l_tax_status);
1455 arp_util.debug('l_frt_status : '||l_frt_status);
1456 arp_util.debug('l_cmm_status : '||l_cmm_status);
1457
1458 IF (NVL(l_all_line_status, 'OK') <> 'OK') THEN
1459 p_status := l_all_line_status;
1460 ELSIF (NVL(l_tax_status, 'OK') <> 'OK') THEN
1461 p_status := l_tax_status;
1462 ELSIF (NVL(l_frt_status, 'OK') <> 'OK') THEN
1463 p_status := l_frt_status;
1464 ELSIF (NVL(l_cmm_status, 'OK') <> 'OK') THEN
1465 p_status := l_cmm_status;
1466 END IF;
1467
1468 arp_util.debug('arp_process_credit.derive_credit_information()-');
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 arp_util.debug('EXCEPTION: derive_credit_information');
1473 RAISE;
1474 END;
1475
1476
1477 /*===========================================================================+
1478 | PROCEDURE |
1479 | insert_header |
1480 | |
1481 | DESCRIPTION |
1482 | Inserts a row into RA_CUSTOMER_TRX for Credit Memos |
1483 | |
1484 | SCOPE - PRIVATE |
1485 | |
1486 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1487 | arp_util.debug |
1488 | |
1489 | ARGUMENTS : IN: |
1490 | p_form_name |
1491 | p_form_version |
1492 | p_credit_rec |
1493 | p_trx_class |
1494 | p_gl_date |
1495 | p_credit_ccid |
1496 | OUT: |
1497 | p_customer_trx_line_id |
1498 | IN/ OUT: |
1499 | |
1500 | RETURNS : NONE |
1501 | |
1502 | NOTES |
1503 | |
1504 | MODIFICATION HISTORY |
1505 | 11-AUG-95 Subash Chadalavada Created |
1506 | 03-APR-02 Michael Raymond Bug 2217161 - added
1507 | p_submit_cm_dist parameter
1508 | to allow supression of gl_dist
1509 | rows coming from form.
1510 | |
1511 +===========================================================================*/
1512
1513 PROCEDURE insert_header(
1514 p_form_name IN varchar2,
1515 p_form_version IN number,
1516 p_trx_rec IN ra_customer_trx%rowtype,
1517 p_trx_class IN ra_cust_trx_types.type%type,
1518 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
1519 p_primary_salesrep_id IN ra_salesreps.salesrep_id%type,
1520 p_currency_code IN fnd_currencies.currency_code%type,
1521 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1522 p_line_percent IN number,
1523 p_freight_pecent IN number,
1524 p_line_amount IN ra_customer_trx_lines.extended_amount%type,
1525 p_freight_amount IN ra_customer_trx_lines.extended_amount%type,
1526 p_compute_tax IN varchar2,
1527 p_trx_number OUT NOCOPY ra_customer_trx.trx_number%type,
1528 p_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type,
1529 p_tax_percent IN OUT NOCOPY number,
1530 p_tax_amount IN OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
1531 p_status OUT NOCOPY varchar2,
1532 p_submit_cm_dist IN varchar2 DEFAULT 'N')
1533 IS
1534
1535 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
1536 l_trx_rec ra_customer_trx%rowtype;
1537 l_total_credit_amount ra_customer_trx_lines.extended_amount%type;
1538
1539 l_result number;
1540 l_ccid number;
1541 l_concat_segments varchar2(2000);
1542 l_num_failed_dist_rows number;
1543 l_errorbuf varchar2(200);
1544
1545 l_rec_aa_status varchar2(30);
1546 l_derive_status varchar2(30);
1547 l_df_return_status VARCHAR2(1);
1548 l_msg_data VARCHAR2(4000);
1549 l_msg_count NUMBER;
1550
1551 --BUG#2750340
1552 l_ev_rec arp_xla_events.xla_events_type;
1553
1554 BEGIN
1555
1556 arp_util.debug('arp_process_credit.insert_header()+');
1557
1558 /*--------------------------------------------------------------+
1559 | check form version to determine IF it is compatible with the |
1560 | entity handler. |
1561 +--------------------------------------------------------------*/
1562 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
1563
1564
1565 /*--------------------+
1566 | pre-insert logic |
1567 +--------------------*/
1568
1569 -- default_credit_header(p_trx_rec, p_prev_customer_trx_id, l_trx_rec);
1570 l_trx_rec := p_trx_rec;
1571
1572 /* Bug 609042: get the default_status_trx */
1573 IF ( p_trx_rec.status_trx IS NULL )
1574 THEN
1575
1576 SELECT NVL( default_status, 'OP' )
1577 INTO l_trx_rec.status_trx
1578 FROM ra_cust_trx_types
1579 WHERE cust_trx_type_id = p_trx_rec.cust_trx_type_id;
1580 END IF;
1581
1582 validate_insert_header;
1583
1584 /*----------------------+
1585 | call table-handler |
1586 +----------------------*/
1587 arp_ct_pkg.insert_p(l_trx_rec, p_trx_number, l_customer_trx_id);
1588
1589 p_customer_trx_id := l_customer_trx_id;
1590
1591 -- BUG#2750340 : Call AR_XLA_EVENTS
1592 l_ev_rec.xla_from_doc_id := p_customer_trx_id;
1593 l_ev_rec.xla_to_doc_id := p_customer_trx_id;
1594 l_ev_rec.xla_req_id := NULL;
1595 l_ev_rec.xla_dist_id := NULL;
1596 l_ev_rec.xla_doc_table := 'CT';
1597 l_ev_rec.xla_doc_event := NULL;
1598 l_ev_rec.xla_mode := 'O';
1599 l_ev_rec.xla_call := 'B';
1600 l_ev_rec.xla_fetch_size := 999;
1601 arp_xla_events.create_events(p_xla_ev_rec => l_ev_rec );
1602
1603 /*--------------------------------------------------+
1604 | call autoaccounting to insert the REC record |
1605 +--------------------------------------------------*/
1606
1607 BEGIN
1608 arp_auto_accounting.do_autoaccounting(
1609 'I',
1610 'REC',
1611 l_customer_trx_id,
1612 null,
1613 null,
1614 null,
1615 p_gl_date,
1616 null,
1617 nvl(l_total_credit_amount, 0),
1618 null,
1619 null,
1620 null,
1621 null,
1622 null,
1623 null,
1624 l_ccid,
1625 l_concat_segments,
1626 l_num_failed_dist_rows);
1627 EXCEPTION
1628 WHEN arp_auto_accounting.no_ccid THEN
1629 l_rec_aa_status := 'ARP_AUTO_ACCOUNTING.NO_CCID';
1630 WHEN OTHERS THEN
1631 RAISE;
1632 END;
1633
1634
1635 /*------------------------------------------------------+
1636 | derive credit information for regular credit memos |
1637 +------------------------------------------------------*/
1638 IF (( p_prev_customer_trx_id IS NOT NULL)
1639 AND
1640 (( p_line_amount IS NOT NULL
1641 OR
1642 p_tax_amount IS NOT NULL
1643 OR
1644 p_freight_amount IS NOT NULL)
1645 OR
1646 nvl(pg_num_credit_lines,0) > 0))
1647 THEN
1648 l_derive_status := 'OK';
1649 /* Bug 2217161 - added p_submit_cm_dist parameter */
1650 derive_credit_information(p_form_name,
1651 p_form_version,
1652 l_customer_trx_id,
1653 p_prev_customer_trx_id,
1654 p_line_amount,
1655 p_freight_amount,
1656 p_currency_code,
1657 p_gl_date,
1658 p_primary_salesrep_id,
1659 p_compute_tax,
1660 p_line_percent,
1661 p_tax_amount,
1662 l_derive_status,
1663 p_submit_cm_dist);
1664 END IF;
1665
1666 arp_util.debug('l_rec_aa_status : '||l_rec_aa_status);
1667 arp_util.debug('l_derive_status : '||l_derive_status);
1668
1669 IF (NVL(l_rec_aa_status, 'OK') <> 'OK') THEN
1670 p_status := l_rec_aa_status;
1671 ELSIF (NVL(l_derive_status, 'OK') <> 'OK') THEN
1672 p_status := l_derive_status;
1673 END IF;
1674
1675 IF l_df_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1676 p_status := 'eTAX_ERROR';
1677 END IF;
1678
1679 arp_util.debug('arp_process_credit.insert_header()-');
1680
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 arp_util.debug('EXCEPTION: arp_process_credit.insert_header');
1684 RAISE;
1685 END;
1686
1687 /*===========================================================================+
1688 | PROCEDURE |
1689 | delete_cm_records |
1690 | |
1691 | DESCRIPTION |
1692 | Deletes Credit Memo records |
1693 | |
1694 | SCOPE - PRIVATE |
1695 | |
1696 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1697 | arp_util.debug |
1698 | |
1699 | ARGUMENTS : IN: |
1700 | p_customer_trx_id |
1701 | p_trx_number_change_flag |
1702 | OUT: |
1703 | IN/ OUT: |
1704 | |
1705 | RETURNS : NONE |
1706 | |
1707 | NOTES |
1708 | |
1709 | MODIFICATION HISTORY |
1710 | 11-AUG-95 Subash Chadalavada Created |
1711 | 19-NOV-01 Santosh Vaze Bug Fix 2109490 |
1712 | Added parameter p_trx_number_change_flag |
1713 | 11-APR-03 M Raymond Bug 2868648 - added delete of CMA rows
1714 | |
1715 +===========================================================================*/
1716 PROCEDURE delete_cm_records(
1717 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1718 p_trx_number_change_flag IN boolean)
1719 IS
1720 BEGIN
1721
1722 arp_util.debug('arp_process_credit.delete_cm_records()+');
1723
1724 /*-----------------------+
1725 | delete sales credits |
1726 +-----------------------*/
1727 arp_ctls_pkg.delete_f_ct_id(p_customer_trx_id);
1728
1729 /*----------------------------------------+
1730 | delete distributions, except for 'REC' |
1731 +----------------------------------------*/
1732 arp_ctlgd_pkg.delete_f_ct_ltctl_id_type(
1733 p_customer_trx_id,
1734 null,
1735 null,
1736 null,
1737 null);
1738
1739 /*--------------------------------------------------------+
1740 | Reverse Tax vendor's audit for existing CM tax amounts.|
1741 +--------------------------------------------------------*/
1742
1743 ARP_ETAX_UTIL.global_document_update(p_customer_trx_id,
1744 NULL,
1745 'DELETE');
1746
1747 /* Bug 2868648 - remove credit memo amount rows */
1748 arp_cma_pkg.delete_f_ct_id(p_customer_trx_id);
1749
1750 /*---------------------+
1751 | delete line records |
1752 +---------------------*/
1753 arp_ctl_pkg.delete_f_ct_id(p_customer_trx_id);
1754
1755 arp_util.debug('arp_process_credit.delete_cm_records()-');
1756
1757 EXCEPTION
1758
1759 WHEN OTHERS THEN
1760 arp_util.debug('EXCEPTION: arp_process_credit.delete_cm_records');
1761 arp_util.debug('');
1762 arp_util.debug('p_customer_trx_id = '||p_customer_trx_id);
1763
1764 RAISE;
1765 END;
1766
1767
1768 PROCEDURE rerun_aa(
1769 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1770 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
1771 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
1772 p_credit_amount IN ra_cust_trx_line_gl_dist.amount%type,
1773 p_status OUT NOCOPY varchar2)
1774 IS
1775 l_ccid number;
1776 l_concat_segments varchar2(2000);
1777 l_num_failed_dist_rows number;
1778 l_result number;
1779 l_errorbuf varchar2(200);
1780
1781 l_rec_aa_status varchar2(30) := 'OK';
1782 l_other_aa_status varchar2(30) := 'OK';
1783 BEGIN
1784 arp_util.debug('arp_process_credit.line_rerun_aa()+');
1785
1786 IF p_customer_trx_line_id IS NULL
1787 THEN
1788 BEGIN
1789 arp_auto_accounting.do_autoaccounting (
1790 'U',
1791 'REC',
1792 p_customer_trx_id,
1793 p_customer_trx_line_id,
1794 null,
1795 null,
1796 null,
1797 p_gl_date,
1798 null,
1799 p_credit_amount,
1800 null,
1801 null,
1802 null,
1803 null,
1804 null,
1805 l_ccid,
1806 l_concat_segments,
1807 l_num_failed_dist_rows );
1808 EXCEPTION
1809 WHEN arp_auto_accounting.no_ccid THEN
1810 l_rec_aa_status := 'ARP_AUTO_ACCOUNTING.NO_CCID';
1811 WHEN NO_DATA_FOUND THEN
1812 null;
1813 WHEN OTHERS THEN
1814 RAISE;
1815 END;
1816
1817 END IF;
1818
1819 BEGIN
1820 arp_auto_accounting.do_autoaccounting (
1821 'U',
1822 'ALL',
1823 p_customer_trx_id,
1824 p_customer_trx_line_id,
1825 null,
1826 null,
1827 null,
1828 null,
1829 null,
1830 null,
1831 null,
1832 null,
1833 null,
1834 null,
1835 null,
1836 l_ccid,
1837 l_concat_segments,
1838 l_num_failed_dist_rows);
1839 EXCEPTION
1840 WHEN arp_auto_accounting.no_ccid THEN
1841 l_other_aa_status := 'ARP_AUTO_ACCOUNTING.NO_CCID';
1842 WHEN NO_DATA_FOUND THEN
1843 null;
1844 WHEN OTHERS THEN
1845 RAISE;
1846 END;
1847
1848 arp_util.debug('l_rec_aa_status : '||l_rec_aa_status);
1849 arp_util.debug('l_other_aa_status : '||l_other_aa_status);
1850
1851 IF (NVL(l_rec_aa_status, 'OK') <> 'OK') THEN
1852 p_status := l_rec_aa_status;
1853 ELSIF (NVL(l_other_aa_status, 'OK') <> 'OK') THEN
1854 p_status := l_other_aa_status;
1855 END IF;
1856
1857 arp_util.debug('arp_process_credit.line_rerun_aa()-');
1858
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 arp_util.debug('EXCEPTION: arp_process_credit.line_rerun_aa');
1862 RAISE;
1863 END;
1864
1865 /* Bug 3619804 */
1866 /*===========================================================================+
1867 | PROCEDURE |
1868 | reverse_revrec_effect_cm |
1869 | |
1870 | DESCRIPTION |
1871 | When a transaction with rule is incompleted, we will now reverse the |
1872 | effect of revenue recognition on the transaction, if it is already run.|
1873 | |
1874 | SCOPE - PRIVATE |
1875 | |
1876 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1877 | arp_util.debug |
1878 | ARGUMENTS : |
1879 | IN : p_customer_trx_id |
1880 | OUT: |
1881 | IN/ OUT: |
1882 | |
1883 | RETURNS : NONE |
1884 | |
1885 | NOTES |
1886 | |
1887 | MODIFICATION HISTORY |
1888 | 10-May-04 Surendra Rajan Created |
1889 | |
1890 +===========================================================================*/
1891 PROCEDURE reverse_revrec_effect_cm (
1892 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type
1893 ) IS
1894
1895 l_line_rec ra_customer_trx_lines%rowtype;
1896 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
1897
1898 BEGIN
1899
1900 arp_util.debug('arp_process_credit.reverse_revrec_effect_cm()+');
1901
1902 arp_ctl_pkg.set_to_dummy( l_line_rec );
1903 l_line_rec.autorule_complete_flag := 'N';
1904 l_line_rec.autorule_duration_processed := NULL;
1905
1906 BEGIN
1907 arp_ctl_pkg.update_f_ct_id( l_line_rec,
1908 p_customer_trx_id,
1909 'LINE');
1910 EXCEPTION
1911 WHEN NO_DATA_FOUND THEN
1912 arp_util.debug('arp_process_credit..reverse_revrec_effect_cm: '||
1913 'no child lines to update.');
1914 WHEN OTHERS THEN
1915 arp_util.debug('EXCEPTION: '||
1916 'arp_process_credit.reverse_revrec_effect_cm()');
1917 RAISE;
1918 END;
1919
1920 BEGIN
1921
1922 --6870437
1923 ARP_XLA_EVENTS.delete_reverse_revrec_event( p_document_id => p_customer_trx_id,
1924 p_doc_table => 'CT');
1925
1926 arp_ctlgd_pkg.delete_f_ct_id(p_customer_trx_id,
1927 'N',
1928 NULL);
1929 EXCEPTION
1930 WHEN NO_DATA_FOUND THEN
1931 arp_util.debug('arp_process_credit.reverse_revrec_effect_cm: '||
1932 'no dists to delete.');
1933 WHEN OTHERS THEN
1934 arp_util.debug('EXCEPTION: '||
1935 'arp_process_credit.reverse_revrec_effect_cm()');
1936 RAISE;
1937 END;
1938
1939 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
1940 l_dist_rec.latest_rec_flag := 'Y';
1941 BEGIN
1942 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
1943 p_customer_trx_id,
1944 'Y',
1945 'REC');
1946 EXCEPTION
1947 WHEN NO_DATA_FOUND THEN
1948 arp_util.debug('arp_process_credit.reverse_revrec_effect_cm: '||
1949 'no dists to update.');
1950 WHEN OTHERS THEN
1951 arp_util.debug('EXCEPTION: '||
1952 'arp_process_credit.reverse_revrec_effect_cm()');
1953 RAISE;
1954 END;
1955 EXCEPTION
1956 WHEN OTHERS THEN
1957 arp_util.debug('EXCEPTION: '||
1958 'arp_process_credit.reverse_revrec_effect_cm()');
1959 RAISE;
1960 END ;
1961
1962
1963
1964 /*===========================================================================+
1965 | PROCEDURE |
1966 | set_header_flags |
1967 | |
1968 | DESCRIPTION |
1969 | Compares the header record with that existing in the database and |
1970 | sets various flags |
1971 | |
1972 | SCOPE - PRIVATE |
1973 | |
1974 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1975 | arp_util.debug |
1976 | |
1977 | ARGUMENTS : IN: |
1978 | p_customer_trx_id |
1979 | p_new_trx_rec |
1980 | p_new_gl_date |
1981 | OUT: |
1982 | p_exch_rate_changed_flag |
1983 | IN/ OUT: |
1984 | |
1985 | RETURNS : NONE |
1986 | |
1987 | NOTES |
1988 | |
1989 | MODIFICATION HISTORY |
1990 | 11-AUG-95 Subash Chadalavada Created |
1991 | |
1992 +===========================================================================*/
1993 PROCEDURE set_header_flags(
1994 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1995 p_new_trx_rec IN ra_customer_trx%rowtype,
1996 p_new_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
1997 p_exch_rate_changed_flag OUT NOCOPY boolean,
1998 p_gl_date_changed_flag OUT NOCOPY boolean,
1999 p_complete_changed_flag OUT NOCOPY boolean,
2000 p_old_trx_rec OUT NOCOPY ra_customer_trx%rowtype)
2001 IS
2002 l_old_trx_rec ra_customer_trx%rowtype;
2003 l_old_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
2004 BEGIN
2005 arp_util.debug('arp_process_credit.set_header_flags()+');
2006
2007 arp_ct_pkg.fetch_p(l_old_trx_rec, p_customer_trx_id);
2008
2009 p_old_trx_rec := l_old_trx_rec;
2010
2011 p_gl_date_changed_flag := FALSE;
2012 p_complete_changed_flag := FALSE;
2013
2014 IF (nvl(p_new_gl_date, pg_earliest_date) <> pg_date_dummy)
2015 THEN
2016
2017 SELECT gl_date
2018 INTO l_old_gl_date
2019 FROM ra_cust_trx_line_gl_dist
2020 WHERE customer_trx_id = p_customer_trx_id
2021 AND account_class = 'REC'
2022 AND latest_rec_flag = 'Y';
2023
2024 IF (nvl(l_old_gl_date, pg_earliest_date) <>
2025 nvl(p_new_gl_date, pg_earliest_date))
2026 THEN
2027 p_gl_date_changed_flag := TRUE;
2028 ELSE
2029 p_gl_date_changed_flag := FALSE;
2030 END IF;
2031
2032 END IF;
2033
2034 IF (nvl(l_old_trx_rec.complete_flag, 'x') <>
2035 nvl(p_new_trx_rec.complete_flag, 'x')
2036 AND
2037 nvl(p_new_trx_rec.complete_flag, 'x') <> pg_flag_dummy)
2038 THEN
2039 p_complete_changed_flag := TRUE;
2040 ELSE
2041 p_complete_changed_flag := FALSE;
2042 END IF;
2043
2044 arp_util.debug('arp_process_credit.set_header_flags()-');
2045
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 arp_util.debug('EXCEPTION: arp_process_credit.set_header_flags');
2049 arp_util.debug('');
2050 RAISE;
2051 END;
2052
2053 /*===========================================================================+
2054 | PROCEDURE |
2055 | update_header |
2056 | |
2057 | DESCRIPTION |
2058 | Updates the row in RA_CUSTOMER_TRX for Credit Memos |
2059 | |
2060 | SCOPE - PRIVATE |
2061 | |
2062 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2063 | arp_util.debug |
2064 | |
2065 | ARGUMENTS : IN: |
2066 | p_form_name |
2067 | p_form_version |
2068 | p_trx_class |
2069 | p_gl_date |
2070 | p_credit_ccid |
2071 | OUT: |
2072 | p_customer_trx_line_id |
2073 | IN/ OUT: |
2074 | p_credit_rec |
2075 | |
2076 | RETURNS : NONE |
2077 | |
2078 | NOTES |
2079 | |
2080 | MODIFICATION HISTORY |
2081 | 11-AUG-95 Subash Chadalavada Created |
2082 | 19-NOV-01 Santosh Vaze Bug Fix 2109490 |
2083 | |
2084 +===========================================================================*/
2085 PROCEDURE update_header(
2086 p_form_name IN varchar2,
2087 p_form_version IN number,
2088 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
2089 p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
2090 p_trx_class IN ra_cust_trx_types.type%type,
2091 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
2092 p_primary_salesrep_id IN ra_salesreps.salesrep_id%type,
2093 p_currency_code IN fnd_currencies.currency_code%type,
2094 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
2095 p_line_percent IN number,
2096 p_freight_pecent IN number,
2097 p_line_amount IN ra_customer_trx_lines.extended_amount%type,
2098 p_freight_amount IN ra_customer_trx_lines.extended_amount%type,
2099 p_credit_amount IN ra_customer_trx_lines.extended_amount%type,
2100 p_cr_txn_invoicing_rule_id IN ra_customer_trx.invoicing_rule_id%type,
2101 p_rederive_credit_info IN varchar2,
2102 p_rerun_aa IN varchar2,
2103 p_rerun_cm_module IN varchar2,
2104 p_compute_tax IN varchar2,
2105 p_tax_percent IN OUT NOCOPY number,
2106 p_tax_amount IN OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
2107 p_status OUT NOCOPY varchar2)
2108 IS
2109 l_old_trx_rec ra_customer_trx%rowtype;
2110 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
2111 l_exchange_rate ra_customer_trx.exchange_rate%type;
2112 l_exch_rate_changed_flag boolean;
2113 l_complete_changed_flag boolean;
2114 l_gl_date_changed_flag boolean;
2115 l_frt_only_rules boolean;
2116 l_acct_set_change varchar2(10);
2117
2118 l_recalc_tax_flag boolean := FALSE;
2119 l_trx_number_change_flag boolean := FALSE; --Bug2109490
2120 orig_trx_number ra_customer_trx.trx_number%type;
2121 l_failure_count number;
2122
2123 l_tax_status varchar2(100) := 'OK';
2124 l_derive_status varchar2(100) := 'OK';
2125 l_cmm_status varchar2(100) := 'OK';
2126 --BUG#2750340
2127 l_ev_rec arp_xla_events.xla_events_type;
2128 l_action VARCHAR2(10);
2129 l_return_status VARCHAR2(1);
2130 l_msg_data VARCHAR2(4000);
2131 l_msg_count NUMBER;
2132 l_ret_status NUMBER;
2133
2134 --6870437
2135 l_event_source_info xla_events_pub_pkg.t_event_source_info;
2136 l_event_id NUMBER;
2137 l_security xla_events_pub_pkg.t_security;
2138
2139 CURSOR l_rec IS
2140 SELECT 222,
2141 min(ev.event_id) event_id,
2142 trx.legal_entity_id legal_entity_id,
2143 trx.set_of_books_id set_of_books_id,
2144 xet.entity_code entity_code,
2145 trx.trx_number trx_number,
2146 trx.customer_trx_id customer_trx_id,
2147 trx.org_id org_id
2148 FROM ra_customer_trx trx,
2149 ra_cust_trx_line_gl_dist gld,
2150 xla_transaction_entities_upg xet,
2151 xla_events ev
2152 WHERE trx.customer_trx_id = p_customer_trx_id
2153 AND trx.customer_trx_id = gld.customer_trx_id
2154 AND gld.account_class = 'REC'
2155 AND gld.posting_control_id = -3
2156 AND gld.latest_rec_flag = 'Y'
2157 AND trx.SET_OF_BOOKS_ID = xet.LEDGER_ID
2158 AND xet.application_id = 222
2159 AND nvl(xet.source_id_int_1, -99) = trx.customer_trx_id
2160 AND xet.entity_code = 'TRANSACTIONS'
2161 AND xet.entity_id = ev.entity_id
2162 AND ev.application_id = 222
2163 AND ev.event_status_code = 'I'
2164 group by trx.legal_entity_id,
2165 trx.set_of_books_id,
2166 xet.entity_code,
2167 trx.trx_number,
2168 trx.customer_trx_id,
2169 trx.org_id;
2170
2171 BEGIN
2172 arp_util.debug('arp_process_credit.update_header()+');
2173
2174 /*--------------------------------------------------------------+
2175 | check form version to determine IF it is compatible with the |
2176 | entity handler. |
2177 +--------------------------------------------------------------*/
2178
2179 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
2180
2181 p_status := 'OK';
2182 /*---------------------------------------------+
2183 | Validate the record that is being updated |
2184 +---------------------------------------------*/
2185 validate_update_header;
2186
2187 --Bug2109490
2188 /*----------------------------------------------------------------------+
2189 | The procedure arp_process_tax.before_update_cm_header checks whether |
2190 | transaction number has been replaced by document sequence value. |
2191 | If Yes, this is identified as Tax Event for Vendors and Vendor data |
2192 | is synchronized with the new transaction number. |
2193 | This is not a Tax Event for GTE. |
2194 +----------------------------------------------------------------------*/
2195 arp_util.debug( 'p_compute_tax : ' || p_compute_tax);
2196 arp_util.debug( 'p_rederive_credit_info : ' || p_rederive_credit_info);
2197 arp_util.debug( 'p_trx_rec.trx_number : ' || p_trx_rec.trx_number);
2198 arp_util.debug( 'p_trx_rec.old_trx_number : ' || p_trx_rec.old_trx_number);
2199 l_trx_number_change_flag := FALSE;
2200
2201
2202 IF ( p_trx_rec.old_trx_number is not null )
2203 THEN
2204
2205 BEGIN
2206 SELECT trx_number
2207 INTO orig_trx_number
2208 FROM ra_customer_trx
2209 WHERE customer_trx_id = p_customer_trx_id;
2210
2211 arp_util.debug( 'orig_trx_number : ' || orig_trx_number);
2212
2213 IF p_trx_rec.trx_number <> orig_trx_number
2214 THEN
2215 l_trx_number_change_flag := TRUE;
2216 --Bug Fix 7115142 Synchronization for Document Sequence should happen only after the header has been updated.
2217 /* arp_etax_util.synchronize_for_doc_seq(null,
2218 p_customer_trx_id); */
2219 END IF;
2220
2221 EXCEPTION
2222 WHEN OTHERS THEN
2223 RAISE;
2224 END;
2225
2226 END IF;
2227
2228 --Bug2109490
2229 arp_util.debug('l_trx_number_change_flag : '||arp_trx_util.boolean_to_varchar2(l_trx_number_change_flag));
2230 --
2231 -- call Tax engine, checking if tax needs to be recalculated
2232 --
2233 l_action := arp_etax_services_pkg.Get_tax_Action(p_customer_trx_id);
2234
2235 IF (l_action = 'UPDATE') THEN
2236 -- next call the etax for header det factor update
2237 arp_etax_services_pkg.Header_det_factors(
2238 p_customer_trx_id => p_customer_trx_id,
2239 p_mode => l_action,
2240 x_return_status => l_return_status,
2241 x_msg_count => l_msg_count,
2242 x_msg_data => l_msg_data);
2243 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2244 p_status := 'ETAX_ERROR';
2245 END IF;
2246 END IF;
2247
2248 /*---------------------------------------------------------------+
2249 | Lock rows in other tables that reference this customer_trx_id |
2250 | and the credited transaction |
2251 +---------------------------------------------------------------*/
2252 arp_trx_util.lock_transaction(p_customer_trx_id);
2253
2254 set_header_flags(p_customer_trx_id,
2255 p_trx_rec,
2256 p_gl_date,
2257 l_exch_rate_changed_flag,
2258 l_gl_date_changed_flag,
2259 l_complete_changed_flag,
2260 l_old_trx_rec);
2261
2262 /*---------------------------------------------------------------+
2263 | If the CM is a freight-only transaction and if the credited |
2264 | transaction has rules, then clear out NOCOPY invoicing_rule_id on CM |
2265 | If CM is made incomplete and if the credited transaction has |
2266 | rules, then set the invoicing_rule_id on CM |
2267 +---------------------------------------------------------------*/
2268 l_acct_set_change := null;
2269
2270 IF ((l_complete_changed_flag = TRUE)
2271 AND
2272 (p_prev_customer_trx_id IS NOT NULL))
2273 THEN
2274 l_frt_only_rules :=
2275 arp_trx_util.detect_freight_only_rules_case(p_customer_trx_id);
2276
2277 IF p_trx_rec.complete_flag = 'Y'
2278 THEN
2279 IF ((l_frt_only_rules = TRUE)
2280 AND
2281 (p_cr_txn_invoicing_rule_id IS NOT NULL))
2282 THEN
2283 p_trx_rec.invoicing_rule_id := null;
2284 l_acct_set_change := 'REAL';
2285 END IF;
2286 ELSE
2287 IF ((l_frt_only_rules = TRUE)
2288 AND
2289 (p_cr_txn_invoicing_rule_id IS NOT NULL))
2290 THEN
2291 p_trx_rec.invoicing_rule_id := p_cr_txn_invoicing_rule_id;
2292 l_acct_set_change := 'ACCT_SET';
2293 END IF;
2294 END IF;
2295
2296 END IF;
2297
2298 /*----------------------+
2299 | call table-handler |
2300 +----------------------*/
2301 arp_ct_pkg.update_p(p_trx_rec, p_customer_trx_id);
2302
2303 /*---------------------+
2304
2305 | post-update logic |
2306 +---------------------*/
2307
2308 --Bug Fix 7115142, Synchronize Document Sequence if required
2309 IF(l_trx_number_change_flag) THEN
2310 arp_etax_util.synchronize_for_doc_seq(p_customer_trx_id,l_ret_status);
2311
2312 IF l_ret_status > 0
2313 THEN
2314 arp_util.debug('EXCEPTION: error calling eBusiness Tax, status = ' ||
2315 l_ret_status);
2316 arp_util.debug('Please review the plsql debug log for additional details.');
2317 p_status := 'SYNCH_DOC_SEQ_ERROR';
2318 END IF;
2319 END IF;
2320
2321 IF ( nvl(p_status, 'OK') <> 'SYNCH_DOC_SEQ_ERROR' )
2322 THEN --bug 7193337
2323 IF p_trx_rec.exchange_rate = pg_number_dummy THEN
2324 l_exchange_rate := nvl(l_old_trx_rec.exchange_rate, 1);
2325 ELSE
2326 l_exchange_rate := nvl(p_trx_rec.exchange_rate, 1);
2327 END IF;
2328
2329 IF (l_acct_set_change = 'REAL')
2330 THEN
2331 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
2332
2333 l_dist_rec.account_set_flag := 'N';
2334
2335 l_dist_rec.acctd_amount := arp_standard.functional_amount(
2336 p_credit_amount,
2337 pg_base_curr_code,
2338 l_exchange_rate,
2339 pg_base_precision,
2340 pg_base_min_acc_unit);
2341 l_dist_rec.amount := p_credit_amount;
2342 l_dist_rec.gl_date := p_gl_date;
2343 l_dist_rec.original_gl_date := p_gl_date;
2344
2345 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
2346 p_customer_trx_id,
2347 null,
2348 null);
2349
2350 ELSIF (l_acct_set_change = 'ACCT_SET')
2351 THEN
2352
2353 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
2354
2355 l_dist_rec.account_set_flag := 'Y';
2356
2357 --
2358 -- update acct_set_flag = Y for REC record
2359 --
2360 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
2361 p_customer_trx_id,
2362 'N',
2363 'REC');
2364
2365 --
2366 -- now update acct_set_flag = Y for the remaining
2367 -- account classes
2368 --
2369 l_dist_rec.account_set_flag := 'N';
2370 l_dist_rec.amount := null;
2371 l_dist_rec.acctd_amount := null;
2372 l_dist_rec.original_gl_date := null;
2373 l_dist_rec.gl_date := null;
2374
2375 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
2376 p_customer_trx_id,
2377 'Y',
2378 null);
2379 END IF;
2380
2381 --Bug2109490
2382 arp_util.debug( 'p_prev_customer_trx_id : ' || to_char( p_prev_customer_trx_id ));
2383 arp_util.debug( 'p_rederive_credit_info : ' || p_rederive_credit_info);
2384 arp_util.debug(' l_recalc_tax_flag : '||arp_trx_util.boolean_to_varchar2(l_recalc_tax_flag));
2385 arp_util.debug( 'p_compute_tax : ' || p_compute_tax);
2386 arp_util.debug('l_trx_number_change_flag : '||arp_trx_util.boolean_to_varchar2(l_trx_number_change_flag));
2387
2388 IF (( p_prev_customer_trx_id IS NOT NULL)
2389 AND
2390 nvl(p_rederive_credit_info, 'N') = 'Y' )
2391 THEN
2392 --
2393 -- if rederive credit memo info is set, then delete all records
2394 -- and rederive info if any of the amounts are not null
2395 --
2396
2397 arp_util.debug( 'p_prev_customer_trx_id : ' || to_char( p_prev_customer_trx_id ));
2398 arp_util.debug( 'p_customer_trx_id : ' || to_char( p_customer_trx_id ));
2399 delete_cm_records(p_customer_trx_id, l_trx_number_change_flag);
2400 /*Bug 8974913*/
2401 IF ( p_compute_tax = 'Y' AND p_tax_amount <> 0)
2402 THEN
2403 p_tax_amount := NULL;
2404 p_tax_percent := NULL;
2405 END IF;
2406
2407 IF ( p_line_amount IS NOT NULL
2408 OR
2409 p_tax_amount IS NOT NULL
2410 OR
2411 p_freight_amount IS NOT NULL)
2412 THEN
2413 derive_credit_information(p_form_name,
2414 p_form_version,
2415 p_customer_trx_id,
2416 p_prev_customer_trx_id,
2417 p_line_amount,
2418 p_freight_amount,
2419 p_currency_code,
2420 p_gl_date,
2421 p_primary_salesrep_id,
2422 p_compute_tax,
2423 p_line_percent,
2424 p_tax_amount,
2425 l_derive_status);
2426 END IF;
2427
2428 END IF;
2429
2430 IF (l_exch_rate_changed_flag = TRUE
2431 AND
2432 l_acct_set_change IS NULL
2433 AND
2434 nvl(p_rederive_credit_info, 'N') = 'N')
2435 THEN
2436 arp_ctlgd_pkg.update_acctd_amount(p_customer_trx_id,
2437 pg_base_curr_code,
2438 l_exchange_rate,
2439 pg_base_precision,
2440 pg_base_min_acc_unit);
2441 END IF;
2442
2443 IF (l_gl_date_changed_flag = TRUE
2444 AND
2445 l_acct_set_change IS NULL
2446 AND
2447 nvl(p_rederive_credit_info, 'N') = 'N')
2448 THEN
2449 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
2450 l_dist_rec.gl_date := p_gl_date;
2451
2452 BEGIN
2453 /* Bug 3251996 Updating the gl_date of the distributions
2454 with account_set_flag Y for CM's attached to invoice
2455 with rules.Else the distributions with account_set_flag
2456 N is updated with the gl_date. */
2457
2458 IF (p_cr_txn_invoicing_rule_id IS NOT NULL)
2459 THEN
2460 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
2461 p_customer_trx_id,
2462 'Y',
2463 null);
2464 ELSE
2465 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
2466 p_customer_trx_id,
2467 'N',
2468 null);
2469 END IF;
2470 EXCEPTION
2471 WHEN NO_DATA_FOUND THEN
2472 arp_util.debug('arp_process_credit.update_header: '||
2473 'no distributions to update');
2474 WHEN OTHERS THEN
2475 arp_util.debug('EXCEPTION : arp_process_credit.update_header');
2476 arp_util.debug(' update gl_date');
2477 RAISE;
2478 END;
2479 END IF;
2480
2481 IF (p_rerun_cm_module = 'Y'
2482 AND
2483 nvl(p_rederive_credit_info, 'N') = 'N')
2484 THEN
2485 BEGIN
2486 arp_util.debug('update_header() : credit_transactions');
2487
2488 arp_credit_memo_module.credit_transactions(
2489 p_customer_trx_id,
2490 null,
2491 p_prev_customer_trx_id,
2492 null,
2493 null,
2494 l_failure_count,
2495 'U');
2496 EXCEPTION
2497 WHEN arp_credit_memo_module.no_ccid THEN
2498 arp_util.debug('credit memo module exception : no_ccid');
2499 l_cmm_status := 'ARP_CREDIT_MEMO_MODULE.NO_CCID';
2500 WHEN NO_DATA_FOUND THEN
2501 arp_util.debug('credit memo module exception : no_data_found');
2502 null;
2503 WHEN app_exception.application_exception THEN
2504 arp_util.debug('credit memo module exception : app_exception ');
2505 RAISE;
2506 WHEN OTHERS THEN
2507 RAISE;
2508 END;
2509
2510 END IF;
2511 /* Bug 3619804 Added the call to the procedure reverse_revrec_effect_cm */
2512 arp_util.debug('Complete_changed_flag : '||arp_trx_util.boolean_to_varchar2(l_complete_changed_flag));
2513 arp_util.debug('Complete Flag : '|| p_trx_rec.complete_flag);
2514 arp_util.debug('Customer_trx_id : '|| p_customer_trx_id );
2515 arp_util.debug('CM invoicing_rule_id : '|| p_cr_txn_invoicing_rule_id );
2516 arp_util.debug('CM Rev Recog Run Flag : ' ||arpt_sql_func_util.get_revenue_recog_run_flag(p_customer_trx_id,p_cr_txn_invoicing_rule_id));
2517
2518 IF l_complete_changed_flag AND p_trx_rec.complete_flag = 'N'
2519 THEN
2520 IF p_cr_txn_invoicing_rule_id IS NOT NULL
2521 AND arpt_sql_func_util.get_revenue_recog_run_flag(p_customer_trx_id,
2522 p_cr_txn_invoicing_rule_id) = 'Y'
2523 THEN
2524
2525 reverse_revrec_effect_cm(p_customer_trx_id);
2526
2527 END IF;
2528 END IF; /* IF l_complete_flag_changed */
2529
2530
2531 arp_util.debug('l_tax_status : '|| l_tax_status);
2532 arp_util.debug('l_derive_status : '|| l_derive_status);
2533 arp_util.debug('l_cmm_status : '|| l_cmm_status);
2534
2535 IF (NVL(l_tax_status, 'OK') <> 'OK') THEN
2536 p_status := l_tax_status;
2537 ELSIF (NVL(l_derive_status, 'OK') <> 'OK' ) THEN
2538 p_status := l_derive_status;
2539 ELSIF (NVL(l_cmm_status, 'OK') <> 'OK' ) THEN
2540 p_status := l_cmm_status;
2541 END IF;
2542
2543 --6870437
2544 IF p_trx_rec.complete_flag = 'N' AND l_gl_date_changed_flag = TRUE
2545 AND p_cr_txn_invoicing_rule_id IS NOT NULL THEN
2546
2547 FOR c IN l_rec loop
2548
2549 l_event_source_info.application_id := 222;
2550 l_event_source_info.legal_entity_id := c.legal_entity_id;
2551 l_event_source_info.ledger_id := c.set_of_books_id;
2552 l_event_source_info.entity_type_code := c.entity_code;
2553 l_event_source_info.transaction_number:= c.trx_number;
2554 l_event_source_info.source_id_int_1 := c.customer_trx_id;
2555
2556 l_security.security_id_int_1 := c.org_id;
2557 l_event_id := c.event_id;
2558
2559 xla_events_pub_pkg.update_event
2560 (p_event_source_info => l_event_source_info,
2561 p_event_id => l_event_id,
2562 p_event_date => p_gl_date,
2563 p_valuation_method => null,
2564 p_transaction_date => p_trx_rec.trx_date,
2565 p_security_context => l_security);
2566 END loop;
2567 ELSE
2568
2569 -- BUG#2750340 : Call AR_XLA_EVENTS
2570 l_ev_rec.xla_from_doc_id := p_customer_trx_id;
2571 l_ev_rec.xla_to_doc_id := p_customer_trx_id;
2572 l_ev_rec.xla_req_id := NULL;
2573 l_ev_rec.xla_dist_id := NULL;
2574 l_ev_rec.xla_doc_table := 'CT';
2575 l_ev_rec.xla_doc_event := NULL;
2576 l_ev_rec.xla_mode := 'O';
2577 l_ev_rec.xla_call := 'B';
2578 l_ev_rec.xla_fetch_size := 999;
2579 arp_xla_events.create_events(p_xla_ev_rec => l_ev_rec );
2580
2581 END IF;
2582 END IF; --bug 7193337
2583 arp_util.debug('arp_process_credit.update_header()-');
2584
2585 EXCEPTION
2586
2587 WHEN OTHERS THEN
2588 arp_util.debug('EXCEPTION : arp_process_credit.update_header');
2589
2590 RAISE;
2591 END;
2592
2593 /*===========================================================================+
2594 | PROCEDURE |
2595 | insert_line |
2596 | |
2597 | DESCRIPTION |
2598 | Inserts a row into RA_CUSTOMER_TRX_LINES for Credit Memos |
2599 | |
2600 | SCOPE - PRIVATE |
2601 | |
2602 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2603 | arp_util.debug |
2604 | |
2605 | ARGUMENTS : IN: |
2606 | p_form_name |
2607 | p_form_version |
2608 | p_credit_rec |
2609 | p_trx_class |
2610 | p_gl_date |
2611 | p_credit_ccid |
2612 | OUT: |
2613 | p_customer_trx_line_id |
2614 | IN/ OUT: |
2615 | |
2616 | RETURNS : NONE |
2617 | |
2618 | NOTES |
2619 | |
2620 | MODIFICATION HISTORY |
2621 | 11-AUG-95 Subash Chadalavada Created |
2622 | |
2623 +===========================================================================*/
2624
2625 PROCEDURE insert_line(
2626 p_form_name IN varchar2,
2627 p_form_version IN number,
2628 p_credit_rec IN ra_customer_trx_lines%rowtype,
2629 p_line_amount IN ra_customer_trx_lines.extended_amount%type,
2630 p_freight_amount IN ra_customer_trx_lines.extended_amount%type,
2631 p_line_percent IN number,
2632 p_freight_percent IN number,
2633 p_memo_line_type IN ar_memo_lines.line_type%type,
2634 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
2635 p_currency_code IN fnd_currencies.currency_code%type,
2636 p_primary_salesrep_id IN ra_salesreps.salesrep_id%type,
2637 p_compute_tax IN varchar2,
2638 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
2639 p_prev_customer_trx_id IN ra_customer_trx_lines.customer_trx_id%type,
2640 p_prev_customer_trx_line_id IN
2641 ra_customer_trx_lines.customer_trx_line_id%type,
2642 p_tax_percent IN OUT NOCOPY number,
2643 p_tax_amount IN OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
2644 p_customer_trx_line_id OUT NOCOPY
2645 ra_customer_trx_lines.customer_trx_line_id%type,
2646 p_status OUT NOCOPY varchar2)
2647 IS
2648
2649 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
2650 l_credit_rec ra_customer_trx_lines%rowtype;
2651 l_line_credit_flag boolean;
2652 l_tax_credit_flag boolean;
2653 l_freight_credit_flag boolean;
2654
2655 l_account_class ra_customer_trx_lines.line_type%type;
2656
2657 l_uncr_line_amount number;
2658 l_uncr_tax_amount number;
2659 l_uncr_freight_amount number;
2660 l_trx_balance_due number;
2661 l_line_balance_due number;
2662 l_tax_balance_due number;
2663 l_freight_balance_due number;
2664 l_memo_line_type ar_memo_lines.line_type%type;
2665 l_freight_type varchar2(1);
2666 l_freight_ctl_id ra_customer_trx_lines.customer_trx_line_id%type;
2667
2668 l_result integer;
2669 l_ccid ra_cust_trx_line_gl_dist.code_combination_id%type;
2670 l_concat_segments varchar2(200);
2671 l_num_failed_dist_rows number;
2672 l_rows_processed number;
2673 l_errorbuf varchar2(2000);
2674
2675 l_rule_start_date date;
2676 l_accounting_rule_duration number;
2677
2678
2679 l_frt_status varchar2(30) := 'OK';
2680 l_tax_status varchar2(30) := 'OK';
2681 l_aa_status varchar2(30) := 'OK';
2682 l_cmm_status varchar2(30) := 'OK';
2683
2684 l_mode varchar2(30);
2685
2686 BEGIN
2687
2688 arp_util.debug('arp_process_credit.insert_line()+');
2689
2690 /*--------------------------------------------------------------+
2691 | check form version to determine IF it is compatible with the |
2692 | entity handler. |
2693 +--------------------------------------------------------------*/
2694
2695 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
2696
2697 /*---------------------------------------------------------------+
2698 | Lock rows in other tables that reference this customer_trx_id |
2699 | and the credited transaction |
2700 +---------------------------------------------------------------*/
2701 arp_trx_util.lock_transaction(p_customer_trx_id);
2702
2703 /*--------------------+
2704 | pre-insert logic |
2705 +--------------------*/
2706
2707 default_credit_line(p_credit_rec,
2708 p_customer_trx_id,
2709 p_prev_customer_trx_line_id,
2710 p_line_amount,
2711 l_credit_rec);
2712 /*4556000-4606558*/
2713 /* This condition is added to keep intact of work flow functionality*/
2714 IF p_credit_rec.attribute_category IS NOT NULL then
2715 l_credit_rec.attribute_category:=p_credit_rec.attribute_category;
2716 l_credit_rec.attribute1:=p_credit_rec.attribute1;
2717 l_credit_rec.attribute2:=p_credit_rec.attribute2;
2718 l_credit_rec.attribute3:=p_credit_rec.attribute3;
2719 l_credit_rec.attribute4:=p_credit_rec.attribute4;
2720 l_credit_rec.attribute5:=p_credit_rec.attribute5;
2721 l_credit_rec.attribute6:=p_credit_rec.attribute6;
2722 l_credit_rec.attribute7:=p_credit_rec.attribute7;
2723 l_credit_rec.attribute8:=p_credit_rec.attribute8;
2724 l_credit_rec.attribute9:=p_credit_rec.attribute9;
2725 l_credit_rec.attribute10:=p_credit_rec.attribute10;
2726 l_credit_rec.attribute11:=p_credit_rec.attribute11;
2727 l_credit_rec.attribute12:=p_credit_rec.attribute12;
2728 l_credit_rec.attribute13:=p_credit_rec.attribute13;
2729 l_credit_rec.attribute14:=p_credit_rec.attribute14;
2730 l_credit_rec.attribute15:=p_credit_rec.attribute15;
2731 END IF;
2732
2733 IF p_credit_rec.interface_line_context IS NOT NULL then
2734 l_credit_rec.interface_line_context:=p_credit_rec.interface_line_context;
2735 l_credit_rec.interface_line_attribute1:=p_credit_rec.interface_line_attribute1;
2736 l_credit_rec.interface_line_attribute2:=p_credit_rec.interface_line_attribute2;
2737 l_credit_rec.interface_line_attribute3:=p_credit_rec.interface_line_attribute3;
2738 l_credit_rec.interface_line_attribute4:=p_credit_rec.interface_line_attribute4;
2739 l_credit_rec.interface_line_attribute5:=p_credit_rec.interface_line_attribute5;
2740 l_credit_rec.interface_line_attribute6:=p_credit_rec.interface_line_attribute6;
2741 l_credit_rec.interface_line_attribute7:=p_credit_rec.interface_line_attribute7;
2742 l_credit_rec.interface_line_attribute8:=p_credit_rec.interface_line_attribute8;
2743 l_credit_rec.interface_line_attribute9:=p_credit_rec.interface_line_attribute9;
2744 l_credit_rec.interface_line_attribute10:=p_credit_rec.interface_line_attribute10;
2745 l_credit_rec.interface_line_attribute11:=p_credit_rec.interface_line_attribute11;
2746 l_credit_rec.interface_line_attribute12:=p_credit_rec.interface_line_attribute12;
2747 l_credit_rec.interface_line_attribute13:=p_credit_rec.interface_line_attribute13;
2748 l_credit_rec.interface_line_attribute14:=p_credit_rec.interface_line_attribute14;
2749 l_credit_rec.interface_line_attribute15:=p_credit_rec.interface_line_attribute15;
2750 END IF;
2751
2752 IF p_credit_rec.global_attribute_category is not null then
2753 l_credit_rec.global_attribute_category:=p_credit_rec.global_attribute_category;
2754 l_credit_rec.global_attribute1:=p_credit_rec.global_attribute1;
2755 l_credit_rec.global_attribute2:=p_credit_rec.global_attribute2;
2756 l_credit_rec.global_attribute3:=p_credit_rec.global_attribute3;
2757 l_credit_rec.global_attribute4:=p_credit_rec.global_attribute4;
2758 l_credit_rec.global_attribute5:=p_credit_rec.global_attribute5;
2759 l_credit_rec.global_attribute6:=p_credit_rec.global_attribute6;
2760 l_credit_rec.global_attribute7:=p_credit_rec.global_attribute7;
2761 l_credit_rec.global_attribute8:=p_credit_rec.global_attribute8;
2762 l_credit_rec.global_attribute9:=p_credit_rec.global_attribute9;
2763 l_credit_rec.global_attribute10:=p_credit_rec.global_attribute10;
2764 l_credit_rec.global_attribute11:=p_credit_rec.global_attribute11;
2765 l_credit_rec.global_attribute12:=p_credit_rec.global_attribute12;
2766 l_credit_rec.global_attribute13:=p_credit_rec.global_attribute13;
2767 l_credit_rec.global_attribute14:=p_credit_rec.global_attribute14;
2768 l_credit_rec.global_attribute15:=p_credit_rec.global_attribute15;
2769 l_credit_rec.global_attribute16:=p_credit_rec.global_attribute16;
2770 l_credit_rec.global_attribute17:=p_credit_rec.global_attribute17;
2771 l_credit_rec.global_attribute18:=p_credit_rec.global_attribute18;
2772 l_credit_rec.global_attribute19:=p_credit_rec.global_attribute19;
2773 l_credit_rec.global_attribute20:=p_credit_rec.global_attribute20;
2774 END IF;
2775
2776 arp_process_credit.validate_insert_line(l_credit_rec);
2777
2778 -- call the table handler
2779 arp_ctl_pkg.insert_p(l_credit_rec, l_customer_trx_line_id);
2780
2781 p_customer_trx_line_id := l_customer_trx_line_id;
2782
2783 /*--------------------+
2784 | post-insert logic |
2785 +--------------------*/
2786
2787 create_salescredits( p_customer_trx_id,
2788 l_customer_trx_line_id,
2789 p_memo_line_type,
2790 p_primary_salesrep_id,
2791 p_currency_code);
2792 --
2793 -- if regular credit memo, then credit freight lines if freight credit
2794 -- is passed
2795 --
2796
2797 IF (p_prev_customer_trx_id IS NOT NULL)
2798 THEN
2799 get_uncredit_amounts(p_customer_trx_id,
2800 null,
2801 p_prev_customer_trx_id,
2802 p_prev_customer_trx_line_id,
2803 'INSERT',
2804 l_uncr_line_amount,
2805 l_uncr_tax_amount,
2806 l_uncr_freight_amount,
2807 l_memo_line_type,
2808 l_freight_type,
2809 l_freight_ctl_id);
2810 END IF;
2811
2812 IF (p_freight_amount IS NOT NULL)
2813 THEN
2814 credit_freight(p_form_name,
2815 p_form_version,
2816 p_customer_trx_id,
2817 l_customer_trx_line_id,
2818 p_prev_customer_trx_id,
2819 p_prev_customer_trx_line_id,
2820 p_freight_amount,
2821 l_uncr_freight_amount,
2822 l_freight_type,
2823 l_freight_ctl_id,
2824 'INSERT',
2825 p_gl_date,
2826 p_currency_code,
2827 l_frt_status);
2828 END IF;
2829
2830 /* R12 eTax uptake - we need to call the line_Det_Factors table handler to
2831 set the attributes for calculating tax */
2832
2833 /* 5402228 - clarified use of tax for line-only scenarios. There are
2834 two to be concerned with. They are:
2835
2836 1) inv or cm with memo_line of type TAX -
2837 INSERT_NO_TAX - LINE_INFO_TAX_ONLY
2838 2) inv or cm with no tax at all
2839 INSERT_NO_TAX_EVER - RECORD_WITH_NO_TAX
2840 */
2841 IF NVL(p_tax_amount,0) = 0
2842 AND p_form_name = 'ARXTWCMI'
2843 THEN
2844
2845 IF NVL(p_memo_line_type, 'XXX') = 'TAX'
2846 THEN
2847 l_mode := 'INSERT_NO_TAX';
2848 ELSE
2849 l_mode := 'INSERT_NO_TAX_EVER';
2850 END IF;
2851
2852 ELSIF NVL(p_line_amount,0) = 0 THEN
2853 l_mode := 'INSERT_NO_LINE';
2854 ELSE
2855 /* 7658882 - determine if tax should be called
2856 based on p_form (source) and p_compute_tax parameters */
2857 IF p_form_name = 'AR_CREDIT_MEMO_API'
2858 THEN
2859 IF p_compute_tax = 'Y'
2860 THEN
2861 /* 7658882 - this is the condition for line+tax */
2862 l_mode := 'INSERT';
2863 ELSE
2864 /* This is for line-only */
2865 l_mode := 'INSERT_NO_TAX_EVER';
2866 END IF;
2867 ELSE
2868 /* original behavior */
2869 l_mode := 'INSERT';
2870 END IF;
2871
2872 END IF;
2873
2874 ARP_ETAX_SERVICES_PKG.line_det_factors(
2875 p_customer_trx_line_id => l_customer_trx_line_id,
2876 p_customer_trx_id => p_customer_trx_id,
2877 p_mode => l_mode,
2878 p_tax_amount => p_tax_amount);
2879
2880 IF (p_prev_customer_trx_id IS NULL)
2881 THEN
2882 -- create distributions for on-account CM lines
2883
2884 IF (p_memo_line_type = 'CHARGES')
2885 THEN
2886 l_account_class := 'CHARGES';
2887 ELSE
2888 l_account_class := 'REV';
2889 END IF;
2890
2891 BEGIN
2892 arp_auto_accounting.do_autoaccounting(
2893 'I',
2894 l_account_class,
2895 p_customer_trx_id,
2896 l_customer_trx_line_id,
2897 null,
2898 null,
2899 p_gl_date,
2900 null,
2901 p_line_amount,
2902 null,
2903 null,
2904 null,
2905 null,
2906 null,
2907 null,
2908 l_ccid,
2909 l_concat_segments,
2910 l_num_failed_dist_rows );
2911 EXCEPTION
2912 WHEN arp_auto_accounting.no_ccid THEN
2913 l_aa_status := 'ARP_AUTO_ACCOUNTING.NO_CCID';
2914 WHEN NO_DATA_FOUND THEN
2915 null;
2916 WHEN OTHERS THEN
2917 RAISE;
2918 END;
2919
2920 ELSE
2921 -- call credit-memo module for regular credit-memo case
2922 /*-------------------------------+
2923 | call credit memo module |
2924 +-------------------------------*/
2925 BEGIN
2926 arp_credit_memo_module.credit_transactions(
2927 p_customer_trx_id,
2928 l_customer_trx_line_id,
2929 p_prev_customer_trx_id,
2930 p_prev_customer_trx_line_id,
2931 null,
2932 l_num_failed_dist_rows,
2933 l_rule_start_date,
2934 l_accounting_rule_duration,
2935 'I');
2936 EXCEPTION
2937 WHEN arp_credit_memo_module.no_ccid THEN
2938 l_cmm_status := 'ARP_CREDIT_MEMO_MODULE.NO_CCID';
2939 WHEN NO_DATA_FOUND THEN
2940 null;
2941 WHEN OTHERS THEN
2942 RAISE;
2943 END;
2944
2945 END IF;
2946
2947 arp_util.debug('l_tax_status : '||l_tax_status);
2948 arp_util.debug('l_frt_status : '||l_frt_status);
2949 arp_util.debug('l_aa_status : '||l_aa_status);
2950 arp_util.debug('l_cmm_status : '||l_cmm_status);
2951
2952 IF (NVL(l_tax_status, 'OK') <> 'OK') THEN
2953 p_status := l_tax_status;
2954 ELSIF (NVL(l_frt_status, 'OK') <> 'OK') THEN
2955 p_status := l_frt_status;
2956 ELSIF (NVL(l_aa_status, 'OK') <> 'OK') THEN
2957 p_status := l_aa_status;
2958 ELSIF (NVL(l_cmm_status, 'OK') <> 'OK') THEN
2959 p_status := l_cmm_status;
2960 END IF;
2961
2962 arp_util.debug('arp_process_credit.insert_line()-');
2963
2964 EXCEPTION
2965 when OTHERS THEN
2966 arp_util.debug('EXCEPTION: arp_process_credit.insert_line()');
2967 arp_ctl_pkg.display_line_rec(l_credit_rec);
2968 RAISE;
2969 END;
2970
2971 /*===========================================================================+
2972 | PROCEDURE |
2973 | set_line_flags |
2974 | |
2975 | DESCRIPTION |
2976 | Compares the line record with that existing in the database and |
2977 | sets various flags |
2978 | |
2979 | SCOPE - PRIVATE |
2980 | |
2981 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2982 | arp_util.debug |
2983 | |
2984 | ARGUMENTS : IN: |
2985 | p_customer_trx_line_id |
2986 | p_new_trx_rec |
2987 | p_new_gl_date |
2988 | OUT: |
2989 | p_exch_rate_changed_flag |
2990 | IN/ OUT: |
2991 | |
2992 | RETURNS : NONE |
2993 | |
2994 | NOTES |
2995 | |
2996 | MODIFICATION HISTORY |
2997 | 11-AUG-95 Subash Chadalavada Created |
2998 | |
2999 +===========================================================================*/
3000 PROCEDURE set_line_flags(
3001 p_line_rec IN ra_customer_trx_lines%rowtype,
3002 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
3003 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
3004 p_prev_ct_id IN ra_customer_trx.customer_trx_id%type,
3005 p_prev_ctl_id IN ra_customer_trx_lines.customer_trx_line_id%type,
3006 p_line_amount IN number,
3007 p_tax_amount IN number,
3008 p_frt_amount IN number,
3009 p_cr_line_amount OUT NOCOPY number,
3010 p_cr_tax_amount OUT NOCOPY number,
3011 p_cr_frt_amount OUT NOCOPY number,
3012 p_line_amt_chng_flag OUT NOCOPY boolean,
3013 p_tax_amt_chng_flag OUT NOCOPY boolean,
3014 p_frt_amt_chng_flag OUT NOCOPY boolean,
3015 p_tax_mode OUT NOCOPY varchar2,
3016 p_frt_mode OUT NOCOPY varchar2,
3017 p_frt_ctlid OUT NOCOPY ra_customer_trx_lines.customer_trx_line_id%type,
3018 p_cm_complete_flag OUT NOCOPY ra_customer_trx.complete_flag%type,
3019 p_old_line_rec OUT NOCOPY ra_customer_trx_lines%rowtype)
3020 IS
3021
3022 l_old_line_rec ra_customer_trx_lines%rowtype;
3023
3024 l_frt_amount ra_customer_trx_lines.extended_amount%type;
3025 l_frt_ctlid ra_customer_trx_lines.customer_trx_line_id%type;
3026
3027 l_cm_complete_flag ra_customer_trx.complete_flag%type;
3028 l_credited_line_amount number;
3029 l_credited_tax_amount number;
3030 l_credited_frt_amount number;
3031
3032 l_line_amt_chng_flag boolean;
3033 l_tax_amt_chng_flag boolean;
3034 l_frt_amt_chng_flag boolean;
3035 l_tax_mode varchar2(10);
3036 l_frt_mode varchar2(10);
3037
3038
3039 BEGIN
3040
3041 arp_util.debug('arp_process_credit.set_line_flags()+');
3042
3043 arp_ctl_pkg.fetch_p(l_old_line_rec, p_customer_trx_line_id);
3044
3045 arp_process_credit.get_cm_amounts(
3046 p_customer_trx_id,
3047 p_customer_trx_line_id,
3048 p_prev_ct_id,
3049 p_prev_ctl_id,
3050 l_cm_complete_flag,
3051 l_credited_line_amount,
3052 l_credited_tax_amount,
3053 l_credited_frt_amount);
3054
3055 p_cr_line_amount := l_credited_line_amount;
3056 p_cr_tax_amount := l_credited_tax_amount;
3057 p_cr_frt_amount := l_credited_frt_amount;
3058
3059 IF (nvl(p_line_rec.extended_amount, 0) <> pg_number_dummy
3060 AND
3061 nvl(p_line_rec.extended_amount, pg_number_dummy) <>
3062 nvl(l_old_line_rec.extended_amount, pg_number_dummy))
3063 THEN
3064 l_line_amt_chng_flag := TRUE;
3065 ELSE
3066 l_line_amt_chng_flag := FALSE;
3067 END IF;
3068
3069 IF (nvl(p_frt_amount, pg_number_dummy) <>
3070 nvl(l_credited_frt_amount, pg_number_dummy))
3071 THEN
3072 l_frt_amt_chng_flag := TRUE;
3073
3074 IF p_frt_amount IS NULL
3075 THEN
3076 l_frt_mode := 'DELETE';
3077 ELSIF l_credited_frt_amount IS NULL
3078 THEN
3079 l_frt_mode := 'INSERT';
3080 ELSE
3081 l_frt_mode := 'UPDATE';
3082 END IF;
3083 ELSE
3084 l_frt_amt_chng_flag := FALSE;
3085 END IF;
3086
3087 IF (nvl(p_tax_amount, pg_number_dummy) <>
3088 nvl(l_credited_tax_amount, pg_number_dummy))
3089 THEN
3090 l_tax_amt_chng_flag := TRUE;
3091
3092 IF p_tax_amount IS NULL
3093 THEN
3094 l_tax_mode := 'DELETE';
3095 ELSIF l_credited_tax_amount IS NULL
3096 THEN
3097 l_tax_mode := 'INSERT';
3098 ELSE
3099 l_tax_mode := 'UPDATE';
3100 END IF;
3101 ELSE
3102 l_tax_amt_chng_flag := FALSE;
3103 END IF;
3104
3105 p_cr_line_amount := l_credited_line_amount;
3106 p_cr_tax_amount := l_credited_tax_amount;
3107 p_cr_frt_amount := l_credited_frt_amount;
3108 p_line_amt_chng_flag := l_line_amt_chng_flag;
3109 p_tax_amt_chng_flag := l_tax_amt_chng_flag;
3110 p_frt_amt_chng_flag := l_frt_amt_chng_flag;
3111 p_tax_mode := l_tax_mode;
3112 p_frt_mode := l_frt_mode;
3113 p_frt_ctlid := l_frt_ctlid;
3114 p_cm_complete_flag := l_cm_complete_flag;
3115
3116 arp_util.debug('p_cr_line_amount : '||l_credited_line_amount);
3117 arp_util.debug('p_cr_tax_amount : '||l_credited_tax_amount);
3118 arp_util.debug('p_cr_frt_amount : '||l_credited_frt_amount );
3119 arp_util.debug('p_line_amt_chng_flag : '||
3120 arp_trx_util.boolean_to_varchar2(l_line_amt_chng_flag));
3121 arp_util.debug('p_tax_amt_chng_flag : '||
3122 arp_trx_util.boolean_to_varchar2(l_tax_amt_chng_flag));
3123 arp_util.debug('p_frt_amt_chng_flag : '||
3124 arp_trx_util.boolean_to_varchar2(l_frt_amt_chng_flag));
3125 arp_util.debug('p_tax_mode : '||l_tax_mode);
3126 arp_util.debug('p_frt_mode : '||l_frt_mode);
3127 arp_util.debug('p_frt_ctlid : '||l_frt_ctlid);
3128 arp_util.debug('p_cm_complete_flag : '||l_cm_complete_flag);
3129
3130 arp_util.debug('arp_process_credit.set_line_flags()-');
3131
3132 EXCEPTION
3133 WHEN OTHERS THEN
3134 arp_util.debug('EXCEPTION: arp_process_credit.set_line_flags');
3135 RAISE;
3136 END;
3137
3138 /*===========================================================================+
3139 | PROCEDURE |
3140 | update_line |
3141 | |
3142 | DESCRIPTION |
3143 | Updates the row in RA_CUSTOMER_TRX_LINES for Credit Memos |
3144 | |
3145 | SCOPE - PRIVATE |
3146 | |
3147 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3148 | arp_util.debug |
3149 | |
3150 | ARGUMENTS : IN: |
3151 | p_form_name |
3152 | p_form_version |
3153 | p_credit_rec |
3154 | p_trx_class |
3155 | p_gl_date |
3156 | p_credit_ccid |
3157 | OUT: |
3158 | p_customer_trx_line_id |
3159 | IN/ OUT: |
3160 | |
3161 | RETURNS : NONE |
3162 | |
3163 | NOTES |
3164 | |
3165 | MODIFICATION HISTORY |
3166 | 11-AUG-95 Subash Chadalavada Created |
3167 | 03-SEP-97 Tasman Tang Fixed bug 547165: Change type of |
3168 | l_old_line_rec to |
3169 | ra_customer_trx_lines%rowtype |
3170 | |
3171 +===========================================================================*/
3172
3173 PROCEDURE update_line(
3174 p_form_name IN varchar2,
3175 p_form_version IN number,
3176 p_credit_rec IN ra_customer_trx_lines%rowtype,
3177 p_customer_trx_line_id IN
3178 ra_customer_trx_lines.customer_trx_line_id%type,
3179 p_line_amount IN ra_customer_trx_lines.extended_amount%type,
3180 p_freight_amount IN ra_customer_trx_lines.extended_amount%type,
3181 p_line_percent IN number,
3182 p_freight_percent IN number,
3183 p_memo_line_type IN ar_memo_lines.line_type%type,
3184 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
3185 p_currency_code IN fnd_currencies.currency_code%type,
3186 p_primary_salesrep_id IN ra_salesreps.salesrep_id%type,
3187 p_exchange_rate IN ra_customer_trx.exchange_rate%type,
3188 p_rerun_aa IN varchar2,
3189 p_recalculate_tax IN varchar2,
3190 p_compute_tax IN varchar2,
3191 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
3192 p_prev_customer_trx_id IN ra_customer_trx_lines.customer_trx_id%type,
3193 p_prev_customer_trx_line_id IN
3194 ra_customer_trx_lines.customer_trx_line_id%type,
3195 p_tax_percent IN OUT NOCOPY number,
3196 p_tax_amount IN OUT NOCOPY ra_customer_trx_lines.extended_amount%type,
3197 p_status OUT NOCOPY varchar2)
3198 IS
3199
3200 l_frt_exists_flag boolean;
3201 l_line_amt_chng_flag boolean;
3202 l_frt_amt_chng_flag boolean;
3203 l_tax_amt_chng_flag boolean;
3204
3205 l_memo_line_type ar_memo_lines.line_type%type;
3206 l_frt_type varchar2(1);
3207 l_prev_frt_ctlid ra_customer_trx_lines.customer_trx_line_id%type;
3208
3209 l_uncr_line_amount number;
3210 l_uncr_tax_amount number;
3211 l_uncr_frt_amount number;
3212 l_cr_line_amount number;
3213 l_cr_tax_amount number;
3214 l_cr_frt_amount number;
3215 l_net_uncr_line_amount number;
3216 l_net_uncr_tax_amount number;
3217 l_net_uncr_frt_amount number;
3218
3219 l_tax_mode varchar2(10);
3220 l_frt_mode varchar2(10);
3221
3222 l_cm_complete_flag ra_customer_trx.complete_flag%type;
3223
3224 l_frt_ctlid ra_customer_trx_lines.customer_trx_line_id%type;
3225 l_old_line_rec ra_customer_trx_lines%rowtype;
3226 l_rerun_aa_status varchar2(30) := 'OK';
3227 l_tax_status varchar2(30) := 'OK';
3228 l_frt_status varchar2(30) := 'OK';
3229 l_recalculate_tax_flag BOOLEAN;
3230
3231 BEGIN
3232 arp_util.debug('arp_process_credit.update_line()+');
3233
3234 /*--------------------------------------------------------------+
3235 | check form version to determine IF it is compatible with the |
3236 | entity handler. |
3237 +--------------------------------------------------------------*/
3238
3239 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
3240
3241 /*---------------------------------------------------------------+
3242 | Lock rows in other tables that reference this customer_trx_id |
3243 | and the credited transaction |
3244 +---------------------------------------------------------------*/
3245 arp_trx_util.lock_transaction(p_customer_trx_id);
3246
3247 /*--------------------+
3248 | pre-update logic |
3249 +--------------------*/
3250
3251 arp_process_credit.validate_update_line;
3252
3253 BEGIN
3254
3255
3256 -- Check to see if any relevant columns have been updated which
3257 -- affect tax calculation. If there are columns which affect tax
3258 -- have been modified, we will delete the tax lines and the
3259 -- accounting from those lines before we will eventually call
3260 -- ETAX to recreate the tax lines.
3261
3262 arp_etax_services_pkg.before_update_line(
3263 p_customer_trx_line_id,
3264 p_credit_rec,
3265 l_recalculate_tax_flag);
3266
3267
3268 EXCEPTION
3269 WHEN OTHERS THEN
3270
3271 arp_util.debug(
3272 'arp_etax_services_pkg.before_update_line raised exception');
3273 RAISE;
3274 END;
3275
3276
3277
3278 arp_process_credit.set_line_flags(
3279 p_credit_rec,
3280 p_customer_trx_id,
3281 p_customer_trx_line_id,
3282 p_prev_customer_trx_id,
3283 p_prev_customer_trx_line_id,
3284 p_line_amount,
3285 p_tax_amount,
3286 p_freight_amount,
3287 l_cr_line_amount,
3288 l_cr_tax_amount,
3289 l_cr_frt_amount,
3290 l_line_amt_chng_flag,
3291 l_tax_amt_chng_flag,
3292 l_frt_amt_chng_flag,
3293 l_tax_mode,
3294 l_frt_mode,
3295 l_frt_ctlid,
3296 l_cm_complete_flag,
3297 l_old_line_rec);
3298
3299 /*--------------------------+
3300 | call the table handler |
3301 +--------------------------*/
3302
3303 arp_ctl_pkg.update_p(p_credit_rec, p_customer_trx_line_id);
3304
3305
3306 /*--------------------+
3307 | post-update logic |
3308 +--------------------*/
3309
3310 IF (p_prev_customer_trx_id IS NOT NULL)
3311 THEN
3312 arp_process_credit.get_uncredit_amounts(
3313 p_customer_trx_id,
3314 p_customer_trx_line_id,
3315 p_prev_customer_trx_id,
3316 p_prev_customer_trx_line_id,
3317 null,
3318 l_uncr_line_amount,
3319 l_uncr_tax_amount,
3320 l_uncr_frt_amount,
3321 l_memo_line_type,
3322 l_frt_type,
3323 l_prev_frt_ctlid);
3324 END IF;
3325
3326 /* R12 eTax uptake */
3327 IF (l_recalculate_tax_flag) THEN
3328 /* we need to call the line_Det_Factors table handler to
3329 set the attributes for calculating tax */
3330 ARP_ETAX_SERVICES_PKG.line_det_factors(
3331 p_customer_trx_line_id => p_customer_trx_line_id,
3332 p_customer_trx_id => p_customer_trx_id,
3333 p_mode => 'UPDATE');
3334 END IF;
3335
3336 IF (l_frt_amt_chng_flag)
3337 THEN
3338
3339 IF (l_cm_complete_flag = 'Y')
3340 THEN
3341 l_net_uncr_frt_amount := nvl(l_uncr_frt_amount, 0) -
3342 nvl(l_cr_frt_amount, 0);
3343 ELSE
3344 l_net_uncr_frt_amount := l_uncr_frt_amount;
3345 END IF;
3346
3347 arp_process_credit.credit_freight(
3348 p_form_name,
3349 p_form_version,
3350 p_customer_trx_id,
3351 p_customer_trx_line_id,
3352 p_prev_customer_trx_id,
3353 p_prev_customer_trx_line_id,
3354 p_freight_amount,
3355 l_net_uncr_frt_amount,
3356 l_frt_type,
3357 l_frt_ctlid,
3358 l_frt_mode,
3359 p_gl_date,
3360 p_currency_code,
3361 l_frt_status);
3362
3363 END IF;
3364
3365 /*-----------------------+
3366 | update salescredits |
3367 *-----------------------*/
3368
3369 IF (l_line_amt_chng_flag = TRUE)
3370 THEN
3371 arp_ctls_pkg.update_amounts_f_ctl_id(p_customer_trx_line_id,
3372 p_line_amount,
3373 p_currency_code);
3374 END IF;
3375
3376 /*-----------------------+
3377 | update distributions |
3378 *-----------------------*/
3379 IF (p_rerun_aa = 'Y')
3380 THEN
3381 rerun_aa(p_customer_trx_id,
3382 p_customer_trx_line_id,
3383 p_gl_date,
3384 null,
3385 l_rerun_aa_status);
3386 ELSE
3387 IF (l_line_amt_chng_flag = TRUE)
3388 THEN
3389 BEGIN
3390 arp_ctlgd_pkg.update_amount_f_ctl_id(p_customer_trx_line_id,
3391 p_line_amount,
3392 p_currency_code,
3393 pg_base_curr_code,
3394 p_exchange_rate,
3395 pg_base_precision,
3396 pg_base_min_acc_unit);
3397 EXCEPTION
3398 WHEN NO_DATA_FOUND THEN
3399 arp_util.debug('arp_process_credit.update_line '||
3400 'no distributions updated');
3401 WHEN OTHERS THEN
3402 RAISE;
3403 END;
3404 END IF;
3405 END IF;
3406
3407 arp_util.debug('l_tax_status : '||l_tax_status);
3408 arp_util.debug('l_frt_status : '||l_frt_status);
3409 arp_util.debug('l_rerun_aa_status : '||l_rerun_aa_status);
3410
3411 IF (NVL(l_tax_status, 'OK') <> 'OK') THEN
3412 p_status := l_tax_status;
3413 ELSIF (NVL(l_frt_status, 'OK') <> 'OK') THEN
3414 p_status := l_frt_status;
3415 ELSIF (NVL(l_rerun_aa_status, 'OK') <> 'OK') THEN
3416 p_status := l_rerun_aa_status;
3417 END IF;
3418
3419 arp_util.debug('arp_process_credit.update_line()-');
3420
3421 EXCEPTION
3422 WHEN OTHERS THEN
3423 arp_util.debug('EXCEPTION: arp_process_credit.update_line');
3424 RAISE;
3425 END;
3426 /*===========================================================================+
3427 | PROCEDURE |
3428 | freight_post_update |
3429 | |
3430 | DESCRIPTION |
3431 | Post update logic for processing freight for CMs |
3432 | |
3433 | SCOPE - PRIVATE |
3434 | |
3435 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3436 | arp_util.debug |
3437 | |
3438 | ARGUMENTS : IN: |
3439 | OUT: |
3440 | IN/ OUT: |
3441 | |
3442 | RETURNS : NONE |
3443 | |
3444 | NOTES |
3445 | |
3446 | MODIFICATION HISTORY |
3447 | 12-JUL-95 Subash Chadalavada Created |
3448 | |
3449 +===========================================================================*/
3450
3451 PROCEDURE freight_post_update(
3452 p_frt_rec IN ra_customer_trx_lines%rowtype,
3453 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
3454 p_frt_ccid IN
3455 ra_cust_trx_line_gl_dist.code_combination_id%type)
3456 IS
3457 l_ccid
3458 ra_cust_trx_line_gl_dist.code_combination_id%type;
3459 l_concat_segments varchar2(200);
3460 l_num_failed_dist_rows number;
3461 l_rows_processed number;
3462 l_errorbuf varchar2(200);
3463 l_result number;
3464 BEGIN
3465 arp_util.debug('arp_process_credit.freight_post_update()+');
3466
3467 -- replace this with the call to the CREDIT MEMO procedure
3468 -- update the distribution record
3469
3470 BEGIN
3471 arp_auto_accounting.do_autoaccounting(
3472 'U',
3473 'FREIGHT',
3474 p_frt_rec.customer_trx_id,
3475 p_frt_rec.customer_trx_line_id,
3476 null,
3477 null,
3478 p_gl_date,
3479 null,
3480 p_frt_rec.extended_amount,
3481 p_frt_ccid,
3482 null,
3483 null,
3484 null,
3485 null,
3486 null,
3487 l_ccid,
3488 l_concat_segments,
3489 l_num_failed_dist_rows );
3490 EXCEPTION
3491 WHEN arp_auto_accounting.no_ccid THEN
3492 null;
3493 WHEN OTHERS THEN
3494 RAISE;
3495 END;
3496
3497
3498 arp_util.debug('arp_process_credit.freight_post_update()-');
3499
3500 EXCEPTION
3501
3502 WHEN OTHERS THEN
3503 arp_util.debug('EXCEPTION: arp_process_credit.freight_post_update()');
3504
3505 arp_util.debug('p_customer_trx_line_id : '||p_frt_rec.customer_trx_line_id);
3506 arp_util.debug('p_frt_ccid : '||p_frt_ccid);
3507 arp_util.debug('p_gl_date : '||p_gl_date);
3508
3509 END;
3510
3511
3512 PROCEDURE init IS
3513 BEGIN
3514
3515 pg_number_dummy := arp_ctl_pkg.get_number_dummy;
3516 pg_date_dummy := arp_ct_pkg.get_date_dummy;
3517 pg_flag_dummy := arp_ct_pkg.get_flag_dummy;
3518 pg_name_dummy := arp_ctl_pkg.get_text_dummy;
3519
3520 pg_earliest_date := to_date('01/01/1901', 'DD/MM/YYYY');
3521
3522 pg_base_curr_code := arp_trx_global.system_info.base_currency;
3523 pg_base_precision := arp_trx_global.system_info.base_precision;
3524 pg_base_min_acc_unit := arp_trx_global.system_info.base_min_acc_unit;
3525 pg_salesrep_required_flag :=
3526 arp_trx_global.system_info.system_parameters.salesrep_required_flag;
3527
3528 EXCEPTION
3529 WHEN OTHERS THEN
3530 arp_util.debug('EXCEPTION: arp_process_credit.initialization');
3531 RAISE;
3532 END init;
3533
3534 BEGIN
3535 init;
3536 END ARP_PROCESS_CREDIT;