EMPLIDPrompt Table: PERSON
OPRIDCOMPANYPrompt Table: COMPANY_TBL
PY_IC_EE_W4_VW(SQL View) |
Index Back |
|---|---|
Payroll Employee Search ViewThis search view is used to locate specific payroll employee data. If an employee has multiple jobs, this search view will look for the job with PRIMARY_JOB_APP = 'BN' (for payroll) and PRIMARY_JOB_IND = 'Y'. |
| SELECT J.EMPLID ,O.OPRID ,J.COMPANY ,P.NAME ,P.NAME_AC ,P.LAST_NAME_SRCH FROM PS_JOB J , PS_PERSON_NAME P , PS_PRIMARY_JOBS R , PSOPRDEFN O , PS_COMPANY_TBL C WHERE J.EMPLID = O.EMPLID AND J.EMPLID=P.EMPLID AND J.EMPLID = R.EMPLID AND J.EMPL_RCD = R.EMPL_RCD AND J.HR_STATUS = 'A' AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD 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' AND R.PRIMARY_JOB_IND = 'Y' AND C.COMPANY = J.COMPANY AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY=C.COMPANY AND C1.EFFDT <= %CurrentDateIn) AND C.COUNTRY = 'USA' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 2 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 3 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 4 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 5 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
| 6 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |