create or replace package body XXACI_AP_IMPORT_PKG is -- Global variable g_pkg_name CONSTANT VARCHAR2 ( 30 ) : = ' XXACI_AP_IMPORT_PKG ' ; -- Debug Enabled l_debug VARCHAR2 ( 1 ) : = nvl(fnd_profile.VALUE( ' AFLOG_ENABLED ' ), ' N ' ); g_character_set CONSTANT VARCHAR2 ( 30 ) : = /* 'ZHS16CGB231280';-- */ ' UTF8 ' ; g_max_field CONSTANT INTEGER : = 10 ; TYPE g_extract_tbl IS TABLE OF VARCHAR2 ( 500 ); g_cells g_extract_tbl; g_group_id NUMBER ; g_currency varchar2 ( 10 ); g_dr_ccid number ; procedure extract_blob(p_file_id IN NUMBER , p_file_name in varchar2 , p_src_cs IN VARCHAR2 , p_delimiter IN VARCHAR2 , p_currency in varchar2 , p_dr_ccid in number , x_group_id OUT NUMBER , x_err_msg OUT VARCHAR2 , x_return_status OUT VARCHAR2 , x_msg_count OUT NOCOPY NUMBER , x_msg_data OUT NOCOPY VARCHAR2 ) is l_api_name CONSTANT VARCHAR2 ( 30 ) : = ' extract_blob ' ; l_api_version CONSTANT NUMBER : = 1.0 ; l_savepoint_name CONSTANT VARCHAR2 ( 30 ) : = ' sp_extract_blob01 ' ; l_data_b BLOB : = NULL ; l_data_c CLOB : = NULL ; l_pos INTEGER ; l_offset INTEGER ; l_clob_size INTEGER ; l_line_no INTEGER ; l_src_offset INTEGER : = 1 ; l_dest_offset INTEGER : = 1 ; l_buf VARCHAR2 ( 4000 ); l_warning VARCHAR2 ( 4000 ); l_ist_count NUMBER ; l_lang_ctx INTEGER : = dbms_lob.default_lang_ctx; BEGIN g_currency : = p_currency; g_dr_ccid : = p_dr_ccid; x_return_status : = xxaci_api.start_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_init_msg_list => fnd_api.g_true); IF x_return_status = fnd_api.g_ret_sts_error THEN RAISE fnd_api.g_exc_error; ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN RAISE fnd_api.g_exc_unexpected_error; END IF ; -- g_group_id:=x_group_id; dbms_lob.createtemporary(l_data_c, FALSE, dbms_lob.session); -- get CSV file ,save into l_data_b IF p_file_id IS NOT NULL THEN SELECT fl.file_data INTO l_data_b FROM fnd_lobs fl WHERE fl. file_id = p_file_id FOR UPDATE OF file_data; dbms_output.put_line(dbms_lob.getlength(l_data_b)); -- check the character set IF p_src_cs <> g_character_set THEN l_data_b : = convertblob(l_data_b, p_src_cs, g_character_set); END IF ; dbms_output.put_line( ' test ' ); -- Convert the BLOB format to CLOB format dbms_lob.converttoclob(dest_lob => l_data_c, src_blob => l_data_b, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset, blob_csid => nls_charset_id(g_character_set), lang_context => l_lang_ctx, warning => l_warning); dbms_output.put_line(dbms_lob.getlength(l_data_c)); l_offset : = 1 ; l_clob_size : = dbms_lob.getlength(l_data_c); l_line_no : = 1 ; SELECT xxaci_mrp_forecast_temp_s.NEXTVAL INTO g_group_id FROM dual; x_group_id : = g_group_id; LOOP l_pos : = dbms_lob.instr(lob_loc => l_data_c, pattern => chr( 10 ), offset => l_offset, nth => 1 ); dbms_output.put_line(to_char(l_pos)); IF nvl(l_pos, 0 ) = 0 THEN l_pos : = l_clob_size + 1 ; END IF ; l_buf : = dbms_lob.substr(lob_loc => l_data_c, amount => l_pos - l_offset, -- N_NEXT_POS - N_POS, offset => l_offset); -- N_POS+1); l_offset : = l_pos + 1 ; -- break down the fields into different columns by the Tab Delimiter extract_cell_data(p_line => REPLACE (l_buf, chr( 13 )), p_line_no => l_line_no, p_delimiter => p_delimiter, x_err_msg => x_err_msg, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data); EXIT WHEN l_pos > l_clob_size; l_line_no : = l_line_no + 1 ; END LOOP; COMMIT ; SELECT COUNT ( 1 ) INTO l_ist_count FROM xxaci_mrp_forecast_temp mft WHERE mft.group_id = g_group_id; IF dbms_lob.istemporary(l_data_b) > 0 THEN dbms_lob.freetemporary(l_data_b); END IF ; IF dbms_lob.istemporary(l_data_c) > 0 THEN dbms_lob.freetemporary(l_data_c); END IF ; END IF ; x_err_msg : = to_char(l_line_no - 1 ) || ' records in data file, ' || l_ist_count || ' processed successfully ' || chr( 10 ) || x_err_msg; dbms_output.put_line( ' x_err_msg: ' || x_err_msg); x_return_status : = xxaci_api.end_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_commit => fnd_api.g_false, x_msg_count => x_msg_count, x_msg_data => x_msg_data); dbms_output.put_line( ' x_msg_data: ' || x_msg_data); EXCEPTION WHEN no_data_found THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_error, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data); IF x_msg_count > 1 THEN x_msg_data : = fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first, p_encoded => fnd_api.g_false); END IF ; WHEN fnd_api.g_exc_error THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_error, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data); IF x_msg_count > 1 THEN x_msg_data : = fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first, p_encoded => fnd_api.g_false); END IF ; WHEN fnd_api.g_exc_unexpected_error THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_unexp, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data); IF x_msg_count > 1 THEN x_msg_data : = fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first, p_encoded => fnd_api.g_false); END IF ; WHEN OTHERS THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_others, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name, p_procedure_name => l_api_name, p_error_text => substrb(SQLERRM, 1 , 240 )); xxaci_conc_utl.log_message_list; x_msg_data : = SQLERRM; END extract_blob; -- ----------------------------- PROCEDURE extract_cell_data(p_line IN VARCHAR2 , p_line_no IN INTEGER , p_delimiter IN VARCHAR2 , x_err_msg OUT VARCHAR2 , x_return_status OUT VARCHAR2 , x_msg_count OUT NOCOPY NUMBER , x_msg_data OUT NOCOPY VARCHAR2 ) IS l_api_name CONSTANT VARCHAR2 ( 30 ) : = ' extract_cell_data ' ; l_api_version CONSTANT NUMBER : = 1.0 ; l_savepoint_name CONSTANT VARCHAR2 ( 30 ) : = ' sp_extract_cell_data01 ' ; l_line VARCHAR2 ( 4000 ); l_field VARCHAR2 ( 4000 ); l_field_cnt INTEGER ; l_delimiter_pos INTEGER ; v_temp_id number ; v_ccid number ; -- l_cell1 NUMBER; /* l_project_name VARCHAR2(100); l_segment1 VARCHAR2(240); l_uom_code VARCHAR2(10); l_currency_code VARCHAR2(3); l_cj_vendor_name VARCHAR2(240); l_vendor_name VARCHAR2(240); l_primary_flag VARCHAR2(1); l_unit_price NUMBER; */ BEGIN x_return_status : = xxaci_api.start_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_init_msg_list => fnd_api.g_true); IF x_return_status = fnd_api.g_ret_sts_error THEN RAISE fnd_api.g_exc_error; ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN RAISE fnd_api.g_exc_unexpected_error; END IF ; g_cells : = g_extract_tbl(); g_cells.EXTEND(g_max_field); l_field_cnt : = 1 ; IF p_line IS NOT NULL THEN -- extract values from field l_line : = p_line; dbms_output.put_line(l_line); LOOP l_field : = NULL ; l_delimiter_pos : = instr(l_line, p_delimiter); IF l_delimiter_pos > 0 THEN l_field : = ltrim ( rtrim (substr(l_line, 1 , l_delimiter_pos - 1 ))); l_line : = substr(l_line, l_delimiter_pos + 1 ); ELSIF ltrim ( rtrim (l_line)) IS NOT NULL THEN l_field : = ltrim ( rtrim (l_line)); l_line : = NULL ; END IF ; dbms_output.put_line(to_char(l_field_cnt) || ' - ' || l_field); -- IF l_field IS NOT NULL THEN dbms_output.put_line(l_field); g_cells(l_field_cnt) : = l_field; -- END IF; l_field_cnt : = l_field_cnt + 1 ; -- exit when finish extract last field IF (l_line IS NULL ) OR (l_field_cnt = g_max_field) THEN EXIT ; END IF ; END LOOP; dbms_output.put_line(g_cells. COUNT ); BEGIN v_ccid: = null ; if g_cells( 5 ) is not null then begin select code_combination_id into v_ccid from gl_code_combinations where segment1 || ' . ' || segment2 || ' . ' || segment3 || ' . ' || segment4 || ' . ' || segment5 || ' . ' || segment6 = g_cells( 5 ); exception when others then v_ccid: = null ; end ; end if ; SELECT XXCUS_ATU.XXACI_AP_IMPORT_S.NEXTVAL into v_temp_id FROM DUAL; INSERT INTO xxaci_ap_import_tmp (vendor_num, header_description, line_amount, line_description, distribution_ccid, group_id, invoice_currency_code, temp_id) VALUES (g_cells( 1 ), g_cells( 2 ), g_cells( 3 ), g_cells( 4 ), nvl(v_ccid, g_dr_ccid), g_group_id, g_currency, v_temp_id); commit ; EXCEPTION WHEN OTHERS THEN x_err_msg : = substrb(SQLERRM, 1 , 240 ); END ; END IF ; x_return_status : = xxaci_api.end_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_commit => fnd_api.g_false, x_msg_count => x_msg_count, x_msg_data => x_msg_data); EXCEPTION WHEN no_data_found THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_error, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data); IF x_msg_count > 1 THEN x_msg_data : = fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first, p_encoded => fnd_api.g_false); END IF ; WHEN fnd_api.g_exc_error THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_error, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data); IF x_msg_count > 1 THEN x_msg_data : = fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first, p_encoded => fnd_api.g_false); END IF ; WHEN fnd_api.g_exc_unexpected_error THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_unexp, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data); IF x_msg_count > 1 THEN x_msg_data : = fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first, p_encoded => fnd_api.g_false); END IF ; WHEN OTHERS THEN x_return_status : = xxaci_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_exc_name => xxaci_api.g_exc_name_others, x_msg_count => x_msg_count, x_msg_data => x_msg_data); fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name, p_procedure_name => l_api_name, p_error_text => substrb(SQLERRM, 1 , 240 )); xxaci_conc_utl.log_message_list; x_msg_data : = SQLERRM; END extract_cell_data;