详解EBS接口开发之库存事务处理-物料批次导入

来源：这里教程网时间：2026-03-03 12:05:22 作者：

详解EBS接口开发之库存事务处理-物料批次导入

2016年11月24日 17:29:01 zhanzhib 阅读数：1333 更多个人分类： INV 接口转：http://blog.csdn.net/cai_xingyun/article/details/17129243

库存事务处理-物料批次导入

--系统批次表

SELECT * FROM MTL_LOT_NUMBERS T;

--API创建批次

inv_lot_api_pub.create_inv_lot(x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_row_id => l_row_id,

x_lot_rec => x_mtl_lot_numbers,

p_lot_rec => l_mtl_lot_numbers,

p_source => l_source,

p_api_version => l_api_version,

p_init_msg_list => l_init_msg_list,

p_commit => l_commit,

p_validation_level => l_validation_level,

p_origin_txn_id => l_origin_txn_id);

--API更新批次

inv_lot_api_pub.update_inv_lot(

x_return_status OUT NOCOPY VARCHAR2

, x_msg_count OUT NOCOPY NUMBER

, x_msg_data OUT NOCOPY VARCHAR2

, x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE

, p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE

, p_source IN NUMBER

, p_api_version IN NUMBER

, p_init_msg_list IN VARCHAR2 := fnd_api.g_false

, p_commit IN VARCHAR2 := fnd_api.g_false);

--验证批次唯一性

inv_lot_api_pub.validate_unique_lot(

p_org_id IN NUMBER

, p_inventory_item_id IN NUMBER

, p_lot_uniqueness IN NUMBER

, p_auto_lot_number IN VARCHAR2

, p_check_same_item IN VARCHAR2

, x_is_unique OUT NOCOPY VARCHAR2

);

--物料批次数量验证

-----------------------------------------------------------------------

-- Name : validate_quantities

-- Desc : This procedure is used to validate transaction quantity2

-- I/P Params :

-- All the relevant transaction details :

-- - organization id

-- - item_id

-- - lot, revision, subinventory

-- - transaction quantities

-- O/P Params :

-- x_rerturn_status.

-- RETURN VALUE :

-- TRUE : IF the transaction is valid regarding Quantity2 and lot indivisible

-- FALSE : IF the transaction is NOT valid regarding Quantity2 and lot indivisible

-----------------------------------------------------------------------

inv_lot_api_pub.validate_quantities(

p_api_version IN NUMBER

, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE

, p_transaction_type_id IN NUMBER

, p_organization_id IN NUMBER

, p_inventory_item_id IN NUMBER

, p_revision IN VARCHAR2

, p_subinventory_code IN VARCHAR2

, p_locator_id IN NUMBER

, p_lot_number IN VARCHAR2

, p_transaction_quantity IN OUT NOCOPY NUMBER

, p_transaction_uom_code IN VARCHAR2

, p_primary_quantity IN OUT NOCOPY NUMBER

, p_primary_uom_code OUT NOCOPY VARCHAR2

, p_secondary_quantity IN OUT NOCOPY NUMBER

, p_secondary_uom_code IN OUT NOCOPY VARCHAR2

, x_return_status OUT NOCOPY VARCHAR2

, x_msg_count OUT NOCOPY NUMBER

, x_msg_data OUT NOCOPY VARCHAR2);

--简单参考例子

/**==================================================

Procedure Name :

create_inv_lot

Description:

This procedure is concurrent entry, perform:

库存事务处理批号生成 api

Argument:

p_inventory_item_id 库存物料id,

p_organization_id 组织id,

p_lot_number 批号:

History:

1.00 2013-10-29 cxy Creation

==================================================*/

PROCEDURE create_inv_lot(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,

p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

p_inventory_item_id IN NUMBER,

p_organization_id IN NUMBER,

p_lot_number VARCHAR2) IS

l_api_name CONSTANT VARCHAR2(30) := 'create_inv_lot' ;

l_count NUMBER;

x_mtl_lot_numbers mtl_lot_numbers%ROWTYPE;

l_mtl_lot_numbers mtl_lot_numbers%ROWTYPE;

/* Defined new variables for overloaded API call */

l_api_version NUMBER := 1.0;

l_init_msg_list VARCHAR2(100) := fnd_api.g_false; -- bug 7513308;

l_commit VARCHAR2(100) := fnd_api.g_false;

l_validation_level NUMBER := fnd_api.g_valid_level_full;

l_origin_txn_id NUMBER := NULL ;

l_source NUMBER := 2;

l_return_status VARCHAR2(1);

l_msg_data VARCHAR2(3000);

l_msg_count NUMBER;

l_row_id ROWID;

BEGIN

SAVEPOINT inv_lot_1;

