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
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!
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.
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.
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.
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
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
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.
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
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!
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
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
