oracle做公司进销存系统,一个朋友帮忙写的基于Oracle 实现进销存的触发器

CREATE OR REPLACE TRIGGER TRG_cux_wms_transaction_line

before insert or update or delete on cux_wms_transaction_line

REFERENCING NEW AS NEW OLD AS OLD

for each row

declare

ai_in_count number;

ai_out_count number;

ai_abs number;

--源仓区游标

Cursor temp_in_cursor is

select count(*)

from cux_wms_stock

where :new.organization_id = cux_wms_stock.organization_id and

:new.org_id = cux_wms_stock.org_id and

:new.INVENTORY_CODE = cux_wms_stock.INVENTORY_CODE and

:new.splint_barcode = cux_wms_stock.splint_barcode and

:new.item_barcode = cux_wms_stock.item_barcode and

:new.batchno= cux_wms_stock.batch_number and

:new.source_loc_code = cux_wms_stock.location_code;

--目标仓区游标

Cursor temp_out_cursor is

select count(*)

from cux_wms_stock

where :new.organization_id = cux_wms_stock.organization_id and

:new.org_id = cux_wms_stock.org_id and

:new.INVENTORY_CODE = cux_wms_stock.INVENTORY_CODE and

:new.splint_barcode = cux_wms_stock.splint_barcode and

:new.item_barcode = cux_wms_stock.item_barcode and

:new.batchno= cux_wms_stock.batch_number and

:new.intent_loc_code = cux_wms_stock.location_code;

begin

--设定计算方向

--case :new.operation_Mark;

--    when 'I' then ai_abs := 1;

--    when 'O' then ai_abs := -1;

--    when 'M' then ai_abs := 1;

--    else ai_abs := 0;

--end case;

ai_abs := 1;

if :new.operation_Mark = 'I' then--设定计算方向

ai_abs := 1;

end if;

if :new.operation_Mark = 'O' then--设定计算方向

ai_abs := -1;

end if;

if :new.operation_Mark = 'M' then--设定计算方向

ai_abs := -1;

end if;

if :new.operation_Mark = 'C' then--设定计算方向

ai_abs := 0;

end if;

--打开源仓区的游标

open temp_in_cursor;

fetch temp_in_cursor into ai_in_count;

if ai_in_count > 0 then

update cux_wms_stock

set cux_wms_stock.quantity = cux_wms_stock.quantity + :new.CASE_NUMBER * ai_abs,LAST_UPDATE_DATE=(select sysdate from dual),LAST_UPDATED_BY=:new.LAST_UPDATED_BY

where :new.organization_id = cux_wms_stock.organization_id and

:new.org_id = cux_wms_stock.org_id and

:new.INVENTORY_CODE = cux_wms_stock.INVENTORY_CODE and

:new.splint_barcode = cux_wms_stock.splint_barcode and

:new.item_barcode = cux_wms_stock.item_barcode and

:new.batchno = cux_wms_stock.batch_number and

:new.source_loc_code= cux_wms_stock.location_code;

if :new.operation_Mark = 'M' then--转移模式

--打开目标仓区的游标

open temp_out_cursor;

fetch temp_out_cursor into ai_out_count;

if ai_out_count > 0 then

update cux_wms_stock

set cux_wms_stock.quantity = cux_wms_stock.quantity + :new.CASE_NUMBER * ai_abs,LAST_UPDATE_DATE=(select sysdate from dual),LAST_UPDATED_BY=:new.LAST_UPDATED_BY

where :new.organization_id = cux_wms_stock.organization_id and

:new.org_id = cux_wms_stock.org_id and

:new.INVENTORY_CODE=cux_wms_stock.INVENTORY_CODE and

:new.splint_barcode = cux_wms_stock.splint_barcode and

:new.item_barcode = cux_wms_stock.item_barcode and

:new.batchno = cux_wms_stock.batch_number and

:new.intent_loc_code= cux_wms_stock.location_code;

else

Insert into CUX_WMS_STOCK(STOCK_ID,ITEM_BARCODE,SPLINT_BARCODE,ORGANIZATION_ID,ORG_ID,INVENTORY_CODE,

LOCATION_CODE,BATCH_NUMBER,QUANTITY,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,

LAST_UPDATED_BY,INVENTORY_ITEM_ID) values(CUX_WMS_STOCK_ID_S.nextval,:new.item_barcode,:new.splint_barcode,:new.organization_id,:new.org_id,

:new.INVENTORY_CODE,:new.intent_loc_code,:new.batchno,:new.CASE_NUMBER * (-ai_abs),:new.CREATION_DATE,:new.CREATED_BY,:new.LAST_UPDATE_DATE,:new.LAST_UPDATED_BY,0);

end if;

close temp_out_cursor;

end if;

else

if :old.org_id is null then --增加模式时

Insert into CUX_WMS_STOCK(STOCK_ID,ITEM_BARCODE,SPLINT_BARCODE,ORGANIZATION_ID,ORG_ID,INVENTORY_CODE,

LOCATION_CODE,BATCH_NUMBER,QUANTITY,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,

LAST_UPDATED_BY,INVENTORY_ITEM_ID) values(CUX_WMS_STOCK_ID_S.nextval,:new.item_barcode,:new.splint_barcode,:new.organization_id,:new.org_id,

:new.INVENTORY_CODE,:new.intent_loc_code,:new.batchno,:new.CASE_NUMBER * ai_abs,:new.CREATION_DATE,:new.CREATED_BY,:new.LAST_UPDATE_DATE,:new.LAST_UPDATED_BY,0);

end if;

end if;

if :old.org_id is not null then --修改或者删除模式，需要回滚原记录的数据

update cux_wms_stock

set cux_wms_stock.quantity = cux_wms_stock.quantity + :old.CASE_NUMBER * ai_abs,LAST_UPDATE_DATE=(select sysdate from dual),LAST_UPDATED_BY=:old.LAST_UPDATED_BY

where :old.organization_id = cux_wms_stock.organization_id and

:old.org_id = cux_wms_stock.org_id and

:old.INVENTORY_CODE=cux_wms_stock.INVENTORY_CODE and

:old.splint_barcode = cux_wms_stock.splint_barcode and

:old.item_barcode = cux_wms_stock.item_barcode and

:old.batchno = cux_wms_stock.batch_number and

:old.source_loc_code= cux_wms_stock.location_code;

if :old.operation_Mark = 'M' then--转移模式

update cux_wms_stock

set cux_wms_stock.quantity = cux_wms_stock.quantity + :old.CASE_NUMBER * (-ai_abs),LAST_UPDATE_DATE=(select sysdate from dual),LAST_UPDATED_BY=:old.LAST_UPDATED_BY

where :old.organization_id = cux_wms_stock.organization_id and

:old.org_id = cux_wms_stock.org_id and

:old.INVENTORY_CODE=cux_wms_stock.INVENTORY_CODE and

:old.splint_barcode = cux_wms_stock.splint_barcode and

:old.item_barcode = cux_wms_stock.item_barcode and

:old.batchno = cux_wms_stock.batch_number and

:old.intent_loc_code= cux_wms_stock.location_code;

end if;

end if;

close temp_in_cursor;

delete from cux_wms_stock where quantity=0;

end TRG_CUX;

/