Created by Corina C Larsen, last modified by Miki Yanagi on Jul 23, 2021
This is a view of RELATIONSHIPS table where the most recent active data is pulled.
| Key | Record.Fieldname | Format | XLAT | Heading Text | Notes |
|---|---|---|---|---|---|
| ADDRESS1 - Address Line 1 | Char55 | Address 1 | |||
| ADDRESS2 - Address Line 2 | Char55 | Address 2 | |||
| ADDRESS3 - Address Line 3 | Char55 | Address 3 | |||
| ADDRESS4 - Address Line 4 | Char55 | Address 4 | |||
| ADDRESS_TYPE - Address Type | Char4 | Addr Type | |||
| ADDR_TYPE3 - Address Type | Char4 | Address Type | |||
| ALL_COMMUNICATIONS - All Communications | Char1 | All | |||
| BIRTHCOUNTRY - Birth Country | Char3 | Country | |||
| CHG_ADDR - Address Changed | Char1 | Addr Chg | |||
| CHG_OTHER - Other Changed | Char1 | Other Chg | |||
| CITIZENSHIP_STATUS - Citizenship Status | Char1 | Status | |||
| CITIZEN_COUNTRY - Country of Citizenship | Char3 | Citizen Country | |||
| CITY - City | Char30 | City | |||
| COMMENTS - Comment | Text | Comment | |||
| CONTACT_THRU - Contact Thru | Num2.0 | Contact | |||
| COUNTRY - Country | Char3 | Country | |||
| COUNTRY_RESIDENCY - Country of Residency | Char3 | Country | |||
| COUNTY - County | Char30 | County | |||
| CURRENCY_CD - Currency Code | Char3 | Currency | |||
| DISSOLVE_REASON - Dissolution Reason | Char2 | N | Dissolv Reason | ||
| → | EFFDT - Effective Date | Date | Eff Date | ||
| EFF_STATUS - Status as of Effective Date | Char1 | N | Status | ||
| EMAILID - Email ID | Char70 | Email ID | |||
| → | EMPLID - Empl ID | Char11 | ID | ||
| EMPLID_RELATED - Related People ID | Char11 | Related ID | |||
| EMPLOYER - Employer | Char30 | Employer | |||
| EXT_ORG_ID - External Org ID | Char11 | Org ID | |||
| E_ADDR_TYP2 - Email Type | Char4 | N | Type | ||
| E_ADDR_TYPE - Email Type | Char4 | N | Type | ||
| GEO_CODE - Tax Vendor Geographical Code | Char11 | Geo Code | |||
| GUARDIAN_OCC - Guardian Occupation | Char10 | Occupation | |||
| GUARDIAN_RELATIONS - Guardian Relationship | Char2 | N | Guardian | ||
| HIGHEST_EDUC_LVL - Highest Education Level | Char2 | N | Hi Educ Lv | ||
| HOUSE_TYPE - House Type | Char2 | N | House | ||
| IN_CITY_LIMIT - In City Limit | Char1 | In Cty Lmt | |||
| JOINT_ADDRESS - Joint Address | Char1 | Joint Address | |||
| JOINT_ADDRESS_RCP - Joint Address | Char1 | Joint Address | |||
| JOINT_RECORD - Create Joint Records | Char1 | Joint Record | |||
| MAR_STATUS - Marital Status | Char1 | N | Mar Status | ||
| NAME - Name | Char50 | Name | |||
| NAME_PREFIX - Name Prefix | Char4 | Prefix | |||
| NAME_SUFFIX - Name Suffix | Char15 | Suffix | |||
| NATIONAL_ID - National ID | Char20 | NID | |||
| NUM1 - Number 1 | Char6 | Nbr 1 | |||
| NUM2 - Number 2 | Char6 | Nbr 2 | |||
| PEOPLE_RELATION - Relationship | Char2 | N | Relation | Click here for the list of values | |
| PHONE_DAY - Daytime Telephone | Char30 | Day Phone | |||
| PHONE_EVE - Evening Telephone | Char30 | Eve Phone | |||
| PHONE_FAX - Fax Nbr | Char30 | Fax Nbr | |||
| POSTAL - Postal Code | Char12 | Postal | |||
| → | RELATIONSHIP_NBR - Relationship Nbr | Num2.0 | Relate Nbr | Sequence number for the relationship | |
| RELATIONS_INCOME - Relations Income | Num9.0 | Rel Income | |||
| RELATION_NBR_RCP - Reciprocal Relationship Number | Num2.0 | Recip Nbr | |||
| RESIDENCY_DT - Residency Date | Date | Res Date | |||
| SALUTATION - Salutation | Char20 | Salutation | |||
| SEX - Gender | Char1 | N | Sex | ||
| STATE - State | Char6 | State | |||
| STATE_RESIDENCY - State of Residency | Char6 | State | |||
| URL_ADDRESS - URL Address | Char254 | URL |
SQL for RELATION_VW Expand source
SELECT A.EMPLID,
A.RELATIONSHIP_NBR,
A.EFFDT,
A.EFF_STATUS,
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.NATIONAL_ID,
A.CURRENCY_CD,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.ADDRESS4,
A.CITY,
A.IN_CITY_LIMIT,
A.COUNTY,
A.STATE,
A.POSTAL,
A.COUNTRY,
a.num1,
a.num2,
a.house_type,
a.geo_code,
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.CITIZEN_COUNTRY,
A.CITIZENSHIP_STATUS,
A.ALL_COMMUNICATIONS,
A.COMMENTS,
A.JOINT_RECORD,
A.JOINT_ADDRESS,
A.JOINT_ADDRESS_RCP,
A.ADDR_TYPE3,
A.ADDRESS_TYPE,
A.DISSOLVE_REASON,
A.E_ADDR_TYPE,
A.E_ADDR_TYP2,
A.BIRTHCOUNTRY
FROM PS_RELATIONSHIPS A
WHERE A.EFFDT = (SELECT MAX(X.EFFDT)
FROM PS_RELATIONSHIPS X
WHERE X.EMPLID = A.EMPLID
AND X.RELATIONSHIP_NBR = A.RELATIONSHIP_NBR
AND X.EFFDT <= TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD'))
AND EFF_STATUS = 'A';