Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Linking Project transactions to General Ledger transactions

Posted on by Microsoft Employee

We are using Dynamics AX 2012 R2 and are currently trying to reconcile numbers between the Project Module and General Ledger. Is there any way to link Project transactions (PROJTRANSPOSTING) to General Ledger transactions (GENERALJOURNALACCOUNTENTRY)? Help would be greatly appreciated, thanks!

*This post is locked for comments

  • Suggested answer
    b_radlyjames Profile Picture
    b_radlyjames 3,061 on at
    RE: Linking Project transactions to General Ledger transactions

    I just wanted to put some information out here.

    It is possible to tie the Project subledger (ProjTransPosting) to the General Ledger (GeneralJournalEntry(GJE)/GeneralJournalAccountEntry(GJAE))

    You can do this by going GJE -> GJAE -> DimensionAttributeValueCombination -> MainAccount1

    Then GJE -> ProjTransPosting -> DimensionAttributeValueCombination -> MainAccount2

    Then relate GJAE to GJE via GJAE.GeneralJournalEntry.

    Then relate ProjTransPosting to GJE via ProjTransPosting.Voucher = GJE.SubledgerVoucher AND ProjTransPosting.LedgerTransDate = GJE.AccountingDate

    You then have to relate MainAccount2.MainAccountId = MainAccount1.MainAccountId. This will make sure you are linking  to the correct accounts.

    I have tested this and it successfully allows you to break out the GL Summarization into individual transactions from the Project subledger.

    In my situation this helps some but not completely.  We have so many project transactions, that one single GJE voucher transaction could translate to thousands of individual project transactions.

    I hope this helps!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    thanks very much for this. Would you have any suggestions about managing deferred revenue through projects? 

    Prepayments and Accruals I have managed but have not managed to find a way to show the revenue hitting the project in the correct period from an accounting perspective - only a cash flow perspective. 

    I'm looking to do both. Be able to run an accounting P+L by period on a certain project, while also looking at the cash flow to the project by removing all accounting treatment. The only thing currently missing from this is deferred revenue where we received revenue on a quarterly basis on the first of every quarter but on an accruals basis the P+L should show this spread over the 3 periods, not just hitting one period.

  • David Massey Profile Picture
    David Massey on at
    RE: Linking Project transactions to General Ledger transactions

    Unless you are using Project as one of the financial dimensions, you cannot get this information from the general ledger.  Project transactions may be summarized when posting to the general ledger. ProjTransPosting contains the detailed posting for project.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hello, 

    I currently have a similar issue with projects. 

    I'm trying to pull out revenue from a GL account and filter a value based on how much of the revenue was posted to the project. 

    Going to 'transaction origin' in AX in the GL you can see the costs which have been posted to 'module' project. This is a temporary table, however, and i can't find any way to access this to filter information. 

    Ideally i want to see a value of transactions in GL X which have been coded to project Y - surely this can't be as difficult as it seems. I can't pull this information from the projects module as this is for looking at deferred revenue. The revenue only hits the project as one sum, the deferrals affect the GL - not the project. 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hi Bobby,

    I have somewhat managed (although not 100% perfect) to link Project to GL. It works ok and I use it primarily to compare the overall (grand total) for each project for what is in the Project module versus what has landed in the GL. Have a try and let us know how you go. Hope it helps and of course you can bring in whatever columns you want from the below tables.

    SELECT
    T1.PROJID,
    T1.TRANSID,
    T1.AMOUNTMST,
    T3.MAINACCOUNTID
    FROM dbo.PROJTRANSPOSTING T1
    LEFT JOIN dbo.DIMENSIONATTRIBUTEVALUECOMBINATION T2
    ON T1.LEDGERDIMENSION = T2.RECID
    LEFT JOIN dbo.MAINACCOUNT T3
    ON T2.MAINACCOUNT = T3.RECID
    LEFT JOIN dbo.PROJREVENUETRANS T4
    ON T1.TransID = T4.TRANSID

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hello M84AB1,

    I just came across yyour query from June 2016, I am having the exact same issue and cannot see any link between PROJTRANSPOSTING and GENERALJOURNALACCOUNTENTRY - I wondered if you manage to solve this ?

    Regards

    Bobby B

  • David Massey Profile Picture
    David Massey on at
    RE: Linking Project transactions to General Ledger transactions

    There is no way to link all project transactions to the GL postings.  As you indicated postings from project to the GL can be summarized. The project transactions will indicate the voucher used to post the GL amounts.  A another issue which can affect trying to reconcile the amounts is that not all postings to a GL account are process through the project module. Depending on the business documents used, the source document framework will help in the scenario of summarized posting.  Unfortunately, not all of project business documents use the source document framework.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hi,

    The project module tables have a lot of relations to other AX module tables. What might be a good starting point though is the standard project cube that includes a number of views that you can access from the AOT. Probably take a look at the ProjTransPostingCube & ProjectTransPosting views available in the AOT to identify the tables and fields that you need for the reconciliation job.  

    Another alternative are the ProjectReconLedger classes. Probably take a look at those classes to identify how and from where they retrieve the data.

    Hope this helps at least to get started.

    All the best,

    Ludwig

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Thank you kindly once again for addressing my question Ludwig, but I am afraid that those out of box front-end reports won't be of much help with our reconciliation. Our GL data is setup in AX to be summarized and there are a large number of projects that need to be reconciled, a process that is cumbersome through the front-end reports.

    Ideally, as a BI/Reporting analyst, I would like to be able to use SQL to link projects to GL transactions and present it via a SSRS report...etc

    Would you know if it is possible to do this through SQL? I am interested in the tables and their associated links. Help as always is greatly appreciated!

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hi,

    GL/Reports/Project and then /reconciliation or /transaction.

    If you are using a financial dimension for your projects you can alternatively also use the Trial Balance form in GL. In addition to the project financial dimension required you would need to setup a financial simension set that includes your main accounts and project financial dimensions under GL/Setup/financial dimensions/financial dimension sets.

    Hope this helps.

    Best regards,

    Ludwig

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans