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
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,
-( 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' )
If you've found this thread useful, dive deeper into User Group community content by role