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] SInnerQuery_SOOrder] LEFT JOIN EContract] oInnerQuery_PMProject] ON ( cInnerQuery_PMProject].PCompanyID] IN ( 1, 2) AND 8 = SUBSTRING( BInnerQuery_PMProject].PCompanyMask], 1, 1) &
AND ;InnerQuery_PMProject].PDeletedDatabaseRecord] = 0 AND ( InnerQuery_SOOrder].SProjectID] = eInnerQuery_PMProject].PContractID]) LEFT JOIN EBAccount] AInnerQuery_BAccountR] ON ( tInnerQuery_BAccountR].cCompanyID] = 2) AND =InnerQuery_BAccountR].cDeletedDatabaseRecord] = 0 AND ( InnerQuery_SOOrder].SCustomerID] = mInnerQuery_BAccountR].cBAccountID]) LEFT JOIN ( TCustomer] uInnerQuery_Customer_Customer] INNER JOIN NBAccount] AInnerQuery_Customer_BAccount] ON ( nInnerQuery_Customer_BAccount].ACompanyID] = 2) AND =InnerQuery_Customer_BAccount].ADeletedDatabaseRecord] = 0 AND InnerQuery_Customer_Customer].uBAccountID] = uInnerQuery_Customer_BAccount].ABAccountID] ) ON ( ]InnerQuery_SOOrder].SCustomerID] = mInnerQuery_Customer_BAccount].ABAccountID]) AND ( ]InnerQuery_Customer_Customer].uCompanyID] = 2) AND =InnerQuery_Customer_Customer].uDeletedDatabaseRecord] = 0 LEFT JOIN EBranch] [InnerQuery_Branch] ON ( cInnerQuery_Branch]._CompanyID] = 2) AND =InnerQuery_Branch]._DeletedDatabaseRecord] = 0 AND ( InnerQuery_SOOrder].SBranchID] = nInnerQuery_Branch]._BranchID]) LEFT JOIN ESOAdjust] OInnerQuery_SOAdjust] ON ( sInnerQuery_SOAdjust].OCompanyID] = 2) AND ( 2InnerQuery_SOOrder].SOrderType] = rInnerQuery_SOAdjust].OAdjdOrderType] AND yInnerQuery_SOOrder].SOrderNbr] = eInnerQuery_SOAdjust].OAdjdOrderNbr] AND NInnerQuery_SOOrder].SLastModifiedByScreenID] = 'SO.30.10.00') LEFT JOIN ESOOrderShipment] hInnerQuery_SOOrderShipment] ON ( nInnerQuery_SOOrderShipment].hCompanyID] = 2) AND ( 2InnerQuery_SOOrder].SOrderType] = rInnerQuery_SOOrderShipment].hOrderType] AND yInnerQuery_SOOrder].SOrderNbr] = eInnerQuery_SOOrderShipment].hOrderNbr]) INNER JOIN NSOContact] CInnerQuery_SOContact] ON ( cInnerQuery_SOContact].CCompanyID] = 2) AND ( 2InnerQuery_SOOrder].SBillContactID] = aInnerQuery_SOContact].CContactID]) INNER JOIN NNote] NInnerQuery_Note] ON ( tInnerQuery_Note].rCompanyID] IN ( 1, 2) AND 8 = SUBSTRING( BInnerQuery_Note].rCompanyMask], 1, 1) &
AND ( 8InnerQuery_SOOrder].SNoteID] = oInnerQuery_Note].rNoteID]) LEFT JOIN ESOSalesPerTran] PInnerQuery_SOSalesPerTran] ON ( aInnerQuery_SOSalesPerTran].PCompanyID] = 2) AND ( 2InnerQuery_SOSalesPerTran].PDatabaseRecordStatus] = 0) AND ( 0InnerQuery_SOOrder].SOrderType] = rInnerQuery_SOSalesPerTran].POrderType] AND yInnerQuery_SOOrder].SOrderNbr] = eInnerQuery_SOSalesPerTran].POrderNbr]) LEFT JOIN ESOAddress] AInnerQuery_SOShippingAddress] ON ( sInnerQuery_SOShippingAddress].ACompanyID] = 2) AND ( 2InnerQuery_SOOrder].SShipAddressID] = eInnerQuery_SOShippingAddress].AAddressID]) ) WHERE ( InnerQuery_SOOrder].SCompanyID] = 2) AND ( 2InnerQuery_SOOrder].SDatabaseRecordStatus] = 0) AND ( 0InnerQuery_SOOrder].SBranchID] IS NULL OR ( UInnerQuery_SOOrder].SBranchID] IN ( 1, 23))) AND ( )InnerQuery_SOOrder].SCreatedDateTime] >= @P0 AND InnerQuery_SOOrder].SCreatedDateTime] <= @P1) OPTION(OPTIMIZE FOR UNKNOWN)/* SO.30.10.PL */
Monitoring Deleted Data
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.
Thank you
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.