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.

Role, Permission List that contain a Page

select A.CLASSID , B.CLASSDEFNDESC, C.ROLENAME, D.DESCR
FROM PSAUTHITEM A, PSCLASSDEFN B, PSROLECLASS C, PSROLEDEFN D
WHERE A.PNLITEMNAME = 'COMPETENCY_TABLE'
AND A.CLASSID = B.CLASSID
AND A.CLASSID = C.CLASSID
AND C.ROLENAME = D.ROLENAME