#GPPT Adding data from any SQL table to SOP Documents

Following on from my recent article, #GPPT Adding Vendor Item Number to SOP Documents, I thought it would be worth showing how we can use GP Power Tools custom Report Writer RW functions with a SQL Query to obtain any data available from any table on the SQL Server.

To make this happen we are going to combine the Runtime Execute Setup custom RW function code with a SQL script created in SQL Execute Setup. Note that these techniques, which have been available since June 2009, are completely Web Client compatible which makes them even more valuable now.

This example is going to be based on SOP Documents and getting string data from any table that can be joined to the SOP_HDR_WORK (Sales Transaction Work) table or the SOP_LINE_WORK (Sales Transaction Amounts Work) table. [Edit[ See note about bottom of the article about performance of this method.

The first stage is to create a SQL Query that returns the primary key fields from the primary table and the data columns you are interested in pulling into the report. You can reference the tables and columns using any combination of Dexterity Technical Names (surrounded by braces { } ) and/or the SQL Physical Names.

For the Header example, let’s grab the Short Name and Statement Name from the Customer Master table.  Below are the settings on the SQL Execute Setup window (using Dexterity Names and the Alias keyword).

Here is the script after the Dexterity Names are converted. This script can be executed directly in SQL.

select H.SOPNUMBE, H.SOPTYPE
	, H.CUSTNMBR, H.CUSTNAME
	, C.SHRTNAME, C.STMTNAME
from SOP10100 H
join RM00101 C on C.CUSTNMBR = H.CUSTNMBR

For the Line example, let’s grab the Vendor Item Number and Vendor Item Description like we did in the previous article.  Below are the settings on the SQL Execute Setup window (using Dexterity Names and the Alias keyword).

Here is the script after the Dexterity Names are converted. This script can be executed directly in SQL.

select L.SOPNUMBE, L.SOPTYPE, L.LNITMSEQ, L.CMPNTSEQ
	, Q.LOCNCODE, Q.PRIMVNDR
	, L.ITEMNMBR, L.ITEMDESC
	, V.VNDITNUM, V.VNDITDSC
from SOP10200 L
join IV00102 Q on Q.ITEMNMBR = L.ITEMNMBR
	and Q.LOCNCODE = L.LOCNCODE and Q.RCRDTYPE = 2
join IV00103 V on V.ITEMNMBR = Q.ITEMNMBR
	and V.VENDORID = Q.PRIMVNDR
where V.VNDITNUM  ''

Now that we have the SQL Queries that provide the data we want, we can use the Runtime Execute Setup window to create the custom report writer functions.

The code we will add to the automatically generated template code will load the SQL script created in the SQL Execute Setup and use that code as a derived table (sub query) so that we can select the one field we want and add the where clause to limit to a single row in the SOP primary table.

Note: This time we cannot use the Dexterity Technical Names (surrounded by braces) as the Runtime Execute Setup window does not have the convert references feature.

Below are the settings for Header example using the rw_TableHeaderString() custom report writer function from the Runtime Execute Setup window:

Here is an excerpt from the script showing the added custom code:

{ Add your code below here }

call with name "MBS_Script_Load_SQL" in dictionary 5261,
	"RW_SQL_SOP_HDR", MBS_Text_Field;

case MBS_Control
	in [1]
		MBS_Text_Field = "select SHRTNAME from (" + MBS_Text_Field + ") A";
	in [2]
		MBS_Text_Field = "select STMTNAME from (" + MBS_Text_Field + ") A";
	else
		abort script;
end case;
MBS_Text_Field = MBS_Text_Field + " where SOPNUMBE = " + SQL_FormatStrings(MBS_Number);
MBS_Text_Field = MBS_Text_Field + " and SOPTYPE = " + str(integer(MBS_Type));

call with name "MBS_SQL_Check_Exists" in dictionary 5261,
	MBS_Text_Field, true {Return Data}, false {Show Names}, MBS_Status;
case MBS_Status
	in [OKAY]
		MBS_Position = 1;
		MBS_TableHeaderString = trim(Field_ParseText(MBS_Text_Field, 255, MBS_Position));
	in [MISSING]
	else
{		warning MBS_Text_Field;
}end case;

{ Add your code above here }

Below are the settings for Line example using the rw_TableLineString() custom report writer function from the Runtime Execute Setup window:

Here is an excerpt from the script showing the added custom code:

{ Add your code below here }

call with name "MBS_Script_Load_SQL" in dictionary 5261,
	"RW_SQL_SOP_LINE", MBS_Text_Field;

case MBS_Control
	in [1]
		MBS_Text_Field = "select VNDITNUM from (" + MBS_Text_Field + ") A";
	in [2]
		MBS_Text_Field = "select VNDITDSC from (" + MBS_Text_Field + ") A";
	else
		abort script;
end case;
MBS_Text_Field = MBS_Text_Field + " where SOPNUMBE = " + SQL_FormatStrings(MBS_Number);
MBS_Text_Field = MBS_Text_Field + " and SOPTYPE = " + str(integer(MBS_Type));
MBS_Text_Field = MBS_Text_Field + " and LNITMSEQ = " + str(long(MBS_SequenceOne));
MBS_Text_Field = MBS_Text_Field + " and CMPNTSEQ = " + str(long(MBS_SequenceTwo));

call with name "MBS_SQL_Check_Exists" in dictionary 5261,
	MBS_Text_Field, true {Return Data}, false {Show Names}, MBS_Status;
case MBS_Status
	in [OKAY]
		MBS_Position = 1;
		MBS_TableLineString = trim(Field_ParseText(MBS_Text_Field, 255, MBS_Position));
	in [MISSING]
	else
{		warning MBS_Text_Field;
}end case;

{ Add your code above here }

You can use these examples as a framework to base your own custom queries and report writer functions on. You only need to adjust the Dexterity code to change the names of the columns you wish to return based on the SQL queries you are using.

While these examples return string data, you can use exactly the same techniques to return any numeric data as a currency value using the rw_TableHeaderCurrency() and rw_TableLineCurrency() functions.

Attached to the bottom of the article is the Configuration Settings file for all the scripts in the development project.

Also provided in the project file is a testing script which can be used to confirm that the scripts are all working.

The full code in this script just calls the RW functions in the same way the Report Writer would and displays the returned data.

warning (rw_TableLineString(5261, "RW_SQL_SOP_LINE", "STDINV2262", 3, 16384.0, 0, 1) + ", " +
		 rw_TableLineString(5261, "RW_SQL_SOP_LINE", "STDINV2262", 3, 16384.0, 0, 2));

warning (rw_TableHeaderString(5261, "RW_SQL_SOP_HDR", "STDINV2262", 3, 1) + ", " +
		 rw_TableHeaderString(5261, "RW_SQL_SOP_HDR", "STDINV2262", 3, 2));

While these examples are based on the SOP Header and Line tables, you can use the same techniques for any table as long as you can use the parameters available to link to your primary table with a string and an integer field (using the header functions) or a string, an integer and two currency fields (using the line functions).

Hope you find these examples useful.

David

11-Jun-2021: Note that there are possible performance issues using this method for data in the body. The reason this that parsing the returned SQL data takes time and can slow down the report printing. If you can access the same data via Dexterity table commands, it will be much faster.

Download: http://winthropdc.com/ftp/Blog/GP_Power_Tools_Settings_RW_SQL_SOP.dbg.zip

This article was originally posted on http://www.winthropdc.com/blog.