How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.



Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box

-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULL
DROP PROCEDURE uspUpdateVendor1099Box;
GO

CREATE PROCEDURE dbo.uspUpdateVendor1099Box
@VENDORID CHAR(21) = NULL
,@TEN99BOXNUMBER SMALLINT = NULL
,@RET_VAL INT OUTPUT
AS

SET @RET_VAL = 0

IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)
BEGIN
  SELECT @RET_VAL = 0;
  RETURN
END

BEGIN TRY
  UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =
  @VENDORID
END TRY
BEGIN CATCH
  SELECT @RET_VAL = ERROR_NUMBER()
END CATCH;
GO

GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRP
GO

Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document

' Created by Mariano Gomez, MVP
Const adUseClient = 3
Const adCmdStoredProc = 4
Const adSmallInt = 2
Const adInteger = 3
Const adChar = 129
Const adParamInput = 1
Const adExecuteNoRecords = 128

Dim oCn
Dim oCmd
Dim pVendorID, pTen99BoxNumber, pRetVal

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

With oCmd
  .ActiveConnection = oCn
  .CommandType = adCmdStoredProc or adExecuteNoRecords
  .CommandText = "dbo.uspUpdateVendor1099Box"


  'Set parameters
  set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _
     GetVariable("gVendorID"))
  .Parameter.Append pVendorID

  set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _
     adParamInput, 8, GetVariable("gBoxNumber"))
  .Parameter.Append pTen99BoxNumber

  set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)
  .Parameter.Append pRetVal

  .Execute
End With

If pRetVal.Value <> 0 Then
' Error handling here, you can choose to write to IM's log
End If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script -- we will no longer map the vendor ID since it's necessary to use a field script.

Vendor ID field script

' Created by Mariano Gomez, MVP
SetVariable "gVendorID", SourceFields("someSource.Vendor")
SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")
CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don't get discourage if you don't see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/