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