Attachment Report – Customer Card

 

Proceeding with the attachment reports provided for the various cards in Dynamics GP, this post extends the attachment reports to include the customer card. Here is the data set result:

 

-- Tables Included:

-- RM00101 | Customer Master
-- CO00101 | Document Attachment Master
-- CO00102 | Document Attachment Reference  

SELECT  A.CUSTNMBR,
        A.CUSTNAME ,
        CASE ISNULL(B.CUSTNMBR, '')
         WHEN '' THEN 'No'
         ELSE 'Yes'
        END Attachment ,
        ISNULL(SUBSTRING(filename, CHARINDEX('.', fileName, 1) + 1,
        LEN(filename) - CHARINDEX('.', fileName, 1) + 1), '')
        AS FileExtension ,
        ISNULL(FILENAME, ' ') AS 'FILE NAME' ,
        ISNULL(B.CreatedDate, '') AS CreatedDate ,
        ISNULL(B.CreatedUser, '') AS CreatedUser ,
        CASE ISNULL(B.DELETE1, '')
         WHEN 1 THEN 'Yes'
         ELSE 'No'
        END AS Deleted
        FROM  dbo.RM00101 AS A
        LEFT OUTER JOIN ( SELECT X.CUSTNMBR ,
                                 Y.fileName ,
                                 X.AllowAttachmentFlow ,
                                 X.AllowAttachmentEmail ,
                                 X.AttachmentOrigin ,
                                 X.DELETE1 ,
                                 X.CreatedDate ,
                                 X.CreatedUser
                          FROM (
                                 SELECT RIGHT(RTRIM(BusObjKey),
                                        LEN(BusObjKey)
                                        - ( CHARINDEX('Customer Maintenance',
                                        RTRIM(BusObjKey),1)
                                        + LEN('Customer Maintenance') ))
                                        AS CUSTNMBR ,
                                        Attachment_ID ,
                                        AllowAttachmentFlow ,
                                        AllowAttachmentEmail ,
                                        AttachmentOrigin ,
                                        DELETE1 ,
                                        CREATDDT AS CreatedDate ,
                                        CRUSRID AS CreatedUser
                                        FROM      CO00102
                                        WHERE
                                        BusObjKey LIKE '%Customer Maintenance%'
                                ) AS X
                                LEFT OUTER JOIN CO00101 AS Y
                                ON X.Attachment_ID = Y.Attachment_ID
                        ) AS B ON A.CUSTNMBR = B.CUSTNMBR
ORDER BY Attachment DESC

 

Best Regards,
Mahmoud M. AlSaadi

Data Cleansing Essentials Series | Chart of Account Analysis “Unused Accounts”

Data cleansing is a wide topic that extends over a variety of issues related to master files specifically, it’s due to the fact that data is prone to “corruption or deformation” especially when the process of configuring master files is not properly managed. As derived from the above, Dynamics GP Essentials blog will start an extended series related to “Data Cleansing techniques” that could be useful either to diagnose, detect or resolve data cleansing issues. The series will provide theory, scripts, excel templates and any valuable information or tools that can be of an added value in this topic.

The starting point will be the General Ledger module with the Chart of Accounts (COA) which could possibly have a number of unused accounts. The script below is valuable as related to:

- Detecting which accounts that have never been used
- Detecting the last time the account was used in (years, months and days)

The difference between the current data and the last date at which the account was used is calculated and provided separately either in three different calculations (No of years, No of months and No of Years). Lets check the following scenarios:

Scenario One

An account is configured on (01-01-2012), and never been used. The time at which the report is generated is (01-11-2014)

• Never Used = Yes
• Not used since (In years) = 2 years
• Not used since (In Month) = 34 Months
• Not used since (In Days) = 1035 days

Scenario Two

An account is configured on (01-01-2012), and was last used (31-12-2012). The time at which the report is generated is (01-11-2014)

• Never Used = No
• Last used = 31-12-2012
• Not used since (In years) = 2 years
• Not used since (In Month) = 23 Months
• Not used since (In Days) = 670 days

The data provided above can play a crucial role in determining which accounts to keep and which account to remove. The business should keep precise parameters which are considered as the main driver of the correction process.

-- Tables Included:

• GL00100 | Account Master
• GL00105 | Account Index Master
• GL20000 | Year-to-Date Transaction Open
• GL30000 | Account Transaction History

 

SELECT  B.ACTNUMST 'Account Number' ,
        A.ACTDESCR 'Account Description' ,
        A.CREATDDT 'Created Date' ,
        CASE
           WHEN ISNULL(D.TRX_Date, 0) < A.CREATDDT
           THEN 'Yes'
           ELSE ''
        END AS NeverUsed ,
        ISNULL(D.TRX_Date, 0) AS 'Last Used' ,
        DATEDIFF(YY, ISNULL(D.TRX_Date, 0), GETDATE())
            'Not Used Since (In Years)' ,
        DATEDIFF(MM, ISNULL(D.TRX_Date, 0), GETDATE())
            'Not Used Since (In Months)' ,
        DATEDIFF(DD, ISNULL(D.TRX_Date, 0), GETDATE())
            'Not Used Since (In Days)'
        FROM    dbo.GL00100 AS A
            LEFT OUTER JOIN dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX
            LEFT OUTER JOIN ( SELECT ACTINDX ,
                                     MAX(TRX_Date) TRX_Date
                                     FROM ( SELECT    ACTINDX ,
                                            MAX(TRXDATE) AS TRX_Date
                                            FROM      dbo.GL20000
                                            GROUP BY  ACTINDX
                                            UNION ALL
                                            SELECT    ACTINDX ,
                                            MAX(TRXDATE) AS TRX_Date
                                            FROM      dbo.GL30000
                                            GROUP BY  ACTINDX
                                           ) AS C
                                     GROUP BY  C.ACTINDX
                            ) AS D ON B.ACTINDX = D.ACTINDX

Best Regards,
Mahmoud M. AlSaadi

Attachment Report – Vendor Card

In a previous post, an attachment report for the item cards was provided listing down the important details such as attachment name, created by and date ..etc. This post extends the attachment report to cover the vendor card.

Note | A new piece of information was added which is the “File Extension”, whether it is a pdf, txt, jpeg, xlsx, docx …etc. It is really useful for categorization and search purposes.

Here is the data set result:

Tables Included:

• PM00200 | Vendor Master
• CO00101 | Document Attachment Master
• CO00102 | Document Attachment Reference

 

SELECT  A.VENDORID ,
        A.VENDNAME ,
        CASE ISNULL(B.VENDORID, '')
          WHEN '' THEN 'No'
        ELSE 'Yes'
        END Attachment ,
        ISNULL(SUBSTRING(filename, CHARINDEX('.', fileName, 1) + 1,
        LEN(filename) - CHARINDEX('.', fileName, 1) + 1), '')
        AS FileExtension ,
        ISNULL(FILENAME, ' ') AS 'FILE NAME' ,
        ISNULL(B.CreatedDate, '') AS CreatedDate ,
        ISNULL(B.CreatedUser, '') AS CreatedUser ,
        CASE ISNULL(B.DELETE1, '')
          WHEN 1 THEN 'Yes'
          ELSE 'No'
        END AS Deleted
        FROM    dbo.PM00200 AS A
        LEFT OUTER JOIN ( SELECT X.VENDORID ,
                                 Y.fileName ,
                                 X.AllowAttachmentFlow ,
                                 X.AllowAttachmentEmail ,
                                 X.AttachmentOrigin ,
                                 X.DELETE1 ,
                                 X.CreatedDate ,
                                 X.CreatedUser
                                 FROM (
                                       SELECT RIGHT(RTRIM(BusObjKey),
                                       LEN(BusObjKey)
                                       - ( CHARINDEX('Vendor Maintenance',
                                       RTRIM(BusObjKey),1)
                                       + LEN('Vendor Maintenance') ))
                                       AS VENDORID ,
                                       Attachment_ID ,
                                       AllowAttachmentFlow ,
                                       AllowAttachmentEmail ,
                                       AttachmentOrigin ,
                                       DELETE1 ,
                                       CREATDDT AS CreatedDate ,
                                       CRUSRID AS CreatedUser
                                       FROM      CO00102
                                       WHERE
                                       BusObjKey LIKE '%Vendor Maintenance%'
                                       ) AS X
                                       LEFT OUTER JOIN CO00101 AS Y
                                       ON X.Attachment_ID = Y.Attachment_ID
                                       ) AS B ON A.VENDORID = B.VENDORID
ORDER BY Attachment DESC

Best Regards,
Mahmoud M. AlSaadi 

Analytical Accounting Entry (Excel to Dynamics GP) – Free Tool (Beta)

Entering analytical accounting journals is usually encountered with a lot of efforts as you have to go through several screens and options. When entering opening balances specifically, the journal with its associated details is quite difficult. From here, the idea to create an Excel-based macro generator tool was derived.

Throughout the previous month, the tool was published for testing and the feedback was quite positive.I highly appreciate the efforts taken by the community members who dedicated some time to help make this tool better.

Few modifications were applied and the tool is now open for the community to download and use.

Download Link >>>>>>>> AA Excel-Based Tool and Prerequisites

Video Tutorial is Provided below:

Tool Prerequisites:

In Brief, here are the tool considerations:

• SQL Scripts: you need to run the attached scripts which will in turn create SQL Views and Stored procedures
• MS Excel: the tool has been built and tested using MS Excel 2013, it contains functions, validations and VBA code embedded. Previous versions are not tested yet
• Connection String: fill in the connection string details in the Setup sheet in order to get connected.

Limitation:

• Accounting Class:The tool consider a standard setup for AA, in which all the dimension are linked to “one accounting class”
• Code Validation: The tool doesn’t support code validation and relations

Macro-Generator Tools - Future Releases

>> This tool is part of a series of Excel-Based Macro Generators to enhance the integration of data from Excel to Dynamics GP. The idea is to build a dynamics Macro that can be generated considering various parameters. Further Macro-Generator tools to be introduced include:

Cards:

• Item Card, Class, Sites, UOM
• Vendor Card, Class
• Account Maintenance (Chart of Account)
• Customer Card, Class

Transactions:

• Inventory Adjustment, Transfer
• Inventory Bill of Material Setup
• Receivable Transaction Entry
• Payable Transaction Entry
• Sales Transaction Entry – SOP
• Receiving Transaction Entry - POP

Your feedback is highly appreciated as part of the continuous improvements,

Best Regards,
Mahmoud M. AlSaadi

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