Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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

9 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
  4. I run the script against the TWO (GP2015) and got this errors:
    Msg 173, Level 15, State 1, Line 14
    The definition for column 'ITEMNumberNVARCHAR' must include a data type.
    Msg 102, Level 15, State 1, Line 25
    Incorrect syntax near '('.
    Msg 137, Level 15, State 1, Line 34
    Must declare the scalar variable "@QuantityBalance".
    Msg 137, Level 15, State 1, Line 35
    Must declare the scalar variable "@CostBalance".
    Msg 137, Level 15, State 2, Line 51
    Must declare the scalar variable "@FROM".
    Msg 137, Level 15, State 2, Line 61
    Must declare the scalar variable "@Date".
    Msg 137, Level 15, State 2, Line 67
    Must declare the scalar variable "@QuantityBalance".
    Msg 137, Level 15, State 2, Line 68
    Must declare the scalar variable "@CostBalance".
    Msg 137, Level 15, State 2, Line 79
    Must declare the scalar variable "@Date".
    Msg 102, Level 15, State 1, Line 87
    Incorrect syntax near 'IV_CurSOR'.
    Msg 102, Level 15, State 1, Line 93
    Incorrect syntax near 'DEALLOCATEIV_CurSOR'.

    ReplyDelete
  5. Got exactly the same message when I run the SQL query on GP2016.

    ReplyDelete
  6. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete