10 Replies Latest reply on Dec 13, 2019 7:28 PM by L. Fernigrini

How to track down stock in a Sales application?

Eslam_Elbyaly Dec 11, 2019 1:41 PM

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
  );

1. Re: How to track down stock in a Sales application?

Paulzip Dec 11, 2019 12:25 AM (in response to Eslam_Elbyaly)

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
I don't know why you think Oracle isn't up to the job for this. The model sounds very simple and executing such queries on a properly designed model, shouldn't have any issues.

If you are concerned about evaluating aggregated results, then an alternate approach would be perhaps creating a materialized view with refresh on commit, which evaluates what you need, or archiving off old inventory documents so that any query is working on a streamlined dataset.

I think you are overthinking things and underestimating Oracle's abilities to perform blistering fast set operations on a wide variation of data models, if they are designed correctly.
Like Show 1 Likes(1)

Actions
2. Re: How to track down stock in a Sales application?

Eslam_Elbyaly Dec 11, 2019 1:43 PM (in response to Paulzip)

Is it true when I am using XE database?
I edited the original post and added a script of some of the tables that will be queried and the query I ended up to. I will just add other scalar subqueries for the rest of the tables like I did with purchase tables' scalar subquery.
Like Show 0 Likes(0)

Actions
3. Re: How to track down stock in a Sales application?

L. Fernigrini Dec 11, 2019 1:46 PM (in response to Eslam_Elbyaly)

Yes, even Oracle XE can handle complex queries, the fact that it is "limited" in resources also means that the amount of data that it can process is not big at all.
Like Show 0 Likes(0)

Actions
4. Re: How to track down stock in a Sales application?

John_K Dec 11, 2019 3:31 PM (in response to Eslam_Elbyaly)
Personally I'd probably go with a design where you have something like:

Inventory Items (Tomato, Olive Oil etc).
Inventory Locations (Kitchen, Stores)
Onhand Stock (InventoryItem, Inventory Location, Qty).

Plus tables to record detailed transactions.

Very simply:
When you receive stock of an item, you add to the onhand stock in that location.
When you take stock from stores to the kitchen, you transact from the stores locator to the kitchen.
When you make a plate of food (etc), you reduce the onhand stock in the kitchen location.
When you move stock from one location to the other (stores > kitchen) you reduce stock in stores and increase in kitchen.

Then your current onhand inventory is simple - it's just a sum of the onhand quantities table for your chosen locators.
In a simplified world, you'd have three API's - Receive_Stock, Reduce_Stock, and Transfer_Stock.

This is how Oracle Inventory (for E-Business Suite) works - and is used to manage warehouses, factories etc with millions of items, tens of thousands of locations, hundreds of millions of stock etc.
Like Show 0 Likes(0)

Actions
5. Re: How to track down stock in a Sales application?

Eslam_Elbyaly Dec 11, 2019 5:08 PM (in response to John_K)
John_K wrote:

Personally I'd probably go with a design where you have something like:

Inventory Items (Tomato, Olive Oil etc).
Inventory Locations (Kitchen, Stores)
Onhand Stock (InventoryItem, Inventory Location, Qty).

Plus tables to record detailed transactions.

Very simply:
When you receive stock of an item, you add to the onhand stock in that location.
When you take stock from stores to the kitchen, you transact from the stores locator to the kitchen.
When you make a plate of food (etc), you reduce the onhand stock in the kitchen location.
When you move stock from one location to the other (stores > kitchen) you reduce stock in stores and increase in kitchen.

Then your current onhand inventory is simple - it's just a sum of the onhand quantities table for your chosen locators.
In a simplified world, you'd have three API's - Receive_Stock, Reduce_Stock, and Transfer_Stock.

This is how Oracle Inventory (for E-Business Suite) works - and is used to manage warehouses, factories etc with millions of items, tens of thousands of locations, hundreds of millions of stock etc.
- Is this the way EBS implement it? Are there a table to store quantities when stock is increased or decreased? Does not it use a query to get the current stock?
Like Show 0 Likes(0)

Actions

6. Re: How to track down stock in a Sales application?

John_K Dec 12, 2019 9:26 AM (in response to Eslam_Elbyaly)

EBS is obviously a bit more complicated, but yeah, that's the gist of it. Here is a very simplified example. There is a lot of repetition here - in reality you wouldn't have the duplicate statements etc and would abstract it into other function calls, however I've left it like that so you can see what is happening.

