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 SMALLINTEXEC 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 OUTPUTSELECT
@oCreditStatus CredStatus,
@oCustCreditLimitFailure CreditLimitFailure,
@oCustAgingCreditFailure AgingCreditFailure,
@oCreditLimit Creditlimit,
@oAvailCredit Availcredit
