一、物料删除
DECLARE
x_errbuf VARCHAR2(240);
x_retcode VARCHAR2(240);
l_iface_rec bom.bom_delete_entities%ROWTYPE;
l_cur_mfg_org_id NUMBER := 81; --Current Inv Organization,注意不是82,因为是当前职责所在的组织
l_user_id NUMBER := 0; --User ID, Sysadmin here
l_delete_group_name VARCHAR2(10) := 'DEL_ITEM_G'; --Item Delete group
l_item_number VARCHAR2(240) := '2010230033'; --Item Number
-- select * from mtl_system_items_b
BEGIN
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := l_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := l_user_id;
l_iface_rec.last_update_login := -1;
l_iface_rec.delete_status_type := 1;
l_iface_rec.prior_process_flag := 2;
l_iface_rec.prior_commit_flag := 1;
SELECT l_cur_mfg_org_id,
bdg.delete_group_sequence_id,
bom_delete_entities_s.NEXTVAL,
bdg.delete_type,
mst.inventory_item_id,
l_item_number,
mst.description
INTO l_iface_rec.organization_id,
l_iface_rec.delete_group_sequence_id,
l_iface_rec.delete_entity_sequence_id,
l_iface_rec.delete_entity_type,
l_iface_rec.inventory_item_id,
l_iface_rec.item_concat_segments,
l_iface_rec.item_description
FROM bom.bom_delete_groups bdg, inv.mtl_system_items_b mst
WHERE bdg.delete_group_name = l_delete_group_name
AND bdg.organization_id = l_cur_mfg_org_id
AND mst.organization_id = l_cur_mfg_org_id
AND mst.segment1 = l_item_number;
INSERT INTO bom.bom_delete_entities VALUES l_iface_rec;
bom_delete_groups_api.delete_groups(errbuf => x_errbuf,
retcode => x_retcode,
delete_group_id => l_iface_rec.delete_group_sequence_id,
action_type => 2 --1 Check, 2 Delete
);
dbms_output.put_line('x_errbuf:' || x_errbuf);
dbms_output.put_line('x_retcode:' || x_retcode);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Delete group or item not exists.');
END;
二、物料清单和工艺路线删除
SELECT bdg.delete_group_sequence_id,
bdg.delete_group_name,
bdg.delete_type,
bdg.action_type,
bdg.description,
bdg.delete_org_type
FROM bom_delete_groups bdg
WHERE 1 = 1
--AND bdg.action_type = 2--新建时为1,运行请求后为2
AND bdg.delete_org_type = 1
AND bdg.delete_type = p_delete_type--2 清单,3 工艺路线,4 组件
AND bdg.delete_group_name = p_delete_group_name
AND bdg.organization_id = p_organization_id
AND ROWNUM = 1;
l_bom_delete_groups := get_delete_group(p_organization_id => rec_org.organization_id,
p_delete_group_name => g_bom_del_group_name,
p_delete_type => 2
);
l_opr_delete_groups := get_delete_group(p_organization_id => rec_org.organization_id,
p_delete_group_name => g_opr_del_group_name,
p_delete_type => 3
);
l_success_count := 0;
l_opr_success_count := 0;
FOR rec_bom IN cur_bom(p_organization_id => rec_org.organization_id,p_inventory_item_id => p_inventory_item_id) LOOP
-- initialize BOM header
l_bom_header_rec.assembly_item_name := rec_bom.ass_item_number; --装配
l_bom_header_rec.alternate_bom_code := rec_bom.alternate_bom_designator;
l_bom_header_rec.organization_code := rec_org.organization_code;
l_bom_header_rec.assembly_type := rec_bom.assembly_type;
l_bom_header_rec.transaction_type := g_opr_delete;--'CREATE';
--l_bom_header_rec.bom_implementation_date := SYSDATE;
l_bom_header_rec.return_status := NULL;
l_bom_header_rec.delete_group_name := l_bom_delete_groups.delete_group_name;
l_bom_header_rec.dg_description := l_bom_delete_groups.description;
--l_bom_header_rec.Attribute2 := 'KD_ASSEMBLY_CODE';--虚拟组件编码标识
-- initialize error stack for logging errors
Error_Handler.initialize;
-- call API to create / update bill
--log_msg('=======================================================');
--log_msg('Calling Bom_Bo_Pub.process_bom API');
Bom_Bo_Pub.process_bom(p_bo_identifier => 'BOM',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_bom_header_rec => l_bom_header_rec,
p_bom_revision_tbl => l_bom_revision_tbl,
p_bom_component_tbl => l_bom_component_tbl,
p_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
p_bom_sub_component_tbl => l_bom_sub_component_tbl,
x_bom_header_rec => x_bom_header_rec,
x_bom_revision_tbl => x_bom_revision_tbl,
x_bom_component_tbl => x_bom_component_tbl,
x_bom_ref_designator_tbl => x_bom_ref_designator_tbl,
x_bom_sub_component_tbl => x_bom_sub_component_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
p_debug => 'N',
p_output_dir => NULL,--l_output_dir
p_debug_filename => 'BOM_BO_debug.log');--l_debug_filename
--log_msg('=======================================================');
--log_msg('Return Status: ' || l_return_status);
IF (l_return_status = fnd_api.g_ret_sts_success) THEN
--COMMIT;
l_success_count := NVL(l_success_count,0) + 1;
log_msg(' ' || cur_bom%ROWCOUNT || ' [Success] 组织''' || rec_org.organization_code || '''物料''' || rec_bom.ass_item_number || '''BOM已成功加入删除组''' || l_bom_delete_groups.delete_group_name || ''';');
--删除BOM后删除其工艺路线
-- 工艺路线
l_rtg_header_rec.transaction_type := g_opr_delete;--'CREATE';
l_rtg_header_rec.organization_code := rec_org.organization_code;
l_rtg_header_rec.assembly_item_name := rec_bom.ass_item_number;
l_rtg_header_rec.alternate_routing_code := NULL;
l_rtg_header_rec.eng_routing_flag := 2;
l_rtg_header_rec.Delete_Group_Name := l_opr_delete_groups.delete_group_name;
l_rtg_header_rec.DG_Description := l_opr_delete_groups.description;
bom_rtg_pub.process_rtg(p_bo_identifier => 'RTG',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_rtg_header_rec => l_rtg_header_rec,
p_operation_tbl => l_operation_tbl,
--p_op_resource_tbl => l_op_resource_tbl,
x_rtg_header_rec => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl => x_operation_tbl,
x_op_resource_tbl => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl => x_op_network_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
p_debug => 'N',
p_output_dir => '/usr/tmp');
--Result
IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
l_opr_success_count := NVL(l_opr_success_count,0) + 1;
log_msg(' ' || cur_bom%ROWCOUNT || ' [Success] 组织''' || rec_org.organization_code || '''物料''' || rec_bom.ass_item_number || '''工艺路线已成功加入删除组''' || l_opr_delete_groups.delete_group_name || ''';');
ELSE
ROLLBACK;
log_msg(' ' || cur_bom%ROWCOUNT || ' [Error] 组织''' || rec_org.organization_code || '''物料''' || rec_bom.ass_item_number || '''工艺路线加入删除组''' || g_bom_del_group_name || '''失败;');
error_handler.get_message_list(x_message_list => l_error_table);
FOR i IN 1 .. l_error_table.COUNT LOOP
log_msg(' ' || to_char(i) || ':' || l_error_table(i).entity_index || ':' || l_error_table(i).table_name || '-' || substr(l_error_table(i).message_text, 1, 200));
x_msg_data := x_msg_data || l_error_table(i).message_text;
END LOOP;
END IF;
ELSE
ROLLBACK;
Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
--log_msg('Error Message Count :' || l_error_table.COUNT);
log_msg(' ' || cur_bom%ROWCOUNT || '[Error] 组织''' || rec_org.organization_code || '''物料''' || rec_bom.ass_item_number || 'BOM加入删除组''' || g_bom_del_group_name || '''失败;');
FOR i IN 1 .. l_error_table.COUNT LOOP
log_msg(' ' || to_char(i) || ':' || l_error_table(i).entity_index || ':' || l_error_table(i).table_name || '-' || l_error_table(i).message_text);
--log_msg(to_char(i) || ':' || l_error_table(i).message_text);
x_msg_data := x_msg_data || l_error_table(i).message_text;
END LOOP;
END IF;
END LOOP;
--物料清单运行删除组
IF NVL(l_success_count,0) > 0 THEN
--如果删除BOM,需要通过删除组来删除,插入删除组后必须运行'删除物料信息'才能删除
Bom_Delete_Groups_Api.delete_groups(errbuf => x_errbuf,
retcode => x_retcode,
delete_group_id => l_bom_delete_groups.delete_group_sequence_id,
action_type => 2,--1 Check, 2 Delete
delete_type => l_bom_delete_groups.delete_type,
archive => NULL
);
IF NVL(x_retcode,'0') = '0' THEN
l_total_count := NVL(l_total_count,0) + NVL(l_success_count,0);
log_msg('[Success] 组织''' || rec_org.organization_code || '''共有 ' || NVL(l_success_count,0) || ' 个BOM通过删除组''' || g_bom_del_group_name || '''已成功删除;');
ELSE
log_msg('[Error] 组织''' || rec_org.organization_code || '''共有 ' || NVL(l_success_count,0) || ' 个BOM通过删除组''' || g_bom_del_group_name || '''删除发生错误,' || x_errbuf);
END IF;
END IF;
--工艺路线运行删除组
IF NVL(l_opr_success_count, 0) > 0 THEN
--如果删除工艺路线,需要通过删除组来删除,插入删除组后必须运行'删除物料信息'才能删除
Bom_Delete_Groups_Api.delete_groups(errbuf => x_errbuf,
retcode => x_retcode,
delete_group_id => l_opr_delete_groups.delete_group_sequence_id,
action_type => 2,--1 Check, 2 Delete
delete_type => l_opr_delete_groups.delete_type,
archive => NULL
);
IF NVL(x_retcode,'0') = '0' THEN
--l_total_count := NVL(l_total_count,0) + NVL(l_success_count,0);
log_msg('[Success] 组织''' || rec_org.organization_code || '''共有 ' || NVL(l_success_count,0) || ' 个工艺路线通过删除组''' || g_opr_del_group_name || '''已成功删除;');
ELSE
log_msg('[Error] 组织''' || rec_org.organization_code || '''共有 ' || NVL(l_success_count,0) || ' 个工艺路线通过删除组''' || g_opr_del_group_name || '''删除发生错误,' || x_errbuf);
END IF;
END IF;