Analytical Account Posting Setup - Breaking the Hidden Secrets!!

Yesterday one of my customers reported an issue that the analytical accounting report is not being printed after the “General Posting Journal” upon posting a GL Transaction.

First thing came to my mind that this report has been unmarked in “Posting Setup” then in moment I realized that the Posting Setup does not contains anything for AA!!

It has been said in the analytical accounting installation manual that YOU MUST CONFIGURE YOU POSTING SETUP BEFORE ACTIVATING THE PRODUCT:

Setting up posting options for Analytical Accounting
Once you’ve installed Analytical Accounting for your company, you must set up
the appropriate posting options for Analytical Accounting in the Microsoft
Dynamics GP Posting Setup window. Be sure that you’ve completed this task before
you activate Analytical Accounting.

In order to collect analytical information for an account, the account must be linked
to an account class. Analytical Accounting does not support posting in summary.

So how to adjust this? Where can I find the posting setup for AA and how to configure my reports?

An hour of profiler trace returned what does GP actually do to load report, specially after activating the “Allow Transactions Posting” option and getting the error below on the report generation:

“Unable to obtain posting destination record.”

I my trace, I noticed that GP is running the following query to get report printed to the appropriate destination:

exec TWO.dbo.zDP_SY02200SS_1 2,'General Entry','General Posting Journal'

This script returned the following results:

Then I saw the following command which returned the following result:

exec TWO.dbo.zDP_SY02200SS_1 2,'General Entry','3180rpt_8'

At this moment I realized that this “3180rpt_8” report is being called without being exist in posting setup table and that’s why I used to get the above message and being able to get the report printed.

Resolution:

I just added the report row into SY02200 table and rerun the posting! That worked like a charm!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Error Saving Cashbook Management Transaction

This morning I been working on a wired case with Cashbook Management Module, the case was many error messages shown up upon saving the payment line:

First error was:

An error occurred executing SQL statements.

Then: GPS Error: 58

Then: SQL Error: 8114 Microsoft SQL Server Native Client 10.0 SQL Server Error converting data type varchar to datetime.

Then: ODBC Error: 37000

I felt like I am guilty! And thought that everything will be missed up after clicking tons of OK’s, then I decided to move on and look for the reasons behind this miss.

Running SQL Profiler Trace identified the query that been causing this error below:

Finally I noticed that the time attached to the query is ‘12:00:00 ص’ instead of ‘12:00:00 AM’

I replaced Arabic character in my time format from Arabic to English and it worked like a charm!!!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Empty “Table Name” when doing Import/Export for EFT File Format

 

Have you ever tried to import and export EFT File Format and found the table name “empty”? It is actually looks like a bug in the applications, it does not import the series of the EFT file upon import and leave it as “0”.

To resolve this, go to SQL Server and open table CM00103, set the series field to “4” for purchasing and enjoy!

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

SOP Master Numbers not being assigned properly

 

A unique document number could not be found. please check setup.

I been with a situation where my customer were getting the above messages, noticed that the system is updating the master number in SOP40100 to a smaller number which sometimes being exist in SOP10100 or SOP30200.

I workaround this by creating a trigger on SOP10100 and SOP30200 to update the SOP40100 once saving or updating transactions and set the next master number to be current maximum master number plus 1!

Below the scripts I used:

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

Enjoy!

UPDATE: Folks at Accolade Publications, Inc has publish an article that contains a modified script to fix this issue, below is the modified script:

/****** Object: Stored Procedure dbo.sopGetMasterNumber ******/
if exists (select * from sysobjects where id = object_id('dbo.sopGetMasterNumber') and sysstat & 0xf = 4)
drop procedure dbo.sopGetMasterNumber
GO

create procedure dbo.sopGetMasterNumber
@O_iOUTMasterNumber int = NULL output,
@O_iErrorState int = NULL output
as

/*
**********************************************************************************************************
* (c) 1994 Great Plains Software, Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: sopGetMasterNumber
*
* SANSCRIPT NAME: Get_Master_Number of form SOP_Entry
*
* PARAMETERS:
* @O_iOUTMasterNumber Retreived Master Number
* @O_iErrorState contains any errors that occur in this procedure
*
* DESCRIPTION:
* Returns the next number field from the given SOP_SETP record and increments
* the next number.
*
* Customization was made to look at SOP40500 to verify the NXTMSTNO is larger than existing values.
*
* TABLES:
* Table Name Access
* ========= =====
* SOP40100 Read/Write
*
* DATABASE:
* Company
*
*
* RETURN VALUE:
*
* 0 = Successful
* non-0 = Not successful
*
* REVISION HISTORY:
*
* Date Who Comments
* ------------- -------- -------------------------------------------------
* 24Jun98 msluke Initial Creation
*****************************************************************************
*/

declare @tTransaction tinyint,
@iError int,
@MaxMSTRNUMB int

/*
* Initialize variables and Output Parameters.
*/
select @O_iOUTMasterNumber = 0,
@O_iErrorState = 0

/*
* Start a transaction if the trancount is 0.
*/
if @@trancount = 0
begin
select @tTransaction = 1
begin transaction
end

