库存事务处理批次有时候出现导入错误需要更新可使用次程序更新,
库存事务处理批次导入
- 库存事务处理-物料批次导入
- --系统批次表
- 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 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;
库存事务处理批次有时候出现导入错误需要更新可使用次程序更新
库存事务处理批次更新
[sql] view plain copy
- DECLARE
- 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;
- l_count NUMBER;
- x_mtl_lot_numbers mtl_lot_numbers%ROWTYPE;
- l_mtl_lot_numbers mtl_lot_numbers%ROWTYPE;
- CURSOR cux_inv(p_lot_number IN VARCHAR2) IS
- SELECT l.lot_number, l.lot_date
- FROM oms_inv_lot_number l
- WHERE l.lot_number = p_lot_number;
- CURSOR cur_mtl IS
- SELECT l.inventory_item_id, l.organization_id, l.lot_number
- FROM mtl_lot_numbers l;
- BEGIN
- fnd_global.apps_initialize(user_id => 1110,
- resp_id => 20634,
- resp_appl_id => 401);
- --MO_GLOBAL.set_policy_context(p_access_mode => 'S',p_org_id => 83);
- -- fnd_profile.PUT(NAME =>'MFG_ORGANIZATION_ID' ,VAL => 83);
- FOR rec_mtl IN cur_mtl LOOP
- FOR rec_inv IN cux_inv(p_lot_number => rec_mtl.lot_number) LOOP
- l_mtl_lot_numbers.inventory_item_id := rec_mtl.inventory_item_id;
- l_mtl_lot_numbers.organization_id := rec_mtl.organization_id;
- l_mtl_lot_numbers.lot_number := rec_mtl.lot_number;
- l_mtl_lot_numbers.origination_date := rec_inv.lot_date;
- inv_lot_api_pub.update_inv_lot(x_return_status => l_return_status,
- x_msg_count => l_msg_count,
- x_msg_data => l_msg_data,
- 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);
- END LOOP;
- END LOOP;
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('---' || SQLERRM);
- END;
- SELECT DISTINCT t.originati
- on_date FROM mtl_lot_numbers t;