We have a query that requires the TranGLC table. This table was generating duplicated records. for the connected InvcDtl table (Left join from InvcDtl InvoiceNum & InvoiceLine to TranGLC). Reviewing the records there are different values in the columns generated causing the issue. There is not really enough info in the Data Dictionary to decipher what is going on. However, I found that the records I wanted to keep all had the same RuleID = 1904. I’m filtering based on this, but would really like to understand why.
Thank you in advance for any help in understanding this complicated little beast.
There should be at least 2, records for every transaction. One (or more) with the Debit Amount(s) and the GL-Acct(s) to debit, and one (or more) with the Credit Amount(s) and the GL-Acct(s) to credit.
The above is incorrect. TranGLC records are only created when the GLC’s are applied. Also, I don’t think that zero dollar transactions are recorded either
Note that like PartTran, records are never deleted. So if TranGLC records were created from a PO receipt, “unreceiving” it would make more records - but with opposite CR’s and DB’s.
Calvin, thank you for trying to decipher my situation. I am filtering on our sales chart (SegValue1 = 1101). It is only happening on a few records (27 of 1000+). I’ve uploaded a sample of 1 invoice line that is being duped.
Also, I noticed that your data has the RecordType column, and one is an R and one is an A.
The data dictionary says:
Indicates if this record contains account data only or reference data such as journal number, amounts, etc. Valid values are:
A - account data only
R - reference data
In this case the TranGLC of type A as calvin mentioned is for account information only, this is shown in a few Forms so you can specify GL Accounts directly using the EpiGLAccountEditor, this one is on the Line/Detail tab of AR Invoice Entry. You can see UserCanModify is true for the type A record.
This means this will be used in the posting rules instead of the usual rules(if you didn’t enter any account it will execute function “Determine Revenue Account for Line Type Part…”)
Once the actual posting engine processes the Invoice is will create the TranGLC record of the transaction, so it will be of type R and will contain amounts, this is the information that is used if the invoice is later referenced by other posting processes like paying the invoice or cancelling the invoice.
This is pretty helpful. The RecordType = “R” seems to be the key here as this is the record with the pertinent information. I had noticed that all the “erroneous data” had a Rule UID = “1917”. Out of curiosity, is there any way to track what those rule UIDs reference?
Erp.ACTType - Stores the gl transactions like AR Invoice and AP Invoice
Erp.ACTRevision - Stores the revisions, you could have Blocked/Draft and 1 Active Revision per ACTType
Erp.BookingRule - Stores the actual posting rules per revision
RuleUID must match the actual booking rule that generated that TranGLC record, for example in my previous post the rule is named “Post Extended Price Amount(Regular Invoice, Part)”
The Booking Rule table had the info I was looking for:
1904: This rule posts extended price amount from Invoice Line of type PART to a revenue account. The rule works only for Regular Invoices.
1917: Pre-Posting Rule to Calculate Default Account for Misc Invoice Line
Now, I see the the dup records were generated from 1917. This also tells me that 1904 may not always be a good item to filter on due to its “Regular Invoice” restriction. This reaffirms the the filter set on RecordType. Thanks guys for your help.
