首页 > 代码库 > 更新工艺路线资源

更新工艺路线资源


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;

更新工艺路线资源