You’re offline. This is a read only version of the page.
Announcements
5
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
Hi All
Let me offer a simpler solution.
Instead of writing your own code, why not use GP Power Tools to automate that task for you.
Any repetitive steps can be easily automated so that you can use the existing built in functionality.
Regards
David
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.
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.
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?
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.
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?
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.
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
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?
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
#1
André Arnaud de Cal...
291,359
Super User 2024 Season 2
#2
Martin Dráb
230,370
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
