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...

Wednesday, November 26, 2014

Attachment Report – Customer Card

 

Proceeding with the attachment reports provided for the various cards in Dynamics GP, this post extends the attachment reports to include the customer card. Here is the data set result:

Customer Attachment 

-- Tables Included:

-- RM00101 | Customer Master
-- CO00101 | Document Attachment Master
-- CO00102 | Document Attachment Reference  

SELECT  A.CUSTNMBR,
        A.CUSTNAME ,
        CASE ISNULL(B.CUSTNMBR, '')
         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.RM00101 AS A
        LEFT OUTER JOIN ( SELECT X.CUSTNMBR ,
                                 Y.fileName ,
                                 X.AllowAttachmentFlow ,
                                 X.AllowAttachmentEmail ,
                                 X.AttachmentOrigin ,
                                 X.DELETE1 ,
                                 X.CreatedDate ,
                                 X.CreatedUser
                          FROM (
                                 SELECT RIGHT(RTRIM(BusObjKey),
                                        LEN(BusObjKey)
                                        - ( CHARINDEX('Customer Maintenance',
                                        RTRIM(BusObjKey),1)
                                        + LEN('Customer Maintenance') ))
                                        AS CUSTNMBR ,
                                        Attachment_ID ,
                                        AllowAttachmentFlow ,
                                        AllowAttachmentEmail ,
                                        AttachmentOrigin ,
                                        DELETE1 ,
                                        CREATDDT AS CreatedDate ,
                                        CRUSRID AS CreatedUser
                                        FROM      CO00102
                                        WHERE
                                        BusObjKey LIKE '%Customer Maintenance%'
                                ) AS X
                                LEFT OUTER JOIN CO00101 AS Y
                                ON X.Attachment_ID = Y.Attachment_ID
                        ) AS B ON A.CUSTNMBR = B.CUSTNMBR
ORDER BY Attachment DESC

 

Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment