客户余额汇总表

sunweijie22

网上搜到一个关于客户余额汇总相关的sql，不知道他这样考虑是否正确，欢迎大家讨论！！！
这是开发按月查询客户余额报表的步骤和SQL脚本，希望对你有用
（1） 收款总额
SELECT SUM (DECODE (acra.currency_code, 'CNY', acra.amount, acra.amount * acra.exchange_rate) )
FROM ar_cash_receipts_all acra,
ar_cash_receipt_history_all acrha,
ra_site_uses_all rsua
WHERE acra.pay_from_customer = 4481
AND acra.customer_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID
AND rsua.site_use_code = 'BILL_TO'
AND acrha.gl_date <= to_date('20030228','yyyymmdd')
AND ((
( acra.receipt_method_id = 1042 ----票据类收款的id
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.org_id = 1
AND acra.cash_receipt_id = acrha.cash_receipt_id
AND UPPER (acrha.status) != 'REVERSED'
（2）开单总额
SELECT SUM (DECODE (rcta.invoice_currency_code, 'CNY', rctla.extended_amount, rctla.extended_amount * rcta.exchange_rate))
FROM ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rcta.customer_trx_id = rctla.customer_trx_id
AND 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 rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
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 rsua.site_use_code = 'BILL_TO'
（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 ra_customer_trx_all rcta
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
, ar_receivable_applications_all araa
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
AND 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 rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
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 rsua.site_use_code = 'BILL_TO' -- 2002/08/22
AND (araa.applied_customer_trx_id = rcta.customer_trx_id
OR araa.customer_trx_id = rcta.customer_trx_id
)
AND araa.display = 'Y'
AND araa.gl_date <= to_date('20030331','yyyymmdd')
（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
, ra_site_uses_all rsua
, ar_receivable_applications_all araa
WHERE acra.pay_from_customer = 4481
AND acra.customer_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rsua.site_use_code = 'BILL_TO' -- added by Devy on 2002/07/30
AND acrha.gl_date <= to_date('20030331','yyyymmdd')
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.org_id = 1
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('20030331','yyyymmdd') -- NO SPECIFY PERIOD_NAME
AND araa.applied_customer_trx_id <> -1
（5）汇兑损益
create or replace view AR_EXCHANGE_GAIN_LOSS_V as
select app.cash_receipt_id,sum(APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO,APP.ACCTD_AMOUNT_APPLIED_FROM)) EXCHANGE_GAIN_LOSS
FROM AR_RECEIVABLE_APPLICATIONS APP
where app.display = 'Y'
group by app.cash_receipt_id
(6)开单的调整额：
SELECT SUM (DECODE(rcta.invoice_currency_code,'CNY',
NVL (aaa.amount, 0),
NVL (aaa.amount, 0) * rcta.exchange_rate)
)
FROM ra_customer_trx_all rcta
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
, ar_adjustments_all aaa
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID
AND rsua.location = 开单至地点
AND 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 rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
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 (x_include_unclosed)
, 'Y', UPPER
(rcta.complete_flag)
, 'N', 'Y'
)
AND rsua.site_use_code = 'BILL_TO' -- 2002/08/22
AND aaa.customer_trx_id = rcta.customer_trx_id
AND aaa.gl_date <= to_date('20030331','yyyymmdd')
（6）当期开单总额，当期收款总额
在计算总额时限定rctlgda.gl_date的起始日期
（7）当期开单核销额，当期收款核销额
在计算总额时限定araa.gl_date的起始日期
（8）公式：期末余额 =（开单总额-已核销额）-（收款总额-已核销额）
期初余额 = 期末余额 - （当期开单总额-当期开单核销额）+ （当期收款总额 - 当期收款核销额）
具体报表开发的话我想按照这个思路后应该不难了吧，数据的准确性已经在我这边的客户那核对过，但是由于各个环境情况不一样，可能存在未考虑到的情况。如发现的话，请告知，谢谢