PD_ADDRESS_VW

(SQL View)
 Index Back

Person's Current Addresses

Contains the current EFFDT row for each Address Type a Person Has. 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 and ADDRESS_TYPE. 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 ,A1.ADDR_TYPE_DESCR ,%subrec(ADDROTH_CPY_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.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)) ) ) AND A.EFF_STATUS = 'A'

  • 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 ADDR_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Address Type
    5 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
    6 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
    7 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
    8 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

    Prompt Table: COUNTRY_TBL

    9 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
    10 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
    11 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
    12 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

    Prompt Table: STATE_NAMES_OTH

    13 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
    14 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
    15 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
    16 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
    AB=House Boat Reference
    WW=House Trailer Reference
    17 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
    18 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
    19 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
    20 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other
    21 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
    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