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...

Monday, April 13, 2015

Attachment Report – All Attachments (Cards and Transactions)


Recently in Convergence 2015, new promising features of Dynamics GP were introduced including an interesting one; All in one document viewer tool. The next coming release will start with the “Purchasing” all in one document viewer which include the Purchase Order, shipments, invoices and payments, according to an article published on MSDynamicsWorld quoted from Chad Sogge; R&D principal program manager speaking at Convergence 2015. 

As related to this topic, GP Essentials blog started a series of attachment reports covering items, customers and vendors so far. In this post, I am providing a report for all the attachment stored in the system with associated details such as; module, screen name, attachment status, file type, attachment name …etc. and most importantly the “Size”, this is quite important in order to monitor the size of attachments in your database.

Here is the report details:

Attachment Report

Tables included:

  • CO00101
  • CO00102
  • CO00104

 

Field Definition | CO00101.ODESCTN

  • CC - Customer Maintenance
  • FO - Fulfillment Order
  • IC - Item Maintenance
  • PI - Purchasing Enter Match Invoice
  • PO - Purchase Order
  • PS - Purchasing Receiving Transaction Entry
  • REQ - Requisition
  • SI - Sales Invoice
  • SO - Sales Order
  • SQ - Sales Quotation
  • Vendor - Vendor Maintenance
  • PM - Payable Transaction

 

/*--------------------------------------------------------------------------

Creation Date: The 14th of April, 2015

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to list all attachments in Dynamics GP

 

Revision History:

Revision No.            RevisionDate    Description

1                       14/04/2015      Original Version

--------------------------------------------------------------------------*/

 

SELECT  SUBSTRING(RTRIM(LTRIM(BusObjKey)), First_Slash + 1,

                  Second_Slash - First_Slash - 1) AS Module ,

        SUBSTRING(RTRIM(LTRIM(BusObjKey)), Second_Slash + 1,

                  Third_Slash - Second_Slash - 1) AS Screen ,

        CASE ODESCTN

          WHEN 'CC' THEN 'Customer Maintenance'

          WHEN 'FO' THEN 'Fulfillment Order'

          WHEN 'IC' THEN 'Item Maintenance'

          WHEN 'PI' THEN 'Purchasing Enter Match Invoice'

          WHEN 'PO' THEN 'Purchase Order'

          WHEN 'PS' THEN 'Purchasing Receiving Transaction Entry'

          WHEN 'REQ' THEN 'Requisition'

          WHEN 'SI' THEN 'Sales Invoice'

          WHEN 'SO' THEN 'Sales Order'

          WHEN 'SQ' THEN 'Sales Quotation'

          WHEN 'Vendor' THEN 'Vendor Maintenance'

          WHEN 'PM' THEN 'Payable Transaction'

          ELSE ''

        END AS Entity_Description ,

        AttachmentStatus ,

        CASE SUBSTRING(RTRIM(LTRIM(BusObjKey)),

                    Second_Slash + 1,

                       Third_Slash - Second_Slash - 1)

          WHEN 'Payables Transaction'

          THEN REPLACE

                           (REPLACE(SUBSTRING(RTRIM(LTRIM(BusObjKey)),

                 Third_Slash + 2,

                 Fourth_Slash -

                            Third_Slash), '\', ''),

                 '~', '')

          WHEN 'Sales Order'

          THEN REPLACE(REPLACE

                           (SUBSTRING(RTRIM(LTRIM(BusObjKey)),

                           Third_Slash + 2,

                           Fourth_Slash - Third_Slash), '\', ''),

                '~', '')

          WHEN 'Sales Order Line'

          THEN REPLACE(REPLACE

                           (SUBSTRING(RTRIM(LTRIM(BusObjKey)),

                Third_Slash + 2,

                Fourth_Slash - Third_Slash), '\', ''),

                '~', '')

          ELSE REPLACE(

                           REPLACE(SUBSTRING(RTRIM(LTRIM(BusObjKey)),

                Third_Slash + 1,

                Fourth_Slash - Third_Slash), '\', ''),

                       '~', '')

        END AS 'Card/Transaction Number' ,

        DOCNUMBR AS DocumentNumber ,

        fileName ,

        FileType ,

        Size ,

        CASE SEQNumberIndex

          WHEN 0 THEN ''

          ELSE RIGHT(RTRIM(LTRIM(BusObjKey)),

                     LEN(RTRIM(LTRIM(BusObjKey))) - SEQNumberIndex)

        END AS LineSequenceNumber ,

              --ORD ,

        CASE ISNULL(DELETE1, '')

          WHEN 1 THEN 'Yes'

          ELSE 'No'

        END AS Deleted ,

        A.CRUSRID AS CreatedUser ,

        A.CREATDDT AS CreatedDate ,

        A.CREATETIME AS CreatedTime ,

        AllowAttachmentFlow ,

        ODESCTN ,

        Deletable ,

        Replaced_Attachment ,

        BusObjKey ,

        A.Attachment_ID

FROM    ( SELECT   

                     CHARINDEX('\', RTRIM(X.BusObjKey), 1) First_Slash ,

            CHARINDEX('\', RTRIM(X.BusObjKey),

            CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1) Second_Slash ,

            CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey), 1)

                     + 1) + 1) Third_Slash ,

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1) AS Fourth ,

                     CASE SUBSTRING(RTRIM(LTRIM(X.BusObjKey)),

                     CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1,

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     1) + 1) - CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) - 1)

                     WHEN 'Sales'

                     THEN CASE CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1)   

                     WHEN 0 THEN 0

                     ELSE CHARINDEX('~', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1))+ 2

                     END

            WHEN 'PM'

                     THEN CASE CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1)

                     WHEN 0 THEN 0

                     ELSE CHARINDEX('~', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1))

                     END

                     ELSE ''

                     END AS SEQNumberIndex ,

                     CASE CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1)

                     WHEN 0 THEN LEN(RTRIM(LTRIM(X.BusObjKey)))

                     ELSE CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\', RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     CHARINDEX('\',

                     RTRIM(X.BusObjKey),

                     1) + 1) + 1) + 1)

                    END AS Fourth_Slash ,

                    X.BusObjKey ,

                    X.Attachment_ID ,

                    X.CRUSRID ,

                    X.CREATDDT ,

                    X.CREATETIME ,

                    HISTRX ,

                    AllowAttachmentFlow ,

                    X.DELETE1 ,

                    AllowAttachmentEmail ,

                    AttachmentOrigin ,

                    X.DEX_ROW_ID ,

                    DOCNUMBR ,

                    STRTDSCR ,

                    FileType ,

                    Size ,

                    ORD ,

                    Z.AttachmentStatus

          FROM      dbo.CO00102 AS X

                    LEFT OUTER JOIN CO00105 AS Y

                                  ON X.Attachment_ID = Y.Attachment_ID

                   AND X.BusObjKey = Y.BusObjKey

                    LEFT OUTER JOIN CO00104 AS Z

                                  ON X.Attachment_ID = Z.Attachment_ID

                              AND X.BusObjKey = Z.BusObjKey

        ) AS A

        LEFT OUTER JOIN CO00101 AS B

              ON A.Attachment_ID = B.Attachment_ID

             

 

You may download the SQL Script from here >>> Download Link

 

Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment