GP 2013 R2 – Copy paste GL journals from Excel “with Analytical Accounting Details”

With the release of GP 2013 R2, a nice feature was introduced which covers a widely required need, it is the ability to copy journal entries from Excel into Dynamics GP, General Ledger Transaction Entry. In this post, I want to shed a light on the “Analytical Accounting” part of this feature.

In the following testing scenario, two transaction dimensions are defined which are location and Project. The dimension codes of each transaction dimension is illustrated in the graph below:

 

Now the next step is to define AA alias, which is a required field for the “Copy Paste” feature. An alias is created to group the transaction dimension codes combinations to be used when entering the AA details on the GL journal entry window.

In our testing scenario, the possible combinations required could be:

• Location (Local) with Project (A)
• Location (Local) with Project (B)
• Location (Export) with Project (A)
• Location (Export) with Project (B)

To create an Alias, Go to Cards > Financial > Analytical Accounting > Alias

Now we will proceed with the required fields of the data set on the Excel Sheet. These fields are :

• Distribution Reference
• Account Number
• Debit
• Credit
• AA Alias

After the data set on the Excel Sheet is ready to be entered into Dynamics GP, highlight it (excluding the columns) and click on Copy option. Then Go to GL transaction Entry window, consider the following points before pasting

• You should fill the header details like (JE number, JE reference, JE Date ….etc)
• Put the mouse cursor on the first line of the grid, in the Account number field
• Then, click Paste icon

When posting the Journal Entry, the General Ledger and AA posting reports will print on the screen, these reports are provided below:

    

     

 

 

Best Regards,

Mahmoud M. AlSaadi

Attachments Report – Item Card

With the new attachment functionality in Dynamics GP, attachments to primary GP cards are enabled. What if I want to see all my cards with their associated attachments in one report, rather than scrolling through all the cards. In this post, an SQL script to retrieve attachment report for “Item Cards”, the same logic can be applied to retrieve Attachment report for other GP cards.

Tables Included:

• IV00101 | Item Master
• CO00101 | Document Attachment Master
• CO00102 | Document Attachment Reference

 

SELECT  A.ITEMNMBR ,
        A.ITEMDESC ,
        CASE ISNULL(B.ItemNumber,'')
          WHEN '' THEN 'No'
          ELSE 'Yes'
        END Attachment,
        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.IV00101 AS A
        LEFT OUTER JOIN
        (
             SELECT    X.ItemNumber,
                       Y.fileName,
                       X.AllowAttachmentFlow,
                       X.AllowAttachmentEmail,
                       X.AttachmentOrigin,
                       X.DELETE1,
                       X.CreatedDate,
                       X.CreatedUser
             FROM     
                       (
                               SELECT    RIGHT(RTRIM(BusObjKey),
                               LEN(BusObjKey)-
                                    ( CHARINDEX('Item Maintenance',
                               RTRIM(BusObjKey), 1) + 
                                  LEN('Item Maintenance') )) 
                                  AS ItemNumber,
                               Attachment_ID,
                               AllowAttachmentFlow,
                               AllowAttachmentEmail,
                               AttachmentOrigin,
                               DELETE1,
                               CREATDDT AS CreatedDate,
                               CRUSRID AS CreatedUser
                               FROM    CO00102
                               WHERE   BusObjKey 
                                       LIKE '%Item Maintenance%'
                       ) AS X
                         LEFT OUTER JOIN CO00101 AS Y 
                         ON X.Attachment_ID = Y.Attachment_ID
        ) AS B ON A.ITEMNMBR = B.ItemNumber
        ORDER BY Attachment DESC

Best Regards,
Mahmoud M. AlSaadi

Payment Document Management – Performance Issues

Regardless of all the advantages provided by the payment document management module, several performance issues are commonly encountered which make it a bit undesired for end-users. Some of these performance issues are:

• Only one bank transfer document is posted for the remittance, for instance, when settling several cash receipts only one bank transfer is posted on the bank module.
• The GL journal entries posted for each remittance don’t have a proper distribution reference to be linked for each cash receipt, which makes reconciliation a difficult process
• Common posting corruption which leads to “received” cash receipts shown as “saved”

These issues and many others have been illustrated in an old support article 2468048 Performance issues with manual payments in Payables Management or cash receipts in Receivables Management in Microsoft Dynamics GP

In this post, a temporary work around solution is provided to handle the “posting corruption” which usually occurs when posting cash receipts. The cash receipt remains as “saved” while it is actually “received”. Technically speaking, the data has not been moved from the “work” to the “open” table.

The script below is only applied when “saved” cash receipts are stuck in the work table and not migrated to the open table. Never use it for any other posting corruption scenarios for PDC.

INSERT  INTO RVLPD009

        ( RMDTYPAL ,

          DOCNUMBR ,

          CUSTNMBR ,

          CUSTNAME ,

          STMTNAME ,

          PMTDOCID ,

          CHEKNMBR ,

          PDOCSTRM ,

          CHEKBKID ,

          DOCDATE ,

          DUEDATE ,

          DOCAMNT ,

          ORDOCAMT ,

          CURNCYID ,

          CURRNIDX ,

          TRXDSCRN ,

          NOTEINDX ,

          VALSTRG1 ,

          VALSTRG2 ,

          VALSTRG3 ,

          VALSTRG4 ,

          VALSTRG5 ,

          VALSTRG6 ,

          VALDATE1 ,

          VALDATE2 ,

          VALCURR1 ,

          VALCURR2 ,

          VALBACT1 ,

          VALBACT2 ,

          TRXSORCE ,

          MKDBYUSR ,

          MKTOPROC ,

          REMTYPE ,

          REMITID ,

          CMRecordNumber

        )

        SELECT  *

        FROM    ( SELECT    A.RMDTYPAL ,

                            A.DOCNUMBR ,

                            A.CUSTNMBR ,

                            A.CUSTNAME ,

                            A.STMTNAME ,

                            A.PMTDOCID ,

                            A.CHEKNMBR ,

                            1 AS PDOCSTRM ,

                            A.CHEKBKID ,

                            A.DOCDATE ,

                            A.DUEDATE ,

                            A.DOCAMNT ,

                            A.ORDOCAMT ,

                            A.CURNCYID ,

                            A.CURRNIDX ,

                            A.TRXDSCRN ,

                            A.NOTEINDX ,

                            A.VALSTRG1 ,

                            A.VALSTRG2 ,

                            A.VALSTRG3 ,

                            A.VALSTRG4 ,

                            A.VALSTRG5 ,

                            A.VALSTRG6 ,

                            A.VALDATE1 ,

                            A.VALDATE2 ,

                            A.VALCURR1 ,

                            A.VALCURR2 ,

                            A.VALBACT1 ,

                            A.VALBACT2 ,

                            B.TRXSORCE AS TRXSORCE ,

                            ' ' AS MKDBYUSR ,

                            0 AS MKTOPROC ,

                            0 AS REMTYPE ,

                            ' ' AS REMITID ,

                            0 AS CMRecordNumber

                  FROM      RVLPD008 AS A

                  LEFT OUTER JOIN RM20101 AS B ON A.DOCNUMBR = B.DOCNUMBR

                ) AS A

 

Best Regards,
Mahmoud M. AlSaadi

Payment Document Management – Post Dated Checks Report, SQL Script

In this post, an SQL script gathers all types of Post Dated Checks along with all the associated details. The report data set is illustrated in the table below:

 

 

 

Post dated checks types are:

• Saved, before the cash receipt is posted
• Received, when the cash receipt is posted
• Collected, when the cash receipt is collected

 

Tables Included:

                     ·         RVLPD008 | Sales Payment Document Work

                     ·         RVLPD009 | Sales Payment Document Open

 

SELECT  'Saved' AS Document_Status ,

        DOCNUMBR AS [Cash Receipt Number] ,

        CUSTNMBR AS [Customer Number] ,

        CUSTNAME AS [Customer Name] ,

        PMTDOCID AS [Payment Document ID] ,

        CHEKNMBR AS [Check Number] ,

        CHEKBKID AS [Checkbook],

        DOCDATE AS [Document Date] ,

        DUEDATE AS [Check Due Date] ,

        DOCAMNT AS [Check Amount]

FROM    dbo.RVLPD008

WHERE   PDOCSTRM = 1

UNION ALL

SELECT  'Collected' AS Document_Status ,

        DOCNUMBR AS [Cash Receipt Number] ,

        CUSTNMBR AS [Customer Number] ,

        CUSTNAME AS [Customer Name] ,

        PMTDOCID AS [Payment Document ID] ,

        CHEKNMBR AS [Check Number] ,

        CHEKBKID AS Checkbook ,

        DOCDATE AS [Document Date] ,

        DUEDATE AS [Check Due Date] ,

        DOCAMNT AS [Check Amount]

FROM    dbo.RVLPD009

WHERE   PDOCSTRM = 4

UNION ALL

SELECT  'Received' AS Document_Status ,

        DOCNUMBR AS [Cash Receipt Number] ,

        CUSTNMBR AS [Customer Number] ,

        CUSTNAME AS [Customer Name] ,

        PMTDOCID AS [Payment Document ID] ,

        CHEKNMBR AS [Check Number] ,

        CHEKBKID AS Checkbook ,

        DOCDATE AS [Document Date] ,

        DUEDATE AS [Check Due Date] ,

        DOCAMNT AS [Check Amount]

FROM    dbo.RVLPD009

WHERE   PDOCSTRM = 1

 

Best Regards,
Mahmoud M. AlSaadi

GP 2013 R2 – Reverse Historical Year

With the release of GP 2013 R2, you can reverse the End Year closing of historical years so that you can open fiscal years. In this post, this new feature will be illustrated in terms of the major and minor considerations.

Through the following scenario, the year (2014) is to be closed and the Year End closing report is generated as shown below.

The following journal entry is generated when closing the year JE No. 3451

 

Reverse Historical Year

GO To Microsoft Dynamics GP > Tools > Routines > Financial > Year End Closing

 

 

The following considerations are essentials when “reversing” the closing:

• You should reverse the most recent historical year first
• As usually, no users should be logged in Dynamics GP.
• The closing journal entry (In our example above, JE No. 3451 ) will be deleted not voided.
• Historical Analytical Accounting details will be deleted from the history tables, and moved to open tables
• Account summary balances will be recalculated (history and open) will be recalculated

Helping Note !

Back up is a must before reversing the historical year. Additionally, you should reconcile all open year. You get warning messages to do so.

Just before reversing the year

Just after reversing the year

 

Technical perspective :

Reverse historical year is performed through a new stored procedure added in GP 2013 R2 named [GLReverseHistYear] in the company database. While checking this stored procedure, the following tables are primarily affected by this process:

• GL20000 | Year to date transaction open
• GL20001
• GL10110 | Account Current Summary Master
• GL10111 | Account Summary History
• GL30000 | Account transaction history
• GL30001
• MC30001 | Multicurrency Account summary history
• MC00201 | Multicurrency Account summary open
• AAG40000 | GL distribution header
• AAG40001 | GL distribution
• AAG40002 | GL History assignment
• AAG40003 | GL History code
• AAG30000 | GL Header
• AAG30001 | GL Distribution
• AAG30002 | GL Assign
• AAG30003 | GL Code

 

Best Regards,
Mahmoud M. AlSaadi

GP 2013 R2 – Attach Documents to Notes

In Dynamics GP 2013 R2, you can consider the document attachment full functionality on the Notes level instead of using OLE objects. Let’s consider the following scenario of adding an attachment on the note field of the vendor maintenance window.

On the vendor card window, click on the notes field

On the note window, click on the attach icon to reach the Document Attachment Management window

 

Benefits of this feature:

• Attachments are stored on the SQL Server database
• Attachments are regularly backed up along with the Dynamics GP data

 

Best Regard,
Mahmoud M. AlSaadi

Simplicity is the Secret - Vote for Multi Dimensional Analysis (MDA)

It’s been a long vacation for me and now I am back on track. I initially went through the most recent uprising against Analytical Accounting (AA) by Dynamics GP MVPs favored by the Multi Dimensional Analysis (MDA)

In this essence, I recall an old knowledge base article illustrating the various and unlimited features and functionality provided by AA, some of which are not even available in MDA. Support Article ID : 935623

Multi Dimensional Analysis has been put on the shelf for long time to push Analytical Accounting as a better and yet more comprehensive solution. Meanwhile, AA has been a victim of complexity as these various features and functionality encountered several potential issues which always require dedication and hard work to be solved.

Simplicity is the secret, vote on Microsoft Connect to Bring MDA back to Dynamics GP

 

Best Regards,
Mahmoud M. AlSaadi