How to locate and resolve missing GL Transactions
Please refer to the below ticket for Terra:
WESS-4047
When creating any type of voucher whether it is a payment in AP invoice, or Journal entry the voucher created has GL Distributions. Once a voucher is approved and goes through posting, the posting process creates GL Transactions.
Numerous things can cause these GL Transactions to be missing. Any kind of interruption, for example: If a flush jobs is done during the posting process, it will keep the GL transactions from being created.
📘 Instructions
Once We know that GL Transactions are missing and we need to create them. Queries are run to find those, be sure to check all four of the below queries when a client states they are missing GL Transactions. This ensures that we can fix it all at once.
If no results come back after running these, it means that there are no JE’s or AP Payments, Vouchers or Invoices that are missing. In this scenario, AP Payments are missing GL Transactions.
Find Journal Entries missing GLT (3).sql
2. Find AP Payments missing GLT (3).sql
3. Find AP Vouchers missing GLT (2).sql
4. Find AR Invoices missing GLT (3).sql
Once these have been run and missing GL Transactions have been identified, a Rails command will need to be run. The Rails command creates the GL transactions for AP Payments that are missing.
Rails to Create AP GL_Ts (3).txt
If Terra had been missing GL Transactions for other voucher types, the following Rails commands could be used to generate the GL Transactions. As you can see, there is a different command for each voucher type.
Rails to Create AP Payment GL_T .txt
Rails to Create AR GL_Ts (4).txt
Rails to Create JE GL_Ts.txt
Rails to Create AP Invoices GL_Ts.txt
Next, we need to recalculate the account balances. To do this, run the below query:
Run Find out of Balance query first.
When this pulls back results it tells us that there are accounts that do not balance. So we need to recalculate the balances, by running the below.
Run the Terra_GL_balance queries, 1, 2 and 3:
1_Terra_gl_balance fix for out of balance accounts (1).sql
2_Terra_gl_balance fix for out of balance NON Close to RE PTD (1).sql
3_Terra_gl_balance fix for out of balance close to RE PTD (1).sql
Lastly run the Find out of balance again to confirm, that GL Balances are no longer out of balance:
Find out of balance gl_balances (5).sql
Article Missing GL Transactions.docx Rails to Create AP Payment GL_T .txt Rails to Create AP Invoices GL_Ts.txt Rails to Create JE GL_Ts.txt Rails to Create AR GL_Ts (4).txt 1_Terra_gl_balance fix for out of balance accounts (1).sql 2_Terra_gl_balance fix for out of balance NON Close to RE PTD (1).sql 3_Terra_gl_balance fix for out of balance close to RE PTD (1).sql Find Journal Entries missing GLT (3).sql Find AP Payments missing GLT (3).sql Find AP Vouchers missing GLT (2).sql Find AR Invoices missing GLT (3).sql Rails to Create AP GL_Ts (3).txt Find out of balance gl_balances (5).sql
Highlight important information in a panel like this one. To edit this panel's color or style, select one of the options in the menu.