Cleanup Client Test data

[Pritesh Shah]

In order to prepare for production setup, we need to clean up all the test/transaction/sample data for each client in idempiere. Is there any utility available in idempiere clean all test data from db and have fresh installation with configurations available for production?

[Nicolas Micoud]

Hi,

I haven't tested but you may have a look at the DeleteAdempiereClient.SQL script there : https://bitbucket.org/CarlosRuiz_globalqss/idempiere-stuff/src

Regards,

Nicolas

[Steven Sackett]

Paul Bowden created a process called 'delete entitites' in adempiere that you might add. It lets you selectively delete data. You can use it to (say) delete all test transactions but keep all BPs and Products and other setup.
Regards

[Pritesh Shah]

Thank You Steven, can you please help me to point to the location where can I find the "delete entities" code in adempiere?

[Steven Sackett]

Hi I am sorry I did not see this post before... 

I am told this is a link to swing version

http://adempiere.hg.sourceforge.net/hgweb/adempiere/contribution_adaxa/file/b46d0da26a59/client/src/org/compiere/apps/form 

there are also patches for a zk version. if you email me at info at adaxa . com I will send to you. 

Please feel free to add any part that is useful to Idempiere.

regards

steven 

[Steven Sackett]

found and added the zk patches...

 

[Chuck Boecking]

Hi Pritesh,

Here is a simple SQL script we developed in the erp-academy.chuckboecking.com. Please note that it is not ad_client_id specific. It will delete test transactions across all clients. You will need to add a where clause to each of the statements if you wish to limit to a specific client.

Please let me know if you find any dependencies that we missed.

I hope this helps!!

Regards,

Chuck Boecking

erp-academy.chuckboecking.com

[Steven Sackett]

... and the way it functions is that you select a client, select a table, choose 'dry run' or for real .. it then searches through AD for linked tables and removes all relevant entries ... attached screen shot shows how it function.

regards 

 

 

[Shereef N Mpm]

Hi Chuck,

Many thanks for sharing the script. I haved used the same script but throw some errors due to dependencies.So i added few more tables. Below is the final script i have used to delete my test transactions. I have added my client ID in the where clause. You can replace that with your desired client ID.

delete from ad_changelog where ad_client_id=1000000;

delete from c_allocationline where ad_client_id=1000000;

delete from c_allocationhdr where ad_client_id=1000000;

Update C_BankAccount Set CurrentBalance = 0 where ad_client_id=1000000;

delete from m_costhistory where ad_client_id=1000000;

delete from m_costdetail where ad_client_id=1000000;

delete from a_asset_addition where ad_client_id=1000000;

delete from m_matchinv where ad_client_id=1000000;

delete from m_matchpo where ad_client_id=1000000;

delete from c_payselectionline where ad_client_id=1000000;

delete from c_payselectioncheck where ad_client_id=1000000;

delete from c_payselection where ad_client_id=1000000;

Update C_Invoice set C_Cashline_ID = null where ad_client_id=1000000;

Update C_Order set C_Cashline_ID = null where ad_client_id=1000000;

delete from C_Cashline where ad_client_id=1000000;

delete from C_Cash where ad_client_id=1000000;

Update c_payment set C_Invoice_ID= null where ad_client_id=1000000;

delete from C_CommissionAmt where ad_client_id=1000000;

delete from C_CommissionDetail where ad_client_id=1000000;

delete from C_CommissionLine where ad_client_id=1000000;

delete from C_CommissionRun where ad_client_id=1000000;

delete from C_Commission where ad_client_id=1000000;

Delete from c_recurring_run where ad_client_id=1000000;

Delete from c_recurring where ad_client_id=1000000;

Delete from s_timeexpenseline where ad_client_id=1000000;

Delete from s_timeexpense where ad_client_id=1000000;

Delete from c_landedcostallocation where ad_client_id=1000000;

Delete from c_landedcost where ad_client_id=1000000;

Delete from a_asset_disposed where ad_client_id=1000000;

delete from c_invoiceline where ad_client_id=1000000;

delete from c_invoicetax where ad_client_id=1000000;

delete from c_paymentallocate where ad_client_id=1000000;

delete from c_bankstatementline where ad_client_id=1000000;

delete from c_bankstatement where ad_client_id=1000000;

Update c_invoice set c_Payment_ID = null where ad_client_id=1000000;

Update c_order set c_Payment_ID= null where ad_client_id=1000000;

delete from c_depositbatchline where ad_client_id=1000000;

delete from c_depositbatch where ad_client_id=1000000;

delete from c_orderpayschedule where ad_client_id=1000000;

delete from c_paymenttransaction where ad_client_id=1000000;

delete from c_payment where ad_client_id=1000000;

delete from c_paymentbatch where ad_client_id=1000000;

Update M_INOUTLINE Set C_Orderline_ID = null, M_RMALine_ID=null where ad_client_id=1000000;

Update M_INOUT Set C_Order_ID = null, C_Invoice_ID=null, M_RMA_ID=null where ad_client_id=1000000;

Update C_INVOICE Set M_RMA_ID = null where ad_client_id=1000000;

update R_Request set m_rma_id = null where ad_client_id=1000000;

delete from m_rmatax where ad_client_id=1000000;

delete from M_RMAline where ad_client_id=1000000;

delete from M_RMA where ad_client_id=1000000;

delete from c_Invoice where ad_client_id=1000000;

delete from PP_MRP where ad_client_id=1000000;

delete from m_requisitionline where ad_client_id=1000000 ;

delete from m_requisition where ad_client_id=1000000;

update pp_order set c_orderline_id = null where ad_client_id=1000000;

delete from c_orderline where ad_client_id=1000000;

delete from c_ordertax where ad_client_id=1000000;

update r_request set c_order_id = null, M_inout_id = null where ad_client_id=1000000;

update r_requestaction set c_order_id = null, M_inout_id = null where ad_client_id=1000000;

delete from c_orderlandedcostallocation where ad_client_id=1000000;

delete from c_orderlandedcost where ad_client_id=1000000;

delete from c_order where ad_client_id=1000000;

delete from fact_reconciliation where ad_client_id=1000000;

delete from fact_acct where ad_client_id=1000000;

delete from fact_acct_summary where ad_client_id=1000000;

delete from gl_journalbatch where ad_client_id=1000000 ;

delete from gl_journal where ad_client_id=1000000; 

delete from gl_journalline where ad_client_id=1000000; 

--delete from m_storage ;  -- use this for ADempiere

delete from m_storageonhand where ad_client_id=1000000;

delete from m_storagereservation where ad_client_id=1000000;

delete from m_transaction where ad_client_id=1000000;

delete from m_packageline where ad_client_id=1000000;

delete from m_package where ad_client_id=1000000;

update c_projectissue set m_inoutline_id = null where ad_client_id=1000000;

delete from m_inoutline where ad_client_id=1000000; 

delete from m_inout where ad_client_id=1000000;

delete from m_inoutconfirm where ad_client_id=1000000; 

delete from m_inoutlineconfirm where ad_client_id=1000000; 

delete from m_inoutlinema where ad_client_id=1000000; 

delete from m_inventoryline where ad_client_id=1000000; 

delete from m_inventory where ad_client_id=1000000;

delete from m_inventorylinema  where ad_client_id=1000000; 

delete from m_Movementline where ad_client_id=1000000; 

delete from m_Movement where ad_client_id=1000000; 

delete from m_Movementconfirm where ad_client_id=1000000; 

delete from m_Movementlineconfirm where ad_client_id=1000000; 

delete from m_Movementlinema where ad_client_id=1000000; 

delete from m_production where ad_client_id=1000000;

delete from m_productionplan where ad_client_id=1000000; 

delete from m_productionline where ad_client_id=1000000; 

delete from c_dunningrun where ad_client_id=1000000; 

delete from c_dunningrunline where ad_client_id=1000000; 

delete from c_dunningrunentry where ad_client_id=1000000; 

delete from AD_WF_EventAudit  where ad_client_id=1000000;

delete from AD_WF_Process  where ad_client_id=1000000;

Update M_Cost SET CurrentQty=0, CumulatedAMT=0, CumulatedQty=0   where ad_client_id=1000000;

Update C_BPartner SET ActualLifetimeValue=0, SO_CreditUsed=0, TotalOpenBalance=0  where ad_client_id=1000000 ;

delete from R_RequestUpdates  where ad_client_id=1000000;

delete from R_RequestUpdate  where ad_client_id=1000000;

delete from R_RequestAction  where ad_client_id=1000000;

delete from R_Request  where ad_client_id=1000000;

Delete from pp_cost_collectorma   where ad_client_id=1000000;

Delete from pp_order_nodenext   where ad_client_id=1000000;

Delete from pp_order_node_trl   where ad_client_id=1000000;

Delete from pp_order_workflow_trl  where ad_client_id=1000000 ;

Delete from pp_order_bomline_trl  where ad_client_id=1000000 ;

Delete from pp_order_bom_trl  where ad_client_id=1000000 ;

update pp_cost_collector set pp_order_bomline_id = null  where ad_client_id=1000000;

Delete from pp_order_bomline  where ad_client_id=1000000 ;

Delete from pp_order_bom  where ad_client_id=1000000 ;

Delete from PP_Cost_Collector  where ad_client_id=1000000 ;

Update pp_order_workflow set PP_Order_Node_id = null  where ad_client_id=1000000; 

Delete from PP_Order_Node  where ad_client_id=1000000;

Delete from PP_Order_Workflow  where ad_client_id=1000000 ;

Delete from pp_order_cost  where ad_client_id=1000000 ;

Delete from PP_Order  where ad_client_id=1000000  ;

delete from dd_orderline  where ad_client_id=1000000;

delete from dd_order  where ad_client_id=1000000;

delete from t_replenish  where ad_client_id=1000000;

delete from i_order  where ad_client_id=1000000;

delete from i_invoice  where ad_client_id=1000000;

delete from i_payment  where ad_client_id=1000000;

delete from I_Inventory  where ad_client_id=1000000;

delete from I_GLJournal  where ad_client_id=1000000;

delete from m_distributionrunline  where ad_client_id=1000000;

delete from c_rfqline  where ad_client_id=1000000;

delete from s_timeexpense  where ad_client_id=1000000;

delete from s_timeexpenseline  where ad_client_id=1000000;

delete from c_projectline where c_project_id not in (select c_project_id from c_acctschema_element where c_project_id is not null)  and ad_client_id=1000000;

delete from c_projecttask where c_projectphase_id not in (select pp.c_projectphase_id from c_acctschema_element ae join c_projectphase pp on ae.c_project_id = pp.c_project_id)  and ad_client_id=1000000;

delete from c_projectphase where c_project_id not in (select c_project_id from c_acctschema_element where c_project_id is not null)  and ad_client_id=1000000;

delete from c_project where c_project_id not in (select c_project_id from c_acctschema_element where c_project_id is not null)  and ad_client_id=1000000;

Thanks,

Shereef N

Nest Information Technologies,India