GET_DATA_FM_DB

FUNCTION GET_DATA_FM_DB RETURN BOOLEAN is x_blob blob; x_clob clob; x_line_data varchar2(3000); x_line_total_num number; x_end_char varchar2(30):=chr('10'); --分行符號 x_separator varchar2(10) :=chr('9'); -- TAB x_start_line_num number:=2; --excel上開始的行數 x_substitute_count number; x_name_in varchar2(100); --x_item_count NUMBER; --x_lot_number varchar2(30); --x_revision varchar2(10); --x_serial_nmumber varchar2(30); --x_subinventory_name varchar2(30); --x_locator_count number; --x_inventory_item_id number; --X_ADJ_QTY number; --v-_locator_count number; v_item_count number; v_category_code varchar2(20); v_count number; v_count_routing number; BEGIN go_block('IMPORT_DATA'); first_record; APPS.BlobToClob (:control.file_id); --Modify by Carol_Her 20140122 Oracle 11g不支援D2K使用clob，改以Function取得匯入資料。 /* begin select FILE_DATA into x_clob from KSMM.KSMM_UPLOAD_DATA_GT where file_id=:control.file_id; exception when others then fnd_message.debug('找不到CLOB檔'); RAISE FORM_TRIGGER_FAILURE; END; */ begin --Modify by Carol_Her 20140122 Oracle 11g不支援D2K使用clob，改以Function取得匯入資料。 --Select get_clob_line_num (x_clob,x_end_char),fl.FILE_NAME Select get_clob_line_num(get_clob_function(:control.file_id),x_end_char),fl.FILE_NAME into x_line_total_num,:control.file_name From fnd_lobs fl Where fl.file_id =:control.file_id ; exception when others then :control.file_name:=''; end; --從第x_start_line_num行開始取資料 for i in x_start_line_num .. x_line_total_num loop --取出資料，將值指定給x_line_data begin --Modify by Carol_Her 20140122 Oracle 11g不支援D2K使用clob，改以Function取得匯入資料。 --select get_clob_line_data(x_clob,i,x_end_char) select get_clob_line_data(get_clob_function(:control.file_id),i,x_end_char) into x_line_data from dual; exception when others then fnd_message.debug(SQLERRM); end ; :import_data.line_num:=i-(x_start_line_num-1); -- read data start --x_name_in:='assembly_item'; --copy(get_column_data(x_line_data,1,x_separator), ':import_data.'||x_name_in); :import_data.year:=get_column_data(x_line_data,1,x_separator); :import_data.seq_no:=get_column_data(x_line_data,2,x_separator); :import_data.prod_type:=get_column_data(x_line_data,3,x_separator); :import_data.item_h:=get_column_data(x_line_data,4,x_separator); :import_data.q1_Yield:=get_column_data(x_line_data,5,x_separator); :import_data.q2_Yield:=get_column_data(x_line_data,6,x_separator); :import_data.q3_Yield:=get_column_data(x_line_data,7,x_separator); :import_data.q4_Yield:=get_column_data(x_line_data,8,x_separator); /*:import_data.work_time:=nvl(round(get_column_data(x_line_data,7,x_separator),2),0); :import_data.depreciation:=nvl(round(get_column_data(x_line_data,8,x_separator),2),0); :import_data.material:=nvl(round(get_column_data(x_line_data,9,x_separator),2),0); :import_data.make_expen:=nvl(round(get_column_data(x_line_data,10,x_separator),2),0); :import_data.bom:=nvl(round(get_column_data(x_line_data,11,x_separator),2),0); :import_data.top_item_no:=get_column_data(x_line_data,12,x_separator); :import_data.top_revision:=get_column_data(x_line_data,13,x_separator); */ -- read data end --如果到了最後一行，將資料刪除 if i = x_line_total_num then delete fnd_lobs fl Where fl.file_id =:control.file_id ; forms_ddl('COMMIT'); else next_record; end if; end loop; return (true); exception when others then -- fnd_message.debug('異常停止'); delete fnd_lobs fl Where fl.file_id =:control.file_id ; forms_ddl('COMMIT'); return (true); END;

upload csv to from - find file

--WEB.SHOW_DOCUMENT('http://XXX.html', '_blank'); Declare access_id Number; button_choice Integer; l_file_id Varchar2(100); l_gfm_id Integer; l_server_url VARCHAR2(255); l_url VARCHAR2(255); import boolean ; Begin --fnd_message.debug(:parameter.org_id); :control.file_id:=''; :control.ERROR_MESSAGE:=''; :control.file_name:=''; access_id := fnd_gfm.authorize(Null); fnd_profile.get('APPS_WEB_AGENT',l_server_url); 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; RETURN; END IF; fnd_utilities.open_url(l_url); 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'); If (button_choice = 3) Then Null; Elsif (button_choice = 1) Then -- clear_block(no_commit); --先將control block 清空 l_gfm_id := fnd_gfm.get_file_id(access_id); :control.file_id:=l_gfm_id; if :control.file_id>0 then -- import_action start go_block('IMPORT_DATA'); clear_block(no_commit); /* select INV.KSINV_PHYSICAL_TAG_DATA_S.NEXTVAL INTO :CONTROL.HEADER_ID FROM DUAL;*/ import :=get_data_fm_db; -- import_action end else fnd_message.debug('沒找到對應檔案'); end if; End If; End;