Duplicate and Copy that existing Dynamics GP Price Level and associated Price Lists to a new Price Level

price_levelA client is often adding new customers and associating new Price Levels to these customers. I was looking for a quick way to copy an existing  Price Level to get the Price Lists on items to match the existing Price Level. That way the few tweaks to a handful of items’ prices would be all that was needed. Traditionally, they were having to manually key in a price for all items, even if that item is priced the same on all Price Levels. I came across this simple SQL query.

SQL Query

DECLARE
@EXISTING_PRICELEVELCODE VARCHAR(250),
@NEW_PRICELEVELCODE VARCHAR(250),
@NEW_PRICELEVELDESC VARCHAR(250)
SET @EXISTING_PRICELEVELCODE = ‘RETAIL’ — SET YOUR SOURCE PRICELEVEL CODE
SET @NEW_PRICELEVELCODE = ‘WHOLESALE’ — SET YOUR NEW PRICELEVEL CODE
SET @NEW_PRICELEVELDESC = ‘WHOLESALE CUSTOM PRICING’ — SET YOUR NEW PRICELEVEL DESCRIPTION
— PROCEED TO COPY PRICELEVEL SET INTO ANOTHER, THERE ARE 3 TABLES INVOLVED
INSERT INTO IV00108(ITEMNMBR, CURNCYID, PRCLEVEL, UOFM, TOQTY, FROMQTY, UOMPRICE, QTYBSUOM)
SELECT ITEMNMBR, CURNCYID, @NEW_PRICELEVELCODE, UOFM, TOQTY, FROMQTY, UOMPRICE, QTYBSUOM
FROM IV00108
WHERE PRCLEVEL = @EXISTING_PRICELEVELCODE
INSERT INTO IV00107 (ITEMNMBR, CURNCYID, PRCLEVEL, UOFM, RNDGAMNT, ROUNDHOW, ROUNDTO, UMSLSOPT, QTYBSUOM)
SELECT ITEMNMBR, CURNCYID, @NEW_PRICELEVELCODE, UOFM, RNDGAMNT, ROUNDHOW, ROUNDTO, UMSLSOPT, QTYBSUOM
FROM IV00107
WHERE PRCLEVEL = @EXISTING_PRICELEVELCODE
INSERT IV40800 (PRCLEVEL, DSCRIPTN)
VALUES (@NEW_PRICELEVELCODE, @NEW_PRICELEVELDESC)

 

Source: A quick way to duplicate or copy a GP dynamics PRICELEVEL SET | Collection of Useful Microsoft Dynamics GP Sql Commands, Tips and Tricks

Tags: , ,