[Home] [Help]
PACKAGE BODY: APPS.ARP_MAINTAIN_PS
Source
1 PACKAGE BODY arp_maintain_ps AS
2 /* $Header: ARTEMPSB.pls 120.18.12020000.2 2012/07/25 13:16:25 kkikkise 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 l_event_id NUMBER;
1067 l_event_source_info xla_events_pub_pkg.t_event_source_info;
1068 l_security xla_events_pub_pkg.t_security;
1069 l_source_id_int_1 NUMBER;
1070
1071 CURSOR get_adj_event_id(pc_customer_trx_id IN NUMBER,pc_subsequent_trx_id IN NUMBER) IS
1072 SELECT
1073 DISTINCT adj.event_id ,adj.ADJUSTMENT_ID
1074 FROM
1075 ar_adjustments adj
1076 WHERE
1077 adj.customer_trx_id = pc_customer_trx_id
1078 and (adj.subsequent_trx_id = p_subsequent_trx_id OR -1=Nvl(p_subsequent_trx_id,-1) )
1079 and adj.receivables_trx_id = -1
1080 AND exists
1081 (Select 'a' from xla_events
1082 where entity_id in (
1083 Select entity_id from xla_transaction_entities
1084 where entity_code = 'ADJUSTMENTS'
1085 and nvl(source_id_int_1 , -99) = adj.ADJUSTMENT_ID
1086 and ledger_id = adj.set_of_books_id
1087 and application_id = 222 )
1088 );
1089
1090 BEGIN
1091
1092 print_fcn_label( 'arp_maintain_ps.delete_adjustments()+' );
1093
1094 --
1095 --
1096 BEGIN
1097
1098
1099
1100 IF PG_DEBUG in ('Y', 'C') THEN
1101 print_fcn_label(' p_customer_trx_id='|| p_customer_trx_id );
1102 print_fcn_label(' p_subsequent_trx_id='|| p_subsequent_trx_id );
1103 END IF;
1104
1105 /* anjprasa bug 13574852 : merged both if else block in one
1106 and added delete XLA event before deleting adjustment */
1107
1108 l_event_source_info.entity_type_code:= 'ADJUSTMENTS';
1109 l_security.security_id_int_1 := arp_global.sysparam.org_id;
1110 l_event_source_info.application_id := 222;
1111 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id;
1112
1113 OPEN get_adj_event_id(p_customer_trx_id,p_subsequent_trx_id);
1114 loop
1115 FETCH get_adj_event_id INTO l_event_id,l_source_id_int_1;
1116
1117 EXIT WHEN get_adj_event_id%ROWCOUNT > 20 OR get_adj_event_id%NOTFOUND;
1118 print_fcn_label('if l_event_id='|| l_event_id );
1119
1120 IF PG_DEBUG in ('Y', 'C') THEN
1121 print_fcn_label(' l_event_id='|| l_event_id );
1122 print_fcn_label(' l_source_id_int_1='|| l_source_id_int_1 );
1123 print_fcn_label(' arp_global.sysparam.org_id='|| arp_global.sysparam.org_id );
1124 print_fcn_label(' arp_standard.sysparm.set_of_books_id='|| arp_standard.sysparm.set_of_books_id );
1125 END IF;
1126 l_event_source_info.source_id_int_1 := l_source_id_int_1;
1127 /* first delete xla record and then delete from adjustment*/
1128 if( l_event_id IS NOT null) then
1129 xla_events_pub_pkg.delete_event
1130 ( p_event_source_info => l_event_source_info,
1131 p_event_id => l_event_id,
1132 p_valuation_method => NULL,
1133 p_security_context => l_security
1134 );
1135 end if;
1136
1137 END LOOP;
1138 CLOSE get_adj_event_id;
1139
1140
1141
1142 DELETE
1143 FROM ar_adjustments adj
1144 WHERE adj.customer_trx_id = p_customer_trx_id
1145 and (adj.subsequent_trx_id = p_subsequent_trx_id OR -1=Nvl(p_subsequent_trx_id,-1) )
1146 and adj.receivables_trx_id = -1
1147 RETURNING adjustment_id
1148 BULK COLLECT INTO l_adj_key_value_list;
1149
1150 /*---------------------------------+
1151 | Calling central MRC library |
1152 | for MRC Integration |
1153 +---------------------------------*/
1154 /* commenting mrc engine call as per Suren
1155 ar_mrc_engine.maintain_mrc_data(
1156 p_event_mode => 'DELETE',
1157 p_table_name => 'AR_ADJUSTMENTS',
1158 p_mode => 'BULK',
1159 p_key_value_list => l_adj_key_value_list); */
1160
1161
1162
1163 debug( SQL%ROWCOUNT||' row(s) deleted', MSG_LEVEL_DEBUG );
1164
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 debug( 'EXCEPTION: Error executing delete stmt',
1168 MSG_LEVEL_BASIC );
1169 RAISE;
1170 END;
1171
1172 print_fcn_label( 'arp_maintain_ps.delete_adjustments()-' );
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 debug( 'EXCEPTION: arp_maintain_ps.delete_adjustments()',
1177 MSG_LEVEL_BASIC );
1178 RAISE;
1179
1180 END delete_adjustments;
1181
1182 ----------------------------------------------------------------------------
1183 PROCEDURE reverse_adjustments(
1184 p_system_info IN arp_trx_global.system_info_rec_type,
1185 p_profile_info IN arp_trx_global.profile_rec_type,
1186 p_customer_trx_id IN BINARY_INTEGER,
1187 p_subsequent_trx_id IN BINARY_INTEGER
1188 ) IS
1189 /* VAT changes */
1190 l_ae_doc_rec ae_doc_rec_type;
1191 l_adjustment_id ar_adjustments.adjustment_id%type;
1192
1193 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1194
1195 BEGIN
1196
1197 print_fcn_label( 'arp_maintain_ps.reverse_adjustments()+' );
1198
1199 BEGIN
1200
1201 UPDATE ar_payment_schedules ps
1202 SET (
1203 ps.status,
1204 ps.gl_date_closed,
1205 ps.actual_date_closed,
1206 ps.amount_adjusted,
1207 ps.amount_due_remaining,
1208 ps.acctd_amount_due_remaining,
1209 ps.amount_line_items_remaining,
1210 ps.tax_remaining,
1211 ps.freight_remaining,
1212 ps.receivables_charges_remaining,
1213 last_updated_by,
1214 last_update_date,
1215 last_update_login) = (
1216 SELECT
1217 decode(ps2.amount_due_remaining - adj.amount, 0, 'CL', 'OP'),
1218 decode(ps2.amount_due_remaining - adj.amount,
1219 0,
1220 greatest(nvl(max(decode(ra2.confirmed_flag,
1221 'N', ps2.gl_date,
1222 ra2.gl_date)),
1223 ps2.gl_date),
1224 max(decode(adj2.customer_trx_id,
1225 p_customer_trx_id,
1226 decode(adj2.subsequent_trx_id,
1227 p_subsequent_trx_id,
1228 decode( adj2.receivables_trx_id,
1229 -1, ps2.gl_date,
1230 adj2.gl_date ),
1231 adj2.gl_date),
1232 adj2.gl_date)
1233 )
1234 ),
1235 TO_DATE('4712/12/31', 'YYYY/MM/DD')),
1236 decode(ps2.amount_due_remaining - adj.amount,
1237 0,
1238 greatest(nvl(max(decode(ra2.confirmed_flag,
1239 'N', ps2.trx_date,
1240 ra2.apply_date)),
1241 ps2.trx_date),
1242 max(decode(adj2.customer_trx_id,
1243 p_customer_trx_id,
1244 decode(adj2.subsequent_trx_id,
1245 p_subsequent_trx_id,
1246 decode(adj2.receivables_trx_id,
1247 -1, ps2.trx_date,
1248 adj2.apply_date),
1249 adj2.apply_date),
1250 adj2.apply_date)
1251 )
1252 ),
1253 TO_DATE('4712/12/31', 'YYYY/MM/DD')),
1254 nvl(ps2.amount_adjusted, 0) - adj.amount,
1255 ps2.amount_due_remaining - adj.amount,
1256 ps2.acctd_amount_due_remaining - adj.acctd_amount,
1257 nvl(ps2.amount_line_items_remaining, 0) -
1258 nvl(adj.line_adjusted, decode(adj.type, 'LINE', adj.amount, 0)),
1259 nvl(ps2.tax_remaining, 0) -
1260 nvl(adj.tax_adjusted, decode(adj.type, 'TAX', adj.amount, 0)),
1261 nvl(ps2.freight_remaining, 0) -
1262 nvl(adj.freight_adjusted,
1263 decode(adj.type, 'FREIGHT', adj.amount, 0)),
1264 nvl(ps2.receivables_charges_remaining, 0) -
1265 nvl(adj.receivables_charges_adjusted,
1266 decode(adj.type, 'CHARGES', adj.amount, 0)),
1267 p_profile_info.user_id,
1268 trunc(sysdate),
1269 p_profile_info.conc_login_id
1270 FROM
1271 ar_adjustments adj,
1272 ar_payment_schedules ps2,
1273 ar_adjustments adj2,
1274 ar_receivable_applications ra2
1275 WHERE adj.receivables_trx_id =-1
1276 and adj.customer_trx_id = p_customer_trx_id
1277 and adj.subsequent_trx_id = p_subsequent_trx_id
1278 and adj.payment_schedule_id = ps2.payment_schedule_id
1279 and ps2.payment_schedule_id = ps.payment_schedule_id
1280 and ps2.payment_schedule_id = adj2.payment_schedule_id
1281 and adj2.status = 'A'
1282 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id(+)
1283 GROUP BY
1284 ps2.payment_schedule_id,
1285 ra2.applied_payment_schedule_id,
1286 adj2.payment_schedule_id,
1287 ps2.amount_due_remaining,
1288 adj.amount,
1289 ps2.gl_date,
1290 ps2.trx_date,
1291 ps2.amount_adjusted,
1292 ps2.acctd_amount_due_remaining,
1293 adj.acctd_amount,
1294 ps2.amount_line_items_remaining,
1295 adj.line_adjusted,
1296 adj.type,
1297 ps2.tax_remaining,
1298 adj.tax_adjusted,
1299 ps2.freight_remaining,
1300 adj.freight_adjusted,
1301 ps2.receivables_charges_remaining,
1302 adj.receivables_charges_adjusted )
1303 WHERE ps.payment_schedule_id in
1304 (
1305 SELECT
1306 adj3.payment_schedule_id
1307 FROM ar_adjustments adj3
1308 WHERE adj3.customer_trx_id = p_customer_trx_id
1309 and adj3.subsequent_trx_id = p_subsequent_trx_id
1310 and adj3.receivables_trx_id = -1
1311 )
1312 RETURNING ps.payment_schedule_id
1313 BULK COLLECT INTO l_ar_ps_key_value_list;
1314
1315 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1316
1317 /*---------------------------------+
1318 | Calling central MRC library |
1319 | for MRC Integration |
1320 +---------------------------------*/
1321
1322 ar_mrc_engine.maintain_mrc_data(
1323 p_event_mode => 'UPDATE',
1324 p_table_name => 'AR_PAYMENT_SCHEDULES',
1325 p_mode => 'BATCH',
1326 p_key_value_list => l_ar_ps_key_value_list);
1327
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 debug( 'EXCEPTION: Error executing update stmt',
1331 MSG_LEVEL_BASIC );
1332 RAISE;
1333 END;
1334
1335 --
1336 --
1337 /* VAT changes: delete acct entry */
1338 /* bug 2808262. Changed the code to have a cursor */
1339
1340 /* anjprasa bug 13574852 : added extra where clause to filter correct no of loop */
1341 DECLARE
1342 CURSOR c1 IS SELECT adj.adjustment_id
1343 FROM ar_adjustments adj
1344 WHERE adj.customer_trx_id = p_customer_trx_id
1345 AND adj.receivables_trx_id = -1
1346 AND (adj.subsequent_trx_id = p_subsequent_trx_id OR -1=Nvl(p_subsequent_trx_id,-1) );
1347 BEGIN
1348 FOR i IN c1 LOOP
1349 delete_adjustments( p_customer_trx_id, p_subsequent_trx_id );
1350
1351 END LOOP;
1352 END;
1353 print_fcn_label( 'arp_maintain_ps.reverse_adjustments()-' );
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 debug( 'EXCEPTION: arp_maintain_ps.reverse_adjustments()',
1358 MSG_LEVEL_BASIC );
1359 RAISE;
1360
1361 END reverse_adjustments;
1362
1363
1364 ----------------------------------------------------------------------------
1365 PROCEDURE reverse_cm_effect(
1366 p_system_info IN arp_trx_global.system_info_rec_type,
1367 p_profile_info IN arp_trx_global.profile_rec_type,
1368 p_customer_trx_id IN BINARY_INTEGER
1369 ) IS
1370
1371 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1372
1373 BEGIN
1374
1375 print_fcn_label( 'arp_maintain_ps.reverse_cm_effect()+' );
1376
1377 BEGIN
1378
1379 UPDATE ar_payment_schedules ps
1380 SET (
1381 status,
1382 gl_date_closed,
1383 actual_date_closed,
1384 amount_credited,
1385 amount_due_remaining,
1386 acctd_amount_due_remaining,
1387 amount_line_items_remaining,
1388 tax_remaining,
1389 freight_remaining,
1390 receivables_charges_remaining,
1391 last_updated_by,
1392 last_update_date,
1393 last_update_login) = (
1394 SELECT
1395 decode(ps2.amount_due_remaining + ra.amount_applied,0,'CL','OP'),
1396 decode(ps2.amount_due_remaining + ra.amount_applied,
1397 0,
1398 greatest(max(decode(ra2.customer_trx_id,
1399 p_customer_trx_id, ps2.gl_date,
1400 ra2.gl_date)),
1401 max(decode(adj2.status,
1402 'A', adj2.gl_date,
1403 ps2.gl_date))),
1404 to_date('31-12-4712','DD-MM-YYYY')),--Added default date 31-12-4712 as per Bug:5514315
1405 decode(ps2.amount_due_remaining + ra.amount_applied,
1406 0,
1407 greatest(max(decode(ra2.customer_trx_id,
1408 p_customer_trx_id, ps2.trx_date,
1409 ra2.apply_date)),
1410 max(decode(adj2.status,
1411 'A', adj2.apply_date,
1412 ps2.trx_date))),
1413 to_date('31-12-4712','DD-MM-YYYY')),--Added default date 31-12-4712 as per Bug:5514315
1414 nvl(ps2.amount_credited, 0) + ra.amount_applied,
1415 ps2.amount_due_remaining + ra.amount_applied,
1416 ps2.acctd_amount_due_remaining + nvl(ra.acctd_amount_applied_to, 0),
1417 nvl(ps2.amount_line_items_remaining, 0) + nvl(ra.line_applied, 0),
1418 nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied,0),
1419 nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0),
1420 nvl(ps2.receivables_charges_remaining, 0) +
1421 nvl(ra.receivables_charges_applied, 0),
1422 p_profile_info.user_id,
1423 trunc(sysdate),
1424 p_profile_info.conc_login_id
1425 FROM
1426 ar_receivable_applications ra,
1427 ar_payment_schedules ps2,
1428 ar_adjustments adj2,
1429 ar_receivable_applications ra2
1430 WHERE ra.customer_trx_id = p_customer_trx_id
1431 and ra.status||'' = 'APP'
1432 and ra.applied_payment_schedule_id = ps2.payment_schedule_id
1433 and ps2.payment_schedule_id =ps.payment_schedule_id
1434 and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
1435 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
1436 and nvl(ra2.confirmed_flag,'Y')= 'Y'
1437 GROUP BY
1438 ps2.payment_schedule_id,
1439 ra2.applied_payment_schedule_id,
1440 adj2.payment_schedule_id,
1441 ps2.amount_due_remaining,
1442 ra.amount_applied,
1443 ps2.gl_date,
1444 ps2.trx_date,
1445 ps2.amount_credited,
1446 ps2.acctd_amount_due_remaining,
1447 ra.acctd_amount_applied_to,
1448 ps2.amount_line_items_remaining,
1449 ra.line_applied,
1450 ps2.tax_remaining,
1451 ra.tax_applied,
1452 ps2.freight_remaining,
1453 ra.freight_applied,
1454 ps2.receivables_charges_remaining,
1455 ra.receivables_charges_applied)
1456 WHERE ps.payment_schedule_id in
1457 (
1458 SELECT ra3.applied_payment_schedule_id
1459 FROM ar_receivable_applications ra3
1460 WHERE ra3.customer_trx_id = p_customer_trx_id
1461 and ra3.status='APP'
1462 )
1463 RETURNING ps.payment_schedule_id
1464 BULK COLLECT INTO l_ar_ps_key_value_list;
1465
1466 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1467
1468 /*---------------------------------+
1469 | Calling central MRC library |
1470 | for MRC Integration |
1471 +---------------------------------*/
1472
1473 ar_mrc_engine.maintain_mrc_data(
1474 p_event_mode => 'UPDATE',
1475 p_table_name => 'AR_PAYMENT_SCHEDULES',
1476 p_mode => 'BATCH',
1477 p_key_value_list => l_ar_ps_key_value_list);
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 debug( 'EXCEPTION: Error executing update stmt',
1482 MSG_LEVEL_BASIC );
1483 RAISE;
1484 END;
1485
1486
1487
1488 print_fcn_label( 'arp_maintain_ps.reverse_cm_effect()-' );
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 debug( 'EXCEPTION: arp_maintain_ps.reverse_cm_effect()',
1493 MSG_LEVEL_BASIC );
1494 RAISE;
1495
1496 END reverse_cm_effect;
1497
1498
1499 ----------------------------------------------------------------------------
1500 PROCEDURE update_payment_schedule(
1501 p_system_info IN arp_trx_global.system_info_rec_type,
1502 p_profile_info IN arp_trx_global.profile_rec_type,
1503 p_control IN control_rec_type
1504 ) IS
1505
1506 --BUG#5324129
1507 CURSOR del_app(p_app_id IN NUMBER) IS
1508 select app.receivable_application_id app_id,
1509 app.customer_trx_id trx_id
1510 from ar_receivable_applications app
1511 where app.applied_payment_schedule_id = p_control.payment_schedule_id --inv ps
1512 and app.customer_trx_id = p_control.customer_trx_id --cm trx id
1513 and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
1514 and app.reversal_gl_date IS NULL
1515 and app.receivable_application_id = p_app_id
1516 and exists (select 'x'
1517 from ar_distributions ard
1518 where ard.source_table = 'RA'
1519 and ard.source_id = app.receivable_application_id); --delete only necessary records
1520
1521 --BUG#5324129
1522 CURSOR cre_app(p_app_id IN NUMBER) IS
1523 select app.receivable_application_id app_id,
1524 app.customer_trx_id trx_id
1525 from ar_receivable_applications app
1526 where app.applied_payment_schedule_id = p_control.payment_schedule_id
1527 and app.customer_trx_id = p_control.customer_trx_id
1528 and nvl(app.confirmed_flag,'Y') = 'Y'
1529 and app.reversal_gl_date IS NULL
1530 and app.receivable_application_id = p_app_id
1531 and not exists (select 'x'
1532 from ar_distributions ard
1533 where ard.source_table = 'RA'
1534 and ard.source_id = app.receivable_application_id);
1535
1536 --BUG#5324129
1537 CURSOR cu_posted IS
1538 select *
1539 from ar_receivable_applications
1540 where applied_payment_schedule_id = p_control.payment_schedule_id
1541 and customer_trx_id = p_control.customer_trx_id
1542 and nvl(confirmed_flag,'Y') = 'Y'
1543 and reversal_gl_date IS NULL;
1544
1545
1546 CURSOR get_app_id(p_app_id IN NUMBER) IS
1547 select app.receivable_application_id,
1548 app.amount_applied
1549 from ar_receivable_applications app
1550 where app.applied_payment_schedule_id = p_control.payment_schedule_id
1551 and app.customer_trx_id = p_control.customer_trx_id
1552 and app.receivable_application_id = p_app_id;
1553
1554
1555 l_ae_doc_rec ae_doc_rec_type;
1556
1557 l_cm_adr NUMBER;
1558 l_cm_acctd_adr NUMBER;
1559 l_cm_rate NUMBER;
1560 l_new_amount_applied NUMBER;
1561 l_new_acctd_amt_applied_from NUMBER;
1562 l_new_acctd_amt_applied_to NUMBER;
1563 l_inv_adr NUMBER;
1564 l_inv_acctd_adr NUMBER;
1565 l_new_inv_acctd_adr NUMBER;
1566 l_inv_rate NUMBER;
1567
1568 l_dummy NUMBER;
1569 l_foreign_transaction VARCHAR2(1) := NO;
1570
1571 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1572
1573 --BUG#5324129
1574 l_app_id NUMBER;
1575 l_amount_applied NUMBER;
1576 l_ra_id NUMBER;
1577 l_del_app_rec del_app%ROWTYPE;
1578 l_cre_app_rec cre_app%ROWTYPE;
1579 old_rec_app ar_receivable_Applications%ROWTYPE;
1580 ins_ra_rec ar_receivable_Applications%ROWTYPE;
1581 no_app_found EXCEPTION;
1582
1583 BEGIN
1584
1585 print_fcn_label( 'arp_maintain_ps.update_payment_schedule()+' );
1586
1587 --BUG#5324129
1588 OPEN cu_posted;
1589 FETCH cu_posted INTO old_rec_app;
1590 IF cu_posted%NOTFOUND THEN
1591 RAISE no_app_found;
1592 END IF;
1593 CLOSE cu_posted;
1594
1595 arp_standard.debug(' old_rec_app.receivable_application_id:'||old_rec_app.receivable_application_id);
1596 arp_standard.debug(' old_rec_app.posting_control_id:'||old_rec_app.posting_control_id);
1597
1598
1599 BEGIN
1600
1601 SELECT
1602 ps_cm.amount_due_remaining - ra.amount_applied,
1603 ps_cm.acctd_amount_due_remaining - ra.acctd_amount_applied_from,
1604 ps_cm.exchange_rate,
1605 -( p_control.line_amount +
1606 p_control.tax_amount +
1607 p_control.freight_amount +
1608 p_control.charge_amount ),
1609 ps_inv.amount_due_remaining + ra.amount_applied,
1610 ps_inv.acctd_amount_due_remaining + ra.acctd_amount_applied_to,
1611 ps_inv.exchange_rate
1612 INTO
1613 l_cm_adr,
1614 l_cm_acctd_adr,
1615 l_cm_rate,
1616 l_new_amount_applied,
1617 l_inv_adr,
1618 l_inv_acctd_adr,
1619 l_inv_rate
1620 FROM
1621 ar_payment_schedules ps_cm,
1622 ar_payment_schedules ps_inv,
1623 ar_receivable_applications ra
1624 WHERE p_system_info.base_currency <> ps_inv.invoice_currency_code
1625 and ra.applied_payment_schedule_id = ps_inv.payment_schedule_id
1626 and ps_inv.payment_schedule_id = p_control.payment_schedule_id
1627 and ra.payment_schedule_id = ps_cm.payment_schedule_id
1628 and ps_cm.customer_trx_id = p_control.customer_trx_id
1629 and ra.reversal_gl_date IS NULL
1630 and ra.receivable_application_id = old_rec_app.receivable_application_id;
1631
1632 l_foreign_transaction := YES;
1633
1634
1635 EXCEPTION
1636 WHEN NO_DATA_FOUND THEN
1637 l_foreign_transaction := NO;
1638
1639 WHEN OTHERS THEN
1640 debug( 'EXCEPTION: Error executing select stmt',
1641 MSG_LEVEL_BASIC );
1642 RAISE;
1643 END;
1644
1645 IF( l_foreign_transaction = YES ) THEN
1646
1647 ------------------------------------------------------------------
1648 -- Compute accounted amounts for ps and applications
1649 ------------------------------------------------------------------
1650 arp_util.calc_acctd_amount(
1651 p_system_info.base_currency,
1652 NULL, -- precision
1653 NULL, -- mau
1654 l_inv_rate,
1655 '-', -- type
1656 l_inv_adr, -- master_from
1657 l_inv_acctd_adr, -- acctd_master_from
1658 l_new_amount_applied, -- detail
1659 l_dummy, -- master_to
1660 l_new_inv_acctd_adr, -- acctd_master_to
1661 l_new_acctd_amt_applied_to -- acctd_detail
1662 );
1663
1664 arp_util.calc_acctd_amount(
1665 p_system_info.base_currency,
1666 NULL, -- precision
1667 NULL, -- mau
1668 l_cm_rate,
1669 '+', -- type
1670 l_cm_adr, -- master_from
1671 l_cm_acctd_adr, -- acctd_master_from
1672 l_new_amount_applied, -- detail
1673 l_dummy, -- master_to
1674 l_dummy, -- acctd_master_to
1675 l_new_acctd_amt_applied_from -- acctd_detail
1676 );
1677
1678 END IF;
1679
1680 BEGIN
1681 ----------------------------------------------------------------
1682 -- Reverse the origial CM application amounts and use the new
1683 -- amounts passed in for the invoice's payment schedule.
1684 ----------------------------------------------------------------
1685 UPDATE ar_payment_schedules ps
1686 SET (
1687 ps.status,
1688 ps.gl_date_closed,
1689 ps.actual_date_closed,
1690 ps.amount_credited,
1691 ps.amount_due_remaining,
1692 ps.acctd_amount_due_remaining,
1693 ps.amount_line_items_remaining,
1694 ps.tax_remaining,
1695 ps.freight_remaining,
1696 ps.receivables_charges_remaining,
1697 ps.last_updated_by,
1698 ps.last_update_date,
1699 ps.last_update_login) = (
1700 SELECT
1701 decode( ps2.amount_due_remaining + ra.amount_applied +
1702 (p_control.line_amount +
1703 p_control.tax_amount +
1704 p_control.freight_amount +
1705 p_control.charge_amount ),
1706 0, 'CL', 'OP'),
1707 decode( ps2.amount_due_remaining + ra.amount_applied +
1708 (p_control.line_amount +
1709 p_control.tax_amount +
1710 p_control.freight_amount +
1711 p_control.charge_amount ),
1712 0,
1713 greatest(max(ra2.gl_date), max(decode(adj2.status,
1714 'A', adj2.gl_date,
1715 ps2.gl_date))),
1716 ''),
1717 decode(ps2.amount_due_remaining + ra.amount_applied +
1718 (p_control.line_amount +
1719 p_control.tax_amount +
1720 p_control.freight_amount +
1721 p_control.charge_amount ),
1722 0, greatest(max(ra2.apply_date),
1723 max(decode(adj2.status,
1724 'A', adj2.apply_date,
1725 ps2.trx_date))),
1726 ''),
1727 nvl(ps2.amount_credited, 0) + ra.amount_applied +
1728 (p_control.line_amount +
1729 p_control.tax_amount +
1730 p_control.freight_amount +
1731 p_control.charge_amount ),
1732 ps2.amount_due_remaining + ra.amount_applied +
1733 (p_control.line_amount +
1734 p_control.tax_amount +
1735 p_control.freight_amount +
1736 p_control.charge_amount ),
1737 decode(l_foreign_transaction,
1738 'N',
1739 ps2.amount_due_remaining + ra.amount_applied +
1740 (p_control.line_amount +
1741 p_control.tax_amount +
1742 p_control.freight_amount +
1743 p_control.charge_amount ),
1744 'Y', to_number(nvl(l_new_inv_acctd_adr, 0))),
1745 nvl(ps2.amount_line_items_remaining, 0) + nvl(ra.line_applied, 0) +
1746 p_control.line_amount,
1747 nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied, 0) +
1748 p_control.tax_amount,
1749 nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0) +
1750 p_control.freight_amount,
1751 nvl(ps2.receivables_charges_remaining, 0) +
1752 nvl(ra.receivables_charges_applied, 0) +
1753 p_control.charge_amount,
1754 p_profile_info.user_id,
1755 trunc(sysdate),
1756 p_profile_info.conc_login_id
1757 FROM
1758 ar_receivable_applications ra,
1759 ar_payment_schedules ps2,
1760 ar_receivable_applications ra2,
1761 ar_adjustments adj2
1762 WHERE ra.customer_trx_id = p_control.customer_trx_id
1763 and ra.status||'' = 'APP'
1764 and ra.reversal_gl_date IS NULL
1765 and ra.applied_payment_schedule_id = ps2.payment_schedule_id
1766 and ps.payment_schedule_id = ps2.payment_schedule_id
1767 and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
1768 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
1769 and ra2.reversal_gl_date IS NULL
1770 and nvl(ra2.confirmed_flag, 'Y') = 'Y'
1771 and ra.receivable_application_id = old_rec_app.receivable_application_id
1772 GROUP BY
1773 ps2.payment_schedule_id,
1774 ra2.applied_payment_schedule_id,
1775 adj2.payment_schedule_id,
1776 ps2.amount_due_remaining,
1777 ra.amount_applied,
1778 ps2.gl_date,
1779 ps2.trx_date,
1780 ps2.amount_credited,
1781 ps2.acctd_amount_due_remaining,
1782 ra.acctd_amount_applied_to,
1783 ps2.amount_line_items_remaining,
1784 ra.line_applied,
1785 ps2.tax_remaining,
1786 ra.tax_applied,
1787 ps2.freight_remaining,
1788 ra.freight_applied,
1789 ps2.receivables_charges_remaining,
1790 ra.receivables_charges_applied,
1791 ps2.exchange_rate)
1792 WHERE ps.payment_schedule_id in
1793 (
1794 SELECT ra3.applied_payment_schedule_id
1795 FROM ar_receivable_applications ra3
1796 WHERE ra3.customer_trx_id = p_control.customer_trx_id
1797 and ra3.status = 'APP'
1798 and ra3.applied_payment_schedule_id = p_control.payment_schedule_id
1799 and ra3.reversal_gl_date IS NULL
1800 )
1801 RETURNING payment_schedule_id
1802 BULK COLLECT INTO l_ar_ps_key_value_list;
1803
1804 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1805
1806 /*---------------------------------+
1807 | Calling central MRC library |
1808 | for MRC Integration |
1809 +---------------------------------*/
1810
1811 ar_mrc_engine.maintain_mrc_data(
1812 p_event_mode => 'UPDATE',
1813 p_table_name => 'AR_PAYMENT_SCHEDULES',
1814 p_mode => 'BATCH',
1815 p_key_value_list => l_ar_ps_key_value_list);
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 debug( 'EXCEPTION: Error executing update stmt #1',
1820 MSG_LEVEL_BASIC );
1821 RAISE;
1822 END;
1823
1824
1825 BEGIN
1826 ----------------------------------------------------------------
1827 -- Release 11.5, plug in changes delete the accounting for the
1828 -- updated CM and then recreate the same. Direct updates are not
1829 -- done for child accounting records.
1830 ----------------------------------------------------------------
1831 IF old_rec_app.posting_control_id = -3 THEN
1832
1833
1834 arp_standard.debug('Path Update CM RA and recreate distributions');
1835 arp_standard.debug('1 Delete current RA distributions');
1836
1837 OPEN del_app(old_rec_app.receivable_application_id);
1838 LOOP
1839 FETCH del_app INTO l_del_app_rec;
1840 EXIT WHEN del_app%NOTFOUND;
1841 --
1842 --Release 11.5 VAT changes, delete accounting for Applications
1843 --
1844 arp_standard.debug(' Current distributions exist delete distributions +');
1845 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
1846 l_ae_doc_rec.document_id := l_del_app_rec.trx_id;
1847 l_ae_doc_rec.accounting_entity_level := 'ONE';
1848 l_ae_doc_rec.source_table := 'RA';
1849 l_ae_doc_rec.source_id := l_del_app_rec.app_id;
1850 l_ae_doc_rec.source_id_old := '';
1851 l_ae_doc_rec.other_flag := '';
1852 --Bug 1329091 - PS is updated before Accounting Engine Call
1853
1854 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1855
1856 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1857 arp_standard.debug(' delete distributions -');
1858
1859 END LOOP;
1860 CLOSE del_app;
1861
1862
1863 ----------------------------------------------------------------
1864 -- Update the CM application record to the correct amount
1865 ----------------------------------------------------------------
1866 arp_standard.debug('Update the CM app ra_id '|| old_rec_app.receivable_application_id || '+');
1867
1868 UPDATE ar_receivable_applications ra
1869 SET
1870 acctd_amount_applied_from =
1871 decode(l_foreign_transaction,
1872 'N',
1873 -( p_control.line_amount +
1874 p_control.tax_amount +
1875 p_control.freight_amount +
1876 p_control.charge_amount ),
1877 'Y', to_number( nvl(l_new_acctd_amt_applied_from, 0) ) ),
1878 acctd_amount_applied_to =
1879 decode(l_foreign_transaction,
1880 'N',
1881 -(p_control.line_amount +
1882 p_control.tax_amount +
1883 p_control.freight_amount +
1884 p_control.charge_amount),
1885 'Y', to_number(nvl(l_new_acctd_amt_applied_to, 0))),
1886 amount_applied =
1887 -(p_control.line_amount +
1888 p_control.tax_amount +
1889 p_control.freight_amount +
1890 p_control.charge_amount),
1891 line_applied = -p_control.line_amount,
1892 tax_applied = -p_control.tax_amount,
1893 freight_applied = -p_control.freight_amount,
1894 receivables_charges_applied = -p_control.charge_amount,
1895 last_updated_by = p_profile_info.user_id,
1896 last_update_date = trunc(sysdate),
1897 last_update_login = p_profile_info.conc_login_id
1898 WHERE ra.applied_payment_schedule_id = p_control.payment_schedule_id
1899 and ra.customer_trx_id = p_control.customer_trx_id
1900 and ra.reversal_gl_date IS NULL
1901 and ra.receivable_application_id = old_rec_app.receivable_application_id;
1902
1903 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
1904
1905
1906 arp_standard.debug('MRC plugins ');
1907 OPEN get_app_id(old_rec_app.receivable_application_id);
1908 LOOP
1909 FETCH get_app_id INTO l_app_id, l_amount_applied;
1910 EXIT WHEN get_app_id%NOTFOUND;
1911 -- Call mrc engine to process update:
1912 ar_mrc_engine3.update_cm_application(
1913 l_app_id,
1914 p_control.payment_schedule_id, /* p_app_ps_id */
1915 p_control.customer_trx_id, /* p_ct_id */
1916 l_amount_applied);
1917 END LOOP;
1918 CLOSE get_app_id;
1919
1920 arp_standard.debug('End update the CM app ra_id');
1921 ----------------------------------------------------------------
1922 -- Release 11.5, plug in changes recreate the accounting for the
1923 -- updated CM once parent records have been updated.Only one APP
1924 -- for the credit memo should get updated in previous statement.
1925 ----------------------------------------------------------------
1926 arp_standard.debug('Creation of new distributions');
1927 OPEN cre_app(old_rec_app.receivable_application_id);
1928 LOOP
1929 FETCH cre_app INTO l_cre_app_rec;
1930 EXIT WHEN cre_app%NOTFOUND;
1931 --
1932 --Release 11.5 VAT changes, recreate accounting for Applications
1933 --
1934 arp_standard.debug(' recreate distributions +:'||l_cre_app_rec.app_id);
1935 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
1936 l_ae_doc_rec.document_id := l_cre_app_rec.trx_id;
1937 l_ae_doc_rec.accounting_entity_level := 'ONE';
1938 l_ae_doc_rec.source_table := 'RA';
1939 l_ae_doc_rec.source_id := l_cre_app_rec.app_id;
1940 l_ae_doc_rec.source_id_old := '';
1941 l_ae_doc_rec.other_flag := '';
1942
1943 --Bug 1329091 - PS is updated before Accounting Engine Call
1944
1945 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1946 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1947
1948 /*bug-6976549*/
1949 arp_balance_check.CHECK_APPLN_BALANCE(l_cre_app_rec.app_id,
1950 NULL,
1951 'N');
1952
1953 arp_standard.debug(' recreate distributions -');
1954 END LOOP;
1955 CLOSE cre_app;
1956 arp_standard.debug('End Path Update CM RA and recreate distributions');
1957
1958 ELSE
1959
1960 arp_standard.debug('Path Reverse CM RA and create new CM RA');
1961 --BUG#5324129
1962 -- Insert RA to reverse the posted one
1963 ins_ra_rec := old_rec_app;
1964 ins_ra_rec.acctd_amount_applied_from := -1 * old_rec_app.acctd_amount_applied_from;
1965 ins_ra_rec.amount_applied := -1 * old_rec_app.amount_applied;
1966 ins_ra_rec.amount_applied_from := -1 * old_rec_app.amount_applied_from;
1967 ins_ra_rec.trans_to_receipt_rate := old_rec_app.trans_to_receipt_rate;
1968 ins_ra_rec.application_type := old_rec_app.application_type;
1969 ins_ra_rec.apply_date := TRUNC(SYSDATE);
1970 ins_ra_rec.code_combination_id := old_rec_app.code_combination_id;
1971 ins_ra_rec.display := 'N';
1972 ins_ra_rec.gl_date := TRUNC(SYSDATE);
1973 ins_ra_rec.payment_schedule_id := old_rec_app.payment_schedule_id;
1974 ins_ra_rec.set_of_books_id := old_rec_app.set_of_books_id;
1975 ins_ra_rec.status := old_rec_app.status;
1976 ins_ra_rec.acctd_amount_applied_to := -1 * old_rec_app.acctd_amount_applied_to;
1977 ins_ra_rec.acctd_earned_discount_taken := -1 * old_rec_app.acctd_earned_discount_taken;
1978 ins_ra_rec.acctd_unearned_discount_taken := -1 * old_rec_app.acctd_unearned_discount_taken;
1979 ins_ra_rec.applied_customer_trx_id := old_rec_app.applied_customer_trx_id;
1980 ins_ra_rec.applied_customer_trx_line_id := old_rec_app.applied_customer_trx_line_id;
1981 ins_ra_rec.applied_payment_schedule_id := old_rec_app.applied_payment_schedule_id;
1982 ins_ra_rec.cash_receipt_id := old_rec_app.cash_receipt_id;
1983 ins_ra_rec.comments := old_rec_app.comments;
1984 ins_ra_rec.confirmed_flag := old_rec_app.confirmed_flag;
1985 ins_ra_rec.customer_trx_id := old_rec_app.customer_trx_id;
1986 ins_ra_rec.days_late := old_rec_app.days_late;
1987 ins_ra_rec.earned_discount_taken := -1 * old_rec_app.earned_discount_taken;
1988 ins_ra_rec.freight_applied := -1 * old_rec_app.freight_applied;
1989 ins_ra_rec.gl_posted_date := NULL;
1990 ins_ra_rec.line_applied := -1 * old_rec_app.line_applied;
1991 ins_ra_rec.on_account_customer := old_rec_app.on_account_customer;
1992 ins_ra_rec.postable := old_rec_app.postable;
1993 ins_ra_rec.posting_control_id := -3;
1994 ins_ra_rec.program_application_id := NULL;
1995 ins_ra_rec.program_id := NULL;
1996 ins_ra_rec.program_update_date := NULL;
1997 ins_ra_rec.receivables_charges_applied := -1 * old_rec_app.receivables_charges_applied;
1998 ins_ra_rec.receivables_trx_id := old_rec_app.receivables_trx_id;
1999 ins_ra_rec.request_id := NULL;
2000 ins_ra_rec.tax_applied := -1 * old_rec_app.tax_applied;
2001 ins_ra_rec.unearned_discount_taken := -1 * old_rec_app.unearned_discount_taken;
2002 ins_ra_rec.unearned_discount_ccid := old_rec_app.unearned_discount_ccid;
2003 ins_ra_rec.earned_discount_ccid := old_rec_app.earned_discount_ccid;
2004 ins_ra_rec.ussgl_transaction_code := old_rec_app.ussgl_transaction_code;
2005 ins_ra_rec.ussgl_transaction_code_context := old_rec_app.ussgl_transaction_code_context;
2006 ins_ra_rec.reversal_gl_date := TRUNC(SYSDATE);
2007 ins_ra_rec.LINE_EDISCOUNTED := -1 * old_rec_app.LINE_EDISCOUNTED;
2008 ins_ra_rec.LINE_UEDISCOUNTED := -1 * old_rec_app.LINE_UEDISCOUNTED;
2009 ins_ra_rec.TAX_EDISCOUNTED := -1 * old_rec_app.TAX_EDISCOUNTED;
2010 ins_ra_rec.TAX_UEDISCOUNTED := -1 * old_rec_app.TAX_UEDISCOUNTED;
2011 ins_ra_rec.FREIGHT_EDISCOUNTED := -1 * old_rec_app.FREIGHT_EDISCOUNTED;
2012 ins_ra_rec.FREIGHT_UEDISCOUNTED := -1 * old_rec_app.FREIGHT_UEDISCOUNTED;
2013 ins_ra_rec.CHARGES_EDISCOUNTED := -1 * old_rec_app.CHARGES_EDISCOUNTED;
2014 ins_ra_rec.CHARGES_UEDISCOUNTED := -1 * old_rec_app.CHARGES_UEDISCOUNTED;
2015 ins_ra_rec.APPLICATION_REF_TYPE := old_rec_app.APPLICATION_REF_TYPE;
2016 ins_ra_rec.application_ref_id := old_rec_app.application_ref_id;
2017 ins_ra_rec.application_ref_num := old_rec_app.application_ref_num;
2018 ins_ra_rec.application_ref_reason := old_rec_app.application_ref_reason;
2019 ins_ra_rec.customer_reference := old_rec_app.customer_reference;
2020 ins_ra_rec.link_to_customer_trx_id := old_rec_app.link_to_customer_trx_id;
2021 ins_ra_rec.customer_reason := old_rec_app.customer_reason;
2022 ins_ra_rec.applied_rec_app_id := old_rec_app.applied_rec_app_id;
2023 ins_ra_rec.application_rule := 'CREDIT MEMO REVERSAL';
2024 ins_ra_rec.receivable_application_id := NULL;
2025
2026 arp_app_pkg.insert_p( ins_ra_rec, l_ra_id );
2027 arp_standard.debug('Reverse application inserted ra_id :'||l_ra_id);
2028
2029 --Update the reversal app record reversal_gl_date
2030 UPDATE ar_receivable_applications
2031 SET reversal_gl_date = TRUNC(SYSDATE),
2032 display = 'N'
2033 WHERE receivable_application_id = old_rec_app.receivable_application_id;
2034
2035 arp_standard.debug('The old ra record '|| old_rec_app.receivable_application_id ||' reversal_gl_date updated ');
2036
2037 -- MRC cm app record inserted
2038 -- need to call mrc engine to process rec apps row
2039 arp_standard.debug('Plugin MRC call for ra reversal ');
2040 ar_mrc_engine3.reversal_insert_oppos_ra_recs(
2041 ins_ra_rec,
2042 old_rec_app.receivable_application_id,
2043 l_ra_id);
2044 -----------------
2045 -- Create reversal distributions
2046 -----------------
2047 arp_standard.debug('create the distribution for reversal app :'||l_ra_id);
2048 l_ae_doc_rec.source_table := 'RA';
2049 l_ae_doc_rec.accounting_entity_level := 'ONE';
2050 l_ae_doc_rec.other_flag := 'REVERSE';
2051 l_ae_doc_rec.source_id_old := old_rec_app.receivable_application_id;
2052 l_ae_doc_rec.source_id := l_ra_id;
2053 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2054
2055 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2056 /*bug-6976549*/
2057 arp_balance_check.CHECK_APPLN_BALANCE(l_ra_id,
2058 NULL,
2059 'N');
2060
2061
2062 ----------------------------------------------------------------
2063 -- create the new CM application record to the correct amount
2064 ----------------------------------------------------------------
2065 arp_standard.debug('create the new CM APP record ');
2066
2067 ins_ra_rec := old_rec_app;
2068 IF l_foreign_transaction = 'N' THEN
2069 ins_ra_rec.acctd_amount_applied_from := -( p_control.line_amount +
2070 p_control.tax_amount +
2071 p_control.freight_amount +
2072 p_control.charge_amount );
2073 ELSE
2074 ins_ra_rec.acctd_amount_applied_from := to_number( nvl(l_new_acctd_amt_applied_from, 0));
2075 END IF;
2076
2077 IF l_foreign_transaction = 'N' THEN
2078 ins_ra_rec.acctd_amount_applied_to := -(p_control.line_amount +
2079 p_control.tax_amount +
2080 p_control.freight_amount +
2081 p_control.charge_amount);
2082 ELSE
2083 ins_ra_rec.acctd_amount_applied_to := to_number(nvl(l_new_acctd_amt_applied_to, 0));
2084 END IF;
2085
2086 ins_ra_rec.amount_applied := -(p_control.line_amount +
2087 p_control.tax_amount +
2088 p_control.freight_amount +
2089 p_control.charge_amount);
2090 ins_ra_rec.line_applied := -p_control.line_amount;
2091 ins_ra_rec.tax_applied := -p_control.tax_amount;
2092 ins_ra_rec.freight_applied := -p_control.freight_amount;
2093 ins_ra_rec.receivables_charges_applied := -p_control.charge_amount;
2094 ins_ra_rec.posting_control_id := -3;
2095 ins_ra_rec.gl_posted_date := NULL;
2096 ins_ra_rec.reversal_gl_date := NULL;
2097 ins_ra_rec.gl_date := TRUNC(SYSDATE);
2098 ins_ra_rec.display := 'Y';
2099 ins_ra_rec.receivable_application_id := NULL;
2100
2101 arp_app_pkg.insert_p( ins_ra_rec, l_ra_id );
2102 arp_standard.debug('CM APP record created :'||l_ra_id);
2103
2104
2105 arp_standard.debug('MRC plugin call for the app record '||l_ra_id);
2106 ar_mrc_engine3.cm_application(
2107 p_cm_ps_id => old_rec_app.payment_schedule_id,
2108 p_invoice_ps_id => old_rec_app.applied_payment_schedule_id,
2109 p_inv_ra_rec => ins_ra_rec,
2110 p_ra_id => l_ra_id);
2111
2112 arp_standard.debug('Create the distributions for '||l_ra_id);
2113 -- Create the distributions
2114 l_ae_doc_rec.document_id := ins_ra_rec.customer_trx_id;
2115 l_ae_doc_rec.accounting_entity_level := 'ONE';
2116 l_ae_doc_rec.source_table := 'RA';
2117 l_ae_doc_rec.source_id := l_ra_id;
2118 l_ae_doc_rec.source_id_old := '';
2119 l_ae_doc_rec.other_flag := '';
2120 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2121
2122 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2123 /*bug-6976549*/
2124 arp_balance_check.CHECK_APPLN_BALANCE(l_ra_id,
2125 NULL,
2126 'N');
2127
2128 END IF;
2129
2130 EXCEPTION
2131 WHEN no_app_found THEN
2132 arp_standard.debug('No app found');
2133 WHEN OTHERS THEN
2134 debug( 'EXCEPTION: Error executing update stmt #2',
2135 MSG_LEVEL_BASIC );
2136 RAISE;
2137 END;
2138
2139 print_fcn_label( 'arp_maintain_ps.update_payment_schedule()-' );
2140
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 debug( 'EXCEPTION: arp_maintain_ps.update_payment_schedule()',
2144 MSG_LEVEL_BASIC );
2145 RAISE;
2146
2147 END update_payment_schedule;
2148
2149
2150 ----------------------------------------------------------------------------
2151 PROCEDURE update_adjustments(
2152 p_system_info IN arp_trx_global.system_info_rec_type,
2153 p_profile_info IN arp_trx_global.profile_rec_type,
2154 p_control IN control_rec_type
2155 ) IS
2156
2157 CURSOR del_app IS
2158 select app.receivable_application_id app_id,
2159 app.customer_trx_id trx_id
2160 from ar_receivable_applications app
2161 where app.applied_payment_schedule_id = p_control.payment_schedule_id
2162 and app.customer_trx_id = p_control.customer_trx_id
2163 and nvl(app.confirmed_flag,'Y') = 'Y' --accounting exists in ar_distributions only if confirmed
2164 and app.status = 'APP'
2165 and exists (select 'x'
2166 from ar_distributions ard
2167 where ard.source_table = 'RA'
2168 and ard.source_id = app.receivable_application_id); --delete only necessary records
2169
2170 l_rec_del_app del_app%ROWTYPE;
2171
2172 l_cm_adr NUMBER;
2173 l_cm_acctd_adr NUMBER;
2174 l_cm_rate NUMBER;
2175 l_inv_adr NUMBER;
2176 l_inv_acctd_adr NUMBER;
2177 l_inv_rate NUMBER;
2178
2179 l_new_inv_adr NUMBER;
2180 l_new_inv_acctd_adr NUMBER;
2181 l_new2_inv_acctd_adr NUMBER;
2182
2183 l_new_adj_amount NUMBER;
2184 l_new_adj_acctd_amount NUMBER;
2185 l_new_amount_applied NUMBER;
2186 l_new_acctd_amt_applied_from NUMBER;
2187 l_new_acctd_amt_applied_to NUMBER;
2188
2189 l_update_inv_adr NUMBER;
2190 l_update_inv_acctd_adr NUMBER;
2191 l_update_new_adj_amount NUMBER;
2192
2193 l_dummy NUMBER;
2194 l_foreign_transaction VARCHAR2(1) := NO;
2195 l_no_adjustments BOOLEAN;
2196
2197 l_doc_where_clause VARCHAR2(1000);
2198 /* VAT changes */
2199 l_ae_doc_rec ae_doc_rec_type;
2200 l_adjustment_id ar_adjustments.adjustment_id%type;
2201 l_ccid ar_adjustments.code_combination_id%type;
2202
2203 BEGIN
2204
2205 print_fcn_label( 'arp_maintain_ps.update_adjustments()+' );
2206
2207 BEGIN
2208
2209 --
2210 -- Determine if adjustments exist on child invoice
2211 --
2212 SELECT adj.adjustment_id
2213 INTO l_dummy
2214 FROM ar_adjustments adj
2215 WHERE adj.receivables_trx_id = -1
2216 and adj.customer_trx_id = p_control.previous_customer_trx_id
2217 and adj.subsequent_trx_id = p_control.customer_trx_id
2218 and rownum = 1;
2219
2220 l_no_adjustments := FALSE;
2221
2222 EXCEPTION
2223 WHEN NO_DATA_FOUND THEN
2224 l_no_adjustments := TRUE;
2225 WHEN OTHERS THEN
2226 debug( 'EXCEPTION: Error executing select stmt #1',
2227 MSG_LEVEL_BASIC );
2228 RAISE;
2229 END;
2230
2231 IF( l_no_adjustments ) THEN
2232
2233 ------------------------------------------------------------
2234 -- If there is no adjustment on the child invoice,
2235 -- call update_payment_schedule():
2236 -- Update the invoice payment schedule with new amounts,
2237 -- Update the CM application record with new amounts
2238 -----------------------------------------------------------
2239 update_payment_schedule(
2240 p_system_info,
2241 p_profile_info,
2242 p_control );
2243 RETURN;
2244
2245 END IF;
2246
2247
2248 BEGIN
2249
2250 SELECT
2251 /* reverse old cm app */
2252 ps_cm.amount_due_remaining - ra.amount_applied,
2253 ps_cm.acctd_amount_due_remaining - ra.acctd_amount_applied_from,
2254 ps_cm.exchange_rate,
2255 -( p_control.line_amount +
2256 p_control.tax_amount +
2257 p_control.freight_amount +
2258 p_control.charge_amount ),
2259 /* reverse old cm app */
2260 ps_inv.amount_due_remaining + ra.amount_applied,
2261 ps_inv.acctd_amount_due_remaining + ra.acctd_amount_applied_to,
2262 ps_inv.exchange_rate,
2263 (-ra.line_applied - p_control.line_amount)
2264 INTO
2265 l_cm_adr,
2266 l_cm_acctd_adr,
2267 l_cm_rate,
2268 l_new_amount_applied,
2269 l_inv_adr,
2270 l_inv_acctd_adr,
2271 l_inv_rate,
2272 l_new_adj_amount
2273 FROM
2274 ar_payment_schedules ps_cm,
2275 ar_payment_schedules ps_inv,
2276 ar_receivable_applications ra
2277 WHERE p_system_info.base_currency <> ps_inv.invoice_currency_code
2278 and ra.applied_payment_schedule_id = ps_inv.payment_schedule_id
2279 and ps_inv.payment_schedule_id = p_control.payment_schedule_id
2280 and ra.payment_schedule_id = ps_cm.payment_schedule_id
2281 and ps_cm.customer_trx_id = p_control.customer_trx_id;
2282
2283 l_foreign_transaction := YES;
2284
2285
2286 EXCEPTION
2287 WHEN NO_DATA_FOUND THEN
2288 l_foreign_transaction := NO;
2289 WHEN OTHERS THEN
2290 debug( 'EXCEPTION: Error executing select stmt #2',
2291 MSG_LEVEL_BASIC );
2292 RAISE;
2293
2294 END;
2295
2296 IF( l_foreign_transaction = YES ) THEN
2297
2298 ------------------------------------------------------------------
2299 -- Compute accounted amounts for ps and applications
2300 ------------------------------------------------------------------
2301
2302 ------------------------------------------------------------------
2303 -- Get New Acctd Amt Applied To and New Acctd Amt Due Remaining
2304 ------------------------------------------------------------------
2305 arp_util.calc_acctd_amount(
2306 p_system_info.base_currency,
2307 NULL, -- precision
2308 NULL, -- mau
2309 l_inv_rate,
2310 '-', -- type
2311 l_inv_adr, -- master_from
2312 l_inv_acctd_adr, -- acctd_master_from
2313 l_new_amount_applied, -- detail
2314 l_new_inv_adr, -- master_to
2315 l_new_inv_acctd_adr, -- acctd_master_to
2316 l_new_acctd_amt_applied_to -- acctd_detail
2317 );
2318
2319 ---------------------------------------------------------------
2320 -- Get New Acctd Amt Applied From
2321 ---------------------------------------------------------------
2322 arp_util.calc_acctd_amount(
2323 p_system_info.base_currency,
2324 NULL, -- precision
2325 NULL, -- mau
2326 l_cm_rate,
2327 '+', -- type
2328 l_cm_adr, -- master_from
2329 l_cm_acctd_adr, -- acctd_master_from
2330 l_new_amount_applied, -- detail
2331 l_dummy, -- master_to
2332 l_dummy, -- acctd_master_to
2333 l_new_acctd_amt_applied_from -- acctd_detail
2334 );
2335
2336 END IF;
2337
2338 BEGIN
2339
2340 ------------------------------------------------------------
2341 -- If no commitment adj exists by this CM, insert one,
2342 -- Otherwise, update the existing record with the new amount
2343 ------------------------------------------------------------
2344
2345 SELECT
2346 /* reverse adj effect */
2347 to_number(nvl(l_new_inv_adr, 0)) - adj.amount,
2348 to_number(nvl(l_new_inv_acctd_adr, 0)) - adj.acctd_amount,
2349 adj.amount + to_number(nvl(l_new_adj_amount, 0))
2350 INTO
2351 l_update_inv_adr,
2352 l_update_inv_acctd_adr,
2353 l_update_new_adj_amount
2354 FROM ar_adjustments adj
2355 WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
2356 and adj.subsequent_trx_id = p_control.customer_trx_id
2357 and adj.receivables_trx_id = -1
2358 and adj.payment_schedule_id = p_control.payment_schedule_id;
2359
2360 l_no_adjustments := FALSE;
2361
2362 EXCEPTION
2363 WHEN NO_DATA_FOUND THEN
2364 l_no_adjustments := TRUE;
2365 WHEN OTHERS THEN
2366 debug( 'EXCEPTION: Error executing select stmt #3',
2367 MSG_LEVEL_BASIC );
2368 RAISE;
2369
2370 END;
2371
2372
2373 IF( l_no_adjustments ) THEN
2374
2375 ------------------------------------------------------------------
2376 -- Insert the adjustment
2377 ------------------------------------------------------------------
2378
2379 IF( l_foreign_transaction = YES ) THEN
2380
2381 ------------------------------------------------------------------
2382 -- Get acctd amt for the adj
2383 ------------------------------------------------------------------
2384 arp_util.calc_acctd_amount(
2385 p_system_info.base_currency,
2386 NULL, -- precision
2387 NULL, -- mau
2388 l_inv_rate,
2389 '+', -- type
2390 l_new_inv_adr, -- master_from
2391 l_new_inv_acctd_adr, -- acctd_master_from
2392 l_new_adj_amount, -- detail
2393 l_dummy, -- master_to
2394 l_new2_inv_acctd_adr, -- acctd_master_to
2395 l_new_adj_acctd_amount -- acctd_detail
2396 );
2397
2398 END IF;
2399
2400 -- do the insert
2401 BEGIN
2402
2403 select ar_adjustments_s.nextval into l_adjustment_id
2404 from dual;
2405
2406 INSERT INTO ar_adjustments
2407 (
2408 created_by,
2409 creation_date,
2410 last_updated_by,
2411 last_update_date,
2412 last_update_login,
2413 set_of_books_id,
2414 receivables_trx_id,
2415 automatically_generated,
2416 type,
2417 adjustment_type,
2418 status,
2419 apply_date,
2420 adjustment_id,
2421 amount,
2422 gl_date,
2423 code_combination_id,
2424 customer_trx_id,
2425 payment_schedule_id,
2426 subsequent_trx_id,
2427 postable,
2428 acctd_amount,
2429 adjustment_number,
2430 created_from,
2431 posting_control_id
2432 ,org_id
2433 )
2434 SELECT
2435 p_profile_info.user_id,
2436 trunc(sysdate),
2437 p_profile_info.user_id,
2438 trunc(sysdate),
2439 p_profile_info.conc_login_id,
2440 adj2.set_of_books_id,
2441 -1,
2442 'Y',
2443 'LINE',
2444 'C',
2445 'A',
2446 adj2.apply_date,
2447 l_adjustment_id,
2448 nvl(-ra.line_applied, 0) - p_control.line_amount,
2449 adj2.gl_date,
2450 adj2.code_combination_id,
2451 p_control.previous_customer_trx_id,
2452 p_control.payment_schedule_id,
2453 p_control.customer_trx_id,
2454 adj2.postable,
2455 decode(l_foreign_transaction,
2456 'N', nvl(-ra.line_applied, 0) - p_control.line_amount,
2457 'Y', to_number(nvl(l_new_adj_acctd_amount, 0))),
2458 to_char(ar_adjustment_number_s.nextval),
2459 'ARAPSI',
2460 -3
2461 ,arp_standard.sysparm.org_id /* SSA changes anuj */
2462 FROM
2463 ar_adjustments adj2,
2464 ar_receivable_applications ra,
2465 ra_customer_trx ct
2466 WHERE adj2.receivables_trx_id= -1
2467 and adj2.customer_trx_id = p_control.previous_customer_trx_id
2468 and adj2.subsequent_trx_id = p_control.customer_trx_id
2469 and ra.customer_trx_id = adj2.subsequent_trx_id
2470 and ra.applied_payment_schedule_id = p_control.payment_schedule_id
2471 and ct.customer_trx_id = ra.applied_customer_trx_id
2472 and adj2.payment_schedule_id =
2473 (
2474 /* find an adjustment against the invoice by the CM */
2475 SELECT max(payment_schedule_id)
2476 FROM ar_adjustments adj3
2477 WHERE adj3.receivables_trx_id=-1
2478 and adj3.customer_trx_id = p_control.previous_customer_trx_id
2479 and adj3.subsequent_trx_id = p_control.customer_trx_id
2480 );
2481
2482 debug( SQL%ROWCOUNT||' row(s) inserted', MSG_LEVEL_DEBUG );
2483
2484 /*---------------------------------+
2485 | Calling central MRC library |
2486 | for MRC Integration |
2487 +---------------------------------*/
2488
2489 ar_mrc_engine.maintain_mrc_data(
2490 p_event_mode => 'INSERT',
2491 p_table_name => 'AR_ADJUSTMENTS',
2492 p_mode => 'SINGLE',
2493 p_key_value => l_adjustment_id
2494 );
2495
2496
2497 /* VAT changes: create acct entry */
2498 l_ae_doc_rec.document_type := 'ADJUSTMENT';
2499 l_ae_doc_rec.document_id := l_adjustment_id;
2500 l_ae_doc_rec.accounting_entity_level := 'ONE';
2501 l_ae_doc_rec.source_table := 'ADJ';
2502 l_ae_doc_rec.source_id := l_adjustment_id;
2503
2504 --Bug 1329091 - PS is updated before Accounting Engine Call
2505
2506 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2507
2508 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2509
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 debug( 'EXCEPTION: Error executing insert stmt',
2513 MSG_LEVEL_BASIC );
2514 RAISE;
2515
2516 END;
2517
2518 ------------------------------------------------------------------
2519 -- Fill in document number in the ar_adjustments table
2520 ------------------------------------------------------------------
2521
2522 l_doc_where_clause :=
2523 'WHERE customer_trx_id = :bind1 '|| CRLF ||
2524 'and subsequent_trx_id = :bind2 '|| CRLF ||
2525 'and receivables_trx_id = -1 and payment_schedule_id = :bind3 ';
2526
2527 ----------------------------------------------------------
2528 -- Update adjustments with document number
2529 ----------------------------------------------------------
2530 update_adj_document_number(
2531 p_system_info,
2532 p_profile_info,
2533 p_control.customer_trx_id,
2534 p_control.previous_customer_trx_id,
2535 p_control.customer_trx_id,
2536 p_control.payment_schedule_id,
2537 l_doc_where_clause );
2538
2539
2540 ELSE
2541
2542 ----------------------------------------------------------------
2543 -- Update old adjustment record with new adjustment amounts
2544 ----------------------------------------------------------------
2545
2546 IF( l_foreign_transaction = YES ) THEN
2547
2548 ------------------------------------------------------------------
2549 -- Get acctd amt for the adj
2550 ------------------------------------------------------------------
2551 arp_util.calc_acctd_amount(
2552 p_system_info.base_currency,
2553 NULL, -- precision
2554 NULL, -- mau
2555 l_inv_rate,
2556 '+', -- type
2557 l_update_inv_adr, -- master_from
2558 l_update_inv_acctd_adr, -- acctd_master_from
2559 l_update_new_adj_amount, -- detail
2560 l_dummy, -- master_to
2561 l_new2_inv_acctd_adr, -- acctd_master_to
2562 l_new_adj_acctd_amount -- acctd_detail
2563 );
2564
2565 END IF;
2566
2567 -- do the update
2568 DECLARE
2569 l_adj_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2570 BEGIN
2571
2572 UPDATE ar_adjustments adj
2573 SET
2574 (
2575 amount,
2576 acctd_amount,
2577 line_adjusted,
2578 last_updated_by,
2579 last_update_date,
2580 last_update_login
2581 ) =
2582 (
2583 SELECT
2584 nvl(adj.amount, 0) - ra.line_applied - p_control.line_amount,
2585 decode(l_foreign_transaction,
2586 'N', nvl(adj.amount, 0) - ra.line_applied -
2587 p_control.line_amount,
2588 'Y', to_number(nvl(l_new_adj_acctd_amount, 0))),
2589 nvl(adj.amount, 0) - ra.line_applied - p_control.line_amount,
2590 p_profile_info.user_id,
2591 trunc(sysdate),
2592 p_profile_info.conc_login_id
2593 FROM
2594 ar_receivable_applications ra,
2595 ra_customer_trx ct
2596 WHERE ra.customer_trx_id = p_control.customer_trx_id
2597 and ra.status||'' = 'APP'
2598 and ra.applied_payment_schedule_id = p_control.payment_schedule_id
2599 and ct.customer_trx_id = ra.applied_customer_trx_id
2600 )
2601 WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
2602 and adj.subsequent_trx_id = p_control.customer_trx_id
2603 and adj.receivables_trx_id = -1
2604 and adj.payment_schedule_id = p_control.payment_schedule_id
2605 RETURNING adj.adjustment_id
2606 BULK COLLECT INTO l_adj_key_value_list;
2607
2608 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
2609
2610 /*---------------------------------+
2611 | Calling central MRC library |
2612 | for MRC Integration |
2613 +---------------------------------*/
2614
2615 ar_mrc_engine.maintain_mrc_data(
2616 p_event_mode => 'UPDATE',
2617 p_table_name => 'AR_ADJUSTMENTS',
2618 p_mode => 'BATCH',
2619 p_key_value_list => l_adj_key_value_list);
2620
2621 EXCEPTION
2622 WHEN OTHERS THEN
2623 debug( 'EXCEPTION: Error executing update stmt',
2624 MSG_LEVEL_BASIC );
2625 RAISE;
2626
2627 END;
2628
2629 /* VAT changes: update accounting entry */
2630 SELECT adjustment_id
2631 INTO l_adjustment_id
2632 FROM ar_adjustments adj
2633 WHERE adj.customer_trx_id = p_control.previous_customer_trx_id
2634 and adj.subsequent_trx_id = p_control.customer_trx_id
2635 and adj.receivables_trx_id = -1
2636 and adj.payment_schedule_id = p_control.payment_schedule_id;
2637
2638 l_ae_doc_rec.document_type := 'ADJUSTMENT';
2639 l_ae_doc_rec.document_id := l_adjustment_id;
2640 l_ae_doc_rec.accounting_entity_level := 'ONE';
2641 l_ae_doc_rec.source_table := 'ADJ';
2642 l_ae_doc_rec.source_id := l_adjustment_id;
2643 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2644 l_ae_doc_rec.source_id_old := l_ccid;
2645 l_ae_doc_rec.other_flag := 'OVERRIDE';
2646
2647 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2648
2649 END IF;
2650
2651
2652 ----------------------------------------------------------------------
2653 -- We know that there's commitment adj done on this
2654 -- invoice, this means, the line remaining amt is not to be changed.
2655 -- Reverse the origial CM application amounts and use the new
2656 -- amounts passed in for the invoice's payment schedule.
2657 ----------------------------------------------------------------------
2658 DECLARE
2659 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2660
2661 BEGIN
2662
2663 UPDATE ar_payment_schedules ps
2664 SET (
2665 ps.status,
2666 ps.gl_date_closed,
2667 ps.actual_date_closed,
2668 ps.amount_credited,
2669 ps.amount_adjusted,
2670 ps.amount_due_remaining,
2671 ps.acctd_amount_due_remaining,
2672 ps.tax_remaining,
2673 ps.freight_remaining,
2674 ps.receivables_charges_remaining,
2675 ps.last_updated_by,
2676 ps.last_update_date,
2677 ps.last_update_login) = (
2678 SELECT
2679 decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2680 nvl(ra.freight_applied, 0) +
2681 nvl(ra.receivables_charges_applied, 0) +
2682 p_control.tax_amount +
2683 p_control.freight_amount +
2684 p_control.charge_amount,
2685 0, 'CL', 'OP'),
2686 decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2687 nvl(ra.freight_applied,0) +
2688 nvl(ra.receivables_charges_applied, 0) +
2689 p_control.tax_amount +
2690 p_control.freight_amount +
2691 p_control.charge_amount,
2692 0, greatest(max(ra2.gl_date),
2693 max(decode(adj2.status,
2694 'A', adj2.gl_date,
2695 ps2.gl_date))),
2696 ''),
2697 decode(ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2698 nvl(ra.freight_applied, 0) +
2699 nvl(ra.receivables_charges_applied,0) +
2700 p_control.tax_amount +
2701 p_control.freight_amount +
2702 p_control.charge_amount,
2703 0, greatest(max(ra2.apply_date),
2704 max(decode(adj2.status,
2705 'A', adj2.apply_date,
2706 ps2.trx_date))),
2707 ''),
2708 nvl(ps2.amount_credited, 0) + ra.amount_applied +
2709 (p_control.line_amount +
2710 p_control.tax_amount +
2711 p_control.freight_amount +
2712 p_control.charge_amount),
2713 nvl(ps2.amount_adjusted, 0) - ra.line_applied - p_control.line_amount,
2714 ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2715 nvl(ra.freight_applied, 0) +
2716 nvl(ra.receivables_charges_applied, 0) +
2717 p_control.tax_amount +
2718 p_control.freight_amount +
2719 p_control.charge_amount,
2720 decode(l_foreign_transaction,
2721 'N',
2722 ps2.amount_due_remaining + nvl(ra.tax_applied, 0) +
2723 nvl(ra.freight_applied, 0) +
2724 nvl(ra.receivables_charges_applied, 0) +
2725 p_control.tax_amount +
2726 p_control.freight_amount +
2727 p_control.charge_amount,
2728 'Y', to_number(nvl(l_new2_inv_acctd_adr, 0))),
2729 nvl(ps2.tax_remaining, 0) + nvl(ra.tax_applied, 0) +
2730 p_control.tax_amount,
2731 nvl(ps2.freight_remaining, 0) + nvl(ra.freight_applied, 0) +
2732 p_control.freight_amount,
2733 nvl(ps2.receivables_charges_remaining, 0) +
2734 nvl(ra.receivables_charges_applied,0) + p_control.charge_amount,
2735 p_profile_info.user_id,
2736 trunc(sysdate),
2737 p_profile_info.conc_login_id
2738 FROM
2739 ar_receivable_applications ra,
2740 ar_payment_schedules ps2,
2741 ar_receivable_applications ra2,
2742 ar_adjustments adj2
2743 WHERE ra.customer_trx_id = p_control.customer_trx_id
2744 and ra.status||'' = 'APP'
2745 and ra.applied_payment_schedule_id = ps2.payment_schedule_id
2746 and ps.payment_schedule_id = ps2.payment_schedule_id
2747 and ps2.payment_schedule_id = adj2.payment_schedule_id(+)
2748 and ps2.payment_schedule_id = ra2.applied_payment_schedule_id
2749 and nvl(ra2.confirmed_flag,'Y')='Y'
2750 GROUP BY
2751 ps2.payment_schedule_id,
2752 ra2.applied_payment_schedule_id,
2753 adj2.payment_schedule_id,
2754 ps2.amount_due_remaining,
2755 ra.amount_applied,
2756 ps2.gl_date,
2757 ps2.trx_date,
2758 ps2.amount_credited,
2759 ps2.amount_adjusted,
2760 ps2.acctd_amount_due_remaining,
2761 ra.acctd_amount_applied_to,
2762 ps2.amount_line_items_remaining,
2763 ra.line_applied,
2764 ps2.tax_remaining,
2765 ra.tax_applied,
2766 ps2.freight_remaining,
2767 ra.freight_applied,
2768 ps2.receivables_charges_remaining,
2769 ra.receivables_charges_applied,
2770 ps2.exchange_rate)
2771 WHERE ps.payment_schedule_id = p_control.payment_schedule_id
2772 RETURNING ps.payment_schedule_id
2773 BULK COLLECT INTO l_ar_ps_key_value_list;
2774
2775 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
2776
2777 /*---------------------------------+
2778 | Calling central MRC library |
2779 | for MRC Integration |
2780 +---------------------------------*/
2781
2782 ar_mrc_engine.maintain_mrc_data(
2783 p_event_mode => 'UPDATE',
2784 p_table_name => 'AR_PAYMENT_SCHEDULES',
2785 p_mode => 'BATCH',
2786 p_key_value_list => l_ar_ps_key_value_list);
2787
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 debug( 'EXCEPTION: Error executing update stmt',
2791 MSG_LEVEL_BASIC );
2792 RAISE;
2793
2794 END;
2795
2796
2797 ----------------------------------------------------------
2798 -- Update CM app with new line, frt, tax amount
2799 ----------------------------------------------------------
2800 DECLARE
2801 CURSOR get_app_id IS
2802 select app.receivable_application_id,
2803 app.amount_applied
2804 from ar_receivable_applications app
2805 where app.applied_payment_schedule_id = p_control.payment_schedule_id
2806 and app.customer_trx_id = p_control.customer_trx_id
2807 and app.status = 'APP';
2808
2809 BEGIN
2810
2811 FOR l_rec_del_app in del_app LOOP
2812 --
2813 --Release 11.5 VAT changes, delete accounting for Applications
2814 --
2815 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2816 l_ae_doc_rec.document_id := l_rec_del_app.trx_id;
2817 l_ae_doc_rec.accounting_entity_level := 'ONE';
2818 l_ae_doc_rec.source_table := 'RA';
2819 l_ae_doc_rec.source_id := l_rec_del_app.app_id;
2820 l_ae_doc_rec.source_id_old := '';
2821 l_ae_doc_rec.other_flag := '';
2822
2823 --Bug 1329091 - PS is updated before Accounting Engine Call
2824
2825 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2826
2827 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2828
2829 END LOOP;
2830
2831 UPDATE ar_receivable_applications ra
2832 SET
2833 acctd_amount_applied_from =
2834 decode(l_foreign_transaction,
2835 'N',
2836 -(p_control.line_amount +
2837 p_control.tax_amount +
2838 p_control.freight_amount +
2839 p_control.charge_amount),
2840 'Y', to_number(nvl(l_new_acctd_amt_applied_from, 0))),
2841 acctd_amount_applied_to =
2842 decode(l_foreign_transaction,
2843 'N',
2844 -(p_control.line_amount +
2845 p_control.tax_amount +
2846 p_control.freight_amount +
2847 p_control.charge_amount),
2848 'Y', to_number(nvl(l_new_acctd_amt_applied_to, 0))),
2849 amount_applied =
2850 -(p_control.line_amount +
2851 p_control.tax_amount +
2852 p_control.freight_amount +
2853 p_control.charge_amount),
2854 line_applied = -to_number(p_control.line_amount),
2855 tax_applied = -to_number(p_control.tax_amount),
2856 freight_applied = -to_number(p_control.freight_amount),
2857 receivables_charges_applied = -to_number(p_control.charge_amount),
2858 last_updated_by = p_profile_info.user_id,
2859 last_update_date = trunc(sysdate),
2860 last_update_login = p_profile_info.conc_login_id
2861 WHERE ra.applied_payment_schedule_id = p_control.payment_schedule_id
2862 and ra.status||'' = 'APP'
2863 and ra.customer_trx_id = p_control.customer_trx_id;
2864
2865 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
2866
2867 FOR l_app_id in get_app_id
2868 LOOP
2869 -- Call mrc engine to process update:
2870 ar_mrc_engine3.update_cm_application(
2871 l_app_id.receivable_application_id,
2872 p_control.payment_schedule_id, /* p_app_ps_id */
2873 p_control.customer_trx_id, /* p_ct_id */
2874 l_app_id.amount_applied);
2875 END LOOP;
2876
2877 FOR l_rec_del_app in del_app LOOP
2878 --
2879 --Release 11.5 VAT changes, recreate accounting for Applications
2880 --
2881 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2882 l_ae_doc_rec.document_id := l_rec_del_app.trx_id;
2883 l_ae_doc_rec.accounting_entity_level := 'ONE';
2884 l_ae_doc_rec.source_table := 'RA';
2885 l_ae_doc_rec.source_id := l_rec_del_app.app_id;
2886 l_ae_doc_rec.source_id_old := '';
2887 l_ae_doc_rec.other_flag := '';
2888 --Bug 1329091 - PS is updated before Accounting Engine Call
2889
2890 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2891
2892 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2893 /*bug-6976549*/
2894 arp_balance_check.CHECK_APPLN_BALANCE(l_rec_del_app.app_id,
2895 NULL,
2896 'N');
2897
2898 END LOOP;
2899
2900 EXCEPTION
2901 WHEN OTHERS THEN
2902 debug( 'EXCEPTION: Error executing update stmt',
2903 MSG_LEVEL_BASIC );
2904 RAISE;
2905
2906 END;
2907
2908
2909 print_fcn_label( 'arp_maintain_ps.update_adjustments()-' );
2910
2911 EXCEPTION
2912 WHEN OTHERS THEN
2913 debug( 'EXCEPTION: arp_maintain_ps.update_adjustments()',
2914 MSG_LEVEL_BASIC );
2915 RAISE;
2916
2917 END update_adjustments;
2918
2919
2920 ----------------------------------------------------------------------------
2921 PROCEDURE dump_control_rec( p_control control_rec_type ) IS
2922 BEGIN
2923
2924 debug( 'control.process_mode='||p_control.process_mode,
2925 MSG_LEVEL_DEBUG );
2926 debug( 'control.customer_trx_id='||p_control.customer_trx_id,
2927 MSG_LEVEL_DEBUG );
2928 debug( 'control.payment_schedule_id='||p_control.payment_schedule_id,
2929 MSG_LEVEL_DEBUG );
2930 debug( 'control.line_amount='||p_control.line_amount,
2931 MSG_LEVEL_DEBUG );
2932 debug( 'control.tax_amount='||p_control.tax_amount,
2933 MSG_LEVEL_DEBUG );
2934 debug( 'control.freight_amount='||p_control.freight_amount,
2935 MSG_LEVEL_DEBUG );
2936 debug( 'control.charge_amount='||p_control.charge_amount,
2937 MSG_LEVEL_DEBUG );
2938 debug( 'control.trx_type='||p_control.trx_type, MSG_LEVEL_DEBUG );
2939 debug( 'control.previous_customer_trx_id='||
2940 p_control.previous_customer_trx_id, MSG_LEVEL_DEBUG );
2941 debug( 'control.initial_customer_trx_id='||
2942 p_control.initial_customer_trx_id, MSG_LEVEL_DEBUG );
2943 debug( 'control.initial_trx_type='||p_control.initial_trx_type,
2944 MSG_LEVEL_DEBUG );
2945
2946 IF( p_control.is_open_receivable ) THEN
2947 debug( 'control.is_open_receivable=TRUE', MSG_LEVEL_DEBUG );
2948 ELSE
2949 debug( 'control.is_open_receivable=FALSE', MSG_LEVEL_DEBUG );
2950 END IF;
2951
2952 IF( p_control.is_postable ) THEN
2953 debug( 'control.is_postable=TRUE', MSG_LEVEL_DEBUG );
2954 ELSE
2955 debug( 'control.is_postable=FALSE', MSG_LEVEL_DEBUG );
2956 END IF;
2957
2958 IF( p_control.is_child ) THEN
2959 debug( 'control.is_child=TRUE', MSG_LEVEL_DEBUG );
2960 ELSE
2961 debug( 'control.is_child=FALSE', MSG_LEVEL_DEBUG );
2962 END IF;
2963
2964 IF( p_control.is_onacct_cm ) THEN
2965 debug( 'control.is_onacct_cm=TRUE', MSG_LEVEL_DEBUG );
2966 ELSE
2967 debug( 'control.is_onacct_cm=FALSE', MSG_LEVEL_DEBUG );
2968 END IF;
2969
2970
2971 EXCEPTION
2972 WHEN OTHERS THEN
2973 debug( 'EXCEPTION: arp_maintain_ps.dump_control_rec()',
2974 MSG_LEVEL_BASIC );
2975 RAISE;
2976
2977 END dump_control_rec;
2978
2979 ----------------------------------------------------------------------------
2980 FUNCTION get_applied_commitment_amount(
2981 p_control IN control_rec_type )
2982
2983 RETURN NUMBER IS
2984
2985 l_temp NUMBER;
2986
2987 BEGIN
2988
2989 print_fcn_label( 'arp_maintain_ps.get_applied_commitment_amount()+' );
2990
2991 BEGIN
2992
2993 SELECT
2994 nvl( sum( nvl(-adj.amount, 0) ), 0 )
2995 INTO l_temp
2996 FROM ar_adjustments adj
2997 WHERE adj.customer_trx_id =
2998 decode( p_control.initial_trx_type,
2999 'DEP', p_control.customer_trx_id,
3000 'GUAR', p_control.initial_customer_trx_id )
3001 and (
3002 ( p_control.initial_trx_type = 'DEP'
3003 and
3004 adj.subsequent_trx_id is null )
3005 or
3006 ( p_control.initial_trx_type = 'GUAR'
3007 and
3008 adj.subsequent_trx_id = p_control.customer_trx_id ) )
3009 and adj.receivables_trx_id = -1;
3010
3011 EXCEPTION
3012 WHEN OTHERS THEN
3013 debug( 'EXCEPTION: Error executing select stmt',
3014 MSG_LEVEL_BASIC );
3015 RAISE;
3016
3017 END;
3018
3019
3020 print_fcn_label( 'arp_maintain_ps.get_applied_commitment_amount()-' );
3021
3022 RETURN l_temp;
3023
3024 EXCEPTION
3025 WHEN OTHERS THEN
3026 debug( 'EXCEPTION: arp_maintain_ps.get_applied_commitment_amount()',
3027 MSG_LEVEL_BASIC );
3028 RAISE;
3029
3030 END get_applied_commitment_amount;
3031
3032
3033 ----------------------------------------------------------------------------
3034 FUNCTION ps_records_exist( p_customer_trx_id IN BINARY_INTEGER )
3035
3036 RETURN BOOLEAN IS
3037
3038 l_temp NUMBER;
3039
3040 BEGIN
3041
3042 print_fcn_label( 'arp_maintain_ps.ps_records_exist()+' );
3043
3044 SELECT 1
3045 INTO l_temp
3046 FROM ar_payment_schedules
3047 WHERE customer_trx_id = p_customer_trx_id;
3048
3049 print_fcn_label( 'arp_maintain_ps.ps_records_exist()-' );
3050
3051 RETURN( TRUE );
3052
3053 EXCEPTION
3054 WHEN NO_DATA_FOUND THEN
3055 print_fcn_label( 'arp_maintain_ps.ps_records_exist()-' );
3056 RETURN( FALSE );
3057 WHEN TOO_MANY_ROWS THEN
3058 print_fcn_label( 'arp_maintain_ps.ps_records_exist()-' );
3059 RETURN( TRUE );
3060 WHEN OTHERS THEN
3061 debug( 'EXCEPTION: arp_maintain_ps.ps_records_exist()',
3062 MSG_LEVEL_BASIC );
3063 RAISE;
3064
3065 END ps_records_exist;
3066
3067
3068 ----------------------------------------------------------------------------
3069 --
3070 -- PROCEDURE NAME: maintain_payment_schedules
3071 --
3072 -- DECSRIPTION:
3073 -- Server-side entry point for the Maintain Payment Schedules.
3074 --
3075 -- ARGUMENTS:
3076 -- IN:
3077 -- mode (I)nsert, (D)elete or (U)pdate
3078 -- customer_trx_id Transaction's payment sched to be modified
3079 -- payment_schedule_id Specific id to be changed.
3080 -- For U mode only and regular CM only.
3081 -- Must pass value for amount parameters.
3082 -- line_amount New CM line amount
3083 -- tax_amount New CM tax amount
3084 -- freight_amount New CM freight amount
3085 -- charge_amount New CM charges amount
3086 -- reversed_cash_receipt_id For DM reversals, I mode only
3087 --
3088 -- IN/OUT:
3089 -- applied_commitment_amount Amount of invoice applied to commitment
3090 --
3091 -- OUT:
3092 --
3093 -- NOTES:
3094 -- Exception raised if Oracle error.
3095 -- App_exception is raised for all other fatal errors and a message
3096 -- is put on the AOL stack. The public variable g_error_buffer is
3097 -- populated for both types of errors.
3098 --
3099 --
3100 -- HISTORY:
3101 --
3102 ----------------------------------------------------------------------------
3103 PROCEDURE maintain_payment_schedules(
3104 p_mode IN VARCHAR2,
3105 p_customer_trx_id IN NUMBER,
3106 p_payment_schedule_id IN NUMBER,
3107 p_line_amount IN NUMBER,
3108 p_tax_amount IN NUMBER,
3109 p_freight_amount IN NUMBER,
3110 p_charge_amount IN NUMBER,
3111 p_applied_commitment_amount IN OUT NOCOPY NUMBER,
3112 p_reversed_cash_receipt_id IN NUMBER DEFAULT NULL
3113 ) IS
3114
3115 l_doc_where_clause VARCHAR2(1000);
3116 l_control_rec control_rec_type;
3117 /* VAT changes */
3118 l_ae_doc_rec ae_doc_rec_type;
3119 l_adjustment_id ar_adjustments.adjustment_id%type;
3120 l_llca_count NUMBER;
3121 l_customer_trx ra_customer_trx%ROWTYPE;
3122 BEGIN
3123
3124 print_fcn_label( 'arp_maintain_ps.maintain_payment_schedules()+' );
3125
3126 -- Validate parameters
3127 IF( p_mode IS NULL OR
3128 p_mode NOT IN (I, U, D) OR
3129 p_customer_trx_id IS NULL OR
3130 (p_payment_schedule_id IS NOT NULL AND p_mode <> U) ) THEN
3131
3132 g_error_buffer := MSG_INVALID_PARAMETERS;
3133 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
3134 RAISE invalid_parameters;
3135
3136 END IF;
3137
3138 --------------------------------------------------------------------
3139 -- Assign values to control_rec
3140 --------------------------------------------------------------------
3141 l_control_rec.process_mode := p_mode;
3142 l_control_rec.customer_trx_id := p_customer_trx_id;
3143 l_control_rec.payment_schedule_id := p_payment_schedule_id;
3144 l_control_rec.line_amount := nvl( p_line_amount, 0);
3145 l_control_rec.tax_amount := nvl( p_tax_amount, 0);
3146 l_control_rec.freight_amount := nvl( p_freight_amount, 0);
3147 l_control_rec.charge_amount := nvl( p_charge_amount, 0);
3148 l_control_rec.reversed_cash_receipt_id := p_reversed_cash_receipt_id;
3149
3150
3151 SAVEPOINT ar_payment_schedule;
3152
3153 --------------------------------------------------------------------
3154 -- Get other info from tables for control_rec
3155 --------------------------------------------------------------------
3156 do_setup( l_control_rec );
3157
3158 --------------------------------------------------------------------
3159 -- Print out NOCOPY control_rec
3160 --------------------------------------------------------------------
3161 dump_control_rec( l_control_rec );
3162
3163 --------------------------------------------------------------------
3164 -- Do nothing if open_rec = N
3165 -- or I mode and ps records exist
3166 --------------------------------------------------------------------
3167 -- Case where the transaction CM does not have a open Receivable
3168 -- and mode is <> Deletion
3169 -- no need to process
3170
3171 IF( NOT l_control_rec.is_open_receivable
3172 AND l_control_rec.process_mode <> 'D' ) THEN
3173
3174 RETURN;
3175
3176 END IF;
3177
3178 IF( l_control_rec.process_mode = I AND
3179 ps_records_exist( l_control_rec.customer_trx_id ) ) THEN
3180
3181 RETURN;
3182
3183 END IF;
3184
3185 --------------------------------------------------------------------
3186 -- Process all transactions except for regular CMs
3187 --------------------------------------------------------------------
3188 IF( l_control_rec.previous_customer_trx_id IS NULL ) THEN
3189
3190 debug( ' Process non CM transactions', MSG_LEVEL_DEBUG );
3191
3192 ----------------------------------------------------------------
3193 -- Update, Delete case
3194 ----------------------------------------------------------------
3195 IF( l_control_rec.process_mode in ( U, D ) ) THEN
3196
3197 debug( ' Update, Delete mode', MSG_LEVEL_DEBUG );
3198
3199 IF( l_control_rec.initial_trx_type = DEP ) THEN
3200
3201 debug( ' DEP case', MSG_LEVEL_DEBUG );
3202
3203 /* VAT changes: delete accounting entry for adjustment */
3204 SELECT adj.adjustment_id into l_adjustment_id
3205 FROM ar_adjustments adj
3206 WHERE adj.customer_trx_id = l_control_rec.customer_trx_id
3207 and adj.receivables_trx_id = -1;
3208
3209 l_ae_doc_rec.document_type := 'ADJUSTMENT';
3210 l_ae_doc_rec.document_id := l_adjustment_id;
3211 l_ae_doc_rec.accounting_entity_level := 'ONE';
3212 l_ae_doc_rec.source_table := 'ADJ';
3213 l_ae_doc_rec.source_id := l_adjustment_id;
3214 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
3215
3216 -- arabdep: delete invoice adjustments
3217 delete_adjustments( l_control_rec.customer_trx_id, NULL );
3218
3219 ELSIF( l_control_rec.initial_trx_type = GUAR ) THEN
3220
3221 debug( ' GUAR case', MSG_LEVEL_DEBUG );
3222
3223 -- arabaj: delete guar adj, update guar ps created by child
3224 reverse_adjustments(
3225 system_info,
3226 profile_info,
3227 l_control_rec.initial_customer_trx_id,
3228 l_control_rec.customer_trx_id );
3229
3230 END IF;
3231
3232 -- aradps: delete old ps
3233 delete_payment_schedule( l_control_rec.customer_trx_id );
3234
3235 END IF;
3236
3237 ----------------------------------------------------------------
3238 -- Insert, Update case
3239 ----------------------------------------------------------------
3240 IF( p_mode in ( I, U ) ) THEN
3241
3242 debug( ' Insert, Update mode', MSG_LEVEL_DEBUG );
3243
3244 --------------------------------------------------------------
3245 -- araips: call raaips
3246 --------------------------------------------------------------
3247 arp_maintain_ps2.insert_inv_ps_private(
3248 system_info,
3249 profile_info,
3250 l_control_rec.customer_trx_id,
3251 l_control_rec.reversed_cash_receipt_id );
3252
3253
3254 IF( l_control_rec.is_child ) THEN
3255
3256 ----------------------------------------------------------
3257 -- araips: call raaups to insert adjustments for
3258 -- commitment invoices
3259 ----------------------------------------------------------
3260 arp_maintain_ps2.insert_child_adj_private(
3261 system_info,
3262 profile_info,
3263 l_control_rec.customer_trx_id );
3264
3265 ----------------------------------------------------------
3266 -- arapca: get invoice amount that was applied to commitment
3267 ----------------------------------------------------------
3268 p_applied_commitment_amount :=
3269 get_applied_commitment_amount( l_control_rec );
3270
3271 ----------------------------------------------------------
3272 -- Update adjustments with document number
3273 ----------------------------------------------------------
3274 ----------------------------------------------------------
3275 -- Construct where clause
3276 ----------------------------------------------------------
3277 IF( l_control_rec.initial_trx_type = DEP ) THEN
3278
3279 l_doc_where_clause :=
3280 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3281 'and receivables_trx_id = -1';
3282
3283 ----------------------------------------------------------
3284 -- Update adjustments with document number
3285 ----------------------------------------------------------
3286 update_adj_document_number(
3287 system_info,
3288 profile_info,
3289 l_control_rec.customer_trx_id,
3290 l_control_rec.customer_trx_id,
3291 null,
3292 null,
3293 l_doc_where_clause );
3294
3295
3296
3297 ELSE -- GUAR
3298
3299 l_doc_where_clause :=
3300 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3301 'and subsequent_trx_id = :bind2 '|| CRLF ||
3302 'and receivables_trx_id = -1';
3303
3304
3305
3306 ----------------------------------------------------------
3307 -- Update adjustments with document number
3308 ----------------------------------------------------------
3309 update_adj_document_number(
3310 system_info,
3311 profile_info,
3312 l_control_rec.customer_trx_id,
3313 l_control_rec.initial_customer_trx_id,
3314 l_control_rec.customer_trx_id,
3315 null,
3316 l_doc_where_clause );
3317
3318 END IF;
3319
3320 END IF;
3321 END IF;
3322
3323 --------------------------------------------------------------------
3324 -- Process regular CMs where payment_schedule_id is NOT specified
3325 --------------------------------------------------------------------
3326 -- Process regular CM
3327 --
3328 ELSIF( l_control_rec.previous_customer_trx_id IS NOT NULL AND
3329 l_control_rec.payment_schedule_id IS NULL ) THEN
3330
3331 debug( ' Process regular CMs (payment_schedule_id = NULL)',
3332 MSG_LEVEL_DEBUG );
3333
3334 --
3335 -- Update, Delete case
3336 --
3337
3338 IF( l_control_rec.process_mode in ( U, D ) ) THEN
3339
3340 debug( ' Update, Delete mode', MSG_LEVEL_DEBUG );
3341
3342 /* Bug 2808262 Check if the CM being incompleted, is actually
3343 having any adjustments records or not. If not, there is no
3344 need for the updation of payment schedule, or deletion of
3345 adjustment record and its corresponding distributions.
3346 */
3347
3348 IF ( l_control_rec.initial_trx_type = DEP
3349 AND
3350 /*salladi 3118714*/
3351 l_control_rec.is_child=TRUE ) THEN
3352
3353 debug( ' DEP case', MSG_LEVEL_DEBUG );
3354
3355 -- arabaj: delete invoice adj (created by cm), update inv ps
3356 reverse_adjustments(
3357 system_info,
3358 profile_info,
3359 l_control_rec.previous_customer_trx_id,
3360 l_control_rec.customer_trx_id );
3361
3362
3363 ELSIF ( l_control_rec.initial_trx_type = GUAR
3364 AND
3365 l_control_rec.is_child ) THEN
3366
3367 debug( ' GUAR case', MSG_LEVEL_DEBUG );
3368
3369 -- arabaj: delete guar adj (created by cm), update guar ps
3370 reverse_adjustments(
3371 system_info,
3372 profile_info,
3373 l_control_rec.initial_customer_trx_id,
3374 l_control_rec.customer_trx_id );
3375
3376 END IF;
3377
3378 -- arabcm: update inv ps (reverse cm effect)
3379 arp_standard.debug(' reverse_cm_effect+');
3380 reverse_cm_effect(
3381 system_info,
3382 profile_info,
3383 l_control_rec.customer_trx_id );
3384 arp_standard.debug(' reverse_cm_effect-');
3385
3386 -- aradra: delete cm app recs
3387 arp_standard.debug(' delete_applications+');
3388 delete_applications( l_control_rec.customer_trx_id );
3389 arp_standard.debug(' delete_applications-');
3390 -- aradps: delete cm ps
3391 arp_standard.debug(' delete_payment_schedule+');
3392 delete_payment_schedule( l_control_rec.customer_trx_id );
3393 arp_standard.debug(' delete_payment_schedule-');
3394
3395 /* 11825040 - Clean up line level balances if required */
3396 arp_standard.debug(' clean up line level balances');
3397 select count(*)
3398 into l_llca_count
3399 from ra_customer_trx_lines
3400 where customer_trx_id = l_control_rec.previous_customer_trx_id
3401 and line_type = 'LINE'
3402 and amount_due_remaining is not null;
3403
3404 IF l_llca_count > 0
3405 THEN
3406 debug(' calling re_calcul_rem_amt()');
3407 l_customer_trx.customer_trx_id :=
3408 l_control_rec.previous_customer_trx_id;
3409 arp_det_dist_pkg.re_calcul_rem_amt(l_customer_trx);
3410 END IF;
3411 END IF;
3412
3413 IF( l_control_rec.process_mode in ( I, U ) ) THEN
3414
3415 debug( ' Insert, Update mode', MSG_LEVEL_DEBUG );
3416
3417 IF( l_control_rec.is_child ) THEN
3418
3419 ----------------------------------------------------------
3420 -- araiad: create adj, update ps
3421 ----------------------------------------------------------
3422 arp_standard.debug(' arp_maintain_ps2.insert_cm_child_adj_private+');
3423 arp_maintain_ps2.insert_cm_child_adj_private(
3424 system_info,
3425 profile_info,
3426 l_control_rec.customer_trx_id );
3427 arp_standard.debug(' arp_maintain_ps2.insert_cm_child_adj_private-');
3428
3429
3430 ----------------------------------------------------------
3431 -- Update adjustments with document number
3432 ----------------------------------------------------------
3433 ----------------------------------------------------------
3434 -- Construct where clause
3435 ----------------------------------------------------------
3436 IF( l_control_rec.initial_trx_type = DEP ) THEN
3437
3438 l_doc_where_clause :=
3439 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3440 'and subsequent_trx_id = :bind2 '|| CRLF ||
3441 'and receivables_trx_id = -1';
3442
3443 ----------------------------------------------------------
3444 -- Update adjustments with document number
3445 ----------------------------------------------------------
3446
3447 update_adj_document_number(
3448 system_info,
3449 profile_info,
3450 l_control_rec.customer_trx_id,
3451 l_control_rec.previous_customer_trx_id,
3452 l_control_rec.customer_trx_id,
3453 null,
3454 l_doc_where_clause );
3455
3456 ELSE -- GUAR
3457
3458 l_doc_where_clause :=
3459 'WHERE customer_trx_id = :bind1 '|| CRLF ||
3460 'and subsequent_trx_id = :bind2 '|| CRLF ||
3461 'and receivables_trx_id = -1';
3462
3463
3464
3465 ----------------------------------------------------------
3466 -- Update adjustments with document number
3467 ----------------------------------------------------------
3468 arp_standard.debug(' update_adj_document_number+');
3469 update_adj_document_number(
3470 system_info,
3471 profile_info,
3472 l_control_rec.customer_trx_id,
3473 l_control_rec.initial_customer_trx_id,
3474 l_control_rec.customer_trx_id,
3475 null,
3476 l_doc_where_clause );
3477 arp_standard.debug(' update_adj_document_number-');
3478 END IF;
3479
3480
3481
3482
3483 END IF;
3484
3485 -- araira: create cm ps, apps
3486 arp_standard.debug(' arp_maintain_ps2.insert_cm_ps_private+');
3487 arp_maintain_ps2.insert_cm_ps_private(
3488 system_info,
3489 profile_info,
3490 l_control_rec.customer_trx_id );
3491 arp_standard.debug(' arp_maintain_ps2.insert_cm_ps_private-');
3492
3493
3494 END IF;
3495
3496
3497 --------------------------------------------------------------------
3498 -- Process regular CMs where payment_schedule_id is specified
3499 --------------------------------------------------------------------
3500 ELSIF( l_control_rec.previous_customer_trx_id IS NOT NULL AND
3501 l_control_rec.payment_schedule_id IS NOT NULL ) THEN
3502
3503 debug( ' Process regular CMs (payment_schedule_id <> NULL)',
3504 MSG_LEVEL_DEBUG );
3505
3506 IF( l_control_rec.process_mode <> U ) THEN
3507
3508 -- >> ERROR: bad mode for this case
3509
3510 debug( ' Bad mode', MSG_LEVEL_DEBUG );
3511 null;
3512 END IF;
3513
3514 IF( l_control_rec.initial_trx_type = DEP ) THEN
3515
3516 debug( ' DEP case', MSG_LEVEL_DEBUG );
3517
3518 -- araudps: insert dep adj, if not exists, else update adj
3519 update_adjustments( system_info, profile_info, l_control_rec );
3520
3521 ELSE
3522 debug( ' non-DEP case', MSG_LEVEL_DEBUG );
3523
3524 -- araups: correct round error, update inv ps, update cm app
3525 arp_standard.debug(' update_payment_schedule+');
3526 update_payment_schedule(
3527 system_info,
3528 profile_info,
3529 l_control_rec );
3530 arp_standard.debug(' update_payment_schedule-');
3531
3532 END IF;
3533
3534 END IF;
3535
3536
3537 print_fcn_label( 'arp_maintain_ps.maintain_payment_schedules()-' );
3538
3539 EXCEPTION
3540 WHEN OTHERS THEN
3541 debug( 'EXCEPTION: arp_maintain_ps.maintain_payment_schedules()',
3542 MSG_LEVEL_BASIC );
3543
3544 close_cursors;
3545 ROLLBACK TO ar_payment_schedule;
3546
3547 IF( sqlcode = 1 ) THEN
3548 --
3549 -- User-defined exception
3550 --
3551 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
3552 FND_MESSAGE.set_token( 'GENERIC_TEXT', g_error_buffer );
3553 APP_EXCEPTION.raise_exception;
3554
3555 ELSE
3556 --
3557 -- Oracle error
3558 --
3559 g_error_buffer := SQLERRM;
3560
3561 RAISE;
3562
3563 END IF;
3564
3565 RAISE;
3566
3567 END maintain_payment_schedules;
3568
3569 ---------------------------------------------------------------------------
3570 -- Test Functions
3571 ---------------------------------------------------------------------------
3572 PROCEDURE test_build_doc_combo_sql
3573 IS
3574
3575 BEGIN
3576
3577 enable_debug( 1000000 );
3578
3579 build_doc_combo_sql(
3580 system_info,
3581 profile_info,
3582 doc_combo_select_c );
3583
3584
3585 END;
3586
3587
3588 ---------------------------------------------------------------------------
3589 PROCEDURE test_build_doc_ins_audit_sql( p_where_clause VARCHAR2 )
3590 IS
3591
3592 BEGIN
3593
3594 enable_debug( 1000000 );
3595
3596 build_doc_insert_audit_sql(
3597 system_info,
3598 profile_info,
3599 p_where_clause,
3600 doc_insert_audit_c );
3601
3602 END;
3603
3604 ---------------------------------------------------------------------------
3605 PROCEDURE test_build_doc_update_adj_sql( p_where_clause VARCHAR2 )
3606 IS
3607
3608 BEGIN
3609
3610 enable_debug( 1000000 );
3611
3612 build_doc_update_adj_sql(
3613 system_info,
3614 profile_info,
3615 'my_seq', -- seq name
3616 1, -- seq id
3617 p_where_clause,
3618 doc_update_adj_c );
3619
3620
3621 END;
3622
3623 ---------------------------------------------------------------------------
3624 PROCEDURE test_update_adj_doc_number(
3625 p_customer_trx_id BINARY_INTEGER,
3626 p_update_where_clause VARCHAR2 ) IS
3627
3628 BEGIN
3629
3630 enable_debug( 1000000 );
3631
3632
3633 update_adj_document_number(
3634 system_info,
3635 profile_info,
3636 p_customer_trx_id,
3637 null,
3638 null,
3639 null,
3640 p_update_where_clause );
3641
3642
3643
3644
3645 END;
3646
3647
3648
3649 ---------------------------------------------------------------------------
3650 --
3651 -- Constructor code
3652 --
3653 PROCEDURE init IS
3654 BEGIN
3655
3656 print_fcn_label( 'arp_maintain_ps.constructor()+' );
3657
3658 get_error_message_text;
3659
3660 print_fcn_label( 'arp_maintain_ps.constructor()-' );
3661
3662
3663 EXCEPTION
3664 WHEN OTHERS THEN
3665 debug('EXCEPTION: arp_maintain_ps.constructor()');
3666 debug(SQLERRM);
3667 RAISE;
3668 END init;
3669
3670 BEGIN
3671 init;
3672 END arp_maintain_ps;