一、获得发票信息

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博客

客户余额报表

最新文章