Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / Sql code to push order...
Microsoft Dynamics GP forum
Suggested answer

Sql code to push order back to open quote

(0) ShareShare
ReportReport
Posted on by Mark Cruse 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

  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,833 Moderator on at
    RE: Sql code to push order back to open quote

    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

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Moderator on at
    RE: Sql code to push order back to open quote

    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. 

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Sql code to push order back to open quote

    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.

  • Mark Cruse Profile Picture
    Mark Cruse 5 on at
    RE: Sql code to push order back to open quote

    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?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Sql code to push order back to open quote

    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.

  • Mark Cruse Profile Picture
    Mark Cruse 5 on at
    RE: Sql code to push order back to open quote

    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?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Sql code to push order back to open quote

    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.

  • Mark Cruse Profile Picture
    Mark Cruse 5 on at
    RE: Sql code to push order back to open quote

    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

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Sql code to push order back to open quote

    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?

  • Mark Cruse Profile Picture
    Mark Cruse 5 on at
    RE: Sql code to push order back to open quote

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,552 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,552 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans