EMPLIDPrompt Table: PERSON
COMPANYPrompt Table: COMPANY_TBL
PY_IC_W4_UPD_VW(SQL View) |
Index Back |
|---|---|
Payroll Employee Search ViewThis search view is used to locate specific payroll employee data from REG_REGION = 'USA'. If an employee has multiple jobs, this search view will look for the job with PRIMARY_JOB_APP = 'BN' (for payroll) |
| SELECT J.EMPLID ,J.COMPANY ,P.NAME ,P.NAME_AC ,P.LAST_NAME_SRCH FROM PS_JOB J , PS_PERSON_NAME P , PS_PRIMARY_JOBS R WHERE J.EMPLID = P.EMPLID AND J.EMPLID = R.EMPLID AND J.EMPL_RCD = R.EMPL_RCD AND J.HR_STATUS = 'A' AND J.REG_REGION = 'USA' AND J.EFFDT IN (( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= %CurrentDateIn),( SELECT MIN(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.ACTION IN ('HIR','REH','ADL') AND J1.EFFDT > %CurrentDateIn)) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) AND R.PRIMARY_JOB_APP = 'BN' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 2 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
| 5 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |