《2022年csv文件上传数据库[] .pdf》由会员分享，可在线阅读，更多相关《2022年csv文件上传数据库[] .pdf（23页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、上传界面：选择按钮：declare access_id number; l_server_url varchar2(100); l_parameters varchar2(100); button_choice integer; l_file_id varchar2(100); l_gfm_id integer; BEGIN access_id := fnd_gfm.authorize(null); fnd_profile.get(APPS_WEB_AGENT, l_server_url); l_parameters :=access_id=|access_id| l_server_url=|
2、l_server_url; fnd_function.execute(FUNCTION_NAME=FND_FNDFLUPL, OPEN_FLAG = Y, SESSION_FLAG = Y, OTHER_PARAMS =l_parameters); - Display a modal message for user to indicate file upload - is completed. FND_MESSAGE.set_name(FND,ATCHMT-FILE-UPLOAD-COMPLETE); button_choice := FND_MESSAGE.question( 名师资料总结
3、 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 23 页 - - - - - - - - - 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 l_file_id := ; l_gfm_id := fnd_gfm.ge
4、t_file_id(access_id); :MAIN.GFM_ID := fnd_gfm.get_file_id(access_id); IF l_gfm_id IS NOT NULL THEN select decode(instr(file_name,/),0,file_name, substr(file_name,instr(file_name,/)+1) into l_file_id from fnd_lobs where file_id = l_gfm_id; if l_file_id is not null then :main.filename := l_file_id; se
5、lect CUX_CE_STMT_S.nextval into :main.working_id from dual; end if; END IF; END IF; END; 上传按钮：DECLARE REQID NUMBER := 0; l_exit BOOLEAN; BEGIN 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 23 页 - - - - - - - - - IF :MAIN.FILENAME IS NULL THEN FND_MESSAGE.SET_S
6、TRING(请选择要装入的数据文件。 ); FND_MESSAGE.SHOW; ELSE -set cursor busy- Set_Application_Property(CURSOR_STYLE, BUSY) ; SAVEPOINT sp_match_one_day ; REQID := FND_REQUEST.submit_request(CE, CUX_STMT_UPLOAD, NULL, NULL, FALSE, :MAIN.statement_number, :MAIN.bank_account_num, -TO_CHAR(:MAIN.statement_date, YYYY-M
7、M-DD), :MAIN.statement_date, :MAIN.bank_name, :MAIN.bank_branch_name, :MAIN.CURRENCY_CODE, CUX_STMT_UPLOAD,:MAIN.GFM_ID, :main.file_cs, :main.DELIMITER, :main.working_id, CHR(0),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,
8、NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 名师资料总结 - - -精品资料欢迎下载 - -
9、 - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 23 页 - - - - - - - - - NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ); IF REQID 0 THEN l_exit := APP_FORM.quietcommit(); FND_MESSAGE.SET_STRING(Request ID: | REQID | 请求已提交， 请查看并发程序。); FND_MESSAGE.SHOW; ELSE ROLLBACK TO sp_match_one_d
10、ay; - FND_MESSAGE.SET_STRING(提交请求失败！); FND_MESSAGE.ERROR; END IF; :MAIN.FILENAME := NULL; - FND_FUNCTION.EXECUTE( FUNCTION_NAME=FND_FNDRSRUN, OPEN_FLAG=Y, SESSION_FLAG=Y, OTHER_PARAMS=CONCURRENT_PROGRAM_NAME=|CUX_STMT_UPLOAD| PROGRAM_APPL_SHORT_NAME=|CE| DODT_REQ_ID=| TO_CHAR(REQID)| ); - Set_Applic
11、ation_Property(CURSOR_STYLE, DEFAULT); END IF; END; 其中： CUX_STMT_UPLOAD，调用的是cux_ce_stmt_upload.main CREATE OR REPLACE PACKAGE cux_ce_stmt_upload IS - Author : Siman he - Created : 2006-3-16 - Purpose : Import HR Absence Attendance Data Into ERP Environment gp_working_id NUMBER; gp_org_id NUMBER; 名师资
12、料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 23 页 - - - - - - - - - gp_user_id NUMBER; gp_statement_number VARCHAR2(100); gp_bank_account_num VARCHAR2(100); -gp_statement_date VARCHAR2(100); gp_statement_date DATE; gp_bank_name VARCHAR2(100); gp_bank_branch_name
13、 VARCHAR2(100); gp_currency_code VARCHAR2(100); PROCEDURE log(i_chr_message IN V ARCHAR2); PROCEDURE output(i_chr_message IN VARCHAR2); FUNCTION convertblob(inblob IN BLOB, sourcecs IN VARCHAR2, destcs IN VARCHAR2) RETURN BLOB AS LANGUAGE JAVA NAME ConvertLOBCS.convertBlob( oracle.sql.BLOB, java.lan
14、g.String, java.lang.String) return oracle.sql.BLOB; FUNCTION convertclob(inblob IN BLOB, sourcecs IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME ConvertLOBCS.convertClob( oracle.sql.BLOB, java.lang.String ) return oracle.sql.CLOB; FUNCTION convertstr(inblob IN BLOB, sourcecs IN VARCHAR2) RETURN
15、VARCHAR2 AS LANGUAGE JA VA NAME ConvertLOBCS.convertStr( oracle.sql.BLOB, java.lang.String ) return java.lang.String; PROCEDURE extract_blob(p_file_id IN NUMBER, p_src_cs IN VARCHAR2, p_delimiter IN VARCHAR2, p_working_id IN NUMBER); PROCEDURE ins_interface; PROCEDURE main(errbuf OUT V ARCHAR2, retc
16、ode OUT V ARCHAR2, p_statement_number IN VARCHAR2, p_bank_account_num IN VARCHAR2, p_statement_date IN VARCHAR2, -p_statement_date IN DATE, p_bank_name IN VARCHAR2, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 23 页 - - - - - - - - - p_bank_branch_name IN VARC
17、HAR2, p_currency_code IN VARCHAR2, p_area IN VARCHAR2, p_gfm_id IN NUMBER, p_file_cs IN VARCHAR2, p_delimiter IN NUMBER, p_working_id IN NUMBER); END cux_ce_stmt_upload; / CREATE OR REPLACE PACKAGE BODY cux_ce_stmt_upload IS /*= Copyright (C) Hand Business Consulting Services AllRights Reserved $ He
18、ader Huawei , Wanglin * Version 1.0 * Purpose : * Batch Import Nonrecurring and Recurring data to Oracle ERP by * Fixed length or Excel * Package : cux_ce_stmt_upload * Procedure : 1.main Manual batch Import Employee payroll element information to Oracle ERP * Create By : Wanglin * Create Date: 2006
19、-3-21 12:50:23 * Purpose : 1. Supply Import API for user to Import Employee payroll element information to Oracle ERP , Application for Notes system 2. Manual Batch Import Employee payroll element information to Oracle ERP Application for Manual Excel Data * Updated By : Wanglin * Updated Date: 2006
20、-5-14 * Purpose : 1. Add Batch Update Recurring and Nonrecurring Pay Value =*/ cn_max_field CONSTANT INTEGER := 40; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 23 页 - - - - - - - - - cv_processing_type VARCHAR2(3); cv_dest_cs CONSTANT V ARCHAR2(30) := UTF8;
21、cv_commit_number CONSTANT NUMBER := 5000; TYPE t_matrix_cell IS TABLE OF VARCHAR2(500); PROCEDURE log(i_chr_message IN VARCHAR2) IS BEGIN fnd_file.put_line(fnd_file.log, i_chr_message); NULL; END; PROCEDURE output(i_chr_message IN VARCHAR2) AS BEGIN fnd_file.put_line(fnd_file.output, i_chr_message);
22、 END; PROCEDURE alter_nls_date(p_date_string IN V ARCHAR2) IS l_cursor_id INTEGER; l_dummy INTEGER; l_string VARCHAR2(128); BEGIN l_string := ALTER session set NLS_DATE_LANGUAGE= | p_date_string; l_cursor_id := dbms_sql.open_cursor; dbms_sql.parse(l_cursor_id, l_string, dbms_sql.native); l_dummy :=
23、dbms_sql.EXECUTE(l_cursor_id); END alter_nls_date; - - Check effective date whether is validation - PROCEDURE check_date(p_effective_date IN V ARCHAR2, o_errcode OUT NUMBER, o_errmsg OUT VARCHAR2) IS v_date DATE; BEGIN o_errcode := 0; SELECT to_date(p_effective_date, YYYY-MM-DD) INTO v_date FROM dua
24、l; EXCEPTION WHEN OTHERS THEN o_errcode := -20001; o_errmsg := 时间格式出错,导入失败 ; END; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 23 页 - - - - - - - - - PROCEDURE extract_cell_data(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_working_id IN NUMBER) IS v_line VA
25、RCHAR2(4000); v_field VARCHAR2(4000); r_cells t_matrix_cell; n_field_cnt INTEGER; n_delimiter_pos INTEGER; BEGIN r_cells := t_matrix_cell(); r_cells.EXTEND(cn_max_field); n_field_cnt := 1; IF p_line IS NOT NULL THEN - extract values from field v_line := p_line; LOOP v_field := NULL; n_delimiter_pos
26、:= instr(v_line, p_delimiter); IF n_delimiter_pos 0 THEN v_field := ltrim(rtrim(substr(v_line, 1, n_delimiter_pos - 1); v_line := substr(v_line, n_delimiter_pos + 1); ELSIF ltrim(rtrim(v_line) IS NOT NULL THEN v_field := ltrim(rtrim(v_line); v_line := NULL; END IF; -output(to_char(n_field_cnt) | - |
27、 v_field); IF v_field IS NOT NULL THEN r_cells(n_field_cnt) := v_field; END IF; n_field_cnt := n_field_cnt + 1; - exit when finish extract last field IF (v_line IS NULL) OR (n_field_cnt = cn_max_field) THEN EXIT; END IF; END LOOP; -output(r_cells.COUNT); - Insert the different column values from the
28、 file to different columns of the cux_HR_MA TRIX_CELLS TABLE /* INSERT INTO cux_ce_stmt_temp 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 23 页 - - - - - - - - - (working_id, line_number, statement_number, bank_account_num, statement_date, bank_name, bank_bran
29、ch_name, trx_date, trx_code, bank_trx_number, effective_date, trx_text, amount, currency_code, process_flag) VALUES (p_working_id, r_cells(1), r_cells(2), r_cells(3), TO_DATE(r_cells(4),YYYY-MM-DD HH24:MI:SS), r_cells(5), r_cells(6), TO_DATE(r_cells(7),YYYY-MM-DD HH24:MI:SS), r_cells(8), r_cells(9),
30、 TO_DATE(r_cells(10),YYYY-MM-DD HH24:MI:SS), r_cells(11), r_cells(12), r_cells(13), N); */ INSERT INTO cux_ce_stmt_temp (working_id, statement_number, bank_account_num, statement_date, bank_name, bank_branch_name, currency_code, line_number, trx_date, trx_code, bank_trx_number, 名师资料总结 - - -精品资料欢迎下载
31、- - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 23 页 - - - - - - - - - effective_date, trx_text, amount, process_flag) VALUES (p_working_id, gp_statement_number, gp_bank_account_num, -to_date(gp_statement_date, DD-MON-RRRR), gp_statement_date, gp_bank_name, gp_bank_branch_name, gp_c
32、urrency_code, r_cells(1), to_date(r_cells(2), YYYY-MM-DD HH24:MI:SS), r_cells(3), r_cells(4), to_date(r_cells(5), YYYY-MM-DD HH24:MI:SS), r_cells(6), r_cells(7), N); END IF; EXCEPTION WHEN OTHERS THEN RAISE; END extract_cell_data; PROCEDURE extract_blob(p_file_id IN NUMBER, p_src_cs IN VARCHAR2, p_d
33、elimiter IN VARCHAR2, p_working_id IN NUMBER) IS l_data BLOB := NULL; c_data CLOB := NULL; n_pos INTEGER; n_offset INTEGER; n_clob_size INTEGER; n_src_offset INTEGER := 1; n_dest_offset INTEGER := 1; v_buf VARCHAR2(4000); v_warn VARCHAR2(4000); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - -
34、- 名师精心整理 - - - - - - - 第 10 页，共 23 页 - - - - - - - - - n_lang_ctx INTEGER := dbms_lob.default_lang_ctx; BEGIN dbms_lob.createtemporary(c_data, FALSE, dbms_lob.session); - 获取大字段文件,存放导入的文件(L_DATA) IF p_file_id IS NOT NULL THEN SELECT fl.file_data INTO l_data FROM fnd_lobs fl WHERE fl.file_id = p_file_
35、id FOR UPDATE OF file_data; dbms_output.put_line(dbms_lob.getlength(l_data); - 判定当前字符集类型是否UTF8 IF p_src_cs cv_dest_cs THEN l_data := convertblob(l_data, p_src_cs, cv_dest_cs); END IF; - Convert the BLOB format to CLOB format dbms_lob.converttoclob(dest_lob = c_data, src_blob = l_data, amount = dbms_
36、lob.lobmaxsize, dest_offset = n_dest_offset, src_offset = n_src_offset, blob_csid = nls_charset_id(cv_dest_cs), lang_context = n_lang_ctx, warning = v_warn); dbms_output.put_line(dbms_lob.getlength(c_data); n_offset := 1; n_clob_size := dbms_lob.getlength(c_data); LOOP n_pos := dbms_lob.instr(lob_lo
37、c = c_data, pattern = chr(10), offset = n_offset, nth = 1); -output(to_char(n_pos); IF nvl(n_pos, 0) = 0 THEN n_pos := n_clob_size + 1; END IF; v_buf := dbms_lob.substr(lob_loc = c_data, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 23 页 - - - - - - - - - amo
38、unt = n_pos - n_offset, - N_NEXT_POS - N_POS, offset = n_offset); -N_POS+1); n_offset := n_pos + 1; - break down the fields into different columns by the Tab Delimiter extract_cell_data(REPLACE(v_buf, chr(13), p_delimiter, p_working_id); EXIT WHEN n_pos n_clob_size; END LOOP; COMMIT; IF dbms_lob.ist
39、emporary(l_data) 0 THEN dbms_lob.freetemporary(l_data); END IF; IF dbms_lob.istemporary(c_data) 0 THEN dbms_lob.freetemporary(c_data); END IF; END IF; EXCEPTION WHEN OTHERS THEN RAISE; END extract_blob; - - Name : import - Description : This script populates the Standard PO Vendor tables - taking th
40、e data from temp table cdc_ap_vendor_intrim - where data file for data conversion is loaded - - The Script populates data into 4 Tables : - CUX_CE_BANK_STATEMENT_TEMP - CE_STATEMENT_HEADERS_INT_ALL - CE_STATEMENT_LINES_INTERFACE - - Modification Log: - Author Date Version Changes 名师资料总结 - - -精品资料欢迎下
41、载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 23 页 - - - - - - - - - - - - - - - Wanglin 20060414 1.0 Created - - PROCEDURE ins_interface IS v_insert_or_update VARCHAR2(10); - 处理标志 : i - 插入u - 修改p_error_flag VARCHAR2(1); p_error_info V ARCHAR2(200); v_line_all NUMBER; v_line_su
42、c NUMBER; v_line_fal NUMBER; v_state_cnt NUMBER := 0; l_line_cnt NUMBER := 0; l_int_cnt NUMBER := 0; exc_stmt EXCEPTION; CURSOR cur_stmt_header IS SELECT DISTINCT t.working_id, t.statement_number, t.bank_account_num, t.bank_name, t.bank_branch_name, t.statement_date, t.currency_code FROM cux_ce_stmt
43、_temp t WHERE (process_flag != S OR process_flag IS NULL) AND t.working_id = gp_working_id ORDER BY t.statement_number; r_stmt_header cur_stmt_header%ROWTYPE; CURSOR cur_stmt_line(p_statement_number IN VARCHAR2, p_bank_account_num IN VARCHAR2, p_bank_name IN VARCHAR2, p_bank_branch_name IN VARCHAR2,
44、 p_statement_date IN DATE, p_currency_code IN V ARCHAR2) IS SELECT * FROM cux_ce_stmt_temp t WHERE (process_flag != S OR process_flag IS NULL) AND t.working_id = gp_working_id 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 23 页 - - - - - - - - - AND t.statemen
45、t_number = p_statement_number AND t.bank_account_num = p_bank_account_num AND t.bank_name = p_bank_name AND t.bank_branch_name = p_bank_branch_name AND t.statement_date = p_statement_date AND t.currency_code = p_currency_code ORDER BY line_number; r_stmt_line cur_stmt_line%ROWTYPE; BEGIN output(银行对帐
46、单数据导入开始于: | to_char(SYSDATE, YYYY/MM/DD HH24:MI:SS); output( ); v_line_all := 0; v_line_suc := 0; v_line_fal := 0; v_insert_or_update := U; - 记录本次处理的总数BEGIN SELECT COUNT(*) INTO v_line_all FROM cux_ce_stmt_temp WHERE (process_flag != S OR process_flag IS NULL); END; OPEN cur_stmt_header; LOOP FETCH
47、cur_stmt_header INTO r_stmt_header; EXIT WHEN cur_stmt_header%NOTFOUND; BEGIN SELECT COUNT(statement_number) INTO v_state_cnt FROM ce_statement_headers_int_all WHERE statement_number = r_stmt_header.statement_number AND bank_account_num = r_stmt_header.bank_account_num AND org_id = gp_org_id 名师资料总结
48、- - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 23 页 - - - - - - - - - ; IF v_state_cnt 0 THEN v_insert_or_update := U; ELSE v_insert_or_update := I; END IF; END; IF v_insert_or_update = I THEN BEGIN - insert ce_statement_headers_int_all INSERT INTO ce_statement_heade
49、rs_int_all (statement_number, bank_account_num, bank_name, bank_branch_name, statement_date, currency_code, record_status_flag, intra_day_flag, org_id, created_by, creation_date, last_updated_by, last_update_date) VALUES (r_stmt_header.statement_number, r_stmt_header.bank_account_num, r_stmt_header.
50、bank_name, r_stmt_header.bank_branch_name, r_stmt_header.statement_date, r_stmt_header.currency_code, N, N, gp_org_id, gp_user_id, SYSDATE, gp_user_id, SYSDATE); EXCEPTION 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 23 页 - - - - - - - - - WHEN OTHERS THEN -