获取会计期间状态

/*=======================================================  *  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;