User Activity Report - Idle and Login Details SQL Script

It’s a common issue to have users logged into Dynamics GP without closing the system for several hours, and sometimes for days. The user activity inquiry doesn’t provide much in-depth details regarding the Idle time and login date and timing. In this post, an SQL script is provided to cover this concern as illustrated below:

 

 

 

 

Tables Included:

• DYNAMICS..ACTIVITY
• DYNAMICS..SY01400  | USER Master
• DYNAMICS..SY01500  | Company Master
• Tempdb..DEX_SESSION
• master..sysprocesses
• master..sysdatabases

/*---------------------------------------------------------------------
Creation Date: 6th of September, 2014
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to provide a user activity report,
along with the Idle and login details
The script has been tested on a very limited sample data.
Please feel free to contact me for any further enhancement

Revision History:

Revision No.         RevisionDate  Description
1                                 06/09/2014           Original Version 
2                                 15/09/2014           Modified Version 
---------------------------------------------------------------------*/

        SELECT  RTRIM(A.USERID) AS UserID ,
        RTRIM(B.USERNAME) AS UserName ,
        RTRIM(C.INTERID) AS CompanyDatabase ,
        RTRIM(C.CMPNYNAM) AS CompanyName ,
        LOGINDAT AS LoginDate ,
        CONVERT(VARCHAR(1000), DATEPART(HH, LOGINTIM)) + ':'
        + CONVERT(VARCHAR(1000), DATEPART(MI, LOGINTIM)) AS LoginTime ,
        ISNULL(CONVERT(VARCHAR(1000),E.last_batch),'') AS SQL_LastBatch ,
        CASE WHEN D.session_id IS NULL
        THEN 'Corrupted Missing DEX_SESSION'
        ELSE CONVERT(VARCHAR(1000),session_id)
        END DEX_SESSION ,
        CASE WHEN CONVERT(VARCHAR(1000),E.SPID) IS NULL
        THEN 'Corrupted SQL_SESSION'
        ELSE CONVERT(VARCHAR(1000),SPID)
        END SQL_SESSION ,
        CASE WHEN DATEDIFF(mi, E.last_batch, GETDATE()) > 1
        THEN DATEDIFF(hh, E.last_batch, GETDATE())
        ELSE 0
        END AS 'IdleTime - InHours' ,
        CASE WHEN DATEDIFF(MI, LOGINDAT + LOGINTIM, GETDATE()) > 1
        THEN DATEDIFF(HH, LOGINDAT + LOGINTIM, GETDATE())
        ELSE 0
        END AS 'Logged in for – InHours'
        FROM    DYNAMICS..ACTIVITY A
        LEFT JOIN DYNAMICS..SY01400 B ON A.USERID = B.USERID
        LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM
        LEFT JOIN tempdb..DEX_SESSION D ON A.SQLSESID = D.session_id
        LEFT JOIN master..sysprocesses E ON D.sqlsvr_spid = E.spid
        AND ecid = 0       
        LEFT JOIN master..sysdatabases F ON E.dbid = F.dbid

 

Best Regards,
Mahmoud M. AlSaadi

Changes to Void Historical Payables Transactions throughout GP versions

A minor yet limiting addition introduced in Dynamics GP 2013 for the void process of historical payable transactions made a lot of noise among Dynamics GP clients, because it simply broke the habit of voiding sequential payables transactions (such as checks) which are not necessarily issued to the same vendor. Here is a summary for the different phases of the void historical payable transactions.

GP 2010

Vendor ID was not a requirement in order to void a historical payable transactions. You can simply void several transactions of several vendors by clicking the associated button below.

GP 2013 and 2013 SP2

The new feature was released, I would personally not prefer to call it a feature since it is nothing but a simple “validation” on the void process. Here if you don’t pick up the vendor, you will not be able to void a historical payable transaction. You can not void a list of payable transactions for different vendors.

GP 2013 R2

In the latest release of Dynamics GP R2, the vendor remains but not as a required field anymore, as it was back in GP 2010, you can simply void multiple transactions for one or different vendors.

 

Best Regards,
Mahmoud M. AlSaadi

TSQL Fun (Pivot Function) – Analytical Accounting Dimensions

