Item Pricing – Currency Conversion Script


Setting up item pricing is always a pain, and we have several personalisation’s to make It easier for our users, however today I was advised the pricing had been setup in the sterling currency (GBP) and needed to be transferred to Euro’s automatically (EU2 on our system!). This TSQL script does this by finding the most recent GBP price and copying it to the new currency code, multiplying any prices by the predefined exchange rate.

If using this script, Please be ensure you are entirely happy with what it does prior to using it.

begin transaction
declare @ex_rate as decimal(10,2) = ‘1.28’

insert into itemprice
(
[item]
,[effect_date]
,[curr_code]
,[unit_price1]
,[unit_price2]
,[unit_price3]
,[unit_price4]
,[unit_price5]
,[unit_price6]
,[reprice]
,[brk_qty##1]
,[brk_qty##2]
,[brk_qty##3]
,[brk_qty##4]
,[brk_qty##5]
,[brk_price##1]
,[brk_price##2]
,[brk_price##3]
,[brk_price##4]
,[brk_price##5]
,[base_code##1]
,[base_code##2]
,[base_code##3]
,[base_code##4]
,[base_code##5]
,[dol_percent##1]
,[dol_percent##2]
,[dol_percent##3]
,[dol_percent##4]
,[dol_percent##5]
,[pricecode]
,[uf_pref_type]
,[uf_salesprice]
,[Uf_oem_price]
,[Uf_ShowInList]
)

select
ip.[item]
,getdate() as [effect_date]
,’EU2′ as [curr_code]
,ip.[unit_price1] * @ex_rate as [unit_price1]
,ip.[unit_price2] * @ex_rate as [unit_price2]
,ip.[unit_price3] * @ex_rate as [unit_price3]
,ip.[unit_price4] * @ex_rate as [unit_price4]
,ip.[unit_price5] * @ex_rate as [unit_price5]
,ip.[unit_price6] * @ex_rate as [unit_price6]
,ip.[reprice]
,ip.[brk_qty##1]
,ip.[brk_qty##2]
,ip.[brk_qty##3]
,ip.[brk_qty##4]
,ip.[brk_qty##5]
,ip.[brk_price##1]  * @ex_rate as [brk_price##1]
,ip.[brk_price##2]  * @ex_rate as [brk_price##2]
,ip.[brk_price##3]   * @ex_rate as [brk_price##3]
,ip.[brk_price##4]  * @ex_rate as [brk_price##4]
,ip.[brk_price##5]    * @ex_rate as [brk_price##5]
,ip.[base_code##1]
,ip.[base_code##2]
,ip.[base_code##3]
,ip.[base_code##4]
,ip.[base_code##5]
,ip.[dol_percent##1]
,ip.[dol_percent##2]
,ip.[dol_percent##3]
,ip.[dol_percent##4]
,ip.[dol_percent##5]
,ip.[pricecode]
,ip.[uf_pref_type]
,isnull(cast(ip.[uf_salesprice] as decimal(10,2))   * @ex_rate,0) as [uf_salesprice]
,isnull(cast(ip.[Uf_oem_price]as decimal(10,2))    * @ex_rate,0) as [Uf_oem_price]
,ip.[Uf_ShowInList]
from itemprice as ip
inner join (
select
item
, max(effect_date) maxeffdate
from itemprice as ip
where curr_code = ‘gbp’
group by item
) as md /* maximum details */ on md.item = ip.item and md.maxeffdate = ip.effect_date and ip.curr_code = ‘gbp’
order by ip.item

commit transaction

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.