Import Excel data to Oracle ERP Form

-- 建立在 FORM 中


--1.
PACKAGE FILE_INFO IS
  procedure import_file(v_block varchar2
                      , v_field varchar2
                      , v_col_separate varchar2
                      , v_row_separate varchar2);
  function  open_file return number;
END;




--2
PACKAGE BODY FILE_INFO IS
   /*
 用 途 : 已 Web方 式 開 啟 檔 案

 參 數 : 無
 
  傳 回 值 : >1(選 擇 檔 案 且 Upgrade)
          0(選 擇 檔 案 但 未 Upgrade)
         -1(未 選 擇 檔 案 )
 
  */
  function  open_file return number is
    access_id     NUMBER;
    l_server_url  VARCHAR2(255);
    l_url         VARCHAR2(255);
    l_gfm_id      NUMBER;
    button_choice NUMBER;
  begin
    -- Get Process ID
    access_id := fnd_gfm.authorize(null);
   
    -- Get Web Server Address
    fnd_profile.get('APPS_WEB_AGENT',l_server_url);
   
    -- Call Web Page
    l_url := RTRIM(l_server_url, '/') ||
              '/fnd_file_upload.displayGFMform?access_id=' ||
              to_char(access_id) || chr(38) || 'l_server_url=' ||
              l_server_url;
    
     IF (l_url IS NULL) THEN
        raise form_trigger_failure;
     END IF;
    
     -- Open Web Page
     fnd_utilities.open_url(l_url);
    
     -- this action is waiting for user select file, and check upgrade
     fnd_message.set_name('FND','ATCHMT-FILE-UPLOAD-COMPLETE');
    
     button_choice := FND_MESSAGE.question(
                                  button1 => 'YES',
                                  button2 => null,
                                  button3 => 'NO',
                                  default_btn => 1,
                                  cancel_btn => 3,
                                  icon => 'question');
    
     -- Get File ID
     l_gfm_id := fnd_gfm.get_file_id(access_id);
    
     if l_gfm_id is not null and l_gfm_id > 0 then
        if button_choice = 1 then
          return l_gfm_id; -- File is selected and upgrade
        else
          return 0;        -- File is selected but not upgrade
        end if;
     else
        return -1;          -- File is not selected
     end if;  
  end open_file;
