Get Next Dynamics GP SOP Transaction Number Using SQL

My blog has moved!  Please visit the new blog at:  https://blog.steveendow.com/

I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com

Thanks!



By Steve Endow

As I mentioned in a recent post, some times it's better to use SQL to retrieve the next document number from Dynamics GP.

I am currently working on an eConnect SOP Order import and needed to retrieve the next order number from GP.

Here is my C# code for retrieving the next SOP number from GP using SQL.

The SQL stored procedure is called taGetSopNumber and you need to provide it with the SOP Type and the Doc Type ID of your desired transaction.


public string GetNextSOPNumber(int sopType, string docTypeID)
{
    //SOP Type:  
    //1 = Quote
    //2 = Order
    //3 = Invoice
    //4 = Return
    //5 = Back Order
    //6 = Fulfillment Order

    try
    {
        SqlConnection gpConn = ConnectionGP();

        string commandText = "taGetSopNumber";

        SqlParameter[] sqlParameters = new SqlParameter[5];
        sqlParameters[0] = new SqlParameter("@I_tSOPTYPE", System.Data.SqlDbType.TinyInt);
        sqlParameters[0].Value = sopType;
        sqlParameters[1] = new SqlParameter("@I_cDOCID", System.Data.SqlDbType.Char, 15);
        sqlParameters[1].Value = docTypeID;
        sqlParameters[2] = new SqlParameter("@I_tInc_Dec", System.Data.SqlDbType.TinyInt);
        sqlParameters[2].Value = 1;
        sqlParameters[3] = new SqlParameter("@O_vSopNumber", System.Data.SqlDbType.Char, 21);
        sqlParameters[3].Value = string.Empty;
        sqlParameters[3].Direction = ParameterDirection.Output;
        sqlParameters[4] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);
        sqlParameters[4].Direction = ParameterDirection.Output;
        sqlParameters[4].Value = 0;

        string nextNum = string.Empty;
        int recordCount = DataAccess.ExecuteNonQuery(gpConn, Controller.Instance.Model.GPDatabase, CommandType.StoredProcedure, commandText, sqlParameters);
        nextNum = sqlParameters[3].Value.ToString().Trim();

        return nextNum;
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in GetNextSOPNumber: " + ex.Message, true);
        return string.Empty;
    }
}

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com