How to report amounts of partially received purchases?
jiipm2
Member Posts: 14
What am I doing wrong here? And more importantly, how can I do this correctly? 
Ok, here's an example scenario:
Let's say it's the middle of a calendar month. I want to use Navision to provide an estimate of the amount of purchases for that month.
1. I can use report Vendor/Item Purchases (report ID 313) with filter Posting Date to find out the amount of already received purchases during the first part of the month. (Or should I use Document Date or Valuation Date instead?)
2. I can use report Inventory Purchase Orders (report ID 709) with filter Expected Receipt Date to find out how much I can expect to still arrive during the latter part of the month.
One big problem I had with this approach was that there are partially received purchases: their amount on back order appear correctly on the Inventory Purchase Orders. But, the already received part (i.e. non-outstanding part of the purchase order) does not show up on the Vendor/Item Purchases. I am guessing that this happens because the report only takes into account purchases with zero outstanding quantity?
Naturally I want to also include the amounts of the purchases which have been partially invoiced but still have an amount also on back order.
What kind of approach should I take to get where I want? I did search mibuso for an answer and also spent plenty of time going through different reports to try to find a solution, but wasn't able to.
Ok, here's an example scenario:
Let's say it's the middle of a calendar month. I want to use Navision to provide an estimate of the amount of purchases for that month.
1. I can use report Vendor/Item Purchases (report ID 313) with filter Posting Date to find out the amount of already received purchases during the first part of the month. (Or should I use Document Date or Valuation Date instead?)
2. I can use report Inventory Purchase Orders (report ID 709) with filter Expected Receipt Date to find out how much I can expect to still arrive during the latter part of the month.
One big problem I had with this approach was that there are partially received purchases: their amount on back order appear correctly on the Inventory Purchase Orders. But, the already received part (i.e. non-outstanding part of the purchase order) does not show up on the Vendor/Item Purchases. I am guessing that this happens because the report only takes into account purchases with zero outstanding quantity?
Naturally I want to also include the amounts of the purchases which have been partially invoiced but still have an amount also on back order.
What kind of approach should I take to get where I want? I did search mibuso for an answer and also spent plenty of time going through different reports to try to find a solution, but wasn't able to.
0
Comments
-
have you tried makeing a report based on Itrem Ledger Entries.
Type: Purchase
Posting Date to get your date filter
Use Adjusted Cost(Expected) column to calc total
:-k0 -
Take a look at the Inventory to G/L Reconcile report. It has exactly the information you need!
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0 -
Alex Chow, what is the ID of that report? We are running Nav 5.0 and I wasn't able to spot that report in the object designer.
Savatage, I will try doing what you suggested once it's not as hectic at the office... Assuming of course I cannot find the Inventory to G/L Reconcile report.
Thank you both for quick replies
0 -
Here is an example of value entries records for an item that was partially received on 04.01.01 (75 units of total 200) and then invoiced on 04.01.05 (receiving remaining 125 and invoicing all 200 units)
date format above is YY.MM.DD
fields indicated:
Posting Date
Item Ledger Entry Type
Entry Type
Document No.
Cost Amount (Expected)
Cost Amount (Actual)
Item Ledger Entry Quantity
Valued Quantity
Invoiced Quantity
Source Type
Source No.
External Document No.
Item Ledger Entry No.04.01.01 Purchase Direct Cost 107029 105,00 0,00 75 75 0 Vendor T00020 317 04.01.05 Purchase Direct Cost 108028 -105,00 105,00 0 75 75 Vendor T00020 9467725 317 04.01.05 Purchase Direct Cost 108028 0,00 175,00 125 125 125 Vendor T00020 9467725 318
0 -
Alex Chow wrote:Take a look at the Inventory to G/L Reconcile report. It has exactly the information you need!

It's report 101380 -
i dont have the report 10138

which country localization range the number 10138 belongs to ?0 -
http://savatage99.googlepages.com/10138 ... oncile.fob
Never tried myself, but if alex says it's got what you need i'd give it a try.0 -
Savatage wrote:http://savatage99.googlepages.com/10138-InventoryToGLReconcile.fob
Never tried myself, but if alex says it's got what you need i'd give it a try.
Yep, it's got what you need. It'll give you the items that are received/shipped that are not invoiced, the report will give you the expected costs of the items that are received, but not invoiced yet.Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0 -
Only thing is my copy is old - I would assume new enhancements have been added :-k
Since it's a us report - you might have to change some of the fields to match the int'l fields.
Like I might have Amount ($) and you might have a different one.0
Categories
- All Categories
- 75 General
- 75 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.8K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 611 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 253 Dynamics CRM
- 103 Dynamics GP
- 6 Dynamics SL
- 1.5K Other
- 991 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 28 Design Patterns (General & Best Practices)
- Architectural Patterns
- 9 Design Patterns
- 4 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1K General Chat
- 1.6K Website
- 77 Testing
- 1.2K Download section
- 23 How Tos section
- 249 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions