[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_HEADER
Source
1 PACKAGE BODY ARP_PROCESS_HEADER AS
2 /* $Header: ARTEHEAB.pls 120.32.12020000.2 2012/07/10 00:40:12 dgaurab ship $ */
3
4 pg_tax_flag varchar2(10);
5 pg_text_dummy varchar2(10);
6 pg_flag_dummy varchar2(10);
7 pg_number_dummy number;
8 pg_date_dummy date;
9 pg_earliest_date date;
10
11 pg_base_curr_code gl_sets_of_books.currency_code%type;
12 pg_base_precision fnd_currencies.precision%type;
13 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
14 pg_set_of_books_id ar_system_parameters.set_of_books_id%type;
15
16 /*3609567*/
17 pg_trx_header_level_rounding ar_system_parameters.TRX_HEADER_LEVEL_ROUNDING%TYPE;
18
19 pg_use_inv_acctg varchar2(1);
20
21 /*===========================================================================+
22 | PROCEDURE |
23 | validate_insert_header |
24 | |
25 | DESCRIPTION |
26 | Validates row that is going to be inserted into ra_customer_trx. |
27 | |
28 | SCOPE - PRIVATE |
29 | |
30 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
31 | arp_util.debug |
32 | |
33 | ARGUMENTS : IN: l_trx_rec |
34 | OUT: l_status |
35 | IN/ OUT: |
36 | |
37 | RETURNS : NONE |
38 | |
39 | NOTES |
40 | |
41 | MODIFICATION HISTORY |
42 | 13-JUL-95 Martin Johnson Created |
43 | 13-MAY-99 Srihari Koukuntla Modified for BugNo :860294 |
44 | added to i/p parameters l_trx_rec and l_status |
45 | to validate complete_flag |
46 | |
47 +===========================================================================*/
48
49 PROCEDURE validate_insert_header( l_trx_rec IN ra_customer_trx%rowtype,
50 l_status OUT NOCOPY varchar2 ) IS
51
52 BEGIN
53
54 arp_util.debug('arp_process_header.validate_insert_header()+');
55 if l_trx_rec.complete_flag is null then
56 arp_util.debug('Complete flag cannot be null,Valid values are Y or N');
57 l_status := 'E';
58 return;
59 end if;
60 arp_util.debug('arp_process_header.validate_insert_header()-');
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 arp_util.debug('EXCEPTION: arp_process_header.validate_insert_header()');
65 RAISE;
66
67 END;
68
69
70 /*===========================================================================+
71 | PROCEDURE |
72 | validate_update_header |
73 | |
74 | DESCRIPTION |
75 | Validates row that is going to be updated in ra_customer_trx. |
76 | |
77 | SCOPE - PRIVATE |
78 | |
79 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
80 | arp_util.debug |
81 | |
82 | ARGUMENTS : IN: |
83 | OUT: |
84 | IN/ OUT: |
85 | |
86 | RETURNS : NONE |
87 | |
88 | NOTES |
89 | |
90 | MODIFICATION HISTORY |
91 | 17-JUL-95 Martin Johnson Created |
92 | |
93 +===========================================================================*/
94
95 PROCEDURE validate_update_header IS
96
97 BEGIN
98
99 arp_util.debug('arp_process_header.validate_update_header()+');
100
101 arp_util.debug('arp_process_header.validate_update_header()-');
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 arp_util.debug('EXCEPTION: arp_process_header.validate_update_header()');
106 RAISE;
107
108 END;
109
110 /*===========================================================================+
111 | PROCEDURE |
112 | validate_delete_header |
113 | |
114 | DESCRIPTION |
115 | Validates row that is going to be delete from ra_customer_trx. |
116 | |
117 | SCOPE - PRIVATE |
118 | |
119 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
120 | arp_util.debug |
121 | |
122 | ARGUMENTS : IN: |
123 | OUT: |
124 | IN/ OUT: |
125 | |
126 | RETURNS : NONE |
127 | |
128 | NOTES |
129 | |
130 | MODIFICATION HISTORY |
131 | 26-JUL-95 Martin Johnson Created |
132 | |
133 +===========================================================================*/
134
135 PROCEDURE validate_delete_header IS
136
137 BEGIN
138
139 arp_util.debug('arp_process_header.validate_delete_header()+');
140
141 arp_util.debug('arp_process_header.validate_delete_header()-');
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 arp_util.debug('EXCEPTION: arp_process_header.validate_delete_header()');
146 RAISE;
147
148 END;
149
150 /*===========================================================================+
151 | PROCEDURE |
152 | set_flags |
153 | |
154 | DESCRIPTION |
155 | Sets various change and status flags for the current record. |
156 | |
157 | SCOPE - PRIVATE |
158 | |
159 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
160 | arp_util.debug |
161 | |
162 | ARGUMENTS : IN: |
163 | p_customer_trx_id |
164 | p_new_trx_rec |
165 | p_new_gl_date |
166 | p_new_open_rec_flag |
167 | pd_dispute_date
168 | OUT: |
169 | p_ex_rate_changed_flag |
170 | p_commitment_changed_flag |
171 | p_gl_date_changed_flag |
172 | p_complete_changed_flag |
173 | p_open_rec_changed_flag |
174 | IN/ OUT: |
175 | None |
176 | |
177 | RETURNS : NONE |
178 | |
179 | NOTES |
180 | |
181 | MODIFICATION HISTORY |
182 | 17-JUL-95 Martin Johnson Created |
183 | 10/10/1996 Harri Kaukovuo Fixed bug 411036. Updating dispute
184 | date did not work.
185 | Added code to check whether dispute date
186 | has changes.
187 | 11-MAR-05 M Raymond Bug 4233770/4235243 - added exception block
188 | for SELECT that fetches gl_date
189 +===========================================================================*/
190
191 PROCEDURE set_flags(p_customer_trx_id IN
192 ra_customer_trx.customer_trx_id%type,
193 p_new_trx_rec IN ra_customer_trx%rowtype,
194 p_new_gl_date IN
195 ra_cust_trx_line_gl_dist.gl_date%type,
196 p_new_open_rec_flag IN
197 ra_cust_trx_types.accounting_affect_flag%type,
198 p_ps_dispute_amount IN
199 ar_payment_schedules.amount_in_dispute%type,
200 pd_dispute_date IN DATE,
201 p_ex_rate_changed_flag OUT NOCOPY boolean,
202 p_commitment_changed_flag OUT NOCOPY boolean,
203 p_gl_date_changed_flag OUT NOCOPY boolean,
204 p_complete_changed_flag OUT NOCOPY boolean,
205 p_open_rec_changed_flag OUT NOCOPY boolean,
206 p_dispute_changed_flag OUT NOCOPY boolean,
207 p_number_of_payment_schedules OUT NOCOPY NUMBER,
208 p_old_trx_rec OUT NOCOPY ra_customer_trx%rowtype,
209 p_cust_trx_type_changed_flag OUT NOCOPY boolean)
210 IS
211
212 l_old_trx_rec ra_customer_trx%rowtype;
213 l_old_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
214 l_old_open_rec_flag ra_cust_trx_types.accounting_affect_flag%type;
215 l_old_dispute_amount ar_payment_schedules.amount_in_dispute%type;
216 ld_old_dispute_date DATE;
217
218 BEGIN
219
220 arp_util.debug('arp_process_header.set_flags()+');
221
222 arp_ct_pkg.fetch_p(l_old_trx_rec,
223 p_customer_trx_id);
224
225 p_old_trx_rec := l_old_trx_rec;
226
227 /* Bug 4233770/4235243 - added exception handling */
228 BEGIN
229
230 select gl_date
231 into l_old_gl_date
232 from ra_cust_trx_line_gl_dist
233 where customer_trx_id = p_customer_trx_id
234 and account_class = 'REC'
235 and latest_rec_flag = 'Y';
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 /* Bug 4233770/4235243 - no data in gl_dist table. This happens
240 for freight lines coming through invoice API.
241 Clearly, there is no assigned gl_date yet */
242 arp_standard.debug('No rows in ra_cust_trx_line_gl_dist');
243 WHEN OTHERS THEN
244 RAISE;
245 END;
246
247 IF (
248 nvl(l_old_trx_rec.exchange_rate, 0) <>
249 nvl(p_new_trx_rec.exchange_rate, 0)
250 AND
251 nvl(p_new_trx_rec.exchange_rate, 0) <> pg_number_dummy
252 )
253 THEN p_ex_rate_changed_flag := TRUE;
254 ELSE p_ex_rate_changed_flag := FALSE;
255 END IF;
256
257 IF (
258 nvl(l_old_trx_rec.initial_customer_trx_id, 0) <>
259 nvl(p_new_trx_rec.initial_customer_trx_id, 0)
260 AND
261 nvl(p_new_trx_rec.initial_customer_trx_id, 0) <> pg_number_dummy
262 )
263 THEN p_commitment_changed_flag := TRUE;
264 ELSE p_commitment_changed_flag := FALSE;
265 END IF;
266
267 IF (
268 nvl(l_old_gl_date, pg_earliest_date) <>
269 nvl(p_new_gl_date, pg_earliest_date)
270 AND
271 nvl(p_new_gl_date, pg_earliest_date) <> pg_date_dummy
272 )
273 THEN p_gl_date_changed_flag := TRUE;
274 ELSE p_gl_date_changed_flag := FALSE;
275 END IF;
276
277 IF (
278 nvl(l_old_trx_rec.complete_flag, 'x') <>
279 nvl(p_new_trx_rec.complete_flag, 'x')
280 AND
281 nvl(p_new_trx_rec.complete_flag, 'x') <> pg_flag_dummy
282 )
283 THEN p_complete_changed_flag := TRUE;
284 ELSE p_complete_changed_flag := FALSE;
285 END IF;
286
287 IF (
288 nvl(l_old_trx_rec.cust_trx_type_id, 0) <>
289 nvl(p_new_trx_rec.cust_trx_type_id, 0)
290 AND
291 nvl(p_new_trx_rec.cust_trx_type_id, 0) <> pg_number_dummy
292 )
293 THEN
294 select accounting_affect_flag
295 into l_old_open_rec_flag
296 from ra_cust_trx_types
297 where cust_trx_type_id = l_old_trx_rec.cust_trx_type_id;
298
299 IF l_old_open_rec_flag <> p_new_open_rec_flag
300 THEN p_open_rec_changed_flag := TRUE;
301 ELSE p_open_rec_changed_flag := FALSE;
302 END IF;
303
304 ELSE p_open_rec_changed_flag := FALSE;
305 END IF;
306
307 -- 10/10/1996 H.Kaukovuo Added dispute date to the selection
308 IF (p_ps_dispute_amount IS NULL)
309 THEN p_dispute_changed_flag := FALSE;
310 ELSE
311 SELECT SUM( NVL(ps.amount_in_dispute,0) ),
312 COUNT(*)
313 , MAX(ps.dispute_date)
314 INTO l_old_dispute_amount,
315 p_number_of_payment_schedules
316 , ld_old_dispute_date
317 FROM ar_payment_schedules ps
318 WHERE ps.customer_trx_id = p_customer_trx_id;
319
320 -- Return true if amount or date was changed
321 IF (p_ps_dispute_amount <> l_old_dispute_amount
322 OR ld_old_dispute_date <> pd_dispute_date)
323 THEN
324 p_dispute_changed_flag := TRUE;
325 ELSE
326 p_dispute_changed_flag := FALSE;
327 END IF;
328
329 END IF;
330
331 -- 10/9/97: Added by OSTEINME for bug 446263
332
333 IF (p_new_trx_rec.cust_trx_type_id <> l_old_trx_rec.cust_trx_type_id) THEN
334 p_cust_trx_type_changed_flag := TRUE;
335 ELSE
336 p_cust_trx_type_changed_flag := FALSE;
337 END IF;
338
339 arp_util.debug('arp_process_header.set_flags()-');
340
341
342 EXCEPTION
343 WHEN OTHERS THEN
344
345 /*---------------------------------------------+
346 | Display parameters and raise the exception |
347 +---------------------------------------------*/
348
349 arp_util.debug('EXCEPTION: arp_process_header.set_flags()');
350
351 arp_util.debug('');
352 arp_util.debug('---------- parameters for set_flags() ---------');
353
354 arp_util.debug('p_customer_trx_id = ' || p_customer_trx_id);
355 arp_util.debug('p_new_gl_date = ' || p_new_gl_date);
356 arp_util.debug('p_new_open_rec_flag = ' || p_new_open_rec_flag);
357 arp_util.debug('p_ps_dispute_amount = ' || TO_CHAR(p_ps_dispute_amount));
358 arp_util.debug('');
359
360 arp_util.debug('---------- new transaction record ----------');
361 arp_ct_pkg.display_header_rec( p_new_trx_rec );
362 arp_util.debug('');
363
364 RAISE;
365
366 END;
367
368 /*===========================================================================+
369 | PROCEDURE |
370 | header_rerun_aa |
371 | |
372 | DESCRIPTION |
373 | reruns autoaccounting at the header level |
374 | |
375 | SCOPE - PRIVATE |
376 | |
377 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
378 | arp_util.debug |
379 | |
380 | ARGUMENTS : IN: |
381 | OUT: |
382 | IN/ OUT: |
383 | None |
384 | |
385 | RETURNS : NONE |
386 | |
387 | NOTES |
388 | |
389 | MODIFICATION HISTORY |
390 | 21-JUL-95 Martin Johnson Created |
391 | |
392 +===========================================================================*/
393
394 PROCEDURE header_rerun_aa(p_customer_trx_id IN number,
395 p_gl_date IN
396 ra_cust_trx_line_gl_dist.gl_date%type,
397 p_total_trx_amount IN number,
398 p_status OUT NOCOPY varchar2) IS
399
400 l_result number;
401 l_ccid number;
402 l_concat_segments varchar2(2000);
403 l_num_failed_dist_rows number;
404 l_errorbuf varchar2(200);
405 l_status1 varchar2(100);
406 l_status2 varchar2(100);
407 l_status3 varchar2(100);
408 l_status4 varchar2(100);
409 l_status5 varchar2(100);
410 l_status6 varchar2(100);
411 l_status7 varchar2(100);
412
413 l_event_source_info xla_events_pub_pkg.t_event_source_info;
414 l_security xla_events_pub_pkg.t_security;
415 l_event_id NUMBER;
416
417
418 CURSOR c_ct IS
419 SELECT distinct gld.event_id event_id
420 FROM ra_cust_trx_line_gl_dist gld, ra_customer_trx ra
421 WHERE gld.customer_trx_id = p_customer_trx_id
422 and gld.customer_trx_id = ra.customer_trx_id
423 and ra.invoicing_rule_id is NULL
424 and gld.gl_date <> p_gl_date
425 and gld.account_class = 'REC'
426 and gld.posting_control_id = -3
427 and gld.account_set_flag = 'N'
428 AND gld.event_id is not null
429 AND exists
430 (Select 1 from xla_events
431 where entity_id in (
432 Select entity_id from xla_transaction_entities
433 where entity_code = 'TRANSACTIONS'
434 and nvl(source_id_int_1 , -99) = ra.customer_trx_id
435 and ledger_id = ra.set_of_books_id
436 and application_id = 222 ));
437
438
439 BEGIN
440
441 arp_util.debug('arp_process_header.header_rerun_aa()+');
442
443
444 -- Bug9005547
445 -- Delete the existing event_id that has been latched to the GLD rows:
446 BEGIN
447 arp_util.debug('header_rerun_a: Deleting existing events');
448
449 FOR c IN c_ct loop
450
451 l_event_id := c.event_id;
452
453 l_event_source_info.entity_type_code:= 'TRANSACTIONS';
454 l_security.security_id_int_1 := arp_global.sysparam.org_id;
455 l_event_source_info.application_id := 222;
456 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id;
457 l_event_source_info.source_id_int_1 := p_customer_trx_id;
458
459 xla_events_pub_pkg.delete_event
460 ( p_event_source_info => l_event_source_info,
461 p_event_id => l_event_id,
462 p_valuation_method => NULL,
463 p_security_context => l_security);
464
465 END loop;
466
467 arp_util.debug('header_rerun_a: Completed deleting existing events');
468 EXCEPTION
469 WHEN OTHERS THEN
470 arp_util.debug('EXCEPTION: header_rerun_aa : delete events'||SQLERRM);
471 RAISE;
472 END;
473
474 BEGIN
475 arp_auto_accounting.do_autoaccounting(
476 'U',
477 'REC',
478 p_customer_trx_id,
479 null,
480 null,
481 null,
482 p_gl_date,
483 null,
484 p_total_trx_amount,
485 null,
486 null,
487 null,
488 null,
489 null,
490 null,
491 l_ccid,
492 l_concat_segments,
493 l_num_failed_dist_rows);
494 EXCEPTION
495 WHEN arp_auto_accounting.no_ccid THEN
496 l_status1 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
497
498 WHEN NO_DATA_FOUND THEN
499 null;
500 WHEN OTHERS THEN
501 RAISE;
502 END;
503
504
505 BEGIN
506 arp_auto_accounting.do_autoaccounting(
507 'U',
508 'REV',
509 p_customer_trx_id,
510 null,
511 null,
512 null,
513 p_gl_date,
514 null,
515 null,
516 null,
517 null,
518 null,
519 null,
520 null,
521 null,
522 l_ccid,
523 l_concat_segments,
524 l_num_failed_dist_rows);
525 EXCEPTION
526 WHEN arp_auto_accounting.no_ccid THEN
527 l_status2 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
528
529 WHEN NO_DATA_FOUND THEN
530 null;
531 WHEN OTHERS THEN
532 RAISE;
533 END;
534
535 /* bug 842360 : added next 2 calls to do_autoaccounting for UNEARN and UNBILL */
536 BEGIN
537 arp_auto_accounting.do_autoaccounting(
538 'U',
539 'UNEARN',
540 p_customer_trx_id,
541 null,
542 null,
543 null,
544 p_gl_date,
545 null,
546 p_total_trx_amount,
547 null,
548 null,
549 null,
550 null,
551 null,
552 null,
553 l_ccid,
554 l_concat_segments,
555 l_num_failed_dist_rows);
556 EXCEPTION
557 WHEN arp_auto_accounting.no_ccid THEN
558 l_status6 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
559
560 WHEN NO_DATA_FOUND THEN
561 null;
562 WHEN OTHERS THEN
563 RAISE;
564 END;
565
566 BEGIN
567 arp_auto_accounting.do_autoaccounting(
568 'U',
569 'UNBILL',
570 p_customer_trx_id,
571 null,
572 null,
573 null,
574 p_gl_date,
575 null,
576 p_total_trx_amount,
577 null,
578 null,
579 null,
580 null,
581 null,
582 null,
583 l_ccid,
584 l_concat_segments,
585 l_num_failed_dist_rows);
586 EXCEPTION
587 WHEN arp_auto_accounting.no_ccid THEN
588 l_status7 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
589
590 WHEN NO_DATA_FOUND THEN
591 null;
592 WHEN OTHERS THEN
593 RAISE;
594 END;
595
596 BEGIN
597 arp_auto_accounting.do_autoaccounting(
598 'U',
599 'CHARGES',
600 p_customer_trx_id,
601 null,
602 null,
603 null,
604 p_gl_date,
605 null,
606 null,
607 null,
608 null,
609 null,
610 null,
611 null,
612 null,
613 l_ccid,
614 l_concat_segments,
615 l_num_failed_dist_rows);
616 EXCEPTION
617 WHEN arp_auto_accounting.no_ccid THEN
618 l_status3 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
619
620 WHEN NO_DATA_FOUND THEN
621 null;
622 WHEN OTHERS THEN
623 RAISE;
624 END;
625
626
627 BEGIN
628 arp_auto_accounting.do_autoaccounting(
629 'U',
630 'TAX',
631 p_customer_trx_id,
632 null,
633 null,
634 null,
635 p_gl_date,
636 null,
637 null,
638 null,
639 null,
640 null,
641 null,
642 null,
643 null,
644 l_ccid,
645 l_concat_segments,
646 l_num_failed_dist_rows);
647 EXCEPTION
648 WHEN arp_auto_accounting.no_ccid THEN
649 l_status4 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
650
651 WHEN NO_DATA_FOUND THEN
652 null;
653 WHEN OTHERS THEN
654 RAISE;
655 END;
656
657
658 BEGIN
659 arp_auto_accounting.do_autoaccounting(
660 'U',
661 'FREIGHT',
662 p_customer_trx_id,
663 null,
664 null,
665 null,
666 p_gl_date,
667 null,
668 null,
669 null,
670 null,
671 null,
672 null,
673 null,
674 null,
675 l_ccid,
676 l_concat_segments,
677 l_num_failed_dist_rows);
678 EXCEPTION
679 WHEN arp_auto_accounting.no_ccid THEN
680 l_status5 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
681
682 WHEN NO_DATA_FOUND THEN
683 null;
684 WHEN OTHERS THEN
685 RAISE;
686 END;
687
688 arp_util.debug('l_status1 = ' || l_status1);
689 arp_util.debug('l_status2 = ' || l_status2);
690 arp_util.debug('l_status3 = ' || l_status3);
691 arp_util.debug('l_status4 = ' || l_status4);
692 arp_util.debug('l_status5 = ' || l_status5);
693 arp_util.debug('l_status6 = ' || l_status6);
694 arp_util.debug('l_status7 = ' || l_status7);
695
696 IF ( NVL(l_status1, 'OK') <> 'OK' )
697 THEN p_status := l_status1;
698 ELSIF ( NVL(l_status2, 'OK') <> 'OK' )
699 THEN p_status := l_status2;
700 ELSIF ( NVL(l_status3, 'OK') <> 'OK' )
701 THEN p_status := l_status3;
702 ELSIF ( NVL(l_status4, 'OK') <> 'OK' )
703 THEN p_status := l_status4;
704 ELSIF ( NVL(l_status5, 'OK') <> 'OK' )
705 THEN p_status := l_status5;
706 ELSIF ( NVL(l_status6, 'OK') <> 'OK' )
707 THEN p_status := l_status6;
708 ELSIF ( NVL(l_status7, 'OK') <> 'OK' )
709 THEN p_status := l_status7;
710 ELSE p_status := 'OK';
711 END IF;
712
713 arp_util.debug('arp_process_header.header_rerun_aa()-');
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 arp_util.debug('EXCEPTION: arp_process_header.header_rerun_aa()');
718 RAISE;
719
720 END;
721
722 /* Bug 2689013 */
723 /*===========================================================================+
724 | PROCEDURE |
725 | reverse_revrec_effect |
726 | |
727 | DESCRIPTION |
728 | When a transaction with rule is incompleted, we will now reverse the |
729 | effect of revenue recognition on the transaction, if it is already run.|
730 | |
731 | SCOPE - PRIVATE |
732 | |
733 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
734 | arp_util.debug |
735 | ARGUMENTS : |
736 | IN : p_customer_trx_id |
737 | OUT: |
738 | IN/ OUT: |
739 | |
740 | RETURNS : NONE |
741 | |
742 | NOTES |
743 | |
744 | MODIFICATION HISTORY |
745 | 25-JUL-03 Veena Rao Created |
746 | |
747 +===========================================================================*/
748
749 PROCEDURE reverse_revrec_effect (
750 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type
751 ) IS
752
753 l_line_rec ra_customer_trx_lines%rowtype;
754 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
755 BEGIN
756
757 arp_util.debug('arp_process_header.reverse_revrec_effect()+');
758
759 arp_ctl_pkg.set_to_dummy( l_line_rec );
760
761 l_line_rec.autorule_complete_flag := 'N';
762 l_line_rec.autorule_duration_processed := NULL;
763
764 BEGIN
765 arp_ctl_pkg.update_f_ct_id( l_line_rec,
766 p_customer_trx_id,
767 'LINE');
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 arp_util.debug('arp_process_header..reverse_revrec_effect: '||
771 'no child lines to update.');
772 WHEN OTHERS THEN
773 arp_util.debug('EXCEPTION: '||
774 'arp_process_header..reverse_revrec_effect()');
775 RAISE;
776 END;
777
778 BEGIN
779
780 --6870437
781 ARP_XLA_EVENTS.delete_reverse_revrec_event( p_document_id => p_customer_trx_id,
782 p_doc_table => 'CT');
783
784 arp_ctlgd_pkg.delete_f_ct_id(p_customer_trx_id,
785 'N',
786 NULL);
787 EXCEPTION
788 WHEN NO_DATA_FOUND THEN
789 arp_util.debug('arp_process_header.reverse_revrec_effect: '||
790 'no dists to delete.');
791 WHEN OTHERS THEN
792 arp_util.debug('EXCEPTION: '||
793 'arp_process_header.reverse_revrec_effect()');
794 RAISE;
795 END;
796
797 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
798 l_dist_rec.latest_rec_flag := 'Y';
799 BEGIN
800 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
801 p_customer_trx_id,
802 'Y',
803 'REC');
804 EXCEPTION
805 WHEN NO_DATA_FOUND THEN
806 arp_util.debug('arp_process_header.reverse_revrec_effect: '||
807 'no dists to update.');
808 WHEN OTHERS THEN
809 arp_util.debug('EXCEPTION: '||
810 'arp_process_header.reverse_revrec_effect()');
811 RAISE;
812 END;
813 EXCEPTION
814 WHEN OTHERS THEN
815 arp_util.debug('EXCEPTION: '||
816 'arp_process_header.reverse_revrec_effect()');
817 RAISE;
818 END ;
819 /*===========================================================================+
820 | PROCEDURE |
821 | insert_header |
822 | |
823 | DESCRIPTION |
824 | Inserts a record into ra_customer_trx. |
825 | |
826 | SCOPE - PUBLIC |
827 | |
828 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
829 | arp_util.debug |
830 | |
831 | ARGUMENTS : IN: |
832 | p_form_name |
833 | p_form_version |
834 | p_trx_rec |
835 | p_trx_class |
836 | p_gl_date |
837 | p_term_in_use_flag |
838 | p_commitment_rec |
839 | OUT: |
840 | p_trx_number |
841 | p_customer_trx_id |
842 | p_status |
843 | IN/ OUT: |
844 | |
845 | RETURNS : NONE |
846 | |
847 | NOTES |
848 | |
849 | MODIFICATION HISTORY |
850 | 11-JUL-95 Martin Johnson Created |
851 | |
852 +===========================================================================*/
853
854 PROCEDURE insert_header(
855 p_form_name IN varchar2,
856 p_form_version IN number,
857 p_trx_rec IN ra_customer_trx%rowtype,
858 p_trx_class IN ra_cust_trx_types.type%type,
859 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
860 p_term_in_use_flag IN varchar2,
861 p_commitment_rec IN arp_process_commitment.commitment_rec_type,
862 p_trx_number OUT NOCOPY ra_customer_trx.trx_number%type,
863 p_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type,
864 p_customer_trx_line_id OUT NOCOPY ra_customer_trx_lines.customer_trx_line_id%type,
865 p_row_id OUT NOCOPY rowid,
866 p_status OUT NOCOPY varchar2,
867 p_receivable_ccid IN gl_code_combinations.code_combination_id%type
868 DEFAULT NULL,
869 p_run_autoacc_flag IN varchar2 DEFAULT 'Y',
870 p_create_default_sc_flag IN varchar2 DEFAULT 'Y' )
871
872 IS
873
874 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
875 l_result number;
876 l_ccid number;
877 l_concat_segments varchar2(2000);
878 l_num_failed_dist_rows number;
879 l_errorbuf varchar2(200);
880
881 l_remit_to_address_rec arp_trx_defaults_3.address_rec_type;
882 l_trx_rec ra_customer_trx%rowtype;
883 l_status1 varchar2(100);
884 l_status2 varchar2(100);
885 --Bug# 2750340
886 l_ev_rec arp_xla_events.xla_events_type;
887
888 /* bug 3609567 */
889 l_error_message VARCHAR2(128) := '';
890 l_dist_count NUMBER;
891
892 BEGIN
893
894 arp_util.debug('arp_process_header.insert_header()+');
895
896 p_trx_number := '';
897 p_customer_trx_id := '';
898 l_trx_rec := p_trx_rec;
899
900 -- check form version to determine if it is compatible with the
901 -- entity handler.
902 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
903
904 -- do validation
905 p_status := 'S';
906 validate_insert_header(p_trx_rec,p_status);
907 if p_status = 'E' then
908 arp_util.debug('Failed in validate insert header');
909 return;
910 end if;
911
912 /*--------------------+
913 | pre-insert logic |
914 +--------------------*/
915
916
917 /*---------------------------------------------------------------------+
918 | IF the remit to address is null |
919 | AND the transaction is not a credit memo |
920 | THEN try to derive the remit to address from the bill to address |
921 | or from the default remit to address. |
922 | |
923 | If no remit to address can be derived, the procedure raises a |
924 | NO_DATA_FOUND error. Ignore this error. |
925 +---------------------------------------------------------------------*/
926
927 IF ( l_trx_rec.remit_to_address_id IS NULL ) AND
928 ( p_trx_class <> 'CM' )
929 THEN
930 BEGIN
931 arp_trx_defaults_3.get_remit_to_address(
932 null,
933 null,
934 null,
935 null,
936 l_trx_rec.bill_to_site_use_id,
937 l_trx_rec.remit_to_address_id,
938 l_remit_to_address_rec
939 );
940 EXCEPTION
941 WHEN NO_DATA_FOUND THEN
942 null;
943 WHEN OTHERS THEN RAISE;
944 END;
945
946 END IF;
947
948 /*------------------------------------------------+
949 | IF the printing option is null |
950 | THEN default it from the transaction type |
951 | or set it to 'Print'. |
952 +------------------------------------------------*/
953
954 IF ( l_trx_rec.printing_option IS NULL )
955 THEN
956
957 SELECT NVL( default_printing_option, 'PRI' )
958 INTO l_trx_rec.printing_option
959 FROM ra_cust_trx_types
960 WHERE cust_trx_type_id = l_trx_rec.cust_trx_type_id;
961
962 END IF;
963
964
965 IF p_trx_class in ('DEP', 'GUAR')
966 THEN arp_process_commitment.header_pre_insert;
967 -- does commitment validation
968 END IF;
969
970
971 /*----------------------+
972 | call table-handler |
973 +----------------------*/
974
975 arp_ct_pkg.insert_p(l_trx_rec, p_trx_number, l_customer_trx_id);
976
977 p_customer_trx_id := l_customer_trx_id;
978
979
980 /*---------------------+
981 | post-insert logic |
982 +---------------------*/
983
984 IF p_trx_class in ('DEP', 'GUAR')
985 THEN arp_process_commitment.header_post_insert(
986 l_customer_trx_id,
987 p_commitment_rec,
988 l_trx_rec.primary_salesrep_id,
989 p_gl_date,
990 p_customer_trx_line_id,
991 l_status1 );
992
993 ELSE arp_process_invoice.header_post_insert(
994 l_trx_rec.primary_salesrep_id,
995 l_customer_trx_id,
996 p_create_default_sc_flag);
997 END IF;
998
999 -- Call AutoAccounting to insert the gl dist record for Receivable
1000
1001 IF ( p_run_autoacc_flag = 'Y' )
1002 THEN
1003
1004 BEGIN
1005
1006 arp_auto_accounting.do_autoaccounting(
1007 'I',
1008 'REC',
1009 l_customer_trx_id,
1010 null,
1011 null,
1012 null,
1013 p_gl_date,
1014 null,
1015 nvl(p_commitment_rec.extended_amount, 0),
1016 p_receivable_ccid,
1017 null,
1018 null,
1019 null,
1020 null,
1021 null,
1022 l_ccid,
1023 l_concat_segments,
1024 l_num_failed_dist_rows);
1025 /* Bug 3609567 */
1026 IF arp_rounding.correct_dist_rounding_errors(
1027 NULL,
1028 l_customer_trx_id ,
1029 NULL,
1030 l_dist_count,
1031 l_error_message ,
1032 pg_base_precision ,
1033 pg_base_min_acc_unit ,
1034 'ALL' ,
1035 NULL,
1036 'N' ,
1037 pg_trx_header_level_rounding ,
1038 'N',
1039 'N') = 0 -- FALSE
1040 THEN
1041 arp_util.debug('EXCEPTION: Insert_Header');
1042 arp_util.debug(l_error_message);
1043 fnd_message.set_name('AR', 'AR_PLCRE_FHLR_CCID');
1044 APP_EXCEPTION.raise_exception;
1045 END IF;
1046
1047 EXCEPTION
1048 WHEN arp_auto_accounting.no_ccid THEN
1049 l_status2 := 'ARP_AUTO_ACCOUNTING.NO_CCID';
1050 WHEN NO_DATA_FOUND THEN
1051 null;
1052 WHEN OTHERS THEN
1053 RAISE;
1054 END;
1055
1056 END IF;
1057
1058 -- update ra_terms.in_use
1059 arp_trx_util.set_term_in_use_flag(
1060 p_form_name,
1061 p_form_version,
1062 l_trx_rec.term_id,
1063 p_term_in_use_flag);
1064
1065 arp_util.debug('l_status1 = ' || l_status1);
1066 arp_util.debug('l_status2 = ' || l_status2);
1067
1068 IF ( NVL(l_status1, 'OK') <> 'OK' )
1069 THEN p_status := l_status1;
1070 ELSIF ( NVL(l_status2, 'OK') <> 'OK' )
1071 THEN p_status := l_status2;
1072 ELSE p_status := 'OK';
1073 END IF;
1074
1075 -- Bug# 2750340 : Call AR_XLA_EVENTS
1076 ------------------------------------------------------------
1077 -- This call to ARP_XLA_EVENT is required when
1078 -- user creates a document through the transaction Workbench
1079 ------------------------------------------------------------
1080 l_ev_rec.xla_from_doc_id := l_customer_trx_id;
1081 l_ev_rec.xla_to_doc_id := l_customer_trx_id;
1082 l_ev_rec.xla_req_id := NULL;
1083 l_ev_rec.xla_dist_id := NULL;
1084 l_ev_rec.xla_doc_table := 'CT';
1085 l_ev_rec.xla_doc_event := NULL;
1086 l_ev_rec.xla_mode := 'O';
1087 l_ev_rec.xla_call := 'B';
1088 l_ev_rec.xla_fetch_size := 999;
1089 arp_xla_events.create_events(p_xla_ev_rec => l_ev_rec );
1090
1091 arp_util.debug('arp_process_header.insert_header()-');
1092
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 arp_util.debug('EXCEPTION: arp_process_header.insert_header()');
1096 RAISE;
1097
1098 END;
1099
1100 /*===========================================================================+
1101 | PROCEDURE |
1102 | update_header |
1103 | |
1104 | DESCRIPTION |
1105 | Updates a record into ra_customer_trx. |
1106 | |
1107 | SCOPE - PUBLIC |
1108 | |
1109 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1110 | arp_util.debug |
1111 | |
1112 | ARGUMENTS : IN: |
1113 | OUT: |
1114 | IN/ OUT: |
1115 | |
1116 | RETURNS : NONE |
1117 | |
1118 | NOTES |
1119 | |
1120 | MODIFICATION HISTORY |
1121 | 17-JUL-95 Martin Johnson Created |
1122 | 29-NOV-95 Nigel Smith Added calls to Tax Engine. |
1123 | 10/10/1996 Harri Kaukovuo Added parameter pd_dispute_date
1124 | to set_flags().
1125 | Fixed bug when updating dispute amount
1126 | would cause the whole ar_payment_schedules
1127 | to be updated.
1128 +===========================================================================*/
1129
1130 PROCEDURE update_header(
1131 p_form_name IN varchar2,
1132 p_form_version IN number,
1133 p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
1134 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
1135 p_trx_amount IN number,
1136 p_trx_class IN ra_cust_trx_types.type%type,
1137 p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
1138 p_initial_customer_trx_line_id IN
1139 ra_customer_trx_lines.initial_customer_trx_line_id%type
1140 default null,
1141 p_commitment_rec IN arp_process_commitment.commitment_rec_type,
1142 p_open_rec_flag IN ra_cust_trx_types.accounting_affect_flag%type,
1143 p_term_in_use_flag IN varchar2,
1144 p_recalc_tax_flag IN boolean,
1145 p_rerun_autoacc_flag IN boolean,
1146 p_ps_dispute_amount IN NUMBER DEFAULT NULL,
1147 p_ps_dispute_date IN DATE DEFAULT NULL,
1148 p_status OUT NOCOPY varchar2)
1149
1150 IS
1151
1152 l_rerun_autoacc_flag boolean; /* Bug-3454082 - 4019170 */
1153 l_frt_only_rules boolean;
1154 l_ex_rate_changed_flag boolean;
1155 l_commitment_changed_flag boolean;
1156 l_gl_date_changed_flag boolean;
1157 l_complete_changed_flag boolean;
1158 l_open_rec_changed_flag boolean;
1159 l_dispute_changed_flag boolean;
1160 l_cust_trx_type_changed_flag boolean;
1161
1162 l_initial_customer_trx_line_id
1163 ra_customer_trx_lines.initial_customer_trx_line_id%type;
1164 l_exchange_rate ra_customer_trx.exchange_rate%type;
1165 l_invoice_currency_code ra_customer_trx.invoice_currency_code%type;
1166
1167 l_line_rec ra_customer_trx_lines%rowtype;
1168 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
1169
1170 l_old_trx_rec ra_customer_trx%rowtype;
1171
1172 l_number_of_pay_scheds NUMBER;
1173 l_new_tax_amount NUMBER;
1174 l_recalc_tax BOOLEAN;
1175 l_dummy_flag varchar2(1); /* Bug-3454082 - 4019170 */
1176
1177 l_status1 varchar2(100);
1178 l_status2 varchar2(100);
1179 l_status3 varchar2(100);
1180
1181 --Bug# 2750340
1182 l_ev_rec arp_xla_events.xla_events_type;
1183
1184 --BUG#5192414
1185 CURSOR cpost IS
1186 SELECT 'Y'
1187 FROM ra_cust_trx_line_gl_dist
1188 WHERE customer_trx_id = p_customer_trx_id
1189 AND posting_control_id <> -3
1190 AND account_set_flag = 'N';
1191 l_test VARCHAR2(1);
1192 --BUG#7366912
1193 l_event_source_info xla_events_pub_pkg.t_event_source_info;
1194 l_event_id NUMBER;
1195 l_security xla_events_pub_pkg.t_security;
1196 l_post_to_gl varchar2(100);
1197 l_event_status_code varchar2(100);
1198 BEGIN
1199
1200 arp_util.debug('arp_process_header.update_header()+');
1201
1202 -- check form version to determine if it is compatible with the
1203 -- entity handler.
1204 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
1205
1206 -- do validation
1207 validate_update_header;
1208
1209 -- Lock rows in other tables that reference this customer_trx_id
1210 /* Bug-3630210 - 3874863 Added the Exception class */
1211 Begin
1212 arp_trx_util.lock_transaction(p_customer_trx_id);
1213 Exception
1214 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1215 FND_MESSAGE.SET_NAME('AR','AR_TW_RECORD_LOCKED');
1216 app_exception.raise_exception;
1217 WHEN OTHERS THEN
1218 arp_util.debug('EXCEPTION: arp_trx_util.delete_lock()');
1219 Raise;
1220 END;
1221
1222 /*--------------------+
1223 | pre-update logic |
1224 +--------------------*/
1225
1226 -- If tax user exit is going to be called in update mode (if
1227 -- recalculate_tax_flag = 'Y'), call salestax delete. The Tax
1228 -- Vendor Code will then be able to "backout" these old tax rows.
1229 -- And, the subsequent call to AR SALESTAX UPDATE can be relied
1230 -- upon to just recalculate the new data. This allows the Tax
1231 -- Vendor Code to keep an audit trail.
1232
1233
1234 l_recalc_tax := p_recalc_tax_flag;
1235
1236
1237
1238 IF p_trx_class in ('DEP', 'GUAR')
1239 THEN arp_process_commitment.header_pre_update;
1240 -- does commitment validation
1241 END IF;
1242
1243 set_flags(p_customer_trx_id,
1244 p_trx_rec,
1245 p_gl_date,
1246 p_open_rec_flag,
1247 p_ps_dispute_amount,
1248 p_ps_dispute_date, -- pd_dispute_date
1249 l_ex_rate_changed_flag,
1250 l_commitment_changed_flag,
1251 l_gl_date_changed_flag,
1252 l_complete_changed_flag,
1253 l_open_rec_changed_flag,
1254 l_dispute_changed_flag,
1255 l_number_of_pay_scheds,
1256 l_old_trx_rec,
1257 l_cust_trx_type_changed_flag);
1258
1259 -- If transaction is a freight-only transaction with rules, then
1260 -- set invoicing_rule_id to null because autorule cannot handle freight
1261 -- only invoices with rules.
1262
1263 l_frt_only_rules := FALSE;
1264
1265 IF l_complete_changed_flag
1266 THEN
1267 IF p_trx_rec.complete_flag = 'Y'
1268 THEN
1269 l_frt_only_rules :=
1270 arp_trx_util.detect_freight_only_rules_case(
1271 p_customer_trx_id);
1272
1273 IF l_frt_only_rules
1274 THEN p_trx_rec.invoicing_rule_id := null;
1275 END IF;
1276 END IF;
1277 END IF;
1278
1279 /*----------------------+
1280 | call table-handler |
1281 +----------------------*/
1282
1283 arp_ct_pkg.update_p(p_trx_rec, p_customer_trx_id);
1284
1285 /*---------------------------------------------------------+
1286 | Update the dispute amounts on the payment schedules if |
1287 | the dispute amount has changed. |
1288 |
1289 | 10/10/1996 Harri Kaukovuo Bug fix 411031.
1290 +---------------------------------------------------------*/
1291
1292 IF ( l_dispute_changed_flag = TRUE )
1293 THEN
1294 DECLARE
1295 /*Adding cursor as part of bug fix 5129946*/
1296 CURSOR get_existing_ps (p_ctrx_id IN NUMBER) IS
1297 SELECT payment_schedule_id,
1298 amount_in_dispute,
1299 amount_due_remaining,
1300 dispute_date
1301 FROM ar_payment_schedules
1302 WHERE customer_trx_id = p_ctrx_id;
1303 l_old_dispute_date DATE;
1304 l_new_dispute_date DATE;
1305 l_old_dispute_amount NUMBER;
1306 l_amount_due_remaining NUMBER;
1307 l_ps_id NUMBER;
1308 l_new_dispute_amount NUMBER;
1309 l_sysdate DATE := SYSDATE;
1310 l_last_update_login NUMBER := arp_standard.profile.last_update_login;
1311 l_user_id NUMBER := arp_standard.profile.user_id;
1312 BEGIN
1313 /*Bug 5129946: Calling arp_dispute_history.DisputeHistory*/
1314 OPEN get_existing_ps(p_customer_trx_id);
1315 FETCH get_existing_ps INTO
1316 l_ps_id,
1317 l_old_dispute_amount,
1318 l_amount_due_remaining,
1319 l_old_dispute_date;
1320 IF get_existing_ps%ROWCOUNT>0 THEN
1321 if(p_ps_dispute_amount = NULL) THEN
1322 l_new_dispute_amount := l_old_dispute_amount;
1323 ELSIF (p_ps_dispute_amount = 0) THEN
1324 l_new_dispute_amount := 0;
1325 ELSE
1326 IF(l_number_of_pay_scheds = 1) THEN
1327 l_new_dispute_amount := p_ps_dispute_amount;
1328 ELSE
1329 l_new_dispute_amount := l_amount_due_remaining;
1330 END IF;
1331 END IF;
1332 l_new_dispute_date := p_ps_dispute_date;
1333 if(l_new_dispute_amount <> l_old_dispute_amount)
1334 OR(l_new_dispute_amount IS NULL AND l_old_dispute_amount IS NOT NULL)
1335 OR(l_new_dispute_amount IS NOT NULL AND l_old_dispute_amount IS NULL)
1336 THEN
1337 arp_dispute_history.DisputeHistory(l_new_dispute_date,
1338 l_old_dispute_date,
1339 l_ps_id,
1340 l_ps_id,
1341 l_amount_due_remaining,
1342 l_new_dispute_amount,
1343 l_old_dispute_amount,
1344 l_user_id,
1345 l_sysdate,
1346 l_user_id,
1347 l_sysdate,
1348 l_last_update_login);
1349 END IF;
1350 END IF;--IF get_existing_ps%ROWCOUNT>0 THEN
1351 CLOSE get_existing_ps;
1352 UPDATE ar_payment_schedules ps
1353 SET ps.amount_in_dispute = DECODE(p_ps_dispute_amount,
1354 NULL, ps.amount_in_dispute,
1355 0, 0,
1356 DECODE(l_number_of_pay_scheds,
1357 1, p_ps_dispute_amount,
1358 ps.amount_due_remaining)),
1359 ps.dispute_date = p_ps_dispute_date
1360 WHERE ps.customer_trx_id = p_customer_trx_id;
1361 END;
1362 END IF;
1363
1364 /*---------------------+
1365 | post-update logic |
1366 +---------------------*/
1367
1368 IF p_trx_rec.exchange_rate = pg_number_dummy
1369 THEN l_exchange_rate := nvl(l_old_trx_rec.exchange_rate, 1);
1370 ELSE l_exchange_rate := nvl(p_trx_rec.exchange_rate, 1);
1371 END IF;
1372
1373 IF p_trx_class in ('DEP', 'GUAR')
1374 THEN
1375 IF p_trx_rec.invoice_currency_code = pg_text_dummy
1376 THEN l_invoice_currency_code :=
1377 l_old_trx_rec.invoice_currency_code;
1378 ELSE l_invoice_currency_code :=
1379 p_trx_rec.invoice_currency_code;
1380 END IF;
1381
1382 arp_process_commitment.header_post_update(
1383 p_commitment_rec,
1384 l_invoice_currency_code,
1385 l_exchange_rate,
1386 p_rerun_autoacc_flag);
1387 END IF;
1388
1389 /*--------------------------------------------------------------------+
1390 | Clear Lines Rule Info for void transactions Bug-3454082-4019170 |
1391 +--------------------------------------------------------------------*/
1392 l_rerun_autoacc_flag := p_rerun_autoacc_flag;
1393 /* Start FP Bug 5501665 Autoaccounting pops-up for tran tupe changed to void */
1394 IF (
1395 l_gl_date_changed_flag AND
1396 p_gl_date IS NULL AND
1397 p_trx_class = 'INV' AND
1398 p_trx_rec.invoicing_rule_id IS NULL AND
1399 l_old_trx_rec.invoicing_rule_id is not NULL
1400 )
1401 THEN
1402 arp_util.debug('Clearing Lines and dist Rule Info ()+');
1403 BEGIN
1404 Select 'X' INTO l_dummy_flag
1405 FROM ra_customer_trx_lines
1406 WHERE customer_trx_id = p_customer_trx_id
1407 AND rownum = 1;
1408
1409 arp_ctl_pkg.set_to_dummy( l_line_rec );
1410 l_line_rec.accounting_rule_id := NULL;
1411 l_line_rec.accounting_rule_duration := NULL;
1412 l_line_rec.rule_start_date := NULL;
1413 l_line_rec.autorule_complete_flag := NULL;
1414 l_line_rec.autorule_duration_processed := NULL;
1415 BEGIN
1416 arp_ctl_pkg.update_f_ct_id( l_line_rec,
1417 p_customer_trx_id,
1418 'LINE');
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 arp_util.debug('EXCEPTION: arp_process_header.Clearing Lines Rule Info: '||
1422 'no child lines to update.');
1423 WHEN OTHERS THEN
1424 arp_util.debug('EXCEPTION: '||
1425 'arp_process_header..Clearing Lines Rule Info()');
1426 RAISE;
1427 END;
1428 EXCEPTION
1429 WHEN NO_DATA_FOUND THEN
1430 arp_util.debug('EXCEPTION: arp_process_header.update_header: '||
1431 'no child lines to clear rule info.');
1432 END;
1433 BEGIN
1434 arp_ctlgd_pkg.delete_f_ct_id(p_customer_trx_id,
1435 'Y',
1436 NULL);
1437 l_rerun_autoacc_flag := TRUE;
1438 EXCEPTION
1439 WHEN NO_DATA_FOUND THEN
1440 arp_util.debug('arp_process_header.Clearing GL Dist Rule Info : '||
1441 'no dists to delete.');
1442 WHEN OTHERS THEN
1443 arp_util.debug('EXCEPTION: '||
1444 'arp_process_header.Clearing GL Dist Rule Info ()');
1445 RAISE;
1446 END;
1447 arp_util.debug('Clearing Lines and dist Rule Info ()-');
1448 END IF;
1449
1450
1451 /*------------------------------------------------------------+
1452 | If autoaccounting is rerun, it drops and recreates the |
1453 | distributions. Therefore, we don't need to update |
1454 | the distributions if autoaccounting is rerun - they will |
1455 | already have the correct values. |
1456 +------------------------------------------------------------*/
1457
1458 IF l_rerun_autoacc_flag /* Bug-3454082 - 4019170 */
1459 THEN
1460 header_rerun_aa(p_customer_trx_id,
1461 p_gl_date,
1462 p_trx_amount,
1463 l_status2);
1464
1465 ELSE
1466 IF l_ex_rate_changed_flag
1467 THEN
1468 arp_ctlgd_pkg.update_acctd_amount(p_customer_trx_id,
1469 pg_base_curr_code,
1470 l_exchange_rate,
1471 pg_base_precision,
1472 pg_base_min_acc_unit);
1473 END IF;
1474 END IF; /* IF l_rerun_autoacc_flag */
1475
1476 /* anjprasa: Bug 12757103
1477 Condition : Type: 'CM' , gl_date_changed_flag =TRUE ,complete_flag = false , Rule enabled
1478 Calling reverse_revrec_effect routine to delete Non modal row from line_gl_dist table for 'CM'
1479 Once User complete this CM, it will regenerate Non Modal row. */
1480
1481 IF (l_gl_date_changed_flag AND
1482 p_trx_rec.complete_flag = 'N' AND
1483 p_trx_rec.previous_customer_trx_id IS NOT Null AND
1484 p_trx_class = 'CM' AND
1485 arpt_sql_func_util.get_nonmodal_row_exist_flag(
1486 p_trx_rec.Customer_trx_id,
1487 p_trx_rec.invoicing_rule_id) = 'Y')
1488 THEN
1489 arp_util.debug('Calling reverse_revrec_effect to delete non modal row +');
1490 reverse_revrec_effect(p_customer_trx_id);
1491 arp_util.debug('Calling reverse_revrec_effect to delete non modal row -');
1492 END IF;
1493
1494
1495
1496
1497
1498 /* Bug 1580246 Moved the following code from the ELSE part of
1499 the above condition which checks for p_rerun_autoacc_flag
1500 so that the GL_DATE of the ROUND record is updated
1501 properly even when autoaccounting is re-run. */
1502
1503 IF l_gl_date_changed_flag
1504 THEN
1505 -- update gl_date for all gl_distributions
1506
1507 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
1508
1509 l_dist_rec.gl_date := p_gl_date;
1510
1511 BEGIN
1512 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
1513 p_customer_trx_id,
1514 null,
1515 null);
1516
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND THEN
1519 arp_util.debug('arp_process_header.update_header: '||
1520 'no dists to update.');
1521 WHEN OTHERS THEN
1522 arp_util.debug('EXCEPTION: '||
1523 'arp_process_header.update_header()');
1524 RAISE;
1525
1526 END;
1527 END IF; /* IF l_gl_date_changed_flag */
1528
1529 /* Bug 2689013 Added the call to the procedure reverse_revrec_effect */
1530
1531 /* Bug 2689013 Added the call to the procedure reverse_revrec_effect */
1532 IF l_complete_changed_flag AND
1533 p_trx_rec.complete_flag = 'N' AND
1534 p_trx_rec.invoicing_rule_id IS NOT NULL
1535 THEN
1536 /* 5633334/5637907 - also reverse for CMs that have not
1537 been through Rev Rec yet */
1538 IF (p_trx_rec.previous_customer_trx_id IS NULL AND
1539 arpt_sql_func_util.get_revenue_recog_run_flag(p_customer_trx_id,
1540 p_trx_rec.invoicing_rule_id) = 'Y')
1541 OR (p_trx_rec.previous_customer_trx_id IS NOT NULL AND
1542 pg_use_inv_acctg = 'N' AND
1543 arpt_sql_func_util.get_revenue_recog_run_flag(
1544 p_trx_rec.previous_customer_trx_id,
1545 p_trx_rec.invoicing_rule_id) = 'Y')
1546 THEN
1547 reverse_revrec_effect(p_customer_trx_id);
1548 END IF;
1549 END IF; /* IF l_complete_flag_changed */
1550
1551 IF l_recalc_tax
1552 THEN
1553 pg_tax_flag := arp_trx_validate2.pg_tax_flag;
1554 arp_util.debug( 'Before Update TAX P'|| pg_tax_flag);
1555
1556 /*------------------------------------------------------------------+
1557 | Do not update line tax codes when the Complete Flag is changed |
1558 | to 'Y' and the system option Enforce from Revenue Account is 'Y'.|
1559 | Line tax codes are corrected when the checkbox is changed on the |
1560 | client side. Calling update_tax will erroneously override them. |
1561 +------------------------------------------------------------------*/
1562 IF ( l_complete_changed_flag AND
1563 p_trx_rec.complete_flag = 'Y' AND
1564 nvl(ARP_GLOBAL.sysparam.tax_enforce_account_flag, 'N') = 'Y' ) THEN
1565
1566 -- Don't update line tax code, Would've been updated at the client
1567 -- side.
1568 null;
1569 ELSE
1570 /*------------------------------------------------------------------+
1571 | Call update_tax to re-default line tax codes |
1572 +------------------------------------------------------------------*/
1573 arp_ct_pkg.update_tax(p_trx_rec.ship_to_site_use_id,
1574 p_trx_rec.bill_to_site_use_id,
1575 p_trx_rec.trx_date,
1576 p_trx_rec.cust_trx_type_id,
1577 p_customer_trx_id,
1578 pg_tax_flag,
1579 FALSE);
1580 END IF;
1581
1582
1583 -- Bug 446263: if the transaction type has the property
1584 -- Calculate_Tax=N, the automatically generated tax codes
1585 -- on the lines should be nulled out.
1586
1587 /* Bug 5093094 - removed logic that nulled
1588 vat_tax_id when trx_type changed from
1589 cal_tax=Y to N. Flag is no longer
1590 used in this way in R12 */
1591
1592 END IF;
1593
1594 IF (l_commitment_changed_flag
1595 OR
1596 (l_complete_changed_flag
1597 AND
1598 p_trx_rec.complete_flag = 'Y'
1599 AND
1600 p_trx_rec.initial_customer_trx_id IS NOT NULL
1601 AND
1602 nvl(p_trx_rec.initial_customer_trx_id, 0) <> pg_number_dummy)
1603 )
1604 THEN
1605 -- update ra_customer_trx_lines.initial_customer_trx_line_id
1606 IF (p_trx_rec.initial_customer_trx_id IS NOT NULL
1607 AND
1608 p_initial_customer_trx_line_id IS NULL)
1609 THEN
1610 BEGIN
1611 SELECT customer_trx_line_id
1612 INTO l_initial_customer_trx_line_id
1613 FROM ra_customer_trx_lines ctl
1614 WHERE ctl.customer_trx_id = p_trx_rec.initial_customer_trx_id
1615 AND ctl.line_type = 'LINE';
1616 END;
1617 ELSE
1618 l_initial_customer_trx_line_id := p_initial_customer_trx_line_id;
1619 END IF;
1620
1621 arp_ctl_pkg.set_to_dummy( l_line_rec );
1622
1623 l_line_rec.initial_customer_trx_line_id :=
1624 l_initial_customer_trx_line_id;
1625
1626 BEGIN
1627 arp_ctl_pkg.update_f_ct_id( l_line_rec,
1628 p_customer_trx_id,
1629 'LINE');
1630
1631 EXCEPTION
1632 WHEN NO_DATA_FOUND THEN
1633 arp_util.debug('arp_process_header.update_header: '||
1634 'no child lines to update.');
1635 WHEN OTHERS THEN
1636 arp_util.debug('EXCEPTION: '||
1637 'arp_process_header.update_header()');
1638 RAISE;
1639 END;
1640 END IF;
1641
1642 IF l_frt_only_rules
1643 THEN
1644 -- update the account sets to be real dists.
1645 -- inv rule is cleared at complete time
1646 -- or when all 'line' lines are deleted
1647 -- if rules and freight only invoice.
1648 -- Reason is that autorule cannot handle freight only invoices
1649 -- with rules.
1650
1651 -- There are two dists in this case:
1652 -- o The REC dist
1653 -- o The FREIGHT dist
1654
1655 arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
1656
1657 l_dist_rec.account_set_flag := 'N';
1658
1659 l_dist_rec.acctd_amount := arp_standard.functional_amount(
1660 p_trx_amount,
1661 pg_base_curr_code,
1662 l_exchange_rate,
1663 pg_base_precision,
1664 pg_base_min_acc_unit);
1665
1666 l_dist_rec.amount := p_trx_amount;
1667 l_dist_rec.gl_date := p_gl_date;
1668 l_dist_rec.original_gl_date := p_gl_date;
1669
1670 arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
1671 p_customer_trx_id,
1672 null,
1673 null);
1674 END IF;
1675
1676
1677 -- IF l_complete_changed_flag
1678 -- THEN IF p_trx_rec.complete_flag = 'N'
1679 -- THEN delete the payment schedule(s) (if there is one)
1680 -- ELSE IF p_open_rec_flag = 'Y'
1681 -- THEN tell post-commit that it needs to create the
1682 -- payment schedule(s).
1683 -- END IF;
1684 -- END IF;
1685 -- ELSE IF l_open_rec_changed_flag
1686 -- THEN IF p_open_rec_flag = 'N'
1687 -- THEN delete the payment schedule (s)
1688 -- ELSE tell post-commit that it needs to create the
1689 -- payment schedule(s).
1690 -- END IF;
1691 -- ELSE IF p_open_rec_flag = 'Y'
1692 -- THEN tell post-commit that it needs to update the
1693 -- payment schedule(s).
1694 -- END IF;
1695 -- END IF;
1696 -- END IF;
1697
1698 IF p_trx_rec.term_id <> pg_number_dummy
1699 THEN
1700 arp_trx_util.set_term_in_use_flag(
1701 p_form_name,
1702 p_form_version,
1703 p_trx_rec.term_id,
1704 p_term_in_use_flag);
1705 END IF;
1706
1707 arp_util.debug('l_status1 = ' || l_status1);
1708 arp_util.debug('l_status2 = ' || l_status2);
1709 arp_util.debug('l_status3 = ' || l_status3);
1710
1711 IF ( NVL(l_status1, 'OK') <> 'OK' )
1712 THEN p_status := l_status1;
1713 ELSIF ( NVL(l_status2, 'OK') <> 'OK' )
1714 THEN p_status := l_status2;
1715 ELSIF ( NVL(l_status3, 'OK') <> 'OK' )
1716 THEN p_status := l_status3;
1717 ELSE p_status := 'OK';
1718 END IF;
1719
1720 --BUG5192414
1721 OPEN cpost;
1722 FETCH cpost INTO l_test;
1723 IF cpost%NOTFOUND THEN
1724 --BUG#7366912
1725 IF (p_gl_date is NULL) THEN
1726 BEGIN
1727 select xet.legal_entity_id legal_entity_id,
1728 ct.SET_OF_BOOKS_ID set_of_books_id,
1729 ct.org_id org_id,
1730 xe.event_id event_id,
1731 xet.entity_code entity_code,
1732 ct.customer_trx_id transaction_id,
1733 xet.application_id,
1734 ctt.post_to_gl,
1735 xe.event_status_code
1736 into
1737 l_event_source_info.legal_entity_id,
1738 l_event_source_info.ledger_id,
1739 l_security.security_id_int_1,
1740 l_event_id ,
1741 l_event_source_info.entity_type_code,
1742 l_event_source_info.source_id_int_1,
1743 l_event_source_info.application_id,
1744 l_post_to_gl,
1745 l_event_status_code
1746 from
1747 ra_customer_trx ct ,
1748 ra_cust_trx_types ctt,
1749 xla_transaction_entities_upg xet ,
1750 xla_events xe
1751 where ct.customer_trx_id = p_customer_trx_id
1752 and ctt.cust_trx_type_id = ct.cust_trx_type_id
1753 and ct.customer_trx_id = nvl(xet.source_id_int_1,-99)
1754 AND ct.SET_OF_BOOKS_ID = xet.LEDGER_ID
1755 and xet.entity_code ='TRANSACTIONS'
1756 AND xet.application_id = 222
1757 AND xe.entity_id = xet.entity_id
1758 AND xe.application_id = 222 ;
1759
1760 IF ((l_post_to_gl ='N')
1761 AND (l_event_status_code = 'I')) THEN
1762
1763 xla_events_pub_pkg.delete_event
1764 ( p_event_source_info => l_event_source_info,
1765 p_event_id => l_event_id,
1766 p_valuation_method => NULL,
1767 p_security_context => l_security);
1768
1769 update ra_cust_trx_line_gl_dist set event_id=null
1770 WHERE customer_trx_id = p_customer_trx_id
1771 and ACCOUNT_SET_FLAG='N'
1772 and event_id =l_event_id;
1773
1774 END IF;
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 arp_util.debug('Unable to get the XLA Entites Data ' ||
1778 'EXCEPTION: arp_process_header.update_header()' );
1779 --RAISE;
1780 END;
1781 END IF;
1782
1783 --Bug# 2750340
1784 ---------------------------------------------------------
1785 -- Call to ARP_XLA_EVENTS for transaction updation in
1786 -- Trx Workbench. Mandatory when user complete a document
1787 ---------------------------------------------------------
1788 l_ev_rec.xla_from_doc_id := p_customer_trx_id;
1789 l_ev_rec.xla_to_doc_id := p_customer_trx_id;
1790 l_ev_rec.xla_req_id := NULL;
1791 l_ev_rec.xla_dist_id := NULL;
1792 l_ev_rec.xla_doc_table := 'CT';
1793 l_ev_rec.xla_doc_event := NULL;
1794 l_ev_rec.xla_mode := 'O';
1795 l_ev_rec.xla_call := 'B';
1796 l_ev_rec.xla_fetch_size := 999;
1797 arp_xla_events.create_events(p_xla_ev_rec => l_ev_rec );
1798
1799 END IF;
1800 CLOSE cpost;
1801
1802 arp_util.debug('arp_process_header.update_header()-');
1803
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 arp_util.debug('EXCEPTION: arp_process_header.update_header()');
1807 RAISE;
1808
1809 END;
1810
1811 --added for bug 7478499
1812 /*===========================================================================+
1813 | PROCEDURE |
1814 | delete_cont_defer_data | |
1815 | |
1816 | DESCRIPTION |
1817 | deletes rows from ar_line_conts and ar_deferred_lines. |
1818 | |
1819 | SCOPE - PRIVATE |
1820 | |
1821 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1822 | arp_util.debug |
1823 | |
1824 | ARGUMENTS : IN: p_customer_trx_id | |
1825 | OUT: |
1826 | IN/ OUT: |
1827 | |
1828 | RETURNS : NONE |
1829 | |
1830 | NOTES |
1831 | |
1832 | MODIFICATION HISTORY |
1833 | 07-NOV-08 Ankur Agarwal Created |
1834 | |
1835 | |
1836 +===========================================================================*/
1837
1838 PROCEDURE delete_cont_defer_data(p_customer_trx_id IN NUMBER) IS
1839 CURSOR cont_cursor IS
1840 select alc.customer_trx_line_id
1841 from ra_customer_trx_lines ctl,
1842 ar_line_conts alc
1843 where
1844 ctl.customer_trx_id = p_customer_trx_id
1845 and ctl.customer_trx_line_id = alc.customer_trx_line_id
1846 and ctl.line_type = 'LINE'
1847 FOR UPDATE OF alc.customer_trx_line_id NOWAIT;
1848
1849 CURSOR deferred_cursor IS
1850 select customer_trx_id
1851 from ar_deferred_lines
1852 where customer_trx_id = p_customer_trx_id
1853 FOR UPDATE OF customer_trx_id NOWAIT;
1854
1855
1856 BEGIN
1857 arp_util.debug('arp_process_header.delete_cont_defer_data+');
1858
1859 FOR l_cont_rec IN cont_cursor LOOP
1860 delete from ar_line_conts
1861 where customer_trx_line_id = l_cont_rec.customer_trx_line_id;
1862 END LOOP;
1863
1864 FOR l_deferred_rec IN deferred_cursor LOOP
1865 delete from ar_deferred_lines
1866 WHERE customer_trx_id = l_deferred_rec.customer_trx_id;
1867 END LOOP;
1868
1869 arp_util.debug('arp_process_header.delete_cont_defer_data-');
1870 EXCEPTION
1871 WHEN NO_DATA_FOUND THEN
1872 null;
1873 WHEN OTHERS THEN
1874 arp_util.debug('EXCEPTION : '||
1875 'arp_process_header.delete_cont_defer_data()-');
1876 END;
1877
1878
1879 /*===========================================================================+
1880 | PROCEDURE |
1881 | delete_header |
1882 | |
1883 | DESCRIPTION |
1884 | deletes row from ra_customer_trx. Also deletes all child rows. |
1885 | |
1886 | SCOPE - PUBLIC |
1887 | |
1888 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1889 | arp_util.debug |
1890 | |
1891 | ARGUMENTS : IN: |
1892 | OUT: |
1893 | IN/ OUT: |
1894 | |
1895 | RETURNS : NONE |
1896 | |
1897 | NOTES |
1898 | |
1899 | MODIFICATION HISTORY |
1900 | 26-JUL-95 Martin Johnson Created |
1901 | 29-NOV-95 Nigel Smith Added call to Tax Engine. |
1902 | |
1903 +===========================================================================*/
1904
1905 PROCEDURE delete_header(
1906 p_form_name IN varchar2,
1907 p_form_version IN number,
1908 p_customer_trx_id IN number,
1909 p_trx_class IN varchar2,
1910 p_status OUT NOCOPY varchar2)
1911
1912 IS
1913
1914 l_new_tax_amount NUMBER;
1915
1916 BEGIN
1917
1918 arp_util.debug('arp_process_header.delete_header()+');
1919
1920 p_status := 'OK';
1921
1922 -- check form version to determine if it is compatible with the
1923 -- entity handler.
1924 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
1925
1926 -- do validation
1927 validate_delete_header;
1928
1929 -- Lock rows in other tables that reference this customer_trx_id
1930 arp_trx_util.lock_transaction(p_customer_trx_id);
1931
1932 /*--------------------+
1933 | pre-delete logic |
1934 +--------------------*/
1935
1936 IF p_trx_class in ('DEP', 'GUAR')
1937 THEN arp_process_commitment.header_pre_delete;
1938 -- does commitment validation
1939 ELSE
1940 /* 5156232 - remove tax from etax repository */
1941 ARP_ETAX_UTIL.GLOBAL_DOCUMENT_UPDATE(p_customer_trx_id,
1942 NULL,'DELETE');
1943 END IF;
1944
1945
1946 --Bug#2750340
1947 --Bug # 6450286
1948 --------------------------------
1949 -- Delete the corresponding event in XLA schema
1950 --------------------------------
1951 ARP_XLA_EVENTS.delete_event( p_document_id => p_customer_trx_id,
1952 p_doc_table => 'CT');
1953
1954 --added for bug 7478499
1955 delete_cont_defer_data(p_customer_trx_id);
1956 /*-------------------------+
1957 | delete the transaction |
1958 +-------------------------*/
1959
1960 arp_trx_util.delete_transaction(p_form_name,
1961 p_form_version,
1962 p_customer_trx_id);
1963
1964
1965
1966
1967 arp_util.debug('arp_process_header.delete_header()-');
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 arp_util.debug('EXCEPTION: arp_process_header.delete_header()');
1972 RAISE;
1973
1974 END;
1975
1976 /*===========================================================================+
1977 | PROCEDURE |
1978 | post_commit |
1979 | |
1980 | DESCRIPTION |
1981 | Performs logic that must occur after all of the other logic for the |
1982 | insertion or update of a transaction has occurred. |
1983 | |
1984 | SCOPE - PUBLIC |
1985 | |
1986 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1987 | arp_util.debug |
1988 | arp_maintain_ps.maintain_payment_schedules |
1989 | |
1990 | ARGUMENTS : IN: |
1991 | OUT: |
1992 | IN/ OUT: |
1993 | |
1994 | RETURNS : NONE |
1995 | |
1996 | NOTES |
1997 | |
1998 | MODIFICATION HISTORY |
1999 | 28-AUG-95 Charlie Tomberg Created |
2000 | 19-FEB-96 Oliver Steinmeier Changed logic in post-commit to |
2001 | make sure the payment schedule |
2002 | gets called for debit memos |
2003 | |
2004 +===========================================================================*/
2005 PROCEDURE post_commit( p_form_name IN varchar2,
2006 p_form_version IN number,
2007 p_customer_trx_id IN
2008 ra_customer_trx.customer_trx_id%type,
2009 p_previous_customer_trx_id IN
2010 ra_customer_trx.previous_customer_trx_id%type,
2011 p_complete_flag IN
2012 ra_customer_trx.complete_flag%type,
2013 p_trx_open_receivables_flag IN
2014 ra_cust_trx_types.accounting_affect_flag%type,
2015 p_prev_open_receivables_flag IN
2016 ra_cust_trx_types.accounting_affect_flag%type,
2017 p_creation_sign IN
2018 ra_cust_trx_types.creation_sign%type,
2019 p_allow_overapplication_flag IN
2020 ra_cust_trx_types.allow_overapplication_flag%type,
2021 p_natural_application_flag IN
2022 ra_cust_trx_types.natural_application_only_flag%type,
2023 p_cash_receipt_id IN
2024 ar_cash_receipts.cash_receipt_id%type DEFAULT NULL
2025 ) IS
2026
2027 BEGIN
2028
2029 arp_process_header_post_commit.
2030 post_commit( p_form_name,
2031 p_form_version,
2032 p_customer_trx_id,
2033 p_previous_customer_trx_id,
2034 p_complete_flag,
2035 p_trx_open_receivables_flag,
2036 p_prev_open_receivables_flag,
2037 p_creation_sign,
2038 p_allow_overapplication_flag,
2039 p_natural_application_flag,
2040 p_cash_receipt_id,
2041 'STANDARD'
2042 );
2043
2044 END;
2045
2046
2047
2048 /*===========================================================================+
2049 | PROCEDURE |
2050 | update_header_freight_cover |
2051 | |
2052 | DESCRIPTION |
2053 | Converts column parameters to a header transaction record and |
2054 | updates the freight columns on the transaction header |
2055 | |
2056 | SCOPE - PUBLIC |
2057 | |
2058 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2059 | arp_util.debug |
2060 | |
2061 | ARGUMENTS : IN: |
2062 | p_form_name |
2063 | p_form_version |
2064 | p_trx_class |
2065 | p_open_rec_flag |
2066 | p_ship_via |
2067 | p_ship_date_actual |
2068 | p_waybill_number |
2069 | p_fob_point |
2070 | OUT: |
2071 | p_status |
2072 | IN/ OUT: |
2073 | None |
2074 | |
2075 | RETURNS : NONE |
2076 | |
2077 | NOTES |
2078 | |
2079 | MODIFICATION HISTORY |
2080 | 10-OCT-95 Subash Chadalavada Created |
2081 | |
2082 +===========================================================================*/
2083
2084 PROCEDURE update_header_freight_cover(
2085 p_form_name IN varchar2,
2086 p_form_version IN number,
2087 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
2088 p_trx_class IN ra_cust_trx_types.type%type,
2089 p_open_rec_flag IN ra_cust_trx_types.accounting_affect_flag%type,
2090 p_ship_via IN ra_customer_trx.ship_via%type,
2091 p_ship_date_actual IN ra_customer_trx.ship_date_actual%type,
2092 p_waybill_number IN ra_customer_trx.waybill_number%type,
2093 p_fob_point IN ra_customer_trx.fob_point%type,
2094 p_status OUT NOCOPY varchar2)
2095
2096 IS
2097
2098 l_trx_rec ra_customer_trx%rowtype;
2099 l_commit_rec arp_process_commitment.commitment_rec_type;
2100 l_dummy varchar2(80);
2101
2102 BEGIN
2103
2104 arp_util.debug('arp_process_header.update_header_freight_cover()+');
2105
2106 arp_ct_pkg.set_to_dummy(l_trx_rec);
2107
2108 l_trx_rec.ship_via := p_ship_via;
2109 l_trx_rec.ship_date_actual := p_ship_date_actual;
2110 l_trx_rec.waybill_number := p_waybill_number;
2111 l_trx_rec.fob_point := p_fob_point;
2112
2113 update_header(
2114 p_form_name,
2115 p_form_version,
2116 l_trx_rec,
2117 p_customer_trx_id,
2118 null,
2119 p_trx_class,
2120 pg_date_dummy,
2121 null,
2122 l_commit_rec,
2123 null,
2124 null,
2125 null,
2126 null,
2127 null,
2128 null,
2129 p_status);
2130
2131 arp_util.debug('arp_process_header.update_header_freight_cover()-');
2132
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 arp_util.debug('EXCEPTION : '||
2136 'arp_process_header.update_header_freight_cover()-');
2137 arp_util.debug('------- parameters for update_header_freight_cover ----');
2138 arp_util.debug('p_form_name = '||p_form_name);
2139 arp_util.debug('p_form_version = '||p_form_version);
2140 arp_util.debug('p_ship_via = '||p_ship_via);
2141 arp_util.debug('p_ship_date_actual = '||p_ship_date_actual);
2142 arp_util.debug('p_waybill_number = '||p_waybill_number);
2143 arp_util.debug('p_fob_point = '||p_fob_point);
2144
2145 RAISE;
2146 END;
2147
2148
2149
2150 /*===========================================================================+
2151 | PROCEDURE |
2152 | post_query() |
2153 | |
2154 | DESCRIPTION |
2155 | Selects additional data from the database in the following cases: |
2156 | - The transaction is the child of a commitment |
2157 | - The transaction is a commitment |
2158 | - The transaction is a credit memo against a specific transaction |
2159 | (not on account). |
2160 | |
2161 | This procedure was created so that the ra_customer_trx_v view could |
2162 | be simplified by removing koins to support these special cases. |
2163 | |
2164 | SCOPE - PUBLIC |
2165 | |
2166 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2167 | arp_util.debug |
2168 | |
2169 | ARGUMENTS : IN: |
2170 | p_ct_rowid |
2171 | p_customer_trx_id |
2172 | p_initial_customer_trx_id |
2173 | p_previous_customer_trx_id |
2174 | p_class |
2175 | OUT: |
2176 | p_ct_commitment_trx_date |
2177 | p_ct_commitment_number |
2178 | p_gd_commitment_gl_date |
2179 | p_ctl_commit_cust_trx_line_id |
2180 | p_ctl_commitment_amount |
2181 | p_ctl_commitment_text |
2182 | p_ctl_commitment_inv_item_id |
2183 | p_interface_line_context |
2184 | p_interface_line_attribute1 |
2185 | p_interface_line_attribute2 |
2186 | p_interface_line_attribute3 |
2187 | p_interface_line_attribute4 |
2188 | p_interface_line_attribute5 |
2189 | p_interface_line_attribute6 |
2190 | p_interface_line_attribute7 |
2191 | p_interface_line_attribute8 |
2192 | p_interface_line_attribute9 |
2193 | p_interface_line_attribute10 |
2194 | p_interface_line_attribute11 |
2195 | p_interface_line_attribute12 |
2196 | p_interface_line_attribute13 |
2197 | p_interface_line_attribute14 |
2198 | p_interface_line_attribute15 |
2199 | p_attribute_category |
2200 | p_attribute1 |
2201 | p_attribute2 |
2202 | p_attribute3 |
2203 | p_attribute4 |
2204 | p_attribute5 |
2205 | p_attribute6 |
2206 | p_attribute7 |
2207 | p_attribute8 |
2208 | p_attribute9 |
2209 | p_attribute10 |
2210 | p_attribute11 |
2211 | p_attribute12 |
2212 | p_attribute13 |
2213 | p_attribute14 |
2214 | p_attribute15 |
2215 | p_ct_prev_trx_number |
2216 | p_ct_prev_trx_reference |
2217 | p_ct_prev_inv_currency_code |
2218 | p_ct_prev_trx_date |
2219 | p_ct_prev_bill_to_customer_id |
2220 | p_ct_prev_ship_to_customer_id |
2221 | p_ct_prev_sold_to_customer_id |
2222 | p_ct_prev_paying_customer_id |
2223 | p_ct_prev_bill_to_site_use_id |
2224 | p_ct_prev_ship_to_site_use_id |
2225 | p_ct_prev_paying_site_use_id |
2226 | p_ct_prev_bill_to_contact_id |
2227 | p_ct_prev_ship_to_contact_id |
2228 | p_ct_prev_initial_cust_trx_id |
2229 | p_ct_prev_primary_salesrep_id |
2230 | p_ct_prev_invoicing_rule_id |
2231 | p_gd_prev_gl_date |
2232 | p_prev_trx_original |
2233 | p_prev_trx_balance |
2234 | p_rac_prev_bill_to_cust_name |
2235 | p_rac_prev_bill_to_cust_num |
2236 | p_bs_prev_source_name |
2237 | p_ctt_prev_class |
2238 | p_ctt_prev_allow_overapp_flag |
2239 | p_ctt_prev_natural_app_only |
2240 | p_al_cm_reason_meaning |
2241 | IN/ OUT: |
2242 | None |
2243 | |
2244 | RETURNS : NONE |
2245 | |
2246 | NOTES |
2247 | |
2248 | MODIFICATION HISTORY |
2249 | 05-APR-96 Charlie Tomberg Created |
2250 | 08-MAY-96 Martin Johnson BugNo:345208. Return p_ctl_commitment_|
2251 | inv_item_id for Child Of A Commitment |
2252 | case |
2253 | 20-Oct-04 Surendra Rajan Bug-3954193 : Added two parameters ct_ |
2254 | prev_open_receviables and ct_prev_post_|
2255 | to_gl_flag in the procedure post_query.|
2256 | |
2257 | |
2258 +===========================================================================*/
2259
2260 PROCEDURE post_query(
2261 p_ct_rowid IN varchar2,
2262 p_customer_trx_id IN NUMBER,
2263 p_initial_customer_trx_id IN NUMBER,
2264 p_previous_customer_trx_id IN NUMBER,
2265 p_class IN varchar2,
2266 p_ct_commitment_trx_date OUT NOCOPY date,
2267 p_ct_commitment_number OUT NOCOPY varchar2,
2268 p_gd_commitment_gl_date OUT NOCOPY date,
2269 p_ctl_commit_cust_trx_line_id OUT NOCOPY number,
2270 p_ctl_commitment_amount OUT NOCOPY number,
2271 p_ctl_commitment_text OUT NOCOPY varchar2,
2272 p_ctl_commitment_inv_item_id OUT NOCOPY number,
2273 p_interface_line_context OUT NOCOPY varchar2,
2274 p_interface_line_attribute1 OUT NOCOPY varchar2,
2275 p_interface_line_attribute2 OUT NOCOPY varchar2,
2276 p_interface_line_attribute3 OUT NOCOPY varchar2,
2277 p_interface_line_attribute4 OUT NOCOPY varchar2,
2278 p_interface_line_attribute5 OUT NOCOPY varchar2,
2279 p_interface_line_attribute6 OUT NOCOPY varchar2,
2280 p_interface_line_attribute7 OUT NOCOPY varchar2,
2281 p_interface_line_attribute8 OUT NOCOPY varchar2,
2282 p_interface_line_attribute9 OUT NOCOPY varchar2,
2283 p_interface_line_attribute10 OUT NOCOPY varchar2,
2284 p_interface_line_attribute11 OUT NOCOPY varchar2,
2285 p_interface_line_attribute12 OUT NOCOPY varchar2,
2286 p_interface_line_attribute13 OUT NOCOPY varchar2,
2287 p_interface_line_attribute14 OUT NOCOPY varchar2,
2288 p_interface_line_attribute15 OUT NOCOPY varchar2,
2289 p_attribute_category OUT NOCOPY varchar2,
2290 p_attribute1 OUT NOCOPY varchar2,
2291 p_attribute2 OUT NOCOPY varchar2,
2292 p_attribute3 OUT NOCOPY varchar2,
2293 p_attribute4 OUT NOCOPY varchar2,
2294 p_attribute5 OUT NOCOPY varchar2,
2295 p_attribute6 OUT NOCOPY varchar2,
2296 p_attribute7 OUT NOCOPY varchar2,
2297 p_attribute8 OUT NOCOPY varchar2,
2298 p_attribute9 OUT NOCOPY varchar2,
2299 p_attribute10 OUT NOCOPY varchar2,
2300 p_attribute11 OUT NOCOPY varchar2,
2301 p_attribute12 OUT NOCOPY varchar2,
2302 p_attribute13 OUT NOCOPY varchar2,
2303 p_attribute14 OUT NOCOPY varchar2,
2304 p_attribute15 OUT NOCOPY varchar2,
2305 p_default_ussgl_trx_code OUT NOCOPY varchar2,
2306 p_ct_prev_trx_number OUT NOCOPY varchar2,
2307 p_ct_prev_trx_reference OUT NOCOPY varchar2,
2308 p_ct_prev_inv_currency_code OUT NOCOPY varchar2,
2309 p_ct_prev_trx_date OUT NOCOPY date,
2310 p_ct_prev_bill_to_customer_id OUT NOCOPY number,
2311 p_ct_prev_ship_to_customer_id OUT NOCOPY number,
2312 p_ct_prev_sold_to_customer_id OUT NOCOPY number,
2313 p_ct_prev_paying_customer_id OUT NOCOPY number,
2314 p_ct_prev_bill_to_site_use_id OUT NOCOPY number,
2315 p_ct_prev_ship_to_site_use_id OUT NOCOPY number,
2316 p_ct_prev_paying_site_use_id OUT NOCOPY number,
2317 p_ct_prev_bill_to_contact_id OUT NOCOPY number,
2318 p_ct_prev_ship_to_contact_id OUT NOCOPY number,
2319 p_ct_prev_initial_cust_trx_id OUT NOCOPY number,
2320 p_ct_prev_primary_salesrep_id OUT NOCOPY number,
2321 p_ct_prev_invoicing_rule_id OUT NOCOPY number,
2322 p_gd_prev_gl_date OUT NOCOPY date,
2323 p_prev_trx_original OUT NOCOPY number,
2324 p_prev_trx_balance OUT NOCOPY number,
2325 p_rac_prev_bill_to_cust_name OUT NOCOPY varchar2,
2326 p_rac_prev_bill_to_cust_num OUT NOCOPY varchar2,
2327 p_bs_prev_source_name OUT NOCOPY varchar2,
2328 p_ctt_prev_class OUT NOCOPY varchar2,
2329 p_ctt_prev_allow_overapp_flag OUT NOCOPY varchar2,
2330 p_ctt_prev_natural_app_only OUT NOCOPY varchar2,
2331 p_ct_prev_open_receivables OUT NOCOPY varchar2, /* Bug-3954193 */
2332 p_ct_prev_post_to_gl_flag OUT NOCOPY varchar2, /* Bug-3954193 */
2333 p_al_cm_reason_meaning OUT NOCOPY varchar2,
2334 p_commit_memo_line_id OUT NOCOPY number,
2335 p_commit_memo_line_desc OUT NOCOPY varchar2
2336 ) IS
2337
2338 l_ct_commitment_trx_date ra_customer_trx.trx_date%type;
2339 l_ct_commitment_number ra_customer_trx.trx_number%type;
2340 l_gd_commitment_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
2341 l_ctl_commit_cust_trx_line_id
2342 ra_customer_trx_lines.customer_trx_line_id%type;
2343 l_ctl_commitment_amount ra_customer_trx_lines.extended_amount%type;
2344 l_ctl_commitment_text ra_customer_trx_lines.description%type;
2345 l_ctl_commitment_inv_item_id ra_customer_trx_lines.inventory_item_id%type;
2346 l_interface_line_context
2347 ra_customer_trx_lines.interface_line_context%type;
2348 l_interface_line_attribute1
2349 ra_customer_trx_lines.interface_line_attribute1%type;
2350 l_interface_line_attribute2
2351 ra_customer_trx_lines.interface_line_attribute2%type;
2352 l_interface_line_attribute3
2353 ra_customer_trx_lines.interface_line_attribute3%type;
2354 l_interface_line_attribute4
2355 ra_customer_trx_lines.interface_line_attribute4%type;
2356 l_interface_line_attribute5
2357 ra_customer_trx_lines.interface_line_attribute5%type;
2358 l_interface_line_attribute6
2359 ra_customer_trx_lines.interface_line_attribute6%type;
2360 l_interface_line_attribute7
2361 ra_customer_trx_lines.interface_line_attribute7%type;
2362 l_interface_line_attribute8
2363 ra_customer_trx_lines.interface_line_attribute8%type;
2364 l_interface_line_attribute9
2365 ra_customer_trx_lines.interface_line_attribute9%type;
2366 l_interface_line_attribute10
2367 ra_customer_trx_lines.interface_line_attribute10%type;
2368 l_interface_line_attribute11
2369 ra_customer_trx_lines.interface_line_attribute11%type;
2370 l_interface_line_attribute12
2371 ra_customer_trx_lines.interface_line_attribute12%type;
2372 l_interface_line_attribute13
2373 ra_customer_trx_lines.interface_line_attribute13%type;
2374 l_interface_line_attribute14
2375 ra_customer_trx_lines.interface_line_attribute14%type;
2376 l_interface_line_attribute15
2377 ra_customer_trx_lines.interface_line_attribute15%type;
2378 l_attribute_category
2379 ra_customer_trx_lines.attribute_category%type;
2380 l_attribute1
2381 ra_customer_trx_lines.attribute1%type;
2382 l_attribute2
2383 ra_customer_trx_lines.attribute2%type;
2384 l_attribute3
2385 ra_customer_trx_lines.attribute3%type;
2386 l_attribute4
2387 ra_customer_trx_lines.attribute4%type;
2388 l_attribute5
2389 ra_customer_trx_lines.attribute5%type;
2390 l_attribute6
2391 ra_customer_trx_lines.attribute6%type;
2392 l_attribute7
2393 ra_customer_trx_lines.attribute7%type;
2394 l_attribute8
2395 ra_customer_trx_lines.attribute8%type;
2396 l_attribute9
2397 ra_customer_trx_lines.attribute9%type;
2398 l_attribute10
2399 ra_customer_trx_lines.attribute10%type;
2400 l_attribute11
2401 ra_customer_trx_lines.attribute11%type;
2402 l_attribute12
2403 ra_customer_trx_lines.attribute12%type;
2404 l_attribute13
2405 ra_customer_trx_lines.attribute13%type;
2406 l_attribute14
2407 ra_customer_trx_lines.attribute14%type;
2408 l_attribute15
2409 ra_customer_trx_lines.attribute15%type;
2410 l_default_ussgl_trx_code
2411 ra_customer_trx_lines.default_ussgl_transaction_code%type;
2412 l_ct_prev_trx_number ra_customer_trx.trx_number%type;
2413 l_ct_prev_trx_reference
2414 ra_customer_trx.interface_header_attribute1%type;
2415 l_ct_prev_inv_currency_code
2416 ra_customer_trx.invoice_currency_code%type;
2417 l_ct_prev_trx_date ra_customer_trx.trx_date%type;
2418 l_ct_prev_bill_to_customer_id ra_customer_trx.bill_to_customer_id%type;
2419 l_ct_prev_ship_to_customer_id ra_customer_trx.ship_to_customer_id%type;
2420 l_ct_prev_sold_to_customer_id ra_customer_trx.sold_to_customer_id%type;
2421 l_ct_prev_paying_customer_id ra_customer_trx.paying_customer_id%type;
2422 l_ct_prev_bill_to_site_use_id ra_customer_trx.bill_to_site_use_id%type;
2423 l_ct_prev_ship_to_site_use_id ra_customer_trx.ship_to_site_use_id%type;
2424 l_ct_prev_paying_site_use_id ra_customer_trx.paying_site_use_id%type;
2425 l_ct_prev_bill_to_contact_id ra_customer_trx.bill_to_contact_id%type;
2426 l_ct_prev_ship_to_contact_id ra_customer_trx.ship_to_contact_id%type;
2427 l_ct_prev_initial_cust_trx_id ra_customer_trx.customer_trx_id%type;
2428 l_ct_prev_primary_salesrep_id ra_customer_trx.primary_salesrep_id%type;
2429 l_ct_prev_invoicing_rule_id ra_customer_trx.invoicing_rule_id%type;
2430 l_gd_prev_gl_date ra_cust_trx_line_gl_dist.gl_date%type;
2431 l_prev_trx_original number;
2432 l_prev_trx_balance number;
2433 l_rac_prev_bill_to_cust_name hz_parties.party_name%type;
2434 l_rac_prev_bill_to_cust_num hz_cust_accounts.account_number%type;
2435 l_bs_prev_source_name ra_batch_sources.name%type;
2436 l_ctt_prev_class ra_cust_trx_types.type%type;
2437 l_ctt_prev_allow_overapp_flag
2438 ra_cust_trx_types.allow_overapplication_flag%type;
2439 l_ctt_prev_natural_app_only
2440 ra_cust_trx_types.natural_application_only_flag%type;
2441 l_ct_prev_open_receivables
2442 ra_cust_trx_types.accounting_affect_flag%type; /* Bug-3954193 */
2443 l_ct_prev_post_to_gl_flag
2444 ra_cust_trx_types.post_to_gl%type; /* Bug-3954193 */
2445 l_al_cm_reason_meaning ar_lookups.meaning%type;
2446 l_ct_prev_rowid rowid;
2447 l_commit_memo_line_id ra_customer_trx_lines.memo_line_id%type;
2448 l_commit_memo_line_desc ar_memo_lines.description%type;
2449
2450 BEGIN
2451
2452 arp_util.debug('arp_process_header.post_query()+');
2453
2454 /*-----------------------------------------+
2455 | Initialize the OUT NOCOPY parameters to NULL |
2456 +-----------------------------------------*/
2457
2458 p_ct_commitment_trx_date := NULL;
2459 p_ct_commitment_number := NULL;
2460 p_gd_commitment_gl_date := NULL;
2461 p_ctl_commit_cust_trx_line_id := NULL;
2462 p_ctl_commitment_amount := NULL;
2463 p_ctl_commitment_text := NULL;
2464 p_ctl_commitment_inv_item_id := NULL;
2465 p_interface_line_context := NULL;
2466 p_interface_line_attribute1 := NULL;
2467 p_interface_line_attribute2 := NULL;
2468 p_interface_line_attribute3 := NULL;
2469 p_interface_line_attribute4 := NULL;
2470 p_interface_line_attribute5 := NULL;
2471 p_interface_line_attribute6 := NULL;
2472 p_interface_line_attribute7 := NULL;
2473 p_interface_line_attribute8 := NULL;
2474 p_interface_line_attribute9 := NULL;
2475 p_interface_line_attribute10 := NULL;
2476 p_interface_line_attribute11 := NULL;
2477 p_interface_line_attribute12 := NULL;
2478 p_interface_line_attribute13 := NULL;
2479 p_interface_line_attribute14 := NULL;
2480 p_interface_line_attribute15 := NULL;
2481 p_default_ussgl_trx_code := NULL;
2482 p_attribute_category := NULL;
2483 p_attribute1 := NULL;
2484 p_attribute2 := NULL;
2485 p_attribute3 := NULL;
2486 p_attribute4 := NULL;
2487 p_attribute5 := NULL;
2488 p_attribute6 := NULL;
2489 p_attribute7 := NULL;
2490 p_attribute8 := NULL;
2491 p_attribute9 := NULL;
2492 p_attribute10 := NULL;
2493 p_attribute11 := NULL;
2494 p_attribute12 := NULL;
2495 p_attribute13 := NULL;
2496 p_attribute14 := NULL;
2497 p_attribute15 := NULL;
2498 p_ct_prev_trx_number := NULL;
2499 p_ct_prev_trx_reference := NULL;
2500 p_ct_prev_inv_currency_code := NULL;
2501 p_ct_prev_trx_date := NULL;
2502 p_ct_prev_bill_to_customer_id := NULL;
2503 p_ct_prev_ship_to_customer_id := NULL;
2504 p_ct_prev_sold_to_customer_id := NULL;
2505 p_ct_prev_paying_customer_id := NULL;
2506 p_ct_prev_bill_to_site_use_id := NULL;
2507 p_ct_prev_ship_to_site_use_id := NULL;
2508 p_ct_prev_paying_site_use_id := NULL;
2509 p_ct_prev_bill_to_contact_id := NULL;
2510 p_ct_prev_ship_to_contact_id := NULL;
2511 p_ct_prev_initial_cust_trx_id := NULL;
2512 p_ct_prev_primary_salesrep_id := NULL;
2513 p_ct_prev_invoicing_rule_id := NULL;
2514 p_gd_prev_gl_date := NULL;
2515 p_prev_trx_original := NULL;
2516 p_prev_trx_balance := NULL;
2517 p_rac_prev_bill_to_cust_name := NULL;
2518 p_rac_prev_bill_to_cust_num := NULL;
2519 p_bs_prev_source_name := NULL;
2520 p_ctt_prev_class := NULL;
2521 p_ctt_prev_allow_overapp_flag := NULL;
2522 p_ctt_prev_natural_app_only := NULL;
2523 p_ct_prev_open_receivables := NULL; /* Bug-3954193 */
2524 p_ct_prev_post_to_gl_flag := NULL; /* Bug-3954193 */
2525 p_al_cm_reason_meaning := NULL;
2526 p_commit_memo_line_id := NULL;
2527 p_commit_memo_line_desc := NULL;
2528
2529
2530 /*------------------------------+
2531 | Child Of A Commitment case |
2532 +------------------------------*/
2533
2534 IF ( p_initial_customer_trx_id IS NOT NULL )
2535 THEN
2536 SELECT ct_commit.trx_date,
2537 ct_commit.trx_number,
2538 gd_commit.gl_date,
2539 ctl_commit.inventory_item_id
2540 INTO l_ct_commitment_trx_date,
2541 l_ct_commitment_number,
2542 l_gd_commitment_gl_date,
2543 l_ctl_commitment_inv_item_id
2544 FROM ra_customer_trx ct_commit,
2545 ra_cust_trx_line_gl_dist gd_commit,
2546 ra_customer_trx_lines ctl_commit
2547 WHERE ct_commit.customer_trx_id = p_initial_customer_trx_id
2548 AND ct_commit.customer_trx_id = ctl_commit.customer_trx_id
2549 AND ct_commit.customer_trx_id = gd_commit.customer_trx_id
2550 AND 'REC' = gd_commit.account_class(+)
2551 AND 'Y' = gd_commit.latest_rec_flag(+);
2552
2553 p_ct_commitment_trx_date := l_ct_commitment_trx_date;
2554 p_ct_commitment_number := l_ct_commitment_number;
2555 p_gd_commitment_gl_date := l_gd_commitment_gl_date;
2556 p_ctl_commitment_inv_item_id := l_ctl_commitment_inv_item_id;
2557
2558 END IF;
2559
2560
2561 /*-------------------+
2562 | Commitment case |
2563 +-------------------*/
2564
2565 IF (p_class IN ('DEP', 'GUAR'))
2566 THEN
2567 BEGIN
2568 SELECT ctl_commit.customer_trx_line_id,
2569 ctl_commit.extended_amount,
2570 ctl_commit.description,
2571 ctl_commit.inventory_item_id,
2572 ctl_commit.interface_line_context,
2573 ctl_commit.interface_line_attribute1,
2574 ctl_commit.interface_line_attribute2,
2575 ctl_commit.interface_line_attribute3,
2576 ctl_commit.interface_line_attribute4,
2577 ctl_commit.interface_line_attribute5,
2578 ctl_commit.interface_line_attribute6,
2579 ctl_commit.interface_line_attribute7,
2580 ctl_commit.interface_line_attribute8,
2581 ctl_commit.interface_line_attribute9,
2582 ctl_commit.interface_line_attribute10,
2583 ctl_commit.interface_line_attribute11,
2584 ctl_commit.interface_line_attribute12,
2585 ctl_commit.interface_line_attribute13,
2586 ctl_commit.interface_line_attribute14,
2587 ctl_commit.interface_line_attribute15,
2588 ctl_commit.attribute_category,
2589 ctl_commit.attribute1,
2590 ctl_commit.attribute2,
2591 ctl_commit.attribute3,
2592 ctl_commit.attribute4,
2593 ctl_commit.attribute5,
2594 ctl_commit.attribute6,
2595 ctl_commit.attribute7,
2596 ctl_commit.attribute8,
2597 ctl_commit.attribute9,
2598 ctl_commit.attribute10,
2599 ctl_commit.attribute11,
2600 ctl_commit.attribute12,
2601 ctl_commit.attribute13,
2602 ctl_commit.attribute14,
2603 ctl_commit.attribute15,
2604 ctl_commit.default_ussgl_transaction_code,
2605 ctl_commit.memo_line_id
2606 INTO l_ctl_commit_cust_trx_line_id,
2607 l_ctl_commitment_amount,
2608 l_ctl_commitment_text,
2609 l_ctl_commitment_inv_item_id,
2610 l_interface_line_context,
2611 l_interface_line_attribute1,
2612 l_interface_line_attribute2,
2613 l_interface_line_attribute3,
2614 l_interface_line_attribute4,
2615 l_interface_line_attribute5,
2616 l_interface_line_attribute6,
2617 l_interface_line_attribute7,
2618 l_interface_line_attribute8,
2619 l_interface_line_attribute9,
2620 l_interface_line_attribute10,
2621 l_interface_line_attribute11,
2622 l_interface_line_attribute12,
2623 l_interface_line_attribute13,
2624 l_interface_line_attribute14,
2625 l_interface_line_attribute15,
2626 l_attribute_category,
2627 l_attribute1,
2628 l_attribute2,
2629 l_attribute3,
2630 l_attribute4,
2631 l_attribute5,
2632 l_attribute6,
2633 l_attribute7,
2634 l_attribute8,
2635 l_attribute9,
2636 l_attribute10,
2637 l_attribute11,
2638 l_attribute12,
2639 l_attribute13,
2640 l_attribute14,
2641 l_attribute15,
2642 l_default_ussgl_trx_code,
2643 l_commit_memo_line_id
2644 FROM ra_customer_trx_lines ctl_commit
2645 WHERE ctl_commit.customer_trx_id = p_customer_trx_id
2646 AND 1 = ctl_commit.line_number
2647 AND 'LINE' = ctl_commit.line_type;
2648
2649 p_ctl_commit_cust_trx_line_id :=
2650 l_ctl_commit_cust_trx_line_id;
2651 p_ctl_commitment_amount :=
2652 l_ctl_commitment_amount;
2653 p_ctl_commitment_text :=
2654 l_ctl_commitment_text;
2655 p_ctl_commitment_inv_item_id :=
2656 l_ctl_commitment_inv_item_id;
2657 p_interface_line_context :=
2658 l_interface_line_context;
2659 p_interface_line_attribute1 :=
2660 l_interface_line_attribute1;
2661 p_interface_line_attribute2 :=
2662 l_interface_line_attribute2;
2663 p_interface_line_attribute3 :=
2664 l_interface_line_attribute3;
2665 p_interface_line_attribute4 :=
2666 l_interface_line_attribute4;
2667 p_interface_line_attribute5 :=
2668 l_interface_line_attribute5;
2669 p_interface_line_attribute6 :=
2670 l_interface_line_attribute6;
2671 p_interface_line_attribute7 :=
2672 l_interface_line_attribute7;
2673 p_interface_line_attribute8 :=
2674 l_interface_line_attribute8;
2675 p_interface_line_attribute9 :=
2676 l_interface_line_attribute9;
2677 p_interface_line_attribute10 :=
2678 l_interface_line_attribute10;
2679 p_interface_line_attribute11 :=
2680 l_interface_line_attribute11;
2681 p_interface_line_attribute12 :=
2682 l_interface_line_attribute12;
2683 p_interface_line_attribute13 :=
2684 l_interface_line_attribute13;
2685 p_interface_line_attribute14 :=
2686 l_interface_line_attribute14;
2687 p_interface_line_attribute15 :=
2688 l_interface_line_attribute15;
2689 p_attribute_category := l_attribute_category;
2690 p_attribute1 := l_attribute1;
2691 p_attribute2 := l_attribute2;
2692 p_attribute3 := l_attribute3;
2693 p_attribute4 := l_attribute4;
2694 p_attribute5 := l_attribute5;
2695 p_attribute6 := l_attribute6;
2696 p_attribute7 := l_attribute7;
2697 p_attribute8 := l_attribute8;
2698 p_attribute9 := l_attribute9;
2699 p_attribute10 := l_attribute10;
2700 p_attribute11 := l_attribute11;
2701 p_attribute12 := l_attribute12;
2702 p_attribute13 := l_attribute13;
2703 p_attribute14 := l_attribute14;
2704 p_attribute15 := l_attribute15;
2705 p_default_ussgl_trx_code :=
2706 l_default_ussgl_trx_code;
2707 p_commit_memo_line_id := l_commit_memo_line_id;
2708
2709 IF l_commit_memo_line_id is NOT NULL
2710 THEN
2711 SELECT description
2712 INTO l_commit_memo_line_desc
2713 FROM ar_memo_lines
2714 WHERE memo_line_id = l_commit_memo_line_id;
2715 p_commit_memo_line_desc :=l_commit_memo_line_desc;
2716 END IF;
2717 EXCEPTION
2718 WHEN NO_DATA_FOUND THEN NULL;
2719 WHEN OTHERS THEN RAISE;
2720 END;
2721
2722 /*-------------------+
2723 | Credit Memo case |
2724 +-------------------*/
2725
2726 ELSIF ( p_previous_customer_trx_id IS NOT NULL )
2727 THEN
2728
2729 SELECT ct_prev.rowid,
2730 ct_prev.trx_number,
2731 ct_prev.invoice_currency_code,
2732 ct_prev.trx_date,
2733 ct_prev.bill_to_customer_id,
2734 ct_prev.ship_to_customer_id,
2735 ct_prev.sold_to_customer_id,
2736 ct_prev.paying_customer_id,
2737 ct_prev.bill_to_site_use_id,
2738 ct_prev.ship_to_site_use_id,
2739 ct_prev.paying_site_use_id,
2740 ct_prev.bill_to_contact_id,
2741 ct_prev.ship_to_contact_id,
2742 ct_prev.initial_customer_trx_id,
2743 ct_prev.primary_salesrep_id,
2744 ct_prev.invoicing_rule_id,
2745 gd_prev.gl_date,
2746 gd_prev.amount,
2747 substrb(party.party_name,1,50),
2748 rac_prev.account_number,
2749 bs_prev.name,
2750 ctt_prev.type,
2751 ctt_prev.allow_overapplication_flag,
2752 ctt_prev.natural_application_only_flag,
2753 ctt_prev.accounting_affect_flag, /* Bug-3954193 */
2754 ctt_prev.post_to_gl, /* Bug-3954193 */
2755 al_cm_reason.meaning
2756 INTO l_ct_prev_rowid,
2757 l_ct_prev_trx_number,
2758 l_ct_prev_inv_currency_code,
2759 l_ct_prev_trx_date,
2760 l_ct_prev_bill_to_customer_id,
2761 l_ct_prev_ship_to_customer_id,
2762 l_ct_prev_sold_to_customer_id,
2763 l_ct_prev_paying_customer_id,
2764 l_ct_prev_bill_to_site_use_id,
2765 l_ct_prev_ship_to_site_use_id,
2766 l_ct_prev_paying_site_use_id,
2767 l_ct_prev_bill_to_contact_id,
2768 l_ct_prev_ship_to_contact_id,
2769 l_ct_prev_initial_cust_trx_id,
2770 l_ct_prev_primary_salesrep_id,
2771 l_ct_prev_invoicing_rule_id,
2772 l_gd_prev_gl_date,
2773 l_prev_trx_original,
2774 l_rac_prev_bill_to_cust_name,
2775 l_rac_prev_bill_to_cust_num,
2776 l_bs_prev_source_name,
2777 l_ctt_prev_class,
2778 l_ctt_prev_allow_overapp_flag,
2779 l_ctt_prev_natural_app_only,
2780 l_ct_prev_open_receivables, /* Bug-3954193 */
2781 l_ct_prev_post_to_gl_flag, /* Bug-3954193 */
2782 l_al_cm_reason_meaning
2783 FROM ra_customer_trx ct,
2784 ra_customer_trx ct_prev,
2785 ra_cust_trx_line_gl_dist gd_prev,
2786 hz_cust_accounts rac_prev,
2787 hz_parties party,
2788 ra_batch_sources bs_prev,
2789 ra_cust_trx_types ctt_prev,
2790 ar_lookups al_cm_reason
2791 WHERE ct.rowid = p_ct_rowid
2792 and ct.previous_customer_trx_id = ct_prev.customer_trx_id
2793 and ct_prev.batch_source_id = bs_prev.batch_source_id
2794 and ct_prev.cust_trx_type_id = ctt_prev.cust_trx_type_id
2795 and ct_prev.customer_trx_id = gd_prev.customer_trx_id
2796 and 'REC' = gd_prev.account_class
2797 and 'Y' = gd_prev.latest_rec_flag
2798 and ct_prev.bill_to_customer_id = rac_prev.cust_account_id
2799 and rac_prev.party_id = party.party_id
2800 and 'CREDIT_MEMO_REASON' = al_cm_reason.lookup_type(+)
2801 and ct.reason_code = al_cm_reason.lookup_code(+);
2802
2803
2804 l_ct_prev_trx_reference :=
2805 arpt_sql_func_util.get_reference(l_ct_prev_rowid);
2806
2807 l_prev_trx_balance :=
2808 arp_bal_util.get_trx_balance( p_previous_customer_trx_id,
2809 NULL);
2810
2811 p_prev_trx_balance := l_prev_trx_balance;
2812 p_ct_prev_trx_reference := l_ct_prev_trx_reference;
2813 p_ct_prev_trx_number := l_ct_prev_trx_number;
2814 p_ct_prev_inv_currency_code := l_ct_prev_inv_currency_code;
2815 p_ct_prev_trx_date := l_ct_prev_trx_date;
2816 p_ct_prev_bill_to_customer_id := l_ct_prev_bill_to_customer_id;
2817 p_ct_prev_ship_to_customer_id := l_ct_prev_ship_to_customer_id;
2818 p_ct_prev_sold_to_customer_id := l_ct_prev_sold_to_customer_id;
2819 p_ct_prev_paying_customer_id := l_ct_prev_paying_customer_id;
2820 p_ct_prev_bill_to_site_use_id := l_ct_prev_bill_to_site_use_id;
2821 p_ct_prev_ship_to_site_use_id := l_ct_prev_ship_to_site_use_id;
2822 p_ct_prev_paying_site_use_id := l_ct_prev_paying_site_use_id;
2823 p_ct_prev_bill_to_contact_id := l_ct_prev_bill_to_contact_id;
2824 p_ct_prev_ship_to_contact_id := l_ct_prev_ship_to_contact_id;
2825 p_ct_prev_initial_cust_trx_id := l_ct_prev_initial_cust_trx_id;
2826 p_ct_prev_primary_salesrep_id := l_ct_prev_primary_salesrep_id;
2827 p_ct_prev_invoicing_rule_id := l_ct_prev_invoicing_rule_id;
2828 p_gd_prev_gl_date := l_gd_prev_gl_date;
2829 p_prev_trx_original := l_prev_trx_original;
2830 p_rac_prev_bill_to_cust_name := l_rac_prev_bill_to_cust_name;
2831 p_rac_prev_bill_to_cust_num := l_rac_prev_bill_to_cust_num;
2832 p_bs_prev_source_name := l_bs_prev_source_name;
2833 p_ctt_prev_class := l_ctt_prev_class;
2834 p_ctt_prev_allow_overapp_flag := l_ctt_prev_allow_overapp_flag;
2835 p_ctt_prev_natural_app_only := l_ctt_prev_natural_app_only;
2836 p_ct_prev_open_receivables := l_ct_prev_open_receivables; /* Bug-3954193 */
2837 p_ct_prev_post_to_gl_flag := l_ct_prev_post_to_gl_flag; /* Bug-3954193 */
2838 p_al_cm_reason_meaning := l_al_cm_reason_meaning;
2839
2840 END IF;
2841
2842 /*-----------------------------------------+
2843 | Print the results to the debug stream |
2844 +-----------------------------------------*/
2845
2846 arp_util.debug('');
2847 arp_util.debug('======= results from post_query() =======');
2848 arp_util.debug('p_ct_commitment_trx_date = ' ||
2849 l_ct_commitment_trx_date);
2850 arp_util.debug('p_ct_commitment_number = ' ||
2851 l_ct_commitment_number);
2852 arp_util.debug('p_gd_commitment_gl_date = ' ||
2853 l_gd_commitment_gl_date);
2854 arp_util.debug('p_ctl_commit_cust_trx_line_id = ' ||
2855 l_ctl_commit_cust_trx_line_id);
2856 arp_util.debug('p_ctl_commitment_amount = ' ||
2857 l_ctl_commitment_amount);
2858 arp_util.debug('p_ctl_commitment_text = ' ||
2859 l_ctl_commitment_text);
2860 arp_util.debug('p_ctl_commitment_inv_item_id = ' ||
2861 l_ctl_commitment_inv_item_id);
2862 arp_util.debug('p_interface_line_context = ' ||
2863 l_interface_line_context);
2864 arp_util.debug('p_interface_line_attribute1 = ' ||
2865 l_interface_line_attribute1);
2866 arp_util.debug('p_interface_line_attribute2 = ' ||
2867 l_interface_line_attribute2);
2868 arp_util.debug('p_interface_line_attribute3 = ' ||
2869 l_interface_line_attribute3);
2870 arp_util.debug('p_interface_line_attribute4 = ' ||
2871 l_interface_line_attribute4);
2872 arp_util.debug('p_interface_line_attribute5 = ' ||
2873 l_interface_line_attribute5);
2874 arp_util.debug('p_interface_line_attribute6 = ' ||
2875 l_interface_line_attribute6);
2876 arp_util.debug('p_interface_line_attribute7 = ' ||
2877 l_interface_line_attribute7);
2878 arp_util.debug('p_interface_line_attribute8 = ' ||
2879 l_interface_line_attribute8);
2880 arp_util.debug('p_interface_line_attribute9 = ' ||
2881 l_interface_line_attribute9);
2882 arp_util.debug('p_interface_line_attribute10 = ' ||
2883 l_interface_line_attribute10);
2884 arp_util.debug('p_interface_line_attribute11 = ' ||
2885 l_interface_line_attribute11);
2886 arp_util.debug('p_interface_line_attribute12 = ' ||
2887 l_interface_line_attribute12);
2888 arp_util.debug('p_interface_line_attribute13 = ' ||
2889 l_interface_line_attribute13);
2890 arp_util.debug('p_interface_line_attribute14 = ' ||
2891 l_interface_line_attribute14);
2892 arp_util.debug('p_interface_line_attribute15 = ' ||
2893 l_interface_line_attribute15);
2894 arp_util.debug('p_attribute_category = ' ||
2895 l_attribute_category);
2896 arp_util.debug('p_attribute1 = ' ||
2897 l_attribute1);
2898 arp_util.debug('p_attribute2 = ' ||
2899 l_attribute2);
2900 arp_util.debug('p_attribute3 = ' ||
2901 l_attribute3);
2902 arp_util.debug('p_attribute4 = ' ||
2903 l_attribute4);
2904 arp_util.debug('p_attribute5 = ' ||
2905 l_attribute5);
2906 arp_util.debug('p_attribute6 = ' ||
2907 l_attribute6);
2908 arp_util.debug('p_attribute7 = ' ||
2909 l_attribute7);
2910 arp_util.debug('p_attribute8 = ' ||
2911 l_attribute8);
2912 arp_util.debug('p_attribute9 = ' ||
2913 l_attribute9);
2914 arp_util.debug('p_attribute10 = ' ||
2915 l_attribute10);
2916 arp_util.debug('p_attribute11 = ' ||
2917 l_attribute11);
2918 arp_util.debug('p_attribute12 = ' ||
2919 l_attribute12);
2920 arp_util.debug('p_attribute13 = ' ||
2921 l_attribute13);
2922 arp_util.debug('p_attribute14 = ' ||
2923 l_attribute14);
2924 arp_util.debug('p_attribute15 = ' ||
2925 l_attribute15);
2926 arp_util.debug('p_default_ussgl_trx_code = ' ||
2927 l_default_ussgl_trx_code);
2928 arp_util.debug('p_ct_prev_trx_number = ' ||
2929 l_ct_prev_trx_number);
2930 arp_util.debug('p_ct_prev_trx_reference = ' ||
2931 l_ct_prev_trx_reference);
2932 arp_util.debug('p_ct_prev_inv_currency_code = ' ||
2933 l_ct_prev_inv_currency_code);
2934 arp_util.debug('p_ct_prev_trx_date = ' ||
2935 l_ct_prev_trx_date);
2936 arp_util.debug('p_ct_prev_bill_to_customer_id = ' ||
2937 l_ct_prev_bill_to_customer_id);
2938 arp_util.debug('p_ct_prev_ship_to_customer_id = ' ||
2939 l_ct_prev_ship_to_customer_id);
2940 arp_util.debug('p_ct_prev_sold_to_customer_id = ' ||
2941 l_ct_prev_sold_to_customer_id);
2942 arp_util.debug('p_ct_prev_paying_customer_id = ' ||
2943 l_ct_prev_paying_customer_id);
2944 arp_util.debug('p_ct_prev_bill_to_site_use_id = ' ||
2945 l_ct_prev_bill_to_site_use_id);
2946 arp_util.debug('p_ct_prev_ship_to_site_use_id = ' ||
2947 l_ct_prev_ship_to_site_use_id);
2948 arp_util.debug('p_ct_prev_paying_site_use_id = ' ||
2949 l_ct_prev_paying_site_use_id);
2950 arp_util.debug('p_ct_prev_bill_to_contact_id = ' ||
2951 l_ct_prev_bill_to_contact_id);
2952 arp_util.debug('p_ct_prev_ship_to_contact_id = ' ||
2953 l_ct_prev_ship_to_contact_id);
2954 arp_util.debug('p_ct_prev_initial_cust_trx_id = ' ||
2955 l_ct_prev_initial_cust_trx_id);
2956 arp_util.debug('p_ct_prev_primary_salesrep_id = ' ||
2957 l_ct_prev_primary_salesrep_id);
2958 arp_util.debug('p_ct_prev_invoicing_rule_id = ' ||
2959 l_ct_prev_invoicing_rule_id);
2960 arp_util.debug('p_gd_prev_gl_date = ' ||
2961 l_gd_prev_gl_date);
2962 arp_util.debug('p_prev_trx_original = ' ||
2963 l_prev_trx_original);
2964 arp_util.debug('p_prev_trx_balance = ' ||
2965 l_prev_trx_balance);
2966 arp_util.debug('p_rac_prev_bill_to_cust_name = ' ||
2967 l_rac_prev_bill_to_cust_name);
2968 arp_util.debug('p_rac_prev_bill_to_cust_num = ' ||
2969 l_rac_prev_bill_to_cust_num);
2970 arp_util.debug('p_bs_prev_source_name = ' ||
2971 l_bs_prev_source_name);
2972 arp_util.debug('p_ctt_prev_class = ' ||
2973 l_ctt_prev_class);
2974 arp_util.debug('p_ctt_prev_allow_overapp_flag = ' ||
2975 l_ctt_prev_allow_overapp_flag);
2976 arp_util.debug('p_ctt_prev_natural_app_only = ' ||
2977 l_ctt_prev_natural_app_only);
2978 arp_util.debug('p_ct_prev_open_receivables = ' ||
2979 l_ct_prev_open_receivables);
2980 arp_util.debug('p_ct_prev_post_to_gl_flag = ' ||
2981 l_ct_prev_post_to_gl_flag );
2982 arp_util.debug('p_al_cm_reason_meaning = ' ||
2983 l_al_cm_reason_meaning);
2984
2985 arp_util.debug('arp_process_header.post_query()-');
2986
2987 EXCEPTION
2988 WHEN OTHERS THEN
2989 arp_util.debug('EXCEPTION : '||
2990 'arp_process_header.post_query()-');
2991 arp_util.debug('------- parameters for post_query ----');
2992 arp_util.debug('p_class = ' || p_class);
2993 arp_util.debug('p_ct_rowid = ' || p_ct_rowid);
2994 arp_util.debug('p_customer_trx_id = ' || p_customer_trx_id);
2995 arp_util.debug('p_initial_customer_trx_id = ' ||
2996 p_initial_customer_trx_id);
2997 arp_util.debug('p_previous_customer_trx_id = ' ||
2998 p_previous_customer_trx_id);
2999
3000 RAISE;
3001 END;
3002
3003 /*---------------------------------------------+
3004 | Package initialization section. |
3005 +---------------------------------------------*/
3006 PROCEDURE init IS
3007 BEGIN
3008
3009 pg_text_dummy := arp_ct_pkg.get_text_dummy;
3010 pg_flag_dummy := arp_ct_pkg.get_flag_dummy;
3011 pg_number_dummy := arp_ct_pkg.get_number_dummy;
3012 pg_date_dummy := arp_ct_pkg.get_date_dummy;
3013 pg_earliest_date := to_date('01/01/1901', 'DD/MM/YYYY');
3014
3015 pg_base_curr_code := arp_global.functional_currency;
3016 pg_base_precision := arp_global.base_precision;
3017 pg_base_min_acc_unit := arp_global.base_min_acc_unit;
3018 pg_set_of_books_id :=
3019 arp_trx_global.system_info.system_parameters.set_of_books_id;
3020 /* bug 3567353 */
3021 pg_trx_header_level_rounding := arp_global.sysparam.trx_header_level_rounding;
3022
3023 /* 5633334/5637907 */
3024 fnd_profile.get('AR_USE_INV_ACCT_FOR_CM_FLAG', pg_use_inv_acctg);
3025 IF pg_use_inv_acctg IS NULL
3026 THEN
3027 pg_use_inv_acctg := 'N';
3028 END IF;
3029
3030 END init;
3031
3032 BEGIN
3033 init;
3034 END ARP_PROCESS_HEADER;