Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
How to set value on column available_qty

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
-
Any idea 💡?