You’re offline. This is a read only version of the page.
10
Here is my goal: I would like to create an SQL query that gives me line-item detail for Purchase Price variances that include the following attributes:
From my research I thought that the following attributes in table INVENTTRANS translated to the following values above:
COSTAMOUNTPHYSICAL = Standard Cost of Total Receipt
COSTAMOUNTOPERATIONS = Standard Cost of Total Relief
COSTAMOUNTPHYSICAL + COSTAMOUNTOPERATIONS + COSTAMOUNTADJUSTMENT = PO Material Cost
Note that for the PO material cost I thought I could also use VENDPACKINGSLIPTRANS.VALUEMST
COSTAMOUNTADJUSTMENT = Purchase Price Variance
COSTAMOUNTPHYSICAL + COSTAMOUNTOPERATIONS = Standard Material Cost
Unfortunately, these assumptions are not always correct.
From the reading that I have been doing online the INVENTTRANS table contains any and all transactions for inventory movements. As I think I understand this, that means all Physical transactions, yet I see what I think are Financial transactions in this table, PIV vouchers. These PIV vouchers will show (in the VOUCHER attribute) in the same record as the Physical Voucher, so the record must get updated at a later date. So then, the values in that record also get updated.
Another post was found with some related information: community2.dynamics-int.com/.../ledgertrans-inventtrans
Then in your post you wrote about the INVENTSETTLEMENT table. In there you wrote these are transactions related to inventory closing or recalculation (Cost Roll?). But these values seem to equal the PPV, which I did not know get calculated during the inventory closing (I see these numbers in the vouchers BEFORE the inventory closing process).
My background is that I have been working with MS Dynamics for a little over a year and am finding that getting to correct table with the correct relationships is cumbersome to say the least. There is the ability in AX to explore the tables and attributes using Alt-D, but the relationships displayed here are very much incomplete, kind of frustrating really.
Anything you could teach me would be most appreciated. The current SQL code I am using is below.
Thanks,
-Luke
lsmith@forneyind.com
Select
t1.VOUCHERPHYSICAL
,t1.VOUCHER
,t2.ORIGPURCHID
,t2.PURCHASELINELINENUMBER
,T2.PURCHNAME
,t1.DATEPHYSICAL
,T2.DELIVERYDATE
,t1.ITEMID
,t2.ItemDescr
,T2.[Qty PO Ordered]
,T2.[Qty PO Rcpts]
,T1.[Tot Std $ Rcpts]
,T1.[Tot Std Relief $ Rcpts]
,T1.[Tot Std Matl $ Rcpts]
,T2.[Tot PO Matl Rcpts]
,t2.[Tot PO Matl Rcpts] - T1.[Tot Std Matl $ Rcpts] as 'Tot PPV $ Rcpts'
from
(
Select
it.DATEPHYSICAL
,it.VOUCHER
,it.VOUCHERPHYSICAL
,IT.ITEMID
,ITO.INVENTTRANSID
,ITO.RECID
,sum(it.COSTAMOUNTPHYSICAL) as 'Tot Std $ Rcpts'
,sum(it.COSTAMOUNTOPERATIONS) as 'Tot Std Relief $ Rcpts'
,sum(it.COSTAMOUNTADJUSTMENT) as 'Tot PPV $ Rcpts'
,sum(IT.COSTAMOUNTPHYSICAL + It.COSTAMOUNTOPERATIONS) as 'Tot Std Matl $ Rcpts'
from inventtrans IT
join INVENTTRANSORIGIN ITO on ITO.RECID = IT.INVENTTRANSORIGIN
where it.DATEPHYSICAL between '1/01/2021' and '01/31/2021'
group by
it.DATEPHYSICAL
,it.VOUCHER
,it.VOUCHERPHYSICAL
,IT.ITEMID
,ITO.INVENTTRANSID
,ITO.RECID
having SUM(it.COSTAMOUNTPHYSICAL + it.COSTAMOUNTOPERATIONS + it.COSTAMOUNTADJUSTMENT) <> 0
) T1
Inner join
(
Select
VPST.COSTLEDGERVOUCHER
,VPST.DELIVERYDATE
,VPST.ITEMID
,vpst.NAME as ItemDescr
,VPST.ORIGPURCHID
,PT.PURCHNAME
,VPSt.PURCHASELINELINENUMBER
,VPST.INVENTTRANSID
,max(vpst.ORDERED) as 'Qty PO Ordered'
,sum (VPST.QTY) as 'Qty PO Rcpts'
,sum(vpst.VALUEMST) as 'Tot PO Matl Rcpts'
From VENDPACKINGSLIPTRANS VPST
inner join PURCHTABLE PT on PT.PURCHID = VPST.ORIGPURCHID
group by
VPST.DELIVERYDATE
,VPST.ITEMID
,vpst.NAME
,VPST.ORIGPURCHID
,VPSt.PURCHASELINELINENUMBER
,VPST.INVENTTRANSID
,VPST.COSTLEDGERVOUCHER
,PT.PURCHNAME
) T2 ON t2.INVENTTRANSID = t1.INVENTTRANSID
and t2.COSTLEDGERVOUCHER = t1.VOUCHERPHYSICAL
Hi Luke,
If you don't mind, I have removed my name from the title and added the link to the other forum post in question which you sent to me via LinkedIn. Now, every volunteer might understand and try to help you.
I have no environment where I can quickly find for some information about the relief amount and where this is stored. Probably, you can share some examples and screenshots from your environment.
In my environment, I have an example where:
COSTAMOUNTPHYSICAL = Standard Cost
COSTAMOUNTPOSTED = Purchase Cost (didn't use charges)
COSTAMOUNTADJUSTMENT = Price Variance.
COSTAMOUNTPOSTED + COSTAMOUNTADJUSTMENT = Standard Cost in my example.
Can you also tell which result you have now and where you found the differences?
Also note that I'm not a person who can't directly read SQL statements without viewing the result. (except for more simple queries).
#1
Martin Dráb
439
Most Valuable Professional
#2
Abhilash Warrier
305
#3
Saalim Ansari
261
Share
Report
All responses (
Answers (
