Доработка, в сообщении об ошибке показывается артикул с максимальным превышением (заказали тут):
Код:
CREATE OR REPLACE TRIGGER supermag.smdocuments_ok_orwi
BEFORE INSERT OR UPDATE
ON supermag.smdocuments
FOR EACH ROW
WHEN (NEW.doctype = 'WI' AND NEW.docstate > 1 AND NEW.opcode=0)
DECLARE
lidx NUMBER (20) := 0;
art VARCHAR(200) :=' ';
BEGIN
SELECT MAX (quantity_wi - quantity_or) idx
INTO lidx
FROM (SELECT article article_or, SUM (quantity) quantity_or
FROM smspec
WHERE (doctype, docid) IN (
SELECT basedoctype, baseid
FROM smcommonbases
WHERE basedoctype = 'OR'
AND doctype = 'WI'
AND ID = :NEW.ID)
GROUP BY article) zk,
(SELECT article article_wi, SUM (quantity) quantity_wi
FROM smspec
WHERE (doctype, docid) IN (
SELECT doctype, ID
FROM smcommonbases
WHERE (basedoctype, baseid) IN (
SELECT basedoctype, baseid
FROM smcommonbases
WHERE basedoctype = 'OR'
AND doctype = 'WI'
AND ID = :NEW.ID))
GROUP BY article) pr
WHERE article_or = article_wi;
IF lidx > 0
THEN
select (' По артикулу '||article_or||' превышение на '||idx)
into art
from (
SELECT article_or,(quantity_wi - quantity_or) idx
FROM (SELECT article article_or, SUM (quantity) quantity_or
FROM smspec
WHERE (doctype, docid) IN (
SELECT basedoctype, baseid
FROM smcommonbases
WHERE basedoctype = 'OR'
AND doctype = 'WI'
AND ID = :NEW.ID)
GROUP BY article) zk,
(SELECT article article_wi, SUM (quantity) quantity_wi
FROM smspec
WHERE (doctype, docid) IN (
SELECT doctype, ID
FROM smcommonbases
WHERE (basedoctype, baseid) IN (
SELECT basedoctype, baseid
FROM smcommonbases
WHERE basedoctype = 'OR'
AND doctype = 'WI'
AND ID = :NEW.ID))
GROUP BY article) pr
WHERE article_or = article_wi order by 2 desc) where rownum<2;
raise_application_error (-20000,
'Кол-во в накладных больше кол-ва в заказе.'||art||' '
);
END IF;
END smdocuments_ok_orwi;
/