How to set value on column available_qty

Elbeshti

Hi,

I have form (Issuing FORM) and Interactive grid (ISUUING_ITEMS) How to set value on column available_qty based on the following:

select sum(QTY_PLUS - QTY_MINUS) into :AVAILABLE_QTY

from TRANSACTIONS

where TRANSACTIONS.ITEM_ID = :ITEM_ID and :P24_ISSUE_DATE <= TRANSACTIONS.TRANS_DATE;

return :AVAILABLE_QTY;

in oracle apex Interactive grid (ISUUING_ITEMS) every time I enter value in Issue_qty column.

my database is 21c enterprise and apex latest version

I have 4 table (ISSUING, ISUUING_ITEMS, TRANSACTIONS, ITEMS) as follow:

CREATE TABLE  "ISSUING" 
   (	"ISSUE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ISSUE_NO" VARCHAR2(20) NOT NULL ENABLE, 
	"ISSUE_DATE" DATE NOT NULL ENABLE, 
	"DEPT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"STORE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	 CONSTRAINT "ISSUING_PK" PRIMARY KEY ("ISSUE_ID")
  USING INDEX  ENABLE
   )
/
ALTER TABLE  "ISSUING" ADD CONSTRAINT "ISSUING_DEPTS_FK" FOREIGN KEY ("DEPT_ID")
	  REFERENCES  "DEPTS" ("DEPT_ID") ENABLE
/
ALTER TABLE  "ISSUING" ADD CONSTRAINT "ISSUING_STORES_FK" FOREIGN KEY ("STORE_ID")
	  REFERENCES  "STORES" ("STORE_ID") ENABLE
/
ALTER TABLE  "ISSUING" ADD CONSTRAINT "ISSUING_USERS_FK" FOREIGN KEY ("USER_ID")
	  REFERENCES  "USERS" ("USER_ID") ENABLE
/


CREATE TABLE  "ISUUING_ITEMS" 
   (	"ISSUE_I_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ISSUE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ISSUE_SERIAL" NUMBER(2,0), 
	"ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"AVAILABLE_QTY" NUMBER(10,0), 
	"ISSUE_QTY" NUMBER(10,2), 
	 CONSTRAINT "ISUUING_ITEMS_PK" PRIMARY KEY ("ISSUE_I_ID")
  USING INDEX  ENABLE
   )
/
ALTER TABLE  "ISUUING_ITEMS" ADD CONSTRAINT "ISUUING_ITEMS_ISSUING_FK" FOREIGN KEY ("ISSUE_ID")
	  REFERENCES  "ISSUING" ("ISSUE_ID") ON DELETE CASCADE ENABLE
/
ALTER TABLE  "ISUUING_ITEMS" ADD CONSTRAINT "ISUUING_ITEMS_ITEMS_FK" FOREIGN KEY ("ITEM_ID")
	  REFERENCES  "ITEMS" ("ITEM_ID") ON DELETE CASCADE ENABLE
/



CREATE OR REPLACE EDITIONABLE TRIGGER  "ISUUING_ITEMS_BI" 
    before insert on ISUUING_ITEMS
    for each row
    begin
      if :new.ISSUE_I_ID is null then
          select ISSUING_ITEMS_KEY_SEQ.nextval into :new.ISSUE_I_ID from sys.dual;
     end if;
    end;

/
ALTER TRIGGER  "ISUUING_ITEMS_BI" ENABLE
/

CREATE TABLE  "ITEMS" 
   (	"ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ITEM_NO" VARCHAR2(16) NOT NULL ENABLE, 
	"ITEM_NAME" VARCHAR2(300) NOT NULL ENABLE, 
	"UNIT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"STORE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"UNIT_PRICE" NUMBER(10,2), 
	 CONSTRAINT "ITEMS_PK" PRIMARY KEY ("ITEM_ID")
  USING INDEX  ENABLE
   )
/
ALTER TABLE  "ITEMS" ADD CONSTRAINT "ITEMS_STORES_FK" FOREIGN KEY ("STORE_ID")
	  REFERENCES  "STORES" ("STORE_ID") ENABLE
/
ALTER TABLE  "ITEMS" ADD CONSTRAINT "ITEMS_UNITS_FK" FOREIGN KEY ("UNIT_ID")
	  REFERENCES  "UNITS" ("UNIT_ID") ENABLE
/

CREATE TABLE  "TRANSACTIONS" 
   (	"TRANS_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"TRANS_TYPE" NUMBER(2,0), 
	"TRANS_NO" VARCHAR2(20), 
	"TRANS_DATE" DATE, 
	"ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"QTY_PLUS" NUMBER(10,3), 
	"QTY_MINUS" NUMBER(10,3), 
	"UNIT_PRICE" NUMBER(10,2), 
	 CONSTRAINT "TRANSACTIONS_PK" PRIMARY KEY ("TRANS_ID")
  USING INDEX  ENABLE
   )
/
ALTER TABLE  "TRANSACTIONS" ADD CONSTRAINT "TRANSACTIONS_ITEMS_FK" FOREIGN KEY ("ITEM_ID")
	  REFERENCES  "ITEMS" ("ITEM_ID") ENABLE
/

Elbeshti

Any idea 💡?