PV_TREERANGE_VW

(SQL View)
 Index Back

SELECT A.SETID , A.TREE_NAME ,B.TREE_NODE_NUM ,B.TREE_NODE ,B.TREE_BRANCH ,C.DYNAMIC_RANGE ,C.RANGE_FROM ,C.RANGE_TO ,T.CATEGORY_ID FROM PSTREEDEFN A , PSTREENODE B , PSTREELEAF C , PS_ITM_CAT_TBL T , PS_INSTALLATION_PO P WHERE A.TREE_STRCT_ID = 'ITEMS' AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A.SETID = A1.SETID AND A.SETCNTRLVALUE = A1.SETCNTRLVALUE AND A.TREE_NAME = A1.TREE_NAME AND A1.EFFDT <= %CurrentDateIn) AND A.SETID = B.SETID AND A.SETCNTRLVALUE = B.SETCNTRLVALUE AND A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND B.SETID = C.SETID AND B.SETCNTRLVALUE = C.SETCNTRLVALUE AND B.TREE_NAME = C.TREE_NAME AND B.TREE_NODE_NUM = C.TREE_NODE_NUM AND B.TREE_BRANCH = C.TREE_BRANCH AND B.SETID = T.SETID AND T.CATEGORY_TYPE = P.CATEGORY_TYPE AND B.TREE_NODE = T.CATEGORY_CD AND T.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PS_ITM_CAT_TBL T1 WHERE T.SETID = T1.SETID AND T.CATEGORY_TYPE = T1.CATEGORY_TYPE AND T.CATEGORY_CD = T1.CATEGORY_CD AND T.CATEGORY_ID = T1.CATEGORY_ID AND T1.EFFDT <= %CurrentDateIn) AND T.EFF_STATUS = 'A'

# 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_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
4 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
5 TREE_BRANCH Character(20) VARCHAR2(20) NOT NULL Tree Branch
6 DYNAMIC_RANGE Character(1) VARCHAR2(1) NOT NULL Tree Leaf Dynamic detail range flag
7 RANGE_FROM Character(30) VARCHAR2(30) NOT NULL Lower boundary for detail value that contains a range of values
8 RANGE_TO Character(30) VARCHAR2(30) NOT NULL Upper boundary for detail value that contains a range of values
9 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID