PERSON_ADDR_HOM

(SQL View)
 Index Back

Person's Current Home Address

Contains the current EFFDT row the Address Type of HOME.. If only Future Dated rows exist for this person, then those rows are returned. One Row per EMPLID and ADDRESS_TYPE is returned. To select against this table, supply the EMPLID The EFFDT is for reference only. 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.ADDRESS_TYPE ,A.EFF_STATUS ,%subrec(ADDRESS_SBR, A) ,ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.EFFDT FROM PS_ADDRESSES A WHERE A.ADDRESS_TYPE = 'HOME' 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 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: ADDRESS_TYP_TBL

    3 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

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

    Prompt Table: SCC_CNT_ADFMTVW

    5 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    6 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    7 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    8 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    9 CITY Character(30) VARCHAR2(30) NOT NULL City
    10 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    11 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    12 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    13 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    14 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    15 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    16 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    17 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    18 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    19 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    20 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    21 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    22 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    23 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    24 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
    25 ASOFDATE Date(10) DATE As of Date