Financial Reporter - equivalent SQL statement of FRAMTA("BALP")

Posted By All lades over 4 years ago

Hi,

Does someone written and willing to share a SQL statement that is an equivalent of FRAMTA("BALP") from the Financial Reporter?

I am trying to figure out how our GL Inventory account is calculated.

I read one of the discussions to run trial balance and compare Inventory account with summing the TRANSCOST from ICIVAL table.

I ran a trial balance and the result says 214,419.95. However, when I sum up the TRANSCOST field the result is 253,392.91.

FRAMTA("BALP") from the Financial Reporter shows the same amount of 214,419.95 as the trial balance.

When I added the TRANSAMT of the GL Inventory account it gave me 422,521.34. (SELECT SUM(TRANSMT) FROM GLJED WHERE ACCTID = 13000).

My head can't handle how the trial balance amount and the FRAMTA("BALP") balances are calculated.

Your help is highly appreciated.

0 Django over 4 years ago

FR reports off of the GLAFS table. Summing transactions from GLJED isn't ideal because it can also include error batches. The Sum of GLPOST is a better choice and should also be the value stored in GLAFS (keeping foriegn keys in mind). If GLPOST <> GLAFS then you have a data integrity issue.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 All lades over 4 years ago in reply to Django

Thanks Django.

My numbers do not match. Do not know what I am missing. I noticed also that GLPOST includes different COMPANYID. I noticed that there are different companies listed - which I think is a shared table.

Still working on making the numbers match though.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Django over 4 years ago in reply to All lades

Sage 300 doesn't have shared tables. You should run the data integrity check on your GL just to make sure that your GL data is good.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 All lades over 4 years ago in reply to Django

really?

this is my SQL statement:

USE GLLDAT
GO

SELECT COMPANYID
FROM GLPOST
GROUP BY COMPANYID

and the result is:

COMPANYID
<BLANK>
GLCDAT
GLLDAT
PLADAT
ZZZDAT

Our Sage 300 uses GLLDAT. Could this have been a result from upgrading from version of Sage 300 to another?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Django over 4 years ago in reply to All lades

Sorry - was thinking audtorg. You have intercompany transactions there.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 All lades over 4 years ago in reply to Django

Thanks for the info.

However, we only have 1 company. As far as I remember, before we upgraded to Sage 300 2016, the previous version used GLCDAT database... the PLADAT is a play or a dummy database wherein i use to enter some dummy info for training purposes... and lastly the ZZZDAT is probably from a very old version.

I will investigate on this. I'm not sure why PLADAT is showing wherein fact it shouldn't be.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 300

Financial Reporter - equivalent SQL statement of FRAMTA("BALP")

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING