Dynamics GP Essentials: February 2015

Saturday, February 21, 2015

Dynamics GP 2015 RTM – PSTL InValid Versions

Recently, PSTL related case was reported on the community for clients who upgraded to Dynamics GP 2015, it seems there is a problem with the version number of this product and associated dictionary files. Here are the errors:

“InValid Versions ! This version of professional services tools is only compatible on GP 2013”

Important Note !
This is not an upgrade issue, it occurs with new installations of GP 2015 as well.
Special Thanks to Heather Roggeveen on this finding !

The PSTL loads the form after prompting the message above, then it gives another error message as you click on any of the tools, it indicates that the utility is not registered.

“You are not registered for this selected utility”

Resolution
This was reported as a known issue by the support team, and a chunk file was provided on the partner source to get this resolved. To resolve this problem, follow the steps below:

Initially, go to control panel and remove the PSTL from the add remove features.
Once the PSTL is removed, proceed and download the chunk file from the partner source. Here is the download link. Remember, you need a partner source account. So if you only have a customer source account, refer to your partner to provide you with the download file accordingly.
Unzip the the PSTL_GP_14.0.3_Release.zip file (downloaded in the previous step), and put it in your installation folder on the default path (C:\Program Files (x86)\Microsoft Dynamics\GP2015)

Helping Note !
Take a back up of the whole installation folder before placing the chunk file within, it is quite important before running the new code.
Run Microsoft Dynamics GP as an administrator, you will be prompted with a message that new code must be included in the installation folder, click “Yes” to proceed
Why Microsoft Dynamics GP is open, if you see “Customer Exception” instead of PSTL. Have a back up of your dynamics db and company db, and run the script below:

USE DYNAMICS
DELETE SY07130
Now open Microsoft Dynamics GO to find the PSTL instead of the customer exception tab.

Best Regards,
Mahmoud M. AlSaadi

Saturday, February 7, 2015

Using DEXSQL.LOG to Track Security Issues “Not Privileged to run this report”

How many times have you encountered a security issue with a user clicking on a specific button and an error message pops up such as “Not Privileged to run this report” ! It must have been a hard time for you looking through the various security tasks and roles trying to figure out what’s missing.

Although I have always been a big fan of the support debugging tool, I still like to use other methods such as the “DexSQL.Log”, which will be thoroughly illustrated through this article.

Describing the case

A user is granted an access to enter AR Transactions including receivable transactions, cash receipts and apply sales documents. A new security role has been created including the following predefined security tasks:

Default User
TRX_SALES_013* | Enter receivable transactions
TRX_SALES_015* | Enter customer cash receipts
TRX_SALES_016* | Apply sales documents

Now as the user enters AR transactions, they clicked on the “Print button” before posting to check the edit list report, and a security message pops up stating that the user has no privilege to run this report.

Solution | DEXSQL.LOG Method

One of the method to track this issue easily and quickly without digging quite too deep in the security tasks and roles, is to create a DEXSql.log which will take you to the specific missing security record for this specific report.

1- Creating DEXSql.log

To create a Dexsql.log, open the Dex.ini file which exists by default in the following location (C:\Program Files\Microsoft Dynamics\GP\Data)
Locate the following statements in the Dex.ini file
        SQLLogSQLStmt=FALSE
        SQLLogODBCMessages=FALSE
        SQLLogAllODBCMessages=FALSE
If the statements are set to false (which is the default case), change the values to “True”
        SQLLogSQLStmt=TRUE
        SQLLogODBCMessages=TRUE
        SQLLogAllODBCMessages=TRUE

Further illustrative details in creating a Dexsql.log for Microsoft Dynamics GP can be found on the support Article ID: 850996

2- Tracking the security issue

Now as the Dexsql.log has been created, you can proceed and open Dynamics GP for the specific user (on the same machine on which the Dex.ini has been modified)
To make the process easier, open the screen and clear the log just before clicking the button which caused the security warning (Just to make the log shorter and easier to read)
Click on the button which caused the security warning, and then open the Dexsql.log file which exists by default in the following locations (C:\Program Files\Microsoft Dynamics\GP\Data)
Look for the command that is calling , zDP_SY10000SS_1 it will tell what’s the exact missing security record

The stored procedure is trying to determine whether the user has an access on this specific security record, here is the select statement being executed by this stored procedure,

      SET nocount ON
      SELECT TOP 1
             USERID ,
             CMPANYID ,
             DICTID ,
             SECURITYID ,
             SECRESTYPE ,
             ALTDICID ,
             Modified ,
             DEX_ROW_ID
      FROM    .SY10000
      WHERE   USERID = @USERID
          AND CMPANYID = @CMPANYID
          AND DICTID = @DICTID
          AND SECURITYID = @SECURITYID
          AND SECRESTYPE = @SECRESTYPE
      ORDER BY USERID ASC ,
               CMPANYID ASC ,
               DICTID ASC ,
               SECURITYID ASC ,
               SECRESTYPE ASC
      SET nocount OFF

If you run this script above after providing the associated parameters, it will retrieve no records, that justifies the security warning since this user has no access for this specific security record.

What is the missing Security Record !

You can simply run the following statement on the [Security Resource Description] table, which will retrieve the specific record that should be granted to the user

SELECT *
FROM    dbo.SY09400
WHERE   DICTID = 0
        AND SECURITYID = 284
        AND SECRESTYPE = 23

Here is the record to be added,

Helping Note !

It is a must to have the security resource description table populated, if the select statement above retrieved no records. That means you should get this table populated first. See the details on How to identify the Security Tasks and Security Roles associated with a specific window or report by David Musgrave

At the end, I should sincerely thank Mr. David Musgrave for the endless source of information provided on developing for Dynamics GP, this Dex.ini switch has been illustrated thoroughly by him back in 2008 on How to Resolve Security errors on Login

Best Regards,
Mahmoud M. AlSaadi

Wednesday, February 4, 2015

BI Essentials Series | Deploying SQL Data Mining for MS Office (Part 1)

Another essentials series is seeing a light today that is related to the insight of your data, Business Intelligence series for Dynamics GP.

The importance of this series comes from the fact that any ERP system needs some sort of an intelligent reporting layer that plays the role of interpreting and analyzing your raw data. Remember, when talking about BI, data cleansing comes as a required prerequisite to be considered initially, this is the main purpose of the previous Data Cleansing Essentials series

The first topic of this series is “SQL Data Mining for MS Office”, a very important add-on that is deployed on the SQL server level to provide important analysis and reporting capabilities for your ERP system, which is Dynamics GP specifically in our series.

SQL Data Mining Prerequisites:

Microsoft Office Excel (2010 or 2013, 32 or 64), make sure to have the appropriate Excel version. Here is the download link
Data mining add-in requires a connection to one of the following editions of SQL Analysis services (Enterprise, Business Intelligence or Standard)

SQL Data Mining Deployment:

After downloading the files from the link above, you need to run the .msi files, and go through the installation wizard.

Click “Next”

Accept the terms in the license agreement to proceed, click “Next”

Choose the feature to be installed as part of the add-in, click “Next”

Proceed with the installation, click “Install”

Once the installation is completed, click “Finish”

Now, open Excel, and a window related to SQL Data Mining add-in will pop up providing three choices as follows:

Download an evaluation version of Microsoft SQL Server and setup Analysis services on my computer
Use an existing instance of Microsoft SQL Server 2008 (or later) analysis services that I administer
Connect to Microsoft SQL Server 2008 (or later) analysis services instance to which I have non-administrative access

Click on option 2, you will be asked to run the Server Configuration Utility.

Next post will take you through the Server Configuration Utility, which main purpose is to connect to the SQL Analysis Services. Meanwhile, you will find a new tab added to Microsoft excel “Data Mining”, including the following categories:

Data Preparation
Data Modeling
Accuracy and Validation
Model Usage

