AV_ADDRESS_2_VW

(SQL View)
 Index Back

Address Type

ADDRESSES contains effective dated history for personal addresses. 10/24/2003 8.9 Person Model Before: SELECT EMPLID ,ADDRESS_TYPE ,EFFDT ,EFF_STATUS ,COUNTRY ,ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,CITY ,NUM1 ,NUM2 ,HOUSE_TYPE ,ADDR_FIELD1 ,ADDR_FIELD2 ,ADDR_FIELD3 ,COUNTY ,STATE ,POSTAL ,GEO_CODE ,IN_CITY_LIMIT ,ADDRESS1_AC ,ADDRESS2_AC ,ADDRESS3_AC ,CITY_AC ,REG_REGION ,LOC_ADDR ,LOC_ADDR_DATA ,LOC_ADDR_LINE ,LOC_ADDR_TYPE ,EXT_ORG_ID ,ORG_LOCATION ,DESCR_EXT_ORG ,DESCR_ORG_LOCATION ,MAINT_ADDR_MANUAL ,MAINT_OTHER_MANUAL ,ORG_CONTACT ,CONTACT_NAME ,DATA_SOURCE ,SEASONAL_ADDR FROM PS_ADDRESSES

SELECT A.EMPLID ,A.ADDRESS_TYPE ,A.EFFDT ,A.EFF_STATUS ,A.COUNTRY ,A.ADDRESS1 ,A.ADDRESS2 ,A.ADDRESS3 ,A.ADDRESS4 ,A.CITY ,A.NUM1 ,A.NUM2 ,A.HOUSE_TYPE ,A.ADDR_FIELD1 ,A.ADDR_FIELD2 ,A.ADDR_FIELD3 ,A.COUNTY ,A.STATE ,A.POSTAL ,A.GEO_CODE ,A.IN_CITY_LIMIT ,A.ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.REG_REGION ,B.LOC_ADDR ,B.LOC_ADDR_DATA ,B.LOC_ADDR_LINE ,B.LOC_ADDR_TYPE ,B.EXT_ORG_ID ,B.ORG_LOCATION ,B.DESCR_EXT_ORG ,B.DESCR_ORG_LOCATION ,B.MAINT_ADDR_MANUAL ,B.MAINT_OTHER_MANUAL ,B.ORG_CONTACT ,B.CONTACT_NAME ,B.DATA_SOURCE ,B.SEASONAL_ADDR FROM PS_SCC_CUR_ADDR_VW A LEFT OUTER JOIN PS_ADDRESSES_SA B ON A.EMPLID = B.EMPLID AND A.ADDRESS_TYPE = B.ADDRESS_TYPE AND A.EFFDT = B.EFFDT WHERE A.EFF_STATUS = 'A'

  • Related Language Record: AVADDRESS2VWLNG

    • # 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: HCR_ADDR_TYP_I

    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

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

    Default Value: A

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

    Prompt Table: SCC_CNT_ADFMTVW

    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 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    27 LOC_ADDR Character(10) VARCHAR2(10) NOT NULL Location Address Name

    Prompt Table: LOC_ADDR_TBL

    28 LOC_ADDR_DATA Character(10) VARCHAR2(10) NOT NULL Location Address Data
    29 LOC_ADDR_LINE Character(1) VARCHAR2(1) NOT NULL Location Address Print Line
    1=Print as Line 1
    2=Print As Line 2
    3=Print as Line 3
    4=Print As Line 4
    30 LOC_ADDR_TYPE Character(4) VARCHAR2(4) NOT NULL Location Address Type
    MLBX=Mailbox
    ROOM=Room
    31 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

    Prompt Table: EXT_ORG_TBL

    32 ORG_LOCATION Number(3,0) SMALLINT NOT NULL Location Nbr

    Prompt Table: ORG_LOCATION

    33 DESCR_EXT_ORG Character(30) VARCHAR2(30) NOT NULL Org Name
    34 DESCR_ORG_LOCATION Character(30) VARCHAR2(30) NOT NULL Org Location
    35 MAINT_ADDR_MANUAL Character(1) VARCHAR2(1) NOT NULL Maintain Address Manually

    Y/N Table Edit

    Default Value: N

    36 MAINT_OTHER_MANUAL Character(1) VARCHAR2(1) NOT NULL Maintain Other Data Manually

    Y/N Table Edit

    Default Value: N

    37 ORG_CONTACT Number(4,0) SMALLINT NOT NULL Contact Nbr

    Prompt Table: ORG_CONTACT

    38 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL Contact Name
    39 DATA_SOURCE Character(2) VARCHAR2(2) NOT NULL Data Source
    AE=Ad-Hoc Employee
    AO=Ad-Hoc Organizational
    E=Extract
    40 SEASONAL_ADDR Character(1) VARCHAR2(1) NOT NULL Seasonal Address