Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

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