INV*物料接收子库存更新
DECLARE
x_return_status VARCHAR2(30);
l_error_tbl inv_item_grp.error_tbl_type;
CURSOR cur_item IS
SELECT t.organization_name,
t.item_number,
t.item_desc,
t.receiving_routing,
t.attribute1,
t.attribute2,
t.attribute3,
t.attribute4,
t.attribute5,
t.attribute6,
t.attribute7,
t.attribute8,
t.attribute9,
t.attribute10,
t.attribute11,
t.attribute12,
t.attribute13,
t.attribute14,
t.attribute15
FROM cux_item_update_temp t
WHERE 1 = 1;
l_organization_id NUMBER;
l_inventory_item_id NUMBER;
l_error_msg VARCHAR2(32767);
l_receiving_routing_id NUMBER;
l_return_status VARCHAR2(240);
l_msg_count NUMBER;
x_msg_data VARCHAR2(240);
BEGIN
FOR rec IN cur_item
LOOP
--组织
l_organization_id := NULL;
BEGIN
SELECTood.organization_id
INTO l_organization_id
FROM org_organization_definitions ood
WHERE 1 = 1
EXCEPTION
WHEN OTHERS THEN
l_organization_id := NULL;
END;
IF (l_organization_id IS NULL) THEN
l_error_msg := l_error_msg || '组织' ||rec.organization_name|| '不存在！';
END IF;
--物料
l_inventory_item_id := NULL;
BEGIN
SELECT msi.inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b msi
WHERE 1 = 1
ANDmsi.organization_id= l_organization_id
AND msi.segment1 = rec.item_number;
EXCEPTION
WHEN OTHERS THEN
l_inventory_item_id := NULL;
END;
IF (l_inventory_item_id IS NULL) THEN
l_error_msg := l_error_msg || '物料' || rec.item_number || '不存在！';
END IF;
--导入
IF (l_error_msg IS NOT NULL) THEN
dbms_output.put_line(rec.item_number || ' 出错：' || l_error_msg);
ELSE
inv_item_sub_default_pkg.insert_upd_item_sub_defaults(p_organization_id => l_organization_id,
p_inventory_item_id => l_inventory_item_id,
p_subinventory_code => rec.receiving_routing,
p_default_type => 2, --1. default shipping subinventory; 2. default receiving subinventory
p_creation_date => SYSDATE,
p_created_by => -1,
p_last_update_date => SYSDATE,
p_last_updated_by => -1,
p_process_code => 'INSERT', --INSERT/UPDATE
p_commit => fnd_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => x_msg_data);
IF x_return_status <> fnd_api.g_ret_sts_success THEN
FOR i IN 1 .. l_error_tbl.last
LOOP
l_error_msg := substrb(l_error_msg || l_error_tbl(i).column_name || ':' || l_error_tbl(i)
.message_text || '#',
1,
200);
END LOOP;
dbms_output.put_line(rec.item_number || ' 出错：' || l_error_msg);
END IF;
END IF;
END LOOP; --FOR rec_item IN cur_item LOOP
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception');
END;
