Oracle 根据一张发票的供应商，取供应商所有符合条件的发票的总额-优快云博客

本文介绍了一个SQL函数，用于计算指定组织ID、发票日期和供应商ID下的所有符合条件的发票本位币总额。该函数考虑了特定的付款条件，并返回在特定期间内的总金额。

/*=====================================================
  根据 一张发票的org_id，发票日期p_date，发票的供应商vendor_id 
    取得 该发票 的供应商 在该期间下，特定付款条件下的所有发票本位币总额
  =====================================================*/
  FUNCTION get_check_amout_period(p_org_id    IN NUMBER,
                                  p_date      IN DATE,
                                  p_vendor_id IN NUMBER) RETURN NUMBER IS
    l_api_name CONSTANT VARCHAR2(30) := 'GET_CHECK_AMOUT_PERIOD';
    l_moudle   CONSTANT VARCHAR2(100) := g_pkg_name || '.' || l_api_name ||
                                         ' : ';
    l_period_amount NUMBER;
  BEGIN
  
    SELECT SUM(nvl(ai.base_amount, ai.invoice_amount))
    /*ai.vendor_id,
    ai.terms_id,
    pv.segment1  vendor_number,
    pv.vendor_name,
    at.name      term_name,*/
    
      INTO l_period_amount
      FROM ap_invoices_all ai, po_vendors pv, ap_terms_vl at
     WHERE ai.vendor_id = pv.vendor_id
       AND ai.terms_id = at.term_id
       AND ai.terms_id IN (10250, 10310, 10259)
       AND ai.cancelled_date IS NULL
       AND ai.source IN ('ERS', 'USE', 'CUX_PO_INVOICE', 'RTS')
       AND ai.org_id = p_org_id
       AND to_char(ai.invoice_date, 'YYYY-MM') = to_char(p_date, 'YYYY-MM')
       AND ai.vendor_id = p_vendor_id
     GROUP BY ai.vendor_id,
              ai.terms_id,
              pv.segment1,
              pv.vendor_name,
              at.name
     ORDER BY ai.vendor_id, ai.terms_id;
    RETURN l_period_amount;
  EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line(l_moudle || ' no_data_found error occured!');
      cux_avic_conc_utl.log_msg(l_moudle || 'no_data_found error occured!');
      cux_avic_conc_utl.log_msg(l_moudle || 'p_org_id = ' || p_org_id);
      cux_avic_conc_utl.log_msg(l_moudle || 'p_date = ' ||
                                to_char(p_date, 'YYYY-MM-DD HH24:MI:SS'));
      cux_avic_conc_utl.log_msg(l_moudle || 'p_vendor_id = ' ||
                                p_vendor_id);
      RETURN NULL;
    WHEN too_many_rows THEN
      dbms_output.put_line(l_moudle || ' too_many_rows error occured!');
      cux_avic_conc_utl.log_msg(l_moudle || 'too_many_rows error occured!');
      cux_avic_conc_utl.log_msg(l_moudle || 'p_org_id = ' || p_org_id);
      cux_avic_conc_utl.log_msg(l_moudle || 'p_date = ' ||
                                to_char(p_date, 'YYYY-MM-DD HH24:MI:SS'));
      cux_avic_conc_utl.log_msg(l_moudle || 'p_vendor_id = ' ||
                                p_vendor_id);
      RETURN NULL;
    WHEN OTHERS THEN
      dbms_output.put_line(l_moudle || 'unexception error occured!');
      cux_avic_conc_utl.log_msg(l_moudle || 'unexception error occured!');
      cux_avic_conc_utl.log_msg(l_moudle || 'p_org_id = ' || p_org_id);
      cux_avic_conc_utl.log_msg(l_moudle || 'p_date = ' ||
                                to_char(p_date, 'YYYY-MM-DD HH24:MI:SS'));
      cux_avic_conc_utl.log_msg(l_moudle || 'p_vendor_id = ' ||
                                p_vendor_id);
      RETURN NULL;
  END;