--------------------------------------------------------
-- View-uri materializate pentru ROAAUTO v 1.4.151    --
-- Created by marius.atanasiu on 2/2/2006, 9:43:11 AM --
--------------------------------------------------------

spool viewuri_materializate.log

PROMPT
PROMPT	Drop view-uri materializate
PROMPT	===================================
PROMPT
begin 
	begin    
		execute immediate 'drop view MV_ORDL_MAT';
	exception
	  when others then
		null;
	end;
	
	begin    
		execute immediate 'drop view mv_ordl_man';
	exception
	  when others then
		null;
	end;

	begin    
		execute immediate 'drop view mv_act_suma';
	exception
	  when others then
		null;
	end;
	
	begin    
		execute immediate 'drop view mv_ordl_sume_act';
	exception
	  when others then
		null;
	end;

	begin    
		execute immediate 'drop table mlog$_rul';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mlog$_rul1';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop materialized view log on RUL';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'DROP MATERIALIZED VIEW MV_ORDL_MAT';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'DROP TABLE MV_ORDL_MAT';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mlog$_dev_oper';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mlog$_dev_oper1';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop materialized view log on dev_oper';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop MATERIALIZED VIEW mv_ordl_man';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mv_ordl_man';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mlog$_act';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mlog$_act1';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop materialized view log on ACT';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop MATERIALIZED VIEW mv_ordl_sume_act';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'drop table mv_ordl_sume_act';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'DROP materialized view MV_ACT_SUMA';
	exception
	  when others then
		null;
	end;
	begin    
		execute immediate 'DROP table MV_ACT_SUMA';
	exception
	  when others then
		null;
	end;
end;
/
		
PROMPT
PROMPT	Creare log view materializat pe act
PROMPT	===================================
PROMPT
CREATE MATERIALIZED VIEW LOG ON act WITH PRIMARY KEY, ROWID, SEQUENCE(id_lucrare,dataact,nract,id_set,scc,scd,luna,an,suma,sters,explicatia,id_sucursala) INCLUDING NEW VALUES
/

