Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to track down stock in a Sales application?

Eslam_ElbyalyDec 10 2019 — edited Mar 14 2020

win 7, db xe 18c, apex 18.2.

---------------------------------------------

I am developing a restaurant management application. There are ingredients to be purchased i.e tomato, olive. Those ingredient are dispatched to the kitchen to be used to make some items i.e Pizza. This Pizza then sold to customers.

- Stock is all about ingredients.

- Stock is increased whenever an inventory when a higher value of qty is created, a purchase is made or ingredients returned from the kitchen. And is decreased whenever a qty is dispatched to the kitchen or an inventory with a lower value of qty is created.

- There is an inventory document which refers to the physical count of ingredients in the warehouse i.e 5 Tomato, 6 Potato, etc... .

- Inventory document could contain one, more or all ingredients.

- if an ingredient "Tomato" mentioned in an inventory document created in May with a qty of "2" then mentioned in another document in June

with qty of 3. Then I need the value of "3" because it's the last one.

- the current qty of an ingredient. Which is the last qty of an ingredient entered in an inventory document plus all quantities purchased of that ingredient after the date of the ingredient's last inventory plus all returns from the kitchen after the date of the ingredient's last inventory.

- When creating a new inventory document, the user needs to know the current qty of one, more or all ingredients.

- The problem is that the query to retrieve the current quantity of ingredients will involve many joins, grouping, group functions and subqueries which will have a very negative impact on performance. So I thought of creating a table to store quantities entered or dispatched from the warehouse whenever a transaction is created i.e a purchase is made or a qty returned.

- Could that be a right solution or could I come across some problems using it?

Thanks a lot.

Edited:

- Here is the script of ingredients, inventory and purchases tables and the query I just wrote to get the current stock,

--------------------------------------------------------

--  DDL for Table INGREDIENT

--------------------------------------------------------

  CREATE TABLE "INGREDIENT" ("ID" NUMBER, "NAME" VARCHAR2(50), "DEFAULT_PRICE" NUMBER) ;

--------------------------------------------------------

--  DDL for Table PHYSICAL_INVENTORY_DTL

--------------------------------------------------------

  CREATE TABLE "PHYSICAL_INVENTORY_DTL" ("ID" NUMBER, "INGREDIENT_ID" NUMBER, "SYSTEM_QTY" NUMBER, "ACTUAL_QTY" NUMBER, "PHYSICAL_INVENTORY_HDR_ID" NUMBER) ;

   COMMENT ON COLUMN "PHYSICAL_INVENTORY_DTL"."SYSTEM_QTY" IS 'Qty according to the system.';

   COMMENT ON COLUMN "PHYSICAL_INVENTORY_DTL"."ACTUAL_QTY" IS 'Qty according to the User. ';

--------------------------------------------------------

--  DDL for Table PHYSICAL_INVENTORY_HDR

--------------------------------------------------------

  CREATE TABLE "PHYSICAL_INVENTORY_HDR" ("ID" NUMBER, "DOCUMENT_DATE" DATE) ;

--------------------------------------------------------

--  DDL for Table PURCHASE_INVOICE_DTL

--------------------------------------------------------

  CREATE TABLE "PURCHASE_INVOICE_DTL" ("ID" NUMBER, "INGREDIENT_ID" NUMBER, "QTY" NUMBER, "PRICE" NUMBER, "PURCHASE_INVOICE_ID" NUMBER) ;

--------------------------------------------------------

--  DDL for Table PURCHASE_INVOICE_HDR

--------------------------------------------------------

  CREATE TABLE "PURCHASE_INVOICE_HDR" ("ID" NUMBER, "DOCUMENT_DATE" DATE) ;

REM INSERTING into INGREDIENT

SET DEFINE OFF;

Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (1,'ING1',1);

Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (2,'ING2',2);

Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (3,'ING3',3);

Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (4,'ING4',4);

Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (5,'ING5',12);

REM INSERTING into PHYSICAL_INVENTORY_DTL

SET DEFINE OFF;

Insert into PHYSICAL_INVENTORY_DTL (ID,INGREDIENT_ID,SYSTEM_QTY,ACTUAL_QTY,PHYSICAL_INVENTORY_HDR_ID) values (22,1,11,100,22);

Insert into PHYSICAL_INVENTORY_DTL (ID,INGREDIENT_ID,SYSTEM_QTY,ACTUAL_QTY,PHYSICAL_INVENTORY_HDR_ID) values (23,2,22,250,22);

Insert into PHYSICAL_INVENTORY_DTL (ID,INGREDIENT_ID,SYSTEM_QTY,ACTUAL_QTY,PHYSICAL_INVENTORY_HDR_ID) values (41,1,14,12,42);

REM INSERTING into PHYSICAL_INVENTORY_HDR

