117
create or replace trigger BI_Serials
before insert on serials
for each row
begin
select SeqSerials.Nextval into :new.IdSerial from dual;
end BI_Serials;
create or replace trigger BI_Tarifs
before insert on tarifs
for each row
begin
select SeqTarifs.Nextval into :new.IdTarif from dual;
end BI_Tarifs;
Запрос для вывода полиса на печать
select o.owner,
o.serial,
o.polisno,
o.polisdate,
o.polissubject,
o.suminsured,
o.cash,
o.datebegin,
o.dateend,
o.dateout,
o.sumacc,
o.fio,
o.pass,
o.address,
max(o.por1) as por1,
max(o.por2) as por2,
max(o.por3) as por3
from (select rownum,
i.owner,
118
i.serial,
i.polisno,
i.polisdate,
i.polissubject,
i.suminsured,
i.cash,
i.datebegin,
i.dateend,
i.dateout,
i.sumacc,
i.fio,
i.pass,
i.address,
decode(rownum,
1,
c.familyname || ' ' || c.firstname || ' ' ||
c.secondname) as por1,
decode(rownum,
2,
c.familyname || ' ' || c.firstname || ' ' ||
c.secondname) as por2,
decode(rownum,
3,
c.familyname || ' ' || c.firstname || ' ' ||
c.secondname) as por3
from (select o.owner,
s.serial,
p.polisno,
p.polisdate,
p.polissubject,
p.suminsured,
p.cash,
119
p.datebegin,
p.dateend,
p.dateout,
p.sumacc,
c.familyname || ' ' || c.firstname || ' ' ||
c.secondname as fio,
'СЕРИЯ ' || c.passserial || ' № ' || c.passno ||
', ВЫДАН ' || c.whengive || ' ГОДА, ' || c.whogive as pass,
c.address,
c.idclient
from owners o, polises p, serials s, tarifs t, clients c
where p.idserial = s.idserial
and p.idtarif = t.idtarif
and p.idowner = o.idowner
and p.idclient = c.idclient
and p.idpolis = :idp) i,
clients c
where c.idclientchild = i.idclient) o
group by o.owner,
o.serial,
o.polisno,
o.polisdate,
o.polissubject,
o.suminsured,
o.cash,
o.datebegin,
o.dateend,
o.dateout,
o.sumacc,
o.fio,
o.pass,
o.address