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