Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Monday, November 10, 2014

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:

Vendor Attachment

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 

No comments:

Post a Comment