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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.