
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
Subscribe To Our Newsletter
Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!
You have Successfully Subscribed!