Thursday, May 30, 2013

Negative Payroll Transactions 2013 and Integration Manager 12.0


In order to get Integration Manager to import Negative Pay Code Hourly Transactions in Dynamics GP 2013, a few changes must be made to the integration.

A script must be placed on the “Amount” field in Destination Mapping:


SourceField = SourceFields("PR Trx Temp.Units")
SetVariable "Units", CStr(SourceField * 100)
If SourceField  < 0 Then
            SetVariable "NegativeTRX", "TRUE"
            CurrentField.Value = 9999.99
Else
            CurrentField.Value = SourceField
End If
This script sets the value of the hours for the imported transaction to 9999.99 and saves the original value in a variable.

NOTE: “PR Trx Temp.Units” will need to be set to the Source Field name from your integration.

Next a script must be placed on the “After Document” event within the Integration Properties:

 

NegativeTrx = GetVariable("NegativeTrx")
Units = GetVariable("Units")
 
If NegativeTrx = "TRUE" then
            UpdateStatement = "UPDATE " + GPConnection.GPConnInterCompanyID + ".dbo.UPR10302 SET TRXHRUNT = '" + Units + "' WHERE TRXHRUNT = '999999' AND BACHNUMB = '" + SourceFields("BatchID") + "' "
            Set MyCon = CreateObject("ADODB.Connection")
            MyCon.ConnectionString = "database=" &     GPConnection.GPConnInterCompanyID
            GPConnection.Open(MyCon)
            Call MyCon.Execute(UpdateStatement)
            Call MyCon.Close
            Set MyCon = nothing
End If
 
ClearVariables
This script checks to see if the current record being imported was a negative hours transaction.  If so, it sets the hours back to the original negative amount from the amount previously stored in a variable.