数据库表：--CreatetableDROPTABLECUX.cux_ap_invoice_interface;createtableCUX.cux_ap_invoice_interface(OU_NAMEVARCHAR2(240),--组织invoice_typeVARCHAR2(2
数据库表：
验证程序：
-- 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_countAP数据导入程序：
