csv文件上传数据库[1]

WHEN OTHERS THEN --p_error_flag := \'Y\'; p_error_info := SQLERRM || \', 对帐单[\' || r_stmt_header.statement_number || \'], 插入ce_statement_headers_int_all时出错;\'; RAISE exc_stmt; END; ELSIF v_insert_or_update = \'U\' THEN BEGIN UPDATE ce_statement_headers_int_all SET bank_name = r_stmt_header.bank_name, bank_branch_name = r_stmt_header.bank_branch_name, statement_date = r_stmt_header.statement_date, currency_code = r_stmt_header.currency_code, record_status_flag = \'N\' WHERE statement_number = r_stmt_header.statement_number AND bank_account_num = r_stmt_header.bank_account_num AND org_id = gp_org_id; EXCEPTION WHEN OTHERS THEN --p_error_flag := \'Y\'; p_error_info := SQLERRM || \', 对帐单[\' || r_stmt_header.statement_number || \'], 插入ce_statement_headers_int_all时出错;\'; RAISE exc_stmt; END; END IF; -- 开始处理银行对帐单行数据 OPEN cur_stmt_line(r_stmt_header.statement_number, r_stmt_header.bank_account_num, r_stmt_header.bank_name, r_stmt_header.bank_branch_name, r_stmt_header.statement_date, r_stmt_header.currency_code); LOOP FETCH cur_stmt_line INTO r_stmt_line; EXIT WHEN cur_stmt_line%NOTFOUND; BEGIN -- 对循环中使用的变量赋初值 --p_error_flag := \'N\'; p_error_info := \'\'; -- 默认处理方式为update --v_insert_or_update := \'U\'; -- 判断接口行表中是否存在该行 SELECT COUNT(line_number) INTO l_int_cnt FROM ce_statement_lines_interface WHERE statement_number = r_stmt_line.statement_number AND bank_account_num = r_stmt_line.bank_account_num AND line_number = to_char(r_stmt_line.line_number); IF l_int_cnt > 0 THEN --p_error_flag := \'Y\'; p_error_info := SQLERRM || \', 对帐单[\' || r_stmt_line.statement_number || \'], 行\' || r_stmt_line.line_number || \'已在接口表中存在\'; RAISE exc_stmt; END IF; -- 插入新的银行对帐单行 BEGIN INSERT INTO ce_statement_lines_interface (statement_number, bank_account_num, line_number, trx_date, bank_trx_number, effective_date, trx_code, trx_text, amount, created_by, creation_date, last_updated_by, last_update_date) VALUES (r_stmt_line.statement_number, r_stmt_line.bank_account_num, r_stmt_line.line_number, r_stmt_line.trx_date, r_stmt_line.bank_trx_number, r_stmt_line.effective_date, r_stmt_line.trx_code, to_char(r_stmt_line.line_number ) || \'-\' || r_stmt_line.trx_text, r_stmt_line.amount, gp_user_id, SYSDATE, gp_user_id, SYSDATE); EXCEPTION WHEN OTHERS THEN p_error_flag := \'Y\'; p_error_info := SQLERRM || \', 对帐单[\' || r_stmt_line.statement_number || \'], 插入ce_statement_lines_interface时出错;\'; RAISE exc_stmt; END; -- 置本条记录为处理成功 UPDATE cux_ce_stmt_temp SET process_flag = \'S\' WHERE line_number = r_stmt_line.line_number -- WHERE bank_account_num = v_bank_account_num -- AND statement_number = v_statement_number ; -- 成功记录数加1 v_line_suc := v_line_suc + 1; EXCEPTION WHEN OTHERS THEN -- 输出错误信息，回滚事务 output(p_error_info); -- 置本条记录为处理失败 UPDATE cux_ce_stmt_temp SET process_flag = \'F\' WHERE line_number = r_stmt_line.line_number; -- WHERE bank_account_num = v_bank_account_num -- AND statement_number = v_statement_number; END; END LOOP; CLOSE cur_stmt_line; END LOOP; CLOSE cur_stmt_header; COMMIT; -- 输出银行对帐单数据导入的统计信息 v_line_fal := v_line_all - v_line_suc; output(\' \'); output(\'本次银行对帐单数据导入一共处理\' || v_line_all || \'行信息,其中：\'); output(\'成功\' || v_line_suc || \'行\'); output(\'失败\' || v_line_fal || \'行\'); output(\' \'); output(\'期初供应商数据转换结束于：\' || to_char(SYSDATE, \'YYYY/MM/DD HH24:MI:SS\')); EXCEPTION WHEN OTHERS THEN output(SQLERRM || \', \' || p_error_info); ROLLBACK; END ins_interface; -- -- Main procedure -- PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2, 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, 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) IS v_b_return BOOLEAN; BEGIN gp_statement_number := p_statement_number; gp_bank_account_num := p_bank_account_num; --gp_statement_date := p_statement_date; gp_bank_name := p_bank_name; gp_bank_branch_name := p_bank_branch_name; gp_currency_code := p_currency_code; BEGIN SELECT to_date(p_statement_date, \'DD-MON-RRRR\', \'NLS_DATE_LANGUAGE=american\') INTO gp_statement_date FROM dual; END; gp_working_id := p_working_id; gp_org_id := fnd_global.org_id; gp_user_id := fnd_global.user_id; --IF p_area = \'CUX_STMT_UPLOAD\' THEN log(\'输出参数：\'); log(\'p_statement_date: \' || p_statement_date); log(\'p_area: \' || p_area); log(\'p_gfm_id: \' || p_gfm_id); log(\'p_file_cs: \' || p_file_cs); log(\'p_delimiter: \' || p_delimiter); log(\'p_working_id: \' || p_working_id); log(\'---=============================---\'); IF p_delimiter = 44 THEN /* begin ALTER session set NLS_DATE_LANGUAGE=american; end; */ --alter_nls_date(\'AMERICAN\');

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库csv文件上传数据库[1]在线全文阅读。