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

Monday, April 13, 2015

Attachment Report – All Attachments (Cards and Transactions)


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:

Attachment Report

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

3 comments:

  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
  2. Programmed ATM Cards
    Do you know that you can hack any ATM machine !!!
    We have specially programmed ATMs that can be used to withdraw money at ATMs, shops and points of sale. We sell these cards to all our customers and interested buyers all over the world, the cards have a withdrawal limit every week.
    Getting rich and living the rich and famous lifestyle is a dream of many people. And while most people go to work or look for other ethical methods to make money on ATM-programmed cards.
    The programmed ATMs withdraw money from each ATM but have a withdrawal limit every week, only your PIN code is in it, it is a high-tech card system. The PROGRAMMED ATM card works on all card-based ATMs, anywhere in the world.
    Email: atmservices44@aol.com
    Email: hacklords.investors@gmail.com

    ReplyDelete
  3. I want to tell the worlds, that the illuminati is real because i have see the benefit of being a member of illuminati, i have tried to join the illuminati some many times, i have been scammed alot of money, but i did not give up because i know if i find the real illuminati, my life will be changed forever so i did not stop, on till i find the real illuminati official email and phone number, if you want to join the real illuminati, i want you to know that i can direct you to the real illuminati official email and phone number, brothers/sisters who is interested or who have be scammed so many times like me, that still have interest to join the illuminati because you believed that if you join your life will be changed, you do not have to worry about anything because i am here to show you all the right way to join the real illuminati, i want you all to know that when i was totally initiated to the illuminati, i was given so many benefits as a new member, i can tell you some of the benefits i was give an instant $1,000,000.00 to start a new life, house of your choice car of your choice, brothers/sisters that have interest to join the illuminati, this is the real illuminati official ( email: lw900325@gmail.com ) and the illuminati phone call center or WhatsApp number is (+2348119132137) you can contact the illuminati official email or WhatsApp number of how to join, the illuminati.🔺👁️🔺

    ReplyDelete