You’re offline. This is a read only version of the page.
Announcements
1,205
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!
| Comes from Sales Invoice | Comes from General Journal | ||||
| Volume (in tonnes) | Net Sales | Gross Margin | |||
| Product | |||||
| Hierarchy 1 | |||||
| Product 1 | 0.526169786 | 8273 | 2899 | ||
| Product 6 | 0.323016874 | 3451 | 8224 | ||
| Product 3 | 0.503629612 | 8693 | 4577 | ||
| Product 7 | 0.725403596 | 974 | 4832 | ||
| Product 5 | 0.441844946 | 6650 | 1414 | ||
| Hierarchy 2 | |||||
| Product 8 | 0.742636967 | 2253 | 2747 | ||
| Product 11 | 0.752802217 | 5874 | 3547 | ||
| Product 9 | 0.280030303 | 6841 | 8401 | ||
| Product 13 | 0.713495586 | 7548 | 6150 | ||
| Product 15 | 0.646640239 | 5983 | 5513 | ||
| Hierarchy 3 | |||||
| Product 12 | 0.174442401 | 9610 | 1120 | ||
| Product 14 | 0.740282075 | 327 | 5108 | ||
| Product 16 | 0.734997691 | 9851 | 7295 | ||
| Product 17 | 0.446216801 | 1230 | 9819 | ||
| Product 19 | 0.264139721 | 3856 | 4206 | ||
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).
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,
There are no standard data entities which can be used for this purpose. You would need custom entities or a custom report.
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.
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.
#1
André Arnaud de Cal...
294,137
Super User 2025 Season 1
#2
Martin Dráb
232,877
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
