Skip to main content

Notifications

Dynamics 365 Community / Blogs / Dynamics GP Essentials / Attachment Report – All Att...

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

Comments

*This post is locked for comments