EMPLIDEMPL_RCDCOMPANY_DESCRA=Active
I=Inactive
Default Value: %date
N=Not Applicable
P=Primary Job
S=Secondary Job
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
PY_W4_EMPDET_VW(SQL View) |
Index Back |
|---|---|
Rvw Employee Details for W4View created to support W4 Updatable PDF |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.COMPANY , C.DESCR , A.HR_STATUS , A.EFFDT , A.JOB_INDICATOR , B.PER_ORG FROM PS_JOB A , PS_PER_ORG_ASGN B , PS_COMPANY_TBL C , PS_PY_PDF_COMP_MAP D , PS_FED_TAX_DATA E WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.COMPANY = C.COMPANY AND A.COMPANY = D.COMPANY AND A.EMPLID = E.EMPLID AND A.COMPANY = E.COMPANY AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT <= %CurrentDateIn AND A2.EFFSEQ IN ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A3.EMPLID=A2.EMPLID AND A3.EMPL_RCD=A2.EMPL_RCD AND A3.EFFDT=A2.EFFDT)) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = C.COMPANY AND C1.EFFDT <= %CurrentDateIn) AND D.COUNTRY = 'USA' AND ((D.PY_VIEW_INACT_JOB = 'Y') OR (D.PY_VIEW_INACT_JOB = 'N' AND A.HR_STATUS = 'A')) AND ((D.PY_POI_UPD_TAX = 'Y' AND B.PER_ORG IN ('EMP','POI')) OR (D.PY_POI_UPD_TAX = 'N' AND B.PER_ORG = 'EMP')) UNION SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.COMPANY , C.DESCR , A.HR_STATUS , A.EFFDT , A.JOB_INDICATOR , B.PER_ORG FROM PS_JOB A , PS_PER_ORG_ASGN B , PS_COMPANY_TBL C , PS_PY_PDF_COMP_MAP D , PS_FED_TAX_DATA E WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.COMPANY = C.COMPANY AND A.COMPANY = D.COMPANY AND A.EMPLID = E.EMPLID AND A.COMPANY = E.COMPANY AND A.EFFDT = ( SELECT MIN(A2.EFFDT) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.HR_STATUS = 'A' AND A2.EFFDT > %CurrentDateIn AND A2.EFFSEQ IN ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A3.EMPLID=A2.EMPLID AND A3.EMPL_RCD=A2.EMPL_RCD AND A3.EFFDT=A2.EFFDT)) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_COMPANY_TBL C1 WHERE C1.COMPANY = C.COMPANY AND C1.EFFDT <= %CurrentDateIn) AND D.COUNTRY = 'USA' AND ((D.PY_VIEW_INACT_JOB = 'Y') OR (D.PY_VIEW_INACT_JOB = 'N' AND A.HR_STATUS = 'A')) AND ((D.PY_POI_UPD_TAX = 'Y' AND B.PER_ORG IN ('EMP','POI')) OR (D.PY_POI_UPD_TAX = 'N' AND B.PER_ORG = 'EMP')) AND NOT EXISTS ( SELECT 'Y' FROM PS_JOB A4 WHERE A4.EMPLID = A.EMPLID AND A4.EMPL_RCD = A.EMPL_RCD AND A4.EFFDT = ( SELECT MAX(A5.EFFDT) FROM PS_JOB A5 WHERE A5.EMPLID = A4.EMPLID AND A5.EMPL_RCD = A4.EMPL_RCD AND A5.EFFDT <= %CurrentDateIn) AND A4.EFFSEQ = ( SELECT MAX(A6.EFFSEQ) FROM PS_JOB A6 WHERE A6.EMPLID = A4.EMPLID AND A6.EMPL_RCD = A4.EMPL_RCD AND A6.EFFDT = A4.EFFDT ) AND A4.COMPANY = A.COMPANY AND A4.HR_STATUS = 'A') AND 'Y' = ( SELECT PY_PREB_W4_SS FROM PS_INSTALLATION_PY) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 4 | COMPANY_DESCR |
Character(30) | VARCHAR2(30) NOT NULL | Company Descr |
| 5 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 6 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 7 | JOB_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee.
In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
N=Not Applicable P=Primary Job S=Secondary Job |
| 8 | PER_ORG | Character(3) | VARCHAR2(3) NOT NULL |
Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker EMP=Employee POI=Person of Interest |