GPTH_DIS_ADD_VW

(SQL View)
 Index Back

Person's Addr Payroll period

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 G.PROCESS_INSTANCE ,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 ,E.DESCR FROM PS_ADDRESSES A LEFT OUTER JOIN PS_STATE_TBL E ON E.COUNTRY = A.COUNTRY AND E.STATE = A.STATE , PS_ADDRESS_TYP_TBL A1 ,PS_GPTH_DIS_RPT G WHERE A.EMPLID=G.EMPLID AND A.ADDRESS_TYPE='LEGL' AND 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 <= G.DATE_DUE ) AND A.EFF_STATUS = 'A'

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: ADDRESS_TYP_TBL

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

    Default Value: A

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

    Prompt Table: SCC_CNT_ADFMTVW

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

    Prompt Table: STATE_TBL

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

    Y/N Table Edit

    23 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    24 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    25 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    26 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
    27 ASOFDATE Date(10) DATE As of Date
    28 DESCR Character(30) VARCHAR2(30) NOT NULL Description