Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Multiple, identical ro...
Finance | Project Operations, Human Resources, ...
Suggested answer

Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

(1) ShareShare
ReportReport
Posted on by hakio 5

Hi,

Via SQL, I need to extract customers invoices, incl. the invoice lines and the costs for each line. Hence I am joining with InventTrans, to get the individual invoice lines cost so I can calculate the margin and profit.

All is good, I can archieve this. BUT, sometimes there are multiple near-identical rows in the InventTrans-table, and I cannot figure out how to only get the rights ones. This is only relevant for some invoice lines, not all. I have researched and looked for columns and other tables which holds some information telling me how to pinpoint only the relevant rows.

My SQL:

SELECT
cij.invoiceid, cit.LINENUM,cit.ITEMID,cit.INVENTDIMID as [CIT.INVENTDIMEID], it.INVENTDIMID as [IT.INVENTDIMEID], cij.LEDGERVOUCHER, it.VOUCHER, it.VOUCHERPHYSICAL, it.DATEINVENT
  FROM CustInvoiceJour cij (NOLOCK)
INNER JOIN custInvoiceTrans cit (NOLOCK) ON (cij.INVOICEID = cit.INVOICEID AND cij.DATAAREAID = cit.DATAAREAID)
INNER JOIN INVENTTRANS it (NOLOCK) ON (it.INVOICEID = cit.INVOICEID AND it.INVENTTRANSID = cit.INVENTTRANSID AND it.INVENTDIMID = cit.INVENTDIMID AND it.DATAAREAID = cit.DATAAREAID)
INNER JOIN INVENTDIM id ON cit.DATAAREAID = id.DATAAREAID AND cit.INVENTDIMID = id.INVENTDIMID
WHERE cit.INVOICEID like '%713620F%' --just for this example, to get a problematic invoice.


Result is like this:
https://ibb.co/GJsD4kF

pastedimage1592292065575v1.png

For 2 of the itemid's there are double lines, which comes from InventTrans. The ONLY difference in the InventTrans-rows are that the VoucherPhysical and DateInvent. I looked into the '1900-01-01' DateInvent value, if this could be the ones to exclude but not - for other invoicelines which only 1 InventTrans row the DateInvent value was '1900-01-01'.

I am not yet fully understanding the whole datamodel and the InventTrans table, so this may be my problem.

I have got the printed invoices and UI-extracted reports, which does not contain the doubled lines.

Please advise if you can, would be much appreciated. I can provide more details and extracts if need be!

  • hakio Profile Picture
    hakio 5 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    I might be able to find the answer myself, looking at the method inventCostAmount() as suggested earlier by Nikoloas. There are other similar methods that i would very much like to see what does.

    You can look at inventCostAmount() method in CustInvoiceTrans to see how to fetch cost amount for the invoice line.

    Can anyone help me find these methods? There are nothing to find googling for it, or maybe i'm not googling right!

    I do not - at the moment - have access to the Dynamic Ax3.0 application.

    Reply

  • hakio Profile Picture
    hakio 5 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    Yup, people still using it!! In particular in Denmark where the Navision and Dynamics product came out from originally.

    But thanks for assisting so far Nikolaos... really hope someone else can assist. There must be ppl outthere knowing... :)

  • nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    Sorry, actually the support has ended 2012 for SP6 but anyway it's a long time :)

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    It's just that support for AX3 has ended 12 years ago (2008) so it's surprising if it's still being used :)

    Must be quite interesting to maintain all the required (also unsupported) dependencies (Windows Server, SQL Server) to use it.

    I guess the data model in this ancient version of AX was a bit different so you can forget about my answer :) Unfortunately I haven't even seen and AX3.0 system in 10 years so I can't check it for you.

    Hopefully someone else can help.

  • hakio Profile Picture
    hakio 5 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    Yes, I am indeed using AX3.0. How come you ask, something not adding up...?

    You mentioned the "InventTransOrigin"-table. This doesn't exist in the database... Only have INVENTTRTANS and INVENTTRANSPOSTING.

    Regarding inventCostAmount(), I'm an external part setting up some reporting/BI for a customer. Only customer got access. I do have the option to contact them about this, though they do not have much knowledge themselves about this. They have a MS-partner helping them when it comes to fiddling around with AX30, but they charge horrific rates for assistance and I would do much to avoid this road.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    You can find it in AX, ask someone in your company who knows AX. How to exactly find it depends on the AX version.

    By the way are you really using AX3.0 as indicated in the tag of your question?

  • hakio Profile Picture
    hakio 5 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    OK, it sheds more light over my issue... would really like to dive into that inventCostAmount() function. But where can I find this to look into what it does...?

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    One Invoice line can be linked to many InventTrans records.

    Perhaps this helps a bit:

    One sales line is 1:1 related to InventTransOrigin (which has one record per InventTransId).

    There can be 1-n InventTrans records that are linked to this InventTransOrigin. For example if the order line is shipped in three separate parts (3 packing slips) there will be 3 InventTrans records, one for each inventory transaction.

    You can look at inventCostAmount() method in CustInvoiceTrans to see how to fetch cost amount for the invoice line.

  • hakio Profile Picture
    hakio 5 on at
    RE: Multiple, identical rows returned when getting InvoiceLines joined with InventTrans, for retrieving costs for calculating profit/margins

    I forgot to mention, I've been using these resources to get insights into the datamodel and table structure in AX3.0. They are mainly for 4.0 but there are some overlap.

    * www.database.fun/.../InventTrans.html

    * kashperuk.blogspot.com/.../microsoft-dynamics-ax-40-data-model.html

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans