Export All Prices for All customers


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

 

Leave a comment

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