This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

How to set value on column available_qty

Elbeshti
Elbeshti Member Posts: 5 Red Ribbon

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
/






Answers