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;