Can anybody tell me which field the CorrAccUID field links to, and how, in the Erp.TranGLC table
The data dictionary defines it as this:
When a posting rule creates a pair of journal details balancing each other, each detail in the pair should reference the other one. This allows to show correspondence of accounts in reports. Support of reports that allow this being visible is not in the scope of this change.
From best that I can tell, using the CorrAccUID and the ABTUID in conjuction will give you the other side of the balancing entry.
So if CorrAccUID = XXXX, and ABTUID = <<some guid>> then if you look for the ABTUID = <<some guid>> and CorrAccUID <> XXXX then you’d get the balancing entry.
2 Likes
Doug,
Thanks for this, it took me a while to get my head around it.
So, I thought I would include the contents of my resulting CTE query to clarify it for anybody else.
with InitGroup
(
Company, ABTUID, CorrAccUID
)
as
(
select Company, ABTUID, CorrAccUID
from Erp.TranGLC with(nolock)
where TranDate>=‘2022-11-01’
and SegValue1 like ‘0705’
)
,
BalGroup
(
Company, ABTUID, CorrAccUID
)
as
(
select T.Company, T.ABTUID, T.CorrAccUID
from Erp.TranGLC as T with(nolock)
inner join InitGroup as I on T.Company=I.Company and T.ABTUID=I.ABTUID
and T.CorrAccUID!=I.CorrAccUID
)
,
CombGroup
(
Company, ABTUID, CorrAccUID
)
as
(
select Company, ABTUID, CorrAccUID
from InitGroup
union
select Company, ABTUID, CorrAccUID
from BalGroup
)
select C.Company, C.ABTUID, C.CorrAccUID,
GLAccount, SegValue1, SegValue2, SegValue3, BookDebitAmount, BookCreditAmount
from CombGroup as C
inner join Erp.TranGLC as T with(nolock) on C.Company=T.Company and C.ABTUID=T.ABTUID
and C.CorrAccUID=T.CorrAccUID
order by ABTUID, CorrAccUID
1 Like