Support Debugging Tool Customization #10 – Credit Limit Check Skip in Receivables Transaction Entry (using SQL Execute in SDT)

veeyeskay | 11/04/2011 | 5 Comments

Recently there was a support request in the forum,

http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/thread/a3237a4f-fa4c-4603-8048-94cc0b2af467

where the requirement was as below.

“Is there anything in Accounts Receivable that will allow a certain type of document (whatever that may be) bypass the Credit Limit Check? My client has a customer with a Credit Limit of $5000. They are additionally seeking a donation to a Charity from this customer for $10,000. They do not want the $10,000 invoice to be included in the Credit Limit Calculation. I know they could temporarily adjust the Credit Limit or they could setup a second Customer but is there any other way to get around the credit Limit in this case?”.

As a follow up to the earlier article titled Support Debugging Tool Customization #9 – Credit Limit Check Skip in Receivables Transaction Entry (using Native Dexterity Save Logic), I am going to post the logic of using SQL Execute method to handle the same requirement, in this article.

As you would be aware of, SQL Execute is an interface/API available inside Support Debugging Tool, where we can execute SQL scripts without the need for a database tool. With the help of David Musgrave, I have explored this a little bit and explained how to create parameterized SQL procedures in Support Debugging Tool and execute them dynamically in runtime by passing various values to the parameters.

Version Information

Dynamics GP : 11.0.1752 (SP2)

Support Debugging Tool : 11.00.0015

Screenshots of the Configuration

SQL Script ID #1 – Screenshot

This script is to update the credit limit for the customer as UNLIMITED. The SQL query for this ID is defined below.

--DECLARE @CUSTNMBR VARCHAR(15)
UPDATE  RM00101
SET     CRLMTTYP = 1 ,
        CRLMTAMT = 0 ,
        CRLMTPER = 0 ,
        CRLMTPAM = 0
WHERE   CUSTNMBR = @CUSTNMBR

SQL Script ID #2 – Screenshot

This script is to update the credit limit for the customer as UNLIMITED. The SQL script for this ID is given below.

--DECLARE @CUSTNMBR VARCHAR(15)
--DECLARE @CRLMTTYP INTEGER
--DECLARE @CRLMTAMT NUMERIC(19, 5)
--DECLARE @CRLMTPER INTEGER
--DECLARE @CRLMTPAM NUMERIC(19, 5)
UPDATE  RM00101
SET     CRLMTTYP = @CRLMTTYP ,
        CRLMTAMT = @CRLMTAMT ,
        CRLMTPER = @CRLMTPER ,
        CRLMTPAM = @CRLMTPAM
WHERE   CUSTNMBR = @CUSTNMBR

Note that I have commented the declaration lines in both the scripts above. This is key to note in this instance, since as I had said earlier, we would be calling this procedure with parameters passed at run time. Once these scripts are created, we can call these SQL script ID’s from other scripts in SDT. This will be explained in the subsequent sections.

Trigger #1 – Resource Tab

This is a trigger on the Customer Number Change – After Original on the Receivables Transaction Entry window. This is to capture the customer number and the document type into a parameter. Note the use of “MBS_Param_Set” to set the parameter values. You can use the Helper button to select the coding syntax for setting parameter values.

Trigger #1 – Customization Script

in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;
local string MBS_Parameter;
OUT_Condition = false;
if isopen(form RM_Sales_Entry) then
	if 'Document Type' of window 'RM_Sales_Entry' of form 'RM_Sales_Entry' = 2 then
		if not empty('Customer Number' of window RM_Sales_Entry of form RM_Sales_Entry) then
			call with name "MBS_Param_Set" in dictionary 5261,
				"CustNmbr",
				'Customer Number' of window RM_Sales_Entry of form RM_Sales_Entry;
			call with name "MBS_Param_Set" in dictionary 5261,
				"RMDocTypeAll",
				str('Document Type' of window 'RM_Sales_Entry' of form 'RM_Sales_Entry');
		end if;
	end if;
end if;

Trigger #2 – Resource Tab

This is a trigger on the Form Level Procedure for Credit Limit Check – Before Original. This is to capture the existing credit limit information into parameters in SDT and update the credit limit as UNLIMITED for the specific customer (which was retrieved from the parameter value captured earlier). Note the use of “MBS_Param_Get” to retrieve the parameter values. You can use the Helper button to select the coding syntax for retrieving parameter values.

Also, note that I have helper functions for getting values from table fields using the Helper button. I have used this helper function to get the Credit Limit Type, Credit Limit Amount, Credit Limit Period and Credit Limit Period Amount and store them in parameters.

