create or replace FUNCTION get_week_list3(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''у'') THEN '||''''|| to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd mon')||''''|| '||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
RETURN RTRIM(l_res);
END;

SELECT t.god, u.FIO, (regexp_substr ( get_week_list3(t.kod_sotr,t.god), '[^;]*;') || 
regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')) as учебный_отпуск FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

create or replace FUNCTION get_week_list3(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''у'') THEN '||''''||to_char( greatest( trunc (to_date(i_year||'0101', 'yyyymmdd')+((LTRIM(column_name,'N'))-1)*7, 'iw')
,to_date(i_year||'0101', 'yyyymmdd')) ,'dd mon')||''''|| '||''; '' ELSE NULL END ' ,'||')
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
RETURN RTRIM(l_res);
END;

SELECT t.god, u.FIO, (regexp_substr ( get_week_list3(t.kod_sotr,t.god), '[^;]*;') ||
regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')) as учебный_отпуск FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

create or replace FUNCTION get_week_list3(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''у'') THEN '||''''||to_char( greatest( trunc (to_date(i_year||'0101', 'yyyymmdd')+((LTRIM(column_name,'N'))-1)*7, 'iw')
,to_date(i_year||'0101', 'yyyymmdd')) ,'dd mon')||''''|| '||''; '' ELSE NULL END ' ,'||')
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
RETURN RTRIM(l_res);
END;

SELECT t.god, u.FIO, (regexp_substr ( get_week_list3(t.kod_sotr,t.god), '[^;]*;') ||
regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')) as учебный_отпуск FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

SELECT t.god, u.FIO, (regexp_substr ( get_week_list3(t.kod_sotr,t.god), '[^;]*;') || 
to_char(to_date(regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')+7), 'dd.mon')) as учебный_отпуск FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

to_char(to_date(regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')), 'dd.mon')+7,'dd.mon')

to_char(to_date(regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')), 'dd.mon')+7,'dd.mon')

SELECT t.god, u.FIO, (regexp_substr ( get_week_list3(t.kod_sotr,2017), '[^;]*;') ||' - '||
to_char(to_date(regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$')), 'dd.mon')+ interval '7' day,'dd.mon') as учебный_отпуск FROM GRAFIK t, USERS u 
where u.kod_sotr = 109909 and t.kod_sotr = u.KOD_SOTR and t.god = 2017;

create or replace FUNCTION
 week_fun2(i_code IN INTEGER, i_year in integer)
RETURN VARCHAR2 IS
l_list VARCHAR2(32767);
 l_res VARCHAR2(4000);
 BEGIN SELECT listagg(column_name,',') WITHIN GROUP(ORDER BY to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mon'))
 INTO l_list
 FROM user_tab_columns
 WHERE TABLE_NAME = 'GRAFIK' AND column_name LIKE 'N%';
   l_list:='with t2 as(
  select God, Kod_sotr,'||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year)'||
  q'~
 select /* God, Kod_sotr, */ listagg(lag_WN) within group (order by RN,Kod_sotr) vacation
  from (
  select rownum RN, God, Kod_sotr,
  case when lead(Week_Number) over(PARTITION by God order by Kod_sotr) - Week_number > 1 then /*Weeks*/Weeks_D||';'
      when lead(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||';'
       when lag(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||','
       when Week_number - lag(Week_Number) over(PARTITION by God order by Kod_sotr) > 1 then  /*Weeks*/Weeks_D||','
       else null end lag_WN
          from (
                   select God, Kod_sotr, /*weeks*/
to_char(trunc(to_date(god,'yyyy'),'y')+(cast(ltrim(weeks,'N') as number)-1)*7,'dd mon') Weeks_D, 
                   Vacation, cast(ltrim(weeks,'N')as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(ltrim(weeks,'N')as number)
                )where vacation='о'
          )group by god, Kod_sotr~';
   EXECUTE IMMEDIATE l_list INTO l_res USING i_code,i_year;
  RETURN RTRIM(l_res);
  END;


SELECT t.god, u.FIO,  week_fun10(t.kod_sotr,t.god) as внеочередной FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

SELECT t.god, u.FIO, regexp_substr ( get_week_list3(t.kod_sotr,2017), '[^;]*;') ||' - '||
to_char((to_date(regexp_substr ( get_week_list3(t.kod_sotr,t.god), ' [^;]*;$'),'DD MON";"')+6),'dd mon";"') as учебный_отпуск FROM GRAFIK t, USERS u 
where u.kod_sotr = 109909 and t.kod_sotr = u.KOD_SOTR and t.god = 2017;

create or replace FUNCTION
 week_fun2(i_code IN INTEGER, i_year in integer)
RETURN VARCHAR2 IS
l_list VARCHAR2(32767);
 l_res VARCHAR2(4000);
 BEGIN SELECT listagg(column_name,',') WITHIN GROUP(ORDER BY to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mon'))
 INTO l_list
 FROM user_tab_columns
 WHERE TABLE_NAME = 'GRAFIK' AND column_name LIKE 'N%';
   l_list:='with t2 as(
  select God, Kod_sotr,'||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year)'||
  q'~
 select /* God, Kod_sotr, */ listagg(lag_WN) within group (order by RN,Kod_sotr) vacation
  from (
  select rownum RN, God, Kod_sotr,
  case when lead(Week_Number) over(PARTITION by God order by Kod_sotr) - Week_number > 1 then /*Weeks*/Weeks_D||';'
      when lead(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||';'
       when lag(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||','
       when Week_number - lag(Week_Number) over(PARTITION by God order by Kod_sotr) > 1 then  /*Weeks*/Weeks_D||','
       else null end lag_WN
          from (
                   select God, Kod_sotr, /*weeks*/
to_char(trunc(to_date(god,'yyyy'),'y')+(cast(ltrim(weeks,'N') as number)-1)*7,'dd mon') Weeks_D, 
                   Vacation, cast(ltrim(weeks,'N')as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(ltrim(weeks,'N')as number)
                )where vacation='о'
          )group by god, Kod_sotr~';
   EXECUTE IMMEDIATE l_list INTO l_res USING i_code,i_year;
  RETURN RTRIM(l_res);
  END;


SELECT t.god, u.FIO,  week_fun10(t.kod_sotr,t.god) as внеочередной FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

create or replace FUNCTION get_week_list3(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''у'') THEN '||''''|| to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd mon')||''''|| '||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
RETURN RTRIM(l_res);
END;

SELECT listagg('CASE WHEN '||column_name||' IN(''у'') THEN '||''''|| to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd mon')||''''|| '||''; '' ELSE NULL END ' ,'||') 

create or replace FUNCTION get_week_list3(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''у'') THEN '||''''|| to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd mon')||''''|| '||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
RETURN RTRIM(l_res);
END;

create or replace FUNCTION
 week_fun10(i_code IN INTEGER, i_year in integer)
RETURN VARCHAR2 IS
l_list VARCHAR2(32767);
 l_res VARCHAR2(4000);
 BEGIN SELECT listagg(column_name,',') WITHIN GROUP(ORDER BY to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mon'))
 INTO l_list
 FROM user_tab_columns
 WHERE TABLE_NAME = 'GRAFIK' AND column_name LIKE 'N%';
   l_list:='with t2 as(
  select God, Kod_sotr,'||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year)'||
  q'~
 select /* God, Kod_sotr, */ listagg(lag_WN) within group (order by RN,Kod_sotr) vacation
  from (
  select rownum RN, God, Kod_sotr,
  case when lead(Week_Number) over(PARTITION by God order by Kod_sotr) - Week_number > 1 then /*Weeks*/Weeks_D||';'
      when lead(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||';'
       when lag(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||','
       when Week_number - lag(Week_Number) over(PARTITION by God order by Kod_sotr) > 1 then  /*Weeks*/Weeks_D||','
       else null end lag_WN
          from (
                   select God, Kod_sotr, /*weeks*/
to_char(trunc(to_date(god,'yyyy'),'y')+(cast(ltrim(weeks,'N') as number)-1)*7,'dd mon') Weeks_D, 
                   Vacation, cast(ltrim(weeks,'N')as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(ltrim(weeks,'N')as number)
                )where vacation='б'
          )group by god, Kod_sotr~';
   EXECUTE IMMEDIATE l_list INTO l_res USING i_code,i_year;
  RETURN RTRIM(l_res);
  END;

create or replace FUNCTION
 week_fun10(i_code IN INTEGER, i_year in integer)
RETURN VARCHAR2 IS
l_list VARCHAR2(32767);
 l_res VARCHAR2(4000);
 BEGIN SELECT listagg(column_name,',') WITHIN GROUP(ORDER BY to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mon'))
 INTO l_list
 FROM user_tab_columns
 WHERE TABLE_NAME = 'GRAFIK' AND column_name LIKE 'N%';
   l_list:='with t2 as(
  select God, Kod_sotr,'||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year)'||
  q'~
 select /* God, Kod_sotr, */ listagg(lag_WN) within group (order by RN,Kod_sotr) vacation
  from (
  select rownum RN, God, Kod_sotr,
  case when lead(Week_Number) over(PARTITION by God order by Kod_sotr) - Week_number > 1 then /*Weeks*/Weeks_D||';'
      when lead(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||';'
       when lag(Week_Number) over(PARTITION by God order by Kod_sotr) is null then            /*Weeks*/Weeks_D||','
       when Week_number - lag(Week_Number) over(PARTITION by God order by Kod_sotr) > 1 then  /*Weeks*/Weeks_D||','
       else null end lag_WN
          from (
                   select God, Kod_sotr, /*weeks*/
to_char(trunc(to_date(god,'yyyy'),'y')+(cast(ltrim(weeks,'N') as number)-1)*7,'dd mon') Weeks_D, 
                   Vacation, cast(ltrim(weeks,'N')as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(ltrim(weeks,'N')as number)
                )where vacation='б'
          )group by god, Kod_sotr~';
   EXECUTE IMMEDIATE l_list INTO l_res USING i_code,i_year;
  RETURN RTRIM(l_res);
  END;