Dynamics GP: Deleting all document attachments for Payables Transactions

By Steve Endow

TOPIC:  Microsoft Dynamics GP


I'm working with a customer to automate the import of Document Attachments into Dynamics GP.

As part of the testing process, we are having to clear out all of the test attachments that have been imported so that we can re-import them again.

In case it's of interest to anyone, here are some SQL scripts I created to delete out ALL document attachments for ALL payables transactions.  You can modify the scripts to remove attachments for other record types.

Make sure to backup your company database before running any of these scripts.

If you find any mistakes or problems with these scripts, please let me know.



Here is a script to delete all Document Attach records for a single Payables Invoice.  This could be modified to work with a different record type.

DECLARE @voucher varchar(17)
DECLARE @AttachmentID varchar(37)
DECLARE @BusObjKey varchar(201)

SET @voucher = '00000000000000475'

SELECT @AttachmentID = Attachment_ID, @BusObjKey = BusObjKey FROM dbo.CO00105 WHERE DOCNUMBR = @voucher AND BusObjKey = '0\PM\Payables Transaction\1~' + @voucher

DELETE FROM dbo.CO00101 WHERE Attachment_ID = @AttachmentID
DELETE FROM dbo.CO00102 WHERE BusObjKey = @BusObjKey AND Attachment_ID = @AttachmentID
DELETE FROM dbo.CO00103 WHERE BusObjKey = @BusObjKey AND Attachment_ID = @AttachmentID
DELETE FROM dbo.CO00104 WHERE BusObjKey = @BusObjKey AND Attachment_ID = @AttachmentID
DELETE FROM dbo.CO00105 WHERE BusObjKey = @BusObjKey AND Attachment_ID = @AttachmentID
DELETE FROM dbo.coAttachmentItems WHERE Attachment_ID = @AttachmentID

And here is a script to delete ALL Document Attach records for ALL payables transactions.

 
 DELETE co1 FROM dbo.CO00101 co1  
 JOIN dbo.CO00105 co5 ON co5.Attachment_ID = co1.Attachment_ID  
 WHERE co5.BusObjKey LIKE '0\PM\Payables Transaction%'  
   
 DELETE coI FROM dbo.coAttachmentItems coI  
 JOIN dbo.CO00105 co5 ON co5.Attachment_ID = coI.Attachment_ID  
 WHERE co5.BusObjKey LIKE '0\PM\Payables Transaction%'  
   
 DELETE co2 FROM dbo.CO00102 co2 WHERE co2.BusObjKey LIKE '0\PM\Payables Transaction%'  
 DELETE co3 FROM dbo.CO00103 co3 WHERE co3.BusObjKey LIKE '0\PM\Payables Transaction%'  
 DELETE co4 FROM dbo.CO00104 co4 WHERE co4.BusObjKey LIKE '0\PM\Payables Transaction%'  
 DELETE co5 FROM dbo.CO00105 co5 WHERE co5.BusObjKey LIKE '0\PM\Payables Transaction%'  

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Microsoft Flow, and PowerApps.

You can also find him on Twitter and YouTube

http://www.precipioservices.com