PROMPT
PROMPT	Creare view materializat mv_ordl_sume_act
PROMPT	=========================================
PROMPT
create materialized view MV_ORDL_SUME_ACT
    refresh fast on commit
    as
    select id_lucrare,dataact,nract,id_set,sum(case when scc='704' then (case when luna+an*12<7+2005*12 then round(suma/10000,2) else
    suma end) when explicatia like 'DISCOUNT MANOPERA%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then round((-1)*(abs(suma))/10000,2) else (-1)*(abs(suma)) end) else 0 end) as manopera_ron, sum(case when scc in ('707','419') then (case when luna+an*12<7+2005*12 then round(suma/10000,2) else suma end)
             when explicatia like 'DISCOUNT MATERIALE%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then round((-1)*(abs(suma))/10000,2) else (-1)*(abs(suma)) end)
             else 0 end) as materiale_ron,sum(case when scc='4427' then (case when luna+an*12<7+2005*12 then round(suma/10000,2)
    else suma end) else 0 end) as tva_ron,sum(case when scc='704' then (case when luna+an*12<7+2005*12 then suma else 0 end) when explicatia like 'DISCOUNT MANOPERA%' and scc<>'4427' then (case when
    luna+an*12<7+2005*12 then (-1)*(abs(suma)) else 0 end) else 0 end) as manopera_rol,sum(case when scc in ('707','419') then (case when luna+an*12<7+2005*12 then suma else 0
    end) when explicatia like 'DISCOUNT MATERIALE%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then (-1)*(abs(suma)) else 0 end) else 0 end) as materiale_rol,
    sum(case when scc='4427' then (case when luna+an*12<7+2005*12 then suma else 0 end) else 0 end) as tva_rol,count(*) as cnt,
    count(case when scc='704' then (case when luna+an*12<7+2005*12 then round(suma/10000,2)
    else suma end) when explicatia like 'DISCOUNT MANOPERA%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then round((-1)*(abs(suma))/10000,2) else (-1)*(abs(suma)) end)
            else 0 end) as cnt_manopera_ron,count(case when scc in ('707','419') then (case when luna+an*12<7+2005*12 then round(suma/10000,2) else suma end)
             when explicatia like 'DISCOUNT MATERIALE%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then round((-1)*(abs(suma))/10000,2) else (-1)*(abs(suma)) end)
             else 0 end) as cnt_materiale_ron,count(case when scc='4427' then (case when luna+an*12<7+2005*12 then round(suma/10000,2)
    else suma end) else 0 end) as cnt_tva_ron,count(case when scc='704' then (case when luna+an*12<7+2005*12 then suma else 0 end)
             when explicatia like 'DISCOUNT MANOPERA%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then (-1)*(abs(suma)) else 0 end)
             else 0 end) as cnt_manopera_rol,count(case when scc in ('707','419') then (case when luna+an*12<7+2005*12 then suma else 0
    end) when explicatia like 'DISCOUNT MATERIALE%' and scc<>'4427' then (case when luna+an*12<7+2005*12 then (-1)*(abs(suma)) else 0 end)
             else 0 end) as cnt_materiale_rol,count(case when scc='4427' then (case when luna+an*12<7+2005*12 then suma else 0 end)
             else 0 end) as cnt_tva_rol from act where sters=0 and scd in ('4111', '667', '482', '711', '6588') and id_set in (31001, 31002, 31003, 31004, 31005, 31006, 31007, 31011, 31012) group by id_lucrare,dataact,nract,id_set
/

PROMPT
PROMPT	Creare view materializat MV_ACT_SUMA
PROMPT	=========================================
PROMPT
create materialized view MV_ACT_SUMA
      REFRESH FAST ON COMMIT 
      AS
      select an, luna, id_sucursala,
         sum(case when TO_NUMBER(SUBSTR(SCD, 1, 1)) between 1 and 7 then suma else 0 end) as sumaD,
         sum(case when TO_NUMBER(SUBSTR(SCC, 1, 1)) between 1 and 7 then suma else 0 end) as sumaC,
         sum(case when TO_NUMBER(SUBSTR(SCD, 1, 1)) = 8 then suma else 0 end) as sumaD_8,
         sum(case when TO_NUMBER(SUBSTR(SCC, 1, 1)) = 8 then suma else 0 end) as sumaC_8,
         sum(case when TO_NUMBER(SUBSTR(SCD, 1, 1)) = 9 then suma else 0 end) as sumaD_9,
         sum(case when TO_NUMBER(SUBSTR(SCC, 1, 1)) = 9 then suma else 0 end) as sumaC_9,
         count(*) as nr,
         count(case when TO_NUMBER(SUBSTR(SCD, 1, 1)) between 1 and 7 then suma else 0 end) as nrsumaD,
         count(case when TO_NUMBER(SUBSTR(SCC, 1, 1)) between 1 and 7 then suma else 0 end) as nrsumaC,
         count(case when TO_NUMBER(SUBSTR(SCD, 1, 1)) = 8 then suma else 0 end) as nrsumaD_8,
         count(case when TO_NUMBER(SUBSTR(SCC, 1, 1)) = 8 then suma else 0 end) as nrsumaC_8,
         count(case when TO_NUMBER(SUBSTR(SCD, 1, 1)) = 9 then suma else 0 end) as nrsumaD_9,
         count(case when TO_NUMBER(SUBSTR(SCC, 1, 1)) = 9 then suma else 0 end) as nrsumaC_9
         from act
         where sters = 0
         group by an, luna, id_sucursala
/		 
		 
PROMPT
PROMPT	Creare log view materializat pe dev_oper
PROMPT	========================================
PROMPT
CREATE MATERIALIZED VIEW LOG ON dev_oper WITH PRIMARY KEY,ROWID, SEQUENCE(id_ordl,datai,pret,timpn,nou,sters) INCLUDING NEW VALUES
/

PROMPT
PROMPT	Creare view materializat mv_ordl_man
PROMPT	====================================
PROMPT
create materialized view MV_ORDL_MAN refresh fast on commit as 
	select id_ordl,extract(month from datai) + extract(year from datai) * 12 as luni,
	sum(case
             when nou = 0 then
              round(round(pret / 10000, 2) * timpn, 2)
             else
              round(pret * timpn, 2)
           end) as manopera_ron,
       sum(case
             when nou = 0 then
              round(pret * timpn, 2)
             else
              0
           end) as manopera_rol,
       count(*) as cnt,
       count(case
               when nou = 0 then
                round(round(pret / 10000, 2) * timpn, 2)
               else
                round(pret * timpn, 2)
             end) as cnt_manopera_ron,
       count(case
               when nou = 0 then
                round(pret * timpn, 2)
               else
                0
             end) as cnt_manopera_rol
  from dev_oper
 where sters = 0
 group by id_ordl,
          extract(month from datai) + extract(year from datai) * 12
/

PROMPT
PROMPT	Creare log view materializat pe rul
PROMPT	===================================
PROMPT
CREATE MATERIALIZED VIEW LOG ON RUL WITH ROWID, SEQUENCE(AN,LUNA,PRETV,CANTE,ID_LUCRARE,STERS) INCLUDING NEW VALUES;
/

PROMPT
PROMPT	Creare view materializat mv_ordl_mat
PROMPT	====================================
PROMPT
CREATE MATERIALIZED VIEW MV_ORDL_MAT 
REFRESH FAST ON COMMIT AS
SELECT STERS,
       ID_LUCRARE,
       LUNA,
       AN,
       SUM(CASE
             WHEN LUNA + AN * 12 < 7 + 2005 * 12 THEN
              ROUND(PRETV * CANTE, 2)
             ELSE
              0
           END) materiale_rol,
       COUNT(CASE
               WHEN LUNA + AN * 12 < 7 + 2005 * 12 THEN
                ROUND(PRETV * CANTE, 2)
               ELSE
                0
             END) cnt_materiale_rol,
       SUM(CASE
             WHEN LUNA + AN * 12 < 7 + 2005 * 12 THEN
              ROUND(ROUND(PRETV / 10000, 2) * CANTE, 2)
             ELSE
              ROUND(PRETV * CANTE, 2)
           END) materiale_ron,
       COUNT(CASE
               WHEN LUNA + AN * 12 < 7 + 2005 * 12 THEN
                ROUND(ROUND(PRETV / 10000, 2) * CANTE, 2)
               ELSE
                ROUND(PRETV * CANTE, 2)
             END) cnt_materiale_ron,
       COUNT(*) cnt
  FROM RUL
 WHERE (STERS = 0)
 GROUP BY STERS, ID_LUCRARE, LUNA, AN
/

spool off