Водка по 89р за бутылку : Супермаг Плюс (Супермаг 2000)

CREATE OR REPLACE PROCEDURE 
CUS_INSP_VODKA_PRICE(
                INDOCTYPE IN SMDOCUMENTS.DOCTYPE%TYPE
              , INDOCID IN SMDOCUMENTS.ID%TYPE
              , INOLDSTATE IN SMDOCUMENTS.DOCSTATE%TYPE := NULL
              , INNEWSTATE IN SMDOCUMENTS.DOCSTATE%TYPE := NULL
              , INDUMMY IN CORE.SMBOOL := NULL)
is
INLOCID SMDOCUMENTS.LOCATION%TYPE;
n integer;
msg1 varchar2(25555);
msg varchar2(25555);
buf varchar2(25555);
t number;
t1 number;
p number:=181.8;
c integer;
Begin

 INSPECT.Onstarttrans;
  
  
  if indoctype='MA' then
  
  select count(*) into c
from
 smlocprices p
 ,smauctionatoms a
 where a.doctype='MA'
 and a.aucid =INDOCID
  and p.locid=a.locid
  and p.pricetype=a.pricetype
  and p.flags=2;
  if c=0 then return; end if;
  
  end if;
  
  
  if indoctype='AC' then
  select count(*) into c
from
smdocuments d
 ,smacts a
  , smlocprices p
 where d.doctype='AC'
 and d.id =INDOCID
 and d.doctype=a.doctype
  and d.id=a.id
  and p.locid=d.location
  and p.pricetype=a.pricetype
  and p.flags=2;
  
  if c=0 then return; end if;
  
  end if;
  
  
    if indoctype='AC' then
  
  select count(*) into c
from
 smlocprices p
 ,smauctionatoms a
 where a.doctype='MA'
 and a.aucid =INDOCID
  and p.locid=a.locid
  and p.pricetype=a.pricetype
  and p.flags=2;
  if c=0 then return; end if;
  
  end if;
  
  
  
   
  
  insert INTO TTINSPECTRESBUFFER
       ( INSPECTID, ERRID, INSPECTNAME, ERRTEXT )
  select 994, 0,  'Проверка мин. цены Водки'  , 'Артикул "'||r.article||'", не имеет альтернативной единицы измерения!'
 from
smcard r
,sacardclass c
,supermag.smaltunits u
,smspec s
where c.id=r.idclass
and c.tree = '1.5.5.'
and u.altunit(+)=2
and u.article(+)=r.article
and u.rate is null
and s.article=r.article
and s.doctype=INDOCTYPE
and s.docid=INDOCID
;

 
  insert INTO TTINSPECTRESBUFFER
       ( INSPECTID, ERRID, INSPECTNAME, ERRTEXT )
  select 994, 0,  'Проверка мин. цены Водки' , 'Цена "'||r.article||'", менее 181,8р. за 1 литр!'
 from
smcard r
,sacardclass c
,supermag.smaltunits u
,smspec s
where c.id=r.idclass
and c.tree = '1.5.5.'
and u.altunit=2
and u.article=r.article
and s.article=r.article
and s.doctype=INDOCTYPE
and s.docid=INDOCID
and s.itemprice/u.rate<p;



    exception  
              when others then raise_application_error(-20999, 'Произошла ошибка во время выполнения проверки 994: '||SQLERRM);
 


 -- INSPECT.SETFUNCNAME( 994 );

END CUS_INSP_VODKA_PRICE;
/


insert into ssinspectfunc(id,name,definspectmode) values (994,'Проверка цены Водки',2);

insert into ssinspectdoc (doctype,docstate,docstatebefore,inspectid,inspectoraname,dobefore)
 values ('AC',2,1,994,'CUS_INSP_VODKA_PRICE',1);
insert into ssinspectdoc (doctype,docstate,docstatebefore,inspectid,inspectoraname,dobefore)
 values ('MA',2,1,994,'CUS_INSP_VODKA_PRICE',1);

commit;

create or replace trigger CUS_TRG_VODKA_PRICE
  before insert or update on smprices
  for each row
declare
p number:=178;
o number;
pra number;
prg number;
m number:=1;
t number;
begin

  select count(l.flags) into o from
     smlocprices l
     where l.locid=:new.storeloc
       and l.pricetype=:new.pricetype;
       if o=0 then return; end if;
       
       select  l.flags into o from
     smlocprices l
     where l.locid=:new.storeloc
       and l.pricetype=:new.pricetype;
       if o!=2 then return; end if;

     select count(*) into o
       from smcard r
       , sacardclass s
       where s.id=r.idclass
        and s.tree='1.5.5.'
        and r.article=:new.article;
        if o=0 then return; end if;

    
    select nvl(max(percent),0) into pra
      from smdisclimits t 
where t.article=:new.article and priceid=:new.pricetype;

select nvl(max(percent),0) into prg
 from smdisclimitsclass t 
 ,smcard r
where t.idclass=r.idclass
and r.article=:new.article
and t.priceid=:new.pricetype;


        select nvl(u.rate,0) into o
        from
smcard r
 ,supermag.smaltunits u
 where r.article=:new.article
and u.altunit(+)=2
and u.article(+)=r.article;

if o=0 then
raise_application_error(-20994, 'Артикул '||:new.article||', не имеет альтернативной единицы измерения!');
 end if;


if prg!=0 then m:=prg; end if;
if pra!=0 then m:=pra; end if;

select decode(m,1,1,(100-m)/100) into t from dual;


   if :new.price/o*t<p then
   raise_application_error(-20994, 'Цена "'||:new.article||'", менее 181,8р. за 1 литр!');
 end if;

 exception when others   then
     raise;

end CUS_TRG_VODKA_PRICE;
/