Inventory “adjustments” to dates in the past
In your SAP Business ByDesign system, you may need to make adjustments in your inventory.
You will use the “Physical Inventory count” documents to manage your inventory counts:
And you can use “new inventory adjustment” to make adjustments to some records of your inventory (you can select them in the search results list):
Perhaps, for some reason, you may need to make an adjustment to a date in the past.
For exemple, after your go-live, you discover that there was some errors in your loaded inventory. How can you adjust them?
You might see the field “Key Date” and think you can use it to adjust your inventory in date in the pas. You will be wrong. Why? How do these screens work?
The “new inventory adjustment” calculates the difference between the quantiy you set in the screen and the quantiy in your stock TODAY (at the moment you are in the transaction). The system doesn’t use the theorical quantity of your stock on the “Key Date” reported on the screen. The “Key Date” is used only as the accounting date for your inventory adjustments.
But, then, how can you handle this type of situation when you need to make one or more adjustments in a past date?
Imagine this example, where your initial loaded stock was “20 bottles” on 31.12.2022
But your stock today (27.05.2023) is “13 bottles”
If you start a new inventory adjustment for this product and try to set “21 bottles” as the quantity in the key date “31.12.2022”
you could expect the system to calculate a different of “1 more bottle” compared to the inventory balance on 31.12.2022
However, the system calculates a difference of “8 bottles”, because it takes today’s quantity (“13 bottles”) compared to the quantity that you fill on the screen (“21 bottles”)
If you continue, your inventory balance at 31.12.2022 will show 28 bottles (20 + 8), because the key date is used as the accounting date:
The only way to adjust the inventory to “21 bottles” as of 31.12.2022 is to use a “Return from a Cost Center” with “1 bottle”
You must to establihs a cost center for the accounting of this operation.
In this way, you’ll get the inventory balance you need as of 31.12.2022:
However, the main problem with this procedure is that you have to calculate your differences by product and fill the screen one by one, manually.
Automation of the adjustments
If you have a long list of adjustments, maybe you can think in some kind of automation
Here I share some tips that can help you achieve it:
- Get inventoy balance quantity using ODATA for Analytics
https://<your_system_url>/sap/byd/odata/scm_physicalinventory_analytics.svc/RPSCMINBU03_Q0001QueryResults?$filter=CMATERIAL_UUID eq ‘<your-material-id>’ and CLOG_AREA_UUID eq ‘<your-location + “/” + your-logisticarea-id>’ and PARA_ENTRY_PERIOD eq datetime’2022-12-31T00:00:00’&$select=KCENDING_QUANTITY&$format=json
In our example:
https://myXXXXXX.sapbydesign.com/sap/byd/odata/scm_physicalinventory_analytics.svc/RPSCMINBU03_Q0001QueryResults?$filter=CMATERIAL_UUID eq ‘AG010’ and CLOG_AREA_UUID eq ‘ESO101/ESO101-30’ and PARA_ENTRY_PERIOD eq datetime’2022-12-31T00:00:00’&$select=KCENDING_QUANTITY&$format=json
You get something like this:
the fields “KCENDING_QUANTITY” and “UCENDING_QUANTITY” tell us that we have 20 EA of this product, in this area, as of 31.12.2022
If you work with identified stocks, you will need to add an expression like this to the filter:
” and CISTOCK_UUID eq ‘<your-product>+”/”+ <your-identified-stock>’ ”
- Calculate the differences
Once you get the quantities from the inventory balance on your adjustment date, you must calculate the differences with your “actual stock” on that date. The result can be positive or negative.
- Use the inbound service to create the consumption/return
You can use the Operation Do Goods Consumption for Cost Center of the Inbound Service InventoryProcessingGoodsAndActivityConfirmationGoodsConfirmationIn to create a consumption (if your real stock is less than your original stock) or a return (if your real stock is greater than your original stock), on your key date.
Remember that you need to set the cost center for this operations
Here you can see a sample tool for adjustments automation using an Excel spreedshet
With these tips, I think you can build your own tool for automatization
Feel free to contact me if you need some kind of support