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:

image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Advertisements

About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: http://mohdaoud.blogspot.com). Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: