Dynamics Community Forum Thread Details

Hm. That's weird. I think that table is used to store multicurrency information, but I'm not sure what the friendly name is to say whether or not you can rebuild it through the UI. That said, you could try recreating the table in a test company via SQL and seeing if you can reverse the historical year there. To do so, take the following steps:

1) Create a test company with a copy of your production data (instructions here: https://support.microsoft.com/en-us/kb/872370)

2) Run the the following script against the test company database to recreate the GL30001 table (scroll all the way down; there's a third step):

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[GL30001](
   [TranslationCurrencyID] [char](15) NOT NULL,
   [HSTYEAR] [smallint] NOT NULL,
   [JRNENTRY] [int] NOT NULL,
   [SOURCDOC] [char](11) NOT NULL,
   [REFRENCE] [char](31) NOT NULL,
   [TRXDATE] [datetime] NOT NULL,
   [TRXSORCE] [char](13) NOT NULL,
   [ACTINDX] [int] NOT NULL,
   [SEQNUMBR] [int] NOT NULL,
   [PERIODID] [smallint] NOT NULL,
   [Ledger_ID] [smallint] NOT NULL,
   [CURNCYID] [char](15) NOT NULL,
   [Original_Exchange_Rate] [numeric](19, 7) NOT NULL,
   [ORGNTSRC] [char](15) NOT NULL,
   [CRDTAMNT] [numeric](19, 5) NOT NULL,
   [DEBITAMT] [numeric](19, 5) NOT NULL,
   [ORCRDAMT] [numeric](19, 5) NOT NULL,
   [ORDBTAMT] [numeric](19, 5) NOT NULL,
   [TranslationCreditAmount] [numeric](19, 5) NOT NULL,
   [TranslationDebitAmount] [numeric](19, 5) NOT NULL,
   [CURRNIDX] [smallint] NOT NULL,
   [DECPLCUR] [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,
   [DENXRATE] [numeric](19, 7) NOT NULL,
   [MCTRXSTT] [smallint] NOT NULL,
   [CurrencyTranslationType] [smallint] NOT NULL,
   [Identity_Column] [int] NOT NULL,
   [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKGL30001] PRIMARY KEY NONCLUSTERED
(
   [TranslationCurrencyID] ASC,
   [HSTYEAR] ASC,
   [ACTINDX] ASC,
   [Ledger_ID] ASC,
   [CURNCYID] ASC,
   [JRNENTRY] ASC,
   [TRXDATE] ASC,
   [SEQNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[GL30001] WITH CHECK ADD CHECK ((datepart(hour,[EXCHDATE])=(0) AND datepart(minute,[EXCHDATE])=(0) AND datepart(second,[EXCHDATE])=(0) AND datepart(millisecond,[EXCHDATE])=(0)))
GO

ALTER TABLE [dbo].[GL30001] WITH CHECK ADD CHECK ((datepart(day,[TIME1])=(1) AND datepart(month,[TIME1])=(1) AND datepart(year,[TIME1])=(1900)))
GO

ALTER TABLE [dbo].[GL30001] WITH CHECK ADD CHECK ((datepart(hour,[TRXDATE])=(0) AND datepart(minute,[TRXDATE])=(0) AND datepart(second,[TRXDATE])=(0) AND datepart(millisecond,[TRXDATE])=(0)))
GO

3) Run the grant.sql script:

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
	where (type = 'U' or type = 'V') and uid = 1

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
	EXEC (@cStatement)
	FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects 
	where type = 'P'  

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
	EXEC (@cStatement)
	FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor

Error when using Reverse Historical Year

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates