GM_XML_PI

(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 , CASE G.ETHNIC_GROUP WHEN 'E' THEN '' ELSE G.ETHNIC_GROUP END , 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 , 'PD/PI' , B.CREDENTIAL , CASE G.ETHNIC_GROUP WHEN 'E' THEN '' ELSE G.ETHNIC_GROUP END , 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 , %Substring(N.DESCR120, 1, 60) , G.US_GOVT_EMP_IND , CASE G.US_GOVT_EMP_IND WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , G.AMOUNT_REQUEST , CASE G.FORN_ORG_EMP_IND WHEN 'Y' THEN 'Y: Yes' ELSE 'N: No' END , G.USA_AGY_NAME , G.USA_GOV_CD , 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' , B.DESCR30 , G.DIS_MOB_ORTH , 'Mobility/Orthopedic Impairment' FROM PS_GM_XML_PROP_TBL A , PS_GM_EC_PERS_PI B , PS_GM_PERS_ADDR_VW F , PS_GM_EC_RACE H , PS_GM_EC_INST N , PS_GM_EC_PROJ_PI_2 G LEFT OUTER JOIN PS_GM_ETHNICITY_VW M ON M.FIELDVALUE = G.ETHNIC_GROUP 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 G.PROF_ROLE_TYPE = 'PI' AND A.OPRID = N.OPRID AND A.RUN_CNTL_ID = N.RUN_CNTL_ID AND B.ECTRANSID = N.ECTRANSID AND B.ECQUEUEINSTANCE = N.ECQUEUEINSTANCE AND B.ECTRANSINOUTSW = N.ECTRANSINOUTSW

  • Parent record: GM_XML_PROP_TBL

    • # 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 PROJECT_ROLE Character(30) VARCHAR2(30) NOT NULL Project Role
    46 CREDENTIAL Character(60) VARCHAR2(60) NOT NULL Credentials
    47 ETHNICITY Character(1) VARCHAR2(1) NOT NULL Ethnicity
    1=Hispanic or Latino
    2=Not Hispanic or Latino
    E=Do Not Wish To Provide
    48 PROF_ROLE_TYPE Character(4) VARCHAR2(4) NOT NULL Role Type
    49 CITIZENSHIP_LNG Character(30) VARCHAR2(30) NOT NULL CITIZENSHIP LONG DESCR
    50 ETHNICITY_LNG Character(30) VARCHAR2(30) NOT NULL ETHNICITY LONG DESCR
    51 ORG_LEG_NAME Character(60) VARCHAR2(60) NOT NULL Organization Legal Name
    52 US_GOVT_EMP_IND Character(1) VARCHAR2(1) NOT NULL US Government Employee
    53 US_GOVT_EMP_IND_D Character(18) VARCHAR2(18) NOT NULL US Government Employee
    54 AMOUNT_REQUEST Number(27,3) DECIMAL(26,3) NOT NULL Amount Requested
    55 FORN_ORG_EMP_IND_D Character(18) VARCHAR2(18) NOT NULL Foreign Organization Employee
    56 USA_AGY_NAME Character(80) VARCHAR2(80) NOT NULL US Government Agency Name
    57 USA_GOV_CD Character(4) VARCHAR2(4) NOT NULL US Government Agency
    58 DIS_HEARING Character(1) VARCHAR2(1) NOT NULL Hearing
    59 DIS_HEARING_D Character(40) VARCHAR2(40) NOT NULL Hearing
    60 DIS_VISION Character(1) VARCHAR2(1) NOT NULL Vision
    61 DIS_VISION_D Character(40) VARCHAR2(40) NOT NULL Vision
    62 DIS_OTHER Character(1) VARCHAR2(1) NOT NULL Other
    63 DIS_OTHER_D Character(40) VARCHAR2(40) NOT NULL Other
    64 DIS_NONE Character(1) VARCHAR2(1) NOT NULL None
    65 DIS_NONE_D Character(40) VARCHAR2(40) NOT NULL None
    66 DIS_DONT_PROVIDE Character(1) VARCHAR2(1) NOT NULL Do Not Wish to Provide
    67 DIS_DONT_PROVIDE_D Character(40) VARCHAR2(40) NOT NULL Do Not Wish to Provide
    68 DESCR30 Character(30) VARCHAR2(30) NOT NULL Description
    69 DIS_MOB_ORTH Character(1) VARCHAR2(1) NOT NULL Mobility/Orthopedic Impairment
    70 DIS_MOB_ORTH_D Character(40) VARCHAR2(40) NOT NULL Mobility/Orthopedic Impairment