Inventory Tables


Commonly Used Tables:
IV00101 – Item Master
IV00102 – Item Quantity Master
IV00103 – Item Vendor Master
IV00107 – Item Price List Options
IV00108 – Item Price List
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

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: June 11, 2012

384 Responses to “Inventory Tables”

  1. 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

    Like this

    • 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

      Like this

      • 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.

        Like this

        • 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

          Like this

          • 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.

            Like this

  2. 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

    Like this

    • 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

      Like this

  3. 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

    Like this

  4. 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 :)

    Like this

    • 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

      Like this

  5. 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,

    Like this

  6. 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

    Like this

    • 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: http://victoriayudin.com/2010/04/23/how-to-find-all-sql-tables-with-a-column-name/.

      -Victoria

      Like this

      • 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

        Like this

  7. 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.

    Like this

  8. 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!

    Like this

    • Ty,

      Try the IV30400 table – that stores the Serial and Lot Number History.

      -Victoria

      Like this

      • 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!

        Like this

        • 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

          Like this

  9. Carleesha Silvera Reply August 16, 2013 at 1:57 pm

    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.

    Like this

    • 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

      Like this

  10. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  11. 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!

    Like this

  12. 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!

    Like this

  13. 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

    Like this

    • 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

      Like this

  14. Leave a Reply text box is disabled

    Regards
    Zafar

    Like this

  15. Hi Victoria,
    Can you tell in which table decimal places value stored . (Tools->setup->Financial->Multicurrency ->Functional currency-> Currency format)

    Like this

  16. 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?

    Like this

    • 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

      Like this

  17. 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!

    Like this

    • 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

      Like this

      • 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?

        Like this

  18. 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!

    Like this

  19. 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?

    Like this

    • 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

      Like this

      • 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.

        Like this

  20. Hi Victoria,
    Any idea? how can we find when an item status was changed from Sales Inventory to discontinued?

    Like this

    • 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

      Like this

  21. 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

    Like this

    • Hi Henry,

      Sorry, I am not aware of anything like that.

      -Victoria

      Like this

    • 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!!!

      Like this

    • 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.

      Like this

      • 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

        Like this

        • 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

          Like this

      • 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

        Like this

      • Sean- I am also looking for mthis solution. Can you send it to me also?
        petefols@yahoo.com

        Like this

  22. 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?

    Like this

    • 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

      Like this

  23. 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.

    Like this

  24. 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.

    Like this

  25. 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!

    Like this

    • 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

      Like this

  26. 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.

    Like this

    • Hi Pete,

      Thanks for your kind words. Unfortunately, the user who created the item is not stored by GP anywhere. :-(

      -Victoria

      Like this

    • 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

      Like this

  27. 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!

    Like this

  28. 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

    Like this

    • 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

      Like this

  29. 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?

    Like this

    • 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

      Like this

  30. 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!

    Like this

    • 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

      Like this

  31. 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

    Like this

  32. 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?

    Like this

    • 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

      Like this

  33. 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.

    Like this

    • 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

      Like this

      • I agree with Victoria on that one. I have used it to load several thousand items into the system and it works well.

        Like this

        • 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

          Like this

          • 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

            Like this

            • 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

              Like this

              • 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

                Like this

                • 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

                  Like this

  34. 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

    Like this

  35. 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.

    Like this

  36. 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.

    Like this

  37. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

          • 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

            Like this

            • 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:

              select ITEMNMBR Item, 
              LISTPRCE ListPrice, 
              CURNCYID Currency
              from IV00105
              

              -Victoria

              Like this

  38. 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?

    Like this

    • 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

      Like this

  39. How to findout total stock of kit item ?

    Like this

  40. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  41. Hi Mark/Victoria,
    Can we not do it through integration manager?

    Like this

  42. 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

    Like this

    • 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

      Like this

    • 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?

      Like this

      • 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!

        Like this

  43. 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

    Like this

    • 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

      Like this

  44. This late was a HUGE help. Very nice Victoria!

    Like this

  45. Victoria

    Can you tell me what PSTGSTUS (Posting Status) of 35 in table IV10000 means?

    Like this

    • 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

      Like this

  46. 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.

    Like this

    • 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

      Like this

  47. Claire van der Merwe Reply October 24, 2011 at 9:04 am

    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

    Like this

    • 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

      Like this

      • Claire van der Merwe Reply October 24, 2011 at 10:56 am

        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.

        Like this

        • 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

          Like this

          • 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!!

            Like this

  48. 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!

    Like this

  49. Typo. IN00107 – Item Price List Options. Should be IV not IN. :)

    Like this

  50. 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

    Like this

    • 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

      Like this

      • 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

        Like this

  51. 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!

    Like this

  52. 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

    Like this

    • 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

      Like this

  53. Hi Victoria, thanks a lot for all informations. Is there any table for Deleted items in GP either for each module or together?

    Like this

  54. 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

    Like this

    • 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

      Like this

  55. Stephan Desmoulin Reply June 29, 2011 at 8:43 am

    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

    Like this

  56. 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

    Like this

    • 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

      Like this

  57. 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

    Like this

    • 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

      Like this

      • 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… :)

        Like this

        • 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

          Like this

  58. 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

    Like this

    • 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

      Like this

  59. 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

    Like this

    • 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

      Like this

      • 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

        Like this

  60. 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.

    Like this

    • 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

      Like this

  61. 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!

    Like this

    • 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

      Like this

  62. 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

    Like this

    • 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

      Like this

  63. 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

    Like this

    • Off the top of my head, the only legitimate reasons I can think of are:

      • The items were marked as non-inventory items
      • The items were not tracking history (at the time the SOP transactions were posted)
      • There was a connectivity error when posting the SOP transactions

      I would recommend talking to GP Support to get a more definitive answer on this.

      -Victoria

      Like this

  64. 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?

    Like this

  65. 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

    Like this

    • 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:

      • GP SDK – this can be installed from the GP installation media.
      • Support Debugging Tool – I have a few posts on it that might be helpful.

      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

      Like this

      • 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

        Like this

  66. 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..

    Like this

    • 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

      Like this

  67. 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.

    Like this

    • 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

      Like this

      • “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.

        Like this

  68. 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.

    Like this

  69. Hi Victoria,
    Thank you so much.

    Like this

  70. 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.

    Like this

    • 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

      Like this

  71. 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.

    Like this

    • Zafar,

      In all my years of working with GP and all the customer installations I have seen, I have never seen GP do this and I would be very surprised if GP itself is causing this. I would suspect some other process – a customization, another product, an import gone astray, a SQL job – to be causing this. Unfortunately, it’s impossible to tell you with any certainty what is causing this, since of course, it should not be happening. I would start by examining everything you have that touches GP data to see if you can determine what is causing this. If it’s not obvious, you may need to put some triggers on tables or some other monitoring tools in place to track this down.

      -Victoria

      Like this

  72. Hi Victoria,
    I am trying to build a report where I need the Receipt No. And Vendor Doc. No. along with some other info for Inventory Transaction Document Types = 4 (Purchase Receipt). I am able to get all the info except Vendor Doc. No. from IV30300. I will be thankful if you can advise me from which table to get the Vendor Doc. No.
    corresponding to the Receipt No.

    Like this

  73. Hi Victoria Yudin

    hope you be fine .I appreciate your work providing help to people.
    I have a problem with batch which is posted with error, Now in smartlist the batch status is both post and unposted,also the quantity is not available, batsh posting status is PSTGSTUS =3

    please provide me some solution.
    as there any sql query to solve this problem.
    Advance Thanks
    Jamil Shah Afridi

    Like this

    • Hi Jamil,

      Sorry, this is not something I would feel comfortable simply giving someone a SQL script for without seeing the data. I would recommend asking your GP Partner or GP Support for help with this to make sure that you do not compromise the integrity of your data.

      -Victoria

      Like this

  74. Hi Victoria,

    I am setting up beginning balances for GP 2010 now, and i am kind a stuck when uploading inventory balances. For your information, each of our item in inventory has lot number and expiration date. I am wondering of how can i upload expiry date since the Inventory Transaction template on Integration Manager seems doesn’t have any field which enable us to do so? I have tried by putting the expiry date information under Date 1 and Date 2 in the lot number attributes, but that doesn’t give me the correct result.

    Can you enlighten me on this? Thank you in advance,

    Regards,

    Lukman

    Like this

    • Hi Lukman,

      I don’t work with lots too often, so I have not tried using IM to import expiration dates for them. Looking at it briefly, I don’t really see a way to do this in IM, so your only option may be to either enter them manually or directly in SQL Server. I believe that is in the IV00300 table.

      -Victoria

      Like this

  75. Thank you so much FAC and Victoria. My apologies for assuming that FAC is some sort of Code name for Victoria.

    Like this

  76. Hi Victoria,
    Thank you so much for such a quick and effective reply. Yes, it helped me a lot and now I have completed the report with all the required fields.

    Like this

  77. Sorry I forgot to mention that we are using GP 10 and when I tried to download the Support Debugging Tool I got following error message:

    Our Apologies
    You are not authorized to view this page

    You do not have permission to view this directory or page using the credentials you supplied. If you have reached this page in error, please contact voice@microsoft.com for assistance with your account.

    Like this

  78. Hi Victoria,
    I am trying to develop a report for getting certain inventory adjsutment records. I am able to get all the required fields, excepts the fields shown on Inventory Transaction Distribution Inquiry window, from Tables IV30200 and IV30300. Kindly advice which table contains the fields, such as Account and Description for Inventory and Inventory Offset, shown on Inventory Transaction Distribution Inquiry window

    Like this

    • Hi Zafar, those fields are Inventory IVIVINDX and Inventory Offset IVIVOFIX in IV30300
      those fields store the account index you can match those with table GL00100 with field ACTINDX and get ACTDESCR wich is the description of the account
      i hope that helps

      Like this

  79. Hi Victoria,

    Can you help me to creat a view finding item (quantity+cost) in a specific date?

    Thank you in advance
    ———————————-
    Yassir Humaid

    Like this

  80. Dear Victoria….
    Thanks for your last help…

    Now we’re using GP portal to input demand of stuff or goods (Purchase request), and every demand processed to be Purchase Order, it using default currency ($US) for each item code.

    If we need another currency such as (Rupiah or yen) it need to go to GP – card -inventory -item currency “and set each item manually”

    -Next time GP will recognize this currency for item code.-

    The Problems is, “there’s a lot of item code to be manual maintain like this.”

    If there’s another way to do this….it will be wonderful ….please

    Abdulloh Ismet

    Note : we use GP v 8.0

    Like this

  81. We use the Copy Item function quite a bit but there’s a problem with it – the new-from-copy item retains the Date Created value from the original item. Is there some way to force it to use the actual date it was created?

    Thank you in advance.

    Like this

  82. Hi Victoria ,

    I usually refer your site for reports and queries. It helped me a lot every time it go through it.
    I have a query. I was just going through the SDK documents. I found that
    IVIVINDX column is GL account index for inventory. But here on this page it is mentioned
    IVIVINDX – Inventory On Hand. Can you please put some light on this.

    Thanking you,

    Like this

    • Waseem,

      Inventory and Inventory On Hand is really the same thing – calling it Inventory On Hand can be helpful to show the distinction between this account and In Use, In Service and Damaged, all of which are also used for Inventory, but of different types/statuses.

      Hope that helps.
      -Victoria

      Like this

  83. Hi Victoria
    please would you help me with this problem?
    I got a Sales Site
    where i got this transactions for X item
    Transfer1 100units
    Sales1 -100units
    Transfer2 500units
    Sales2 -500units
    Return1 500units
    Sales2.1 -500units
    Sales2.2 -500units
    Return2 500units
    if you count the stock it should be zero, but GP keeps telling me that there is 500units in stock
    there are no other transactions in that site for that X item
    i even reconcile inventory but the stock is sitll in 500units
    i even update IV00102 to zero for that site and the other site where LOCNCODE=”
    the run reconcile inventory again and the stock is back to 500units

    i thought QTYONHND of IV00102 was calculated by the sum of fields in IV30300
    is there another table where stocks in IV00102 are calculated from?

    thanks a lot

    Like this

    • Hi FAC,

      I am not sure what the formula for QTYONHAND is, sorry. But it does sound like something may have gone wrong with one of the transactions or GP didn’t think it was taking the items out of inventory for one of the sales. Everything is definitely posted? Have you tried running Check Links on the Sales series?

      What does the Item Stock Inquiry window (Inquiry | Inventory | Item Stock) show for the very first line? If the first transaction was a transfer in for 100 units, does it show a balance of 100 or 600?

      Are there any 3rd party products or customizations in place? I saw something similar caused one time by a 3rd party product that was not accounting for POP returns.

      -Victoria

      Like this

      • Hi Victoria
        Thanks for your quick reply

        Yes everything is posted, ran checklinks and reconcile in all series :P

        The other day realized that Inquiry | Inventory | Item Stock goes backwards i mean it grabs the stock quantity in IV00102 and starts calculating (just for the report) backwards (according to date trx starts adding or substracting the last trx by date and that is the balance for that trx then adds or substract the previos by date trx and go on) (i hope i made myself clear in that)

        so if my current balance in IV00102 is 500 then the first trx balance is 600 (after the first trx of 100unit)
        but when i made the update to iv00102 to zero the last trx balance was o and the first one 100

        about the 3rd party there is any, my GP is in spanish, Andina Localization, in this localization there is another kardex but this Localization creates another tables (nsaKD_xxxxxx) and another menus
        and there is one table nsaKD_IV30103 that stores balances per Site and all Sites
        but these balances are Ok

        So i guess there is another table that Reconcile is checking to get another balance

        i hope i didnt confuse you because i already am ;)

        Thanks!

        Like this

        • FAC,

          You are right, Item Stock counts backwards from the current balance. I find that sometimes this window is really helpful to see if there is an issue. If the very 1st transaction is for 100 and that makes the balance 600, obviously something went wrong. At this point, I think you have 2 choices. (1) If it were my data and this happened all the time, I would probably want to investigate further into why this is happening and find a way to prevent it. (2) If it was an isolated incident, I would just enter an inventory adjustment for -500 units and make sure the GL is correct and reconciles to my inventory subledger.

          -Victoria

          Like this

          • Hi Victoria
            this is the only case where this error happened, i made an inventory adjustment and fixed the balance, and cut the post to GL

            Thanks a lot

            Like this

  84. Hi,
    I am using Smartlist Builder to create a calculated field for a smartlist. One of the fields I want to use in the calculation is “Order Up To Level” from the Item Quantity Master table. It allows me to create the calculation using this field. I save it. But it replaces the “Order Up To Level” field with the “Order Point Qty” field. Nothing I do seems to prevent this. Can you tell me why this might be happening. Thanks!

    Like this

    • Hi Susan,

      While I have not run into this particular issue, I have run into many limitations when trying to add logic directly in SmartList Builder. I always recommend creating a SQL view and then pointing your SmartList to it so that you can code any logic you need directly in SQL and do not have to rely on SmartList Builder for this.

      -Victoria

      Like this

  85. Last Received Bin… So if that is what I am needing to find, am I correct in thinking that is table IV30302? We have had some problem happen last week that when the guy ran remove bins that it removed bins for items that had quantities. don’t know if there was a process problem before or what. But after inventory reconcile was run, I now have 117 new items assigned to auto create and need to quickly identify the last place the system recorded where the inventory was received and placed.

    Like this

  86. Hi Vic,

    I’m trying to find the QTY SOLD for each item.. life to date. not only for current year.
    I need exactly to know the items not sold from its created date.

    which table/s can be used for this view?

    thanks in advnce,
    ——————————-
    Yassir Humaid

    Like this

    • Yassir,

      You should be able to get total quantity sold for all time using the Item Quantity Master table, IV00102.

      -Victoria

      Like this

      • Thank you fo fast response..

        I found that the column “QTY SOLD” in this table returns only the current year informations, but not previously closed years!
        To make sure ..
        In my view i inquired about the items with criteria “QTYSOLD=0″ from IV00102
        But through “Item Trx Inquiry” inside GP, I found there are some sales Trx on some of these items in the last year!

        Can you help plz?

        Like this

        • Yassir,

          Try the code below, make sure to replace Your Item Number :

          select sum(SMRYQTYS) [Total Sold] 
          from IV30102 
          where ITEMNMBR = 'Your Item Number' -- change to your item 
          and SMRYTYPE = 1 and LOCNCODE = ''

          -Victoria

          Like this

          • It gives me: Total Sold=12
            that’s really the quantity shown in the sls invoice in”Item Trx Inq”
            While from smartlist>>Inventory>>Item Qty: where “Record Type=Overall” It gives me QTYSold=0 for the same Item!!

            I don’t know if you understood me or not..!

            but i think.. I have to make view to exclude those items in table (IV30102) from “all Items” to get with zero sold quantity Items overall the item life.

            is it right?

            Like this

            • Yassir,

              It does sound like I am not understanding what you’re looking to do. If you would like to rephrase your question we can try again.

              The total of all items sold will be returned by the code I gave you IF item history was being kept for the item at the time of the sale. There are also a LOT of other variables that come into play. Comparing information on different windows in GP will not always give you the same numbers, as they may be looking at different data.

              It may be that your questions will best be answered by someone looking at your data and with a better understanding of what exactly you are trying to accomplish. Have you tried working with your GP Partner on this?

              -Victoria

              Like this

              • OK,
                Suppose that I Have 1000 Items entered in system 3 years ago.
                at least 20 items .. not sold during the 3 years..
                How can I find or filter only these 20 items?
                FYI: Qty Sold field in table IV00102 not for whole item life but only for the current year so that when I search for items with “Qty Sold=0″ it does not search in item history trx.
                Is it clear now? :)

                Thanks Victoria

                Like this

                • Thank you at all..
                  I tested a code to get all Items from IV00102 in which NOT EXISTS in IV30102 then exported them on Excel so can be easly managed through pivot table.
                  It works fine!

                  Like this

          • I appreciate your concern and following up

            Like this

  87. Victoria, I having problem with IV10200 and IV10201, I fould that both IV table (IV10200,IV10201) is not properly updated, some of the information in both tables are missing such as IV10200.ValuationMethod, IV10201.TRXREFERENCE, how I can update both fields.

    Thanks

    Rashid

    Like this

    • Rashid,

      If you suspect that something is going wrong with your data, I would not recommend updating it yourself. To make such a recommendation without knowing a lot more detail and looking at your data would be irresponsible, at best. I would recommend talking to GP Support or your GP Partner about this, so they can take a look at your data and work with you on finding the best resolution.

      -Victoria

      Like this

  88. Needing some advice. We are going to be bringing a product line back from another sister site. Since we transferred the product line to them, a corporate conversion has occured on their item numbers and replaced the dashes in the item numbers with periods. I want as much as possible to limit the number of new items in the system. I am wanting to consider the impact of changing the item number itself replacing the dashes in the number with periods. How much will this break? Is there an easy way to do this?

    Like this

    • Allen,

      I would recommend using the PSTL Item Modifier tool for this. It will ‘rename’ any items you want throughout your system and is the easiest/safest way to accomplish this and should not break anything. If you have 3rd party add-ons or customizations those may need to be addressed separately, but otherwise, I feel comfortable that this is a safe process.

      -Victoria

      Like this

      • The price on that kills me. I’ll have to see what I can potentially do with Integration Manager. I am using that to upload all the new items into the database, those that do not have a previously written item number with dashes. I am having a problem though because though I am giving it price lists to update, it is not putting the price list information into the system but yet says it is successful. I’m on GP10 latest SP and running from the server on 2003Std. It simply is not updated IV00108 as would be expected. Any thoughts?

        Like this

        • Allen,

          Troubleshooting IM in a forum like this, without actually seeing it, is pretty tough. Often, when there are multiple sources the issue is in the Query Relationships or in the sorting of the data. Can you test it with just one item to see if works?

          For the $750 price tag on the tool, in my experience people are often going to spend more than that in other resources and effort to accomplish what they need and may still not have as good as solution.

          -Victoria

          Like this

          • Hi Allen,

            I just thought of you as I was reviewing the Integration Manager chapter for my upcoming book…in my testing (for GP 2010, but I would expect the same for GP 10.0), the price import will fail without at error if you are entering both a Percent and Price. One of them should be used, the other should say “Use Default” under the Rule. Not sure if this is what is causing your issue, but worth checking.

            -Victoria

            Like this

  89. Hi Victoria

    I need to bring in some item history data. I know these 2 tables hold that kind of data and I have the same data from my old system already in a table.
    Inventory Sales Summary History IV_SUM_HIST IV30101
    Inventory Sales Summary Period History IV_SUM_HIST_Period IV30102

    I only really need the period data for the report being requested but i figured i would include the other table as well.

    My questions are: do i need to update the data anywhere else?
    And by adding the data to either of thos tables will I effect any financial data?

    This is for sales reporting not financial.

    thanks

    Vic

    Like this

    • Vic,

      No General Ledger data will be affected by entering amounts into these tables. I have not entered historical data this way, but I would test this with a small set of data and make sure that running an inventory reconciliation doesn’t clear it out.

      Another option – if this data is needed for reports being created in SmartList Builder or some other (external) tool, I would consider creating your own table(s) to hold it. That way you are not relying on GP not ‘messing’ with that data.

      -Victoria

      Like this

  90. Thanks for the site, its been a great help thus far.
    One question I have is about deleting units of measure. For example, under Unit of Measure ID ‘EACH’ we have a handful of selling uofm’s – each, 100, 1000, etc. Our tables have grown out of control due to mismanagement to the point where we have 40MM records in IV00107/8 and it is causing a nightmare for other products, most notably the CRM/GP Adapter which won’t work because of the size of those tables. So, in a case with MS we are trying to figure out how to reduce the size of those tables but they aren’t being very helpful thus far. If I use the built in Dynamics method for deleting rows under a UOFM, it will take atleast a week based on results in a test enviro to delete the superfluous rows under uofm EACH. So, I was thinking about maybe just running a delete statement in IV00107/8 and IV40202. Might that work?

    Like this

    • Hi Jeff,

      In general, I would not recommend deleting from the tables without a lot of testing first. This type of clean up is not something that I have done in the past, so I don’t have any first hand experience to share. Since this sounds like a pretty large issue, I would recommend either escalating the case with GP Support, or perhaps talking to the Professional Services team instead to see if they can come up with the best, ‘safe’ option for handling this.

      -Victoria

      Like this

  91. I have found a problem where IV00107 RNDGAMNT is overriding the list price set in IV00105. How is this being generated? What is this “Rounding Amount” field used for?

    Like this

    • Allen,

      The Rounding Amount is used when you want to make sure that your prices always round to a particular number or a multiple of a particular number. For example, if your price method is a % of list price, but you want to make sure the price rounds up to end in $0.95. Is this is not what you want, you would need to change the rounding setup for your price lists.

      -Victoria

      Like this

      • Thanks Victoria, but I am confused. We have the IV00105 LISTPRC set at 2763.60 and it is showing 100% as 9500.00 and 30% at 6650.00. When I look at the “Options” from the Item Price List Maintenance window, I have 100% UOM = “EACH” with Selling option Whole, Round up, multiple of 9500….. same change price for 30%. Is this just set up incorrectly? Should this be set differently or wiped out for all of our items so the list price is properly used at the relative percentage rates.

        I imagine that the purpose would be to maybe designate the pricing to be set to a division of 1.00 if I want only whole dollars and no change or maybe .25 if I want quarter increments right? But if I don’t care what the change amount is on 30% off then I would just remove these options all together right?

        Like this

        • Allen,

          Am I understanding correctly that you have the list price set to 2763.60 and the rounding option to be multiples of $9500?? I guess this will work, but it’s really not how the setup was meant to be used and is a bit counter-intuitive for anyone that needs to maintain item price lists. Yes, what you are saying at the end is correct, the idea of the options is to round to something – many companies choose to have all their prices end is the same amount, like $0.95 or $0.99…or to always round to whole dollars. If you do not need this functionality, there is no reason to use the options, without which I would think the pricing setup would be much simpler for any new items or changes needed.

          -Victoria

          Like this

  92. Victoria,

    I am working in eConnect to create inventory items and prices in DynGP10. What is the relationship between the PriceListHeader and PriceListLine for a multi-currency environment? I see that the IV00107/IV00108 tables are updated when I add a pricelist thru eConnect but they seem to be updated in a one-record-each-table instead of a parent-child relationship. Are there other IV tables for PriceLists?

    –Chad L

    Like this

    • Hi Chad,

      I don’t know if I would call this a parent/child relationship.

      IV00108 is the Price List – this will hold what you see on the Item Price List Maintenance window in GP.

      IV00107 is the Price List Options table – this will hold what you see on the Item Price List Options Maintenance window.

      Unless you have quantity breaks for your pricing, you will have the same number of records in each of these tables. If you have pricing based on quantity, you will have more records in IV00108.

      -Victoria

      Like this

  93. This is a wonderful blog. Thank you for all your efforts.

    I need to get a list of item with extended pricing given customer Number. Do you think it’s possible? I’m trying to get that information using eConnect and I’m very new to GP and eConnect…

    Detailed explanation:

    I’m using Dynamics GP 2010. I have setup extended pricing for my company. Now given customer number, how can I get a list of item with their corresponding pricing? For example, I have two products A and B. I have two customers cus1 and cus2. For cus1, A is $10, B is $5. For cus2, A is $20, B is $10. I need to get a XML document back via eConnect’s requester – pass in cus1 and I get A|$10 and B|$5 (I know it should be XML, just trying to explain concept here) and pass in cus2, I should get A|$20 and B|$10.

    Seems like I can try to create a new transaction requester document type by add a new record to eConnect_Out_Setup table. But I’m having a hard time to come out a query to do what I want. I’m a brand new GP developer. If you can give me a query that can do what I want (or the tables I need to work with), I might be able to create the new requester document type by myself.

    Like this

    • Hi Jeff,

      Thanks for your kind words. I don’t work with Extended Pricing, so I am not the best person to help with this. I see that you have also tried to get help on the GP Customer Forum and have not gotten any replies. I would recommend talking to GP Support to see if they can help you with this.

      -Victoria

      Like this

  94. Victoria

    I have what should be an easy question, but I can’t find an answer. For stock counting is the next count date a data field that I can get to using Crystal or is a calculated field that is not stored. I found the current count data but I can’t find a field for the next count date.

    Patrick

    Like this

  95. Hi Victoria,
    Is it fine to directly update UMSLSOPT field in IV00107 table using sql query as I could’t find this field in integration manager when tried to create an integartion. If not, is there a way I can update UMSLSOPT field for a list of items for certain price level?

    Like this

  96. Victoria I have a question regarding the item master table and check links. The database I work on has had little to no maintenance for years. Yesterday on our test data base I ran Check links on the sales section to try and remove what appeared to be orphan INV records. When it completed I found it had removed a large group of our part numbers. I have check the part numbers against our live database and I can’t find any connection some were old, some were new, some had a little history, some had a lot. Any ideas why check link removed these from the IV00101 table (I checked using sql)

    Like this

    • issue resolved. The test master was old and we had added some customer numbers through SQL that that didn’t have a match.

      Patrick

      Like this

      • Patrick,

        Great! Thanks for following up and letting me know. I was just getting ready to write and ask if you can recopy your live database to test and re-run the process to see if the same thing happens. Glad to hear you were able to track it down quickly.

        -Victoria

        Like this

  97. Victoria,

    I have several hundred inventory items showing up with negative or incorrect allocation amounts. What would the best approach on correcting this issue and determine what caused it in the first place?

    Regards,
    Raul

    Like this

    • Raul,

      First thing I would try to fix this is to run Inventory Reconcile.

      I doubt if anyone can easily tell you what caused the issue without extensive investigation into your GP environment and usage.

      -Victoria

      Like this

      • Victoria,

        Thanks for your prompt response. I tried to reconcile a handful of the items and I got you can’t complete this process while transactions are being edited, so my guess there is something hung out in the system. I’m looking to see if I can identify if there are any stock count associated to the items that may be running or hung.

        Raul

        Like this

  98. Larry Carrethers Reply July 28, 2010 at 1:35 pm

    Is there a way to do this that you can think of:
    I wanted to see if the system was able to implement a notification for negative inventory? When we write an order for a customer and the inventory goes negative it will give us a pop up box that shows what we have on hand and lets us know that we are going to go into the negative. I was wondering if there could be a box that would pop up that would list any orders in the system that are containing the items that are going negative. That way we could see the orders right there in front of us. That would help to show if there are tickets that are wrote that may be going out for delivery in a couple of weeks but the customer would like them wrote now to secure the material while we have it on hand. That way we do not double sell material and cause problems and delays for our customers. We would be able to see all tickets that are containing the product and help to refresh our memories on special orders that might be placed. it would also help to keep an eye on inventory. If the negative inventory box pops up and there are no orders that contain the item and we have the material on hand then we know that there needs to be investigation on that item to help find where we are off. I would like to discuss it further once you have time to go over it so that way we can really understand what I am trying to describe. Thank you for your time and let me know what you think. Thank you!!

    Like this

  99. Hi Victoria,

    Thanks for taking the time to compile this information. I am trying to build three reports in CrystalReport:
    1. Bank loan (bank draw down) monthly
    2. Inventory COGS (cost of goods sold) (monthly)
    3. Inventory Turns by Location
    I am having a hard time finding the views/tables in the GP database which would have this information. Can you point me in the right direction?

    Like this

    • Hi Janell,

      There is no easy answer this without knowing the full specifications of what your reports need to accomplish and how the relevant data is being entered into GP.

      1. Bank loan – since there is no standard ‘bank loan’ concept in GP, this would very much depend on what this means to your company and how/where this data is entered. Is it a GL account? A vendor? Something else?

      2. Inventory COGS – this is something typically found in the GL. So, identify the GL accounts that record COGS and use the details in GL20000 or GL30000 for those accounts.

      3. Inventory Turns – there is a report called Turnover Report in GP. It’s under Reports > Inventory > Analysis. If that’s not what you need, you want want to define what your report needs to do and where the data is populated. This may not be so easy to do by location, as it’s often a report based on GL numbers, which may not be separated out by location.

      -Victoria

      Like this

  100. Hi all GP9 SQL2005 w/ manufacturing
    We are going through Stock Count activities currently. When trying to process a particular stock count ID, we get the exception message: “You can’t complete this procedure while Inventory decimal places are being changed” and subsequently cannot process the schedule. There is only one item listed in the exception list.
    Since I am the only one that runs this decimal places change routine, I know I didn’t do any recently and certainly not before the stock counts were started. Any ideas where this may be stuck?
    Help always appreciated.
    Stu

    Like this

    • Stu,

      With all users out of GP, check the SY00800 and SY00801 tables in the DYNAMICS database and the DEX_LOCK and DEX_SESSION tables in the tempdb database. Those should all be empty if there are no users in GP. If they are not, some of the records there could be causing the issue.

      -Victoria

      Like this

  101. Hi Victoria,

    We are running FIFO Periodic as our costing method, I need to report on Material vs Labor cost elements. What table(s) can I pull to get labor/material cost on a inventory item.

    Thanks.

    Jeff

    Like this

  102. Hi Victoria,

    Can you please help me finding which tables are related to the “Item stock Inquiry” Window?

    Like this

  103. Hi Victoria,

    I need to report on Serial Numbers on Hand. I can find the serial number transaction history in IV30400, however, I can not locate where serial numbers are tied to units on hand.

    Thanks for your help!

    JTH

    Like this

  104. We have some inventory transactions that we entered, but the person who entered them forgot to check post to general ledger. Now these are posted but not to the GL. I was wondering how I get these to post to GL? Thank you

    Like this

  105. Hi Victoria,

    Thank you for your site. Very informative.

    I run GP 10 with a 3rd party inventory matrix product. We did some inventory adjustments through Inv>Transactions>Transaction entry. The batch was posted, the transactions appear in Inquiry, but the adjustments did not affect the item qty’s.

    Any suggestions?

    Bruce

    Like this

    • Hi Bruce,

      Thanks for the kind words.

      First thing I would do is double check that there is no different way to enter adjustments in conjunction with the 3rd party product you have. Taking the 3rd party product out of the equation, it sounds like the posting of the batch possibly did not complete correctly and I would recommend running Inventory Reconcile to see if that will correct the quantities. Again, you will want to make sure that there is no issue with running Inventory Reconcile and your 3rd party product. (Or they may have their own reconcile process.)

      -Victoria

      Like this

  106. Hi Victoria

    I saw your site very recently. I really appreciate the work you do to help others.

    i was trying to create a report where i need to find out the available quantity of an item for a selected range of receipt dates. Any views or reports which you might have done already!?

    Like this

  107. Hi Victoria,

    Congratulations on winning the MVP award!

    I have an Inventory Transfer Batch that appears to have posted, yet is still stuck in the Work Tables. The exact same records appear in IV10000/IV30200 and IV10001/IV30300. The transfer hit the GL because there are records in GL20000 for the corresponding Inventory Transaction Source. When we look at Item Inquiry for one of the items in the Transfer, it shows the quantity from the transfer as being allocated. We click on the Allocated link and the Item Allocation Inquiry Window opens and listed is the Item Transfer Document. We click on the link to open the document and receive “This batch has been marked for posting by another user”. I ran Check Links for all Inventory and Items. There were no errors. So, it appears that the Item Transfer was successful, but somehow didn’t get cleared out of the Open Tables.

    Any suggestions?

    Thanks.

    Brent

    Like this

    • Hi Brent,

      I have seen this before, though not often with Inventory transactions. My guess is that a connectivity failure or some similar issue at the very end of the posting process prevented the unposted transactions from being deleted from the work tables. If you are confident that the transactions have posted to Inventory and GL, I would delete the unposted ones. You know the drill – make backups first, etc. before making any changes like this. :-)

      -Victoria

      Like this

      • Thanks for the quick response…Are there any other OPEN (10000s) tables that I should be looking at in addition to IV10000 and IV10001?

        Like this

        • I forgot to ask…do I run Check Links (which ones) and/or Item Reconcile after I delete the records?

          Like this

          • Brent,

            I would delete them from IV10000 and IV10001 and then run Check Links on the Inventory series. I always prefer to run the whole series if possible, but if you’re pressed for time, you can try running just the Inventory Transaction Work logical tables.

            -Victoria

            Like this

            • Hi Victoria,

              Thanks for your responses. I followed what you wrote and backed up the GP company db, deleted the records from IV10000 and IV10001 and ran Check Links on the entire Inventory Series. Unfortunately, the allocations were still off. So, I ran Inventory Reconcile for the 49 affected items and the allocations were corrected.

              Thanks for all of your help.

              Brent

              Like this

  108. Hello Victoria,
    I have several reports that tie into IV00101. I have a field IV0010.ITEMSHWT that pulls in the item weight that range from .01 to 999.

    Without formulation the items weight that is less than 0 comes in as 10 and for items that weigh more it reads with two additional 0′s for example 2500.

    I have dynamically changed the decimal in the format field custom symbol number to the following: decimals: 1, rounding: 1, negatives: -123, decimal separator: ., thousand separator symbol: , , and leading zero and the results are the 2500 now displays as 25 but the less than 0 is just 0.

    My issue is I need the zero and less to show .01 and not 0.
    Is there a solution that you can share with me?

    Thank you in advance,
    Cindy

    Like this

    • I wanted to add that in Crystal I have a SQL Expression Fields that states: IV00101.Itemshwt / 100 that gives us the 0 instead of 10.

      Thanks.

      Like this

    • Hi Cindy,

      I am guessing from your second post that you are using Crystal Reports for this. If so, it may be that you’re overcomplicating this a little. Why not create a formula that looks like this:
      {IV00101.ITEMSHWT}/100
      and use that for the shipping weight? I just tired it and it works perfectly for all the scenarios I have tested. Or am I missing something in your requirements?

      -Victoria

      Like this

      • Thank you very much for your response.

        Yes I am using Crystal Reports version 11. In a sequel I have : {IV00101.ITEMSHWT}/100 and that reduces the amount of zeros so when Dynamics on the Item Maintenance window has the shipping weight for example :39.00 when it comes over to Crystal Reports as :3,900. The sequel expression take the 3,900 and reduces it to 39.00 and that’s the way we want it. The problem I’m having our products that weight less than 100 pounds do not read correctly. The sequel expression reduces the weight as 0. Is there a way to have it show up as .10?

        Like this

        • Cynthia,

          I have not tested this with a SQL Expression, only with a formula, however, if I have 0.10 entered for the weight in GP, my Crystal report using the formula above brings in 0.10. Can you try taking off any formatting/formulas you might have on that field? I think you mentioned in one of your posts that you have a formula on the decimal places?

          -Victoria

          Like this

  109. Hi Victoria,

    I appreciate the information provided here, it is of great help.

    My question is: Is there any way to create or modify a stock count schedule so that it can be counted by bin location?

    Like this

  110. Hi Victoria,

    We are running a single currency (USD), we had a user change the decimal value to add digits to the right of the decimal place. It was a mistake and now we are trying to roll back, however, the GP ui does not allow you to roll back to 2. Do you know what table houses this data? My table catalog says it should be MC00100, however, that table is not found in our instance of the application. Thanks for your help.

    JTH

    Like this

    • Jeff,

      If you are talking about quantity decimal places, that is stored in IV00101. Any table starting with MC is a Multicurrency table, so if you are only using one currency, it would be unusual for you to need any Multicurrency tables.

      -Victoria

      Like this

  111. Hi Victoria,

    Do you know of any issues with the Shrinkage Factor field in IV00102? The field is in the Item Resourse Planning Maintenance and I can get other fields to pull into Crystal, but this particular field just brings in 0.00. We have noticed that it doesn’t pull in SmartList either. Just curious if you know of any ways to manipulate?

    Thank you,
    Jill

    Like this

    • Hi Jill,

      This field stores percentages with more decimals than you might think. For example, 0.15% is stored as 0.00150. So you will most likely need to create a formula to multiply that field by 100. Also, from a quick glance, the Shrinkage Factor is only stored at the ‘summary’ level, so only on records with a RCRDTYPE = 1.

      In general, when in doubt (and if possible), I try to look at the data directly in the SQL tables. It often helps find the patterns in the data. :-)

      Hope that helps.
      -Victoria

      Like this

  112. Victoria,

    I needed to look up this infomation on the IV40400 table for a report I needed to write and thoguth I would share it with you. I am not sure if you would add this or not to your Inventory table but here it is. I needed to look up all of the Item Classes in the system and their corresponding defalt GL accounts. Here is the table and field information.

    IV40400 – Item Class Setup

    IV40400 Account Indexes:

    IVIVINDX = Inventory On Hand
    IVIVOFIX = Inventory Offset
    IVCOGSIX = Cost of Goods Sold
    IVSLSIDX = Sales
    IVSLDSIX = Markdowns
    IVSLRNIX = Sales Returns
    IVINUSIX = In Use
    IVINSVIX = In Service
    IVDMGIDX = Damaged
    IVVARIDX = Variance
    DPSHPIDX = Drop Ship Items
    PURPVIDX = Purchase Price Variance
    UPPVIDX = Unrealized Purchase Price Var
    IVRETIDX = Inventory Returns
    ASMVRIDX = Assembly Variance

    Ron

    Like this

    • Ron,

      Thank you very much! These are also in the IV00101 table and I can see them being very useful for anyone needing to look this detail up. I have added the list to the page above.

      -Victoria

      Like this

  113. Hi Victoria,
    I found out that the users did not tick to maintain history for the item master. Was that the reason why there are no records in IV30102 even though there had been many sales transaction for the items?

    After noticing this, I had ticked the checkbox for Maintain history. Is there anyway I can get records updated back to IV30102?

    Any advice is greatly appreciated.

    May

    Like this

    • May,

      Yes, not checking the maintain history checkboxes will cause the data not to be tracked in the history summary tables, as well as numerous others. I recently had a similar request from one of our customers and spoke to GP Support about this – the only option they had was to populate the history summary as a consulting engagement.

      -Victoria

      Like this

  114. any one know how to resolve inventroy quantity difference.
    i catch the item but i don’t know how to get other detail.

    thanks

    Like this

  115. Hello

    Can you point me to the proper table(s) to find BIN info?

    Like this

  116. Victoria,

    Each inventory item has a Note Index number which, I’m sure, is a reference to data within another table. Do you know what table stores all of the note indexes? I would like to pull them out in smartlists and reports.

    Thanks!

    Like this

  117. Victoria,

    The update to the status did not make a difference.

    It had about 50,000 items so I deleted from the unposted stock count table IV10301. Then went in through GP and deleted the stock count ID.

    Thanks,
    Raul

    Like this

    • Raul,

      Thanks for the update. For the future, there is a ine in the DEX.ini file that by default says:
      STOCKCOUNTLEVEL=10000
      You would want to change this if your stock counts have more than 10,000 line items. This setting is machine specific.

      -Victoria

      Like this

  118. Victoria,

    There is a stock count schedule that a user is trying delete but unable to do so through but locks their machine up after it is pulled up onto the screen. I checked on the table side and noticed that the stock count status is 0 which is not listed above. Is this the problem and be updated to a valid entry of 1 thru 3 then try deleting it or is there more to it than it appears?

    Thanks in advance for valuable knowledge in resolving it,

    Raul

    Like this

    • Raul,

      I have not run into this issue before. You could certainly try changing the status and see if that makes any difference.

      How many lines are on the stock count? It may be it’s locking up because it has too many lines. I believe there is a setting in the dex.ini file that can be changed to address this.

      -Victoria

      Like this

  119. Hi Victoria,

    The site is very good for people like me. I am getting more knowledge using this site.

    I am new to GP and my understanding is that, Item Stock Inquiry window is used to track all transactions for the items with UOFM. It will keep all types of records (sales, purchase, receipts etc) for the item number. But I am checking few document number but they are not in IV30300 table. For example, I have placed a sales order, processed and posted in GP. I think posting needs to create the transaction entry on IV30300 table but there is no entry for the posted sales order in IV30300. I don’t know why?
    Note : It is not happening for all sales orders but for few.
    Any advice is appreciated.

    Thank you in advance.
    Regards
    Solomon

    Like this

    • Solomon,

      There are many reasons why you might not see a record of a sale in the inventory history tables. Without looking at the particular scenario and GP setup in detail, sometimes it is impossible to determine. That said, the 2 most typical scenarios I have seen are:
      - the item was not an inventory item when added to the sales order
      - the item was not set to track history

      -Victoria

      Like this

  120. Hi Victoria,

    Hoping you can help me….we are trying to get a report to help guide us on reserving for inventory obsolescence and I’m trying to find a “last transaction date” field in the inventory tables. Is there on available that you know of?

    Thanks in advance!
    Jill

    Like this

    • Jill,

      The table you want is IV30300. There is a lot of different information in this table, but if all you’re looking for is the last date anything was done with a particular inventory item, then find the latest DOCDATE for the item. If you need to distinguish between last receipt date vs. last sale date, you would need to add quite a bit more logic.

      -Victoria

      Like this

      • So there is no “Stored” field containing the last activity date that can be queried? I need to make a list of all items that were used in the current month, without getting all transactions.

        Like this

        • Cheryl,

          There is no place in GP that stores ‘last activity’ for an item across all modules and types of activities. If that’s what you need, you will need to look in multiple places and then consolidate the detail into the report you need.

          -Victoria

          Like this

  121. Hi Victoria,

    We are getting closer to the end of the year and thus we’ve begun to look at Stock Counts and doing a Physical in GP. I looked at Integration Manager and did not see a destination that would allow me to load my stock counts. Do you know if Integration Manager or eConnect is capable of loading the stock counts? If not, what are your thoughts on loading the counts via SQL?

    Thanks.

    Brent

    Like this

    • Hi Brent,

      I am not a big fan of importing transactional data directly into SQL. Here is a thought – why not just use the Inventory transaction destination in Integration Manager for what you need?

      Example: export your stock counts into Excel, enter your actual stock into Excel (was that you that said on a newsgroup somewhere that you scan it into Excel?), calculate the variance in Excel, then use Integration Manager to import an inventory adjustment for the variances.

      -Victoria

      Like this

  122. Hi Victoria-
    I’m currently in the middle of a conversion to GP from our legacy system and your website is my “go-to” resource! Thanks!

    I just populated our inventory items using Integration Manager, but my ABC code did not come over into the Item Maintenance Options window. The source file has each item coded with and A, B or C…because this is a picklist in the Item Maintenance Options window, should my ABC values be different? Any suggestions are appreciated. Thanks Victoria!

    Like this

    • Hi Katrina,

      Thank you very much for frequenting my site!

      I just tested this with GP 9.0 SP 3 using A, B or C in my source file and it came through with no problem. I have not imported ABC codes too often, but I have not run into any issues with this on the past. You might want to ask this on the GP Newsgroup to see if anyone else has seen this issue – you’ll have a wider audience with more diverse experience there. Be sure to mention what GP and IM version and service pack you are on.

      -Victoria

      Like this

      • Thanks Victoria – I just went into resource descriptions for fields and found the the static values for the ABC (2,3,4 respectively). I changed those in my source file and they came over no problem. Lesson learned by me: Always check the field info :) I’m a newbie but learning more and more every day!

        Like this

        • Katrina,

          Glad you were able to resolve this. Interesting that the A, B, and C worked just fine for me. What version/build of IM are you on? I have seen some things not work correctly on early builds of IM 10.0…

          -Victoria

          Like this

  123. Hi Victoria,

    I reference your website daily…it is a great tool.

    We have some Item quantity levels that appear to be out of sync. If we go by the same Item and Site ID (assuming Inventory Reconcile has been run and there are no issues with the Item), should the following three things produce the same number for On-Hand Quantities:
    1.) Item Inquiry
    2.) Item Transaction Inquiry
    3.) Reports | Inventory | Activity | Historical IV Trial Balance

    Thanks so much for your help.

    Brent

    Like this

    • Hi Brent,

      Thank you very much for the kind words and for frequenting my site!

      I believe that if all the transactions were entered in GP and there were no issues or data manipulation directly in SQL, then the on-hand quantity on the Item Inquiry screen should equal the sum of all the ins and outs on the Item Transaction Inquiry screen. The Historical Trial Balance report is a completely different story and I have seen cases in the past where it does not always show the correct data. I would also not use that report to compare to ‘current’ on hand quantities – instead I would use the Stock Status report.

      That said, I have seen some very complicated inventory issues crop up in my travels…I have also seen 3rd party products and customizations sometimes cause issues with quantities. So if you’re having recurring or lingering issues, you may want to get your GP Partner or Dynamics GP support involved to help you get to the bottom of this.

      -Victoria

      Like this

      • Hi Victoria,

        Thanks for the response. I’m hoping the problem is small or else I’ll have to get our Partner or Microsoft involved. Perhaps you can explain a few things about the tables for me.

        1.) Are tables IV10200 and IV10201 open or history?

        2.) Do records stay in table IV10200 forever? If no, then when do they move and where do they go to?

        3.) Is there any connection between tables IV10200 and IV30300…IV10201 and IV30301?

        As usual, thanks so much for all of your help.

        Brent

        Like this

        • Brent,

          Disclaimer: For most of this my answers are deductions based on what I have seen in the data I have worked with, not the ‘official’ answer.

          The IV10200 and IV10201 tables get cleared out when a year is closed in the inventory module, so in that sense they are ‘open’. GP actually lists them as ‘work’ tables. When the year is closed and records are cleared from this table, I do not believe they are moved anywhere, as they are already in the IV30300 and IV30101 tables. Not sure what you mean by ‘connection’ between the tables, but I would describe it as IV10200 and IV10201 are subsets of the IV30300 table. So when creating reports, I would use the IV30300 table, not IV10200 or IV10201.

          -Victoria

          Like this

          • I have a similar question (use FIFO Perpetual).

            I am looking to connect Inventory that has been Transferred back to its original Purchase receipt (it is possible that the inventory has been transferred more than once). I can do this as long as the original receipt records are in the IV10200 and IV10201 tables, but this relationship becomes fuzzy when I only have the IV30300 to look at.

            For example, In the IV30300 I can see the Inflow to SITE1 referencing the PO Receipt, and then the Inflow to SITE2 referencing the Transfer transaction, but I do not see a way to identify the outflows from SITE1, so when I have several transaction lines, it is not possible to see which Receipts go with which Transfers.

            My reason for wanting to do this is so that I can maintain visibility of Landed Cost details for Inventory after it has been moved from its original receipt location.

            Thanks for any advice you might have.

            Kurt

            Like this

  124. Does anyone know of a query that could give me the current cost, the sell price of our items?

    Like this

  125. Hi Victoria,

    I have a confusion over following tables.
    - Item Quantity Master
    – Item Price List
    Can you please give some details over these?

    My concern is, how you are keeping each individual item of assume 5 quantity cost of 100 $ and than the same another 2 items cost of 50$?

    Should the Item Quantity Master table be like this?
    Item Id| Receive Voucher Id| Unit Cost| Status|
    1 | 1
    1 | 1
    1 | 1
    1 | 1
    1 | 1
    1 | 2
    1 | 2

    Like this

  126. Hi Victoria

    I am trying to setup the Item lookup window to default to an advanced lookup list. instead of viewing all inventory items with no search criteria the list should default using my restricted list created via Smartlist as a Favorite.
    Do you have any idea how I can go about doing this?

    I have tried Field Security but I am not getting anywhere.

    regards,

    NG

    Like this

    • Hi NG,

      This would have to be a customization. You could either ask your GP partner for this, or if you are looking to do it yourself, you could try posting a question on the GP Newsgroup or checking out some of the more technical blogs on my blogroll.

      -Victoria

      Like this

  127. Hello Victoria

    Thanks for providing this ad-hoc repository of invaluable information. It has eased my introduction into Great Plains no end.

    I have been asked to make the “Inventory Turns Report” (GP9 – Reports|Inventory|Analysis|Turnover Report) available in electronic format. I can see no other way than manipulating the data after an extract to excel.

    I have tried to recreate the detail via SQL but cannot seem to generate the ‘Summary of each months ending on hand inventory’ (as outlined in various docs – for example Article KB856731 on Cust Source).

    Have you had to recreate this report in the past for Crystal Reports/SSRS or do you know of a solution to do so? Any help is greatly appreciated.

    Regards

    Stu

    Like this

    • Hi Stu,

      I have not created a report like this. The only other thing i can suggest is that you trace what stored procedure is being called what that report runs in GP and then see if you can use it in your report, possibly by using it in your own stored procedure.

      -Victoria

      Like this

  128. Hello Victoria,

    I am working with a client and we want to store product categories in a comma-separated format for each inventory item. The User Defined fields seem to be too short to hold the data. We want up to 64 characters of space if possible. Is there any way to do this for each inventory item?

    Thank you once again for your help.

    Like this

  129. Victoria,

    I am running GP 10, when i receive the inventory in multi currency the system create split purchase receipts since it cannot arrive at the cost of item at BUOM, since i have many cases like this which keeps happening….

    and this some differences in each trx will accumilate to a good amount, and it any point of time i will have difference between the GL inventory control account and my Inventory cost from Sub Ledger….

    how should i resolve this issue, i am using Average Perpetual valuation method.

    Please guide me in this regard….

    How to resolve the reco between my inventory and GL accounts.

    Thanks for your guidance

    Akram.

    Like this

  130. What fields/tables are affected on the purchase or sale of an inventory item? I am integrating items based on their change in price or quantity and want to filter only those items that have changed since last integration. Along with the fields/tables on purchasing and sales of items, are there any other transactions that affect the quantity of an item?

    Thank you for your help!

    Like this

    • John,

      There are a lot of tables and fields that get updated by purchases and sales. The precise tables and fields will also depend on the details of your transactions and the modules you are using. If you need a complete list I would recommend running SQL Profiler and entering the transactions that you are trying to trap. Additional transactions that may affect quantities are: inventory transactions, bill of materials transactions, manufacturing transactions, and sales/purchasing returns. There may be more depending on what modules and additional products you are using.

      -Victoria

      Like this

  131. Raul,

    When an item gets assigned to a site a new record for the item/site combination gets created in the IV00102 table.

    -Victoria

    Like this

  132. Victoria,

    I’m trying to do some maintenance cleanup in our inventory and was wondering if you could tell me what tables are used when an item gets assigned to a site or location?

    Thanks!

    Like this

  133. Hi, Thanks for posting this information. I’m tring to use it, but still have trouble actually finding which tables hold the information I’m looking for. Do you have any additional resources for that?

    Thanks!

    Like this

  134. Hello,

    I appreciate the work you have put in to help get others acclimated with GP. Thank you.

    I want to know if there is a way to check to see if an inventory item has been updated based on a date field. I have noticed that if I update an item through the main Item Maintenance window, this is reflected in the date. However, when I change a price level in the Item Price List Maintenance window, the date modified field doesn’t change. Is there a date field that gets updated when a price level changes in the Item Price List table?

    Thank you!

    Like this

    • John,

      I don’t see any date fields available for you to track changes to the price list table. You could use an auditing add-on or add a SQL trigger to write the information to your own table to track changes to pricing.

      -Victoria

      Like this

      • i should add that the reason i want to do this, is so i can disply the revision level of the parts being ordered on the PO form. The only place i see the item revision level field available is in the MFG tables….i cant seem to get the data to cross over though =(

        Like this

        • Hi Jon,

          So you’re looking to do this in Report Writer? If so, you may not be able to add Manufacturing tables without writing a custom function. However, this is not really my area of expertise, as frankly, I try to avoid Report Writer if I can help it. :-) Have you tried posting your question on the Dynamics GP community forum? You might find someone who can help more specifically with this question.

          -Victoria

          Like this

          • thanks victoria! yes, I am personally trying to do this in report writer, but there are others here who are just trying to create smartlists that pull data from both the GP tables and MFG tables. I will dig around for answers on the report writer side of it…any suggestions for those relationships in GP itself/smartlist builder? thanks!

            Like this

            • Jon,

              For SmartList Builder, I always recommend creating a SQL view first. There you can join whatever tables you want in whatever way you want and add whatever logic you need. Once you have a SQL view, simply point SmartList Builder to it and you’re good to go.

              -Victoria

              Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,357 other followers

%d bloggers like this: