Login / Register  search  syndication  about

          Kris Halsrud's Blog

Kris Halsrud on development and Integration with CRM and Development

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

Tags:
What's This?
  
Bookmark and Share

About Kris Halsrud

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


Related Content
   September MAS License Promotion Announced
Announcing two Sweet September Promotions!
Posted on Sep 10, 2009 by Scott Weber to MAS 500 Product Blog
 
   SalesLogix ERP Link- How the sales order push to MAS determines which warehouse to use
How ERP Link determines warehouse values when posting a Sales Order to MAS 500 from SalesLogix
Posted on Jul 22, 2009 by Kris Halsrud to Kris Halsrud's Blog
 
   MAS 500 Technical Tips
Changing Report Locations and Setting Up SQL Server 2005 System Data Permissions Changing Report Locatio
Posted on Jul 16, 2009 by Brianna Ojard to MAS 500 Product Blog
 
   A Bug in SalesLogix ERP Sync when not running it on the SalesLogix Server
In an earlier post, I reviewed the ERP Sync component of the Sage ERP Link product.  We had a client
Posted on Jul 09, 2009 by Kris Halsrud to Kris Halsrud's Blog
 
   Sage SalesLogix ERP Link Sync
In addition to the client side aspect of ERP Link there is a "server" side component that allow
Posted on Jun 18, 2009 by Kris Halsrud to Kris Halsrud's Blog
 
Comments

 

Twitter Trackbacks for MAS 500 checking a customers credit limit - The Integration Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 MAS 500 checking a customers credit limit - The Integration Blog         [customerfx.com]        on Topsy.com

September 10, 2009 1:56 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register