Add status based on Quantity ordered

I am using Adempiere. I have three tables and a view. It is 'M_INVENTORY', 'M_INVENTORYLINE', 'M_REPLENISH' and the other is 'VW_DAFTARBARANG_AVAILABLE'. M_Inventory is used when we want to choose Warehouse. It shows like this

            M_INVENTORY
------------------------------------
M_Inventory_ID  || M_Warehouse_ID
------------------------------------
2000001         || 1000001
2000002     || 1000002
2000003     || 1000003

M_InventoryLine is used when we want to order stock, in here we put Product and Quantity ordered. M_InventoryLine is nested from M_Inventory, so we order stock from relevant Warehouse we chose in M_Inventory.

                   M_INVENTORYLINE
-----------------------------------------------------------
M_Inventory_ID  || M_Product_ID || QtyInternalUse || Status
2000001         || 1000011      || 5              ||
2000001         || 1000012      || 7              || 
2000001         || 1000013      || 8              ||

M_Replenish is used to check minimum stock level.

        M_REPLENISH
-----------------------------
M_Product_ID || Level_Min
1000011      || 20
1000012      || 15
1000013      || 12

The availability of stock can be checked in view VW_DAFTARBARANG_AVAILABLE.

 VW_DAFTARBARANG_AVAILABLE
--------------------------------------------
M_Warehouse_ID || M_Product_ID || Available
--------------------------------------------
1000001        || 1000011      || 27
1000001        || 1000012      || 20
1000001        || 1000013      || 12 

1000002        || 1000011      || 25
1000002        || 1000012      || 20

1000003        || 1000011      || 25
1000003        || 1000012      || 20

I want to put information in Status column in table M_InventoryLine.

If Available is more than minimum stock when stock ordered, then status shows 'Complete'

Example : M_Product_ID = 1000011 (QtyInternalUse [order] = 5, Level_Min = 20, Av = 27, 27-5 = 22 -> Still above Minimum Level)

If Available reaches minimum stock when stock half ordered, then status shows ' Partial'

Example : M_Product_ID = 1000012 (QtyInternalUse [order] = 7, Level_Min = 15, Av = 20, 20-7 = 13 -> Becomes below Minimum Level,

Therefore, it can only fulfill 5 out of 7 so the stock still in the minimum level.)

If Available is in his minimum stock so that stock can't be ordered, then status shows 'N/A'

Example : M_Product_ID = 1000013 (QtyInternalUse [order] = 8, Level_Min = 12, Av = 12, -> Qty Available the same as minimum level, therefore it can't be ordered)

I've tried making trigger by making something like this =

CREATE OR REPLACE TRIGGER STATUS_MR
BEFORE INSERT ON M_INVENTORYLINE
FOR EACH ROW
BEGIN
WHEN M_INVENTORY.M_WAREHOUSE_ID = M_WAREHOUSE_ID AND M_PRODUCT_ID = M_PRODUCT_ID;

IF :NEW.QTYINTERNALUSE <= VW_DAFTARBARANG_AVAILABLE.AVAILABLE THEN 
:new.Status := "Complete"
ELSIF :NEW.QTYINTERNALUSE > VW_DAFTARBARANG_AVAILABLE.AVAILABLE THEN
:new.Status := "Partial"
ELSE 
:new.status := "Not Available"
END IF;
END;

There are still lots of errors and I am confused on how to write the trigger well based on the condition I have.

Any suggestions will be appreciated :)

Solution

I'm not sure if trigger is necessary here, maybe view based on this query would be sufficient:

select m_inventory_id, m_product_id, qtyinternaluse qty, level_min lvl, available,
       case when available - qtyinternaluse > level_min then 'Complete'
            when available <= level_min then 'Not Available'
            else 'Partial' end status
  from m_inventory i 
  join m_inventoryline il using (m_inventory_id)
  join m_replenish r using (m_product_id)
  join vw_daftarbarang_available d using (m_warehouse_id, m_product_id);

^{SQLFiddle demo}

If you insist on trigger then below is something what worked for data, logic and examples you provided. I added part for updating qtyinternaluse, not sure if this is important/possible. Trigger may need adjustments and definitely needs tests, anyway I hope this helps. Also - if view vw_daftarbarang_available uses table m_inventoryline you may encounter "mutating table error", but this is only my warning as I don't see view definition.

create or replace trigger status_mr
before insert or update of qtyinternaluse on m_inventoryline for each row
declare
  v_qty_max number := 0;
begin
  select available-level_min into v_qty_max
    from m_replenish r join vw_daftarbarang_available da using (m_product_id)
    join m_inventory i using (m_warehouse_id)
    where m_product_id = :new.m_product_id and m_inventory_id = :new.m_inventory_id;

  if inserting then
    if :new.qtyinternaluse <= v_qty_max then
      :new.Status := 'Complete';
    elsif v_qty_max <= 0 then 
      :new.status := 'Not Available';
    else
      :new.Status := 'Partial';
    end if;
  elsif updating then
    if :new.qtyinternaluse <= v_qty_max + :old.qtyinternaluse then
      :new.Status := 'Complete';
    elsif v_qty_max + :old.qtyinternaluse <= 0 then 
      :new.status := 'Not Available';
    else
      :new.Status := 'Partial';
    end if;  
  end if;
end;