Canceling a PO in Dynamics GP

Hi
I am looking for a way to cancel a GP 10.00  PO using T-SQL Script.
Script or direction would be appreciated.
Thank you
drivingforceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

It seems that changing a PO status to Cancelled via SQL could potentially be quite complex, depending on the state of the PO and its line items.  There is alot going on behind the scenes when a PO is cancelled, so it isn't necessarily as simple as just changing the status flag to Cancelled.

After reviewing the processes that are performed, I would personally recommend cancelling them manually in GP rather than through SQL.  

I was able to see what fields are updated to cancel a new or released PO (POSTATUS, POLNESTA, PO_Line_Status_Orig, QTYCANCE, etc.), but I also see that GP is checking the status of many other things in that process that could be affected by the cancellation of the PO (Qty received, qty invoiced, qty rejected, SO commitments, etc.).  If you only update the PO records via SQL, it could cause problems in other areas that expect to be updated when that PO is cancelled.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional



0
drivingforceAuthor Commented:
Thanks Steve ... I suspected that much but thought I would through it out there and see if I am luck! The problem is that we have a custom solution that generates these POs and then they are integrated through e-connect. It would be great if the cancellation can be done similarity.

Are you aware of any way that e-connect can be used to integrate canceled POs?

Thanks
0
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

It does not look like eConnect or Web Services supports importing or changing the status of POs. (although Web Services does appear to support deleting POs)

If these are brand new POs that are being imported, but will never be printed or otherwise used, then I think a SQL approach might work.

The risk I referred to earlier is primarily if GP is relying on the POs and if actions have been performed against them.

If they are not going to be printed or otherwise used (just need to be recorded in GP for tracking purposes), then you can try the SQL approach, but just be careful to monitor for any issues with the POs or inventory.

It looks like it cancels the PO and PO Lines, copies the status and costs to "Originating" fields, then zeros out all costs and subtotals.  I believe that storing the originating amounts allows it to restore those in case the PO is ever set back to New.

Below are some update statements based on a quick review of the PO Header and Line tables.  They are only a very rough initial guide.  You'll want to do some SQL traces on the cancel process and review all of the statements and make sure to develop your own statements that will meet your needs.

You might want to run the Purchasing Transaction Checklinks routine on your database before you do the updates, and then after you do some, run checklinks again to see if it finds any issues.  Just make sure to test in a test environment and have backups of your databases before you do anything in production.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional




UPDATE POP10110 SET POLNESTA = 6, STATGRP = 2, QTYCANCE = QTYORDER, REMSUBTO = 0, SUBTO = 0, OREMSUBT = 0, ORSUBTOT = 0, Originating_Canceled_Sub = 10,  WHERE PONUMBER = 'PO2086' AND POLNESTA = 1

UPDATE POP10100 SET POSTATUS = 6, QTYCANCE = QTYORDER, QTYCMTBASE = 0, QTYUNCMTBASE = 0, EXTDCOST = 0, OREXTCST = 0,  WHERE PONUMBER = 'PO2086' AND POSTATUS = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
drivingforceAuthor Commented:
Yep ... the POs created externally and generated are strictly one-liner (1 item) and as they are printed externally, the status of these POs in GP after integration is NEW until they are received.  
Thanks for the tips, I will apply these to the test company and monitor a trace for a deletion or two.
0
drivingforceAuthor Commented:
Good understanding of the problem. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.