如何避免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 
VARCHAR2is
    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, 1240));
      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 
VARCHAR2IS
    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 NULLOR (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(5is 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, 1240);
      
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, 1240));
      xxaci_conc_utl.log_message_list;
      x_msg_data :
= SQLERRM;
  
END extract_cell_data;
复制代码
posted @ 2012-06-04 15:22  刘伟聪  阅读(342)  评论(0编辑  收藏  举报