I have questions about few of the GLD tables:

a. What is the sequence in which these tables get updated?
- tfgld102
- tfgld106
- tfgld410
- tfgld416
- tfgld417
- tfgld418
(For example, say for a Purchase Receipt or a Production Issue)

b. What is the relationships between these tables? For example, why are there two records in tfgld418 for every record in tfgld410, the only difference being the value of the field tfgld418.line?

c. What is a good 'merge' for tfgld418' with 'tfgld410' and 'tfgld417'? The idea is to get both sides of the double-entry (the Ledger Accounts) visible together.

Thanks in advance.

Hi,

The tfgld410, 416, 417, and 418 get updated first and then when you run post integrations transaction the tfgld102 get created and the tfgld106 when you finalize.

I'm not sure what you mean about merging the tables.

Thanks! That confirms at least that I chose my starting point correctly (I am manufacturing 'oriented' not 'finance', so it took a while to figue things out).


The gentleman in accounts wants to know both sides of a group of ledger account transactions (debit and credit) in the form of a report. So I am trying to find an efficient 'join' between the tables that will wade through 33 million records in most of these table in some reasonable time! What I have done so far is use tfgld410 as the starting point, constrain by Company/ Transaction Origin and ledger account number and for any record found, determine if the record exists in tfgld418. If yes then I do a primary-key indexed read of tfgld417 to get the 'other side' of the transaction.

I think I am almost there, but I have to evaluate performance. Unfortunately, there is no index on ledger account in tfgld410. And the DBA cannot schedule adding indexes and consequent re-organization till Christmas.

The joys of gigabyte databases!