/*=====================================================根據 一張發票的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 ISl_api_name CONSTANT VARCHAR2(30) := 'GET_CHECK_AMOUT_PERIOD';l_moudle CONSTANT VARCHAR2(100) := g_pkg_name || '.' || l_api_name ||' : ';l_period_amount NUMBER;BEGINSELECT 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_amountFROM ap_invoices_all ai, po_vendors pv, ap_terms_vl atWHERE ai.vendor_id = pv.vendor_idAND ai.terms_id = at.term_idAND ai.terms_id IN (10250, 10310, 10259)AND ai.cancelled_date IS NULLAND ai.source IN ('ERS', 'USE', 'CUX_PO_INVOICE', 'RTS')AND ai.org_id = p_org_idAND to_char(ai.invoice_date, 'YYYY-MM') = to_char(p_date, 'YYYY-MM')AND ai.vendor_id = p_vendor_idGROUP BY ai.vendor_id,ai.terms_id,pv.segment1,pv.vendor_name,at.nameORDER BY ai.vendor_id, ai.terms_id;RETURN l_period_amount;EXCEPTIONWHEN no_data_found THENdbms_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 THENdbms_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 THENdbms_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;

?

總結

以上是生活随笔為你收集整理的Oracle 根据一张发票的供应商，取供应商所有符合条件的发票的总额的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。