Introduction
A company who are part of the Syteline User Group approached me for some assistance recently, as they were not having much joy with Infor’s Centre of Excellence. They had a requirement to export all their customers custom pricing so as it can be imported into an ecommerce site. This soulds straight forward but due to the numerous pricing options available it is quite difficult.
Please note the following :-
- This was written for Syteline 8, the company in question is running Syteline 9 and it took a modification to the table names of the source data inorder for it to work on their system.
- If you wish to re-use this code, please read the notes section first, it does not cover all possible pricing structures available in Syteline, rather just those appropriate to the company who requested the data
- I use a lot of row_numbers to select the first instance of a record, but double the workload by putting the results into another temporary table (I’m not describing that particularly well but looking through the code you will see what I mean!) – This isn’t very efficient, but helped during the development phase for various reasons and I have not had change to re-edit it.
The Code
create procedure JRF_CustomerPricing as
/*********************************************************************************
* *
* www.jordanfey.co.uk *
* *
* 2015/02/02 V1.0 Written for a company *
* *
* *
* Purpose *
* *
* The purpose of this script is to generate an output file for all customer *
* prices for the purpose of an ecommerce site written by a third party *
* *
* The aims of this script are for all components to be confined within it, *
* so as it is not necessary to insert additonal functions, views or *
* procedures to make it work, and for the code to be commented and nested *
* in such a way that anyone should be able to support it. *
* *
* This code has also been written in a very “Modular” way whereas each *
* data source is exctracted seperately and joined together at the end of the *
* script. This should mean any errors identified with the output should be *
* easier to resolve. *
* *
* Assumptions *
* *
* 1) Price is “Contract Price” lited on form Item Customer Price *
* *
* 2) No break Pricing is in use *
* *
* 3) If using Cost as your break price the cost is the “unit Code” as *
* displayed on the general tab of the Items form *
* *
* 3) If using Cost as your break price and foreign currencies, the cost is *
* translated at the current rate set in your system, therefore prices *
* customers see will fluctuate as exchange rates are altered *
* *
* 4) If using Cost as your break price and foreign currencies, the exchange *
* rate in use is the sell rate *
* *
* 5) There is no limitations on customers – I would strongly recomend a *
* change to this script to limit the output to only customers who have *
* a create date OR a last invoice date within the last 6 months *
* *
* 6) There is no limiation on the products exported. I would strongly *
* recomend use of a UDF on the items tab to state whether the prduct is *
* active for website itnegration. We have also found it useful to have a *
* “Launch” date UDF to keep historw of when products have been made live. *
* *
* 7) To match the syteline standard behaviour, if no pricing formula is *
* configured against the customer, the base price is used, and if no base *
* price is found in the customers currency, the domestic currency base *
* price is used, with the current exchange rate. When this happens, this *
* scrpt always uses Unit Price 1 *
* *
* 8) Because of the nature of the data, this script uses a holding table to *
* store the output that can then be used as a data source. At present on *
* the system I have used to test this I have returned 2’390’752 records *
* in 1 minute 30 seconds. *
* *
* 9) In no event shall I, (Jordan Fey), any of the companies I work for *
* or organisations I may represent now or in the future be liable to *
* you or any party related to you for any indirect, incidental, *
* consequential, special, exemplary, or punitive damages or lost *
* profits, even if your organisation has been advised of the *
* possibility of such damages. *
* *
*********************************************************************************/
— All variables to be declared in one position within script
declare @ipa /*Customer Item Price All */ as table(
item dbo.itemtype
, curr_code dbo.currcodetype
, effect_date datetime
, unit_price1 dbo.costprctype
, unit_price2 dbo.costprctype
, unit_price3 dbo.costprctype
, unit_price4 dbo.costprctype
, unit_price5 dbo.costprctype
, unit_price6 dbo.costprctype
, rn integer
)
declare @ipc /*Custoemr Item Price Current */ as table(
item dbo.itemtype
, curr_code dbo.currcodetype
, effect_date datetime
, unit_price1 dbo.costprctype
, unit_price2 dbo.costprctype
, unit_price3 dbo.costprctype
, unit_price4 dbo.costprctype
, unit_price5 dbo.costprctype
, unit_price6 dbo.costprctype
, rn integer
)
declare @pfa /*Price Formula All */ as table (
[priceformula] dbo.priceformulatype
,[effect_date] datetime
,[curr_code] dbo.currcodetype
,[first_base] dbo.pricebasecodetype
,[first_dol_percent] dbo.listamountpercenttype
,[first_price] dbo.costprctype
, rn integer
)
declare @pfc /*Price Formula Current */ as table (
[priceformula] dbo.priceformulatype
,[effect_date] datetime
,[curr_code] dbo.currcodetype
,[first_base] dbo.pricebasecodetype
,[first_dol_percent] dbo.listamountpercenttype
,[first_price] dbo.costprctype
, rn integer
)
declare @cpa /*Customer Price All */ as table (
cust_num dbo.custnumtype
, item dbo.itemtype
, cont_price dbo.costprctype
, effect_date datetime
, rn integer
)
declare @cpc /*Customer Price current */ as table (
cust_num dbo.custnumtype
, item dbo.itemtype
, cont_price dbo.costprctype
, effect_date datetime
, rn integer
)
declare @era /* Exchange Rate All */ as table (
from_curr_code dbo.currcodetype
, to_curr_code dbo.currcodetype
, eff_date datetime
, sell_rate dbo.amounttype
, buy_rate dbo.amounttype
, rn integer
)
declare @IPF /*Item Price Formula*/ as table (
priceformula dbo.priceformulatype
, first_base dbo.pricebasecodetype
, first_dol_percent dbo.listamountpercenttype
, first_price dbo.amounttype
,curr_code dbo.currcodetype
,item dbo.itemtype
, BasePrice dbo.amounttype
, CalculatedPrice dbo.amounttype
, sell_rate dbo.amounttype
)
declare @DomCur /*DomesticCurrenccy*/ as dbo.currcodetype = (SELECT curr_code FROM currparms)
— Phase 1 : Check the holding table exists – if not, add it
if OBJECT_ID(‘dbo.JRF_CustomerPricing_HOLDING’,‘u’) is null
begin
CREATE TABLE [dbo].[JRF_CustomerPricing_HOLDING](
[CreatedBy] [dbo].[UsernameType] NOT NULL
,[UpdatedBy] [dbo].[UsernameType] NOT NULL
,[CreateDate] [dbo].[CurrentDateType] NOT NULL
,[RecordDate] [dbo].[CurrentDateType] NOT NULL
,[RowPointer] [dbo].[RowPointerType] NOT NULL
,[NoteExistsFlag] [dbo].[FlagNyType] NOT NULL
,[InWorkflow] [dbo].[FlagNyType] NOT NULL
,cust_num dbo.custnumtype null
, curr_code dbo.currcodetype
, item dbo.itemtype
, CustomerContractPrice dbo.amounttype
, PriceCode dbo.pricecodetype
, ItemPriceForeign dbo.amounttype
, ItemPriceDomestic dbo.amounttype
, PriceFormulaPrice dbo.amounttype
, ChargePrice dbo.amounttype
,CONSTRAINT [IX_JRF_CustomerPricing_HOLDING_RowPointer] UNIQUE NONCLUSTERED
(
[RowPointer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_CreatedBy] DEFAULT (suser_sname()) FOR [CreatedBy]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_UpdatedBy] DEFAULT (suser_sname()) FOR [UpdatedBy]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_RecordDate] DEFAULT (getdate()) FOR [RecordDate]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_RowPointer] DEFAULT (newid()) FOR [RowPointer]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_NoteExistsFlag] DEFAULT ((0)) FOR [NoteExistsFlag]
ALTER TABLE [dbo].[JRF_CustomerPricing_HOLDING] ADD CONSTRAINT [DF_JRF_CustomerPricing_HOLDING_InWorkflow] DEFAULT ((0)) FOR [InWorkflow]
end
— Phase 2 : Truncate the holding table
truncate table [dbo].[JRF_CustomerPricing_HOLDING]
— Phase 3 : Get the Exchange Rates
insert into @era (from_curr_code, to_curr_code, eff_date, sell_rate, buy_rate, rn)
select
from_curr_code
, to_curr_code
, eff_date
, buy_rate
, sell_rate
, row_number() over (partition by from_curr_code order by from_curr_code, eff_date desc) as rn
from currate
where eff_date <= getdate()
and to_curr_code = @DomCur
— Phase 4 : Get all item prices
insert into @ipa (item, curr_code, effect_date, unit_price1, unit_price2, unit_price3, unit_price4, unit_price5, unit_price6, rn)
Select
item
, curr_code
, effect_date
, unit_price1
, unit_price2
, unit_price3
, unit_price4
, unit_price5
, unit_price6
, ROW_NUMBER() over (Partition by item, curr_code order by item, curr_code, effect_date desc) rn
from itemprice as ip
where CAST(effect_date as DATE) <= CAST(getdate() as DATE)
insert into @ipc (item, curr_code, effect_date, unit_price1, unit_price2, unit_price3, unit_price4, unit_price5, unit_price6, rn)
select
item
, curr_code
, effect_date
, unit_price1
, unit_price2
, unit_price3
, unit_price4
, unit_price5
, unit_price6
, rn
from @ipa
where rn = 1
— Phase 5 : Get all price formulas
insert into @pfa ([priceformula],[effect_date],[curr_code],[first_base],[first_dol_percent],[first_price],rn)
SELECT [priceformula]
,[effect_date]
,[curr_code]
,[first_base]
,[first_dol_percent]
,[first_price]
,row_number() over (partition by priceformula, curr_code, effect_date order by priceformula, curr_code, effect_date desc)
FROM [priceformula] as pf
where effect_date <= GETDATE()
insert into @pfc ([priceformula],[effect_date],[curr_code],[first_base],[first_dol_percent],[first_price],rn)
SELECT [priceformula]
,[effect_date]
,[curr_code]
,[first_base]
,[first_dol_percent]
,[first_price]
,rn
FROM @pfa as pf
where rn = 1
— Phase 6 : Get all customer contract prices
insert into @cpa (cust_num, item, cont_price, effect_date, rn)
select
cust_num
, item
, cont_price
, effect_date
, ROW_NUMBER() over (Partition by cust_num, item order by cust_num, item, effect_date desc) rn
from itemcustprice as icp
where CAST(effect_date as DATE) <= CAST(getdate() as DATE)
and cust_item_seq = 1
— Phase 7 : Get current customer contract prices
insert into @cpc (cust_num, item, cont_price, effect_date, rn) (
SELECT
cust_num
, item
, cont_price
, effect_date
, rn
FROM @cpa
WHERE rn = 1
)
— Phase 8 : Get a price list for each price formula
insert into @IPF (priceformula, first_base, first_dol_percent, first_price,curr_code,item, BasePrice,CalculatedPrice, sell_rate)
select
pfc.priceformula
, pfc.first_base
, pfc.first_dol_percent
, pfc.first_price
,pfc.curr_code
,ipc.item
, case
when pfc.first_base = ‘P1’ then ipc.unit_price1
when pfc.first_base = ‘P2’ then ipc.unit_price2
when pfc.first_base = ‘P3’ then ipc.unit_price3
when pfc.first_base = ‘P4’ then ipc.unit_price4
when pfc.first_base = ‘P5’ then ipc.unit_price5
when pfc.first_base = ‘P6’ then ipc.unit_price6
when pfc.first_base = ‘c’ then i.unit_cost
end as BasePrice
, case
when pfc.first_base = ‘P1’ and pfc.first_dol_percent = ‘p’ then (ipc.unit_price1/100) * (100 + pfc.first_price)
when pfc.first_base = ‘P1’ and pfc.first_dol_percent = ‘a’ then ipc.unit_price1 + pfc.first_price
when pfc.first_base = ‘P2’ and pfc.first_dol_percent = ‘p’ then (ipc.unit_price2/100) * (100 + pfc.first_price)
when pfc.first_base = ‘P2’ and pfc.first_dol_percent = ‘a’ then ipc.unit_price2 + pfc.first_price
when pfc.first_base = ‘P3’ and pfc.first_dol_percent = ‘p’ then (ipc.unit_price3/100) * (100 + pfc.first_price)
when pfc.first_base = ‘P3’ and pfc.first_dol_percent = ‘a’ then ipc.unit_price3 + pfc.first_price
when pfc.first_base = ‘P4’ and pfc.first_dol_percent = ‘p’ then (ipc.unit_price4/100) * (100 + pfc.first_price)
when pfc.first_base = ‘P4’ and pfc.first_dol_percent = ‘a’ then ipc.unit_price4 + pfc.first_price
when pfc.first_base = ‘P5’ and pfc.first_dol_percent = ‘p’ then (ipc.unit_price5/100) * (100 + pfc.first_price)
when pfc.first_base = ‘P5’ and pfc.first_dol_percent = ‘a’ then ipc.unit_price5 + pfc.first_price
when pfc.first_base = ‘P6’ and pfc.first_dol_percent = ‘p’ then (ipc.unit_price6/100) * (100 + pfc.first_price)
when pfc.first_base = ‘P6’ and pfc.first_dol_percent = ‘a’ then ipc.unit_price6 + pfc.first_price
when pfc.first_base = ‘C’ and pfc.first_dol_percent = ‘p’ and pfc.curr_code = @DomCur then (i.unit_cost/100) * (100 + pfc.first_price)
when pfc.first_base = ‘C’ and pfc.first_dol_percent = ‘a’ and pfc.curr_code = @DomCur then i.unit_cost + pfc.first_price
when pfc.first_base = ‘C’ and pfc.first_dol_percent = ‘p’ and pfc.curr_code <> @DomCur then ((i.cur_u_cost*era.sell_rate)/100) * (100 + pfc.first_price)
when pfc.first_base = ‘C’ and pfc.first_dol_percent = ‘a’ and pfc.curr_code <> @DomCur then (i.cur_u_cost*era.sell_rate) + pfc.first_price
else null
end as CalculatedPrice
, era.sell_rate
from @pfc as pfc
cross apply @ipc as ipc
left join item as i on i.item = ipc.item
left join @era as era on era.from_curr_code = pfc.curr_code and era.to_curr_code = @DomCur and era.rn = 1
order by ipc.item, pfc.priceformula
— Phase 9 : Link data together and insert into holding table
Insert into [dbo].[JRF_CustomerPricing_HOLDING] (
cust_num
, curr_code
, item
, CustomerContractPrice
, PriceCode
, ItemPriceForeign
, ItemPriceDomestic
, PriceFormulaPrice
, ChargePrice )
select
c.cust_num
, ca.curr_code
, i.item
,cpc.cont_price CustomerContractPrice
, c.pricecode
, ipcf.unit_price1 ItemPriceForeign
, ipcd.unit_price1 ItemPriceDomestic
,ipf.CalculatedPrice PriceFormulaPrice
, Coalesce(cpc.cont_price,ipf.CalculatedPrice,ipcf.unit_price1,ipcf.unit_price1,ipcd.unit_price1 * era.sell_rate) ChargePrice
from customer as c
left join custaddr as ca on c.cust_num = ca.cust_num and c.cust_seq = ca.cust_seq
cross apply item as i
left join @cpc as cpc on cpc.item = i.item and cpc.cust_num = c.cust_num
left join @IPF as IPF on ipf.item = i.item and ipf.curr_code = ca.curr_code and ipf.priceformula = c.pricecode
left join @ipc as ipcf /*Item Price Current Foreign */ on ipcf.curr_code = ca.curr_code and ipcf.item = i.item
left join @ipc as ipcd /*Item Price Current Domestic */ on ipcd.curr_code = @DomCur and ipcd.item = i.item
left join @era as era on era.from_curr_code = ca.curr_code and era.to_curr_code = @DomCur and era.rn = 1
where c.cust_seq = 0
–and i.uf_pbti_status = ‘live’
and i.stat <> ‘o’
and (c.last_inv > dateadd(month,-6,GETDATE()) or c.CreateDate > dateadd(month,-6,GETDATE()))
and Coalesce(cpc.cont_price,ipf.CalculatedPrice,ipcf.unit_price1,ipcf.unit_price1,ipcd.unit_price1 * era.sell_rate) is not null
— Phase 10 display the records