[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_conc_req IS
BEGIN
DELETE from AR_CONC_PROCESS_REQUESTS
where CONCURRENT_PROGRAM_NAME = 'ARSUMREF';
INSERT INTO AR_CONC_PROCESS_REQUESTS
(CONCURRENT_PROGRAM_NAME, REQUEST_ID)
values ('ARSUMREF',FND_GLOBAL.conc_request_id);
SELECT *
FROM ar_sum_ref_event_hist;
insert_conc_req;
DELETE from ar_trx_bal_summary;
SELECT sysdate INTO l_program_start_date FROM dual;
INSERT INTO AR_TRX_BAL_SUMMARY
(CUST_ACCOUNT_ID,
SITE_USE_ID,
CURRENCY,
ORG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OP_INVOICES_VALUE,
OP_INVOICES_COUNT,
OP_CREDIT_MEMOS_VALUE,
OP_CREDIT_MEMOS_COUNT,
OP_DEPOSITS_VALUE,
OP_DEPOSITS_COUNT,
OP_CHARGEBACK_VALUE,
OP_CHARGEBACK_COUNT,
OP_DEBIT_MEMOS_VALUE,
OP_DEBIT_MEMOS_COUNT,
OP_BILLS_RECEIVABLES_VALUE,
OP_BILLS_RECEIVABLES_COUNT,
UNRESOLVED_CASH_VALUE,
UNRESOLVED_CASH_COUNT,
PAST_DUE_INV_VALUE,
PAST_DUE_INV_INST_COUNT,
INV_AMT_IN_DISPUTE,
DISPUTED_INV_COUNT,
BEST_CURRENT_RECEIVABLES,
RECEIPTS_AT_RISK_VALUE,
LAST_PAYMENT_AMOUNT,
LAST_PAYMENT_DATE,
LAST_PAYMENT_NUMBER,
PENDING_ADJ_VALUE
)
(SELECT D.CUSTOMER_ID,
D.CUSTOMER_SITE_USE_ID,
D.CURRENCY_CODE,
D.ORG_ID,
SYSDATE,
-2003,
SYSDATE,
-2003,
-2003,
nvl(SUM(D.OP_INV_SUM),0) OP_INV_SUM,
nvl(SUM(D.OP_INV_COUNT),0) OP_INV_COUNT,
nvl(SUM(D.OP_CM_SUM),0) OP_CM_SUM,
nvl(SUM(D.OP_CM_COUNT),0) OP_CM_COUNT,
nvl(SUM(D.OP_DEP_SUM),0) OP_DEP_SUM,
nvl(SUM(D.OP_DEP_COUNT),0) OP_DEP_COUNT,
nvl(SUM(D.OP_CB_SUM),0) OP_CB_SUM,
nvl(SUM(D.OP_CB_COUNT),0) OP_CB_COUNT,
nvl(SUM(D.OP_DM_SUM),0) OP_DM_SUM,
nvl(SUM(D.OP_DM_COUNT),0) OP_DM_COUNT,
nvl(SUM(D.OP_BR_SUM),0) OP_BR_SUM,
nvl(SUM(D.OP_BR_COUNT),0) OP_BR_COUNT,
nvl(SUM(D.UNRESOLVED_CASH_VALUE),0) UNRESOLVED_CASH_VALUE,
nvl(SUM(D.UNRESOLVED_CASH_COUNT),0) UNRESOLVED_CASH_COUNT,
nvl(SUM(D.PAST_DUE_INV_VALUE),0) PAST_DUE_INV_VALUE,
nvl(SUM(D.PAST_DUE_INV_COUNT),0) PAST_DUE_INV_COUNT,
nvl(SUM(D.INV_AMT_IN_DISPUTE),0) INV_AMT_IN_DISPUTE,
nvl(SUM(D.INV_DISPUTE_COUNT),0) INV_DISPUTE_COUNT,
nvl(SUM(D.BEST_CURRENT_RECEIVABLES),0) BEST_CURRENT_RECEIVABLES,
nvl(SUM(D.RECEIPT_AT_RISK_AMT),0) RECEIPT_AT_RISK_AMT,
nvl(SUM(D.LAST_RECEIPT_AMOUNT),0) LAST_RECEIPT_AMOUNT,
MAX(D.LAST_RECEIPT_DATE) LAST_RECEIPT_DATE,
nvl(MAX(D.LAST_RECEIPT_NUMBER),0) LAST_RECEIPT_NUMBER,
nvl(SUM(D.PENDING_ADJ_AMT),0) PENDING_ADJ_AMT
FROM (
SELECT C.CUSTOMER_ID,
nvl(C.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
C.INVOICE_CURRENCY_CODE CURRENCY_CODE,
C.ORG_ID,
SUM(DECODE(CLASS,'INV', C.AMOUNT_DUE_REMAINING,0)) OP_INV_SUM,
COUNT(DECODE(CLASS,'INV', DECODE(C.STATUS,'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_INV_COUNT,
SUM(DECODE(CLASS,'CM', C.AMOUNT_DUE_REMAINING,0) ) OP_CM_SUM,
COUNT(DECODE(CLASS,'CM', DECODE(C.STATUS,'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CM_COUNT,
SUM(DECODE(CLASS,'CB', C.AMOUNT_DUE_REMAINING,0)) OP_CB_SUM,
COUNT(DECODE(CLASS,'CB',DECODE(C.STATUS, 'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CB_COUNT,
SUM(DECODE(C.CLASS,'DEP', C.AMOUNT_DUE_REMAINING) ) OP_DEP_SUM,
COUNT(DECODE(C.CLASS,'DEP', DECODE(C.STATUS ,'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DEP_COUNT,
SUM(DECODE(C.CLASS,'DM', C.AMOUNT_DUE_REMAINING ,0)) OP_DM_SUM,
COUNT(DECODE(C.CLASS,'DM', DECODE(C.STATUS, 'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DM_COUNT,
SUM(DECODE(C.CLASS,'BR', C.AMOUNT_DUE_REMAINING, NULL)) OP_BR_SUM,
COUNT(DECODE(C.CLASS,'BR', DECODE(C.STATUS, 'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_BR_COUNT,
SUM(DECODE(C.CLASS,'PMT', C.AMOUNT_DUE_REMAINING, NULL)) UNRESOLVED_CASH_VALUE,
COUNT(DECODE(C.CLASS,'PMT', DECODE(C.STATUS, 'OP',
C.PAYMENT_SCHEDULE_ID,NULL),NULL)) UNRESOLVED_CASH_COUNT,
SUM(DECODE(CLASS,'INV',DECODE(C.STATUS, 'OP',
DECODE(SIGN(TRUNC(SYSDATE) -
TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
(C.AMOUNT_DUE_ORIGINAL - NVL(C.AMOUNT_APPLIED,0)
+NVL(C.AMOUNT_ADJUSTED,0)),
0),0),0)) PAST_DUE_INV_VALUE,
COUNT(DECODE(C.CLASS,'INV',DECODE(C.STATUS, 'OP',
DECODE(SIGN(TRUNC(SYSDATE) -
TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
C.PAYMENT_SCHEDULE_ID,
NULL),NULL),NULL)) PAST_DUE_INV_COUNT,
SUM(DECODE(CLASS,'INV',C.AMOUNT_IN_DISPUTE,0)) INV_AMT_IN_DISPUTE,
COUNT(DECODE(C.CLASS,'INV',DECODE(C.AMOUNT_IN_DISPUTE,
NULL,NULL,0,NULL,C.PAYMENT_SCHEDULE_ID),
NULL)) INV_DISPUTE_COUNT,
SUM(DECODE(C.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1,
0)
* DECODE(SIGN(C.DUE_DATE-SYSDATE),
-1,0,C.AMOUNT_DUE_REMAINING ))
BEST_CURRENT_RECEIVABLES,
0 RECEIPT_AT_RISK_AMT ,
0 LAST_RECEIPT_AMOUNT,
TO_DATE(NULL) LAST_RECEIPT_DATE,
NULL LAST_RECEIPT_NUMBER,
SUM(C.AMOUNT_ADJUSTED_PENDING) PENDING_ADJ_AMT
FROM AR_PAYMENT_SCHEDULES_ALL C
WHERE c.customer_id >0
GROUP BY C.CUSTOMER_ID,
C.CUSTOMER_SITE_USE_ID,
C.INVOICE_CURRENCY_CODE ,
C.ORG_ID
UNION ALL
SELECT A1.CUSTOMER_ID,
A1.CUSTOMER_SITE_USE_ID,
A1.CURRENCY,
A1.ORG_ID ,
0 OP_INV_SUM,
0 OP_INV_COUNT,
0 OP_CM_SUM,
0 OP_CM_COUNT,
0 OP_CB_SUM,
0 OP_CB_COUNT,
0 OP_DEP_SUM,
0 OP_DEP_COUNT,
0 OP_DM_SUM,
0 OP_DM_COUNT,
0 OP_BR_SUM,
0 OP_BR_COUNT,
0 UNRESOLVED_CASH_VALUE,
0 UNRESOLVED_CASH_COUNT,
0 PAST_DUE_INV_VALUE,
0 PAST_DUE_INV_COUNT,
0 INV_AMT_IN_DISPUTE,
0 INV_DISPUTE_COUNT,
0 BEST_CURRENT_RECEIVABLES_ADO,
0 RECEIPT_AT_RISK_AMT,
B.AMOUNT * -1 LAST_RECEIPT_AMOUNT,
B.RECEIPT_DATE LAST_RECEIPT_DATE,
B.RECEIPT_NUMBER LAST_RECEIPT_NUMBER,
0 PENDING_ADJ_AMT
FROM (
select a.customer_id,
a.customer_site_use_id,
a.currency,
a.org_id,
max(b.cash_receipt_id) last_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_ALL CR
WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
AND CR.REVERSAL_DATE IS NULL
AND CR.PAY_FROM_CUSTOMER > 0
AND CR.TYPE = 'CASH'
GROUP BY CR.PAY_FROM_CUSTOMER,
NVL(CR.CUSTOMER_SITE_USE_ID,-99),
CR.CURRENCY_CODE,
CR.ORG_ID) a,
ar_cash_receipts_all 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.currency = b.currency_code
and a.org_id = b.org_id
group by a.customer_id,
a.customer_site_use_id,
a.currency,
a.org_id) a1,
AR_CASH_RECEIPTS_ALL B
WHERE a1.LAST_CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
UNION ALL
SELECT CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
NVL(CR.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
CR.CURRENCY_CODE CURRENCY_CODE,
CR.ORG_ID ORG_ID,
0 OP_INV_SUM,
0 OP_INV_COUNT,
0 OP_CM_SUM,
0 OP_CM_COUNT,
0 OP_CB_SUM,
0 OP_CB_COUNT,
0 OP_DEP_SUM,
0 OP_DEP_COUNT,
0 OP_DM_SUM,
0 OP_DM_COUNT,
0 OP_BR_SUM,
0 OP_BR_COUNT,
0 UNRESOLVED_CASH_VALUE,
0 UNRESOLVED_CASH_COUNT,
0 PAST_DUE_INV_VALUE,
0 PAST_DUE_INV_COUNT,
0 INV_AMT_IN_DISPUTE,
0 INV_DISPUTE_COUNT,
0 BEST_CURRENT_RECEIVABLES_ADO,
SUM(DECODE(RAP.APPLIED_PAYMENT_SCHEDULE_ID, -2, NULL, CRH.AMOUNT))
RECEIPT_AT_RISK_AMT,
0 LAST_RECEIPT_AMOUNT,
TO_DATE(NULL) LAST_RECEIPT_DATE,
NULL LAST_RECEIPT_NUMBER,
0 PENDING_ADJ_AMT
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 CR.PAY_FROM_CUSTOMER > 0
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS NOT IN (DECODE (CRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED',
'N', 'CLEARED'), 'REVERSED')
AND RAP.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND RAP.APPLIED_PAYMENT_SCHEDULE_ID(+) = -2
GROUP BY CR.PAY_FROM_CUSTOMER,NVL(CR.CUSTOMER_SITE_USE_ID,-99),
CR.ORG_ID,CR.CURRENCY_CODE
) D
GROUP BY D.CUSTOMER_ID,D.CUSTOMER_SITE_USE_ID,D.CURRENCY_CODE,D.ORG_ID);
DELETE from ar_trx_summary;
INSERT into ar_trx_summary
(CUST_ACCOUNT_ID,
SITE_USE_ID,
CURRENCY,
ORG_ID,
AS_OF_DATE,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
TOTAL_INVOICES_VALUE,
TOTAL_INVOICES_COUNT,
TOTAL_CREDIT_MEMOS_VALUE ,
TOTAL_CREDIT_MEMOS_COUNT,
TOTAL_CHARGEBACK_VALUE,
TOTAL_CHARGEBACK_COUNT,
TOTAL_DEPOSITS_VALUE,
TOTAL_DEPOSITS_COUNT,
TOTAL_DEBIT_MEMOS_VALUE,
TOTAL_DEBIT_MEMOS_COUNT,
TOTAL_BILLS_RECEIVABLES_VALUE,
TOTAL_BILLS_RECEIVABLES_COUNT,
TOTAL_CASH_RECEIPTS_VALUE,
TOTAL_CASH_RECEIPTS_COUNT,
COUNT_OF_DISC_INV_INST,
DAYS_CREDIT_GRANTED_SUM,
COUNT_OF_INV_INST_PAID_LATE,
COUNT_OF_TOT_INV_INST_PAID,
INV_PAID_AMOUNT,
INV_INST_PMT_DAYS_SUM,
NSF_STOP_PAYMENT_COUNT,
NSF_STOP_PAYMENT_AMOUNT,
SUM_APP_AMT,
TOTAL_EARNED_DISC_VALUE,
TOTAL_EARNED_DISC_COUNT,
TOTAL_UNEARNED_DISC_VALUE,
TOTAL_UNEARNED_DISC_COUNT,
SUM_APP_AMT_DAYS_LATE,
TOTAL_ADJUSTMENTS_VALUE,
TOTAL_ADJUSTMENTS_COUNT)
( select D.customer_id,
D.customer_site_use_id,
D.currency_code,
D.org_id,
D.as_of_date,
sysdate,
-2003,
sysdate,
-2003,
-2003,
sum(decode(D.TOT_INV_SUM,0,null,D.TOT_INV_SUM)) TOT_INV_SUM,
sum(decode(D.TOT_INV_COUNT,0,null,D.TOT_INV_COUNT)) TOT_INV_COUNT,
SUM(decode(D.TOT_CM_SUM,0,null,D.TOT_CM_SUM)) TOT_CM_SUM,
SUM(decode(D.TOT_CM_COUNT,0,null,D.TOT_CM_COUNT)) TOT_CM_COUNT,
sum(decode(D.TOT_CB_SUM,0,null,D.TOT_CB_SUM)) TOT_CB_SUM,
SUM(decode(D.TOT_CB_COUNT,0,null,D.TOT_CB_COUNT)) TOT_CB_COUNT,
SUM(decode(D.TOT_DEP_SUM,0,null,D.TOT_DEP_SUM)) TOT_DEP_SUM,
SUM(decode(D.TOT_DEP_COUNT,0,null,D.TOT_DEP_COUNT)) TOT_DEP_COUNT,
SUM(decode(D.TOT_DM_SUM,0,null,D.TOT_DM_SUM)) TOT_DM_SUM,
SUM(decode(D.TOT_DM_COUNT,0,null,D.TOT_DM_COUNT)) TOT_DM_COUNT,
SUM(decode(D.TOT_BR_SUM,0,null,D.TOT_BR_SUM)) TOT_BR_SUM,
SUM(decode(D.TOT_BR_COUNT,0,null,D.TOT_BR_COUNT)) TOT_BR_COUNT,
SUM(decode(D.TOT_PMT_SUM,0,null,D.TOT_PMT_SUM)) TOT_PMT_SUM,
SUM(decode(D.TOT_PMT_COUNT,0,null,D.TOT_PMT_COUNT)) TOT_PMT_COUNT,
SUM(decode(D.disc_inv_inst_count,0,null,D.disc_inv_inst_count)) disc_inv_inst_count,
SUM(decode(D.days_credit_granted_sum,0,null,D.days_credit_granted_sum)) days_credit_granted_sum,
SUM(decode(D.COUNT_OF_INV_INST_PAID_LATE,0,null,D.COUNT_OF_INV_INST_PAID_LATE)) COUNT_OF_INV_INST_PAID_LATE,
SUM(decode(D.COUNT_OF_TOT_INV_INST_PAID,0,null,D.COUNT_OF_TOT_INV_INST_PAID)) COUNT_OF_TOT_INV_INST_PAID,
SUM(decode(D.INV_PAID_AMOUNT,0,null,D.INV_PAID_AMOUNT)) INV_PAID_AMOUNT,
SUM(decode(D.inv_inst_pmt_days_sum,0,null,D.inv_inst_pmt_days_sum)) inv_inst_pmt_days_sum,
sum(decode(D.NSF_STOP_PAYMENT_COUNT,0,null,D.NSF_STOP_PAYMENT_COUNT)) NSF_STOP_PAYMENT_COUNT,
sum(decode(D.NSF_STOP_PAYMENT_AMOUNT,0,null,D.NSF_STOP_PAYMENT_AMOUNT)) NSF_STOP_PAYMENT_AMOUNT,
sum(decode(D.sum_amt_applied,0,null,D.sum_amt_applied)) sum_amt_applied,
sum(decode(D.edisc_taken,0,null,D.edisc_taken)) edisc_taken,
sum(decode(D.edisc_taken,0,null,D.edisc_count)) edisc_count,
sum(decode(D.unedisc_taken,0,null,D.unedisc_taken)) unedisc_taken,
sum(decode(D.unedisc_taken,0,null,D.unedisc_count)) unedisc_count,
sum(decode(D.app_amt_days_late,0,null,D.app_amt_days_late)) app_amt_days_late,
sum(decode(D.adj_amount,0,null,D.adj_amount)) adj_amount,
sum(decode(D.adj_count,0,null,D.adj_count)) adj_count
from ( select C.customer_id,
C.customer_site_use_id,
C.currency_code,
C.org_id,
C.trx_date as_of_date,
sum(DECODE(C.class,'INV',C.amount_due_original,0 )) TOT_INV_SUM,
count(decode(C.class,'INV',C.payment_schedule_id,null)) TOT_INV_COUNT,
sum(DECODE(C.class,'CM',C.amount_due_original,0 )) TOT_CM_SUM,
count(decode(C.class,'CM',C.payment_schedule_id,null)) TOT_CM_COUNT,
sum(DECODE(C.class,'CB',C.amount_due_original,0 )) TOT_CB_SUM,
count(decode(C.class,'CB',C.payment_schedule_id,null)) TOT_CB_COUNT,
sum(DECODE(C.class,'DEP',C.amount_due_original,0 )) TOT_DEP_SUM,
count(decode(C.class,'DEP',C.payment_schedule_id,null)) TOT_DEP_COUNT,
sum(DECODE(C.class,'DM',C.amount_due_original,0 )) TOT_DM_SUM,
count(decode(C.class,'DM',C.payment_schedule_id,null)) TOT_DM_COUNT,
sum(DECODE(C.class,'BR',C.amount_due_original,0)) TOT_BR_SUM,
count(decode(C.class,'BR',C.payment_schedule_id,null)) TOT_BR_COUNT,
sum(DECODE(C.class,'PMT',C.amount_due_original,0 )) TOT_PMT_SUM,
count(decode(C.class,'PMT',C.payment_schedule_id,null)) TOT_PMT_COUNT,
sum(DECODE(C.class, 'INV', DECODE((nvl(C.edisc_taken,0) +
nvl(C.unedisc_taken,0)), 0, 0, 1),0)) DISC_INV_INST_COUNT,
sum(decode(C.class,'INV',((C.due_date - C.trx_date)*(nvl(C.amount_due_original,0)+
nvl(C.ADJ_AMOUNT,0))),null)) DAYS_CREDIT_GRANTED_SUM,
sum(decode(C.class,'INV',
DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
- NVL(C.AMOUNT_APPLIED,0)
- nvl(C.edisc_taken,0)
- nvl(C.unedisc_taken,0)
+ NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
null,
decode(sign(C.due_date - C.actual_date_closed),
-1, 1,null))),null)) COUNT_OF_INV_INST_PAID_LATE,
sum(decode(C.class,'INV',
DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
- NVL(C.AMOUNT_APPLIED,0)
- nvl(C.edisc_taken,0)
- nvl(C.unedisc_taken,0)
+ NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL)
,null,
1)),null)) COUNT_OF_TOT_INV_INST_PAID,
sum(decode(C.class,'INV',DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
- NVL(C.AMOUNT_APPLIED,0)
- nvl(C.edisc_taken,0)
- nvl(C.unedisc_taken,0)
+ NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
null,nvl(C.amount_applied,0)),null)) INV_PAID_AMOUNT,
sum(decode(C.class,'INV',1,null)) COUNT_OF_TOT_INV_INST,
0 inv_inst_pmt_days_sum,
0 NSF_STOP_PAYMENT_COUNT,
0 NSF_STOP_PAYMENT_AMOUNT,
0 sum_amt_applied,
0 edisc_taken,
0 edisc_count,
0 unedisc_taken,
0 unedisc_count,
0 app_amt_days_late,
0 ADJ_AMOUNT,
0 ADJ_COUNT
FROM (
SELECT A.CUSTOMER_ID,
A.CUSTOMER_SITE_USE_ID,
A.CURRENCY_CODE,
A.ORG_ID ,
A.CLASS,
A.DUE_DATE,
A.TRX_DATE,
A.actual_date_closed,
A.PAYMENT_SCHEDULE_ID,
A.AMOUNT_DUE_ORIGINAL,
A.AMOUNT_IN_DISPUTE,
A.AMOUNT_APPLIED,
A.edisc_taken,
A.unedisc_taken,
SUM(ADJ.AMOUNT) ADJ_AMOUNT
FROM (
SELECT PS.CUSTOMER_ID,
NVL(PS.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
PS.INVOICE_CURRENCY_CODE CURRENCY_CODE,
PS.ORG_ID,
PS.CLASS,
nvl(trx_hist.amount_in_dispute, ps.amount_in_dispute) AMOUNT_IN_DISPUTE,
nvl(trx_hist.due_date, ps.due_date) DUE_DATE,
PS.AMOUNT_DUE_ORIGINAL,
PS.TRX_DATE,
PS.actual_date_closed,
PS.PAYMENT_SCHEDULE_ID,
SUM( RA.AMOUNT_APPLIED) AMOUNT_APPLIED,
sum(decode(ps.class, 'INV',
decode(ra.earned_discount_taken,0,
null,ra.earned_discount_taken), null)) edisc_taken,
sum(decode(ps.class, 'INV',
decode(ra.unearned_discount_taken,0,
null,ra.unearned_discount_taken), null)) unedisc_taken
FROM AR_PAYMENT_SCHEDULES_all ps,
AR_RECEIVABLE_APPLICATIONS_ALL RA,
( select history_id, payment_schedule_id, due_date, amount_in_dispute
from ar_trx_summary_hist
where history_id in
(select max(history_id)
from ar_trx_summary_hist
where nvl(complete_flag,'N') = 'N'
and creation_date <= l_program_start_date
group by payment_schedule_id)) TRX_HIST
WHERE RA.APPLIED_PAYMENT_SCHEDULE_ID(+) = PS.PAYMENT_SCHEDULE_ID
AND PS.payment_schedule_id = TRX_HIST.payment_schedule_id(+)
AND RA.CREATION_DATE(+) <= l_program_start_date
AND RA.DISPLAY(+) = 'Y'
AND RA.STATUS(+) = 'APP'
AND PS.CUSTOMER_ID > 0
and ra.apply_date(+) >= add_months(sysdate, -24)
AND ps.trx_date >= add_months(sysdate, -24)
AND PS.CREATION_DATE <= l_program_start_date
GROUP BY PS.CUSTOMER_ID, NVL(PS.CUSTOMER_SITE_USE_ID,-99),
PS.INVOICE_CURRENCY_CODE, PS.ORG_ID,
PS.CLASS, PS.TRX_DATE,nvl(trx_hist.due_date, ps.due_date),
PS.AMOUNT_DUE_ORIGINAL,
nvl(trx_hist.amount_in_dispute, ps.amount_in_dispute),
ps.actual_date_closed, PS.PAYMENT_SCHEDULE_ID
) A,
AR_ADJUSTMENTS_ALL ADJ
WHERE A.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID(+)
AND ADJ.CREATION_DATE (+) <= l_program_start_date
AND ADJ.STATUS(+) = 'A'
GROUP BY A.CUSTOMER_ID, A.CUSTOMER_SITE_USE_ID,
A.CURRENCY_CODE, A.ORG_ID,
A.CLASS, A.TRX_DATE,A.DUE_DATE,
A.AMOUNT_DUE_ORIGINAL, A.AMOUNT_IN_DISPUTE,
A.actual_date_closed,A.AMOUNT_APPLIED,
A.edisc_taken,A.unedisc_taken,
A.PAYMENT_SCHEDULE_ID
) C
group by C.customer_id,
C.customer_site_use_id,
C.currency_code,
C.org_id,
C.trx_date
UNION
select cr.pay_from_customer customer_id,
nvl(cr.customer_site_use_id,-99) customer_site_use_id,
cr.currency_code invoice_currency_code,
cr.org_id,
cr.reversal_date as_of_date,
0 TOT_INV_SUM,
0 TOT_INV_COUNT,
0 TOT_CM_SUM,
0 TOT_CM_COUNT,
0 TOT_CB_SUM,
0 TOT_CB_COUNT,
0 TOT_DEP_SUM,
0 TOT_DEP_COUNT,
0 TOT_DM_SUM,
0 TOT_DM_COUNT,
0 TOT_BR_SUM,
0 TOT_BR_COUNT,
0 TOT_PMT_SUM,
0 TOT_PMT_COUNT,
0 disc_inv_inst_count,
0 days_credit_granted_sum,
0 COUNT_OF_INV_INST_PAID_LATE,
0 COUNT_OF_TOT_INV_INST_PAID,
0 INV_PAID_AMOUNT,
0 COUNT_OF_TOT_INV_INST,
0 inv_inst_pmt_days,
count(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT,
sum(cr.amount) NSF_STOP_PAYMENT_AMOUNT,
0 sum_amt_applied,
0 edisc_taken,
0 edisc_count,
0 unedisc_taken,
0 unedisc_count,
0 app_amt_days_late,
0 adj_amount,
0 adj_count
from ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh
where cr.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag = 'Y'
and crh.status = 'REVERSED'
and crh.creation_date <= l_program_start_date
and cr.status = 'REV'
and cr.reversal_category = 'NSF'
and cr.reversal_date > add_months(sysdate, -24)
/*bug#5378555---------------------------------------------------------------------+
|kjoshi included condition of cr.pay_from_customer > 0 to exclude 'MISC' receipts |
+---------------------------------------------------------------------------------*/
and nvl(cr.pay_from_customer,0) > 0
group by cr.pay_from_customer,
nvl(cr.customer_site_use_id,-99),
cr.currency_code,
cr.org_id,
cr.reversal_date
UNION
select customer_id,
customer_site_use_id,
invoice_currency_code,
org_id,
apply_date as_of_date,
0 TOT_INV_SUM,
0 TOT_INV_COUNT,
0 TOT_CM_SUM,
0 TOT_CM_COUNT,
0 TOT_CB_SUM,
0 TOT_CB_COUNT,
0 TOT_DEP_SUM,
0 TOT_DEP_COUNT,
0 TOT_DM_SUM,
0 TOT_DM_COUNT,
0 TOT_BR_SUM,
0 TOT_BR_COUNT,
0 TOT_PMT_SUM,
0 TOT_PMT_COUNT,
0 disc_inv_inst_count,
0 days_credit_granted_sum,
0 COUNT_OF_INV_INST_PAID_LATE,
0 COUNT_OF_TOT_INV_INST_PAID,
0 INV_PAID_AMOUNT,
0 COUNT_OF_TOT_INV_INST,
sum(decode(inv_inst_pmt_days,0,null,inv_inst_pmt_days)) inv_inst_pmt_days,
0 NSF_STOP_PAYMENT_COUNT,
0 NSF_STOP_PAYMENT_AMOUNT,
sum(decode(sum_amt_applied,0,null,sum_amt_applied)) sum_amt_applied,
sum(decode(edisc_taken,0,null,edisc_taken)) edisc_taken,
sum(decode(edisc_taken,0,null,edisc_count)) edisc_count,
sum(decode(unedisc_taken,0,null,unedisc_taken)) unedisc_taken,
sum(decode(unedisc_taken,0,null,unedisc_count)) unedisc_count,
sum(decode(app_amt_days_late,0,null,app_amt_days_late)) app_amt_days_late,
0 adj_amount,
0 adj_count
from ( select ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
trunc(ra.apply_date) apply_date,
ra.cash_receipt_id,
ra.applied_payment_schedule_id,
sum(decode(ps.class, 'INV',ra.amount_applied,0)) sum_amt_applied,
sum(decode(ps.class, 'INV',((ra.apply_date - (ps.trx_date + nvl(rt.printing_lead_days,0)))
* (nvl(ra.amount_applied,0))),null)) inv_inst_pmt_days,
sum(decode(ps.class, 'INV', decode(ra.earned_discount_taken,0,null,ra.earned_discount_taken), null)) edisc_taken,
sum(decode(ps.class, 'INV',decode(nvl(ra.earned_discount_taken,0),0,null,1),null)) edisc_count,
sum(decode(ps.class, 'INV', decode(ra.unearned_discount_taken,0,null,ra.unearned_discount_taken), null)) unedisc_taken,
sum(decode(ps.class, 'INV',decode(nvl(ra.unearned_discount_taken,0),0,null,1),null)) unedisc_count,
sum(decode(ps.class, 'INV',
(ra.apply_date - nvl(trx_hist.due_date, ps.due_date))* ra.amount_applied, null)) app_amt_days_late
from ar_payment_schedules_all ps,
( select history_id, payment_schedule_id, due_date, amount_in_dispute
from ar_trx_summary_hist
where history_id in
(select max(history_id)
from ar_trx_summary_hist
where nvl(complete_flag,'N') = 'N'
and creation_date <= l_program_start_date
group by payment_schedule_id)) TRX_HIST,
ra_terms_b rt,
ar_receivable_applications_all ra
where ps.payment_schedule_id = ra.applied_payment_schedule_id
and trx_hist.payment_schedule_id(+) = ps.payment_schedule_id
and ps.customer_id > 0
and ps.term_id = rt.term_id(+)
and ra.creation_date <= l_program_start_date
and ra.status = 'APP'
and ra.display = 'Y'
and ra.application_type = 'CASH'
and ra.apply_date >= add_months(sysdate, -24)
group by ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
trunc(ra.apply_date),
ra.cash_receipt_id,
ra.applied_payment_schedule_id
)
group by customer_id,
customer_site_use_id,
invoice_currency_code,
org_id,
apply_date
UNION
select ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
adj.apply_date as_of_date,
0 TOT_INV_SUM,
0 TOT_INV_COUNT,
0 TOT_CM_SUM,
0 TOT_CM_COUNT,
0 TOT_CB_SUM,
0 TOT_CB_COUNT,
0 TOT_DEP_SUM,
0 TOT_DEP_COUNT,
0 TOT_DM_SUM,
0 TOT_DM_COUNT,
0 TOT_BR_SUM,
0 TOT_BR_COUNT,
0 TOT_PMT_SUM,
0 TOT_PMT_COUNT,
0 disc_inv_inst_count,
0 days_credit_granted_sum,
0 COUNT_OF_INV_INST_PAID_LATE,
0 COUNT_OF_TOT_INV_INST_PAID,
0 INV_PAID_AMOUNT,
0 COUNT_OF_TOT_INV_INST,
0 inv_inst_pmt_days,
0 NSF_STOP_PAYMENT_COUNT,
0 NSF_STOP_PAYMENT_AMOUNT,
0 sum_amt_applied,
0 edisc_taken,
0 edisc_count,
0 unedisc_taken,
0 unedisc_count,
0 app_amt_days_late,
sum(adj.amount) adj_amount,
count(adjustment_id) adj_count
from ar_payment_schedules_all ps,
ar_adjustments_all adj
where ps.payment_schedule_id = adj.payment_schedule_id
and adj.receivables_trx_id(+) > 0
and ps.trx_date > add_months(sysdate, -24)
and ps.creation_date <= l_program_start_date
and adj.creation_date <= l_program_start_date
and adj.status = 'A'
and adj.apply_date > add_months(sysdate, -24)
group by ps.customer_id,
ps.customer_site_use_id,
ps.invoice_currency_code,
ps.org_id,
adj.apply_date
) D
group by D.customer_id,
D.customer_site_use_id,
D.currency_code,
D.org_id,
D.as_of_date);
| LOGIC TO UPDATE THE LARGEST INV INFO IN |
| AR_TRX_SUMMARY TABLE |
| |
+--------------------------------------------*/
declare
v_cursor1 NUMBER;
text_select VARCHAR2(4000);
text_update VARCHAR2(4000);
text_select :=
'SELECT customer_id, customer_site_use_id,
invoice_currency_code, trunc(trx_date), amount,customer_trx_id
FROM (
select customer_id, customer_site_use_id,
invoice_currency_code,
trx_date, amount,customer_trx_id,
RANK() OVER (PARTITION BY customer_id,
customer_site_use_id,
invoice_currency_code,
trx_date
ORDER BY amount desc, trx_date desc,
customer_trx_id desc) rank_amount
from ( select customer_id,customer_site_use_id,
invoice_currency_code,customer_trx_id,
trx_date,SUM(amount_due_original) amount
from ar_payment_schedules_all
where class = '||''''||'INV'||''''||
' and customer_id > 0
and trx_date >= add_months(sysdate, -24)
group by customer_id,customer_site_use_id,
invoice_currency_code, trx_date, customer_trx_id
)
)
WHERE rank_amount = 1';
text_update := 'Update ar_trx_summary set LARGEST_INV_AMOUNT = :amount,
LARGEST_INV_CUST_TRX_ID = :cust_trx_id,
LARGEST_INV_DATE = :trx_date,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = :customer_id
and SITE_USE_ID = :site_use_id
and CURRENCY = :currency_code
and AS_OF_DATE = :trx_date';
dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
v_customer_id.delete;
v_site_use_id.delete;
v_currency_code.delete;
v_trx_date.delete;
v_cust_trx_id.delete;
v_amount.delete;
| LOGIC TO UPDATE THE HIGHWATER MARK BALANCE |
| IN AR_TRX_SUMMARY |
| |
+--------------------------------------------*/
declare
v_cursor1 NUMBER;
text_select VARCHAR2(4000);
text_update VARCHAR2(4000);
text_select :=
'select customer_id, customer_site_use_id, invoice_currency_code,
as_of_date , cum_balance
from (
select customer_id, customer_site_use_id, invoice_currency_code,
as_of_date , sum(net_amount) OVER (PARTITION BY customer_id,
customer_site_use_id, invoice_currency_code
ORDER BY customer_id, customer_site_use_id,
invoice_currency_code ROWS UNBOUNDED PRECEDING) cum_balance
from (
select customer_id, customer_site_use_id, invoice_currency_code,
as_of_date , sum(net_amount) net_amount
from
(select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
ps.trx_date as_of_date, sum(ps.amount_due_original) net_amount
from ar_payment_schedules_all ps
where ps.class in ('||''''||'INV'||''''||','
||''''||'CM'||''''||','
||''''||'DM'||''''||','
||''''||'DEP'||''''||','
||''''||'BR'||''''||','
||''''||'CB'||''''||')
and ps.customer_id > 0
group by ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code, ps.trx_date
union
select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
ra.apply_date as_of_date,
sum(-ra.amount_applied
-nvl(ra.earned_discount_taken,0)
-nvl(ra.unearned_discount_taken,0)) net_amount
from ar_payment_schedules_all ps,
ar_receivable_applications_all ra
where ps.payment_schedule_id = ra.applied_payment_schedule_id
and ps.customer_id > 0
and ra.status = '||''''||'APP'||''''||'
and nvl(ra.confirmed_flag,'||''''||'Y'||''''||') = '||''''||'Y'||''''||'
and ps.class in ('||''''||'INV'||''''||','
||''''||'CM'||''''||','
||''''||'DM'||''''||','
||''''||'DEP'||''''||','
||''''||'BR'||''''||','
||''''||'CB'||''''||')
group by ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code, ra.apply_date
union all
select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
adj.apply_date as_of_date, sum(adj.amount)
from ar_payment_schedules_all ps,
ar_adjustments_all adj
where ps.payment_schedule_id = adj.payment_schedule_id
and ps.class in ('||''''||'INV'||''''||','
||''''||'CM'||''''||','
||''''||'DM'||''''||','
||''''||'DEP'||''''||','
||''''||'BR'||''''||','
||''''||'CB'||''''||')
and adj.status = '||''''||'A'||''''||'
and ps.customer_id > 0
group by ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code, adj.apply_date
union all
select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
ra.apply_date as_of_date,
sum(nvl(ra.amount_applied_from, ra.amount_applied)
+ nvl(ra.earned_discount_taken,0)
+ nvl(ra.unearned_discount_taken,0)) net_amount
from ar_payment_schedules_all ps,
ar_receivable_applications_all ra
where ps.payment_schedule_id = ra.payment_schedule_id
and ps.class in ('||''''||'CM'||''''||')
and ra.status = '||''''||'APP'||''''||'
and nvl(ra.confirmed_flag,'||''''||'Y'||''''||') = '||''''||'Y'||''''||'
group by ps.customer_id, ps.customer_site_use_id,
ps.invoice_currency_code, ra.apply_date
)
group by customer_id, customer_site_use_id, invoice_currency_code,
as_of_date
order by customer_id, customer_site_use_id, invoice_currency_code,
as_of_date )
)
where as_of_date > add_months(sysdate , -24)';
text_update :=
'Update ar_trx_summary
set OP_BAL_HIGH_WATERMARK = :cum_balance,
OP_BAL_HIGH_WATERMARK_DATE = :as_of_date,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where cust_account_id = :customer_id
and SITE_USE_ID = :site_use_id
and CURRENCY = :currency_code
and AS_OF_DATE = :as_of_date';
dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
v_customer_id.delete;
v_site_use_id.delete;
v_currency_code.delete;
v_trx_date.delete;
v_cum_balance.delete;
DELETE from AR_CONC_PROCESS_REQUESTS
where request_id = FND_GLOBAL.conc_request_id;
l_list.DELETE;
DELETE FROM AR_SUM_REF_EVENT_HIST;
DELETE from AR_CONC_PROCESS_REQUESTS
WHERE REQUEST_ID = FND_GLOBAL.conc_request_id;
/* over commit to insure that deleted rows are recorded */
COMMIT;