PY_MOD_US_JOBVW

(SQL View)
 Index Back

Paycheck Modeling

This view list employee jobs that can be use to create a model check

SELECT DISTINCT A.COMPANY , A.PAYGROUP , A.EMPLID , A.EMPL_RCD , G.PAY_FREQUENCY , D.DESCR , E.DESCR , F.DESCR FROM PS_JOB_CURR_ALL_VW A , PS_JOBCODE_TBL D , PS_DEPT_TBL E , PS_COMPANY_TBL F , PS_PAYGROUP_TBL G WHERE A.EMPL_STATUS IN ('A','P','Q','U') AND D.SETID = A.SETID_JOBCODE AND D.JOBCODE = A.JOBCODE AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_JOBCODE_TBL D_ED WHERE D.SETID = D_ED.SETID AND D.JOBCODE = D_ED.JOBCODE AND D_ED.EFFDT <= %CurrentDateIn) AND E.DEPTID = A.DEPTID AND E.SETID = A.SETID_DEPT AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.DEPTID = E_ED.DEPTID AND E_ED.EFFDT <= %CurrentDateIn) AND F.COMPANY = A.COMPANY AND F.EFFDT = ( SELECT MAX(F_ED.EFFDT) FROM PS_COMPANY_TBL F_ED WHERE F.COMPANY = F_ED.COMPANY AND F_ED.EFFDT <= %CurrentDateIn) AND G.COMPANY = A.COMPANY AND G.PAYGROUP = A.PAYGROUP AND G.EFF_STATUS = 'A' AND G.COUNTRY = 'USA' AND G.EFFDT = ( SELECT MAX(G_ED.EFFDT) FROM PS_PAYGROUP_TBL G_ED WHERE G.COMPANY = G_ED.COMPANY AND G.PAYGROUP = G_ED.PAYGROUP AND G_ED.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
A=Annual
B=Biweekly
C=Contract
D=Daily
H=Hourly
M=Monthly
S=Semimonthly
W=Weekly
6 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
7 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name
8 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr