Hybrid – SOP Batch Splitting

Two weeks ago, I began working on a Microsoft CRM to Microsoft Dynamics GP integration project. Sales orders initiated in CRM and are transferred to GP once they are submitted in CRM. Once the orders got to GP they must be placed on automatic hold for document verification. However, once the hold was removed, the client wanted to be able to transfer all verified orders in bulk… so far good! The problem though was, when orders were transferred to invoices, the invoices remained mixed with the orders that did not get transferred in the same batch. This presented a control issue for the client as the staff performing the documentation is not the same staff transferring the orders.

Last year I posted the article Moving SOP Transactions from One Batch to Another. This article demonstrated a SQL script that would allow a database administrator to move SOP documents from one batch to another. The script in that article forms the basis for this customization. However, to make it more user friendly, I decided to provide the client with a way of running it from the Microsoft Dynamics GP interface by adding a Split button with Modifier to the SOP Batch Entry window. The following is a modified version of the SOP Batch Entry window with the Split button.

The Split button in turn, prompts the user for a new batch that will host the invoices. Upon entering the new batch number, the VBA script instantiates the UserInfo object to create an ADO connection. The connection executes a stored procedure to move the invoices into the batch entered by the user.

While the customization has been designed specifically to move invoices into a new batch, it can be adapted to move same SOP document types out of a batch containing other SOP document types.

Downloads

SOP Split Batch – Click here to download package file and SQL stored procedure.

Until next post!

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

SQL – Retrieving the most recent receipt info for an item

It’s been quite a while since I have posted a SQL script, and it’s funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question on the Microsoft Dynamics GP public newsgroup, as follows:

“We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
unsuccessful.”

At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as simple. Luckily enough, we can take advatage of the latest T-SQL enhancements to use ranks and partitions on sets to deliver the requested query, as follows:

-- Created by: Mariano Gomez, MVPSELECT A.ITEMNMBR , B.ITEMDESC , B.CURRCOST , ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate , ISNULL(R.UNITCOST, 0.00) AS UNITCOST , ISNULL(R.VENDNAME, '') AS VENDNAMEFROM IV00102 A LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR) LEFT OUTER JOIN (  SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (   SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER    (PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK   FROM POP30310 C    LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)  ) Receipts WHERE RECEIPT_RANK = 1 ) R ON (A.ITEMNMBR = R.ITEMNMBR)WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

In Microsoft Dynamics GP v10, the POP Receipt history tables also hold In-Transit Transfer transactions. If you are looking to retrieve strictly vendor receipts, you may change the query as follows:

-- Created by: Mariano Gomez, MVPSELECT A.ITEMNMBR  , B.ITEMDESC  , B.CURRCOST  , ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate  , ISNULL(R.UNITCOST, 0.00) AS UNITCOST  , ISNULL(R.VENDNAME, '') AS VENDNAMEFROM IV00102 A LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR) LEFT OUTER JOIN (  SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (   SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER     (PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK    FROM POP30310 C     LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)    WHERE D.POPTYPE  8   ) Receipts WHERE RECEIPT_RANK = 1  ) R ON (A.ITEMNMBR = R.ITEMNMBR)WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

Hope you find this query useful.

Until next post,

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

"Unhandled Script Exception: Set Precision.." when selecting item in Item Maintenance window

Background

The user was attempting to select an item in the Item Maintenance window and received the error:

Unhandled script exception:
set precision passed bad precision value -1.

EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE
SCRIPT_CMD_SETPRECISION

The error only happened for a handful of items in a catalog of a few thousands.

Solution

Dynamics will attempt to set the decimal precision for the currency and quantity fields on the Item Maintenance window based on the number of decimal places for the functional currency (this is the default) or based on the number of decimal places used to define the item list price under the Item Currency Maintenance window; and in the case of the quantities, the number of decimal places for the quantities. To fix the issue, you will want to look at the columns DECPLQTY and DECPLCUR for any values at or below zero which would sure render an incorrect precision value when Dynamics GP attempts to set the value of the drop-down list on the screen. In this case, a zero value in any of these columns will result in a precision value of -1, when the system attempts to set the drop-down list value.

The following query will identify any suspect records causing the problem:

SELECT * FROM IV00101 WHERE (DECPLQTY <= 0) OR (DECPLCUR <= 0);

SELECT * FROM IV00105 WHERE (DECPLCUR <= 0);

To fix, you will want to at least set them to the values of your functional currency, for example:

UPDATE IV00101 SET DECPLQTY = 3 — 2 decimals
WHERE (DECPLQTY <= 0);

UPDATE IV00101 SET DECPLCUR = 3 — 2 decimals
WHERE (DECPLCUR <= 0);

UPDATE IV00105 SET DECPLCUR = 3 — 2 decimals
WHERE (DECPLCUR <= 0);

Until next post!

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

More on OLE attachments and record notes

Microsoft’s David Musgrave takes on OLE attachments and record notes based on a support case he tackled recently. David reviews the process use by Dynamics GP to retrieve the next note index value and how the system associates the note to a record. He explores the OLE container and how the file names are encoded along with the path.

Furthermore, he provides some Dexterity and SQL code to demonstrate how you would retrieve the hexadecimal value used to compose the notes file name. Be sure to read David’s article to get the complete scoop.

Related articles

All About the Dexterity OLE Container – click here
Understanding Notes and the Note Index Field @ Developing for Dynamics GP, click here

Until next post!

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

Fixing Microsoft Dynamics GP Toolbar erratic behavior

A few months aback, I blogged about a case where the Microsoft Dynamics GP toolbar menu would reorganized itself erratically even with the Lock Toolbar option activated. Back then, I alluded to the Script Debugging Tool being activated via the DEX.INI. Since then, I have also found that this may (or may not) be the case when returning from Modifier or Report Writer.

If you do happen to experience this issue and do not use the Script Debugging Tool, you can run the following T-SQL script in SQL Server Management Studio to correct the problem:

USE DYNAMICS;GO

UPDATE SY07121 SET Visible = 0, ROWNMBR = 1, RowSequence = 1 WHERE CmdBarDictID = 3830   AND CmdBarFormID = 22006   AND CmdBarWindowID = 22003   AND USERID  ''

UPDATE SY07121  SET Visible = 1, ROWNMBR = 1, RowSequence = 4  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 294    AND USERID  ''

UPDATE SY07121  SET Visible = 1, ROWNMBR = 1, RowSequence = 3  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 295    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 231    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 223    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 230    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 228    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 225    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 224    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 226    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 227    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 192    AND USERID  ''GO

The above script is applicable ONLY to Microsoft Dynamics GP v10. Make a backup of your DYNAMICS database before executing this script.

If the above fails to correct the issue, then you will need to remove the records for the user in question:

USE DYNAMICS;GODELETE FROM SY07121 WHERE USERID = 'theUserID';

The scripts were originally provided by Sarah Purdy on the Dynamics GP Partner Technical Community and must be executed without any users in the system.

Until next post!

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

"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. “End of Month + Net Days” (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 — or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM PM20000 A    INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM RM20101 A    INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)      AND (A.RMDTYPAL = I.RMDTYPAL)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:

NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

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

How to calculate the number of days a vendor check takes to clear?

I am always amazed at the things you can do with Microsoft Dynamics GP. Just recently, a user pointed out that the Days for Checks to Clear field on the Vendor Credit Summary window was always blank and that they needed this field to update automatically.

According to the help file, the field is defined as “View or change the average number of days that a check you issue to the vendor usually takes to clear your bank.“, in other words, you can manually update it if you need to track this data.

The user also pointed out that they performed daily bank reconciles and that the Cleared Date column in the CM Transaction (dbo.CM20200) table was reflective of the actual check cleared date in the bank and that they would like to use this field to update the Days for Checks to Clear field in the Vendor Credit Summary window. After all, the date value of the Cleared Date column only gets updated when the check is marked as cleared during a checkbook reconciliation process and does not necessarily reflect the actual date the check cleared the bank, but with daily bank reconciles this should not be an issue, because the would be one of the same.