create table xxjk_items (item_id number, description varchar2(10));
create table xxjk_locs (loc_id number, description varchar2(10));
create table xxjk_onhand(item_id number, loc_id number, qty number);
create table xxjk_txns(txn_type varchar2(10), item_id number, from_loc_id number, to_loc_id number, qty number);


insert into xxjk_items select level, 'Item ' || level from dual connect by level <= 5;
insert into xxjk_locs select level, 'Locator ' || level from dual connect by level <=3;


create or replace package xxjk_stock_pk is
  procedure add_stock(p_item_id number, p_loc_id number, p_qty number);
  procedure remove_stock(p_item_id number, p_loc_id number, p_qty number);
  procedure transfer_stock(p_item_id number, p_from_loc_id number, p_to_loc_id number, p_qty number);
end xxjk_stock_pk;
/
create or replace package body xxjk_stock_pk is
  procedure add_stock(p_item_id number, p_loc_id number, p_qty number) is
  begin
    -- Record a transaction
    insert into xxjk_txns values ('ADD',p_item_id, null, p_loc_id, p_qty);
    
    -- Update stock
    merge into xxjk_onhand o 
         using (select p_item_id item_id, p_loc_id loc_id, p_qty qty from dual) t
            on (t.item_id = o.item_id and t.loc_id = o.loc_id)
    when matched then update set o.qty = o.qty + t.qty
    when not matched then insert(item_id, loc_id, qty) values (t.item_id, t.loc_id, t.qty)
    ;
  end add_stock;
  
  procedure remove_stock(p_item_id number, p_loc_id number, p_qty number) is
  begin
    -- Theoretically just add_stock but negative, however will keep separate as a demo.
    insert into xxjk_txns values ('REMOVE',p_item_id, null, p_loc_id, p_qty);
    update xxjk_onhand o set o.qty = o.qty - p_qty where o.item_id = p_item_id and o.loc_id = p_loc_id; -- We just set to zero rather than removing
  end remove_stock;
  
  procedure transfer_stock(p_item_id number, p_from_loc_id number, p_to_loc_id number, p_qty number) is
  begin
    insert into xxjk_txns values ('TRANSFER',p_item_id, p_from_loc_id, p_to_loc_id, p_qty);
    
    -- Reduce stock in source
    update xxjk_onhand o set o.qty = o.qty - p_qty where o.item_id = p_item_id and o.loc_id = p_from_loc_id;
    
    -- Increase stock in destination
    merge into xxjk_onhand o 
         using (select p_item_id item_id, p_to_loc_id loc_id, p_qty qty from dual) t
            on (t.item_id = o.item_id and t.loc_id = o.loc_id)
    when matched then update set o.qty = o.qty + t.qty
    when not matched then insert(item_id, loc_id, qty) values (t.item_id, t.loc_id, t.qty);
  end transfer_stock;
end xxjk_stock_pk;
/

Now run a few tests.

exec xxjk_stock_pk.add_stock(1, 1, 100);
exec xxjk_stock_pk.add_stock(2, 1, 5);
exec xxjk_stock_pk.add_stock(2, 2, 10);
exec xxjk_stock_pk.add_stock(3, 1, 1);
select * from xxjk_onhand;
select * from xxjk_txns;
exec xxjk_stock_pk.transfer_stock(1,1,4,50);
exec xxjk_stock_pk.transfer_stock(2,2,1,5);
exec xxjk_stock_pk.transfer_stock(3,1,1,1);
select * from xxjk_onhand;
select * from xxjk_txns;
exec xxjk_stock_pk.remove_stock(1, 4, 10);
exec xxjk_stock_pk.remove_stock(1, 1, 10);
exec xxjk_stock_pk.remove_stock(3, 1, 1);
select * from xxjk_onhand;
select * from xxjk_txns;

SQL> @a.sql
SQL> exec xxjk_stock_pk.add_stock(1, 1, 100);
SQL> exec xxjk_stock_pk.add_stock(2, 1, 5);
SQL> exec xxjk_stock_pk.add_stock(2, 2, 10);
SQL> exec xxjk_stock_pk.add_stock(3, 1, 1);
SQL> select * from xxjk_onhand;


   ITEM_ID     LOC_ID        QTY
---------- ---------- ----------
         1          1        100
         2          1          5
         2          2         10
         3          1          1
SQL> select * from xxjk_txns;


