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;
/