D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

inventtrans link to ledger tables

  • 1.  inventtrans link to ledger tables

    Posted 17 days ago

    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
    Academy - Online Interactive Learning from Experts

  • 2.  RE: inventtrans link to ledger tables

    Posted 14 days ago
    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,


    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

    Academy - Online Interactive Learning from Experts

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