Notifications
Announcements
No record found.
Hi all,
I have a client with a report that looks like this coming out of SAP who are migrating to D 365. They are using BYODB and have a data warehouse. How do I split Net Sales/Gross Margin which come from the General Journal by item/Product? ie what are all the relationships/tables/entities between General Journal and Sales Invoice? Thanks!
Hi Jhowe,
This would be quite cumbersome to get a correct query. Probably, you can better get the information from the inventory transactions instead of the general journal.
Tables involved:
InventTrans
InventTransOrigin
InventTransSettlement
InventTransPosting (this is a details table with a reference to the inventory transaction and the ledger transaction)
When getting the data from the inventory transactions without the general ledger, you can use the first two listed tables.
Hi, thanks for your reply, as you know with D 365 unfortunately we do not have direct access to the database, are there any entities out of the box we can utilise? We’re due to be going live in the next couple of weeks so can’t really make any code changes at this stage unfortunately.
There are no standard data entities which can be used for this purpose. You would need custom entities or a custom report.
Hi Andre so we are using entities SalesInvoiceHeaderV2Entity (custinvoicejour) and SalesInvoiceLineV2Entity (custinvoicetrans), could you take me through an example of how i would take a line from custinvoicetrans for example and replicate that same information in the tables you mentioned? That would give me a good understanding of how those tables work... thanks again.
Hi jhowe,
The table CustInvoiceTrans contains a field InventTransID which can be linked to the inventory transaction table(s) including also the financial date (invoice date) and financial voucher (invoice ID).
Apologies Andre, I didn't realise you'd replied again. Thanks I will investigate, I think I have all the information I need.
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 544 Most Valuable Professional
André Arnaud de Cal... 450 Super User 2025 Season 2
Sohaib Cheema 250 User Group Leader
