数据库表：--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
AP数据导入程序：
