获取会计期间状态
/*=======================================================
* FUNCTION / PROCEDURE
* get_period_status
* DESCRIPTION:
* 得到当前日期所在期间的状态
* ARGUMENT:
* RETURN:
* N/A
* HISTORY:
* 1.00 2013-10-31 cxy
=========================================================*/
FUNCTION get_period_status(p_org_id IN NUMBER, p_gl_date IN DATE)
RETURN VARCHAR2 IS
l_closing_status VARCHAR2(1); --O:打开 C:关闭 N:未打开,F:将来期间
BEGIN
SELECT gps.closing_status
INTO l_closing_status
FROM gl_period_statuses gps,
gl_periods gp,
hr_operating_units hou,
gl_sets_of_books gsob
WHERE gps.application_id = 101 --GL /*模块的id*/
AND gps.adjustment_period_flag = 'N'--排除调整期
AND gps.set_of_books_id = hou.set_of_books_id
AND hou.set_of_books_id = gsob.set_of_books_id
AND gp.period_set_name = gsob.period_set_name
AND gps.period_name = gp.period_name
AND (trunc(p_gl_date) BETWEEN gp.start_date AND gp.end_date)--时间
AND hou.organization_id = p_org_id;--OU
RETURN l_closing_status;
EXCEPTION
WHEN no_data_found THEN
cux_conc_utl.log_msg('FUNCTION get_period_status ERROR:' ||
'NO_DATA_FOUND');
RAISE fnd_api.g_exc_error;
WHEN too_many_rows THEN
cux_conc_utl.log_msg('FUNCTION get_period_status ERROR:' ||
'TOO_MANY_ROWS');
RAISE fnd_api.g_exc_error;
WHEN OTHERS THEN
cux_conc_utl.log_msg('FUNCTION get_period_status ERROR:' || SQLERRM);
RAISE fnd_api.g_exc_error;
END get_period_status;
注意
取会计期间的时候处理不好会有 too_many_rows 的异常 应为会计期间有一个调整期,例如下图
12月31日这天就会有两个符合的,这时候我们就需要把2013-12-31到2013-12-31这个排除掉,应为这天是调整期
处理办法就是加上条件
获取会计期间名称
FUNCTION get_period_name(p_org_id IN NUMBER, p_gl_date IN DATE)
RETURN VARCHAR2 IS
l_period_name VARCHAR2(15); --期间名称
BEGIN
SELECT gp.period_name
INTO l_period_name
FROM gl_periods gp, hr_operating_units hou, gl_sets_of_books gsob
WHERE hou.set_of_books_id = gsob.set_of_books_id
AND gp.period_set_name = gsob.period_set_name
AND (trunc(p_gl_date) BETWEEN gp.start_date AND gp.end_date)
AND hou.organization_id = p_org_id
AND gp.adjustment_period_flag = 'N'; --有调整期的把调整期排除
RETURN l_period_name;
EXCEPTION
WHEN no_data_found THEN
cux_conc_utl.log_msg('FUNCTION get_period_name ERROR:' ||
'NO_DATA_FOUND');
RAISE fnd_api.g_exc_error;
WHEN too_many_rows THEN
cux_conc_utl.log_msg('FUNCTION get_period_name ERROR:' ||
'TOO_MANY_ROWS');
RAISE fnd_api.g_exc_error;
WHEN OTHERS THEN
cux_conc_utl.log_msg('FUNCTION get_period_name ERROR:' || SQLERRM);
RAISE fnd_api.g_exc_error;
END get_period_name;