And then I call the SQL script ID which I have created above to update the credit limit for the customer as UNLIMITED. For this, I first call the procedure to load the SQL Script ID (i.e.) “MBS_Script_Load_SQL”, using the Helper functions. This procedure basically retrieves the SQL script for the ID into a text field. We then append append the parameter declarations and the values passed to these parameters before the query which is retrieved in the text variable as shown below. Then the final SQL script is executed using the “MBS_SQL_Check_Exists” procedure as shown below.

Trigger #2 – Customization Script

out 	boolean 	OUT_Condition;
local 	text 		MBS_Text_Field;
local 	integer 	MBS_Status;
 
local 	string		ls_CustNmbr;
local	string		ls_DocType;
local	integer		li_DocType;
local	text		lt_ParamQuery;
local 	string 		ls_Result;
local 	integer 	li_Pos;
local 	integer 	li_CredLimitType;
local 	currency 	lc_CredLimitAmt;
local 	integer 	li_CredLimitPeriod;
local 	currency 	lc_CredLimitPeriodAmt;
 
OUT_Condition = false;
 
if true then { Insert Condition Here }
	call with name "MBS_Param_Get" in dictionary 5261, "CustNmbr", ls_CustNmbr;
	call with name "MBS_Param_Get" in dictionary 5261, "RMDocTypeAll", ls_DocType;
	set li_DocType to integer(ls_DocType);
 
	if li_DocType = 2 and not empty(ls_CustNmbr) then
 
		{To get the current Credit Limit Type}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Type'" {Field},
			li_CredLimitType, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitType", str(li_CredLimitType);
 
		{To get the current Credit Limit Amount}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Amount'" {Field},
			lc_CredLimitAmt, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitAmt", str(lc_CredLimitAmt);
 
		{To get the current Credit Limit Period}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Period'" {Field},
			li_CredLimitPeriod, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitPeriod", str(li_CredLimitPeriod);
 
		{To get the current Credit Limit Period Amount}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Period Amount'" {Field},
			lc_CredLimitPeriodAmt, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitPeriodAmt", str(lc_CredLimitPeriodAmt);
 
		if li_CredLimitType <> 1 then
			clear lt_ParamQuery;
			{Load the SQL Script ID into a text variable}
			call with name "MBS_Script_Load_SQL" in dictionary 5261,
				"CREDLIMITCLEAR", MBS_Text_Field;
			{Set the variable declaration and values to the variables at run time}
			set lt_ParamQuery to lt_ParamQuery + "DECLARE @CUSTNMBR VARCHAR(15);" + char(13);
			set lt_ParamQuery to lt_ParamQuery + "SET @CUSTNMBR = '" + ls_CustNmbr + "';" + char(13);
			{Append the variable declaration string before the actual SQL script fetched from the SQL Script ID}
			set MBS_Text_Field to lt_ParamQuery + MBS_Text_Field;
			{Execute the SQL Script}
			call with name "MBS_SQL_Check_Exists" in dictionary 5261,
				MBS_Text_Field, false {Return Data}, false {Show Names}, MBS_Status;
 
		end if;
	end if;
	OUT_Condition = true;
end if;

Trigger #3 – Resource Tab

This is a trigger on the Form Level Procedure for Credit Limit Check – After Original. This is to reset the credit limit information back to the original values for the selected customer. Note the use of “MBS_Param_DelAll” procedure to delete any parameters which have been used in the process. You can use the Helper button to select the coding syntax for deleting parameter values.

Trigger #3 – Customization Script

OUT BOOLEAN OUT_Condition;
LOCAL text MBS_Text_Field;
LOCAL INTEGER MBS_Status;
 
LOCAL 	string		ls_CustNmbr;
LOCAL	string		ls_DocType;
LOCAL	string		ls_CredLimitType;
LOCAL	string		ls_CredLimitAmt;
LOCAL	string		ls_CredLimitPeriod;
LOCAL	string		ls_CredLimitPeriodAmt;
LOCAL	INTEGER		li_DocType;
LOCAL	INTEGER		li_CredLimitType;
LOCAL	currency	lc_CredLimitAmt;
LOCAL	INTEGER		li_CredLimitPeriod;
LOCAL	currency	lc_CredLimitPeriodAmt;
LOCAL	text		lt_ParamQuery;
 
OUT_Condition = FALSE;
 
IF TRUE THEN { INSERT Condition Here }
	{GET the parameter VALUES}
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CustNmbr", ls_CustNmbr;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "RMDocTypeAll", ls_DocType;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitType", ls_CredLimitType;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitAmt", ls_CredLimitAmt;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitPeriod", ls_CredLimitPeriod;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitPeriodAmt", ls_CredLimitPeriodAmt;
 
	SET li_DocType TO INTEGER(ls_DocType);
	SET li_CredLimitType TO INTEGER(ls_CredLimitType);
	SET lc_CredLimitAmt TO VALUE(ls_CredLimitAmt);
	SET li_CredLimitPeriod TO INTEGER(ls_CredLimitPeriod);
	SET lc_CredLimitPeriodAmt TO VALUE(ls_CredLimitPeriodAmt);
 
	IF li_DocType = 2 AND NOT empty(ls_CustNmbr) THEN
		clear lt_ParamQuery;
		{LOAD the SQL Script ID}
		CALL WITH name "MBS_Script_Load_SQL" IN dictionary 5261,
			"CREDLIMITRESET", MBS_Text_Field;
		{Setting the dynamic parameter query string}
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CUSTNMBR VARCHAR(15);" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTTYP INTEGER;" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTAMT NUMERIC(19, 5);" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTPER INTEGER;" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTPAM NUMERIC(19, 5);" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CUSTNMBR = '" + ls_CustNmbr + "';" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTTYP = " + str(li_CredLimitType) + ";" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTAMT = " + str(lc_CredLimitAmt) + ";" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTPER = " + str(li_CredLimitPeriod) + ";" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTPAM = " + str(lc_CredLimitPeriodAmt) + ";" + CHAR(13);
		{Appending the dynamic parameter query string TO the actual query}
		SET MBS_Text_Field TO lt_ParamQuery + MBS_Text_Field;
		{EXECUTE the SQL script}
		CALL WITH name "MBS_SQL_Check_Exists" IN dictionary 5261,
			MBS_Text_Field, FALSE {RETURN DATA}, FALSE {SHOW Names}, MBS_Status;
	END IF;
	CALL WITH name "MBS_Param_DelAll" IN dictionary 5261;
	OUT_Condition = TRUE;
END IF;

Note: The earlier post was done using Dexterity code logic and this version was done using SQL Execute. It does not make a difference if either option is chosen. We have just provided an insight into this functionality and the use of SQL Execute in Support Debugging Tool. This type is generally very useful when you want to update tables which are not GP related, since the other options will not be viable to update non GP tables in SQL. As said earlier, the method to be used will differ on a case to case basis, and the appropriate choice can be used as and when needed. Thanks again to David, for guiding me through each step in utilizing Support Debugging Tool to help the community.

Configuration File Location

You can download the configuration for this requirement here.

Reference

Take a look at the article below which summarizes the usage of Support Debugging Tool with some real life examples. Great compilation by David! http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/08/05/using-the-support-debugging-tool-with-real-life-examples.aspx

Hope this helps the community…

Until next post!

Rating: 0.0/10 (0 votes cast)

Rating: 0 (from 0 votes)

Tags: credit, credit limit bypass, credit limit check, credit-limit, customer, customer-number, debit memos, document-type, no credit, receivables, sql execute, unlimited credit

Category: Accounts Receivables, Customizations, Dynamics, Great Plains, Support Debugging Tool

About the Author (Author Profile)

Pingback: Support Debugging Tool Customization #10 – Credit Limit Check Skip in Receivables Transaction Entry (using SQL Execute in SDT) | Interesting Findings & Knowledge Sharing - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community
Pingback: Support Debugging Tool Customization #10 – Credit Limit Check Skip in Receivables Transaction Entry (using SQL Execute in SDT) | Interesting Findings & Knowledge Sharing - DynamicAccounting.net - GP Technical Blogs - Telligent
Pingback: Everything Dynamics GP #44 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community
jivtesh singh

Great post again!

VA:F [1.9.22_1171]
Please wait while your rating is cast...
Rating: 0.0/5 (0 votes cast)
VA:F [1.9.22_1171]
Rating: 0 (from 0 votes)
- Sivakumar Venkataraman
  
  Thanks buddy!
  
  VA:F [1.9.22_1171]
  Please wait while your rating is cast...
  Rating: 0.0/5 (0 votes cast)
  VA:F [1.9.22_1171]
  Rating: 0 (from 0 votes)

M	T	W	T	F	S	S
« Oct				Dec »
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

Support Debugging Tool Customization #10 – Credit Limit Check Skip in Receivables Transaction Entry (using SQL Execute in SDT)

About the Author (Author Profile)

Subscribe

Interesting Links

Blog Rating

Rating

Views

Polls

Visitors Map

Administration