APIs and Open Interface--非工单领、发料（含调拨）

sen_shan

于 2023-03-01 13:51:54 发布

阅读量314

点赞数

分类专栏： MTL EBS 文章标签： oracle

本文链接：https://blog.csdn.net/sen_shan/article/details/129279847

版权

EBS 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

MTL

1 篇文章 0 订阅

订阅专栏

表名

MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE

序列
1. MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
APIs

INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS

案例
1. 杂发/杂收（代码）

Declare

v_user_id number := fnd_global.user_id;

v_login_id number := fnd_global.login_id;

v_request_id number := fnd_profile.value('CONC_REQUEST_ID');

Begin

--取transaction_interface_id

v_transaction_interface_id := null;

begin

select mtl_material_transactions_s.nextval

into v_transaction_interface_id

from dual;

exception

when others then v_transaction_interface_id := null;

end;

--处理lot no

if v_lot_no is not null then

begin

insert into mtl_transaction_lots_interface

(transaction_interface_id

,lot_number

,transaction_quantity

,primary_quantity

,last_update_date

,last_updated_by

,creation_date

,created_by

,last_update_login

)

values

(v_transaction_interface_id -- transaction_interface_id

,v_lot_no -- lot_number

,v_transaction_quantity -- transaction_quantity

,v_transaction_quantity -- primary_quantity

,sysdate -- last_update_date

,v_user_id -- last_updated_by

,sysdate -- creation_date

,v_user_id -- created_by

,v_login_id -- last_update_login

);

exception

when others then

v_err_msg :=v_err_msg||' 新增LOT No交易时错误：'||SQLERRM;

end;

end if;

begin

insert into mtl_transactions_interface(

process_flag

,organization_id

,inventory_item_id

,transaction_quantity

,transaction_type_id

,reason_id

,transaction_reference

,transfer_subinventory

,transfer_locator

,transfer_organization

,subinventory_code

,locator_id

,transaction_date

,transaction_uom

,transaction_interface_id

,source_code

,source_header_id

,source_line_id

,transaction_header_id

,validation_required

,lock_flag

,transaction_mode

,last_update_date

,last_updated_by

,creation_date

,created_by

,last_update_login

,request_id

,distribution_account_id)

values(

1 -- process_flag：'1' for ready, '2' for not ready，3：fails

,v_organization_id -- organization_id

,v_inventory_item_id -- inventory_item_id

,v_transaction_quantity -- transaction_quantity

,v_transaction_type_id -- transaction_type_id 杂收

,v_reason_id -- reason_id

,v_request_number -- transaction_reference

,NULL -- transfer_subinventory v_from_subinventory_code

,NULL -- transfer_locator_id \*目的储位 *\

,v_organization_id -- transfer_organization

,v_from_subinventory_code -- subinventory_code

,v_from_locator_id -- locator_id from

,sysdate -- transaction_date

,v_primary_uom_code -- transaction_uom

,v_transaction_interface_id -- transaction_interface_id

,'SIVP325' -- source_code

,v_header_id -- source_header_id

,v_line_id -- source_line_id

,v_transaction_interface_id -- transaction_header_id

,1 -- validation_required

,2 -- lock_flag ('1' for locked, '2' or NULL for not locked

,3 -- transaction_mode 3-background 2-immediate

,sysdate -- last_update_date

,v_user_id -- last_updated_by

,sysdate -- creation_date

,v_user_id -- created_by

,v_login_id -- last_update_login

,v_request_id

,v_account_id);

exception

when others then

v_err_msg :=v_err_msg||' 新增 ERP 杂项出库时发生错误:'||SQLERRM;

v_error_str :=v_error_str||v_err_msg;

end;

if sql%found then

COMMIT;

do_import(v_transaction_interface_id,v_err_code,v_err_msg);

if v_err_code = 'E' then -- import 失败

rollback;

v_err_msg :=v_err_msg||' 自动杂项出库import失败:'

v_error_str :=v_error_str||v_err_msg;

else

Null;

--根据实际情况，处理实际业务

end if;

end if; --sql%found

-- ELSE

End;

PROCEDURE DO_IMPORT(P_TRANSACTION_INTERFACE_ID IN NUMBER

,X_RET_CODE OUT NOCOPY VARCHAR2

,X_ERR_MSG OUT VARCHAR2) IS

x_return_status varchar2(1);

x_msg_count number;

x_msg_data varchar2(4000);

x_trans_count number;

l_retcode number;

--l_return_message varchar2(32767);

cursor c_mti is

select mti.error_code||'-'||mti.error_explanation error_message

from mtl_transactions_interface mti

WHERE mti.transaction_header_id = P_TRANSACTION_INTERFACE_ID;

BEGIN

l_retcode := inv_txn_manager_pub.process_transactions

(p_api_version => 1.0,

p_commit => 'F',

p_init_msg_list => 'T',

p_validation_level => 100,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_trans_count => x_trans_count,

p_header_id => P_TRANSACTION_INTERFACE_ID);

if l_retcode = -1 or x_return_status <> 'S' then

for r_mti in c_mti loop

if X_ERR_MSG is null then

X_ERR_MSG := r_mti.error_message;

else

X_ERR_MSG := X_ERR_MSG||'-'||r_mti.error_message;

end if;

end loop;

rollback;

x_ret_code := 'E';

else

x_ret_code := 'S';

end if;

END DO_IMPORT;

1. 详细说明
步骤：先把资料导入到Interface，然后调用INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS
v_transaction_interface_id：根据序列进行获取mtl_material_transactions_s.nextval
v_transaction_quantity ：杂发数量为负数，杂收为正数。transaction_quantity/ primary_quantity是否一致，根据实际情况定义
v_transaction_type_id要存在MTL_TRANSACTION_TYPES中
v_reason_id 要存在MTL_TRANSACTION_REASONS中
v_request_number：记录单据号码
source_code：记录源代码，
v_header_id：记录源表头代码
v_line_id：记录源表身代码
v_account_id：会计科目
1. 出库部分，建议在Interface前先检查库存数量是否满足。（该程序资料已检查，所以在此部分未做处理）
2. 调拨transfer_subinventory、transfer_locator_id需要进行赋值，该值为调拨至的值，v_transaction_quantity为正数。

手工转Interface功能与DO_IMPORT相同

You can use the following steps:
   a. (N) Inventory > Transactions > Transaction Open Interface
   b. Query the stuck records
   c. No record should get queried up.