GVT_PD_EFDT_PSH

(SQL View)
 Index Back

Effective Dated Personal Data

PERS_DATA_EFFDT is one of the core records in the database. Use it to record effective dated personal data about an employee or applicant including name and address.

SELECT A.EMPLID , A.EFFDT , 'N' , %subrec(ADDRESS_SBR,A) , %subrec(ADDR_OTR_SBR,A) , A.MAR_STATUS , A.SMOKER , %subrec(NAMEGBL_SBR,A) , A.ADDRESS1_AC , A.ADDRESS2_AC , A.ADDRESS3_AC , A.CITY_AC FROM PS_GVT_PERS_DATA A , PS_GVT_JOB B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EMPL_RCD = 0 AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND B.GVT_STATUS_TYPE IN ('COR', 'COM') AND B.EFFSEQ = ( SELECT MAX(C.EFFSEQ) FROM PS_GVT_JOB C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT = B.EFFDT AND C.GVT_STATUS_TYPE IN ('COM', 'COR'))

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Default Value: NEW

    2 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    3 UPDATE_PERS_DATA Character(1) VARCHAR2(1) NOT NULL Update Personal Data

    Y/N Table Edit

    Default Value: N

    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 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

    Prompt Table: COUNTRY_TBL

    22 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
    23 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
    24 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
    25 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
    26 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
    27 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
    28 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

    Prompt Table: STATE_OTH

    29 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
    30 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
    31 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
    32 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
    AB=House Boat Reference
    WW=House Trailer Reference
    33 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
    34 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
    35 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
    36 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other

    Y/N Table Edit

    37 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
    38 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
    C=Common-Law
    D=Divorced
    E=Separated
    H=Head of Household
    L=DissDeclLost Civil Partner
    M=Married
    P=Civil Partnership
    S=Single
    T=Surviving Civil Partner
    U=Unknown
    V=Dissolved Civil Partnership
    W=Widowed

    Default Value: S

    39 SMOKER Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee or dependent is a smoker.
    N=Non Smoker
    Y=Smoker

    Y/N Table Edit

    Default Value: N

    40 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types

    Default Value: OPR_DEF_TBL_HR.COUNTRY_NM_FORMAT

    Prompt Table: NAME_FORMAT_TBL

    41 NAME Character(50) VARCHAR2(50) NOT NULL Name
    42 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
    43 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

    Prompt Table: NAME_PREFIX_TBL

    44 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix

    Prompt Table: NAME_SUFFIX_TBL

    45 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix

    Prompt Table: NM_ROYPREF_GBL

    46 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Suffix

    Prompt Table: NM_ROYSUFF_GBL

    47 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Title

    Prompt Table: TITLE_TBL

    48 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    49 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    50 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    51 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    52 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    53 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    54 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
    55 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    56 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
    57 PARTNER_LAST_NAME Character(30) VARCHAR2(30) NOT NULL The Partner Last Name is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name as their prefered last name.
    58 PARTNER_ROY_PREFIX Character(15) VARCHAR2(15) NOT NULL The Partner Royal Prefix is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name and prefix as their prefered last name.

    Prompt Table: NM_ROYPREF_GBL

    59 LAST_NAME_PREF_NLD Character(1) VARCHAR2(1) NOT NULL The field Last Name Preference is used in the Netherlands specific for people who are married to indicated their preference regarding the last name, which can be 4 choices based on the own last name and the partners last name.
    1=Own Name
    2=Name Partner
    3=Name Partner + Own Name
    4=Own Name + Name Partner

    Default Value: 1

    60 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    61 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
    62 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    63 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    64 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    65 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City