物料
PROCEDURE update_item(p_init_msg_list 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) IS l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ITEM';
l_savepoint_name VARCHAR2(30) := 'UPDATE_ITEM';
l_item_rec1 inv_item_grp.item_rec_type;
x_item_rec1 inv_item_grp.item_rec_type;
l_item_rec inv_item_grp.item_rec_type;
x_item_rec inv_item_grp.item_rec_type;
x_error_tbl inv_item_grp.error_tbl_type;
-- x_return_status VARCHAR2(10);
l_user_id NUMBER := 0; --User ID, Sysadmin here
l_message VARCHAR2(500);
x_message VARCHAR2(500);
l_buyer_id NUMBER;
l_old_category_id NUMBER;
l_old_category_id1 NUMBER;
l_category_id NUMBER;
l_category_id1 NUMBER;
l_category_set_name VARCHAR2(240) := '库存';
--l_item_id NUMBER;
--l_organization_id NUMBER;
l_errorcode VARCHAR2(100);
l_msg_count NUMBER;
l_structure_id NUMBER;
l_structure_id1 NUMBER;
l_category_set_id NUMBER;
l_category_set_id1 NUMBER; l_chart_of_account_id NUMBER;
l_ccid1 NUMBER;
l_ccid2 NUMBER;
l_ccid3 NUMBER;
l_ccid4 NUMBER;
l_default_org NUMBER;
l_organization_id NUMBER;
l_organization_id1 NUMBER;
l_item_id NUMBER;
l_organization_id2 NUMBER;
l_item_id1 NUMBER; l_err_count NUMBER; --更新类别信息
l_category_rec inv_item_category_pub.category_rec_type;
BEGIN -- and initialize message list, include debug message hint to enter api
x_return_status := hss_api.start_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_init_msg_list => p_init_msg_list);
-- check if activity started successfully
IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
END IF; BEGIN
SELECT mcs.category_set_id,
mcs.structure_id
INTO l_category_set_id,
l_structure_id
FROM mtl_category_sets mcs
WHERE mcs.category_set_name = l_category_set_name;
EXCEPTION
WHEN OTHERS THEN
hss_conc_utl.log_msg('无效的类别集！');
RETURN;
END; l_err_count := 0;
FOR c IN (SELECT *
FROM cux.cux_mtl_update_prperty ccp
WHERE nvl(ccp.return_stastus,
'N') <> 'S'
ORDER BY ccp.item_code)
LOOP
SAVEPOINT l_savepoint_name; --设置保存点 hss_conc_utl.log_msg('物料编码：' || c.item_code); l_message := NULL;
--=============================================================================
/*当需要更新物料说明和默认发运组织时，需先更新主组织的物料说明和默认发运组织*/
--=============================================================================
IF (c.desc_mir IS NOT NULL OR c.default_shipping_org IS NOT NULL) THEN l_item_rec1.organization_code := 'MST';
l_item_rec1.item_number := c.item_code; --IF (c.desc_mir IS NOT NULL) THEN
-- l_item_rec1.description := c.desc_mir;
--ELSE
-- l_item_rec1.description := NULL;
---END IF; IF c.item_desc IS NOT NULL THEN
l_item_rec1.long_description := c.item_desc;
ELSE
l_item_rec1.long_description := NULL;
END IF; IF (c.default_shipping_org IS NOT NULL) THEN
BEGIN
SELECT oog.organization_id
INTO l_default_org
FROM org_organization_definitions oog
WHERE oog.organization_code = c.default_shipping_org;
EXCEPTION
WHEN OTHERS THEN
l_default_org := NULL;
END; l_item_rec1.default_shipping_org := l_default_org;
ELSE
l_item_rec1.default_shipping_org := NULL;
END IF; --API
----更新物料信息-----
inv_item_grp.update_item(p_commit => fnd_api.g_false,
p_item_rec => l_item_rec1,
x_item_rec => x_item_rec1,
x_return_status => x_return_status,
x_error_tbl => x_error_tbl);
IF x_return_status <> fnd_api.g_ret_sts_success THEN
-- l_message := NULL;
FOR i IN 1 .. x_error_tbl.count
LOOP
l_message := l_message || x_error_tbl(i).message_name;
END LOOP;
x_error_tbl.delete;
--ELSE
-- l_message := NULL;
END IF;
END IF; --IF (c.desc_mir IS NOT NULL OR c.default_shipping_org IS NOT NULL) THEN --=============================================================================
--更新子组织信息
--============================================================================
--Master Inv Organization first, then Current Inv Organization
l_item_rec.organization_code := c.organization_code;
l_item_rec.item_number := c.item_code; IF c.buyer IS NOT NULL THEN
BEGIN
SELECT he.employee_id
INTO l_buyer_id
FROM hr_employees he
WHERE he.full_name = c.buyer;
EXCEPTION
WHEN OTHERS THEN
l_buyer_id := NULL;
END;
IF l_buyer_id IS NOT NULL THEN
l_item_rec.buyer_id := l_buyer_id;
END IF;
ELSE
l_item_rec.buyer_id := NULL;
END IF;
IF c.planer IS NOT NULL THEN
l_item_rec.planner_code := c.planer;
ELSE
l_item_rec.planner_code := NULL;
END IF;
IF c.attribute1 IS NOT NULL THEN
l_item_rec.attribute1 := c.attribute1;
ELSE
l_item_rec.attribute1 := NULL;
END IF;
IF c.attribute2 IS NOT NULL THEN
l_item_rec.attribute2 := c.attribute2;
ELSE
l_item_rec.attribute2 := NULL;
END IF;
IF c.attribute3 IS NOT NULL THEN
l_item_rec.attribute3 := c.attribute3;
ELSE
l_item_rec.attribute3 := NULL;
END IF;
IF c.attribute4 IS NOT NULL THEN
l_item_rec.attribute4 := c.attribute4;
ELSE
l_item_rec.attribute4 := NULL;
END IF;
IF c.attribute5 IS NOT NULL THEN
l_item_rec.attribute5 := c.attribute5;
ELSE
l_item_rec.attribute5 := NULL;
END IF;
IF c.attribute6 IS NOT NULL THEN
l_item_rec.attribute6 := c.attribute6;
ELSE
l_item_rec.attribute6 := NULL;
END IF;
IF c.attribute7 IS NOT NULL THEN
l_item_rec.attribute7 := c.attribute7;
ELSE
l_item_rec.attribute7 := NULL;
END IF;
IF c.attribute8 IS NOT NULL THEN
l_item_rec.attribute8 := c.attribute8;
ELSE
l_item_rec.attribute8 := NULL;
END IF;
IF c.attribute9 IS NOT NULL THEN
l_item_rec.attribute9 := c.attribute9;
ELSE
l_item_rec.attribute9 := NULL;
END IF;
IF c.attribute10 IS NOT NULL THEN
l_item_rec.attribute10 := c.attribute10;
ELSE
l_item_rec.attribute10 := NULL;
END IF;
IF c.attribute11 IS NOT NULL THEN
l_item_rec.attribute11 := c.attribute11;
ELSE
l_item_rec.attribute11 := NULL;
END IF;
IF c.attribute12 IS NOT NULL THEN
l_item_rec.attribute12 := c.attribute12;
ELSE
l_item_rec.attribute12 := NULL;
END IF;
IF c.attribute13 IS NOT NULL THEN
l_item_rec.attribute13 := c.attribute13;
ELSE
l_item_rec.attribute13 := NULL;
END IF;
IF c.attribute14 IS NOT NULL THEN
l_item_rec.attribute14 := c.attribute14;
ELSE
l_item_rec.attribute14 := NULL;
END IF;
IF c.attribute15 IS NOT NULL THEN
l_item_rec.attribute15 := c.attribute15;
ELSE
l_item_rec.attribute15 := NULL;
END IF;
IF c.attribute16 IS NOT NULL THEN
l_item_rec.attribute16 := c.attribute16;
ELSE
l_item_rec.attribute16 := NULL;
END IF;
IF c.attribute17 IS NOT NULL THEN
l_item_rec.attribute17 := c.attribute17;
ELSE
l_item_rec.attribute17 := NULL;
END IF;
IF c.attribute18 IS NOT NULL THEN
l_item_rec.attribute18 := c.attribute18;
ELSE
l_item_rec.attribute18 := NULL;
END IF;
IF c.attribute19 IS NOT NULL THEN
l_item_rec.attribute19 := c.attribute19;
ELSE
l_item_rec.attribute19 := NULL;
END IF;
IF c.attribute20 IS NOT NULL THEN
l_item_rec.attribute20 := c.attribute20;
ELSE
l_item_rec.attribute20 := NULL;
END IF; IF (c.supply_subinventory IS NOT NULL) THEN
l_item_rec.wip_supply_subinventory := c.supply_subinventory;
ELSE
l_item_rec.wip_supply_subinventory := NULL;
END IF; IF (c.min_quantity IS NOT NULL) THEN
l_item_rec.min_minmax_quantity := c.min_quantity;
ELSE
l_item_rec.min_minmax_quantity := NULL;
END IF;
IF (c.max_quantity IS NOT NULL) THEN
l_item_rec.max_minmax_quantity := c.max_quantity;
ELSE
l_item_rec.max_minmax_quantity := NULL;
END IF;
IF (c.min_order_quantity IS NOT NULL) THEN
l_item_rec.minimum_order_quantity := c.min_order_quantity;
ELSE
l_item_rec.minimum_order_quantity := NULL;
END IF;
IF (c.max_order_quantity IS NOT NULL) THEN
l_item_rec.maximum_order_quantity := c.max_order_quantity;
ELSE
l_item_rec.maximum_order_quantity := NULL;
END IF;
IF (c.fixed_order_quantity IS NOT NULL) THEN
l_item_rec.fixed_order_quantity := c.fixed_order_quantity;
ELSE
l_item_rec.fixed_order_quantity := NULL;
END IF;
IF (c.fixed_days_supply IS NOT NULL) THEN
l_item_rec.fixed_days_supply := c.fixed_days_supply;
ELSE
l_item_rec.fixed_days_supply := NULL;
END IF;
IF (c.fixed_lot_multiplier IS NOT NULL) THEN
l_item_rec.fixed_lot_multiplier := c.fixed_lot_multiplier;
ELSE
l_item_rec.fixed_lot_multiplier := NULL;
END IF; BEGIN
SELECT gsob.chart_of_accounts_id
INTO l_chart_of_account_id
FROM gl_sets_of_books gsob,
hr_operating_units hou,
org_organization_definitions ood
WHERE gsob.set_of_books_id = hou.set_of_books_id
AND hou.organization_id = ood.operating_unit
AND ood.organization_code = c.organization_code;
EXCEPTION
WHEN OTHERS THEN
hss_conc_utl.log_msg('chart_of_account_id 没有找到'); l_message := l_message || '组织对应chart_of_account_id 没有找到==';
END; ----------------------- IF (c.encumbrance_account IS NOT NULL) THEN
l_ccid1 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_chart_of_account_id,
validation_date => to_char(SYSDATE,
'DD-MON-YYYY'),
concatenated_segments => c.encumbrance_account);
l_item_rec.encumbrance_account := l_ccid1; IF (l_ccid1 IS NULL) THEN
hss_conc_utl.log_msg('更新保留款账户异常！');
l_message := l_message || '保留款账户异常==';
END IF;
ELSE
l_item_rec.encumbrance_account := NULL;
END IF;
IF (c.expense_account IS NOT NULL) THEN
l_ccid2 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_chart_of_account_id,
validation_date => to_char(SYSDATE,
'DD-MON-YYYY'),
concatenated_segments => c.expense_account);
l_item_rec.expense_account := l_ccid2; IF (l_ccid2 IS NULL) THEN
hss_conc_utl.log_msg('更新费用账户异常！');
l_message := l_message || '费用账户异常==';
END IF;
ELSE
l_item_rec.expense_account := NULL;
END IF; --l_item_rec.sales_account := NULL;
IF (c.sales_account IS NOT NULL) THEN
l_ccid3 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_chart_of_account_id,
validation_date => to_char(SYSDATE,
'DD-MON-YYYY'),
concatenated_segments => c.sales_account);
l_item_rec.sales_account := l_ccid3; IF (l_ccid3 IS NULL) THEN
hss_conc_utl.log_msg('更新销售账户异常！');
l_message := l_message || '销售账户异常==';
END IF;
ELSE
hss_conc_utl.log_msg('物料' || c.item_code || '下的销售账户不能为空');
l_message := l_message || '物料' || c.item_code || '下的销售账户不能为空==';
-- l_item_rec.sales_account := NULL;
END IF; --l_item_rec.cost_of_sales_account := NULL;
IF (c.cost_of_sales_account IS NOT NULL) THEN
l_ccid4 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_chart_of_account_id,
validation_date => to_char(SYSDATE,
'DD-MON-YYYY'),
concatenated_segments => c.cost_of_sales_account);
l_item_rec.cost_of_sales_account := l_ccid4; IF (l_ccid4 IS NULL) THEN
hss_conc_utl.log_msg('更新销售成本账户异常！');
l_message := l_message || '销售成本账户异常==';
END IF;
ELSE
hss_conc_utl.log_msg('物料' || c.item_code || '下的销售成本账户不能为空');
l_message := l_message || '物料' || c.item_code || '下的销售成本账户不能为空==';
-- l_item_rec.cost_of_sales_account := NULL;
END IF;
--API
----更新物料信息-----
inv_item_grp.update_item(p_commit => fnd_api.g_false,
p_item_rec => l_item_rec,
x_item_rec => x_item_rec,
x_return_status => x_return_status,
x_error_tbl => x_error_tbl); --Result
IF x_return_status <> fnd_api.g_ret_sts_success THEN
FOR i IN 1 .. x_error_tbl.count
LOOP
l_message := l_message || x_error_tbl(i).message_name;
END LOOP;
x_error_tbl.delete;
END IF; IF x_return_status = fnd_api.g_ret_sts_success THEN
IF c.inv_category_contractsegment IS NOT NULL THEN BEGIN
--获取物料类别ID--------
SELECT mcv.category_id
INTO l_category_id
FROM mtl_categories_v mcv
WHERE 1 = 1
AND mcv.structure_id = l_structure_id
AND mcv.category_concat_segs = c.inv_category_contractsegment
AND (mcv.disable_date IS NULL OR mcv.disable_date > SYSDATE);
EXCEPTION
WHEN OTHERS THEN
l_category_id := NULL;
END; IF l_category_id IS NULL THEN
x_return_status := 'E';
l_message := l_message || '物料类别 :' || c.inv_category_contractsegment || '无效';
ELSE ----获取原来类别id
BEGIN
SELECT mic.category_id
INTO l_old_category_id
FROM mtl_system_items_b msi,
mtl_item_categories mic
WHERE 1 = 1
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = x_item_rec.inventory_item_id
AND msi.organization_id = x_item_rec.organization_id
AND mic.category_set_id = l_category_set_id;
EXCEPTION
WHEN OTHERS THEN
l_old_category_id := NULL;
END; --- inv_item_category_pub.Create_Category_Assignment
IF l_old_category_id IS NOT NULL
AND l_old_category_id <> l_category_id THEN
---更新物料类别分配
inv_item_category_pub.update_category_assignment(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_category_id => l_category_id,
p_old_category_id => l_old_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => x_item_rec.inventory_item_id,
p_organization_id => x_item_rec.organization_id,
x_return_status => x_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => x_message); IF x_return_status <> fnd_api.g_ret_sts_success THEN hss_conc_utl.log_msg('l_category_id:=' || l_category_id);
hss_conc_utl.log_msg('l_category_set_id:=' || l_category_set_id); l_message := l_message || '==分配物料类别报错：' || x_message; END IF;
ELSIF l_old_category_id IS NULL THEN
inv_item_category_pub.create_category_assignment(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => x_message,
p_category_id => l_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => x_item_rec.inventory_item_id,
p_organization_id => x_item_rec.organization_id);
IF x_return_status <> fnd_api.g_ret_sts_success THEN
hss_conc_utl.log_msg('l_category_id1:=' || l_category_id);
hss_conc_utl.log_msg('l_category_set_id1:=' || l_category_set_id); l_message := l_message || '==创建物料类别报错：' || x_message;
END IF;
END IF;
END IF; --IF l_old_category_id IS NOT NULL END IF; -- IF c.inv_category_contractsegment IS NOT NULL THEN --更新类别集
FOR rec_cate IN (SELECT mcs.category_set_id,
mcs.structure_id,
mcs.category_set_name
FROM mtl_category_sets mcs
WHERE 1 = 1
AND ((mcs.category_set_name = 'BOSUN成本类别集' AND c.chenben_category IS NOT NULL) OR
(mcs.category_set_name = 'BOSUN生产类别集' AND c.shengchan_category IS NOT NULL) OR
(mcs.category_set_name = 'BOSUN折扣类别集' AND c.zhekou_category IS NOT NULL)))
LOOP
hss_conc_utl.log_msg('-----------------------------------'); l_structure_id1 := rec_cate.structure_id;
l_category_set_id1 := rec_cate.category_set_id; BEGIN
--获取物料类别ID--------
l_category_id := NULL;
SELECT mcv.category_id
INTO l_category_id1
FROM mtl_categories_v mcv
WHERE 1 = 1
AND mcv.structure_id = l_structure_id1
AND mcv.category_concat_segs = decode(rec_cate.category_set_name,
'BOSUN成本类别集',
c.chenben_category,
'BOSUN生产类别集',
c.shengchan_category,
'BOSUN折扣类别集',
c.zhekou_category,
NULL)
AND (mcv.disable_date IS NULL OR mcv.disable_date > SYSDATE);
EXCEPTION
WHEN OTHERS THEN
l_category_id1 := NULL;
END; IF (rec_cate.category_set_name IN ('BOSUN生产类别集',
'BOSUN成本类别集')) THEN l_organization_id2 := 102;
ELSE
l_organization_id2 := x_item_rec.organization_id;
END IF; ----获取原来类别id
BEGIN
SELECT mic.category_id
INTO l_old_category_id1
FROM mtl_system_items_b msi,
mtl_item_categories mic
WHERE 1 = 1
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = x_item_rec.inventory_item_id
AND msi.organization_id = l_organization_id2
AND mic.category_set_id = l_category_set_id1;
EXCEPTION
WHEN OTHERS THEN
l_old_category_id1 := NULL;
END; hss_conc_utl.log_msg('l_old_category_id1:=' || l_old_category_id1); IF l_old_category_id1 IS NOT NULL
-- AND l_old_category_id1 <> l_category_id
THEN
---更新物料类别分配
hss_conc_utl.log_msg('---更新类别集');
inv_item_category_pub.update_category_assignment(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_category_id => l_category_id1,
p_old_category_id => l_old_category_id1,
p_category_set_id => l_category_set_id1,
p_inventory_item_id => x_item_rec.inventory_item_id,
p_organization_id => l_organization_id2,
x_return_status => x_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => x_message); IF x_return_status <> fnd_api.g_ret_sts_success THEN hss_conc_utl.log_msg('l_category_id1:=' || l_category_id1);
hss_conc_utl.log_msg('l_category_set_id:=' || l_category_set_id1); l_message := l_message || '--分配物料类别报错：' || x_message;
END IF;
ELSIF l_old_category_id1 IS NULL THEN
hss_conc_utl.log_msg('--创建类别集'); inv_item_category_pub.create_category_assignment(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => x_message,
p_category_id => l_category_id1,
p_category_set_id => l_category_set_id1,
p_inventory_item_id => x_item_rec.inventory_item_id,
p_organization_id => l_organization_id2);
IF x_return_status <> fnd_api.g_ret_sts_success THEN hss_conc_utl.log_msg('l_category_id1:=' || l_category_id);
hss_conc_utl.log_msg('l_category_set_id1:=' || l_category_set_id1); l_message := l_message || '--创建物料类别报错：' || x_message;
END IF;
END IF; END LOOP; END IF; --IF x_return_status = fnd_api.g_ret_sts_success THEN IF (l_message IS NOT NULL) THEN
--dbms_transaction.rollback_savepoint(l_savepoint_name);
hss_conc_utl.log_msg('l_message:=' || l_message);
l_err_count := l_err_count + 1; ROLLBACK TO l_savepoint_name; UPDATE cux.cux_mtl_update_prperty t
SET t.return_stastus = 'E',
t.mes_text = substr(l_message,
1,
2000)
WHERE t.id = c.id; l_message := NULL;
continue;
ELSE
UPDATE cux.cux_mtl_update_prperty t
SET t.return_stastus = x_return_status,
t.mes_text = substr(l_message,
1,
200)
WHERE t.id = c.id;
l_message := NULL;
END IF;
END LOOP; IF (l_err_count > 0) THEN
hss_conc_utl.log_msg('存在异常未处理的数据，请求报黄！');
RAISE fnd_api.g_exc_error;
END IF;
-- end activity, include debug message hint to exit api
x_return_status := hss_api.end_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data); EXCEPTION
WHEN fnd_api.g_exc_error THEN
x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_exc_name => hss_api.g_exc_name_error,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WHEN fnd_api.g_exc_unexpected_error THEN
x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_exc_name => hss_api.g_exc_name_unexp,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WHEN OTHERS THEN
x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_exc_name => hss_api.g_exc_name_others,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END update_item;
物料属性更新