Retrieve the deleted purchases from the database

[FarrukhNaz]

Hi Folks, Is there any possibility retrieving the deleted purchase records from the database along with the user who deleted them?

[mag00_75]

Hi
Not the full answer to your question but maybe can give some ideas.
We had a similar topic where assets was deleted and we didn't want to restore the full DB. Then we basically restored an older version of the database into a new instance and from that we created a script to load the missing objects. 

[maxhensel]

Hi,

one (complex) idea: maybe you can have a look in the ComplianceHistory table in the FNMSCompliance DB. You could create a sql query to retrieve the delete events of the POs. Then you can have a look for the create events of the POs and build a query to re-create the entries in the database.

[mfranz]

Hi,

Looking for deleted PO lines:

SELECT TOP 100
	OldValue ItemDescription
	,UserName
	,HistoryDate
	,Comments
FROM ComplianceHistory
WHERE PurchaseOrderDetailID IS NOT NULL
	AND ComplianceHistoryTypeID = 22 -- Deleted - final state
	AND FieldName = 'ItemDescription'
ORDER BY ComplianceHistoryID DESC

There are some tricky parts in this:

• the history data does does not seem to contain any reference to the actual PO, so you'll have to identify the lines by their description and/or other fields
• the history does not seem to log the "item number" (also sequence number), identifying the actual position within a PO
• if the last PO line is removed, the system also removes the PO itself; you might want to look for them as well

SELECT TOP 100
	OldValue PurchaseOrderNo
	,UserName
	,HistoryDate
	,Comments
FROM ComplianceHistory
WHERE PurchaseOrderID IS NOT NULL
	AND ComplianceHistoryTypeID = 22 -- Deleted - final state
	AND FieldName = 'PurchaseOrderNo'
ORDER BY ComplianceHistoryID DESC

Best regards,

Markward