JOBCD_WRK_VW

(SQL View)
 Index Back

Job Codes Worker View

This view is used to validate the Job Codes for RB_WORKER table. This view should be kept in sync. with the JOBCODE_TBL record except for the SETID field which is replaced by SETID_JOBCODE field. Selects only the current active rows.

SELECT A.SETID , A.JOBCODE , A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCRSHORT , A.JOB_FUNCTION , A.SETID_SALARY , A.SAL_ADMIN_PLAN , A.GRADE , A.STEP , A.MANAGER_LEVEL , A.SURVEY_SALARY , A.SURVEY_JOB_CODE , A.UNION_CD , A.RETRO_RATE , A.RETRO_PERCENT , A.CURRENCY_CD , A.STD_HOURS , A.STD_HRS_FREQUENCY , A.COMP_FREQUENCY , A.WORKERS_COMP_CD , A.JOB_FAMILY , A.JOB_KNOWHOW_POINTS , A.JOB_ACCNTAB_POINTS , A.JOB_PROBSLV_POINTS , A.JOB_POINTS_TOTAL , A.JOB_KNOWHOW_PCT , A.JOB_ACCNTAB_PCT , A.JOB_PROBSLV_PCT , A.REG_TEMP , A.DIRECTLY_TIPPED , A.MED_CHKUP_REQ , %subrec(JOBCD_USA_SBR,a) , %subrec(JOBCD_CAN_SBR,a) , %subrec(JOBCD_GER_SBR,a) , %subrec(JOBCD_FRA_SBR,a) , %subrec(JOBCD_UK_SBR,a) , %subrec(JOBCD_FED_SBR,a) , A.ENCUMBER_INDC , A.POSN_MGMT_INDC , A.EG_ACADEMIC_RANK , A.EG_GROUP , A.HP_STATS_DUTIES , A.HP_STATS_RPT_FLAG , A.ENCUMB_SAL_OPTN , A.ENCUMB_SAL_AMT , %subrec(JOBCD_ITA_SBR,a) , A.DESCRLONG FROM PS_JOBCODE_TBL A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_JOBCODE_TBL B WHERE B.SETID = A.SETID AND B.JOBCODE = A.JOBCODE AND B.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A'

  • Related Language Record: JOBCD_WRK_VW_LN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL SETID_JOBCODE field used in the HCM/ CRM sync Application Messages.

    Prompt Table: SP_SETID_NONVW

    2 JOBCODE Character(15) VARCHAR2(15) NOT NULL Job Code or Job Title.
    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    7 JOB_FUNCTION Character(3) VARCHAR2(3) NOT NULL Job Function Category
    8 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL SETID_SALARY field used in the HCM/ CRM sync Application Messages.
    9 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL SAL_ADMIN_PLAN field used in the HCM/ CRM sync Application Messages.
    10 GRADE Character(3) VARCHAR2(3) NOT NULL This is the salary grade field.
    11 STEP Number(2,0) SMALLINT NOT NULL STEP field is being use in one of the HCM/CRM Application Message.
    12 MANAGER_LEVEL Character(2) VARCHAR2(2) NOT NULL Manager Level.
    0=Chief Operating Officer
    1=Director
    2=Senior Officer
    3=Vice President
    4=Senior Manager
    5=Mid-Level Manager
    6=First-Line Manager
    7=Supervisor
    8=All Other Positions
    9=Non-Manager

    Default Value: 8

    13 SURVEY_SALARY Number(8,0) INTEGER NOT NULL SURVEY_SALARY field used in the HCM/ CRM sync Application Messages.
    14 SURVEY_JOB_CODE Character(8) VARCHAR2(8) NOT NULL SURVEY_JOB_CODE field used in the HCM/ CRM sync Application Messages.
    15 UNION_CD Character(3) VARCHAR2(3) NOT NULL UNION_CD field used in the HCM/ CRM sync Application Messages.
    16 RETRO_RATE Signed Number(8,4) DECIMAL(6,4) NOT NULL RETRO_RATE field used in the HCM/ CRM sync Application Messages.
    17 RETRO_PERCENT Signed Number(8,4) DECIMAL(6,4) NOT NULL RETRO_PERCENT field used in the HCM/ CRM sync Application Messages.
    18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.

    Prompt Table: CURRENCY_CD_TBL

    19 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours field used in HD_360_RESPONSE_SYNC message.
    20 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL STD_HRS_FREQUENCY field used in the HCM/ CRM sync Application Messages.
    21 COMP_FREQUENCY Character(1) VARCHAR2(1) NOT NULL Compensation Frequency
    A=Annual
    B=Biweekly
    C=Contract
    D=Daily
    H=Hourly
    M=Monthly
    S=Semimonthly
    W=Weekly
    22 WORKERS_COMP_CD Character(4) VARCHAR2(4) NOT NULL WORKERS_COM_CD field used in JOBCODE and Jobcode HCM/ CRM sync Application Messages.
    23 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    24 JOB_KNOWHOW_POINTS Number(5,0) INTEGER NOT NULL Job Knowledge Points
    25 JOB_ACCNTAB_POINTS Number(5,0) INTEGER NOT NULL Job Accountablity Points
    26 JOB_PROBSLV_POINTS Number(5,0) INTEGER NOT NULL Problem Solving Points
    27 JOB_POINTS_TOTAL Number(5,0) INTEGER NOT NULL Job Total Points
    28 JOB_KNOWHOW_PCT Number(5,1) DECIMAL(4,1) NOT NULL Job Knowledge Percentage
    29 JOB_ACCNTAB_PCT Number(5,1) DECIMAL(4,1) NOT NULL Job Accountability Percentage
    30 JOB_PROBSLV_PCT Number(5,1) DECIMAL(4,1) NOT NULL Problem Solving Percentage
    31 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Identifies whether the worker is a Regular employee or a Temporary employee.
    R=Regular
    T=Temporary
    32 DIRECTLY_TIPPED Character(1) VARCHAR2(1) NOT NULL This is the direct indication of the tip field.
    D=Directly Tipped
    I=Indirectly Tipped
    N=Not Tipped

    Y/N Table Edit

    Default Value: N

    33 MED_CHKUP_REQ Character(1) VARCHAR2(1) NOT NULL Medical Checkup Required Indicator

    Y/N Table Edit

    Default Value: N

    34 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status
    A=Administrative
    E=Executive
    M=Management
    N=Nonexempt
    O=Outside Salesperson
    P=Professional
    X=No FLSA Required

    Default Value: N

    35 EEO1CODE Character(1) VARCHAR2(1) NOT NULL EEO-1 Job Category

    Default Value: N

    36 EEO4CODE Character(1) VARCHAR2(1) NOT NULL EEO-4 Job Category

    Default Value: N

    37 EEO5CODE Character(2) VARCHAR2(2) NOT NULL EEO-5 Job Category

    Default Value: N

    38 EEO6CODE Character(1) VARCHAR2(1) NOT NULL EEO-6 Job Category

    Default Value: N

    39 EEO_JOB_GROUP Character(4) VARCHAR2(4) NOT NULL EEO Job Group
    40 US_SOC_CD Character(4) VARCHAR2(4) NOT NULL Standard Occupational Classif
    41 IPEDSSCODE Character(1) VARCHAR2(1) NOT NULL IPEDS-S Job Category

    Default Value: N

    42 CAN_PAYEQ_JOB_CLS Character(3) VARCHAR2(3) NOT NULL Canadian Pay Equity Job Class
    43 CAN_NOC_CD Character(4) VARCHAR2(4) NOT NULL National Occupational Classif
    44 SEASONAL Character(1) VARCHAR2(1) NOT NULL Seasonal

    Y/N Table Edit

    Default Value: N

    45 BPS_ACTIVITY_CD Character(6) VARCHAR2(6) NOT NULL BPS Activity
    46 FUNCTION_CD Character(2) VARCHAR2(2) NOT NULL Function Code
    47 BA_CD Character(3) VARCHAR2(3) NOT NULL BA Code
    48 TECHNICAL Character(1) VARCHAR2(1) NOT NULL Technical

    Y/N Table Edit

    49 MED_SURV_REQ Character(1) VARCHAR2(1) NOT NULL Medical Surveillance Required

    Y/N Table Edit

    Default Value: N

    50 EMP_CATEGRY_FRA Character(3) VARCHAR2(3) NOT NULL Employee Category
    51 ACTIVITY_TYPE_FRA Character(3) VARCHAR2(3) NOT NULL Activity Type
    52 INSEE_CD_FRA Character(4) VARCHAR2(4) NOT NULL INSEE Code
    53 UK_SOC_CD Character(2) VARCHAR2(2) NOT NULL Standard Occupational Classif
    54 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan
    55 GVT_OCC_SERIES Character(4) VARCHAR2(4) NOT NULL Occupational Series
    56 TRN_PROGRAM Character(6) VARCHAR2(6) NOT NULL Training Program
    57 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    58 GVT_FUNC_CLASS Character(2) VARCHAR2(2) NOT NULL Functional Class

    Default Value: 00

    59 GVT_POSN_TITLE_CD Character(4) VARCHAR2(4) NOT NULL Official Posn Title Code
    60 GVT_OFFICIAL_DESCR Character(70) VARCHAR2(70) NOT NULL Official Position Title
    61 GVT_ORG_TTL_CD Character(4) VARCHAR2(4) NOT NULL Organization Posn Title Cd
    62 GVT_ORG_TTL_DESCR Character(70) VARCHAR2(70) NOT NULL Organization Position Title
    63 GVT_PAY_BASIS Character(2) VARCHAR2(2) NOT NULL Pay Basis
    64 GVT_SUB_AGENCY Character(2) VARCHAR2(2) NOT NULL Sub-Agency
    65 GVT_POI Character(4) VARCHAR2(4) NOT NULL Personnel Office ID
    66 GVT_PAREN_TITLE Character(1) VARCHAR2(1) NOT NULL Parenthetical Title
    D=(Data Transcribing)
    O=(Office Automation)
    S=(Stenography)
    T=(Typing)
    67 GVT_OPM_CERT_NBR Character(8) VARCHAR2(8) NOT NULL OPM Certification Number
    68 GVT_POSN_CLASS_STD Character(254) VARCHAR2(254) NOT NULL Position Classification Stds
    69 GVT_POSN_SENS_CD Character(1) VARCHAR2(1) NOT NULL Sensitivity Code
    1=Non Sensitive
    2=Non-Critical, Sensitive
    3=Critical Sensitive
    4=Special Sensitive

    Default Value: 1

    70 GVT_IA_ACTIONS Character(1) VARCHAR2(1) NOT NULL IA Actions

    Y/N Table Edit

    Default Value: N

    71 GVT_EXEC_FIN_DISCL Character(1) VARCHAR2(1) NOT NULL Executive Financial Disclosure

    Y/N Table Edit

    Default Value: N

    72 GVT_EMP_FIN_INT Character(1) VARCHAR2(1) NOT NULL Employee Financial Interests

    Y/N Table Edit

    Default Value: N

    73 GVT_PATCOB_CD Character(1) VARCHAR2(1) NOT NULL PATCOB Code
    74 GVT_CLS_STANDARD Character(15) VARCHAR2(15) NOT NULL Classification Standard
    75 GVT_CLASSIFIER_ID Character(11) VARCHAR2(11) NOT NULL Classifier's ID
    76 GVT_DT_CLASSIFIED Date(10) DATE Date Classified

    Default Value: %date

    77 BARG_UNIT Character(4) VARCHAR2(4) NOT NULL Bargaining Unit
    78 GVT_FUND_SOURCE Character(1) VARCHAR2(1) NOT NULL Fund Source
    A=Appropriated Funds
    E=External Funds
    N=Non-Appropriated Funds
    O=Other Funds
    79 GVT_LEO_POSITION Character(1) VARCHAR2(1) NOT NULL LEO/Fire Position
    5=5USC 5305 LEO
    C=D.C. Police Forces
    F=Fire Fighter
    N=Not Applicable
    P=Primary FEPCA
    S=Secondary FEPCA
    80 GVT_PERF_PLAN Character(8) VARCHAR2(8) NOT NULL Performance Plan
    81 GVT_TARGET_GRADE Character(2) VARCHAR2(2) NOT NULL Target Grade
    82 GVT_PI_UPD_IND Character(1) VARCHAR2(1) NOT NULL pi upd ind
    N=Not Ready to Process
    P=Processed
    R=Ready To Process
    83 ENCUMBER_INDC Character(1) VARCHAR2(1) NOT NULL This is the encumberence indicator field.
    I=Encumber Immediately
    N=No Encumbrance
    R=Encumber on Requisition

    Y/N Table Edit

    Default Value: N

    84 POSN_MGMT_INDC Character(1) VARCHAR2(1) NOT NULL Used by Position Mgmt?

    Y/N Table Edit

    Default Value: Y

    85 EG_ACADEMIC_RANK Character(3) VARCHAR2(3) NOT NULL Academic Rank
    86 EG_GROUP Character(6) VARCHAR2(6) NOT NULL Service Calculation Group
    87 HP_STATS_DUTIES Character(2) VARCHAR2(2) NOT NULL This is the Stats-Can Duties field.
    10=Teaching Only
    20=Teaching & Othr Responsibility
    30=Research
    88 HP_STATS_RPT_FLAG Character(1) VARCHAR2(1) NOT NULL This is the Stats-Can Reports flag field.
    B=Both Surveys
    F=Full-Time Survey
    N=Not Applicable
    P=Part-Time Survey

    Default Value: N

    89 ENCUMB_SAL_OPTN Character(3) VARCHAR2(3) NOT NULL Encumber Salary Option
    COM=Sum of Default Components
    MAX=Salary Grade Maximum
    MID=Salary Grade Midpoint
    MIN=Salary Grade Minimum
    STP=Salary Step
    USR=User Specified Amount

    Default Value: STP

    90 ENCUMB_SAL_AMT Signed Number(20,3) DECIMAL(18,3) NOT NULL Encumber Salary Amount
    91 INAIL_CODE Character(11) VARCHAR2(11) NOT NULL INAIL Code
    92 DESCRLONG Long Character CLOB Long Description