PY_IN_EMP_ST_VW (SQL View)	Index Back
Insights Employee Status View Insights Employee Status View based on Categoey Setup

PY_IN_EMP_ST_VW

(SQL View)

Insights Employee Status View

Insights Employee Status View based on Categoey Setup

SELECT DISTINCT A.EMPLID ,A.EMPL_STATUS ,A.HR_STATUS ,1 ,C.TAXFORM_ID ,C.CALENDAR_YEAR ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'EMPL_STATUS' AND X.FIELDVALUE = A.EMPL_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) FROM PS_YE_AMOUNTS C ,PS_JOB A WHERE C.EMPLID=A.EMPLID AND C.COMPANY=A.COMPANY AND A.EMPL_RCD=( SELECT MAX(AA1.EMPL_RCD) FROM PS_JOB AA1 WHERE A.EMPLID=AA1.EMPLID AND A.COMPANY=AA1.COMPANY AND AA1.EFFDT<=%DateIn((%NumtoChar(C.CALENDAR_YEAR) %Concat '-12-31'))) AND A.EFFDT=( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE A.EMPLID=AA1.EMPLID AND A.EMPL_RCD=AA1.EMPL_RCD AND A.COMPANY=AA1.COMPANY AND AA1.EFFDT<=%DateIn((%NumtoChar(C.CALENDAR_YEAR) %Concat '-12-31'))) AND A.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_Job D WHERE D.EmplID = A.EmplID AND D.EMPL_RCD = A.EMPL_RCD AND D.COMPANY=A.COMPANY AND D.EffDt = A.EffDt) AND NOT EXISTS ( SELECT 'X' FROM PS_PY_EMPL_ST_PRTY E ) AND EXISTS ( SELECT '1' FROM PS_PY_IN_YE_FMS_VW FM1 WHERE C.EMPLID=FM1.EMPLID AND C.CALENDAR_YEAR=FM1.PY_TAX_YEAR) UNION SELECT DISTINCT A.EMPLID ,A.EMPL_STATUS ,A.HR_STATUS ,B.PRIORITY ,C.TAXFORM_ID ,C.CALENDAR_YEAR ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'EMPL_STATUS' AND X.FIELDVALUE = A.EMPL_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)) FROM PS_YE_AMOUNTS C ,PS_JOB A ,PS_PY_EMPL_ST_PRTY B WHERE A.EMPL_STATUS=B.EMPL_STATUS AND C.EMPLID=A.EMPLID AND C.COMPANY=A.COMPANY AND A.EMPL_RCD=( SELECT MAX(AA1.EMPL_RCD) FROM PS_JOB AA1 WHERE A.EMPLID=AA1.EMPLID AND A.COMPANY=AA1.COMPANY AND AA1.EFFDT<=%DateIn((%NumtoChar(C.CALENDAR_YEAR) %Concat '-12-31'))) AND A.EFFDT=( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE A.EMPLID=AA1.EMPLID AND A.EMPL_RCD=AA1.EMPL_RCD AND A.COMPANY=AA1.COMPANY AND AA1.EFFDT<=%DateIn((%NumtoChar(C.CALENDAR_YEAR) %Concat '-12-31'))) AND A.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_Job D WHERE D.EmplID = A.EmplID AND D.EMPL_RCD=A.EMPL_RCD AND D.COMPANY=A.COMPANY AND D.EffDt = A.EffDt) AND B.PRIORITY=( SELECT MIN (B1.PRIORITY) FROM PS_YE_AMOUNTS C1 ,PS_JOB A1 ,PS_PY_EMPL_ST_PRTY B1 WHERE A1.EMPL_STATUS=B1.EMPL_STATUS AND A1.EMPLID=A.EMPLID AND C1.COMPANY=A1.COMPANY AND C1.EMPLID=A1.EMPLID AND A1.EMPL_RCD=( SELECT MAX(AA11.EMPL_RCD) FROM PS_JOB AA11 WHERE A1.EMPLID=AA11.EMPLID AND A1.COMPANY=AA11.COMPANY AND AA11.EFFDT<=%DateIn((%NumtoChar(C.CALENDAR_YEAR) %Concat '-12-31'))) AND A1.EFFDT=( SELECT MAX(AAA1.EFFDT) FROM PS_JOB AAA1 WHERE A1.EMPLID=AAA1.EMPLID AND A1.EMPL_RCD=AAA1.EMPL_RCD AND A1.COMPANY=AAA1.COMPANY AND AAA1.EFFDT<=%DateIn((%NumtoChar(C.CALENDAR_YEAR) %Concat '-12-31'))) AND A1.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_Job D WHERE D.EmplID = A1.EmplID AND D.EMPL_RCD=A1.EMPL_RCD AND D.COMPANY=A1.COMPANY AND D.EffDt = A1.EffDt) ) AND EXISTS ( SELECT '1' FROM PS_PY_IN_YE_FMS_VW FM1 WHERE C.EMPLID=FM1.EMPLID AND C.CALENDAR_YEAR=FM1.PY_TAX_YEAR) AND EXISTS ( SELECT '1' FROM PS_PY_EMPL_ST_PRTY E WHERE A.EMPL_STATUS=E.EMPL_STATUS)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration

3 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive

4 PRIORITY Number(3,0) SMALLINT NOT NULL Priority

5 TAXFORM_ID Character(1) VARCHAR2(1) NOT NULL Tax Form Identification
A=T4A Slip
B=Business Payrolls Survey - CAN
C=WCB Assessable Earnings
E=Record of Employment
F=940 - Annual Employer's FUTA
I=W-2VI
J=W-2cVI
M=W-2GU
N=W-2cGU
P=W-2PR
Q=W-2cPR
R=RL-1 Slip
S=W-2AS
T=T4 Slip
U=W-2cAS
V=RL-2 Slip
W=W-2
X=W-2c
Y=1099-R
Z=1042-S

6 PY_TAX_YEAR Number(4,0) SMALLINT NOT NULL Represents the year for personal income tax purposes.

7 PAYROLL_STATUS_PY Character(30) VARCHAR2(30) NOT NULL Employee Status

8 HR_STATUS_PY Character(30) VARCHAR2(30) NOT NULL HR Status Description

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll Status A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration
3	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
4	PRIORITY	Number(3,0)	SMALLINT NOT NULL	Priority
5	TAXFORM_ID	Character(1)	VARCHAR2(1) NOT NULL	Tax Form Identification A=T4A Slip B=Business Payrolls Survey - CAN C=WCB Assessable Earnings E=Record of Employment F=940 - Annual Employer's FUTA I=W-2VI J=W-2cVI M=W-2GU N=W-2cGU P=W-2PR Q=W-2cPR R=RL-1 Slip S=W-2AS T=T4 Slip U=W-2cAS V=RL-2 Slip W=W-2 X=W-2c Y=1099-R Z=1042-S
6	PY_TAX_YEAR	Number(4,0)	SMALLINT NOT NULL	Represents the year for personal income tax purposes.
7	PAYROLL_STATUS_PY	Character(30)	VARCHAR2(30) NOT NULL	Employee Status
8	HR_STATUS_PY	Character(30)	VARCHAR2(30) NOT NULL	HR Status Description