Skip to main content
Solved

Monitoring Deleted Data


We are using data from Acumatica in our BI (Superset) and Data Warehouse, but we are running into an issue. We pull the data via OData for each DAC. But, our number are off every time someone deletes a Sales Order, Return, Shipment, Refund, Payment, Invoice, etc. I looked at https://help.acumatica.com/(W(9))/Help?ScreenId=ShowWiki&pageid=4a1939a3-26aa-49e3-83c6-5bcc884e66b9, and tried adding the column to the SOOrder table, then deleting an order, and it didn’t work (I tried with and without the Usr in front) - It deleted the order, and it did not preserve the data as it says it would in the doc. I had planned to create a customization with a DB view to then use in a GI to then send push notifications to our Data Warehouse, but I can’t get past the part of being able to see what data is being deleted. I also looked at the Trace after deleting an order, which wasn’t helpful because it doesn’t show the deleted data either (that is below). Any pointers or suggestions here?

SELECT COUNT( *) FROM ( [SOOrder] [InnerQuery_SOOrder] LEFT JOIN [Contract] [InnerQuery_PMProject] ON ( [InnerQuery_PMProject].[CompanyID] IN ( 1, 2) AND 8 = SUBSTRING( [InnerQuery_PMProject].[CompanyMask], 1, 1) & 8) AND [InnerQuery_PMProject].[DeletedDatabaseRecord] = 0 AND ( [InnerQuery_SOOrder].[ProjectID] = [InnerQuery_PMProject].[ContractID]) LEFT JOIN [BAccount] [InnerQuery_BAccountR] ON ( [InnerQuery_BAccountR].[CompanyID] = 2) AND [InnerQuery_BAccountR].[DeletedDatabaseRecord] = 0 AND ( [InnerQuery_SOOrder].[CustomerID] = [InnerQuery_BAccountR].[BAccountID]) LEFT JOIN ( [Customer] [InnerQuery_Customer_Customer] INNER JOIN [BAccount] [InnerQuery_Customer_BAccount] ON ( [InnerQuery_Customer_BAccount].[CompanyID] = 2) AND [InnerQuery_Customer_BAccount].[DeletedDatabaseRecord] = 0 AND [InnerQuery_Customer_Customer].[BAccountID] = [InnerQuery_Customer_BAccount].[BAccountID] ) ON ( [InnerQuery_SOOrder].[CustomerID] = [InnerQuery_Customer_BAccount].[BAccountID]) AND ( [InnerQuery_Customer_Customer].[CompanyID] = 2) AND [InnerQuery_Customer_Customer].[DeletedDatabaseRecord] = 0 LEFT JOIN [Branch] [InnerQuery_Branch] ON ( [InnerQuery_Branch].[CompanyID] = 2) AND [InnerQuery_Branch].[DeletedDatabaseRecord] = 0 AND ( [InnerQuery_SOOrder].[BranchID] = [InnerQuery_Branch].[BranchID]) LEFT JOIN [SOAdjust] [InnerQuery_SOAdjust] ON ( [InnerQuery_SOAdjust].[CompanyID] = 2) AND ( [InnerQuery_SOOrder].[OrderType] = [InnerQuery_SOAdjust].[AdjdOrderType] AND [InnerQuery_SOOrder].[OrderNbr] = [InnerQuery_SOAdjust].[AdjdOrderNbr] AND [InnerQuery_SOOrder].[LastModifiedByScreenID] = 'SO.30.10.00') LEFT JOIN [SOOrderShipment] [InnerQuery_SOOrderShipment] ON ( [InnerQuery_SOOrderShipment].[CompanyID] = 2) AND ( [InnerQuery_SOOrder].[OrderType] = [InnerQuery_SOOrderShipment].[OrderType] AND [InnerQuery_SOOrder].[OrderNbr] = [InnerQuery_SOOrderShipment].[OrderNbr]) INNER JOIN [SOContact] [InnerQuery_SOContact] ON ( [InnerQuery_SOContact].[CompanyID] = 2) AND ( [InnerQuery_SOOrder].[BillContactID] = [InnerQuery_SOContact].[ContactID]) INNER JOIN [Note] [InnerQuery_Note] ON ( [InnerQuery_Note].[CompanyID] IN ( 1, 2) AND 8 = SUBSTRING( [InnerQuery_Note].[CompanyMask], 1, 1) & 8) AND ( [InnerQuery_SOOrder].[NoteID] = [InnerQuery_Note].[NoteID]) LEFT JOIN [SOSalesPerTran] [InnerQuery_SOSalesPerTran] ON ( [InnerQuery_SOSalesPerTran].[CompanyID] = 2) AND ( [InnerQuery_SOSalesPerTran].[DatabaseRecordStatus] = 0) AND ( [InnerQuery_SOOrder].[OrderType] = [InnerQuery_SOSalesPerTran].[OrderType] AND [InnerQuery_SOOrder].[OrderNbr] = [InnerQuery_SOSalesPerTran].[OrderNbr]) LEFT JOIN [SOAddress] [InnerQuery_SOShippingAddress] ON ( [InnerQuery_SOShippingAddress].[CompanyID] = 2) AND ( [InnerQuery_SOOrder].[ShipAddressID] = [InnerQuery_SOShippingAddress].[AddressID]) ) WHERE ( [InnerQuery_SOOrder].[CompanyID] = 2) AND ( [InnerQuery_SOOrder].[DatabaseRecordStatus] = 0) AND ( [InnerQuery_SOOrder].[BranchID] IS NULL OR ( [InnerQuery_SOOrder].[BranchID] IN ( 1, 23))) AND ( [InnerQuery_SOOrder].[CreatedDateTime] >= @P0 AND [InnerQuery_SOOrder].[CreatedDateTime] <= @P1) OPTION(OPTIMIZE FOR UNKNOWN)/* SO.30.10.PL */

Best answer by RohitRattan88

@pdeatherage 

Try following:

alter table TABLENAME add UsrDeletedDatabaseRecord int not null default 0;

Perform IIS reset after creating the Column.

Without the IIS Reset, the column exists, but may not function as intended.

View original
Did this topic help you find an answer to your question?

2 replies

RohitRattan88
Acumatica Moderator
Forum|alt.badge.img+4
  • Acumatica Moderator
  • 245 replies
  • Answer
  • July 29, 2024

@pdeatherage 

Try following:

alter table TABLENAME add UsrDeletedDatabaseRecord int not null default 0;

Perform IIS reset after creating the Column.

Without the IIS Reset, the column exists, but may not function as intended.


  • Author
  • Freshman I
  • 4 replies
  • August 2, 2024

Thank you @RohitRattan88 - That did the trick.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings