VALIDATING QTY [message #646528] |
Mon, 04 January 2016 00:00 |
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 #646589 is a reply to message #646581] |
Tue, 05 January 2016 03:47 |
cookiemonster
Messages: 13943 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 #646644 is a reply to message #646598] |
Wed, 06 January 2016 04:42 |
cookiemonster
Messages: 13943 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 #646756 is a reply to message #646753] |
Fri, 08 January 2016 03:30 |
cookiemonster
Messages: 13943 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.
|
|
|