Plugin: Delete Client and Initialize Client

> JPiere - Top > JPPS > Plugin: Delete Client and Initialize Client

• Maintainer: Hagiwara Hideaki in JAPAN (I'm sorry for my poor English. Please modify correctly if my English expressions are mistaken.)
• SPONSOR: OSS ERP Solutions in JAPAN
• License: GPLv2 - Free as in Freedom not Free Lunch
• Source: GitHub JPiere repository
• Japanese:【JPIERE-0158】クライアントの初期化と削除
• Distro: JPiere (Japan + iDempiere)

Note: This plugin was developed in reference to "drop_client.sql". "drop_client.sql" have Credits: Credits due to Fernando Lucktemberg(fer_luck) for the main workings Credit to the guys @ e-Nition.com for improving it :) Credit to the Tony Snook for change in discovering the relnamespace Credit to Jesus Garcia and Carlos Ruiz from globalqss for adding changes to reference columns with names <> AD_Client_ID

Delete Client and Initialize Client Overview

• You will delete records in iDempiere/JPiere when you perform Delete Client and Initialize Client Process. JPiere provides 5 ways that delete records as follows.
  • Delete a Client ---> Delete a Client can delete all data in a client that you select.
  • Delete a Client Transactions ---> Delete a Client Transactions can delete all transaction data in a client that you select.
  • Delete All Clients Transactions ---> Delete All Clients Transactions can delete all transaction data in all client.
  • Initialize a Client ---> Initialize a Client can delete transaction data and master data. All transaction data is deleted basically. And master data is deleted by Definition of Delete Profile .
  • Custom Delete ---> Custom Delete can delete data according to Definition of Delete Profile . You can delete a Client data flexibly.

About "Delete Profile"

• Delete Client and Initialize Client Process deletes data according by Definition of Delete Profile . Delete Client and Initialize Client Process can delete data flexibly by using Delete Profile .
• Definition of Delete Profile is configuration setting of Delete Profile .

• Relations of Delete a Client and Delete Profile ---> Delete a Client don't use Delete Profile . Delete a Client need not to customize because Delete a Client deletes all data in a client.

• Relations of Delete (a/All) Client(s) Transactions and Delete Profile ---> You can add delete tables to use Delete Profile at Delete a Client Transactions and Delete All Clients Transactions .

• Relations of Initialize a client and Delete Profile ---> Initialize a Client can delete transaction data and master data. All transaction data is deleted basically. And master data is deleted by Definition of Delete Profile .

• Relations of Custom Delete and Delete Profile ---> Custom Delete delete data according to Definition of Delete Profile . You can delete a Client data flexibly.

About "Transaction Tables" and "Initialize Tables"

• You need understand Transaction Tables and Initialize Table for performing Delete Client and Initialize Client Process correctly.

About Transaction Tables

• Transaction Tables are tables that have document data like a order. Transaction Tables are defined at Delete Client and Initialize Client Process. You can confirm Transaction Tables at Delete Tables Confirmation Report.
• Transaction data in Transaction Tables is deleted basically except Custom Delete.

About Initialize Tables

• Initialize Tables are tables that have master data like a Business Partner and a Product. Initialize Tables are defined at Delete Client and Initialize Client Process. You can confirm Initialize Tables at Delete Tables Confirmation Report.
• Initialize Tables is used by Initialize a Client. In Initialize a Client, Delete Client and Initialize Client Process do not delete data that is contained Initialize Tables if the Initialize Table is not defined in Delete Profile. On the other hand, Delete Client and Initialize Client Process deletes all data that is not contained Initialize Table and Definition of Delete Profile.

Note: Delete Client and Initialize Client Process defines Transaction Tables and Initialize Tables. You can confirm it at Delete Tables Confirmation Report. You can customize Delete Client and Initialize Client Process flexibly by using Delete Profile. Definition of Delete Profile is configuration setting of Delete Profile. Transaction data in Transaction Tables is deleted basically except Custom Delete. In Initialize a Client, Delete Client and Initialize Client Process do not delete data that is contained Initialize Tables if the Initialize Table is not defined in Delete Profile. In Initialize a Client, Delete Client and Initialize Client Process deletes all data that is not contained in both Initialize Table and Definition of Delete Profile.

Other Overview and Instructions

• You can not delete data that is in System Client.
• Please back up data before perform Delete Client and Initialize Client Process.
• Data base is PostgreSQL only.
• Delete Client and Initialize Client Process delete session log and change log except System Client.
• Delete Client and Initialize Client Process delete data that Import Tables(Prefix "I_") and Temporary Tables(Prefix "T_").
• You can perform Delete Client and Initialize Client Process System Client only.
• Super User can not perform Delete Client and Initialize Client Process . Please use System User.
• Please reboot iDempiere server after delete.
• Common Error
  • Error of Foreign key Constraint ---> Delete Client and Initialize Client Process invalid Foreign key constraint before delete. And valid Foreign key Constraint after delete. When valid Foreign key, You may get Error of Foreign key constraint. Please check log and Delete Profile, data in data base.
  • Error of Not Null Constraint ---> If you configure to set Null at Delete Profile, You may get Error of Not Null Constraint.　Please check Delete Profile if you get Error of Not Null Constraint.
• Menu of Delete Client and Initialize Client Plugin.
  • Delete Client and Initialize Client Process
  • Delete Profile
  • Delete Tables Confirmation Report
  • Table Relations Confirmation Report
  • Delere Client and Initialize Client Log

Delete Client and Initialize Client Process

• If you perform Delete Client and Initialize Client Process, You can delete client and Initialize client.

Parameters

• Client ID ---> You have to enter AD_Client_ID that you want to delete.
• Delete or Initialize Client ---> You have to choice a way that delete records as follows.
  • Delete a Client ---> Delete a Client deletes all data in a client that you select.
  • Delete a Client Transactions ---> Delete a Client Transactions deletes all transaction data in a client that you select.
  • Delete All Clients Transactions ---> Delete All Clients Transactions deletes all transaction data in all client.
  • Initialize a Client ---> Initialize a Client can delete transaction data and master data. All transaction data is deleted basically. And master data is deleted by Definition of Delete Profile.
  • Custom Delete ---> Custom Delete delete data according to Definition of Delete Profile. You can delete a Client data flexibly.
• Truncate ---> If you selected Delete All Clients Transactions, Flag of Truncate is displayed. If you tick the flag, Delete Client and Initialize Client Process deletes data by using Truncate.
• Delete Profile ---> You can select Delete profile except Delete a Client.
• Get Log of Update SQL 0 ---> If you tick this flag, Delete Client and Initialize Client Process get the all log of SQL that update and delete records.
• Others:
  • You can not perform Super User. Please use System User
  • If you want to perform Delete All Clients Transactions Process, Please Enter 0 to Client ID. Else You can not enter 0 to Client ID.
  • You have to enter a Delete Profile, When you selected Initialize a Client or Custom Delete.

Flow of Delete Client and Initialize Client process

Process flow of "Delete a Client"

• 1 : Delete Data of Transaction Tables.
• 2 : Delete Data of Not Transaction Tables.
• 3 : Delete records that refer to delete Client.
• 4 : Reset count of Primary key ID.
  • Except Prefix of Table Name is "AD_" and current ID less than 1000000 .

Process flow of "Custom Delete"

• Custom Delete delete data according to Definition of Delete Profile. It is handled in Sequence number.

Process flow of "Delete a Client Transactions"

• 1 : Delete Data of Transaction Tables.
• 2 : Set null records that refer Transaction Tables.
• 3 : Reset count of Document number.
• 4 : Process of Delete profile.
• 5 : Reset count of Primary key ID to Transaction Tables.
  • Except Prefix of Table Name is "AD_" and current ID less than 1000000 .

Process flow of "Delete All Clients Transactions"

• Process flow of Delete All Clients Transactions is same as Process flow of Delete a Client Transactions basically.
• But be careful to tick TRUNCATE flag. TRUNCATE perform with option of CASCADE and ignore WHERE clause of Delete Profile. All records that table is defined in Delete Profile is deleted.

Process flow of "Initialize a Client"

• 1 : Delete Data of Transaction Tables.
• 2 : Set null records that refer Transaction Tables.
• 3 : Reset count of Document number.
• 4 : Delete all data that table is not contained in both Initialize Table and Definition of Delete Profile.
• 5 : Process of Delete profile.
  • Delete Organization ---> Delete Organization that is not contained in AD_Org_ID Where Clause of Delete Profile. All Records that have delete Organization delete(Like a CASCADE).
  • Delete Role ---> Delete Role that is not contained in AD_Role_ID Where Clause of Delete Profile.
  • Delete User ---> Delete User that is not contained in AD_User_ID Where Clause of Delete Profile.
  • Delete Business Partner ---> Delete Business Partner that is not contained in C_BPartner_ID Where Clause of Delete Profile.
  • Delete Product ---> Delete Product that is not contained in M_Product_ID Where Clause of Delete Profile.
  • Delete Tables that is defined by Delete Profile .
• 6 : Reset count of Primary key ID.
  • Except Prefix of Table Name is "AD_" and current ID less than 1000000 .

Delete Profile

• You can customize Delete Client and Initialize Client Process flexibly by using Delete Profile. Definition of Delete Profile is configuration setting of Delete Profile.

Tab of Delete Profile

• Delete or Initialize Client --->You can select next 4 ways that delete or initialize client.
  • Delete a Client Transactions
  • Delete All Clients Transactions
  • Initialize a Client
  • Custom Delete
• Modify Client name ---> This field is displayed when you select Initialize a Client. You can input new Client Name in stead of Initialized old Client Name.
• AD_Org_ID Where ---> This field is displayed when you select Initialize a Client. You can input default organization condition. If you keep blank this field, Organizations are not deleted.
• AD_Role_ID Where ---> This field is displayed when you select Initialize a Client. You can input default Role condition. If you keep blank this field, Roles are not deleted.
• AD_User_ID Where ---> This field is displayed when you select Initialize a Client. You can input default User condition. If you keep blank this field, Users are not deleted. If you delete user, Delete Client and Initialize Client Process updates "CreatedBy" and "UpdatedBy" at "SuperUser".
• C_BPartner_ID Where ---> This field is displayed when you select Initialize a Client. You can input default Business partner condition. If you keep blank this field, Business Partners are not deleted.
• M_Product_ID Where ---> This field is displayed when you select Initialize a Client". You can input default Product condition. If you keep blank this field, Products are not deleted.

Tab of Custom Delete Profile

• Tab of Custom Delete Profile can define Delete Profile flexibly. Important definition that need to explain is Treat of Foreign key. You can define a way to process data that refer this table records.

• Sequence ---> Process order.
• Table ---> Delete Table.
• Delete data not use ---> If you tick on this check box, Delete Client and Initialize Process deletes records that is not referenced by other records.

Note: Judgment of the records that are using now or not This flag can not judge correclty if Foreign Key use below reference. ID / Location / Locator / Account / Assignment / PAttribute / Image / Chart / Color

• Sql WHERE --->You can define a remain record condition. If you keep blank this field, Data in this table is not deleted(All records remain).
• Treat of Foreign Key ---> Policy of Treat of Foreign Key.
  • Set NULL ---> If Foreign Key has refer to delete records, set Null except define of Custom Delete Profile Line.
  • Set Value ---> If Foreign Key has refer to delete records, set Value except define of Custom Delete Profile Line.
  • Delete Records---> If Foreign Key has refer to delete records, Delete the records except define of Custom Delete Profile Line.
  • Mandatory = true ? Delete : NULL ---> If Column of Foreign Key is Mandatory, Delete the record. If Column of Foreign Key is not Mandatory, set null to the column.
  • Mandatory = true ? Delete : Value ---> If Column of Foreign Key is Mandatory, Delete the record. If Column of Foreign Key is not Mandatory, set Value to the column.
  • Mandatory = true ? Value : NULL ---> If Column of Foreign Key is Mandatory, set Value to the column. If Column of Foreign Key is not Mandatory, set null to the column.
  • Mandatory = true ? Value : Delete ---> If Column of Foreign Key is Mandatory, set Value to the column. If Column of Foreign Key is not Mandatory, Delete the record.
• Foreign key ID ---> If you select Set Value at field of Treat of Foreign Key. Delete Client and Initialize Process put this value into Foreign Key.

Note: if you use process of Create Custom Delete Profile Line, you can create custom delete profile lines that refer this table. Note: precautions for use This process can not create line if there are lines already. This process can not create line if Foreign Key refer to table by using as follow reference and name of column is not "ReferredTableName_ID". Location / Locator / Account / Assignment / PAttribute / Image / Chart / Color

Tab of Custom Delete Profile Line

If you want to configuration that is irregular policy of Treat of foreign key, Please create Custom Delete Profile Line.

• Sequence ---> Process order.
• Table ---> Table that have Foreign Key refer to parent table record.
• Link Column ---> Column that refer to parent table record.
• Treat of Foreign Key ---> Policy of Treat of Foreign Key.
  • Set NULL ---> If Foreign Key has refer to delete records, Set null to this Foreign Key.
  • Set Value ---> If Foreign Key has refer to delete records, Set Value to this Foreign Key.
  • Delete Records ---> If Foreign Key has refer to delete records, Delete the records.
• Foreign key ID ---> If you select Set Value at field of Treat of Foreign Key. Delete Client and Initialize Process put this value into Foreign Key.

Delete Client and Initialize Client Confirmation Reports

Delete Tables Confirmation Report

• This report can list of tables that is defined by Delete Profile. You can confirm Delete way per Table.

• Please refer "Description" column. That column display information of delete way.

Table Relations Confirmation Report

• This report can list information of foreign key column.

Note: Case of tables that is not able to contain this list Columns that are used below reference to referred table is not in list: ID / Location / Locator / Account / Assignment / PAttribute / Image / Chart / Color

Set Up

Pack in 2Pack.zip under META-INF folder

• 2Pack will create the 2 windows and 2 Reports, 1 Process.
  • Delere Profile(Window)
  • Delere Client and Initialize Client Log(Window)
  • Delere Tables Confirmation Report(Report)
  • Table Relations Confirmation Report(Report)
  • Delere Client and Lnitialize Client Process(Process)

• You can create Table from DDL that is in SQL-DDL folder. If you can't pack in, please create table from DDL as follows.
  • JPIERE-0158-JP_DeleteProfile.txt
  • JPIERE-0158-JP_CustomDeleteProfile.txt
  • JPIERE-0158-JP_CustomDeleteProfileLine.txt
  • JPIERE-0158-T_DeleteClientLogJP.txt

Please apply process of "Create Custom Delete Profile Line" to Tab of Custom Delete Profile

• After pack in 2Pack.zip, please apply process of Create Custom Delete Profile Line to Tab of Custom Delete Profile