create or replace trigger l_checks_o_upd before update of pur_item,check_qty ,yh_qty on l_checks_o for each row declare -- local variables here v_purplan varchar2(16); v_mk varchar2(1); v_all_mk varchar2(1); v_mk_o varchar2(1); v_all_mk_o varchar2(1); v_count number; v_pur_qty number; v_yh_qty number; v_lv_qty number; v_lvyh_qty number; v_pur_qty_o number; v_yh_qty_o number; v_lv_qty_o number; v_lvyh_qty_o number;
begin select purplan_no into v_purplan from l_checkm_o where fact_no = :new.fact_no and check_no = :new.check_no; if :old.pur_item != :new.pur_item then select nvl(pur_qty,0), nvl(yh_qty,0),nvl(mk,'N'),nvl(all_mk,'N') into v_pur_qty_o, v_yh_qty_o, v_mk_o, v_all_mk_o from l_purplans_o where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; select nvl(qty,0) ,nvl(yh_qty,0) into v_lv_qty_o,v_lvyh_qty_o from lv_check where fact_no = :old.fact_no and purplan_no = v_purplan and pur_item = :old.pur_item; select nvl(pur_qty,0), nvl(yh_qty,0),nvl(mk,'N'),nvl(all_mk,'N') into v_pur_qty, v_yh_qty, v_mk, v_all_mk from l_purplans_o where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item; select nvl(sum(fact_no),0) into v_count from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item; if v_count = 0 then v_lv_qty := 0; v_lvyh_qty := 0; else select nvl(qty,0) ,nvl(yh_qty,0) into v_lv_qty,v_lvyh_qty from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item; end if; if v_all_mk = 'Y' then RAISE_APPLICATION_ERROR(-20603,'此采購單的此采購項次已全部驗收入庫,無須修改!'); elsif v_mk = 'Y' then if v_lv_qty + :NEW.check_qty > v_pur_qty or v_lvyh_qty + :new.yh_qty > v_yh_qty then RAISE_APPLICATION_ERROR(-20604,'此采購單的此采購項次驗收入庫大于采購數量,無法修改!'); elsif v_lv_qty + :NEW.check_qty = v_pur_qty and v_lvyh_qty + :new.yh_qty = v_yh_qty then update l_purplans_o set all_mk = 'Y' where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item; end if; else if :NEW.check_qty > v_pur_qty or :new.yh_qty > v_yh_qty then RAISE_APPLICATION_ERROR(-20604,'此采購單的此采購項次驗收入庫大于采購數量,無法修改!'); elsif :NEW.check_qty = v_pur_qty and :new.yh_qty = v_yh_qty then update l_purplans_o set mk = 'Y',all_mk = 'Y' where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item; else update l_purplans_o set mk = 'Y' where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item; end if ; end if; if v_lv_qty_o - :old.check_qty = v_pur_qty_o and v_lvyh_qty_o - :old.yh_qty = v_yh_qty_o then if v_all_mk_o = 'N' or v_mk_o = 'N' then update l_purplans_o set mk = 'Y',all_mk = 'Y' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; end if; elsif v_lv_qty_o - :old.check_qty = 0 and v_lvyh_qty_o - :old.yh_qty = 0 then if v_all_mk_o = 'Y' or v_mk_o = 'Y' then update l_purplans_o set mk = 'N',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; end if ; elsif (v_lv_qty_o - :old.check_qty <= v_pur_qty_o and v_lv_qty_o - :old.check_qty >= 0 ) or (v_lvyh_qty_o - :old.yh_qty <= v_yh_qty_o and v_lvyh_qty_o - :old.yh_qty >= 0 )then if v_all_mk_o = 'Y' or v_mk_o = 'N' then update l_purplans_o set mk = 'Y',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; end if ; else RAISE_APPLICATION_ERROR(-20603,'此采購單的此采購項次驗收入庫大于采購數量或少于0,無法修改!'); end if ; else select nvl(pur_qty,0), nvl(yh_qty,0),nvl(mk,'N'),nvl(all_mk,'N') into v_pur_qty, v_yh_qty, v_mk, v_all_mk from l_purplans_o where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item; select nvl(sum(fact_no),0) into v_count from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item; if v_count = 0 then v_lv_qty := 0; v_lvyh_qty := 0; else select nvl(qty,0) ,nvl(yh_qty,0) into v_lv_qty,v_lvyh_qty from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item; end if; if v_lv_qty + :new.check_qty - :old.check_qty = v_pur_qty and v_lvyh_qty + :new.yh_qty - :old.yh_qty = v_yh_qty then if v_all_mk = 'N' or v_mk = 'N' then update l_purplans_o set mk = 'Y',all_mk = 'Y' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; end if; elsif v_lv_qty + :new.check_qty - :old.check_qty = 0 and v_lvyh_qty + :new.yh_qty - :old.yh_qty = 0 then if v_all_mk = 'Y' or v_mk = 'Y' then update l_purplans_o set mk = 'N',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; end if ; elsif (v_lv_qty + :new.check_qty - :old.check_qty <= v_pur_qty and v_lv_qty + :new.check_qty - :old.check_qty >= 0 ) or (v_lvyh_qty + :new.yh_qty - :old.yh_qty <= v_yh_qty and v_lvyh_qty + :new.yh_qty - :old.yh_qty >= 0 )then if v_all_mk = 'Y' or v_mk = 'N' then update l_purplans_o set mk = 'Y',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item; end if ; else RAISE_APPLICATION_ERROR(-20603,'此采購單的此采購項次驗收入庫大于采購數量或少于0,無法修改!'); end if ; end if; end ;其中lv_check 是l_checks_o的視圖
before update of pur_item,check_qty ,yh_qty on l_checks_o
for each row
declare
-- local variables here
v_purplan varchar2(16);
v_mk varchar2(1);
v_all_mk varchar2(1);
v_mk_o varchar2(1);
v_all_mk_o varchar2(1);
v_count number;
v_pur_qty number;
v_yh_qty number;
v_lv_qty number;
v_lvyh_qty number;
v_pur_qty_o number;
v_yh_qty_o number;
v_lv_qty_o number;
v_lvyh_qty_o number;
begin
select purplan_no into v_purplan from l_checkm_o where fact_no = :new.fact_no and check_no = :new.check_no;
if :old.pur_item != :new.pur_item then
select nvl(pur_qty,0), nvl(yh_qty,0),nvl(mk,'N'),nvl(all_mk,'N') into v_pur_qty_o, v_yh_qty_o, v_mk_o, v_all_mk_o from l_purplans_o where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
select nvl(qty,0) ,nvl(yh_qty,0) into v_lv_qty_o,v_lvyh_qty_o from lv_check where fact_no = :old.fact_no and purplan_no = v_purplan and pur_item = :old.pur_item;
select nvl(pur_qty,0), nvl(yh_qty,0),nvl(mk,'N'),nvl(all_mk,'N') into v_pur_qty, v_yh_qty, v_mk, v_all_mk from l_purplans_o where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item;
select nvl(sum(fact_no),0) into v_count from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item;
if v_count = 0 then
v_lv_qty := 0;
v_lvyh_qty := 0;
else
select nvl(qty,0) ,nvl(yh_qty,0) into v_lv_qty,v_lvyh_qty from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item;
end if;
if v_all_mk = 'Y' then
RAISE_APPLICATION_ERROR(-20603,'此采購單的此采購項次已全部驗收入庫,無須修改!');
elsif v_mk = 'Y' then
if v_lv_qty + :NEW.check_qty > v_pur_qty or v_lvyh_qty + :new.yh_qty > v_yh_qty then
RAISE_APPLICATION_ERROR(-20604,'此采購單的此采購項次驗收入庫大于采購數量,無法修改!');
elsif v_lv_qty + :NEW.check_qty = v_pur_qty and v_lvyh_qty + :new.yh_qty = v_yh_qty then
update l_purplans_o set all_mk = 'Y' where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item;
end if;
else
if :NEW.check_qty > v_pur_qty or :new.yh_qty > v_yh_qty then
RAISE_APPLICATION_ERROR(-20604,'此采購單的此采購項次驗收入庫大于采購數量,無法修改!');
elsif :NEW.check_qty = v_pur_qty and :new.yh_qty = v_yh_qty then
update l_purplans_o set mk = 'Y',all_mk = 'Y' where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item;
else
update l_purplans_o set mk = 'Y' where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item;
end if ;
end if;
if v_lv_qty_o - :old.check_qty = v_pur_qty_o and v_lvyh_qty_o - :old.yh_qty = v_yh_qty_o then
if v_all_mk_o = 'N' or v_mk_o = 'N' then
update l_purplans_o set mk = 'Y',all_mk = 'Y' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
end if;
elsif v_lv_qty_o - :old.check_qty = 0 and v_lvyh_qty_o - :old.yh_qty = 0 then
if v_all_mk_o = 'Y' or v_mk_o = 'Y' then
update l_purplans_o set mk = 'N',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
end if ;
elsif (v_lv_qty_o - :old.check_qty <= v_pur_qty_o and v_lv_qty_o - :old.check_qty >= 0 ) or (v_lvyh_qty_o - :old.yh_qty <= v_yh_qty_o and v_lvyh_qty_o - :old.yh_qty >= 0 )then
if v_all_mk_o = 'Y' or v_mk_o = 'N' then
update l_purplans_o set mk = 'Y',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
end if ;
else
RAISE_APPLICATION_ERROR(-20603,'此采購單的此采購項次驗收入庫大于采購數量或少于0,無法修改!');
end if ;
else
select nvl(pur_qty,0), nvl(yh_qty,0),nvl(mk,'N'),nvl(all_mk,'N') into v_pur_qty, v_yh_qty, v_mk, v_all_mk from l_purplans_o where fact_no = :new.fact_no and purplan_no = v_purplan and item_no = :new.pur_item;
select nvl(sum(fact_no),0) into v_count from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item;
if v_count = 0 then
v_lv_qty := 0;
v_lvyh_qty := 0;
else
select nvl(qty,0) ,nvl(yh_qty,0) into v_lv_qty,v_lvyh_qty from lv_check where fact_no = :new.fact_no and purplan_no = v_purplan and pur_item = :new.pur_item;
end if;
if v_lv_qty + :new.check_qty - :old.check_qty = v_pur_qty and v_lvyh_qty + :new.yh_qty - :old.yh_qty = v_yh_qty then
if v_all_mk = 'N' or v_mk = 'N' then
update l_purplans_o set mk = 'Y',all_mk = 'Y' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
end if;
elsif v_lv_qty + :new.check_qty - :old.check_qty = 0 and v_lvyh_qty + :new.yh_qty - :old.yh_qty = 0 then
if v_all_mk = 'Y' or v_mk = 'Y' then
update l_purplans_o set mk = 'N',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
end if ;
elsif (v_lv_qty + :new.check_qty - :old.check_qty <= v_pur_qty and v_lv_qty + :new.check_qty - :old.check_qty >= 0 ) or (v_lvyh_qty + :new.yh_qty - :old.yh_qty <= v_yh_qty and v_lvyh_qty + :new.yh_qty - :old.yh_qty >= 0 )then
if v_all_mk = 'Y' or v_mk = 'N' then
update l_purplans_o set mk = 'Y',all_mk = 'N' where fact_no = :old.fact_no and purplan_no = v_purplan and item_no = :old.pur_item;
end if ;
else
RAISE_APPLICATION_ERROR(-20603,'此采購單的此采購項次驗收入庫大于采購數量或少于0,無法修改!');
end if ;
end if;
end ;其中lv_check 是l_checks_o的視圖