SHARE
TWEET

Untitled

a guest Jun 16th, 2019 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION calc_overhead_days(ID IN NUMBER)
  2. RETURN NUMBER
  3. AS
  4.   gaps INTEGER;
  5.   srok INTEGER;
  6.   planned_date DATE;
  7.   srok_type VARCHAR2(2);
  8.  
  9.   timing_sql VARCHAR2(100);
  10.   gaps_sql VARCHAR2(1000);
  11.  
  12.   timing_rec timing%ROWTYPE;
  13.  
  14. BEGIN
  15.   timing_sql := 'select * from timing where id_documents = :ID';
  16.   --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) )';
  17.     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';
  18.  
  19. EXECUTE immediate timing_sql INTO timing_rec USING ID;
  20. EXECUTE immediate gaps_sql INTO gaps USING ID, ID;
  21.   srok := to_number(substr(timing_rec.srok,2,3));
  22.   srok_type := substr(timing_rec.srok, -2, 2);
  23.   CASE srok_type
  24.     WHEN 'КД' THEN
  25.       RETURN trunc(timing_rec.ispol_data - timing_rec.rn_data) - (srok + gaps);
  26.    
  27.     WHEN 'РД' THEN
  28.       planned_date := ADDWORKDAYLOCAL(timing_rec.rn_data,srok);
  29.       RETURN trunc(timing_rec.ispol_data - planned_date) - gaps;
  30.      
  31.     WHEN 'НД' THEN
  32.       planned_date := fixnormativedate(timing_rec.rn_data + srok);
  33.       RETURN trunc(timing_rec.ispol_data - planned_date) - gaps;
  34.     ELSE
  35.       RETURN -32768;
  36.   END CASE;
  37. END;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top