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
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 :-
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.
Subsequently 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 :-
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.
