265 lines
8.0 KiB
Plaintext
265 lines
8.0 KiB
Plaintext
CREATE MATERIALIZED VIEW LOG ON act WITH PRIMARY KEY, ROWID, SEQUENCE(id_lucrare,dataact,nract,id_set,scc,scd,luna,an,suma,sters,explicatia)
|
|
INCLUDING NEW VALUES;
|
|
CREATE MATERIALIZED VIEW LOG ON rul WITH PRIMARY KEY, ROWID, SEQUENCE(id_lucrare,luna,an,pretv,cante,sters)
|
|
INCLUDING NEW VALUES;
|
|
CREATE MATERIALIZED VIEW LOG ON dev_oper WITH PRIMARY KEY, ROWID, SEQUENCE(id_ordl,datai,pret,timpn,nou,sters)
|
|
INCLUDING NEW VALUES;
|
|
|
|
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
|
|
/
|
|
|
|
create materialized view MV_ORDL_MAT
|
|
refresh force on commit
|
|
as
|
|
select id_lucrare,
|
|
luna + an * 12 as luni,
|
|
sum(case
|
|
when luna + an * 12 < 7 + 2005 * 12 then
|
|
round(round(pretv / 10000, 2) * cante, 2)
|
|
else
|
|
round(pretv * cante, 2)
|
|
end) as materiale_ron,
|
|
sum(case
|
|
when luna + an * 12 < 7 + 2005 * 12 then
|
|
round(pretv * cante, 2)
|
|
else
|
|
0
|
|
end) as materiale_rol,
|
|
count(*) as cnt,
|
|
count(case
|
|
when luna + an * 12 < 7 + 2005 * 12 then
|
|
round(round(pretv / 10000, 2) * cante, 2)
|
|
else
|
|
round(pretv * cante, 2)
|
|
end) as cnt_materiale_ron,
|
|
count(case
|
|
when luna + an * 12 < 7 + 2005 * 12 then
|
|
round(pretv * cante, 2)
|
|
else
|
|
0
|
|
end) as cnt_materiale_rol
|
|
from rul
|
|
where sters = 0
|
|
group by id_lucrare, luna + an * 12
|
|
/
|
|
|
|
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
|
|
/ |