GPGB_PU_SRCH

(SQL View)
 Index Back

PU Payslip Search Record

Payroll Users Payslip Seach Record

SELECT %Sql(SCRTY_SEL_PKEY,SEC,SEC) , C.CAL_RUN_ID , C.GP_PAYGROUP , C.CAL_ID , C.RSLT_SEG_NUM , C.RSLT_VER_NUM , C.RSLT_REV_NUM , D.PYMT_DT , SEC.NAME , B.DESCR FROM PS_JOB A , PS_JOBCODE_TBL B , PS_GP_PYE_SEG_STAT C , PS_GP_PYE_PRC_STAT D , PS_EMPLMT_SRCH_GBL SEC WHERE SEC.EMPLID = C.EMPLID AND SEC.EMPL_RCD = C.EMPL_RCD AND 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 C_1 WHERE C.EMPLID=C_1.EMPLID AND C.CAL_RUN_ID=C_1.CAL_RUN_ID AND C_1.EMPL_RCD=C.EMPL_RCD AND C_1.GP_PAYGROUP=C.GP_PAYGROUP AND C.CAL_ID=C_1.CAL_ID AND C.RSLT_SEG_NUM=C_1.RSLT_SEG_NUM) AND A.SETID_JOBCODE=B.SETID AND C.CALC_ACTION = 'C' AND A.EMPLID = C.EMPLID AND A.EMPLID = SEC.EMPLID AND A.EMPL_RCD = C.EMPL_RCD 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 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
5 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id

Prompt Table: GP_CAL_RUN4_VW
Set Control Field: CAL_RUN_ID

6 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

Prompt Table: GP_PYGRP5_VW
Set Control Field: GP_PAYGROUP

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

Prompt Table: GP_CALENDAR9_VW
Set Control Field: CAL_ID

8 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
9 RSLT_VER_NUM Number(4,0) SMALLINT NOT NULL Result Version Number
10 RSLT_REV_NUM Number(4,0) SMALLINT NOT NULL Result Revision Number
11 PAYMENT_DT Date(10) DATE Payment Date
12 NAME Character(50) VARCHAR2(50) NOT NULL Name
13 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title