-- start activity to create savepoint, check compatibility

-- and initialize message list, include debug message hint to enter api

SELECT COUNT (1)

INTO l_count

FROM cux_inv_lot_number

WHERE lot_number = p_lot_number;

IF l_count = 0 THEN

fnd_message.set_name( 'INV' ,

'在系统中不存在此批号：CUX_INV_LOT_NUMBER.lot_number' );

fnd_message.set_token( 'LOT_NUMBER' , to_char(p_lot_number));

fnd_msg_pub. add ;

RAISE fnd_api.g_exc_error;

END IF;

FOR rec_inv_lot IN cur_inv_lot LOOP

l_mtl_lot_numbers.inventory_item_id := p_inventory_item_id;

l_mtl_lot_numbers.organization_id := p_organization_id;

l_mtl_lot_numbers.lot_number := p_lot_number;

l_mtl_lot_numbers.last_update_date := SYSDATE;

l_mtl_lot_numbers.last_updated_by := g_user_id;

l_mtl_lot_numbers.creation_date := rec_inv_lot.lot_date;

l_mtl_lot_numbers.created_by := rec_inv_lot.created_by;

l_mtl_lot_numbers.last_update_login := g_login_id;

l_mtl_lot_numbers.program_application_id := g_prog_appl_id;

l_mtl_lot_numbers.program_id := g_conc_program_id;

l_mtl_lot_numbers.program_update_date := SYSDATE;

l_mtl_lot_numbers.expiration_date := NULL ;

l_mtl_lot_numbers.disable_flag := NULL ;

l_mtl_lot_numbers.attribute_category := NULL ;

l_mtl_lot_numbers.attribute1 := NULL ;

l_mtl_lot_numbers.attribute2 := NULL ;

l_mtl_lot_numbers.attribute3 := NULL ;

l_mtl_lot_numbers.attribute4 := NULL ;

l_mtl_lot_numbers.attribute5 := NULL ;

l_mtl_lot_numbers.attribute6 := NULL ;

l_mtl_lot_numbers.attribute7 := NULL ;

l_mtl_lot_numbers.attribute8 := NULL ;

l_mtl_lot_numbers.attribute9 := NULL ;

l_mtl_lot_numbers.attribute10 := NULL ;

l_mtl_lot_numbers.attribute11 := NULL ;

l_mtl_lot_numbers.attribute12 := NULL ;

l_mtl_lot_numbers.attribute13 := NULL ;

l_mtl_lot_numbers.attribute14 := NULL ;

l_mtl_lot_numbers.attribute15 := NULL ;

l_mtl_lot_numbers.request_id := NULL ;

l_mtl_lot_numbers.gen_object_id := NULL ;

l_mtl_lot_numbers.description := NULL ;

l_mtl_lot_numbers.vendor_name := NULL ;

l_mtl_lot_numbers.supplier_lot_number := NULL ;

l_mtl_lot_numbers.country_of_origin := NULL ;

l_mtl_lot_numbers.grade_code := NULL ;

l_mtl_lot_numbers.origination_date := NULL ;

l_mtl_lot_numbers.date_code := NULL ;

l_mtl_lot_numbers.status_id := NULL ;

l_mtl_lot_numbers.change_date := NULL ;

l_mtl_lot_numbers.age := NULL ;

l_mtl_lot_numbers.retest_date := NULL ;

l_mtl_lot_numbers.maturity_date := NULL ;

l_mtl_lot_numbers.lot_attribute_category := NULL ;

l_mtl_lot_numbers.item_size := NULL ;

l_mtl_lot_numbers.color := NULL ;

l_mtl_lot_numbers.volume := NULL ;

l_mtl_lot_numbers.volume_uom := NULL ;

l_mtl_lot_numbers.place_of_origin := NULL ;

l_mtl_lot_numbers.kill_date := NULL ;

l_mtl_lot_numbers.best_by_date := NULL ;

l_mtl_lot_numbers.length := NULL ;

l_mtl_lot_numbers.length_uom := NULL ;

l_mtl_lot_numbers.recycled_content := NULL ;

l_mtl_lot_numbers.thickness := NULL ;

l_mtl_lot_numbers.thickness_uom := NULL ;

l_mtl_lot_numbers.width := NULL ;

l_mtl_lot_numbers.width_uom := NULL ;

l_mtl_lot_numbers.curl_wrinkle_fold := NULL ;

l_mtl_lot_numbers.c_attribute1 := NULL ;

l_mtl_lot_numbers.c_attribute2 := NULL ;

l_mtl_lot_numbers.c_attribute3 := NULL ;

l_mtl_lot_numbers.c_attribute4 := NULL ;

l_mtl_lot_numbers.c_attribute5 := NULL ;

