In Progress

Convert Excel Power Query to Sap b1 SQL HANA

Job Description:

We have to convert Excel Power Query report to Sap b1 SQL HANA query.

1.

The query will will start with calculating the total sales for each item per year .

--sales per year--

SELECT "ItemCode" , "Year" , SUM("Total") FROM

(SELECT T0."ItemCode" , Extract(year FROM T1."DocDate") AS "Year" , SUM(T0."Quantity") AS "Total"

FROM "CO4"."INV1" T0

LEFT JOIN "CO4"."OINV" T1

ON T0."DocEntry" = T1."DocEntry"

WHERE T1."CANCELED" = 'N'

GROUP BY T0."ItemCode" , Extract(year FROM T1."DocDate")

UNION ALL

SELECT T0."ItemCode" , Extract(year FROM T1."DocDate") AS "Year" , SUM(-T0."Quantity") AS "Total"

FROM "CO4"."RIN1" T0

LEFT JOIN "CO4"."ORIN" T1

ON T0."DocEntry" = T1."DocEntry"

WHERE T1."CANCELED" = 'N'

GROUP BY T0."ItemCode" , Extract(year FROM T1."DocDate"))

GROUP BY "ItemCode" , "Year"

ORDER BY "ItemCode"

--end of sales per year--

Then we want unpivots the result for the last 2 years ( if the current year is in the third quarter will be counted )

Then we want to get the average of monthly sales based on the sales of previous 2 years

( Again if current year is in the third quarter the average will be "sales divided by 12+the current month" else "sales divided by 24)

2.

after that

we need Left join OBTN with OBTQ ON "itemcode" and "sysnumber"

remove Quantity = 0

Group the result by

Item code

Distribution number

expiry date

sum Quantity

3.

then

we need to filter the result

based on a predefined variable "Expiry" representing the number of months we consider the item is expired were

[ExpDate] <= [login to view URL]([login to view URL](), Expiry))

4.

then the result will be grouped and remaining will be only item code and sum of quantity

Group(Item code) : SUM(Quantity)

5.

from [OITM] we take only the active items (forzen for = "N" )

6.

left join OITM with OMRC to get the firm name

7.

left join OITM with OITB to get the group name

8.

Join the result with the table from step 3

9.

the final table

left joint the result from step 8 with the result from step 1

the result will be

8 ItemCode : 8 ItemName : 8 OnHand : 8 IsCommited : 8 OnOrder : 1Near Expiry : 1 monthly_AVG : 1 2021 sales : 1 2022 sales: 8 U_Volume_Code : 8 [login to view URL] : 8 [login to view URL]

10.

add column to check how after how many months each product will be sold lets call it "RMN"

([OnHand]+[OnOrder]-[Near Expiry])/[monthly_AVG]

11.

finally we need to add column calls status

will check if the remaining months is less than a threshold we could define as variable also

" for example 13 months " lets call it "Remaining Months"

=[RMN]<[Remaining Months]

Best Regards

Skills: SAP HANA, SQL

About the Client:
( 1 review ) Jeddah, Saudi Arabia

Project ID: #35364382