Price List Version Query

Ray Lee ✓ Verified SQL Financial Management 2026-03-02

SQL query to retrieve the current active price list version with all product prices. Joins M_PriceList, M_PriceList_Version, and M_ProductPrice tables to get list price, standard price, and limit price for each product. Commonly used for reporting and data migration.

Code 
-- Get current active price list with all product prices
SELECT
    pl.Name              AS PriceListName,
    pl.IsSOPriceList,
    plv.ValidFrom,
    p.Value              AS ProductValue,
    p.Name               AS ProductName,
    pp.PriceList         AS ListPrice,
    pp.PriceStd          AS StandardPrice,
    pp.PriceLimit        AS LimitPrice,
    c.ISO_Code           AS Currency
FROM M_PriceList pl
JOIN M_PriceList_Version plv ON pl.M_PriceList_ID = plv.M_PriceList_ID
JOIN M_ProductPrice pp       ON plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID
JOIN M_Product p             ON pp.M_Product_ID = p.M_Product_ID
JOIN C_Currency c            ON pl.C_Currency_ID = c.C_Currency_ID
WHERE pl.IsActive = 'Y'
  AND plv.IsActive = 'Y'
  AND plv.ValidFrom = (
      SELECT MAX(plv2.ValidFrom)
      FROM M_PriceList_Version plv2
      WHERE plv2.M_PriceList_ID = pl.M_PriceList_ID
        AND plv2.IsActive = 'Y'
        AND plv2.ValidFrom <= CURRENT_DATE
  )
ORDER BY pl.Name, p.Value;

Related Snippets

You Missed