Query dùng để truy vết thay đổi giá của các mặt hàng (SAP Note 1165947)

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";