利用Receiving Open Interface批量导入Oracle EBS接收表单实例

来源：互联网发布：广日电梯知乎编辑：程序博客网时间：2024/05/16 05:49

准备：

1. 创建采购订单。请确认：

a) 订单创建结束后，其审批状态应为‘Approved’；

b) 请填写Shipments中的Distribution，确认其中的Subinventory已经正确填写。

2. 创建csv样例数据文件

a) imp_header.csv

process id,po number

4,6484

4,6485

b) imp_trans.csv

process id,quantity,po number,line number

4,4,6484,1

4,3,6484,2

4,2,6484,3

4,1,6485,1

3. 创建sql Loader control文件

a) imp_header.ctl

options (skip=1)

Load data

CHARACTERSET UTF8

infile 'imp_header.csv'

append

into table XHU_IMP_REC_header_2205

fields terminated by "," optionally enclosed by '"'

(

PROCESS_ID,

PO_NUM

)

b) imp_trans.ctl

options (skip=1)

Load data

CHARACTERSET UTF8

infile 'imp_trans.csv'

append

into table XHU_IMP_REC_trans_2205

fields terminated by "," optionally enclosed by '"'

(

PROCESS_ID,

QUANTITY,

PO_NUM,

LINE_NUM

)

4. 创建临时表

a) XHU_IMP_REC_header_2205

-- Create table

create table XHU.XHU_IMP_REC_HEADER_2205

(

PO_NUM VARCHAR2(20),

PROCESS_ID VARCHAR2(20)

)

tablespace APPS_TS_TX_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 128K

next 128K

minextents 1

maxextents unlimited

pctincrease 0

);

b) XHU_IMP_REC_trans_2205

-- Create table

create table XHU.XHU_IMP_REC_TRANS_2205

(

PROCESS_ID NUMBER,

QUANTITY NUMBER,

PO_NUM VARCHAR2(20),

LINE_NUM VARCHAR2(20)

)

tablespace APPS_TS_TX_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 128K

next 128K

minextents 1

maxextents unlimited

pctincrease 0

);

5. 登陆Oracle EBS 数据库文件系统，上传之前创建的csv和ctl文件

6. 运行sql Loader，将数据从csv文件导入临时刚刚创建的表中

a) sqlldr apps/apps control=imp_header.ctl

b) sqlldr apps/apps control=imp_trans.ctl

7. 创建package XHU_RCV_IMP_2205_PKG，用以将数据从临时表中导入接口表

CREATE OR REPLACE PACKAGE XHU_RCV_IMP_2205_PKG IS

--AUTHOR: YUNFEI.MA

-- DATE: 2009-9-23

--PURPOSE:IMPORT BRAND NEW PO DATA

PROCEDURE IMP_ORIGIN_RCV(errbuf OUT VARCHAR2,

retcode OUT VARCHAR2,

PROCESS_ID IN VARCHAR2);

END XHU_RCV_IMP_2205_PKG;

create or replace package body xhu_rcv_imp_2205_pkg is

procedure imp_origin_rcv(errbuf out varchar2,

retcode out varchar2,

process_id varchar2) is

cursor m_ret(PO_NUM in VARCHAR2) is(

SELECT poh.po_header_id,

pol.po_line_id,

pol.line_num,

pll.line_location_id,

pd.po_distribution_id,

poh.vendor_id,

poh.vendor_site_id,

pll.ship_to_location_id,

pll.ship_to_organization_id,

msi.inventory_item_id,

msi.segment1,

msi.primary_uom_code,

msi.primary_unit_of_measure

FROM po_headers_all poh,

po_lines_all pol,

po_line_locations_all pll,

po_distributions_all pd,

po_vendors pv,

mtl_system_items_b msi

WHERE poh.po_header_id = pol.po_header_id

AND poh.vendor_id = pv.vendor_id

AND pol.po_line_id = pll.po_line_id

AND pol.po_header_id = pll.po_header_id

AND pll.po_header_id = pd.po_header_id

AND pll.po_line_id = pd.po_line_id

AND pll.line_location_id = pd.line_location_id

AND pol.item_id = msi.inventory_item_id

AND pll.ship_to_organization_id = msi.Organization_Id

AND poh.type_lookup_code = 'STANDARD'

AND poh.segment1 = po_num);

HAS_HEADER NUMBER := 0;

quantity number := 0;

g_id NUMBER := rcv_interface_groups_s.NEXTVAL;

begin

for c_ret in (SELECT *

FROM XHU_IMP_REC_HEADER_2205 t

where t.process_id = process_id) loop

HAS_HEADER := 0; --未插入header

FOR M_IDS IN M_RET(replace(c_ret.po_num,chr(13),'')) LOOP

--获取接收数量

select t.quantity

into quantity

from xhu_imp_rec_trans_2205 t

where replace(t.po_num,chr(13),'') = replace(c_ret.po_num,chr(13),'')

and replace(t.line_num,chr(13),'') =replace(m_ids.line_num,chr(13),'')

and replace(t.process_id,chr(13),'') = process_id;

IF (HAS_HEADER = 0) THEN

