AA_HIST_JPN_VW

(SQL View)
 Index Back

Additional Appt History View

Lists all Additional Appointment History for one Main Appointment.

SELECT A.EMPLID , A.MAIN_APPT_NUM_JPN , A.EFFDT , A.EMPL_RCD ,A.EMPL_STATUS , A.BUSINESS_UNIT , A.COMPANY , A.DEPTID , A.LOCATION , A.SUPV_LVL_ID , B.INTCP_COMPANY2 , B.INTCP_DEPTID2 , B.INTCP_SUPV_LVL_ID2 , B.START_DATE_JPN , B.EXP_END_DATE_JPN , B.END_DATE_JPN FROM PS_JOB A , PS_JOB_JR B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.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 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EMPL_RCD_JPN Signed Number(4,0) DECIMAL(3) NOT NULL Tracks the empl_rcd of an additional appointment. This is used in records that also lists the main appointment empl_rcd.
5 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
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

Prompt Table: BUSUNIT_HR_VW

7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

10 SUPV_LVL_ID Character(8) VARCHAR2(8) NOT NULL Supervisor Level ID for use in capability grade system
11 INTCP_COMPANY2 Character(3) VARCHAR2(3) NOT NULL External Company

Prompt Table: INTCPX_CPY_JPN

12 INTCP_DEPTID2 Character(10) VARCHAR2(10) NOT NULL External DeptID

Prompt Table: INTCPX_DPT_JPN

13 INTCP_SUPV_LVL_ID2 Character(8) VARCHAR2(8) NOT NULL External Supv Lvl

Prompt Table: INTCPX_SPV_JPN

14 START_DATE_JPN Date(10) DATE Tracks the beginning date of an additional appointment assignment.
15 EXP_END_DATE_JPN Date(10) DATE Tracks the date in the future when the additional appointment assignment is expected to be completed.
16 END_DATE_JPN Date(10) DATE The actual date that an additional appointment assignment was completed.