REGN_ORG_ZIP_VW

(SQL View)
 Index Back

Region ZipCode Org View

This view uses REGION_ZIP_VW to retrieve the external organizations where the zipcode is between the ranges below the region on the Region Tree. It is used as a prompt on the recruiter tables.

SELECT DISTINCT A.SETID , A.REGION , B.EXT_ORG_ID , B.DESCR , B.OTH_NAME_SORT_SRCH , B.EXT_ORG_TYPE , C.CITY , C.STATE , C.COUNTRY , D.SCHOOL_CODE , D.LS_SCHOOL_TYPE , D.FICE_CD , D.ATP_CD , A.TREE_NODE_NUM FROM PS_REGION_ZIP_VW A , PS_EXT_ORG_TBL B , PS_ORG_LOCATION C , PS_EXT_ORG_TBL_ADM D WHERE B.EXT_ORG_ID = C.EXT_ORG_ID AND B.ORG_LOCATION = C.ORG_LOCATION AND %EffdtCheck(ORG_LOCATION C1 , C , %CurrentDateIn) AND C.EFF_STATUS = 'A' AND B.EXT_ORG_ID = D.EXT_ORG_ID AND %EffdtCheck(EXT_ORG_TBL_ADM D1 , D , %CurrentDateIn) AND C.POSTAL <> ' ' AND C.COUNTRY %Concat C.POSTAL BETWEEN A.RANGE_FROM AND A.RANGE_TO AND %EffdtCheck(EXT_ORG_TBL B1, B, %CurrentDateIn) AND B.EFF_STATUS = 'A'

  • Related Language Record: RGN_O_Z_VW_LNG

    • # 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 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

    Prompt Table: EXT_ORG_TBL

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 OTH_NAME_SORT_SRCH Character(50) VARCHAR2(50) NOT NULL Search Name
    6 EXT_ORG_TYPE Character(4) VARCHAR2(4) NOT NULL Organization Type

    Prompt Table: SCC_ORG_TYP_TBL

    7 CITY Character(30) VARCHAR2(30) NOT NULL City
    8 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_NAMES_TBL

    9 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    10 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code

    Prompt Table: SCHOOL_TBL

    11 LS_SCHOOL_TYPE Character(3) VARCHAR2(3) NOT NULL School Type

    Prompt Table: LS_SCHL_TYP_TBL

    12 FICE_CD Character(6) VARCHAR2(6) NOT NULL FICE Code
    13 ATP_CD Character(6) VARCHAR2(6) NOT NULL ATP Code
    14 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number