GL20000 (and GL30000) - PERIODID field - whats it for?

(0) Share

Report

Posted on by Community Member

Hi All,

What is the purpose of the PERIODID field on the above two tables - its value is always '0'? It doesn't seem to be a reference to the GL Period that the transaction was posted into.

Next question - If I wanted to select * from GL20000 - and show a period ID based on the Financial Period Setup - anyone got a SQL query that would return this data? I presume you wold use the Transaction Date and compare it to the date ranges in the Period Set up table?

Best regards,

Ian.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Andrew King on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

Richard,

I've got the same situation with a Client on GP10. Their PeriodID is always 0 in GL2000, but I've got other Clients on GP10 and GP2010R2 where the Period ID is always populated and is based on the Fiscal Period ID of the current year. Is there a setting somewhere in Setup that controls this?

Thanks.

Was this reply helpful? Yes No
Tim Foster 8,515 on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

Ian,

Try this:

SELECT SY40100.PERIODID

FROM SY40101 inner join SY40100 ON SY40101.YEAR1=SY40100.YEAR1

WHERE SERIES=0 AND FORIGIN=1 AND PERIODID<>0 AND GETDATE() BETWEEN SY40100.PERIODDT AND SY40100.PERDENDT

Sub TRXDATE for the getdate().

Tim

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

Understood. But the SY40100 table stores the Period ID's. These are known uniquely by their Year and PeriodID.

The Year is stored in the GL20000 table - but not the PeriodID.

I'd say you are correct - there has to be some logical reason why storing the Period ID in GL20000 was changed...and of course the Field wasn't deleted. Probably because at some stage the ability to re-build the financial calendar was added...and the reconcile utility would have to look at GL20000 (and GL30000) and not just the summary tables as it does now.

Anyway.

Still have to figure out a SQL query that will get me the Period ID from SY40100 based on the TRXDATE in GL20000!!!

Best regards,

Ian.

Was this reply helpful? Yes No
Suggested answer

Richard Whaley 25,195 on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

Should the number be 9 as in the ninth month of the fiscal year or 21 as the ninth month of the second year? And what do you do when a short year is used to change the fiscal year?

For these reasons, Period IDs were abandoned. (But you can NEVER delete a field)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

Hi Richard,

Understood - but any idea why GP wouldn't be recording the Period ID in the PERIODID field?

A transaction posted to Financial Period 9 - still has a PERIODID value of '0' in this table.

Just wondering if anyone knows the purpose of this field - if its value is always '0'.

Best regards,

Ian.

Was this reply helpful? Yes No
Richard Whaley 25,195 on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

today, yes, in years past????? I have seen several accounting systems that recorded the period id in the transactions rather than the posting dates. Most of these are gone now or changed the way they store the accounting period information.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

Hi Richard,

No - the transaction date is on the table - but there is a field called PERIODID - and this is always set to '0'.

From its description, you would guess (I know!!!) that it stores the GL Period ID of the period that the transaction was posted into, but its always '0' regardless of the transaction date.

Ian.

Was this reply helpful? Yes No
Richard Whaley 25,195 on at

Like (0)

Report

Re: GL20000 (and GL30000) - PERIODID field - whats it for?

I am guessing that this is a hold over from the days when disk space was expensive and rather than put the posting date in the transaction the accounting period (shorter field) was recorded. I know of no use for this now.

Was this reply helpful? Yes No