EBS 采购订单入库

来源：互联网发布：网络实名制知乎编辑：程序博客网时间：2024/04/26 21:29

1. 采购订单入库表

1.1. 表

表名

说明

rcv_headers_interface

接收单头表

rcv_transactions_interface

接收行表、接收事务、库存事务

mtl_transaction_lots_interface

批次信息

mtl_serial_numbers_interface

序列信息

mtl_supply

包括了所有可以接收入库的记录

rcv_transaction

已经接收入库的事物处理记

rcv_shipment_headers

发运头表

rcv_shipment_lines

发运行表

po_interface_errors

错误信息表

1.2. 关联关系

rcv_headers_interface.header_interface_id :=rcv_transactions_interface.header_interface_id

rcv_transactions_interface. interface_transaction_id

= mtl_transaction_lots_interface interface_transaction_id

  rcv_transactions_interface. interface_transaction_id

= mtl_serial_numbers_interface. interface_transaction_id

rcv_transactions_interface. interface_transaction_id

= po_interface_errors. interface_transaction_id

2. 采购订单入库

2.1 脚本

（该脚本12.2.6亲测可用）

DECLARE

g_group_id NUMBER :=rcv_interface_groups_s.nextval;

g_creation_date DATE:=SYSDATE;

g_created_by NUMBER:= fnd_global.user_id;

g_last_updated_by NUMBER:= fnd_global.user_id;

g_last_update_date DATE:=SYSDATE;

g_last_update_login NUMBER:= fnd_global.login_id;

l_request_id NUMBER;

l_request_flag BOOLEAN;

l_phase VARCHAR2(30);

l_dev_status VARCHAR2(30);

l_status VARCHAR2(20);

l_dev_phase VARCHAR2(200);

l_message VARCHAR2(2000);

l_header_iface rcv_headers_interface%ROWTYPE;

l_trx_iface rcv_transactions_interface%ROWTYPE;

l_rcv_lots_interface mtl_transaction_lots_interface%ROWTYPE;

BEGIN

fnd_global.apps_initialize(user_id =>1170,--fnd_global.user_id,

resp_id =>50838,--fnd_global.resp_id,

resp_appl_id=>20004);--fnd_global.resp_appl_id);

mo_global.init('PO');

mo_global.set_policy_context('S',101);

l_header_iface.last_update_date := g_last_update_date;

l_header_iface.last_updated_by := g_last_updated_by;

l_header_iface.last_update_login := g_last_update_login;

l_header_iface.creation_date := g_creation_date;

l_header_iface.created_by := g_created_by;

l_trx_iface.last_update_date := g_last_update_date;

l_trx_iface.last_updated_by := g_last_updated_by;

l_trx_iface.creation_date := g_creation_date;

l_trx_iface.created_by := g_created_by;

l_trx_iface.last_update_login:= g_last_update_login;

l_rcv_lots_interface.last_update_date := g_last_update_date;

l_rcv_lots_interface.last_updated_by := g_last_updated_by;

l_rcv_lots_interface.creation_date := g_creation_date;

l_rcv_lots_interface.created_by := g_created_by;

l_rcv_lots_interface.last_update_login := g_last_update_login;

--FOR c_header IN cur_header LOOP

SELECT po.rcv_headers_interface_s.nextval

INTO l_header_iface.header_interface_id

FROM dual;

l_header_iface.processing_status_code:='PENDING';

l_header_iface.receipt_source_code :='VENDOR';

l_header_iface.transaction_type :='NEW';

l_header_iface.auto_transact_code :='RECEIVE';

l_header_iface.receipt_num :=NULL;--接收编号,可自动生成

l_header_iface.shipment_num :='10120170424004';--发运编号

l_header_iface.vendor_id := 1;--供应商

l_header_iface.vendor_site_id :=5001; -- 供应商地点

l_header_iface.org_id:=101; --业务实体

l_header_iface.comments :='菱商发运采购订单入库('||10120170424004||')';--备注，会写到发运行

l_header_iface.expected_receipt_date:=SYSDATE; --接收日期

l_header_iface.validation_flag :='Y';

l_header_iface.group_id:= g_group_id;--组编号

l_header_iface.ship_to_organization_id :=123;

/* SELECT l.ship_to_organization_id

INTO l_header_iface.ship_to_organization_id

FROM po_line_locations_all l

WHERE l.po_header_id = c_header.po_header_id

AND rownum = 1;*/

INSERTINTO rcv_headers_interfaceVALUES l_header_iface;

-- FOR c_line IN cur_line(c_header.po_num) LOOP

SELECT rcv_transactions_interface_s.nextval

INTO l_trx_iface.interface_transaction_id

FROM dual;

--直接入库

l_trx_iface.transaction_type :='RECEIVE';

l_trx_iface.auto_transact_code :='DELIVER';

l_trx_iface.destination_type_code :='INVENTORY';

l_trx_iface.processing_status_code :='PENDING';

l_trx_iface.processing_mode_code :='BATCH';

l_trx_iface.transaction_status_code:='PENDING';

l_trx_iface.receipt_source_code:='VENDOR';

l_trx_iface.validation_flag :='Y';

l_trx_iface.header_interface_id:= l_header_iface.header_interface_id;

l_trx_iface.vendor_id :=1;--供应商

l_trx_iface.vendor_site_id :=5001;--供应商地点

l_trx_iface.source_document_code :='PO';--来源

l_trx_iface.po_header_id :=4105;--采购订单头表ID

l_trx_iface.po_line_id :=6105;--采购订单行表ID

l_trx_iface.po_line_location_id :=5124;--定位表ID

l_trx_iface.po_distribution_id :=5078;--分配表ID

l_trx_iface.po_release_id :=NULL;--

l_trx_iface.category_id:=5125;--物料类型

l_trx_iface.item_id:=7001; --物料ID

l_trx_iface.item_description:= '低值医疗耗材测试';--物料描述

l_trx_iface.group_id := g_group_id; --组编号

l_trx_iface.transaction_date :=SYSDATE; --接收日期

l_trx_iface.expected_receipt_date :=SYSDATE;--需要日期

l_trx_iface.unit_of_measure:='盒';--单位（注意不是code）

l_trx_iface.use_mtl_lot:=2;--物料如果按批次，必须赋值2

l_trx_iface.quantity:= 0; --数量

l_trx_iface.primary_quantity:= 0;--主数量

l_trx_iface.to_organization_id:=123; -- 接收组织ID

l_trx_iface.ship_to_location_id := 142;--接收地址ID

l_trx_iface.interface_source_code:='RCV';--来源

l_trx_iface.comments:='PO菱商发运接收'; --会写到发运行表

l_trx_iface.subinventory:='JIH_test01';--子库存

l_trx_iface.inspection_status_code:='NOT INSPECTED'; --不检查

--批

-- FOR c_lot IN cur_lot(c_header.po_num,c_line.po_line_num) LOOP

SELECT mtl_material_transactions_s.nextval

INTO l_rcv_lots_interface.transaction_interface_id

FROM dual;

l_rcv_lots_interface.product_code :='RCV';

l_rcv_lots_interface.product_transaction_id:= l_trx_iface.interface_transaction_id;

l_rcv_lots_interface.lot_number :='2017042405';--批次号

l_rcv_lots_interface.transaction_quantity :=100; --数量

l_rcv_lots_interface.primary_quantity :=100; --主数量

l_trx_iface.quantity:= 100;

l_trx_iface.primary_quantity:= 100;

INSERTINTO mtl_transaction_lots_interfaceVALUES l_rcv_lots_interface;

-- END LOOP;

INSERTINTO rcv_transactions_interfaceVALUES l_trx_iface;

-- END LOOP;

--END LOOP;

dbms_output.put_line('g_group_id :='||g_group_id);

l_request_id := fnd_request.submit_request(application=>'PO',

program =>'RVCTP',

sub_request =>FALSE,

argument1 =>'BATCH',

argument2 => g_group_id);

IF l_request_id>0THEN

dbms_output.put_line('l_request_id :='||l_request_id);

COMMIT;

l_request_flag := fnd_concurrent.wait_for_request(request_id=> l_request_id,

INTERVAL =>5,

phase => l_phase,

status => l_status,

dev_phase => l_dev_phase,

dev_status=> l_dev_status,

message => l_message);

IF l_request_flagTHEN

IF l_dev_status='NORMAL'THEN

--对于未成功的，回写错误消息

FOR error_recIN(SELECT poe.error_message,

rti.po_header_id,

rti.po_line_id

FROM po_interface_errors poe,

rcv_transactions_interface rti

WHERE poe.interface_line_id= rti.interface_transaction_id

AND rti.group_id= g_group_id)

LOOP

dbms_output.put_line('错误消息 :='|| error_rec.error_message);

ENDLOOP;

ELSE

dbms_output.put_line('Exceptions happen during execution of the request(request id:'|| l_request_id ||').');

ENDIF;

ELSE

dbms_output.put_line('The request has not been finished yet and the result is unknown.');

ENDIF;

ELSE

dbms_output.put_line('Fail to submit request to process data in interface.');

ENDIF;

END;

脚本运行结果：

2.2 入库注意事项

2.2.1 入库方式

Oracle提供了三种可选的入库方式(在PO订单分配行界面维护)：

1．直接入库：接收后自动入库；

2．标准入库：接收后，进行入库动作，才完成最终入库；

3．要求检验的入库：接收后，进行检验，打印检验通过标记后，才能进行入库动作。

对于这三种接收入库，接口表通过不同的控制字段来完成期望的接收入库动作：

a) 对于直接入库的接收，插接收事务处理的接口行表(rcv_transactions_interface)时的几个关键的状态字段：

rcv_transactions_interface.transaction_type := 'RECEIVE';

rcv_transactions_interface.auto_transact_code := 'DELIVER';

rcv_transactions_interface.destination_type_code := 'INVENTORY';

b) 对于标准以及需检验的入库的接收，插接收事务处理的接口行表(rcv_transactions_interface)时的几个关键的状态字段：

rcv_transactions_interface.transaction_type := 'RECEIVE';

rcv_transactions_interface.auto_transact_code := NULL;

rcv_transactions_interface.destination_type_code := ' RECEIVING ';

c) 对于接收甚至检验后的数据做入库时，插接收事务处理的接口行表(rcv_transactions_interface)时的几个关键的状态字段：

rcv_transactions_interface.transaction_type := 'DELIVER';

rcv_transactions_interface.auto_transact_code := NULL;

rcv_transactions_interface.destination_type_code := 'INVENTORY';

2.2.2 序列

上脚本根据开发需要只到批次号，到序列脚本入下

rcv_transactions_interface.use_mtl_serial:=2; --如果有序列号，必须为2

mtl_serial_numbers_interface.last_update_date := g_last_update_date;

mtl_serial_numbers_interface.last_updated_by := g_last_updated_by;

mtl_serial_numbers_interface.creation_date := g_creation_date;

mtl_serial_numbers_interface.created_by := g_last_updated_by;

mtl_serial_numbers_interface.last_update_login := g_last_update_login;

mtl_serial_numbers_interface.product_code :='RCV';

mtl_serial_numbers_interface.fm_serial_number :=serial_number;

mtl_serial_numbers_interface.to_serial_number :=serial_number;

mtl_serial_numbers_interface.process_flag :=1;

mtl_serial_numbers_interface.product_transaction_id:= l_iface_rcv_rec.interface_transaction_id;

SELECT mtl_material_transactions_s.nextval

INTO mtl_serial_numbers_interface.transaction_interface_id

FROM dual;

INSERTINTO mtl_serial_numbers_interfaceVALUES mtl_serial_numbers_interface;

2.2.3 数量

要注意数量的，如果有批次号，行接口表的主数量必定要是序列行主数量的总和，数量必须要是批次行的数据综合，否则会报错：

主数量和数量之间的区别：对于一个物料A，如果他的主要单位是‘个’（mtl_system_items_b表中primary_unit_of_measure），现在发运了10盒，1盒相当于12个(单位的换算关系请见mtl_uom_class_conversions)，那么数量是10，主数量是120。

1 0