Adempiere 360LTS Delete Client

    本文是系统上线前，实施过程中产生疑惑，面临的问题，进行的思考和讨论的日常工作记录，供日后参考。

1.备份数据库；

2.删除  AD_ORGINFO 下的 两个外键约束 cbank_adorginfo ，ccashbook_adorginfo。（运行删除程序时提示此两个约束没法禁用）；

3.

以下步骤不是必须的，也有可能需要增加，视具体情况而定（根据运行删除程序错误Log调整，谨慎 勿删核心数据）。

--delete FROM AD_changelog
--WHERE AD_SESSION_ID NOT IN
--(SELECT AD_SESSION_ID FROM AD_SESSION);COMMIT;

--DELETE FROM AD_PREFERENCE
--WHERE AD_USER_ID NOT IN
--(SELECT AD_USER_ID FROM AD_USER);COMMIT;

--DELETE FROM AD_WF_NODE_TRL
--WHERE AD_WF_NODE_ID NOT IN
--(SELECT AD_WF_NODE_ID FROM AD_WF_NODE );COMMIT;

--DELETE FROM T_REPORTSTATEMENT
--WHERE AD_PINSTANCE_ID NOT IN
--(SELECT AD_PINSTANCE_ID FROM AD_PINSTANCE);COMMIT;

--DELETE FROM AD_PINSTANCE_LOG
--WHERE AD_PINSTANCE_ID NOT IN
--(SELECT AD_PINSTANCE_ID FROM AD_PINSTANCE);COMMIT;

--DELETE FROM T_TRIALBALANCE
--WHERE AD_PINSTANCE_ID NOT IN
--(SELECT AD_PINSTANCE_ID FROM AD_PINSTANCE);COMMIT;

4. 运行RUN_DELETECLIENT.bat，执行Oracle目录下相应的 DELETECLIENT.sql

5. 报错转第3步

6.更新AD_SEQUENCE

以下代码根据实际情况调整（慎用）

============================================================

DECLARE

v_AD_SEQUENCE_ID NUMBER;

v_NAME VARCHAR2(60);

v_sql1  VARCHAR2(100);

v_sql2  VARCHAR2(100);

CURSOR cur_ad_sequence

IS

SELECT AD_SEQUENCE_ID,NAME

FROM AD_SEQUENCE

WHERE NAME NOT LIKE 'DocumentNo%'

AND AD_CLIENT_ID =0

AND NAME NOTIN('T_BOMLine_Costs','AD_Document_Action_Access')

AND CURRENTNEXT >1000000;

 

BEGIN

 

FOR p IN cur_ad_sequence

LOOP

 v_sql1 := 'SELECTMAX('||p.NAME||'_ID) FROM '||p.NAME;

 --DBMS_OUTPUT.put_line(v_sql1);

 EXECUTE IMMEDIATEv_sql1 INTO V_AD_SEQUENCE_ID;

 --DBMS_OUTPUT.put_line(v_AD_SEQUENCE_ID);

 IF V_AD_SEQUENCE_IDIS NULL THEN

  V_AD_SEQUENCE_ID:=0;

 END IF;

 IF V_AD_SEQUENCE_ID< 1000000 THEN

  V_AD_SEQUENCE_ID:=1000000;

 ELSE

  V_AD_SEQUENCE_ID:=V_AD_SEQUENCE_ID+1;

 END IF;

 v_sql2 :='UPDATEAD_SEQUENCE SET CURRENTNEXT='||V_AD_SEQUENCE_ID||' WHEREAD_SEQUENCE_ID='||p.AD_SEQUENCE_ID;

 EXECUTE IMMEDIATEv_sql2;

 --DBMS_OUTPUT.put_line(v_sql2);

END LOOP;

COMMIT;

END;

 

/

EXIT

可能存在的问题：

DELETECLIENT.sql 执行时可能出现字符集不匹配的情况，

修改部分SQL如下

========================================================================================

  SELECT    N'delete from '
             || tablename
             || ' where AD_Client_ID='
             || v_client_id AS v_sql
         , a.TableName
        FROM AD_TABLE a
       WHERE a.isview = 'N'
         AND EXISTS (
                SELECT ad_column_id
                  FROM AD_COLUMN c
                 WHERE a.ad_table_id = c.ad_table_id
                   AND UPPER (c.columnname) = 'AD_CLIENT_ID')
     -- Assure that the table is really a table in database
     AND EXISTS (SELECT 1 FROM user_objects dbo WHERE UPPER(dbo.object_name)=UPPER(a.TableName) AND dbo.object_type='TABLE')
      UNION
      SELECT    N'delete from '
             || t.tablename
             || ' where '
             || columnname
             || '='
             || v_client_id AS v_sql
         , t.TableName
        FROM AD_COLUMN c, AD_TABLE t

===========================================================================================