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:

• Historical IV Trial Balance - Per Period
• HITB Per Period (Cumulative) –Inventory Balance on a Prior Date
• IV Available Quantity Report - Pivot
• Inventory Balance Between Two Dates
• Cost Layers Analysis – SQL Script

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