AR客户对账单（上月欠款）

create or replace function amountfn
(
    customernum varchar2,
    startdate   date
) return char is
    v_amount         number;
    v_receipt_amount number;
    refundamount     number;
    lastdebt         number;
begin
    ---------------------------**********得到上月欠款**************-------------------------------
    select sum(nvl(rct.extended_amount_tax, 0)) extended_amount --当前参数日期之前的所有货款
      into v_amount
      from (select ctl.customer_trx_line_id,
                   ct.customer_trx_id,
                   ct.initial_customer_trx_id,
                   ct.trx_number,
                   nvl(cux_ar_utl_pkg.get_trx_gl_date(ct.customer_trx_id),
                       ct.trx_date) trx_date,
                   ct.bill_to_customer_id,
                   nvl(ctl.gross_extended_amount, ctl.extended_amount) extended_amount_tax
              from ra_customer_trx_all       ct,
                   ra_customer_trx_lines_all ctl,
                   hz_parties                hpt,
                   hz_cust_accounts          hca,
                   ra_cust_trx_types_all     ctt,
                   mtl_units_of_measure      uom
             where ct.customer_trx_id = ctl.customer_trx_id
               and ct.org_id = ctl.org_id
               and ct.bill_to_customer_id = hca.cust_account_id
               and hca.party_id = hpt.party_id
               and ctt.cust_trx_type_id = ct.cust_trx_type_id
               and ctl.line_type != 'TAX'
               and ctt.type in ('INV', 'CM')
               and ctt.cust_trx_type_id !=
                   cux_ar_utl_pkg.get_return_commitment_type
               and ctl.uom_code = uom.uom_code(+)) rct
     where 1 = 1
       and rct.bill_to_customer_id =
           (select a.customer_id
              from ar_customers a
             where a.customer_number = customernum)
       and rct.trx_date <= trunc(startdate, 'month') - 1;
    dbms_output.put_line(v_amount ||
                         '：v_amountv_amountv_amountv_amountv_amount');
    select nvl(sum(acr.functional_amount), 0) --当前参数日期之前的所有应付款
      into v_receipt_amount
      from ar_cash_receipts_v acr
     where acr.gl_date <= trunc(startdate, 'month') - 1
       and acr.state != 'REVERSED'
       and acr.customer_id =
           (select a.customer_id
              from ar_customers a
             where a.customer_number = customernum);
    dbms_output.put_line(v_receipt_amount ||
                         '：v_receipt_amountv_receipt_amountv_receipt_amount');
    select nvl(sum(araa.amount_applied), 0) --当前参数日期之前的所有退款
      into refundamount
      from ar_cash_receipt_history_all    acrh,
           ar_cash_receipts_all           acr,
           ar_receivable_applications_all araa
     where acrh.gl_date <= trunc(startdate, 'month') - 1
       and acrh.status != 'REVERSED'
       and acr.cash_receipt_id = acrh.cash_receipt_id
       and acrh.cash_receipt_history_id = araa.cash_receipt_history_id
       and araa.applied_customer_trx_id is null
       and acrh.org_id = acr.org_id
       and araa.display = 'Y'
       and araa.status = 'ACTIVITY'
       and acr.pay_from_customer =
           (select a.customer_id
              from ar_customers a
             where a.customer_number = customernum);
    dbms_output.put_line(refundamount ||
                         '：refundAmountrefundAmountrefundAmount');
    lastdebt := (v_amount - v_receipt_amount + refundamount); --小于当前日期之前的所有货款 减去 小于当前日期之前的所有应付帐款 加 小于当前日期之前的所有退款=上月欠款

    return lastdebt;
end;