So, if you perform daily bank reconciliations and would like to know the average days a vendor check takes to clear your bank, you could use the following T-SQL query to update this column value:

-- Created by Mariano Gomez, MVPWITH AVGCTE(VENDORID, AVGDAYS) AS (  SELECT A.CMLinkID, AVG(DATEDIFF(DD, A.TRXDATE, A.clearedate)) AS AVGDAYS  FROM CM20200 A  GROUP BY A.CMLinkID)UPDATE PM00201 SET DYCHTCLR = AVGDAYSFROM PM00201 INNER JOIN AVGCTE ON (PM00201.VENDORID = AVGCTE.VENDORID)

Now what to do with the script? You can schedule it as part of a SQL Server job that will run daily to update this value for you! This will allow you to have accurate average days for each vendor and in turn will allow you to make better purchasing decisions to preserve your hard earned cash.

Until next post!

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

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

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

Fixing Microsoft Dynamics GP and Illegal Characters error messages

By now you probably read David Musgrave’s series on Microsoft Dynamics GP and Illegal Characters and have come to realize that the single quote character is perhaps one of the biggest culprits in the errors you are receiving when running reports such as your age trial balances in both receivables and payables. It’s enough for the single quote character to be in your customer ID, vendor ID, or even document numbers to cause some error while running a report or inquiring a transaction.

The following is an actual case reported by a user on the Dynamics GP newsgroup when running the Payables Historical Aged Trial Balance report:

The stored procedure pmHistoricalAgedTrialBalance returned the following
result: DMBS: -127, Microsoft Dynamics GP: 0.

After some research the user realized they had a few document numbers that contained the illegal single quote character, for example INV’23002. Note that this case is specific to the document number.

So, what to do if you are a victim of the single quotes?

1) You will need to first identify the tables possibly affected. In particular, you will need to search all tables in the database that may contain the column storing the data — in the case of the example, DOCNUMBR. For this you can use a script I published before here. You may also use the script provided by David Musgrave on Part 2 of his series, which would allow you to search through all your company databases if experiencing the issue in more than one company.

2) You will then need to identify the specific records affected by a single quote. Once the tables have been identified, you can do the following:

SELECT * FROM PM00400 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM10201 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM10300 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM10400 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM20000 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM30200 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM50100 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM80200 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM GL20000 WHERE ORDOCNUM LIKE '%''%'

NOTE: The GL20000 and GL30000 tables will store this information in the ORDOCNUM column.

3) Once you identify the document numbers you will need to have a plan to correct them. Your plan should include making sure the new resulting document number is not going to create a duplicate situation with an already existing record.

4) Update the affected records.

UPDATE PM00400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM10201 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM10300 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM10400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM20000 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM30200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM50100 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM80200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE GL20000 SET ORDOCNUM = 'NEW_DOC_NUM' WHERE ORDOCNUM = 'XY''Z'

Note the use of a double single quote to format the document number string properly and avoid an early string termination.

Since this situation can present itself in other modules, be sure to identify the specific module affected and the tables that may be impacted based on the data column storing the informatin. Hope this helps and complements the articles and information on Developing for Dynamics GP.

Until next post!

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

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 NULLDROP PROCEDURE uspUpdateVendor1099Box;GO


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


SET @RET_VAL = 0


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


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


GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRPGO

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, MVPConst adUseClient = 3Const adCmdStoredProc = 4Const adSmallInt = 2Const adInteger = 3Const adChar = 129Const adParamInput = 1Const adExecuteNoRecords = 128


Dim oCnDim oCmdDim pVendorID, pTen99BoxNumber, pRetVal


set oCn = CreateObject("ADODB.Connection")oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyIDoCn.CursorLocation = adUseClientGPConnection.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


  .ExecuteEnd With


If pRetVal.Value  0 Then' Error handling here, you can choose to write to IM's logEnd 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, MVPSetVariable "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