GPBR_EVHIST_VW

(SQL View)
 Index Back

Employee Event List

Employee Event List

SELECT CASE WHEN A.GPBR_EVENT_CD = 'S-1200' OR A.GPBR_EVENT_CD = 'S-1210' THEN D.EMPLID WHEN A.GPBR_EVENT_CD = 'S-2500' THEN E.EMPLID WHEN A.GPBR_EVENT_CD = 'S-2501' THEN F.EMPLID ELSE B.EMPLID END AS EMPLID, CASE WHEN A.GPBR_EVENT_CD = 'S-1200' OR A.GPBR_EVENT_CD = 'S-1210' OR A.GPBR_EVENT_CD = 'S-2500' OR A.GPBR_EVENT_CD = 'S-2501' THEN 0 ELSE B.EMPL_RCD END AS EMPL_RCD, A.GPBR_DRIVER_SEQ, A.COMPANY, A.GPBR_EVENT_CD, A.GPBR_ES_PS, CASE WHEN A.GPBR_STATUS = 'CANC' THEN 'CANC' WHEN A.GPBR_STATUS = 'COMP' THEN 'SUCC' WHEN A.GPBR_STATUS = 'SMOV' THEN 'SUCC' WHEN A.GPBR_STATUS = 'ERRM' THEN 'ERRO' WHEN A.GPBR_STATUS = 'GERR' THEN 'RTER' WHEN A.GPBR_STATUS = 'GSUC' THEN 'SUCC' WHEN A.GPBR_STATUS = 'HOLD' THEN 'PEND' WHEN A.GPBR_STATUS = 'MAPP' THEN 'PROC' WHEN A.GPBR_STATUS = 'NEW' THEN 'PEND' WHEN A.GPBR_STATUS = 'RDY' THEN 'PEND' WHEN A.GPBR_STATUS = 'SUCC' THEN 'PROC' WHEN A.GPBR_STATUS = 'XCRT' THEN 'AWRE' WHEN A.GPBR_STATUS = 'XERR' THEN 'ERRO' WHEN A.GPBR_STATUS = 'XRDY' THEN 'PROC' ELSE ' ' END AS STATUS, CASE WHEN A.GPBR_EVENT_CD = 'S-1200' OR A.GPBR_EVENT_CD = 'S-1210' THEN CASE WHEN D.GPBR_PRE_DRV_SEQ > 0 THEN 'P' WHEN B_R2.GPBR_DRIVER_SEQ > 0 THEN 'R' WHEN B_D.GPBR_DRIVER_SEQ > 0 THEN 'D' ELSE 'O' END WHEN A.GPBR_EVENT_CD = 'S-2500' THEN CASE WHEN E.GPBR_PRE_DRV_SEQ > 0 THEN 'P' WHEN E_R.GPBR_DRIVER_SEQ > 0 THEN 'R' WHEN B_D.GPBR_DRIVER_SEQ > 0 THEN 'D' ELSE 'O' END WHEN A.GPBR_EVENT_CD = 'S-2501' THEN CASE WHEN F.GPBR_PRE_DRV_SEQ > 0 THEN 'P' WHEN F_R.GPBR_DRIVER_SEQ > 0 THEN 'R' WHEN B_D.GPBR_DRIVER_SEQ > 0 THEN 'D' ELSE 'O' END ELSE CASE WHEN B.GPBR_PRE_DRV_SEQ > 0 THEN 'P' WHEN B_R.GPBR_DRIVER_SEQ > 0 THEN 'R' WHEN B_D.GPBR_DRIVER_SEQ > 0 THEN 'D' ELSE 'O' END END AS DRIVER_TYPE, CASE WHEN A.GPBR_EVENT_CD = 'S-1200' OR A.GPBR_EVENT_CD = 'S-1210' THEN D.GPBR_PRE_DRV_SEQ WHEN A.GPBR_EVENT_CD = 'S-2500' THEN E.GPBR_PRE_DRV_SEQ WHEN A.GPBR_EVENT_CD = 'S-2501' THEN F.GPBR_PRE_DRV_SEQ ELSE B.GPBR_PRE_DRV_SEQ END AS GPBR_PRE_DRV_SEQ, A.GPBR_RECEIPT_NUM, A.GPBR_TIMESTAMP, CASE WHEN A.GPBR_EVENT_CD = 'S-1200' OR A.GPBR_EVENT_CD = 'S-1210' THEN D.GPBR_PAYROLL_TP ELSE ' ' END AS GPBR_PAYROLL_TP FROM PS_GPBR_DRIVER_DAT A LEFT OUTER JOIN PS_GPBR_DRVEMP_XRF B ON A.GPBR_DRIVER_SEQ = B.GPBR_DRIVER_SEQ LEFT OUTER JOIN PS_GPBR_DRVEMP_XRF B_R ON A.GPBR_DRIVER_SEQ = B_R.GPBR_PRE_DRV_SEQ LEFT OUTER JOIN PS_GPBR_DRIVER_DAT B_D ON A.GPBR_DRIVER_SEQ = B_D.GPBR_PRE_DRV_SEQ LEFT OUTER JOIN PS_GPBR_DRVCMP_XRF D ON A.GPBR_DRIVER_SEQ = D.GPBR_DRIVER_SEQ LEFT OUTER JOIN PS_GPBR_DRVCMP_XRF B_R2 ON A.GPBR_DRIVER_SEQ = B_R2.GPBR_PRE_DRV_SEQ LEFT OUTER JOIN PS_GPBR_EMP_LBR_VW E ON A.GPBR_DRIVER_SEQ = E.GPBR_DRIVER_SEQ LEFT OUTER JOIN PS_GPBR_EMP_LBR_VW E_R ON A.GPBR_DRIVER_SEQ = E_R.GPBR_PRE_DRV_SEQ LEFT OUTER JOIN PS_GPBR_EMP_LPT_VW F ON A.GPBR_DRIVER_SEQ = F.GPBR_DRIVER_SEQ LEFT OUTER JOIN PS_GPBR_EMP_LPT_VW F_R ON A.GPBR_DRIVER_SEQ = F_R.GPBR_PRE_DRV_SEQ, PS_GPBR_EVNT_SETUP C WHERE A.GPBR_EVENT_CD = C.GPBR_EVENT_CD AND C.GPBR_EVNT_CATEGORY IN ('N', 'P', 'T')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 GPBR_DRIVER_SEQ Number(30,0) DECIMAL(30) NOT NULL Driver Sequence
4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
5 GPBR_EVENT_CD Character(6) VARCHAR2(6) NOT NULL eSocial - Event Code
6 GPBR_ES_PS Date(10) DATE eSocial on PeopleSoft Date
7 GPBR_DRV_STATUS Character(4) VARCHAR2(4) NOT NULL Driver Status to show records by emplid historic event
ACRE=Action Required
AWRE=Awaiting Return
CANC=Canceled
ERRO=Errors
PEND=Pending
PROC=Process
RTER=Returned with Error
SUCC=Successfully Completed
8 GPBR_DRV_TYPE Character(1) VARCHAR2(1) NOT NULL Driver Type
D=Deleted
O=Original
P=Rectifier
R=Rectified
9 GPBR_PRE_DRV_SEQ Number(30,0) DECIMAL(30) NOT NULL eSocial Pre Driver Sequence
10 GPBR_RECEIPT_NUM Character(40) VARCHAR2(40) NOT NULL Receipt Num
11 GPBR_TIMESTAMP DateTime(26) TIMESTAMP Process Time Stamp for Mass Termination
12 GPBR_PAYROLL_TP Character(1) VARCHAR2(1) NOT NULL Payroll TP
0=N/A
1=Monthly
2=Annual