AP发票数据导入接口开发实例(ap_invoices_interface)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了AP发票数据导入接口开发实例(ap_invoices_interface),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3813字,纯文字阅读大概需要6分钟。
内容图文
![AP发票数据导入接口开发实例(ap_invoices_interface)](/upload/InfoBanner/zyjiaocheng/546/de4b6dc228494deabc1ade0363c8a367.jpg)
数据库表:--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_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数据导入程序:
内容总结
以上是互联网集市为您收集整理的AP发票数据导入接口开发实例(ap_invoices_interface)全部内容,希望文章能够帮你解决AP发票数据导入接口开发实例(ap_invoices_interface)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。