How to import the Vendor 1099 Box with Integration Manager
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.
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.
-- 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:
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.