/*
* Read record from SOP_SETP table within an update statement so a lock is held
* on the record until the number is updated. This will ensure that only a single
* user is reading this record at any given time.
*/
update
SOP40100 WITH (TABLOCKX,HOLDLOCK)
set
@O_iOUTMasterNumber = NXTMSTNO,
NXTMSTNO= NXTMSTNO + 1

if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
/*
* Do an additional read from SOP40500 to attempt to recover from the situation where the NXTMSTNO
* is less than or equal to the max value in SOP40500.
*/
select @MaxMSTRNUMB = max(MSTRNUMB) from SOP40500 (nolock)
if (@MaxMSTRNUMB >= @O_iOUTMasterNumber)
begin
update
SOP40100
set
@O_iOUTMasterNumber = @MaxMSTRNUMB + 1,
NXTMSTNO= @MaxMSTRNUMB + 2
if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end

/*
* Reset next master number to 2, if master number has reached max value
* or it is zero.
*/
if (( @O_iOUTMasterNumber = 99999999) or ( @O_iOUTMasterNumber = 0)) and @O_iErrorState = 0
begin
select @O_iOUTMasterNumber = 1
update
SOP40100
set
NXTMSTNO = 2

if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end

/*
* Determine if a rollback or commit should be executed.
*/
if @O_iErrorState <> 0
begin
select @O_iOUTMasterNumber = 0
/*
* Rollback the transaction if this procedure started it.
*/
if @tTransaction = 1
rollback transaction
end
else
begin
/*
* Commit the transaction if this procedure started it.
*/

if @tTransaction = 1
commit transaction
end

return

GO

GRANT EXECUTE ON dbo.sopGetMasterNumber TO DYNGRP
GO

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

Post to General Ledger has not been marked for this batch.

 

I got a call yesterday from one of my customers, they have a case that they are being notified when posting inventory transactions that the transaction will not post to GL:

“Post to General Ledger has not been marked for this batch. Analytical Accounting information that may be created for this batch will be deleted on posting. Do you want to continue?”

First things I used the customer to check is the posting setup, but they confirmed that nothing changed and posting setup is marked to post to GL.

Then I realized the they are posting a “transaction” not a “batch”, which lead to the fact that something is incorrect.

I asked them to go to SY00500 and check if there is any batches with empty batch number and that was the case! They had a batch with empty batch number exists in the table and not marked to post to GL and GP was looking into this table when posting without batches!

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

Customer Summary

I got a question from one of my consultants at Dynamics Innovations:

Why SOP Customer Returns does not affect Customer Summary Inquiry??

I checked this a dozen of times and wanted to say it does affect, but unfortunately I couldn’t! SOP returns does not affect customer balance inquiry:

Receivables management returns are included, but Sales Order Processing Returns are not, I reported this on Connect and waiting for reply, please vote guys to get this done!

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

Issue in Bank Deposit Entry Form

Talking about Bank Deposit Entry form, I noticed an issue when trying to change the Checkbook ID, the system does not automatically populates the next available deposit number, instead its uses the old number exists in the form and tries to load it document with the following message:

“The deposit already has been posted for this checkbook. Choose Continue to display the posted transaction for voiding.”

Or worst, if the selected deposit number for the old checkbook was reconciled in the newly selected checkbook, the following message will appear:

“This deposit can’t be used. It has been reconciled, is marked for reconciliation or has been previously voided.”

The simple workaround is just by clicking “Clear” before changing the Checkbook ID, I will report this to Microsoft and hopefully will get a reply!

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

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

“The note ID has not been set”

David in his “Fixing missing Note Index Values” post resolved the notes indexing errors, checkout his post here.

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

Inside Microsoft Dynamics 2010: Metrics Flash Activation for Old Internet Explorers

Recently I found that when pointing the mouse over the metrics, the system cannot display the object correctly and appear with a gray border and a text “click here to activate and use this control“:

We used to face such issues in web development when we upload flash objects in the site and used to workaround it by adding “IEUpdate” script that automatically activates the control, find out the details about the resolution here.

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

Inside Microsoft Dynamics 2010: Issues found so far

For the time being, I am working on a standard installation of the application without upgrading any existing database, so far 2 issues where identified:

1. Inquiry >> Financial >> Financial Summery does not working properly, allot of the zero-transactional accounts displayed as zero’s in the list while the totals below displays numbers, the totals are correct while the lists are not:

[UPDATE] Resolution:

Reconcile your open year will resolve the issue.

2. “Set as default view” command is getting disabled upon performing any changes on the sort order:

3. In addition to the installation error I already posted few hours ago:

 http://mohdaoud.blogspot.com/2010/01/inside-microsoft-dynamics-2010.html

4. After performing budget transaction for one of your budgets, and trying to post you get “The Journal Entry has already been used, please enter another.” even if this was correct the system should automatically load a valid Journal Number as after starting journal entry you cannot change the number and will have to redo the journal:

Currently I am running the following environment:

Windows Server 2003 R2, SQL 2005 with Arabic_CI_AI collation.

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