应收计算SQL

应收开票金额：
SELECT SUM(RCL.EXTENDED_AMOUNT * NVL(RCT.EXCHANGE_RATE, 1))
FROM RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCL,
RA_CUST_TRX_LINE_GL_DIST_ALL GD,
RA_CUST_TRX_TYPES_ALL CTT
WHERE 1 = 1
AND RCT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND 'REC' = GD.ACCOUNT_CLASS
AND 'Y' = GD.LATEST_REC_FLAG

AND RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID
AND RCT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CTT.TYPE IN ('INV', 'CM') --标准发票，贷项通知单
AND CTT.ORG_ID = RCT.ORG_ID
AND gd.gl_date < = TO_DATE('&deadline_date','YYYY-MM-DD')
AND RCT.TRX_NUMBER = '&IN_TRX_NUMBER'；

收款原始金额：
分为两部分：
1。不存在冲销记录的收款金额：
SELECT CR.AMOUNT * NVL(CR.EXCHANGE_RATE, 1) AMOUNT
FROM AR_CASH_RECEIPTS_ALL CR,
AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED
WHERE 1 = 1
AND CRH_FIRST_POSTED.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_FIRST_POSTED.ORG_ID = CR.ORG_ID
AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG = 'Y'
AND CR.CASH_RECEIPT_ID = &IN_CASH_RECEIPT_ID
AND TRUNC(CRH_FIRST_POSTED.GL_DATE) <=
TRUNC(TO_DATE(&IV_DEADLINE, 'yyyy-mm-dd hh24:mi:ss'))
AND NOT EXISTS
(SELECT 1
FROM AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT --冲销日期;
WHERE CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_CURRENT.ORG_ID = CR.ORG_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG = NVL('Y', CR.RECEIPT_NUMBER)
AND CRH_CURRENT.STATUS = 'REVERSED')
2。存在收款记录的收款金额：
SELECT CR.AMOUNT * NVL(CR.EXCHANGE_RATE, 1) AMOUNT,
FROM AR_CASH_RECEIPTS_ALL CR,
AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED,
AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT --冲销日期
WHERE 1 = 1
AND CRH_FIRST_POSTED.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_FIRST_POSTED.ORG_ID = CR.ORG_ID
AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG = 'Y'
AND CR.CASH_RECEIPT_ID = &IN_CASH_RECEIPT_ID
AND TRUNC(CRH_FIRST_POSTED.GL_DATE) <=
TRUNC(TO_DATE(&IV_DEADLINE, 'yyyy-mm-dd hh24:mi:ss'))
AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_CURRENT.ORG_ID = CR.ORG_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG = NVL('Y', CR.RECEIPT_NUMBER)
AND CRH_CURRENT.STATUS = 'REVERSED'
--冲销GL日期早于或者等于参数"截止日期不选
AND CRH_CURRENT.GL_DATE >
TRUNC(TO_DATE(&IV_DEADLINE, 'yyyy-mm-dd hh24:mi:ss'))
3。开票已核销
SELECT SUM(DECODE(RCTA.INVOICE_CURRENCY_CODE,
'CNY',
DECODE(UPPER(UPPER(RCTTA.TYPE)) ||
UPPER(ARAA.APPLICATION_TYPE),
'CMCM',
-1 * NVL(ARAA.AMOUNT_APPLIED, 0),
NVL(ARAA.AMOUNT_APPLIED, 0)),
DECODE(UPPER(UPPER(RCTTA.TYPE)) ||
UPPER(ARAA.APPLICATION_TYPE),
'CMCM',
-1 * NVL(ARAA.AMOUNT_APPLIED, 0),
NVL(ARAA.AMOUNT_APPLIED, 0)) * RCTA.EXCHANGE_RATE))
FROM AR.RA_CUSTOMER_TRX_ALL RCTA,
AR.RA_CUST_TRX_TYPES_ALL RCTTA,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA,
AR.AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE RCTA.CUST_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_ID
AND UPPER(RCTTA.TYPE) IN ('INV', 'CM', 'DM')
AND UPPER(RCTTA.POST_TO_GL) = 'Y'
AND UPPER(RCTTA.ACCOUNTING_AFFECT_FLAG) = 'Y'
AND UPPER(RCTLGDA.ACCOUNT_CLASS) = 'REC'
AND UPPER(RCTLGDA.LATEST_REC_FLAG) = 'Y'
AND RCTLGDA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND UPPER(RCTA.COMPLETE_FLAG) =
DECODE(UPPER('n'), 'Y', UPPER(RCTA.COMPLETE_FLAG), 'N', 'Y')
AND (ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID OR
ARAA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID)
AND ARAA.DISPLAY = 'Y'
AND RCTA.TRX_NUMBER = &IN_TRX_NUMBER
4。收款已核销
SELECT ACRA.CASH_RECEIPT_ID,
ACRA.RECEIPT_NUMBER,
DECODE(ACRA.CURRENCY_CODE,
'CNY',
NVL(ARAA.AMOUNT_APPLIED * NVL(ARAA.TRANS_TO_RECEIPT_RATE, 1),
0),
NVL(ARAA.AMOUNT_APPLIED, 0) * ACRA.EXCHANGE_RATE *
NVL(ARAA.TRANS_TO_RECEIPT_RATE, 1))

FROM AR_CASH_RECEIPTS_ALL ACRA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE ACRHA.GL_DATE <= TO_DATE('&deadline_date', 'yyyy-mm-dd')
AND (((ACRA.RECEIPT_METHOD_ID = 1042 AND
ACRHA.STATUS NOT IN ('REMITTED', 'CLEARED' ， 'RISK_ELIMINATED') AND
NVL(ACRHA.CURRENT_RECORD_FLAG, 'Y') = 'Y')) OR
(ACRA.RECEIPT_METHOD_ID <> 1042 AND
NVL(ACRHA.CURRENT_RECORD_FLAG, 'N') = 'Y'))
AND EXISTS (SELECT 'A'
FROM AR_CASH_RECEIPT_HISTORY_ALL T
WHERE T.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
AND T.CURRENT_RECORD_FLAG = 'Y'
AND T.STATUS != 'REVERSED')
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
AND UPPER(ACRHA.STATUS) != 'REVERSED'
AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ARAA.DISPLAY = 'Y'
AND ARAA.GL_DATE <= TO_DATE('&deadline_date', 'yyyy-mm-dd')
AND ARAA.APPLIED_CUSTOMER_TRX_ID <> -1
AND ACRA.RECEIPT_NUMBER = '&IN_RECEIPT_NUMBER';

以上SQL在R12中已经验证过，并非100%原创。希望对大家有帮助。
我也是站在了巨人的肩上。哈哈