begin
sys.dbms_scheduler.create_job(job_name => 'SYS.SMPRICES_51',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into supermag.SMCashQueue (enqseq,Locid,Article) select distinct rownum,p.locid,t.article from supermag.smprices t, supermag.smcashdesks p where t.pricetype=51 and t.savedprice is null and (p.locid, t.article) not in (select c.locid, c.article from supermag.smcashqueue c);
commit;
delete from supermag.smprices t where t.pricetype=51 and t.savedprice is null;
commit;',
start_date => to_date('27-01-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Minutely;Interval=15',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => '');
end;
/ create or replace trigger SMDocumentsMAPr
after insert or update of docstate ON SMDocuments
for each row
when (old.DocType = 'MA')
declare
vType integer;
begin
if (:old.docstate = 2) and (:new.docstate = 3) then
select distinct a.pricetype
into vType
from smauctionatoms a, svlocalshops l
where a.doctype = :old.doctype
and a.aucid = :old.id
and a.locid = l.ID and a.pricetype not in (список служебных видов цен);
if vType = 51 then
merge into supermag.smpricerArticle x
using (select distinct s.article,
pr.pricetype,
p.pricercategory,
p.pricertype
from supermag.smauctionatoms a,
supermag.smauctionpricer p,
supermag.smspec s,
supermag.smlocprices pr,
supermag.svlocalshops l
where a.doctype = :old.doctype
and a.aucid = :old.id
and :old.doctype = p.doctype
and :old.id = p.docid
and s.doctype = :old.doctype
and s.docid = :old.id
and a.locid = pr.locid
and l.ID = a.locid
and pr.flags = 3
and a.locid not in (список служебных мест хранения)
and pr.pricetype not in (список служебных видов цен, 51)) z
on (x.article = z.Article and x.pricetype = z.pricetype and x.pricercategory = z.pricercategory)
when matched then
update set x.pricertype = z.pricertype
when not matched then
insert
(x.article, x.pricetype, x.pricercategory, x.pricertype)
values
(z.article, z.pricetype, z.pricercategory, z.pricertype);
end if;
end if;
if (:old.docstate = 3) and (:new.docstate = 0) then
select distinct a.pricetype
into vType
from smauctionatoms a, svlocalshops l
where a.doctype = :old.doctype
and a.aucid = :old.id
and a.locid = l.ID and a.pricetype not in (список служебных видов цен);
if vType = 51 then
delete from supermag.smpricerarticle x
where (x.article, x.pricetype, x.pricercategory, x.pricertype) in
(select distinct s.article,
pr.pricetype,
p.pricercategory,
p.pricertype
from supermag.smauctionatoms a,
supermag.smauctionpricer p,
supermag.smspec s,
supermag.smlocprices pr
where a.doctype = :old.doctype
and a.aucid = :old.id
and :old.doctype = p.doctype
and :old.id = p.docid
and s.doctype = :old.doctype
and s.docid = :old.id
and a.locid = pr.locid
and pr.flags = 3
and a.locid not in (список служебных мест хранения)
and pr.pricetype not in (список служебных видов цен,51));
end if;
end if;
end;