General Ledger and DTA

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.


A/P Distribution to GL - when using Intercompany
Generate a GL running sum for a specific year and account
GL and DTA
GL Template Query - Start here
Move GL recurring batches from TEST to LIVE
taGetNextJournalEntry

... and you 'll find more on the SQL (Dynamics) Menu

IF OBJECT_ID (N'dbo.f_Dyn_GLExtract') IS NOT NULL

DROP FUNCTION dbo.f_Dyn_GLExtract

GO

CREATE FUNCTION dbo.f_Dyn_GLExtract(@GroupID varchar(15))

-- SELECT JRNENTRY FROM f_Dyn_GLExtract('cc')

RETURNS @gl TABLE (JRNENTRY int, SEQNUMBR int, DEX_ROW_ID int, DTARef varchar(25), ACTINDX int, ACCT varchar(4),actnumst varchar(129),

codeid varchar(25), LTD_AMT numeric(19,2), PTD_AMT numeric(19,2), OPENYEAR int, PERIODID int, GLAMT numeric(19,5), linetype varchar(3)

)

AS

begin

declare @dtDate datetime

declare @periodid int

declare @YEAR1 int

declare @currentPeriodDT as datetime

declare @PeriodDT as datetime

--initialize

set @dtDate = GETDATE()

--get the current period

select @currentPeriodDT = max(s.PERIODDT)

from SY40100 s

where SERIES = 2

and ODESCTN = 'General Entry'

and PERIODID <> 0

and PERIODDT < @dtDate

--get the period before the current period

select @PeriodDT = MAX(PeriodDT)

from SY40100

where SERIES = 2

and ODESCTN = 'General Entry'

and PERIODID <> 0

and PERIODDT < @currentPeriodDT

--get the data from the period before the current period

select @periodid = Periodid, @YEAR1 = year1

from SY40100

where PERIODDT = @perioddt;

/* GL TRX */

insert into

@gl (JRNENTRY, SEQNUMBR ,DEX_ROW_ID , DTARef ,  ACTINDX,  ACCT,actnumst,    codeid,LTD_AMT, PTD_AMT , OPENYEAR , PERIODID , GLAMT, linetype)

select gt.JRNENTRY,

gt.SEQNUMBR,

gt.dex_row_id,

dtaCC.dtaref ,

gt.actindx,

left(g.ACTNUMBR_1,4) as ACCT,

g.ACTNUMST,

dtaCC.codeid ,

convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT,

convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT,

gt.OPENYEAR ,

gt.PERIODID ,

gt.debitamt - gt.crdtamnt as GLAMT,

'GL'

from GL20000 gt

join GL00105 g on gt.ACTINDX = g.ACTINDX

left join DTA10200 dtaCC on dtaCC.dtaref = dbo.f_GLDTAJoin(gt.DTA_Index, gt.ORTRXSRC) and dtaCC.SEQNUMBR = gt.SEQNUMBR

and dtaCC.groupid = @GroupID

where gt.PERIODID <= @periodid

and gt.SOURCDOC in ('gj')

and gt.OPENYEAR = @YEAR1

union all

/* SOP */

select gt.JRNENTRY,

gt.SEQNUMBR,

gt.dex_row_id,

dtaCC.dtaref as DTARefTP,

gt.actindx,

left(g.ACTNUMBR_1,4) as ACCT,

g.ACTNUMST,

dtaCC.codeid ,

convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT,

convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT,

gt.OPENYEAR ,

gt.PERIODID ,

gt.debitamt - gt.crdtamnt as GLAMT,

'SOP'

from GL20000 gt

join gl00105 g on gt.ACTINDX = g.ACTINDX

left join DTA10200 dtaCC on dtaCC.DOCNUMBR = gt.ORDOCNUM and dtaCC.RMDTYPAL = gt.ORTRXTYP and dtaCC.SEQNUMBR = gt.OrigSeqNum

and dtaCC.groupid = @GroupID

where gt.PERIODID <= @periodid

and SOURCDOC in ('sj')

and gt.OPENYEAR = @YEAR1

union all

/* Payables */

select gt.JRNENTRY,

gt.SEQNUMBR,

gt.dex_row_id,

dtaCC.dtaref as DTARefTP,

gt.actindx,

left(g.ACTNUMBR_1,4) as ACCT,

g.ACTNUMST,

dtaCC.codeid ,

convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT,

convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT,

gt.OPENYEAR ,

gt.PERIODID ,

gt.debitamt - gt.crdtamnt as GLAMT,

'PM'

from GL20000 gt

join gl00105 g on gt.ACTINDX = g.ACTINDX

left join DTA10200 dtaCC on dtaCC.DOCNUMBR = gt.ORCTRNUM and dtaCC.SEQNUMBR = gt.OrigSeqNum and gt.ORTRXTYP <> 6

and gt.ACTINDX = dtaCC.ACTINDX and dtaCC.groupid = @GroupID

where gt.PERIODID <= @periodid

and SOURCDOC in ('PMCHK', 'PMPAY','PMTRX','PMVPY')

and gt.OPENYEAR = @YEAR1

union all

/* POP */

select gt.JRNENTRY,

gt.SEQNUMBR,

gt.dex_row_id,

dtaCC.dtaref as DTARefTP,

gt.actindx,

left(g.ACTNUMBR_1,4) as ACCT,

g.ACTNUMST,

dtaCC.codeid ,

convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT,

convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT,

gt.OPENYEAR ,

gt.PERIODID ,

gt.debitamt - gt.crdtamnt as GLAMT,

'POP'

from GL20000 gt

join gl00105 g on gt.ACTINDX = g.ACTINDX

left join DTA10200 dtaCC on dtaCC.DOCNUMBR = gt.ORCTRNUM and dtaCC.SEQNUMBR = gt.OrigSeqNum

and dtaCC.groupid = @GroupID

where gt.PERIODID <= @periodid

and SOURCDOC in ('POIVC', 'RECVG')

and gt.OPENYEAR = @YEAR1

union all

/* all the rest */

select gt.JRNENTRY,

gt.SEQNUMBR,

gt.dex_row_id,

null as DTARefTP,

gt.actindx,

left(g.ACTNUMBR_1,4) as ACCT,

g.ACTNUMST,

null as codeid ,

convert(numeric(19,2),gt.debitamt - gt.crdtamnt) as LTD_AMT,

convert(numeric(19,2),case when gt.PERIODID = @periodid then gt.debitamt - gt.crdtamnt else 0 end)as PTD_AMT,

gt.OPENYEAR ,

gt.PERIODID ,

gt.debitamt - gt.crdtamnt as GLAMT,

'XX'

from GL20000 gt

join gl00105 g on gt.ACTINDX = g.ACTINDX

where gt.PERIODID <= @periodid

--these are the transaction types that are explicitly covered above

and Not gt.SOURCDOC in ('pmtrx','GJ','SJ','PMCHK', 'PMPAY','PMTRX','PMVPY','POIVC', 'RECVG')

and gt.OPENYEAR = @YEAR1

RETURN

end

GO

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.

Links
Home
Knowledge Areas
Sitemap

Contact

Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences

General Ledger and DTA

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu