Open Forum

Expand all | Collapse all

COI - CUSTOM STORED PROCEDURE

  • 1.  COI - CUSTOM STORED PROCEDURE

    Posted 9 days ago
    Hi Samantha,
    I was able to update the COI on the vendor table, however when I got a bit further under tracking, I was not able to complete task 2. Can you provide the steps on where I need to go in GP to get this done?

    2.) If you have a LOT of vendors to where manually placing them on hold would be unrealistic, you could utilize a custom stored procedure to run at regular intervals (daily in the morning is my initial thought) that evaluates whether the expiration date is in the past and automatically places the vendor on hold.


    Thanks for your help!

    ------------------------------
    Debra
    Manager-Accounts Payable
    The HoneyBaked Ham Company
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: COI - CUSTOM STORED PROCEDURE

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Good Morning @Debra T,

    The custom stored procedure isn't set up directly in GP.  It would be a custom stored procedure that would be written directly in the SQL database that GP runs on.  I would recommend talking to someone in your IT department about who the most appropriate resource to help you out would be.

    They would essentially want to write a procedure that uses the conditional logic below.  Please note it's not written in actual SQL coding that the IT person would use, but it should give you and them the general idea.

    Update PM00200
    Set HOLD='1' where USERDEF1 <= Today's date


    The PM00200 is the SQL table that stores the vendor master information.  The "HOLD" field is the hold checkbox you're wanting to have marked automatically when they are expired.  The "USERDEF1<= Today's Date" will do an analysis on the User Defined 1 field (assuming that's the one you used) and compare it to today's date.  If the User Defined 1 date is less than or equal to Today's date, the vendor will be placed on hold.

    Note you may need to have the IT person do some formatting on the User Defined 1 field for it to properly evaluate the date, but that should be pretty straight forward.


    Does that make sense?  If not, let me know.

    Thanks,

    Samantha

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Lagom, LLC
    Carmel IN
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role