[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT largest_inv_amount
FROM ar_trx_summary
WHERE cust_account_id = p_cust_acct_id
AND site_use_id = nvl(p_site_use_id,-99)
AND currency = p_currency
AND as_of_date = p_date
FOR UPDATE;
SELECT last_payment_date,last_payment_number
FROM ar_trx_bal_summary
WHERE cust_account_id = p_cust_acct_id
AND site_use_id = nvl(p_site_use_id,-9999)
AND currency = p_currency
FOR UPDATE;
PROCEDURE Update_recapp_info(l_trx_class IN VARCHAR2,
l_trx_customer_id IN NUMBER,
l_trx_site_use_id IN NUMBER,
l_trx_currency_code IN VARCHAR2,
l_trx_amt IN NUMBER,
l_op_trx_count IN NUMBER,
l_rcpt_customer_id IN NUMBER,
l_rcpt_site_use_id IN NUMBER,
l_rcpt_currency_code IN VARCHAR2,
l_rcpt_amt IN NUMBER,
l_apply_date IN DATE,
l_edisc_value IN NUMBER,
l_edisc_count IN NUMBER,
l_uedisc_value IN NUMBER,
l_uedisc_count IN NUMBER,
l_inv_paid_amt IN NUMBER,
l_inv_inst_pmt_days_sum IN NUMBER,
l_sum_app_amt_days_late IN NUMBER,
l_sum_app_amt IN NUMBER,
l_count_of_tot_inv_inst_paid IN NUMBER,
l_count_of_inv_inst_paid_late IN NUMBER,
l_count_of_disc_inv_inst IN NUMBER,
l_unresolved_cash_value IN NUMBER,
l_unresolved_cash_count IN NUMBER,
l_op_cm_count IN NUMBER , --this is relevant to credit memo applications
l_app_type IN VARCHAR2,
l_past_due_inv_value IN NUMBER,
l_past_due_inv_inst_count IN NUMBER,
l_org_id IN NUMBER
) IS
BEGIN
IF pg_debug = 'Y'
THEN
debug ('AR_BUS_EVENT_SUB_PVT.Update_recapp_info(+)');
UPDATE ar_trx_bal_summary
set OP_CREDIT_MEMOS_VALUE
= nvl(OP_CREDIT_MEMOS_VALUE,0)
+ DECODE(l_trx_class,'CM',
nvl(l_trx_amt,0),0)
+ DECODE(l_app_type,'CM',l_trx_amt,0),
OP_CREDIT_MEMOS_COUNT = nvl(OP_CREDIT_MEMOS_COUNT,0)
- DECODE(l_trx_class,'CM',
nvl(l_op_trx_count,0),0)
- DECODE(l_app_type,'CM',l_op_cm_count,0),
OP_INVOICES_VALUE = nvl(OP_INVOICES_VALUE,0)
- DECODE(l_trx_class,'INV',
nvl(l_trx_amt,0),0),
OP_INVOICES_COUNT = nvl(OP_INVOICES_COUNT,0)
- DECODE(l_trx_class, 'INV',
nvl(l_op_trx_count,0),0),
OP_DEBIT_MEMOS_VALUE = nvl(OP_DEBIT_MEMOS_VALUE,0)
- DECODE(l_trx_class,'DM',
nvl(l_trx_amt,0),0),
OP_DEBIT_MEMOS_COUNT = nvl(OP_DEBIT_MEMOS_COUNT,0)
- DECODE(l_trx_class, 'DM',
nvl(l_op_trx_count,0),0),
OP_DEPOSITS_VALUE = nvl(OP_DEPOSITS_VALUE,0)
- DECODE(l_trx_class,'DEP',
nvl(l_trx_amt,0),0),
OP_DEPOSITS_COUNT = nvl(OP_DEPOSITS_COUNT,0)
- DECODE(l_trx_class, 'DEP',
nvl(l_op_trx_count,0),0),
OP_CHARGEBACK_VALUE = nvl(OP_CHARGEBACK_VALUE,0)
- DECODE(l_trx_class,'CB',
nvl(l_trx_amt,0),0),
OP_CHARGEBACK_COUNT = nvl(OP_CHARGEBACK_COUNT,0)
- DECODE(l_trx_class, 'CB',
nvl(l_op_trx_count,0),0),
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0) -
DECODE(l_trx_class,'INV',
nvl(l_past_due_inv_value,0),0),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0)
- DECODE(l_trx_class,'INV',
nvl(l_past_due_inv_inst_count,0),0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_trx_customer_id
and site_use_id = l_trx_site_use_id
and currency = l_trx_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
UPDATE ar_trx_bal_summary
set UNRESOLVED_CASH_VALUE = nvl(UNRESOLVED_CASH_VALUE,0)
+ nvl(l_unresolved_cash_value,0),
UNRESOLVED_CASH_COUNT = nvl(UNRESOLVED_CASH_COUNT,0) -
nvl(l_unresolved_cash_count,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_rcpt_customer_id
and site_use_id = l_rcpt_site_use_id
and currency = l_rcpt_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
UPDATE ar_trx_summary
set INV_PAID_AMOUNT = nvl(INV_PAID_AMOUNT,0)
+ nvl(l_inv_paid_amt,0),
INV_INST_PMT_DAYS_SUM = nvl(INV_INST_PMT_DAYS_SUM,0)
+ nvl(l_inv_inst_pmt_days_sum,0),
TOTAL_EARNED_DISC_VALUE = nvl(TOTAL_EARNED_DISC_VALUE,0)
+ nvl(l_edisc_value,0),
TOTAL_EARNED_DISC_COUNT = nvl(TOTAL_EARNED_DISC_COUNT,0)
+ nvl(l_edisc_count,0),
TOTAL_UNEARNED_DISC_VALUE = nvl(TOTAL_UNEARNED_DISC_VALUE,0)
+ nvl(l_uedisc_value,0),
TOTAL_UNEARNED_DISC_COUNT = nvl(TOTAL_UNEARNED_DISC_COUNT,0)
+ nvl(l_uedisc_count,0),
SUM_APP_AMT_DAYS_LATE = nvl(SUM_APP_AMT_DAYS_LATE,0)
+ nvl(l_SUM_APP_AMT_DAYS_LATE,0),
SUM_APP_AMT = nvl(SUM_APP_AMT,0) + nvl(l_sum_app_amt,0),
COUNT_OF_TOT_INV_INST_PAID = nvl(COUNT_OF_TOT_INV_INST_PAID,0)
+ nvl(l_count_of_tot_inv_inst_paid,0),
COUNT_OF_INV_INST_PAID_LATE = nvl(COUNT_OF_INV_INST_PAID_LATE,0)
+ nvl(l_count_of_inv_inst_paid_late,0),
COUNT_OF_DISC_INV_INST = nvl(COUNT_OF_DISC_INV_INST,0) +
nvl(l_count_of_disc_inv_inst,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
-- DAYS_CREDIT_GRANTED_SUM = nvl(DAYS_CREDIT_GRANTED_SUM)
WHERE cust_account_id = l_rcpt_customer_id
and site_use_id = l_rcpt_site_use_id
and currency = l_rcpt_currency_code
and as_of_date = l_apply_date
and NVL(org_id,'-99') = NVL(l_org_id,-99);
debug ('AR_BUS_EVENT_SUB_PVT.Update_recapp_info(-)');
END Update_recapp_info;
PROCEDURE Update_summary_for_request_id (p_request_id IN NUMBER)
IS
CURSOR get_req_run_data(p_req_id IN NUMBER) IS
Select ps.class,
ps.customer_id,
ps.customer_site_use_id,
ps.trx_date,
ps.invoice_currency_code,
ps.org_id,
ps.due_date,
ps.customer_trx_id ,
trx.previous_customer_trx_id,
ctt.type prev_trx_type,
ps.terms_sequence_number,
ps.amount_due_original,
trx_sum.largest_inv_amount largest_inv_amount,
trx_sum.largest_inv_date largest_inv_date,
trx_sum.largest_inv_cust_trx_id largest_inv_cust_trx_id,
count(nvl(rtl.term_id,1)) installment_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'INV',
decode(cm_app_ps.status,'CL',1,null))))
cm_closed_inv_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'DM',
decode(cm_app_ps.status,'CL',1,null))))
cm_closed_dm_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'CM',
decode(cm_app_ps.status,'CL',1,null))))
cm_closed_cm_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'INV',
decode(cm_app_ps.status,'CL',
decode(sign( cm_app_ps.due_date - trunc(sysdate)),-1,1,null)
)
)
)) cm_cl_past_due_inv_ct,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'INV',
decode(cm_app_ps.status,'CL',
decode(sign( cm_app_ps.due_date - trunc(sysdate)),-1,
ra_cm.amount_applied,null)
)
)
)) cm_cl_past_due_inv_amt
from ra_customer_trx trx,
ar_payment_schedules ps,
ra_customer_trx prev_trx,
ra_cust_trx_types ctt,
ra_terms rt,
ra_terms_lines rtl,
ar_receivable_applications_all ra_cm,
ar_payment_schedules_all cm_app_ps,
ar_trx_summary trx_sum
where trx.customer_trx_id = ps.customer_trx_id
and trx.request_id = p_req_id
and trx.previous_customer_trx_id = prev_trx.customer_trx_id(+)
and prev_trx.cust_trx_type_id = ctt.cust_trx_type_id(+)
and rt.term_id(+) = ps.term_id
and rt.term_id = rtl.term_id(+)
and trx.customer_trx_id = ra_cm.customer_trx_id(+)
and ra_cm.applied_payment_schedule_id = cm_app_ps.payment_schedule_id(+)
and trx_sum.cust_account_id(+) = trx.bill_to_customer_id
and trx_sum.site_use_id(+) = trx.bill_to_site_use_id
and trx_sum.currency(+) = trx.invoice_currency_code
and trx_sum.as_of_date(+) = trx.trx_date
and trx_sum.org_id (+) = trx.org_id
group by ps.class,
ps.customer_id,
ps.customer_site_use_id,
ps.trx_date,
ps.invoice_currency_code,
ps.org_id,
ps.due_date,
ps.customer_trx_id ,
trx.previous_customer_trx_id,
ctt.type,
ps.terms_sequence_number,
ps.amount_due_original,
trx_sum.largest_inv_amount,
trx_sum.largest_inv_date ,
trx_sum.largest_inv_cust_trx_id
order by ps.customer_trx_id,ps.terms_sequence_number;
debug ('AR_BUS_EVENT_SUB_PVT.Update_summary_for_request_id(+)');
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+DECODE(sign(rec.due_date - trunc(sysdate)),-1,0,
rec.amount_due_original),
OP_INVOICES_VALUE
= nvl(OP_INVOICES_VALUE,0)
+ DECODE(rec.class , 'INV' , rec.amount_due_original,
'CM', decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
rec.amount_due_original,0)),0),
OP_INVOICES_COUNT
= nvl(OP_INVOICES_COUNT,0) +
DECODE(rec.class,'INV',1,'CM',
decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
-rec.cm_closed_inv_count)),0),
PAST_DUE_INV_VALUE
= nvl(PAST_DUE_INV_VALUE,0) + decode(rec.class , 'INV',
decode(sign(rec.due_date - trunc(sysdate)),-1,
rec.amount_due_original,0),'CM',
decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
rec.cm_cl_past_due_inv_amt,0)),0),
PAST_DUE_INV_INST_COUNT
= nvl(PAST_DUE_INV_INST_COUNT,0) + decode(rec.class,'INV',
decode(sign(rec.due_date - trunc(sysdate)),-1,1,0),
'CM', decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
-rec.cm_cl_past_due_inv_ct,0)),0),
OP_CREDIT_MEMOS_VALUE
= nvl(OP_CREDIT_MEMOS_VALUE,0) + DECODE(rec.class,'CM',
decode(rec.previous_customer_trx_id, null,
rec.amount_due_original),0),
OP_CREDIT_MEMOS_COUNT
= nvl(OP_CREDIT_MEMOS_COUNT,0) +
DECODE(rec.class,'CM',
DECODE(rec.previous_customer_trx_id, null,1,
DECODE(rec.prev_trx_type,'CM',
-rec.cm_closed_cm_count,0)),0),
OP_DEBIT_MEMOS_VALUE
= nvl(OP_DEBIT_MEMOS_VALUE,0) + DECODE(rec.class , 'DM',
rec.amount_due_original,'CM',
DECODE(rec.previous_customer_trx_id, null,0,
DECODE(rec.prev_trx_type,'DM',
rec.amount_due_original,0)),0),
OP_DEBIT_MEMOS_COUNT
= nvl(OP_DEBIT_MEMOS_COUNT,0)+ DECODE(rec.class,'DM',1,'CM',
DECODE(rec.previous_customer_trx_id, null,0,
DECODE(rec.prev_trx_type,'DM',
-rec.cm_closed_dm_count,0)),0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = rec.customer_id
and site_use_id = nvl(rec.customer_site_use_id,-99)
and currency = rec.invoice_currency_code
and NVL(org_id,'-99') = NVL(rec.org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_INVOICES_VALUE,
OP_INVOICES_COUNT,
PAST_DUE_INV_VALUE,
PAST_DUE_INV_INST_COUNT,
OP_CREDIT_MEMOS_VALUE,
OP_CREDIT_MEMOS_COUNT,
OP_DEBIT_MEMOS_VALUE,
OP_DEBIT_MEMOS_COUNT)
VALUES
( rec.customer_id,
nvl(rec.customer_site_use_id,-99),
rec.org_id,
rec.invoice_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
DECODE(sign(rec.due_date - trunc(sysdate)),-1,0,
rec.amount_due_original),
DECODE(rec.class , 'INV' , rec.amount_due_original,
'CM', decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
rec.amount_due_original,0)),0),
DECODE(rec.class,'INV',1,'CM',
decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
-rec.cm_closed_inv_count)),0),
-decode(rec.class , 'INV',
decode(sign(rec.due_date - trunc(sysdate)),-1,
rec.amount_due_original,0),'CM',
decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
rec.cm_cl_past_due_inv_amt,0)),0),
decode(rec.class,'INV',
decode(sign(rec.due_date - trunc(sysdate)),-1,1,0),
'CM', decode(rec.previous_customer_trx_id, null,0,
decode(rec.prev_trx_type,'INV',
-rec.cm_cl_past_due_inv_ct,0)),0),
DECODE(rec.class,'CM',
decode(rec.previous_customer_trx_id, null,
rec.amount_due_original),0),
DECODE(rec.previous_customer_trx_id, null,1,
DECODE(rec.prev_trx_type,'CM',
-rec.cm_closed_cm_count,0)),
DECODE(rec.class , 'DM',
rec.amount_due_original,'CM',
DECODE(rec.previous_customer_trx_id, null,0,
DECODE(rec.prev_trx_type,'DM',
rec.amount_due_original,0)),0),
DECODE(rec.class,'DM',1,'CM'
, DECODE(rec.previous_customer_trx_id, null,0,
DECODE(rec.prev_trx_type,'DM',
-rec.cm_closed_dm_count,0)),0)
);
UPDATE ar_trx_summary
SET OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) +
nvl(rec.amount_due_original,0),
TOTAL_INVOICES_VALUE
= DECODE(rec.class , 'INV',
(nvl(TOTAL_INVOICES_VALUE,0)
+ nvl(rec.amount_due_original,0)),
TOTAL_INVOICES_VALUE),
TOTAL_INVOICES_COUNT
= DECODE(rec.class,'INV',nvl(TOTAL_INVOICES_COUNT,0)+1,
TOTAL_INVOICES_COUNT),
LARGEST_INV_AMOUNT
= DECODE(rec.class , 'INV',
DECODE(sign(rec.installment_count - l_inst_counter),0,
DECODE(sign(l_trx_amount-nvl(LARGEST_INV_AMOUNT,0)),
1,l_trx_amount,LARGEST_INV_AMOUNT),
LARGEST_INV_AMOUNT), LARGEST_INV_AMOUNT),
LARGEST_INV_DATE = rec.trx_date,
LARGEST_INV_CUST_TRX_ID
= DECODE(rec.class , 'INV',
DECODE(sign(rec.installment_count - l_inst_counter),0,
DECODE(sign(l_trx_amount-nvl(LARGEST_INV_AMOUNT,0)),1,
rec.customer_trx_id,LARGEST_INV_CUST_TRX_ID),
LARGEST_INV_CUST_TRX_ID),LARGEST_INV_CUST_TRX_ID),
TOTAL_CREDIT_MEMOS_VALUE
= DECODE(rec.class,'CM',
nvl(TOTAL_CREDIT_MEMOS_VALUE,0)+rec.amount_due_original,
TOTAL_CREDIT_MEMOS_VALUE),
TOTAL_CREDIT_MEMOS_COUNT
= DECODE(rec.class,'CM',nvl(TOTAL_CREDIT_MEMOS_COUNT,0)+1,
TOTAL_CREDIT_MEMOS_COUNT),
TOTAL_DEBIT_MEMOS_VALUE
= DECODE(rec.class,'DM',
nvl(TOTAL_DEBIT_MEMOS_VALUE,0)+rec.amount_due_original,
TOTAL_DEBIT_MEMOS_VALUE),
TOTAL_DEBIT_MEMOS_COUNT
= DECODE(rec.class,'DM',nvl(TOTAL_DEBIT_MEMOS_COUNT,0)+1,
TOTAL_DEBIT_MEMOS_COUNT),
DAYS_CREDIT_GRANTED_SUM
= DECODE(rec.class,'INV',
nvl(DAYS_CREDIT_GRANTED_SUM,0) +
(rec.amount_due_original *
(rec.due_date - rec.trx_date)),
DAYS_CREDIT_GRANTED_SUM),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = rec.customer_id
AND site_use_id = nvl(rec.customer_site_use_id,-99)
AND currency = rec.invoice_currency_code
AND NVL(org_id,'-99') = NVL(rec.org_id,-99)
AND as_of_date = rec.trx_date;
INSERT INTO ar_trx_summary
( CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
OP_BAL_HIGH_WATERMARK,
TOTAL_INVOICES_VALUE,
TOTAL_INVOICES_COUNT,
LARGEST_INV_AMOUNT,
LARGEST_INV_DATE,
LARGEST_INV_CUST_TRX_ID ,
TOTAL_CREDIT_MEMOS_VALUE ,
TOTAL_CREDIT_MEMOS_COUNT ,
TOTAL_DEBIT_MEMOS_VALUE,
TOTAL_DEBIT_MEMOS_COUNT ,
DAYS_CREDIT_GRANTED_SUM)
VALUES
( rec.customer_id,
nvl(rec.customer_site_use_id,-99),
rec.org_id,
rec.invoice_currency_code,
rec.trx_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
rec.amount_due_original,
rec.amount_due_original,
DECODE(rec.class , 'INV',1,null),
DECODE(rec.class , 'INV',
DECODE(sign(rec.installment_count - l_inst_counter),0,
l_trx_amount,null),null),
rec.trx_date,
DECODE(rec.class , 'INV',
DECODE(sign(rec.installment_count - l_inst_counter),0,
rec.customer_trx_id,null),null),
DECODE(rec.class,'CM', rec.amount_due_original,null),
DECODE(rec.class,'CM',1,null),
DECODE(rec.class,'DM',rec.amount_due_original,null),
DECODE(rec.class,'DM',1, null),
DECODE(rec.class,'INV',
(rec.amount_due_original * (rec.due_date - rec.trx_date)),
null));
debug ('AR_BUS_EVENT_SUB_PVT.Update_summary_for_request_id(-)');
END Update_summary_for_request_id;
PROCEDURE Update_Adj_info (
l_customer_id IN NUMBER,
l_site_use_id IN NUMBER,
l_org_id IN NUMBER,
l_currency_code IN VARCHAR2,
l_adj_amount IN NUMBER,
l_op_trx_count IN NUMBER,
l_apply_date IN DATE,
l_pending_adj_amount IN NUMBER,
l_class IN VARCHAR2,
l_special_adj IN VARCHAR2 DEFAULT null,
l_past_due_inv_inst_count IN NUMBER,
l_past_due_inv_value IN NUMBER
) IS
BEGIN
IF pg_debug = 'Y'
THEN
debug ('AR_BUS_EVENT_SUB_PVT.Update_Adj_info(+)');
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+ nvl(l_adj_amount,0),
OP_CREDIT_MEMOS_VALUE
= nvl(OP_CREDIT_MEMOS_VALUE,0)
+ DECODE(l_class,'CM',
nvl(l_adj_amount,0),0),
OP_CREDIT_MEMOS_COUNT = nvl(OP_CREDIT_MEMOS_COUNT,0)
+ DECODE(l_class, 'CM',
nvl(l_op_trx_count,0),0),
OP_INVOICES_VALUE = nvl(OP_INVOICES_VALUE,0)
+ DECODE(l_class,'INV',
nvl(l_adj_amount,0),0),
OP_INVOICES_COUNT = nvl(OP_INVOICES_COUNT,0)
+ DECODE(l_class, 'INV',
nvl(l_op_trx_count,0),0),
OP_DEBIT_MEMOS_VALUE = nvl(OP_DEBIT_MEMOS_VALUE,0)
+ DECODE(l_class,'DM',
nvl(l_adj_amount,0),0),
OP_DEBIT_MEMOS_COUNT = nvl(OP_DEBIT_MEMOS_COUNT,0)
+ DECODE(l_class, 'DM',
nvl(l_op_trx_count,0),0),
OP_DEPOSITS_VALUE = nvl(OP_DEPOSITS_VALUE,0)
+ DECODE(l_class,'DEP',
nvl(l_adj_amount,0),0),
OP_DEPOSITS_COUNT = nvl(OP_DEPOSITS_COUNT,0)
+ DECODE(l_class, 'DEP',
nvl(l_op_trx_count,0),0),
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0) +
DECODE(l_class,'INV',
nvl(l_past_due_inv_value,0),0),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0)
+ DECODE(l_class,'INV',
nvl(l_past_due_inv_inst_count,0),0),
PENDING_ADJ_VALUE = nvl(PENDING_ADJ_VALUE,0)
+ DECODE(l_special_adj, 'Y', 0, nvl(l_pending_adj_amount,0)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_customer_id
and site_use_id = l_site_use_id
and currency = l_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_CREDIT_MEMOS_VALUE,
OP_CREDIT_MEMOS_COUNT,
OP_INVOICES_VALUE,
OP_INVOICES_COUNT,
OP_DEBIT_MEMOS_VALUE,
OP_DEBIT_MEMOS_COUNT,
OP_DEPOSITS_VALUE,
OP_DEPOSITS_COUNT,
PENDING_ADJ_VALUE)
VALUES
( l_customer_id,
l_site_use_id,
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
+nvl(l_adj_amount,0),
+ DECODE(l_class,'CM',nvl(l_adj_amount,0),0),
DECODE(l_class, 'CM', nvl(l_op_trx_count,0),0),
+ DECODE(l_class,'INV',nvl(l_adj_amount,0),0),
+ DECODE(l_class, 'INV', nvl(l_op_trx_count,0),0),
+ DECODE(l_class,'DM', nvl(l_adj_amount,0),0),
+ DECODE(l_class, 'DM', nvl(l_op_trx_count,0),0),
+ DECODE(l_class,'DEP', nvl(l_adj_amount,0),0),
+ DECODE(l_class, 'DEP', nvl(l_op_trx_count,0),0),
+ DECODE(l_special_adj, 'Y',null,nvl(l_pending_adj_amount,0))
);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0)
+ nvl(l_adj_amount,0),
TOTAL_ADJUSTMENTS_VALUE = nvl(TOTAL_ADJUSTMENTS_VALUE,0)
+ DECODE(l_special_adj, 'Y',0, nvl(l_adj_amount,0)),
TOTAL_ADJUSTMENTS_COUNT = nvl(TOTAL_ADJUSTMENTS_COUNT,0)
+ DECODE(l_special_adj, 'Y',0,nvl(l_op_trx_count,0)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = l_site_use_id
and currency = l_currency_code
and as_of_date = l_apply_date
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
TOTAL_ADJUSTMENTS_VALUE,
TOTAL_ADJUSTMENTS_COUNT,
OP_BAL_HIGH_WATERMARK
)
VALUES
( l_customer_id,
l_site_use_id,
l_org_id,
l_currency_code,
l_apply_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
nvl(l_adj_amount,0),
nvl(l_op_trx_count,0),
nvl(l_adj_amount,0) );
debug ('AR_BUS_EVENT_SUB_PVT.Update_Adj_info(-)');
END Update_Adj_info;
PROCEDURE Update_rcpt_app_info_for_req(p_req_id in number,
p_org_id in number)
IS
cursor create_recept_info(p_req_id IN NUMBER) IS
select rps.customer_id customer_id,
nvl(rps.customer_site_use_id, -99) site_use_id,
rps.invoice_currency_code rcpt_currency,
cr.receipt_date as_of_date,
sum(nvl(ra.amount_applied_from,ra.amount_applied)) receipt_amount
from ar_receivable_applications ra,
ar_payment_schedules rps,
ar_cash_receipts cr
where ra.request_id = p_req_id
and ra.status = 'UNAPP'
and sign(ra.amount_applied) = 1
and rps.payment_schedule_id = ra.payment_schedule_id
and cr.cash_receipt_id = ra.cash_receipt_id
group by rps.customer_id,
nvl(rps.customer_site_use_id, -99),
rps.invoice_currency_code,
cr.receipt_date,
ra.cash_receipt_id
order by rps.customer_id,
site_use_id,
rps.invoice_currency_code,
cr.receipt_date;
select c.customer_id customer_id,
c.customer_site_use_id site_use_id,
c.currency rcpt_currency,
cr1.amount last_payment_amount,
cr1.receipt_date last_payment_date,
cr1.receipt_number last_payment_number
from (
select a.customer_id,
a.customer_site_use_id,
a.currency,
max(b.cash_receipt_id) cash_receipt_id
from (
select cr.pay_from_customer customer_id,
nvl(cr.customer_site_use_id,-99) customer_site_use_id,
cr.currency_code currency,
cr.org_id,
max(cr.receipt_date) last_cash_receipt_date
from ar_cash_receipts cr,
ar_receivable_applications ra
where ra.request_id = p_req_id
and ra.status = 'UNAPP'
and sign(ra.amount_applied) = 1
and cr.cash_receipt_id = ra.cash_receipt_id
group by cr.pay_from_customer,
nvl(cr.customer_site_use_id,-99),
cr.currency_code,
cr.org_id) a,
ar_cash_receipts b
where a.last_cash_receipt_date = b.receipt_date
and a.customer_id = b.pay_from_customer
and a.customer_site_use_id = nvl(b.customer_site_use_id,-99)
and a.org_id = b.org_id
and a.currency = b.currency_code
group by a.customer_id,
a.customer_site_use_id,
a.currency) c,
ar_cash_receipts cr1
WHERE cr1.cash_receipt_id = c.cash_receipt_id;
select ps.customer_id trx_customer_id,
nvl(ps.customer_site_use_id, -99) trx_site_use_id,
ps.invoice_currency_code trx_currency,
ps.class trx_class,
ra.apply_date apply_date,
sum(decode(sign(ps.due_date - ra.apply_date),-1,
(ra.apply_date -
nvl(ps.due_date ,ra.apply_date))
* ra.amount_applied,null)) sum_app_amt_days_late,
sum(ra.earned_discount_taken) edisc_value,
sum(ra.unearned_discount_taken) uedisc_value,
sum(decode(sign(nvl(ra.earned_discount_taken,0)),
-1,-1,0,0,1)) edisc_count,
sum(decode(sign(nvl(ra.unearned_discount_taken,
0)),-1,-1,0,0,1)) uedisc_count,
sum(ra.amount_applied) amt_applied,
sum((ra.apply_date -
(ps.due_date + nvl(rt.printing_lead_days,0)))
*ra.amount_applied) inv_inst_pmt_days_sum,
sum(DECODE(ps.class,'INV',
DECODE((nvl(ps.discount_taken_earned,0)
+ nvl(ps.discount_taken_unearned,0)),0,0,1),0))
count_of_disc_inv_inst,
count(DECODE(ps.class,'INV',
ps.payment_schedule_id, null)) count_of_tot_inv_inst_paid,
count(decode(sign(ps.due_date-ra.apply_date),-1,
ps.payment_schedule_id, null)) count_of_inv_inst_paid_late
from ar_receivable_applications ra,
ar_payment_schedules ps,
ra_terms_b rt
where ra.request_id = p_req_id
and ra.status = 'APP'
and ps.payment_schedule_id = ra.applied_payment_schedule_id
and rt.term_id(+) = ps.term_id
group by ps.customer_id,
nvl(ps.customer_site_use_id, -99),
ps.invoice_currency_code,
ra.apply_date,
ps.class;
l_max_rows_per_update NUMBER := 1000;
debug ('AR_BUS_EVENT_SUB_PVT.Update_rcpt_app_info_for_req(+)');
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0) +
i.receipt_amount,
total_cash_receipts_count = nvl(total_cash_receipts_count,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = i.customer_id
and site_use_id = nvl(i.site_use_id,-99)
and NVL(org_id,'-99') = NVL(p_org_id,-99)
and currency = i.rcpt_currency
and as_of_date = i.as_of_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(i.customer_id,
nvl(i.site_use_id,-99),
p_org_id,
i.rcpt_currency,
i.as_of_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
i.receipt_amount,
1);
UPDATE ar_trx_bal_summary
set last_payment_amount = decode(sign(i.last_payment_date-last_payment_date),
-1,last_payment_amount,i.last_payment_amount),
last_payment_date =decode(sign(i.last_payment_date-last_payment_date),
-1,last_payment_date,i.last_payment_date),
last_payment_number = decode(sign(i.last_payment_date-last_payment_date),
-1,last_payment_number,i.last_payment_number),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = i.customer_id
and site_use_id = nvl(i.site_use_id,-99)
and NVL(org_id,'-99') = NVL(p_org_id,-99)
and currency = i.rcpt_currency;
INSERT into ar_trx_bal_summary
(cust_account_id,
site_use_id,
org_id,
currency,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(i.customer_id,
nvl(i.site_use_id,-99),
p_org_id,
i.rcpt_currency,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
i.last_payment_amount,
i.last_payment_date,
i.last_payment_number
);
UPDATE ar_trx_summary
SET inv_paid_amount = nvl(inv_paid_amount,0)
+ nvl(i.amt_applied,0),
inv_inst_pmt_days_sum = nvl(inv_inst_pmt_days_sum,0)
+ nvl(i.inv_inst_pmt_days_sum,0),
total_earned_disc_value = nvl(total_earned_disc_value,0)
+ nvl(i.edisc_value,0),
total_earned_disc_count = nvl(total_earned_disc_count,0)
+ nvl(i.edisc_count,0),
total_unearned_disc_value = nvl(total_unearned_disc_value,0)
+ nvl(i.uedisc_value,0),
total_unearned_disc_count = nvl(total_unearned_disc_count,0)
+ nvl(i.uedisc_count,0),
sum_app_amt_days_late = nvl(sum_app_amt_days_late,0)
+ nvl(i.sum_app_amt_days_late,0),
sum_app_amt = nvl(sum_app_amt,0) +
nvl(i.amt_applied,0),
count_of_tot_inv_inst_paid = nvl(count_of_tot_inv_inst_paid,0)
+ nvl(i.count_of_tot_inv_inst_paid,0),
count_of_inv_inst_paid_late = nvl(count_of_inv_inst_paid_late,0)
+ nvl(i.count_of_inv_inst_paid_late,0),
count_of_disc_inv_inst = nvl(count_of_disc_inv_inst,0) +
nvl(i.count_of_disc_inv_inst,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = i.trx_customer_id
and site_use_id = i.trx_site_use_id
and currency = i.trx_currency
and as_of_date = i.apply_date
and NVL(org_id,'-99') = NVL(p_org_id,-99);
IF l_row_counter >= l_max_rows_per_update
THEN
IF pg_debug = 'Y'
THEN
debug('total rows exceeds threshold.. executing update for block');
l_customer_id_tab.delete;
l_site_use_id_tab.delete;
l_currency_tab.delete;
l_org_id_tab.delete;
l_customer_id_tab.delete;
l_site_use_id_tab.delete;
l_currency_tab.delete;
l_org_id_tab.delete;
debug ('AR_BUS_EVENT_SUB_PVT.Update_rcpt_app_info_for_req(-)');
END Update_rcpt_app_info_for_req;
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE customer_trx_id = p_cust_trx_id
and nvl(complete_flag,'N') = 'N'
and amount_due_original is not null
for update;
SELECT * from ar_payment_schedules
WHERE customer_trx_id = cust_trx_id
FOR UPDATE;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE customer_trx_id = l_customer_trx_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
(l_ps_tab(j).amount_due_original
+nvl(l_ps_tab(j).amount_adjusted,0))),
OP_INVOICES_VALUE
= nvl(OP_INVOICES_VALUE,0)
+l_ps_tab(j).amount_due_original
+nvl(l_ps_tab(j).amount_adjusted,0),
OP_INVOICES_COUNT = nvl(OP_INVOICES_COUNT,0) + 1,
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0) +
decode(sign(l_ps_tab(j).due_date - trunc(sysdate)),-1,
(l_ps_tab(j).amount_due_original
+nvl(l_ps_tab(j).amount_adjusted,0)),0),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0) +
decode(sign(l_ps_tab(j).due_date - trunc(sysdate)),-1,1,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_tab(j).customer_id
and site_use_id = nvl(l_ps_tab(j).customer_site_use_id,-99)
and currency = l_ps_tab(j).invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_INVOICES_VALUE,
OP_INVOICES_COUNT,
PAST_DUE_INV_VALUE,
PAST_DUE_INV_INST_COUNT)
VALUES
( l_ps_tab(j).customer_id,
nvl(l_ps_tab(j).customer_site_use_id,-99),
l_org_id,
l_ps_tab(j).invoice_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
(l_ps_tab(j).amount_due_original
+nvl(l_ps_tab(j).amount_adjusted,0))),
l_ps_tab(j).amount_due_original+nvl(l_ps_tab(j).amount_adjusted,0),
1,
decode(sign(l_ps_tab(j).due_date - trunc(sysdate)),-1,
(l_ps_tab(j).amount_due_original
+nvl(l_ps_tab(j).amount_adjusted,0)),0),
decode(sign(l_ps_tab(j).due_date - trunc(sysdate)),-1,1,null));
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) +
l_ps_tab(j).amount_due_original+
nvl(l_ps_tab(j).amount_adjusted,0),
TOTAL_INVOICES_VALUE = nvl(TOTAL_INVOICES_VALUE,0) +
l_ps_tab(j).amount_due_original,
TOTAL_INVOICES_COUNT = nvl(TOTAL_INVOICES_COUNT,0) + 1,
LARGEST_INV_AMOUNT = DECODE(sign(l_inv_inst_count -j),0,
DECODE(sign(l_tot_inv_amt- nvl(LARGEST_INV_AMOUNT,0)),
1,l_tot_inv_amt,LARGEST_INV_AMOUNT),LARGEST_INV_AMOUNT),
LARGEST_INV_DATE = l_ps_tab(j).trx_date,
LARGEST_INV_CUST_TRX_ID = DECODE(sign(l_inv_inst_count -j),0,
DECODE(sign(l_tot_inv_amt- nvl(LARGEST_INV_AMOUNT,0)),
1,l_ps_tab(j).customer_trx_id,LARGEST_INV_CUST_TRX_ID),
LARGEST_INV_CUST_TRX_ID),
DAYS_CREDIT_GRANTED_SUM = nvl(DAYS_CREDIT_GRANTED_SUM,0) +
((l_ps_tab(j).amount_due_original
+ nvl(l_ps_tab(j).amount_adjusted,0))
* (l_ps_tab(j).due_date
- l_ps_tab(j).trx_date)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_tab(j).customer_id
and site_use_id = nvl(l_ps_tab(j).customer_site_use_id,-99)
and currency = l_ps_tab(j).invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_ps_tab(j).trx_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
OP_BAL_HIGH_WATERMARK,
TOTAL_INVOICES_VALUE,
TOTAL_INVOICES_COUNT,
LARGEST_INV_AMOUNT,
LARGEST_INV_DATE,
LARGEST_INV_CUST_TRX_ID,
DAYS_CREDIT_GRANTED_SUM)
VALUES
(l_ps_tab(j).customer_id,
nvl(l_ps_tab(j).customer_site_use_id,-99),
l_org_id,
l_ps_tab(j).invoice_currency_code,
l_ps_tab(j).trx_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_ps_tab(j).amount_due_original+
nvl(l_ps_tab(j).amount_adjusted,0),
l_ps_tab(j).amount_due_original,
1,
DECODE(sign(l_inv_inst_count -j),0,l_tot_inv_amt,null),
l_ps_tab(j).trx_date,
DECODE(sign(l_inv_inst_count -j),0,l_ps_tab(j).customer_trx_id,null),
((l_ps_tab(j).amount_due_original+ nvl(l_ps_tab(j).amount_adjusted,0))
* (l_ps_tab(j).due_date - l_ps_tab(j).trx_date))
);
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE history_id = p_hist_id;
SELECT LARGEST_INV_CUST_TRX_ID
FROM ar_trx_summary
WHERE cust_account_id = p_cust_account_id
and site_use_id = p_site_use_id
and currency = p_currency
and as_of_date = p_as_of_date
FOR UPDATE;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id
and history_id = l_history_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
-DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
(l_history_rec.amount_due_original
+nvl(l_history_rec.amount_adjusted,0))),
OP_INVOICES_VALUE
= nvl(OP_INVOICES_VALUE,0)
-l_history_rec.amount_due_original
-nvl(l_history_rec.amount_adjusted,0),
OP_INVOICES_COUNT = nvl(OP_INVOICES_COUNT,0) - 1,
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0) -
(decode(sign(l_history_rec.due_date - trunc(sysdate)),-1,
(l_history_rec.amount_due_original
+nvl(l_history_rec.amount_adjusted,0)),0)),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0) -
decode(sign(l_history_rec.due_date - trunc(sysdate)),-1,1,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
l_text := 'SELECT CUSTOMER_TRX_ID, inv_amount
FROM (
Select trx_date,customer_trx_id,
sum(amount_due_original) inv_amount,
RANK() OVER (ORDER BY sum(amount_due_original) desc,
customer_trx_id desc) rank_amt
FROM ar_payment_schedules
WHERE customer_id = :customer_id_bind
and customer_site_use_id = :customer_site_use_id_bind
and invoice_currency_code = :invoice_currency_code_bind
and trx_date = :trx_date_bind
group by trx_date,customer_trx_id)
where rank_amt = 1 ';
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) -
(l_history_rec.amount_due_original+
nvl(l_history_rec.amount_adjusted,0)),
TOTAL_INVOICES_VALUE = nvl(TOTAL_INVOICES_VALUE,0) -
l_history_rec.amount_due_original,
TOTAL_INVOICES_COUNT = nvl(TOTAL_INVOICES_COUNT,0) - 1,
LARGEST_INV_AMOUNT = DECODE(sign(nvl(l_history_rec.installments,0)),
1, DECODE(sign(nvl(LARGEST_INV_CUST_TRX_ID,0)-
l_history_rec.customer_trx_id),
0,l_larg_inv_amt,LARGEST_INV_AMOUNT),LARGEST_INV_AMOUNT),
LARGEST_INV_DATE = LARGEST_INV_DATE,
LARGEST_INV_CUST_TRX_ID = DECODE(sign(nvl(l_history_rec.installments,0)),
1, DECODE(sign(nvl(LARGEST_INV_CUST_TRX_ID,0)-
l_history_rec.customer_trx_id),
0,l_larg_inv_cust_trx_id,
LARGEST_INV_CUST_TRX_ID),LARGEST_INV_AMOUNT),
DAYS_CREDIT_GRANTED_SUM = nvl(DAYS_CREDIT_GRANTED_SUM,0) -
((l_history_rec.amount_due_original
+ l_history_rec.amount_adjusted)
* (l_history_rec.due_date
- l_history_rec.trx_date)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_history_rec.trx_date;
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE history_id = p_hist_id;
select *
from ar_trx_summary_hist
where previous_history_id = p_history_id;
select due_date, amount_in_dispute
from ar_payment_schedules
where payment_schedule_id = p_ps_id;
|cuddagir Added Exception Handling for update statement and moved the logic for |
|setting the flag inside the exception handling. |
|Have modified the earlier logic to make sure that the exceptions raised prior |
|to calling the update statement are not trapped in "IF SQL%NOTFOUND" condition |
|Changes Start |
+------------------------------------------------------------------------------*/
BEGIN
--Stamp the history record for the modification.
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id
and history_id = l_history_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
-DECODE(l_due_date_change,'+',
(l_history_rec.amount_due_original
+nvl(l_history_rec.amount_adjusted,0)),
'-',
-(l_history_rec.amount_due_original
+nvl(l_history_rec.amount_adjusted,0)),0),
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0)
- DECODE(l_due_date_change,'+',
-(l_history_rec.amount_due_remaining),
'-',
(l_history_rec.amount_due_remaining),0),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0) -
- DECODE(l_due_date_change,'+',
-1, '-',1,0),
INV_AMT_IN_DISPUTE = nvl(INV_AMT_IN_DISPUTE,0)
+(nvl(l_history_rec2.amount_in_dispute,0)
- nvl(l_history_rec.amount_in_dispute,0)),
DISPUTED_INV_COUNT = nvl(DISPUTED_INV_COUNT,0)
+ nvl(l_inv_dispute_count,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
UPDATE ar_trx_summary
set DAYS_CREDIT_GRANTED_SUM = nvl(DAYS_CREDIT_GRANTED_SUM,0) +
((l_history_rec.amount_due_original
+ l_history_rec.amount_adjusted)
* (l_history_rec2.due_date -l_history_rec.due_date)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_history_rec.trx_date;
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE customer_trx_id = p_cust_trx_id
and nvl(complete_flag,'N') = 'N'
and amount_due_original is not null
for update;
SELECT * from ar_payment_schedules
WHERE customer_trx_id = cust_trx_id
FOR UPDATE;
select ct.previous_customer_trx_id , ctt.type
from ra_customer_trx ct,
ra_customer_trx prev_ct,
ra_cust_trx_types ctt
where ct.customer_trx_id = ctx_id
and prev_ct.customer_trx_id = ct.previous_customer_trx_id
and prev_ct.cust_trx_type_id = ctt.cust_trx_type_id;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE customer_trx_id = l_customer_trx_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+ DECODE(sign(l_ps_rec.due_date - sysdate),-1,0,
(l_ps_rec.amount_due_original
+nvl(l_ps_rec.amount_adjusted,0))),
OP_CREDIT_MEMOS_VALUE
= nvl(OP_CREDIT_MEMOS_VALUE,0)
+ DECODE(l_prev_ctx_id, null,
l_ps_rec.amount_due_original,
DECODE(l_prev_trx_class,'CM',
l_ps_rec.amount_due_original,0)),
OP_CREDIT_MEMOS_COUNT = nvl(OP_CREDIT_MEMOS_COUNT,0)
+ DECODE(l_prev_ctx_id, null,1,
DECODE(l_prev_trx_class,'CM',
l_prev_trx_op_count,0)),
OP_INVOICES_VALUE = nvl(OP_INVOICES_VALUE,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'INV',
l_ps_rec.amount_due_original,0)),
OP_INVOICES_COUNT = nvl(OP_INVOICES_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'INV',
l_prev_trx_op_count,0)),
OP_DEBIT_MEMOS_VALUE = nvl(OP_DEBIT_MEMOS_VALUE,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DM',
l_ps_rec.amount_due_original,0)),
OP_DEBIT_MEMOS_COUNT = nvl(OP_DEBIT_MEMOS_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DM',
l_prev_trx_op_count,0)),
OP_DEPOSITS_VALUE = nvl(OP_DEPOSITS_VALUE,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DEP',
l_ps_rec.amount_due_original,0)),
OP_DEPOSITS_COUNT = nvl(OP_DEPOSITS_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DEP',
l_prev_trx_op_count,0)),
OP_CHARGEBACK_VALUE = nvl(OP_CHARGEBACK_VALUE,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'CB',
l_ps_rec.amount_due_original,0)),
OP_CHARGEBACK_COUNT = nvl(OP_CHARGEBACK_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'CB',
l_prev_trx_op_count,0)),
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0)
+ nvl(l_past_due_inv_amt,0),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0)
+ nvl(l_past_due_inv_count,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_rec.customer_id
and site_use_id = nvl(l_ps_rec.customer_site_use_id,-99)
and currency = l_ps_rec.invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_CREDIT_MEMOS_VALUE,
OP_CREDIT_MEMOS_COUNT,
OP_INVOICES_VALUE,
OP_INVOICES_COUNT,
OP_DEBIT_MEMOS_VALUE,
OP_DEBIT_MEMOS_COUNT,
OP_CHARGEBACK_VALUE,
OP_CHARGEBACK_COUNT,
PAST_DUE_INV_VALUE,
PAST_DUE_INV_INST_COUNT,
OP_DEPOSITS_VALUE,
OP_DEPOSITS_COUNT
)
VALUES
( l_ps_rec.customer_id,
nvl(l_ps_rec.customer_site_use_id,-99),
l_org_id,
l_ps_rec.invoice_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
DECODE(sign(l_ps_rec.due_date - sysdate),-1,0,
l_ps_rec.amount_due_original),
DECODE(l_prev_ctx_id, null, l_ps_rec.amount_due_original,
DECODE(l_prev_trx_class,'CM',l_ps_rec.amount_due_original,0)),
DECODE(l_prev_ctx_id, null,1,
DECODE(l_prev_trx_class,'CM', l_prev_trx_op_count,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'INV',l_ps_rec.amount_due_original,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'INV', l_prev_trx_op_count,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DM',l_ps_rec.amount_due_original,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DM', l_prev_trx_op_count,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'CB',l_ps_rec.amount_due_original,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'CB', l_prev_trx_op_count,0)),
l_past_due_inv_amt,
l_past_due_inv_count,
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DEP',l_ps_rec.amount_due_original,0)),
DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DEP', l_prev_trx_op_count,0))
);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) +
l_ps_rec.amount_due_original,
TOTAL_CREDIT_MEMOS_VALUE = nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
l_ps_rec.amount_due_original,
TOTAL_CREDIT_MEMOS_COUNT = nvl(TOTAL_CREDIT_MEMOS_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_rec.customer_id
and site_use_id = nvl(l_ps_rec.customer_site_use_id,-99)
and currency = l_ps_rec.invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_ps_rec.trx_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
OP_BAL_HIGH_WATERMARK,
TOTAL_CREDIT_MEMOS_VALUE,
TOTAL_CREDIT_MEMOS_COUNT
)
VALUES
(l_ps_rec.customer_id,
nvl(l_ps_rec.customer_site_use_id,-99),
l_org_id,
l_ps_rec.invoice_currency_code,
l_ps_rec.trx_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_ps_rec.amount_due_original+
nvl(l_ps_rec.amount_adjusted,0),
l_ps_rec.amount_due_original,
1
);
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE history_id = p_hist_id;
select previous_customer_trx_id
from ra_customer_trx
where customer_trx_id = ctx_id;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id
and history_id = l_history_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
- DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
(l_history_rec.amount_due_original
+nvl(l_history_rec.amount_adjusted,0))),
OP_CREDIT_MEMOS_VALUE
= nvl(OP_CREDIT_MEMOS_VALUE,0)
- DECODE(l_prev_ctx_id, null,
l_history_rec.amount_due_original,
DECODE(l_prev_trx_class,'CM',
l_history_rec.amount_due_original,0)),
OP_CREDIT_MEMOS_COUNT = nvl(OP_CREDIT_MEMOS_COUNT,0)
+ DECODE(l_prev_ctx_id, null,1,
DECODE(l_prev_trx_class,'CM',
l_prev_trx_op_count,0)),
OP_INVOICES_VALUE = nvl(OP_INVOICES_VALUE,0)
- DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'INV',
l_history_rec.amount_due_original,0)),
OP_INVOICES_COUNT = nvl(OP_INVOICES_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'INV',
l_prev_trx_op_count,0)),
OP_DEBIT_MEMOS_VALUE = nvl(OP_DEBIT_MEMOS_VALUE,0)
- DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DM',
l_history_rec.amount_due_original,0)),
OP_DEBIT_MEMOS_COUNT = nvl(OP_DEBIT_MEMOS_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DM',
l_prev_trx_op_count,0)),
OP_DEPOSITS_VALUE = nvl(OP_DEPOSITS_VALUE,0)
- DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DEP',
l_history_rec.amount_due_original,0)),
OP_DEPOSITS_COUNT = nvl(OP_DEPOSITS_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'DEP',
l_prev_trx_op_count,0)),
OP_CHARGEBACK_VALUE = nvl(OP_CHARGEBACK_VALUE,0)
- DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'CB',
l_history_rec.amount_due_original,0)),
OP_CHARGEBACK_COUNT = nvl(OP_CHARGEBACK_COUNT,0)
+ DECODE(l_prev_ctx_id, null,0,
DECODE(l_prev_trx_class,'CB',
l_prev_trx_op_count,0)),
PAST_DUE_INV_VALUE = nvl(PAST_DUE_INV_VALUE,0)
- nvl(l_past_due_inv_amt,0),
PAST_DUE_INV_INST_COUNT = nvl(PAST_DUE_INV_INST_COUNT,0)
- nvl(l_past_due_inv_count,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) -
l_history_rec.amount_due_original,
TOTAL_CREDIT_MEMOS_VALUE = nvl(TOTAL_CREDIT_MEMOS_VALUE,0) -
l_history_rec.amount_due_original,
TOTAL_CREDIT_MEMOS_COUNT = nvl(TOTAL_CREDIT_MEMOS_COUNT,0) - 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_history_rec.trx_date;
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE customer_trx_id = p_cust_trx_id
and nvl(complete_flag,'N') = 'N'
and amount_due_original is not null
for update;
SELECT * from ar_payment_schedules
WHERE customer_trx_id = cust_trx_id
FOR UPDATE;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE customer_trx_id = l_customer_trx_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
l_ps_tab(j).amount_due_original),
OP_DEBIT_MEMOS_VALUE
= nvl(OP_DEBIT_MEMOS_VALUE,0)
+l_ps_tab(j).amount_due_original,
OP_DEBIT_MEMOS_COUNT = nvl(OP_DEBIT_MEMOS_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_tab(j).customer_id
and site_use_id = nvl(l_ps_tab(j).customer_site_use_id,-99)
and currency = l_ps_tab(j).invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_DEBIT_MEMOS_VALUE,
OP_DEBIT_MEMOS_COUNT
)
VALUES
( l_ps_tab(j).customer_id,
nvl(l_ps_tab(j).customer_site_use_id,-99),
l_org_id,
l_ps_tab(j).invoice_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
l_ps_tab(j).amount_due_original),
l_ps_tab(j).amount_due_original,
1
);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) +
l_ps_tab(j).amount_due_original,
TOTAL_DEBIT_MEMOS_VALUE = nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
l_ps_tab(j).amount_due_original,
TOTAL_DEBIT_MEMOS_COUNT = nvl(TOTAL_DEBIT_MEMOS_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_tab(j).customer_id
and site_use_id = nvl(l_ps_tab(j).customer_site_use_id,-99)
and currency = l_ps_tab(j).invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_ps_tab(j).trx_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
OP_BAL_HIGH_WATERMARK,
TOTAL_DEBIT_MEMOS_VALUE,
TOTAL_DEBIT_MEMOS_COUNT
)
VALUES
(l_ps_tab(j).customer_id,
nvl(l_ps_tab(j).customer_site_use_id,-99),
l_org_id,
l_ps_tab(j).invoice_currency_code,
l_ps_tab(j).trx_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_ps_tab(j).amount_due_original,
l_ps_tab(j).amount_due_original,
1
);
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE history_id = p_hist_id;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id
and history_id = l_history_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
-DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
l_history_rec.amount_due_original),
OP_DEBIT_MEMOS_VALUE
= nvl(OP_DEBIT_MEMOS_VALUE,0)
-l_history_rec.amount_due_original,
OP_DEBIT_MEMOS_COUNT = nvl(OP_DEBIT_MEMOS_COUNT,0) - 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) -
l_history_rec.amount_due_original+
nvl(l_history_rec.amount_adjusted,0),
TOTAL_DEBIT_MEMOS_VALUE = nvl(TOTAL_DEBIT_MEMOS_VALUE,0) -
l_history_rec.amount_due_original,
TOTAL_DEBIT_MEMOS_COUNT = nvl(TOTAL_DEBIT_MEMOS_COUNT,0) - 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_history_rec.trx_date;
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE customer_trx_id = p_cust_trx_id
and nvl(complete_flag,'N') = 'N'
and amount_due_original is not null
for update;
SELECT * from ar_payment_schedules
WHERE customer_trx_id = cust_trx_id
FOR UPDATE;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE customer_trx_id = l_customer_trx_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
l_ps_tab(j).amount_due_original),
OP_DEPOSITS_VALUE
= nvl(OP_DEPOSITS_VALUE,0)
+l_ps_tab(j).amount_due_original
+nvl(l_ps_tab(j).amount_adjusted,0),
OP_DEPOSITS_COUNT = nvl(OP_DEPOSITS_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_tab(j).customer_id
and site_use_id = nvl(l_ps_tab(j).customer_site_use_id,-99)
and currency = l_ps_tab(j).invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_INVOICES_VALUE,
OP_INVOICES_COUNT
)
VALUES
( l_ps_tab(j).customer_id,
nvl(l_ps_tab(j).customer_site_use_id,-99),
l_org_id,
l_ps_tab(j).invoice_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
l_ps_tab(j).amount_due_original),
l_ps_tab(j).amount_due_original,
1
);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) +
l_ps_tab(j).amount_due_original+
nvl(l_ps_tab(j).amount_adjusted,0),
TOTAL_DEPOSITS_VALUE = nvl(TOTAL_DEPOSITS_VALUE,0) +
l_ps_tab(j).amount_due_original,
TOTAL_DEPOSITS_COUNT = nvl(TOTAL_DEPOSITS_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_tab(j).customer_id
and site_use_id = nvl(l_ps_tab(j).customer_site_use_id,-99)
and currency = l_ps_tab(j).invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_ps_tab(j).trx_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
OP_BAL_HIGH_WATERMARK,
TOTAL_DEPOSITS_VALUE,
TOTAL_DEPOSITS_COUNT
)
VALUES
(l_ps_tab(j).customer_id,
nvl(l_ps_tab(j).customer_site_use_id,-99),
l_org_id,
l_ps_tab(j).invoice_currency_code,
l_ps_tab(j).trx_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_ps_tab(j).amount_due_original+
nvl(l_ps_tab(j).amount_adjusted,0),
l_ps_tab(j).amount_due_original,
1
);
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE history_id = p_hist_id;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id
and history_id = l_history_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
-DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
l_history_rec.amount_due_original),
OP_DEPOSITS_VALUE
= nvl(OP_DEPOSITS_VALUE,0)
-l_history_rec.amount_due_original,
OP_DEPOSITS_COUNT = nvl(OP_DEPOSITS_COUNT,0) - 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) -
l_history_rec.amount_due_original+
nvl(l_history_rec.amount_adjusted,0),
TOTAL_DEPOSITS_VALUE = nvl(TOTAL_DEPOSITS_VALUE,0) -
l_history_rec.amount_due_original,
TOTAL_DEPOSITS_COUNT = nvl(TOTAL_DEPOSITS_COUNT,0) - 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_history_rec.customer_id
and site_use_id = nvl(l_history_rec.site_use_id,-99)
and currency = l_history_rec.currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_history_rec.trx_date;
SELECT *
FROM AR_TRX_SUMMARY_HIST
WHERE customer_trx_id = p_cust_trx_id
and nvl(complete_flag,'N') = 'N'
and amount_due_original is not null
for update;
SELECT *
FROM ar_payment_schedules
WHERE customer_trx_id = cust_trx_id
FOR UPDATE;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE customer_trx_id = l_customer_trx_id
and nvl(complete_flag,'N') = 'N';
UPDATE ar_trx_bal_summary
set BEST_CURRENT_RECEIVABLES
= nvl(BEST_CURRENT_RECEIVABLES,0)
+DECODE(sign(l_ps_rec.due_date - sysdate),-1,0,
l_ps_rec.amount_due_original),
OP_CHARGEBACK_VALUE
= nvl(OP_CHARGEBACK_VALUE,0)
+l_ps_rec.amount_due_original,
OP_CHARGEBACK_COUNT = nvl(OP_CHARGEBACK_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_rec.customer_id
and site_use_id = nvl(l_ps_rec.customer_site_use_id,-99)
and currency = l_ps_rec.invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99);
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
BEST_CURRENT_RECEIVABLES,
OP_CHARGEBACK_VALUE,
OP_CHARGEBACK_COUNT
)
VALUES
( l_ps_rec.customer_id,
nvl(l_ps_rec.customer_site_use_id,-99),
l_org_id,
l_ps_rec.invoice_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
DECODE(sign(l_ps_rec.due_date - sysdate),-1,0,
l_ps_rec.amount_due_original),
l_ps_rec.amount_due_original,
1
);
UPDATE ar_trx_summary
set OP_BAL_HIGH_WATERMARK = nvl(OP_BAL_HIGH_WATERMARK,0) +
l_ps_rec.amount_due_original,
TOTAL_CHARGEBACK_VALUE = nvl(TOTAL_CHARGEBACK_VALUE,0) +
l_ps_rec.amount_due_original,
TOTAL_CHARGEBACK_COUNT = nvl(TOTAL_CHARGEBACK_COUNT,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = l_ps_rec.customer_id
and site_use_id = nvl(l_ps_rec.customer_site_use_id,-99)
and currency = l_ps_rec.invoice_currency_code
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and as_of_date = l_ps_rec.trx_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
OP_BAL_HIGH_WATERMARK,
TOTAL_CHARGEBACK_VALUE,
TOTAL_CHARGEBACK_COUNT
)
VALUES
(l_ps_rec.customer_id,
nvl(l_ps_rec.customer_site_use_id,-99),
l_org_id,
l_ps_rec.invoice_currency_code,
l_ps_rec.trx_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_ps_rec.amount_due_original,
l_ps_rec.amount_due_original,
1
);
SELECT cash_receipt_id, trx_date, amount_due_original * -1,
trx_number, customer_id, customer_site_use_id,
invoice_currency_code
FROM ar_payment_schedules ps
WHERE payment_schedule_id = p_ps_id;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id;
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0) +
l_receipt_amount,
unresolved_cash_count = nvl(unresolved_cash_count,0) + 1,
last_payment_amount = DECODE(sign(l_receipt_date-last_payment_date),
-1,last_payment_amount,l_receipt_amount),
last_payment_date =DECODE(sign(l_receipt_date-last_payment_date),
-1,last_payment_date,l_receipt_date),
last_payment_number = DECODE(sign(l_receipt_date-last_payment_date),
-1,last_payment_number,l_receipt_number),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unresolved_cash_value,
unresolved_cash_count,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_receipt_amount,
1,
l_receipt_amount,
l_receipt_date,
l_receipt_number
);
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0) +
l_receipt_amount,
total_cash_receipts_count = nvl(total_cash_receipts_count,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_receipt_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
l_receipt_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_receipt_amount,
1);
SELECT ps.amount_due_original * -1,
ps.trx_number, ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code,
cr.reversal_category,
cr.reversal_date,
cr.receipt_date,
sum(DECODE(ra.status,
'UNAPP', nvl(ra.amount_applied_from,ra.amount_applied),
'ACC', nvl(ra.amount_applied_from,ra.amount_applied),
'OTHER ACC',nvl(ra.amount_applied_from,ra.amount_applied),
null)) unresolved_cash
FROM ar_payment_schedules ps,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_receivable_applications ra
WHERE ps.payment_schedule_id = p_ps_id
and ps.cash_receipt_id = cr.cash_receipt_id
and crh.cash_receipt_id = cr.cash_receipt_id
and crh.cash_receipt_history_id = ra.cash_receipt_history_id -- apandit
and crh.status = 'REVERSED'
and ra.cash_receipt_id = cr.cash_receipt_id
group by ps.amount_due_original,
ps.trx_number, ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code,
cr.reversal_category,
cr.reversal_date,
cr.receipt_date;
select 'Y'
from ar_trx_bal_summary
where cust_account_id = p_customer_id
and site_use_id = p_site_use_id
and currency = p_currency
and last_payment_number = p_pmt_number
and last_payment_date = p_pmt_date
and NVL(org_id,'-99') = NVL(p_org_id,-99);
select receipt_number, amount, receipt_date
from ar_cash_receipts
where cash_receipt_id =
(select max(cr.cash_receipt_id)
from ar_cash_receipts cr,
ar_cash_receipt_history crh --apandit
where cr.pay_from_customer = p_customer_id
and cr.cash_receipt_id = crh.cash_receipt_id --apandit
and crh.current_record_flag = 'Y'
and crh.status <> 'REVERSED'
and nvl(cr.customer_site_use_id,-99) = nvl(p_site_use_id, -99)
and cr.currency_code = p_currency);
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id;
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0) -
l_unresolved_cash,
unresolved_cash_count = nvl(unresolved_cash_count,0) - 1,
last_payment_amount = nvl(l_last_receipt_amount,last_payment_amount),
last_payment_date = nvl(l_last_receipt_date,last_payment_date),
last_payment_number = nvl(l_last_receipt_number,last_payment_number),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0) -
nvl(l_receipt_amount,0),
total_cash_receipts_count = nvl(total_cash_receipts_count,0) - 1,
nsf_stop_payment_amount = nvl(nsf_stop_payment_amount,0)
- nvl(l_receipt_amount,0),
nsf_stop_payment_count = nvl(nsf_stop_payment_count,0) + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_reversal_date;
SELECT cash_receipt_id, trx_date, amount_due_original * -1,
trx_number, customer_id, customer_site_use_id,
invoice_currency_code
FROM ar_payment_schedules ps
WHERE payment_schedule_id = p_ps_id;
select *
from ar_trx_summary_hist
where history_id = p_hist_id
and nvl(complete_flag ,'N') = 'N'
for update;
select ps.cash_receipt_id, ps.trx_date, hist.amount_due_original * -1,
ps.trx_number, hist.customer_id, hist.site_use_id,
ps.invoice_currency_code
from ar_trx_summary_hist hist,
ar_payment_schedules ps
where previous_history_id = p_hist_id
and ps.payment_schedule_id = hist.payment_schedule_id;
UPDATE ar_trx_summary_hist
set complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE history_id = l_history_id
and nvl(complete_flag ,'N') = 'N';
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0) +
(l_receipt_amount -
nvl(l_hist_rec.amount_due_original,0)),
unresolved_cash_count = nvl(unresolved_cash_count,0),
last_payment_amount = l_receipt_amount,
last_payment_date = l_receipt_date,
last_payment_number = l_receipt_number,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unresolved_cash_value,
unresolved_cash_count,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
(l_receipt_amount - nvl(l_hist_rec.amount_due_original,0)),
1,
(l_receipt_amount - nvl(l_hist_rec.amount_due_original,0)),
l_receipt_date,
l_receipt_number
);
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0) +
(l_receipt_amount - nvl(l_hist_rec.amount_due_original,0)),
total_cash_receipts_count = nvl(total_cash_receipts_count,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_receipt_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
l_receipt_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
(l_receipt_amount - nvl(l_hist_rec.amount_due_original,0)),
1);
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0)
- nvl(l_hist_rec.amount_due_original,0),
unresolved_cash_count = nvl(unresolved_cash_count,0) -1,
last_payment_amount = nvl(l_hist_rec.amount_due_original,0),
last_payment_date = l_receipt_date,
last_payment_number = l_receipt_number,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_hist_rec.customer_id
and site_use_id = nvl(l_hist_rec.site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unresolved_cash_value,
unresolved_cash_count,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(l_hist_rec.customer_id,
nvl(l_hist_rec.site_use_id,-99),
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_hist_rec.amount_due_original,
1,
l_hist_rec.amount_due_original,
l_receipt_date,
l_receipt_number
);
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0)
- nvl(l_hist_rec.amount_due_original,0) ,
total_cash_receipts_count = nvl(total_cash_receipts_count,0) -1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_hist_rec.customer_id
and site_use_id = nvl(l_hist_rec.site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_receipt_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(l_hist_rec.customer_id,
nvl(l_hist_rec.site_use_id,-99),
l_org_id,
l_currency_code,
l_receipt_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_hist_rec.amount_due_original,
1);
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0)
+l_receipt_amount ,
unresolved_cash_count = nvl(unresolved_cash_count,0) +1,
last_payment_amount = l_receipt_amount,
last_payment_date = l_receipt_date,
last_payment_number = l_receipt_number,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unresolved_cash_value,
unresolved_cash_count,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_receipt_amount,
1,
l_receipt_amount,
l_receipt_date,
l_receipt_number
);
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0)
+ l_receipt_amount ,
total_cash_receipts_count = nvl(total_cash_receipts_count,0)+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_receipt_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
l_receipt_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_receipt_amount,
1);
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0)
- nvl(l_hist_rec.amount_due_original,0),
unresolved_cash_count = nvl(unresolved_cash_count,0) -1,
last_payment_amount = nvl(l_hist_rec.amount_due_original,0),
last_payment_date = l_receipt_date,
last_payment_number = l_receipt_number,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_hist_rec.customer_id
and site_use_id = nvl(l_hist_rec.site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unresolved_cash_value,
unresolved_cash_count,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(l_hist_rec.customer_id,
nvl(l_hist_rec.site_use_id,-99),
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_hist_rec.amount_due_original,
1,
l_hist_rec.amount_due_original,
l_receipt_date,
l_receipt_number
);
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0)
- nvl(l_hist_rec.amount_due_original,0) ,
total_cash_receipts_count = nvl(total_cash_receipts_count,0),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_hist_rec.customer_id
and site_use_id = nvl(l_hist_rec.site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_receipt_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(l_hist_rec.customer_id,
nvl(l_hist_rec.site_use_id,-99),
l_org_id,
l_currency_code,
l_receipt_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_hist_rec.amount_due_original,
1);
UPDATE ar_trx_bal_summary
set unresolved_cash_value = nvl(unresolved_cash_value,0)
+l_receipt_amount ,
unresolved_cash_count = nvl(unresolved_cash_count,0)+1,
last_payment_amount = l_receipt_amount,
last_payment_date = l_receipt_date,
last_payment_number = l_receipt_number,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code;
INSERT INTO ar_trx_bal_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unresolved_cash_value,
unresolved_cash_count,
last_payment_amount,
last_payment_date,
last_payment_number
)VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_receipt_amount,
1,
l_receipt_amount,
l_receipt_date,
l_receipt_number
);
UPDATE ar_trx_summary
set total_cash_receipts_value = nvl(total_cash_receipts_value,0)
+ l_receipt_amount ,
total_cash_receipts_count = nvl(total_cash_receipts_count,0)+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = l_customer_id
and site_use_id = nvl(l_customer_site_use_id,-99)
and NVL(org_id,'-99') = NVL(l_org_id,-99)
and currency = l_currency_code
and as_of_date = l_receipt_date;
INSERT INTO ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
ORG_ID,
CURRENCY,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
total_cash_receipts_value,
total_cash_receipts_count
) VALUES
(l_customer_id,
nvl(l_customer_site_use_id,-99),
l_org_id,
l_currency_code,
l_receipt_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_receipt_amount ,
1);
FUNCTION CashReceipt_Delete
( p_subscription_guid In RAW
, p_event IN OUT NOCOPY WF_EVENT_T
)
RETURN VARCHAR2
IS
CURSOR hist(ps_id in number) IS
SELECT *
FROM ar_trx_summary_hist
WHERE payment_schedule_id = ps_id;
SELECT 'Y'
FROM ar_trx_bal_summary
WHERE cust_account_id = p_customer_id
AND site_use_id = p_site_use_id
AND currency = p_currency
AND last_payment_number = p_pmt_number
AND last_payment_date = p_pmt_date
AND NVL(org_id,'-99') = NVL(p_org_id,-99);
SELECT receipt_number, amount, receipt_date
FROM ar_cash_receipts
WHERE cash_receipt_id =
(SELECT MAX(cr.cash_receipt_id)
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.pay_from_customer = p_customer_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status <> 'REVERSED'
AND NVL(cr.customer_site_use_id,-99) = NVL(p_site_use_id, -99)
AND cr.currency_code = p_currency
);
debug ('AR_BUS_EVENT_SUB_PVT.CashReceipt_Delete(+)');
SAVEPOINT CR_Delete;
UPDATE ar_trx_summary_hist
SET complete_flag = 'Y',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE payment_schedule_id = l_payment_schedule_id;
UPDATE ar_trx_bal_summary
SET unresolved_cash_value = NVL(unresolved_cash_value,0) -
NVL(k.amount_due_original * -1, 0),
unresolved_cash_count = NVL(unresolved_cash_count,0) - 1,
last_payment_amount = NVL(l_last_receipt_amount,last_payment_amount),
last_payment_date = NVL(l_last_receipt_date,last_payment_date),
last_payment_number = NVL(l_last_receipt_number,last_payment_number),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = k.customer_id
AND site_use_id = nvl(k.site_use_id,-99)
AND NVL(org_id,'-99') = NVL(l_org_id,-99)
AND currency = k.currency_code;
UPDATE ar_trx_summary
SET total_cash_receipts_value = nvl(total_cash_receipts_value,0) -
nvl(k.amount_due_original * -1, 0),
total_cash_receipts_count = nvl(total_cash_receipts_count,0) - 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE cust_account_id = k.customer_id
AND site_use_id = nvl(k.site_use_id,-99)
AND NVL(org_id,'-99') = NVL(l_org_id,-99)
AND currency = k.currency_code
AND as_of_date = l_deletion_date;
debug ('AR_BUS_EVENT_SUB_PVT.CashReceipt_Delete(-)');
ROLLBACK TO CR_Delete;
WF_CORE.CONTEXT('AR_BUS_EVENT_SUB_PVT', 'CashReceipt_Delete', p_event.getEventName(), p_subscription_guid);
END CashReceipt_Delete;
SELECT trx_ps.customer_id,
trx_ps.customer_site_use_id,
trx_ps.invoice_currency_code,
trx_ps.class,
ra.amount_applied,
rcpt_ps.customer_id,
rcpt_ps.customer_site_use_id,
rcpt_ps.invoice_currency_code,
nvl(ra.amount_applied_from,ra.amount_applied),
ra.apply_date
FROM ar_payment_schedules trx_ps,
ar_receivable_applications ra,
ar_payment_schedules rcpt_ps
WHERE ra.receivable_application_id = p_ra_id
and ra.status in ('APP')
and ra.payment_schedule_id = rcpt_ps.payment_schedule_id
and ra.applied_payment_schedule_id = trx_ps.payment_schedule_id
;
select to_date(l_due_date_str, 'J')
into l_trx_due_date
from dual;
Columns that need to be updated in the summary tables due to a
credit memo application
AR_TRX_SUMMARY
==================
AR_TRX_BAL_SUMMARY
==================
1) OP_INVOICES_VALUE
2) OP_INVOICES_COUNT
3) OP_DEBIT_MEMOS_VALUE
4) OP_DEBIT_MEMOS_COUNT
5) OP_DEPOSITS_VALUE
6) OP_DEPOSITS_COUNT
7) OP_CHARGEBACK_VALUE
8) OP_CHARGEBACK_COUNT
9) OP_CREDIT_MEMOS_VALUE
10)OP_CREDIT_MEMOS_COUNT
11)PAST_DUE_INV_VALUE
12)PAST_DUE_INV_INST_COUNT
*****************************************************************/
Update_recapp_info(l_trx_class,
l_trx_customer_id,
l_trx_site_use_id,
l_trx_currency_code,
l_trx_amt,
l_op_trx_count,
l_cm_customer_id ,
l_cm_site_use_id ,
l_cm_currency_code,
l_cm_amt,
l_apply_date ,
null, --l_edisc_value,
null, --l_edisc_count,
null, --l_uedisc_value,
null, --l_uedisc_count,
null, --l_inv_paid_amt,
null, --l_inv_inst_pmt_days_sum,
null, --l_sum_app_amt_days_late,
null, --l_sum_app_amt,
null, --l_count_of_tot_inv_inst_paid,
null, --l_count_of_inv_inst_paid_late,
null, --l_count_of_disc_inv_inst,
null, --l_unresolved_cash_value,
null, --l_unresolved_cash_count,
l_op_cm_count,
'CM',
l_past_due_inv_value,
l_past_due_inv_inst_count,
l_org_id
);
SELECT trx_ps.customer_id,
trx_ps.customer_site_use_id,
trx_ps.invoice_currency_code,
trx_ps.class,
ra.amount_applied,
rcpt_ps.customer_id,
rcpt_ps.customer_site_use_id,
rcpt_ps.invoice_currency_code,
nvl(ra.amount_applied_from,ra.amount_applied),
ra.apply_date
FROM ar_payment_schedules trx_ps,
ar_receivable_applications ra,
ar_payment_schedules rcpt_ps
WHERE ra.receivable_application_id = p_ra_id
and ra.status in ('APP')
and ra.payment_schedule_id = rcpt_ps.payment_schedule_id
and ra.applied_payment_schedule_id = trx_ps.payment_schedule_id
;
select to_date(l_due_date_str, 'J')
into l_trx_due_date
from dual;
Columns that need to be updated in the summary tables due to a
credit memo application
AR_TRX_SUMMARY
==================
AR_TRX_BAL_SUMMARY
==================
1) OP_INVOICES_VALUE
2) OP_INVOICES_COUNT
3) OP_DEBIT_MEMOS_VALUE
4) OP_DEBIT_MEMOS_COUNT
5) OP_DEPOSITS_VALUE
6) OP_DEPOSITS_COUNT
7) OP_CHARGEBACK_VALUE
8) OP_CHARGEBACK_COUNT
9) OP_CREDIT_MEMOS_VALUE
10)OP_CREDIT_MEMOS_COUNT
11)PAST_DUE_INV_VALUE
12)PAST_DUE_INV_INST_COUNT
*****************************************************************/
Update_recapp_info(l_trx_class,
l_trx_customer_id,
l_trx_site_use_id,
l_trx_currency_code,
l_trx_amt,
l_op_trx_count,
l_cm_customer_id ,
l_cm_site_use_id ,
l_cm_currency_code,
l_cm_amt,
l_apply_date ,
null, --l_edisc_value,
null, --l_edisc_count,
null, --l_uedisc_value,
null, --l_uedisc_count,
null, --l_inv_paid_amt,
null, --l_inv_inst_pmt_days_sum,
null, --l_sum_app_amt_days_late,
null, --l_sum_app_amt,
null, --l_count_of_tot_inv_inst_paid,
null, --l_count_of_inv_inst_paid_late,
null, --l_count_of_disc_inv_inst,
null, --l_unresolved_cash_value,
null, --l_unresolved_cash_count,
l_op_cm_count,
'CM',
l_past_due_inv_value,
l_past_due_inv_inst_count,
l_org_id
);
SELECT trx_ps.customer_id,
trx_ps.customer_site_use_id,
trx_ps.invoice_currency_code,
trx_ps.class,
DECODE(trx_ps.class,'INV',
DECODE((nvl(trx_ps.discount_taken_earned,0)
+ nvl(trx_ps.discount_taken_unearned,0)),0,0,1),0) disc_inv_inst_count,
rt.printing_lead_days,
ra.amount_applied,
rcpt_ps.customer_id,
rcpt_ps.customer_site_use_id,
rcpt_ps.invoice_currency_code,
nvl(ra.amount_applied_from,ra.amount_applied),
ra.apply_date,
ra.earned_discount_taken,
ra.unearned_discount_taken,
decode(sign(nvl(ra.earned_discount_taken,0)),-1,-1,0,0,1) count_of_edisc,
decode(sign(nvl(ra.unearned_discount_taken,0)),-1,-1,0,0,1) count_of_uedisc
FROM ar_payment_schedules trx_ps,
ar_receivable_applications ra,
ar_payment_schedules rcpt_ps,
ra_terms_b rt
WHERE ra.receivable_application_id = ra_id
and ra.status in ('APP','ACTIVITY')
and ra.payment_schedule_id = rcpt_ps.payment_schedule_id
and ra.applied_payment_schedule_id = trx_ps.payment_schedule_id
and trx_ps.term_id = rt.term_id(+);
select to_date(l_due_date_str, 'J')
into l_trx_due_date
from dual;
Columns that need to be updated in the summary tables due to a
receipt application
AR_TRX_SUMMARY
==================
1) INV_PAID_AMOUNT
2) INV_INST_PMT_DAYS_SUM
3) TOTAL_EARNED_DISC_VALUE
4) TOTAL_EARNED_DISC_COUNT
5) TOTAL_UNEARNED_DISC_VALUE
6) TOTAL_UNEARNED_DISC_COUNT
7) SUM_APP_AMT_DAYS_LATE
8) SUM_APP_AMT
9) COUNT_OF_TOT_INV_INST_PAID
10)COUNT_OF_INV_INST_PAID_LATE
11)COUNT_OF_DISC_INV_INST
12)DAYS_CREDIT_GRANTED_SUM
AR_TRX_BAL_SUMMARY
==================
1) OP_INVOICES_VALUE
2) OP_INVOICES_COUNT
3) OP_DEBIT_MEMOS_VALUE
4) OP_DEBIT_MEMOS_COUNT
5) OP_DEPOSITS_VALUE
6) OP_DEPOSITS_COUNT
7) OP_CHARGEBACK_VALUE
8) OP_CHARGEBACK_COUNT
9) OP_CREDIT_MEMOS_VALUE
10)OP_CREDIT_MEMOS_COUNT
11)UNRESOLVED_CASH_VALUE l_rcp_cash_amt
12)UNRESOLVED_CASH_COUNT
13)PAST_DUE_INV_VALUE
14)PAST_DUE_INV_INST_COUNT
*****************************************************************/
Update_recapp_info(l_trx_class,
l_trx_customer_id,
l_trx_site_use_id,
l_trx_currency_code,
l_trx_amt ,
l_op_trx_count,
l_rcpt_customer_id ,
l_rcpt_site_use_id ,
l_rcpt_currency_code,
l_rcpt_amt ,
l_apply_date ,
l_edisc_value,
l_edisc_count,
l_uedisc_value,
l_uedisc_count,
l_inv_paid_amt,
l_inv_inst_pmt_days_sum,
l_sum_app_amt_days_late,
l_sum_app_amt,
l_count_of_tot_inv_inst_paid,
l_count_of_inv_inst_paid_late,
l_count_of_disc_inv_inst,
l_unresolved_cash_value,
l_unresolved_cash_count,
null,
'CASH',
l_past_due_inv_value,
l_past_due_inv_inst_count,
l_org_id
);
SELECT trx_ps.customer_id,
trx_ps.customer_site_use_id,
trx_ps.invoice_currency_code,
trx_ps.class,
DECODE(trx_ps.class,'INV',
DECODE((nvl(trx_ps.discount_taken_earned,0)
+ nvl(trx_ps.discount_taken_unearned,0)),0,0,1),0) disc_inv_inst_count,
rt.printing_lead_days,
ra.amount_applied,
rcpt_ps.customer_id,
rcpt_ps.customer_site_use_id,
rcpt_ps.invoice_currency_code,
nvl(ra.amount_applied_from,ra.amount_applied),
ra.apply_date,
ra.earned_discount_taken,
ra.unearned_discount_taken,
decode(sign(nvl(ra.earned_discount_taken,0)),-1,-1,0,0,1) count_of_edisc,
decode(sign(nvl(ra.unearned_discount_taken,0)),-1,-1,0,0,1) count_of_uedisc
FROM ar_payment_schedules trx_ps,
ar_receivable_applications ra,
ar_payment_schedules rcpt_ps,
ra_terms_b rt
WHERE ra.receivable_application_id = ra_id
and ra.status in ('APP','ACTIVITY')
and ra.payment_schedule_id = rcpt_ps.payment_schedule_id
and ra.applied_payment_schedule_id = trx_ps.payment_schedule_id
and trx_ps.term_id = rt.term_id(+);
select sum( nvl(ra.earned_discount_taken,0)
+ nvl(ra.unearned_discount_taken,0)
) total_disc
from ar_receivable_applications ra
where receivable_application_id = p_rec_app_id
and status = 'APP'
and display = 'Y';
select to_date(l_due_date_str, 'J')
into l_trx_due_date
from dual;
Columns that need to be updated in the summary tables due to a
receipt application
AR_TRX_SUMMARY
==================
1) INV_PAID_AMOUNT
2) INV_INST_PMT_DAYS_SUM
3) TOTAL_EARNED_DISC_VALUE
4) TOTAL_EARNED_DISC_COUNT
5) TOTAL_UNEARNED_DISC_VALUE
6) TOTAL_UNEARNED_DISC_COUNT
7) SUM_APP_AMT_DAYS_LATE
8) SUM_APP_AMT
9) COUNT_OF_TOT_INV_INST_PAID
10)COUNT_OF_INV_INST_PAID_LATE
11)COUNT_OF_DISC_INV_INST
12)DAYS_CREDIT_GRANTED_SUM
AR_TRX_BAL_SUMMARY
==================
1) OP_INVOICES_VALUE
2) OP_INVOICES_COUNT
3) OP_DEBIT_MEMOS_VALUE
4) OP_DEBIT_MEMOS_COUNT
5) OP_DEPOSITS_VALUE
6) OP_DEPOSITS_COUNT
7) OP_CHARGEBACK_VALUE
8) OP_CHARGEBACK_COUNT
9) OP_CREDIT_MEMOS_VALUE
10)OP_CREDIT_MEMOS_COUNT
11)UNRESOLVED_CASH_VALUE l_rcp_cash_amt
12)UNRESOLVED_CASH_COUNT
13)PAST_DUE_INV_VALUE
14)PAST_DUE_INV_INST_COUNT
*****************************************************************/
Update_recapp_info(l_trx_class,
l_trx_customer_id,
l_trx_site_use_id,
l_trx_currency_code,
l_trx_amt ,
l_op_trx_count,
l_rcpt_customer_id ,
l_rcpt_site_use_id ,
l_rcpt_currency_code,
l_rcpt_amt ,
l_apply_date ,
l_edisc_value,
l_edisc_count,
l_unedisc_value,
l_unedisc_count,
l_inv_paid_amt,
l_inv_inst_pmt_days_sum,
l_sum_app_amt_days_late,
l_sum_app_amt,
l_count_of_tot_inv_inst_paid,
l_count_of_inv_inst_paid_late,
l_count_of_disc_inv_inst,
l_unresolved_cash_value,
l_unresolved_cash_count,
null,
'CASH',
l_past_due_inv_value,
l_past_due_inv_inst_count,
l_org_id
);
Select ps.class,
ps.customer_id,
ps.customer_site_use_id,
ps.trx_date,
ps.invoice_currency_code,
ps.org_id,
ps.due_date,
ps.customer_trx_id ,
trx.previous_customer_trx_id,
ctt.type prev_trx_type,
ps.terms_sequence_number,
ps.amount_due_original,
trx_sum.largest_inv_amount largest_inv_amount,
trx_sum.largest_inv_date largest_inv_date,
trx_sum.largest_inv_cust_trx_id largest_inv_cust_trx_id,
count(nvl(rtl.term_id,1)) installment_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'INV',
decode(cm_app_ps.status,'CL',1,null))))
cm_closed_inv_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'DM',
decode(cm_app_ps.status,'CL',1,null))))
cm_closed_dm_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'CM',
decode(cm_app_ps.status,'CL',1,null))))
cm_closed_cm_count,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'INV',
decode(cm_app_ps.status,'CL',
decode(sign( cm_app_ps.due_date - trunc(sysdate)),-1,1,null)
)
)
)) cm_cl_past_due_inv_ct,
sum(decode(sign(ra_cm.amount_applied),0,null,
decode(ctt.type,'INV',
decode(cm_app_ps.status,'CL',
decode(sign( cm_app_ps.due_date - trunc(sysdate)),-1,
ra_cm.amount_applied,null)
)
)
)) cm_cl_past_due_inv_amt
from ra_customer_trx trx,
ar_payment_schedules ps,
ra_customer_trx prev_trx,
ra_cust_trx_types ctt,
ra_terms rt,
ra_terms_lines rtl,
ar_receivable_applications_all ra_cm,
ar_payment_schedules_all cm_app_ps,
ar_trx_summary trx_sum
where trx.customer_trx_id = ps.customer_trx_id
and trx.request_id = p_req_id
and trx.previous_customer_trx_id = prev_trx.customer_trx_id(+)
and prev_trx.cust_trx_type_id = ctt.cust_trx_type_id(+)
and rt.term_id(+) = ps.term_id
and rt.term_id = rtl.term_id(+)
and trx.customer_trx_id = ra_cm.customer_trx_id(+)
and ra_cm.applied_payment_schedule_id = cm_app_ps.payment_schedule_id(+)
and trx_sum.cust_account_id(+) = trx.bill_to_customer_id
and trx_sum.site_use_id(+) = trx.bill_to_site_use_id
and trx_sum.currency(+) = trx.invoice_currency_code
and trx_sum.as_of_date(+) = trx.trx_date
and trx_sum.org_id (+) = trx.org_id
group by ps.class,
ps.customer_id,
ps.customer_site_use_id,
ps.trx_date,
ps.invoice_currency_code,
ps.org_id,
ps.due_date,
ps.customer_trx_id ,
trx.previous_customer_trx_id,
ctt.type,
ps.terms_sequence_number,
ps.amount_due_original,
trx_sum.largest_inv_amount,
trx_sum.largest_inv_date ,
trx_sum.largest_inv_cust_trx_id
order by ps.customer_trx_id,ps.terms_sequence_number;
Update_summary_for_request_id(l_request_id);
Update_rcpt_app_info_for_req(l_request_id, l_org_id);
SELECT sum(amount),count(adj.adjustment_id) adj_count,
ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code, adj.apply_date,
ps.class, ps.due_date, adj.status
FROM ar_adjustments adj,
ar_payment_schedules ps
WHERE adj.request_id = p_req_id
and adj.payment_schedule_id = ps.payment_schedule_id
group by ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
adj.apply_date,
ps.class,
ps.due_date,
adj.status;
Update_Adj_info (
l_customer_id,
l_site_use_id,
l_org_id ,
l_currency_code,
l_adj_amount ,
l_adj_count ,
l_apply_date ,
l_pending_adj_amount,
l_class,
null,
l_past_due_inv_inst_count,
l_past_due_inv_value );
Update_rcpt_app_info_for_req(l_request_id, l_org_id);
SELECT adj.amount, adj.apply_date, adj.receivables_trx_id,
ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
ps.class, ps.due_date
FROM ar_adjustments adj,
ar_payment_schedules ps
WHERE adj.payment_schedule_id = ps.payment_schedule_id
and adj.adjustment_id = p_adj_id ;
Update_Adj_info (
l_customer_id,
l_site_use_id,
l_org_id ,
l_currency_code,
l_adj_amount ,
l_op_trx_count ,
l_apply_date ,
l_pending_adj_amount,
l_class ,
l_special_adj,
l_past_due_inv_inst_count,
l_past_due_inv_value);
SELECT adj.amount, adj.apply_date, adj.receivables_trx_id,
ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
ps.class, ps.due_date
FROM ar_adjustments adj,
ar_payment_schedules ps
WHERE adj.payment_schedule_id = ps.payment_schedule_id
and adj.adjustment_id = p_adj_id ;
Update_Adj_info (
l_customer_id,
l_site_use_id,
l_org_id ,
l_currency_code,
l_adj_amount ,
l_op_trx_count ,
l_apply_date ,
-l_pending_adj_amount,
l_class,
null,
l_past_due_inv_inst_count,
l_past_due_inv_value);
Update_summary_for_request_id(l_request_id);
SELECT rowid,
customer_trx_id,
customer_trx_line_id,
amount_due_original,
acctd_amount_due_original
FROM ar_ramc_audit_trail
WHERE request_id = p_request_id
AND original_collectibility_flag = 'N';
l_rowid_table.delete;
mass updates (receipt applications):
op_invoices_count
op_invoices_value
past_due_inv_inst_count
past_due_inv_value
In the original bug, lockbox receipts were corrupting these columns in
ar_trx_bal_summary for partial receipts, multiple applications to one
trx from different receipts, and receipts with apply_dates that
occurr before the trx.due_date. */
PROCEDURE refresh_counts(
p_customer_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
p_site_use_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
p_currency_tab IN ar_bus_event_sub_pvt.currency_type,
p_org_id_tab IN ar_bus_event_sub_pvt.generic_id_type ) IS
l_rows NUMBER;
update ar_trx_bal_summary main_sum
set (op_invoices_count,
op_invoices_value,
past_due_inv_inst_count,
past_due_inv_value,
op_credit_memos_count,
op_credit_memos_value,
op_debit_memos_count,
op_debit_memos_value,
op_deposits_count,
op_deposits_value,
op_chargeback_count,
op_chargeback_value ) =
(select
/* OP invoices */
count(decode(trx_ps.class,'INV',trx_ps.payment_schedule_id, null)),
sum(decode(trx_ps.class,'INV',trx_ps.amount_due_remaining,0)),
/* past due invoices */
count(decode(trx_ps.class,'INV',
decode(sign(trx_ps.due_date - trunc(sysdate)),
-1, trx_ps.payment_schedule_id,null),null)),
sum(decode(trx_ps.class,'INV',
decode(sign(trx_ps.due_date - trunc(sysdate)),
-1, decode(trx_ps.class,'INV',trx_ps.amount_due_remaining,0),0),0)),
/* OP credit memos */
count(decode(trx_ps.class,'CM',trx_ps.payment_schedule_id, null)),
sum(decode(trx_ps.class,'CM',trx_ps.amount_due_remaining,0)),
/* OP debit memos */
count(decode(trx_ps.class,'DM',trx_ps.payment_schedule_id, null)),
sum(decode(trx_ps.class,'DM',trx_ps.amount_due_remaining,0)),
/* OP deposits */
count(decode(trx_ps.class,'DEP',trx_ps.payment_schedule_id, null)),
sum(decode(trx_ps.class,'DEP',trx_ps.amount_due_remaining,0)),
/* OP chargebacks */
count(decode(trx_ps.class,'CB',trx_ps.payment_schedule_id, null)),
sum(decode(trx_ps.class,'CB',trx_ps.amount_due_remaining,0))
from ar_payment_schedules_all trx_ps
where trx_ps.status = 'OP'
and trx_ps.customer_id = main_sum.cust_account_id
and trx_ps.customer_site_use_id = decode(main_sum.site_use_id,-99,
trx_ps.customer_site_use_id,
main_sum.site_use_id)
and trx_ps.org_id = main_sum.org_id
and trx_ps.invoice_currency_code = main_sum.currency
group by trx_ps.customer_id, trx_ps.customer_site_use_id,
trx_ps.invoice_currency_code, trx_ps.org_id),
/* WHO columns */
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login= fnd_global.login_id
where cust_account_id = p_customer_id_tab(i)
and site_use_id = p_site_use_id_tab(i)
and currency = p_currency_tab(i)
and NVL(org_id,'-99') = NVL(p_org_id_tab(i),-99);
debug (' updated row(s) = ' || l_rows);
after mass updates (receipt applications)
*/
PROCEDURE refresh_at_risk_value(
p_customer_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
p_site_use_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
p_currency_tab IN ar_bus_event_sub_pvt.currency_type,
p_org_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE default null ) IS
l_rows NUMBER;
UPDATE ar_trx_bal_summary main_sum
SET receipts_at_risk_value =
(SELECT SUM(DECODE(rap.applied_payment_schedule_id, -2, 0,
crh.amount))
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_receivable_applications_all rap
WHERE nvl(cr.confirmed_flag, 'Y') = 'Y'
AND cr.reversal_date IS NULL
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status NOT IN ('REVERSED',
DECODE(crh.factor_flag, 'Y', 'RISK_ELIMINATED',
'N', 'CLEARED'))
AND cr.cash_receipt_id = rap.cash_receipt_id (+)
AND rap.applied_payment_schedule_id (+) = -2
AND cr.pay_from_customer = main_sum.cust_account_id
AND nvl(cr.customer_site_use_id,-99) = decode(main_sum.site_use_id,-99,
-99,
main_sum.site_use_id)
AND cr.org_id = main_sum.org_id
AND cr.currency_code = main_sum.currency
GROUP BY cr.pay_from_customer, nvl(cr.customer_site_use_id,-99),
cr.currency_code, cr.org_id),
/* WHO columns */
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login= fnd_global.login_id
WHERE cust_account_id = p_customer_id_tab(i)
AND site_use_id = p_site_use_id_tab(i)
AND currency = p_currency_tab(i)
AND NVL(org_id,'-99') = NVL(p_org_id_tab(i),-99);
UPDATE ar_trx_bal_summary main_sum
SET receipts_at_risk_value = receipts_at_risk_value -
(SELECT SUM(DECODE(rap.applied_payment_schedule_id, -2, 0,
crh.amount))
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_receivable_applications_all rap
WHERE nvl(cr.confirmed_flag, 'Y') = 'Y'
AND cr.reversal_date IS NULL
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status IN ('REVERSED',
DECODE(crh.factor_flag, 'Y', 'RISK_ELIMINATED',
'N', 'CLEARED'))
AND cr.cash_receipt_id = rap.cash_receipt_id (+)
AND rap.applied_payment_schedule_id (+) = -2
AND cr.pay_from_customer = main_sum.cust_account_id
AND cr.customer_site_use_id = decode(main_sum.site_use_id,-99,
cr.customer_site_use_id,
main_sum.site_use_id)
AND cr.org_id = main_sum.org_id
AND cr.currency_code = main_sum.currency
AND cr.cash_receipt_id = p_cash_receipt_id
GROUP BY cr.pay_from_customer, cr.customer_site_use_id,
cr.currency_code, cr.org_id),
/* WHO columns */
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login= fnd_global.login_id
WHERE cust_account_id = p_customer_id_tab(0)
AND site_use_id = p_site_use_id_tab(0)
AND currency = p_currency_tab(0)
AND NVL(org_id,'-99') = NVL(p_org_id_tab(0),-99);
debug (' updated row(s) = ' || l_rows);