一、获得发票信息
SELECT rct.customer_trx_id customer_trx_id, hc.account_number customer_number, party.party_name customer_name, rct.trx_number, rct.trx_date, gd.gl_date, nvl(rct.exchange_rate, 1) exchange_rate, rtt.TYPE FROM hz_cust_accounts hc, hz_parties party, ra_customer_trx_all rct, ra_cust_trx_types_all rtt, ra_cust_trx_line_gl_dist_all gd WHERE rtt.cust_trx_type_id = rct.cust_trx_type_id AND rtt.org_id = rct.org_id AND rct.complete_flag = 'Y' AND rtt.type <> 'BR' AND gd.gl_date < ld_end_date + 1 AND gd.customer_trx_id = rct.customer_trx_id AND gd.account_class = 'REC' AND gd.latest_rec_flag = 'Y' AND rct.set_of_books_id = gn_set_of_books_id AND rct.org_id = gn_org_id AND rct.bill_to_customer_id = hc.cust_account_id AND hc.party_id = party.party_id |
二、获得发票收款核销信息,如需要,可加入是否已审批状态
SELECT nvl(SUM(aaa.total_amount * decode(aaa.ps_class, 'CM', -1, 1) * decode(aaa.adjustment_id, NULL, 1, -1)), 0) INTO ln_adjustment_amount FROM ar_app_adj_v aaa WHERE aaa.customer_trx_id = i_customer_trx_id AND aaa.gl_date < i_end_date + 1; |
三、如果计算客户真正的余额,还要去掉未核销收款
SELECT acr.pay_from_customer customer_id, acr.cash_receipt_id trx_id, SYSDATE - 9999 /*acr.receipt_date*/ trx_date, 2 trx_type, acr.attribute9 contract_number, -1 * (nvl(acr.amount, 0) - (SELECT nvl(SUM(nvl(arp.amount_applied_from, arp.amount_applied)), 0) FROM ar_receivable_applications_all arp WHERE arp.cash_receipt_id = acr.cash_receipt_id AND arp.gl_date < ld_date_to + 1 AND arp.status <> 'UNAPP' )) * nvl(acr.exchange_rate, 1) cny_due_amount, acr.set_of_books_id, acr.org_id FROM ar_cash_receipts_all acr, ar_cash_receipt_history_all crh WHERE acr.amount <> 0 AND crh.first_posted_record_flag(+) = 'Y' AND crh.cash_receipt_id(+) = acr.cash_receipt_id AND crh.gl_date < ld_date_to + 1 AND acr.set_of_books_id = gn_set_of_books_id AND acr.org_id = gn_org_id AND ((lp_customer_id IS NULL AND acr.pay_from_customer IS NOT NULL) OR acr.pay_from_customer = lp_customer_id) AND (EXISTS (SELECT 1 FROM ar_receivable_applications_all v WHERE v.cash_receipt_id = acr.cash_receipt_id AND v.gl_date >= ld_date_to + 1) OR acr.status = 'UNAPP') |
四、如果计算帐龄,注意计算的日期。发票要加上付款条款的日期,可使用函数arpt_sql_func_util.get_first_real_due_date。而未核销收款可依业务的需要取最近或最远日期。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/57020/viewspace-510257/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/57020/viewspace-510257/