D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

inventtrans link to ledger tables

  • 1.  inventtrans link to ledger tables

    Posted Jun 26, 2020 11:59 AM

    I have created SQL queries the give me all ledger transactions for all accounts for a defined time frame down to the voucher level of detail.  I use the SQL code in Power Pivot with a connection to the database to provide basic transnational analysis and trend analysis within Excel.  All of this data ties to the trial balance.

    Now I want to enhance this data to provide drill down capability related to inventory transactions.   I want to be able to look into inventory related accounts (PPV, Warranty, Scrap, etc...) and drill down to see exactly what part numbers, quantities and dollar amounts make up the ledger transactions.

    I am new to AX (8 months) and there is no one at my company with this knowledge; so far, I am self-taught.  I have had some success with this, but the queries run slow.  Also, I do not feel that I have a good understanding of the distinction between Physical and Non-physical transactions and the required table relationships.

    As I currently understand, the physical transaction is related to a physical movement of inventory, such as an inventory receipt, and this would have a physical voucher number. The processing of the vendor invoice for the inventory receipt, I believe, would be a purely financial transaction, and so a non-physical voucher. 

    Also, (very important) where can I get the level below the Voucher?  For example, if we receive multiple different part numbers on one voucher, I would like to see the dollar ledger impact (along with the quantities) for each part number.

    #Technical #finance

    ​​

    ------------------------------
    Luke Smith
    Cost Accountant
    Forney Industries, Inc.
    Fort Collins CO
    ------------------------------
    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: inventtrans link to ledger tables

    SILVER CONTRIBUTOR
    Posted Jun 29, 2020 09:21 AM
    HI Luke,

    Roughly you need to get the Trans table for each module.  Let's look at customer invoices as an example.  The CustInvoiceJour table has your voucher.  From there you link to CustInvoiceTrans where you find the InventTransId, and with the InventTransId you can link to InventTransOrigin and from InventTransOrigin you link to InventTrans
    One issue you will probably encounter is that in most setups the ledger has 1 line which will link to many underlying transactions ( such as an receipt or invoice having more than 1 line ).

    For example, let's say you want to see the cost of sales for the voucher ARSI-000104156:

    Select CIJ.Partition, CIJ.DataAreaId, CIJ.LedgerVoucher, CIJ.InvoiceAccount,

           CIT.InvoiceDate, CIT.InvoiceId, CIT.InventTransId,  CIT.Linenum, CIT.ItemId,

           IT1.CostExt

    From CustInvoiceJour As CIJ

    Left Join CustInvoiceTrans As CIT On CIJ.Partition = CIT.Partition

           And CIJ.DataAreaId = CIT.DataAreaId

           And CIJ.NumberSequenceGroup = CIT.NumberSequenceGroup

           And CIJ.InvoiceDate = CIT.InvoiceDate

           And CIJ.SalesId = CIT.SalesId

           And CIJ.InvoiceId = CIT.InvoiceId

    Left Join (

           Select ITO.Partition, ITO.DataAreaId, ITO.InventTransId,

                  IT.InvoiceId, IT.DateFinancial,

                  -( Sum(IT.CostAmountPosted + IT.CostAmountAdjustment) ) As CostExt

           From InventTransOrigin As ITO

           Inner Join InventTrans As IT On ITO.Partition = IT.Partition

                  And ITO.DataAreaId = IT.DataAreaId

                  And ITO.RecId = IT.InventTransOrigin

           Group By ITO.Partition, ITO.DataAreaId, ITO.InventTransId, IT.InvoiceId, IT.DateFinancial

    ) As IT1 On CIT.Partition = IT1.Partition

           And CIT.DataAreaId = IT1.DataAreaId

           And CIT.InventTransId = IT1.InventTransId

           And CIT.InvoiceId = IT1.InvoiceId

           And CIT.InvoiceDate = IT1.DateFinancial

    Where CIJ.LedgerVoucher In ( 'ARSI-000104156' )



    ------------------------------
    Barclay Hershey
    Financial Analyst
    Sugar Creek Packing Co
    Cincinnati OH
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: inventtrans link to ledger tables

    Posted Jul 30, 2020 05:44 PM
    Hi Barclay,

    This response is much appreciated!

    I just got back to working on this again and saw your post.  I will have a look at this, and I am sure to have some questions.  I am heading into the month-end close, so it may be a week or so.

    I do have one basic question, however.  I am new to this site and I posted the question that you responded to, but I do not know how to get back to my posting to see the responses (like yours); I just happened to find this in another search.  Any help would be appreciated.

    Thanks again.

    ------------------------------
    Luke Smith
    Cost Accountant
    Forney Industries, Inc.
    Fort Collins CO
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: inventtrans link to ledger tables

    GOLD CONTRIBUTOR
    Posted Jul 31, 2020 08:14 AM
    Luke,

    We see responses to our posts come via e-mail with a link right back to the original message.  'Search' also works well.  If looking for an older post and responses of yours enter your first and last name (in quotes) as search criteria.  This has worked for me.


    ------------------------------
    Mark Yankovich
    Allegheny Bradford Corporation
    PA
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role