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

1 comment:

  1. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you 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

    ReplyDelete