GPSG_APP_8A_VW

(SQL View)
Index Back

Search Vw-Appendix 8A

Based on the core PERS_SRCH_GBL view, to show records only for employees in departments that your operator class is allowed to access, with addition of Appendix 8A, to show only employees with App 8A records.

SELECT %Sql(SCRTY_SEL_PKEY,SEC,SEC) , %Sql(SCRTY_SEL_NMSBR,SEC,SEC) ,A8A.BALANCE_GRP_NUM ,A8A.GPSG_CPY_TAX_REF ,A8A.GPSG_TAX_YEAR FROM PS_GPSG_APP_8A A8A , PS_JOB_JR JJR , PS_PERS_SRCH_GBL SEC WHERE A8A.EMPLID = SEC.EMPLID AND JJR.EMPLID = SEC.EMPLID AND JJR.EMPL_RCD = SEC.EMPL_RCD AND JJR.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB_JR JJR2 WHERE JJR2.EMPLID = JJR.EMPLID AND JJR2.EMPL_RCD = JJR.EMPL_RCD AND JJR2.EFFDT <= %CurrentDateIn) AND JJR.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB_JR JJR3 WHERE JJR3.EMPLID = JJR.EMPLID AND JJR3.EMPL_RCD = JJR.EMPL_RCD AND JJR3.EFFDT = JJR.EFFDT) AND (SEC.EMPL_STATUS IN ('A', 'D','L','P', 'Q', 'R', 'S','T', 'U', 'V','W','X') OR A8A.GPSG_TAX_YEAR <= %Sql(GPSG_DATE_YEARPART,JJR.EFFDT))

# 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 Record
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
7 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI
8 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
9 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
10 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
11 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
12 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
13 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
14 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
15 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
16 BALANCE_GRP_NUM Character(3) VARCHAR2(3) NOT NULL This field will be used to determine the grouping of balances from the different jobs of the employee.
17 GPSG_CPY_TAX_REF Character(11) VARCHAR2(11) NOT NULL Singapore Company Tax Reference Number

Prompt Table: GPSG_IRAS_CPY

18 GPSG_TAX_YEAR Number(4,0) SMALLINT NOT NULL Tax Year for Singapore IRAS Reporting