Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Tuesday, August 11, 2015

Item Stock Movement Report


Throughout the previous years, we have received the same request to modify the Historical Inventory Trial Balance report so that it reflects the following modification:
  • Cumulative Quantity Balance (much like the item stock inquiry)
  • Cumulative Cost Balance (much like the item stock inquiry)
  • Restrict the report to specific date range
Additionally, the report should be exportable to excel in a proper format, without any work around solutions such as comma delimited ...etc. I have provided several customized reports covering this area, such as:
In this post, I am providing more of an enhanced report with further enhanced functionality and comprehensive layout. The report was deployed on Excel so that parameters can be passed such as Item Number, Date Range and Location. 
 
           Report Layout - Generation Page
As you pass the parameters, the report will be generated in another sheet, which is the report layout as shown below:
Report Layout - Item Stock Movement 
 
Document Types 
The reports includes all inventory-related transaction types such as receipts, adjustments, transfers and even cost adjustments. The beginning balance will be calculated according to the date ranged specified at the report generation page.

 
SQL Script 
The script used to generate this report is different than the previous ones mentioned above, the script is enhanced to retrieve data in a very efficient way in terms of retrieval time, it was tested on a database with over than a million record in the "SEE30303" table, and the retrieval time was almost 2 seconds.
 
Here is the SQL Script
 
/*-----------------------------------------------------------------------
Creation Date: The 11th of August, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to provide Inventory Stock Movement
Revision History:

Revision No.            Revision Date    Description
1                       11/08/2015       Original Version
------------------------------------------------------------------------ */

DECLARE @ResutlTable TABLE
    (
      [Date] DATETIME ,
      ITEMNumber NVARCHAR(MAX) ,
      LocationCode NVARCHAR(MAX) ,
      Quantity NUMERIC(38, 10) ,
      Quantitybalance NUMERIC(38, 10) ,
      Cost NUMERIC(38, 10) ,
      CostBalance NUMERIC(38, 10)
    )

DECLARE @From DATE ,
    @To DATE ,
    @Date DATETIME ,
    @ItemNumber NVARCHAR(MAX) ,
    @LocationCode NVARCHAR(MAX) ,
    @Quantity NUMERIC(38, 10) ,
    @Cost NUMERIC(38, 10) ,
    @QuantityBalance NUMERIC(38, 10) ,
    @CostBalance NUMERIC(38, 10)
      
      
      
SET @QuantityBalance = 0
SET @CostBalance = 0
--SET @ItemNumber = ''
--SET @LocationCode = ''
--SET @From = ''
--SET @To = ''

DECLARE IV_CurSOR CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
    SELECT  RANK()
              OVER ( ORDER BY DOCDATE, DOCNUMBR, DEX_ROW_ID )
              AS Record_Index ,
            ITEMNMBR ,
            LOCNCODE ,
            TRXQTYInBase ,
            EXTDCOST
    FROM    dbo.SEE30303
    WHERE   DOCDATE BETWEEN @FROM AND @To
            AND ITEMNMBR = @ItemNumber
            AND LOCNCODE = @LocationCode
    ORDER BY DOCDATE ,
            DOCNUMBR ,
            DEX_ROW_ID

OPEN IV_CurSOR;

FETCH NEXT FROM IV_CurSOR
       INTO @Date, @ItemNumber,
               @LocationCode, @Quantity, @Cost

WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @QuantityBalance = @QuantityBalance + @Quantity
        SET @CostBalance = @CostBalance + @Cost

        INSERT  @ResutlTable
                ( [Date] ,
                  ITEMNumber ,
                  LocationCode ,
                  Quantity ,
                  Quantitybalance ,
                  Cost ,
                  CostBalance
                )
                SELECT  @Date ,
                        @ItemNumber ,
                        @LocationCode ,
                        @Quantity ,
                        @QuantityBalance ,
                        @Cost ,
                        @CostBalance;

        FETCH NEXT FROM IV_CurSOR
                     INTO @Date, @ItemNumber,
                            @LocationCode, @Quantity, @Cost;
    END

CLOSE IV_CurSOR;
DEALLOCATE IV_CurSOR;

SELECT  *
FROM    @ResutlTable


Best Regards, 
Mahmoud M. AlSaadi

6 comments:

  1. Hi Mahmoud,

    Perfect timing on this - Thank you! A client was looking for a stock movement report. But I tested this on my GP install for TWO and got blank results. Would you be able to create a SQL view for this? And can you please let me know why use the SEE30303 vs any of the IV tables? Thanks!!

    ReplyDelete
    Replies
    1. Hello Gina
      Hope you are doing just well
      Practically, the SEE30303 (Historical Inventory Trial Balance) includes a detail log for all the cost adjustments. This provides more drill down capabilities on the cost changes.

      If the script above is returning no results, you do need to check on whether your HITB (SEE30303) has got any data or not.

      Your feedback is highly appreciated,
      Mahmoud M. AlSaadi

      Delete
  2. My report returned no result also...I don't see the SEE30303 table in my database. Is there another table that can create a movement report?

    ReplyDelete
    Replies
    1. You can deploy HITB (Historical Inventory Trial Balance) report by running the HITB reset tool.

      Check it out on:
      http://mahmoudsaadi.blogspot.com/2014/04/hitb-essentials-series-hitb-reset-tool.html

      Delete
  3. What are the steps to deploy this SQL script to Excel.
    Thanks

    ReplyDelete
    Replies
    1. Basic Knowledge in SQL and Excel :)
      You may discuss this with the IT person in your company.

      Let me know if you have any further inquiries or concerns,

      Best Regards,
      Mahmoud M. AlSaadi

      Delete