找了好多资料,终于找到写到procedure方法代替form界面物料平均成本更新操作,核心就是往mtl_transactions_interface跟mtl_txn_cost_det_interface 插值,然后调用inv_txn_manager_pub.process_transactions API;代码备注地方改成自己公司需要的值即可
DECLARE
l_itfs_rec mtl_transactions_interface%ROWTYPE;
l_cst_itfs_rec mtl_txn_cost_det_interface%ROWTYPE;
p_txn_header_id NUMBER := mtl_material_transactions_s.nextval;
l_txn_header_id NUMBER;
l_api_stus VARCHAR2(10);
l_api_msg_cnt NUMBER;
l_api_msg_data VARCHAR2(240);
l_api_trans_cnt NUMBER;
l_api_flag NUMBER;
BEGIN
l_itfs_rec.transaction_header_id := p_txn_header_id;
SELECT mtl_material_transactions_s.nextval
INTO l_itfs_rec.transaction_interface_id
FROM dual;
l_itfs_rec.source_code := 'AvgCostUpdate';
l_itfs_rec.source_line_id := 1;
l_itfs_rec.source_header_id := 1;
l_itfs_rec.process_flag := 1;
l_itfs_rec.transaction_mode := 3;
l_itfs_rec.validation_required := 1;
l_itfs_rec.transaction_type_id := 80;--需要修改--
l_itfs_rec.transaction_source_type_id := 13;--需要修改--
l_itfs_rec.organization_id := 83; --需要修改--
l_itfs_rec.cost_type_id := 2; --Average--
-- l_itfs_rec.org_cost_group_id := 2000; -----需要修改----
l_itfs_rec.cost_group_id := 2000; -----需要修改----
l_itfs_rec.transaction_date := sysdate;
l_itfs_rec.inventory_item_id := 11529; ----需要修改----
l_itfs_rec.transaction_quantity := 0;
l_itfs_rec.transaction_uom := '件'; ----需要修改-----
l_itfs_rec.new_average_cost := '';
l_itfs_rec.percentage_change := '';
l_itfs_rec.value_change := 100; --需要修改--
l_itfs_rec.material_account := 20054;--需要修改--
l_itfs_rec.material_overhead_account := 20054;--需要修改--
l_itfs_rec.resource_account := 20054;--需要修改--
l_itfs_rec.outside_processing_account := 20054;--需要修改--
l_itfs_rec.overhead_account := 20054;--需要修改--
l_itfs_rec.reason_id := '';
l_itfs_rec.transaction_reference := 'bbbbbb';--需要修改--
l_itfs_rec.attribute4 := '100060010003';--需要修改--
l_itfs_rec.attribute6 := '10001'; --需要修改--
l_itfs_rec.created_by := -1;
l_itfs_rec.creation_date := SYSDATE;
l_itfs_rec.last_updated_by := -1;
l_itfs_rec.last_update_date := SYSDATE;
l_itfs_rec.last_update_login := -1;
l_itfs_rec.request_id := -1;
l_itfs_rec.program_id := 36119;
l_itfs_rec.program_application_id := 702;
l_itfs_rec.program_update_date := SYSDATE;
INSERT INTO mtl_transactions_interface VALUES l_itfs_rec;
l_cst_itfs_rec.transaction_interface_id := l_itfs_rec.transaction_interface_id;
l_cst_itfs_rec.last_update_date := SYSDATE;
l_cst_itfs_rec.last_updated_by := -1;
l_cst_itfs_rec.creation_date := SYSDATE;
l_cst_itfs_rec.created_by := -1;
l_cst_itfs_rec.last_update_login := -1;
l_cst_itfs_rec.request_id := -1;
l_cst_itfs_rec.program_application_id := 702;
l_cst_itfs_rec.program_id := 36119;
l_cst_itfs_rec.program_update_date := SYSDATE;
l_cst_itfs_rec.cost_element_id := 1;
l_cst_itfs_rec.level_type := 1;
l_cst_itfs_rec.organization_id := 83; --需要修改--
l_cst_itfs_rec.value_change := 100;--需要修改--
INSERT INTO mtl_txn_cost_det_interface VALUES l_cst_itfs_rec;
l_api_flag := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_api_stus,
x_msg_count => l_api_msg_cnt,
x_msg_data => l_api_msg_data,
x_trans_count => l_api_trans_cnt,
p_table => 1,
p_header_id => p_txn_header_id);
IF nvl(l_api_flag, -1) <> 0 OR nvl(l_api_stus, 'X') <> 'S' THEN
DBMS_OUTPUT.put_line('成本更新失败, 全部回滚');
DBMS_OUTPUT.put_line( 'l_api_stus-->' || l_api_stus);
DBMS_OUTPUT.put_line('l_api_msg_cnt-->' || l_api_msg_cnt);
DBMS_OUTPUT.put_line('l_api_msg_data-->' || l_api_msg_data);
DBMS_OUTPUT.put_line('l_api_trans_cnt-->' || l_api_trans_cnt);
--RAISE e_itfs_fail;
ELSE
DBMS_OUTPUT.put_line('成本更新成功');
END IF;
END;