AV_RELATION_VW

(SQL View)
 Index Back

Relationships

Use AV_ RELATIONSHIPS to record information about a person's relationship to other person. You can enter multiple people. The GUARDIAN_RELATIONS field allow you to enter information about the legal relationship to this person, while the PEOPLE_RELATION field identifies the familial or other relationship to the person. For example, a grandparent may legally be a guardian and you would want to capture both these pieces of information about the relationship to the person. ======================================================== modified on 2/16/01 for HR merge impact. The 2 fields below are no longer on the Relationships record so they were removed from this view. BEGIN_DT and END_DT were removed, they were after Citizenship_Status and before Address1 field in the query. ========================================================

SELECT A.EMPLID , A.RELATIONSHIP_NBR , A.EFFDT , A.EFF_STATUS , 'P' , 'xx' , '00' , A.PEOPLE_RELATION , A.GUARDIAN_RELATIONS , A.NAME , A.EMPLID_RELATED , A.RELATION_NBR_RCP , A.HIGHEST_EDUC_LVL , A.GUARDIAN_OCC , A.EXT_ORG_ID , A.EMPLOYER , A.RELATIONS_INCOME , A.MAR_STATUS , A.RESIDENCY_DT , A.STATE_RESIDENCY , A.COUNTRY_RESIDENCY , A.NID_COUNTRY , A.NATIONAL_ID_TYPE , A.NATIONAL_ID , A.CURRENCY_CD , A.CHG_ADDR , A.CHG_OTHER , A.NAME_PREFIX , A.NAME_SUFFIX , A.SALUTATION , A.CONTACT_THRU , A.PHONE_DAY , A.PHONE_EVE , A.PHONE_FAX , A.EMAILID , A.URL_ADDRESS , A.SEX , A.CITIZENSHIP_STATUS , A.JOINT_RECORD , A.ADDR_TYPE3 , A.ADDRESS_TYPE , A.JOINT_ADDRESS , A.JOINT_ADDRESS_RCP , 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.COMMENTS FROM PS_RELATIONSHIPS A WHERE A.EFFDT= ( SELECT MAX (A1.EFFDT) FROM PS_RELATIONSHIPS A1 WHERE A1.EMPLID=A.EMPLID AND A1.RELATIONSHIP_NBR=A.RELATIONSHIP_NBR)

  • Parent record: PERSON

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

    Prompt Table: AV_PEOPLE_SRCH

    2 RELATIONSHIP_NBR Number(2,0) SMALLINT NOT NULL Relationship Nbr
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    5 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
    O=Organization
    P=Person
    6 PE_RELATION Character(2) VARCHAR2(2) NOT NULL Relationship
    7 PE_RELATE_NBR Character(10) VARCHAR2(10) NOT NULL Relation Nbr
    8 PEOPLE_RELATION Character(2) VARCHAR2(2) NOT NULL Relationship

    Default Value: NI

    9 GUARDIAN_RELATIONS Character(2) VARCHAR2(2) NOT NULL Guardian Relationship
    LG=Legal Guardian
    NA=Not Applicable
    OT=Other
    PG=Parent Guardian
    SG=Self Guardian
    XG=Primary Guardian

    Default Value: NA

    10 NAME Character(50) VARCHAR2(50) NOT NULL Name
    11 EMPLID_RELATED Character(11) VARCHAR2(11) NOT NULL Related People ID

    Prompt Table: AV_PEOPLE_SRCH

    12 RELATION_NBR_RCP Number(2,0) SMALLINT NOT NULL Reciprocal Relationship Number
    13 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level
    14 GUARDIAN_OCC Character(10) VARCHAR2(10) NOT NULL Guardian Occupation

    Prompt Table: US_SOC_TBL

    15 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

    Prompt Table: AV_EXT_ORG_SRCH

    16 EMPLOYER Character(30) VARCHAR2(30) NOT NULL Employer
    17 RELATIONS_INCOME Number(9,0) DECIMAL(9) NOT NULL Relations Income
    18 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
    19 RESIDENCY_DT Date(10) DATE Residency Date
    20 STATE_RESIDENCY Character(6) VARCHAR2(6) NOT NULL State of Residency

    Prompt Table: STATE_RES_VW

    21 COUNTRY_RESIDENCY Character(3) VARCHAR2(3) NOT NULL Country of Residency

    Default Value: INSTALLATION.COUNTRY

    Prompt Table: COUNTRY_TBL

    22 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
    23 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
    24 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
    25 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL

    26 CHG_ADDR Character(1) VARCHAR2(1) NOT NULL Address Changed

    Y/N Table Edit

    Default Value: N

    27 CHG_OTHER Character(1) VARCHAR2(1) NOT NULL Other Changed

    Y/N Table Edit

    Default Value: N

    28 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

    Prompt Table: NAME_PREFIX_TBL

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

    Prompt Table: NAME_SUFFIX_TBL

    30 SALUTATION Character(20) VARCHAR2(20) NOT NULL Salutation

    Prompt Table: SALUTATION_TBL

    31 CONTACT_THRU Number(2,0) SMALLINT NOT NULL Contact Thru

    Prompt Table: REL_CONTACT_VW

    32 PHONE_DAY Character(30) VARCHAR2(30) NOT NULL Daytime Telephone
    33 PHONE_EVE Character(30) VARCHAR2(30) NOT NULL Evening Telephone
    34 PHONE_FAX Character(30) VARCHAR2(30) NOT NULL Fax Nbr
    35 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
    36 URL_ADDRESS Character(254) VARCHAR2(254) NOT NULL URL Address
    37 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec
    38 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status

    Prompt Table: CITIZEN_STS_TBL

    39 JOINT_RECORD Character(1) VARCHAR2(1) NOT NULL Create Joint Records
    40 ADDR_TYPE3 Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: RELATED_ADDR_VW

    41 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

    Prompt Table: ADDRESSES_EFFVW

    42 JOINT_ADDRESS Character(1) VARCHAR2(1) NOT NULL Joint Address

    Y/N Table Edit

    Default Value: N

    43 JOINT_ADDRESS_RCP Character(1) VARCHAR2(1) NOT NULL Joint Address

    Y/N Table Edit

    Default Value: N

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

    Prompt Table: COUNTRY_TBL

    45 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    46 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    47 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    48 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    49 CITY Character(30) VARCHAR2(30) NOT NULL City
    50 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    51 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    52 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    53 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    54 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    55 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    56 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    57 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: %EDIT_STATE

    58 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    59 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    60 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    61 COMMENTS Long Character CLOB Comment