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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24899662/viewspace-680638/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24899662/viewspace-680638/