Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Thursday, October 30, 2014

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

Vendor Update

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     

DB_CDC Enabled

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

Table_CDC Enabled

 

 

 

 

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

TWO-CDC-Objects

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.

CDC_ItemMaster

 

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

Monday, October 20, 2014

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

IV Reconciliation FlowInventory  Reconciliation Flow – Summarized Representation

Point 2 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:

Lot Master

Serial Master

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:

ItemLot_Recon

ItemSerial Recon

 

2- Added quantity sold detail record quantity

Point 1 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:

Added Recon Record - IV10201

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

Friday, October 17, 2014

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

Thursday, October 16, 2014

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 - GP 2013 R2

 

 

 

 

 

 

 

 

 

 

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.

Security Resource Information - SQL View

 

 

 

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:

image

Here is the Pivot Report:

image

You can download the Security Resource Excel report from Here

Best Regards,
Mahmoud M. AlSaadi

Tuesday, October 14, 2014

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.

SalesReconciliation

 

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

Unhandled script

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)

Customers Options

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

Sunday, October 12, 2014

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.

 Cover

 

 

 

 

 

 

 

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