Open Forum

Cancel By for Quote Order Type and Behavior

  • 1.  Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted Jun 21, 2019 09:24 AM
    Look for a way to automatically close/cancel open quotes after a specified number of days.  Since the Quote order type is not the correct order type behavior....

    Cancel By determines when the order schedules will be automatically cancelled for order type behaviors Manual Order, Sales Order, and Will Call Order.
    Process Manager does not cancel these quotes by the date specified.

    Does anyone know of an existing and tested solution to address this issue.

    Thank you
    Steve




    ------------------------------
    Steve Wierenga
    Officer - President
    Ajacs
    Grand Rapids MI
    ------------------------------


  • 2.  RE: Cancel By for Quote Order Type and Behavior

    BRONZE CONTRIBUTOR
    Posted Jun 21, 2019 12:47 PM
    Steve,

    I think the proc ADG_ProcessMgr_ExpiredSchedules is the one that PM runs to determine if there are SO Schedules with Cancel Dates less than the current date.  In that proc, there is a where clause:
    where h.Status = 'O' and t.Behavior in('SO', 'WC', 'MO')
    If you modify the Behavior types to include the 'Q' behavior, PM should cancel the Quote order types as well when running the nightly maintenance.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    Warminster PA
    ------------------------------



  • 3.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 30 days ago
    Excellent idea!  Thank you!

    ------------------------------
    Steve Wierenga
    Officer - President
    Ajacs
    Grand Rapids MI
    ------------------------------



  • 4.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 28 days ago
    I did the following:
    Created a SQL job that runs nightly to cancel Quote Orders (SOType 'Q').
    To further extend the flexibility, I added a user field to the Site table to set the number of days past the 'cancel by' date before cancelling..  This gave pour operations a little flexibility as some of our offices needed longer tolerance before cancelling.

    I would be glad to supply the SQL to you.

    ------------------------------
    Mark Schrier
    Information Systems Director
    Mackay Communications, Inc
    Raleigh NC
    ------------------------------



  • 5.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 26 days ago

    Hi Mark!

    We may be interested in this SQL code/job.  I appreciate your offering to share it!  Would you be interested in doing a webex to help us get it installed and in working order?  I don't have time on my schedule for this week so it would need to be next week or after.  And honestly it can be next month - no rush!  It is vacation season :)

    Cathy



    ------------------------------
    Cathy McCarter
    Financial Analyst
    Innovative Wireless Technologies
    Lynchburg VA
    ------------------------------



  • 6.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 26 days ago
    Mark,

    I am interested in looking at the SQL job you created if you are willing to share it.

    Thanks in advance!

    ------------------------------
    Steve Wierenga
    Officer - President
    Ajacs
    Grand Rapids MI
    ------------------------------



  • 7.  RE: Cancel By for Quote Order Type and Behavior

    BRONZE CONTRIBUTOR
    Posted 26 days ago
    Steve,

    Just curious if the change to the standard proc ADG_ProcessMgr_ExpiredSchedules I suggested actually worked for you.  I did not try it on my end, but believe that proc is the one that determines which SO's to cancel.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    Warminster PA
    ------------------------------



  • 8.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 26 days ago

    Mark,

     

    We are going to try this in a test environment... that we need to get setup. I will let you know as soon as we have it tested. 

     

     

    image001.jpg@01D4FA6E.5F998470

    Steve Wierenga

    4625 Clay Avenue SW, Grand Rapids, Michigan 49548 USA

    t: 800.968.6868  |  p: 616.452.1469  |  m: 616.340.2757

    image002.png@01D4FA6E.5F998470     image003.png@01D4FA6E.5F998470     image004.png@01D4FA6E.5F998470     image005.png@01D4FA6E.5F998470

    Material Handling   |   Metal Forming   |   Sensors & Safety

     

    The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material.  Any review, retransmission, dissemination or other use of, or acting in reliance upon this information by persons or entities other than the intended recipient is prohibited.  If you received this in error, please contact the sender and delete the material from any computer.

     






  • 9.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 26 days ago
    I looked at the procedure and rejected it because we needed to give each location flexibility to set 2 switches
    1. a check box - to exempt the site from cancellation of quotes
    2. An integer control to set the number of days after the quote expires before cancelling.

    From the Site screen
    Cancellation settings
    From the Other Information on the Sales Order
    Quote parameters.
    The stored procedure uses the settings in the site screen against the quote expiration date in the Sales Order screen

    ------------------------------
    Mark Schrier
    Information Systems Director
    Mackay Communications, Inc
    Raleigh NC
    ------------------------------



  • 10.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 26 days ago
    I will go through the code and document the code and the customization I did in the Site screen.
    Once that is done, I will post it for the group.

    ------------------------------
    Mark Schrier
    Information Systems Director
    Mackay Communications, Inc
    Raleigh NC
    ------------------------------



  • 11.  RE: Cancel By for Quote Order Type and Behavior

    SILVER CONTRIBUTOR
    Posted 26 days ago
      |   view attached
    If you have questions and or issues, let me know.


    Here is how the quote cancellation works:
    1. We have a stored procedure that is set up in a job:  Screen shot is below.  Each night the Job runs.
    a. it looks at the Quote Date
    b. Calculates the datediff between the quote date and the getdate()
    c. Using the difference, it compares the difference to the (quote expiration + Days old to cancel quote in the site screen)
    d. If the difference is greater, then the quote is cancelled, and the lost business code is populated
    e. This only cancels qhotes in the selected site rows based on the Auto-Cancel Quotes check box

    2. Sales Order screen customization (screen shot below
    3. Site (10.310.00) screen customization that uses 3 custom fields.  Screen shot below
        We are out of user fields, so I added a table extension (xmkSite)
    4. Stored procedure and the create table/Index script for our table extension (xmkSite) is in the attached document.

    I have a previous blog that explains how to add a table extension to a screen that has one level and displays only one row at a time..

    The stored procedure can be easily modified to use standard user fields.  Screen customizations should be no problem if you have available user fields.
    Scripts: and screen shots:


    Site Customization

    Sales Order Customization

    Job Setup



    ------------------------------
    Mark Schrier
    Information Systems Director
    Mackay Communications, Inc
    Raleigh NC
    ------------------------------

    Attachment(s)