With the new attachment functionality in Dynamics GP, attachments to primary GP cards are enabled. What if I want to see all my cards with their associated attachments in one report, rather than scrolling through all the cards. In this post, an SQL script to retrieve attachment report for “Item Cards”, the same logic can be applied to retrieve Attachment report for other GP cards.
Tables Included:
- IV00101 | Item Master
- CO00101 | Document Attachment Master
- CO00102 | Document Attachment Reference
SELECT A.ITEMNMBR ,
A.ITEMDESC ,
CASE ISNULL(B.ItemNumber,'')
WHEN '' THEN 'No'
ELSE 'Yes'
END Attachment,
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.IV00101 AS A
LEFT OUTER JOIN
(
SELECT X.ItemNumber,
Y.fileName,
X.AllowAttachmentFlow,
X.AllowAttachmentEmail,
X.AttachmentOrigin,
X.DELETE1,
X.CreatedDate,
X.CreatedUser
FROM
(
SELECT RIGHT(RTRIM(BusObjKey),
LEN(BusObjKey)-
( CHARINDEX('Item Maintenance',
RTRIM(BusObjKey), 1) +
LEN('Item Maintenance') ))
AS ItemNumber,
Attachment_ID,
AllowAttachmentFlow,
AllowAttachmentEmail,
AttachmentOrigin,
DELETE1,
CREATDDT AS CreatedDate,
CRUSRID AS CreatedUser
FROM CO00102
WHERE BusObjKey
LIKE '%Item Maintenance%'
) AS X
LEFT OUTER JOIN CO00101 AS Y
ON X.Attachment_ID = Y.Attachment_ID
) AS B ON A.ITEMNMBR = B.ItemNumber
ORDER BY Attachment DESC
Best Regards,
Mahmoud M. AlSaadi
No comments:
Post a Comment