REGION_ZIP_VW

(SQL View)
 Index Back

Region ZipCode Ranges View

This view returns all the zipcode ranges beneath each region node in the region tree. The full hierarchy is returned beneath each region.

SELECT A.SETID ,D.REGION ,A.TREE_NODE_NUM ,B.RANGE_FROM ,B.RANGE_TO ,C.TREE_EFFDT FROM PSTREENODE A ,PSTREELEAF B ,PS_R_AD505 C ,PS_REGION_TBL D WHERE A.TREE_NAME = 'REGION' AND A.EFFDT = C.TREE_EFFDT AND A.SETID = C.SETID AND B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT AND D.SETID = A.SETID AND D.REGION = A.TREE_NODE AND D.EFFDT = (SELECT MAX(E.EFFDT) FROM PS_REGION_TBL E WHERE E.SETID = D.SETID AND E.REGION = D.REGION AND E.EFFDT <= C.TREE_EFFDT) AND D.EFF_STATUS = 'A' AND B.TREE_NODE_NUM BETWEEN A.TREE_NODE_NUM AND A.TREE_NODE_NUM_END

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Prompt Table: SETID_TBL

2 REGION Character(10) VARCHAR2(10) NOT NULL Region

Prompt Table: REGION_TBL

3 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
4 RANGE_FROM Character(30) VARCHAR2(30) NOT NULL Lower boundary for detail value that contains a range of values
5 RANGE_TO Character(30) VARCHAR2(30) NOT NULL Upper boundary for detail value that contains a range of values
6 TREE_EFFDT Date(10) DATE NOT NULL Effective date of the Tree