CREATE OR REPLACE FUNCTION  BYamountFN(CUSTOMERNUM varchar2,
                                    startdate   date,
                                    enddate     date) RETURN CHAR IS
  v_amount          number;
  v_receipt_amount  number;
  refundAmount      number;
  l_receive_amount1 number;
  l_receive_amount2 number;
  lastdebt          number;
  thisamount        number;
  thismoeny         number;
  upExtendedamount  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 ctt.NAME != 'SH-内销开帐'
             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 <= startdate - 1;

 

  SELECT SUM(nvl(acr.functional_amount, 0)) --当前参数日期之前的所有应付款
    INTO v_receipt_amount
    FROM ar_cash_receipts_v acr
   WHERE acr.gl_date <= startdate - 1
     AND acr.state != 'REVERSED'
     AND acr.customer_id =
         (SELECT a.CUSTOMER_ID
            FROM ar_customers a
           where a.CUSTOMER_NUMBER = CUSTOMERNUM);

 

  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 <= startdate - 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);

  lastdebt := (v_amount - v_receipt_amount+refundAmount); --小于当前日期之前的所有货款 减去 小于当前日期之前的所有应付帐款 加小于当前日期之前的所有退款=上月欠款
  ---------------------------***********本月货款**********-------------------------------
  select nvl(sum(extended_amount),0)
    into thisamount
    from (SELECT ct.trx_number,
                 ct.trx_date,
                 decode(ctl.gross_extended_amount,
                        null,
                        ctl.extended_amount,
                        ctl.gross_extended_amount) as extended_amount
            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,
                 ra_cust_trx_line_gl_dist_all rctl
           WHERE ct.customer_trx_id = ctl.customer_trx_id
             AND RCTL.Customer_Trx_Id = CT.CUSTOMER_TRX_ID
             AND 'REC' = rctl.ACCOUNT_CLASS
             AND 'Y' = rctl.LATEST_REC_FLAG
             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 ctt.NAME != 'SH-内销开帐'
             AND ctl.uom_code = uom.uom_code(+)
             AND to_char(RCTL.GL_DATE, 'yyyy-mm-dd') BETWEEN
                 TO_CHAR(startdate, 'YYYY-MM-DD') AND
                 TO_CHAR(enddate, 'YYYY-MM-DD')
             AND hca.account_number = CUSTOMERNUM);
  ---------------------------**********本月回款************-------------------------------
  SELECT nvl(SUM(acr.functional_amount), 0) --付款
    INTO l_receive_amount1
    FROM ar_cash_receipts_v acr
   WHERE TO_CHAR(acr.gl_date, 'YYYY-MM-DD') BETWEEN
         TO_CHAR(startdate, 'YYYY-MM-DD') AND
         TO_CHAR(enddate, 'YYYY-MM-DD')
     AND acr.state != 'REVERSED'
     AND acr.customer_id =
         (SELECT a.CUSTOMER_ID
            FROM ar_customers a
           where a.CUSTOMER_NUMBER = CUSTOMERNUM);

  SELECT nvl(sum(araa.amount_applied),0) --退款
    into l_receive_amount2
    FROM AR_CASH_RECEIPT_HISTORY_ALL    acrh,
         AR_CASH_RECEIPTS_all           aCR,
         AR_RECEIVABLE_APPLICATIONS_all araa
   WHERE TO_CHAR(acrh.gl_date, 'YYYY-MM-DD') BETWEEN
         TO_CHAR(startdate, 'YYYY-MM-DD') AND
         TO_CHAR(enddate, 'YYYY-MM-DD')
     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);
  thismoeny := (l_receive_amount1 - l_receive_amount2); --本月回款 = 本月应付帐款-本月退款
  ---------------------------**********本月欠款************-------------------------------             
  --本月欠款= 上月欠款+本月货款-本月回款         
  upExtendedamount := (lastdebt + thisamount - thismoeny);

  return upExtendedamount;
end;

 

 

 

 

 

cux_ar_utl_pkg.get_return_commitment_type

 

FUNCTION get_return_commitment_type RETURN NUMBER IS
    CURSOR csr_type IS
      SELECT cust_trx_type_id
        FROM ra_cust_trx_types_all
       WHERE NAME = 'SH-保证金退回';
    retval NUMBER;
  BEGIN
    OPEN csr_type;
    FETCH csr_type
      INTO retval;
    CLOSE csr_type;
    RETURN nvl(retval, -1);
  END;

 

公式：上月欠款+本月货款-本月回款=本月欠款