会计期间

获取会计期间状态

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