Finance | Project Operations, Human Resources, ...

Sql code to push order back to open quote

(0) Share

Report

Posted on by Mark Cruse

Hi,

I'm hoping someone can help provide the sql code to do the following.

In Dynamics GP i regularly need to perform the following command:

Transactions / Sales / Sales Transactions Entry

Enter order number, press tab

Click Actions / Back To Open Quote

As far as im aware this deletes the order from GP and passes instruction to our InforCRM system to put the order back to open quote stage.

Is anyone able to provide an Sql code that will do the same thing and make the same changes to the same tables etc please?

Many thanks,

Mark

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (11)

Answers (0)

Richard Wheeler 75,852 Moderator on at

Like (0)

Report
Copy link

Link copied!

This task would need to designed and tested. Changing the document type would be one thing but now you would have to go through lines on the order and adjust inventory quantities.Not to mention if order lines get committed to purchase orders. I am sure this can be done given the proper design.

Was this reply helpful? Yes No
Mark Cruse 5 on at

Like (0)

Report
Copy link

Link copied!

Hi Richard,

Thanks for your reply.

We only carry out this action when the order hasnt imported into GP correctly and all quantities and prices show as zero, so this would not need to edit inventory tables, stock quantities etc and the orders would not have been committed to purchase orders.

Thanks,

Mark

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like (0)

Report
Copy link

Link copied!

Do you already have the code that adds the menu item in GP so all you need is the SQL script to change the SOPTYPE from Order to Quote? What about the document number? Will that need to change or do your document numbers stay the same regardless of type?

Was this reply helpful? Yes No
Mark Cruse 5 on at

Like (0)

Report
Copy link

Link copied!

No i don't have the code that adds the menu item in GP so would need the code to do both.

Our document numbers stay the same regardless of type.

I believe the 'back to open quote' action is the same as deleting the order from GP but also passes a command to our separate sales system (inforCRM) to put the order back to open quote stage in InforCRM.

I hope that makes sense?

Thanks

Mark

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like (0)

Report
Copy link

Link copied!

Do you an IT person who can assist with this? We would need to know what tool was used to add the menu option and what it does under the hood. The SQL part to change the document type is the easiest part.

Was this reply helpful? Yes No
Mark Cruse 5 on at

Like (0)

Report
Copy link

Link copied!

Hi Richard,

Unfortunately I am it. The developers who originally handled our Dynamics gp have since moved departments or left the company and are no longer available to assist.

Am i right in thinking that 'back to open quote' action is not a standard action in gp and therefore a customisation on our end?

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like (0)

Report
Copy link

Link copied!

Going backwards is not standard using GP. You need to change the SOPTYPE from 2 to 1. This is done in the SOP10100 and SOP10200 tables. Plus the order number is not the same as the quote number.

Was this reply helpful? Yes No
Mark Cruse 5 on at

Like (0)

Report
Copy link

Link copied!

Ah, perhaps that is what the back to open quote action is doing then. Is there a way i can see under the hood of what the action button does? A way that i can see the sql code that is carried out when i click this button?

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like (0)

Report
Copy link

Link copied!

With no one else in GP fire up SQL Profiler then click the button. Hopefully the SQL log will show you what is happening under the hood.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi Mark,

Reading this thread and seeing the last answer to it by Richard, your only options to try understand what's behind that menu option is either the SQL trace, or a GP DEXSQL Trace file.

Microsoft has a KB article on how to do this, but the problem with that is that it captures everything since you start the GP client. The pro side of it vs. the SQL trace is that you don't have the 'noise' of other SQL sessions coming into play.

An easier way to capture a DEXSQL trace log just on the spot, would be to use the GP PowerTools from David Musgrave. You can install it with a free 30-days trial key and would be able to start the DEXSQL trace from within GP, right before clicking on the menu option and stop it right after, which keeps the trace LOG to the bare minimum and stick to just the action you just performed in GP.

Once the log has been captured, you can open it with a Notepad and read backwards to see what it has done. Maybe you'll be able to spot some Stored Procedures or other SQL code that is called up by that menu entry.

Was this reply helpful? Yes No