create or replace FUNCTION
 week_fun1(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 1)
 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||';'
      when lead(Week_Number) over(PARTITION by God order by Kod_sotr) is null then Weeks||';'
       when lag(Week_Number) over(PARTITION by God order by Kod_sotr) is null then Weeks||','
       when Week_number - lag(Week_Number) over(PARTITION by God order by Kod_sotr) > 1 then Weeks||','
       else null end lag_WN
          from (
                   select God, Kod_sotr, weeks, 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_fun1(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_number(LTRIM(column_name,'N')))
 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||';'
      when lead(Week_Number) over(PARTITION by God order by Kod_sotr) is null then Weeks||';'
       when lag(Week_Number) over(PARTITION by God order by Kod_sotr) is null then Weeks||','
       when Week_number - lag(Week_Number) over(PARTITION by God order by Kod_sotr) > 1 then Weeks||','
       else null end lag_WN
          from (
                   select God, Kod_sotr, weeks, Vacation, cast(to_char( trunc (to_date(~'||i_year||q'~||'0101', 'yyyymmdd') 
+ ((LTRIM(weeks,'N'))-1)*7, 'ww'), 'dd.mm')as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(to_char( trunc (to_date(~'||i_year||q'~||'0101', 'yyyymmdd') 
+ ((LTRIM(weeks,'N'))-1)*7, 'ww'), 'dd.mm')as number)
                )
          )group by god, Kod_sotr~';
   EXECUTE IMMEDIATE l_list INTO l_res USING i_code,i_year;
  RETURN RTRIM(l_res);
  END;

  select week_fun( kod_sotr,god) f from grafik g;

cast (to_char(..., 'dd.mm') as number)

from (
                   select God, Kod_sotr, weeks, Vacation, cast(to_char( trunc (to_date(2017||'0101', 'yyyymmdd') 
+ ((LTRIM(weeks,'N'))-1)*7, 'ww'), 'dd.mm')as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(to_char( trunc (to_date(2017||'0101', 'yyyymmdd') 
+ ((LTRIM(weeks,'N'))-1)*7, 'ww'), 'dd.mm')as number)
                )where vacation='о'
          )group by god, Kod_sotr~';

cast (to_char(..., 'dd,mm') as number)

cast (to_char(..., 'dd,mm') as number)

select God, Kod_sotr, weeks, Vacation, cast(to_char( trunc (to_date(2017||'0101', 'yyyymmdd') 
+ ((LTRIM(weeks,'N'))-1)*7, 'ww'), 'dd,mm')as number) Week_Number from t2

BEGIN SELECT listagg(to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mm'),',') WITHIN GROUP(ORDER BY 1)
 INTO l_list
 FROM user_tab_columns
 WHERE TABLE_NAME = 'GRAFIK' AND column_name LIKE 'N%';

from (
                   select God, Kod_sotr, weeks, 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~';

from (
                   select God, Kod_sotr, weeks, Vacation, cast(weeks as number) Week_Number from t2
                  unpivot (vacation for weeks in (~'||l_list||q'~))
                 order by God, Kod_sotr, cast(weeks as number)
                )where vacation='о'
          )group by god, Kod_sotr~';

 select /* God, Kod_sotr, */ listagg(ltrim(lag_WN, 'N')) within group (order by RN,Kod_sotr) vacation
  from (

rom (
                   select God, Kod_sotr, /*weeks*/
to_char(trunc(to_date(god,'yyyy'),'y')+(cast(ltrim(weeks,'N') as number)-1)*7,'dd.mm') Weeks_D, 
                   Vacation, cast(ltrim(weeks,'N')as number) Week_Number from t2

create or replace FUNCTION get_week_list_prob(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;

EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;

println

create or replace FUNCTION get_week_list_prob(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%';
  dbms_output.enable;
  dbms_output.put_line('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year') ;
--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_list_prob(t.kod_sotr,t.god), '^([[:digit:]]+)(,)([[:digit:]]+)(;)') || 
regexp_substr (get_week_list_prob(t.kod_sotr,t.god), '([[:digit:]]+)(,)([[:digit:]]+)(;)$')) as за_свой_счёт FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

println

--RETURN RTRIM(l_res);

create or replace FUNCTION get_week_list_prob(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,'yyyy'),'y')+((LTRIM(column_name, 'N'))-1)*7,'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%';
 dbms_output.enable;
dbms_output.put_line('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=' ||i_code||' AND god=' ||i_year);
l_res:=('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=' ||i_code||' AND god=' ||i_year);
return l_res;
---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_list_prob(t.kod_sotr,t.god), '^([[:digit:]]+)(,)([[:digit:]]+)(;)') || 
regexp_substr (get_week_list_prob(t.kod_sotr,t.god), '([[:digit:]]+)(,)([[:digit:]]+)(;)$')) as за_свой_счёт FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

select get_week_list_prob(0,0) s from dual

SELECT CASE WHEN N01 IN('б') THEN 01.янв||'; ' ELSE NULL END ||CASE WHEN N02 IN('б') THEN 08.янв||'; ' ELSE NULL END |

THEN 01.янв||

EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;

create or replace FUNCTION get_week_list_prob(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,'yyyy'),'y')+((LTRIM(column_name, 'N'))-1)*7,'dd mm')||'||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
 --dbms_output.enable;
--dbms_output.put_line('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:' ||i_code||' AND god=:' ||i_year);
--l_res:=('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:' ||i_code||' AND god=:' ||i_year);
--return l_res;
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
return l_res;
END;