PERSON_ADDROTH

(SQL View)
 Index Back

Current Mailing Addresses

Contains the Current Effdt Mailing Address for Persons. If only future dated rows exist for a person, then those rows are returned. This view is keyed on EMPLID only because only one row of data is returned for each EMPLID. The EFFDT and the ADDR_TYPE are included just to provide reference back to ADDRESSES but are not part of the key. This is provided for backward compatability. For future coding use the PER_CUR_ADDR and select the ADDRESS_TYPE of MAIL. No Query Security record is specified because in query this would be one of the joined in records. The main record should contain the query security.

SELECT A.EMPLID ,A.COUNTRY ,A.ADDRESS1 ,A.ADDRESS2 ,A.ADDRESS3 , A.ADDRESS4 ,A.CITY ,A.COUNTY ,A.STATE ,A.POSTAL ,A.NUM1 ,A.NUM2 ,A.HOUSE_TYPE ,A.ADDR_FIELD1 ,A.ADDR_FIELD2 ,A.ADDR_FIELD3 ,A.IN_CITY_LIMIT ,A.GEO_CODE ,ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.ADDRESS_TYPE ,A.EFFDT ,A.EFF_STATUS FROM PS_ADDRESSES A WHERE A.ADDRESS_TYPE = 'MAIL' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ADDRESSES B WHERE B.EMPLID = A.EMPLID AND B.ADDRESS_TYPE = A.ADDRESS_TYPE AND ( B.EFFDT <= %CurrentDateIn OR (B.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(C.EFFDT) FROM PS_ADDRESSES C WHERE C.EMPLID = B.EMPLID AND C.ADDRESS_TYPE = B.ADDRESS_TYPE) ) ))

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

    Prompt Table: COUNTRY_TBL

    3 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
    4 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
    5 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
    6 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
    7 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
    8 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
    9 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

    Prompt Table: STATE_OTH

    10 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
    11 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
    12 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
    13 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
    AB=House Boat Reference
    WW=House Trailer Reference
    14 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
    15 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
    16 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
    17 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other

    Y/N Table Edit

    18 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
    19 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    20 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    21 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    22 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
    23 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: ADDRESS_TYP_TBL

    24 ASOFDATE Date(10) DATE As of Date
    25 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A