[Home] [Help]
PACKAGE BODY: APPS.ARP_MAINTAIN_PS
Source
1 PACKAGE BODY arp_maintain_ps AS
2 /* $Header: ARTEMPSB.pls 120.15.12010000.2 2008/11/11 13:32:30 rasarasw ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 ------------------------------------------------------------------------
6 -- Private types
7 ------------------------------------------------------------------------
8 -- Constants
9 --
10 -- Linefeed character
11 --
12 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
13
14 MSG_LEVEL_BASIC CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_BASIC;
15 MSG_LEVEL_TIMING CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_TIMING;
16 MSG_LEVEL_DEBUG CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG;
17 MSG_LEVEL_DEBUG2 CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG2;
18 MSG_LEVEL_DEVELOP CONSTANT BINARY_INTEGER :=
19 arp_global.MSG_LEVEL_DEVELOP;
20
21 YES CONSTANT VARCHAR2(1) := arp_global.YES;
22 NO CONSTANT VARCHAR2(1) := arp_global.NO;
23
24 DEP CONSTANT VARCHAR2(10) := 'DEP';
25 GUAR CONSTANT VARCHAR2(10) := 'GUAR';
26
27 I CONSTANT VARCHAR2(10) := 'I';
28 U CONSTANT VARCHAR2(10) := 'U';
29 D CONSTANT VARCHAR2(10) := 'D';
30
31 --
32 -- User-defined exceptions
33 --
34 invalid_parameters EXCEPTION;
35 invalid_mode EXCEPTION;
36
37
38 --
39 -- Translated error messages
40 --
41 MSG_INVALID_PARAMETERS VARCHAR2(240);
42
43
44 -- This record holds general information used by autoaccounting and
45 -- credit memo module. Passed as argument to most functions/procs.
46 --
47 system_info arp_trx_global.system_info_rec_type :=
48 arp_trx_global.system_info;
49
50 --
51 -- This record holds profile information used by autoaccounting and
52 -- credit memo module. Passed as argument to most functions/procs.
53 --
54 profile_info arp_trx_global.profile_rec_type :=
55 arp_trx_global.profile_info;
56
57
58 TYPE control_rec_type IS RECORD
59 (
60 process_mode VARCHAR2(1),
61 customer_trx_id BINARY_INTEGER,
62 payment_schedule_id BINARY_INTEGER,
63 line_amount NUMBER,
64 tax_amount NUMBER,
65 freight_amount NUMBER,
66 charge_amount NUMBER,
67 reversed_cash_receipt_id NUMBER,
68 trx_type ra_cust_trx_types.type%type,
69 is_open_receivable BOOLEAN,
70 is_postable BOOLEAN,
71 is_child BOOLEAN,
72 is_onacct_cm BOOLEAN,
73 previous_customer_trx_id BINARY_INTEGER,
74 initial_customer_trx_id BINARY_INTEGER,
75 initial_trx_type ra_cust_trx_types.type%type
76 );
77
78 /* VAT changes */
79 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
80
81 ------------------------------------------------------------------------
82 -- Private cursors
83 ------------------------------------------------------------------------
84
85
86 ------------------------------------------------------------------------
87 -- Covers
88 ------------------------------------------------------------------------
89 PROCEDURE debug( p_line IN VARCHAR2 ) IS
90 BEGIN
91 arp_util.debug( p_line );
92 END;
93 --
94 PROCEDURE debug(
95 p_str VARCHAR2,
96 p_print_level BINARY_INTEGER ) IS
97 BEGIN
98 arp_util.debug( p_str, p_print_level );
99 END;
100 --
101 PROCEDURE enable_debug IS
102 BEGIN
103 arp_util.enable_debug;
104 END;
105 --
106 PROCEDURE enable_debug( buffer_size NUMBER ) IS
107 BEGIN
108 arp_util.enable_debug( buffer_size );
109 END;
110 --
111 PROCEDURE disable_debug IS
112 BEGIN
113 arp_util.disable_debug;
114 END;
115 --
116 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
117 BEGIN
118 arp_util.print_fcn_label( p_label );
119 END;
120 --
121 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
122 BEGIN
123 arp_util.print_fcn_label2( p_label );
124 END;
125 --
126 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY INTEGER ) IS
127 BEGIN
128 arp_util.close_cursor( p_cursor_handle );
129 END;
130
131
132 ----------------------------------------------------------------------------
133 -- Functions and Procedures
134 ----------------------------------------------------------------------------
135
136 PROCEDURE close_cursors IS
137 BEGIN
138
139 close_cursor( arp_maintain_ps2.ips_insert_ps_c );
140 close_cursor( arp_maintain_ps2.ips_select_c );
141
142 close_cursor( arp_maintain_ps2.ira_insert_ps_c );
143 close_cursor( arp_maintain_ps2.ira_insert_ra_c );
144 close_cursor( arp_maintain_ps2.ira_update_ps_c );
145 close_cursor( arp_maintain_ps2.ira_select_c );
146
147 close_cursor( arp_maintain_ps2.ups_insert_adj_c );
148 close_cursor( arp_maintain_ps2.ups_update_ps_c );
149 close_cursor( arp_maintain_ps2.ups_select_c );
150
151 close_cursor( arp_maintain_ps2.iad_insert_adj_c );
152 close_cursor( arp_maintain_ps2.iad_update_ps_c );
153 close_cursor( arp_maintain_ps2.iad_select_c );
154
155 END;
156
157
158 ----------------------------------------------------------------------------
159 PROCEDURE get_error_message_text is
160
161 l_msg_name VARCHAR2(100);
162
163 BEGIN
164
165 print_fcn_label( 'arp_maintain_ps.get_error_message_text()+' );
166
167 ---
168 l_msg_name := 'AR_INV_ARGS';
169 fnd_message.set_name('AR', l_msg_name);
170 fnd_message.set_token('PROCEDURE','arp_maintain_ps.get_error_message_text()');
171 MSG_INVALID_PARAMETERS := fnd_message.get;
172
173 -- print
174 debug( 'MSG_INVALID_PARAMETERS='||MSG_INVALID_PARAMETERS,
175 MSG_LEVEL_DEBUG );
176
177 print_fcn_label( 'arp_maintain_ps.get_error_message_text()-' );
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 debug('EXCEPTION: arp_maintain_ps.get_error_message_text()');
182 RAISE;
183 END get_error_message_text;
184
185 ----------------------------------------------------------------------------
186
187 PROCEDURE do_setup( p_control_rec IN OUT NOCOPY control_rec_type ) IS
188
189 l_open_rec VARCHAR2(1);
190 l_post_to_gl VARCHAR2(1);
191 l_onacct_cm VARCHAR2(1);
192
193
194 BEGIN
195
196 print_fcn_label( 'arp_maintain_ps.do_setup()+' );
197
198 BEGIN
199
200 SELECT
201 ctt.type,
202 ctt.accounting_affect_flag,
203 ctt.post_to_gl,
204 decode(ctt.type,
205 'CM', decode(ct.previous_customer_trx_id,
206 null, 'Y','N'),
207 'N'), /* determine if onacct cm */
208 ct.previous_customer_trx_id,
209 ct.initial_customer_trx_id,
210 ctt_init.type
211 INTO
212 p_control_rec.trx_type,
213 l_open_rec,
214 l_post_to_gl,
215 l_onacct_cm,
216 p_control_rec.previous_customer_trx_id,
217 p_control_rec.initial_customer_trx_id,
218 p_control_rec.initial_trx_type
219 FROM
220 ra_cust_trx_types ctt_init,
221 ra_customer_trx ct_init,
222 ra_cust_trx_types ctt,
223 ra_customer_trx ct
224 WHERE ct.customer_trx_id = p_control_rec.customer_trx_id
225 and ct.cust_trx_type_id = ctt.cust_trx_type_id
226 and ct.initial_customer_trx_id = ct_init.customer_trx_id(+)
227 and ct_init.cust_trx_type_id = ctt_init.cust_trx_type_id(+);
228
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 debug( 'EXCEPTION: Error executing select stmt #1',
233 MSG_LEVEL_BASIC );
234 RAISE;
235 END;
236 --
237 --
238 IF( l_open_rec = YES ) THEN
239 p_control_rec.is_open_receivable := TRUE;
240 ELSE
241 p_control_rec.is_open_receivable := FALSE;
242 END IF;
243 --
244 --
245 IF( l_post_to_gl = YES ) THEN
246 p_control_rec.is_postable := TRUE;
247 ELSE
248 p_control_rec.is_postable := FALSE;
249 END IF;
250 --
251 --
252 IF( l_onacct_cm = YES ) THEN
253 p_control_rec.is_onacct_cm := TRUE;
254 ELSE
255 p_control_rec.is_onacct_cm := FALSE;
256 END IF;
257 --
258 --
259
260 -- check if regular CM
261 IF( p_control_rec.previous_customer_trx_id IS NOT NULL ) THEN
262
263
264 BEGIN
265 SELECT
266 ct_prev.initial_customer_trx_id,
267 ctt_init.type
268 INTO
269 p_control_rec.initial_customer_trx_id,
270 p_control_rec.initial_trx_type
271 FROM
272 ra_customer_trx ct,
273 ra_customer_trx ct_prev,
274 ra_customer_trx ct_init,
275 ra_cust_trx_types ctt_init
276 WHERE ct.customer_trx_id = p_control_rec.customer_trx_id
277 and ct.previous_customer_trx_id = ct_prev.customer_trx_id
278 and ct_prev.initial_customer_trx_id = ct_init.customer_trx_id(+)
279 and ct_init.cust_trx_type_id = ctt_init.cust_trx_type_id(+);
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 debug( 'EXCEPTION: Error executing select stmt #2',
284 MSG_LEVEL_BASIC );
285 RAISE;
286 END;
287
288 END IF;
289
290
291 IF( p_control_rec.initial_customer_trx_id IS NOT NULL ) THEN
292
293 /* Bugfix 2742877.
294 Check if adjustments exist before actually setting the flag.
295 - For invoices, at this stage, there will be no adjustments.
296 - We should check for existence of commitments adjustments
297 against invoices,ONLY for regular CMs before setting the
298 is_child flag.
299 - The commitment adj will be against guarantee's customer_trx_id
300 for GUAR
301 - It will be against invoice's customer_trx_id for the DEP type
302 commitment.
303 Bug 2808262. When the CM is for lesser amount than the inv balance,
304 the cmtmt adj is not reversed. Set the is_child flag accordingly.
305 */
306
307 IF( p_control_rec.previous_customer_trx_id IS NOT NULL ) THEN
308
309 DECLARE
310 l_adj_exists NUMBER;
311 BEGIN
312
313 /* salladi 3118714 */
314 IF p_control_rec.process_mode = 'I' THEN
315
316 SELECT 1
317 INTO l_adj_exists
318 FROM ar_adjustments
319 WHERE customer_trx_id = p_control_rec.previous_customer_trx_id
320 AND adjustment_type = 'C'
321 AND receivables_trx_id = -1
322 AND rownum = 1
323 UNION ALL
324 SELECT 1
325 FROM ar_adjustments
326 WHERE subsequent_trx_id = p_control_rec.previous_customer_trx_id
327 AND adjustment_type = 'C'
328 AND receivables_trx_id = -1
329 AND rownum = 1;
330 ELSE
331
332
333 /* IF p_control_rec.process_mode = 'I' THEN
334 IF p_control_rec.initial_trx_type = 'DEP' THEN
335 SELECT 1
336 INTO l_adj_exists
337 FROM ar_adjustments
338 WHERE customer_trx_id = p_control_rec.previous_customer_trx_id
339 AND adjustment_type = 'C'
340 AND receivables_trx_id = -1
341 AND rownum = 1;
342 ELSIF p_control_rec.initial_trx_type = 'GUAR' THEN
343 SELECT 1
344 INTO l_adj_exists
345 FROM ar_adjustments
346 WHERE subsequent_trx_id = p_control_rec.previous_customer_trx_id
347 AND adjustment_type = 'C'
348 AND receivables_trx_id = -1
349 AND rownum = 1;
350 END IF;
351 ELSE */
352 /* salladi */
353
354 IF p_control_rec.initial_trx_type = 'DEP' THEN
355 SELECT 1
356 INTO l_adj_exists
357 FROM ar_adjustments
358 WHERE customer_trx_id = p_control_rec.previous_customer_trx_id
359 AND subsequent_trx_id = p_control_rec.customer_trx_id
360 AND adjustment_type = 'C'
361 AND receivables_trx_id = -1
362 AND rownum = 1;
363 ELSIF p_control_rec.initial_trx_type = 'GUAR' THEN
364 SELECT 1
365 INTO l_adj_exists
366 FROM ar_adjustments
367 WHERE customer_trx_id = p_control_rec.initial_customer_trx_id
368 AND subsequent_trx_id = p_control_rec.customer_trx_id
369 AND adjustment_type = 'C'
370 AND receivables_trx_id = -1
371 AND rownum = 1;
372 END IF;
373 END IF;
374
375 p_control_rec.is_child := TRUE;
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 p_control_rec.is_child := FALSE;
379 WHEN OTHERS THEN
380 debug( 'EXCEPTION: Error executing select stmt for check for adjustments',
381 MSG_LEVEL_BASIC );
382 RAISE;
383 END;
384
385 ELSE
386 p_control_rec.is_child := TRUE;
387 END IF;
388
389 ELSE
390 p_control_rec.is_child := FALSE;
391 END IF;
392
393
394 print_fcn_label( 'arp_maintain_ps.do_setup()-' );
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 debug( 'EXCEPTION: arp_maintain_ps.do_setup()',
399 MSG_LEVEL_BASIC );
400 RAISE;
401
402 END do_setup;
403
404
405 ----------------------------------------------------------------------------
406 PROCEDURE build_doc_combo_sql(
407 p_system_info IN arp_trx_global.system_info_rec_type,
408 p_profile_info IN arp_trx_global.profile_rec_type,
409 p_select_c IN OUT NOCOPY INTEGER ) IS
410
411 l_select_sql VARCHAR2(500);
412
413
414 BEGIN
415
416 print_fcn_label( 'arp_maintain_ps.build_doc_combo_sql()+' );
417
418 ------------------------------------------------
419 -- Select sql
420 ------------------------------------------------
421 l_select_sql :=
422 'SELECT
423 ct.set_of_books_id,
424 ct.trx_date,
425 rt.name
426 FROM
427 ra_customer_trx ct,
428 ar_receivables_trx rt
429 WHERE rt.receivables_trx_id = -1
430 and ct.customer_trx_id = :customer_trx_id';
431
432
433 ------------------------------------------------
434 -- Parse sql stmts
435 ------------------------------------------------
436 BEGIN
437 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
438
439 p_select_c := dbms_sql.open_cursor;
440 dbms_sql.parse( p_select_c, l_select_sql,
441 dbms_sql.v7 );
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
446 RAISE;
447 END;
448
449 print_fcn_label( 'arp_maintain_ps.build_doc_combo_sql()-' );
450
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 debug( 'EXCEPTION: arp_maintain_ps.build_doc_combo_sql()',
455 MSG_LEVEL_BASIC );
456
457 RAISE;
458 END build_doc_combo_sql;
459
460
461 ----------------------------------------------------------------------------
462 PROCEDURE build_doc_insert_audit_sql(
463 p_system_info IN arp_trx_global.system_info_rec_type,
464 p_profile_info IN arp_trx_global.profile_rec_type,
465 p_where_clause IN VARCHAR2,
466 p_insert_audit_c IN OUT NOCOPY INTEGER ) IS
467
468 l_insert_audit_sql VARCHAR2(1000);
469
470
471 BEGIN
472
473 print_fcn_label( 'arp_maintain_ps.build_doc_insert_audit_sql()+' );
474
475 ------------------------------------------------
476 -- Insert audit table sql
477 ------------------------------------------------
478 l_insert_audit_sql :=
479 'INSERT INTO ar_doc_sequence_audit
480 (
481 doc_sequence_id,
482 doc_sequence_assignment_id,
483 doc_sequence_value,
484 creation_date,
485 created_by
486 )
487 SELECT
488 doc_sequence_id,
489 :sequence_assignment_id,
490 doc_sequence_value,
491 creation_date,
492 created_by
493 FROM AR_ADJUSTMENTS'||CRLF||p_where_clause;
494
495
496 ------------------------------------------------
497 -- Parse sql stmts
498 ------------------------------------------------
499 BEGIN
500 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
501
502 p_insert_audit_c := dbms_sql.open_cursor;
503 dbms_sql.parse( p_insert_audit_c, l_insert_audit_sql,
504 dbms_sql.v7 );
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
509 RAISE;
510 END;
511
512 print_fcn_label( 'arp_maintain_ps.build_doc_insert_audit_sql()-' );
513
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 debug( 'EXCEPTION: arp_maintain_ps.build_doc_insert_audit_sql()',
518 MSG_LEVEL_BASIC );
519
520 RAISE;
521 END build_doc_insert_audit_sql;
522
523
524 ----------------------------------------------------------------------------
525 PROCEDURE build_doc_update_adj_sql(
526 p_system_info IN arp_trx_global.system_info_rec_type,
527 p_profile_info IN arp_trx_global.profile_rec_type,
528 p_sequence_name IN VARCHAR2,
529 p_sequence_id IN BINARY_INTEGER,
530 p_where_clause IN VARCHAR2,
531 p_update_adj_c IN OUT NOCOPY INTEGER ) IS
532
533 l_update_adj_sql VARCHAR2(1000);
534
535
536
537 BEGIN
538
539 print_fcn_label( 'arp_maintain_ps.build_doc_update_adj_sql()+' );
540
541 ------------------------------------------------
542 -- Update adjustments sql
543 ------------------------------------------------
544 --Bug 1508981 - Update statement modified to take the sequence value
545 --provided, rather than getting the sequence_name.nextval. Previous method
546 --did not work for gaples sequence.
547 --p_sequence_name contains the sequence value now.
548
549 l_update_adj_sql :=
550 'UPDATE ar_adjustments adj
551 SET
552 doc_sequence_value = ' || p_sequence_name || ',' || CRLF ||
553 'doc_sequence_id = ' || p_sequence_id || CRLF || p_where_clause;
554
555
556 ------------------------------------------------
557 -- Parse sql stmts
558 ------------------------------------------------
559 BEGIN
560 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
561
562 p_update_adj_c := dbms_sql.open_cursor;
563 dbms_sql.parse( p_update_adj_c, l_update_adj_sql,
564 dbms_sql.v7 );
565
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
570 RAISE;
571 END;
572
573 print_fcn_label( 'arp_maintain_ps.build_doc_update_adj_sql()-' );
574
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 debug( 'EXCEPTION: arp_maintain_ps.build_doc_update_adj_sql()',
579 MSG_LEVEL_BASIC );
580
581 RAISE;
582 END build_doc_update_adj_sql;
583
584
585 ----------------------------------------------------------------------------
586 PROCEDURE update_adj_document_number(
587 p_system_info IN arp_trx_global.system_info_rec_type,
588 p_profile_info IN arp_trx_global.profile_rec_type,
589 p_customer_trx_id BINARY_INTEGER,
590 p_bind1 BINARY_INTEGER,
591 p_bind2 BINARY_INTEGER,
592 p_bind3 BINARY_INTEGER,
593 p_update_where_clause VARCHAR2 ) IS
594
595
596
597 l_set_of_books_id BINARY_INTEGER;
598 l_trx_date DATE;
599 l_type VARCHAR2(500);
600
601 l_sequence_name VARCHAR2(500);
602 l_sequence_id BINARY_INTEGER;
603 l_sequence_assignment_id BINARY_INTEGER;
604 l_sequence_value NUMBER;
605 l_dummy NUMBER;
606 l_sequence_type VARCHAR2(100);
607 l_db_sequence_name VARCHAR2(100);
608 l_prod_table_name VARCHAR2(50);
609 l_audit_table_name VARCHAR2(50);
610 l_mesg_flag VARCHAR2(1);
611 l_ignore INTEGER;
612 v_profVal varchar2(40);--Bug 1796816
613 --BugFix 2095183 Added the Following 2 Statements.
614 l_update_where_clause varchar2(2000) default NULL;
615 /* bugfix 2454787 */
616 cursor c_ar_adjustments(cust_trx_id BINARY_INTEGER) is
617 select adjustment_id from ar_adjustments where customer_trx_id=cust_trx_id
618 UNION
619 select adjustment_id from ar_adjustments where subsequent_trx_id = cust_trx_id;
620
621 BEGIN
622
623 print_fcn_label( 'arp_maintain_ps.update_adj_document_number()+' );
624
625 ---------------------------------------------------------------
626 -- Build dynamic sql
627 ---------------------------------------------------------------
628 IF( NOT( dbms_sql.is_open( doc_combo_select_c ) ) ) THEN
629
630 build_doc_combo_sql(
631 system_info,
632 profile_info,
633 doc_combo_select_c );
634
635 END IF;
636
637 build_doc_insert_audit_sql(
638 system_info,
639 profile_info,
640 p_update_where_clause,
641 doc_insert_audit_c );
642
643 ---------------------------------------------------------------
644 -- Bind variables
645 ---------------------------------------------------------------
646 dbms_sql.bind_variable( doc_combo_select_c,
647 'customer_trx_id',
648 p_customer_trx_id );
649
650 if (p_bind1 is not null) then
651 dbms_sql.bind_variable( doc_insert_audit_c,':bind1',p_bind1);
652 end if;
653
654 if (p_bind2 is not null) then
655 dbms_sql.bind_variable( doc_insert_audit_c,':bind2',p_bind2);
656 end if;
657
658 if (p_bind3 is not null) then
659 dbms_sql.bind_variable( doc_insert_audit_c,':bind3',p_bind3);
660 end if;
661
662 ---------------------------------------------------------------
663 -- Define columns
664 ---------------------------------------------------------------
665 dbms_sql.define_column( doc_combo_select_c, 1, l_set_of_books_id );
666 dbms_sql.define_column( doc_combo_select_c, 2, l_trx_date );
667 dbms_sql.define_column( doc_combo_select_c, 3, l_type, 30 );
668
669 ---------------------------------------------------------------
670 -- Execute sql
671 ---------------------------------------------------------------
672 debug( ' Executing select sql', MSG_LEVEL_DEBUG );
673
674 BEGIN
675 l_ignore := dbms_sql.execute( doc_combo_select_c );
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 debug( 'EXCEPTION: Error executing select sql',
680 MSG_LEVEL_BASIC );
681 RAISE;
682 END;
683
684
685 ---------------------------------------------------------------
686 -- Fetch rows
687 ---------------------------------------------------------------
688 BEGIN
689 LOOP
690
691 IF dbms_sql.fetch_rows( doc_combo_select_c ) > 0 THEN
692
693 debug(' Fetched a row', MSG_LEVEL_DEBUG );
694
695 -----------------------------------------------------------
696 -- Get variables:
697 -----------------------------------------------------------
698 dbms_sql.column_value( doc_combo_select_c,
699 1, l_set_of_books_id );
700 dbms_sql.column_value( doc_combo_select_c, 2, l_trx_date );
701 dbms_sql.column_value( doc_combo_select_c, 3, l_type );
702
703 debug(' set_of_books_id='||l_set_of_books_id,
704 MSG_LEVEL_DEVELOP );
705 debug(' trx_date='||l_trx_date, MSG_LEVEL_DEVELOP );
706 debug(' type='||l_type, MSG_LEVEL_DEVELOP );
707
708 -----------------------------------------------------------
709 -- Call AOL sequential numbers API
710 -----------------------------------------------------------
711 BEGIN
712
713 /********************************************
714 * Bug 1097459. *
715 * Changing all fnd sequnce calls *
716 * to the new ones. *
717 * We are using FND_SEQNUM.GET_SEQ_NAME *
718 * Instead of FND_SEQNUM.GET_SEQ_INFO *
719 *******************************************/
720
721 /* fnd_seqnum.get_seq_name(
722 222, -- application_id
723 l_type, -- category_code
724 l_set_of_books_id,
725 'A', -- method_code
726 l_trx_date,
727 l_sequence_name,
728 l_sequence_id,
729 l_sequence_assignment_id );
730 */
731
732 /* Bug NO:1796816-Passed parameters supress_error,supress_warning
733 as 'Y','Y' so taht FND error message will be supressed
734 when sequence numbering profile option is set to Partial
735 used.
736 */
737
738 FND_PROFILE.GET( 'UNIQUE:SEQ_NUMBERS', v_profVal );
739 debug(' v_profVal='||v_profVal, MSG_LEVEL_DEVELOP );
740 if(v_profVal = 'P') THEN
741 l_dummy:= FND_SEQNUM.GET_SEQ_INFO(
742 222, -- application_id
743 l_type, -- category_code
744 l_set_of_books_id,
745 'A', -- method_code
746 l_trx_date,
747 l_sequence_id,
748 l_sequence_type,
749 l_sequence_name,
750 l_db_sequence_name,
751 l_sequence_assignment_id,
752 l_prod_table_name,
753 l_audit_table_name,
754 l_mesg_flag,'y','y');
755
756 else
757 l_dummy:= FND_SEQNUM.GET_SEQ_INFO(
758 222, -- application_id
759 l_type, -- category_code
760 l_set_of_books_id,
761 'A', -- method_code
762 l_trx_date,
763 l_sequence_id,
764 l_sequence_type,
765 l_sequence_name,
766 l_db_sequence_name,
767 l_sequence_assignment_id,
768 l_prod_table_name,
769 l_audit_table_name,
770 l_mesg_flag);
771 END IF;
772
773
774 debug(' sequence_name='||l_sequence_name, MSG_LEVEL_DEVELOP );
775 debug(' sequence_id='||l_sequence_id, MSG_LEVEL_DEVELOP );
776 debug(' sequence_assignment_id='|| l_sequence_assignment_id, MSG_LEVEL_DEVELOP );
777
778 /* Bug 1535839 : When 'Sequential Numbering' is 'Not Used' , the
779 adjustment record must not be updated */
780
781
782 IF ( l_dummy = -7 or l_sequence_id is NULL ) THEN
783 GOTO skip;
784 END IF;
785
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 debug(' doc assignment does not exist',
789 MSG_LEVEL_DEBUG );
790
791 GOTO skip;
792
793 END;
794
795 /*
796 l_sequence_value :=
797 fnd_seqnum.get_next_auto_seq( l_sequence_name );
798
799 debug(' sequence_value='||
800 l_sequence_value, MSG_LEVEL_DEVELOP );
801 */
802 --BugFix 2095183 Added the Following For Loop For Fetching
803 --AR_ADJUSTMENTS Records for the Corresponding customer_trx_id.
804
805 For adj_rec in c_ar_adjustments(p_customer_trx_id) loop
806
807 --Bug 1508981 - Added the call to get the next sequence number
808
809 l_sequence_value := FND_SEQNUM.get_next_sequence (222,
810 l_type,
811 l_set_of_books_id,
812 'A',
813 l_trx_date,
814 l_db_sequence_name,
815 l_sequence_assignment_id);
816
817 -----------------------------------------------------------
818 -- Update the ar_adjustments table
819 -----------------------------------------------------------
820 BEGIN
821
822 -------------------------------------------------------
823 -- Build update stmt
824 -------------------------------------------------------
825 --BugFix 2095183 Added the Following Statement in order to add
826 --an Extra condition for p_update_where_clause.
827
828 l_update_where_clause := p_update_where_clause || ' and adjustment_id = :adjustment_id ';
829 build_doc_update_adj_sql(
830 system_info,
831 profile_info,
832 l_sequence_value, --Bug 1508981 Modified to pass the value
833 l_sequence_id,
834 l_update_where_clause, --BugFix 2095183.Changed the parameter
835 doc_update_adj_c );
836
837 -- Bind Variables
838 if (p_bind1 is not null) then
839 dbms_sql.bind_variable( doc_update_adj_c ,':bind1',p_bind1);
840 end if;
841
842 if (p_bind2 is not null) then
843 dbms_sql.bind_variable( doc_update_adj_c ,':bind2',p_bind2);
844 end if;
845
846 if (p_bind3 is not null) then
847 dbms_sql.bind_variable( doc_update_adj_c ,':bind3',p_bind3);
848 end if;
849 dbms_sql.bind_variable( doc_update_adj_c ,':adjustment_id',adj_rec.adjustment_id);
850
851
852
853 l_ignore := dbms_sql.execute( doc_update_adj_c );
854
855 /* MRC call not required because update only affects
856 doc_sequence_value and doc_sequence_id - Ie. no
857 accounting columns */
858
859 debug( to_char(l_ignore) || ' row(s) updated',
860 MSG_LEVEL_DEBUG );
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 debug( 'EXCEPTION: Error updating ar_adjustments',
865 MSG_LEVEL_BASIC );
866 RAISE;
867
868 END;
869 --BugFix 2095183 Added the following Statement.
870 END LOOP; /* For c_ar_adjustments Cursor */
871
872
873 -----------------------------------------------------------
874 -- Insert into the audit table: ar_doc_sequence_audit
875 -----------------------------------------------------------
876 BEGIN
877 -------------------------------------------------------
878 -- Bind variables
879 -------------------------------------------------------
880 dbms_sql.bind_variable(
881 doc_insert_audit_c,
882 'sequence_assignment_id',
883 l_sequence_assignment_id );
884
885 l_ignore := dbms_sql.execute( doc_insert_audit_c );
886
887 debug( to_char(l_ignore) || ' row(s) inserted',
888 MSG_LEVEL_DEBUG );
889
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 debug( 'EXCEPTION: Error inserting audit table',
894 MSG_LEVEL_BASIC );
895 RAISE;
896
897 END;
898
899 ELSE -- no more rows to fetch
900 EXIT;
901
902 END IF; -- if row was fetched
903
904
905 <<skip>>
906 NULL;
907
908 END LOOP; -- end fetching
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 debug( 'EXCEPTION: Error fetching select cursor',
913 MSG_LEVEL_BASIC );
914 RAISE;
915
916 END;
917
918
919 print_fcn_label( 'arp_maintain_ps.update_adj_document_number()-' );
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 debug( 'EXCEPTION: arp_maintain_ps.update_adj_document_number()',
924 MSG_LEVEL_BASIC );
925 RAISE;
926
927 END update_adj_document_number;
928
929
930 ----------------------------------------------------------------------------
931 PROCEDURE delete_payment_schedule( p_customer_trx_id IN BINARY_INTEGER ) IS
932
933 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
934
935 BEGIN
936
937 print_fcn_label( 'arp_maintain_ps.delete_payment_schedule()+' );
938
939 --
940 --
941 BEGIN
942
943 DELETE
944 FROM ar_payment_schedules ps
945 WHERE ps.customer_trx_id = p_customer_trx_id
946 RETURNING ps.payment_schedule_id
947 BULK COLLECT INTO l_ar_ps_key_value_list;
948
949 debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
950
951 /*-------------------------------+
952 | Calling central MRC library |
953 | for MRC integration |
954 +-------------------------------*/
955
956 ar_mrc_engine.maintain_mrc_data(
957 p_event_mode => 'DELETE',
958 p_table_name => 'AR_PAYMENT_SCHEDULES',
959 p_mode => 'BATCH',
960 p_key_value_list => l_ar_ps_key_value_list);
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 debug( 'EXCEPTION: Error executing delete stmt',
965 MSG_LEVEL_BASIC );
966 RAISE;
967 END;
968
969 print_fcn_label( 'arp_maintain_ps.delete_payment_schedule()-' );
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 debug( 'EXCEPTION: arp_maintain_ps.delete_payment_schedule()',
974 MSG_LEVEL_BASIC );
975 RAISE;
976
977 END delete_payment_schedule;
978
979
980 ----------------------------------------------------------------------------
981 PROCEDURE delete_applications( p_customer_trx_id IN BINARY_INTEGER ) IS
982
983 CURSOR del_app IS
984 select app.receivable_application_id app_id,
985 app.customer_trx_id trx_id
986 from ar_receivable_applications app
987 where app.customer_trx_id = p_customer_trx_id
988 and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
989 and exists (select 'x'
990 from ar_distributions ard
991 where ard.source_table = 'RA'
992 and ard.source_id = app.receivable_application_id); --delete only necessary records
993
994 l_rec_del_app del_app%ROWTYPE;
995 l_ae_doc_rec ae_doc_rec_type;
996
997 l_rec_app_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
998
999 BEGIN
1000
1001 print_fcn_label( 'arp_maintain_ps.delete_applications()+' );
1002
1003 BEGIN
1004
1005 FOR l_rec_del_app in del_app LOOP
1006 --
1007 --Release 11.5 VAT changes, delete accounting for Applications
1008 --
1009 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
1010 l_ae_doc_rec.document_id := l_rec_del_app.trx_id;
1011 l_ae_doc_rec.accounting_entity_level := 'ONE';
1012 l_ae_doc_rec.source_table := 'RA';
1013 l_ae_doc_rec.source_id := l_rec_del_app.app_id;
1014 l_ae_doc_rec.source_id_old := '';
1015 l_ae_doc_rec.other_flag := '';
1016
1017 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1018
1019 END LOOP;
1020
1021 --Now delete parent application record
1022 DELETE
1023 FROM ar_receivable_applications ra
1024 WHERE ra.customer_trx_id = p_customer_trx_id
1025 RETURNING receivable_application_id
1026 BULK COLLECT INTO l_rec_app_key_value_list;
1027
1028 debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
1029
1030 /*---------------------------------+
1031 | Calling central MRC library |
1032 | for MRC Integration |
1033 +---------------------------------*/
1034
1035 ar_mrc_engine.maintain_mrc_data(
1036 p_event_mode => 'DELETE',
1037 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
1038 p_mode => 'BATCH',
1039 p_key_value_list => l_rec_app_key_value_list);
1040
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 debug( 'EXCEPTION: Error executing delete stmt',
1044 MSG_LEVEL_BASIC );
1045 RAISE;
1046 END;
1047
1048 print_fcn_label( 'arp_maintain_ps.delete_applications()-' );
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 debug( 'EXCEPTION: arp_maintain_ps.delete_applications()',
1053 MSG_LEVEL_BASIC );
1054 RAISE;
1055
1056 END delete_applications;
1057
1058
1059 ----------------------------------------------------------------------------
1060 PROCEDURE delete_adjustments(
1061 p_customer_trx_id IN BINARY_INTEGER,
1062 p_subsequent_trx_id IN BINARY_INTEGER
1063 ) IS
1064 l_adj_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1065
1066 BEGIN
1067
1068 print_fcn_label( 'arp_maintain_ps.delete_adjustments()+' );
1069
1070 --
1071 --
1072 BEGIN
1073
1074 IF( p_subsequent_trx_id IS NULL ) THEN
1075
1076 DELETE
1077 FROM ar_adjustments adj
1078 WHERE adj.customer_trx_id = p_customer_trx_id
1079 and adj.receivables_trx_id = -1
1080 RETURNING adjustment_id
1081 BULK COLLECT INTO l_adj_key_value_list;
1082
1083 /*---------------------------------+
1084 | Calling central MRC library |
1085 | for MRC Integration |
1086 +---------------------------------*/
1087
1088 ar_mrc_engine.maintain_mrc_data(
1089 p_event_mode => 'DELETE',
1090 p_table_name => 'AR_ADJUSTMENTS',
1091 p_mode => 'BULK',
1092 p_key_value_list => l_adj_key_value_list);
1093
1094 ELSE
1095
1096 DELETE
1097 FROM ar_adjustments adj
1098 WHERE adj.customer_trx_id = p_customer_trx_id
1099 and adj.subsequent_trx_id = p_subsequent_trx_id
1100 and adj.receivables_trx_id = -1
1101 RETURNING adjustment_id
1102 BULK COLLECT INTO l_adj_key_value_list;
1103
1104 /*---------------------------------+
1105 | Calling central MRC library |
1106 | for MRC Integration |
1107 +---------------------------------*/
1108
1109 ar_mrc_engine.maintain_mrc_data(
1110 p_event_mode => 'DELETE',
1111 p_table_name => 'AR_ADJUSTMENTS',
1112 p_mode => 'BULK',
1113 p_key_value_list => l_adj_key_value_list);
1114
1115 END IF;
1116
1117 debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
1118
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 debug( 'EXCEPTION: Error executing delete stmt',
1122 MSG_LEVEL_BASIC );
1123 RAISE;
1124 END;
1125
1126 print_fcn_label( 'arp_maintain_ps.delete_adjustments()-' );
1127
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 debug( 'EXCEPTION: arp_maintain_ps.delete_adjustments()',
1131 MSG_LEVEL_BASIC );
1132 RAISE;
1133
1134 END delete_adjustments;
1135
1136 ----------------------------------------------------------------------------
1137 PROCEDURE reverse_adjustments(
1138 p_system_info IN arp_trx_global.system_info_rec_type,
1139 p_profile_info IN arp_trx_global.profile_rec_type,
1140 p_customer_trx_id IN BINARY_INTEGER,
1141 p_subsequent_trx_id IN BINARY_INTEGER
1142 ) IS
1143 /* VAT changes */
1144 l_ae_doc_rec ae_doc_rec_type;
1145 l_adjustment_id ar_adjustments.adjustment_id%type;
1146
1147 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1148
1149 BEGIN
1150
1151 print_fcn_label( 'arp_maintain_ps.reverse_adjustments()+' );
1152
1153 BEGIN
1154
1155 UPDATE ar_payment_schedules ps
1156 SET (
1157 ps.status,
1158 ps.gl_date_closed,
1159 ps.actual_date_closed,
1160 ps.amount_adjusted,
1161 ps.amount_due_remaining,
1162 ps.acctd_amount_due_remaining,
1163 ps.amount_line_items_remaining,
1164 ps.tax_remaining,
1165 ps.freight_remaining,
1166 ps.receivables_charges_remaining,
1167 last_updated_by,
1168 last_update_date,
1169 last_update_login) = (
1170 SELECT
1171 decode(ps2.amount_due_remaining - adj.amount, 0, 'CL', 'OP'),
1172 decode(ps2.amount_due_remaining - adj.amount,
1173 0,
1174 greatest(nvl(max(decode(ra2.confirmed_flag,
1175 'N', ps2.gl_date,
1176 ra2.gl_date)),
1177 ps2.gl_date),
1178 max(decode(adj2.customer_trx_id,
1179 p_customer_trx_id,
1180 decode(adj2.subsequent_trx_id,
1181 p_subsequent_trx_id,
1182 decode( adj2.receivables_trx_id,
1183 -1, ps2.gl_date,
1184 adj2.gl_date ),
1185 adj2.gl_date),
1186 adj2.gl_date)
1187 )
1188 ),
1189 ''),
1190 decode(ps2.amount_due_remaining - adj.amount,
1191 0,
1192 greatest(nvl(max(decode(ra2.confirmed_flag,
1193 'N', ps2.trx_date,
1194 ra2.apply_date)),
1195 ps2.trx_date),
1196 max(decode(adj2.customer_trx_id,
1197 p_customer_trx_id,
1198 decode(adj2.subsequent_trx_id,
1199 p_subsequent_trx_id,
1200 decode(adj2.receivables_trx_id,
1201 -1, ps2.trx_date,
1202 adj2.apply_date),
1203 adj2.apply_date),
1204 adj2.apply_date)
1205 )
1206 ),
1207 ''),
1208 nvl(ps2.amount_adjusted, 0) - adj.amount,
1209 ps2.amount_due_remaining - adj.amount,
1210 ps2.acctd_amount_due_remaining - adj.acctd_amount,
1211 nvl(ps2.amount_line_items_remaining, 0) -
1212 nvl(adj.line_adjusted, decode(adj.type, 'LINE', adj.amount, 0)),
1213 nvl(ps2.tax_remaining, 0) -
1214 nvl(adj.tax_adjusted, decode(adj.type, 'TAX', adj.amount, 0)),
1215 nvl(ps2.freight_remaining, 0) -
1216 nvl(adj.freight_adjusted,
1217 decode(adj.type, 'FREIGHT', adj.amount, 0)),
1218 nvl(ps2.receivables_charges_remaining, 0) -
1219 nvl(adj.receivables_charges_adjusted,
1220 decode(adj.type, 'CHARGES', adj.amount, 0)),
1221 p_profile_info.user_id,
1222 trunc(sysdate),
1223 p_profile_info.conc_login_id
1224 FROM
1225 ar_adjustments adj,
1226 ar_payment_schedules ps2,
1227 ar_adjustments adj2,
1228 ar_receivable_applications ra2
1229 WHERE adj.receivables_trx_id =-1
1230 and adj.customer_trx_id = p_customer_trx_id
1231 and adj.subsequent_trx_id = p_subsequent_trx_id
1232 and adj.payment_schedule_id = ps2.payment_schedule_id
1233 and ps2.payment_schedule_id = ps.payment_schedule_id
1234 and ps2.payment_schedule_id = adj2.payment_schedule_id
1235 and adj2.status = 'A'
1236 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id(+)
1237 GROUP BY
1238 ps2.payment_schedule_id,
1239 ra2.applied_payment_schedule_id,
1240 adj2.payment_schedule_id,
1241 ps2.amount_due_remaining,
1242 adj.amount,
1243 ps2.gl_date,
1244 ps2.trx_date,
1245 ps2.amount_adjusted,
1246 ps2.acctd_amount_due_remaining,
1247 adj.acctd_amount,
1248 ps2.amount_line_items_remaining,
1249 adj.line_adjusted,
1250 adj.type,
1251 ps2.tax_remaining,
1252 adj.tax_adjusted,
1253 ps2.freight_remaining,
1254 adj.freight_adjusted,
1255 ps2.receivables_charges_remaining,
1256 adj.receivables_charges_adjusted )
1257 WHERE ps.payment_schedule_id in
1258 (
1259 SELECT
1260 adj3.payment_schedule_id
1261 FROM ar_adjustments adj3
1262 WHERE adj3.customer_trx_id = p_customer_trx_id
1263 and adj3.subsequent_trx_id = p_subsequent_trx_id
1264 and adj3.receivables_trx_id = -1
1265 )
1266 RETURNING ps.payment_schedule_id
1267 BULK COLLECT INTO l_ar_ps_key_value_list;
1268
1269 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1270
1271 /*---------------------------------+
1272 | Calling central MRC library |
1273 | for MRC Integration |
1274 +---------------------------------*/
1275
1276 ar_mrc_engine.maintain_mrc_data(
1277 p_event_mode => 'UPDATE',
1278 p_table_name => 'AR_PAYMENT_SCHEDULES',
1279 p_mode => 'BATCH',
1280 p_key_value_list => l_ar_ps_key_value_list);
1281
1282 EXCEPTION
1283 WHEN OTHERS THEN
1284 debug( 'EXCEPTION: Error executing update stmt',
1285 MSG_LEVEL_BASIC );
1286 RAISE;
1287 END;
1288
1289 --
1290 --
1291 /* VAT changes: delete acct entry */
1292 /* bug 2808262. Changed the code to have a cursor */
1293
1294 DECLARE
1295 CURSOR c1 IS SELECT adj.adjustment_id
1296 FROM ar_adjustments adj
1297 WHERE adj.customer_trx_id = p_customer_trx_id
1298 AND adj.receivables_trx_id = -1;
1299 BEGIN
1300 FOR i IN c1 LOOP
1301 delete_adjustments( p_customer_trx_id, p_subsequent_trx_id );
1302
1303 END LOOP;
1304 END;
1305 print_fcn_label( 'arp_maintain_ps.reverse_adjustments()-' );
1306
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 debug( 'EXCEPTION: arp_maintain_ps.reverse_adjustments()',
1310 MSG_LEVEL_BASIC );
1311 RAISE;
1312
1313 END reverse_adjustments;
1314
1315
1316 ----------------------------------------------------------------------------
1317 PROCEDURE reverse_cm_effect(
1318 p_system_info IN arp_trx_global.system_info_rec_type,
1319 p_profile_info IN arp_trx_global.profile_rec_type,
1320 p_customer_trx_id IN BINARY_INTEGER
1321 ) IS
1322
1323 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1324
1325 BEGIN
1326
1327 print_fcn_label( 'arp_maintain_ps.reverse_cm_effect()+' );
1328
1329 BEGIN
1330
1331 UPDATE ar_payment_schedules ps
1332 SET (
1333 status,
1334 gl_date_closed,
1335 actual_date_closed,
1336 amount_credited,
1337 amount_due_remaining,
1338 acctd_amount_due_remaining,
1339 amount_line_items_remaining,
1340 tax_remaining,
1341 freight_remaining,
1342 receivables_charges_remaining,
1343 last_updated_by,
1344 last_update_date,
1345 last_update_login) = (
1346 SELECT
1347 decode(ps2.amount_due_remaining + ra.amount_applied,0,'CL','OP'),
1348 decode(ps2.amount_due_remaining + ra.amount_applied,
1349 0,
1350 greatest(max(decode(ra2.customer_trx_id,
1351 p_customer_trx_id, ps2.gl_date,
1352 ra2.gl_date)),
1353 max(decode(adj2.status,
1354 'A', adj2.gl_date,
1355 ps2.gl_date))),
1356 to_date('31-12-4712','DD-MM-YYYY')),--Added default date 31-12-4712 as per Bug:5514315
1357 decode(ps2.amount_due_remaining + ra.amount_applied,
1358 0,
1359 greatest(max(decode(ra2.customer_trx_id,
1360 p_customer_trx_id, ps2.trx_date,
1361 ra2.apply_date)),
1362 max(decode(adj2.status,
1363 'A', adj2.apply_date,
1364 ps2.trx_date))),
1365 to_date('31-12-4712','DD-MM-YYYY')),--Added default date 31-12-4712 as per Bug:5514315
1366 nvl(ps2.amount_credited, 0) + ra.amount_applied,
1367 ps2.amount_due_remaining + ra.amount_applied,
1368 ps2.acctd_amount_due_remaining + nvl(ra.acctd_amount_applied_to, 0),
1369 nvl(ps2.amount_line_items_remaining, 0) + nvl(ra.line_applied, 0),
1370 nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied,0),
1371 nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0),
1372 nvl(ps2.receivables_charges_remaining, 0) +
1373 nvl(ra.receivables_charges_applied, 0),
1374 p_profile_info.user_id,
1375 trunc(sysdate),
1376 p_profile_info.conc_login_id
1377 FROM
1378 ar_receivable_applications ra,
1379 ar_payment_schedules ps2,
1380 ar_adjustments adj2,
1381 ar_receivable_applications ra2
1382 WHERE ra.customer_trx_id = p_customer_trx_id
1383 and ra.status||'' = 'APP'
1384 and ra.applied_payment_schedule_id = ps2.payment_schedule_id
1385 and ps2.payment_schedule_id =ps.payment_schedule_id
1386 and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
1387 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
1388 and nvl(ra2.confirmed_flag,'Y')= 'Y'
1389 GROUP BY
1390 ps2.payment_schedule_id,
1391 ra2.applied_payment_schedule_id,
1392 adj2.payment_schedule_id,
1393 ps2.amount_due_remaining,
1394 ra.amount_applied,
1395 ps2.gl_date,
1396 ps2.trx_date,
1397 ps2.amount_credited,
1398 ps2.acctd_amount_due_remaining,
1399 ra.acctd_amount_applied_to,
1400 ps2.amount_line_items_remaining,
1401 ra.line_applied,
1402 ps2.tax_remaining,
1403 ra.tax_applied,
1404 ps2.freight_remaining,
1405 ra.freight_applied,
1406 ps2.receivables_charges_remaining,
1407 ra.receivables_charges_applied)
1408 WHERE ps.payment_schedule_id in
1409 (
1410 SELECT ra3.applied_payment_schedule_id
1411 FROM ar_receivable_applications ra3
1412 WHERE ra3.customer_trx_id = p_customer_trx_id
1413 and ra3.status='APP'
1414 )
1415 RETURNING ps.payment_schedule_id
1416 BULK COLLECT INTO l_ar_ps_key_value_list;
1417
1418 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1419
1420 /*---------------------------------+
1421 | Calling central MRC library |
1422 | for MRC Integration |
1423 +---------------------------------*/
1424
1425 ar_mrc_engine.maintain_mrc_data(
1426 p_event_mode => 'UPDATE',
1427 p_table_name => 'AR_PAYMENT_SCHEDULES',
1428 p_mode => 'BATCH',
1429 p_key_value_list => l_ar_ps_key_value_list);
1430
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 debug( 'EXCEPTION: Error executing update stmt',
1434 MSG_LEVEL_BASIC );
1435 RAISE;
1436 END;
1437
1438
1439
1440 print_fcn_label( 'arp_maintain_ps.reverse_cm_effect()-' );
1441
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 debug( 'EXCEPTION: arp_maintain_ps.reverse_cm_effect()',
1445 MSG_LEVEL_BASIC );
1446 RAISE;
1447
1448 END reverse_cm_effect;
1449
1450
1451 ----------------------------------------------------------------------------
1452 PROCEDURE update_payment_schedule(
1453 p_system_info IN arp_trx_global.system_info_rec_type,
1454 p_profile_info IN arp_trx_global.profile_rec_type,
1455 p_control IN control_rec_type
1456 ) IS
1457
1458 --BUG#5324129
1459 CURSOR del_app(p_app_id IN NUMBER) IS
1460 select app.receivable_application_id app_id,
1461 app.customer_trx_id trx_id
1462 from ar_receivable_applications app
1463 where app.applied_payment_schedule_id = p_control.payment_schedule_id --inv ps
1464 and app.customer_trx_id = p_control.customer_trx_id --cm trx id
1465 and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
1466 and app.reversal_gl_date IS NULL
1467 and app.receivable_application_id = p_app_id
1468 and exists (select 'x'
1469 from ar_distributions ard
1470 where ard.source_table = 'RA'
1471 and ard.source_id = app.receivable_application_id); --delete only necessary records
1472
1473 --BUG#5324129
1474 CURSOR cre_app(p_app_id IN NUMBER) IS
1475 select app.receivable_application_id app_id,
1476 app.customer_trx_id trx_id
1477 from ar_receivable_applications app
1478 where app.applied_payment_schedule_id = p_control.payment_schedule_id
1479 and app.customer_trx_id = p_control.customer_trx_id
1480 and nvl(app.confirmed_flag,'Y') = 'Y'
1481 and app.reversal_gl_date IS NULL
1482 and app.receivable_application_id = p_app_id
1483 and not exists (select 'x'
1484 from ar_distributions ard
1485 where ard.source_table = 'RA'
1486 and ard.source_id = app.receivable_application_id);
1487
1488 --BUG#5324129
1489 CURSOR cu_posted IS
1490 select *
1491 from ar_receivable_applications
1492 where applied_payment_schedule_id = p_control.payment_schedule_id
1493 and customer_trx_id = p_control.customer_trx_id
1494 and nvl(confirmed_flag,'Y') = 'Y'
1495 and reversal_gl_date IS NULL;
1496
1497
1498 CURSOR get_app_id(p_app_id IN NUMBER) IS
1499 select app.receivable_application_id,
1500 app.amount_applied
1501 from ar_receivable_applications app
1502 where app.applied_payment_schedule_id = p_control.payment_schedule_id
1503 and app.customer_trx_id = p_control.customer_trx_id
1504 and app.receivable_application_id = p_app_id;
1505
1506
1507 l_ae_doc_rec ae_doc_rec_type;
1508
1509 l_cm_adr NUMBER;
1510 l_cm_acctd_adr NUMBER;
1511 l_cm_rate NUMBER;
1512 l_new_amount_applied NUMBER;
1513 l_new_acctd_amt_applied_from NUMBER;
1514 l_new_acctd_amt_applied_to NUMBER;
1515 l_inv_adr NUMBER;
1516 l_inv_acctd_adr NUMBER;
1517 l_new_inv_acctd_adr NUMBER;
1518 l_inv_rate NUMBER;
1519
1520 l_dummy NUMBER;
1521 l_foreign_transaction VARCHAR2(1) := NO;
1522
1523 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1524
1525 --BUG#5324129
1526 l_app_id NUMBER;
1527 l_amount_applied NUMBER;
1528 l_ra_id NUMBER;
1529 l_del_app_rec del_app%ROWTYPE;
1530 l_cre_app_rec cre_app%ROWTYPE;
1531 old_rec_app ar_receivable_Applications%ROWTYPE;
1532 ins_ra_rec ar_receivable_Applications%ROWTYPE;
1533 no_app_found EXCEPTION;
1534
1535 BEGIN
1536
1537 print_fcn_label( 'arp_maintain_ps.update_payment_schedule()+' );
1538
1539 --BUG#5324129
1540 OPEN cu_posted;
1541 FETCH cu_posted INTO old_rec_app;
1542 IF cu_posted%NOTFOUND THEN
1543 RAISE no_app_found;
1544 END IF;
1545 CLOSE cu_posted;
1546
1547 arp_standard.debug(' old_rec_app.receivable_application_id:'||old_rec_app.receivable_application_id);
1548 arp_standard.debug(' old_rec_app.posting_control_id:'||old_rec_app.posting_control_id);
1549
1550
1551 BEGIN
1552
1553 SELECT
1554 ps_cm.amount_due_remaining - ra.amount_applied,
1555 ps_cm.acctd_amount_due_remaining - ra.acctd_amount_applied_from,
1556 ps_cm.exchange_rate,
1557 -( p_control.line_amount +
1558 p_control.tax_amount +
1559 p_control.freight_amount +
1560 p_control.charge_amount ),
1561 ps_inv.amount_due_remaining + ra.amount_applied,
1562 ps_inv.acctd_amount_due_remaining + ra.acctd_amount_applied_to,
1563 ps_inv.exchange_rate
1564 INTO
1565 l_cm_adr,
1566 l_cm_acctd_adr,
1567 l_cm_rate,
1568 l_new_amount_applied,
1569 l_inv_adr,
1570 l_inv_acctd_adr,
1571 l_inv_rate
1572 FROM
1573 ar_payment_schedules ps_cm,
1574 ar_payment_schedules ps_inv,
1575 ar_receivable_applications ra
1576 WHERE p_system_info.base_currency <> ps_inv.invoice_currency_code
1577 and ra.applied_payment_schedule_id = ps_inv.payment_schedule_id
1578 and ps_inv.payment_schedule_id = p_control.payment_schedule_id
1579 and ra.payment_schedule_id = ps_cm.payment_schedule_id
1580 and ps_cm.customer_trx_id = p_control.customer_trx_id
1581 and ra.reversal_gl_date IS NULL
1582 and ra.receivable_application_id = old_rec_app.receivable_application_id;
1583
1584 l_foreign_transaction := YES;
1585
1586
1587 EXCEPTION
1588 WHEN NO_DATA_FOUND THEN
1589 l_foreign_transaction := NO;
1590
1591 WHEN OTHERS THEN
1592 debug( 'EXCEPTION: Error executing select stmt',
1593 MSG_LEVEL_BASIC );
1594 RAISE;
1595 END;
1596
1597 IF( l_foreign_transaction = YES ) THEN
1598
1599 ------------------------------------------------------------------
1600 -- Compute accounted amounts for ps and applications
1601 ------------------------------------------------------------------
1602 arp_util.calc_acctd_amount(
1603 p_system_info.base_currency,
1604 NULL, -- precision
1605 NULL, -- mau
1606 l_inv_rate,
1607 '-', -- type
1608 l_inv_adr, -- master_from
1609 l_inv_acctd_adr, -- acctd_master_from
1610 l_new_amount_applied, -- detail
1611 l_dummy, -- master_to
1612 l_new_inv_acctd_adr, -- acctd_master_to
1613 l_new_acctd_amt_applied_to -- acctd_detail
1614 );
1615
1616 arp_util.calc_acctd_amount(
1617 p_system_info.base_currency,
1618 NULL, -- precision
1619 NULL, -- mau
1620 l_cm_rate,
1621 '+', -- type
1622 l_cm_adr, -- master_from
1623 l_cm_acctd_adr, -- acctd_master_from
1624 l_new_amount_applied, -- detail
1625 l_dummy, -- master_to
1626 l_dummy, -- acctd_master_to
1627 l_new_acctd_amt_applied_from -- acctd_detail
1628 );
1629
1630 END IF;
1631
1632 BEGIN
1633 ----------------------------------------------------------------
1634 -- Reverse the origial CM application amounts and use the new
1635 -- amounts passed in for the invoice's payment schedule.
1636 ----------------------------------------------------------------
1637 UPDATE ar_payment_schedules ps
1638 SET (
1639 ps.status,
1640 ps.gl_date_closed,
1641 ps.actual_date_closed,
1642 ps.amount_credited,
1643 ps.amount_due_remaining,
1644 ps.acctd_amount_due_remaining,
1645 ps.amount_line_items_remaining,
1646 ps.tax_remaining,
1647 ps.freight_remaining,
1648 ps.receivables_charges_remaining,
1649 ps.last_updated_by,
1650 ps.last_update_date,
1651 ps.last_update_login) = (
1652 SELECT
1653 decode( ps2.amount_due_remaining + ra.amount_applied +
1654 (p_control.line_amount +
1655 p_control.tax_amount +
1656 p_control.freight_amount +
1657 p_control.charge_amount ),
1658 0, 'CL', 'OP'),
1659 decode( ps2.amount_due_remaining + ra.amount_applied +
1660 (p_control.line_amount +
1661 p_control.tax_amount +
1662 p_control.freight_amount +
1663 p_control.charge_amount ),
1664 0,
1665 greatest(max(ra2.gl_date), max(decode(adj2.status,
1666 'A', adj2.gl_date,
1667 ps2.gl_date))),
1668 ''),
1669 decode(ps2.amount_due_remaining + ra.amount_applied +
1670 (p_control.line_amount +
1671 p_control.tax_amount +
1672 p_control.freight_amount +
1673 p_control.charge_amount ),
1674 0, greatest(max(ra2.apply_date),
1675 max(decode(adj2.status,
1676 'A', adj2.apply_date,
1677 ps2.trx_date))),
1678 ''),
1679 nvl(ps2.amount_credited, 0) + ra.amount_applied +
1680 (p_control.line_amount +
1681 p_control.tax_amount +
1682 p_control.freight_amount +
1683 p_control.charge_amount ),
1684 ps2.amount_due_remaining + ra.amount_applied +
1685 (p_control.line_amount +
1686 p_control.tax_amount +
1687 p_control.freight_amount +
1688 p_control.charge_amount ),
1689 decode(l_foreign_transaction,
1690 'N',
1691 ps2.amount_due_remaining + ra.amount_applied +
1692 (p_control.line_amount +
1693 p_control.tax_amount +
1694 p_control.freight_amount +
1695 p_control.charge_amount ),
1696 'Y', to_number(nvl(l_new_inv_acctd_adr, 0))),
1697 nvl(ps2.amount_line_items_remaining, 0) + nvl(ra.line_applied, 0) +
1698 p_control.line_amount,
1699 nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied, 0) +
1700 p_control.tax_amount,
1701 nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0) +
1702 p_control.freight_amount,
1703 nvl(ps2.receivables_charges_remaining, 0) +
1704 nvl(ra.receivables_charges_applied, 0) +
1705 p_control.charge_amount,
1706 p_profile_info.user_id,
1707 trunc(sysdate),
1708 p_profile_info.conc_login_id
1709 FROM
1710 ar_receivable_applications ra,
1711 ar_payment_schedules ps2,
1712 ar_receivable_applications ra2,
1713 ar_adjustments adj2
1714 WHERE ra.customer_trx_id = p_control.customer_trx_id
1715 and ra.status||'' = 'APP'
1716 and ra.reversal_gl_date IS NULL
1717 and ra.applied_payment_schedule_id = ps2.payment_schedule_id
1718 and ps.payment_schedule_id = ps2.payment_schedule_id
1719 and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
1720 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
1721 and ra2.reversal_gl_date IS NULL
1722 and nvl(ra2.confirmed_flag, 'Y') = 'Y'
1723 and ra.receivable_application_id = old_rec_app.receivable_application_id
1724 GROUP BY
1725 ps2.payment_schedule_id,
1726 ra2.applied_payment_schedule_id,
1727 adj2.payment_schedule_id,
1728 ps2.amount_due_remaining,
1729 ra.amount_applied,
1730 ps2.gl_date,
1731 ps2.trx_date,
1732 ps2.amount_credited,
1733 ps2.acctd_amount_due_remaining,
1734 ra.acctd_amount_applied_to,
1735 ps2.amount_line_items_remaining,
1736 ra.line_applied,
1737 ps2.tax_remaining,
1738 ra.tax_applied,
1739 ps2.freight_remaining,
1740 ra.freight_applied,
1741 ps2.receivables_charges_remaining,
1742 ra.receivables_charges_applied,
1743 ps2.exchange_rate)
1744 WHERE ps.payment_schedule_id in
1745 (
1746 SELECT ra3.applied_payment_schedule_id
1747 FROM ar_receivable_applications ra3
1748 WHERE ra3.customer_trx_id = p_control.customer_trx_id
1749 and ra3.status = 'APP'
1750 and ra3.applied_payment_schedule_id = p_control.payment_schedule_id
1751 and ra3.reversal_gl_date IS NULL
1752 )
1753 RETURNING payment_schedule_id
1754 BULK COLLECT INTO l_ar_ps_key_value_list;
1755
1756 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1757
1758 /*---------------------------------+
1759 | Calling central MRC library |
1760 | for MRC Integration |
1761 +---------------------------------*/
1762
1763 ar_mrc_engine.maintain_mrc_data(
1764 p_event_mode => 'UPDATE',
1765 p_table_name => 'AR_PAYMENT_SCHEDULES',
1766 p_mode => 'BATCH',
1767 p_key_value_list => l_ar_ps_key_value_list);
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 debug( 'EXCEPTION: Error executing update stmt #1',
1772 MSG_LEVEL_BASIC );
1773 RAISE;
1774 END;
1775
1776
1777 BEGIN
1778 ----------------------------------------------------------------
1779 -- Release 11.5, plug in changes delete the accounting for the
1780 -- updated CM and then recreate the same. Direct updates are not
1781 -- done for child accounting records.
1782 ----------------------------------------------------------------
1783 IF old_rec_app.posting_control_id = -3 THEN
1784
1785
1786 arp_standard.debug('Path Update CM RA and recreate distributions');
1787 arp_standard.debug('1 Delete current RA distributions');
1788
1789 OPEN del_app(old_rec_app.receivable_application_id);
1790 LOOP
1791 FETCH del_app INTO l_del_app_rec;
1792 EXIT WHEN del_app%NOTFOUND;
1793 --
1794 --Release 11.5 VAT changes, delete accounting for Applications
1795 --
1796 arp_standard.debug(' Current distributions exist delete distributions +');
1797 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
1798 l_ae_doc_rec.document_id := l_del_app_rec.trx_id;
1799 l_ae_doc_rec.accounting_entity_level := 'ONE';
1800 l_ae_doc_rec.source_table := 'RA';
1801 l_ae_doc_rec.source_id := l_del_app_rec.app_id;
1802 l_ae_doc_rec.source_id_old := '';
1803 l_ae_doc_rec.other_flag := '';
1804 --Bug 1329091 - PS is updated before Accounting Engine Call
1805
1806 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1807
1808 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1809 arp_standard.debug(' delete distributions -');
1810
1811 END LOOP;
1812 CLOSE del_app;
1813
1814
1815 ----------------------------------------------------------------
1816 -- Update the CM application record to the correct amount
1817 ----------------------------------------------------------------
1818 arp_standard.debug('Update the CM app ra_id '|| old_rec_app.receivable_application_id || '+');
1819
1820 UPDATE ar_receivable_applications ra
1821 SET
1822 acctd_amount_applied_from =
1823 decode(l_foreign_transaction,
1824 'N',
1825 -( p_control.line_amount +
1826 p_control.tax_amount +
1827 p_control.freight_amount +
1828 p_control.charge_amount ),
1829 'Y', to_number( nvl(l_new_acctd_amt_applied_from, 0) ) ),
1830 acctd_amount_applied_to =
1831 decode(l_foreign_transaction,
1832 'N',
1833 -(p_control.line_amount +
1834 p_control.tax_amount +
1835 p_control.freight_amount +
1836 p_control.charge_amount),
1837 'Y', to_number(nvl(l_new_acctd_amt_applied_to, 0))),
1838 amount_applied =
1839 -(p_control.line_amount +
1840 p_control.tax_amount +
1841 p_control.freight_amount +
1842 p_control.charge_amount),
1843 line_applied = -p_control.line_amount,
1844 tax_applied = -p_control.tax_amount,
1845 freight_applied = -p_control.freight_amount,
1846 receivables_charges_applied = -p_control.charge_amount,
1847 last_updated_by = p_profile_info.user_id,
1848 last_update_date = trunc(sysdate),
1849 last_update_login = p_profile_info.conc_login_id
1850 WHERE ra.applied_payment_schedule_id = p_control.payment_schedule_id
1851 and ra.customer_trx_id = p_control.customer_trx_id
1852 and ra.reversal_gl_date IS NULL
1853 and ra.receivable_application_id = old_rec_app.receivable_application_id;
1854
1855 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1856
1857
1858 arp_standard.debug('MRC plugins ');
1859 OPEN get_app_id(old_rec_app.receivable_application_id);
1860 LOOP
1861 FETCH get_app_id INTO l_app_id, l_amount_applied;
1862 EXIT WHEN get_app_id%NOTFOUND;
1863 -- Call mrc engine to process update:
1864 ar_mrc_engine3.update_cm_application(
1865 l_app_id,
1866 p_control.payment_schedule_id, /* p_app_ps_id */
1867 p_control.customer_trx_id, /* p_ct_id */
1868 l_amount_applied);
1869 END LOOP;
1870 CLOSE get_app_id;
1871
1872 arp_standard.debug('End update the CM app ra_id');
1873 ----------------------------------------------------------------
1874 -- Release 11.5, plug in changes recreate the accounting for the
1875 -- updated CM once parent records have been updated.Only one APP
1876 -- for the credit memo should get updated in previous statement.
1877 ----------------------------------------------------------------
1878 arp_standard.debug('Creation of new distributions');
1879 OPEN cre_app(old_rec_app.receivable_application_id);
1880 LOOP
1881 FETCH cre_app INTO l_cre_app_rec;
1882 EXIT WHEN cre_app%NOTFOUND;
1883 --
1884 --Release 11.5 VAT changes, recreate accounting for Applications
1885 --
1886 arp_standard.debug(' recreate distributions +:'||l_cre_app_rec.app_id);
1887 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
1888 l_ae_doc_rec.document_id := l_cre_app_rec.trx_id;
1889 l_ae_doc_rec.accounting_entity_level := 'ONE';
1890 l_ae_doc_rec.source_table := 'RA';
1891 l_ae_doc_rec.source_id := l_cre_app_rec.app_id;
1892 l_ae_doc_rec.source_id_old := '';
1893 l_ae_doc_rec.other_flag := '';
1894
1895 --Bug 1329091 - PS is updated before Accounting Engine Call
1896
1897 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1898 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1899
1900 /*bug-6976549*/
1901 arp_balance_check.CHECK_APPLN_BALANCE(l_cre_app_rec.app_id,
1902 NULL,
1903 'N');
1904
1905 arp_standard.debug(' recreate distributions -');
1906 END LOOP;
1907 CLOSE cre_app;
1908 arp_standard.debug('End Path Update CM RA and recreate distributions');
1909
1910 ELSE
1911
1912 arp_standard.debug('Path Reverse CM RA and create new CM RA');
1913 --BUG#5324129
1914 -- Insert RA to reverse the posted one
1915 ins_ra_rec := old_rec_app;
1916 ins_ra_rec.acctd_amount_applied_from := -1 * old_rec_app.acctd_amount_applied_from;
1917 ins_ra_rec.amount_applied := -1 * old_rec_app.amount_applied;
1918 ins_ra_rec.amount_applied_from := -1 * old_rec_app.amount_applied_from;
1919 ins_ra_rec.trans_to_receipt_rate := old_rec_app.trans_to_receipt_rate;
1920 ins_ra_rec.application_type := old_rec_app.application_type;
1921 ins_ra_rec.apply_date := TRUNC(SYSDATE);
1922 ins_ra_rec.code_combination_id := old_rec_app.code_combination_id;
1923 ins_ra_rec.display := 'N';
1924 ins_ra_rec.gl_date := TRUNC(SYSDATE);
1925 ins_ra_rec.payment_schedule_id := old_rec_app.payment_schedule_id;
1926 ins_ra_rec.set_of_books_id := old_rec_app.set_of_books_id;
1927 ins_ra_rec.status := old_rec_app.status;
1928 ins_ra_rec.acctd_amount_applied_to := -1 * old_rec_app.acctd_amount_applied_to;
1929 ins_ra_rec.acctd_earned_discount_taken := -1 * old_rec_app.acctd_earned_discount_taken;
1930 ins_ra_rec.acctd_unearned_discount_taken := -1 * old_rec_app.acctd_unearned_discount_taken;
1931 ins_ra_rec.applied_customer_trx_id := old_rec_app.applied_customer_trx_id;
1932 ins_ra_rec.applied_customer_trx_line_id := old_rec_app.applied_customer_trx_line_id;
1933 ins_ra_rec.applied_payment_schedule_id := old_rec_app.applied_payment_schedule_id;
1934 ins_ra_rec.cash_receipt_id := old_rec_app.cash_receipt_id;
1935 ins_ra_rec.comments := old_rec_app.comments;
1936 ins_ra_rec.confirmed_flag := old_rec_app.confirmed_flag;
1937 ins_ra_rec.customer_trx_id := old_rec_app.customer_trx_id;
1938 ins_ra_rec.days_late := old_rec_app.days_late;
1939 ins_ra_rec.earned_discount_taken := -1 * old_rec_app.earned_discount_taken;
1940 ins_ra_rec.freight_applied := -1 * old_rec_app.freight_applied;
1941 ins_ra_rec.gl_posted_date := NULL;
1942 ins_ra_rec.line_applied := -1 * old_rec_app.line_applied;
1943 ins_ra_rec.on_account_customer := old_rec_app.on_account_customer;
1944 ins_ra_rec.postable := old_rec_app.postable;
1945 ins_ra_rec.posting_control_id := -3;
1946 ins_ra_rec.program_application_id := NULL;
1947 ins_ra_rec.program_id := NULL;
1948 ins_ra_rec.program_update_date := NULL;
1949 ins_ra_rec.receivables_charges_applied := -1 * old_rec_app.receivables_charges_applied;
1950 ins_ra_rec.receivables_trx_id := old_rec_app.receivables_trx_id;
1951 ins_ra_rec.request_id := NULL;
1952 ins_ra_rec.tax_applied := -1 * old_rec_app.tax_applied;
1953 ins_ra_rec.unearned_discount_taken := -1 * old_rec_app.unearned_discount_taken;
1954 ins_ra_rec.unearned_discount_ccid := old_rec_app.unearned_discount_ccid;
1955 ins_ra_rec.earned_discount_ccid := old_rec_app.earned_discount_ccid;
1956 ins_ra_rec.ussgl_transaction_code := old_rec_app.ussgl_transaction_code;
1957 ins_ra_rec.ussgl_transaction_code_context := old_rec_app.ussgl_transaction_code_context;
1958 ins_ra_rec.reversal_gl_date := TRUNC(SYSDATE);
1959 ins_ra_rec.LINE_EDISCOUNTED := -1 * old_rec_app.LINE_EDISCOUNTED;
1960 ins_ra_rec.LINE_UEDISCOUNTED := -1 * old_rec_app.LINE_UEDISCOUNTED;
1961 ins_ra_rec.TAX_EDISCOUNTED := -1 * old_rec_app.TAX_EDISCOUNTED;
1962 ins_ra_rec.TAX_UEDISCOUNTED := -1 * old_rec_app.TAX_UEDISCOUNTED;
1963 ins_ra_rec.FREIGHT_EDISCOUNTED := -1 * old_rec_app.FREIGHT_EDISCOUNTED;
1964 ins_ra_rec.FREIGHT_UEDISCOUNTED := -1 * old_rec_app.FREIGHT_UEDISCOUNTED;
1965 ins_ra_rec.CHARGES_EDISCOUNTED := -1 * old_rec_app.CHARGES_EDISCOUNTED;
1966 ins_ra_rec.CHARGES_UEDISCOUNTED := -1 * old_rec_app.CHARGES_UEDISCOUNTED;
1967 ins_ra_rec.APPLICATION_REF_TYPE := old_rec_app.APPLICATION_REF_TYPE;
1968 ins_ra_rec.application_ref_id := old_rec_app.application_ref_id;
1969 ins_ra_rec.application_ref_num := old_rec_app.application_ref_num;
1970 ins_ra_rec.application_ref_reason := old_rec_app.application_ref_reason;
1971 ins_ra_rec.customer_reference := old_rec_app.customer_reference;
1972 ins_ra_rec.link_to_customer_trx_id := old_rec_app.link_to_customer_trx_id;
1973 ins_ra_rec.customer_reason := old_rec_app.customer_reason;
1974 ins_ra_rec.applied_rec_app_id := old_rec_app.applied_rec_app_id;
1975 ins_ra_rec.application_rule := 'CREDIT MEMO REVERSAL';
1976 ins_ra_rec.receivable_application_id := NULL;
1977
1978 arp_app_pkg.insert_p( ins_ra_rec, l_ra_id );
1979 arp_standard.debug('Reverse application inserted ra_id :'||l_ra_id);
1980
1981 --Update the reversal app record reversal_gl_date
1982 UPDATE ar_receivable_applications
1983 SET reversal_gl_date = TRUNC(SYSDATE),
1984 display = 'N'
1985 WHERE receivable_application_id = old_rec_app.receivable_application_id;
1986
1987 arp_standard.debug('The old ra record '|| old_rec_app.receivable_application_id ||' reversal_gl_date updated ');
1988
1989 -- MRC cm app record inserted
1990 -- need to call mrc engine to process rec apps row
1991 arp_standard.debug('Plugin MRC call for ra reversal ');
1992 ar_mrc_engine3.reversal_insert_oppos_ra_recs(
1993 ins_ra_rec,
1994 old_rec_app.receivable_application_id,
1995 l_ra_id);
1996 -----------------
1997 -- Create reversal distributions
1998 -----------------
1999 arp_standard.debug('create the distribution for reversal app :'||l_ra_id);
2000 l_ae_doc_rec.source_table := 'RA';
2001 l_ae_doc_rec.accounting_entity_level := 'ONE';
2002 l_ae_doc_rec.other_flag := 'REVERSE';
2003 l_ae_doc_rec.source_id_old := old_rec_app.receivable_application_id;
2004 l_ae_doc_rec.source_id := l_ra_id;
2005 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2006
2007 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2008 /*bug-6976549*/
2009 arp_balance_check.CHECK_APPLN_BALANCE(l_ra_id,
2010 NULL,
2011 'N');
2012
2013
2014 ----------------------------------------------------------------
2015 -- create the new CM application record to the correct amount
2016 ----------------------------------------------------------------
2017 arp_standard.debug('create the new CM APP record ');
2018
2019 ins_ra_rec := old_rec_app;
2020 IF l_foreign_transaction = 'N' THEN
2021 ins_ra_rec.acctd_amount_applied_from := -( p_control.line_amount +
2022 p_control.tax_amount +
2023 p_control.freight_amount +
2024 p_control.charge_amount );
2025 ELSE
2026 ins_ra_rec.acctd_amount_applied_from := to_number( nvl(l_new_acctd_amt_applied_from, 0));
2027 END IF;
2028
2029 IF l_foreign_transaction = 'N' THEN
2030 ins_ra_rec.acctd_amount_applied_to := -(p_control.line_amount +
2031 p_control.tax_amount +
2032 p_control.freight_amount +
2033 p_control.charge_amount);
2034 ELSE
2035 ins_ra_rec.acctd_amount_applied_to := to_number(nvl(l_new_acctd_amt_applied_to, 0));
2036 END IF;
2037
2038 ins_ra_rec.amount_applied := -(p_control.line_amount +
2039 p_control.tax_amount +
2040 p_control.freight_amount +
2041 p_control.charge_amount);
2042 ins_ra_rec.line_applied := -p_control.line_amount;
2043 ins_ra_rec.tax_applied := -p_control.tax_amount;
2044 ins_ra_rec.freight_applied := -p_control.freight_amount;
2045 ins_ra_rec.receivables_charges_applied := -p_control.charge_amount;
2046 ins_ra_rec.posting_control_id := -3;
2047 ins_ra_rec.gl_posted_date := NULL;
2048 ins_ra_rec.reversal_gl_date := NULL;
2049 ins_ra_rec.gl_date := TRUNC(SYSDATE);
2050 ins_ra_rec.display := 'Y';
2051 ins_ra_rec.receivable_application_id := NULL;
2052
2053 arp_app_pkg.insert_p( ins_ra_rec, l_ra_id );
2054 arp_standard.debug('CM APP record created :'||l_ra_id);
2055
2056
2057 arp_standard.debug('MRC plugin call for the app record '||l_ra_id);
2058 ar_mrc_engine3.cm_application(
2059 p_cm_ps_id => old_rec_app.payment_schedule_id,
2060 p_invoice_ps_id => old_rec_app.applied_payment_schedule_id,
2061 p_inv_ra_rec => ins_ra_rec,
2062 p_ra_id => l_ra_id);
2063
2064 arp_standard.debug('Create the distributions for '||l_ra_id);
2065 -- Create the distributions
2066 l_ae_doc_rec.document_id := ins_ra_rec.customer_trx_id;
2067 l_ae_doc_rec.accounting_entity_level := 'ONE';
2068 l_ae_doc_rec.source_table := 'RA';
2069 l_ae_doc_rec.source_id := l_ra_id;
2070 l_ae_doc_rec.source_id_old := '';
2071 l_ae_doc_rec.other_flag := '';
2072 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2073
2074 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2075 /*bug-6976549*/
2076 arp_balance_check.CHECK_APPLN_BALANCE(l_ra_id,
2077 NULL,
2078 'N');
2079
2080 END IF;
2081
2082 EXCEPTION
2083 WHEN no_app_found THEN
2084 arp_standard.debug('No app found');
2085 WHEN OTHERS THEN
2086 debug( 'EXCEPTION: Error executing update stmt #2',
2087 MSG_LEVEL_BASIC );
2088 RAISE;
2089 END;
2090
2091 print_fcn_label( 'arp_maintain_ps.update_payment_schedule()-' );
2092
2093 EXCEPTION
2094 WHEN OTHERS THEN
2095 debug( 'EXCEPTION: arp_maintain_ps.update_payment_schedule()',
2096 MSG_LEVEL_BASIC );
2097 RAISE;
2098
2099 END update_payment_schedule;
2100
2101
2102 ----------------------------------------------------------------------------
2103 PROCEDURE update_adjustments(
2104 p_system_info IN arp_trx_global.system_info_rec_type,
2105 p_profile_info IN arp_trx_global.profile_rec_type,
2106 p_control IN control_rec_type
2107 ) IS
2108
2109 CURSOR del_app IS
2110 select app.receivable_application_id app_id,
2111 app.customer_trx_id trx_id
2112 from ar_receivable_applications app
2113 where app.applied_payment_schedule_id = p_control.payment_schedule_id
2114 and app.customer_trx_id = p_control.customer_trx_id
2115 and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
2116 and app.status = 'APP'
2117 and exists (select 'x'
2118 from ar_distributions ard
2119 where ard.source_table = 'RA'
2120 and ard.source_id = app.receivable_application_id); --delete only necessary records
2121
2122 l_rec_del_app del_app%ROWTYPE;
2123
2124 l_cm_adr NUMBER;
2125 l_cm_acctd_adr NUMBER;
2126 l_cm_rate NUMBER;
2127 l_inv_adr NUMBER;
2128 l_inv_acctd_adr NUMBER;
2129 l_inv_rate NUMBER;
2130
2131 l_new_inv_adr NUMBER;
2132 l_new_inv_acctd_adr NUMBER;
2133 l_new2_inv_acctd_adr NUMBER;
2134
2135 l_new_adj_amount NUMBER;
2136 l_new_adj_acctd_amount NUMBER;
2137 l_new_amount_applied NUMBER;
2138 l_new_acctd_amt_applied_from NUMBER;
2139 l_new_acctd_amt_applied_to NUMBER;
2140
2141 l_update_inv_adr NUMBER;
2142 l_update_inv_acctd_adr NUMBER;
2143 l_update_new_adj_amount NUMBER;
2144
2145 l_dummy NUMBER;
2146 l_foreign_transaction VARCHAR2(1) := NO;
2147 l_no_adjustments BOOLEAN;
2148
2149 l_doc_where_clause VARCHAR2(1000);
2150 /* VAT changes */
2151 l_ae_doc_rec ae_doc_rec_type;
2152 l_adjustment_id ar_adjustments.adjustment_id%type;
2153 l_ccid ar_adjustments.code_combination_id%type;
2154
2155 BEGIN
2156
2157 print_fcn_label( 'arp_maintain_ps.update_adjustments()+' );
2158
2159 BEGIN
2160
2161 --
2162 -- Determine if adjustments exist on child invoice
2163 --
2164 SELECT adj.adjustment_id
2165 INTO l_dummy
2166 FROM ar_adjustments adj
2167 WHERE adj.receivables_trx_id = -1
2168 and adj.customer_trx_id = p_control.previous_customer_trx_id
2169 and adj.subsequent_trx_id = p_control.customer_trx_id
2170 and rownum = 1;
2171
2172 l_no_adjustments := FALSE;
2173
2174 EXCEPTION
2175 WHEN NO_DATA_FOUND THEN
2176 l_no_adjustments := TRUE;
2177 WHEN OTHERS THEN
2178 debug( 'EXCEPTION: Error executing select stmt #1',
2179 MSG_LEVEL_BASIC );
2180 RAISE;
2181 END;
2182
2183 IF( l_no_adjustments ) THEN
2184
2185 ------------------------------------------------------------
2186 -- If there is no adjustment on the child invoice,
2187 -- call update_payment_schedule():
2188 -- Update the invoice payment schedule with new amounts,
2189 -- Update the CM application record with new amounts
2190 -----------------------------------------------------------
2191 update_payment_schedule(
2192 p_system_info,
2193 p_profile_info,
2194 p_control );
2195 RETURN;
2196
2197 END IF;
2198
2199
2200 BEGIN
2201
2202 SELECT
2203 /* reverse old cm app */
2204 ps_cm.amount_due_remaining - ra.amount_applied,
2205 ps_cm.acctd_amount_due_remaining - ra.acctd_amount_applied_from,
2206 ps_cm.exchange_rate,
2207 -( p_control.line_amount +
2208 p_control.tax_amount +
2209 p_control.freight_amount +
2210 p_control.charge_amount ),
2211 /* reverse old cm app */
2212 ps_inv.amount_due_remaining + ra.amount_applied,
2213 ps_inv.acctd_amount_due_remaining + ra.acctd_amount_applied_to,
2214 ps_inv.exchange_rate,
2215 (-ra.line_applied - p_control.line_amount)
2216 INTO
2217 l_cm_adr,
2218 l_cm_acctd_adr,
2219 l_cm_rate,
2220 l_new_amount_applied,
2221 l_inv_adr,
2222 l_inv_acctd_adr,
2223 l_inv_rate,
2224 l_new_adj_amount
2225 FROM
2226 ar_payment_schedules ps_cm,
2227 ar_payment_schedules ps_inv,
2228 ar_receivable_applications ra
2229 WHERE p_system_info.base_currency <> ps_inv.invoice_currency_code
2230 and ra.applied_payment_schedule_id = ps_inv.payment_schedule_id
2231 and ps_inv.payment_schedule_id = p_control.payment_schedule_id
2232 and ra.payment_schedule_id = ps_cm.payment_schedule_id
2233 and ps_cm.customer_trx_id = p_control.customer_trx_id;
2234
2235 l_foreign_transaction := YES;
2236
2237
2238 EXCEPTION
2239 WHEN NO_DATA_FOUND THEN
2240 l_foreign_transaction := NO;
2241 WHEN OTHERS THEN
2242 debug( 'EXCEPTION: Error executing select stmt #2',
2243 MSG_LEVEL_BASIC );
2244 RAISE;
2245
2246 END;
2247
2248 IF( l_foreign_transaction = YES ) THEN
2249
2250 ------------------------------------------------------------------
2251 -- Compute accounted amounts for ps and applications
2252 ------------------------------------------------------------------
2253
2254 ------------------------------------------------------------------
2255 -- Get New Acctd Amt Applied To and New Acctd Amt Due Remaining
2256 ------------------------------------------------------------------
2257 arp_util.calc_acctd_amount(
2258 p_system_info.base_currency,
2259 NULL, -- precision
2260 NULL, -- mau
2261 l_inv_rate,
2262 '-', -- type
2263 l_inv_adr, -- master_from
2264 l_inv_acctd_adr, -- acctd_master_from
2265 l_new_amount_applied, -- detail
2266 l_new_inv_adr, -- master_to
2267 l_new_inv_acctd_adr, -- acctd_master_to
2268 l_new_acctd_amt_applied_to -- acctd_detail
2269 );
2270
2271 ---------------------------------------------------------------
2272 -- Get New Acctd Amt Applied From
2273 ---------------------------------------------------------------
2274 arp_util.calc_acctd_amount(
2275 p_system_info.base_currency,
2276 NULL, -- precision
2277 NULL, -- mau
2278 l_cm_rate,
2279 '+', -- type
2280 l_cm_adr, -- master_from
2281 l_cm_acctd_adr, -- acctd_master_from
2282 l_new_amount_applied, -- detail
2283 l_dummy, -- master_to
2284 l_dummy, -- acctd_master_to
2285 l_new_acctd_amt_applied_from -- acctd_detail
2286 );
2287
2288 END IF;
2289
2290 BEGIN
2291
2292 ------------------------------------------------------------
2293 -- If no commitment adj exists by this CM, insert one,
2294 -- Otherwise, update the existing record with the new amount
2295 ------------------------------------------------------------
2296
2297 SELECT
2298 /* reverse adj effect */
2299 to_number(nvl(l_new_inv_adr, 0)) - adj.amount,
2300 to_number(nvl(l_new_inv_acctd_adr, 0)) - adj.acctd_amount,
2301 adj.amount + to_number(nvl(l_new_adj_amount, 0))
2302 INTO
2303 l_update_inv_adr,
2304 l_update_inv_acctd_adr,
2305 l_update_new_adj_amount
2306 FROM ar_adjustments adj
2307 WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
2308 and adj.subsequent_trx_id = p_control.customer_trx_id
2309 and adj.receivables_trx_id = -1
2310 and adj.payment_schedule_id = p_control.payment_schedule_id;
2311
2312 l_no_adjustments := FALSE;
2313
2314 EXCEPTION
2315 WHEN NO_DATA_FOUND THEN
2316 l_no_adjustments := TRUE;
2317 WHEN OTHERS THEN
2318 debug( 'EXCEPTION: Error executing select stmt #3',
2319 MSG_LEVEL_BASIC );
2320 RAISE;
2321
2322 END;
2323
2324
2325 IF( l_no_adjustments ) THEN
2326
2327 ------------------------------------------------------------------
2328 -- Insert the adjustment
2329 ------------------------------------------------------------------
2330
2331 IF( l_foreign_transaction = YES ) THEN
2332
2333 ------------------------------------------------------------------
2334 -- Get acctd amt for the adj
2335 ------------------------------------------------------------------
2336 arp_util.calc_acctd_amount(
2337 p_system_info.base_currency,
2338 NULL, -- precision
2339 NULL, -- mau
2340 l_inv_rate,
2341 '+', -- type
2342 l_new_inv_adr, -- master_from
2343 l_new_inv_acctd_adr, -- acctd_master_from
2344 l_new_adj_amount, -- detail
2345 l_dummy, -- master_to
2346 l_new2_inv_acctd_adr, -- acctd_master_to
2347 l_new_adj_acctd_amount -- acctd_detail
2348 );
2349
2350 END IF;
2351
2352 -- do the insert
2353 BEGIN
2354
2355 select ar_adjustments_s.nextval into l_adjustment_id
2356 from dual;
2357
2358 INSERT INTO ar_adjustments
2359 (
2360 created_by,
2361 creation_date,
2362 last_updated_by,
2363 last_update_date,
2364 last_update_login,
2365 set_of_books_id,
2366 receivables_trx_id,
2367 automatically_generated,
2368 type,
2369 adjustment_type,
2370 status,
2371 apply_date,
2372 adjustment_id,
2373 amount,
2374 gl_date,
2375 code_combination_id,
2376 customer_trx_id,
2377 payment_schedule_id,
2378 subsequent_trx_id,
2379 postable,
2380 acctd_amount,
2381 adjustment_number,
2382 created_from,
2383 posting_control_id
2384 ,org_id
2385 )
2386 SELECT
2387 p_profile_info.user_id,
2388 trunc(sysdate),
2389 p_profile_info.user_id,
2390 trunc(sysdate),
2391 p_profile_info.conc_login_id,
2392 adj2.set_of_books_id,
2393 -1,
2394 'Y',
2395 'LINE',
2396 'C',
2397 'A',
2398 adj2.apply_date,
2399 l_adjustment_id,
2400 nvl(-ra.line_applied, 0) - p_control.line_amount,
2401 adj2.gl_date,
2402 adj2.code_combination_id,
2403 p_control.previous_customer_trx_id,
2404 p_control.payment_schedule_id,
2405 p_control.customer_trx_id,
2406 adj2.postable,
2407 decode(l_foreign_transaction,
2408 'N', nvl(-ra.line_applied, 0) - p_control.line_amount,
2409 'Y', to_number(nvl(l_new_adj_acctd_amount, 0))),
2410 to_char(ar_adjustment_number_s.nextval),
2411 'ARAPSI',
2412 -3
2413 ,arp_standard.sysparm.org_id /* SSA changes anuj */
2414 FROM
2415 ar_adjustments adj2,
2416 ar_receivable_applications ra,
2417 ra_customer_trx ct
2418 WHERE adj2.receivables_trx_id= -1
2419 and adj2.customer_trx_id = p_control.previous_customer_trx_id
2420 and adj2.subsequent_trx_id = p_control.customer_trx_id
2421 and ra.customer_trx_id = adj2.subsequent_trx_id
2422 and ra.applied_payment_schedule_id = p_control.payment_schedule_id
2423 and ct.customer_trx_id = ra.applied_customer_trx_id
2424 and adj2.payment_schedule_id =
2425 (
2426 /* find an adjustment against the invoice by the CM */
2427 SELECT max(payment_schedule_id)
2428 FROM ar_adjustments adj3
2429 WHERE adj3.receivables_trx_id=-1
2430 and adj3.customer_trx_id = p_control.previous_customer_trx_id
2431 and adj3.subsequent_trx_id = p_control.customer_trx_id
2432 );
2433
2434 debug( SQL%ROWCOUNT||' row(s) inserted', MSG_LEVEL_DEBUG );
2435
2436 /*---------------------------------+
2437 | Calling central MRC library |
2438 | for MRC Integration |
2439 +---------------------------------*/
2440
2441 ar_mrc_engine.maintain_mrc_data(
2442 p_event_mode => 'INSERT',
2443 p_table_name => 'AR_ADJUSTMENTS',
2444 p_mode => 'SINGLE',
2445 p_key_value => l_adjustment_id
2446 );
2447
2448
2449 /* VAT changes: create acct entry */
2450 l_ae_doc_rec.document_type := 'ADJUSTMENT';
2451 l_ae_doc_rec.document_id := l_adjustment_id;
2452 l_ae_doc_rec.accounting_entity_level := 'ONE';
2453 l_ae_doc_rec.source_table := 'ADJ';
2454 l_ae_doc_rec.source_id := l_adjustment_id;
2455
2456 --Bug 1329091 - PS is updated before Accounting Engine Call
2457
2458 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2459
2460 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2461
2462 EXCEPTION
2463 WHEN OTHERS THEN
2464 debug( 'EXCEPTION: Error executing insert stmt',
2465 MSG_LEVEL_BASIC );
2466 RAISE;
2467
2468 END;
2469
2470 ------------------------------------------------------------------
2471 -- Fill in document number in the ar_adjustments table
2472 ------------------------------------------------------------------
2473
2474 l_doc_where_clause :=
2475 'WHERE customer_trx_id = :bind1 '|| CRLF ||
2476 'and subsequent_trx_id = :bind2 '|| CRLF ||
2477 'and receivables_trx_id = -1 and payment_schedule_id = :bind3 ';
2478
2479 ----------------------------------------------------------
2480 -- Update adjustments with document number
2481 ----------------------------------------------------------
2482 update_adj_document_number(
2483 p_system_info,
2484 p_profile_info,
2485 p_control.customer_trx_id,
2486 p_control.previous_customer_trx_id,
2487 p_control.customer_trx_id,
2488 p_control.payment_schedule_id,
2489 l_doc_where_clause );
2490
2491
2492 ELSE
2493
2494 ----------------------------------------------------------------
2495 -- Update old adjustment record with new adjustment amounts
2496 ----------------------------------------------------------------
2497
2498 IF( l_foreign_transaction = YES ) THEN
2499
2500 ------------------------------------------------------------------
2501 -- Get acctd amt for the adj
2502 ------------------------------------------------------------------
2503 arp_util.calc_acctd_amount(
2504 p_system_info.base_currency,
2505 NULL, -- precision
2506 NULL, -- mau
2507 l_inv_rate,
2508 '+', -- type
2509 l_update_inv_adr, -- master_from
2510 l_update_inv_acctd_adr, -- acctd_master_from
2511 l_update_new_adj_amount, -- detail
2512 l_dummy, -- master_to
2513 l_new2_inv_acctd_adr, -- acctd_master_to
2514 l_new_adj_acctd_amount -- acctd_detail
2515 );
2516
2517 END IF;
2518
2519 -- do the update
2520 DECLARE
2521 l_adj_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2522 BEGIN
2523
2524 UPDATE ar_adjustments adj
2525 SET
2526 (
2527 amount,
2528 acctd_amount,
2529 line_adjusted,
2530 last_updated_by,
2531 last_update_date,
2532 last_update_login
2533 ) =
2534 (
2535 SELECT
2536 nvl(adj.amount, 0) - ra.line_applied - p_control.line_amount,
2537 decode(l_foreign_transaction,
2538 'N', nvl(adj.amount, 0) - ra.line_applied -
2539 p_control.line_amount,
2540 'Y', to_number(nvl(l_new_adj_acctd_amount, 0))),
2541 nvl(adj.amount, 0) - ra.line_applied - p_control.line_amount,
2542 p_profile_info.user_id,
2543 trunc(sysdate),
2544 p_profile_info.conc_login_id
2545 FROM
2546 ar_receivable_applications ra,
2547 ra_customer_trx ct
2548 WHERE ra.customer_trx_id = p_control.customer_trx_id
2549 and ra.status||'' = 'APP'
2550 and ra.applied_payment_schedule_id = p_control.payment_schedule_id
2551 and ct.customer_trx_id = ra.applied_customer_trx_id
2552 )
2553 WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
2554 and adj.subsequent_trx_id = p_control.customer_trx_id
2555 and adj.receivables_trx_id = -1
2556 and adj.payment_schedule_id = p_control.payment_schedule_id
2557 RETURNING adj.adjustment_id
2558 BULK COLLECT INTO l_adj_key_value_list;
2559
2560 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
2561
2562 /*---------------------------------+
2563 | Calling central MRC library |
2564 | for MRC Integration |
2565 +---------------------------------*/
2566
2567 ar_mrc_engine.maintain_mrc_data(
2568 p_event_mode => 'UPDATE',
2569 p_table_name => 'AR_ADJUSTMENTS',
2570 p_mode => 'BATCH',
2571 p_key_value_list => l_adj_key_value_list);
2572
2573 EXCEPTION
2574 WHEN OTHERS THEN
2575 debug( 'EXCEPTION: Error executing update stmt',
2576 MSG_LEVEL_BASIC );
2577 RAISE;
2578
2579 END;
2580
2581 /* VAT changes: update accounting entry */
2582 SELECT adjustment_id
2583 INTO l_adjustment_id
2584 FROM ar_adjustments adj
2585 WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
2586 and adj.subsequent_trx_id = p_control.customer_trx_id
2587 and adj.receivables_trx_id = -1
2588 and adj.payment_schedule_id = p_control.payment_schedule_id;
2589
2590 l_ae_doc_rec.document_type := 'ADJUSTMENT';
2591 l_ae_doc_rec.document_id := l_adjustment_id;
2592 l_ae_doc_rec.accounting_entity_level := 'ONE';
2593 l_ae_doc_rec.source_table := 'ADJ';
2594 l_ae_doc_rec.source_id := l_adjustment_id;
2595 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2596 l_ae_doc_rec.source_id_old := l_ccid;
2597 l_ae_doc_rec.other_flag := 'OVERRIDE';
2598
2599 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2600
2601 END IF;
2602
2603
2604 ----------------------------------------------------------------------
2605 -- We know that there's commitment adj done on this
2606 -- invoice, this means, the line remaining amt is not to be changed.
2607 -- Reverse the origial CM application amounts and use the new
2608 -- amounts passed in for the invoice's payment schedule.
2609 ----------------------------------------------------------------------
2610 DECLARE
2611 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2612
2613 BEGIN
2614
2615 UPDATE ar_payment_schedules ps
2616 SET (
2617 ps.status,
2618 ps.gl_date_closed,
2619 ps.actual_date_closed,
2620 ps.amount_credited,
2621 ps.amount_adjusted,
2622 ps.amount_due_remaining,
2623 ps.acctd_amount_due_remaining,
2624 ps.tax_remaining,
2625 ps.freight_remaining,
2626 ps.receivables_charges_remaining,
2627 ps.last_updated_by,
2628 ps.last_update_date,
2629 ps.last_update_login) = (
2630 SELECT
2631 decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2632 nvl(ra.freight_applied, 0) +
2633 nvl(ra.receivables_charges_applied, 0) +
2634 p_control.tax_amount +
2635 p_control.freight_amount +
2636 p_control.charge_amount,
2637 0, 'CL', 'OP'),
2638 decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2639 nvl(ra.freight_applied,0) +
2640 nvl(ra.receivables_charges_applied, 0) +
2641 p_control.tax_amount +
2642 p_control.freight_amount +
2643 p_control.charge_amount,
2644 0, greatest(max(ra2.gl_date),
2645 max(decode(adj2.status,
2646 'A', adj2.gl_date,
2647 ps2.gl_date))),
2648 ''),
2649 decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2650 nvl(ra.freight_applied, 0) +
2651 nvl(ra.receivables_charges_applied,0) +
2652 p_control.tax_amount +
2653 p_control.freight_amount +
2654 p_control.charge_amount,
2655 0, greatest(max(ra2.apply_date),
2656 max(decode(adj2.status,
2657 'A', adj2.apply_date,
2658 ps2.trx_date))),
2659 ''),
2660 nvl(ps2.amount_credited, 0) + ra.amount_applied +
2661 (p_control.line_amount +
2662 p_control.tax_amount +
2663 p_control.freight_amount +
2664 p_control.charge_amount),
2665 nvl(ps2.amount_adjusted, 0) - ra.line_applied - p_control.line_amount,
2666 ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2667 nvl(ra.freight_applied, 0) +
2668 nvl(ra.receivables_charges_applied, 0) +
2669 p_control.tax_amount +
2670 p_control.freight_amount +
2671 p_control.charge_amount,
2672 decode(l_foreign_transaction,
2673 'N',
2674 ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2675 nvl(ra.freight_applied, 0) +
2676 nvl(ra.receivables_charges_applied, 0) +
2677 p_control.tax_amount +
2678 p_control.freight_amount +
2679 p_control.charge_amount,
2680 'Y', to_number(nvl(l_new2_inv_acctd_adr, 0))),
2681 nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied, 0) +
2682 p_control.tax_amount,
2683 nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0) +
2684 p_control.freight_amount,
2685 nvl(ps2.receivables_charges_remaining, 0) +
2686 nvl(ra.receivables_charges_applied,0) + p_control.charge_amount,
2687 p_profile_info.user_id,
2688 trunc(sysdate),
2689 p_profile_info.conc_login_id
2690 FROM
2691 ar_receivable_applications ra,
2692 ar_payment_schedules ps2,
2693 ar_receivable_applications ra2,
2694 ar_adjustments adj2
2695 WHERE ra.customer_trx_id = p_control.customer_trx_id
2696 and ra.status||'' = 'APP'
2697 and ra.applied_payment_schedule_id = ps2.payment_schedule_id
2698 and ps.payment_schedule_id = ps2.payment_schedule_id
2699 and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
2700 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
2701 and nvl(ra2.confirmed_flag,'Y')='Y'
2702 GROUP BY
2703 ps2.payment_schedule_id,
2704 ra2.applied_payment_schedule_id,
2705 adj2.payment_schedule_id,
2706 ps2.amount_due_remaining,
2707 ra.amount_applied,
2708 ps2.gl_date,
2709 ps2.trx_date,
2710 ps2.amount_credited,
2711 ps2.amount_adjusted,
2712 ps2.acctd_amount_due_remaining,
2713 ra.acctd_amount_applied_to,
2714 ps2.amount_line_items_remaining,
2715 ra.line_applied,
2716 ps2.tax_remaining,
2717 ra.tax_applied,
2718 ps2.freight_remaining,
2719 ra.freight_applied,
2720 ps2.receivables_charges_remaining,
2721 ra.receivables_charges_applied,
2722 ps2.exchange_rate)
2723 WHERE ps.payment_schedule_id = p_control.payment_schedule_id
2724 RETURNING ps.payment_schedule_id
2725 BULK COLLECT INTO l_ar_ps_key_value_list;
2726
2727 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
2728
2729 /*---------------------------------+
2730 | Calling central MRC library |
2731 | for MRC Integration |
2732 +---------------------------------*/
2733
2734 ar_mrc_engine.maintain_mrc_data(
2735 p_event_mode => 'UPDATE',
2736 p_table_name => 'AR_PAYMENT_SCHEDULES',
2737 p_mode => 'BATCH',
2738 p_key_value_list => l_ar_ps_key_value_list);
2739
2740 EXCEPTION
2741 WHEN OTHERS THEN
2742 debug( 'EXCEPTION: Error executing update stmt',
2743 MSG_LEVEL_BASIC );
2744 RAISE;
2745
2746 END;
2747
2748
2749 ----------------------------------------------------------
2750 -- Update CM app with new line, frt, tax amount
2751 ----------------------------------------------------------
2752 DECLARE
2753 CURSOR get_app_id IS
2754 select app.receivable_application_id,
2755 app.amount_applied
2756 from ar_receivable_applications app
2757 where app.applied_payment_schedule_id = p_control.payment_schedule_id
2758 and app.customer_trx_id = p_control.customer_trx_id
2759 and app.status = 'APP';
2760
2761 BEGIN
2762
2763 FOR l_rec_del_app in del_app LOOP
2764 --
2765 --Release 11.5 VAT changes, delete accounting for Applications
2766 --
2767 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2768 l_ae_doc_rec.document_id := l_rec_del_app.trx_id;
2769 l_ae_doc_rec.accounting_entity_level := 'ONE';
2770 l_ae_doc_rec.source_table := 'RA';
2771 l_ae_doc_rec.source_id := l_rec_del_app.app_id;
2772 l_ae_doc_rec.source_id_old := '';
2773 l_ae_doc_rec.other_flag := '';
2774
2775 --Bug 1329091 - PS is updated before Accounting Engine Call
2776
2777 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2778
2779 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2780
2781 END LOOP;
2782
2783 UPDATE ar_receivable_applications ra
2784 SET
2785 acctd_amount_applied_from =
2786 decode(l_foreign_transaction,
2787 'N',
2788 -(p_control.line_amount +
2789 p_control.tax_amount +
2790 p_control.freight_amount +
2791 p_control.charge_amount),
2792 'Y', to_number(nvl(l_new_acctd_amt_applied_from, 0))),
2793 acctd_amount_applied_to =
2794 decode(l_foreign_transaction,
2795 'N',
2796 -(p_control.line_amount +
2797 p_control.tax_amount +
2798 p_control.freight_amount +
2799 p_control.charge_amount),
2800 'Y', to_number(nvl(l_new_acctd_amt_applied_to, 0))),
2801 amount_applied =
2802 -(p_control.line_amount +
2803 p_control.tax_amount +
2804 p_control.freight_amount +
2805 p_control.charge_amount),
2806 line_applied = -to_number(p_control.line_amount),
2807 tax_applied = -to_number(p_control.tax_amount),
2808 freight_applied = -to_number(p_control.freight_amount),
2809 receivables_charges_applied = -to_number(p_control.charge_amount),
2810 last_updated_by = p_profile_info.user_id,
2811 last_update_date = trunc(sysdate),
2812 last_update_login = p_profile_info.conc_login_id
2813 WHERE ra.applied_payment_schedule_id = p_control.payment_schedule_id
2814 and ra.status||'' = 'APP'
2815 and ra.customer_trx_id = p_control.customer_trx_id;
2816
2817 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
2818
2819 FOR l_app_id in get_app_id
2820 LOOP
2821 -- Call mrc engine to process update:
2822 ar_mrc_engine3.update_cm_application(
2823 l_app_id.receivable_application_id,
2824 p_control.payment_schedule_id, /* p_app_ps_id */
2825 p_control.customer_trx_id, /* p_ct_id */
2826 l_app_id.amount_applied);
2827 END LOOP;
2828
2829 FOR l_rec_del_app in del_app LOOP
2830 --
2831 --Release 11.5 VAT changes, recreate accounting for Applications
2832 --
2833 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2834 l_ae_doc_rec.document_id := l_rec_del_app.trx_id;
2835 l_ae_doc_rec.accounting_entity_level := 'ONE';
2836 l_ae_doc_rec.source_table := 'RA';
2837 l_ae_doc_rec.source_id := l_rec_del_app.app_id;
2838 l_ae_doc_rec.source_id_old := '';
2839 l_ae_doc_rec.other_flag := '';
2840 --Bug 1329091 - PS is updated before Accounting Engine Call
2841
2842 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2843
2844 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2845 /*bug-6976549*/
2846 arp_balance_check.CHECK_APPLN_BALANCE(l_rec_del_app.app_id,
2847 NULL,
2848 'N');
2849
2850 END LOOP;
2851
2852 EXCEPTION
2853 WHEN OTHERS THEN
2854 debug( 'EXCEPTION: Error executing update stmt',
2855 MSG_LEVEL_BASIC );
2856 RAISE;
2857
2858 END;
2859
2860
2861 print_fcn_label( 'arp_maintain_ps.update_adjustments()-' );
2862
2863 EXCEPTION
2864 WHEN OTHERS THEN
2865 debug( 'EXCEPTION: arp_maintain_ps.update_adjustments()',
2866 MSG_LEVEL_BASIC );
2867 RAISE;
2868
2869 END update_adjustments;
2870
2871
2872 ----------------------------------------------------------------------------
2873 PROCEDURE dump_control_rec( p_control control_rec_type ) IS
2874 BEGIN
2875
2876 debug( 'control.process_mode='||p_control.process_mode,
2877 MSG_LEVEL_DEBUG );
2878 debug( 'control.customer_trx_id='||p_control.customer_trx_id,
2879 MSG_LEVEL_DEBUG );
2880 debug( 'control.payment_schedule_id='||p_control.payment_schedule_id,
2881 MSG_LEVEL_DEBUG );
2882 debug( 'control.line_amount='||p_control.line_amount,
2883 MSG_LEVEL_DEBUG );
2884 debug( 'control.tax_amount='||p_control.tax_amount,
2885 MSG_LEVEL_DEBUG );
2886 debug( 'control.freight_amount='||p_control.freight_amount,
2887 MSG_LEVEL_DEBUG );
2888 debug( 'control.charge_amount='||p_control.charge_amount,
2889 MSG_LEVEL_DEBUG );
2890 debug( 'control.trx_type='||p_control.trx_type, MSG_LEVEL_DEBUG );
2891 debug( 'control.previous_customer_trx_id='||
2892 p_control.previous_customer_trx_id, MSG_LEVEL_DEBUG );
2893 debug( 'control.initial_customer_trx_id='||
2894 p_control.initial_customer_trx_id, MSG_LEVEL_DEBUG );
2895 debug( 'control.initial_trx_type='||p_control.initial_trx_type,
2896 MSG_LEVEL_DEBUG );
2897
2898 IF( p_control.is_open_receivable ) THEN
2899 debug( 'control.is_open_receivable=TRUE', MSG_LEVEL_DEBUG );
2900 ELSE
2901 debug( 'control.is_open_receivable=FALSE', MSG_LEVEL_DEBUG );
2902 END IF;
2903
2904 IF( p_control.is_postable ) THEN
2905 debug( 'control.is_postable=TRUE', MSG_LEVEL_DEBUG );
2906 ELSE
2907 debug( 'control.is_postable=FALSE', MSG_LEVEL_DEBUG );
2908 END IF;
2909
2910 IF( p_control.is_child ) THEN
2911 debug( 'control.is_child=TRUE', MSG_LEVEL_DEBUG );
2912 ELSE
2913 debug( 'control.is_child=FALSE', MSG_LEVEL_DEBUG );
2914 END IF;
2915
2916 IF( p_control.is_onacct_cm ) THEN
2917 debug( 'control.is_onacct_cm=TRUE', MSG_LEVEL_DEBUG );
2918 ELSE
2919 debug( 'control.is_onacct_cm=FALSE', MSG_LEVEL_DEBUG );
2920 END IF;
2921
2922
2923 EXCEPTION
2924 WHEN OTHERS THEN
2925 debug( 'EXCEPTION: arp_maintain_ps.dump_control_rec()',
2926 MSG_LEVEL_BASIC );
2927 RAISE;
2928
2929 END dump_control_rec;
2930
2931 ----------------------------------------------------------------------------
2932 FUNCTION get_applied_commitment_amount(
2933 p_control IN control_rec_type )
2934
2935 RETURN NUMBER IS
2936
2937 l_temp NUMBER;
2938
2939 BEGIN
2940
2941 print_fcn_label( 'arp_maintain_ps.get_applied_commitment_amount()+' );
2942
2943 BEGIN
2944
2945 SELECT
2946 nvl( sum( nvl(-adj.amount, 0) ), 0 )
2947 INTO l_temp
2948 FROM ar_adjustments adj
2949 WHERE adj.customer_trx_id =
2950 decode( p_control.initial_trx_type,
2951 'DEP', p_control.customer_trx_id,
2952 'GUAR', p_control.initial_customer_trx_id )
2953 and (
2954 ( p_control.initial_trx_type = 'DEP'
2955 and
2956 adj.subsequent_trx_id is null )
2957 or
2958 ( p_control.initial_trx_type = 'GUAR'
2959 and
2960 adj.subsequent_trx_id = p_control.customer_trx_id ) )
2961 and adj.receivables_trx_id = -1;
2962
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 debug( 'EXCEPTION: Error executing select stmt',
2966 MSG_LEVEL_BASIC );
2967 RAISE;
2968
2969 END;
2970
2971
2972 print_fcn_label( 'arp_maintain_ps.get_applied_commitment_amount()-' );
2973
2974 RETURN l_temp;
2975
2976 EXCEPTION
2977 WHEN OTHERS THEN
2978 debug( 'EXCEPTION: arp_maintain_ps.get_applied_commitment_amount()',
2979 MSG_LEVEL_BASIC );
2980 RAISE;
2981
2982 END get_applied_commitment_amount;
2983
2984
2985 ----------------------------------------------------------------------------
2986 FUNCTION ps_records_exist( p_customer_trx_id IN BINARY_INTEGER )
2987
2988 RETURN BOOLEAN IS
2989
2990 l_temp NUMBER;
2991
2992 BEGIN
2993
2994 print_fcn_label( 'arp_maintain_ps.ps_records_exist()+' );
2995
2996 SELECT 1
2997 INTO l_temp
2998 FROM ar_payment_schedules
2999 WHERE customer_trx_id = p_customer_trx_id;
3000
3001 print_fcn_label( 'arp_maintain_ps.ps_records_exist()-' );
3002
3003 RETURN( TRUE );
3004
3005 EXCEPTION
3006 WHEN NO_DATA_FOUND THEN
3007 print_fcn_label( 'arp_maintain_ps.ps_records_exist()-' );
3008 RETURN( FALSE );
3009 WHEN TOO_MANY_ROWS THEN
3010 print_fcn_label( 'arp_maintain_ps.ps_records_exist()-' );
3011 RETURN( TRUE );
3012 WHEN OTHERS THEN
3013 debug( 'EXCEPTION: arp_maintain_ps.ps_records_exist()',
3014 MSG_LEVEL_BASIC );
3015 RAISE;
3016
3017 END ps_records_exist;
3018
3019
3020 ----------------------------------------------------------------------------
3021 --
3022 -- PROCEDURE NAME: maintain_payment_schedules
3023 --
3024 -- DECSRIPTION:
3025 -- Server-side entry point for the Maintain Payment Schedules.
3026 --
3027 -- ARGUMENTS:
3028 -- IN:
3029 -- mode (I)nsert, (D)elete or (U)pdate
3030 -- customer_trx_id Transaction's payment sched to be modified
3031 -- payment_schedule_id Specific id to be changed.
3032 -- For U mode only and regular CM only.
3033 -- Must pass value for amount parameters.
3034 -- line_amount New CM line amount
3035 -- tax_amount New CM tax amount
3036 -- freight_amount New CM freight amount
3037 -- charge_amount New CM charges amount
3038 -- reversed_cash_receipt_id For DM reversals, I mode only
3039 --
3040 -- IN/OUT:
3041 -- applied_commitment_amount Amount of invoice applied to commitment
3042 --
3043 -- OUT:
3044 --
3045 -- NOTES:
3046 -- Exception raised if Oracle error.
3047 -- App_exception is raised for all other fatal errors and a message
3048 -- is put on the AOL stack. The public variable g_error_buffer is
3049 -- populated for both types of errors.
3050 --
3051 --
3052 -- HISTORY:
3053 --
3054 ----------------------------------------------------------------------------
3055 PROCEDURE maintain_payment_schedules(
3056 p_mode IN VARCHAR2,
3057 p_customer_trx_id IN NUMBER,
3058 p_payment_schedule_id IN NUMBER,
3059 p_line_amount IN NUMBER,
3060 p_tax_amount IN NUMBER,
3061 p_freight_amount IN NUMBER,
3062 p_charge_amount IN NUMBER,
3063 p_applied_commitment_amount IN OUT NOCOPY NUMBER,
3064 p_reversed_cash_receipt_id IN NUMBER DEFAULT NULL
3065 ) IS
3066
3067 l_doc_where_clause VARCHAR2(1000);
3068 l_control_rec control_rec_type;
3069 /* VAT changes */
3070 l_ae_doc_rec ae_doc_rec_type;
3071 l_adjustment_id ar_adjustments.adjustment_id%type;
3072
3073 BEGIN
3074
3075 print_fcn_label( 'arp_maintain_ps.maintain_payment_schedules()+' );
3076
3077 -- Validate parameters
3078 IF( p_mode IS NULL OR
3079 p_mode NOT IN (I, U, D) OR
3080 p_customer_trx_id IS NULL OR
3081 (p_payment_schedule_id IS NOT NULL AND p_mode <> U) ) THEN
3082
3083 g_error_buffer := MSG_INVALID_PARAMETERS;
3084 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
3085 RAISE invalid_parameters;
3086
3087 END IF;
3088
3089 --------------------------------------------------------------------
3090 -- Assign values to control_rec
3091 --------------------------------------------------------------------
3092 l_control_rec.process_mode := p_mode;
3093 l_control_rec.customer_trx_id := p_customer_trx_id;
3094 l_control_rec.payment_schedule_id := p_payment_schedule_id;
3095 l_control_rec.line_amount := nvl( p_line_amount, 0);
3096 l_control_rec.tax_amount := nvl( p_tax_amount, 0);
3097 l_control_rec.freight_amount := nvl( p_freight_amount, 0);
3098 l_control_rec.charge_amount := nvl( p_charge_amount, 0);
3099 l_control_rec.reversed_cash_receipt_id := p_reversed_cash_receipt_id;
3100
3101
3102 SAVEPOINT ar_payment_schedule;
3103
3104 --------------------------------------------------------------------
3105 -- Get other info from tables for control_rec
3106 --------------------------------------------------------------------
3107 do_setup( l_control_rec );
3108
3109 --------------------------------------------------------------------
3110 -- Print out NOCOPY control_rec
3111 --------------------------------------------------------------------
3112 dump_control_rec( l_control_rec );
3113
3114 --------------------------------------------------------------------
3115 -- Do nothing if open_rec = N
3116 -- or I mode and ps records exist
3117 --------------------------------------------------------------------
3118 -- Case where the transaction CM does not have a open Receivable
3119 -- and mode is <> Deletion
3120 -- no need to process
3121
3122 IF( NOT l_control_rec.is_open_receivable
3123 AND l_control_rec.process_mode <> 'D' ) THEN
3124
3125 RETURN;
3126
3127 END IF;
3128
3129 IF( l_control_rec.process_mode = I AND
3130 ps_records_exist( l_control_rec.customer_trx_id ) ) THEN
3131
3132 RETURN;
3133
3134 END IF;
3135
3136 --------------------------------------------------------------------
3137 -- Process all transactions except for regular CMs
3138 --------------------------------------------------------------------
3139 IF( l_control_rec.previous_customer_trx_id IS NULL ) THEN
3140
3141 debug( ' Process non CM transactions', MSG_LEVEL_DEBUG );
3142
3143 ----------------------------------------------------------------
3144 -- Update, Delete case
3145 ----------------------------------------------------------------
3146 IF( l_control_rec.process_mode in ( U, D ) ) THEN
3147
3148 debug( ' Update, Delete mode', MSG_LEVEL_DEBUG );
3149
3150 IF( l_control_rec.initial_trx_type = DEP ) THEN
3151
3152 debug( ' DEP case', MSG_LEVEL_DEBUG );
3153
3154 /* VAT changes: delete accounting entry for adjustment */
3155 SELECT adj.adjustment_id into l_adjustment_id
3156 FROM ar_adjustments adj
3157 WHERE adj.customer_trx_id = l_control_rec.customer_trx_id
3158 and adj.receivables_trx_id = -1;
3159
3160 l_ae_doc_rec.document_type := 'ADJUSTMENT';
3161 l_ae_doc_rec.document_id := l_adjustment_id;
3162 l_ae_doc_rec.accounting_entity_level := 'ONE';
3163 l_ae_doc_rec.source_table := 'ADJ';
3164 l_ae_doc_rec.source_id := l_adjustment_id;
3165 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
3166
3167 -- arabdep: delete invoice adjustments
3168 delete_adjustments( l_control_rec.customer_trx_id, NULL );
3169
3170 ELSIF( l_control_rec.initial_trx_type = GUAR ) THEN
3171
3172 debug( ' GUAR case', MSG_LEVEL_DEBUG );
3173
3174 -- arabaj: delete guar adj, update guar ps created by child
3175 reverse_adjustments(
3176 system_info,
3177 profile_info,
3178 l_control_rec.initial_customer_trx_id,
3179 l_control_rec.customer_trx_id );
3180
3181 END IF;
3182
3183 -- aradps: delete old ps
3184 delete_payment_schedule( l_control_rec.customer_trx_id );
3185
3186 END IF;
3187
3188 ----------------------------------------------------------------
3189 -- Insert, Update case
3190 ----------------------------------------------------------------
3191 IF( p_mode in ( I, U ) ) THEN
3192
3193 debug( ' Insert, Update mode', MSG_LEVEL_DEBUG );
3194
3195 --------------------------------------------------------------
3196 -- araips: call raaips
3197 --------------------------------------------------------------
3198 arp_maintain_ps2.insert_inv_ps_private(
3199 system_info,
3200 profile_info,
3201 l_control_rec.customer_trx_id,
3202 l_control_rec.reversed_cash_receipt_id );
3203
3204
3205 IF( l_control_rec.is_child ) THEN
3206
3207 ----------------------------------------------------------
3208 -- araips: call raaups to insert adjustments for
3209 -- commitment invoices
3210 ----------------------------------------------------------
3211 arp_maintain_ps2.insert_child_adj_private(
3212 system_info,
3213 profile_info,
3214 l_control_rec.customer_trx_id );
3215
3216 ----------------------------------------------------------
3217 -- arapca: get invoice amount that was applied to commitment
3218 ----------------------------------------------------------
3219 p_applied_commitment_amount :=
3220 get_applied_commitment_amount( l_control_rec );
3221
3222 ----------------------------------------------------------
3223 -- Update adjustments with document number
3224 ----------------------------------------------------------
3225 ----------------------------------------------------------
3226 -- Construct where clause
3227 ----------------------------------------------------------
3228 IF( l_control_rec.initial_trx_type = DEP ) THEN
3229
3230 l_doc_where_clause :=
3231 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3232 'and receivables_trx_id = -1';
3233
3234 ----------------------------------------------------------
3235 -- Update adjustments with document number
3236 ----------------------------------------------------------
3237 update_adj_document_number(
3238 system_info,
3239 profile_info,
3240 l_control_rec.customer_trx_id,
3241 l_control_rec.customer_trx_id,
3242 null,
3243 null,
3244 l_doc_where_clause );
3245
3246
3247
3248 ELSE -- GUAR
3249
3250 l_doc_where_clause :=
3251 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3252 'and subsequent_trx_id = :bind2 '|| CRLF ||
3253 'and receivables_trx_id = -1';
3254
3255
3256
3257 ----------------------------------------------------------
3258 -- Update adjustments with document number
3259 ----------------------------------------------------------
3260 update_adj_document_number(
3261 system_info,
3262 profile_info,
3263 l_control_rec.customer_trx_id,
3264 l_control_rec.initial_customer_trx_id,
3265 l_control_rec.customer_trx_id,
3266 null,
3267 l_doc_where_clause );
3268
3269 END IF;
3270
3271 END IF;
3272 END IF;
3273
3274 --------------------------------------------------------------------
3275 -- Process regular CMs where payment_schedule_id is NOT specified
3276 --------------------------------------------------------------------
3277 -- Process regular CM
3278 --
3279 ELSIF( l_control_rec.previous_customer_trx_id IS NOT NULL AND
3280 l_control_rec.payment_schedule_id IS NULL ) THEN
3281
3282 debug( ' Process regular CMs (payment_schedule_id = NULL)',
3283 MSG_LEVEL_DEBUG );
3284
3285 --
3286 -- Update, Delete case
3287 --
3288
3289 IF( l_control_rec.process_mode in ( U, D ) ) THEN
3290
3291 debug( ' Update, Delete mode', MSG_LEVEL_DEBUG );
3292
3293 /* Bug 2808262 Check if the CM being incompleted, is actually
3294 having any adjustments records or not. If not, there is no
3295 need for the updation of payment schedule, or deletion of
3296 adjustment record and its corresponding distributions.
3297 */
3298
3299 IF ( l_control_rec.initial_trx_type = DEP
3300 AND
3301 /*salladi 3118714*/
3302 l_control_rec.is_child=TRUE ) THEN
3303
3304 debug( ' DEP case', MSG_LEVEL_DEBUG );
3305
3306 -- arabaj: delete invoice adj (created by cm), update inv ps
3307 reverse_adjustments(
3308 system_info,
3309 profile_info,
3310 l_control_rec.previous_customer_trx_id,
3311 l_control_rec.customer_trx_id );
3312
3313
3314 ELSIF ( l_control_rec.initial_trx_type = GUAR
3315 AND
3316 l_control_rec.is_child ) THEN
3317
3318 debug( ' GUAR case', MSG_LEVEL_DEBUG );
3319
3320 -- arabaj: delete guar adj (created by cm), update guar ps
3321 reverse_adjustments(
3322 system_info,
3323 profile_info,
3324 l_control_rec.initial_customer_trx_id,
3325 l_control_rec.customer_trx_id );
3326
3327 END IF;
3328
3329 -- arabcm: update inv ps (reverse cm effect)
3330 arp_standard.debug(' reverse_cm_effect+');
3331 reverse_cm_effect(
3332 system_info,
3333 profile_info,
3334 l_control_rec.customer_trx_id );
3335 arp_standard.debug(' reverse_cm_effect-');
3336
3337 -- aradra: delete cm app recs
3338 arp_standard.debug(' delete_applications+');
3339 delete_applications( l_control_rec.customer_trx_id );
3340 arp_standard.debug(' delete_applications-');
3341 -- aradps: delete cm ps
3342 arp_standard.debug(' delete_payment_schedule+');
3343 delete_payment_schedule( l_control_rec.customer_trx_id );
3344 arp_standard.debug(' delete_payment_schedule-');
3345 END IF;
3346
3347 IF( l_control_rec.process_mode in ( I, U ) ) THEN
3348
3349 debug( ' Insert, Update mode', MSG_LEVEL_DEBUG );
3350
3351 IF( l_control_rec.is_child ) THEN
3352
3353 ----------------------------------------------------------
3354 -- araiad: create adj, update ps
3355 ----------------------------------------------------------
3356 arp_standard.debug(' arp_maintain_ps2.insert_cm_child_adj_private+');
3357 arp_maintain_ps2.insert_cm_child_adj_private(
3358 system_info,
3359 profile_info,
3360 l_control_rec.customer_trx_id );
3361 arp_standard.debug(' arp_maintain_ps2.insert_cm_child_adj_private-');
3362
3363
3364 ----------------------------------------------------------
3365 -- Update adjustments with document number
3366 ----------------------------------------------------------
3367 ----------------------------------------------------------
3368 -- Construct where clause
3369 ----------------------------------------------------------
3370 IF( l_control_rec.initial_trx_type = DEP ) THEN
3371
3372 l_doc_where_clause :=
3373 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3374 'and subsequent_trx_id = :bind2 '|| CRLF ||
3375 'and receivables_trx_id = -1';
3376
3377 ----------------------------------------------------------
3378 -- Update adjustments with document number
3379 ----------------------------------------------------------
3380
3381 update_adj_document_number(
3382 system_info,
3383 profile_info,
3384 l_control_rec.customer_trx_id,
3385 l_control_rec.previous_customer_trx_id,
3386 l_control_rec.customer_trx_id,
3387 null,
3388 l_doc_where_clause );
3389
3390 ELSE -- GUAR
3391
3392 l_doc_where_clause :=
3393 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3394 'and subsequent_trx_id = :bind2 '|| CRLF ||
3395 'and receivables_trx_id = -1';
3396
3397
3398
3399 ----------------------------------------------------------
3400 -- Update adjustments with document number
3401 ----------------------------------------------------------
3402 arp_standard.debug(' update_adj_document_number+');
3403 update_adj_document_number(
3404 system_info,
3405 profile_info,
3406 l_control_rec.customer_trx_id,
3407 l_control_rec.initial_customer_trx_id,
3408 l_control_rec.customer_trx_id,
3409 null,
3410 l_doc_where_clause );
3411 arp_standard.debug(' update_adj_document_number-');
3412 END IF;
3413
3414
3415
3416
3417 END IF;
3418
3419 -- araira: create cm ps, apps
3420 arp_standard.debug(' arp_maintain_ps2.insert_cm_ps_private+');
3421 arp_maintain_ps2.insert_cm_ps_private(
3422 system_info,
3423 profile_info,
3424 l_control_rec.customer_trx_id );
3425 arp_standard.debug(' arp_maintain_ps2.insert_cm_ps_private-');
3426
3427
3428 END IF;
3429
3430
3431 --------------------------------------------------------------------
3432 -- Process regular CMs where payment_schedule_id is specified
3433 --------------------------------------------------------------------
3434 ELSIF( l_control_rec.previous_customer_trx_id IS NOT NULL AND
3435 l_control_rec.payment_schedule_id IS NOT NULL ) THEN
3436
3437 debug( ' Process regular CMs (payment_schedule_id <> NULL)',
3438 MSG_LEVEL_DEBUG );
3439
3440 IF( l_control_rec.process_mode <> U ) THEN
3441
3442 -- >> ERROR: bad mode for this case
3443
3444 debug( ' Bad mode', MSG_LEVEL_DEBUG );
3445 null;
3446 END IF;
3447
3448 IF( l_control_rec.initial_trx_type = DEP ) THEN
3449
3450 debug( ' DEP case', MSG_LEVEL_DEBUG );
3451
3452 -- araudps: insert dep adj, if not exists, else update adj
3453 update_adjustments( system_info, profile_info, l_control_rec );
3454
3455 ELSE
3456 debug( ' non-DEP case', MSG_LEVEL_DEBUG );
3457
3458 -- araups: correct round error, update inv ps, update cm app
3459 arp_standard.debug(' update_payment_schedule+');
3460 update_payment_schedule(
3461 system_info,
3462 profile_info,
3463 l_control_rec );
3464 arp_standard.debug(' update_payment_schedule-');
3465
3466 END IF;
3467
3468 END IF;
3469
3470
3471 print_fcn_label( 'arp_maintain_ps.maintain_payment_schedules()-' );
3472
3473 EXCEPTION
3474 WHEN OTHERS THEN
3475 debug( 'EXCEPTION: arp_maintain_ps.maintain_payment_schedules()',
3476 MSG_LEVEL_BASIC );
3477
3478 close_cursors;
3479 ROLLBACK TO ar_payment_schedule;
3480
3481 IF( sqlcode = 1 ) THEN
3482 --
3483 -- User-defined exception
3484 --
3485 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
3486 FND_MESSAGE.set_token( 'GENERIC_TEXT', g_error_buffer );
3487 APP_EXCEPTION.raise_exception;
3488
3489 ELSE
3490 --
3491 -- Oracle error
3492 --
3493 g_error_buffer := SQLERRM;
3494
3495 RAISE;
3496
3497 END IF;
3498
3499 RAISE;
3500
3501 END maintain_payment_schedules;
3502
3503 ---------------------------------------------------------------------------
3504 -- Test Functions
3505 ---------------------------------------------------------------------------
3506 PROCEDURE test_build_doc_combo_sql
3507 IS
3508
3509 BEGIN
3510
3511 enable_debug( 1000000 );
3512
3513 build_doc_combo_sql(
3514 system_info,
3515 profile_info,
3516 doc_combo_select_c );
3517
3518
3519 END;
3520
3521
3522 ---------------------------------------------------------------------------
3523 PROCEDURE test_build_doc_ins_audit_sql( p_where_clause VARCHAR2 )
3524 IS
3525
3526 BEGIN
3527
3528 enable_debug( 1000000 );
3529
3530 build_doc_insert_audit_sql(
3531 system_info,
3532 profile_info,
3533 p_where_clause,
3534 doc_insert_audit_c );
3535
3536 END;
3537
3538 ---------------------------------------------------------------------------
3539 PROCEDURE test_build_doc_update_adj_sql( p_where_clause VARCHAR2 )
3540 IS
3541
3542 BEGIN
3543
3544 enable_debug( 1000000 );
3545
3546 build_doc_update_adj_sql(
3547 system_info,
3548 profile_info,
3549 'my_seq', -- seq name
3550 1, -- seq id
3551 p_where_clause,
3552 doc_update_adj_c );
3553
3554
3555 END;
3556
3557 ---------------------------------------------------------------------------
3558 PROCEDURE test_update_adj_doc_number(
3559 p_customer_trx_id BINARY_INTEGER,
3560 p_update_where_clause VARCHAR2 ) IS
3561
3562 BEGIN
3563
3564 enable_debug( 1000000 );
3565
3566
3567 update_adj_document_number(
3568 system_info,
3569 profile_info,
3570 p_customer_trx_id,
3571 null,
3572 null,
3573 null,
3574 p_update_where_clause );
3575
3576
3577
3578
3579 END;
3580
3581
3582
3583 ---------------------------------------------------------------------------
3584 --
3585 -- Constructor code
3586 --
3587 PROCEDURE init IS
3588 BEGIN
3589
3590 print_fcn_label( 'arp_maintain_ps.constructor()+' );
3591
3592 get_error_message_text;
3593
3594 print_fcn_label( 'arp_maintain_ps.constructor()-' );
3595
3596
3597 EXCEPTION
3598 WHEN OTHERS THEN
3599 debug('EXCEPTION: arp_maintain_ps.constructor()');
3600 debug(SQLERRM);
3601 RAISE;
3602 END init;
3603
3604 BEGIN
3605 init;
3606 END arp_maintain_ps;