------------------------------------------------------------------------ 
  /*
 用 途 : 接 收 外 部 檔 案 資 料

 用 法 : import_file( 'block', 'a,b,c', '<Tab>', '<Enter>' ) -> multi-row by <enter> and multi-field by <tab>
       import_file( 'block', 'a,b,c', ',',     '<Enter>' ) -> multi-row by <enter> and multi-field by comma
       import_file( 'block', 'a,b,c', 'null',  '<Enter>' ) -> multi-row by <enter> and one-field
       import_file( 'block', 'a',     'null',  ','       ) -> multi-row by comma   and one field
       import_file( 'block', 'a',     'null',  'null'    ) -> one-row              and one field
      
 參 數 : v_block        > 接 收 資 料 的 Block
       v_field        > 接 收 資 料 的 Field, 若 不 只 一 個 , 以 逗 號 隔 開
     v_col_separate > 隔 開 資 料 的 符 號 , 也 可 用 <Tab>
     v_row_separate > 區 分 一 筆 一 筆 資 料 的 符 號 , 也 可 用 <Enter>
 
  傳 回 值 : 無

  */
  procedure import_file(v_block varchar2, v_field varchar2, v_col_separate varchar2, v_row_separate varchar2) is
    l_gfm_id       NUMBER;
    v_file         varchar2(100);
   
    v_row_sep      varchar2(1);
    v_col_sep      varchar2(1);
    v_comma_exist  boolean := false;
   
    my_field       dbms_utility.uncl_array;
    my_field_cnt   binary_integer;
    my_field_loc   binary_integer := 1;
       
    i              NUMBER;
    file_len       NUMBER;
    file_loc       NUMBER;  -- file location
    temp_data      varchar2(1000);
    temp_tab       varchar2(1000);
    enter_loc      NUMBER;  -- enter_key location on temp buffer
    tab_loc        NUMBER;  -- tab_key location on temp buffer
    tab_loc_old    NUMBER;  -- tab_key location on temp buffer
  begin
    -- Sperate v_field to my_field( ex: 'x,y,z' -> 'x', 'y', 'z')
    dbms_utility.comma_to_table( v_field, my_field_cnt, my_field );
   
    for i in 1 .. my_field_cnt loop
        -- Check item is exist ?
        declare
           item_id item;
        begin
           item_id := Find_Item( v_block||'.'||ltrim(rtrim(my_field(i))) );
          
           if id_null(item_id) then
             fnd_message.debug( 'Item : ' || v_block||'.'||my_field(i) || ' is not exist !!' );
             raise form_trigger_failure;
           end if;
        end;
    end loop;
   
     if upper(v_col_separate) = '<TAB>' then
        v_col_sep := chr(09);
     elsif upper(v_col_separate) <> 'NULL' then
        v_col_sep := v_col_separate;
     else
        v_col_sep := chr(0);
     end if;
   
    -- Get Row Separate char
     if upper(v_row_separate) = '<ENTER>' then
        v_row_sep := chr(13);
     elsif upper(v_row_separate) <> 'NULL' then
        v_row_sep := v_row_separate;
     else
        v_row_sep := chr(0);
     end if;
    ------------------------
   
    -- Open File    
     l_gfm_id := open_file;
        
     if l_gfm_id > 0 then
        -- Get File Length
        select dbms_lob.getlength(file_data) 
          into file_len
          from fnd_lobs
         where file_id = l_gfm_id;
        
        go_block ( v_block );
         
        -- Select Data
        file_loc := 1;
        enter_loc := 1;
        /*select dbms_lob.instr(file_data,utl_raw.cast_to_raw(v_row_sep),file_loc,1) + 2
            into file_loc
            from fnd_lobs
           where file_id = l_gfm_id; */
          
       
        ---Added at 2003/11/12
        Loop
         -- Find Enter Key Location
         select dbms_lob.instr(file_data,utl_raw.cast_to_raw(v_row_sep),file_loc,1)
            into enter_loc
            from fnd_lobs
           where file_id = l_gfm_id;
         
         
         if enter_loc > 0 then
       
        --  if :CONTROL.LANGUAGE = 1 then
            select ltrim(rtrim(utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(file_data,enter_loc-file_loc,file_loc),'AMERICAN_AMERICA.UTF8','AMERICAN_AMERICA.ZHT16BIG5'))))
               into temp_data
              from fnd_lobs
             where file_id = l_gfm_id;
         /* elsif :CONTROL.LANGUAGE = 2 then
           select utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(file_data,enter_loc-file_loc,file_loc),'AMERICAN_AMERICA.UTF8','AMERICAN_AMERICA.ZHS16GBK'))
               into temp_data
              from fnd_lobs
             where file_id = l_gfm_id; */
         /* else
           select ltrim(rtrim(utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,enter_loc-file_loc,file_loc))))
               into temp_data
              from fnd_lobs
             where file_id = l_gfm_id;
           end if;*/
       
            file_loc := enter_loc + 2;
             enter_loc := enter_loc + 2;
         else
         --  if :CONTROL.LANGUAGE = 1 then
           select ltrim(rtrim(utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(file_data,1000,file_loc),'AMERICAN_AMERICA.UTF8','AMERICAN_AMERICA.ZHT16BIG5'))))
               into temp_data
              from fnd_lobs
             where file_id = l_gfm_id;
          /* elsif :CONTROL.LANGUAGE = 2 then
            select utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(file_data,1000,file_loc),'AMERICAN_AMERICA.UTF8','AMERICAN_AMERICA.ZHS16GBK'))
               into temp_data
              from fnd_lobs
             where file_id = l_gfm_id; */
         /*  else
            select ltrim(rtrim(utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,1000,file_loc))))
               into temp_data
              from fnd_lobs
             where file_id = l_gfm_id;
           end if;*/
       
            file_loc := file_len + 2;
             enter_loc := file_len + 2;
         end if;
                            
          tab_loc := 1;
          tab_loc_old := 1;
          my_field_loc := 1;
          WHILE (my_field_loc <= my_field_cnt and tab_loc > 0) Loop
            tab_loc := instr(temp_data, v_col_sep, tab_loc_old);
         
           if tab_loc > 0 then
              temp_tab := substr(temp_data, tab_loc_old, tab_loc - tab_loc_old);
            else
              temp_tab := substr(temp_data, tab_loc_old);
           end if;
           tab_loc_old := tab_loc + 1;
         
            copy( temp_tab, v_block || '.' || my_field(my_field_loc) );
           my_field_loc := my_field_loc + 1; 
          END Loop;
           
          Next_Record;
          Exit When file_loc >= file_len;
        End Loop;
          
        first_record;
     end if;
     ----------------
    -- Delete temp data
     if l_gfm_id >= 0 then
        copy( '5', 'system.message_level' );
        delete from fnd_lobs where file_id = l_gfm_id;
        commit;
        copy( '0', 'system.message_level' );
     end if;
  end import_file;
END;






--3. 使用
GO_BLOCK('SERIAL_ENTRY');
/* 將檔案上傳並將資料顯示於所對應之欄位內 */
File_Info.Import_File('SERIAL_ENTRY', 'FM_SERIAL_NUMBER,TO_SERIAL_NUMBER', ',', '<Enter>' );