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:

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