Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

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 !

“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. error 2

“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:

  1. Initially, go to control panel and remove the PSTL from the add remove features.

    Add remove
  2. 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.
  3. 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.
  4. 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
    Run Code
  5. 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

                               Customer
  6. Now open Microsoft Dynamics GO to find the PSTL instead of the customer exception tab.

    1 

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

Secuirty Role - AR Transaction

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.

 Error Message

     

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

 

DEXSQL

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,

Security Resource Description

 

Security Task

 

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

1423077036_kchart

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”

Setup 1

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

Setup 2

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

Setup 3

Proceed with the installation, click “Install”

Setup 4

Once the installation is completed, click “Finish”

Setup 5

 

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

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

SQL Data Mining Add-in for Office 2010

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

SQL Data Mining Add-in for Office 2010 2

 

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

Data Mining Tab

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 .


Data Cleansing Essentials Series 

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:

  1. Correct item site assignment ensures that no site will be picked incorrectly by an accountant or a warehouse keeper when entering a transactions
  2. Inventory Reporting and inquires will show only the associated sites under the “site ID” field, which makes it an easier experience
  3. 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.

Item Site Assignment

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.

 MDA - Data Set

 

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

Accont Maintenance Window

 

Printing Arabic 1 

Printing Arabic 2

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:

Web Window

When printing the card above, here is the result:

Web Window

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.

Old menu

1422816052_arrow_down

New window ribbon

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:

 New Window Ribbon Command

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'

Window Command Display | Action Pane

CommandExec dictionary 'default' form 'GL_Transaction_Entry' command 'Save Button_w_GL_Transaction_Entry_f_GL_Transaction_Entry'

 

Delete Button

Window Command Display | Menu Bar

MoveTo field 'Delete Button'
ClickHit field 'Delete Button'

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'GL_Transaction_Entry' command 'Delete Button_w_GL_Transaction_Entry_f_GL_Transaction_Entry'

 

Print Button

Window Command Display | Menu Bar

MoveTo field WindowPrint
ClickHit field WindowPrint

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'GL_Transaction_Entry' command 'WindowPrint_w_GL_Transaction_Entry_f_GL_Transaction_Entry'

 

Post Button

Window Command Display | Menu Bar

MoveTo field 'Post Button'
ClickHit field 'Post Button'

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'GL_Transaction_Entry' command 'Post Button_w_GL_Transaction_Entry_f_GL_Transaction_Entry'

 

Clear Button

Window Command Display | Menu Bar

MoveTo field 'Clear Button'
ClickHit field 'Clear Button

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'GL_Account_Maintenance' command 'Clear Button_w_GL_Account_Maintenance_f_GL_Account_Maintenance'

 

Copy Button

Window Command Display | Menu Bar

MoveTo field 'Copy Button'
ClickHit field 'Copy Button'

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'GL_Transaction_Entry' command '(L) Copy Button_w_GL_Transaction_Entry_f_GL_Transaction_Entry'

 

Correct Button

Window Command Display | Menu Bar

MoveTo field 'Correct Button'
ClickHit field 'Correct Button'

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'GL_Transaction_Entry' command 'Correct Button_w_GL_Transaction_Entry_f_GL_Transaction_Entry'

 

Void Button

Window Command Display | Menu Bar

MoveTo field 'Void Button'
ClickHit field 'Void Button'

Window Command Display | Action Pane

CommandExec dictionary 'default'  form 'CM_Transaction_Entry' command 'Void Button O_w_CM_Transaction_Entry_f_CM_Transaction_Entry'

 

View Currency Button

Window Command Display | Menu Bar

MoveTo field 'Currency View Button' item 1  # 'F&unctional (US Dollars)'
ClickHit field 'Currency View Button' item 1  # 'item 1  # 'F&unctional (US Dollars)'

Window Command Display | Action Pane

MenuSelect title View entry 'Functional (US Dollars)'

 

 

Best Regards,
Mahmoud M. AlSaadi