Additionally, you can check the Excel sample data file (DMAddins_SampleData.xlsx) on the following path (C:\Program Files\Microsoft SQL Server 2012 DM Add-Ins\DMAddins_SampleData.xlsx). The excel file includes various cases which will help you understand the benefit of this add-in.

Best Regards,
Mahmoud M. AlSaadi

Tuesday, February 3, 2015

Data Cleansing Essentials Series | Dummy Item-Site assignment

In the previous article of the data essentials series, a script was provided in order to determine the status of the Chart of Accounts and make sure that no “un-used” accounts exist.

In this post, another script is provided to handle another data quality issue related to the inventory module. It is primarily about the incorrect item-site assignment .

Inventory items should only be assigned to the actual sites on which there are transactions. Although, it is a very common case to find items assigned to site that they don’t actually have transactions on. The reasons are quite too many, either due to the lack of correct master files at the implementation phases, granting access to the item-site assignment window to several users without any segregation of duty or control, or many possible other reasons.

There are many reasons why the item-site assignment topic should be considered by clients, some of the reasons are:

Correct item site assignment ensures that no site will be picked incorrectly by an accountant or a warehouse keeper when entering a transactions
Inventory Reporting and inquires will show only the associated sites under the “site ID” field, which makes it an easier experience
The cost of correcting invalid item-site assignment after transactions are posted could cause data quality issues. Taking into consideration that deleting item-site assignment will remove all inventory cost layers.

A very simple example below shows that a “raw material” item is assigned to the Finished Goods warehouse, which is totally incorrect. Deleting this assignment will ensure that no incorrect transactions for the raw material item will be posted to the FG warehouse.

How to detect the problem

The SQL script below checks the posted and un-posted transactions to find actual current assignments. Then look for the master assignment as retrieved from the IV00102 table. In case there is an assignment on which there is no actual (saved or posted) transactions, this assignment will be deleted.

Note !

Do Not run on live environment unless you have done a previous test of the result. The script below “Deletes” records from the database as derived form the logic described above.
Taking a back up of your database beforehand is a must.

Tables Included:

IV10200 | Inventory Purchase Receipt
IV10001 | Un posted Inventory Transactions
IV00102 | Item Site Assignment

DELETE FROM dbo.IV00102
WHERE   DEX_ROW_ID IN (
                        SELECT DISTINCT
                        DEX_ROW_ID
                        FROM (
                             SELECT DISTINCT
                             DEX_ROW_ID ,
                             RTRIM(ITEMNMBR) + '-' + RTRIM(LOCNCODE) AS ITEM_INDEX
                             FROM      dbo.IV00102
                             WHERE     RCRDTYPE = 2
                             AND RTRIM(ITEMNMBR) + '-' + RTRIM(LOCNCODE)
                             NOT IN (
                               SELECT DISTINCT
                               RTRIM(ITEMNMBR) + '-' + RTRIM(TRXLOCTN) AS ITEM_INDEX
                               FROM    dbo.IV10200
                               UNION ALL
                               SELECT DISTINCT
                               RTRIM(ITEMNMBR) + '-' + RTRIM(TRXLOCTN) AS ITEM_INDEX
                               FROM    dbo.IV10001 )
                         ) IncorrectAssignemtn )

Best Regards,
Mahmoud M. AlSaadi

Sunday, February 1, 2015

General Ledger Journals with Multi Dimensional Analysis Details – SQL Script

For analysis details in the General Ledger module, some companies consider the AA (Analytical Accounting) module while others consider the MDA (Multi Dimensional Analysis). A previous post provided an SQL script for General Ledger Journals with Analytical Accounting Details –SQL Script, now it is important to proceed and provide an SQL script for the MDA details.

Tables Included:

- DTA10100 | Transaction Analysis Groups
- DTA10200 | Transaction Analysis Codes
- GL00100 | Account Master
- GL00105 | Account Index Master

