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