X_EMPL_ORG_VW

(SQL View)
 Index Back

Employee Org View

PeoopleTools Tech Tips Randy Groncki 2023-12-16 [email protected] BI Publisher QR Codes

SELECT p.emplid , p.empl_rcd , j.empl_status , j.jobcode , jc.descr AS jobcode_descr , j.company , c.descr AS company_descr , j.deptid , d.descr AS dept_descr , j.full_part_time , ' ' FROM ps_per_org_asgn p , ps_job j , ps_company_tbl c , ps_dept_tbl d , ps_jobcode_tbl jc WHERE j.emplid = p.emplid and j.empl_rcd = p.empl_rcd AND j.effdt = ( SELECT MAX(j2.effdt) FROM ps_job j2 WHERE j2.emplid = j.emplid AND j2.empl_rcd = j.empl_rcd AND j2.effdt <= sysdate) AND j.effseq = ( SELECT MAX(j3.effseq) FROM ps_job j3 WHERE j3.emplid = j.emplid AND j3.empl_rcd = j.empl_rcd AND j3.effdt = j.effdt) AND c.company = j.company AND c.effdt = ( SELECT MAX(c2.effdt) FROM ps_company_tbl c2 WHERE c2.company = c.company AND c2.effdt <= j.effdt) AND d.setid = j.setid_dept AND d.deptid = j.deptid AND d.effdt = ( SELECT MAX(d2.effdt) FROM ps_dept_tbl d2 WHERE d2.setid = d.setid AND d2.deptid = d.deptid AND d2.effdt <= j.effdt) AND jc.setid = j.setid_jobcode AND jc.jobcode = j.jobcode AND jc.effdt = ( SELECT MAX(jc2.effdt) FROM ps_jobcode_tbl jc2 WHERE jc2.setid = jc.setid AND jc2.jobcode = j.jobcode AND jc2.effdt <= j.effdt)

# 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 Rcd Nbr
3 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
A=Active
D=Deceased
I=Inactive
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

Default Value: A

4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

5 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Jobcode Description
6 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

7 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

9 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department ID Description
10 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
F=Full-Time
P=Part-Time

Default Value: F

11 PTDM_LINK Character(254) VARCHAR2(254) NOT NULL Summary Page