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
BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast
ReplyDeleteyou can be to get the new PROGRAMMED blank ATM card that is capable of
hacking into any ATM machine,anywhere in the world. I got to know about
this BLANK ATM CARD when I was searching for job online about a month
ago..It has really changed my life for good and now I can say I'm rich and
I can never be poor again. The least money I get in a day with it is about
$50,000.(fifty thousand USD) Every now and then I keeping pumping money
into my account. Though is illegal,there is no risk of being caught
,because it has been programmed in such a way that it is not traceable,it
also has a technique that makes it impossible for the CCTVs to detect
you..For details on how to get yours today, email the hackers on : (
atmmachinehackers1@gmail.com ). Tell your
loved once too, and start to live large. That's the simple testimony of how
my life changed for good...Love you all ...the email address again is ;
atmmachinehackers1@gmail.com