Subsequent Adjustment of the Purchase Prices for Incoming Goods with a Purchase Price of 0,- €

If the purchase prices for individual or all items were not set before the stock initialisation, the initial physical stock of these items is valued with a purchase price of €0. This may result in the stock valuation for these items being lower than it actually should be.

# 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: 

# Copy purchase price to stock entries that increase the stock

UPDATE s_plugin_pickware_stocks 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 s_plugin_pickware_stocks AS stocks
INNER JOIN s_plugin_pickware_stocks AS parentStocks
 ON parentStocks.id = stocks.parentId
SET stocks.purchasePrice = parentStocks.purchasePrice
WHERE stocks.changeAmount < 0;