Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Luke Smith]

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
——————————

[Barclay Hershey]

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
——————————
——————————————-

[Luke Smith]

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
——————————
——————————————-

[Mark Yankovich]

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
——————————
——————————————-