Keeping Item Vendor Pricing Aligned Across Sites


Introduction

For various business reasons, we have 2 sites that buy and sell between each other, and items need to be cost price plus a given amount. Our purchasing team have been keeping the systems in line, but humans being what they are, errors were being made, so I was asked to implement a process to do this automatically. I have done this using a stored procedure as follows :-

The Stored Procedure Code

create procedure pbti_ih_sp_SetUkToXiniaTPrices as

with UKVend as (
select
ivp.item
, ivp.vend_num
, ivp.effect_date
, ROW_NUMBER() over (partition by ivp.item, ivp.vend_num order by ivp.item, ivp.vend_num, ivp.effect_date desc)  as rn
, ivp.brk_cost##1 as UK_Price
, ivp.brk_cost##1 / 0.9 as ProposedXiniaT_Price
from live_app.dbo.itemvendprice   as ivp
where effect_date < GETDATE()
)
, UKPrice as (select
iv.item
, iv.vend_num
, iv.rank
, UKVend.effect_date
, UKVend.UK_Price
, UKVend.ProposedXiniaT_Price
from live_app.dbo.itemvend  as iv
left join UKVend on UKVend.vend_num = iv.vend_num  and UKVend.rn = 1 and iv.item = UKVend.item
where iv.rank = 1
and UKVend.vend_num is not null

)
,XTVend as (
select
ivp.item
, ivp.vend_num
, ivp.effect_date
, ROW_NUMBER() over (partition by ivp.item, ivp.vend_num order by ivp.item, ivp.vend_num, ivp.effect_date desc)  as rn
, ivp.brk_cost##1 as XT_Price
from Ireland_app.dbo.itemvendprice   as ivp
where effect_date < GETDATE()
)
, XTPrice as (select
iv.item
, iv.vend_num
, iv.rank
, XTVend.effect_date
, XTVend.XT_Price
from Ireland_app.dbo.itemvend  as iv
left join XTVend on XTVend.vend_num = iv.vend_num  and XTVend.rn = 1 and iv.item = XTVend.item
where iv.rank = 1
and XTVend.vend_num is not null

)

insert into ireland_app.dbo.itemvendprice
(
[item]
,[vend_num]
,[effect_date]
,[brk_qty##1]
,[brk_qty##2]
,[brk_qty##3]
,[brk_qty##4]
,[brk_qty##5]
,[brk_cost##1]
,[brk_cost##2]
,[brk_cost##3]
,[brk_cost##4]
,[brk_cost##5]
,[brk_qty_conv##1]
,[brk_qty_conv##2]
,[brk_qty_conv##3]
,[brk_qty_conv##4]
,[brk_qty_conv##5]
,[brk_cost_conv##1]
,[brk_cost_conv##2]
,[brk_cost_conv##3]
,[brk_cost_conv##4]
,[brk_cost_conv##5]
,[stat])

(
select
xtprice.item
,’PBT0001′
,getdate()
,1
,0
,0
,0
,0
,ukprice.ProposedXiniaT_Price
,0
,0
,0
,0
,0
,0
,0
,0
,0
,ukprice.ProposedXiniaT_Price
,0
,0
,0
,0
,’A’
from xtprice
left join ukprice  on xtprice.item = UKPrice.item
where xtprice.XT_Price – ukprice.ProposedXiniaT_Price not between -0.01 and 0.01
and xtprice.vend_num = ‘pbt0001’
)

Integrating with Syteline 8

So as our users can run this procedure on an adhoc basis I have added a button (Set PBT0001 Vendor Prices”) to one of our in-house Syteline Forms called “Purchasing Shortcuts” as shown here :-

Production Shortcuts

Production Shortcuts

Setting the Event Handler against the button to run a Stored Procedure is quite straight forward, it is a method call, with “SP!” as the IDO as shown here :-

EventHandlerEditMethodCall

To advise the user that the process has started, I originally popped in an inline script as follows :-

Sub Main()
ReturnValue = “0”
Application.ShowMessage(“Process to import UK prices to XiniaT prices has been started. Please check you vendor contract pricing that you are expecting to see changed and” _
& “ensure the process is sucessful”, MsgBoxStyle.Information, “Process Started”)

End Sub
End Class
End Namespace

This in turn generated the following message to the user.

 

UserMessage1Subsequently I decided that the user needs a more positive feedback. as such I have amended the stored procedure to have an output variable and use @@rowcount to return how many rows had been effected as follows :-

alter procedure pbti_ih_sp_SetUkToXiniaTPrices(@rows int output) as

with UKVend as (
select
ivp.item
, ivp.vend_num
, ivp.effect_date
, ROW_NUMBER() over (partition by ivp.item, ivp.vend_num order by ivp.item, ivp.vend_num, ivp.effect_date desc)  as rn
, ivp.brk_cost##1 as UK_Price
, ivp.brk_cost##1 / 0.9 as ProposedXiniaT_Price
from live_app.dbo.itemvendprice   as ivp
where effect_date < GETDATE()
)
, UKPrice as (select
iv.item
, iv.vend_num
, iv.rank
, UKVend.effect_date
, UKVend.UK_Price
, UKVend.ProposedXiniaT_Price
from live_app.dbo.itemvend  as iv
left join UKVend on UKVend.vend_num = iv.vend_num  and UKVend.rn = 1 and iv.item = UKVend.item
where iv.rank = 1
and UKVend.vend_num is not null

)
,XTVend as (
select
ivp.item
, ivp.vend_num
, ivp.effect_date
, ROW_NUMBER() over (partition by ivp.item, ivp.vend_num order by ivp.item, ivp.vend_num, ivp.effect_date desc)  as rn
, ivp.brk_cost##1 as XT_Price
from Ireland_app.dbo.itemvendprice   as ivp
where effect_date < GETDATE()
)
, XTPrice as (select
iv.item
, iv.vend_num
, iv.rank
, XTVend.effect_date
, XTVend.XT_Price
from Ireland_app.dbo.itemvend  as iv
left join XTVend on XTVend.vend_num = iv.vend_num  and XTVend.rn = 1 and iv.item = XTVend.item
where iv.rank = 1
and XTVend.vend_num is not null

)

insert into ireland_app.dbo.itemvendprice
(
[item]
,[vend_num]
,[effect_date]
,[brk_qty##1]
,[brk_qty##2]
,[brk_qty##3]
,[brk_qty##4]
,[brk_qty##5]
,[brk_cost##1]
,[brk_cost##2]
,[brk_cost##3]
,[brk_cost##4]
,[brk_cost##5]
,[brk_qty_conv##1]
,[brk_qty_conv##2]
,[brk_qty_conv##3]
,[brk_qty_conv##4]
,[brk_qty_conv##5]
,[brk_cost_conv##1]
,[brk_cost_conv##2]
,[brk_cost_conv##3]
,[brk_cost_conv##4]
,[brk_cost_conv##5]
,[stat])

select
xtprice.item
,’PBT0001′
,getdate()
,1
,0
,0
,0
,0
,ukprice.ProposedXiniaT_Price
,0
,0
,0
,0
,0
,0
,0
,0
,0
,ukprice.ProposedXiniaT_Price
,0
,0
,0
,0
,’A’
from xtprice
left join ukprice  on xtprice.item = UKPrice.item
where xtprice.XT_Price – ukprice.ProposedXiniaT_Price not between -0.01 and 0.01
and xtprice.vend_num = ‘pbt0001’

select @rows =  @@rowcount

I have then changed the event that calls the method as follows to return the output variable to a form variable as shown here :-

EventHandlerEditMethodCall2

I have then amended the script that generated the user message as shown here :-

Sub pbti_UKtoXiniaTPricesStarted()
Application.ShowMessage(“Process to import UK prices to XiniaT prices has been started. Please check you vendor contract pricing that you are expecting to see changed and” _
& ” ensure the process is sucessful” _
& vbCrLf _
& vbCrLf _
& ThisForm.Variables(“PBTI_RowsEffected”).Value.ToString() _
& ” rows updated” _
, MsgBoxStyle.Information, “Process Started ……”)
End Sub

This then generated the following user message box.

 

UserMessage2

Leave a comment

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