CODEWITHSUNDEEP
axaptamicrosoft-dynamicsdynamics-ax-2012
Josh Jay
Josh Jay

Reputation: 1250

Relationship between GeneralJournalAccountEntry and LedgerJournalTrans_Project

We are attempting to relate GL transactions GeneralJournalAccountEntry with their appropriate Project Category Id LedgerJournalTrans_Project, but are having a very difficult time relating the two tables.

We can get there by this route:

But the individual leg of the transaction gets lost in the Many to 1 and 1 to Many relationships. We understand the tables are from different "modules", but are hoping there is some way to connect the two without depending on something messy like transaction amount.

Is this possible, or is there another way to accomplish our goal?

Upvotes: 1

Views: 16690

Answers (2)

Joshua Perry
Joshua Perry

Reputation: 93

I'm not certain yet that I have this completely figured out, but I think this can be used to create a similar link. I'm trying to link the generaljournalaccountentry line items to the inventjounrnaltrans line items. This should provide the link between the two tables based on the voucher and line item number and give me the itemid. I would think there would necessarily be a similar link to the project because of the ability to navigate that way in the front end application. It might also be helpful to trace the SQL query when performing the action in the front end.

SELECT itp.[PARTITION]
     , itp.[DATAAREAID]
     , itp.[TRANSDATE]
     , itp.[VOUCHER]
     , itp.[ITEMID]
     , itojt.[INVENTJOURNALLINENUM]
     , MAX(ito.[REFERENCEID]) AS [REFERENCEID]
FROM [dbo].[INVENTTRANSPOSTING] itp WITH(NOLOCK)
    INNER JOIN [dbo].[INVENTTRANSORIGIN] ito WITH(NOLOCK)
        ON ito.[RECID] = itp.[INVENTTRANSORIGIN]
    INNER JOIN [dbo].[INVENTTRANSORIGINJOURNALTRANS] itojt WITH(NOLOCK)
        ON itojt.[INVENTTRANSORIGIN] = ito.[RECID]
WHERE (itp.[INVENTTRANSPOSTINGTYPE] = 1 OR itp.[INVENTTRANSPOSTINGTYPE] = 0)
      AND itp.[ISPOSTED] = 1
GROUP BY itp.[PARTITION]
       , itp.[DATAAREAID]
       , itp.[TRANSDATE]
       , itp.[VOUCHER]
       , itp.[ITEMID]
       , itojt.[INVENTJOURNALLINENUM]

Upvotes: 0

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18061

There is not a one-to-one relationship.

The closest thing would be to use Voucher and TransDate on LedgerJournalTrans to fetch ledger transactions.

See this post.

There is a white paper describing the AX 2012 ledger posting framework.

Upvotes: 2

Related Questions