insert into RCV_HEADERS_INTERFACE

(HEADER_INTERFACE_ID,

GROUP_ID,

PROCESSING_STATUS_CODE,

RECEIPT_SOURCE_CODE,

TRANSACTION_TYPE,

VENDOR_ID,

VENDOR_SITE_ID,

last_update_date,

last_updated_by,

creation_date,

created_by,

last_update_login)

values

(RCV_HEADERS_INTERFACE_S.NEXTVAL,

g_id,

'PENDING',

'VENDOR',

'NEW',

m_ids.vendor_id,

m_ids.vendor_site_id,

SYSDATE, --LAST_UPDATE_DATE

fnd_global.user_id, --LAST_UPDATE_BY

SYSDATE, --CREATION_DATE

fnd_global.user_id, --CREATED_BY

fnd_global.LOGIN_ID --LAST_UPDATE_LOGIN

);

HAS_HEADER := 1;

END IF;

INSERT INTO rcv_transactions_interface

(interface_transaction_id,

group_id,

last_update_date,

last_updated_by,

creation_date,

created_by,

last_update_login,

transaction_type,

transaction_date,

processing_status_code,

processing_mode_code,

transaction_status_code,

auto_transact_code,

quantity,

unit_of_measure,

item_id,

receipt_source_code,

vendor_id,

to_organization_id,

locator_id,

source_document_code,

po_header_id,

po_line_id,

po_line_location_id,

po_distribution_id,

destination_type_code,

deliver_to_person_id,

location_id,

deliver_to_location_id,

validation_flag,

header_interface_id)

VALUES

(rcv_transactions_interface_s.NEXTVAL, --INTERFACE_TRANSACTION_ID

g_id, --GROUP_ID

SYSDATE, --LAST_UPDATE_DATE

fnd_global.user_id, --LAST_UPDATE_BY

SYSDATE, --CREATION_DATE

fnd_global.user_id, --CREATED_BY

fnd_global.LOGIN_ID, --LAST_UPDATE_LOGIN

'RECEIVE', --TRANSACTION_TYPE

SYSDATE, --TRANSACTION_DATE

'PENDING', --PROCESSING_STATUS_CODE

'BATCH', --PROCESSING_MODE_CODE

'PENDING', --TRANSACTION_STATUS_CODE

'DELIVER', -- AUTO_TRANSACT_CODE

quantity, --QUANTITY

'Each', --c_ret.primary_unit_of_measure, --UNIT_OF_MEASURE

m_ids.inventory_item_id, --p_item_id, --ITEM_ID

'VENDOR', --RECEIPT_SOURCE_CODE

m_ids.vendor_id, --c_ret.vendor_id, --VENDOR_ID

m_ids.ship_to_organization_id, -- c_ret.ship_to_organization_id, --TO_ORGANIZATION_ID 122

NULL, --LOCATOR_ID

'PO', --SOURCE_DOCUMENT_CODE

m_ids.po_header_id, --c_ret.po_header_id, --PO_HEADER_ID

m_ids.po_line_id, --c_ret.po_line_id, --PO_LINE_ID

m_ids.line_location_id, --c_ret.line_location_id, --PO_LINE_LOCATION_ID

m_ids.po_distribution_id, --c_ret.po_distribution_id, --PO_DISTRIBUTION_ID

'INVENTORY', --DESTINATION_TYPE_CODE

NULL, --DELIVER_TO_PERSON_ID

NULL, --LOCATION_ID

NULL, --DELIVER_TO_LOCATION_ID

'Y', --Validation_flag

RCV_HEADERS_INTERFACE_S.Currval);

END LOOP; --TRANS LOOP

end loop; --BIG LOOP

COMMIT;

end imp_origin_rcv;

end xhu_rcv_imp_2205_pkg;

8. 在ebs中注册该包的并发程序，

application developer->Concurrent->Executable

并为其添加参数Process ID

application developer->Concurrent->Program->Parameters

具体过程不赘述，需要请留言，我再写详细些。

9. 将定义的Program赋给自己职责的Request Group

System administrator->responsibility->request

10. 在该指责下运行刚定义的并发请求，该并发请求将把数据从临时表中插入到接口表。

至此，系统接口表中已经保存了我们即将导入的接受单的所有必要信息，请检查数据是否正确插入。

11. 在 Purchasing，Vision Operations职责下，运行并发请求Receiving Transaction Processor，参数可不填。

12. 当该并发请求运行完毕，接收单应正确建立。请到EBS中查看一开始创建的订单，其接收数量是否正确。

需要注意的地方：

1. 出现任何导入异常，请首先在Purchasing，Vision Operations->Receiving->Transaction Status Summory中查找待导入的订单，应该可以看到错误信息。大部分错误是导入数据不合法造成的，如果提示某数据为必填，请在XHU_RCV_IMP_2205_PKG中为添加该列信息。

2. 在做步骤10的时候会要求填一个Proces ID的参数，请将该值设置为待导入的csv文件中Process ID的值。每次运行这个并发请求时，都应该使用不同于先前的Process ID。