|
SELECT DISTINCT Z.COMMON_ID emplid ,Z.BUSINESS_UNIT ,Z.ITEM_TYPE ,Z.ITEM_TERM ,D.ITEM_TYPE_GROUP FROM PS_ITEM_SF Z ,PSTREEDEFN A ,PSTREENODE B ,PSTREELEAF C ,PS_ITEM_GROUP_TBL D ,PS_ITEM_GROUP_DTL E ,PS_BUS_UNIT_TBL_SF F WHERE Z.SA_ID_TYPE ='P' AND Z.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.SETID=B.SETID AND A.SETID=C.SETID AND A.SETID = D.TREE_NAME_SETID AND D.ITEM_TYPE_GROUP = E.ITEM_TYPE_GROUP AND D.SETID=E.SETID AND D.SETID = ( SELECT D1.SETID FROM PS_SET_CNTRL_REC D1 WHERE D1.SETCNTRLVALUE = F.INSTITUTION AND D1.RECNAME = 'ITEM_GROUP_TBL' AND D1.REC_GROUP_ID='ITEM TYPES') AND D.EFFDT=E.EFFDT AND D.EFF_STATUS='A' AND D.EFFDT= ( SELECT MAX(D2.EFFDT) FROM PS_ITEM_GROUP_TBL D2 WHERE D.SETID=D2.SETID AND D.ITEM_TYPE_GROUP = D2.ITEM_TYPE_GROUP AND D2.EFFDT <= %CurrentDateIn ) AND B.TREE_NODE = E.TREE_NODE AND A.VALID_TREE='Y' AND A.TREE_NAME = B.TREE_NAME AND A.TREE_NAME=C.TREE_NAME AND A.EFFDT=B.EFFDT AND A.EFFDT=C.EFFDT AND A.EFFDT= ( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A1.SETID = A.SETID AND A1.TREE_NAME = A.TREE_NAME AND A1.EFFDT <= %CurrentDateIn ) AND C.TREE_NODE_NUM >= B.TREE_NODE_NUM AND C.TREE_NODE_NUM <= B.TREE_NODE_NUM_END AND Z.ITEM_TYPE >= C.RANGE_FROM AND Z.ITEM_TYPE <= C.RANGE_TO
|