会计期间的选定问题

DECLARE

  g_period_set_name VARCHAR2(20) := '山推日历';
  g_period_type     VARCHAR2(20) := 21;

  l_period_begin VARCHAR2(15) := '2012-01';
  l_period_end   VARCHAR2(15) := '2013-01';

  l_start_period_year NUMBER;
  l_end_period_year   NUMBER;

  l_start_period_num NUMBER;
  l_end_period_num   NUMBER;

  CURSOR cur_period(p_start_period_year NUMBER, p_start_period_num NUMBER, p_end_period_year NUMBER, p_end_period_num NUMBER) IS
    SELECT gpd.period_name
      FROM gl.gl_periods gpd
     WHERE 1 = 1
       AND gpd.period_year BETWEEN p_start_period_year AND p_end_period_year
       AND (CASE
             WHEN gpd.period_year = p_start_period_year THEN
              p_start_period_num
             ELSE
              gpd.period_num
           END) <= gpd.period_num
       AND (CASE
             WHEN gpd.period_year = p_end_period_year THEN
              p_end_period_num
             ELSE
              gpd.period_num
           END) >= gpd.period_num
       AND gpd.period_set_name = g_period_set_name
       AND gpd.period_type = g_period_type;
BEGIN

  BEGIN
    SELECT t.period_year,
           t.period_num
      INTO l_start_period_year,
           l_start_period_num
      FROM gl.gl_periods t
     WHERE t.period_set_name = g_period_set_name
       AND t.period_type = g_period_type
       AND t.period_name = l_period_begin;
 
    SELECT t.period_year,
           t.period_num
      INTO l_end_period_year,
           l_end_period_num
      FROM gl.gl_periods t
     WHERE t.period_set_name = g_period_set_name
       AND t.period_type = g_period_type
       AND t.period_name = l_period_end;
 
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('输入期间异常,请检查！');
      --RAISE fnd_api.g_exc_error;
  END;

  FOR rec IN cur_period(l_start_period_year, l_start_period_num, l_end_period_year, l_end_period_num) LOOP
    dbms_output.put_line(rec.period_name);
  END LOOP;

END;

2013-01-29_100306.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25543703/viewspace-753465/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25543703/viewspace-753465/