This article will discuss the connection between GL and DTA, and tell how to relate the GL20000 table to the DTA tables in order to retrieve data.
DTA sub-distributions may be applied directly to a General Ledger distribution or may be applied in a sub ledger and will be transferred to GL when posted.
Currently, Sales Order Processing (SOP), Purchase Order Processing (POP) and Payables Management (PM) are supported.
DTA allows additional information to be stored about GL and sub ledger account entries. Entries can be grouped into many groups. Each group can be attached to the same distribution, and the sub-distributions are not required to add up to 100%. The entire sub distribution is not required, that needs to be accounted for as well.
In other words, a GL distribution for $100 might be
- Linked to a group called Profit Center (PC) and have sub distributions for $50
- Linked to a group called Cost Center (CC) and have sub distributions for $20 and $80
The code samples that follow take this into account. Generally we separate the groups and report on only one of them. We recommend creating business rules that require 100% distributions and then creating alerts of some sort that will show management when this rule is not followed. That way the code can assume 100% distributions.
The code below is written as a function, it returns a table. This method allows the function to be called from multiple reports and ensures consistent data.
Note that the script uses several different methods to join in the DTA10200 table. Trial and error has shown that the link is different in all the sub ledgers.
The GL join uses another function called dbo.f_GLDTAJoin to concatenate two fields so that they meet the format of the DTA10200.DTAREF field.
The different sections of the query look at the GL.SOURCDOC field to determine the sub ledger that the data originated in. There is a final query that picks up all the remaining lines that do not have a DTA sub distribution and includes them also.
This script will create the SQL function that is used to get the DTA distributions related to GL transactions.