Commonly Used Tables:
IV00101 – Item Master
IV00102 – Item Quantity Master
IV00103 – Item Vendor Master
IV00104 – Item Kit Master
IV00105 – Item Currency Master
IV00106 – Item Purchasing
IV00107 – Item Price List Options
IV00108 – Item Price List
IV00109 – Item Serial Number Mask
IV00111 – Site Defaults
IV00112 – Item Site Bin Master
IV00113 – Item Price List Details
IV00118 – Item Cost Change History
IV00200 – Item Serial Number Master
IV00300 – Lot Number Master
IV00301 – Lot Attribute Master
IV10000 – Unposted/Work Transactions (header)
IV10001 – Unposted/Work Transactions (line detail)
IV10002 – Serial and Lot Number Work
IV10200 – Purchase Receipts (header)
IV10201 – Purchase Receipts Detail (line detail)
IV10300 – Unposted Stock Count (header)
IV10301 – Unposted Stock Count (line detail)
IV30101 – Sales Summary History
IV30102 – Sales Summary Period History
IV30200 – Transaction History (header)
IV30300 – Transaction Amounts History (line detail)
IV30301 – Transaction Amounts Detail History
IV30400 – Serial and Lot Number History
IV30500 – Distribution History
IV30600 – Lot Attribute History
IV30700 – Stock Count History (header)
IV30701 – Stock Count Line History (line detail)
IV40400 – Item Class Setup
IV40201 – U of M Schedule Setup (header)
IV40202 – U of M Schedule Detail Setup
IV40600 – Item Category Setup
IV40700 – Site Setup
IV40800 – Price Level Setup
Bill of Materials Tables:
BM00101 – Bill of Materials Header
BM00111 – Bill of Materials Component
BM10200 – Assembly Transaction (header)
BM10300 – Assembly Component (line detail)
BM10400 – Assembly Serial/Lot
BM30100 – Assembly Batch History
BM30200 – Assembly Transaction History (header)
BM30300 – Assembly Component History (line detail)
BM30400 – Assembly Serial/Lot History
BM40100 – Bill of Materials Setup
Inventory Transaction Document Types:
1 – Inventory Adjustment
2 – Variance
3 – Inventory Transfer
4 – Purchase Receipt
5 – Sales Returns
6 – Sales Invoices
7 – Assembly
8 – Inventory cost adjustment from POP Shipment (transaction source GLTRX)
11 – Inventory cost adjustment from POP Invoice (transaction source POIVC)
Inventory Valuation Methods:
1 – FIFO Perpetual
2 – LIFO Perpetual
3 – Average Perpetual
4 – FIFO Periodic
5 – LIFO Periodic
Item Types:
1 – Sales Inventory
2 – Discontinued
3 – Kit
4 – Misc Charges
5 – Services
6 – Flat Fee
RCRDTYPE (Record Type) in IV00102:
1 – Overall record (total for all sites)
2 – Site record
ORDERPOLICY (Order Policy) in IV00102:
1 – Not planned
2 – Lot for Lot
3 – Fixed Order Quantity
4 – Period Order Quantity
5 – Order Point
6 – Manually Planned
ITMVNDTY (Item Vendor Type) in IV00103:
1 – Primary Vendor
2 – Non-primary Vendor
Quantity Types:
1 – On Hand
2 – Returned
3 – In Use
4 – In Service
5 – Damaged
Decimal Places:
1 – 0
2 – 1
3 – 2
4 – 3
5 – 4
6 – 5
TAXOPTNS and Purchase_Tax_Options (Tax Options):
1 – Taxable
2 – Nontaxable
3 – Base on customer / vendor
ITMTRKOP (Item Tracking Option):
1 – None
2 – Serial Numbers
3 – Lot Numbers
PRICMTHD (Price Method):
1 – Currency Amount
2 – % of List Price
3 – % Markup – Current Cost
4 – % Markup – Standard Cost
5 – % Margin – Current Cost
6 – % Margin – Standard Cost
KTACCTSR (Kit COGS Account Source):
0 – COGS accounts from component items
1 – COGS account from kit
PSTGSTUS (Posting Status) in IV10000:
0 – New Transaction
1 – Unposted
2 – Posted
3 – Posted with Error
Source Indicator in IV10000:
1 – (none)
2 – Issue
3 – Reverse Issue
4 – Finished Good Post
5 – Reverse Finished Good Post
6 – Stock Count
7 – Field Service – Service Call
8 – Field Service – Return Material Authorization
9 – Field Service – Return to Vendor
10 – Field Service – Work Order
11 – Project Accounting
12 – In-Transit Inventory Transfer (thanks to Trevor for this one)
STCKCNTSTTS (Stock Count Status):
1 – Available
2 – Started
3 – Entered
SMRYTYPE (Summary Type):
1 – Calendar
2 – Fiscal
Account Indexes:
[Thanks to Ron Wilson for providing these!]
IVIVINDX – Inventory On Hand
IVIVOFIX – Inventory Offset
IVCOGSIX – Cost of Goods Sold
IVSLSIDX – Sales
IVSLDSIX – Markdowns
IVSLRNIX – Sales Returns
IVINUSIX – In Use
IVINSVIX – In Service
IVDMGIDX – Damaged
IVVARIDX – Variance
DPSHPIDX – Drop Ship Items
PURPVIDX – Purchase Price Variance
UPPVIDX – Unrealized Purchase Price Var
IVRETIDX – Inventory Returns
ASMVRIDX – Assembly Variance
PCHSRCTY from IV10200
[Thanks to Stephan Desmoulin for providing these!]
1 – Adjustment
2 – Variance
3 – Transfer
4 – Override
5 – Receipt
6 – Return
7 – Assembly
8 – In-Transit
—
Last Updated: Oct 20, 2023
Victoria Yudin 
May I request we add IV00112 Item Site Bin Master to this list? I feel it would be helpful for those trying to track-down/correct phantom allocations.
Thanks!
LikeLike
Hi Scott,
Great idea. Not too many companies out there are using bins, but for the ones that do, I can see this being a useful table.
Thank you for the suggestion,
Victoria
LikeLike
Hi Victoria, your website has been invaluable in helping learn the GP tables and details between the different modules. I have a question regarding the Price List window. When our team creates new items, the Default Selling Unite of Measurement and the Default Price List fields are blank in the price list window. This is causing orders to get held up in our EDI/API connections because those aren’t set. I had thought that the information is in the IV00108 or IV00107 tables and also checked on the IV00101 but I’m not able to find the correlating tables/fields that would population that data.
Would you happen to know? Any help would be greatly appreciated. Thank you!
LikeLike
HI Mike,
These are in the IV00101 table.
-Victoria
LikeLike
Thanks for the prompt response. Just to confirm, would that be the UOMSCHDL and PRCLEVEL fields?
LikeLike
SELNGUOM and PRCLEVEL 🙂
LikeLike
Hey Victoria,
I saw your pricing report but its set up on the standard pricing module. We are using extended pricing and I can’t for the life of me find where extended pricing stores the price methodology:
Net Price
Percent of List
Value off
Do you know what table this is in? I thought at first it would be EPITMTYP in IV10402; but this isn’t it. It also doesn’t use PRCMTHD as this returns 1 for all price sheets including those which use Percent of List.
Looking forward to the help in advance! Cheers,
Steven
LikeLike
Hi Steven,
I have not worked with Extended Pricing at all, so I cannot help with it. Perhaps this post will help you: http://kbase.icbconsulting.com/dynamics-gp/gp-extended-pricing-database-tables
-Victoria
LikeLike
Thanks for making this website Victoria. I reference it almost everyday at work.
On table IV00108, there are UOFM and UOMPRICE fields. Do you know where the Unit of Measure Schedule coming from? Is it supposed to be the UOMSCHDL from IV00101? It seems odd to put the grouping field on the product table because I’m not sure if that field is locked after an item is created in GP. Maybe they have some kind of cascading logic if the field is changed on product.
Thanks!
LikeLike
Hi Reg,
The UOFM is not the same as the UOMSCHDL. And the IV00108 table is not working with UofM Schedules – it is simply listing the price for each item/currency/UofM combination. So for example, if you have an item that can be sold as both an EACH and a CASE, you could enter a price for EACH and a price for CASE in the item pricing. Those would be stored as different lines in the IV00108 table. EACH and CASE are the UOFM values.
The UofM Schedule is assigned to each item (stored in the IV00101 table) and is what determines what units of measure are available for each item. The setup of the UofM Schedule is stored in tables IV40201 and IV40202.
Hope that helps.
-Victoria
LikeLike
Yup, this explains the issue I’m having! I’m making an SSIS package to integrate the price lists between GP and Dynamics 365. Interestingly, in Dynamics 365, for product prices, it is not allowed to specify a text value for a UOFM (using system fields). The system wants a UOFM and the group that the Unit of measure came from. This is fine if each UOFM on IV00108 corresponds to a single UOFM across any Unit Group in Dynamics. In my case, we have more than one ‘EA’ UOFM in Dynamics 365 in different Unit groups so the package wants to know which one to pick.
Anyway, looks like I’ll have to come up with some mapping logic to sort this out.
Thanks for the help!
LikeLike
Are you importing directly into the IV00108 table in SQL? If so, I would recommend prior data validation to make sure you’re not breaking any logic GP would impose when entering these manually. There is no built-in data validation when importing directly into SQL and GP may not let you use the items on transactions if this data is not clean. And you will need to import into table IV00107, as well.
-Victoria
LikeLike
Looking to confirm the Customer Items view would be the best location to identify the products that each customer purchases. If not, suggestions to get that data would be awesome. We are looking to create one record in outside database for each customer and the product it uses.
LikeLike
Hi Brian,
Customer Items is just what has been set up in the system in case you want to use alternate item numbers for your customers (as opposed to your item numbers). No reason to assume that each of these customer/item combinations has been sold. Also, no reason to assume that other customer/item combinations, that are not set up in Customer Items, have not been sold.
I would maybe look at this view, which shows that last time each customer/item combination has been sold:
-Victoria
LikeLike
Hi. I decided to transfer some item qtys to “in use” status. I did this because the items were not physically on hand and I am waiting for a sales order to show up for them. I didn’t want the items to appear available. I didn’t realize that the “in use” qty affects the on hand quantity. I want the items to show on-hand, just not available. What do you the best practices for this situation should be?
LikeLike
Hi Terrie,
There might be other ways, but one quick way you could show quantities on hand but not available is to put them on a dummy sales invoice. (Or on an order and allocate it.) If you don’t already have one, create an internal customer called STOCK or IN USE or maybe your company name and use that and put it into a batch called something like DO NOT POST so that it’s not inadvertently posted.
-Victoria
LikeLike
I have used this method too, and I usually combine it with a specific Invoice ID and add a Process Hold just to make sure it doesn’t get posted. 🙂
LikeLiked by 1 person
Wow, thank you so much for your fast response. I am so glad I found your blog.
Yes, I thought of creating a dummy sales order. So I guess I will do that. So what do people use the IN USE type for? I am the inventory control admin. I hope the fact that I moved items to IN USE , and then used the same inventory account number that it was transferred from – I hope this doesn’t cause probs on the accounting side. I have a few more questions coming for ya. My goal is to move away from using spreadsheets to record missing inventory make qtys in GP more accurate.
LikeLike
And thank you Janice. I like the idea of adding process hold. thank you.
LikeLike
Hi Terrie,
All those ‘other’ inventory buckets like In Use, Damaged, etc. are meant to be used when you want to track inventory quantities separately from what is on hand. For example, if you take an item out of inventory to use internally, you might move it to In Use, then move it back to On Hand once you’re done with it. Damaged might be items received back from customers that are being fixed, and once they are fixed they can be transferred back to On Hand. Of course some of this does not apply for all types of inventory and I find in real life most companies do not use the In Use, Damaged, Returned, etc., because they cause a lot of confusion for little benefit. However, there are definitely situations where these buckets can be useful if utilized properly.
No way to tell if there are any accounting issues cause by what you did without actually looking at the individual transactions. Typically, if you debited an account and credited the same account for the same amount, there should be no issue as there is zero net change.
-Victoria
LikeLiked by 1 person
Hi Victoria,
Do you know in which GP table(s) I can find the information on the Inventory Available to Promise Inquiry screen? I like to pull “Free Forward” information per each item. Is this field a calculated field and if I would like to pull this information on SQL do I need to link different tables and do a calculation? Please advise.
LikeLike
Hi Joyce,
I believe the information on this window is calculated on the fly, so it won’t be in any table. I am not positive what the exact calculations are,
-Victoria
LikeLike
Hi Victoria,
I am trying to set up GP, so I can type an item number on an invoice and the price automatically populates. Is the price data on the item maintenance table or unit price table? Any direction is appreciated.
LikeLike
HI John,
This will typically depend on how you have your pricing and customers set up in GP, as there are multiple options. This is the kind of thing I do not like answering without knowing anything about someone’s setup and needs, as it’s just as easy to set it up incorrectly and cause additional headaches and work. I would recommend getting some advice and training on this from your GP Partner who will hopefully be able to give you the best recommendations based on your specific situation.
-Victoria
LikeLike
Hi Victoria,
I’m trying to determine where the Item: Finished Goods checkmark on the Item Maintenance > Options card is stored.’
Also, do you have a list of BOM tables?
Thanks,
Sandy
LikeLike
I found the BOM tables above, but I can’t seem to find the table for the finished goods flag.
LikeLike
Sandy,
I do not believe there is a “finished goods flag” – I think you calculate that from other tables, like SOP and/or BOM.
-Victoria
LikeLike
Hi Victoria,
This is what I am looking for. Is it not stored in a table somewhere?
[cid:image002.jpg@01D4B3E8.4AD89700]
Thanks,
Sandy Dimopoulos
LikeLike
Sandy,
Your image did not come through…are you able to put it somewhere on the web with a link?
-Victoria
LikeLike
Please try this.
https://drive.google.com/file/d/18TGltCulsxXiZ11jwZcWRHOmKGy9SkGb/view?usp=sharing
Thanks,
Sandy Dimopoulos
847-457-2258
http://www.pharmedium.com
LikeLike
Thanks, that worked. I am pretty certain that is calculated on the fly and not stored in any table. I believe if there is an entry for the item in BM00111, then it will be marked off as a Finished Good on the Item Maintenance Options window. I know I have used that logic for reporting before and it’s not been an issue.
Hope that helps,
-Victoria
LikeLike
Thanks so much! Keep warm!
Thanks,
Sandy Dimopoulos
847-457-2258
http://www.pharmedium.com
LikeLike
Hi Victoria,
I’m trying to find the table that has the Annual Historical Usage from the Item History window. is there a table i can access to view this information?
LikeLike
Hi Jose,
It’s one of these, depending on the level of details you want to see:
IV30101 – Sales Summary History
IV30102 – Sales Summary Period History
-Victoria
LikeLike
Hello Victoria,
On table IV30200, what’s the difference between IV_Trxent and XIV_Trxent? I am assuming that IV_Trxent is a system generated batch source on any assembly, or material issued to a manufacturing order. The XIV_Trxent has to be a manual adjustment.
LikeLike
Hi Chase,
Typically an X in front of the bach source indicates that the transaction was posted without a batch. No other meaning. You can usually confirm this by looking at the bach number – if it’s a GP user ID, especially if it is lower case, that would be another indication of a transaction posted without a batch.
-Victoria
LikeLike
Hi Victoria,
I am updating the USCATVLS through SQL on items in IV00101, and I am using the values from IV40600 the category setup table. Do I need to update the items categories elsewhere aside from the IV00101 table? It has worked a few times now, and seems like it is ok… but you never know where else that information is
LikeLike
Hi Aaron,
Updating these in IV00101 should do it. I have done this many times with no issues. I always do data validation first to make sure the values are in IV40600, but it sounds like you are already doing that.
-Victoria
LikeLike
Hi Victoria, I often google (bing!) GP table names and when your site appears, I always start there, and I usually find the answer and don’t have to go further. You have provided a huge service to the community by taking the time to document and share information. Many Many thanks from me.
Lisa Williams
Velosio
LikeLike
Hi Victoria,
I’m looking at tables
IV10200 – Purchase Receipts (header)
IV10201 – Purchase Receipts Detail (line detail)
and I notice neither has U of M columns identifying how they are being received. Are items always received as Purchasing U Of M as identified on IV00101? If no, how can I identify which U Of M items are received?
LikeLike
Hi SpaceCommanderJ,
I believe these tables will always store quantities in the base unit of measure for each item. To see what U of M was actually on the purchase order or receipt you can check the POP tables.
-Victoria
LikeLike
Hi Vic, Would you let me know at what time does the IV00102 record get created?
LikeLike
Hi tarun,
My understanding is that a general – overall – record is created in IN00102 when an item is created. Subsequently, any time an item is assigned to a site an additional – site specific – record is created in IV00102.
-Victoria
LikeLike
Is there a good way to find the date an item went out of Stock? I’m surprised it isn’t in the IV00102 table. I imagine I’ll have to do some type of calculation. Love the site. First time poster.
LikeLike
HI Kyle,
Unfortunately GP does not keep track of this. Nor would it be easy to calculate. you need this on an ongoing basis, you might consider a SQL trigger on the IV00102 table.
-Victoria
LikeLike
Victoria,
Thanks. I figured that might be the case. I have a trigger in place now that will tell me this but I was hoping to look at some history.
Keep up the good work!
Kyle
LikeLike
Hi,
I’m trying to get the last cost/last receipt from IV30300 where DOCTYPE = ‘4’ but this code don’t work. Can someone help.
select ITEMNMBR, UNITCOST, MAX(DOCDATE)
from IV30300
where DOCTYPE = ‘4’
GROUP BY ITEMNMBR,UNITCOST
ORDER BY ITEMNMBR
thanks,
LikeLike
Hi Ricky,
In order to do that you basically have to take 2 passes at the data. First, determine the latest date that DOCTYPE 4 exists for each item in IV30300, then get the data from IV30300 for each item/max date combination.
Below is one way to do it. I left the columns unrestricted, with a select * because I wanted you to see all the data – it’s entirely possible that you have multiple receipts on the same date, so you may need to decide what to do in that circumstance if you’re only looking for one result per item.
select *
from IV30300 i
inner join
(select ITEMNMBR,
max(DOCDATE) MaxDate
from iv30300
where DOCTYPE = 4
group by ITEMNMBR) m
on i.ITEMNMBR = m.ITEMNMBR
and i.DOCDATE = m.MaxDate
and i.DOCTYPE = 4
Hope this helps,
-Victoria
LikeLike
Thanks!!! you are also so helpful.
ricky
LikeLike
Hi Victoria,
Love your site. Question concerning column PLANNERID in IV00102. We note that everywhere PLANNERID is defined in the database, it is defined as:
However, in our installation (not sure if a customization was made – we do not think so though) – the Planner ID in the Item Resource Planning window is restricted to 3 characters. When we load more than 3 chars directly into the table for a record, the display truncates to 3 chars. The Planner master table IV00110 is the same – and Planner maintenance also allows only 3 char entry – HOWEVER – it shows all 15 chars in list.
Is this standard behavior ? (we are GP 2010 V: 11.00.2044) Thank you for any guidance!
LikeLike
Hi Jason,
I see the same thing in my GP 2016, so it seems to be standard.
I would suspect that this field was panned for something else originally when the field was included in the tables and then when the user interface was created it was decided to limit the Planner ID to 3 characters. Alternately, it could be a bug.
For what it’s worth, in all my years of working with GP I have actually never seen anyone using this field. 😦
-Victoria
LikeLike
Victoria,
Thank you for the quick response! We use it extensively for our MRP / Planning to assign responsibility to Master Planners .
Jason
LikeLike
Jason,
That makes sense, I don’t usually work with MRP. If it’s important, it might be worth contacting Microsoft support about this, however, even if they determine it’s a bug they might not be too quick to fix it because it may not be a high priority.
Another option if you are looking to store more information might be to use Extender.
-Victoria
LikeLike
Hello, We are using the currency method of price levels and have one attributed to almost every one of our customers (we are a service company and pricing is dependent on system sizes so this is the best way we came up with to do it). I know how to enter quantity breaks in pricing, but we have a customer who sent us a PO for a certain amount for the first system and a second amount for each additional system. So if they have 1 system serviced, it is $95. If they have 2 systems, it is $95 plus $45. If they have three, $95 + $45 + $45. I cannot figure out how to do this in our current configuration. Is there a way to set this up? Thank you in advance.
LikeLike
Hi Staci,
I can’t think of a way to set this up with GP’s pricing only. One option might be to create new items for the services depending on whether it’s the 1st service or not. So for example – FIRST SYSTEM SERVICE has regular pricing ($95), SECOND SYSTEM SERVICE or ADDITIONAL SYSTEM SERVICE has different pricing ($45 each).
-Victoria
LikeLike
Something really strange happened today. I have a transaction that is in the GL but not in the inventory subledger. Its a PO receipt. How is this possible? It’s not in the inventory reconciliation but when I go to a form and lookup the PO it is there and shows received.
-Baffled
LikeLike
Scott,
That is very strange. Can you check to see if that PO number shows up in the POP10500 table?
-Victoria
LikeLike
Hi Victoria – working with Scott on this….there receipts are indeed included in POP10500 table – have quantities received into the proper location in inventory in that table. Simply not part of any inventory inquiry. Any ideas?
LikeLike
Also verified that the receipt is not part of the inventory tables IV30300 or IV10200. We are using these tables for custom queries and is how we noticed the problem.
LikeLike
Hi Ben,
In the POP10500 table – get the POPRCTNM for the receipt. Look for that POPRCTNM in the POP30310 table. What is the value in the NONINVEN column?
-Victoria
LikeLike
Value = 1
LikeLike
So this is a non-inventory item flag – troubling because items that we are receiving against a PO now have this flag. This is troubling.
LikeLike
I often see this happen when the PO is created before the inventory item is created, so it may be a timing issue. Are your Purchase Orders entered manually into GP? If so, this may also be a training issue – when creating a PO for a non-inventory item the user gets a message asking them if they want to create the item, this should alert them that they are entering a non-inventory item.
-Victoria
LikeLike
Victoria
This is happening with manually entered POs and on occasion the item may be new but for the most part these are existing items that have been processed by the system correctly in the past. If I look in Item Maintenance the item type is listed as “Sales Inventory”. I’m not sure if this is the appropriate filed to be referencing in this situation. Additionally I want to point out as it may be relevant, that the debit to INV is going to the wrong GL acccount. In item set up I can see that it should be going to INV control but the debit ends up going to INV General.
LikeLike
Scott,
The GL account for a non-inventory item will be taken from the Vendor default Purchases account (or the company default posting Purchases account if the vendor one is blank). 99% of the time that will be different from the inventory account, so that makes sense if your items are not recognized as inventory items.
I wonder if maybe what the users are entering for the item number on the PO’s is not identical to what is in the GP inventory module. In the past I have seen situations where there are special characters present – for example when something is copied from Excel sometimes the ‘carriage return’ is copied into the ID. This is impossible to see in the user interface when just looking at the items, you would have to do some testing.
As a start – enter one of the items you are having a problem with onto a new Purchase Order. Don’t use a lookup, manually type it in. First, if GP does not recognize it as an inventory item, the Description field on the line should be blank. You can also go to the Purchasing Item Detail Entry window and at the bottom left will be a field for Item Type – what does that say? If it says Sales Inventory then this is not the case and something else is going on when the PO’s are being entered.
Also, there is an option you can turn on that will give you a pop-up every time you enter a non-inventory item – on the Purchase Order Entry window under Options select Add Item – this is a toggle, you want it to have a check next to it. I believe this is a per user setting, it will be saved but only for the user you are logged into GP as.
One final note – if this is an issue with a lot of items, you may want to get your GP Partner involved and have them look at what might be causing this and help you find ways to fix this for the future. And also help you find the best way to clean up the issues caused by the prior PO’s with this problem.
-Victoria
LikeLike
A thought relating to this . . . I encourage people to enter the line item it the p.o. by doing a lookup and then selecting. That way if it is not listed, it is a flag. Either the item has not yet been entered or has not been assigned to the vendor. This seems to have eliminated the accidental entry of non-inventory items.
LikeLike
Michelle – great idea!
-Victoria
LikeLike
Hi Victoria,
I have a process question – that I am hoping you can help me with. We recently started using Great Plains to cycle count our inventory for specific sites/racks/bins/items/lots – we are using the Stock Count Schedule to do this along with Panatrack and Scan Guns. We have a consultant that is telling our Inventory Team that we should be using Available Quantity as the value we compare against rather than on hand quantity. I completely disagree with this approach and believe that you have to look at on hand quantity – that is the way that GP is set up and that is the way that Panatrack works with GP. My question for you is, is there validity to the approach of basing your cycle count on the Quantity Available data rather than the On Hand – my understanding is that you count on hand because that is quite literally what should be in the bin for that lot. Your process should be to freeze activity for that area of your inventory by not putting in any adjustments or physically receiving material into that location until you are done counting. What you have allocated should still be accounted for after you’ve processed your cycle count the same as it would have been before except in the case where it will cause a negative variance and in that case you need to research why you have less inventory than you have allocations for, those should have gone to a back order.
I appreciate any feedback you can provide
LikeLike
Hi Gail,
Yes, in general I agree, you should be looking at Oh Hand, since that’s what should match what is in your inventory. The only reason to use Available Qty would be if what’s allocated is actually not on the shelf anymore. That is theoretically possible, so I would clarify with your consultant if this is what they are expecting and why they are recommending using Available Qty.
-Victoria
LikeLike
What sql table(s) is/are the Item Extended Attributes link to?
particularly the Unavailable Reason field
LikeLike
Vinh,
I am not familiar with a window by that name – is it possibly from a 3rd party product or customization? Or maybe an Extender window?
-Victoria
LikeLike
Hi Victoria (or anyone else who may know this answer!),
I am trying to update many Reorder Points and Restock Levels at once (about 1500 items in 20 different sites). If it makes a difference, these are items that currently have a reorder point/restock level of 1, or 2, etc., but I need to change them all to 0.
I was planning on using Integration Manager to do this but it doesn’t look like IM can make changes to the Item Quantity Master table, which I think is the only table where I can make these changes.
I looked into using the Table Import function but I don’t know if that will work, either.
Do you know of a way to do this? I have a list of all of the item numbers, so it’s a matter of changing the ORDRPNTQTY and ORDRUPTOLVL to zero for all 1500 items at all 20 sites…and I certainly don’t want to do it manually!
Many thanks,
Katie
LikeLike
Hi Katie,
I believe Table Import will only import, not update existing data.
Unless you have customizations or add-ons using them, there should be no problem with changing these values directly in the SQL tables. That would be the most efficient and painless way to do it. If you are not 100% comfortable doing this, I would recommend getting some help with that either from your GP Partner and/or your IT department.
-Victoria
LikeLike
You could do it with an update query. Import the item numbers you want to change into a separate new table then use an “in select” statement so that it only updates those item numbers. If possible update it in a test version of your company first to make sure it works as you want and doesn’t break anything first.
Like Victoria Said if your not comfortable doing that though check with your GP partner or IT department.
LikeLike
Vic,
You don’t necessarily need a separate table, you can do it all inside the update SQL query. 🙂
-Victoria
LikeLike
I know that but she said she had a list so i thought that route would be easier.
LikeLike
Yep, you can still do that without a table. Might be a good topic for a blog post. I will try to do that next week. 🙂
-Victoria
LikeLike
Thanks so much Victoria! I am indeed new at SQL, so I’ll be reaching out to my IT dept. for some help. 🙂
LikeLike
Hi Victoria
Is there a way to setup a default vendor for a customer? Example
Item PS12345 default Vendor is ABC Company for 90% of my customers. I have other customers who also buy PS12345 BUT i need to purchase it through DEF Company. We have a lot of automation so we don’t want to manually change the vendor on the order from the customers who need to have the item purchased from DEF Company
Thanks
Vic
LikeLike
Hi Vic,
I am not aware of any way to do this without some kind of customization.
-Victoria
LikeLike
Can you tell me what tables make up the Item Resource Planning Maintenance window?
LikeLike
Hi Jay,
I believe all the fields are in the IV00101 table. Might be something in IV00102, but I doubt it.
-Victoria
LikeLike
Thank you so much!
LikeLike
Hi Jay, You probably already found what you need, but in case anyone else can benefit from this…Reorder Point (ORDRPNTQTY) and Restock Level (ORDRUPTOLVL) are on the Item Resource Planning Maintenance window but I just found those fields in IV00102.
LikeLike
Victoria,
We recently added 117 new inventory items in Test for new invoicing system in CRM. I have verified these records in IV00101,2,5,6,7,and8. These were all entered by copying existing items so that gl accounts and other variables so did not need to be entered by hand. My question is can I pull these records into an update script to apply against Live db, after backup? Test is copy of Live with these records added and about 2 months out of date. These are GP 2015 instances.
LikeLike
Hi Steven,
I would not recommend this without a huge amount of testing and data verification. In this case, I suspect the amount of testing and data verification you’re going to have to do to ensure this all goes properly would likely take more time than creating these 117 items manually. Just my 2 cents.
-Victoria
LikeLike
Victoria,
I bow to your experience, knowledge, and wisdom. Just hoping to avoid going back to verify after staff finishes work. I did this myself in Test as I was writing new entities in CRM and stored procs for use in scribe.
Thanks again,
Steven
LikeLike
Dear Victoria
Hello
I would appreciate you could help me with something
While Using Order Fulfillment in SOP the Qty On hand remain the same even after printing the Fulfillment order
My question is does fulfillment change the on hand qty or no and why if it does not change on hand qty
Thank you in advance
Mohamed
LikeLike
Mohamed,
In Dynamics GP the On Hand Qty only changes once an invoice is posted. The Qty Available does change as soon as you fulfill an order.
-Victoria
LikeLiked by 1 person
Victoria,
Thank you for the valuable information.
I’ve been trying to get what I thought would be a simple list of item numbers and serial numbers by receipt number put together. I started with SmartList Builder and then moved back to SQL to see if I can get the right table combination with no luck.
So far, I’ve tried IV00200, IV10200, IV10201, and IV30400 and I can’t seem to figure out how to combine these properly to provide the desired result.
I would appreciate if you could give me an idea of the best tables to use and how to link them.
Cheers,
Steven
LikeLike
Hi Steven,
Are you talking about Purchasing Receipts? If so, I would recommend using the POP tables: POP30300, POP30310, POP30330.
-Victoria
LikeLike
Thank you Victoria,
After two hours of no luck, I had the inventory clerk who made the request sit down with me to show me her data-entry steps. As soon as she said click on the Payables module the dots connected.
A mental note was made to not make assumptions and adjust my procedure for any future changes!
Cheers,
Steven
LikeLike
Steven,
Good idea – always very important to speak to the people actually entering the data to determine where it will end up in the tables.
-Victoria
LikeLike
Hi Victoria!
I need to remove all the current vendor item numbers in the IV00103 table and replace them with a corrected vendor item numbers. Would i be able to just truncate that table and write an insert sql script with the corrected ones. There is no inventory issues as they do not stock anything it is all drop ship.
Vic
LikeLike
Vic,
If the data is already in the IV00103 table and you simply need to update one of the fields (vendor item number), I would recommend updating just that field. Deleting and repopulating the data requires a lot more effort and error checking, IMO.
-Victoria
LikeLike
I am sorry i forgot to mention that there are existing records in there that are for the same item. Like I have an item number 101034561 the vendor item is 101035461 then they added another vendor item with an item number PSICQ5950A and the vendor item number is 101065461. See they set this all up wrong to begin with by saying we don’t need to use vendor item numbers. Then they decided recently to start using internal item numbers and vendor item numbers. They didn’t call me and just setup all these so the IV00103 table is filled with duplicates where the item number and vendor item number are the same
LikeLike
Vic,
In that case, I am not 100% comfortable simply suggesting a fix in the tables without actually seeing the data and checking other related tables. Part of the issue is that a lot of the data will have to be validated if you simply import it into the table. Another part is that there are other tables that have data linked to the IV00103 table, IV00102 just to name one, but I believe there are others. In short I would not recommend simply fixing this data directly in SQL.
-Victoria
LikeLike
Victoria, May one write a sql update query to modify the ORDERPOLICY and ORDRPNTQTY in the IV00102 Table?
LikeLike
Hi Kevin,
Yes, if you are using GP out of the box, you should be able to update these without a problem directly in SQL.
-Victoria
LikeLike
Awesome! Thank you! Wasn’t sure if any other tables would be involved.
LikeLike
Hi Victoria! Where I can find the flag that shows me if the item is STOCK or NON-STOCK. I was looking at IV40400, but nothing seems to give me a clue.
I will appreciate your help.
LikeLike
Hi Yuliana,
By “non-stock” do you mean “non-inventory” using Dynamics GP terminology, or are you referring to something else?
If yes, then you will not find “non-inventory” items anywhere in the inventory tables. You can see these on Sales and Purchasing transaction lines, though. For example tables SOP10200 and SOP30300 have a column called NONINVEN. This column will have a 0 for inventory items and a 1 for non-inventory items.
If you mean something else, can you please give me some more detail?
-Victoria
LikeLike
Hi,
I was wondering in IV00301, what the RCRDSTTS choices are? My current record has a value of ‘1’. Was needing to know what that value means and other choices that may be available. I was needing to close that Lot.
Any information would be appreciated.
Thanks.
LikeLike
Hi April,
I see values of 0, 1 and 2 in the sample data for that field. Unfortunately I am not seeing any information on what those values mean anywhere, sorry. If you get the answer, please write back to let me know and I will post it on the blog for everyone to benefit from.
-Victoria
LikeLike
0 is for a new attribute record on an unposted (WORK) transaction (e.g. on an Inventory Increase Adjustment). Once posted the RCRDSTTS will change to 1. The RCRDSTTS will be 2, when an ad hoc lot number is used, perhaps on a transfer or decrease transaction.
0 = WORK Record
1 = LOT Record
2 = Ad Hoc Record
LikeLike
Thanks Jeremy!
-Victoria
LikeLike
Hi Victoria, is there a way to track the inventory transfer entries in the IV tables to the root. In other words, if I have an entry with Document Type # 3 (Inventory transfer), can I know what type it was back at the source (was it also a transfer, sales invoice, adjustment, etc…)? The goal is to trace the transfers all the way to the root. Thank you!
LikeLike
Hi Mostafa,
Not sure what you mean. An inventory transfer normally can only be initiated by an inventory transfer transaction. It would never come from a sale or an adjustment – those would be other Doc Types. It’s possible that you have some kind of customization or 3rd party product that causes other events to create inventory transfers. In that case, you would need to talk to the developer(s) of that to get the answer to your question.
-Victoria
LikeLike
Thanks for the quick reply! Maybe I didn’t formulate my question clearly. So let’s say we have a Doc Type 3 entry showing that Item A was transferred from Tank 1 to Tank 2. Is there a way to see what the Doc Type of Item A was before the transfer (when it was in Tank 1) and get all the details?
Thanks again.
LikeLike
Hi Mostafa,
You mean the entry that ’caused’ you to have to make the transfer? I don’t think there would be a definitive way to track this, since the entries are not related in any way. So the generic answer is no. The longer answer is that you may be able to derive this by looking at your data specifically and making some assumptions and educated guesses. Coding that could be a challenge depending, again, on your data. Sorry not to have an easy answer.
-Victoria
LikeLike
Hello Victoria,
Is there anyway I can find out who created the item in Item Maintenance(IV00101) table ?
LikeLike
Hi Suresh,
Dynamics GP does not track this information. So unless you set up something separate the track this, the answer to your question is no. 😦
-Victoria
LikeLike
We track BOM edits which usually reveals the creator for manufactured parts. A weekly report also lets everyone know about BOM changes.
For purchased parts, maybe the buyer field would help determine the creator?
BOM edits single part: SELECT PPN_I, CPN_I, BOMSEQ_I, POSITION_NUMBER, CHANGEDATE_I, QUANTITY_I, USERID
FROM BM010115
WHERE PPN_I = @PARTID
BOM edits since date: SELECT PPN_I, CPN_I, BOMSEQ_I, POSITION_NUMBER, CHANGEDATE_I, QUANTITY_I, USERID
FROM BM010115
WHERE CHANGEDATE_I > @BeginningDate
LikeLike
Thanks for responding, I am trying to determine which way is more accurate to use in my SQL view. Should I use the field qtyonhnd in IV00102 that gets updated every time a transaction gets posted, or should I use the formula of qty received – qty sold from IV00300.
Since this us for a report running a reconcile for every item we suspect may be off is not a viable solution to having inacurate data in the report. I need to make a decision of which way of displaying the quantity in hand for an item is most likely to be accurate every time the report is refreshed when the user opens it to check inventory levels.
Thank you again for your assistance, your site and books have been an invaluable GP resource!
Best regards
Gail
LikeLike
Gail,
I would think using QTYRECVD – QTYSOLD in IV10200. That’s going to be ‘more’ correct than the Quantity on Hand in IV00102 is something goes wrong during posting.
-Victoria
LikeLike
Hi Victoria,
I am trying to build a Bin Lot Inventory Quantity report, and I need some help understanding the difference between the QTYONHND value in IV00102 and the Calculation for IV00300.QTYRECVD – IV00300.QTYSOLD = QTYONHAND I would have thought that these 2 amounts should have been the same, but they are not. Could you explain where and how the IV00102 Qtyonhand value is calculated?
LikeLike
Gail,
Quantity on Hand is not technically ‘calculated’. It’s a field that is updated every single time a transaction is posted that causes a changes the quantity you have in stock of an item. It’s entirely possible that some of these are not correct for various reasons like posting interruptions, incorrect data manipulation, etc. If you suspect a particular item is not right, you can try running Inventory Reconcile for that item to see if anything changes.
-Victoria
LikeLike
Gail,
Victoria is correct (as usual)! You can also try looking at IV10200 table for QTYRECVD – QTYSOLD. It seems also that GP is very prone to (transaction) “disruptions”…that why the Inventory Reconcile routine was created.
Regards
LikeLike
Hello Victoria, thanks for your tireless efforts keeping all of us on point with GP and its best practices. I have an end-user request to “Remove the code that deletes the component from inventory (IV00200) table.” I would like to know what are the ramification of taking such a drastic step. Thank you!
LikeLike
Hi Tucker,
Sorry, I am not sure what this means…how would you “remove” this code?
-Victoria
LikeLike
Through customizations we have been able to mirror the Kit process in GP. As you know, with the Kits there is no serialization, however the components of a kit is serialized. Well…through modifications we have been able to serialized both the kit and the components via a process call Pack. When the Pack is returned to us, it is causing duplications because both the Pack and the components of the pack are updating in Inventory. So, to modify the inventory process further a request to modify the code so that inventory is not updated has been submitted.
-Tucker
LikeLike
Hi Tucker,
I don’t know if it’s possible to solve this in blog comments. I would recommend sitting down with your GP Partner or whoever is creating these modifications for you and walking through your needs and what is possible in GP in detail.
If it were me, as a start, I would recommend not using kits, but using the “light” bill of materials process that comes with the Inventory module to assemble your finished products. That might avoid all of the issues you’ve mentioned and not require any modifications/customizations.
-Victoria
LikeLike
Good Afternoon,
We are using GP 10 (still 🙂 ) and I need to ‘duplicate’ all items in IV00103 vendor item table with a new vendor . We have over 3000 SKUs so the process is daunting. If I create a file containing the new item/vendor combo) can I use a SQL insert to get records into the table, or are there other table associated that I need to worry about?
Your website has helped me out of lots of jams over the 6 years we have been using GP . It is an invaluable resource!
Thanks In Advance
LikeLike
Hi Cindy,
I have not done this myself, so I am not 100% certain it will work. You could try it with one item and test using the item/vendor combination in GP to see how it goes…or post a question on the Dynamics GP forum to see if anyone has done this in the past.
-Victoria
LikeLike
Hi Victoria, thank you for so much valuable info in this blog!
I need to update the “Comment 1” field in the vendor table PM00200. I tried Integration Manager, but it is only attempting to insert rather than update, thus reporting an error (Object XXXX… already exist — cannot insert)
Do you suggest I update directly from SQL? Or is there a way to get IM to update instead of insert?
Thank you!
LikeLike
Hi Steven,
Since this field is in a master record only and does not need to be validated, I don’t see any reason you cannot update it directly in SQL. I have done this before with no problems.
-Victoria
LikeLike
I also updated it directly in SQL. I didn’t use integration manager or anything for that. It actually is smoother doing it direct.
LikeLike
Thank you Victoria for your quick response!
I ran the update script for my first vendors. I worked perfect! However, I then ran a second script for my other group of vendors but that froze GP… Now now of my users can access GP. Help!
LikeLike
Hi Steven,
Updating data, whether in SQL or Integration Manager should not be done when users are in the system, nor should it be done without a backup. That’s general best practices and should be followed for any future updates. At this point I would try to force quit the SQL update and see if that fixes the GP access issue. If not, you will likely need to call Microsoft support or your GP partner for immediate help.
-Victoria
LikeLike
I was working out of my test company. I did not think this would have affected the other companies… Beginner’s error…
I had not committed the update and I guess this caused the hiccup. My systems are running smoothly again.
Lesson learned, I will take your advice of best practices. I will run the actual update to my production companies when nobody is logged in and having backed up the database,
I know you get this a lot, but your are awesome!!!
Thank you!!!
LikeLike
Steven,
Gotcha, glad you were running this on a test company and not live! Unfortunately, when you’re doing a lot in SQL, it will sometimes impact the entire environment. If you’re doing a lot of this type of stuff, you might want to consider setting up a separate development server where nothing will impact the production environment. 🙂
-Victoria
LikeLike
What update script are you running? Is it with Great Plains or is it something that you wrote? Can you tell me what you mean by your second set of vendors?
LikeLike
Steven, If this is a new integration you just built, it defaults to “Insert Only”. You need to change the Properties>Destination Edit Mode to “Update Only” or “Insert and Update”.
LikeLike
Thank you David for tip!
LikeLike
Hi Victoria, I have come across your website looking for answer to many issues and it’s always been incredibly useful, thankyou 🙂
We currently change the item type to discontinued for discontinued items, as expected. What we thought would be that it would only allow sales documents to use the quantity on hand, but it does not prompt the user in any way that the item is discontinued. They can order any quantity that turns into a back order.
Inactivating the item prevents them from putting it on a sales transaction completely, discontinuing the item but allow sales of discontinued items results in backorders.
Is there any other solution?
Thankyou 🙂
LikeLike
Tania,
I see you’ve got this message on a number of forums already, the only other advice I can think of in case you have not tried this yet is to uncheck the Allow Back Orders option on the Item Maintenance Options window for your Discontinued Items.
-Victoria
LikeLike
if you delete the price index on the item, the part number cannot be used in order entry
LikeLike
Hi Victoria, by the way I love your table fields index.
I am doing a new stock count schedule using the Mass Add. I have noticed that not all items were selected from the Mass Add using the filter by Site ID. Is there a field somewhere that indicates that this item should be included in future count ?
Digging a bit through these items I think it’s because there were no purchase but only variances transactions. It might also be because these items were initially imported at the beggining of the GP deployment.
Thanks
Pat
LikeLike
Hi Pat,
The few reasons I can think of for items not to be included with Mass Add are:
If none of those are the case, you might need to ask your GP Partner or Dynamics GP Support for help on this so they can look at your data together with you.
-Victoria
LikeLike
Hi Victoria
We had most of our inventory duplicated last week in another location in error (don’t know what caused that to happen)! Luckily only 4 items were used in transactions before I was able to delete the location from each item. Most of them I did with a DELETE in table IV00102, because there were thousands of items. For the 4 items that had inventory adjustment transactions posted, I used the Remove Transaction utility first.
Now, however, for these 4 items, inquiring in Item Transaction Inquiry or Item Stock Inquiry causes GP to hang.
I’ve tested removing records in these tables IV30100, IV10200, IV10201 AND IV30500, IV30300, I’ve run Check Links on all of the Inventory tables and reconciled Inventory but it still hangs in the inquiry screens. , The (wrong) location doesn’t show up in Item Quantities Maintenance for these 4 items.
Any other suggestions?
Colleen
LikeLike
Colleen,
As to what could have caused it – it’s pretty easy to go into the Site Maintenance window and assign all items to a site, takes just a few clicks. You might want to make sure most people do not have access to that window.
As to how to fix the issue you’re having – unfortunately, removing data directly from tables and removing history can have some very unpredictable results. It’s really impossible to tell you what to do without a lot of additional analysis of your system and data. I would recommend starting a case with Dynamics GP support – they will likely have you do a bunch of logging to determine what exactly is going on when GP hangs to help resolve this.
-Victoria
LikeLike
Thanks for the reply, Victoria. We’ll look into your suggestions.
– Colleen
LikeLike
Is there any way to get an ID number related to the Price Lists in GP? like an identifier of sorts,
LikeLike
Chris, not quite sure what you mean…usually everything in GP has an ID. Can you please clarify? Also, are you using standard pricing or extended pricing?
-Victoria
LikeLike
Hello, where does the SOP10200.UOFM comes from which IV table?
LikeLike
CRC,
The UofM here is what the user types in on the Sales Transaction Entry window.
-Victoria
LikeLike
As per my understanding or at least in our system it comes from IV00108. However, It can be a result of our customization because we never type UofM in Sales Transaction Entry window.
LikeLike
Zafar,
I believe out-of-the-box what defaults into the Sales Transaction Entry window for the UofM is the Default Selling UofM stored in the IV00101 table in field SELNGUOM. However, the user can override this on each line if they want. 🙂
-Victoria
LikeLike
Hi Victoria!
Thanks so much for the quick reply. I recreated a transaction in the test environment and did see that the serial number will be placed back once document is posted.
Thanks again.
Denise
LikeLike
Hi Victoria,
When a serialized item is being returned, how do you make that serial number available in Great Plains again
LikeLike
Denise,
When you enter the return, if you put the quantity back to On Hand, the serial number should automatically be available again once the return is posted. Is that not happening?
-Victoria
LikeLike
Hi Victoria,
First, thanks for all the information here. Your site has been immeasurably helpful.
I’m currently assembling a view for my organization so they can see the price list, which I would like to include price level description. I could not find that anywhere on the commonly used tables above.
I found the data to be located at table IV40800, and I thought I’d share it here in case any other users run into the same problem I did or if you would like to include it in the list at the top of the page.
Thanks again!
LikeLike
Thanks Luke!
Good idea – I added IV40800 to the list. 🙂
-Victoria
LikeLike
hi Victoria,
I need to update quantity on hand with over thousand items to GP db. Do you have any suggestion to do this with easiest/fastest way? I can use the sql query to update IV00102 table but heard that is not a recommended way. please let me know your thoughts.
thanks,
Aaron
LikeLike
Aaron,
Updating the IV00102 table is absolutely not recommended and will cause you data issues. The best way to do this is to import an inventory transaction using something like Integration Manager.
-Victoria
LikeLike
hi Victoria,
I installed the Integration Manager already, but I could not find quantity on hand field to match it when select the Destination from the IM tool in GP. please let me know which adapters and/or destinations I need to pick for this.
Thanks for your input.
Aaron
LikeLike
Aaron,
You want the Inventory Transaction destination. Basically, you will be importing a transaction to bring the inventory level to what it should be. So, if it’s currently 5 and should be 7, you will import a qty of 2. If the current quantity in GP is 10 and should be 7, you will import a qty of -3. For increases you will also need the correct prices for each item.
-Victoria
LikeLike
Hi Victoria,
Can you tell me which table holds Internet information for an Item? In the inventory Maintenance window the second field is for the description – at the end of the field there is a button that expands a window for internet information and user defined fields. Need help finding that info.
Thanks!
LikeLike
Hi Kevin,
All internet information (for items, customers, vendors and company records) is stored in the SY01200 table.
-Victoria
LikeLike
Thanks Victoria! Great help!
LikeLike
Hello Victoria,
Just discovered your site; a GP newbie’s wish come true:) I am looking for a book or resource that can get me started from the ground floor with GP’s eConnect API’s. Everything I’ve come across on the net thus far has been bits and pieces meant for people already familiar with this technology. Any help on locating a resource would be greatly appreciated.
Thank you,
Ray
LikeLike
Hi Ray,
Unfortunately, I don’t know if anything like that exists. 😦 I will post back if come across anything.
-Victoria
LikeLike
No book I know of, but the DynDeveloper.com site was good for me when I started. Small monthly fee, I think…
LikeLike
Hi Victoria,
First post on your blog. Thanks a lot for sharing your incredible experience.
I actually need to fine-tune my SQL view to display the complete price list, to be able to import it into another software.
We use 3 currencies (CAD, USD, EURO) and many UOM.
I would like to only have 1 line per item. Currencies and UOM should be available by rows.
It’s not really a problem for currencies, But what is the solution for UOM ?
My following SQL view is working for currencies, but each Unit of Mesure is reported to another line. Maybe adding a condition which add a postfix to the item if the UOM is different than “one”, as “-x” (-5, -ft, -m for example) should be the solution ? How I can do that ?
Here is my temporary SQL View …
select h.USCATVLS_3 Model,
h.ITEMNMBR Item_Number,
h.ITEMDESC Description_Eng,
h.PRCHSUOM U_of_M,
d.UOMPRICE Price_CAD,
e.UOMPRICE Price_US,
f.UOMPRICE Price_Euro,
h.PriceGroup R_List,
h.STNDCOST STD_Cost
from IV00101 h
inner join IV00108 d
on h.ITEMNMBR = d.ITEMNMBR
AND d.CURNCYID = ‘Z-C$’
left join IV00108 e
on h.ITEMNMBR = e.ITEMNMBR
AND e.CURNCYID = ‘Z-US$’
left join IV00108 f
on h.ITEMNMBR = f.ITEMNMBR
AND f.CURNCYID = ‘Z-EURO’
inner join SY01200 i
on h.ITEMNMBR = i.MASTER_ID
Thanks for your help
Ben
LikeLike
Ben,
You’re looking to get only one row per item in the results? If so, instead of joining the IV00108 on the currency ID, also add a UoM to it…so something like:
inner join IV00108 d
on h.ITEMNMBR = d.ITEMNMBR
AND d.CURNCYID = ‘Z-C$’ and d.UOFM = ‘EACH’
You will have one of these for each unique combination of Currency and UofM. Maybe not the most elegant, but it should work. 🙂
LikeLike
Thanks Victoria
But, I cannot use “EACH” as AND condition, because I can’t list of Unit of Mesure for each product.
Here is another approch of my SQL view, which works perfectly,
But I would like to obtain currencies by columns instead of rows. So with the following example, I should obtain 2 rows (instead of 8) and and 8 columns (instead of 6).
This item has only 2x UOFM, but some items can have more than 10 UOFM…
The actual result of the view is like :
Model Item Desc. Curr UofM Pr_C$
Dyn 44-55 Desc01 Z-C$ 5 44
Dyn 44-55 Desc01 Z-C$ one 8
Dyn 44-55 Desc01 Z-H$ 5 47
Dyn 44-55 Desc01 Z-H$ one 9
Dyn 44-55 Desc01 Z-E$ 5 34
Dyn 44-55 Desc01 Z-E$ one 6
Dyn 44-55 Desc01 Z-U$ 5 41
Dyn 44-55 Desc01 Z-U$ one 7
I would like to obtain :
Model Item Desc. UofM Pr_C$ Pr_H$ Pr_E$ Pr_U$
Dyn 44-55 Desc01 5 44 47 51 64
Dyn 44-55 Desc01 one 8 11 15 18
Here is my view :
SELECT IM.USCATVLS_3 Model,
IV.ITEMNMBR Item,
IM.ITEMDESC Descr,
IV.CURNCYID Curr, — no more necessary
IV.UOFM U_of_M,
IV.UOMPRICE Price_C$,
–###?? Price_H$, — ??
–###?? Price_E$, — ??
–###?? Price U$ — ??
FROM IV00108 IV
LEFT OUTER JOIN
IV00101 IM
ON IM.ITEMNMBR = IV.ITEMNMBR
LEFT OUTER JOIN
IV00105 IC
ON IC.ITEMNMBR = IV.ITEMNMBR
AND IV.CURNCYID = IC.CURNCYID
LEFT OUTER JOIN SY01200 IW
on IM.ITEMNMBR = IW.MASTER_ID
Thanks for your help…
Ben
LikeLike
Ben,
I took out the tables links you’re not using for now, just to simplify. Try something like this:
SELECT IM.USCATVLS_3 Model,
IV.ITEMNMBR Item,
IM.ITEMDESC Descr,
IV.UOFM U_of_M,
sum(case when IV.CURNCYID = ‘Z-C$’
then IV.UOMPRICE else 0 end) Price_C$,
sum(case when IV.CURNCYID = ‘Z-US$’
then IV.UOMPRICE else 0 end) Price_US$
FROM IV00108 IV
LEFT OUTER JOIN
IV00101 IM
ON IM.ITEMNMBR = IV.ITEMNMBR
group by IM.USCATVLS_3, IV.ITEMNMBR,
IM.ITEMDESC, IV.UOFM
-Victoria
LikeLike
Victoria,
You are the best ! Exactly what I expected. Working perfectly. Thanks very very much.
Last thing for you : I need to add another item description (other langage), which is located to the SY01200 table, field INETINFO.
This field is a ntext field. So If I add it to my view and try to add the GROUP BY command including “IU.INETINFO”, I obtain the message “Msg 306, Leve; 16, State 2… The text, ntext and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.’
Seems to be very clear, but can you suggest me a solution to complete my view ?
Thanks a lot again
Benjamin
LikeLike
Hi Benjamin,
I think you should be able to use something like the following to convert it to a varchar:
cast(IU.INETINFO as varchar(500))
The 500 is just a suggestion, you can make that bigger or smaller, as needed. Use that in both your select statement and the group by. Please let me know if that does the trick.
-Victoria
LikeLiked by 1 person
Hi Victoria,
It was the solution. I now just finished my view which work perfectly. It was not be possible without your precious help.
A big thanks to you.
Ben
LikeLike
Hi Victoria – thanks for all you do for the community. I have a business case where I need to bypass FIFO perpetual for certain instances where i receive product at no charge and need to try an assign that cost layer to a specific sales order. Any solutions you are aware of? Thanks again
Jason
LikeLike
Hi Jason,
The only way I know of to ensure a specific cost layer goes on a particular sales invoice or order is to use serial numbers or lot tracking on the items. It’s a bit of a pain to switch to if you have the item on a lot of open transactions, but it works beautifully – we do this in our production GP environment for things like maintenance renewals, which are the same item, but a different cost and price each time.
-Victoria
LikeLike
Thanks for the quick reply. I agree that works well as I use lot tracking for a similar case where I’m selling the same item for multiple costs. However, in this case, I very rarely will sell the items for a cost other than current cost. I will have to test further to see if I can automate or bypass the tasks that go along with have items lot controlled like assigning the lot at receipt and fulfilling orders.
Thanks again!
LikeLike
We are trying to change the inventory document number of unposted transactions via sql, since there was quite a volume of transactions that were encoded with an incorrect document number. Aside from IV10000 and IV10001, are there any other tables to update? And what could be the effect of this change in document number after posting? Thank you.
LikeLike
Kim,
There could be another dozen tables that you would need to update, it all depends on what features of GP you’re using and the specific detail of your transactions. After posting, there would be many more tables involved. I would hesitate simply giving you a list of tables, as I would want to look at the data itself before giving advice like this. If possible, you might be better off deleting the transactions and importing or re-entering them. If this absolutely has to be done in SQL, I recommend that you work with your GP Partner and/or Microsoft Dynamics GP Support to help you with the changes. That way they can look at your data and confirm what needs to be done.
-Victoria
-Victoria
LikeLike
Hi Victoria,
I would like to add a column with indicate the latest transaction for each item without adding any new rows (can be more than one transaction for the same latest day,..)
I need to link my view to the SOP30300 table
Is there a very simple way to do that ?
Thanks for you help
Ben
LikeLike
Ben,
Sorry, what are you looking to add this to?
-Victoria
LikeLike
Hi Victoria,
I first want to thank you for your blog,
all the precious informations there is unvaluable.
I’m struggling with GP2010 Item Classes,
The problem is when I update the ClassId from the SQL, the business logic is not applied (currency, account, etc…)
Do you know if there is a stored procedure to call or something else to do to force GP to reapply the Item class ‘template’ on items?
Sorry if this appear twice, something happens the first time… :-S
Thanks a lot!
LikeLike
Hi Francis,
I am not aware of any ‘automatic’ way to do this from SQL. You could try to run a SQL profile to see everything that’s called when you roll down settings from a Class, but it’s possible there is not just one stored procedure to call. And you would really have to try changing every single setting to make sure you’re seeing everything.
Most of the settings that can be changed from the class after an item is already in place should be changing values in the IV00101 table. Depending on what exactly you’re looking to do, it might not be so bad to write individual update scripts in SQL for each field. However, I would be EXTREMELY careful about validating data, testing, and making sure you’re not changing anything the GP user interface would not allow you to do, as you could cause some serious damage to your data this way.
-Victoria
LikeLike
Victoria,
I will retry to run the profiler with every possible options combination to find out what is really happening.
I hope it is not something that is done in the code of the GP client user interface…
Thanks again for your time,
Francis
LikeLike
Would you happen to know a way to “hide” newly created items in GP for a specified period until these new items were ready to be revealed to the sales team? Thanks
LikeLike
Sean,
I am not aware of anything that would easily allow this to happen. And even thinking about customizing it, I suspect that would be a lot of effort, as there are so many places the items can be seen in GP. There may be ways of setting the items up without pricing, or not assigned to warehouses, so they cannot easily be put on transactions, but that would need to be thought through with full knowledge of your workflow, user permissions and GP setup…so something you may want to discuss with your GP partner and test in your environment.
-Victoria
LikeLike
Hi Victoria – I’ve been a fan of your blog for as long as we’ve been on GP (since 2010). I can definitely say you’ve saved me many hours of heartache and growing pains with GP over the years, so thank you for your efforts!
Our company reports on and references in-transit transfers quite often (SVC00700 and 701) so I thought it might be helpful to share what the statuses for SVC00700 mean:
0 = open
1 = picked
3 = partial shipped
4 = shipped
5 = partial received
6 = received
99 = in use
There may be others but these are all the ones that currently exist in our system.
Cheers!
-Dan
LikeLike
Thanks Dan!
Since I don’t have a page for the SVC tables, I will leave this in the comment here. They are all searchable, so someone looking for this table should find this info.
-Victoria
LikeLike
Victoria,
A client of ours has extra pieces of info for their inventory items that normally we would put in the user defined fields, however the data is too long for the UDF’s. Even longer than short name. Do you have a suggestions of how to handle the extra data?
LikeLike
Nancy,
Extender is a good solution for something like this.
-Victoria
LikeLike
I recently found out that extender does not use a regular table structure but rather stores fields in rows, so this is something to note before jumping on board as I imagine its complicated to report on. Not having used the product I’m not very familiar and if anything has changed please correct me but figured I would put that information out there. Sounded weird to me when I heard that.
LikeLike
Boris,
I have created a lot of reports based on Extender data. While it is definitely a bit more challenging than simply having all your related data in one table, it’s really not something I would consider a deal breaker. Once you get used to it, it’s pretty logical and should not present any problems. Extender also gives you the ability to automatically create views for the Extender data, so that can help with the reporting quite a bit. Even if you do not end up using those views, you can see where the data is easily from them.
-Victoria
LikeLike
Hello Victoria,
I have a need to add 5 new price levels for 1200+ items.
Is it possible edit the price list’s in XLS and insert the data?
insert into iv00108 (itemnmbr,CURNCYID,PRCLEVEL,UOFM,TOQTY,FROMQTY,UOMPRICE,QTYBSUOM) values (‘2STICK’,”,’DC’,’Ea’,’999999999999′,’1′,’100′,’1′)
insert into iv00108 (itemnmbr,CURNCYID,PRCLEVEL,UOFM,TOQTY,FROMQTY,UOMPRICE,QTYBSUOM) values (‘3STICK’,”,’DC’,’Ea’,’999999999999′,’1′,’100′,’1′)
insert into iv00108 (itemnmbr,CURNCYID,PRCLEVEL,UOFM,TOQTY,FROMQTY,UOMPRICE,QTYBSUOM) values (‘ADMIN1′,”,’DC’,’Ea’,’999999999999′,’1′,’100′,’1′)
insert into iv00108 (itemnmbr,CURNCYID,PRCLEVEL,UOFM,TOQTY,FROMQTY,UOMPRICE,QTYBSUOM) values (‘ADMIN2′,”,’DC’,’Ea’,’999999999999′,’1′,’100′,’1′)
I am certain this is not supported, but most things are not…LOL
This would save days of man hours (up front) but will it have costs down the line?
Any insight is very appreciated.
Gene
LikeLike
Hi Gene,
You will also need to insert corresponding records into IV00107. I have similar imports directly into SQL before, but I usually do a lot of data validation ahead of time to make sure that the data I am importing is clean. I would recommend inserting one record this way and then trying to use it on a transaction to make sure there are no issues.
-Victoria
LikeLike
Thank you Victoria!
The iv00107 fixed another issue I hadn’t had a chance to look at.
This will save us a ton of time. Still need User Approval testing.
But looks good.
Gene
LikeLike
🙂 Well, I don’t give out credit card info…but I’ll send you the code for free.
Regards,
Robert M.
LikeLike
I’m going to attempt it. Will you review when finished?
RM
LikeLike
Sure, what’s your credit card number? 🙂
-Victoria
LikeLike
Hello Victoria,
I tried to send this comment before but since I received no response maybe it did not go through. So here again:
Have you ever done a view that ages inventory (say in 6-month buckets) on hand?
This is a requirement in my org. for making “provisions for obsolescence”.
Regards,
Robert M.
LikeLike
Hi Robert,
I’ve not done a report like this, sorry. 😦
-Victoria
LikeLike
Hi Victoria,
We have a complicated issue w/ item names, and I was hoping if you could please help. We have several companies set in GP 2013, w/ each company having its own inventory databases of course. However, we are using two item numbers/names for the same part across two companies. Is there a way for us to change the those in one of the companies to match the other? We want to be able to do inter-company transactions, but have to enter each order manually when orders need to go between companies. The only thing that is common between them is the item descriptions, but I don’t know if that would help. There are thousands of items, and I’d like to find a way to do a batch update of one of the companies items to match the other.
Thanks,
Kay
LikeLike
Kay,
You can use the Item Modifier in the PSTL (Professional Services Tools Library) for this. It will take an imported list of item numbers to change. However, you would have to come up with that list on your own. This should certainly be possible with some SQL coding if you can link on the item descriptions. I would recommend talking to your Dynamics GP Partner, they should be able to help with this.
-Victoria
LikeLike
Thank you so much Victoria. I’ll look into this, and yes I should be able to get further help from our partner.
Regards,
Kay
LikeLike
Hi Victoria
I created a new company and was able to upload my inventory items using IM. Now we would like to re classify the items and also I would like to delete around 50% of uploaded items as a result of reclassification of items. Can I just delete the records in IV00101 table and re-upload the new set of items? These items don’t have transactions yet. Also, I am using GP 2013.
Many thanks.
LikeLike
Allan,
I have not done this before directly in SQL, but I think you would need to check a lot of additional tables – when an item gets created it populated a number of setup tables, depending on the item details. I would check all the tables starting with IV00. Also, if you end up doing this in SQL, you should run Check Links on the Inventory series after deleting the items.
-Victoria
LikeLike
Dear Victoria,
You posts have been very useful to me at many times.
Is there any way to auto-create item number in GP?
Waiting for your valuable response.
Thanks & Regards,
LikeLike
Hi Muhammad,
There is no way to do this out-of-the-box in Dynamics GP.
-Victoria
LikeLike
Hi Victoria! Is there any way to sort the available serial numbers in the Item Transfer Serial Number Entry screen?
LikeLike
Lynn,
Unless the screen already offers sort options, to change the default sorting on any window would require a modification/customization.
-Victoria
LikeLike
Hi Victoria,
In Item Maintenance window -> Item Notes, do you know how many characters it can hold?
Thanks,
Rotchine
LikeLike
Rotchine,
Do you mean the note next to the Item Number? That is a text type field in SQL and can hold a LOT of data: http://msdn.microsoft.com/en-us/library/ms187993(v=sql.110).aspx.
-Victoria
LikeLike
Hi Victoria,
I am fairly new to GP and your blog has been a wonderful resource! I searched and read through the blog about Kits and can’t seem to find any information on Item Kit Maintenance Window particularly on Quantity distribution.
GP Dynamics 9.0
My company is using Kit as an identifier via reports for what component items are combined. But the Sales Order are processed as non-kit defaulted items each with a percentage of sale. Example, Sales Order 1234 with 3 items at 40% in Quantity and Sales Order 1235 with same 3 items at 60% in Quantity. Using the Kit Item number with those 3 component items to identify them in reports as Kits.
The problem is, now we have a transaction that combines 12 items and more which make this process and reports very tedious. Is the Quantity in Item Kit Maintenance being stored in SQL? Because I can’t seem to track them down. Without knowing where they are stored I was thinking to create Kits with the percentage allocated as its name then process the Sales Order as a package of those components. Reports will then use the Kits percentage name as identifier of how much percentages of sale. Do you have a better solution to this via using the default Kit without customization?
Thanks!
Dennis
LikeLike
Hi Dennis,
The kit quantities are stored in the IV00104 table. Hope that helps. If not, please give me a more detailed example of what you’re looking to do and I will try to help further.
-Victoria
LikeLike
Hi Victoria,
Sorry if my question sounded ambiguous. Here’s a more detailed example:
Items 001,004,006,007,011,012,013,014,019,020,031,032 need to be combined and sell for 25%,25% and 50% to different Customers.
1. Initial Problem:
I made the above items in inventory individually and posted under the purchase transactions entry.
Then I made a Combined Kit that included the above component items at 100 Quantity each.
Started a Sales Order Transaction Entry with the Combined Kit but it only allows me to input the Quantity of 1 and not 0.25. If I use a component item individually instead of a Kit then it allows me to put Quantity to 2 decimal places.
What we have been doing is to create 3 Sales Orders that include all the items above at 0.25, 0.25, 0.50 Quantity respectively. In reporting services, however, these 3 Sales Orders need to be identified as a combined package sale. So we used the Combined Kit inventory ITEMNMBR as an identifier inside our reports for that purpose.
2. Current Problem:
I repeat the process above but this time I made a Combined Kit with the component items at 0.25 Quantity each. Name of the Combined Kit is Kit1-25 as in 25%. Then made Kit2-25 and Kit3-50
Created 3 Sales Order Transaction Entries with Kit1-25 and Quantity of 1, Kit2-25 and Quantity of 1, Kit3-50 and Quantity of 1.
I haven’t adjusted the reports yet and will like to confirm if this method will work?
Thank you!
Dennis
LikeLike
Hi Dennis,
Thanks for the additional detail. I guess the part that I am still missing is what specifically you need to show on your report that’s making it so difficult. From what you’ve described, you should be able to report on this in whatever detail you need.
-Victoria
LikeLike
Hi Victoria,
can you classify the document types in Inventory transaction table IV30300 ? i m in need of your feed back..
LikeLike
Hi Nataraj,
That’s already here, under Inventory Transaction Document Types.
-Victoria
LikeLike
Thanks Victoria,
at the first sight i dint get that.. 🙂
LikeLike
Hi Victoria,
I am fairly new to GP and you have been a wonderful resource! I have searched but I may have misssed the answer to this question. If I have, I apologize. I have built a smartlist that shows all components in a bill of material. What I am trying to do now, is build a smartlist that will show all items that contain the same two input components. I can make it show all that contain either – or, but now just the ones that contain both. Can you help? Thanks
LikeLike
Hi Donna,
On the SmartList search window you can change the Search Type (bottom right) to ‘Match 1 or More’ – that will let you do an OR search. Hope that helps.
-Victoria
LikeLike
Thanks for your reply. I have the smartlist set that way. What I am trying to do is pull ONLY the parts that contain both, not either or. Thanks.
LikeLike
Hi Donna,
Thanks for the clarification, sorry I misunderstood your question originally. I don’t see how you could do this in SmartList. Is your SmartList based on a SQL view? If so, it would be very easy to do this in SQL. If you need this to be a repeatable search, (ie, enter 2 different components, return the part that contains both) then you may need to go to a different reporting tool, one that will allow you to enter parameters, which SmartList does not. The 3 reporting tools I typically see used are Crystal, SSRS, and Excel.
-Victoria
LikeLike
Thanks so much for your time. I really appreciate the response
. I am not that great with SQL. will see what I can do in Excel.
LikeLike
Hi Victoria, Can we add more item types in GP. In addition to the following:
Item Types:
1 – Sales Inventory
2 – Discontinued
3 – Kit
4 – Misc Charges
5 – Services
6 – Flat Fee
LikeLiked by 1 person
Hi Zafar,
I cannot see how this would be possible without a HUGE customization. Those item types control all the behavior of the inventory items throughout GP and there is no mechanism or support that I am aware of for adding additional item types.
-Victoria
LikeLiked by 1 person
Hi Victoria, Do you know the table where the setup of GL account done/seen in the Item Account maintenance is stored, I’ve been asked to do a mass setup import for the Sales account for Items Thank you Nancy
LikeLike
Hi Nancy,
That’s stored in the IV00101 table. I believe the sales account index is IVSLSIDX.
-Victoria
LikeLike
Should have thought of that, I was looking for a whole other table. Thanks, happy holidays
Nancy
LikeLike
Hello Victoria,
I’m trying to do stock adjustments via Integration Manager tool and I guess it’s not working ! I ran the tool and I found no transaction ! I went to the batch details and I found just the transaction header with Quantity = 0 and without items (details). also I checked the underlying tables and I found no details !
Please if you can help me out and tell me how exactly I can do such transaction via Integration manager tool.
Thank you for reading this 🙂
LikeLike
Hi Mohamed,
It’s very difficult to troubleshoot something like this without seeing it. I would recommend you get my book – it goes through an example of setting up an inventory transaction import using Integration Manager.
-Victoria
LikeLike
I have a series of items assigned to a site that no longer need to be there. There are about 8,000 items. How can safely “unassign” those items from the site? Delete the records from IV00102? Current on-hand inventory is 0 for all items so the deletions shouldn’t affect the overall record for an item. Is that how that would be handled? Thanks,
LikeLike
Hi Jeremy,
I have not done this directly in SQL before, so I would hesitate to recommend it without some serious testing. I would recommend posting your question on the Dynamics GP Community Forum to see if someone has done what you’re suggesting before.
-Victoria
LikeLike
Hi Victoria,
Shipment is posted with wrong Lot Numbers and allocated to Sales Order.
I need to change Lot Number. I search for any eConnect procedure available to update but I couldn’t found any.
The following tables I will be updating:
[dbo].[IV00300].[LOTNUMBR] Lot number master
[dbo].[IV00301].[LOTNUMBR] Lot Attributes
[dbo].[POP30330].[SERLTNUM] Shipment Serial/Lot History
[dbo].[SOP10201].[SERLTNUM] Sales Transaction Serial/Lot Work and History
Is there any other tables also needs to be updated?
Thanks & Regards
LikeLike
Akbar,
If you received this in POP, I would also update the POP30330 table. You might also consider doing a search for all tables that have either SERLTNUM or LOTNUMBR columns to make sure you’re not missing any tables. You can use the code here to find all the tables: https://victoriayudin.com/2010/04/23/how-to-find-all-sql-tables-with-a-column-name/.
-Victoria
LikeLike
Hi Victoria,
Thank you very much for your reply.
Yes, its received in POP and POP30330 (Serial / Lot History) also included in my list.
Once again thanks for your useful link to find a particular column in database.
As this Lot number received first time in the system and its unique, I did search of all tables which contains this lot number through search-all-tables-for-a-string query which shows list of tables and came to conclusion that tables selected to update is as specified in my first comment.
Thanks & Regards
Akbar
LikeLike
Hi Victoria,
I am trying to make some old inventory sites inactive. However, I cannot find the flag for active/inactive on the IV40700 table (which is the Site Maintenance).
Can you point me in the right direction? What am I missing?
Thanks a bunch.
LikeLike
M.M.,
What version of GP are you using? The ability to make inventory sites inactive was only added in GP 2013.
-Victoria
LikeLike
Victoria,
I am trying to create a smartlist using smartlist builder that would show the expiration date of all raw material lots for both historical and current inventory. The problem I am having is that when I use the Item Lot # Master table (IV00300) it only shows lot numbers that currently have inventory values and not items that have been completely used/sold. Is there another table that would show the lot number expiration date for both used/sold and current inventory?
Thanks!
LikeLike
Ty,
Try the IV30400 table – that stores the Serial and Lot Number History.
-Victoria
LikeLike
Victoria,
I have tried using the IV30400 table, the problem with that table is that it ends up displaying the expiration date and lot attributes for every inventory transaction for that lot number, which can create anywhere from several to hundreds of additional lines per lot in the smartlist depending on how many inventory adjustments or manufacturing orders the lot was used in. For example, if lot ABC has been used in transactions 1, 2, 3 and 4 I do not want 4 lines in my data but rather just a header line with the lot attributes and expiration date for lot ABC. In addition, if the lot is in current inventory and has not yet been had an inventory transaction it does not show up in the smartlist if you use IV30400.
I am trying to create a single smartlist that will show one line of information related to the expiration date and lot attributes for all lots (current and used/sold), like the IV00300 does for current inventory. Do you know if this is possible? If so, could you point me in the right direction?
Thanks!
LikeLike
Ty,
How about using IV00301 and IV30600? Those tables should have one line per item per lot number. I would recommend creating a view first, where you union the data from the 2 tables, then creating the SmartList.
-Victoria
LikeLike
Hi Victoria,
Thank you for all your assistance once again. I have a small problem, in Dynamics GP 2010-Inventory Transaction Inquiry Window, there is a field called Adjustment Reference, unfortunately I am not able to locate this field in any Inventory Table and the help section for that window does not even make mention of that field. I am hoping that you would be able to point me in the right direction.
LikeLike
Carleesha,
On my GP 2010 I do not have a field called Adjustment Reference on the Inventory Transaction Inquiry window. So you are possibly using a modified or 3rd party window for this and would need to check with the developer or documentation for that to determine where that is stored.
-Victoria
LikeLike
Thank you Victoria, I will be making that contact right away.
LikeLike
Victoria
I’ve got what seems to me to be a weird one. In the user “item Quantities Maintenance” screen for a part I see data in the primary vendor Id fields, but when I run “select itemnmbr,vendorid,itmvndty from iv00103 where itemnmbr like ‘900.0000’
I only return “itemnmbr vendorid itmvndty
900.0000 0” .
Is there any other table where items are tied to primary vendors? Can an item have both “0” no ventor? and “1,2” primary and non primary vendors?
Patrick
LikeLike
Patrick,
I checked a number of different datasets and I am not seeing any where the ITMVNDTY is zero. Sounds like a possible data issue to me, but without actually looking at it and knowing the history of how it got in there, it’s impossible to say.
-Victoria
LikeLike
With the database I inherited I don’t know how or when data was externally modified. I have 70 item numbers with “0” as the itmvndty. That makes it fun to write reports, trust me. I’ve run reconcile and check links are there any other tools available to clean up the mess I have behind the curtain?
Patrick
LikeLike
Patrick,
Unfortunately, no. If it is causing a problem and you can confirm that it is wrong, maybe the proper course of action would be to fix this in the database, but I would hesitate recommending this without any additional information. If it’s causing you grief, it may be worth a call to GP Support to see what they recommend.
-Victoria
LikeLike
Thank you for the wonderful info!
Where is the “Default Site ID” on the “Item Transfer Entry” stored? I looked in IV10000 but didn’t see it. Thanks again!
LikeLike
Hi Jeremy,
I looked all over, but am also not seeing where that is stored. Sorry to not be able to help. Maybe you can post this question on the Dynamics GP Community Forum to see if someone else can help with this.
-Victoria
LikeLike
Good afternoon Victoria,
I am trying to create a table relationship between the ‘Purchase Order Work’ table and the ‘IV_Item_ENG’ (IVR10015) table, but i cant seem to figure out exactly what is wrong. when I bring up the table relationships window from the GP side, i cant see any of the MFG tables. is this normal? do you know if it is even possible to create relationships between tables in GP MFG module? please advise!
LikeLike
Hi Victoria,
First time poster and an altogether new member of the GP community (4 months now) and have found your blog to be extremely helpful. I’m trying to pull the bin of each item in a unposted sales order but after looking through all of the tables that I thought could be relevant (SOP10200, IV10001) I haven’t found any bin information. Would you happen to know where this info is stored?
Thank you,
Boris
LikeLike
Hi Boris,
I don’t really use bins too much, so I do not have any good data to test this with, but have you looked at table SOP10203?
If that does not help, you can use the code in this blog post to help find any tables that have either ‘BIN’ or ‘BINNMBR’ in the column names and go through them to see if any look promising. For ‘BIN’ you will find a lot, so i would start with the SOP and IV ones.
-Victoria
LikeLike
Leave a Reply text box is disabled
Regards
Zafar
LikeLike
Hi Zafar,
I just tested it in both Google Chrome and IE 9 and it seems to be working for me. Can you let me know what browser you are using?
Thanks,
-Victoria
LikeLike
Hi Victoria,
Thanks for your reply. I am using IE9. Originally I was thinking that it is my machine but then I was able to post the comments on you resources page.
Did you receive my question about deleting some items from a price level directly from IV00108?
Regards
Zafar Khan
LikeLike
Hi Zafar,
Yep, I replied there already. 🙂
-Victoria
LikeLike
Hi Victoria,
Can you tell in which table decimal places value stored . (Tools->setup->Financial->Multicurrency ->Functional currency-> Currency format)
LikeLike
Kavitha,
Do you mean Currency Setup? I am not seeing anything called “Currency format” – if you are, can you explain exactly how you are getting to it?
If you do mean Currency Setup, that is in the DYNAMICS database in table MC40200.
-Victoria
LikeLiked by 1 person
can you see this link : for screen shot …https://skydrive.live.com/#cid=5E1D2EB753C00953&id=5E1D2EB753C00953%21127
when we click Function currency under Multi Currency Setup window.
i need to find how many decimal places is setup.
LikeLike
Kavitha,
Yes, your screenshot shows the Currency Setup window. The number of decimal places is stored in the DECPLCUR field of the MC40200 table. You will need to subtract 1 from the value in the table. So if you have a currency with 2 decimal places, the value in DECPLCUR for it will be 3.
-Victoria
LikeLike
Thanks for your quick reply…..
LikeLike
Long time reader, first time poster here. I can’t seem to find a good answer to this question. I would like to find a way to report on items on a sales order or invoice where the primary vendor was not used. We want to track second sourcing to find out how often an item is sold and the primary vendor is not used. Looks like the value I need is ITMVNDTY from IV00103, but this information is not held in any of the SOP tables at the line item level. Any suggestions?
LikeLike
Hi Todd,
As a first step, you would need to tie the inventory receipts to the sales. That should be possible with tables IV10200 and IV10201. Once you’ve got that, you can link to the receipts to see what vendor was used and compare that to the primary vendor for each item. Hope that helps.
-Victoria
LikeLike
I am trying to find out some more information about items that are classified as “Kits”. My company makes use of kits and when these kits appear on an invoice we see the kit line item and also the line items that make up the kit. What we are running into however, is that the actual Kit line item carries the Cost and Price but the line items that make up the kit only carry the Cost and no Price. So when we look at the margin on these line items they show up as a pure loss because they do not carry a price with them. I know that we can identify the Kit from the ITEMCODE. Is there any way to identify the items that are part of the kit? The reason is that we would like to remove the cost from those items that are part of the kit. Or attach the price to the kit items. Not sure how to deal with this. Thank you!
LikeLike
Hi Karen,
Just to clarify – a kit is like having a combo sale. For example, let’s say you have 3 items in stock and you say to customers, “If you buy all 3 of these together, the price is going to be $400, but buying them individually, it would be $500.” In GP a kit is not a real item, it’s not like you ‘make’ a new item out of the 3 items in the kits, it is more like you’re selling them together at a group discount. Since your cost on the items does not change, the cost of the kit is whatever you paid for the 3 items. So the way this is recorded in GP is that the price is on the kit, but the cost is coming from the component items making up the kit.
The issue you’re describing sounds like pure reporting problem to me, which I think you have also identified. You’re showing the price correctly – only on the kits, but you’re showing the cost twice – on the kits and on the component items. If all you want is the correct margin, you need to exclude the component items that were sold as part of a kit from your reporting. To be able to help with that, I would need to know what report(s) you are looking at. It’s possible that you either need to change/customize your current report or use other reports for this.
-Victoria
LikeLike
You’re exactly right. It does have to do with the reporting side. The reporting we are doing here is actually in a homegrown reporting tool. We import the line items from GP every night from the SOP30200/SOP30300 tables into another SQL database. I would like to identify the component items that were sold as a kit but I don’t know what the identifier is. On a single invoice, I see the kit and I see several items which I suppose may or may not be part of the kit. I think they could include additional items on an invoice outside of the kit and it’s component items. Is there a field in GP that would identify those component items?
LikeLike
Karen,
Any line in the SOP30300 table where the CMPNTSEQ field is not 0 is a kit component. Hopefully you can filter your data to remove the cost from those.
-Victoria
LikeLike
Thank you, thank you Victoria. That is perfect! This should be exactly what I need to filter out those components. Thank you! (did I say that already?) 🙂
LikeLike
Karen,
You are very welcome, glad I could help!
-Victoria
LikeLike
this was VERY helpful – I couldn’t find some inventory transactions in the IV30300 table and you pointed me to the unposted table of IV10001 table – much appreciated!
LikeLike
Hi Victoria, thank you for this resource. I am looking for field in Great Plains to add extra information about our stock items. What is the difference between the ITEMNMBR field and ITEMCODE field in IV00101? I am hoping that I can use the ITEMCODE field for the extra data. Otherwise, how would you recommend adding extra tags to my stock items? I am already using the Short Description, Generic Description and Class ID but I need at least one more field. I could also use the user defined fields in the internet information. Is this the best place to add the extra details?
LikeLike
Andrew,
The answer to your question will depend on how you need to interact with the extra details you want to store. For example, how will the data be entered, do users need to change it or inquire on it inside GP, do you need to see it on GP reports, what size of data will you be storing, etc. While ITEMCODE does not seem to be used for anything, it’s only 15 characters and I am not sure if you can see it on any out-of-the-box GP windows.
-Victoria
LikeLike
It will only be for reporting purposes. Users will not need to access the field via GP. Changes can be made through SQL if necessary. I couldn’t find the ITEMCODE field on any of the GP screens.
LikeLike
Andrew,
In that case, if 15 characters is enough, I do not see any reason you cannot use the ITEMCODE field for this. Just keep an eye out for GP starting to use this field for something when applying service packs pr upgrading GP in the future.
-Victoria
LikeLike
I used Modifier to add ITEMCODE to our Item Maintenance card and we use it to indicate maintenance levels.
I just checked the GP2013 SDK and it doesn’t look like ItemCode is being used yet. Unfortunately the ITEMMAS.doc hasn’t been updated since 2007.
LikeLike
Thanks for the info, David!
-Victoria
LikeLike
Thanks for the assistance, Victoria and David.
LikeLike
Hi Victoria,
Any idea? how can we find when an item status was changed from Sales Inventory to discontinued?
LikeLike
Zafar,
Out-of-the-box, without setting something up to specifically track it, there is no definitive way to find this. There is a Modified Date in the IV00101 (Item Master) table which will show the last date an item was modified, but there is no way to guarantee that the last modification was the change you’re looking for.
-Victoria
LikeLike
Hi Victoria,
I am new to your blog, but I have found a lot of useful information. I am currently trying to set up inventory cycle counts, but I am having a problem with the stock count entry. I have to many items to enter my cycle count manually. Is there anyway that you know of to enter the cycle counts quickly without having to use a third party software?
Thank you,
Henry
LikeLike
Hi Henry,
Sorry, I am not aware of anything like that.
-Victoria
LikeLike
What I’ve done before is export the items you want to count along with the current quantities on hand out to an Excel file.
–Do your stock count and update a column in the Excel file with your actual count.
–Then create a column for the difference between your GP QOH and actual count.
–Sort the spreadsheet and get rid of the lines where the difference is zero.
–From this, create a CSV file.
–In GP, go to Tools>Integrate>Table Import.
— Create a Table Import Definition for Inventory > Inventory Transactions Work using you calculated difference column for the TRX QTY Total.
— Run the import then you’ll need to do a File Maintenance Check Links on Inventory Transactions Work to put it into a batch.
— Find the Inventory Batch that Check Links created and post it.
It’s been a few years since I’ve done this so I may have forgotten a few steps. But that’s it in a nutshell. It’s actually a pretty easy process.
BE SURE TO TEST THIS OUT PREFERABLY IN A TEST COMPANY!!!
LikeLike
Thanks for the advice David!
-Victoria
LikeLike
I use a macro to fill in everything. I created a macro entering 1 line into the stock count, then I use Microsoft Word to mail merge each row I have in my stock count from an excel spreadsheet. Then I run the macro on the stock count entry screen.
LikeLike
Sean – I was able to use the mail merge macro for th stock count schedule. However, the mail merge did not work for the stock count entry. Could you possibly send me your macro? enrique.rivera@atsol.com
Henry
LikeLike
After you merge everything with this template, on the top 7 items take out the last line “scrollbyline”. On 8th item down on your excel list keep the line scrolling line in the merged macro. Last item will error out due to line scroll. I just enter manually. Check all zeros manually. Line row #’s should be 8 all the way down after item 8. The mail merge items I enter are: 1 (line number), WH, BIN, ITEM, 200 (qty).
CheckActiveWin dictionary ‘default’ form ivStockCountEntry window ivStockCountEntry
# Key 1:
MoveTo line 1 scrollwin LineScroll field ‘Counted Qty’
# Key 1: ‘WH’, ‘BIN’, ‘ITEM’
TypeTo line 1 scrollwin LineScroll field ‘Counted Qty’ , ‘200’
# Key 1: ‘WH’, ‘BIN’, ‘ITEM’
ScrollByLine down scrollwin LineScroll
LikeLike
Sean – I was successful using the mail merge macro for the stock count schedule. My mail merge macro for the stock count entry screen keeps errorring out. Sometimes, the error message is that the window is not active. Other times it cannot write to the counted qty field.
Could you provide me a copy of the macro that you used?
Please send to enrique.rivera@atsol.com
Thank you,
Henry
LikeLike
Sean- I am also looking for mthis solution. Can you send it to me also?
petefols@yahoo.com
LikeLike
Hi Victoria, I have been following your blog and have always found it fantastic for us GP users.
I was wondering if you could shed some light on a reporting issue I have stumbled across?
I am trying to create a WIP report in SQL showing each contract and account number by posting date which I have done but I want to try and connect it back to the account period balances but I am using PaFeebillamount for each contract and that is not tying back to the correct figure for the total account amount.
can you help shed any light on how I can do this?
LikeLike
Hi Phil,
Thank you for your kind words!
I have only done only limited reporting on PA tables, so I am not the best resource for this. I do know I have used the PAFeebillamount field to get the revenue, but without actually looking at the data and what you’re pulling in with your code, I cannot help with this, sorry.
You could try posting your question on the GP Community Forum, perhaps someone there can help.
-Victoria
LikeLike
No problem except I think I meant to say left inner. The radio button just below the standard join on crystal reports. I will double check and update in the office. I’m just on my iPhone right now.
LikeLike
Multi bins data is stored in table IV00112. You cross that with IV00101 to get item description and with IV00102 to get site level details. Join IV00101 to IV00102 on the ITEMNMBR and IV00102 to IV00112 on ITEMNBR and LOCCODE (site). This will give you a list of only items that have a bin in 112 but if you want to include all items regardless of an associated bin that has been moved to or from inventory then you need to right outer join 102 and 112. Hope this helps. You also may need to filter out LOCCODE “”. Just look through your results to verify returned numbers don’t exceed actual as it will add an extra result for each item otherwise.
LikeLike
Thanks Allen!
-Victoria
LikeLike
Correction. Left Outer Join on 102 and 112 to get a list of all in 102 and any references that are there in 112
LikeLike
Victoria,
I’m looking for a SQL query that will return the inventory items in each BIN. Another words it will list each BIN location and the item and quantities in that location. Any ideas?
As aways, thank you very much for all your help!
LikeLike
Hi Mark,
Unfortunately, I do not have multiple bins enabled anywhere on a test system where I can check this, but I thought this would all be in the IV00102 table. Have you ruled that out?
-Victoria
LikeLike
Victoria, as a new user, your site has been very helpful in finding the needle in the haystack when trying to learn what was done when. One thing I havent found in the Inventory tables is a created by field to know what user created an item. Any ideas?? Thanks – have a blessed day.
LikeLike
Hi Pete,
Thanks for your kind words. Unfortunately, the user who created the item is not stored by GP anywhere. 😦
-Victoria
LikeLike
Hi! Victoria. I am having a hard time tracing what table and fields on the database the Final Unit Price, located at the bottom of Price Trace Inquiry window on GP. My Input are Customer ID, Item Number, U of M, Quantity, and Date. Any idea how will I write a query to get the Unit Price. Much appreciated if I can query it joining to SOP10100 with a parameter of SOPNUMBE or BACHNUMB. Many thanks in advance
LikeLike
Hi Ryan,
I am not familiar with the Price Trace Inquiry window – is it possible that this is a custom window or something added by a 3rd party product? If so, I would recommend asking the developer of this window your question.
-Victoria
LikeLike
Thanks for a quick reply. I think Price Trace Inquiry window is not a custom window. I’m using GP9. It is located on the Inquiry tab menu bar then choose Inventory>>Extended Pricing>>Price Trace.
LikeLike
Ryan,
I don’t have Extended Pricing enabled currently on any of my test systems, so I cannot look at the window myself, but I was able to get a list of the tables used by it with the Support Debugging Tool: https://victoriayudin.files.wordpress.com/2012/11/price-trace-inquiry.png
Hope this helps you track it down.
-Victoria
LikeLike
Many thanks Victoria. God bless. I appreciate that you are helping not only me, but many people.
LikeLike
Hi Victoria,
I stumbled across your blog, which was a great success for me–Thank You! My question is in regards to building a smarlist. I am trying to build one that shows an item number and the components of the BOM in relation. I’ve tried different BOM tables, but I cannot get one to show all components of one item number. Any help would be greatly appreciated!
LikeLike
Hi Jessica,
All you should need is the BM00111 table. Is there something specifically that is not working or that you have a question on?
-Victoria
LikeLike
Hi Victoria!
Huge fan of your book & site. I run a small business and implemented GP by myself with only the help of your book. Anyways, a lot of my prices have changed and I’m trying to mass update pricing (Currency Amount) for our inventory but am having trouble. Integration manager wont work with our setup because we are wholesale and have pricing breaks at different quantities, and Integration Manager through eConnect only works through “Insert”, no update.
There’s GOT to be an easier way?! I have all the item #s, price level, quantity to/from, and price all in CSV file (just like how the IV00108 table is laid out, minus the DEX TS & ROW ID); but can’t figure out a way to import it into GP. I tried just updating IV00108 in SQL through a bulk insert from the CSV file but am getting errors (plus its probably not good habit). Maybe I’m writing the script wrong? Any ideas? Thanks!
-Michael
LikeLike
Hi Michael,
Thank you very much for the kind words, I am glad my blog and book have been able to help you with your GP implementation!
My general advice is that I would be VERY careful about updating IV00108 (or any table, for that matter) directly in SQL unless you are 100% confident of the outcome. While I have done both inserts and updates to item pricing in the past for customers that required a lot of price changes, it always required testing and data validation and the code was written specifically for the situation. Unfortunately, there is nothing generic that I am aware of that will work for all possible price updates.
If you have a lot of these changes to do (and especially if you feel this will be something you will need again in the future), perhaps this is something we can work on as a consulting project for you. Please let me know if you’re interested in talking about this option.
-Victoria
LikeLike
Is it OK to delete records from IV00200?
I was asked to clear the inventory quantities of some items, so I cleared the quantities in IV00112. Now, there are some items in IV00200 with a QtyType of 3. Can I just delete them from the table?
LikeLike
Sun,
Deleting records directly from a table is absolutely not ok unless you are 100% certain of the outcome. Without knowing more details of what is really needed, it is difficult to recommend a solution, but if you need to clear out inventory it is typically best to do it with an Inventory Adjustment transaction.
-Victoria
LikeLike
Good afternoon Victoria,
The owner of our company, for whatever reason, has decided to devalue certain old items in our inventory. He wants the 2000+ of this item to all have a value of $0 in the Inventory subledger and for that to carry through to the GL. In effect, he wants to write off the value of that old stock while keeping the quantities.
Is that as simple as running the adjust costs utility for this item and marking them as $0? And will that automatically post adjustments through to the GL?
Thank You!
LikeLike
Scot,
Yes, you can use the Adjust Costs Utility for this – it will create a GL transaction debiting the Inventory Offset account and crediting the Inventory account that is set up for each of your items. I would recommend performing a test in a test company prior to doing anything like this to make sure you are clear on exactly what will happen, what accounts will be used, what dates will be defaulted, etc.
-Victoria
LikeLike
HI Victoria,
I love your site and use it as my main resource when building reports. Thank you so much for what I’ve been able to learn over the years just by referencing your site. I found an addition for the Source Indicator in IV10000 that I thought you might want to add. Twelve (12) is an In-Transit Inventory Transfer – something I discovered recently while trying to build a historical WIP report for manufacturing.
Thanks again for your invaluable support of the Dynamics GP community.
-Trevor
LikeLike
Thanks Trevor!
I added this source indicator value to the list – I appreciate the info.
-Victoria
LikeLike
Hi Victoria, great site!
I’m attempting to import our inventory item listing through integration manager. We currently do not have any stock to speak of, and only 1 site. I try to specify that site for the QTYS/SITES destination in the integration, but it doesn’t seem to flow through into GP. I have a constant field for Site Display Options-Site ID, Site Location Code – CA, Default Site ID-CA, and default for everything else in that folder. Am i missing something?
LikeLike
Hi Mike,
Thanks for your kind words.
It a bit difficult to troubleshoot an import without seeing it. However if you only have one site and all the items need to be assigned to it, you could do this in one simple step after your import. Go to Cards | Inventory | Site, select your Site ID, click on the Assign button and click OK. (There will be a confirmation message that you need to click Continue on.)
-Victoria
LikeLike
I need to add several hundred inventory items, and want to do via SQL. Can you point me to some procedure that covers the essential table relationships and required fields? Right now, I am thinking it enough to add records to the inventory master, price master, item-vendor master, and item quantity master. But, I’m sure I’m missing something.
LikeLike
Jim,
I do not have the complete list of tables for importing inventory items in SQL and that list may actually change depending on all the components and features of GP your company is using. Instead of importing directly into SQL tables, I would recommend using an import tool like Integration Manager to import new items. That way you can guarantee that you will update all the tables needed and will not break any of the GP application business rules/logic in the process. If that is not an option, you could look in the GP SDK to see if it has the information you need for an import like this.
-Victoria
LikeLike
I agree with Victoria on that one. I have used it to load several thousand items into the system and it works well.
LikeLike
Victoria,
Indeed, Integration services are not an option, unfortunately, but we have a pretty simple set up with only the most basic inventory modules, and don’t use extensions and serial numbers, bins, etc. I’ve done some research and had a discussion with our partner, so am plowing ahead. I’d be happy to share the outcome and procedure I come up with. Where best should I post it?
Jim
LikeLike
Hi Jim,
Understood. 🙂 Thanks for offering to post the outcome and procedure – that would be great! I think this page is probably the best place for it.
Thanks,
-Victoria
LikeLike
Okay…making progress but have run into a slight snag on the pricing (just standard item by item pricing–not using extended). I have all the data in the right place for the unit of measures and price tables, but here’s the problem When I put the default Selling U of M in the Item Master record for the item, the pricing on the Sales Order Form doesn’t calculate upon entering the quantity; the price and amount just stay at 0, as if the form can’t find the qty/pricing schedule.
Now, I can get it to work if 1) I null the IV00101. SellingUofM field, or 2) if I change the unit measure in the pricing list detail window–even just changing an upper case to a lower case letter. So, I figure that there’s some “after update” event on the detail price list u of m field that’s linking some piece of data somewhere. Follow all that? I never get any error messages about a disconnect between the default and the detail U of M, which happens if it can’t find the detail line U of M or if the default has nothing in the schedule detail corresponding.
One more twist is that the hierarchical tree on the bottom left of the Item Price List window (which has the UofM schedules as top nodes and UofM details as subnodes) does not contain up to date information on the UofM/price/qty as they exist in the Item Price List table (IV00108). For instance, it will have a unit of measure that’s not even listed. But, again when I force an update by changing a u of m detail field, the tree/nodes suddenly refresh with the correct data, and from then on, the pricing calcs on the SOP entry form work just great.
So, questions arise: Where is the tree on the price list window get it’s data from? It’s not IV40402 since it doesn’t correspond exactly until I force the refresh. But where else could it be? I think the form is “syncing” the tree’s data source with IV40402, and that somehow affects the SOP entry form as well. Perhaps if I can track down this data source, I can update it via SQL and all will be peachy keen.
Thanks–lots of detail, but hopefully there are enough clues…
Blessings,
Jim
LikeLike
Jim,
Are you only importing the pricing into IV00108? What about IV00107? And are you also importing a default price level for the items into IV00101?
Also, not sure what you mean by ‘pricing list detail window’ – For standard pricing this should be the Item Price List Maintenance window. Looking at mine, I do not see a tree with UofM Schedules as top nodes there (or actually anywhere), my tree is:
Item Number
Currency ID
Price Level
UofM (not UofM Schedule, just UofM)
Finally, are you running check links after your import? If not, this might not be a bad idea…it would let you know if something was wrong and/or possibly fix some of the missing needed links.
-Victoria
LikeLike
Thanks, Victoria,
I had somehow overlooked IV00107, which apparently stores all the price-level/UofM combinations for an item. And that’s exactly where that tree-view control in the Price List Maint. window gets it’s information. You can import all sorts of pricing/UofM structures into IV00108 and put the default price level into IV00101, but until IV00107 is updated with the price-levels/UofM’s pairs, that tree-view control won’t reflect what you’re looking at in the Price List Maintenance Window, and sales transactions won’t calculate the default pricing.
Anyway, I did successfully update all the pricing, (doing some major overhaul of IV108 and 107) and it works just fine in the SOP and POP transactions. Also, for a couple of hundred new items, I was able to import all the product and vendor information our admin staff usually has to enter in six different windows with just a few SQL statements.
I did document the procedure I used, and as soon as I tidy it up, I’d be happy to share. But my full document, for my own records, will be a couple of pages (for when I look again at this next year and don’t remember a thing!). So, again, not sure where to post it.
Thanks again for your help and wonderful site.
Jim
Colorado Springs
LikeLike
David,
Thank you for your response to this string and Victoria for this blog. I was unaware of the Price list utility. We’re in the process of changing our price method from % of Standard Cost to % of Current Cost. Not only does this change delete the Price levels, we saw no way to do the price method change in bulk. This utility will save us hours of doing them one at a time. Plus it will update BOTH our price method and Price levels (Which we needed to do also) in bulk.
Thanks again,
Mark
LikeLike
Victoria,
A question has come up on how to eliminate currencies that we no longer use in our price lists. I could easy enough rip them out via SQL delete of records from IV00107 and IV00108 then run Checklinks, but I don’t know if that is the smartest way to do it. Is there an in applicaiton way to remove price lists from items in mass? From a query against the non US$ price lists that are targeted, I have 2608 entries in each table to eliminate.
LikeLike
Hi Allen,
I am not aware of any easier way to do this in the GP UI. 😦 I’ve not done this myself before, so I cannot give you any real life feedback about doing this in SQL except the usual…make lots of backups and test as much as you can. 🙂
-Victoria
LikeLike
You might look into Price List Utilities. I’ve used it to get rid of un-needed Price Levels.
LikeLike
Thanks David. I am using it in my test environment now and seems to be exactly what I need. Much appreciated.
LikeLike
Hi David,
Thank you very much for this suggestion!
-Victoria
LikeLike
Hi, first of all congratulate Victoria for your great site, what do you think is the problem that do not fit even reconciling inventory quantities? as the first movement does not fit against the balance .. and reconciled ‘n’ sometimes not solve the problem, check links of tables nor gp utilities .. no longer could be causing this situation, apologize for my English .. hoping I can target in this regard .. thanks and regards.
LikeLike
Hi Alberto,
I am sorry, I am not sure what you mean. If you are having trouble reconciling inventory, consider getting a product like The Closer to help with this.
-Victoria
LikeLike
I am in the process of getting familiar with GP 2010 via the 90-day trial version and using both the Sample company as a reference and concurrently setting up new company so that I can go through the process.
But I cannot get the List Price to appear as Enabled (not grayed-out) in the Inventory Item card within the new company.
That new company is not set up as Multi-currency (or at least I don’t think it is), but the Sample company is. Does having one company set up within the Client as MC somehow force the second company into the same condition?
If not, then where would I go to confirm that the new company has not somehow ‘inherited’ a MC setting?
Thanks
LikeLike
JRB,
If Multicurrency (MC) is enabled, it’s for an entire installation. However, I don’t believe MC settings should have any impact on whether or not you’re able to enter pricing for items. Are you sure you didn’t enable Extended Pricing? If you did, you may not be able to use the ‘regular’ pricing. Are you following any kind of guide and/or getting any help on setting up your new company?
-Victoria
LikeLike
Victoria,
Thanks for the reply.
Within both the MC company and the new company I can enter a Standard and a Current COST.
But traditionally COST is a reflection of what the company to had to pay to purchase/acquire/manufacture/etc. the item, not what we intend to sell it for to the customers.
But I cannot enter a List PRICE in either of the companies.
Within the Item Card, the List Price text label and the associated text box is grayed-out (not enabled)
My other investigation on the web has indicated that this is due to the companies being configured as Multi-Currency. I have no idea why that should be a rule to eliminate entering List PRICE, but that is what I found.
Since installation of the Sample Company (‘Fabrikam Inc’) forces a MC company into the Client, it must somehow be affecting the new company which I have set up with only US-$ via Multicurrency Access. And I cannot Delete the other currencies from the system via Currency as long as the Sample company is using them.
Since I want to set up my various Item Pricings as a % of List Price the in-ability to enter that List PRICE is a problem that I need to eliminate.
Thanks
LikeLike
JRB,
Thanks for the more detailed explanation, I now understand the real question. 🙂 When you are on the Item Maintenance window, click the little GoTo button in the upper right corner and choose Item Currency. Select your Currency ID and enter the List Price. This List Price will be used for all pricing you enter for the selected currency. Even if you’re not using Multicurrency, this is fine and will work with no issues. You can theoretically turn off Multicurrency, but that might break other stuff that may have been set up, and this is a little more difficult to answer without looking at other settings. So I would just leave it for now, especially since this is a test, right?
hope that helps,
-Victoria
LikeLike
Hello Victoria
I followed your suggestion and entered the List Price value – thank you.
However when I went back to the Item Maintenance ‘card’ the List Price was still grayed-out and the card’s text box was still empty – it did not reflect the entry I had made so that it could be visually reviewed for accuracy.
If indeed the process succeeded in entering a List Price, why does it not appear in the Item Maintenance card for the item itself?
Thanks
LikeLike
JRB,
What you’re seeing is by design. Since you could possibly have multiple list prices (one per each currency), and there is only 1 place for the List Price on this window, it will not show up. You can check your prices by (a) entering a price on the price list – for example, enter a price level that is 100% of the list price – that will calculate and show you the list price. Or you can look in the IV00105 table. Here is a query that will give you all the list prices:
-Victoria
LikeLike
Hi Ms. Victoria,
Our fiscal year is about to end and we need to close each module. The problem now is our server as of now have two companies running in two databases. Is it advisable to simultaneously perform the closing of modules?
LikeLike
Chris,
You mean closing the same module in two different companies at the same time? This is very difficult to answer without having a lot more information, like the size of your data, the resources available on your server and network, etc. However, in general, if there are enough resources, there should be no issue with running the year-end process in two companies at the same time.
-Victoria
LikeLike
How to findout total stock of kit item ?
LikeLike
Arun,
A kit is not a real item…it is a like a virtual grouping of items. Thus you will never have a kit in stock and it will never have quantities.
-Victoria
LikeLike
HI Victoria,
Thanks for sharing your extensive knowledge! I have been trying to figure out what sets the primary vendor on an item. We often buy from different vendors and sometimes our data shows two primary vendors and no secondary vendors. But other times, we might have no primary vendor but multiple secondary. I am equating a value of “1” in IV00103.ITMVNDTY as “primary” and a value of “2” as secondary vendor.
Thanks,
Jerry
LikeLike
Hi Jerry,
In GP, an item can (optionally) have one primary vendor per site. So it is possible to have as many primary vendors as site for an item. In gP you can see this on the Item Quantities Maintenance window (Cards | Inventory | Quantities/Sites). In SQL table IV00102 will store the primary vendor per site in the PRIMVNDR field.
-Victoria
LikeLike
Hi again. I understand that part but what I don’t get is how the value of “1” or “2” gets set in IV00103.ITMVNDTY through Dynamics (GP10). I don’t see anything in the interface that will show me the We have vendor/item reports that are looking at that field and expecting it to be a “1” for the vendor who we primarily purchase from. I can’t see how to have the user set that through the interface but it is usually correct. Just trying to keep away from exceptions. — Thanks
LikeLike
Jerry,
When you create vendors for each item, by default they will at first be set up with a value of 2, meaning they are not the primary vendor. If you then go to the Item Quantities Maintenance window, you can select a primary vendor for each site for that item, and that will change the value to 1 in the IV00103 table and also add the primary vendor ID to the IV00102 table. Hope that helps.
-Victoria
LikeLike
Victoria,
Thanks so much. That’s exactly what I wanted to know!
Jerry
LikeLike
Hi Mark/Victoria,
Can we not do it through integration manager?
LikeLike
Victoria,
The GP 9.0 (Yes we’re upgrading to 2010 very soon) documentation indicates that changing the price method (we’re using standard pricing) is not something that can be rolled down to an item class. Have you come across anyway to get around this without having to change each item one at a time.
Thanks,
Mark
LikeLike
Mark,
I have not done this myself, so I do not know if it would work without testing it, but you could possibly accomplish this directly in SQL… How complicated this is would greatly depend on the exact type of change you’re making as well as the current pricing you have in place. No matter what, it will require a very good understanding of the tables involved and a lot of testing, so would only make sense if you are going this for a large number of items.
-Victoria
LikeLike
Mark, can you specifically tell me from what and two what you are going? I have done quite a bit here. Also do you have integration manager?
LikeLike
Allen, Zafar, Victoria,
It appears that we do not have Integration Manager. If I go to Tools – Integrate – Integration Manager, it is greyed out. Not sure how to get Integration Manager or how much it costs. I suppose I’ll follow up with our GP Partner.
What we need to do is very simple. We have about 40 Item Classes representing about 4000 to 5000 inventory items. We simply want to change each Item Class’ Price Method from % Markup – Standard Cost to % Markup – Current Cost and have it roll down to all the items. We just wanted to avoid changing each of the 5000 items one at a time.
Thanks for any help!
LikeLike
hi Victoria,
when we tried to post purchase receipt on dropship order, we got this error msg and i can’t figure out why.
“ERROR: Item’s vendor record doesn’t exist”
Do you have any idea on how to fix this? i checked item setup and all look fine.
thanks,
Aaron
LikeLike
Aaron,
I have not come across this message before in GP. Do you by any chance have any customizations or 3rd party products that might be requiring some additional setup to be present? If not, then I would recommend posting your question on the GP Community Forum to check if others have seen this error.
-Victoria
LikeLike
This late was a HUGE help. Very nice Victoria!
LikeLike
Victoria
Can you tell me what PSTGSTUS (Posting Status) of 35 in table IV10000 means?
LikeLike
Seth,
The SDK only shows possible values of 0, 1, 2 or 3 for the Posting Status. Are these transactions being imported? What is the batch status for the batch in the SY00500 table?
-Victoria
LikeLike
Victoria
can you tell mw what this field is for ITEMCODE [Item Code] (AK6) char(15) in IV00101 and if it is accesable to put data in. there is nothing in the field and someone here wants to use it for some kind of classification.
LikeLike
Hi Vic,
There is nothing in the SDK about that field, and I do not see data in that field in a few installations that I have checked, so it is most likely reserved for something that is either not in place yet or there for integration with a module that is not too common. However, I also do not see a way to enter anything in there via the GP User Interface.
-Victoria
LikeLike
Hi there,
We are running GP2010 and using the In-Transit Transfer functionality. I have a transfer that has been shipped and showing a status of Received but the transaction has not moved to history. I have run checklinks on IV and it did nothing. I have also checked the document header and line status in SVC00700 and SVC00701, all of which are 6.
Any suggestions on how I get this “completed” document to history?
Thanks Claire
LikeLike
Hi Claire,
I have not run into this before, have you confirmed that the transaction has posted? If so, I would try running Inventory Reconcile.
-Victoria
LikeLike
Hi Victoria,
Thanks for the reply. The document has definately posted. I was hoping to avoid Reconcile as it runs for 5 hours and this is a 24hr business but what must be must be.
Appreciate the help.
LikeLike
Claire,
I hear you. 😦 Unfortunately, there is no way to tell if Reconcile will fix this, either. If you have a test company where you could restore your live data and test this, you might want to do that first…so that you don’t waste all that time on the live company.
-Victoria
LikeLike
We are having this happen to all of our In-Transit Transfers now. At some time it did move to history and then stopped. Reconcile has not helped resolve this. Any other ideas? Thanks!!
LikeLike
Has there been a solution for this problem, I am running GP 2013 and am Running into this currently. Any Idea of whats going on?
LikeLike
Dear Stehen, you should run this script…
UPDATE SVC00700 SET STATUS = 6 WHERE ORDDOCID = ‘TransferNumber’
exec SVC_Transfer_TransferToHistory ‘TransferNumber’
see http://support.microsoft.com/kb/871916
Regarts…
Diego
LikeLike
Hi Victoria
Thanks for such a great resource. We currently have an invoice that completed without allocating the serial numbers on the order. Now we have a large qty of serials that are available to be picked by another user but should be on an existing order. Is there anything you would suggest to secure these serials to the correct order?
Thanks!
LikeLike
Hi Ryan,
Sorry, I have not run into this situation before. I would recommend posting your question on the GP Community Forum to see if anyone else has encountered it before. Otherwise, you might want to ask GP Support to help resolve this.
-Victoria
LikeLike
Typo. IN00107 – Item Price List Options. Should be IV not IN. 🙂
LikeLike
Jimmy,
Thanks for catching that, it has been fixed.
-Victoria
LikeLike
Hi Victoria
What table and field would I find the default price level for each item . I need to set all the default prices to our Retail/Internet price level for all items.
thanks
Vic
LikeLike
Vic,
That is PRCLEVEL in IV00101. Be careful with this – if you assign a price level that is not assigned to the item, SQL will let you do it, but you might get errors in GP.
-Victoria
LikeLike
Thanks Victoria for the warning. I will be using your AWSOME view_Inventory_Price_Levels view to make sure that the items i am updating indeed are in that price level.
Thanks again for the resuce!!
Vic
LikeLike
Hi Victoria – I’m a first time poster, thanks for providing such a comprehensive resource!
I am trying to gather data via ODBC regarding inventory transactions and the only thing I cannot find is the “offset account” that inventory is signed out against. Can you tell me what table that lives in? I am getting the rest of my transaction data from IV30300. Thanks!
LikeLike
Hi Bob,
It sounds like you want the IVIVOFIX column – you will need to link that to the ACTINDX in either GL00100 or GL00105 to get the account name/number. Please let me know if that’s not what you’re looking for.
-Victoria
LikeLike
Jackpot! Thanks very much, that’ll do it!!
Bob.
LikeLike
Hi Victoria
I was wondering if there was a script that i can run to remove items from a price level. We are trying to remove a couple of price levels from the system but there are items associated with it. Like 2800 of them. To pull up each and everyone would take too long.
Thanks as always!!!!
Vic
LikeLike
Hi Vic,
I am not aware of a generic existing script to do this, sorry. I know I have done something similar in the past for a customer, but I am not comfortable giving anyone scripts to delete data without being able to see their data first and test on it.
-Victoria
LikeLike
Hi Victoria, thanks a lot for all informations. Is there any table for Deleted items in GP either for each module or together?
LikeLike
Sreejith,
Out-of-the-box GP does not store any deleted information.
-Victoria
LikeLike
Hi Victoria,
When one of the user is trying to add an item, user is getting error message “a save operation on table ivitemcurr failed accessing sql data”. However, on the same machine GP is not showing any error with other GP user ids. We are using GP 10 and SQL 2008 R2. We tried to create a new userid for that user but the new userid is also getting the same error message, Please note that the user has the same security role id (power user) as those for whom the GP is working fine
LikeLike
Hi Zafar,
Is this a custom table? I am not familiar with a table called ivitemcurr. If so, the issue may be SQL permissions on the table or it may be something specific to the customization that this table is part of.
-Victoria
LikeLike
Hi Victoria,
Thanks for your reply. The problem was happening due to a tracking software installed by our Microsoft partner to track a Item Master table related issue.
LikeLike
Victoria,
I’m a first-time poster, so first, thanks for all the information you’ve compiled here; it has been very helpful for me a number of times.
I didn’t see this one listed anywhere, but there’s another field that I think could be useful to include. I’ve used the field PCHSRCTY from table IV10200 a few times to identify the type of transaction. I’ve listed the different values from the GP Resource Descriptions. Feel free to incorporate this if you feel it could be useful.
1 – Adjustment
2 – Variance
3 – Transfer
4 – Override
5 – Receipt
6 – Return
7 – Assembly
8 – In-Transit
Thanks!
Stephan
LikeLike
Stephan,
Thank you very much! I have added these to the page above.
-Victoria
LikeLike
Hi Victoria
We are doing a physical inventory today. We have set up our count schedules in GP with all the items. Can i import our counts directly into IV10301 then process through GP?
thanks
Vic
LikeLike
Hi Vic,
I have not done this, so I am not sure if this will work or not, or what the dangers might be…sorry. One thing I do know is that if you are using lot or serial number, that is in a separate table. If you decide to go ahead and do this, please post back to let me know how it went.
-Victoria
LikeLike
Hi Victoria
This went well. We are not doing lots or serials numbers so updating tha IV10301 table then procrssing the count through GP worked like a charm.
Thanks
Vic
LikeLike
Vic,
Excellent, thanks for the follow up and I am glad that it worked for you!
-Victoria
LikeLike
Hi Victoria- I am trying to learn more about the above post. I have 10,000+ non serialized parts that need to be counted. The real issue and bottleneck is inputting all of them into GP. A upload would be helpful. Since this mpost, have you gotten more information on how to mdo this?
-Pete
LikeLike
Hi Pete,
No, sorry, I do not have any more information on this.
-Victoria
LikeLike
Hi Victoria!
Another, hopefully quick, question on Inventory…
We have items that we purchase for marketing purposes. The CFO normally just expenses them and does not want to track them for COGS, etc. The Dir. of Operations wants them as inventory items so that she can track quantities and is willing to SOP and Inovice them for that purpose but she also needs their cost in the system for POs. So naturally, the receipt of these items adds to inventory and the invoicing of them tries to hit COGS, etc. Is there anyway to have items that have cost for Purchasing and are tracked for quantities but that do not affect G/L? Thank you,
Scot
LikeLike
Hi Scot,
I don’t think you can easily track inventory costs and quantities and have it not hit the GL completely. However, you should be able to set this up so it hits a different set of accounts than you normally use for ‘regular’ inventory. For example, if the only issue is the COGS account being hit, why not create a separate COGS account for these items, or even use a Marketing Expense account? I would think that would be preferable, as that would actually expense the cost of these items to the correct place. Depending on how you are set up to use SOP (using accounts from the items or customers), you can change the COGS account either on the items or the customers (I imagine you have a ‘dummy’ or special customer ID use use to process these through SOP). Hope that helps.
-Victoria
LikeLike
Thank you very much for the response, unfortunately the CFO doesn’t really want to create accounts to hold these false COGS amounts. They are expensed items.
Is there anyway to automatically override the cost amount to 0 at invoice generation? We need the costs on the item master for POs but right now, that means that the invoicing clerk needs to remember to 0 the distribution for COGS and she does not normally even look at the distributions.
Thanks again, and btw, how does your site pick the avatar for these comments? I do wear reading glasses but don’t think that I normally appear that angry…need to ask my wife… 🙂
LikeLike
Scot,
I don’t think that you can do what you are asking for without a customization. It’s a catch 22 – you cannot track quantities and costs for purchasing without also having inventory and COGS distributions. However, why not just set up the inventory account to be the same as the COGS account? The GL entry will still be created, but with a net result of zero.
Your avatar is pretty angry looking! 🙂 The avatar is automatically created by the blog software for anyone who does not have one. If you click on it, I believe it will take you to a website where you can set up your own.
-Victoria
LikeLike
Good morning Victoria,
I hope you can assist with this…
We recently migrated to GP2010. It appears that during the migration, possibly in the loading of inventory, we got some items that have quantities without purchase receipts in the system. One in particular shows about 300 items onhand. The problem is that the stock status and purchase receipts reports are showing negative valuations. We are getting records on the purchase receipt report that shows a vendor id of “Sales Entry” and a receipt type of “Override”. The unit cost is correct but the extended cost shows $0 and we then get a negative value remaining. A query of POP10500 shows no receipts with unsold quantities for that item number despite the 300+ onhand.
G/L accounts were balance-forwarded so I do not want to do anything that will adjust those…
I am very comfortable with SQL updates if that is the only way to correct this.
Hope you can help. Tx
Scot
LikeLike
Scot,
It’s possible that the processes that are run as part of the upgrade actually brought to light lingering data issues that were there all along. From what you’re describing it sounds like there was a shortage of inventory when allocating or fulfilling an SOP transaction and the shortage was overridden by the user. That would explain the vendor being “Sales Entry” and the receipt type being “Override”. This should have been ‘fixed’ by a subsequent receipt of inventory, but it’s hard to say without looking at all the related data and understanding what has happened in the past. In general, I would be careful about fixing things in SQL unless you are 100% certain of the fix. 🙂
At this point, I would recommend talking to GP Support or your GP Partner so they can help you figure out what exactly happened and suggest the best fix for you. Sorry not to have an easy answer.
-Victoria
LikeLike
Dear Victoria,
First off all thanks for compiling such a great info blog of GP tables and making our life little easy.
My problem is we are having differences in our Inventory Sub-ledger as compare to GL Inventory ledger at the time of Period Closing (i.e. All Sales Invoices and Purchase Receiving posted). To investigate the issue I tried to run a query agains GL20000 and IV30300 tables
My question is what join I should have between GL20000 and IV30300 for all type of transactions i.e. Sales, Receiving, IV adjustments in order to get the Total Cost posted to IV Accounts for a particular period. Currently what I am using is “GL.ORTRXSRC = IV.TRXSORCE AND GL.ORDOCNUM = IV.DOCNUMBR”, but it seems that this join is not giving me Same total for SUM(IV.EXTDCOST * SIGN(IV.TRXQTY)) compare to SUM(IV.EXTDCOST * SIGN(IV.TRXQTY)).
Any recomendations on this.
Thanks in anticipation.
Regards,
Amjad
LikeLike
Amjad,
The amount of work and complexity to do this yourself in SQL is going to be crazy. I would recommend at looking a product that already does this, like The Closer.
-Victoria
LikeLike
Hi Victoria,
Thanks for your prompt response.
I had feelings that it will be complex to get the ledger & sub-ledger comparison.
The product “The Close” you recommended is good but due to budget constraints, is not a feasible solution.
I will try dig more deeper into that.
Thanks once again.
Amjad
LikeLike
Victoria,
I am needing to do a cost comparison of intercompany and third party vendors. As such, the factory has given us a price sheet from which I can get their costs. I would like however to have a place I can place that against the vendor so that 1. that cost is available for reporting and 2. that cost will be used anytime we order from the intecompany vendor. For now, I am going to split the report between accessing an access database and the GP database but would prefer for performance reasons for all of it to be in the GP server for part 2 of my “likes”. Any suggestions? I was wondering about updating the IV00103.LastOriginatingCost for the specific vendor but I am not sure if that would stick or be safe.
LikeLike
Allen,
As far as keeping the data on the SQL Server, why not just create your own table that hold the data inside either DYNAMICS or your company database(s)?
For the cost to be automatically correct in GP…I don’t believe you can simply update the Last Originating Cost once – as you suspect, it will not ‘stick’, it will be overwritten every time a receipt of an item is posted. So you either need to keep updating it constantly or come up with some other customization for this.
-Victoria
LikeLike
Hi Victoria,
We posted an inventory adjustment on the fly. Something happend when posting and it did not go through. However, I went to create a new transaction and put it in a batch as my user ID. I did an edit list and saw that rogue transaction in there. I tried pulling it up in transaction entry but it said it was posted. I looked at my detail and it wasn’t. I posted the batch anyway and only my second transaction posted. I tried running a checklinks on the transaction work files and it updated the batch….so it says. Tried posting the batch again, but it just stays there. Any ideas? Thanks in advance!
LikeLike
Hi Brian,
If I am understanding what you are describing correctly, there is no simple fix for this and certainly nothing that I would be able to recommend without actually looking at your data. Your best bet will probably be to work with either your GP partner or GP support to help you fix this.
-Victoria
LikeLike
Hi Victoria,
hope you will be fine,
Dear Victoria how I can roolback(reverse a posted transfer batch).
because I have problems of sites for some items,
thanks n advance.
Jamil Shah Afridi
LikeLike
Jamil,
I am not aware of any way to “roll back” anything posted in GP. Unless you’re talking about a SQL Server point in time restore, which would also wipe out any other changes made and is not an option in most cases.
Typically, to reverse a transfer you would enter a transfer going the opposite way. So if you originally transferred Qty 3 of Item A from SiteA to SiteB, you would enter a transfer of Qty 3 of Item A from SiteB to SiteA.
-Victoria
LikeLike
Hi,
IV30300 table and IV10201 table not got updated for some sales documents.
I have verified the item information, Maintain Transaction History Marked in IV Maintenance option Screen.
And The Sales transaction were not a Dropship transactions.
Please let me know for which scenarios the IV30300 and IV10201 tables not got updated ?
Thanks in advance.
Regards,
S.Vigneshwaran
LikeLike
Off the top of my head, the only legitimate reasons I can think of are:
I would recommend talking to GP Support to get a more definitive answer on this.
-Victoria
LikeLike
Hi Victoria, I tried but could not find any option to install GP SDK from the GP 10.0 installation media. Is it available in GP 10.0?
LikeLike
Zafar,
Yes it is. For GP 10.0 it is in the Tools folder under SDK. Look for the SDK.msi file.
-Victoria
LikeLike
Hi Victoria,
Thank you so much for such a valuable information.
LikeLike
Hi Victoria
Is there anywhere i get get a listing of Fileds Defs for GP specifically the IV00101 table. DECPLCUR doesnt tell me mucha as to what data that is. I need to have the field defs for all the fields for a project I am doing for sales. They are claiming GP does not hold all the data they need.
Thanks
LikeLike
Vic,
There are a few ways to find table information, however, none of them may give you everything you’re looking for. That’s where experience and practice come in. 😉
Two resources for finding field information:
The DECPLCUR field stores a code for the number of decimal places to use for the currency amounts on the item. If you are using multicurrency, there will be a number of decimal places for each item/currency combination in IV00105. It is important to note that the code stored in DECPLCUR is not the actual number of decimal places, which will be the code minus 1. So if DECPLCUR = 3, that is 2 decimal places.
-Victoria
LikeLike
Victoria
You strike again!!! The SDK was perfect there is a database diagram that lists the fileds and what they mean!!!! PERFECT
Once again you save us mere mortals in a time of need!!!!
Vic
LikeLike
Hi Victoria,
Hope you can help me in this. i bill an item, but could not be able to bill because it said that the item still alocated.
i checked it on Inventory>inquiry> serial
i insert the Item number and it maked check on aloc.
is ther a away i can uncheck that?
i do verified the PO and its received already.
thanks..
LikeLike
Hi Jhunn,
You’re on an inquiry window, so you cannot “uncheck” allocation there, it is informational only. An item will show that it is allocated when it is fulfilled on an order – you can find what order or invoice it is fulfilled on by going to Inquiry | Inventory | Serial/Lot Trace. Once you find the order or invoice it’s allocated on, if this is not correct, you can open that sales transactions and remove the serial number from it…that will “un-fulfill” it, so you need to be 100% sure this is what you want to do.
-Victoria
LikeLike
Hi Victoria, is it possible to change the settings in GP so that the Go To action from an Inquiry window opens the required Inquiry window instead of Maintenance window. Currently, for example, when a user click Go To>Price List from Item Inquiry, the GP opens the Item Price List Maintenance instead of Item Price List Inquiry. We are using GP 10.
LikeLike
Zafar,
This is not possible out-of-the-box in GP. I do not know how easy it would be to customize this, you would need to talk to a developer or GP Support to find out.
-Victoria
LikeLike
“Hi Victoria, is it possible to change the settings in GP so that the Go To action from an Inquiry window opens the required Inquiry window instead of Maintenance window. Currently, for example, when a user click Go To>Price List from Item Inquiry, the GP opens the Item Price List Maintenance instead of Item Price List Inquiry. We are using GP 10.”
I wrote a VBA mod that does this. What I did was modify the Item Inquiry window to include some of the fields that are on Item Maintenance but missing on the Inquiry window. Then used VBA to redirect the Item Inquiry to the PriceList Inquiry window.
So basically what happens is anywhere the Item Maintenance gets called – for example in Sales Transaction Entry – the Item Inquiry opens up instead. Only the users that NEED to access the Item Maintenance card get access to it.
I can send you the package if you want it.
LikeLike
Hi Victoria Yudin
Thanks for Posting,
I have problem in Inventory module “The serial number and extended quantity for this item do not balance”.
Thanks in advance.
LikeLike
Jamil,
Is this when posting in SOP? If so, take a look at KB article 938693.
-Victoria
LikeLike
Good Morning Victoria
No this occur during entry time(system shutdown)
Best Regards
Jamil Shah Afridi
LikeLike
Jamil,
Sorry, I don’t understand. During entry time of what? What about system shutdown?
-Victoria
LikeLike
Hi Victoria,
Thank you so much.
LikeLike
Hi Victoria,
Thank you so much for your prompt reply. Is it safe to add triggers to GP tables? In addition, I will appreciate if you can suggest some monitoring tools for these types of situations.
LikeLike
Hi Zafar,
Yes, triggers are safe to add if it is done right. 🙂 This is not really my area of expertise, I would recommend asking on a SQL Server forum, or at least one of the public GP forums to see if you can get some more advice on how to best monitor this stuff.
-Victoria
LikeLike
Hi Victoria,
We are noticing for sometime that following happens to any item randomly:
– All the price level prices disappears from the Item Price list Maintenance card
– ListPrice becomes 0
– Currency decimal changes from 2 to 5
In last 5 months, it has happened to 6 items. Any idea what may be happening?
When we checked these items in IV00105, we find DECPLCUR equal to 6 for these items whereas for all other items it was 3.
We are using GP 10 and SQL 2008 R2.
LikeLike
Zafar,
In all my years of working with GP and all the customer installations I have seen, I have never seen GP do this and I would be very surprised if GP itself is causing this. I would suspect some other process – a customization, another product, an import gone astray, a SQL job – to be causing this. Unfortunately, it’s impossible to tell you with any certainty what is causing this, since of course, it should not be happening. I would start by examining everything you have that touches GP data to see if you can determine what is causing this. If it’s not obvious, you may need to put some triggers on tables or some other monitoring tools in place to track this down.
-Victoria
LikeLike
Hi Victoria,
I am trying to build a report where I need the Receipt No. And Vendor Doc. No. along with some other info for Inventory Transaction Document Types = 4 (Purchase Receipt). I am able to get all the info except Vendor Doc. No. from IV30300. I will be thankful if you can advise me from which table to get the Vendor Doc. No.
corresponding to the Receipt No.
LikeLike
i hope this helps
i do that by joining IV30300 WITH
POP30300
ON
IV30300.DOCNUMBR = POP30300.POPRCTNM
AND –i do this just in case
IV30300.TRXSORCE = POP30300.TRXSORCE
LikeLike
Hi FAC,
Thank you so much for you quick and effective help.
LikeLike
Hi Victoria Yudin
hope you be fine .I appreciate your work providing help to people.
I have a problem with batch which is posted with error, Now in smartlist the batch status is both post and unposted,also the quantity is not available, batsh posting status is PSTGSTUS =3
please provide me some solution.
as there any sql query to solve this problem.
Advance Thanks
Jamil Shah Afridi
LikeLike
Hi Jamil,
Sorry, this is not something I would feel comfortable simply giving someone a SQL script for without seeing the data. I would recommend asking your GP Partner or GP Support for help with this to make sure that you do not compromise the integrity of your data.
-Victoria
LikeLike
Hi Victoria,
I am setting up beginning balances for GP 2010 now, and i am kind a stuck when uploading inventory balances. For your information, each of our item in inventory has lot number and expiration date. I am wondering of how can i upload expiry date since the Inventory Transaction template on Integration Manager seems doesn’t have any field which enable us to do so? I have tried by putting the expiry date information under Date 1 and Date 2 in the lot number attributes, but that doesn’t give me the correct result.
Can you enlighten me on this? Thank you in advance,
Regards,
Lukman
LikeLike
Hi Lukman,
I don’t work with lots too often, so I have not tried using IM to import expiration dates for them. Looking at it briefly, I don’t really see a way to do this in IM, so your only option may be to either enter them manually or directly in SQL Server. I believe that is in the IV00300 table.
-Victoria
LikeLike
Thank you so much FAC and Victoria. My apologies for assuming that FAC is some sort of Code name for Victoria.
LikeLike
Hi Victoria,
Thank you so much for such a quick and effective reply. Yes, it helped me a lot and now I have completed the report with all the required fields.
LikeLike
Hi Zafar,
That was actually another reader, FAC, that helped you. 🙂
-Victoria
LikeLike
Sorry I forgot to mention that we are using GP 10 and when I tried to download the Support Debugging Tool I got following error message:
Our Apologies
You are not authorized to view this page
You do not have permission to view this directory or page using the credentials you supplied. If you have reached this page in error, please contact voice@microsoft.com for assistance with your account.
LikeLike
Zafar,
If you are a GP customer, you will need to ask your partner to get the Support Debugging Tool for you.
-Victoria
LikeLike
Hi Victoria,
I am trying to develop a report for getting certain inventory adjsutment records. I am able to get all the required fields, excepts the fields shown on Inventory Transaction Distribution Inquiry window, from Tables IV30200 and IV30300. Kindly advice which table contains the fields, such as Account and Description for Inventory and Inventory Offset, shown on Inventory Transaction Distribution Inquiry window
LikeLike
Hi Zafar, those fields are Inventory IVIVINDX and Inventory Offset IVIVOFIX in IV30300
those fields store the account index you can match those with table GL00100 with field ACTINDX and get ACTDESCR wich is the description of the account
i hope that helps
LikeLike
Hi Victoria,
Can you help me to creat a view finding item (quantity+cost) in a specific date?
Thank you in advance
———————————-
Yassir Humaid
LikeLike
Yassir,
Are you looking for a historical stock status? If so, I do not have any code I can share for that.
-Victoria
LikeLike
Yes I am.
Ok, Which table/s are used for “Item Trx Inquiry” Window?
I think with some filtering ~ Criteria ..we can find what we are looking for.
LikeLike
Yassir,
I would recommend that you install the Support Debugging Tool to determine what tables are associated with what windows. Here is a link to a blog post on how to do this: https://victoriayudin.com/2010/05/17/support-debugging-tool-new-build-released/.
-Victoria
LikeLike
Dear Victoria….
Thanks for your last help…
Now we’re using GP portal to input demand of stuff or goods (Purchase request), and every demand processed to be Purchase Order, it using default currency ($US) for each item code.
If we need another currency such as (Rupiah or yen) it need to go to GP – card -inventory -item currency “and set each item manually”
-Next time GP will recognize this currency for item code.-
The Problems is, “there’s a lot of item code to be manual maintain like this.”
If there’s another way to do this….it will be wonderful ….please
Abdulloh Ismet
Note : we use GP v 8.0
LikeLike
Abdulloh,
If you have Integration Manager you could try importing all the item currencies you need.
-Victoria
LikeLike
We use the Copy Item function quite a bit but there’s a problem with it – the new-from-copy item retains the Date Created value from the original item. Is there some way to force it to use the actual date it was created?
Thank you in advance.
LikeLike
Hi Patrick,
Not out-of-the-box. In the past I have seen companies create a SQL trigger to update the date on any new items created.
-Victoria
LikeLike
Hi Victoria ,
I usually refer your site for reports and queries. It helped me a lot every time it go through it.
I have a query. I was just going through the SDK documents. I found that
IVIVINDX column is GL account index for inventory. But here on this page it is mentioned
IVIVINDX – Inventory On Hand. Can you please put some light on this.
Thanking you,
LikeLike
Waseem,
Inventory and Inventory On Hand is really the same thing – calling it Inventory On Hand can be helpful to show the distinction between this account and In Use, In Service and Damaged, all of which are also used for Inventory, but of different types/statuses.
Hope that helps.
-Victoria
LikeLike
Hi Victoria
please would you help me with this problem?
I got a Sales Site
where i got this transactions for X item
Transfer1 100units
Sales1 -100units
Transfer2 500units
Sales2 -500units
Return1 500units
Sales2.1 -500units
Sales2.2 -500units
Return2 500units
if you count the stock it should be zero, but GP keeps telling me that there is 500units in stock
there are no other transactions in that site for that X item
i even reconcile inventory but the stock is sitll in 500units
i even update IV00102 to zero for that site and the other site where LOCNCODE=”
the run reconcile inventory again and the stock is back to 500units
i thought QTYONHND of IV00102 was calculated by the sum of fields in IV30300
is there another table where stocks in IV00102 are calculated from?
thanks a lot
LikeLike
Hi FAC,
I am not sure what the formula for QTYONHAND is, sorry. But it does sound like something may have gone wrong with one of the transactions or GP didn’t think it was taking the items out of inventory for one of the sales. Everything is definitely posted? Have you tried running Check Links on the Sales series?
What does the Item Stock Inquiry window (Inquiry | Inventory | Item Stock) show for the very first line? If the first transaction was a transfer in for 100 units, does it show a balance of 100 or 600?
Are there any 3rd party products or customizations in place? I saw something similar caused one time by a 3rd party product that was not accounting for POP returns.
-Victoria
LikeLike
Hi Victoria
Thanks for your quick reply
Yes everything is posted, ran checklinks and reconcile in all series 😛
The other day realized that Inquiry | Inventory | Item Stock goes backwards i mean it grabs the stock quantity in IV00102 and starts calculating (just for the report) backwards (according to date trx starts adding or substracting the last trx by date and that is the balance for that trx then adds or substract the previos by date trx and go on) (i hope i made myself clear in that)
so if my current balance in IV00102 is 500 then the first trx balance is 600 (after the first trx of 100unit)
but when i made the update to iv00102 to zero the last trx balance was o and the first one 100
about the 3rd party there is any, my GP is in spanish, Andina Localization, in this localization there is another kardex but this Localization creates another tables (nsaKD_xxxxxx) and another menus
and there is one table nsaKD_IV30103 that stores balances per Site and all Sites
but these balances are Ok
So i guess there is another table that Reconcile is checking to get another balance
i hope i didnt confuse you because i already am 😉
Thanks!
LikeLike
FAC,
You are right, Item Stock counts backwards from the current balance. I find that sometimes this window is really helpful to see if there is an issue. If the very 1st transaction is for 100 and that makes the balance 600, obviously something went wrong. At this point, I think you have 2 choices. (1) If it were my data and this happened all the time, I would probably want to investigate further into why this is happening and find a way to prevent it. (2) If it was an isolated incident, I would just enter an inventory adjustment for -500 units and make sure the GL is correct and reconciles to my inventory subledger.
-Victoria
LikeLike
Hi Victoria
this is the only case where this error happened, i made an inventory adjustment and fixed the balance, and cut the post to GL
Thanks a lot
LikeLike
Hi,
I am using Smartlist Builder to create a calculated field for a smartlist. One of the fields I want to use in the calculation is “Order Up To Level” from the Item Quantity Master table. It allows me to create the calculation using this field. I save it. But it replaces the “Order Up To Level” field with the “Order Point Qty” field. Nothing I do seems to prevent this. Can you tell me why this might be happening. Thanks!
LikeLike
Hi Susan,
While I have not run into this particular issue, I have run into many limitations when trying to add logic directly in SmartList Builder. I always recommend creating a SQL view and then pointing your SmartList to it so that you can code any logic you need directly in SQL and do not have to rely on SmartList Builder for this.
-Victoria
LikeLike
Last Received Bin… So if that is what I am needing to find, am I correct in thinking that is table IV30302? We have had some problem happen last week that when the guy ran remove bins that it removed bins for items that had quantities. don’t know if there was a process problem before or what. But after inventory reconcile was run, I now have 117 new items assigned to auto create and need to quickly identify the last place the system recorded where the inventory was received and placed.
LikeLike
Sorry, I don’t work with bins much, so while that looks like the right table, I would advise either doing some testing/tracing on that or verifying this with GP Support.
-Victoria
LikeLike
Hi Vic,
I’m trying to find the QTY SOLD for each item.. life to date. not only for current year.
I need exactly to know the items not sold from its created date.
which table/s can be used for this view?
thanks in advnce,
——————————-
Yassir Humaid
LikeLike
Yassir,
You should be able to get total quantity sold for all time using the Item Quantity Master table, IV00102.
-Victoria
LikeLike
Thank you fo fast response..
I found that the column “QTY SOLD” in this table returns only the current year informations, but not previously closed years!
To make sure ..
In my view i inquired about the items with criteria “QTYSOLD=0” from IV00102
But through “Item Trx Inquiry” inside GP, I found there are some sales Trx on some of these items in the last year!
Can you help plz?
LikeLike
Yassir,
Try the code below, make sure to replace Your Item Number :
-Victoria
LikeLike
It gives me: Total Sold=12
that’s really the quantity shown in the sls invoice in”Item Trx Inq”
While from smartlist>>Inventory>>Item Qty: where “Record Type=Overall” It gives me QTYSold=0 for the same Item!!
I don’t know if you understood me or not..!
but i think.. I have to make view to exclude those items in table (IV30102) from “all Items” to get with zero sold quantity Items overall the item life.
is it right?
LikeLike
Yassir,
It does sound like I am not understanding what you’re looking to do. If you would like to rephrase your question we can try again.
The total of all items sold will be returned by the code I gave you IF item history was being kept for the item at the time of the sale. There are also a LOT of other variables that come into play. Comparing information on different windows in GP will not always give you the same numbers, as they may be looking at different data.
It may be that your questions will best be answered by someone looking at your data and with a better understanding of what exactly you are trying to accomplish. Have you tried working with your GP Partner on this?
-Victoria
LikeLike
OK,
Suppose that I Have 1000 Items entered in system 3 years ago.
at least 20 items .. not sold during the 3 years..
How can I find or filter only these 20 items?
FYI: Qty Sold field in table IV00102 not for whole item life but only for the current year so that when I search for items with “Qty Sold=0” it does not search in item history trx.
Is it clear now? 🙂
Thanks Victoria
LikeLike
Thank you at all..
I tested a code to get all Items from IV00102 in which NOT EXISTS in IV30102 then exported them on Excel so can be easly managed through pivot table.
It works fine!
LikeLike
I appreciate your concern and following up
LikeLike
Victoria, I having problem with IV10200 and IV10201, I fould that both IV table (IV10200,IV10201) is not properly updated, some of the information in both tables are missing such as IV10200.ValuationMethod, IV10201.TRXREFERENCE, how I can update both fields.
Thanks
Rashid
LikeLike
Rashid,
If you suspect that something is going wrong with your data, I would not recommend updating it yourself. To make such a recommendation without knowing a lot more detail and looking at your data would be irresponsible, at best. I would recommend talking to GP Support or your GP Partner about this, so they can take a look at your data and work with you on finding the best resolution.
-Victoria
LikeLike
Needing some advice. We are going to be bringing a product line back from another sister site. Since we transferred the product line to them, a corporate conversion has occured on their item numbers and replaced the dashes in the item numbers with periods. I want as much as possible to limit the number of new items in the system. I am wanting to consider the impact of changing the item number itself replacing the dashes in the number with periods. How much will this break? Is there an easy way to do this?
LikeLike
Allen,
I would recommend using the PSTL Item Modifier tool for this. It will ‘rename’ any items you want throughout your system and is the easiest/safest way to accomplish this and should not break anything. If you have 3rd party add-ons or customizations those may need to be addressed separately, but otherwise, I feel comfortable that this is a safe process.
-Victoria
LikeLike
The price on that kills me. I’ll have to see what I can potentially do with Integration Manager. I am using that to upload all the new items into the database, those that do not have a previously written item number with dashes. I am having a problem though because though I am giving it price lists to update, it is not putting the price list information into the system but yet says it is successful. I’m on GP10 latest SP and running from the server on 2003Std. It simply is not updated IV00108 as would be expected. Any thoughts?
LikeLike
Allen,
Troubleshooting IM in a forum like this, without actually seeing it, is pretty tough. Often, when there are multiple sources the issue is in the Query Relationships or in the sorting of the data. Can you test it with just one item to see if works?
For the $750 price tag on the tool, in my experience people are often going to spend more than that in other resources and effort to accomplish what they need and may still not have as good as solution.
-Victoria
LikeLike
Hi Allen,
I just thought of you as I was reviewing the Integration Manager chapter for my upcoming book…in my testing (for GP 2010, but I would expect the same for GP 10.0), the price import will fail without at error if you are entering both a Percent and Price. One of them should be used, the other should say “Use Default” under the Rule. Not sure if this is what is causing your issue, but worth checking.
-Victoria
LikeLike
Hi Victoria
I need to bring in some item history data. I know these 2 tables hold that kind of data and I have the same data from my old system already in a table.
Inventory Sales Summary History IV_SUM_HIST IV30101
Inventory Sales Summary Period History IV_SUM_HIST_Period IV30102
I only really need the period data for the report being requested but i figured i would include the other table as well.
My questions are: do i need to update the data anywhere else?
And by adding the data to either of thos tables will I effect any financial data?
This is for sales reporting not financial.
thanks
Vic
LikeLike
Vic,
No General Ledger data will be affected by entering amounts into these tables. I have not entered historical data this way, but I would test this with a small set of data and make sure that running an inventory reconciliation doesn’t clear it out.
Another option – if this data is needed for reports being created in SmartList Builder or some other (external) tool, I would consider creating your own table(s) to hold it. That way you are not relying on GP not ‘messing’ with that data.
-Victoria
LikeLike
Thanks for the site, its been a great help thus far.
One question I have is about deleting units of measure. For example, under Unit of Measure ID ‘EACH’ we have a handful of selling uofm’s – each, 100, 1000, etc. Our tables have grown out of control due to mismanagement to the point where we have 40MM records in IV00107/8 and it is causing a nightmare for other products, most notably the CRM/GP Adapter which won’t work because of the size of those tables. So, in a case with MS we are trying to figure out how to reduce the size of those tables but they aren’t being very helpful thus far. If I use the built in Dynamics method for deleting rows under a UOFM, it will take atleast a week based on results in a test enviro to delete the superfluous rows under uofm EACH. So, I was thinking about maybe just running a delete statement in IV00107/8 and IV40202. Might that work?
LikeLike
Hi Jeff,
In general, I would not recommend deleting from the tables without a lot of testing first. This type of clean up is not something that I have done in the past, so I don’t have any first hand experience to share. Since this sounds like a pretty large issue, I would recommend either escalating the case with GP Support, or perhaps talking to the Professional Services team instead to see if they can come up with the best, ‘safe’ option for handling this.
-Victoria
LikeLike
I have found a problem where IV00107 RNDGAMNT is overriding the list price set in IV00105. How is this being generated? What is this “Rounding Amount” field used for?
LikeLike
Allen,
The Rounding Amount is used when you want to make sure that your prices always round to a particular number or a multiple of a particular number. For example, if your price method is a % of list price, but you want to make sure the price rounds up to end in $0.95. Is this is not what you want, you would need to change the rounding setup for your price lists.
-Victoria
LikeLike
Thanks Victoria, but I am confused. We have the IV00105 LISTPRC set at 2763.60 and it is showing 100% as 9500.00 and 30% at 6650.00. When I look at the “Options” from the Item Price List Maintenance window, I have 100% UOM = “EACH” with Selling option Whole, Round up, multiple of 9500….. same change price for 30%. Is this just set up incorrectly? Should this be set differently or wiped out for all of our items so the list price is properly used at the relative percentage rates.
I imagine that the purpose would be to maybe designate the pricing to be set to a division of 1.00 if I want only whole dollars and no change or maybe .25 if I want quarter increments right? But if I don’t care what the change amount is on 30% off then I would just remove these options all together right?
LikeLike
Allen,
Am I understanding correctly that you have the list price set to 2763.60 and the rounding option to be multiples of $9500?? I guess this will work, but it’s really not how the setup was meant to be used and is a bit counter-intuitive for anyone that needs to maintain item price lists. Yes, what you are saying at the end is correct, the idea of the options is to round to something – many companies choose to have all their prices end is the same amount, like $0.95 or $0.99…or to always round to whole dollars. If you do not need this functionality, there is no reason to use the options, without which I would think the pricing setup would be much simpler for any new items or changes needed.
-Victoria
LikeLike
Victoria,
I am working in eConnect to create inventory items and prices in DynGP10. What is the relationship between the PriceListHeader and PriceListLine for a multi-currency environment? I see that the IV00107/IV00108 tables are updated when I add a pricelist thru eConnect but they seem to be updated in a one-record-each-table instead of a parent-child relationship. Are there other IV tables for PriceLists?
–Chad L
LikeLike
Hi Chad,
I don’t know if I would call this a parent/child relationship.
IV00108 is the Price List – this will hold what you see on the Item Price List Maintenance window in GP.
IV00107 is the Price List Options table – this will hold what you see on the Item Price List Options Maintenance window.
Unless you have quantity breaks for your pricing, you will have the same number of records in each of these tables. If you have pricing based on quantity, you will have more records in IV00108.
-Victoria
LikeLike
This is a wonderful blog. Thank you for all your efforts.
I need to get a list of item with extended pricing given customer Number. Do you think it’s possible? I’m trying to get that information using eConnect and I’m very new to GP and eConnect…
Detailed explanation:
I’m using Dynamics GP 2010. I have setup extended pricing for my company. Now given customer number, how can I get a list of item with their corresponding pricing? For example, I have two products A and B. I have two customers cus1 and cus2. For cus1, A is $10, B is $5. For cus2, A is $20, B is $10. I need to get a XML document back via eConnect’s requester – pass in cus1 and I get A|$10 and B|$5 (I know it should be XML, just trying to explain concept here) and pass in cus2, I should get A|$20 and B|$10.
Seems like I can try to create a new transaction requester document type by add a new record to eConnect_Out_Setup table. But I’m having a hard time to come out a query to do what I want. I’m a brand new GP developer. If you can give me a query that can do what I want (or the tables I need to work with), I might be able to create the new requester document type by myself.
LikeLike
Hi Jeff,
Thanks for your kind words. I don’t work with Extended Pricing, so I am not the best person to help with this. I see that you have also tried to get help on the GP Customer Forum and have not gotten any replies. I would recommend talking to GP Support to see if they can help you with this.
-Victoria
LikeLike
Victoria
I have what should be an easy question, but I can’t find an answer. For stock counting is the next count date a data field that I can get to using Crystal or is a calculated field that is not stored. I found the current count data but I can’t find a field for the next count date.
Patrick
LikeLike
Patrick,
I believe that is the NXTCNTDT in the IV00102 table.
-Victoria
LikeLike
Boy do I look foolish. I looked at that table 3X’s. I guess sometimes you truely can’t see what’s right in front of you. Thanks for the help.
Patrick
LikeLike
Hi Victoria,
Is it fine to directly update UMSLSOPT field in IV00107 table using sql query as I could’t find this field in integration manager when tried to create an integartion. If not, is there a way I can update UMSLSOPT field for a list of items for certain price level?
LikeLike
Hi Zafar,
It looks like you’ve already got some great feedback on this in the GP Customer Forum.
-Victoria
LikeLike
Thank you everyone. I have got some great feedback on this issue through Microsoft Dynamics Community forum thread named:: Updating fields which are not available through Integration Manager.
LikeLike
Victoria I have a question regarding the item master table and check links. The database I work on has had little to no maintenance for years. Yesterday on our test data base I ran Check links on the sales section to try and remove what appeared to be orphan INV records. When it completed I found it had removed a large group of our part numbers. I have check the part numbers against our live database and I can’t find any connection some were old, some were new, some had a little history, some had a lot. Any ideas why check link removed these from the IV00101 table (I checked using sql)
LikeLike
issue resolved. The test master was old and we had added some customer numbers through SQL that that didn’t have a match.
Patrick
LikeLike
Patrick,
Great! Thanks for following up and letting me know. I was just getting ready to write and ask if you can recopy your live database to test and re-run the process to see if the same thing happens. Glad to hear you were able to track it down quickly.
-Victoria
LikeLike
Victoria,
I have several hundred inventory items showing up with negative or incorrect allocation amounts. What would the best approach on correcting this issue and determine what caused it in the first place?
Regards,
Raul
LikeLike
Raul,
First thing I would try to fix this is to run Inventory Reconcile.
I doubt if anyone can easily tell you what caused the issue without extensive investigation into your GP environment and usage.
-Victoria
LikeLike
Victoria,
Thanks for your prompt response. I tried to reconcile a handful of the items and I got you can’t complete this process while transactions are being edited, so my guess there is something hung out in the system. I’m looking to see if I can identify if there are any stock count associated to the items that may be running or hung.
Raul
LikeLike
Raul,
Do you use multi-bin and kits?
Stefanie
LikeLike
Is there a way to do this that you can think of:
I wanted to see if the system was able to implement a notification for negative inventory? When we write an order for a customer and the inventory goes negative it will give us a pop up box that shows what we have on hand and lets us know that we are going to go into the negative. I was wondering if there could be a box that would pop up that would list any orders in the system that are containing the items that are going negative. That way we could see the orders right there in front of us. That would help to show if there are tickets that are wrote that may be going out for delivery in a couple of weeks but the customer would like them wrote now to secure the material while we have it on hand. That way we do not double sell material and cause problems and delays for our customers. We would be able to see all tickets that are containing the product and help to refresh our memories on special orders that might be placed. it would also help to keep an eye on inventory. If the negative inventory box pops up and there are no orders that contain the item and we have the material on hand then we know that there needs to be investigation on that item to help find where we are off. I would like to discuss it further once you have time to go over it so that way we can really understand what I am trying to describe. Thank you for your time and let me know what you think. Thank you!!
LikeLike
Larry,
If I am understanding you correctly, this functionality is already available in GP’s Available to Promise module. Whether you have it or not would depend on the modules you own and/or what licensing mode of GP you have.
-Victoria
LikeLike
I have never heard of this module. Who would i talk to about it?
LikeLike
Larry,
I would start with your GP Partner.
-Victoria
LikeLike
Hi Victoria,
Thanks for taking the time to compile this information. I am trying to build three reports in CrystalReport:
1. Bank loan (bank draw down) monthly
2. Inventory COGS (cost of goods sold) (monthly)
3. Inventory Turns by Location
I am having a hard time finding the views/tables in the GP database which would have this information. Can you point me in the right direction?
LikeLike
Hi Janell,
There is no easy answer this without knowing the full specifications of what your reports need to accomplish and how the relevant data is being entered into GP.
1. Bank loan – since there is no standard ‘bank loan’ concept in GP, this would very much depend on what this means to your company and how/where this data is entered. Is it a GL account? A vendor? Something else?
2. Inventory COGS – this is something typically found in the GL. So, identify the GL accounts that record COGS and use the details in GL20000 or GL30000 for those accounts.
3. Inventory Turns – there is a report called Turnover Report in GP. It’s under Reports > Inventory > Analysis. If that’s not what you need, you want want to define what your report needs to do and where the data is populated. This may not be so easy to do by location, as it’s often a report based on GL numbers, which may not be separated out by location.
-Victoria
LikeLike
Thank you so much, Victoria.
LikeLike
Hi all GP9 SQL2005 w/ manufacturing
We are going through Stock Count activities currently. When trying to process a particular stock count ID, we get the exception message: “You can’t complete this procedure while Inventory decimal places are being changed” and subsequently cannot process the schedule. There is only one item listed in the exception list.
Since I am the only one that runs this decimal places change routine, I know I didn’t do any recently and certainly not before the stock counts were started. Any ideas where this may be stuck?
Help always appreciated.
Stu
LikeLike
Stu,
With all users out of GP, check the SY00800 and SY00801 tables in the DYNAMICS database and the DEX_LOCK and DEX_SESSION tables in the tempdb database. Those should all be empty if there are no users in GP. If they are not, some of the records there could be causing the issue.
-Victoria
LikeLike
Hi Victoria,
We are running FIFO Periodic as our costing method, I need to report on Material vs Labor cost elements. What table(s) can I pull to get labor/material cost on a inventory item.
Thanks.
Jeff
LikeLike
Jeff,
This is not something I can easily answer, sorry. I would recommend posting your question on the Dynamics GP Customer Forum to see if anyone there can help you.
-Victoria
LikeLike
Hi Victoria,
Can you please help me finding which tables are related to the “Item stock Inquiry” Window?
LikeLike
Yassir,
There are many, here is the list: IV00101, IV00102, IV40700, IV30300, IV40202, PM00200, POP10150, RM00101.
-Victoria
LikeLike
Hi Victoria,
I need to report on Serial Numbers on Hand. I can find the serial number transaction history in IV30400, however, I can not locate where serial numbers are tied to units on hand.
Thanks for your help!
JTH
LikeLike
Hi Jeff,
I think you’re looking for the IV00200 table. I just updated the table list on this page to include it.
-Victoria
LikeLike
We have some inventory transactions that we entered, but the person who entered them forgot to check post to general ledger. Now these are posted but not to the GL. I was wondering how I get these to post to GL? Thank you
LikeLike
MC,
At this point, the easiest fix is to manually enter the needed GL transaction. For the future, you can ask your GP Partner to obtain the free Add Item customization from Microsoft – this will automatically check the Post to GL button on all Inventory Transaction batches and no one has to remember to do it.
-Victoria
LikeLike
how do they do this, transactions, financial, general?
LikeLike
MC,
Yes.
-Victoria
LikeLike
Hi Victoria,
Thank you for your site. Very informative.
I run GP 10 with a 3rd party inventory matrix product. We did some inventory adjustments through Inv>Transactions>Transaction entry. The batch was posted, the transactions appear in Inquiry, but the adjustments did not affect the item qty’s.
Any suggestions?
Bruce
LikeLike
Hi Bruce,
Thanks for the kind words.
First thing I would do is double check that there is no different way to enter adjustments in conjunction with the 3rd party product you have. Taking the 3rd party product out of the equation, it sounds like the posting of the batch possibly did not complete correctly and I would recommend running Inventory Reconcile to see if that will correct the quantities. Again, you will want to make sure that there is no issue with running Inventory Reconcile and your 3rd party product. (Or they may have their own reconcile process.)
-Victoria
LikeLike
Hi Victoria
I saw your site very recently. I really appreciate the work you do to help others.
i was trying to create a report where i need to find out the available quantity of an item for a selected range of receipt dates. Any views or reports which you might have done already!?
LikeLike
Thanks Jubith!
This might help as a start for your report: https://victoriayudin.com/2009/06/18/sql-view-for-inventory-quantities-on-hand/. Table IV10200 holds all inventory receipts and sales against them, so you can compare Quantity Received (QTYRECVD) against Quantity Sold (QTYSOLD) by line item.
-Victoria
LikeLike
Hi Victoria
That was a quick response. I am also interested to get the lot numbers (expiry date) as well.
thanks.
LikeLike
Hi Jubith,
I don’t have anything published with lot numbers, sorry. I have not tested this, but it looks like you should be able to link your receipts from the IV10200 table to the lot information in POP30330.
-Victoria
LikeLike
Hi Victoria,
Congratulations on winning the MVP award!
I have an Inventory Transfer Batch that appears to have posted, yet is still stuck in the Work Tables. The exact same records appear in IV10000/IV30200 and IV10001/IV30300. The transfer hit the GL because there are records in GL20000 for the corresponding Inventory Transaction Source. When we look at Item Inquiry for one of the items in the Transfer, it shows the quantity from the transfer as being allocated. We click on the Allocated link and the Item Allocation Inquiry Window opens and listed is the Item Transfer Document. We click on the link to open the document and receive “This batch has been marked for posting by another user”. I ran Check Links for all Inventory and Items. There were no errors. So, it appears that the Item Transfer was successful, but somehow didn’t get cleared out of the Open Tables.
Any suggestions?
Thanks.
Brent
LikeLike
Hi Brent,
I have seen this before, though not often with Inventory transactions. My guess is that a connectivity failure or some similar issue at the very end of the posting process prevented the unposted transactions from being deleted from the work tables. If you are confident that the transactions have posted to Inventory and GL, I would delete the unposted ones. You know the drill – make backups first, etc. before making any changes like this. 🙂
-Victoria
LikeLike
Thanks for the quick response…Are there any other OPEN (10000s) tables that I should be looking at in addition to IV10000 and IV10001?
LikeLike
I forgot to ask…do I run Check Links (which ones) and/or Item Reconcile after I delete the records?
LikeLike
Brent,
I would delete them from IV10000 and IV10001 and then run Check Links on the Inventory series. I always prefer to run the whole series if possible, but if you’re pressed for time, you can try running just the Inventory Transaction Work logical tables.
-Victoria
LikeLike
Hi Victoria,
Thanks for your responses. I followed what you wrote and backed up the GP company db, deleted the records from IV10000 and IV10001 and ran Check Links on the entire Inventory Series. Unfortunately, the allocations were still off. So, I ran Inventory Reconcile for the 49 affected items and the allocations were corrected.
Thanks for all of your help.
Brent
LikeLike
Hello Victoria,
I have several reports that tie into IV00101. I have a field IV0010.ITEMSHWT that pulls in the item weight that range from .01 to 999.
Without formulation the items weight that is less than 0 comes in as 10 and for items that weigh more it reads with two additional 0’s for example 2500.
I have dynamically changed the decimal in the format field custom symbol number to the following: decimals: 1, rounding: 1, negatives: -123, decimal separator: ., thousand separator symbol: , , and leading zero and the results are the 2500 now displays as 25 but the less than 0 is just 0.
My issue is I need the zero and less to show .01 and not 0.
Is there a solution that you can share with me?
Thank you in advance,
Cindy
LikeLike
I wanted to add that in Crystal I have a SQL Expression Fields that states: IV00101.Itemshwt / 100 that gives us the 0 instead of 10.
Thanks.
LikeLike
Hi Cindy,
I am guessing from your second post that you are using Crystal Reports for this. If so, it may be that you’re overcomplicating this a little. Why not create a formula that looks like this:
{IV00101.ITEMSHWT}/100and use that for the shipping weight? I just tired it and it works perfectly for all the scenarios I have tested. Or am I missing something in your requirements?
-Victoria
LikeLike
Thank you very much for your response.
Yes I am using Crystal Reports version 11. In a sequel I have : {IV00101.ITEMSHWT}/100 and that reduces the amount of zeros so when Dynamics on the Item Maintenance window has the shipping weight for example :39.00 when it comes over to Crystal Reports as :3,900. The sequel expression take the 3,900 and reduces it to 39.00 and that’s the way we want it. The problem I’m having our products that weight less than 100 pounds do not read correctly. The sequel expression reduces the weight as 0. Is there a way to have it show up as .10?
LikeLike
Cynthia,
I have not tested this with a SQL Expression, only with a formula, however, if I have 0.10 entered for the weight in GP, my Crystal report using the formula above brings in 0.10. Can you try taking off any formatting/formulas you might have on that field? I think you mentioned in one of your posts that you have a formula on the decimal places?
-Victoria
LikeLike
Hi Victoria,
I appreciate the information provided here, it is of great help.
My question is: Is there any way to create or modify a stock count schedule so that it can be counted by bin location?
LikeLike
Hi David,
I don’t see any way to restrict by bin location when adding items to a stock schedule. Blue Moon has a product called QualityCount that may let you do this, here is a link: http://products.bluemoonind.com/productDetails.php?product=QualityCount.
-Victoria
LikeLike
Hi Victoria,
We are running a single currency (USD), we had a user change the decimal value to add digits to the right of the decimal place. It was a mistake and now we are trying to roll back, however, the GP ui does not allow you to roll back to 2. Do you know what table houses this data? My table catalog says it should be MC00100, however, that table is not found in our instance of the application. Thanks for your help.
JTH
LikeLike
Jeff,
If you are talking about quantity decimal places, that is stored in IV00101. Any table starting with MC is a Multicurrency table, so if you are only using one currency, it would be unusual for you to need any Multicurrency tables.
-Victoria
LikeLike
Hi Victoria,
Do you know of any issues with the Shrinkage Factor field in IV00102? The field is in the Item Resourse Planning Maintenance and I can get other fields to pull into Crystal, but this particular field just brings in 0.00. We have noticed that it doesn’t pull in SmartList either. Just curious if you know of any ways to manipulate?
Thank you,
Jill
LikeLike
Hi Jill,
This field stores percentages with more decimals than you might think. For example, 0.15% is stored as 0.00150. So you will most likely need to create a formula to multiply that field by 100. Also, from a quick glance, the Shrinkage Factor is only stored at the ‘summary’ level, so only on records with a RCRDTYPE = 1.
In general, when in doubt (and if possible), I try to look at the data directly in the SQL tables. It often helps find the patterns in the data. 🙂
Hope that helps.
-Victoria
LikeLike
Victoria,
I needed to look up this infomation on the IV40400 table for a report I needed to write and thoguth I would share it with you. I am not sure if you would add this or not to your Inventory table but here it is. I needed to look up all of the Item Classes in the system and their corresponding defalt GL accounts. Here is the table and field information.
IV40400 – Item Class Setup
IV40400 Account Indexes:
IVIVINDX = Inventory On Hand
IVIVOFIX = Inventory Offset
IVCOGSIX = Cost of Goods Sold
IVSLSIDX = Sales
IVSLDSIX = Markdowns
IVSLRNIX = Sales Returns
IVINUSIX = In Use
IVINSVIX = In Service
IVDMGIDX = Damaged
IVVARIDX = Variance
DPSHPIDX = Drop Ship Items
PURPVIDX = Purchase Price Variance
UPPVIDX = Unrealized Purchase Price Var
IVRETIDX = Inventory Returns
ASMVRIDX = Assembly Variance
Ron
LikeLike
Ron,
Thank you very much! These are also in the IV00101 table and I can see them being very useful for anyone needing to look this detail up. I have added the list to the page above.
-Victoria
LikeLike
Hi Victoria,
I found out that the users did not tick to maintain history for the item master. Was that the reason why there are no records in IV30102 even though there had been many sales transaction for the items?
After noticing this, I had ticked the checkbox for Maintain history. Is there anyway I can get records updated back to IV30102?
Any advice is greatly appreciated.
May
LikeLike
May,
Yes, not checking the maintain history checkboxes will cause the data not to be tracked in the history summary tables, as well as numerous others. I recently had a similar request from one of our customers and spoke to GP Support about this – the only option they had was to populate the history summary as a consulting engagement.
-Victoria
LikeLike
any one know how to resolve inventroy quantity difference.
i catch the item but i don’t know how to get other detail.
thanks
LikeLike
I am not sure what you mean. Can you please give some more details on the issue you are having or what you are trying to do?
-Victoria
LikeLike
Hello
Can you point me to the proper table(s) to find BIN info?
LikeLike
Bill,
There are a few, depending on what you need:
IV00113 – Item Site Bin Master
IV00117 – Item Site Bin Priorities
IV40701 – Site Bin Master
Hopefully one of these is what you’re looking for.
-Victoria
LikeLike
IV40701 works great!
Thanks
Bill
LikeLike
Victoria,
Each inventory item has a Note Index number which, I’m sure, is a reference to data within another table. Do you know what table stores all of the note indexes? I would like to pull them out in smartlists and reports.
Thanks!
LikeLike
Bill,
All notes for the entire GP company are stored in the SY03900 table. You would link your IV00101.NOTEINDX to the SY03900.NOTEINDX to get the contents of the note.
-Victoria
LikeLiked by 1 person
Hi Victoria,
Is there a way one can update the item notes (TXTFIELD in SY03900 corresponding to NOTEINDEX for items in IV00101)through automation? I tried it through integration manager with input text file having Item number and note fields. The integration completed without any error, however, the notes were not updated.
Thanks.
LikeLike
Hi Zafar,
If it’s not working with Integration Manager, you could certainly update this directly in the database.
-Victoria
LikeLike
Victoria,
The update to the status did not make a difference.
It had about 50,000 items so I deleted from the unposted stock count table IV10301. Then went in through GP and deleted the stock count ID.
Thanks,
Raul
LikeLike
Raul,
Thanks for the update. For the future, there is a ine in the DEX.ini file that by default says:
STOCKCOUNTLEVEL=10000
You would want to change this if your stock counts have more than 10,000 line items. This setting is machine specific.
-Victoria
LikeLike
Victoria,
There is a stock count schedule that a user is trying delete but unable to do so through but locks their machine up after it is pulled up onto the screen. I checked on the table side and noticed that the stock count status is 0 which is not listed above. Is this the problem and be updated to a valid entry of 1 thru 3 then try deleting it or is there more to it than it appears?
Thanks in advance for valuable knowledge in resolving it,
Raul
LikeLike
Raul,
I have not run into this issue before. You could certainly try changing the status and see if that makes any difference.
How many lines are on the stock count? It may be it’s locking up because it has too many lines. I believe there is a setting in the dex.ini file that can be changed to address this.
-Victoria
LikeLike
Hi Victoria,
The site is very good for people like me. I am getting more knowledge using this site.
I am new to GP and my understanding is that, Item Stock Inquiry window is used to track all transactions for the items with UOFM. It will keep all types of records (sales, purchase, receipts etc) for the item number. But I am checking few document number but they are not in IV30300 table. For example, I have placed a sales order, processed and posted in GP. I think posting needs to create the transaction entry on IV30300 table but there is no entry for the posted sales order in IV30300. I don’t know why?
Note : It is not happening for all sales orders but for few.
Any advice is appreciated.
Thank you in advance.
Regards
Solomon
LikeLike
Solomon,
There are many reasons why you might not see a record of a sale in the inventory history tables. Without looking at the particular scenario and GP setup in detail, sometimes it is impossible to determine. That said, the 2 most typical scenarios I have seen are:
– the item was not an inventory item when added to the sales order
– the item was not set to track history
-Victoria
LikeLike
Hi Victoria,
Hoping you can help me….we are trying to get a report to help guide us on reserving for inventory obsolescence and I’m trying to find a “last transaction date” field in the inventory tables. Is there on available that you know of?
Thanks in advance!
Jill
LikeLike
Jill,
The table you want is IV30300. There is a lot of different information in this table, but if all you’re looking for is the last date anything was done with a particular inventory item, then find the latest DOCDATE for the item. If you need to distinguish between last receipt date vs. last sale date, you would need to add quite a bit more logic.
-Victoria
LikeLike
So there is no “Stored” field containing the last activity date that can be queried? I need to make a list of all items that were used in the current month, without getting all transactions.
LikeLike
Cheryl,
There is no place in GP that stores ‘last activity’ for an item across all modules and types of activities. If that’s what you need, you will need to look in multiple places and then consolidate the detail into the report you need.
-Victoria
LikeLike
Hi Victoria,
We are getting closer to the end of the year and thus we’ve begun to look at Stock Counts and doing a Physical in GP. I looked at Integration Manager and did not see a destination that would allow me to load my stock counts. Do you know if Integration Manager or eConnect is capable of loading the stock counts? If not, what are your thoughts on loading the counts via SQL?
Thanks.
Brent
LikeLike
Hi Brent,
I am not a big fan of importing transactional data directly into SQL. Here is a thought – why not just use the Inventory transaction destination in Integration Manager for what you need?
Example: export your stock counts into Excel, enter your actual stock into Excel (was that you that said on a newsgroup somewhere that you scan it into Excel?), calculate the variance in Excel, then use Integration Manager to import an inventory adjustment for the variances.
-Victoria
LikeLike
Hi Victoria-
I’m currently in the middle of a conversion to GP from our legacy system and your website is my “go-to” resource! Thanks!
I just populated our inventory items using Integration Manager, but my ABC code did not come over into the Item Maintenance Options window. The source file has each item coded with and A, B or C…because this is a picklist in the Item Maintenance Options window, should my ABC values be different? Any suggestions are appreciated. Thanks Victoria!
LikeLike
Hi Katrina,
Thank you very much for frequenting my site!
I just tested this with GP 9.0 SP 3 using A, B or C in my source file and it came through with no problem. I have not imported ABC codes too often, but I have not run into any issues with this on the past. You might want to ask this on the GP Newsgroup to see if anyone else has seen this issue – you’ll have a wider audience with more diverse experience there. Be sure to mention what GP and IM version and service pack you are on.
-Victoria
LikeLike
Thanks Victoria – I just went into resource descriptions for fields and found the the static values for the ABC (2,3,4 respectively). I changed those in my source file and they came over no problem. Lesson learned by me: Always check the field info 🙂 I’m a newbie but learning more and more every day!
LikeLike
Katrina,
Glad you were able to resolve this. Interesting that the A, B, and C worked just fine for me. What version/build of IM are you on? I have seen some things not work correctly on early builds of IM 10.0…
-Victoria
LikeLike
I thought that was interesting too. It must be the reason why yours worked and mine didn’t, I’m on version 10.00.1305.
LikeLike
Katrina,
Even stranger…I just tested this with GP 10.0 SP 4 using IM 10.00.1221 (SP 3) and IM 10.00.1378 (SP 4) it worked with A, B and C on both those versions.
-Victoria
LikeLike
Hi Victoria,
I reference your website daily…it is a great tool.
We have some Item quantity levels that appear to be out of sync. If we go by the same Item and Site ID (assuming Inventory Reconcile has been run and there are no issues with the Item), should the following three things produce the same number for On-Hand Quantities:
1.) Item Inquiry
2.) Item Transaction Inquiry
3.) Reports | Inventory | Activity | Historical IV Trial Balance
Thanks so much for your help.
Brent
LikeLike
Hi Brent,
Thank you very much for the kind words and for frequenting my site!
I believe that if all the transactions were entered in GP and there were no issues or data manipulation directly in SQL, then the on-hand quantity on the Item Inquiry screen should equal the sum of all the ins and outs on the Item Transaction Inquiry screen. The Historical Trial Balance report is a completely different story and I have seen cases in the past where it does not always show the correct data. I would also not use that report to compare to ‘current’ on hand quantities – instead I would use the Stock Status report.
That said, I have seen some very complicated inventory issues crop up in my travels…I have also seen 3rd party products and customizations sometimes cause issues with quantities. So if you’re having recurring or lingering issues, you may want to get your GP Partner or Dynamics GP support involved to help you get to the bottom of this.
-Victoria
LikeLike
Hi Victoria,
Thanks for the response. I’m hoping the problem is small or else I’ll have to get our Partner or Microsoft involved. Perhaps you can explain a few things about the tables for me.
1.) Are tables IV10200 and IV10201 open or history?
2.) Do records stay in table IV10200 forever? If no, then when do they move and where do they go to?
3.) Is there any connection between tables IV10200 and IV30300…IV10201 and IV30301?
As usual, thanks so much for all of your help.
Brent
LikeLike
Brent,
Disclaimer: For most of this my answers are deductions based on what I have seen in the data I have worked with, not the ‘official’ answer.
The IV10200 and IV10201 tables get cleared out when a year is closed in the inventory module, so in that sense they are ‘open’. GP actually lists them as ‘work’ tables. When the year is closed and records are cleared from this table, I do not believe they are moved anywhere, as they are already in the IV30300 and IV30101 tables. Not sure what you mean by ‘connection’ between the tables, but I would describe it as IV10200 and IV10201 are subsets of the IV30300 table. So when creating reports, I would use the IV30300 table, not IV10200 or IV10201.
-Victoria
LikeLike
I have a similar question (use FIFO Perpetual).
I am looking to connect Inventory that has been Transferred back to its original Purchase receipt (it is possible that the inventory has been transferred more than once). I can do this as long as the original receipt records are in the IV10200 and IV10201 tables, but this relationship becomes fuzzy when I only have the IV30300 to look at.
For example, In the IV30300 I can see the Inflow to SITE1 referencing the PO Receipt, and then the Inflow to SITE2 referencing the Transfer transaction, but I do not see a way to identify the outflows from SITE1, so when I have several transaction lines, it is not possible to see which Receipts go with which Transfers.
My reason for wanting to do this is so that I can maintain visibility of Landed Cost details for Inventory after it has been moved from its original receipt location.
Thanks for any advice you might have.
Kurt
LikeLike
Hi Kurt,
Looking at it briefly, I am not seeing a way to track this without using Lots or Serial Numbers. 😦
-Victoria
LikeLike
Does anyone know of a query that could give me the current cost, the sell price of our items?
LikeLike
MC,
Sorry, but there is no one answer to this, as it very greatly depends on your specific setup.
-Victoria
LikeLike
Hi Victoria,
I have a confusion over following tables.
– Item Quantity Master
– Item Price List
Can you please give some details over these?
My concern is, how you are keeping each individual item of assume 5 quantity cost of 100 $ and than the same another 2 items cost of 50$?
Should the Item Quantity Master table be like this?
Item Id| Receive Voucher Id| Unit Cost| Status|
1 | 1
1 | 1
1 | 1
1 | 1
1 | 1
1 | 2
1 | 2
LikeLike
Imran,
The item quantity master only holds quantities, no costs. The price list is a list of what you charge customers, again, not cost. To find the cost of the inventory on hand you could use the IV10200 (purchase receipts) table. Also, my SQL view for inventory quantities on hand post may help.
-Victoria
LikeLike
Hi Victoria
I am trying to setup the Item lookup window to default to an advanced lookup list. instead of viewing all inventory items with no search criteria the list should default using my restricted list created via Smartlist as a Favorite.
Do you have any idea how I can go about doing this?
I have tried Field Security but I am not getting anywhere.
regards,
NG
LikeLike
Hi NG,
This would have to be a customization. You could either ask your GP partner for this, or if you are looking to do it yourself, you could try posting a question on the GP Newsgroup or checking out some of the more technical blogs on my blogroll.
-Victoria
LikeLike
Hello Victoria
Thanks for providing this ad-hoc repository of invaluable information. It has eased my introduction into Great Plains no end.
I have been asked to make the “Inventory Turns Report” (GP9 – Reports|Inventory|Analysis|Turnover Report) available in electronic format. I can see no other way than manipulating the data after an extract to excel.
I have tried to recreate the detail via SQL but cannot seem to generate the ‘Summary of each months ending on hand inventory’ (as outlined in various docs – for example Article KB856731 on Cust Source).
Have you had to recreate this report in the past for Crystal Reports/SSRS or do you know of a solution to do so? Any help is greatly appreciated.
Regards
Stu
LikeLike
Hi Stu,
I have not created a report like this. The only other thing i can suggest is that you trace what stored procedure is being called what that report runs in GP and then see if you can use it in your report, possibly by using it in your own stored procedure.
-Victoria
LikeLike
Hello Victoria,
I am working with a client and we want to store product categories in a comma-separated format for each inventory item. The User Defined fields seem to be too short to hold the data. We want up to 64 characters of space if possible. Is there any way to do this for each inventory item?
Thank you once again for your help.
LikeLike
John,
I think Extender might help you accomplish this.
-Victoria
LikeLike
Thank you Victoria, we will look into this option. I appreciate your willingness to assist.
JO3
LikeLike
Victoria,
I am running GP 10, when i receive the inventory in multi currency the system create split purchase receipts since it cannot arrive at the cost of item at BUOM, since i have many cases like this which keeps happening….
and this some differences in each trx will accumilate to a good amount, and it any point of time i will have difference between the GL inventory control account and my Inventory cost from Sub Ledger….
how should i resolve this issue, i am using Average Perpetual valuation method.
Please guide me in this regard….
How to resolve the reco between my inventory and GL accounts.
Thanks for your guidance
Akram.
LikeLike
Akram,
Here is a post from the Microsoft Dynamics GP newsgroup that might help with your question: How to reconcile Inventory to General Ledger in GP?
-Victoria
LikeLike
What fields/tables are affected on the purchase or sale of an inventory item? I am integrating items based on their change in price or quantity and want to filter only those items that have changed since last integration. Along with the fields/tables on purchasing and sales of items, are there any other transactions that affect the quantity of an item?
Thank you for your help!
LikeLike
John,
There are a lot of tables and fields that get updated by purchases and sales. The precise tables and fields will also depend on the details of your transactions and the modules you are using. If you need a complete list I would recommend running SQL Profiler and entering the transactions that you are trying to trap. Additional transactions that may affect quantities are: inventory transactions, bill of materials transactions, manufacturing transactions, and sales/purchasing returns. There may be more depending on what modules and additional products you are using.
-Victoria
LikeLike
Raul,
When an item gets assigned to a site a new record for the item/site combination gets created in the IV00102 table.
-Victoria
LikeLike
Victoria,
I’m trying to do some maintenance cleanup in our inventory and was wondering if you could tell me what tables are used when an item gets assigned to a site or location?
Thanks!
LikeLike
Hi, Thanks for posting this information. I’m tring to use it, but still have trouble actually finding which tables hold the information I’m looking for. Do you have any additional resources for that?
Thanks!
LikeLike
Susan,
There is a section called More Online Resources for GP Reporting on my GP Reports page that might help. If you’re still not finding what you need, I recommend posting a question on the Microsoft Dynamics GP Newsgroup, there are lots of very knowledgeable folks out there.
-Victoria
LikeLike
Hello,
I appreciate the work you have put in to help get others acclimated with GP. Thank you.
I want to know if there is a way to check to see if an inventory item has been updated based on a date field. I have noticed that if I update an item through the main Item Maintenance window, this is reflected in the date. However, when I change a price level in the Item Price List Maintenance window, the date modified field doesn’t change. Is there a date field that gets updated when a price level changes in the Item Price List table?
Thank you!
LikeLike
John,
I don’t see any date fields available for you to track changes to the price list table. You could use an auditing add-on or add a SQL trigger to write the information to your own table to track changes to pricing.
-Victoria
LikeLike
i should add that the reason i want to do this, is so i can disply the revision level of the parts being ordered on the PO form. The only place i see the item revision level field available is in the MFG tables….i cant seem to get the data to cross over though =(
LikeLike
Hi Jon,
So you’re looking to do this in Report Writer? If so, you may not be able to add Manufacturing tables without writing a custom function. However, this is not really my area of expertise, as frankly, I try to avoid Report Writer if I can help it. 🙂 Have you tried posting your question on the Dynamics GP community forum? You might find someone who can help more specifically with this question.
-Victoria
LikeLike
thanks victoria! yes, I am personally trying to do this in report writer, but there are others here who are just trying to create smartlists that pull data from both the GP tables and MFG tables. I will dig around for answers on the report writer side of it…any suggestions for those relationships in GP itself/smartlist builder? thanks!
LikeLike
Jon,
For SmartList Builder, I always recommend creating a SQL view first. There you can join whatever tables you want in whatever way you want and add whatever logic you need. Once you have a SQL view, simply point SmartList Builder to it and you’re good to go.
-Victoria
LikeLike