在EBS 系统中客制化采购对账系统时，采用了ISP客户化，采购对账流程经历：1、手动/自动生成采购对账单；2、将采购对账单提交给供应商查看/确认；3、将供应商确认后的对账单进行审批，审批可提交放置在OA系统；4、审批后的对账单进行立账，即产生AP发票。
本章简要梳理产生AP发票的过程，即AP发票的接口导入。
AP发票接口相关表主要有：
ap_invoices_interface
ap_invoice_lines_interface
ap_interface_rejections
以及 APXIIMPT 标准发票接口导入请求
1、首先是校验需要导入发票的对账单信息，校验数据是否完整/正确，校验完成后再分别导入到AP接口表中。
可创建PKG进行CODE。
org_id 组织ID不可为空，有效
vendor_id 供应商ID不可为空，有效
vendor_site_id 供应商地点ID不可为空 ，有效
currency_code 币种不可为空，有效，非本位币还需校验汇率
invoice_number 发票号不可为空，且不ap_invoices_all 重复
invoice_date 发票日期不可为空
gl_date 记账日期不可为空
condition_date 条件日期不可为空
terms_id 付款条件不可为空，有效
payment_method_lookup_code 付款方法不可为空，有效
select count(1)
into v_cnt
from Iby_Payment_Methods_b ipmb
where ipmb.payment_method_code = c.payment_method_lookup_code
and (ipmb.inactive_date is null or ipmb.inactive_date >= trunc(c.invoice_date));
if v_cnt = 0 then
v_error_message := v_error_message || chr(13) || '付款方法【' ||
c.payment_method_lookup_code || '】无效！';
v_error_count := v_error_count + 1;
end if;
LINE_LOCATION_ID 发运行id ,不为空，有效
AMOUNT 金额不可为空，有效
inventory_item_id 物料ID不可为空！
invoice_quantity 开票数量不可为空！
发票行ID 的产生：ap_invoice_lines_interface_s.nextval => v_invoice_line_id
发票ID 的产生：ap_invoices_interface_s.nextval => v_invoice_id
(1)导入ap_invoice_lines_interface数据
一个发票可能存在多条发票行。
声明接口表类型变量：
TYPE ap_inv_lines_ifc_tbl IS TABLE OF ap_invoice_lines_interface%rowtype INDEX BY BINARY_INTEGER;
v_ap_inv_line_ifc_tbl_c ap_inv_lines_ifc_tbl;
查看ap_invoice_lines_interface的表栏位字段，其中是ap发票接口所必需的，如下：
v_ap_inv_line_ifc_tbl_c(i).invoice_id := v_invoice_id_c; --发票ID
v_ap_inv_line_ifc_tbl_c(i).invoice_line_id := v_invoice_line_id; --发票行ID
v_ap_inv_line_ifc_tbl_c(i).line_number := d.detail_number; --发票行号
v_ap_inv_line_ifc_tbl_c(i).line_type_lookup_code := xxcusp001.C_AP_INV_LINE_TYPE; --发票行类型
v_ap_inv_line_ifc_tbl_c(i).amount := d.amount; --金额
v_ap_inv_line_ifc_tbl_c(i).accounting_date := c.gl_date; --记账日期
v_ap_inv_line_ifc_tbl_c(i).description := ''; --发票行描述
v_ap_inv_line_ifc_tbl_c(i).amount_includes_tax_flag := 'N';
v_ap_inv_line_ifc_tbl_c(i).po_header_id := v_po_header_id; --PO 头ID
v_ap_inv_line_ifc_tbl_c(i).po_line_id := v_po_line_id; --PO 行ID
v_ap_inv_line_ifc_tbl_c(i).po_line_location_id := d.line_location_id; --PO 发运行 id
v_ap_inv_line_ifc_tbl_c(i).po_release_id := v_po_release_id;
v_ap_inv_line_ifc_tbl_c(i).org_id := org_id; --组织
v_ap_inv_line_ifc_tbl_c(i).rcv_transaction_id := d.rcv_transaction_id; --接收事务处理ID
v_ap_inv_line_ifc_tbl_c(i).inventory_item_id := d.inventory_item_id; --料号ID
v_ap_inv_line_ifc_tbl_c(i).quantity_invoiced := d.invoice_quantity; --发票数量
v_ap_inv_line_ifc_tbl_c(i).unit_price := d.unit_price; --单价
v_ap_inv_line_ifc_tbl_c(i).dist_code_combination_id := d.dist_code_combination_id; --发票行账户，可为空
v_ap_inv_line_ifc_tbl_c(i).tax_classification_code := d.tax_classification_code; --税分类代码
v_ap_inv_line_ifc_tbl_c(i).reference_key1 := c.statement_header_id; --对账单 头id
v_ap_inv_line_ifc_tbl_c(i).reference_key2 := c.statement_line_id; --对账单 行id
v_ap_inv_line_ifc_tbl_c(i).reference_key3 := d.statement_details_id; --对账单 明细id
v_ap_inv_line_ifc_tbl_c(i).last_updated_by := fnd_global.USER_ID;
v_ap_inv_line_ifc_tbl_c(i).last_update_date := sysdate;
v_ap_inv_line_ifc_tbl_c(i).last_update_login := fnd_global.LOGIN_ID;
v_ap_inv_line_ifc_tbl_c(i).created_by := fnd_global.USER_ID;
v_ap_inv_line_ifc_tbl_c(i).creation_date := sysdate;
INSERTINTOap_invoice_lines_interfaceVALUESv_ap_inv_line_ifc_tbl_c(i);
(2)导入ap_invoices_interface 数据
v_ap_inv_ifc_rec ap_invoices_interface%rowtype;
C_SOURCE CONSTANT VARCHAR2(30) := 'USE'; --发票来源 ‘USE’
v_rate_type gl_daily_rates.conversion_type%type; -- 每日汇率信息
v_rate_date gl_daily_rates.conversion_date%type;
v_rate gl_daily_rates.conversion_rate%type;
v_ap_inv_ifc_rec.invoice_id := v_invoice_id; --发票ID
v_ap_inv_ifc_rec.invoice_num := c.invoice_number; --发票号
v_ap_inv_ifc_rec.invoice_type_lookup_code := xxcusp001.C_AP_INV_TYPE_C; --发票行类型
v_ap_inv_ifc_rec.source := C_SOURCE; --发票来源
v_ap_inv_ifc_rec.invoice_date := c.invoice_date; --发票日期
v_ap_inv_ifc_rec.vendor_id := c.vendor_id; --供应商id
v_ap_inv_ifc_rec.vendor_site_id := c.vendor_site_id; --供应商地点
v_ap_inv_ifc_rec.invoice_amount := v_invoice_amt; --发票总金额，可由行汇总
v_ap_inv_ifc_rec.control_amount := c.tax - v_tax_amt_c;
v_ap_inv_ifc_rec.invoice_currency_code := c.currency_code; --币种
v_ap_inv_ifc_rec.exchange_rate := v_rate;
v_ap_inv_ifc_rec.exchange_rate_type := v_rate_type;
v_ap_inv_ifc_rec.exchange_date := v_rate_date;
v_ap_inv_ifc_rec.terms_id := c.terms_id; --付款条件
v_ap_inv_ifc_rec.terms_date := c.condition_date; --条件日期
v_ap_inv_ifc_rec.description := c.remark; --备注
v_ap_inv_ifc_rec.group_id := pi_group_id; --对账单处理批次
v_ap_inv_ifc_rec.payment_method_code := c.payment_method_lookup_code;
v_ap_inv_ifc_rec.gl_date := c.gl_date; --总账日期
v_ap_inv_ifc_rec.org_id := c.org_id; --组织
v_ap_inv_ifc_rec.calc_tax_during_import_flag := 'Y';
v_ap_inv_ifc_rec.add_tax_to_inv_amt_flag := 'Y';
v_ap_inv_ifc_rec.reference_key1 := c.statement_header_id; --对账单头ID
v_ap_inv_ifc_rec.reference_key2 := c.statement_line_id; --对账单行ID
v_ap_inv_ifc_rec.last_update_date := sysdate;
v_ap_inv_ifc_rec.last_updated_by := fnd_global.USER_ID;
v_ap_inv_ifc_rec.last_update_login := fnd_global.LOGIN_ID;
v_ap_inv_ifc_rec.creation_date := sysdate;
v_ap_inv_ifc_rec.created_by := fnd_global.USER_ID;
insert into ap_invoices_interface values v_ap_inv_ifc_rec; --插入发票
2、插入AP发票接口表成功后，调用标准请求生成AP发票。
调用的请求为：应付款管理系统开放接口导入，并发执行简称为：APXIIMPT。
C_PROGRAM_SHORT_NAME := ‘APXIIMPT’;
--获取应用程式所属模块
SELECT fa.application_short_name
INTO l_applica_code
FROM fnd_application fa, fnd_concurrent_programs fcp
WHERE fa.application_id = fcp.application_id
AND fcp.concurrent_program_name = C_PROGRAM_SHORT_NAME;
2.1、调用并发程序
需要注意的是：
(1) 在Package里面调用只需要传递需要的参数个数，因为它有默认值指示结束； (2) 在form里面则不行，要写满105个，而且我们参数结束之后要用一个chr(0)来表示结束 。
l_result := fnd_request.submit_request(application => l_applica_code, --模块 program => C_PROGRAM_SHORT_NAME, --应用程式 description => '', --请求说明(可选) start_time => to_char(sysdate), --RUN 时间(可选) sub_request => FALSE, --立刻提交请求 argument1 => C_SOURCE, --参数1 --发票来源 ‘USE’ argument2 => pi_group_id, --参数2 --GROUP argument3 => 'N/A', --参数3 --Batch Name argument4 => '', --参数4 argument5 => '', --参数5
argument6 => '', --参数6 argument7 => 'Y' , --参数7 --Purge 清楚接口表中成功导入的数据 argument8 => 'N', --参数8 --Trace Switch argument9 => 'N', --参数9 --Debug Switch argument10 => 'N', --参数10 --Summarize Report
argument11 => '1000', --参数11 --commit batch size
argument12 => fnd_global.USER_ID, --USER ID
argument13 => fnd_global.LOGIN_ID --LOGIN_ID);
l_result := fnd_request.submit_request(l_applica_code,
C_PROGRAM_SHORT_NAME,
'',
to_char(sysdate),
FALSE,
C_SOURCE,
pi_group_id,
'N/A',
'',
'',
'',
'Y' , --Purge 清楚接口表中成功导入的数据
'N', --Trace Switch
'N', --Debug Switch
'N', --Summarize Report
'1000', --commit batch size
fnd_global.USER_ID, --USER ID
fnd_global.LOGIN_ID,
CHR(0),
'','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','');
2.2、判断导入是否成功
v_interval NUMBER := 5;
v_max_wait NUMBER := 0;
v_phase VARCHAR2(80);
v_status VARCHAR2(80);
v_dev_phase VARCHAR2(80);
v_dev_status VARCHAR2(80);
v_message VARCHAR2(240)
IF l_result = 0 THEN --不成功则删除接口表数据
DELETEap_invoices_interface ai WHERE ai.invoice_id = v_invoice_id;
DELETEap_invoice_lines_interface ail WHERE ail.invoice_id = v_invoice_id;
ELSE -- 成功则等待“运行发票接口导入请求”运行完成
v_ret := FND_CONCURRENT.WAIT_FOR_REQUEST(l_result ,
v_interval,
v_max_wait,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message);
IF upper(rtrim(v_dev_phase) = 'COMPLETE' THEN
po_error_message := null;
ELSE
po_error_message := '程序' || pi_program_short_name || '异常终止!';
END IF;
END IF;
2.3、检查接口表错误信息
--首先获取错误的发票
select count(1), max(i.invoice_id)
into v_cnt, v_invoice_id
from ap_invoices_interface i
where i.status = 'REJECTED'
and i.group_id = to_char(pi_group_id)
and i.reference_key1 = c.statement_header_id --可选 对账单信息
and i.reference_key2 = c.statement_line_id; --可选
--有错误则查找错误信息，分别查找发票 、 发票行 错误信息
IF v_cnt > 0 THEN
SELECT nvl(flv.meaning, a.reject_lookup_code) err_msg --发票错误信息
INTO l_err_msg
FROM ap_interface_rejections a,
ap_invoices_interface b,
fnd_lookup_values flv
WHERE a.parent_id = b.invoice_id
and a.parent_table = 'AP_INVOICES_INTERFACE'
AND flv.lookup_type(+) = 'REJECT CODE'
AND flv.lookup_code(+) = a.reject_lookup_code
AND flv.language(+) = 'ZHS' --USERENV('LANG')
AND b.invoice_id = v_invoice_id；
SELECT nvl(flv.meaning, a.reject_lookup_code) err_msg
INTO l_err_msg_line
FROM ap_interface_rejections a,
ap_invoices_interface b,
ap_invoice_lines_interface c,
fnd_lookup_values flv
WHERE a.parent_id = c.invoice_line_id
and c.invoice_id = b.invoice_id
and a.parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND flv.lookup_type(+) = 'REJECT CODE'
AND flv.lookup_code(+) = a.reject_lookup_code
AND flv.language(+) = USERENV('LANG')
AND b.invoice_id = v_invoice_id;
--最后将错误信息更新到对账单中
update xxx_po_statement_header h
set h.error_mes := l_err_msg||l_err_msg_line,
h.status := 'ERROR'
where h.statement_header_id =c.statement_header_id;
ELSE
--无错误，最后将对账单中的状态更新为已对账
update xxx_po_statement_header h
set h.error_mes := NULL,
h.status := 'invoice'
where h.statement_header_id =c.statement_header_id;
END IF;
3、其他注意事项
以上只是简单介绍过程和主要代码，注意最开始数据的检验，以及程序的防呆，程序的健壮。
2878
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
