How to Resolve an Issue with Closing Entries Being Flagged Improperly for NAV

Symptoms

After upgrading to version 14.2.5 or later of the Jet Data Manager there are no longer any financial transactions that have a Closing Entry flag set to 1 (flagging the transaction as a closing entry). Filtering on the Closing Entry field in the Finance Transactions table in the data warehouse or setting the Closing Entry dimension in the Finance cube does not exclude closing entries from the result set. If you are using version 2.9 or later of the NAV project from the CubeStore then the issue has already been resolved.

Cause

This issue is a result of a correction in the way that field transformations are handled in the Jet Data Manager.  It will only manifest itself if the checkbox for Use Legacy Transformations has been unchecked in the G/L Entry table in the staging database.

Resolution

To resolve the issue, complete the following steps in the Jet Data Manager:

1. Locate the G/L Entry table in the staging database and add a new field named Posting Date Only .  This field should have a data type of Date and Time

   

2. Add a custom transformation to this new Posting Date Only field by right clicking on the field name and clicking Field Transformations , setting the Operator to Custom , and clicking Add.

3. Add the following code to the custom transformation and then right click the Posting Date field from the list on the right and click Add Parameter :

   DATEADD(DAY,0,DATEDIFF(DAY,0,[Posting Date]))

4. Expand the existing Posting Date field and remove the Date Only transformation.

5. Go to the data warehouse section, locate the Finance Transactions table, and replace the existing Posting Date copy movement with the Posting Date Only field that was created in the G/L Entry table of the staging database.

   

6. Deploy and execute the modified tables.