As part of a personal project I have been working on, I needed to change the structure of the data set retrieved by a simple join TSQL query, I ended up creating a dynamic code to automatically change the data structure for Analytical Accounting dimensions.

Data Structure

 

Methodology

To get the first result on the left, a simple join is required between AAG00400 (Transaction Dimension Master) and AAG00401(Transaction Dimension Code Setup).

SELECT  A.aaTrxDim ,
        B.aaTrxDimCode
        FROM    AAG00400 AS A
        LEFT OUTER JOIN AAG00401 AS B ON A.aaTrxDimID = b.aaTrxDimID

 

I will review two methods to switch the rows into columns, a static Pivot and dynamic one.

Static Method

In this case, I should include a hard-coded values within the SQL statement in order to switch the rows into columns. Which means, when a new dimension is added, it will not “automatically” appear in the result unless the code is modified.

SELECT  *
FROM
( SELECT *
  FROM    ( SELECT 
               ROW_NUMBER() OVER
               ( PARTITION BY A.aaTRXDIM ORDER BY B.aaTrxDimCode ) 
                 AS DimensionCodeRank,
                 A.aaTrxDim ,
                 B.aaTrxDimCode
                 FROM AAG00400 AS A
                 LEFT OUTER JOIN AAG00401 AS B
                 ON A.aaTrxDimID = b.aaTrxDimID
                 ) P PIVOT 
     ( MAX(aaTRXDIMCode) FOR aaTRXDIM IN 
                           ( [COSTCENTER], [EXPENSETYPE],
                             [LOCATION], [ORGANAZIONAL LEVEL],
                             [PROJECT], [REGION], [SECTOR] ) ) AS PVT
           ) AS AB

As applied above, the dimension codes are hard-coded into the SQL statement, which makes this method improper for dynamic solution. A similar PIVOT function has been used in a previous post to manipulate inventory balances, IV Available Quantity Report – SQL Pivot Function

Dynamic Method

Through this method, any dimension added on the Analytical Accounting module will be automatically retrieved, there is no need at all to modify the statement.

DECLARE @columns NVARCHAR(MAX) ,
        @columns_n NVARCHAR(MAX) ,
        @Statement NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT  @columns += N', X.' + QUOTENAME(aaTrxDim)
FROM    ( SELECT    RTRIM(aaTrxDim) AS aaTrxDim
          FROM      dbo.AAG00400
         ) AS Y;
