GPSG_APP_8A_VW(SQL View) |
Index Back |
---|---|
Search Vw-Appendix 8ABased 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 |