When using currency translation in MR, you may have encountered an issue where some accounts do not translate.  Some accounts work correctly, but others that are set up exactly the same do not translate.  They show the same foreign currency amount as the functional amount.  If you try the same thing with the Legacy provider, the account will translate correctly. 

This is due to how data mart combines accounts based on the main account segment to get certain account details. One of these details is the Currency Translation Type. If the first account the data mart loads does not have a CurrencyTranslationType in the MC00200 table, then every account with that main segment won’t translate because it comes through as NULL which later gets set to 0 (Unknown).

If the account was created in GP, it will always add the default record to the MC00200.  But, if the accounts were imported using eConnect or Smart Connect (which uses eConnect), the MC00200 is not updated and the account will be missing the default record.  There are two scenarios that can happen.

Scenario 1

The default record is missing from the MC00200. You can check this by running this statement against your company database:

 

SELECT * FROM GL00100 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM MC00200) AND ACCTTYPE = 1

 

Default records will have a blank currency ID and the Currency Translation Type will be set to 1.  It should look something like this:

Scenario 2

The default record exists, but the CurrencyTranslationType has a value of 0.  You can check this by running this statement against your company database:

 select * from MC00200 where CurrencyTranslationType = '0' and CURNCYID = ''

 

 Notice how this default record has 0 for the CurrencyTranslationType.

 

 

 

 Depending on the scenario you encounter, the fix will be different.  You should do this in a test environment first. 

 

Scenario 1 fix

Run the script below to insert the missing default records into the MC00200.  This will fix the existing records in the table. 

SET QUOTED_IDENTIFIER ON

DECLARE 
@ACTINDX INT

DECLARE INSERTMC00200 CURSOR 
FOR 
SELECT ACTINDX FROM GL00100 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM MC00200) AND ACCTTYPE = 1

OPEN INSERTMC00200 

FETCH NEXT FROM INSERTMC00200 INTO
       @ACTINDX
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO MC00200 (ACTINDX,CURNCYID,CurrencyTranslationType,DEX_ROW_TS,Post_Results_To,REVALUE,REVLUHOW ) 
VALUES (@ACTINDX, '', '1','2020-05-06', 0,0,0)

fetch next from INSERTMC00200 into
@ACTINDX

end
close INSERTMC00200
deallocate INSERTMC00200

Scenario 2 fix

  1. Log into the company in GP.
  2. Go to Cards>>Financial>>Currency Account Update.
  3. Select your main segment in the Segment ID
  4. Enter the account number in the From and To
  5. Click Insert.
  6. Select Update Currency Translation.
  7. Select the desired Currency Translation Type.
  8. Click Update.

 

You will need to rebuild the MR data mart after updating the Currency Translation Type.

 

To ensure you don't run into this problem going forward, you will need to modify the taUpdateCreateAccountRcdPost stored procedure.  This will automatically insert a default record into the MC00200 table when new accounts are integrated into GP via eConnect or Smart Connect. This will ensure you don’t run into the problem going forward.

  1. Open SQL Server Management Studio.
  2. Expand the Database folder.
  3. Expand the company database folder.
  4. Expand Programmability.
  5. Expand Stored Procedures.
  6. Find the taUpdateCreateAccountRcdPost procedure and right-click on it.
  7. Select Script Stored Procedure As | ALTER To | New Query Editor Window.
  8. Below the SET nocount ON line, insert the following:

DECLARE @ACTINDX INT
       SELECT @ACTINDX = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = @I_vACTNUMST)
       IF NOT EXISTS (SELECT ACTINDX FROM MC00200 WHERE ACTINDX = @ACTINDX)
       BEGIN
       INSERT INTO .MC00200
                  (ACTINDX,
                   CURNCYID,
                   REVALUE,
                   REVLUHOW,
                   Post_Results_To,
                   CurrencyTranslationType)
      VALUES      ( @ACTINDX,
                    '',
                    0,
                    0,
                    0,
                    1)
       END

  1. Double-check that the new script is inserted after the SET noaccount ON line and before SELECT @O_iErrorState=0 line. 
  2. Save your changes and execute the script.