EVOLUTION OF STOCK QUERY

bradderz — Tue, 26 Mar 2024 12:10:51 GMT

Dear Experts.

I am currently in the mist of trying to develop a query in SAP Business one in the aim to display visibility based on the MRP and forecasts.

what i would like to be able to see is each item stored in the MRP senario is populated in the results across 3 lines and then the next 17 months (a column a month) display the SALES, PURCHASES AND STOCK VALUE an ensample of which i have enclosed below:

Part Code	Overview Category	Price	Item Group	Mar	Mar-24	Apr	Apr-24	May	May-24	Jun	Jun-24	Jul	Jul-24	Aug	Aug-24
TOY A	STOCK	10	GDX	17.00	170.00	12.00	120.00	12.00	120.00	15.00	150.00	15.00	150.00	15.00	150.00
TOY A	PURCHASES	10	GDX	0.00	0.00	0.00	0.00	5.00	50.00	0.00	0.00	0.00	0.00	0.00	0.00
TOY A	SALES	25	GDX	5.00	125.00	0.00	0.00	3.00	75.00	0.00	0.00	0.00	0.00	0.00	0.00

i have been playing with some logic ideas however i have been unsuccessful to this point. this is what i managed to come up with to commence my logic :

SELECT
MRP."ItemCode",
MRP."Month",
SUM(Sales."Quantity") AS SalesQuantity,
SUM(Purchases."Quantity") AS PurchasesQuantity,
SUM(Stock."Quantity" * Item."Price") AS StockValue
FROM
"MRP1" MRP
LEFT JOIN
"INV1" Sales ON MRP."ItemCode" = Sales."ItemCode" AND MRP."Month" = MONTH(Sales."DocDate")
LEFT JOIN
"OPOR" Purchases ON MRP."ItemCode" = Purchases."ItemCode" AND MRP."Month" = MONTH(Purchases."DocDate")
LEFT JOIN
"OITW" Stock ON MRP."ItemCode" = Stock."ItemCode" AND MRP."Month" = Stock."Month"
LEFT JOIN
"OITM" Item ON MRP."ItemCode" = Item."ItemCode"
GROUP BY
MRP."ItemCode", MRP."Month"
ORDER BY
MRP."ItemCode", MRP."Month"
Any assistance would be greatly appreciated.
Many thanks

Re: EVOLUTION OF STOCK QUERY

LoHa — Tue, 26 Mar 2024 14:15:49 GMT

Hi,

it seems that you use HANA. Normally in SQL i would say th best practice would be a pivot table but it is not avaiable in HANA but here is a workaround
https://community.sap.com/t5/technology-blogs-by-sap/how-to-pivot-unpivot-in-sap-hana/ba-p/13420392

regards Lothar

Re: EVOLUTION OF STOCK QUERY

bradderz — Wed, 03 Apr 2024 16:16:07 GMT

Thanks Loha

Question Re: EVOLUTION OF STOCK QUERY in Supply Chain Management Q&A

EVOLUTION OF STOCK QUERY

Re: EVOLUTION OF STOCK QUERY

Re: EVOLUTION OF STOCK QUERY