Stock Count Macro Generator - Macro Essentials Series (Free Tool - Beta Version)

As part of the Macro Essentials Series, another tool is being delivered today which aims to make the data entry process into Dynamics GP easier and more efficient. The purpose of this tool is to do the following:

• Retrieve a stock count Schedule from Dynamics GP
• Fill out the actual stock quantity on the Excel Sheet, either manually or by considering a bar code reader solution for further automation
• Generate a Macro in order to be enter actual quantities into Dynamics GP for a specific stock count schedule and calculate variances automatically. 

In order to use this tool, you need to follow the steps below:

1. Run the SQL command on the GP company database, which creates a stored procedure that "reads" the stock count schedule details
2. Open the Excel file, and adjust the ODBC connection by providing the SQL server name and required authentication to login 
3. Fill out the Stock Count Schedule ID on the home page, and refresh the Excel in order to retrieve the stock count schedule details
4. Fill out Actual Quantities on the Macro Page. 
5. Copy the Macro column into Word (to preserve the code formatting), then into a Macro file. 
6. Run he Macro on the Stock Count entry

SQL Script : 

CREATE PROCEDURE [dbo].[RetriveStockCount]

    @StockCountID AS NVARCHAR(MAX)

AS

    SELECT  CASE WHEN ROW_NUMBER() OVER ( ORDER BY A.STCKCNTID ) > 9 THEN 9

                 ELSE ROW_NUMBER() OVER ( ORDER BY A.STCKCNTID )

            END AS Row_ID ,

            A.STCKCNTID StockCountID ,

            A.DOCDATE DocumentDate ,

            CASE A.STCKCNTSTTS

              WHEN 1 THEN 'Available'

              WHEN 2 THEN 'Started'

              WHEN 3 THEN 'Entered'

              ELSE ''

            END AS StockCountStatus ,

            B.ITEMNMBR ItemNumber ,

            B.LOCNCODE LocationCode ,

            B.CAPTUREDQTY CapturedQuantity ,

            C.ATYALLOC AllocatedQuantity

    FROM    dbo.IV10300 AS A

            LEFT OUTER JOIN dbo.IV10301 AS B ON B.STCKCNTID = A.STCKCNTID

            LEFT OUTER JOIN dbo.IV00102 AS C ON C.ITEMNMBR = B.ITEMNMBR

                                                AND C.LOCNCODE = A.LOCNCODE

    WHERE   A.STCKCNTID = @StockCountID

            AND C.RCRDTYPE <> 1

    ORDER BY B.ITEMNMBR ,

            A.LOCNCODE;

GO




On the Macro Generator file, you need to fill out the "Stock Count Schedule ID" in order to retrieve its details:

You can download the Stock count Macro generator files from this link.


Best Regards,
Mahmoud M. AlSaad