Only Transactions that has special characters in Number field are displayed in Bank Statement Reconciliation

One of my customers reported that when they are trying to reconcile bank statement, not all transactions entered in bank transaction window are displayed, while investigating the issue, I did the following:

1. Checked-links.

2. Changed Regional and Language options language for Unicode characters to make sure that this is not a collation error.

3. Reinstalled the application.

4. Tried to use another machine.

5. Checked the knowledgebase, the only solution they provided is to compare CM20200 and CM20500 with no luck.

6. Checked the database collation and validated that the collation for all databases is matched to the SQL engine.

7. Compared one of the displayed lines with another one and noted that only transactions that has Special Characters in the Number field are displayed! as shown below:

I created SQL Server profiler trace to get the SQL Command that retrieve the data from CM20200 and got the following SQL Statement:

SELECT TOP 25
CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, CMTrxNum, CMTrxType, TRXDATE, GLPOSTDT, TRXAMNT, CURNCYID, CMLinkID, paidtorcvdfrom, DSCRIPTN, Recond, RECONUM, ClrdAmt, clearedate, VOIDED, VOIDDATE, VOIDPDATE, VOIDDESC, NOTEINDX, AUDITTRAIL, DEPTYPE, SOURCDOC, SRCDOCTYP, SRCDOCNUM, POSTEDDT, PTDUSRID, MODIFDT, MDFUSRID, USERDEF1, USERDEF2, ORIGAMT, Checkbook_Amount, RATETPID, EXGTBLID, XCHGRATE, EXCHDATE, TIME1, RTCLCMTD, EXPNDATE, CURRNIDX, DECPLCUR, DENXRATE, MCTRXSTT, Xfr_Record_Number, DEX_ROW_ID
FROM JBC.dbo.CM20200 WHERE

(CHEKBKID >= 'JODCA-500' AND CHEKBKID <= 'JODCA-500'

AND ((Recond = 0 AND VOIDED = 0) OR (VOIDED = 1 AND VOIDDATE > '20101231'))

AND TRXDATE >= '19000101' AND TRXDATE <= '20101231'

AND CMTrxNum >= '' AND CMTrxNum <= 'øøøøøøøøøøøøøøøøøøø')

Last condition (AND CMTrxNum <= 'øøøøøøøøøøøøøøøøøøø') is the one used to filter characters to display only “CMTrxNum“ that has special characters.

I posted this case in the Microsoft Dynamics GP Newsgroup under this link, and a great guy called Robert Cavill gave me a great tip on the source of the “ø” character, it seemed to be that the character is the 'fill' character used by Dexterity to get the upper limit of characters for the given collation This character is generated when first initiating the Dynamics GP session, with the SQL, below is the command that returns this character:

exec master..smDEX_Max_Char

This character is being changed from collation to another and not sure why this was the generated one based on my collation.

However, I excluded this character from the stored procedures to get the next max character and the issue was resolved.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com