FO_CAN_JOIN_VW(SQL View) |
Index Back |
|---|---|
Candidate Summary ViewStaffing Front Office Recruiting Funnel Candidate Summary View: (1) select in progress events if there is none, then (2) select outstanding events if there is none, then (3) select completed events |
| SELECT A.SO_ID , A.SO_LINE , A.CANDIDATE_ID , B.EVENT_TYPE , B.FO_SUBEVENT , B.FO_EVENT_STAT FROM PS_FO_CN_IT_FUN_VW A , PS_FO_RF_ENTRY B WHERE B.HISTORY_ID = A.HISTORY_ID UNION SELECT X.SO_ID , X.SO_LINE , X.CANDIDATE_ID , C.EVENT_TYPE , C.FO_SUBEVENT , C.FO_EVENT_STAT FROM PS_FO_CN_OT_FUN_VW X , PS_FO_RF_ENTRY C WHERE C.HISTORY_ID = X.HISTORY_ID AND NOT EXISTS ( SELECT A.SO_ID , A.SO_LINE , A.CANDIDATE_ID , B.EVENT_TYPE , B.FO_SUBEVENT , B.FO_EVENT_STAT FROM PS_FO_CN_IT_FUN_VW A , PS_FO_RF_ENTRY B WHERE B.HISTORY_ID = A.HISTORY_ID AND A.SO_ID = X.SO_ID AND A.SO_LINE = X.SO_LINE AND A.CANDIDATE_ID = X.CANDIDATE_ID ) UNION SELECT K.SO_ID , K.SO_LINE , K.CANDIDATE_ID , J.EVENT_TYPE , J.FO_SUBEVENT , J.FO_EVENT_STAT FROM PS_FO_CN_PT_FUN_VW K , PS_FO_RF_ENTRY J WHERE J.HISTORY_ID = K.HISTORY_ID AND NOT EXISTS ( SELECT A.SO_ID , A.SO_LINE , A.CANDIDATE_ID , B.EVENT_TYPE , B.FO_SUBEVENT , B.FO_EVENT_STAT FROM PS_FO_CN_IT_FUN_VW A , PS_FO_RF_ENTRY B WHERE B.HISTORY_ID = A.HISTORY_ID AND A.SO_ID = K.SO_ID AND A.SO_LINE = K.SO_LINE AND A.CANDIDATE_ID = K.CANDIDATE_ID ) AND NOT EXISTS ( SELECT M.SO_ID , M.SO_LINE , M.CANDIDATE_ID , N.EVENT_TYPE , N.FO_SUBEVENT , N.FO_EVENT_STAT FROM PS_FO_CN_OT_FUN_VW M , PS_FO_RF_ENTRY N WHERE N.HISTORY_ID = M.HISTORY_ID AND M.SO_ID = K.SO_ID AND M.SO_LINE = K.SO_LINE AND M.CANDIDATE_ID = K.CANDIDATE_ID ) UNION SELECT Q.SO_ID , Q.SO_LINE , Q.CANDIDATE_ID , W.EVENT_TYPE , W.FO_SUBEVENT , W.FO_EVENT_STAT FROM PS_FO_CN_CT_FUN_VW Q , PS_FO_RF_ENTRY W WHERE W.HISTORY_ID = Q.HISTORY_ID AND NOT EXISTS ( SELECT A.SO_ID , A.SO_LINE , A.CANDIDATE_ID , B.EVENT_TYPE , B.FO_SUBEVENT , B.FO_EVENT_STAT FROM PS_FO_CN_IT_FUN_VW A , PS_FO_RF_ENTRY B WHERE B.HISTORY_ID = A.HISTORY_ID AND A.SO_ID = Q.SO_ID AND A.SO_LINE = Q.SO_LINE AND A.CANDIDATE_ID = Q.CANDIDATE_ID ) AND NOT EXISTS ( SELECT M.SO_ID , M.SO_LINE , M.CANDIDATE_ID , N.EVENT_TYPE , N.FO_SUBEVENT , N.FO_EVENT_STAT FROM PS_FO_CN_OT_FUN_VW M , PS_FO_RF_ENTRY N WHERE N.HISTORY_ID = M.HISTORY_ID AND M.SO_ID = Q.SO_ID AND M.SO_LINE = Q.SO_LINE AND M.CANDIDATE_ID = Q.CANDIDATE_ID ) AND NOT EXISTS ( SELECT U.SO_ID , U.SO_LINE , U.CANDIDATE_ID , V.EVENT_TYPE , V.FO_SUBEVENT , V.FO_EVENT_STAT FROM PS_FO_CN_PT_FUN_VW U , PS_FO_RF_ENTRY V WHERE V.HISTORY_ID = U.HISTORY_ID AND U.SO_ID = Q.SO_ID AND U.SO_LINE = Q.SO_LINE AND U.CANDIDATE_ID = Q.CANDIDATE_ID ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SO_ID | Character(15) | VARCHAR2(15) NOT NULL | Service Order ID |
| 2 | SO_LINE | Number(3,0) | SMALLINT NOT NULL | Line Number |
| 3 | CANDIDATE_ID | Character(11) | VARCHAR2(11) NOT NULL | Used as a general ID for anything from Employee id to other types of ID like asset and material. |
| 4 | EVENT_TYPE | Character(10) | VARCHAR2(10) NOT NULL | Event Type |
| 5 | FO_SUBEVENT | Character(10) | VARCHAR2(10) NOT NULL | Staffing Front Office Recruiting Funnel SubEvent |
| 6 | FO_EVENT_STAT | Character(1) | VARCHAR2(1) NOT NULL |
Staffing Front Office Recruiting Funnel Event Status
C=Completed I=In Progress O=Pending P=Partially complete |