Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

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