Oracle EBS Interface/API(1)--AP Invoice费用报表类型导入

来源：互联网发布：淘宝yc u盘编辑：程序博客网时间：2024/05/05 20:59

本文章讲述如何通过接口导入费用报表类型的AP Invoice实例。

前提条件:

1.定义员工供应商，该供应商的地点名称为办公室
2.定义费用报表模板：AP/Setup/Invoice/Expense Report Templates

快速参考:

参考点内容功能导航N：AP/Invoices/Entry/Expense Reports并发请求Expense Report Import(导入费用报表)接口表

AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL

API无错误信息表无接口FormN：AP/Invoices/Entry/Expense Reports接口Report

开发参考:

接口表信息

接口表数据表说明AP_EXPENSE_REPORT_HEADERS_ALLAP_INVOICES_ALL AP_EXPENSE_REPORT_LINES_ALLAP_INVOICE_DISTRIBUTIONS_ALL

Table Relation:

Select *  From AP_EXPENSE_REPORT_HEADERS_ALL AERH, AP_EXPENSE_REPORT_LINES_ALL AERL Where AERH.REPORT_HEADER_ID = AERL.REPORT_HEADER_ID --二个接口表联连条件

并发请求
N：View/Requests/Expense Report Import
ParametersDescriptionBatch Name批名称Summarize ReportYes/No本例子参数为NoSource来源:Payables Expense ReportsGL Date空Purge Date空Transfer Descriptive Flexfield是否传输入说明弹性域字段值
Interface Form: AP/Invoices/Entry/Expense Reports

关键字段(参数)+最简单例子

关键字段
AP_EXPENSE_REPORT_HEADERS_ALL
FieldDescriptionREPORT_HEADER_ID题头IDEMPLOYEE_ID员工标识IDWEEK_END_DATE日期VOUCHNO　TOTAL金额VENDOR_ID员工供应商标识IDVENDOR_SITE_ID员工供应商地点标识EXPENSE_CHECK_ADDRESS_FLAG发送地点标识INVOICE_NUM发票编号EXPENSE_REPORT_ID费用模板标识IDSET_OF_BOOKS_ID帐本标识IDSOURCE来源说明EMPLOYEE_CCID默认GL帐户标识IDDESCRIPTION发票说明ATTRIBUTE_CATEGORY　ATTRIBUTE1　ATTRIBUTE2　ATTRIBUTE3　ATTRIBUTE4　ATTRIBUTE5　ATTRIBUTE6　ATTRIBUTE7　ATTRIBUTE8　ATTRIBUTE9　ATTRIBUTE10　ATTRIBUTE11　ATTRIBUTE12　ATTRIBUTE13　ATTRIBUTE14　ATTRIBUTE15　DEFAULT_CURRENCY_CODE默认币种DEFAULT_EXCHANGE_RATE_TYPE默认汇率类型DEFAULT_EXCHANGE_RATE默认汇率DEFAULT_EXCHANGE_DATE默认汇率日期VOUCHER_NUM凭证单据ORG_ID组织标识PAYMENT_CROSS_RATE_TYPE付款汇率类型PAYMENT_CROSS_RATE_DATE付款汇率日期PAYMENT_CROSS_RATE付款汇率PAYMENT_CURRENCY_CODE付款币种

AP_EXPENSE_REPORT_LINES_ALL

FieldDescriptionREPORT_HEADER_ID题头IDCODE_COMBINATION_ID帐户IDITEM_DESCRIPTION行说明SET_OF_BOOKS_ID帐薄标识IDAMOUNT金额ATTRIBUTE_CATEGORY　ATTRIBUTE1　ATTRIBUTE2　ATTRIBUTE3　ATTRIBUTE4　ATTRIBUTE5　ATTRIBUTE6　ATTRIBUTE7　ATTRIBUTE8　ATTRIBUTE9　ATTRIBUTE10　ATTRIBUTE11　ATTRIBUTE12　ATTRIBUTE13　ATTRIBUTE14　ATTRIBUTE15　CURRENCY_CODE币种VAT_CODE税率LINE_TYPE_LOOKUP_CODE行类型(项目/税)DISTRIBUTION_LINE_NUMBER分配行ORG_IDOU IDSTART_EXPENSE_DATEGL 日期END_EXPENSE_DATE　RECEIPT_CURRENCY_CODE　RECEIPT_CONVERSION_RATE　DAILY_AMOUNT　RECEIPT_CURRENCY_AMOUNT　WEB_PARAMETER_ID　AMOUNT_INCLUDES_TAX_FLAG金额是否含税

Open Inerface最简单例子（AP Invoices 费用报表）(11.5.10.2)

