News and Announcements icon

Dynamics 365 CommunityForums Small and medium business | Business Central, N... Cost price on Sales li...

Small and medium business | Business Central, N...

Unanswered

Cost price on Sales lines (SQL table structure question)

(0) Share

Report

Report

Posted on by KristianSauer

5

Hello,

Im trying to make an SQL query on the sales line table for BI reporting. I have run into some problems with the fact that the unit cost doesn't get updated automaticly.

We sell a lot where we don't have the item in the warehouse yet, so a lot of sales lines and invoiced sales lines doesn't have a cost price.

Is there a workaround? Can i get a correct cost price from somewhere else? Can I get it from the Value Entry table or maybe Item Ledger Entry. Average price is fine - it doesn't have to be FIFO.

We are on a NAV2017 if that matters.

All responses Img

All responses (1)

Answers Img

Answers (0)

Suresh Kulla 43,745 on at

Like (0)

Report

RE: Cost price on Sales lines (SQL table structure question)

Yes you can get that from using Value Entry and Item Ledger Entry, once you find the Item Ledger Entry, filter the Value Entry table for that Item Ledger Entry No. then sum cost amount expected and cost amount Actual then divide by Item Ledger Entry Quantity * Qty. Per Unit of Measure

Helpful resources

Quick Links

Leaderboard

#1

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

#2

Martin Dráb 230,198 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans