Cloning Tree Node Table — Creating Record DEPT_NODE_TBL

Since your scope is based on tree nodes, the key to entering appropriate distribution information into the tree is to modify the tree node table to include additional distribution fields. You need to add the additional fields, DISTLIST and EMAIL_TO,to the node table to be the distribution fields for web and email distribution separately. You can run the following SQL scripts to move the nodes from record TREE_NODE_TBL to record DEPT_NODE_TBL, while adding the two new fields to record DEPT_NODE_TB.

To move all nodes where the tree has detail values based on a given field (such as DEPTID), use the following script:

Note:

You need to change the field name in quotes to the field you want to key from.

INSERT INTO PS_DEPT_NODE_TBL
SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS, A.DESCR, ' ', ' '
  FROM PS_TREE_NODE_TBL A, PSTREENODE B
  WHERE A.TREE_NODE = B.TREE_NODE
    AND B.TREE_NAME IN (SELECT C.TREE_NAME
  FROM PSTREEDEFN C, PSTREESTRCT D
  WHERE D.TREE_STRCT_ID = C.TREE_STRCT_ID
    AND ( C.EFFDT = B.EFFDT
    AND C.SETID = B.SETID
    AND D.DTL_FIELDNAME = 'DEPTID' )) 

To move all nodes for a given tree name, use the following script:

Note:

You need to change the tree name in quotes to the tree name you want to key from.

INSERT INTO PS_DEPT_NODE_TBL
SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS, A.DESCR, ' ', ' '
  FROM PS_TREE_NODE_TBL A, PSTREENODE B
  WHERE A.TREE_NODE = B.TREE_NODE
    AND B.TREE_NAME = 'DEPARTMENTS' 

To move all nodes where the tree is based on a specific tree structure ID, use the following script:

Note:

You need to change the structure name in quotes to the structure ID you want to key from.

INSERT INTO PS_DEPT_NODE_TBL
SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS, A.DESCR, ' ', ' '
  FROM PS_TREE_NODE_TBL A, PSTREENODE B
  WHERE A.TREE_NODE = B.TREE_NODE
    AND B.TREE_NAME IN (SELECT C.TREE_NAME
  FROM PSTREEDEFN C
  WHERE C.TREE_STRCT_ID = 'DEPTID'
    AND ( C.EFFDT = B.EFFDT
    AND C.SETID = B.SETID ))