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 */
Solved
Monitoring Deleted Data
Best answer by RohitRattan88
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.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.