csv文件上传数据库[1](3)

p_bank_branch_name IN VARCHAR2, 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 $ Header 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-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-5-14 * Purpose : 1. Add Batch Update Recurring and Nonrecurring Pay Value =============================================================================*/ cn_max_field CONSTANT INTEGER := 40; cv_processing_type VARCHAR2(3); cv_dest_cs CONSTANT VARCHAR2(30) := \'UTF8\'; 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); END; PROCEDURE alter_nls_date(p_date_string IN VARCHAR2) 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 := dbms_sql.EXECUTE(l_cursor_id); END alter_nls_date; -- -- Check effective date whether is validation -- PROCEDURE check_date(p_effective_date IN VARCHAR2, 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 dual; EXCEPTION WHEN OTHERS THEN o_errcode := -20001; o_errmsg := \'时间格式出错,导入失败\'; END; PROCEDURE extract_cell_data(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_working_id IN NUMBER) IS v_line VARCHAR2(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 := 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) || \'-\' || 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 file to different columns of the cux_HR_MATRIX_CELLS TABLE /* INSERT INTO cux_ce_stmt_temp (working_id, line_number, statement_number, bank_account_num, statement_date, bank_name, bank_branch_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), 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, 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_currency_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_delimiter 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);