|
SELECT DISTINCT A.PERSON_ID , E.SYNCID , E.SYNCDTTM , MAX(A.PERSON_ID_MANAGER) FROM PS_RSF_ACC_SUSER A , PSTREENODE B , PSTREENODE C , PS_RSF_TR_TEAM D , PS_RSF_TR_TEAM D1 , PS_RD_PERSON E WHERE A.PERSON_ID > ' ' AND A.PERSON_ID <> A.PERSON_ID_MANAGER AND A.PERSON_ID = E.PERSON_ID AND A.TREE_NAME = B.TREE_NAME AND B.TREE_NODE = A.TERRITORY_ID AND B.PARENT_NODE_NAME = A.TERRITORY_PARENT AND B.EFFDT = C.EFFDT AND B.PARENT_NODE_NAME = C.TREE_NODE AND B.TREE_LEVEL_NUM = C.TREE_LEVEL_NUM + 1 AND D.TREE_NAME = A.TREE_NAME AND D.PERSON_ID = A.PERSON_ID_MANAGER AND ( D.PRIMARY_USER = 'Y' OR NOT EXISTS ( SELECT 'X' FROM PS_RSF_TR_TEAM D1 WHERE D.TREE_NAME = D1.TREE_NAME AND D.TERRITORY_ID = D1.TERRITORY_ID AND D.EFFDT = D1.EFFDT AND D1.PRIMARY_USER = 'Y')) AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PSTREENODE E WHERE E.TREE_NAME = B.TREE_NAME AND EFFDT <= %CurrentDateIn) GROUP BY A.PERSON_ID, E.SYNCID, E.SYNCDTTM
|