如何避免Oracle Form界面倒入.csv文件出现乱码
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;
-- 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;
