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