Commonly Used Tables:
POP00101 – Buyer Master
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
POP10390 – Receipt Work Distributions
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 POP30100 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: Feb 12, 2021
Victoria Yudin 
Victoria,
Do you know how to post Receiving Transaction Entry using eConnect/Store Procedure/Query ?
Thanks.
-Jonathan
LikeLike
Hi Jonathan,
eConnect does not have the ability for posting anything. I would not advise doing this with stored procedures or queries, as that is not a safe way to do it. If you need to post things automatically in GP, I would look into Post Master by Envisage, here is a link if you want to check it out: https://www.envisagesoftware.com/post-master.
-Victoria
LikeLike
Hi all,
I’m having a problem where A/P can’t enter/match an invoice from our inventory vendor. What’s odd is there is an invoice-receipt that shows in POP10310 but NOT in POP10300. It also shows in POP10390, POP10500, & POP 10600.
Very confused by this. I appreciate any suggestions.
LikeLike
Hi Scott,
Sounds like there might have been a problem posting/saving the invoice/receipt and now you’re stuck with orphaned or partially updated records. Sometimes running check links on the Purchasing module and/or reconcile on the POP module will fix this. I would try that first. If that does not work, it will require updating / cleaning up records directly in SQL. For this, I would recommend either working with MS support or your GP partner if they are knowledgeable about this to make sure it’s done correctly.
-Victoria
LikeLike
Victoria,
We are having issues with our SmartList reports not populating certain columns(or those columns not being present to add). Do you know what table the Inventory Account Number column comes out of? I havent been able to find the table and column in my searches.
Thank you
LikeLike
Chris, which inventory account number are you looking for? From which GP window?
-Victoria
LikeLike
Victoria,
Its the one in SmartList->Purchasing->Purchase Orders->Annual List of POs. Its the Column Inventory Account Number listed in there. We are also trying to find the SiteID(internal department), but thats another issue entirely as there doesnt seem to be a column matching that at all in the report or SmartList Builder.
Thank you
LikeLike
Chris,
The account number can be different for each line item on a PO, so it cannot be on a SmartList that is listing Purchase Orders. It would have to be on Purchase Line Items. Hope that helps.
-Victoria
LikeLike
Hi All – trying to see if there is a link in the GP tables that relates a PO to a vendor to a sales order from a customer? As in, a PO that is tied to a specific SOP table. Thanks in advance…
LikeLike
Good morning Victoria, Let me apologize if this has been asked and answered but we have a couple of PO’s that had remaining balances cancelled resulting in the PO’s going to closed/received status prior to matching up the last vendor receipts. Now when payables is attempting to recall these PO’s they’re getting the closed message and are unable to bring either PO up. Is there a way to change this PO status back to match the last remaining invoices?
LikeLike
Hi Scott,
If the PO’s are already “closed” then GP thinks they are matched, so there is no way to match anything else to them. For any received PO’s you should still be able to match invoices to them. If you need to enter the vendor invoices and the PO/receipt is not available anymore to match to, then I would recommend doing it directly in payables and just making sure the correct GL accounts are impacted.
If it’s just a few PO’s, this happens periodically for various reasons and it’s probably not worth spending a lot of time on. If this is happening routinely, you may want to address this with your GP partner to see if they can help you figure out why that is.
-Victoria
LikeLike
Victoria, love the site. I’m a controller and I use this a lot along with our IT team.
I have a question but I think I know the answer. Either something or someone keeps closing lines after a receipt is made but before an invoice is enter/matched. I have been using the DEXROW timestamp to see roughly when this happened in the line detail. However, I know that other row updates could update this value. We use Rockton for audit control but its almost useless IMO. I’ve had Trimble and Microsoft look at this and run table updates to correct large amounts of these. Any ways you know of to see what is causing it? We have learned a return and re-receipt can make these available to E/M in case anyone needs the info.
LikeLike
Hi Marshall,
Closing lines? You mean the line shows as received and invoiced even though it’s not? I have not seen this before with any client I’ve worked with and I can’t imagine a user manually doing this – it’s just too much trouble. So I have to think there is some other ‘automated’ process causing it. I would consider turning off any modifications/customizations/imports that impact POP short term to see if the issue is still occurring. If not, add them back one at a time. I know this is easier said than done in a busy environment, but until you know what’s causing it, it may be impossible to fix. The alternative would be using a dev environment. If you can duplicate it happening there, it’s much easier to turn things on and off there and do the testing I am suggesting.
-Victoria
LikeLike
POP00101 is missing from the list. This is the Buyer Master, or where the Buyer Maintenance window stores the list of Buyers. I use this so rarely that I cannot remember if Buyers are in the
POP tables or Inventory tables.
LikeLike
Hi Steve,
I have added it to the list – thank you for the suggestion.
-Victoria
LikeLike
Can you tell me where the PRMDATE_DATE comes from in table POP10100?
LikeLike
Hi Diane,
Did you mean the PRMDATE? If so, that is the Promised Date at the header level of the Purchase Order (click the blue arrow next to Date at the top of the PO to see it).
If you actually have a field called PRMDATE_DATE, I do not believe that is standard in GP, so it might be coming from a customization.
-Victoria
LikeLike
Thank you so much Victoria. I found the Prospr site and it was so very helpful. I think the _DATE is from the data analysis tool I’m using. I’m an Internal Auditor and don’t actually work IN the GP modules, I just pull data from the tables to run certain analysis on. We also use ReQlogic so trying to tie GP table data to the information in ReQlogic is proving to be interesting.
LikeLike
Hi Victoria, in the view: slbItemQuantities, what is CREATDDT? I am looking for the date items were created? I’m not entirely sure CREATDDT is the correct field. I am currently pulling DocDate and CREATDDT for the Current Inventory where I am filtering the ‘Created Date’ from Current Inventory. In the backend, they have a field called LSTORDDT and CREATDDT.
Thanks for your help!
LikeLike
Hi Tony, in this view (at least in my version of it from GP 2016), that CREATDDT is the date the vendor was created. The LSTORDDT is the latest order date for the item, either for the site or overall, depending on the Site ID (LOCNCODE).
Hope that helps,
-Victoria
LikeLike
HI Victoria, thanks for your response. From slbItemQuantities, I would like to extract the created date that corresponds with the item master – when the part was created. Do you know the proper field for this?
LikeLike
Hi Victoria,
I am trying to pull item purchased by vendor by year from POP30300 & 30310 using POPRCTNM to join the two tables. However, the result looks very weird, looks like it is multiply by itself. I feel like I shouldn’t use POPRCTNM to join the 2 tables but I can’t find another common column to join them
Below is the query I have. Should I pull the data from another table instead? Say POP10500 instead? Please advise.
SELECT P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC,
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN Q.QTYSHPPD ELSE 0 END) [2019 QTY],
–SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST/P.UNITCOST) ELSE 0 END) [2019 QTY],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) [2019 AMOUNT],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2018 THEN CAST (Q.QTYSHPPD AS INT) ELSE 0 END) [2018 QTY],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2018 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) [2018 AMOUNT],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2017 THEN CAST (Q.QTYSHPPD AS INT) ELSE 0 END) [2017 QTY],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2017 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) [2017 AMOUNT]
FROM
(SELECT PH.VENDORID, PH.VENDNAME, PH.RECEIPTDATE, PH.VCHRNMBR, PL.ITEMNMBR, PL.ITEMDESC, PL.EXTDCOST, PL.UNITCOST
FROM POP30300 PH
INNER JOIN POP30310 PL
ON PH.POPRCTNM = PL.POPRCTNM
WHERE PH.POPTYPE IN (2,3)
) P
LEFT OUTER JOIN POP10500 Q
ON P.VENDORID = Q.VENDORID
LEFT OUTER JOIN PM00200 V
ON V.VENDORID = P.VENDORID
AND V.VNDCLSID = ‘INVENTORY’
GROUP BY P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC
ORDER BY P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC
LikeLike
Hi Joyce,
I think the issue is your link to the POP10500 table – that is only on vendor, so it’s not selecting unique results properly (and as you’re seeing, multiplying the results). Try the code below, I believe that should fix it. I added a date restriction inside your subquery to improve performance – there is no reason to get all the raw data when all you want are transactions after a certain date.
Also, I commented out your link to the PM00200 table, as it’s not changing your results in any way. If you want to restrict this to only vendors with a class ID of inventory, I would add an additional restriction inside your subquery – something like:
and PH.VENDORID in (select VENDORID from PM00200 where VNDCLSID = ‘INVENTORY’)
SELECT P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC,
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN Q.QTYSHPPD ELSE 0 END) [2019 QTY],
–SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST/P.UNITCOST) ELSE 0 END) [2019 QTY],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) [2019 AMOUNT],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2018 THEN CAST (Q.QTYSHPPD AS INT) ELSE 0 END) [2018 QTY],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2018 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) [2018 AMOUNT],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2017 THEN CAST (Q.QTYSHPPD AS INT) ELSE 0 END) [2017 QTY],
SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2017 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) [2017 AMOUNT]
from
(select
PH.VENDORID, PH.VENDNAME, PH.RECEIPTDATE, PH.VCHRNMBR, PL.ITEMNMBR, PL.ITEMDESC,
PL.EXTDCOST, PL.UNITCOST, ph.POPRCTNM, ph.POPTYPE
from POP30300 PH
inner join POP30310 PL
on PH.POPRCTNM = PL.POPRCTNM
where PH.POPTYPE IN (2,3) and PH.receiptdate > ‘2016-12-31’
) P
left outer join POP10500 Q
on P.VENDORID = Q.VENDORID and P.POPRCTNM = Q.POPRCTNM and P.POPTYPE = Q.POPTYPE
–LEFT OUTER JOIN PM00200 V
–ON V.VENDORID = P.VENDORID and V.VNDCLSID = ‘INVENTORY’
GROUP BY P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC
ORDER BY P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC
LikeLike
Hi Victoria,
Thanks for the revised query and yes the duplicate records are gone. However, the QTY is all zero now even though there is an amount for the item. The QTY is pulled from POP10500 and we did include this table in, but I couldn’t figure out why the QTY does not show.
LikeLike
Hi Joyce,
Ok, I see the issue. There is still a problem linking to POP10500 because we need to link by individual items and we’re not currently.
If we can assume that Extended Cost divided by Unit Cost in POP30310 will get us the correct Qty, which I think is a reasonable assumption, then we don’t need POP10500 at all. See if the query below works for you. I tested it on our data and I think it is working correctly.
select ph.VENDORID, ph.VENDNAME, pl.ITEMNMBR, pl.ITEMDESC,
sum(case when year(ph.receiptdate) = 2019 then convert(int,(pl.EXTDCOST/pl.UNITCOST)) else 0 end) [2019 QTY],
sum(case when year(ph.receiptdate) = 2019 then convert(numeric(19,2),pl.EXTDCOST) else 0 end) [2019 AMOUNT],
sum(case when year(ph.receiptdate) = 2018 then convert(int,(pl.EXTDCOST/pl.UNITCOST)) else 0 end) [2018 QTY],
sum(case when year(ph.receiptdate) = 2018 then convert(numeric(19,2),pl.EXTDCOST) else 0 end) [2018 AMOUNT],
sum(case when year(ph.receiptdate) = 2017 then convert(int,(pl.EXTDCOST/pl.UNITCOST)) else 0 end) [2017 QTY],
sum(case when year(ph.receiptdate) = 2017 then convert(numeric(19,2),pl.EXTDCOST) else 0 end) [2017 AMOUNT]
from POP30300 ph
inner join POP30310 pl
on ph.POPRCTNM = pl.POPRCTNM
where ph.POPTYPE IN (2,3) and ph.receiptdate > ‘2016-12-31’
group by ph.VENDORID, ph.VENDNAME, pl.ITEMNMBR, pl.ITEMDESC
order by ph.VENDORID, ph.VENDNAME, pl.ITEMNMBR, pl.ITEMDESC
LikeLike
Thanks Victoria. Looks like it works if we use the EXTDCOST/UNITCOST instead of pulling from POP10500.
Learn new sql comments everytime! 🙂
LikeLike
Good Morning Victoria!
This has helped me so much – but now i ran into another stumbling block. I am trying to get a customer a report that show PO remaining amounts – which the PO Status report works for UNTIL you put a return against the PO, the returns are not included in the PO remaining balance. DO you know of a report, or how in SmartList i might be able to get a good remaining about that include PO returns??
Thank you in advance for your assistance!
Sheila
LikeLike
Sheila,
I don’t believe returning something “against the PO” is actually altering the PO in any way. In other words, it does not add the returned quantities back onto PO to be received again. So what you’re seeing is correct, as far as data in GP is concerned. You could either manually alter the PO if it is still open or create a new PO for the returned items.
-Victoria
LikeLike
Hello Victoria,
It looks like the PO status is sometimes different between two tables:
POP10100 – Purchase Order Work (header) (Column POSTATUS)
POP10110 – Purchase Order Line Work (line detail) (Column POLNESTA)
Are changes in PO status pushed from the header to the line detail?
Thanks
LikeLiked by 1 person
Hi SpacecommanderJ,
Those are actually 2 different things. POSTATUS is the overall PO status, as a whole. POLNESTA is the line item status, for each individual line.
-Victoria
LikeLike
All of these tables for me are blank. Is there a switch to enable data to enter into them?
LikeLike
Aaron,
If these tables are empty, it would indicate to me that your company is not using the POP module. There are many companies out there that do not use it.
-Victoria
LikeLiked by 1 person
hi Victoria
I dont know if its just my company that has this
but is there a field for something called PO Inception?
LikeLike
Hi Milan,
That is a not a standard term found in Dynamics GP, at least to my knowledge.
-Victoria
LikeLike
Alright thank you
LikeLike
Hey Victoria
I know that POs can have a type
How do you identity which one is credit?
LikeLike
Hi Milan,
I am not aware of a “credit” PO type in Dynamics GP.
-Victoria
LikeLike
Well how would you identify a factory po vs a internal invoicing pos and others
LikeLike
Again, there is no such concept in GP. If you are already doing this, it’s something specific to your company. Maybe just with a creative use of what’s there or maybe with a customization or 3rd party product. You best bet is probably to discuss this with your GP Partner.
-Victoria
LikeLike
Hey Victoria
I have heard that a PO can be placed on hold.
Which means that somewhere there is a field for “PO on hold”
where would i find that exactly?
Thank you
LikeLike
Milan,
Do you mean in the tables? There is a field called HOLD in the POP10100 table.
-Victoria
LikeLike
ah
Found it thank you
which stands for on hold, 0 or 1?
sorry to ask but just gotta be sure
LikeLike
Milan,
0 = not on hold
1 = on hold
-Victoria
LikeLike
thanks XD
LikeLike
Hi! how the field POP10110.ORD is populated? what are the logic which defines the value in this field?
LikeLike
Hi Farukh,
Here is a blog post that talks about the line item sequence numbers, the ORD field follows the same logic:
http://dynamicsgpblogster.blogspot.com/2008/07/scrolling-windows-and-line-sequence.html
-Victoria
LikeLike
Have you ever experienced an issue when a PO number was generated with a specific vendor and items, w/confirmation sent to the customer? Then after some time elapses, somehow that same PO number has a different vendor and item number associated to it?
LikeLike
Hi Jay,
If this is really happening, that would seem to indicate either your data was changed directly in the database OR an issue with how you’re looking at the data. Usually in these cases, I would start by looking at the data directly in SQL to confirm what the data really is currently.
-Victoria
LikeLike
Hi Victoria,
Does USER2ENT in POP30100 update with the user who most recently edited the purchase order? Or is it the user who last printed the purchase order?
Thanks!
Laura
LikeLiked by 1 person
Hi Laura,
I believe that is actually the user who originally entered the PO.
-Victoria
LikeLike
Hi Victoria,
First of all, thank you for all your great work. I find myself on your site everyday looking up stuff or referring to it. IMHO this is one of the best GP10 resources on the web.
We are running GP10. My GP crashed while I was in the middle of creating a receipt for a PO, when I reopened GP is showing that, that receipt has been posted. Is there a way I can unpost that receipt or if not possible then void the receipt and unlink it from this PO and start over with a new receipt. Any help/suggestions would be greatly appreciated.
Kind regards,
-Alex
LikeLike
Hi Alex,
Thanks for your kind words. When you crash in the middle of entering a transaction, 99% of the time it possible to easily fix it with a change in the database, however, without actually seeing the data, I would not feel comfortable with giving someone instructions for it. It would be best to work with your GP partner or GP support to do this. Another option may be to run check links on the purchasing series and then reconcile on that PO, that fixes it about 50% of the time. But for that you want to make sure all users are out of GP first.
-Victoria
LikeLike
Hey, long time listener first time caller… We’re using MDA Codes on our PO Receipt Distributions, only problem were having is that we might receive several PO lines on a receipt, and if the items share the same GLs, the receipt distributions seem to combine the GL distributions from all of the line items (If they match GL Numbers). Normally that would be fine, but we want to put different MDA codes on each of the line items, and we can’t do it if they are combined. Any ideas of a work around / fix? Thanks!
LikeLike
Hi Steve,
We just set up a few Sonos devices for our living room – we love them! 🙂
Unfortunately, the POP receipts will always combine the GL distributions…I am not aware of any way around that. This not only causes problems for what you’re looking to do with MDA, but also for reporting, which I get asked about a lot. The only thing I can think of is that you can link back to the original PO line for each line item and see what account number was used, that would help you determine which GL account goes with which line for the PURCH distribution, which is what I am guessing you’re after.
Are you adding the MDA codes outside of the POP module? Either manually or through a customization? I recall doing something similar in a customization a long time ago for the SOP module…there it’s even more difficult because the distributions are only created after the transaction is posted.
If I can help with anything else, please let me know.
-Victoria
LikeLike
Hi Victoria! Glad you like the Sonos gear!
We are adding the MDA using SmartConnect. You’re right, the name of the game here is reporting. We have a Cube set up that we’re trying not to make any modifications to, so if we can modify the data to be what we want in GP, we won’t have to touch the Cube. I’m playing around with using SmartConnect to manually re-build the distributions for the Receipts and include a distribution line for each item on the PO that is received and it is looking promising. Thanks for your reply! I wanted to make sure I wasn’t just missing an easy configuration thing before I started on the project.
LikeLike
I like your comment Steve ..” long time listener first time caller… ” 🙂
Victoria, I would just like to say thank you for this great site as I have it bookmarked and check it almost daily and you are well loved within our organization … I won’t tell you the name becuase we are supposed to be professionals here 🙂
thanks again …
LikeLike
Possible correction:
[…there is a value starting with POPVT in TRXSORCE of POP30310 for voided PO’s.]
I think POP30310 should be corrected to say POP30100. I found voided POs in GP with POPVT in TRXSORCE in the PO header, but the PO had no receipt lines.
LikeLiked by 1 person
Thanks Drewmie, you are right – I have made the correction. Appreciate you catching that!
-Victoria
LikeLike
Greetings Victoria (et al)
I am trying to find out where the Customer and Customer Address fields on the Purchase Order Entry window live for a “Standard” type Purchase order.
Free Virtual Espresso for the first correct answer 🙂
Thanks
John.
LikeLike
John,
For a Standard PO, there is no link to a customer or a customer address on the PO Entry window. The only thing there could be is a Sales Document commitment – that will be shown with a link icon on each PO line item. If that is the case, you can then click through the link to the sales transaction to see the customer details.
-Victoria
LikeLike
Thank you Victoria (where should I send your coffee?)
Your response led me to looking into customizations to our system – and low and behold, there was one on the PO Edit screen with Customer and Customer Address. That then led me to to review the SQL change tracking log to see what tables were being updated and – poof! there it was.
Thanks!
John
LikeLike
John,
Glad I was able to help. Don’t worry about the coffee, it’s all good.
-Victoria
LikeLike
Hi Victoria,
I am trying to bring in the PONUMBER to my data set but no matter which table I join, It returns more records than it should. I can only assume there is a many to many relationship involved. I guess what I am getting at is the [PONUMBER] needs to roll up to the [DATERECD] but instead it is bringing in every [PONUMBER]’s for every [ITEMNMBR] which is creating duplication, For instance, after filtering everything I end up with 9 records. I join a table that has the PONUMBER and add it to my select statement, it basically triples the number of records when it should still be 9. I have tried every table where that field is housed. POP10100, POP10110, POP10500, and POP30310. Very frustrating. I’m pretty new to GP so any help you could give me would be very much appreciated.
SELECT DISTINCT
FROM [POP30330] p330
INNER JOIN [IV00101] i1 ON i1.[ITEMNMBR] = p330.[ITEMNMBR]
INNER JOIN [IV00103] i3 ON i3.[ITEMNMBR] = i1.[ITEMNMBR]
INNER JOIN [POP30300] p0 ON p0.[POPRCTNM] = p330.[POPRCTNM]
Thanks for your time,
Meredith
LikeLike
Hi Meredith,
Try something like this:
selectv.VENDNAME,
p0.VENDORID,
p0.POPRCTNM,
p330.SERLTQTY,
p0.DATERECD,
p0.ITEMNMBR,
p330.EXPNDATE,
i1.ITEMDESC,
p330.SERLTNUM,
i3.VNDITNUM
from POP10500 p0
inner join PM00200 v on p0.VENDORID = v.VENDORID
inner join POP30330 p330 on p0.POPRCTNM = p330.POPRCTNM and p0.RCPTLNNM = p330.RCPTLNNM
inner join IV00101 i1 on p0.ITEMNMBR = i1.ITEMNMBR
inner join IV00103 i3 on p0.VENDORID = i3.VENDORID and p0.ITEMNMBR = i3.ITEMNMBR
where p0.POPTYPE in (1,3)
I prefer using the POP10500 table for receipts, it’s a little cleaner. But you also need to add some additional links to make sure your data is not getting duplicated. 🙂
Hope that helps.
-Victoria
LikeLike
Thank you so much Victoria! That is a huge help and great advice. I love the site and will continue to reference it in time of need. Keep up the good work.
Meredith
LikeLike
Hi Victoria,
Is it possible to create a database query (in Excel) that tells us how many units are left in a PO to be received/invoiced? There doesn’t seem to have a direct link between POP10110 – Purchase Order Line Work (line detail) and POP30310 – Receipt Line History (line detail).
LikeLike
T.Y.H.,
I would look to link the POP10110 table to the POP10500 table for the receipt information.
-Victoria
LikeLike
Thanks for your suggestion, I didn’t think of it. However I cannot find any common field between those two to be linked. I tried using LineNumber (POP10110) and POLNENUM (POP10500) but they don’t match. Any ideas?
-T.Y.H.
LikeLike
T.Y.H.,
Looking at my data, I can link POP10110 to POP10500 on:
POP10110.PONUMBER = POP10500.PONUMBER and POP10110.ORD = POP10500.POLNENUM
Also, make sure you’re only looking at POP10500.POPTYPE 1 or 3.
Hope that helps.
-Victoria
LikeLike
Yes it’s working, thanks so much!
T.Y.H.
LikeLike
Good Day,
Not all POs found in POP10110 are reflected in POP10500. When joined several POs are missing, is there anyway to have these tables joined whilst reflecting all POs found in POP10110 or any other table that can be utilised.
Your feedback is greatly appreciated
LikeLike
Warren,
The general rule is that you want to start with the table that the largest set of data, then left join the other tables. So something like the following code will give you all the records from POP10110 and only what matches from POP10500:
select *
from POP10110 p
left outer join POP10500 r
on p.PONUMBER = r.PONUMBER
and p.ORD = r.POLNENUM
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
Hope you can help me. I need to add the Tax Schedule ID for PO Line Item on to the PO Other Form in Report Writer. I can see the field Purchase Site Tax Schedule ID in POP10110 table. But I am not able to display the field on the PO.
What do you suggest?
Thanks in advance
Regards,
Saras
LikeLike
Hi Saras,
I try very hard to stay as far away from Report Writer as I can, so I am not a good source for help with it. I would recommend posting your question on the Dynamics GP Community Forum.
-Victoria
LikeLike
Hi All,
I am looking for the table that would return the purchases account for line items on PO’s (not for any particular transaction that has been entered).
Any ideas?
Thanks!
Laura
LikeLike
Hi Laura,
The account number for each PO line item is based on what is on the PO, either the item or the vendor. So when you say ‘not for any particular transaction that has been entered’ I am not quite sure what you mean. Can you please give a more specific example of what you’re looking for?
-Victoria
LikeLike
I would like to pull the purchases account for each PO line item.
I have created an excel report to pull the purchase order line item detail such as item and description, but I can’t seem to find which table to pull the account number from.
Thanks so much!!
Laura
LikeLike
Laura,
The account number is in the same tables you’re pulling the item number and description from (POP10110 and POP30110)…it is just not as obvious. 🙂 Most Dynamics GP tables store account numbers as an ‘account index’ and you need to link to GL00105 to actually get the account number. In POP10110 and POP30110 the account index is in column INVINDX and you can link from that to GL00105.ACTINDX, then pull in column ACTNUMST from GL00105 for the actual account number. Here is an example:
select
p.PONUMBER PO,
p.ITEMNMBR Item,
p.ITEMDESC Description,
a.ACTNUMST Account
from pop10110 p
left outer join GL00105 a
on p.INVINDX = a.ACTINDX
Hope that helps.
-Victoria
LikeLike
Thank you so much!!!! 🙂
LikeLike
Hi Victoria,
I would like to know what table to look in to find out who originated a transaction.
As it is now, I know that the batched is GLTRX048190 and that the Transaction Reference, based on General Transaction Edit List is POPRCT016000.
Those two numbers are tied together in the GL10001 table, but I can’t find out who the originator is.
Any help would be appreciated.
Thanks
-Jeff Morris
LikeLike
Hi Jeff,
Unfortunately, GP does not store this information. However, in case it helps, based on the numbers you’ve mentioned, this looks like a cost adjustment that was created automatically by GP after someone entered/matched a POP invoice with an item cost that is different from what it was received for.
-Victoria
LikeLike
Hi Victoria! Thank you for your site. It is really helpful! I’m trying to find a purchasing table that contains the “quantity ordered” for items in a purchase order. I don’t know if the table columns name is different, but I just can’t find the table and field. Can you help? Thanks!
LikeLike
Hi Bill,
I believe you’re looking for QTYORDER in table POP10110.
-Victoria
LikeLike
Hi Victoria,
I would be (more) lost without your site.
I am trying to locate returns to vendors. Is there a column in a table that designates these?
thanks for everything.
LikeLike
He Jeff,
Returns that are entered as POP transactions are in the same tables as other POP transactions, listed on this page. You can identify them by the POPTYPE. They will also end up in the PM tables once posted. Returns that are entered directly in the Payables module would only be in the PM tables – you can see the details for those tables on this page. Hope that helps.
-Victoria
LikeLike
Hi Victoria,
We have some POs that we’ve cancelled and set the cost values to 0 for the line items. In the POP10110 line tables, the items reflect this, however for some POs (not many, 17 in out of 16800 total POs) in POP10100, there is a PO status of 0. This status doesn’t correspond to any of the ones you have listed above. Less than half of these 17 with a 0 status have credit amounts for the header total of the PO despite their line items in POP10110 totaling to 0.
My question is this: If the header statuses are 0, then do I not have to worry about these PO header costing values being added to any of the GL/accounting kinds of tables? Or, are these costs counted towards other tables regardless of the PO status?
Thank you
LikeLike
Hi Matt,
I believe PO’s with a status of 0 are not going to be valid transactions. You might want to take a look at some of the other attributes on these. Usually you will see that they are missing dates and vendor ID’s, as well, and are actually ‘orphaned’ records – ones that someone started but crashed out before actually saving them or something like that.
As to your other question – Purchase Orders are not considered ‘GL/accounting’ transactions, there is no GL posting that is caused by a PO. Only by receipts of PO’s or invoices from PO’s will cause a GL event.
Hope that helps,
-Victoria
LikeLike
I appreciate the prompt reply. I figured they weren’t doing any harm as they were however I’m happy to have sought your input before anyone around here resorted to running some unnecessary UPDATE statements.
Thank you
LikeLike
Victoria,
We have along list of Open POs that are no longer needed. Is there a way to mass close or Mass-delete Open POs? Our ultimate objective is to inactivate vendors that we no longer deal with, but the open POs are preventing us from doing that. Any thoughts/suggestions appreciated.
-David
LikeLike
David,
I am not aware of a way to mass close PO’s. The only way to mass delete them would be in SQL, but there is no error checking there, you could easily delete something that links to something else, thus causing a data issue. I would recommend posting this question on the GP Community Forum to see if anyone has done this before and can let you know what they did.
-Victoria
LikeLike
Is there a way to find out which user closed a purchase order?
LikeLike
Mike,
I do not believe Dynamics GP tracks this information. So if you needed this, you would have to create some custom process to track it.
-Victoria
LikeLike
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.
LikeLike
Mark,
I don’t think that’s an out of the box option – is it possible that you have a customization for this?
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Dave,
There is no history version of this table. However, there is a separate set of historical tables that tracks POP receipt transactions: POP30300 and POP30310.
I don’t have a query like the one you’re describing written yet. If you need help when writing it, let me know and I will try to help.
-Victoria
LikeLike
I did see those tables, however I did not find a recieved quantity field… So are you saying that the data in the POP10500 table stays there when a PO moves to history?
LikeLike
Dave,
POP10500 is probably the best table to use…yes, the data stays in there after the PO moves to history.
-Victoria
LikeLike
Thank you very much!!!!! And for the quick reply!!!!!
And again, I love your site, keep up the good work!!!!
Dave M
LikeLike
Thanks Dave! 🙂
-Victoria
LikeLike
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?
LikeLike
Jenny,
You should be able to see this data in table POA40003.
-Victoria
LikeLike
Looks good!
Thank you
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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:
-Victoria
LikeLike
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.
LikeLike
How can i tell if its out of the box or customized. It may have been customized but was probably before my time.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Victoria:
I just notice that the POP Type = 8 when it was from an Intransit Transfer. Please update that part in the page. Thanks.
LikeLike
Thanks Jesus, I have updated the list.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Understand the complexity involved Victoria, thanks.
Is there any other product available which is similar to Post Master?
Regards,
Keshav
LikeLike
Keshav,
There may be others, I don’t know. Post Master is the only one I have used.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Victoria,
Thank you so much for fast reply victoria. It really help.
-rolan
LikeLike
Hi Victoria,
Need Your Help…Again 😛 ,
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.. 😮
LikeLike
sry table name pop30310 not 303310 😛
LikeLike
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
LikeLike
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.
LikeLike
Hi Carleesha,
I am not aware of any existing method or product to accomplish this. So you would most likely need to develop your own customization if you needed to do this.
-Victoria
LikeLike
Hi Victoria, Thank you for responding, I guess that is something we will have to look into then.
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
I bow to you, Victoria! You make my job easier.
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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~
LikeLike
Yash,
It looks like you have double posted your question here and on a forum: http://dynamicsuser.net/forums/p/55744/295737.aspx#295737. I have answered it there.
-Victoria
LikeLike
Thanks victoria, i got ur reply there already 🙂
~Yash~
LikeLike
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.
LikeLike
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
LikeLike
Thanks, Victoria. I’ll try the testing route.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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:
Of course there are variations on this process, but hopefully that gives you a better idea of the process.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Thank you so much Victoria!
LikeLike
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?
LikeLike
Hi Alexander,
This looks good to me.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
Hi Rob,
I believe those are in POP10310.
-Victoria
LikeLike
Thank you, just what I needed!
LikeLike
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)
LikeLike
Vic,
If you look at your raw data, do you have promise dates that are before today’s date? If so, why would you not expect to see them in your results?
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks a bunch, Victoria, for your prompt response and help. I really appreciate it.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
From pop10110 field QTYUNCMTBASE do you now what does it mean?
LikeLike
Pablo,
That holds the QTY Uncommitted in Base Unit of Measure.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
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!!
LikeLike
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
LikeLike
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
LikeLike
Vic,
Have you tried check links and reconcile?
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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’)
LikeLike
That worked great, thank you.
LikeLike
Hi Victoria, how would one restrict the delete and void button for New Approved POs? Thanks.
LikeLike
Hi Victoria,
How would one restrict the Delete and Void button in PO for New Approved POs? Thanks.
LikeLike
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
LikeLike
Hi Victoria,
Where (Table) can I find the Payments to all the PO’s which are not closed?
Thanks,
Aaron
LikeLike
Hi Aaron,
The only way GP (out-of-the-box) can associate payments with PO’s is if the PO’s are already received and invoiced. If that’s what you’re looking for, you can use this view: https://victoriayudin.com/2008/10/17/sql-view-what-check-paid-purchase-order/. If you’re making prepayments on PO’s, then GP does not have a way to track this, so the reporting would have to be custom-created for you, based on specifically how you are entering this information.
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
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?
LikeLike
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:
-Victoria
LikeLike
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…
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Allen, did you find a solution to your problem? I think I might be having the same issue with SP5.
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Aaron,
I believe you’re looking for IV10200. Here is a view you might find helpful using this table: https://victoriayudin.com/2009/06/18/sql-view-for-inventory-quantities-on-hand/.
-Victoria
LikeLike
Victoria,
This is exactly what I was looking for. Thank you for your help once again.
-Aaron
LikeLike
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?
LikeLike
Victoria,
It sounds to me like that documentation is not correct…can you tell me where you were reading this?
-Victoria
LikeLike
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
LikeLike
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
LikeLike
That’s explain it.
Thank you so much!!
LikeLike
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.
LikeLike
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
LikeLike
Hello –
Just want someone’s agreement (or disagreement). In the POP10500 table, is the QTYRESERVED = to the quantity returned?
Thank you
LikeLike
Hi Stephanie,
According to the GP 2010 SDK, Quantity Reserved = “The quantity to be returned.”
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Khalil,
I believe that information is stored in POP10600 – Purchasing Shipment Invoice Apply. I just added this table to the list above.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
thanks Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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???
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Mike,
That is great, thanks for following up and for letting me know what it turned out to be!
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks Victoria
I’m union POP10100 and sy03900, with noindx and PONOTIDS_1.
Thanks Victoria.
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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?
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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….
LikeLike
Is there a table that has both Account Index and PO Number?
LikeLike
Nick,
POP10110 has both, but the account index is called INVINDX.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Archie,
When a receipt is posted it is not cleared from the POP10500 table, only the Status is changed.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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:
-Victoria
LikeLike
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
LikeLike
Hi Jeff,
Sorry, I can’t help with Business Portal tables, I would recommend posting this question on one of the GP newsgroups to see if someone there can help you.
-Victoria
LikeLike
Hi Victoria
Thanks a lot for the reply, I will try myself, and will update you once I succeed.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Tin,
If you’re using the POP-SOP links in GP, this view I have posted may be of help: https://victoriayudin.com/2009/02/04/sql-view-to-show-items-with-sop-pop-link/.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
You are my hero!
Thanks!
Merry Christmas!
LikeLike
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!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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….
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
Thanks for the quick response.
The Status column has a value of 0.
Brent
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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 POA40003set POA_PO_Approval_Status = 2,
POA_Approved_By = 'sa'
where PONUMBER = 'PO12345'
and POA_PO_Approval_Status = 1
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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@
LikeLike
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
LikeLike
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
LikeLike
Drume,
Good points, thanks!
-Victoria
LikeLike
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
LikeLike
Nikki,
Yes, one way to see historical POP receipts is to go to Inquiry > Purchasing > Purchase Order Documents. In there you can search by either Vendor, PO Number, Receipt Number or date range.
-Victoria
LikeLike
Ah, I see, I missed the drop-down under ‘Documents’. Thanks so much!
Nikki
LikeLike
Victoria,
Your site is very helpful.
Are there any sites like yours that focus on Dynamics GP payroll issues?
Chris
LikeLike
Chris,
Thanks! I am not aware of anything like this for the Payroll module, sorry.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Yep got it after a bit of digging….thanks for your speedy response though 🙂
LikeLike
Where is the distribution refernece stored in the payable transaction entry distribution screen pls?
PS….really useful site.
LikeLike
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
LikeLike
Wow! Awesome.. great work. Thanks Victoria.
This is very helpful.
LikeLike
This is great Victoria!! I use it nearly every day. Thanks.
LikeLike
Awesome Stuff….
Thankx
LikeLike