create or replace procedure test_proc
as
begin


insert into test
select * from(
with qq as (
         select  date '2015-12-31' + level dt
             from  dual
             connect by date '2015-12-31' + level <= date '2016-12-31'
          )
select  case grouping(data)
          when 0 then to_char(data,'ddspth "of "fmMonth, yyyy','NLS_DATE_LANGUAGE = AMERICAN')
          else to_char(trunc(data,'mm'),'mm')
        end name,
        case grouping(data)
          when 0 then to_char(data,'ddspth')
          else to_char(trunc(data,'mm'),'fmMonth','NLS_DATE_LANGUAGE = AMERICAN')
        end short_name,
        case grouping(data)
          when 0 then 1
          else 3
        end date_level,
        case grouping(data)
          when 0 then 'day'
          else 'localperiod'
        end date_level_name,
        case grouping(data)
          when 0 then to_char(data,'dd.mm.yyyy')
          else to_char(trunc(data,'mm'),'dd.mm.yyyy')
        end start_date,
        case grouping(data)
          when 0 then to_char(data,'dd.mm.yyyy')
          else to_char(last_day(trunc(data,'mm')),'dd.mm.yyyy')
        end finish_date,
        case to_char(data, 'dd')-to_char(data, 'dd')
          when 0 then 1
          else 31
         end count_day_period,
        to_char (data, 'd') n_day_w,
        to_char (data, 'dd') n_day_m,
        to_char (data, 'ddd') n_day_y,
        to_char (data, 'ww') n_week_y,
        to_char (data, 'mm') n_month_y,
        to_char (data, 'q') n_quart_y, 
        case when to_char(data,'mm') < 7 
          then 1 
          else 2 
        end n_halfyear_y,
        to_char (data, 'YYYY')n_year,
        to_char (data, 'dd.mm.yyyy')calendar_date,
        to_char (data, 'Month','NLS_DATE_LANGUAGE = AMERICAN') month_eng,
        to_char (data, 'Mon','NLS_DATE_LANGUAGE = AMERICAN') short_month_eng,
        to_char (data, 'Month','NLS_DATE_LANGUAGE = RUSSIAN') month_rus,
        to_char (data, 'Mon','NLS_DATE_LANGUAGE = RUSSIAN') short_month_rus,
        case  to_char(last_day(data),'dd')- to_char(last_day(data),'dd')
          when 0 then 0
          else 1
        end is_lastday_m,
        to_char (data, 'yyyy/mm','NLS_DATE_LANGUAGE = RUSSIAN') year_month,
        to_char (data, 'yyyymm','NLS_DATE_LANGUAGE = RUSSIAN') month_key
from  qq
group by grouping sets((trunc(data,'mm')),(trunc(data,'mm'),data)))aa ;

                                  
end;