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
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: Jun 3, 2015
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:
https://victoriayudin.com/2011/03/29/sql-view-for-last-sale-by-customer-and-item/
-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.