l_mtl_lot_numbers.c_attribute6 := NULL ;

l_mtl_lot_numbers.c_attribute7 := NULL ;

l_mtl_lot_numbers.c_attribute8 := NULL ;

l_mtl_lot_numbers.c_attribute9 := NULL ;

l_mtl_lot_numbers.c_attribute10 := NULL ;

l_mtl_lot_numbers.c_attribute11 := NULL ;

l_mtl_lot_numbers.c_attribute12 := NULL ;

l_mtl_lot_numbers.c_attribute13 := NULL ;

l_mtl_lot_numbers.c_attribute14 := NULL ;

l_mtl_lot_numbers.c_attribute15 := NULL ;

l_mtl_lot_numbers.c_attribute16 := NULL ;

l_mtl_lot_numbers.c_attribute17 := NULL ;

l_mtl_lot_numbers.c_attribute18 := NULL ;

l_mtl_lot_numbers.c_attribute19 := NULL ;

l_mtl_lot_numbers.c_attribute20 := NULL ;

l_mtl_lot_numbers.c_attribute21 := NULL ;

l_mtl_lot_numbers.c_attribute22 := NULL ;

l_mtl_lot_numbers.c_attribute23 := NULL ;

l_mtl_lot_numbers.c_attribute24 := NULL ;

l_mtl_lot_numbers.c_attribute25 := NULL ;

l_mtl_lot_numbers.c_attribute26 := NULL ;

l_mtl_lot_numbers.c_attribute27 := NULL ;

l_mtl_lot_numbers.c_attribute28 := NULL ;

l_mtl_lot_numbers.c_attribute29 := NULL ;

l_mtl_lot_numbers.c_attribute30 := NULL ;

l_mtl_lot_numbers.d_attribute1 := NULL ;

l_mtl_lot_numbers.d_attribute2 := NULL ;

l_mtl_lot_numbers.d_attribute3 := NULL ;

l_mtl_lot_numbers.d_attribute4 := NULL ;

l_mtl_lot_numbers.d_attribute5 := NULL ;

l_mtl_lot_numbers.d_attribute6 := NULL ;

l_mtl_lot_numbers.d_attribute7 := NULL ;

l_mtl_lot_numbers.d_attribute8 := NULL ;

l_mtl_lot_numbers.d_attribute9 := NULL ;

l_mtl_lot_numbers.d_attribute10 := NULL ;

l_mtl_lot_numbers.d_attribute11 := NULL ;

l_mtl_lot_numbers.d_attribute12 := NULL ;

l_mtl_lot_numbers.d_attribute13 := NULL ;

l_mtl_lot_numbers.d_attribute14 := NULL ;

l_mtl_lot_numbers.d_attribute15 := NULL ;

l_mtl_lot_numbers.d_attribute16 := NULL ;

l_mtl_lot_numbers.d_attribute17 := NULL ;

l_mtl_lot_numbers.d_attribute18 := NULL ;

l_mtl_lot_numbers.d_attribute19 := NULL ;

l_mtl_lot_numbers.d_attribute20 := NULL ;

l_mtl_lot_numbers.n_attribute1 := NULL ;

l_mtl_lot_numbers.n_attribute2 := NULL ;

l_mtl_lot_numbers.n_attribute3 := NULL ;

l_mtl_lot_numbers.n_attribute4 := NULL ;

l_mtl_lot_numbers.n_attribute5 := NULL ;

l_mtl_lot_numbers.n_attribute6 := NULL ;

l_mtl_lot_numbers.n_attribute7 := NULL ;

l_mtl_lot_numbers.n_attribute8 := NULL ;

l_mtl_lot_numbers.n_attribute9 := NULL ;

l_mtl_lot_numbers.n_attribute10 := NULL ;

l_mtl_lot_numbers.n_attribute11 := NULL ;

l_mtl_lot_numbers.n_attribute12 := NULL ;

l_mtl_lot_numbers.n_attribute13 := NULL ;

l_mtl_lot_numbers.n_attribute14 := NULL ;

l_mtl_lot_numbers.n_attribute15 := NULL ;

l_mtl_lot_numbers.n_attribute16 := NULL ;

l_mtl_lot_numbers.n_attribute17 := NULL ;

l_mtl_lot_numbers.n_attribute18 := NULL ;

l_mtl_lot_numbers.n_attribute19 := NULL ;

l_mtl_lot_numbers.n_attribute20 := NULL ;

l_mtl_lot_numbers.n_attribute21 := NULL ;

l_mtl_lot_numbers.n_attribute22 := NULL ;

l_mtl_lot_numbers.n_attribute23 := NULL ;

l_mtl_lot_numbers.n_attribute24 := NULL ;

