本章主要目的是介绍Oracle EBS 应付发票 接口导入的API,因为有时需要开发一些应付发票批量导入或者给外围系统提供应付发票导入功能。
背景介绍:因为有时需要开发一些应付发票批量导入或者从外围系统导入功能,这些时候我们就需要用到Oracle提供的应付发票接口表和API。
Oracle EBS 版本:11.5i
开发工具:PL/SQL Developer
代码:(以下代码已经在11.5i环境测试通过)
- --author:jimmy
- --Date:2014.05.24
- --program:invoice_import
- --desc:应付发票导入程序
- DECLARE
- l_iface_rec ap_invoices_interface%ROWTYPE;
- l_iface_lines_rec ap_invoice_lines_interface%ROWTYPE;
- l_org_id NUMBER := 232;
- gv_user_id NUMBER := 0;--fnd_global.user_id;
- gv_login_id NUMBER := 0;--fnd_global.login_id;
- l_success BOOLEAN;
- --
- v_batch_error_flag VARCHAR2(30);
- v_invoices_fetched NUMBER;
- v_invoices_created NUMBER;
- v_total_invoice_amount NUMBER; -- bug 989221
- v_print_batch_flag VARCHAR2(30);
- l_input_batch_number VARCHAR2(80);
- --
- BEGIN
- --SELECT * FROM fnd_user WHERE user_name='SYSADMIN';
- --初始化环境变量变量
- fnd_global.apps_initialize(user_id => gv_user_id,
- resp_id => 50243,
- resp_appl_id => 20003);
- --set who date
- --发票头最后更新人
- l_iface_rec.last_updated_by := gv_user_id;
- --发票头创建时间
- l_iface_rec.creation_date := SYSDATE;
- --发票头创建人
- l_iface_rec.created_by := gv_user_id;
- --发票头最后登录人
- l_iface_rec.last_update_login := gv_login_id;
- --发票行最后更新时间
- l_iface_lines_rec.last_update_date := SYSDATE;
- --发票行最后更新人
- l_iface_lines_rec.last_updated_by := gv_user_id;
- --发票行创建时间
- l_iface_lines_rec.creation_date := SYSDATE;
- --发票行的创建人
- l_iface_lines_rec.created_by := gv_user_id;
- --发票行最后更新人
- l_iface_lines_rec.last_update_login := gv_login_id;
- --
- --发票头
- --获取发票invoice id
- SELECT ap_invoices_interface_s.NEXTVAL
- INTO l_iface_rec.invoice_id
- FROM dual;
- dbms_output.put_line('提示:发票ID:' || l_iface_rec.invoice_id);
- --发票编号
- l_iface_rec.invoice_num := 'invoice_test_01';
- --发票类型
- l_iface_rec.invoice_type_lookup_code := 'STANDARD';
- --发票日期
- l_iface_rec.invoice_date := SYSDATE;
- --供应商ID
- l_iface_rec.vendor_id := 7811;
- --供应商地点
- l_iface_rec.vendor_site_id := 9099;
- --发票金额
- l_iface_rec.invoice_amount := 1000;
- --发票币别
- l_iface_rec.invoice_currency_code := 'CNY';
- --汇率类型
- l_iface_rec.exchange_rate_type := NULL;
- --汇率日期
- l_iface_rec.exchange_date := NULL;
- --汇率
- l_iface_rec.exchange_rate := NULL;
- --来源
- l_iface_rec.SOURCE := 'INV IMPORT';
- --发票头-附件张数(根据每个环境实际环境填写,可选)
- l_iface_rec.attribute1 := 2;
- --发票批次号
- l_input_batch_number := 'TEST_B_040599';
- --Lookup type SOURCE
- l_iface_rec.group_id := l_iface_rec.invoice_id;
- l_iface_rec.org_id := l_org_id;
- --把数据插入到发票头接口表
- INSERT INTO ap_invoices_interface VALUES l_iface_rec;
- --发票行
- SELECT ap_invoice_lines_interface_s.NEXTVAL
- INTO l_iface_lines_rec.invoice_line_id
- FROM dual;
- --OU
- l_iface_lines_rec.org_id := l_iface_rec.org_id;
- --发票ID
- l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id;
- --发票行日期
- l_iface_lines_rec.accounting_date := SYSDATE;
- --发票行号
- l_iface_lines_rec.line_number := 1;
- --发票行类型
- l_iface_lines_rec.line_type_lookup_code := 'ITEM';
- --发票行金额
- l_iface_lines_rec.amount := 1000;
- --发票行账户
- l_iface_lines_rec.dist_code_combination_id := 4614;
- --把数据插入发票行接口表
- INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;
- --提交事务处理
- COMMIT;
- --调用api把发票导入
- l_success := ap_import_invoices_pkg.import_invoices(p_batch_name => l_input_batch_number,
- p_gl_date => NULL,
- p_hold_code => NULL,
- p_hold_reason => NULL,
- p_commit_cycles => NULL,
- p_source => l_iface_rec.SOURCE,
- p_group_id => l_iface_rec.invoice_id,
- p_conc_request_id => -1,
- p_debug_switch => 'Y',
- p_batch_error_flag => v_batch_error_flag,
- p_invoices_fetched => v_invoices_fetched,
- p_invoices_created => v_invoices_created,
- p_total_invoice_amount => v_total_invoice_amount, -- bug 989221
- p_print_batch => v_print_batch_flag,
- p_calling_sequence => 'invoice_import');
- IF NOT l_success THEN
- dbms_output.put_line('错误提示:发票导入失败。错误信息:' || SQLERRM||v_batch_error_flag);
- --把接口表的数据删除
- --发票头
- DELETE ap_invoices_interface aii
- WHERE aii.invoice_id = l_iface_rec.invoice_id;
- --发票行
- DELETE ap_invoice_lines_interface aili
- WHERE aili.invoice_id = l_iface_rec.invoice_id;
- --提交事务处理
- COMMIT;
- ELSE
- dbms_output.put_line('成功:发票(' || l_iface_rec.invoice_num || ')导入成功');
- END IF;
- END;
需要注意:
1.因为是在PL/SQL Developer工具直接调用,这是需要初始化一下环境变量(用户ID、职责ID、应用ID);
2.因为是在PL/SQL Developer工具直接调用,数据已经插入到标准的接口表,不能够提交请求标准发票接口导入请求“APXIIMPT”,生成应付发票。不过Oracle EBS系统给开发者提供应付发票导入的API(ap_import_invoices_pkg.import_invoices),详细使用方法请看以上的代码;
3.本次测试代码,并没有对数据进行校验,假如实际情况需要对数据进行校验,读者可以自行添加。(校验内容例如:发票号码、供应商、供应商地点、弹性域等等)
以下介绍是,把数据插入到接口表中,通过调用请求生成应付发票(适用范围:需要把改方法挂到Oracle EBS,直接在PL/SQL是不能执行的)。
代码:
- dbms_output.put_line('运行发票接口导入请求 : APXIIMPT');
- l_phase := NULL;
- l_status := NULL;
- l_req_id := 0;
- l_req_id := fnd_request.submit_request(application => 'SQLAP',
- program => 'APXIIMPT',
- description => NULL,
- start_time => NULL,
- sub_request => FALSE,
- argument1 => l_iface_rec.SOURCE,
- argument2 => l_iface_rec.invoice_id ---group_id
- ,
- argument3 => l_input_batch_number --l_batch_number
- ,
- argument4 => '',
- argument5 => '',
- argument6 => '',
- argument7 => 'Y',
- argument8 => 'N',
- argument9 => 'N',
- argument10 => 'N',
- argument11 => 1000,
- argument12 => gv_user_id,
- argument13 => gv_login_id);
- COMMIT;
- IF (l_req_id = 0) THEN
- dbms_output.put_line('错误提示:发票接口导入请求提交出错');
- --把接口表的数据删除
- --发票头
- DELETE ap_invoices_interface aii
- WHERE aii.invoice_id = l_iface_rec.invoice_id;
- --发票行
- DELETE ap_invoice_lines_interface aili
- WHERE aili.invoice_id = l_iface_rec.invoice_id;
- --提交事务处理
- COMMIT;
- dbms_output.put_line('已经把数据接口表的数据删除');
- --RAISE submit_failed;
- ELSE
- COMMIT;
- --等待“运行发票接口导入请求”运行完成
- l_wait_outcome := fnd_concurrent.wait_for_request(request_id => l_req_id,
- INTERVAL => 10,
- phase => l_phase,
- status => l_status,
- dev_phase => l_dev_phase,
- dev_status => l_dev_status,
- message => l_message);
- END IF;
- IF upper(l_dev_phase) = 'COMPLETE' AND upper(l_dev_status) = 'NORMAL' THEN
- dbms_output.put_line('应付发票导入结束:' ||
- to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
- dbms_output.put_line('应付发票导入结束:' ||
- to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
- dbms_output.put_line('应付发票');
- COMMIT;
- ELSE
- dbms_output.put_line('发票接口导入出错,请检查日志文件');
- END IF;