SELECT CASE A.DTASERIES
         WHEN 2 THEN 'Financial'
         WHEN 3 THEN 'Sales'
         WHEN 4 THEN 'Purchasing'
         WHEN 5 THEN 'Inventory'
         WHEN 6 THEN 'Payroll – USA'
         WHEN 7 THEN 'Project'
         ELSE ''
        END AS DTA_Series ,
        JRNENTRY AS JournalEntry,
        D.ACTNUMST AS AccountNumber,
        C.ACTDESCR AS AccountDescription,
        A.GROUPID AS DTA_GroupID,
        CODEID AS DTA_CodeID,
        GROUPAMT ,
        CASE
         WHEN CODEAMT > 0 THEN CODEAMT
         ELSE 0
        END AS Debit,
        CASE
         WHEN CODEAMT < 0 THEN CODEAMT
         ELSE 0
        END AS Credit,
        A.TRXDATE AS TransactionDate,
        A.DTAREF AS DTA_Reference,
        DTA_GL_Reference ,
        A.DOCNUMBR AS OriginalDocumentNumber ,
        A.RMDTYPAL ,
        CASE PSTGSTUS
         WHEN 1 THEN 'Unposted'
         WHEN 2 THEN 'Posted'
         ELSE ''
        END AS DTA_PostingStatus ,
        B.DOCNUMBR ,
        B.RMDTYPAL ,
        POSTDESC AS PostingDescription,
        DTAQNTY
        FROM    dbo.DTA10100 AS A
        LEFT OUTER JOIN dbo.DTA10200 AS B ON A.ACTINDX = B.ACTINDX
        AND A.DOCNUMBR = B.DOCNUMBR
        AND A.DTAREF = B.DTAREF
        AND A.DTASERIES = B.DTASERIES
        AND A.GROUPID = B.GROUPID
        AND A.SEQNUMBR = B.SEQNUMBR
        LEFT OUTER JOIN dbo.GL00100 AS C ON A.ACTINDX = C.ACTINDX
        LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX

Best Regards,
Mahmoud M. AlSaadi

Dynamics GP 2015 – Printing Arabic Characters Issue !

Recently, we have encountered a serious issue in GP 2015 as related to printing “Arabic” characters, this could be a serious problem for companies that are willing to upgrade to GP 2015. The issue has been reported by MVP Mohammad Daoud and is being tracked with Microsoft accordingly, here is the article related to this issue.

When printing any report from Dynamics GP, Arabic characters prints out as rubbish, usually, switching the language to Arabic from the language bar results with printing Arabic correctly. Now the problem in 2015 is that Arabic characters are being overridden for some reason, and printed as rubbish when sending the report to the printer.

Desktop Client

Web Client

The same behavior can be reproduced on the web client and the result is the same, Arabic characters are never printed correctly on the paper. Here is an example from the web client:

When printing the card above, here is the result:

MS Connect Case

Here is the case on MS Connect Dynamics GP 2015 – Printing Arabic Text

Best Regards,
Mahmoud M. AlSaadi

Macro Code changes regarding the new “Window Ribbon” - Dynamics GP 2013 R2 and 2015

With the new window ribbon introduced in Dynamics GP 2013 R2, we have seen a slight modifications on the commands used to deal with the window ribbon functions specifically. You might need to take a look at these modifications if you have a predefined set of macros that are used in different environment, because a macro recorded on a previous version such as GP 2013 will not run on GP 2013 unless these points are taken into consideration.

The modifications are primarily related to the following buttons:

Save
Clear
Delete
Post
Clear
Print
Void
Copy
Correct
View Currency

The switch is represented primarily between the [ MoveTo Field and ClickHit Field ] commands to the command execution [ CommandExec ], as shown below:

Helping Note !
The commands below represents “examples” taken from different forms. Unlike the old menu commands which can be used across different forms, the new ones can not be taken for granted and used for any form since the form is included in the command line.

Save Button

Window Command Display | Menu Bar

MoveTo field 'Save Button'
ClickHit field 'Save Button'