GPJP_YEA_ST_VW

(SQL View)
 Index Back

Employee YEA Self Serv status

This record contains the statuses of an Employee YEA Information. It is used to populate the YEA Information main page

SELECT OPR.OPRID , C.EMPLID , C.DATE_YEAR_VAL , C.GPJP_YEA_EEPR_STAT , C.GPJP_YEA_EETX_STAT , C.GPJP_YEA_DTAX_STAT , C.GPJP_YEA_LIFE_STAT , C.GPJP_YEA_NLIF_STAT , C.GPJP_YEA_SP_STAT , C.GPJP_YEA_HOUS_STAT , C.DATETIME_STAMP , A.GPJP_TAX_ESTAB , H.PAY_ENTITY , B.BUSINESS_UNIT , B.DEPTID , B.LOCATION FROM PS_GPJP_IT_PYE_TAX A , PS_JOB B , PS_GPJP_YEA_STATUS C , PS_GPJP_YEA_SETUP D , PS_GP_PYGRP H , PS_SJT_PERSON SEC , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EMPLID = C.EMPLID AND A.EMPLID = SEC.EMPLID AND A.EMPL_RCD = SEC.EMPL_RCD AND B.GP_PAYGROUP = H.GP_PAYGROUP AND B.EMPL_RCD = ( SELECT D.EMPL_RCD FROM PS_GPJP_IT_PYE_TAX D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.EFFDT = A.EFFDT AND A.GPJP_IT_TYPE = '10') AND C.DATE_YEAR_VAL = D.DATE_YEAR_VAL AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB BB WHERE BB.EMPLID = B.EMPLID AND BB.EMPL_RCD = B.EMPL_RCD AND BB.EFFDT <= D.ASOFDATE ) AND B.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT = B.EFFDT) AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_GPJP_IT_PYE_TAX AA WHERE AA.EMPLID = A.EMPLID AND AA.EMPL_RCD = A.EMPL_RCD AND EFFDT <= D.ASOFDATE) AND B.HR_STATUS = 'A' AND %Sql(SCRTY_WHERE, 'PPLJOB')

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 DATE_YEAR_VAL Number(4,0) SMALLINT NOT NULL Date Year Value
4 GPJP_YEA_EEPR_STAT Character(3) VARCHAR2(3) NOT NULL YEA Employee Information Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
5 GPJP_YEA_EETX_STAT Character(3) VARCHAR2(3) NOT NULL YEA Employee Tax Information Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
6 GPJP_YEA_DTAX_STAT Character(3) VARCHAR2(3) NOT NULL YEA Dep Tax Information Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
7 GPJP_YEA_LIFE_STAT Character(3) VARCHAR2(3) NOT NULL YEA Life Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
8 GPJP_YEA_NLIF_STAT Character(3) VARCHAR2(3) NOT NULL YEA Non Life Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
9 GPJP_YEA_SP_STAT Character(3) VARCHAR2(3) NOT NULL YEA Spouse Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
10 GPJP_YEA_HOUS_STAT Character(3) VARCHAR2(3) NOT NULL YEA Housing Status
10=Open
20=Reviewed
30=Rejected
40=Submitted
50=Approved
60=Sent to Payroll
11 DATETIME_STAMP DateTime(26) TIMESTAMP DateTime Stamp
12 GPJP_TAX_ESTAB Character(15) VARCHAR2(15) NOT NULL Salary Payer Reference Number

Prompt Table: GPJP_TAXESIT_VW

13 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity

Prompt Table: GP_PYENT

14 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL