|
SELECT B.BUSINESS_UNIT , B.PROJECT_ID , %Substring(A.TREE_NODE, 1, 15) FROM PSTREENODE A , PS_PROJECT B , PS_BUS_UNIT_OPT_PC C WHERE %Substring(A.SETCNTRLVALUE, 1, 5) = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.EFF_STATUS <> 'T' AND B.EFF_STATUS IN ('A','P') AND A.TREE_NAME = C.PGM_EPT_NAME AND A.EFFDT = ( SELECT MAX(D.EFFDT) FROM PSTREEDEFN D WHERE D.SETCNTRLVALUE = A.SETCNTRLVALUE AND D.TREE_NAME = A.TREE_NAME AND D.EFFDT <= %CurrentDateIn ) AND EXISTS ( SELECT 'X' FROM PSTREENODE E , PS_PROJECT F WHERE E.SETCNTRLVALUE = A.SETCNTRLVALUE AND E.TREE_NAME = A.TREE_NAME AND E.EFFDT = A.EFFDT AND A.TREE_NODE_NUM BETWEEN E.TREE_NODE_NUM AND E.TREE_NODE_NUM_END AND E.TREE_NODE = B.PROJECT_ID AND A.SETCNTRLVALUE = F.BUSINESS_UNIT AND %Substring(A.TREE_NODE, 1, 15) = F.PROJECT_ID AND F.SUMMARY_PRJ = 'N')
|