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
