Retrieving Sales Order defaults for a MAS 500 customer

One of the useful stored procedures in MAS 500 that I have used quite a bit is spsoGetCustDflts.  This stored procedure allows you to pass in customer parameters and have the system return to you nearly all of the customer attributes you would need to populate a Sales Order in MAS 500.

The spsoGetCustDflts procedure takes three inputs:

iCompany – the CompanyID that the customer belongs to (COA, SOA, etc.)

iCustID – The MAS 500 tarCustomer.CustID of the customer record you want to get attributes about

iOnlyCust – Set to either 0 or 1.  If you set to 1 it will only search customers.

Here is a sample T-SQL script showing how to run this procedure:

DECLARE
@_oAllowInvtSubst     smallint,
@_oBillingType        smallint  ,
@_oCreditLimit        dec(15,3) ,
@_oCreditLimitAgeCat  smallint  ,
@_oCustID             VARCHAR(15)  ,
@_oCustName           VARCHAR(40)  ,
@_oBillToAddrKey      integer   ,
@_oBillToCustAddrID   VARCHAR(15)  ,
@_oDfltItemKey        integer   ,
@_oDfltSalesAcctKey   integer   ,
@_oShipToAddrKey      integer   ,
@_oShipToCustAddrID   VARCHAR(15)  ,
@_oCreditHold         smallint  ,
@_oPrimaryAddrKey     integer   ,
@_oReqPO              smallint  ,
@_oRetntPct           dec(5,4)  ,
@_oDfltReturnAcctKey  integer   ,
@_oStatus             smallint  ,
@_oTradeDiscPct       dec(5,4)  ,
@_oCustClassKey       integer   ,
@_oClassOvrdSegVal    VARCHAR(15)  ,
@_oPriority           integer   ,
@_oPmtTermsKey        integer   ,
@_oSOAckFormKey       integer   ,
@_oCurrExchSchdKey    integer   ,
@_oCurrID             VARCHAR(3)   ,
@_oPrintOrdAck        smallint  ,
@_oCntctKey           integer   ,
@_oPackListKey        integer   ,
@_oShipMethKey        integer   ,
@_oShipZoneKey        integer   ,
@_oSperKey            integer   ,
@_oSTaxSchdKey        integer   ,
@_oFOBKey             integer   ,
@_oCommPlanKey        integer   ,
@_oWhseKey            integer   ,
@_oShipDays           integer   ,
@_oShipComplete       smallint  ,
@_oShipLabelKey       integer   ,
@_oCustSourceKey      integer   ,
@_oReqAck             smallint  ,
@_oWhseAllowImmedPick smallint  ,
@_oFreightMethod      smallint  ,
@_oRetVal             smallint 

EXEC dbo.spsoGetCustDflts

‘NelsHard’, –@_iCustID
‘SOA’, –@_iCompanyID
1,–@_iOnlyCust
@_oAllowInvtSubst OUTPUT,
@_oBillingType OUTPUT,
@_oCreditLimit OUTPUT,
@_oCreditLimitAgeCat OUTPUT,
@_oCustID OUTPUT,
@_oCustName OUTPUT,
@_oBillToAddrKey OUTPUT,
@_oBillToCustAddrID OUTPUT,
@_oDfltItemKey OUTPUT,
@_oDfltSalesAcctKey OUTPUT,
@_oShipToAddrKey OUTPUT,
@_oShipToCustAddrID OUTPUT,
@_oCreditHold OUTPUT,
@_oPrimaryAddrKey OUTPUT,
@_oReqPO OUTPUT,
@_oRetntPct OUTPUT,
@_oDfltReturnAcctKey OUTPUT,
@_oStatus OUTPUT,
@_oTradeDiscPct OUTPUT,
@_oCustClassKey OUTPUT,
@_oClassOvrdSegVal OUTPUT,
@_oPriority OUTPUT,
@_oPmtTermsKey OUTPUT,
@_oSOAckFormKey OUTPUT,
@_oCurrExchSchdKey OUTPUT,
@_oCurrID OUTPUT,
@_oPrintOrdAck OUTPUT,
@_oCntctKey OUTPUT,
@_oPackListKey OUTPUT,
@_oShipMethKey OUTPUT,
@_oShipZoneKey OUTPUT,
@_oSperKey OUTPUT,
@_oSTaxSchdKey OUTPUT,
@_oFOBKey OUTPUT,
@_oCommPlanKey OUTPUT,
@_oWhseKey OUTPUT,
@_oShipDays OUTPUT,
@_oShipComplete OUTPUT,
@_oShipLabelKey OUTPUT,
@_oCustSourceKey OUTPUT,
@_oReqAck OUTPUT,
@_oWhseAllowImmedPick OUTPUT,
@_oFreightMethod OUTPUT,
@_oRetVal OUTPUT

