This particular issue stemmed from the addition of the checkboxes for Item Quantity and Amount in the Dynamics GP Report Writer version of the HITB report. The parameter change was made in the stored procedure for the report, but not reflected in the SSRS version of the report. Adding the following information to the see HITB stored procedure for the SSRS version of the report will correct the issue.
QTYONHND int NOT NULL
Both changes are highlighted in Yellow, but you can use the stored procedure script below to modify the stored proc and let me know if the issue occurs or not. You will need to change the DBNAME portion of the USE [DBNAME] part of the script to use the company database name where reports are deployed and run it for each company database that has reports deployed.
USE [DBNAME]
GO
/****** Object: StoredProcedure [dbo].[seeHITB] Script Date: 11/19/2018 3:48:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[seeHITB] @I_nSortBy tinyint = NULL,
@I_nReceiptOptions tinyint = NULL,
@I_sStartItemNumber char(31) = NULL,
@I_sEndItemNumber char(31) = NULL,
@I_sStartAccountNumber char(128) = NULL,
@I_sEndAccountNumber char(128) = NULL,
@I_sStartLocationCode char(11) = NULL,
@I_sEndLocationCode char(11) = NULL,
@I_nStartQTYType int = NULL,
@I_nEndQTYType int = NULL,
@I_dtStart datetime = NULL,
@I_dtEnd datetime = NULL,
@sStartClass char(11) = NULL,
@sEndClass char(11) = NULL,
@sStartGenericDesc char(11) = NULL,
@sEndGenericDesc char(11) = NULL,
@I_fUseGLPostDate tinyint = NULL,
@I_fIncludeZeroQtyItems tinyint = NULL,
@O_SQL_Error_State int = 0 output
as
declare @numsegs int,
@natseg tinyint,
@cnt tinyint,
@acctseg char(40),
@acctsegd varchar(400),
@acctsegl varchar(400),
@acctsegt varchar(4000),
@guid uniqueidentifier,
@temptable varchar(50),
@length tinyint
select @numsegs = (select MXNUMSEG
from DYN18..SY003001)
select @cnt = 1
select @acctseg = ' '
select @acctsegd = ' '
select @guid = NEWID()
select @temptable = @guid
set @temptable = '##' + substring(@temptable, 1, 8)
while @cnt <= @numsegs
Begin
select @length = SGMNTLTH
from DYN18..SY00302
where SGMTNUMB = @cnt
select @acctseg = ' [ACTNUMBR_' + ltrim(STR(@cnt))
+ '] [char] (' + ltrim(STR(@length))
+ ') NOT NULL, ' + char(13)
set @acctsegl = rtrim(@acctseg)
select @cnt = @cnt + 1
set @acctsegd = @acctsegd + @acctsegl
End
set @acctsegt = 'CREATE TABLE ' + @temptable + '(' + ( ltrim(rtrim(@acctsegd)) ) +
' ITEMNMBR char(31) NOT NULL, LOCNCODE char(11) NOT NULL, DOCDATE datetime NOT NULL CHECK (DATEPART(hour,DOCDATE) = 0 and DATEPART(minute,DOCDATE) = 0 and DATEPART(second,DOCDATE) = 0 and DATEPART(millisecond,DOCDATE) = 0), GLPOSTDT datetime NOT NULL CHECK (DATEPART(hour,GLPOSTDT) = 0 and DATEPART(minute,GLPOSTDT) = 0 and DATEPART(second,GLPOSTDT) = 0 and DATEPART(millisecond,GLPOSTDT) = 0), JRNENTRY int NOT NULL, SEQNUMBR int NOT NULL, ITEMDESC char(101) NOT NULL, ITMGEDSC char(11) NOT NULL, DOCNUMBR char(21) NOT NULL, DOCTYPE smallint NOT NULL, TRXSORCE char(13) NOT NULL, TRXREFERENCE smallint NOT NULL, HSTMODUL char(3) NOT NULL, TRXQTY numeric(19,5) NOT NULL, VARIANCEQTY numeric(19,5) NOT NULL, QTYTYPE smallint NOT NULL, UNITCOST numeric(19,5) NOT NULL, EXTDCOST numeric(19,5) NOT NULL, CRDTAMNT numeric(19,5) NOT NULL, DEBITAMT numeric(19,5) NOT NULL, IsLandedCostTrx tinyint NOT NULL, IsVarianceTrx tinyint NOT NULL, IsOverrideReceipt tinyint NOT NULL, VCTNMTHD smallint NOT NULL, ASOFDATE datetime NOT NULL CHECK (DATEPART(hour,ASOFDATE) = 0 and DATEPART(minute,ASOFDATE) = 0 and DATEPART(second,ASOFDATE) = 0 and DATEPART(millisecond,ASOFDATE) = 0), STR1 char(1) NOT NULL, DECPLQTY smallint NOT NULL, DECPLCUR smallint NOT NULL, ACTINDX int NOT NULL, QTYONHND int NOT NULL, DEX_ROW_ID int identity NOT NULL)'
exec (@acctsegt)
exec ivCreateHistoricalIVTrialBalance
@temptable,
@I_nSortBy,
@I_nReceiptOptions,
@I_sStartItemNumber,
@I_sEndItemNumber,
@I_sStartAccountNumber,
@I_sEndAccountNumber,
@I_sStartLocationCode,
@I_sEndLocationCode,
@I_nStartQTYType,
@I_nEndQTYType,
@I_dtStart,
@I_dtEnd,
@sStartClass,
@sEndClass,
@sStartGenericDesc,
@sEndGenericDesc,
@I_fUseGLPostDate,
@I_fIncludeZeroQtyItems,
1,
@O_SQL_Error_State
set @acctsegt = 'select a.*, b.ACTNUMST from '
+ rtrim(@temptable)
+ ' a, GL00105 b where a.ACTINDX = b.ACTINDX'
exec (@acctsegt)
GO
Have any questions about fixing this glitch or other questions about Dynamics GP? Please contact us at any time!
This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.