HRS_JOSCRLVL_VW(SQL View) |
Index Back |
|---|
| SELECT E.HRS_JOB_OPENING_ID , B.JOBCODE , C.HRS_JO_SCR_SUMM_ID , C.HRS_JO_SCR_SEQ , C.HRS_SCREENING_ID , E.DESCR AS JOBCODE_DESCR , E.HRS_JO_PRM_JCD_FLG , D.DESCR AS SCREENING_DESCR , (%NumToChar(C.HRS_JO_SCR_SEQ) %Concat '-' %Concat D.DESCR) AS SCR_LABEL , COUNT(*) AS TOTAL_SCREENED , SUM(CASE WHEN rslt.hrs_scr_status = '1' THEN 1 ELSE 0 END) AS PASSED_SCREENING , SUM(CASE WHEN rslt.hrs_scr_status = '2' THEN 1 ELSE 0 END)AS FAILED_SCREENING , SUM(CASE WHEN rslt.hrs_rslt_applied <> 'Y' THEN 1 ELSE 0 END) AS UNAPPLIED_RESULT FROM PS_HRS_JO_SCR_SM_I B , PS_HRS_JO_SCR_LV_I C , PS_HRS_SCREEN_TBL D , ps_hrs_jo_jobcd_i E , PS_HRS_RCM_SCR2_VW rslt WHERE B.HRS_JO_SCR_SUMM_ID = C.HRS_JO_SCR_SUMM_ID AND C.HRS_SCREENING_ID = D.HRS_SCREENING_ID AND B.HRS_JOB_OPENING_ID = E.HRS_JOB_OPENING_ID AND B.JOBCODE = E.JOBCODE AND E.HRS_JOB_OPENING_ID = RSLT.HRS_JOB_OPENING_ID AND C.HRS_SCREENING_ID = RSLT.HRS_SCREENING_ID AND B.JOBCODE = RSLT.JOBCODE GROUP BY E.HRS_JOB_OPENING_ID, B.JOBCODE, E.DESCR, E.hrs_jo_prm_jcd_flg, C.HRS_JO_SCR_SUMM_ID, C.HRS_JO_SCR_SEQ, C.HRS_SCREENING_ID,D.DESCR, (%NumToChar(C.HRS_JO_SCR_SEQ) %Concat '-' %Concat D.DESCR) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | HRS_JOB_OPENING_ID | Number(15,0) | DECIMAL(15) NOT NULL | Job Opening Clone |
| 2 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 3 | HRS_JO_SCR_SUMM_ID | Number(15,0) | DECIMAL(15) NOT NULL | JO Screening Summary ID |
| 4 | HRS_JO_SCR_SEQ | Number(3,0) | SMALLINT NOT NULL | Job opening screening sequence |
| 5 | HRS_SCREENING_ID | Number(15,0) | DECIMAL(15) NOT NULL | Screening ID |
| 6 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 7 | HRS_JO_PRM_JCD_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Primary job code for job opening
N=No Y=Yes |
| 8 | HRS_SCR_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Screening Level |
| 9 | HRS_JO_SCR_LVL_NM | Character(50) | VARCHAR2(50) NOT NULL | Screening Level |
| 10 | HRS_JO_SCR_TOTAL | Number(8,0) | INTEGER NOT NULL | Applicants Screened |
| 11 | HRS_JO_SCR_PASSED | Number(8,0) | INTEGER NOT NULL | Applicants Passed |
| 12 | HRS_JO_SCR_FAILED | Number(8,0) | INTEGER NOT NULL | Applicants Failed |
| 13 | HRS_JO_SCR_UNAPPL | Number(8,0) | INTEGER NOT NULL | Unapplied Results |