SQL Scripts & Views
***If some of the links do not work, it is because I have not published the actual article yet. I am trying to figure out how to time the updating of this page with the publication of an article. Just think of it as a preview of what’s to come 🙂
-Ron***
SQL Views
How To Use SQL Views – A little help getting you started…
Financial Management
– Active Chart of Accounts (tspvActiveChartofAccounts)
Inventory Management
– Item Master (tspvItemMaster)
– Inventory Multi-bin (tspvInventoryMultiBin)
– Inventory Serial/Lot Numbers (tspvInventorySerialNum)
– Inventory Summaries – All Locations (tspvInventory)
– Inventory Summaries – By Location (tspvInventoryByLoc)
– Inventory Receipts (tspvInventoryReceipts)
Purchase Order Processing
– Purchase Order Header (tspvPurchaseOrder)
– Purchase Order Line Items (tspvPurchaseOrderLineItems)
– POP to SOP Links (tspvSopToPop)
Sales Order Processing
– Customers (tspvCustomers)
– Open Sales Documents (tspvSalesDocument)
– Open Sales Line Items (tspvSalesLineItem)
– Historical Sales Documents (tspvSalesDocumentHistory)
– Historical Sales Line Items (tspvSalesLineItemHistory)
– Sales Document Holds (tspvSalesDocumentHolds)
– SOP to POP Links (tspvSopToPop)
System
– GP Notes (tspvGPNotes)
SQL Stored Procedures
Inventory Management
– Update Item Costs (tspspUpdateItemCosts)
SQL Scripts
*** (Script can also be used as an Alert)
Financial
– Default Item Class GL Accounts (Item Class Account Setup)
Inventory Management
– Creating Price Levels Based On Existing Price Level
– Update Outdated Inventory Pricing
– Update Inventory Pricing With Excel, Access, and SQL Server
– Standard Cost Less Than Valuation Cost ***
Purchase Order Processing
– Invoice Line Item to Receipt Line Item Match
– Orders Needing Purchase ***
– Purchase Order Line Item Discrepancy ***
– SOP to POP Freight Analysis ***
Sales Order Processing
– Invoices Coming Due Next Week ***
– Order Count – Previous Week By User ***
– Order Count – YTD By User ***
System
– Clear Users and Activity Locks
– Dynamics GP User Idle Time ***
– Script to update Your Test Company After Restoring From Your Live Company
Trackbacks & Pingbacks
- There’s a new blog in town « Victoria Yudin
- There’s a new blog in town - Victoria Yudin
- Tips and Tricks #4 – SQL (nolock) – Do You Need To Use It? « Real Life Dynamics User
- Tips and Tricks #6 – How To Use SQL Views « Real Life Dynamics User
- Tips and Tricks #6 – How To Use SQL Views - Real Life Dynamics User (RLDU)
- From The Community #5 – Match Invoice Receipt To PO Line Item « Real Life Dynamics User
- From The Community #5 – Match Invoice Receipt To PO Line Item - Real Life Dynamics User (RLDU)
- Using a SQL view in SmartList Builder « Dynamics GP Builders & Reporting
Real Life Dynamics User 
Hi Ron
I am a new user to GP and have been reading up on stock counts in GP. I am wondering if you know of a way to run cycle counts by bin ID instead of running by site ID, ABC code, item number etc. Our company has about 3,000 different products and the easiest way to perform daily cycle counts would be by bin location. Any help or ideas would be much appreciated.
Chad
Chad,
We have done this via SQL Reporting Services (outside of Dynamics GP). I have not found a way to modify the existing GP Stock Count report to sort by bin number. In the near future, I have it on my list to share my stock count report but I haven’t had the time yet. Do you use Mulitbin in GP? Do you have any serial/lot items?
Ron
If your aretclis are always this helpful, “I’ll be back.”
Ron
It doesn’t look like we are currently using the multibin function and I’m not sure if it is something we want to use as I’m not entirely sure what it does. We do have some items that we load serial numbers on.
Chad
Chad,
You are the second person this month that has asked about stock counts. We use multibin and serial/lot tracking, so my stock count report that I have written will not work for you. However, bare with me as I am involved in finishing up 1st quarter financials, I plan to adapt my stock count script and post it here soon. That should be my next posts on the blog.
Ron
Sounds great Ron. Thanks for any help you can give a new GP user
Chad
Thanks for the help on cycle counting Ron. We are all set. I have now moved on to creating kits for a few packages we sell and I have been able to to create the kit number and assign its components but when I enter the kit number on a sales order to sell it says “The price level/U of M combination is not included in the price list for this item.” I can’t seem to figure out where this warning is trying to direct me. Any ideas?
Chad
Chad,
That error message typically means that the kit itself or an item in the kit do not have pricing setup in the Price List Utilities. Can you verify that all your items, including the kit part number itself, have pricing setup in the Price List utilities?
Ron
That was the issue. I drilled down one more screen on the kit pricing and found that there wasn’t a default price loaded. The kit appears to be working now.
Chad
Ron
We have a vendor that is continually superseding part numbers. Is there a way to merge the old number into the new number and have the history transfer? It is the same product, just a new part number.
Chad
Chad,
I wish there was a rule where vendors could only change part numbers in DIRE emergencies. The fact of the matter is there is not good *cheap* way of doing this. We have purchased the Item Number Changer from the PSTL (Professional Service Tools Library) to accomplish this task. It is pretty simple and will do exactly what you are looking to do. However, everyone has to be logged out of the system to run it (so for the most part that means it has to be ran at night) and it and it costs $700 (or that is what we paid for it a few years ago).
Ron
Hi,
I need to reconsile GRNI account with GL. I need to know Project wise, PO wise Accrual balance to receive.
I have taken all debit and credit from GL20000 and GL30000 table.Excluded BBF Source transaction. My GL data ia maching with GP. I need detail of GL transaction from POP table.
Do you have any thought on this?
Regards,
Santosh
Santosh,
Are you looking for the GL detail as it pertains to each line item in the POP tables? Are you wanting to see each purchase order line item’s distributions?
Ron
Ron
I have been working with the po generator and it is doing some things that I can’t explain and am hoping you might have some ideas. I have an item set with an order point of 4 and right now we are out of stock but when I go to suggest po’s through the generator it only tells me to order 1. My understanding is that it should be ordering up to 4. Any thoughts?
Chad
Chad,
The order point is the level your inventory is allowed to get down to before it needs to be reordered. The order up to level is the order you want to order up to.
For example…
You have your item’s order point set to 4. When the qty available drops to below 4 then PO generator knows that it needs to reorder. You need to have your order up to level defined to the number you want at most on hand, let’s say 10.
Your inventory drops to 3 and the PO generator picks up to reorder. If your order up to level is set to 10, then it will know it needs to order 7 to get you to the 10 on hand.
One other thing to think about is if your vendor requires you to order in box quantities. Let’s say that the item in this example can only be purchased in box quantities of 5. There is another field to set on the Item Resource Planning in Dynamics GP, under the Order Quantity Modifiers, that is called Multiples. You would enter 5 in this field since you are required to order boxes of 5. When the PO generator runs, it will see that you need to order 7 to get back up to the 10 on hand order up to level, but the multiples are set to 5 for that item.
At this point, I am not 100% sure whether it will order 5 (taking your on hand to 8) or order 10 (taking your on hand to 13). Either way, it will know that it can’t order 7.
Does this help?
Ron
Right now we really just want to use the po generator to flag us to order inventory when it gets below order point and we are going to tweak the order quantities as needed. On the majority of our items it is telling us to order up to the order point quantity when it drops below. So if the OP is set at 10 and we get to 6, the generator is telling us to order 4 but I have no idea where it is coming up with an order quantity of 1 from my previous post. If it stays consistent it should still want me to order 4. Is the order up to field required to be entered to make the generator work? Maybe that is causing issues.
Thanks as always
Chad
Chad try making order point and order up to the same number and see if you get the results you are looking for. I know we use both but I am not sure if order up to is required. It may be why you are not seeing the results you expect.
I set the order up to at 4 also and it still wants to order 1. I checked the UOM and that is set at each so that isn’t causing it but all the other I have set are working like it think they should so I’ll just keep working with this one. One more question, is there a way to run a mass update to change all inventory items from a order policy of “not planned” to use po gen? We want all of our items flowing through the generator so if one of our CSR’s writes up an order an forget to order the stock it gets picked up here.
Thanks
Chad
Chad,
You may try Tools->Utilities->Inventory->Purchase Order Item Mass Update and select all items and set the Replenishment Level to Order Point Quantity (make sure to check the Update box).
If that doesn’t work, we could try a SQL update.
Ron
Ron
Unfortunately that didn’t work. We have about 7000 item numbers created so it will take a little time to change all of them over to use po gen but not the end of the world if it takes a few days to update. If you think of an easier way I’m all ears.
Chad
Chad,
Are you familiar with macro’s in GP? That would be your next easiest route. I am going to have a busy week this week with financial statements, so I don’t know how much time I will have to explain them to you. You may want to try a post in the forums about it.
Ron
Ron
I’m not familiar with macro’s in GP but I was messing around last night and found a fix for my problem. I went into Site under inventory cards and there is a planning tab. I selected the site I wanted to fix and changed the order policy to use po gen from not planned and hit save. It then asks if I want to update all items or just changed. I have tried it both ways on test and they both appear to do the same thing. All items are now updated with use po gen. The downside to this is that it erases and order points that have been loaded up to this point so I’ll have to re-load the 800 I have entered but that is a much easier fix than going item by item.
Chad
I am glad that worked for you. I have not used that because of the fact that it erases order points. We have over 60,000 skus and it would be a monster to try and recode all order points. You should really check out macros. They are not perfect but can get the job done when you are in a pinch.
Here is a little help on macros.
http://www.dynamicscare.com/blog/index.php/macros-in-dynamics-gp/
Well, this material has just blown up my mind!
So, many interesting facts and critical instances
that I am astonished and highly satisfied with the data you give us.
The topic is burning as well, so I suggest I’ll read it twice, as I did with this article https://mormonhistoricsitesregistry.org/. Last but not least is
I can share this information with friends of mine and get their pleasure from the specified material also.
Let such info be!