Skip to main content

Notifications

Announcements

No record found.

Finance | Project Operations, Human Resources, ...
Unanswered

Help with creating a PPV SQL Query report with part number detail

Posted on by 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:

 

  • Item number
  • Item Number description
  • Vendor Name
  • Physical Voucher
  • Financial Voucher
  • Purchase Order Number
  • Receipt Date
  • Order Quantity
  • Receipt Quantity
  • Standard Cost of Total Receipt
  • Standard Cost of relief (We have Freight, Labor and Overhead)
  • PO value of material receipt
  • Purchase Price Variance

 

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

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    RE: Help with creating a PPV SQL Query report with part number detail

    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).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans