CREATE OR REPLACE FUNCTION calc_overhead_days(ID IN NUMBER)
RETURN NUMBER
AS
  gaps INTEGER;
  srok INTEGER;
  planned_date DATE;
  srok_type VARCHAR2(2);

  timing_sql VARCHAR2(100);
  gaps_sql VARCHAR2(1000);

  timing_rec timing%ROWTYPE;

BEGIN
  timing_sql := 'select * from timing where id_documents = :ID';
  --gaps_sql :=  'select trunc(sum(Data_real - Data_Plan)) from (select Trunc(Data_Start) as Data_Plan,Trunc(Data_Real) as Data_Real  from etaps_doc d, etaps_techproc t, g_etaps g  where d.id_documents=:ID  and Data_Plan is not null   and d.id_etaptechproc=t.id_etaptechproc and t.id_etap=g.id_etap and g.type_etap in (4,7) union select Trunc(d.Data_Start) as Data_Plan, Trunc(d.Data_Real) as Data_Real from etaps_version d, etaps_techproc t, g_etaps g where d.id_documents=:ID and Data_Plan is not null and Data_Real is not null and d.id_etaptechproc=t.id_etaptechproc and t.id_etap=g.id_etap and g.type_etap in (4,7) )';
    gaps_sql :=  'select trunc(sum(Data_real - Data_Plan)) from (select Trunc(Data_Start) as Data_Plan,Trunc(Data_Real) as Data_Real  from etaps_doc d, etaps_techproc t, g_etaps g  where d.id_documents=:ID  and Data_Plan is not null   and d.id_etaptechproc=t.id_etaptechproc and t.id_etap=g.id_etap and g.type_etap in (4,7) union select Trunc(d.Data_Start) as Data_Plan, Trunc(d.Data_Real) as Data_Real from etaps_version d, etaps_techproc t, g_etaps g where d.id_documents=:ID and Data_Plan is not null and Data_Real is not null and d.id_etaptechproc=t.id_etaptechproc and t.id_etap=g.id_etap and g.type_etap in (4,7) ) where data_real is not null and data_plan is not null';

EXECUTE immediate timing_sql INTO timing_rec USING ID;
EXECUTE immediate gaps_sql INTO gaps USING ID, ID;
  srok := to_number(substr(timing_rec.srok,2,3));
  srok_type := substr(timing_rec.srok, -2, 2);
  CASE srok_type
    WHEN 'КД' THEN
      RETURN trunc(timing_rec.ispol_data - timing_rec.rn_data) - (srok + gaps);

    WHEN 'РД' THEN
      planned_date := ADDWORKDAYLOCAL(timing_rec.rn_data,srok);
      RETURN trunc(timing_rec.ispol_data - planned_date) - gaps;

    WHEN 'НД' THEN
      planned_date := fixnormativedate(timing_rec.rn_data + srok);
      RETURN trunc(timing_rec.ispol_data - planned_date) - gaps;
    ELSE
      RETURN -32768;
  END CASE;
END;