#GPPT Adding Vendor Item Number to SOP Documents

In my previous post, RW: Using the rw_TableLineString() and rw_TableLineCurrency() Report Writer functions, I mentioned the six report writer functions which can be used by developers to get custom data onto reports.

GP Power Tools takes advantage of these functions to allow the creation of custom Report Writer functions. In fact, this custom report writer function feature has been available since the release of the Support Debugging Tool build 11 in June 2009.

This article covers how to display the Vendor Item Number and Vendor Item Description for an item on the SOP Blank Picking Ticket Order Entered report. This information can never be added to a report using standard technques as the table relationship would require a constant value when defining the relationship and this is not supported by the Report Writer.

It would be possible to use Visual Basic for Applications (VBA) with ActiveX Data Objects (ADO) to connect to the SQL Server and run a query, but this would not be future proof as it is not compatible with the Web Client.

To obtain this information via the user interface, we need to know the Item Number and the Site ID from the Sales transaction line. Once we have this information we can look at the Item Quantities Maintanance window to get the Primary Vendor ID.

Now we have the Item and the Primary Vendor ID for the site, we can look up the Vendor Item Number and Description on the Item Vendors Maintenance window.

We can look at this from a SQL Query perspective using GP Power Tools’ SQL Execute Setup with a Parameter List to ask for the Item Number and Site ID.

The SQL Script is below. Note the placeholders for the parameters from the Parameter List.

select V.ITEMNMBR, V.VENDORID, V.VNDITNUM, V.VNDITDSC --, V.ITMVNDTY, Q.RCRDTYPE
from IV00103 V
join IV00102 Q on Q.PRIMVNDR = V.VENDORID and Q.ITEMNMBR = V.ITEMNMBR
where Q.LOCNCODE = ''/*%02%*/ and Q.ITEMNMBR = ''/*%01%*/
     and Q.RCRDTYPE = 2 -- and V.ITMVNDTY = 1

The above script and parameter list will be included with the attachment at the bottom of this article and is included as it is really handy to confirm that the Vendor Item Number and Description have been set up correctly.

---

Now to get the data onto the report.

The first step is to use GP Power Tools’ Runtime Execute Setup to create the custom report writer function. The code for this example will be imported from the file attached to the bottom of this article.

When you select the Script Purpose as 0: rw_TableLineString(), the template script will be added to handle all the parameter passing. All you need to do is use the parameter variables passed in to obtain the data you want and place the result into the parameter variable being passed out.

{ Add your code below here }

clear table SOP_LINE_WORK; 
'SOP Number' of table SOP_LINE_WORK = MBS_Number;
'SOP Type' of table SOP_LINE_WORK = MBS_Type;
'Line Item Sequence' of table SOP_LINE_WORK = MBS_SequenceOne;
'Component Sequence' of table SOP_LINE_WORK = MBS_SequenceTwo;
get table SOP_LINE_WORK;
if err() = OKAY then
	clear table IV_Item_MSTR_QTYS;
	'Item Number' of table IV_Item_MSTR_QTYS = 'Item Number' of table SOP_LINE_WORK;
	'Record Type' of table IV_Item_MSTR_QTYS = 2;
	'Location Code' of table IV_Item_MSTR_QTYS = 'Location Code' of table SOP_LINE_WORK;
	get table IV_Item_MSTR_QTYS;
	if err() = OKAY then
		clear table IV_Item_MSTR_VNDR;
		'Item Number' of table IV_Item_MSTR_VNDR = 'Item Number' of table IV_Item_MSTR_QTYS;
{		'Item Vendor Type' of table IV_Item_MSTR_VNDR = 1;
}		'Vendor ID' of table IV_Item_MSTR_VNDR = 'Primary Vendor' of table IV_Item_MSTR_QTYS;
		get table IV_Item_MSTR_VNDR by number 2;
		if err() = OKAY then
			case MBS_Control
				in [1]
					MBS_TableLineString = 'Vendor Item Number' of table IV_Item_MSTR_VNDR;
				in [2]
					MBS_TableLineString = 'Vendor Item Description' of table IV_Item_MSTR_VNDR;
				else
			end case;
		end if;	
	end if;
end if;

{ Add your code above here }

The script above will return either the Vendor Item Number or the Vendor Item Description field based on the MBS_Control variable passed in.

The next step is to add the SOP Blank Picking Ticket Order Entered report added to the Report Writer and to adjust the Alternate / Modified Forms and Report ID to point to the modified version of the report.

Then we can start modifying the report:

• Create the (c) Line Item Sequence and (c) Component Sequence calculated fields as discussed on the blog post: RW: Using the rw_TableLineString() and rw_TableLineCurrency() Report Writer functions
• Create a string calculated field for (c) Vendor Item Number.

Below is a description of the components of the Calculated Expression from the Calculated Field Definition. They are listed vertically so I can explain what each component means.

Component	Meaning
FUNCTION_SCRIPT(	Call a User Defined RW function
rw_TableLineString	Name of the function to call
5261	Dictionary ID for GP Power Tools
“RW_VENDOR_ITEM”	Script ID from Runtime Execute
SOP_LINE_WORK.SOP Number	SOP Number from transaction line
SOP_LINE_WORK.SOP Type	SOP Type from transaction line
(c) Line Item Sequence	Line Item Sequence as currency
(c) Component Sequence	Component Sequence as currency
1	Data element to return
)	End User Defined RW function call

Note the Dictionary ID, Script ID and Data element are string and integer constants as appropriate.

• Create another string calculated field (c) Vendor Item Description with the only difference being that the Data element to return is set to 2.
• In the report layout expand the height of the Body section to make space for an additional line.
• Drag the (c) Vendor Item Number and (c) Vendor Item Description fields into the space below and slightly to the right of each of the existing Item Number and Item Description fields.
• Make the formatting of the new fields match the existing fields; Click on the existing Item Description field. Then shift-click on the (c) Vendor Item Number and (c) Vendor Item Description fields. Now press Ctrl-D and once the Drawing Options window is displayed press Enter. Now the fields all match the first selected field.
• While the fields are still selected, on the Toolbox window, select the Arrange Tab and click the Size to Shortest button.
• Click on the Item Number field and shift-click on the (c) Vendor Item Number field. Use the Arrange tab again to Align to Left button and then the Size to Narrowest or Size to Widest buttons as appropriate to match the existing field width.
• Use the keyboard up and down arrows to move the field so it is vertically centered in the space available.
• Repeat sizing and positioning for the Item Description and (c) Vendor Item Description fields. You can use Align to Top or Align to Bottom Arrange tools to get the two added fields to the same vertical position.

Below is the completed report layout with the new fields highlighted:

• Finally close and save the report layout and return to Microsoft Dynamics GP (saving your changes, if asked).

Testing the report shows it is working perfectly with the Vendor Item Number and Description being displayed on the report.

For more information and examples see:

• GP Power Tools Portal
• RW: Using the rw_TableLineString() and rw_TableLineCurrency() Report Writer functions
• How to display more than 80 characters of an Extender Long String field in reports using the Support Debugging Tool
• Update: Microsoft Dynamics GP 2013 R2 updates to Report Writer Functions – RW_CoAddrIDInfo broken
• How to add Item Category Long Descriptions to reports using the Support Debugging Tool
• Getting data from ISV tables onto reports without needing to create alternate reports

Hope this information and example is useful and shows how you can use Dexterity scripting to obtain any information you need to pull into a report

David

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

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

Advertisements