SELECT
–@_oAllowInvtSubst,
–@_oBillingType ,
–@_oCreditLimit ,
–@_oCreditLimitAgeCat ,
@_oCustID CustID,
@_oCustName CustName,
–@_oBillToAddrKey,
–@_oBillToCustAddrID ,
–@_oDfltItemKey ,
–@_oDfltSalesAcctKey ,
–@_oShipToAddrKey ,
–@_oShipToCustAddrID ,
–@_oCreditHold ,
@_oPrimaryAddrKey PrimaryAddressKey,
–@_oReqPO ,
–@_oRetntPct ,
–@_oDfltReturnAcctKey ,
–@_oStatus ,
–@_oTradeDiscPct ,
–@_oCustClassKey ,
–@_oClassOvrdSegVal ,
–@_oPriority ,
–@_oPmtTermsKey ,
–@_oSOAckFormKey ,
–@_oCurrExchSchdKey ,
@_oCurrID CUrrID,
–@_oPrintOrdAck ,
–@_oCntctKey ,
–@_oPackListKey ,
@_oShipMethKey ,
–@_oShipZoneKey ,
–@_oSperKey ,
–@_oSTaxSchdKey ,
–@_oFOBKey ,
–@_oCommPlanKey ,
@_oWhseKey Warehousekey,
–@_oShipDays ,
–@_oShipComplete ,
–@_oShipLabelKey ,
–@_oCustSourceKey ,
–@_oReqAck ,
–@_oWhseAllowImmedPick ,
–@_oFreightMethod ,
@_oRetVal

This returns the various attributes shown in the sample Select clause above (Some of them are commented out in the provided sample).

The stored procedure also returns oRetVal which has the following possible attributes:

• 0 -Error


• 1 -No record found


• 2 -Good results

Here is a sample of using this code from a VBScript in SalesLogix:

First we initialize a global variable called m_DefaultsCMD with the following routine:

Private Sub MASInitCustomerDefaults(ByVal custno, ByVal company)
Dim p

    Set m_DefaultsCMD = CreateObject(“ADODB.Command”)
    With m_DefaultsCMD
        .CommandText = “spsoGetCustDflts”
        .CommandType = 4
        .CommandTimeout = 480
        .ActiveConnection = m_MasConn ‘This is a connection object to the MAS 500 SQL database

        .Parameters.Append .CreateParameter(“@_iCustID”, 129, 1, 12, custno)
        .Parameters.Append .CreateParameter(“@_iCompanyID”, 129, 1, 12, company)
        .Parameters.Append .CreateParameter(“@_iOnlyCust”, 2, 1, 1, 1)

        .Parameters.Append .CreateParameter(“@_oAllowInvtSubst”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oBillingType”, 2, 2, 2)

        Set p = .CreateParameter
        p.Name = “@_oCreditLimit”
        p.Type = 14
        p.Direction = 2
        p.Precision = 15
        p.NumericScale = 3
        .Parameters.Append p ‘.CreateParameter(“@_oCreditLimit”, 14, 2, 15)

        .Parameters.Append .CreateParameter(“@_oCreditLimitAgeCat”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oCustID”, 200, 2, 15)
        .Parameters.Append .CreateParameter(“@_oCustName”, 200, 2, 40)
        .Parameters.Append .CreateParameter(“@_oBillToAddrKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oBillToCustAddrID”, 200, 2, 15)
        .Parameters.Append .CreateParameter(“@_oDfltItemKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oDfltSalesAcctKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oShipToAddrKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oShipToCustAddrID”, 200, 2, 15)
        .Parameters.Append .CreateParameter(“@_oCreditHold”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oPrimaryAddrKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oReqPO”, 2, 2, 2)

        Set p = .CreateParameter
        p.Name = “@_oRetntPct”
        p.Type = 14
        p.Direction = 2
        p.Precision = 5
        p.NumericScale = 4
        .Parameters.Append p ‘.CreateParameter(“@_oRetntPct”, 14, 2, 5)

        .Parameters.Append .CreateParameter(“@_oDfltReturnAcctKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oStatus”, 2, 2, 2)

        Set p = .CreateParameter
        p.Name = “@_oTradeDiscPct”
        p.Type = 14
        p.Direction = 2
        p.Precision = 5
        p.NumericScale = 4
        .Parameters.Append p ‘.CreateParameter(“@_oTradeDiscPct”, 14, 2, 5)

        .Parameters.Append .CreateParameter(“@_oCustClassKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oClassOvrdSegVal”, 200, 2, 15)
        .Parameters.Append .CreateParameter(“@_oPriority”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oPmtTermsKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oSOAckFormKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oCurrExchSchdKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oCurrID”, 200, 2, 3)
        .Parameters.Append .CreateParameter(“@_oPrintOrdAck”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oCntctKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oPackListKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oShipMethKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oShipZoneKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oSperKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oSTaxSchdKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oFOBKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oCommPlanKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oWhseKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oShipDays”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oShipComplete”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oShipLabelKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oCustSourceKey”, 3, 2, 4)
        .Parameters.Append .CreateParameter(“@_oReqAck”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oWhseAllowImmedPick”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oFreightMethod”, 2, 2, 2)
        .Parameters.Append .CreateParameter(“@_oRetVal”, 2, 2, 2)

        .Execute , , 128

        If Defaults(“RetVal”) <> 2 Then
            AddError “Error retrieving customer defaults to create new MAS customer. ” & IIF(Defaults(“RetVal”) = 1, “The template record was not found.”, “”)
        End If
    End With

End Sub

Now with our global variable initialized we can use it from other areas.  The following if a function that returns the default value of the specified input parameter:

Private Function Defaults(ByVal FieldName)
    Defaults = m_DefaultsCMD.Parameters(“@_o” & FieldName).Value
End Function

So with these two routines/functions we can utilize the whole thing like so

Sub Main
  Dim CustID
  Dim CompanyID

CustID=”NelsHard”

CompanyID = “SOA” 

MASInitCustomerDefaults CustID, CompanyID

  Msgbox “This customer’s default Customer Class Key is: ” & Defaults(“CustClassKey”)

  Set m_DefaultsCMD.ActiveConnection = Nothing
  Set m_DefaultsCMD = Nothing
End Sub