SET DEFINE OFF;

Insert into PHYSICAL_INVENTORY_HDR (ID,DOCUMENT_DATE) values (22,to_date('03/05/2019','DD/MM/YYYY'));

Insert into PHYSICAL_INVENTORY_HDR (ID,DOCUMENT_DATE) values (42,to_date('04/12/2019','DD/MM/YYYY'));

--------------------------------------------------------

--  Constraints for Table INGREDIENT

--------------------------------------------------------

  ALTER TABLE "INGREDIENT" ADD CONSTRAINT "INGREDIENT_PK" PRIMARY KEY ("ID") USING INDEX  ENABLE;

  ALTER TABLE "INGREDIENT" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "INGREDIENT" MODIFY ("NAME" NOT NULL ENABLE);

--------------------------------------------------------

--  Constraints for Table PHYSICAL_INVENTORY_DTL

--------------------------------------------------------

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("PHYSICAL_INVENTORY_HDR_ID" NOT NULL ENABLE);

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("ACTUAL_QTY" NOT NULL ENABLE);

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("SYSTEM_QTY" NOT NULL ENABLE);

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("INGREDIENT_ID" NOT NULL ENABLE);

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("ID" NOT NULL ENABLE);

--------------------------------------------------------

--  Constraints for Table PHYSICAL_INVENTORY_HDR

--------------------------------------------------------

  ALTER TABLE "PHYSICAL_INVENTORY_HDR" ADD CONSTRAINT "PHYSICAL_INVENTORY_HDR_PK" PRIMARY KEY ("ID") USING INDEX  ENABLE;

  ALTER TABLE "PHYSICAL_INVENTORY_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);

  ALTER TABLE "PHYSICAL_INVENTORY_HDR" MODIFY ("ID" NOT NULL ENABLE);

--------------------------------------------------------

--  Constraints for Table PURCHASE_INVOICE_DTL

--------------------------------------------------------

  ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PURCHASE_INVOICE_ID" NOT NULL ENABLE);

  ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PRICE" NOT NULL ENABLE);

  ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("QTY" NOT NULL ENABLE);

  ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("INGREDIENT_ID" NOT NULL ENABLE);

  ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("ID" NOT NULL ENABLE);

--------------------------------------------------------

--  Constraints for Table PURCHASE_INVOICE_HDR

--------------------------------------------------------

  ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PURCHASE_INVOICE_HDR_PK" PRIMARY KEY ("ID") USING INDEX  ENABLE;

  ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);

  ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("ID" NOT NULL ENABLE);

--------------------------------------------------------

--  Ref Constraints for Table PHYSICAL_INVENTORY_DTL

--------------------------------------------------------

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" ADD CONSTRAINT "PHYSICAL_INVENTORY_DTL_FK1" FOREIGN KEY ("PHYSICAL_INVENTORY_HDR_ID") REFERENCES "PHYSICAL_INVENTORY_HDR" ("ID") ON DELETE CASCADE ENABLE;

  ALTER TABLE "PHYSICAL_INVENTORY_DTL" ADD CONSTRAINT "PHY_INV_DTL_INGREDIENTID_FK" FOREIGN KEY ("INGREDIENT_ID") REFERENCES "INGREDIENT" ("ID") ENABLE;

--------------------------------------------------------

--  Ref Constraints for Table PURCHASE_INVOICE_DTL

--------------------------------------------------------

  ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PI_DTL_INGREDIENTID_FK" FOREIGN KEY ("INGREDIENT_ID") REFERENCES "INGREDIENT" ("ID") ENABLE;

  ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PURCHASE_INVOICE_DTL_FK1" FOREIGN KEY ("PURCHASE_INVOICE_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ON DELETE CASCADE ENABLE;

SELECT pih.document_date,

  pid.INGREDIENT_ID ingredient_id,

  (pid.actual_qty +

  (SELECT SUM(QTY) PURCHASED_QTY

  FROM PURCHASE_INVOICE_HDR IHDR

  JOIN PURCHASE_INVOICE_DTL IDTL

  ON IHDR.ID             = IDTL.PURCHASE_INVOICE_ID

  AND IHDR.DOCUMENT_DATE > PIH.DOCUMENT_DATE

  AND IDTL.INGREDIENT_ID = PID.INGREDIENT_ID

  )) CURRENT_QTY

FROM physical_inventory_hdr pih

JOIN physical_inventory_dtl pid

ON pih.id                           = pid.physical_inventory_hdr_id

WHERE pid.physical_inventory_hdr_id =

  (SELECT MAX(d.physical_inventory_hdr_id)

  FROM physical_inventory_dtl d

  WHERE d.INGREDIENT_ID = pid.INGREDIENT_ID

  );

Comments

Processing

Post Details

Added on Dec 10 2019
13 comments
770 views