ar.ra_customer_trx_all rcta where trx_number='10009' 发票号 (rcta.customer_trx_id=apsa.customer_trx_id)
ar.ar_payment_schedules_all apsa, 应收款表 (apsa.PAYMENT_SCHEDULE_ID= araa.APPLIED_PAYMENT_SCHEDULE_ID)
ar.ar_receivable_applications_all araa, AMOUNT_APPLIED ---已核销金额 ( araa.CASH_RECEIPT_ID=acra.CASH_RECEIPT_ID)
ar.ar_cash_receipts_all acra, receipt_number ='00028722' 收款编号
ar.ar_cash_receipt_history_all acrha, round(nvl(acrha.amount,0),2) net_amt, --收款金额
ar.ar_receipt_methods arm,
ar.ra_customers rc,
ar.RA_ADDRESSES_ALL raa,
ar.ra_site_uses_all rsua,
select * from ar_distributions_all 分 录表
apps.ra_cust_trx_types_all
这是开发按月查询客户余额报表的步骤和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)当期开单总额,当期收款总额
在计算总额时限定rctlgda.gl_date的起始日期
(7)当期开单核销额,当期收款核销额
在计算总额时限定araa.gl_date的起始日期
(8)公式:期末余额 =(开单总额-已核销额)-(收款总额-已核销额)
期初余额 = 期末余额 - (当期开单总额-当期开单核销额)+ (当期收款总额 - 当期收款核销额)
具体报表开发的话我想按照这个思路后应该不难了吧,数据的准确性已经在我这边的客户那核对过,但是由于各个环境情况不一样,可能存在未考虑到的情况。如发现的话,请告知,谢谢
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12122734/viewspace-329664/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12122734/viewspace-329664/