SELECT  @columns_n +=
        N', ISNULL(' + N'X.' + QUOTENAME(aaTrxDim) + N', '''' ) AS'
        + QUOTENAME(aaTrxDim)
FROM    ( SELECT    RTRIM(aaTrxDim) AS aaTrxDim
FROM      dbo.AAG00400
) AS Y;

SET @Statement = N'
SELECT ' + STUFF(@columns_n, 1, 2, '') + '
FROM
(
    SELECT   ROW_NUMBER()
    over(partition by  A.aaTRXDIM order by B.aaTrxDimCode) AS DimensionCodeRank,
    A.aaTrxDim,B.aaTrxDimCode FROM AAG00400 AS A LEFT OUTER JOIN AAG00401 AS B
    ON A.aaTrxDimID = b.aaTrxDimID
) AS j

PIVOT
(
MAX(aaTRXDIMCode) FOR aaTRXDIM IN  (' + STUFF(REPLACE(@columns, ', X.[', ',['),1, 1, '') + ')) AS X;';

PRINT @Statement;
EXEC sp_executesql @Statement;

Here is the result for both of the method above,

 

Best Regards,
Mahmoud M. AlSaadi

Analytical Accounting Budget Import Trap

While importing Analytical Accounting budget, after being exported from another machine with the most recent backup of the Live GP Company, I got stuck with an “Unhandled Object Exception”, it keeps on popping up right after choosing the file, it is not even checking or validating the data within the Excel.

After several tries, I have discovered that someone else has been there before, not too long ago Mr. Mark Polino has gone through the same issue, and found out that you’ve got to have Excel installed on the machine, so simple. I shall keep credit for DynamicAccounting on this one.

Since this is not documented, I am writing this post to further spread the remedy, hopefully it will save someone in the near future from spending hours away from home, with a great companion “Analytical Accounting” !

Error Message:

Unhandled object exception:
Invalid class string

EXCEPTION_CLASS_OBJECT_EXCEPTION
ExceptionSubClass:-2147221005
Unhandled object exception:
Invalid class string

EXCEPTION_CLASS_OBJECT_EXCEPTION
ExceptionSubClass:-2147221005

Resolution:

Install Microsoft Excel on the machine

In this essence, I would like to shed a light on another common import error with Analytical Accounting. After you import the file, you get a report on the screen stating “ The import failed because the spreadsheet is in an incorrect format ”. There are three possible reasons for this:

• In most cases when you export the AA budget, the budge tree ID field is empty. Sometimes, when you manually fill in the template, it doesn’t match the one configured in Dynamics GP. To get this resolved, fill in the correct Budget Tree ID.

• In case you fill in the template manually, double check the totals entered on each level of the tree
• In addition to the above, in case of manual entry as well, The final total column should be the total of all period amount and the opening balance.

Fortunately, this is documented in knowledge base Article ID 2815557

 

Best Regards,

Mahmoud M. AlSaadi

Pay attention through Initial Assessment , when it Comes to Inventory Items and Currency Decimals

After an implementation of Dynamics GP for one of our clients, I have received a critical and weird case stating that all the inventory related transactions (Shipment, Inventory Adjustment and Sales) are recorded with a “Zero” unit cost, although, they are entering the correct unit cost when receiving the shipment. Here is the starting point of the case”

                                                               >>  All unit cost fields are zero, on both In and Out transactions

Troubleshooting:

The item number is defined as a sales inventory, FIFP perpetual valuation method and assigned to a KG unit of measurement. Currency decimals on the system level and distribution modules is set to (3) for this selected currency used for this item. The following is the UOM setup of KG:

 

Here is the interesting part, starting with the first transaction on this item (which is a shipment), the following are the transaction details:

• Quantity shipped: 50
• UOM: KG
• Unit Cost (for the KG): 0.44

Now, on the inventory module, the system will break down the unit cost into the smallest unit of measurement, and record the cost layer in the base unit of measurement (IV10200). In case of rounding, the system will consider splitting the cost layers into two cost layers >> Reference: unit cost and currency decimal setup

• Quantity in Base UOM: 50 * (1000) = 50,000 Gram
• Unit cost (for the Gram): 0.44/1000 = 0.00044

The result that the Inventory Module will split into two cost layers, (49,999 Gram) and (1 Gram), the one gram will have the variance which is the total cost in our scenario (22). Here is the purchase receipt smart list again:

By now, it is clear that any (out) transaction withdrawing from this cost layer will have a zero unit cost, which is incorrect. Until, all 49,999 Gram are consumed, the unit cost will be zero. Then when withdrawing the final one gram, the total cost of the whole 50 KG will be consumed.

Correction Criteria

1. Post or delete all pending “saved” transactions
2. Change the currency decimals (on the distribution level, not the system level) to encompass more than the current number of decimals for this specific item, not necessarily all the items.
3. Adjust cost for the historical transactions

 

In order to change currency decimals, go to Microsoft Dynamics GP > Tools > Utilities > Inventory > Change Decimal Places

Helping Note !

• Before running the change decimal places utilities, no user in the company should be working on any of the inventory related transactions (Such as:Inventory Adjustment, Transfer, Receiving, Sales …etc).
• No transaction should be saved or pending on a purchase order, receiving …etc.
• One pressing on “Process”, you will be warned that Manufacturing isn’t updated when you change currency decimals. It’s fine, just proceed

In order to adjust the cost, go to Microsoft Dynamics GP > Tools > Utilities > Inventory > Adjust Cost

After processing the cost adjustment, a report will show all the journal entries created to adjust the cost on the “General Ledger” as well. Please be noted that not only the current cost layer; which is being adjusted, will be affected. ALL related “out” transaction which withdraws from this specific cost layer will be affected accordingly.

The affected transactions will be listed in the Purchase Receipt Update Detail report, after processing the cost adjustment.

Here is the journal entries created on the General Ledger, it will affect inventory and inventory offset accounts (COGS)

 

Best Regards,

Mahmoud M. AlSaadi