应收计算SQL代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】应收计算SQL
作者:Dezai.CN / 发布于2013/4/8/ 1563
应收计算

	应收开票金额：
	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';
试试其它关键字
　应收计算　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)