Sage 100

Client imported several hundred (1800-ish i think) rows into PO to change the warehouse code, but did not include the line numbers. As a result instead of altering the lines on the PO, it created new lines. The upside it, there is no quantity on the lines, so it's not affecting actual inventory or reorders. Is there an easy way to remove all the lines? They are in 2013, so no PO history yet, and with several hundred-thousand PO's in ""history"" with a closed status, I don't feel like exporting, and reimporting is an option... so I am hoping there is a magical trick I am not aware of??? Has anyone dealt with anything like this before? TIA!!

Sounds like it would be a great scripting opportunity

No time @RobertWhite, of course this is one of those it has to be complete by yesterday deals. I think we could manually remove the lines faster than we could write and test a script. I will keep that as an option though.

Back up :)

right?? Lesson learned (hopefully)

Is it one giant PO? You could export it, delete the whole PO and then import just the lines with Qty or with the warehouse you want.

@BrettLyder no it's a few hundred PO's :(

@BrettLyder - That would be a VERY large PO........

Nope, this is a VERY large client - I'll bet there have been thousands of transactions :(

Bummer, I assumed it was several but figured it couldn't hurt to ask. Obviously they have to be very careful selectively restoring but if they've been fairly ""quiet"" in the PO module, it might be worth exploring a partial PO restore from a nightly backup.

What if you create a new PO Header record, VI all those bad lines and point them to the new PO Header, then DFDM kill the header and Rebuild Lines - would that kick out the bad records?

ACTUALLY.... this is SQL... so I wonder if we can just do a select statement to remove the PO lines with a 0 qty....???? @ShawnSlavin

SUCCESS! Thanks guys!

So.. for our edification. Did you end up just deleting them with a SQL query? Or did you use some variation of @WayneSchulz incredibly creative idea?

Michelle, you should be on the right track with SQL query.

Deleting with a SQL query. We did a select statement first to make sure it returned the results we wanted. Since there were no quantities uploaded, we were able to use the PO date, along with the Qty Ordered, Invoiced and rec'd = 0 and status <> X. The once we verified it was ok, we changed from a select statement to a delete statement and Viola! I can post the actual statement it anyone would find it helpful.

Select * from PO_PurchaseOrderDetail where QuantityOrdered = '0' and QuantityInvoiced = '0' and QuantityReceived = '0'and RequiredDate > '2015-09-01 00:00:00.000' and ItemCode != '/c' and linekey != '000001' and linekey != '000002' Select * From PO_PurchaseOrderHeader where OrderStatus != 'X'

They decided we missed some. (of course) so restore, and re-do. I am simply posting this in case I need it again or it can benefit anyone in the future :)

Great idea to use the SQL tools to solve this. What I have done in providex is to copy the company to a new company code using Company Maintenance. Reinitialize the file with the bad records in the live company (PO header) then use a VI job to copy the needed records into the cleared file. Using the VI Job to skip 0 quantity records in this case.