GM_XML_COPI(SQL View) |
Index Back |
|---|
| SELECT A.OPRID , A.RUN_CNTL_ID , A.PROPOSAL_ID , A.VERSION_ID , B.NAME , B.FIRST_TIME_PI , B.EMPLID , B.DEGREE_EARNED , B.DESCR , ' ' , B.DESCR1 , ' ' , ' ' , F.ADDRESS1_OTHER , F.ADDRESS2_OTHER , F.ADDRESS3_OTHER , F.ADDRESS4_OTHER , F.CITY_OTHER , F.COUNTRY_OTHER , F.COUNTY_OTHER , F.POSTAL_OTHER , F.STATE_OTHER , B.BIRTHDATE , B.NATIONAL_ID , B.SEX , CASE B.SEX WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' ELSE 'Do Not Wish to Provide' END , G.ETHNIC_GROUP , H.AMERICAN_INDIAN , 'American Indian or Alaska Native' , H.ASIAN , 'Asian' , H.BLACK , 'Black or African American' , H.NATIVE_HAWAIIAN , 'Native Hawaiian or Other Pacific Islander' , H.NOT_APPLICABLE , 'Do Not Wish to Provide' , H.WHITE , 'White' , G.NAME_PREFIX , G.NAME_SUFFIX , G.FIRST_NAME , G.LAST_NAME , G.MIDDLE_NAME , G.DIS_HEARING , 'Hearing' , G.DIS_VISION , 'Visual' , G.DIS_OTHER , 'Other' , G.DIS_NONE , 'None' , G.DIS_DONT_PROVIDE , 'Do Not Wish to Provide' , G.DIS_MOB_ORTH , 'Mobility/Orthopedic Impairment' , 'Co-PD/PI' , B.CREDENTIAL , G.ETHNIC_GROUP , G.PROF_ROLE_TYPE ,CASE G.CITIZENSHIP_STATUS WHEN '1' THEN 'US Citizen' WHEN '2' THEN 'US Citizen' WHEN '5' THEN 'Permanent Resident' WHEN '3' THEN 'Permanent Resident' WHEN 'E' THEN 'Do Not Wish to Provide' WHEN 'N' THEN 'Do Not Wish to Provide' ELSE 'Other non-US Citizen' END , M.XLATLONGNAME FROM PS_GM_XML_PROP_TBL A , PS_GM_EC_PERS_PI B , PS_GM_PERS_ADDR_VW F , PS_GM_EC_PROJ_PI_2 G , PS_GM_EC_RACE H , PS_GM_ETHNICITY_VW M WHERE A.OPRID = B.OPRID AND A.RUN_CNTL_ID = B.RUN_CNTL_ID AND F.EMPLID = B.EMPLID AND B.ECTRANSID = G.ECTRANSID AND B.ECQUEUEINSTANCE = G.ECQUEUEINSTANCE AND B.ECTRANSINOUTSW = G.ECTRANSINOUTSW AND B.OPRID = G.OPRID AND B.RUN_CNTL_ID = G.RUN_CNTL_ID AND B.BUSINESS_UNIT = G.BUSINESS_UNIT AND B.PROPOSAL_ID = G.PROPOSAL_ID AND B.EMPLID = G.EMPLID AND A.SUB_PROP_NBR = G.SUB_PROP_NBR AND B.EMPLID = H.EMPLID AND M.FIELDVALUE = G.ETHNIC_GROUP AND G.PROF_ROLE_TYPE = 'CPI' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | RUN_CNTL_ID |
Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |
| 3 | PROPOSAL_ID | Character(25) | VARCHAR2(25) NOT NULL | Proposal ID |
| 4 | VERSION_ID | Character(7) | VARCHAR2(7) NOT NULL | Version ID |
| 5 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 6 | FIRST_TIME_PI | Character(1) | VARCHAR2(1) NOT NULL | First Time PI |
| 7 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 8 | DEGREE_EARNED | Character(30) | VARCHAR2(30) NOT NULL | Degree Earned |
| 9 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 10 | EMAIL_ADDR | Character(70) | VARCHAR2(70) NOT NULL | Email Address |
| 11 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 12 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 13 | FAX | Character(24) | VARCHAR2(24) NOT NULL | Fax Number |
| 14 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 15 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 16 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 17 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 18 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 19 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 20 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 21 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 22 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 23 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 24 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 25 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown |
| 26 | SEX_DESC | Character(25) | VARCHAR2(25) NOT NULL | Sex |
| 27 | ETHNIC_GROUP | Character(1) | VARCHAR2(1) NOT NULL |
Ethnic Group
1=White 2=Black/African American 3=Hispanic/Latino 4=Asian 5=American Indian/Alaska Native 6=Not Specified 7=Native Hawaiian/Oth Pac Island |
| 28 | AMERICAN_INDIAN | Character(1) | VARCHAR2(1) NOT NULL |
American Indian/Alaska Native
Y/N Table Edit |
| 29 | AMERICAN_INDIAN_D | Character(50) | VARCHAR2(50) NOT NULL | American Indian/Alaska Native |
| 30 | ASIAN | Character(1) | VARCHAR2(1) NOT NULL |
Asian
Y/N Table Edit |
| 31 | ASIAN_D | Character(50) | VARCHAR2(50) NOT NULL | Asian |
| 32 | BLACK | Character(1) | VARCHAR2(1) NOT NULL |
Black/African American
Y/N Table Edit |
| 33 | BLACK_D | Character(50) | VARCHAR2(50) NOT NULL | Black/African American |
| 34 | NATIVE_HAWAIIAN | Character(1) | VARCHAR2(1) NOT NULL |
Native Hawaiian/Pacific Island
Y/N Table Edit |
| 35 | NATIVE_HAWAIIAN_D | Character(50) | VARCHAR2(50) NOT NULL | Native Hawaiian/Pacific Island |
| 36 | NOT_APPLICABLE | Character(1) | VARCHAR2(1) NOT NULL |
Not Applicable
Y/N Table Edit |
| 37 | NOT_APPLICABLE_D | Character(50) | VARCHAR2(50) NOT NULL | Not Applicable |
| 38 | WHITE | Character(1) | VARCHAR2(1) NOT NULL |
White
Y/N Table Edit |
| 39 | WHITE_D | Character(50) | VARCHAR2(50) NOT NULL | White |
| 40 | NAME_PREFIX | Character(4) | VARCHAR2(4) NOT NULL |
Name Prefix
1=Mr. 2=Mrs. 3=Ms. |
| 41 | NAME_SUFFIX | Character(15) | VARCHAR2(15) NOT NULL | Name Suffix |
| 42 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 43 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 44 | MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | Middle Name |
| 45 | DIS_HEARING | Character(1) | VARCHAR2(1) NOT NULL | Hearing |
| 46 | DIS_HEARING_D | Character(40) | VARCHAR2(40) NOT NULL | Hearing |
| 47 | DIS_VISION | Character(1) | VARCHAR2(1) NOT NULL | Vision |
| 48 | DIS_VISION_D | Character(40) | VARCHAR2(40) NOT NULL | Vision |
| 49 | DIS_OTHER | Character(1) | VARCHAR2(1) NOT NULL | Other |
| 50 | DIS_OTHER_D | Character(40) | VARCHAR2(40) NOT NULL | Other |
| 51 | DIS_NONE | Character(1) | VARCHAR2(1) NOT NULL | None |
| 52 | DIS_NONE_D | Character(40) | VARCHAR2(40) NOT NULL | None |
| 53 | DIS_DONT_PROVIDE | Character(1) | VARCHAR2(1) NOT NULL | Do Not Wish to Provide |
| 54 | DIS_DONT_PROVIDE_D | Character(40) | VARCHAR2(40) NOT NULL | Do Not Wish to Provide |
| 55 | DIS_MOB_ORTH | Character(1) | VARCHAR2(1) NOT NULL | Mobility/Orthopedic Impairment |
| 56 | DIS_MOB_ORTH_D | Character(40) | VARCHAR2(40) NOT NULL | Mobility/Orthopedic Impairment |
| 57 | PROJECT_ROLE | Character(30) | VARCHAR2(30) NOT NULL | Project Role |
| 58 | CREDENTIAL | Character(60) | VARCHAR2(60) NOT NULL | Credentials |
| 59 | ETHNICITY | Character(1) | VARCHAR2(1) NOT NULL |
Ethnicity
1=Hispanic or Latino 2=Not Hispanic or Latino E=Do Not Wish To Provide |
| 60 | PROF_ROLE_TYPE | Character(4) | VARCHAR2(4) NOT NULL | Role Type |
| 61 | CITIZENSHIP_LNG | Character(30) | VARCHAR2(30) NOT NULL | CITIZENSHIP LONG DESCR |
| 62 | ETHNICITY_LNG | Character(30) | VARCHAR2(30) NOT NULL | ETHNICITY LONG DESCR |