l_mtl_lot_numbers.n_attribute25 := NULL ;

l_mtl_lot_numbers.n_attribute26 := NULL ;

l_mtl_lot_numbers.n_attribute27 := NULL ;

l_mtl_lot_numbers.n_attribute28 := NULL ;

l_mtl_lot_numbers.n_attribute29 := NULL ;

l_mtl_lot_numbers.n_attribute30 := NULL ;

l_mtl_lot_numbers.vendor_id := NULL ;

l_mtl_lot_numbers.territory_code := NULL ;

l_mtl_lot_numbers.parent_lot_number := NULL ;

l_mtl_lot_numbers.origination_type := NULL ;

l_mtl_lot_numbers.availability_type := NULL ;

l_mtl_lot_numbers.expiration_action_code := NULL ;

l_mtl_lot_numbers.expiration_action_date := NULL ;

l_mtl_lot_numbers.hold_date := NULL ;

l_mtl_lot_numbers.inventory_atp_code := NULL ;

l_mtl_lot_numbers.reservable_type := NULL ;

l_mtl_lot_numbers.sampling_event_id := NULL ;

inv_lot_api_pub.create_inv_lot(x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_row_id => l_row_id,

x_lot_rec => x_mtl_lot_numbers,

p_lot_rec => l_mtl_lot_numbers,

p_source => l_source,

p_api_version => l_api_version,

p_init_msg_list => l_init_msg_list,

p_commit => l_commit,

p_validation_level => l_validation_level,

p_origin_txn_id => l_origin_txn_id);

IF l_return_status = g_ret_sts_success THEN

UPDATE cux_inv_lot_number h

SET h.process_status = 'COMPLETE' ,

h.process_date = SYSDATE,

h.process_message = NULL ,

h.row_version_number = h.row_version_number + 1,

h.last_updated_by = g_user_id,

h.last_update_date = SYSDATE,

h.last_update_login = g_login_id

WHERE h.id = rec_inv_lot.id;

ELSE

UPDATE cux_inv_lot_number h

SET h.process_status = 'ERROR' ,

h.process_date = SYSDATE,

h.process_message = '创建批次失败-' || l_msg_data,

h.row_version_number = h.row_version_number + 1,

h.last_updated_by = g_user_id,

h.last_update_date = SYSDATE,

h.last_update_login = g_login_id

WHERE h.id = rec_inv_lot.id;

IF l_return_status = g_ret_sts_error THEN

RAISE g_exc_error;

ELSIF l_return_status = g_ret_sts_unexp_error THEN

fnd_message.set_name( 'INV' , 'INV_PROGRAM_ERROR' );

fnd_message.set_token( 'PROG_NAME' ,

'inv_lot_api_pub.Create_Inv_lot' );

fnd_msg_pub. add ;

RAISE g_exc_unexpected_error;

END IF;

END LOOP;

-- API end body

-- end activity, include debug message hint to exit api

EXCEPTION

WHEN no_data_found THEN

x_return_status := g_ret_sts_error;

ROLLBACK TO inv_lot_1;

fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,

p_count => x_msg_count,

p_data => x_msg_data);

IF (x_msg_count > 1) THEN

x_msg_data := fnd_msg_pub.get(x_msg_count, fnd_api.g_false);

END IF;

-- print_debug('In No data found Create_Inv_Lot ' || SQLERRM, 9);

WHEN g_exc_error THEN

x_return_status := g_ret_sts_error;

ROLLBACK TO inv_lot_1;

fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,

p_count => x_msg_count,

p_data => x_msg_data);

IF (x_msg_count > 1) THEN

x_msg_data := fnd_msg_pub.get(x_msg_count, fnd_api.g_false);

END IF;

--print_debug('In g_exc_error Create_Inv_Lot ' || SQLERRM, 9);

WHEN g_exc_unexpected_error THEN

x_return_status := g_ret_sts_unexp_error;

ROLLBACK TO inv_lot_1;

fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,

p_count => x_msg_count,

p_data => x_msg_data);

IF (x_msg_count > 1) THEN

x_msg_data := fnd_msg_pub.get(x_msg_count, fnd_api.g_false);

END IF;

WHEN OTHERS THEN

x_return_status := g_ret_sts_unexp_error;

ROLLBACK TO inv_lot_1;

fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,

p_count => x_msg_count,

p_data => x_msg_data);

IF (x_msg_count > 1) THEN

x_msg_data := fnd_msg_pub.get(x_msg_count, fnd_api.g_false);

END IF;

END create_inv_lot;

编辑推荐：

返回列表

详解EBS接口开发之库存事务处理-物料批次导入

详解EBS接口开发之库存事务处理-物料批次导入

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推