AP发票数据导入接口开发实例（ap_invoices_interface）

数据库表：--CreatetableDROPTABLECUX.cux_ap_invoice_interface;createtableCUX.cux_ap_invoice_interface(OU_NAMEVARCHAR2(240),--组织invoice_typeVARCHAR2(2

数据库表：

-- Create table DROP TABLE CUX.cux_ap_invoice_interface; create table CUX.cux_ap_invoice_interface ( OU_NAME VARCHAR2(240), --组织 invoice_type VARCHAR2(240), --发票类型 vendor_name VARCHAR2(240), --供应商名称 site_name VARCHAR2(240), --供应商地点 invoice_date VARCHAR2(240), --发票日期 gl_date VARCHAR2(240), --GL日期 invoice_num VARCHAR2(240), --发票编号 currency_code VARCHAR2(240), --发票币种 invoice_amount VARCHAR2(240), --发票金额 exchange_rate_type VARCHAR2(240), --汇率类型 exchange_rate VARCHAR2(240), --汇率 pay_group VARCHAR2(240), --支付组 pay_method VARCHAR2(240), --付款方法 terms_date VARCHAR2(240), --条件日期 terms_name VARCHAR2(240), --条件 --ap_code_combination varchar2(240), --应付账款帐户() --header_att8 VARCHAR2(240), --头弹性8() header_global_att19 VARCHAR2(240), --头弹性global_19 line_num VARCHAR2(240), --行号 line_amount VARCHAR2(240), --行金额 project_name VARCHAR2(240), --采购项目号 expenditure_type VARCHAR2(240), --支出类型 expenditure_item_date VARCHAR2(240), --支出项日期 expenditure_organization VARCHAR2(240), --支出组织 line_gl_date VARCHAR2(240), --行GL日期 dist_code_combination VARCHAR2(240), --分配行账户 org_id VARCHAR2(240), vendor_id VARCHAR2(240), vendor_site_id VARCHAR2(240), dist_ccid VARCHAR2(240), ap_ccid VARCHAR2(240), project_id VARCHAR2(240), task_id VARCHAR2(240), is_success VARCHAR2(1) ) tablespace APPS_TS_TX_DATA;

验证程序：

-- ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE'; /* alter table cux.cux_ap_invoice_interface add org_id number; alter table cux.cux_ap_invoice_interface add vendor_id number; alter table cux.cux_ap_invoice_interface add vendor_site_id number; alter table cux.cux_ap_invoice_interface add dist_ccid number; alter table cux.cux_ap_invoice_interface add ap_ccid number; alter table cux.cux_ap_invoice_interface add project_id number; alter table cux.cux_ap_invoice_interface add task_id number; */ --1、导入数据 /*select * from CUX.cux_ap_invoice_interface for update*/ --2.为数据分配编号 /*DECLARE CURSOR cur IS SELECT ap.rowid, ap.* FROM cux.cux_ap_invoice_interface ap; l_num NUMBER := 0; BEGIN FOR i IN cur LOOP l_num := l_num + 1; UPDATE cux.cux_ap_invoice_interface ap SET ap.row_id = l_num WHERE ap.rowid = i.rowid; END LOOP; COMMIT; END; */ --3.校验数据 DECLARE CURSOR cur IS SELECT * FROM cux.cux_ap_invoice_interface; l_count NUMBER := 0; l_org_id NUMBER; l_vendor_id NUMBER; l_vendor_site_id NUMBER; l_dist_ccid NUMBER; l_ap_ccid NUMBER; l_project_id NUMBER; l_task_id NUMBER; l_flag VARCHAR2(1); BEGIN FOR i IN cur LOOP l_flag := 'T'; BEGIN SELECT hou.organization_id INTO l_org_id FROM hr_operating_units hou WHERE hou.name = i.ou_name; EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_org_id := NULL; dbms_output.put_line(i.row_id || 'OU不存在；'); END; BEGIN SELECT pv.vendor_id INTO l_vendor_id FROM po_vendors pv WHERE pv.vendor_name = rtrim(i.vendor_name); EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_vendor_id := NULL; dbms_output.put_line(i.row_id || '供应商不存在；'); END; BEGIN /*SELECT max(pvs.vendor_site_id) INTO l_vendor_site_id FROM po_vendor_sites_all pvs, po_vendors pv WHERE pv.vendor_name = rtrim(i.vendor_name) AND pv.vendor_id = pvs.vendor_id AND pvs.org_id = l_org_id;*/ SELECT pvs.vendor_site_id INTO l_vendor_site_id FROM po_vendor_sites_all pvs, po_vendors pv WHERE pv.vendor_name = rtrim(i.vendor_name) AND pv.vendor_id = pvs.vendor_id AND pvs.org_id = l_org_id AND pvs.vendor_site_code = i.site_name; EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_vendor_site_id := NULL; dbms_output.put_line(i.row_id || '供应商在该OU下不存在地点；'); END; IF i.currency_code <> 'CNY' AND (i.exchange_rate_type IS NULL OR i.exchange_rate IS NULL) THEN l_flag := 'F'; dbms_output.put_line(i.row_id || '该外币发票没有汇率或者汇率类型；'); END IF; BEGIN SELECT gcc.code_combination_id INTO l_dist_ccid FROM gl_code_combinations_kfv gcc WHERE gcc.concatenated_segments = i.dist_code_combination; EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_dist_ccid := NULL; dbms_output.put_line(i.row_id || '分配帐户不存在或不唯一；'); END; /* IF i.ap_code_combination IS NOT NULL THEN BEGIN SELECT gcc.code_combination_id INTO l_ap_ccid FROM gl_code_combinations_kfv gcc WHERE gcc.concatenated_segments = i.ap_code_combination; EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_ap_ccid := NULL; dbms_output.put_line(i.invoice_num || '负债帐户不存在或不唯一；'); END; ELSE l_ap_ccid := NULL; END IF;*/ BEGIN SELECT ppa.project_id INTO l_project_id FROM pa_projects_all ppa WHERE ppa.segment1 = rtrim(i.project_name) AND ppa.org_id = l_org_id; EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_project_id := NULL; dbms_output.put_line(i.row_id || '采购项目不存在或不唯一；'); END; BEGIN SELECT pt.task_id INTO l_task_id FROM pa_projects_all ppa, pa_tasks pt WHERE ppa.segment1 = rtrim(i.project_name) AND ppa.org_id = l_org_id AND ppa.project_id = pt.project_id; EXCEPTION WHEN OTHERS THEN l_flag := 'F'; l_task_id := NULL; dbms_output.put_line(i.row_id || '采购项目对应的任务号不存在；'); END; /*SELECT COUNT(1) INTO l_count FROM fnd_flex_values_vl ffvv, fnd_flex_value_sets ffvs WHERE ffvv.flex_value_set_id = ffvs.flex_value_set_id AND ffvs.flex_value_set_name = 'CUX_HR_PEOPLE_GRADE' AND ffvv.flex_value = i.header_att8; IF l_count <1 THEN l_flag := 'F'; dbms_output.put_line(i.invoice_num || '事业部板块信息错误；'); END IF;*/ IF l_flag = 'T' THEN UPDATE cux.cux_ap_invoice_interface a SET a.org_id = l_org_id, a.vendor_id = l_vendor_id, a.vendor_site_id = l_vendor_site_id, a.dist_ccid = l_dist_ccid, a.ap_ccid = l_ap_ccid, a.project_id = l_project_id, a.task_id = l_task_id, a.is_success = 'T' WHERE a.row_id = i.row_id; END IF; END LOOP; COMMIT; END; --4.导入数据 --5,运行请求：应付款管理系统开放接口导入

AP数据导入程序：