如何避免Oracle Form界面倒入.csv文件出现乱码

create or replace package body XXACI_AP_IMPORT_PKG is
  -- Global variable
  g_pkg_name CONSTANT VARCHAR2(30) := 'XXACI_AP_IMPORT_PKG';
  -- Debug Enabled
  l_debug VARCHAR2(1) := nvl(fnd_profile.VALUE('AFLOG_ENABLED'), 'N');
  g_character_set CONSTANT VARCHAR2(30) :=/*'ZHS16CGB231280';--*/ 'UTF8';
  g_max_field     CONSTANT INTEGER := 10;
  TYPE g_extract_tbl IS TABLE OF VARCHAR2(500);
  g_cells    g_extract_tbl;
  g_group_id NUMBER;

  g_currency varchar2(10);
  g_dr_ccid  number;

  procedure extract_blob(p_file_id       IN NUMBER,
                         p_file_name     in varchar2,
                         p_src_cs        IN VARCHAR2,
                         p_delimiter     IN VARCHAR2,
                         p_currency      in varchar2,
                         p_dr_ccid       in number,
                         x_group_id      OUT NUMBER,
                         x_err_msg       OUT VARCHAR2,
                         x_return_status OUT VARCHAR2,
                         x_msg_count     OUT NOCOPY NUMBER,
                         x_msg_data      OUT NOCOPY VARCHAR2) is
    l_api_name       CONSTANT VARCHAR2(30) := 'extract_blob';
    l_api_version    CONSTANT NUMBER := 1.0;
    l_savepoint_name CONSTANT VARCHAR2(30) := 'sp_extract_blob01';

    l_data_b      BLOB := NULL;
    l_data_c      CLOB := NULL;
    l_pos         INTEGER;
    l_offset      INTEGER;
    l_clob_size   INTEGER;
    l_line_no     INTEGER;
    l_src_offset  INTEGER := 1;
    l_dest_offset INTEGER := 1;
    l_buf         VARCHAR2(4000);
    l_warning     VARCHAR2(4000);
    l_ist_count   NUMBER;
    l_lang_ctx    INTEGER := dbms_lob.default_lang_ctx;

  BEGIN
    g_currency      := p_currency;
    g_dr_ccid       := p_dr_ccid;
    x_return_status := xxaci_api.start_activity(p_pkg_name      => g_pkg_name,
                                                p_api_name      => l_api_name,
                                                p_init_msg_list => fnd_api.g_true);

    IF x_return_status = fnd_api.g_ret_sts_error THEN
      RAISE fnd_api.g_exc_error;
    ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
      RAISE fnd_api.g_exc_unexpected_error;
    END IF;

    --g_group_id:=x_group_id;

    dbms_lob.createtemporary(l_data_c, FALSE, dbms_lob.session);

    --get CSV file ,save into l_data_b
    IF p_file_id IS NOT NULL THEN
      SELECT fl.file_data
        INTO l_data_b
        FROM fnd_lobs fl
       WHERE fl.file_id = p_file_id
         FOR UPDATE OF file_data;

      dbms_output.put_line(dbms_lob.getlength(l_data_b));

      --check the character set
      IF p_src_cs <> g_character_set THEN
        l_data_b := convertblob(l_data_b, p_src_cs, g_character_set);
      END IF;

      dbms_output.put_line('test');

      -- Convert the BLOB format to CLOB format
      dbms_lob.converttoclob(dest_lob     => l_data_c,
                             src_blob     => l_data_b,
                             amount       => dbms_lob.lobmaxsize,
                             dest_offset  => l_dest_offset,
                             src_offset   => l_src_offset,
                             blob_csid    => nls_charset_id(g_character_set),
                             lang_context => l_lang_ctx,
                             warning      => l_warning);

      dbms_output.put_line(dbms_lob.getlength(l_data_c));

      l_offset    := 1;
      l_clob_size := dbms_lob.getlength(l_data_c);
      l_line_no   := 1;

      SELECT xxaci_mrp_forecast_temp_s.NEXTVAL INTO g_group_id FROM dual;

      x_group_id := g_group_id;

      LOOP
        l_pos := dbms_lob.instr(lob_loc => l_data_c,
                                pattern => chr(10),
                                offset  => l_offset,
                                nth     => 1);
        dbms_output.put_line(to_char(l_pos));
        IF nvl(l_pos, 0) = 0 THEN
          l_pos := l_clob_size + 1;
        END IF;

        l_buf    := dbms_lob.substr(lob_loc => l_data_c,
                                    amount  => l_pos - l_offset, -- N_NEXT_POS - N_POS,
                                    offset  => l_offset); --N_POS+1);
        l_offset := l_pos + 1;

        --break down the fields into different columns by the Tab Delimiter
        extract_cell_data(p_line          => REPLACE(l_buf, chr(13)),
                          p_line_no       => l_line_no,
                          p_delimiter     => p_delimiter,
                          x_err_msg       => x_err_msg,
                          x_return_status => x_return_status,
                          x_msg_count     => x_msg_count,
                          x_msg_data      => x_msg_data);

        EXIT WHEN l_pos > l_clob_size;
        l_line_no := l_line_no + 1;
      END LOOP;

      COMMIT;

      SELECT COUNT(1)
        INTO l_ist_count
        FROM xxaci_mrp_forecast_temp mft
       WHERE mft.group_id = g_group_id;

      IF dbms_lob.istemporary(l_data_b) > 0 THEN
        dbms_lob.freetemporary(l_data_b);
      END IF;

      IF dbms_lob.istemporary(l_data_c) > 0 THEN
        dbms_lob.freetemporary(l_data_c);
      END IF;
    END IF;

    x_err_msg := to_char(l_line_no - 1) || ' records in data file,' ||
                 l_ist_count || ' processed successfully' || chr(10) ||
                 x_err_msg;

    dbms_output.put_line('x_err_msg:' || x_err_msg);

    x_return_status := xxaci_api.end_activity(p_pkg_name  => g_pkg_name,
                                              p_api_name  => l_api_name,
                                              p_commit    => fnd_api.g_false,
                                              x_msg_count => x_msg_count,
                                              x_msg_data  => x_msg_data);

    dbms_output.put_line('x_msg_data:' || x_msg_data);
  EXCEPTION
    WHEN no_data_found THEN

      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_error,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);

      IF x_msg_count > 1 THEN
        x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                             p_encoded   => fnd_api.g_false);
      END IF;

    WHEN fnd_api.g_exc_error THEN
      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_error,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);

      IF x_msg_count > 1 THEN
        x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                             p_encoded   => fnd_api.g_false);
      END IF;

    WHEN fnd_api.g_exc_unexpected_error THEN
      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_unexp,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);

      IF x_msg_count > 1 THEN
        x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                             p_encoded   => fnd_api.g_false);
      END IF;

    WHEN OTHERS THEN
      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_others,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.add_exc_msg(p_pkg_name       => g_pkg_name,
                              p_procedure_name => l_api_name,
                              p_error_text     => substrb(SQLERRM, 1, 240));
      xxaci_conc_utl.log_message_list;
      x_msg_data := SQLERRM;
  END extract_blob;
  -------------------------------
  PROCEDURE extract_cell_data(p_line          IN VARCHAR2,
                              p_line_no       IN INTEGER,
                              p_delimiter     IN VARCHAR2,
                              x_err_msg       OUT VARCHAR2,
                              x_return_status OUT VARCHAR2,
                              x_msg_count     OUT NOCOPY NUMBER,
                              x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name       CONSTANT VARCHAR2(30) := 'extract_cell_data';
    l_api_version    CONSTANT NUMBER := 1.0;
    l_savepoint_name CONSTANT VARCHAR2(30) := 'sp_extract_cell_data01';

    l_line          VARCHAR2(4000);
    l_field         VARCHAR2(4000);
    l_field_cnt     INTEGER;
    l_delimiter_pos INTEGER;
    v_temp_id       number;

    v_ccid number;
    --l_cell1         NUMBER;

    /*l_project_name   VARCHAR2(100);
    l_segment1       VARCHAR2(240);
    l_uom_code       VARCHAR2(10);
    l_currency_code  VARCHAR2(3);
    l_cj_vendor_name VARCHAR2(240);
    l_vendor_name    VARCHAR2(240);
    l_primary_flag   VARCHAR2(1);
    l_unit_price     NUMBER;*/

  BEGIN
    x_return_status := xxaci_api.start_activity(p_pkg_name      => g_pkg_name,
                                                p_api_name      => l_api_name,
                                                p_init_msg_list => fnd_api.g_true);

    IF x_return_status = fnd_api.g_ret_sts_error THEN
      RAISE fnd_api.g_exc_error;
    ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
      RAISE fnd_api.g_exc_unexpected_error;
    END IF;

    g_cells := g_extract_tbl();
    g_cells.EXTEND(g_max_field);
    l_field_cnt := 1;

    IF p_line IS NOT NULL THEN
      -- extract values from field
      l_line := p_line;
      dbms_output.put_line(l_line);
      LOOP

        l_field         := NULL;
        l_delimiter_pos := instr(l_line, p_delimiter);
        IF l_delimiter_pos > 0 THEN
          l_field := ltrim(rtrim(substr(l_line, 1, l_delimiter_pos - 1)));
          l_line  := substr(l_line, l_delimiter_pos + 1);
        ELSIF ltrim(rtrim(l_line)) IS NOT NULL THEN
          l_field := ltrim(rtrim(l_line));
          l_line  := NULL;
        END IF;

        dbms_output.put_line(to_char(l_field_cnt) || '-' || l_field);
        --IF l_field IS NOT NULL THEN
        dbms_output.put_line(l_field);
        g_cells(l_field_cnt) := l_field;
        --END IF;
        l_field_cnt := l_field_cnt + 1;
        -- exit when finish extract last field
        IF (l_line IS NULL) OR (l_field_cnt = g_max_field) THEN
          EXIT;
        END IF;
      END LOOP;

      dbms_output.put_line(g_cells.COUNT);

      BEGIN
      v_ccid:=null;
        if g_cells(5) is not null then
        begin
           select code_combination_id into v_ccid from
           gl_code_combinations where
           segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5||'.'||segment6=g_cells(5);
        exception
        when others then v_ccid:=null;
        end;
        end if;
        SELECT XXCUS_ATU.XXACI_AP_IMPORT_S.NEXTVAL
          into v_temp_id
          FROM DUAL;
        INSERT INTO xxaci_ap_import_tmp
          (vendor_num,
           header_description,
           line_amount,
           line_description,
           distribution_ccid,
           group_id,
           invoice_currency_code,
           temp_id)
        VALUES
          (g_cells(1),
           g_cells(2),
           g_cells(3),
           g_cells(4),
           nvl(v_ccid, g_dr_ccid),
           g_group_id,
           g_currency,
           v_temp_id);
        commit;
      EXCEPTION
        WHEN OTHERS THEN
          x_err_msg := substrb(SQLERRM, 1, 240);
      END;
    END IF;

    x_return_status := xxaci_api.end_activity(p_pkg_name  => g_pkg_name,
                                              p_api_name  => l_api_name,
                                              p_commit    => fnd_api.g_false,
                                              x_msg_count => x_msg_count,
                                              x_msg_data  => x_msg_data);
  EXCEPTION
    WHEN no_data_found THEN

      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_error,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);

      IF x_msg_count > 1 THEN
        x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                             p_encoded   => fnd_api.g_false);
      END IF;

    WHEN fnd_api.g_exc_error THEN
      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_error,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);

      IF x_msg_count > 1 THEN
        x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                             p_encoded   => fnd_api.g_false);
      END IF;

    WHEN fnd_api.g_exc_unexpected_error THEN
      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_unexp,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);

      IF x_msg_count > 1 THEN
        x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                             p_encoded   => fnd_api.g_false);
      END IF;

    WHEN OTHERS THEN
      x_return_status := xxaci_api.handle_exceptions(p_pkg_name  => g_pkg_name,
                                                     p_api_name  => l_api_name,
                                                     p_exc_name  => xxaci_api.g_exc_name_others,
                                                     x_msg_count => x_msg_count,
                                                     x_msg_data  => x_msg_data);
      fnd_msg_pub.add_exc_msg(p_pkg_name       => g_pkg_name,
                              p_procedure_name => l_api_name,
                              p_error_text     => substrb(SQLERRM, 1, 240));
      xxaci_conc_utl.log_message_list;
      x_msg_data := SQLERRM;
  END extract_cell_data;