[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insertion or update of a transaction has occurred. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| arp_maintain_ps.maintain_payment_schedules |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-AUG-95 Charlie Tomberg Created |
| 19-FEB-96 Oliver Steinmeier Changed logic in post-commit to |
| make sure the payment schedule |
| gets called for debit memos |
| 05-05-97 OSTEINME Changed post_commit logic to recreate |
| payment schedules if the exchange |
| rate, exchange date, or exchange type |
| was changed (bug 486369) |
| 08-26-97 D. Jancis Changed post commit logic to recreate |
| payment schedules if the bill to |
| customer or the bill to address was |
| changed (Bug 520221 ) |
| 10-14-97 D. Jancis Changed post commit logic to |
| recreate payment schedules if the |
| GL-Date or transaction type |
| was changed (Bug 564308 and 562342) |
| 11-14-97 D. Jancis Added code to retrieve necessary |
| values to call the arpt_sql_func_util.|
| get_activity_flag routine. If for |
| some reason, the flag to recreate the |
| payment schedule is Y and there is |
| activity against the trans., an error |
| is raised. bug 586371 |
| 11-19-97 Sai Rangarajan Bug Fix 586968 - Changed logic to |
| to check for recreation of payment |
| schedules until after the |
| gl_line_dist records are updated. |
| also moved code written for bug 586371|
| 12-17-97 D. Jancis Bug Fix 598442 - changed logic to |
| check for differences in exchange |
| with the posibility of a value being |
| NULL. also added addition check |
| to see if there is activity against |
| trans do not 'delete/recreate' ps |
| 03-23-98 Tasman Tang Bug Fix 643716 - passed |
| p_previous_customer_trx_id instead of |
| l_previous_customer_trx_id in first |
| call to get_activity_flag since the |
| local var is never initialized |
| 28-Sep-01 Pravin Pawar Bug NO :1915785 - Added the message |
| 'AR_PLCRE_FHLR_CCID' , to prompt the |
| user to enable the Header Level |
| Rounding Account |
| 01-FEB-02 M Raymond Bug 2164863 - added a parameter
| to do_completion_checking routine and
| added a second call to that routine
| after the rounding was completed.
| Also substituted arp_trx_complete_chk
| for arp_trx_validate.
| 02-DEC-04 V Crisostomo Bug3049044/3041195: Changed
| arp_trx_validate.check_sign_and_overapp
| to take p_error_mode and p_error_count
| to allow for better error handling.
+===========================================================================*/
PROCEDURE post_commit( p_form_name IN varchar2,
p_form_version IN number,
p_customer_trx_id IN
ra_customer_trx.customer_trx_id%type,
p_previous_customer_trx_id IN
ra_customer_trx.previous_customer_trx_id%type,
p_complete_flag IN
ra_customer_trx.complete_flag%type,
p_trx_open_receivables_flag IN
ra_cust_trx_types.accounting_affect_flag%type,
p_prev_open_receivables_flag IN
ra_cust_trx_types.accounting_affect_flag%type,
p_creation_sign IN
ra_cust_trx_types.creation_sign%type,
p_allow_overapplication_flag IN
ra_cust_trx_types.allow_overapplication_flag%type,
p_natural_application_flag IN
ra_cust_trx_types.natural_application_only_flag%type,
p_cash_receipt_id IN
ar_cash_receipts.cash_receipt_id%type DEFAULT NULL,
p_error_mode IN VARCHAR2
) IS
l_scredit_count NUMBER;
SELECT customer_trx_id,
payment_schedule_id,
invoice_currency_code,
due_date,
amount_in_dispute,
amount_due_original,
amount_due_remaining,
amount_adjusted,
customer_id,
customer_site_use_id,
trx_date,
amount_credited,
status
FROM ar_payment_schedules
WHERE customer_trx_id = p_cust_trx_id;
/*3463885 Added two cols reversed_cash_receipt_id to be selected*/
SELECT DECODE(
ctt.accounting_affect_flag,
'Y', 'Y',
DECODE(
COUNT(ps.payment_schedule_id),
0, 'N',
'Y'
)
), -- Open Receivables Flag
DECODE(
ctt.accounting_affect_flag,
'Y', DECODE(
COUNT(ps.payment_schedule_id),
0, 'N',
'Y'
),
NULL
),
ctt.accounting_affect_flag,
ps.reversed_cash_receipt_id,
ct.reversed_cash_receipt_id
INTO l_open_receivables_flag,
l_old_complete_flag,
l_true_open_receivables_flag,
l_ps_rev_cash_id,
l_ct_rev_cash_id
FROM ar_payment_schedules ps,
ra_cust_trx_types ctt,
ra_cust_trx_line_gl_dist lgd,
ra_customer_trx ct
WHERE ct.customer_trx_id = ps.customer_trx_id(+)
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.customer_trx_id = lgd.customer_trx_id
AND lgd.account_class = 'REC'
AND lgd.latest_rec_flag = 'Y'
AND ct.customer_trx_id = p_customer_trx_id
GROUP BY ctt.accounting_affect_flag,
ct.complete_flag,
ct.term_id,
ct.invoice_currency_code,
lgd.amount,
ct.exchange_rate,
ct.exchange_date,
ct.exchange_rate_type,
ct.bill_to_customer_id,
ct.bill_to_site_use_id,
lgd.gl_date,
ct.cust_trx_type_id,
ps.reversed_cash_receipt_id,
ct.reversed_cash_receipt_id;
arp_util.debug('Complete Flag Before Update = ' || ARP_PROCESS_HEADER_UPDTE_COVER.g_old_complete_flag); /* manishri, 08-Jun-2010. Bug 9693686*/
UPDATE RA_CUSTOMER_TRX SET REVERSED_CASH_RECEIPT_ID = l_ps_rev_cash_id
WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
SELECT ctt.type,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
INTO
l_type,
l_initial_customer_trx_id,
l_previous_customer_trx_id
from
ra_customer_trx ct,
ra_cust_trx_types ctt
WHERE
ct.customer_trx_id = p_customer_trx_id
and
ct.cust_trx_type_id = ctt.cust_trx_type_id;
arp_standard.debug('Calling maintain_payment_schedules EH in insert mode');
/* This second select on the ar_payment_schedules
has been placed here temporarily. Going forward this
needs to be changed, we need to use analytic function in the
cursor get_existing_ps and get the count from there itself
*/
IF l_counter = 1 THEN
select count(*)
into l_trx_sum_hist_rec.installments
from ar_payment_schedules_all
where customer_trx_id = p_customer_trx_id;
AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
l_history_id,
l_type,
'INCOMPLETE_TRX');
select DECODE(
ctt.accounting_affect_flag,
'Y', DECODE(
SUM( ps.amount_due_original ),
lgd.amount,
DECODE( ct.term_id,
MAX( ps.term_id ),
DECODE(ct.invoice_currency_code,
MAX(ps.invoice_currency_code),
DECODE(NVL(ct.exchange_rate,1),
NVL(MAX(ps.exchange_rate),1),
DECODE(ct.exchange_date,
MAX(ps.exchange_date),
DECODE(ct.exchange_rate_type,
MAX(ps.exchange_rate_type),
DECODE(ct.bill_to_customer_id,
MAX(ps.customer_id),
DECODE(ct.bill_to_site_use_id,
MAX(ps.customer_site_use_id),
DECODE(ct.cust_trx_type_id,
MAX(ps.cust_trx_type_id),
DECODE(ctt.post_to_gl, 'Y',
DECODE(lgd.gl_date,
MAX(ps.gl_date),
'N', 'Y'),
'N'),
'Y'),
'Y'),
'Y'),
'Y'),
'Y'),
'Y'),
'Y'),
'Y'),
'Y'),
'N'
), -- amount, currency, terms, bill to address or customer changed
DECODE(ctt.accounting_affect_flag,
'Y', DECODE( ct.term_id,
MAX( ps.term_id ),'N', 'Y'))
INTO l_recreate_ps_flag,
l_term_changed_flag
FROM ar_payment_schedules ps,
ra_cust_trx_types ctt,
ra_cust_trx_line_gl_dist lgd,
ra_customer_trx ct
WHERE ct.customer_trx_id = ps.customer_trx_id(+)
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.customer_trx_id = lgd.customer_trx_id
AND lgd.account_class = 'REC'
AND lgd.latest_rec_flag = 'Y'
AND ct.customer_trx_id = p_customer_trx_id
GROUP BY ctt.accounting_affect_flag,
ct.complete_flag,
ct.term_id,
ct.invoice_currency_code,
lgd.amount,
ct.exchange_rate,
ct.exchange_date,
ct.exchange_rate_type,
ct.bill_to_customer_id,
ct.bill_to_site_use_id,
lgd.gl_date,
ct.cust_trx_type_id,
ctt.post_to_gl;
SELECT ctt.type,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
INTO
l_type,
l_initial_customer_trx_id,
l_previous_customer_trx_id
from
ra_customer_trx ct,
ra_cust_trx_types ctt
WHERE
ct.customer_trx_id = p_customer_trx_id
and
ct.cust_trx_type_id = ctt.cust_trx_type_id;
/* This second select on the ar_payment_schedules
has been placed here temporarily. Going forward this
needs to be changed, we need to use analytic function in the
cursor get_existing_ps and get the count from there itself
*/
IF l_counter = 1 THEN
select count(*)
into l_trx_sum_hist_rec.installments
from ar_payment_schedules_all
where customer_trx_id = p_customer_trx_id;
AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
l_history_id,
l_type,
'INCOMPLETE_TRX');