所有接口的注意事项
1、验证数据的准确性,也就是各个字段进行数据校验,包括金额正负数等的验证
2、验证数据的重复性,也就是已经传过的数据不允许再传送,否则造成数据重复导入
3、并发的控制,如果前端系统无法保证数据的并发处理,那么需要在接口环境进行控制,否则一旦并发还是会造成数据重复,甚至错误。
4、性能,如果数据量大的时候如果保证快速进行传送也是接口需要考虑的问题。
5、日记账导入无法也是按照以上规则进行,再就是会有特殊要求,导入之后进行过账。或者进行一些分组等特殊逻辑。
日记账导入详细介绍
1 接口表 gl_interface
字段
|
说明
|
|
|
group_id
|
可以放空,也可以从 gl_interface_control_s 取,也可自己指定
|
status
|
固定给 NEW
|
set_of_books_id
|
账套 ID
|
code_combination_id
|
帐户 ID
|
user_je_category_name
|
日记帐类别名称
|
user_je_source_name
|
日记帐分录来源名称
|
accounting_date
|
凭证日期、会计结算日期
|
period_name
|
会计期间
|
currency_code
|
币种代码
|
date_created
|
日记帐分录行的创建日期
|
actual_flag
|
类别表示,一般导总账都是导入实际金额 A表示实际金额,保留款和预算也由此标记控制
|
entered_dr
|
借方金额
|
entered_cr
|
贷方金额
|
accounted_dr
|
借方本位币金额
|
accounted_cr
|
贷方本位币金额
|
created_by
|
创建人
|
reference1
|
批名
|
reference2
|
批说明
|
reference3
|
请勿在此列输入值
|
reference4
|
日记账名称
|
reference5
|
日记账说明
|
reference6
|
日记帐分录参考
|
reference7
|
日记帐分录冲销标志
|
|
|
reference9
|
日记帐冲销方法
|
reference10
|
日记帐分录行说明
|
reference11 至 reference20
|
请勿在此列输入值
|
je_batch_id
|
请勿在此列输入值
|
je_header_id
|
请勿在此列输入值
|
je_line_num
|
请勿在此列输入值
|
chart_of_accounts_id
|
请勿在此列输入值
|
date_created_in_gl
|
请勿在此列输入值
|
warning_code
|
请勿在此列输入值
|
status_descr īption
|
请勿在此列输入值
|
desc_flex_error_message
|
请勿在此列输入值
|
request_id
|
请勿在此列输入值
|
subledger_doc_sequence_id
|
请勿在此列输入值
|
subledger_doc_sequence_value
|
用于总帐与子分类帐间的数据传递。请勿填入您自己的数据。
|
3、数据导入
4、提交导入请求
5、过账
具体程序如下:
程序背景:
含有两个客户化接口表cux_gl_interface_header,cux_gl_interface_line 因为采用了新旧系统同时存在,所以需要新旧系统凭证进行传送,所以会有各个段值的映射关系。如果有其他需要核心内容不变更,程序仅当做参考。
CREATE OR REPLACE PACKAGE BODY cux_gl_interface_imp_pkg AS
g_package_name CONSTANT VARCHAR2(100) := 'CUX_GL_INTERFACE_imp_pkg';
g_user_id NUMBER := fnd_global.user_id;
g_login_id NUMBER := fnd_global.login_id;
g_conc_program_id NUMBER := fnd_global.conc_program_id;
g_prog_appl_id NUMBER := fnd_global.prog_appl_id;
g_request_id NUMBER := fnd_global.conc_request_id;
g_gl_request_id NUMBER;
g_ledger_id NUMBER := fnd_profile.value('GL_SET_OF_BKS_ID');
g_session_id NUMBER;
l_msg_data VARCHAR2(4000);
g_return_status VARCHAR2(120) := 'S';
g_line_err_msg VARCHAR2(4000);
g_line_return_status VARCHAR2(120);
g_debug_flag VARCHAR2(30) := 'N' /* nvl(fnd_profile.value('AFLOG_ENABLED'),
'N')*/
;
PROCEDURE log_msg(p_log_msg IN VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.log, p_log_msg);
END log_msg;
PROCEDURE output(p_message VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.output, p_message);
END output;
PROCEDURE stack_message(p_msg IN VARCHAR2) IS
BEGIN
cux_api.set_message(upper('fnd'),
upper('conc-places message on stack'),
upper('message'),
p_msg);
END stack_message;
PROCEDURE raise_exception(x_return_status VARCHAR2) IS
BEGIN
IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
ELSE
NULL;
END IF;
END raise_exception;
FUNCTION get_org_id(p_org_code IN VARCHAR2) RETURN NUMBER IS
l_org_id NUMBER;
BEGIN
SELECT hou.organization_id
INTO l_org_id
FROM xle_entity_profiles p,
hr_operating_units hou,
gl_legal_entities_bsvs gl
WHERE 1 = 1
AND hou.default_legal_context_id = p.legal_entity_id
AND p.legal_entity_id = gl.legal_entity_id
AND gl.flex_segment_value = p_org_code
AND hou.set_of_books_id = g_ledger_id;
RETURN l_org_id;
EXCEPTION
WHEN no_data_found THEN
RETURN - 1;
WHEN OTHERS THEN
RETURN - 3;
END get_org_id;
--获取值集
FUNCTION get_flex_desc(p_flex_value_set IN VARCHAR2,
p_flex_value IN VARCHAR2) RETURN VARCHAR2 IS
l_segment_value VARCHAR2(120);
BEGIN
SELECT flv.description
INTO l_segment_value
FROM fnd_flex_values_vl flv, fnd_flex_value_sets ffs
WHERE flv.flex_value_set_id = ffs.flex_value_set_id
AND ffs.flex_value_set_name = p_flex_value_set
AND flv.flex_value = p_flex_value
--AND flv.enabled_flag = 'Y'
/*AND SYSDATE BETWEEN nvl(flv.start_date_active, SYSDATE - 1) AND
nvl(flv.end_date_active, SYSDATE + 1)*/
;
RETURN nvl(l_segment_value, 'N');
EXCEPTION
WHEN no_data_found THEN
RETURN 'N';
WHEN too_many_rows THEN
RETURN 'M';
WHEN OTHERS THEN
RETURN 'O';
END get_flex_desc;
--获取各段段值
FUNCTION get_old_segment_value(p_flex_value_set IN VARCHAR2,
p_flex_value IN VARCHAR2)
RETURN VARCHAR2 IS
l_segment_value VARCHAR2(120);
BEGIN
SELECT flv.attribute1
INTO l_segment_value
FROM fnd_flex_values_vl flv, fnd_flex_value_sets ffs
WHERE flv.flex_value_set_id = ffs.flex_value_set_id
AND ffs.flex_value_set_name = p_flex_value_set
AND flv.flex_value = p_flex_value
AND flv.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(flv.start_date_active, SYSDATE - 1) AND
nvl(flv.end_date_active, SYSDATE + 1);
RETURN nvl(l_segment_value, 'N');
EXCEPTION
WHEN no_data_found THEN
RETURN 'N';
WHEN too_many_rows THEN
RETURN 'M';
WHEN OTHERS THEN
RETURN 'O';
END get_old_segment_value;
PROCEDURE get_old_segment_special(p_new_account IN VARCHAR2,
p_new_manacc IN VARCHAR2,
p_new_unit_code IN VARCHAR2,
p_new_produce IN VARCHAR2,
x_old_account IN OUT VARCHAR2,
x_old_manacc IN OUT VARCHAR2, --新科目
x_old_produce IN OUT VARCHAR2) IS
BEGIN
SELECT flv.attribute5, flv.attribute6, flv.attribute7
INTO x_old_account, x_old_manacc, x_old_produce
FROM apps.fnd_flex_values_vl flv, apps.fnd_flex_value_sets ffs
WHERE flv.flex_value_set_id = ffs.flex_value_set_id
AND ffs.flex_value_set_name = 'CUX_GL_ACCOUNT_PRODUCT'
AND flv.attribute1 = p_new_account
AND flv.attribute2 = p_new_manacc
AND nvl(flv.attribute3, '0') = nvl(p_new_unit_code, '1')
AND nvl(flv.attribute4, '0') = substr(p_new_produce, 1, 2)
AND flv.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(flv.start_date_active, SYSDATE - 1) AND
nvl(flv.end_date_active, SYSDATE + 1);
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
x_old_account := 'N';
x_old_produce := 'N';
END get_old_segment_special;
FUNCTION get_employee_id(p_email_name VARCHAR2) RETURN NUMBER IS
l_user_id NUMBER;
BEGIN
SELECT t.employee_id
INTO l_user_id
FROM fnd_user t
WHERE t.email_address = p_email_name
AND SYSDATE BETWEEN nvl(t.start_date, SYSDATE - 1) AND
nvl(t.end_date, SYSDATE + 1);
RETURN nvl(l_user_id, -1);
EXCEPTION
WHEN no_data_found THEN
RETURN '-1';
WHEN too_many_rows THEN
RETURN '-2';
WHEN OTHERS THEN
RETURN '-3';
END;
--获取人员信息
FUNCTION get_user_id(p_email_name VARCHAR2) RETURN NUMBER IS
l_user_id NUMBER;
BEGIN
SELECT t.user_id
INTO l_user_id
FROM fnd_user t
WHERE t.email_address = p_email_name
AND SYSDATE BETWEEN nvl(t.start_date, SYSDATE - 1) AND
nvl(t.end_date, SYSDATE + 1);
RETURN nvl(l_user_id, -1);
EXCEPTION
WHEN no_data_found THEN
RETURN '-1';
WHEN too_many_rows THEN
RETURN '-2';
WHEN OTHERS THEN
RETURN '-3';
END get_user_id;
FUNCTION get_code_combination_id(p_concatenated_segments IN VARCHAR2,
p_chart_of_accounts_id IN NUMBER,
x_posting_allowed_flag OUT VARCHAR2)
RETURN NUMBER IS
l_code_combination_id NUMBER NULL;
BEGIN
l_code_combination_id := apps.fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
validation_date => to_char(SYSDATE,
apps.fnd_flex_ext.date_format),
concatenated_segments => p_concatenated_segments);
BEGIN
SELECT t.detail_posting_allowed
INTO x_posting_allowed_flag
FROM gl_code_combinations_kfv t
WHERE t.code_combination_id = l_code_combination_id;
EXCEPTION
WHEN OTHERS THEN
x_posting_allowed_flag := 'N';
END;
RETURN nvl(l_code_combination_id, -1);
EXCEPTION
WHEN OTHERS THEN
IF fnd_flex_keyval.validate_segs(operation => 'CREATE_COMBINATION',
appl_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
concat_segments => p_concatenated_segments) THEN
l_code_combination_id := fnd_flex_ext.get_ccid('SQLGL',
'GL#',
p_chart_of_accounts_id,
to_char(SYSDATE,
'YYYY-MM-DD'),
p_concatenated_segments);
BEGIN
SELECT t.detail_posting_allowed
INTO x_posting_allowed_flag
FROM gl_code_combinations_kfv t
WHERE t.code_combination_id = l_code_combination_id;
EXCEPTION
WHEN OTHERS THEN
x_posting_allowed_flag := 'N';
END;
ELSE
l_code_combination_id := -1;
x_posting_allowed_flag := 'N';
END IF;
RETURN l_code_combination_id;
END get_code_combination_id;
FUNCTION lock_data(p_source_code VARCHAR2, p_source_header_id NUMBER)
RETURN NUMBER IS
CURSOR cur_data IS
SELECT 1 flag
FROM cux_gl_interface_header t
WHERE t.source_code = p_source_code
AND t.source_header_id = p_source_header_id
AND nvl(t.process_status, 'NEW') IN ('NEW', 'VALID-ERROR')
FOR UPDATE NOWAIT;
l_count NUMBER;
l_lock NUMBER;
BEGIN
BEGIN
FOR i IN cur_data LOOP
l_count := i.flag;
END LOOP;
l_lock := 1;
EXCEPTION
WHEN OTHERS THEN
l_lock := 0; --没有锁住
END;
IF l_lock = 0 THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
--模拟职责
PROCEDURE moac_init(p_org_id NUMBER, p_user_id NUMBER, p_resp_id NUMBER) IS
l_api_name VARCHAR2(32) := 'moac_init';
l_resp_appl_id NUMBER;
l_org_id NUMBER;
BEGIN
fnd_global.apps_initialize(user_id => p_user_id, --
resp_id => p_resp_id, --
resp_appl_id => 101); --GL
IF p_org_id IS NOT NULL THEN
mo_global.set_policy_context('S', p_org_id);
END IF;
END moac_init;
PROCEDURE check_je_source(p_user_je_source_name IN VARCHAR2,
x_je_source_name OUT VARCHAR2,
x_return_status OUT VARCHAR2,