Using power query to create a pick list
Check out our SAP MRP tool, it combines material requirements for upcoming process orders (COOISPI / IW33) with the current stock on hand in multiple SLOCS (MB52).
The data is extracted and combined for easy viewing with one click of your mouse. You can easily see shortages, or plan material movements between storage locations in time for your production or planned work.
Here is the SAP stocktaking process using the AVB – Import SAP Stocktake Excel program.
The first step is to create a PID (physical inventory document) in SAP. There are a few ways to do this, below shows the method using transaction MI31. Enter the materials you wish to count, the SLOC, and whether you want Unrestricted Stock only etc.
When you have made your selections, press the execute button to proceed. Highlight all the lines you want to count and press the ‘Create Documents Directly’ button, they should all turn green and the ‘PhysInvDoc’ column will populate with the PID number. Take note of this number, or press CTRL+Y and highlight the number, then press CTRL+C to copy it into your memory.
Open the AVB – Import SAP Stocktake Excel spreadsheet. Right-Click anywhere in the PID tab and click on ‘Download PID’.
The sheet will clear itself of any existing data, and present the following pop-up box:
Enter the PID number into the pop-up box, and press ok. The PID details will now be imported into the spreadsheet (the below example has its data blurred out).
You can now treat the document as a regular spreadsheet, the print margins will be setup automatically as well as a formula to calculate the difference qty. You can apply filters to sort by the storage bin, or any other way you wish.
Print the sheet, and perform the count of the materials. When you have the sheet back, enter the results into the ‘Counted Qty’ column, noting the running totals in column L and adding comments as you go.
When you are satisfied with the count, right click anywhere in the PID tab and click ‘Upload PID’
The counted qty will now be uploaded to SAP.
Go to transaction MI20 and post the count.
The count is then complete, with the posted differences the same.
This is a free app that uses SAP scripting to move from the purchase order field straight to the delivery note field after pressing enter in MIGO.
This is very handy for speeding up goods receipts and also doubles as a check to see if your system can run AVB’s other SAP scripting tools.
Download now, remember to comment with your feedback.
Rent Intermec CK71 handheld scanners that are loaded with software for stocktaking.
We provide scanners and the software for counting inventory easily and quickly. Get a snapshot of your stock without having to invest capital into costly hardware.