Declare  l_iface_h           AP_EXPENSE_REPORT_HEADERS_ALL%Rowtype;  l_iface_l           AP_EXPENSE_REPORT_LINES_ALL%Rowtype;  v_SetOfBook         := 1001; --帐薄ID  v_OrgId             := 114; --OU ID  v_Source            := 'XpenseXpress'; --来源代码  v_EmployeeCCID      := 2339; --  v_CurrencyCode      := 'CNY'; --币种  v_UserId            Number := 0; --用户ID  v_ExpenseReportID   Number; --模板ID  v_ChartOfAccountsID := 58802;Begin  --获得费用报表板模ID  Begin    Select APER.EXPENSE_REPORT_ID      Into v_ExpenseReportID      From AP_EXPENSE_REPORTS_ALL APER     Where APER.ORG_ID = P_ORG_ID       And APER.REPORT_TYPE = 'CUX_EXPENSE';  Exception    When No_Data_Found Then      Null;  End;  v_ExpenseReportID := Nvl(v_ExpenseReportID, 0);  --题头  l_iface_h.creation_date     := Sysdate;  l_iface_h.created_by        := v_UserId;  l_iface_h.last_update_date  := Sysdate;  l_iface_h.last_updated_by   := v_UserId;  l_iface_h.Last_Update_Login := -1;  --行  l_iface_l.creation_date     := Sysdate;  l_iface_l.created_by        := v_UserId;  l_iface_l.last_update_date  := Sysdate;  l_iface_l.last_updated_by   := v_UserId;  l_iface_l.Last_Update_Login := -1;  --题头序   Select AP_EXPENSE_REPORT_HEADERS_S.NEXTVAL    Into l_iface_h.REPORT_HEADER_ID    From Dual;  l_iface_h.EMPLOYEE_ID                := 94;  l_iface_h.WEEK_END_DATE              := Trunc(Sysdate); --发票日期/GL日期  l_iface_h.VOUCHNO                    := 0;  l_iface_h.TOTAL                      := 541; --发票金额  l_iface_h.EXPENSE_CHECK_ADDRESS_FLAG := '0';  l_iface_h.INVOICE_NUM                := '9530'; --发票编号  l_iface_h.EXPENSE_REPORT_ID          := v_ExpenseReportID;  l_iface_h.SET_OF_BOOKS_ID            := v_SetOfBook;  l_iface_h.SOURCE                     := v_Source;  l_iface_h.APPLY_ADVANCES_DEFAULT     := 'N'; --  l_iface_h.EMPLOYEE_CCID              := v_EmployeeCCID; --默认GL帐户标识ID  l_iface_h.ATTRIBUTE1                 := 'DD';  l_iface_h.ATTRIBUTE2                 := 'DD';  l_iface_h.DEFAULT_CURRENCY_CODE      := v_CurrencyCode;  l_iface_h.ORG_ID                     := v_OrgId;  l_iface_h.PAYMENT_CROSS_RATE_DATE    := Sysdate;  l_iface_h.PAYMENT_CROSS_RATE         := 0;  l_iface_h.PAYMENT_CURRENCY_CODE      := v_CurrencyCode;  l_iface_h.DESCRIPTION                := '测试费用报表报销费用';  Insert Into AP_EXPENSE_REPORT_HEADERS_ALL Values l_iface_h;  --行  l_iface_l.REPORT_HEADER_ID         := l_iface_h.REPORT_HEADER_ID;  l_iface_l.CODE_COMBINATION_ID      := 3019; --GL帐户标识ID  l_iface_l.ITEM_DESCRIPTION         := '报销费用XXXXXXXXXXXXX';  l_iface_l.SET_OF_BOOKS_ID          := v_SetOfBook;  l_iface_l.AMOUNT                   := 541; --分配行金额  l_iface_l.CURRENCY_CODE            := v_CurrencyCode;  l_iface_l.LINE_TYPE_LOOKUP_CODE    := 'ITEM'; --分配类型:ITEM为项目,TAX为税  l_iface_l.DISTRIBUTION_LINE_NUMBER := 1;  l_iface_l.ORG_ID                   := v_OrgId;  l_iface_l.START_EXPENSE_DATE       := Sysdate; --GL Date  l_iface_l.END_EXPENSE_DATE         := Null;  l_iface_l.RECEIPT_CURRENCY_CODE    := v_CurrencyCode;  l_iface_l.RECEIPT_CONVERSION_RATE  := 0;  l_iface_l.DAILY_AMOUNT             := 541;  l_iface_l.RECEIPT_CURRENCY_AMOUNT  := 541;  l_iface_l.WEB_PARAMETER_ID         := 0;  l_iface_l.TAX_CODE_OVERRIDE_FLAG   := 'N';  l_iface_l.AMOUNT_INCLUDES_TAX_FLAG := 'Y'; --发票行金额是否:Y为含税/N为不含税  l_iface_l.Vat_Code                 := 'VAT17';  l_iface_l.ATTRIBUTE1               := 'DDAF';  Insert Into AP_EXPENSE_REPORT_LINES_ALL Values l_iface_l;  --调用并发请求:Expense Report Import()  fnd_global.apps_initialize(P_USER_ID, 50255, 700, 0); --初始化环境  v_request_id := FND_request.SUBMIT_request('SQLAP',                                             'APXXTR',                                             '',                                             '',                                             false,                                             'A', ----参数1                                             v_ChartOfAccountsID,                                             v_UserId,                                             'EXP130110', --产生AP Invoice 批的名称                                                     v_SetOfBook,                                             'N', --CHR(0),                                             'XpenseXpress', --'',                                             '',                                             58,                                             '',                                             '',                                             'Y',                                             'N');  Commit;End;

插入费用报表接口表以后，也可以手工提交请求:

0 0