摘自:https://blog.csdn.net/chosen1brain/article/details/37692653
--此脚本创建了一个工艺路线,并修改了标准工序的单位使用量
DECLARE
-- API input variables
l_operation_tbl bom_rtg_pub.operation_tbl_type := bom_rtg_pub.g_miss_operation_tbl;
l_rtg_header_rec bom_rtg_pub.rtg_header_rec_type := bom_rtg_pub.g_miss_rtg_header_rec;
l_rtg_revision_tbl bom_rtg_pub.rtg_revision_tbl_type := bom_rtg_pub.g_miss_rtg_revision_tbl;
l_op_resource_tbl bom_rtg_pub.op_resource_tbl_type := bom_rtg_pub.g_miss_op_resource_tbl;
l_sub_resource_tbl bom_rtg_pub.sub_resource_tbl_type := bom_rtg_pub.g_miss_sub_resource_tbl;
l_op_network_tbl bom_rtg_pub.op_network_tbl_type := bom_rtg_pub.g_miss_op_network_tbl;
-- API output variables
x_rtg_header_rec bom_rtg_pub.rtg_header_rec_type; -- routing header record
x_rtg_revision_tbl bom_rtg_pub.rtg_revision_tbl_type; -- routing revisions
x_operation_tbl bom_rtg_pub.operation_tbl_type; -- routing operations
x_op_resource_tbl bom_rtg_pub.op_resource_tbl_type; -- oepration resources
x_sub_resource_tbl bom_rtg_pub.sub_resource_tbl_type; -- sub operation resources
x_op_network_tbl bom_rtg_pub.op_network_tbl_type; -- operation networks
x_message_list error_handler.error_tbl_type;
-- Other API variables
l_return_status VARCHAR2(1) := NULL;
l_msg_count NUMBER := 0;
l_cnt NUMBER := 1;
-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2(30) := 'SETUP_USER14';
l_resp_name VARCHAR2(30) := 'XXWE_MG_USER';
BEGIN
-- Get the user_id
SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id,
responsibility_id
INTO l_application_id,
l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
-- Initialize applications information
fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id); -- Mfg / Mfg & Dist Mgr / INV
dbms_output.put_line('Initialized applications context: ' || l_user_id || ' ' || l_resp_id || ' ' || l_application_id);
-- Create the routing header
l_rtg_header_rec.assembly_item_name := 'lcy_test_item01';
l_rtg_header_rec.organization_code := 'WE1';
l_rtg_header_rec.alternate_routing_code := NULL;
--l_rtg_header_rec.completion_subinventory := 'FGI';--200-ITR
l_rtg_header_rec.transaction_type := 'CREATE';
-- Create the routing operations
-- operation 1
l_operation_tbl(l_cnt).assembly_item_name := 'lcy_test_item01';
l_operation_tbl(l_cnt).organization_code := 'WE1';
l_operation_tbl(l_cnt).alternate_routing_code := NULL;
l_operation_tbl(l_cnt).operation_sequence_number := '10';
l_operation_tbl(l_cnt).operation_type := 1;
l_operation_tbl(l_cnt).start_effective_date := SYSDATE;
l_operation_tbl(l_cnt).standard_operation_code := '120F';
l_operation_tbl(l_cnt).yield := 0.87;
l_operation_tbl(l_cnt).transaction_type := 'CREATE';
l_operation_tbl(l_cnt).REFERENCE_FLAG :=2;--参考字段的Flag不能勾选,否则无法更改工序资源的单位使用量
--
l_op_resource_tbl(l_cnt).assembly_item_name := 'lcy_test_item01';
l_op_resource_tbl(l_cnt).organization_code := 'WE1';
l_op_resource_tbl(l_cnt).alternate_routing_code := NULL;
l_op_resource_tbl(l_cnt).operation_sequence_number := '10';
l_op_resource_tbl(l_cnt).USAGE_RATE_OR_AMOUNT := 3;
l_op_resource_tbl(l_cnt).transaction_type := 'CREATE';
l_op_resource_tbl(l_cnt).Resource_Sequence_Number := '10';
l_op_resource_tbl(l_cnt).Op_Start_Effective_Date := sysdate;
l_op_resource_tbl(l_cnt).Resource_Code := '20LT_11';
/*-- operation 2
l_cnt := l_cnt + 1;
l_operation_tbl(l_cnt).assembly_item_name := 'lcy_test_item01';
l_operation_tbl(l_cnt).organization_code := 'M1';
l_operation_tbl(l_cnt).alternate_routing_code := NULL;
l_operation_tbl(l_cnt).operation_sequence_number := '20';
l_operation_tbl(l_cnt).operation_type := 1;
l_operation_tbl(l_cnt).start_effective_date := SYSDATE; -- to_date('16-JUL-2010 19:30:39','DD-MON-YY HH24:MI:SS'); -- should match timestamp for UPDATE
l_operation_tbl(l_cnt).standard_operation_code := 'SFPK';
l_operation_tbl(l_cnt).yield := 1.0;
l_operation_tbl(l_cnt).transaction_type := 'CREATE';*/
-- initialize error stack for logging errors
error_handler.initialize;
-- call API to create / update routing
dbms_output.put_line('=======================================================');
dbms_output.put_line('Calling Bom_Rtg_Pub.Process_Rtg API');
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_rtg_revision_tbl => l_rtg_revision_tbl,
p_operation_tbl => l_operation_tbl,
p_op_resource_tbl => l_op_resource_tbl,
p_sub_resource_tbl => l_sub_resource_tbl,
p_op_network_tbl => l_op_network_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 => l_return_status,
x_msg_count => l_msg_count,
p_debug => 'N',
p_output_dir => '/usr/tmp/',
p_debug_filename => 'rtg_bo_debug.log');
dbms_output.put_line('=======================================================');
dbms_output.put_line('Return Status: ' || l_return_status);
IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
dbms_output.put_line('x_msg_count:' || l_msg_count);
error_handler.get_message_list(x_message_list => x_message_list);
dbms_output.put_line('Error Message Count :' || x_message_list.count);
FOR i IN 1 .. x_message_list.count LOOP
dbms_output.put_line(to_char(i) || ':' || x_message_list(i).entity_index || ':' || x_message_list(i).table_name);
dbms_output.put_line(to_char(i) || ':' || x_message_list(i).message_text);
END LOOP;
END IF;
dbms_output.put_line('=======================================================');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception Occured :');
dbms_output.put_line(SQLCODE || ':' || SQLERRM);
dbms_output.put_line('=======================================================');
RAISE;
END;