PV_CAT_TREE_LVW

(SQL View)
 Index Back

SELECT DISTINCT a.SETID , a.TREE_NAME , a.TREE_NODE , a.TREE_NODE_NUM , b.CATEGORY_ID , D.RANGE_FROM , D.RANGE_TO , D.DYNAMIC_RANGE FROM PSTREENODE a , PS_ITM_CAT_TBL b , PSTREEDEFN c , PSTREELEAF d WHERE a.SETID = b.SETID AND a.TREE_NODE = b.CATEGORY_CD AND b.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ITM_CAT_TBL WHERE SETID = b.SETID AND CATEGORY_TYPE = b.CATEGORY_TYPE AND CATEGORY_ID = b.CATEGORY_ID AND CATEGORY_CD = b.CATEGORY_CD AND EFFDT <= %CurrentDateIn) AND b.EFF_STATUS = 'A' AND a.SETID = c.SETID AND a.TREE_NAME = c.TREE_NAME AND a.SETCNTRLVALUE = c.SETCNTRLVALUE AND a.EFFDT = ( SELECT MAX(EFFDT) FROM PSTREENODE WHERE a.SETID = SETID AND a.TREE_NAME = TREE_NAME AND a.SETCNTRLVALUE = SETCNTRLVALUE AND a.TREE_NODE_NUM = TREE_NODE_NUM AND a.TREE_NODE = TREE_NODE AND a.TREE_BRANCH = TREE_BRANCH AND a.EFFDT <= %CurrentDateIn) AND c.EFF_STATUS = 'A' AND C.TREE_STRCT_ID = 'ITEMS' AND a.SETID = D.SETID AND a.SETCNTRLVALUE = D.SETCNTRLVALUE AND a.TREE_NAME = D.TREE_NAME AND a.TREE_NODE_NUM = D.TREE_NODE_NUM AND a.TREE_BRANCH = D.TREE_BRANCH AND a.EFFDT = ( SELECT MAX(EFFDT) FROM PSTREELEAF WHERE a.SETID = SETID AND a.TREE_NAME = TREE_NAME AND a.SETCNTRLVALUE = SETCNTRLVALUE AND a.TREE_NODE_NUM = TREE_NODE_NUM AND a.TREE_BRANCH = TREE_BRANCH AND a.EFFDT <= %CurrentDateIn) AND D.RANGE_FROM <> ' ' AND D.DYNAMIC_RANGE = 'N'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
3 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
4 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
5 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
6 RANGE_FROM Character(30) VARCHAR2(30) NOT NULL Lower boundary for detail value that contains a range of values
7 RANGE_TO Character(30) VARCHAR2(30) NOT NULL Upper boundary for detail value that contains a range of values
8 DYNAMIC_RANGE Character(1) VARCHAR2(1) NOT NULL Tree Leaf Dynamic detail range flag