Oracle OPM 完成工单

DECLARE

  l_org_code VARCHAR2(30);

  l_message_count NUMBER;
  l_message_list  VARCHAR2(240);
  l_return_status VARCHAR2(1);
  l_msg_data      VARCHAR2(240);

  lv_batch_header_rec      gme_batch_header%ROWTYPE;
  lx_batch_header_rec      gme_batch_header%ROWTYPE;
  l_exception_material_tbl gme_common_pvt.exceptions_tab;
BEGIN

  fnd_global.apps_initialize(user_id => 86329, resp_id => 55699, resp_appl_id => 553);
  lv_batch_header_rec          := NULL;
  lv_batch_header_rec.batch_id := 5414792;

  gme_api_pub.complete_batch(p_api_version            => 2,
                             p_validation_level       => fnd_api.g_valid_level_full,
                             p_init_msg_list          => fnd_api.g_true,
                             p_commit                 => fnd_api.g_false,
                             x_message_count          => l_message_count,
                             x_message_list           => l_message_list,
                             x_return_status          => l_return_status,
                             p_batch_header_rec       => lv_batch_header_rec,
                             p_org_code               => l_org_code,
                             p_ignore_exception       => fnd_api.g_true, --是否忽略例外
                             p_validate_flexfields    => fnd_api.g_false,
                             x_batch_header_rec       => lx_batch_header_rec,
                             x_exception_material_tbl => l_exception_material_tbl);
  dbms_output.put_line('l_return_status :=' || l_return_status);
  dbms_output.put_line('l_message_count :=' || l_message_count);
  dbms_output.put_line('l_message_list :=' || l_message_list);
  dbms_output.put_line('batch_status :=' || lx_batch_header_rec.batch_status);
  dbms_output.put_line('batch_close_date :=' || to_char(lx_batch_header_rec.batch_close_date, 'YYYY-MM-DD HH24:MI:SS'));
  IF (l_return_status <> fnd_api.g_ret_sts_success)
  THEN
  
    dbms_output.put_line('Batch complete error!');
  
    l_message_count := fnd_msg_pub.count_msg;
  
    IF l_message_count > 1
    THEN
      FOR i IN 1 .. l_message_count
      LOOP
        l_msg_data := substr(fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false), 1, 240);
        IF l_msg_data IS NOT NULL
        THEN
          dbms_output.put_line('[' || i || ']' || l_msg_data);
        END IF;
      END LOOP;
      fnd_msg_pub.delete_msg();
    ELSE
      dbms_output.put_line(l_message_list);
    END IF;
  
  ELSE
    dbms_output.put_line('Batch has been completed!');
  END IF; 
  
  --当 p_ignore_exception 为空时候，忽略物料例外
  IF l_exception_material_tbl.count > 0
  THEN
    FOR i IN 1 .. l_exception_material_tbl.count
    LOOP
      dbms_output.put_line('material_detail_id :=' || l_exception_material_tbl(i).material_detail_id);
      dbms_output.put_line('transacted_qty :=' || l_exception_material_tbl(i).transacted_qty);
      dbms_output.put_line('onhand_qty :=' || l_exception_material_tbl(i).onhand_qty);
      dbms_output.put_line('exception_qty :=' || l_exception_material_tbl(i).exception_qty); 
    END LOOP;
  END IF;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('sqlerrm :=' || SQLERRM);
END;