Follow

Tracking Item Price Changes Query

 

Solution
There is no Price History table for the Price List table. However, the item prices are saved in the ITM1 table and there is a history table for this, AIT1. Consequently, to track the change in item prices, a user query is necessary.

 

Step By Step Guide

Please save the attached query to the Query Manager. To do this:

  1. Open the Query Generator ( ). Reports -> Query Generatormenu.png
  2. Press Execute without entering any table information.exececute.png
  3. Click the pencil icon to the left of the 'Select*'  statement. This opens the query for editing.pencil.png
  4. Delete the words 'SELECT *' and copy in the query from below or from the PDF filer here:Price History Query
  5. Press Save; the Query Manager opens.
  6. Click a title from the displayed list. In the Query Name field, enter a name for the query. 
  7. Press Save; the query is saved in the Query Manager. 

 Query

/*This query uses Common Table Expression (CTE) to return only those rows
where there is a price change per item per price list.*/
With PriceTrack (ItemCode, Pricelist_No, Pricelist_Name, Currency, Price, UpdateDate, LogInstanc) as
(

/* Select each item's price, per log instance, in a specific price
list and return those rows where the price is different */

Select distinct T0.itemcode, T1.PriceList, T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc
FROM
AITM T0
Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode
and t0.loginstanc = t1.loginstanc
left outer join AIT1 T3 on T1.itemcode = T3.itemcode
and t1.pricelist = T3.pricelist
and t1.loginstanc > t3.loginstanc
INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where (t1.price <> t3.price )
AND T2.ListName = [%0]

/*select price changes where only the currency changes*/
union
Select distinct T0.itemcode, T1.PriceList , T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc
FROM
AITM T0
Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode
and t0.loginstanc = t1.loginstanc
left outer join AIT1 T3 on T1.itemcode = T3.itemcode
and t1.pricelist = T3.pricelist
and t1.loginstanc > t3.loginstanc
INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum
where (t1.price = t3.price and T1.currency <> T3.currency ) AND T2.ListName = [%0]

/*Select factors which have not been manually updated.*/
union
Select distinct T5.itemcode, T5.PriceList , T2.ListName, t5.currency, T5.Price, '', T5.loginstanc
FROM
AITM T0
Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode
and t0.loginstanc = t1.loginstanc
left outer join AIT1 T3 on T1.itemcode = T3.itemcode
and t1.pricelist = T3.pricelist
and t1.loginstanc > t3.loginstanc
Right Outer join OPLN T4 on T1.PriceList <> T4.ListNum
Inner join ITM1 T5 on T4.listnum = T5.pricelist
INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where
T4.Base_Num <> T4.listNum
and
T5.price <> 0
AND T2.ListName = [%0]

/*Exclude all those incidents where the price has not changed.
e.g. log isntance 5 shows a change to the price of $20 but there is no subsequent price
change until log instance 10. This section removes all the irrelevant rows from the result
set */

except
Select distinct T0.itemcode,T1.PriceList, T2.ListName, t1.currency, T1.Price, T0.UpdateDate, T1.loginstanc
FROM
AITM T0
Inner Join AIT1 T1 on T0.itemcode = T1.Itemcode
and t0.loginstanc = t1.loginstanc
left outer join AIT1 T3 on T1.itemcode = T3.itemcode
and t1.pricelist = T3.pricelist
and t1.loginstanc > t3.loginstanc
INNER JOIN OPLN T2 on T1.PriceList = T2.ListNum

where t1.price = t3.price and t1.currency =T3.currency

AND T2.ListName = [%0]
)

/*Select all data from the common table expression*/
select * from pricetrack ORDER BY "ItemCode", "Pricelist_No", "LogInstanc"

Explanation
Query with some explanation:

  • Copy the query (SQL or HANA) from the relevant attached file.
  • This query detects all price changes, per item per price list, in the AIT1 table.
  • The query detects all regular manual changes to the item prices in its price list.
  • Changes to the currency but not the price.
  • It tracks the item price in a target price list when its price is changed in its base price list.
  • To limit the query to a specific item and/or price list, add a where condition to the last select statement.
    • For example:
      select * from pricetrack where Itemcode = 'Itemcode1' and Pricelist = '1'
  • To use SAP Business One variables in this section, the code appears as follows:
    select * from pricetrack where Itemcode = '[%1]' and Pricelist = '[%2]'

 

 

Version 4.7.1.0

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments