这是一个在Oracle EBS环境中实现自动创建AP（应付账款）付款的PL/SQL代码示例。代码首先初始化环境，然后获取发票信息、银行账户信息，并处理付款相关的参数，最后执行付款操作。涉及的表和视图包括ap_invoices_all、ap_bank_accounts_all等。
--以下代码于20181002在Tony环境测试通过
/*
BEGIN
--mo_global.init('SQLAP');
apps.fnd_global.apps_initialize(user_id => 1110, resp_id => 50597, resp_appl_id => 20003);
--mo_global.set_policy_context('S', 0);
mo_global.init('M');--务必加上这个语句
END;
-- ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';
*/
/*select * from ap_checks;
select * from po_headers_all
*/
DECLARE
g_user_id CONSTANT NUMBER := fnd_global.user_id;
g_login_id CONSTANT NUMBER := fnd_global.login_id;
--
l_invoice_id NUMBER := 10103;
l_period_name VARCHAR2(20) := '2018-10';
l_set_of_books_id NUMBER;
l_exchange_rate NUMBER;
l_exchange_date DATE;
l_when_payment_accounted ap_system_parameters_all.when_to_account_pmt%TYPE;
l_count NUMBER;
l_stock_name VARCHAR2(50);
l_in_use_by VARCHAR2(20);
l_doc_category_code VARCHAR2(30);
l_last_document_num NUMBER;
l_quick_check VARCHAR2(200);
l_hold_all_payments_flag VARCHAR2(1);
l_payment_type_flag VARCHAR2(1) := 'Q'; --人工M，快速Q，退款R
l_status_lookup_code VARCHAR2(20) := 'NEGOTIABLE';
--
l_row_id VARCHAR2(240);
l_invoice_amount NUMBER; -- 付款额
l_bank_account_id NUMBER;
l_bank_account_name VARCHAR2(120);
l_gl_date DATE := last_day(trunc(SYSDATE));
l_check_id NUMBER;
l_check_number NUMBER;
l_invoice_currency_code VARCHAR2(10);
l_payment_method_lookup_code VARCHAR2(25);
l_address_line1 VARCHAR2(240);
l_address_line2 VARCHAR2(240);
l_address_line3 VARCHAR2(240);
l_checkrun_name VARCHAR2(50); -- 批名
l_check_format_id NUMBER;
l_check_stock_id NUMBER;
l_city VARCHAR2(25);
l_country VARCHAR2(25);
l_bank_account_num VARCHAR2(30);
l_bank_account_type VARCHAR2(30);
l_zip VARCHAR2(20);
l_province VARCHAR2(150);
l_state VARCHAR2(150);
l_address_line4 VARCHAR2(240);
l_county VARCHAR2(25);
l_address_style VARCHAR2(30);
l_org_id NUMBER;
l_vendor_id NUMBER;
l_vendor_site_id NUMBER;
l_VENDOR_SITE_CODE VARCHAR2(30);
l_VENDOR_NAME VARCHAR2(3000);
l_exchange_rate_type VARCHAR2(30);
l_party_site_id number;
l_party_id number;
l_ap_checks_all_row ap_checks_all%ROWTYPE;
--
l_accounting_event_id NUMBER;
--
l_invoice_payment_id NUMBER;
--
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_err_msg VARCHAR2(10000);
PROCEDURE log(p_msg IN VARCHAR2) IS
BEGIN
dbms_output.put_line(p_msg);
END;
PROCEDURE get_msg(x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2) IS
BEGIN
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => x_msg_count,
p_data => x_msg_data);
IF x_msg_count > 1 THEN
x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
p_encoded => fnd_api.g_false);
END IF;
END;
BEGIN
dbms_output.enable(100000);
-- 1.0 获取发票信息
log('Step1.0 获取付款发票信息');
SELECT aia.invoice_amount,
aia.invoice_currency_code,
aia.set_of_books_id,
aia.org_id,
aia.vendor_id,
aia.vendor_site_id,
aia.exchange_rate,
aia.exchange_rate_type,
aia.exchange_date,
(SELECT pv.VENDOR_NAME from po_vendors pv where pv.VENDOR_ID = aia.vendor_id )
INTO l_invoice_amount,
l_invoice_currency_code,
l_set_of_books_id,
l_org_id,
l_vendor_id,
l_vendor_site_id,
l_exchange_rate,
l_exchange_rate_type,
l_exchange_date,
l_VENDOR_NAME
FROM ap_invoices_all aia
WHERE aia.invoice_id = l_invoice_id;
SELECT t.party_site_id, a.party_id
into l_party_site_id, l_party_id
FROM ap_supplier_sites_all t, ap_suppliers a
WHERE a.vendor_id = t.vendor_id
AND t.vendor_site_id = l_vendor_site_id; --p_vendor_site_id;
-- 1.2 获取GL_DATE
log('Step1.2 获取检查应付款选项设置参数 BEGIN');
-- 对应路径 应付超级职责 -> 设置 -> 选项 -> 应付款
-- 页签 本位币, 付款会计.将付款记账: 发放付款时, 币种.使用多币种
BEGIN
SELECT t.when_to_account_pmt
INTO l_when_payment_accounted
FROM ap_system_parameters_all t
WHERE t.set_of_books_id = l_set_of_books_id
AND t.org_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_err_msg := '获取业务实体信息(' || l_org_id || ')异常！' || SQLCODE || ',' ||
SQLERRM;
log(l_err_msg);
--RETURN;
END;
log('Step1.2 获取检查应付款选项设置参数 END WHEN[' || l_when_payment_accounted || ']');
-- 1.3 获取银行信息
log('Step1.3 获取付款银行信息 begin');
BEGIN
/* SELECT aba.bank_account_id, aba.bank_account_num, aba.bank_account_name, aba.bank_account_type
INTO l_bank_account_id, l_bank_account_num, l_bank_account_name, l_bank_account_type
FROM ap_bank_accounts_all aba
WHERE aba.bank_account_name = '虚拟银行'
AND aba.org_id = l_org_id;*/
SELECT ibybanks.bank_account_id,
ibybanks.bank_account_number,
ibybanks.bank_account_name,
ibybanks.bank_account_type
/* ibybanks.bank_name, --银行
ibybanks.bank_branch_name, --分行
ibybanks.bank_account_num_electronic, --银行帐号
ibybanks.**/
INTO l_bank_account_id,
l_bank_account_num,
l_bank_account_name,
l_bank_account_type
FROM iby_external_payers_all iepa,
iby_pmt_instr_uses_all ipiua,
iby_ext_bank_accounts_v ibybanks
WHERE 1 = 1 --iepa.payment_function = 'CUSTOMER_PAYMENT'
-- AND iepa.cust_account_id = p_customer_id --客户帐号ID
AND ibybanks.bank_account_id = ipiua.instrument_id
-- AND ipiua.instrument_type = 'BANKACCOUNT'
AND ipiua.ext_pmt_party_id = iepa.EXT_PAYER_ID
--AND ipiua.payment_function = 'CUSTOMER_PAYMENT'
AND sysdate BETWEEN ibybanks.start_date AND
nvl(ibybanks.end_date, sysdate + 1);
EXCEPTION
WHEN OTHERS THEN
l_err_msg := '获取付款银行 虚拟银行 异常！' || SQLCODE || ',' || SQLERRM;
log(l_err_msg);
RAISE fnd_api.g_exc_error;
END;
log('Step1.3.1 验证付款单据');
/* IF l_bank_account_id IS NOT NULL THEN
SELECT COUNT(1)
INTO l_count
FROM ap_check_stocks_all acs
WHERE acs.bank_account_id = l_bank_account_id;
IF l_count = 0 THEN
log('虚拟银行有误，找不到付款单据');
RAISE fnd_api.g_exc_error;
END IF;
END IF;*/
log('Step1.4.1 获取付款单据号');
/* BEGIN
SELECT NAME,
in_use_by,
check_stock_id,
check_format_id,
payment_method_lookup_code,
doc_category_code,
last_available_document_num
INTO l_stock_name,
l_in_use_by,
l_check_stock_id,
l_check_format_id,
l_payment_method_lookup_code,
l_doc_category_code,
l_last_document_num
FROM ap_check_stocks_active_v acs
WHERE (acs.format_currency_code = 'CNY' OR
(acs.format_currency_code IS NULL AND acs.format_multi_currency_flag = 'Y' AND
acs.bank_multi_currency_flag = 'Y'))
AND acs.disbursement_type_lookup_code != 'RECORDED'
AND SYSDATE < nvl(acs.inactive_date, SYSDATE + 1)
AND acs.payment_method_lookup_code = 'CHECK'
AND acs.bank_account_id = l_bank_account_id;
EXCEPTION
WHEN OTHERS THEN
l_err_msg := '获取付款银行帐号异常:' || SQLCODE || ',' || SQLERRM;
log(l_err_msg);
--RETURN;
END;*/
log('Step1.4.1 获取付款单据[ID: ' || l_check_stock_id || ']');
IF l_in_use_by IS NOT NULL THEN
log('Step1.4.1 付款单据---------------------------END l_in_use_by:' ||
l_in_use_by);
fnd_message.set_name('SQLAP', 'AP_PAY_DOCUMENT_ALREADY_IN_USE');
l_err_msg := '错误: ' || fnd_message.get;
log(l_err_msg);
--RETURN;
END IF;
log('Step1.4.2 锁定银行付款单据----------------------------------------BEGIN');
--锁定银行付款单据
/* BEGIN
SELECT last_document_num + 1
INTO l_check_number
FROM ap_check_stocks_all
WHERE check_stock_id = l_check_stock_id
FOR UPDATE OF last_document_num NOWAIT;
EXCEPTION
WHEN OTHERS THEN
log('锁定单据[' || l_stock_name || ']时异常[' || SQLERRM || ']');
--RETURN;
END;
--检查银行付款单据
IF nvl(l_check_number, 0) > nvl(l_last_document_num, 0) THEN
l_err_msg := '付款单据:' || l_stock_name || '的编号已经超过最大允许值.';
log(l_err_msg);
--RETURN;
END IF;*/
l_check_number := to_number(TO_CHAR(SYSDATE,'yyyymmddhh24miss'));
--检查付款编号是否重复
SELECT COUNT(1)
INTO l_count
FROM ap_checks_all aca
WHERE aca.check_stock_id = l_check_stock_id
AND aca.check_number = l_check_number;
log('l_duplicate:' || l_count);
IF (l_count > 0) THEN
fnd_message.set_name('SQLAP', 'AP_ALL_DUPLICATE_VALUE');
l_err_msg := chr(10) || '错误: 付款编号' || l_check_number || ' ' ||
fnd_message.get;
log(l_err_msg);
--RETURN;
END IF;
log('Step1.4.3 检查付款编号是否重复------------------------------------END');
log('Step1.4 付款单据号[' || l_check_number ||
']---------------------------END');
--check id
SELECT ap_checks_s.nextval INTO l_check_id FROM dual;
--checkrun name
IF l_payment_type_flag = 'Q' THEN
log('Step1.5 获取批名 --------------BEGIN');
BEGIN
SELECT alc.displayed_field
INTO l_quick_check
FROM ap_lookup_codes alc
WHERE alc.lookup_type = 'NLS TRANSLATION'
AND alc.lookup_code = 'QUICKCHECK ID';
EXCEPTION
WHEN OTHERS THEN
log('获取批名前缀时异常[' || SQLERRM || ']');
END;
l_checkrun_name := substrb(l_quick_check,
1,
30 - nvl(lengthb(to_char(l_check_id)), 0)) ||
to_char(l_check_id);
log('Step1.5 获取批名[' || l_checkrun_name || '] --------------END');
END IF;
log('Step1.6 获取供应商信息------------------------------------------BEGIN');
--获取供应商信息
BEGIN
SELECT pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.address_line4,
nvl(pvs.address_style, 'DEFAULT') address_style,
pvs.city,
pvs.county,
pvs.state,
pvs.province,
pvs.zip,
pvs.country,
pvs.hold_all_payments_flag,
pvs.VENDOR_SITE_CODE
INTO l_address_line1,
l_address_line2,
l_address_line3,
l_address_line4,
l_address_style,
l_city,
l_county,
l_state,
l_province,
l_zip,
l_country,
l_hold_all_payments_flag,
l_VENDOR_SITE_CODE
FROM po_vendor_sites_all pvs
WHERE pvs.org_id = l_org_id
AND pvs.vendor_site_id = l_vendor_site_id;
EXCEPTION
WHEN OTHERS THEN
log('获取供应商地点[' || l_vendor_site_id || ']信息时异常[' || SQLERRM || ']');
END;
IF l_hold_all_payments_flag = 'Y' THEN
l_err_msg := '错误: ' || '当前供应商地点设置了"暂挂所有付款",不能创建付款.';
log(l_err_msg);
--RETURN;
END IF;
log('Step4.2.5 获取供应商信息------------------------------------------END');
----RETURN;
-- 2.1 插入供应商付款
log('Step2.1 插入供应商付款-------------------------------------------BEGIN');
BEGIN
/* ap_checks_pkg.insert_row(x_rowid => l_row_id,
x_amount => l_invoice_amount,
x_bank_account_id => l_bank_account_id,
x_bank_account_name => l_bank_account_name,
x_check_date => l_gl_date,
x_check_id => l_check_id,
x_check_number => l_check_number,
x_currency_code => l_invoice_currency_code,
x_last_updated_by => g_user_id,
x_last_update_date => SYSDATE,
x_payment_method_lookup_code => l_payment_method_lookup_code,
x_payment_type_flag => l_payment_type_flag,
x_address_line1 => l_address_line1,
x_address_line2 => l_address_line2,
x_address_line3 => l_address_line3,
x_checkrun_name => l_checkrun_name,
x_check_format_id => l_check_format_id,
x_check_stock_id => l_check_stock_id,
x_city => l_city,
x_country => l_country,
x_created_by => g_user_id,
x_creation_date => SYSDATE,
x_last_update_login
最低0.47元/天 解锁文章
扫一扫
521
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
