|
SELECT N.SETID ,N.TREE_NAME ,N.TREE_NODE ,N.PARENT_NODE_NAME ,B.BUSINESS_UNIT FROM PS_BUS_UNIT_TBL_GL B ,PSTREELEAF L ,PSTREENODE N WHERE N.EFFDT = ( SELECT MAX(X.EFFDT) FROM PSTREENODE X WHERE X.SETID = N.SETID AND X.TREE_NAME = N.TREE_NAME AND X.EFFDT <= %CurrentDateIn) AND L.SETID = N.SETID AND L.TREE_NAME = N.TREE_NAME AND L.EFFDT= ( SELECT MAX(EFFDT) FROM PSTREELEAF WHERE SETID = N.SETID AND TREE_NAME = N.TREE_NAME AND EFFDT <= N.EFFDT) AND L.TREE_NODE_NUM >= N.TREE_NODE_NUM AND L.TREE_NODE_NUM <= N.TREE_NODE_NUM_END AND B.BUSINESS_UNIT >= L.RANGE_FROM AND B.BUSINESS_UNIT <= L.RANGE_TO
|