ACCOMPLISHMENTS

(SQL View)
 Index Back

Person Accomplishments

ACCOMPLISHMENTS contains the degrees, tests, languages, honors/awards, licenses/certifications, and professional memberships of your employees, applicants and contractors. Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from Human Resources to JPM.

SELECT B.EMPLID ,%Substring(A.JPM_CAT_ITEM_ID,1,8) ,A.EFFDT ,CASE WHEN A.JPM_CAT_TYPE = 'DEG' THEN %Substring(A.JPM_CAT_ITEM_QUAL2,1,10) ELSE ' ' END ,A.JPM_DECIMAL_1 ,A.JPM_YN_1 ,%Substring(A.JPM_TEXT254_2,1,15) ,%Substring(A.JPM_TEXT254_4,1,30) ,A.COUNTRY ,A.STATE ,A.JPM_DATE_3 ,A.JPM_YN_3 ,A.JPM_YN_2 ,CASE WHEN A.JPM_CAT_TYPE = 'LNG' THEN A.JPM_YN_1 ELSE 'N' END ,A.JPM_YN_2 ,A.JPM_YN_3 ,A.JPM_RATING2 ,A.JPM_RATING1 ,A.JPM_RATING3 ,CASE WHEN A.JPM_CAT_TYPE = 'LNG' THEN A.EFFDT ELSE %DateNull END ,%Substring(A.JPM_TEXT254_2,1,15) ,%Substring(A.JPM_TEXT254_3,1,15) ,A.JPM_DATE_2 ,A.JPM_DATE_3 ,%Substring(A.MAJOR_DESCR,1,30) ,A.IPE_SW ,A.AVERAGE_GRADE ,%Substring(A.JPM_TEXT254_4,1,50) ,A.JPM_YN_1 ,A.COUNTRY ,A.STATE ,A.SCHOOL_CODE ,%Substring(A.SCHOOL_DESCR,1,30) ,A.JPM_YN_3 ,A.PRACTIC_GRADE_GER ,A.THEORY_GRADE_GER ,%Substring(A.JPM_TEXT254_4,1,20) ,A.JPM_DATE_3 ,A.BONUS_DT_FRA ,A.BONUS_AMOUNT_FRA ,A.GVT_CREDIT_HOURS ,A.GVT_CRED_HRS_TYPE ,A.JPM_DECIMAL_1 ,A.JPM_INTEGER_2 ,A.EDUC_LVL_AUS ,A.APS_HEDUC_CD_AUS ,A.JPM_MINOR_CD ,%Substring(A.MINOR_DESCR,1,30) ,A.NVQ_STATUS ,A.JPM_DATE_2 ,A.JPM_DATE_3 ,A.JPM_DATE_4 ,%Substring(A.JPM_TEXT254_2,1,16) ,A.JPM_DATE_5 ,A.FP_SUBJECT_CD ,A.FACULTY_CODE ,%Substring(A.FACULTY_DESCR,1,30) ,A.SUBFACULTY_CODE ,%Substring(A.SUBFACULTY_DESCR,1,50) ,A.MAJOR_CATEGORY FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND B.JPM_JP_TYPE = ( SELECT JPM_PRI_PERS_TYPE FROM PS_JPM_JP_PRI_TYPE) AND A.JPM_CAT_TYPE IN('DEG' ,'HON' ,'LIC' ,'LNG' ,'MEM' ,'TST' ,'NVQ')

  • 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: PERSON

    2 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment

    Prompt Table: %RECNAME_EDIT

    3 DT_ISSUED Date(10) DATE Issue Date
    4 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code

    Prompt Table: MAJOR_TBL

    5 SCORE Number(7,2) DECIMAL(6,2) NOT NULL Test Score
    6 PASSED Character(1) VARCHAR2(1) NOT NULL Passed Test
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    7 LICENSE_NBR Character(15) VARCHAR2(15) NOT NULL License/Certification Number
    8 ISSUED_BY Character(30) VARCHAR2(30) NOT NULL Issued By
    9 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Default Value: OPR_DEF_TBL_HR.COUNTRY

    Prompt Table: COUNTRY_TBL

    10 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: HCR_STONLY_I

    11 EXPIRATN_DT Date(10) DATE Expiration Date
    12 LICENSE_VERIFIED Character(1) VARCHAR2(1) NOT NULL License Verified

    Y/N Table Edit

    Default Value: N

    13 RENEWAL Character(1) VARCHAR2(1) NOT NULL Renewal in Progress

    Y/N Table Edit

    Default Value: N

    14 NATIVE_LANGUAGE Character(1) VARCHAR2(1) NOT NULL Native Language

    Y/N Table Edit

    Default Value: N

    15 TRANSLATOR Character(1) VARCHAR2(1) NOT NULL Able to Translate

    Y/N Table Edit

    Default Value: N

    16 TEACHER Character(1) VARCHAR2(1) NOT NULL Able to Teach

    Y/N Table Edit

    Default Value: N

    17 SPEAK_PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Speaking Proficiency
    1=Low
    2=Moderate
    3=High
    18 READ_PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Reading Proficiency
    1=Low
    2=Moderate
    3=High
    19 WRITE_PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Writing Proficiency
    1=Low
    2=Moderate
    3=High
    20 EVALUATION_DT Date(10) DATE Evaluation Date
    21 MANDATE Character(15) VARCHAR2(15) NOT NULL Mandate
    22 MANDATE_FUNCTION Character(15) VARCHAR2(15) NOT NULL Mandate Position
    23 MANDATE_BEGIN_DATE Date(10) DATE Mandate Begin Date
    24 MANDATE_END_DATE Date(10) DATE Mandate End Date
    25 MAJOR Character(30) VARCHAR2(30) NOT NULL Major
    26 IPE_SW Character(1) VARCHAR2(1) NOT NULL Intern Professional Education

    Y/N Table Edit

    Default Value: N

    27 AVERAGE_GRADE Character(5) VARCHAR2(5) NOT NULL Average Grade
    28 EDUCATOR Character(50) VARCHAR2(50) NOT NULL Educator
    29 GRADUATE_INDICATOR Character(1) VARCHAR2(1) NOT NULL Graduated
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    30 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

    Default Value: OPR_DEF_TBL_HR.COUNTRY

    Prompt Table: COUNTRY_TBL

    31 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

    Prompt Table: HCR_STONLY_OT_I

    32 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code

    Prompt Table: SCHOOL_TBL_VW

    33 SCHOOL Character(30) VARCHAR2(30) NOT NULL School Name
    34 TERMINAL_DEGREE Character(1) VARCHAR2(1) NOT NULL Terminal Degree for Discipline

    Y/N Table Edit

    Default Value: N

    35 PRACTIC_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Track Practical Grade applicable in Germany.
    36 THEORY_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Track Theoretical Grade applicable in Germany.
    37 GRANTOR Character(20) VARCHAR2(20) NOT NULL The name of the person granting the award
    38 END_DATE Date(10) DATE End Date
    39 BONUS_DT_FRA Date(10) DATE Bonus Date
    40 BONUS_AMOUNT_FRA Number(8,2) DECIMAL(7,2) NOT NULL Bonus Amount
    41 GVT_CREDIT_HOURS Character(3) VARCHAR2(3) NOT NULL Education Credit Hours
    42 GVT_CRED_HRS_TYPE Character(1) VARCHAR2(1) NOT NULL Type of education credit hours
    C=Continuing Education
    O=Other Pay
    Q=Quarter Hours
    S=Semester Hours
    43 GPA Number(5,2) DECIMAL(4,2) NOT NULL GPA
    44 YR_ACQUIRED Number(4,0) SMALLINT NOT NULL Year Acquired
    45 EDUC_LVL_AUS Character(1) VARCHAR2(1) NOT NULL This field refers to the highest education attained by the member of staff. This is an Australia specific code which is needed for DETYA electronic reporting purposes.
    0=Non-Academic
    1=Higher Degree
    2=Bachelor's Degree
    3=Other
    4=No Info
    46 APS_HEDUC_CD_AUS Character(2) VARCHAR2(2) NOT NULL The APS maintains a list of education codes that should be used for reporting purposes. Users should select the APS code to an existing education code in the system.
    01=Doctorate
    02=Masters
    03=Postgraduate Diploma
    04=Bachelor Degree
    05=Undergraduate Diploma
    06=Associate Diploma
    07=Skilled Vocational Qualificatn
    08=Basic Vocational Qualification
    09=Year 12 (Higher School Cert)
    11=Year 11
    12=Year 10 (Leaving/School Cert)
    13=Less than Year 10
    97=Chose not to Give

    Default Value: 97

    47 APS_MINOR_CODE_AUS Character(10) VARCHAR2(10) NOT NULL Employee undertake multiple qualifications that can be classified as Major and Minor qualification. Minor code identifies the minor qualification undertaken by an employee.

    Prompt Table: MAJOR_TBL

    48 APS_MINOR_AUS Character(30) VARCHAR2(30) NOT NULL This field holds the description of the minor degree attained by an individual.
    49 NVQ_STATUS Character(1) VARCHAR2(1) NOT NULL The status of the employee's NVQ
    A=Achieved
    C=Cancelled
    E=Enrolled
    P=Previous Achievement
    R=Reviewed
    50 NVQ_START_DT Date(10) DATE NVQ Start Date
    51 NVQ_COMP_DT Date(10) DATE NVQ Completion Date
    52 NVQ_CERT_DT Date(10) DATE Certificate Award Date
    53 NVQ_CAND_NBR Character(16) VARCHAR2(16) NOT NULL Registration Number
    54 NVQ_CAND_DT Date(10) DATE Registration Date
    55 FP_SUBJECT_CD Character(3) VARCHAR2(3) NOT NULL FPS Subject Code

    Prompt Table: FPMSUBJECTS

    56 FACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Faculty Code (Japan HR)

    Prompt Table: FACULTY_TBL_JPN

    57 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    58 SUBFACULTY_CODE Character(10) VARCHAR2(10) NOT NULL Sub Faculty Code(Japan HR)

    Prompt Table: SBFCLTY_TBL_JPN

    59 SUBFACULTY_NAME Character(50) VARCHAR2(50) NOT NULL Sub Faculty Name(Description)(Japan HR)
    60 MAJOR_CATEGORY Character(1) VARCHAR2(1) NOT NULL Major Category(Japan HR)
    H=Humanities
    S=Sciences