Duplicate Stock Bucket With For The Same Item and Location

In Wavelet EMP, each inventory item has a stock bucket (inv_stock.sql)  in each location when there are transactions for this location. If the stock bucket does not exist, it will create a new stock bucket when transaction occurs.

When server is not shut down correctly, or when there is a database connection problem due to network interruptions, especially at the point when a transaction is being carried out halfway, the system assume there was not previous bucket, and hence, create another one, which results in duplicated stock bucket.

To solve this problem, we need to merge all buckets created wrongly into one single bucket. See steps below:

Currently, we are doing these steps manually. When we have enhanced our program to self correct the error, we will indicate where to click the function:

1. First of all, find out all the duplicated buckets:

wsemp=# SELECT itemid,locationid, count(pkid) FROM inv_stock GROUP BY itemid,locationid HAVING count(pkid) > 1;

 itemid | locationid | count

--------+------------+-------

   1027 |       1008 |     2

(1 row)

2. Next, we find out the primary key (pkid) of the buckets that require merging, sorted by pkid.

wsemp=# SELECT stk.pkid AS stockid,stk.bal, stk.unit_cost_ma, stk.itemid, stk.locationid FROM inv_stock AS stk INNER JOIN (SELECT itemid,locationid, count(pkid) FROM inv_stock GROUP BY itemid,locationid HAVING count(pkid) > 1) AS chk ON (stk.itemid = chk.itemid AND stk.locationid = chk.locationid) ORDER BY stockid;

 stockid |   bal   | unit_cost_ma | itemid | locationid

---------+---------+--------------+--------+------------

    1199 |  1.0000 |    1045.6325 |   1027 |       1008

    1200 | -1.0000 |    1045.6325 |   1027 |       1008

(2 rows)

3. Now, we will transfer all transactions tied to later stock buckets into the earliest stock bucket:

wsemp=# UPDATE inv_stock_delta SET stockid='1199' WHERE stockid='1200';

wsemp=# UPDATE inv_serial_number_delta SET stockid='1199' WHERE stockid='1200';

4. Lastly, we remove the unwated stock bucket.

wsemp=# DELETE FROM inv_stock WHERE pkid='1200';