GPGB_SS_SRCH

(SQL View)
 Index Back

SS Payslip seach Record

Self Service Payslip Search Record

SELECT E.OPRID , C.CAL_RUN_ID , A.EMPL_RCD , C.GP_PAYGROUP , C.CAL_ID , C.RSLT_SEG_NUM , D.PYMT_DT , B.JOBCODE , B.DESCR , C.RSLT_VER_NUM , C.RSLT_REV_NUM FROM PS_JOB A , PS_JOBCODE_TBL B , PS_GP_PYE_SEG_STAT C , PS_GP_PYE_PRC_STAT D , PSOPRDEFN E WHERE C.EMPLID = D.EMPLID AND C.CAL_RUN_ID = D.CAL_RUN_ID AND C.EMPL_RCD=D.EMPL_RCD AND C.GP_PAYGROUP=D.GP_PAYGROUP AND C.CAL_ID=D.CAL_ID AND D.CALC_TYPE = 'P' AND C.RSLT_REV_NUM=1 AND C.RSLT_VER_NUM= ( SELECT MAX(RSLT_VER_NUM) FROM PS_GP_PYE_SEG_STAT C1 WHERE C1.EMPLID=C.EMPLID AND C1.CAL_RUN_ID=C.CAL_RUN_ID AND C1.EMPL_RCD=C.EMPL_RCD AND C1.GP_PAYGROUP=C.GP_PAYGROUP AND C1.CAL_ID=C.CAL_ID AND C1.RSLT_SEG_NUM=C.RSLT_SEG_NUM ) AND A.SETID_JOBCODE=B.SETID AND A.EMPLID = C.EMPLID AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND C.CALC_ACTION = 'C' AND (C.PYE_CALC_STAT = '60' OR C.PYE_CALC_STAT = '65' OR C.PYE_CALC_STAT = '70' OR C.PYE_CALC_STAT = '75') AND C.GP_PAYGROUP = A.GP_PAYGROUP AND ( A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= C.SEG_END_DT) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) ) AND A.JOBCODE = B.JOBCODE AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOBCODE_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.JOBCODE = B_ED.JOBCODE AND B_ED.EFFDT <= A.EFFDT AND B_ED.JOBCODE = A.JOBCODE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id

Prompt Table: GP_CAL_RUN4_VW

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

Prompt Table: GP_PYGRP5_VW

5 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id

Prompt Table: GP_CALENDAR9_VW

6 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
7 PAYMENT_DT Date(10) DATE Payment Date
8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
9 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
10 RSLT_VER_NUM Number(4,0) SMALLINT NOT NULL Result Version Number
11 RSLT_REV_NUM Number(4,0) SMALLINT NOT NULL Result Revision Number