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

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