Recently in Convergence 2015, new promising features of Dynamics GP were introduced including an interesting one; All in one document viewer tool. The next coming release will start with the “Purchasing” all in one document viewer which include the Purchase Order, shipments, invoices and payments, according to an article published on MSDynamicsWorld quoted from Chad Sogge; R&D principal program manager speaking at Convergence 2015.
As related to this topic, GP Essentials blog started a series of attachment reports covering items, customers and vendors so far. In this post, I am providing a report for all the attachment stored in the system with associated details such as; module, screen name, attachment status, file type, attachment name …etc. and most importantly the “Size”, this is quite important in order to monitor the size of attachments in your database.
Here is the report details:
Tables included:
- CO00101
- CO00102
- CO00104
Field Definition | CO00101.ODESCTN
- CC - Customer Maintenance
- FO - Fulfillment Order
- IC - Item Maintenance
- PI - Purchasing Enter Match Invoice
- PO - Purchase Order
- PS - Purchasing Receiving Transaction Entry
- REQ - Requisition
- SI - Sales Invoice
- SO - Sales Order
- SQ - Sales Quotation
- Vendor - Vendor Maintenance
- PM - Payable Transaction
/*--------------------------------------------------------------------------
Creation Date: The 14th of April, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to list all attachments in Dynamics GP
Revision History:
Revision No. RevisionDate Description
1 14/04/2015 Original Version
--------------------------------------------------------------------------*/
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'
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
You may download the SQL Script from here >>> Download Link
Best Regards,
Mahmoud M. AlSaadi
