MAS 500 checking a customers credit limit

MAS offers a stored procedure that allows you to check if a custom has exceeded their credit limit.  The procedure is called "sparCreditCheck" and takes 8 parameters:

  • @iCustKey – the customer key of the customer you want to check the credit limit on.
  • @iTranType – Transaction type of the current record.
  • @iTranKey – The document key to exclude
  • @iPmtKey – Current Payment to Exclude, if desired
  • @iTranAmt -Current Invoice amount
  • @iDownPayAmt – Current down payment
  • @iOverrideAmt – Current invoice override amount
  • @iCallingTask – Defines which process is calling the credit check.  This has the following options:
    • 1 = AR Invoice Entry
      2 = Sales Order Entry
      3 = Sales Order Menu Picking
      4 = Customer Status Inquiry
      5 = Generate Shipments
      6 = Inquiry Only
      7 = Quick Pick (aka Immediate Pick, Process Orders)

To retrieve the custkey you can run a query like: "select custkey from tarcustomer where custid = ‘some value’ and companyid=’soa’"

To retrieve the trankey you can run a similar query: "select sokey from tsosalesorder where tranno=’000000001′ and companyid=’soa’"

The trantype can be determined utilizing the trankey, like: "select trantype from tsosalesorder where sokey=’1’"

 

This procedure returns many different output values, but the primary output of @oCreditStatus will return a 0 if the credit check passed and a 1 if it failed.

 

Here is a complete list of the various output parameters:

@oCurBal               Current Balance
@oAdjTranAmt
@oPendInvcs            Pending Invoice balance
@oSalesOrds            Sales Order Balance
@oUnapprovedSalesOrds  Balance of Unapproved Sales Orders
@oPendPmts             Pending Payment Balance
@oCreditLimit          Credit Limit for Customer
@oBalOver1             Balance over credit aging category 1
@oBalOver2             Balance over credit aging category 2
@oBalOver3             Balance over credit aging category 3
@oBalOver4             Balance over credit aging category 4
@oCurAgeBal            Current Aging Balance
@oFutAgeBal            Future Aging Balance
@oCurAdjBal            Calculated customer’s Adjustment amount
@oAvailCredit          Calculated customer’s Available credit
@oNACurBal             Current National Account Balance
@oNAPendInvcs          Pending National Account Invoice balance
@oNASalesOrds          National Account Sales Order Balance
@oNAPendPmts           National Account Pending Payment Balance
@oNACreditLimit        National Account Credit Limit for Customer
@oNACurAdjBal          Calculated Natl Acct’s Adjustment amount
@oNAAvailCredit        Calculated Natl Acct’s Available credit
@oCreditStatus         Did the credit check pass or fail?
                           0 = Credit passed
                           1 = Credit failed, see failure flags for why
@oCustCreditLimitFailure  Credit Check Failed, Credit Limit exceeded
@oCustAgingCreditFailure  Credit Check Failed, Customer Aging Limits exceeded
@oNACreditLimitFailure    Credit Check Failed, National Account limit exceeded

 

And here is a sample T-SQL statement using the procedure:

DECLARE
/*
@oCreditStatus     smallint,
@oCustCreditLimitFailure        smallint  ,
@oCustAgingCreditFailure        smallint  ,
@oSalesOrds smallint,
@oUnapprovedSalesOrds smallint,
@oPendPmts smallint,
@oCreditLimit float,
@oBalOver1 float,
@oBalOver2 float,
@oBalOver3 float,
@oBalOver4 float,
@oCurAgeBal float,
@oFutAgeBal float,
@oCurAdjBal float,
@oAvailCredit float,
@oNACurBal float,
@oNAPendInvcs smallint,
@oNASalesOrds smallint,
@oNAPendPmts float,
@oNACreditLimit float,
@oNACurAdjBal float,
@oNAAvailCredit float
*/
@iCustKey                      INT,
@iTranType                     INT,
@iTranKey                      INT,
@iPmtKey                       INT,
@iTranAmt                      FLOAT,
@iDownPayAmt                   FLOAT,
@iOverrideAmt                  FLOAT,
@iCallingTask                  SMALLINT,
@oCurBal                       FLOAT,
@oAdjTranAmt                   FLOAT,
@oPendInvcs                    FLOAT,
@oSalesOrds                    FLOAT,
@oUnapprovedSalesOrds          FLOAT,
@oPendPmts                     FLOAT,
@oCreditLimit                  FLOAT,
@oBalOver1                     FLOAT,
@oBalOver2                     FLOAT,
@oBalOver3                     FLOAT,
@oBalOver4                     FLOAT,
@oCurAgeBal                    FLOAT,
@oFutAgeBal                    FLOAT,
@oCurAdjBal                    FLOAT,
@oAvailCredit                  FLOAT,
@oNACurBal                     FLOAT,
@oNAPendInvcs                  FLOAT,
@oNASalesOrds                  FLOAT,
@oNAPendPmts                   FLOAT,
@oNACreditLimit                FLOAT,
@oNACurAdjBal                  FLOAT,
@oNAAvailCredit                FLOAT,
@oCreditStatus                 SMALLINT,
@oCustCreditLimitFailure       SMALLINT,
@oCustAgingCreditFailure       SMALLINT,
@oNACreditLimitFailure         SMALLINT,
@oNAAcctID                     VARCHAR(15),
@oNAAcctDesc                   VARCHAR(30),
@oNAAcctLevel                  VARCHAR(30),
@oLastFinChrgDate              DATETIME,
@oLastFinChrgAmt               FLOAT,
@oLastPmtDate                  DATETIME,
@oLastPmtTranID                  VARCHAR(13),
@oLastPmtAmt                  FLOAT,
@oOldestInvcDate               DATETIME,
@oOldestInvcTranID             VARCHAR(13),
@oOldestInvcAmt                FLOAT ,
@oLastFinChrgKey               INT,
@oLastPmtKey                 INT ,
@oOldestInvcKey                INT,
@oCreditLimitAgeCat            SMALLINT

EXEC dbo.sparCreditCheck
196809 , –@iCustKey
801, –@iTranType
1314, –@iTranKey
null, –@iPmtKey
0, –@iTranAmt
0, –@iDownPayAmt
0, –@iOverrideAmt
0, –@iCallingTask
@oCurBal OUTPUT,
@oAdjTranAmt  OUTPUT,
@oPendInvcs  OUTPUT,
@oSalesOrds  OUTPUT,
@oUnapprovedSalesOrds OUTPUT,
@oPendPmts  OUTPUT,
@oCreditLimit  OUTPUT,
@oBalOver1    OUTPUT,
@oBalOver2    OUTPUT,
@oBalOver3    OUTPUT,
@oBalOver4    OUTPUT,
@oCurAgeBal   OUTPUT,
@oFutAgeBal   OUTPUT,
@oCurAdjBal   OUTPUT,
@oAvailCredit OUTPUT,
@oNACurBal    OUTPUT,
@oNAPendInvcs OUTPUT,
@oNASalesOrds  OUTPUT,
@oNAPendPmts  OUTPUT,
@oNACreditLimit OUTPUT,
@oNACurAdjBal   OUTPUT,
@oNAAvailCredit OUTPUT,
@oCreditStatus  OUTPUT,
@oCustCreditLimitFailure OUTPUT,
@oCustAgingCreditFailure OUTPUT,
@oNACreditLimitFailure   OUTPUT,
@oNAAcctID OUTPUT,
@oNAAcctDesc OUTPUT,
@oNAAcctLevel OUTPUT,
@oLastFinChrgDate OUTPUT,
@oLastFinChrgAmt OUTPUT,
@oLastPmtDate OUTPUT,
@oLastPmtTranID OUTPUT,
@oLastPmtAmt OUTPUT,
@oOldestInvcDate OUTPUT,
@oOldestInvcTranID OUTPUT,
@oOldestInvcAmt OUTPUT,
@oLastFinChrgKey OUTPUT,
@oLastPmtKey    OUTPUT,
@oOldestInvcKey  OUTPUT,
@oCreditLimitAgeCat OUTPUT

SELECT
@oCreditStatus CredStatus,
@oCustCreditLimitFailure CreditLimitFailure,
@oCustAgingCreditFailure AgingCreditFailure,
@oCreditLimit Creditlimit,
@oAvailCredit Availcredit

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!