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_rev

 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 ) := 'HAND-GH';
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 l_application_id := 702; l_resp_id := 50723;
-- 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 := 'TEST01'; l_rtg_header_rec.organization_code := '101'; l_rtg_header_rec.alternate_routing_code := NULL; --l_rtg_header_rec.completion_subinventory := 'FGI';--200-ITR l_rtg_header_rec.transaction_type := 'UPDATE';
-- Create the routing operations -- operation 1 /* l_operation_tbl(l_cnt).assembly_item_name := 'TEST01'; l_operation_tbl(l_cnt).organization_code := '101'; l_operation_tbl(l_cnt).alternate_routing_code := NULL; l_operation_tbl(l_cnt).department_code := 'THCJ'; 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; l_operation_tbl(l_cnt).standard_operation_code := 'TH'; --l_operation_tbl(l_cnt).yield := 0.87; l_operation_tbl(l_cnt).transaction_type := 'UPDATE'; l_operation_tbl(l_cnt).reference_flag := 2; l_operation_tbl(l_cnt).attribute1 := 'Y';*/ -- l_op_resource_tbl(l_cnt).assembly_item_name := 'TEST01'; l_op_resource_tbl(l_cnt).organization_code := '101'; l_op_resource_tbl(l_cnt).alternate_routing_code := NULL; l_op_resource_tbl(l_cnt).operation_sequence_number := '20'; l_op_resource_tbl(l_cnt).usage_rate_or_amount := 3; l_op_resource_tbl(l_cnt).transaction_type := 'UPDATE'; l_op_resource_tbl(l_cnt).resource_sequence_number := '10'; l_op_resource_tbl(l_cnt).op_start_effective_date := to_date( '2014/5/29 18:58:26', 'YYYY-MM-DD hh24:mi:ss' ); --Actually the issue again was effectivity date, for Resource update I was supplying effectivity date l_op_resource_tbl (j).op_start_effective_date which was not exact match as in Application. --Picked up correct effectivity date and it worked fine. --l_op_resource_tbl(l_cnt).resource_code := 'THL'; /*-- 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;

下载本文