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
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
Programmed ATM Cards
ReplyDeleteDo 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
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