Retroactive adjustment of purchase prices for goods receipts with a purchase price of 0.00 €

If you forgot to set the purchase prices for individual/all products before the stock initialization, the initial physical stock of these products will be valued with a purchase price of 0.00 €. This may result in the valued stock for these products being lower than it actually should be.

Please note that you should always make a backup of the database before running SQL queries on your database. In this case, it's especially important to back up the pickware_erp_stock_ledger_entries table!

# Copy purchase price to stock entries that increase the stock

UPDATE pickware_erp_stock_ledger_entries AS stocks
INNER JOIN s_articles_details AS articleDetail
ON articleDetail.id = stocks.articleDetailId
SET stocks.purchasePrice = articleDetail.purchaseprice
WHERE
stocks.type IN (
'incoming',
'initialization',
'manual',
'purchase',
'stocktake'
)
AND (
stocks.purchasePrice = 0
OR stocks.purchasePrice IS NULL
)
    AND stocks.changeAmount > 0;

# Apply purchase price to all children of stock entries that increase the stock

UPDATE pickware_erp_stock_ledger_entries AS stocks
INNER JOIN pickware_erp_stock_ledger_entries AS parentStocks
ON parentStocks.id = stocks.sourceLotEntryId
SET stocks.purchasePrice = parentStocks.purchasePrice
WHERE stocks.changeAmount < 0;

If you are using a Pickware ERP version < 5.0.0, please use the following script instead:

# Copy purchase price to stock entries that increase the stockUPDATE s_plugin_pickware_stocks AS stocksINNER JOIN s_articles_details AS articleDetail ON articleDetail.id = stocks.articleDetailIdSET stocks.purchasePrice = articleDetail.purchasepriceWHERE stocks.type IN ( 'incoming', 'initialization', 'manual', 'purchase', 'stocktake' ) AND ( stocks.purchasePrice = 0 OR stocks.purchasePrice IS NULL )

    AND stocks.changeAmount > 0;

# Apply purchase price to all children of stock entries that increase the stockUPDATE s_plugin_pickware_stocks AS stocksINNER JOIN s_plugin_pickware_stocks AS parentStocks ON parentStocks.id = stocks.parentIdSET stocks.purchasePrice = parentStocks.purchasePriceWHERE stocks.changeAmount < 0;