POP Tables


Commonly Used Tables:
POP10100 – Purchase Order Work (header)
POP10110 – Purchase Order Line Work (line detail)
POP10150 – Purchase Order Comment (header)
POP10300 – Receipt Work (header)
POP10306 – Purchasing Receipt User-Defined
POP10310 – Receipt Work Line (line detail)
POP10330 – Serial / Lot Work
POP10500 – Receipt Line Quantities (line detail)
POP10550 – Line Item Comment (line detail)
POP10600 – Purchasing Shipment Invoice Apply
POP30100 – Purchase Order History (header)
POP30110 – Purchase Order Line History (line detail)
POP30300 – Receipt History (header)
POP30310 – Receipt Line History (line detail)
POP30330 – Serial / Lot History
POP30390 – Distribution History
POA40003 – PO Approval Control

Status in POP10500:
0 – Unposted
1 – Posted
2 – Voided

PO Status:
1 – New
2 – Released
3 – Change Order
4 – Received
5 – Closed
6 – Cancelled

STATGRP (Status Group):
0 – Voided [this is not a valid according to the SDK, but we have seen it for voided PO's]
1 – Active (includes New, Open and Modified)
2 – Closed (includes Cancelled and Closed)

[Note: In our experience, when a PO is voided and it moves to the history tables, in POP30100 the STATGRP will be 1 or 0 whereas for a regularly closed PO the STATGRP will be 2. Also, there is a value starting with POPVT in TRXSORCE of POP30310 for voided PO's.]

PO Type:
1 – Standard
2 – Drop Ship
3 – Blanket
4 – Blanket Drop Ship

POLNESTA (PO Line Status):
1 – New
2 – Released
3 – Change Order
4 – Received
5 – Closed
6 – Cancelled

POPTYPE (POP Receipt Type):
1 – Shipment
2 – Invoice
3 – Shipment/Invoice
4 – Return
5 – Return w/Credit
6 – Inventory Return
7 – Inventory Return w/Credit
8 – Intransit Transfer (thanks to Jesus M. Cruz Narvaez for this one)

[Thanks to Frank Hamelly of GP2theMax for the complete list of POP Receipt Types, as these are not in the SDK.]

DISTTYPE (Distribution Type):
1 – Purchases
2 – Trade Discounts
3 – Freight
4 – Miscellaneous
5 – Tax
6 – Discounts Available
7 – Accounts Payable
8 – Other
9 – Accrued
10 – Round

NONINVEN (Non-Inventory):
0 – Inventory item
1 – Non-inventory item

DECPLCUR (Decimal Places for Currency):
7 – 0
8 – 1
9 – 2
10 – 3
11 – 4
12 – 5

DECPLQTY (Decimal Places for Quantity):
1 – 0
2 – 1
3 – 2
4 – 3
5 – 4
6 – 5

DOCTYPE in POP10550:
1 – PO
2 – Receipt

ITMTRKOP (Item Tracking):
1 – None
2 – Serial Numbers
3 – Lot Numbers

PO Approval Status in POA40003:
1 – Unapproved
2 – Approved

PO Note ID Array in POP10100:
1 – PO Note Index – PONOTIDS_1
2 – Buyer Note Index – PONOTIDS_2
3 – Vendor ID Index – PONOTIDS_3
4 – Comment Note Index – PONOTIDS_4
5 – Payment Term ID Note Index – PONOTIDS_5
6 – Shipping Method Note Index – PONOTIDS_6
7 – Currency ID Index – PONOTIDS_7
8 – Tax Schedule Index – PONOTIDS_8
9 – Freight Tax Schedule Index – PONOTIDS_9
10 – Misc Tax Schedule Index – PONOTIDS_10
11 – Contract Number Index – PONOTIDS_11
12 – PONOTIDS_12 – not used at this time
13 – PONOTIDS_13 – not used at this time
14 – PONOTIDS_14 – not used at this time
15 – PONOTIDS_15 – not used at this time

PO Line Note ID Array Array in POP10110:
1 – Item Number Note Index – POLNEARY_1
2 – Location Code Note Index – POLNEARY_2
3 – Line Comment Note Index – POLNEARY_3
4 – Inventory Index Note Index – POLNEARY_4
5 – PO Line Note Index – POLNEARY_5
6 – Item Tax Schedule Note Index – POLNEARY_6
7 – Site Tax Schedule Note Index – POLNEARY_7
8 – Landed Cost Group ID Note Index – POLNEARY_8
9 – Shipping Method Note Index – POLNEARY_9


Last Updated: May 9, 2014

277 Responses to “POP Tables”

  1. Is there a way to find out which user closed a purchase order?

    Like

  2. Victoria I we appear to have a password set up when removing a PO commitment for a Sales Document we dont know what this password is or where to change it. Can you help with the location of this setting.

    Like

  3. We are getting a ‘Cannot insert duplicate key in object ‘dbo.###156242′. The duplicate key value is (PO 0005619, RCT00023288).

    This is an old PO, and it looks like its both open and in history, We tried closing the PO, running a Check links and Reconcile to no avail. Should i delete it from the history table and try closing it again?

    Like

    • Debra,

      Without examining both the records in both the history and open tables, it’s impossible to say which you should keep. Generally, the historical record will be more complete and you want to get rid of the open one.

      -Victoria

      Like

      • Thank you for your quick response, it’s really appreciated. I checked the following history tables.

        POP30100 – Record does not exist
        POP30110 – Record does not exist
        POP30300 – Record does not exist
        POP30310 – Record is there
        POP10500 – Record is there

        Looks like the receipt line history is there but not the PO is self.

        Like

        • Hi Debra,

          I would not feel comfortable giving advice on what data to clear without looking at it. It is very easy to mess up more than you will fix that way. If you’re not sure, I would ask Microsoft support or your GP Partner to help you with this.

          -Victoria

          Like

  4. Hi Victoria :)
    Firstly, your site is an inspiration! I often refer to it during the course of a day for support issues and am always happy to say that I usually find what I am looking for, keep up the excellent work :)

    I have an issue which I am hoping either yourself or someone else has come across…

    We have PO Enhancement Approvals switched on and all is working well (using GP2010). However, we have a situation where when an employee leaves and the user id is removed from GP the purchase orders that they placed are randomly appearing in the approvals window? Is this something that you have seen before?

    I am trying to find something which I can write a script for to make sure that the orders do not “re=appear” but am struggling to see where the orders are coming from to be “re-approved”

    Any ideas or suggestions would be gratefully received :)

    Many thanks from an avid follower

    Tracy

    Like

    • Hi Tracy,

      Thanks for your kind words!

      I’ve not run into this situation before, so I don’t have any concrete advice for you, sorry. It almost sounds like a bug, but I have not heard of this one before. What build of GP 2010 are you on?

      Also, have you tried posting your question to the Dynamics GP Forum? Sometimes you an get some great advice there.

      -Victoria

      Like

  5. Hi Victoria,

    Love your site, use it all the time.

    Question regarding received PO Lines, POP10500, is there a history version of this table? Where are the received quantities for historical lines? Do they stay in the POP10500 table?

    My goal is to get a list of PO’s that have had lines received in the last X days, maybe you have a script / view for that already? I couldn’t find one.

    Let me know please,
    Thanks,
    Dave M

    Like

  6. Hi Victoria,
    Thanks for all the useful information. We have a request for a smartlist to show approved date and approved by persn for a PO. I have read that PO enhancements are not standard and must be built with smartlist builder. Any ideas on which tables and if this is even possible?

    Like

  7. Hi,

    Environment: Dynamics GP 10.0 SP5 , SQL Svr 2008
    Drop-ship Purchase Orders
    ——————————
    We have two different Drop-Ship Accounts for two different sites. But GP Allows only 1 Drop-Ship Account to be defaulted for Drop-Ship Purchase Orders. Since the Volume of POs is very high is it advisable to change the Inventory Account from the backend.in the following tables & fields for the specific site.
    POP10110-INVINDX and POP10500 – INVINDX

    Are there any other tables to be updated ? or Is there a better method of doing this,

    Thanks,
    Satish

    Like

    • Hi Satish,

      I have not done this before, so I cannot say for sure whether it will work. I would suspect you will also need to change the drop ship account on the sales transactions. You could try posting this question on the Dynamics GP Community Forum to see if anyone has done something similar before. Also, eOne has a product called Flexicoder – not sure if they can do POP, as well as SOP, but may be worthwhile asking them.

      -Victoria

      Like

  8. I have looked and looked and cannot seem to find the Batch Comments history for POP receipt batches. The batches are listed in POP30000, but I cannot find the Batch Comments. Many modules seem to have a BCHCOMNT field in their history table, e.g. SOP30100, IV30100, etc. Can you help? Thanks! I am relatively new to Dynamics GP (we run 2010), and I have found your site INCREDIBLY HELPFUL!

    Like

    • Hi Richard,

      It does not look like GP is storing the batch comment after the batch goes away. If this is important, here are some ways to possibly address this:

      • Create a custom SQL table and trigger to populate and store the batch comments.
      • Use Extender to create a custom window to enter and store comments.

      -Victoria

      Like

      • Thanks! At least I know I am not missing something obvious. It is so strange to me that all of the other modules save the comment and that POP does not. Oh well… maybe they will fix this going forward.

        Like

  9. How can i tell if its out of the box or customized. It may have been customized but was probably before my time.

    Like

  10. The total on the PO is correct, its only when we print it where the amount is wrong. We have not tried to reconcile cause every where we look the amount is correct, its only when we try to print.

    Like

    • Michael,

      If the total on the screen is correct, but it is not correct when printing, that sounds like a report issue. Are you using the out of the box report? If not, can you try that to see if you get the same results? Also, try from a different computer, different user, different printer…

      -Victoria

      Like

  11. I have a question about print a purchase order. We had a very large purchase order and we noticed that all the quantities were printing correctly but the ending total amount was not correct. When we look at the line numbers it looks like right around line 45 it starts over. Is this a report printing issue or is this something else? Any help would be great. We are using GP 10

    Like

    • Michael,

      It could be the report, hard to say without knowing anything at all about your environment. Is the total on the screen correct? Have you tried reconciling the PO? (Microsoft Dynamics GP | Tools | Utilities | Purchasing | Reconcile Purchasing Documents)

      -Victoria

      Like

  12. Victoria:

    I just notice that the POP Type = 8 when it was from an Intransit Transfer. Please update that part in the page. Thanks.

    Like

  13. Very useful information Victoria. This could be unrelated to what you’ve covered so far. But here’s my doubt:

    eConnect is used to Create/Receive/Distribute/Post a PO. However, this is not changing the PO Status to “Posted” in POP10500. A record can be seen under “Series Posting”. Problem is, we’re still able to see/edit PO/Receiving Entry. If we use the GP UI to do the same and do posting, then the PO and Receiving entry are no longer editable/visible.

    Question is whether it is possible to complete “Transaction Posting” using eConnect. If not, what is the alternative available?

    Like

    • Keshav,

      eConnect will not post any transactions in Dynamics GP. I would recommend a product like Post Master from Envisage if you want to automate posting transactions: http://envisagesoftware.com/post-master-for-dynamics-gp/.

      -Victoria

      Like

      • Thanks Victoria for the information and the useful link. Will check the suggested software. On a different note, is it possible to do it through the built in Stored Procedures? If it is a big NO NO, please let me know.

        Like

        • Keshav,

          There is a great deal of logic surrounding posting and it’s different for every transaction type. I would not recommend attempting something like this on your own.

          -Victoria

          Like

          • Understand the complexity involved Victoria, thanks.

            Is there any other product available which is similar to Post Master?

            Regards,
            Keshav

            Like

            • Keshav,

              There may be others, I don’t know. Post Master is the only one I have used.

              -Victoria

              Like

              • Victoria,

                We’ve started using Post Master and it does address our requirement. Thanks for pointing in the correct direction.

                Do you’ve any idea on integrating Analytical Accounting (AA) with PO till it is posted? We’re using eConnect to do that, but the dimensions are not visible after posting.

                Regards,
                Keshav

                Like

                • Hi Keshav,

                  Glad that Post Master was able to help you!

                  I don’t really work with eConnect (or AA for that matter) much myself, so I cannot answer this for you. It’s possible you could create a custom object or add additional processing to an existing one to accomplish what you need, but I would talk to either Microsoft or an eConnect consultant about this.

                  -Victoria

                  Like

  14. Hi Victoria
    I am newbie here but it feels like great after reading all of this stuff, anyway we have a situation..We posted a Transaction under Purchasing>Receiving Trx Entry with POPTYPE=shipment, can we edit the POPTYPE to shipment/invoice if its already posted?

    Any help would be much appreciated

    -rolan vh.

    Like

    • Hi Rolan,

      There is no way to ‘edit’ this once posted. What you can do now is enter/match an invoice for the receipt you have posted. The only difference is that you are doing it in 2 steps (shipment, then invoice) as opposed to one step (shipment/invoice). The end result will be the same.

      -Victoria

      Like

  15. Hi Victoria,
    Need Your Help…Again :P ,
    PO table pop303310 missing receipt data.
    pop30300 table have the transaction(POPRCTNM is RCT00000000002901). But transaction not inserted in Receipt Line History table. ((pop303310 table have no transaction with RCT00000000002901) . How to solve this…if not possible how can i remove this transaction…. Please help.. :o

    Like

    • sry table name pop30310 not 303310 :P

      Like

      • Fazil,

        Helping clean up specific records is very difficult in this type of forum, since normally, I would want to run queries against a number of various tables and see the results. That said, as a first step for these kinds of issues, I would recommend running check links.

        -Victoria

        Like

  16. Carleesha Silvera Reply April 26, 2013 at 10:43 am

    Hi Victoria, I am not sure if you have ever been asked this question before, but is it possible to automate the invoice matching process in Dynamics GP 2010, or even do a mass match, instead of having to match one by one.

    Like

  17. Hi Victoria,
    First want to say Thank You for all of the amazing material I have found on your site! Really helpful to a newbie like me..
    I am trying to verify receiving GP data with data that is passed back from our WMS . I can find all posted receipts in the tables POP30300 , POP30310 & POP10500 , but I can not find the corrections (po returns). I see them in IV30300 – but where else do they reside – so that I can get to all of the other data fields?
    Thanks in advance

    Like

    • Hi Cindy,

      Thanks for your kind words.

      POP Returns should also be in the POP10500, POP30300 and POP30310 tables, but with different receipt types (POPTYPE):
      4 – Return
      5 – Return w/Credit
      6 – Inventory Return
      7 – Inventory Return w/Credit

      -Victoria

      Like

  18. Sreedhar Neekalapu Reply March 8, 2013 at 10:24 am

    Hi victoria,

    When does a POP move from received to closed status. I see a lot of PO’s that are received but not yet moved to closed status.Your answer is very much appreciated.

    Thanks
    Sreedhar.

    Like

    • Sreedhar,

      I believe PO’s move to Closed status after they have been received AND invoiced. Some companies I have worked with do not use the invoice matching functionality in GP, so their PO’s would never move to Closed. This would not going to cause any problems in GP, but it must be taken into consideration for reporting and when looking at old PO’s.

      -Victoria

      Like

  19. Hi Victoria-
    Similar to Nic, I have a very basic question that I have been unable to find the answer to. When we first implemented GP 2010, we created some POs for non inventoried items. Subsequently, in a hurry to process the payment, we created an invoice without receiving the purchase order. Is there any way to subsequently link the invoice to the released PO, or is our only option to cancel or void the PO? Thanks!

    Like

    • Hi Liz,

      If your invoices are POP invoices (with line items) then this may be possible, although it would involve changing things in the database and I have never done this before, so I could not tell you how to do it or what kind of effort (or cost) it would involve. If your invoices were entered directly in Accounts Payable, then I can’t really see any way to accomplish this without a significant customization.

      Just about anything is possible with enough work. In my experience, when presented with the actual cost of doing something like this, most companies do not think it is worth it. If you think that this is important and may be worth spending some money on, then it may be worth pursuing. Otherwise, cancelling or voiding the PO’s, while not the only option, may be the best option.

      -Victoria

      Like

  20. Hi Victoria,

    First and foremost I would like to thank you on the resolution you have provided to us regarding our issue on voiding transactions which was partially applied.

    Anyway i have issue again is it possible In GP Receiving Transaction module to received a purchase order with a status of unapproved. We encountered this issue every now and then and as far as I know receiving of unapproved P.O is not allowed in GP Purchasing module.

    Again hoping for your response with regards to this inquiry.

    Many thanks,

    Myrna

    Like

    • Myrna,

      I agree, if you have Purchase Approvals turned on, you cannot receive an unapproved PO. However, is it possible that the PO was approved at the time of receipt, then something changed on the PO, causing it to be unapproved again?

      -Victoria

      Like

  21. I bow to you, Victoria! You make my job easier.

    Like

  22. Thank you Victoria. We worked with our GP Partner all day yesterday and dispite her best efforts, should could not recover more up to date data due to the data corruption which sucks! She recommended recoverytoolbox.com but said it may take the software days to run which we don’t have. So now we’re faced today with rekeying 30+ days of data.

    Unfortunately, we’re going to obviously not have those open drop-ship and warehouse purchase orders. Additionally, we won’t have any unposted sales order batches awaiting transfer to invoice either. We’ll use our posting journals to recreate the financial data, but everything else is lost. My concern now is the integrity of our financial data as a result of these missing componets….Do you have any idea how long the recovery software program might take to recover data?

    Thanks for you help!

    Like

    • Neciey,

      I have never heard of recoverytoolbox.com, so I cannot tell you anything about what to expect. We had a customer who lost 2 of the drives on their SQL Server a number of years ago. It was very costly, but Ontrack was able to recover all of their data within a day or two. (They have different turn-around levels that determine cost.)

      There is no way that anyone can predict what kind of issues you might run into if you are only able to partially recover your data without in-depth analysis of the data itself. As a start you will need to run all the possible SQL as well as GP maintenance on the data to see what comes back, but I would not even hazard a guess as to the results without being able to work with the data myself.

      -Victoria

      Like

  23. Victoria,
    We had a problem with our backup exec and as a result lost 2 drives on our server. The last good backup was over a month ago and GP could only be restored to that point. As a result we’ve lost 30+ days of data. Is there any way to recover this data using a sql script

    Like

    • Neciey,

      If you do not have the physical database files, I would talk to a data recovery company, such as Ontrack. If you do have the database files, I would talk to your GP Partner or Microsoft Support to help you recover / reinstall your data. This is pretty difficult and important stuff and you want to make sure it is done right.

      -Victoria

      Like

  24. Hi victoria,

    I would like to implement customization in GP. I would like to add one new field i.e. “Bill of Lading Date” on PO line item level which can be used for each item. I can easily add control on the GP screen but i’m bit confused for database end. Because to store this value in database, i will need to add one new field in POP10110 table which contains standard fields provided by MICROSOFT.

    Is this doable to add new field in this existing table POP10110 to store its value ?

    Thanks,

    ~Yash~

    Like

  25. Hi Victoria,
    I normally post my questions in Microsoft site first, but no one seems to know the answers to my questions. Here is one of them:

    I’m trying to mass-close old POs that have not been closed or cancelled using SQL.

    Can I just do the following?

    UPDATE POP10100 SET POSTATUS = 5 WHERE PONUMBER in (‘PO1′, ‘PO2′,’PO3’…)

    This only changes the header level status, but not line-level status. I’m not sure if I will be able to remove them to history afterwards.

    Or do I need to change the line-level status too? If so, would I be changing to 5 (closed) or 6(cancelled)?

    Does this interfere with GP’s normal process?

    Any input would be appreaciated. Thank you.

    Like

    • Hi Takeshi,

      I have never done this directly in SQL, however, I would suspect that you would need to update the status of every line to Cancelled. I would recommend a lot of testing to confirm that everything will work as expected and that nothing will interfere with other processes in GP.

      -Victoria

      Like

      • Thanks, Victoria. I’ll try the testing route.

        Like

        • Hi Takeshi,
          I’m curious about the route you took on this process. I’m looking to do the same thing, but at the moment I’m using eConnect to do this. I’m running into issues, which I’ve submitted to Microsoft, but am still waiting to hear back from them.

          Thanks!
          -Michael

          Like

      • Hi Victoria,

        I have a Test PO that has been received and therefore cannot be deleted or cancelled. I also cannot process a Return in order to Close it because the system says that there is an Unposted Invoice attached to it. However there is no visible Unposted Invoice for this Vendor on any reports or in any Unposted Batch. It is also not showing up in Series Post for either the GL or the Purchasing Module. Please Help! I need to close or remove this PO.

        Thanks,

        Miranda

        Like

        • Miranda,

          It sounds like you may have a stuck PO receipt/invoice in the table that is causing this. Running Check Links on the Purchasing series typically fixes this. If not, you may need to fix this directly in the tables. But I would start with Check Links.

          -Victoria

          Like

          • Victoria,

            I am at the point where Check Links did not correct this issue. Can you tell me which tables/fields I would need to correct?

            Thanks,

            Jeanne

            Like

            • Hi Jeanne,

              I believe you want to look in POP10300 and POP10310. However, without actually looking at it myself, I would not be sure what you need to change. Sorry, I just like to be very careful with fixed directly in the database, as it is very easy to mess something up.

              -Victoria

              Like

  26. Hello Victoria – I have a very remedial question where utilizing the purchase order entry is concerned. In general, creating the purchase order will not directly affect the general ledger at its origination correct? We are considering using this in the near future and are trying to understand the way purchasing batches relate to the general ledger at the point of posting. Is it only posted to the purchasing module and at point of posting the actual invoice for payment is where the expenses (and AP aging) are actually altered? Again I apologize for the very basic question and thank you in advance to any insight you can provide.

    Like

    • Hi Nic,

      You are correct, creating a Purchase Order will not have any General Ledger impact. There are 3 typical steps to the process in the POP (Purchase Order Processing) module:

      1. Purchase Order – this creates a record of your orders and (if needed) gives you something to print to send to the vendor. No GL entries are created.
      2. Receipt of goods – this records the fact that you received the items on the PO. If you are are receiving inventory items, this will record a debit to Inventory and a credit to Accrued Purchases in the GL. This also updates the Inventory subledger.
      3. Invoice – this records the payable due to the vendor. In the GL it will record a debit to Accrued Purchases and a credit to Accounts Payable. It will also update the Payables subledger.

      Of course there are variations on this process, but hopefully that gives you a better idea of the process.

      -Victoria

      Like

      • Hi Victoria!
        Thank you so much for the response! I have one more remedial question while we are in the middle of trying to figure out if this will work for us. At the point of wanting to post the invoice – when there is a variation in cost associated with a line item the system automatically generates a line at the distribution level to account for the dollar difference however it goes to the default clearing account and claims it cannot be changed at this level? It says I have to make the change at the inventory level however the purchase is for a non-inventoried item. Is the only way to let it go to the clearing account each time and then JE it out at the ledger level or is there a way around this?
        Thank you again!
        -Nic

        Like

        • Hi Nic,

          For non-inventory items, I believe all the accounts should be taken from the vendor setup. So if you change (or set up) the Purchase Price Variance on the Vendor Account Maintenance window, that should let you determine the account used for the cost difference for each vendor. However, sometimes that may not be enough, for example, if you use multiple purchase accounts for one vendor and change the account number per PO line item, as needed, then you would be stuck with only one variance account per vendor. A correcting JE is fine, however, there is another way. Prior to posting the invoice, when there is a variance, you can add the ‘reclass’ distributions right there. Simply add two distributions lines with a MISC type to debit and credit the appropriate accounts. This way you link the reclass to the transaction it belongs with right away.

          Hope this helps,
          -Victoria

          Like

  27. Alexander G. Schwallbach Reply May 15, 2012 at 3:00 am

    Hi Victoria,

    I am trying to get a data set containing all purchase order line items and associated headers for the last seven days. I am including POP30100 and POP30110 just in case someone issues a PO and receives it since that would put it in the history file. While I think the chaces of that happening are slim I do not want someone to be able to slide one through the system. This is the code that I came up with. Is there any reason to believe I will get an incomplete dataset?

    SELECT POP10110.PONUMBER AS PO_Number, POP10100.POSTATUS AS PO_Status, POP10110.POLNESTA AS PO_Line_Status, POP10110.ITEMNMBR AS Item_Number,
    POP10110.ITEMDESC AS Item_Description, POP10100.USER2ENT AS EnteredBy, POP10110.VENDORID AS VendorID, POP10100.VENDNAME AS VendorName,
    POP10110.UOFM AS UofM, POP10110.QTYORDER AS QtyOrdered, POP10110.QTYCANCE AS QtyCanceled, POP10110.UNITCOST AS UnitCost,
    POP10100.PYMTRMID AS Payment_Terms, POP10100.DOCDATE AS PO_Date, POP10100.LSTEDTDT AS Last_Edited, POP10100.MODIFDT AS Last_Modified,
    POP10110.INVINDX AS Cost_Account
    FROM POP10110 INNER JOIN
    POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
    where POP10100.LSTEDTDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP10100.REQTNDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP10100.MODIFDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP10100.CREATDDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP10110.Released_Date >=dateadd(day,datediff(day,0,GetDate())- 7,0)
    UNION ALL
    SELECT POP30110.PONUMBER AS PO_Number, POP30100.POSTATUS AS PO_Status, POP30110.POLNESTA AS PO_Line_Status, POP30110.ITEMNMBR AS Item_Number,
    POP30110.ITEMDESC AS Item_Description, POP30100.USER2ENT AS EnteredBy, POP30110.VENDORID AS VendorID, POP30100.VENDNAME AS VendorName,
    POP30110.UOFM AS UofM, POP30110.QTYORDER AS QtyOrdered, POP30110.QTYCANCE AS QtyCanceled, POP30110.UNITCOST AS UnitCost,
    POP30100.PYMTRMID AS Payment_Terms, POP30100.DOCDATE AS PO_Date, POP30100.LSTEDTDT AS Last_Edited, POP30100.MODIFDT AS Last_Modified,
    POP30110.INVINDX AS Cost_Account
    FROM POP30110 INNER JOIN
    POP30100 ON POP30110.PONUMBER = POP30100.PONUMBER
    where POP30100.LSTEDTDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP30100.REQTNDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP30100.MODIFDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP30100.CREATDDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
    POP30110.Released_Date >=dateadd(day,datediff(day,0,GetDate())- 7,0)

    Any way I could do this better?

    Like

    • Hi Alexander,

      This looks good to me.

      -Victoria

      Like

    • Alexander – Loved the query. Made a couple of changes I thought you might appreciate. Added a Field to let you know if the records were in Activity or History Tables, and added a CASE statement to convert the PO Status numbers to Text.

      SELECT 'ACTIVITY' DATA_SOURCE,POP10110.PONUMBER AS PO_Number, 
      POP10100.POSTATUS AS PO_Status,
      CASE POSTATUS
      	WHEN 1 THEN 'NEW'
      	WHEN 2 THEN 'RELEASED'
      	WHEN 3 THEN 'CHANGE ORDER'
      	WHEN 4 THEN 'RECEIVED'
      	WHEN 5 THEN 'CLOSED'
      	WHEN 6 THEN 'CANCELED'
      	ELSE 'INVALID'
      END PO_STATUS_Text,
      POP10110.POLNESTA AS PO_Line_Status, POP10110.ITEMNMBR AS Item_Number,
      POP10110.ITEMDESC AS Item_Description, POP10100.USER2ENT AS EnteredBy, 
      POP10110.VENDORID AS VendorID, POP10100.VENDNAME AS VendorName,
      POP10110.UOFM AS UofM, POP10110.QTYORDER AS QtyOrdered, 
      POP10110.QTYCANCE AS QtyCanceled, POP10110.UNITCOST AS UnitCost,
      POP10100.PYMTRMID AS Payment_Terms, POP10100.DOCDATE AS PO_Date, 
      POP10100.LSTEDTDT AS Last_Edited, POP10100.MODIFDT AS Last_Modified,
      POP10110.INVINDX AS Cost_Account
      FROM POP10110 INNER JOIN
      POP10100 ON POP10110.PONUMBER = POP10100.PONUMBER
      where POP10100.LSTEDTDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP10100.REQTNDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP10100.MODIFDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP10100.CREATDDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP10110.Released_Date >=dateadd(day,datediff(day,0,GetDate())- 7,0)
      UNION ALL
      SELECT 'HISTORY' DATA_SOURCE,POP30110.PONUMBER AS PO_Number, 
      POP30100.POSTATUS AS PO_Status, 
      CASE POSTATUS
      	WHEN 1 THEN 'NEW'
      	WHEN 2 THEN 'RELEASED'
      	WHEN 3 THEN 'CHANGE ORDER'
      	WHEN 4 THEN 'RECEIVED'
      	WHEN 5 THEN 'CLOSED'
      	WHEN 6 THEN 'CANCELED'
      	ELSE 'INVALID'
      END PO_STATUS_Text,
      POP30110.POLNESTA AS PO_Line_Status, POP30110.ITEMNMBR AS Item_Number,
      POP30110.ITEMDESC AS Item_Description, POP30100.USER2ENT AS EnteredBy, 
      POP30110.VENDORID AS VendorID, POP30100.VENDNAME AS VendorName,
      POP30110.UOFM AS UofM, POP30110.QTYORDER AS QtyOrdered, 
      POP30110.QTYCANCE AS QtyCanceled, POP30110.UNITCOST AS UnitCost,
      POP30100.PYMTRMID AS Payment_Terms, POP30100.DOCDATE AS PO_Date, 
      POP30100.LSTEDTDT AS Last_Edited, POP30100.MODIFDT AS Last_Modified,
      POP30110.INVINDX AS Cost_Account
      FROM POP30110 INNER JOIN
      POP30100 ON POP30110.PONUMBER = POP30100.PONUMBER
      where POP30100.LSTEDTDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP30100.REQTNDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP30100.MODIFDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP30100.CREATDDT >=dateadd(day,datediff(day,0,GetDate())- 7,0) or
      POP30110.Released_Date >=dateadd(day,datediff(day,0,GetDate())- 7,0)
      

      Like

  28. Hi All,
    I am trying to find the table where the purchases account for the line transactions that are stored in the POP10300 table(Purchasing Receipt Work), does anyone know the purchases account is linked.

    I am trying to create a list of all unposted invoices which will also show me the purchases account to form a set of accurals. Any ideas?
    Thanks
    Rob

    Like

  29. Hi Victoria

    I am looking for a way to calculate the next PRMDATE from POP10110, Below is what I am using but I kep getting the occasional date that is before todays date. Any ideas

    SELECT dbo.x_CS_INV_LIST.Code, dbo.x_CS_INV_LIST.[Desc], CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0)
    < 30 THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END AS Qty_Avail, MIN(DISTINCT CONVERT(VARCHAR(10), DATEADD(day,
    20, dbo.POP10110.PRMDATE), 101)) AS ETA
    FROM dbo.IV00102 LEFT OUTER JOIN
    dbo.x_CS_INV_LIST ON dbo.IV00102.ITEMNMBR = dbo.x_CS_INV_LIST.Code LEFT OUTER JOIN
    dbo.POP10110 ON dbo.x_CS_INV_LIST.Code = dbo.POP10110.ITEMNMBR AND dbo.POP10110.POLNESTA < 4
    WHERE (dbo.IV00102.LOCNCODE = 'WHSE')
    GROUP BY dbo.x_CS_INV_LIST.Code, dbo.x_CS_INV_LIST.[Desc], CASE WHEN ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0)
    < 30 THEN 0 ELSE ROUND(IV00102.QTYONHND – IV00102.ATYALLOC, 0, 0) END
    HAVING (dbo.x_CS_INV_LIST.Code IS NOT NULL)

    Like

  30. Hi Victoria, I need to pull data about purchases per vendor, store or fiscal period, do you know where is the table where I can find this data? or I need to extrat the data from many tables?

    regards

    thanks!

    Like

    • Hi Francisco,

      What you’ve asked is not really specific enough to answer your question. Do you have a list of columns you’re looking to see on your report and a more detailed description of what you are trying to accomplish?

      -Victoria

      Like

  31. Hi Victoria,

    We have unposted transactions under batch CheckLink. We confirmed that these transactions had already been re-created and posted.

    Can I safely Delete or Void these unposted transactions? What’s the difference between delete and void?

    Thanks,

    Chris C. Ardines

    Like

    • Chris,

      Yes, if you have already re-created and posted these, I don’t see any reason why you should not void or delete them. Deleting something clears it out of the system without leaving any record of it. Voiding something keeps a record without having it affect any balances or totals. If you think there may be a reason to refer to these later, it may be better to add a note explaining what happened with these and void them.

      -Victoria

      Like

  32. Hi Victoria – Happy New Year! Your blogs has been most helpful.

    I am using smartlist builder to re-create the Receiving Edit List Report. I need the vendor, item number, item description, qty shipped, subtotal, extended cost, GL accounts, debit and credit amounts. Here are the tables I have joined: Purchasing Receipt Work > Purchasing Receipt Line Work > Purchasing Distribution Work. Can these tables be joined together without duplicating line items? I am stuck on how to join the Purchasing Distribution Work. For example, a transaction has 2 items and 4 GL accounts were used. The smartlist would show 8 line items. Is there a better way of capturing this data?

    Thank you in advance for any adivce you may provide.

    Like

    • Linh,

      I don’t think there is a better way to do this in SmartList because if you are linking to the GL distributions you’re going to have to see the lowest level of detail, so you’re always going to see lines x distributions number of lines in your results. Since GP does not link the items with the distributions in any way, there is really no meaningful way to show this all in one SmartList. Using a different reporting tool (something like Crystal or SRS), you could put the data in different sections. For example, there you can show something like:

      RECEIPT 12345
      LINE 1
      LINE 2

      GP DISTRIBUTIONS:
      Account 1 Amount 1
      Account 2 Amount 2
      Account 3 Amount 3
      Account 4 Amount 4

      The only other alternative would be to create 2 separate SmartLists for this data: one with the line items, another with the distributions.

      Like

  33. I have some users that are asking for a promise vs ship / required vs receipt on time report.

    I believe I will need POP10110 & POP10110, possible POP30100 & POP30110. PO comments need to be seen as a column also.

    They are not very clear in their requirements (they asked for a SmartList to do all this) and I am having a hard time visualizing the SQL needed to create such a report.

    They wish to measure when something was
    – Promised vs when it actual arrived
    – Required vs arrived
    – Date ordered vs arrived

    Any suggestions, pointers, etc. to go with this would be appreciated.

    Like

    • David,

      As a very first step I would ask the users to show you (in the GP application) exactly what they mean by all of these dates. Otherwise you’re not going to be able to match them up to anything in the tables. For example – there are 3 possible dates with the word ‘promised’ – which one? Also, are they entering these dates at the PO (header) level of the line item (detail) level? Then you need them to decide what happens if there are multiple receipts. For example – promised date is 1/15/2012, there is a partial receipt on 1/14/2012 and another on 1/16/2012 – what do you show? Once you’ve got all the requirements nailed down, I believe that you can use POP10100, POP10110, POP30100 and POP30110 for the dates on the PO’s…then you can potentially use the POP10500 table for the receipt dates. If not, then you’d need to use POP30300 and POP30310.

      -Victoria

      Like

  34. Hi Victoria,

    We had a former employee create a Smartlist/Smartview report that was able to list each line of text in “Sales Comment Entry” for sales returns in GP. I was wondering if it was possible to get this same detail, only for Purchase Orders. Of course, this would be the “Purchasing Comment Entry” screen in POP. I’m not worried about Comment IDs, just the comments added to each particular purchase order.

    Thanks!

    Like

    • Hi Kevin,

      I see that the ‘header’ level comment (the one entered at the bottom of the Sales Transaction Entry window) is available in the out-of-the-box Sames Transactions and Sales Line Items SmartLists. However, the PO comments are not available to add in the same way in the POP SmartLists. So if you needed this, you would have to create a custom SmartList using SmartList Builder, I do not see any way to do this in any out-of-the-box SmartLists. :-(

      If you want the ‘header’ level comment, the one that appears at the bottom of the Purchase Order Entry window, that will be in table POP10150 (Purchase Order Comment). If you want the line item comments, they are in the table POP10550 (Purchasing Comment). One advantage of getting this data directly from the tables is that you would be able to add more than the 4 lines of 50 characters. The actual comment can hold up to 500 characters.

      Hope that helps,
      -Victoria

      Like

  35. Hi Victoria
    As always, thanks for such a great resource.

    We have a PO line item that is showing an incorrect value for On Order Qty on the ‘Purchase Order Processing Item Inquiry’ window. We have attempted to an item and PO reconcile but it never seems to hit on the right value. Would you know where this field value can be located and how to best correct the value? We have attempted to locate the field via the support debugging tool but the field found under ‘Item Scroll’ did not have an entry for ‘Associated Tables’

    Thanks
    Ryan

    Like

    • Hi Ryan,

      I am trying to answer this for you, but am not finding a window called ‘Purchase Order Processing Item Inquiry’ in my GP 2010… Can you walk me through the steps of how you get to this window?

      -Victoria

      Like

  36. From pop10110 field QTYUNCMTBASE do you now what does it mean?

    Like

  37. Hi Victoria, I love youir site, thank you. I have a stray PO commitment that is showing up under Inquiry>Purchasing>budget vs actual but when drilling in there is no PO associated with this commitment. I have done some testing and even if the PO is removed from the POP10100 and POP10110 the commitment remains. Do you have any advise on how to get rid of this stray commitment?

    Like

    • Hi Darlene,

      Thanks for the kind words! I have not worked with PO Commitment tables yet, however, I would probably look in the tables starting with CPO for this (or ECM if you’re using Enhanced PO Commitment Mgmt). Good luck!

      -Victoria

      Like

      • Thanks Victoria, I tried removing the document from the CPO10110 but the inquiry is still picking up the commitment. This is a bit odd, I have removed the POP10100 and POP10110(PO work) and the CPO10110(commitments), The ECM tables are empty even though I am using Enhanced PO Commitment. My theory is that there are references in other tables. I have used the window modifier to view the tables but most of them are Temp tables, the others.. budget and GL tables I have checked and there is no reference to a commitment. My next step will be to run a sql profiler while building the window. Ill keep you posted.

        Darlene

        Like

        • I have found this useful when hunting for strays. http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm This provides a script to create a stored procedure that you can include in a query (included on this link as well) to search all tables in a db for a certain reference. It will give you the table and field name in which the reference exists. Depending on the performance of your DB server, you may want to do after hours as any keyword search on all tables and fields will be intensive. I have had no issues though on mine. It is a Dell 2950 w/dual quad core 2Ghz cpus, 4GB RAM and 15K rpm drives in mirrors (3 separate R1 pairs for OS/page/app) on a PERC5/i controller. PERC5/i is the weakest link but other than the time it takes to hunt through everything, no one else has had performance issues while I used the query. Anyway, it may help you hunt down what you are looking form.

          Like

  38. Thanks a Million. I am always coming to your site for GP table questions. The DecPLCUR information here was just what I needed for the last month.

    Thanks again.

    Like

  39. Hello,

    I was wondering if you could tell me the difference between the two tables:
    POP30110 – Purchase Order Line History (line detail)
    POP30310 – Receipt Line History (line detail)
    Thanks in advance!!

    Like

    • Sherry,

      POP30110 holds PO lines – these are lines entered on the Purchase Order Entry window.
      POP30310 holds receipt lines – lines entered on POP transactions, like receipts, invoices and returns.
      Hope that helps.

      -Victoria

      Like

  40. Victoria

    We were recieving in a PO when GP crashed now one of the line items is not showing to recieve. How can I get that one back.

    thanks
    Vic

    Like

  41. Hi Victoria,

    We have a situation where GP crashed while PO invoice matching was being entered. The POP10310 has correct information, but the POP10300 is missing most of the information other than the receipt number. I have deleted the transactions from the POP10300, POP10310, POP10500, and POP10600 tables and ran check links on PO transactions, which is also deleting the related distributions. The problem is that the items are no longer available for invoice matching. It seems as though there must be something I need to update in the PO tables, but I can’t seem to find it. Do you know what I am missing?

    Thanks for your help.

    Like

    • Joanne,

      This is very difficult to say for certain without looking at the data first, but based on what you’re describing, most likely you need to update the statuses of the line items to show that they are not matched to an invoice.

      -Victoria

      Like

    • Have you been able to resolve this problem? We had the same thing happen and I deleted the transaction from the same tables that you mentioned above. I also checked the PO Line status and it is showing received. But, the line items are still not available to be matched.

      Like

      • Yes, I did figure out the last piece of the puzzle. The shipment invoice in the POP10500 table has a QTYMATCH field that is updated with the quantity matched on the invoice receipt.

        You can find out the shipment receipt number that was being matched from the POP10600 table: select POPRCTNM from POP10600 where POPIVCNO= ‘RCT1184′

        The update can be a little tricky if there are multiple shipment receipts on the invoice and/or if the quantities being invoiced are not the full amounts that were shipped on the shipment receipt. The following is a simple script that will open up a shipment receipt to be matched against, assuming that nothing was previously invoiced against the shipment receipt.
        Update POP10500 set QTYMATCH=0 where POPRCTNM=(select POPRCTNM from POP10600 where POPIVCNO= ‘RCT1184′)

        Like

  42. Hi Victoria, how would one restrict the delete and void button for New Approved POs? Thanks.

    Like

  43. Hi Victoria,

    How would one restrict the Delete and Void button in PO for New Approved POs? Thanks.

    Like

    • Jamie,

      This should be possible with a customization, but not out-of-the-box. However, I believe as soon as you release a PO, the delete and void options are not available anymore, so that might be a work around.

      -Victoria

      Like

  44. Hi Victoria,
    Where (Table) can I find the Payments to all the PO’s which are not closed?

    Thanks,
    Aaron

    Like

  45. Victoria,

    We have an issue where for whatever reason, the dropship account is not being properly applied for drop ship purchases. Sometimes they get the inventory account instead and when the item card is inspected, it has the correct drop ship account listed. Also, it happens for only 2 of our 3 item classes. The 3rd works like a charm. I did an upload to the DPSHPIDX for all three of these. I have confirmed it is correct. And like I said, it is more an odd case with significant headaches to fix when it does not use the correct account. In the past, at least it asked which account to use but now, having something there, even if it is the wrong account (standard inventory account), then is is more of a pain during end of month reconciles if not caught during the day to day workflow process. I have seen comments on the offical GP newsgroups for the problem by others but no answers or responses from anyone. Is there another place GP’s database where the dropship inventory account needs to be applied?

    Thanks in advance again!

    Like

    • Allen,

      GP first looks at the account set up on the Item Account Maintenance window, then at the Posting Accounts for the company. There are 3 different Drop Ship Items accounts you can set up here – one each under Inventory, Sales and Purchasing. If you have one account that is used for all drop ships, I would recommend making sure all 3 of these are set up with that account, then you should not need to set these up for individual items. If you use different accounts each time, then you may be better off clearing these so that the system goes back to asking you if the account is empty for some reason.

      -Victoria

      Like

      • So, I checked these three posting accounts and they are all blank. So it should just use the drop ship account from the inventory item. If it is not doing that, you would say then that it is a bug in GP?

        Like

        • Allen,

          I would be surprised if this was a bug in GP. You don’t say what version of GP you’re on, but this is functionality that has existed for a very long time and I have never heard of this being an issue. Before suspecting a bug, I would check a number of other things, including:

          • The actual transactions – are they really “drop ship” or is this just internal terminology that is used by your users?
          • Are the accounts being used possibly being substituted because of the Inventory Site Segment ID setup?
          • Are there any customizations or additional products installed that may be changing the expected behavior?
          • Is it possible that the issue is with old transactions, that were entered prior to recent changes?
          • Go through the setup to confirm it and try to duplicate the issue at will, not randomly.

          -Victoria

          Like

          • Ok. So I have been testing this. They have an order that went through warehouse processing and we don’t have the part. So they go back and drop ship the item. For one item class/order type which is used to group product lines, it will update the Inventory account. For the other two, it will not. Now, I think, hey, let’s clear the drop ship account, roll down the changes. Bam! Now, when I drop ship on this, it removes the inventory account. So, I go back, add the DS inventory account back in, roll down the changes to the items in the class, do the same process on the drop ship and it WON’T UPDATE FROM THE STANDARD INVENTORY ACCOUNT!!!!! ARRGGGHHHH! What the heck. So it will remove the inventory account when not specified but it won’t update to the right account. And that is just for 2 of the 3. For the third product line I have tested it and it works correctly changing out the inventory account for the drop ship account as expected. Irratating…

            Like

            • Allen,

              This doesn’t sound right…what version and SP of GP? I can try to test this to see if I get the same results, however, I suspect this is something you really need to show someone, there are just too many moving variables and possibly very specific setup to easily help with this in blog comments. Have you talked to your GP partner or GP support about this to see if you can walk through what you’re testing with them in a remote session?

              -Victoria

              Like

              • I have since opened a ticket with Microsoft. One of those 5 included per year so I need to use them up. But ya, something is definitely not right. Either a config issue buried somewhere or a programming error that people have not noticed… I am on GP 10 SP4.

                Like

                • Allen,

                  This is probably a good one to use a support incident on, especially if you have already paid for it. I don’t have GP 10.0 SP 4 available in a test environment, so since you opened up a support incident – let’s see how that goes. If you find anything interesting, would you mind posting back to let me know?

                  Thanks!
                  -Victoria

                  Like

                • Allen, did you find a solution to your problem? I think I might be having the same issue with SP5.

                  Like

                  • Sorry for the late response. Things got crazy here at that time. Anyway, there is a conflict in how GP applies information with sites and the accounts for Item Class. In Site Maintenance, there is an option for “Segment3″. For whatever reason and against the documentation and knowledge of the MS GP Tech, this was overriding the account setup on the item. What confused it, is that we had one Item Class for which we used the PSTL GL Account changer to move segment 3 listing of 20 to 99. The tool made all the necessary updates but of course, did not change the Site Maintenance Segment 3 listing. Which I guess is good but also confused things. So our 2 other Site/Item Classes of 10 and 30 when needing to be 13 and 33 respectively were overridden with 10 and 30 from Site Maintenance. Now, MS GP Tech said that if you have an account specified in Item Account Maintenance, it was the authority and nothing would change it. But that is not the case. Since I have had other problems, such as Bin removal removing entries for default bins (SOF BIN etc) and they came back with “the help file is wrong”, I didn’t pursue it further. I figured they would give the same lame excuse. Anyway, I deleted the Segment3 from Site Maint and the problem is fixed. But, it does mean that you must have all accounts setup against the Items. Hope that helps.

                    Like

                    • Allen,

                      Thanks for the follow up! It’s a little hard to follow this without seeing it in action, but it sounds to me like this is using the Segment ID for Sites and substituting based on what is in the site setup – this is standard (and expected) GP behavior. And yes, if you are using this functionality, then you must have all the accounts set up prior to them being needed, otherwise GP will not be able to correctly do the substitution. Maybe I am misunderstanding…but in any case, you’ve got it fixed, so it’s all good. :-)

                      -Victoria

                      Like

  46. Hi Victoria,

    I have request to modify the existing Purchasing Invoice Batch Edit list (POP Receivings Posting Journal). The users would like to include the Distribution Reference field as part of the GL Distribution section. in reviewing the GP report, it appears the distribution information is stored in the POP_PostingJournal_TEMP table. I know trying to link a temp table other tables is not easy, if at all possible.

    My question is do you know of a way to include the GL Distribution Reference field as part of the GP Standard POP Receivings Posting Journal report? Is it even possible. I saw in the Purchasing Receipt Work table there is a Reference field, but it doesn’t seem logical that it would be the same as the Distribution Reference field using in the GL Distribution Window.

    Like

    • Hi Lisa,

      Report Writer is not my tool of choice, so I am not the best person for these types of questions. But have you tried adding POP10390 (POP_Distribution) table? I believe that is the table that has the data you want.

      -Victoria

      Like

  47. Alishia,

    I am not able to reproduce this, so I am not sure what is causing it. I would suspect an account is not set up correctly somewhere, either on the item setup, the vendor setup, or the Posting Accounts setup. If you have checked all of those and they are all correct, then perhaps something else is causing the problem. Do you have any customizations or 3rd party products installed? I would recommend posting your question to the Dynamics GP Community forum, as you have a better chance of finding someone that has run into a similar issue there.

    -Victoria

    Like

  48. Hi Victoria,
    on Purchase Receipts Inquiry screen, it displays the Quantity received and Quantity Sold for each purchase Order. Is there a table/view where I can find this information?

    Thanks for your help.

    -Aaron

    Like

  49. Hi Victoria!

    I was reading on the GP documenation that you can set a PO ‘On Hold’, print it, have a supervisor to approve it, then at that point you can change the status to ‘Approved’. When I try this I get a message stating that ‘Unapproved PO can not be printed’. Am I missing something here?

    Like

    • Victoria,

      It sounds to me like that documentation is not correct…can you tell me where you were reading this?

      -Victoria

      Like

      • I am GP 10. From the Purchase Order Entry windows, click on Help and select ‘About this Window’. The select ‘Placing or removing a purchase order hold’. Look at second paragraph:

        “Occasionally a situation will arise that requires you to temporarily place a purchase order on hold to stop further processing. For example, a buyer may want to use a hold if he or she has a purchasing amount limit, and needs a supervisor’s approval before sending a purchase order to the vendor. The buyer could enter a purchase order and place it on hold, then print a copy to send to the supervisor for approval. The printed purchase order will clearly indicate that it is on hold. The buyer could later remove the hold with the supervisor’s approval, and then print the purchase order and send it to the vendor. The supervisor could also review the purchase order online and remove the hold

        Like

        • Victoria,

          Thanks, that is helpful. What you are looking at is correct, but only for the POP module without PO Approvals activated. :-( PO Approvals is actually part of a separate module, called POE (PO Enhancements). When the POP module documentation is using the word ‘approval’ in the description, it is talking about a verbal approval, not the approval you are thinking of, as it is used with PO Approvals activated. While the word choice is unfortunate, the documentation is correct, it just does not apply to you. With PO Approvals activated, I do not believe there is any way with out-of-the-box GP to print an unapproved PO.

          -Victoria

          Like

    • That’s explain it.

      Thank you so much!!

      Like

  50. I was trying to list the NOTES entered against the receipts using the below query.

    SELECT POP30300.POPRCTNM, SY03900.TXTFIELD
    FROM POP30300, SY03900
    WHERE POP30300.RCPTNOTE_1 = SY03900.NOTEINDX
    AND POP30300.POPRCTNM = <>

    The query returned first two lines from the NOTES where the original NOTE has 5 lines.

    Any clue on why it is not returning the remaining 3 lines.

    Like

    • Jaykay,

      How/where are you running this? In SQL Server Management Studio, if your output is set to Results to Grid, all the text in a note will come in as one line, no matter how many lines the note actually has. If you switch to Results to Text, you will see the actual lines. I just tested this with a note with 5 lines and it shows as 5 lines. (I did have to remove the last line of your query, as that caused an error.)

      -Victoria

      Like

  51. Hello –

    Just want someone’s agreement (or disagreement). In the POP10500 table, is the QTYRESERVED = to the quantity returned?

    Thank you

    Like

    • Hi Stephanie,

      According to the GP 2010 SDK, Quantity Reserved = “The quantity to be returned.”

      -Victoria

      Like

      • Hi Victoria – Can you further explain how data is populated into the QTYRESERVED field? You have stated that it’s “The quantity to be returned.”, so if someone started a purchasing return however changed their mind and exit out with posting, GP will still hold that quantity?

        Like

        • Linh,

          Unfortunately, I have no further information about how that field is populated. There is only that one mention of it in the SDK with what I previously posted. If this is important, I would recommend starting a support case with Microsoft to find out for sure.

          That said, based on my experience, as a general rule in GP, if you started entering a transaction and you exited without posting (ie, you deleted the transaction), GP should reverse any quantities or amounts that may have been originally ‘held’ by the transaction.

          -Victoria

          Like

    • thanks Victoria for the quick response. For some reasons the result is not appearing in grid and text in the SQL Server Management Studio. But I could get the desired results through smartlist.

      Like

  52. Does gp store Purchase Receiving Info when we match it to a Purchase Invoice ie what is link between Purchase Reveiving and Purchase Matched Invoice. – Thank

    Like

  53. Hi Victoria

    I am wanting to track company overheads. Basically one of the shortfalls of most systems is that the general ledger balance at a point in time can be easily verified and compared to “budget” etc. but that does not take into account open purchase orders which are yet to be cancelled or received. My question is which table holds the detail for open purchase orders (POP10110???) or would it have to be calculated between purchase order detail and receipt line detail?

    I am fairly new at working around the GP framework, but most tables seem to be split up between work and history. In terms of the Purchase order tables, can I assume that the receipts history tables are receipts for closed PO’s and that the POP10310 are receipts for PO’s still open?

    Thanks a million, your table information has allowed me to shave days off some of my staff’s reporting.

    Regards

    Digby

    Like

    • Hi Digby,

      There is a PO Commitments module available that might help you with this requirement.

      As far as the tables, this can get pretty complicated, because you can have partially fulfilled Purchase Orders and also because PO’s do not automatically move to history – there is a manual process to do that. I would recommend that you look at the status of each PO line in POP10110 when you are creating any reporting like this. You will also need to match that up to receipts, a good table for that is POP10500.

      You cannot “assume that the receipts history tables are receipts for closed PO’s and that the POP10310 are receipts for PO’s still open” – this is not how GP stores the data. POP10310 simply holds unposted receipts – whether the PO is closed or not. However, you may find that you do not need POP10310 or POP30310, as POP10500 table is typically better for getting the receipt data.

      -Victoria

      Like

  54. Victoria, I am going to check a few other feeds but I wonder if you have experienced this. GP 10 SP4. The POP Receivings Edit List as a total for the GL distributions of $27,364 (Debit to Inv Purch, Credit to Accrued Purchases) but the Inventory value is actually $34,487.87 and when we add up the lines extended cost on the edit list is is $34,487.87 (quick Crystal Sum on the EXTDCOST from the POP30310 table for this receipt). There is nothing funny looking in the distrib from POP30390 that I notice. Any idea of what may cause this? Any particular area that I should look for “missing data” on maybe the inventory items? All items in the receipt have their proper inventory index listings. All items current cost match the receipt cost.

    Like

    • Allen,

      What does the resulting GL entry look like? Is it correct (with the numbers you’re adding up to) or does it have the incorrect numbers on the Edit List?

      If the GL entry is correct and everything looks ok when you check the transaction and the tables, then it sounds like the Edit List report may have had some sort of glitch, and, unless it happens again, I am not sure if this is something I would be terribly concerned about. If the GL has the incorrect numbers, then it’s a much bigger concern and needs to be investigated further to determine what cause the incorrect posting.

      -Victoria

      Like

      • Well, the Journal Entry seems to be wrong. Inventory was increased correctly but the JE and the edit list have the same numbers. I will enter a ticket with MS to investigate further. Thanks.

        Like

  55. Hello,

    I am hoping I can get an answer here as I haven’t been able to locate a solution or actual instructions for what I am trying to accomplish. Also, I am extremely new to Dynamics GP so please forgive me if this obvious to others.

    We have a custom solution that integrates with a Dynamics installation. I need to create a purchase order receipt from existing purchase orders, which we can do without a problem. However, we also need to tell dynamics that the item was actually received. This is where the issues arise. We want to do this from our back end custom integration. I tried to update the PO with eConnect from our custom application but I get an error that says I cannot change POLNESTA to received (4) because the item has not been received. Is it possible to receive this item behind the scenes, using our custom app with eConnect integration???

    Like

    • Hi Mike,

      I believe this is possible with eConnect, but I do not think that the goal should be to update the PO with eConnect. You want to import a PO Receipt transaction which will, in turn, update the PO statuses. That’s the idea of using a tool like eConnect – the related updates will be done automatically for you.

      -Victoria

      Like

      • Hello Victoria,

        Thank you for the reply.

        I figured that’s what should happen when using eConnect. The problem is, it doesn’t. The receipt is created, the batch is posted, but the item status never changes to received.

        Could you enlighten me as to what fields I need to set on PO receipt so Dynamics knows the item has been received??

        Thanks again for the first reply. And also thanks for the sharing and putting together this site. It has helped me tremendously in the short time I have been working with Dynamics.

        Mike

        Like

        • Hi Mike,

          The reason I do not recommend changing the statuses on the PO’s “manually” is that it’s a little more complicated than just changing one value. First, there could be partial shipments, so quantities in numerous fields need to be considered, second there are multiple statuses, some on the PO header, some on each line item. Then there are the implications for inventory quantity updates, if you are receiving inventory items. There are also dollar amounts that get updated on PO’s when they are received.

          Because of all this, I would only consider what you’re asking about as a last resort, after exhausting trying to figure out why the receipt created by eConnect is not updating the PO properly. Perhaps it’s something as simple as the PO number not being specified. Perhaps there is a switch not set or a service pack needed. I do not work with eConnect myself, so I am not a great resource for that, but I would recommend either posting this on one of the GP forums, talking to your GP Partner (or whoever helped you set up the eConnect integrations), or asking GP Support about this.

          -Victoria

          Like

          • Hello Victoria,

            Thanks for your insight.

            I have accomplished what I was attempting entirely with eConnect. I needed to pass to eConnect the PO Receipt information as well as the Serialized Item information. Once I created a PO Receipt XML document that contained my serialized item information as well as the PO Receipt info, everything fell into place and posting and updates to inventory happened as expected.

            Thanks again for your insight and for the resources. Much appreciated.

            ~Mike

            Like

  56. Hi Victoria

    Is there a table that holds the next po number sequence. We are having an issue with new PO numbers being lower than the last one we entered. I think we got them out of sequnce because we dont have PO history turned on (Exactly how do you do that BTW) and we deleted a bunch of POS.

    I would like to just jump the number sequence by 2000 and be done.

    thanks

    Like

    • Vic you can do this in GP
      Configuration / Purchases / purchase order processal
      in that windows the sequence of the purchase order is stored

      sorry my GP is in spanish i dont know if i translated it ok

      Like

      • Thanks for that but when we reset the numbers there they still revert back to a lower number. I need to know what table is holding the next number in so i can update that

        Like

        • Vic,

          Updating the table does exactly the same thing as updating the next PO number inside GP. In other words, it will not accomplish anything different in this case. Don’t be so hasty to go to the tables. :-)

          There is a free tool that is part of the Professional Services Tools Library called “Minimum PO/Receipt Number” – it will allow you to set a minimum PO number.

          -Victoria

          Like

  57. Dear Victoria Yudin

    I’m newbie in GP, now I use GP 8.0
    i need a help to join po number and its comment, I have found sy03900 have noteindx that contain po comment in txtfield.

    Please give direction, how to make it.

    Many Thanks
    Abdulloh

    Like

    • Abdulloh,

      A ‘comment’ and a ‘note’ are 2 different things in GP terminology, so it is important to determine which one you are looking for first. On the Purchase Order Entry window, the ‘comment’ will be at the bottom, next to the Comment ID field – this is stored in the POP10150 table. There are, separately, a number of ‘notes’ that you can add to the PO: there is one next to PO Number, one next to buyer ID, one next to the Comment ID, etc. It is very important to understand that if you are using the note icon next to the Comment ID field to store a note, this note is for the Comment ID, and not specific to the PO. So any PO with the same Comment ID will show this note.

      If that is what you’re looking for, you can link in this note with the following: POP10100.PONOTIDS_4 = SY03900.NOTEINDX.

      Hope that helps,
      -Victoria

      Like

  58. I hope you could help me with this urgent case , we have GP 10 and Requisition Management every thing are working great but the issue is how to create a Po reports with the requisition number or how to make links between the Requisition Management and the PO tables or Po screen to show the requisition number for a specific PO

    Like

    • Sorry Mahmoud, I do not work with Requisition Management, so I cannot help with this. I would recommend posting this question on one of the GP forums or asking GP Support to get help with this.

      -Victoria

      Like

  59. Hi Victoria
    your site is the greatest!, thanks for all of your posts You helped us a lot!.

    I hope you could help me with this urgent case , we have GP 10 and Requisition Management every thing are working great but the issue is how to create a Po reports with the requisition number or how to make links between the Requisition Management and the PO tables or Po screen to show the requisition number for a specific PO.

    Like

  60. Hi Victoria,

    I have a question for the group.

    I would like to automatically generate sku’s (Inventory Item ID’s) in GP, particulaly for the PO process. Do you know of a method or a 3rd party solution that handles this need?

    Like

    • Hi Jeff,

      Just to make sure I understand…where would the information for the new items come from? How would the system know to create them? Are you currently importing sales orders or purchase orders?

      -Victoria

      Like

  61. Ok, so I have my mass import of 13000 items done and never got Integration Manager to import mulitple records for each item on the price list but managed to get that working through access import. Anyway, I am trying to now get integration manager to work using the eConnect functions to create 2 large container orders to cover the incoming products from our sister sites. One will have 1000+ items that are being shipped from the shut down facility. The other a stock order of 500+ items from the factory to make up the difference on what we feel is needed. I have set up the IM using one source and it created 500+ PO entries with no line item data. I have since deleted those and cleaned up the system with checklinks. I am now back working on the dev environment trying to figure this out. I have used the PO sample. I have edited the POP.txt sample with my data. I used all the mappings that it specified. The result is I have the expected 2 purchase orders from the data and no line items on either. Any clue? Since it has created the header, can I just use access to append the item info? Will SQL figure out the rest?

    Like

    • Allen,

      I would not recommend using Access or SQL to get transactional data into GP. You will be bypassing all the GP business rules that IM (or eConnect) abide by and it’s really easy to have data issues resulting from entering transactions directly into tables. I can tell you with 100% certainty that SQL will not ‘figure out the rest’.

      Usually with what you are describing the issue is the query relationship, but it’s really quite difficult to troubleshoot something like this without seeing it. I would start out with one PO and one line item to see if it works. Why dump all your data in there before you know if it’s going to work?

      -Victoria

      Like

      • I did testing using SQL inserts and found as expected that the totalling and all the various GP specific entries of course don’t just appear and would not auto correct after opening the po and tabbing through the inserted line items. I ended up finding the problem though. The Samples are setup incorrectly. Thanks Microsoft for creating a problem for me… Not like my users don’t create enough already…. Further, they have left off a useable discussion of the record set options from the documentation that states when processing line items on an integration, not only do you need to setup source listings as separate “source files” (oh wait, they didn’t say that, you did) even though the POP.txt clearly is one file to mask 2 sources, but also to go into the “Lines” Options and change the Record Source from “Use Default” to “Use Source Recordset” and then Specify the Source “POP Item”. Now, logically, It would make sense that the “Default” would be the specified Source Recordset. Why in the world would you specify sources which present themselves as “POP Item.Item” and then not use “POP Item” as the record set containing “Item”. Sounds like a bug in the code to me. Is it trying to use POP Header instead of what I have specified? Does it just think that doesn’t matter and we added it for giggles? Wouldn’t that have thrown an error and not integrated? Anyway, when I manually set the rule, the integration worked and created the necessary line items. Now that I have found the software bug in dev, I will be doing the live load. I figure that this was also my problem with the price lists not loading correctly when I did the integration of the 13000+ items but the manual SQL inserts worked… well, I found that for some reason, the Access 2007 append database function at least on my install would not work so I brought out my old XP machine with Access 2003 on it and the Append worked as it always had…. Just had more fun with this on Sunday than I really wanted….. Anyway, since you are working on a book, maybe touch on that topic with the Samples not being setup correctly or just explain that part a bit more than the MS documentation provides. Thanks as always….

        Like

  62. Is there a table that has both Account Index and PO Number?

    Like

  63. Is there a maximum number of line items that can be placed in a Purchase Order? We might be doing an intercompany purchase to move the inventory from one company (Oracle ERP) to our facility (Dynamics GP). I have the Integration Manager package to create the PO from a spreadsheet. I just need to determine what will be the best way and maximum possible lines that it can utilize.

    Like

    • Allen,

      There is no limit on how many lines you can have on a PO, the only limits would be what SQL Server will support, which is quite large. Take a look at this blog post on that very topic. That said, you might want to not put more than a few hundred lines on a single PO simply for usability purposes if you anticipate receiving this partially over a period of time.

      -Victoria

      Like

  64. Hello Victoria,

    I was hoping you could give us an advise on how to fix a PO. The PO was created through eRequester in the US $. There were several changes made to the PO, but they were all in US $.
    Now, we have cost showing in Japanese yens, the Extended Amount in US $ on each line, and the total of the PO is in US $$, but is negative and incorrect amount.
    I didn’t find anything in Utilities that could fix the PO.

    Thanks in advance,
    Yana

    Like

    • Hi Yana,

      I have not run into something like this before, it certainly sounds like something has gone seriously wrong with this PO. Have you tried running Reconcile and/or Check Links? If not, I would try those, not sure if they will help, but worth a shot. Another option, if possible, would be to delete this PO and re-enter it. If none of that helps, you may need to talk to GP Support on this one.

      -Victoria

      Like

  65. Dominic G. Miranda Reply July 16, 2010 at 12:24 am

    good day. i was wondering if what other tables aside from POP10100, POP10110 and POP10150. should i modify in order to do a proper insert of a purchase order document? thank you.

    Like

    • Dominic,

      Depending on what other modules and functionality you are using in GP, there may be other tables than need to be updated, it’s really tough to say without more detail. For example, are you using PO approvals? If you’re importing PO’s with a released status, do you need to update inventory tables?

      I typically do not recommend inserting transactional data directly into tables unless you have absolutely have no other choice. In that case, you really need to do a lot of testing to make sure you’re getting the correct results.

      -Victoria

      Like

      • Hi Victoria! Thanks for addressing my query. I am actually trying to input an approved PO so i should probably learn how to insert a corresponding approval for it in the POA40003 table.However, firstly I have to be able to able to insert a proper po with corresponding po lines in the tables POP10100 and POP10110 respectively. I have started trying to add details to a ‘safe’ po i generated using the Great Plains UI and i am able to view the details I manually added through the GP UI. however, when i try to creation a Recieving Trx Entry under the Vendor ID that I assigned to the po line, the specific PO Number does not show in the list.

        What I actually need to do here is create a proper approved PO through a transactional data entry that can be seen by other users through the GP UI.

        P.S. What does the fields POLNEARY_1 to 8 represent? These are the values that I defaulted to 0.0000 when i tried a manual line item insert and which probably broke my data.

        Thanks!

        Like

        • Hi Dominic,

          POLNEARY stands for PO Line Note ID Array – there are actually 9 of these in GP 2010, which is what I am looking at right now. The details on these are in the GP SDK. I would recommend studying the POP section of the SDK before proceeding with your project to make sure that you understand all the fields in the tables.

          Is there a reason you are not using any of the GP import tools for this project? If so, you may want to consider getting some help from GP Support or the Microsoft Professional Services team.

          -Victoria

          Like

          • Victoria,

            Hi. I am currently attempting to integrate into a GP 7.5 and support for the version has already stopped. So I’m rather short on options. The PO Note ID array and PO Line Note Id Array are the fields that I have trouble tracking the valid values. I was wondering if you have a mapping on which tables each of these indexes can be validated like the relationship of the POP10110 INVINDX with GL00100’s ACTINDX.

            Thanks again.
            – Dominic

            Like

            • Dominic,

              I do not have a mapping of those, sorry. I would recommend talking to Microsoft’s Professional Services team. For an unsupported version they may offer help for this as a consulting service.

              -Victoria

              Like

            • Hi Dominic

              those fields are index of po, buyer, vendor, comment, currency….

              i found this in GP10′ sdk you should check the SDK of 7.5

              PO Note ID Array Note Index Array for all purchase order note indexes:
              [1] PO Note Index – POP_PONOTEID_PO
              [2] Buyer Note Index – POP_PONOTEID_BUYER
              [3] Vendor ID Index – POP_PONOTEID_VENDOR
              [4] Comment Note Index – POP_PONOTEID_COMMENT
              [5] Payment Term ID Note Index – POP_PONOTEID_PAYTERM
              [6] Shipping Method Note Index – POP_PONOTEID_SHIPMETHOD
              [7] Currency ID Index – POP_PONOTEID_CURRENCY
              [8] Tax Schedule Index – POP_PONOTEID_TAXSCHED
              [9] Freight Tax Schedule Index – POP_PONOTEID_FREIGHTTAXSCHED
              [10] Misc Tax Schedule Index – POP_PONOTEID_MISCTAXSCHED
              [11] Contract Number Index – POP_PONOTEID_CONTRACTNUMBER
              [12] not used at this time
              [13] not used at this time
              [14] not used at this time
              [15] not used at this time

              Like

              • FAC and Dominic,

                Please note that the listing FAC shows is for the PO, not the PO Line. Probably both are needed, but they are 2 separate arrays. Also, once you have this listing, you still need to determine where to get the correct index for each one – I think that’s what Dominic was really asking about.

                -Victoria

                Like

  66. Hi Victoria,

    I’ve been trying to create an SQL table that links the POs(for parts) with the sales orders for the finished goods to monitor the parts’ dock dates. Is this possible? If not, I think there are a few places for notes in the Purchase Item Details Entry screen where the sales orders can be manually typed in. What’s the SQL table that keeps the notes? I’ve been struggling with this for quite some time so I would appreciate any kind of guidance.
    Thanks,
    Yusri

    Like

    • Yusri,

      I think that if entering the sales number on the PO line item detail is doable, that would be the easiest solution. For the tables – if you are using line item Comments, they are stored in POP10550. You can link to POP10550 from POP10110 on:

      POP10110.PONUMBER = POP10550.POPNUMBE
      and POP10110.ORD = POP10550.ORD

      -Victoria

      Like

      • Hello Victoria,

        Thanks for your prompt response. The Line Item comment has been taken by somebody else. I’m thinking of using the Note next to the Comment ID field. What would be the table for that?

        Thanks,
        Yusri

        Like

        • Yusri,

          The Note next to the Comment ID is not specific to the line item. It is specific to the Comment ID you fill in. So you would have to create a new Comment ID for each PO line item. Not the end of the world, just making sure this is what you intended. If that is what you want, then the text of the note will be in SY03900. Linked to the Comment Master table (SY04200) by the NOTEINDX field.

          -Victoria

          Like

  67. Hello again Victoria

    I didnt know where to post my question exactly. I’m having some troubling importing Purchase order documents into GP. We’ve done a re-implementation and setup a new company DB which means I have “bring over” historical and outstanding data.

    I’m trying to find out what tools, tricks people are using to import PO documents specifically. There is no IM adapter except through e-connect which we done use. I tried table import but I must be doing something wrong because I can see the data in the applicable tables (POP10100 & POP10110) but I cannot view the document from Great Plains itself.

    I’ve built macros in the past but this is a tedious & time-consuming job which I dont have on my side right now.

    Any suggestions please?

    Regards,
    Lulu

    Like

    • Hi Lulu,

      I would not recommend importing POP transactions (or any transactions, for that matter) using table import unless you know the tables extremely well, and even then, you’re talking about a lot of work with the setup and testing – as you are finding out. Unfortunately, there is no easy way to do this for a large number of transactions without an import. If this is a one time need and you do not have thousands of transactions to enter, consider hiring someone (or paying someone for some overtime) to manually type them in. That may actually be less expensive than getting a consultant to help you with the import, which would be my next suggestion.

      -Victoria

      Like

  68. Hi victoria,

    I have a PO in new status which is approved. The report of PO is showing as cancelled. The reason being POP10100 table is updated wrongly with postatus 0. Could you please tell me what would be the reason for this.

    Thanks,
    nisha.

    Like

    • Hi Nisha,

      If I am understanding correctly, you have a PO with a POSTATUS of 0 in POP10100. Since 0 is not one of the valid values for POSTATUS, I would suspect that something went wrong with the creation of this PO. If possible, I would delete it and start over.

      -Victoria

      Like

  69. We want to put our company Logo in the POP Blank Order Form. Is it possible to insert logo in the report? and if yes, then Will that affect the speed of PO printing to screen Or to printer?
    Also we need different PO design for Internal Suppliers & Overseas Suppliers. Can these two modified PO’s accessible normally from GP.

    Best Regards,

    Wasay

    Like

    • Wasay,

      As a general rule, once you start having complex requirements for Purchase Orders I recommend looking at alternatives to Report Writer, such as Crystal Reports or SSRS, for printing your Purchase Orders. In addition, if you have a nice 3-D or color logo, it will typically not look very good when printed from Report Writer, as there is a 32kb limitation on the file size.

      That said, you can have 2 PO report layouts co-exist, the users will have to know which one to pick when printing PO’s. And here is a link to a KB article on how to add a logo in Report Writer: Adding Logos to Dynamics Reports in Report Writer. It references old GP versions, but it is still valid.

      -Victoria

      Like

  70. We are having an audit done and there are some questions concerning a report on PO’s that is based on SmartList. Is there something that will tell me what tables are used to comprise the purchasing sections of smartlist?

    Like

    • Allen,

      You can find this information in the IG.pdf manual in chapter 32. (If it was a short list, I would put it in here, but it’s quite lengthy.) If you have GP 9.0 CDs, IG.pdf is on the 2nd CD in the Tools\Dex\Doc\PDF folder. If not, your GP partner should be able to provide this for you. I do not see anywhere online where you could download it.

      -Victoria

      Like

  71. I have a report that is supposed to show me unposted GRVs and for some reason it sometimes shows me transactions that have been posted already. My question is when you have posted a receipt in the Receivings Transaction Entry window, will the transaction clear from POP10500 or does it change the status only and the line quantities remain in the table? Thanks in advance

    Like

  72. Hi Victoria,

    I don’t know if it s related to this page

    However, I would like to ask for your assistance,

    I am creating an integration between our legacy requisition system to GP purchase Order via IM eConnect and I am having problem running the said IM application.

    “DOC ERROR: System.Data.SqlError: Invalid object name ‘PA10601′

    We do not have the Project Accounting module and we are not trying to pass an PA data on PO line

    We’re using GP 10 and IM eConnect 10.00.1378

    Thanks

    Allan

    Like

    • Allan,

      I have not run into this issue before, however doing an internet search for Invalid object name ‘PA10601′ brought up a number of results where others have seen this. Some suggestions were to make sure you have the latest service pack and also make sure that if you are using eConnect you are not using any schemas that start with . If this does not solve your issue, you may need to contact Dynamics GP Support about this.

      -Victoria

      Like

  73. We have certain items in the inventory module having Lot Tracking. Now we are thinking of removing the Lot Tracking from these items. Can you tell us, what are the after effects of such an action.

    Like

    • Waasay,

      The only way you can do this in the GP interface is if these items are not in stock and are not on any open transactions. If that is the case, I believe you should be fine changing the tracking options without an issue. You may not be able to see prior lot detail for these items, but if you’re turning off the tracking options, I imagine that is fine.

      All that said, it is always best to test proposed changes like this and I would strongly recommend that prior to making the change you perform some tests.

      -Victoria

      Like

  74. Hi,

    I am using gp10 with sp2, normally we enter PO and then we do the Receiving transaction with the Type ‘shipment/invoice’ and then we post to affect the Stock & create Journal in GL.

    We would like to use Enter/Match Invoice option. Can you guide us as how we enter this transaction and what is the standard steps of doing this transaction.

    Best regards,

    Wasay

    Like

    • Hi Wasay,

      It sounds like you are looking for some training on how to use this GP functionality. I am not sure I can do that justice in a blog comment. I would recommend a few options available to you:

      1. If you are current on your GP maintenance plan, you have free online training available on CustomerSource.
      2. The detailed steps for entering every type of transaction in GP are in the user guides (Help > Printable Manuals > Purchasing > Purchase Order Processing).
      3. Talk to your GP Partner about providing this training for you.

      -Victoria

      Like

  75. Hi Victoria,

    When the GP Business Portal is installed. Is there a standard table that would house purchase requests (requisitions) that are entered in the business portal?

    Thanks,

    Jeff Hanson

    Like

  76. Hi Victoria

    Thanks a lot for the reply, I will try myself, and will update you once I succeed.

    Like

  77. Dear Victoria

    I am looking for a report in POP module. which gives me the list of shipments, invoice number (enter/Match invoice) against those shipments, and if returned (PO Return) and the GL post dates of each entry. Can you help us

    Like

    • Hello,

      This is not the easiest of reports to put together, unfortunately I do not have any samples of anything like this to share. You will need to pull together information from a number of tables listed on this page. I would probably start with tables POP30300, POP30310 and POP10500. Sorry not to be of more help.

      -Victoria

      Like

  78. Hi Victoria,

    Just would like to know the tables i can use to view the Items received then sold on a particular sales invoice. I tried tracing this on profiler but I can’t see any link. Hope you can help me on this.

    Thanks in advance.

    -Tin

    Like

  79. Hi Victoria

    I hope u can help me with this please.
    I got a purchase order, then i have a receipt of the PO, then an invoice.
    Then i make a return with credit, the stocks of inventory are returned ok, but the purchase order is available to make an invoice again, the status is Received, i thought that after returning the PO the status was set to Closed but it is still in Received, is this correct or am I doing something wrong

    I tried to edit the PO and change the status but GP doesnt allow me to change the status to Closed, so i have to update the polnesta field of POP10110 to 5

    thanks a lot Victoria

    you are very helpfull

    Like

    • Hi FAC,

      One of my first rules: do not change anything in the database unless you have exhausted all other options and are 100% sure it will not break anything.

      Entering a Return will not ‘re-open’ the Purchase Order, so something else is going on with that PO. Maybe it was not fully received? You can try running Reconcile Purchasing Documents to see if it will correct the PO.

      -Victoria

      Like

  80. Greetings!

    I’ve lurked here for a while, and your blog has helped me solve many, many issues I’ve had in GP. Thank you very much for doing it. I have a question about PO’s that I am hoping you can give me a hand with.

    We have an application that changes the location for a PO line item from INTERIM to the destination warehouse location. Trouble arose when we started looking at the total on order and received for those items, however. When changing the location for a PO line item, what else should be updated at the same time?

    Thanks in advance, it is much appreciated.

    Like

    • Anthony,

      Thanks for the kind words, I am glad to be of help!

      If you’re talking about the On Order and Received quantities for the items in inventory, I think that you should also be updating the inventory quantities table, IV00102. However, there may be additional tables that get updated. One way to find this out would be to use SQL Profiler and run a trace when making the same change manually. Another option is to see if running Inventory Reconcile will fix the quantities. If so, perhaps just running that routinely will be enough.

      -Victoria

      Like

      • Those are the same conclusions I ended up coming to yesterday, Victoria. Thanks very, very much for your help, I really appreciate it.

        All the best,
        ~A!

        Like

  81. Hi Victoria,

    I am trying to create a smartlist using smartlist builder that would pull the purchases account from the recievings line item detail window, along with item and cost data from the receivings line item itself.

    I am struggling with the tables and links needed to get at this account.

    Any help would be greatly appreciated.

    Thanks…Bill

    Like

    • Bill,

      I believe all the information you want is in the POP30310 table (assuming you’re looking for posted receipts). The purchases account index is stored in INVINDX. In SmartList you can tell it to display the account number instead of the index.

      -Victoria

      Like

  82. You are my hero!
    Thanks!

    Merry Christmas!

    Like

  83. Hi Victoria,

    I am trying to create a Purchase Receipts Report that goes back to the PO line to get the requested by details. I can not find a way to join POP10310 (receipt line) to POP10110 (po line). I can join the PONUMBER of course, however, the POLineNumber in POP10500 is not the same as the POLineNumber in POP10110. Without joining the PO line, I get repeating rows and a left join could pull the wrong PO line. Thanks for your help!

    Like

    • Jeff,

      Try joining POP10110 and POP10500 on
      POP10500.PONUMBER = POP10110.PONUMBER and
      POP10500.POLNENUM = POP10110.ORD
      (There may be multiple lines in POP10500 corresponding to one line in POP10110 if there were partial receipts of the item.)

      and then joining in POP10310 on
      POP10500.POPRCTNM = POP10310.POPRCTNM and
      POP10500.RCPTLNNM = POP10310.RCPTLNNM

      Is this what you’re doing already and getting multiples?

      -Victoria

      Like

  84. Hi Victoria,
    On the Receiving Transaction Entry form the unit cost of the product is 0. When I open up the same PO in purchase order entry form, unit cost is not formatted(no dollar sign) for this particular PO. But when I look up any other PO, the unit cost on the PO is formatted. Could you shed some light on it?

    Thanks,
    Aaron

    Like

    • Aaron,

      Sorry, this is not one I have seen before. Is this a non-inventory item? Are you using the Multicurrency module? If so, maybe this was entered incorrectly on the PO? I would recommend posting this question on one of the GP Newsgroups to see if someone else has seen this behavior before.

      -Victoria

      Like

  85. Hi Victoria,
    I get an error message when i post an order “Error:Item site record doesn’t exist” could please shed some light on this one?

    Thanks
    Aaron

    Like

    • Aaron,

      How were your inventory items entered into GP? I believe that ‘item site record’ refers to the fact that each item / site ID combination must have a line in the IV00102 table. If items were imported directly into tables, this may not have been done or there may be another reason for the item sire record not being there. You could try running check links on the Inventory series to see if that will fix the problem.

      -Victoria

      Like

  86. Hi Victoria,

    I am redesigning GP purchase order to print out of crystal reports. I need to get bin number printed on the PO. While I am trying to link IV00102 to POP10110, my line items are getting printed more than once. What’s the best way to get that table linked in to POP tables to have bin number on the PO. Thanks

    Like

    • Ajith,

      Typically if results are being duplicated its because of how you are joining your tables. Usually using left outer joins is the best way to avoid duplication, however that is just a generalization and without looking at your code it may be impossible to say.

      -Victoria

      Like

  87. Hi Ms Victoria,

    I need to add the PO Line Notes in Purchase Order Other Form Report. Can you tell me how to do this?

    Thanks….

    Like

    • Felix,

      KB Article 858264 goes over the steps to do this for the Purchase Order Blank Form. You should be able to use the same steps for the Other Form. My 2 cents: I don’t believe notes can be handled very well in Report Writer. You may want to consider using something like Crystal Reports for your PO’s.

      -Victoria

      Like

  88. Hi Victoria,

    We needed to do a PO return of inventory to a vendor. So, we created a Return Transaction Entry of Type Return. We filled out all the relevant fields: Return No, Date, Batch ID, Vendor ID, PO Number, Item, Receipt No and Quantity Returned. We clicked save. I tried to go back and view and it now says “The Return number now exists in history”. However, we never posted it. We are now unable to find this document through the GP interface. I was able to find it in SQL Table POP10500. The inventory inquiry for the item shows the quantity from the return as allocated, but when I click on the Allocated link there is no results.

    Is this what normally happens?

    Any suggestions?

    Thank you and Best Regards,

    Brent

    Like

    • Brent,

      In the POP10500 table what is the value of the Status column? If it is 1, then the transaction was posted. Maybe it was saved in a batch that was later posted. Maybe someone else opened the transaction and posted it…

      -Victoria

      Like

      • Hi Victoria,

        Thanks for the quick response.

        The Status column has a value of 0.

        Brent

        Like

        • Brent,

          Then you should be able to open the transaction. I would make sure that no one else is in it or in that batch and there are no stuck processes in GP.

          -Victoria

          Like

          • Victoria,

            I made sure that all users were out of the system. There were no records in Table SY00800. I made sure there were no orphan records in TempDB..DEX_SESSION and TempDB..DEX_LOCK. I ran Check Links for Purchasing History. The report was empty. Should I run for Purchasing Transactions, too? Would this transaction normally appear in more than just table POP10500? Could I delete this record from SQL and run an Inventory Reconcile on the part from this PO/Return?

            Thank you very much for your help.

            Brent

            Like

            • Brent,

              There are a great many tables this could be in. I would not advise deleting anything directly from the tables like this. I think at this point I would recommend either getting your GP partner or GP support involved, or maybe posting on the GP newsgroups/forums to see if you can get some additional advice from others.

              -Victoria

              Like

  89. Hello Victoria,

    I have a query I was hoping you could help with.

    But before I ask can I just say that as a complete novice to GP, your Blog is a godsend. Thank you very much for sharing your wisdom.

    Do you have any SQL that gives a listing of POs and the GL accounts they were coded to?

    Thank you for your assistance.
    Daniel

    Like

    • Hi Daniel,

      Thank you for your kind words!

      Are you looking to see what account each line item of a PO is set to use on the PO itself? If so, you can just look at the INVINDX column in the POP10110 and POP30110 tables. Link the INVINDX to the ACTINDX in GL00105 to get the account number.

      Or are you looking to go ‘backwards’ and look at the payables transaction that is tied to a particular Purchase Order and what GL accounts are on that Payables transaction? If so, you could start with this view I have posted up for GL distributions on AP transactions and then add the PO number to it.

      Let me know if you need further help with this.

      -Victoria

      Like

      • Hi Victoria,

        I also find your website very helpful in nagavating through the inner workings of GP. Thanks for creating it.

        One question I did have is whether it is possible to start with a journal entry in the GL20000 table and find the associated PONUMBER (for those entries that have one).

        I do not see any obvious links between the tables GL20000 and POP10100 or POP10110. Is possible to do what I want to do?

        Thanks,

        Mark

        Like

        • Hi Mark,

          Since there is no GP posting of Purchase Orders themselves, you will not be able to link GL entries directly to PO tables, like POP10100 or POP10110. I think you can accomplish what you’re looking for by linking GL20000 to the POP10500 table which will have the receipt number you can link to the GL as well as the PO Number.

          -Victoria

          Like

  90. Victoria,

    Thank you for the information related to GP on this website. It has been very useful for me throughout my learning process with GP.

    I am having difficulty however, linking data from the POP tables so that I can link Purchase Orders Line Items with Receipts.

    I just can’t seem to figure out what the “PK/FK” relationship is between the POP10110 and POP10310 as well as with the historical tables and the IV10200 table.

    Any help or just a pointer in the right direction would be greatly appreciated.

    Like

    • Hi hirschrm,

      Take a look at the POP10500 table. This has all the receipt line quantity details and will link back to the POP10110 table on:
      POP10500.PONUMBER = POP10110.PONUMBER and
      POP10500.POLNENUM = POP10110.ORD
      (There may be multiple lines in POP10500 corresponding to one line in POP10110 if there were partial receipts of the item.)

      To link to inventory tables, you could link to IV30300 instead using:
      POP10500.POPRCTNM = IV30300.DOCNUMBR and
      POP10500.RCPTLNNM = IV30300.LNSEQNMBR and
      IV30300.DOCTYPE = 4

      Please test all of these out to make sure they work with your data. This is just what I am seeing from a small amount of data I am testing with.

      -Victoria

      Like

      • Victoria,

        I wanted to let you know this link here helped me join the POP10110 to the pop30310 table…I really appreciate your blog…the info is very helpful for me as a partner serving my customers.

        Like

  91. Hi Victoria,

    One of my users had a service item on a PO. He was receiving one a month and invoicing via Enter/Match Invoices in Purchasing. There were four left to receive on the PO and this month he accidentally received all 4 remaining service items. He already invoiced 1 and can invoice the other 3. However, the extra 3 received are throwing off the GL because we are reporting 3 extra on our books that we really don’t have. Thus, what is the best way to fix this? Can we reverse the receipt of the extra 3 on the PO? If not, I’m assuming we have to do some sort of GL adjustment? How would we get the extra 3 service items out of inventory since they technically aren’t inventory?

    Thanks for your help.

    Brent

    Like

    • Brent,

      Probably the easiest option is to create a Purchase Return (Transactions > Purchasing > Return Transactions Entry). This will not re-open your PO though, so you may want to create a new PO for the remaining 3 items.

      -Victoria

      Like

      • Victoria,

        Thanks so much for the response. Your suggestion was great. One more question…the user posted the Return with the wrong date…it should have been last month…do I just update this in SQL or do I have to start entering in more transactions?

        Brent

        Like

        • Brent,

          If the transaction is already posted, I strongly recommend NOT updating dates directly in SQL. The reason for this is that there are a very large number of related tables that already used those dates. You would either have to investigate them all (a huge amount of work) or be left with data that is not quite correct. The exact answer may depend on the kind of reporting and reconciliations you typically perform at month end, but it may be acceptable to simply create a reversing GL entry to correct for this.

          -Victoria

          Like

          • Victoria,

            As always, thanks so much for your responses. It is greatly appreciated. I think it is very evident how helpful your website is from my responses and everyone elses. I just got a chance to see your suggestion and we did a reversing GL entry just like you suggested.

            Thanks again.

            Brent

            Like

  92. Hi Victoria
    your site is the greatest!, thanks for all of your posts You helped us a lot!.

    I hope you could help me with this

    Is there a way that I could Update the POA40003 table to mark a Purchase Order as approved?

    I mean could you do that or it’s wrong to update a table in GP?

    Thanks a lot!

    Like

    • Hi Fernando,

      Thank you very much for your kind words!

      In general I try to avoid doing anything directly in the database tables unless there is no other way. However, I have changed the PO status in the database before and have not had issues. Here is some sample code I have used for this:
      update POA40003
      set POA_PO_Approval_Status = 2,
      POA_Approved_By = 'sa'
      where PONUMBER = 'PO12345'
      and POA_PO_Approval_Status = 1

      -Victoria

      Like

      • Hi Victoria

        this worked great in 2009 thanks a lot, but we updated to SP4 in 2010 and we started having problems with this update.

        The problem is that we approve the purchase order by another application that make the update just like your code above, but now when post the Receipt of the purchase order there is a message that says something like this “amount is greater than the approval amount of this user the purchase order will be saved as not approved”
        so after posting the Receipt the purchase order is not approved so it does not allow to do the purchase invoice, so we have to approved that purchase order again, and after posting the purchase invoice the purchase order is saved as not approved again

        Do you think that happens because the update, or it has to be with the REMSUBTO field?

        Thans a lot Victoria

        Like

        • FAC,

          You are doing something custom, so it’s really impossible to tell what is causing the issue without seeing your environment. Since this is important functionality, I would recommend talking to Dynamics GP Support to determine (a) what changes in SP 4 may have caused what you are experiencing and (b) whether what you are doing with your updates is correct/complete and what implications it may have on other functionality in GP.

          -Victoria

          Like

  93. I’m new to GP and I’m still trying to find my way thru the database. This site is amazing – thank you for putting this together.

    I have a question about the PO Note ID array, specifically
    1 – PO Note Index – POP_PONOTEID_PO (PONOTIDS_1 correct?) – where is this linked to so I can get the text for those notes?

    Thanks in advance

    P@

    Like

    • P@,

      Thank you for the kind words. All notes for a company are stored in the SY03900 table. They are linked by the NOTEINDX.

      -Victoria

      Like

      • Love your site! I’ve used it a lot. I suggest making a change to your list of note ID array lists. Here’s an example:

        Current header:
        PO Line Note ID Array Array in POP10110:

        Suggested header:
        PO Line Note ID Array in POP10110 (maps to SY03900.NOTEINDX):

        Current list item:
        1 – Item Number Note Index – POP_POLINENOTEID_ITEM

        Suggested list item:
        POLNEARY_1 – Item Number Note Index

        Reason: It took me forever to find the foreign keys that map to SY03900.NOTEINDX since I kept looking for a NOTEINDX field for my PO lines. Three of us googled for a couple hours, but nobody actually listed POLNEARY_5 and NOTEINDX together.

        Question: To what does POP_POLINENOTEID_ITEM refer? It’s not a column, right? Does anyone use it?

        Thanks again for your great site!

        -drume

        Like

  94. Hi Victoria,

    Sorry to bother you again but could you help me with the concern below?

    For PO Receipts that have already been posted into POP30300 table, is there a place within Great Plains to view them from a GP user standpoint?

    Thanks for all your help!

    Best,
    Nikki

    Like

  95. Victoria,

    Your site is very helpful.

    Are there any sites like yours that focus on Dynamics GP payroll issues?

    Chris

    Like

  96. Thanks Victoria,

    Got it sussed after a little bit of playing around with the USers, Classes, Roles and tasks etc in GP10.

    Thank you again for your response.

    Nev

    Like

  97. Nev,

    The security settings differ pretty significantly depending on what GP version you’re using and what method of security. In GP 10, you need to make sure that the user has the role allowed to post all Purchasing Series transactions. In GP 9.0 and earlier, if you are using Advanced Security, there is a separate section for Posting Permissions where you can choose what types of transactions the user can post.

    -Victoria

    Like

  98. Hi,

    I am looking for some guidance with regards to users and user security.

    I need to set up a user “Accounts Payable Processing” who should have all the accounts payable security with the exception of being able to post anything…..how is this acheived?

    Thanks in advance
    Nev Browitt

    Like

  99. Yep got it after a bit of digging….thanks for your speedy response though :-)

    Like

  100. Where is the distribution refernece stored in the payable transaction entry distribution screen pls?

    PS….really useful site.

    Like

    • Nev,

      The GL distribution reference for payables transactions will be in the DistRef column in either the PM10100 table (work and open transactions) or PM30600 table (historical transactions).

      -Victoria

      Like

  101. Wow! Awesome.. great work. Thanks Victoria.
    This is very helpful.

    Like

  102. This is great Victoria!! I use it nearly every day. Thanks.

    Like

  103. Awesome Stuff….

    Thankx

    Like

Trackbacks/Pingbacks

  1. PO In Open and History | keyongtech - March 18, 2009

    [...] PO In Open and History Thanks. Victoria Yudin has a list on her site that helped. http://victoriayudin.com/gp-reports/pop-tables that gave a name for most of the tables listed. I removed these in the test then did checklinks and [...]

    Like

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,493 other followers

%d bloggers like this: