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