数据库表：--createtabledroptablecux.cux_ap_invoice_interface;createtablecux.cux_ap_invoice_interface(ou_namevarchar2(240),--组织invoice_typevarchar2(2
数据库表：
-- create tabledrop table cux.cux_ap_invoice_interface;create table cux.cux_ap_invoice_interface( ou_namevarchar2(240), --组织 invoice_typevarchar2(240), --发票类型 vendor_namevarchar2(240), --供应商名称 site_namevarchar2(240), --供应商地点 invoice_datevarchar2(240), --发票日期 gl_datevarchar2(240), --gl日期 invoice_numvarchar2(240), --发票编号 currency_codevarchar2(240), --发票币种 invoice_amountvarchar2(240), --发票金额 exchange_rate_typevarchar2(240), --汇率类型 exchange_ratevarchar2(240), --汇率 pay_groupvarchar2(240), --支付组 pay_methodvarchar2(240), --付款方法 terms_datevarchar2(240), --条件日期 terms_namevarchar2(240), --条件 --ap_code_combinationvarchar2(240), --应付账款帐户() --header_att8varchar2(240), --头弹性8() header_global_att19varchar2(240), --头弹性global_19 line_numvarchar2(240), --行号line_amountvarchar2(240),--行金额 project_namevarchar2(240),--采购项目号 expenditure_typevarchar2(240),--支出类型 expenditure_item_datevarchar2(240),--支出项日期 expenditure_organization varchar2(240),--支出组织 line_gl_datevarchar2(240),--行gl日期 dist_code_combinationvarchar2(240), --分配行账户 org_idvarchar2(240), vendor_idvarchar2(240), vendor_site_idvarchar2(240), dist_ccidvarchar2(240), ap_ccidvarchar2(240), project_idvarchar2(240), task_idvarchar2(240), is_successvarchar2(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 isselect ap.rowid, ap.*from cux.cux_ap_invoice_interface ap; l_num number := 0;begin for i in cur loopl_num := l_num + 1;update cux.cux_ap_invoice_interface apset ap.row_id = l_numwhere ap.rowid = i.rowid; end loop; commit;end;*/--3.校验数据declare cursor cur isselect *from cux.cux_ap_invoice_interface; l_countnumber := 0; l_org_idnumber; l_vendor_idnumber; l_vendor_site_id number; l_dist_ccidnumber; l_ap_ccidnumber; l_project_idnumber; l_task_idnumber; l_flagvarchar2(1);begin for i in cur loopl_flag := 't';beginselect hou.organization_idinto l_org_idfrom hr_operating_units houwhere hou.name = i.ou_name;exceptionwhen others thenl_flag := 'f';l_org_id := null;dbms_output.put_line(i.row_id || 'ou不存在；');end;beginselect pv.vendor_idinto l_vendor_idfrom po_vendors pvwhere pv.vendor_name = rtrim(i.vendor_name);exceptionwhen others thenl_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_idfrom po_vendor_sites_all pvs, po_vendors pvwhere pv.vendor_name = rtrim(i.vendor_name)and pv.vendor_id = pvs.vendor_idand pvs.org_id = l_org_id;*/select pvs.vendor_site_idinto l_vendor_site_idfrom po_vendor_sites_all pvs, po_vendors pvwhere pv.vendor_name = rtrim(i.vendor_name)and pv.vendor_id = pvs.vendor_idand pvs.org_id = l_org_idand pvs.vendor_site_code = i.site_name;exceptionwhen others thenl_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) thenl_flag := 'f';dbms_output.put_line(i.row_id || '该外币发票没有汇率或者汇率类型；');end if;beginselect gcc.code_combination_idinto l_dist_ccidfrom gl_code_combinations_kfv gccwhere gcc.concatenated_segments = i.dist_code_combination;exceptionwhen others thenl_flag:= 'f';l_dist_ccid := null;dbms_output.put_line(i.row_id || '分配帐户不存在或不唯一；');end;/* if i.ap_code_combination is not null thenbeginselect gcc.code_combination_idinto l_ap_ccidfrom gl_code_combinations_kfv gccwhere gcc.concatenated_segments = i.ap_code_combination;exceptionwhen others thenl_flag := 'f';l_ap_ccid := null;dbms_output.put_line(i.invoice_num || '负债帐户不存在或不唯一；');end;elsel_ap_ccid := null;end if;*/beginselect ppa.project_idinto l_project_idfrom pa_projects_all ppawhere ppa.segment1 = rtrim(i.project_name)and ppa.org_id = l_org_id;exceptionwhen others thenl_flag:= 'f';l_project_id := null;dbms_output.put_line(i.row_id || '采购项目不存在或不唯一；');end;beginselect pt.task_idinto l_task_idfrom pa_projects_all ppa, pa_tasks ptwhere ppa.segment1 = rtrim(i.project_name)and ppa.org_id = l_org_idand ppa.project_id = pt.project_id;exceptionwhen others thenl_flag := 'f';l_task_id := null;dbms_output.put_line(i.row_id || '采购项目对应的任务号不存在；');end;/*select count(1)into l_countfrom fnd_flex_values_vl ffvv, fnd_flex_value_sets ffvswhere ffvv.flex_value_set_id = ffvs.flex_value_set_idand ffvs.flex_value_set_name = 'cux_hr_people_grade'and ffvv.flex_value = i.header_att8;if l_count
ap数据导入程序：