Aaron Berquist Rotating Header Image

July 15th, 2011:

Populate Rounding Account On Multicurrency Payables Transactions Created Via eConnect

Jul 15th, 2011
by Aaron Berquist.
4 comments

If you create multicurrency payables (PM) transactions using eConnect, and you are passing in the distributions manually (by setting CREATEDIST to 0), you may have encountered the same issue I encountered today.

When the transaction was created, eConnect also (correctly) added in a ROUND distribution, to account for 0.01 cents worth of FX related rounding on the transaction. Unfortunately, eConnect did not populate the distribution with an account. This struck me as strange – if you manually create a voucher in Dynamics GP and rounding is encountered, a ROUND distribution is created, and the account is populated (with the Rounding Difference Account, which is set up under Tools: Setup: Posting: Posting Accounts).

I should also mention – there is a specific order that should be followed when creating a multicurrency payables document – create the distributions first, then create the header record. Details are in VSTools thread here.

To resolve this issue, I entered some code into the taPMTransactionInsertPost procedure to retrieve the account and populate the distribution. The code snippet is below, or you can download it here.

/****** Object:  StoredProcedure [dbo].[taPMTransactionInsertPost]    Script Date: 07/15/2011 09:48:57 ******/
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[taPMTransactionInsertPost]')
                    AND type IN ( N'P', N'PC' ) )
    DROP PROCEDURE [dbo].[taPMTransactionInsertPost]
GO
 
/****** Object:  StoredProcedure [dbo].[taPMTransactionInsertPost]    Script Date: 07/15/2011 09:48:57 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
CREATE PROCEDURE [dbo].[taPMTransactionInsertPost]
    @I_vBACHNUMB CHAR(15) ,
    @I_vVCHNUMWK CHAR(17) ,
    @I_vVENDORID CHAR(15) ,
    @I_vDOCNUMBR CHAR(20) ,
    @I_vDOCTYPE SMALLINT ,
    @I_vDOCAMNT NUMERIC(19, 5) ,
    @I_vDOCDATE DATETIME ,
    @I_vPSTGDATE DATETIME ,
    @I_vVADCDTRO CHAR(15) ,
    @I_vVADDCDPR CHAR(15) ,
    @I_vPYMTRMID CHAR(20) ,
    @I_vTAXSCHID CHAR(15) ,
    @I_vDUEDATE DATETIME ,
    @I_vDSCDLRAM NUMERIC(19, 5) ,
    @I_vDISCDATE DATETIME ,
    @I_vPRCHAMNT NUMERIC(19, 5) ,
    @I_vCHRGAMNT NUMERIC(19, 5) ,
    @I_vCASHAMNT NUMERIC(19, 5) ,
    @I_vCAMCBKID CHAR(15) ,
    @I_vCDOCNMBR CHAR(20) ,
    @I_vCAMTDATE DATETIME ,
    @I_vCAMPMTNM CHAR(20) ,
    @I_vCHEKAMNT NUMERIC(19, 5) ,
    @I_vCHAMCBID CHAR(15) ,
    @I_vCHEKDATE DATETIME ,
    @I_vCAMPYNBR CHAR(20) ,
    @I_vCRCRDAMT NUMERIC(19, 5) ,
    @I_vCCAMPYNM CHAR(20) ,
    @I_vCHEKNMBR CHAR(20) ,
    @I_vCARDNAME CHAR(15) ,
    @I_vCCRCTNUM CHAR(20) ,
    @I_vCRCARDDT DATETIME ,
    @I_vCHEKBKID CHAR(15) ,
    @I_vTRXDSCRN CHAR(30) ,
    @I_vTRDISAMT NUMERIC(19, 5) ,
    @I_vTAXAMNT NUMERIC(19, 5) ,
    @I_vFRTAMNT NUMERIC(19, 5) ,
    @I_vTEN99AMNT NUMERIC(19, 5) ,
    @I_vMSCCHAMT NUMERIC(19, 5) ,
    @I_vPORDNMBR CHAR(20) ,
    @I_vSHIPMTHD CHAR(15) ,
    @I_vDISAMTAV NUMERIC(19, 5) ,
    @I_vDISTKNAM NUMERIC(19, 5) ,
    @I_vAPDSTKAM NUMERIC(19, 5) ,
    @I_vMDFUSRID CHAR(15) ,
    @I_vPOSTEDDT DATETIME ,
    @I_vPTDUSRID CHAR(15) ,
    @I_vPCHSCHID CHAR(15) ,
    @I_vFRTSCHID CHAR(15) ,
    @I_vMSCSCHID CHAR(15) ,
    @I_vPRCTDISC NUMERIC(19, 2) ,
    @I_vTax_Date DATETIME ,
    @I_vCURNCYID CHAR(15) ,
    @I_vXCHGRATE NUMERIC(19, 7) ,
    @I_vRATETPID CHAR(15) ,
    @I_vEXPNDATE DATETIME ,
    @I_vEXCHDATE DATETIME ,
    @I_vEXGTBDSC CHAR(30) ,
    @I_vEXTBLSRC CHAR(50) ,
    @I_vRATEEXPR SMALLINT ,
    @I_vDYSTINCR SMALLINT ,
    @I_vRATEVARC NUMERIC(19, 7) ,
    @I_vTRXDTDEF SMALLINT ,
    @I_vRTCLCMTD SMALLINT ,
    @I_vPRVDSLMT SMALLINT ,
    @I_vDATELMTS SMALLINT ,
    @I_vTIME1 DATETIME ,
    @I_vBatchCHEKBKID CHAR(15) ,
    @I_vCREATEDIST SMALLINT ,
    @I_vRequesterTrx SMALLINT ,
    @I_vUSRDEFND1 CHAR(50) ,
    @I_vUSRDEFND2 CHAR(50) ,
    @I_vUSRDEFND3 CHAR(50) ,
    @I_vUSRDEFND4 VARCHAR(8000) ,
    @I_vUSRDEFND5 VARCHAR(8000) ,
    @O_iErrorState INT OUTPUT ,
    @oErrString VARCHAR(255) OUTPUT
AS 
 
	/*
	AUTHOR: AARON BERQUIST
	DATE: 07/15/2011
	PURPOSE: POPULATE THE "ROUND" DISTRIBUTION AUTOMATICALLY CREATED WHEN
	A MULTICURRENCY PM TRANSACTION IS CREATED USING eCONNECT.
 
	BACKGROUND: WHEN CREATING A MULTICURRENCY PM TRANSACTION USING eCONNECT,
	IF THE "CREATEDIST" FLAG IS SET TO 0 AND DISTRIBUTIONS ARE BEING PASSED IN MANUALLY,
	THE SYSTEM WILL CORRECTLY CREATE A "ROUND" DISTRIBUTION (DISTTYPE = 16) IN THE PM10100 TABLE.
	UNFORTUNATELY, IT APPEARS THAT eCONNECT WILL NOT POPULATE THE DISTRIBUTION WITH THE ACCOUNT
	FROM THE ROUNDING DIFFERENCE ACCOUNT SETUP. IF YOU ENTER THE SAME TRANSACTION MANUALLY
	THROUGH THE GP APPLICATION, THE ACCOUNT IS AUTOMATICALLY POPULATED.
 
	TESTED ON DYNAMICS GP 10.00.1257.	
 
	*/
    SET nocount ON
    SELECT  @O_iErrorState = 0
 
    DECLARE @ROUNDINDEX INT
 
    --CHECK TO SEE IF THE INTEGRATION CREATED A ROUND ENTRY
    --IF SO, eCONNECT DOES NOT PICK THE DEFAULT ROUNDING ACCOUNT IF YOU CHOOSE TO MANUALLY CREATE DISTRIBUTIONS
    --SO THE POST PROCEDURE WILL READ THE ACCOUNT FROM POSTING ACCOUNT SETUP (TOOLS: SETUP: POSTING: POSTING ACCOUNTS: ROUNDING DIFFERENCE ACCOUNT)
    --AND POPULATE THE DISTRIBUTION
    IF EXISTS (SELECT 1 FROM PM10100 WHERE VCHRNMBR = @I_vVCHNUMWK AND DISTTYPE = 16 AND DSTINDX = 0)
    BEGIN
 
		--ROUNDING DIFFERENCE ACCOUNT IS SERIES 2, SEQNUMBR 900
		SELECT @ROUNDINDEX = ACTINDX FROM SY01100 WHERE SERIES = 2 AND SEQNUMBR = 900
		UPDATE PM10100 SET DSTINDX = ISNULL(@ROUNDINDEX,0) WHERE VCHRNMBR = @I_vVCHNUMWK AND DISTTYPE = 16 AND DSTINDX = 0
 
    END
 
    RETURN (@O_iErrorState)
GO
 
GRANT EXECUTE ON [dbo].[taPMTransactionInsertPost] TO [DYNGRP] AS [dbo]
GO

Post to Twitter