1 PACKAGE BODY ARP_PROCESS_HEADER_POST_COMMIT AS
2 /* $Header: ARTEHPCB.pls 120.13.12010000.1 2008/07/24 16:56:11 appldev ship $ */
3
4 pg_base_precision fnd_currencies.precision%type;
5 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
6 pg_trx_header_level_rounding ar_system_parameters.trx_header_level_rounding%type;
7
8 /*===========================================================================+
9 | PROCEDURE |
10 | post_commit |
11 | |
12 | DESCRIPTION |
13 | Performs logic that must occur after all of the other logic for the |
14 | insertion or update of a transaction has occurred. |
15 | |
16 | SCOPE - PUBLIC |
17 | |
18 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
19 | arp_util.debug |
20 | arp_maintain_ps.maintain_payment_schedules |
21 | |
22 | ARGUMENTS : IN: |
23 | OUT: |
24 | IN/ OUT: |
25 | |
26 | RETURNS : NONE |
27 | |
28 | NOTES |
29 | |
30 | MODIFICATION HISTORY |
31 | 28-AUG-95 Charlie Tomberg Created |
32 | 19-FEB-96 Oliver Steinmeier Changed logic in post-commit to |
33 | make sure the payment schedule |
34 | gets called for debit memos |
35 | 05-05-97 OSTEINME Changed post_commit logic to recreate |
36 | payment schedules if the exchange |
37 | rate, exchange date, or exchange type |
38 | was changed (bug 486369) |
39 | 08-26-97 D. Jancis Changed post commit logic to recreate |
40 | payment schedules if the bill to |
41 | customer or the bill to address was |
42 | changed (Bug 520221 ) |
43 | 10-14-97 D. Jancis Changed post commit logic to |
44 | recreate payment schedules if the |
45 | GL-Date or transaction type |
46 | was changed (Bug 564308 and 562342) |
47 | 11-14-97 D. Jancis Added code to retrieve necessary |
48 | values to call the arpt_sql_func_util.|
49 | get_activity_flag routine. If for |
50 | some reason, the flag to recreate the |
51 | payment schedule is Y and there is |
52 | activity against the trans., an error |
53 | is raised. bug 586371 |
54 | 11-19-97 Sai Rangarajan Bug Fix 586968 - Changed logic to |
55 | to check for recreation of payment |
56 | schedules until after the |
57 | gl_line_dist records are updated. |
58 | also moved code written for bug 586371|
59 | 12-17-97 D. Jancis Bug Fix 598442 - changed logic to |
60 | check for differences in exchange |
61 | with the posibility of a value being |
62 | NULL. also added addition check |
63 | to see if there is activity against |
64 | trans do not 'delete/recreate' ps |
65 | 03-23-98 Tasman Tang Bug Fix 643716 - passed |
66 | p_previous_customer_trx_id instead of |
67 | l_previous_customer_trx_id in first |
68 | call to get_activity_flag since the |
69 | local var is never initialized |
70 | 28-Sep-01 Pravin Pawar Bug NO :1915785 - Added the message |
71 | 'AR_PLCRE_FHLR_CCID' , to prompt the |
72 | user to enable the Header Level |
73 | Rounding Account |
74 | 01-FEB-02 M Raymond Bug 2164863 - added a parameter
75 | to do_completion_checking routine and
76 | added a second call to that routine
77 | after the rounding was completed.
78 | Also substituted arp_trx_complete_chk
79 | for arp_trx_validate.
80 | 02-DEC-04 V Crisostomo Bug3049044/3041195: Changed
81 | arp_trx_validate.check_sign_and_overapp
82 | to take p_error_mode and p_error_count
83 | to allow for better error handling.
84 +===========================================================================*/
85 PROCEDURE post_commit( p_form_name IN varchar2,
86 p_form_version IN number,
87 p_customer_trx_id IN
88 ra_customer_trx.customer_trx_id%type,
89 p_previous_customer_trx_id IN
90 ra_customer_trx.previous_customer_trx_id%type,
91 p_complete_flag IN
92 ra_customer_trx.complete_flag%type,
93 p_trx_open_receivables_flag IN
94 ra_cust_trx_types.accounting_affect_flag%type,
95 p_prev_open_receivables_flag IN
96 ra_cust_trx_types.accounting_affect_flag%type,
97 p_creation_sign IN
98 ra_cust_trx_types.creation_sign%type,
99 p_allow_overapplication_flag IN
100 ra_cust_trx_types.allow_overapplication_flag%type,
101 p_natural_application_flag IN
102 ra_cust_trx_types.natural_application_only_flag%type,
103 p_cash_receipt_id IN
104 ar_cash_receipts.cash_receipt_id%type DEFAULT NULL,
105 p_error_mode IN VARCHAR2
106 ) IS
107
108 l_scredit_count NUMBER;
109 l_dist_count NUMBER;
110 l_error_count NUMBER;
111 l_error_message VARCHAR2(128);
112
113 l_open_receivables_flag ra_cust_trx_types.accounting_affect_flag%type;
114 l_true_open_receivables_flag ra_cust_trx_types.accounting_affect_flag%type;
115 l_old_complete_flag VARCHAR2(1);
116 l_recreate_ps_flag VARCHAR2(1);
117 l_applied_commitment_amt NUMBER;
118
119 /* added for Bug 586371 */
120
121 l_activity_flag VARCHAR2(1);
122 l_previous_customer_trx_id NUMBER(15);
123 l_initial_customer_trx_id NUMBER(15);
124 l_type VARCHAR(20);
125 l_called_from_api VARCHAR2(1);
126
127 /*added for the bug 2641517 */
128 l_term_changed_flag VARCHAR2(1);
129 l_trx_sum_hist_rec AR_TRX_SUMMARY_HIST%rowtype;
130 l_history_id NUMBER;
131 CURSOR get_existing_ps (p_cust_trx_id IN NUMBER) IS
132 SELECT customer_trx_id,
133 payment_schedule_id,
134 invoice_currency_code,
135 due_date,
136 amount_in_dispute,
137 amount_due_original,
138 amount_due_remaining,
139 amount_adjusted,
140 customer_id,
141 customer_site_use_id,
142 trx_date,
143 amount_credited,
144 status
145 FROM ar_payment_schedules
146 WHERE customer_trx_id = p_cust_trx_id;
147 l_counter NUMBER := 0;
148 l_amt_credited NUMBER;
149 l_stat VARCHAR2(10);
150
151 l_prev_cust_old_state AR_BUS_EVENT_COVER.prev_cust_old_state_tab;
152
153 /*3463885*/
154 l_ps_rev_cash_id NUMBER;
155 l_ct_rev_cash_id NUMBER;
156 begin
157
158 arp_util.print_fcn_label( 'arp_process_header.post_commit()+ ');
159
160 /*-----------------------------------------------------------------+
161 | check form version to determine if it is compatible with the |
162 | entity handler. |
163 +-----------------------------------------------------------------*/
164
165 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
166
167
168 /*---------------------------------------------------------+
169 | Derive flags for maintain payment schedule procedure |
170 +---------------------------------------------------------*/
171
172 /*----------------------------------------------------------------------------------+
173 | Bug Fix 586968 - srangara -- Removing check for re-creation of payment schedules|
174 | so that it is done after the gl_line_dist records are created |
175 | Also moved logic for bug 586371 so that activity_flag is deduced |
176 | after l_recreate_ps_flag is found out. |
177 +----------------------------------------------------------------------------------*/
178
179 /*3463885 Added two cols reversed_cash_receipt_id to be selected*/
180 SELECT DECODE(
181 ctt.accounting_affect_flag,
182 'Y', 'Y',
183 DECODE(
184 COUNT(ps.payment_schedule_id),
185 0, 'N',
186 'Y'
187 )
188 ), -- Open Receivables Flag
189 DECODE(
190 ctt.accounting_affect_flag,
191 'Y', DECODE(
192 COUNT(ps.payment_schedule_id),
193 0, 'N',
194 'Y'
195 ),
196 NULL
197 ),
198 ctt.accounting_affect_flag,
199 ps.reversed_cash_receipt_id,
200 ct.reversed_cash_receipt_id
201 INTO l_open_receivables_flag,
202 l_old_complete_flag,
203 l_true_open_receivables_flag,
204 l_ps_rev_cash_id,
205 l_ct_rev_cash_id
206 FROM ar_payment_schedules ps,
207 ra_cust_trx_types ctt,
208 ra_cust_trx_line_gl_dist lgd,
209 ra_customer_trx ct
210 WHERE ct.customer_trx_id = ps.customer_trx_id(+)
211 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
212 AND ct.customer_trx_id = lgd.customer_trx_id
213 AND lgd.account_class = 'REC'
214 AND lgd.latest_rec_flag = 'Y'
215 AND ct.customer_trx_id = p_customer_trx_id
216 GROUP BY ctt.accounting_affect_flag,
217 ct.complete_flag,
218 ct.term_id,
219 ct.invoice_currency_code,
220 lgd.amount,
221 ct.exchange_rate,
222 ct.exchange_date,
223 ct.exchange_rate_type,
224 ct.bill_to_customer_id,
225 ct.bill_to_site_use_id,
226 lgd.gl_date,
227 ct.cust_trx_type_id,
228 ps.reversed_cash_receipt_id,
229 ct.reversed_cash_receipt_id;
230
231 arp_util.debug('Open Receivables Flag = ' || l_open_receivables_flag);
232 arp_util.debug('Complete Flag Old Value = ' || l_old_complete_flag);
233 arp_util.debug('Complete Flag New Value = ' || p_complete_flag);
234
235 -- code enclosed in IF clause is NOT necessary when calling program is ARPBFBIB
236 if p_form_name <> 'ARPBFBIB' then
237
238 /*----------------------------------------------------------------------+
239 | Ensure that the transaction can be completed if it is now complete |
240 | but previously was incomplete. This is necessary because something |
241 | could have changed since the completion check was done in the form |
242 | to make it incorrect to complete the transaction. |
243 +----------------------------------------------------------------------*/
244
245 IF (
246 P_complete_flag = 'Y'
247 AND
248 l_old_complete_flag = 'N'
249 )
250 THEN
251
252 /* Bug 2164863 - Do not execute tax and accounting validation
253 at this point. We will recall this procedure after
254 rounding has occurred for those tests */
255
256 arp_trx_complete_chk.do_completion_checking(
257 p_customer_trx_id,
258 NULL,
259 NULL,
260 p_error_mode,
261 l_error_count,
262 'N'
263 );
264
265 IF (l_error_count > 0)
266 THEN
267 app_exception.raise_exception;
268 END IF;
269
270 END IF;
271
272 /*-------------------------------------------------------+
273 | Correct rounding errors in the salescredit records. |
274 +-------------------------------------------------------*/
275
276 /*3463885*/
277 IF l_ps_rev_cash_id is not null and l_ct_rev_cash_id is null THEN
278 UPDATE RA_CUSTOMER_TRX SET REVERSED_CASH_RECEIPT_ID = l_ps_rev_cash_id
279 WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
280 END IF;
281
282 arp_rounding.correct_scredit_rounding_errs( p_customer_trx_id,
283 l_scredit_count);
284
285
286 /*------------------------------------------------------------+
287 | Header level Rounding |
288 | the following code is redundant - eventually the code |
289 | should be studied so that l_activity_flag is set only once |
290 | |
291 | l_activity_flag to be used in correct_dist_rounding |
292 +------------------------------------------------------------*/
293
294 SELECT ctt.type,
295 ct.initial_customer_trx_id,
296 ct.previous_customer_trx_id
297 INTO
298 l_type,
299 l_initial_customer_trx_id,
300 l_previous_customer_trx_id
301 from
302 ra_customer_trx ct,
303 ra_cust_trx_types ctt
304 WHERE
305 ct.customer_trx_id = p_customer_trx_id
306 and
307 ct.cust_trx_type_id = ctt.cust_trx_type_id;
308
309
310 l_activity_flag := arpt_sql_func_util.get_activity_flag(
311 p_customer_trx_id,
312 l_true_open_receivables_flag,
313 p_complete_flag,
314 l_type,
315 l_initial_customer_trx_id,
316 l_previous_customer_trx_id );
317
318 /*---------------------------------------------------+
319 | Correct rounding errors in the account set and |
320 | account assignments records. |
321 +---------------------------------------------------*/
322
323 /* 6774561 - do not call Header Level rounding blindly
324 if the transaction is a chargeback.
325
326 Reason: Receipt chargebacks have amounts/acctds that
327 are specific to the receipt and may not total out
328 to the converted amount. If we round them, then the
329 CB could be for a different amount than the original
330 receipt. */
331 IF (l_type <> 'CB')
332 THEN
333 IF (arp_rounding.correct_dist_rounding_errors
334 ( null, -- request_id
335 p_customer_trx_id,
336 null, -- customer_trx_line_id
337 l_dist_count,
338 l_error_message,
339 arp_global.base_precision,
340 arp_global.base_min_acc_unit,
341 'ALL',
342 'N',
343 null, -- debug_mode
344 arp_global.sysparam.trx_header_level_rounding,
345 l_activity_flag
346 ) = 0 -- FALSE
347 )
348 THEN
349 arp_util.debug('EXCEPTION: arp_process_header.post_commit()');
350 arp_util.debug(l_error_message);
351 fnd_message.set_name('AR', 'AR_PLCRE_FHLR_CCID');
352
353 APP_EXCEPTION.raise_exception;
354 END IF;
355 ELSE
356 arp_util.debug('call to arp_rounding bypassed due to trx_type');
357 END IF; -- end CB
358
359 /* Bug 2164863 - The tax and accounting validations were postponed
360 until after the rounding routines were called. This was due to
361 the fact that some localizations will recalculate the tax and
362 distributions within do_completion_checking.
363
364 Note that the last parameter on the call is 'Y' which means
365 that only the validation of tax and accounting will execute
366 at this point. */
367
368 IF (
369 P_complete_flag = 'Y'
370 AND
371 l_old_complete_flag = 'N'
372 )
373 THEN
374
375 arp_trx_complete_chk.do_completion_checking(
376 p_customer_trx_id,
377 NULL,
378 NULL,
379 p_error_mode,
380 l_error_count,
381 'Y'
382 );
383
384 IF (l_error_count > 0)
385 THEN
386 app_exception.raise_exception;
387 END IF;
388
389 END IF;
390
391 /*-------------------------------------------------------------+
392 | if complete then |
393 | check that the invoice total's sign is correct according |
394 | to the sign constraint on the type |
395 +-------------------------------------------------------------*/
396
397 -- in order to get debit memo reversal to work, this code is not
398 -- executed if p_cash_receipt_id is NOT NULL. This needs to be
399 -- changed when Charlie is around to help figure out the proper
400 -- value for the creation sign.
401
402 IF (p_cash_receipt_id IS NULL) THEN
403 IF ( p_complete_flag = 'Y' )
404 THEN
405 /*Bug3049044/3041195: Changed arp_trx_validate.check_sign_and_overapp
406 to take p_error_mode and p_error_count to allow for better
407 error handling */
408
409 arp_trx_validate.check_sign_and_overapp( p_customer_trx_id,
410 p_previous_customer_trx_id,
411 p_trx_open_receivables_flag,
412 p_prev_open_receivables_flag,
413 p_creation_sign,
414 p_allow_overapplication_flag,
415 p_natural_application_flag,
416 p_error_mode,
417 l_error_count
418 );
419 IF (l_error_count > 0)
420 THEN
421 app_exception.raise_exception;
422 END IF;
423
424 END IF;
425 END IF;
426
427 /* Bug fix 4910860
428 Check if the accounting entries of this transaction balance */
429
430 IF p_form_name in ('AR_INVOICE_API','AR_TRANSACTION_GRP','AR_DEPOSIT_API_PUB') THEN
431 l_called_from_api := 'Y';
432 ELSE
433 l_called_from_api := 'N';
434 END IF;
435 arp_balance_check.check_transaction_balance(p_customer_trx_id,l_called_from_api);
436
437 -----------------------------------------------------------------------
438 -- Maintain transaction payment schedules:
439 --
440 -- open_receivable_flag: if open_rec = Y or payment schedules exist
441 -- complete_changed_flag:
442 -- if open_rec = Y and complete = Y and no PS exist ==> Y
443 -- if open_rec = Y and complete = N and PS exists ==> N
444 -- amount_or_terms_changed_flag:
445 -- if open_rec = Y sum of lines <> sum of ps
446 -----------------------------------------------------------------------
447
448
449 -----------------------------------------------------------------------
450 -- IF complete flag has changed to Y
451 -- AND open_rec_flag = 'Y'
452 -- THEN
453 -- Create payment schedules
454 -- IF CM against transaction
455 -- THEN
456 -- update the invoice's payment schedules
457 -- create receivable application record
458 -- END IF;
459 -- END IF;
460 -----------------------------------------------------------------------
461
462 IF( p_complete_flag <> l_old_complete_flag AND
463 P_complete_flag = 'Y' AND
464 l_open_receivables_flag = 'Y' ) THEN
465
466 --apandit : populating summary table using business events
467 --for regular credit memo get the ps status information for prev_cust_trx_id
468 IF l_previous_customer_trx_id IS NOT NULL THEN
469
470
471 FOR i in get_existing_ps(l_previous_customer_trx_id) LOOP
472 l_prev_cust_old_state(i.payment_schedule_id).amount_due_remaining := i.amount_due_remaining;
473 l_prev_cust_old_state(i.payment_schedule_id).status := i.status;
474 l_prev_cust_old_state(i.payment_schedule_id).amount_credited := i.amount_credited;
475
476 END LOOP;
477
478
479 END IF;
480
481 arp_standard.debug('Calling maintain_payment_schedules EH in insert mode');
482 arp_maintain_ps.maintain_payment_schedules(
483 'I',
484 p_customer_trx_id,
485 NULL, -- ps_id
486 NULL, -- line_amount
487 NULL, -- tax_amount
488 NULL, -- frt_amount
489 NULL, -- charge_amount
490 l_applied_commitment_amt,
491 p_cash_receipt_id
492 );
493 --apandit
494 --Bug 2641517 Raise the Complete business event.
495 AR_BUS_EVENT_COVER.Raise_Trx_Creation_Event
496 (l_type,
497 p_customer_trx_id,
498 l_prev_cust_old_state);
499
500 END IF;
501
502
503 -----------------------------------------------------------------------
504 -- IF complete_flag has changed to N
505 -- AND open_rec_flag = 'Y'
506 -- OR open receivables has been changed to N
507 -- THEN
508 -- delete payment schedules
509 -- IF CM against transaction
510 -- THEN
511 -- update the invoice's payment schedules
512 -- delete receivable application record
513 -- END IF;
514 -- END IF;
515 -----------------------------------------------------------------------
516
517 IF(
518 (
519 p_complete_flag <> l_old_complete_flag
520 AND p_complete_flag = 'N'
521 AND l_open_receivables_flag = 'Y'
522 )
523 OR
524 (
525 p_complete_flag = 'Y'
526 AND l_open_receivables_flag = 'Y'
527 AND l_true_open_receivables_flag = 'N'
528 )
529 ) THEN
530 /* now we want to make the call to check the activity flag */
531 /* Bug 643716: pass p_previous_customer_trx_id instead of */
532 /* l_previous_customer_trx_id */
533
534 l_activity_flag := arpt_sql_func_util.get_activity_flag(
535 p_customer_trx_id,
536 l_true_open_receivables_flag,
537 p_complete_flag,
538 l_type,
539 l_initial_customer_trx_id,
540 p_previous_customer_trx_id );
541
542 /* if there is activity, we do not want the ps recreated */
543
544 IF (l_activity_flag = 'N') then
545
546 --apandit : Bug 2641517
547 --Before calls to any maintain payment schedules routine
548 --insert the ps image in the history table.
549
550 l_counter := 0;
551 OPEN get_existing_ps (p_customer_trx_id);
552 LOOP
553 FETCH get_existing_ps INTO
554 l_trx_sum_hist_rec.customer_trx_id,
555 l_trx_sum_hist_rec.payment_schedule_id,
556 l_trx_sum_hist_rec.currency_code,
557 l_trx_sum_hist_rec.due_date,
558 l_trx_sum_hist_rec.amount_in_dispute,
559 l_trx_sum_hist_rec.amount_due_original,
560 l_trx_sum_hist_rec.amount_due_remaining,
561 l_trx_sum_hist_rec.amount_adjusted,
562 l_trx_sum_hist_rec.customer_id,
563 l_trx_sum_hist_rec.site_use_id,
564 l_trx_sum_hist_rec.trx_date,
565 l_amt_credited,
566 l_stat;
567
568 l_trx_sum_hist_rec.installments := null;
569
570 IF get_existing_ps%NOTFOUND THEN
571 EXIT;
572 END IF;
573
574 l_counter := nvl(l_counter,0) + 1;
575
576 /* This second select on the ar_payment_schedules
577 has been placed here temporarily. Going forward this
578 needs to be changed, we need to use analytic function in the
579 cursor get_existing_ps and get the count from there itself
580 */
581 IF l_counter = 1 THEN
582
583 select count(*)
584 into l_trx_sum_hist_rec.installments
585 from ar_payment_schedules_all
586 where customer_trx_id = p_customer_trx_id;
587
588 END IF;
589
590 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
591 l_history_id,
592 l_type,
593 'INCOMPLETE_TRX');
594 -- for credit memos... since we have only one payment schedule
595 -- so raising the business event outside this loop is ok.
596 IF l_type <> 'CM' THEN
597 AR_BUS_EVENT_COVER.Raise_Trx_Incomplete_Event
598 (l_type,
599 p_customer_trx_id,
600 l_trx_sum_hist_rec.payment_schedule_id,
601 l_history_id,
602 l_prev_cust_old_state --contains null value
603 );
604 END IF;
605
606 END LOOP;
607
608 CLOSE get_existing_ps;
609
610 IF l_previous_customer_trx_id IS NOT NULL THEN
611
612
613 FOR i in get_existing_ps(l_previous_customer_trx_id) LOOP
614 l_prev_cust_old_state(i.payment_schedule_id).amount_due_remaining
615 := i.amount_due_remaining;
616 l_prev_cust_old_state(i.payment_schedule_id).status := i.status;
617 l_prev_cust_old_state(i.payment_schedule_id).amount_credited
618 := i.amount_credited;
619
620 END LOOP;
621
622 END IF;
623
624
625 arp_maintain_ps.maintain_payment_schedules(
626 'D',
627 p_customer_trx_id,
628 NULL, -- ps_id
629 NULL, -- line_amount
630 NULL, -- tax_amount
631 NULL, -- frt_amount
632 NULL, -- charge_amount
633 l_applied_commitment_amt
634 );
635
636 IF l_type = 'CM' THEN
637 AR_BUS_EVENT_COVER.Raise_Trx_Incomplete_Event
638 (l_type,
639 p_customer_trx_id,
640 l_trx_sum_hist_rec.payment_schedule_id,
641 l_history_id,
642 l_prev_cust_old_state);
643 END IF;
644
645 ELSE
646 FND_MESSAGE.Set_Name('AR','AR_TW_NO_RECREATE_PS');
647 APP_EXCEPTION.Raise_Exception;
648 END IF;
649
650 END IF;
651
652 end if; /* if p_form_name <> 'ARPBFBIB' */
653
654 -----------------------------------------------------------------------
655 -- IF complete flag is unchanged
656 -- AND complete flag is Y
657 -- AND amount, currency or term has changed
658 -- AND open_rec_flag = 'Y'
659 -- THEN
660 -- update payment schedules
661 -- IF CM against transaction THEN
662 -- update the invoice's payment schedules
663 -- update receivable application record
664 -- END IF;
665 -- END IF;
666 -----------------------------------------------------------------------
667
668 /*----------------------------------------------------------------------------------+
669 | Bug Fix 586968 - srangara -- Get l_recreate_ps_flag to check if payment |
670 | schedules need to be re-created |
671 | Logic for bug 586371 has been moved so that it is done after we get the |
672 | l_recreate_ps_flag. |
673 +----------------------------------------------------------------------------------*/
674
675 /*--------------------------------------------------------------------------+
676 | Bug Fix 620760: when no-post-to-GL, the gl_date in |
677 | ra_cust_trx_line_gl_dist is NULL. We need to check |
678 | for this before comparing to see if the gl_date has |
679 | changed. |
680 +--------------------------------------------------------------------------*/
681
682 select DECODE(
683 ctt.accounting_affect_flag,
684 'Y', DECODE(
685 SUM( ps.amount_due_original ),
686 lgd.amount,
687 DECODE( ct.term_id,
688 MAX( ps.term_id ),
689 DECODE(ct.invoice_currency_code,
690 MAX(ps.invoice_currency_code),
691 DECODE(NVL(ct.exchange_rate,1),
692 NVL(MAX(ps.exchange_rate),1),
693 DECODE(ct.exchange_date,
694 MAX(ps.exchange_date),
695 DECODE(ct.exchange_rate_type,
696 MAX(ps.exchange_rate_type),
697 DECODE(ct.bill_to_customer_id,
698 MAX(ps.customer_id),
699 DECODE(ct.bill_to_site_use_id,
700 MAX(ps.customer_site_use_id),
701 DECODE(ct.cust_trx_type_id,
702 MAX(ps.cust_trx_type_id),
703 DECODE(ctt.post_to_gl, 'Y',
704 DECODE(lgd.gl_date,
705 MAX(ps.gl_date),
706 'N', 'Y'),
707 'N'),
708 'Y'),
709 'Y'),
710 'Y'),
711 'Y'),
712 'Y'),
713 'Y'),
714 'Y'),
715 'Y'),
716 'Y'),
717 'N'
718 ), -- amount, currency, terms, bill to address or customer changed
719 DECODE(ctt.accounting_affect_flag,
720 'Y', DECODE( ct.term_id,
721 MAX( ps.term_id ),'N', 'Y'))
722 INTO l_recreate_ps_flag,
723 l_term_changed_flag
724 FROM ar_payment_schedules ps,
725 ra_cust_trx_types ctt,
726 ra_cust_trx_line_gl_dist lgd,
727 ra_customer_trx ct
728 WHERE ct.customer_trx_id = ps.customer_trx_id(+)
729 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
730 AND ct.customer_trx_id = lgd.customer_trx_id
731 AND lgd.account_class = 'REC'
732 AND lgd.latest_rec_flag = 'Y'
733 AND ct.customer_trx_id = p_customer_trx_id
734 GROUP BY ctt.accounting_affect_flag,
735 ct.complete_flag,
736 ct.term_id,
737 ct.invoice_currency_code,
738 lgd.amount,
739 ct.exchange_rate,
740 ct.exchange_date,
741 ct.exchange_rate_type,
742 ct.bill_to_customer_id,
743 ct.bill_to_site_use_id,
744 lgd.gl_date,
745 ct.cust_trx_type_id,
746 ctt.post_to_gl;
747
748
749 arp_util.debug('Recreate Paysched Flag = ' || l_recreate_ps_flag );
750
751 /* if the l_recreate_ps_flag is set to Y then we want to see if there
752 the transaction has any activity. To do this, we need to get some
753 information about the transaction and then get the activity flag.
754 This is for Bug 586371 */
755
756 IF ( l_recreate_ps_flag = 'Y' ) then
757
758 -- check on activity is already done in ARPBFBIB, we don't need to do it again
759 IF p_form_name = 'ARPBFBIB' THEN
760 l_activity_flag := 'N';
761 ELSE
762
763 SELECT ctt.type,
764 ct.initial_customer_trx_id,
765 ct.previous_customer_trx_id
766 INTO
767 l_type,
768 l_initial_customer_trx_id,
769 l_previous_customer_trx_id
770 from
771 ra_customer_trx ct,
772 ra_cust_trx_types ctt
773 WHERE
774 ct.customer_trx_id = p_customer_trx_id
775 and
776 ct.cust_trx_type_id = ctt.cust_trx_type_id;
777
778 /* now we want to make the call to get the activity flag */
779
780 l_activity_flag := arpt_sql_func_util.get_activity_flag(
781 p_customer_trx_id,
782 l_true_open_receivables_flag,
783 p_complete_flag,
784 l_type,
785 l_initial_customer_trx_id,
786 l_previous_customer_trx_id );
787 END IF;
788
789 END IF;
790
791 IF( p_complete_flag = l_old_complete_flag AND
792 p_complete_flag = 'Y' AND
793 l_recreate_ps_flag = 'Y' AND
794 l_open_receivables_flag = 'Y' AND
795 l_activity_flag = 'N' ) THEN
796
797 --Bug 2641517 : apandit
798 IF nvl(l_term_changed_flag,0) = 'Y' THEN
799 OPEN get_existing_ps (p_customer_trx_id);
800
801 l_counter := 0;
802 LOOP
803 FETCH get_existing_ps INTO
804 l_trx_sum_hist_rec.customer_trx_id,
805 l_trx_sum_hist_rec.payment_schedule_id,
806 l_trx_sum_hist_rec.currency_code,
807 l_trx_sum_hist_rec.due_date,
808 l_trx_sum_hist_rec.amount_in_dispute,
809 l_trx_sum_hist_rec.amount_due_original,
810 l_trx_sum_hist_rec.amount_due_remaining,
811 l_trx_sum_hist_rec.amount_adjusted,
812 l_trx_sum_hist_rec.customer_id,
813 l_trx_sum_hist_rec.site_use_id,
814 l_trx_sum_hist_rec.trx_date,
815 l_amt_credited,
816 l_stat;
817
818 l_trx_sum_hist_rec.installments := null;
819
820 IF get_existing_ps%NOTFOUND THEN
821 EXIT;
822 END IF;
823 l_counter := nvl(l_counter,0) +1;
824
825 /* This second select on the ar_payment_schedules
826 has been placed here temporarily. Going forward this
827 needs to be changed, we need to use analytic function in the
828 cursor get_existing_ps and get the count from there itself
829 */
830
831 IF l_counter = 1 THEN
832
833 select count(*)
834 into l_trx_sum_hist_rec.installments
835 from ar_payment_schedules_all
836 where customer_trx_id = p_customer_trx_id;
837
838 END IF;
839
840
841 --This flow will never be executed for the credit memo case
842 --as we do not change term on the credit memo.
843
844 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
845 l_history_id,
846 l_type,
847 'INCOMPLETE_TRX');
848
849 --Raise the Incompletion business event.
850 AR_BUS_EVENT_COVER.Raise_Trx_Incomplete_Event
851 (l_type,
852 p_customer_trx_id,
853 l_trx_sum_hist_rec.payment_schedule_id,
854 l_history_id,
855 l_prev_cust_old_state --null value
856 );
857 END LOOP;
858 CLOSE get_existing_ps;
859
860 END IF; --l_term_changed_flag
861
862 arp_maintain_ps.maintain_payment_schedules(
863 'U',
864 p_customer_trx_id,
865 NULL, -- ps_id
866 NULL, -- line_amount
867 NULL, -- tax_amount
868 NULL, -- frt_amount
869 NULL, -- charge_amount
870 l_applied_commitment_amt
871 );
872
873 --apandit
874 --Bug 2641517
875 IF nvl(l_term_changed_flag,0) = 'Y' THEN
876 --Raise the Complete business event.
877 AR_BUS_EVENT_COVER.Raise_Trx_Creation_Event
878 (l_type,
879 p_customer_trx_id,
880 l_prev_cust_old_state);
881 END IF;
882
883 END IF;
884
885 /* if the recreation flag is Y but there is activity then raise an
886 exception. for Bug 586371 */
887
888 IF ( l_recreate_ps_flag = 'Y' and l_activity_flag = 'Y') then
889 FND_MESSAGE.Set_Name('AR','AR_TW_NO_RECREATE_PS');
890 APP_EXCEPTION.Raise_Exception;
891 END IF;
892
893 -----------------------------------------------------------------------
894 -- IF complete flag is unchanged AND complete flag is N
895 -- THEN do nothing
896 -- END IF;
897 -----------------------------------------------------------------------
898
899 arp_util.print_fcn_label( 'arp_process_header.post_commit()- ');
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 arp_util.debug('EXCEPTION: arp_process_header.post_commit()');
904
905 arp_util.debug('');
906 arp_util.debug('---------- parameters for post_commit() ---------');
907
908 arp_util.debug('p_form_name = ' ||
909 p_form_name);
910 arp_util.debug('p_form_version = ' ||
911 p_form_version);
912 arp_util.debug('p_customer_trx_id = ' ||
913 p_customer_trx_id);
914 arp_util.debug('p_previous_customer_trx_id = ' ||
915 p_previous_customer_trx_id);
916 arp_util.debug('p_complete_flag = ' ||
917 p_complete_flag);
918 arp_util.debug('p_trx_open_receivables_flag = ' ||
919 p_trx_open_receivables_flag);
920 arp_util.debug('p_prev_open_receivables_flag = ' ||
921 p_prev_open_receivables_flag);
922 arp_util.debug('p_creation_sign = ' ||
923 p_creation_sign);
924 arp_util.debug('p_allow_overapplication_flag = ' ||
925 p_allow_overapplication_flag);
926 arp_util.debug('p_natural_application_flag = ' ||
927 p_natural_application_flag);
928
929 RAISE;
930
931 END;
932
933
934 /*---------------------------------------------+
935 | Package initialization section. |
936 +---------------------------------------------*/
937 PROCEDURE init IS
938 BEGIN
939
940 pg_base_precision := arp_global.base_precision;
941 pg_base_min_acc_unit := arp_global.base_min_acc_unit;
942 pg_trx_header_level_rounding := arp_global.sysparam.trx_header_level_rounding;
943 END init;
944
945 BEGIN
946 init;
947 END ARP_PROCESS_HEADER_POST_COMMIT;
[Home] [Help]