Copy Missing Departments after Tree Copy

Since Copying a Tree via App Designer doesnt copy the department records in DEPT_TBL, the tree and department table may become out of sync. Here is a little SQl to copy Department records in DEPT_TBL once you have copied the Tree. The SQL is for SQL Server platform and relies on database links.


INSERT INTO PS_DEPT_TBL
SELECT B.* FROM PSTREENODE A, HCMPRD88.dbo.PS_DEPT_TBL B
WHERE A.TREE_NAME = 'DEPT_SECURITY'
AND A.EFFDT = '2006-01-01'
AND NOT EXISTS (SELECT 'X' FROM PS_DEPT_TBL
WHERE A.TREE_NODE = DEPTID)
AND A.TREE_NODE = B.DEPTID


In version 8.8 and abover, PeopleSoft provides a web based page to export and import Tree. This can be accessed via -Company Hierarchy, Administer Tree, Copy/Delete Tree.