PERSON_EE_ADDR

(SQL View)
 Index Back

Person's SS Addresses

The view retrieves the current, the future and the pending approval addresses for the all ddress types configured to be displayed in self service

SELECT A.EMPLID ,A.ADDRESS_TYPE ,A.EFF_STATUS ,A1.ADDR_TYPE_DESCR ,%subrec(ADDRESS_SBR, A) ,ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.EFFDT ,' ' FROM PS_ADDRESSES A , PS_ADDRESS_TYP_TBL A1 WHERE A.ADDRESS_TYPE = A1.ADDRESS_TYPE AND A.EFF_STATUS = 'A' 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) UNION SELECT A.EMPLID ,A.ADDRESS_TYPE ,A.EFF_STATUS ,A1.ADDR_TYPE_DESCR ,%subrec(ADDRESS_SBR, A) ,ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.EFFDT ,' ' FROM PS_ADDRESSES A , PS_ADDRESS_TYP_TBL A1 WHERE A.ADDRESS_TYPE = A1.ADDRESS_TYPE AND A.EFF_STATUS = 'A' AND A.EFFDT > %CurrentDateIn UNION SELECT A.EMPLID ,A.ADDRESS_TYPE ,A.EFF_STATUS ,A1.ADDR_TYPE_DESCR ,%subrec(ADDRESS_SBR, A) ,ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.ACTION_DT_SS ,A.SS_STAT_INDICATOR FROM PS_HR_ADDR_CHG_DAT A , PS_ADDR_CFG_TYP_SS A1 WHERE A.ADDRESS_TYPE = A1.ADDRESS_TYPE AND A.SS_STAT_INDICATOR IN ('M', 'I') AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_HR_ADDR_CHG_DAT A2 WHERE A2.EMPLID = A.EMPLID AND A2.ADDRESS_TYPE = A.ADDRESS_TYPE AND A2.ACTION_DT_SS = A.ACTION_DT_SS)

  • Related Language Record: PERSON_ADDR_LNG

  • 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: TYPE_CNTL_ADDR

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

    Default Value: A

    4 ADDR_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Address Type Description
    5 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

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

    Prompt Table: STATE_TBL

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

    Y/N Table Edit

    22 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    23 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    24 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    25 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
    26 ASOFDATE Date(10) DATE As of Date
    27 SS_STAT_INDICATOR Character(1) VARCHAR2(1) NOT NULL Used on "_DAT" records for 8.3 Workflow Enhancements
    A=Approved
    B=CI failed - Errors & Warnings
    C=Cancelled
    D=Denied
    E=Errors Encountered
    I=In Approval Process
    M=Administrator Action Required
    S=Success
    W=Warnings Encountered