AP-应付发票余额

摘自：http://blog.csdn.net/qq51567242/article/details/7634376

AP-应付发票余额

CREATE OR REPLACE FUNCTION MEW_GET_APINVOICE_BALANCE_F(P_INVOICE_ID NUMBER)
  RETURN NUMBER IS
  V_INVOICE_AMOUNT           NUMBER;
  V_PAYMENT_AMOUNT           NUMBER;
  V_PREPAID_AMOUNT           NUMBER;
  V_PREPAID_AMOUNT1          NUMBER;
  V_INVOICE_TYPE_LOOKUP_CODE VARCHAR2(30);
BEGIN
  --1发票金额
  SELECT AIA.INVOICE_AMOUNT
        ,AIA.INVOICE_TYPE_LOOKUP_CODE
    INTO V_INVOICE_AMOUNT
        ,V_INVOICE_TYPE_LOOKUP_CODE
    FROM AP_INVOICES_ALL AIA
   WHERE AIA.INVOICE_ID = P_INVOICE_ID;
  --2正常付款金额
  BEGIN
    SELECT SUM(NVL(AIP.AMOUNT
                  ,0)) AMOUNT
      INTO V_PAYMENT_AMOUNT
      FROM AP_INVOICE_PAYMENTS_ALL AIP
          ,AP_INVOICES_ALL         AI
          ,AP_INVOICES_ALL         AI2
          ,AP_CHECKS_ALL           AC
     WHERE AIP.INVOICE_ID = AI.INVOICE_ID
       AND AIP.OTHER_INVOICE_ID = AI2.INVOICE_ID(+)
       AND AIP.CHECK_ID = AC.CHECK_ID
       AND AIP.AMOUNT <> 0
       AND AIP.INVOICE_ID = P_INVOICE_ID;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      V_PAYMENT_AMOUNT := 0;
  END;

  IF V_INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
  THEN
    --预付款发票核销普通发票的金额
    SELECT (-1) * SUM(AID1.AMOUNT) PREPAY_AMOUNT_APPLIED
      INTO V_PREPAID_AMOUNT
      FROM AP_INVOICES_ALL              AI
          ,AP_INVOICE_DISTRIBUTIONS_ALL AID1
          ,AP_INVOICE_DISTRIBUTIONS_ALL AID2
     WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
       AND AI.INVOICE_ID = AID1.INVOICE_ID
       AND AID1.AMOUNT < 0
       AND NVL(AID1.REVERSAL_FLAG
              ,'N') != 'Y'
       AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
       AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN
           ('PREPAYMENT'
           ,'CREDIT'
           ,'DEBIT')
       AND AID2.INVOICE_ID = P_INVOICE_ID;
  ELSE
    --普通发票核销预付款发票的金额
    SELECT (-1) * SUM(AID1.AMOUNT) PREPAY_AMOUNT_APPLIED
      INTO V_PREPAID_AMOUNT
      FROM AP_INVOICES_ALL              AI
          ,AP_INVOICE_DISTRIBUTIONS_ALL AID1
          ,AP_INVOICE_DISTRIBUTIONS_ALL AID2
     WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
       AND AI.INVOICE_ID = AID2.INVOICE_ID
       AND AID1.AMOUNT < 0
       AND NVL(AID1.REVERSAL_FLAG
              ,'N') != 'Y'
       AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
       AND AID1.INVOICE_ID = P_INVOICE_ID;
  
  END IF;

  DBMS_OUTPUT.PUT_LINE(' 1v_invoice_amount: ' || V_INVOICE_AMOUNT ||
                       ' 2v_payment_amount: ' || V_PAYMENT_AMOUNT ||
                       ' 3v_prepaid_amount: ' || V_PREPAID_AMOUNT);

  IF V_INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
  THEN
  
    RETURN NVL(V_PAYMENT_AMOUNT
              ,0) - NVL(V_PREPAID_AMOUNT
                        
                       ,0);
  ELSE
    RETURN NVL(V_INVOICE_AMOUNT
              ,0) - NVL(V_PAYMENT_AMOUNT
                       ,0) - NVL(V_PREPAID_AMOUNT
                                ,0);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;