[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1469
  • Last Modified:

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
0
drivingforce
Asked:
drivingforce
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now