Notifications
Clear all
Oct 19, 2023 8:55 pm
Hệ thống SAP Business One hiện chưa hỗ trợ các bảng lưu lịch sử của OPLN. Tuy nhiện, giá của các mặt hàng được lưu trong bảng ITM1 và chi tiết các lần thay đổi được lưu ở bảng AIT1. Do đó, SAP hỗ trợ câu query giúp truy vấn các thay đổi về giá.
SQL
/*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 )
/*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 )
/*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
/*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
)
/*Select all data from the common table expression*/
select top 10 * from pricetrack ORDER BY "ItemCode", "Pricelist_No", "LogInstanc"
HANA
CREATE COLUMN TABLE "Pricetrack" AS (
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")
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")
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
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");
SELECT * FROM "Pricetrack" ORDER BY "ItemCode", "PriceList", "LogInstanc" ;
DROP TABLE "Pricetrack";