TXN_TYPE      ITEM_ID FROM_LOC_ID  TO_LOC_ID        QTY
---------- ---------- ----------- ---------- ----------
ADD                 1                      1        100
ADD                 2                      1          5
ADD                 2                      2         10
ADD                 3                      1          1
SQL> exec xxjk_stock_pk.transfer_stock(1,1,4,50);
SQL> exec xxjk_stock_pk.transfer_stock(2,2,1,5);
SQL> exec xxjk_stock_pk.transfer_stock(3,1,1,1);
SQL> select * from xxjk_onhand;


   ITEM_ID     LOC_ID        QTY
---------- ---------- ----------
         1          1         50
         2          1         10
         2          2          5
         3          1          1
         1          4         50
SQL> select * from xxjk_txns;


TXN_TYPE      ITEM_ID FROM_LOC_ID  TO_LOC_ID        QTY
---------- ---------- ----------- ---------- ----------
ADD                 1                      1        100
ADD                 2                      1          5
ADD                 2                      2         10
ADD                 3                      1          1
TRANSFER            1           1          4         50
TRANSFER            2           2          1          5
TRANSFER            3           1          1          1
SQL> exec xxjk_stock_pk.remove_stock(1, 4, 10);
SQL> exec xxjk_stock_pk.remove_stock(1, 1, 10);
SQL> exec xxjk_stock_pk.remove_stock(3, 1, 1);
SQL> select * from xxjk_onhand;


   ITEM_ID     LOC_ID        QTY
---------- ---------- ----------
         1          1         40
         2          1         10
         2          2          5
         3          1          0
         1          4         40
SQL> select * from xxjk_txns;


TXN_TYPE      ITEM_ID FROM_LOC_ID  TO_LOC_ID        QTY
---------- ---------- ----------- ---------- ----------
ADD                 1                      1        100
ADD                 2                      1          5
ADD                 2                      2         10
ADD                 3                      1          1
TRANSFER            1           1          4         50
TRANSFER            2           2          1          5
TRANSFER            3           1          1          1
REMOVE              1                      4         10
REMOVE              1                      1         10
REMOVE              3                      1          1
SQL>

Clean up.

drop table xxjk_items;
drop table xxjk_locs;
drop table xxjk_onhand;
drop table xxjk_txns;
drop package xxjk_stock_pk;

1 person found this helpful

7. Re: How to track down stock in a Sales application?

Eslam_Elbyaly Dec 12, 2019 12:38 PM (in response to John_K)

That was the other way I was considering. But this will involve many individual update statement to the "stock" table with every item that's transacted. For example if I am buying 10 different items this will issue 10 update statements to the "stock" table(xxjk_onhand in your example).

- Would this be better than the other way(not storing the on_hand qty in a table and query all involved tables and making the right calculation?

Edited:
- I tried the "Query" approach with only purchases tables. The master had a million record and the detail had 4 million records. The query took about 9 seconds on a core i5 processor.
Like Show 0 Likes(0)

Actions
8. Re: How to track down stock in a Sales application?

John_K Dec 12, 2019 12:48 PM (in response to Eslam_Elbyaly)

You do one update statement for each item you purchase - so yes, if you buy 10 items, you do 10 updates. That's insignificant. If you are doing 100,000 updates, maybe I'd be thinking about doing something else, but for normal data volumes, it's really not an issue. You're storing the transaction anyway for every order, so it's not a great overhead regardless.

I just find it a bit clunky having to calculate stock levels based on all previous transactions. It'll almost certainly be quicker querying an "onhand" table, indexed by the item/locator, than it will be querying a load of individual transactions and aggregating them.

The EBS system I'm currently using- the transactions table has 45 million records going back about 20 years. The onhand table has 28,000 records. I'm sure you can figure which is quicker and easier to use in queries.
1 person found this helpful
Like Show 0 Likes(0)

Actions
9. Re: How to track down stock in a Sales application?

Eslam_Elbyaly Dec 13, 2019 7:00 PM (in response to John_K)

I'd appreciate it If you could help me with that.... . What about violating a database design rule that states that derived/calculated values should not be stored? I see no problem here but I am worried I could be missing something. Or why not store a derived value in all situations if it will simplify querying data?
Like Show 0 Likes(0)

Actions
10. Re: How to track down stock in a Sales application?

L. Fernigrini Dec 13, 2019 7:28 PM (in response to Eslam_Elbyaly)

Some de-normalization is required to ensure performance is acceptable.

The important thing in those cases is to ensure that there is no way to avoid updating the stored totals when doing DML on the "transaction" table.

You may use a PL/SQL API to create / update / delete rows on the transaction table so you are 100% sure that after each operation the totals are updated accordingly
1 person found this helpful
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.