32,738
Like
Share
ReportIn 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
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
Mahmoud M. AlSaadi
*This post is locked for comments