Audit Requirements for Dynamics GP

The need to keep a log for every single modification on the system usually never comes without considerable cost either from a technical or business perspective. Although, the need for such requirement remains crucial. In this post, I am shedding a light specifically on the need to keep track of every single modification of a record in a simple and yet cost effective methodology.

Technical Perspective

There are several methods to accomplish such requirement; very great and well know third party products can just deliver the need, techniques such as triggers, timestamp columns, join queries … etc could be a time-consuming which sometimes result in an undesirable performance. Meanwhile, there is a great feature out there in SQL management which can give you all these requirements with a low-cost, it is called the “Change Data Capture - CDC”

SQL Server | Change Data Capture – CDC

Change data capture provides information about the DML (Data Manipulation Language) changes on a table or database, it is very useful to know what are the inserted or deleted records, what are the values of the updated records (before and after the update).

The important of CDC will be explained through a simple example of updating a vendor card, which got few fields updated from a specific value to another value. CDC will provide detailed information of the data before and after update as shown below

Configuring Change Data Catalog for Dynamics GP

Once configured, CDC builds new system tables, stored procedures, SQL jobs and functions. Initially, you can run the scripts below to check whether CDC is enabled either on the database level or table level.

To check whether the CDC is enabled on the database level

-- The script below checks whether CDC is enabled for each database
USE master
SELECT  is_cdc_enabled ,
database_id,
name,
state_desc,
create_date,
user_access_desc,
is_read_only,
snapshot_isolation_state_desc,
recovery_model_desc
FROM    sys.databases     

To check whether the CDC is enabled on the table level

-- The script below checks whether CDC is enabled for each table
USE TWO
SELECT object_id,type_desc,name,is_tracked_by_cdc
FROM sys.tables

 

 

 

 

In the screen shot above, it is obvious that CDC is enabled on the TWO db, and specifically on the IV00101 table which is the Item Master. In order to enable CDC on the DB and Table, run the following scripts:

To enable the CDC on the database level:

-- The script below enables CDC on the database level
USE TWO
EXEC sys.sp_cdc_enable_db

To enable the CDC on the table level:

-- The script below enables CDC on the table level

USE TWO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'IV00101',
@role_name     = NULL

Once CDC is enabled, the following objects are created under TWO db > Tables > System Tables

Important Tables:

cdc.change_tables | Storing the tables being tracked
cdc.captured_columns | Storing the column being tracked per table

 

Real Case Scenario

After enabling the CDC on the TWO db, and the item master table specifically IV00101, I am going to apply the following scenario in order to check the result:

1. Adding a new item card
2. Changing few fields of the item such as; item class, UOM schedule, item description …etc
3. Delete the item

Here is the tracking log as retrieved from [cdc].[dbo_IV00101_CT]  on the following path  (TWO db > Tables > System Tables), the table shows the different changes applied on the item card since it was inserted, updated then deleted.

 

Here is the script which is used to retrieve tracking information above, it should be different according to the table for which you enable CDC.

SELECT CASE __$OPERATION
         WHEN 1 THEN 'Deleted'
         WHEN 2 THEN 'Inserted'
         WHEN 3 THEN 'Before being Updated'
         WHEN 4 THEN 'After beign Updated'
         ELSE ''
       END AS Record_Status,
       *
       FROM [cdc].[dbo_IV00101_CT]

 

In brief, change data catalog feature is a simple and cost-effective method that can be applied to track highly sensitive data such as setup in general. In proceeding posts, I will include a real case example of tracking opening and closing fiscal periods on the financial module and provide alert accordingly.

Best Regards,
Mahmoud M. AlSaadi

Inventory Reconciliation – Adding new records

It is commonly known that Inventory Reconciliation can mostly handle incorrect balances as it goes through the transactions and recalculates the different balances of the item (quantity on hand, allocated quantity …etc). Although, it does definitely check the data integrity; like check links ,and add new records to the database. In this post, two common scenarios as related to Inventory Reconciliation are illustrated.

Before we start, you may refer to the overall visual explanation and illustration of the inventory reconciliation process on Inventory Reconciliation Flow

1- Added Purchase Receipt for lot number or serial number  | Or, Added Serial Number Reconcile | Or, Added Lot number Reconcile:

One of the primary checks conducted by the IV reconciliation is checking the lot number master and serial number master against inventory purchase receipt. Technically speaking, these are IV00300 and IV00200 against IV10200. The following chart illustrates generally the overall process

Inventory  Reconciliation Flow – Summarized Representation

Point indicates that lot number master and serial number master are both checked against the purchase receipt cost layers and accordingly the required modifications or corrections are deployed. In other words, the following rules are applied:

Scenario (1) Each purchase receipt for items with either with (lot number /serial number tracking options) should have a master lot or serial number record. If there is a missing record, a new record will be inserted in IV00300 or IV00200

See below reconciliation reports after running the IV Reconciliation Process:

Scenario (2) Visa versa, each open lot number in the IV00300 and serial number in IV00200 will check the associated purchase receipts in IV10200, if there is a purchase receipt missing, a new purchase receipt will be created

See below reconciliation reports after running the IV Reconciliation Process:

 

2- Added quantity sold detail record quantity

Point indicates that purchase receipt work and details are to be checked as well. In IV10200, each cost layer has a quantity received and quantity sold, the total number of quantity sold which is stored in IV10200 should match the total of all out transactions in IV10201. In case of mismatch, a RECON record will be created in IV10201 in order to tie the work and details.

See below reconciliation reports after running the IV Reconciliation Process:

In a previous post, an SQL Script was provided to check the data integrity between purchase receipt work and details before running the reconciliation, if you have no records returned, it means you are on the safe side.

In summary, it is important to take into consideration that Inventory Reconciliation is not only about recalculating the balances and correcting the summary tables, It could definitely insert new records when required for the sake of data integrity.

Best Regards,
Mahmoud M. AlSaadi

David Musgrave has left Microsoft – New Journey Begins with “Winthrop Development Consultants”

It has been sadly one of the most shocking news I’ve heard this year, David Musgrave is not a Microsoft employee anymore ! Well, this has been even more shocking to me than the announcement of dropping Microsoft Dynamics SMB ERP certifications. It’s been thirteen and half years of continuous achievements in the different aspects of Microsoft Dynamics GP that will never be forgotten.

Change has always been an inevitable part of our lives, it goes without saying that Intelligence is the ability to adapt to change and yet expect it and get well planned for it. The end of David’s journey with Microsoft is the beginning of another as he has already restarted his consulting business which he had before joining Microsoft; Winthrop Development Consultants. In this essence, the news is shocking but quite promising as there is a too much on the horizon to come.

Best Regards,
Mahmoud M. AlSaadi

Limited Users in Dynamics GP 2013 R2 – Security Insight

A very common issue that has been seen recently with among Dynamics GP client is the access of the limited user, you always encounter a simple question and proceed with several clicks and checks to reach an answer or “Yes it has access or No it doesn’t have an access” on this specific window.

It is worth saying that the answer is just there at the tips of your fingers. When opening the security task window, a new icon has been added to “denote windows available to limited user types”. See below:

 

 

 

 

 

 

 

 

 

 

Security Task Details as Related to Limited User – SQL View

The script below retrieves all the security task details as related to whether a limited user has an the access or not. This will definitely help you while building customized security roles for your limited users.

 

 

 

USE DYNAMICS
SELECT  RTRIM(PRODNAME) ProductName ,
        RTRIM(TYPESTR) Type ,
        RTRIM(Series_Name) AS Series ,
        RTRIM(DSPLNAME) DisplayName ,
        RESTECHNAME AS Resource_Technical_Name,
        CASE AvailLmtdUsrs
           WHEN 1 THEN 'Yes'
           ELSE 'No'
        END AS AvailableToLimitedUser
        --  SECRESTYPE SecurityResourceType,
        --     DICTID AS DictionaryID,
        --  SECURITYID SecyrityID ,
FROM    SY09400
ORDER BY 1,2,3,4

 

Security Resource Description – Excel Report

In order to have the maximum benefit of the script above, you can drop it into Excel and have a pivot table report, as shown below:

Here is the filter to consider:

Here is the Pivot Report:

You can download the Security Resource Excel report from Here

Best Regards,
Mahmoud M. AlSaadi

Unhandled script exception “Value of range” error when Running Receivables Reconciliation

When attempting to run the sales reconciliation; specifically for the “Current Customer Information”
an unhandled script exception [Value of Range]
Exception_Class_Script_Out_of_Range_Script_CMD_Selected.

 

As you proceed, when pressing the “Process”, you will get the error stated below:

Resolution
This is most likely caused by a missing statement cycle definition on the customer card, as shown below:

• Go to Cards > Sales > Customer
• On the customer card , press on the options button
• On the customer option window, check the “Statement Cycle”. If it is empty, choose one of the predefined options (monthly, weekly …etc)
  

For a mass update, you can consider the update of this option on the SQL level by running a simple update statement

To identify the customers with no statement cycle definition, run the script below:

SELECT  STMTCYCL AS StatementCycle ,
        CUSTNMBR
FROM    dbo.RM00101
WHERE   STMTCYCL = 0

To correct a bulk of customers, you can consider the following update statement:

UPDATE  dbo.RM00101
SET     STMTCYCL = StatementCycleValue
WHERE   CUSTCLAS IN ( )

 Statement Cycle values are:

1 NO STATEMENT
2 Weekly
3 Biweekly
4 SemiMonthly
5 Monthly
6 BiMonthly
7 Quarterly

If all the above doesn’t apply to resolving this error, you may need to check missing records in RM tables (RM00101,RM00102, RM00103, RM00104 and RM00106). Refer to Support Article ID: 851191 for further in-depth details

Best Regards,
Mahmoud M. AlSaadi

Analytical Accounting Excel-Based Macro Generator – Beta Testers Needed

Analytical Accounting journal entry has always been a complex area from a data entry perspective especially when it comes to mass JE’s with analytical details. In this essence, AA Macro-generator tool is designed to provide an added value and help the users in this specific area.

In this post, I am proposing a new approach for data entry in Dynamics GP by enhancing the use of both Dynamics GP Macros and MS Excel. This tool will be totally free and will be published with its full functionality on this blog.

Since it has been initially done for the community, having it tested by the great folks out there comes as an essential step before having it publicly shared.

 

 

 

 

 

 

 

 

If you are willing to be part of the beta test, please drop me an e-mail on MahmoodSaadi@Live.com and you will be notified of the details once enough testers are involved.

Best Regards,
Mahmoud M. AlSaadi