(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)公式:期末余额 =(开单总额-已核销额)-(收款总额-已核销额) 期初余额 = 期末余额 - (当期开单总额-当期开单核销额)+ (当期收款总额 - 当期收款核销额) |
按月查询客户余额报表的步骤和SQL脚本
最新推荐文章于 2022-05-17 11:15:44 发布