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

Sunday, December 10, 2017

Dynamics GP 2018 - Attachment Report – All Attachments ( Updated SQL Script )


In a previous post on Dynamics GP Essentials, an SQL Script was provided for the attachment details across all the modules. Now with the release of GP 2018, new enhancements have been introduced addressing powerful capabilities for the document attachment module in GP. 

In this essence, it is important to provide an updated SQL script which includes the new windows which have been covered by the recent enhancement



Document Attachment - Updated Script




SELECT
SUBSTRING(RTRIM(LTRIM(BusObjKey)),
First_Slash + 1,
Second_Slash - First_Slash - 1)
AS Module,
SUBSTRING(RTRIM(LTRIM(BusObjKey)),
Second_Slash + 1, Third_Slash - Second_Slash - 1)
AS Screen,
CASE ODESCTN
WHEN 'CC' THEN
'Customer Maintenance'
WHEN 'FO' THEN
'Fulfillment Order'
WHEN 'IC' THEN
'Item Maintenance'
WHEN 'PI' THEN
'Purchasing Enter Match Invoice'
WHEN 'PO' THEN
'Purchase Order'
WHEN 'PS' THEN
'Purchasing Receiving Transaction Entry'
WHEN 'REQ' THEN
'Requisition'
WHEN 'SI' THEN
'Sales Invoice'
WHEN 'SO' THEN
'Sales Order'
WHEN 'SQ' THEN
'Sales Quotation'
WHEN 'Vendor' THEN
'Vendor Maintenance'
WHEN 'PM' THEN
'Payable Transaction'
WHEN 'GL' THEN
'General Ledger Transaction Entry'
WHEN 'RM' THEN
'Receivable Transaction Entry'
ELSE
''
END AS Entity_Description,
AttachmentStatus,
CASE SUBSTRING(RTRIM(LTRIM(BusObjKey)), Second_Slash + 1, Third_Slash - Second_Slash - 1)
WHEN 'Payables Transaction' THEN
REPLACE(
REPLACE(
SUBSTRING(RTRIM(LTRIM(BusObjKey)), Third_Slash + 2, Fourth_Slash - Third_Slash),
'\',
''
),
'~',
''
)
WHEN 'Sales Order' THEN
REPLACE(
REPLACE(
SUBSTRING(RTRIM(LTRIM(BusObjKey)), Third_Slash + 2, Fourth_Slash - Third_Slash),
'\',
''
),
'~',
''
)
WHEN 'Sales Order Line' THEN
REPLACE(
REPLACE(
SUBSTRING(RTRIM(LTRIM(BusObjKey)), Third_Slash + 2, Fourth_Slash - Third_Slash),
'\',
''
),
'~',
''
)
ELSE
REPLACE(
REPLACE(
SUBSTRING(RTRIM(LTRIM(BusObjKey)), Third_Slash + 1, Fourth_Slash - Third_Slash),
'\',
''
),
'~',
''
)
END AS 'Card/Transaction Number',
DOCNUMBR AS DocumentNumber,
fileName,
FileType,
Size,
CASE SEQNumberIndex
WHEN 0 THEN
''
ELSE
RIGHT(RTRIM(LTRIM(BusObjKey)), LEN(RTRIM(LTRIM(BusObjKey))) - SEQNumberIndex)
END AS LineSequenceNumber,

--ORD ,

CASE ISNULL(DELETE1, '')
WHEN 1 THEN
'Yes'
ELSE
'No'
END AS Deleted,
A.CRUSRID AS CreatedUser,
A.CREATDDT AS CreatedDate,
A.CREATETIME AS CreatedTime,
AllowAttachmentFlow,
ODESCTN,
Deletable,
Replaced_Attachment,
BusObjKey,
A.Attachment_ID
FROM
(
SELECT CHARINDEX('\', RTRIM(X.BusObjKey), 1) First_Slash,
CHARINDEX('\', RTRIM(X.BusObjKey), CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1) Second_Slash,
CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX('\', RTRIM(X.BusObjKey), CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1) + 1
) Third_Slash,
CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX('\', RTRIM(X.BusObjKey), CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1) + 1
) + 1
) AS Fourth,
CASE SUBSTRING(
RTRIM(LTRIM(X.BusObjKey)),
CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1,
CHARINDEX('\', RTRIM(X.BusObjKey), CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1)
- CHARINDEX('\', RTRIM(X.BusObjKey), 1) - 1
)
WHEN 'Sales' THEN
CASE CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX(
    '\',
    RTRIM(X.BusObjKey),
    CHARINDEX(
                '\',
                RTRIM(X.BusObjKey),
                CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1
            ) + 1
) + 1
)
WHEN 0 THEN
0
ELSE
CHARINDEX(
'~',
RTRIM(X.BusObjKey),
CHARINDEX(
        '\',
        RTRIM(X.BusObjKey),
        CHARINDEX(
                    '\',
                    RTRIM(X.BusObjKey),
                    CHARINDEX(
                                '\',
                                RTRIM(X.BusObjKey),
                                CHARINDEX('\', RTRIM(X.BusObjKey), 1)
                                + 1
                            ) + 1
                ) + 1
    )
) + 2
END
WHEN 'PM' THEN
CASE CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX(
    '\',
    RTRIM(X.BusObjKey),
    CHARINDEX(
                '\',
                RTRIM(X.BusObjKey),
                CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1
            ) + 1
) + 1
)
WHEN 0 THEN
0
ELSE
CHARINDEX(
'~',
RTRIM(X.BusObjKey),
CHARINDEX(
        '\',
        RTRIM(X.BusObjKey),
        CHARINDEX(
                    '\',
                    RTRIM(X.BusObjKey),
                    CHARINDEX(
                                '\',
                                RTRIM(X.BusObjKey),
                                CHARINDEX('\', RTRIM(X.BusObjKey), 1)
                                + 1
                            ) + 1
                ) + 1
    )
)
END
ELSE
''
END AS SEQNumberIndex,
CASE CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX('\', RTRIM(X.BusObjKey), CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1)
+ 1
) + 1
)
WHEN 0 THEN
LEN(RTRIM(LTRIM(X.BusObjKey)))
ELSE
CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX(
'\',
RTRIM(X.BusObjKey),
CHARINDEX(
            '\',
            RTRIM(X.BusObjKey),
            CHARINDEX('\', RTRIM(X.BusObjKey), 1) + 1
        ) + 1
) + 1
)
END AS Fourth_Slash,
X.BusObjKey,
X.Attachment_ID,
X.CRUSRID,
X.CREATDDT,
X.CREATETIME,
HISTRX,
AllowAttachmentFlow,
X.DELETE1,
AllowAttachmentEmail,
AttachmentOrigin,
X.DEX_ROW_ID,
DOCNUMBR,
STRTDSCR,
FileType,
Size,
ORD,
Z.AttachmentStatus
FROM dbo.CO00102 AS X
LEFT OUTER JOIN CO00105 AS Y
ON X.Attachment_ID = Y.Attachment_ID
AND X.BusObjKey = Y.BusObjKey
LEFT OUTER JOIN CO00104 AS Z
ON X.Attachment_ID = Z.Attachment_ID
AND X.BusObjKey = Z.BusObjKey
) AS A
LEFT OUTER JOIN CO00101 AS B
              ON A.Attachment_ID = B.Attachment_ID;


Best Regards,
Mahmoud M. AlSaadi