Home » Developer & Programmer » Forms » VALIDATING QTY (Forms6i on Windows7)
VALIDATING QTY [message #646528] Mon, 04 January 2016 00:00 Go to next message
bluesky_18011971
Messages: 9
Registered: August 2008
Location: Multan
Junior Member

I have a query regarding checking of STOCKS qty (live). I am using 3 tables to post my invoices and system check STOCK qty if qty available we can post invoice otherwise generate error message.

1- tm_invoice
2- td_invoice
3- stocks

for example 3 users are posting invoices and at some stage same item (bottle 100 ml having stock 3000) select all 3 users

1- User 1 put the qty 3000
2- User 2 put the qty 2000
3- User 3 put the qty 3000

When the user 1 put the QTY (3000) system check and post invoices because user 1 hit the ENTER first and system validate the stocks. but the user 2 and user 3 also the same hit but due to little difference of time system give all 3 user authorize to save the qty.

The reason is user 1 hit the qty but did not save and the same happen with user 2 and user 3. the out put is that 5000 stocks goes to NEGATIVE. which is not a good sign.

on QTY I am using when-validate-trigger.
all stocks entry also post in STOCK table.

Can we solve this issue.
Re: VALIDATING QTY [message #646545 is a reply to message #646528] Mon, 04 January 2016 03:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What table is the datablock the QTY item is in based on?
Re: VALIDATING QTY [message #646581 is a reply to message #646545] Mon, 04 January 2016 22:42 Go to previous messageGo to next message
bluesky_18011971
Messages: 9
Registered: August 2008
Location: Multan
Junior Member

TM_invoice : Having customer code, date etc.
TD_invoice : having Item Code, qty, rate, amount (User here put the qty)
STOCK : having stock status (System check this table for available stocks)
Re: VALIDATING QTY [message #646589 is a reply to message #646581] Tue, 05 January 2016 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is there a datablock based on stock?
Does the stock table get updated when the user saves the td_invoice record?

Really you need to either:
a) lock the stock record as soon as they start entering data for that item.
b) double check the qty on stock at the point of saving (lock stock at that point) and if it goes negative raise an error.
Re: VALIDATING QTY [message #646593 is a reply to message #646589] Tue, 05 January 2016 04:47 Go to previous messageGo to next message
bluesky_18011971
Messages: 9
Registered: August 2008
Location: Multan
Junior Member

No block is based on stock.
No updates on TD_invoice.
Re: VALIDATING QTY [message #646594 is a reply to message #646593] Tue, 05 January 2016 04:58 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I asked if stock gets updated when they insert a td_invoice record
Re: VALIDATING QTY [message #646598 is a reply to message #646594] Tue, 05 January 2016 05:09 Go to previous messageGo to next message
bluesky_18011971
Messages: 9
Registered: August 2008
Location: Multan
Junior Member

no updates.
When record insert into Td_invoice. Same insert into Stock Table.

When the user put the Qty. System sum up all stocks documents with (+/-) and give in hand qty
Re: VALIDATING QTY [message #646644 is a reply to message #646598] Wed, 06 January 2016 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
My general answer above stands. If you want a more specific answer then you need to describe the process for calculating stock in detail
Re: VALIDATING QTY [message #646646 is a reply to message #646644] Wed, 06 January 2016 05:23 Go to previous messageGo to next message
bluesky_18011971
Messages: 9
Registered: August 2008
Location: Multan
Junior Member

Tm_Invoice & Td_invoice is master / detail table. and all data save on when do commit.
Stock is stand alone table. When Data commit on invoice. Data insert into STOCKS table through POST INSERT trigger.

When Stock in through purchase we insert QTY in stock table with (+ plus sign) through POST-INSERT trigger.
When Stock out through invoice we insert QTY in stock table with (- negative sign) through POST-INSERT trigger.

System check stocks from the stock table using WHEN-VALIDATE-TRIGGER on QTY. Using Select statement to sum up stock qty and then check with the given qty.


Re: VALIDATING QTY [message #646647 is a reply to message #646646] Wed, 06 January 2016 05:39 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you want to disallow negative total stock under all circumstances or just some?
Re: VALIDATING QTY [message #646753 is a reply to message #646647] Fri, 08 January 2016 02:18 Go to previous messageGo to next message
bluesky_18011971
Messages: 9
Registered: August 2008
Location: Multan
Junior Member

Negative stocks not allowed in any case. If stocks available invoice should post, other wise denied.
Re: VALIDATING QTY [message #646756 is a reply to message #646753] Fri, 08 January 2016 03:30 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your best bet is probably a view with a check constraint on it:
SQL> CREATE TABLE stock (item VARCHAR2(10), seq NUMBER, amt NUMBER);
 
Table created
SQL> ALTER TABLE stock ADD CONSTRAINT stock_pk PRIMARY KEY (item, seq);
 
Table altered
SQL> create materialized view log on stock WITH ROWID (item, seq, amt) INCLUDING NEW VALUES;
 
Materialized view log created
SQL> CREATE MATERIALIZED VIEW stock_v REFRESH FAST ON COMMIT
  2  AS SELECT item, SUM(amt) total_amt
  3  FROM stock
  4  GROUP BY item;
 
Materialized view created
SQL> ALTER TABLE stock_v ADD CONSTRAINT stock_v_chk CHECK (total_amt >= 0);
 
Table altered
 
SQL> INSERT INTO stock (item, seq, amt) VALUES ('A', 1, 1000);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> INSERT INTO stock (item, seq, amt) VALUES ('A', 2, -500);
 
1 row inserted
 
SQL> INSERT INTO stock (item, seq, amt) VALUES ('A', 3, -500);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from stock;
 
ITEM              SEQ        AMT
---------- ---------- ----------
A                   1       1000
A                   2       -500
A                   3       -500
 
SQL> INSERT INTO stock (item, seq, amt) VALUES ('A', 4, -500);
 
1 row inserted
 
SQL> commit;
 
commit
 
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TAGTRAK.STOCK_V_CHK) violated
 
SQL> 


Just bear in mind that the constraint is only checked when the user commits.
Previous Topic: Symbols & Units in Oracle Forms
Next Topic: Problem in Oracle Forms 6i
Goto Forum:
  


Current Time: Fri Jul 03 22:00:51 CDT 2020