I submitted a case to Microsoft Support and received this reply - We actually had an issue written up in GP 2013 where the accounts would not display in the report if there was not any other activity in the year that you are running the report for other than the beginning balance entry. This was written up as PR 67943, and was actually corrected in Service Pack 2 of GP 2013.
Interestingly we are running SP4 and the issue was not resolved in that version.
Below is the Stored Procedure fix sent.
if exists (select * from sysobjects where id = object_id(N'[dbo].[seeglPrintSRSTrialBalance]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[seeglPrintSRSTrialBalance]
GO
/*
**********************************************************************************************************
* (c) 2006 Microsoft Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: seeglPrintSRSTrialBalance
*
* SANSCRIPT NAME: NA
*
* PARAMETERS:
*
*
*
* DESCRIPTION: Runs through the detail GL transactions for purposes of calculating the GL Detailed Trail balance
*
*
* TABLES:
* Table Name Access
* ========== ======
* GL20000 Read
* GL30000 Read
*
*
* PROCEDURES CALLED: glPrintTrialBalanceReport
*
*
* DATABASE: Company
*
*
* RETURN VALUE:
*
*
* REVISION HISTORY:
*
* Date Who Comments
* ---------- -------- -------------------------------------------------
* 01/23/2007 Aaron Donat Added additional logic to account for and cover the different account framework in one of the temp tables that is normally passed
* created and passed from the GP client for the data.
* 01/23/2007 Aaron Donat Added logic to cover state where end user selects a date range that isn't within the open Year selected.
* 01/12/2010 Kurt Larson Removed the line which sets fiscal year to a calendar year, to fix bug 48647. The exec'd glPrintTrialBalanceReport and the tables
* it uses already take into account date ranges and fiscal years that don't match up.
* 01/11/2011 Tim Satrom 57433 - Changed SubTotalBy for call to GLTrialBalance proc to be 3 and added date condition to the final select statement
* 02/14/2013 Tim Satrom 67943 - Added s.TRXDATE is null to bring back balances without trx in current year also changed KeepingSummary value to 1
* 03/08/2013 Nate Haaland 67943 - Added @I_dStartingPeriodDate to determine the period starting date and pass it into the glPrintTrialBalanceReport.
* This prevented the beginning balances from being doubled.
*
**********************************************************************************************************
*
**********************************************************************************************************
*/
create procedure [dbo].[seeglPrintSRSTrialBalance]
@I_tInactiveAccounts tinyint = NULL, /* Param 01: "1" selects Inavtive Accounts "0" eliminates Inactive Accounts*/
@I_tIncludeUnitAccounts tinyint = NULL, /* Param 02: "1" selects Unit Accounts "0" eliminates Unit Accounts*/
@I_tIncludeZeroBalance tinyint = NULL, /* Param 03: "1" selects Zero Amount Accounts "0" eliminates Zero Amount Accounts*/
@I_cStartingAccountNumber varchar(128) = NULL, /* Param 04: Uses the "Account Number String" field*/
@I_cEndingAccountNumber varchar(128) = NULL, /* Param 05: Uses the "Account Number String" field*/
@I_dStartingDate datetime = NULL, /* Param 06: Report Start Date*/
@I_dEndingDate datetime = NULL, /* Param 07: Report End Date*/
@I_iYear int = NULL, /* Param 08: Fiscal Year*/
@I_tHistoryYear tinyint = NULL, /* Param 09: "0" selects Open Year "1" selects History Year*/
@I_iRangeByAccount int = NULL,
@I_iPostingAccounts int = NULL
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),
@useReportingLedgers binary(4),
@cGeneral_Entry char(55),
@O_SQL_Error_State int,
@FINANCIAL int,
@iStartingPeriod int,
@iEndingPeriod int,
@I_tKeepingSummary tinyint,
@I_dStartingPeriodDate datetime
/* 67943 start (set @I_tKeepingSummary value) */
if exists(select 1 from GL10110 where YEAR1 = @I_iYear UNION select 1 from GL10111 where YEAR1 = @I_iYear)
begin
set @I_tKeepingSummary = (select KPGACHST from GL40000)
end
else
begin
set @I_tKeepingSummary = 0
end
/* 67943 end */
select @numsegs = (select MXNUMSEG from DYNAMICS..SY003001)
select @cnt = 1
select @acctseg = ''
select @acctsegd = ''
select @guid = NEWID()
select @useReportingLedgers = power(2,24)
select @temptable = @guid
set @temptable = '##' + substring(@temptable,1,8)
while @cnt <= @numsegs
Begin
select @acctseg = ' [ACTNUMBR_' + ltrim(STR(@cnt)) + '] [char] (9) NOT NULL, ' + char(13)
set @acctsegl = rtrim(@acctseg)
select @cnt = @cnt + 1
set @acctsegd = @acctsegd + @acctsegl
End
set @acctsegt =
'CREATE TABLE ' + @temptable + '(
[ACTINDX] [int] NOT NULL, ' + char(13) +
ltrim(rtrim(@acctsegd)) +
'[ACTNUMST] [char](129) NOT NULL,
[Identity_Column] [int] NOT NULL,
[BGNGBAL] [numeric](19, 5) NOT NULL,
[ENDNGBAL] [numeric](19, 5) NOT NULL,
[HSTYEAR] [smallint] NOT NULL,
[STRTNGDT] [datetime] NOT NULL,
[ENDINGDT] [datetime] NOT NULL,
[TOTDEB] [numeric](19, 5) NOT NULL,
[TOTCRED] [numeric](19, 5) NOT NULL,
[PERNAME] [char](21) NOT NULL,
[PERINDX] [smallint] NOT NULL,
[DEBITAMTS] [numeric](19, 5) NOT NULL,
[CRDTAMNTS] [numeric](19, 5) NOT NULL,
[NETAMNT] [numeric](19, 5) NOT NULL,
[NMBRACTS] [int] NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[ORDBTAMTS] [numeric](19, 5) NOT NULL,
[ORCRDAMTS] [numeric](19, 5) NOT NULL,
[ORNETAMT] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL)'
exec (@acctsegt)
CREATE TABLE [dbo].[#GLTBDTemp](
[YEAR1] [smallint] NOT NULL,
[JRNENTRY] [int] NOT NULL,
[RCTRXSEQ] [numeric](19, 5) NOT NULL,
[SOURCDOC] [char](11) NOT NULL,
[REFRENCE] [char](31) NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[TRXDATE] [datetime] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[ACTINDX] [int] NOT NULL,
[POLLDTRX] [tinyint] NOT NULL,
[LASTUSER] [char](15) NOT NULL,
[LSTDTEDT] [datetime] NOT NULL,
[USWHPSTD] [char](15) NOT NULL,
[ORGNTSRC] [char](15) NOT NULL,
[ORGNATYP] [smallint] NOT NULL,
[QKOFSET] [smallint] NOT NULL,
[SERIES] [smallint] NOT NULL,
[ORTRXTYP] [smallint] NOT NULL,
[ORCTRNUM] [char](21) NOT NULL,
[ORMSTRID] [char](31) NOT NULL,
[ORMSTRNM] [char](65) NOT NULL,
[ORDOCNUM] [char](21) NOT NULL,
[ORPSTDDT] [datetime] NOT NULL,
[ORTRXSRC] [char](13) NOT NULL,
[OrigDTASeries] [smallint] NOT NULL,
[OrigSeqNum] [int] NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[DTA_GL_Status] [smallint] NOT NULL,
[DTA_Index] [numeric](19, 5) NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[RATETPID] [char](15) NOT NULL,
[EXGTBLID] [char](15) NOT NULL,
[XCHGRATE] [numeric](19, 7) NOT NULL,
[EXCHDATE] [datetime] NOT NULL,
[TIME1] [datetime] NOT NULL,
[RTCLCMTD] [smallint] NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[ICTRX] [tinyint] NOT NULL,
[ORCOMID] [char](5) NOT NULL,
[ORIGINJE] [int] NOT NULL,
[PERIODID] [smallint] NOT NULL,
[CRDTAMNTD] [numeric](19, 5) NOT NULL,
[DEBITAMTD] [numeric](19, 5) NOT NULL,
[ORCRDAMTD] [numeric](19, 5) NOT NULL,
[ORDBTAMTD] [numeric](19, 5) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[PSTGNMBR] [int] NOT NULL,
[PPSGNMBR] [int] NOT NULL,
[DENXRATE] [numeric](19, 7) NOT NULL,
[MCTRXSTT] [smallint] NOT NULL,
[CorrespondingUnit] [char](5) NOT NULL,
[PERINDX] [smallint] NOT NULL,
[PERNAME] [char](21) NOT NULL,
[CURIDKEY] [char](15) NOT NULL,
[Ledger_ID] [int] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL)
set @iStartingPeriod =
(select isnull((
select top 1 c.PERIODID from
(select top 1000 a.PERIODID ,a.PERIODDT, b.LSTFSCDY, b.NUMOFPER
from SY40100 a, SY40101 b
where a.YEAR1 = b.YEAR1
and a.FORIGIN = 1 and a.YEAR1 = @I_iYear and a.PERIODID > 0
order by a.YEAR1, a.PERIODID DESC) c
where @I_dStartingDate between c.PERIODDT and c.LSTFSCDY), 1))
set @iEndingPeriod =
(select isnull((
select top 1 c.PERIODID from
(select top 1000 a.PERIODID ,a.PERIODDT, b.LSTFSCDY, b.NUMOFPER
from SY40100 a, SY40101 b
where a.YEAR1 = b.YEAR1
and a.FORIGIN = 1 and a.YEAR1 = @I_iYear and a.PERIODID > 0
order by a.YEAR1, a.PERIODID DESC) c
where @I_dEndingDate between c.PERIODDT and c.LSTFSCDY), isnull((select NUMOFPER from SY40101 where YEAR1 = @I_iYear), 1)))
/* 67943 start (set @I_dStartingPeriodDate date) */
set @I_dStartingPeriodDate =
(select isnull((
select top 1 c.PERIODDT from
(select top 1000 a.PERIODID ,a.PERIODDT, b.LSTFSCDY, b.NUMOFPER
from SY40100 a, SY40101 b
where a.YEAR1 = b.YEAR1
and a.FORIGIN = 1 and a.YEAR1 = @I_iYear and a.PERIODID > 0
order by a.YEAR1, a.PERIODID DESC) c
where @I_dStartingDate between c.PERIODDT and c.LSTFSCDY), 1))
/* 67943 end (set @I_dStartingPeriodDate date) */
exec glPrintTrialBalanceReport
'', /*@I_cAccountListTable*/
0, /*@I_tDateRangeByPeriod DO NOT CHANGE*/
@temptable, /*'#GLTBSTemp', --@I_cTableName DO NOT CHANGE*/
'#GLTBDTemp', /*@I_cTableNameDetail DO NOT CHANGE*/
@I_tInactiveAccounts,
@I_iPostingAccounts, /*@I_iPostingAccounts MUST BE SET TO "1"*/
2, /*@I_iUnitAccounts MUST BE SET TO "2"*/
@I_tIncludeUnitAccounts,
@I_tIncludeZeroBalance,
@I_cStartingAccountNumber,
@I_cEndingAccountNumber,
@I_dStartingDate,
@I_dEndingDate,
@iStartingPeriod, /*@I_iStartingPeriod DO NOT CHANGE*/
@iEndingPeriod, /*@I_iEndingPeriod int DO NOT CHANGE*/
@I_dStartingPeriodDate, -- NDH 67943 '1900-01-01', /*@I_dStartingPeriodDate DO NOT CHANGE*/
@I_tKeepingSummary, /*@I_tKeepingSummary MUST BE SET TO "0"*/
@I_iYear,
'1900-01-01', /*@I_dFirstDayOfYear Does not seem to be used int the SQL code at all*/
@I_tHistoryYear,
1, /*@I_iReportType MUST BE SET TO "1" TO RETURN A DETAIL SET*/
3, /*@I_tSubtotalBy MUST BE SET TO "2"*/
0, /*@I_tMCRegistered*/
0, /*@I_sPrintCurrencyIn*/
1, /*@I_nReportingExchangeRate*/
0, /*@I_sReportingRateCalcMethod*/
2, /*@I_sReportingDecimalPlaces*/
@I_iRangeByAccount,
0, /*@I_tIncludeMCInfo */
'', /*@I_cStartingCurrID */
'ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ', /*@I_cEndingCurrID */
@useReportingLedgers
/*THIS SELECT SCRIPT IS NOT TO BE CHANGED*/
if (@I_tIncludeUnitAccounts = 0 )
begin
exec ('delete from ' + @temptable + /* #GLTBSTemp */
' where ACTINDX in (select ACTINDX from GL00100 where ACCTTYPE = 2)')
end
exec DYNAMICS..smGetConstantString 'TRX_SOURCE_GL_GENERAL_ENTRY', @cGeneral_Entry output, @O_SQL_Error_State output
exec DYNAMICS..smGetConstantInt 'FINANCIAL', @FINANCIAL output, @O_SQL_Error_State output
exec ('update a set a.PERIODID = b.PERIODID from #GLTBDTemp a, SY40100 b
where a.YEAR1 = b.YEAR1 and a.TRXDATE between b.PERIODDT and b.PERDENDT
and a.YEAR1 = ' + @I_iYear + ' and b.SERIES = ' + @FINANCIAL + ' and b.ODESCTN = ''' + @cGeneral_Entry + '''
and a.PERIODID = 0')
exec ('select d.*,
isnull([s].[YEAR1], 0) as [YEAR1],
isnull([s].[JRNENTRY], 0) as [JRNENTRY],
isnull([s].[RCTRXSEQ], 0.00000) as [RCTRXSEQ],
isnull([s].[SOURCDOC], '''') as [SOURCDOC],
isnull([s].[REFRENCE], '''') as [REFRENCE],
isnull([s].[DSCRIPTN], '''') as [DSCRIPTN],
isnull([s].[TRXDATE], ''1/1/1900'') as [TRXDATE],
isnull([s].[TRXSORCE], '''') as [TRXSORCE],
isnull([s].[ACTINDX], 0) as [ACTINDX],
isnull([s].[POLLDTRX], 0) as [POLLDTRX],
isnull([s].[LASTUSER], '''') as [LASTUSER],
isnull([s].[LSTDTEDT], ''1/1/1900'') as [LSTDTEDT],
isnull([s].[ORGNTSRC], '''') as [ORGNTSRC],
isnull([s].[ORGNATYP], 0) as [ORGNATYP],
isnull([s].[QKOFSET], 0) as [QKOFSET],
isnull([s].[SERIES], 0) as [SERIES],
isnull([s].[ORTRXTYP], 0) as [ORTRXTYP],
isnull([s].[ORCTRNUM], '''') as [ORCTRNUM],
isnull([s].[ORMSTRID], '''') as [ORMSTRID],
isnull([s].[ORMSTRNM], '''') as [ORMSTRNM],
isnull([s].[ORDOCNUM], '''') as [ORDOCNUM],
isnull([s].[ORPSTDDT], ''1/1/1900'') as [ORPSTDDT],
isnull([s].[ORTRXSRC], '''') as [ORTRXSRC],
isnull([s].[OrigDTASeries], 0) as [OrigDTASeries],
isnull(s.[OrigSeqNum], 0) as [OrigSeqNum],
isnull([s].[SEQNUMBR], 0) as [SEQNUMBR],
isnull([s].[DTA_GL_Status], 0) as [DTA_GL_Status],
isnull(s.[DTA_Index], 0.00000) as [DTA_Index],
isnull([s].[NOTEINDX], 0.00000) as [NOTEINDX],
isnull([s].[ICTRX], 0) as [ICTRX],
isnull([s].[ORCOMID], '''') as [ORCOMID],
isnull([s].[ORIGINJE], 0) as [ORIGINJE],
isnull([s].[PERIODID], 0) as [PERIODID],
isnull([s].[CRDTAMNTD], 0.00000) as [CRDTAMNTD],
isnull([s].[DEBITAMTD], 0.00000) as [DEBITAMTD],
isnull([s].[DOCDATE], ''1/1/1900'') as [DOCDATE],
isnull([s].[PSTGNMBR], 0) as [PSTGNMBR],
isnull([s].[PPSGNMBR], 0) as [PPSGNMBR],
isnull([s].[CorrespondingUnit], '''') as [CorrespondingUnit],
isnull([s].[PERINDX], 0) as [PeriodIndex],
isnull([s].[PERNAME], '''') as [PeriodName],
isnull([s].[DEX_ROW_ID], 0) as [DEX_ROW_IDD],
case when [ACCTTYPE] = 1 then isnull([s].[DEBITAMTD], 0.00000) else 0 end as [DEBITAMT],
case when [ACCTTYPE] = 1 then isnull([s].[CRDTAMNTD], 0.00000) else 0 end as [CREDITAMT],
[m].*,
case when TRXDATE is null then 1 else DATEPART(month, TRXDATE) end as MonthIndex,
case when TRXDATE is null then ''Janurary'' else DateName(month, TRXDATE) end as MonthField,
case when SOURCDOC is null then ''*No Transactions for this account*'' else '''' end as ''ActZeroDesc'',
case when ACTIVE = 0 then ''*Inactive account*'' else '''' end as ''ActInactiveDesc'',
case
when [s].[DEX_ROW_ID] is null then [BGNGBAL]
else
case
when [s].[DEX_ROW_ID] = (select min([tmp].[DEX_ROW_ID])
from #GLTBDTemp tmp
where [s].[ACTINDX] = [tmp].[ACTINDX]
and [tmp].[TRXDATE] = (select min([tmp].[TRXDATE]) from #GLTBDTemp tmp where [s].[ACTINDX] = [tmp].[ACTINDX]))
then [BGNGBAL] else 0 end end as Beg_Bal,
case
when [s].[DEX_ROW_ID] is null then [ENDNGBAL]
else
case when [s].[DEX_ROW_ID] = (select min([tmp].[DEX_ROW_ID])
from #GLTBDTemp tmp
where [s].[ACTINDX] = [tmp].[ACTINDX] )
then [ENDNGBAL]
else 0
end
end as End_Bal
from ' + @temptable + ' d -- #GLTBSTemp d
left join #GLTBDTemp s on d.ACTINDX = s.ACTINDX
left join GL00100 m on d.ACTINDX = m.ACTINDX
where (m.ACTIVE = 1 or ' + @I_tInactiveAccounts + ' = 1) and
(s.TRXDATE between ''' + @I_dStartingDate + ''' and ''' + @I_dEndingDate + ''')' + 'or s.TRXDATE is null ' )
GO
GRANT EXECUTE ON dbo.seeglPrintSRSTrialBalance TO [rpt_accounting manager]
GRANT EXECUTE ON dbo.seeglPrintSRSTrialBalance TO [rpt_bookkeeper]
GRANT EXECUTE ON dbo.seeglPrintSRSTrialBalance TO [rpt_certified accountant]
GRANT EXECUTE ON dbo.seeglPrintSRSTrialBalance TO [rpt_power user]
GRANT EXECUTE ON dbo.glPrintTrialBalanceReport TO [rpt_accounting manager]
GRANT EXECUTE ON dbo.glPrintTrialBalanceReport TO [rpt_bookkeeper]
GRANT EXECUTE ON dbo.glPrintTrialBalanceReport TO [rpt_certified accountant]
GRANT EXECUTE ON dbo.glPrintTrialBalanceReport TO [rpt_power user]
GO
Share
Report
250
All responses (
Answers (
